OPM Product Qty in batches
Description
Categories: Draft
Query to Find out Quantity of Products and Ingredients of all Batches in particular Month.
Run
OPM Product Qty in batches and other Oracle EBS reports with Blitz Report™ on our demo environment
select gr.recipe_description, gbh.recipe_validity_rule_id, gmd.inventory_item_id, msib.description, --gmd.line_type, decode(gmd.line_type,-1,'ingredient',2,'product') type, sum(mmt.transaction_quantity) quantity from gme_batch_header gbh, gmd_recipes gr, gmd_recipe_validity_rules grr, gme_material_details gmd, mtl_system_items_b msib, mtl_material_transactions mmt, mtl_parameters mp where 1=1 and gbh.formula_id=gr.formula_id and gbh.routing_id=gr.routing_id and gbh.recipe_validity_rule_id=grr.recipe_validity_rule_id and grr.recipe_id=gr.recipe_id and gbh.batch_id=gmd.batch_id and gbh.organization_id=gmd.organization_id and gmd.inventory_item_id=msib.inventory_item_id and gmd.organization_id=msib.organization_id and gbh.batch_id=mmt.transaction_source_id and gbh.organization_id=mmt.organization_id and gbh.organization_id=mp.organization_id and gmd.inventory_item_id=mmt.inventory_item_id --and gbh.batch_no in (select batch_no from apps.gme_batch_header where trunc(plan_start_date) between :from_date and :to_date) --and trunc(mmt.transaction_date) between :from_date and :to_date group by gr.recipe_description, gbh.recipe_validity_rule_id, gmd.inventory_item_id, msib.description, gmd.line_type order by recipe_description |