VIEW: APPS.OKL_BPD_INVOICE_HDR_BC_V

Source

View Text - Preformatted

SELECT cnr.consolidated_invoice_number INVOICE_ID, party.name CUSTOMER_NAME, site.name BILL_TO_SITE_NAME, cnr.currency_code CURRENCY_CODE, cnr.due_date DUE_DATE, cnr.amount AMOUNT, sum(aps.amount_due_remaining) BALANCE, /*--lsm.receivables_invoice_id AR_INVOICE_ID,*/ NULL 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, CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER, 'OKL-CONS-INV' INVOICE_TYPE, /*--KHR.CONTRACT_NUMBER CONTRACT_NUMBER*/ NULL CONTRACT_NUMBER 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,OKX_CUST_SITE_USES_V SITE,OKX_PARTIES_V PARTY,HZ_CUST_ACCOUNTS CUST_ACCT 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 khr.bill_to_site_use_id = site.id1 AND party.ID1 = cust_acct.PARTY_ID and cust_acct.cust_account_id = cnr.IXX_ID group by cnr.consolidated_invoice_number , party.name , site.name , cnr.currency_code , cnr.due_date , cnr.amount, /*--lsm.receivables_invoice_id, */ NULL, cnr.date_consolidated, cnr.IBT_ID, cnr.IXX_ID, cnr.ORG_ID, CUST_ACCT.ACCOUNT_NUMBER, 'OKL-CONS-INV', /*--KHR.CONTRACT_NUMBER*/ NULL union all /*--SELECT distinct cust_trx.customer_trx_id INVOICE_ID,*/ SELECT distinct cust_trx.trx_number INVOICE_ID, cust.party_name CUSTOMER_NAME, site.name BILL_TO_SITE_NAME, cust_trx.invoice_currency_code CURRENCY_CODE, /*--pay_sch.trx_date INVOICE_DATE, */ pay_sch.due_date DUE_DATE, pay_sch.amount_due_original AMOUNT, pay_sch.amount_due_remaining BALANCE, 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, ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER, 'AR-INVOICE' INVOICE_TYPE, /*--cust_trx_lines.interface_line_attribute6 CONTRACT_NUMBER -- Assume 6 as contract number*/ NULL CONTRACT_NUMBER FROM RA_CUSTOMER_TRX_ALL CUST_TRX,HZ_CUST_ACCOUNTS ACCT,HZ_PARTIES CUST,OKX_CUST_SITE_USES_V SITE,AR_PAYMENT_SCHEDULES_ALL PAY_SCH, RA_CUSTOMER_TRX_LINES_ALL CUST_TRX_LINES WHERE cust_trx.bill_to_customer_id = acct.cust_account_id and acct.party_id = cust.party_id and cust_trx.bill_to_site_use_id = site.id1 and cust_trx.customer_trx_id = pay_sch.customer_trx_id and cust_trx.customer_trx_id = cust_trx_lines.customer_trx_id and not exists (select 1 from okl_cnsld_ar_strms_b lsm where lsm.receivables_invoice_id = cust_trx.customer_trx_id)

View Text - HTML Formatted

SELECT CNR.CONSOLIDATED_INVOICE_NUMBER INVOICE_ID
, PARTY.NAME CUSTOMER_NAME
, SITE.NAME BILL_TO_SITE_NAME
, CNR.CURRENCY_CODE CURRENCY_CODE
, CNR.DUE_DATE DUE_DATE
, CNR.AMOUNT AMOUNT
, SUM(APS.AMOUNT_DUE_REMAINING) BALANCE
, /*--LSM.RECEIVABLES_INVOICE_ID AR_INVOICE_ID
, */ NULL 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
, CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER
, 'OKL-CONS-INV' INVOICE_TYPE
, /*--KHR.CONTRACT_NUMBER CONTRACT_NUMBER*/ NULL CONTRACT_NUMBER
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
, OKX_CUST_SITE_USES_V SITE
, OKX_PARTIES_V PARTY
, HZ_CUST_ACCOUNTS CUST_ACCT
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 KHR.BILL_TO_SITE_USE_ID = SITE.ID1
AND PARTY.ID1 = CUST_ACCT.PARTY_ID
AND CUST_ACCT.CUST_ACCOUNT_ID = CNR.IXX_ID GROUP BY CNR.CONSOLIDATED_INVOICE_NUMBER
, PARTY.NAME
, SITE.NAME
, CNR.CURRENCY_CODE
, CNR.DUE_DATE
, CNR.AMOUNT
, /*--LSM.RECEIVABLES_INVOICE_ID
, */ NULL
, CNR.DATE_CONSOLIDATED
, CNR.IBT_ID
, CNR.IXX_ID
, CNR.ORG_ID
, CUST_ACCT.ACCOUNT_NUMBER
, 'OKL-CONS-INV'
, /*--KHR.CONTRACT_NUMBER*/ NULL UNION ALL /*--SELECT DISTINCT CUST_TRX.CUSTOMER_TRX_ID INVOICE_ID
, */ SELECT DISTINCT CUST_TRX.TRX_NUMBER INVOICE_ID
, CUST.PARTY_NAME CUSTOMER_NAME
, SITE.NAME BILL_TO_SITE_NAME
, CUST_TRX.INVOICE_CURRENCY_CODE CURRENCY_CODE
, /*--PAY_SCH.TRX_DATE INVOICE_DATE
, */ PAY_SCH.DUE_DATE DUE_DATE
, PAY_SCH.AMOUNT_DUE_ORIGINAL AMOUNT
, PAY_SCH.AMOUNT_DUE_REMAINING BALANCE
, 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
, ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER
, 'AR-INVOICE' INVOICE_TYPE
, /*--CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE6 CONTRACT_NUMBER -- ASSUME 6 AS CONTRACT NUMBER*/ NULL CONTRACT_NUMBER
FROM RA_CUSTOMER_TRX_ALL CUST_TRX
, HZ_CUST_ACCOUNTS ACCT
, HZ_PARTIES CUST
, OKX_CUST_SITE_USES_V SITE
, AR_PAYMENT_SCHEDULES_ALL PAY_SCH
, RA_CUSTOMER_TRX_LINES_ALL CUST_TRX_LINES
WHERE CUST_TRX.BILL_TO_CUSTOMER_ID = ACCT.CUST_ACCOUNT_ID
AND ACCT.PARTY_ID = CUST.PARTY_ID
AND CUST_TRX.BILL_TO_SITE_USE_ID = SITE.ID1
AND CUST_TRX.CUSTOMER_TRX_ID = PAY_SCH.CUSTOMER_TRX_ID
AND CUST_TRX.CUSTOMER_TRX_ID = CUST_TRX_LINES.CUSTOMER_TRX_ID
AND NOT EXISTS (SELECT 1
FROM OKL_CNSLD_AR_STRMS_B LSM
WHERE LSM.RECEIVABLES_INVOICE_ID = CUST_TRX.CUSTOMER_TRX_ID)