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

Unable to Launch the Java Virtual Machine Located at path: ..\..jdk\jre\bin\client\jvm.dll

 

I encountered this issue while Jdeveloper Setup. Below are the steps followed.

Step 1: Run regedit (remember to run it as the administrator)

Step 2: Expand HKEY_LOCAL_MACHINE

Step 3: EXPAND SOFTWARE

Step 4: EXPAND Microsoft

Step 5: Expand Windows

Step 6: Expand Current Version

Step 7: Expand App Paths

       . At Paths, add a New Key.

        Name it as jdevW.exe

Step 8: Expand jdevW.exe. On the right hand side, you will see the (DEFAULT) value. Go to the pathway to the jdeveloper executable.

Ex:- C:\p22064122_R12_GENERIC\jdevbin\jdev\bin\jdevW.exe

Step 9: Create a new string  value like DEFAULT and name the field as PATH and set it value to the jdeveloper path way.

Ex:-C:\p22064122_R12_GENERIC\jdevbin\jdk\jre\bin

Please set the profile option FND Validation Level to None

 Please set the profile option FND Validation Level to None

I received an email from Business user, when they are trying to approve the notifications in the dashboard they are facing this issue.

Resolution:-

Clear the cache in functional administrator and asked the business user to approve the notifications. User said now she is able to approve the notifications.

I have checked in the other blogs to find out the issue, they mentioned to check the profile option values table when any profile option value got changed recently. There is no change in the profile option values table.

AR_RECEIPT_API_PUB.APPLY_ON_ACCOUNT

 

create or replace PROCEDURE      XX_AR_RCPT_APPLY_ONACCOUNT(errbuff out varchar2,retcode out number)

IS

--DECLARE

   p_api_version                    NUMBER;

   p_init_msg_list                  VARCHAR2 (200);

   p_commit                         VARCHAR2 (200);

   p_validation_level               NUMBER;

   x_return_status                  VARCHAR2 (200);

   x_msg_count                      NUMBER;

   x_msg_data                       VARCHAR2 (200);

  -- p_receipt_number                 ar_cash_receipts.receipt_number%TYPE;

   p_ussgl_transaction_code         ar_receivable_applications.ussgl_transaction_code%TYPE;

   p_attribute_rec                  AR_RECEIPT_API_PUB.attribute_rec_type;

   p_global_attribute_rec           AR_RECEIPT_API_PUB.global_attribute_rec_type;

   p_comments                       ar_receivable_applications.comments%TYPE;

   p_application_ref_num            ar_receivable_applications.application_ref_num%TYPE;

   p_secondary_application_ref_id   ar_receivable_applications.secondary_application_ref_id%TYPE;

   p_customer_reference             ar_receivable_applications.customer_reference%TYPE;

   p_called_from                    VARCHAR2 (200);

   p_customer_reason                ar_receivable_applications.customer_reason%TYPE;

   p_secondary_app_ref_type         ar_receivable_applications.secondary_application_ref_type%TYPE;

   p_secondary_app_ref_num          ar_receivable_applications.secondary_application_ref_num%TYPE;

   l_cash_receipt_id                ar_cash_receipts.cash_receipt_id%TYPE;

   l_receipt_number                 ar_cash_receipts.receipt_number%TYPE;

   l_amount_applied                 ar_receivable_applications.amount_applied%TYPE;

   l_apply_date                     ar_receivable_applications.apply_date%TYPE;

   l_apply_gl_date                  ar_receivable_applications.gl_date%TYPE;

--Query to get the details of receipts which are unapplied for receipt methods

CURSOR c_get_details

IS 

SELECT acra.cash_receipt_id,

       acra.receipt_number,

       sum(araa.amount_applied) amount_applied

FROM ar_cash_receipts_all acra ,

     ar_receivable_applications_all araa

WHERE acra.cash_receipt_id = araa.cash_receipt_id

AND acra.type ='CASH' 

AND acra.status = 'UNAPP'

AND araa.status = 'UNAPP'

AND acra.receipt_method_id in ('37029','42028','15025','12024')  

--AND acra.cash_receipt_id in ('1330277')

GROUP BY acra.cash_receipt_id,acra.receipt_number;

BEGIN

   mo_global.SET_POLICY_CONTEXT ('S', 103);                     -- your org id

   mo_global.init ('AR');

  FOR j in c_get_details

  LOOP

  l_cash_receipt_id := j.cash_receipt_id;

  l_amount_applied  := j.amount_applied;

  l_receipt_number  := j.receipt_number;

  l_apply_date      := TRUNC(SYSDATE);

  l_apply_gl_date   := TRUNC(SYSDATE);

  --Passing Cash_receipt_id and Amount_Applied to Apply On Account API 

   AR_RECEIPT_API_PUB.Apply_on_account (

      p_api_version                    => 1.0,

      p_init_msg_list                  => FND_API.G_FALSE,

      p_commit                         => FND_API.G_FALSE,

      p_validation_level               => FND_API.G_VALID_LEVEL_FULL,

      x_return_status                  => x_return_status,

      x_msg_count                      => x_msg_count,

      x_msg_data                       => x_msg_data,

      p_cash_receipt_id                => l_cash_receipt_id,

      p_receipt_number                 => l_receipt_number,

      p_amount_applied                 => l_amount_applied,

      p_apply_date                     => l_apply_date,

      p_apply_gl_date                  => l_apply_gl_date,

      p_org_id                         => 103, ------ <Org id >

      p_ussgl_transaction_code         => p_ussgl_transaction_code,

      p_attribute_rec                  => p_attribute_rec,

      p_global_attribute_rec           => p_global_attribute_rec,

      p_comments                       => p_comments,

      p_application_ref_num            => p_application_ref_num,

      p_secondary_application_ref_id   => p_secondary_application_ref_id,

      p_customer_reference             => p_customer_reference,

      p_called_from                    => p_called_from,

      p_customer_reason                => p_customer_reason,

      p_secondary_app_ref_type         => p_secondary_app_ref_type,

      p_secondary_app_ref_num          => p_secondary_app_ref_num

   );

   IF (x_return_status = 'S')

   THEN

      COMMIT;

    --  DBMS_OUTPUT.put_line ('SUCCESS');

      FND_FILE.PUT_LINE(FND_FILE.LOG,'SUCCESS -> '||' Receipt Number'||' '||l_receipt_number||' Applied OnAccount');

      --DBMS_OUTPUT.put_line ('Return Status            = ' || SUBSTR (x_return_status, 1, 255));

      --FND_FILE.PUT_LINE(FND_FILE.LOG,'Return Status            = ' || SUBSTR (x_return_status, 1, 255));

      --DBMS_OUTPUT.put_line ('Message Count             = ' || x_msg_count);

      --FND_FILE.PUT_LINE(FND_FILE.LOG,'Message Count             = ' || x_msg_count);

      --DBMS_OUTPUT.put_line ('Message Data            = ' || x_msg_data);

      --FND_FILE.PUT_LINE(FND_FILE.LOG,'Message Data            = ' || x_msg_data);

   ELSE

      ROLLBACK;

      DBMS_OUTPUT.put_line ('Return Status    = ' || SUBSTR (x_return_status, 1, 255));

      FND_FILE.PUT_LINE(FND_FILE.LOG,'Return Status            = ' || SUBSTR (x_return_status, 1, 255)||' '||l_receipt_number);

      DBMS_OUTPUT.put_line ('Message Count     = ' || TO_CHAR (x_msg_count));

      FND_FILE.PUT_LINE(FND_FILE.LOG,'Message Count             = ' || x_msg_count);

      DBMS_OUTPUT.put_line ('Message Data    = ' || SUBSTR (x_msg_data, 1, 255));

      FND_FILE.PUT_LINE(FND_FILE.LOG,'Message Data            = ' || x_msg_data);

      DBMS_OUTPUT.put_line(APPS.FND_MSG_PUB.Get (p_msg_index   => APPS.FND_MSG_PUB.G_LAST,p_encoded     => APPS.FND_API.G_FALSE));

      IF x_msg_count >= 0

      THEN

         FOR I IN 1 .. 10

         LOOP

            FND_FILE.PUT_LINE(FND_FILE.LOG,I || '. '|| SUBSTR (FND_MSG_PUB.Get (p_encoded   => FND_API.G_FALSE),1,255));

         END LOOP;

      END IF;

   END IF;

END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('Exception :' || SQLERRM);

      FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception :' || SQLERRM);

END;

Oracle Apps using SOAP Request.

 create or replace PROCEDURE XX_ESB_SOAP_REQUEST(

      ERRBUFF           OUT VARCHAR2 ,

      RETCODE           OUT NUMBER ,

      P_IN_MODULE       IN VARCHAR2,

      P_IN_KEY_VALUE1   IN VARCHAR2,

  P_IN_KEY_VALUE2   IN VARCHAR2)

IS

/******************************************************************************************

--HEADER: XXESBREQ.prc

--Description: This Procedure contains the HTTP SOAP trigger to ESB.

********************************************************************************************/

    v_soap_request  VARCHAR2(3000);

    v_soap_response VARCHAR2(1500);

    http_req        utl_http.req;

    http_resp       utl_http.resp;

v_len           NUMBER;

    v_txt           VARCHAR2(1500);

v_error_text    VARCHAR2(1500);

v_key_value1    VARCHAR2(100);

v_key_value2    VARCHAR2(100);

v_curr_date     VARCHAR2(100);

v_esb_soap_url  VARCHAR2(100);

BEGIN

--Initialization

v_soap_request := NULL;

v_soap_response := NULL;

v_len  := 0;

v_txt  := NULL;

v_error_text := NULL;

v_esb_soap_url := NULL;

  IF TRIM(P_IN_MODULE) = 'MDM_ORCL_CUST_INT' THEN

    v_key_value1 :=  TRIM(P_IN_KEY_VALUE1); --MDM_PARTY_ID

--ESB Status Code from the staging table.

--Modified for ver1.1

--v_key_value2 :=  'MAP-REQ';

v_key_value2 := TRIM(P_IN_KEY_VALUE2);

v_curr_date  :=  TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MM:SS');

  END IF;

FND_FILE.PUT_LINE(FND_FILE.LOG,'Begin Processing Request....... ');

FND_FILE.PUT_LINE(FND_FILE.LOG,'P_IN_MODULE: '||P_IN_MODULE);

    FND_FILE.PUT_LINE(FND_FILE.LOG,'MDM_PARTY_ID: '||v_key_value1);

FND_FILE.PUT_LINE(FND_FILE.LOG,'ESB_STATUS_CODE: '||v_key_value2);

--Build SOAP Request

    v_soap_request:= '<?xml version = "1.0" encoding = "UTF-8"?>'||

                   '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:v1="http://companyname.net/controller/v1.0">

                <soapenv:Header/>

                <soapenv:Body>

                                <v1:notify>

                                                <eventRequest>

                                                                <events>

                                                                                <userID>EBSService</userID>

                                                                                <passPhrase></passPhrase>

                                                                                <creationTime>'||v_curr_date||'</creationTime>

                                                                                <action>CREATE</action>

                                                                                <entity>MDM_CUSTOMERMASTER</entity>

                                                                                <recordCount>1</recordCount>

                                                                                <primaryKey>'||v_key_value1||'</primaryKey>

                                                                                <transactionType>SINGLE</transactionType>

                                                                                <sourceSystem>EBS</sourceSystem>

<eventType>'||v_key_value2||'</eventType>

                                                                </events>

                                                </eventRequest>

                                </v1:notify>

                </soapenv:Body>

</soapenv:Envelope>';

--Connecting to ESB Master Controller

-- Query to retrieve EBS URL Based on Oracle EBS Instance

BEGIN

SELECT FFVV.DESCRIPTION

      INTO v_esb_soap_url

      FROM FND_FLEX_VALUE_SETS FFVS

         , FND_FLEX_VALUES_VL FFVV

     WHERE FFVV.FLEX_VALUE = (SELECT INSTANCE_NAME FROM V$INSTANCE)

       And FFVS.FLEX_VALUE_SET_ID = FFVV.FLEX_VALUE_SET_ID

       AND FFVS.FLEX_VALUE_SET_NAME = 'XX_ESB_URL_LIST';

EXCEPTION

WHEN NO_DATA_FOUND THEN

FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR NO ESB Controller URL defined. '||SUBSTR(SQLERRM,1,100));

WHEN OTHERS THEN

FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR while retrieving ESB Controller URL. '||SUBSTR(SQLERRM,1,100));

END;

    http_req:= utl_http.begin_request

              ( v_esb_soap_url

              , 'POST'

              , 'HTTP/1.1'

              );

    UTL_HTTP.SET_HEADER(HTTP_REQ, 'Content-Type', 'text/xml charset=UTF-8');

    UTL_HTTP.SET_HEADER(HTTP_REQ, 'Content-Length', LENGTH(v_soap_request));

UTL_HTTP.SET_HEADER(HTTP_REQ, 'SOAPAction','"MP-ORCL-MDM"');    -- SOAP Request HEader Attribute added 

    UTL_HTTP.WRITE_TEXT(http_req, v_soap_request);

    http_resp:= UTL_HTTP.GET_RESPONSE(http_req);

-- Obtain the length of the response

utl_http.get_header_by_name(http_resp, 'Content-Length', v_len, 1);

   BEGIN

        utl_http.read_text(http_resp, v_txt, v_len);

        v_soap_response := v_txt; --Read the response

  EXCEPTION

WHEN Utl_Http.End_Of_Body THEN NULL;

WHEN OTHERS THEN

  FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR While parsing the SOAP response. '||SUBSTR(SQLERRM,1,100));

  END;

    utl_http.end_response(http_resp);

FND_FILE.PUT_LINE(FND_FILE.LOG,'soap_response: '||v_soap_response);

--Update the SOAP response

IF v_soap_response IS NOT NULL THEN

  FND_FILE.PUT_LINE(FND_FILE.LOG,'Oracle - ESB trigger successful. ');

  BEGIN

UPDATE XX_MDM_CUSTOMER_STG_ALL

SET ESB_RESPONSE = v_soap_response

    ,ERROR_CODE  = 'TRIGGER_SUCCESS'||' '||ERROR_CODE

        ,LAST_UPDATED_BY = 'ARCUSTOMER'

        ,LAST_UPDATE_DATE = SYSDATE

WHERE MDM_PARTY_ID = v_key_value1

AND ESB_STATUS_CODE = v_key_value2

AND NVL(PROCESSED_FLAG,'N') = 'Y';

FND_FILE.PUT_LINE(FND_FILE.LOG,'Response successfully updated on the staging table for Party Id: '||v_key_value1);

  EXCEPTION

  WHEN OTHERS THEN

FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR While Updating the ESB Response on the staging table. '||SUBSTR(SQLERRM,1,100));

  END;

END IF;

--Procedure Level exception

EXCEPTION

WHEN OTHERS THEN

v_error_text := SQLERRM;

FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR While Triggering ESB.....'||SUBSTR(SQLERRM,1,100));

  BEGIN

UPDATE XX_MDM_CUSTOMER_STG_ALL

SET ESB_RESPONSE = v_error_text

    ,ERROR_CODE = 'TRIGGER_FAILURE'||' '||ERROR_CODE

,ERROR_DESCRIPTION = v_error_text||' '||ERROR_DESCRIPTION

,LAST_UPDATED_BY   = 'ARCUSTOMER'

,LAST_UPDATE_DATE  = SYSDATE

WHERE MDM_PARTY_ID = v_key_value1

AND ESB_STATUS_CODE = v_key_value2

AND NVL(PROCESSED_FLAG,'N') = 'Y';

  EXCEPTION

  WHEN OTHERS THEN

FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR While Updating the TRIGGER_FAILURE exception Details. '||SUBSTR(SQLERRM,1,100));

  END;

END;

Solution for OAF Error oracle.apps.fnd.framework.OAException: Application: ICX, Message Name: Could not find the specified responsibility.

 Solution for OAF Error oracle.apps.fnd.framework.OAException: Application: ICX, Message Name: Could not find the specified responsibility.

ERROR:-

oracle.apps.fnd.framework.OAException: Application: ICX, Message Name: Could not find the specified responsibility.

at oracle.apps.fnd.framework.CreateIcxSession.getEncryptedSessId(Unknown Source)

at oracle.apps.fnd.framework.CreateIcxSession.createSession(Unknown Source)

at _runregion._jspService(_runregion.java:132)

Reason For Error:-

This issue occurred because the user didn't assign the below responsibilities.

 Solution:-

Add the above responsibilities to the user and retest the issue.