Sales Order Notification In Order Management

Sales Order Notification In Order Management

Client Requirement:

1. When an Order is entered no alert should be sent.
2. When the Order is booked the status changes to booked or awaiting shipping then the alert should be sent.
3. 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 =**=

         

GRN NOTIFICATION

GRN  NOTIFICATION PROCESS

Purpose:

·    Purpose of this extension is to allow the users to receive the email alerts once new receipt created in the respective PO then Oracle alert will send mail to the Item Planner.

Notification Feature:

GRN Notification provides you with the features you need to satisfy the following basic business needs.  You will be able to address the following:

·         Once the receipt made at the XXXX Stores

·         Alert is triggered

·         Alert carries set of information

o    GRN Number, GRN Date

o    PO Number, Supplier Name, Supplier Site

o    Item Name, Description, Receiving Quantity,

o    Purchase requisition Number and Quantity requested

·         Notification will contain the above information

·         Notification will be sent to the respective Item Planer (MTR).

Navigation:

Navigate to the Responsibility Alert Manager  -> Alert  -> Define . Details to be filled are below

Method

1. Build SQL Query as per requirement.
2. Paste in the Select Statement Area and Click on verify button to cross check.

3.. Click on Action Button

4. Click on Action Details Button fill it like below

5. Action Detials-->Text
ALERT/MAIL FORMAT:

                GRN Notification Information
------------------------------------------------------------------
GRN Number           : &GRN_NO
GRN Date             : &GRN_DATE
------------------------------------------------------------------
                    Purchase Order Details
------------------------------------------------------------------
PO Number            : &PONUMBER
Supplier Name        : &SUPPLIERNAME
Supplier Site        : &SUPPLIERSITE
--------------------------------------------------------------------
                    Item Details
--------------------------------------------------------------------
Item Planner         : &EMAIL
Item Number          : &ITEMNUMBER
Item Description     : &ITEMDESCRIPTION

Receipt Quantity     : &QUANTITY
---------------------------------------------------------------------
                    Requisition Details
---------------------------------------------------------------------
Requisition Number   : &REQ_NUMBER
Requisition Quantity : &REQ_QUANTITY
---------------------------------------------------------------------

6. Click on Action Sets

7. Action set Details-->Output

8. Action set Details-->Members

9. Alert Details-->Outputs

10. Alert Details-->Installations