常見問題分類
 

工廠版

 

電商版

 

通—用

常見問題

工-SpitPdtOdrDtl拆套成包

3
發表時間:2020-09-07 17:44

IF Object_Id('SpitPdtOdrDtl') IS NULL

BEGIN

EXEC dbo.sp_executesql @statement = N'CREATE   PROCEDURE [dbo].[SpitPdtOdrDtl]

  (@mrelcode VARCHAR(20),

   @serid INT,

   @num INT

  )

AS

  DECLARE @tmpAllRelCode VARCHAR(100)

  DECLARE @maxSerid INT

  DECLARE @maxID INT

  DECLARE @oldNumber NUMERIC(18, 3)

  DECLARE @oldNumStore NUMERIC(18, 3)

  DECLARE @oldNumEjt NUMERIC(18, 3)

  DECLARE @oldSumJZWeight NUMERIC(18, 3)

  DECLARE @disount FLOAT

  DECLARE @oldTotal NUMERIC(18, 4)

  DECLARE @oldNumWorkShop INT

  DECLARE @NumError INT=0


  BEGIN

     --取到需要插入的位置

SELECT@maxSerid=Max(serID), @maxID=Max(ID)

FROMdPdtOdrDtl

WHERE[email protected] ANDcomMode=501

--保存原始訂單某些數值

SELECT@oldNumber=Number, @oldTotal=Total, @oldNumStore=NumStore, @oldNumWorkShop=NumWorkShop,

@oldNumEjt=NumEjt, @[email protected] / NullIf(Number, 0)

FROMdPdtOdrDtl

WHERE[email protected] ANDcomMode=501 AND [email protected] AND PdtSort=''組合''


SET @disount=IsNull(@disount, 0)

--開始插入數據

INSERT INTO dPdtOdrDtl (ID, comMode, mRelCode, serID, OdrID, subSerID, relCode, stdID, styleID,

Number, NumStore, PriceDist, SalesWay, Price, Total, CostPrice,

LowPrice, StandardPrice, NumHpn, NumEjt, NumWorkShop, memoText,

PdtName, PdtSeriesName, PdtKind, IsBuy, PdtSort, PdtCode, PdtEngName,

PdtSpecName, PdtUnit, PdtClrName, PdtClrWMName, PackageTxt,

PackageType, CtrType, PrintLogo, PrintMemo, NumPackage, CBM, MZWeight,

JZWeight, WorkShopID, MtlAffordDptID, MtlCommentMan, MtlCommentDate,

PdtCommentMan, PdtCommentDate, PdtDateTime, MtlDateTime, PINO, CostID,

CostDate, NumReq, PlacedStoreID, PINOStore, CtrNameStore, isNormal,

SumCBM, SumMZWeight, SumJZWeight, NumBox, NumBuy, PdtSpecNameOld,

DiscountDtl, BarClrCode, BarPdtCode, Custom1, Custom2, Custom3, BagName

   )

SELECT@maxID+(Row_Number() OVER(ORDER BY B.ID ASC)), B.comMode, B.mRelCode,

@maxSerid+Row_Number() OVER (ORDER BY B.ID ASC), B.OdrID, B.subSerID, B.relCode, B.stdID,

A.styleID, @num * B.NumPackage,

CASE WHEN B.NumStore+ (@num * B.NumPackage) >A.Number * B.NumPackage THEN

(B.NumStore- ([email protected]) * B.NumPackage) ELSE 0 END, 0, SalesWay, A.Price, 0,

A.CostPrice, A.LowPrice, A.StandardPrice, 0,

CASE WHEN [email protected]>A.Number THEN [email protected] ELSE 0 END,

CASE WHEN [email protected]>A.Number THEN B.NumPackage * ([email protected]) ELSE

   0 END, A.memoText, B.PdtName, B.PdtSeriesName, B.PdtKind, B.IsBuy, B.PdtSort,

B.PdtCode, A.PdtEngName, B.PdtSpecName, B.PdtUnit, B.PdtClrName, A.PdtClrWMName,

B.PackageTxt, B.PackageType, A.CtrType, A.PrintLogo, A.PrintMemo, B.NumPackage, B.CBM,

B.MZWeight, B.JZWeight, A.WorkShopID, A.MtlAffordDptID, A.MtlCommentMan,

B.MtlCommentDate, A.PdtCommentMan, A.PdtCommentDate, A.PdtDateTime, A.MtlDateTime,

A.PINO, A.CostID, A.CostDate, A.NumReq, A.PlacedStoreID, A.PINOStore, A.CtrNameStore,

A.isNormal, CASE WHEN B.CBM>0 THEN B.CBM * @num ELSE A.SumCBM * @disount END,

CASE WHEN B.MZWeight>0 THEN B.MZWeight * @num ELSE A.SumMZWeight * @disount END, 0,

@num * B.NumPackage, A.NumBuy, A.PdtName+''拆分:''+Cast(@num AS VARCHAR), A.DiscountDtl,

B.BarClrCode, B.BarPdtCode, B.Custom1, B.Custom2, B.Custom3, B.BagName

FROMdPdtOdrDtlDtl B

JOIN dPdtOdrDtl A ON B.mRelCode=A.mRelCode ANDB.serID=A.serID

WHEREA.PdtSort=''組合'' AND[email protected] AND B.comMode=501 AND[email protected]

AND B.Number * B.NumPackage-NumInOut>0


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


--更新明細

UPDATEdPdtOdrDtl

SET [email protected]@num, [email protected] * ([email protected]), NumBox=NumBox * ([email protected]),

NumStore=CASE WHEN [email protected]>Number THEN [email protected] ELSE NumStore END,

NumWorkShop=CASE WHEN [email protected]>Number THEN [email protected] ELSE NumWorkShop END,

NumEjt=CASE WHEN [email protected]>Number THEN [email protected] ELSE NumEjt END

WHERE[email protected] AND[email protected] AND PdtSort=''組合'' AND comMode=501


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


--更新明細的明細數據

UPDATEdPdtOdrDtlDtl

SET [email protected],

NumStore=CASE WHEN [email protected] * NumPackage>Number * NumPackage THEN

([email protected]) * NumPackage ELSE NumStore END,

RedNum=CASE WHEN [email protected]>Number THEN [email protected] ELSE RedNum END

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


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


    --更新新增的包件價格到第一行

    UPDATE dPdtOdrDtl

    SET [email protected] * @disount, [email protected] * @disount / @num,

        Price=(@oldTotal * @disount / @num)/ NullIf(DiscountDtl, 0), [email protected] * @disount

    FROM(SELECT TOP 1 serID

         FROM dPdtOdrDtl WITH(NOLOCK)

         WHERE [email protected] AND serID>@maxSerid AND comMode=501) AS T

    WHERE [email protected] AND dPdtOdrDtl.serID=T.serID AND comMode=501


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


--日志記錄

/**//*--------- 判斷是否有錯誤 ----------*/

IF (@NumError<>0)

BEGIN

   /**//*---------- 自定義錯誤輸出 ----------*/

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


   RAISERROR(@tmpAllRelCode, 16, 1);


   RETURN;

END

  END'

END




聯系方式
 
 

聯系人:肖生     

手 機:189-28668085

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

在線QQ: fma.png 冉工

郵 箱:[email protected]

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