VIEW: APPS.AR_LL_SUMMARY_V

Source

View Text - Preformatted

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*/

View Text - HTML Formatted

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*/