Oracle Alert : Move order Created from Production Batch

I Got one Requirement from Production and Store Team, as below


Whenever Production team raises request for Move order against any batch in OPM, Automated mail should go to Stores Team.

So I created one Event Based Alert on Move Order Headers Table.


Query is:



SELECT  distinct ORG.ORGANIZATION_NAME,
         TRH.REQUEST_NUMBER,
         GBH.BATCH_NO,
         TRL.TO_SUBINVENTORY_CODE,
         TRL.STATUS_DATE,
         (SELECT (ppf.first_name || ' ' || ppf.last_name)
            FROM fnd_user fnd, per_all_people_f ppf
           WHERE fnd.user_id = trh.created_by
             AND ppf.person_id = fnd.employee_id) creater,
          (SELECT NVL (ppf.email_address, fnd.email_address)
            FROM fnd_user fnd, per_all_people_f ppf
           WHERE fnd.user_id = trh.created_by
             AND ppf.person_id = fnd.employee_id) Email
      INTO &ENTITY,
           &MOVE_ORDER,
           &BATCH_NO,
           &SUB_INV,
           &MV_DATE,
           &CREATER,
           &EMAIL_ID      
    FROM MTL_TXN_REQUEST_HEADERS TRH,
         MTL_TXN_REQUEST_LINES TRL,
         GME_BATCH_HEADER GBH,
         ORG_ORGANIZATION_DEFINITIONS ORG,
         MTL_PARAMETERS MP,
         MFG_LOOKUPS ML1,
         MTL_SYSTEM_ITEMS MSI,
         MFG_LOOKUPS ML2
   WHERE TRH.HEADER_ID = TRL.HEADER_ID
     AND TRH.ORGANIZATION_ID = TRL.ORGANIZATION_ID
     AND TRH.ORGANIZATION_ID = ORG.ORGANIZATION_ID
     AND TRL.TXN_SOURCE_ID=GBH.BATCH_ID
     AND GBH.ORGANIZATION_ID = TRH.ORGANIZATION_ID
     AND GBH.ORGANIZATION_ID = ORG.ORGANIZATION_ID
     AND TRH.ORGANIZATION_ID = MP.ORGANIZATION_ID
     AND TRH.MOVE_ORDER_TYPE = ML1.LOOKUP_CODE
     AND ML1.LOOKUP_TYPE = 'MOVE_ORDER_TYPE'
     AND TRL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
     AND TRL.ORGANIZATION_ID = MSI.ORGANIZATION_ID
     AND TRL.LINE_STATUS = ML2.LOOKUP_CODE
     AND ML2.LOOKUP_TYPE = 'MTL_TXN_REQUEST_STATUS'
     AND TRH.MOVE_ORDER_TYPE=5
     AND TRL.LINE_STATUS IN (3,7)
     AND ORG.OPERATING_UNIT=82--------------Operating unit ID
    AND TRH.rowid=:rowid
     ORDER BY 3,4





Message Body :

Stores Team : Below Move Order Generated from Plant Against Batch No :&BATCH_NO From &SUB_INV


Message Text:

From :Support Team

Below Move Order Generated from Plant

MOVE ORDER NO       : &MOVE_ORDER
ORGANIZATION NAME   : &ENTITY

Thank You
(ERP Support Team)



Screen Shots Captured As:

Provide Email Id's in Action Details and The Message Subject as per the need.