VIEW: APPS.OKL_AP_INVOICE_LINES_V

Source

View Text - Preformatted

SELECT aph.invoice_id, aph.invoice_num Invoice_Number, apl.line_number Invoice_Line_Number, INITCAP(apl.LINE_TYPE_LOOKUP_CODE) Invoice_Line_Type, CASE tap.funding_type_code WHEN 'ASSET' THEN 'Asset Funding' WHEN 'EXPENSE' THEN 'Expense Funding' WHEN 'SUPPLIER_RETENTION' THEN 'Supplier Retention' WHEN 'PREFUNDING' THEN 'Pre-Funding' WHEN 'MANU_DISBURSEMENT' THEN 'Manual Disbursement' WHEN 'BORROWER_PAYMENT' THEN 'Borrower Payment' ELSE sty.STYB_PURPOSE_MEANING END Disbursement_type, sty.name Stream_Type, CASE tpl.inv_distr_line_code WHEN 'INVESTOR' THEN NULL ELSE okc.contract_number END Conract_number, (select vp.contract_number vendor_program from okc_k_headers_all_b vp, okl_k_headers okl where okl.id = okc.id and okl.template_type_code = 'PROGRAM' and okl.khr_id = vp.id) vendor_program, CASE tpl.inv_distr_line_code WHEN 'INVESTOR' THEN okc.contract_number ELSE (select okc1.contract_number from OKL_POOLS_ALL pol, okc_k_headers_all_b okc1 where pol.khr_id = okc1.id and okc1.STS_CODE = 'ACTIVE' and exists (select 1 from OKL_POOL_CONTENTS poc where poc.pol_id = pol.id and poc.khr_id = tpl.khr_id) ) END Investor_Agreement_Number, (select cle.name from OKC_K_LINES_V cle, okl_trx_ap_invs_all_b tap1, okl_txl_ap_inv_lns_all_b tpl1 where tpl1.tap_id = tap1.id and cle.id = tpl1.kle_id and tpl1.id = tpl.id and tap1.funding_type_code = 'ASSET') Asset_Number, (select cle.name from OKC_K_LINES_V cle, okl_trx_ap_invs_all_b tap1, okl_txl_ap_inv_lns_all_b tpl1 where tpl1.tap_id = tap1.id and cle.id = tpl1.kle_id and tpl1.id = tpl.id and tpl1.inv_distr_line_code = 'AUTO_DISBURSEMENT') Service_Item, DECODE(tap.funding_type_code, NULL, NULL, tap.vendor_invoice_number) Funding_Request_Number, ( select fnd.meaning from okl_ins_policies_b ins, OKL_INS_PRODUCTS_B inp, FND_LOOKUPS fnd, okl_trx_ap_invs_all_b tap1, okl_txl_ap_inv_lns_all_b tpl1, OKL_STRM_TYPE_V sty1 where tpl1.tap_id = tap1.id and tpl1.id = tpl.id and ins.khr_id = tpl.khr_id and ins.ISS_CODE = 'ACTIVE' and ins.ipt_id = inp.id and inp.ipt_type = fnd.lookup_code and fnd.lookup_type = 'OKL_INSURANCE_PRODUCT_TYPE' and tpl.sty_id = sty1.id and sty1.stream_type_purpose in ('INSURANCE_PAYABLE', 'INSURANCE_ADJUSTMENT') ) Insurance_Product, ( select ins.POLICY_NUMBER from okl_ins_policies_b ins, okl_trx_ap_invs_all_b tap1, okl_txl_ap_inv_lns_all_b tpl1, OKL_STRM_TYPE_V sty1 where tpl1.tap_id = tap1.id and tpl1.id = tpl.id and ins.khr_id = tpl.khr_id and ins.ISS_CODE = 'ACTIVE' and tpl.sty_id = sty1.id and sty1.stream_type_purpose in ('INSURANCE_PAYABLE', 'INSURANCE_ADJUSTMENT') ) Insurance_Policy_Number, (select cur.refund_number from OKL_CURE_REFUNDS cur where cur.tap_id = tpl.tap_id) Refund_number FROM AP_INVOICES_ALL APH,AP_INVOICE_LINES_ALL APL,OKL_TRX_AP_INVS_ALL_B TAP,OKL_TXL_AP_INV_LNS_ALL_B TPL,OKL_STRM_TYPE_V STY,OKC_K_HEADERS_ALL_B OKC WHERE aph.invoice_id = apl.invoice_id and apl.reference_key1 = tpl.id and apl.product_table = 'OKL_TXL_AP_INV_LNS_ALL_B' and tpl.tap_id = tap.id and tpl.sty_id = sty.id and tpl.khr_id = okc.id UNION ALL select aph.invoice_id, aph.invoice_num Invoice_Number, apl.line_number Invoice_Line_Number, INITCAP(apl.LINE_TYPE_LOOKUP_CODE) Invoice_Line_Type, CASE tap.funding_type_code WHEN 'ASSET' THEN 'Asset Funding' WHEN 'EXPENSE' THEN 'Expense Funding' WHEN 'SUPPLIER_RETENTION' THEN 'Supplier Retention' WHEN 'PREFUNDING' THEN 'Pre-Funding' WHEN 'MANU_DISBURSEMENT' THEN 'Manual Disbursement' WHEN 'BORROWER_PAYMENT' THEN 'Borrower Payment' ELSE sty.STYB_PURPOSE_MEANING END Disbursement_type, sty.name Stream_Type, CASE tpl.inv_distr_line_code WHEN 'INVESTOR' THEN NULL ELSE okc.contract_number END Conract_number, (select vp.contract_number vendor_program from okc_k_headers_all_b vp, okl_k_headers okl where okl.id = okc.id and okl.template_type_code = 'PROGRAM' and okl.khr_id = vp.id) vendor_program, CASE tpl.inv_distr_line_code WHEN 'INVESTOR' THEN okc.contract_number ELSE (select okc1.contract_number from OKL_POOLS_ALL pol, okc_k_headers_all_b okc1 where pol.khr_id = okc1.id and okc1.STS_CODE = 'ACTIVE' and exists (select 1 from OKL_POOL_CONTENTS poc where poc.pol_id = pol.id and poc.khr_id = tpl.khr_id) ) END Investor_Agreement_Number, (select cle.name from OKC_K_LINES_V cle, okl_trx_ap_invs_all_b tap1, okl_txl_ap_inv_lns_all_b tpl1 where tpl1.tap_id = tap1.id and cle.id = tpl1.kle_id and tpl1.id = tpl.id and tap1.funding_type_code = 'ASSET') Asset_Number, (select cle.name from OKC_K_LINES_V cle, okl_trx_ap_invs_all_b tap1, okl_txl_ap_inv_lns_all_b tpl1 where tpl1.tap_id = tap1.id and cle.id = tpl1.kle_id and tpl1.id = tpl.id and tpl1.inv_distr_line_code = 'AUTO_DISBURSEMENT') Service_Item, DECODE(tap.funding_type_code, NULL, NULL, tap.vendor_invoice_number) Funding_Request_Number, ( select fnd.meaning from okl_ins_policies_b ins, OKL_INS_PRODUCTS_B inp, FND_LOOKUPS fnd, okl_trx_ap_invs_all_b tap1, okl_txl_ap_inv_lns_all_b tpl1, OKL_STRM_TYPE_V sty1 where tpl1.tap_id = tap1.id and tpl1.id = tpl.id and ins.khr_id = tpl.khr_id and ins.ISS_CODE = 'ACTIVE' and ins.ipt_id = inp.id and inp.ipt_type = fnd.lookup_code and fnd.lookup_type = 'OKL_INSURANCE_PRODUCT_TYPE' and tpl.sty_id = sty1.id and sty1.stream_type_purpose in ('INSURANCE_PAYABLE', 'INSURANCE_ADJUSTMENT') ) Insurance_Product, ( select ins.POLICY_NUMBER from okl_ins_policies_b ins, okl_trx_ap_invs_all_b tap1, okl_txl_ap_inv_lns_all_b tpl1, OKL_STRM_TYPE_V sty1 where tpl1.tap_id = tap1.id and tpl1.id = tpl.id and ins.khr_id = tpl.khr_id and ins.ISS_CODE = 'ACTIVE' and tpl.sty_id = sty1.id and sty1.stream_type_purpose in ('INSURANCE_PAYABLE', 'INSURANCE_ADJUSTMENT') ) Insurance_Policy_Number, (select cur.refund_number from OKL_CURE_REFUNDS cur where cur.tap_id = tpl.tap_id) Refund_number from ap_invoices_all aph, ap_invoice_lines_all apl, okl_cnsld_ap_invs_all cns, okl_trx_ap_invs_all_b tap, okl_txl_ap_inv_lns_all_b tpl, OKL_STRM_TYPE_V sty, okc_k_headers_all_b okc where aph.invoice_id = apl.invoice_id and aph.reference_key1 = cns.cnsld_ap_inv_id and apl.reference_key1 IS NULL and apl.product_table = 'OKL_TXL_AP_INV_LNS_ALL_B' and aph.product_table = 'OKL_CNSLD_AP_INVS_ALL' and cns.cnsld_ap_inv_id = tpl.cnsld_ap_inv_id and tpl.tap_id = tap.id and tpl.sty_id = sty.id and tpl.khr_id = okc.id and aph.vendor_site_id = tap.ipvs_id

View Text - HTML Formatted

SELECT APH.INVOICE_ID
, APH.INVOICE_NUM INVOICE_NUMBER
, APL.LINE_NUMBER INVOICE_LINE_NUMBER
, INITCAP(APL.LINE_TYPE_LOOKUP_CODE) INVOICE_LINE_TYPE
, CASE TAP.FUNDING_TYPE_CODE WHEN 'ASSET' THEN 'ASSET FUNDING' WHEN 'EXPENSE' THEN 'EXPENSE FUNDING' WHEN 'SUPPLIER_RETENTION' THEN 'SUPPLIER RETENTION' WHEN 'PREFUNDING' THEN 'PRE-FUNDING' WHEN 'MANU_DISBURSEMENT' THEN 'MANUAL DISBURSEMENT' WHEN 'BORROWER_PAYMENT' THEN 'BORROWER PAYMENT' ELSE STY.STYB_PURPOSE_MEANING END DISBURSEMENT_TYPE
, STY.NAME STREAM_TYPE
, CASE TPL.INV_DISTR_LINE_CODE WHEN 'INVESTOR' THEN NULL ELSE OKC.CONTRACT_NUMBER END CONRACT_NUMBER
, (SELECT VP.CONTRACT_NUMBER VENDOR_PROGRAM
FROM OKC_K_HEADERS_ALL_B VP
, OKL_K_HEADERS OKL
WHERE OKL.ID = OKC.ID
AND OKL.TEMPLATE_TYPE_CODE = 'PROGRAM'
AND OKL.KHR_ID = VP.ID) VENDOR_PROGRAM
, CASE TPL.INV_DISTR_LINE_CODE WHEN 'INVESTOR' THEN OKC.CONTRACT_NUMBER ELSE (SELECT OKC1.CONTRACT_NUMBER
FROM OKL_POOLS_ALL POL
, OKC_K_HEADERS_ALL_B OKC1
WHERE POL.KHR_ID = OKC1.ID
AND OKC1.STS_CODE = 'ACTIVE'
AND EXISTS (SELECT 1
FROM OKL_POOL_CONTENTS POC
WHERE POC.POL_ID = POL.ID
AND POC.KHR_ID = TPL.KHR_ID) ) END INVESTOR_AGREEMENT_NUMBER
, (SELECT CLE.NAME
FROM OKC_K_LINES_V CLE
, OKL_TRX_AP_INVS_ALL_B TAP1
, OKL_TXL_AP_INV_LNS_ALL_B TPL1
WHERE TPL1.TAP_ID = TAP1.ID
AND CLE.ID = TPL1.KLE_ID
AND TPL1.ID = TPL.ID
AND TAP1.FUNDING_TYPE_CODE = 'ASSET') ASSET_NUMBER
, (SELECT CLE.NAME
FROM OKC_K_LINES_V CLE
, OKL_TRX_AP_INVS_ALL_B TAP1
, OKL_TXL_AP_INV_LNS_ALL_B TPL1
WHERE TPL1.TAP_ID = TAP1.ID
AND CLE.ID = TPL1.KLE_ID
AND TPL1.ID = TPL.ID
AND TPL1.INV_DISTR_LINE_CODE = 'AUTO_DISBURSEMENT') SERVICE_ITEM
, DECODE(TAP.FUNDING_TYPE_CODE
, NULL
, NULL
, TAP.VENDOR_INVOICE_NUMBER) FUNDING_REQUEST_NUMBER
, ( SELECT FND.MEANING
FROM OKL_INS_POLICIES_B INS
, OKL_INS_PRODUCTS_B INP
, FND_LOOKUPS FND
, OKL_TRX_AP_INVS_ALL_B TAP1
, OKL_TXL_AP_INV_LNS_ALL_B TPL1
, OKL_STRM_TYPE_V STY1
WHERE TPL1.TAP_ID = TAP1.ID
AND TPL1.ID = TPL.ID
AND INS.KHR_ID = TPL.KHR_ID
AND INS.ISS_CODE = 'ACTIVE'
AND INS.IPT_ID = INP.ID
AND INP.IPT_TYPE = FND.LOOKUP_CODE
AND FND.LOOKUP_TYPE = 'OKL_INSURANCE_PRODUCT_TYPE'
AND TPL.STY_ID = STY1.ID
AND STY1.STREAM_TYPE_PURPOSE IN ('INSURANCE_PAYABLE'
, 'INSURANCE_ADJUSTMENT') ) INSURANCE_PRODUCT
, ( SELECT INS.POLICY_NUMBER
FROM OKL_INS_POLICIES_B INS
, OKL_TRX_AP_INVS_ALL_B TAP1
, OKL_TXL_AP_INV_LNS_ALL_B TPL1
, OKL_STRM_TYPE_V STY1
WHERE TPL1.TAP_ID = TAP1.ID
AND TPL1.ID = TPL.ID
AND INS.KHR_ID = TPL.KHR_ID
AND INS.ISS_CODE = 'ACTIVE'
AND TPL.STY_ID = STY1.ID
AND STY1.STREAM_TYPE_PURPOSE IN ('INSURANCE_PAYABLE'
, 'INSURANCE_ADJUSTMENT') ) INSURANCE_POLICY_NUMBER
, (SELECT CUR.REFUND_NUMBER
FROM OKL_CURE_REFUNDS CUR
WHERE CUR.TAP_ID = TPL.TAP_ID) REFUND_NUMBER
FROM AP_INVOICES_ALL APH
, AP_INVOICE_LINES_ALL APL
, OKL_TRX_AP_INVS_ALL_B TAP
, OKL_TXL_AP_INV_LNS_ALL_B TPL
, OKL_STRM_TYPE_V STY
, OKC_K_HEADERS_ALL_B OKC
WHERE APH.INVOICE_ID = APL.INVOICE_ID
AND APL.REFERENCE_KEY1 = TPL.ID
AND APL.PRODUCT_TABLE = 'OKL_TXL_AP_INV_LNS_ALL_B'
AND TPL.TAP_ID = TAP.ID
AND TPL.STY_ID = STY.ID
AND TPL.KHR_ID = OKC.ID UNION ALL SELECT APH.INVOICE_ID
, APH.INVOICE_NUM INVOICE_NUMBER
, APL.LINE_NUMBER INVOICE_LINE_NUMBER
, INITCAP(APL.LINE_TYPE_LOOKUP_CODE) INVOICE_LINE_TYPE
, CASE TAP.FUNDING_TYPE_CODE WHEN 'ASSET' THEN 'ASSET FUNDING' WHEN 'EXPENSE' THEN 'EXPENSE FUNDING' WHEN 'SUPPLIER_RETENTION' THEN 'SUPPLIER RETENTION' WHEN 'PREFUNDING' THEN 'PRE-FUNDING' WHEN 'MANU_DISBURSEMENT' THEN 'MANUAL DISBURSEMENT' WHEN 'BORROWER_PAYMENT' THEN 'BORROWER PAYMENT' ELSE STY.STYB_PURPOSE_MEANING END DISBURSEMENT_TYPE
, STY.NAME STREAM_TYPE
, CASE TPL.INV_DISTR_LINE_CODE WHEN 'INVESTOR' THEN NULL ELSE OKC.CONTRACT_NUMBER END CONRACT_NUMBER
, (SELECT VP.CONTRACT_NUMBER VENDOR_PROGRAM
FROM OKC_K_HEADERS_ALL_B VP
, OKL_K_HEADERS OKL
WHERE OKL.ID = OKC.ID
AND OKL.TEMPLATE_TYPE_CODE = 'PROGRAM'
AND OKL.KHR_ID = VP.ID) VENDOR_PROGRAM
, CASE TPL.INV_DISTR_LINE_CODE WHEN 'INVESTOR' THEN OKC.CONTRACT_NUMBER ELSE (SELECT OKC1.CONTRACT_NUMBER
FROM OKL_POOLS_ALL POL
, OKC_K_HEADERS_ALL_B OKC1
WHERE POL.KHR_ID = OKC1.ID
AND OKC1.STS_CODE = 'ACTIVE'
AND EXISTS (SELECT 1
FROM OKL_POOL_CONTENTS POC
WHERE POC.POL_ID = POL.ID
AND POC.KHR_ID = TPL.KHR_ID) ) END INVESTOR_AGREEMENT_NUMBER
, (SELECT CLE.NAME
FROM OKC_K_LINES_V CLE
, OKL_TRX_AP_INVS_ALL_B TAP1
, OKL_TXL_AP_INV_LNS_ALL_B TPL1
WHERE TPL1.TAP_ID = TAP1.ID
AND CLE.ID = TPL1.KLE_ID
AND TPL1.ID = TPL.ID
AND TAP1.FUNDING_TYPE_CODE = 'ASSET') ASSET_NUMBER
, (SELECT CLE.NAME
FROM OKC_K_LINES_V CLE
, OKL_TRX_AP_INVS_ALL_B TAP1
, OKL_TXL_AP_INV_LNS_ALL_B TPL1
WHERE TPL1.TAP_ID = TAP1.ID
AND CLE.ID = TPL1.KLE_ID
AND TPL1.ID = TPL.ID
AND TPL1.INV_DISTR_LINE_CODE = 'AUTO_DISBURSEMENT') SERVICE_ITEM
, DECODE(TAP.FUNDING_TYPE_CODE
, NULL
, NULL
, TAP.VENDOR_INVOICE_NUMBER) FUNDING_REQUEST_NUMBER
, ( SELECT FND.MEANING
FROM OKL_INS_POLICIES_B INS
, OKL_INS_PRODUCTS_B INP
, FND_LOOKUPS FND
, OKL_TRX_AP_INVS_ALL_B TAP1
, OKL_TXL_AP_INV_LNS_ALL_B TPL1
, OKL_STRM_TYPE_V STY1
WHERE TPL1.TAP_ID = TAP1.ID
AND TPL1.ID = TPL.ID
AND INS.KHR_ID = TPL.KHR_ID
AND INS.ISS_CODE = 'ACTIVE'
AND INS.IPT_ID = INP.ID
AND INP.IPT_TYPE = FND.LOOKUP_CODE
AND FND.LOOKUP_TYPE = 'OKL_INSURANCE_PRODUCT_TYPE'
AND TPL.STY_ID = STY1.ID
AND STY1.STREAM_TYPE_PURPOSE IN ('INSURANCE_PAYABLE'
, 'INSURANCE_ADJUSTMENT') ) INSURANCE_PRODUCT
, ( SELECT INS.POLICY_NUMBER
FROM OKL_INS_POLICIES_B INS
, OKL_TRX_AP_INVS_ALL_B TAP1
, OKL_TXL_AP_INV_LNS_ALL_B TPL1
, OKL_STRM_TYPE_V STY1
WHERE TPL1.TAP_ID = TAP1.ID
AND TPL1.ID = TPL.ID
AND INS.KHR_ID = TPL.KHR_ID
AND INS.ISS_CODE = 'ACTIVE'
AND TPL.STY_ID = STY1.ID
AND STY1.STREAM_TYPE_PURPOSE IN ('INSURANCE_PAYABLE'
, 'INSURANCE_ADJUSTMENT') ) INSURANCE_POLICY_NUMBER
, (SELECT CUR.REFUND_NUMBER
FROM OKL_CURE_REFUNDS CUR
WHERE CUR.TAP_ID = TPL.TAP_ID) REFUND_NUMBER
FROM AP_INVOICES_ALL APH
, AP_INVOICE_LINES_ALL APL
, OKL_CNSLD_AP_INVS_ALL CNS
, OKL_TRX_AP_INVS_ALL_B TAP
, OKL_TXL_AP_INV_LNS_ALL_B TPL
, OKL_STRM_TYPE_V STY
, OKC_K_HEADERS_ALL_B OKC
WHERE APH.INVOICE_ID = APL.INVOICE_ID
AND APH.REFERENCE_KEY1 = CNS.CNSLD_AP_INV_ID
AND APL.REFERENCE_KEY1 IS NULL
AND APL.PRODUCT_TABLE = 'OKL_TXL_AP_INV_LNS_ALL_B'
AND APH.PRODUCT_TABLE = 'OKL_CNSLD_AP_INVS_ALL'
AND CNS.CNSLD_AP_INV_ID = TPL.CNSLD_AP_INV_ID
AND TPL.TAP_ID = TAP.ID
AND TPL.STY_ID = STY.ID
AND TPL.KHR_ID = OKC.ID
AND APH.VENDOR_SITE_ID = TAP.IPVS_ID