Scribd
Upload
120 views

Formula Query

This document contains two SQL queries that retrieve formula, product, and ingredient data from Oracle Process Manufacturing tables. The first query retrieves the formula ID, number, description, associated inventory items and descriptions, organization, and whether each item is an ingredient or product. The second query retrieves similar data but also includes the formula version. Both queries join tables to retrieve the required data for a given organization.

Uploaded by

Srinivasa Prabhu
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
120 views

Formula Query

This document contains two SQL queries that retrieve formula, product, and ingredient data from Oracle Process Manufacturing tables. The first query retrieves the formula ID, number, description, associated inventory items and descriptions, organization, and whether each item is an ingredient or product. The second query retrieves similar data but also includes the formula version. Both queries join tables to retrieve the required data for a given organization.

Uploaded by

Srinivasa Prabhu
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 1

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.organ
ization_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

You might also like