常見問題分類
 

工廠版

 

電商版

 

通—用

常見問題

金狐軟件2016版本升級到2020版本SQL語句

發表時間:2020-06-09 17:41
IF NOT EXISTS (SELECT * FROM sys.sysobjects WHERE name='exchangetablecolumtype' AND type='P')
    EXEC('
Create   PROCEDURE exchangetablecolumtype(
@tableName VARCHAR(200),
@columname sysname,
@newtype sysname,
@default VARCHAR(20))
AS
BEGIN
    DECLARE @DFName VARCHAR(200)
    SELECT @DFName=name
    FROM sys.default_constraints
    WHERE Col_Name(parent_object_id, parent_column_id)[email protected] AND parent_object_id=Object_Id(@tableName)
    SET @DFName=IsNull(@DFName, '''')
    IF IsNull(@DFName, '''')<>''''
    BEGIN
        EXEC(''ALTER TABLE ''[email protected]+'' DROP CONSTRAINT ''[email protected])
        EXEC(''ALTER TABLE ''[email protected]+'' ALTER COLUMN ''[email protected]+'' ''[email protected])
    END
    ELSE
    BEGIN
        SET @DFName=''DF_''[email protected]+''_''[email protected]+''_''
                    +Cast(Convert(VARBINARY(4) , DateDiff(SS, ''1970-1-1 00:00:00'', GetUtcDate()),23) AS VARCHAR)
    END
    EXEC(''ALTER TABLE ''[email protected]+'' ADD CONSTRAINT ''[email protected]+'' DEFAULT ''[email protected]+'' FOR [''[email protected]+'']'')
END
'   )
--20161129產品規格字段加長
ALTER TABLE [dbo].[cDefine]
ALTER COLUMN [pyShort] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[cPdtCom]
ALTER COLUMN [MtlSpecName] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[cPdtCom]
ALTER COLUMN [PdtSplName] VARCHAR(170) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[dPdtOdrDtl]
ALTER COLUMN [PdtSpecName] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[dTradeDtl]
ALTER COLUMN [PdtSpecName] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[dPdtOdrDtlDtl]
ALTER COLUMN [PdtSpecName] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[dTradeDtlDtl]
ALTER COLUMN [PdtSpecName] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[dWorkShopDtl]
ALTER COLUMN [PdtSpecName] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[dWorkShopDtlPdt]
ALTER COLUMN [PdtSpecName] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[dWorkPieceDtl]
ALTER COLUMN [PdtSpecName] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[cPdtPiePrice]
ALTER COLUMN [PdtSplName] VARCHAR(170) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[dPdtStgDtl]
ALTER COLUMN [PdtSpecName] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[dPdtStore]
ALTER COLUMN [PdtSpecName] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[cPdtBomTree]
ALTER COLUMN [PdtSpecName] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[dPdtPartDtl]
ALTER COLUMN [PdtSpecName] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[dPdtPartStore]
ALTER COLUMN [PdtSpecName] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[dPdtStockDtl]
ALTER COLUMN [PdtSpecName] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[cPdtPie]
ALTER COLUMN [PdtSpecName] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[dPdtStoreBar]
ALTER COLUMN [PdtSpecName] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[dPdtStoreSale]
ALTER COLUMN [PdtSpecName] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[dPdtCostPrice]
ALTER COLUMN [PdtSpecName] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[cPdtClr]
ALTER COLUMN [PdtSplName] VARCHAR(170) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[cDefine]
ALTER COLUMN [SwiftCode] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[dPdtOdrDtl]
ALTER COLUMN [PdtClrName] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[dTradeDtl]
ALTER COLUMN [PdtClrName] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[dPdtOdrDtlDtl]
ALTER COLUMN [PdtClrName] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[dTradeDtlDtl]
ALTER COLUMN [PdtClrName] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[dWorkShopDtl]
ALTER COLUMN [PdtClrName] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[dWorkShopDtlPdt]
ALTER COLUMN [PdtClrName] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[dWorkPieceDtl]
ALTER COLUMN [PdtClrName] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[cPdtPiePrice]
ALTER COLUMN [PdtSplName] VARCHAR(170) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[dPdtStgDtl]
ALTER COLUMN [PdtClrName] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[dPdtStore]
ALTER COLUMN [PdtClrName] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[dPdtPartDtl]
ALTER COLUMN [PdtClrName] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[dPdtPartStore]
ALTER COLUMN [PdtClrName] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[dPdtStockDtl]
ALTER COLUMN [PdtClrName] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[dPdtStoreBar]
ALTER COLUMN [PdtClrName] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[dPdtStoreSale]
ALTER COLUMN [PdtClrName] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[dPdtCostPrice]
ALTER COLUMN [PdtClrName] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[dPdtOdrDtl]
ALTER COLUMN [CtrType] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[dPdtOdrDtl]
ALTER COLUMN [PdtClrWMName] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[dPdtOdrDtl]
ALTER COLUMN [PdtSpecNameOld] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[dTradeDtl]
ALTER COLUMN [CtrType] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[dTradeDtl]
ALTER COLUMN [PdtClrWMName] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[dTradeDtl]
ALTER COLUMN [PdtSpecNameOld] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[dWorkShopDtl]
ALTER COLUMN [CtrType] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[dWorkShopDtl]
ALTER COLUMN [PdtClrWMName] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
ALTER TABLE [dbo].[dWorkShopDtl]
ALTER COLUMN [PdtSpecNameOld] VARCHAR(150) COLLATE Chinese_PRC_CI_AS
GO
--20170110工價設置增加產品表模式
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='cPdtPiePrice' AND b.name='PdtMode')
    ALTER TABLE [dbo].[cPdtPiePrice] ADD [PdtMode] INT DEFAULT 0 NULL
--20170218過賬銷售折扣語句
IF NOT EXISTS (SELECT * FROM cAccountItem WHERE comMode=150 AND relCode='090')
    INSERT INTO cAccountItem(comMode, relCode, Name, PrelCode, FilialeID, creDpt)
    VALUES(150, '090', '銷售折扣與折讓', '', 90, '')
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='cAccountSet' AND b.name='DiscountSql')
    ALTER TABLE [dbo].[cAccountSet] ADD [DiscountSql] VARCHAR(8000) DEFAULT '' NULL
--EXEC sp_addextendedproperty 'MS_Description', N'用在銷售折扣語句', 'user', 'dbo', 'table', 'cAccountSet', 'column', 'DiscountSql'
ALTER TABLE [dbo].[cAccountSet]
ALTER COLUMN [TableID] VARCHAR(5000) COLLATE Chinese_PRC_CI_AS
--20170419自定義主鍵問題
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='dPdtStoreBar' AND b.name='SPINO')
    ALTER TABLE [dbo].[dPdtStoreBar] ADD [SPINO] VARCHAR(100) DEFAULT '' NULL
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='cDefine' AND b.name='IsCustom')
    ALTER TABLE [dbo].[cDefine] ADD [IsCustom] BIT DEFAULT 0 NULL
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='cPdtCom' AND b.name='IsCustom')
    ALTER TABLE [dbo].[cPdtCom] ADD [IsCustom] BIT DEFAULT 0 NULL
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='dWorkShopDtlPdt' AND b.name='IsCustom')
    ALTER TABLE [dbo].[dWorkShopDtlPdt] ADD [IsCustom] BIT DEFAULT 0 NULL
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='dPdtOdrDtlDtl' AND b.name='IsCustom')
    ALTER TABLE [dbo].[dPdtOdrDtlDtl] ADD [IsCustom] BIT DEFAULT 0 NULL
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='dTradeDtlDtl' AND b.name='IsCustom')
    ALTER TABLE [dbo].[dTradeDtlDtl] ADD [IsCustom] BIT DEFAULT 0 NULL
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='dWorkShopHisDtlPdt' AND b.name='IsCustom')
    ALTER TABLE [dbo].[dWorkShopHisDtlPdt] ADD [IsCustom] BIT DEFAULT 0 NULL
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='dPdtOdrHisDtlDtl' AND b.name='IsCustom')
    ALTER TABLE [dbo].[dPdtOdrHisDtlDtl] ADD [IsCustom] BIT DEFAULT 0 NULL
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='dTradeHisDtlDtl' AND b.name='IsCustom')
    ALTER TABLE [dbo].[dTradeHisDtlDtl] ADD [IsCustom] BIT DEFAULT 0 NULL
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='dWorkShopHisDtlPdt' AND b.name='IsCustom')
    ALTER TABLE [dbo].[dWorkShopHisDtlPdt] ADD [IsCustom] BIT DEFAULT 0 NULL
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='dPdtOdrHisDtlDtl' AND b.name='IsCustom')
    ALTER TABLE [dbo].[dPdtOdrHisDtlDtl] ADD [IsCustom] BIT DEFAULT 0 NULL
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='dTradeHisDtlDtl' AND b.name='IsCustom')
    ALTER TABLE [dbo].[dTradeHisDtlDtl] ADD [IsCustom] BIT DEFAULT 0 NULL
--20170521會計科目加借貸方向
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='dAccountRegDtl' AND b.name='Direction')
    ALTER TABLE [dbo].[dAccountRegDtl] ADD [Direction] VARCHAR(10) NULL
--現有憑證加借貸方向
UPDATE dAccountRegDtl
SET Direction=T.Direction
FROM(SELECT Name, Direction FROM cAccountItem WHERE IsNull(PrelCode, '')='')T
WHERE dAccountRegDtl.Name=T.Name
--20170525工價設置增加備注和增加客戶和供應商流水賬備注長度
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='cPdtPiePrice' AND b.name='memoText')
    ALTER TABLE [dbo].[cPdtPiePrice] ADD [memoText] VARCHAR(2000) NULL
ALTER TABLE [dbo].[cPdtPiePrice] ALTER COLUMN [memoText] VARCHAR(2000) NULL
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='dTradeFareInOutDtl' AND b.name='memoText')
    ALTER TABLE [dbo].[dTradeFareInOutDtl]
    ADD [memoText] VARCHAR(2000) COLLATE Chinese_PRC_CI_AS
ALTER TABLE [dbo].[dMtlFareInOutDtl]
ALTER COLUMN [memoText] VARCHAR(2000) COLLATE Chinese_PRC_CI_AS NULL
--20170725會計增加費用部門
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='dAccountRegDtl' AND b.name='DptName')
    ALTER TABLE [dbo].[dAccountRegDtl] ADD [DptName] VARCHAR(50) NULL
--EXEC sp_addextendedproperty 'MS_Description', N'費用部門', 'user', 'dbo', 'table', 'dAccountRegDtl', 'column', 'DptName'
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='dAccountRegDtl' AND b.name='Field1')
    ALTER TABLE [dbo].[dAccountRegDtl] ADD [Field1] VARCHAR(100) NULL
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='dAccountRegDtl' AND b.name='Field2')
    ALTER TABLE [dbo].[dAccountRegDtl] ADD [Field2] VARCHAR(100) NULL
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='dAccountRegDtl' AND b.name='Field3')
    ALTER TABLE [dbo].[dAccountRegDtl] ADD [Field3] VARCHAR(100) NULL
IF EXISTS (SELECT * FROM sFunc WHERE relCode=194)
    UPDATE sFunc SET FuncName='費用部門' WHERE relCode=194
--20171114訂單包裝件數改字段數字
EXEC exchangetablecolumtype 'dPdtOdrDtl', 'NumPackage', 'NUMERIC(18, 3)', '0'
EXEC exchangetablecolumtype 'dTradeDtl', 'NumPackage', 'NUMERIC(18, 3)', '0'
EXEC exchangetablecolumtype 'dWorkShopDtl', 'NumPackage', 'NUMERIC(18, 3)', '0'
--20170725取消IX_cPdtCom_Unique
IF EXISTS (SELECT name FROM sys.indexes idx WHERE idx.name='IX_cPdtCom_Unique')
    ALTER TABLE [dbo].[cPdtCom] DROP CONSTRAINT [IX_cPdtCom_Unique]
--20170620分支客戶加系列和折扣
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='cCtrSplrDtl' AND b.name='aDiscount')
    ALTER TABLE [dbo].[cCtrSplrDtl] ADD [aDiscount] NUMERIC(18, 3) DEFAULT 1 NULL
--20160329材料庫存加單價權限
IF NOT EXISTS (SELECT *
               FROM sFuncDtl
               WHERE comMode=100 AND FuncID=72 AND ControlName='actCostPrice')
    INSERT INTO sFuncDtl(comMode, EmployeID, FuncID, ControlName, ChnName, WinControlName, OrdID)
    VALUES(100, 30001, 72, 'actCostPrice', '可看單價', 'actCostPrice', 1)
--20180721工廠版產品清單拆分保存
ALTER TABLE [dbo].[cPdtCom]
ALTER COLUMN [Marked] VARCHAR(50) COLLATE Chinese_PRC_CI_AS
---------------------------------------------我是分割線---------------------------------------------
--   20190101工-增加BagName
IF NOT EXISTS (SELECT * FROM syscolumns WHERE id=Object_Id('dWorkShopDtl')AND name='BagName')
    ALTER TABLE dWorkShopDtl ADD BagName VARCHAR(300) NULL
IF NOT EXISTS (SELECT * FROM syscolumns WHERE id=Object_Id('dTradeDtlDtl')AND name='BagName')
    ALTER TABLE dTradeDtlDtl ADD BagName VARCHAR(300) NULL
IF NOT EXISTS (SELECT *
               FROM syscolumns
               WHERE id=Object_Id('dWorkShopDtlPdt')AND name='BagName')
    ALTER TABLE dWorkShopDtlPdt ADD BagName VARCHAR(300) NULL
IF NOT EXISTS (SELECT * FROM syscolumns WHERE id=Object_Id('dPdtStgHisDtl')AND name='BagName')
    ALTER TABLE dPdtStgHisDtl ADD BagName VARCHAR(300) NULL
IF NOT EXISTS (SELECT * FROM syscolumns WHERE id=Object_Id('dPdtOdrDtl')AND name='BagName')
    ALTER TABLE dPdtOdrDtl ADD BagName VARCHAR(300) NULL
IF NOT EXISTS (SELECT * FROM syscolumns WHERE id=Object_Id('cPdtCom')AND name='BagName')
    ALTER TABLE cPdtCom ADD BagName VARCHAR(300) NULL
IF NOT EXISTS (SELECT * FROM sys.sysobjects WHERE name='cPdtPartCom' AND type='U')
BEGIN
    CREATE TABLE [dbo].[cPdtPartCom] (
    [ID] [INT] NULL,
    [comMode] [INT] NOT NULL,
    [mRelCode] [INT] NOT NULL,
    [serID] [INT] NOT NULL,
    [UniqueName] [VARCHAR](300) NOT NULL,
    [FirCode] [INT] NULL,
    [SecCode] [INT] NULL,
    [NumUp] [NUMERIC](18, 3) NULL,
    [NumDown] [NUMERIC](18, 3) NULL,
    [NumLoss] [NUMERIC](18, 3) NULL,
    [MtlPrice] [NUMERIC](19, 4) NULL,
    [MtlCode] [VARCHAR](150) NULL,
    [MtlName] [VARCHAR](50) NULL,
    [MtlSpecName] [VARCHAR](150) NULL,
    [MtlUnit] [VARCHAR](6) NULL,
    [MtlClrName] [VARCHAR](150) NULL,
    [PaperName] [VARCHAR](7000) NULL,
    [PaperLength] [NUMERIC](18, 3) NULL,
    [PaperWidth] [NUMERIC](18, 3) NULL,
    [PaperHigh] [NUMERIC](18, 3) NULL,
    [MZWeight] [NUMERIC](18, 2) NULL,
    [JZWeight] [NUMERIC](18, 2) NULL,
    [MemoText] [VARCHAR](7000) NULL,
    [PdtUseCode] [VARCHAR](50) NULL,
    [PdtSplName] [VARCHAR](170) NULL,
    [DptStk] [INT] NULL,
    [BarClrCode] [VARCHAR](100) NULL,
    [BarPdtCode] [VARCHAR](30) NULL,
    [IsPdtClr] [BIT] NULL,
    [Marked] [VARCHAR](10) NULL,
    [IsBuy] [BIT] NULL,
    [Custom1] [VARCHAR](100) NULL,
    [Custom2] [VARCHAR](100) NULL,
    [Custom3] [VARCHAR](100) NULL,
    [ZHCustom1] [VARCHAR](100) NULL,
    [ZHCustom2] [VARCHAR](100) NULL,
    [ZHCustom3] [VARCHAR](100) NULL,
    [IsCustom] [BIT] NULL,
    [BagName] [VARCHAR](300) NULL,
    CONSTRAINT [PK_cPdtPartCom] PRIMARY KEY CLUSTERED
        (
    [comMode] ASC,
    [mRelCode] ASC,
    [serID] ASC)                WITH(PAD_INDEX=OFF, STATISTICS_NORECOMPUTE=OFF, IGNORE_DUP_KEY=OFF, ALLOW_ROW_LOCKS= ON,
                                     ALLOW_PAGE_LOCKS=ON)ON [PRIMARY])ON [PRIMARY]
    SET ANSI_PADDING OFF
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'包件信息(比如:型號+規格+顏色+自定義),這樣跟產品庫存同步好操作',
                                    @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',
                                    @level1name=N'cPdtPartCom', @level2type=N'COLUMN', @level2name=N'UniqueName'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'包件成本', @level0type=N'SCHEMA', @level0name=N'dbo',
                                    @level1type=N'TABLE', @level1name=N'cPdtPartCom', @level2type=N'COLUMN',
                                    @level2name=N'NumLoss'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'材料單位單價,用來算成本的', @level0type=N'SCHEMA',
                                    @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'cPdtPartCom',
                                    @level2type=N'COLUMN', @level2name=N'MtlPrice'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'型號', @level0type=N'SCHEMA', @level0name=N'dbo',
                                    @level1type=N'TABLE', @level1name=N'cPdtPartCom', @level2type=N'COLUMN',
                                    @level2name=N'MtlCode'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'系列', @level0type=N'SCHEMA', @level0name=N'dbo',
                                    @level1type=N'TABLE', @level1name=N'cPdtPartCom', @level2type=N'COLUMN',
                                    @level2name=N'MtlName'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'規格', @level0type=N'SCHEMA', @level0name=N'dbo',
                                    @level1type=N'TABLE', @level1name=N'cPdtPartCom', @level2type=N'COLUMN',
                                    @level2name=N'MtlSpecName'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'單位', @level0type=N'SCHEMA', @level0name=N'dbo',
                                    @level1type=N'TABLE', @level1name=N'cPdtPartCom', @level2type=N'COLUMN',
                                    @level2name=N'MtlUnit'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'顏色', @level0type=N'SCHEMA', @level0name=N'dbo',
                                    @level1type=N'TABLE', @level1name=N'cPdtPartCom', @level2type=N'COLUMN',
                                    @level2name=N'MtlClrName'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'產品類別', @level0type=N'SCHEMA', @level0name=N'dbo',
                                    @level1type=N'TABLE', @level1name=N'cPdtPartCom', @level2type=N'COLUMN',
                                    @level2name=N'PaperName'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'紙板長cm', @level0type=N'SCHEMA', @level0name=N'dbo',
                                    @level1type=N'TABLE', @level1name=N'cPdtPartCom', @level2type=N'COLUMN',
                                    @level2name=N'PaperLength'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'紙板寬cm', @level0type=N'SCHEMA', @level0name=N'dbo',
                                    @level1type=N'TABLE', @level1name=N'cPdtPartCom', @level2type=N'COLUMN',
                                    @level2name=N'PaperWidth'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'紙板寬cm', @level0type=N'SCHEMA', @level0name=N'dbo',
                                    @level1type=N'TABLE', @level1name=N'cPdtPartCom', @level2type=N'COLUMN',
                                    @level2name=N'PaperHigh'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'毛重', @level0type=N'SCHEMA', @level0name=N'dbo',
                                    @level1type=N'TABLE', @level1name=N'cPdtPartCom', @level2type=N'COLUMN',
                                    @level2name=N'MZWeight'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'凈重', @level0type=N'SCHEMA', @level0name=N'dbo',
                                    @level1type=N'TABLE', @level1name=N'cPdtPartCom', @level2type=N'COLUMN',
                                    @level2name=N'JZWeight'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'組合型號', @level0type=N'SCHEMA', @level0name=N'dbo',
                                    @level1type=N'TABLE', @level1name=N'cPdtPartCom', @level2type=N'COLUMN',
                                    @level2name=N'PdtUseCode'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'組合規格', @level0type=N'SCHEMA', @level0name=N'dbo',
                                    @level1type=N'TABLE', @level1name=N'cPdtPartCom', @level2type=N'COLUMN',
                                    @level2name=N'PdtSplName'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'組合顏色', @level0type=N'SCHEMA', @level0name=N'dbo',
                                    @level1type=N'TABLE', @level1name=N'cPdtPartCom', @level2type=N'COLUMN',
                                    @level2name=N'BarClrCode'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'產品條形碼(系列+名稱+型號+規格)', @level0type=N'SCHEMA',
                                    @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'cPdtPartCom',
                                    @level2type=N'COLUMN', @level2name=N'BarPdtCode'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'自定義字段1', @level0type=N'SCHEMA', @level0name=N'dbo',
                                    @level1type=N'TABLE', @level1name=N'cPdtPartCom', @level2type=N'COLUMN',
                                    @level2name=N'Custom1'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'自定義字段1', @level0type=N'SCHEMA', @level0name=N'dbo',
                                    @level1type=N'TABLE', @level1name=N'cPdtPartCom', @level2type=N'COLUMN',
                                    @level2name=N'Custom2'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'自定義字段1', @level0type=N'SCHEMA', @level0name=N'dbo',
                                    @level1type=N'TABLE', @level1name=N'cPdtPartCom', @level2type=N'COLUMN',
                                    @level2name=N'Custom3'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'組合自定義字段1', @level0type=N'SCHEMA', @level0name=N'dbo',
                                    @level1type=N'TABLE', @level1name=N'cPdtPartCom', @level2type=N'COLUMN',
                                    @level2name=N'ZHCustom1'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'組合自定義字段2', @level0type=N'SCHEMA', @level0name=N'dbo',
                                    @level1type=N'TABLE', @level1name=N'cPdtPartCom', @level2type=N'COLUMN',
                                    @level2name=N'ZHCustom2'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'組合自定義字段3', @level0type=N'SCHEMA', @level0name=N'dbo',
                                    @level1type=N'TABLE', @level1name=N'cPdtPartCom', @level2type=N'COLUMN',
                                    @level2name=N'ZHCustom3'
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'取消皮號,只保留型號+規格+顏色', @level0type=N'SCHEMA',
                                    @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'cPdtPartCom',
                                    @level2type=N'COLUMN', @level2name=N'BagName'
    ALTER TABLE [dbo].[cPdtPartCom]
    ADD CONSTRAINT [DF_cPdtPartCom_UniqueName] DEFAULT('')FOR [UniqueName]
    ALTER TABLE [dbo].[cPdtPartCom]
    ADD CONSTRAINT [DF_cPdtPartCom_FirCode] DEFAULT(0)FOR [FirCode]
    ALTER TABLE [dbo].[cPdtPartCom]
    ADD CONSTRAINT [DF_cPdtPartCom_SecCode] DEFAULT(0)FOR [SecCode]
    ALTER TABLE [dbo].[cPdtPartCom]
    ADD CONSTRAINT [DF_cPdtPartCom_Number] DEFAULT(1)FOR [NumUp]
    ALTER TABLE [dbo].[cPdtPartCom]
    ADD CONSTRAINT [DF__cPdtPartCom__NumPerc__31832429] DEFAULT(1)FOR [NumDown]
    ALTER TABLE [dbo].[cPdtPartCom]
    ADD CONSTRAINT [DF_cPdtPartCom_Number1] DEFAULT(0)FOR [NumLoss]
    ALTER TABLE [dbo].[cPdtPartCom]
    ADD CONSTRAINT [DF_cPdtPartCom_MtlPrice] DEFAULT(0)FOR [MtlPrice]
    ALTER TABLE [dbo].[cPdtPartCom]
    ADD CONSTRAINT [DF__cPdtPartCom__MtlName__4E36D375] DEFAULT('')FOR [MtlName]
    ALTER TABLE [dbo].[cPdtPartCom]
    ADD CONSTRAINT [DF__cPdtPartCom__MtlSpec__4A664291] DEFAULT('')FOR [MtlSpecName]
    ALTER TABLE [dbo].[cPdtPartCom]
    ADD CONSTRAINT [DF__cPdtPartCom__MtlClrN__4F2AF7AE] DEFAULT('')FOR [MtlClrName]
    ALTER TABLE [dbo].[cPdtPartCom]
    ADD CONSTRAINT [DF__cPdtPartCom__PaperNa__4C4E8B03] DEFAULT('')FOR [PaperName]
    ALTER TABLE [dbo].[cPdtPartCom]
    ADD CONSTRAINT [DF_cPdtPartCom_PaperLength] DEFAULT(0)FOR [PaperLength]
    ALTER TABLE [dbo].[cPdtPartCom]
    ADD CONSTRAINT [DF_cPdtPartCom_PaperWidth] DEFAULT(0)FOR [PaperWidth]
    ALTER TABLE [dbo].[cPdtPartCom]
    ADD CONSTRAINT [DF_cPdtPartCom_PaperHigh] DEFAULT(0)FOR [PaperHigh]
    ALTER TABLE [dbo].[cPdtPartCom]
    ADD CONSTRAINT [DF_cPdtPartCom_MZWeight] DEFAULT(0)FOR [MZWeight]
    ALTER TABLE [dbo].[cPdtPartCom]
    ADD CONSTRAINT [DF_cPdtPartCom_JZWeight] DEFAULT(0)FOR [JZWeight]
    ALTER TABLE [dbo].[cPdtPartCom]
    ADD CONSTRAINT [DF__cPdtPartCom__MemoTex__4B5A66CA] DEFAULT('')FOR [MemoText]
    ALTER TABLE [dbo].[cPdtPartCom]
    ADD CONSTRAINT [DF__cPdtPartCom__DptStk__6FECCCFB] DEFAULT(0)FOR [DptStk]
    ALTER TABLE [dbo].[cPdtPartCom]
    ADD CONSTRAINT [DF__cPdtPartCom__BarClrC__6C7141D2] DEFAULT('')FOR [BarClrCode]
    ALTER TABLE [dbo].[cPdtPartCom]
    ADD CONSTRAINT [DF__cPdtPartCom__BarPdtC__6D65660B] DEFAULT('')FOR [BarPdtCode]
    ALTER TABLE [dbo].[cPdtPartCom]
    ADD CONSTRAINT [DF__cPdtPartCom__IsPdtCl__33C2D45B] DEFAULT(0)FOR [IsPdtClr]
    ALTER TABLE [dbo].[cPdtPartCom]
    ADD CONSTRAINT [DF__cPdtPartCom__Marked__5B5C8FC0] DEFAULT('')FOR [Marked]
    ALTER TABLE [dbo].[cPdtPartCom]
    ADD CONSTRAINT [DF__cPdtPartCom__Custom1__74FD3189] DEFAULT('')FOR [Custom1]
    ALTER TABLE [dbo].[cPdtPartCom]
    ADD CONSTRAINT [DF__cPdtPartCom__Custom2__75F155C2] DEFAULT('')FOR [Custom2]
    ALTER TABLE [dbo].[cPdtPartCom]
    ADD CONSTRAINT [DF__cPdtPartCom__Custom3__76E579FB] DEFAULT('')FOR [Custom3]
    ALTER TABLE [dbo].[cPdtPartCom]
    ADD CONSTRAINT [DF__cPdtPartCom__ZHCusto__32063FC9] DEFAULT('')FOR [ZHCustom1]
    ALTER TABLE [dbo].[cPdtPartCom]
    ADD CONSTRAINT [DF__cPdtPartCom__ZHCusto__32FA6402] DEFAULT('')FOR [ZHCustom2]
    ALTER TABLE [dbo].[cPdtPartCom]
    ADD CONSTRAINT [DF__cPdtPartCom__ZHCusto__33EE883B] DEFAULT('')FOR [ZHCustom3]
    ALTER TABLE [dbo].[cPdtPartCom]
    ADD CONSTRAINT [DF__cPdtPartCom__IsCusto__54264DA3] DEFAULT(0)FOR [IsCustom]
    ALTER TABLE [dbo].[cPdtPartCom]
    ADD CONSTRAINT [DF__cPdtPartCom__BagName__0770E77F] DEFAULT('')FOR [BagName]
END
ELSE IF NOT EXISTS (SELECT * FROM syscolumns WHERE id=Object_Id('cPdtPartCom')AND name='BagName')
    ALTER TABLE cPdtPartCom ADD BagName VARCHAR(300) NULL
---------------------------------------------我是分割線---------------------------------------------
IF NOT EXISTS (SELECT * FROM syscolumns WHERE id=Object_Id('dPdtOdrDtlDtl')AND name='BagName')
    ALTER TABLE dPdtOdrDtlDtl ADD BagName VARCHAR(300) NULL
IF NOT EXISTS (SELECT * FROM syscolumns WHERE id=Object_Id('dTradeDtl')AND name='BagName')
    ALTER TABLE dTradeDtl ADD BagName VARCHAR(300) NULL
IF NOT EXISTS (SELECT * FROM syscolumns WHERE id=Object_Id('dWorkShopDtl')AND name='BagName')
    ALTER TABLE dWorkShopDtl ADD BagName VARCHAR(300) NULL
IF NOT EXISTS (SELECT * FROM syscolumns WHERE id=Object_Id('dPdtStore')AND name='BagName')
    ALTER TABLE dPdtStore ADD BagName VARCHAR(300) NULL
IF NOT EXISTS (SELECT * FROM syscolumns WHERE id=Object_Id('dPdtStgDtl')AND name='BagName')
    ALTER TABLE dPdtStgDtl ADD BagName VARCHAR(300) NULL
IF NOT EXISTS (SELECT * FROM syscolumns WHERE id=Object_Id('dPdtStoreSale')AND name='BagName')
    ALTER TABLE dPdtStoreSale ADD BagName VARCHAR(300) NULL
--拆分保存過程 SQL2000
IF @@version LIKE '%2000%'
BEGIN
    IF NOT EXISTS (SELECT * FROM sys.sysobjects WHERE name='PdtComToPdtZH' AND type='P')
        EXEC('
CREATE PROCEDURE PdtComToPdtZH(@sRelCode INT)
AS
    DECLARE @NumCount INT, @sPdtRelCode INT, @sTogetherRelCode INT, @tReturn VARCHAR(8000)
    BEGIN
        --銷售產品
        SELECT comMode=110, RelCode=Identity(INT, 1, 1), creDpt=Max(NumLoss),
               UniqueName=''組合~'' + PdtUseCode + ''~'' + IsNull(PdtSplName, '''') + ''~'' + BarClrCode,
               PdtSeriesName=Max(MtlName), PdtKind=Max(PaperName), PdtUseCode, PdtSplName, BarClrCode,
               nSalePrice=Max(PaperHigh), MakeStore=Max(DptStk), MakeDpt=Max(SecCode),
               BarPdtCode=Max(Marked), CBM=Max(JZWeight), PdtUnit=Max(BagName), ZHCustom1,
               ZHCustom2=Max(ZHCustom2), ZHCustom3=Max(ZHCustom3)
        INTO #PdtComTable
        FROM cPdtCom AS C
        WHERE C.comMode=150
              AND NOT EXISTS (SELECT ID
                              FROM cDefine AS B
                              WHERE B.comMode=110 AND C.PdtUseCode=B.userCode
                                    AND IsNull(C.PdtSplName, '''')=IsNull(B.pyShort, '''')
                                    AND C.BarClrCode=B.SwiftCode)
        GROUP BY PdtUseCode, PdtSplName, BarClrCode, ZHCustom1
        SELECT @sPdtRelCode=IsNull(CurID, 0) + 1 FROM sFunc WHERE relCode=29
        SET @sPdtRelCode=IsNull(@sPdtRelCode, 1)
        INSERT INTO cDefine(comMode, relCode, creDpt, UniqueName, ACNO, pdcArea, userCode, pyShort,
                            SwiftCode, nSalePrice, CBM, MakeStore, makeDpt, ZJPdtCode, PdtSort, preGet,
                            BankAddress, IsSale, UnitName, Custom1, Custom2, Custom3)
        SELECT comMode, RelCode=290000 + @sPdtRelCode + RelCode, creDpt, UniqueName, PdtSeriesName,
               PdtKind, PdtUseCode, IsNull(PdtSplName, ''''), BarClrCode, nSalePrice, CBM, MakeStore,
               MakeDpt, BarPdtCode, ''組合'', 2, ''常規銷售'', 1, PdtUnit, ZHCustom1, ZHCustom2, ZHCustom3
        FROM #PdtComTable
        SELECT @NumCount=Count(*)FROM #PdtComTable
        SET @NumCount=IsNull(@NumCount, 0)
        UPDATE sFunc SET CurID=CurID + @NumCount + 1 WHERE relCode=29
        DROP TABLE #PdtComTable
        --包件
        SELECT comMode=101, RelCode=Identity(INT, 1, 1), creDpt=Max(NumLoss),
               UniqueName=''常規~'' + MtlCode + ''~'' + IsNull(MtlSpecName, '''') + ''~'' + MtlClrName,
               PdtSeriesName=Max(MtlName), PdtKind=Max(PaperName), PdtCode=MtlCode,
               PdtSpecName=IsNull(MtlSpecName, ''''), PdtClrName=MtlClrName, MakeStore=Max(DptStk),
               MakeDpt=Max(SecCode), NumPackage=Max(NumDown), PackageType=Max(PaperWidth),
               CBM=Max(PaperLength), MZWeight=Max(MZWeight), nSalePrice=Max(MtlPrice),
               BarPdtCode=Max(BarPdtCode), IsPdtClr=Max(CASE WHEN IsPdtClr=1 THEN 1 ELSE 0 END),
               IsCustom=Max(CASE WHEN IsCustom=1 THEN 1 ELSE 0 END), Custom1, Custom2=Max(Custom2),
               Custom3=Max(Custom3)
        INTO #PdtBagTable
        FROM cPdtCom AS C
        WHERE comMode=150
              AND NOT EXISTS (SELECT ID
                              FROM cDefine AS B
                              WHERE B.comMode=101 AND C.MtlCode=B.userCode
                                    AND IsNull(C.MtlSpecName, '''')=IsNull(B.pyShort, '''')
                                    AND C.MtlClrName=B.SwiftCode
                                    AND IsNull(B.Custom1, '''')=IsNull(C.Custom1, ''''))
        GROUP BY MtlCode, MtlSpecName, MtlClrName, C.Custom1
        SET @sPdtRelCode=0
        SELECT @sPdtRelCode=IsNull(CurID, 0) + 1 FROM sFunc WHERE relCode=7
        SET @sPdtRelCode=IsNull(@sPdtRelCode, 1)
        INSERT INTO cDefine(comMode, relCode, creDpt, UniqueName, ACNO, pdcArea, userCode, pyShort,
                            SwiftCode, MakeStore, makeDpt, PdtSort, BagNum, preGet, CBM, MZWeight,
                            nSalePrice, BankAddress, IsPdtClr, IsCustom, Alias, Custom1, Custom2,
                            Custom3)
        SELECT comMode, RelCode=70000 + @sPdtRelCode + RelCode, creDpt, UniqueName, PdtSeriesName,
               PdtKind, PdtCode, PdtSpecName, PdtClrName, MakeStore, MakeDpt, ''常規'', NumPackage,
               PackageType, CBM, MZWeight, nSalePrice, ''常規銷售'', IsPdtClr, IsCustom, BarPdtCode, Custom1,
               Custom2, Custom3
        FROM #PdtBagTable
        SELECT @NumCount=Count(*)FROM #PdtBagTable
        SET @NumCount=IsNull(@NumCount, 0)
        UPDATE sFunc SET CurID=CurID + @NumCount + 1 WHERE relCode=7
        DROP TABLE #PdtBagTable
        UPDATE cPdtCom
        SET mRelCode=T.relCode
        FROM(SELECT relCode, userCode, pyShort, SwiftCode, Custom1 FROM cDefine WHERE comMode=110) AS T
        WHERE PdtUseCode=T.userCode AND IsNull(cPdtCom.PdtSplName, '''')=IsNull(T.pyShort, '''')
              AND cPdtCom.BarClrCode=T.SwiftCode AND IsNull(cPdtCom.ZHCustom1, '''')=IsNull(T.Custom1, '''')
              AND comMode=150
        --銷售組合信息的體積和毛重是否來自產品清單(自動根據包件體積算組合體積)
        IF EXISTS (SELECT relCode FROM cCtrSplr WHERE comMode=120 AND dbo.f_getstr(CtryName, 5, ''|'')=''1'')
        BEGIN
            UPDATE cDefine
            SET CBM=CASE WHEN T.CBM>0 THEN T.CBM ELSE cDefine.CBM END,
                MZWeight=CASE WHEN T.MZWeight>0 THEN T.MZWeight ELSE cDefine.MZWeight END
            FROM(SELECT mRelCode,
                        CBM=Sum(
                            CASE WHEN(PaperWidth=1) THEN
                            (CASE WHEN(NumDown<>0) THEN (NumUp / NumDown)ELSE 0 END)
                            ELSE NumUp * NumDown END * PaperLength),
                        MZWeight=Sum(
                                 CASE WHEN(PaperWidth=1) THEN
                                 (CASE WHEN(NumDown<>0) THEN (NumUp / NumDown)ELSE 0 END)
                                 ELSE NumUp * NumDown END * MZWeight)
                 FROM cPdtCom
                 WHERE comMode=150
                 GROUP BY mRelCode) AS T
            WHERE cDefine.relCode=T.mRelCode AND cDefine.comMode=110 AND (T.CBM>0 OR T.MZWeight>0)
        END
        UPDATE cDefine
        SET UniqueName=Cast(comMode AS VARCHAR(10)) + ''~'' + PdtSort + ''~'' + userCode + ''~''
                       + IsNull(pyShort, '''') + ''~'' + SwiftCode
                       + CASE WHEN IsCustom=1 THEN ''~'' + Custom1 ELSE '''' END
        WHERE comMode IN (101, 110)
        UPDATE cPdtCom
        SET UniqueName=''常規~'' + MtlCode + ''~'' + IsNull(MtlSpecName, '''') + ''~'' + MtlClrName
                       + CASE WHEN IsCustom=1 THEN ''~'' + Custom1 ELSE '''' END
        WHERE comMode=150
        --更新包件清單的編碼   
        UPDATE cPdtCom
        SET FirCode=T.relCode, PaperName=T.pdcArea, NumLoss=T.creDpt, DptStk=T.MakeStore,
            SecCode=T.makeDpt, MemoText=T.ZJPdtCode, IsPdtClr=T.IsPdtClr, IsCustom=T.IsCustom
        FROM(SELECT relCode, pdcArea, creDpt, MakeStore, makeDpt, ZJPdtCode,
                    uniquename=Replace(UniqueName, ''101~'', ''''), IsPdtClr, IsCustom
             FROM cDefine
             WHERE comMode=101) AS T
        WHERE cPdtCom.comMode=150 AND cPdtCom.UniqueName=T.uniquename
        --更新序號
        --DECLARE @minmrelcode INT
        --DECLARE @maxmrelcode INT
        --SELECT @minmrelcode=Min(relCode), @maxmrelcode=Max(relCode)
        --FROM cDefine
        --WHERE comMode=110
        --SET @minmrelcode=IsNull(@minmrelcode, 0)
        --SET @maxmrelcode=IsNull(@maxmrelcode, 0)
        --WHILE(@minmrelcode<[email protected])
        --BEGIN
        --    UPDATE cPdtCom
        --    SET ID=T.id
        --    FROM(SELECT Rank() OVER (ORDER BY FirCode) AS id, FirCode
        --         FROM cPdtCom
        --         WHERE comMode=150 AND [email protected]) AS T
        --    WHERE [email protected] AND cPdtCom.FirCode=T.FirCode
        --    SET @[email protected] + 1
        --END
        ----插入系列
        --INSERT INTO cDefine(comMode, relCode, creDpt, ChkInMan, AthrMan, Name, Picture, UniqueName)
        --SELECT 170,
        --       (310000 + Rank() OVER (ORDER BY T.MtlName ASC)
        --        +   (SELECT CurID FROM sFunc WHERE relCode=31)), 90002, '''', '''', T.MtlName, '''',
        --       ''170~'' + T.MtlName
        --FROM(SELECT MtlName
        --     FROM cPdtCom
        --     WHERE comMode=150 AND IsNull(MtlName, '''')<>''''
        --     GROUP BY MtlName) AS T
        --WHERE NOT EXISTS (SELECT * FROM cDefine AS CD WHERE CD.comMode=170 AND CD.Name=T.MtlName)
        UPDATE sFunc
        SET CurID=(SELECT Max(relCode)FROM cDefine WHERE comMode=170) - 310000
        WHERE sFunc.relCode=31
        ----插入顏色信息
        --INSERT INTO cDefine(comMode, relCode, creDpt, ChkInMan, AthrMan, Name, Picture, UniqueName)
        --SELECT 109,
        --       (150002 + Rank() OVER (ORDER BY T.BarClrCode ASC)
        --        +   (SELECT CurID FROM sFunc WHERE relCode=15)), 90002, '''', '''', T.BarClrCode, '''',
        --       ''109~'' + T.BarClrCode
        --FROM(SELECT BarClrCode
        --     FROM cPdtCom
        --     WHERE comMode=150 AND IsNull(BarClrCode, '')<>''
        --     GROUP BY BarClrCode) AS T
        --WHERE NOT EXISTS (SELECT * FROM cDefine AS CD WHERE CD.comMode=109 AND CD.Name=T.BarClrCode)
        --UPDATE sFunc
        --SET CurID=(SELECT Max(relCode)FROM cDefine WHERE comMode=109) - 150000
        --WHERE sFunc.relCode=15
        --插入類別
        --      DELETE cdefine WHERE comMode=160
        --      UPDATE sFunc
        --      SET CurID=0
        --      WHERE FuncName=''產品類別''
        --INSERT INTO cDefine(comMode, relCode, creDpt, ChkInMan, AthrMan, Name, Picture, UniqueName)
        --SELECT 160,
        --       (160000 + Rank() OVER (ORDER BY T.PaperName ASC)
        --        +   (SELECT CurID FROM sFunc WHERE FuncName=''產品類別'')), 90002, '''', '''', T.PaperName, '''',
        --       ''160~'' + T.PaperName
        --FROM(SELECT PaperName
        --     FROM cPdtCom
        --     WHERE IsNull(PaperName, '')<>'' AND comMode=150
        --     GROUP BY PaperName) AS T
        --WHERE NOT EXISTS (SELECT * FROM cDefine AS CD WHERE CD.comMode=160 AND CD.Name=T.PaperName)
        UPDATE sFunc
        SET CurID=(SELECT Max(relCode)FROM cDefine WHERE comMode=160) - 160000
        WHERE FuncName=''產品類別''
    END')
    ELSE
        EXEC('
ALTER PROCEDURE PdtComToPdtZH(@sRelCode INT)
AS
    DECLARE @NumCount INT, @sPdtRelCode INT, @sTogetherRelCode INT, @tReturn VARCHAR(8000)
    BEGIN
        --銷售產品
        SELECT comMode=110, RelCode=Identity(INT, 1, 1), creDpt=Max(NumLoss),
               UniqueName=''組合~'' + PdtUseCode + ''~'' + IsNull(PdtSplName, '''') + ''~'' + BarClrCode,
               PdtSeriesName=Max(MtlName), PdtKind=Max(PaperName), PdtUseCode, PdtSplName, BarClrCode,
               nSalePrice=Max(PaperHigh), MakeStore=Max(DptStk), MakeDpt=Max(SecCode),
               BarPdtCode=Max(Marked), CBM=Max(JZWeight), PdtUnit=Max(BagName), ZHCustom1,
               ZHCustom2=Max(ZHCustom2), ZHCustom3=Max(ZHCustom3)
        INTO #PdtComTable
        FROM cPdtCom AS C
        WHERE C.comMode=150
              AND NOT EXISTS (SELECT ID
                              FROM cDefine AS B
                              WHERE B.comMode=110 AND C.PdtUseCode=B.userCode
                                    AND IsNull(C.PdtSplName, '''')=IsNull(B.pyShort, '''')
                                    AND C.BarClrCode=B.SwiftCode)
        GROUP BY PdtUseCode, PdtSplName, BarClrCode, ZHCustom1
        SELECT @sPdtRelCode=IsNull(CurID, 0) + 1 FROM sFunc WHERE relCode=29
        SET @sPdtRelCode=IsNull(@sPdtRelCode, 1)
        INSERT INTO cDefine(comMode, relCode, creDpt, UniqueName, ACNO, pdcArea, userCode, pyShort,
                            SwiftCode, nSalePrice, CBM, MakeStore, makeDpt, ZJPdtCode, PdtSort, preGet,
                            BankAddress, IsSale, UnitName, Custom1, Custom2, Custom3)
        SELECT comMode, RelCode=290000 + @sPdtRelCode + RelCode, creDpt, UniqueName, PdtSeriesName,
               PdtKind, PdtUseCode, IsNull(PdtSplName, ''''), BarClrCode, nSalePrice, CBM, MakeStore,
               MakeDpt, BarPdtCode, ''組合'', 2, ''常規銷售'', 1, PdtUnit, ZHCustom1, ZHCustom2, ZHCustom3
        FROM #PdtComTable
        SELECT @NumCount=Count(*)FROM #PdtComTable
        SET @NumCount=IsNull(@NumCount, 0)
        UPDATE sFunc SET CurID=CurID + @NumCount + 1 WHERE relCode=29
        DROP TABLE #PdtComTable
        --包件
        SELECT comMode=101, RelCode=Identity(INT, 1, 1), creDpt=Max(NumLoss),
               UniqueName=''常規~'' + MtlCode + ''~'' + IsNull(MtlSpecName, '''') + ''~'' + MtlClrName,
               PdtSeriesName=Max(MtlName), PdtKind=Max(PaperName), PdtCode=MtlCode,
               PdtSpecName=IsNull(MtlSpecName, ''''), PdtClrName=MtlClrName, MakeStore=Max(DptStk),
               MakeDpt=Max(SecCode), NumPackage=Max(NumDown), PackageType=Max(PaperWidth),
               CBM=Max(PaperLength), MZWeight=Max(MZWeight), nSalePrice=Max(MtlPrice),
               BarPdtCode=Max(BarPdtCode), IsPdtClr=Max(CASE WHEN IsPdtClr=1 THEN 1 ELSE 0 END),
               IsCustom=Max(CASE WHEN IsCustom=1 THEN 1 ELSE 0 END), Custom1, Custom2=Max(Custom2),
               Custom3=Max(Custom3)
        INTO #PdtBagTable
        FROM cPdtCom AS C
        WHERE comMode=150
              AND NOT EXISTS (SELECT ID
                              FROM cDefine AS B
                              WHERE B.comMode=101 AND C.MtlCode=B.userCode
                                    AND IsNull(C.MtlSpecName, '''')=IsNull(B.pyShort, '''')
                                    AND C.MtlClrName=B.SwiftCode
                                    AND IsNull(B.Custom1, '''')=IsNull(C.Custom1, ''''))
        GROUP BY MtlCode, MtlSpecName, MtlClrName, C.Custom1
        SET @sPdtRelCode=0
        SELECT @sPdtRelCode=IsNull(CurID, 0) + 1 FROM sFunc WHERE relCode=7
        SET @sPdtRelCode=IsNull(@sPdtRelCode, 1)
        INSERT INTO cDefine(comMode, relCode, creDpt, UniqueName, ACNO, pdcArea, userCode, pyShort,
                            SwiftCode, MakeStore, makeDpt, PdtSort, BagNum, preGet, CBM, MZWeight,
                            nSalePrice, BankAddress, IsPdtClr, IsCustom, Alias, Custom1, Custom2,
                            Custom3)
        SELECT comMode, RelCode=70000 + @sPdtRelCode + RelCode, creDpt, UniqueName, PdtSeriesName,
               PdtKind, PdtCode, PdtSpecName, PdtClrName, MakeStore, MakeDpt, ''常規'', NumPackage,
               PackageType, CBM, MZWeight, nSalePrice, ''常規銷售'', IsPdtClr, IsCustom, BarPdtCode, Custom1,
               Custom2, Custom3
        FROM #PdtBagTable
        SELECT @NumCount=Count(*)FROM #PdtBagTable
        SET @NumCount=IsNull(@NumCount, 0)
        UPDATE sFunc SET CurID=CurID + @NumCount + 1 WHERE relCode=7
        DROP TABLE #PdtBagTable
        UPDATE cPdtCom
        SET mRelCode=T.relCode
        FROM(SELECT relCode, userCode, pyShort, SwiftCode, Custom1 FROM cDefine WHERE comMode=110) AS T
        WHERE PdtUseCode=T.userCode AND IsNull(cPdtCom.PdtSplName, '''')=IsNull(T.pyShort, '''')
              AND cPdtCom.BarClrCode=T.SwiftCode AND IsNull(cPdtCom.ZHCustom1, '''')=IsNull(T.Custom1, '''')
              AND comMode=150
        --銷售組合信息的體積和毛重是否來自產品清單(自動根據包件體積算組合體積)
        IF EXISTS (SELECT relCode FROM cCtrSplr WHERE comMode=120 AND dbo.f_getstr(CtryName, 5, ''|'')=''1'')
        BEGIN
            UPDATE cDefine
            SET CBM=CASE WHEN T.CBM>0 THEN T.CBM ELSE cDefine.CBM END,
                MZWeight=CASE WHEN T.MZWeight>0 THEN T.MZWeight ELSE cDefine.MZWeight END
            FROM(SELECT mRelCode,
                        CBM=Sum(
                            CASE WHEN(PaperWidth=1) THEN
                            (CASE WHEN(NumDown<>0) THEN (NumUp / NumDown)ELSE 0 END)
                            ELSE NumUp * NumDown END * PaperLength),
                        MZWeight=Sum(
                                 CASE WHEN(PaperWidth=1) THEN
                                 (CASE WHEN(NumDown<>0) THEN (NumUp / NumDown)ELSE 0 END)
                                 ELSE NumUp * NumDown END * MZWeight)
                 FROM cPdtCom
                 WHERE comMode=150
                 GROUP BY mRelCode) AS T
            WHERE cDefine.relCode=T.mRelCode AND cDefine.comMode=110 AND (T.CBM>0 OR T.MZWeight>0)
        END
        UPDATE cDefine
        SET UniqueName=Cast(comMode AS VARCHAR(10)) + ''~'' + PdtSort + ''~'' + userCode + ''~''
                       + IsNull(pyShort, '''') + ''~'' + SwiftCode
                       + CASE WHEN IsCustom=1 THEN ''~'' + Custom1 ELSE '''' END
        WHERE comMode IN (101, 110)
        UPDATE cPdtCom
        SET UniqueName=''常規~'' + MtlCode + ''~'' + IsNull(MtlSpecName, '''') + ''~'' + MtlClrName
                       + CASE WHEN IsCustom=1 THEN ''~'' + Custom1 ELSE '''' END
        WHERE comMode=150
        --更新包件清單的編碼   
        UPDATE cPdtCom
        SET FirCode=T.relCode, PaperName=T.pdcArea, NumLoss=T.creDpt, DptStk=T.MakeStore,
            SecCode=T.makeDpt, MemoText=T.ZJPdtCode, IsPdtClr=T.IsPdtClr, IsCustom=T.IsCustom
        FROM(SELECT relCode, pdcArea, creDpt, MakeStore, makeDpt, ZJPdtCode,
                    uniquename=Replace(UniqueName, ''101~'', ''''), IsPdtClr, IsCustom
             FROM cDefine
             WHERE comMode=101) AS T
        WHERE cPdtCom.comMode=150 AND cPdtCom.UniqueName=T.uniquename
        --更新序號
        DECLARE @minmrelcode INT
        DECLARE @maxmrelcode INT
        SELECT @minmrelcode=Min(relCode), @maxmrelcode=Max(relCode)
        FROM cDefine
        WHERE comMode=110
        SET @minmrelcode=IsNull(@minmrelcode, 0)
        SET @maxmrelcode=IsNull(@maxmrelcode, 0)
        WHILE(@minmrelcode<[email protected])
        BEGIN
            UPDATE cPdtCom
            SET ID=T.id
            FROM(SELECT Rank() OVER (ORDER BY FirCode) AS id, FirCode
                 FROM cPdtCom
                 WHERE comMode=150 AND [email protected]) AS T
            WHERE [email protected] AND cPdtCom.FirCode=T.FirCode
            SET @[email protected] + 1
        END
        ----插入系列
        --INSERT INTO cDefine(comMode, relCode, creDpt, ChkInMan, AthrMan, Name, Picture, UniqueName)
        --SELECT 170,
        --       (310000 + Rank() OVER (ORDER BY T.MtlName ASC)
        --        +   (SELECT CurID FROM sFunc WHERE relCode=31)), 90002, '''', '''', T.MtlName, '''',
        --       ''170~'' + T.MtlName
        --FROM(SELECT MtlName
        --     FROM cPdtCom
        --     WHERE comMode=150 AND IsNull(MtlName, '''')<>''''
        --     GROUP BY MtlName) AS T
        --WHERE NOT EXISTS (SELECT * FROM cDefine AS CD WHERE CD.comMode=170 AND CD.Name=T.MtlName)
        UPDATE sFunc
        SET CurID=(SELECT Max(relCode)FROM cDefine WHERE comMode=170) - 310000
        WHERE sFunc.relCode=31
        ----插入顏色信息
        --INSERT INTO cDefine(comMode, relCode, creDpt, ChkInMan, AthrMan, Name, Picture, UniqueName)
        --SELECT 109,
        --       (150002 + Rank() OVER (ORDER BY T.BarClrCode ASC)
        --        +   (SELECT CurID FROM sFunc WHERE relCode=15)), 90002, '''', '''', T.BarClrCode, '''',
        --       ''109~'' + T.BarClrCode
        --FROM(SELECT BarClrCode
        --     FROM cPdtCom
        --     WHERE comMode=150 AND IsNull(BarClrCode, '''')<>''''
        --     GROUP BY BarClrCode) AS T
        --WHERE NOT EXISTS (SELECT * FROM cDefine AS CD WHERE CD.comMode=109 AND CD.Name=T.BarClrCode)
        --UPDATE sFunc
        --SET CurID=(SELECT Max(relCode)FROM cDefine WHERE comMode=109) - 150000
        --WHERE sFunc.relCode=15
        --插入類別
        --      DELETE cdefine WHERE comMode=160
        --      UPDATE sFunc
        --      SET CurID=0
        --      WHERE FuncName=''產品類別''
        --INSERT INTO cDefine(comMode, relCode, creDpt, ChkInMan, AthrMan, Name, Picture, UniqueName)
        --SELECT 160,
        --       (160000 + Rank() OVER (ORDER BY T.PaperName ASC)
        --        +   (SELECT CurID FROM sFunc WHERE FuncName=''產品類別'')), 90002, '''', '''', T.PaperName, '''',
        --       ''160~'' + T.PaperName
        --FROM(SELECT PaperName
        --     FROM cPdtCom
        --     WHERE IsNull(PaperName, '''')<>'''' AND comMode=150
        --     GROUP BY PaperName) AS T
        --WHERE NOT EXISTS (SELECT * FROM cDefine AS CD WHERE CD.comMode=160 AND CD.Name=T.PaperName)
        UPDATE sFunc
        SET CurID=(SELECT Max(relCode)FROM cDefine WHERE comMode=160) - 160000
        WHERE FuncName=''產品類別''
    END')
END
--拆分保存過程 SQL2008更新
IF @@version LIKE '%2008%'
BEGIN
    IF NOT EXISTS (SELECT * FROM sys.sysobjects WHERE name='PdtComToPdtZH' AND type='P')
        EXEC('
CREATE PROCEDURE PdtComToPdtZH(@sRelCode INT)
AS
    DECLARE @NumCount INT, @sPdtRelCode INT, @sTogetherRelCode INT, @tReturn VARCHAR(8000)
    BEGIN
        --銷售產品
        SELECT comMode=110, RelCode=Identity(INT, 1, 1), creDpt=Max(NumLoss),
               UniqueName=''組合~'' + PdtUseCode + ''~'' + IsNull(PdtSplName, '''') + ''~'' + BarClrCode,
               PdtSeriesName=Max(MtlName), PdtKind=Max(PaperName), PdtUseCode, PdtSplName, BarClrCode,
               nSalePrice=Max(PaperHigh), MakeStore=Max(DptStk), MakeDpt=Max(SecCode),
               BarPdtCode=Max(Marked), CBM=Max(JZWeight), PdtUnit=Max(BagName), ZHCustom1,
               ZHCustom2=Max(ZHCustom2), ZHCustom3=Max(ZHCustom3)
        INTO #PdtComTable
        FROM cPdtCom AS C
        WHERE C.comMode=150
              AND NOT EXISTS (SELECT ID
                              FROM cDefine AS B
                              WHERE B.comMode=110 AND C.PdtUseCode=B.userCode
                                    AND IsNull(C.PdtSplName, '''')=IsNull(B.pyShort, '''')
                                    AND C.BarClrCode=B.SwiftCode)
        GROUP BY PdtUseCode, PdtSplName, BarClrCode, ZHCustom1
        SELECT @sPdtRelCode=IsNull(CurID, 0) + 1 FROM sFunc WHERE relCode=29
        SET @sPdtRelCode=IsNull(@sPdtRelCode, 1)
        INSERT INTO cDefine(comMode, relCode, creDpt, UniqueName, ACNO, pdcArea, userCode, pyShort,
                            SwiftCode, nSalePrice, CBM, MakeStore, makeDpt, ZJPdtCode, PdtSort, preGet,
                            BankAddress, IsSale, UnitName, Custom1, Custom2, Custom3)
        SELECT comMode, RelCode=290000 + @sPdtRelCode + RelCode, creDpt, UniqueName, PdtSeriesName,
               PdtKind, PdtUseCode, IsNull(PdtSplName, ''''), BarClrCode, nSalePrice, CBM, MakeStore,
               MakeDpt, BarPdtCode, ''組合'', 2, ''常規銷售'', 1, PdtUnit, ZHCustom1, ZHCustom2, ZHCustom3
        FROM #PdtComTable
        SELECT @NumCount=Count(*)FROM #PdtComTable
        SET @NumCount=IsNull(@NumCount, 0)
        UPDATE sFunc SET CurID=CurID + @NumCount + 1 WHERE relCode=29
        DROP TABLE #PdtComTable
        --包件
        SELECT comMode=101, RelCode=Identity(INT, 1, 1), creDpt=Max(NumLoss),
               UniqueName=''常規~'' + MtlCode + ''~'' + IsNull(MtlSpecName, '''') + ''~'' + MtlClrName,
               PdtSeriesName=Max(MtlName), PdtKind=Max(PaperName), PdtCode=MtlCode,
               PdtSpecName=IsNull(MtlSpecName, ''''), PdtClrName=MtlClrName, MakeStore=Max(DptStk),
               MakeDpt=Max(SecCode), NumPackage=Max(NumDown), PackageType=Max(PaperWidth),
               CBM=Max(PaperLength), MZWeight=Max(MZWeight), nSalePrice=Max(MtlPrice),
               BarPdtCode=Max(BarPdtCode), IsPdtClr=Max(CASE WHEN IsPdtClr=1 THEN 1 ELSE 0 END),
               IsCustom=Max(CASE WHEN IsCustom=1 THEN 1 ELSE 0 END), Custom1, Custom2=Max(Custom2),
               Custom3=Max(Custom3)
        INTO #PdtBagTable
        FROM cPdtCom AS C
        WHERE comMode=150
              AND NOT EXISTS (SELECT ID
                              FROM cDefine AS B
                              WHERE B.comMode=101 AND C.MtlCode=B.userCode
                                    AND IsNull(C.MtlSpecName, '''')=IsNull(B.pyShort, '''')
                                    AND C.MtlClrName=B.SwiftCode
                                    AND IsNull(B.Custom1, '''')=IsNull(C.Custom1, ''''))
        GROUP BY MtlCode, MtlSpecName, MtlClrName, C.Custom1
        SET @sPdtRelCode=0
        SELECT @sPdtRelCode=IsNull(CurID, 0) + 1 FROM sFunc WHERE relCode=7
        SET @sPdtRelCode=IsNull(@sPdtRelCode, 1)
        INSERT INTO cDefine(comMode, relCode, creDpt, UniqueName, ACNO, pdcArea, userCode, pyShort,
                            SwiftCode, MakeStore, makeDpt, PdtSort, BagNum, preGet, CBM, MZWeight,
                            nSalePrice, BankAddress, IsPdtClr, IsCustom, Alias, Custom1, Custom2,
                            Custom3)
        SELECT comMode, RelCode=70000 + @sPdtRelCode + RelCode, creDpt, UniqueName, PdtSeriesName,
               PdtKind, PdtCode, PdtSpecName, PdtClrName, MakeStore, MakeDpt, ''常規'', NumPackage,
               PackageType, CBM, MZWeight, nSalePrice, ''常規銷售'', IsPdtClr, IsCustom, BarPdtCode, Custom1,
               Custom2, Custom3
        FROM #PdtBagTable
        SELECT @NumCount=Count(*)FROM #PdtBagTable
        SET @NumCount=IsNull(@NumCount, 0)
        UPDATE sFunc SET CurID=CurID + @NumCount + 1 WHERE relCode=7
        DROP TABLE #PdtBagTable
        UPDATE cPdtCom
        SET mRelCode=T.relCode
        FROM(SELECT relCode, userCode, pyShort, SwiftCode, Custom1 FROM cDefine WHERE comMode=110) AS T
        WHERE PdtUseCode=T.userCode AND IsNull(cPdtCom.PdtSplName, '''')=IsNull(T.pyShort, '''')
              AND cPdtCom.BarClrCode=T.SwiftCode AND IsNull(cPdtCom.ZHCustom1, '''')=IsNull(T.Custom1, '''')
              AND comMode=150
        --銷售組合信息的體積和毛重是否來自產品清單(自動根據包件體積算組合體積)
        IF EXISTS (SELECT relCode FROM cCtrSplr WHERE comMode=120 AND dbo.f_getstr(CtryName, 5, ''|'')=''1'')
        BEGIN
            UPDATE cDefine
            SET CBM=CASE WHEN T.CBM>0 THEN T.CBM ELSE cDefine.CBM END,
                MZWeight=CASE WHEN T.MZWeight>0 THEN T.MZWeight ELSE cDefine.MZWeight END
            FROM(SELECT mRelCode,
                        CBM=Sum(
                            CASE WHEN(PaperWidth=1) THEN
                            (CASE WHEN(NumDown<>0) THEN (NumUp / NumDown)ELSE 0 END)
                            ELSE NumUp * NumDown END * PaperLength),
                        MZWeight=Sum(
                                 CASE WHEN(PaperWidth=1) THEN
                                 (CASE WHEN(NumDown<>0) THEN (NumUp / NumDown)ELSE 0 END)
                                 ELSE NumUp * NumDown END * MZWeight)
                 FROM cPdtCom
                 WHERE comMode=150
                 GROUP BY mRelCode) AS T
            WHERE cDefine.relCode=T.mRelCode AND cDefine.comMode=110 AND (T.CBM>0 OR T.MZWeight>0)
        END
        UPDATE cDefine
        SET UniqueName=Cast(comMode AS VARCHAR(10)) + ''~'' + PdtSort + ''~'' + userCode + ''~''
                       + IsNull(pyShort, '''') + ''~'' + SwiftCode
                       + CASE WHEN IsCustom=1 THEN ''~'' + Custom1 ELSE '''' END
        WHERE comMode IN (101, 110)
        UPDATE cPdtCom
        SET UniqueName=''常規~'' + MtlCode + ''~'' + IsNull(MtlSpecName, '''') + ''~'' + MtlClrName
                       + CASE WHEN IsCustom=1 THEN ''~'' + Custom1 ELSE '''' END
        WHERE comMode=150
        --更新包件清單的編碼   
        UPDATE cPdtCom
        SET FirCode=T.relCode, PaperName=T.pdcArea, NumLoss=T.creDpt, DptStk=T.MakeStore,
            SecCode=T.makeDpt, MemoText=T.ZJPdtCode, IsPdtClr=T.IsPdtClr, IsCustom=T.IsCustom
        FROM(SELECT relCode, pdcArea, creDpt, MakeStore, makeDpt, ZJPdtCode,
                    uniquename=Replace(UniqueName, ''101~'', ''''), IsPdtClr, IsCustom
             FROM cDefine
             WHERE comMode=101) AS T
        WHERE cPdtCom.comMode=150 AND cPdtCom.UniqueName=T.uniquename
        --更新序號
        DECLARE @minmrelcode INT
        DECLARE @maxmrelcode INT
        SELECT @minmrelcode=Min(relCode), @maxmrelcode=Max(relCode)
        FROM cDefine
        WHERE comMode=110
        SET @minmrelcode=IsNull(@minmrelcode, 0)
        SET @maxmrelcode=IsNull(@maxmrelcode, 0)
        WHILE(@minmrelcode<[email protected])
        BEGIN
            UPDATE cPdtCom
            SET ID=T.id
            FROM(SELECT Rank() OVER (ORDER BY FirCode) AS id, FirCode
                 FROM cPdtCom
                 WHERE comMode=150 AND [email protected]) AS T
            WHERE [email protected] AND cPdtCom.FirCode=T.FirCode
            SET @[email protected] + 1
        END
        --插入系列
        INSERT INTO cDefine(comMode, relCode, creDpt, ChkInMan, AthrMan, Name, Picture, UniqueName)
        SELECT 170,
               (310000 + Rank() OVER (ORDER BY T.MtlName ASC)
                +   (SELECT CurID FROM sFunc WHERE relCode=31)), 90002, '''', '''', T.MtlName, '''',
               ''170~'' + T.MtlName
        FROM(SELECT MtlName
             FROM cPdtCom
             WHERE comMode=150 AND IsNull(MtlName, '''')<>''''
             GROUP BY MtlName) AS T
        WHERE NOT EXISTS (SELECT * FROM cDefine AS CD WHERE CD.comMode=170 AND CD.Name=T.MtlName)
        UPDATE sFunc
        SET CurID=(SELECT Max(relCode)FROM cDefine WHERE comMode=170) - 310000
        WHERE sFunc.relCode=31
        --插入顏色信息
        INSERT INTO cDefine(comMode, relCode, creDpt, ChkInMan, AthrMan, Name, Picture, UniqueName)
        SELECT 109,
               (150002 + Rank() OVER (ORDER BY T.BarClrCode ASC)
                +   (SELECT CurID FROM sFunc WHERE relCode=15)), 90002, '''', '''', T.BarClrCode, '''',
               ''109~'' + T.BarClrCode
        FROM(SELECT BarClrCode
             FROM cPdtCom
             WHERE comMode=150 AND IsNull(BarClrCode, '''')<>''''
             GROUP BY BarClrCode) AS T
        WHERE NOT EXISTS (SELECT * FROM cDefine AS CD WHERE CD.comMode=109 AND CD.Name=T.BarClrCode)
        UPDATE sFunc
        SET CurID=(SELECT Max(relCode)FROM cDefine WHERE comMode=109) - 150000
        WHERE sFunc.relCode=15
       -- 插入類別
              DELETE cdefine WHERE comMode=160
              UPDATE sFunc
              SET CurID=0
              WHERE FuncName=''產品類別''
        INSERT INTO cDefine(comMode, relCode, creDpt, ChkInMan, AthrMan, Name, Picture, UniqueName)
        SELECT 160,
               (160000 + Rank() OVER (ORDER BY T.PaperName ASC)
                +   (SELECT CurID FROM sFunc WHERE FuncName=''產品類別'')), 90002, '''', '''', T.PaperName, '''',
               ''160~'' + T.PaperName
        FROM(SELECT PaperName
             FROM cPdtCom
             WHERE IsNull(PaperName, '''')<>'''' AND comMode=150
             GROUP BY PaperName) AS T
        WHERE NOT EXISTS (SELECT * FROM cDefine AS CD WHERE CD.comMode=160 AND CD.Name=T.PaperName)
        UPDATE sFunc
        SET CurID=(SELECT Max(relCode)FROM cDefine WHERE comMode=160) - 160000
        WHERE FuncName=''產品類別''
    END')
    ELSE
        EXEC('
ALTER PROCEDURE PdtComToPdtZH(@sRelCode INT)
AS
    DECLARE @NumCount INT, @sPdtRelCode INT, @sTogetherRelCode INT, @tReturn VARCHAR(8000)
    BEGIN
        --銷售產品
        SELECT comMode=110, RelCode=Identity(INT, 1, 1), creDpt=Max(NumLoss),
               UniqueName=''組合~'' + PdtUseCode + ''~'' + IsNull(PdtSplName, '''') + ''~'' + BarClrCode,
               PdtSeriesName=Max(MtlName), PdtKind=Max(PaperName), PdtUseCode, PdtSplName, BarClrCode,
               nSalePrice=Max(PaperHigh), MakeStore=Max(DptStk), MakeDpt=Max(SecCode),
               BarPdtCode=Max(Marked), CBM=Max(JZWeight), PdtUnit=Max(BagName), ZHCustom1,
               ZHCustom2=Max(ZHCustom2), ZHCustom3=Max(ZHCustom3)
        INTO #PdtComTable
        FROM cPdtCom AS C
        WHERE C.comMode=150
              AND NOT EXISTS (SELECT ID
                              FROM cDefine AS B
                              WHERE B.comMode=110 AND C.PdtUseCode=B.userCode
                                    AND IsNull(C.PdtSplName, '''')=IsNull(B.pyShort, '''')
                                    AND C.BarClrCode=B.SwiftCode)
        GROUP BY PdtUseCode, PdtSplName, BarClrCode, ZHCustom1
        SELECT @sPdtRelCode=IsNull(CurID, 0) + 1 FROM sFunc WHERE relCode=29
        SET @sPdtRelCode=IsNull(@sPdtRelCode, 1)
        INSERT INTO cDefine(comMode, relCode, creDpt, UniqueName, ACNO, pdcArea, userCode, pyShort,
                            SwiftCode, nSalePrice, CBM, MakeStore, makeDpt, ZJPdtCode, PdtSort, preGet,
                            BankAddress, IsSale, UnitName, Custom1, Custom2, Custom3)
        SELECT comMode, RelCode=290000 + @sPdtRelCode + RelCode, creDpt, UniqueName, PdtSeriesName,
               PdtKind, PdtUseCode, IsNull(PdtSplName, ''''), BarClrCode, nSalePrice, CBM, MakeStore,
               MakeDpt, BarPdtCode, ''組合'', 2, ''常規銷售'', 1, PdtUnit, ZHCustom1, ZHCustom2, ZHCustom3
        FROM #PdtComTable
        SELECT @NumCount=Count(*)FROM #PdtComTable
        SET @NumCount=IsNull(@NumCount, 0)
        UPDATE sFunc SET CurID=CurID + @NumCount + 1 WHERE relCode=29
        DROP TABLE #PdtComTable
        --包件
        SELECT comMode=101, RelCode=Identity(INT, 1, 1), creDpt=Max(NumLoss),
               UniqueName=''常規~'' + MtlCode + ''~'' + IsNull(MtlSpecName, '''') + ''~'' + MtlClrName,
               PdtSeriesName=Max(MtlName), PdtKind=Max(PaperName), PdtCode=MtlCode,
               PdtSpecName=IsNull(MtlSpecName, ''''), PdtClrName=MtlClrName, MakeStore=Max(DptStk),
               MakeDpt=Max(SecCode), NumPackage=Max(NumDown), PackageType=Max(PaperWidth),
               CBM=Max(PaperLength), MZWeight=Max(MZWeight), nSalePrice=Max(MtlPrice),
               BarPdtCode=Max(BarPdtCode), IsPdtClr=Max(CASE WHEN IsPdtClr=1 THEN 1 ELSE 0 END),
               IsCustom=Max(CASE WHEN IsCustom=1 THEN 1 ELSE 0 END), Custom1, Custom2=Max(Custom2),
               Custom3=Max(Custom3)
        INTO #PdtBagTable
        FROM cPdtCom AS C
        WHERE comMode=150
              AND NOT EXISTS (SELECT ID
                              FROM cDefine AS B
                              WHERE B.comMode=101 AND C.MtlCode=B.userCode
                                    AND IsNull(C.MtlSpecName, '''')=IsNull(B.pyShort, '''')
                                    AND C.MtlClrName=B.SwiftCode
                                    AND IsNull(B.Custom1, '''')=IsNull(C.Custom1, ''''))
        GROUP BY MtlCode, MtlSpecName, MtlClrName, C.Custom1
        SET @sPdtRelCode=0
        SELECT @sPdtRelCode=IsNull(CurID, 0) + 1 FROM sFunc WHERE relCode=7
        SET @sPdtRelCode=IsNull(@sPdtRelCode, 1)
        INSERT INTO cDefine(comMode, relCode, creDpt, UniqueName, ACNO, pdcArea, userCode, pyShort,
                            SwiftCode, MakeStore, makeDpt, PdtSort, BagNum, preGet, CBM, MZWeight,
                            nSalePrice, BankAddress, IsPdtClr, IsCustom, Alias, Custom1, Custom2,
                            Custom3)
        SELECT comMode, RelCode=70000 + @sPdtRelCode + RelCode, creDpt, UniqueName, PdtSeriesName,
               PdtKind, PdtCode, PdtSpecName, PdtClrName, MakeStore, MakeDpt, ''常規'', NumPackage,
               PackageType, CBM, MZWeight, nSalePrice, ''常規銷售'', IsPdtClr, IsCustom, BarPdtCode, Custom1,
               Custom2, Custom3
        FROM #PdtBagTable
        SELECT @NumCount=Count(*)FROM #PdtBagTable
        SET @NumCount=IsNull(@NumCount, 0)
        UPDATE sFunc SET CurID=CurID + @NumCount + 1 WHERE relCode=7
        DROP TABLE #PdtBagTable
        UPDATE cPdtCom
        SET mRelCode=T.relCode
        FROM(SELECT relCode, userCode, pyShort, SwiftCode, Custom1 FROM cDefine WHERE comMode=110) AS T
        WHERE PdtUseCode=T.userCode AND IsNull(cPdtCom.PdtSplName, '''')=IsNull(T.pyShort, '''')
              AND cPdtCom.BarClrCode=T.SwiftCode AND IsNull(cPdtCom.ZHCustom1, '''')=IsNull(T.Custom1, '''')
              AND comMode=150
        --銷售組合信息的體積和毛重是否來自產品清單(自動根據包件體積算組合體積)
        IF EXISTS (SELECT relCode FROM cCtrSplr WHERE comMode=120 AND dbo.f_getstr(CtryName, 5, ''|'')=''1'')
        BEGIN
            UPDATE cDefine
            SET CBM=CASE WHEN T.CBM>0 THEN T.CBM ELSE cDefine.CBM END,
                MZWeight=CASE WHEN T.MZWeight>0 THEN T.MZWeight ELSE cDefine.MZWeight END
            FROM(SELECT mRelCode,
                        CBM=Sum(
                            CASE WHEN(PaperWidth=1) THEN
                            (CASE WHEN(NumDown<>0) THEN (NumUp / NumDown)ELSE 0 END)
                            ELSE NumUp * NumDown END * PaperLength),
                        MZWeight=Sum(
                                 CASE WHEN(PaperWidth=1) THEN
                                 (CASE WHEN(NumDown<>0) THEN (NumUp / NumDown)ELSE 0 END)
                                 ELSE NumUp * NumDown END * MZWeight)
                 FROM cPdtCom
                 WHERE comMode=150
                 GROUP BY mRelCode) AS T
            WHERE cDefine.relCode=T.mRelCode AND cDefine.comMode=110 AND (T.CBM>0 OR T.MZWeight>0)
        END
        UPDATE cDefine
        SET UniqueName=Cast(comMode AS VARCHAR(10)) + ''~'' + PdtSort + ''~'' + userCode + ''~''
                       + IsNull(pyShort, '''') + ''~'' + SwiftCode
                       + CASE WHEN IsCustom=1 THEN ''~'' + Custom1 ELSE '''' END
        WHERE comMode IN (101, 110)
        UPDATE cPdtCom
        SET UniqueName=''常規~'' + MtlCode + ''~'' + IsNull(MtlSpecName, '''') + ''~'' + MtlClrName
                       + CASE WHEN IsCustom=1 THEN ''~'' + Custom1 ELSE '''' END
        WHERE comMode=150
        --更新包件清單的編碼   
        UPDATE cPdtCom
        SET FirCode=T.relCode, PaperName=T.pdcArea, NumLoss=T.creDpt, DptStk=T.MakeStore,
            SecCode=T.makeDpt, MemoText=T.ZJPdtCode, IsPdtClr=T.IsPdtClr, IsCustom=T.IsCustom
        FROM(SELECT relCode, pdcArea, creDpt, MakeStore, makeDpt, ZJPdtCode,
                    uniquename=Replace(UniqueName, ''101~'', ''''), IsPdtClr, IsCustom
             FROM cDefine
             WHERE comMode=101) AS T
        WHERE cPdtCom.comMode=150 AND cPdtCom.UniqueName=T.uniquename
        --更新序號
        DECLARE @minmrelcode INT
        DECLARE @maxmrelcode INT
        SELECT @minmrelcode=Min(relCode), @maxmrelcode=Max(relCode)
        FROM cDefine
        WHERE comMode=110
        SET @minmrelcode=IsNull(@minmrelcode, 0)
        SET @maxmrelcode=IsNull(@maxmrelcode, 0)
        WHILE(@minmrelcode<[email protected])
        BEGIN
            UPDATE cPdtCom
            SET ID=T.id
            FROM(SELECT Rank() OVER (ORDER BY FirCode) AS id, FirCode
                 FROM cPdtCom
                 WHERE comMode=150 AND [email protected]inmrelcode) AS T
            WHERE [email protected] AND cPdtCom.FirCode=T.FirCode
            SET @[email protected] + 1
        END
        --插入系列
        INSERT INTO cDefine(comMode, relCode, creDpt, ChkInMan, AthrMan, Name, Picture, UniqueName)
        SELECT 170,
               (310000 + Rank() OVER (ORDER BY T.MtlName ASC)
                +   (SELECT CurID FROM sFunc WHERE relCode=31)), 90002, '''', '''', T.MtlName, '''',
               ''170~'' + T.MtlName
        FROM(SELECT MtlName
             FROM cPdtCom
             WHERE comMode=150 AND IsNull(MtlName, '''')<>''''
             GROUP BY MtlName) AS T
        WHERE NOT EXISTS (SELECT * FROM cDefine AS CD WHERE CD.comMode=170 AND CD.Name=T.MtlName)
        UPDATE sFunc
        SET CurID=(SELECT Max(relCode)FROM cDefine WHERE comMode=170) - 310000
        WHERE sFunc.relCode=31
        --插入顏色信息
        INSERT INTO cDefine(comMode, relCode, creDpt, ChkInMan, AthrMan, Name, Picture, UniqueName)
        SELECT 109,
               (150002 + Rank() OVER (ORDER BY T.BarClrCode ASC)
                +   (SELECT CurID FROM sFunc WHERE relCode=15)), 90002, '''', '''', T.BarClrCode, '''',
               ''109~'' + T.BarClrCode
        FROM(SELECT BarClrCode
             FROM cPdtCom
             WHERE comMode=150 AND IsNull(BarClrCode, '''')<>''''
             GROUP BY BarClrCode) AS T
        WHERE NOT EXISTS (SELECT * FROM cDefine AS CD WHERE CD.comMode=109 AND CD.Name=T.BarClrCode)
        UPDATE sFunc
        SET CurID=(SELECT Max(relCode)FROM cDefine WHERE comMode=109) - 150000
        WHERE sFunc.relCode=15
        --插入類別
              DELETE cdefine WHERE comMode=160
              UPDATE sFunc
              SET CurID=0
              WHERE FuncName=''產品類別''
        INSERT INTO cDefine(comMode, relCode, creDpt, ChkInMan, AthrMan, Name, Picture, UniqueName)
        SELECT 160,
               (160000 + Rank() OVER (ORDER BY T.PaperName ASC)
                +   (SELECT CurID FROM sFunc WHERE FuncName=''產品類別'')), 90002, '''', '''', T.PaperName, '''',
               ''160~'' + T.PaperName
        FROM(SELECT PaperName
             FROM cPdtCom
             WHERE IsNull(PaperName, '''')<>'''' AND comMode=150
             GROUP BY PaperName) AS T
        WHERE NOT EXISTS (SELECT * FROM cDefine AS CD WHERE CD.comMode=160 AND CD.Name=T.PaperName)
        UPDATE sFunc
        SET CurID=(SELECT Max(relCode)FROM cDefine WHERE comMode=160) - 160000
        WHERE FuncName=''產品類別''
    END')
END
---------------------------------------------我是分割線---------------------------------------------
--功能自定義過濾字段增加
ALTER TABLE [dbo].[sFunc]
ALTER COLUMN [PntStr] VARCHAR(200) COLLATE Chinese_PRC_CI_AS
ALTER TABLE [dbo].[sFunc]
ALTER COLUMN [OrderByName] VARCHAR(300) COLLATE Chinese_PRC_CI_AS
--ALTER TABLE [dbo].[dTaoBaoPdtOdr]
--ALTER COLUMN [seller_memo] NVARCHAR(4000) COLLATE Chinese_PRC_CI_AS
--20181126更新訂貨單出貨倉庫
UPDATE dPdtOdrDtl
SET PINOStore=T.Name
FROM cCtrSplr T
WHERE PlacedStoreID=T.relCode AND T.comMode=122
---------------------------------------------我是分割線---------------------------------------------
--20181008產品清單加單位和包裝規格
ALTER TABLE [dbo].[cPdtCom]
ALTER COLUMN [BarPdtCode] VARCHAR(100) COLLATE Chinese_PRC_CI_AS
---------------------------------------------我是分割線---------------------------------------------
--20180930產品型號字段加長
ALTER TABLE [dbo].[cDefine]
ALTER COLUMN [UniqueName] VARCHAR(500) COLLATE Chinese_PRC_CI_AS NOT NULL
ALTER TABLE [dbo].[cDefine]
ALTER COLUMN [name] VARCHAR(200) COLLATE Chinese_PRC_CI_AS
ALTER TABLE [dbo].[cDefine]
ALTER COLUMN [userCode] VARCHAR(200) COLLATE Chinese_PRC_CI_AS
ALTER TABLE [dbo].[cPdtCom]
ALTER COLUMN [MtlCode] VARCHAR(200) COLLATE Chinese_PRC_CI_AS
ALTER TABLE [dbo].[cPdtCom]
ALTER COLUMN [PdtUseCode] VARCHAR(200) COLLATE Chinese_PRC_CI_AS
ALTER TABLE [dbo].[dPdtOdrDtl]
ALTER COLUMN [PdtCode] VARCHAR(200) COLLATE Chinese_PRC_CI_AS
ALTER TABLE [dbo].[dPdtOdrDtlDtl]
ALTER COLUMN [PdtCode] VARCHAR(200) COLLATE Chinese_PRC_CI_AS
ALTER TABLE [dbo].[dTradeDtl]
ALTER COLUMN [PdtCode] VARCHAR(200) COLLATE Chinese_PRC_CI_AS
ALTER TABLE [dbo].[dTradeDtlDtl]
ALTER COLUMN [PdtCode] VARCHAR(200) COLLATE Chinese_PRC_CI_AS
ALTER TABLE [dbo].[dWorkShopDtl]
ALTER COLUMN [PdtCode] VARCHAR(200) COLLATE Chinese_PRC_CI_AS
ALTER TABLE [dbo].[dWorkShopDtlPdt]
ALTER COLUMN [PdtCode] VARCHAR(200) COLLATE Chinese_PRC_CI_AS
ALTER TABLE [dbo].[dWorkPieceDtl]
ALTER COLUMN [PdtCode] VARCHAR(200) COLLATE Chinese_PRC_CI_AS
ALTER TABLE [dbo].[cPdtPiePrice]
ALTER COLUMN [PdtUseCode] VARCHAR(200) COLLATE Chinese_PRC_CI_AS
ALTER TABLE [dbo].[dPdtStgDtl]
ALTER COLUMN [PdtCode] VARCHAR(200) COLLATE Chinese_PRC_CI_AS
ALTER TABLE [dbo].[dPdtStore]
ALTER COLUMN [PdtCode] VARCHAR(200) COLLATE Chinese_PRC_CI_AS
ALTER TABLE [dbo].[dPdtStoreBar]
ALTER COLUMN [PdtCode] VARCHAR(200) COLLATE Chinese_PRC_CI_AS
ALTER TABLE [dbo].[dPdtStoreSale]
ALTER COLUMN [PdtCode] VARCHAR(200) COLLATE Chinese_PRC_CI_AS
ALTER TABLE [dbo].[dPdtStockDtl]
ALTER COLUMN [PdtCode] VARCHAR(200) COLLATE Chinese_PRC_CI_AS
ALTER TABLE [dbo].[cPdtBomTree]
ALTER COLUMN [PdtCode] VARCHAR(200) COLLATE Chinese_PRC_CI_AS
ALTER TABLE [dbo].[dPdtPartDtl]
ALTER COLUMN [PdtCode] VARCHAR(200) COLLATE Chinese_PRC_CI_AS
ALTER TABLE [dbo].[dPdtPartStore]
ALTER COLUMN [PdtCode] VARCHAR(200) COLLATE Chinese_PRC_CI_AS
ALTER TABLE [dbo].[cPdtPie]
ALTER COLUMN [PdtCode] VARCHAR(200) COLLATE Chinese_PRC_CI_AS
ALTER TABLE [dbo].[dPdtCostPrice]
ALTER COLUMN [PdtCode] VARCHAR(200) COLLATE Chinese_PRC_CI_AS
ALTER TABLE [dbo].[cPdtClr]
ALTER COLUMN [PdtUseCode] VARCHAR(200) COLLATE Chinese_PRC_CI_AS
---------------------------------------------我是分割線---------------------------------------------
--20180911材料增加字段
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='dWorkShopDtlDtl' AND b.name='SplrName')
    ALTER TABLE [dbo].[dWorkShopDtlDtl] ADD [SplrName] VARCHAR(200) DEFAULT '' NULL
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='dWorkShopDtlDtl' AND b.name='SplrMtlName')
    ALTER TABLE [dbo].[dWorkShopDtlDtl]
    ADD [SplrMtlName] VARCHAR(200) DEFAULT '' NULL
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='dWorkShopDtlDtl' AND b.name='SplMtlMemo')
    ALTER TABLE [dbo].[dWorkShopDtlDtl]
    ADD [SplMtlMemo] VARCHAR(2000) DEFAULT '' NULL
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='dWorkShopHisDtlDtl' AND b.name='SplrName')
    ALTER TABLE [dbo].[dWorkShopHisDtlDtl]
    ADD [SplrName] VARCHAR(200) DEFAULT '' NULL
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='dWorkShopHisDtlDtl' AND b.name='SplrMtlName')
    ALTER TABLE [dbo].[dWorkShopHisDtlDtl]
    ADD [SplrMtlName] VARCHAR(200) DEFAULT '' NULL
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='dWorkShopHisDtlDtl' AND b.name='SplMtlMemo')
    ALTER TABLE [dbo].[dWorkShopHisDtlDtl]
    ADD [SplMtlMemo] VARCHAR(2000) DEFAULT '' NULL
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='cPdtBom' AND b.name='SplrName')
    ALTER TABLE [dbo].[cPdtBom] ADD [SplrName] VARCHAR(200) DEFAULT '' NULL
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='cPdtBom' AND b.name='SplrMtlName')
    ALTER TABLE [dbo].[cPdtBom] ADD [SplrMtlName] VARCHAR(200) DEFAULT '' NULL
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='cPdtBom' AND b.name='SplMtlMemo')
    ALTER TABLE [dbo].[cPdtBom] ADD [SplMtlMemo] VARCHAR(2000) DEFAULT '' NULL
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='cMtl' AND b.name='IsOutStore')
    ALTER TABLE [dbo].[cMtl] ADD [IsOutStore] BIT DEFAULT 0 NULL
---------------------------------------------我是分割線---------------------------------------------
--20190410計劃單計件加字段
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='cPdtBomTree' AND b.name='Pr_ParentName')
    ALTER TABLE [dbo].[cPdtBomTree] ADD [Pr_ParentName] VARCHAR(200) NULL
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='cPdtBomTree' AND b.name='Pr_ParentSpecName')
    ALTER TABLE [dbo].[cPdtBomTree] ADD [Pr_ParentSpecName] VARCHAR(200) NULL
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='dWorkShopDtlProcess' AND b.name='Pr_ParentName')
    ALTER TABLE [dbo].[dWorkShopDtlProcess] ADD [Pr_ParentName] VARCHAR(200) NULL
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='dWorkShopDtlProcess' AND b.name='Pr_ParentSpecName')
    ALTER TABLE [dbo].[dWorkShopDtlProcess]
    ADD [Pr_ParentSpecName] VARCHAR(200) NULL
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='cPdtBomTree' AND b.name='PLevel')
    ALTER TABLE [dbo].[cPdtBomTree] ADD [PLevel] INT NULL
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='dWorkShopDtlProcess' AND b.name='PLevel')
    ALTER TABLE [dbo].[dWorkShopDtlProcess] ADD [PLevel] INT NULL
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='dWorkShopDtlProcess' AND b.name='ProcessID')
    ALTER TABLE [dbo].[dWorkShopDtlProcess] ADD [ProcessID] INT NULL
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='dWorkShopDtlProcess' AND b.name='pPrintNum')
    ALTER TABLE [dbo].[dWorkShopDtlProcess] ADD [pPrintNum] INT NULL
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='dWorkShopDtlProcess' AND b.name='PdtClrName')
    ALTER TABLE [dbo].[dWorkShopDtlProcess] ADD [PdtClrName] VARCHAR(200) NULL
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='dWorkShopDtlProcess' AND b.name='PdtSort')
    ALTER TABLE [dbo].[dWorkShopDtlProcess] ADD [PdtSort] VARCHAR(20) NULL
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='dWorkShopDtlProcess' AND b.name='pPrintNum')
    ALTER TABLE [dbo].[dWorkShopDtlProcess] ADD DEFAULT 0 FOR [pPrintNum]
---------------------------------------------我是分割線---------------------------------------------
--20190501工價設置加產品顏色
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='cPdtPiePrice' AND b.name='PdtPClrName')
    ALTER TABLE [dbo].[cPdtPiePrice] ADD [PdtPClrName] VARCHAR(200) DEFAULT '' NULL
UPDATE cPdtPiePrice
SET PdtUseCode=userCode, PdtSplName=pyShort, PdtPClrName=SwiftCode
FROM cDefine AS T
WHERE(T.relCode=mRelCode)
---------------------------------------------我是分割線---------------------------------------------
--20190723增加圖片字段
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='cDefine' AND b.name='Pic_url')
    ALTER TABLE [dbo].[cDefine] ADD [Pic_url] VARCHAR(500) NULL
IF NOT EXISTS (SELECT *
               FROM sys.tables a
               JOIN sys.columns b ON b.object_id=a.object_id
               WHERE a.name='cMtl' AND b.name='Pic_url')
    ALTER TABLE [dbo].[cMtl] ADD [Pic_url] VARCHAR(500) NULL
---------------------------------------------我是分割線---------------------------------------------
--20190726訂單顯示庫存改為函數   
IF NOT EXISTS (SELECT * FROM sys.sysobjects WHERE name='f_PdtOdrStore' AND type='TF')
BEGIN
    EXEC('
CREATE   FUNCTION f_PdtOdrStore
   (@BillNo   VARCHAR(100),
   @SerID   INT,
   @PdtID   INT,
   @IsBill   BIT,
   @CtrSplrName VARCHAR(100)
   )
RETURNS @ReTable TABLE
   (StoreName   VARCHAR(100),
   curStore   NUMERIC(18, 3),
   CtrStore   NUMERIC(18, 3),
   CtrOthrNum NUMERIC(18, 3),
   KeStore   NUMERIC(18, 3),
   DHWC    NUMERIC(18, 3)
   )
AS
BEGIN
   IF @IsBill=1 --按訂單來提取庫存
   BEGIN
INSERT @ReTable(StoreName, curStore, CtrStore, CtrOthrNum, KeStore, DHWC)
SELECT M.Name, curStore=IsNull(Min(curStore / NullIf(B.NumPackage, 0)), 0),
   CtrStore=IsNull(Min(CtrStore / NullIf(B.NumPackage, 0)), 0),
   CtrOthrNum=IsNull(Min(CtrOthrNum / NullIf(B.NumPackage, 0)), 0),
   KeStore=IsNull(Min(curStore / NullIf(B.NumPackage, 0)), 0)
     -IsNull(Min(CtrStore / NullIf(B.NumPackage, 0)), 0)
     -IsNull(Min(CtrOthrNum / NullIf(B.NumPackage, 0)), 0),
   DHWC=IsNull(Max(DHWCNumber / NullIf(B.NumPackage, 0)), 0)
FROM dPdtOdrDtl A
JOIN dPdtOdrDtlDtl B ON A.mRelCode=B.mRelCode AND A.serID=B.serID
LEFT JOIN(SELECT creDpt, PdtName, curStore=Sum(Number),
      CtrStore=Sum(CASE WHEN CtrSplrName<>''本公司庫存'' THEN Number ELSE 0 END)
     FROM dPdtStore
     WHERE comMode=300
     GROUP BY creDpt, PdtName
    )S ON S.creDpt=A.PlacedStoreID AND S.PdtName=B.PdtName
LEFT JOIN(SELECT StoreID, PdtName, CtrOthrNum=Sum(Number)
     FROM dPdtStoreSale
     WHERE comMode=400 AND CtrSplrName=''本公司庫存''
     GROUP BY StoreID, PdtName
    )D ON D.StoreID=A.PlacedStoreID AND   D.PdtName=B.PdtName
LEFT JOIN(SELECT PdtName, PlacedStoreID, DHWCNumber=Sum(WSNumber)
     FROM (SELECT PdtName, PlacedStoreID, WSNumber=Number-NumHpn
      FROM dPdtOdrDtl K
      JOIN dPdtOdr D ON K.mRelCode=D.relCode
      WHERE D.isValidity=1 AND Number-NumHpn>0
      UNION ALL
      SELECT K.PdtName, PlacedStoreID, WSNumber=K.Number * K.NumPackage-NumInOut
      FROM dPdtOdrDtlDtl K
      JOIN dPdtOdrDtl F ON K.mRelCode=F.mRelCode AND K.serID=F.serID
      JOIN dPdtOdr D ON F.mRelCode=D.relCode
      WHERE D.isValidity=1 AND K.Number * K.NumPackage-K.NumInOut>0
     ) AS M
     GROUP BY PdtName,PlacedStoreID
    )C ON C.PlacedStoreID=A.PlacedStoreID AND C.PdtName=B.PdtName
LEFT JOIN cCtrSplr M ON M.relCode=A.PlacedStoreID
WHERE [email protected] AND [email protected] AND M.Name IS NOT NULL
GROUP BY M.Name
   END
   ELSE --按產品提取庫存
   BEGIN
INSERT @ReTable(StoreName, curStore, CtrStore, CtrOthrNum, KeStore, DHWC)
SELECT StoreName=C.Name, curStore=Min(curStore), CtrStore=Min(CtrStore),
   CtrOthrNum=Min(CtrOthrNum),
   [KeStore]=Min(IsNull(T2.curStore, 0)-IsNull(CtrOthrNum, 0)-IsNull(CtrStore, 0)),
   DHWC=Max(DHWCNumber)
FROM cPdtCom T1
LEFT JOIN(SELECT creDpt, PdtName, curStore=Sum(Number),
      CtrStore=Sum(CASE WHEN CtrSplrName<>''本公司庫存'' THEN Number ELSE 0 END)
     FROM dPdtStore
     WHERE comMode=300
     GROUP BY creDpt, PdtName
    ) AS T2 ON T1.UniqueName=T2.PdtName
LEFT JOIN(SELECT StoreID, PdtName, CtrOthrNum=Sum(Number)
     FROM dPdtStoreSale
     WHERE comMode=400 AND CtrSplrName=''本公司庫存''
     GROUP BY StoreID, PdtName
    ) AS T3 ON T1.UniqueName=T3.PdtName AND   T2.creDpt=T3.StoreID
LEFT JOIN(SELECT PdtName, PlacedStoreID, DHWCNumber=Sum(WSNumber)
     FROM (SELECT PdtName, PlacedStoreID, WSNumber=Number-NumHpn
      FROM dPdtOdrDtl K
      JOIN dPdtOdr D ON K.mRelCode=D.relCode
      WHERE D.isValidity=1 AND Number-NumHpn>0
      UNION ALL
      SELECT K.PdtName, PlacedStoreID, WSNumber=K.Number * K.NumPackage-NumInOut
      FROM dPdtOdrDtlDtl K
      JOIN dPdtOdrDtl F ON K.mRelCode=F.mRelCode AND K.serID=F.serID
      JOIN dPdtOdr D ON F.mRelCode=D.relCode
      WHERE D.isValidity=1 AND K.Number * K.NumPackage-K.NumInOut>0
     ) AS M
     GROUP BY PdtName, PlacedStoreID
    )DH ON DH.PdtName=T1.UniqueName AND DH.PlacedStoreID=T2.creDpt
LEFT JOIN cCtrSplr C ON C.relCode=T2.creDpt
WHERE [email protected] AND C.Name IS NOT NULL
GROUP BY C.Name
   END
   RETURN
END   ')
END
---------------------------------------------我是分割線---------------------------------------------
-- 20191020產品替換合并和復制新增加權限
IF NOT EXISTS (SELECT * FROM sFuncDtl WHERE comMode=100 AND ControlName='actReplace')
    INSERT INTO sFuncDtl(comMode, EmployeID, FuncID, ControlName, ChnName, WinControlName, IsUsed, IsReadOnly, sFuncCountID,
                         OrdID, BaseTable)
    VALUES(100, 30001, 7, 'actReplace', '包件替換', 'actReplace', 0, 0, 0, 9, '')
IF NOT EXISTS (SELECT * FROM sFuncDtl WHERE comMode=100 AND ControlName='actAddPdtZH')
    INSERT INTO sFuncDtl(comMode, EmployeID, FuncID, ControlName, ChnName, WinControlName, IsUsed, IsReadOnly, sFuncCountID,
                         OrdID, BaseTable)
    VALUES(100, 30001, 7, 'actAddPdtZH', '包件合并', 'actAddPdtZH', 0, 0, 0, 10, '')
---------------------------------------------我是分割線---------------------------------------------
IF NOT EXISTS (SELECT * FROM sFuncDtl WHERE comMode=100 AND ControlName='actNewCopy')
BEGIN
    INSERT INTO sFuncDtl(comMode, EmployeID, FuncID, ControlName, ChnName, WinControlName, OrdID, IsUsed)
    SELECT comMode=100, EmployeID=30001, relCode, 'actNewCopy', '復制新增', 'actNewCopy', 1, 1
    FROM sFunc
    WHERE(FKind=1 OR FKind=2)
         AND NOT EXISTS (SELECT *
                         FROM sFuncDtl
                         WHERE comMode=100
                               AND EmployeID=30001
                               AND FuncID=sFunc.relCode
                               AND ControlName='actNewCopy'
                               AND ChnName='復制新增')
    INSERT INTO sFuncDtl(comMode, EmployeID, FuncID, ControlName, ChnName, WinControlName, OrdID)
    SELECT 200, EmployeID, FuncID, 'actNewCopy', '復制新增', 'actNewCopy', 1
    FROM sFuncDtl S
    WHERE ChnName='新增'
          AND comMode=200
          AND EXISTS (SELECT relCode FROM sFunc WHERE(FKind=1 OR FKind=2)AND S.FuncID=relCode)
          AND NOT EXISTS (SELECT *
                          FROM sFuncDtl K
                          WHERE K.EmployeID=S.EmployeID AND ControlName='actNewCopy' AND ChnName='復制新增')
    GROUP BY EmployeID, FuncID
    UPDATE sFuncDtl SET IsUsed=1 WHERE ControlName='actNewCopy'
END
---------------------------------------------我是分割線---------------------------------------------
--20200408計劃單增加拆分和變更顏色和訂單拆分包件
IF NOT EXISTS (SELECT * FROM sys.sysobjects WHERE name='SpitWorkShopDtl' AND type='P')
    EXEC('CREATE PROCEDURE SpitWorkShopDtl
(@mRelCode VARCHAR(50), --單號
@SerID VARCHAR(50), --序號
@SpitNum VARCHAR(50), --拆分數量
@Field VARCHAR(50) ='''' --暫時不用
)
AS
    DECLARE @tmpSerID INT, @tmpComMode INT, @tmpMaxRelCode VARCHAR(20),
            @tmpMaxZHRelCode VARCHAR(20), @tmpAllRelCode VARCHAR(2000), @tmpCode VARCHAR(10),
            @tmpNum VARCHAR(10), @CtrSplrID INT, @CtrSplrName VARCHAR(50), @OutStoreID INT,
            @SprID INT, @SplrName VARCHAR(50), @NumError INT
    BEGIN
        SET @NumError=0
        SELECT @tmpSerID=Max(serID)FROM dWorkShopDtl WHERE [email protected]
        SET @tmpSerID=IsNull(@tmpSerID, 0)+5
        INSERT INTO dWorkShopDtl(ID, comMode, mRelCode, serID, OdrID, subSerID, SubSubSerID,
                                 PdtSort, Total, PackageTxt, PdtName, relCode, styleID, stdID,
                                 PINO, Number, Price, memoText, IsBuy, ReqDate, PdtCode,
                                 PdtSpecName, PdtUnit, PdtClrName, CtrType, PrintLogo, PrintMemo,
                                 MtlCommentDate, PdtCommentDate, CtrSplrID, CtrSplrName, NumOrd,
                                 CBM, PlacedStoreID, PdtClrWMName, QualityMan, QualityDate,
                                 QualityText, PartCode, PartName, NumPackage, PdtSeriesName,
                                 PdtKind, NumWastage, PONO, PackageType, PdtSpecNameOld, PlanDate,
                                 isPrint, BarClrCode, BarPdtCode, IsCal, Custom1, Custom2, Custom3,
                                 BagName)
        SELECT ID, comMode, mRelCode, [email protected], OdrID, subSerID, SubSubSerID, PdtSort,
               Total, PackageTxt, PdtName, relCode, styleID, stdID, PINO, [email protected], Price,
               memoText, IsBuy, ReqDate, PdtCode, PdtSpecName, PdtUnit, PdtClrName, CtrType,
               PrintLogo, PrintMemo, MtlCommentDate, PdtCommentDate, CtrSplrID, CtrSplrName,
               NumOrd, CBM, PlacedStoreID, PdtClrWMName, QualityMan, QualityDate, QualityText,
               PartCode, PartName, NumPackage, PdtSeriesName, PdtKind, NumWastage, PONO,
               PackageType, PdtSpecNameOld, PlanDate, isPrint, BarClrCode, BarPdtCode, IsCal,
               Custom1, Custom2, Custom3, BagName
        FROM dWorkShopDtl
        WHERE [email protected] AND [email protected]
        INSERT INTO dWorkShopDtlPdt(ID, comMode, mRelCode, serID, SubSerID, OdrID, OdrserID,
                                    OdrSubSerID, relCode, Number, memoText, PdtName, IsBuy,
                                    PdtSort, PdtCode, PdtSpecName, PdtUnit, PdtClrName,
                                    PdtSeriesName, PdtKind, NumPackage, MZWeight, JZWeight, CBM,
                                    NumBox, PackNum, PackageType, BarClrCode, BarPdtCode, styleID,
                                    IsPdtClr, Custom1, Custom2, Custom3, IsCustom, BagName)
        SELECT ID, comMode, mRelCode, [email protected], [email protected] * 1000+SubSerID, OdrID,
               OdrserID, OdrSubSerID, relCode, [email protected] * NumPackage, memoText, PdtName,
               IsBuy, PdtSort, PdtCode, PdtSpecName, PdtUnit, PdtClrName, PdtSeriesName, PdtKind,
               NumPackage, MZWeight, JZWeight, CBM, NumBox, PackNum, PackageType, BarClrCode,
               BarPdtCode, styleID, IsPdtClr, Custom1, Custom2, Custom3, IsCustom, BagName
        FROM dWorkShopDtlPdt
        WHERE [email protected] AND [email protected]
        UPDATE dWorkShopDtl SET [email protected] WHERE [email protected] AND [email protected]
        UPDATE dWorkShopDtlPdt
        SET [email protected] * NumPackage
        WHERE [email protected] AND [email protected]
             
        IF   (@NumError<>0)
        BEGIN
                 
            SET @tmpAllRelCode=''更新數據出錯,操作失敗!編號:''+Cast(@NumError AS VARCHAR(20))
            RAISERROR(@tmpAllRelCode, 16, 1);
            RETURN;
        END
    END ')
---------------------------------------------我是分割線---------------------------------------------
--2020年盤點單合并
IF NOT EXISTS (SELECT * FROM sys.sysobjects WHERE name='dPdtStoreBarStock' AND type='U')
    CREATE TABLE [dbo].[dPdtStoreBarStock] (
    [ID] [BIGINT] IDENTITY(1, 1) NOT NULL,
    [BuyID] [BIGINT] NOT NULL,
    [InStoreID] [BIGINT] NULL,
    [mRelCode] [VARCHAR](20) NULL,
    [SerID] [INT] NULL,
    [SubSerID] [INT] NULL,
    [CtrSplrName] [VARCHAR](50) NULL,
    [PdtName] [VARCHAR](500) NOT NULL,
    [SplrName] [VARCHAR](50) NULL,
    [PdtSeriesName] [VARCHAR](50) NULL,
    [PdtKind] [VARCHAR](50) NULL,
    [PdtSort] [VARCHAR](20) NULL,
    [PdtCode] [VARCHAR](150) NULL,
    [PdtSpecName] [VARCHAR](50) NULL,
    [PdtClrName] [VARCHAR](100) NULL,
    [Number] [NUMERIC](18, 3) NULL,
    [CreateDate] [DATETIME] NULL,
    [IsBuyInStore] [BIT] NULL,
    [BuyInStoreDate] [DATETIME] NULL,
    [IsSaleOutStore] [BIT] NULL,
    [SaleOutStoreDate] [DATETIME] NULL,
    [OthrInStore] [BIT] NULL,
    [OthrInStoreDate] [DATETIME] NULL,
    [OthrOutStore] [BIT] NULL,
    [OthrOutStoreDate] [DATETIME] NULL,
    [Price] [NUMERIC](18, 4) NULL,
    [IsPrint] [BIT] NULL,
    [PrintBillNo] [VARCHAR](30) NULL,
    [PrintDate] [DATETIME] NULL,
    [BuyInStoreNo] [VARCHAR](30) NULL,
    [SaleOutStoreNo] [VARCHAR](30) NULL,
    [OthrInStoreNo] [VARCHAR](30) NULL,
    [OthrOutStoreNo] [VARCHAR](30) NULL,
    [TradeNo] [VARCHAR](200) NULL,
    [sPdtID] [INT] NULL,
    [Custom1] [VARCHAR](100) NULL,
    [Custom2] [VARCHAR](100) NULL,
    [Custom3] [VARCHAR](100) NULL,
    [PdtUnit] [VARCHAR](10) NULL,
    [Memo] [VARCHAR](1000) NULL,
    [TradeSerID] [INT] NULL,
    [DStoreID] [INT] NULL) ON [PRIMARY]
IF NOT EXISTS (SELECT * FROM sys.sysobjects WHERE name='f_split' AND type='IF')
    EXEC('CREATE FUNCTION f_split
(
   @s varchar(8000), --待分拆的字符串
   @split varchar(10) --數據分隔符
)returns table
as
return
(
   select substring(@s,number,charindex(@split,@[email protected],number)-number) as col
   from master..spt_values
   where type=''p'' and number<=len(@s+''a'')
     and charindex(@split,@[email protected],number)=number
)'   )
IF NOT EXISTS (SELECT * FROM sys.sysobjects WHERE name='PdtStoreStockHB' AND type='P')
    EXEC('
   CREATE PROCEDURE PdtStoreStockHB
(@sIsStockHB INT, --1、盤點單合并;2、合并相同包件
@sRelCode   VARCHAR(500)
)
AS
    DECLARE @tmpRelCode VARCHAR(200), @ErrorNum INT, @newrelcode VARCHAR(20)
    BEGIN
       
        SET @ErrorNum=0
   SET XACT_ABORT ON
   BEGIN TRANSACTION
        IF @sIsStockHB=1
        BEGIN
            SELECT relcode=col INTO #StockRelcode FROM dbo.f_split(@sRelCode, '','')
            SELECT TOP 1 @newrelcode=relcode FROM #StockRelcode
            IF @newrelcode<>''''
            BEGIN
                --判斷是否有serid重復
                IF EXISTS (SELECT   SerID
                           FROM     dPdtStockDtl
                           WHERE    mRelCode IN(SELECT relcode FROM #StockRelcode)
                           GROUP BY SerID
                           HAVING   Count(SerID)>1)
                BEGIN
                    CREATE TABLE #temp
                    (ID          INT IDENTITY(1, 1),
                     mRelcode    VARCHAR(30),
                     CtrSplrName VARCHAR(50),
                     PdtName     VARCHAR(500)
                    )
                    INSERT INTO #temp(mRelcode, CtrSplrName, PdtName)
                    SELECT   mRelCode, CtrSplrName, PdtName
                    FROM     dPdtStockDtl
                    WHERE    mRelCode IN(SELECT relcode FROM #StockRelcode)
                    ORDER BY mRelCode, CtrSplrName, PdtName
                    UPDATE dPdtStockDtl
                    SET    SerID=T.ID
                    FROM   #temp T
                    WHERE   T.mRelcode=dPdtStockDtl.mRelCode AND T.CtrSplrName=dPdtStockDtl.CtrSplrName
                           AND dPdtStockDtl.PdtName=T.PdtName
                    DROP TABLE #temp
                END
                SET @[email protected][email protected]@error
                 
                UPDATE dPdtStoreBarStock
                SET    [email protected]
                FROM   #StockRelcode AS T
                WHERE   mRelCode=relcode
                SET @[email protected][email protected]@error
            END
            DROP TABLE #StockRelcode
        END
        IF @sIsStockHB=2 --合并相同包件的時候
        BEGIN
            --電商
            UPDATE dPdtStoreBar
            SET    IsBuyInStore=1,   
                   IsSaleOutStore=NULL
            FROM   dPdtStoreBarStock T
            WHERE   [email protected] AND dPdtStoreBar.BuyID=T.BuyID
   
            SET @[email protected][email protected]@error
        END
        IF @ErrorNum<>0
        BEGIN
            ROLLBACK TRANSACTION
            SET @tmpRelCode=''更新出錯!''+''數量:''+Cast(@ErrorNum AS VARCHAR(10))
            SELECT @tmpRelCode
            RETURN;
        END
        ELSE
        BEGIN
            COMMIT TRANSACTION
            SELECT @tmpRelCode
        END
    END
   ' )


留言
聯系方式
 
 

聯系人:肖生     

手 機:189-28668085

在線QQ: pa?p=1:58413709:3 肖工

在線QQ: fma.png 冉工

郵 箱:[email protected]

地 址:佛山市順德區樂從鎮新華路1號三樂路口(領航國際604號)