Search This Blog

Friday, February 5, 2010

Notify when SO is booked or new line is entered on booked order

Based on a request from one of our reader, below are the steps on how to notify users when an order is booked or new line is inserted on a booked order.

The Alert below will send notification once a day in the morning at 8:00 am.

1) Define Alert

Query used is


SELECT ooh.order_number
, ool.line_number||'.'||ool.shipment_number line_number
, ordered_item, ordered_quantity, ool.flow_Status_code
INTO &order_num, &line_num,&Item_num, &Quantity, &line_Status
FROM oe_order_headers_all ooh, oe_order_lines_all ool
WHERE ooh.header_id = ool.header_id
AND
( ooh.booked_date >= to_date(Sysdate,'DD-MON-RRRR HH24:MI:SS')
OR (ool.creation_Date >= to_date(Sysdate,'DD-MON-RRRR HH24:MI:SS')
AND ool.creation_date > ooh.booked_date)
)

2) Define Actions
Click on the actions button and then actions Detail button and define message as shown in screenshot. Note that the message type is summary.

3) Define Action Sets
Click on action sets and then action set details and in the members tab enter the action defined in step 2

4) Schedule the Request
Navigate to Request --> Check and submit the alert. Based on the definition of alert it will be scheduled to run.

Kindly let me know if any questions.

Related Link: Introduction to Oracle Alerts

4 Comments:

Vijayakumar said...

Dear Suresh,

The articles in your website is excellent.It gives an in-depth knowledge to anyone.The presentation of the articles is also excellent.Good Work.

Can you please tel me,How will you Register Reports through API?.

Thanks in anticipation.

Thanks,
Kasi, Vijayakumar.

Anonymous said...

Dear Suresh,

The articles in your website is to good .Realy Good Work.

Can you tell me , How to prevent purhase order withot autocreate .

thanks
K Joshi

Lakki Reddy Sreehari Reddy said...

Hi Suresh,

I need to fire sales order report automatically whenever user changed the quantity of sales order line only. (Increase or decrease).

We can do this through triggers.

Is there any other process to do this..

Please do needful.

AnjanaReddy said...

Hi Suresh,

We have created an alert and we r getting alert mails as per defined schedule in alert. my problem is, whenever alert query returns 2 rows data then we are getting 2 alert mails for each one.

Can you tell me , How to change 2 alerts details in one mail.

Thanks,
AnjanaReddy

Copyright (c) All rights reserved. Presented by Suresh Vaishya