Oracle Apss R12 OPM (Oracle Process manufacturing) Formula Base Tables and Detail of Products and Ingredients

select a.FORMULA_ID,a.formula_no,a.FORMULA_DESC1,b.INVENTORY_ITEM_ID,c.description,b.organization_id,
decode(b.line_type,-1,'Ingredient','Product') "Type"
from FM_FORM_MST a,FM_MATL_DTL b,mtl_system_items c
where a.formula_id=b.FORMULA_ID
and b.ORGANIZATION_ID=:your_Org_id
and a.FORMULA_CLASS<>'COSTING'
and b.INVENTORY_ITEM_ID=c.inventory_item_id
and b.ORGANIZATION_ID=c.organization_id
order by a.FORMULA_ID

---------------------


select  a.FORMULA_ID,a.FORMULA_NO,decode(a.FORMULA_VERS,1,'Version 1',2,'Version 2', 3, 'Version 3', 'Others') "Formula_Versions",
a.FORMULA_DESC1,b.INVENTORY_ITEM_ID,c.description,b.organization_id, --distinct(a.FORMULA_NO),
decode(b.line_type,-1,'Ingredient','Product') "Type"
from FM_FORM_MST a,FM_MATL_DTL b,mtl_system_items c, org_organization_definitions ood --- ORG_ORGANIZATION_DEFINITIONS
where a.FORMULA_ID = b.FORMULA_ID
and b.INVENTORY_ITEM_ID=c.inventory_item_id
and c.ORGANIZATION_ID = ood.ORGANIZATION_ID
---and b.ORGANIZATION_ID=c.organization_id
and ood.ORGANIZATION_CODE = '&A01'

--and b.ORGANIZATION_ID = c.ORGANIZATION_ID
--and b.ORGANIZATION_ID=:your_Org_id
--and a.FORMULA_CLASS<>'COSTING'

--order by a.FORMULA_NO, a.FORMULA_ID, a.FORMULA_DESC1,b.INVENTORY_ITEM_ID,c.description,b.organization_id

INVENTORY ORGANIZATION AND CORRESPONDING OPERATING UNIT, LEDGER AND LEGAL ENTITY

INVENTORY ORGANIZATION AND CORRESPONDING OPERATING UNIT, LEDGER AND LEGAL ENTITY

Select a.organization_id, a.organization_code, a.organization_name,
a.operating_unit, b.name OU, a.set_of_books_id,d.name LEDGER,
a.legal_entity,c.name LE_NAME
From  apps. ORG_ORGANIZATION_DEFINITIONS a,
apps. HR_OPERATING_UNITS b,
apps. xle_entity_profiles c,
apps. gl_ledgers d
Where a.operating_unit=b.organization_id
AND c.legal_entity_id=a.legal_entity
AND d.ledger_id=a.set_of_books_id

Display all categories that the Item Belongs

SELECT unique micv.CATEGORY_SET_NAME "Category Set",
micv.CATEGORY_SET_ID "Category Set ID",
decode( micv.CONTROL_LEVEL, 1, 'Master', 2, 'Org', 'Other') "Control Level",
micv.CATEGORY_ID "Category ID",
micv.CATEGORY_CONCAT_SEGS "Category"
FROM MTL_ITEM_CATEGORIES_V micv

Find the Request Groups and Concurrent Programs

SELECT g.request_group_name, c.user_concurrent_program_name,
a.application_name, g.application_id, g.request_group_id,
u.unit_application_id, u.request_unit_id
FROM fnd_request_groups g,
fnd_request_group_units u,
fnd_application_tl a,
fnd_concurrent_programs_tl c
WHERE g.request_group_id = u.request_group_id
AND u.unit_application_id = a.application_id
AND u.request_unit_id = c.concurrent_program_id
and c.USER_CONCURRENT_PROGRAM_NAME like '%Purchase Order%'
ORDER BY C.user_concurrent_program_name, A.application_name, g.request_group_id;

Total received Qty in 9 months

select sum(transaction_quantity) from mtl_material_transactions mtl
where mtl.organization_id = mtl.organization_id
and mtl.transaction_quantity>0
and mtl.transaction_date between trunc(sysdate) and trunc(sysdate-270)"kooo"; --- tot_rec_qty_9mths;

Total Received Qty

select sum(transaction_quantity),mt.transaction_date from mtl_material_transactions mt  
where mt.ORGANIZATION_ID = mt.ORGANIZATION_ID
and mt.ORGANIZATION_ID = 162
and mt.transaction_quantity>0
--and mt.transaction_date between trunc(sysdate) and trunc(sysdate-270)    ---"tot_rec_qty_9mths"
--order by mt.transaction_quantity,mt.transaction_date
group by mt.transaction_quantity,mt.transaction_date;

Query to Find Responsibilities assigned to particular user.

SELECT b.responsibility_name NAME
FROM apps.fnd_user_resp_groups a,
apps.fnd_responsibility_vl b,
apps.fnd_user u
WHERE a.user_id = u.user_id
AND u.user_id = (select user_id from FND_USER where user_name=:User_name)
AND a.responsibility_id = b.responsibility_id
AND a.responsibility_application_id = b.application_id
AND SYSDATE BETWEEN a.start_date AND NVL (a.end_date, SYSDATE + 1)
AND b.end_date IS NULL
AND u.end_date IS NULL

ROWID and ROWNUM

SELECT ROWID tt, rownum, last_name
   FROM employees
   WHERE department_id = 60;
 
 
SELECT * FROM employees WHERE ROWNUM < 10;


SELECT ROWID ooo, rownum   FROM employees
WHERE department_id = 80

select rowid ooo,rownum, ci.INVENTORY_ITEM_ID  from gmd_recipe_validity_rules ci 

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
GME_BATCH_HEADER a,
gmd_recipes b,
gmd_recipe_validity_rules grr,
gme_material_details c,
mtl_system_items d,
mtl_material_transactions e,
org_organization_definitions f
where a.FORMULA_ID = b.FORMULA_ID
and b.RECIPE_ID = grr.RECIPE_ID
and grr.ITEM_ID = c.ITEM_ID
and c.INVENTORY_ITEM_ID = d.INVENTORY_ITEM_ID
and d.INVENTORY_ITEM_ID = e.INVENTORY_ITEM_ID
and e.ORGANIZATION_ID = f.ORGANIZATION_ID
and f.ORGANIZATION_CODE = 'A01'
group by b.RECIPE_DESCRIPTION,a.RECIPE_VALIDITY_RULE_ID,c.INVENTORY_ITEM_ID,d.description,c.line_type,e.TRANSACTION_QUANTITY;

-------------------------

select ai.BATCH_NO,bi.RECIPE_DESCRIPTION,ci.STD_QTY,di.MATERIAL_DETAIL_ID,ei.DESCRIPTION,fi.TRANSACTION_QUANTITY,gi.ORGANIZATION_CODE,
decode(di.LINE_TYPE, -1,'Ingredient',1,'Product') "Type", sum(fi.TRANSACTION_QUANTITY)"quantity"
from gme_batch_header ai, gmd_recipes bi, gmd_recipe_validity_rules ci, gme_material_details di,mtl_system_items ei,
mtl_material_transactions fi,org_organization_definitions gi
where ai.FORMULA_ID = bi.FORMULA_ID
and bi.RECIPE_ID = ci.RECIPE_ID
and ci.ITEM_ID = di.ITEM_ID
and di.ORGANIZATION_ID = ei.ORGANIZATION_ID
and ei.INVENTORY_ITEM_ID = fi.INVENTORY_ITEM_ID
and fi.ORGANIZATION_ID = gi.ORGANIZATION_ID
and gi.ORGANIZATION_CODE = 'A01'
group by ai.BATCH_NO,bi.RECIPE_DESCRIPTION,ci.STD_QTY,di.MATERIAL_DETAIL_ID,ei.DESCRIPTION,fi.TRANSACTION_QUANTITY,gi.ORGANIZATION_CODE,
di.LINE_TYPE,fi.TRANSACTION_QUANTITY;

--and di.INVENTORY_ITEM_ID = ei.INVENTORY_ITEM_ID
--and ei.ORGANIZATION_ID = fi.ORGANIZATION_ID