ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN E811F942597A2075E0530100007FF4F2 Blitz Upload Mode Y select xxen_upload.action_create id, xxen_upload.action_meaning(xxen_upload.action_create) value, null description from dual union all select xxen_upload.action_create || ',' || xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, null description from dual US 8E2FF36EDEB979D2E0530100007F1FF2 HR Operating Unit Y select hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hou.organization_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) order by hou.name Fixed where clause duplication AR وحدة التشغيل مقيدة بالتحكم في الوصول متعدد المنظمات (MOAC) D Bedieneinheit eingeschränkt durch Multi Org Access Control (MOAC) E Unidad operativa restringida por el control de acceso multiorgánico (MOAC) F Unité opérationnelle restreinte par un contrôle d'accès multi-organismes (MOAC) I Unità operativa limitata dal controllo di accesso multiorgano (MOAC) JA 多機関アクセス制御(MOAC)で制限された操作ユニット KO 다중 조직 액세스 제어 (MOAC)에 의해 제한되는 운영 단위 PTB Unidade operacional restrita por controle de acesso multi org (MOAC) RU Операционное устройство, ограниченное мультиорганизованным контролем доступа (MOAC) S Styrenhet begränsad av multi-org access control (MOAC) TR Çoklu kuruluş erişim kontrolü (MOAC) tarafından kısıtlanan işletim birimi US Operating unit restricted by multi org access control (MOAC) ZHS 受多机构访问控制（MOAC）限制的操作单元。 8E2FF36EDEA679D2E0530100007F1FF2 Yes Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US D6C04E11BB5E4870BFFBBCC439D8F69DY 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 ) U A xxen_ar_upload.upload_refund (select case when :$column$.refund_source is null or :$column$.operating_unit is null then null when :$column$.refund_source='Credit Memo' and :$column$.credit_memo_number is null then 'Credit Memo Number is required when Refund Source is Credit Memo.' when :$column$.refund_source='Unapplied Receipt' and :$column$.receipt_number is null then 'Receipt Number is required when Refund Source is Unapplied Receipt.' when :$column$.refund_amount is null then null when :$column$.refund_amount<=0 then 'Refund Amount must be greater than zero.' when :$column$.refund_source='Credit Memo' and :$column$.refund_amount>nvl((select abs(apsa.amount_due_remaining) from ar_payment_schedules_all apsa, ra_customer_trx_all rcta, hr_all_organization_units_vl haouv where rcta.trx_number=:$column$.credit_memo_number and haouv.name=:$column$.operating_unit and apsa.org_id=haouv.organization_id and apsa.customer_trx_id=rcta.customer_trx_id and apsa.class='CM' and apsa.status='OP' and rownum=1),:$column$.refund_amount+1) then 'Refund Amount exceeds the open credit memo balance.' when :$column$.refund_source='Unapplied Receipt' and :$column$.refund_amount>nvl((select sum(ara.amount_applied) from ar_receivable_applications_all ara, ar_cash_receipts_all acra, hr_all_organization_units_vl haouv where acra.receipt_number=:$column$.receipt_number and haouv.name=:$column$.operating_unit and acra.org_id=haouv.organization_id and ara.cash_receipt_id=acra.cash_receipt_id and ara.status in ('UNAPP','ACC') and ara.amount_applied>0),:$column$.refund_amount+1) then 'Refund Amount exceeds the unapplied receipt amount.' end from dual) US AR Refund Upload Bulk-refund customer credit balances in AR. Each row refunds either an open credit memo or the unapplied/on-account portion of a customer cash receipt by applying it against the operating unit's Credit Memo Refund Receivables Activity, via AR_CM_APPLICATION_PUB.Activity_application or AR_RECEIPT_API_PUB.Activity_application. WHEN TO USE EACH REFUND SOURCE - Credit Memo - the customer has an open credit memo (e.g. overpayment recorded as a CM) and wants the money back. - Unapplied Receipt - the customer paid more than they owed, or paid against the account with no specific invoice. The receipt has an unapplied/on-account balance to return. WORKFLOW (review and select) The template downloads (Create, Update mode) pre-populated with every eligible credit memo and unapplied receipt for the parameters, showing Customer Name and Open Balance. Review the list, then: 1. Set Refund = Yes on the rows to refund; leave blank to skip. Blank rows are not processed. 2. Refund Amount defaults to the source open balance - override with a smaller positive amount for a partial refund (validated > 0 and <= balance, in Excel and at upload). 3. Apply Date / Apply GL Date default to today; the GL Date must be in an open AR period. 4. Optionally set Payment Method, Pay Group, Payment Reason Comments, Comments - these flow to the AP Payment Request invoice. To add a row manually: pick Refund Source, then the Credit Memo Number or Receipt Number (the LOV adapts to the source). APPROVAL LIMITS The AR API runs with p_chk_approval_limit_flag='Y' - the user's AR approval limit applies. Rows over the limit error with the API's own message. Limits are never bypassed. END-TO-END FLOW 1. AR (this upload): a new ar_receivable_applications_all row is created (application_type='CM' or 'CASH', status='ACTIVITY'). 2. AR -> AP business event (automatic): AP creates a Payment Request invoice (source='Receivables', invoice_type_lookup_code='PAYMENT REQUEST') payable to the customer's TCA party. 3. AP Invoice Validation (standard AP concurrent). 4. AP Payment Process Request issues the disbursement. This upload performs step 1 only. Steps 2-4 are standard Oracle AP processing - a second human control point before any money leaves. PER-OU SETUP Each OU that processes refunds needs a Credit Memo Refund-type Receivables Activity (AR Setup -> Receipts -> Receivable Activities). The same activity is used for both CM and receipt refunds. If none, the upload errors: "No Credit Memo Refund activity is defined for this operating unit." CANDIDATE IDENTIFICATION - Credit Memo Number LOV: open credit memos in the OU whose customer account has no open invoices (standard refund-candidate rule). Set "Include accounts with open invoices" = Yes to override the safety filter (e.g. when open invoices are disputed). - Receipt Number LOV: unapplied/on-account customer receipts in the OU with positive unapplied amount. - Download in Create, Update mode to see existing candidates as rows. FILTER PARAMETERS - Operating Unit - filters both branches. - Customer Name - LOV of customers that have an eligible refund in the OU (respects the open-invoice rules below). - GL Date From / To, Credit Memo Number (multi-value), Batch Name (multi-value LOV on AR transaction batches) - Credit Memo branch only. The Receipt branch always shows all unapplied/on-account receipts in the chosen OU. - Include accounts with open invoices - default blank (= safety filter on for CMs). Does not apply to receipts. - Open Invoice Due Within Days - applies only when Include accounts with open invoices = Yes (otherwise ignored). Enter any number N: a credit memo is hidden only when the customer has an open invoice due within N days; invoices due later are ignored so the CM can still be refunded. Blank = due dates not considered. MODE Create, Update by default so the eligible candidates pre-populate. Only rows with Refund = Yes are processed; the result XLSM lists the refunded rows. Enginatics R12 only Upload 1=1 :include_with_open_invoices :open_invoice_due_within_days 1 10 1=1 :p_upload_mode like '%' || xxen_upload.action_update LOV Blitz Upload Mode E811F942597A2075E0530100007FF4F2 Y select xxen_upload.action_create id, xxen_upload.action_meaning(xxen_upload.action_create) value, null description from dual union all select xxen_upload.action_create || ',' || xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, null description from dual C,U Y US Upload Mode 2 20 1=1 haouv.name=:operating_unit LOV HR Operating Unit 8E2FF36EDEB979D2E0530100007F1FF2 Y select hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hou.organization_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) order by hou.name US Operating Unit 3 30 1=1 apsa.gl_date>=:gl_date_from Date US GL Date From 4 40 1=1 apsa.gl_date<:gl_date_to+1 Date US GL Date To 5 50 1=1 rcta.trx_number=:credit_memo_number Char US Credit Memo Number 6 60 1=1 hp.party_name=:customer_name LOV custom Y select distinct hp.party_name value, hca.account_number description 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 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 (:$flex$.operating_unit is null or haouv.name=:$flex$.operating_unit) 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(:$flex$.Include_accounts_with_open_invoices,'N')<>'Y' or :$flex$.open_invoice_due_within_days is not null and inv.due_date<trunc(sysdate)+:$flex$.open_invoice_due_within_days)) union select distinct hp.party_name value, hca.account_number description from ar_cash_receipts_all acra, hr_all_organization_units_vl haouv, hz_cust_accounts hca, hz_parties hp where acra.status='UNAPP' and acra.org_id=haouv.organization_id and (:$flex$.operating_unit is null or haouv.name=:$flex$.operating_unit) 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) order by value US Customer Name 7 70 1=1 rcta.batch_id=:batch_id LOV custom Y select rb.batch_id id, rb.name value, to_char(rb.batch_date,'DD-MON-YYYY')||' - '||haouv.name description from ra_batches rb, hr_all_organization_units_vl haouv where rb.org_id=haouv.organization_id and (:$flex$.operating_unit is null or haouv.name=:$flex$.operating_unit) and rb.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) order by rb.name desc US Batch Name 8 80 :include_with_open_invoices LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Include accounts with open invoices 9 90 :open_invoice_due_within_days Number US Open Invoice Due Within Days 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 MODIFIED_COLUMNS_ Char varchar2 Y 5 OPERATING_UNIT LOV custom varchar2 240 Y select hou.organization_id id, hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and hou.organization_id in (select aspa.org_id from ar_system_parameters_all aspa) and hou.organization_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) order by hou.name Y 6 REFUND LOV varchar2 30 Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual Set to Yes to refund this row; leave blank to skip it. The download pre-populates all eligible credit memos and unapplied receipts - tick Refund = Yes only on the ones you want to refund. Blank rows are not processed. 7 REFUND_SOURCE LOV custom varchar2 17 Y select 'CM' id, 'Credit Memo' value, null description from dual union all select 'RECEIPT' id, 'Unapplied Receipt' value, null description from dual Credit Memo Pick Credit Memo to refund an open credit memo, or Unapplied Receipt to refund the unapplied/on-account portion of a customer receipt. Only one of Credit Memo Number / Receipt Number is needed per row (the one matching the source). Y 8 CREDIT_MEMO_NUMBER LOV custom varchar2 20 Y select rcta.customer_trx_id id, rcta.trx_number value, hp.party_name description 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 :$column$.refund_source='Credit Memo' 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 haouv.name=:$column$.operating_unit and apsa.customer_id=hca.cust_account_id and hca.party_id=hp.party_id 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(:$flex$.Include_accounts_with_open_invoices,'N')<>'Y' or :$flex$.open_invoice_due_within_days is not null and inv.due_date<trunc(sysdate)+:$flex$.open_invoice_due_within_days) ) order by rcta.trx_number Required when Refund Source is Credit Memo. Lists open credit memos in the operating unit. 9 RECEIPT_NUMBER LOV custom varchar2 30 Y select acra.cash_receipt_id id, acra.receipt_number value, hp.party_name description from ar_cash_receipts_all acra, hr_all_organization_units_vl haouv, hz_cust_accounts hca, hz_parties hp where :$column$.refund_source='Unapplied Receipt' and acra.status='UNAPP' and acra.org_id=haouv.organization_id and haouv.name=:$column$.operating_unit and acra.pay_from_customer=hca.cust_account_id and hca.party_id=hp.party_id 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 ) order by acra.receipt_number Required when Refund Source is Unapplied Receipt. Lists unapplied/on-account customer receipts in the operating unit. 10 CUSTOMER_NAME Char varchar2 360 Read-only reference - the customer the credit memo or receipt belongs to, shown so you can review each row before selecting Refund. Y 11 OPEN_BALANCE Number number select case :$column$.refund_source when 'Credit Memo' then ( select abs(apsa.amount_due_remaining) from ar_payment_schedules_all apsa, ra_customer_trx_all rcta, hr_all_organization_units_vl haouv where rcta.trx_number=:$column$.credit_memo_number and haouv.name=:$column$.operating_unit and apsa.org_id=haouv.organization_id and apsa.customer_trx_id=rcta.customer_trx_id and apsa.class='CM' and apsa.status='OP' ) when 'Unapplied Receipt' then ( select sum(ara.amount_applied) from ar_receivable_applications_all ara, ar_cash_receipts_all acra, hr_all_organization_units_vl haouv where acra.receipt_number=:$column$.receipt_number and haouv.name=:$column$.operating_unit and acra.org_id=haouv.organization_id and ara.cash_receipt_id=acra.cash_receipt_id and ara.status in ('UNAPP','ACC') and ara.amount_applied>0 ) end from dual Read-only reference. Shows the credit memo's open remaining balance or the receipt's unapplied/on-account amount, so you can see what is available to refund alongside the Refund Amount you enter. Y 12 REFUND_AMOUNT Number number select case :$column$.refund_source when 'Credit Memo' then ( select abs(apsa.amount_due_remaining) from ar_payment_schedules_all apsa, ra_customer_trx_all rcta, hr_all_organization_units_vl haouv where rcta.trx_number=:$column$.credit_memo_number and haouv.name=:$column$.operating_unit and apsa.org_id=haouv.organization_id and apsa.customer_trx_id=rcta.customer_trx_id and apsa.class='CM' and apsa.status='OP' ) when 'Unapplied Receipt' then ( select sum(ara.amount_applied) from ar_receivable_applications_all ara, ar_cash_receipts_all acra, hr_all_organization_units_vl haouv where acra.receipt_number=:$column$.receipt_number and haouv.name=:$column$.operating_unit and acra.org_id=haouv.organization_id and ara.cash_receipt_id=acra.cash_receipt_id and ara.status in ('UNAPP','ACC') and ara.amount_applied>0 ) end from dual Defaults to the source's open balance (credit memo) or unapplied amount (receipt) when a source is picked. Enter a smaller positive amount for a partial refund - it cannot exceed the source balance and must be greater than zero (validated in Excel and at upload time). 13 APPLY_DATE Date date trunc(sysdate) 14 APPLY_GL_DATE Date date trunc(sysdate) Must fall within an open AR accounting period. 15 PAYMENT_METHOD LOV custom varchar2 100 Y select ipmv.payment_method_code id, ipmv.payment_method_name value, null description from iby_payment_methods_vl ipmv where nvl(ipmv.inactive_date,sysdate+1)>sysdate order by ipmv.payment_method_name Optional. Payment method for the downstream AP Payment Request invoice. 16 PAY_GROUP LOV custom varchar2 80 Y select flvv.lookup_code id, flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='PAY GROUP' and flvv.security_group_id=0 and flvv.enabled_flag='Y' and trunc(sysdate) between nvl(flvv.start_date_active,trunc(sysdate)) and nvl(flvv.end_date_active,trunc(sysdate)) order by flvv.meaning Optional. Pay group for the downstream AP Payment Request invoice. 17 PAYMENT_REASON_COMMENTS Char varchar2 240 Optional free-text note flowed to the AP Payment Request invoice. 18 COMMENTS Char varchar2 240 Optional comment recorded on the AR application. 19 UPLOAD_ROW Number number Y U 1 status_ out Char STATUS_CODE_ U 2 message_ out Char MESSAGE_ U 3 p_org_id in Number OPERATING_UNIT U 4 p_refund in Char REFUND U 5 p_refund_source in Char REFUND_SOURCE U 6 p_customer_trx_id in Number Y CREDIT_MEMO_NUMBER U 7 p_cash_receipt_id in Number Y RECEIPT_NUMBER U 8 p_refund_amount in Number Y REFUND_AMOUNT U 9 p_apply_date in Date Y APPLY_DATE U 10 p_apply_gl_date in Date Y APPLY_GL_DATE U 11 p_payment_method_code in Char Y PAYMENT_METHOD U 12 p_pay_group in Char Y PAY_GROUP U 13 p_payment_reason_comments in Char Y PAYMENT_REASON_COMMENTS U 14 p_comments in Char Y COMMENTS U 15 p_upload_row in Number Y UPLOAD_ROW Error SQL Order By upload_row, operating_unit, refund_source, credit_memo_number, receipt_number Success SQL select null action_, xu.status_, xu.message_, xu.modified_columns_, xu.operating_unit, xu.refund, xu.refund_source, xu.credit_memo_number, xu.receipt_number, xu.customer_name, xu.open_balance, araa.amount_applied refund_amount, trunc(araa.apply_date) apply_date, trunc(araa.gl_date) apply_gl_date, xu.payment_method, xu.pay_group, xu.payment_reason_comments, araa.comments, xu.upload_row from xxen_ar_refund_upload_5128_u xu, hr_all_organization_units_vl haouv, ra_customer_trx_all rcta, ar_receivable_applications_all araa, ar_receivables_trx_all arta where xu.status_code_=xxen_upload.status_success and xu.refund_source='Credit Memo' and xu.operating_unit=haouv.name and haouv.organization_id=rcta.org_id and rcta.trx_number=xu.credit_memo_number and rcta.customer_trx_id=araa.customer_trx_id and araa.application_type='CM' and araa.status='ACTIVITY' and araa.display='Y' and araa.request_id=coalesce( (select fcr.parent_request_id from fnd_concurrent_requests fcr where fcr.request_id=fnd_global.conc_request_id), (select xrr.upload_request_id from xxen_report_runs xrr where xrr.run_id=to_number(regexp_substr(sys_context('userenv','action'),'^\d+'))) --webservice result render has no concurrent request; the engine stamps the run id into the session action ) and araa.receivables_trx_id=arta.receivables_trx_id and arta.type='CM_REFUND' union all select null action_, xu.status_, xu.message_, xu.modified_columns_, xu.operating_unit, xu.refund, xu.refund_source, xu.credit_memo_number, xu.receipt_number, xu.customer_name, xu.open_balance, araa.amount_applied refund_amount, trunc(araa.apply_date) apply_date, trunc(araa.gl_date) apply_gl_date, xu.payment_method, xu.pay_group, xu.payment_reason_comments, araa.comments, xu.upload_row from xxen_ar_refund_upload_5128_u xu, hr_all_organization_units_vl haouv, ar_cash_receipts_all acra, ar_receivable_applications_all araa, ar_receivables_trx_all arta where xu.status_code_=xxen_upload.status_success and xu.refund_source='Unapplied Receipt' and xu.operating_unit=haouv.name and haouv.organization_id=acra.org_id and acra.receipt_number=xu.receipt_number and acra.cash_receipt_id=araa.cash_receipt_id and araa.application_type='CASH' and araa.status='ACTIVITY' and araa.display='Y' and araa.request_id=coalesce( (select fcr.parent_request_id from fnd_concurrent_requests fcr where fcr.request_id=fnd_global.conc_request_id), (select xrr.upload_request_id from xxen_report_runs xrr where xrr.run_id=to_number(regexp_substr(sys_context('userenv','action'),'^\d+'))) ) and araa.receivables_trx_id=arta.receivables_trx_id and arta.type='CM_REFUND'