VIEW: APPS.OKL_CS_DISBURSEMENTS_UV

Source

View Text - Preformatted

SELECT CHR.contract_number contract_number, faa.asset_number asset_number, pov.vendor_name vendor_name, styt.name transaction_type, cin.vendor_invoice_number vendor_invoice_number, cin.date_invoiced invoice_date, tpl.amount disbursement_amount, api.amount_paid amount_paid, apc.payment_method_lookup_code payment_method, apc.check_number check_number, apc.check_date check_date, apc.amount check_amount, apc.currency_code check_currency, cin.cnsld_ap_inv_id cnsld_inv_id, tpl.khr_id khr_id, faa.asset_id asset_id, styt.id sty_id, pov.vendor_id vendor_id, apc.check_id check_id, cin.org_id org_id, cin.currency_code, FND_TYPE.MEANING INVOICE_TYPE_MEANING FROM OKL_CNSLD_AP_INVS_ALL CIN,OKL_TXL_AP_INV_LNS_ALL_B TPL,OKL_STRM_TYPE_TL STYT,OKC_K_HEADERS_B CHR,OKC_K_LINES_B CLE,OKC_LINE_STYLES_B LSE,OKC_K_ITEMS CIM,FA_ADDITIONS_B FAA,AP_INVOICES_ALL API,PO_VENDORS POV,AP_INVOICE_PAYMENTS_ALL APP,AP_CHECKS_ALL APC,FND_APPLICATION FND,FND_LOOKUPS FND_TYPE WHERE cin.cnsld_ap_inv_id = tpl.cnsld_ap_inv_id AND tpl.kle_id IS NOT NULL AND tpl.kle_id = cle.cle_id AND cle.lse_id = lse.id AND lse.lty_code = 'FIXED_ASSET' AND cle.id = cim.cle_id AND cim.jtot_object1_code = 'OKX_ASSET' AND cim.object1_id1 = faa.asset_id AND tpl.sty_id = styt.id(+) AND styt.LANGUAGE(+) = userenv('LANG') AND tpl.khr_id = CHR.id AND nvl(cin.org_id, -99) = nvl(cin.org_id, -99) AND api.invoice_num = cin.vendor_invoice_number AND api.vendor_id = pov.vendor_id AND api.invoice_id = app.invoice_id(+) AND app.check_id = apc.check_id(+) AND cin.cnsld_ap_inv_id = to_number(api.reference_key1) AND api.application_id = fnd.application_id AND fnd.application_short_name = 'OKL' AND api.product_table = 'OKL_CNSLD_AP_INVS_ALL' AND FND_TYPE.LOOKUP_TYPE(+) = 'OKL_PAYABLES_INVOICE_TYPE' AND FND_TYPE.LOOKUP_CODE(+) = CIN.INVOICE_TYPE UNION SELECT CHR.contract_number contract_number, NULL asset_number, pov.vendor_name vendor_name, styt.name transaction_type, cin.vendor_invoice_number vendor_invoice_number, cin.date_invoiced invoice_date, tpl.amount disbursement_amount, api.amount_paid, apc.payment_method_lookup_code payment_method, apc.check_number check_number, apc.check_date check_date, apc.amount check_amount, apc.currency_code check_currency, cin.cnsld_ap_inv_id cnsld_inv_id, tpl.khr_id khr_id, to_number(NULL) asset_id, styt.id sty_id, pov.vendor_id vendor_id, apc.check_id check_id, cin.org_id org_id, cin.currency_code, FND_TYPE.MEANING INVOICE_TYPE_MEANING FROM okl_cnsld_ap_invs_all cin, okl_txl_ap_inv_lns_all_b tpl, okl_strm_type_tl styt, okc_k_headers_b CHR, ap_invoices_all api, po_vendors pov, ap_invoice_payments_all app, ap_checks_all apc, fnd_application fnd, FND_LOOKUPS FND_TYPE WHERE cin.cnsld_ap_inv_id = tpl.cnsld_ap_inv_id AND tpl.kle_id IS NULL AND tpl.sty_id = styt.id(+) AND styt.LANGUAGE(+) = userenv('LANG') AND tpl.khr_id = CHR.id AND nvl(cin.org_id, -99) = nvl(api.org_id, -99) AND api.invoice_num = cin.vendor_invoice_number AND api.vendor_id = pov.vendor_id AND api.invoice_id = app.invoice_id(+) AND app.check_id = apc.check_id(+) AND cin.cnsld_ap_inv_id = to_number(api.reference_key1) AND api.application_id = fnd.application_id AND fnd.application_short_name = 'OKL' AND api.product_table = 'OKL_CNSLD_AP_INVS_ALL' AND FND_TYPE.LOOKUP_TYPE(+) = 'OKL_PAYABLES_INVOICE_TYPE' AND FND_TYPE.LOOKUP_CODE(+) = CIN.INVOICE_TYPE UNION SELECT CHR.contract_number contract_number, clt.name asset_number, pov.vendor_name vendor_name, styt.name transaction_type, cin.vendor_invoice_number vendor_invoice_number, cin.date_invoiced invoice_date, tpl.amount disbursement_amount, api.amount_paid, apc.payment_method_lookup_code payment_method, apc.check_number check_number, apc.check_date check_date, apc.amount check_amount, apc.currency_code check_currency, cin.cnsld_ap_inv_id cnsld_inv_id, tpl.khr_id khr_id, to_number(NULL) asset_id, styt.id sty_id, pov.vendor_id vendor_id, apc.check_id check_id, cin.org_id org_id, cin.currency_code, FND_TYPE.MEANING INVOICE_TYPE_MEANING FROM okl_cnsld_ap_invs_all cin, okl_txl_ap_inv_lns_all_b tpl, okl_strm_type_tl styt, okc_k_headers_b CHR, okl_k_headers chl, okc_k_lines_b cle, okc_line_styles_b lse, okc_k_lines_tl clt, ap_invoices_all api, po_vendors pov, ap_invoice_payments_all app, ap_checks_all apc, fnd_application fnd, FND_LOOKUPS FND_TYPE WHERE cin.cnsld_ap_inv_id = tpl.cnsld_ap_inv_id AND tpl.kle_id IS NOT NULL AND tpl.kle_id = cle.id AND tpl.sty_id = styt.id(+) AND styt.LANGUAGE(+) = userenv('LANG') AND tpl.khr_id = CHR.id AND CHR.id = chl.id AND chl.deal_type = 'LOAN' AND cle.lse_id = lse.id AND lse.lty_code = 'FREE_FORM1' AND cle.id = clt.id AND clt.LANGUAGE(+) = userenv('LANG') AND nvl(cin.org_id, -99) = nvl(api.org_id, -99) AND api.invoice_num = cin.vendor_invoice_number AND api.vendor_id = pov.vendor_id AND api.invoice_id = app.invoice_id(+) AND app.check_id = apc.check_id(+) AND cin.cnsld_ap_inv_id = to_number(api.reference_key1) AND api.application_id = fnd.application_id AND fnd.application_short_name = 'OKL' AND api.product_table = 'OKL_CNSLD_AP_INVS_ALL' AND FND_TYPE.LOOKUP_TYPE(+) = 'OKL_PAYABLES_INVOICE_TYPE' AND FND_TYPE.LOOKUP_CODE(+) = CIN.INVOICE_TYPE UNION SELECT CHR.contract_number contract_number, cle.name asset_number, pov.vendor_name vendor_name, styt.name transaction_type, cin.vendor_invoice_number vendor_invoice_number, cin.date_invoiced invoice_date, tpl.amount disbursement_amount, api.amount_paid, apc.payment_method_lookup_code payment_method, apc.check_number check_number, apc.check_date check_date, apc.amount check_amount, apc.currency_code check_currency, cin.cnsld_ap_inv_id cnsld_inv_id, tpl.khr_id khr_id, to_number(NULL) asset_id, styt.id sty_id, pov.vendor_id vendor_id, apc.check_id check_id, cin.org_id org_id, cin.currency_code, FND_TYPE.MEANING INVOICE_TYPE_MEANING FROM okl_cnsld_ap_invs_all cin, okl_txl_ap_inv_lns_all_b tpl, okl_strm_type_tl styt, okc_k_headers_b CHR, okc_k_lines_v cle, ap_invoices_all api, po_vendors pov, ap_invoice_payments_all app, ap_checks_all apc, fnd_application fnd, FND_LOOKUPS FND_TYPE WHERE cin.cnsld_ap_inv_id = tpl.cnsld_ap_inv_id AND tpl.kle_id IS NOT NULL AND tpl.sty_id = styt.id(+) AND styt.LANGUAGE(+) = userenv('LANG') AND tpl.khr_id = CHR.id AND nvl(cin.org_id, -99) = nvl(api.org_id, -99) AND cin.vendor_invoice_number = api.invoice_num AND api.vendor_id = pov.vendor_id AND api.invoice_id = app.invoice_id(+) AND app.check_id = apc.check_id(+) AND NOT EXISTS (SELECT 1 FROM okc_k_lines_b cle2, okc_line_styles_b lse WHERE cle2.cle_id = tpl.kle_id AND cle2.lse_id = lse.id AND lse.lty_code = 'FIXED_ASSET') AND tpl.kle_id = cle.id AND cin.cnsld_ap_inv_id = to_number(api.reference_key1) AND api.application_id = fnd.application_id AND fnd.application_short_name = 'OKL' AND api.product_table = 'OKL_CNSLD_AP_INVS_ALL' AND FND_TYPE.LOOKUP_TYPE(+) = 'OKL_PAYABLES_INVOICE_TYPE' AND FND_TYPE.LOOKUP_CODE(+) = CIN.INVOICE_TYPE UNION SELECT CHR.contract_number contract_number, clt.name asset_number, pov.vendor_name vendor_name, NULL transaction_type, cin.vendor_invoice_number vendor_invoice_number, cin.date_invoiced invoice_date, tpl.amount disbursement_amount, api.amount_paid, apc.payment_method_lookup_code payment_method, apc.check_number check_number, apc.check_date check_date, apc.amount check_amount, apc.currency_code check_currency, cin.cnsld_ap_inv_id cnsld_inv_id, tpl.khr_id khr_id, to_number(NULL) asset_id, to_number(NULL) sty_id, pov.vendor_id vendor_id, apc.check_id check_id, cin.org_id org_id, cin.currency_code, FND_TYPE.MEANING INVOICE_TYPE_MEANING FROM okl_cnsld_ap_invs_all cin, okl_txl_ap_inv_lns_all_b tpl, okc_k_headers_b CHR, okc_k_lines_b cle, okc_line_styles_b lse, okc_k_lines_tl clt, ap_invoices_all api, po_vendors pov, ap_invoice_payments_all app, ap_checks_all apc, fnd_application fnd, FND_LOOKUPS FND_TYPE WHERE cin.cnsld_ap_inv_id = tpl.cnsld_ap_inv_id AND tpl.kle_id IS NOT NULL AND tpl.funding_reference_type_code = 'ASSET' AND tpl.khr_id = CHR.id AND tpl.kle_id = cle.id AND cle.lse_id = lse.id AND lse.lty_code = 'FREE_FORM1' AND cle.id = clt.id AND clt.LANGUAGE = userenv('LANG') AND nvl(cin.org_id, -99) = nvl(api.org_id, -99) AND cin.vendor_invoice_number = api.invoice_num AND api.vendor_id = pov.vendor_id AND api.invoice_id = app.invoice_id(+) AND app.check_id = apc.check_id(+) AND NOT EXISTS (SELECT 1 FROM okc_k_lines_b cle2, okc_line_styles_b lse2, okc_k_items cim, fa_additions_b faa WHERE tpl.kle_id = cle2.cle_id AND cle2.lse_id = lse2.id AND lse2.lty_code = 'FIXED_ASSET' AND cle2.id = cim.cle_id AND cim.jtot_object1_code = 'OKX_ASSET' AND cim.object1_id1 = faa.asset_id) AND cin.cnsld_ap_inv_id = to_number(api.reference_key1) AND api.application_id = fnd.application_id AND fnd.application_short_name = 'OKL' AND api.product_table = 'OKL_CNSLD_AP_INVS_ALL' AND FND_TYPE.LOOKUP_TYPE(+) = 'OKL_PAYABLES_INVOICE_TYPE' AND FND_TYPE.LOOKUP_CODE(+) = CIN.INVOICE_TYPE

View Text - HTML Formatted

SELECT CHR.CONTRACT_NUMBER CONTRACT_NUMBER
, FAA.ASSET_NUMBER ASSET_NUMBER
, POV.VENDOR_NAME VENDOR_NAME
, STYT.NAME TRANSACTION_TYPE
, CIN.VENDOR_INVOICE_NUMBER VENDOR_INVOICE_NUMBER
, CIN.DATE_INVOICED INVOICE_DATE
, TPL.AMOUNT DISBURSEMENT_AMOUNT
, API.AMOUNT_PAID AMOUNT_PAID
, APC.PAYMENT_METHOD_LOOKUP_CODE PAYMENT_METHOD
, APC.CHECK_NUMBER CHECK_NUMBER
, APC.CHECK_DATE CHECK_DATE
, APC.AMOUNT CHECK_AMOUNT
, APC.CURRENCY_CODE CHECK_CURRENCY
, CIN.CNSLD_AP_INV_ID CNSLD_INV_ID
, TPL.KHR_ID KHR_ID
, FAA.ASSET_ID ASSET_ID
, STYT.ID STY_ID
, POV.VENDOR_ID VENDOR_ID
, APC.CHECK_ID CHECK_ID
, CIN.ORG_ID ORG_ID
, CIN.CURRENCY_CODE
, FND_TYPE.MEANING INVOICE_TYPE_MEANING
FROM OKL_CNSLD_AP_INVS_ALL CIN
, OKL_TXL_AP_INV_LNS_ALL_B TPL
, OKL_STRM_TYPE_TL STYT
, OKC_K_HEADERS_B CHR
, OKC_K_LINES_B CLE
, OKC_LINE_STYLES_B LSE
, OKC_K_ITEMS CIM
, FA_ADDITIONS_B FAA
, AP_INVOICES_ALL API
, PO_VENDORS POV
, AP_INVOICE_PAYMENTS_ALL APP
, AP_CHECKS_ALL APC
, FND_APPLICATION FND
, FND_LOOKUPS FND_TYPE
WHERE CIN.CNSLD_AP_INV_ID = TPL.CNSLD_AP_INV_ID
AND TPL.KLE_ID IS NOT NULL
AND TPL.KLE_ID = CLE.CLE_ID
AND CLE.LSE_ID = LSE.ID
AND LSE.LTY_CODE = 'FIXED_ASSET'
AND CLE.ID = CIM.CLE_ID
AND CIM.JTOT_OBJECT1_CODE = 'OKX_ASSET'
AND CIM.OBJECT1_ID1 = FAA.ASSET_ID
AND TPL.STY_ID = STYT.ID(+)
AND STYT.LANGUAGE(+) = USERENV('LANG')
AND TPL.KHR_ID = CHR.ID
AND NVL(CIN.ORG_ID
, -99) = NVL(CIN.ORG_ID
, -99)
AND API.INVOICE_NUM = CIN.VENDOR_INVOICE_NUMBER
AND API.VENDOR_ID = POV.VENDOR_ID
AND API.INVOICE_ID = APP.INVOICE_ID(+)
AND APP.CHECK_ID = APC.CHECK_ID(+)
AND CIN.CNSLD_AP_INV_ID = TO_NUMBER(API.REFERENCE_KEY1)
AND API.APPLICATION_ID = FND.APPLICATION_ID
AND FND.APPLICATION_SHORT_NAME = 'OKL'
AND API.PRODUCT_TABLE = 'OKL_CNSLD_AP_INVS_ALL'
AND FND_TYPE.LOOKUP_TYPE(+) = 'OKL_PAYABLES_INVOICE_TYPE'
AND FND_TYPE.LOOKUP_CODE(+) = CIN.INVOICE_TYPE UNION SELECT CHR.CONTRACT_NUMBER CONTRACT_NUMBER
, NULL ASSET_NUMBER
, POV.VENDOR_NAME VENDOR_NAME
, STYT.NAME TRANSACTION_TYPE
, CIN.VENDOR_INVOICE_NUMBER VENDOR_INVOICE_NUMBER
, CIN.DATE_INVOICED INVOICE_DATE
, TPL.AMOUNT DISBURSEMENT_AMOUNT
, API.AMOUNT_PAID
, APC.PAYMENT_METHOD_LOOKUP_CODE PAYMENT_METHOD
, APC.CHECK_NUMBER CHECK_NUMBER
, APC.CHECK_DATE CHECK_DATE
, APC.AMOUNT CHECK_AMOUNT
, APC.CURRENCY_CODE CHECK_CURRENCY
, CIN.CNSLD_AP_INV_ID CNSLD_INV_ID
, TPL.KHR_ID KHR_ID
, TO_NUMBER(NULL) ASSET_ID
, STYT.ID STY_ID
, POV.VENDOR_ID VENDOR_ID
, APC.CHECK_ID CHECK_ID
, CIN.ORG_ID ORG_ID
, CIN.CURRENCY_CODE
, FND_TYPE.MEANING INVOICE_TYPE_MEANING
FROM OKL_CNSLD_AP_INVS_ALL CIN
, OKL_TXL_AP_INV_LNS_ALL_B TPL
, OKL_STRM_TYPE_TL STYT
, OKC_K_HEADERS_B CHR
, AP_INVOICES_ALL API
, PO_VENDORS POV
, AP_INVOICE_PAYMENTS_ALL APP
, AP_CHECKS_ALL APC
, FND_APPLICATION FND
, FND_LOOKUPS FND_TYPE
WHERE CIN.CNSLD_AP_INV_ID = TPL.CNSLD_AP_INV_ID
AND TPL.KLE_ID IS NULL
AND TPL.STY_ID = STYT.ID(+)
AND STYT.LANGUAGE(+) = USERENV('LANG')
AND TPL.KHR_ID = CHR.ID
AND NVL(CIN.ORG_ID
, -99) = NVL(API.ORG_ID
, -99)
AND API.INVOICE_NUM = CIN.VENDOR_INVOICE_NUMBER
AND API.VENDOR_ID = POV.VENDOR_ID
AND API.INVOICE_ID = APP.INVOICE_ID(+)
AND APP.CHECK_ID = APC.CHECK_ID(+)
AND CIN.CNSLD_AP_INV_ID = TO_NUMBER(API.REFERENCE_KEY1)
AND API.APPLICATION_ID = FND.APPLICATION_ID
AND FND.APPLICATION_SHORT_NAME = 'OKL'
AND API.PRODUCT_TABLE = 'OKL_CNSLD_AP_INVS_ALL'
AND FND_TYPE.LOOKUP_TYPE(+) = 'OKL_PAYABLES_INVOICE_TYPE'
AND FND_TYPE.LOOKUP_CODE(+) = CIN.INVOICE_TYPE UNION SELECT CHR.CONTRACT_NUMBER CONTRACT_NUMBER
, CLT.NAME ASSET_NUMBER
, POV.VENDOR_NAME VENDOR_NAME
, STYT.NAME TRANSACTION_TYPE
, CIN.VENDOR_INVOICE_NUMBER VENDOR_INVOICE_NUMBER
, CIN.DATE_INVOICED INVOICE_DATE
, TPL.AMOUNT DISBURSEMENT_AMOUNT
, API.AMOUNT_PAID
, APC.PAYMENT_METHOD_LOOKUP_CODE PAYMENT_METHOD
, APC.CHECK_NUMBER CHECK_NUMBER
, APC.CHECK_DATE CHECK_DATE
, APC.AMOUNT CHECK_AMOUNT
, APC.CURRENCY_CODE CHECK_CURRENCY
, CIN.CNSLD_AP_INV_ID CNSLD_INV_ID
, TPL.KHR_ID KHR_ID
, TO_NUMBER(NULL) ASSET_ID
, STYT.ID STY_ID
, POV.VENDOR_ID VENDOR_ID
, APC.CHECK_ID CHECK_ID
, CIN.ORG_ID ORG_ID
, CIN.CURRENCY_CODE
, FND_TYPE.MEANING INVOICE_TYPE_MEANING
FROM OKL_CNSLD_AP_INVS_ALL CIN
, OKL_TXL_AP_INV_LNS_ALL_B TPL
, OKL_STRM_TYPE_TL STYT
, OKC_K_HEADERS_B CHR
, OKL_K_HEADERS CHL
, OKC_K_LINES_B CLE
, OKC_LINE_STYLES_B LSE
, OKC_K_LINES_TL CLT
, AP_INVOICES_ALL API
, PO_VENDORS POV
, AP_INVOICE_PAYMENTS_ALL APP
, AP_CHECKS_ALL APC
, FND_APPLICATION FND
, FND_LOOKUPS FND_TYPE
WHERE CIN.CNSLD_AP_INV_ID = TPL.CNSLD_AP_INV_ID
AND TPL.KLE_ID IS NOT NULL
AND TPL.KLE_ID = CLE.ID
AND TPL.STY_ID = STYT.ID(+)
AND STYT.LANGUAGE(+) = USERENV('LANG')
AND TPL.KHR_ID = CHR.ID
AND CHR.ID = CHL.ID
AND CHL.DEAL_TYPE = 'LOAN'
AND CLE.LSE_ID = LSE.ID
AND LSE.LTY_CODE = 'FREE_FORM1'
AND CLE.ID = CLT.ID
AND CLT.LANGUAGE(+) = USERENV('LANG')
AND NVL(CIN.ORG_ID
, -99) = NVL(API.ORG_ID
, -99)
AND API.INVOICE_NUM = CIN.VENDOR_INVOICE_NUMBER
AND API.VENDOR_ID = POV.VENDOR_ID
AND API.INVOICE_ID = APP.INVOICE_ID(+)
AND APP.CHECK_ID = APC.CHECK_ID(+)
AND CIN.CNSLD_AP_INV_ID = TO_NUMBER(API.REFERENCE_KEY1)
AND API.APPLICATION_ID = FND.APPLICATION_ID
AND FND.APPLICATION_SHORT_NAME = 'OKL'
AND API.PRODUCT_TABLE = 'OKL_CNSLD_AP_INVS_ALL'
AND FND_TYPE.LOOKUP_TYPE(+) = 'OKL_PAYABLES_INVOICE_TYPE'
AND FND_TYPE.LOOKUP_CODE(+) = CIN.INVOICE_TYPE UNION SELECT CHR.CONTRACT_NUMBER CONTRACT_NUMBER
, CLE.NAME ASSET_NUMBER
, POV.VENDOR_NAME VENDOR_NAME
, STYT.NAME TRANSACTION_TYPE
, CIN.VENDOR_INVOICE_NUMBER VENDOR_INVOICE_NUMBER
, CIN.DATE_INVOICED INVOICE_DATE
, TPL.AMOUNT DISBURSEMENT_AMOUNT
, API.AMOUNT_PAID
, APC.PAYMENT_METHOD_LOOKUP_CODE PAYMENT_METHOD
, APC.CHECK_NUMBER CHECK_NUMBER
, APC.CHECK_DATE CHECK_DATE
, APC.AMOUNT CHECK_AMOUNT
, APC.CURRENCY_CODE CHECK_CURRENCY
, CIN.CNSLD_AP_INV_ID CNSLD_INV_ID
, TPL.KHR_ID KHR_ID
, TO_NUMBER(NULL) ASSET_ID
, STYT.ID STY_ID
, POV.VENDOR_ID VENDOR_ID
, APC.CHECK_ID CHECK_ID
, CIN.ORG_ID ORG_ID
, CIN.CURRENCY_CODE
, FND_TYPE.MEANING INVOICE_TYPE_MEANING
FROM OKL_CNSLD_AP_INVS_ALL CIN
, OKL_TXL_AP_INV_LNS_ALL_B TPL
, OKL_STRM_TYPE_TL STYT
, OKC_K_HEADERS_B CHR
, OKC_K_LINES_V CLE
, AP_INVOICES_ALL API
, PO_VENDORS POV
, AP_INVOICE_PAYMENTS_ALL APP
, AP_CHECKS_ALL APC
, FND_APPLICATION FND
, FND_LOOKUPS FND_TYPE
WHERE CIN.CNSLD_AP_INV_ID = TPL.CNSLD_AP_INV_ID
AND TPL.KLE_ID IS NOT NULL
AND TPL.STY_ID = STYT.ID(+)
AND STYT.LANGUAGE(+) = USERENV('LANG')
AND TPL.KHR_ID = CHR.ID
AND NVL(CIN.ORG_ID
, -99) = NVL(API.ORG_ID
, -99)
AND CIN.VENDOR_INVOICE_NUMBER = API.INVOICE_NUM
AND API.VENDOR_ID = POV.VENDOR_ID
AND API.INVOICE_ID = APP.INVOICE_ID(+)
AND APP.CHECK_ID = APC.CHECK_ID(+)
AND NOT EXISTS (SELECT 1
FROM OKC_K_LINES_B CLE2
, OKC_LINE_STYLES_B LSE
WHERE CLE2.CLE_ID = TPL.KLE_ID
AND CLE2.LSE_ID = LSE.ID
AND LSE.LTY_CODE = 'FIXED_ASSET')
AND TPL.KLE_ID = CLE.ID
AND CIN.CNSLD_AP_INV_ID = TO_NUMBER(API.REFERENCE_KEY1)
AND API.APPLICATION_ID = FND.APPLICATION_ID
AND FND.APPLICATION_SHORT_NAME = 'OKL'
AND API.PRODUCT_TABLE = 'OKL_CNSLD_AP_INVS_ALL'
AND FND_TYPE.LOOKUP_TYPE(+) = 'OKL_PAYABLES_INVOICE_TYPE'
AND FND_TYPE.LOOKUP_CODE(+) = CIN.INVOICE_TYPE UNION SELECT CHR.CONTRACT_NUMBER CONTRACT_NUMBER
, CLT.NAME ASSET_NUMBER
, POV.VENDOR_NAME VENDOR_NAME
, NULL TRANSACTION_TYPE
, CIN.VENDOR_INVOICE_NUMBER VENDOR_INVOICE_NUMBER
, CIN.DATE_INVOICED INVOICE_DATE
, TPL.AMOUNT DISBURSEMENT_AMOUNT
, API.AMOUNT_PAID
, APC.PAYMENT_METHOD_LOOKUP_CODE PAYMENT_METHOD
, APC.CHECK_NUMBER CHECK_NUMBER
, APC.CHECK_DATE CHECK_DATE
, APC.AMOUNT CHECK_AMOUNT
, APC.CURRENCY_CODE CHECK_CURRENCY
, CIN.CNSLD_AP_INV_ID CNSLD_INV_ID
, TPL.KHR_ID KHR_ID
, TO_NUMBER(NULL) ASSET_ID
, TO_NUMBER(NULL) STY_ID
, POV.VENDOR_ID VENDOR_ID
, APC.CHECK_ID CHECK_ID
, CIN.ORG_ID ORG_ID
, CIN.CURRENCY_CODE
, FND_TYPE.MEANING INVOICE_TYPE_MEANING
FROM OKL_CNSLD_AP_INVS_ALL CIN
, OKL_TXL_AP_INV_LNS_ALL_B TPL
, OKC_K_HEADERS_B CHR
, OKC_K_LINES_B CLE
, OKC_LINE_STYLES_B LSE
, OKC_K_LINES_TL CLT
, AP_INVOICES_ALL API
, PO_VENDORS POV
, AP_INVOICE_PAYMENTS_ALL APP
, AP_CHECKS_ALL APC
, FND_APPLICATION FND
, FND_LOOKUPS FND_TYPE
WHERE CIN.CNSLD_AP_INV_ID = TPL.CNSLD_AP_INV_ID
AND TPL.KLE_ID IS NOT NULL
AND TPL.FUNDING_REFERENCE_TYPE_CODE = 'ASSET'
AND TPL.KHR_ID = CHR.ID
AND TPL.KLE_ID = CLE.ID
AND CLE.LSE_ID = LSE.ID
AND LSE.LTY_CODE = 'FREE_FORM1'
AND CLE.ID = CLT.ID
AND CLT.LANGUAGE = USERENV('LANG')
AND NVL(CIN.ORG_ID
, -99) = NVL(API.ORG_ID
, -99)
AND CIN.VENDOR_INVOICE_NUMBER = API.INVOICE_NUM
AND API.VENDOR_ID = POV.VENDOR_ID
AND API.INVOICE_ID = APP.INVOICE_ID(+)
AND APP.CHECK_ID = APC.CHECK_ID(+)
AND NOT EXISTS (SELECT 1
FROM OKC_K_LINES_B CLE2
, OKC_LINE_STYLES_B LSE2
, OKC_K_ITEMS CIM
, FA_ADDITIONS_B FAA
WHERE TPL.KLE_ID = CLE2.CLE_ID
AND CLE2.LSE_ID = LSE2.ID
AND LSE2.LTY_CODE = 'FIXED_ASSET'
AND CLE2.ID = CIM.CLE_ID
AND CIM.JTOT_OBJECT1_CODE = 'OKX_ASSET'
AND CIM.OBJECT1_ID1 = FAA.ASSET_ID)
AND CIN.CNSLD_AP_INV_ID = TO_NUMBER(API.REFERENCE_KEY1)
AND API.APPLICATION_ID = FND.APPLICATION_ID
AND FND.APPLICATION_SHORT_NAME = 'OKL'
AND API.PRODUCT_TABLE = 'OKL_CNSLD_AP_INVS_ALL'
AND FND_TYPE.LOOKUP_TYPE(+) = 'OKL_PAYABLES_INVOICE_TYPE'
AND FND_TYPE.LOOKUP_CODE(+) = CIN.INVOICE_TYPE