VIEW: APPS.AR_BR_ACTIVITIES_V

Source

View Text - Preformatted

SELECT rec_app.rowid, rec_app.receivable_application_id, rec_app.cash_receipt_id, rcp_sch.trx_number, rec_app.applied_customer_trx_id, rec_app.apply_date, rec_app.gl_date, arl_cla.lookup_code, arl_cla.meaning, trx_sch.amount_due_original, trx_sch.amount_applied , to_number(null), /* br_amount_adjusted does not exist,*/ to_number(null), /*br_amount_exchanged does not exist,*/ trx_sch.amount_due_remaining, trx_sch.acctd_amount_due_remaining, -rec_app.amount_applied, rec_app.application_type, arl_typ.meaning, rcp_sch.invoice_currency_code, trx_sch.exchange_rate, rec_app.status, arl_sta.meaning, rec_app.creation_date, rec_app.created_by, rec_app.last_update_login, rec_app.last_update_date, rec_app.last_updated_by from ar_receivable_applications rec_app, ar_payment_schedules rcp_sch, ar_payment_schedules trx_sch, ra_cust_trx_types trx_typ, ar_lookups arl_typ, ar_lookups arl_sta, ar_lookups arl_cla where rec_app.payment_schedule_id = rcp_sch.payment_schedule_id and rec_app.applied_customer_trx_id = trx_sch.customer_trx_id and trx_sch.cust_trx_type_id = trx_typ.cust_trx_type_id and arl_cla.lookup_code = rcp_sch.class and arl_cla.lookup_type = 'INV/CM' and arl_sta.lookup_code = rec_app.status and arl_sta.lookup_type = 'PAYMENT_TYPE' and arl_typ.lookup_code = rec_app.application_type and arl_typ.lookup_type = 'APPLICATION_TYPE' and rec_app.applied_payment_schedule_id <> -2 /* STD should not be diplayed in History Window.*/ and trx_typ.type = 'BR' and arl_cla.lookup_code <> 'CM' and rec_app.status = 'APP' UNION ALL select adj.rowid, adj.adjustment_id, adj.adjustment_id, adj.adjustment_number, adj.customer_trx_id, adj.apply_date, adj.gl_date, arl_cla.lookup_code, arl_cla.meaning, trx_sch.amount_due_original, to_number(null), /*br_amount_applied does not exist,*/ trx_sch.amount_adjusted, to_number(null), /* br_amount_exchanged does not exist,*/ trx_sch.amount_due_remaining, trx_sch.acctd_amount_due_remaining, adj.amount, null, /* no type is required for adjustments. */ null, /*no type is required for adjustments. */ trx_sch.invoice_currency_code, trx_sch.exchange_rate, null, /* no status is required for adjustments. */ null, /* no status is required for adjustments. */ adj. creation_date, adj. created_by, adj. last_update_login, adj. last_update_date, adj. last_updated_by from ar_payment_schedules trx_sch, ar_adjustments adj, ra_cust_trx_types trx_typ, ar_lookups arl_cla where adj.payment_schedule_id = trx_sch.payment_schedule_id and trx_sch.cust_trx_type_id = trx_typ.cust_trx_type_id and arl_cla.lookup_code = 'ADJUSTMENT' and arl_cla.lookup_type = 'MISC_PHRASES' and adj.postable = 'Y' and adj.status = 'A' and trx_typ.type = 'BR' UNION ALL select adj_exch.rowid, adj_exch.adjustment_id, adj_exch.adjustment_id, adj_exch.adjustment_number, adj_exch.customer_trx_id, adj_exch.apply_date, adj_exch.gl_date, arl_cla.lookup_code, arl_cla.meaning, trx_sch.amount_due_original, to_number(null), /* br_amount_applied does not exist,*/ to_number(null), /* br_adjustment_applied does not exist, */ trx_sch.amount_adjusted, /* amount exhanged */ trx_sch.amount_due_remaining, trx_sch.acctd_amount_due_remaining, adj_exch.amount, null, /* no type is required for exchanges.*/ null, /* no type is required for exchanges. */ trx_sch.invoice_currency_code, trx_sch.exchange_rate, null, /* no status is required for exchanges. */ null, /* no status is required for exchanges. */ adj_exch. creation_date, adj_exch. created_by, adj_exch. last_update_login, adj_exch. last_update_date, adj_exch. last_updated_by from ar_payment_schedules trx_sch, ar_adjustments adj_exch, ar_lookups arl_cla where exists (select '' from ar_transaction_history_all hist where adj_exch.customer_trx_id = hist.customer_trx_id and hist.event = 'EXCHANGED' ) and adj_exch.payment_schedule_id = trx_sch.payment_schedule_id and arl_cla.lookup_code = 'ASSIGNMENT' and arl_cla.lookup_type = 'MISC_PHRASES' and adj_exch.postable = 'N' and adj_exch.status = 'A'

View Text - HTML Formatted

SELECT REC_APP.ROWID
, REC_APP.RECEIVABLE_APPLICATION_ID
, REC_APP.CASH_RECEIPT_ID
, RCP_SCH.TRX_NUMBER
, REC_APP.APPLIED_CUSTOMER_TRX_ID
, REC_APP.APPLY_DATE
, REC_APP.GL_DATE
, ARL_CLA.LOOKUP_CODE
, ARL_CLA.MEANING
, TRX_SCH.AMOUNT_DUE_ORIGINAL
, TRX_SCH.AMOUNT_APPLIED
, TO_NUMBER(NULL)
, /* BR_AMOUNT_ADJUSTED DOES NOT EXIST
, */ TO_NUMBER(NULL)
, /*BR_AMOUNT_EXCHANGED DOES NOT EXIST
, */ TRX_SCH.AMOUNT_DUE_REMAINING
, TRX_SCH.ACCTD_AMOUNT_DUE_REMAINING
, -REC_APP.AMOUNT_APPLIED
, REC_APP.APPLICATION_TYPE
, ARL_TYP.MEANING
, RCP_SCH.INVOICE_CURRENCY_CODE
, TRX_SCH.EXCHANGE_RATE
, REC_APP.STATUS
, ARL_STA.MEANING
, REC_APP.CREATION_DATE
, REC_APP.CREATED_BY
, REC_APP.LAST_UPDATE_LOGIN
, REC_APP.LAST_UPDATE_DATE
, REC_APP.LAST_UPDATED_BY
FROM AR_RECEIVABLE_APPLICATIONS REC_APP
, AR_PAYMENT_SCHEDULES RCP_SCH
, AR_PAYMENT_SCHEDULES TRX_SCH
, RA_CUST_TRX_TYPES TRX_TYP
, AR_LOOKUPS ARL_TYP
, AR_LOOKUPS ARL_STA
, AR_LOOKUPS ARL_CLA
WHERE REC_APP.PAYMENT_SCHEDULE_ID = RCP_SCH.PAYMENT_SCHEDULE_ID
AND REC_APP.APPLIED_CUSTOMER_TRX_ID = TRX_SCH.CUSTOMER_TRX_ID
AND TRX_SCH.CUST_TRX_TYPE_ID = TRX_TYP.CUST_TRX_TYPE_ID
AND ARL_CLA.LOOKUP_CODE = RCP_SCH.CLASS
AND ARL_CLA.LOOKUP_TYPE = 'INV/CM'
AND ARL_STA.LOOKUP_CODE = REC_APP.STATUS
AND ARL_STA.LOOKUP_TYPE = 'PAYMENT_TYPE'
AND ARL_TYP.LOOKUP_CODE = REC_APP.APPLICATION_TYPE
AND ARL_TYP.LOOKUP_TYPE = 'APPLICATION_TYPE'
AND REC_APP.APPLIED_PAYMENT_SCHEDULE_ID <> -2 /* STD SHOULD NOT BE DIPLAYED IN HISTORY WINDOW.*/
AND TRX_TYP.TYPE = 'BR'
AND ARL_CLA.LOOKUP_CODE <> 'CM'
AND REC_APP.STATUS = 'APP' UNION ALL SELECT ADJ.ROWID
, ADJ.ADJUSTMENT_ID
, ADJ.ADJUSTMENT_ID
, ADJ.ADJUSTMENT_NUMBER
, ADJ.CUSTOMER_TRX_ID
, ADJ.APPLY_DATE
, ADJ.GL_DATE
, ARL_CLA.LOOKUP_CODE
, ARL_CLA.MEANING
, TRX_SCH.AMOUNT_DUE_ORIGINAL
, TO_NUMBER(NULL)
, /*BR_AMOUNT_APPLIED DOES NOT EXIST
, */ TRX_SCH.AMOUNT_ADJUSTED
, TO_NUMBER(NULL)
, /* BR_AMOUNT_EXCHANGED DOES NOT EXIST
, */ TRX_SCH.AMOUNT_DUE_REMAINING
, TRX_SCH.ACCTD_AMOUNT_DUE_REMAINING
, ADJ.AMOUNT
, NULL
, /* NO TYPE IS REQUIRED FOR ADJUSTMENTS. */ NULL
, /*NO TYPE IS REQUIRED FOR ADJUSTMENTS. */ TRX_SCH.INVOICE_CURRENCY_CODE
, TRX_SCH.EXCHANGE_RATE
, NULL
, /* NO STATUS IS REQUIRED FOR ADJUSTMENTS. */ NULL
, /* NO STATUS IS REQUIRED FOR ADJUSTMENTS. */ ADJ. CREATION_DATE
, ADJ. CREATED_BY
, ADJ. LAST_UPDATE_LOGIN
, ADJ. LAST_UPDATE_DATE
, ADJ. LAST_UPDATED_BY
FROM AR_PAYMENT_SCHEDULES TRX_SCH
, AR_ADJUSTMENTS ADJ
, RA_CUST_TRX_TYPES TRX_TYP
, AR_LOOKUPS ARL_CLA
WHERE ADJ.PAYMENT_SCHEDULE_ID = TRX_SCH.PAYMENT_SCHEDULE_ID
AND TRX_SCH.CUST_TRX_TYPE_ID = TRX_TYP.CUST_TRX_TYPE_ID
AND ARL_CLA.LOOKUP_CODE = 'ADJUSTMENT'
AND ARL_CLA.LOOKUP_TYPE = 'MISC_PHRASES'
AND ADJ.POSTABLE = 'Y'
AND ADJ.STATUS = 'A'
AND TRX_TYP.TYPE = 'BR' UNION ALL SELECT ADJ_EXCH.ROWID
, ADJ_EXCH.ADJUSTMENT_ID
, ADJ_EXCH.ADJUSTMENT_ID
, ADJ_EXCH.ADJUSTMENT_NUMBER
, ADJ_EXCH.CUSTOMER_TRX_ID
, ADJ_EXCH.APPLY_DATE
, ADJ_EXCH.GL_DATE
, ARL_CLA.LOOKUP_CODE
, ARL_CLA.MEANING
, TRX_SCH.AMOUNT_DUE_ORIGINAL
, TO_NUMBER(NULL)
, /* BR_AMOUNT_APPLIED DOES NOT EXIST
, */ TO_NUMBER(NULL)
, /* BR_ADJUSTMENT_APPLIED DOES NOT EXIST
, */ TRX_SCH.AMOUNT_ADJUSTED
, /* AMOUNT EXHANGED */ TRX_SCH.AMOUNT_DUE_REMAINING
, TRX_SCH.ACCTD_AMOUNT_DUE_REMAINING
, ADJ_EXCH.AMOUNT
, NULL
, /* NO TYPE IS REQUIRED FOR EXCHANGES.*/ NULL
, /* NO TYPE IS REQUIRED FOR EXCHANGES. */ TRX_SCH.INVOICE_CURRENCY_CODE
, TRX_SCH.EXCHANGE_RATE
, NULL
, /* NO STATUS IS REQUIRED FOR EXCHANGES. */ NULL
, /* NO STATUS IS REQUIRED FOR EXCHANGES. */ ADJ_EXCH. CREATION_DATE
, ADJ_EXCH. CREATED_BY
, ADJ_EXCH. LAST_UPDATE_LOGIN
, ADJ_EXCH. LAST_UPDATE_DATE
, ADJ_EXCH. LAST_UPDATED_BY
FROM AR_PAYMENT_SCHEDULES TRX_SCH
, AR_ADJUSTMENTS ADJ_EXCH
, AR_LOOKUPS ARL_CLA
WHERE EXISTS (SELECT ''
FROM AR_TRANSACTION_HISTORY_ALL HIST
WHERE ADJ_EXCH.CUSTOMER_TRX_ID = HIST.CUSTOMER_TRX_ID
AND HIST.EVENT = 'EXCHANGED' )
AND ADJ_EXCH.PAYMENT_SCHEDULE_ID = TRX_SCH.PAYMENT_SCHEDULE_ID
AND ARL_CLA.LOOKUP_CODE = 'ASSIGNMENT'
AND ARL_CLA.LOOKUP_TYPE = 'MISC_PHRASES'
AND ADJ_EXCH.POSTABLE = 'N'
AND ADJ_EXCH.STATUS = 'A'