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
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.
No comments:
Post a Comment