|
SELECT COUNT(*) ,CASE WHEN A.DUE_DATE-D.AS_OF_DATE BETWEEN 1 AND 5 THEN 'Due In 01-05 days' WHEN A.DUE_DATE-D.AS_OF_DATE BETWEEN 6 AND 10 THEN 'Due In 06-10 days' WHEN A.DUE_DATE-D.AS_OF_DATE BETWEEN 11 AND 15 THEN 'Due In 11-15 days' WHEN A.DUE_DATE - D.AS_OF_DATE > 15 THEN 'Due In 16+ days' WHEN A.DUE_DATE - D.AS_OF_DATE < 0 THEN 'Past Due' WHEN A.DUE_DATE - D.AS_OF_DATE = 0 THEN 'Due Today' END,D.AS_OF_DATE,E.OPRID FROM PS_GLRN_RECON_TBL A , PS_GLRN_RULE_TBL B , PS_GLRN_STATUS_TBL C , PS_GLRN_RECOND_TBL D , PS_GLRN_RECDUT_TBL E , PS_GLRN_UOR_TBL F WHERE ( ( A.GLRN_RULE = B.GLRN_RULE AND B.EFFDT = ( SELECT MAX(C_ED.EFFDT) FROM PS_GLRN_RULE_TBL C_ED WHERE B.SETID = C_ED.SETID AND B.GLRN_RULE = C_ED.GLRN_RULE AND C_ED.EFFDT <= D.AS_OF_DATE AND B.EFF_STATUS = 'A') AND B.SETID = ( SELECT D.SETID FROM PS_SET_CNTRL_TBL D WHERE D.SETCNTRLVALUE = A.BUSINESS_UNIT) AND C.GLRN_RECON_STATUS = A.GLRN_RECON_STATUS AND D.GLACT_USERDSP_OPT = 'S' AND D.OPRID = E.OPRID AND C.GLRN_RECON_STATUS = E.GLRN_RECON_STATUS AND A.GLRN_UNIT_OF_RECON = F.GLRN_UNIT_OF_RECON)) GROUP BY CASE WHEN A.DUE_DATE-D.AS_OF_DATE BETWEEN 1 AND 5 THEN 'Due In 01-05 days' WHEN A.DUE_DATE-D.AS_OF_DATE BETWEEN 6 AND 10 THEN 'Due In 06-10 days' WHEN A.DUE_DATE-D.AS_OF_DATE BETWEEN 11 AND 15 THEN 'Due In 11-15 days' WHEN A.DUE_DATE - D.AS_OF_DATE > 15 THEN 'Due In 16+ days' WHEN A.DUE_DATE - D.AS_OF_DATE < 0 THEN 'Past Due' WHEN A.DUE_DATE - D.AS_OF_DATE = 0 THEN 'Due Today' END ,D.AS_OF_DATE ,E.OPRID UNION ALL SELECT COUNT(*) ,CASE WHEN A.DUE_DATE-D.AS_OF_DATE BETWEEN 1 AND 5 THEN 'Due In 01-05 days' WHEN A.DUE_DATE-D.AS_OF_DATE BETWEEN 6 AND 10 THEN 'Due In 06-10 days' WHEN A.DUE_DATE-D.AS_OF_DATE BETWEEN 11 AND 15 THEN 'Due In 11-15 days' WHEN A.DUE_DATE - D.AS_OF_DATE > 15 THEN 'Due In 16+ days' WHEN A.DUE_DATE - D.AS_OF_DATE < 0 THEN 'Past Due' WHEN A.DUE_DATE - D.AS_OF_DATE = 0 THEN 'Due Today' END,D.AS_OF_DATE , D.OPRID FROM PS_GLRN_RECON_TBL A , PS_GLRN_RULE_TBL B , PS_GLRN_STATUS_TBL C , PS_GLRN_RECOND_TBL D , PS_GLRN_UOR_TBL F WHERE ( ( A.GLRN_RULE = B.GLRN_RULE AND B.EFFDT = ( SELECT MAX(C_ED.EFFDT) FROM PS_GLRN_RULE_TBL C_ED WHERE B.SETID = C_ED.SETID AND B.GLRN_RULE = C_ED.GLRN_RULE AND C_ED.EFFDT <= D.AS_OF_DATE AND B.EFF_STATUS = 'A') AND B.SETID = ( SELECT D.SETID FROM PS_SET_CNTRL_TBL D WHERE D.SETCNTRLVALUE = A.BUSINESS_UNIT) AND C.GLRN_RECON_STATUS = A.GLRN_RECON_STATUS AND D.GLACT_USERDSP_OPT = 'A' AND A.GLRN_UNIT_OF_RECON = F.GLRN_UNIT_OF_RECON)) GROUP BY CASE WHEN A.DUE_DATE-D.AS_OF_DATE BETWEEN 1 AND 5 THEN 'Due In 01-05 days' WHEN A.DUE_DATE-D.AS_OF_DATE BETWEEN 6 AND 10 THEN 'Due In 06-10 days' WHEN A.DUE_DATE-D.AS_OF_DATE BETWEEN 11 AND 15 THEN 'Due In 11-15 days' WHEN A.DUE_DATE - D.AS_OF_DATE > 15 THEN 'Due In 16+ days' WHEN A.DUE_DATE - D.AS_OF_DATE < 0 THEN 'Past Due' WHEN A.DUE_DATE - D.AS_OF_DATE = 0 THEN 'Due Today' END,D.AS_OF_DATE, D.OPRID
|