Oracle BOM Routing Details Report

Oracle BOM Routing Details Report :

SELECT
MSI.SEGMENT1 ROUTING_ITEM,
BOR.ROUTING_SEQUENCE_ID,
TO_CHAR(DECODE(FU.EMAIL_ADDRESS , NULL , FU.DESCRIPTION , FU.EMAIL_ADDRESS )) ROUTING_CREATED_BY,
TO_CHAR(BOR.CREATION_DATE,'DY DD-MON-YYYY HH12:MI:SS') ROUTINGS_CREATION_DATE,
TO_CHAR(DECODE(FU1.EMAIL_ADDRESS , NULL , FU1.DESCRIPTION , FU1.EMAIL_ADDRESS ))  ROUTINGS_LAST_UPDATED_BY,
TO_CHAR(BOR.LAST_UPDATE_DATE,'DY DD-MON-YYYY HH12:MI:SS') ROUTINGS_LAST_UPDATE_DATE,
TO_CHAR(DECODE(FU2.EMAIL_ADDRESS , NULL , FU2.DESCRIPTION , FU2.EMAIL_ADDRESS )) ROUTINGS_LAST_UPDATE_LOGIN,

BOS.OPERATION_SEQ_NUM,
BSO.OPERATION_CODE ,
BOS.OPERATION_DESCRIPTION,
BD.DEPARTMENT_CODE,
TO_CHAR(DECODE(FU3.EMAIL_ADDRESS , NULL , FU3.DESCRIPTION , FU3.EMAIL_ADDRESS )) OPERATION_SEQ_CREATED_BY,
TO_CHAR(BOS.CREATION_DATE,'DY DD-MON-YYYY HH12:MI:SS') OPERATION_CREATION_DATE,
TO_CHAR(DECODE(FU4.EMAIL_ADDRESS , NULL , FU4.DESCRIPTION , FU4.EMAIL_ADDRESS )) OPERATION_SEQ_LAST_UPDATED_BY,
TO_CHAR(BOS.LAST_UPDATE_DATE,'DY DD-MON-YYYY HH12:MI:SS') OPERATION_SEQ_LAST_UPDATE_DATE,
TO_CHAR(DECODE(FU5.EMAIL_ADDRESS , NULL , FU5.DESCRIPTION , FU5.EMAIL_ADDRESS )) OPR_SEQ_LAST_UPDATE_LOGIN,


BOC.RESOURCE_SEQ_NUM,
BR.RESOURCE_CODE,
BOC.BASIS_TYPE,
BOC.USAGE_RATE_OR_AMOUNT,
BOC.USAGE_RATE_OR_AMOUNT_INVERSE,
TO_CHAR(DECODE(FU6.EMAIL_ADDRESS , NULL , FU6.DESCRIPTION , FU6.EMAIL_ADDRESS )) RESOURCE_CREATED_BY,
TO_CHAR(BOC.CREATION_DATE,'DY DD-MON-YYYY HH12:MI:SS') RESOURCE_CREATION_DATE,
TO_CHAR(DECODE(FU7.EMAIL_ADDRESS , NULL , FU7.DESCRIPTION , FU7.EMAIL_ADDRESS )) RESOURCE_LAST_UPDATED_BY,
TO_CHAR(BOC.LAST_UPDATE_DATE,'DY DD-MON-YYYY HH12:MI:SS') RESOURCE_LAST_UPDATE_DATE,
TO_CHAR(DECODE(FU8.EMAIL_ADDRESS , NULL , FU8.DESCRIPTION , FU8.EMAIL_ADDRESS )) RESOURCE_LAST_UPDATE_LOGIN

FROM 

APPS.MTL_SYSTEM_ITEMS MSI,
APPS.BOM_OPERATIONAL_ROUTINGS BOR,
APPS.BOM_OPERATION_SEQUENCES_V BOS,
APPS.BOM_DEPARTMENTS BD,
APPS.BOM_RESOURCES BR,
APPS.BOM_OPERATION_RESOURCES BOC,
APPS.BOM_STANDARD_OPERATIONS BSO,

APPS.FND_USER FU,
APPS.FND_USER FU1,
APPS.FND_USER FU2,
APPS.FND_LOGINS FL,

APPS.FND_USER FU3,
APPS.FND_USER FU4,
APPS.FND_USER FU5,
APPS.FND_LOGINS FL1,

APPS.FND_USER FU6,
APPS.FND_USER FU7,
APPS.FND_USER FU8,
APPS.FND_LOGINS FL2 

WHERE

MSI.ORGANIZATION_ID     ='123'
AND MSI.SEGMENT1                 = '410215' 
AND MSI.INVENTORY_ITEM_ID     = BOR.ASSEMBLY_ITEM_ID
AND MSI.ORGANIZATION_ID       = BOR.ORGANIZATION_ID
AND BOR.ROUTING_SEQUENCE_ID     = BOS.ROUTING_SEQUENCE_ID
AND BOS.DEPARTMENT_ID           = BD.DEPARTMENT_ID
AND BOR.ROUTING_SEQUENCE_ID      = BOR.COMMON_ROUTING_SEQUENCE_ID
AND BOS.OPERATION_SEQUENCE_ID    = BOC.OPERATION_SEQUENCE_ID
AND BR.RESOURCE_ID                 = BOC.RESOURCE_ID
AND BOS.STANDARD_OPERATION_ID = BSO.STANDARD_OPERATION_ID (+)

AND FU.USER_ID  = BOR.CREATED_BY
AND FU1.USER_ID = BOR.LAST_UPDATED_BY
AND FL.LOGIN_ID (+)= BOR.LAST_UPDATE_LOGIN
AND FU2.USER_ID (+)= FL.USER_ID

AND FU3.USER_ID = BOS.CREATED_BY
AND FU4.USER_ID = BOS.LAST_UPDATED_BY
AND FL1.LOGIN_ID (+)= BOS.LAST_UPDATE_LOGIN
AND FU5.USER_ID (+)= FL1.USER_ID

AND FU6.USER_ID (+)= BOC.CREATED_BY
AND FU7.USER_ID (+)= BOC.LAST_UPDATED_BY
AND FL2.LOGIN_ID (+)= BOC.LAST_UPDATE_LOGIN
AND FU8.USER_ID (+) = FL2.USER_ID
ORDER BY MSI.SEGMENT1 , BOS.OPERATION_SEQ_NUM , BOS.CREATION_DATE ,BOC.RESOURCE_SEQ_NUM

Inventory Part Numbers with Catalog Values Details Report

Inventory Part Numbers with Catalog Values Details Report : 

SELECT UNIQUE

OOD.ORGANIZATION_NAME "ORGANIZATION NAME #",

MSI.SEGMENT1 "PART NUMBER #",

MIAV.ELEMENT_SEQUENCE "ELEMENT SEQUENCE #",

MIAV.ELEMENT_NAME "ELEMENT NAME #”,

MIAV.ELEMENT_VALUE "ELEMENT VALUES #",

FU.USER_NAME "ELEMENT CREATED BY",

TO_CHAR (MIAV.CREATION_DATE,'DY DD-MON-YYYY HH:MI:SS AM') "ELEMENT CREATION DATE",

FU1.USER_NAME "ELEMENT LAST UPDATED BY",

TO_CHAR (MIAV.LAST_UPDATE_DATE,'DY DD-MON-YYYY HH:MM:SS AM') "ELEMENT LAST UPDATED DATE" ,

MSI.ITEM_CATALOG_GROUP_ID "CATALOG GROUP ID #",

MICGB.SEGMENT1||' ' || MICGB.SEGMENT2||' '||MICGB.SEGMENT3 "CATALOG GROUP NAME #"            

FROM

APPS.MTL_SYSTEM_ITEMS MSI,

APPS.MTL_DESCR_ELEMENT_VALUES MIAV,

APPS.ORG_ORGANIZATION_DEFINITIONS OOD,

APPS.FND_USER FU,

APPS.FND_USER FU1,

APPS.MTL_ITEM_CATALOG_GROUPS_B MICGB

WHERE

MSI.INVENTORY_ITEM_ID=MIAV.INVENTORY_ITEM_ID

AND MSI.ORGANIZATION_ID=121

AND MSI.ORGANIZATION_ID=OOD.ORGANIZATION_ID

AND FU.USER_ID=MIAV.CREATED_BY

AND FU1.USER_ID=MIAV.LAST_UPDATED_BY

AND MSI.ITEM_CATALOG_GROUP_ID = MICGB.ITEM_CATALOG_GROUP_ID (+)

AND MSI.SEGMENT1 IN ( ‘PART NUMBER / OR LIST OF PART NUMBERS’)

ORDER BY

MSI.ITEM_CATALOG_GROUP_ID,

MSI.SEGMENT1,

MIAV.ELEMENT_SEQUENCE

Bills of Material Details Report

Bills of Material Details Report :

SELECT UNIQUE

MSI.SEGMENT1 " BILL NAME #",

MSI.DESCRIPTION " BILL DESC #",

MSI.PRIMARY_UNIT_OF_MEASURE "PRIMARY UOM #",

MSI.ITEM_TYPE "BILL TYPE #",

TO_CHAR(BBOM.CREATION_DATE,'DY DD-MON-YYYY HH:MI:SS AM') "BILL CREATION DATE #",

FU1.USER_NAME "BILL CREATED BY #",

TO_CHAR(BBOM.LAST_UPDATE_DATE,'DY DD-MON-YYYY HH:MI:SS AM') "BILL UPDATED DATE #",

FU2.USER_NAME "BILL LAST UPDATED BY #",

BIC.ITEM_NUM "ITEM NUMBER #",
BIC.OPERATION_SEQ_NUM "OPERATION SEQ NUMBER #",

MSI1.SEGMENT1 "COMPONENT ITEM NAME #",

MSI1.ITEM_TYPE "COMP TYPE #",

MSI1.DESCRIPTION "COMPONENT ITEM DESC #",

TO_CHAR(BIC.CREATION_DATE,'DY DD-MON-YYYY HH:MI:SS AM') "COMPONENT LINE CREATION DATE #",

FU3.USER_NAME "COMPONENT LINE CREATED BY #",

TO_CHAR(BIC.LAST_UPDATE_DATE,'DY DD-MON-YYYY HH:MI:SS AM') "COMPONENT LINE UPDATED DATE #",

FU4.USER_NAME "COMPON LINE LAST UPDATED BY #",

BIC.COMPONENT_QUANTITY "COMPONENT QUANTITY#",

TO_CHAR(BIC.DISABLE_DATE,'DY DD-MON-YYYY HH:MI:SS AM') "DISABLE DATE#"

FROM

APPS.BOM_BILL_OF_MATERIALS BBOM,

APPS.MTL_SYSTEM_ITEMS_B MSI,

APPS.BOM_INVENTORY_COMPONENTS BIC,

APPS.MTL_SYSTEM_ITEMS_B MSI1,

APPS.FND_USER FU1,

APPS.FND_USER FU2,

APPS.FND_USER FU3,

APPS.FND_USER FU4

WHERE

MSI.INVENTORY_ITEM_ID = BBOM.ASSEMBLY_ITEM_ID

AND BBOM.CREATED_BY=FU1.USER_ID

AND BBOM.LAST_UPDATED_BY=FU2.USER_ID

AND BIC.CREATED_BY=FU3.USER_ID

AND BIC.LAST_UPDATED_BY=FU4.USER_ID

AND MSI.ORGANIZATION_ID = BBOM.ORGANIZATION_ID

AND MSI.ORGANIZATION_ID = 121

AND MSI1.INVENTORY_ITEM_ID = BIC.COMPONENT_ITEM_ID

AND MSI1.ORGANIZATION_ID = 121

AND BBOM.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID

AND BIC.DISABLE_DATE IS NULL

AND MSI.SEGMENT1 ='SRVS' -- Bill Name

ORDER BY

MSI.SEGMENT1,

BIC.ITEM_NUM,

BIC.OPERATION_SEQ_NUM

Inventory Part Number Report with Catalog Group Name

Inventory Part Number Report with Catalog Group Name :- 

 

SELECT  UNIQUE OOD.ORGANIZATION_NAME "ORGANIZATION NAME #", 
MSI.INVENTORY_ITEM_ID "INVENTORY_ITEM_ID #",

MSI.SEGMENT1 "PART NUMBER#",

MSI.DESCRIPTION "DESCRIPTION #",

TL.LONG_DESCRIPTION  "LONG DESCRIPTION #",

MSI.INVENTORY_ITEM_STATUS_CODE "INVENTORY ITEM STATUS CODE #",

MSI.PRIMARY_UNIT_OF_MEASURE "PRIMARY_UOM #",

MSI.ITEM_CATALOG_GROUP_ID "CATALOG GROUP ID #",

MICGB.SEGMENT1 "CATALOG GROUP NAME #",              

TO_CHAR(FU.USER_NAME) "ITEM CREATED BY #",       

TO_CHAR(MSI.CREATION_DATE,'DD-MON-YYYY') "ITEM CREATION DATE#" ,

TO_CHAR(FU1.USER_NAME) "ITEM LAST UPDATED BY #",

TO_CHAR(MSI.LAST_UPDATE_DATE,'DD-MON-YYYY ') "ITEM LAST UPDATED DATE #"

FROM
INV.MTL_SYSTEM_ITEMS_B MSI,

APPS.MTL_SYSTEM_ITEMS_TL TL,

APPS.ORG_ORGANIZATION_DEFINITIONS OOD,

APPS.FND_USER FU,

APPS.FND_USER FU1,

APPS.MTL_ITEM_CATALOG_GROUPS_B MICGB

WHERE
               OOD.ORGANIZATION_ID=MSI.ORGANIZATION_ID

AND       MSI.CREATED_BY=FU.USER_ID

AND       MSI.LAST_UPDATED_BY=FU1.USER_ID

AND       OOD.ORGANIZATION_ID = 122

AND       MSI.ITEM_CATALOG_GROUP_ID =  MICGB.ITEM_CATALOG_GROUP_ID (+)

AND       MSI.INVENTORY_ITEM_ID=TL.INVENTORY_ITEM_ID

AND       MSI.ORGANIZATION_ID=TL.ORGANIZATION_ID

AND       MSI.ORGANIZATION_ID=122

AND       MSI.SEGMENT1 = ‘PART NUMBER’

ORDER BY MSI.ITEM_CATALOG_GROUP_ID ,MSI.INVENTORY_ITEM_ID

The SQL Query to Find the On Hand Quantity

The SQL Query to Find the On Hand Quantity :-

SELECT   MOQ.ORGANIZATION_ID,

                  MSI.SEGMENT1 "ITEM NAME #",    

                  MSI.DESCRIPTION "ITEM DESC #",

                  SUM (MOQ.TRANSACTION_QUANTITY)

FROM      APPS.MTL_ONHAND_QUANTITIES MOQ,

                  INV.MTL_SYSTEM_ITEMS_B MSI

WHERE  MSI.INVENTORY_ITEM_ID   = MOQ.INVENTORY_ITEM_ID (+)

AND        MSI.ORGANIZATION_ID     = MOQ.ORGANIZATION_ID (+)

AND        MOQ.ORGANIZATION_ID    = 122

AND        MSI.SEGMENT1 IN (

'AIRSET-P',

'AIRSETMTGKIT-P')

GROUP BY MOQ.ORGANIZATION_ID, MSI.SEGMENT1 ,  MSI.DESCRIPTION

UNION

SELECT  MMT.ORGANIZATION_ID,

                 MSI.SEGMENT1 "ITEM NAME #",    

                 MSI.DESCRIPTION "ITEM DESC #",

                 SUM (MMT.TRANSACTION_QUANTITY)

FROM    APPS.MTL_MATERIAL_TRANSACTIONS MMT,

                INV.MTL_SYSTEM_ITEMS_B MSI

WHERE  MSI.INVENTORY_ITEM_ID   = MMT.INVENTORY_ITEM_ID (+)

AND       MSI.ORGANIZATION_ID     = MMT.ORGANIZATION_ID (+)

AND       MMT.ORGANIZATION_ID    = 122

AND       MSI.SEGMENT1 IN (

'AIRSET-P',

'AIRSETMTGKIT-P'

)

GROUP BY MMT.ORGANIZATION_ID, MSI.SEGMENT1 ,  MSI.DESCRIPTION