☞ 계정별잔액장

정의 :

Report Sample

[잔액장(계정별)]
  1. 조회항목 : 전일잔액     : 연도0101 ~ 조회기간FROM -1 까지의 금액
                금일거래내역 : 조회기간FROM ~ 조회기간TO  까지의 금액 



[ 인쇄 Report Sample File ]   (오른쪽 마우스버튼을 눌러서  '다른이름으로저장' 으로 해야 됨)
     Reqube File Download   
   

sql:

--[계정별잔액장]
--인자 : 회계단위
--       시작일 ~ 종료일     : 20090315 ~ 20090520
--       년0101 ~ 시작일 -1  : 20090101 ~ 20090314
--       계정코드            : 1231500

--총합계 구하기
SELECT DECODE(GROUPING_ID(DTLACCOUNTNAME, BEFORE_AMT), 3, '합계', DTLACCOUNTNAME) DTLACCOUNTNAME
     , BEFORE_AMT
     , SUM(DR_AMT)    DR_AMT
     , SUM(CR_AMT)    CR_AMT
     , SUM(TODAY_AMT) TODAY_AMT
FROM  ( --계정별 전일잔액, 금일잔액 구하기
        SELECT '(' || B.DTLACCOUNTCD || ')' || B.DTLACCOUNTNAME                             AS DTLACCOUNTNAME  --세목명
             , DECODE(B.DRCRCD, 'TFN2510', BEFORE_DR_AMT - BEFORE_CR_AMT
                                         , BEFORE_CR_AMT - BEFORE_DR_AMT)                   AS BEFORE_AMT      --전일잔액
             , DR_AMT
             , CR_AMT
             , DECODE(B.DRCRCD, 'TFN2510', BEFORE_DR_AMT + DR_AMT - BEFORE_CR_AMT - CR_AMT
                                         , BEFORE_CR_AMT + CR_AMT - BEFORE_DR_AMT - DR_AMT) AS TODAY_AMT       --금일잔액
        FROM  ( --계정별 합계
                SELECT ACCOUNTCD
                     , DTLACCOUNTCD
                     , SUM(CASE WHEN SLIPDATE >= '20090101' AND SLIPDATE <= '20090314' AND DRCRCD = 'TFN2510' THEN AMT ELSE 0 END ) BEFORE_DR_AMT
                     , SUM(CASE WHEN SLIPDATE >= '20090101' AND SLIPDATE <= '20090314' AND DRCRCD = 'TFN2520' THEN AMT ELSE 0 END ) BEFORE_CR_AMT
                     , SUM(CASE WHEN SLIPDATE >= '20090315' AND SLIPDATE <= '20090520' AND DRCRCD = 'TFN2510' THEN AMT ELSE 0 END ) DR_AMT
                     , SUM(CASE WHEN SLIPDATE >= '20090315' AND SLIPDATE <= '20090520' AND DRCRCD = 'TFN2520' THEN AMT ELSE 0 END ) CR_AMT
                FROM ( SELECT ACCOUNTCD, DTLACCOUNTCD, SLIPDATE, DRCRCD, SUM(AMT) AMT
                       FROM   BH_SLIPSLV
                       WHERE  ACCOUNTUNITCD = 'TBD0001'
                       AND    SLIPDATE      BETWEEN '20090101' AND '20090520'
                       AND    ACCOUNTCD     = '1231500'
                       GROUP  BY ACCOUNTCD, DTLACCOUNTCD, SLIPDATE, DRCRCD
                     )
                GROUP BY ACCOUNTCD, DTLACCOUNTCD
              ) A JOIN BH_ACCOUNTS B
                      ON (    A.ACCOUNTCD     = B.ACCOUNTCD
                          AND A.DTLACCOUNTCD  = B.DTLACCOUNTCD
                          AND B.ACCOUNTUNITCD = 'TBD0001'
                         )
      )
WHERE BEFORE_AMT > 0 OR DR_AMT > 0 OR CR_AMT > 0 OR TODAY_AMT > 0
GROUP BY ROLLUP(DTLACCOUNTNAME, BEFORE_AMT)
HAVING GROUPING_ID(DTLACCOUNTNAME, BEFORE_AMT) IN (0, 3)
ORDER BY DTLACCOUNTNAME