Oracle EBS R12 OPM Quality Sample number with Inventory Lot Script

--OPM Quality Sample data with Lot number

 SELECT a.source_header_id,

       a.source_line_id,

       g.sample_no,

       g.sample_desc,

       g.sample_nosample_qty,

       g.sample_qty_uom,

       a.LOT_NUMBER,

       e.test_unit "TEST",

       NVL (b.result_value_char, b.result_value_num) "TEST RESULTS",

       e.test_id,

       c.segment1 item_no,

       c.inventory_item_id item_id,

       d.lot_number lot_no,

       f.min_value_num,

       b.result_date,

       f.max_value_num,

       g.lot_no,

       e.test_unit,

       f.target_value_num,

       b.test_id,

       f.target_value_char "TEXT_SPEC",

       (f.min_value_num || ' - ' || f.max_value_num || ' - ' || test_unit)

          "SPEC",

       g.lot_no

  FROM wsh_delivery_details a,

       gmd_samples g,

       gmd_results b,

       gmd_qc_tests e,

       gmd_spec_tests_b f,

       gmd_event_spec_disp h,

       mtl_system_items_b c,

       mtl_lot_numbers d

 WHERE     1 = 1

       AND a.lot_number = g.lot_number

       AND a.inventory_item_id = g.inventory_item_id

       AND a.organization_id = g.organization_id

       AND b.sample_id = g.sample_id

       AND b.test_id = e.test_id

       AND b.test_id = f.test_id

       AND f.spec_id = h.spec_id

       AND h.sampling_event_id = g.sampling_event_id

       AND a.inventory_item_id = c.inventory_item_id

       AND a.organization_id = c.organization_id

       AND g.inventory_item_id = c.inventory_item_id

       AND g.organization_id = c.organization_id

       AND a.inventory_item_id = d.inventory_item_id

       AND a.organization_id = d.organization_id

       AND a.lot_number = d.lot_number

       AND d.inventory_item_id = c.inventory_item_id

       AND d.organization_id = c.organization_id

       AND d.inventory_item_id = g.inventory_item_id

       AND d.organization_id = g.organization_id

       AND d.lot_number = g.lot_number

       AND a.released_status = 'C'

       --AND e.test_code not like '%DESPATCH%'

       AND e.delete_mark = 0

       --AND b.attribute18 = 'Y'

       AND b.sample_id IN

              (SELECT MAX (sample_id)

                 FROM gmd_samples

                WHERE     inventory_item_id = g.inventory_item_id

                      AND SOURCE = NVL (:p_source, SOURCE)

                      AND lot_number = g.lot_number)

       AND G.SOURCE = NVL (:p_source, G.SOURCE)

Order By g.sample_no       

OPM: Production Batch and Status Query

/*Production  Batch and Status */

SELECT  batch_no,flv1.meaning batch_status

    --,DECODE(gbh.BATCH_STATUS,1,'Pending',2,'WIP',3,'Completed',4,'Closed','Cancelled') STATUS

    ,recipe_validity_rule_id,formula_id,routing_id

    ,actual_start_date,due_date,plan_cmplt_date,actual_cmplt_date

FROM gme_batch_header gbh

    ,fnd_lookup_values flv1

   WHERE -3 = -3

     AND flv1.lookup_code = TO_CHAR (gbh.batch_status)

     AND flv1.lookup_type = 'GME_BATCH_STATUS'

     AND flv1.LANGUAGE = 'US'

--     AND flv1.meaning <> 'Cancelled'

OPM Class Extraction Script

 /* Cost Component Class*/
SELECT   cost_cmpntcls_code, cmpnt_group, cost_cmpntcls_desc
    FROM cm_cmpt_mst_vl
   --WHERE cost_cmpntcls_code LIKE 'OSD2%'
ORDER BY 1

/*  Activity Class */
SELECT   activity, activity_desc
    FROM gmd_activities_vl
ORDER BY 1

/* Resource Class*/
SELECT   resource_class, resource_class_desc
    FROM cr_rsrc_cls_vl
ORDER BY 1

/* Operation Class */
SELECT   oprn_class operation_class, oprn_class_desc
    FROM fm_oprn_cls
ORDER BY 1

/* Routing Class */
SELECT   routing_class, routing_class_desc
    FROM fm_rout_cls
ORDER BY 1

/* Formula Class */
SELECT   formula_class, formula_class_desc
    FROM fm_form_cls
ORDER BY 1

/* Alternate Resource*/
SELECT   crm.resources primary_resource,
         crm.resource_desc primary_resource_desc, cam.alternate_resource,
         cam.runtime_factor, cam.preference, msi.segment1 item_code,
         msi.description item_description
    FROM cr_rsrc_mst crm,
         cr_ares_mst cam,
         gmp_altresource_products gap,
         (SELECT inventory_item_id, segment1, description
            FROM mtl_system_items_b
           WHERE enabled_flag = 'Y' AND organization_id = 1823   -- MASTER ORG 
                                                              ) msi
   WHERE -1 = -1
     AND crm.resources = cam.primary_resource
     AND crm.resources = gap.primary_resource
     AND cam.alternate_resource = gap.alternate_resource
     AND msi.inventory_item_id = gap.inventory_item_id
ORDER BY 1

MTL Transaction Lot number to find OPM Batch Number Query

SELECT gbh.batch_no 

FROM gme_batch_header gbh

WHERE EXISTS

(SELECT 1          

   FROM mtl_transaction_lot_numbers mtln,mtl_lot_numbers lot,

        mtl_material_transactions mmt

  WHERE mmt.transaction_type_id = 35  --- WIP Issue 

    AND mtln.lot_number = lot.lot_number 

    AND mtln.transaction_id = mmt.transaction_id

    AND mmt.organization_id = NVL(:p_organization_id,mmt.organization_id)

    AND lot.lot_number = NVL(:P_lot_number,lot.lot_number)

    AND mmt.transaction_source_id is not null

    AND gbh.batch_id = mmt.transaction_source_id)

/

OPM Recipe, Routing, Formula, Operation, Activity and Resource Data Extraction Script

--Recipe, Routing, Formula, Operation, Activity and Resource Find in a Single Query:

SELECT   ood.organization_code, recipe_no, recipe_version, recipe_description,
         (SELECT meaning FROM gmd_status WHERE status_code = gr.recipe_status) recipe_status
         , grv.orgn_code validity_orgn_code
         ,(SELECT meaning FROM gmd_status WHERE status_code = grv.validity_rule_status) validity_rule_status
         ,ff.formula_no, ff.formula_vers,ff.formula_description, ff.product_qty,ff.formula_status
         ,rr.routing_no, rr.routing_vers,rr.routing_description, rr.routing_qty, routing_uom, rr.routing_status
         ,aa.routingstep_no,aa.step_qty, aa.oprn_no, aa.oprn_vers
         ,aa.operation_status
         ,aa.activity, aa.resources
    FROM gmd_recipes gr
         ,org_organization_definitions ood                   
         ,gmd_recipe_validity_rules grv
         ---------Routing----------
         ,(SELECT   frh.routing_id,organization_code, routing_no, routing_vers
                    ,routing_desc routing_description, frh.routing_qty, routing_uom
                    ,(SELECT meaning FROM gmd_status WHERE status_code = frh.routing_status) routing_status
                FROM fm_rout_hdr frh, org_organization_definitions ood
               WHERE frh.owner_organization_id = ood.organization_id
            GROUP BY organization_code,frh.routing_id,
                     routing_no,
                     routing_vers,
                     routing_desc,
                     routing_uom,
                     routing_qty,frh.routing_status
            ORDER BY organization_code, routing_no) RR
        ------------Formula---------------------------
        ,(SELECT  ffm.formula_id, organization_code, formula_no, formula_vers,
                 formula_desc1 formula_description, qty product_qty,
                 fmd.detail_uom uom, total_input_qty, total_output_qty, yield_uom
                 ,(SELECT meaning FROM gmd_status WHERE status_code = ffm.formula_status) FORMULA_STATUS
            FROM fm_form_mst ffm,
                 (SELECT *
                    FROM fm_matl_dtl
                   WHERE line_type = 1) fmd,
                 org_organization_definitions ood
           WHERE ffm.formula_id = fmd.formula_id
             AND ffm.owner_organization_id = ood.organization_id
        GROUP BY organization_code,
                 formula_no,
                 formula_desc1,
                 formula_vers,
                 yield_uom,
                 qty,
                 fmd.detail_uom,
                 total_input_qty,
                 total_output_qty,ffm.formula_id,ffm.formula_status
        ORDER BY organization_code, formula_no)FF
        ------------Operation and Activity and Resource ---------------------------
        ,(SELECT gov.oprn_id, gor.oprn_line_id, frd.routing_id, frd.routingstep_no,
               frd.step_qty, gov.oprn_no, gov.oprn_vers
               ,(SELECT meaning FROM gmd_status WHERE status_code =  gov.operation_status) operation_status
               ,goa.activity, gor.resources
          FROM gmd_operation_resources gor,
               gmd_operation_activities goa,
               gmd_operations_vl gov,
               fm_rout_dtl frd
         WHERE -1 = -1
           AND gov.oprn_id = frd.oprn_id
           AND gov.oprn_id = goa.oprn_id
           AND gor.oprn_line_id = goa.oprn_line_id
        Order BY   frd.routingstep_no) AA
        ----------- END Resource and Operation -----------------
   WHERE gr.owner_organization_id = ood.organization_id
     AND gr.recipe_id = grv.recipe_id
     AND gr.routing_id = rr.routing_id(+)
     AND gr.formula_id = ff.formula_id(+)
     AND rr.routing_id = aa.routing_id(+)
GROUP BY ood.organization_code,recipe_no,
         recipe_version,
         recipe_description,  gr.recipe_status
         ,grv.orgn_code, grv.validity_rule_status
         ,ff.formula_no, ff.formula_vers,ff.formula_description, ff.product_qty,ff.formula_status
         ,rr.routing_no, rr.routing_vers,rr.routing_description, rr.routing_qty, rr.routing_uom, rr.routing_status       
         ,aa.routingstep_no,aa.step_qty, aa.oprn_no, aa.oprn_vers
         ,aa.operation_status
         ,aa.activity, aa.resources
ORDER BY ood.organization_code, recipe_no

Best Sellers in Music