|
SELECT COUNT(A.GLRN_RECON_STATUS) ,E.SHORT_DESCR ,B.USERID ,F.AS_OF_DATE FROM PS_GLRN_RECON_TBL A , PS_GLRN_RECON_ASGN B , PS_GLRN_ATTRIB_TBL C , PS_GLRN_STATUS_TBL E , PS_GLRN_RECONS_TBL F WHERE ( A.GLRN_SET = B.GLRN_SET AND A.GLRN_RULE = B.GLRN_RULE AND A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.AS_OF_DATE = B.AS_OF_DATE AND A.SEQUENCE_NBR_9 = B.SEQUENCE_NBR_9 AND A.GLRN_RULE = C.GLRN_RULE AND C.EFFDT = ( SELECT MAX(C_ED.EFFDT) FROM PS_GLRN_ATTRIB_TBL C_ED WHERE C.SETID = C_ED.SETID AND C.GLRN_RULE = C_ED.GLRN_RULE AND C_ED.EFFDT <= %CurrentDateIn ) AND C.SETID = ( SELECT D.SETID FROM PS_SET_CNTRL_TBL D WHERE D.SETCNTRLVALUE = A.BUSINESS_UNIT ) AND E.GLRN_RECON_STATUS = A.GLRN_RECON_STATUS AND B.USERID = F.OPRID AND F.GLACT_USERDSP_OPT = 'A' AND A.AS_OF_DATE <= F.AS_OF_DATE ) GROUP BY E.SHORT_DESCR ,B.USERID,F.AS_OF_DATE UNION SELECT COUNT(A.GLRN_RECON_STATUS) ,E.SHORT_DESCR ,B.USERID ,F.AS_OF_DATE FROM PS_GLRN_RECON_TBL A , PS_GLRN_RECON_ASGN B , PS_GLRN_ATTRIB_TBL C , PS_GLRN_STATUS_TBL E , PS_GLRN_RECONS_TBL F , PS_GLRN_RECSTA_TBL G WHERE ( A.GLRN_SET = B.GLRN_SET AND A.GLRN_RULE = B.GLRN_RULE AND A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.AS_OF_DATE = B.AS_OF_DATE AND A.SEQUENCE_NBR_9 = B.SEQUENCE_NBR_9 AND A.GLRN_RULE = C.GLRN_RULE AND C.EFFDT = ( SELECT MAX(C_ED.EFFDT) FROM PS_GLRN_ATTRIB_TBL C_ED WHERE C.SETID = C_ED.SETID AND C.GLRN_RULE = C_ED.GLRN_RULE AND C_ED.EFFDT <= %CurrentDateIn ) AND C.SETID = ( SELECT D.SETID FROM PS_SET_CNTRL_TBL D WHERE D.SETCNTRLVALUE = A.BUSINESS_UNIT ) AND E.GLRN_RECON_STATUS = A.GLRN_RECON_STATUS AND B.USERID = F.OPRID AND A.AS_OF_DATE <= F.AS_OF_DATE AND F.OPRID = G.OPRID AND E.GLRN_RECON_STATUS = G.GLRN_RECON_STATUS AND F.GLACT_USERDSP_OPT = 'S') GROUP BY E.SHORT_DESCR,B.USERID,F.AS_OF_DATE
|