Quantity of Ingredients and Products
Oracle Apps R12. OPM
Query to Find out Quantity of Products and Ingredients of all Batches in particular Month.
Select b.RECIPE_DESCRIPTION,a.RECIPE_VALIDITY_RULE_ID,c.INVENTORY_ITEM_ID,d.description,decode(c.line_type,-1,’Ingredient’,’Product’) Type,
sum(e.TRANSACTION_QUANTITY) quantity
from apps.GME_BATCH_HEADER a,apps.gmd_recipes b,gmd_recipe_validity_rules grr,apps.gme_material_details c,apps.mtl_system_items d,apps.mtl_material_transactions e
where a.FORMULA_ID=b.FORMULA_ID
and a.ROUTING_ID=b.ROUTING_ID
and a.RECIPE_VALIDITY_RULE_ID=grr.RECIPE_VALIDITY_RULE_ID
and grr.RECIPE_ID=b.recipe_id
and a.BATCH_ID=c.BATCH_ID
and a.ORGANIZATION_ID=c.ORGANIZATION_ID
and c.INVENTORY_ITEM_ID=d.INVENTORY_ITEM_ID
and c.ORGANIZATION_ID=d.organization_id
and a.batch_id=e.TRANSACTION_SOURCE_ID
and a.ORGANIZATION_ID=e.ORGANIZATION_ID
and c.INVENTORY_ITEM_ID=e.INVENTORY_ITEM_ID
and a.batch_no in (select batch_no from apps.GME_BATCH_HEADER where trunc(plan_start_date) between :from_date and :to_date)
and a.ORGANIZATION_ID=:your_org_id
and trunc(e.transaction_date) between :from_date and :to_date
group by b.RECIPE_DESCRIPTION,a.RECIPE_VALIDITY_RULE_ID,c.INVENTORY_ITEM_ID,d.description,c.line_type
order by RECIPE_DESCRIPTION
Be careful of using decode(c.line_type,-1,’Ingredient’,’Product’) Type if someone pulls this query. Our company has another product line known as ByProduct. Ours would show decode(c.line_type,-1,’Ingredient’,1,’Product’,2,’ByProduct’) Type. You may want to explicityly call out the comparison value instead of just leaving it as an EVERYTHING ELSE is this type.