/* exec USP_ACBC110_BY_SELECT @LANGKIND=N'CHS',@CO_CD=N'9000',@FS_CD=N'001',@FS_TY=N'40',@DIV_CDS=N'1000|', @FRTO_YM_FR=N'200812',@FRTO_YM_TO=N'200812',@FILL_YN=N'0',@FR_DT=N'20080101',@GISU=1 */ --CREATE PROCEDURE [dbo].[USP_ACBC110_BY_SELECT] ALTER PROCEDURE [dbo].[USP_ACBC110_BY_SELECT] ( @LANGKIND NVARCHAR(3), -- 언어종류( 필수, KOR, CHS, ENG, JPN 등) @CO_CD NVARCHAR(4), -- 회사코드( 필수 ) @FS_CD NVARCHAR(6), -- 서식코드( 필수 ) @FS_TY NVARCHAR(4), -- 양식구분( 필수 ) @DIV_CDS NTEXT, -- 회계단위 @FRTO_YM_FR NVARCHAR(6), -- 기간 FROM @FRTO_YM_TO NVARCHAR(6), -- 기간 TO @FILL_YN NVARCHAR(2), -- 기장구분 @FR_DT NVARCHAR(8), -- 초일 @GISU NUMERIC(3,0) -- 기수 ) WITH ENCRYPTION AS /*************************************************************************/ --설 명 : 현금흐름표(중국) - 조회 ( 항목 집계 및 추가 계산 로직이 포함되지 않음. - 프로그램에서 처리 ) --수 정 자 : 김철희 --수정일자 : 2008/07/04 --수정내역 : 2008/07/04 : 신규 -- 김철희 : 2011/08/26 : 저장데이터 중복 조회 현상 수정 /*************************************************************************/ BEGIN SET NOCOUNT ON DECLARE @ACCT_FG NVARCHAR(8) SELECT @ACCT_FG = ACCT_FG FROM SCO WHERE CO_CD = @CO_CD DECLARE @NODATA_DIVS TABLE ( DIV_CD NVARCHAR(4) COLLATE database_default ) INSERT INTO @NODATA_DIVS ( DIV_CD ) SELECT D.STR_PARAMETER FROM DBO.UFN_MULTI_PARAMETERS( @DIV_CDS ) D WHERE D.STR_PARAMETER NOT IN ( SELECT DIV_CD FROM ACFLOW WHERE CO_CD = @CO_CD AND DIV_CD IN ( SELECT STR_PARAMETER FROM DBO.UFN_MULTI_PARAMETERS( @DIV_CDS ) ) AND GISU = @GISU AND FRTO_YM = ( @FRTO_YM_FR + @FRTO_YM_TO ) AND ( ISNULL( PRE_AM, 0 ) <> 0 OR ISNULL( AJUST_AM, 0 ) <> 0 ) ) SELECT D.LINE_NO, D.DISP_NM, D.DISP_NMK, D.DISP_NO, D.PRE_AM, D.AJUST_AM, D.TOTAL_AM, D.CALC_TY, D.DATA_FG FROM ( SELECT H.LINE_NO AS LINE_NO, H.DISP_NM AS DISP_NM, H.DISP_NMK AS DISP_NMK, H.DISP_NO AS DISP_NO, SUM( ISNULL( F.PRE_AM, 0 ) ) AS PRE_AM, SUM( ISNULL( F.AJUST_AM, 0 ) ) AS AJUST_AM, CAST( 0 AS NUMERIC(17,4) ) AS TOTAL_AM, H.CALC_TY AS CALC_TY, 'D' AS DATA_FG FROM AFSFRG_I H LEFT OUTER JOIN ( -- 저장 데이터 SELECT F.CF_CD, F.PRE_AM, F.AJUST_AM FROM ACFLOW F WHERE F.CO_CD = @CO_CD AND F.DIV_CD IN ( SELECT STR_PARAMETER FROM DBO.UFN_MULTI_PARAMETERS( @DIV_CDS ) ) AND F.GISU = @GISU AND F.FRTO_YM = ( @FRTO_YM_FR + @FRTO_YM_TO ) -- 직접법 UNION ALL SELECT H.LINE_NO, CASE H.CALC_TY WHEN '3' THEN CASE WHEN A.DRCR_FG IN ( '1', '3' ) THEN ISNULL( A.ACCT_AM, 0 ) ELSE 0 END WHEN '4' THEN CASE WHEN A.DRCR_FG IN ( '2', '4' ) THEN ISNULL( A.ACCT_AM, 0 ) ELSE 0 END WHEN '5' THEN CASE WHEN A.DRCR_FG IN ( '1', '3' ) THEN ISNULL( A.ACCT_AM, 0 ) WHEN A.DRCR_FG IN ( '2', '4' ) THEN ISNULL( A.ACCT_AM, 0 ) * -1.0 ELSE 0 END ELSE 0 END AS PRE_AM, 0 AS AJUST_AM FROM AFSFRG_I H LEFT OUTER JOIN ADOCUD A ON H.CO_CD = A.CO_CD AND H.LINE_NO = A.CT_USER1 AND A.USER1_TY = 'L9' AND A.DIV_CD IN ( SELECT DIV_CD FROM @NODATA_DIVS ) AND ( ( @FILL_YN = '1' AND LEFT( A.FILL_DT, 6 ) BETWEEN @FRTO_YM_FR AND @FRTO_YM_TO AND A.FILL_DT >= @FR_DT ) OR ( @FILL_YN = '0' AND LEFT( A.ISU_DT, 6 ) BETWEEN @FRTO_YM_FR AND @FRTO_YM_TO AND A.ISU_DT >= @FR_DT ) ) WHERE H.CO_CD = @CO_CD AND H.FS_CD = @FS_CD AND ( H.FS_TY = @FS_TY AND H.FS_TY = '40' ) -- 간접법 : 순이익, 전표기초 제외 -- 以下内容是发生额 7-损益类 3-借方发生 4-贷方发生 5-借贷差额(아래 내용은 발생액 7-손익류 3- 차변발생 4-대변발생 5-차대차액) UNION ALL SELECT H.LINE_NO, CASE H.CALC_TY WHEN '1' THEN CASE WHEN SA.DRCR_FG IN ( '1', '3' ) AND A.DRCR_FG IN ( '1', '3' ) THEN ISNULL( A.ACCT_AM, 0 ) WHEN SA.DRCR_FG IN ( '2', '4' ) AND A.DRCR_FG IN ( '2', '4' ) THEN ISNULL( A.ACCT_AM, 0 ) * -1.0 ELSE 0 END WHEN '7' THEN CASE WHEN A.DRCR_FG IN ( '1', '3' ) AND SA.DRCR_FG IN ( '1', '3') THEN ISNULL( A.ACCT_AM, 0 ) WHEN A.DRCR_FG IN ( '2', '4' ) AND SA.DRCR_FG IN ( '2', '4') THEN ISNULL( A.ACCT_AM, 0 ) ELSE 0 END WHEN '3' THEN CASE WHEN A.DRCR_FG IN ( '1', '3' ) THEN ISNULL( A.ACCT_AM, 0 ) ELSE 0 END WHEN '4' THEN CASE WHEN A.DRCR_FG IN ( '2', '4' ) THEN ISNULL( A.ACCT_AM, 0 ) ELSE 0 END WHEN '5' THEN CASE WHEN A.DRCR_FG IN ( '1', '3' ) THEN ISNULL( A.ACCT_AM, 0 ) WHEN A.DRCR_FG IN ( '2', '4' ) THEN ISNULL( A.ACCT_AM, 0 ) * -1.0 ELSE 0 END --WHEN '9' THEN CASE WHEN A.DRCR_FG IN ( '1', '3' ) THEN ISNULL( A.ACCT_AM, 0 ) -- WHEN A.DRCR_FG IN ( '2', '4' ) THEN ISNULL( A.ACCT_AM, 0 ) -- ELSE 0 END ELSE 0 END * CASE D.ID_FG WHEN '1' THEN -1.0 ELSE 1 END AS PRE_AM, 0 AS AJUST_AM FROM AFSFRG_I H LEFT OUTER JOIN AFSFRG_I_D D ON H.CO_CD = D.CO_CD AND H.FS_CD = D.FS_CD AND H.FS_TY = D.FS_TY AND H.LINE_NO = D.LINE_NO AND D.ACCT_TY = @ACCT_FG LEFT OUTER JOIN ADOCUD A ON D.CO_CD = A.CO_CD AND D.ACCT_CD = LEFT(A.ACCT_CD,LEN(D.ACCT_CD)) AND A.DIV_CD IN ( SELECT DIV_CD FROM @NODATA_DIVS ) AND ( ( @FILL_YN = '1' AND LEFT( A.FILL_DT, 6 ) BETWEEN @FRTO_YM_FR AND @FRTO_YM_TO AND A.FILL_DT >= @FR_DT ) OR ( @FILL_YN = '0' AND LEFT( A.ISU_DT, 6 ) BETWEEN @FRTO_YM_FR AND @FRTO_YM_TO AND A.ISU_DT >= @FR_DT ) ) LEFT OUTER JOIN SACCT SA ON A.CO_CD = SA.CO_CD AND A.ACCT_CD = SA.ACCT_CD --LEFT(A.ACCT_CD,LEN(D.ACCT_CD)) = SA.ACCT_CD -- LEFT(SA.ACCT_CD,LEN(D.ACCT_CD)) WHERE H.CO_CD = @CO_CD AND H.FS_CD = @FS_CD AND ( H.FS_TY = @FS_TY AND H.FS_TY IN ('45','40') ) -- 간접법 : 순이익 UNION ALL SELECT H.LINE_NO, CASE WHEN ISNULL( SA.ACCT_CD, '' ) = '' THEN 0 WHEN SA.DRCR_FG IN ( '1', '3' ) AND A.DRCR_FG IN ( '1', '3' ) THEN ISNULL( A.ACCT_AM, 0 ) * -1.0 WHEN SA.DRCR_FG IN ( '2', '4' ) AND A.DRCR_FG IN ( '2', '4' ) THEN ISNULL( A.ACCT_AM, 0 ) ELSE 0 END AS PRE_AM, 0 AS AJUST_AM FROM AFSFRG_I H LEFT OUTER JOIN ADOCUD A ON H.CO_CD = A.CO_CD AND A.DIV_CD IN ( SELECT DIV_CD FROM @NODATA_DIVS ) AND ( ( @FILL_YN = '1' AND LEFT( A.FILL_DT, 6 ) BETWEEN @FRTO_YM_FR AND @FRTO_YM_TO AND A.FILL_DT >= @FR_DT ) OR ( @FILL_YN = '0' AND LEFT( A.ISU_DT, 6 ) BETWEEN @FRTO_YM_FR AND @FRTO_YM_TO AND A.ISU_DT >= @FR_DT ) ) LEFT OUTER JOIN SACCT SA ON A.CO_CD = SA.CO_CD AND A.ACCT_CD = SA.ACCT_CD AND ( ( @ACCT_FG = '0000' AND SA.GROUP_CD = '5000' ) OR ( @ACCT_FG = '0001' AND SA.GROUP_CD = '6000' ) ) WHERE H.CO_CD = @CO_CD AND H.FS_CD = @FS_CD AND ( H.FS_TY = @FS_TY AND H.FS_TY = '45' ) AND H.CALC_TY = '6' -- 간접법 : 전표기초 - APREV UNION ALL ------------------------------------------------期初余额초기잔액-------------------------------------------------------------------------------- SELECT A.LINE_NO,SUM(ISNULL(PRE_AM,0)),SUM(ISNULL(A.AJUST_AM,0)) FROM ( SELECT H.LINE_NO, CASE WHEN SA.DRCR_FG = '1' AND A.DRCR_FG IN ( '1', '3' ) THEN ISNULL( A.ACCT_AM, 0 ) WHEN SA.DRCR_FG = '2' AND A.DRCR_FG IN ( '2', '4' ) THEN ISNULL( A.ACCT_AM, 0 ) WHEN SA.DRCR_FG = '2' AND A.DRCR_FG IN ( '1', '3' ) THEN -1*ISNULL( A.ACCT_AM, 0 ) WHEN SA.DRCR_FG = '1' AND A.DRCR_FG IN ( '2', '4' ) THEN -1*ISNULL( A.ACCT_AM, 0 ) ELSE 0 END * CASE D.ID_FG WHEN '1' THEN -1.0 ELSE 1 END AS PRE_AM, 0 AS AJUST_AM FROM AFSFRG_I H LEFT OUTER JOIN AFSFRG_I_D D ON H.CO_CD = D.CO_CD AND H.FS_CD = D.FS_CD AND H.FS_TY = D.FS_TY AND H.LINE_NO = D.LINE_NO AND D.ACCT_TY = @ACCT_FG LEFT OUTER JOIN ADOCUD A ON D.CO_CD = A.CO_CD AND D.ACCT_CD = LEFT(A.ACCT_CD,LEN(D.ACCT_CD)) -- 凭证发生,查询日期之前的. AND A.DIV_CD IN ( SELECT DIV_CD FROM @NODATA_DIVS ) LEFT OUTER JOIN SACCT SA ON D.CO_CD = SA.CO_CD AND D.ACCT_CD = SA.ACCT_CD WHERE H.CO_CD = @CO_CD AND H.FS_CD = @FS_CD AND ( H.FS_TY = @FS_TY AND H.FS_TY IN ('45','40') ) AND H.CALC_TY = '9' AND ( ( @FILL_YN = '1' AND LEFT( A.FILL_DT, 6 ) <= @FRTO_YM_TO AND A.FILL_DT >= @FR_DT ) OR ( @FILL_YN = '0' AND LEFT( A.ISU_DT, 6 ) <= @FRTO_YM_TO AND A.ISU_DT >= @FR_DT ) ) UNION ALL SELECT H.LINE_NO, CASE WHEN SA.DRCR_FG = '1' AND A.DRCR_FG IN ( '1', '3' ) THEN ISNULL( A.ACCT_AM, 0 ) WHEN SA.DRCR_FG = '2' AND A.DRCR_FG IN ( '2', '4' ) THEN ISNULL( A.ACCT_AM, 0 ) WHEN SA.DRCR_FG = '2' AND A.DRCR_FG IN ( '1', '3' ) THEN -1*ISNULL( A.ACCT_AM, 0 ) WHEN SA.DRCR_FG = '1' AND A.DRCR_FG IN ( '2', '4' ) THEN -1*ISNULL( A.ACCT_AM, 0 ) ELSE 0 END * CASE D.ID_FG WHEN '1' THEN -1.0 ELSE 1 END AS PRE_AM, 0 AS AJUST_AM FROM AFSFRG_I H LEFT OUTER JOIN AFSFRG_I_D D ON H.CO_CD = D.CO_CD AND H.FS_CD = D.FS_CD AND H.FS_TY = D.FS_TY AND H.LINE_NO = D.LINE_NO AND D.ACCT_TY = @ACCT_FG LEFT OUTER JOIN APREV A ON D.CO_CD = A.CO_CD AND D.ACCT_CD = LEFT(A.ACCT_CD,LEN(D.ACCT_CD)) -- 年初数据错误 AND A.DIV_CD IN ( SELECT DIV_CD FROM @NODATA_DIVS ) AND A.GISU = @GISU LEFT OUTER JOIN SACCT SA ON D.CO_CD = SA.CO_CD AND D.ACCT_CD = SA.ACCT_CD WHERE H.CO_CD = @CO_CD AND H.FS_CD = @FS_CD AND ( H.FS_TY = @FS_TY AND H.FS_TY IN ('45','40') ) AND H.CALC_TY = '9' ) A GROUP BY A.LINE_NO UNION ALL ------------------------------------------------期初余额초기잔액-------------------------------------------------------------------------------- SELECT A.LINE_NO,SUM(ISNULL(PRE_AM,0)),SUM(ISNULL(A.AJUST_AM,0)) FROM ( SELECT H.LINE_NO, CASE WHEN SA.DRCR_FG = '1' AND A.DRCR_FG IN ( '1', '3' ) THEN ISNULL( A.ACCT_AM, 0 ) WHEN SA.DRCR_FG = '2' AND A.DRCR_FG IN ( '2', '4' ) THEN ISNULL( A.ACCT_AM, 0 ) WHEN SA.DRCR_FG = '2' AND A.DRCR_FG IN ( '1', '3' ) THEN -1*ISNULL( A.ACCT_AM, 0 ) WHEN SA.DRCR_FG = '1' AND A.DRCR_FG IN ( '2', '4' ) THEN -1*ISNULL( A.ACCT_AM, 0 ) ELSE 0 END * CASE D.ID_FG WHEN '1' THEN -1.0 ELSE 1 END AS PRE_AM, 0 AS AJUST_AM FROM AFSFRG_I H LEFT OUTER JOIN AFSFRG_I_D D ON H.CO_CD = D.CO_CD AND H.FS_CD = D.FS_CD AND H.FS_TY = D.FS_TY AND H.LINE_NO = D.LINE_NO AND D.ACCT_TY = @ACCT_FG LEFT OUTER JOIN ADOCUD A ON D.CO_CD = A.CO_CD AND D.ACCT_CD = LEFT(A.ACCT_CD,LEN(D.ACCT_CD)) -- 凭证发生,查询日期之前的. AND A.DIV_CD IN ( SELECT DIV_CD FROM @NODATA_DIVS ) LEFT OUTER JOIN SACCT SA ON D.CO_CD = SA.CO_CD AND D.ACCT_CD = SA.ACCT_CD WHERE H.CO_CD = @CO_CD AND H.FS_CD = @FS_CD AND ( H.FS_TY = @FS_TY AND H.FS_TY IN ('45','40') ) AND H.CALC_TY = '8' AND ( ( @FILL_YN = '1' AND LEFT( A.FILL_DT, 6 ) < @FRTO_YM_FR AND A.FILL_DT >= @FR_DT ) OR ( @FILL_YN = '0' AND LEFT( A.ISU_DT, 6 ) < @FRTO_YM_FR AND A.ISU_DT >= @FR_DT ) ) UNION ALL SELECT H.LINE_NO, CASE WHEN SA.DRCR_FG = '1' AND A.DRCR_FG IN ( '1', '3' ) THEN ISNULL( A.ACCT_AM, 0 ) WHEN SA.DRCR_FG = '2' AND A.DRCR_FG IN ( '2', '4' ) THEN ISNULL( A.ACCT_AM, 0 ) WHEN SA.DRCR_FG = '2' AND A.DRCR_FG IN ( '1', '3' ) THEN -1*ISNULL( A.ACCT_AM, 0 ) WHEN SA.DRCR_FG = '1' AND A.DRCR_FG IN ( '2', '4' ) THEN -1*ISNULL( A.ACCT_AM, 0 ) ELSE 0 END * CASE D.ID_FG WHEN '1' THEN -1.0 ELSE 1 END AS PRE_AM, 0 AS AJUST_AM FROM AFSFRG_I H LEFT OUTER JOIN AFSFRG_I_D D ON H.CO_CD = D.CO_CD AND H.FS_CD = D.FS_CD AND H.FS_TY = D.FS_TY AND H.LINE_NO = D.LINE_NO AND D.ACCT_TY = @ACCT_FG LEFT OUTER JOIN APREV A ON D.CO_CD = A.CO_CD AND D.ACCT_CD = LEFT(A.ACCT_CD,LEN(D.ACCT_CD)) -- 年初数据错误연초데이타오류 AND A.DIV_CD IN ( SELECT DIV_CD FROM @NODATA_DIVS ) AND A.GISU = @GISU LEFT OUTER JOIN SACCT SA ON D.CO_CD = SA.CO_CD AND D.ACCT_CD = SA.ACCT_CD WHERE H.CO_CD = @CO_CD AND H.FS_CD = @FS_CD AND ( H.FS_TY = @FS_TY AND H.FS_TY IN ('45','40') ) AND H.CALC_TY = '8' ) A GROUP BY A.LINE_NO -- 간접법 : 전표기초 - ADOCUD <感觉跟前面重复了앞부분과 중복> /* UNION ALL SELECT H.LINE_NO, CASE WHEN A.DRCR_FG IN ( '1', '3' ) THEN ISNULL( A.ACCT_AM, 0 ) WHEN A.DRCR_FG IN ( '2', '4' ) THEN ISNULL( A.ACCT_AM, 0 ) ELSE 0 END * CASE D.ID_FG WHEN '1' THEN -1.0 ELSE 1 END AS PRE_AM, 0 AS AJUST_AM FROM AFSFRG_I H LEFT OUTER JOIN AFSFRG_I_D D ON H.CO_CD = D.CO_CD AND H.FS_CD = D.FS_CD AND H.FS_TY = D.FS_TY AND H.LINE_NO = D.LINE_NO AND D.ACCT_TY = @ACCT_FG LEFT OUTER JOIN ADOCUD A ON D.CO_CD = A.CO_CD AND D.ACCT_CD = A.ACCT_CD AND A.DIV_CD IN ( SELECT DIV_CD FROM @NODATA_DIVS ) AND ( ( @FILL_YN = '1' AND LEFT( A.FILL_DT, 6 ) < @FRTO_YM_FR AND A.FILL_DT >= @FR_DT ) OR ( @FILL_YN = '0' AND LEFT( A.ISU_DT, 6 ) < @FRTO_YM_FR AND A.ISU_DT >= @FR_DT ) ) LEFT OUTER JOIN SACCT SA ON A.CO_CD = SA.CO_CD AND A.ACCT_CD = SA.ACCT_CD WHERE H.CO_CD = @CO_CD AND H.FS_CD = @FS_CD AND ( H.FS_TY = @FS_TY AND H.FS_TY = '45' ) AND H.CALC_TY = '7' */ ) F ON H.LINE_NO = F.CF_CD WHERE H.CO_CD = @CO_CD AND H.FS_CD = @FS_CD AND H.FS_TY = @FS_TY GROUP BY H.LINE_NO, H.DISP_NM, H.DISP_NMK, H.DISP_NO, H.CALC_TY UNION ALL -- 항목 계산을 위한 데이터 SELECT H.LINE_NO, D.ACCT_CD AS DISP_NM, D.ID_FG AS DISP_NMK, '' AS DISP_NO, 0 AS PRE_AM, 0 AS AJUST_AM, 0 AS TOTAL_AM, H.CALC_TY AS CALC_TY, 'M' AS DATA_FG FROM AFSFRG_I H INNER JOIN AFSFRG_I_D D ON H.CO_CD = D.CO_CD AND H.FS_CD = D.FS_CD AND H.FS_TY = D.FS_TY AND H.LINE_NO = D.LINE_NO AND D.ACCT_TY = @ACCT_FG WHERE H.CO_CD = @CO_CD AND H.FS_CD = @FS_CD AND H.FS_TY = @FS_TY AND H.CALC_TY = '2' ) D ORDER BY D.DATA_FG, D.LINE_NO, D.DISP_NM SET NOCOUNT OFF END