Service Contracts Query

Headers
=======
create or replace view service_contract_headers_v as
SELECT oah.contract_number contract_number
,oah.id contract_id
, (SELECT TO_CHAR(MAJOR_VERSION)
|| '.'
|| TO_CHAR(MINOR_VERSION)
FROM OKC_K_VERS_NUMBERS_V
WHERE CHR_ID=oah.id
) VERSION ,
oah.class_meaning Category ,
oah.sts_meaning Status ,
oah.short_description SHORT_DESCRIPTION,
opr.role ROLE ,
(SELECT PARTY_NAME
FROM HZ_PARTIES HP,
okc_k_party_roles_b OKP
WHERE HP.PARTY_ID=OKP.object1_id1
AND RLE_CODE =opr.rle_code
AND dnz_chr_id =oah.id
UNION
SELECT hou.name
FROM hr_operating_units hou,
okc_k_party_roles_b OKP
WHERE hou.organization_id =oKP.object1_id1
AND RLE_CODE =opr.rle_code
AND dnz_chr_id =oah.id
) party,
(SELECT cro_code
FROM OKC_CONTACTS OKC
WHERE dnz_chr_id=oah.id
AND cpl_id =opr.id
) contact_role,
(SELECT DECODE(P2.PARTY_NAME,NULL,'NO SALESREP',P2.PARTY_NAME)
FROM hz_relationships r,
hz_parties p3 ,
hz_parties p2 ,
OKC_CONTACTS OKC
WHERE p2.party_id = r.subject_id
AND r.relationship_code IN('CONTACT_OF', 'EMPLOYEE_OF', 'CONTACT')
AND r.content_source_type = 'USER_ENTERED'
AND p3.party_id = r.party_id
AND r.object_id =opr.object1_id1
AND P3.PARTY_ID =OKC.object1_id1
AND okc.dnz_chr_id =oah.id
UNION
SELECT NAME
FROM OKC_CONTACTS OKC,
jtf_rs_salesreps JTF
WHERE OKC.object1_id1 = jtf.salesrep_id
AND jtot_object1_code ='OKX_SALEPERS'
AND OKC.cpl_id =opr.id
AND dnz_chr_id =oah.id
) contact,
(SELECT P3.email_address
FROM hz_relationships r,
hz_parties p3 ,
OKC_CONTACTS OKC
WHERE 1 =1
AND r.relationship_code IN('CONTACT_OF', 'EMPLOYEE_OF', 'CONTACT')
AND r.content_source_type = 'USER_ENTERED'
AND p3.party_id = r.party_id
AND EXISTS
(SELECT '1'
FROM hz_org_contacts oc
WHERE oc.party_relationship_id = r.relationship_id
)
AND r.object_id =opr.object1_id1
AND P3.PARTY_ID =OKC.object1_id1
AND okc.dnz_chr_id=oah.id
AND cpl_id =opr.id
AND rownum =1
UNION
SELECT email_address
FROM OKC_CONTACTS OKC,
jtf_rs_salesreps JTF
WHERE OKC.object1_id1 = jtf.salesrep_id
AND jtot_object1_code ='OKX_SALEPERS'
AND OKC.cpl_id =opr.id
AND dnz_chr_id =oah.id
) email_address ,
(SELECT DECODE (hca.account_name,NULL,hp.party_name,hca.account_name)
||'/'
||hp.party_name --8368
FROM okc_rules_b OKCR ,
hz_cust_site_uses_all hcsu ,
hz_cust_acct_sites_all hzca,
HZ_DQM_AC_AS_V hda ,
hz_parties hp ,
hz_cust_accounts hca , --CUST_ACCOUNT_ID
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1 =hcsu.site_use_id
AND hca.party_id =hp.party_id
AND hca.cust_account_id = hzca.cust_account_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_BILLTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
AND hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND hp.party_id =hda.dqparty_id
AND hda.dqcust_acct_site_id= hzca.cust_acct_site_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =oah.id
) bill_to_account --"BILL TO Account/Party"
,( SELECT DECODE(hca.sales_channel_code,'GOVERNMENT','Yes','No')
FROM okc_rules_b OKCR ,
hz_cust_site_uses_all hcsu ,
hz_cust_acct_sites_all hzca,
hz_cust_accounts_all hca ,
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1 =hcsu.site_use_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_BILLTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
AND hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND hca.cust_account_id = hzca.cust_account_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =oah.id
) GOVERNMENT
, (SELECT HCSU.location
FROM okc_rules_b OKCR ,
hz_cust_site_uses_all hcsu ,
hz_cust_acct_sites_all hzca,
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1 =hcsu.site_use_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_BILLTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
AND hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =oah.id
) bill_to_location ,
(SELECT hda.dqaddress1
FROM okc_rules_b OKCR ,
hz_cust_site_uses_all hcsu ,
hz_cust_acct_sites_all hzca,
HZ_DQM_AC_AS_V hda ,
hz_parties hp ,
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1 =hcsu.site_use_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_BILLTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
AND hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND hp.party_id =hda.dqparty_id
AND hda.dqcust_acct_site_id= hzca.cust_acct_site_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =oah.id
) bill_to_address1 ,
(SELECT hda.dqaddress2
FROM okc_rules_b OKCR ,
hz_cust_site_uses_all hcsu ,
hz_cust_acct_sites_all hzca,
HZ_DQM_AC_AS_V hda ,
hz_parties hp ,
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1 =hcsu.site_use_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_BILLTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
AND hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND hp.party_id =hda.dqparty_id
AND hda.dqcust_acct_site_id= hzca.cust_acct_site_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =oah.id
) bill_to_address2 ,
(SELECT hda.dqcity
FROM okc_rules_b OKCR ,
hz_cust_site_uses_all hcsu ,
hz_cust_acct_sites_all hzca,
HZ_DQM_AC_AS_V hda ,
hz_parties hp ,
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1 =hcsu.site_use_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_BILLTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
AND hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND hp.party_id =hda.dqparty_id
AND hda.dqcust_acct_site_id= hzca.cust_acct_site_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =oah.id
) bill_to_city ,
(SELECT hda.dqstate
FROM okc_rules_b OKCR ,
hz_cust_site_uses_all hcsu ,
hz_cust_acct_sites_all hzca,
HZ_DQM_AC_AS_V hda ,
hz_parties hp ,
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1 =hcsu.site_use_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_BILLTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
AND hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND hp.party_id =hda.dqparty_id
AND hda.dqcust_acct_site_id= hzca.cust_acct_site_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =oah.id
) bill_to_state ,
(SELECT DECODE(SUBSTR(hda.dqpostal_code,1,1),'0',hda.dqpostal_code,'0'||hda.dqpostal_code)
FROM okc_rules_b OKCR ,
hz_cust_site_uses_all hcsu ,
hz_cust_acct_sites_all hzca,
HZ_DQM_AC_AS_V hda ,
hz_parties hp ,
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1 =hcsu.site_use_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_BILLTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
AND hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND hp.party_id =hda.dqparty_id
AND hda.dqcust_acct_site_id= hzca.cust_acct_site_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =oah.id
) bill_to_zipcode ,
(SELECT hda.dqcountry --P.O. BOX 6641 ENGLEWOOD ARAPAHOE CO 80155-6641 United States
FROM okc_rules_b OKCR ,
hz_cust_site_uses_all hcsu ,
hz_cust_acct_sites_all hzca,
HZ_DQM_AC_AS_V hda ,
hz_parties hp ,
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1 =hcsu.site_use_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_BILLTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
AND hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND hp.party_id =hda.dqparty_id
AND hda.dqcust_acct_site_id= hzca.cust_acct_site_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =oah.id
) bill_to_country ,
(SELECT DECODE (hca.account_name,NULL,hp.party_name,hca.account_name)
||'/'
||hp.party_name --8368
FROM okc_rules_b OKCR ,
hz_cust_site_uses_all hcsu ,
hz_cust_acct_sites_all hzca,
HZ_DQM_AC_AS_V hda ,
hz_parties hp ,
hz_cust_accounts hca , --CUST_ACCOUNT_ID
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1 =hcsu.site_use_id
AND hca.party_id =hp.party_id
AND hca.cust_account_id = hzca.cust_account_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_SHIPTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
AND hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND hp.party_id =hda.dqparty_id
AND hda.dqcust_acct_site_id= hzca.cust_acct_site_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =oah.id
) ship_to_account -- "SHIP TO Account/Party"
,
(SELECT HCSU.location
FROM okc_rules_b OKCR ,
hz_cust_site_uses_all hcsu ,
hz_cust_acct_sites_all hzca,
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1 =hcsu.site_use_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_SHIPTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
AND hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =oah.id
) ship_to_location ,
(SELECT hda.dqaddress1
FROM okc_rules_b OKCR ,
hz_cust_site_uses_all hcsu ,
hz_cust_acct_sites_all hzca,
HZ_DQM_AC_AS_V hda ,
hz_parties hp ,
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1 =hcsu.site_use_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_SHIPTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
AND hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND hp.party_id =hda.dqparty_id
AND hda.dqcust_acct_site_id= hzca.cust_acct_site_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =oah.id
) ship_to_address1 ,
(SELECT hda.dqaddress2
FROM okc_rules_b OKCR ,
hz_cust_site_uses_all hcsu ,
hz_cust_acct_sites_all hzca,
HZ_DQM_AC_AS_V hda ,
hz_parties hp ,
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1 =hcsu.site_use_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_SHIPTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
AND hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND hp.party_id =hda.dqparty_id
AND hda.dqcust_acct_site_id= hzca.cust_acct_site_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =oah.id
) ship_to_address2 ,
(SELECT hda.dqcity
FROM okc_rules_b OKCR ,
hz_cust_site_uses_all hcsu ,
hz_cust_acct_sites_all hzca,
HZ_DQM_AC_AS_V hda ,
hz_parties hp ,
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1 =hcsu.site_use_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_SHIPTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
AND hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND hp.party_id =hda.dqparty_id
AND hda.dqcust_acct_site_id= hzca.cust_acct_site_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =oah.id
) ship_to_city ,
(SELECT hda.dqstate
FROM okc_rules_b OKCR ,
hz_cust_site_uses_all hcsu ,
hz_cust_acct_sites_all hzca,
HZ_DQM_AC_AS_V hda ,
hz_parties hp ,
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1 =hcsu.site_use_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_SHIPTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
AND hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND hp.party_id =hda.dqparty_id
AND hda.dqcust_acct_site_id= hzca.cust_acct_site_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =oah.id
) ship_to_state ,
(SELECT DECODE(SUBSTR(hda.dqpostal_code,1,1),'0',hda.dqpostal_code,'0'||hda.dqpostal_code)
FROM okc_rules_b OKCR ,
hz_cust_site_uses_all hcsu ,
hz_cust_acct_sites_all hzca,
HZ_DQM_AC_AS_V hda ,
hz_parties hp ,
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1 =hcsu.site_use_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_SHIPTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
AND hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND hp.party_id =hda.dqparty_id
AND hda.dqcust_acct_site_id= hzca.cust_acct_site_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =oah.id
) ship_to_zipcode ,
(SELECT hda.dqcountry
FROM okc_rules_b OKCR ,
hz_cust_site_uses_all hcsu ,
hz_cust_acct_sites_all hzca,
HZ_DQM_AC_AS_V hda ,
hz_parties hp ,
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1 =hcsu.site_use_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_SHIPTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
AND hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND hp.party_id =hda.dqparty_id
AND hda.dqcust_acct_site_id= hzca.cust_acct_site_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =oah.id
) ship_to_country ,
(SELECT B.name
FROM OKC_RULES_B A,
qp_list_headers_all B
WHERE A.object1_id1 =B.list_header_id
AND A.jtot_object1_code='OKX_PRICE'
AND dnz_chr_id =oah.id
) price_list ,
oah.currency_code ,
oah.CUST_PO_NUMBER ,
(SELECT rule_information11
FROM OKC_RULES_B okr,okc_rule_groups_b okcg
WHERE okr.rule_information_category='SBG'
AND okcg.ID = okr.rgp_id
and okcg.chr_ID = oah.id
)ar_interface ,
(SELECT rule_information12
FROM OKC_RULES_B okr,okc_rule_groups_b okcg
WHERE rule_information_category='SBG'
AND okcg.ID = okr.rgp_id
and okcg.chr_ID = oah.id
)hold_billing ,
(SELECT rule_information1
FROM OKC_RULES_B okr,okc_rule_groups_b okcg
WHERE rule_information_category='CCR'
AND okcg.ID = okr.rgp_id
and okcg.chr_ID = oah.id
)credit_card_number ,
(SELECT rule_information2
FROM OKC_RULES_B okr,okc_rule_groups_b okcg
WHERE rule_information_category='CCR'
AND okcg.ID = okr.rgp_id
and okcg.chr_ID = oah.id
)expiry ,
oah.date_approved ,
oah.date_signed ,
oah.datetime_cancelled ,
oah.date_terminated ,
oah.date_renewed ,
rct.trx_number invoice_number,
rct.trx_date billed_date,
oah.creation_date ,
(select user_name from fnd_user fu
where fu.user_id =oah.created_by
) created_by,
oah.last_update_date ,
(select user_name from fnd_user fu
where fu.user_id =oah.last_updated_by
) last_updated_by
FROM OKS_AUTH_HEADERS_V oah,
OKC_K_PARTY_ROLES_V opr,
ra_customer_trx_all rct
WHERE 1 =1
AND oah.id = opr.chr_id
and rct.ct_reference=oah.contract_number
--and okl.chr_id=169117819153706617322098785615065897622
;


Lines
=====
create or replace view service_contract_lines_v as
select
okh.contract_number contract_number
,okh.id contract_id
,OKL.line_number line_number
,ols.name LINE_TYPE
,(select msi.segment1 from OKC_K_ITEMS oki
,mtl_system_items_b msi
where oki.object1_id1=msi.inventory_item_id
and to_char(msi.organization_id)=oki.object1_id2
and oki.dnz_chr_id=okl.chr_id
)service_item
,okl.sts_code line_status
,( SELECT hca.account_number --decode (hca.account_name,NULL,hp.party_name,hca.account_name),hca.account_number --8368
FROM okc_rules_b OKCR,
hz_cust_site_uses_all hcsu,
hz_cust_acct_sites_all hzca,
hz_parties hp,
hz_cust_accounts hca, --CUST_ACCOUNT_ID
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1=hcsu.site_use_id
and hca.party_id=hp.party_id
and hca.cust_account_id= hzca.cust_account_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_BILLTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
and hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =okl.chr_id
) BILL_TO_ACCOUNT_NUMBER
/*
,( SELECT HCSU.location --decode (hca.account_name,NULL,hp.party_name,hca.account_name),hca.account_number --8368
FROM okc_rules_b OKCR,
hz_cust_site_uses_all hcsu,
hz_cust_acct_sites_all hzca,
hz_parties hp,
hz_cust_accounts hca, --CUST_ACCOUNT_ID
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1=hcsu.site_use_id
and hca.party_id=hp.party_id
and hca.cust_account_id= hzca.cust_account_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_BILLTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
and hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =okl.chr_id
) BILL_TO_LOCATION
*/
,( SELECT hca.account_number --decode (hca.account_name,NULL,hp.party_name,hca.account_name),hca.account_number --8368
FROM okc_rules_b OKCR,
hz_cust_site_uses_all hcsu,
hz_cust_acct_sites_all hzca,
hz_parties hp,
hz_cust_accounts hca, --CUST_ACCOUNT_ID
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1=hcsu.site_use_id
and hca.party_id=hp.party_id
and hca.cust_account_id= hzca.cust_account_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_SHIPTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
and hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =okl.chr_id
) SHIP_TO_ACCOUNT_NUMBER
/*
,( SELECT HCSU.location --decode (hca.account_name,NULL,hp.party_name,hca.account_name),hca.account_number --8368
FROM okc_rules_b OKCR,
hz_cust_site_uses_all hcsu,
hz_cust_acct_sites_all hzca,
hz_parties hp,
hz_cust_accounts hca, --CUST_ACCOUNT_ID
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1=hcsu.site_use_id
and hca.party_id=hp.party_id
and hca.cust_account_id= hzca.cust_account_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_SHIPTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
and hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =okl.chr_id
) SHIP_TO_LOCATION
*/
,(select OKL.line_number||'.'||oklb.line_number from okc_k_lines_b oklb
WHERE 1=1 --okl.id= oki.cle_id
and oklb.price_level_ind='Y'
and oki.dnz_chr_id=okl.dnz_chr_id
and oklb.id=oki.cle_id
)sub_line_number
,(SELECT olsv.name FROM OKC_LINE_STYLES_v olsv
where olsv.id=(select oklb.lse_id from okc_k_lines_b oklb
WHERE 1=1 --okl.id= oki.cle_id
and oklb.price_level_ind='Y'
and oki.dnz_chr_id=okl.dnz_chr_id
and oklb.id=oki.cle_id)
)Level_name
,(select cii.serial_number from csi_item_instances cii
where cii.instance_id=oki.object1_id1
) serial_number
,(select oklb.start_date from okc_k_lines_b oklb
WHERE 1=1 --okl.id= oki.cle_id
and oklb.price_level_ind='Y'
and oki.dnz_chr_id=okl.dnz_chr_id
and oklb.id=oki.cle_id
)start_date
,(select oklb.end_date from okc_k_lines_b oklb
WHERE 1=1 --okl.id= oki.cle_id
and oklb.price_level_ind='Y'
and oki.dnz_chr_id=okl.dnz_chr_id
and oklb.id=oki.cle_id
)end_date
,TO_NUMBER(OKS_ENT_UTIL_PVT.get_duration_period(okh.START_DATE,
okh.END_DATE,'D')) DURATION
,SUBSTR(OKS_ENT_UTIL_PVT.get_duration_period(okh.START_DATE,
okh.END_DATE, 'P'), 1,25) PERIOD_CODE
,(select oklb.date_terminated from okc_k_lines_b oklb
WHERE 1=1 --okl.id= oki.cle_id
and oklb.price_level_ind='Y'
and oki.dnz_chr_id=okl.dnz_chr_id
and oklb.id=oki.cle_id
)date_terminated
,cii.external_reference
,msi.description external_ref_desc
,oks.name coverage_name
,oks.description coverage_desc
,oki.number_of_items qty
,oki.uom_code uom
,(select oklb.price_unit from okc_k_lines_b oklb
WHERE 1=1 --okl.id= oki.cle_id
and oklb.price_level_ind='Y'
and oki.dnz_chr_id=okl.dnz_chr_id
and oklb.id=oki.cle_id
)unit_price
,(select TO_char(oklb.price_negotiated,'999999999D99')
from okc_k_lines_b oklb
WHERE 1=1
and oklb.id=oki.cle_id
)extended_price
,(select TO_char(oklb.price_negotiated,'999999999D99')
from okc_k_lines_b oklb
WHERE 1=1
and oklb.id=oki.cle_id
)subtotal
,(SELECT ole.date_to_interface
FROM okc_rules_b okcr
,oks_level_elements ole
WHERE 1=1
and ole.rul_id = okcr.id
AND okcr.rule_information_category = 'SLL'
AND okcr.dnz_chr_id = okl.chr_id
and rownum=1
)interface_date
from
OKC_K_HEADERS_B okh
,okc_k_lines_b okl
,oks_coverages_v oks
,OKC_LINE_STYLES_v ols
,OKC_K_ITEMS oki
,mtl_system_items_b msi
,csi_item_instances cii
where 1=1
and okh.id=okl.chr_id
and ols.id=okl.lse_id
AND oks.dnz_chr_id = okl.dnz_chr_id
--AND oks.service_cle_id = okl.cle_id
and msi.inventory_item_id=cii.inventory_item_id
and cii.instance_id = oki.object1_id1
and oki.jtot_object1_code='OKX_CUSTPROD'
and to_char(msi.organization_id)=okh.inv_organization_id --fnd_profile.value('ORG_ID') --oki.object1_id2
and okl.sts_code='ACTIVE'
and oki.dnz_chr_id=okl.dnz_chr_id
--and okl.chr_id=169117819153706617322098785615065897622
;

Headers & Lines
===============
create or replace view service_contract_dtls_v as
select
sch.contract_number
,sch.contract_id
,sch.VERSION
,sch.Category
,sch.Status
,sch.SHORT_DESCRIPTION
,sch.ROLE
,sch.party
,sch.contact_role
,sch.contact
,sch.government
,sch.email_address
,sch.bill_to_account
,sch.bill_to_location
,sch.bill_to_address1
,sch.bill_to_address2
,sch.bill_to_city
,sch.bill_to_state
,sch.bill_to_zipcode
,sch.bill_to_country
,sch.ship_to_account
,sch.ship_to_location
,sch.ship_to_address1
,sch.ship_to_address2
,sch.ship_to_city
,sch.ship_to_state
,sch.ship_to_zipcode
,sch.ship_to_country
,sch.price_list
,sch.currency_code
,sch.cust_po_number
,sch.ar_interface
,sch.hold_billing
,sch.credit_card_number
,sch.date_approved
,sch.date_signed
,sch.datetime_cancelled
,sch.date_terminated
,sch.date_renewed
,sch.invoice_number
,sch.billed_date
,sch.creation_date
,sch.created_by
,sch.last_update_date
,sch.last_updated_by
,sch.expiry
,scl.line_number
,scl.LINE_TYPE
,scl.service_item
,scl.line_status
,scl.BILL_TO_ACCOUNT_NUMBER
--,scl.BILL_TO_LOCATION
,scl.SHIP_TO_ACCOUNT_NUMBER
--,scl.SHIP_TO_LOCATION
,scl.sub_line_number
,scl.Level_name
,scl.serial_number
,scl.start_date
,scl.end_date
,scl.DURATION
,scl.PERIOD_CODE
--,scl.date_terminated
,scl.external_reference
,scl.external_ref_desc
,scl.coverage_name
,scl.coverage_desc
--,scl.invoice_text1||invoice_text2 invoice_text
,scl.qty
,scl.uom
,scl.unit_price
,scl.extended_price
,scl.subtotal
,scl.interface_date
from service_contract_headers_v sch
,service_contract_lines_v scl
where 1=1
and scl.contract_id=sch.contract_id;