常見問題分類
 

工廠版

 

電商版

 

通—用

常見問題

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

19
發表時間:2020-06-09 17:41

--1,把約束重命名,方便drop

IF EXISTS (SELECT * FROM sysobjects WHERE name='ReNameDefaultConstraints' AND type='P')

BEGIN

    DROP PROCEDURE ReNameDefaultConstraints

END


EXEC(N'--將約束名設置為DF_TableName_ColumName 支持SQL2000

CREATE   PROCEDURE ReNameDefaultConstraints

AS

BEGIN  

DECLARE @tableName VARCHAR(200)

DECLARE @columname sysname

DECLARE @newtype sysname

DECLARE @default VARCHAR(20)

DECLARE @DFName VARCHAR(256)


DECLARE mycur CURSOR LOCAL

FOR

SELECT   tableName=Object_Name(id), columname=Col_Name(id, colid),DFname=Object_Name(constid),

dfault=B.COLUMN_DEFAULT

FROM sysconstraints A

LEFT JOIN information_schema.columns B ON B.TABLE_NAME=Object_Name(id) AND B.COLUMN_NAME=Col_Name(id, colid)

WHERE colid>0


OPEN mycur


FETCH NEXT FROM mycur

INTO @tableName, @columname, @DFName, @default


WHILE @@fetch_Status=0

BEGIN

    EXEC(''ALTER TABLE ''[email protected]+'' DROP CONSTRAINT [''[email protected]+'']'')


    --PRINT(''ALTER TABLE ''[email protected]+'' DROP CONSTRAINT [''[email protected]+'']'')


    SET @DFName=''DF_''[email protected]+''_''[email protected]

    EXEC(''ALTER TABLE ''[email protected]+'' ADD CONSTRAINT [''[email protected]+''] DEFAULT ''[email protected]+'' FOR [''[email protected]+'']'')


    --PRINT(''ALTER TABLE ''[email protected]+'' ADD CONSTRAINT [''[email protected]+''] DEFAULT ''[email protected]+'' FOR ['' [email protected]+'']'')


    FETCH NEXT FROM mycur

    INTO @tableName, @columname, @DFName, @default --逐條讀取   

END


CLOSE mycur

DEALLOCATE mycur


END ')


EXEC ReNameDefaultConstraints


--20161129產品規格字段加長

ALTER TABLE [dbo].[cDefine]

ALTER COLUMN [pyShort] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[cPdtCom]

ALTER COLUMN [MtlSpecName] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[cPdtCom]

ALTER COLUMN [PdtSplName] Varchar(170) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtOdrDtl]

ALTER COLUMN [PdtSpecName] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dTradeDtl]

ALTER COLUMN [PdtSpecName] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtOdrDtlDtl]

ALTER COLUMN [PdtSpecName] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dTradeDtlDtl]

ALTER COLUMN [PdtSpecName] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dWorkShopDtl]

ALTER COLUMN [PdtSpecName] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dWorkShopDtlPdt]

ALTER COLUMN [PdtSpecName] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dWorkPieceDtl]

ALTER COLUMN [PdtSpecName] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[cPdtPiePrice]

ALTER COLUMN [PdtSplName] Varchar(170) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtStgDtl]

ALTER COLUMN [PdtSpecName] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtStore]

ALTER COLUMN [PdtSpecName] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[cPdtBomTree]

ALTER COLUMN [PdtSpecName] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtPartDtl]

ALTER COLUMN [PdtSpecName] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtPartStore]

ALTER COLUMN [PdtSpecName] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtStockDtl]

ALTER COLUMN [PdtSpecName] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[cPdtPie]

ALTER COLUMN [PdtSpecName] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtStoreBar]

ALTER COLUMN [PdtSpecName] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtStoreSale]

ALTER COLUMN [PdtSpecName] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtCostPrice]

ALTER COLUMN [PdtSpecName] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[cPdtClr]

ALTER COLUMN [PdtSplName] Varchar(170) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[cDefine]

ALTER COLUMN [SwiftCode] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtOdrDtl]

ALTER COLUMN [PdtClrName] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dTradeDtl]

ALTER COLUMN [PdtClrName] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtOdrDtlDtl]

ALTER COLUMN [PdtClrName] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dTradeDtlDtl]

ALTER COLUMN [PdtClrName] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dWorkShopDtl]

ALTER COLUMN [PdtClrName] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dWorkShopDtlPdt]

ALTER COLUMN [PdtClrName] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dWorkPieceDtl]

ALTER COLUMN [PdtClrName] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[cPdtPiePrice]

ALTER COLUMN [PdtSplName] Varchar(170) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtStgDtl]

ALTER COLUMN [PdtClrName] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtStore]

ALTER COLUMN [PdtClrName] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtPartDtl]

ALTER COLUMN [PdtClrName] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtPartStore]

ALTER COLUMN [PdtClrName] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtStockDtl]

ALTER COLUMN [PdtClrName] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtStoreBar]

ALTER COLUMN [PdtClrName] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtStoreSale]

ALTER COLUMN [PdtClrName] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtCostPrice]

ALTER COLUMN [PdtClrName] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtOdrDtl]

ALTER COLUMN [CtrType] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtOdrDtl]

ALTER COLUMN [PdtClrWMName] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtOdrDtl]

ALTER COLUMN [PdtSpecNameOld] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dTradeDtl]

ALTER COLUMN [CtrType] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dTradeDtl]

ALTER COLUMN [PdtClrWMName] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dTradeDtl]

ALTER COLUMN [PdtSpecNameOld] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dWorkShopDtl]

ALTER COLUMN [CtrType] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dWorkShopDtl]

ALTER COLUMN [PdtClrWMName] Varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dWorkShopDtl]

ALTER COLUMN [PdtSpecNameOld] Varchar(150) COLLATE Chinese_PRC_CI_AS


--20170110工價設置增加產品表模式

IF NOT EXISTS (SELECT * FROM syscolumns WHERE id=Object_Id('cPdtPiePrice')AND 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 syscolumns

               WHERE id=Object_Id('cAccountSet')AND 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 syscolumns WHERE id=Object_Id('dPdtStoreBar')AND name='SPINO')

    ALTER TABLE [dbo].[dPdtStoreBar]

    ADD [SPINO] Varchar(100) DEFAULT '' NULL


IF NOT EXISTS (SELECT * FROM syscolumns WHERE id=Object_Id('cDefine')AND name='IsCustom')

    ALTER TABLE [dbo].[cDefine]

    ADD [IsCustom] Bit DEFAULT 0 NULL


IF NOT EXISTS (SELECT * FROM syscolumns WHERE id=Object_Id('cPdtCom')AND name='IsCustom')

    ALTER TABLE [dbo].[cPdtCom]

    ADD [IsCustom] Bit DEFAULT 0 NULL


IF NOT EXISTS (SELECT *

               FROM syscolumns

               WHERE id=Object_Id('dWorkShopDtlPdt')AND name='IsCustom')

    ALTER TABLE [dbo].[dWorkShopDtlPdt]

    ADD [IsCustom] Bit DEFAULT 0 NULL


IF NOT EXISTS (SELECT * FROM syscolumns WHERE id=Object_Id('dPdtOdrDtlDtl')AND name='IsCustom')

    ALTER TABLE [dbo].[dPdtOdrDtlDtl]

    ADD [IsCustom] Bit DEFAULT 0 NULL


IF NOT EXISTS (SELECT *

               FROM syscolumns

               WHERE id=Object_Id('dWorkShopHisDtlPdt')AND name='IsCustom')

    ALTER TABLE [dbo].[dWorkShopHisDtlPdt]

    ADD [IsCustom] Bit DEFAULT 0 NULL


IF NOT EXISTS (SELECT *

               FROM syscolumns

               WHERE id=Object_Id('dPdtOdrHisDtlDtl')AND name='IsCustom')

    ALTER TABLE [dbo].[dPdtOdrHisDtlDtl]

    ADD [IsCustom] Bit DEFAULT 0 NULL


IF NOT EXISTS (SELECT *

               FROM syscolumns

               WHERE id=Object_Id('dTradeHisDtlDtl')AND name='IsCustom')

    ALTER TABLE [dbo].[dTradeHisDtlDtl]

    ADD [IsCustom] Bit DEFAULT 0 NULL


IF NOT EXISTS (SELECT *

               FROM syscolumns

               WHERE id=Object_Id('dWorkShopHisDtlPdt')AND name='IsCustom')

    ALTER TABLE [dbo].[dWorkShopHisDtlPdt]

    ADD [IsCustom] Bit DEFAULT 0 NULL


IF NOT EXISTS (SELECT *

               FROM syscolumns

               WHERE id=Object_Id('dPdtOdrHisDtlDtl')AND name='IsCustom')

    ALTER TABLE [dbo].[dPdtOdrHisDtlDtl]

    ADD [IsCustom] Bit DEFAULT 0 NULL


IF NOT EXISTS (SELECT *

               FROM syscolumns

               WHERE id=Object_Id('dTradeHisDtlDtl')AND name='IsCustom')

    ALTER TABLE [dbo].[dTradeHisDtlDtl]

    ADD [IsCustom] Bit DEFAULT 0 NULL


--20170521會計科目加借貸方向

IF NOT EXISTS (SELECT *

               FROM syscolumns

               WHERE id=Object_Id('dAccountRegDtl')AND 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 syscolumns WHERE id=Object_Id('cPdtPiePrice')AND 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 syscolumns

               WHERE id=Object_Id('dTradeFareInOutDtl')AND 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 syscolumns WHERE id=Object_Id('dAccountRegDtl')AND 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 syscolumns WHERE id=Object_Id('dAccountRegDtl')AND name='Field1')

    ALTER TABLE [dbo].[dAccountRegDtl]

    ADD [Field1] Varchar(100) NULL


IF NOT EXISTS (SELECT * FROM syscolumns WHERE id=Object_Id('dAccountRegDtl')AND name='Field2')

    ALTER TABLE [dbo].[dAccountRegDtl]

    ADD [Field2] Varchar(100) NULL


IF NOT EXISTS (SELECT * FROM syscolumns WHERE id=Object_Id('dAccountRegDtl')AND 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訂單包裝件數改字段數字


--dPdtOdrDtl

IF EXISTS (SELECT 1 FROM sysconstraints WHERE Object_Name(constid)='DF_dPdtOdrDtl_NumPackage')

    ALTER TABLE dPdtOdrDtl DROP CONSTRAINT DF_dPdtOdrDtl_NumPackage


ALTER TABLE dPdtOdrDtl ALTER COLUMN NumPackage Numeric(18, 3) NULL


ALTER TABLE dPdtOdrDtl ADD CONSTRAINT DF_dPdtOdrDtl_NumPackage DEFAULT 0 FOR NumPackage

--dPdtOdrDtlDtl

IF EXISTS (SELECT 1   FROM sysconstraints   WHERE Object_Name(constid)='DF_dPdtOdrDtlDtl_NumPackage')

    ALTER TABLE dPdtOdrDtlDtl DROP CONSTRAINT DF_dPdtOdrDtlDtl_NumPackage


ALTER TABLE dPdtOdrDtlDtl ALTER COLUMN NumPackage Numeric(18, 3)


ALTER TABLE dPdtOdrDtlDtl ADD CONSTRAINT DF_dPdtOdrDtlDtl_NumPackage DEFAULT 0 FOR NumPackage

--dTradeDtl

IF EXISTS (SELECT 1   FROM sysconstraints   WHERE Object_Name(constid)='DF_dTradeDtl_NumPackage')

    ALTER TABLE dTradeDtl DROP CONSTRAINT DF_dTradeDtl_NumPackage


ALTER TABLE dTradeDtl ALTER COLUMN NumPackage Numeric(18, 3)


ALTER TABLE dTradeDtl ADD CONSTRAINT DF_dTradeDtl_NumPackage DEFAULT 0 FOR NumPackage

--dWorkShopDtl

IF EXISTS (SELECT 1   FROM sysconstraints   WHERE Object_Name(constid)='DF_dWorkShopDtl_NumPackage')

    ALTER TABLE dWorkShopDtl DROP CONSTRAINT DF_dWorkShopDtl_NumPackage


ALTER TABLE dWorkShopDtl ALTER COLUMN NumPackage Numeric(18, 3)


ALTER TABLE dWorkShopDtl ADD CONSTRAINT DF_dWorkShopDtl_NumPackage DEFAULT 0 FOR NumPackage


--20170725取消IX_cPdtCom_Unique

IF EXISTS (SELECT *

           FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS idx

           WHERE idx.CONSTRAINT_NAME='IX_cPdtCom_Unique')

    ALTER TABLE [dbo].[cPdtCom]

    DROP CONSTRAINT [IX_cPdtCom_Unique]


--20170620分支客戶加系列和折扣

IF NOT EXISTS (SELECT * FROM syscolumns WHERE id=Object_Id('cCtrSplrDtl')AND 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 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)


    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [PK_cPdtPartCom] PRIMARY KEY CLUSTERED

            (

            [comMode] ASC,

            [mRelCode] ASC,

            [serID] ASC)ON [PRIMARY]


    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 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 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


---------------------------------------------我是分割線---------------------------------------------

--功能自定義過濾字段增加

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 syscolumns   WHERE id=Object_Id('dWorkShopDtlDtl')AND name='SplrName')

    ALTER TABLE [dbo].[dWorkShopDtlDtl] ADD [SplrName] Varchar(200) DEFAULT '' NULL


IF NOT EXISTS (SELECT * FROM syscolumns   WHERE id=Object_Id('dWorkShopDtlDtl')AND name='SplrMtlName')

    ALTER TABLE [dbo].[dWorkShopDtlDtl] ADD [SplrMtlName] Varchar(200) DEFAULT '' NULL


IF NOT EXISTS (SELECT * FROM syscolumns WHERE id=Object_Id('dWorkShopDtlDtl')AND name='SplMtlMemo')

    ALTER TABLE [dbo].[dWorkShopDtlDtl] ADD [SplMtlMemo] Varchar(2000) DEFAULT '' NULL


IF NOT EXISTS (SELECT * FROM syscolumns   WHERE id=Object_Id('dWorkShopHisDtlDtl')AND name='SplrName')

    ALTER TABLE [dbo].[dWorkShopHisDtlDtl] ADD [SplrName] Varchar(200) DEFAULT '' NULL


IF NOT EXISTS (SELECT *   FROM syscolumns WHERE id=Object_Id('dWorkShopHisDtlDtl')AND name='SplrMtlName')

    ALTER TABLE [dbo].[dWorkShopHisDtlDtl]   ADD [SplrMtlName] Varchar(200) DEFAULT '' NULL


IF NOT EXISTS (SELECT * FROM syscolumns   WHERE id=Object_Id('dWorkShopHisDtlDtl')AND name='SplMtlMemo')

    ALTER TABLE [dbo].[dWorkShopHisDtlDtl] ADD [SplMtlMemo] Varchar(2000) DEFAULT '' NULL


IF NOT EXISTS (SELECT * FROM syscolumns WHERE id=Object_Id('cPdtBom')AND name='SplrName')

    ALTER TABLE [dbo].[cPdtBom]   ADD [SplrName] Varchar(200) DEFAULT '' NULL


IF NOT EXISTS (SELECT * FROM syscolumns WHERE id=Object_Id('cPdtBom')AND name='SplrMtlName')

    ALTER TABLE [dbo].[cPdtBom] ADD [SplrMtlName] Varchar(200) DEFAULT '' NULL


IF NOT EXISTS (SELECT * FROM syscolumns WHERE id=Object_Id('cPdtBom')AND name='SplMtlMemo')

    ALTER TABLE [dbo].[cPdtBom]   ADD [SplMtlMemo] Varchar(2000) DEFAULT '' NULL


IF NOT EXISTS (SELECT * FROM syscolumns WHERE id=Object_Id('cMtl')AND name='IsOutStore')

    ALTER TABLE [dbo].[cMtl] ADD [IsOutStore] Bit DEFAULT 0 NULL


---------------------------------------------我是分割線---------------------------------------------

--20190410計劃單計件加字段

IF NOT EXISTS (SELECT *   FROM syscolumns WHERE id=Object_Id('cPdtBomTree')AND name='Pr_ParentName')

    ALTER TABLE [dbo].[cPdtBomTree] ADD [Pr_ParentName] Varchar(200) NULL


IF NOT EXISTS (SELECT * FROM syscolumns   WHERE id=Object_Id('cPdtBomTree')AND name='Pr_ParentSpecName')

    ALTER TABLE [dbo].[cPdtBomTree] ADD [Pr_ParentSpecName] Varchar(200) NULL


IF NOT EXISTS (SELECT *

               FROM syscolumns

               WHERE id=Object_Id('dWorkShopDtlProcess')AND name='Pr_ParentName')

    ALTER TABLE [dbo].[dWorkShopDtlProcess]

    ADD [Pr_ParentName] Varchar(200) NULL


IF NOT EXISTS (SELECT * FROM syscolumns   WHERE id=Object_Id('dWorkShopDtlProcess')AND name='Pr_ParentSpecName')

    ALTER TABLE [dbo].[dWorkShopDtlProcess]   ADD [Pr_ParentSpecName] Varchar(200) NULL


IF NOT EXISTS (SELECT * FROM syscolumns WHERE id=Object_Id('cPdtBomTree')AND name='PLevel')

    ALTER TABLE [dbo].[cPdtBomTree] ADD [PLevel] Int NULL


IF NOT EXISTS (SELECT * FROM syscolumns   WHERE id=Object_Id('dWorkShopDtlProcess')AND name='PLevel')

    ALTER TABLE [dbo].[dWorkShopDtlProcess] ADD [PLevel] Int NULL


IF NOT EXISTS (SELECT *   FROM syscolumns   WHERE id=Object_Id('dWorkShopDtlProcess')AND name='ProcessID')

    ALTER TABLE [dbo].[dWorkShopDtlProcess]

    ADD [ProcessID] Int NULL


IF NOT EXISTS (SELECT *   FROM syscolumns   WHERE id=Object_Id('dWorkShopDtlProcess')AND name='pPrintNum')

    ALTER TABLE [dbo].[dWorkShopDtlProcess]   ADD [pPrintNum] Int NULL


IF NOT EXISTS (SELECT *   FROM syscolumns   WHERE id=Object_Id('dWorkShopDtlProcess')AND name='PdtClrName')

    ALTER TABLE [dbo].[dWorkShopDtlProcess] ADD [PdtClrName] Varchar(200) NULL


IF NOT EXISTS (SELECT * FROM syscolumns   WHERE id=Object_Id('dWorkShopDtlProcess')AND name='PdtSort')

    ALTER TABLE [dbo].[dWorkShopDtlProcess] ADD [PdtSort] Varchar(20) NULL


IF NOT EXISTS (SELECT * FROM syscolumns   WHERE id=Object_Id('dWorkShopDtlProcess')AND name='pPrintNum')

    ALTER TABLE [dbo].[dWorkShopDtlProcess]   ADD DEFAULT 0 FOR [pPrintNum]


---------------------------------------------我是分割線---------------------------------------------

--20190501工價設置加產品顏色

IF NOT EXISTS (SELECT *

               FROM syscolumns

               WHERE id=Object_Id('cPdtPiePrice')AND name='PdtPClrName')

BEGIN

    ALTER TABLE [dbo].[cPdtPiePrice]

    ADD [PdtPClrName] Varchar(200) DEFAULT '' NULL


    EXEC('UPDATE cPdtPiePrice

SET PdtUseCode=userCode, PdtSplName=pyShort, PdtPClrName=SwiftCode

FROM cDefine AS T

WHERE(T.relCode=mRelCode) ')

END


---------------------------------------------我是分割線---------------------------------------------

--20190723增加圖片字段

IF NOT EXISTS (SELECT * FROM syscolumns WHERE id=Object_Id('cDefine')AND name='Pic_url')

    ALTER TABLE [dbo].[cDefine] ADD [Pic_url] Varchar(500) NULL


IF NOT EXISTS (SELECT * FROM syscolumns WHERE id=Object_Id('cMtl')AND name='Pic_url')

    ALTER TABLE [dbo].[cMtl] ADD [Pic_url] Varchar(500) NULL


---------------------------------------------我是分割線---------------------------------------------

--20190726訂單顯示庫存改為函數   

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='f_PdtOdrStore' AND type='TF')

BEGIN

    EXEC('

CREATE   FUNCTION [dbo].[f_PdtOdrStore](

    @BillNo Varchar(100),

    @SerID Int,

    @PdtID Int,

    @IsBill Bit,

    @CtrSplrName Varchar(100),

    @PdtName Varchar(500),

    @PdtSort Varchar(20))

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 @PdtSort=''組合''

    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 AS A WITH(NOLOCK)

            JOIN dPdtOdrDtlDtl AS B WITH(NOLOCK)

              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) AS 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) AS 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 AS K WITH(NOLOCK)

                           JOIN dPdtOdr AS D WITH(NOLOCK)

                             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 AS K WITH(NOLOCK)

                           JOIN dPdtOdrDtl AS F WITH(NOLOCK)

                             ON K.mRelCode=F.mRelCode AND K.serID=F.serID

                           JOIN dPdtOdr AS D WITH(NOLOCK)

                             ON F.mRelCode=D.relCode

                           WHERE D.isValidity=1 AND K.Number * K.NumPackage-K.NumInOut>0) AS M

                      GROUP BY PdtName, PlacedStoreID) AS C

                   ON C.PlacedStoreID=A.PlacedStoreID AND C.PdtName=B.PdtName

            LEFT JOIN cCtrSplr AS M WITH(NOLOCK)

                   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 AS T1 WITH(NOLOCK)

            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 AS K WITH(NOLOCK)

                           JOIN dPdtOdr AS D WITH(NOLOCK)

                             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 AS K WITH(NOLOCK)

                           JOIN dPdtOdrDtl AS F WITH(NOLOCK)

                             ON K.mRelCode=F.mRelCode AND K.serID=F.serID

                           JOIN dPdtOdr AS D WITH(NOLOCK)

                             ON F.mRelCode=D.relCode

                           WHERE D.isValidity=1 AND K.Number * K.NumPackage-K.NumInOut>0) AS M

                      GROUP BY PdtName, PlacedStoreID) AS DH

                   ON DH.PdtName=T1.UniqueName AND DH.PlacedStoreID=T2.creDpt

            LEFT JOIN cCtrSplr AS C WITH(NOLOCK)

                   ON C.relCode=T2.creDpt

            WHERE [email protected] AND C.Name IS NOT NULL

            GROUP BY C.Name

        END

    END

    ELSE

    BEGIN

        INSERT @ReTable(StoreName, curStore, CtrStore, CtrOthrNum, KeStore, DHWC)

        SELECT StoreName=C.Name, curStore, CtrStore, CtrOthrNum=CtrOthrNum-CtrHSNum, DHWC,

            [KeStore]=IsNull(curStore, 0)-IsNull(CtrStore, 0)-IsNull(CtrOthrNum, 0)

                      +IsNull(CtrHSNum, 0)

        FROM cCtrSplr AS C WITH(NOLOCK)

        LEFT JOIN(SELECT creDpt, curStore=Sum(Number),

                      CtrStore=Sum(CASE WHEN CtrSplrName<>''本公司庫存'' THEN Number ELSE 0 END)

                  FROM dPdtStore WITH(NOLOCK)

                  WHERE [email protected]

                  GROUP BY creDpt) AS S

               ON C.relCode=S.creDpt

        LEFT JOIN(SELECT StoreID, CtrOthrNum=Sum(Number),

                      CtrHSNum=Sum(CASE WHEN CtrSplrName<>''本公司庫存'' THEN Number ELSE 0 END)

                  FROM dPdtStoreSale WITH(NOLOCK)

                  WHERE comMode=400 AND [email protected]

                  GROUP BY StoreID) AS T3

               ON C.relCode=T3.StoreID

        LEFT JOIN(SELECT PlacedStoreID, DHWC=Sum(DHWC)

                  FROM(SELECT PlacedStoreID, DHWC=T2.Number-T2.NumHpn

                       FROM dPdtOdrDtl AS T2 WITH(NOLOCK)

                       JOIN dPdtOdr AS T1 WITH(NOLOCK)

                         ON T1.relCode=T2.mRelCode AND T1.isValidity=1 AND T1.comMode=501

                       WHERE T2.Number-T2.NumHpn>0 AND [email protected]

                       UNION ALL

                       SELECT PlacedStoreID, DHWC=T3.Number * T3.NumPackage-T3.NumInOut

                       FROM dPdtOdrDtl AS T2 WITH(NOLOCK)

                       JOIN dPdtOdrDtlDtl AS T3 WITH(NOLOCK)

                         ON T2.mRelCode=T3.mRelCode AND T2.serID=T3.serID

                       JOIN dPdtOdr AS T1 WITH(NOLOCK)

                         ON T1.relCode=T2.mRelCode AND T1.isValidity=1 AND T1.comMode=501

                       WHERE T3.Number * T3.NumPackage-T3.NumInOut>0 AND [email protected]) AS M

                  GROUP BY M.PlacedStoreID) AS T5

               ON C.relCode=T5.PlacedStoreID

        WHERE C.comMode=122 AND dptKind=141 AND isNotUsing<>1 AND isUsed=1

    END

    RETURN

END ')

END

ELSE

BEGIN

    EXEC('

ALTER FUNCTION [dbo].[f_PdtOdrStore](

    @BillNo Varchar(100),

    @SerID Int,

    @PdtID Int,

    @IsBill Bit,

    @CtrSplrName Varchar(100),

    @PdtName Varchar(500),

    @PdtSort Varchar(20))

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 @PdtSort=''組合''

    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 AS A WITH(NOLOCK)

            JOIN dPdtOdrDtlDtl AS B WITH(NOLOCK)

              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) AS 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) AS 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 AS K WITH(NOLOCK)

                           JOIN dPdtOdr AS D WITH(NOLOCK)

                             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 AS K WITH(NOLOCK)

                           JOIN dPdtOdrDtl AS F WITH(NOLOCK)

                             ON K.mRelCode=F.mRelCode AND K.serID=F.serID

                           JOIN dPdtOdr AS D WITH(NOLOCK)

                             ON F.mRelCode=D.relCode

                           WHERE D.isValidity=1 AND K.Number * K.NumPackage-K.NumInOut>0) AS M

                      GROUP BY PdtName, PlacedStoreID) AS C

                   ON C.PlacedStoreID=A.PlacedStoreID AND C.PdtName=B.PdtName

            LEFT JOIN cCtrSplr AS M WITH(NOLOCK)

                   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 AS T1 WITH(NOLOCK)

            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 AS K WITH(NOLOCK)

                           JOIN dPdtOdr AS D WITH(NOLOCK)

                             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 AS K WITH(NOLOCK)

                           JOIN dPdtOdrDtl AS F WITH(NOLOCK)

                             ON K.mRelCode=F.mRelCode AND K.serID=F.serID

                           JOIN dPdtOdr AS D WITH(NOLOCK)

                             ON F.mRelCode=D.relCode

                           WHERE D.isValidity=1 AND K.Number * K.NumPackage-K.NumInOut>0) AS M

                      GROUP BY PdtName, PlacedStoreID) AS DH

                   ON DH.PdtName=T1.UniqueName AND DH.PlacedStoreID=T2.creDpt

            LEFT JOIN cCtrSplr AS C WITH(NOLOCK)

                   ON C.relCode=T2.creDpt

            WHERE [email protected] AND C.Name IS NOT NULL

            GROUP BY C.Name

        END

    END

    ELSE

    BEGIN

        INSERT @ReTable(StoreName, curStore, CtrStore, CtrOthrNum, KeStore, DHWC)

        SELECT StoreName=C.Name, curStore, CtrStore, CtrOthrNum=CtrOthrNum-CtrHSNum, DHWC,

            [KeStore]=IsNull(curStore, 0)-IsNull(CtrStore, 0)-IsNull(CtrOthrNum, 0)

                      +IsNull(CtrHSNum, 0)

        FROM cCtrSplr AS C WITH(NOLOCK)

        LEFT JOIN(SELECT creDpt, curStore=Sum(Number),

                      CtrStore=Sum(CASE WHEN CtrSplrName<>''本公司庫存'' THEN Number ELSE 0 END)

                  FROM dPdtStore WITH(NOLOCK)

                  WHERE [email protected]

                  GROUP BY creDpt) AS S

               ON C.relCode=S.creDpt

        LEFT JOIN(SELECT StoreID, CtrOthrNum=Sum(Number),

                      CtrHSNum=Sum(CASE WHEN CtrSplrName<>''本公司庫存'' THEN Number ELSE 0 END)

                  FROM dPdtStoreSale WITH(NOLOCK)

                  WHERE comMode=400 AND [email protected]

                  GROUP BY StoreID) AS T3

               ON C.relCode=T3.StoreID

        LEFT JOIN(SELECT PlacedStoreID, DHWC=Sum(DHWC)

                  FROM(SELECT PlacedStoreID, DHWC=T2.Number-T2.NumHpn

                       FROM dPdtOdrDtl AS T2 WITH(NOLOCK)

                       JOIN dPdtOdr AS T1 WITH(NOLOCK)

                         ON T1.relCode=T2.mRelCode AND T1.isValidity=1 AND T1.comMode=501

                       WHERE T2.Number-T2.NumHpn>0 AND [email protected]

                       UNION ALL

                       SELECT PlacedStoreID, DHWC=T3.Number * T3.NumPackage-T3.NumInOut

                       FROM dPdtOdrDtl AS T2 WITH(NOLOCK)

                       JOIN dPdtOdrDtlDtl AS T3 WITH(NOLOCK)

                         ON T2.mRelCode=T3.mRelCode AND T2.serID=T3.serID

                       JOIN dPdtOdr AS T1 WITH(NOLOCK)

                         ON T1.relCode=T2.mRelCode AND T1.isValidity=1 AND T1.comMode=501

                       WHERE T3.Number * T3.NumPackage-T3.NumInOut>0 AND [email protected]) AS M

                  GROUP BY M.PlacedStoreID) AS T5

               ON C.relCode=T5.PlacedStoreID

        WHERE C.comMode=122 AND dptKind=141 AND isNotUsing<>1 AND isUsed=1

    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 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]


    SET @[email protected][email protected]@error


    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]


    SET @[email protected][email protected]@error


    UPDATE dWorkShopDtl SET [email protected] WHERE [email protected] AND [email protected]


    SET @[email protected][email protected]@error


    UPDATE dWorkShopDtlPdt

    SET [email protected] * NumPackage

    WHERE [email protected] AND [email protected]


    SET @[email protected][email protected]@error


         

    IF   (@NumError<>0)

    BEGIN

             

        SET @tmpAllRelCode=''更新數據出錯,操作失敗!編號:''+Cast(@NumError AS VARCHAR(20))


        RAISERROR(@tmpAllRelCode, 16, 1);


        RETURN;

    END

END ')


---------------------------------------------我是分割線---------------------------------------------


--2020年盤點單合并

IF NOT EXISTS (SELECT * FROM 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 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 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

  ' )


---------------------------------------------我是分割線---------------------------------------------

--公司信息加長

ALTER TABLE [dbo].[cCtrSplr]

ALTER COLUMN [CtryName] Varchar(300) COLLATE Chinese_PRC_CI_AS


---------------------------------------------我是分割線---------------------------------------------

--20200612   固定資產生成憑證

--UPDATE dAccountRegDtl

--SET Field1=t.relCode

--FROM dFixedAssets t

--WHERE serID=t.ID AND mRelCode IN(SELECT relCode FROM dAccountReg WHERE ReferBillID='206')

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='FixedAssetsToAccountReg' AND type='P')

    EXEC('

CREATE   PROCEDURE   FixedAssetsToAccountReg

(

    @CreDpt     Int,      --部門編號

    @JTDate      DATETIME,   --計提日期

@Num         varchar(4), --號

    @OprMan    varchar(50), --操作人

    @CXBillNo    varchar(50)='''', --撤銷憑證單號

    @sMdl     int =1   --

)

AS

DECLARE

    @tmpRelCode   VARCHAR(20),

    @NumError     INT,

    @RelCode      INT,

    @tmpPdtName VARCHAR(200)

BEGIN

      SET XACT_ABORT ON

      BEGIN TRANSACTION

      SET @NumError =0

      IF @sMdl =1 --折舊生成憑證

      BEGIN

          SET @tmpRelCode = ''''

          EXEC GetAllBillNo 178, @tmpRelCode OUTPUT

          SET @NumError [email protected] + @@error

                 

              --建憑證主表

          INSERT INTO dAccountReg(comMode,creDpt,tDate,oprMan,

             isValidity,ChkInMan,isReferBill,UseTimes,UseStatus,relCode,

             allowDel,CtrSplrID,KdClnPay,MakeID,isRed,tMoney,

             ReferBillID,Word,Number)

           SELECT 151, creDpt, @JTDate, @OprMan, 1, @OprMan,0,0, Convert(VARCHAR(6), Cast(@JTDate AS DATETIME), 112),

             @tmpRelCode,0,0,3, @OprMan,0,Sum(MonthTotal),''206'', ''記'', @Num

           FROM dFixedAssets

           WHERE IsNull(QLBillNo, '''') ='''' AND IsNull(CurTotal, 0) > IsNull(NetValue, 0)

             AND IsNull(CurTotal, 0) >0 AND BeginDate < @JTDate

             AND creDpt [email protected]

           GROUP BY creDpt

           SET @NumError [email protected] + @@error

         

          --建憑證子表 借

           INSERT INTO dAccountRegDtl (comMode, mRelCode, serID, remark,

                relCode, Name, SubRelCode, SubName, Field1, Credit, Debit)

           SELECT 151, + @tmpRelCode, M.ID,

              M.relCode + M.Name + IsNull(M.SpecName,''''), P.relCode, P.Name,

              M.SubID, C.Name,   M.relCode, 0,

              MonthTotal =CASE WHEN IsNull(Total,0) -IsNull(OldTotal,0) >IsNull(MonthTotal,0)

                               THEN M.MonthTotal ELSE IsNull(Total,0) -IsNull(OldTotal,0) END

           FROM dFixedAssets M

             LEFT JOIN cAccountItem C ON M.SubID = C.relCode

             LEFT JOIN cAccountItem P ON Left(C.relCode, 3) =P.relCode AND IsNull(P.PrelCode, '''') =''''

           WHERE IsNull(QLBillNo, '''') ='''' AND BeginDate < @JTDate

             AND IsNull(CurTotal, 0) > IsNull(NetValue, 0) AND IsNull(CurTotal, 0) >0

             AND IsNull(Total,0) -IsNull(OldTotal,0) >0

             AND M.creDpt = @CreDpt

           ORDER BY M.ID

           SET @NumError [email protected] + @@error


           INSERT INTO dAccountRegDtl (comMode, mRelCode,serID,remark,

               relCode,Name,SubRelCode,SubName, Debit,Credit)

           SELECT 151, @tmpRelCode, Max(M.ID + 10000000),

              Remark =Cast(Year(@JTDate) AS VARCHAR(6)) + ''年'' + Cast(Month(@JTDate) AS VARCHAR(6))+''月累計折舊'',

             relCode=''039'', Name=''累計折舊'', C.relCode, C.Name, 0,

             Credit =Sum(CASE WHEN IsNull(Total,0) -IsNull(OldTotal,0) >IsNull(MonthTotal,0)

                              THEN M.MonthTotal ELSE IsNull(Total,0) -IsNull(OldTotal,0) END)

           FROM dFixedAssets M

             LEFT JOIN cAccountItem C ON (M.TypeName = C.Name) AND Left(C.relCode, 3) = ''039''   -- M.Name + ISNULL(M.SpecName, '''') = C.Name or

           WHERE IsNull(QLBillNo, '''') ='''' AND BeginDate < @JTDate

             AND IsNull(CurTotal, 0) > IsNull(NetValue, 0) AND IsNull(CurTotal, 0) >0

             AND IsNull(Total,0) -IsNull(OldTotal,0) >0

             AND M.creDpt = @CreDpt

           GROUP BY C.relCode, C.Name

          SET @NumError [email protected] + @@error

         

          --調整固定資產信息

          UPDATE dFixedAssets SET CalcMonth=IsNull(CalcMonth, 0) + 1,

               OldTotal = IsNull(OldTotal, 0) + IsNull(D.Debit, 0),

               CurTotal = CASE WHEN (IsNull(CurTotal, 0) -IsNull(D.Debit, 0) >0) AND (IsNull(Total,0) -IsNull(OldTotal,0) -IsNull(D.Debit, 0)>0)

                              THEN IsNull(CurTotal, 0)-IsNull(D.Debit, 0) ELSE 0 END

          FROM dFixedAssets M

            JOIN dAccountRegDtl D ON D.Field1 =M.relCode  

          WHERE M.comMode=206 AND D.mRelCode [email protected]

           AND M.creDpt = @CreDpt

           AND IsNull(D.Debit, 0) > 0 AND IsNull(CurTotal, 0) >0

          SET @NumError [email protected] + @@error       

         

          UPDATE dAccountRegDtl SET SubName = B.path

          FROM dbo.f_SubItem('''') AS B

          WHERE SubRelCode = B.RelCode AND dAccountRegDtl.mRelCode [email protected]

            AND IsNull(SubRelCode, '''') <> ''''


          UPDATE dAccountRegDtl SET Direction = T.Direction

          FROM (SELECT Name, Direction FROM cAccountItem WHERE IsNull(PrelCode, '''') ='''') T

          WHERE dAccountRegDtl.Name =T.Name AND mRelCode [email protected]

          INSERT INTO sOprLog(DptID, relCode, FuncID, FuncName, OprType, OprName)

          VALUES(@CreDpt, @tmpRelCode, 178, Cast(Month(@JTDate) AS VARCHAR(6))+''月固定資產-計提折舊核準'', ''自動核準憑證'',@OprMan)

       END ELSE

       BEGIN

            UPDATE dFixedAssets SET CalcMonth = IsNull(CalcMonth, 0) -1,

               OldTotal = CASE WHEN IsNull(OldTotal, 0) - IsNull(D.Debit, 0) >0 THEN IsNull(OldTotal, 0) - IsNull(D.Debit, 0) ELSE 0 END,

               CurTotal = IsNull(CurTotal, 0) + IsNull(D.Debit, 0)

            FROM dFixedAssets M

               JOIN dAccountRegDtl D ON D.Field1 =M.relCode   

            WHERE M.comMode=206

                AND [email protected]

                AND IsNull(D.Debit, 0) > 0

            SET @NumError [email protected] + @@error

                 

            DELETE dAccountReg WHERE [email protected]

            SET @NumError [email protected] + @@error

           

            INSERT INTO sOprLog(DptID, relCode, FuncID, FuncName, OprType, OprName)

            VALUES(@CreDpt, @CXBillNo, 178, ''撤消計提->刪除憑證(&D)...'', ''刪除憑證'',@OprMan)

       END

           

       IF (@NumError <> 0 )

       BEGIN

            ROLLBACK   TRANSACTION

            IF (IsNull(@tmpPdtName, '''') = '''')

               SET @tmpPdtName =''固定資產-計提折舊失敗!錯誤碼:'' + Cast(@NumError AS VARCHAR(20))

            RAISERROR(@tmpPdtName, 16 ,1);

            RETURN;

       END ELSE

       BEGIN

            COMMIT TRANSACTION

       END

END

'   )


---------------------------------------------我是分割線---------------------------------------------

--增加表鎖

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='sLock' AND type='U')

BEGIN

    CREATE TABLE [dbo].[sLock] (

    [ID]        [Int]         IDENTITY(1, 1) NOT NULL,

    [relCode]   [Varchar](20) NOT NULL,

    [OprType]   [Int]         NULL,

    [LockManID] [Int]         NULL,

    [LockMan]   [Varchar](20) NULL,

    [LockTime]   [DateTime]    NULL) ON [PRIMARY]


    ALTER TABLE [dbo].[sLock]

    ADD CONSTRAINT [DF_sLock_OprType] DEFAULT((0))FOR [OprType]


    ALTER TABLE [dbo].[sLock]

    ADD CONSTRAINT [DF_sLock_LockManID] DEFAULT((0))FOR [LockManID]


    ALTER TABLE [dbo].[sLock]

    ADD CONSTRAINT [DF_sLock_LockMan] DEFAULT('')FOR [LockMan]

END


---------------------------------------------我是分割線---------------------------------------------

--自定義顯示字段

IF NOT EXISTS (SELECT 1   FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='sSaveFieldChn' AND COLUMN_NAME='IsShow')

BEGIN

    ALTER TABLE [dbo].[sSaveFieldChn] ADD [IsShow] Bit DEFAULT 1 NULL


    EXEC(' UPDATE sSaveFieldChn SET IsShow =1 ')

END


--條碼表加送貨單號

IF NOT EXISTS (SELECT *

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE EXISTS (SELECT * FROM sysobjects WHERE name=TABLE_NAME AND type='U')

                 AND TABLE_NAME='dPdtStoreBar'

                        AND (COLUMN_NAME='TradeNo' OR COLUMN_NAME='TradeSerID'))

BEGIN

    ALTER TABLE [dbo].[dPdtStoreBar]   ADD [TradeNo] Varchar(30) DEFAULT '' NULL


    ALTER TABLE [dbo].[dPdtStoreBar]   ADD [TradeSerID] Int DEFAULT 0 NULL


    EXEC sp_addextendedproperty 'MS_Description', N'送貨', 'user', 'dbo', 'table', 'dPdtStoreBar',

        'column', 'TradeNo'


    EXEC('UPDATE dPdtStoreBar

SET TradeNo=T.OdrID, TradeSerID=T.OdrSubSerID

FROM dPdtStgDtl AS T

WHERE T.mRelCode=OutBillNo

  AND T.comMode=302')

END

-----------------------------------------------分割線--------------------------------------------------

--PdtBarOutStore存儲過程改:

/*

UPDATE dPdtStoreBar SET IsSaleOutStore =1, SaleOutStoreDate =getDate(),

          TradeNo [email protected], TradeSerID [email protected], OutBillNo [email protected]

WHERE BuyID [email protected]

*/

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='TradeDlrPdtBar' AND type='P')

    EXEC(N'CREATE   PROCEDURE   TradeDlrPdtBar

(

    @BillNo varchar(100),  

@SerID   varchar(20) =''''

)


AS


DECLARE

    @SELECT varchar(8000),

    @SELECT1 varchar(8000)


BEGIN

 

    SET @SELECT =''SELECT T1.CreateDate [條碼打印日期], T1.OdrID [計劃單號], SPINO [生產批號], ''

        + ''   T1.BuyID [條碼], T1.PdtCode [包件名稱], T1.PdtSpecName [規格], T1.PdtClrName [顏色], ''   

        + ''   T1.OutBillNo [出倉單號], T1.SaleOutStoreDate [出倉日期], InBillNo [進倉單號], ''

        + ''   BuyInStoreDate [進倉日期],   T1.TradeNo [送貨單號], T1.mRelCode [完成單號]''

        + '' FROM dPdtStoreBar T1 WITH (NOLOCK)   ''  

        + '' WHERE   T1.TradeNo ='''''' + @BillNo + '''''' AND T1.TradeSerID ='' [email protected]

        + ''    AND T1.IsSaleOutStore =1 ''  

       

    EXEC(@SELECT)

   

END')

-----------------------------------------------分割線--------------------------------------------------


--- 折率按分支客戶經營系列折率

ALTER TABLE [dbo].[cCtrSplr]

ALTER COLUMN [MoneyIni] Numeric(19, 4)


ALTER TABLE [dbo].[cCtrSplr]

ALTER COLUMN [MoneyCln] Numeric(19, 4)


ALTER TABLE [dbo].[cCtrSplr]

ALTER COLUMN [MoneyPay] Numeric(19, 4)


ALTER TABLE [dbo].[cCtrSplr]

ALTER COLUMN [CtryName] Varchar(300) COLLATE Chinese_PRC_CI_AS


ALTER TABLE cCtrSplrDtl

ALTER COLUMN aDiscount Numeric(18, 4) NULL

-----------------------------------------------分割線--------------------------------------------------

--材料調價單


DELETE FROM SFUNC WHERE RELCODE =70


INSERT INTO [SFUNC] ([relCode], [FuncGroup], [ViewStatus], [ViewOrder], [FKind], [FuncName], [IsShow], [TableName], [TableMode], [TFormName], [TFormMode], [ID], [SecNode], [PntStr], [SqlStr], [SqlListDtl], [WhereStr], [OrderByName], [TableNameDtl], [TableNameDtlDtl], [MasterSql], [DtlSql], [DtlDtlSql], [IsPrint], [PrintNum], [ItemNum], [CurID], [vDate], [BillSign], [DateFormat], [SerID], [SerMode], [ViewPrint], [CustomPrint], [alCount], [BaseFuncID], [BaseField], [FuncHelp], [FuncDemo], [BaseEditDate], [WhereStrOthr])

VALUES (70, 140, 0, 0, 2, N'材料調價單', 1, N'dMtlOdr', 408, N'TdMtlCostAdjForm', 408, 8, 105, N'單據編號', N'SELECT DptName [倉庫],   T1.relCode [單據編號], T1.tDate [日期],  

      T1.ChkInMan AS 核準, T1.AthrMan AS 審批,   handleOfOthr [經手人], tMoney [金額],  

      T1.cntDate AS 結算期, T1.memoText AS 備注

FROM   dMtlOdr T1 with(Nolock)

LEFT JOIN (SELECT RelCode, DptName=Name FROM cCtrSplr WHERE comMode =122) AS Dpt ON T1.creDpt =Dpt.RelCode', N' SELECT T1.relCode [單據編號], T1.tDate [日期], T1.tDate [日期],   MtlCode [編碼], MtlName [名稱],

       MtlSpec [規格],   MtlUnit [單位], Price [調前成本價], PriceOdr [調后成本價], NumberOdr [庫存數量],

       T2.RealPayMoney [調整金額], T2.memoText [備注]

FROM dMtlOdrDtl T2 JOIN dMtlOdr T1 ON T1.relCode = T2.mRelCode', NULL, N'Order by   T1.tDate Desc, T1.relCode Desc', N'dMtlOdrDtl', N'dMtlOdrDtlDtl', N'SELECT * FROM dMtlOdr', N'SELECT * FROM dMtlOdrDtl', N'SELECT * FROM dMtlOdrDtlDtl', 1, 1, 1000, 2, N'2008', N'MTP', N'YYMM', N'9', 1, 1, 1, 1, 11, N'MtlCode', NULL, N'直接打印(&P)...;報表預覽(&B);報表預覽(其它);打印進倉單(&S)...;定制報表A4(&A)...;定制報表(其它1);定制報表(其它2);定制報表(其它3);定制報表(其它4);單號條碼打印(&R)...;其它打印預覽(&T)', NULL, NULL)

DELETE FROM sFuncSelectData WHERE funcid =70

INSERT INTO [sFuncSelectData] ([comMode], [FuncID], [FuncName], [OrdID], [SelectEngName], [IsShow], [SelectChnName], [SelectSQL], [SelectToEditField], [sFuncID], [CDSTable], [IsSelectBill], [DateConditionStr], [CompConditionStr], [OrderByName], [SpcConditionStr], [SpcConditionField], [SelectCaption1], [SelectCaption2], [SelectToLocateField], [FillField], [BillFuncID])

VALUES (100, 70, N'材料調價單', 1, N'mlSelectMtl1', 1, N'材料庫存', N'SELECT T.MtlKind [材料類別], T.MtlCode [編碼], T.MtlName [名稱], T.MtlSpec [規格],

   T.MtlUnit [單位], NumCurStk [當前庫存], T.MtlUnName,

   T.MemoSplMtl, T.MemoForPdt, T.Price

FROM   dMtlStore T

WHERE T.comMode =300 AND NumCurStk <>0   %0:s', N'MtlKind/材料類別/MtlCode/編碼/MtlName/名稱/MtlSpec/規格/MtlUnit/單位/NumberOdr/當前庫存/MtlUnName/MtlUnName/MemoSplMtl/MemoSplMtl/Price/Price/', 0, N'名稱;編碼;規格', 0, N'', N'', N'', N'AND t.creDpt = %0:s', N'creDpt', N'', N'', N'', N'', 0)

-----------------------------------------------分割線--------------------------------------------------


---dMtlStore

IF EXISTS (SELECT 1   FROM sysconstraints   WHERE Object_Name(constid)='DF_dMtlStore_NumCurStk')

    ALTER TABLE dMtlStore DROP CONSTRAINT DF_dMtlStore_NumCurStk


ALTER TABLE dMtlStore ALTER COLUMN NumCurStk Float


ALTER TABLE dMtlStore ADD CONSTRAINT DF_dMtlStore_NumCurStk DEFAULT 0 FOR NumCurStk


IF EXISTS (SELECT 1 FROM sysconstraints WHERE Object_Name(constid)='DF_dMtlStore_Total')

    ALTER TABLE dMtlStore DROP CONSTRAINT DF_dMtlStore_Total


ALTER TABLE dMtlStore ALTER COLUMN Total Float


ALTER TABLE dMtlStore

ADD CONSTRAINT DF_dMtlStore_Total DEFAULT 0 FOR Total


-----------------------------------------------分割線--------------------------------------------------

--增加tAvgPrice

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS   WHERE TABLE_NAME='dMtlOdrDtl' AND COLUMN_NAME='tAvgPrice')

    ALTER TABLE dMtlOdrDtl ADD tAvgPrice Float DEFAULT 0


IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='dMtlOdrDtlDtl' AND COLUMN_NAME='tAvgPrice')

    ALTER TABLE dMtlOdrDtlDtl ADD tAvgPrice Float DEFAULT 0

-----------------------------------------------分割線--------------------------------------------------   

--dMtlOdrDtlDtl 改number為float

IF EXISTS (SELECT 1   FROM sysconstraints   WHERE Object_Name(constid)='DF_dMtlOdrDtlDtl_Number')

    ALTER TABLE dMtlOdrDtlDtl   DROP CONSTRAINT DF_dMtlOdrDtlDtl_Number


ALTER TABLE dMtlOdrDtlDtl ALTER COLUMN Number Float


ALTER TABLE dMtlOdrDtlDtl ADD CONSTRAINT DF_dMtlOdrDtlDtl_Number DEFAULT 0 FOR Number

-----------------------------------------------分割線--------------------------------------------------

--dMtlOdrDtl 改number為float

IF EXISTS (SELECT 1 FROM sysconstraints WHERE Object_Name(constid)='DF_dMtlOdrDtl_Number')

    ALTER TABLE dMtlOdrDtl DROP CONSTRAINT DF_dMtlOdrDtl_Number


ALTER TABLE dMtlOdrDtl ALTER COLUMN Number Float


ALTER TABLE dMtlOdrDtl ADD CONSTRAINT DF_dMtlOdrDtl_Number DEFAULT 0 FOR Number

-----------------------------------------------分割線--------------------------------------------------

--改dMtlOdrDtl number為float

IF EXISTS (SELECT 1 FROM sysconstraints WHERE Object_Name(constid)='DF_dMtlOdrDtl_PriceOdr')

    ALTER TABLE dMtlOdrDtl DROP CONSTRAINT DF_dMtlOdrDtl_PriceOdr


ALTER TABLE dMtlOdrDtl ALTER COLUMN PriceOdr Float


ALTER TABLE dMtlOdrDtl ADD CONSTRAINT DF_dMtlOdrDtl_PriceOdr DEFAULT 0 FOR PriceOdr

-----------------------------------------------分割線--------------------------------------------------

--dMtlOdrDtl 改Price 為float


IF EXISTS (SELECT 1 FROM sysconstraints WHERE Object_Name(constid)='DF_dMtlOdrDtl_Price')

    ALTER TABLE dMtlOdrDtl DROP CONSTRAINT DF_dMtlOdrDtl_Price


ALTER TABLE dMtlOdrDtl ALTER COLUMN Price Float


ALTER TABLE dMtlOdrDtl ADD CONSTRAINT DF_dMtlOdrDtl_Price DEFAULT 0 FOR Price



-----------------------------------------------分割線--------------------------------------------------


--MtlStore 材料庫存

EXEC('ALTER PROCEDURE [dbo].[MtlStore](

@aIsSplLook   Bit,

@StoreID     Varchar(1000),--倉庫編碼

@BeginDate   DateTime,--開始日期

@EndDate     DateTime,--結束日期

@IsDynamic   Int,--顯示模式(0、有動態   1、全庫存   2、無動態)

@IsZreo      Bit=1,--顯示大于零的庫存

@IsCostPrice Bit=0 --成本

)

AS

DECLARE @aBeginDate Varchar(20)

DECLARE @aEndDate Varchar(20)


BEGIN

    SET @aBeginDate=Convert(Varchar(10),@BeginDate,101)+'' 00:00:00''

    SET @aEndDate=Convert(Varchar(10),@EndDate,101)+'' 23:59:59''


    --要查詢的倉庫

    SELECT relCode,Name

    INTO #Lookstore

    FROM cCtrSplr WITH(NOLOCK)

    WHERE comMode=122 AND dptKind=140 AND CharIndex(Cast(relCode AS Varchar),@StoreID)>0


    --期初

    SELECT T3.creDpt,StoreName=C.Name,MtlKind=Max(T3.MtlKind),MtlCode=Max(T3.MtlCode),

        MtlName=Max(T3.MtlName),MtlSpec=Max(T3.MtlSpec),MtlUnit=Max(T3.MtlUnit),

        MemoSplMtl=Max(T3.MemoSplMtl),Number=T3.NumCurStk,T3.Price,T3.Total,qcNumber,

        qcTotal,cgInNumber=Sum(CASE WHEN T2.comMode=402 THEN T2.Number ELSE 0 END),

        cgInTotal=Sum(CASE WHEN T2.comMode=402 THEN T2.RealPayMoney ELSE 0 END),

        zzInNumber=Sum(CASE WHEN T2.FuncName=''材料組裝'' THEN T2.Number ELSE 0 END),

        zzInTotal=Sum(CASE WHEN T2.FuncName=''材料組裝'' THEN T2.RealPayMoney ELSE 0 END),

        qtInNumber=Sum(CASE WHEN T2.FuncName=''其他進倉'' THEN T2.Number ELSE 0 END),

        qtInTotal=Sum(CASE WHEN T2.FuncName=''其他進倉'' THEN T2.RealPayMoney ELSE 0 END),

        cjtlNumber=Sum(CASE WHEN T2.FuncName=''車間退料'' THEN T2.Number ELSE 0 END),

        cjtlTotal=Sum(CASE WHEN T2.FuncName=''車間退料'' THEN T2.RealPayMoney ELSE 0 END),

        pdInNumber=Sum(CASE WHEN T2.FuncName=''盤盈進倉'' THEN T2.Number ELSE 0 END),

        pdInTotal=Sum(CASE WHEN T2.FuncName=''盤盈進倉'' THEN T2.RealPayMoney ELSE 0 END),

        qcInNumber=Sum(CASE WHEN T2.FuncName=''期初進倉'' THEN T2.Number ELSE 0 END),

        qcInTotal=Sum(CASE WHEN T2.FuncName=''期初進倉'' THEN T2.RealPayMoney ELSE 0 END),

        dbInNumber=Sum(CASE WHEN T2.FuncName=''調撥進倉'' THEN T2.Number ELSE 0 END),

        dbInTotal=Sum(CASE WHEN T2.FuncName=''調撥進倉'' THEN T2.RealPayMoney ELSE 0 END),

        wwInNumber=Sum(CASE WHEN T2.comMode=202 THEN T2.Number ELSE 0 END),

        wwInTotal=Sum(CASE WHEN T2.comMode=202 THEN T2.RealPayMoney ELSE 0 END),

        InNumber=Sum(CASE WHEN T2.InOutType=1 THEN T2.InOutType*T2.Number ELSE 0 END),

        InTotal=Sum(CASE WHEN T2.InOutType=1 THEN T2.InOutType*T2.RealPayMoney ELSE 0 END),

        wwOutNumber=Sum(CASE WHEN T2.comMode=201 THEN T2.Number ELSE 0 END),

        wwOutTotal=Sum(CASE WHEN T2.comMode=201 THEN T2.RealPayMoney ELSE 0 END),

        cgthOutNumber=Sum(CASE WHEN T2.comMode=403 THEN T2.Number ELSE 0 END),

        cgthOutTotal=Sum(CASE WHEN T2.comMode=403 THEN T2.RealPayMoney ELSE 0 END),

        cjllOutNumber=Sum(CASE WHEN T2.FuncName=''車間領料'' THEN T2.Number ELSE 0 END),

        cjllOutTotal=Sum(CASE WHEN T2.FuncName=''車間領料'' THEN T2.RealPayMoney ELSE 0 END),

        zzOutNumber=Sum(CASE WHEN T2.FuncName=''組裝出倉'' THEN T2.Number ELSE 0 END),

        zzOutTotal=Sum(CASE WHEN T2.FuncName=''組裝出倉'' THEN T2.RealPayMoney ELSE 0 END),

        qtOutNumber=Sum(CASE WHEN T2.FuncName=''其他出倉'' THEN T2.Number ELSE 0 END),

        qtOutTotal=Sum(CASE WHEN T2.FuncName=''其他出倉'' THEN T2.RealPayMoney ELSE 0 END),

        pdOutNumber=Sum(CASE WHEN T2.FuncName=''盤虧出倉'' THEN T2.Number ELSE 0 END),

        pdOutTotal=Sum(CASE WHEN T2.FuncName=''盤虧出倉'' THEN T2.RealPayMoney ELSE 0 END),

        dbOutNumber=Sum(CASE WHEN T2.comMode=406 THEN T2.Number ELSE 0 END),

        dbOutTotal=Sum(CASE WHEN T2.comMode=406 THEN T2.RealPayMoney ELSE 0 END),

        OutNumber=Sum(CASE WHEN T2.InOutType=-1 THEN T2.Number ELSE 0 END),

        OutTotal=Sum(CASE WHEN T2.InOutType=-1 THEN T2.RealPayMoney ELSE 0 END),

        cbtjTotal=Sum(CASE WHEN T2.comMode=408 THEN T2.RealPayMoney ELSE 0 END),

        qmNumber=IsNull(qcNumber,0)+IsNull(Sum(T2.InOutType*T2.Number),0),

        qmTotal=IsNull(qcTotal,0)+IsNull(Sum(T2.InOutType*T2.RealPayMoney),0),T3.MtlUnName

    INTO #MtlInOut

    FROM dMtlStore T3

    JOIN #Lookstore AS C   ON T3.creDpt=C.relCode

    --期初

    LEFT JOIN(SELECT QC.creDpt,QC.MtlUnName,qcNumber=Sum(Number),qcTotal=Sum(QC.RealPayMoney)

              FROM(SELECT A.creDpt,B.MtlUnName,Number=B.Number*A.InOutType,

                       RealPayMoney=CASE WHEN A.InOutType=1 THEN B.RealPayMoney*A.InOutType

                                    ELSE tAvgPrice*Number*InOutType END

                   FROM dMtlOdr AS A WITH(NOLOCK)

                   JOIN #Lookstore AS C ON A.creDpt=C.relCode

                   JOIN dMtlOdrDtl AS B WITH(NOLOCK)

                     ON   (A.relCode=B.mRelCode AND A.comMode=B.comMode AND A.isValidity=1)

                   WHERE A.ChkInDate<@aBeginDate   AND A.InOutType<>0

                     AND A.comMode IN (201,402,403,405,406,408)

                   UNION ALL

                   SELECT A.creDpt,B.MtlUnName,Number=B.Number*-1,RealPayMoney=B.tAvgPrice*B.Number*-1

                   FROM dMtlOdr AS A WITH(NOLOCK)

                   JOIN #Lookstore AS C   ON A.creDpt=C.relCode

                   JOIN dMtlOdrDtlDtl AS B WITH(NOLOCK)

                     ON   (A.relCode=B.mRelCode AND A.comMode=B.comMode AND A.isValidity=1)

                   WHERE A.ChkInDate<@aBeginDate AND A.comMode=405 AND A.FuncName=''材料組裝''

                   UNION ALL

                   SELECT creDpt=A.DptOfOthr,B.MtlUnName,B.Number,RealPayMoney=B.tAvgPrice*B.Number

                   FROM dMtlOdr AS A WITH(NOLOCK)

                   JOIN #Lookstore AS C   ON A.DptOfOthr=C.relCode

                   JOIN dMtlOdrDtl AS B WITH(NOLOCK)

                     ON   (A.relCode=B.mRelCode AND A.comMode=B.comMode AND A.isValidity=1)

                   WHERE A.ChkInDate<@aBeginDate AND A.comMode=406

                   UNION ALL

                   SELECT A.creDpt,MtlUnName=B.MemoText,Number=B.NumberOdr,

                       RealPayMoney=IsNull(B.RealPayMoney,0)+IsNull(B.MoneyOdr,0)

                   FROM dMtlOdr AS A WITH(NOLOCK)

                   JOIN #Lookstore AS C ON A.creDpt=C.relCode

                   JOIN dMtlOdrDtl AS B WITH(NOLOCK)

                     ON   (A.relCode=B.mRelCode AND A.comMode=B.comMode AND A.isValidity=1)

                   WHERE A.ChkInDate<@aBeginDate   AND A.InOutType=1 AND A.comMode=202) AS QC

              GROUP BY QC.creDpt,QC.MtlUnName) AS T1

           ON T1.creDpt=T3.creDpt AND T1.MtlUnName=T3.MtlUnName

    --本期進出

    LEFT JOIN(SELECT A.comMode,A.creDpt,A.InOutType,A.FuncName,B.MtlUnName,B.Number,

                  RealPayMoney=CASE WHEN A.InOutType=1 THEN B.RealPayMoney ELSE B.tAvgPrice*B.Number END

              FROM dMtlOdr AS A WITH(NOLOCK)

              JOIN #Lookstore AS C   ON A.creDpt=C.relCode

              JOIN dMtlOdrDtl AS B WITH(NOLOCK)

                ON   (A.relCode=B.mRelCode AND A.comMode=B.comMode AND A.isValidity=1)

              WHERE (A.ChkInDate BETWEEN @aBeginDate AND @aEndDate)   AND A.InOutType<>0

                AND A.comMode IN (201,402,403,405,406,408)

              UNION ALL

              SELECT A.comMode,A.creDpt,InOutType=-1,FuncName=''組裝出倉'',B.MtlUnName,B.Number,

                  RealPayMoney=B.tAvgPrice*B.Number

              FROM dMtlOdr AS A WITH(NOLOCK)

              JOIN #Lookstore AS C ON A.creDpt=C.relCode

              JOIN dMtlOdrDtlDtl AS B WITH(NOLOCK)

                ON   (A.relCode=B.mRelCode AND A.comMode=B.comMode AND A.isValidity=1)

              WHERE (A.ChkInDate BETWEEN @aBeginDate AND @aEndDate)

                AND (A.comMode=405 AND A.FuncName=''材料組裝'')

              UNION ALL

              SELECT A.comMode,creDpt=A.DptOfOthr,InOutType=1,FuncName=''調撥進倉'',B.MtlUnName,

                  B.Number,RealPayMoney=B.tAvgPrice*B.Number

              FROM dMtlOdr AS A WITH(NOLOCK)

              JOIN #Lookstore AS C   ON A.DptOfOthr=C.relCode

              JOIN dMtlOdrDtl AS B WITH(NOLOCK)

                ON   (A.relCode=B.mRelCode AND A.comMode=B.comMode AND A.isValidity=1)

              WHERE (A.ChkInDate BETWEEN @aBeginDate AND @aEndDate) AND A.comMode=406

              UNION ALL

              SELECT A.comMode,A.creDpt,A.InOutType,A.FuncName,MtlUnName=B.MemoText,

                  Number=B.NumberOdr,RealPayMoney=IsNull(B.RealPayMoney,0)+IsNull(B.MoneyOdr,0)

              FROM dMtlOdr AS A WITH(NOLOCK)

              JOIN #Lookstore AS C   ON A.creDpt=C.relCode

              JOIN dMtlOdrDtl AS B WITH(NOLOCK)

                ON   (A.relCode=B.mRelCode AND A.comMode=B.comMode AND A.isValidity=1)   

              WHERE (A.ChkInDate BETWEEN @aBeginDate AND @aEndDate) AND A.InOutType=1 AND A.comMode=202

              ) AS T2   ON T2.MtlUnName=T3.MtlUnName AND T2.creDpt=T3.creDpt

    WHERE T3.comMode=300

    GROUP BY T3.creDpt,C.Name,T3.MtlUnName,T3.NumCurStk,T3.Price,T3.Total,T1.qcNumber,T1.qcTotal


    IF @IsZreo=0 --不看庫存<0的

        DELETE #MtlInOut WHERE IsNull(Number,0)<0


    IF @IsDynamic=0 --有動態

        DELETE #MtlInOut WHERE IsNull(InNumber,0)=0 AND IsNull(OutNumber,0)=0


    IF @IsDynamic=2 --無動態

        DELETE #MtlInOut WHERE IsNull(InNumber,0)<>0 OR IsNull(OutNumber,0)<>0

 

    IF @IsCostPrice=1

       

        SELECT A.StoreName 存放倉庫,A.MtlKind [材料信息|類別],A.MtlCode [材料信息|編碼],A.MtlName [材料信息|名稱],

            A.MtlSpec [材料信息|規格],A.MtlUnit [材料信息|單位],A.MemoSplMtl [材料信息|批號],

            A.Price [庫存|單價],A.Number [庫存|數量],A.Total [庫存|金額],

            A.qcNumber [期初|數量],A.qcTotal [期初|金額],

            --A.cbtjTotal [本期|成本調價],

            --A.cgInNumber [本期進倉|采購數量], A.cgInTotal [本期進倉|采購金額],

            --A.zzInNumber [本期進倉|組裝數量],A.zzInTotal [本期進倉|組裝金額],

            --A.qtInNumber [本期進倉|其他進倉數量],A.qtInTotal [本期進倉|其他進倉金額],

            --A.cjtlNumber [本期進倉|車間退料數量],A.cjtlTotal [本期進倉|車間退料金額],

            --A.pdInNumber [本期進倉|盤盈進倉數量],A.pdInTotal [本期進倉|盤盈進倉金額],

            --A.qcInNumber [本期進倉|期初進倉數量],A.qcInTotal [本期進倉|期初進倉金額],

            --A.dbInNumber [本期進倉|調撥進倉數量],A.dbInTotal [本期進倉|調撥進倉金額],

            --A.wwInNumber [本期進倉|委外返回數量],A.wwInTotal [本期進倉|委外返回金額],

            A.InNumber [本期進倉|數量合計],A.InTotal [本期進倉|金額合計],

            --A.wwOutNumber [本期出倉|委外數量], A.wwOutTotal [本期出倉|委外金額],

            --A.cgthOutNumber [本期出倉|采購退貨數量], A.cgthOutTotal [本期出倉|采購退貨金額],

            --A.cjllOutNumber [本期出倉|車間領料數量], A.cjllOutTotal [本期出倉|車間領料金額],

            --A.zzOutNumber [本期出倉|材料組裝數量],A.zzOutTotal [本期出倉|材料組裝金額],

            --A.qtOutNumber [本期出倉|其他出倉數量],A.qtOutTotal [本期出倉|其他出倉金額],

            --A.pdOutNumber [本期出倉|盤虧出倉數量], A.pdOutTotal [本期出倉|盤虧出倉金額],

            --A.dbOutNumber [本期出倉|調撥出倉數量],A.dbOutTotal [本期出倉|調撥出倉金額],

            A.OutNumber [本期出倉|數量合計],A.OutTotal [本期出倉|金額合計],

            A.qmNumber [期末|數量],A.qmTotal [期末|金額],

            B.MinStore 最小庫存,creDpt=A.creDpt,A.MtlUnName,

            MinStore=CASE WHEN B.MinStore>0 THEN A.Number-B.MinStore ELSE 0 END

        FROM #MtlInOut A

        LEFT JOIN cMtl B WITH(NOLOCK)

               ON A.MtlName=B.Name AND A.MtlSpec=B.MtlSpec AND A.MtlUnit=B.MtlUnit

        ORDER BY A.creDpt,A.MtlKind,A.MtlCode,A.MtlName   

       

    ELSE

       

        SELECT A.StoreName 存放倉庫,A.MtlKind [材料信息|類別],A.MtlCode [材料信息|編碼],A.MtlName [材料信息|名稱],

            A.MtlSpec [材料信息|規格],A.MtlUnit [材料信息|單位],A.MemoSplMtl [材料信息|批號],

            A.Number [庫存|數量],

            A.qcNumber [期初|數量],

            --A.cgInNumber [本期進倉|采購數量],   A.zzInNumber [本期進倉|組裝數量],

            --A.qtInNumber [本期進倉|其他進倉數量],A.cjtlNumber [本期進倉|車間退料數量],

            --A.pdInNumber [本期進倉|盤盈進倉數量],A.qcInNumber [本期進倉|期初進倉數量],

            --A.dbInNumber [本期進倉|調撥進倉數量],A.wwInNumber [本期進倉|委外返回數量],

            --A.InNumber [本期進倉|數量合計],A.wwOutNumber [本期出倉|委外數量],

            --A.cgthOutNumber [本期出倉|采購退貨數量],A.cjllOutNumber [本期出倉|車間領料數量],

            --A.zzOutNumber [本期出倉|材料組裝數量],A.qtOutNumber [本期出倉|其他出倉數量],

            --A.pdOutNumber [本期出倉|盤虧出倉數量],A.dbOutNumber [本期出倉|調撥出倉數量],

            A.OutNumber [本期出倉|數量合計], A.qmNumber [期末|數量],B.MinStore 最小庫存,creDpt=A.creDpt,

            A.MtlUnName,MinStore=CASE WHEN B.MinStore>0 THEN A.Number-B.MinStore ELSE 0 END

        FROM #MtlInOut A

        LEFT JOIN cMtl B WITH(NOLOCK)

               ON A.MtlName=B.Name AND A.MtlSpec=B.MtlSpec AND A.MtlUnit=B.MtlUnit

        ORDER BY A.creDpt,A.MtlKind,A.MtlCode,A.MtlName


    -- @aIsSplLook=1 按快捷鍵才可以查看

    DROP TABLE #Lookstore

    DROP TABLE #MtlInOut

END


')

-----------------------------------------------分割線--------------------------------------------------

--MtlStoreMainDtl

EXEC('ALTER PROCEDURE [dbo].[MtlStoreMainDtl](

@MtlUnName   Varchar(1000),--物料

@StoreID     Varchar(1000),--倉庫編碼

@BeginDate   DateTime,--開始日期

@EndDate     DateTime,--結束日期

@IsDynamic   Int=0,--顯示模式(0、材料庫存   1、外協庫存)

@IsCostPrice Bit=1 --成本

)

AS

BEGIN

DECLARE @aBeginDate DateTime

DECLARE @aEndDate DateTime

DECLARE @relCode Varchar(50)

DECLARE @jcNumber Float

DECLARE @jcPrice Float

DECLARE @jcTotal Float

DECLARE @serID Int


SET @aBeginDate=Convert(DateTime,Convert(Varchar(10),@BeginDate,121)+'' 00:00:00'')

SET @aEndDate=Convert(DateTime,Convert(Varchar(10),@EndDate,121)+'' 23:59:59'')


SELECT relCode,Name

INTO #Lookstore

FROM cCtrSplr WITH(NOLOCK)

WHERE((comMode=122 AND dptKind=140)OR(comMode=118))

AND CharIndex(Cast(relCode AS Varchar),@StoreID)>0


IF @IsDynamic=0 --材料庫存

BEGIN

    SELECT T.ChkInDate,T.relCode,T.creDpt,T.InOutType,T.FuncName,T.MtlUnName,T.Price,

        T.Number,T.RealPayMoney,T.ChkInMan,T.serID,UserPrice

    INTO #dMtlOdrTemp

    FROM(SELECT A.ChkInDate,A.relCode,A.creDpt,A.InOutType,A.FuncName,B.MtlUnName,

             Price=CASE WHEN A.InOutType=-1 THEN B.tAvgPrice ELSE B.Price END,B.Number,

             RealPayMoney=CASE WHEN A.InOutType=-1 THEN B.tAvgPrice*B.Number ELSE B.RealPayMoney END,

             A.ChkInMan,B.serID,UserPrice=B.Price

         FROM dMtlOdr AS A WITH(NOLOCK)

         JOIN #Lookstore AS C ON A.creDpt=C.relCode

         JOIN dMtlOdrDtl AS B WITH(NOLOCK)

           ON   (A.relCode=B.mRelCode AND A.comMode=B.comMode AND A.isValidity=1)

         WHERE A.ChkInDate<@aEndDate   AND A.InOutType<>0

           AND A.comMode IN (201,402,403,405,406)

           AND [email protected]

         UNION ALL

         SELECT A.ChkInDate,A.relCode,A.creDpt,InOutType=-1,FuncName=''組裝出倉'',B.MtlUnName,

             Price=tAvgPrice,B.Number,RealPayMoney=B.tAvgPrice*B.Number,A.ChkInMan,

             B.serID,UserPrice=B.Price

         FROM dMtlOdr AS A WITH(NOLOCK)

         JOIN #Lookstore AS C ON A.creDpt=C.relCode

         JOIN dMtlOdrDtlDtl AS B WITH(NOLOCK)

           ON   (A.relCode=B.mRelCode AND A.comMode=B.comMode AND A.isValidity=1)

         WHERE A.ChkInDate<@aEndDate

           AND (A.comMode=405   AND A.FuncName=''材料組裝'')

           AND [email protected]

         UNION ALL

         SELECT A.ChkInDate,A.relCode,creDpt=A.DptOfOthr,InOutType=1,FuncName=''調撥入倉'',

             B.MtlUnName,Price=B.tAvgPrice,B.Number,RealPayMoney=B.tAvgPrice*B.Number,

             A.ChkInMan,B.serID,UserPrice=B.Price

         FROM dMtlOdr AS A WITH(NOLOCK)

         JOIN #Lookstore AS C   ON A.DptOfOthr=C.relCode

         JOIN dMtlOdrDtl AS B WITH(NOLOCK)

           ON   (A.relCode=B.mRelCode AND A.comMode=B.comMode AND A.isValidity=1)

         WHERE A.ChkInDate<@aEndDate AND A.comMode=406 AND [email protected]

         UNION ALL

         SELECT A.ChkInDate,A.relCode,A.creDpt,InOutType,FuncName=''委外返回'',

             MtlUnName=B.MemoText,Price=(B.RealPayMoney+B.MoneyOdr)/NullIf(B.NumberOdr,0),

             Number=B.NumberOdr,RealPayMoney=IsNull(B.RealPayMoney,0)+IsNull(B.MoneyOdr,0),

             A.ChkInMan,B.serID,UserPrice=B.Price

         FROM dMtlOdr AS A WITH(NOLOCK)

         JOIN #Lookstore AS C   ON A.creDpt=C.relCode

         JOIN dMtlOdrDtl AS B WITH(NOLOCK)

           ON   (A.relCode=B.mRelCode AND A.comMode=B.comMode AND A.isValidity=1)

         WHERE A.ChkInDate<@aEndDate   AND A.comMode=202   AND A.InOutType=1

           AND [email protected]

         UNION ALL

         SELECT A.ChkInDate,A.relCode,A.creDpt,

             InOutType=CASE WHEN B.RealPayMoney>0 THEN 1 ELSE -1 END,FuncName=''成本調價'',

             B.MtlUnName,Price=Abs(B.PriceOdr),Number=0,RealPayMoney=Abs(B.RealPayMoney),

             A.ChkInMan,B.serID,UserPrice=B.Price

         FROM dMtlOdr AS A WITH(NOLOCK)

         JOIN #Lookstore AS C   ON A.creDpt=C.relCode

         JOIN dMtlOdrDtl AS B WITH(NOLOCK)

           ON   (A.relCode=B.mRelCode AND A.comMode=B.comMode AND A.isValidity=1)

         WHERE A.ChkInDate<@aEndDate AND A.comMode=408 AND [email protected]

         ) AS T


    --要返回的表

    CREATE TABLE #dMtlOdrEnd (

    relCode   Varchar(30),

    ChkInDate DateTime,

    FuncName   Varchar(50),

    ChkInMan   Varchar(50),

    MtlUnName Varchar(500),

    inNumber   Float,

    inPrice   Float,

    inTotal   Float,

    outNumber Float,

    outPrice   Float,

    outTotal   Float,

    jcNumber   Float,

    jcPrice   Float,

    jcTotal   Float,

    UserPrice Float,

    serid     Int)


    --插入期初數據

    INSERT INTO #dMtlOdrEnd(relCode,ChkInDate,FuncName,ChkInMan,MtlUnName,inNumber,

        inPrice,inTotal,outNumber,outPrice,outTotal,jcNumber,jcPrice,jcTotal,serid,

        UserPrice)

    SELECT relCode=''期初庫存'',[email protected],FuncName='''',ChkInMan='''',[email protected],inNumber=0,

        inPrice=Round(Sum(CASE WHEN InOutType=1 THEN RealPayMoney ELSE 0 END)/NullIf(Sum(CASE WHEN InOutType=1 THEN Number ELSE 0 END),0),6),

        inTotal=0,outNumber=0,

        outPrice=Round(Sum(CASE WHEN InOutType=-1 THEN RealPayMoney ELSE 0 END)/NullIf(Sum(CASE WHEN InOutType=-1 THEN Number ELSE 0 END),0),6),

        outTotal=0,jcNumber=Round(Sum(Number*InOutType),6),

        jcPrice=Round(IsNull(Sum(RealPayMoney*InOutType)/NullIf(Sum(Number*InOutType),0),

           Sum(CASE WHEN InOutType=1 THEN RealPayMoney ELSE 0 END)/NullIf(Sum(CASE WHEN InOutType=1 THEN Number ELSE 0 END),0)),6),

        jcTotal=Round(Sum(RealPayMoney*InOutType),6),

        serID=NULL,MAX(UserPrice)

    FROM #dMtlOdrTemp

    WHERE ChkInDate<@aBeginDate

   

    SET @jcNumber=0

    SET @jcPrice=0

    SET @jcTotal=0


    --準備循環插入明細數據,需要結存計算

    SELECT @jcNumber=jcNumber,@jcPrice=jcPrice,@jcTotal=jcTotal FROM #dMtlOdrEnd


    SET @jcNumber=IsNull(@jcNumber,0)

    SET @jcPrice=IsNull(@jcPrice,0)

    SET @jcTotal=IsNull(@jcTotal,0)


    DECLARE mycur CURSOR LOCAL

    FOR

    SELECT relCode,serID

    FROM #dMtlOdrTemp

    WHERE ChkInDate>[email protected]

    ORDER BY ChkInDate ASC


    OPEN mycur


    FETCH NEXT FROM mycur

    INTO @relCode,@serID


    WHILE @@fetch_Status=0

    BEGIN

        INSERT INTO #dMtlOdrEnd(relCode,ChkInDate,FuncName,ChkInMan,MtlUnName,inNumber,

            inPrice,inTotal,outNumber,outPrice,outTotal,jcNumber,jcPrice,jcTotal,serid,

            UserPrice)

        SELECT relCode,ChkInDate,FuncName,ChkInMan,MtlUnName,

            inNumber=Round(CASE WHEN InOutType=1 THEN Number ELSE 0 END,6),

            inPrice=Round(CASE WHEN InOutType=1 THEN Price ELSE 0 END,6),

            inTotal=Round(CASE WHEN InOutType=1 THEN RealPayMoney ELSE 0 END,6),

            outNumber=Round(CASE WHEN InOutType=-1 THEN Number ELSE 0 END,6),

            outPrice=Round(CASE WHEN InOutType=-1 THEN Price ELSE 0 END,6),

            outTotal=Round(CASE WHEN InOutType=-1 THEN RealPayMoney ELSE 0 END,6),

            jcNumber=Round(@jcNumber+Number*InOutType,6),

            jcPrice=Round(IsNull((@jcTotal+IsNull(RealPayMoney,0)*InOutType)/NullIf(@jcNumber+IsNull(Number,0)*InOutType,0),@jcPrice),6),

            jcTotal=Round(@jcTotal+IsNull(RealPayMoney,0)*InOutType,6),serID,UserPrice

        FROM #dMtlOdrTemp

        WHERE [email protected] AND [email protected]


        SELECT @jcNumber=jcNumber,@jcPrice=jcPrice,@jcTotal=jcTotal

        FROM #dMtlOdrEnd

        WHERE [email protected] AND [email protected]


        SET @jcNumber=IsNull(@jcNumber,0)

        SET @jcPrice=IsNull(@jcPrice,0)

        SET @jcTotal=IsNull(@jcTotal,0)


        FETCH NEXT FROM mycur

        INTO @relCode,@serID --逐條讀取   

    END


    CLOSE mycur

    DEALLOCATE mycur


    IF @IsCostPrice=1

        SELECT relCode [單據編號],ChkInDate [核準日期],FuncName [進出類別],ChkInMan [核準],

            MtlUnName [材料],inNumber [進倉|數量],inPrice [進倉|單價],inTotal [進倉|金額],

            outNumber [出倉|數量],outPrice [出倉|單價],outTotal [出倉|金額],jcNumber [結存|數量],

            jcPrice [結存|成本價],jcTotal [結存|金額],serid [序號SerID]

        FROM #dMtlOdrEnd

    ELSE

        SELECT relCode [單據編號],ChkInDate [核準日期],FuncName [進出類別],ChkInMan [核準],

            MtlUnName [材料],inNumber [進倉|數量],outNumber [出倉|數量],jcNumber [結存|數量],

            serid [序號SerID]

        FROM #dMtlOdrEnd


    DROP TABLE #dMtlOdrEnd

    DROP TABLE #dMtlOdrTemp

END

ELSE IF   @IsDynamic =1 --采購中

BEGIN

SELECT M.relCode [單據編號], [采購訂單]=''采購訂單'', M.tDate [單據日期], M.ChkInMan [核準],

    [采購量]=D.Number, [已到貨量]=D.NumHpn, [采購中]=D.Number-D.NumHpn

FROM dMtlOdrDtl D

JOIN dMtlOdr M ON   (D.mRelCode=M.relCode)

JOIN #Lookstore C ON C.relCode=M.creDpt

WHERE(IsNull(M.AthrMan, '''')<>'''')

AND M.comMode=401

AND (D.Number>D.NumHpn)

AND [email protected]

END

ELSE IF @IsDynamic =2 --未出倉需求

BEGIN

    SELECT M.relCode [單據編號], [生產計劃單]=''生產計劃單'', M.tDate [單據日期], M.ChkInMan [核準],

        [總需求量]=D.NumSum,[已出倉量]=D.NumOut, [未出倉需求]=D.NumSum-D.NumOut

    FROM dWorkShopDtlDtl D

    JOIN dWorkShop M   ON   (D.mRelCode=M.relCode)

    JOIN #Lookstore C ON C.relCode=OutStoreID

    WHERE(IsNull(M.AthrMan, '''')<>'''')

     AND M.comMode=701

     AND (D.NumSum>D.NumOut)

     AND [email protected]

END

ELSE --委外庫存

BEGIN

    SET @jcNumber=0

    SELECT T.relCode,T.ChkInDate,T.creDpt,T.InOutType,T.FuncName,T.MtlUnName,

        T.Number,T.ChkInMan,T.serID

    INTO #dMtlOdrTempWW

    FROM(SELECT A.relCode,A.ChkInDate,creDpt=A.DptOfOthr,

             InOutType=CASE A.comMode WHEN 201 THEN 1 ELSE -1 END,A.FuncName,B.MtlUnName,

              B.Number,A.ChkInMan,B.serID

         FROM dMtlOdr AS A WITH(NOLOCK)

         JOIN #Lookstore AS C ON A.DptOfOthr=C.relCode

         JOIN dMtlOdrDtl AS B WITH(NOLOCK)

           ON   (A.relCode=B.mRelCode AND A.comMode=B.comMode AND A.isValidity=1)AND B.MtlType=0

         WHERE A.ChkInDate<@aEndDate AND A.comMode IN (201,202)AND [email protected]

         UNION ALL  

         SELECT A.relCode,A.ChkInDate,creDpt=A.DptOfOthr,InOutType=CASE A.comMode WHEN 201 THEN 1 ELSE -1 END,

             A.FuncName,MtlUnName,B.Number, A.ChkInMan,B.serID

         FROM dMtlOdr AS A WITH(NOLOCK)

         JOIN #Lookstore AS C ON A.DptOfOthr=C.relCode

         JOIN dMtlOdrDtlDtl AS B WITH(NOLOCK)

           ON   (A.relCode=B.mRelCode AND A.comMode=B.comMode AND A.isValidity=1)

         WHERE A.ChkInDate<@aEndDate AND A.comMode IN (201,202) AND [email protected]  

        ) AS T

        ORDER BY T.ChkInDate ASC,T.InOutType DESC


    --要返回的表

    CREATE TABLE #dMtlOdrEndWW (

    relCode   Varchar(30),

    ChkInDate DateTime,

    creDpt    Int,

    FuncName   Varchar(50),

    ChkInMan   Varchar(50),

    MtlUnName Varchar(500),

    inNumber   Float,

    outNumber Float,

    jcNumber   Float,

    serid     Int)


    --插入期初數據

    INSERT INTO #dMtlOdrEndWW(creDpt,relCode,ChkInDate,FuncName,ChkInMan,MtlUnName,

        inNumber,outNumber,jcNumber,serid)

    SELECT creDpt=Max(creDpt),relCode=''期初庫存'',[email protected],FuncName='''',ChkInMan='''',

        [email protected],inNumber=0, outNumber=0,jcNumber=Sum(Number*InOutType),serID=NULL

    FROM #dMtlOdrTempWW

    WHERE ChkInDate<@aBeginDate


    --準備循環插入明細數據,需要結存計算

    SELECT @jcNumber=jcNumber FROM #dMtlOdrEndWW


    SET @jcNumber=IsNull(@jcNumber,0)

   

    DECLARE mycur CURSOR LOCAL

    FOR

    SELECT relCode,serID

    FROM #dMtlOdrTempWW

    WHERE ChkInDate>[email protected]

    ORDER BY ChkInDate ASC


    OPEN mycur


    FETCH NEXT FROM mycur

    INTO @relCode,@serID


    WHILE @@fetch_Status=0

    BEGIN

        INSERT INTO #dMtlOdrEndWW(creDpt,relCode,ChkInDate,FuncName,ChkInMan,MtlUnName,

            inNumber, outNumber, jcNumber , serid)

        SELECT creDpt,relCode,Convert(Varchar(12),ChkInDate,23),FuncName,ChkInMan,

            MtlUnName,inNumber=CASE WHEN InOutType=1 THEN Number ELSE 0 END,

            outNumber=CASE WHEN InOutType=-1 THEN Number ELSE 0 END,

            [email protected]+Number*InOutType,   serID

        FROM #dMtlOdrTempWW

        WHERE ChkInDate>[email protected] AND [email protected] AND [email protected]


        SELECT @jcNumber=jcNumber

        FROM #dMtlOdrEndWW

        WHERE ChkInDate>[email protected] AND [email protected] AND [email protected]


        SET @jcNumber=IsNull(@jcNumber,0)

        SET @jcPrice=IsNull(@jcPrice,0)

        SET @jcTotal=IsNull(@jcTotal,0)


        FETCH NEXT FROM mycur

        INTO @relCode,@serID --逐條讀取   

    END


    CLOSE mycur

    DEALLOCATE mycur


SELECT B.Name [供應商], A.relCode [單據編號], A.ChkInDate [核準日期], A.FuncName [進出類別], A.ChkInMan [核準],

A.MtlUnName [材料], A.inNumber [進倉|數量], A.outNumber [出倉|數量], A.jcNumber [結存|數量],

A.serid [序號SerID]

FROM #dMtlOdrEndWW A

LEFT JOIN cCtrSplr B WITH(NOLOCK)   ON A.creDpt=B.relCode AND B.comMode=118

       


    DROP TABLE #dMtlOdrTempWW

    DROP TABLE #dMtlOdrEndWW

END


DROP TABLE #Lookstore

END


')

-----------------------------------------------分割線--------------------------------------------------

--AdjustByMtlStg

EXEC('ALTER PROCEDURE [dbo].[AdjustByMtlStg](

@sComMode Int,--表模式

@sRelCode Varchar(20),--單據號

@sMdl Int --加減系數

)

AS

DECLARE @MtlName Varchar(500)

DECLARE @NumError Int


BEGIN

    SET XACT_ABORT ON


    BEGIN TRANSACTION


    SET @NumError=0


    DECLARE @creDpt Int,@FuncName Varchar(50),@InOutType Int,@DptOfOthr Int,@tDate DateTime


    SELECT @creDpt=creDpt,@FuncName=FuncName,@InOutType=InOutType,@DptOfOthr=DptOfOthr,

        @tDate=tDate

    FROM dMtlOdr

    WHERE [email protected] AND [email protected]


    UPDATE dMtlOdrDtlDtl

    SET Price=Round(Price,6),RealPayMoney=Round(RealPayMoney,6)

    WHERE [email protected] AND [email protected]

   

    SET @[email protected][email protected]@error


    UPDATE dMtlOdrDtl

    SET Price=Round(Price,6),PriceOdr=Round(PriceOdr,6),

        RealPayMoney=Round(RealPayMoney,6),MoneyOdr=Round(MoneyOdr,6)

    WHERE [email protected] AND [email protected]

   

    SET @[email protected][email protected]@error

   

IF @sComMode=402

--回填最新采購價

BEGIN

UPDATE cMtl SET BuyPrice=T.Price

FROM dMtlOdrDtl T

WHERE [email protected]

  AND [email protected]

  AND T.MtlName=cMtl.Name

  AND T.MtlSpec=cMtl.MtlSpec

  AND T.MtlUnit=cMtl.MtlUnit

END


    --將出倉的單價和委外的單價設置為庫存單價

    IF @FuncName=''材料組裝'' AND @sComMode=405

    BEGIN

        UPDATE dMtlOdrDtlDtl

        SET tAvgPrice=Round(S.Price,6),

            Price=Round(CASE WHEN dMtlOdrDtlDtl.Price=0 THEN S.Price ELSE dMtlOdrDtlDtl.Price END,6),

            RealPayMoney=Round(CASE WHEN dMtlOdrDtlDtl.Price=0 THEN S.Price*Number   ELSE RealPayMoney END,6),

            memoText=IsNull(memoText,'''')

                     +CASE WHEN dMtlOdrDtlDtl.Price=0 THEN ''原價格為0,更新為庫存單價'' ELSE '''' END

        FROM dMtlStore S

        WHERE([email protected] AND @sComMode=405)

         AND (S.comMode=300 AND [email protected])

         AND dMtlOdrDtlDtl.MtlUnName=S.MtlUnName


        SET @[email protected][email protected]@error

    END


    --回填出倉價格

    IF   (@InOutType=-1 AND @sComMode IN (201,403,405,406))

    BEGIN

        UPDATE dMtlOdrDtl

        SET tAvgPrice=Round(S.Price,6),

            Price=Round(CASE WHEN dMtlOdrDtl.Price=0 THEN S.Price ELSE dMtlOdrDtl.Price END,6),

            RealPayMoney=Round(CASE WHEN dMtlOdrDtl.Price=0 THEN S.Price*Number ELSE RealPayMoney END,6),

            FHMemo=IsNull(FHMemo,'''')   +CASE WHEN dMtlOdrDtl.Price=0 THEN ''原價格為0,更新為庫存單價'' ELSE '''' END

        FROM dMtlStore S

        WHERE [email protected]

          AND S.comMode=300

          AND [email protected]

          AND dMtlOdrDtl.MtlUnName=S.MtlUnName


        SET @[email protected][email protected]@error

    END


    --1,如果庫存表沒有插入材料庫存表里面

    INSERT INTO dMtlStore(comMode,creDpt,MtlUnName,MtlKind,MtlCode,MtlName,MtlSpec,

        MemoSplMtl,MtlUnit,MemoForPdt,NumCurStk,LastInStoreDate,Price,Total)

    SELECT D.comMode,D.creDpt,D.MtlUnName,Max(D.MtlKind),Max(D.MtlCode),Max(D.MtlName),

        Max(D.MtlSpec),Max(D.MemoSplMtl),Max(D.MtlUnit),'''',0,@tDate,0,0

    FROM( --材料庫存影響

        SELECT comMode=300,[email protected],MtlUnName,MtlKind,MtlCode,MtlName,MtlSpec,

            MtlUnit,MemoSplMtl

        FROM dMtlOdrDtl

        WHERE [email protected] AND comMode IN (201,402,403,405,406,408)AND @InOutType<>0

        UNION ALL

        SELECT comMode=300,[email protected],MtlUnName,MtlKind,MtlCode,MtlName,MtlSpec,

            MtlUnit,MemoSplMtl

        FROM dMtlOdrDtlDtl

        WHERE [email protected] AND @FuncName=''材料組裝'' AND comMode=405

        UNION ALL

        SELECT comMode=300,[email protected],MtlUnName,MtlKind,MtlCode,MtlName,MtlSpec,

            MtlUnit,MemoSplMtl

        FROM dMtlOdrDtl

        WHERE [email protected] AND comMode=406

        UNION ALL

        SELECT comMode=300,[email protected],MtlUnName=MemoText,MtlKind,MtlCode,

            MtlName=MtlNameOfOdr,MtlSpec=MemoSplMtl,MtlUnit=UnitOdr,MemoSplMtl

        FROM dMtlOdrDtl

        WHERE [email protected] AND comMode=202 AND @InOutType=1

        UNION ALL

        --委外庫存影響

        SELECT comMode=200,[email protected],B.MtlUnName,B.MtlKind,B.MtlCode,B.MtlName,

            B.MtlSpec,B.MtlUnit,B.MemoSplMtl

        FROM dMtlOdrDtl B

        WHERE [email protected] AND comMode IN (201,202) --不能把組合帶到庫存去

           AND NOT EXISTS(SELECT 1 FROM dMtlOdrDtlDtl C WITH(NOLOCK) WHERE C.mRelCode=B.mRelCode AND C.serID=B.serID)

        UNION ALL

        SELECT comMode=200,[email protected],MtlUnName,MtlKind,MtlCode,MtlName,MtlSpec,

            MtlUnit,MemoSplMtl

        FROM dMtlOdrDtlDtl

        WHERE [email protected] AND comMode=202   

        ) AS D

    WHERE NOT EXISTS (SELECT S.MtlUnName FROM dMtlStore AS S

                      WHERE S.comMode=D.comMode AND S.creDpt=D.creDpt   AND S.MtlUnName=D.MtlUnName)

    GROUP BY D.comMode,D.creDpt,D.MtlUnName


    SET @[email protected][email protected]@error


    --1調價單直接改價格

    IF @sComMode=408

    BEGIN

        UPDATE dMtlStore

        SET Price=Abs(D.PriceOdr),Total=Abs(D.PriceOdr)*NumCurStk,[email protected]

        FROM dMtlOdrDtl D

        WHERE [email protected]

          AND D.comMode=408

          AND [email protected]

          AND dMtlStore.comMode=300


        SET @[email protected][email protected]@error

    END

    ELSE

    BEGIN

        --2,更新材料庫存,改均價

UPDATE dMtlStore

SET NumCurStk=Round(NumCurStk+S.Number*@sMdl,6),[email protected],

Total=Round(Total+S.Total1*@sMdl,6),

Price=Round(Abs(IsNull(((Total+Total1*@sMdl))/NullIf((NumCurStk+S.Number*@sMdl),0),Price)),6)

FROM(SELECT T.creDpt,T.MtlUnName,Number=Round(Sum(T.Number),6),Total1=Round(Sum(T.RealPayMoney),6)

FROM( --所有入庫影響

SELECT [email protected],MtlUnName,Number,RealPayMoney

FROM dMtlOdrDtl

WHERE [email protected] AND comMode IN (402,405)AND @InOutType=1

UNION ALL

--調撥進倉換倉庫

SELECT [email protected],MtlUnName,Number,RealPayMoney=tAvgPrice*Number

FROM dMtlOdrDtl

WHERE [email protected] AND comMode=406

UNION ALL

--委外返回用新名字MemoText

SELECT [email protected],MtlUnName=MemoText,Number=NumberOdr,

RealPayMoney=IsNull(RealPayMoney,0)+IsNull(MoneyOdr,0)

FROM dMtlOdrDtl

WHERE [email protected] AND comMode=202 AND @InOutType=1) AS T

GROUP BY T.creDpt,T.MtlUnName) AS S

WHERE comMode=300 AND S.creDpt=dMtlStore.creDpt AND S.MtlUnName=dMtlStore.MtlUnName


        SET @[email protected][email protected]@error


        --3,更新庫存表,出倉和委外,不改單價

        UPDATE dMtlStore

        SET NumCurStk=Round(NumCurStk+S.Number*@sMdl,6),

            Total=Round(Total+S.Number*Price*@sMdl,6)

        FROM(SELECT T.comMode,T.creDpt,T.MtlUnName,Number=Sum(T.Number*T.InOutType)

             FROM( --所有出庫(201,403,405,406)

                 SELECT comMode=300,InOutType=-1,[email protected],MtlUnName,Number

                 FROM dMtlOdrDtl

                 WHERE [email protected] AND @InOutType=-1 AND comMode IN (201,403,405,406)

                 UNION ALL

                 --材料組裝明細出庫

                 SELECT comMode=300,InOutType=-1,[email protected],MtlUnName,Number

                 FROM dMtlOdrDtlDtl

                 WHERE [email protected] AND (@FuncName=''材料組裝'' AND comMode=405)

                 UNION ALL

                 --委外進銷存

                 SELECT comMode=200,InOutType=CASE WHEN comMode=201 THEN 1 ELSE -1 END,

                     [email protected],MtlUnName,Number

                 FROM dMtlOdrDtl B

                 WHERE [email protected] AND comMode IN (201,202)

                 AND NOT EXISTS(SELECT 1 FROM dMtlOdrDtlDtl C WITH(NOLOCK) WHERE C.mRelCode=B.mRelCode AND C.serID=B.serID)

                 UNION ALL

                 SELECT comMode=200,InOutType=-1,[email protected],MtlUnName,Number

                 FROM dMtlOdrDtlDtl

                 WHERE [email protected] AND comMode=202) AS T

             GROUP BY T.comMode,T.creDpt,T.MtlUnName) AS S

        WHERE dMtlStore.comMode=S.comMode

          AND dMtlStore.creDpt=S.creDpt

          AND dMtlStore.MtlUnName=S.MtlUnName


        SET @[email protected][email protected]@error

    END


    --/*

IF EXISTS (SELECT 1

   FROM dMtlStore S

   WHERE S.NumCurStk<0 AND S.creDpt IN (@creDpt,@DptOfOthr))

BEGIN

SET @MtlName=''''


SELECT @[email protected]+''【''+S.MtlUnName+''】庫存為:(''

+Cast(IsNull(S.NumCurStk,0) AS Varchar)+'')''+Char(13)

FROM dMtlStore S

WHERE S.NumCurStk<0 AND S.creDpt IN (@creDpt,@DptOfOthr)


SET @MtlName=IsNull(@MtlName,'''')

SET @[email protected]+''當前庫存不足,操作失敗!''


ROLLBACK TRANSACTION


RAISERROR(@MtlName,16,1);


--PRINT @sRelCode+'',''[email protected]+'':''+ @MtlName

RETURN;

END


    --*/

    IF   (@NumError<>0)

    BEGIN

        ROLLBACK TRANSACTION


        SET @MtlName=''更新數據出錯,操作失敗!編號:''+Cast(@NumError AS Varchar(20))


        RAISERROR(@MtlName,16,1);


        RETURN;

    END

    ELSE

    BEGIN

        COMMIT TRANSACTION

    END

END ')

-----------------------------------------------分割線--------------------------------------------------

--委外加工添加核準日期dMtlOdrHpnToWWStore

EXEC(' ALTER PROCEDURE dMtlOdrHpnToWWStore(

@sComMode Int,--表模式

@sRelCode Varchar(20),--單據號

@sFunc Int=0)

AS

DECLARE @tmpMaxRelCode Varchar(20),@wwSprCode Int,@tmpWWSplrName Varchar(100),@NumError Int


BEGIN

    IF EXISTS (SELECT comMode

               FROM dMtlOdrDtl

               WHERE [email protected] AND IsNull(OdrSubSerID,0)<>0)

    BEGIN

        SET XACT_ABORT ON


        BEGIN TRANSACTION


        SET @NumError=0


        IF   (@sFunc=1)

        BEGIN

            DECLARE #tmpBuyToWWSprName CURSOR

            FOR

            SELECT DISTINCT M.OdrSubSerID,C.Name

            FROM dMtlOdrDtl M

            LEFT JOIN(SELECT relCode,Name FROM cCtrSplr WHERE comMode=118) AS C

                   ON M.OdrSubSerID=C.relCode

            WHERE(M.comMode=402)AND [email protected] AND IsNull(M.OdrSubSerID,0)<>0


            OPEN #tmpBuyToWWSprName


            FETCH NEXT FROM #tmpBuyToWWSprName

            INTO @wwSprCode,@tmpWWSplrName


            WHILE @@fetch_Status=0

            BEGIN

                EXEC GetAllBillNo 173,@tmpMaxRelCode OUTPUT,2


                INSERT INTO dMtlOdr(comMode,creDpt,relCode,ChkInMan,isValidity,tDate,

                    OprMan,DptOfOthr,SplrName,InOutType,FuncName,CodeOfMtlPlan,tMoney,

                    BillFuncID,OprDate,ChkInDate)

                SELECT 201,creDpt,@tmpMaxRelCode,ChkInMan,isValidity,tDate,ChkInMan,

                    @wwSprCode,--CONVERT(varchar(10),GETDATE(),101)

                    @tmpWWSplrName,-1,''倉庫發外'',relCode,tMoney,0,OprDate,GetDate()

                FROM dMtlOdr

                WHERE [email protected] AND comMode=402


                SET @[email protected][email protected]@error


                INSERT INTO dMtlOdrDtl(ID,comMode,mRelCode,serID,MtlUnName,MtlKind,

                    MtlCode,MtlName,MtlSpec,MemoSplMtl,RealPayMoney,MtlUnit,Number,

                    Price,OdrID,OdrSerID,MemoText)

                SELECT ID,201,@tmpMaxRelCode,serID,MtlUnName,MtlKind,MtlCode,MtlName,

                    MtlSpec,MemoSplMtl,RealPayMoney,MtlUnit,Number,Price,mRelCode,serID,

                    MemoText

                FROM dMtlOdrDtl

                WHERE [email protected] AND comMode=402 AND [email protected]


                SET @[email protected][email protected]@error


                INSERT INTO sOprLog(DptID,relCode,FuncID,FuncName,OprType,OprName)

                SELECT 100,@tmpMaxRelCode,100,''采購進倉'',@sRelCode,OprMan

                FROM dMtlOdr

                WHERE [email protected] AND comMode=402


                INSERT INTO sOprLog(DptID,relCode,FuncID,FuncName,OprType,OprName)

                SELECT 100,@sRelCode,100,''委外加工'',@tmpMaxRelCode,OprMan

                FROM dMtlOdr

                WHERE [email protected] AND comMode=402


                EXEC AdjustByMtlStg 201,@tmpMaxRelCode,1


                FETCH NEXT FROM #tmpBuyToWWSprName

                INTO @wwSprCode,@tmpWWSplrName

            END


            CLOSE #tmpBuyToWWSprName

            DEALLOCATE #tmpBuyToWWSprName

        END

         


        IF   (@NumError<>0)

        BEGIN

            ROLLBACK TRANSACTION


            RAISERROR(''生成外協加工有誤,操作失敗!'',16,1);


            RETURN;

        END

        ELSE

        BEGIN

            COMMIT TRANSACTION

        END

    END

END

')

-----------------------------------------------分割線--------------------------------------------------

--結算

EXEC('

ALTER   PROCEDURE   AdjustByMtlStgCnt

(

    @sCntDate   Int,      --當前結算月份

    @sPreCntDate   Int,      --上期結算月份

    @sBeginDate   DateTime, --結算開始日期

@sEndDate     DateTime   --結算結束日期

)

--移動加權平均價,每月計算

AS


DECLARE

    @SELECT1   NVarchar(4000),

    @SELECT2 NVarchar(4000),

    @SELECT3 NVarchar(4000),

    @aPreDate   Varchar(30),

    @aBeginDate   Varchar(30),

@aEndDate     Varchar(30),

@ErrorName    Varchar(50),

    @NumError      Int

BEGIN

    BEGIN TRANSACTION

    SET @NumError =0

    --SET @aBeginDate =   + CONVERT(varchar(10), @sBeginDate, 101)   00:00:00

    --SET @aEndDate   =   + CONVERT(varchar(10), @sEndDate, 101)   23:59:59

    --SET @aPreDate   =   + CONVERT(varchar(10), @sBeginDate-1, 101)   00:00:00

    --移動加權平均法計算公式是: 移動平均單價=(本次進貨的成本+原有庫存的成本)/(本次進貨數量+原有存貨數量)

    --本批發出存貨成本=本批發出存貨數量×存貨當前移動平均單價

   

    INSERT INTO dMtlStoreCnt(creDpt, MtlUnName, CntDate, BeginDate, EndDate,

           EndOfStore, EndOfTotal, CntPrice)

    SELECT T1.creDpt, T1.MtlUnName, @sCntDate, @sBeginDate, @sEndDate,

       EndOfStore = IsNull(EndOfStore, 0) + IsNull(InStore, 0)

                  + IsNull(InWXStore, 0) + IsNull(DBInStore, 0)

                  - IsNull(OutStore, 0) -IsNull(ZHOutStore,0),


       EndOfTotal = IsNull(EndOfTotal,0) + IsNull(BuyMoney, 0) + IsNull(InWXMoney, 0)+ IsNull(DBMoney, 0),

       

       CntPrice   = (IsNull(EndOfTotal,0) + IsNull(BuyMoney, 0) + IsNull(InWXMoney, 0)+ IsNull(DBMoney, 0))

                    /NullIf(IsNull(EndOfStore, 0) + IsNull(InStore, 0)+ IsNull(InWXStore, 0) + IsNull(DBInStore, 0),0)

    FROM (SELECT creDpt, MtlUnName

          FROM (

             SELECT DISTINCT creDpt, MtlUnName

             FROM dMtlOdrDtl D JOIN dMtlOdr M ON (D.mRelCode = M.relCode)

             WHERE M.ChkInDate BETWEEN @sBeginDate   AND @sEndDate  

               AND isValidity = 1 AND (M.InOutType=-1 OR M.InOutType=1)

               AND (M.comMode =201 OR M.comMode =402 OR M.comMode =403 OR

                    M.comMode =405 OR M.comMode =406 OR M.comMode =408)

             UNION ALL

             SELECT DISTINCT creDpt, MtlUnName

             FROM dMtlOdrDtlDtl D JOIN dMtlOdr M ON (D.mRelCode = M.relCode)

             WHERE M.ChkInDate BETWEEN @sBeginDate AND @sEndDate  

               AND isValidity = 1 AND (M.InOutType=-1 OR M.InOutType=1)

               AND (M.comMode =405)

             UNION ALL

             SELECT DISTINCT creDpt, MtlUnName =D.MemoText

             FROM dMtlOdrDtl D JOIN dMtlOdr M ON (D.mRelCode = M.relCode)

             WHERE M.ChkInDate BETWEEN @sBeginDate   AND @sEndDate

               AND M.comMode =202 AND isValidity = 1

             UNION ALL

             SELECT DISTINCT creDpt =DptOfOthr, MtlUnName

             FROM dMtlOdrDtl D JOIN dMtlOdr M ON (D.mRelCode = M.relCode)

             WHERE M.comMode =406 AND isValidity = 1

               AND M.ChkInDate BETWEEN @sBeginDate   AND @sEndDate

            ) AS T GROUP BY creDpt, MtlUnName ) AS T1

     

     --本期進倉--InOutType =1為本月所有進倉數量和金額

       LEFT JOIN (SELECT MtlUnName, creDpt, InStore = Sum(CASE WHEN M.InOutType =1 THEN Number ELSE 0 END),   

                     BuyMoney = Sum(CASE WHEN M.InOutType =1 THEN RealPayMoney ELSE 0 END),

                     OutStore = Sum(CASE WHEN M.InOutType =-1 THEN Number ELSE 0 END)

                  FROM dMtlOdrDtl D

                     JOIN dMtlOdr M ON (D.mRelCode = M.relCode)

                  WHERE M.ChkInDate BETWEEN @sBeginDate   AND @sEndDate   

                    AND isValidity = 1 AND (M.InOutType=-1 OR M.InOutType=1)

                    AND (M.comMode =201 OR M.comMode =402 OR M.comMode =403 OR

                         M.comMode =405 OR M.comMode =406 OR M.comMode =408)

                  GROUP BY MtlUnName, creDpt)AS T3

             ON (T1.MtlUnName=T3.MtlUnName AND T1.creDpt =T3.creDpt)

     --本期出倉-組合

       LEFT JOIN (SELECT MtlUnName, creDpt, ZHOutStore = Sum(Number)

                  FROM dMtlOdrDtlDtl D

                    JOIN dMtlOdr M ON (D.mRelCode = M.relCode)

                  WHERE M.ChkInDate BETWEEN @sBeginDate   AND @sEndDate   

                    AND isValidity = 1 AND (M.InOutType=-1 OR M.InOutType=1)

                    AND (M.comMode =405) AND M.FuncName=''材料組裝''

                  GROUP BY MtlUnName, creDpt)AS T9

            ON (T1.MtlUnName=T9.MtlUnName AND T1.creDpt =T9.creDpt)

   

      --本期外協進倉

       LEFT JOIN (SELECT MtlUnName =D.MemoText, creDpt,

                     InWXMoney = Sum(MoneyOdr), InWXStore = Sum(NumberOdr)

                  FROM dMtlOdrDtl D

                   JOIN dMtlOdr M ON (D.mRelCode = M.relCode)

                  WHERE isValidity = 1 AND M.comMode =202 AND M.InOutType=1

                    AND M.ChkInDate BETWEEN @sBeginDate   AND @sEndDate

                  GROUP BY D.MemoText, creDpt)AS T5

             ON (T1.MtlUnName=T5.MtlUnName AND T1.creDpt =T5.creDpt)


      --本期調撥進倉

       LEFT JOIN (SELECT MtlUnName, creDpt=M.DptOfOthr, DBInStore = Sum(Number),

                    DBMoney = Sum(RealPayMoney)

                  FROM dMtlOdrDtl D

                    JOIN dMtlOdr M ON (D.mRelCode = M.relCode)

                  WHERE isValidity = 1 AND M.comMode =406

                    AND M.ChkInDate BETWEEN @sBeginDate   AND @sEndDate

                  GROUP BY MtlUnName, M.DptOfOthr)AS T6

            ON (T1.MtlUnName=T6.MtlUnName AND T1.creDpt =T6.creDpt)

           

      --調上期期末

       LEFT JOIN dMtlStoreCnt AS T11

            ON T1.MtlUnName =T11.MtlUnName AND T1.creDpt =T11.creDpt

           AND CntDate= Convert(Char(6), DateAdd(ms,-3,DateAdd(mm, DateDiff(m,0, @sBeginDate), 0)), 112)

            --AND @sBeginDate-1   between T11.BeginDate AND T11.EndDate

       SET @NumError = @NumError + @@error



    --4、上期有本期沒有

    INSERT INTO dMtlStoreCnt (CntDate, creDpt, MtlUnName, MtlKind, MtlCode, MtlName,

         MtlSpec, MtlUnit, EndOfStore, EndOfTotal, CntPrice, BeginDate, EndDate)

    SELECT @sCntDate, creDpt, MtlUnName, Max(MtlKind), Max(MtlCode), Max(MtlName),

       Max(MtlSpec), Max(MtlUnit), Max(EndOfStore), Max(EndOfTotal), Max(CntPrice),

       @sBeginDate, @sEndDate

    FROM dMtlStoreCnt D

    WHERE D.CntDate = @sPreCntDate

        AND NOT EXISTS (SELECT MtlUnName FROM dMtlStoreCnt S

                        WHERE S.MtlUnName = D.MtlUnName AND S.creDpt = D.creDpt AND S.CntDate [email protected])

    GROUP BY MtlUnName, creDpt

    SET @NumError = @NumError + @@error

   

    --把移動加權平均價-更新到本月出倉的單價

    UPDATE dMtlOdrDtl SET tAvgPrice =T.CntPrice

    FROM dMtlStoreCnt T

    WHERE T.CntDate = @sCntDate AND T.MtlUnName =dMtlOdrDtl.MtlUnName AND comMode =405

      AND EXISTS(SELECT ID FROM dMtlOdr M

                 WHERE relCode = mRelCode AND isValidity = 1 AND InOutType=-1

                   AND T.creDpt = M.creDpt

                   AND ChkInDate BETWEEN T.BeginDate AND T.EndDate)

                   

    --組裝進倉

    UPDATE dMtlOdrDtlDtl SET tAvgPrice =T.CntPrice

    FROM dMtlStoreCnt T

    WHERE T.CntDate = @sCntDate AND T.MtlUnName =dMtlOdrDtlDtl.MtlUnName AND comMode =405

      AND EXISTS(SELECT ID FROM dMtlOdr M

                 WHERE relCode = mRelCode AND isValidity = 1  

                   AND T.creDpt = M.creDpt

                   AND ChkInDate BETWEEN T.BeginDate AND T.EndDate)


    UPDATE dMtlOdr SET cntDate = Cast(@sCntDate AS Varchar(20))

    WHERE (comMode IN (201,   405, 406, 202, 402, 403) AND isValidity =1)

      AND ChkInDate BETWEEN @sBeginDate AND @sEndDate

        --or comMode in () and isnull(AthrMan, '''') <>''''

       --and isnull(cntDate, '''') =''''

       

    SET @NumError = @NumError + @@error

       

    IF (@NumError <> 0 )

    BEGIN

        ROLLBACK   TRANSACTION

        SET @ErrorName =''更新數據出錯,操作失敗!編號:'' + Cast(@NumError AS Varchar(20))

        RAISERROR(@ErrorName, 16 ,1);

        RETURN;

    END ELSE

    BEGIN

        COMMIT TRANSACTION

    END


END')

-----------------------------------------------分割線--------------------------------------------------

--添加材料組裝類別

INSERT INTO dStoreFunc(FuncGroup, TableMode, FuncID, FuncName, InOutType, ID)

SELECT 140, 405, 64, '材料組裝', 1, 3

WHERE NOT EXISTS (SELECT * FROM dStoreFunc WHERE FuncGroup=140 AND FuncID=64 AND FuncName='材料組裝')


DELETE sFuncSelectData WHERE comMode=100 AND FuncID=64 AND SelectChnName='材料組合'


INSERT INTO sFuncSelectData(comMode, FuncID, FuncName, OrdID, SelectEngName, IsShow, SelectChnName,

    SelectSQL, SelectToEditField, sFuncID, CDSTable, IsSelectBill, DateConditionStr,

    CompConditionStr, OrderByName, SpcConditionStr, SpcConditionField, SelectCaption1,

    SelectCaption2, SelectToLocateField, FillField, BillFuncID)

SELECT 100, 64, '材料出倉', (SELECT Max(OrdID)FROM sFuncSelectData WHERE comMode=100 AND FuncID=64)+1,

    'mlSelectMtl25', 1, '材料組合',

    'SELECT MtlKind [材料類別], T1.userCode [編碼], T1.Name [名稱], T1.MtlSpec [規格], T1.MtlUnit [庫存單位],

    NumCurStk [配套庫存],ZHPrice[組合單價], MtlUnName, relCode, Price, ZHPrice, memoText, T1.MemoSplMtl

FROM cMtl T1

LEFT JOIN(SELECT mRelCode, NumCurStk=Min(IsNull(NumCurStk/NullIf(NumUp, 0), 0)),

              ZHPrice=Sum(S.Price*NumUp)

          FROM cPdtBom B

          JOIN cMtl M   ON M.relCode=B.mRelCode AND M.comMode=105

          LEFT JOIN dMtlStore S ON S.comMode=300   %0:s  

                AND B.MtlName=S.MtlName

                AND IsNull(B.MtlSpecName,'''')=IsNull(S.MtlSpec,'''')

                AND B.MtlUnit=S.MtlUnit

          WHERE B.comMode=105

          GROUP BY mRelCode) AS T2

       ON T1.relCode=T2.mRelCode

WHERE(T1.isNotUsing=0)AND (T1.comMode=105 OR T1.IsShare=1 AND T1.comMode=105)',

    'MtlType/RelCode/MtlKind/材料類別/MtlCode/編碼/MtlName/名稱/MtlSpec/規格/MtlUnit/庫存單位/Number/配套庫存/Price/ZHPrice/',

    0, '名稱;編碼;規格', 0, '', '', 'Order by T1.RelCode desc', 'AND   S.creDpt = %0:s', 'creDpt', '', '',

    '', '', 3

WHERE NOT EXISTS (SELECT *

                  FROM sFuncSelectData

                  WHERE comMode=100 AND FuncID=64 AND SelectChnName='材料組合' AND IsShow=1)


-----------------------------------------------分割線--------------------------------------------------

--把編碼放到最后去對應,以便取庫存單價

UPDATE sFuncSelectData

SET SelectToEditField=Replace(SelectToEditField, 'MtlCode/編碼/', '')+'MtlCode/編碼/'

WHERE FuncID IN (8, 64, 65, 173, 174)AND CharIndex('MtlCode/編碼/', SelectToEditField)>0


UPDATE dMtlOdr

SET ChkInDate=tDate

WHERE IsNull(ChkInDate, '')='' AND isValidity=1


UPDATE dMtlOdr

SET ChkInDate=S.InDate

FROM(SELECT relCode, InDate=ChkInDate

     FROM dMtlOdr

     WHERE isValidity=1 AND comMode=402)S

WHERE CodeOfMtlPlan=S.relCode AND isValidity=1 AND IsNull(ChkInDate, '')=''


-----------------------------------------------分割線--------------------------------------------------




聯系方式
 
 

聯系人:肖生     

手 機:189-28668085

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

在線QQ: fma.png 冉工

郵 箱:[email protected]

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