工-SpitPdtOdrDtl拆套成包

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

WHEREmRelCode=@mrelcode ANDcomMode=501

--保存原始订单某些数值

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

@oldNumEjt=NumEjt, @disount=@num / NullIf(Number, 0)

FROMdPdtOdrDtl

WHEREmRelCode=@mrelcode ANDcomMode=501 AND serID=@serid 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- (B.Number-@num) * B.NumPackage) ELSE 0 END, 0, SalesWay, A.Price, 0,

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

CASE WHEN NumEjt+@num>A.Number THEN NumEjt+@num-A.Number ELSE 0 END,

CASE WHEN NumWorkShop+@num>A.Number THEN B.NumPackage * (NumWorkShop+@num-A.Number) 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=''组合'' ANDB.mRelCode=@mrelcode AND B.comMode=501 ANDB.serID=@serid

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

SET @NumError=@NumError+@@error

--更新明细

UPDATEdPdtOdrDtl

SET Number=@oldNumber-@num, Total=@oldTotal * (1-@disount), NumBox=NumBox * (1-@disount),

NumStore=CASE WHEN NumStore+@num>Number THEN Number-@num ELSE NumStore END,

NumWorkShop=CASE WHEN NumWorkShop+@num>Number THEN Number-@num ELSE NumWorkShop END,

NumEjt=CASE WHEN NumEjt+@num>Number THEN Number-@num ELSE NumEjt END

WHEREmRelCode=@mrelcode ANDserID=@serid AND PdtSort=''组合'' AND comMode=501

SET @NumError=@NumError+@@error

--更新明细的明细数据

UPDATEdPdtOdrDtlDtl

SET Number=Number-@num,

NumStore=CASE WHEN NumStore+@num * NumPackage>Number * NumPackage THEN

(Number-@num) * NumPackage ELSE NumStore END,

RedNum=CASE WHEN RedNum+@num>Number THEN Number-@num ELSE RedNum END

WHEREmRelCode=@mrelcode ANDserID=@serid AND comMode=501

SET @NumError=@NumError+@@error

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

    UPDATE dPdtOdrDtl

    SET Total=@oldTotal * @disount, PriceDist=@oldTotal * @disount / @num,

        Price=(@oldTotal * @disount / @num)/ NullIf(DiscountDtl, 0), SumJZWeight=@oldSumJZWeight * @disount

    FROM(SELECT TOP 1 serID

         FROM dPdtOdrDtl WITH(NOLOCK)

         WHERE mRelCode=@mrelcode AND serID>@maxSerid AND comMode=501) AS T

    WHERE mRelCode=@mrelcode AND dPdtOdrDtl.serID=T.serID AND comMode=501

    SET @NumError=@NumError+@@error

--日志记录

/**//*--------- 判断是否有错误 ----------*/

IF (@NumError<>0)

BEGIN

   /**//*---------- 自定义错误输出 ----------*/

   SET @tmpAllRelCode=''拆分数据过程出错，操作失败!编号：''+Cast(@NumError AS VARCHAR(20))

   RAISERROR(@tmpAllRelCode, 16, 1);

   RETURN;

END

  END'

END