[QUERY] 평가내역확인

SELECT 'N'            AS S_FLAG 
       ,'0'           AS CHK 
       ,A.SBJ_NO      AS SBJ_NMO 
       ,A.SUBJ_NM     AS SUBJ_NM 
       ,A.PRPS_EMP_NO AS EMP_NO 
       ,B.ESTI_CODE   AS ESTI_CODE 
       ,B.OK_DT       AS OK_DT 
       ,B.AVG_SCR     AS AVG_SCR 
       ,(SELECT COUNT(1) 
         FROM   AGA580TB 
         WHERE  ESTI_CODE = B.ESTI_CODE) AS CNT580 
       ,(SELECT COUNT(1) 
         FROM   AGA580TB 
         WHERE  SBJ_NO = A.SBJ_NO 
                AND ESTI_CLO_FL = 1 
                AND ESTI_CODE = B.ESTI_CODE) AS ESTI_CLO_FL_CNT 
       ,B.OK_FL       AS OK_FL 
FROM   AGA540TB A 
       ,AGA570TB B 
       ,APS010TB MST 
       ,AGA505TB C 
WHERE  A.SBJ_NO = B.SBJ_NO (+) 
       AND A.PRPS_EMP_NO = MST.EMP_NO 
       AND (B.OK_FL IS NULL 
             OR B.OK_FL <> '1') 
       AND :as_DT BETWEEN SUBSTR(C.MNG_DT_FR,0,6) AND SUBSTR(C.MNG_DT_TO,1,6) 
       AND ((A.PRPSPAP_SUB_DT BETWEEN MNG_DT_FR AND C.MNG_DT_TO) 
             OR (A.PAPER_SUB_DT BETWEEN MNG_DT_FR AND C.MNG_DT_TO) 
             OR (A.PLS_SUB_DT BETWEEN MNG_DT_FR AND C.MNG_DT_TO))