View: AR_LL_SUMMARY_V

Product:	AR - Receivables
Description:	Summary for Line-Level Cash Application
Implementation/DBA Data:	APPS.AR_LL_SUMMARY_V

View Text

SELECT CASH_RECEIPT_ID
, CUSTOMER_TRX_ID
, SUM(ALLOCATED_RECEIPT_AMOUNT) ALLOCATED_RECEIPT_AMOUNT
, SUM(LINE) LINE
, SUM(TAX) TAX
, SUM(FREIGHT) FREIGHT
, SUM(CHARGES) CHARGES
, SUM(LIN_DISCOUNT) LIN_DISCOUNT
, SUM(TAX_DISCOUNT) TAX_DISCOUNT
, SUM(FRT_DISCOUNT) FRT_DISCOUNT
, MAX(COMMENTS) COMMENTS
, MAX(CREATED_BY_MODULE) CREATED_BY_MODULE
, SUM(OBJECT_VERSION_NUMBER) OBJECT_VERSION_NUMBER
, SUM(LIN_BALANCE) LIN_BALANCE
, SUM(TAX_BALANCE) TAX_BALANCE
, SUM(FRT_BALANCE) FRT_BALANCE
, SUM(CHG_BALANCE) CHG_BALANCE
, MAX(LAST_UPDATE_DATE) LAST_UPDATE_DATE
, MAX(LAST_UPDATED_BY) LAST_UPDATED_BY
, MAX(CREATION_DATE) CREATION_DATE
, MAX(CREATED_BY) CREATED_BY
, MAX(LAST_UPDATE_LOGIN) LAST_UPDATE_LOGIN
FROM ( (SELECT LL.CASH_RECEIPT_ID CASH_RECEIPT_ID
, CTL.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, LL.ALLOCATED_RECEIPT_AMOUNT ALLOCATED_RECEIPT_AMOUNT
, LL.AMOUNT LINE
, LL.TAX TAX
, 0 FREIGHT
, 0 CHARGES
, LL.LINE_DISCOUNT LIN_DISCOUNT
, LL.TAX_DISCOUNT TAX_DISCOUNT
, 0 FRT_DISCOUNT
, LL.COMMENTS COMMENTS
, LL.CREATED_BY_MODULE CREATED_BY_MODULE
, LL.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
, CTL.AMOUNT_DUE_REMAINING LIN_BALANCE
, CTLTAX.AMOUNT_DUE_REMAINING TAX_BALANCE
, 0 FRT_BALANCE
, 0 CHG_BALANCE
, LL.LAST_UPDATE_DATE LAST_UPDATE_DATE
, LL.LAST_UPDATED_BY LAST_UPDATED_BY
, LL.CREATION_DATE CREATION_DATE
, LL.CREATED_BY CREATED_BY
, LL.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
FROM RA_CUSTOMER_TRX_LINES CTL
, (SELECT LINK_TO_CUST_TRX_LINE_ID
, LINE_TYPE
, SUM(NVL(AMOUNT_DUE_ORIGINAL
, 0)) AMOUNT_DUE_ORIGINAL
, SUM(NVL(AMOUNT_DUE_REMAINING
, 0)) AMOUNT_DUE_REMAINING
FROM RA_CUSTOMER_TRX_LINES /* BUG FIX 5503852 */
WHERE LINE_TYPE = 'TAX' GROUP BY LINK_TO_CUST_TRX_LINE_ID
, LINE_TYPE ) CTLTAX
, AR_ACTIVITY_DETAILS LL
WHERE CTL.CUSTOMER_TRX_LINE_ID = LL.CUSTOMER_TRX_LINE_ID
AND CTL.LINE_TYPE = 'LINE'
AND CTL.CUSTOMER_TRX_LINE_ID = CTLTAX.LINK_TO_CUST_TRX_LINE_ID (+)
AND NVL(LL.SOURCE_TABLE
, 'RA') = 'RA'
AND NVL(LL.CURRENT_ACTIVITY_FLAG
, 'Y') = 'Y' /* 7241111 */ ) UNION ALL (SELECT LL.CASH_RECEIPT_ID CASH_RECEIPT_ID
, CTL.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, LL.ALLOCATED_RECEIPT_AMOUNT ALLOCATED_RECEIPT_AMOUNT
, 0 LINE
, 0 TAX
, LL.FREIGHT FREIGHT
, 0 CHARGES
, 0 LIN_DISCOUNT
, 0 TAX_DISCOUNT
, LL.FREIGHT_DISCOUNT FRT_DISCOUNT
, LL.COMMENTS COMMENTS
, LL.CREATED_BY_MODULE CREATED_BY_MODULE
, LL.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
, 0 LIN_BALANCE
, 0 TAX_BALANCE
, CTL.AMOUNT_DUE_REMAINING FRT_BALANCE
, 0 CHG_BALANCE
, LL.LAST_UPDATE_DATE LAST_UPDATE_DATE
, LL.LAST_UPDATED_BY LAST_UPDATED_BY
, LL.CREATION_DATE CREATION_DATE
, LL.CREATED_BY CREATED_BY
, LL.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
FROM RA_CUSTOMER_TRX_LINES CTL
, AR_ACTIVITY_DETAILS LL
WHERE CTL.CUSTOMER_TRX_LINE_ID = LL.CUSTOMER_TRX_LINE_ID
AND CTL.LINE_TYPE = 'FREIGHT' /*AND CTL.CUSTOMER_TRX_ID = LINTAXFRTCHG.CUSTOMER_TRX_ID*/
AND NVL(LL.SOURCE_TABLE
, 'RA') = 'RA'
AND NVL(LL.CURRENT_ACTIVITY_FLAG
, 'Y') = 'Y' /* 7241111 */ ) UNION ALL (SELECT LL.CASH_RECEIPT_ID CASH_RECEIPT_ID
, CTL.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, LL.ALLOCATED_RECEIPT_AMOUNT ALLOCATED_RECEIPT_AMOUNT
, 0 LINE
, 0 TAX
, 0 FREIGHT
, LL.CHARGES CHARGES
, 0 LIN_DISCOUNT
, 0 TAX_DISCOUNT
, 0 FRT_DISCOUNT
, LL.COMMENTS COMMENTS
, LL.CREATED_BY_MODULE CREATED_BY_MODULE
, LL.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
, 0 LIN_BALANCE
, 0 TAX_BALANCE
, 0 FRT_BALANCE
, CTL.AMOUNT_DUE_REMAINING CHG_BALANCE
, LL.LAST_UPDATE_DATE LAST_UPDATE_DATE
, LL.LAST_UPDATED_BY LAST_UPDATED_BY
, LL.CREATION_DATE CREATION_DATE
, LL.CREATED_BY CREATED_BY
, LL.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
FROM RA_CUSTOMER_TRX_LINES CTL
, AR_ACTIVITY_DETAILS LL
WHERE CTL.CUSTOMER_TRX_LINE_ID = LL.CUSTOMER_TRX_LINE_ID
AND CTL.LINE_TYPE = 'CHARGES' /*AND CTL.CUSTOMER_TRX_ID = LINTAXFRTCHG.CUSTOMER_TRX_ID*/
AND NVL(LL.SOURCE_TABLE
, 'RA') = 'RA'
AND NVL(LL.CURRENT_ACTIVITY_FLAG
, 'Y') = 'Y' /* 7241111 */ ) ) LINTAXFRTCHG GROUP BY CASH_RECEIPT_ID
, CUSTOMER_TRX_ID/*
, TAX.CUSTOMER_TRX_ID
, FRT.CUSTOMER_TRX_ID
, CHG.CUSTOMER_TRX_ID*/

Columns