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