Trigger to update a column based on another column in oracle receipts.

 CREATE or REPLACE TRIGGER APPS.XX_PO_BOXES_TRG

BEFORE INSERT OR UPDATE ON AR.AR_CASH_RECEIPTS_ALL

FOR EACH ROW

DECLARE

po_box_by_call_lock varchar2(20);

po_box_by_call  varchar2(20);

BEGIN

po_box_by_call_lock := NULL;

po_box_by_call:= NULL;

 --Checking whether Network/Station field is not null

IF :new.attribute3 IS NOT NULL  THEN

-- Getting po box number for attribute8 when call letter is present

SELECT tag

INTO po_box_by_call_lock

FROM FND_LOOKUP_VALUES

WHERE lookup_type = 'XX_PO_BOXES'

AND language = 'US'

AND lookup_code = UPPER(:new.attribute3)

UNION

SELECT '0'||lockbox_number tag

--INTO po_box_by_call

from AR_LOCKBOXES_ALL

   where status ='A'

and ( lockbox_number =  :new.attribute3

or '0'||lockbox_number= :new.attribute3);

-- If Call Letter is present in Lookup created or lockbox table.

IF po_box_by_call_lock IS NOT NULL THEN

IF INSERTING THEN

:new.attribute8 := po_box_by_call_lock;

ELSIF UPDATING THEN

:new.attribute8 := po_box_by_call_lock;

END IF;

-- Comparing Attribute 3 and Attribute8

IF :new.attribute8 = :new.attribute3 THEN

    :new.attribute3 := NULL;

END IF;

    END IF;

END IF;

 EXCEPTION WHEN OTHERS THEN

 :new.attribute3 := NULL;

END;

Oracle AR Receipt Detail Report

SELECT TO_CHAR(sysdate,'DD-MON-YYYY HH24:MI:SS') report_run_date,

                  onaccount_amount,

  unapplied_amount,

  unidentified_amount,

  manually_applied_amount,

  auto_applied_amount,

  receipt_amount,

  receipt_number,

  to_char(receipt_date,'DD-MON-YYYY') receipt_date,

  to_char(apply_date,'DD-MON-YYYY') apply_date,

  receipt_method,

  decode(status,'APP','Applied','UNAPP','Unapplied','UNID','Unidentified','Reversed') status,

  pobox,

  station,

  to_char(gl_date,'DD-MON-YYYY') gl_date,

  customer_name, 

  customer_number, 

  masked_account_num, 

  decode(receipt_type,'CASH','Standard','MISC','Miscellaneous',receipt_type) receipt_type,

  market

   FROM 

(

SELECT sum(onaccount_amount)onaccount_amount,

       sum(unapplied_amount)unapplied_amount, 

   sum(manually_applied_amount) manually_applied_amount,

   sum(auto_applied_amount) auto_applied_amount, 

   sum(unidentified_amount) unidentified_amount,

   receipt_number, 

   receipt_date, 

   receipt_amount, 

   max(apply_date) apply_date , 

   status , 

   pobox, 

   station,

   receipt_method, 

   gl_date, 

   customer_name, 

   customer_number, 

   masked_account_num, 

   receipt_type, 

   market

FROM

(

--UNAPPLIED RECEIPT AMOUNTS

select onaccount_amount, sum(unapplied_amount)unapplied_amount, unidentified_amount, manually_applied_amount,  auto_applied_amount,

      receipt_number, receipt_date, receipt_amount, apply_date , status, pobox,station, receipt_method, gl_date, customer_name,

      customer_number, masked_account_num, receipt_type, market

from

( 

--UNAPPLIED AMOUNTS

select 0 onaccount_amount,

       sum(araa.amount_applied) unapplied_amount,

       0 unidentified_amount, 

       0 manually_applied_amount,

       0 Auto_applied_amount,

       acra.amount receipt_amount,

   acra.receipt_number,

       acra.receipt_date,       

       max(apply_date) apply_date,

       acra.status,

       acra.attribute8 pobox,

       acra.attribute3 station,

       arm.name receipt_method,

       arpsa.gl_date,

       ac.customer_name,

       ac.customer_number,

       CBA.MASKED_ACCOUNT_NUM,

       acra.type receipt_type,

 (select ffvt.description 

from fnd_flex_values  ffv,

     fnd_flex_values_tl ffvt

where flex_value = acra.attribute8

and ffv.flex_value_id = ffvt.flex_value_id

and flex_value_set_id = '1010389'

and ffv.enabled_flag ='Y'

and ffvt.language= 'US') market

from ar_receivable_applications_all araa,

     ar_cash_receipts_all acra,

     ar_receipt_methods arm,

     ar_payment_schedules_all arpsa,

     ar_customers ac,

     CE_BANK_ACCOUNTS CBA, 

     CE_BANK_ACCT_USES_ALL ABA, 

     CE_BANK_BRANCHES_V ABB

where araa.status = 'UNAPP'

and acra.cash_receipt_id = araa.cash_receipt_id

and arm.receipt_method_id = acra.receipt_method_id

and arpsa.payment_schedule_id = araa.payment_schedule_id

and ac.customer_id(+) = arpsa.customer_id

and acra.REMIT_BANK_ACCT_USE_ID = ABA.BANK_ACCT_USE_ID

and  CBA.BANK_BRANCH_ID = ABB.BRANCH_PARTY_ID

and ABA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID

and trunc(acra.receipt_date) between  nvl(:P_RECEIPT_FROM_DATE,trunc(acra.receipt_date)) and nvl(:P_RECEIPT_TO_DATE,trunc(acra.receipt_date))

and trunc(arpsa.gl_date) between  nvl(:P_GL_FROM_DATE,trunc(arpsa.gl_date)) and nvl(:P_GL_TO_DATE,trunc(arpsa.gl_date))

and acra.set_of_books_id = '1001'

group by acra.receipt_number,acra.receipt_date,acra.status,

       acra.attribute8,acra.attribute3,arm.name, acra.amount,

       arpsa.gl_date,

       ac.customer_name,

       ac.customer_number,CBA.MASKED_ACCOUNT_NUM,acra.type )

group by onaccount_amount, auto_applied_amount, unidentified_amount, manually_applied_amount, 

      receipt_number, receipt_date, receipt_amount, apply_date , status, pobox,station, receipt_method, gl_date, customer_name,

      customer_number, masked_account_num, receipt_type, market

UNION

--UNIDENTIFIED RECEIPT AMOUNTS

select   onaccount_amount, unapplied_amount,sum(unidentified_amount) unidentified_amount, manually_applied_amount,  auto_applied_amount,

      receipt_number, receipt_date, receipt_amount, apply_date , status, pobox,station, receipt_method, gl_date, customer_name,

      customer_number, masked_account_num, receipt_type, market

from

(

select 0 onaccount_amount,

       0 unapplied_amount,

       sum(araa.amount_applied) unidentified_amount, 

       0 manually_applied_amount,

       0 Auto_applied_amount,

       acra.amount receipt_amount,

   acra.receipt_number,

       acra.receipt_date,       

       apply_date apply_date,

       acra.status,

       acra.attribute8 pobox,

       acra.attribute3 station,

       arm.name receipt_method,

       arpsa.gl_date,

       ac.customer_name,

       ac.customer_number,

       CBA.MASKED_ACCOUNT_NUM,

       acra.type receipt_type,

 (select ffvt.description 

from fnd_flex_values  ffv,

     fnd_flex_values_tl ffvt

where flex_value = acra.attribute8

and ffv.flex_value_id = ffvt.flex_value_id

and flex_value_set_id = '1010389'

and ffv.enabled_flag ='Y'

and ffvt.language= 'US') market

from ar_receivable_applications_all araa,

     ar_cash_receipts_all acra,

     ar_receipt_methods arm,

     ar_payment_schedules_all arpsa,

     ar_customers ac,

     CE_BANK_ACCOUNTS CBA, 

     CE_BANK_ACCT_USES_ALL ABA, 

     CE_BANK_BRANCHES_V ABB

where araa.status = 'UNID'

and acra.cash_receipt_id = araa.cash_receipt_id

and arm.receipt_method_id = acra.receipt_method_id

and arpsa.payment_schedule_id = araa.payment_schedule_id

and ac.customer_id(+) = arpsa.customer_id

and acra.REMIT_BANK_ACCT_USE_ID = ABA.BANK_ACCT_USE_ID

and  CBA.BANK_BRANCH_ID = ABB.BRANCH_PARTY_ID

and ABA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID

and trunc(acra.receipt_date) between  nvl(:P_RECEIPT_FROM_DATE,trunc(acra.receipt_date)) and nvl(:P_RECEIPT_TO_DATE,trunc(acra.receipt_date))

and trunc(arpsa.gl_date) between  nvl(:P_GL_FROM_DATE,trunc(arpsa.gl_date)) and nvl(:P_GL_TO_DATE,trunc(arpsa.gl_date))

and acra.set_of_books_id = '1001'

group by acra.amount ,

   acra.receipt_number,

       acra.receipt_date,       

       apply_date ,

       acra.status,

       acra.attribute8 ,

       acra.attribute3 ,

       arm.name ,

       arpsa.gl_date,

       ac.customer_name,

       ac.customer_number,

       CBA.MASKED_ACCOUNT_NUM,

       acra.type 

having sum(araa.amount_applied) > 0

)

group by auto_applied_amount, unapplied_amount, onaccount_amount, manually_applied_amount, 

      receipt_number, receipt_date, receipt_amount, apply_date , status, pobox,station, receipt_method, gl_date, customer_name,

      customer_number, masked_account_num, receipt_type, market

UNION

--ONACCOUNT RECEIPT AMOUNTS

select  sum(onaccount_amount) onaccount_amount, unapplied_amount, unidentified_amount, manually_applied_amount,  auto_applied_amount,

      receipt_number, receipt_date, receipt_amount, apply_date , status, pobox,station, receipt_method, gl_date, customer_name,

      customer_number, masked_account_num, receipt_type, market

from

(

select sum(araa.amount_applied) onaccount_amount,

       0 unapplied_amount,

       0 unidentified_amount, 

       0 manually_applied_amount,

       0 Auto_applied_amount,

       acra.amount receipt_amount,

   acra.receipt_number,

       acra.receipt_date,       

       max(apply_date) apply_date,

       acra.status,

       acra.attribute8 pobox,

       acra.attribute3 station,

       arm.name receipt_method,

       arpsa.gl_date,

       ac.customer_name,

       ac.customer_number,

       CBA.MASKED_ACCOUNT_NUM,

       acra.type receipt_type,

 (select ffvt.description 

from fnd_flex_values  ffv,

     fnd_flex_values_tl ffvt

where flex_value = acra.attribute8

and ffv.flex_value_id = ffvt.flex_value_id

and flex_value_set_id = '1010389'

and ffv.enabled_flag ='Y'

and ffvt.language= 'US') market

from ar_receivable_applications_all araa,

     ar_cash_receipts_all acra,

     ar_receipt_methods arm,

     ar_payment_schedules_all arpsa,

     ar_customers ac,

     CE_BANK_ACCOUNTS CBA, 

     CE_BANK_ACCT_USES_ALL ABA, 

     CE_BANK_BRANCHES_V ABB

where  araa.status = 'ACC'

and acra.cash_receipt_id = araa.cash_receipt_id

and arm.receipt_method_id = acra.receipt_method_id

and arpsa.payment_schedule_id = araa.payment_schedule_id

and ac.customer_id(+)  = arpsa.customer_id

and acra.REMIT_BANK_ACCT_USE_ID = ABA.BANK_ACCT_USE_ID

and  CBA.BANK_BRANCH_ID = ABB.BRANCH_PARTY_ID

and ABA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID

and trunc(acra.receipt_date) between  nvl(:P_RECEIPT_FROM_DATE,trunc(acra.receipt_date)) and nvl(:P_RECEIPT_TO_DATE,trunc(acra.receipt_date))

and trunc(arpsa.gl_date) between  nvl(:P_GL_FROM_DATE,trunc(arpsa.gl_date)) and nvl(:P_GL_TO_DATE,trunc(arpsa.gl_date))

and acra.set_of_books_id = '1001'

and araa.display = 'Y'

group by acra.receipt_number,acra.receipt_date,acra.status,

       acra.attribute8,acra.attribute3,arm.name, acra.amount,

       arpsa.gl_date,

       ac.customer_name,

       ac.customer_number,CBA.MASKED_ACCOUNT_NUM,acra.type )

group by auto_applied_amount, unapplied_amount, unidentified_amount, manually_applied_amount, 

      receipt_number, receipt_date, receipt_amount, apply_date , status, pobox,station, receipt_method, gl_date, customer_name,

      customer_number, masked_account_num, receipt_type, market

UNION

--Receipt Write Off amount as Manual Applied Amount

select onaccount_amount, unapplied_amount,unidentified_amount, sum(manually_applied_amount) manually_applied_amount, auto_applied_amount, receipt_number, receipt_date, 

receipt_amount,apply_date, status, pobox,station,receipt_method,gl_date, customer_name, customer_number, masked_account_num,receipt_type,market

from (

select 0 onaccount_amount,

       0 unapplied_amount,

       0 unidentified_amount,

       araa.amount_applied manually_applied_amount,

       0 Auto_applied_amount,

       acra.amount receipt_amount,

       acra.receipt_number,

       acra.receipt_date,

       apply_date ,

       acra.status,

       acra.attribute8 pobox,

       acra.attribute3 station,

       arm.name receipt_method,

       arpsa.gl_date,

       ac.customer_name,

       ac.customer_number,

       CBA.MASKED_ACCOUNT_NUM,

       acra.type receipt_type,

 (select ffvt.description 

from fnd_flex_values  ffv,

     fnd_flex_values_tl ffvt

where flex_value = acra.attribute8

and ffv.flex_value_id = ffvt.flex_value_id

and flex_value_set_id = '1010389'

and ffv.enabled_flag ='Y'

and ffvt.language= 'US') market

from ar_receivable_applications_all araa,

     ar_cash_receipts_all acra,

     ar_receipt_methods arm,

     ar_payment_schedules_all arpsa,

     ar_customers ac,

     CE_BANK_ACCOUNTS CBA, 

     CE_BANK_ACCT_USES_ALL ABA, 

     CE_BANK_BRANCHES_V ABB

where 1=1 --and  receipt_number = '275944750084'

and araa.status in ('ACTIVITY')

and acra.cash_receipt_id = araa.cash_receipt_id

and arm.receipt_method_id = acra.receipt_method_id

and arpsa.payment_schedule_id = araa.payment_schedule_id

and ac.customer_id(+) = arpsa.customer_id

and acra.REMIT_BANK_ACCT_USE_ID = ABA.BANK_ACCT_USE_ID

and  CBA.BANK_BRANCH_ID = ABB.BRANCH_PARTY_ID

and ABA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID

and trunc(acra.receipt_date) between  nvl(:P_RECEIPT_FROM_DATE,trunc(acra.receipt_date)) and nvl(:P_RECEIPT_TO_DATE,trunc(acra.receipt_date))

and trunc(arpsa.gl_date) between  nvl(:P_GL_FROM_DATE,trunc(arpsa.gl_date)) and nvl(:P_GL_TO_DATE,trunc(arpsa.gl_date))

and acra.set_of_books_id = '1001'

and display ='Y'

and araa.receivable_application_id not in (

select araa.receivable_application_id

from ar_receivable_applications_all araa,

     ar_cash_receipts_all acra,

     ar_receipt_methods arm,

     ar_payment_schedules_all arpsa,

     ar_customers ac,

     CE_BANK_ACCOUNTS CBA, 

     CE_BANK_ACCT_USES_ALL ABA, 

     CE_BANK_BRANCHES_V ABB

where araa.status in ('APP')

and araa.display = 'Y'

and acra.cash_receipt_id = araa.cash_receipt_id

and arm.receipt_method_id = acra.receipt_method_id

and arpsa.payment_schedule_id = araa.payment_schedule_id

and ac.customer_id(+) = arpsa.customer_id

and acra.REMIT_BANK_ACCT_USE_ID = ABA.BANK_ACCT_USE_ID

and  CBA.BANK_BRANCH_ID = ABB.BRANCH_PARTY_ID

and ABA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID

and trunc(acra.receipt_date) between  nvl(:P_RECEIPT_FROM_DATE,trunc(acra.receipt_date)) and nvl(:P_RECEIPT_TO_DATE,trunc(acra.receipt_date))

and trunc(arpsa.gl_date) between  nvl(:P_GL_FROM_DATE,trunc(arpsa.gl_date)) and nvl(:P_GL_TO_DATE,trunc(arpsa.gl_date))

and acra.set_of_books_id = '1001'

and araa.applied_customer_trx_id in (

select ract.customer_trx_id

from ar_cash_remit_refs_all acrra,

     ra_customer_trx_all ract

where ract.trx_number = acrra.invoice_reference

and acrra.cash_receipt_id  = araa.cash_receipt_id

and acrra.invoice_reference is not null

and acrra.resolved_matching_number is not null

and acrra.resolved_matching_number <> 'NULL'

and  trunc(acra.receipt_date) between  nvl(:P_RECEIPT_FROM_DATE,trunc(acra.receipt_date)) and nvl(:P_RECEIPT_TO_DATE,trunc(acra.receipt_date))

and trunc(arpsa.gl_date) between  nvl(:P_GL_FROM_DATE,trunc(arpsa.gl_date)) and nvl(:P_GL_TO_DATE,trunc(arpsa.gl_date)))

)

) group by 

onaccount_amount, unapplied_amount,unidentified_amount, auto_applied_amount, receipt_number, receipt_date,receipt_amount, 

apply_date, status, pobox,station,receipt_method,gl_date, customer_name, customer_number, masked_account_num,receipt_type,market

UNION

-- MANUALLY_APPLIED_AMOUNT 

select onaccount_amount, unapplied_amount,unidentified_amount, sum(manually_applied_amount) manually_applied_amount, auto_applied_amount, receipt_number, receipt_date, 

receipt_amount,apply_date, status, pobox,station,receipt_method,gl_date, customer_name, customer_number, masked_account_num,receipt_type,market

from (

select 0 onaccount_amount,

       0 unapplied_amount,

       0 unidentified_amount,

       araa.amount_applied manually_applied_amount,

       0 Auto_applied_amount,

       acra.amount receipt_amount,

       acra.receipt_number,

       acra.receipt_date,

       apply_date ,

       acra.status,

       acra.attribute8 pobox,

       acra.attribute3 station,

       arm.name receipt_method,

       arpsa.gl_date,

       ac.customer_name,

       ac.customer_number,

       CBA.MASKED_ACCOUNT_NUM,

       acra.type receipt_type,

 (select ffvt.description 

from fnd_flex_values  ffv,

     fnd_flex_values_tl ffvt

where flex_value = acra.attribute8

and ffv.flex_value_id = ffvt.flex_value_id

and flex_value_set_id = '1010389'

and ffv.enabled_flag ='Y'

and ffvt.language= 'US') market

from ar_receivable_applications_all araa,

     ar_cash_receipts_all acra,

     ar_receipt_methods arm,

     ar_payment_schedules_all arpsa,

     ar_customers ac,

     CE_BANK_ACCOUNTS CBA, 

     CE_BANK_ACCT_USES_ALL ABA, 

     CE_BANK_BRANCHES_V ABB

where 1=1 --and  receipt_number = '275944750084'

and araa.status in ('APP')

and acra.cash_receipt_id = araa.cash_receipt_id

and arm.receipt_method_id = acra.receipt_method_id

and arpsa.payment_schedule_id = araa.payment_schedule_id

and ac.customer_id(+) = arpsa.customer_id

and acra.REMIT_BANK_ACCT_USE_ID = ABA.BANK_ACCT_USE_ID

and  CBA.BANK_BRANCH_ID = ABB.BRANCH_PARTY_ID

and ABA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID

and trunc(acra.receipt_date) between  nvl(:P_RECEIPT_FROM_DATE,trunc(acra.receipt_date)) and nvl(:P_RECEIPT_TO_DATE,trunc(acra.receipt_date))

and trunc(arpsa.gl_date) between  nvl(:P_GL_FROM_DATE,trunc(arpsa.gl_date)) and nvl(:P_GL_TO_DATE,trunc(arpsa.gl_date))

and acra.set_of_books_id = '1001'

and display ='Y'

and araa.receivable_application_id not in (

select araa.receivable_application_id

from ar_receivable_applications_all araa,

     ar_cash_receipts_all acra,

     ar_receipt_methods arm,

     ar_payment_schedules_all arpsa,

     ar_customers ac,

     CE_BANK_ACCOUNTS CBA, 

     CE_BANK_ACCT_USES_ALL ABA, 

     CE_BANK_BRANCHES_V ABB

where araa.status in ('APP')

and araa.display = 'Y'

and acra.cash_receipt_id = araa.cash_receipt_id

and arm.receipt_method_id = acra.receipt_method_id

and arpsa.payment_schedule_id = araa.payment_schedule_id

and ac.customer_id(+) = arpsa.customer_id

and acra.REMIT_BANK_ACCT_USE_ID = ABA.BANK_ACCT_USE_ID

and  CBA.BANK_BRANCH_ID = ABB.BRANCH_PARTY_ID

and ABA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID

and trunc(acra.receipt_date) between  nvl(:P_RECEIPT_FROM_DATE,trunc(acra.receipt_date)) and nvl(:P_RECEIPT_TO_DATE,trunc(acra.receipt_date))

and trunc(arpsa.gl_date) between  nvl(:P_GL_FROM_DATE,trunc(arpsa.gl_date)) and nvl(:P_GL_TO_DATE,trunc(arpsa.gl_date))

and acra.set_of_books_id = '1001'

and araa.applied_customer_trx_id in (

select ract.customer_trx_id

from ar_cash_remit_refs_all acrra,

     ra_customer_trx_all ract

where ract.trx_number = acrra.invoice_reference

and acrra.cash_receipt_id  = araa.cash_receipt_id

and acrra.invoice_reference is not null

and acrra.resolved_matching_number is not null

and acrra.resolved_matching_number <> 'NULL'

and  trunc(acra.receipt_date) between  nvl(:P_RECEIPT_FROM_DATE,trunc(acra.receipt_date)) and nvl(:P_RECEIPT_TO_DATE,trunc(acra.receipt_date))

and trunc(arpsa.gl_date) between  nvl(:P_GL_FROM_DATE,trunc(arpsa.gl_date)) and nvl(:P_GL_TO_DATE,trunc(arpsa.gl_date)))

)

) group by 

onaccount_amount, unapplied_amount,unidentified_amount, auto_applied_amount, receipt_number, receipt_date,receipt_amount, 

apply_date, status, pobox,station,receipt_method,gl_date, customer_name, customer_number, masked_account_num,receipt_type,market

UNION

-- AUTO APPLIED RECEIPT AMOUNTS WITH AUTO_APPLIED Y

select onaccount_amount, unapplied_amount, unidentified_amount, manually_applied_amount, sum(auto_applied_amount) auto_applied_amount,

      receipt_number, receipt_date, receipt_amount, apply_date , status, pobox,station, receipt_method, gl_date, customer_name,

      customer_number, masked_account_num, receipt_type, market

from

(

select 0 onaccount_amount,

       0 unapplied_amount,

       0 unidentified_amount,

       0 manually_applied_amount,

       araa.amount_applied Auto_applied_amount,

       acra.amount receipt_amount,

       acra.receipt_number,

       acra.receipt_date,

       apply_date apply_date,

       acra.status,

       acra.attribute8 pobox,

       acra.attribute3 station,

       arm.name receipt_method,

       arpsa.gl_date,

       ac.customer_name,

       ac.customer_number,

       CBA.MASKED_ACCOUNT_NUM,

       acra.type receipt_type,

 (select ffvt.description 

from fnd_flex_values  ffv,

     fnd_flex_values_tl ffvt

where flex_value = acra.attribute8

and ffv.flex_value_id = ffvt.flex_value_id

and flex_value_set_id = '1010389'

and ffv.enabled_flag ='Y'

and ffvt.language= 'US') market

from ar_receivable_applications_all araa,

     ar_cash_receipts_all acra,

     ar_receipt_methods arm,

     ar_payment_schedules_all arpsa,

     ar_customers ac,

     CE_BANK_ACCOUNTS CBA, 

     CE_BANK_ACCT_USES_ALL ABA, 

     CE_BANK_BRANCHES_V ABB

where araa.status in ('APP')

and araa.display = 'Y'

and acra.cash_receipt_id = araa.cash_receipt_id

and arm.receipt_method_id = acra.receipt_method_id

and arpsa.payment_schedule_id = araa.payment_schedule_id

and ac.customer_id(+) = arpsa.customer_id

and acra.REMIT_BANK_ACCT_USE_ID = ABA.BANK_ACCT_USE_ID

and  CBA.BANK_BRANCH_ID = ABB.BRANCH_PARTY_ID

and ABA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID

and trunc(acra.receipt_date) between  nvl(:P_RECEIPT_FROM_DATE,trunc(acra.receipt_date)) and nvl(:P_RECEIPT_TO_DATE,trunc(acra.receipt_date))

and trunc(arpsa.gl_date) between  nvl(:P_GL_FROM_DATE,trunc(arpsa.gl_date)) and nvl(:P_GL_TO_DATE,trunc(arpsa.gl_date))

and acra.set_of_books_id = '1001'

and araa.applied_customer_trx_id in (

select ract.customer_trx_id

from ar_cash_remit_refs_all acrra,

     ra_customer_trx_all ract

where ract.trx_number = acrra.invoice_reference

and acrra.invoice_reference is not null

and acrra.cash_receipt_id = araa.cash_receipt_id

and acrra.resolved_matching_number is not null

and acrra.resolved_matching_number <> 'NULL'

and  trunc(acra.receipt_date) between  nvl(:P_RECEIPT_FROM_DATE,trunc(acra.receipt_date)) and nvl(:P_RECEIPT_TO_DATE,trunc(acra.receipt_date))

and trunc(arpsa.gl_date) between  nvl(:P_GL_FROM_DATE,trunc(arpsa.gl_date)) and nvl(:P_GL_TO_DATE,trunc(arpsa.gl_date)))

)

group by onaccount_amount, unapplied_amount, unidentified_amount, manually_applied_amount, 

      receipt_number, receipt_date, receipt_amount, apply_date , status, pobox,station, receipt_method, gl_date, customer_name,

      customer_number, masked_account_num, receipt_type, market

)group by receipt_number, receipt_date, receipt_amount, status , pobox, station,

      receipt_method, gl_date, customer_name, customer_number, masked_account_num, receipt_type, market

)

order by receipt_date

PLSQL Script to run the concurrent program from backend

 DECLARE

l_responsibility_id 	NUMBER;
l_application_id    	NUMBER;
l_user_id           	NUMBER;
l_request_id            NUMBER;
BEGIN
  --
  SELECT DISTINCT fr.responsibility_id,
    frx.application_id
     INTO l_responsibility_id,
    l_application_id
     FROM apps.fnd_responsibility frx,
    apps.fnd_responsibility_tl fr
    WHERE fr.responsibility_id = frx.responsibility_id
  AND LOWER (fr.responsibility_name) LIKE LOWER('XXCRG Custom');
  --
   SELECT user_id INTO l_user_id FROM fnd_user WHERE user_name = 'SDEVAKI';
  --
  --To set environment context.
  --
  apps.fnd_global.apps_initialize (l_user_id,l_responsibility_id,l_application_id);
  --
  --Submitting Concurrent Request
  --
  l_request_id := fnd_request.submit_request ( 
      application   => 'XXCRG Custom', 
      program       => 'XXCRG_UPDATE_COLLECTORS_LIST', 
      description   => 'Update new collectors for Customers', 
      start_time    => sysdate, 
      sub_request   => FALSE,
      argument1     => '/u05/app/oracle/R122/XXCRGDEBS/fs2/EBSapps/appl/xxcrg/12.0.0/bin/collectors_table_data.csv'
  );
  --
  COMMIT;
  --
  IF l_request_id = 0
  THEN
     dbms_output.put_line ('Concurrent request failed to submit');
  ELSE
     dbms_output.put_line('Successfully Submitted the Concurrent Request');
  END IF;
  --
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line('Error While Submitting Concurrent Request '||TO_CHAR(SQLCODE)||'-'||sqlerrm);
END;
/