Card Connect Funding API in Oracle apps using REST API

This Card Connect application extension is to implement a feasible solution that accesses the

merchants/ mids funding information from Card Connect via the funding service API and process the

transactions directly into Oracle system.

The idea is to replace the existing ..NET application with an Oracle based application/system.

This system is designed in a way that it will trigger email notification to the users regarding

the every day's transaction data. The program can be run in On Schedule and On Demand to trigger email Notifications

Prerequisites

1.   Open port connection must be available for the application server.

2.   Server availability while trying to communicate the third party Server availability while trying to communicate the third--party vendor using Funding APIs party vendor using Funding APIs

3. Data must be present in JSON format at the correct location. 

Approach

The Funding Service API will fetch the data from third party vendor and insert the data into oracle tables using Java

Concurrent Program.

Extracting the data from oracle interface table into Excel sheet and sent an email to the business users with the excel attachment.

1. Job to get the yesterday’s transactions from card connect Funding API.

a. https://xxcompany.cardconnect.com:8000/ cardconnect / funding?merchid =123121231&date=<Yesterday’s date> (Date Format:MMDD)

b. https://xxcompany.cardconnect.com:8000/ cardconnect / funding?merchid =123121121&date=<Yesterday’s date> (Date Format:MMDD)

2. After getting the response from API inserting the data into oracle temporary table (XX_JSON_TEMP) to hold the JSON data

3. Read the “ txns ” node from the API

4. After performing necessary validations on data insert the data into two oracle tables

a.XX_FIS_AR_PAY_HIST_ALL.

b.XX_FIS_AR_PAYMENTS_INT_ALL.

5. Extracting the interface table data into excel sheet using XML Publisher Report.

6. Send an email to the business user.

7. Run History will be maintained for each run.

   a.XX_FIS_AR_RUN_HIST_ALL

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;