Alert Once Lot Is Generated from Plant in OPM

I got one Requirement from one of my client,

They need one alert to be Generated from system, as soon as Production batch Generates a lot , Respective departments like Logistics, planning and Quality should get immediate Alert, containing info like , Batch Number, Lot Number, Product Code, Quantity.


Query :



SELECT ood.operating_unit,
       ood.organization_code,
       gbh.batch_no,
       msik.segment1||'-'||msik.segment2 product,
       msik.description product_description,
       gmd.actual_qty qty_produced,
       mtln.lot_number
   INTO &ORG,
       &ORG_CODE,
       &BATCH_NO,
       &CODE,
       &DESC,
       &QTY,
       &LOT
  FROM gme_batch_header gbh,
       gme_material_details gmd,
       mtl_system_items msik,
       org_organization_definitions ood,
       fnd_lookup_values flv,
       (SELECT *
          FROM mtl_material_transactions
         WHERE transaction_type_id IN (44, 17)) mmt,
       mtl_transaction_lot_numbers mtln
 WHERE gbh.batch_id = gmd.batch_id
   AND gmd.line_type = 1
   AND gmd.cost_alloc =
                  (SELECT MAX (gmd1.cost_alloc)
                     FROM gme_material_details gmd1
                    WHERE gmd1.batch_id = gmd.batch_id AND gmd1.line_type = 1)
   AND gmd.inventory_item_id = msik.inventory_item_id
   AND gmd.organization_id = msik.organization_id
   AND gmd.organization_id = ood.organization_id
   AND gbh.batch_status = flv.lookup_code
   AND flv.lookup_type = 'GME_BATCH_STATUS'
   AND flv.language='US'
   AND gmd.batch_id = mmt.transaction_source_id(+)
   AND gmd.inventory_item_id = mmt.inventory_item_id(+)
   --AND mmt.source_code(+) = 'OPM'
   AND mmt.transaction_id = mtln.transaction_id(+)
   AND ood.operating_unit = XXX
and msik.segment2  <> '000000'
  --  AND TRUNC (mmt.transaction_date) =trunc(sysdate)
and mtln.rowid=:rowid



Steps: 1st Define Event Based Alert , On MTL_TRANSACTION_LOT_NUMBERS , with AFTER INSERT event.

Once Alert query is defined, click on Action Button.

Give Email id's to whom this alert is expected to receive, Then Subject of the alert , and define Text as below.

From : Support Team,

Below Batch and Lot generated for QC Inspection.

BATCH NO      :&BATCH_NO
LOT NUMBER    :&LOT
PRODUCT CODE  :&CODE
PRODUCT DESC  :&DESC
QUANTITY      :&QTY


Thank You
 (ERP Department)