Oracle Service Contract : Service Contract Tables and Their Joins

Service Contract Table (Header , Line, billing, invoice) :
Query:
select * from okc_k_headers_all_b where contract_number = '22042'; -- contract number step1

select * from okc_k_lines_b where dnz_chr_id = 155090; -- id from step1

select * from OKC_LINE_STYLES_TL
where 1= 1
and id IN(9)
and language = USERENV('LANG') ;

select * from OKC_LINE_STYLES_V
where id in(1,9);

select * from okc_k_items
where 1=1
and dnz_chr_id = 155090; -- id from step2 to see service and covered line item information

select * from csi_item_instances where instance_id = 5314658; -- instance details

select * from mtl_system_items
where inventory_item_id = 209003
and organization_id = 204;

---===========================

select * from oks_k_lines_b where dnz_chr_id = 155111;

select * from mtl_parameters where organization_id = 204;

--============================

--//Billing schedule
select * from OKS_LEVEL_ELEMENTS
where dnz_chr_id = 155090;

select * from oks_subscr_elements
where dnz_chr_id = 155111;

select * from oks_stream_levels_b
where dnz_chr_id = 155111;

--========================
--//Invoive details

select * from ra_interface_lines_all
where interface_line_attribute1 = '22044';

select * from ra_interface_errors_all
where INTERFACE_LINE_ID = 1951157;

select * from ra_customer_trx_all
where interface_header_attribute1 = '22086';

--===================
--//Billing information, invoice number
select * from okc_k_headers_all_b where contract_number = '22042';

select * from okc_k_lines_b where dnz_chr_id = 155090;

select * from oks_bill_cont_lines
where cle_id = '220402334052569069257879398966843068026'; -- cle_id from "okc_k_lines_b"

select * from oks_bill_txn_lines
where bcl_id = '265774538909987897250870486493601488499'; -- id from "oks_bill_cont_lines"

select * from oks_bill_transactions
where id = '265774538909991524028329330381125607027'; -- btn_id from "oks_bill_txn_lines"
--==================

--//Backtrace from invoice
select * from ra_customer_trx_all where interface_header_attribute1 = '22042';

select * from ra_customer_trx_lines_all where customer_trx_id = 1201283;

select * from okc_k_lines_b where id = '220402334052565442480420555079318949498';

select * from okc_k_lines_b where id = '220402334052569069257879398966843068026';

--===================
--//when you create contract from order
select * from oks_reprocessing
where order_number = '69338';

select * from okc_k_rel_objs
where object1_id1 = '362792';

SELECT hdr.contract_number
FROM oe_order_headers_all oe,
okc_k_rel_objs rel,
okc_k_headers_all_b hdr
WHERE rel.chr_id = hdr.id
AND rel.jtot_object1_code = 'OKX_ORDERHEAD'
AND rel.object1_id1 = oe.header_id
AND oe.order_number = '69338';

select a.creation_date,a.* from csi_item_instances a
where INVENTORY_ITEM_ID = 162744
order by a.creation_date desc;



----====================

select * from hz_cust_site_uses_all
where site_use_code = 'BILL_TO'
and site_use_id = 1400;

select * from hz_cust_site_uses_all
where site_use_code = 'SHIP_TO'
and site_use_id = 1401;

select * from hz_cust_acct_sites_all
where cust_acct_site_id = 1331;

select * from hz_cust_accounts
where cust_account_id = 1005;

select * from hz_parties
where party_id = 1005;

select * from hz_party_sites
where party_site_id = 1331;

select * from hz_locations
where location_id =1102;