Query to get the Service Contract Details
Introduction: This query is used to fetch the service contract details.In this we will be passing contract_number as the parameter.
SQL Query:
select
a.id,a.contract_number,a.start_date,a.sts_code,a.cust_po_number,g.segment1,g.description,b.currency_code,o.trx_number “Invoice Number”
,TO_CHAR( q.date_billed_from, ‘DD-MON-YYYY HH24-MI’ ) Bill_From,TO_CHAR(q.date_billed_to, ‘DD-MON-YYYY HH24-MI’ ) Bill_To ,q.amount,DECODE(c.lse_id,8,’Party’,7,’Item’,9,’Product’,10,’Site’,11,’System’,35,’Customer’) Level_e
,d.jtot_object1_code,l.party_id,
n.address1,n.address2,n.address3,n.address4,n.city,n.state,n.country,n.postal_code
from okc_k_headers_all_b a,
okc_k_lines_b b,
okc_k_lines_b c,
okc_k_items d,
csi_item_instances f,
mtl_system_items_b g,
okc_k_party_roles_b h,
hz_cust_accounts i,
hz_cust_acct_sites_all j,
hz_cust_site_uses_all k,
hz_parties l,
hz_party_sites m,
hz_locations n,
oks_bill_transactions o,
oks_bill_txn_lines p,
oks_bill_cont_lines q
where 1=1
and a.id = b.dnz_chr_id
and b.id = c.cle_id
and c.id = d.cle_id
and d.object1_id1 = f.instance_id
and f.inventory_item_id = g.inventory_item_id
and a.inv_organization_id = g.organization_id
and b.ship_to_site_use_id = k.site_use_id
and k.cust_acct_site_id = j.cust_acct_site_id
and j.cust_account_id = i.cust_account_id
and i.party_id = l.party_id
and n.location_id = m.location_id
and m.party_site_id = j.party_site_id
and l.party_id = m.party_id
and h.dnz_chr_id = a.id
and o.id = p.btn_id
and p.bcl_id = q.id
and q.cle_id = c.cle_id
and b.dnz_chr_id = a.id
and h.cle_id is null
and h.rle_code = ‘CUSTOMER’
and h.jtot_object1_code =’OKX_PARTY’
and a.scs_code = ‘SERVICE’
and a.sts_code =’ACTIVE’
and b.sts_code =’ACTIVE’
and a.contract_number = :p_ contract_number
;
Conclusion: This SQL query provides information about the details of the service contract.
