VIEW: APPS.OKL_AP_TRX_LINE_UV

Source

View Text - Preformatted

SELECT TO_CHAR(AI.INVOICE_ID) ID, 1 LINE_NUMBER, AI.DESCRIPTION DESCRIPTION, AI.INVOICE_AMOUNT AMOUNT, AI.INVOICE_CURRENCY_CODE CURRENCY_CODE, NULL STREAM_TYPE, NULL STREAM_TYPE_PURPOSE, AI.INVOICE_ID TRANSACTION_ID, 'AP_INVOICES' SOURCE_TABLE, -1 CHECK_ID, TPL.KHR_ID KHR_ID , TO_CHAR(AI.INVOICE_ID) INVOICE_ID FROM AP_INVOICES AI,OKL_TRX_AP_INVS_ALL_B TAP,OKL_TXL_AP_INV_LNS_B TPL,OKL_CNSLD_AP_INVS_ALL CNSLD,FND_APPLICATION APP WHERE TAP.VENDOR_INVOICE_NUMBER = AI.INVOICE_NUM AND TAP.VENDOR_ID = AI.VENDOR_ID AND TAP.ID = TPL.TAP_ID AND TPL.CNSLD_AP_INV_ID = CNSLD.CNSLD_AP_INV_ID AND AI.APPLICATION_ID = APP.APPLICATION_ID AND APP.APPLICATION_SHORT_NAME = 'OKL' AND AI.PRODUCT_TABLE = 'OKL_CNSLD_AP_INVS_ALL' AND CNSLD.CNSLD_AP_INV_ID = TO_NUMBER(AI.REFERENCE_KEY1) UNION ALL SELECT TO_CHAR(AC.CHECK_NUMBER) ID, 1 LINE_NUMBER, BB.BANK_NAME || ' - ' || BB.BANK_BRANCH_NAME || ' - ' || BA.BANK_ACCOUNT_NAME || ' - ' || BA.BANK_ACCOUNT_NUM || ' - ' || OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('PAYMENT METHOD', AC.PAYMENT_METHOD_LOOKUP_CODE, 200, 200) DESCRIPTION, AIP.AMOUNT AMOUNT, AC.CURRENCY_CODE CURRENCY_CODE, NULL STREAM_TYPE, NULL STREAM_TYPE_PURPOSE, AC.CHECK_NUMBER TRANSACTION_ID, 'AP_CHECKS' SOURCE_TABLE, AC.CHECK_ID CHECK_ID, TPL.KHR_ID KHR_ID, TO_CHAR(AIP.INVOICE_ID) INVOICE_ID FROM OKL_TRX_AP_INVS_ALL_B TAP, AP_INVOICES AI, AP_INVOICE_PAYMENTS_ALL AIP, AP_CHECKS_ALL AC, CE_BANK_ACCOUNTS BA, CE_BANK_ACCT_USES_ALL BAU, CE_BANK_BRANCHES_V BB, OKL_TXL_AP_INV_LNS_B TPL, OKL_CNSLD_AP_INVS_ALL CNSLD, FND_APPLICATION APP WHERE tap.vendor_invoice_number = ai.invoice_num AND tap.vendor_id = ai.vendor_id AND ai.invoice_id = aip.invoice_id AND aip.check_id = ac.check_id AND AC.CE_BANK_ACCT_USE_ID = BAU.BANK_ACCT_USE_ID AND BA.BANK_ACCOUNT_ID = BAU.BANK_ACCOUNT_ID AND BA.BANK_BRANCH_ID = BB.BRANCH_PARTY_ID AND TAP.ID = TPL.TAP_ID AND TPL.CNSLD_AP_INV_ID = CNSLD.CNSLD_AP_INV_ID AND AI.APPLICATION_ID = APP.APPLICATION_ID AND APP.APPLICATION_SHORT_NAME = 'OKL' AND AI.PRODUCT_TABLE = 'OKL_CNSLD_AP_INVS_ALL' AND CNSLD.CNSLD_AP_INV_ID = TO_NUMBER(AI.REFERENCE_KEY1)

View Text - HTML Formatted

SELECT TO_CHAR(AI.INVOICE_ID) ID
, 1 LINE_NUMBER
, AI.DESCRIPTION DESCRIPTION
, AI.INVOICE_AMOUNT AMOUNT
, AI.INVOICE_CURRENCY_CODE CURRENCY_CODE
, NULL STREAM_TYPE
, NULL STREAM_TYPE_PURPOSE
, AI.INVOICE_ID TRANSACTION_ID
, 'AP_INVOICES' SOURCE_TABLE
, -1 CHECK_ID
, TPL.KHR_ID KHR_ID
, TO_CHAR(AI.INVOICE_ID) INVOICE_ID
FROM AP_INVOICES AI
, OKL_TRX_AP_INVS_ALL_B TAP
, OKL_TXL_AP_INV_LNS_B TPL
, OKL_CNSLD_AP_INVS_ALL CNSLD
, FND_APPLICATION APP
WHERE TAP.VENDOR_INVOICE_NUMBER = AI.INVOICE_NUM
AND TAP.VENDOR_ID = AI.VENDOR_ID
AND TAP.ID = TPL.TAP_ID
AND TPL.CNSLD_AP_INV_ID = CNSLD.CNSLD_AP_INV_ID
AND AI.APPLICATION_ID = APP.APPLICATION_ID
AND APP.APPLICATION_SHORT_NAME = 'OKL'
AND AI.PRODUCT_TABLE = 'OKL_CNSLD_AP_INVS_ALL'
AND CNSLD.CNSLD_AP_INV_ID = TO_NUMBER(AI.REFERENCE_KEY1) UNION ALL SELECT TO_CHAR(AC.CHECK_NUMBER) ID
, 1 LINE_NUMBER
, BB.BANK_NAME || ' - ' || BB.BANK_BRANCH_NAME || ' - ' || BA.BANK_ACCOUNT_NAME || ' - ' || BA.BANK_ACCOUNT_NUM || ' - ' || OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('PAYMENT METHOD'
, AC.PAYMENT_METHOD_LOOKUP_CODE
, 200
, 200) DESCRIPTION
, AIP.AMOUNT AMOUNT
, AC.CURRENCY_CODE CURRENCY_CODE
, NULL STREAM_TYPE
, NULL STREAM_TYPE_PURPOSE
, AC.CHECK_NUMBER TRANSACTION_ID
, 'AP_CHECKS' SOURCE_TABLE
, AC.CHECK_ID CHECK_ID
, TPL.KHR_ID KHR_ID
, TO_CHAR(AIP.INVOICE_ID) INVOICE_ID
FROM OKL_TRX_AP_INVS_ALL_B TAP
, AP_INVOICES AI
, AP_INVOICE_PAYMENTS_ALL AIP
, AP_CHECKS_ALL AC
, CE_BANK_ACCOUNTS BA
, CE_BANK_ACCT_USES_ALL BAU
, CE_BANK_BRANCHES_V BB
, OKL_TXL_AP_INV_LNS_B TPL
, OKL_CNSLD_AP_INVS_ALL CNSLD
, FND_APPLICATION APP
WHERE TAP.VENDOR_INVOICE_NUMBER = AI.INVOICE_NUM
AND TAP.VENDOR_ID = AI.VENDOR_ID
AND AI.INVOICE_ID = AIP.INVOICE_ID
AND AIP.CHECK_ID = AC.CHECK_ID
AND AC.CE_BANK_ACCT_USE_ID = BAU.BANK_ACCT_USE_ID
AND BA.BANK_ACCOUNT_ID = BAU.BANK_ACCOUNT_ID
AND BA.BANK_BRANCH_ID = BB.BRANCH_PARTY_ID
AND TAP.ID = TPL.TAP_ID
AND TPL.CNSLD_AP_INV_ID = CNSLD.CNSLD_AP_INV_ID
AND AI.APPLICATION_ID = APP.APPLICATION_ID
AND APP.APPLICATION_SHORT_NAME = 'OKL'
AND AI.PRODUCT_TABLE = 'OKL_CNSLD_AP_INVS_ALL'
AND CNSLD.CNSLD_AP_INV_ID = TO_NUMBER(AI.REFERENCE_KEY1)