if not( exists (SELECT * FROM syscolumns WHERE OBJECT_NAME(id) = 'ctpPracticeM' AND name = N'MasterID'))
  ALTER TABLE ctpPracticeM ADD MasterID varchar(12) NULL
GO





if not( exists (SELECT * FROM syscolumns WHERE OBJECT_NAME(id) = 'ctpPracticeM' AND name = N'ManageFlag'))
BEGIN



CREATE TABLE [dbo].[ctpPracticeMNew] (
	[PracticeType] [varchar] (8) NULL ,
	[PracticeCode] [varchar] (8) NOT NULL ,
	[PracticeSeq] [int] NULL ,
	[PracticeName] [varchar] (40) NULL ,
	[UnusedFlag] [tinyint] NULL ,
	[TCloseFlag] [tinyint] NULL ,
	[DefaultFlag] [smallint] NULL ,
	[ManageFlag] [smallint] NULL ,
	[MasterPwd] [varchar] (8) NULL ,
	[Remarks] [varchar] (250) NULL ,
	[BgImage] [varchar] (100) NULL ,
	[StartDate] [datetime] NULL ,
	[EndDate] [datetime] NULL ,
	[NowDate] [datetime] NULL ,
	[Settlement] [smallint] NULL ,
	[ExCode] [varchar] (3) NULL ,
	[ExRate] [real] NULL ,
	[ExConsumptionTax] [money] NULL ,
	[ConsumptionTax] [money] NULL ,
	[ComputeFlag] [smallint] NULL ,
	[PCapitalAmount] [money] NULL ,
	[CCapitalAmount] [money] NULL ,
	[COfficeGuarantee] [money] NULL ,
	[COfficeRentalFee] [money] NULL ,
	[COfficeAssetsPrice] [money] NULL ,
	[HpOpeningCostH] [money] NULL ,
	[HpOpeningCostL] [money] NULL ,
	[HpRentalFeeH] [money] NULL ,
	[HpRentalFeeL] [money] NULL ,
	[HpManagerSalary] [money] NULL ,
	[HpClerkSalary] [money] NULL ,
	[HpIMaxSell] [smallint] NULL ,
	[HpIMaxBuy] [smallint] NULL ,
	[HpStockCheck] [smallint] NULL ,
	[HpStockAutoFlag] [smallint] NULL ,
	[HpVoteCheck] [smallint] NULL ,
	[HpVoteTitle1] [varchar] (50) NULL ,
	[HpVoteTitle2] [varchar] (50) NULL ,
	[HpVoteTitle3] [varchar] (50) NULL ,
	[HpVoteTitle4] [varchar] (50) NULL ,
	[HpVoteTitle5] [varchar] (50) NULL ,
	[PBankLoanLimit] [money] NULL ,
	[CBankLoanLimit] [money] NULL ,
	[CCreditFee] [money] NULL ,
	[CCreditRate] [money] NULL ,
	[CCreditLimit] [money] NULL ,
	[PCreditLimit] [money] NULL ,
	[XCreditActFee] [money] NULL ,
	[XSecActFee] [money] NULL ,
	[XSecRate] [real] NULL ,
	[XSecTax] [money] NULL ,
	[PDealerDiscAmnt] [money] NULL ,
	[PDealerDiscRate] [real] NULL ,
	[CDealerDiscAmnt] [money] NULL ,
	[CDealerDiscRate] [real] NULL ,
	[PMakerDiscAmnt] [money] NULL ,
	[PMakerDiscRate] [real] NULL ,
	[PMakerMakeRate] [real] NULL ,
	[PMakerCostRate] [real] NULL ,
	[PMakerSellRate] [real] NULL ,
	[CMakerDiscAmnt] [money] NULL ,
	[CMakerDiscRate] [real] NULL ,
	[CMakerMakeRate] [real] NULL ,
	[CMakerCostRate] [real] NULL ,
	[CMakerSellRate] [real] NULL ,
	[XAdFee] [money] NULL ,
	[XAdFee2] [money] NULL ,
	[SearchNGFlag] [smallint] NULL ,
	[POption01] [money] NULL ,
	[POption02] [money] NULL ,
	[POption03] [money] NULL ,
	[POption04] [money] NULL ,
	[POption05] [money] NULL ,
	[AddDate] [datetime] NULL ,
	[UpdDate] [datetime] NULL ,
	[AddProc] [varchar] (20) NULL ,
	[UpdProc] [varchar] (20) NULL 
)


INSERT INTO ctpPracticeMNew
                      (PracticeType, PracticeCode, PracticeSeq, PracticeName, UnusedFlag, 
                      TCloseFlag, DefaultFlag, MasterPwd, Remarks, StartDate, EndDate, NowDate, 
                      Settlement, ExCode, ExRate, ExConsumptionTax, ConsumptionTax, ComputeFlag, 
                      PCapitalAmount, CCapitalAmount, COfficeGuarantee, COfficeRentalFee, 
                      COfficeAssetsPrice, HpOpeningCostH, HpOpeningCostL, HpRentalFeeH, 
                      HpRentalFeeL, HpManagerSalary, HpClerkSalary, HpIMaxSell, HpIMaxBuy, 
                      HpStockCheck, HpStockAutoFlag, HpVoteCheck, HpVoteTitle1, HpVoteTitle2, 
                      HpVoteTitle3, HpVoteTitle4, HpVoteTitle5, PBankLoanLimit, CBankLoanLimit, 
                      CCreditFee, CCreditRate, CCreditLimit, PCreditLimit, XCreditActFee, 
                      XSecActFee, XSecRate, XSecTax, PDealerDiscAmnt, PDealerDiscRate, 
                      CDealerDiscAmnt, CDealerDiscRate, PMakerDiscAmnt, PMakerDiscRate, 
                      PMakerMakeRate, PMakerCostRate, PMakerSellRate, CMakerDiscAmnt, 
                      CMakerDiscRate, CMakerMakeRate, CMakerCostRate, CMakerSellRate, XAdFee,
                       XAdFee2, SearchNGFlag, POption01, POption02, POption03, POption04, 
                      POption05, AddDate, UpdDate, AddProc, UpdProc)
SELECT          PracticeType, PracticeCode, PracticeSeq, PracticeName, UnusedFlag, TCloseFlag,
                       DefaultFlag, MasterPwd, Remarks, StartDate, EndDate, NowDate, Settlement, 
                      ExCode, ExRate, ExConsumptionTax, ConsumptionTax, ComputeFlag, 
                      PCapitalAmount, CCapitalAmount, COfficeGuarantee, COfficeRentalFee, 
                      COfficeAssetsPrice, HpOpeningCostH, HpOpeningCostL, HpRentalFeeH, 
                      HpRentalFeeL, HpManagerSalary, HpClerkSalary, HpIMaxSell, HpIMaxBuy, 
                      HpStockCheck, HpStockAutoFlag, HpVoteCheck, HpVoteTitle1, HpVoteTitle2, 
                      HpVoteTitle3, HpVoteTitle4, HpVoteTitle5, PBankLoanLimit, CBankLoanLimit, 
                      CCreditFee, CCreditRate, CCreditLimit, PCreditLimit, XCreditActFee, 
                      XSecActFee, XSecRate, XSecTax, PDealerDiscAmnt, PDealerDiscRate, 
                      CDealerDiscAmnt, CDealerDiscRate, PMakerDiscAmnt, PMakerDiscRate, 
                      PMakerMakeRate, PMakerCostRate, PMakerSellRate, CMakerDiscAmnt, 
                      CMakerDiscRate, CMakerMakeRate, CMakerCostRate, CMakerSellRate, XAdFee,
                       XAdFee2, SearchNGFlag, POption01, POption02, POption03, POption04, 
                      POption05, AddDate, UpdDate, AddProc, UpdProc
FROM            ctpPracticeM

UPDATE ctpPracticeMNew SET ManageFlag = 0

drop table [dbo].[ctpPracticeM]



EXEC sp_rename 'ctpPracticeMNew', 'ctpPracticeM'



ALTER TABLE [dbo].[ctpPracticeM] WITH NOCHECK ADD 
	CONSTRAINT [PK_ctpPracticeM] PRIMARY KEY  CLUSTERED 
	(
		[PracticeCode]
	)  

ALTER TABLE [dbo].[ctpPracticeM] WITH NOCHECK ADD 
	CONSTRAINT [DF_ctpPracticeM_PracticeSeq] DEFAULT (0) FOR [PracticeSeq],
	CONSTRAINT [DF_ctpPracticeM_UnusedFlag] DEFAULT (0) FOR [UnusedFlag],
	CONSTRAINT [DF_ctpPracticeM_TCloseFlag] DEFAULT (0) FOR [TCloseFlag],
	CONSTRAINT [DF_ctpPracticeM_DefaultFlag] DEFAULT (0) FOR [DefaultFlag],
	CONSTRAINT [DF_ctpPracticeM_ManageFlag] DEFAULT (0) FOR [ManageFlag],
	CONSTRAINT [DF_ctpPracticeM_Settlement] DEFAULT (0) FOR [Settlement],
	CONSTRAINT [DF_ctpPracticeM_ExRate] DEFAULT (0) FOR [ExRate],
	CONSTRAINT [DF_ctpPracticeM_ExConsumptionTax] DEFAULT (0) FOR [ExConsumptionTax],
	CONSTRAINT [DF_ctpPracticeM_ConsumptionTax] DEFAULT (0) FOR [ConsumptionTax],
	CONSTRAINT [DF_ctpPracticeM_ComputeFlag] DEFAULT (0) FOR [ComputeFlag],
	CONSTRAINT [DF_ctpPracticeM_PCapitalAmount] DEFAULT (0) FOR [PCapitalAmount],
	CONSTRAINT [DF_ctpPracticeM_CCapitalAmount] DEFAULT (0) FOR [CCapitalAmount],
	CONSTRAINT [DF_ctpPracticeM_COfficeGuarantee] DEFAULT (0) FOR [COfficeGuarantee],
	CONSTRAINT [DF_ctpPracticeM_COfficeRentalFee] DEFAULT (0) FOR [COfficeRentalFee],
	CONSTRAINT [DF_ctpPracticeM_COfficeAssetsPrice] DEFAULT (0) FOR [COfficeAssetsPrice],
	CONSTRAINT [DF_ctpPracticeM_HpOpeningCostH] DEFAULT (0) FOR [HpOpeningCostH],
	CONSTRAINT [DF_ctpPracticeM_HpOpeningCostL] DEFAULT (0) FOR [HpOpeningCostL],
	CONSTRAINT [DF_ctpPracticeM_HpRentalFeeH] DEFAULT (0) FOR [HpRentalFeeH],
	CONSTRAINT [DF_ctpPracticeM_HpRentalFeeL] DEFAULT (0) FOR [HpRentalFeeL],
	CONSTRAINT [DF_ctpPracticeM_HpManagerSalary] DEFAULT (0) FOR [HpManagerSalary],
	CONSTRAINT [DF_ctpPracticeM_HpClerkSalary] DEFAULT (0) FOR [HpClerkSalary],
	CONSTRAINT [DF_ctpPracticeM_HpIMaxSell] DEFAULT (0) FOR [HpIMaxSell],
	CONSTRAINT [DF_ctpPracticeM_HpIMaxBuy] DEFAULT (0) FOR [HpIMaxBuy],
	CONSTRAINT [DF_ctpPracticeM_HpStockCheck] DEFAULT (0) FOR [HpStockCheck],
	CONSTRAINT [DF_ctpPracticeM_HpStockAutoFlag] DEFAULT (0) FOR [HpStockAutoFlag],
	CONSTRAINT [DF_ctpPracticeM_HpVoteCheck] DEFAULT (0) FOR [HpVoteCheck],
	CONSTRAINT [DF_ctpPracticeM_PBankLoanLimit] DEFAULT (0) FOR [PBankLoanLimit],
	CONSTRAINT [DF_ctpPracticeM_CBankLoanLimit] DEFAULT (0) FOR [CBankLoanLimit],
	CONSTRAINT [DF_ctpPracticeM_CCreditFee] DEFAULT (0) FOR [CCreditFee],
	CONSTRAINT [DF_ctpPracticeM_CCreditRate] DEFAULT (0) FOR [CCreditRate],
	CONSTRAINT [DF_ctpPracticeM_CCreditLimit] DEFAULT (0) FOR [CCreditLimit],
	CONSTRAINT [DF_ctpPracticeM_PCreditLimit] DEFAULT (0) FOR [PCreditLimit],
	CONSTRAINT [DF_ctpPracticeM_XCreditActFee] DEFAULT (0) FOR [XCreditActFee],
	CONSTRAINT [DF_ctpPracticeM_XSecActFee] DEFAULT (0) FOR [XSecActFee],
	CONSTRAINT [DF_ctpPracticeM_XSecRate] DEFAULT (0) FOR [XSecRate],
	CONSTRAINT [DF_ctpPracticeM_XSecTax] DEFAULT (0) FOR [XSecTax],
	CONSTRAINT [DF_ctpPracticeM_PDealerDiscAmnt] DEFAULT (0) FOR [PDealerDiscAmnt],
	CONSTRAINT [DF_ctpPracticeM_PDealerDiscRate] DEFAULT (0) FOR [PDealerDiscRate],
	CONSTRAINT [DF_ctpPracticeM_CDealerDiscAmnt] DEFAULT (0) FOR [CDealerDiscAmnt],
	CONSTRAINT [DF_ctpPracticeM_CDealerDiscRate] DEFAULT (0) FOR [CDealerDiscRate],
	CONSTRAINT [DF_ctpPracticeM_PMakerDiscAmnt] DEFAULT (0) FOR [PMakerDiscAmnt],
	CONSTRAINT [DF_ctpPracticeM_PMakerDiscRate] DEFAULT (0) FOR [PMakerDiscRate],
	CONSTRAINT [DF_ctpPracticeM_PMakerMakeRate] DEFAULT (0) FOR [PMakerMakeRate],
	CONSTRAINT [DF_ctpPracticeM_PMakerCostRate] DEFAULT (0) FOR [PMakerCostRate],
	CONSTRAINT [DF_ctpPracticeM_PMakerSellRate] DEFAULT (0) FOR [PMakerSellRate],
	CONSTRAINT [DF_ctpPracticeM_CMakerDiscAmnt] DEFAULT (0) FOR [CMakerDiscAmnt],
	CONSTRAINT [DF_ctpPracticeM_CMakerDiscRate] DEFAULT (0) FOR [CMakerDiscRate],
	CONSTRAINT [DF_ctpPracticeM_CMakerMakeRate] DEFAULT (0) FOR [CMakerMakeRate],
	CONSTRAINT [DF_ctpPracticeM_CMakerCostRate] DEFAULT (0) FOR [CMakerCostRate],
	CONSTRAINT [DF_ctpPracticeM_CMakerSellRate] DEFAULT (0) FOR [CMakerSellRate],
	CONSTRAINT [DF_ctpPracticeM_XAdFee] DEFAULT (0) FOR [XAdFee],
	CONSTRAINT [DF_ctpPracticeM_XAdFee2] DEFAULT (0) FOR [XAdFee2],
	CONSTRAINT [DF_ctpPracticeM_SearchNGFlag] DEFAULT (0) FOR [SearchNGFlag],
	CONSTRAINT [DF_ctpPracticeM_POption01] DEFAULT (0) FOR [POption01],
	CONSTRAINT [DF_ctpPracticeM_POption02] DEFAULT (0) FOR [POption02],
	CONSTRAINT [DF_ctpPracticeM_POption03] DEFAULT (0) FOR [POption03],
	CONSTRAINT [DF_ctpPracticeM_POption04] DEFAULT (0) FOR [POption04],
	CONSTRAINT [DF_ctpPracticeM_POption05] DEFAULT (0) FOR [POption05]

END

if not( exists (SELECT * FROM syscolumns WHERE OBJECT_NAME(id) = 'ctpTradeTrn' AND name = N'ItemName'))
BEGIN



CREATE TABLE [dbo].[ctpTradeTrnNew] (
	[PracticeCode] [varchar] (8) NOT NULL ,
	[APrivateCode] [varchar] (12) NOT NULL ,
	[ACompanyCode] [varchar] (12) NOT NULL ,
	[ATenantCode] [varchar] (12) NOT NULL ,
	[ALoginId] [varchar] (12) NULL ,
	[ActNo] [int] NULL ,
	[TradeCode] [varchar] (4) NOT NULL ,
	[OrderNo] [int] NOT NULL ,
	[OrderSeq] [smallint] NOT NULL ,
	[BPrivateCode] [varchar] (12) NULL ,
	[BCompanyCode] [varchar] (12) NULL ,
	[BTenantCode] [varchar] (12) NULL ,
	[ItemType] [varchar] (1) NULL ,
	[ItemCode] [varchar] (4) NULL ,
	[ItemName] [varchar] (40) NULL ,
	[ItemImage] [varchar] (100) NULL ,
	[PaymentTerm] [smallint] NULL ,
	[ProcessStatus] [varchar] (1) NULL ,
	[BusinessDateA] [datetime] NULL ,
	[BusinessDateB] [datetime] NULL ,
	[BusinessDateE] [datetime] NULL ,
	[BusinessDateT] [datetime] NULL ,
	[TransportFlag] [int] NULL ,
	[SellsDiscFlag] [int] NULL ,
	[COfficeAssetsFlag] [int] NULL ,
	[Qty] [int] NULL ,
	[StandardPrice] [money] NULL ,
	[MakesPrice] [money] NULL ,
	[StockPrice] [money] NULL ,
	[SellsPrice] [money] NULL ,
	[TransportPrice] [money] NULL ,
	[ConsumptionTax] [money] NULL ,
	[CCreditRate] [real] NULL ,
	[MakesAmnt] [money] NULL ,
	[StockAmnt] [money] NULL ,
	[SellsAmnt] [money] NULL ,
	[TaxAmnt] [money] NULL ,
	[TransportAmnt] [money] NULL ,
	[TransportTax] [money] NULL ,
	[CreditAmnt] [money] NULL ,
	[AAmnt] [money] NULL ,
	[BAmnt] [money] NULL ,
	[RemarksA] [varchar] (250) NULL ,
	[RemarksB] [varchar] (250) NULL ,
	[RemarksE] [varchar] (250) NULL ,
	[AddDate] [datetime] NOT NULL ,
	[UpdDate] [datetime] NULL ,
	[AddProc] [varchar] (20) NULL ,
	[UpdProc] [varchar] (20) NULL 
)



INSERT INTO ctpTradeTrnNew
                      (PracticeCode, APrivateCode, ACompanyCode, ATenantCode, ALoginId, ActNo, 
                      TradeCode, OrderNo, OrderSeq, BPrivateCode, BCompanyCode, BTenantCode, 
                      ItemType, ItemCode, 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)
SELECT          PracticeCode, APrivateCode, ACompanyCode, ATenantCode, ALoginId, ActNo, 
                      TradeCode, OrderNo, OrderSeq, BPrivateCode, BCompanyCode, BTenantCode, 
                      ItemType, ItemCode, 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
FROM            ctpTradeTrn


drop table [dbo].[ctpTradeTrn]


EXEC sp_rename 'ctpTradeTrnNew', 'ctpTradeTrn'


ALTER TABLE [dbo].[ctpTradeTrn] WITH NOCHECK ADD 
	CONSTRAINT [PK_ctpTradeTrn] PRIMARY KEY  CLUSTERED 
	(
		[PracticeCode],
		[APrivateCode],
		[ACompanyCode],
		[ATenantCode],
		[TradeCode],
		[OrderNo],
		[OrderSeq],
		[AddDate]
	)  


ALTER TABLE [dbo].[ctpTradeTrn] WITH NOCHECK ADD 
	CONSTRAINT [DF_ctpTradeTrn_APrivateCode] DEFAULT ('') FOR [APrivateCode],
	CONSTRAINT [DF_ctpTradeTrn_ACompanyCode] DEFAULT ('') FOR [ACompanyCode],
	CONSTRAINT [DF_ctpTradeTrn_ATenantCode] DEFAULT ('') FOR [ATenantCode],
	CONSTRAINT [DF_ctpTradeTrn_ActNo] DEFAULT (0) FOR [ActNo],
	CONSTRAINT [DF_ctpTradeTrn_OrderNo] DEFAULT (0) FOR [OrderNo],
	CONSTRAINT [DF_ctpTradeTrn_OrderSeq] DEFAULT (0) FOR [OrderSeq],
	CONSTRAINT [DF_ctpTradeTrn_BPrivateCode] DEFAULT ('') FOR [BPrivateCode],
	CONSTRAINT [DF_ctpTradeTrn_BCompanyCode] DEFAULT ('') FOR [BCompanyCode],
	CONSTRAINT [DF_ctpTradeTrn_BTenantCode] DEFAULT ('') FOR [BTenantCode],
	CONSTRAINT [DF_ctpTradeTrn_PaymentTerm] DEFAULT (0) FOR [PaymentTerm],
	CONSTRAINT [DF_ctpTradeTrn_TransportFlag] DEFAULT (0) FOR [TransportFlag],
	CONSTRAINT [DF_ctpTradeTrn_SellsDiscFlag] DEFAULT (0) FOR [SellsDiscFlag],
	CONSTRAINT [DF_ctpTradeTrn_COfficeAssetsFlag] DEFAULT (0) FOR [COfficeAssetsFlag],
	CONSTRAINT [DF_ctpTradeTrn_Qty] DEFAULT (0) FOR [Qty],
	CONSTRAINT [DF_ctpTradeTrn_StandardPrice] DEFAULT (0) FOR [StandardPrice],
	CONSTRAINT [DF_ctpTradeTrn_MakesPrice] DEFAULT (0) FOR [MakesPrice],
	CONSTRAINT [DF_ctpTradeTrn_StockPrice] DEFAULT (0) FOR [StockPrice],
	CONSTRAINT [DF_ctpTradeTrn_SellsPrice] DEFAULT (0) FOR [SellsPrice],
	CONSTRAINT [DF_ctpTradeTrn_TransportPrice] DEFAULT (0) FOR [TransportPrice],
	CONSTRAINT [DF_ctpTradeTrn_ConsumptionTax] DEFAULT (0) FOR [ConsumptionTax],
	CONSTRAINT [DF_ctpTradeTrn_CCreditRate] DEFAULT (0) FOR [CCreditRate],
	CONSTRAINT [DF_ctpTradeTrn_MakesAmnt] DEFAULT (0) FOR [MakesAmnt],
	CONSTRAINT [DF_ctpTradeTrn_StockAmnt] DEFAULT (0) FOR [StockAmnt],
	CONSTRAINT [DF_ctpTradeTrn_SellsAmnt] DEFAULT (0) FOR [SellsAmnt],
	CONSTRAINT [DF_ctpTradeTrn_TaxAmnt] DEFAULT (0) FOR [TaxAmnt],
	CONSTRAINT [DF_ctpTradeTrn_TransportAmnt] DEFAULT (0) FOR [TransportAmnt],
	CONSTRAINT [DF_ctpTradeTrn_TransportTax] DEFAULT (0) FOR [TransportTax],
	CONSTRAINT [DF_ctpTradeTrn_CreditAmnt] DEFAULT (0) FOR [CreditAmnt],
	CONSTRAINT [DF_ctpTradeTrn_AAmnt] DEFAULT (0) FOR [AAmnt],
	CONSTRAINT [DF_ctpTradeTrn_BAmnt] DEFAULT (0) FOR [BAmnt]


END