|
解決方案: |
解決辦法：
1、在升級前，請查看工資項目中是否包含T6 5.1或者T6 6.0產品中已經默認的工資項目，如果有這種工資項目，那么請先修改工資項目名稱，再升級。
2、升級后，出現工資變動時，工資項目不能錄入金額的問題，就要考慮，是否這個工資項目與產品默認的工資項目沖突。需要在WA_GZBItemTitle中修改工資項目名稱。
提供修復腳本：
--注意：計件工資自定義改成自己定義的名字
---第一部分
CREATE TABLE [dbo].[WA_GZDataTmp] (
[cGZGradeNum] [varchar] (3) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[cPsn_Num] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[cPsn_Name] [varchar] (40) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[cLkxcode] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
[cDept_Num] [varchar] (12) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[iPsnGrd_id] [int] NOT NULL ,
[iYear] [int] NOT NULL ,
[iMonth] [tinyint] NOT NULL ,
[iAccMonth] [tinyint] NULL ,
[bDCBZ] [bit] NULL ,
[bTFBZ] [bit] NULL ,
[cPreLkxcode] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
[cPreDeptNum] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
[F_1] [numeric](18, 2) NULL ,
[F_2] [numeric](18, 2) NULL ,
[F_3] [numeric](18, 2) NULL ,
[F_4] [numeric](18, 2) NULL ,
[F_5] [numeric](18, 2) NULL ,
[F_6] [numeric](18, 2) NULL ,
[F_7] [numeric](18, 2) NULL ,
[F_8] [numeric](18, 2) NULL ,
[F_9] [numeric](18, 2) NULL ,
[F_10] [numeric](18, 2) NULL ,
[F_11] [numeric](18, 2) NULL ,
[F_12] [numeric](18, 2) NULL ,
[F_13] [numeric](18, 2) NULL ,
[F_14] [numeric](18, 2) NULL ,
[F_15] [numeric](18, 2) NULL ,
[F_16] [numeric](18, 2) NULL ,
[F_17] [numeric](18, 2) NULL ,
[F_18] [numeric](18, 2) NULL ,
[F_19] [numeric](18, 2) NULL ,
[F_20] [varchar] (80) COLLATE Chinese_PRC_CI_AS NULL ,
[F_21] [numeric](18, 2) NULL ,
[F_22] [numeric](18, 2) NULL
) ON [PRIMARY]
insert into WA_GZDataTmp(cGZGradeNum , cPsn_Num ,cPsn_Name ,cLkxcode,cDept_Num ,iPsnGrd_id ,
iYear ,iMonth ,iAccMonth ,bDCBZ,bTFBZ ,cPreLkxcode ,cPreDeptNum ,F_1,F_2 ,F_3 , F_4 ,
F_5 ,F_6 ,F_7 , F_8 , F_9 ,F_10 ,F_11 ,F_12 ,F_13 ,F_14 ,F_15 ,F_16 ,F_17 ,F_18 ,
F_19 ,F_20 , F_21 , F_22 )
select cGZGradeNum , cPsn_Num ,cPsn_Name ,null,cDept_Num ,iPsnGrd_id ,
iYear ,iMonth ,iAccMonth ,bDCBZ,bTFBZ ,null ,cPreDeptNum ,F_1,F_2 ,F_3 , F_4 ,
F_5 ,F_6 ,null , F_8 , F_9 ,F_10 ,F_11 ,F_12 ,F_13 ,F_14 ,F_15 ,F_16 ,F_17 ,F_18 ,
F_19 ,F_20 , F_21 , F_22 from WA_GZData
drop table WA_GZData
exec sp_rename 'WA_GZDataTmp','WA_GZData'
EXEC sp_bindefault N'[dbo].[WA_GZData_bDCBZ_D]', N'[WA_GZData].[bDCBZ]'
GO
EXEC sp_bindefault N'[dbo].[WA_GZData_bTFBZ_D]', N'[WA_GZData].[bTFBZ]'
GO
EXEC sp_bindefault N'[dbo].[WA_GZData_F_1_D]', N'[WA_GZData].[F_1]'
GO
EXEC sp_bindefault N'[dbo].[WA_GZData_F_2_D]', N'[WA_GZData].[F_2]'
GO
EXEC sp_bindefault N'[dbo].[WA_GZData_F_3_D]', N'[WA_GZData].[F_3]'
GO
EXEC sp_bindefault N'[dbo].[WA_GZData_F_4_D]', N'[WA_GZData].[F_4]'
GO
EXEC sp_bindefault N'[dbo].[WA_GZData_F_5_D]', N'[WA_GZData].[F_5]'
GO
EXEC sp_bindefault N'[dbo].[WA_GZData_F_6_D]', N'[WA_GZData].[F_6]'
GO
EXEC sp_bindefault N'[dbo].[WA_GZData_iAccMonth_D]', N'[WA_GZData].[iAccMonth]'
GO
EXEC sp_bindefault N'[dbo].[WA_GZData_iMonth_D]', N'[WA_GZData].[iMonth]'
GO
EXEC sp_bindefault N'[dbo].[WA_GZData_iPsnGrd_id_D]', N'[WA_GZData].[iPsnGrd_id]'
GO
EXEC sp_bindefault N'[dbo].[WA_GZData_iYear_D]', N'[WA_GZData].[iYear]'
GO
CREATE TABLE [dbo].[WA_GZHZBTmp](
[cGZGradeNum] [varchar](3) COLLATE Chinese_PRC_CI_AS NOT NULL,
[cDept_Num] [varchar](12) COLLATE Chinese_PRC_CI_AS NOT NULL,
[iPsnGrd_id] [int] NOT NULL,
[iPsnAmt] [int] NULL,
[iYear] [int] NOT NULL,
[iMonth] [tinyint] NOT NULL,
[iAccMonth] [tinyint] NULL,
[FG_1] [flOAt] NULL,
[FG_2] [flOAt] NULL,
[FG_3] [flOAt] NULL,
[FG_4] [flOAt] NULL,
[FG_5] [flOAt] NULL,
[FG_6] [flOAt] NULL,
[FG_7] [flOAt] NULL,
[FG_8] [numeric](18, 2) NULL,
[FG_9] [numeric](18, 2) NULL,
[FG_10] [numeric](18, 2) NULL,
[FG_11] [numeric](18, 2) NULL,
[FG_12] [numeric](18, 2) NULL,
[FG_13] [numeric](18, 2) NULL,
[FG_14] [numeric](18, 2) NULL,
[FG_15] [numeric](18, 2) NULL,
[FG_16] [numeric](18, 2) NULL,
[FG_17] [numeric](18, 2) NULL,
[FG_18] [numeric](18, 2) NULL,
[FG_19] [numeric](18, 2) NULL,
[FG_20] [numeric](18, 2) NULL,
[FG_21] [varchar](30) COLLATE Chinese_PRC_CI_AS NULL,
[FG_22] [numeric](18, 2) NULL,
CONSTRAINT [aaaaaWA_GZHZB_PKTmp] PRIMARY KEY NONCLUSTERED
(
[cGZGradeNum] ASC,
[cDept_Num] ASC,
[iPsnGrd_id] ASC,
[iYear] ASC,
[iMonth] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
insert into WA_GZHZBTmp(cGZGradeNum,
cDept_Num,iPsnGrd_id,iPsnAmt,iYear,
iMonth,iAccMonth,FG_1,FG_2,FG_3,FG_4,FG_5,FG_6, FG_7
,FG_8,FG_9,FG_10,FG_11,FG_12,FG_13, FG_14,FG_15,FG_16,FG_17,FG_18,FG_19,FG_20, FG_21,FG_22)
select cGZGradeNum,
cDept_Num,iPsnGrd_id,iPsnAmt,iYear,
iMonth,iAccMonth,FG_1,FG_2,FG_3,FG_4,FG_5,FG_6, FG_7,FG_8,FG_9,FG_10,FG_11,FG_12,FG_13, FG_14,FG_15,FG_16,FG_17,FG_18,FG_19,FG_20, FG_21,FG_22 from WA_GZHZB
drop table WA_GZHZB
exec sp_rename 'WA_GZHZBTmp','WA_GZHZB'
EXEC sys.sp_bindefault @defname=N'[dbo].[WA_GZHZB_iPsnGrd_id_D]', @objname=N'[dbo].[WA_GZHZB].[iPsnGrd_id]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[WA_GZHZB_iPsnAmt_D]', @objname=N'[dbo].[WA_GZHZB].[iPsnAmt]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[WA_GZHZB_iYear_D]', @objname=N'[dbo].[WA_GZHZB].[iYear]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[WA_GZHZB_iMonth_D]', @objname=N'[dbo].[WA_GZHZB].[iMonth]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[WA_GZHZB_iAccMonth_D]', @objname=N'[dbo].[WA_GZHZB].[iAccMonth]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[WA_GZHZB_FG_1_D]', @objname=N'[dbo].[WA_GZHZB].[FG_1]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[WA_GZHZB_FG_2_D]', @objname=N'[dbo].[WA_GZHZB].[FG_2]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[WA_GZHZB_FG_3_D]', @objname=N'[dbo].[WA_GZHZB].[FG_3]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[WA_GZHZB_FG_4_D]', @objname=N'[dbo].[WA_GZHZB].[FG_4]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[WA_GZHZB_FG_5_D]', @objname=N'[dbo].[WA_GZHZB].[FG_5]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[WA_GZHZB_FG_6_D]', @objname=N'[dbo].[WA_GZHZB].[FG_6]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[WA_GZHZB_FG_7_D]', @objname=N'[dbo].[WA_GZHZB].[FG_7]' , @futureonly='futureonly'
GO
ALTER TABLE [dbo].[WA_GZHZB] WITH CHECK ADD FOREIGN KEY([cDept_Num])
REFERENCES [dbo].[Department] ([cDepCode])
GO
ALTER TABLE [dbo].[WA_GZHZB] WITH CHECK ADD FOREIGN KEY([iPsnGrd_id])
REFERENCES [dbo].[WA_grade] ([iPsnGrd_id])
-----第二部分
update WA_GZBItemTitle set cGZItemTitle='計件工資自定義',cExpression='計件工資自定義' where cGZItemTitle='計件工資'
update WA_GZtblset set cSetGZItemName='計件工資自定義' where cSetGZItemName='計件工資'
declare @iGZItem_id int
declare @cGZItemFormula varchar(100)
declare cur cursor for select iGZItem_id,cGZItemFormula from wa_formula where cGZItemFormula like '%計件工資%' and cGZItemFormula not like '%計件工資自定義%'
open cur
fetch next from cur into @iGZItem_id,@cGZItemFormula
while @@fetch_status=0
begin
update wa_formula set cGZItemFormula=replace(cGZItemFormula,'計件工資','計件工資自定義') where iGZItem_id=@iGZItem_id
fetch next from cur into @iGZItem_id,@cGZItemFormula
end
close cur
deallocate cur
if not exists(select *from syscolumns where id in(select id from sysobjects where name='wa_psn') and name='cLkxcode')
alter table wa_psn add [cLkxcode] [varchar] (30)
if not exists(select *from syscolumns where id in(select id from sysobjects where name='wa_psn') and name='cPreLkxcode')
alter table wa_psn add [cPreLkxcode] [varchar] (30)
|
