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

Oracle Alert : Move order Created from Production Batch

I Got one Requirement from Production and Store Team, as below
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.

Sunday, September 13, 2015

Run Concurrent Program from Form: Launch SRS Built In

Form Personalization to run Concurrent Program from Form 

Built-in : Launch SRS Program.

Example :

Our Requirement is to Run Production Batch Sample Label from Production Batch Form.

We could completed this requirement through Form Personalization in below steps.

A)-Define 1 Menu : Special Menu for the Report, that shall show in tools Menu bar.

B)-Attach Concurrent Program menu with this Special Menu (defined in the above step).

C)- Store batch id ( For parameter to run your concurrent) in one Global variable.

D)- In Concurrent Definition window , Assign Global variable value to the Parameter for Batch id.

Here are the screen shots.

Requirement is like Below.

Goto Help --> Diagnostics --> Custom Code --> Personalize.

Add 1 New Sequence for You personalization, and Select Event as When-New-Form-Instance.

Goto Actions, and Choose any 1 special Menu.

Once menu is define, we need to attach Concurrent program to this menu.

Create new Sequence, for attaching The concurrent program with special menu defined in above step.

Goto Actions, here we need to define 2 Actions

1)- Global Variable to store Batch ID for Parameter.

2)- Attach Concurrent program to the Special Menu.

Global Variable name should be Logical and unique, as this needs to be pass to your concurrent program.

Save your work, Till here personalization is done for Concurrent program is attached and run, now Concurrent program will be run , but we have to assign Parameters manually, it will not pick any parameter automatically.

Step-3, To pass parameters from Batch form automatically.

GO TOà System adminà applicationà Concurrentà define

Now we have to attach Global variable from above steps to Concurrent Program.

Go to Parameters, and then pass global variable from the earlier form i.e, Batch Form.

Select Batch id parameter.

Its Default Type Should be SQL Statement.

Then Pass the 

“select :GLOBAL.XX_BATCH_ID from dual”

Now try to run report from Batch form,

It will automatically pass your current (Batch opened at form) to the report.

Tuesday, July 28, 2015

Daily Production Query with LOT

SELECT ood.organization_code, ood.organization_id, ood.organization_name,
       ood.operating_unit, gbh.batch_no,
     /*   DECODE (gmg.group_id,1002, 'PLANT_1'
                           ,1003, 'PLANT_2'
                           ,1004, 'PLANT_3'
                           ,1001 ,'WIP_CUBE'
                           ,1000, 'OSP' ) Plant, */
       TRUNC (gbh.actual_cmplt_date) date_of_production,
       TRUNC (gbh.actual_start_date) actual_start_date,
       msik.concatenated_segments product,
       msik.description product_description, gmd.actual_qty qty_produced,
       gmd.plan_qty, gmd.dtl_um uom,
       (Select gms.SOURCE_COMMENT
        from GMD_SAMPLES gms
        where gms.LOT_NUMBER = mtln.lot_number
        and gms.organization_id = msik.organization_id
        and gms.INVENTORY_ITEM_ID = msik.INVENTORY_ITEM_ID
        and rownum = 1) QC_Comments,
       ROUND (  gmd.actual_qty
              * 100
              / (SELECT DECODE (SUM (gmd1.actual_qty),
                                NULL, 1,
                                0, 1,
                                SUM (gmd1.actual_qty)
                               )
                   FROM gme_material_details gmd1
                  WHERE gmd1.line_type = '-1' AND gmd1.batch_id = gbh.batch_id),
              2
             ) yield_percent,
       flv.meaning batch_status,
                                --mmt.transaction_id,
                                --mmt.source_code,
                                mtln.lot_number,
          av.STATUS_CODE quality_status,
     /*  (SELECT flv_spec_disp.meaning spec_disposition
          FROM gmd_samples gs,
               gmd_sampling_events gse,
               gmd_event_spec_disp gesd,
               fnd_lookup_values flv_spec_disp
         WHERE gs.sampling_event_id = gse.sampling_event_id
           AND gse.sampling_event_id = gesd.sampling_event_id
           AND gesd.disposition = flv_spec_disp.lookup_code
           AND flv_spec_disp.lookup_type = 'GMD_QC_SAMPLE_DISP'
           AND gs.lot_number = mtln.lot_number
           AND gs.inventory_item_id = gmd.inventory_item_id
           AND gs.organization_id = gmd.organization_id
           AND ROWNUM = 1) quality_status,*/
       mmt.transaction_quantity, mmt.transaction_uom, mmt.transaction_date,
       gbh.attribute1 dcs_batch_number, gbh.attribute2 dcs_batch_date
  FROM gme_batch_header gbh,
       gme_material_details gmd,
       mtl_system_items_kfv msik,
       MTL_LOT_NUMBERS_ALL_V av,
       org_organization_definitions ood,
   --    gme_batch_groups_association gmg    ,
       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 gmg.batch_id=gbh.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 av.inventory_item_id=mtln.inventory_item_id
   and av.organization_id = mtln.organization_id
   and av.lot_number = mtln.lot_number
   AND flv.lookup_type = 'GME_BATCH_STATUS'
   AND flv.LANGUAGE (+) =  'PTB'
   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.organization_id in (select ood.organization_id from
--AND gbh.batch_no = '3040004832';
   AND ood.operating_unit = :p_org_id
  /*  and DECODE (gmg.group_id,1002, 'PLANT_1'
                           ,1003, 'PLANT_2'
                           ,1004, 'PLANT_3'
                           ,1001 ,'WIP_CUBE'
                           ,1000, 'OSP' ) = nvl(:p_plant,  DECODE (gmg.group_id,1002, 'PLANT_1'
                                               ,1003, 'PLANT_2'
                                               ,1004, 'PLANT_3'
                                               ,1001 ,'WIP_CUBE'
                                               ,1000, 'OSP' )) */
   AND gbh.batch_no BETWEEN NVL (:p_from_batch, gbh.batch_no)
                        AND NVL (:p_to_batch, gbh.batch_no)
   AND flv.meaning = NVL (:p_status, flv.meaning)
   AND msik.concatenated_segments between NVL(:P_FROM_ITEM,msik.concatenated_segments)
                                                                 AND NVL(:P_TO_ITEM,msik.concatenated_segments)
   AND TRUNC (mmt.transaction_date) BETWEEN NVL
                                                (TRUNC (TO_DATE (:p_from_date,
                                                                 'DD-MON-RRRR'
                                                                )
                                                       ),
                                                 TRUNC (mmt.transaction_date)
                                                )
                                         AND NVL
                                               (TRUNC (TO_DATE (:p_to_date,
                                                                'DD-MON-RRRR'
                                                               )
                                                      ),
                                                TRUNC (mmt.transaction_date)
                                               )
ORDER BY gbh.batch_no

Friday, July 24, 2015

SQL Query for Oracle OPM Receipe and Formula Dump

select gmd.RECIPE_NO Receipe_code,RECIPE_DESCRIPTION Receipe_Name ,gmd.RECIPE_VERSION,
(select distinct description from mtl_system_items mtl1,FM_MATL_DTL fd1
where fd1.line_type=1
and fd1.formula_id=fd.formula_id
and fd1.inventory_item_id = mtl1.inventory_item_id
and fd1.organization_id= mtl1.organization_id) product,
fm.formula_no,fm.FORMULA_DESC1,fm.FORMULA_VERS,
decode (gmd.RECIPE_STATUS,'900', 'Frozen','700','Approved for General Use') Rec_Status,
b.segment1||'-'||b.segment2 Item_code,
b.description item_desc,
fd.line_no,
fd.qty
from FM_FORM_MST fm,
FM_MATL_DTL fd,
mtl_system_items b,
GMD_RECIPE_VALIDITY_RULES GV,
gmd_recipes gmd
where  fd.formula_id=fm.formula_id
and fd.organization_id=fm.OWNER_ORGANIZATION_ID
and fm.formula_id =gmd.formula_id
and fm.owner_organization_id=gmd.owner_organization_id
AND GV.RECIPE_ID=GMd.RECIPE_ID
and b.inventory_item_id=fd.inventory_item_id
and b.organization_id=fm.OWNER_ORGANIZATION_ID
and fm.OWNER_ORGANIZATION_ID in (XXXX)
and fd.line_type in (-1,2)
--and fm.FORMULA_STATUS=700
and gmd.RECIPE_STATUS in (700)
--and gmd.RECIPE_NO=''XXXX
order by 1