|
SELECT A.BUSINESS_UNIT , A.WO_ID , A.WO_TASK_ID , A.WM_WO_TASK_ID , A.WM_BU_WO_TSK_ID , A.WO_TYPE , A.WO_TYPE_DESCR , A.SRVC_GRP_ID , A.WM_PM , A.SHOP_ID , A.SHOP_DESCR , A.WM_PRIORITY_DESCR , A.TASK_DESCR , A.MAINT_TYPE , A.STATUS_DESCR , A.BUSINESS_UNIT_AM , A.ASSET_ID , A.TAG_NUMBER , A.SERIAL_ID , A.ASSET_DESCR30 , A.REQ_START_DT , A.REQ_END_DT , A.SCHED_START_DT , A.SCHED_END_DT , A.ASSET_TYPE , A.ASSET_SUBTYPE , A.WM_SUBTYPE_DESCR , A.MANUFACTURER , A.MODEL , A.PROBLEM_DESCR , A.CRITICALITY_DESCR , CASE WHEN B.LAST_UPDATE_DTTM > A.LAST_UPDATE_DTTM THEN B.LAST_UPDATE_DTTM ELSE A.LAST_UPDATE_DTTM END , A.WORK_LOC_DESCR , A.LOCATION_DESCR , A.AREA_DESCR , A.ADDRESS1 , CASE WHEN B.RES_LN_NBR IS NULL THEN 0 ELSE B.RES_LN_NBR END , '1' , '1' , CASE WHEN P.NAME IS NULL THEN 'Unassigned' ELSE P.NAME END , CASE WHEN P.NAME IS NULL THEN 'Unassigned' ELSE P.NAME END , CFT.CRAFT_DESCRIPTION , B.ACTL_START_DT , B.ACTL_END_DT , B.SCHED_DURATION , B.ACTL_DURATION , ' ' , 0 , 0 , B.SCHED_COST_LBR , B.ACTL_COST_LBR , 0 , B.CURRENCY_CD , A.COMPLTED_DT , A.WM_COMPLT_ON_TIME , CASE WHEN B.ACTL_DURATION <> 0 THEN B.SCHED_DURATION / B.ACTL_DURATION * 100 ELSE 0 END FROM PS_WM_WO_KIBANA_VW A , ((PS_WM_WO_SCHED_LBR B LEFT OUTER JOIN PS_PERSONAL_DATA P ON P.EMPLID = B.EMPLID) LEFT OUTER JOIN PS_WM_EIP_CFT_VW CFT ON CFT.BUSINESS_UNIT = B.BUSINESS_UNIT AND CFT.CRAFT_ID = B.CRAFT_ID) WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.WO_ID = B.WO_ID AND A.WO_TASK_ID = B.WO_TASK_ID UNION SELECT C.BUSINESS_UNIT , C.WO_ID , C.WO_TASK_ID , C.WM_WO_TASK_ID , C.WM_BU_WO_TSK_ID , C.WO_TYPE , C.WO_TYPE_DESCR , C.SRVC_GRP_ID , C.WM_PM , C.SHOP_ID , C.SHOP_DESCR , C.WM_PRIORITY_DESCR , C.TASK_DESCR , C.MAINT_TYPE , C.STATUS_DESCR , C.BUSINESS_UNIT_AM , C.ASSET_ID , C.TAG_NUMBER , C.SERIAL_ID , C.ASSET_DESCR30 , C.REQ_START_DT , C.REQ_END_DT , C.SCHED_START_DT , C.SCHED_END_DT , C.ASSET_TYPE , C.ASSET_SUBTYPE , C.WM_SUBTYPE_DESCR , C.MANUFACTURER , C.MODEL , C.PROBLEM_DESCR , C.CRITICALITY_DESCR , CASE WHEN D.LAST_UPDATE_DTTM > C.LAST_UPDATE_DTTM THEN D.LAST_UPDATE_DTTM ELSE C.LAST_UPDATE_DTTM END , C.WORK_LOC_DESCR , C.LOCATION_DESCR , C.AREA_DESCR , C.ADDRESS1 , CASE WHEN D.RES_LN_NBR IS NULL THEN 0 ELSE D.RES_LN_NBR END , '2' , '2' , I.DESCR , '' , '' , %DateNull , %DateNull , 0 , 0 , D.INV_ITEM_ID , D.QTY_SCHEDULED , D.USED_QTY , D.SCHED_COST_MAT , D.ACTL_COST_MAT , 0 , D.CURRENCY_CD , C.COMPLTED_DT , C.WM_COMPLT_ON_TIME , 0 FROM PS_WM_WO_KIBANA_VW C , PS_WM_WO_SCHED_MAT D , PS_WM_INV_ITM_VW I WHERE ( D.BUSINESS_UNIT = C.BUSINESS_UNIT AND D.WO_ID = C.WO_ID AND D.WO_TASK_ID = C.WO_TASK_ID AND D.BUSINESS_UNIT = I.BUSINESS_UNIT AND I.INV_ITEM_ID = D.INV_ITEM_ID) UNION SELECT E.BUSINESS_UNIT , E.WO_ID , E.WO_TASK_ID , E.WM_WO_TASK_ID , E.WM_BU_WO_TSK_ID , E.WO_TYPE , E.WO_TYPE_DESCR , E.SRVC_GRP_ID , E.WM_PM , E.SHOP_ID , E.SHOP_DESCR , E.WM_PRIORITY_DESCR , E.TASK_DESCR , E.MAINT_TYPE , E.STATUS_DESCR , E.BUSINESS_UNIT_AM , E.ASSET_ID , E.TAG_NUMBER , E.SERIAL_ID , E.ASSET_DESCR30 , E.REQ_START_DT , E.REQ_END_DT , E.SCHED_START_DT , E.SCHED_END_DT , E.ASSET_TYPE , E.ASSET_SUBTYPE , E.WM_SUBTYPE_DESCR , E.MANUFACTURER , E.MODEL , E.PROBLEM_DESCR , E.CRITICALITY_DESCR , CASE WHEN F.LAST_UPDATE_DTTM > E.LAST_UPDATE_DTTM THEN F.LAST_UPDATE_DTTM ELSE E.LAST_UPDATE_DTTM END , E.WORK_LOC_DESCR , E.LOCATION_DESCR , E.AREA_DESCR , E.ADDRESS1 , CASE WHEN F.RES_LN_NBR IS NULL THEN 0 ELSE F.RES_LN_NBR END , CASE WHEN F.ON_HAND_STOCK = 'Y' THEN '5' ELSE '3' END , CASE WHEN F.RSRC_TYPE = '1' THEN '6' WHEN F.ON_HAND_STOCK = 'Y' THEN '5' ELSE '3' END , F.DESCR254_MIXED , '' , '' , %DateNull , %DateNull , 0 , 0 , F.INV_ITEM_ID , F.QTY_REQUIRED , F.USED_QTY , F.SCHED_COST_PO , CASE WHEN F.ON_HAND_STOCK = 'Y' THEN (F.USED_QTY * F.EST_UNIT_COST) ELSE F.ACTL_COST_PO END , 0 , F.CURRENCY_CD , E.COMPLTED_DT , E.WM_COMPLT_ON_TIME , 0 FROM PS_WM_WO_KIBANA_VW E , PS_WM_WO_SCHED_PO F WHERE E.BUSINESS_UNIT = F.BUSINESS_UNIT AND E.WO_ID = F.WO_ID AND E.WO_TASK_ID = F.WO_TASK_ID /* AND F.RSRC_TYPE = '3' RSRC_TYPE = '1' - Labor purchases are excluded */ UNION SELECT G.BUSINESS_UNIT , G.WO_ID , G.WO_TASK_ID , G.WM_WO_TASK_ID , G.WM_BU_WO_TSK_ID , G.WO_TYPE , G.WO_TYPE_DESCR , G.SRVC_GRP_ID , G.WM_PM , G.SHOP_ID , G.SHOP_DESCR , G.WM_PRIORITY_DESCR , G.TASK_DESCR , G.MAINT_TYPE , G.STATUS_DESCR , G.BUSINESS_UNIT_AM , G.ASSET_ID , G.TAG_NUMBER , G.SERIAL_ID , G.ASSET_DESCR30 , G.REQ_START_DT , G.REQ_END_DT , G.SCHED_START_DT , G.SCHED_END_DT , G.ASSET_TYPE , G.ASSET_SUBTYPE , G.WM_SUBTYPE_DESCR , G.MANUFACTURER , G.MODEL , G.PROBLEM_DESCR , G.CRITICALITY_DESCR , CASE WHEN T.LAST_UPDATE_DTTM > G.LAST_UPDATE_DTTM THEN T.LAST_UPDATE_DTTM ELSE G.LAST_UPDATE_DTTM END , G.WORK_LOC_DESCR , G.LOCATION_DESCR , G.AREA_DESCR , G.ADDRESS1 , CASE WHEN T.RES_LN_NBR IS NULL THEN 0 ELSE T.RES_LN_NBR END , '4' , '4' , 'Tool' , '' , '' , %DateNull , %DateNull , 0 , 0 , '' , T.SCHED_USAGE , T.ACTL_USAGE , T.SCHED_COST_TLS , T.ACTL_COST_TLS , 0 , T.CURRENCY_CD , G.COMPLTED_DT , G.WM_COMPLT_ON_TIME , 0 FROM PS_WM_WO_KIBANA_VW G , PS_WM_WO_SCHED_TLS T WHERE T.BUSINESS_UNIT = G.BUSINESS_UNIT AND T.WO_ID = G.WO_ID AND T.WO_TASK_ID = G.WO_TASK_ID
|
BUSINESS_UNIT