eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.AR_CM_ACTIVITIES_V

Source

View Text - Preformatted


SELECT PS.CUSTOMER_ID, 'CM APPLICATION' activity_source, APP.PAYMENT_SCHEDULE_ID , APP.CUSTOMER_TRX_ID , APP.APPLIED_PAYMENT_SCHEDULE_ID activity_ps_id, APP.APPLIED_CUSTOMER_TRX_ID activity_id, PS.TRX_NUMBER activity_number, PS.CLASS activity_class, ARPT_SQL_FUNC_UTIL.get_lookup_meaning('INV/CM',PS.CLASS), ARPT_SQL_FUNC_UTIL.get_lookup_meaning('PAYMENT_TYPE',APP.STATUS), PS.TERMS_SEQUENCE_NUMBER activity_tsn, APP.APPLY_DATE activity_date, APP.AMOUNT_APPLIED activity_amount, nvl(PS.AMOUNT_DUE_ORIGINAL,0) original_amount, nvl(PS.AMOUNT_DUE_REMAINING,0) remaining_amount, nvl(PS.AMOUNT_LINE_ITEMS_ORIGINAL,0) line_amount, nvl(PS.TAX_ORIGINAL,0) tax_amount, nvl(PS.FREIGHT_ORIGINAL,0) freight_amount, nvl(PS.RECEIVABLES_CHARGES_CHARGED,0) charges_amount, (nvl(PS.AMOUNT_LINE_ITEMS_ORIGINAL,0) + nvl(PS.FREIGHT_ORIGINAL,0) + nvl(PS.TAX_ORIGINAL,0) + nvl(PS.RECEIVABLES_CHARGES_CHARGED,0)) total_amount FROM AR_PAYMENT_SCHEDULES PS, AR_RECEIVABLE_APPLICATIONS APP WHERE APP.STATUS = 'APP' AND APP.APPLICATION_TYPE = 'CM' AND APP.APPLIED_PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID AND APP.DISPLAY='Y' UNION ALL SELECT PS.CUSTOMER_ID, 'CM ADJUSTMENT' activity_source, ADJ.PAYMENT_SCHEDULE_ID, ADJ.CUSTOMER_TRX_ID, to_number(null) activity_ps_id, ADJ.ADJUSTMENT_ID activity_id, ADJ.ADJUSTMENT_NUMBER activity_number, 'ADJ' activity_class, ARPT_SQL_FUNC_UTIL.get_lookup_meaning('INV/CM/ADJ','ADJ'), ARPT_SQL_FUNC_UTIL.get_lookup_meaning('APPROVAL_TYPE',ADJ.STATUS), PS.TERMS_SEQUENCE_NUMBER activity_tsn, ADJ.APPLY_DATE activity_date, ADJ.AMOUNT activity_amount, nvl(PS.AMOUNT_DUE_ORIGINAL,0) original_amount, nvl(PS.AMOUNT_DUE_REMAINING,0) remaining_amount, nvl(PS.AMOUNT_LINE_ITEMS_ORIGINAL,0) line_amount, nvl(PS.TAX_ORIGINAL,0) tax_amount, nvl(PS.FREIGHT_ORIGINAL,0) freight_amount, nvl(PS.RECEIVABLES_CHARGES_CHARGED,0) charges_amount, (nvl(PS.AMOUNT_LINE_ITEMS_ORIGINAL,0) + nvl(PS.FREIGHT_ORIGINAL,0) + nvl(PS.TAX_ORIGINAL,0) + nvl(PS.RECEIVABLES_CHARGES_CHARGED,0)) total_amount FROM AR_ADJUSTMENTS ADJ, AR_PAYMENT_SCHEDULES PS WHERE ADJ.PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID UNION ALL SELECT PS.CUSTOMER_ID, 'RECEIPT APPLIED ON CM' activity_source, APP.APPLIED_PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID, APP.APPLIED_CUSTOMER_TRX_ID CUSTOMER_TRX_ID, APP.PAYMENT_SCHEDULE_ID activity_ps_id, APP.CASH_RECEIPT_ID activity_id, PS.TRX_NUMBER activity_number, PS.CLASS activity_class, ARPT_SQL_FUNC_UTIL.get_lookup_meaning('INV/CM',PS.CLASS), ARPT_SQL_FUNC_UTIL.get_lookup_meaning('PAYMENT_TYPE',APP.STATUS), PS.TERMS_SEQUENCE_NUMBER activity_tsn, APP.APPLY_DATE activity_date, NVL(APP.AMOUNT_APPLIED_FROM, APP.AMOUNT_APPLIED) activity_amount, nvl(PS.AMOUNT_DUE_ORIGINAL,0) original_amount, nvl(PS.AMOUNT_DUE_REMAINING,0) remaining_amount, nvl(PS.AMOUNT_LINE_ITEMS_ORIGINAL,0) line_amount, nvl(PS.TAX_ORIGINAL,0) tax_amount, nvl(PS.FREIGHT_ORIGINAL,0) freight_amount, nvl(PS.RECEIVABLES_CHARGES_CHARGED,0) charges_amount, (nvl(PS.AMOUNT_LINE_ITEMS_ORIGINAL,0) + nvl(PS.FREIGHT_ORIGINAL,0) + nvl(PS.TAX_ORIGINAL,0) + nvl(PS.RECEIVABLES_CHARGES_CHARGED,0)) total_amount FROM AR_PAYMENT_SCHEDULES PS, AR_RECEIVABLE_APPLICATIONS APP WHERE APP.PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID AND APP.STATUS = 'APP' AND APP.DISPLAY = 'Y'

View Text - HTML Formatted

SELECT PS.CUSTOMER_ID
, 'CM APPLICATION' ACTIVITY_SOURCE
, APP.PAYMENT_SCHEDULE_ID
, APP.CUSTOMER_TRX_ID
, APP.APPLIED_PAYMENT_SCHEDULE_ID ACTIVITY_PS_ID
, APP.APPLIED_CUSTOMER_TRX_ID ACTIVITY_ID
, PS.TRX_NUMBER ACTIVITY_NUMBER
, PS.CLASS ACTIVITY_CLASS
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INV/CM'
, PS.CLASS)
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('PAYMENT_TYPE'
, APP.STATUS)
, PS.TERMS_SEQUENCE_NUMBER ACTIVITY_TSN
, APP.APPLY_DATE ACTIVITY_DATE
, APP.AMOUNT_APPLIED ACTIVITY_AMOUNT
, NVL(PS.AMOUNT_DUE_ORIGINAL
, 0) ORIGINAL_AMOUNT
, NVL(PS.AMOUNT_DUE_REMAINING
, 0) REMAINING_AMOUNT
, NVL(PS.AMOUNT_LINE_ITEMS_ORIGINAL
, 0) LINE_AMOUNT
, NVL(PS.TAX_ORIGINAL
, 0) TAX_AMOUNT
, NVL(PS.FREIGHT_ORIGINAL
, 0) FREIGHT_AMOUNT
, NVL(PS.RECEIVABLES_CHARGES_CHARGED
, 0) CHARGES_AMOUNT
, (NVL(PS.AMOUNT_LINE_ITEMS_ORIGINAL
, 0) + NVL(PS.FREIGHT_ORIGINAL
, 0) + NVL(PS.TAX_ORIGINAL
, 0) + NVL(PS.RECEIVABLES_CHARGES_CHARGED
, 0)) TOTAL_AMOUNT
FROM AR_PAYMENT_SCHEDULES PS
, AR_RECEIVABLE_APPLICATIONS APP
WHERE APP.STATUS = 'APP'
AND APP.APPLICATION_TYPE = 'CM'
AND APP.APPLIED_PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID
AND APP.DISPLAY='Y' UNION ALL SELECT PS.CUSTOMER_ID
, 'CM ADJUSTMENT' ACTIVITY_SOURCE
, ADJ.PAYMENT_SCHEDULE_ID
, ADJ.CUSTOMER_TRX_ID
, TO_NUMBER(NULL) ACTIVITY_PS_ID
, ADJ.ADJUSTMENT_ID ACTIVITY_ID
, ADJ.ADJUSTMENT_NUMBER ACTIVITY_NUMBER
, 'ADJ' ACTIVITY_CLASS
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INV/CM/ADJ'
, 'ADJ')
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('APPROVAL_TYPE'
, ADJ.STATUS)
, PS.TERMS_SEQUENCE_NUMBER ACTIVITY_TSN
, ADJ.APPLY_DATE ACTIVITY_DATE
, ADJ.AMOUNT ACTIVITY_AMOUNT
, NVL(PS.AMOUNT_DUE_ORIGINAL
, 0) ORIGINAL_AMOUNT
, NVL(PS.AMOUNT_DUE_REMAINING
, 0) REMAINING_AMOUNT
, NVL(PS.AMOUNT_LINE_ITEMS_ORIGINAL
, 0) LINE_AMOUNT
, NVL(PS.TAX_ORIGINAL
, 0) TAX_AMOUNT
, NVL(PS.FREIGHT_ORIGINAL
, 0) FREIGHT_AMOUNT
, NVL(PS.RECEIVABLES_CHARGES_CHARGED
, 0) CHARGES_AMOUNT
, (NVL(PS.AMOUNT_LINE_ITEMS_ORIGINAL
, 0) + NVL(PS.FREIGHT_ORIGINAL
, 0) + NVL(PS.TAX_ORIGINAL
, 0) + NVL(PS.RECEIVABLES_CHARGES_CHARGED
, 0)) TOTAL_AMOUNT
FROM AR_ADJUSTMENTS ADJ
, AR_PAYMENT_SCHEDULES PS
WHERE ADJ.PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID UNION ALL SELECT PS.CUSTOMER_ID
, 'RECEIPT APPLIED ON CM' ACTIVITY_SOURCE
, APP.APPLIED_PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID
, APP.APPLIED_CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, APP.PAYMENT_SCHEDULE_ID ACTIVITY_PS_ID
, APP.CASH_RECEIPT_ID ACTIVITY_ID
, PS.TRX_NUMBER ACTIVITY_NUMBER
, PS.CLASS ACTIVITY_CLASS
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INV/CM'
, PS.CLASS)
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('PAYMENT_TYPE'
, APP.STATUS)
, PS.TERMS_SEQUENCE_NUMBER ACTIVITY_TSN
, APP.APPLY_DATE ACTIVITY_DATE
, NVL(APP.AMOUNT_APPLIED_FROM
, APP.AMOUNT_APPLIED) ACTIVITY_AMOUNT
, NVL(PS.AMOUNT_DUE_ORIGINAL
, 0) ORIGINAL_AMOUNT
, NVL(PS.AMOUNT_DUE_REMAINING
, 0) REMAINING_AMOUNT
, NVL(PS.AMOUNT_LINE_ITEMS_ORIGINAL
, 0) LINE_AMOUNT
, NVL(PS.TAX_ORIGINAL
, 0) TAX_AMOUNT
, NVL(PS.FREIGHT_ORIGINAL
, 0) FREIGHT_AMOUNT
, NVL(PS.RECEIVABLES_CHARGES_CHARGED
, 0) CHARGES_AMOUNT
, (NVL(PS.AMOUNT_LINE_ITEMS_ORIGINAL
, 0) + NVL(PS.FREIGHT_ORIGINAL
, 0) + NVL(PS.TAX_ORIGINAL
, 0) + NVL(PS.RECEIVABLES_CHARGES_CHARGED
, 0)) TOTAL_AMOUNT
FROM AR_PAYMENT_SCHEDULES PS
, AR_RECEIVABLE_APPLICATIONS APP
WHERE APP.PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID
AND APP.STATUS = 'APP'
AND APP.DISPLAY = 'Y'