ERPstuff - Oracle EBS Community

Hi,

I'am new to ORACLE ALERTS and I need to set an alert in oracle receivables when a RECEIPT IS REVERSED. Can anyone tell me what are the steps that I would need to follow to achieve the requirement. Kindly hlep me.

Regards,
BEN.

Hi,

I just wanted to add more detail to the Question. I would like to set a PERIODIC ALERT and even have an e-mail sent to the concerned person...kindly help me.

Regards,
Ben.

i am using following query in alerts to get higest value cheque bounced. modify it according to your requirement

SELECT rc.customer_number,
rc.customer_name,
COUNT (acra.receipt_number),
SUM (acra.amount)
INTO &output1, &output2, &output3,&output4
FROM ar_cash_receipts_all acra,
ra_customers rc,
ar_cash_receipt_history_all acrha,
hz_locations hl,
hz_cust_acct_sites_all hcasa,
hz_cust_site_uses_all hcsua,
hz_party_sites hps,
ra_territories rt
WHERE acra.pay_from_customer = rc.customer_id
AND acra.cash_receipt_id = acrha.cash_receipt_id
AND acrha.status = 'REVERSED'
<b>--AND ACRA.REVERSAL_REASON_CODE = _REASON
--AND ACRA.ORG_ID = _ORG_ID</b>
AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
AND hcasa.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_id
AND hcsua.org_id = hcasa.org_id
AND hcsua.org_id = acra.org_id
AND hcsua.territory_id = rt.territory_id
AND acra.customer_site_use_id = hcsua.site_use_id
GROUP BY rc.customer_number, rc.customer_name

and use in alerts