[Home] [Help]
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, cust_trx_lines.interface_line_attribute6 contract_number, pay_trm.description 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_TERMS_TL pay_trm, ra_customer_trx_lines_all cust_trx_lines, okl_txd_ar_ln_dtls_b tld, okc_k_headers_b CHR, okc_k_party_roles_v pt, 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 pay_sch.term_id = pay_trm.term_id AND pay_trm.LANGUAGE = USERENV('LANG') 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 AND pt.dnz_chr_id = chr.id and pt.rle_code = 'OKL_VENDOR' and pt.cust_acct_id = tai.IXX_ID
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
, CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE6 CONTRACT_NUMBER
, PAY_TRM.DESCRIPTION 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_TERMS_TL PAY_TRM
, RA_CUSTOMER_TRX_LINES_ALL CUST_TRX_LINES
, OKL_TXD_AR_LN_DTLS_B TLD
, OKC_K_HEADERS_B CHR
, OKC_K_PARTY_ROLES_V PT
, 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 PAY_SCH.TERM_ID = PAY_TRM.TERM_ID
AND PAY_TRM.LANGUAGE = USERENV('LANG')
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
AND PT.DNZ_CHR_ID = CHR.ID
AND PT.RLE_CODE = 'OKL_VENDOR'
AND PT.CUST_ACCT_ID = TAI.IXX_ID
|
|
|
|