View: OKL_RCPT_ARINV_BALANCES_UV

Product:	OKL - Leasing and Finance Management
Description:
Implementation/DBA Data:	APPS.OKL_RCPT_ARINV_BALANCES_UV

View Text

SELECT CUST_TRX.TRX_NUMBER INVOICE_NUMBER
, CUST_TRX.CUSTOMER_TRX_ID AR_INVOICE_ID
, CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID INVOICE_LINE_ID
, CUST_TRX.TRX_DATE INVOICE_DATE
, CUST_TRX.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
, 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
, CUST_TRX_LINES.LINE_NUMBER LINE_IDENTIFIER
, 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 CUST_TRX_LINES.INTERFACE_LINE_CONTEXT IN ( 'OKL_CONTRACTS'
, 'OKL_MANUAL')
AND AR.CLASS = 'INV'

Columns