View: OKL_RECEIPT_APPLICATIONS_UV

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

View Text

SELECT ARCASH.CASH_RECEIPT_ID
, ARCASH.RECEIPT_NUMBER
, ARAP.APPLIED_CUSTOMER_TRX_ID
, DECODE(CUST_TRX_LINES.INTERFACE_LINE_CONTEXT
, 'OKL_CONTRACTS'
, NVL(CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE1
, PAY_SCH.TRX_NUMBER)
, PAY_SCH.TRX_NUMBER) INVOICE_NUMBER
, 'LINE' LINE_TYPE
, ARL.AMOUNT LINE_APPLIED
, ARL.TAX TAX_APPLIED
, CUST_TRX_LINES.AMOUNT_DUE_REMAINING LINE_BALANCE
, (SELECT NVL(SUM(AMOUNT_DUE_REMAINING)
, 0)
FROM RA_CUSTOMER_TRX_LINES_ALL TAX_LINES
WHERE TAX_LINES.LINK_TO_CUST_TRX_LINE_ID = CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID
AND TAX_LINES.LINE_TYPE = 'TAX') TAX_BALANCE
, DECODE(CUST_TRX_LINES.INTERFACE_LINE_CONTEXT
, 'OKL_CONTRACTS'
, NVL2(CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE1
, (SELECT LN.SEQUENCE_NUMBER
FROM OKL_CNSLD_AR_STRMS_B ST
, OKL_CNSLD_AR_LINES_B LN
WHERE ST.RECEIVABLES_INVOICE_ID = CUST_TRX_LINES.CUSTOMER_TRX_ID
AND LN.ID = ST.LLN_ID)
, TO_CHAR(CUST_TRX_LINES.LINE_NUMBER))
, TO_CHAR(CUST_TRX_LINES.LINE_NUMBER)) LINE_NUMBER
, CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID
, ARAP.APPLY_DATE
, PAY_SCH.TRX_DATE INVOICE_DATE
, PAY_SCH.DUE_DATE
, DECODE(CUST_TRX_LINES.INTERFACE_LINE_CONTEXT
, 'OKL_CONTRACTS'
, CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE7
, 'OKL_MANUAL'
, CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE7
, NULL) ASSET_NUMBER
, DECODE(CUST_TRX_LINES.INTERFACE_LINE_CONTEXT
, 'OKL_CONTRACTS'
, CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE9
, 'OKL_MANUAL'
, CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE9
, NULL) STREAM_TYPE
, DECODE(CUST_TRX_LINES.INTERFACE_LINE_CONTEXT
, 'OKL_CONTRACTS'
, CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE6
, 'OKL_MANUAL'
, CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE6
, NULL) CONTRACT_NUMBER
, ARAP.GL_DATE
, NVL(ARAP.TRANS_TO_RECEIPT_RATE
, 1) TRANS_TO_RECEIPT_RATE
, PAY_SCH.INVOICE_CURRENCY_CODE
, ARAP.RECEIVABLE_APPLICATION_ID
, (ARL.AMOUNT + ARL.TAX ) LINE_TAX_APPLIED
, (CUST_TRX_LINES.AMOUNT_DUE_REMAINING + (SELECT NVL(SUM(AMOUNT_DUE_REMAINING)
, 0)
FROM RA_CUSTOMER_TRX_LINES_ALL TAX_LINES
WHERE TAX_LINES.LINK_TO_CUST_TRX_LINE_ID = CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID
AND TAX_LINES.LINE_TYPE = 'TAX') ) LINE_TAX_BALANCE
FROM AR_CASH_RECEIPTS_ALL ARCASH
, AR_RECEIVABLE_APPLICATIONS_ALL ARAP
, AR_ACTIVITY_DETAILS ARL
, AR_PAYMENT_SCHEDULES_ALL PAY_SCH
, RA_CUSTOMER_TRX_LINES_ALL CUST_TRX_LINES
WHERE ARCASH.CASH_RECEIPT_ID = ARAP.CASH_RECEIPT_ID
AND ARAP.STATUS = 'APP'
AND ARAP.DISPLAY = 'Y'
AND ARAP.RECEIVABLE_APPLICATION_ID = ARL.SOURCE_ID
AND ARL.SOURCE_TABLE = 'RA'
AND ARL.CUSTOMER_TRX_LINE_ID = CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID
AND CUST_TRX_LINES.LINE_TYPE = 'LINE'
AND PAY_SCH.PAYMENT_SCHEDULE_ID = ARAP.APPLIED_PAYMENT_SCHEDULE_ID UNION ALL SELECT ARCASH.CASH_RECEIPT_ID
, ARCASH.RECEIPT_NUMBER
, ARAP.APPLIED_CUSTOMER_TRX_ID
, DECODE(CUST_TRX.INTERFACE_HEADER_CONTEXT
, 'OKL_CONTRACTS'
, NVL((SELECT LN.INTERFACE_LINE_ATTRIBUTE1
FROM RA_CUSTOMER_TRX_LINES_ALL LN
WHERE LN.CUSTOMER_TRX_ID = CUST_TRX.CUSTOMER_TRX_ID
AND LN.LINE_TYPE = 'LINE'
AND ROWNUM < 2)
, PAY_SCH.TRX_NUMBER)
, PAY_SCH.TRX_NUMBER) INVOICE_NUMBER
, 'LINE' LINE_TYPE
, ARAP.LINE_APPLIED
, ARAP.TAX_APPLIED
, PAY_SCH.AMOUNT_LINE_ITEMS_REMAINING LINE_BALANCE
, PAY_SCH.TAX_REMAINING TAX_BALANCE
, NULL
, NULL
, ARAP.APPLY_DATE
, PAY_SCH.TRX_DATE INVOICE_DATE
, PAY_SCH.DUE_DATE
, DECODE(CUST_TRX.INTERFACE_HEADER_CONTEXT
, 'OKL_CONTRACTS'
, NVL2((SELECT LN.INTERFACE_LINE_ATTRIBUTE1
FROM RA_CUSTOMER_TRX_LINES_ALL LN
WHERE LN.CUSTOMER_TRX_ID = CUST_TRX.CUSTOMER_TRX_ID
AND LN.LINE_TYPE = 'LINE'
AND ROWNUM < 2)
, CUST_TRX.INTERFACE_HEADER_ATTRIBUTE7
, NULL)
, NULL) ASSET_NUMBER
, DECODE(CUST_TRX.INTERFACE_HEADER_CONTEXT
, 'OKL_CONTRACTS'
, NVL2((SELECT LN.INTERFACE_LINE_ATTRIBUTE1
FROM RA_CUSTOMER_TRX_LINES_ALL LN
WHERE LN.CUSTOMER_TRX_ID = CUST_TRX.CUSTOMER_TRX_ID
AND LN.LINE_TYPE = 'LINE'
AND ROWNUM < 2)
, CUST_TRX.INTERFACE_HEADER_ATTRIBUTE9
, NULL)
, NULL) STREAM_TYPE
, DECODE(CUST_TRX.INTERFACE_HEADER_CONTEXT
, 'OKL_CONTRACTS'
, NVL2((SELECT LN.INTERFACE_LINE_ATTRIBUTE1
FROM RA_CUSTOMER_TRX_LINES_ALL LN
WHERE LN.CUSTOMER_TRX_ID = CUST_TRX.CUSTOMER_TRX_ID
AND LN.LINE_TYPE = 'LINE'
AND ROWNUM < 2)
, CUST_TRX.INTERFACE_HEADER_ATTRIBUTE6
, NULL)
, NULL) CONTRACT_NUMBER
, ARAP.GL_DATE
, NVL(ARAP.TRANS_TO_RECEIPT_RATE
, 1) TRANS_TO_RECEIPT_RATE
, PAY_SCH.INVOICE_CURRENCY_CODE
, ARAP.RECEIVABLE_APPLICATION_ID
, (ARAP.LINE_APPLIED + ARAP.TAX_APPLIED) LINE_TAX_APPLIED
, (PAY_SCH.AMOUNT_LINE_ITEMS_REMAINING + PAY_SCH.TAX_REMAINING ) LINE_TAX_BALANCE
FROM AR_CASH_RECEIPTS_ALL ARCASH
, AR_RECEIVABLE_APPLICATIONS_ALL ARAP
, AR_PAYMENT_SCHEDULES_ALL PAY_SCH
, RA_CUSTOMER_TRX_ALL CUST_TRX
WHERE ARCASH.CASH_RECEIPT_ID = ARAP.CASH_RECEIPT_ID
AND ARAP.STATUS = 'APP'
AND ARAP.DISPLAY = 'Y'
AND PAY_SCH.PAYMENT_SCHEDULE_ID = ARAP.APPLIED_PAYMENT_SCHEDULE_ID
AND PAY_SCH.CUSTOMER_TRX_ID = CUST_TRX.CUSTOMER_TRX_ID
AND NOT EXISTS (SELECT 'X'
FROM AR_ACTIVITY_DETAILS ARL
WHERE ARL.SOURCE_ID = ARAP.RECEIVABLE_APPLICATION_ID
AND ARL.SOURCE_TABLE = 'RA') UNION ALL SELECT ARCASH.CASH_RECEIPT_ID
, ARCASH.RECEIPT_NUMBER
, ARAP.APPLIED_CUSTOMER_TRX_ID
, PAY_SCH.TRX_NUMBER INVOICE_NUMBER
, 'FREIGHT' LINE_TYPE
, ARAP.FREIGHT_APPLIED LINE_APPLIED
, 0 TAX_APPLIED
, PAY_SCH.FREIGHT_REMAINING LINE_BALANCE
, 0 TAX_BALANCE
, NULL LINE_NUMBER
, NULL CUSTOMER_TRX_LINE_ID
, ARAP.APPLY_DATE
, PAY_SCH.TRX_DATE INVOICE_DATE
, PAY_SCH.DUE_DATE
, NULL ASSET_NUMBER
, NULL STREAM_TYPE
, NULL CONTRACT_NUMBER
, ARAP.GL_DATE
, NVL(ARAP.TRANS_TO_RECEIPT_RATE
, 1) TRANS_TO_RECEIPT_RATE
, PAY_SCH.INVOICE_CURRENCY_CODE
, ARAP.RECEIVABLE_APPLICATION_ID
, ARAP.FREIGHT_APPLIED LINE_TAX_APPLIED
, PAY_SCH.FREIGHT_REMAINING LINE_TAX_BALANCE
FROM AR_CASH_RECEIPTS_ALL ARCASH
, AR_RECEIVABLE_APPLICATIONS_ALL ARAP
, AR_PAYMENT_SCHEDULES_ALL PAY_SCH
WHERE ARCASH.CASH_RECEIPT_ID = ARAP.CASH_RECEIPT_ID
AND ARAP.STATUS = 'APP'
AND ARAP.DISPLAY = 'Y'
AND PAY_SCH.PAYMENT_SCHEDULE_ID = ARAP.APPLIED_PAYMENT_SCHEDULE_ID
AND ARAP.FREIGHT_APPLIED > 0 UNION ALL SELECT ARCASH.CASH_RECEIPT_ID
, ARCASH.RECEIPT_NUMBER
, ARAP.APPLIED_CUSTOMER_TRX_ID
, PAY_SCH.TRX_NUMBER INVOICE_NUMBER
, 'CHARGES' LINE_TYPE
, ARAP.RECEIVABLES_CHARGES_APPLIED LINE_APPLIED
, 0 TAX_APPLIED
, PAY_SCH.RECEIVABLES_CHARGES_REMAINING LINE_BALANCE
, 0 TAX_BALANCE
, NULL LINE_NUMBER
, NULL CUSTOMER_TRX_LINE_ID
, ARAP.APPLY_DATE
, PAY_SCH.TRX_DATE INVOICE_DATE
, PAY_SCH.DUE_DATE
, NULL ASSET_NUMBER
, NULL STREAM_TYPE
, NULL CONTRACT_NUMBER
, ARAP.GL_DATE
, NVL(ARAP.TRANS_TO_RECEIPT_RATE
, 1) TRANS_TO_RECEIPT_RATE
, PAY_SCH.INVOICE_CURRENCY_CODE
, ARAP.RECEIVABLE_APPLICATION_ID
, ARAP.RECEIVABLES_CHARGES_APPLIED LINE_TAX_APPLIED
, PAY_SCH.RECEIVABLES_CHARGES_REMAINING LINE_TAX_BALANCE
FROM AR_CASH_RECEIPTS_ALL ARCASH
, AR_RECEIVABLE_APPLICATIONS_ALL ARAP
, AR_PAYMENT_SCHEDULES_ALL PAY_SCH
WHERE ARCASH.CASH_RECEIPT_ID = ARAP.CASH_RECEIPT_ID
AND ARAP.STATUS = 'APP'
AND ARAP.DISPLAY = 'Y'
AND PAY_SCH.PAYMENT_SCHEDULE_ID = ARAP.APPLIED_PAYMENT_SCHEDULE_ID
AND ARAP.RECEIVABLES_CHARGES_APPLIED > 0

Columns