eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.OKL_CS_BPD_INV_DTL_V

Source

View Text - Preformatted


SELECT cnr.id invoice_id, khr.id chr_id, lsm.id lsm_id, cnr.consolidated_invoice_number INVOICE_NUMBER, cnr.currency_code INVOICE_CURRENCY_CODE, cnr.due_date DUE_DATE, cnr.amount INVOICE_AMOUNT, lsm.receivables_invoice_id AR_INVOICE_ID, cnr.date_consolidated INVOICE_DATE, cnr.IBT_ID BILL_TO_SITE_USE_ID, cnr.IXX_ID CUSTOMER_ACCT_ID, cnr.ORG_ID ORG_ID, 'OKL-CONS-INV' INVOICE_TYPE, KHR.CONTRACT_NUMBER CONTRACT_NUMBER, 'IMMEDIATE' PAYMENT_TERM, LSM.STY_ID sty_id, LSM.KLE_ID KLE_ID, LSM.AMOUNT LINE_AMOUNT, LSM.TAX_AMOUNT TAX_AMOUNT, APS.TAX_REMAINING TAX_REMAINING, to_number(APS.TRX_NUMBER) LINE_IDENTIFIER, NVL(LSM.AMOUNT + LSM.TAX_AMOUNT,0) AMOUNT_ORIGINAL, ractrl.customer_trx_line_id INVOICE_LINE_ID, OKL_BILLING_UTIL_PVT.INVOICE_AMOUNT_APPLIED(lsm.receivables_invoice_id) AMOUNT_APPLIED, OKL_BILLING_UTIL_PVT.INVOICE_AMOUNT_CREDITED(lsm.receivables_invoice_id) AMOUNT_CREDITED, OKL_BILLING_UTIL_PVT.INVOICE_AMOUNT_REMAINING(lsm.receivables_invoice_id) AMOUNT_REMAINING, OKL_BILLING_UTIL_PVT.INVOICE_AMOUNT_ADJUSTED(LSM.RECEIVABLES_INVOICE_ID) AMOUNT_ADJUSTED, fndv.meaning TRX_STATUS, CNR.INVOICE_PULL_YN FROM OKC_K_HEADERS_B KHR, OKL_CNSLD_AR_HDRS_B CNR, OKL_CNSLD_AR_LINES_B LLN, OKL_CNSLD_AR_STRMS_B LSM, AR_PAYMENT_SCHEDULES_ALL APS, RA_CUSTOMER_TRX_LINES_ALL RACTRL, FND_LOOKUPS FNDV WHERE cnr.id = lln.cnr_id and lln.id = lsm.lln_id and lsm.receivables_invoice_id = aps.customer_trx_id and lsm.khr_id = khr.id and lsm.receivables_invoice_id = ractrl.customer_trx_id and ractrl.line_type = 'LINE' AND cnr.trx_status_code = fndv.lookup_code AND fndv.lookup_type = 'OKL_TRANSACTION_STATUS' union ALL SELECT cust_trx.customer_trx_id invoice_id, CHR.id chr_id, tld.id lsm_id, cust_trx.trx_number INVOICE_NUMBER, cust_trx.invoice_currency_code INVOICE_CURRENCY_CODE, pay_sch.due_date DUE_DATE, pay_sch.amount_due_original INVOICE_AMOUNT, cust_trx.customer_trx_id AR_INVOICE_ID, cust_trx.trx_date INVOICE_DATE, cust_trx.BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID, cust_trx.BILL_TO_CUSTOMER_ID CUSTOMER_ACCT_ID, cust_trx.ORG_ID ORG_ID, 'AR-INVOICE' INVOICE_TYPE, cust_trx_lines.interface_line_attribute6 CONTRACT_NUMBER, 'IMMEDIATE' PAYMENT_TERM, tld.sty_id sty_id, tld.kle_id kle_id, EXTENDED_AMOUNT LINE_AMOUNT, (select sum(nvl(extended_amount,0)) from ra_customer_trx_lines_all b where b.link_to_cust_trx_line_id = cust_trx_lines.customer_trx_line_id) TAX_AMOUNT, (select sum(nvl(amount_due_remaining,0)) from ra_customer_trx_lines_all b where b.link_to_cust_trx_line_id = cust_trx_lines.customer_trx_line_id) TAX_REMAINING, cust_trx_lines.LINE_NUMBER LINE_IDENTIFIER, OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_ORIG(cust_trx.customer_trx_id, cust_trx_lines.customer_trx_line_id) AMOUNT_ORIGINAL, cust_trx_lines.CUSTOMER_TRX_LINE_ID INVOICE_LINE_ID, OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_APPLIED(cust_trx.customer_trx_id, cust_trx_lines.customer_trx_line_id) AMOUNT_APPLIED, OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_CREDITED(cust_trx.customer_trx_id, cust_trx_lines.customer_trx_line_id) AMOUNT_CREDITED, OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_REMAINING(cust_trx.customer_trx_id, cust_trx_lines.customer_trx_line_id) AMOUNT_REMAINING, OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_ADJUSTED(CUST_TRX.CUSTOMER_TRX_ID,CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID) AMOUNT_ADJUSTED, fndv.meaning TRX_STATUS,TAI.INVOICE_PULL_YN FROM RA_CUSTOMER_TRX_ALL CUST_TRX, AR_PAYMENT_SCHEDULES_ALL PAY_SCH, RA_CUSTOMER_TRX_LINES_ALL CUST_TRX_LINES, OKL_TXD_AR_LN_DTLS_B TLD, okc_k_headers_b chr,OKL_TRX_AR_INVOICES_B TAI, OKL_TXL_AR_INV_LNS_B TIL, FND_LOOKUPS FNDV WHERE cust_trx.customer_trx_id = pay_sch.customer_trx_id and cust_trx.customer_trx_id = cust_trx_lines.customer_trx_id and TLD.ID = cust_trx_lines.INTERFACE_LINE_ATTRIBUTE14 and cust_trx_lines.interface_line_attribute1 is NULL and cust_trx_lines.LINE_TYPE = 'LINE' AND CHR.contract_number = cust_trx_lines.interface_line_attribute6 AND cust_trx_lines.interface_line_context in ( 'OKL_CONTRACTS','OKL_MANUAL') AND chr.scs_code ='LEASE' AND TAI.trx_status_code = fndv.lookup_code AND fndv.lookup_type = 'OKL_TRANSACTION_STATUS' AND TLD.TIL_ID_DETAILS = TIL.ID AND TIL.TAI_ID = TAI.ID

View Text - HTML Formatted

SELECT CNR.ID INVOICE_ID
, KHR.ID CHR_ID
, LSM.ID LSM_ID
, CNR.CONSOLIDATED_INVOICE_NUMBER INVOICE_NUMBER
, CNR.CURRENCY_CODE INVOICE_CURRENCY_CODE
, CNR.DUE_DATE DUE_DATE
, CNR.AMOUNT INVOICE_AMOUNT
, LSM.RECEIVABLES_INVOICE_ID AR_INVOICE_ID
, CNR.DATE_CONSOLIDATED INVOICE_DATE
, CNR.IBT_ID BILL_TO_SITE_USE_ID
, CNR.IXX_ID CUSTOMER_ACCT_ID
, CNR.ORG_ID ORG_ID
, 'OKL-CONS-INV' INVOICE_TYPE
, KHR.CONTRACT_NUMBER CONTRACT_NUMBER
, 'IMMEDIATE' PAYMENT_TERM
, LSM.STY_ID STY_ID
, LSM.KLE_ID KLE_ID
, LSM.AMOUNT LINE_AMOUNT
, LSM.TAX_AMOUNT TAX_AMOUNT
, APS.TAX_REMAINING TAX_REMAINING
, TO_NUMBER(APS.TRX_NUMBER) LINE_IDENTIFIER
, NVL(LSM.AMOUNT + LSM.TAX_AMOUNT
, 0) AMOUNT_ORIGINAL
, RACTRL.CUSTOMER_TRX_LINE_ID INVOICE_LINE_ID
, OKL_BILLING_UTIL_PVT.INVOICE_AMOUNT_APPLIED(LSM.RECEIVABLES_INVOICE_ID) AMOUNT_APPLIED
, OKL_BILLING_UTIL_PVT.INVOICE_AMOUNT_CREDITED(LSM.RECEIVABLES_INVOICE_ID) AMOUNT_CREDITED
, OKL_BILLING_UTIL_PVT.INVOICE_AMOUNT_REMAINING(LSM.RECEIVABLES_INVOICE_ID) AMOUNT_REMAINING
, OKL_BILLING_UTIL_PVT.INVOICE_AMOUNT_ADJUSTED(LSM.RECEIVABLES_INVOICE_ID) AMOUNT_ADJUSTED
, FNDV.MEANING TRX_STATUS
, CNR.INVOICE_PULL_YN
FROM OKC_K_HEADERS_B KHR
, OKL_CNSLD_AR_HDRS_B CNR
, OKL_CNSLD_AR_LINES_B LLN
, OKL_CNSLD_AR_STRMS_B LSM
, AR_PAYMENT_SCHEDULES_ALL APS
, RA_CUSTOMER_TRX_LINES_ALL RACTRL
, FND_LOOKUPS FNDV
WHERE CNR.ID = LLN.CNR_ID
AND LLN.ID = LSM.LLN_ID
AND LSM.RECEIVABLES_INVOICE_ID = APS.CUSTOMER_TRX_ID
AND LSM.KHR_ID = KHR.ID
AND LSM.RECEIVABLES_INVOICE_ID = RACTRL.CUSTOMER_TRX_ID
AND RACTRL.LINE_TYPE = 'LINE'
AND CNR.TRX_STATUS_CODE = FNDV.LOOKUP_CODE
AND FNDV.LOOKUP_TYPE = 'OKL_TRANSACTION_STATUS' UNION ALL SELECT CUST_TRX.CUSTOMER_TRX_ID INVOICE_ID
, CHR.ID CHR_ID
, TLD.ID LSM_ID
, CUST_TRX.TRX_NUMBER INVOICE_NUMBER
, CUST_TRX.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
, PAY_SCH.DUE_DATE DUE_DATE
, PAY_SCH.AMOUNT_DUE_ORIGINAL INVOICE_AMOUNT
, CUST_TRX.CUSTOMER_TRX_ID AR_INVOICE_ID
, CUST_TRX.TRX_DATE INVOICE_DATE
, CUST_TRX.BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID
, CUST_TRX.BILL_TO_CUSTOMER_ID CUSTOMER_ACCT_ID
, CUST_TRX.ORG_ID ORG_ID
, 'AR-INVOICE' INVOICE_TYPE
, CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE6 CONTRACT_NUMBER
, 'IMMEDIATE' PAYMENT_TERM
, TLD.STY_ID STY_ID
, TLD.KLE_ID KLE_ID
, EXTENDED_AMOUNT LINE_AMOUNT
, (SELECT SUM(NVL(EXTENDED_AMOUNT
, 0))
FROM RA_CUSTOMER_TRX_LINES_ALL B
WHERE B.LINK_TO_CUST_TRX_LINE_ID = CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID) TAX_AMOUNT
, (SELECT SUM(NVL(AMOUNT_DUE_REMAINING
, 0))
FROM RA_CUSTOMER_TRX_LINES_ALL B
WHERE B.LINK_TO_CUST_TRX_LINE_ID = CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID) TAX_REMAINING
, CUST_TRX_LINES.LINE_NUMBER LINE_IDENTIFIER
, OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_ORIG(CUST_TRX.CUSTOMER_TRX_ID
, CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID) AMOUNT_ORIGINAL
, CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID INVOICE_LINE_ID
, OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_APPLIED(CUST_TRX.CUSTOMER_TRX_ID
, CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID) AMOUNT_APPLIED
, OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_CREDITED(CUST_TRX.CUSTOMER_TRX_ID
, CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID) AMOUNT_CREDITED
, OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_REMAINING(CUST_TRX.CUSTOMER_TRX_ID
, CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID) AMOUNT_REMAINING
, OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_ADJUSTED(CUST_TRX.CUSTOMER_TRX_ID
, CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID) AMOUNT_ADJUSTED
, FNDV.MEANING TRX_STATUS
, TAI.INVOICE_PULL_YN
FROM RA_CUSTOMER_TRX_ALL CUST_TRX
, AR_PAYMENT_SCHEDULES_ALL PAY_SCH
, RA_CUSTOMER_TRX_LINES_ALL CUST_TRX_LINES
, OKL_TXD_AR_LN_DTLS_B TLD
, OKC_K_HEADERS_B CHR
, OKL_TRX_AR_INVOICES_B TAI
, OKL_TXL_AR_INV_LNS_B TIL
, FND_LOOKUPS FNDV
WHERE CUST_TRX.CUSTOMER_TRX_ID = PAY_SCH.CUSTOMER_TRX_ID
AND CUST_TRX.CUSTOMER_TRX_ID = CUST_TRX_LINES.CUSTOMER_TRX_ID
AND TLD.ID = CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE14
AND CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE1 IS NULL
AND CUST_TRX_LINES.LINE_TYPE = 'LINE'
AND CHR.CONTRACT_NUMBER = CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE6
AND CUST_TRX_LINES.INTERFACE_LINE_CONTEXT IN ( 'OKL_CONTRACTS'
, 'OKL_MANUAL')
AND CHR.SCS_CODE ='LEASE'
AND TAI.TRX_STATUS_CODE = FNDV.LOOKUP_CODE
AND FNDV.LOOKUP_TYPE = 'OKL_TRANSACTION_STATUS'
AND TLD.TIL_ID_DETAILS = TIL.ID
AND TIL.TAI_ID = TAI.ID