
USE @DBNAMEYEAR

IF NOT EXISTS (SELECT name FROM sysobjects WHERE name = N'WebSpecificExtraWork' AND TYPE = 'U')
BEGIN 

 BEGIN TRAN CreateWebSpecificExtraWork

   CREATE TABLE [dbo].[WebSpecificExtraWork](
	            [PageNo] [numeric](6, 0) IDENTITY(1,1) NOT NULL,
	            [Perno] [char](@pernoLenght) COLLATE Arabic_CI_AS NOT NULL,
	            [Period] [numeric](6, 0) NULL,
	            [LastChangeDate] [char](10) COLLATE Arabic_CI_AS NULL,
				[RecDate][char](5) COLLATE Arabic_CI_AS NULL,
				[BeginDate][char](5) COLLATE Arabic_CI_AS NULL,
				[EndDate][char](5) COLLATE Arabic_CI_AS NULL,
	            [IsTransfer] [bit] NOT NULL DEFAULT ((0)),
	            [ApprovedBy] [char](@pernoLenght) COLLATE Arabic_CI_AS NULL,
	            [Status] [int] NULL DEFAULT ((0)),
	            [Amount] [char](6) COLLATE Arabic_CI_AS NULL,
				[BeginPeriod] [char](5) COLLATE Arabic_CI_AS NULL,
				[EndPeriod] [char](5) COLLATE Arabic_CI_AS NULL,
				[Section] [char](30) COLLATE Arabic_CI_AS NULL


    ) ON [PRIMARY]  

 IF @@ERROR > 0 
   ROLLBACK TRAN CreateWebSpecificExtraWork	
 COMMIT

END

ELSE

BEGIN

 BEGIN TRAN AlterWebSpecificExtraWork

 IF NOT EXISTS (SELECT t.TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS AS c JOIN  INFORMATION_SCHEMA.TABLES AS t ON t.TABLE_NAME = c.TABLE_NAME WHERE COLUMNPROPERTY(OBJECT_ID(c.TABLE_NAME),c.COLUMN_NAME,'IsIdentity') = 1 AND t.TABLE_NAME = 'WebSpecificExtraWork')
 BEGIN
  SELECT * INTO newWebSpecificExtraWork FROM WebSpecificExtraWork

  DROP TABLE WebSpecificExtraWork

  CREATE TABLE [dbo].[WebSpecificExtraWork](
				[PageNo] [numeric](6, 0) IDENTITY(1,1) NOT NULL,
  				[Perno] [char](@pernoLenght) COLLATE Arabic_CI_AS NOT NULL,
  				[Period] [numeric](6, 0) NULL,
  				[LastChangeDate] [char](10) COLLATE Arabic_CI_AS NULL,
			    [RecDate][char](5) COLLATE Arabic_CI_AS NULL,
  				[IsTransfer] [bit] NOT NULL DEFAULT ((0)),
  				[ApprovedBy] [char](@pernoLenght) COLLATE Arabic_CI_AS NULL,
  				[Status] [int] NULL DEFAULT ((0)),
  				[Amount] [char](6) COLLATE Arabic_CI_AS NULL,
			    [BeginPeriod] [char](5) COLLATE Arabic_CI_AS NULL,
				[EndPeriod] [char](5) COLLATE Arabic_CI_AS NULL,
			    [Section] [char](30) COLLATE Arabic_CI_AS NULL
  ) ON [PRIMARY]  

IF NOT EXISTS (SELECT name FROM  syscolumns WHERE  id = OBJECT_ID('newWebSpecificExtraWork') AND name='status' )
BEGIN
EXECUTE (' INSERT INTO WebSpecificExtraWork
	       (Perno, PageNo, IsTransfer, Status, LastChangeDate, ApprovedBy, Amount, Period, RECDATE, Section )
           SELECT Perno, PageNo, IsTransfer, Status, LastChangeDate, ApprovedBy, Amount, Period, RECDATE, Section
	       FROM WebSpecificExtraWork ')
END
ELSE
BEGIN
EXECUTE(' INSERT INTO WebSpecificExtraWork
	      (Perno, PageNo, IsTransfer, Status, LastChangeDate, ApprovedBy, Amount, Period, RECDATE, Section )
          SELECT Perno, PageNo, IsTransfer, Status, LastChangeDate, ApprovedBy, Amount, Period, RECDATE, Section
	      FROM         newWebSpecificExtraWork ')
END

DROP TABLE newWebSpecificExtraWork
END

ALTER TABLE WebSpecificExtraWork ALTER COLUMN [ApprovedBy] char(@pernoLenght)

IF @@ERROR > 0 
  ROLLBACK TRAN AlterAskAbsence	
COMMIT

END


