VIEW: APPS.AR_LL_FREIGHT_CHARGES_V

Source

View Text - Preformatted

SELECT CASH_RECEIPT_ID, min(customer_trx_line_id) customer_trx_line_id, sum(ALLOCATED_RECEIPT_AMOUNT) ALLOCATED_RECEIPT_AMOUNT , sum(AMOUNT) AMOUNT, sum(FREIGHT_AMOUNT) FREIGHT_AMOUNT, sum(CHARGES_AMOUNT) CHARGES_AMOUNT, max(LAST_UPDATE_DATE) LAST_UPDATE_DATE, max(LAST_UPDATED_BY) LAST_UPDATED_BY, sum(LINE_DISCOUNT) LINE_DISCOUNT, max(CREATION_DATE) CREATION_DATE, max(CREATED_BY) CREATED_BY, max(LAST_UPDATE_LOGIN) LAST_UPDATE_LOGIN, max(COMMENTS) COMMENTS, APPLY_TO, max(created_by_module) created_by_module, min(object_version_number) object_version_number, customer_trx_id customer_trx_id, min(line_number) line_number, sum(line_balance) line_balance from ( select ll.CASH_RECEIPT_ID, ll.CUSTOMER_TRX_LINE_ID, ll.ALLOCATED_RECEIPT_AMOUNT, decode (line.line_type,'FREIGHT', ll.freight, 'CHARGES', ll.charges, 'LINE', nvl(ll.charges, 0) + nvl(ll.freight, 0)) AMOUNT, ll.freight FREIGHT_AMOUNT, ll.charges CHARGES_AMOUNT, ll.LAST_UPDATE_DATE, ll.LAST_UPDATED_BY, ll.freight_DISCOUNT line_discount, ll.CREATION_DATE, ll.CREATED_BY, ll.LAST_UPDATE_LOGIN, ll.COMMENTS, to_char(ll.APPLY_TO) apply_to, ll.created_by_module, ll.object_version_number, line.customer_trx_id, line.line_number line_number, decode(line.line_type, 'FREIGHT', line.amount_due_remaining, 'CHARGES', line.amount_due_remaining,'LINE', decode(ll.apply_to, 'FREIGHT', line.frt_adj_remaining,'CHARGES', line.chrg_amount_remaining)) line_balance from AR_ACTIVITY_DETAILS ll, ra_customer_trx_lines line WHERE line.customer_trx_line_id = ll.customer_trx_line_id and line.line_type in ('FREIGHT', 'CHARGES','LINE') and (nvl(ll.freight, 0) <> 0 OR nvl(ll.charges, 0) <> 0 OR nvl(ll.freight_discount, 0) <> 0) and nvl(ll.source_table,'RA') = 'RA' and nvl(ll.CURRENT_ACTIVITY_FLAG,'Y') = 'Y' ) frtchg group by cash_receipt_id,customer_trx_id,apply_to

View Text - HTML Formatted

SELECT CASH_RECEIPT_ID
, MIN(CUSTOMER_TRX_LINE_ID) CUSTOMER_TRX_LINE_ID
, SUM(ALLOCATED_RECEIPT_AMOUNT) ALLOCATED_RECEIPT_AMOUNT
, SUM(AMOUNT) AMOUNT
, SUM(FREIGHT_AMOUNT) FREIGHT_AMOUNT
, SUM(CHARGES_AMOUNT) CHARGES_AMOUNT
, MAX(LAST_UPDATE_DATE) LAST_UPDATE_DATE
, MAX(LAST_UPDATED_BY) LAST_UPDATED_BY
, SUM(LINE_DISCOUNT) LINE_DISCOUNT
, MAX(CREATION_DATE) CREATION_DATE
, MAX(CREATED_BY) CREATED_BY
, MAX(LAST_UPDATE_LOGIN) LAST_UPDATE_LOGIN
, MAX(COMMENTS) COMMENTS
, APPLY_TO
, MAX(CREATED_BY_MODULE) CREATED_BY_MODULE
, MIN(OBJECT_VERSION_NUMBER) OBJECT_VERSION_NUMBER
, CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, MIN(LINE_NUMBER) LINE_NUMBER
, SUM(LINE_BALANCE) LINE_BALANCE
FROM ( SELECT LL.CASH_RECEIPT_ID
, LL.CUSTOMER_TRX_LINE_ID
, LL.ALLOCATED_RECEIPT_AMOUNT
, DECODE (LINE.LINE_TYPE
, 'FREIGHT'
, LL.FREIGHT
, 'CHARGES'
, LL.CHARGES
, 'LINE'
, NVL(LL.CHARGES
, 0) + NVL(LL.FREIGHT
, 0)) AMOUNT
, LL.FREIGHT FREIGHT_AMOUNT
, LL.CHARGES CHARGES_AMOUNT
, LL.LAST_UPDATE_DATE
, LL.LAST_UPDATED_BY
, LL.FREIGHT_DISCOUNT LINE_DISCOUNT
, LL.CREATION_DATE
, LL.CREATED_BY
, LL.LAST_UPDATE_LOGIN
, LL.COMMENTS
, TO_CHAR(LL.APPLY_TO) APPLY_TO
, LL.CREATED_BY_MODULE
, LL.OBJECT_VERSION_NUMBER
, LINE.CUSTOMER_TRX_ID
, LINE.LINE_NUMBER LINE_NUMBER
, DECODE(LINE.LINE_TYPE
, 'FREIGHT'
, LINE.AMOUNT_DUE_REMAINING
, 'CHARGES'
, LINE.AMOUNT_DUE_REMAINING
, 'LINE'
, DECODE(LL.APPLY_TO
, 'FREIGHT'
, LINE.FRT_ADJ_REMAINING
, 'CHARGES'
, LINE.CHRG_AMOUNT_REMAINING)) LINE_BALANCE
FROM AR_ACTIVITY_DETAILS LL
, RA_CUSTOMER_TRX_LINES LINE
WHERE LINE.CUSTOMER_TRX_LINE_ID = LL.CUSTOMER_TRX_LINE_ID
AND LINE.LINE_TYPE IN ('FREIGHT'
, 'CHARGES'
, 'LINE')
AND (NVL(LL.FREIGHT
, 0) <> 0 OR NVL(LL.CHARGES
, 0) <> 0 OR NVL(LL.FREIGHT_DISCOUNT
, 0) <> 0)
AND NVL(LL.SOURCE_TABLE
, 'RA') = 'RA'
AND NVL(LL.CURRENT_ACTIVITY_FLAG
, 'Y') = 'Y' ) FRTCHG GROUP BY CASH_RECEIPT_ID
, CUSTOMER_TRX_ID
, APPLY_TO