|
SELECT DISTINCT A.EX_DOC_ID , A.OPRID FROM %Table(EX_APRVL_HIST) A WHERE A.EX_DOC_TYPE = 'S' AND A.ACTION_NAME = 'APR' UNION ALL SELECT DISTINCT F.SHEET_ID ,B.OPRID FROM PS_EX_SHEET_AW A , PS_EOAW_STEP_VW B , PS_EX_TRANS_PROP C , PS_EX_APPRVR_TYPE D , PS_EOAW_STAGE E , PS_EX_SHEET_HDR F WHERE A.EOAWDEFN_ID = B.EOAWDEFN_ID AND B.EOAWDEFN_ID = C.SETID AND B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_EOAW_STEP_VW B1 WHERE B1.EOAWPRCS_ID = B.EOAWPRCS_ID AND B1.EOAWDEFN_ID = B.EOAWDEFN_ID AND B1.EFFDT <= %CurrentDateIn AND B1.EOAWSTAGE_NBR = B.EOAWSTAGE_NBR AND B1.EOAWPATH_ID = B.EOAWPATH_ID AND B1.EOAWSTEP_NBR = B.EOAWSTEP_NBR AND B1.EOAWTHREAD_ID = B.EOAWTHREAD_ID AND B1.EOAWSTEP_INSTANCE = B.EOAWSTEP_INSTANCE AND B1.EOAWUSTEP_INST_ID = B.EOAWUSTEP_INST_ID ) AND B.EOAWDEFN_ID = D.SETID AND A.EOAWTHREAD_STATUS IN ('P' , 'E') AND A.EOAWTHREAD_ID = B.EOAWTHREAD_ID AND A.EOAWPRCS_ID = B.EOAWPRCS_ID AND B.EOAWSTEP_STATUS IN ('P','H') AND B.EOAWDEFN_ID = E.EOAWDEFN_ID AND B.EOAWPRCS_ID = E.EOAWPRCS_ID AND B.EOAWSTAGE_NBR = E.EOAWSTAGE_NBR AND B.EFFDT = E.EFFDT AND E.EOAWPRCS_ID = C.EOAWPRCS_ID AND E.SEQ_NBR = C.STAGE_ID AND C.APPR_TRANS_TYPE = 'ER' AND C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_EX_TRANS_PROP C1 WHERE C.SETID = C1.SETID AND C.APPR_TRANS_TYPE = C1.APPR_TRANS_TYPE AND C1.EFFDT <= %CurrentDateIn AND C.EOAWPRCS_ID = C1.EOAWPRCS_ID AND C.STAGE_ID = C1.STAGE_ID ) AND C.APPRVR_TYPE = D.APPRVR_TYPE AND A.SHEET_ID = F.SHEET_ID AND F.APPR_PROCESS_FLAG = 'N' AND F.PROCESS_FLAG = 'N'
|