View: AR_LL_LINES_GROUPS_V

Product:	AR - Receivables
Description:	Lines and Groups for Line-Level Cash Application
Implementation/DBA Data:	APPS.AR_LL_LINES_GROUPS_V

View Text

SELECT LL.CASH_RECEIPT_ID
, LL.CUSTOMER_TRX_LINE_ID
, LL.ALLOCATED_RECEIPT_AMOUNT
, LL.AMOUNT
, LL.TAX
, NVL(LL.AMOUNT
, 0) + NVL(LL.TAX
, 0) AMOUNT_APPLIED
, LL.LAST_UPDATE_DATE
, LL.LAST_UPDATED_BY
, LL.LINE_DISCOUNT
, LL.TAX_DISCOUNT
, LL.CREATION_DATE
, LL.CREATED_BY
, LL.LAST_UPDATE_LOGIN
, LL.COMMENTS
, TO_CHAR(LL.APPLY_TO) APPLY_TO
, LL.ATTRIBUTE1
, LL.ATTRIBUTE10
, LL.ATTRIBUTE11
, LL.ATTRIBUTE12
, LL.ATTRIBUTE13
, LL.ATTRIBUTE14
, LL.ATTRIBUTE15
, LL.ATTRIBUTE2
, LL.ATTRIBUTE3
, LL.ATTRIBUTE4
, LL.ATTRIBUTE5
, LL.ATTRIBUTE6
, LL.ATTRIBUTE7
, LL.ATTRIBUTE8
, LL.ATTRIBUTE9
, LL.ATTRIBUTE_CATEGORY
, LL.REFERENCE4
, LL.REFERENCE5
, TO_CHAR(LL.GROUP_ID) GROUP_ID
, LL.REFERENCE2
, LL.REFERENCE1
, LL.REFERENCE3
, LL.CREATED_BY_MODULE
, LL.OBJECT_VERSION_NUMBER
, LINE.CUSTOMER_TRX_ID
, LINE.SOURCE_DATA_KEY5 GROUP_NAME
, TO_CHAR(LINE.LINE_NUMBER) LINE_NUMBER
, LINE.AMOUNT_DUE_REMAINING LINE_BALANCE
, TAX.AMOUNT_DUE_REMAINING TAX_BALANCE
FROM AR_ACTIVITY_DETAILS LL
, RA_CUSTOMER_TRX_LINES LINE
, (SELECT CUSTOMER_TRX_ID
, 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 CUSTOMER_TRX_ID
, LINK_TO_CUST_TRX_LINE_ID
, LINE_TYPE ) TAX
WHERE LINE.CUSTOMER_TRX_LINE_ID = LL.CUSTOMER_TRX_LINE_ID
AND LINE.CUSTOMER_TRX_LINE_ID = TAX.LINK_TO_CUST_TRX_LINE_ID (+)
AND LINE.CUSTOMER_TRX_ID = TAX.CUSTOMER_TRX_ID(+)
AND LINE.LINE_TYPE = 'LINE'
AND NVL(LL.APPLY_TO
, '1') NOT IN ('FREIGHT'
, 'CHARGES')
AND NVL(LL.SOURCE_TABLE
, 'RA') = 'RA'
AND NVL(LL.CURRENT_ACTIVITY_FLAG
, 'Y') = 'Y' UNION (SELECT LL.CASH_RECEIPT_ID
, 0 CUSTOMER_TRX_LINE_ID
, SUM(LL.ALLOCATED_RECEIPT_AMOUNT) ALLOCATED_RECEIPT_AMOUNT
, SUM(LL.AMOUNT) AMOUNT
, SUM(LL.TAX) TAX
, SUM(NVL(LL.AMOUNT
, 0) + NVL(LL.TAX
, 0)) AMOUNT_APPLIED
, MAX(LL.LAST_UPDATE_DATE) LAST_UPDATE_DATE
, MAX(LL.LAST_UPDATED_BY) LAST_UPDATED_BY
, SUM(LL.LINE_DISCOUNT) LINE_DISCOUNT
, SUM(LL.TAX_DISCOUNT) TAX_DISCOUNT
, MAX(LL.CREATION_DATE) CREATION_DATE
, MAX(LL.CREATED_BY) CREATED_BY
, MAX(LL.LAST_UPDATE_LOGIN) LAST_UPDATE_LOGIN
, MAX(LL.COMMENTS) COMMENTS
, NVL(LINE.SOURCE_DATA_KEY5
, 'NOT GROUPED') APPLY_TO
, MAX(LL.ATTRIBUTE1) ATTRIBUTE1
, MAX(LL.ATTRIBUTE10) ATTRIBUTE10
, MAX(LL.ATTRIBUTE11) ATTRIBUTE11
, MAX(LL.ATTRIBUTE12) ATTRIBUTE12
, MAX(LL.ATTRIBUTE13) ATTRIBUTE13
, MAX(LL.ATTRIBUTE14) ATTRIBUTE14
, MAX(LL.ATTRIBUTE15) ATTRIBUTE15
, MAX(LL.ATTRIBUTE2) ATTRIBUTE2
, MAX(LL.ATTRIBUTE3) ATTRIBUTE3
, MAX(LL.ATTRIBUTE4) ATTRIBUTE4
, MAX(LL.ATTRIBUTE5) ATTRIBUTE5
, MAX(LL.ATTRIBUTE6) ATTRIBUTE6
, MAX(LL.ATTRIBUTE7) ATTRIBUTE7
, MAX(LL.ATTRIBUTE8) ATTRIBUTE8
, MAX(LL.ATTRIBUTE9) ATTRIBUTE9
, MAX(LL.ATTRIBUTE_CATEGORY) ATTRIBUTE_CATEGORY
, MAX(LL.REFERENCE4) REFERENCE4
, MAX(LL.REFERENCE5) REFERENCE5
, TO_CHAR(NVL(LINE.SOURCE_DATA_KEY4
, 0)) GROUP_ID
, MAX(LL.REFERENCE2) REFERENCE2
, MAX(LL.REFERENCE1) REFERENCE1
, MAX(LL.REFERENCE3) REFERENCE3
, MAX(LL.CREATED_BY_MODULE) CREATED_BY_MODULE
, SUM(LL.OBJECT_VERSION_NUMBER) OBJECT_VERSION_NUMBER
, LINE.CUSTOMER_TRX_ID
, NVL(LINE.SOURCE_DATA_KEY5
, 'NOT GROUPED') GROUP_NAME
, TO_CHAR(NVL(LINE.SOURCE_DATA_KEY4
, 0)) LINE_NUMBER
, SUM(LINE.AMOUNT_DUE_REMAINING) LINE_BALANCE
, SUM(TAX.AMOUNT_DUE_REMAINING) TAX_BALANCE
FROM AR_ACTIVITY_DETAILS LL
, RA_CUSTOMER_TRX_LINES LINE
, (SELECT CUSTOMER_TRX_ID
, 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 CUSTOMER_TRX_ID
, LINK_TO_CUST_TRX_LINE_ID
, LINE_TYPE ) TAX
WHERE LINE.CUSTOMER_TRX_LINE_ID = LL.CUSTOMER_TRX_LINE_ID
AND LINE.CUSTOMER_TRX_LINE_ID = TAX.LINK_TO_CUST_TRX_LINE_ID (+)
AND LINE.CUSTOMER_TRX_ID = TAX.CUSTOMER_TRX_ID(+)
AND LINE.LINE_TYPE = 'LINE'
AND NVL(LL.APPLY_TO
, '1') NOT IN ('FREIGHT'
, 'CHARGES')
AND NVL(LL.SOURCE_TABLE
, 'RA') = 'RA'
AND NVL(LL.CURRENT_ACTIVITY_FLAG
, 'Y') = 'Y' GROUP BY LL.CASH_RECEIPT_ID
, LINE.SOURCE_DATA_KEY4
, LINE.CUSTOMER_TRX_ID
, LINE.SOURCE_DATA_KEY5 )

Columns