AR Refund Upload

➜ Back to Library
Description

AR Refund Upload returns customer credit balances in Oracle Receivables from Excel. Each row refunds either an open credit memo or the unapplied / on-account balance of a customer cash receipt. The upload records the Receivables side of the refund, which automatically raises a Payment Request in Payables – so the actual disbursement still goes through the normal Payables review and payment cycle.

When to use it

  • A customer has an open credit memo (for example an overpayment recorded as a credit memo) and wants the money back.
  • A customer paid more than they owed, or paid with no specific invoice, and the unapplied / on-account cash should be returned.
  • You want to clear long-standing customer credit balances in bulk rather than one at a time.

Before you start

  • Blitz Report is installed and you are signed in to a Receivables responsibility with an appropriate AR approval limit – the upload never bypasses your approval limit.
  • Each operating unit that processes refunds has a Credit Memo Refund-type Receivables Activity defined (Receivables > Setup > Receipts > Receivables Activities). The same activity is used for both credit-memo and receipt refunds.
  • The Apply GL Date you use falls in an open Receivables period.

Step 1 – Open the upload and download the candidates

Run AR Refund Upload from the Blitz Report menu. In the default Create, Update mode the template downloads pre-populated with every eligible credit memo and unapplied receipt for your parameters, showing the Customer Name and Open Balance – turning the upload into a review-and-select worksheet.

ParameterPurpose
Upload ModeCreate, Update (recommended – pre-lists the candidates) or Create (empty template for manual entry).
Operating UnitLimits both credit memos and receipts.
Customer NameLimits to customers that have an eligible refund in the operating unit.
GL Date From / ToCredit-memo date range.
Credit Memo NumberOne or more specific credit memos.
Batch NameOne or more Receivables transaction batches (credit memos).
Include accounts with open invoicesBy default a credit memo is offered only when the customer has no open invoices. Set this to Yes to also list customers who do have open invoices (for example when those invoices are disputed).
Open Invoice Due Within DaysUsed only when the above is Yes. Enter a number of days N: a credit memo is hidden only if the customer has an open invoice due within N days; invoices due later are ignored so the credit memo can still be refunded. Leave blank to ignore due dates.

Step 2 – Review and mark the rows to refund

  1. Review the listed candidates – each shows the Refund Source (Credit Memo or Unapplied Receipt), Customer Name and Open Balance.
  2. Set Refund = Yes on the rows you want to refund. Leave it blank to skip a row – blank rows are not processed.
  3. Refund Amount defaults to the open balance. Enter a smaller positive amount for a partial refund (it must be greater than zero and no more than the balance).
  4. Apply Date and Apply GL Date default to today; the GL Date must be in an open period.
  5. Optionally set Payment Method, Pay Group, Payment Reason Comments and Comments – these flow through to the Payables Payment Request.

To add a refund manually, pick the Refund Source first, then choose the Credit Memo Number or Receipt Number (the list adapts to the source).

Step 3 – Save and upload

Save the workbook and use the Upload button. Only the rows marked Refund = Yes are processed; the result workbook lists those rows with a Status and Message.

What happens next (end-to-end)

  1. Receivables (this upload) records the refund against the operating unit’s Credit Memo Refund activity.
  2. An automatic Receivables-to-Payables event creates a Payment Request invoice payable to the customer.
  3. Payables Invoice Validation runs as normal.
  4. A Payment Process Request issues the disbursement.

This upload performs step 1 only. Steps 2-4 are standard Payables processing and act as a second control point before any money leaves.

Common questions

Credit memo or unapplied receipt – which refund source do I use?
Use Credit Memo when the credit sits on an open credit memo; use Unapplied Receipt when the customer’s cash receipt has an unapplied or on-account balance to return.

Can I do a partial refund?
Yes – set Refund Amount to a value smaller than the open balance.

Does the money leave immediately?
No. The upload only records the Receivables side. The payment is issued later through the standard Payables payment cycle.

Why isn’t a particular credit memo in the list?
By default credit memos for customers that have open invoices are hidden. Set “Include accounts with open invoices” to Yes (optionally with a due-within-days threshold) to see them.

What about approval limits?
Your Receivables approval limit always applies. A refund above your limit is rejected with the standard message; limits are never bypassed.

Troubleshooting

MessageCauseWhat to do
No Credit Memo Refund activity is defined for this operating unitThe operating unit has no Credit Memo Refund-type Receivables Activity.Define one in Receivables > Setup > Receipts > Receivables Activities.
Refund over approval limitThe refund amount exceeds your Receivables approval limit.Have a user with a sufficient limit process the row.
GL period not openThe Apply GL Date is in a closed period.Use a date in an open Receivables period.
Refund amount invalidThe amount is zero, negative, or larger than the open balance.Enter a positive amount no greater than the Open Balance.
select
null action_,
null status_,
null message_,
null modified_columns_,
haouv.name operating_unit,
cast(null as varchar2(30)) refund,
'Credit Memo' refund_source,
rcta.trx_number credit_memo_number,
cast(null as varchar2(30)) receipt_number,
hp.party_name customer_name,
abs(apsa.amount_due_remaining) open_balance,
abs(apsa.amount_due_remaining) refund_amount,
cast(null as date) apply_date,
cast(null as date) apply_gl_date,
cast(null as varchar2(100)) payment_method,
cast(null as varchar2(80)) pay_group,
cast(null as varchar2(240)) payment_reason_comments,
cast(null as varchar2(240)) comments,
rownum upload_row
from
ar_payment_schedules_all apsa,
ra_customer_trx_all rcta,
hr_all_organization_units_vl haouv,
hz_cust_accounts hca,
hz_parties hp
where
1=1 and
apsa.class='CM' and
apsa.status='OP' and
apsa.amount_due_remaining<>0 and
apsa.customer_trx_id=rcta.customer_trx_id and
apsa.org_id=haouv.organization_id and
apsa.customer_id=hca.cust_account_id and
hca.party_id=hp.party_id and
apsa.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) and
not exists (
select
null
from
ar_payment_schedules_all inv
where
inv.customer_id=apsa.customer_id and
inv.org_id=apsa.org_id and
inv.class='INV' and
inv.status='OP' and
inv.amount_due_remaining<>0 and
(nvl(:include_with_open_invoices,'N')<>'Y' or :open_invoice_due_within_days is not null and inv.due_date<trunc(sysdate)+:open_invoice_due_within_days)
)
union all
select
null action_,
null status_,
null message_,
null modified_columns_,
haouv.name operating_unit,
cast(null as varchar2(30)) refund,
'Unapplied Receipt' refund_source,
cast(null as varchar2(20)) credit_memo_number,
acra.receipt_number,
hp.party_name customer_name,
(select sum(ara.amount_applied) from ar_receivable_applications_all ara where ara.cash_receipt_id=acra.cash_receipt_id and ara.status in ('UNAPP','ACC') and ara.amount_applied>0) open_balance,
(select sum(ara.amount_applied) from ar_receivable_applications_all ara where ara.cash_receipt_id=acra.cash_receipt_id and ara.status in ('UNAPP','ACC') and ara.amount_applied>0) refund_amount,
cast(null as date) apply_date,
cast(null as date) apply_gl_date,
cast(null as varchar2(100)) payment_method,
cast(null as varchar2(80)) pay_group,
cast(null as varchar2(240)) payment_reason_comments,
cast(null as varchar2(240)) comments,
rownum upload_row
from
ar_cash_receipts_all acra,
hr_all_organization_units_vl haouv,
hz_cust_accounts hca,
hz_parties hp
where
1=1 and
acra.status='UNAPP' and
acra.org_id=haouv.organization_id and
acra.pay_from_customer=hca.cust_account_id and
hca.party_id=hp.party_id and
acra.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) and
exists (
select
null
from
ar_receivable_applications_all ara
where
ara.cash_receipt_id=acra.cash_receipt_id and
ara.status in ('UNAPP','ACC') and
ara.amount_applied>0
)
Parameter NameSQL textValidation
Upload Mode
:p_upload_mode like '%' || xxen_upload.action_update
LOV
Operating Unit
haouv.name=:operating_unit
LOV
GL Date From
apsa.gl_date>=:gl_date_from
Date
GL Date To
apsa.gl_date<:gl_date_to+1
Date
Credit Memo Number
rcta.trx_number=:credit_memo_number
Char
Customer Name
hp.party_name=:customer_name
LOV
Batch Name
rcta.batch_id=:batch_id
LOV
Include accounts with open invoices
 
LOV
Open Invoice Due Within Days
 
Number
Download
Related Blitz Reports
AR Unapplied Receipts Register AR Receipt Register CAC Interface Error Summary AR Aging AR Aging - SLA Account COPY OF: AR Aging Galana AR Aging IEX Collection Note Upload AR Customer Credit Limits
Blitz Report™