EAM ,BOM ,WIP

 INV

-----------

select * from mtl_system_items_b where segment1='AS5499';

select * from mtl_system_items_tl where inventory_item_id=237063;

select * from mtl_item_categories  where  inventory_item_id =237063;

select * from mtl_item_revisions where inventory_item_id=237063;

select * from mtl_customer_items where customer_item_number='lenovo 15"led india';

select * from mtl_customer_item_xrefs where customer_item_id=13237;

select * from MTL_RESERVATIONS;

LINK MSIB.ORGANIZATION_ID=MR.ORGANIZATION_ID AND  MSIB.SEGMENT1='DELL 26" LCD';

select * from mtl_parameters;

select * from org_organization_codes;

MTL_PARAMETERS (MP)

MTL_SYSTEM_ITEMS_B (MSIB)

MTL_MATERIAL_TRANSACTION (MTT)

MTL_SERIAL_NUMBERS (MSN)

MTL_ITEM_REVISIONS (MIR)

MTL_ITEM_CATEGORIES (MIC)

MTL_DEMAND (MD)

MTL_TRANSACTION_TYPES (MTTY)

MTL_CATEGORIES (MC)

CST_ITEM_COSTS (CIC)

CST_COST_ELEMENTS (CCE)

Reservation Quantity

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

SELECT SUM(MR.RESERVATION_QUANTITY) ITEM

      FROM MTL_RESERVATIONS MR,

           MTL_SYSTEM_ITEMS_B MSIB

     WHERE MSIB.ORGANIZATION_ID=MR.ORGANIZATION_ID

      AND  MSIB.SEGMENT1='DELL 26" LCD'

     

Item Onhand Quantity

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

SELECT MOQ.TRANSACTION_QUANTITY ONHAND_QUANTITY

       FROM MTL_ONHAND_QUANTITIES MOQ,

            MTL_SYSTEM_ITEMS_B MSIB,

            MTL_PARAMETERS MP

       WHERE MOQ.INVENTORY_ITEM_ID=MSIB.INVENTORY_ITEM_ID

    AND  MOQ.ORGANIZATION_ID=MSIB.ORGANIZATION_ID

        AND  MSIB.ORGANIZATION_ID=MP.ORGANIZATION_ID

    AND  MOQ.ORGANIZATION_ID=MP.ORGANIZATION_ID

        AND   MSIB.SEGMENT1='DELL 26" LCD'

        AND  MP.ORGANIZATION_CODE='M1'

      

Master Organizations

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

SELECT ORGANIZATION_CODE MASTER_ORGANIZATION_CODE

      FROM  MTL_PARAMETERS MP

      WHERE ORGANIZATION_ID=MASTER_ORGANIZATION_ID

Child Organizations

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

SELECT ORGANIZATION_CODE CHILD_INV_ORG_CODE

      FROM  MTL_PARAMETERS MP

      WHERE ORGANIZATION_ID<>MASTER_ORGANIZATION_ID

  

Child under Master Organizations

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

SELECT MPC.ORGANIZATION_CODE,MPM.ORGANIZATION_CODE MASTER_ORGANIZATION_CODE

     FROM MTL_PARAMETERS MPC,

          MTL_PARAMETERS MPM

     WHERE MPC.MASTER_ORGANIZATION_ID=MPM.ORGANIZATION_ID  

    

SELECT MPC.ORGANIZATION_CODE,DECODE(MPC.ORGANIZATION_CODE,MPM.ORGANIZATION_CODE,'ITSELF',MPM.ORGANIZATION_ID) MASTER_ORGANIZATION_CODE

     FROM MTL_PARAMETERS MPC,

          MTL_PARAMETERS MPM

     WHERE MPC.MASTER_ORGANIZATION_ID=MPM.ORGANIZATION_ID

    

Combine Items

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

SELECT MSIB.SEGMENT1 ITEMS

      FROM MTL_SYSTEM_ITEMS_B MSIB,

           MTL_PARAMETERS MP

      WHERE MSIB.ORGANIZATION_ID=MP.ORGANIZATION_ID

      AND MP.ORGANIZATION_CODE='M1'      INTERSECT

SELECT MSIB.SEGMENT1 ITEMS

      FROM MTL_SYSTEM_ITEMS_B MSIB,

           MTL_PARAMETERS MP

      WHERE MSIB.ORGANIZATION_ID=MP.ORGANIZATION_ID

      AND MP.ORGANIZATION_CODE='M2'  

M1-M2      

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

SELECT MSIB.SEGMENT1 ITEMS

      FROM MTL_SYSTEM_ITEMS_B MSIB,

           MTL_PARAMETERS MP

      WHERE MSIB.ORGANIZATION_ID=MP.ORGANIZATION_ID

      AND MP.ORGANIZATION_CODE='M2'  

     MINUS

SELECT MSIB.SEGMENT1 ITEMS

      FROM MTL_SYSTEM_ITEMS_B MSIB,

           MTL_PARAMETERS MP

      WHERE MSIB.ORGANIZATION_ID=MP.ORGANIZATION_ID

      AND MP.ORGANIZATION_CODE='M1'

     

SELECT SUM(MOQ.TRANSACTION_QUANTITY) ONHAND_QUANTITY

      FROM MTL_ONHAND_QUANTITIES MOQ,

           MTL_SYSTEM_ITEMS_B MSIB

      WHERE MSIB.INVENTORY_ITEM_ID=MOQ.INVENTORY_ITEM_ID

      AND MSIB.ORGANIZATION_ID=MOQ.ORGANIZATION_ID

      AND MSIB.SEGMENT1='DELL 26" LCD'

EAM

-----------

Interface tables

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

New Asset Groups      ---- MTL_SYSTEM_ITEMS_INTERFACE

Revision Details      ---- MTL_ITEM_REVISIONS_INTERFACE

Item Categories       ---- MTL_ITEM_CATEGORIES_INTERFACE

the Table / View Name of the Asset Groups

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

 View Name – MTL_SYSTEM_ITEMS_VL

 

the Asset Group / Item Code is stored

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

SEGMENT1 or

CONCATENATED_SEGMENTS of MTL_SYSTEM_ITEMS_B_KFV

SELECT inventory_item_id, accounting_rule_id, invoicing_rule_id,

           segment1 asset_group, segment2, segment3, segment4, segment5

    FROM mtl_system_items

    WHERE eam_item_type = 1

BOM

---------

1.BOM_INVENTORY_COMPONENTS

2.BOM_BILL_OF_MATERIALS

BOM_RESOURCES (BR)

BOM_BILL_OF_MATERIALS (BBOM)

BOM_DEPARTMENTS (BD)

BOM_OPERATIONAL_ROUTINGS (BOR)

BOM_OPERATION_SEQUENCES (BOS)

BOM_OPERATION_SEQUENCES (BOS)

BOM_INVENTORY_COMPONENTS (BIC)

BOM_STANDARD_OPERATIONS (BSO)

Query

-------

select

bom.assembly_item_id,

(select msi.segment1 from mtl_system_items msi where msi.inventory_item_id=bom.assembly_item_id and msi.organization_id=85) parent_item,

lpad(' ',2*(level-1),' ')||

(select msi.segment1 from mtl_system_items msi where

msi.inventory_item_id=bic.component_item_id and msi.organization_id=207) child_item,

bic.component_item_id child_item_id,

bic.bill_sequence_id ,

bic.operation_seq_num ,

bic.bom_item_type ,

bic.item_num ,

level,

bic.component_quantity

from

bom_inventory_components bic,

(select * from bom_bill_of_materials where organization_id=85) bom

where bom.bill_sequence_id=bic.bill_sequence_id

start with bom.assembly_item_id=:P_BOM_ITEM_ID

connect by prior bic.component_item_id=bom.assembly_item_id;

WIP

---------

WIP_PARAMETERS (WP)

WIP_DISCRETE_JOBS (WDJ)

WIP_ENTITIES (WE)

WIP_OPERATIONS (WO)

WIP_REQUIREMENT_OPERATIONS (WRO)

WIP_OPERATION_RESOURCES (WOR)

WIP_TRANSACTIONS (WT)

WIP_TRANSACTION_ACCOUNTS (WTA)

WIP_LINES (WL)

WIP_EAM_WORK_REQUESTS (WEWR)

WIP_EAM_PARAMETERS (WEP)