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