if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ZDC_SP_USE_ANALYSIS]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[ZDC_SP_USE_ANALYSIS] GO GO /******************************************************* --TYPE: --DETAIL: Use analysis --CREATE: 2005/07/12 ; KWON TAE HWAN --REVISION1: 2005/07/21 ; KWON TAE HWAN --REVISION2: 2009/02/01 ; KWON TAE HWAN --REVISION3: 2011/01/12 ; KWON TAE HWAN ; LSTKMOVE HEAD -> DETAIL --REVISION3: 2011/01/13 ; KWON TAE HWAN ; LANG ********************************************************/ --Exec ZDC_SP_USE_ANALYSIS '1000', '1000', '2011', 'eng' CREATE PROC ZDC_SP_USE_ANALYSIS @CO_CD VARCHAR(4), @DIV_CD VARCHAR(4), @YYYY VARCHAR(4), @LANG VARCHAR(3) AS BEGIN IF(ISNULL(@LANG,'') = '') SET @LANG = 'KOR' IF OBJECT_ID('tempdb..##S_use_analysis_master') is not null DROP TABLE ##S_use_analysis_master CREATE TABLE ##S_use_analysis_master (Type NVARCHAR(100) COLLATE database_default NULL ,Class NVARCHAR(100) COLLATE database_default NULL ,Qty VARCHAR(20) COLLATE database_default NULL ) IF OBJECT_ID('tempdb..##S_use_analysis_transact') is not null DROP TABLE ##S_use_analysis_transact CREATE TABLE ##S_use_analysis_transact (Module NVARCHAR(100) COLLATE database_default NULL ,Type NVARCHAR(100) COLLATE database_default NULL ,Class NVARCHAR(100) COLLATE database_default NULL ,Qty_1 Int ,Qty_2 Int ,Qty_3 Int ,Qty_4 Int ,Qty_5 Int ,Qty_6 Int ,Qty_7 Int ,Qty_8 Int ,Qty_9 Int ,Qty_10 Int ,Qty_11 Int ,Qty_12 Int ) --------------------------------------------------------------------------------- -- MASTER -- SDIV, SSECT, SDEPT, SEMP -- SLOCBASE, SLOC -- SACCT -- SITEMGRP, SITEM, SBOM, PURPRICE, SALESPRICE -- STRADE -- AFTER: STANDARD COST --------------------------------------------------------------------------------- INSERT ##S_use_analysis_master(Type, Class, Qty) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '사업장'), '', ISNULL(COUNT(*),0) FROM SDIV WHERE CO_CD = @CO_CD INSERT ##S_use_analysis_master(Type, Class, Qty) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '부문'), '', ISNULL(COUNT(*),0) FROM SSECT WHERE CO_CD = @CO_CD INSERT ##S_use_analysis_master(Type, Class, Qty) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '부서'), '', ISNULL(COUNT(*),0) FROM SDEPT WHERE CO_CD = @CO_CD INSERT ##S_use_analysis_master(Type, Class, Qty) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '사원'), '', ISNULL(COUNT(*),0) FROM SEMP WHERE CO_CD = @CO_CD INSERT ##S_use_analysis_master(Type, Class, Qty) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '기본장소'), CASE BASELOC_FG WHEN '0' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '창고') WHEN '1' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '공정') WHEN '2' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '외주') Else dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '기타') END, ISNULL(COUNT(*),0) FROM SBASELOC WHERE CO_CD = @CO_CD GROUP BY BASELOC_FG ORDER BY BASELOC_FG INSERT ##S_use_analysis_master(Type, Class, Qty) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '장소'), CASE B.BASELOC_FG WHEN '0' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '창고') WHEN '1' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '장소') WHEN '2' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '외주') Else dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '기타') END, ISNULL(COUNT(*),0) FROM SLOC L LEFT OUTER JOIN SBASELOC B ON L.CO_CD = B.CO_CD AND L.BASELOC_CD =B.BASELOC_CD WHERE L.CO_CD = @CO_CD GROUP BY B.BASELOC_FG ORDER BY B.BASELOC_FG INSERT ##S_use_analysis_master(Type, Class, Qty) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '생산경로'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '헤더'), ISNULL(COUNT(*),0) FROM LROUTING L WHERE L.CO_CD = @CO_CD INSERT ##S_use_analysis_master(Type, Class, Qty) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '생산경로'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '디테일'), ISNULL(COUNT(*),0) FROM LROUTING_D L WHERE L.CO_CD = @CO_CD INSERT ##S_use_analysis_master(Type, Class, Qty) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '생산경로'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '품목'), ISNULL(COUNT(*),0) FROM LROUT_ITEM L WHERE L.CO_CD = @CO_CD INSERT ##S_use_analysis_master(Type, Class, Qty) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '품목군'), '', ISNULL(COUNT(*),0) FROM SITEMGRP WHERE CO_CD = @CO_CD INSERT ##S_use_analysis_master(Type, Class, Qty) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '품목'), CASE ACCT_FG WHEN '2' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '제품') WHEN '4' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '반제품') WHEN '0' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '원재료') WHEN '1' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '부재료') WHEN '5' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '상품') WHEN '6' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '저장품') WHEN '7' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '비용') WHEN '8' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '수익') Else dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '기타') END, ISNULL(COUNT(*),0) FROM SITEM WHERE CO_CD = @CO_CD GROUP BY ACCT_FG INSERT ##S_use_analysis_master(Type, Class, Qty) SELECT 'BOM', CASE I.ACCT_FG WHEN '2' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '제품') WHEN '4' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '반제품') WHEN '0' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '원재료') WHEN '1' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '부재료') WHEN '5' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '상품') WHEN '6' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '저장품') WHEN '7' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '비용') WHEN '8' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '수익') Else dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '기타') END, COUNT(DISTINCT B.ITEMPARENT_CD) FROM SBOM B LEFT OUTER JOIN SITEM I ON B.CO_CD = I.CO_CD AND B.ITEMPARENT_CD = I.ITEM_CD WHERE I.CO_CD = @CO_CD GROUP BY I.ACCT_FG INSERT ##S_use_analysis_master(Type, Class, Qty) SELECT 'BOM BATCH', CASE I.ACCT_FG WHEN '2' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '제품') WHEN '4' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '반제품') WHEN '0' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '원재료') WHEN '1' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '부재료') WHEN '5' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '상품') WHEN '6' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '저장품') WHEN '7' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '비용') WHEN '8' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '수익') Else dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '기타') END, COUNT(DISTINCT B.ITEMPARENT_CD) FROM SBOM_B B LEFT OUTER JOIN SITEM I ON B.CO_CD = I.CO_CD AND B.ITEMPARENT_CD = I.ITEM_CD WHERE I.CO_CD = @CO_CD GROUP BY I.ACCT_FG INSERT ##S_use_analysis_master(Type, Class, Qty) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '단가'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '구매'), CASE VALUE_FG WHEN '0' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '없음') WHEN '1' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '품목단가') WHEN '2' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '거래처단가') WHEN '3' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '거래처관리단가') WHEN '4' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '품목유형단가') WHEN '5' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '사전단가') END FROM SL_CFG WHERE CO_CD = @CO_CD AND CFG_CD = 'P1' INSERT ##S_use_analysis_master(Type, Class, Qty) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '단가'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '판매'), CASE VALUE_FG WHEN '0' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '없음') WHEN '1' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '품목단가') WHEN '2' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '거래처단가') WHEN '3' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '거래처관리단가') WHEN '4' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '품목유형단가') WHEN '5' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '사전단가') END FROM SL_CFG WHERE CO_CD = @CO_CD AND CFG_CD = 'S1' INSERT ##S_use_analysis_master(Type, Class, Qty) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '거래처단가'), CASE I.ACCT_FG WHEN '2' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '제품') WHEN '4' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '반제품') WHEN '0' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '원재료') WHEN '1' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '부재료') WHEN '5' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '상품') WHEN '6' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '저장품') WHEN '7' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '비용') WHEN '8' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '수익') Else dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '기타') END, ISNULL(COUNT(*),0) FROM LCUSTM_UM B LEFT OUTER JOIN SITEM I ON B.CO_CD = I.CO_CD AND B.ITEM_CD = I.ITEM_CD WHERE I.CO_CD = @CO_CD GROUP BY I.ACCT_FG INSERT ##S_use_analysis_master(Type, Class, Qty) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '구매') + dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '거래처관리단가'), CASE I.ACCT_FG WHEN '2' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '제품') WHEN '4' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '반제품') WHEN '0' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '원재료') WHEN '1' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '부재료') WHEN '5' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '상품') WHEN '6' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '저장품') WHEN '7' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '비용') WHEN '8' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '수익') Else dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '기타') END, ISNULL(COUNT(*),0) FROM LPUR_UNITP B LEFT OUTER JOIN SITEM I ON B.CO_CD = I.CO_CD AND B.ITEM_CD = I.ITEM_CD WHERE I.CO_CD = @CO_CD GROUP BY I.ACCT_FG INSERT ##S_use_analysis_master(Type, Class, Qty) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '판매') + dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '거래처관리단가'), CASE I.ACCT_FG WHEN '2' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '제품') WHEN '4' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '반제품') WHEN '0' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '원재료') WHEN '1' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '부재료') WHEN '5' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '상품') WHEN '6' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '저장품') WHEN '7' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '비용') WHEN '8' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '수익') Else dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '기타') END, ISNULL(COUNT(*),0) FROM LSALE_UNITP B LEFT OUTER JOIN SITEM I ON B.CO_CD = I.CO_CD AND B.ITEM_CD = I.ITEM_CD WHERE I.CO_CD = @CO_CD GROUP BY I.ACCT_FG INSERT ##S_use_analysis_master(Type, Class, Qty) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '계정과목'), CASE GROUP_CD WHEN '1000' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '자산') WHEN '2000' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '부채') WHEN '3000' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '자본') WHEN '4000' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '비용') WHEN '5000' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '손익') Else dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '기타') END, ISNULL(COUNT(*),0) FROM SACCT WHERE CO_CD = @CO_CD GROUP BY GROUP_CD ORDER BY GROUP_CD INSERT ##S_use_analysis_master(Type, Class, Qty) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '고정자산'), '', ISNULL(COUNT(*),0) FROM ASSET WHERE CO_CD = @CO_CD INSERT ##S_use_analysis_master(Type, Class, Qty) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '거래처'), CASE TR_FG WHEN '1' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '일반') WHEN '2' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '무역') WHEN '3' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '개인') WHEN '4' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '거주') WHEN '5' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '금융') WHEN '6' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '저축') WHEN '7' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '예금') WHEN '8' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '카드') WHEN '9' THEN dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '신용') Else dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '기타') END, ISNULL(COUNT(*),0) FROM STRADE GROUP BY TR_FG INSERT ##S_use_analysis_master(Type, Class, Qty) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '마감'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '재고'), CTRL_DT FROM LCTRL_MTL WHERE CTRL_CD = 'MP' AND CO_CD = @CO_CD AND DIV_CD = @DIV_CD INSERT ##S_use_analysis_master(Type, Class, Qty) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '마감'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '판매'), CTRL_DT FROM LCTRL_MTL WHERE CTRL_CD = 'MS' AND CO_CD = @CO_CD AND DIV_CD = @DIV_CD INSERT ##S_use_analysis_master(Type, Class, Qty) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '마감'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '전표'), CLOSE_DT FROM SCLOSE WHERE CLOSE_CD = '01' AND CO_CD = @CO_CD AND DIV_CD = @DIV_CD INSERT ##S_use_analysis_master(Type, Class, Qty) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '마감'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '고정자산'), CLOSE_DT FROM SCLOSE WHERE CLOSE_CD = '04' AND CO_CD = @CO_CD AND DIV_CD = @DIV_CD --------------------------------------------------------------------------------- -- TRANSACTION -- FI: ADOCUH, ASSET -- MRP: LMPS, LDEMAND_D, LPUR_REQ_D -- PURCHASE: LPO_D, LQC_STK, LSTOCK_D, LPURCLS_D -- SALES: LSO_D, LISU_REQ_D, LDELIVER_D, LSALECLS_D -- PRODUCT: LWO, LWO_REQ, LSTKMOVE, LORCV_H, LMTL_USE, LRESUSE, LQC_INSP, LPRDINWH -- SYS: SITEM --------------------------------------------------------------------------------- INSERT ##S_use_analysis_transact (Module,Type,Class,Qty_1,Qty_2,Qty_3,Qty_4,Qty_5,Qty_6,Qty_7,Qty_8,Qty_9,Qty_10,Qty_11,Qty_12) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '기초'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '사원'), '', ISNULL(SUM(X.Qty_1),0), ISNULL(SUM(X.Qty_2),0), ISNULL(SUM(X.Qty_3),0), ISNULL(SUM(X.Qty_4),0), ISNULL(SUM(X.Qty_5),0), ISNULL(SUM(X.Qty_6),0), ISNULL(SUM(X.Qty_7),0), ISNULL(SUM(X.Qty_8),0), ISNULL(SUM(X.Qty_9),0), ISNULL(SUM(X.Qty_10),0), ISNULL(SUM(X.Qty_11),0), ISNULL(SUM(X.Qty_12),0) FROM (SELECT CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'01' THEN COUNT(*) ELSE 0 END Qty_1, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'02' THEN COUNT(*) ELSE 0 END Qty_2, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'03' THEN COUNT(*) ELSE 0 END Qty_3, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'04' THEN COUNT(*) ELSE 0 END Qty_4, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'05' THEN COUNT(*) ELSE 0 END Qty_5, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'06' THEN COUNT(*) ELSE 0 END Qty_6, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'07' THEN COUNT(*) ELSE 0 END Qty_7, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'08' THEN COUNT(*) ELSE 0 END Qty_8, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'09' THEN COUNT(*) ELSE 0 END Qty_9, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'10' THEN COUNT(*) ELSE 0 END Qty_10, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'11' THEN COUNT(*) ELSE 0 END Qty_11, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'12' THEN COUNT(*) ELSE 0 END Qty_12 FROM SEMP WHERE CO_CD = @CO_CD AND LEFT(CONVERT(VARCHAR, INSERT_DT, 112),4) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default GROUP BY LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) )X INSERT ##S_use_analysis_transact (Module,Type,Class,Qty_1,Qty_2,Qty_3,Qty_4,Qty_5,Qty_6,Qty_7,Qty_8,Qty_9,Qty_10,Qty_11,Qty_12) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '기초'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '품목'), '', ISNULL(SUM(X.Qty_1),0), ISNULL(SUM(X.Qty_2),0), ISNULL(SUM(X.Qty_3),0), ISNULL(SUM(X.Qty_4),0), ISNULL(SUM(X.Qty_5),0), ISNULL(SUM(X.Qty_6),0), ISNULL(SUM(X.Qty_7),0), ISNULL(SUM(X.Qty_8),0), ISNULL(SUM(X.Qty_9),0), ISNULL(SUM(X.Qty_10),0), ISNULL(SUM(X.Qty_11),0), ISNULL(SUM(X.Qty_12),0) FROM (SELECT CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'01' THEN COUNT(*) ELSE 0 END Qty_1, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'02' THEN COUNT(*) ELSE 0 END Qty_2, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'03' THEN COUNT(*) ELSE 0 END Qty_3, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'04' THEN COUNT(*) ELSE 0 END Qty_4, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'05' THEN COUNT(*) ELSE 0 END Qty_5, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'06' THEN COUNT(*) ELSE 0 END Qty_6, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'07' THEN COUNT(*) ELSE 0 END Qty_7, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'08' THEN COUNT(*) ELSE 0 END Qty_8, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'09' THEN COUNT(*) ELSE 0 END Qty_9, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'10' THEN COUNT(*) ELSE 0 END Qty_10, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'11' THEN COUNT(*) ELSE 0 END Qty_11, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'12' THEN COUNT(*) ELSE 0 END Qty_12 FROM SITEM WHERE CO_CD = @CO_CD AND LEFT(CONVERT(VARCHAR, INSERT_DT, 112),4) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default GROUP BY LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) )X INSERT ##S_use_analysis_transact (Module,Type,Class,Qty_1,Qty_2,Qty_3,Qty_4,Qty_5,Qty_6,Qty_7,Qty_8,Qty_9,Qty_10,Qty_11,Qty_12) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '기초'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, 'BOM'), '', ISNULL(SUM(X.Qty_1),0), ISNULL(SUM(X.Qty_2),0), ISNULL(SUM(X.Qty_3),0), ISNULL(SUM(X.Qty_4),0), ISNULL(SUM(X.Qty_5),0), ISNULL(SUM(X.Qty_6),0), ISNULL(SUM(X.Qty_7),0), ISNULL(SUM(X.Qty_8),0), ISNULL(SUM(X.Qty_9),0), ISNULL(SUM(X.Qty_10),0), ISNULL(SUM(X.Qty_11),0), ISNULL(SUM(X.Qty_12),0) FROM (SELECT CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'01' THEN COUNT(*) ELSE 0 END Qty_1, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'02' THEN COUNT(*) ELSE 0 END Qty_2, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'03' THEN COUNT(*) ELSE 0 END Qty_3, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'04' THEN COUNT(*) ELSE 0 END Qty_4, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'05' THEN COUNT(*) ELSE 0 END Qty_5, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'06' THEN COUNT(*) ELSE 0 END Qty_6, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'07' THEN COUNT(*) ELSE 0 END Qty_7, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'08' THEN COUNT(*) ELSE 0 END Qty_8, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'09' THEN COUNT(*) ELSE 0 END Qty_9, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'10' THEN COUNT(*) ELSE 0 END Qty_10, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'11' THEN COUNT(*) ELSE 0 END Qty_11, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'12' THEN COUNT(*) ELSE 0 END Qty_12 FROM SBOM WHERE CO_CD = @CO_CD AND LEFT(CONVERT(VARCHAR, INSERT_DT, 112),4) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default GROUP BY LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) )X INSERT ##S_use_analysis_transact (Module,Type,Class,Qty_1,Qty_2,Qty_3,Qty_4,Qty_5,Qty_6,Qty_7,Qty_8,Qty_9,Qty_10,Qty_11,Qty_12) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '기초'), 'Batch BOM', '', ISNULL(SUM(X.Qty_1),0), ISNULL(SUM(X.Qty_2),0), ISNULL(SUM(X.Qty_3),0), ISNULL(SUM(X.Qty_4),0), ISNULL(SUM(X.Qty_5),0), ISNULL(SUM(X.Qty_6),0), ISNULL(SUM(X.Qty_7),0), ISNULL(SUM(X.Qty_8),0), ISNULL(SUM(X.Qty_9),0), ISNULL(SUM(X.Qty_10),0), ISNULL(SUM(X.Qty_11),0), ISNULL(SUM(X.Qty_12),0) FROM (SELECT CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'01' THEN COUNT(*) ELSE 0 END Qty_1, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'02' THEN COUNT(*) ELSE 0 END Qty_2, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'03' THEN COUNT(*) ELSE 0 END Qty_3, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'04' THEN COUNT(*) ELSE 0 END Qty_4, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'05' THEN COUNT(*) ELSE 0 END Qty_5, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'06' THEN COUNT(*) ELSE 0 END Qty_6, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'07' THEN COUNT(*) ELSE 0 END Qty_7, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'08' THEN COUNT(*) ELSE 0 END Qty_8, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'09' THEN COUNT(*) ELSE 0 END Qty_9, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'10' THEN COUNT(*) ELSE 0 END Qty_10, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'11' THEN COUNT(*) ELSE 0 END Qty_11, CASE WHEN LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'12' THEN COUNT(*) ELSE 0 END Qty_12 FROM SBOM_B WHERE CO_CD = @CO_CD AND LEFT(CONVERT(VARCHAR, INSERT_DT, 112),4) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default GROUP BY LEFT(CONVERT(VARCHAR, INSERT_DT, 112),6) )X INSERT ##S_use_analysis_transact (Module,Type,Class,Qty_1,Qty_2,Qty_3,Qty_4,Qty_5,Qty_6,Qty_7,Qty_8,Qty_9,Qty_10,Qty_11,Qty_12) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, 'MRP'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, 'MPS'), '', ISNULL(SUM(X.Qty_1),0), ISNULL(SUM(X.Qty_2),0), ISNULL(SUM(X.Qty_3),0), ISNULL(SUM(X.Qty_4),0), ISNULL(SUM(X.Qty_5),0), ISNULL(SUM(X.Qty_6),0), ISNULL(SUM(X.Qty_7),0), ISNULL(SUM(X.Qty_8),0), ISNULL(SUM(X.Qty_9),0), ISNULL(SUM(X.Qty_10),0), ISNULL(SUM(X.Qty_11),0), ISNULL(SUM(X.Qty_12),0) FROM (SELECT CASE WHEN LEFT(MPS_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'01' THEN COUNT(*) ELSE 0 END Qty_1, CASE WHEN LEFT(MPS_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'02' THEN COUNT(*) ELSE 0 END Qty_2, CASE WHEN LEFT(MPS_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'03' THEN COUNT(*) ELSE 0 END Qty_3, CASE WHEN LEFT(MPS_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'04' THEN COUNT(*) ELSE 0 END Qty_4, CASE WHEN LEFT(MPS_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'05' THEN COUNT(*) ELSE 0 END Qty_5, CASE WHEN LEFT(MPS_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'06' THEN COUNT(*) ELSE 0 END Qty_6, CASE WHEN LEFT(MPS_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'07' THEN COUNT(*) ELSE 0 END Qty_7, CASE WHEN LEFT(MPS_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'08' THEN COUNT(*) ELSE 0 END Qty_8, CASE WHEN LEFT(MPS_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'09' THEN COUNT(*) ELSE 0 END Qty_9, CASE WHEN LEFT(MPS_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'10' THEN COUNT(*) ELSE 0 END Qty_10, CASE WHEN LEFT(MPS_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'11' THEN COUNT(*) ELSE 0 END Qty_11, CASE WHEN LEFT(MPS_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'12' THEN COUNT(*) ELSE 0 END Qty_12 FROM LMPS WHERE CO_CD = @CO_CD AND LEFT(MPS_DT,4) = @YYYY AND DIV_CD = @DIV_CD GROUP BY LEFT(MPS_DT,6) )X INSERT ##S_use_analysis_transact (Module,Type,Class,Qty_1,Qty_2,Qty_3,Qty_4,Qty_5,Qty_6,Qty_7,Qty_8,Qty_9,Qty_10,Qty_11,Qty_12) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, 'MRP'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '소요량'), '', ISNULL(SUM(X.Qty_1),0), ISNULL(SUM(X.Qty_2),0), ISNULL(SUM(X.Qty_3),0), ISNULL(SUM(X.Qty_4),0), ISNULL(SUM(X.Qty_5),0), ISNULL(SUM(X.Qty_6),0), ISNULL(SUM(X.Qty_7),0), ISNULL(SUM(X.Qty_8),0), ISNULL(SUM(X.Qty_9),0), ISNULL(SUM(X.Qty_10),0), ISNULL(SUM(X.Qty_11),0), ISNULL(SUM(X.Qty_12),0) FROM (SELECT CASE WHEN LEFT(SCHD_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'01' THEN COUNT(*) ELSE 0 END Qty_1, CASE WHEN LEFT(SCHD_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'02' THEN COUNT(*) ELSE 0 END Qty_2, CASE WHEN LEFT(SCHD_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'03' THEN COUNT(*) ELSE 0 END Qty_3, CASE WHEN LEFT(SCHD_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'04' THEN COUNT(*) ELSE 0 END Qty_4, CASE WHEN LEFT(SCHD_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'05' THEN COUNT(*) ELSE 0 END Qty_5, CASE WHEN LEFT(SCHD_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'06' THEN COUNT(*) ELSE 0 END Qty_6, CASE WHEN LEFT(SCHD_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'07' THEN COUNT(*) ELSE 0 END Qty_7, CASE WHEN LEFT(SCHD_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'08' THEN COUNT(*) ELSE 0 END Qty_8, CASE WHEN LEFT(SCHD_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'09' THEN COUNT(*) ELSE 0 END Qty_9, CASE WHEN LEFT(SCHD_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'10' THEN COUNT(*) ELSE 0 END Qty_10, CASE WHEN LEFT(SCHD_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'11' THEN COUNT(*) ELSE 0 END Qty_11, CASE WHEN LEFT(SCHD_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'12' THEN COUNT(*) ELSE 0 END Qty_12 FROM LDEMAND_D WHERE CO_CD = @CO_CD AND LEFT(SCHD_DT,4) = @YYYY AND DIV_CD = @DIV_CD GROUP BY LEFT(SCHD_DT,6) )X INSERT ##S_use_analysis_transact (Module,Type,Class,Qty_1,Qty_2,Qty_3,Qty_4,Qty_5,Qty_6,Qty_7,Qty_8,Qty_9,Qty_10,Qty_11,Qty_12) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, 'MRP'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '청구'), '', ISNULL(SUM(X.Qty_1),0), ISNULL(SUM(X.Qty_2),0), ISNULL(SUM(X.Qty_3),0), ISNULL(SUM(X.Qty_4),0), ISNULL(SUM(X.Qty_5),0), ISNULL(SUM(X.Qty_6),0), ISNULL(SUM(X.Qty_7),0), ISNULL(SUM(X.Qty_8),0), ISNULL(SUM(X.Qty_9),0), ISNULL(SUM(X.Qty_10),0), ISNULL(SUM(X.Qty_11),0), ISNULL(SUM(X.Qty_12),0) FROM (SELECT CASE WHEN LEFT(H.REQ_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'01' THEN COUNT(*) ELSE 0 END Qty_1, CASE WHEN LEFT(H.REQ_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'02' THEN COUNT(*) ELSE 0 END Qty_2, CASE WHEN LEFT(H.REQ_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'03' THEN COUNT(*) ELSE 0 END Qty_3, CASE WHEN LEFT(H.REQ_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'04' THEN COUNT(*) ELSE 0 END Qty_4, CASE WHEN LEFT(H.REQ_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'05' THEN COUNT(*) ELSE 0 END Qty_5, CASE WHEN LEFT(H.REQ_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'06' THEN COUNT(*) ELSE 0 END Qty_6, CASE WHEN LEFT(H.REQ_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'07' THEN COUNT(*) ELSE 0 END Qty_7, CASE WHEN LEFT(H.REQ_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'08' THEN COUNT(*) ELSE 0 END Qty_8, CASE WHEN LEFT(H.REQ_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'09' THEN COUNT(*) ELSE 0 END Qty_9, CASE WHEN LEFT(H.REQ_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'10' THEN COUNT(*) ELSE 0 END Qty_10, CASE WHEN LEFT(H.REQ_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'11' THEN COUNT(*) ELSE 0 END Qty_11, CASE WHEN LEFT(H.REQ_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'12' THEN COUNT(*) ELSE 0 END Qty_12 FROM LPUR_REQ_D D LEFT OUTER JOIN LPUR_REQ H ON D.CO_CD = H.CO_CD AND D.REQ_NB = H.REQ_NB WHERE D.CO_CD = @CO_CD AND LEFT(H.REQ_DT,4) = @YYYY AND DIV_CD = @DIV_CD GROUP BY LEFT(H.REQ_DT,6) )X INSERT ##S_use_analysis_transact (Module,Type,Class,Qty_1,Qty_2,Qty_3,Qty_4,Qty_5,Qty_6,Qty_7,Qty_8,Qty_9,Qty_10,Qty_11,Qty_12) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '구매'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '발주'), '', ISNULL(SUM(X.Qty_1),0), ISNULL(SUM(X.Qty_2),0), ISNULL(SUM(X.Qty_3),0), ISNULL(SUM(X.Qty_4),0), ISNULL(SUM(X.Qty_5),0), ISNULL(SUM(X.Qty_6),0), ISNULL(SUM(X.Qty_7),0), ISNULL(SUM(X.Qty_8),0), ISNULL(SUM(X.Qty_9),0), ISNULL(SUM(X.Qty_10),0), ISNULL(SUM(X.Qty_11),0), ISNULL(SUM(X.Qty_12),0) FROM (SELECT CASE WHEN LEFT(H.PO_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'01' THEN COUNT(*) ELSE 0 END Qty_1, CASE WHEN LEFT(H.PO_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'02' THEN COUNT(*) ELSE 0 END Qty_2, CASE WHEN LEFT(H.PO_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'03' THEN COUNT(*) ELSE 0 END Qty_3, CASE WHEN LEFT(H.PO_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'04' THEN COUNT(*) ELSE 0 END Qty_4, CASE WHEN LEFT(H.PO_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'05' THEN COUNT(*) ELSE 0 END Qty_5, CASE WHEN LEFT(H.PO_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'06' THEN COUNT(*) ELSE 0 END Qty_6, CASE WHEN LEFT(H.PO_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'07' THEN COUNT(*) ELSE 0 END Qty_7, CASE WHEN LEFT(H.PO_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'08' THEN COUNT(*) ELSE 0 END Qty_8, CASE WHEN LEFT(H.PO_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'09' THEN COUNT(*) ELSE 0 END Qty_9, CASE WHEN LEFT(H.PO_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'10' THEN COUNT(*) ELSE 0 END Qty_10, CASE WHEN LEFT(H.PO_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'11' THEN COUNT(*) ELSE 0 END Qty_11, CASE WHEN LEFT(H.PO_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'12' THEN COUNT(*) ELSE 0 END Qty_12 FROM LPO_D D LEFT OUTER JOIN LPO H ON D.CO_CD = H.CO_CD AND D.PO_NB = H.PO_NB WHERE D.CO_CD = @CO_CD AND LEFT(H.PO_DT,4) = @YYYY AND DIV_CD = @DIV_CD GROUP BY LEFT(H.PO_DT,6) )X INSERT ##S_use_analysis_transact (Module,Type,Class,Qty_1,Qty_2,Qty_3,Qty_4,Qty_5,Qty_6,Qty_7,Qty_8,Qty_9,Qty_10,Qty_11,Qty_12) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '구매'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '입고검사'), '', ISNULL(SUM(X.Qty_1),0), ISNULL(SUM(X.Qty_2),0), ISNULL(SUM(X.Qty_3),0), ISNULL(SUM(X.Qty_4),0), ISNULL(SUM(X.Qty_5),0), ISNULL(SUM(X.Qty_6),0), ISNULL(SUM(X.Qty_7),0), ISNULL(SUM(X.Qty_8),0), ISNULL(SUM(X.Qty_9),0), ISNULL(SUM(X.Qty_10),0), ISNULL(SUM(X.Qty_11),0), ISNULL(SUM(X.Qty_12),0) FROM (SELECT CASE WHEN LEFT(QC_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'01' THEN COUNT(*) ELSE 0 END Qty_1, CASE WHEN LEFT(QC_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'02' THEN COUNT(*) ELSE 0 END Qty_2, CASE WHEN LEFT(QC_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'03' THEN COUNT(*) ELSE 0 END Qty_3, CASE WHEN LEFT(QC_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'04' THEN COUNT(*) ELSE 0 END Qty_4, CASE WHEN LEFT(QC_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'05' THEN COUNT(*) ELSE 0 END Qty_5, CASE WHEN LEFT(QC_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'06' THEN COUNT(*) ELSE 0 END Qty_6, CASE WHEN LEFT(QC_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'07' THEN COUNT(*) ELSE 0 END Qty_7, CASE WHEN LEFT(QC_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'08' THEN COUNT(*) ELSE 0 END Qty_8, CASE WHEN LEFT(QC_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'09' THEN COUNT(*) ELSE 0 END Qty_9, CASE WHEN LEFT(QC_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'10' THEN COUNT(*) ELSE 0 END Qty_10, CASE WHEN LEFT(QC_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'11' THEN COUNT(*) ELSE 0 END Qty_11, CASE WHEN LEFT(QC_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'12' THEN COUNT(*) ELSE 0 END Qty_12 FROM LQC_STK WHERE CO_CD = @CO_CD AND LEFT(QC_DT,4) = @YYYY AND DIV_CD = @DIV_CD GROUP BY LEFT(QC_DT,6) )X INSERT ##S_use_analysis_transact (Module,Type,Class,Qty_1,Qty_2,Qty_3,Qty_4,Qty_5,Qty_6,Qty_7,Qty_8,Qty_9,Qty_10,Qty_11,Qty_12) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '구매'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '입고'), '', ISNULL(SUM(X.Qty_1),0), ISNULL(SUM(X.Qty_2),0), ISNULL(SUM(X.Qty_3),0), ISNULL(SUM(X.Qty_4),0), ISNULL(SUM(X.Qty_5),0), ISNULL(SUM(X.Qty_6),0), ISNULL(SUM(X.Qty_7),0), ISNULL(SUM(X.Qty_8),0), ISNULL(SUM(X.Qty_9),0), ISNULL(SUM(X.Qty_10),0), ISNULL(SUM(X.Qty_11),0), ISNULL(SUM(X.Qty_12),0) FROM (SELECT CASE WHEN LEFT(H.RCV_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'01' THEN COUNT(*) ELSE 0 END Qty_1, CASE WHEN LEFT(H.RCV_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'02' THEN COUNT(*) ELSE 0 END Qty_2, CASE WHEN LEFT(H.RCV_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'03' THEN COUNT(*) ELSE 0 END Qty_3, CASE WHEN LEFT(H.RCV_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'04' THEN COUNT(*) ELSE 0 END Qty_4, CASE WHEN LEFT(H.RCV_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'05' THEN COUNT(*) ELSE 0 END Qty_5, CASE WHEN LEFT(H.RCV_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'06' THEN COUNT(*) ELSE 0 END Qty_6, CASE WHEN LEFT(H.RCV_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'07' THEN COUNT(*) ELSE 0 END Qty_7, CASE WHEN LEFT(H.RCV_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'08' THEN COUNT(*) ELSE 0 END Qty_8, CASE WHEN LEFT(H.RCV_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'09' THEN COUNT(*) ELSE 0 END Qty_9, CASE WHEN LEFT(H.RCV_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'10' THEN COUNT(*) ELSE 0 END Qty_10, CASE WHEN LEFT(H.RCV_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'11' THEN COUNT(*) ELSE 0 END Qty_11, CASE WHEN LEFT(H.RCV_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'12' THEN COUNT(*) ELSE 0 END Qty_12 FROM LSTOCK_D D LEFT OUTER JOIN LSTOCK H ON D.CO_CD = H.CO_CD AND D.RCV_NB = H.RCV_NB WHERE D.CO_CD = @CO_CD AND LEFT(H.RCV_DT,4) = @YYYY AND DIV_CD = @DIV_CD GROUP BY LEFT(H.RCV_DT,6) )X INSERT ##S_use_analysis_transact (Module,Type,Class,Qty_1,Qty_2,Qty_3,Qty_4,Qty_5,Qty_6,Qty_7,Qty_8,Qty_9,Qty_10,Qty_11,Qty_12) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '구매'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '마감'), '', ISNULL(SUM(X.Qty_1),0), ISNULL(SUM(X.Qty_2),0), ISNULL(SUM(X.Qty_3),0), ISNULL(SUM(X.Qty_4),0), ISNULL(SUM(X.Qty_5),0), ISNULL(SUM(X.Qty_6),0), ISNULL(SUM(X.Qty_7),0), ISNULL(SUM(X.Qty_8),0), ISNULL(SUM(X.Qty_9),0), ISNULL(SUM(X.Qty_10),0), ISNULL(SUM(X.Qty_11),0), ISNULL(SUM(X.Qty_12),0) FROM (SELECT CASE WHEN LEFT(H.CLS_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'01' THEN COUNT(*) ELSE 0 END Qty_1, CASE WHEN LEFT(H.CLS_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'02' THEN COUNT(*) ELSE 0 END Qty_2, CASE WHEN LEFT(H.CLS_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'03' THEN COUNT(*) ELSE 0 END Qty_3, CASE WHEN LEFT(H.CLS_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'04' THEN COUNT(*) ELSE 0 END Qty_4, CASE WHEN LEFT(H.CLS_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'05' THEN COUNT(*) ELSE 0 END Qty_5, CASE WHEN LEFT(H.CLS_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'06' THEN COUNT(*) ELSE 0 END Qty_6, CASE WHEN LEFT(H.CLS_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'07' THEN COUNT(*) ELSE 0 END Qty_7, CASE WHEN LEFT(H.CLS_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'08' THEN COUNT(*) ELSE 0 END Qty_8, CASE WHEN LEFT(H.CLS_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'09' THEN COUNT(*) ELSE 0 END Qty_9, CASE WHEN LEFT(H.CLS_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'10' THEN COUNT(*) ELSE 0 END Qty_10, CASE WHEN LEFT(H.CLS_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'11' THEN COUNT(*) ELSE 0 END Qty_11, CASE WHEN LEFT(H.CLS_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'12' THEN COUNT(*) ELSE 0 END Qty_12 FROM LPURCLS_D D LEFT OUTER JOIN LPURCLS H ON D.CO_CD = H.CO_CD AND D.CLS_NB = H.CLS_NB WHERE D.CO_CD = @CO_CD AND LEFT(H.CLS_DT,4) = @YYYY AND DIV_CD = @DIV_CD GROUP BY LEFT(H.CLS_DT,6) )X INSERT ##S_use_analysis_transact (Module,Type,Class,Qty_1,Qty_2,Qty_3,Qty_4,Qty_5,Qty_6,Qty_7,Qty_8,Qty_9,Qty_10,Qty_11,Qty_12) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '생산'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '작업지시'), 'Head', ISNULL(SUM(X.Qty_1),0), ISNULL(SUM(X.Qty_2),0), ISNULL(SUM(X.Qty_3),0), ISNULL(SUM(X.Qty_4),0), ISNULL(SUM(X.Qty_5),0), ISNULL(SUM(X.Qty_6),0), ISNULL(SUM(X.Qty_7),0), ISNULL(SUM(X.Qty_8),0), ISNULL(SUM(X.Qty_9),0), ISNULL(SUM(X.Qty_10),0), ISNULL(SUM(X.Qty_11),0), ISNULL(SUM(X.Qty_12),0) FROM (SELECT CASE WHEN LEFT(ORD_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'01' THEN COUNT(*) ELSE 0 END Qty_1, CASE WHEN LEFT(ORD_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'02' THEN COUNT(*) ELSE 0 END Qty_2, CASE WHEN LEFT(ORD_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'03' THEN COUNT(*) ELSE 0 END Qty_3, CASE WHEN LEFT(ORD_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'04' THEN COUNT(*) ELSE 0 END Qty_4, CASE WHEN LEFT(ORD_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'05' THEN COUNT(*) ELSE 0 END Qty_5, CASE WHEN LEFT(ORD_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'06' THEN COUNT(*) ELSE 0 END Qty_6, CASE WHEN LEFT(ORD_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'07' THEN COUNT(*) ELSE 0 END Qty_7, CASE WHEN LEFT(ORD_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'08' THEN COUNT(*) ELSE 0 END Qty_8, CASE WHEN LEFT(ORD_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'09' THEN COUNT(*) ELSE 0 END Qty_9, CASE WHEN LEFT(ORD_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'10' THEN COUNT(*) ELSE 0 END Qty_10, CASE WHEN LEFT(ORD_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'11' THEN COUNT(*) ELSE 0 END Qty_11, CASE WHEN LEFT(ORD_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'12' THEN COUNT(*) ELSE 0 END Qty_12 FROM LWO_WF WHERE CO_CD = @CO_CD AND LEFT(ORD_DT,4) = @YYYY AND DIV_CD = @DIV_CD GROUP BY LEFT(ORD_DT,6) )X INSERT ##S_use_analysis_transact (Module,Type,Class,Qty_1,Qty_2,Qty_3,Qty_4,Qty_5,Qty_6,Qty_7,Qty_8,Qty_9,Qty_10,Qty_11,Qty_12) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '생산'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '작업지시'), 'Detail', ISNULL(SUM(X.Qty_1),0), ISNULL(SUM(X.Qty_2),0), ISNULL(SUM(X.Qty_3),0), ISNULL(SUM(X.Qty_4),0), ISNULL(SUM(X.Qty_5),0), ISNULL(SUM(X.Qty_6),0), ISNULL(SUM(X.Qty_7),0), ISNULL(SUM(X.Qty_8),0), ISNULL(SUM(X.Qty_9),0), ISNULL(SUM(X.Qty_10),0), ISNULL(SUM(X.Qty_11),0), ISNULL(SUM(X.Qty_12),0) FROM (SELECT CASE WHEN LEFT(LWO_WF_D.ORD_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'01' THEN COUNT(*) ELSE 0 END Qty_1, CASE WHEN LEFT(LWO_WF_D.ORD_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'02' THEN COUNT(*) ELSE 0 END Qty_2, CASE WHEN LEFT(LWO_WF_D.ORD_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'03' THEN COUNT(*) ELSE 0 END Qty_3, CASE WHEN LEFT(LWO_WF_D.ORD_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'04' THEN COUNT(*) ELSE 0 END Qty_4, CASE WHEN LEFT(LWO_WF_D.ORD_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'05' THEN COUNT(*) ELSE 0 END Qty_5, CASE WHEN LEFT(LWO_WF_D.ORD_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'06' THEN COUNT(*) ELSE 0 END Qty_6, CASE WHEN LEFT(LWO_WF_D.ORD_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'07' THEN COUNT(*) ELSE 0 END Qty_7, CASE WHEN LEFT(LWO_WF_D.ORD_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'08' THEN COUNT(*) ELSE 0 END Qty_8, CASE WHEN LEFT(LWO_WF_D.ORD_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'09' THEN COUNT(*) ELSE 0 END Qty_9, CASE WHEN LEFT(LWO_WF_D.ORD_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'10' THEN COUNT(*) ELSE 0 END Qty_10, CASE WHEN LEFT(LWO_WF_D.ORD_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'11' THEN COUNT(*) ELSE 0 END Qty_11, CASE WHEN LEFT(LWO_WF_D.ORD_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'12' THEN COUNT(*) ELSE 0 END Qty_12 FROM LWO_WF_D LEFT OUTER JOIN LWO_WF ON LWO_WF_D.CO_CD = LWO_WF.CO_CD AND LWO_WF.WO_CD = LWO_WF_D.WO_CD WHERE LWO_WF_D.CO_CD = @CO_CD AND LEFT(LWO_WF_D.ORD_DT,4) = @YYYY AND LWO_WF.DIV_CD = @DIV_CD GROUP BY LEFT(LWO_WF_D.ORD_DT,6) )X INSERT ##S_use_analysis_transact (Module,Type,Class,Qty_1,Qty_2,Qty_3,Qty_4,Qty_5,Qty_6,Qty_7,Qty_8,Qty_9,Qty_10,Qty_11,Qty_12) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '생산'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '자재청구'), '', ISNULL(SUM(X.Qty_1),0), ISNULL(SUM(X.Qty_2),0), ISNULL(SUM(X.Qty_3),0), ISNULL(SUM(X.Qty_4),0), ISNULL(SUM(X.Qty_5),0), ISNULL(SUM(X.Qty_6),0), ISNULL(SUM(X.Qty_7),0), ISNULL(SUM(X.Qty_8),0), ISNULL(SUM(X.Qty_9),0), ISNULL(SUM(X.Qty_10),0), ISNULL(SUM(X.Qty_11),0), ISNULL(SUM(X.Qty_12),0) FROM (SELECT CASE WHEN LEFT(REQ_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'01' THEN COUNT(*) ELSE 0 END Qty_1, CASE WHEN LEFT(REQ_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'02' THEN COUNT(*) ELSE 0 END Qty_2, CASE WHEN LEFT(REQ_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'03' THEN COUNT(*) ELSE 0 END Qty_3, CASE WHEN LEFT(REQ_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'04' THEN COUNT(*) ELSE 0 END Qty_4, CASE WHEN LEFT(REQ_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'05' THEN COUNT(*) ELSE 0 END Qty_5, CASE WHEN LEFT(REQ_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'06' THEN COUNT(*) ELSE 0 END Qty_6, CASE WHEN LEFT(REQ_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'07' THEN COUNT(*) ELSE 0 END Qty_7, CASE WHEN LEFT(REQ_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'08' THEN COUNT(*) ELSE 0 END Qty_8, CASE WHEN LEFT(REQ_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'09' THEN COUNT(*) ELSE 0 END Qty_9, CASE WHEN LEFT(REQ_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'10' THEN COUNT(*) ELSE 0 END Qty_10, CASE WHEN LEFT(REQ_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'11' THEN COUNT(*) ELSE 0 END Qty_11, CASE WHEN LEFT(REQ_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'12' THEN COUNT(*) ELSE 0 END Qty_12 FROM LWO_REQ_WF WHERE CO_CD = @CO_CD AND LEFT(REQ_DT,4) = @YYYY AND DIV_CD = @DIV_CD GROUP BY LEFT(REQ_DT,6) )X INSERT ##S_use_analysis_transact (Module,Type,Class,Qty_1,Qty_2,Qty_3,Qty_4,Qty_5,Qty_6,Qty_7,Qty_8,Qty_9,Qty_10,Qty_11,Qty_12) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '생산'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '투입'), '', ISNULL(SUM(X.Qty_1),0), ISNULL(SUM(X.Qty_2),0), ISNULL(SUM(X.Qty_3),0), ISNULL(SUM(X.Qty_4),0), ISNULL(SUM(X.Qty_5),0), ISNULL(SUM(X.Qty_6),0), ISNULL(SUM(X.Qty_7),0), ISNULL(SUM(X.Qty_8),0), ISNULL(SUM(X.Qty_9),0), ISNULL(SUM(X.Qty_10),0), ISNULL(SUM(X.Qty_11),0), ISNULL(SUM(X.Qty_12),0) FROM (SELECT CASE WHEN LEFT(H.MOVE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'01' THEN COUNT(*) ELSE 0 END Qty_1, CASE WHEN LEFT(H.MOVE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'02' THEN COUNT(*) ELSE 0 END Qty_2, CASE WHEN LEFT(H.MOVE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'03' THEN COUNT(*) ELSE 0 END Qty_3, CASE WHEN LEFT(H.MOVE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'04' THEN COUNT(*) ELSE 0 END Qty_4, CASE WHEN LEFT(H.MOVE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'05' THEN COUNT(*) ELSE 0 END Qty_5, CASE WHEN LEFT(H.MOVE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'06' THEN COUNT(*) ELSE 0 END Qty_6, CASE WHEN LEFT(H.MOVE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'07' THEN COUNT(*) ELSE 0 END Qty_7, CASE WHEN LEFT(H.MOVE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'08' THEN COUNT(*) ELSE 0 END Qty_8, CASE WHEN LEFT(H.MOVE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'09' THEN COUNT(*) ELSE 0 END Qty_9, CASE WHEN LEFT(H.MOVE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'10' THEN COUNT(*) ELSE 0 END Qty_10, CASE WHEN LEFT(H.MOVE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'11' THEN COUNT(*) ELSE 0 END Qty_11, CASE WHEN LEFT(H.MOVE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'12' THEN COUNT(*) ELSE 0 END Qty_12 FROM LSTKMOVE_D D LEFT OUTER JOIN LSTKMOVE H ON D.CO_CD = H.CO_CD AND D.MOVE_NB = H.MOVE_NB WHERE H.CO_CD = @CO_CD AND LEFT(H.MOVE_DT,4) = @YYYY AND H.GRP_FG = '0' AND IO_FG = '2' AND H.DIV_CD = @DIV_CD GROUP BY LEFT(H.MOVE_DT,6) )X INSERT ##S_use_analysis_transact (Module,Type,Class,Qty_1,Qty_2,Qty_3,Qty_4,Qty_5,Qty_6,Qty_7,Qty_8,Qty_9,Qty_10,Qty_11,Qty_12) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '생산'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '작업실적'), '', ISNULL(SUM(X.Qty_1),0), ISNULL(SUM(X.Qty_2),0), ISNULL(SUM(X.Qty_3),0), ISNULL(SUM(X.Qty_4),0), ISNULL(SUM(X.Qty_5),0), ISNULL(SUM(X.Qty_6),0), ISNULL(SUM(X.Qty_7),0), ISNULL(SUM(X.Qty_8),0), ISNULL(SUM(X.Qty_9),0), ISNULL(SUM(X.Qty_10),0), ISNULL(SUM(X.Qty_11),0), ISNULL(SUM(X.Qty_12),0) FROM (SELECT CASE WHEN LEFT(DOC_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'01' THEN COUNT(*) ELSE 0 END Qty_1, CASE WHEN LEFT(DOC_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'02' THEN COUNT(*) ELSE 0 END Qty_2, CASE WHEN LEFT(DOC_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'03' THEN COUNT(*) ELSE 0 END Qty_3, CASE WHEN LEFT(DOC_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'04' THEN COUNT(*) ELSE 0 END Qty_4, CASE WHEN LEFT(DOC_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'05' THEN COUNT(*) ELSE 0 END Qty_5, CASE WHEN LEFT(DOC_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'06' THEN COUNT(*) ELSE 0 END Qty_6, CASE WHEN LEFT(DOC_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'07' THEN COUNT(*) ELSE 0 END Qty_7, CASE WHEN LEFT(DOC_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'08' THEN COUNT(*) ELSE 0 END Qty_8, CASE WHEN LEFT(DOC_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'09' THEN COUNT(*) ELSE 0 END Qty_9, CASE WHEN LEFT(DOC_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'10' THEN COUNT(*) ELSE 0 END Qty_10, CASE WHEN LEFT(DOC_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'11' THEN COUNT(*) ELSE 0 END Qty_11, CASE WHEN LEFT(DOC_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'12' THEN COUNT(*) ELSE 0 END Qty_12 FROM LORCV_H WHERE CO_CD = @CO_CD AND LEFT(DOC_DT,4) = @YYYY AND DIV_CD = @DIV_CD GROUP BY LEFT(DOC_DT,6) )X INSERT ##S_use_analysis_transact (Module,Type,Class,Qty_1,Qty_2,Qty_3,Qty_4,Qty_5,Qty_6,Qty_7,Qty_8,Qty_9,Qty_10,Qty_11,Qty_12) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '생산'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '사용')+dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '자재'), '', ISNULL(SUM(X.Qty_1),0), ISNULL(SUM(X.Qty_2),0), ISNULL(SUM(X.Qty_3),0), ISNULL(SUM(X.Qty_4),0), ISNULL(SUM(X.Qty_5),0), ISNULL(SUM(X.Qty_6),0), ISNULL(SUM(X.Qty_7),0), ISNULL(SUM(X.Qty_8),0), ISNULL(SUM(X.Qty_9),0), ISNULL(SUM(X.Qty_10),0), ISNULL(SUM(X.Qty_11),0), ISNULL(SUM(X.Qty_12),0) FROM (SELECT CASE WHEN LEFT(USE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'01' THEN COUNT(DISTINCT WR_CD) ELSE 0 END Qty_1, CASE WHEN LEFT(USE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'02' THEN COUNT(DISTINCT WR_CD) ELSE 0 END Qty_2, CASE WHEN LEFT(USE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'03' THEN COUNT(DISTINCT WR_CD) ELSE 0 END Qty_3, CASE WHEN LEFT(USE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'04' THEN COUNT(DISTINCT WR_CD) ELSE 0 END Qty_4, CASE WHEN LEFT(USE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'05' THEN COUNT(DISTINCT WR_CD) ELSE 0 END Qty_5, CASE WHEN LEFT(USE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'06' THEN COUNT(DISTINCT WR_CD) ELSE 0 END Qty_6, CASE WHEN LEFT(USE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'07' THEN COUNT(DISTINCT WR_CD) ELSE 0 END Qty_7, CASE WHEN LEFT(USE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'08' THEN COUNT(DISTINCT WR_CD) ELSE 0 END Qty_8, CASE WHEN LEFT(USE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'09' THEN COUNT(DISTINCT WR_CD) ELSE 0 END Qty_9, CASE WHEN LEFT(USE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'10' THEN COUNT(DISTINCT WR_CD) ELSE 0 END Qty_10, CASE WHEN LEFT(USE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'11' THEN COUNT(DISTINCT WR_CD) ELSE 0 END Qty_11, CASE WHEN LEFT(USE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'12' THEN COUNT(DISTINCT WR_CD) ELSE 0 END Qty_12 FROM LMTL_USE WHERE CO_CD = @CO_CD AND LEFT(USE_DT,4) = @YYYY AND DIV_CD = @DIV_CD GROUP BY LEFT(USE_DT,6) )X INSERT ##S_use_analysis_transact (Module,Type,Class,Qty_1,Qty_2,Qty_3,Qty_4,Qty_5,Qty_6,Qty_7,Qty_8,Qty_9,Qty_10,Qty_11,Qty_12) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '생산'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '사용')+dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '자원'), '', ISNULL(SUM(X.Qty_1),0), ISNULL(SUM(X.Qty_2),0), ISNULL(SUM(X.Qty_3),0), ISNULL(SUM(X.Qty_4),0), ISNULL(SUM(X.Qty_5),0), ISNULL(SUM(X.Qty_6),0), ISNULL(SUM(X.Qty_7),0), ISNULL(SUM(X.Qty_8),0), ISNULL(SUM(X.Qty_9),0), ISNULL(SUM(X.Qty_10),0), ISNULL(SUM(X.Qty_11),0), ISNULL(SUM(X.Qty_12),0) FROM (SELECT CASE WHEN LEFT(USE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'01' THEN COUNT(DISTINCT DOC_CD) ELSE 0 END Qty_1, CASE WHEN LEFT(USE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'02' THEN COUNT(DISTINCT DOC_CD) ELSE 0 END Qty_2, CASE WHEN LEFT(USE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'03' THEN COUNT(DISTINCT DOC_CD) ELSE 0 END Qty_3, CASE WHEN LEFT(USE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'04' THEN COUNT(DISTINCT DOC_CD) ELSE 0 END Qty_4, CASE WHEN LEFT(USE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'05' THEN COUNT(DISTINCT DOC_CD) ELSE 0 END Qty_5, CASE WHEN LEFT(USE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'06' THEN COUNT(DISTINCT DOC_CD) ELSE 0 END Qty_6, CASE WHEN LEFT(USE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'07' THEN COUNT(DISTINCT DOC_CD) ELSE 0 END Qty_7, CASE WHEN LEFT(USE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'08' THEN COUNT(DISTINCT DOC_CD) ELSE 0 END Qty_8, CASE WHEN LEFT(USE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'09' THEN COUNT(DISTINCT DOC_CD) ELSE 0 END Qty_9, CASE WHEN LEFT(USE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'10' THEN COUNT(DISTINCT DOC_CD) ELSE 0 END Qty_10, CASE WHEN LEFT(USE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'11' THEN COUNT(DISTINCT DOC_CD) ELSE 0 END Qty_11, CASE WHEN LEFT(USE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'12' THEN COUNT(DISTINCT DOC_CD) ELSE 0 END Qty_12 FROM LRESUSE WHERE CO_CD = @CO_CD AND LEFT(USE_DT,4) = @YYYY AND DIV_CD = @DIV_CD GROUP BY LEFT(USE_DT,6) )X INSERT ##S_use_analysis_transact (Module,Type,Class,Qty_1,Qty_2,Qty_3,Qty_4,Qty_5,Qty_6,Qty_7,Qty_8,Qty_9,Qty_10,Qty_11,Qty_12) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '생산'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '검사'), '', ISNULL(SUM(X.Qty_1),0), ISNULL(SUM(X.Qty_2),0), ISNULL(SUM(X.Qty_3),0), ISNULL(SUM(X.Qty_4),0), ISNULL(SUM(X.Qty_5),0), ISNULL(SUM(X.Qty_6),0), ISNULL(SUM(X.Qty_7),0), ISNULL(SUM(X.Qty_8),0), ISNULL(SUM(X.Qty_9),0), ISNULL(SUM(X.Qty_10),0), ISNULL(SUM(X.Qty_11),0), ISNULL(SUM(X.Qty_12),0) FROM (SELECT CASE WHEN LEFT(DOC_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'01' THEN COUNT(*) ELSE 0 END Qty_1, CASE WHEN LEFT(DOC_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'02' THEN COUNT(*) ELSE 0 END Qty_2, CASE WHEN LEFT(DOC_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'03' THEN COUNT(*) ELSE 0 END Qty_3, CASE WHEN LEFT(DOC_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'04' THEN COUNT(*) ELSE 0 END Qty_4, CASE WHEN LEFT(DOC_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'05' THEN COUNT(*) ELSE 0 END Qty_5, CASE WHEN LEFT(DOC_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'06' THEN COUNT(*) ELSE 0 END Qty_6, CASE WHEN LEFT(DOC_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'07' THEN COUNT(*) ELSE 0 END Qty_7, CASE WHEN LEFT(DOC_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'08' THEN COUNT(*) ELSE 0 END Qty_8, CASE WHEN LEFT(DOC_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'09' THEN COUNT(*) ELSE 0 END Qty_9, CASE WHEN LEFT(DOC_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'10' THEN COUNT(*) ELSE 0 END Qty_10, CASE WHEN LEFT(DOC_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'11' THEN COUNT(*) ELSE 0 END Qty_11, CASE WHEN LEFT(DOC_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'12' THEN COUNT(*) ELSE 0 END Qty_12 FROM LQC_INSP WHERE CO_CD = @CO_CD AND LEFT(DOC_DT,4) = @YYYY AND DIV_CD = @DIV_CD GROUP BY LEFT(DOC_DT,6) )X INSERT ##S_use_analysis_transact (Module,Type,Class,Qty_1,Qty_2,Qty_3,Qty_4,Qty_5,Qty_6,Qty_7,Qty_8,Qty_9,Qty_10,Qty_11,Qty_12) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '생산'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '실적입고'), '', ISNULL(SUM(X.Qty_1),0), ISNULL(SUM(X.Qty_2),0), ISNULL(SUM(X.Qty_3),0), ISNULL(SUM(X.Qty_4),0), ISNULL(SUM(X.Qty_5),0), ISNULL(SUM(X.Qty_6),0), ISNULL(SUM(X.Qty_7),0), ISNULL(SUM(X.Qty_8),0), ISNULL(SUM(X.Qty_9),0), ISNULL(SUM(X.Qty_10),0), ISNULL(SUM(X.Qty_11),0), ISNULL(SUM(X.Qty_12),0) FROM (SELECT CASE WHEN LEFT(INWH_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'01' THEN COUNT(*) ELSE 0 END Qty_1, CASE WHEN LEFT(INWH_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'02' THEN COUNT(*) ELSE 0 END Qty_2, CASE WHEN LEFT(INWH_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'03' THEN COUNT(*) ELSE 0 END Qty_3, CASE WHEN LEFT(INWH_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'04' THEN COUNT(*) ELSE 0 END Qty_4, CASE WHEN LEFT(INWH_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'05' THEN COUNT(*) ELSE 0 END Qty_5, CASE WHEN LEFT(INWH_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'06' THEN COUNT(*) ELSE 0 END Qty_6, CASE WHEN LEFT(INWH_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'07' THEN COUNT(*) ELSE 0 END Qty_7, CASE WHEN LEFT(INWH_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'08' THEN COUNT(*) ELSE 0 END Qty_8, CASE WHEN LEFT(INWH_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'09' THEN COUNT(*) ELSE 0 END Qty_9, CASE WHEN LEFT(INWH_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'10' THEN COUNT(*) ELSE 0 END Qty_10, CASE WHEN LEFT(INWH_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'11' THEN COUNT(*) ELSE 0 END Qty_11, CASE WHEN LEFT(INWH_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'12' THEN COUNT(*) ELSE 0 END Qty_12 FROM LPRDINWH WHERE CO_CD = @CO_CD AND LEFT(INWH_DT,4) = @YYYY AND DIV_CD = @DIV_CD GROUP BY LEFT(INWH_DT,6) )X INSERT ##S_use_analysis_transact (Module,Type,Class,Qty_1,Qty_2,Qty_3,Qty_4,Qty_5,Qty_6,Qty_7,Qty_8,Qty_9,Qty_10,Qty_11,Qty_12) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '판매'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '수주'), '', ISNULL(SUM(X.Qty_1),0), ISNULL(SUM(X.Qty_2),0), ISNULL(SUM(X.Qty_3),0), ISNULL(SUM(X.Qty_4),0), ISNULL(SUM(X.Qty_5),0), ISNULL(SUM(X.Qty_6),0), ISNULL(SUM(X.Qty_7),0), ISNULL(SUM(X.Qty_8),0), ISNULL(SUM(X.Qty_9),0), ISNULL(SUM(X.Qty_10),0), ISNULL(SUM(X.Qty_11),0), ISNULL(SUM(X.Qty_12),0) FROM (SELECT CASE WHEN LEFT(H.SO_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'01' THEN COUNT(*) ELSE 0 END Qty_1, CASE WHEN LEFT(H.SO_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'02' THEN COUNT(*) ELSE 0 END Qty_2, CASE WHEN LEFT(H.SO_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'03' THEN COUNT(*) ELSE 0 END Qty_3, CASE WHEN LEFT(H.SO_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'04' THEN COUNT(*) ELSE 0 END Qty_4, CASE WHEN LEFT(H.SO_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'05' THEN COUNT(*) ELSE 0 END Qty_5, CASE WHEN LEFT(H.SO_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'06' THEN COUNT(*) ELSE 0 END Qty_6, CASE WHEN LEFT(H.SO_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'07' THEN COUNT(*) ELSE 0 END Qty_7, CASE WHEN LEFT(H.SO_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'08' THEN COUNT(*) ELSE 0 END Qty_8, CASE WHEN LEFT(H.SO_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'09' THEN COUNT(*) ELSE 0 END Qty_9, CASE WHEN LEFT(H.SO_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'10' THEN COUNT(*) ELSE 0 END Qty_10, CASE WHEN LEFT(H.SO_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'11' THEN COUNT(*) ELSE 0 END Qty_11, CASE WHEN LEFT(H.SO_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'12' THEN COUNT(*) ELSE 0 END Qty_12 FROM LSO_D D LEFT OUTER JOIN LSO H ON D.CO_CD = H.CO_CD AND D.SO_NB = H.SO_NB WHERE D.CO_CD = @CO_CD AND LEFT(H.SO_DT,4) = @YYYY AND DIV_CD = @DIV_CD GROUP BY LEFT(H.SO_DT,6) )X INSERT ##S_use_analysis_transact (Module,Type,Class,Qty_1,Qty_2,Qty_3,Qty_4,Qty_5,Qty_6,Qty_7,Qty_8,Qty_9,Qty_10,Qty_11,Qty_12) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '판매'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '출고'), '', ISNULL(SUM(X.Qty_1),0), ISNULL(SUM(X.Qty_2),0), ISNULL(SUM(X.Qty_3),0), ISNULL(SUM(X.Qty_4),0), ISNULL(SUM(X.Qty_5),0), ISNULL(SUM(X.Qty_6),0), ISNULL(SUM(X.Qty_7),0), ISNULL(SUM(X.Qty_8),0), ISNULL(SUM(X.Qty_9),0), ISNULL(SUM(X.Qty_10),0), ISNULL(SUM(X.Qty_11),0), ISNULL(SUM(X.Qty_12),0) FROM (SELECT CASE WHEN LEFT(H.ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'01' THEN COUNT(*) ELSE 0 END Qty_1, CASE WHEN LEFT(H.ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'02' THEN COUNT(*) ELSE 0 END Qty_2, CASE WHEN LEFT(H.ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'03' THEN COUNT(*) ELSE 0 END Qty_3, CASE WHEN LEFT(H.ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'04' THEN COUNT(*) ELSE 0 END Qty_4, CASE WHEN LEFT(H.ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'05' THEN COUNT(*) ELSE 0 END Qty_5, CASE WHEN LEFT(H.ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'06' THEN COUNT(*) ELSE 0 END Qty_6, CASE WHEN LEFT(H.ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'07' THEN COUNT(*) ELSE 0 END Qty_7, CASE WHEN LEFT(H.ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'08' THEN COUNT(*) ELSE 0 END Qty_8, CASE WHEN LEFT(H.ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'09' THEN COUNT(*) ELSE 0 END Qty_9, CASE WHEN LEFT(H.ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'10' THEN COUNT(*) ELSE 0 END Qty_10, CASE WHEN LEFT(H.ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'11' THEN COUNT(*) ELSE 0 END Qty_11, CASE WHEN LEFT(H.ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'12' THEN COUNT(*) ELSE 0 END Qty_12 FROM LDELIVER_D D LEFT OUTER JOIN LDELIVER H ON D.CO_CD = H.CO_CD AND D.ISU_NB = H.ISU_NB WHERE D.CO_CD = @CO_CD AND LEFT(H.ISU_DT,4) = @YYYY AND DIV_CD = @DIV_CD GROUP BY LEFT(H.ISU_DT,6) )X INSERT ##S_use_analysis_transact (Module,Type,Class,Qty_1,Qty_2,Qty_3,Qty_4,Qty_5,Qty_6,Qty_7,Qty_8,Qty_9,Qty_10,Qty_11,Qty_12) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '판매'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '마감'), '', ISNULL(SUM(X.Qty_1),0), ISNULL(SUM(X.Qty_2),0), ISNULL(SUM(X.Qty_3),0), ISNULL(SUM(X.Qty_4),0), ISNULL(SUM(X.Qty_5),0), ISNULL(SUM(X.Qty_6),0), ISNULL(SUM(X.Qty_7),0), ISNULL(SUM(X.Qty_8),0), ISNULL(SUM(X.Qty_9),0), ISNULL(SUM(X.Qty_10),0), ISNULL(SUM(X.Qty_11),0), ISNULL(SUM(X.Qty_12),0) FROM (SELECT CASE WHEN LEFT(H.CLS_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'01' THEN COUNT(*) ELSE 0 END Qty_1, CASE WHEN LEFT(H.CLS_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'02' THEN COUNT(*) ELSE 0 END Qty_2, CASE WHEN LEFT(H.CLS_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'03' THEN COUNT(*) ELSE 0 END Qty_3, CASE WHEN LEFT(H.CLS_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'04' THEN COUNT(*) ELSE 0 END Qty_4, CASE WHEN LEFT(H.CLS_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'05' THEN COUNT(*) ELSE 0 END Qty_5, CASE WHEN LEFT(H.CLS_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'06' THEN COUNT(*) ELSE 0 END Qty_6, CASE WHEN LEFT(H.CLS_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'07' THEN COUNT(*) ELSE 0 END Qty_7, CASE WHEN LEFT(H.CLS_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'08' THEN COUNT(*) ELSE 0 END Qty_8, CASE WHEN LEFT(H.CLS_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'09' THEN COUNT(*) ELSE 0 END Qty_9, CASE WHEN LEFT(H.CLS_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'10' THEN COUNT(*) ELSE 0 END Qty_10, CASE WHEN LEFT(H.CLS_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'11' THEN COUNT(*) ELSE 0 END Qty_11, CASE WHEN LEFT(H.CLS_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'12' THEN COUNT(*) ELSE 0 END Qty_12 FROM LSALECLS_D D LEFT OUTER JOIN LSALECLS H ON D.CO_CD = H.CO_CD AND D.CLS_NB = H.CLS_NB WHERE D.CO_CD = @CO_CD AND LEFT(H.CLS_DT,4) = @YYYY AND DIV_CD = @DIV_CD GROUP BY LEFT(H.CLS_DT,6) )X INSERT ##S_use_analysis_transact (Module,Type,Class,Qty_1,Qty_2,Qty_3,Qty_4,Qty_5,Qty_6,Qty_7,Qty_8,Qty_9,Qty_10,Qty_11,Qty_12) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '판매'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '출고요청'), '', ISNULL(SUM(X.Qty_1),0), ISNULL(SUM(X.Qty_2),0), ISNULL(SUM(X.Qty_3),0), ISNULL(SUM(X.Qty_4),0), ISNULL(SUM(X.Qty_5),0), ISNULL(SUM(X.Qty_6),0), ISNULL(SUM(X.Qty_7),0), ISNULL(SUM(X.Qty_8),0), ISNULL(SUM(X.Qty_9),0), ISNULL(SUM(X.Qty_10),0), ISNULL(SUM(X.Qty_11),0), ISNULL(SUM(X.Qty_12),0) FROM (SELECT CASE WHEN LEFT(H.RENT_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'01' THEN COUNT(*) ELSE 0 END Qty_1, CASE WHEN LEFT(H.RENT_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'02' THEN COUNT(*) ELSE 0 END Qty_2, CASE WHEN LEFT(H.RENT_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'03' THEN COUNT(*) ELSE 0 END Qty_3, CASE WHEN LEFT(H.RENT_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'04' THEN COUNT(*) ELSE 0 END Qty_4, CASE WHEN LEFT(H.RENT_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'05' THEN COUNT(*) ELSE 0 END Qty_5, CASE WHEN LEFT(H.RENT_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'06' THEN COUNT(*) ELSE 0 END Qty_6, CASE WHEN LEFT(H.RENT_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'07' THEN COUNT(*) ELSE 0 END Qty_7, CASE WHEN LEFT(H.RENT_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'08' THEN COUNT(*) ELSE 0 END Qty_8, CASE WHEN LEFT(H.RENT_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'09' THEN COUNT(*) ELSE 0 END Qty_9, CASE WHEN LEFT(H.RENT_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'10' THEN COUNT(*) ELSE 0 END Qty_10, CASE WHEN LEFT(H.RENT_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'11' THEN COUNT(*) ELSE 0 END Qty_11, CASE WHEN LEFT(H.RENT_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'12' THEN COUNT(*) ELSE 0 END Qty_12 FROM LRENT_D D LEFT OUTER JOIN LRENT H ON D.CO_CD = H.CO_CD AND D.RENT_NB = H.RENT_NB WHERE D.CO_CD = @CO_CD AND LEFT(H.RENT_DT,4) = @YYYY AND H.DIV_CD = @DIV_CD GROUP BY LEFT(H.RENT_DT,6) )X INSERT ##S_use_analysis_transact (Module,Type,Class,Qty_1,Qty_2,Qty_3,Qty_4,Qty_5,Qty_6,Qty_7,Qty_8,Qty_9,Qty_10,Qty_11,Qty_12) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '판매'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '출고'), '', ISNULL(SUM(X.Qty_1),0), ISNULL(SUM(X.Qty_2),0), ISNULL(SUM(X.Qty_3),0), ISNULL(SUM(X.Qty_4),0), ISNULL(SUM(X.Qty_5),0), ISNULL(SUM(X.Qty_6),0), ISNULL(SUM(X.Qty_7),0), ISNULL(SUM(X.Qty_8),0), ISNULL(SUM(X.Qty_9),0), ISNULL(SUM(X.Qty_10),0), ISNULL(SUM(X.Qty_11),0), ISNULL(SUM(X.Qty_12),0) FROM (SELECT CASE WHEN LEFT(D.ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'01' THEN COUNT(*) ELSE 0 END Qty_1, CASE WHEN LEFT(D.ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'02' THEN COUNT(*) ELSE 0 END Qty_2, CASE WHEN LEFT(D.ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'03' THEN COUNT(*) ELSE 0 END Qty_3, CASE WHEN LEFT(D.ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'04' THEN COUNT(*) ELSE 0 END Qty_4, CASE WHEN LEFT(D.ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'05' THEN COUNT(*) ELSE 0 END Qty_5, CASE WHEN LEFT(D.ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'06' THEN COUNT(*) ELSE 0 END Qty_6, CASE WHEN LEFT(D.ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'07' THEN COUNT(*) ELSE 0 END Qty_7, CASE WHEN LEFT(D.ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'08' THEN COUNT(*) ELSE 0 END Qty_8, CASE WHEN LEFT(D.ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'09' THEN COUNT(*) ELSE 0 END Qty_9, CASE WHEN LEFT(D.ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'10' THEN COUNT(*) ELSE 0 END Qty_10, CASE WHEN LEFT(D.ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'11' THEN COUNT(*) ELSE 0 END Qty_11, CASE WHEN LEFT(D.ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'12' THEN COUNT(*) ELSE 0 END Qty_12 FROM LRENT_ISU D LEFT OUTER JOIN LRENT H ON D.CO_CD = H.CO_CD AND D.RENT_NB = H.RENT_NB WHERE D.CO_CD = @CO_CD AND LEFT(D.ISU_DT,4) = @YYYY AND H.DIV_CD = @DIV_CD GROUP BY LEFT(D.ISU_DT,6) )X INSERT ##S_use_analysis_transact (Module,Type,Class,Qty_1,Qty_2,Qty_3,Qty_4,Qty_5,Qty_6,Qty_7,Qty_8,Qty_9,Qty_10,Qty_11,Qty_12) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '재고'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '이동'), '', ISNULL(SUM(X.Qty_1),0), ISNULL(SUM(X.Qty_2),0), ISNULL(SUM(X.Qty_3),0), ISNULL(SUM(X.Qty_4),0), ISNULL(SUM(X.Qty_5),0), ISNULL(SUM(X.Qty_6),0), ISNULL(SUM(X.Qty_7),0), ISNULL(SUM(X.Qty_8),0), ISNULL(SUM(X.Qty_9),0), ISNULL(SUM(X.Qty_10),0), ISNULL(SUM(X.Qty_11),0), ISNULL(SUM(X.Qty_12),0) FROM (SELECT CASE WHEN LEFT(H.MOVE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'01' THEN COUNT(*) ELSE 0 END Qty_1, CASE WHEN LEFT(H.MOVE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'02' THEN COUNT(*) ELSE 0 END Qty_2, CASE WHEN LEFT(H.MOVE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'03' THEN COUNT(*) ELSE 0 END Qty_3, CASE WHEN LEFT(H.MOVE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'04' THEN COUNT(*) ELSE 0 END Qty_4, CASE WHEN LEFT(H.MOVE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'05' THEN COUNT(*) ELSE 0 END Qty_5, CASE WHEN LEFT(H.MOVE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'06' THEN COUNT(*) ELSE 0 END Qty_6, CASE WHEN LEFT(H.MOVE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'07' THEN COUNT(*) ELSE 0 END Qty_7, CASE WHEN LEFT(H.MOVE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'08' THEN COUNT(*) ELSE 0 END Qty_8, CASE WHEN LEFT(H.MOVE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'09' THEN COUNT(*) ELSE 0 END Qty_9, CASE WHEN LEFT(H.MOVE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'10' THEN COUNT(*) ELSE 0 END Qty_10, CASE WHEN LEFT(H.MOVE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'11' THEN COUNT(*) ELSE 0 END Qty_11, CASE WHEN LEFT(H.MOVE_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'12' THEN COUNT(*) ELSE 0 END Qty_12 FROM LSTKMOVE_D D LEFT OUTER JOIN LSTKMOVE H ON D.CO_CD = H.CO_CD AND D.MOVE_NB = H.MOVE_NB WHERE D.CO_CD = @CO_CD AND LEFT(H.MOVE_DT,4) = @YYYY AND DIV_CD = @DIV_CD GROUP BY LEFT(H.MOVE_DT,6) )X INSERT ##S_use_analysis_transact (Module,Type,Class,Qty_1,Qty_2,Qty_3,Qty_4,Qty_5,Qty_6,Qty_7,Qty_8,Qty_9,Qty_10,Qty_11,Qty_12) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '재고'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '조정'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '기초'), ISNULL(SUM(X.Qty_1),0), ISNULL(SUM(X.Qty_2),0), ISNULL(SUM(X.Qty_3),0), ISNULL(SUM(X.Qty_4),0), ISNULL(SUM(X.Qty_5),0), ISNULL(SUM(X.Qty_6),0), ISNULL(SUM(X.Qty_7),0), ISNULL(SUM(X.Qty_8),0), ISNULL(SUM(X.Qty_9),0), ISNULL(SUM(X.Qty_10),0), ISNULL(SUM(X.Qty_11),0), ISNULL(SUM(X.Qty_12),0) FROM (SELECT CASE WHEN LEFT(H.ADJUST_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'01' THEN COUNT(*) ELSE 0 END Qty_1, CASE WHEN LEFT(H.ADJUST_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'02' THEN COUNT(*) ELSE 0 END Qty_2, CASE WHEN LEFT(H.ADJUST_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'03' THEN COUNT(*) ELSE 0 END Qty_3, CASE WHEN LEFT(H.ADJUST_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'04' THEN COUNT(*) ELSE 0 END Qty_4, CASE WHEN LEFT(H.ADJUST_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'05' THEN COUNT(*) ELSE 0 END Qty_5, CASE WHEN LEFT(H.ADJUST_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'06' THEN COUNT(*) ELSE 0 END Qty_6, CASE WHEN LEFT(H.ADJUST_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'07' THEN COUNT(*) ELSE 0 END Qty_7, CASE WHEN LEFT(H.ADJUST_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'08' THEN COUNT(*) ELSE 0 END Qty_8, CASE WHEN LEFT(H.ADJUST_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'09' THEN COUNT(*) ELSE 0 END Qty_9, CASE WHEN LEFT(H.ADJUST_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'10' THEN COUNT(*) ELSE 0 END Qty_10, CASE WHEN LEFT(H.ADJUST_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'11' THEN COUNT(*) ELSE 0 END Qty_11, CASE WHEN LEFT(H.ADJUST_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'12' THEN COUNT(*) ELSE 0 END Qty_12 FROM LADJUST_D D LEFT OUTER JOIN LADJUST H ON D.CO_CD = H.CO_CD AND D.ADJUST_NB = H.ADJUST_NB WHERE D.CO_CD = @CO_CD AND LEFT(H.ADJUST_DT,4) = @YYYY AND DIV_CD = @DIV_CD AND ADJUST_FG = '0' GROUP BY LEFT(H.ADJUST_DT,6) )X INSERT ##S_use_analysis_transact (Module,Type,Class,Qty_1,Qty_2,Qty_3,Qty_4,Qty_5,Qty_6,Qty_7,Qty_8,Qty_9,Qty_10,Qty_11,Qty_12) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '재고'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '조정'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '입고')+dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '출고'), ISNULL(SUM(X.Qty_1),0), ISNULL(SUM(X.Qty_2),0), ISNULL(SUM(X.Qty_3),0), ISNULL(SUM(X.Qty_4),0), ISNULL(SUM(X.Qty_5),0), ISNULL(SUM(X.Qty_6),0), ISNULL(SUM(X.Qty_7),0), ISNULL(SUM(X.Qty_8),0), ISNULL(SUM(X.Qty_9),0), ISNULL(SUM(X.Qty_10),0), ISNULL(SUM(X.Qty_11),0), ISNULL(SUM(X.Qty_12),0) FROM (SELECT CASE WHEN LEFT(H.ADJUST_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'01' THEN COUNT(*) ELSE 0 END Qty_1, CASE WHEN LEFT(H.ADJUST_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'02' THEN COUNT(*) ELSE 0 END Qty_2, CASE WHEN LEFT(H.ADJUST_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'03' THEN COUNT(*) ELSE 0 END Qty_3, CASE WHEN LEFT(H.ADJUST_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'04' THEN COUNT(*) ELSE 0 END Qty_4, CASE WHEN LEFT(H.ADJUST_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'05' THEN COUNT(*) ELSE 0 END Qty_5, CASE WHEN LEFT(H.ADJUST_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'06' THEN COUNT(*) ELSE 0 END Qty_6, CASE WHEN LEFT(H.ADJUST_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'07' THEN COUNT(*) ELSE 0 END Qty_7, CASE WHEN LEFT(H.ADJUST_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'08' THEN COUNT(*) ELSE 0 END Qty_8, CASE WHEN LEFT(H.ADJUST_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'09' THEN COUNT(*) ELSE 0 END Qty_9, CASE WHEN LEFT(H.ADJUST_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'10' THEN COUNT(*) ELSE 0 END Qty_10, CASE WHEN LEFT(H.ADJUST_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'11' THEN COUNT(*) ELSE 0 END Qty_11, CASE WHEN LEFT(H.ADJUST_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'12' THEN COUNT(*) ELSE 0 END Qty_12 FROM LADJUST_D D LEFT OUTER JOIN LADJUST H ON D.CO_CD = H.CO_CD AND D.ADJUST_NB = H.ADJUST_NB WHERE D.CO_CD = @CO_CD AND LEFT(H.ADJUST_DT,4) = @YYYY AND DIV_CD = @DIV_CD AND ADJUST_FG <> '0' GROUP BY LEFT(H.ADJUST_DT,6) )X INSERT ##S_use_analysis_transact (Module,Type,Class,Qty_1,Qty_2,Qty_3,Qty_4,Qty_5,Qty_6,Qty_7,Qty_8,Qty_9,Qty_10,Qty_11,Qty_12) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '재고'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '평가'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '구매품'), ISNULL(SUM(X.Qty_1),0), ISNULL(SUM(X.Qty_2),0), ISNULL(SUM(X.Qty_3),0), ISNULL(SUM(X.Qty_4),0), ISNULL(SUM(X.Qty_5),0), ISNULL(SUM(X.Qty_6),0), ISNULL(SUM(X.Qty_7),0), ISNULL(SUM(X.Qty_8),0), ISNULL(SUM(X.Qty_9),0), ISNULL(SUM(X.Qty_10),0), ISNULL(SUM(X.Qty_11),0), ISNULL(SUM(X.Qty_12),0) FROM (SELECT CASE WHEN T.SMM = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'01' THEN COUNT(*) ELSE 0 END Qty_1, CASE WHEN T.SMM = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'02' THEN COUNT(*) ELSE 0 END Qty_2, CASE WHEN T.SMM = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'03' THEN COUNT(*) ELSE 0 END Qty_3, CASE WHEN T.SMM = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'04' THEN COUNT(*) ELSE 0 END Qty_4, CASE WHEN T.SMM = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'05' THEN COUNT(*) ELSE 0 END Qty_5, CASE WHEN T.SMM = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'06' THEN COUNT(*) ELSE 0 END Qty_6, CASE WHEN T.SMM = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'07' THEN COUNT(*) ELSE 0 END Qty_7, CASE WHEN T.SMM = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'08' THEN COUNT(*) ELSE 0 END Qty_8, CASE WHEN T.SMM = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'09' THEN COUNT(*) ELSE 0 END Qty_9, CASE WHEN T.SMM = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'10' THEN COUNT(*) ELSE 0 END Qty_10, CASE WHEN T.SMM = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'11' THEN COUNT(*) ELSE 0 END Qty_11, CASE WHEN T.SMM = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'12' THEN COUNT(*) ELSE 0 END Qty_12 FROM LINV_TAV T LEFT OUTER JOIN SITEM I ON T.CO_CD = I.CO_CD AND T.ITEM_CD = I.ITEM_CD WHERE T.CO_CD = @CO_CD AND LEFT(T.SMM,4) = @YYYY AND I.ODR_FG = '0' AND DIV_CD = @DIV_CD GROUP BY T.SMM )X INSERT ##S_use_analysis_transact (Module,Type,Class,Qty_1,Qty_2,Qty_3,Qty_4,Qty_5,Qty_6,Qty_7,Qty_8,Qty_9,Qty_10,Qty_11,Qty_12) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '재고'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '평가'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '생산품'), ISNULL(SUM(X.Qty_1),0), ISNULL(SUM(X.Qty_2),0), ISNULL(SUM(X.Qty_3),0), ISNULL(SUM(X.Qty_4),0), ISNULL(SUM(X.Qty_5),0), ISNULL(SUM(X.Qty_6),0), ISNULL(SUM(X.Qty_7),0), ISNULL(SUM(X.Qty_8),0), ISNULL(SUM(X.Qty_9),0), ISNULL(SUM(X.Qty_10),0), ISNULL(SUM(X.Qty_11),0), ISNULL(SUM(X.Qty_12),0) FROM (SELECT CASE WHEN T.SMM = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'01' THEN COUNT(*) ELSE 0 END Qty_1, CASE WHEN T.SMM = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'02' THEN COUNT(*) ELSE 0 END Qty_2, CASE WHEN T.SMM = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'03' THEN COUNT(*) ELSE 0 END Qty_3, CASE WHEN T.SMM = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'04' THEN COUNT(*) ELSE 0 END Qty_4, CASE WHEN T.SMM = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'05' THEN COUNT(*) ELSE 0 END Qty_5, CASE WHEN T.SMM = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'06' THEN COUNT(*) ELSE 0 END Qty_6, CASE WHEN T.SMM = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'07' THEN COUNT(*) ELSE 0 END Qty_7, CASE WHEN T.SMM = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'08' THEN COUNT(*) ELSE 0 END Qty_8, CASE WHEN T.SMM = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'09' THEN COUNT(*) ELSE 0 END Qty_9, CASE WHEN T.SMM = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'10' THEN COUNT(*) ELSE 0 END Qty_10, CASE WHEN T.SMM = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'11' THEN COUNT(*) ELSE 0 END Qty_11, CASE WHEN T.SMM = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'12' THEN COUNT(*) ELSE 0 END Qty_12 FROM LINV_TAV T LEFT OUTER JOIN SITEM I ON T.CO_CD = I.CO_CD AND T.ITEM_CD = I.ITEM_CD WHERE T.CO_CD = @CO_CD AND LEFT(T.SMM,4) = @YYYY AND I.ODR_FG = '1' AND DIV_CD = @DIV_CD GROUP BY T.SMM )X INSERT ##S_use_analysis_transact (Module,Type,Class,Qty_1,Qty_2,Qty_3,Qty_4,Qty_5,Qty_6,Qty_7,Qty_8,Qty_9,Qty_10,Qty_11,Qty_12) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '회계'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '전표'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '승인'), ISNULL(SUM(X.Qty_1),0), ISNULL(SUM(X.Qty_2),0), ISNULL(SUM(X.Qty_3),0), ISNULL(SUM(X.Qty_4),0), ISNULL(SUM(X.Qty_5),0), ISNULL(SUM(X.Qty_6),0), ISNULL(SUM(X.Qty_7),0), ISNULL(SUM(X.Qty_8),0), ISNULL(SUM(X.Qty_9),0), ISNULL(SUM(X.Qty_10),0), ISNULL(SUM(X.Qty_11),0), ISNULL(SUM(X.Qty_12),0) FROM (SELECT CASE WHEN LEFT(ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'01' THEN COUNT(*) ELSE 0 END Qty_1, CASE WHEN LEFT(ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'02' THEN COUNT(*) ELSE 0 END Qty_2, CASE WHEN LEFT(ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'03' THEN COUNT(*) ELSE 0 END Qty_3, CASE WHEN LEFT(ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'04' THEN COUNT(*) ELSE 0 END Qty_4, CASE WHEN LEFT(ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'05' THEN COUNT(*) ELSE 0 END Qty_5, CASE WHEN LEFT(ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'06' THEN COUNT(*) ELSE 0 END Qty_6, CASE WHEN LEFT(ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'07' THEN COUNT(*) ELSE 0 END Qty_7, CASE WHEN LEFT(ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'08' THEN COUNT(*) ELSE 0 END Qty_8, CASE WHEN LEFT(ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'09' THEN COUNT(*) ELSE 0 END Qty_9, CASE WHEN LEFT(ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'10' THEN COUNT(*) ELSE 0 END Qty_10, CASE WHEN LEFT(ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'11' THEN COUNT(*) ELSE 0 END Qty_11, CASE WHEN LEFT(ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'12' THEN COUNT(*) ELSE 0 END Qty_12 FROM ADOCUH WHERE CO_CD = @CO_CD AND LEFT(ISU_DT,4) = @YYYY AND DOCU_ST = '1' AND DIV_CD = @DIV_CD GROUP BY LEFT(ISU_DT,6) )X INSERT ##S_use_analysis_transact (Module,Type,Class,Qty_1,Qty_2,Qty_3,Qty_4,Qty_5,Qty_6,Qty_7,Qty_8,Qty_9,Qty_10,Qty_11,Qty_12) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '회계'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '전표'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '미승인'), ISNULL(SUM(X.Qty_1),0), ISNULL(SUM(X.Qty_2),0), ISNULL(SUM(X.Qty_3),0), ISNULL(SUM(X.Qty_4),0), ISNULL(SUM(X.Qty_5),0), ISNULL(SUM(X.Qty_6),0), ISNULL(SUM(X.Qty_7),0), ISNULL(SUM(X.Qty_8),0), ISNULL(SUM(X.Qty_9),0), ISNULL(SUM(X.Qty_10),0), ISNULL(SUM(X.Qty_11),0), ISNULL(SUM(X.Qty_12),0) FROM (SELECT CASE WHEN LEFT(ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'01' THEN COUNT(*) ELSE 0 END Qty_1, CASE WHEN LEFT(ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'02' THEN COUNT(*) ELSE 0 END Qty_2, CASE WHEN LEFT(ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'03' THEN COUNT(*) ELSE 0 END Qty_3, CASE WHEN LEFT(ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'04' THEN COUNT(*) ELSE 0 END Qty_4, CASE WHEN LEFT(ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'05' THEN COUNT(*) ELSE 0 END Qty_5, CASE WHEN LEFT(ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'06' THEN COUNT(*) ELSE 0 END Qty_6, CASE WHEN LEFT(ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'07' THEN COUNT(*) ELSE 0 END Qty_7, CASE WHEN LEFT(ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'08' THEN COUNT(*) ELSE 0 END Qty_8, CASE WHEN LEFT(ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'09' THEN COUNT(*) ELSE 0 END Qty_9, CASE WHEN LEFT(ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'10' THEN COUNT(*) ELSE 0 END Qty_10, CASE WHEN LEFT(ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'11' THEN COUNT(*) ELSE 0 END Qty_11, CASE WHEN LEFT(ISU_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'12' THEN COUNT(*) ELSE 0 END Qty_12 FROM ADOCUH WHERE CO_CD = @CO_CD AND LEFT(ISU_DT,4) = @YYYY AND DOCU_ST <> '1' AND DIV_CD = @DIV_CD GROUP BY LEFT(ISU_DT,6) )X INSERT ##S_use_analysis_transact (Module,Type,Class,Qty_1,Qty_2,Qty_3,Qty_4,Qty_5,Qty_6,Qty_7,Qty_8,Qty_9,Qty_10,Qty_11,Qty_12) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '회계'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '고정자산'), '', ISNULL(SUM(X.Qty_1),0), ISNULL(SUM(X.Qty_2),0), ISNULL(SUM(X.Qty_3),0), ISNULL(SUM(X.Qty_4),0), ISNULL(SUM(X.Qty_5),0), ISNULL(SUM(X.Qty_6),0), ISNULL(SUM(X.Qty_7),0), ISNULL(SUM(X.Qty_8),0), ISNULL(SUM(X.Qty_9),0), ISNULL(SUM(X.Qty_10),0), ISNULL(SUM(X.Qty_11),0), ISNULL(SUM(X.Qty_12),0) FROM (SELECT CASE WHEN LEFT(GET_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'01' THEN COUNT(*) ELSE 0 END Qty_1, CASE WHEN LEFT(GET_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'02' THEN COUNT(*) ELSE 0 END Qty_2, CASE WHEN LEFT(GET_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'03' THEN COUNT(*) ELSE 0 END Qty_3, CASE WHEN LEFT(GET_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'04' THEN COUNT(*) ELSE 0 END Qty_4, CASE WHEN LEFT(GET_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'05' THEN COUNT(*) ELSE 0 END Qty_5, CASE WHEN LEFT(GET_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'06' THEN COUNT(*) ELSE 0 END Qty_6, CASE WHEN LEFT(GET_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'07' THEN COUNT(*) ELSE 0 END Qty_7, CASE WHEN LEFT(GET_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'08' THEN COUNT(*) ELSE 0 END Qty_8, CASE WHEN LEFT(GET_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'09' THEN COUNT(*) ELSE 0 END Qty_9, CASE WHEN LEFT(GET_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'10' THEN COUNT(*) ELSE 0 END Qty_10, CASE WHEN LEFT(GET_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'11' THEN COUNT(*) ELSE 0 END Qty_11, CASE WHEN LEFT(GET_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'12' THEN COUNT(*) ELSE 0 END Qty_12 FROM ASSET WHERE CO_CD = @CO_CD AND LEFT(GET_DT,4) = @YYYY AND DIV_CD = @DIV_CD GROUP BY LEFT(GET_DT,6) )X INSERT ##S_use_analysis_transact (Module,Type,Class,Qty_1,Qty_2,Qty_3,Qty_4,Qty_5,Qty_6,Qty_7,Qty_8,Qty_9,Qty_10,Qty_11,Qty_12) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '인사'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '조건'), '', ISNULL(SUM(X.Qty_1),0), ISNULL(SUM(X.Qty_2),0), ISNULL(SUM(X.Qty_3),0), ISNULL(SUM(X.Qty_4),0), ISNULL(SUM(X.Qty_5),0), ISNULL(SUM(X.Qty_6),0), ISNULL(SUM(X.Qty_7),0), ISNULL(SUM(X.Qty_8),0), ISNULL(SUM(X.Qty_9),0), ISNULL(SUM(X.Qty_10),0), ISNULL(SUM(X.Qty_11),0), ISNULL(SUM(X.Qty_12),0) FROM (SELECT CASE WHEN LEFT(WORK_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'01' THEN COUNT(DISTINCT EMP_CD) ELSE 0 END Qty_1, CASE WHEN LEFT(WORK_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'02' THEN COUNT(DISTINCT EMP_CD) ELSE 0 END Qty_2, CASE WHEN LEFT(WORK_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'03' THEN COUNT(DISTINCT EMP_CD) ELSE 0 END Qty_3, CASE WHEN LEFT(WORK_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'04' THEN COUNT(DISTINCT EMP_CD) ELSE 0 END Qty_4, CASE WHEN LEFT(WORK_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'05' THEN COUNT(DISTINCT EMP_CD) ELSE 0 END Qty_5, CASE WHEN LEFT(WORK_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'06' THEN COUNT(DISTINCT EMP_CD) ELSE 0 END Qty_6, CASE WHEN LEFT(WORK_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'07' THEN COUNT(DISTINCT EMP_CD) ELSE 0 END Qty_7, CASE WHEN LEFT(WORK_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'08' THEN COUNT(DISTINCT EMP_CD) ELSE 0 END Qty_8, CASE WHEN LEFT(WORK_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'09' THEN COUNT(DISTINCT EMP_CD) ELSE 0 END Qty_9, CASE WHEN LEFT(WORK_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'10' THEN COUNT(DISTINCT EMP_CD) ELSE 0 END Qty_10, CASE WHEN LEFT(WORK_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'11' THEN COUNT(DISTINCT EMP_CD) ELSE 0 END Qty_11, CASE WHEN LEFT(WORK_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'12' THEN COUNT(DISTINCT EMP_CD) ELSE 0 END Qty_12 FROM HTHPWRG WHERE CO_CD = @CO_CD AND LEFT(WORK_DT,4) = @YYYY AND DIV_CD = @DIV_CD AND CLSE_YN = '001' GROUP BY LEFT(WORK_DT,6) )X INSERT ##S_use_analysis_transact (Module,Type,Class,Qty_1,Qty_2,Qty_3,Qty_4,Qty_5,Qty_6,Qty_7,Qty_8,Qty_9,Qty_10,Qty_11,Qty_12) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '인사'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '근태결과'), '', ISNULL(SUM(X.Qty_1),0), ISNULL(SUM(X.Qty_2),0), ISNULL(SUM(X.Qty_3),0), ISNULL(SUM(X.Qty_4),0), ISNULL(SUM(X.Qty_5),0), ISNULL(SUM(X.Qty_6),0), ISNULL(SUM(X.Qty_7),0), ISNULL(SUM(X.Qty_8),0), ISNULL(SUM(X.Qty_9),0), ISNULL(SUM(X.Qty_10),0), ISNULL(SUM(X.Qty_11),0), ISNULL(SUM(X.Qty_12),0) FROM (SELECT CASE WHEN LEFT(WORK_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'01' THEN COUNT(DISTINCT EMP_CD) ELSE 0 END Qty_1, CASE WHEN LEFT(WORK_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'02' THEN COUNT(DISTINCT EMP_CD) ELSE 0 END Qty_2, CASE WHEN LEFT(WORK_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'03' THEN COUNT(DISTINCT EMP_CD) ELSE 0 END Qty_3, CASE WHEN LEFT(WORK_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'04' THEN COUNT(DISTINCT EMP_CD) ELSE 0 END Qty_4, CASE WHEN LEFT(WORK_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'05' THEN COUNT(DISTINCT EMP_CD) ELSE 0 END Qty_5, CASE WHEN LEFT(WORK_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'06' THEN COUNT(DISTINCT EMP_CD) ELSE 0 END Qty_6, CASE WHEN LEFT(WORK_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'07' THEN COUNT(DISTINCT EMP_CD) ELSE 0 END Qty_7, CASE WHEN LEFT(WORK_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'08' THEN COUNT(DISTINCT EMP_CD) ELSE 0 END Qty_8, CASE WHEN LEFT(WORK_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'09' THEN COUNT(DISTINCT EMP_CD) ELSE 0 END Qty_9, CASE WHEN LEFT(WORK_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'10' THEN COUNT(DISTINCT EMP_CD) ELSE 0 END Qty_10, CASE WHEN LEFT(WORK_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'11' THEN COUNT(DISTINCT EMP_CD) ELSE 0 END Qty_11, CASE WHEN LEFT(WORK_DT,6) = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'12' THEN COUNT(DISTINCT EMP_CD) ELSE 0 END Qty_12 FROM HTOPACR WHERE CO_CD = @CO_CD AND LEFT(WORK_DT,4) = @YYYY AND DIV_CD = @DIV_CD AND CLSE_YN = '001' GROUP BY LEFT(WORK_DT,6) )X INSERT ##S_use_analysis_transact (Module,Type,Class,Qty_1,Qty_2,Qty_3,Qty_4,Qty_5,Qty_6,Qty_7,Qty_8,Qty_9,Qty_10,Qty_11,Qty_12) SELECT dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '인사'), dbo.UFN_GET_STRING_BY_LANGKIND(@LANG, '급여'), '', ISNULL(SUM(X.Qty_1),0), ISNULL(SUM(X.Qty_2),0), ISNULL(SUM(X.Qty_3),0), ISNULL(SUM(X.Qty_4),0), ISNULL(SUM(X.Qty_5),0), ISNULL(SUM(X.Qty_6),0), ISNULL(SUM(X.Qty_7),0), ISNULL(SUM(X.Qty_8),0), ISNULL(SUM(X.Qty_9),0), ISNULL(SUM(X.Qty_10),0), ISNULL(SUM(X.Qty_11),0), ISNULL(SUM(X.Qty_12),0) FROM (SELECT CASE WHEN RVRS_YM = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'01' THEN COUNT(DISTINCT EMP_CD) ELSE 0 END Qty_1, CASE WHEN RVRS_YM = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'02' THEN COUNT(DISTINCT EMP_CD) ELSE 0 END Qty_2, CASE WHEN RVRS_YM = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'03' THEN COUNT(DISTINCT EMP_CD) ELSE 0 END Qty_3, CASE WHEN RVRS_YM = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'04' THEN COUNT(DISTINCT EMP_CD) ELSE 0 END Qty_4, CASE WHEN RVRS_YM = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'05' THEN COUNT(DISTINCT EMP_CD) ELSE 0 END Qty_5, CASE WHEN RVRS_YM = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'06' THEN COUNT(DISTINCT EMP_CD) ELSE 0 END Qty_6, CASE WHEN RVRS_YM = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'07' THEN COUNT(DISTINCT EMP_CD) ELSE 0 END Qty_7, CASE WHEN RVRS_YM = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'08' THEN COUNT(DISTINCT EMP_CD) ELSE 0 END Qty_8, CASE WHEN RVRS_YM = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'09' THEN COUNT(DISTINCT EMP_CD) ELSE 0 END Qty_9, CASE WHEN RVRS_YM = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'10' THEN COUNT(DISTINCT EMP_CD) ELSE 0 END Qty_10, CASE WHEN RVRS_YM = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'11' THEN COUNT(DISTINCT EMP_CD) ELSE 0 END Qty_11, CASE WHEN RVRS_YM = CAST(@YYYY AS VARCHAR(4)) COLLATE database_default+'12' THEN COUNT(DISTINCT EMP_CD) ELSE 0 END Qty_12 FROM HPOMNPY WHERE CO_CD = @CO_CD AND LEFT(RVRS_YM,4) = @YYYY AND DIV_CD = @DIV_CD AND EXCL_YN = '0' GROUP BY RVRS_YM )X SELECT * FROM ##S_use_analysis_master SELECT * FROM ##S_use_analysis_transact END