|
sql:
SELECT DECODE(COUNT(ACCOUNTCD),'','0',COUNT(ACCOUNTCD)) AS CNT --건수
,ACCOUNTUNITCD --계정
,SLIPNUM
,SLIPSEQ
,CUSTOMERNAME
,DETAILREMARK
,DECODE(SUBSTR(ACCOUNTCD, 0, 1), '1','자산','2','부채','3','자본','4','수익','5','비용') AS TYP --구분
,ACCOUNTCD AS ACCOUNTCD --계정
,DTLACCOUNTCD AS DTLACCOUNTCD
,ACCOUNTCD ||' '|| DTLACCOUNTCD AS ACCDTLCD
,ACCOUNTNAME AS ACCOUNTNAME --계정과목명
,ACCOUNTNAME||' '||(SELECT V.DTLACCOUNTNAME
FROM BH_ACCOUNTS V
WHERE V.ACCOUNTUNITCD = '$[ACCOUNTUNITCD]'
AND V.ACCOUNTCD = C.ACCOUNTCD
AND V.DTLACCOUNTCD = C.DTLACCOUNTCD
AND V.DTLACCOUNTNAME IS NOT NULL) AS SUMTITL
,DECODE(SUM(CR),'','0',SUM(CR)) AS SUMCR --차변금액
,DECODE(SUM(DR),'','0',SUM(DR)) AS SUMDR --대변금액
,'0' AS CASH1
,'0' AS CASH2
FROM (SELECT ACCOUNTUNITCD,
SLIPNUM,
SLIPSEQ,
CUSTOMERNAME,
DETAILREMARK,
ACCOUNTCD,
DTLACCOUNTCD,
BH_ACCOUNTS_FU1(ACCOUNTUNITCD, ACCOUNTCD) AS ACCOUNTNAME,
DECODE(DRCRCD, 'TFN2510', SUM(AMT)) AS CR,
DECODE('', '', '') AS DR
FROM BH_SLIPSLV
WHERE SLIPDATE = '$[SLIPDATE]'
AND ACCOUNTUNITCD = '$[ACCOUNTUNITCD]'
GROUP BY ACCOUNTUNITCD, SLIPNUM, SLIPSEQ, CUSTOMERNAME, DETAILREMARK, ACCOUNTCD, DRCRCD, DTLACCOUNTCD
UNION ALL
SELECT ACCOUNTUNITCD,
SLIPNUM,
SLIPSEQ,
CUSTOMERNAME,
DETAILREMARK,
ACCOUNTCD,
DTLACCOUNTCD,
BH_ACCOUNTS_FU1(ACCOUNTUNITCD, ACCOUNTCD) AS ACCOUNTNAME,
DECODE('', '', '') AS CR,
DECODE(DRCRCD, 'TFN2520', SUM(AMT)) AS DR
FROM BH_SLIPSLV
WHERE SLIPDATE = '$[SLIPDATE]'
AND ACCOUNTUNITCD = '$[ACCOUNTUNITCD]'
GROUP BY ACCOUNTUNITCD, SLIPNUM, SLIPSEQ, CUSTOMERNAME, DETAILREMARK, ACCOUNTCD, DRCRCD, DTLACCOUNTCD) C
GROUP BY ACCOUNTUNITCD, SLIPNUM, SLIPSEQ, CUSTOMERNAME, DETAILREMARK, ACCOUNTCD, ACCOUNTNAME, DTLACCOUNTCD
ORDER BY SLIPNUM ASC, SLIPSEQ ASC, TYP ASC
|
