全站搜索
常见问题分类
 

工厂版

 

电商版

 

通—用

工-SpitPdtOdrDtl拆套成包

19
发表时间: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

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




联系方式
 
 

联系人:肖生     

手 机:189-28668085

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

在线QQ: fma.png 冉工

邮 箱:58413709@qq.com

地 址:佛山市顺德区乐从镇新华路1号三乐路口(领航国际604号)