Sales Order Notification In Order Management
Client Requirement:
- When an Order is entered no alert should be sent.
- When the Order is booked the status changes to booked or awaiting shipping then the alert should be sent.
- When a new line is entered then an after insert event alert should be trigerred but duplicates should not be sent out
Query:
SELECT DISTINCT
OHA.ORDER_NUMBER,
OLA.LINE_NUMBER,
OHA.CUST_PO_NUMBER,
OHA.ORDER_CATEGORY_CODE
ORDER_TYPE,
OHA.FLOW_STATUS_CODE
HEADER_STATUS,
OLA.ORDERED_ITEM,
(SELECT RTRIM( SUBSTR (A.DESCRIPTION, 1,30)) DESCRIPTION
FROM
MTL_SYSTEM_ITEMS_B A
WHERE A.INVENTORY_ITEM_ID
= OLA.INVENTORY_ITEM_ID AND ROWNUM = 1)
DESCRIPTION,
TO_CHAR (OHA.ORDERED_DATE, 'DD-MON-YYYY')
ORDERED_DATE,
TO_CHAR (OLA.SCHEDULE_SHIP_DATE, 'DD-MON-YYYY')
SCHEDULE_SHIP_DATE,
TO_CHAR (OLA.REQUEST_DATE, 'DD-MON-YYYY')
REQUEST_DATE,
TO_CHAR (OLA.PROMISE_DATE, 'DD-MON-YYYY')
PROMISE_DATE,
OLA.ORDERED_QUANTITY,
ROUND (OLA.UNIT_SELLING_PRICE, 2)
UNIT_SELLING_PRICE,
OLA.FLOW_STATUS_CODE
LINE_STATUS,
HP.PARTY_NAME,
YHCA.ACCOUNT_NUMBER
CUSTOMER_NUMBER,
YHCA.ACCOUNT_NAME
CUSTOMER_ACCOUNT_NAME,
( SELECT DISTINCT HCP.EMAIL_ADDRESS
MAIL_ID
FROM HZ_PARTIES
HZ,
HZ_CUST_ACCOUNTS XHCA,
HZ_CUST_ACCT_SITES_ALL HCASA,
HZ_PARTY_SITES HPSA,
HZ_CONTACT_POINTS HCP
WHERE HZ.PARTY_ID = XHCA.PARTY_ID
AND HCASA.CUST_ACCOUNT_ID
= XHCA.CUST_ACCOUNT_ID
AND HCASA.PARTY_SITE_ID
= HPSA.PARTY_SITE_ID
AND HPSA.PARTY_SITE_ID
= HCP.OWNER_TABLE_ID
AND XHCA.CUST_ACCOUNT_ID
= YHCA.CUST_ACCOUNT_ID
AND HCP.EMAIL_ADDRESS
IS NOT NULL
AND HCP.CONTACT_POINT_PURPOSE
= 'BUSINESS'
AND ROWNUM = 1
GROUP BY HZ.PARTY_NAME, HCP.EMAIL_ADDRESS)
EMAIL_ADDRESS
INTO
&ORDER_NUMBER,
&LINE_NUMBER,
&CUST_PO_NUMBER,
&ORDER_TYPE,
&HEADER_STATUS,
&ORDER_ITEM,
&DESCRIPTION,
&ORDER_DATE,
&SCHEDULE_SHIP_DATE,
&REQUEST_DATE,
&PROMISE_DATE,
&ORDER_QUANTITY,
&UNIT_SELLING_PRICE,
&LINE_STATUS,
&PARTY_NAME,
&CUSTOMER_NUMBER,
&CUSTOMER_ACCOUNT_NAME,
&EMAIL_ADDRESS
FROM
OE_ORDER_HEADERS_ALL OHA,
OE_ORDER_LINES_ALL OLA,
HZ_PARTIES HP,
HZ_CUST_ACCOUNTS YHCA
WHERE 1 = 1
AND OHA.HEADER_ID = OLA.HEADER_ID
AND YHCA.CUST_ACCOUNT_ID
= OHA.SOLD_TO_ORG_ID
AND HP.PARTY_ID = YHCA.PARTY_ID
AND OHA.ORG_ID = 82
AND OHA.FLOW_STATUS_CODE
= 'BOOKED'
AND OHA.ROWID = :ROWID
ORDER BY 1, 2;
Steps:
Sample Message Layout:
MAX SALES ORDER NOTIFICATION
---------------------------------------------------------------------------------------------------------
Order Details:
-------------------------
Order Number:&ORDER_NUMBER
Ordered Date:&ORDER_DATE
Order Status:&HEADER_STATUS
Customer PO NO:&CUST_PO_NUMBER
Customer Name:&PARTY_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SO Item Description Request_Date Promise_Date Qty U.Price
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
=**= Enter summary template below this line =**=
**&LINE_NUMBER, &ORDER_ITEM , &DESCRIPTION , &REQUEST_DATE, &PROMISE_DATE, &ORDER_QUANTITY, &UNIT_SELLING_PRICE.
=**= Enter summary template above this line =**=