VIEW: APPS.AR_LL_LINES_GROUPS_V

Source

View Text - Preformatted

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 )

View Text - HTML Formatted

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 )