Alert on Order is booked but line is Entered

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   (select lower(name) from v$database) instance_name ,RPAD(HZP.PARTY_NAME,40)
,        RPAD(OOH.CUST_PO_NUMBER,35)
,        RPAD(OOH.ORDER_NUMBER,15)
,        RPAD(OOL.LINE_NUMBER,10)
,        RPAD(OOL.ORDERED_ITEM,40)
,        RPAD(OOL.ORDERED_QUANTITY,15)
,        RPAD(HCSU.LOCATION,15)  
,        RPAD(DECODE(OOL.DELIVER_TO_ORG_ID,NULL,NULL,HCSU2.LOCATION),15) 
,        RPAD('CUSTOMER',41)||RPAD('CUST_PO_NUMBER',36)
                            ||RPAD('ORDER_NUMBER',16)
                            ||RPAD('LINE_NUM',11)
                            ||RPAD('ORDERED_ITEM',41)
                            ||RPAD('ORDERED_QTY',16)
                            ||RPAD('SHIP_TO_STORE',16)
                            ||RPAD('DELIVER_TO',16)
,        RPAD('--------',41)||RPAD('--------------',36)
                            ||RPAD('------------',16)
                            ||RPAD('--------',11)
                            ||RPAD('------------',41)
                            ||RPAD('-----------',16)
                            ||RPAD('-------------',16)
                            ||RPAD('----------',16)
 INTO    &instance,&CUSTOMER
,        &CUST_PO_NUMBER
,        &ORDER_NUMBER 
,        &LINE_NUMBER
,        &ORDERED_ITEM
,        &ORDERED_QUANTITY
,        &SHIP_TO_STORE 
,        &DELIVER_TO 
,        &HEADING
,        &UNDER_LINE 
 FROM    OE_ORDER_LINES_ALL              OOL,
         OE_ORDER_HEADERS_ALL            OOH,
         HZ_CUST_ACCOUNTS_ALL            HCA,
         HZ_PARTIES                      HZP,
         HZ_CUST_SITE_USES_ALL           HCSU,
         HZ_CUST_ACCT_SITES_ALL          HCAS,
         HZ_PARTY_SITES                  HPS,
         HZ_LOCATIONS                    HZL,
         HZ_CUST_ACCOUNTS_ALL            HCA2,
         HZ_PARTIES                      HZP2,
         HZ_CUST_SITE_USES_ALL           HCSU2,
         HZ_CUST_ACCT_SITES_ALL          HCAS2,
         HZ_PARTY_SITES                  HPS2,
         HZ_LOCATIONS                    HZL2
WHERE    OOL.HEADER_ID            =   OOH.HEADER_ID
  AND    HCA.PARTY_ID             =   HZP.PARTY_ID
  AND    HCSU.CUST_ACCT_SITE_ID   =   HCAS.CUST_ACCT_SITE_ID(+)
  AND    HCAS.PARTY_SITE_ID       =   HPS.PARTY_SITE_ID
  AND    HPS.LOCATION_ID          =   HZL.LOCATION_ID
  AND    HCSU.SITE_USE_ID         =   OOL.SHIP_TO_ORG_ID
  AND    OOH.SOLD_TO_ORG_ID       =   HCA.CUST_ACCOUNT_ID
  AND    HCA2.PARTY_ID            =   HZP2.PARTY_ID
  AND    HCSU2.CUST_ACCT_SITE_ID  =   HCAS2.CUST_ACCT_SITE_ID(+)
  AND    HCAS2.PARTY_SITE_ID      =   HPS2.PARTY_SITE_ID
  AND    HPS2.LOCATION_ID         =   HZL2.LOCATION_ID
  AND    HCSU2.SITE_USE_ID        =   NVL(OOL.DELIVER_TO_ORG_ID,OOL.SHIP_TO_ORG_ID)
  AND    OOH.SOLD_TO_ORG_ID       =   HCA2.CUST_ACCOUNT_ID
  AND    OOL.FLOW_STATUS_CODE='ENTERED'
  AND    OOH.FLOW_STATUS_CODE='BOOKED'
  AND    OOH.CREATION_DATE LIKE SYSDATE

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.