|
SELECT O.OPRID , 'A:' %Concat BU.BUSINESS_UNIT FROM PS_BUS_UNIT_TBL_WM BU , PS_OPR_DEF_TBL_WM O WHERE BU.WM_WO_ACCESS = 'N' OR O.APPLY_WOA_FLG = 'N' UNION ALL SELECT DISTINCT O.OPRID , 'S:' %Concat A.BUSINESS_UNIT %Concat '-' %Concat A.SHOP_ID FROM PS_WM_WO_ACCESS A , PS_BUS_UNIT_TBL_WM BU , PSOPRDEFN O WHERE BU.WM_WO_ACCESS IN ('S','B') AND A.BUSINESS_UNIT = BU.BUSINESS_UNIT AND O.EMPLID IN ( SELECT DISTINCT EMPLID FROM PS_WM_SHOP_RES_VW2 WHERE BUSINESS_UNIT = A.BUSINESS_UNIT AND SHOP_ID = A.SHOP_ID) UNION ALL SELECT DISTINCT O.OPRID , 'P:' %Concat WOAPC.BUSINESS_UNIT_PC %Concat '-' %Concat WOAPC.POOL_ID FROM PS_BUS_UNIT_TBL_WM WOABU , PS_WM_WO_TSK WOAT , PS_OPR_DEF_TBL_WM O , PS_PGM_ENFPOOL_ACT WOAPC , PSOPRDEFN WHERE WOABU.WM_WO_ACCESS IN ('R','B') AND WOAT.BUSINESS_UNIT = WOABU.BUSINESS_UNIT AND PSOPRDEFN.OPRID = O.OPRID AND WOAPC.EMPLID = PSOPRDEFN.EMPLID AND WOAPC.BUSINESS_UNIT_PC = WOAT.BUSINESS_UNIT_PC AND WOAPC.PROJECT_ID = WOAT.PROJECT_ID AND WOAPC.ACTIVITY_ID = WOAT.ACTIVITY_ID AND WOAPC.START_DATE <= %CurrentDateIn AND (WOAPC.END_DATE IS NULL OR WOAPC.END_DATE >= %CurrentDateIn)
|
OPRID