/****************************************************************************************/ --설 명: 마감및년도이월 --수 정 자: 김주영 --수 정 일: 2008/04/18 --유 형: 수정 --내 역: 다국어만 년도이월시 파라미터오류 /******************************************************************************************/ set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go IF EXISTS ( SELECT 1 from dbo.sysobjects where id = object_id('[DZGOLD].[SPA_APREH]') and OBJECTPROPERTY(id, 'IsProcedure') = 1) DROP PROCEDURE [DZGOLD].[SPA_APREH] GO CREATE PROC [DZGOLD].[SPA_APREH] ( @S_CO_CD VARCHAR(4), @S_DIV_CD VARCHAR(4), @S_DEPT_CD VARCHAR(4), @S_EMP_CD VARCHAR(10), @S_GISU NUMERIC(3), @START VARCHAR(8), @FRDT VARCHAR(8), @TODT VARCHAR(8), @V_ACCT_CD VARCHAR(5), @V_ACCT_FG VARCHAR(1), @V_SUB_DISP VARCHAR(2), @V_GR_CD VARCHAR(1), --BY 제희경 : 이월항목 1개 에서 3개까지로 변경되면서 이월항목 2, 3 추가 @V_GR_CD1 VARCHAR(1), @V_GR_CD2 VARCHAR(1), --========================= @V_DRCR_FG1 VARCHAR(1), @LANG_KIND VARCHAR(3) -- 보여지는 언어 종류 ("KOR", "ENG"..) ) -- WITH ENCRYPTION AS /*-------------------------------------------------------------------*/ /*기 능: 회계보조화면계정 마감이월 */ /*작성자: 제희경<- 이강수 */ /*작성일: 2001/05/30 */ /*수정일: 2005/01/10 <- 2003/05/22 <- 2002/01/03 */ /*사용법: SPA_APREH @S_CO_CD,@S_DIV_CD,@S_DEPT_CD,@S_EMP_CD,@S_GISU,@FRDT,@TODT,@V_ACCT_CD,@V_ACCT_FG,@V_SUB_DISP,@V_GR_CD,@V_RACCT_CD,@V_DRCR_FG */ /*-------------------------------------------------------------------*/ -- 수정일 : 2002/01/23 -- 수정자 : 제희경 -- 수정내역 : 이월항목 갯수가 1개에서 3개로 증설 ----------------------------------------------------------- -- 수정일 : 2005/01/11 -- 수정자 : 제희경 -- 수정내역 : *연동항목이 '09'(기간비용)일때는 다른 연동항목들과 이월방식 차별화. ----------------------------------------------------------- -- 수정일 : 2005/02/14 -- 수정자 : 제희경 -- 수정내역 : *연동항목이 '09'(기간비용)일때는 이월항목 설정되지 않았을때는 해당계정으로 이월, 설정됐을때는 이월항목에 따라서만 이월되도록 수정. ----------------------------------------------------------- -- 수정일 : 2005/10/14 -- 수정자 : 제희경 -- 수정내역 : *SYBASE DB에서 필드명 중복으로 이월 결과값 달라지는 문제 수정. ----------------------------------------------------------- -- 수정일 : 2006/03/24 -- 수정자 : 제희경 -- 수정내역 : *이월항목에 'A','F','G','H','I','J'를 제외한 C와 D계열등만 설정된 상태로 이월시 이월 누락되는 문제 수정. -- : *한국어, 다국어(중국어제외) 프로시져 통합 및 LANG_KIND 파라미터 추가. ----------------------------------------------------------- -- 수정일 : 2006/08/11 -- 수정자 : 제희경 -- 수정내역 : 이월항목 변수 최대 자릿수 변경 (10-> 20으로) ----------------------------------------------------------- -- INIT_AM에 null값 들어가는 문제 수정 (20070808) ----------------------------------------------------------- -- 수정일 : 2008/01/03 -- 수정자 : 김철희 -- 수정내역 : * 이월항목 'G' 인 경우 이월 처리하지 않음. ( TR_CD 충돌 ) ----------------------------------------------------------- SET NOCOUNT ON SET ANSI_WARNINGS OFF DECLARE @V_FILL_NB NUMERIC(5), --BY 제희경 2003/01/27 --V_TR_CD 최대 자리수는 10임.(PJT_CD일때 사원의 경우 10임) --@V_TR_CD VARCHAR(5), -- 최대자릿수가 20으로 변경.(CT_NB일때 20까지임) - 2006.08.11 --@V_TR_CD VARCHAR(10), @V_TR_CD VARCHAR(20), @V_CT_DEPT VARCHAR(4), @V_TR_NM VARCHAR(40), @V_CT_NB VARCHAR(20), @V_NUM INT, @V_ISU_DT VARCHAR(8), @V_ISU_SQ INT, @V_LN_SQ INT, @V_CNT NUMERIC(5), @V_ACCT_AM NUMERIC(17,4), @V_CT_AM NUMERIC(17,4), @V_CT_DEAL VARCHAR(4), @FLAG VARCHAR(16), @ERRNO INT, @ERRMSG VARCHAR(5000), -- BY 제희경 : 이월항목 1개 에서 3개까지로 변경되면서 이월항목 2, 3 추가되면서 필요한 변수 추가 --@V_TR_CD1 VARCHAR(10), --@V_TR_CD2 VARCHAR(10), @V_TR_CD1 VARCHAR(20), @V_TR_CD2 VARCHAR(20), @v_Sel VARCHAR(20), @v_Sel1 VARCHAR(20), @v_Sel2 VARCHAR(20), @v_NULL VARCHAR(40), @v_NULL1 VARCHAR(40), @v_NULL2 VARCHAR(40), @v_Group VARCHAR(35), @v_Group1 VARCHAR(35), @v_Group2 VARCHAR(35), @v_Into VARCHAR(40), @v_Into1 VARCHAR(40), @v_Into2 VARCHAR(40), @v_Update VARCHAR(40), @v_Update1 VARCHAR(40), @v_Update2 VARCHAR(40), @v_Set1 VARCHAR(10), @v_Set2 VARCHAR(10), @v_Set3 VARCHAR(10), @v_strQUERY VARCHAR(8000), @v_strSQL VARCHAR(1000), @v_strFETCH VARCHAR(200), @v_strUPDATE VARCHAR(300), @V_DRCR_FG VARCHAR(1), @O_ISU_SQ INT, @V_AMT0 NUMERIC(17,4), @V_AMT5 NUMERIC(17,4), @V_AMT6 NUMERIC(17,4), @V_AMT8 NUMERIC(17,4) --============================ IF (@V_SUB_DISP = '04' ) --연동항목이 받을어음 일때 BEGIN DECLARE SBU_SBILL CURSOR FOR SELECT ISNULL(D.CT_NB,'No') AS CT_NB,SUM(D.ACCT_AM) ACCT_AM FROM (SELECT CO_CD,DIV_CD,ACCT_CD,CT_NB, CASE WHEN DRCR_FG IN ('1','3') THEN ACCT_AM ELSE ACCT_AM *-1 END ACCT_AM FROM APREV WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND FILL_DT BETWEEN @FRDT AND @TODT AND ACCT_CD =@V_ACCT_CD UNION ALL SELECT CO_CD,DIV_CD,ACCT_CD,CT_NB, CASE WHEN DRCR_FG IN ('1','3') THEN ACCT_AM ELSE ACCT_AM *-1 END ACCT_AM FROM ADOCUD WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND FILL_DT BETWEEN @FRDT AND @TODT AND ACCT_CD =@V_ACCT_CD ) D GROUP BY ISNULL(D.CT_NB,'No') OPEN SBU_SBILL FETCH SBU_SBILL INTO @V_CT_NB,@V_ACCT_AM WHILE (@@FETCH_STATUS = 0) BEGIN SELECT @V_FILL_NB=( SELECT ISNULL(MAX(ISU_SQ),0)+1 FROM APREV WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND GISU = @S_GISU+1 AND ACCT_CD =@V_ACCT_CD ) IF (@V_FILL_NB>0 AND @V_ACCT_AM <> 0) BEGIN INSERT APREV ( CO_CD, GISU, DIV_CD, ACCT_CD, ISU_SQ, DEPT_CD, EMP_CD, DRCR_FG, FILL_DT, FILL_NB, RMK_NB, RMK_DC, ACCT_AM , TRCD_TY, TRNM_TY, DEPTCD_TY, PJTCD_TY, CTNB_TY, FRDT_TY, TODT_TY, QT_TY, AM_TY, RT_TY, DEAL_TY , USER1_TY, USER2_TY, TR_CD, TR_NM , CT_DEPT, PJT_CD, CT_NB, FR_DT, TO_DT , CT_DEAL, CT_USER1, CT_USER2) SELECT TOP 1 @S_CO_CD,@S_GISU+1,@S_DIV_CD,@V_ACCT_CD,@V_FILL_NB,@S_DEPT_CD,@S_EMP_CD, '3',@START,@V_FILL_NB,0, CASE WHEN @LANG_KIND = 'KOR' THEN '마감이월' ELSE 'Closing C/F' END,@V_ACCT_AM, A.TRCD_TY, A.TRNM_TY, A.DEPTCD_TY, A.PJTCD_TY, A.CTNB_TY, A.FRDT_TY, A.TODT_TY, A.QT_TY, A.AM_TY, A.RT_TY, A.DEAL_TY ,A.USER1_TY, A.USER2_TY,D.TR_CD,T.TR_NM ,D.CLT_DEPT,'' PJT_CD,@V_CT_NB ,CASE WHEN D.ISS_DT = '00000000' or (D.ISS_DT > '19500101' and D.ISS_DT < '21000101' and DATALENGTH(RTRIM(D.ISS_DT)) = 8 ) THEN D.ISS_DT ELSE '00000000' END AS ISS_DT ,CASE WHEN D.DUE_DT = '00000000' or (D.DUE_DT > '19500101' and D.DUE_DT < '21000101' and DATALENGTH(RTRIM(D.DUE_DT)) = 8 ) THEN D.DUE_DT ELSE '00000000' END AS DUE_DT ,D.BILL_FG,'' CT_USER1,'' CT_USER2 FROM SBILL D LEFT OUTER JOIN SACCT A ON D.CO_CD=A.CO_CD AND A.ACCT_CD=@V_ACCT_CD LEFT OUTER JOIN STRADE T ON D.CO_CD=T.CO_CD AND D.TR_CD=T.TR_CD WHERE D.CO_CD=@S_CO_CD AND D.ACCT_CD=@V_ACCT_CD AND ISNULL(D.BILL_NB,'No')=@V_CT_NB -- DETAIL INSERT IF ( @V_ACCT_CD >= '14600' AND @V_ACCT_CD <= '16399' AND @V_ACCT_FG = '2' ) BEGIN SELECT @O_ISU_SQ = ( SELECT ISNULL(MAX(ISU_SQ), 0)+1 FROM APREV_D WHERE CO_CD = @S_CO_CD AND DIV_CD = @S_DIV_CD AND GISU = @S_GISU+1 AND ACCT_CD = @V_ACCT_CD ) IF ( @O_ISU_SQ > 0 ) BEGIN INSERT INTO APREV_D ( CO_CD, GISU, DIV_CD, ACCT_CD, ISU_SQ ) VALUES ( @S_CO_CD, @S_GISU+1, @S_DIV_CD, @V_ACCT_CD, @O_ISU_SQ ) --update 항목 집계 -- 기초재고액(AMT0) SELECT @V_AMT0 = ISNULL(ACCT_AM, 0) FROM APREV WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND GISU = @S_GISU AND ACCT_CD = @V_ACCT_CD --타계정입고(AMT5) SELECT @V_AMT5 = ISNULL(SUM (ISNULL(ACCT_AM, 0)) , 0 ) FROM ADOCUD D WHERE D.CO_CD=@S_CO_CD AND D.DIV_CD = @S_DIV_CD AND D.DRCR_FG IN ('1','3') AND D.CT_DEAL = '1' AND D.ACCT_CD = @V_ACCT_CD AND LEFT(D.FILL_DT, 6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) --타계정출고(AMT6) SELECT @V_AMT6 = ISNULL(SUM (ISNULL(ACCT_AM, 0)) , 0 ) FROM ADOCUD D WHERE D.CO_CD=@S_CO_CD AND D.DIV_CD = @S_DIV_CD AND D.DRCR_FG IN ('2','4') AND D.CT_DEAL = '2' AND D.ACCT_CD = @V_ACCT_CD AND LEFT(D.FILL_DT, 6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) --기말재고액(AMT8) SET @V_AMT8 = @V_AMT0 + @V_AMT5 - @V_AMT6 UPDATE APREV_D SET INIT_AM = @V_AMT0, AIN_AM = @V_AMT5, AOUT_AM = @V_AMT6, STOC_AM = @V_AMT8 WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND ACCT_CD = @V_ACCT_CD AND GISU = @S_GISU+1 AND ISU_SQ = @O_ISU_SQ END END END FETCH SBU_SBILL INTO @V_CT_NB,@V_ACCT_AM END -- COMMIT TRANSACTION CLOSE SBU_SBILL DEALLOCATE SBU_SBILL END IF (@V_SUB_DISP = '05' ) --연동항목이 지급어음 일때 BEGIN DECLARE SBU_ABILL CURSOR FOR SELECT ISNULL(D.CT_NB,'No') ,SUM(D.ACCT_AM) ACCT_AM FROM (SELECT CO_CD,DIV_CD,ACCT_CD,CT_NB, CASE WHEN DRCR_FG IN ('2','4') THEN ACCT_AM ELSE ACCT_AM *-1 END ACCT_AM FROM APREV WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND FILL_DT = @FRDT AND ACCT_CD =@V_ACCT_CD UNION ALL SELECT CO_CD,DIV_CD,ACCT_CD,CT_NB, CASE WHEN DRCR_FG IN ('2','4') THEN ACCT_AM ELSE ACCT_AM *-1 END ACCT_AM FROM ADOCUD WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND FILL_DT BETWEEN @FRDT AND @TODT AND ACCT_CD =@V_ACCT_CD ) D GROUP BY ISNULL(D.CT_NB,'No') OPEN SBU_ABILL FETCH SBU_ABILL INTO @V_CT_NB,@V_ACCT_AM WHILE (@@FETCH_STATUS = 0) BEGIN SELECT @V_FILL_NB=( SELECT ISNULL(MAX(ISU_SQ),0)+1 FROM APREV WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND GISU = @S_GISU+1 AND ACCT_CD =@V_ACCT_CD ) --2003.01.29 by 제희경 : 지급어음 금액이 '0'(이미 결제가 이루어진경우)인 경우 제외하고 이월 --IF (@V_FILL_NB>0 ) IF (@V_FILL_NB>0 AND @V_ACCT_AM <> 0 ) BEGIN INSERT APREV ( CO_CD, GISU, DIV_CD, ACCT_CD, ISU_SQ, DEPT_CD, EMP_CD, DRCR_FG, FILL_DT, FILL_NB, RMK_NB, RMK_DC, ACCT_AM , TRCD_TY, TRNM_TY, DEPTCD_TY, PJTCD_TY, CTNB_TY, FRDT_TY, TODT_TY, QT_TY, AM_TY, RT_TY, DEAL_TY , USER1_TY, USER2_TY, TR_CD, TR_NM , CT_DEPT, PJT_CD, CT_NB, FR_DT, TO_DT , CT_DEAL, CT_USER1, CT_USER2) SELECT @S_CO_CD,@S_GISU+1,@S_DIV_CD,@V_ACCT_CD,@V_FILL_NB,@S_DEPT_CD,@S_EMP_CD, '4',@START,@V_FILL_NB,0,CASE WHEN @LANG_KIND = 'KOR' THEN'마감이월' ELSE 'Closing C/F' END ,@V_ACCT_AM, A.TRCD_TY, A.TRNM_TY, A.DEPTCD_TY, A.PJTCD_TY, A.CTNB_TY, A.FRDT_TY, A.TODT_TY, A.QT_TY, A.AM_TY, A.RT_TY, A.DEAL_TY --2003.01.29 by 제희경 : 지급어음 거래처명이 ''으로 들어옴. => 거래처명 들어오도록 수정 -- ,A.USER1_TY, A.USER2_TY,D.TR_CD,'' TR_NM ,A.USER1_TY, A.USER2_TY, D.TR_CD, T.TR_NM ,D.DEPT_cd,'' PJT_CD,@V_CT_NB ,CASE WHEN D.ISS_DT = '00000000' or (D.ISS_DT > '19500101' and D.ISS_DT < '21000101' and DATALENGTH(RTRIM(D.ISS_DT)) = 8 ) THEN D.ISS_DT ELSE '00000000' END AS ISS_DT ,CASE WHEN D.DUE_DT = '00000000' or (D.DUE_DT > '19500101' and D.DUE_DT < '21000101' and DATALENGTH(RTRIM(D.DUE_DT)) = 8 ) THEN D.DUE_DT ELSE '00000000' END AS DUE_DT ,D.BILL_FG,'' CT_USER1,'' CT_USER2 FROM ABILLDEB D LEFT OUTER JOIN SACCT A ON D.CO_CD=A.CO_CD AND A.ACCT_CD=@V_ACCT_CD LEFT OUTER JOIN STRADE T ON D.CO_CD=T.CO_CD AND D.TR_CD=T.TR_CD WHERE D.CO_CD=@S_CO_CD AND A.ACCT_CD=@V_ACCT_CD AND ISNULL(D.BILL_NB,'No')=@V_CT_NB -- DETAIL INSERT IF ( @V_ACCT_CD >= '14600' AND @V_ACCT_CD <= '16399' AND @V_ACCT_FG = '2' ) BEGIN SELECT @O_ISU_SQ = ( SELECT ISNULL(MAX(ISU_SQ), 0)+1 FROM APREV_D WHERE CO_CD = @S_CO_CD AND DIV_CD = @S_DIV_CD AND GISU = @S_GISU+1 AND ACCT_CD = @V_ACCT_CD ) IF ( @O_ISU_SQ > 0 ) BEGIN INSERT INTO APREV_D ( CO_CD, GISU, DIV_CD, ACCT_CD, ISU_SQ ) VALUES ( @S_CO_CD, @S_GISU+1, @S_DIV_CD, @V_ACCT_CD, @O_ISU_SQ ) --update 항목 집계 -- 기초재고액(AMT0) SELECT @V_AMT0 = ISNULL(ACCT_AM, 0) FROM APREV WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND GISU = @S_GISU AND ACCT_CD = @V_ACCT_CD --타계정입고(AMT5) SELECT @V_AMT5 = ISNULL(SUM (ISNULL(ACCT_AM, 0)) , 0 ) FROM ADOCUD D WHERE D.CO_CD=@S_CO_CD AND D.DIV_CD = @S_DIV_CD AND D.DRCR_FG IN ('1','3') AND D.CT_DEAL = '1' AND D.ACCT_CD = @V_ACCT_CD AND LEFT(D.FILL_DT, 6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) --타계정출고(AMT6) SELECT @V_AMT6 = ISNULL(SUM (ISNULL(ACCT_AM, 0)) , 0 ) FROM ADOCUD D WHERE D.CO_CD=@S_CO_CD AND D.DIV_CD = @S_DIV_CD AND D.DRCR_FG IN ('2','4') AND D.CT_DEAL = '2' AND D.ACCT_CD = @V_ACCT_CD AND LEFT(D.FILL_DT, 6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) --기말재고액(AMT8) SET @V_AMT8 = @V_AMT0 + @V_AMT5 - @V_AMT6 UPDATE APREV_D SET INIT_AM = @V_AMT0, AIN_AM = @V_AMT5, AOUT_AM = @V_AMT6, STOC_AM = @V_AMT8 WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND ACCT_CD = @V_ACCT_CD AND GISU = @S_GISU+1 AND ISU_SQ = @O_ISU_SQ END END END FETCH SBU_ABILL INTO @V_CT_NB,@V_ACCT_AM END -- COMMIT TRANSACTION CLOSE SBU_ABILL DEALLOCATE SBU_ABILL END IF (@V_SUB_DISP IN ('06','07' ) ) --연동항목이 차입대여금 이면 BEGIN DECLARE SBU_ACTNB CURSOR FOR SELECT D.TR_CD,D.TR_NM,D.JAN_AM,D.ISU_DT,D.ISU_SQ,D.LN_SQ FROM VA_RF D WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND D.ISU_DT BETWEEN @FRDT AND @TODT AND D.ACCT_CD =@V_ACCT_CD AND D.JAN_AM<>0 OPEN SBU_ACTNB FETCH SBU_ACTNB INTO @V_TR_CD, @V_TR_NM,@V_ACCT_AM,@V_ISU_DT,@V_ISU_SQ,@V_LN_SQ WHILE (@@FETCH_STATUS = 0) BEGIN SELECT @V_FILL_NB=( SELECT ISNULL(MAX(ISU_SQ),0)+1 FROM APREV WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND GISU = @S_GISU+1 AND ACCT_CD =@V_ACCT_CD ) IF (@V_FILL_NB>0) BEGIN INSERT APREV ( CO_CD, GISU, DIV_CD, ACCT_CD, ISU_SQ, DEPT_CD, EMP_CD, DRCR_FG, FILL_DT, FILL_NB, RMK_NB, RMK_DC, ACCT_AM , TRCD_TY, TRNM_TY, DEPTCD_TY, PJTCD_TY, CTNB_TY, FRDT_TY, TODT_TY, QT_TY, AM_TY, RT_TY, DEAL_TY , USER1_TY, USER2_TY, TR_CD, TR_NM , CT_DEPT, PJT_CD, CT_NB, FR_DT, TO_DT --, CT_QT, CT_AM, CT_RT , CT_DEAL, CT_USER1, CT_USER2) SELECT @S_CO_CD,@S_GISU+1,@S_DIV_CD,@V_ACCT_CD,@V_FILL_NB,@S_DEPT_CD,@S_EMP_CD, (CASE WHEN A.DRCR_FG='1' THEN '3' ELSE '4' END) AS DRCR_FG, @START,@V_FILL_NB,0,CASE WHEN @LANG_KIND = 'KOR' THEN '마감이월' ELSE 'Closing C/F' END,@V_ACCT_AM, A.TRCD_TY, A.TRNM_TY, A.DEPTCD_TY, A.PJTCD_TY, A.CTNB_TY, A.FRDT_TY, A.TODT_TY, A.QT_TY, A.AM_TY, A.RT_TY, A.DEAL_TY ,A.USER1_TY, A.USER2_TY,@V_TR_CD,@V_TR_NM ,D.CT_DEPT,D.PJT_CD,D.CT_NB ,CASE WHEN D.FR_DT = '00000000' or (D.FR_DT > '19500101' and D.FR_DT < '21000101' and DATALENGTH(RTRIM(D.FR_DT))=8) THEN D.FR_DT ELSE '00000000' END AS FR_DT ,CASE WHEN D.TO_DT = '00000000' or (D.TO_DT > '19500101' and D.TO_DT < '21000101' and DATALENGTH(RTRIM(D.TO_DT))=8) THEN D.TO_DT ELSE '00000000' END AS TO_DT ,D.CT_DEAL,D.CT_USER1,D.CT_USER2 FROM VA_RF D,SACCT A WHERE D.CO_CD=A.CO_CD AND D.ACCT_CD = A.ACCT_CD AND D.CO_CD=@S_CO_CD AND D.ACCT_CD=@V_ACCT_CD AND D.ISU_DT=@V_ISU_DT AND D.ISU_SQ=@V_ISU_SQ AND D.LN_SQ = @V_LN_SQ -- ) -- DETAIL INSERT IF ( @V_ACCT_CD >= '14600' AND @V_ACCT_CD <= '16399' AND @V_ACCT_FG = '2' ) BEGIN SELECT @O_ISU_SQ = ( SELECT ISNULL(MAX(ISU_SQ), 0)+1 FROM APREV_D WHERE CO_CD = @S_CO_CD AND DIV_CD = @S_DIV_CD AND GISU = @S_GISU+1 AND ACCT_CD = @V_ACCT_CD ) IF ( @O_ISU_SQ > 0 ) BEGIN INSERT INTO APREV_D ( CO_CD, GISU, DIV_CD, ACCT_CD, ISU_SQ ) VALUES ( @S_CO_CD, @S_GISU+1, @S_DIV_CD, @V_ACCT_CD, @O_ISU_SQ ) --update 항목 집계 -- 기초재고액(AMT0) SELECT @V_AMT0 = ISNULL(ACCT_AM, 0) FROM APREV WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND GISU = @S_GISU AND ACCT_CD = @V_ACCT_CD --타계정입고(AMT5) SELECT @V_AMT5 = ISNULL(SUM (ISNULL(ACCT_AM, 0)) , 0 ) FROM ADOCUD D WHERE D.CO_CD=@S_CO_CD AND D.DIV_CD = @S_DIV_CD AND D.DRCR_FG IN ('1','3') AND D.CT_DEAL = '1' AND D.ACCT_CD = @V_ACCT_CD AND LEFT(D.FILL_DT, 6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) --타계정출고(AMT6) SELECT @V_AMT6 = ISNULL(SUM (ISNULL(ACCT_AM, 0)) , 0 ) FROM ADOCUD D WHERE D.CO_CD=@S_CO_CD AND D.DIV_CD = @S_DIV_CD AND D.DRCR_FG IN ('2','4') AND D.CT_DEAL = '2' AND D.ACCT_CD = @V_ACCT_CD AND LEFT(D.FILL_DT, 6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) --기말재고액(AMT8) SET @V_AMT8 = @V_AMT0 + @V_AMT5 - @V_AMT6 UPDATE APREV_D SET INIT_AM = @V_AMT0, AIN_AM = @V_AMT5, AOUT_AM = @V_AMT6, STOC_AM = @V_AMT8 WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND ACCT_CD = @V_ACCT_CD AND GISU = @S_GISU+1 AND ISU_SQ = @O_ISU_SQ END END END FETCH SBU_ACTNB INTO @V_TR_CD, @V_TR_NM,@V_ACCT_AM,@V_ISU_DT,@V_ISU_SQ,@V_LN_SQ END CLOSE SBU_ACTNB DEALLOCATE SBU_ACTNB END IF (@V_SUB_DISP = '08' ) --연동항목이 유가증권 이면 BEGIN DECLARE SBU_SCU CURSOR FOR SELECT F.ACCT_CD,F.CT_NB,SUM(ISNULL(F.ACCT_AM,0)) AS ACCT_AM FROM ( SELECT D.ACCT_CD , ISNULL(D.CT_NB,'NO') AS CT_NB, CASE WHEN D.DRCR_FG IN('1','3') THEN D.ACCT_AM ELSE D.ACCT_AM * -1 END AS ACCT_AM FROM APREV D INNER JOIN ( SELECT CO_CD,ACCT_CD,DEAL_TY FROM SACCT WHERE SUB_DISP = '08' ) S ON D.CO_CD = S.CO_CD AND D.ACCT_CD = S.ACCT_CD WHERE D.CO_CD=@S_CO_CD AND D.DIV_CD = @S_DIV_CD AND D.FILL_DT BETWEEN @FRDT AND @TODT AND D.ACCT_CD =@V_ACCT_CD UNION ALL SELECT D.ACCT_CD , ISNULL(D.CT_NB,'NO') AS CT_NB, CASE WHEN D.DRCR_FG IN('1','3') THEN D.ACCT_AM ELSE D.ACCT_AM * -1 END AS ACCT_AM FROM ADOCUD D INNER JOIN ( SELECT CO_CD,ACCT_CD,DEAL_TY FROM SACCT WHERE SUB_DISP = '08' ) S ON D.CO_CD = S.CO_CD AND D.ACCT_CD = S.ACCT_CD AND D.FILL_DT > '00000000' WHERE D.CO_CD=@S_CO_CD AND D.DIV_CD = @S_DIV_CD AND D.FILL_DT BETWEEN @FRDT AND @TODT AND D.ACCT_CD =@V_ACCT_CD ) F GROUP BY F.ACCT_CD,F.CT_NB HAVING SUM(ISNULL(F.ACCT_AM,0))<>0 OPEN SBU_SCU FETCH SBU_SCU INTO @V_ACCT_CD,@V_CT_NB, @V_ACCT_AM WHILE (@@FETCH_STATUS = 0) BEGIN SELECT @V_FILL_NB=( SELECT ISNULL(MAX(ISU_SQ),0)+1 FROM APREV WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND GISU = @S_GISU+1 AND ACCT_CD =@V_ACCT_CD ) IF (@V_FILL_NB>0) BEGIN INSERT APREV ( CO_CD, GISU, DIV_CD, ACCT_CD, ISU_SQ, DEPT_CD, EMP_CD, DRCR_FG, FILL_DT, FILL_NB, RMK_NB, RMK_DC, ACCT_AM , TRCD_TY, TRNM_TY, DEPTCD_TY, PJTCD_TY, CTNB_TY, FRDT_TY, TODT_TY, QT_TY, AM_TY, RT_TY, DEAL_TY , USER1_TY, USER2_TY, TR_CD, TR_NM , CT_DEPT, PJT_CD, CT_NB, FR_DT, TO_DT --, CT_QT, CT_AM, CT_RT , CT_DEAL, CT_USER1, CT_USER2) SELECT TOP 1 @S_CO_CD,@S_GISU+1,@S_DIV_CD,@V_ACCT_CD,@V_FILL_NB,@S_DEPT_CD,@S_EMP_CD, '3' AS DRCR_FG, @START,@V_FILL_NB,0, CASE WHEN @LANG_KIND = 'KOR' THEN '마감이월' ELSE 'Closing C/F' END,@V_ACCT_AM, A.TRCD_TY, A.TRNM_TY, A.DEPTCD_TY, A.PJTCD_TY, A.CTNB_TY, A.FRDT_TY, A.TODT_TY, A.QT_TY, A.AM_TY, A.RT_TY, A.DEAL_TY ,A.USER1_TY, A.USER2_TY,@V_TR_CD,@V_TR_NM ,D.CT_DEPT,D.PJT_CD,D.CT_NB ,CASE WHEN D.FR_DT = '00000000' or (D.FR_DT > '19500101' and D.FR_DT < '21000101' and DATALENGTH(RTRIM(D.FR_DT))=8) THEN D.FR_DT ELSE '00000000' END AS FR_DT ,CASE WHEN D.TO_DT = '00000000' or (D.TO_DT > '19500101' and D.TO_DT < '21000101' and DATALENGTH(RTRIM(D.TO_DT))=8) THEN D.TO_DT ELSE '00000000' END AS TO_DT ,D.CT_DEAL,D.CT_USER1,D.CT_USER2 FROM SACCT A LEFT OUTER JOIN VA_SECUR D ON A.CO_CD=D.CO_CD AND A.ACCT_CD = D.ACCT_CD AND ISNULL(D.CT_NB,'NO')=@V_CT_NB AND D.DRCR_FG IN ('1','3') WHERE A.CO_CD=@S_CO_CD AND A.ACCT_CD=@V_ACCT_CD -- DETAIL INSERT IF ( @V_ACCT_CD >= '14600' AND @V_ACCT_CD <= '16399' AND @V_ACCT_FG = '2' ) BEGIN SELECT @O_ISU_SQ = ( SELECT ISNULL(MAX(ISU_SQ), 0)+1 FROM APREV_D WHERE CO_CD = @S_CO_CD AND DIV_CD = @S_DIV_CD AND GISU = @S_GISU+1 AND ACCT_CD = @V_ACCT_CD ) IF ( @O_ISU_SQ > 0 ) BEGIN INSERT INTO APREV_D ( CO_CD, GISU, DIV_CD, ACCT_CD, ISU_SQ ) VALUES ( @S_CO_CD, @S_GISU+1, @S_DIV_CD, @V_ACCT_CD, @O_ISU_SQ ) --update 항목 집계 -- 기초재고액(AMT0) SELECT @V_AMT0 = ISNULL(ACCT_AM, 0) FROM APREV WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND GISU = @S_GISU AND ACCT_CD = @V_ACCT_CD --타계정입고(AMT5) SELECT @V_AMT5 = ISNULL(SUM (ISNULL(ACCT_AM, 0)) , 0 ) FROM ADOCUD D WHERE D.CO_CD=@S_CO_CD AND D.DIV_CD = @S_DIV_CD AND D.DRCR_FG IN ('1','3') AND D.CT_DEAL = '1' AND D.ACCT_CD = @V_ACCT_CD AND LEFT(D.FILL_DT, 6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) --타계정출고(AMT6) SELECT @V_AMT6 = ISNULL(SUM (ISNULL(ACCT_AM, 0)) , 0 ) FROM ADOCUD D WHERE D.CO_CD=@S_CO_CD AND D.DIV_CD = @S_DIV_CD AND D.DRCR_FG IN ('2','4') AND D.CT_DEAL = '2' AND D.ACCT_CD = @V_ACCT_CD AND LEFT(D.FILL_DT, 6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) --기말재고액(AMT8) SET @V_AMT8 = @V_AMT0 + @V_AMT5 - @V_AMT6 UPDATE APREV_D SET INIT_AM = @V_AMT0, AIN_AM = @V_AMT5, AOUT_AM = @V_AMT6, STOC_AM = @V_AMT8 WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND ACCT_CD = @V_ACCT_CD AND GISU = @S_GISU+1 AND ISU_SQ = @O_ISU_SQ END END END FETCH SBU_SCU INTO @V_ACCT_CD,@V_CT_NB, @V_ACCT_AM END CLOSE SBU_SCU DEALLOCATE SBU_SCU END --2005.01.11 연동항목 09 기간비용은 이월항목 없을때 SPA_APREV에서 이월되도록 변경, 이월항목 있을때는 프로시져하단의 이월항목값따라 이월 /* IF (@V_SUB_DISP ='09' ) --연동항목이 기간비용이면 이면 BEGIN DECLARE SBU_TERMC CURSOR FOR SELECT D.ISU_DT,D.ISU_SQ,D.LN_SQ FROM ATERMC D JOIN ADOCUD F ON D.ISU_DT=F.ISU_DT AND D.ISU_SQ=F.ISU_SQ AND D.LN_SQ=F.LN_SQ AND D.CO_CD=F.CO_CD WHERE D.CO_CD=@S_CO_CD AND D.DIV_CD = @S_DIV_CD AND D.FILL_DT BETWEEN @FRDT AND @TODT AND D.PPAY_AM>0 AND F.ACCT_CD=@V_ACCT_CD OPEN SBU_TERMC FETCH SBU_TERMC INTO @V_ISU_DT,@V_ISU_SQ,@V_LN_SQ WHILE (@@FETCH_STATUS = 0) BEGIN SELECT @V_FILL_NB = ( SELECT ISNULL(MAX(ISU_SQ),0)+1 FROM APREV WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND GISU = @S_GISU+1 AND ACCT_CD =@V_ACCT_CD ) IF (@V_FILL_NB>0) BEGIN INSERT APREV ( CO_CD, GISU, DIV_CD, ACCT_CD, ISU_SQ, DEPT_CD, EMP_CD, DRCR_FG, FILL_DT, FILL_NB, RMK_NB, RMK_DC, ACCT_AM , TRCD_TY, TRNM_TY, DEPTCD_TY, PJTCD_TY, CTNB_TY, FRDT_TY, TODT_TY, QT_TY, AM_TY, RT_TY, DEAL_TY , USER1_TY, USER2_TY, TR_CD, TR_NM , CT_DEPT, PJT_CD, CT_NB, FR_DT, TO_DT , CT_QT, CT_AM, CT_RT , CT_DEAL, CT_USER1, CT_USER2) SELECT @S_CO_CD,@S_GISU+1,@S_DIV_CD,AD.ACCT_CD,@V_FILL_NB,@S_DEPT_CD,@S_EMP_CD, (CASE WHEN A.DRCR_FG='1' THEN '3' ELSE '4' END) AS DRCR_FG, @START,@V_FILL_NB,0,'마감이월',D.PPAY_AM ACCT_AM, A.TRCD_TY, A.TRNM_TY, A.DEPTCD_TY, A.PJTCD_TY, A.CTNB_TY, A.FRDT_TY, A.TODT_TY, A.QT_TY, A.AM_TY, A.RT_TY, A.DEAL_TY ,A.USER1_TY, A.USER2_TY,AD.TR_CD,AD.TR_NM ,AD.CT_DEPT,AD.PJT_CD,AD.CT_NB ,@START ,CASE WHEN D.TO_DT = '00000000' or (D.TO_DT > '19500101' and D.TO_DT < '21000101' and DATALENGTH(RTRIM(D.TO_DT))=8) THEN AD.TO_DT ELSE '00000000' END AS TO_DT ,AD.CT_QT,AD.CT_AM,AD.CT_RT ,AD.CT_DEAL,AD.CT_USER1,AD.CT_USER2 FROM ATERMC D LEFT OUTER JOIN ADOCUD AD ON D.CO_CD=AD.CO_CD AND D.ISU_DT=AD.ISU_DT AND D.ISU_SQ=AD.ISU_SQ AND D.LN_SQ=AD.LN_SQ LEFT OUTER JOIN SACCT A ON AD.CO_CD=A.CO_CD AND AD.ACCT_CD=A.ACCT_CD WHERE D.CO_CD=@S_CO_CD AND D.ISU_DT=@V_ISU_DT AND D.ISU_SQ=@V_ISU_SQ AND D.LN_SQ = @V_LN_SQ AND AD.ACCT_CD=@V_ACCT_CD -- DETAIL INSERT IF ( @V_ACCT_CD >= '14600' AND @V_ACCT_CD <= '16399' AND @V_ACCT_FG = '2' ) BEGIN SELECT @O_ISU_SQ = ( SELECT ISNULL(MAX(ISU_SQ), 0)+1 FROM APREV_D WHERE CO_CD = @S_CO_CD AND DIV_CD = @S_DIV_CD AND GISU = @S_GISU+1 AND ACCT_CD = @V_ACCT_CD ) IF ( @O_ISU_SQ > 0 ) BEGIN INSERT INTO APREV_D ( CO_CD, GISU, DIV_CD, ACCT_CD, ISU_SQ ) VALUES ( @S_CO_CD, @S_GISU+1, @S_DIV_CD, @V_ACCT_CD, @O_ISU_SQ ) --update 항목 집계 -- 기초재고액(AMT0) SELECT @V_AMT0 = ISNULL(ACCT_AM, 0) FROM APREV WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND GISU = @S_GISU AND ACCT_CD = @V_ACCT_CD --타계정입고(AMT5) SELECT @V_AMT5 = ISNULL(SUM (ISNULL(ACCT_AM, 0)) , 0 ) FROM ADOCUD D WHERE D.CO_CD=@S_CO_CD AND D.DIV_CD = @S_DIV_CD AND D.DRCR_FG IN ('1','3') AND D.CT_DEAL = '1' AND D.ACCT_CD = @V_ACCT_CD AND LEFT(D.FILL_DT, 6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) --타계정출고(AMT6) SELECT @V_AMT6 = ISNULL(SUM (ISNULL(ACCT_AM, 0)) , 0 ) FROM ADOCUD D WHERE D.CO_CD=@S_CO_CD AND D.DIV_CD = @S_DIV_CD AND D.DRCR_FG IN ('2','4') AND D.CT_DEAL = '2' AND D.ACCT_CD = @V_ACCT_CD AND LEFT(D.FILL_DT, 6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) --기말재고액(AMT8) SET @V_AMT8 = @V_AMT0 + @V_AMT5 - @V_AMT6 UPDATE APREV_D SET INIT_AM = @V_AMT0, AIN_AM = @V_AMT5, AOUT_AM = @V_AMT6, STOC_AM = @V_AMT8 WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND ACCT_CD = @V_ACCT_CD AND GISU = @S_GISU+1 AND ISU_SQ = @O_ISU_SQ END END END FETCH SBU_TERMC INTO @V_ISU_DT,@V_ISU_SQ,@V_LN_SQ END CLOSE SBU_TERMC DEALLOCATE SBU_TERMC END */ IF (@V_SUB_DISP = '10' ) --연동항목이 외화자산 이면 BEGIN DECLARE SBU_AFOR CURSOR FOR SELECT K.TR_CD, T.TR_NM, K.CT_DEAL, K.AM_1, K.CM_1 FROM ( SELECT C.ACCT_CD,C.CO_CD, C.TR_CD, C.CT_DEAL, SUM(C.AM_1) AS AM_1,SUM(C.CM_1) AS CM_1 FROM (SELECT A.ACCT_CD,A.CO_CD, A.TR_CD, A.CT_DEAL, isnull(CASE WHEN B.DRCR_FG = '1' AND A.DRCR_FG IN ('1', '3') THEN A.ACCT_AM WHEN B.DRCR_FG = '2' AND A.DRCR_FG IN ('2', '4') THEN A.ACCT_AM WHEN B.DRCR_FG = '1' AND A.DRCR_FG IN ('2', '4') THEN A.ACCT_AM*-1 WHEN B.DRCR_FG = '2' AND A.DRCR_FG IN ('1', '3') THEN A.ACCT_AM*-1 END,0) AS AM_1, isnull(CASE WHEN B.DRCR_FG = '1' AND A.DRCR_FG IN ('1', '3') THEN A.CT_AM WHEN B.DRCR_FG = '2' AND A.DRCR_FG IN ('2', '4') THEN A.CT_AM WHEN B.DRCR_FG = '1' AND A.DRCR_FG IN ('2', '4') THEN A.CT_AM*-1 WHEN B.DRCR_FG = '2' AND A.DRCR_FG IN ('1', '3') THEN A.CT_AM*-1 END,0) AS CM_1 FROM APREV A JOIN SACCT B ON A.CO_CD=B.CO_CD AND A.ACCT_CD=B.ACCT_CD AND B.SUB_DISP='10' WHERE A.CO_CD = @S_CO_CD AND A.ACCT_CD=@V_ACCT_CD AND A.DIV_CD =@S_DIV_CD AND A.GISU = @S_GISU UNION ALL SELECT A.ACCT_CD,A.CO_CD, A.TR_CD, A.CT_DEAL, isnull(CASE WHEN B.DRCR_FG = '1' AND A.DRCR_FG IN ('1', '3') THEN A.ACCT_AM WHEN B.DRCR_FG = '2' AND A.DRCR_FG IN ('2', '4') THEN A.ACCT_AM WHEN B.DRCR_FG = '1' AND A.DRCR_FG IN ('2', '4') THEN A.ACCT_AM*-1 WHEN B.DRCR_FG = '2' AND A.DRCR_FG IN ('1', '3') THEN A.ACCT_AM*-1 END,0) AS AM_1, isnull(CASE WHEN B.DRCR_FG = '1' AND A.DRCR_FG IN ('1', '3') THEN A.CT_AM WHEN B.DRCR_FG = '2' AND A.DRCR_FG IN ('2', '4') THEN A.CT_AM WHEN B.DRCR_FG = '1' AND A.DRCR_FG IN ('2', '4') THEN A.CT_AM*-1 WHEN B.DRCR_FG = '2' AND A.DRCR_FG IN ('1', '3') THEN A.CT_AM*-1 END,0) AS CM_1 FROM ADOCUD A JOIN SACCT B ON A.CO_CD=B.CO_CD AND A.ACCT_CD=B.ACCT_CD AND B.SUB_DISP='10' WHERE A.CO_CD = @S_CO_CD AND A.ACCT_CD=@V_ACCT_CD AND A.DIV_CD =@S_DIV_CD AND A.FILL_DT BETWEEN @FRDT AND @TODT ) C GROUP BY C.ACCT_CD,C.CO_CD, C.TR_CD, C.CT_DEAL ) K LEFT OUTER JOIN STRADE T ON K.CO_CD = T.CO_CD AND K.TR_CD = T.TR_CD WHERE K.AM_1 <> 0 OR K.CM_1 <> 0 OPEN SBU_AFOR FETCH SBU_AFOR INTO @V_TR_CD, @V_TR_NM,@V_CT_DEAL,@V_ACCT_AM,@V_CT_AM WHILE (@@FETCH_STATUS = 0) BEGIN SELECT @V_FILL_NB=( SELECT ISNULL(MAX(ISU_SQ),0)+1 FROM APREV WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND GISU = @S_GISU+1 AND ACCT_CD =@V_ACCT_CD ) IF (@V_FILL_NB>0) BEGIN INSERT APREV ( CO_CD, GISU, DIV_CD, ACCT_CD, ISU_SQ, DEPT_CD, EMP_CD, DRCR_FG, FILL_DT, FILL_NB, RMK_NB, RMK_DC, ACCT_AM , TRCD_TY, TRNM_TY, DEPTCD_TY, PJTCD_TY, CTNB_TY, FRDT_TY, TODT_TY, QT_TY, AM_TY, RT_TY, DEAL_TY , USER1_TY, USER2_TY, TR_CD, TR_NM ,CT_AM , CT_DEAL ,FR_DT, TO_DT) SELECT @S_CO_CD,@S_GISU+1,@S_DIV_CD,@V_ACCT_CD,@V_FILL_NB,@S_DEPT_CD,@S_EMP_CD, (CASE WHEN A.DRCR_FG='1' THEN '3' ELSE '4' END) AS DRCR_FG, @START,@V_FILL_NB,0,CASE WHEN @LANG_KIND = 'KOR' THEN '마감이월' ELSE 'Closing C/F' END,@V_ACCT_AM, A.TRCD_TY, A.TRNM_TY, A.DEPTCD_TY, A.PJTCD_TY, A.CTNB_TY, A.FRDT_TY, A.TODT_TY, A.QT_TY, A.AM_TY, A.RT_TY, A.DEAL_TY ,A.USER1_TY, A.USER2_TY,@V_TR_CD,@V_TR_NM,@V_CT_AM ,@V_CT_DEAL, '00000000', '00000000' FROM SACCT A WHERE A.CO_CD=@S_CO_CD AND A.ACCT_CD=@V_ACCT_CD -- DETAIL INSERT IF ( @V_ACCT_CD >= '14600' AND @V_ACCT_CD <= '16399' AND @V_ACCT_FG = '2' ) BEGIN SELECT @O_ISU_SQ = ( SELECT ISNULL(MAX(ISU_SQ), 0)+1 FROM APREV_D WHERE CO_CD = @S_CO_CD AND DIV_CD = @S_DIV_CD AND GISU = @S_GISU+1 AND ACCT_CD = @V_ACCT_CD ) IF ( @O_ISU_SQ > 0 ) BEGIN INSERT INTO APREV_D ( CO_CD, GISU, DIV_CD, ACCT_CD, ISU_SQ ) VALUES ( @S_CO_CD, @S_GISU+1, @S_DIV_CD, @V_ACCT_CD, @O_ISU_SQ ) --update 항목 집계 -- 기초재고액(AMT0) SELECT @V_AMT0 = ISNULL(ACCT_AM, 0) FROM APREV WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND GISU = @S_GISU AND ACCT_CD = @V_ACCT_CD --타계정입고(AMT5) SELECT @V_AMT5 = ISNULL(SUM (ISNULL(ACCT_AM, 0)) , 0 ) FROM ADOCUD D WHERE D.CO_CD=@S_CO_CD AND D.DIV_CD = @S_DIV_CD AND D.DRCR_FG IN ('1','3') AND D.CT_DEAL = '1' AND D.ACCT_CD = @V_ACCT_CD AND LEFT(D.FILL_DT, 6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) --타계정출고(AMT6) SELECT @V_AMT6 = ISNULL(SUM (ISNULL(ACCT_AM, 0)) , 0 ) FROM ADOCUD D WHERE D.CO_CD=@S_CO_CD AND D.DIV_CD = @S_DIV_CD AND D.DRCR_FG IN ('2','4') AND D.CT_DEAL = '2' AND D.ACCT_CD = @V_ACCT_CD AND LEFT(D.FILL_DT, 6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) --기말재고액(AMT8) SET @V_AMT8 = @V_AMT0 + @V_AMT5 - @V_AMT6 UPDATE APREV_D SET INIT_AM = @V_AMT0, AIN_AM = @V_AMT5, AOUT_AM = @V_AMT6, STOC_AM = @V_AMT8 WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND ACCT_CD = @V_ACCT_CD AND GISU = @S_GISU+1 AND ISU_SQ = @O_ISU_SQ END END END FETCH SBU_AFOR INTO @V_TR_CD, @V_TR_NM,@V_CT_DEAL,@V_ACCT_AM,@V_CT_AM END CLOSE SBU_AFOR DEALLOCATE SBU_AFOR END --################################################################### --연동항목(x) and 이월항목만 setting된 계정의 마감이월 과정 --################################################################### -- < 1. 변수 assign 시작 > ======================================================================= -- ★ 첫번째 이월항목(V_GR_CD)용 변수 -- : @v_Sel , @v_NULL, @v_Group, @v_Into, @v_Update, @v_Set1 -- ★ 두번째 이월항목(V_GR_CD1)용 변수 -- : @v_Sel1 , @v_NULL1, @v_Group1, @v_Into1, @v_Update1, @v_Set2 -- ★ 세번째 이월항목(V_GR_CD2)용 변수 -- : @v_Sel2 , @v_NULL2, @v_Group2, @v_Into2, @v_Update2, @v_Set3 --2005.02.14 by 제희경 ('09'기간비용일때는 이월항목으로만..) --IF (@V_SUB_DISP NOT IN ( '04','05','06','07','08','09','10') AND @V_ACCT_CD < '40000') IF (@V_SUB_DISP NOT IN ( '04','05','06','07','08','10') AND @V_ACCT_CD < '40000') BEGIN SET @v_Sel = NULL SET @v_NULL = NULL SET @v_Group = NULL SET @v_Sel1 = NULL SET @v_NULL1 = NULL SET @v_Group1 = NULL SET @v_Sel2 = NULL SET @v_NULL2 = NULL SET @v_Group2 = NULL -- 첫번째 이월항목 ============================ IF (@V_GR_CD IN ('A','F','H','I','J') ) BEGIN SET @v_Sel = 'D.TR_CD,T.TR_NM' SET @v_NULL = 'ISNULL(TR_CD,'''') TR_CD' --[SYBASE]일때는 GROUP BY를 이렇게 해줘야 에러 안남. --SET @v_Group = 'ISNULL(TR_CD,'''')' SET @v_Group = 'TR_CD' SET @v_Into = '@V_TR_CD, @V_TR_NM' SET @v_Update = 'TR_CD, TR_NM' SET @v_Set1 = 'TR_CD' END ELSE BEGIN SET @v_Into = '@V_TR_CD' END IF ( @V_GR_CD = 'C' ) BEGIN SET @v_Sel = 'D.CT_DEPT' SET @v_NULL = 'ISNULL(CT_DEPT,'''') CT_DEPT' --SET @v_Group = 'ISNULL(CT_DEPT,'''')' SET @v_Group = 'CT_DEPT' SET @v_Update = 'CT_DEPT' SET @v_Set1 = 'CT_DEPT' END IF ( @V_GR_CD = 'D' ) BEGIN SET @v_Sel = 'D.PJT_CD' SET @v_NULL = 'ISNULL(PJT_CD,'''') PJT_CD' --SET @v_Group = 'ISNULL(PJT_CD,'''')' SET @v_Group = 'PJT_CD' SET @v_Update = 'PJT_CD' SET @v_Set1 = 'PJT_CD' END IF ( @V_GR_CD = 'E' ) BEGIN SET @v_Sel = 'D.CT_NB' SET @v_NULL = 'ISNULL(CT_NB,'''') CT_NB' --SET @v_Group = 'ISNULL(CT_NB,'''')' SET @v_Group = 'CT_NB' SET @v_Update = 'CT_NB' SET @v_Set1 = 'CT_NB' END IF ( @V_GR_CD = 'K' ) BEGIN SET @v_Sel = 'D.CT_DEAL' SET @v_NULL = 'ISNULL(CT_DEAL,'''') CT_DEAL' --SET @v_Group = 'ISNULL(CT_DEAL,'''')' SET @v_Group = 'CT_DEAL' SET @v_Update = 'CT_DEAL' SET @v_Set1 = 'CT_DEAL' END IF ( @V_GR_CD = 'L' ) BEGIN SET @v_Sel = 'D.CT_USER1' SET @v_NULL = 'ISNULL(CT_USER1,'''') CT_USER1' --SET @v_Group = 'ISNULL(CT_USER1,'''')' SET @v_Group = 'CT_USER1' SET @v_Update = 'CT_USER1' SET @v_Set1 = 'CT_USER1' END IF ( @V_GR_CD = 'M' ) BEGIN SET @v_Sel = 'D.CT_USER2' SET @v_NULL = 'ISNULL(CT_USER2,'''') CT_USER2' --SET @v_Group = 'ISNULL(CT_USER2,'''')' SET @v_Group = 'CT_USER2' SET @v_Update = 'CT_USER2' SET @v_Set1 = 'CT_USER2' END --두번째 이월항목 ============================ IF (@V_GR_CD1 IN ('A','F','H','I','J') ) BEGIN SET @v_Sel1 = 'D.TR_CD,T.TR_NM' SET @v_NULL1 = 'ISNULL(TR_CD,'''') TR_CD' --SET @v_Group1 = 'ISNULL(TR_CD,'''')' SET @v_Group1 = 'TR_CD' SET @v_Into1 = '@V_TR_CD1, @V_TR_NM' SET @v_Update1 = 'TR_CD, TR_NM' SET @v_Set2 = 'TR_CD' END ELSE BEGIN SET @v_Into1 = '@V_TR_CD1' END IF ( @V_GR_CD1 = 'C' ) BEGIN SET @v_Sel1 = 'D.CT_DEPT' SET @v_NULL1 = 'ISNULL(CT_DEPT,'''') CT_DEPT' --SET @v_Group1 = 'ISNULL(CT_DEPT,'''')' SET @v_Group1 = 'CT_DEPT' SET @v_Update1 = 'CT_DEPT' SET @v_Set2 = 'CT_DEPT' END IF ( @V_GR_CD1 = 'D' ) BEGIN SET @v_Sel1 = 'D.PJT_CD' SET @v_NULL1 = 'ISNULL(PJT_CD,'''') PJT_CD' --SET @v_Group1 = 'ISNULL(PJT_CD,'''')' SET @v_Group1 = 'PJT_CD' SET @v_Update1 = 'PJT_CD' SET @v_Set2 = 'PJT_CD' END IF ( @V_GR_CD1 = 'E' ) BEGIN SET @v_Sel1 = 'D.CT_NB' SET @v_NULL1 = 'ISNULL(CT_NB,'''') CT_NB' --SET @v_Group1 = 'ISNULL(CT_NB,'''')' SET @v_Group1 = 'CT_NB' SET @v_Update1 = 'CT_NB' SET @v_Set2 = 'CT_NB' END IF ( @V_GR_CD1 = 'K' ) BEGIN SET @v_Sel1 = 'D.CT_DEAL' SET @v_NULL1 = 'ISNULL(CT_DEAL,'''') CT_DEAL' --SET @v_Group1 = 'ISNULL(CT_DEAL,'''')' SET @v_Group1 = 'CT_DEAL' SET @v_Update1 = 'CT_DEAL' SET @v_Set2 = 'CT_DEAL' END IF ( @V_GR_CD1 = 'L' ) BEGIN SET @v_Sel1 = 'D.CT_USER1' SET @v_NULL1 = 'ISNULL(CT_USER1,'''') CT_USER1' --SET @v_Group1 = 'ISNULL(CT_USER1,'''')' SET @v_Group1 = 'CT_USER1' SET @v_Update1 = 'CT_USER1' SET @v_Set2 = 'CT_USER1' END IF ( @V_GR_CD1 = 'M' ) BEGIN SET @v_Sel1 = 'D.CT_USER2' SET @v_NULL1 = 'ISNULL(CT_USER2,'''') CT_USER2' --SET @v_Group1 = 'ISNULL(CT_USER2,'''')' SET @v_Group1 = 'CT_USER2' SET @v_Update1 = 'CT_USER2' SET @v_Set2 = 'CT_USER2' END --세번째 이월항목 ============================ IF (@V_GR_CD2 IN ('A','F','H','I','J') ) BEGIN SET @v_Sel2 = 'D.TR_CD,T.TR_NM' SET @v_NULL2 = 'ISNULL(TR_CD,'''') TR_CD' --SET @v_Group2 = 'ISNULL(TR_CD,'''')' SET @v_Group2 = 'TR_CD' SET @v_Into2 = '@V_TR_CD2, @V_TR_NM' SET @v_Update2 = 'TR_CD, TR_NM' SET @v_Set3 = 'TR_CD' END ELSE BEGIN SET @v_Into2 = '@V_TR_CD2' END IF ( @V_GR_CD2 = 'C' ) BEGIN SET @v_Sel2 = 'D.CT_DEPT' SET @v_NULL2 = 'ISNULL(CT_DEPT,'''') CT_DEPT' --SET @v_Group2 = 'ISNULL(CT_DEPT,'''')' SET @v_Group2 = 'CT_DEPT' SET @v_Update2 = 'CT_DEPT' SET @v_Set3 = 'CT_DEPT' END IF ( @V_GR_CD2 = 'D' ) BEGIN SET @v_Sel2 = 'D.PJT_CD' SET @v_NULL2 = 'ISNULL(PJT_CD,'''') PJT_CD' --SET @v_Group2 = 'ISNULL(PJT_CD,'''')' SET @v_Group2 = 'PJT_CD' SET @v_Update2 = 'PJT_CD' SET @v_Set3 = 'PJT_CD' END IF ( @V_GR_CD2 = 'E' ) BEGIN SET @v_Sel2 = 'D.CT_NB' SET @v_NULL2 = 'ISNULL(CT_NB,'''') CT_NB' --SET @v_Group2 = 'ISNULL(CT_NB,'''')' SET @v_Group2 = 'CT_NB' SET @v_Update2 = 'CT_NB' SET @v_Set3 = 'CT_NB' END IF ( @V_GR_CD2 = 'K' ) BEGIN SET @v_Sel2 = 'D.CT_DEAL' SET @v_NULL2 = 'ISNULL(CT_DEAL,'''') CT_DEAL' --SET @v_Group2 = 'ISNULL(CT_DEAL,'''')' SET @v_Group2 = 'CT_DEAL' SET @v_Update2 = 'CT_DEAL' SET @v_Set3 = 'CT_DEAL' END IF ( @V_GR_CD2 = 'L' ) BEGIN SET @v_Sel2 = 'D.CT_USER1' SET @v_NULL2 = 'ISNULL(CT_USER1,'''') CT_USER1' --SET @v_Group2 = 'ISNULL(CT_USER1,'''')' SET @v_Group2 = 'CT_USER1' SET @v_Update2 = 'CT_USER1' SET @v_Set3 = 'CT_USER1' END IF ( @V_GR_CD2 = 'M' ) BEGIN SET @v_Sel2 = 'D.CT_USER2' SET @v_NULL2 = 'ISNULL(CT_USER2,'''') CT_USER2' --SET @v_Group2 = 'ISNULL(CT_USER2,'''')' SET @v_Group2 = 'CT_USER2' SET @v_Update2 = 'CT_USER2' SET @v_Set3 = 'CT_USER2' END -- 변수 assign 끝======================================================================== -- < 2. 쿼리문 assign 시작 > =================================================================== -- ★ @v_strSQL : 이월항목으로 집계(group by)한 마감 DATA 생성 쿼리 -- ★ @v_strUPDATE : 마감이월 테이블 APREV에 공통부분(기본 사항) INSERT후에 각 마감이월별 값을 UPDATE하는 쿼리 -- ★ @v_strFETCH : CURSOR 실행 FETCH문 -- 이월항목 1개 =========== IF ( @v_Sel IS NOT NULL AND @v_Sel1 IS NULL AND @v_Sel2 IS NULL ) -- V_GR_CD NOT NULL 1개 BEGIN IF ( @V_GR_CD IN ('A','F','H','I','J') ) BEGIN --[SYBASE] D.DRCR_FG와 하위쿼리의 DRCR_FG와의 중복 필드명으로인해 DATA 이상해짐. D.DRCR_FG_V로 변경.(전체 소스의 유사문장 전부 수정) SET @v_strSQL = 'SELECT ' + @v_Sel + ', D.ACCT_AM, D.DRCR_FG_V ' + ' FROM ( ' + 'SELECT CO_CD, DIV_CD, ACCT_CD, DRCR_FG_V = (CASE WHEN FG = ''1'' THEN ''3'' ELSE ''4'' END ), ' + @v_NULL + ', ' + 'ACCT_AM= SUM( CASE ' + 'WHEN FG=''1'' AND DRCR_FG IN( ''1'',''3'') THEN ACCT_AM ' + 'WHEN FG=''2'' AND DRCR_FG IN( ''2'',''4'') THEN ACCT_AM ' + 'WHEN FG=''1'' AND DRCR_FG IN( ''2'',''4'') THEN ACCT_AM * -1 ' + 'WHEN FG=''2'' AND DRCR_FG IN( ''1'',''3'') THEN ACCT_AM * -1 END ) ' + 'FROM VA_APTRS ' + 'WHERE CO_CD= ''' + @S_CO_CD + ''' AND DIV_CD = ''' + @S_DIV_CD + ''' AND FILL_YM BETWEEN LEFT(''' + @FRDT + ''', 6) AND LEFT( ''' + @TODT + ''', 6) AND ACCT_CD = ''' + @V_ACCT_CD + ''' GROUP BY CO_CD, DIV_CD, ACCT_CD, ' + @v_Group + ', FG ' + ' ) D LEFT OUTER JOIN STRADE T ' + 'ON D.CO_CD=T.CO_CD AND D.TR_CD = T.TR_CD ' + 'WHERE D.ACCT_AM<>0 ' SET @v_strUPDATE = 'UPDATE APREV SET TR_CD = @V_TR_CD, TR_NM = @V_TR_NM ' + ' WHERE CO_CD = ''' + @S_CO_CD + ''' AND GISU = @V_NEXTGISU AND DIV_CD = ''' + @S_DIV_CD + ''' AND ACCT_CD = ''' + @V_ACCT_CD + ''' AND ISU_SQ = @V_FILL_NB ' END ELSE BEGIN SET @v_strSQL = 'SELECT ' + @v_Sel + ', D.ACCT_AM, D.DRCR_FG_V ' + ' FROM ( ' + 'SELECT CO_CD, DIV_CD, ACCT_CD, DRCR_FG_V = (CASE WHEN FG = ''1'' THEN ''3'' ELSE ''4'' END ), ' + @v_NULL + ', ' + 'ACCT_AM= SUM( CASE ' + 'WHEN FG = ''1'' AND DRCR_FG IN( ''1'',''3'') THEN ACCT_AM ' + 'WHEN FG = ''2'' AND DRCR_FG IN( ''2'',''4'') THEN ACCT_AM ' + 'WHEN FG = ''1'' AND DRCR_FG IN( ''2'',''4'') THEN ACCT_AM * -1 ' + 'WHEN FG = ''2'' AND DRCR_FG IN( ''1'',''3'') THEN ACCT_AM * -1 END ) ' + 'FROM VA_APTRS ' + 'WHERE CO_CD= ''' + @S_CO_CD + ''' AND DIV_CD = ''' + @S_DIV_CD + ''' AND FILL_YM BETWEEN LEFT( ''' + @FRDT + ''', 6) AND LEFT( ''' + @TODT + ''', 6) AND ACCT_CD = ''' + @V_ACCT_CD + ''' GROUP BY CO_CD, DIV_CD, ACCT_CD, ' + @v_Group + ', FG ' + ' ) D ' + 'WHERE D.ACCT_AM<>0 ' SET @v_strUPDATE = 'UPDATE APREV SET '+ @v_Set1 + ' = @V_TR_CD ' + ' WHERE CO_CD = ''' + @S_CO_CD + ''' AND GISU = @V_NEXTGISU AND DIV_CD = ''' + @S_DIV_CD + ''' AND ACCT_CD = ''' + @V_ACCT_CD + ''' AND ISU_SQ = @V_FILL_NB ' END SET @v_strFETCH = 'FETCH SBU_TR INTO '+ @v_Into + ' ,@V_ACCT_AM, @V_DRCR_FG ' END ----------------------------------------------------------------------------------------- IF ( @v_Sel IS NULL AND @v_Sel1 IS NOT NULL AND @v_Sel2 IS NULL ) --V_GR_CD1 NOT NULL 1개 BEGIN IF ( @V_GR_CD1 IN ('A','F','H','I','J') ) BEGIN SET @v_strSQL = 'SELECT ' + @v_Sel1 + ', D.ACCT_AM, D.DRCR_FG_V ' + ' FROM ( ' + 'SELECT CO_CD, DIV_CD, ACCT_CD, DRCR_FG_V =(CASE WHEN FG=''1'' THEN ''3'' ELSE ''4'' END ), ' + @v_NULL1 + ', ' + 'ACCT_AM= SUM( CASE ' + 'WHEN FG=''1'' AND DRCR_FG IN( ''1'',''3'') THEN ACCT_AM ' + 'WHEN FG=''2'' AND DRCR_FG IN( ''2'',''4'') THEN ACCT_AM ' + 'WHEN FG=''1'' AND DRCR_FG IN( ''2'',''4'') THEN ACCT_AM * -1 ' + 'WHEN FG=''2'' AND DRCR_FG IN( ''1'',''3'') THEN ACCT_AM * -1 END ) ' + 'FROM VA_APTRS ' + 'WHERE CO_CD= ''' + @S_CO_CD + ''' AND DIV_CD = ''' + @S_DIV_CD + ''' AND FILL_YM BETWEEN LEFT( ''' +@FRDT + ''',6) AND LEFT( ''' +@TODT + ''',6) AND ACCT_CD = ''' + @V_ACCT_CD + ''' GROUP BY CO_CD, DIV_CD, ACCT_CD, ' + @v_Group1 + ', FG ' + ' ) D LEFT OUTER JOIN STRADE T ' + 'ON D.CO_CD=T.CO_CD AND D.TR_CD = T.TR_CD ' + 'WHERE D.ACCT_AM<>0 ' SET @v_strUPDATE = 'UPDATE APREV SET '+ @v_Set2 + ' = @V_TR_CD1 , TR_NM = @V_TR_NM ' + ' WHERE CO_CD = ''' + @S_CO_CD + ''' AND GISU = @V_NEXTGISU AND DIV_CD = ''' + @S_DIV_CD + ''' AND ACCT_CD = ''' + @V_ACCT_CD + ''' AND ISU_SQ = @V_FILL_NB ' END ELSE BEGIN SET @v_strSQL = 'SELECT ' + @v_Sel1 + ', D.ACCT_AM, D.DRCR_FG_V ' + ' FROM ( ' + 'SELECT CO_CD, DIV_CD, ACCT_CD, DRCR_FG_V =(CASE WHEN FG=''1'' THEN ''3'' ELSE ''4'' END ), ' + @v_NULL1 + ', ' + 'ACCT_AM= SUM( CASE ' + 'WHEN FG=''1'' AND DRCR_FG IN( ''1'',''3'') THEN ACCT_AM ' + 'WHEN FG=''2'' AND DRCR_FG IN( ''2'',''4'') THEN ACCT_AM ' + 'WHEN FG=''1'' AND DRCR_FG IN( ''2'',''4'') THEN ACCT_AM * -1 ' + 'WHEN FG=''2'' AND DRCR_FG IN( ''1'',''3'') THEN ACCT_AM * -1 END ) ' + 'FROM VA_APTRS ' + 'WHERE CO_CD= ''' + @S_CO_CD + ''' AND DIV_CD = ''' + @S_DIV_CD + ''' AND FILL_YM BETWEEN LEFT( ''' + @FRDT + ''',6) AND LEFT( ''' + @TODT + ''',6) AND ACCT_CD = ''' + @V_ACCT_CD + ''' GROUP BY CO_CD, DIV_CD, ACCT_CD, ' + @v_Group1 + ', FG ' + ' ) D ' + 'WHERE D.ACCT_AM<>0 ' SET @v_strUPDATE = 'UPDATE APREV SET '+ @v_Set2 + ' = @V_TR_CD1' + ' WHERE CO_CD = ''' + @S_CO_CD + ''' AND GISU = @V_NEXTGISU AND DIV_CD = ''' + @S_DIV_CD + ''' AND ACCT_CD = ''' + @V_ACCT_CD + ''' AND ISU_SQ = @V_FILL_NB ' END SET @v_strFETCH = 'FETCH SBU_TR INTO '+ @v_Into1 + ' ,@V_ACCT_AM,@V_DRCR_FG ' END ----------------------------------------------------------------------------------------- IF ( @v_Sel IS NULL AND @v_Sel1 IS NULL AND @v_Sel2 IS NOT NULL ) --V_GR_CD2 NOT NULL 1개 BEGIN IF ( @V_GR_CD2 IN ('A','F','H','I','J') ) BEGIN SET @v_strSQL = 'SELECT ' + @v_Sel2 + ', D.ACCT_AM, D.DRCR_FG_V ' + ' FROM ( ' + 'SELECT CO_CD, DIV_CD, ACCT_CD, DRCR_FG_V =(CASE WHEN FG=''1'' THEN ''3'' ELSE ''4'' END ), ' + @v_NULL2 + ', ' + 'ACCT_AM= SUM( CASE ' + 'WHEN FG=''1'' AND DRCR_FG IN( ''1'',''3'') THEN ACCT_AM ' + 'WHEN FG=''2'' AND DRCR_FG IN( ''2'',''4'') THEN ACCT_AM ' + 'WHEN FG=''1'' AND DRCR_FG IN( ''2'',''4'') THEN ACCT_AM * -1 ' + 'WHEN FG=''2'' AND DRCR_FG IN( ''1'',''3'') THEN ACCT_AM * -1 END ) ' + 'FROM VA_APTRS ' + 'WHERE CO_CD= ''' + @S_CO_CD + ''' AND DIV_CD = ''' + @S_DIV_CD + ''' AND FILL_YM BETWEEN LEFT( ''' +@FRDT + ''',6) AND LEFT( ''' +@TODT + ''' ,6) AND ACCT_CD = ''' + @V_ACCT_CD + ''' GROUP BY CO_CD, DIV_CD, ACCT_CD, ' + @v_Group2 + ', FG ' + ' ) D LEFT OUTER JOIN STRADE T ' + 'ON D.CO_CD=T.CO_CD AND D.TR_CD = T.TR_CD ' + 'WHERE D.ACCT_AM<>0 ' SET @v_strUPDATE = 'UPDATE APREV SET '+ @v_Set3 + ' = @V_TR_CD2 , TR_NM = @V_TR_NM ' + ' WHERE CO_CD = ''' + @S_CO_CD + ''' AND GISU = @V_NEXTGISU AND DIV_CD = ''' + @S_DIV_CD + ''' AND ACCT_CD = ''' + @V_ACCT_CD + ''' AND ISU_SQ = @V_FILL_NB ' END ELSE BEGIN SET @v_strSQL = 'SELECT ' + @v_Sel2 + ', D.ACCT_AM, D.DRCR_FG_V ' + ' FROM ( ' + 'SELECT CO_CD, DIV_CD, ACCT_CD, DRCR_FG_V =(CASE WHEN FG=''1'' THEN ''3'' ELSE ''4'' END ), ' + @v_NULL2 + ', ' + 'ACCT_AM= SUM( CASE ' + 'WHEN FG=''1'' AND DRCR_FG IN( ''1'',''3'') THEN ACCT_AM ' + 'WHEN FG=''2'' AND DRCR_FG IN( ''2'',''4'') THEN ACCT_AM ' + 'WHEN FG=''1'' AND DRCR_FG IN( ''2'',''4'') THEN ACCT_AM * -1 ' + 'WHEN FG=''2'' AND DRCR_FG IN( ''1'',''3'') THEN ACCT_AM * -1 END ) ' + 'FROM VA_APTRS ' + 'WHERE CO_CD= ''' + @S_CO_CD + ''' AND DIV_CD = ''' + @S_DIV_CD + ''' AND FILL_YM BETWEEN LEFT( ''' + @FRDT + ''',6) AND LEFT( ''' + @TODT + ''' ,6) AND ACCT_CD = ''' + @V_ACCT_CD + ''' GROUP BY CO_CD, DIV_CD, ACCT_CD, ' + @v_Group2 + ', FG ' + ' ) D ' + 'WHERE D.ACCT_AM<>0 ' SET @v_strUPDATE = 'UPDATE APREV SET '+ @v_Set3 + ' = @V_TR_CD2 ' + ' WHERE CO_CD = ''' + @S_CO_CD + ''' AND GISU = @V_NEXTGISU AND DIV_CD = ''' + @S_DIV_CD + ''' AND ACCT_CD = ''' + @V_ACCT_CD + ''' AND ISU_SQ = @V_FILL_NB ' END SET @v_strFETCH = 'FETCH SBU_TR INTO '+ @v_Into2 + ' , @V_ACCT_AM, @V_DRCR_FG ' END -- 이월항목 2개 ==================== IF ( @v_Sel IS NOT NULL AND @v_Sel1 IS NOT NULL AND @v_Sel2 IS NULL ) -- V_GR_CD, V_GR_CD1 NOT NULL 2개 BEGIN IF ( @V_GR_CD IN ('A','F','H','I','J') OR @V_GR_CD1 IN ('A','F','H','I','J') OR @V_GR_CD2 IN ('A','F','H','I','J') ) BEGIN SET @v_strSQL = 'SELECT ' + @v_Sel + ', ' + @v_Sel1 + ', D.ACCT_AM, D.DRCR_FG_V ' + ' FROM ( ' + 'SELECT CO_CD, DIV_CD, ACCT_CD, DRCR_FG_V =(CASE WHEN FG=''1'' THEN ''3'' ELSE ''4'' END ), ' + @v_NULL + ', ' + @v_NULL1 + ', ' + 'ACCT_AM= SUM( CASE ' + 'WHEN FG=''1'' AND DRCR_FG IN( ''1'',''3'') THEN ACCT_AM ' + 'WHEN FG=''2'' AND DRCR_FG IN( ''2'',''4'') THEN ACCT_AM ' + 'WHEN FG=''1'' AND DRCR_FG IN( ''2'',''4'') THEN ACCT_AM * -1 ' + 'WHEN FG=''2'' AND DRCR_FG IN( ''1'',''3'') THEN ACCT_AM * -1 END ) ' + 'FROM VA_APTRS ' + 'WHERE CO_CD= ''' + @S_CO_CD + ''' AND DIV_CD = ''' + @S_DIV_CD + ''' AND FILL_YM BETWEEN LEFT( ''' + @FRDT + ''' ,6) AND LEFT( ''' + @TODT + ''' ,6) AND ACCT_CD = ''' + @V_ACCT_CD + ''' GROUP BY CO_CD, DIV_CD, ACCT_CD, ' + @v_Group + ', ' + @v_Group1 + ', FG ' + ' ) D LEFT OUTER JOIN STRADE T ' + 'ON D.CO_CD=T.CO_CD AND D.TR_CD = T.TR_CD ' + 'WHERE D.ACCT_AM<>0 ' END ELSE BEGIN SET @v_strSQL = 'SELECT ' + @v_Sel + ', ' + @v_Sel1 + ', D.ACCT_AM, D.DRCR_FG_V ' + ' FROM ( ' + 'SELECT CO_CD, DIV_CD, ACCT_CD, DRCR_FG_V =(CASE WHEN FG=''1'' THEN ''3'' ELSE ''4'' END ), ' + @v_NULL + ', ' + @v_NULL1 + ', ' + 'ACCT_AM= SUM( CASE ' + 'WHEN FG=''1'' AND DRCR_FG IN( ''1'',''3'') THEN ACCT_AM ' + 'WHEN FG=''2'' AND DRCR_FG IN( ''2'',''4'') THEN ACCT_AM ' + 'WHEN FG=''1'' AND DRCR_FG IN( ''2'',''4'') THEN ACCT_AM * -1 ' + 'WHEN FG=''2'' AND DRCR_FG IN( ''1'',''3'') THEN ACCT_AM * -1 END ) ' + 'FROM VA_APTRS ' + 'WHERE CO_CD= ''' + @S_CO_CD + ''' AND DIV_CD = ''' + @S_DIV_CD + ''' AND FILL_YM BETWEEN LEFT( ''' + @FRDT + ''' ,6) AND LEFT( ''' + @TODT + ''' ,6) AND ACCT_CD = ''' + @V_ACCT_CD + ''' GROUP BY CO_CD, DIV_CD, ACCT_CD, ' + @v_Group + ', ' + @v_Group1 + ', FG ' + ' ) D ' + 'WHERE D.ACCT_AM<>0 ' END SET @v_strFETCH = 'FETCH SBU_TR INTO '+ @v_Into + ' ,' + @v_Into1 + ' , @V_ACCT_AM, @V_DRCR_FG ' IF ( @V_GR_CD IN ('A','F','H','I','J') ) BEGIN SET @v_strUPDATE = 'UPDATE APREV SET '+ @v_Set1 + ' = @V_TR_CD , TR_NM = @V_TR_NM, ' + @v_Set2 + ' = @V_TR_CD1 ' + ' WHERE CO_CD = ''' + @S_CO_CD + ''' AND GISU = @V_NEXTGISU AND DIV_CD = ''' + @S_DIV_CD + ''' AND ACCT_CD = ''' + @V_ACCT_CD + ''' AND ISU_SQ = @V_FILL_NB ' END ELSE IF ( @V_GR_CD1 IN ('A','F','H','I','J') ) BEGIN SET @v_strUPDATE = 'UPDATE APREV SET '+ @v_Set2 + ' = @V_TR_CD1 , TR_NM = @V_TR_NM , ' + @v_Set1 + ' = @V_TR_CD ' + ' WHERE CO_CD = ''' + @S_CO_CD + ''' AND GISU = @V_NEXTGISU AND DIV_CD = ''' + @S_DIV_CD + ''' AND ACCT_CD = ''' + @V_ACCT_CD + ''' AND ISU_SQ = @V_FILL_NB ' END -- 2006.03.28 추가 ELSE BEGIN SET @v_strUPDATE = 'UPDATE APREV SET '+ @v_Set1 + ' = @V_TR_CD , ' + @v_Set2 + ' = @V_TR_CD1 ' + ' WHERE CO_CD = ''' + @S_CO_CD + ''' AND GISU = @V_NEXTGISU AND DIV_CD = ''' + @S_DIV_CD + ''' AND ACCT_CD = ''' + @V_ACCT_CD + ''' AND ISU_SQ = @V_FILL_NB ' END END ----------------------------------------------------------------------------------------- IF ( @v_Sel IS NULL AND @v_Sel1 IS NOT NULL AND @v_Sel2 IS NOT NULL ) -- V_GR_CD1, V_GR_CD2 NOT NULL 2개 BEGIN IF ( @V_GR_CD IN('A','F','H','I','J') OR @V_GR_CD1 IN('A','F','H','I','J') OR @V_GR_CD2 IN('A','F','H','I','J') ) BEGIN SET @v_strSQL = 'SELECT ' + @v_Sel1 + ', ' + @v_Sel2 + ', D.ACCT_AM, D.DRCR_FG_V ' + ' FROM ( ' + 'SELECT CO_CD, DIV_CD, ACCT_CD, DRCR_FG_V =(CASE WHEN FG=''1'' THEN ''3'' ELSE ''4'' END ), ' + @v_NULL1 + ', ' + @v_NULL2 + ', ' + 'ACCT_AM= SUM( CASE ' + 'WHEN FG=''1'' AND DRCR_FG IN( ''1'',''3'') THEN ACCT_AM ' + 'WHEN FG=''2'' AND DRCR_FG IN( ''2'',''4'') THEN ACCT_AM ' + 'WHEN FG=''1'' AND DRCR_FG IN( ''2'',''4'') THEN ACCT_AM * -1 ' + 'WHEN FG=''2'' AND DRCR_FG IN( ''1'',''3'') THEN ACCT_AM * -1 END ) ' + 'FROM VA_APTRS ' + 'WHERE CO_CD= ''' + @S_CO_CD + ''' AND DIV_CD = ''' + @S_DIV_CD + ''' AND FILL_YM BETWEEN LEFT( ''' + @FRDT +''' ,6) AND LEFT( ''' + @TODT + ''' ,6) AND ACCT_CD = ''' + @V_ACCT_CD + ''' GROUP BY CO_CD, DIV_CD, ACCT_CD, ' + @v_Group1 + ', ' + @v_Group2 + ', FG ' + ' ) D LEFT OUTER JOIN STRADE T ' + 'ON D.CO_CD=T.CO_CD AND D.TR_CD = T.TR_CD ' + 'WHERE D.ACCT_AM<>0 ' END ELSE BEGIN SET @v_strSQL = 'SELECT ' + @v_Sel1 + ', ' + @v_Sel2 + ', D.ACCT_AM, D.DRCR_FG_V ' + ' FROM ( ' + 'SELECT CO_CD, DIV_CD, ACCT_CD, DRCR_FG_V =(CASE WHEN FG=''1'' THEN ''3'' ELSE ''4'' END ), ' + @v_NULL1 + ', ' + @v_NULL2 + ', ' + 'ACCT_AM= SUM( CASE ' + 'WHEN FG=''1'' AND DRCR_FG IN( ''1'',''3'') THEN ACCT_AM ' + 'WHEN FG=''2'' AND DRCR_FG IN( ''2'',''4'') THEN ACCT_AM ' + 'WHEN FG=''1'' AND DRCR_FG IN( ''2'',''4'') THEN ACCT_AM * -1 ' + 'WHEN FG=''2'' AND DRCR_FG IN( ''1'',''3'') THEN ACCT_AM * -1 END ) ' + 'FROM VA_APTRS ' + 'WHERE CO_CD= ''' + @S_CO_CD + ''' AND DIV_CD = ''' + @S_DIV_CD + ''' AND FILL_YM BETWEEN LEFT( ''' + @FRDT + ''' ,6) AND LEFT( ''' + @TODT + ''' ,6) AND ACCT_CD = ''' + @V_ACCT_CD + ''' GROUP BY CO_CD, DIV_CD, ACCT_CD, ' + @v_Group1 + ', ' + @v_Group2 + ', FG ' + ' ) D ' + 'WHERE D.ACCT_AM<>0 ' END SET @v_strFETCH = 'FETCH SBU_TR INTO '+ @v_Into1 + ' ,' + @v_Into2 + ' , @V_ACCT_AM, @V_DRCR_FG ' IF ( @V_GR_CD1 IN('A','F','H','I','J') ) BEGIN SET @v_strUPDATE = 'UPDATE APREV SET '+ @v_Set2 + ' = @V_TR_CD1 , TR_NM = @V_TR_NM , ' + @v_Set3 + ' = @V_TR_CD2 ' + ' WHERE CO_CD = ''' + @S_CO_CD + ''' AND GISU = @V_NEXTGISU AND DIV_CD = ''' + @S_DIV_CD + ''' AND ACCT_CD = ''' + @V_ACCT_CD + ''' AND ISU_SQ = @V_FILL_NB ' END ELSE IF ( @V_GR_CD2 IN('A','F','H','I','J') ) BEGIN SET @v_strUPDATE = 'UPDATE APREV SET '+ @v_Set3 + ' = @V_TR_CD2 , TR_NM = @V_TR_NM , ' + @v_Set2 + ' = @V_TR_CD1 ' + ' WHERE CO_CD = ''' + @S_CO_CD + ''' AND GISU = @V_NEXTGISU AND DIV_CD = ''' + @S_DIV_CD + ''' AND ACCT_CD = ''' + @V_ACCT_CD + ''' AND ISU_SQ = @V_FILL_NB ' END -- 2006.03.28 추가 ELSE BEGIN SET @v_strUPDATE = 'UPDATE APREV SET '+ @v_Set2 + ' = @V_TR_CD1 , ' + @v_Set3 + ' = @V_TR_CD2 ' + ' WHERE CO_CD = ''' + @S_CO_CD + ''' AND GISU = @V_NEXTGISU AND DIV_CD = ''' + @S_DIV_CD + ''' AND ACCT_CD = ''' + @V_ACCT_CD + ''' AND ISU_SQ = @V_FILL_NB ' END END ----------------------------------------------------------------------------------------- IF ( @v_Sel IS NOT NULL AND @v_Sel1 IS NULL AND @v_Sel2 IS NOT NULL ) -- V_GR_CD, V_GR_CD2 NOT NULL 2개 BEGIN IF ( @V_GR_CD IN('A','F','H','I','J') OR @V_GR_CD1 IN('A','F','H','I','J') OR @V_GR_CD2 IN('A','F','H','I','J') ) BEGIN SET @v_strSQL = 'SELECT ' + @v_Sel + ', ' + @v_Sel2 + ', D.ACCT_AM, D.DRCR_FG_V ' + ' FROM ( ' + 'SELECT CO_CD, DIV_CD, ACCT_CD, DRCR_FG_V =(CASE WHEN FG=''1'' THEN ''3'' ELSE ''4'' END ), ' + @v_NULL + ', ' + @v_NULL2 + ', ' + 'ACCT_AM= SUM( CASE ' + 'WHEN FG=''1'' AND DRCR_FG IN( ''1'',''3'') THEN ACCT_AM ' + 'WHEN FG=''2'' AND DRCR_FG IN( ''2'',''4'') THEN ACCT_AM ' + 'WHEN FG=''1'' AND DRCR_FG IN( ''2'',''4'') THEN ACCT_AM * -1 ' + 'WHEN FG=''2'' AND DRCR_FG IN( ''1'',''3'') THEN ACCT_AM * -1 END ) ' + 'FROM VA_APTRS ' + 'WHERE CO_CD= ''' + @S_CO_CD + ''' AND DIV_CD = ''' + @S_DIV_CD + ''' AND FILL_YM BETWEEN LEFT( ''' +@FRDT + ''' ,6) AND LEFT( ''' + @TODT + ''' ,6) AND ACCT_CD = ''' + @V_ACCT_CD + ''' GROUP BY CO_CD, DIV_CD, ACCT_CD, ' + @v_Group + ', ' + @v_Group2 + ', FG ' + ' ) D LEFT OUTER JOIN STRADE T ' + 'ON D.CO_CD=T.CO_CD AND D.TR_CD = T.TR_CD ' + 'WHERE D.ACCT_AM<>0 ' END ELSE BEGIN SET @v_strSQL = 'SELECT ' + @v_Sel + ', ' + @v_Sel2 + ', D.ACCT_AM, D.DRCR_FG_V ' + ' FROM ( ' + 'SELECT CO_CD, DIV_CD, ACCT_CD, DRCR_FG_V =(CASE WHEN FG=''1'' THEN ''3'' ELSE ''4'' END ), ' + @v_NULL + ', ' + @v_NULL2 + ', ' + 'ACCT_AM= SUM( CASE ' + 'WHEN FG=''1'' AND DRCR_FG IN( ''1'',''3'') THEN ACCT_AM ' + 'WHEN FG=''2'' AND DRCR_FG IN( ''2'',''4'') THEN ACCT_AM ' + 'WHEN FG=''1'' AND DRCR_FG IN( ''2'',''4'') THEN ACCT_AM * -1 ' + 'WHEN FG=''2'' AND DRCR_FG IN( ''1'',''3'') THEN ACCT_AM * -1 END ) ' + 'FROM VA_APTRS ' + 'WHERE CO_CD= ''' + @S_CO_CD + ''' AND DIV_CD = ''' + @S_DIV_CD + ''' AND FILL_YM BETWEEN LEFT( ''' + @FRDT + ''' ,6) AND LEFT( ''' + @TODT + ''' ,6) AND ACCT_CD = ''' + @V_ACCT_CD + ''' GROUP BY CO_CD, DIV_CD, ACCT_CD, ' + @v_Group + ', ' + @v_Group2 + ', FG ' + ' ) D ' + 'WHERE D.ACCT_AM<>0 ' END SET @v_strFETCH = 'FETCH SBU_TR INTO '+ @v_Into + ' ,' + @v_Into2 + ' , @V_ACCT_AM, @V_DRCR_FG ' IF ( @V_GR_CD IN('A','F','H','I','J') ) BEGIN SET @v_strUPDATE = 'UPDATE APREV SET '+ @v_Set1 +' = @V_TR_CD, TR_NM = @V_TR_NM, ' + @v_Set3 + ' = @V_TR_CD2 ' + ' WHERE CO_CD = ''' + @S_CO_CD + ''' AND GISU = @V_NEXTGISU AND DIV_CD = ''' + @S_DIV_CD + ''' AND ACCT_CD = ''' + @V_ACCT_CD + ''' AND ISU_SQ = @V_FILL_NB ' END ELSE IF ( @V_GR_CD2 IN('A','F','H','I','J') ) BEGIN SET @v_strUPDATE = 'UPDATE APREV SET '+ @v_Set3 + ' = @V_TR_CD2 , TR_NM = @V_TR_NM , ' + @v_Set1 + ' = @V_TR_CD ' + ' WHERE CO_CD = ''' + @S_CO_CD + ''' AND GISU = @V_NEXTGISU AND DIV_CD = ''' + @S_DIV_CD + ''' AND ACCT_CD = ''' + @V_ACCT_CD + ''' AND ISU_SQ = @V_FILL_NB ' END -- 2006.03.28 추가 ELSE BEGIN SET @v_strUPDATE = 'UPDATE APREV SET '+ @v_Set1 +' = @V_TR_CD, ' + @v_Set3 + ' = @V_TR_CD2 ' + ' WHERE CO_CD = ''' + @S_CO_CD + ''' AND GISU = @V_NEXTGISU AND DIV_CD = ''' + @S_DIV_CD + ''' AND ACCT_CD = ''' + @V_ACCT_CD + ''' AND ISU_SQ = @V_FILL_NB ' END END -- 이월항목 3개 ==================== IF ( @v_Sel IS NOT NULL AND @v_Sel1 IS NOT NULL AND @v_Sel2 IS NOT NULL ) -- V_GR_CD, V_GR_CD1, V_GR_CD2 NOT NULL 3개 BEGIN IF ( @V_GR_CD IN('A','F','H','I','J') OR @V_GR_CD1 IN('A','F','H','I','J') OR @V_GR_CD2 IN('A','F','H','I','J') ) BEGIN SET @v_strSQL = 'SELECT ' + @v_Sel + ', ' + @v_Sel1 + ', ' + @v_Sel2 + ', D.ACCT_AM, D.DRCR_FG_V ' + ' FROM ( ' + 'SELECT CO_CD, DIV_CD, ACCT_CD, DRCR_FG_V =(CASE WHEN FG=''1'' THEN ''3'' ELSE ''4'' END ), ' + @v_NULL + ', ' + @v_NULL1 + ', ' + @v_NULL2 + ', ' + 'ACCT_AM= SUM( CASE ' + 'WHEN FG=''1'' AND DRCR_FG IN( ''1'',''3'') THEN ACCT_AM ' + 'WHEN FG=''2'' AND DRCR_FG IN( ''2'',''4'') THEN ACCT_AM ' + 'WHEN FG=''1'' AND DRCR_FG IN( ''2'',''4'') THEN ACCT_AM * -1 ' + 'WHEN FG=''2'' AND DRCR_FG IN( ''1'',''3'') THEN ACCT_AM * -1 END ) ' + 'FROM VA_APTRS ' + 'WHERE CO_CD= ''' + @S_CO_CD + ''' AND DIV_CD = ''' + @S_DIV_CD + ''' AND FILL_YM BETWEEN LEFT(''' + @FRDT + ''',6) AND LEFT( ''' + @TODT + ''',6) AND ACCT_CD = ''' + @V_ACCT_CD + ''' GROUP BY CO_CD, DIV_CD, ACCT_CD, ' + @v_Group + ', ' + @v_Group1 + ', ' + @v_Group2 + ', FG ' + ' ) D LEFT OUTER JOIN STRADE T ' + 'ON D.CO_CD=T.CO_CD AND D.TR_CD = T.TR_CD ' + 'WHERE D.ACCT_AM<>0 ' END ELSE BEGIN SET @v_strSQL = 'SELECT ' + @v_Sel + ', ' + @v_Sel1 + ', ' + @v_Sel2 + ', D.ACCT_AM, D.DRCR_FG_V ' + ' FROM ( ' + 'SELECT CO_CD, DIV_CD, ACCT_CD, DRCR_FG_V =(CASE WHEN FG=''1'' THEN ''3'' ELSE ''4'' END ), ' + @v_NULL + ', ' + @v_NULL1 + ', ' + @v_NULL2 + ', ' + 'ACCT_AM= SUM( CASE ' + 'WHEN FG=''1'' AND DRCR_FG IN( ''1'',''3'') THEN ACCT_AM ' + 'WHEN FG=''2'' AND DRCR_FG IN( ''2'',''4'') THEN ACCT_AM ' + 'WHEN FG=''1'' AND DRCR_FG IN( ''2'',''4'') THEN ACCT_AM * -1 ' + 'WHEN FG=''2'' AND DRCR_FG IN( ''1'',''3'') THEN ACCT_AM * -1 END ) ' + 'FROM VA_APTRS ' + 'WHERE CO_CD= ''' + @S_CO_CD + ''' AND DIV_CD = ''' + @S_DIV_CD + ''' AND FILL_YM BETWEEN LEFT(''' + @FRDT + ''' ,6) AND LEFT( ''' + @TODT + ''',6) AND ACCT_CD = ''' + @V_ACCT_CD + ''' GROUP BY CO_CD, DIV_CD, ACCT_CD, ' + @v_Group + ', ' + @v_Group1 + ', ' + @v_Group2 + ', FG ' + ' ) D ' + 'WHERE D.ACCT_AM<>0 ' END SET @v_strFETCH = 'FETCH SBU_TR INTO '+ @v_Into + ' ,' + @v_Into1 + ' ,' + @v_Into2 + ' , @V_ACCT_AM, @V_DRCR_FG' IF ( @V_GR_CD IN ('A','F','H','I','J') ) BEGIN SET @v_strUPDATE = 'UPDATE APREV SET '+ @v_Set1 + ' = @V_TR_CD , TR_NM = @V_TR_NM , ' + @v_Set2 + ' = @V_TR_CD1 , ' + @v_Set3 + ' = @V_TR_CD2 ' + ' WHERE CO_CD = ''' + @S_CO_CD + ''' AND GISU = @V_NEXTGISU AND DIV_CD = ''' + @S_DIV_CD + ''' AND ACCT_CD = ''' + @V_ACCT_CD + ''' AND ISU_SQ = @V_FILL_NB ' END ELSE IF ( @V_GR_CD1 IN('A','F','H','I','J') ) BEGIN SET @v_strUPDATE = 'UPDATE APREV SET ' + @v_Set2 + ' = @V_TR_CD1 , TR_NM = @V_TR_NM , ' + @v_Set1 + ' = @V_TR_CD , ' + @v_Set3 + ' = @V_TR_CD2 ' + ' WHERE CO_CD = ''' + @S_CO_CD + ''' AND GISU = @V_NEXTGISU AND DIV_CD = ''' + @S_DIV_CD + ''' AND ACCT_CD = ''' + @V_ACCT_CD + ''' AND ISU_SQ = @V_FILL_NB ' END ELSE IF ( @V_GR_CD2 IN('A','F','H','I','J') ) BEGIN SET @v_strUPDATE = 'UPDATE APREV SET '+ @v_Set3 + ' = @V_TR_CD2 , TR_NM = @V_TR_NM , ' + @v_Set1 + ' = @V_TR_CD , ' + @v_Set2 + ' = @V_TR_CD1 ' + ' WHERE CO_CD = ''' + @S_CO_CD + ''' AND GISU = @V_NEXTGISU AND DIV_CD = ''' + @S_DIV_CD + ''' AND ACCT_CD = ''' + @V_ACCT_CD + ''' AND ISU_SQ = @V_FILL_NB ' END ELSE BEGIN SET @v_strUPDATE = 'UPDATE APREV SET '+ @v_Set1 + ' = @V_TR_CD , ' + @v_Set2 + ' = @V_TR_CD1 , ' + @v_Set3 + ' = @V_TR_CD2 ' + ' WHERE CO_CD = ''' + @S_CO_CD + ''' AND GISU = @V_NEXTGISU AND DIV_CD = ''' + @S_DIV_CD + ''' AND ACCT_CD = ''' + @V_ACCT_CD + ''' AND ISU_SQ = @V_FILL_NB ' END END -- 쿼리문 assign 끝 ====================================================================== -- < 3. 커서 구성 및 각종 쿼리문 결합 시작 > ======================================================= -- ★ @v_strQUERY : CURSOR 실행 및 APREV 테이블에 저장하는 전체 쿼리문 저장 SET @v_strQUERY = -- 'DECLARE @V_TR_CD VARCHAR(10) ' + -- 'DECLARE @V_TR_CD1 VARCHAR(10) ' + -- 'DECLARE @V_TR_CD2 VARCHAR(10) ' + 'DECLARE @V_TR_CD VARCHAR(20) ' + 'DECLARE @V_TR_CD1 VARCHAR(20) ' + 'DECLARE @V_TR_CD2 VARCHAR(20) ' + 'DECLARE @V_TR_NM VARCHAR(40) ' + 'DECLARE @V_ACCT_AM NUMERIC(17,4) ' + 'DECLARE @V_DRCR_FG VARCHAR(1) ' + 'DECLARE @V_FILL_NB NUMERIC(5) ' + 'DECLARE @V_NEXTGISU NUMERIC(3) ' + 'DECLARE @V_AMT0 NUMERIC(17,4) ' + 'DECLARE @V_AMT5 NUMERIC(17,4) ' + 'DECLARE @V_AMT6 NUMERIC(17,4) ' + 'DECLARE @O_ISU_SQ INT ' + 'DECLARE @V_AMT8 NUMERIC(17,4) ' + 'DECLARE @V_CURGISU NUMERIC(3) ' + 'DECLARE @LANG_KIND VARCHAR(3) ' + 'SET @V_NEXTGISU = ' + CAST(@S_GISU+1 AS VARCHAR(3)) + 'SET @V_CURGISU = ' + CAST(@S_GISU AS VARCHAR(3)) + ' DECLARE SBU_TR CURSOR FOR ' + @v_strSQL + ' OPEN SBU_TR ' + @v_strFETCH + ' WHILE ( @@FETCH_STATUS = 0 ) ' + ' BEGIN ' + ' SELECT @V_FILL_NB = ( SELECT ISNULL(MAX(ISU_SQ),0)+1 FROM APREV ' + ' WHERE CO_CD = ''' + @S_CO_CD + ''' AND DIV_CD = ''' + @S_DIV_CD + ''' AND GISU = @V_NEXTGISU AND ACCT_CD = ''' + @V_ACCT_CD + ''' ) ' + ' IF ( @V_FILL_NB >0 ) ' + ' BEGIN ' + ' INSERT APREV ' + ' ( CO_CD, GISU, DIV_CD, ACCT_CD, ISU_SQ, DEPT_CD, EMP_CD, ' + ' DRCR_FG, FILL_DT, FILL_NB, RMK_NB, RMK_DC, ACCT_AM ' + ', TRCD_TY, TRNM_TY, DEPTCD_TY, PJTCD_TY, CTNB_TY, ' + ' FRDT_TY, TODT_TY, QT_TY, AM_TY, RT_TY, DEAL_TY ' + ', USER1_TY, USER2_TY ' + ' , TR_CD, TR_NM, CT_DEPT, PJT_CD, CT_NB, CT_DEAL, CT_USER1, CT_USER2 ,FR_DT, TO_DT ) ' + ' SELECT ''' + @S_CO_CD+ ''', @V_NEXTGISU , ''' + @S_DIV_CD+ ''', ''' + @V_ACCT_CD +''', @V_FILL_NB, ''' + @S_DEPT_CD + ''', ''' + @S_EMP_CD + ''', ' + '@V_DRCR_FG' + ', ''' + @START + ''', @V_FILL_NB , 0, CASE WHEN @LANG_KIND = ''KOR'' THEN ''마감이월'' ELSE ''Closing C/F'' END, @V_ACCT_AM , ' + ' A.TRCD_TY, A.TRNM_TY, A.DEPTCD_TY, A.PJTCD_TY, A.CTNB_TY, ' + ' A.FRDT_TY, A.TODT_TY, A.QT_TY, A.AM_TY, A.RT_TY, A.DEAL_TY ' + ' ,A.USER1_TY, A.USER2_TY ' + ' ,'''','''', '''','''', '''','''', '''','''', ''00000000'', ''00000000'' ' + ' FROM SACCT A ' + ' WHERE A.CO_CD= ''' + @S_CO_CD + ''' AND A.ACCT_CD= ''' + @V_ACCT_CD + ''' ' + @v_strUPDATE + -- DETAIL INSERT ' IF ( ' + @V_ACCT_CD + ' >= ''14600'' AND ' + @V_ACCT_CD + ' <= ''16399'' AND ' + @V_ACCT_FG + ' = ''2'' ) ' + ' BEGIN ' + ' SELECT @O_ISU_SQ = ( SELECT ISNULL(MAX(ISU_SQ), 0)+1 FROM APREV_D ' + ' WHERE CO_CD = ''' + @S_CO_CD + ''' AND DIV_CD = ''' + @S_DIV_CD + ''' AND GISU = @V_NEXTGISU AND ACCT_CD = ''' + @V_ACCT_CD + ''' ) ' + ' IF ( @O_ISU_SQ > 0 ) ' + ' BEGIN ' + ' INSERT INTO APREV_D ' + ' ( CO_CD, GISU, DIV_CD, ACCT_CD, ISU_SQ ) ' + ' VALUES ' + ' ( ''' + @S_CO_CD + ''', @V_NEXTGISU, ''' + @S_DIV_CD + ''', ''' + @V_ACCT_CD + ''', @O_ISU_SQ ) ' + --update 항목 집계 -- 기초재고액(AMT0) ' SELECT @V_AMT0 = ISNULL(ACCT_AM, 0) ' + ' FROM APREV ' + ' WHERE CO_CD = ''' + @S_CO_CD + ''' AND DIV_CD = ''' + @S_DIV_CD + ''' AND GISU = @V_CURGISU AND ACCT_CD = ''' + @V_ACCT_CD + '''' + --타계정입고(AMT5) ' SELECT @V_AMT5 = ISNULL(SUM (ISNULL(ACCT_AM, 0)) , 0 ) ' + ' FROM ADOCUD D ' + ' WHERE D.CO_CD = ''' + @S_CO_CD + ''' AND D.DIV_CD = ''' + @S_DIV_CD + ''' AND D.DRCR_FG IN (''1'',''3'') AND D.CT_DEAL = ''1'' ' + ' AND D.ACCT_CD = ''' + @V_ACCT_CD + ''' AND LEFT(D.FILL_DT, 6) BETWEEN LEFT( ''' +@FRDT+ ''',6) AND LEFT( ''' + @TODT + ''',6) ' + --타계정출고(AMT6) ' SELECT @V_AMT6 = ISNULL(SUM (ISNULL(ACCT_AM, 0)) , 0 ) ' + ' FROM ADOCUD D ' + ' WHERE D.CO_CD = ''' + @S_CO_CD + ''' AND D.DIV_CD = ''' + @S_DIV_CD + ''' AND D.DRCR_FG IN (''2'',''4'') AND D.CT_DEAL = ''2'' ' + ' AND D.ACCT_CD = ''' + @V_ACCT_CD + ''' AND LEFT(D.FILL_DT, 6) BETWEEN LEFT( ''' +@FRDT + ''',6) AND LEFT( ''' + @TODT + ''' ,6) ' + --기말재고액(AMT8) ' SET @V_AMT8 = @V_ACCT_AM ' + ' UPDATE APREV_D ' + ' SET INIT_AM = @V_AMT0, AIN_AM = @V_AMT5, AOUT_AM = @V_AMT6, STOC_AM = @V_AMT8 ' + ' WHERE CO_CD = ''' + @S_CO_CD + ''' AND DIV_CD = ''' + @S_DIV_CD + ''' AND ACCT_CD = ''' + @V_ACCT_CD + ''' AND GISU = @V_NEXTGISU AND ISU_SQ = @O_ISU_SQ ' + ' END ' + ' END ' + --나머지 ACCT_FG = '3', ACCT_FG = '4'일때는 관련계정의 값을 UPDATE하므로 생략함.(관련계정의미없음) ' END ' + @v_strFETCH + ' END ' + 'CLOSE SBU_TR ' + 'DEALLOCATE SBU_TR ' EXEC(@v_strQUERY) END -- 커서 구성 및 각종 쿼리문 결합 끝 ======================================================= SET NOCOUNT OFF SET ANSI_WARNINGS ON RETURN 1 ERROR: ROLLBACK TRANSACTION RAISERROR @ERRNO @ERRMSG GO set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go IF EXISTS ( SELECT 1 from dbo.sysobjects where id = object_id('[DZGOLD].[SPA_APREV]') and OBJECTPROPERTY(id, 'IsProcedure') = 1) DROP PROCEDURE [DZGOLD].[SPA_APREV] GO /****** 개체: 저장 프로시저 DZGOLD.SPA_APREV 스크립트 날짜: 2002-02-15 오후 1:19:53 ******/ CREATE PROC [DZGOLD].[SPA_APREV] ( @S_CO_CD VARCHAR(4), --로긴회사 @S_DIV_CD VARCHAR(4), --회계단위 @S_DEPT_CD VARCHAR(4), --결의부서 @S_EMP_CD VARCHAR(10), --사원코드 @S_GISU NUMERIC(3), --현재의 회계기수 @START VARCHAR(8), --다음기수초일 @FRDT VARCHAR(8), --현재의 회계기간 초일 @TODT VARCHAR(8), --현재의 회계기간 말일 @LANG_KIND VARCHAR(3) --보여지는 언어 종류 ("KOR", "ENG"..) ) --WITH ENCRYPTION AS /*-------------------------------------------------------*/ --기 능:회계마감이월 --작성자:제희경 <-이강수 --작성일:2001/05/30 --수정일:2005/01/18 <- 2005/01/17 <- 2005/01/11 <-2002/05/22 <-2002/03/28 --손익계정에서 세목사용시 세목간 계정구분이 틀릴경우 INSERT에라 수정 --사용법: SPA_APREV '8272','1000','1100','1234567890',7,'20020101','20010101','20011231' /*-------------------------------------------------------*/ -- 수정일 : 2002/01/23 -- 수정자 : 제희경 -- 수정내역 : 이월항목 갯수가 1개에서 3개로 증설 ----------------------------------------------------------- -- 수정일 : 2005/01/11 -- 수정자 : 제희경 -- 수정내역 : *자산계정(<40000)의 계정그룹에 따라 APREV_D INSERT하는 항목 변경 -- *연동항목이 '09'(기간비용)일때는 다른 연동항목들과 이월방식 차별화. ----------------------------------------------------------- -- 수정일 : 2005/01/18 <- 2005/01/17 -- 수정자 : 제희경 -- 수정내역 : *영업외비용, 영업외수익 그룹은 기존 40000보다 작은 계정의 타계정과 다르게 이월. -- *기존 이월detail 입력 내역 수정. ----------------------------------------------------------- -- 수정일 : 2005/01/31 -- 수정자 : 제희경 -- 수정내역 : * 각 항목별 기말재고액 계산 방식 수정. ----------------------------------------------------------- -- 수정일 : 2005/02/23 -- 수정자 : 제희경 -- 수정내역 : * 재고자산평가손실, 재고자산평가손실환입, 관세환급금에 대해 당기매입액 산출 방식 수정. ----------------------------------------------------------- -- 수정일 : 2005/09/12 -- 수정자 : 제희경 -- 수정내역 : * 마감이월 data select시 40000번 이상계정(손익계정)일 경우는 세목발생액도 본계정에 수렴하던것을 세목,과목 그대로 이월하도록 수정. ----------------------------------------------------------- -- 수정일 : 2006/03/28 -- 수정자 : 제희경 -- 수정내역 : * 한국어/다국어 프로시져 통합 및 다국어 LANG_KIND 파라미터 추가. ----------------------------------------------------------- -- 수정일 : 2006/04/06 -- 수정자 : 제희경 -- 수정내역 : * 14600 ~ 16399 매입할인(3), 매입환출(4)는 HEADER로 이월되지 않도록 수정 ----------------------------------------------------------- -- 수정일 : 2006/04/13 -- 수정자 : 이경일 -- 수정내역 : * 관련계정 ( 기초재고액 )에 대한 이월금액 SUM ----------------------------------------------------------- -- 수정일 : 2006/04/19, 2006/05/02 -- 수정자 : 제희경 -- 수정내역 : * 14600 ~ 16399 매입할인(3), 매입환출(4)는 HEADER에서 관련계정의 매입할인액이나 환출액이 빠진 순액만 이월되도록 수정. ----------------------------------------------------------- -- INIT_AM에 null값 들어가는 문제 수정 (20070808) ----------------------------------------------------------- -- 수정일 : 2008/01/07 -- 수정자 : 제희경 -- 수정내역 : 이월항목에 '0'대신 '' DEFAULT인 경우 체크하도록 수정 ----------------------------------------------------------- SET NOCOUNT ON SET ANSI_WARNINGS OFF DECLARE @V_CO_CD VARCHAR(4), --SCLOSE의 CLOSE_DT @V_ACCT_CD VARCHAR(5), @V_RACCT_CD VARCHAR(5), @V_ACCT_FG VARCHAR(1), @V_SUB_DISP VARCHAR(2), @V_GR_CD VARCHAR(1), --BY 제희경 : 이월항목이 1개에서 3개까지 가능하게 수정됨. @V_GR_CD1 VARCHAR(1), @V_GR_CD2 VARCHAR(1), ---------------------------- @V_DRCR_FG VARCHAR(1), @V_CLS_DT VARCHAR(8), @V_ACCT_AM NUMERIC(17,4), @V_ACCT_AM1 NUMERIC(17,4), @V_ACCT_AM2 NUMERIC(17,4), @V_FILL_NB NUMERIC(5), @V_CNT NUMERIC(5), @FLAG VARCHAR(16), @ERRNO INT, --BY 제희경 @O_ISU_SQ INT, @V_AMT00 NUMERIC(17,4), @V_AMT0 NUMERIC(17,4), @V_AMT1 NUMERIC(17,4), @V_AMT2 NUMERIC(17,4), @V_AMT3 NUMERIC(17,4), @V_AMT4 NUMERIC(17,4), @V_AMT5 NUMERIC(17,4), @V_AMT6 NUMERIC(17,4), @V_AMT7 NUMERIC(17,4), @V_AMT8 NUMERIC(17,4), @V_AMT9 NUMERIC(17,4), @V_AMT10 NUMERIC(17,4), @V_TACCT_CD VARCHAR(5), @V_TACCT_FG VARCHAR(1), @V_TRACCT_CD VARCHAR(5), @V_HAMT NUMERIC(17,4), @V_RACCT_FG VARCHAR(1), @ERRMSG VARCHAR(100) --마감일 확인 SELECT @V_CLS_DT=ISNULL(CLOSE_DT,'00000000') FROM SCLOSE WHERE CLOSE_CD='07' AND CO_CD=@S_CO_CD --마감일이 다음 기수 초일 보다 크거나 같다. IF (@START<=@V_CLS_DT) BEGIN SET @ERRNO = 50814 IF ( @LANG_KIND = 'KOR' ) SET @ERRMSG = '마감통제 된 자료로 이월할 수 할 수 없습니다.!' ELSE SET @ERRMSG = 'Data with closing controlled can''t be carried forward..!' GOTO ERROR END --차기 초기이월 삭제(detail삭제 -> header삭제) DELETE FROM APREV_D WHERE CO_CD = @S_CO_CD AND DIV_CD=@S_DIV_CD AND GISU = @S_GISU +1 --현재 회계기수+1 DELETE FROM APREV WHERE CO_CD = @S_CO_CD AND DIV_CD=@S_DIV_CD AND GISU = @S_GISU +1 --마감자료 선택 DECLARE CUSOR_APREV CURSOR FOR SELECT DISTINCT M.CO_CD ,M.ACCT_CD ,S.ACCT_FG ,CASE WHEN S.SUB_DISP <> '' THEN S.SUB_DISP ELSE '00' END AS SUB_DISP ,LEFT(S.NORM_CD,1) AS GR_CD ,LEFT(S.NORM_CD1, 1) AS GR_CD1 ,LEFT(S.NORM_CD2, 1) AS GR_CD2 ,CASE WHEN S.RACCT_CD <>'' THEN S.RACCT_CD ELSE '0' END AS RACCT_CD ,S.DRCR_FG FROM ( SELECT DISTINCT CO_CD,ACCT_CD FROM APREV WHERE CO_CD=@S_CO_CD AND DIV_CD=@S_DIV_CD AND FILL_DT = @FRDT AND ACCT_CD <'40000' UNION SELECT DISTINCT CO_CD, --2005.09.12 (김지선 주임 요청) /* CASE WHEN ACCT_CD > '40000' THEN LEFT(ACCT_CD,3)+'00' ELSE ACCT_CD END AS ACCT_CD */ ACCT_CD FROM ADSUM --일집계화일 WHERE CO_CD=@S_CO_CD AND DIV_CD=@S_DIV_CD AND FILL_DT BETWEEN @FRDT AND @TODT AND ACCT_CD <>'40000' UNION SELECT @S_CO_CD CO_CD,'10100' AS ACCT_CD ) M JOIN SACCT S ON M.CO_CD=S.CO_CD AND M.ACCT_CD=S.ACCT_CD AND S.ACCT_FG < '9' ORDER BY M.ACCT_CD OPEN CUSOR_APREV --BY 제희경(2002.12.23) : 이월항목이 1개에서 3개까지 가능하게 수정됨. FETCH CUSOR_APREV INTO @V_CO_CD, @V_ACCT_CD,@V_ACCT_FG,@V_SUB_DISP,@V_GR_CD, @V_GR_CD1, @V_GR_CD2, @V_RACCT_CD,@V_DRCR_FG -- -- BEGIN TRANSACTION WHILE (@@FETCH_STATUS = 0) BEGIN SET @V_AMT00 = 0 SET @V_AMT0 = 0 SET @V_AMT1 = 0 SET @V_AMT2 = 0 SET @V_AMT3 = 0 SET @V_AMT4 = 0 SET @V_AMT5 = 0 SET @V_AMT6 = 0 SET @V_AMT7 = 0 SET @V_AMT8 = 0 SET @V_AMT9 = 0 SET @V_AMT10 = 0 SET @V_HAMT = 0 IF ( @V_ACCT_CD>'40000' ) --손익계정 이월 BEGIN SELECT @V_ACCT_AM=ISNULL(SUM(ACCT_AM),0) FROM VA_PLCOST -- 2005.09.12 (과목, 세목 각각 이월) -- WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND LEFT(FILL_YM,6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) AND ACCT_CD =LEFT(@V_ACCT_CD, 3) WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND LEFT(FILL_YM,6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) AND ACCT_CD =@V_ACCT_CD -- GROUP BY CO_CD, ACCT_CD IF (@V_ACCT_AM<>0) --이월금액이 존재하면 BEGIN SELECT @V_RACCT_FG = ACCT_FG FROM SACCT WHERE CO_CD = @S_CO_CD AND ACCT_CD = @V_RACCT_CD --*1)----------- IF ( ( (@V_ACCT_CD >= '45100' AND @V_ACCT_CD <= '47099' AND @V_ACCT_FG IN ('2','3','5','7' )) OR (@V_ACCT_CD >= '50100' AND @V_ACCT_CD <= '60099' AND @V_ACCT_FG IN ('1','5')) OR (@V_ACCT_CD >= '60100' AND @V_ACCT_CD <= '70099' AND @V_ACCT_FG IN ('1')) OR (@V_ACCT_CD >= '70100' AND @V_ACCT_CD <= '80099' AND @V_ACCT_FG IN ('1','6')) ) AND ( @V_RACCT_CD <> '0' ) ) BEGIN -- HEADER INSERT INSERT APREV ( CO_CD, GISU, DIV_CD, ACCT_CD, ISU_SQ, DEPT_CD, EMP_CD, DRCR_FG, FILL_DT, FILL_NB, RMK_NB, RMK_DC, ACCT_AM , TRCD_TY, TRNM_TY, DEPTCD_TY, PJTCD_TY, CTNB_TY, FRDT_TY, TODT_TY, QT_TY, AM_TY, RT_TY, DEAL_TY , USER1_TY, USER2_TY ,FR_DT, TO_DT ) SELECT @S_CO_CD,@S_GISU+1,@S_DIV_CD,@V_ACCT_CD,1,@S_DEPT_CD,@S_EMP_CD, (CASE WHEN A.DRCR_FG='1' THEN '3' ELSE '4' END) DRCR_FG, @START,1,0, CASE WHEN @LANG_KIND = 'KOR' THEN '마감이월' ELSE 'Closing C/F' END,@V_ACCT_AM, A.TRCD_TY, A.TRNM_TY, A.DEPTCD_TY, A.PJTCD_TY, A.CTNB_TY, A.FRDT_TY, A.TODT_TY, A.QT_TY, A.AM_TY, A.RT_TY, A.DEAL_TY ,A.USER1_TY, A.USER2_TY, '00000000', '00000000' FROM SACCT A WHERE A.CO_CD=@S_CO_CD AND A.ACCT_CD=@V_ACCT_CD -- DETAIL INSERT SELECT @O_ISU_SQ = ( SELECT ISNULL(MAX(ISU_SQ), 0)+1 FROM APREV_D WHERE CO_CD = @S_CO_CD AND DIV_CD = @S_DIV_CD AND GISU = @S_GISU+1 AND ACCT_CD = @V_ACCT_CD ) IF ( @O_ISU_SQ > 0 AND @O_ISU_SQ <> 2 ) BEGIN INSERT INTO APREV_D ( CO_CD, GISU, DIV_CD, ACCT_CD, ISU_SQ ) VALUES ( @S_CO_CD, @S_GISU+1, @S_DIV_CD, @V_ACCT_CD, @O_ISU_SQ ) END ELSE BEGIN SET @O_ISU_SQ = 1 --기타 DB값 read SELECT @V_AMT3 = ISNULL(RETURN_AM,0), @V_AMT4 = ISNULL(DC_AM,0), @V_AMT7 = ISNULL(TARI_AM,0), @V_AMT9 = ISNULL(DEV_AM,0), @V_AMT10 = ISNULL(REDEV_AM,0) FROM APREV_D WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND ACCT_CD = @V_ACCT_CD AND GISU = @S_GISU+1 AND ISU_SQ = 1 END --update할 금액 조회-- -- 기초재고액(AMT0)-- 20060412 기초 재고금액 이월SUM(이경일)-- SELECT @V_AMT0 = ISNULL(SUM(ISNULL(ACCT_AM, 0)), 0) FROM APREV WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND GISU = @S_GISU AND ACCT_CD = @V_RACCT_CD --(AMT2) SELECT @V_AMT2 = ISNULL(SUM(ISNULL(ACCT_AM,0)), 0) FROM ADSUM WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND DRCR_FG IN ('1', '3') AND ACCT_CD = @V_RACCT_CD AND LEFT(FILL_DT, 6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) --타계정입고(AMT5) SELECT @V_AMT5 = ISNULL(SUM (ISNULL(ACCT_AM, 0)) , 0 ) FROM ADOCUD D WHERE D.CO_CD=@S_CO_CD AND D.DIV_CD = @S_DIV_CD AND D.DRCR_FG IN ('1','3') AND D.CT_DEAL = '1' AND D.ACCT_CD = @V_RACCT_CD AND LEFT(D.FILL_DT, 6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) --타계정출고(AMT6) SELECT @V_AMT6 = ISNULL(SUM (ISNULL(ACCT_AM, 0)) , 0 ) FROM ADOCUD D WHERE D.CO_CD=@S_CO_CD AND D.DIV_CD = @S_DIV_CD AND D.DRCR_FG IN ('2','4') AND D.CT_DEAL = '2' AND D.ACCT_CD = @V_RACCT_CD AND LEFT(D.FILL_DT, 6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) --당기매입액(AMT1) SET @V_AMT1 = @V_AMT2 - @V_AMT5 -- 손익계정 이월금액(AMT00) SET @V_AMT00 = @V_ACCT_AM + @V_AMT9 - @V_AMT10 /* RAISERROR('1) @V_ACCT_CD : %s, @V_RACCT_CD : %s, @V_AMT00 : %d, @V_HAMT : %d, @V_AMT0 : %d, @V_AMT1 : %d, @V_AMT2 : %d, @V_AMT3 : %d, @V_AMT4 : %d, @V_AMT5 : %d, @V_AMT6 : %d, @V_AMT7 : %d, @V_AMT9 : %d, @V_AMT10 : %d', 16, 1, @V_ACCT_CD, @V_RACCT_CD, @V_AMT00, @V_HAMT, @V_AMT0, @V_AMT1, @V_AMT2, @V_AMT3 , @V_AMT4, @V_AMT5 , @V_AMT6 , @V_AMT7, @V_AMT9, @V_AMT10 ) CLOSE CUSOR_APREV DEALLOCATE CUSOR_APREV RETURN 1 */ --UPDATE 문.. IF ( @V_ACCT_CD >= '45100' AND @V_ACCT_CD <= '47099' AND @V_ACCT_FG IN ('2','3','5','7' ) ) BEGIN SET @V_AMT8 = @V_AMT0 + @V_AMT1 - @V_AMT3 - @V_AMT4 + @V_AMT5 - @V_AMT6 - @V_AMT7 + @V_AMT9 - @V_AMT10 - @V_AMT00 UPDATE APREV_D SET INIT_AM = @V_AMT0, PUR_AM = @V_AMT1, AIN_AM = @V_AMT5, AOUT_AM = @V_AMT6, STOC_AM = @V_AMT8 WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND ACCT_CD = @V_ACCT_CD AND GISU = @S_GISU+1 AND ISU_SQ = @O_ISU_SQ END ELSE IF ( ( @V_ACCT_CD >= '50100' AND @V_ACCT_CD <= '60099' AND @V_ACCT_FG IN ('1','5')) OR (@V_ACCT_CD >= '60100' AND @V_ACCT_CD <= '70099' AND @V_ACCT_FG IN ('1')) OR (@V_ACCT_CD >= '70100' AND @V_ACCT_CD <= '80099' AND @V_ACCT_FG IN ('1','6')) ) BEGIN SET @V_AMT8 = @V_AMT0 + @V_AMT1 - @V_AMT3 - @V_AMT4 + @V_AMT5 - @V_AMT6 - @V_AMT00 UPDATE APREV_D SET INIT_AM = @V_AMT0, PUR_AM = @V_AMT1, AIN_AM = @V_AMT5, AOUT_AM = @V_AMT6, STOC_AM = @V_AMT8 WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND ACCT_CD = @V_ACCT_CD AND GISU = @S_GISU+1 AND ISU_SQ = @O_ISU_SQ END END --*2)----------- 재고자산평가손실, 재고자산평가손실환입 ELSE IF ( ( (@V_ACCT_CD >= '90100' AND @V_ACCT_CD <= '93099' AND @V_ACCT_FG IN ('4' )) OR (@V_ACCT_CD >= '93100' AND @V_ACCT_CD <= '96099' AND @V_ACCT_FG IN ('8')) ) AND ( @V_RACCT_CD <> '0' ) ) BEGIN -- EX) RACCT_CD : 45100일때..RACCT_CD의 관련계정에 대해 V_AMT2 산출 SELECT @V_TACCT_CD = RACCT_CD, @V_TACCT_FG = ACCT_FG FROM SACCT WHERE CO_CD = @S_CO_CD AND ACCT_CD = @V_RACCT_CD AND ( (ACCT_CD >= '45100' AND ACCT_CD <= '47099' AND ACCT_FG IN ('2','3','5','7' )) OR (ACCT_CD >= '50100' AND ACCT_CD <= '60099' AND ACCT_FG IN ('1','5')) OR (ACCT_CD >= '60100' AND ACCT_CD <= '70099' AND ACCT_FG IN ('1')) OR (ACCT_CD >= '70100' AND ACCT_CD <= '80099' AND ACCT_FG IN ('1','6')) ) --(AMT2) SELECT @V_AMT2 = ISNULL(SUM(ISNULL(ACCT_AM,0)), 0) FROM ADSUM WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND DRCR_FG IN ('1', '3') AND ACCT_CD = @V_TACCT_CD AND LEFT(FILL_DT, 6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) --기타 DB값 read SELECT @V_AMT0 = ISNULL(INIT_AM,0), @V_AMT1 = ISNULL(PUR_AM,0) , @V_AMT3 = ISNULL(RETURN_AM,0), @V_AMT4 = ISNULL(DC_AM,0), @V_AMT5 = ISNULL(AIN_AM,0), @V_AMT6 = ISNULL(AOUT_AM,0), @V_AMT7 = ISNULL(TARI_AM,0), @V_AMT8 = ISNULL(STOC_AM,0) FROM APREV_D WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND ACCT_CD = @V_RACCT_CD AND GISU = @S_GISU+1 AND ISU_SQ = 1 --재고자산평가손실(AMT9) SELECT @V_AMT9 = ISNULL( SUM(CASE WHEN D.DRCR_FG IN ('1','3') THEN D.ACCT_AM ELSE 0 END) , 0 ) FROM ADSUM D INNER JOIN SACCT S ON D.CO_CD=S.CO_CD AND D.ACCT_CD=S.ACCT_CD AND S.GROUP_CD = '4172' AND S.ACCT_FG = '8' WHERE D.CO_CD=@S_CO_CD AND D.DIV_CD = @S_DIV_CD AND D.DRCR_FG IN ('1', '3') AND D.ACCT_CD = @V_ACCT_CD AND LEFT(D.FILL_DT, 6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) --재고자산평가손실환입(AMT10) SELECT @V_AMT10 = ISNULL( SUM(CASE WHEN D.DRCR_FG IN ('2','4') THEN D.ACCT_AM ELSE 0 END) , 0 ) FROM ADSUM D INNER JOIN SACCT S ON D.CO_CD=S.CO_CD AND D.ACCT_CD=S.ACCT_CD AND S.GROUP_CD = '4171' AND S.ACCT_FG = '4' WHERE D.CO_CD=@S_CO_CD AND D.DIV_CD = @S_DIV_CD AND D.DRCR_FG IN ('2', '4') AND D.ACCT_CD = @V_ACCT_CD AND LEFT(D.FILL_DT, 6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) SELECT @V_HAMT=ISNULL(SUM(ACCT_AM),0) FROM VA_PLCOST --2005.09.12 (과목,세목 각각 이월) -- WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND LEFT(FILL_YM,6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) AND ACCT_CD =LEFT(@V_RACCT_CD,3) WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND LEFT(FILL_YM,6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) AND ACCT_CD =@V_RACCT_CD --(해당계정금액 + 재고자산평가손실 - 재고자산평가손실환입) SET @V_AMT00 = @V_HAMT + @V_AMT9 - @V_AMT10 --당기매입액(AMT1) SET @V_AMT1 = @V_AMT2 - @V_AMT5 /* RAISERROR('2) @V_ACCT_CD : %s, @V_RACCT_CD : %s, @V_ACCT_AM : %d, @V_AMT00 : %d, @V_HAMT : %d, @V_AMT0 : %d, @V_AMT1 : %d, @V_AMT2 : %d, @V_AMT3 : %d, @V_AMT4 : %d, @V_AMT5 : %d, @V_AMT6 : %d, @V_AMT7 : %d, @V_AMT9 : %d, @V_AMT10 : %d', 16, 1, @V_ACCT_CD, @V_RACCT_CD, @V_ACCT_AM, @V_AMT00, @V_HAMT, @V_AMT0, @V_AMT1, @V_AMT2, @V_AMT3 , @V_AMT4, @V_AMT5 , @V_AMT6 , @V_AMT7, @V_AMT9, @V_AMT10 ) CLOSE CUSOR_APREV DEALLOCATE CUSOR_APREV RETURN 1 */ --UPDATE 문... IF ( @V_RACCT_CD >= '45100' AND @V_RACCT_CD <= '47099' AND @V_RACCT_FG IN ('2','3','5','7' ) ) BEGIN SET @V_AMT8 = @V_AMT0 + @V_AMT1 - @V_AMT3 - @V_AMT4 + @V_AMT5 - @V_AMT6 - @V_AMT7 + @V_AMT9 - @V_AMT10 - @V_AMT00 END ELSE IF ( ( @V_RACCT_CD >= '50100' AND @V_RACCT_CD <= '60099' AND @V_RACCT_FG IN ('1','5')) OR (@V_RACCT_CD >= '60100' AND @V_RACCT_CD <= '70099' AND @V_RACCT_FG IN ('1')) OR (@V_RACCT_CD >= '70100' AND @V_RACCT_CD <= '80099' AND @V_RACCT_FG IN ('1','6')) ) BEGIN SET @V_AMT8 = @V_AMT0 + @V_AMT1 - @V_AMT3 - @V_AMT4 + @V_AMT5 - @V_AMT6 - @V_HAMT END UPDATE APREV_D SET DEV_AM = @V_AMT9, REDEV_AM = @V_AMT10, STOC_AM = @V_AMT8 WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND ACCT_CD = @V_RACCT_CD AND GISU = @S_GISU+1 AND ISU_SQ = 1 --HEADER (해당계정금액 - 관세환급금 + 재고자산평가손실 - 재고자산평가손실환입) UPDATE APREV SET ACCT_AM = @V_AMT00 WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND GISU = @S_GISU+1 AND ACCT_CD=@V_RACCT_CD END --*3)-----------관세환급금 ELSE IF ( ( @V_ACCT_CD >= '45100' AND @V_ACCT_CD <= '47099' AND @V_ACCT_FG IN ('4' ) ) AND ( @V_RACCT_CD <> '0' ) ) BEGIN -- EX) RACCT_CD : 45100일때..RACCT_CD의 관련계정에 대해 V_AMT2 산출 SELECT @V_TACCT_CD = RACCT_CD, @V_TACCT_FG = ACCT_FG FROM SACCT WHERE CO_CD = @S_CO_CD AND ACCT_CD = @V_RACCT_CD AND ( (ACCT_CD >= '45100' AND ACCT_CD <= '47099' AND ACCT_FG IN ('2','3','5','7' )) OR (ACCT_CD >= '50100' AND ACCT_CD <= '60099' AND ACCT_FG IN ('1','5')) OR (ACCT_CD >= '60100' AND ACCT_CD <= '70099' AND ACCT_FG IN ('1')) OR (ACCT_CD >= '70100' AND ACCT_CD <= '80099' AND ACCT_FG IN ('1','6')) ) --(AMT2) SELECT @V_AMT2 = ISNULL(SUM(ISNULL(ACCT_AM,0)), 0) FROM ADSUM WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND DRCR_FG IN ('1', '3') AND ACCT_CD = @V_TACCT_CD AND LEFT(FILL_DT, 6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) --기타 DB값 read SELECT @V_AMT0 = ISNULL(INIT_AM,0), @V_AMT1 = ISNULL(PUR_AM,0) , @V_AMT3 = ISNULL(RETURN_AM,0) , @V_AMT4 = ISNULL(DC_AM,0) , @V_AMT5 = ISNULL(AIN_AM,0), @V_AMT6 = ISNULL(AOUT_AM,0) , @V_AMT8 = ISNULL(STOC_AM,0), @V_AMT9 = ISNULL(DEV_AM,0), @V_AMT10 = ISNULL(REDEV_AM,0) FROM APREV_D WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND ACCT_CD = @V_RACCT_CD AND GISU = @S_GISU+1 AND ISU_SQ = 1 --관세환급금(AMT7) SELECT @V_AMT7 = ISNULL( SUM(CASE WHEN D.DRCR_FG IN ('2','4') THEN D.ACCT_AM ELSE 0 END) , 0 ) FROM ADOCUD D INNER JOIN SACCT S ON D.CO_CD = S.CO_CD AND D.ACCT_CD = S.ACCT_CD WHERE D.CO_CD=@S_CO_CD AND D.DIV_CD = @S_DIV_CD AND S.GROUP_CD = '4120' AND S.ACCT_FG = '4' AND D.ACCT_CD = @V_ACCT_CD AND LEFT(D.FILL_DT, 6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) SELECT @V_HAMT=ISNULL(SUM(ACCT_AM),0) FROM VA_PLCOST -- 2005.09.12 -- WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND LEFT(FILL_YM,6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) AND ACCT_CD =LEFT(@V_RACCT_CD,3) WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND LEFT(FILL_YM,6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) AND ACCT_CD =@V_RACCT_CD --AMT00 SET @V_AMT00 = @V_HAMT + @V_AMT9 - @V_AMT10 --당기매입액(AMT1) SET @V_AMT1 = @V_AMT2 - @V_AMT5 /* RAISERROR('3) @V_ACCT_CD : %s, @V_RACCT_CD : %s, @V_ACCT_AM : %d, @V_AMT00 : %d, @V_HAMT : %d, @V_AMT0 : %d, @V_AMT1 : %d, @V_AMT2 : %d, @V_AMT3 : %d, @V_AMT4 : %d, @V_AMT5 : %d, @V_AMT6 : %d, @V_AMT7 : %d, @V_AMT9 : %d, @V_AMT10 : %d', 16, 1, @V_ACCT_CD, @V_RACCT_CD, @V_ACCT_AM, @V_AMT00, @V_HAMT, @V_AMT0, @V_AMT1, @V_AMT2, @V_AMT3 , @V_AMT4, @V_AMT5 , @V_AMT6 , @V_AMT7, @V_AMT9, @V_AMT10 ) CLOSE CUSOR_APREV DEALLOCATE CUSOR_APREV RETURN 1 */ --UPDATE 문... IF ( @V_RACCT_CD >= '45100' AND @V_RACCT_CD <= '47099' AND @V_RACCT_FG IN ('2','3','5','7' ) ) BEGIN SET @V_AMT8 = @V_AMT0 + @V_AMT1 - @V_AMT3 - @V_AMT4 + @V_AMT5 - @V_AMT6 - @V_AMT7 + @V_AMT9 - @V_AMT10 - @V_AMT00 END ELSE IF ( ( @V_RACCT_CD >= '50100' AND @V_RACCT_CD <= '60099' AND @V_RACCT_FG IN ('1','5')) OR (@V_RACCT_CD >= '60100' AND @V_RACCT_CD <= '70099' AND @V_RACCT_FG IN ('1')) OR (@V_RACCT_CD >= '70100' AND @V_RACCT_CD <= '80099' AND @V_RACCT_FG IN ('1','6')) ) BEGIN SET @V_AMT8 = @V_AMT0 + @V_AMT1 - @V_AMT3 - @V_AMT4 + @V_AMT5 - @V_AMT6 - @V_HAMT END UPDATE APREV_D SET TARI_AM = @V_AMT7, STOC_AM = @V_AMT8 WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND ACCT_CD = @V_RACCT_CD AND GISU = @S_GISU+1 AND ISU_SQ = 1 --HEADER (해당계정금액 - 관세환급금 + 재고자산평가손실 - 재고자산평가손실환입) UPDATE APREV SET ACCT_AM = @V_AMT00 WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND GISU = @S_GISU+1 AND ACCT_CD=@V_RACCT_CD END --*4)----------- ELSE BEGIN -- HEADER INSERT INSERT APREV ( CO_CD, GISU, DIV_CD, ACCT_CD, ISU_SQ, DEPT_CD, EMP_CD, DRCR_FG, FILL_DT, FILL_NB, RMK_NB, RMK_DC, ACCT_AM , TRCD_TY, TRNM_TY, DEPTCD_TY, PJTCD_TY, CTNB_TY, FRDT_TY, TODT_TY, QT_TY, AM_TY, RT_TY, DEAL_TY , USER1_TY, USER2_TY ,FR_DT, TO_DT ) SELECT @S_CO_CD,@S_GISU+1,@S_DIV_CD,@V_ACCT_CD,1,@S_DEPT_CD,@S_EMP_CD, (CASE WHEN A.DRCR_FG='1' THEN '3' ELSE '4' END) DRCR_FG, @START,1,0, CASE WHEN @LANG_KIND = 'KOR' THEN '마감이월' ELSE 'Closing C/F' END,@V_ACCT_AM, A.TRCD_TY, A.TRNM_TY, A.DEPTCD_TY, A.PJTCD_TY, A.CTNB_TY, A.FRDT_TY, A.TODT_TY, A.QT_TY, A.AM_TY, A.RT_TY, A.DEAL_TY ,A.USER1_TY, A.USER2_TY, '00000000', '00000000' FROM SACCT A WHERE A.CO_CD=@S_CO_CD AND A.ACCT_CD=@V_ACCT_CD END END END IF ( @V_ACCT_CD<'40000' ) --자산계정 BEGIN --SACCT의 연동항목이 다음에 해당하지 않고(X)...NORM_CD(이월항목)가 없다면(X)... --################################### -- 2005.01.11 (연동항목이 '09'(기간비용)일때는 다른 연동항목과 다르게 넘기도록 함. -- IF (@V_SUB_DISP NOT IN ('04','05','06','07','08','09','10') AND @V_GR_CD='0' and @V_GR_CD1 = '0' and @V_GR_CD2 = '0' ) --대차대조표 항목이고 계정별합계 -- 2008.01.07 DEFAULT값을 '0'과 ''으로 혼용해서 생기는 문제 -- IF (@V_SUB_DISP NOT IN ('04','05','06','07','08','10') AND @V_GR_CD='0' and @V_GR_CD1 = '0' and @V_GR_CD2 = '0' ) --대차대조표 항목이고 계정별합계 IF (@V_SUB_DISP NOT IN ('04','05','06','07','08','10') AND ( @V_GR_CD='0' OR @V_GR_CD='' ) and ( @V_GR_CD1 = '0' OR @V_GR_CD1='') and (@V_GR_CD2 = '0' OR @V_GR_CD2 = '' ) ) --대차대조표 항목이고 계정별합계 --################################### BEGIN SELECT @V_ACCT_AM=ISNULL(SUM(DCR_AM),0) FROM VA_ACMSUM WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND FILL_YM BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) AND ACCT_CD =@V_ACCT_CD -- GROUP BY CO_CD, ACCT_CD IF (@V_ACCT_AM<>0 ) --이월금액이 존재하면 BEGIN -- 2006.04.06 매입할인(3), 매입환출(4)는 HEADER로 이월되지 않도록 수정 IF ( @V_ACCT_CD >= '14600' AND @V_ACCT_CD <= '16399' AND @V_ACCT_FG IN ('3', '4') ) BEGIN -- 2006.04.19 이월되지 않는게 아니라 금액만큼 빠진 순금액이 넘어가도록 수정됨. -- 2006.05.02 할인, 환출 모두 이월되는 경우 처리 SELECT @V_CNT = COUNT(ACCT_CD) FROM APREV WHERE CO_CD = @S_CO_CD AND DIV_CD = @S_DIV_CD AND GISU = @S_GISU+1 AND ISU_SQ = '1' AND ACCT_CD = @V_RACCT_CD IF ( @V_CNT > 0 ) BEGIN SELECT @V_ACCT_AM1 = ISNULL(ACCT_AM, 0) FROM APREV WHERE CO_CD = @S_CO_CD AND DIV_CD = @S_DIV_CD AND GISU = @S_GISU+1 AND ISU_SQ = '1' AND ACCT_CD = @V_RACCT_CD END ELSE BEGIN SELECT @V_ACCT_AM1=ISNULL(SUM(DCR_AM),0) FROM VA_ACMSUM WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND FILL_YM BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) AND ACCT_CD =@V_RACCT_CD END UPDATE APREV SET ACCT_AM = @V_ACCT_AM1 - @V_ACCT_AM WHERE CO_CD = @S_CO_CD AND DIV_CD = @S_DIV_CD AND GISU = @S_GISU+1 AND ISU_SQ = '1' AND ACCT_CD = @V_RACCT_CD END ELSE BEGIN -- HEADER INSERT INSERT APREV ( CO_CD, GISU, DIV_CD, ACCT_CD, ISU_SQ, DEPT_CD, EMP_CD, DRCR_FG, FILL_DT, FILL_NB, RMK_NB, RMK_DC, ACCT_AM , TRCD_TY, TRNM_TY, DEPTCD_TY, PJTCD_TY, CTNB_TY, FRDT_TY, TODT_TY, QT_TY, AM_TY, RT_TY, DEAL_TY , USER1_TY, USER2_TY ,FR_DT, TO_DT) SELECT @S_CO_CD,@S_GISU+1,@S_DIV_CD,@V_ACCT_CD,1,@S_DEPT_CD,@S_EMP_CD, (CASE WHEN A.DRCR_FG='1' THEN '3' ELSE '4' END) DRCR_FG, @START,1,0, CASE WHEN @LANG_KIND = 'KOR' THEN '마감이월' ELSE 'Closing C/F' END,@V_ACCT_AM, A.TRCD_TY, A.TRNM_TY, A.DEPTCD_TY, A.PJTCD_TY, A.CTNB_TY, A.FRDT_TY, A.TODT_TY, A.QT_TY, A.AM_TY, A.RT_TY, A.DEAL_TY ,A.USER1_TY, A.USER2_TY, '00000000', '00000000' FROM SACCT A WHERE A.CO_CD=@S_CO_CD AND A.ACCT_CD=@V_ACCT_CD END -- DETAIL INSERT IF ( @V_ACCT_CD >= '14600' AND @V_ACCT_CD <= '16399' AND @V_ACCT_FG = '2' ) BEGIN SELECT @O_ISU_SQ = ( SELECT ISNULL(MAX(ISU_SQ), 0)+1 FROM APREV_D WHERE CO_CD = @S_CO_CD AND DIV_CD = @S_DIV_CD AND GISU = @S_GISU+1 AND ACCT_CD = @V_ACCT_CD ) IF ( @O_ISU_SQ > 0 ) BEGIN INSERT INTO APREV_D ( CO_CD, GISU, DIV_CD, ACCT_CD, ISU_SQ ) VALUES ( @S_CO_CD, @S_GISU+1, @S_DIV_CD, @V_ACCT_CD, @O_ISU_SQ ) --update 항목 집계 -- 기초재고액(AMT0) SELECT @V_AMT0 = ISNULL(ACCT_AM, 0) FROM APREV WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND GISU = @S_GISU AND ACCT_CD = @V_ACCT_CD --타계정입고(AMT5) SELECT @V_AMT5 = ISNULL(SUM (ISNULL(ACCT_AM, 0)) , 0 ) FROM ADOCUD D WHERE D.CO_CD=@S_CO_CD AND D.DIV_CD = @S_DIV_CD AND D.DRCR_FG IN ('1','3') AND D.CT_DEAL = '1' AND D.ACCT_CD = @V_ACCT_CD AND LEFT(D.FILL_DT, 6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) --타계정출고(AMT6) SELECT @V_AMT6 = ISNULL(SUM (ISNULL(ACCT_AM, 0)) , 0 ) FROM ADOCUD D WHERE D.CO_CD=@S_CO_CD AND D.DIV_CD = @S_DIV_CD AND D.DRCR_FG IN ('2','4') AND D.CT_DEAL = '2' AND D.ACCT_CD = @V_ACCT_CD AND LEFT(D.FILL_DT, 6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) --기말재고액(AMT8) --SET @V_AMT8 = @V_AMT0 + @V_AMT5 - @V_AMT6 SET @V_AMT8 = @V_ACCT_AM UPDATE APREV_D SET INIT_AM = @V_AMT0, AIN_AM = @V_AMT5, AOUT_AM = @V_AMT6, STOC_AM = @V_AMT8 WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND ACCT_CD = @V_ACCT_CD AND GISU = @S_GISU+1 AND ISU_SQ = @O_ISU_SQ END END -- 매입환출에누리 IF ( @V_ACCT_CD >= '14600' AND @V_ACCT_CD <= '16399' AND @V_ACCT_FG = '3' ) BEGIN SELECT @V_TACCT_CD = ACCT_CD, @V_TACCT_FG = ACCT_FG FROM SACCT WHERE CO_CD = @S_CO_CD AND RACCT_CD = @V_RACCT_CD AND ( (ACCT_CD >= '45100' AND ACCT_CD <= '47099' AND ACCT_FG IN ('2','3','5','7' )) OR (ACCT_CD >= '50100' AND ACCT_CD <= '60099' AND ACCT_FG IN ('1','5')) OR (ACCT_CD >= '60100' AND ACCT_CD <= '70099' AND ACCT_FG IN ('1')) OR (ACCT_CD >= '70100' AND ACCT_CD <= '80099' AND ACCT_FG IN ('1','6')) ) SELECT @O_ISU_SQ = ( SELECT ISNULL(MAX(ISU_SQ), 0)+1 FROM APREV_D WHERE CO_CD = @S_CO_CD AND DIV_CD = @S_DIV_CD AND GISU = @S_GISU+1 AND ACCT_CD = @V_TACCT_CD ) IF ( @O_ISU_SQ > 0 AND @O_ISU_SQ <> 2 ) BEGIN INSERT INTO APREV_D ( CO_CD, GISU, DIV_CD, ACCT_CD, ISU_SQ ) VALUES ( @S_CO_CD, @S_GISU+1, @S_DIV_CD, @V_TACCT_CD, @O_ISU_SQ ) --============================================= IF ( @@ERROR <> 0 ) BEGIN CLOSE CUSOR_APREV DEALLOCATE CUSOR_APREV RAISERROR(@V_ACCT_CD, 16, 1) RETURN END -- 기초재고액(AMT0) SELECT @V_AMT0 = ISNULL(ACCT_AM, 0) FROM APREV WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND GISU = @S_GISU AND ACCT_CD = @V_RACCT_CD --(AMT2) SELECT @V_AMT2 = ISNULL(SUM(ISNULL(ACCT_AM,0)), 0) FROM ADSUM WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND DRCR_FG IN ('1', '3') AND ACCT_CD = @V_RACCT_CD AND LEFT(FILL_DT, 6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) --매입할인( AMT4) SELECT @V_AMT4 = ISNULL(SUM(CASE WHEN D.DRCR_FG IN ('1','3') THEN D.ACCT_AM * -1 WHEN D.DRCR_FG IN ('2','4') THEN D.ACCT_AM ELSE 0 END) , 0 ) FROM ADSUM D INNER JOIN SACCT S ON D.CO_CD=S.CO_CD AND D.ACCT_CD=S.ACCT_CD AND S.GROUP_CD = '1120' AND S.ACCT_FG = '4' WHERE D.CO_CD=@S_CO_CD AND D.DIV_CD = @S_DIV_CD AND D.DRCR_FG IN ('1', '3') AND D.ACCT_CD = @V_TACCT_CD AND LEFT(D.FILL_DT, 6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) --타계정입고(AMT5) SELECT @V_AMT5 = ISNULL(SUM (ISNULL(ACCT_AM, 0)) , 0 ) FROM ADOCUD D WHERE D.CO_CD=@S_CO_CD AND D.DIV_CD = @S_DIV_CD AND D.DRCR_FG IN ('1','3') AND D.CT_DEAL = '1' --AND D.ACCT_CD = @V_RACCT_CD AND LEFT(D.FILL_DT, 6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) AND D.ACCT_CD = @V_TACCT_CD AND LEFT(D.FILL_DT, 6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) --타계정출고(AMT6) SELECT @V_AMT6 = ISNULL(SUM (ISNULL(ACCT_AM, 0)) , 0 ) FROM ADOCUD D WHERE D.CO_CD=@S_CO_CD AND D.DIV_CD = @S_DIV_CD AND D.DRCR_FG IN ('2','4') AND D.CT_DEAL = '2' --AND D.ACCT_CD = @V_RACCT_CD AND LEFT(D.FILL_DT, 6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) AND D.ACCT_CD = @V_TACCT_CD AND LEFT(D.FILL_DT, 6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) --관세환급금(AMT7) SELECT @V_AMT7 = ISNULL( SUM(CASE WHEN D.DRCR_FG IN ('2','4') THEN D.ACCT_AM ELSE 0 END) , 0 ) FROM ADOCUD D INNER JOIN SACCT S ON D.CO_CD = S.CO_CD AND D.ACCT_CD = S.ACCT_CD WHERE D.CO_CD=@S_CO_CD AND D.DIV_CD = @S_DIV_CD AND S.GROUP_CD = '4120' AND S.ACCT_FG = '4' AND D.ACCT_CD = @V_TACCT_CD AND LEFT(D.FILL_DT, 6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) --매입환출에누리(AMT3) SET @V_AMT3 = @V_ACCT_AM /* --2006.04.19 이월금액에서 할인, 환출 관련한 금액은 빼고 조회되도록 UPDATE APREV SET ACCT_AM = ACCT_AM - @V_ACCT_AM WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND ACCT_CD = @V_ACCT_CD AND GISU = @S_GISU+1 AND ISU_SQ = 1 */ --당기매입액(AMT1) SET @V_AMT1 = @V_AMT2 - @V_AMT5 -- 손익계정 이월금액(AMT00) SELECT @V_HAMT=ISNULL(SUM(ACCT_AM),0) FROM VA_PLCOST --2005.09.12 -- WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND LEFT(FILL_YM,6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) AND ACCT_CD =LEFT(@V_TACCT_CD,3) WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND LEFT(FILL_YM,6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) AND ACCT_CD =@V_TACCT_CD SET @V_AMT00 = @V_HAMT + @V_AMT9 - @V_AMT10 --UPDATE 문... IF ( @V_TACCT_CD >= '45100' AND @V_TACCT_CD <= '47099' AND @V_TACCT_FG IN ('2','3','5','7' ) ) BEGIN SET @V_AMT8 = @V_AMT0 + @V_AMT1 - @V_AMT3 - @V_AMT4 + @V_AMT5 - @V_AMT6 - @V_AMT7 + @V_AMT9 - @V_AMT10 - @V_AMT00 UPDATE APREV_D SET INIT_AM = @V_AMT0, PUR_AM = @V_AMT1, RETURN_AM = @V_AMT3, DC_AM = @V_AMT4, AIN_AM = @V_AMT5, AOUT_AM = @V_AMT6, TARI_AM = @V_AMT7, DEV_AM = @V_AMT9, REDEV_AM = @V_AMT10, STOC_AM = @V_AMT8 WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND ACCT_CD = @V_TACCT_CD AND GISU = @S_GISU+1 AND ISU_SQ = @O_ISU_SQ END ELSE IF ( ( @V_TACCT_CD >= '50100' AND @V_TACCT_CD <= '60099' AND @V_TACCT_FG IN ('1','5')) OR (@V_TACCT_CD >= '60100' AND @V_TACCT_CD <= '70099' AND @V_TACCT_FG IN ('1')) OR (@V_TACCT_CD >= '70100' AND @V_TACCT_CD <= '80099' AND @V_TACCT_FG IN ('1','6')) ) BEGIN SET @V_AMT8 = @V_AMT0 + @V_AMT1 - @V_AMT3 - @V_AMT4 + @V_AMT5 - @V_AMT6 - @V_AMT00 UPDATE APREV_D SET INIT_AM = @V_AMT0, PUR_AM = @V_AMT1, RETURN_AM = @V_AMT3, DC_AM = @V_AMT4, AIN_AM = @V_AMT5, AOUT_AM = @V_AMT6, STOC_AM = @V_AMT8 WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND ACCT_CD = @V_TACCT_CD AND GISU = @S_GISU+1 AND ISU_SQ = @O_ISU_SQ END END ELSE BEGIN SET @O_ISU_SQ = 1 --(AMT2) SELECT @V_AMT2 = ISNULL(SUM(ISNULL(ACCT_AM,0)), 0) FROM ADSUM WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND DRCR_FG IN ('1', '3') AND ACCT_CD = @V_TACCT_CD AND LEFT(FILL_DT, 6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) --기타 DB값 read SELECT @V_AMT0 = ISNULL(INIT_AM,0), @V_AMT1 = ISNULL(PUR_AM,0) , @V_AMT4 = ISNULL(DC_AM,0), @V_AMT5 = ISNULL(AIN_AM,0), @V_AMT6 = ISNULL(AOUT_AM,0) , @V_AMT7 = ISNULL(TARI_AM,0), @V_AMT8 = ISNULL(STOC_AM,0), @V_AMT9 = ISNULL(DEV_AM,0), @V_AMT10 = ISNULL(REDEV_AM,0) FROM APREV_D WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND ACCT_CD = @V_TACCT_CD AND GISU = @S_GISU+1 AND ISU_SQ = 1 --매입환출에누리(AMT3) SET @V_AMT3 = @V_ACCT_AM --당기매입액(AMT1) SET @V_AMT1 = @V_AMT2 - @V_AMT5 -- 손익계정 이월금액(AMT00) SELECT @V_HAMT=ISNULL(SUM(ACCT_AM),0) FROM VA_PLCOST -- 2005.09.12 -- WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND LEFT(FILL_YM,6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) AND ACCT_CD =LEFT(@V_TACCT_CD,3) WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND LEFT(FILL_YM,6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) AND ACCT_CD =@V_TACCT_CD SET @V_AMT00 = @V_HAMT + @V_AMT9 - @V_AMT10 --UPDATE 문... SET @V_AMT8 = @V_AMT0 + @V_AMT1 - @V_AMT3 - @V_AMT4 + @V_AMT5 - @V_AMT6 - @V_AMT7 + @V_AMT9 - @V_AMT10 - @V_AMT00 UPDATE APREV_D SET RETURN_AM = @V_AMT3, STOC_AM = @V_AMT8 WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND ACCT_CD = @V_TACCT_CD AND GISU = @S_GISU+1 AND ISU_SQ = 1 END END -- 매입할인 IF ( @V_ACCT_CD >= '14600' AND @V_ACCT_CD <= '16399' AND @V_ACCT_FG = '4' ) BEGIN SELECT @V_TACCT_CD = ACCT_CD, @V_TACCT_FG = ACCT_FG FROM SACCT WHERE CO_CD = @S_CO_CD AND RACCT_CD = @V_RACCT_CD AND ( (ACCT_CD >= '45100' AND ACCT_CD <= '47099' AND ACCT_FG IN ('2','3','5','7' )) OR (ACCT_CD >= '50100' AND ACCT_CD <= '60099' AND ACCT_FG IN ('1','5')) OR (ACCT_CD >= '60100' AND ACCT_CD <= '70099' AND ACCT_FG IN ('1')) OR (ACCT_CD >= '70100' AND ACCT_CD <= '80099' AND ACCT_FG IN ('1','6')) ) SELECT @O_ISU_SQ = ( SELECT ISNULL(MAX(ISU_SQ), 0)+1 FROM APREV_D WHERE CO_CD = @S_CO_CD AND DIV_CD = @S_DIV_CD AND GISU = @S_GISU+1 AND ACCT_CD = @V_TACCT_CD ) IF ( @O_ISU_SQ > 0 AND @O_ISU_SQ <> 2 ) BEGIN INSERT INTO APREV_D ( CO_CD, GISU, DIV_CD, ACCT_CD, ISU_SQ ) VALUES ( @S_CO_CD, @S_GISU+1, @S_DIV_CD, @V_TACCT_CD, @O_ISU_SQ ) --============================================= IF ( @@ERROR <> 0 ) BEGIN CLOSE CUSOR_APREV DEALLOCATE CUSOR_APREV RAISERROR(@V_ACCT_CD, 16, 1) RETURN END -- 기초재고액(AMT0) SELECT @V_AMT0 = ISNULL(ACCT_AM, 0) FROM APREV WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND GISU = @S_GISU AND ACCT_CD = @V_RACCT_CD --(AMT2) SELECT @V_AMT2 = ISNULL(SUM(ISNULL(ACCT_AM,0)), 0) FROM ADSUM WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND DRCR_FG IN ('1', '3') AND ACCT_CD = @V_RACCT_CD AND LEFT(FILL_DT, 6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) --매입환출에누리(AMT3) SELECT @V_AMT3 = ISNULL(SUM(CASE WHEN D.DRCR_FG IN ('1','3') THEN D.ACCT_AM * -1 WHEN D.DRCR_FG IN ('2','4') THEN D.ACCT_AM ELSE 0 END) , 0 ) FROM ADSUM D INNER JOIN SACCT S ON D.CO_CD=S.CO_CD AND D.ACCT_CD=S.ACCT_CD AND S.GROUP_CD = '1120' AND S.ACCT_FG = '3' WHERE D.CO_CD=@S_CO_CD AND D.DIV_CD = @S_DIV_CD AND D.DRCR_FG IN ('1', '3') AND D.ACCT_CD = @V_TACCT_CD AND LEFT(D.FILL_DT, 6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) --타계정입고(AMT5) SELECT @V_AMT5 = ISNULL(SUM (ISNULL(ACCT_AM, 0)) , 0 ) FROM ADOCUD D WHERE D.CO_CD=@S_CO_CD AND D.DIV_CD = @S_DIV_CD AND D.DRCR_FG IN ('1','3') AND D.CT_DEAL = '1' AND D.ACCT_CD = @V_RACCT_CD AND LEFT(D.FILL_DT, 6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) --타계정출고(AMT6) SELECT @V_AMT6 = ISNULL(SUM (ISNULL(ACCT_AM, 0)) , 0 ) FROM ADOCUD D WHERE D.CO_CD=@S_CO_CD AND D.DIV_CD = @S_DIV_CD AND D.DRCR_FG IN ('2','4') AND D.CT_DEAL = '2' AND D.ACCT_CD = @V_RACCT_CD AND LEFT(D.FILL_DT, 6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) SET @V_AMT00 = @V_ACCT_AM --(AMT2) SELECT @V_AMT2 = ISNULL(SUM(ISNULL(ACCT_AM,0)), 0) FROM ADSUM WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND DRCR_FG IN ('1', '3') AND ACCT_CD = @V_TACCT_CD AND LEFT(FILL_DT, 6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) --매입할인(AMT4) SET @V_AMT4 = @V_ACCT_AM /* --2006.04.19 이월금액에서 할인, 환출 관련한 금액은 빼고 조회되도록 UPDATE APREV SET ACCT_AM = ACCT_AM - @V_ACCT_AM WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND ACCT_CD = @V_ACCT_CD AND GISU = @S_GISU+1 AND ISU_SQ = 1 */ --당기매입액(AMT1) SET @V_AMT1 = @V_AMT2 - @V_AMT5 -- 손익계정 이월금액(AMT00) SELECT @V_HAMT=ISNULL(SUM(ACCT_AM),0) FROM VA_PLCOST -- 2005.09.12 (과목, 세목 각각 이월) -- WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND LEFT(FILL_YM,6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) AND ACCT_CD =LEFT(@V_TACCT_CD,3) WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND LEFT(FILL_YM,6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) AND ACCT_CD =@V_TACCT_CD SET @V_AMT00 = @V_HAMT + @V_AMT9 - @V_AMT10 --UPDATE 문... UPDATE APREV_D SET INIT_AM = @V_AMT0, PUR_AM = @V_AMT1, RETURN_AM = @V_AMT3, DC_AM = @V_AMT4, AIN_AM = @V_AMT5, AOUT_AM = @V_AMT6, TARI_AM = @V_AMT7, DEV_AM = @V_AMT9, REDEV_AM = @V_AMT10 WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND ACCT_CD = @V_TACCT_CD AND GISU = @S_GISU+1 AND ISU_SQ = @O_ISU_SQ IF ( @V_TACCT_CD >= '45100' AND @V_TACCT_CD <= '47099' AND @V_TACCT_FG IN ('2','3','5','7' ) ) BEGIN SET @V_AMT8 = @V_AMT0 + @V_AMT1 - @V_AMT3 - @V_AMT4 + @V_AMT5 - @V_AMT6 - @V_AMT7 + @V_AMT9 - @V_AMT10 - @V_AMT00 UPDATE APREV_D SET INIT_AM = @V_AMT0, PUR_AM = @V_AMT1, RETURN_AM = @V_AMT3, DC_AM = @V_AMT4, AIN_AM = @V_AMT5, AOUT_AM = @V_AMT6, TARI_AM = @V_AMT7, DEV_AM = @V_AMT9, REDEV_AM = @V_AMT10, STOC_AM = @V_AMT8 WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND ACCT_CD = @V_TACCT_CD AND GISU = @S_GISU+1 AND ISU_SQ = @O_ISU_SQ END ELSE IF ( ( @V_TACCT_CD >= '50100' AND @V_TACCT_CD <= '60099' AND @V_TACCT_FG IN ('1','5')) OR (@V_TACCT_CD >= '60100' AND @V_TACCT_CD <= '70099' AND @V_TACCT_FG IN ('1')) OR (@V_TACCT_CD >= '70100' AND @V_TACCT_CD <= '80099' AND @V_TACCT_FG IN ('1','6')) ) BEGIN SET @V_AMT8 = @V_AMT0 + @V_AMT1 - @V_AMT3 - @V_AMT4 + @V_AMT5 - @V_AMT6 - @V_AMT00 UPDATE APREV_D SET INIT_AM = @V_AMT0, PUR_AM = @V_AMT1, RETURN_AM = @V_AMT3, DC_AM = @V_AMT4, AIN_AM = @V_AMT5, AOUT_AM = @V_AMT6, STOC_AM = @V_AMT8 WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND ACCT_CD = @V_TACCT_CD AND GISU = @S_GISU+1 AND ISU_SQ = @O_ISU_SQ END END ELSE BEGIN SET @O_ISU_SQ = 1 --(AMT2) SELECT @V_AMT2 = ISNULL(SUM(ISNULL(ACCT_AM,0)), 0) FROM ADSUM WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND DRCR_FG IN ('1', '3') AND ACCT_CD = @V_TACCT_CD AND LEFT(FILL_DT, 6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) --기타 DB값 read SELECT @V_AMT0 = ISNULL(INIT_AM,0), @V_AMT1 = ISNULL(PUR_AM,0) , @V_AMT3 = ISNULL(RETURN_AM,0), @V_AMT5 = ISNULL(AIN_AM,0), @V_AMT6 = ISNULL(AOUT_AM,0) , @V_AMT7 = ISNULL(TARI_AM,0), @V_AMT8 = ISNULL(STOC_AM,0), @V_AMT9 = ISNULL(DEV_AM,0), @V_AMT10 = ISNULL(REDEV_AM,0) FROM APREV_D WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND ACCT_CD = @V_TACCT_CD AND GISU = @S_GISU+1 AND ISU_SQ = 1 --매입할인(AMT4) SET @V_AMT4 = @V_ACCT_AM /* --2006.04.19 이월금액에서 할인, 환출 관련한 금액은 빼고 이월되도록 UPDATE APREV SET ACCT_AM = ACCT_AM - @V_ACCT_AM WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND ACCT_CD = @V_ACCT_CD AND GISU = @S_GISU+1 AND ISU_SQ = 1 */ --당기매입액(AMT1) SET @V_AMT1 = @V_AMT2 - @V_AMT5 -- 손익계정 이월금액(AMT00) /* SELECT @V_HAMT = ISNULL(ACCT_AM,0) FROM APREV WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND GISU = @S_GISU AND ACCT_CD=@V_TACCT_CD */ SELECT @V_HAMT=ISNULL(SUM(ACCT_AM),0) FROM VA_PLCOST -- 2005.09.12 (과목, 세목 각각 이월) -- WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND LEFT(FILL_YM,6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) AND ACCT_CD =LEFT(@V_TACCT_CD,3) WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND LEFT(FILL_YM,6) BETWEEN LEFT(@FRDT,6) AND LEFT(@TODT,6) AND ACCT_CD =@V_TACCT_CD SET @V_AMT00 = @V_HAMT + @V_AMT9 - @V_AMT10 --UPDATE 문... UPDATE APREV_D SET DC_AM = @V_AMT4 WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND ACCT_CD = @V_TACCT_CD AND GISU = @S_GISU+1 AND ISU_SQ = 1 SET @V_AMT8 = @V_AMT0 + @V_AMT1 - @V_AMT3 - @V_AMT4 + @V_AMT5 - @V_AMT6 - @V_AMT7 + @V_AMT9 - @V_AMT10 - @V_AMT00 UPDATE APREV_D SET DC_AM = @V_AMT4, STOC_AM = @V_AMT8 WHERE CO_CD=@S_CO_CD AND DIV_CD = @S_DIV_CD AND ACCT_CD = @V_TACCT_CD AND GISU = @S_GISU+1 AND ISU_SQ = 1 END END END END --SACCT의 연동항목이 다음에 해당하거나(O), NORM_CD(이월항목)가 있다면(O)... --################################## -- 2005.01.11 (연동항목이 '09'(기간비용)일때는 다른 연동항목과 다르게 넘기도록 함. -- IF (@V_SUB_DISP IN ('04','05','06','07','08','09','10') OR @V_GR_CD<>'0' OR @V_GR_CD1 <> '0' OR @V_GR_CD2 <> '0' ) -- 2008.01.07 DEFAULT값을 '0'과 ''으로 혼용해서 생기는 문제 --IF (@V_SUB_DISP IN ('04','05','06','07','08','10') OR @V_GR_CD<>'0' OR @V_GR_CD1 <> '0' OR @V_GR_CD2 <> '0' ) IF (@V_SUB_DISP IN ('04','05','06','07','08','10') OR ( @V_GR_CD<>'0' AND @V_GR_CD <> '' )OR ( @V_GR_CD1 <> '0' AND @V_GR_CD1 <> '' )OR (@V_GR_CD2 <> '0' AND @V_GR_CD2 <> '') ) --################################## BEGIN EXEC SPA_APREH @S_CO_CD,@S_DIV_CD,@S_DEPT_CD,@S_EMP_CD,@S_GISU,@START,@FRDT,@TODT,@V_ACCT_CD,@V_ACCT_FG,@V_SUB_DISP,@V_GR_CD,@V_GR_CD1, @V_GR_CD2, @V_DRCR_FG, @LANG_KIND --############################## END END --##################################### FETCH CUSOR_APREV INTO @V_CO_CD, @V_ACCT_CD,@V_ACCT_FG,@V_SUB_DISP,@V_GR_CD, @V_GR_CD1, @V_GR_CD2, @V_RACCT_CD,@V_DRCR_FG --##################################### END CLOSE CUSOR_APREV DEALLOCATE CUSOR_APREV -- 이익잉여금처리 EXEC SPA_ATRANS_BS @S_CO_CD,@S_DIV_CD,@S_DEPT_CD,@S_EMP_CD,@S_GISU,@START,@FRDT,@TODT, @LANG_KIND ------------------- SET NOCOUNT OFF SET ANSI_WARNINGS ON RETURN 1 ERROR: --ROLLBACK TRANSACTION RAISERROR @ERRNO @ERRMSG SET NOCOUNT OFF SET ANSI_WARNINGS ON GO /***************************************************************************/ declare @module_cd as varchar(3), @ver_info as varchar(8) set @module_cd = 'XSP' set @ver_info = '20080420' delete from smadiary where module_cd = @module_cd and ver_info = @ver_info insert into smadiary (module_cd, upgrade_dt, ver_info, solution_cd) values (@module_cd, getdate(), @ver_info, 'X') GO