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.
Alerts in receivables
-
- Posts: 615
- Joined: Mon Sep 18, 2006 1:32 am
- Location: United Arab Emirates
- Contact:
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
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 =
![Razz :P](./images/smilies/icon_razz.gif)
--AND ACRA.ORG_ID =
![Razz :P](./images/smilies/icon_razz.gif)
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
Who is online
Users browsing this forum: No registered users and 1 guest