VIEW: APPS.AR_LL_SUMMARY_V

Source

View Text - Preformatted

SELECT cash_receipt_id, ad.customer_trx_id customer_trx_id, allocated_receipt_amount, line, tax, freight, charges, lin_discount, tax_discount, frt_discount, comments, created_by_module, object_version_number, lin_balance, tax_balance, frt_balance, chg_balance, last_update_date, last_updated_by, creation_date, created_by, last_update_login FROM (SELECT ll.cash_receipt_id, ctl.customer_trx_id, SUM(ll.allocated_receipt_amount) allocated_receipt_amount, SUM(ll.amount) line, SUM(ll.tax) tax, SUM(ll.freight) freight, SUM(ll.charges) charges, SUM(ll.line_discount) lin_discount, SUM(ll.tax_discount) tax_discount, SUM(ll.freight_discount) frt_discount, MAX(ll.comments) comments, MAX(ll.created_by_module) created_by_module, SUM(ll.object_version_number) object_version_number, MAX(ll.last_update_date) last_update_date, MAX(ll.last_updated_by) last_updated_by, MAX(ll.creation_date) creation_date, MAX(ll.created_by) created_by, MAX(ll.last_update_login) last_update_login FROM ar_activity_details ll, ra_customer_trx_lines ctl WHERE nvl(ll.source_table, 'RA') = 'RA' AND nvl(ll.current_activity_flag, 'Y') = 'Y' AND ctl.customer_trx_line_id = ll.customer_trx_line_id GROUP BY ll.cash_receipt_id, ctl.customer_trx_id) ad, (SELECT SUM(decode(line_type, 'LINE', amount_due_remaining, 0)) lin_balance, SUM(decode(line_type, 'TAX', amount_due_remaining, 0)) tax_balance, SUM(decode(line_type, 'LINE', frt_adj_remaining, 'FREIGHT', amount_due_remaining, 0)) frt_balance, SUM(decode(line_type, 'LINE', chrg_amount_remaining, 'CHARGES', amount_due_remaining, 0)) chg_balance, customer_trx_id FROM ra_customer_trx_lines GROUP BY customer_trx_id) lines WHERE ad.customer_trx_id = lines.customer_trx_id

View Text - HTML Formatted

SELECT CASH_RECEIPT_ID
, AD.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, ALLOCATED_RECEIPT_AMOUNT
, LINE
, TAX
, FREIGHT
, CHARGES
, LIN_DISCOUNT
, TAX_DISCOUNT
, FRT_DISCOUNT
, COMMENTS
, CREATED_BY_MODULE
, OBJECT_VERSION_NUMBER
, LIN_BALANCE
, TAX_BALANCE
, FRT_BALANCE
, CHG_BALANCE
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
FROM (SELECT LL.CASH_RECEIPT_ID
, CTL.CUSTOMER_TRX_ID
, SUM(LL.ALLOCATED_RECEIPT_AMOUNT) ALLOCATED_RECEIPT_AMOUNT
, SUM(LL.AMOUNT) LINE
, SUM(LL.TAX) TAX
, SUM(LL.FREIGHT) FREIGHT
, SUM(LL.CHARGES) CHARGES
, SUM(LL.LINE_DISCOUNT) LIN_DISCOUNT
, SUM(LL.TAX_DISCOUNT) TAX_DISCOUNT
, SUM(LL.FREIGHT_DISCOUNT) FRT_DISCOUNT
, MAX(LL.COMMENTS) COMMENTS
, MAX(LL.CREATED_BY_MODULE) CREATED_BY_MODULE
, SUM(LL.OBJECT_VERSION_NUMBER) OBJECT_VERSION_NUMBER
, MAX(LL.LAST_UPDATE_DATE) LAST_UPDATE_DATE
, MAX(LL.LAST_UPDATED_BY) LAST_UPDATED_BY
, MAX(LL.CREATION_DATE) CREATION_DATE
, MAX(LL.CREATED_BY) CREATED_BY
, MAX(LL.LAST_UPDATE_LOGIN) LAST_UPDATE_LOGIN
FROM AR_ACTIVITY_DETAILS LL
, RA_CUSTOMER_TRX_LINES CTL
WHERE NVL(LL.SOURCE_TABLE
, 'RA') = 'RA'
AND NVL(LL.CURRENT_ACTIVITY_FLAG
, 'Y') = 'Y'
AND CTL.CUSTOMER_TRX_LINE_ID = LL.CUSTOMER_TRX_LINE_ID GROUP BY LL.CASH_RECEIPT_ID
, CTL.CUSTOMER_TRX_ID) AD
, (SELECT SUM(DECODE(LINE_TYPE
, 'LINE'
, AMOUNT_DUE_REMAINING
, 0)) LIN_BALANCE
, SUM(DECODE(LINE_TYPE
, 'TAX'
, AMOUNT_DUE_REMAINING
, 0)) TAX_BALANCE
, SUM(DECODE(LINE_TYPE
, 'LINE'
, FRT_ADJ_REMAINING
, 'FREIGHT'
, AMOUNT_DUE_REMAINING
, 0)) FRT_BALANCE
, SUM(DECODE(LINE_TYPE
, 'LINE'
, CHRG_AMOUNT_REMAINING
, 'CHARGES'
, AMOUNT_DUE_REMAINING
, 0)) CHG_BALANCE
, CUSTOMER_TRX_ID
FROM RA_CUSTOMER_TRX_LINES GROUP BY CUSTOMER_TRX_ID) LINES
WHERE AD.CUSTOMER_TRX_ID = LINES.CUSTOMER_TRX_ID