Capability to access Key "Application Reference" field in "Receivables – Standard Receipts Applicat

Comakshi Ars

Organization Name

ADO Staffing Inc,

Description

We need capability to report the Application reference as part of the receipt or Credit Memo application detail.

Application Reference field denotes the transaction that the refund was applied to, example AP Invoice number on Application UI.

Currently, Oracle does not provide any feature to drill back from Payables to Receivables for PAYMENT REQUEST of type Receivables Refunds on AP Invoice UI nor  field "Application Reference" has been exposed in the pertinent OTBI subject area for Receipt or Credit memo.

Use Case and Business Need

When AR Customer Refund is issued on a receipt or a credit memo, Oracle provides a link to AP Invoice - Payment request.

However the reverse is NOT addressed as a standard feature, if we know the Payment request AP Transaction Number, the AR refund origination AR Transaction cannot be easily identified, either on the UI or the OTBI Subject area, "Receivables – Standard Receipts Application Details Real Time",

More details

Service request with Oracle support was raised  and was directed to log an Idea on Customer Connect

SR 3-26458294051 : Unable to find "Application Reference" Field on the OTBI Subject area  

Solution

As a workaround, Following SQL Query was built as a Data Model and a Report was created using the standard BI tool, This is an implemented and working solution.

Data Model - SQL Query

 

SELECT apsa.trx_number ar_trx_num, apsa.class ar_trx_typ, (apsa.amount_applied*-1) refund_amt ,araa.application_ref_num ap_inv_num

FROM

ar_receivable_applications_all araa

, ar_payment_schedules_all apsa

,ap_invoices_all aia

WHERE

araa.APPLICATION_REF_NUM = aia.INVOICE_NUM

AND araa.RECEIVABLE_APPLICATION_ID  = aia.REFERENCE_KEY1

AND apsa.AMOUNT_APPLIED <>0

AND araa.PAYMENT_SCHEDULE_ID =  apsa.PAYMENT_SCHEDULE_ID

AND araa.APPLICATION_REF_TYPE='AP_REFUND_REQUEST'

AND aia.INVOICE_NUM IN :P_AP_INV_NUM

 

Original Idea Number: cb6c4237c5

Vinod Ronold

I agree