SELECT RM.YARD_ID ,EWO.UT_NBR ,CRW.CRW_CMC_CODE||CRW.CRW_YARD_ASSIGNED||CRW.CRW_WORK_FUNCTION||CRW.CRW_NBR CREW_CONCAT ,MT.MT_CODE ,RM.SERIAL_NBR ,RM.REEL_TYPE ,RM.QTY_RECD ,SUM(DM.QTY_AVAIL) DM_QTY_AVAIL ,SUM(DM.QTY_AVAIL*DM.UNIT_COST) DM_COST ,FLOOR(SYSDATE-RM.RECD_DATE) AGE ,RM.RECD_DATE ,RL.RL_REQN_NBR ,RL.RL_NBR ,DECODE(SIGN_OUT.SIGN_OUT_BY,NULL,NULL,DECODE(SIGN_OUT.TRANS_TYPE,'C',SIGN_OUT.SIGN_OUT_BY||' - '||CONT.CONTRACTOR_NAME,'T', SIGN_OUT.SIGN_OUT_BY))sign_out_by ,DECODE(SIGN_OUT.SIGN_OUT_BY,NULL,NULL,SIGN_OUT.TRANS_DATE) Trans_date , sign_out.RETURNED_QTY RETURNED_QTY ,TSK.TSK_ID , TSK.TSK_PRINT_NBR ,RL.REMARKS ,LOC.STREET_ADDRESS_1||', '||LOC.STREET_ADDRESS_2||', '||LOC.CITY||', '||LOC.STATE ADDRESS FROM DISTRIBUTED_MATERIALS DM, RECEIVED_MATERIALS RM, REQUISITION_LINES RL, TASKS TSK, CREWS CRW, MATERIAL_TYPES MT, EWO EWO, YARDS YARD ,GEOLOCS GLOC ,LOCATIONS LOC ,ACAS_CONTRACTORS CONT , MTL_SIGN_OUT SIGN_OUT WHERE RM.RM_MT_CODE = MT.MT_CODE AND RM.RM_NBR = DM.RM_NBR AND DM.TSK_NBR = TSK.TSK_NBR AND RM.RM_RL_REQN_NBR = RL.RL_REQN_NBR AND RM.RM_RL_NBR = RL.RL_NBR AND ewo.EWO_NBR = tsk.TSK_EG_EWO_NBR AND TSK.CRW_CMC_CODE = CRW.CRW_CMC_CODE AND TSK.CRW_YARD_ASSIGNED = CRW.CRW_YARD_ASSIGNED AND TSK.CRW_WORK_FUNCTION = CRW.CRW_WORK_FUNCTION AND TSK.CRW_NBR = CRW.CRW_NBR AND YARD.YARD_ID = RM.YARD_ID AND GLOC.GLOC_CODE = YARD.GLOC_CODE AND GLOC.GLOC_LENT_ID = YARD.GLOC_LENT_ID AND GLOC.GLOC_TYPE = YARD.GLOC_TYPE AND LOC.LOC_NBR = GLOC.LOC_NBR AND DM.TSK_NBR=Sign_out.TSK_NBR(+) AND DM.TSK_DET_NBR =Sign_out.TSK_DET_NBR(+) AND CONT.CONTRACTOR_ID(+) =SIGN_OUT.SIGN_OUT_BY AND DM.EWO_NBR = EWO.EWO_NBR -- ADDED FOR TUNING AND RM.YARD_ID = YARD.YARD_ID -- ADDED FOR TUNING AND RM.EWO_NBR=EWO.EWO_NBR -- ADDED FOR 193464 AND DM.QTY_AVAIL > 0 AND RM.QTY_AVAIL > 0 AND RM.RECD_DATE IS NOT NULL --rx7343, rosp, 11/23/04 AND RM.STATUS = 'R' --rx7343, rosp, 11/23/04 AND EWO.PROJ_CAT != 'BE' -- ADDED FOR 193464 AND TSK.CRW_CMC_CODE = 'MME' AND SUBSTR(MT.PRI_CODE,1,1) IN ('1','2','8') GROUP BY RM. YARD_ID ,UT_NBR ,CRW.CRW_CMC_CODE||CRW.CRW_YARD_ASSIGNED||CRW.CRW_WORK_FUNCTION||CRW.CRW_NBR ,MT.MT_CODE ,RM.SERIAL_NBR ,RM.REEL_TYPE ,RM.RECD_DATE ,RM.QTY_RECD ,RL.RL_REQN_NBR ,RL.RL_NBR ,TSK.TSK_ID , TSK.TSK_PRINT_NBR, DECODE(SIGN_OUT.SIGN_OUT_BY,NULL,NULL,DECODE(SIGN_OUT.TRANS_TYPE,'C',SIGN_OUT.SIGN_OUT_BY||' - '||CONT.CONTRACTOR_NAME,'T', SIGN_OUT.SIGN_OUT_BY)) ,DECODE(SIGN_OUT.SIGN_OUT_BY,NULL,NULL,SIGN_OUT.TRANS_DATE) , sign_out.RETURNED_QTY ,RL.REMARKS ,LOC.STREET_ADDRESS_1||', '||LOC.STREET_ADDRESS_2||', '||LOC.CITY||', '||LOC.STATE Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop SELECT STATEMENT Optimizer Mode=ALL_ROWS 1 1882 SORT GROUP BY 1 351 1882 NESTED LOOPS NESTED LOOPS 1 351 1881 NESTED LOOPS 1 308 1879 NESTED LOOPS 1 292 1878 NESTED LOOPS 1 257 1877 NESTED LOOPS 1 241 1875 NESTED LOOPS OUTER 1 226 1874 NESTED LOOPS OUTER 1 190 1873 NESTED LOOPS 1 161 1872 NESTED LOOPS 1 92 1871 NESTED LOOPS 392 23 K 1061 NESTED LOOPS 392 16 K 667 INDEX RANGE SCAN .CRW_PK 17 204 2 TABLE ACCESS BY INDEX ROWID .TASKS 23 736 39 INDEX RANGE SCAN .TSK_CRW_FK_I 1 38 TABLE ACCESS BY INDEX ROWID .EWO 1 18 1 INDEX UNIQUE SCAN .EWO_PK 1 0 TABLE ACCESS BY INDEX ROWID .DISTRIBUTED_MATERIALS 1 30 3 INDEX RANGE SCAN .DM_TSK 1 2 TABLE ACCESS BY INDEX ROWID .RECEIVED_MATERIALS 1 69 1 INDEX UNIQUE SCAN .RM_PK 1 0 TABLE ACCESS BY INDEX ROWID .MTL_SIGN_OUT 1 29 1 INDEX UNIQUE SCAN .MTL_SIGN_OUT_TSK_PK 1 0 TABLE ACCESS BY INDEX ROWID .ACAS_CONTRACTORS 1 36 1 INDEX UNIQUE SCAN .ACAS_CONTRACTOR_PK 1 0 TABLE ACCESS BY INDEX ROWID .YARDS 1 15 1 INDEX UNIQUE SCAN .YARD_PK 1 0 TABLE ACCESS BY INDEX ROWID .GEOLOCS 1 16 2 INDEX RANGE SCAN .GLOC_UK2 1 1 TABLE ACCESS BY INDEX ROWID .REQUISITION_LINES 1 35 1 INDEX UNIQUE SCAN .RL_PK 1 0 TABLE ACCESS BY INDEX ROWID .MATERIAL_TYPES 1 16 1 INDEX UNIQUE SCAN .MT_PK 1 0 INDEX UNIQUE SCAN .LOC_PK 1 1 TABLE ACCESS BY INDEX ROWID .LOCATIONS 1 43 2