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