To counter reading for which invoice got generated

select mtl.segment1, obsld.ACTUAL,obsld.AMOUNT,obsld.END_READING,obsld.BASE_READING,( select trx_number from OKS_BILL_TRANSACTIONS
where id=okcl.BTN_ID ) Invoice_number from
okc_k_headers_b okh,
okc_k_lines_b okl,
oks_bill_cont_lines okcl,
oks_bill_sub_lines obsl,
oks_bill_sub_line_dtls obsld,
okc_k_items oki ,
mtl_system_items mtl
where okh.contract_number='XX-OKS-001'
and okl.chr_id=okh.id
and okl.lse_id=12
and okcl.cle_id= okl.id
and obsl.bcl_id= okcl.id
and obsld.bsl_id= obsl.id
and oki.cle_id = okl.id
and mtl.inventory_item_id =to_number(oki.obJECT1_ID1)
and mtl.organization_id = to_number(oki.obJECT1_ID2)
and mtl.segment1='&usage_item' -- if more than one usage item,you can give the item number here

How to get the contract associated customers

Ship to site details ....from service line...


SELECT hcsu.LOCATION, hcas.*,hz.*
FROM okc_k_headers_b hdr,
okc_k_lines_b LN--,
okc_rule_groups_b grp,
okc_rules_b rul --,
hz_cust_site_uses_all hcsu,
hz_cust_acct_sites_all hcas,
hz_cust_accounts hca,
hz_party_sites hps,
hz_locations hz
WHERE hdr.contract_number = 'XX-OKS-001' -- sample contract ..available in vision instance
AND hdr.ID = LN.chr_id
AND LN.lse_id = 1
AND grp.cle_id = LN.ID
AND grp.ID = rul.rgp_id
AND rul.jtot_object1_code = 'OKX_BILLTO'
AND hcsu.site_use_id = rul.object1_id1
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.cust_account_id = hca.cust_account_id
and hps.party_site_id=hcas.party_site_id
and hz.location_id= hps.location_id

or

SELECT orb.object1_id1
FROM okc_rules_b orb
,okc_rule_groups_b orgb
,okc_k_headers_b okhb
WHERE orgb.ID = orb.rgp_id
AND orb.rule_information_category LIKE 'BTO'
AND orgb.dnz_chr_id = okhb.ID
AND okhb.ID = p_contract_id
AND orgb.cle_id IS NULL;


SELECT hcsu.LOCATION, hcas.*,hz.*
FROM okc_k_headers_b hdr,
okc_k_lines_b LN--,
okc_rule_groups_b grp,
okc_rules_b rul --,
hz_cust_site_uses_all hcsu,
hz_cust_acct_sites_all hcas,
hz_cust_accounts hca,
hz_party_sites hps,
hz_locations hz
WHERE hdr.contract_number = 'XX-OKS-001' -- sample contract ..available in vision instance
AND hdr.ID = LN.chr_id
AND LN.lse_id = 1
AND grp.cle_id = LN.ID
AND grp.ID = rul.rgp_id
AND rul.jtot_object1_code = 'OKX_SHIPTO'
AND hcsu.site_use_id = rul.object1_id1
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.cust_account_id = hca.cust_account_id
and hps.party_site_id=hcas.party_site_id
and hz.location_id= hps.location_id

or

SELECT orb.object1_id1
FROM okc_rules_b orb
,okc_rule_groups_b orgb
,okc_k_headers_b okhb
WHERE orgb.ID = orb.rgp_id
AND orb.rule_information_category LIKE 'STO'
AND orgb.dnz_chr_id = okhb.ID
AND okhb.ID = p_contract_id
AND orgb.cle_id IS NULL;

Contract customer

SELECT hca.cust_account_id
,hca.party_id
FROM okc_k_party_roles_b okprb
,hz_cust_accounts hca
WHERE okprb.object1_id1 = hca.party_id
AND okprb.rle_code LIKE 'CUSTOMER'
AND okprb.jtot_object1_code LIKE 'OKX_PARTY'
AND okprb.dnz_chr_id = p_contract_id
AND hca.status = 'A';

Relation between usage covered line and counters and readings

select mtl_ln_itm.segment1,csi.serial_number from
okc_k_headers_b okh,
okc_k_lines_b ln,
okc_k_items ln_item,
mtl_system_items mtl_ln_itm,
okc_k_lines_b cln,
okc_k_items oki,
cS_COUNTERS cs,
CS_COUNTER_VALUES csv,
CS_COUNTER_GROUPS csg,
csi_item_instances csi
where okh.contract_number='XX-OKS-001'
and okh.id= ln.chr_id
and ln.lse_id=12
and cln.cle_id= ln.id
and oki.cle_id=cln.id
and cs.counter_id=to_number(oki.OBJECT1_ID1)
and csv.COUNTER_ID= cs.counter_id
and ln_item.CLE_ID = ln.id
and mtl_ln_itm.inventory_item_id= to_number(ln_item.OBJECT1_ID1)
and mtl_ln_itm.organization_id= to_number(ln_item.OBJECT1_ID2)
and mtl_ln_itm.segment1='&usage_item'
and csg.COUNTER_GROUP_ID=cs.COUNTER_GROUP_ID
and csi.instance_id =csg.source_object_id
and csi.serial_number ='&serail number'