View: OKL_RCPT_CUST_CONT_BALANCES_UV

Product:	OKL - Lease and Finance Management
Description:
Implementation/DBA Data:	APPS.OKL_RCPT_CUST_CONT_BALANCES_UV

View Text

SELECT HD.CONSOLIDATED_INVOICE_NUMBER CONSOLIDATED_INVOICE_NUMBER
, HD.ID CONSOLIDATED_INVOICE_ID
, HD.CURRENCY_CODE
, HD.DATE_CONSOLIDATED INVOICE_DATE
, LN.SEQUENCE_NUMBER LINE_NUMBER
, CUST_TRX.TRX_NUMBER AR_INVOICE_NUMBER
, CUST_TRX.CUSTOMER_TRX_ID AR_INVOICE_ID
, CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID INVOICE_LINE_ID
, CUST_TRX.ORG_ID ORG_ID
, CUST.PARTY_NAME CUSTOMER_NAME
, CUST.PARTY_NUMBER CUSTOMER_NUMBER
, ACCT.CUST_ACCOUNT_ID CUSTOMER_ACCT_ID
, ACCT.ACCOUNT_NUMBER CUSTOMER_ACCOUNT_NUMBER
, CUST_TRX.BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID
, ST.KHR_ID
, CN.CONTRACT_NUMBER CONTRACT_NUMBER
, CN.START_DATE CONTRACT_START_DATE
, CUST_TRX.INTERFACE_HEADER_ATTRIBUTE7 ASSET_NUMBER
, CUST_TRX.INTERFACE_HEADER_ATTRIBUTE9 STREAM_TYPE
, ST.STY_ID
, ST.KLE_ID
, ST.SEL_ID
, PAY_SCH.AMOUNT_DUE_ORIGINAL AMOUNT_DUE_ORIGINAL
, PAY_SCH.AMOUNT_DUE_REMAINING AMOUNT_DUE_REMAINING
, PAY_SCH.DUE_DATE
, PAY_SCH.TRX_DATE TRX_DATE
, PAY_SCH.STATUS STATUS
FROM RA_CUSTOMER_TRX_ALL CUST_TRX
, RA_CUSTOMER_TRX_LINES_ALL CUST_TRX_LINES
, HZ_CUST_ACCOUNTS ACCT
, HZ_PARTIES CUST
, AR_PAYMENT_SCHEDULES_ALL PAY_SCH
, OKL_CNSLD_AR_STRMS_B ST
, OKL_CNSLD_AR_LINES_B LN
, OKL_CNSLD_AR_HDRS_ALL_B HD
, OKC_K_HEADERS_ALL_B CN
WHERE CUST_TRX.BILL_TO_CUSTOMER_ID = ACCT.CUST_ACCOUNT_ID
AND ACCT.PARTY_ID = CUST.PARTY_ID
AND CUST_TRX.CUSTOMER_TRX_ID = PAY_SCH.CUSTOMER_TRX_ID
AND CUST_TRX.CUSTOMER_TRX_ID = CUST_TRX_LINES.CUSTOMER_TRX_ID
AND CUST_TRX_LINES.LINE_TYPE = 'LINE'
AND CN.ID = ST.KHR_ID
AND PAY_SCH.CLASS = 'INV'
AND ST.RECEIVABLES_INVOICE_ID = CUST_TRX.CUSTOMER_TRX_ID
AND LN.ID = ST.LLN_ID
AND HD.ID = LN.CNR_ID
AND CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE1 IS NOT NULL UNION ALL SELECT NULL CONSOLIDATED_INVOICE_NUMBER
, NULL CONSOLIDATED_INVOICE_ID
, CUST_TRX.INVOICE_CURRENCY_CODE CURRENCY_CODE
, CUST_TRX.TRX_DATE INVOICE_DATE
, CUST_TRX_LINES.LINE_NUMBER LINE_NUMBER
, CUST_TRX.TRX_NUMBER AR_INVOICE_NUMBER
, CUST_TRX.CUSTOMER_TRX_ID AR_INVOICE_ID
, CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID INVOICE_LINE_ID
, CUST_TRX.ORG_ID ORG_ID
, CUST.PARTY_NAME CUSTOMER_NAME
, CUST.PARTY_NUMBER CUSTOMER_NUMBER
, ACCT.CUST_ACCOUNT_ID CUSTOMER_ACCT_ID
, ACCT.ACCOUNT_NUMBER CUSTOMER_ACCOUNT_NUMBER
, CUST_TRX.BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID
, ST.KHR_ID
, CN.CONTRACT_NUMBER CONTRACT_NUMBER
, CN.START_DATE START_DATE
, CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE7 ASSET_NUMBER
, CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE9 STREAM_TYPE
, ST.STY_ID STY_ID
, ST.KLE_ID
, ST.SEL_ID
, (NVL(CUST_TRX_LINES.EXTENDED_AMOUNT
, 0) + (SELECT NVL(SUM(AA.EXTENDED_AMOUNT)
, 0)
FROM RA_CUSTOMER_TRX_LINES_ALL AA
WHERE AA.LINK_TO_CUST_TRX_LINE_ID = CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID
AND AA.LINE_TYPE ='TAX'))AMOUNT_DUE_ORIGINAL
, (NVL(CUST_TRX_LINES.AMOUNT_DUE_REMAINING
, 0) + (SELECT NVL(SUM(AA.AMOUNT_DUE_REMAINING)
, 0)
FROM RA_CUSTOMER_TRX_LINES_ALL AA
WHERE AA.LINK_TO_CUST_TRX_LINE_ID = CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID
AND AA.LINE_TYPE ='TAX')) AMOUNT_DUE_REMAINING
, AR.DUE_DATE INVOICE_DUE_DATE
, AR.TRX_DATE TRX_DATE
, AR.STATUS STATUS
FROM RA_CUSTOMER_TRX_ALL CUST_TRX
, RA_CUSTOMER_TRX_LINES_ALL CUST_TRX_LINES
, HZ_CUST_ACCOUNTS ACCT
, HZ_PARTIES CUST
, AR_PAYMENT_SCHEDULES_ALL AR
, OKL_TXD_AR_LN_DTLS_B ST
, OKC_K_HEADERS_ALL_B CN
WHERE CUST_TRX.CUSTOMER_TRX_ID = CUST_TRX_LINES.CUSTOMER_TRX_ID
AND CUST_TRX.BILL_TO_CUSTOMER_ID = ACCT.CUST_ACCOUNT_ID
AND ACCT.PARTY_ID = CUST.PARTY_ID
AND CUST_TRX_LINES.LINE_TYPE = 'LINE'
AND CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE1 IS NULL
AND CUST_TRX_LINES.CUSTOMER_TRX_ID = AR.CUSTOMER_TRX_ID
AND CUST_TRX.CUSTOMER_TRX_ID = AR.CUSTOMER_TRX_ID
AND ST.ID = CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE14
AND ST.KHR_ID = CN.ID
AND AR.CLASS = 'INV'
AND CUST_TRX_LINES.INTERFACE_LINE_CONTEXT IN ( 'OKL_CONTRACTS'
, 'OKL_MANUAL')

Columns