----------Query For Sales order Details-----------
SELECT OOHA.HEADER_ID
,OOHA.ORDER_NUMBER
, OOLA.LINE_ID
, OOLA.ORDERED_ITEM
,OOLA.QUANTITY
,OOLA.UNIT_PRICE
, OOLA.ORG_ID
, MSIB.ORGANIZATION_ID
, MSIB.SEGMENT1
, MSIB.INVENTORY_ITEM_ID
FROM OE_ORDER_HEADERS_ALL OOHA
, OE_ORDER_LINES_ALL OOLA
, MTL_SYSTEM_ITEMS_B MSIB
, MTL_PARAMETERS MP
WHERE 1=1
AND OOHA.HEADER_ID = OOLA.HEADER_ID
AND OOLA.ORDERED_ITEM = MSIB.SEGMENT1
AND MSIB.ORGANIZATION_ID=MP.ORGANIZATION_ID;
------ Query for Assembly Details---------------
SELECT BS. ASSEMBLY_ITEM_ID
, MSIB.ORGANIZATION_ID
, BBOM.ASSEMBLY_TYPE
, BBOM.ASSEMBLY_ITEM_ID
, BC. ITEM_NUM
FROM BOM_BILL_OF_MATERIALS BBOM
, BOM_STRUCTURES_B BS
, BOM_COMPONENTS_B BC
, MTL_SYSTEM_ITEMS_B MSIB
WHERE 1=1
AND BBOM.ASSEMBLY_ITEM_ID = BS. ASSEMBLY_ITEM_ID
AND BC.COMPONENT_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND BS.ORGANIZATION_ID = MSIB.ORGANIZATION_ID;
----- Query For Category------------
SELECT
MCB.SEGMENT3
, MIC.INVENTORY_ITEM_ID
, MIC.ORGANIZATION_ID
, MCB.CATEGORY_ID
FROM
MTL_ITEM_CATEGORIES MIC
, MTL_CATEGORIES_B MCB
, MTL_CATEGORY_SETS MCS
WHERE 1=1
AND MIC.CATEGORY_ID = MCB.CATEGORY_ID
AND MIC.CATEGORY_SET_ID = MCS.CATEGORY_SET_ID
AND MCB.STRUCTURE_ID = MCS.STRUCTURE_ID;
------- BOM For Items-------------
SELECT
MB1.SEGMENT1
, MB2.SEGMENT1
, BC.COMPONENT_QUANTITY
, BC.COMPONENT_SEQUENCE_ID
, BS.ASSEMBLY_ITEM_ID
,BS.BILL_SEQUENCE_ID
, BC.COMPONENT_ITEM_ID
, MB2.ORGANIZATION_ID
, BE.EXPLOSION_TYPE
, BE.COMPONENT_ID
FROM
BOM_COMPONENTS_B BC
, BOM_STRUCTURES_B BS
, MTL_SYSTEM_ITEMS_B MB1
, MTL_SYSTEM_ITEMS_B MB2
, BOM_EXPLOSIONS BE
WHERE 1=1
AND BS.ASSEMBLY_ITEM_ID = MB1.INVENTORY_ITEM_ID
AND BC.COMPONENT_ITEM_ID = MB2.INVENTORY_ITEM_ID
AND BC.BILL_SEQUENCE_ID = BS.BILL_SEQUENCE_ID
AND MB1.ORGANIZATION_ID = MB2.ORGANIZATION_ID
AND BS.ORGANIZATION_ID = MB2.ORGANIZATION_ID
AND BBOM.BILL_SEQUENCE_ID = BE.BILL_SEQUENCE_ID;
----------Query for Sourcing Rule details---------------
SELECT MSR.SOURCING_RULE_ID
, MSR.SOURCING_RULE_NAME
, MSR.ORGANIZATION_ID
, MSR.SOURCING_RULE_TYPE
, MSIB.INVENTORY_ITEM_ID
, MSIB.SEGMENT1
, MP.MASTER_ORGANIZATION_ID
, MP.ORGANIZATION_CODE
FROM MRP_SOURCING_RULES MSR,
MTL_SYSTEM_ITEMS_B MSIB,
MTL_PARAMETERS MP
WHERE 1=1
AND MSR.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND MSIB.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MSIB.INVENTORY_ITEM_ID = 'your inventory id';
------------------- Query For Status Code---------
SELECT MSIB.INVENTORY_ITEM_ID
,MSIB.INVENTORY_ITEM_STATUS_CODE
,OOD.ORGANIZATION_ID
,BIC.ITEM_NUM
,BIC.COMPONENT_QUANTITY
,MSIB.PRIMARY_UOM_CODE
,BBOM.ASSEMBLY_ITEM_ID
FROM BOM_BILL_OF_MATERIALS BBOM
,BOM_INVENTORY_COMPONENTS BIC
,MTL_SYSTEM_ITEMS_B MSIB
,ORG_ORGANIZATION_DEFINITIONS OOD
WHERE 1=1
AND MSIB.INVENTORY_ITEM_ID = BIC.COMPONENT_ITEM_ID
AND MSIB.INVENTORY_ITEM_ID = BBOM.ASSEMBLY_ITEM_ID
AND MSIB.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND MSIB.INVENTORY_ITEM_STATUS_CODE NOT IN ('M_INACTIVE') ;
--------------- BOM RESOURCE QUERY-------------------
SELECT BOR.ROUTING_SEQUENCE_ID
,BOR.ASSEMBLY_ITEM_ID
,OOD.ORGANIZATION_ID
,BOR.ALTERNATE_ROUTING_DESIGNATOR
,BOR.ROUTING_TYPE
,BOR.COMMON_ASSEMBLY_ITEM_ID
,BOR.COMPLETION_SUBINVENTORY
,BOS.OPERATION_SEQUENCE_ID
,BOS.OPERATION_SEQ_NUM
,BD.DEPARTMENT_ID
,BSO.STANDARD_OPERATION_ID
,BSO.OPERATION_DESCRIPTION
,BSO.OPERATION_TYPE
,MSIB.INVENTORY_ITEM_ID
,MSIB.SEGMENT1 "ITEM NAME"
FROM BOM_OPERATIONAL_ROUTINGS BOR
,BOM_OPERATION_SEQUENCES BOS
,MTL_SYSTEM_ITEMS_B MSIB
,ORG_ORGANIZATION_DEFINITIONS OOD
,BOM_DEPARTMENTS BD
,BOM_STANDARD_OPERATIONS BSO
,BOM_OPERATION_RESOURCES BOR
,BOM_RESOURCES BR
WHERE 1=1
AND BOR.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND BOR.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND BOR.ASSEMBLY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND BOR.ROUTING_SEQUENCE_ID = BOS.ROUTING_SEQUENCE_ID
AND BOS.DEPARTMENT_ID = BD.DEPARTMENT_ID
AND BOR.OPERATION_SEQUENCE_ID = BOS.OPERATION_SEQUENCE_ID
AND BR.RESOURCE_ID = BOR.RESOURCE_ID;
---------Routing details---------------
SELECT MSIB.SEGMENT1
,MSIB.DESCRIPTION
,BOR.COMPLETION_SUBINVENTORY
,BOS.OPERATION_SEQ_NUM
,BOS.OPERATION_DESCRIPTION
,BR.RESOURCE_SEQ_NUM
,BR.RESOURCE_CODE
,BR.UOM RESOURCE_UOM
,USAGE_RATE_OR_AMOUNT
,DEPARTMENT_CODE
FROM BOM_OPERATIONAL_ROUTINGS_V BOR,
BOM_OPERATION_SEQUENCES_V BOS,
MTL_SYSTEM_ITEMS_B MSIB,
BOM_OPERATION_RESOURCES_V BR
WHERE BOR.ROUTING_SEQUENCE_ID = BOS.ROUTING_SEQUENCE_ID
AND BOR.ASSEMBLY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND BOR.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND BOR.ORGANIZATION_ID = 85
AND BOS.DISABLE_DATE BETWEEN 'FROM DATE' AND 'TO DATE'
AND BR.OPERATION_SEQUENCE_ID = BOS.OPERATION_SEQUENCE_ID;
---Routing Data----
SELECT SEGMENT1
,BOS.OPERATION_SEQ_NUM
,RESOURCE_SEQ_NUM
,EFFECTIVITY_DATE
FROM BOM_OPERATION_SEQUENCES BOS
,BOM_OPERATIONAL_ROUTINGS BOR
,MTL_SYSTEM_ITEMS_B MSIB
,BOM_OPERATION_RESOURCES BORS
WHERE 1=1
AND BOR.ROUTING_SEQUENCE_ID = BOS.ROUTING_SEQUENCE_ID
AND BORS.OPERATION_SEQUENCE_ID = BOS.OPERATION_SEQUENCE_ID
AND BOR.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND ASSEMBLY_ITEM_ID = INVENTORY_ITEM_ID
AND MSIB.ORGANIZATION_ID = 'ORGANIZATION_ID';
-----Query for BOM Data----------
SELECT MSIB.INVENTORY_ITEM_ID,
BBOM.ASSEMBLY_ITEM_ID,
BBOM.ORGANIZATION_ID,
BBOM.BILL_SEQUENCE_ID,
BBOM.ALTERNATE_BOM_DESIGNATOR,
BIC.OPERATION_SEQ_NUM,
BIC.BILL_SEQUENCE_ID,
BIC.COMPONENT_ITEM_ID,
BIC.ITEM_NUM,
BIC.COMPONENT_QUANTITY,
BIC.WIP_SUPPLY_TYPE,
MSIB.SEGMENT1,
MSIB.DESCRIPTION
FROM BOM_BILL_OF_MATERIALS BBOM,
BOM_INVENTORY_COMPONENTS BIC,
MTL_SYSTEM_ITEMS_B MSIB
WHERE 1=1
AND BBOM.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
AND BBOM.ASSEMBLY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND BBOM.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND MSIB.ORGANIZATION_ID = 'YOUR ORGANIZATION_ID' ;
No comments:
Post a Comment