VIEW: APPS.OKL_CS_PAY_LINE_DTL_UV

Source

View Text - Preformatted

SELECT chr.id chr_id, tpl.id TPL_ID, chr.contract_number CONTRACT_NUMBER, look.meaning LINE_TYPE, tas.asset_number ASSET_NUMBER, styt.name STREAM_TYPE, tpl.amount INVOICE_AMOUNT, vnd_prog.contract_number VENDOR_PROGRAM, pri_label.party_name PRIVATE_LABEL, OKL_CS_LC_CONTRACT_PVT.get_ap_line_tax(apinv.invoice_id,tpl.line_number) TAX_AMOUNT, cust.party_name CUSTOMER, look1.meaning FUNDING_TYPE, DECODE(lseb.lty_code,'ITEM',osi2.NAME,osi1.NAME) ITEM_NAME, iptl.name INSURANCE_PRODUCT, ipc.policy_number POLICY_NUMBER, cin.currency_code CURRENCY, tpl.cnsld_ap_inv_id, apinv.invoice_id FROM OKL_CNSLD_AP_INVS_ALL CIN,OKL_TXL_AP_INV_LNS_ALL_B TPL,AP_INVOICES_ALL APINV,OKL_STRM_TYPE_TL STYT,OKC_K_HEADERS_ALL_B CHR,OKL_K_HEADERS KHR,OKC_K_HEADERS_ALL_B VND_PROG,OKC_K_PARTY_ROLES_B CPL,HZ_PARTIES PRI_LABEL,OKL_TXL_ASSETS_B TAS,OKC_K_LINES_B CLE,OKC_K_LINES_B CLEB,OKC_LINE_STYLES_B LSE,OKC_LINE_STYLES_B LSEB,FND_LOOKUPS LOOK,HZ_CUST_ACCOUNTS HCA,HZ_PARTIES CUST,FND_LOOKUPS LOOK1,OKC_K_ITEMS CIM,OKC_K_ITEMS CIMB,OKX_SYSTEM_ITEMS_V OSI1,OKL_INS_POLICIES_ALL_B IPC,OKL_INS_PRODUCTS_TL IPTL,FND_APPLICATION APP,OKX_SYSTEM_ITEMS_V OSI2 WHERE tpl.cnsld_ap_inv_id = cin.cnsld_ap_inv_id AND cin.cnsld_ap_inv_id = to_number(apinv.reference_key1) AND apinv.application_id = app.application_id AND app.application_short_name = 'OKL' AND apinv.product_table = 'OKL_CNSLD_AP_INVS_ALL' AND apinv.vendor_site_id = cin.ipvs_id AND tpl.sty_id = styt.id AND styt.language = USERENV('LANG') AND tpl.khr_id = chr.id AND chr.id = khr.id AND khr.khr_id = vnd_prog.id(+) AND vnd_prog.scs_code(+) = 'PROGRAM' AND cle.cle_id(+) = tpl.kle_id AND cleb.lse_id = lseb.id(+) AND lseb.lty_code = 'ITEM' AND cleb.dnz_chr_id(+)= cle.dnz_chr_id AND cimb.cle_id(+) = cleb.id AND cimb.dnz_chr_id(+) = cleb.dnz_chr_id AND cleb.cle_id(+) = cle.cle_id AND cle.lse_id = lse.id(+) AND lse.lty_code = look.lookup_code(+) AND (lse.lty_code is null or lse.lty_code IN ('FEE','SOLD_SERVICE','FIXED_ASSET','INSURANCE')) AND look.lookup_type(+) = 'OKC_LINE_TYPE' AND cle.id = tas.kle_id(+) AND tas.dnz_khr_id(+) = cle.dnz_chr_id AND chr.id = cpl.dnz_chr_id(+) AND cpl.rle_code(+) = 'PRIVATE_LABEL' AND cpl.object1_id1 = pri_label.party_id(+) AND chr.cust_acct_id = hca.cust_account_id AND hca.party_id = cust.party_id AND tpl.funding_reference_type_code=look1.lookup_code(+) AND look1.lookup_type(+) = 'OKL_FUNDING_TYPE' AND cim.cle_id(+) = cle.id AND cim.dnz_chr_id(+) = cle.dnz_chr_id AND osi1.id1(+) = cim.object1_id1 AND to_char(osi1.id2(+)) = cim.object1_id2 AND osi2.id1(+) = cimb.object1_id1 AND to_char(osi2.id2(+)) = cimb.object1_id2 AND ipc.khr_id(+) = chr.id AND ipc.ipt_id = iptl.id(+) AND ipc.ipy_type(+) = 'LEASE_POLICY' AND ipc.quote_yn(+) ='N' AND iptl.language(+) = userenv('LANG')

View Text - HTML Formatted

SELECT CHR.ID CHR_ID
, TPL.ID TPL_ID
, CHR.CONTRACT_NUMBER CONTRACT_NUMBER
, LOOK.MEANING LINE_TYPE
, TAS.ASSET_NUMBER ASSET_NUMBER
, STYT.NAME STREAM_TYPE
, TPL.AMOUNT INVOICE_AMOUNT
, VND_PROG.CONTRACT_NUMBER VENDOR_PROGRAM
, PRI_LABEL.PARTY_NAME PRIVATE_LABEL
, OKL_CS_LC_CONTRACT_PVT.GET_AP_LINE_TAX(APINV.INVOICE_ID
, TPL.LINE_NUMBER) TAX_AMOUNT
, CUST.PARTY_NAME CUSTOMER
, LOOK1.MEANING FUNDING_TYPE
, DECODE(LSEB.LTY_CODE
, 'ITEM'
, OSI2.NAME
, OSI1.NAME) ITEM_NAME
, IPTL.NAME INSURANCE_PRODUCT
, IPC.POLICY_NUMBER POLICY_NUMBER
, CIN.CURRENCY_CODE CURRENCY
, TPL.CNSLD_AP_INV_ID
, APINV.INVOICE_ID
FROM OKL_CNSLD_AP_INVS_ALL CIN
, OKL_TXL_AP_INV_LNS_ALL_B TPL
, AP_INVOICES_ALL APINV
, OKL_STRM_TYPE_TL STYT
, OKC_K_HEADERS_ALL_B CHR
, OKL_K_HEADERS KHR
, OKC_K_HEADERS_ALL_B VND_PROG
, OKC_K_PARTY_ROLES_B CPL
, HZ_PARTIES PRI_LABEL
, OKL_TXL_ASSETS_B TAS
, OKC_K_LINES_B CLE
, OKC_K_LINES_B CLEB
, OKC_LINE_STYLES_B LSE
, OKC_LINE_STYLES_B LSEB
, FND_LOOKUPS LOOK
, HZ_CUST_ACCOUNTS HCA
, HZ_PARTIES CUST
, FND_LOOKUPS LOOK1
, OKC_K_ITEMS CIM
, OKC_K_ITEMS CIMB
, OKX_SYSTEM_ITEMS_V OSI1
, OKL_INS_POLICIES_ALL_B IPC
, OKL_INS_PRODUCTS_TL IPTL
, FND_APPLICATION APP
, OKX_SYSTEM_ITEMS_V OSI2
WHERE TPL.CNSLD_AP_INV_ID = CIN.CNSLD_AP_INV_ID
AND CIN.CNSLD_AP_INV_ID = TO_NUMBER(APINV.REFERENCE_KEY1)
AND APINV.APPLICATION_ID = APP.APPLICATION_ID
AND APP.APPLICATION_SHORT_NAME = 'OKL'
AND APINV.PRODUCT_TABLE = 'OKL_CNSLD_AP_INVS_ALL'
AND APINV.VENDOR_SITE_ID = CIN.IPVS_ID
AND TPL.STY_ID = STYT.ID
AND STYT.LANGUAGE = USERENV('LANG')
AND TPL.KHR_ID = CHR.ID
AND CHR.ID = KHR.ID
AND KHR.KHR_ID = VND_PROG.ID(+)
AND VND_PROG.SCS_CODE(+) = 'PROGRAM'
AND CLE.CLE_ID(+) = TPL.KLE_ID
AND CLEB.LSE_ID = LSEB.ID(+)
AND LSEB.LTY_CODE = 'ITEM'
AND CLEB.DNZ_CHR_ID(+)= CLE.DNZ_CHR_ID
AND CIMB.CLE_ID(+) = CLEB.ID
AND CIMB.DNZ_CHR_ID(+) = CLEB.DNZ_CHR_ID
AND CLEB.CLE_ID(+) = CLE.CLE_ID
AND CLE.LSE_ID = LSE.ID(+)
AND LSE.LTY_CODE = LOOK.LOOKUP_CODE(+)
AND (LSE.LTY_CODE IS NULL OR LSE.LTY_CODE IN ('FEE'
, 'SOLD_SERVICE'
, 'FIXED_ASSET'
, 'INSURANCE'))
AND LOOK.LOOKUP_TYPE(+) = 'OKC_LINE_TYPE'
AND CLE.ID = TAS.KLE_ID(+)
AND TAS.DNZ_KHR_ID(+) = CLE.DNZ_CHR_ID
AND CHR.ID = CPL.DNZ_CHR_ID(+)
AND CPL.RLE_CODE(+) = 'PRIVATE_LABEL'
AND CPL.OBJECT1_ID1 = PRI_LABEL.PARTY_ID(+)
AND CHR.CUST_ACCT_ID = HCA.CUST_ACCOUNT_ID
AND HCA.PARTY_ID = CUST.PARTY_ID
AND TPL.FUNDING_REFERENCE_TYPE_CODE=LOOK1.LOOKUP_CODE(+)
AND LOOK1.LOOKUP_TYPE(+) = 'OKL_FUNDING_TYPE'
AND CIM.CLE_ID(+) = CLE.ID
AND CIM.DNZ_CHR_ID(+) = CLE.DNZ_CHR_ID
AND OSI1.ID1(+) = CIM.OBJECT1_ID1
AND TO_CHAR(OSI1.ID2(+)) = CIM.OBJECT1_ID2
AND OSI2.ID1(+) = CIMB.OBJECT1_ID1
AND TO_CHAR(OSI2.ID2(+)) = CIMB.OBJECT1_ID2
AND IPC.KHR_ID(+) = CHR.ID
AND IPC.IPT_ID = IPTL.ID(+)
AND IPC.IPY_TYPE(+) = 'LEASE_POLICY'
AND IPC.QUOTE_YN(+) ='N'
AND IPTL.LANGUAGE(+) = USERENV('LANG')