select
null action_,
null status_,
null message_,
null modified_columns_,
haouv.name operating_unit,
'Credit Memo' refund_source,
rcta.trx_number credit_memo_number,
cast(null as varchar2(30)) receipt_number,
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
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
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
(nvl(:include_with_open_invoices,'N')='Y' or 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
))
union all
select
null action_,
null status_,
null message_,
null modified_columns_,
haouv.name operating_unit,
'Unapplied Receipt' refund_source,
cast(null as varchar2(20)) credit_memo_number,
acra.receipt_number,
(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
where
1=1 and
acra.status='UNAPP' and
acra.org_id=haouv.organization_id and
acra.pay_from_customer=hca.cust_account_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
) |
