全站搜索
常见问题分类
 

工厂版

 
常见问题
 
功能权限
 
基础设置
 
销售管理
 
电商管理
 
生产管理
 
物料管理
 
出纳管理
 
会计管理
 
成品管理
 
半成品
 

电商版

 
常见问题
 
功能权限
 
订单管理
 
采购管理
 
退款管理
 
售后管理
 
赔偿管理
 
仓储管理
 
绩效管理
 
基础设置
 
数据分析
 
出纳管理
 

通—用

工-SpitPdtOdrDtl拆套成包

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

fma.png:58413709  

fma.png:2189948296

邮 箱:58413709@qq.com

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

工作时间:(星期一至星期六)

上午08:30-12:00 

下午14:00-18:00

联系方式
 
 

联系人:肖生     

手 机:189-28668085

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

在线QQ: fma.png 冉工

邮 箱:58413709@qq.com

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