|
SELECT D.OPRID , A.BUSINESS_UNIT , A.WORK_ORDER_ID FROM PS_SPF_WORDERREC A , PS_SPF_WO_PAYRATE B ,PS_BUS_UNIT_TBL_SP C , PSOPRDEFN D WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.WORK_ORDER_ID = B.WORK_ORDER_ID AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.TEAM_ID =' ' AND (A.SERVICE_METHOD = 'M' AND A.SP_WO_TYPE = 'O' AND(((WO_STATUS ='A' OR WO_STATUS ='L' OR WO_STATUS ='O' OR WO_STATUS ='S' ) AND C.SPB_II_SRC ='Y') OR (WO_STATUS ='A' OR WO_STATUS ='L') AND C.SPB_II_SRC ='N')) OR (A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.WORK_ORDER_ID = B.WORK_ORDER_ID AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND (A.SERVICE_METHOD = 'M' AND A.SP_WO_TYPE = 'E' AND(((WO_STATUS ='A' OR WO_STATUS ='L' OR WO_STATUS ='O' OR WO_STATUS ='S' ) AND C.SPB_II_SRC ='Y') OR (WO_STATUS ='A' OR WO_STATUS ='L') AND C.SPB_II_SRC ='N')) AND 1 >= ( SELECT COUNT(*) FROM PS_SPF_WO_SERVICE1 WHERE BUSINESS_UNIT = A.BUSINESS_UNIT AND WORK_ORDER_ID= A.WORK_ORDER_ID AND LINKED_SRVC_SEQNBR <= 0)) OR (A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.WORK_ORDER_ID = B.WORK_ORDER_ID AND (A.SERVICE_METHOD = 'R' AND A.SP_REP_ACT_START <> 'N') AND (A.WO_STATUS <> 'C' OR A.WO_STATUS <> 'N' OR A.WO_STATUS <> 'T')) AND B.EFFDT <= %CurrentDateIn AND B.EFF_STATUS ='A' AND A.RECRUITER = ( SELECT PERSON_ID FROM PSOPRALIAS WHERE OPRALIASTYPE ='SPP' AND OPRID = D.OPRID)
|
OPRID