CREATE TABLE [dbo].[TK“`•[F] ( [‰‰KCD] [varchar] (12) NOT NULL , [‰ļŽŠCD] [varchar] (20) NOT NULL , [“`•[‹ę•Ŗ] [smallint] NOT NULL , [“`•[”Ō†] [varchar] (12) NOT NULL , [Žęˆų“ś] [datetime] NULL , [“`•[ˆ—‹ę•Ŗ] [smallint] NULL , [“`•[“E—v] [varchar] (40) NULL , [ģ¬“ś] [datetime] NULL , [ģ¬AP] [varchar] (30) NULL , [ŽęˆųęCD] [varchar] (20) NULL , [’Źķ•Ō•i‹ę•Ŗ] [varchar] (1) NULL , [Šw¶”Ō†] [varchar] (12) NULL , [ķœƒtƒ‰ƒO] [smallint] NOT NULL , [—\”õ‚ ] [varchar] (200) NULL , [—\”õ‚¢] [varchar] (200) NULL , [—\”õ‚¤] [varchar] (200) NULL , [—\”õ1] [int] NULL , [—\”õ2] [int] NULL , [—\”õ3] [int] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[TK“`•[–¾×F] ( [‰‰KCD] [varchar] (12) NOT NULL , [‰ļŽŠCD] [varchar] (20) NOT NULL , [“`•[‹ę•Ŗ] [smallint] NOT NULL , [“`•[”Ō†] [varchar] (12) NOT NULL , [“`•[SEQ] [smallint] NOT NULL , [‘ŻŽŲ‹ę•Ŗ] [varchar] (1) NULL , [ŠØ’čCD] [varchar] (4) NULL , [ŽŲ•ū‹ąŠz] [money] NULL , [‘Ż•ū‹ąŠz] [money] NULL , [‘ŠŽčŠØ’č] [varchar] (4) NULL , [“E—vCD] [varchar] (20) NULL , [“E—v] [varchar] (40) NULL , [—\”õ‚ ] [varchar] (200) NULL , [—\”õ‚¢] [varchar] (200) NULL , [—\”õ‚¤] [varchar] (200) NULL , [—\”õ1] [int] NULL , [—\”õ2] [int] NULL , [—\”õ3] [int] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[TM–¼ŽhƒfƒUƒCƒ“M] ( [–¼ŽhƒfƒUƒCƒ“CD] [varchar] (12) NOT NULL , [c‰”‹ę•Ŗ] [varchar] (2) NULL , [ƒfƒUƒCƒ“] [varchar] (8000) NULL , [—\”õ‚ ] [varchar] (200) NULL , [—\”õ‚¢] [varchar] (200) NULL , [—\”õ‚¤] [varchar] (200) NULL , [—\”õ1] [int] NULL , [—\”õ2] [int] NULL , [—\”õ3] [int] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[TM–¼Žhƒf[ƒ^F] ( [‰‰KCD] [varchar] (12) NULL , [‰ļŽŠCD] [varchar] (20) NULL , [Šw¶”Ō†] [varchar] (50) NULL , [–¼ŽhƒfƒUƒCƒ“CD] [varchar] (12) NULL , [‰ļŽŠ–¼Ģ] [varchar] (50) NULL , [ŒØ‘] [varchar] (50) NULL , [Ž–¼] [varchar] (50) NULL , [—X•Ö”Ō†] [varchar] (50) NULL , [ZŠ1] [varchar] (50) NULL , [ZŠ2] [varchar] (50) NULL , [“d˜b”Ō†] [varchar] (50) NULL , [ƒ[ƒ‹ƒAƒhƒŒƒX] [varchar] (50) NULL , [‰ę‘œƒtƒ@ƒCƒ‹] [varchar] (250) NULL , [—\”õ‚ ] [varchar] (200) NULL , [—\”õ‚¢] [varchar] (200) NULL , [—\”õ‚¤] [varchar] (200) NULL , [—\”õ1] [int] NULL , [—\”õ2] [int] NULL , [—\”õ3] [int] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[TS‰ļŽŠM] ( [‰‰KCD] [varchar] (12) NOT NULL , [‰ļŽŠCD] [varchar] (20) NOT NULL , [Šw¶”Ō†] [varchar] (12) NULL , [‰ļŽŠ–¼Ģ] [varchar] (40) NULL , [‰ļŽŠ—Ŗ–¼Ģ] [varchar] (10) NULL , [‰ļŽŠƒJƒi–¼Ģ] [varchar] (40) NULL , [—X•Ö”Ō†] [varchar] (8) NULL , [ZŠ1] [varchar] (50) NULL , [ZŠ2] [varchar] (50) NULL , [TEL] [varchar] (14) NULL , [FAX] [varchar] (14) NULL , [‹ĘŽķCD] [varchar] (1) NULL , [Ž©ŽŠƒtƒ‰ƒO] [smallint] NULL , [—\”õ‚ ] [varchar] (200) NULL , [—\”õ‚¢] [varchar] (200) NULL , [—\”õ‚¤] [varchar] (200) NULL , [—\”õ1] [int] NULL , [—\”õ2] [int] NULL , [—\”õ3] [int] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[TSŠØ’č‰Č–ŚM] ( [‰‰KCD] [varchar] (12) NOT NULL , [ŠØ’čCD] [varchar] (4) NOT NULL , [‹ĘŽķCD] [varchar] (1) NULL , [ŠØ’č–¼Ģ] [varchar] (40) NULL , [ŠØ’č—Ŗ–¼Ģ] [varchar] (20) NULL , [ŠØ’č‹ę•Ŗ] [varchar] (1) NULL , [ŠØ’č×–Ś] [varchar] (1) NULL , [‘ŻŽŲ‹ę•Ŗ] [varchar] (1) NULL , [ƒf[ƒ^‹ę•Ŗ] [varchar] (1) NULL , [”ļ—p‹ę•Ŗ] [varchar] (1) NULL , [•ŖĶ—p‹ę•Ŗ] [varchar] (2) NULL , [WŒvęŠØ’č] [varchar] (4) NULL , [WŒv‹ę•Ŗ] [varchar] (1) NULL , [”•\WŒvCD] [varchar] (4) NULL , [ģ¬‹ę•Ŗ] [smallint] NULL , [XV“śŽž] [datetime] NULL , [—\”õ‚ ] [varchar] (200) NULL , [—\”õ‚¢] [varchar] (200) NULL , [—\”õ‚¤] [varchar] (200) NULL , [—\”õ1] [int] NULL , [—\”õ2] [int] NULL , [—\”õ3] [int] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[TSŠw¶M] ( [Šw¶”Ō†] [varchar] (12) NOT NULL , [Ž–¼] [varchar] (30) NULL , [ƒpƒXƒ[ƒh] [varchar] (12) NULL , [‰‰KCD] [varchar] (12) NULL , [Šw¶ƒOƒ‹[ƒvCD] [varchar] (12) NULL , [ÅVƒƒOƒCƒ““ś] [datetime] NULL , [—\”õ‚ ] [varchar] (200) NULL , [—\”õ‚¢] [varchar] (200) NULL , [—\”õ‚¤] [varchar] (200) NULL , [—\”õ1] [int] NULL , [—\”õ2] [int] NULL , [—\”õ3] [int] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[TS‰‰KM] ( [‰‰KCD] [varchar] (12) NOT NULL , [‰‰K–¼Ģ] [varchar] (40) NULL , [‰‰K‹ę•Ŗ] [varchar] (1) NULL , [‰‰K‹ę•Ŗ–¼] [varchar] (20) NULL , [‰ļŒvŠJŽn“ś] [datetime] NULL , [‰ļŒvI—¹“ś] [datetime] NULL , [Į”ļÅ] [float] NULL , [—˜—p“`•[”‹ę•Ŗ] [varchar] (1) NULL , [“]‹L‹ę•Ŗ] [varchar] (1) NULL , [Žd“ü”„ćis‹ę•Ŗ] [varchar] (1) NULL , [‹āsˆ—‹ę•Ŗ] [varchar] (1) NULL , [Žč“®•ā••ė•\Ž¦] [varchar] (2) NULL , [Žč“®Žd–ó’ •\Ž¦] [varchar] (2) NULL , [Žč“®Žd–óWŒv•\•\Ž¦] [varchar] (2) NULL , [Žč“®‡ŒvŽc‚ŽŽŽZ•\•\Ž¦] [varchar] (2) NULL , [Žč“®‘ŠØ’čŒ³’ •\Ž¦] [varchar] (2) NULL , [Žč“®‘ŻŽŲ‘ĪĘ•\•\Ž¦] [varchar] (2) NULL , [Žč“®‘¹‰vŒvŽZ‘•\Ž¦] [varchar] (2) NULL , [Žč“®8Œ…øŽZ•\•\Ž¦] [varchar] (2) NULL , [Ž©“®“]‹LŽd–ó’ •\Ž¦] [varchar] (2) NULL , [Ž©“®“]‹LŽd–óWŒv•\•\Ž¦] [varchar] (2) NULL , [Ž©“®“]‹L‡ŒvŽc‚ŽŽŽZ•\•\Ž¦] [varchar] (2) NULL , [Ž©“®“]‹L‘ŠØ’čŒ³’ •\Ž¦] [varchar] (2) NULL , [Ž©“®“]‹L‘ŻŽŲ‘ĪĘ•\•\Ž¦] [varchar] (2) NULL , [Ž©“®“]‹L‘¹‰vŒvŽZ‘•\Ž¦] [varchar] (2) NULL , [Ž©“®“]‹L8Œ…øŽZ•\•\Ž¦] [varchar] (2) NULL , [‰‰KŠĒ—ŽŅƒpƒXƒ[ƒh] [varchar] (12) NULL , [—\”õ‚ ] [varchar] (200) NULL , [—\”õ‚¢] [varchar] (200) NULL , [—\”õ‚¤] [varchar] (200) NULL , [—\”õ1] [int] NULL , [—\”õ2] [int] NULL , [—\”õ3] [int] NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[TK“`•[F] WITH NOCHECK ADD CONSTRAINT [PK_TK“`•[F] PRIMARY KEY CLUSTERED ( [‰‰KCD], [‰ļŽŠCD], [“`•[‹ę•Ŗ], [“`•[”Ō†] ) ON [PRIMARY] GO ALTER TABLE [dbo].[TK“`•[–¾×F] WITH NOCHECK ADD CONSTRAINT [PK_TK“`•[–¾×F] PRIMARY KEY CLUSTERED ( [‰‰KCD], [‰ļŽŠCD], [“`•[‹ę•Ŗ], [“`•[”Ō†], [“`•[SEQ] ) ON [PRIMARY] GO ALTER TABLE [dbo].[TM–¼ŽhƒfƒUƒCƒ“M] WITH NOCHECK ADD CONSTRAINT [PK_TM–¼ŽhƒfƒUƒCƒ“M] PRIMARY KEY CLUSTERED ( [–¼ŽhƒfƒUƒCƒ“CD] ) ON [PRIMARY] GO ALTER TABLE [dbo].[TS‰ļŽŠM] WITH NOCHECK ADD CONSTRAINT [PK_TS‰ļŽŠM] PRIMARY KEY CLUSTERED ( [‰‰KCD], [‰ļŽŠCD] ) ON [PRIMARY] GO ALTER TABLE [dbo].[TSŠØ’č‰Č–ŚM] WITH NOCHECK ADD CONSTRAINT [PK_TSŠØ’č‰Č–ŚM] PRIMARY KEY CLUSTERED ( [‰‰KCD], [ŠØ’čCD] ) ON [PRIMARY] GO ALTER TABLE [dbo].[TSŠw¶M] WITH NOCHECK ADD CONSTRAINT [PK_TSŠw¶M] PRIMARY KEY CLUSTERED ( [Šw¶”Ō†] ) ON [PRIMARY] GO ALTER TABLE [dbo].[TS‰‰KM] WITH NOCHECK ADD CONSTRAINT [PK_TS‰‰KM] PRIMARY KEY CLUSTERED ( [‰‰KCD] ) ON [PRIMARY] GO ALTER TABLE [dbo].[TK“`•[F] WITH NOCHECK ADD CONSTRAINT [DF_TK“`•[F_ķœƒtƒ‰ƒO] DEFAULT (0) FOR [ķœƒtƒ‰ƒO] GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[S0“`•[Ģ”ŌŽę“¾]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[S0“`•[Ģ”ŌŽę“¾] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SK“`•[_U‘Ö“`•[ˆź——]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[SK“`•[_U‘Ö“`•[ˆź——] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SK“`•[_U‘Ö“`•[Žę“¾]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[SK“`•[_U‘Ö“`•[Žę“¾] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SK“`•[_U‘Ö“`•[‘}“ü]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[SK“`•[_U‘Ö“`•[‘}“ü] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SK“`•[_U‘Ö“`•[XV]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[SK“`•[_U‘Ö“`•[XV] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SK“`•[‘O”Ō†]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[SK“`•[‘O”Ō†] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SK“`•[ŽŸ”Ō†]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[SK“`•[ŽŸ”Ō†] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SKŽ©“®“]‹L’ •ė_‡ŒvŽc‚ŽŽŽZ•\Ę‰ļ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[SKŽ©“®“]‹L’ •ė_‡ŒvŽc‚ŽŽŽZ•\Ę‰ļ] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SKŽ©“®“]‹L’ •ė_ą–±”•\]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[SKŽ©“®“]‹L’ •ė_ą–±”•\] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ST–¼ŽhƒfƒUƒCƒ“ˆź——]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[ST–¼ŽhƒfƒUƒCƒ“ˆź——] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ST–¼Žhƒf[ƒ^Žę“¾]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[ST–¼Žhƒf[ƒ^Žę“¾] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ST–¼Žhƒf[ƒ^XV]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[ST–¼Žhƒf[ƒ^XV] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[STŲŒ”‰ļˆõ_“o˜^ķœ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[STŲŒ”‰ļˆõ_“o˜^ķœ] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SVŠØ’č‰Č–ŚŽę“¾]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[SVŠØ’č‰Č–ŚŽę“¾] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SV“E—vˆź——]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[SV“E—vˆź——] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SXŽęˆų–¾×“o˜^]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[SXŽęˆų–¾×“o˜^] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SXŠw¶ī•ńŽę“¾]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[SXŠw¶ī•ńŽę“¾] GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE dbo.S0“`•[Ģ”ŌŽę“¾ @‰‰KCD varchar(12) , @‰ļŽŠCD varchar(20) , @Žęˆų“ś datetime, @“`•[‹ę•Ŗ varchar(12) AS declare @”Ō† int declare @“`•[”Ō† varchar(12) begin transaction if exists( (SELECT MAX(“`•[”Ō†) AS “`•[”Ō†‚ĢÅ‘å FROM TK“`•[F WHERE YEAR(Žęˆų“ś) = YEAR(@Žęˆų“ś) AND MONTH(Žęˆų“ś) = MONTH(@Žęˆų“ś) AND (‰‰KCD = @‰‰KCD) AND (‰ļŽŠCD = @‰ļŽŠCD) GROUP BY ‰ļŽŠCD, ‰‰KCD)) begin select @”Ō† = (SELECT MAX(“`•[”Ō†) AS “`•[”Ō†‚ĢÅ‘å FROM TK“`•[F WHERE YEAR(Žęˆų“ś) = YEAR(@Žęˆų“ś) AND MONTH(Žęˆų“ś) = MONTH(@Žęˆų“ś) AND (‰‰KCD = @‰‰KCD) AND (‰ļŽŠCD = @‰ļŽŠCD) GROUP BY ‰ļŽŠCD, ‰‰KCD) select @”Ō† = @”Ō† + 1 end else begin /* Ģ”Ōƒtƒ@ƒCƒ‹‚Ŗ‘¶Ż‚µ‚Č‚¢ź‡ */ select @”Ō† = (YEAR(@Žęˆų“ś)-2000) * 100000 + MONTH(@Žęˆų“ś) * 1000 + 1 end commit transaction select @“`•[”Ō† = REPLACE(STR(@”Ō†, 7), ' ','0') select @“`•[”Ō† return @“`•[”Ō† GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE dbo.SK“`•[_U‘Ö“`•[ˆź—— @‰‰KCD varchar(12), @‰ļŽŠCD varchar(20) AS SELECT MIN(TK“`•[F.Žęˆų“ś) AS Žęˆų“ś‚Ģę“Ŗ, TK“`•[F.“`•[”Ō†, MIN(TK“`•[F.“`•[‹ę•Ŗ) AS “`•[‹ę•Ŗ‚Ģę“Ŗ, MIN(TK“`•[F.“`•[“E—v) AS “`•[“E—v‚Ģę“Ŗ, SUM(TK“`•[–¾×F.ŽŲ•ū‹ąŠz) AS ŽŲ•ū‹ąŠz‚Ģ‡Œv, SUM(TK“`•[–¾×F.‘Ż•ū‹ąŠz) AS ‘Ż•ū‹ąŠz‚Ģ‡Œv, TK“`•[F.ķœƒtƒ‰ƒO FROM TK“`•[F INNER JOIN TK“`•[–¾×F ON TK“`•[F.“`•[”Ō† = TK“`•[–¾×F.“`•[”Ō† AND TK“`•[F.‰ļŽŠCD = TK“`•[–¾×F.‰ļŽŠCD AND TK“`•[F.“`•[‹ę•Ŗ = TK“`•[–¾×F.“`•[‹ę•Ŗ AND TK“`•[F.‰‰KCD = TK“`•[–¾×F.‰‰KCD WHERE (TK“`•[F.‰‰KCD = @‰‰KCD) AND (TK“`•[F.‰ļŽŠCD = @‰ļŽŠCD) AND (TK“`•[F.“`•[‹ę•Ŗ = 5) AND (TK“`•[F.“`•[ˆ—‹ę•Ŗ = 1) GROUP BY TK“`•[F.“`•[”Ō†, TK“`•[F.“`•[ˆ—‹ę•Ŗ, TK“`•[F.“`•[‹ę•Ŗ, TK“`•[F.ķœƒtƒ‰ƒO ORDER BY MIN(TK“`•[F.Žęˆų“ś), TK“`•[F.“`•[”Ō† GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE dbo.SK“`•[_U‘Ö“`•[Žę“¾ @‰‰KCD varchar(12), @‰ļŽŠCD varchar(20), @“`•[”Ō† varchar(40) AS SELECT TK“`•[F.*, TK“`•[–¾×F.“`•[SEQ, TK“`•[–¾×F.ŠØ’čCD, TK“`•[–¾×F.ŽŲ•ū‹ąŠz, TK“`•[–¾×F.‘Ż•ū‹ąŠz, TK“`•[–¾×F.‰ļŽŠCD, TK“`•[–¾×F.“E—vCD, TK“`•[–¾×F.“E—v, TSŠw¶M.Ž–¼ FROM TK“`•[F INNER JOIN TK“`•[–¾×F ON TK“`•[F.“`•[”Ō† = TK“`•[–¾×F.“`•[”Ō† AND TK“`•[F.‰ļŽŠCD = TK“`•[–¾×F.‰ļŽŠCD AND TK“`•[F.“`•[‹ę•Ŗ = TK“`•[–¾×F.“`•[‹ę•Ŗ AND TK“`•[F.‰‰KCD = TK“`•[–¾×F.‰‰KCD LEFT OUTER JOIN TSŠw¶M ON TK“`•[F.Šw¶”Ō† = TSŠw¶M.Šw¶”Ō† WHERE TK“`•[F.‰‰KCD = @‰‰KCD AND TK“`•[F.‰ļŽŠCD = @‰ļŽŠCD AND TK“`•[–¾×F.“`•[‹ę•Ŗ = 5 AND TK“`•[–¾×F.“`•[”Ō† = @“`•[”Ō† GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE dbo.SK“`•[_U‘Ö“`•[‘}“ü @“`•[”Ō† varchar(12) , @Žęˆų“ś datetime , @“`•[“E—v varchar(40) , @“`•[‹ę•Ŗ smallint ,@“`•[ˆ—‹ę•Ŗ smallint , @‰‰KCD varchar(12) , @‰ļŽŠCD varchar(20) ,@Šw¶”Ō† varchar(12) /* “`•[–¾× */ ,@LNO1 smallint ,@ŠØ’čCD1 varchar(4) , @ŽŲ•ū‹ąŠz1 money , @‘Ż•ū‹ąŠz1 money ,@‘ŻŽŲ‹ę•Ŗ1 varchar(1) ,@“E—vCD1 varchar(20) ,@“E—v1 varchar(30) , @‘ŠŽčAC1 varchar(4) ,@LNO2 smallint ,@ŠØ’čCD2 varchar(4) , @ŽŲ•ū‹ąŠz2 money , @‘Ż•ū‹ąŠz2 money ,@‘ŻŽŲ‹ę•Ŗ2 varchar(1) ,@“E—vCD2 varchar(20) ,@“E—v2 varchar(30) , @‘ŠŽčAC2 varchar(4) ,@LNO3 smallint ,@ŠØ’čCD3 varchar(4) , @ŽŲ•ū‹ąŠz3 money , @‘Ż•ū‹ąŠz3 money ,@‘ŻŽŲ‹ę•Ŗ3 varchar(1) ,@“E—vCD3 varchar(20) ,@“E—v3 varchar(30) , @‘ŠŽčAC3 varchar(4) ,@LNO4 smallint ,@ŠØ’čCD4 varchar(4) , @ŽŲ•ū‹ąŠz4 money , @‘Ż•ū‹ąŠz4 money ,@‘ŻŽŲ‹ę•Ŗ4 varchar(1) ,@“E—vCD4 varchar(20) ,@“E—v4 varchar(30) , @‘ŠŽčAC4 varchar(4) ,@LNO5 smallint ,@ŠØ’čCD5 varchar(4) , @ŽŲ•ū‹ąŠz5 money , @‘Ż•ū‹ąŠz5 money ,@‘ŻŽŲ‹ę•Ŗ5 varchar(1) ,@“E—vCD5 varchar(20) ,@“E—v5 varchar(30) , @‘ŠŽčAC5 varchar(4) ,@LNO6 smallint ,@ŠØ’čCD6 varchar(4) , @ŽŲ•ū‹ąŠz6 money , @‘Ż•ū‹ąŠz6 money ,@‘ŻŽŲ‹ę•Ŗ6 varchar(1) ,@“E—vCD6 varchar(20) ,@“E—v6 varchar(30) , @‘ŠŽčAC6 varchar(4) ,@LNO7 smallint ,@ŠØ’čCD7 varchar(4) , @ŽŲ•ū‹ąŠz7 money , @‘Ż•ū‹ąŠz7 money ,@‘ŻŽŲ‹ę•Ŗ7 varchar(1) ,@“E—vCD7 varchar(20) ,@“E—v7 varchar(30) , @‘ŠŽčAC7 varchar(4) ,@LNO8 smallint ,@ŠØ’čCD8 varchar(4) , @ŽŲ•ū‹ąŠz8 money , @‘Ż•ū‹ąŠz8 money ,@‘ŻŽŲ‹ę•Ŗ8 varchar(1) ,@“E—vCD8 varchar(20) ,@“E—v8 varchar(30) , @‘ŠŽčAC8 varchar(4) ,@LNO9 smallint ,@ŠØ’čCD9 varchar(4) , @ŽŲ•ū‹ąŠz9 money , @‘Ż•ū‹ąŠz9 money ,@‘ŻŽŲ‹ę•Ŗ9 varchar(1) ,@“E—vCD9 varchar(20) ,@“E—v9 varchar(30) , @‘ŠŽčAC9 varchar(4) ,@LNO10 smallint ,@ŠØ’čCD10 varchar(4) , @ŽŲ•ū‹ąŠz10 money , @‘Ż•ū‹ąŠz10 money ,@‘ŻŽŲ‹ę•Ŗ10 varchar(1) ,@“E—vCD10 varchar(20) ,@“E—v10 varchar(30) , @‘ŠŽčAC10 varchar(4) AS declare @”Ō† varchar(12) begin transaction /* “`•[”Ō†Ģ”Ō */ execute @”Ō† = S0“`•[Ģ”ŌŽę“¾ @‰‰KCD, @‰ļŽŠCD, @Žęˆų“ś, @“`•[‹ę•Ŗ if @@error <> 0 begin rollback transaction select -1 return end select @“`•[”Ō† = REPLACE(STR(@”Ō†, 7), ' ','0') /* “`•[F“o˜^ */ insert TK“`•[F ( ‰‰KCD , ‰ļŽŠCD , “`•[”Ō† , “`•[‹ę•Ŗ , “`•[ˆ—‹ę•Ŗ , Žęˆų“ś , “`•[“E—v , ģ¬“ś ,Šw¶”Ō†) values ( @‰‰KCD , @‰ļŽŠCD , @“`•[”Ō† , @“`•[‹ę•Ŗ , @“`•[ˆ—‹ę•Ŗ , @Žęˆų“ś , @“`•[“E—v , getdate() , @Šw¶”Ō†) if @@error <> 0 begin rollback transaction select -1 return end /* “`•[–¾×F’Ē‰Į */ if @ŠØ’čCD1 is not null begin insert TK“`•[–¾×F ( ‰‰KCD ,‰ļŽŠCD , “`•[”Ō†, “`•[‹ę•Ŗ , “`•[SEQ , ŠØ’čCD , ŽŲ•ū‹ąŠz ,‘Ż•ū‹ąŠz ,‘ŻŽŲ‹ę•Ŗ ,‘ŠŽčŠØ’č , “E—vCD , “E—v ) values ( @‰‰KCD , @‰ļŽŠCD , @“`•[”Ō†, @“`•[‹ę•Ŗ, @LNO1 , @ŠØ’čCD1 , @ŽŲ•ū‹ąŠz1 ,@‘Ż•ū‹ąŠz1 ,@‘ŻŽŲ‹ę•Ŗ1 ,@‘ŠŽčAC1 , @“E—vCD1 , @“E—v1 ) if @@error <> 0 begin rollback transaction select -1 return end end if @ŠØ’čCD2 is not null begin insert TK“`•[–¾×F ( ‰‰KCD ,‰ļŽŠCD , “`•[”Ō†, “`•[‹ę•Ŗ , “`•[SEQ , ŠØ’čCD , ŽŲ•ū‹ąŠz ,‘Ż•ū‹ąŠz ,‘ŻŽŲ‹ę•Ŗ ,‘ŠŽčŠØ’č , “E—vCD , “E—v ) values ( @‰‰KCD , @‰ļŽŠCD , @“`•[”Ō†, @“`•[‹ę•Ŗ, @LNO2 , @ŠØ’čCD2 , @ŽŲ•ū‹ąŠz2 ,@‘Ż•ū‹ąŠz2 ,@‘ŻŽŲ‹ę•Ŗ2 ,@‘ŠŽčAC2 , @“E—vCD2 , @“E—v2 ) if @@error <> 0 begin rollback transaction select -1 return end end if @ŠØ’čCD3 is not null begin insert TK“`•[–¾×F ( ‰‰KCD ,‰ļŽŠCD , “`•[”Ō†, “`•[‹ę•Ŗ , “`•[SEQ , ŠØ’čCD , ŽŲ•ū‹ąŠz ,‘Ż•ū‹ąŠz ,‘ŻŽŲ‹ę•Ŗ ,‘ŠŽčŠØ’č , “E—vCD , “E—v ) values ( @‰‰KCD , @‰ļŽŠCD , @“`•[”Ō†, @“`•[‹ę•Ŗ, @LNO3 , @ŠØ’čCD3 , @ŽŲ•ū‹ąŠz3 ,@‘Ż•ū‹ąŠz3 ,@‘ŻŽŲ‹ę•Ŗ3 ,@‘ŠŽčAC3 , @“E—vCD3 , @“E—v3 ) if @@error <> 0 begin rollback transaction select -1 return end end if @ŠØ’čCD4 is not null begin insert TK“`•[–¾×F ( ‰‰KCD ,‰ļŽŠCD , “`•[”Ō†, “`•[‹ę•Ŗ , “`•[SEQ , ŠØ’čCD , ŽŲ•ū‹ąŠz ,‘Ż•ū‹ąŠz ,‘ŻŽŲ‹ę•Ŗ ,‘ŠŽčŠØ’č , “E—vCD , “E—v ) values ( @‰‰KCD , @‰ļŽŠCD , @“`•[”Ō†, @“`•[‹ę•Ŗ, @LNO4 , @ŠØ’čCD4 , @ŽŲ•ū‹ąŠz4 ,@‘Ż•ū‹ąŠz4 ,@‘ŻŽŲ‹ę•Ŗ4 ,@‘ŠŽčAC4 , @“E—vCD4 , @“E—v4 ) if @@error <> 0 begin rollback transaction select -1 return end end if @ŠØ’čCD5 is not null begin insert TK“`•[–¾×F ( ‰‰KCD ,‰ļŽŠCD , “`•[”Ō†, “`•[‹ę•Ŗ , “`•[SEQ , ŠØ’čCD , ŽŲ•ū‹ąŠz ,‘Ż•ū‹ąŠz ,‘ŻŽŲ‹ę•Ŗ ,‘ŠŽčŠØ’č , “E—vCD , “E—v ) values ( @‰‰KCD , @‰ļŽŠCD , @“`•[”Ō†, @“`•[‹ę•Ŗ, @LNO5 , @ŠØ’čCD5 , @ŽŲ•ū‹ąŠz5 ,@‘Ż•ū‹ąŠz5 ,@‘ŻŽŲ‹ę•Ŗ5 ,@‘ŠŽčAC5 , @“E—vCD5 , @“E—v5 ) if @@error <> 0 begin rollback transaction select -1 return end end if @ŠØ’čCD6 is not null begin insert TK“`•[–¾×F ( ‰‰KCD ,‰ļŽŠCD , “`•[”Ō†, “`•[‹ę•Ŗ , “`•[SEQ , ŠØ’čCD , ŽŲ•ū‹ąŠz ,‘Ż•ū‹ąŠz ,‘ŻŽŲ‹ę•Ŗ ,‘ŠŽčŠØ’č , “E—vCD , “E—v ) values ( @‰‰KCD , @‰ļŽŠCD , @“`•[”Ō†, @“`•[‹ę•Ŗ, @LNO6 , @ŠØ’čCD6 , @ŽŲ•ū‹ąŠz6 ,@‘Ż•ū‹ąŠz6 ,@‘ŻŽŲ‹ę•Ŗ6 ,@‘ŠŽčAC6 , @“E—vCD6 , @“E—v6 ) if @@error <> 0 begin rollback transaction select -1 return end end if @ŠØ’čCD7 is not null begin insert TK“`•[–¾×F ( ‰‰KCD ,‰ļŽŠCD , “`•[”Ō†, “`•[‹ę•Ŗ , “`•[SEQ , ŠØ’čCD , ŽŲ•ū‹ąŠz ,‘Ż•ū‹ąŠz ,‘ŻŽŲ‹ę•Ŗ ,‘ŠŽčŠØ’č , “E—vCD , “E—v ) values ( @‰‰KCD , @‰ļŽŠCD , @“`•[”Ō†, @“`•[‹ę•Ŗ, @LNO7 , @ŠØ’čCD7 , @ŽŲ•ū‹ąŠz7 ,@‘Ż•ū‹ąŠz7 ,@‘ŻŽŲ‹ę•Ŗ7 ,@‘ŠŽčAC7 , @“E—vCD7 , @“E—v7 ) if @@error <> 0 begin rollback transaction select -1 return end end if @ŠØ’čCD8 is not null begin insert TK“`•[–¾×F ( ‰‰KCD ,‰ļŽŠCD , “`•[”Ō†, “`•[‹ę•Ŗ , “`•[SEQ , ŠØ’čCD , ŽŲ•ū‹ąŠz ,‘Ż•ū‹ąŠz ,‘ŻŽŲ‹ę•Ŗ ,‘ŠŽčŠØ’č , “E—vCD , “E—v ) values ( @‰‰KCD , @‰ļŽŠCD , @“`•[”Ō†, @“`•[‹ę•Ŗ, @LNO8 , @ŠØ’čCD8 , @ŽŲ•ū‹ąŠz8 ,@‘Ż•ū‹ąŠz8 ,@‘ŻŽŲ‹ę•Ŗ8 ,@‘ŠŽčAC8 , @“E—vCD8 , @“E—v8 ) if @@error <> 0 begin rollback transaction select -1 return end end if @ŠØ’čCD9 is not null begin insert TK“`•[–¾×F ( ‰‰KCD ,‰ļŽŠCD , “`•[”Ō†, “`•[‹ę•Ŗ , “`•[SEQ , ŠØ’čCD , ŽŲ•ū‹ąŠz ,‘Ż•ū‹ąŠz ,‘ŻŽŲ‹ę•Ŗ ,‘ŠŽčŠØ’č , “E—vCD , “E—v ) values ( @‰‰KCD , @‰ļŽŠCD , @“`•[”Ō†, @“`•[‹ę•Ŗ, @LNO9 , @ŠØ’čCD9 , @ŽŲ•ū‹ąŠz9 ,@‘Ż•ū‹ąŠz9 ,@‘ŻŽŲ‹ę•Ŗ9 ,@‘ŠŽčAC9 , @“E—vCD9 , @“E—v9 ) if @@error <> 0 begin rollback transaction select -1 return end end if @ŠØ’čCD10 is not null begin insert TK“`•[–¾×F ( ‰‰KCD ,‰ļŽŠCD , “`•[”Ō†, “`•[‹ę•Ŗ , “`•[SEQ , ŠØ’čCD , ŽŲ•ū‹ąŠz ,‘Ż•ū‹ąŠz ,‘ŻŽŲ‹ę•Ŗ ,‘ŠŽčŠØ’č , “E—vCD , “E—v ) values ( @‰‰KCD , @‰ļŽŠCD , @“`•[”Ō†, @“`•[‹ę•Ŗ, @LNO10 , @ŠØ’čCD10 , @ŽŲ•ū‹ąŠz10 ,@‘Ż•ū‹ąŠz10 ,@‘ŻŽŲ‹ę•Ŗ10 ,@‘ŠŽčAC10 , @“E—vCD10 , @“E—v10 ) if @@error <> 0 begin rollback transaction select -1 return end end commit transaction GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE dbo.SK“`•[_U‘Ö“`•[XV @“`•[”Ō† varchar(12) , @Žęˆų“ś datetime, @“`•[“E—v varchar(40) , @“`•[‹ę•Ŗ int , @“`•[ˆ—‹ę•Ŗ smallint , @‰‰KCD varchar(12) , @‰ļŽŠCD varchar(20) , @Šw¶”Ō† varchar(12) , @LNO1 smallint , @ŠØ’čCD1 varchar(4), @ŽŲ•ū‹ąŠz1 money , @‘Ż•ū‹ąŠz1 money , @‘ŻŽŲ‹ę•Ŗ1 varchar(1), @“E—vCD1 varchar(20), @“E—v1 varchar(30) , @‘ŠŽčAC1 varchar(4) , @LNO2 smallint , @ŠØ’čCD2 varchar(4), @ŽŲ•ū‹ąŠz2 money , @‘Ż•ū‹ąŠz2 money , @‘ŻŽŲ‹ę•Ŗ2 varchar(1), @“E—vCD2 varchar(20), @“E—v2 varchar(30) , @‘ŠŽčAC2 varchar(4) , @LNO3 smallint , @ŠØ’čCD3 varchar(4), @ŽŲ•ū‹ąŠz3 money , @‘Ż•ū‹ąŠz3 money , @‘ŻŽŲ‹ę•Ŗ3 varchar(1), @“E—vCD3 varchar(20) , @“E—v3 varchar(30) , @‘ŠŽčAC3 varchar(4) , @LNO4 smallint , @ŠØ’čCD4 varchar(4), @ŽŲ•ū‹ąŠz4 money , @‘Ż•ū‹ąŠz4 money , @‘ŻŽŲ‹ę•Ŗ4 varchar(1), @“E—vCD4 varchar(20) , @“E—v4 varchar(30) , @‘ŠŽčAC4 varchar(4) , @LNO5 smallint , @ŠØ’čCD5 varchar(4), @ŽŲ•ū‹ąŠz5 money , @‘Ż•ū‹ąŠz5 money , @‘ŻŽŲ‹ę•Ŗ5 varchar(1), @“E—vCD5 varchar(20) , @“E—v5 varchar(30) , @‘ŠŽčAC5 varchar(4) , @LNO6 smallint , @ŠØ’čCD6 varchar(4), @ŽŲ•ū‹ąŠz6 money , @‘Ż•ū‹ąŠz6 money , @‘ŻŽŲ‹ę•Ŗ6 varchar(1), @“E—vCD6 varchar(20) , @“E—v6 varchar(30) , @‘ŠŽčAC6 varchar(4) , @LNO7 smallint , @ŠØ’čCD7 varchar(4), @ŽŲ•ū‹ąŠz7 money , @‘Ż•ū‹ąŠz7 money , @‘ŻŽŲ‹ę•Ŗ7 varchar(1), @“E—vCD7 varchar(20) , @“E—v7 varchar(30) , @‘ŠŽčAC7 varchar(4) , @LNO8 smallint , @ŠØ’čCD8 varchar(4), @ŽŲ•ū‹ąŠz8 money , @‘Ż•ū‹ąŠz8 money , @‘ŻŽŲ‹ę•Ŗ8 varchar(1), @“E—vCD8 varchar(20) , @“E—v8 varchar(30) , @‘ŠŽčAC8 varchar(4) , @LNO9 smallint , @ŠØ’čCD9 varchar(4), @ŽŲ•ū‹ąŠz9 money , @‘Ż•ū‹ąŠz9 money , @‘ŻŽŲ‹ę•Ŗ9 varchar(1), @“E—vCD9 varchar(20) , @“E—v9 varchar(30) , @‘ŠŽčAC9 varchar(4) , @LNO10 smallint , @ŠØ’čCD10 varchar(4), @ŽŲ•ū‹ąŠz10 money , @‘Ż•ū‹ąŠz10 money , @‘ŻŽŲ‹ę•Ŗ10 varchar(1), @“E—vCD10 varchar(20) , @“E—v10 varchar(30) , @‘ŠŽčAC10 varchar(4) AS declare @”Ō† varchar(12) begin transaction set @“`•[”Ō† = REPLACE(STR( @“`•[”Ō†, 7), ' ','0') delete from TK“`•[F WHERE ‰‰KCD = @‰‰KCD AND ‰ļŽŠCD = @‰ļŽŠCD AND “`•[‹ę•Ŗ = 5 AND “`•[”Ō† = @“`•[”Ō† delete from TK“`•[–¾×F WHERE ‰‰KCD = @‰‰KCD AND ‰ļŽŠCD = @‰ļŽŠCD AND “`•[‹ę•Ŗ = 5 AND “`•[”Ō† = @“`•[”Ō† execute @“`•[”Ō† = SK“`•[_U‘Ö“`•[‘}“ü '', @Žęˆų“ś, @“`•[“E—v, @“`•[‹ę•Ŗ, @“`•[ˆ—‹ę•Ŗ, @‰‰KCD, @‰ļŽŠCD, @Šw¶”Ō† , @LNO1, @ŠØ’čCD1, @ŽŲ•ū‹ąŠz1, @‘Ż•ū‹ąŠz1, @‘ŻŽŲ‹ę•Ŗ1, @“E—vCD1, @“E—v1, @‘ŠŽčAC1 , @LNO2, @ŠØ’čCD2, @ŽŲ•ū‹ąŠz2, @‘Ż•ū‹ąŠz2, @‘ŻŽŲ‹ę•Ŗ2, @“E—vCD2, @“E—v2, @‘ŠŽčAC2 , @LNO3, @ŠØ’čCD3, @ŽŲ•ū‹ąŠz3, @‘Ż•ū‹ąŠz3, @‘ŻŽŲ‹ę•Ŗ3, @“E—vCD3, @“E—v3, @‘ŠŽčAC3 , @LNO4, @ŠØ’čCD4, @ŽŲ•ū‹ąŠz4, @‘Ż•ū‹ąŠz4, @‘ŻŽŲ‹ę•Ŗ4, @“E—vCD4, @“E—v4, @‘ŠŽčAC4 , @LNO5, @ŠØ’čCD5, @ŽŲ•ū‹ąŠz5, @‘Ż•ū‹ąŠz5, @‘ŻŽŲ‹ę•Ŗ5, @“E—vCD5, @“E—v5, @‘ŠŽčAC5 , @LNO6, @ŠØ’čCD6, @ŽŲ•ū‹ąŠz6, @‘Ż•ū‹ąŠz6, @‘ŻŽŲ‹ę•Ŗ6, @“E—vCD6, @“E—v6, @‘ŠŽčAC6 , @LNO7, @ŠØ’čCD7, @ŽŲ•ū‹ąŠz7, @‘Ż•ū‹ąŠz7, @‘ŻŽŲ‹ę•Ŗ7, @“E—vCD7, @“E—v7, @‘ŠŽčAC7 , @LNO8, @ŠØ’čCD8, @ŽŲ•ū‹ąŠz8, @‘Ż•ū‹ąŠz8, @‘ŻŽŲ‹ę•Ŗ8, @“E—vCD8, @“E—v8, @‘ŠŽčAC8 , @LNO9, @ŠØ’čCD9, @ŽŲ•ū‹ąŠz9, @‘Ż•ū‹ąŠz9, @‘ŻŽŲ‹ę•Ŗ9, @“E—vCD9, @“E—v9, @‘ŠŽčAC9 , @LNO10, @ŠØ’čCD10, @ŽŲ•ū‹ąŠz10, @‘Ż•ū‹ąŠz10, @‘ŻŽŲ‹ę•Ŗ10, @“E—vCD10, @“E—v10, @‘ŠŽčAC10 commit transaction GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE dbo.SK“`•[‘O”Ō† @‰‰KCD varchar(12), @‰ļŽŠCD varchar(20), @“`•[‹ę•Ŗ varchar(40), @Žęˆų“ś datetime, @“`•[”Ō† varchar(40) AS SELECT TOP 1 TK“`•[F.“`•[”Ō† FROM TK“`•[F WHERE TK“`•[F.‰‰KCD = @‰‰KCD AND TK“`•[F.‰ļŽŠCD = @‰ļŽŠCD AND TK“`•[F.“`•[‹ę•Ŗ = @“`•[‹ę•Ŗ AND TK“`•[F.“`•[ˆ—‹ę•Ŗ = 1 AND ( (Žęˆų“ś = @Žęˆų“ś AND “`•[”Ō† < @“`•[”Ō†) OR (Žęˆų“ś < @Žęˆų“ś) ) AND ķœƒtƒ‰ƒO = 0 ORDER BY TK“`•[F.Žęˆų“ś DESC, TK“`•[F.“`•[”Ō† DESC GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE dbo.SK“`•[ŽŸ”Ō† @‰‰KCD varchar(12), @‰ļŽŠCD varchar(20), @“`•[‹ę•Ŗ varchar(40), @Žęˆų“ś datetime, @“`•[”Ō† varchar(40) AS SELECT TOP 1 TK“`•[F.“`•[”Ō† FROM TK“`•[F WHERE TK“`•[F.‰‰KCD = @‰‰KCD AND TK“`•[F.‰ļŽŠCD = @‰ļŽŠCD AND TK“`•[F.“`•[‹ę•Ŗ = @“`•[‹ę•Ŗ AND TK“`•[F.“`•[ˆ—‹ę•Ŗ = 1 AND ( (Žęˆų“ś = @Žęˆų“ś AND “`•[”Ō† > @“`•[”Ō†) OR (Žęˆų“ś > @Žęˆų“ś) ) AND ķœƒtƒ‰ƒO = 0 ORDER BY TK“`•[F.Žęˆų“ś, TK“`•[F.“`•[”Ō† GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE dbo.SKŽ©“®“]‹L’ •ė_‡ŒvŽc‚ŽŽŽZ•\Ę‰ļ @‰‰KCD varchar(12), @‰ļŽŠCD varchar(20), @ŠJŽn“ś datetime, @I—¹“ś datetime AS SELECT TK“`•[–¾×F.‰‰KCD, TK“`•[–¾×F.‰ļŽŠCD, TK“`•[–¾×F.ŠØ’čCD, MIN(TSŠØ’č‰Č–ŚM.ŠØ’č–¼Ģ) AS ŠØ’č–¼Ģ, MIN(TSŠØ’č‰Č–ŚM.‘ŻŽŲ‹ę•Ŗ) AS ‘ŻŽŲ‹ę•Ŗ, SUM(TK“`•[–¾×F.ŽŲ•ū‹ąŠz) AS ŽŲ•ū‹ąŠz, SUM(TK“`•[–¾×F.‘Ż•ū‹ąŠz) AS ‘Ż•ū‹ąŠz, CASE WHEN ISNULL(SUM(TK“`•[–¾×F.ŽŲ•ū‹ąŠz), 0) - ISNULL(SUM(TK“`•[–¾×F.‘Ż•ū‹ąŠz), 0) > 0 THEN ISNULL(SUM(TK“`•[–¾×F.ŽŲ•ū‹ąŠz), 0) - ISNULL(SUM(TK“`•[–¾×F.‘Ż•ū‹ąŠz), 0) ELSE 0 END AS ŽŲ•ūŽc‚, CASE WHEN ISNULL(SUM(TK“`•[–¾×F.‘Ż•ū‹ąŠz), 0) - ISNULL(SUM(TK“`•[–¾×F.ŽŲ•ū‹ąŠz), 0) > 0 THEN ISNULL(SUM(TK“`•[–¾×F.‘Ż•ū‹ąŠz), 0) - ISNULL(SUM(TK“`•[–¾×F.ŽŲ•ū‹ąŠz), 0) ELSE 0 END AS ‘Ż•ūŽc‚ FROM TK“`•[F INNER JOIN TK“`•[–¾×F ON TK“`•[F.‰‰KCD = TK“`•[–¾×F.‰‰KCD AND TK“`•[F.‰ļŽŠCD = TK“`•[–¾×F.‰ļŽŠCD AND TK“`•[F.“`•[‹ę•Ŗ = TK“`•[–¾×F.“`•[‹ę•Ŗ AND TK“`•[F.“`•[”Ō† = TK“`•[–¾×F.“`•[”Ō† INNER JOIN TSŠØ’č‰Č–ŚM ON TK“`•[–¾×F.ŠØ’čCD = TSŠØ’č‰Č–ŚM.ŠØ’čCD WHERE TK“`•[F.ķœƒtƒ‰ƒO = 0 and TK“`•[F.‰‰KCD = @‰‰KCD and TK“`•[F.‰ļŽŠCD = @‰ļŽŠCD GROUP BY TK“`•[–¾×F.‰‰KCD, TK“`•[–¾×F.‰ļŽŠCD, TK“`•[–¾×F.ŠØ’čCD ORDER BY TK“`•[–¾×F.ŠØ’čCD GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE dbo.SKŽ©“®“]‹L’ •ė_ą–±”•\ @‰‰KCD varchar(12), @‰ļŽŠCD varchar(20), @ŠJŽn“ś datetime, @I—¹“ś datetime ,@ŠØ’č‹ę•Ŗ varchar(1) AS SELECT TK“`•[–¾×F.‰‰KCD, TK“`•[–¾×F.‰ļŽŠCD, TK“`•[–¾×F.ŠØ’čCD, MIN(TSŠØ’č‰Č–ŚM.ŠØ’č‹ę•Ŗ) AS ŠØ’č‹ę•Ŗ, MIN(TSŠØ’č‰Č–ŚM.ŠØ’č×–Ś) AS ŠØ’č×–Ś, MIN(TSŠØ’č‰Č–ŚM.ŠØ’č–¼Ģ) AS ŠØ’č–¼Ģ, MIN(TSŠØ’č‰Č–ŚM.‘ŻŽŲ‹ę•Ŗ) AS ‘ŻŽŲ‹ę•Ŗ, MIN(TSŠØ’č‰Č–ŚM.”•\WŒvCD) AS ”•\WŒvCD, CASE WHEN MIN(TSŠØ’č‰Č–ŚM.‘ŻŽŲ‹ę•Ŗ) = 'D' THEN ISNULL(SUM(TK“`•[–¾×F.ŽŲ•ū‹ąŠz),0) - ISNULL(SUM(TK“`•[–¾×F.‘Ż•ū‹ąŠz),0) ELSE 0 END AS ŽŲ•ū‹ąŠz, CASE WHEN MIN(TSŠØ’č‰Č–ŚM.‘ŻŽŲ‹ę•Ŗ) = 'C' THEN ISNULL(SUM(TK“`•[–¾×F.‘Ż•ū‹ąŠz),0) - ISNULL(SUM(TK“`•[–¾×F.ŽŲ•ū‹ąŠz),0) ELSE 0 END AS ‘Ż•ū‹ąŠz FROM TK“`•[F INNER JOIN TK“`•[–¾×F ON TK“`•[F.“`•[”Ō† = TK“`•[–¾×F.“`•[”Ō† AND TK“`•[F.“`•[‹ę•Ŗ = TK“`•[–¾×F.“`•[‹ę•Ŗ AND TK“`•[F.‰ļŽŠCD = TK“`•[–¾×F.‰ļŽŠCD AND TK“`•[F.‰‰KCD = TK“`•[–¾×F.‰‰KCD INNER JOIN TSŠØ’č‰Č–ŚM ON TK“`•[–¾×F.ŠØ’čCD = TSŠØ’č‰Č–ŚM.ŠØ’čCD WHERE TK“`•[F.ķœƒtƒ‰ƒO = 0 AND TK“`•[F.‰‰KCD = @‰‰KCD AND TK“`•[F.‰ļŽŠCD = @‰ļŽŠCD AND TSŠØ’č‰Č–ŚM.ŠØ’č‹ę•Ŗ = @ŠØ’č‹ę•Ŗ GROUP BY TK“`•[–¾×F.‰‰KCD, TK“`•[–¾×F.‰ļŽŠCD, TK“`•[–¾×F.ŠØ’čCD, TSŠØ’č‰Č–ŚM.ŠØ’č‹ę•Ŗ, TSŠØ’č‰Č–ŚM.ŠØ’č×–Ś, TSŠØ’č‰Č–ŚM.ŠØ’č–¼Ģ, TSŠØ’č‰Č–ŚM.‘ŻŽŲ‹ę•Ŗ, TSŠØ’č‰Č–ŚM.”•\WŒvCD GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE dbo.ST–¼ŽhƒfƒUƒCƒ“ˆź—— @c‰”‹ę•Ŗ varchar(2) AS begin transaction SELECT –¼ŽhƒfƒUƒCƒ“CD, ƒfƒUƒCƒ“ FROM TM–¼ŽhƒfƒUƒCƒ“M WHERE (c‰”‹ę•Ŗ = @c‰”‹ę•Ŗ) commit transaction GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE dbo.ST–¼Žhƒf[ƒ^Žę“¾ @‰‰KCD varchar(12), @‰ļŽŠCD varchar(20), @Šw¶”Ō† varchar(20), @–¼ŽhƒfƒUƒCƒ“CD varchar(12) AS begin transaction SELECT *, —\”õ‚  As –šE, —\”õ‚¢ As FAX FROM TM–¼Žhƒf[ƒ^F WHERE (‰‰KCD = @‰‰KCD) AND (‰ļŽŠCD = @‰ļŽŠCD) AND (Šw¶”Ō† = @Šw¶”Ō†) AND (–¼ŽhƒfƒUƒCƒ“CD = @–¼ŽhƒfƒUƒCƒ“CD) commit transaction GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE dbo.ST–¼Žhƒf[ƒ^XV @‰‰KCD varchar(12), @‰ļŽŠCD varchar(20), @Šw¶”Ō† varchar(20), @–¼ŽhƒfƒUƒCƒ“CD varchar(12), @‰ļŽŠ–¼Ģ varchar(50), @ŒØ‘ varchar(50), @–šE varchar(50), @Ž–¼ varchar(50), @—X•Ö”Ō† varchar(50), @ZŠ1 varchar(50), @ZŠ2 varchar(50), @“d˜b”Ō† varchar(50), @FAX varchar(50), @ƒ[ƒ‹ƒAƒhƒŒƒX varchar(50), @‰ę‘œƒtƒ@ƒCƒ‹ varchar(250) AS begin transaction if not(exists(SELECT * FROM TM–¼Žhƒf[ƒ^F WHERE ‰‰KCD = @‰‰KCD AND ‰ļŽŠCD = @‰ļŽŠCD AND Šw¶”Ō† = @Šw¶”Ō†)) begin INSERT TM–¼Žhƒf[ƒ^F (‰‰KCD, ‰ļŽŠCD, Šw¶”Ō†, –¼ŽhƒfƒUƒCƒ“CD) VALUES (@‰‰KCD, @‰ļŽŠCD, @Šw¶”Ō†, @–¼ŽhƒfƒUƒCƒ“CD) end UPDATE TM–¼Žhƒf[ƒ^F SET ‰ļŽŠ–¼Ģ = @‰ļŽŠ–¼Ģ, ŒØ‘ = @ŒØ‘, Ž–¼ = @Ž–¼, —X•Ö”Ō† = @—X•Ö”Ō†, ZŠ1 = @ZŠ1, ZŠ2 = @ZŠ2, “d˜b”Ō† = @“d˜b”Ō†, ƒ[ƒ‹ƒAƒhƒŒƒX = @ƒ[ƒ‹ƒAƒhƒŒƒX, ‰ę‘œƒtƒ@ƒCƒ‹ = @‰ę‘œƒtƒ@ƒCƒ‹, —\”õ‚  = @–šE, —\”õ‚¢ = @FAX WHERE (‰‰KCD = @‰‰KCD) AND (‰ļŽŠCD = @‰ļŽŠCD) AND (Šw¶”Ō† = @Šw¶”Ō†) AND (–¼ŽhƒfƒUƒCƒ“CD = @–¼ŽhƒfƒUƒCƒ“CD) commit transaction GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE dbo.STŲŒ”‰ļˆõ_“o˜^ķœ /* ˆ—‹ę•Ŗ 0:“o˜^@‚PFķœ */ @ˆ—‹ę•Ŗ smallint /* Žęˆųģ¬ŽŅ‹ę•Ŗ A:‘ć‹ą‚ĢŽx•„‚¢‘¤@@B:‘ć‹ą‚ĢŽóŽę‘¤ */ ,@TradeKbn varchar(1) /* ctpTradeTran */ ,@PracticeCode varchar(8),@APrivateCode varchar(12) ,@ACompanyCode varchar(12),@ATenantCode varchar(12),@ALoginId varchar(12) ,@ActNo int ,@TradeCode varchar(4), @OrderNo int, @OrderSeq smallint ,@BPrivateCode varchar(12), @BCompanyCode varchar(12), @BTenantCode varchar(12) ,@ItemType varchar(1) ,@ItemCode varchar(4) ,@ItemName varchar(40) ,@ItemImage varchar(100) ,@PaymentTerm smallint,@ProcessStatus varchar(1) ,@BusinessDateA datetime ,@BusinessDateB datetime ,@BusinessDateE datetime ,@BusinessDateT datetime ,@TransportFlag int ,@SellsDiscFlag int ,@COfficeAssetsFlag int ,@Qty int,@StandardPrice money,@MakesPrice money,@StockPrice money,@SellsPrice money ,@TransportPrice money,@ConsumptionTax money ,@CCreditRate real ,@MakesAmnt money ,@StockAmnt money ,@SellsAmnt money ,@TaxAmnt money ,@TransportAmnt money ,@TransportTax money ,@CreditAmnt money ,@AAmnt money,@BAmnt money ,@RemarksA varchar(250) ,@RemarksB varchar(250) ,@RemarksE varchar(250) ,@AddDate datetime,@UpdDate datetime,@AddProc varchar(20), @UpdProc varchar(20) /* cttTradeMemberM */ ,@AccountNo varchar(12),@UserType smallint ,@MemberCode varchar(20),@MemberName varchar(40),@Sign varchar(20) /* cteMailTran */ ,@SPrivateCode varchar(12) ,@SCompanyCode varchar(12) ,@STenantCode varchar(12) ,@TPrivateCode varchar(12),@TCompanyCode varchar(12) ,@Title varchar(40), @Message varchar(7000) ,@ReadFlag smallint ,@SndDelFlag smallint , @RecDelFlag smallint AS declare @MaxNo int , @MailNo int ,@status int begin transaction /* ‰ļˆõƒ}ƒXƒ^[“o˜^ķœ */ if @ˆ—‹ę•Ŗ = 0 /* cttTradeMemberM@“o˜^ */ begin Insert into cttTradeMemberM (PracticeCode,AccountNo,UserType,MemberCode,MemberName,[Sign],AddDate,UpdDate,AddProc,UpdProc) Values (@PracticeCode , @AccountNo , @UserType , @MemberCode , @MemberName , @Sign ,@AddDate , @UpdDate , @AddProc , @UpdProc ) end else /* cttTradeMemberM@ķœ */ begin DELETE FROM cttTradeMemberM WHERE PracticeCode = @PracticeCode AND UserType = @UserType AND AccountNo = @AccountNo end if @@error <> 0 begin rollback transaction select -1 return end /* Žęˆų–¾×“o˜^ : STŲŒ”Žęˆų–¾×“o˜^‚š‚b‚`‚k‚k@*/ exec @status = SXŽęˆų–¾×“o˜^ @TradeKbn ,@PracticeCode ,@APrivateCode ,@ACompanyCode,@ATenantCode,@ALoginId ,@ActNo ,@TradeCode , @OrderNo , @OrderSeq ,@BPrivateCode , @BCompanyCode , @BTenantCode ,@ItemType ,@ItemCode ,@ItemName ,@ItemImage ,@PaymentTerm ,@ProcessStatus ,@BusinessDateA ,@BusinessDateB ,@BusinessDateE ,@BusinessDateT ,@TransportFlag ,@SellsDiscFlag ,@COfficeAssetsFlag ,@Qty ,@StandardPrice ,@MakesPrice ,@StockPrice ,@SellsPrice ,@TransportPrice ,@ConsumptionTax ,@CCreditRate ,@MakesAmnt ,@StockAmnt ,@SellsAmnt ,@TaxAmnt ,@TransportAmnt ,@TransportTax ,@CreditAmnt ,@AAmnt ,@BAmnt ,@RemarksA ,@RemarksB ,@RemarksE ,@AddDate ,@UpdDate ,@AddProc , @UpdProc /* Ž©g‚ĢƒGƒ‰[*/ if @@error <> 0 begin rollback transaction select -1 return end /* ‚b‚`‚k‚k‚É‚ę‚éƒGƒ‰[@*/ if @status = -1 begin rollback transaction select -1 return end /* Ģ”Ō‚³‚ź‚½‚`‚b‚s‚m‚n‚šŻ’č@*/ SET @ActNo = @status /* ƒ[ƒ‹‘—M */ /* ƒ[ƒ‹‘—M-1 ƒ[ƒ‹NO Ģ”Ō */ SELECT @MailNo = (SELECT Max(MailNo) FROM cteMailTrn WHERE PracticeCode = @PracticeCode) SELECT @MailNo = isnull(@MailNo,0) + 1 if @@error <> 0 begin rollback transaction select -1 return end /* ƒ[ƒ‹‘—M-2 cteMailTrn ģ¬ */ Insert into cteMailTrn (PracticeCode,MailNo,SPrivateCode,SCompanyCode,STenantCode,TPrivateCode,TCompanyCode ,ALoginId,ActNo,Title,Message,ReadFlag,SndDelFlag,RecDelFlag ,AddDate,UpdDate,AddProc,UpdProc) Values (@PracticeCode,@MailNo,@SPrivateCode,@SCompanyCode,@STenantCode,@TPrivateCode,@TCompanyCode ,@ALoginId,@ActNo,@Title,@Message,@ReadFlag,@SndDelFlag,@RecDelFlag ,@AddDate,@UpdDate,@AddProc,@UpdProc) if @@error <> 0 begin rollback transaction select -1 return end commit transaction SELECT 0 GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE dbo.SVŠØ’č‰Č–ŚŽę“¾ @ŠØ’č×–Ś varchar(40), @‹ĘŽķ‹ę•Ŗ varchar(40), @‰‰KCD varchar(12) AS SELECT @‰‰KCD = "SYSORIGN" If @ŠØ’č×–Ś = '' begin SELECT ŠØ’čCD , ŠØ’č–¼Ģ, ŠØ’č×–Ś, ŠØ’č‹ę•Ŗ FROM TSŠØ’č‰Č–ŚM WHERE (‹ĘŽķCD = @‹ĘŽķ‹ę•Ŗ) AND (‰‰KCD = @‰‰KCD) ORDER BY ŠØ’čCD end else begin SELECT ŠØ’čCD , ŠØ’č–¼Ģ, ŠØ’č×–Ś, ŠØ’č‹ę•Ŗ FROM TSŠØ’č‰Č–ŚM WHERE (‹ĘŽķCD = @‹ĘŽķ‹ę•Ŗ) AND (‰‰KCD = @‰‰KCD) AND (ŠØ’č×–Ś = @ŠØ’č×–Ś) ORDER BY ŠØ’čCD End GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE dbo.SV“E—vˆź—— @‰‰KCD varchar(12), @‰ļŽŠCD varchar(20), @Šw¶”Ō† varchar(40) AS SELECT * FROM TK“`•[F WHERE 1 = 0 GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE dbo.SXŽęˆų–¾×“o˜^ /* ‘Sƒf[ƒ^€–Ś‚šŽó‚ÆŽę‚čActpTradeTran‚ɃŒƒR[ƒh‚Ģ‘}“ü‚šs‚¤@*/ /* ‚n‚j‚Å‚ ‚ź‚ĪAĢ”Ō‚µ‚½‚`‚b‚s‚m‚n‚š–ß‚č’l‚Ę‚µ‚Ä•Ō‚·@‚m‚f‚Ķ|‚P@*/ /* Žęˆųģ¬ŽŅ‹ę•Ŗ A:‘ć‹ą‚ĢŽx•„‚¢‘¤@@B:‘ć‹ą‚ĢŽóŽę‘¤ */ @TradeKbn varchar(1) /* ctpTradeTran */ ,@PracticeCode varchar(8),@APrivateCode varchar(12) ,@ACompanyCode varchar(12),@ATenantCode varchar(12),@ALoginId varchar(12) ,@ActNo int ,@TradeCode varchar(4), @OrderNo int, @OrderSeq smallint ,@BPrivateCode varchar(12), @BCompanyCode varchar(12), @BTenantCode varchar(12) ,@ItemType varchar(1) ,@ItemCode varchar(4) ,@ItemName varchar(40) ,@ItemImage varchar(100) ,@PaymentTerm smallint,@ProcessStatus varchar(1) ,@BusinessDateA datetime ,@BusinessDateB datetime ,@BusinessDateE datetime ,@BusinessDateT datetime ,@TransportFlag int ,@SellsDiscFlag int ,@COfficeAssetsFlag int ,@Qty int,@StandardPrice money,@MakesPrice money,@StockPrice money,@SellsPrice money ,@TransportPrice money,@ConsumptionTax money ,@CCreditRate real ,@MakesAmnt money ,@StockAmnt money ,@SellsAmnt money ,@TaxAmnt money ,@TransportAmnt money ,@TransportTax money ,@CreditAmnt money ,@AAmnt money,@BAmnt money ,@RemarksA varchar(250) ,@RemarksB varchar(250) ,@RemarksE varchar(250) ,@AddDate datetime,@UpdDate datetime,@AddProc varchar(20), @UpdProc varchar(20) AS declare @MaxNo int begin transaction /* Žęˆų–¾×“o˜^ */ /* Žęˆų–¾×“o˜^-1 ActNoĢ”Ō */ SET @MaxNo = isnull((SELECT MAX(ActNo) FROM ctpTradeTrn WHERE PracticeCode = @PracticeCode AND ALoginId = @ALoginId ),0) SELECT @ActNo = @MaxNo + 1 if @@error <> 0 begin rollback transaction select -1 return end /* Žęˆų–¾×“o˜^-2 OrderNoĢ”Ō */ if @TradeKbn = 'a' or @TradeKbn = 'A' begin SET @MaxNo = isnull((SELECT Max(OrderNo) FROM ctpTradeTrn WHERE (PracticeCode = @PracticeCode) AND (TradeCode = @TradeCode) AND (APrivateCode = @APrivateCode) AND (ACompanyCode = @ACompanyCode) AND (ATenantCode = @ATenantCode)),0) end else begin SET @MaxNo = isnull((SELECT Max(OrderNo) FROM ctpTradeTrn WHERE (PracticeCode = @PracticeCode) AND (TradeCode = @TradeCode) AND (BPrivateCode = @BPrivateCode) AND (BCompanyCode = @BCompanyCode) AND (BTenantCode = @BTenantCode)),0) end SELECT @OrderNo = @MaxNo + 1 if @@error <> 0 begin rollback transaction select -1 return end /* Žęˆų–¾×“o˜^-3 ctpTradeTrnģ¬@*/ Insert into ctpTradeTrn (PracticeCode ,APrivateCode ,ACompanyCode ,ATenantCode ,ALoginId ,ActNo ,TradeCode ,OrderNo ,OrderSeq ,BPrivateCode ,BCompanyCode ,BTenantCode ,ItemType ,ItemCode ,ItemName ,ItemImage ,PaymentTerm ,ProcessStatus ,BusinessDateA ,BusinessDateB ,BusinessDateE ,BusinessDateT ,TransportFlag ,SellsDiscFlag ,COfficeAssetsFlag ,Qty ,StandardPrice ,MakesPrice ,StockPrice ,SellsPrice ,TransportPrice ,ConsumptionTax ,CCreditRate ,MakesAmnt ,StockAmnt ,SellsAmnt ,TaxAmnt ,TransportAmnt ,TransportTax ,CreditAmnt ,AAmnt ,BAmnt ,RemarksA ,RemarksB ,RemarksE ,AddDate ,UpdDate ,AddProc ,UpdProc ) Values (@PracticeCode ,@APrivateCode ,@ACompanyCode ,@ATenantCode ,@ALoginId ,@ActNo ,@TradeCode ,@OrderNo ,@OrderSeq ,@BPrivateCode ,@BCompanyCode ,@BTenantCode ,@ItemType ,@ItemCode ,@ItemName ,@ItemImage ,@PaymentTerm ,@ProcessStatus ,@BusinessDateA ,@BusinessDateB ,@BusinessDateE ,@BusinessDateT ,@TransportFlag ,@SellsDiscFlag ,@COfficeAssetsFlag ,@Qty ,@StandardPrice ,@MakesPrice ,@StockPrice ,@SellsPrice ,@TransportPrice ,@ConsumptionTax ,@CCreditRate ,@MakesAmnt ,@StockAmnt ,@SellsAmnt ,@TaxAmnt ,@TransportAmnt ,@TransportTax ,@CreditAmnt ,@AAmnt ,@BAmnt ,@RemarksA ,@RemarksB ,@RemarksE ,@AddDate ,@UpdDate ,@AddProc , @UpdProc ) if @@error <> 0 begin rollback transaction select -1 return end commit transaction select 0 return @ActNo GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE dbo.SXŠw¶ī•ńŽę“¾ @Šw¶”Ō† varchar(40) AS SELECT TSŠw¶M.* FROM TSŠw¶M WHERE TSŠw¶M.Šw¶”Ō† = @Šw¶”Ō† GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO