----------------------------------------------------------------------------
TRD_StockMgr_PurchaseArrivalMgr_ModifyArrivalConfirmByID
作 者：
创建日期：2015-7-10
功能描述：修改采购到货-按订单到货确认
参数描述：
调用示例：
修改: 添加有备货与无备货确认到货行为。
declare @counts int
set @counts=0
exec TRD_StockMgr_PurchaseArrivalMgr_ModifyArrivalConfirmByID '10','','1','',,'3'@counts output
select @counts
----------------------------------------------------------------------------
*/
ALTER PROCEDURE [dbo].[TRD_ModifyArrivalConfirmByID]
(
@OPER_USER_ID INT, --添加人ID
@OPER_USER_NAME VARCHAR(50), --添加人
@XML XML , --到货xml
@ORDERID INT, --订单主表ID
@MSG NVARCHAR(100)=N'' OUTPUT, --返回信息
@OFCIDS NVARCHAR(MAX)=N'' OUTPUT --备货到货返回到货ID拼接字符串
)
AS
BEGIN
--日志记录参数------------
DECLARE @_opertype INT = 0
DECLARE @_desc VARCHAR(400)
DECLARE @_title VARCHAR(400)
----------------------------
--定义临时表
DECLARE @TEMP TABLE
(
ID INT,
AMOUNT NUMERIC(18,4),
BATCH_NO NVARCHAR(200) DEFAULT '',
STERILIZATION_BATCH_NO NVARCHAR(200) DEFAULT '',
VAILD_DATE NVARCHAR(200),
PRODUCTION_DATE NVARCHAR(200),
INVOICE_NO NVARCHAR(200),
PN NVARCHAR(200) DEFAULT '',
SN NVARCHAR(200) DEFAULT '',
REMARK NVARCHAR(1024)
)
--XML数据插入临时表
INSERT INTO @TEMP(ID,AMOUNT,BATCH_NO,STERILIZATION_BATCH_NO,VAILD_DATE,PRODUCTION_DATE,INVOICE_NO,PN,SN,REMARK)
SELECT
m.value('(ID)[1]','INT'),
m.value('(AMOUNT)[1]','NUMERIC(18,4)'),
m.value('(BATCH_NO)[1]','NVARCHAR(200)'),
m.value('(STERILIZATION_BATCH_NO)[1]','NVARCHAR(200)'),
m.value('(VAILD_DATE)[1]','NVARCHAR(200)'),
m.value('(PRODUCTION_DATE)[1]','NVARCHAR(200)'),
m.value('(INVOICE_NO)[1]','NVARCHAR(200)'),
m.value('(PN)[1]','NVARCHAR(200)'),
m.value('(SN)[1]','NVARCHAR(200)'),
m.value('(REMARK)[1]','NVARCHAR(1024)')
FROM @XML.nodes('/ROOT/PurchaseConfirmEntity') T(m)
--插入订单明细内容修正表
INSERT INTO [dbo].[TRD_ORDERFORM_CONTENT_LOG]
([ORDERFORM_CONTENT_ID]
,[SENDER_CONTENT_ID]
,[ORDER_DETAIL_ID]
,DC_ORG_ID --中心机构ID
,SENDER_ID --配送商ID
,DC_PRODUCT_ID --中心产品ID
,[PUR_CMMD_ID]
,[BATCH_NO]
,[STERILIZATION_BATCH_NO]
,[VAILD_DATE]
,[PRODUCTION_DATE]
,[PN]
,[SN]
,[OLD_BATCH_NO]
,[OLD_STERILIZATION_BATCH_NO]
,[OLD_VAILD_DATE]
,[OLD_PRODUCTION_DATE]
,[OLD_PN]
,[OLD_SN]
,[MIRROR_BALL]
,[CONCAVE_CYLINDER]
,[INVOICE_NO]
,OLD_INVOICE_NO
,[SEND_AMOUNT]
,[SEND_SUM]
,[OVER_AMOUNT]
,[OVER_SUM]
,[CREATE_USER_ID]
,[CREATE_USER]
,[CREATE_DATE])
SELECT TOC.ID,
TOC.SENDER_CONTENT_ID,
TOC.ORDER_DETAIL_ID,
TOG.DC_ORG_ID, --中心机构ID
TOD.SENDER_ID , --配送商ID
TOD.DC_PRODUCT_ID, --中心产品ID
TOC.PUR_CMMD_ID,
TMP.BATCH_NO,
TMP.STERILIZATION_BATCH_NO,
CASE WHEN LTRIM(RTRIM(TMP.VAILD_DATE))='' THEN CAST('1900-01-01 00:00:00.000' AS DATETIME) ELSE TMP.VAILD_DATE END ,
CASE WHEN LTRIM(RTRIM( TMP.PRODUCTION_DATE))='' THEN CAST('1900-01-01 00:00:00.000' AS DATETIME) ELSE TMP.PRODUCTION_DATE END,
TMP.PN,
TMP.SN,
TOC.BATCH_NO,
TOC.STERILIZATION_BATCH_NO,
TOC.VAILD_DATE,
TOC.PRODUCTION_DATE,
TOC.PN,
TOC.SN,
TOC.MIRROR_BALL,
TOC.CONCAVE_CYLINDER,
TMP.INVOICE_NO,
TOC.INVOICE_NO,
TOC.SEND_AMOUNT,
TOC.SEND_SUM,
TMP.AMOUNT,
TMP.AMOUNT*TOD.TRD_PRICE,--*TOD.SEND_MEASURE_EX,
@OPER_USER_ID,
@OPER_USER_NAME,
GETDATE()
FROM @TEMP AS TMP
INNER JOIN TRD_ORDERFORM_CONTENT AS TOC
ON TMP.ID=TOC.ID
INNER JOIN TRD_ORDER_DETAIL AS TOD
ON TOC. ORDER_DETAIL_ID=TOD.ID
INNER JOIN TRD_ORG AS TOG
ON TOG.ID = TOD.SENDER_ID
WHERE TMP.ID=TOC.ID AND
(ISNULL(TMP.BATCH_NO,'')<>LTRIM(RTRIM(ISNULL(TOC.BATCH_NO,'')))
OR ISNULL(TMP.STERILIZATION_BATCH_NO,'')<>LTRIM(RTRIM(ISNULL(TOC.STERILIZATION_BATCH_NO,'')))
OR REPLACE(TMP.VAILD_DATE,'-','')<>CONVERT(VARCHAR(8),CASE WHEN RTRIM(LTRIM(ISNULL(TOC.VAILD_DATE,'')))='' THEN '19000101' ELSE TOC.VAILD_DATE END,112)
OR REPLACE(TMP.PRODUCTION_DATE,'-','')<>CONVERT(VARCHAR(8),CASE WHEN RTRIM(LTRIM(ISNULL(TOC.PRODUCTION_DATE,'')))='' THEN '19000101' ELSE TOC.PRODUCTION_DATE END,112)
OR TMP.PN<>LTRIM(RTRIM(ISNULL(TOC.PN,'')))
OR TMP.SN<>LTRIM(RTRIM(ISNULL(TOC.SN,'')))
OR TMP.INVOICE_NO<>LTRIM(RTRIM(ISNULL(TOC.INVOICE_NO,'')))
)
--信息字段发生改变后，更新表字段信息
UPDATE TRD_OrderForm_Content
SET TRD_OrderForm_Content.BATCH_NO=TMP.BATCH_NO,
TRD_OrderForm_Content.STERILIZATION_BATCH_NO=TMP.STERILIZATION_BATCH_NO,
TRD_OrderForm_Content.VAILD_DATE=CASE WHEN RTRIM(LTRIM(TMP.VAILD_DATE))='' THEN CAST('1900-01-01 00:00:00.000' AS DATETIME) ELSE TMP.VAILD_DATE END ,
TRD_OrderForm_Content.PRODUCTION_DATE=CASE WHEN RTRIM(LTRIM(TMP.PRODUCTION_DATE))='' THEN CAST('1900-01-01 00:00:00.000' AS DATETIME) ELSE TMP.PRODUCTION_DATE END,
TRD_OrderForm_Content.INVOICE_NO=TMP.INVOICE_NO,
TRD_OrderForm_Content.PN=TMP.PN,
TRD_OrderForm_Content.SN=TMP.SN
FROM @TEMP AS TMP
INNER JOIN TRD_ORDERFORM_CONTENT AS TOC
ON TMP.ID=TOC.ID
WHERE TMP.ID=TOC.ID AND
(ISNULL(TMP.BATCH_NO,'')<>LTRIM(RTRIM(ISNULL(TOC.BATCH_NO,'')))
OR ISNULL(TMP.STERILIZATION_BATCH_NO,'')<>LTRIM(RTRIM(ISNULL(TOC.STERILIZATION_BATCH_NO,'')))
OR REPLACE(TMP.VAILD_DATE,'-','')<>CONVERT(VARCHAR(8),CASE WHEN RTRIM(LTRIM(ISNULL(TOC.VAILD_DATE,'')))='' THEN '19000101' ELSE TOC.VAILD_DATE END,112)
OR REPLACE(TMP.PRODUCTION_DATE,'-','')<>CONVERT(VARCHAR(8),CASE WHEN RTRIM(LTRIM(ISNULL(TOC.PRODUCTION_DATE,'')))='' THEN '19000101' ELSE TOC.PRODUCTION_DATE END,112)
OR TMP.PN<>LTRIM(RTRIM(ISNULL(TOC.PN,'')))
OR TMP.SN<>LTRIM(RTRIM(ISNULL(TOC.SN,'')))
OR TMP.INVOICE_NO<>LTRIM(RTRIM(ISNULL(TOC.INVOICE_NO,'')))
) ;
--无备货情况
IF EXISTS(SELECT TOP 1 1 FROM TRD_ORDER WHERE ID=@ORDERID AND [TYPE]<>20)
BEGIN
--验证是否重复到货
IF EXISTS (SELECT TOP 1 1
FROM @TEMP AS T
INNER JOIN TRD_ORDERFORM_CONTENT AS TOC
ON T.ID=TOC.ID AND TOC.[STATE]=30
)
BEGIN
SET @MSG='该订单下包含已到货的数据，请刷新后重新到货。'
RETURN ;
END
--修改订单明细内容表,主要是更新状态，到货数量，到户金额
UPDATE TRD_OrderForm_Content
SET TRD_OrderForm_Content.OVER_AMOUNT=TMP.AMOUNT,
TRD_OrderForm_Content.OVER_SUM=TMP.AMOUNT*TOD.TRD_PRICE,--*TOD.SEND_MEASURE_EX,
TRD_OrderForm_Content.ARRIVAL_REMARK=TMP.REMARK,
STATE=30,
LAST_UPDATE_USER_ID=@OPER_USER_ID,
LAST_UPDATE_USER=@OPER_USER_NAME,
LAST_UPDATE_DATE=GETDATE()
FROM @TEMP AS TMP
INNER JOIN TRD_ORDERFORM_CONTENT AS TOC
ON TMP.ID=TOC.ID
INNER JOIN TRD_ORDER_DETAIL AS TOD
ON TOC. ORDER_DETAIL_ID=TOD.ID;
--根据订单明细内容表状态修改订单明细表状态
WITH T AS (
SELECT ORDER_DETAIL_ID ,
STATE ,
COUNT(1) Qty
FROM TRD_OrderForm_Content T WHERE T.STATE IN(20,30)
GROUP BY ORDER_DETAIL_ID ,
STATE
)
UPDATE TRD_ORDER_DETAIL
SET STATE = CASE WHEN (
SELECT COUNT(1)
FROM T
WHERE T.ORDER_DETAIL_ID = TRD.ID AND
T.STATE IN ( 20 , 30 )
) = 2 THEN 40--部分发货
WHEN 20=ALL ( SELECT STATE
FROM T
WHERE T.ORDER_DETAIL_ID = TRD.ID ) THEN 20--卖家已发货
WHEN 30=ALL ( SELECT STATE
FROM T
WHERE T.ORDER_DETAIL_ID = TRD.ID ) THEN 50--50已到货
END
FROM TRD_ORDER_DETAIL TRD
JOIN T
ON T.ORDER_DETAIL_ID = TRD.ID
WHERE TRD.STATE <> 30 AND TRD.STATE<>70;--新加条件
--根据订单明细表状态更新订单表状态
WITH T AS (
SELECT ORDER_ID ,
STATE ,
COUNT(1) Qty
FROM TRD_ORDER_DETAIL T WHERE T.STATE IN(10,20,30,40,50)
GROUP BY ORDER_ID ,
STATE
)
UPDATE TRD_ORDER
SET STATE = CASE
WHEN (
SELECT COUNT(1)
FROM T
WHERE T.ORDER_ID = TRD.ID AND
T.STATE IN ( 20 , 10 )--卖家已发货和卖家未阅读
) = 2 THEN 30--已发货
WHEN (
SELECT COUNT(1)
FROM T
WHERE T.ORDER_ID = TRD.ID AND
T.STATE IN ( 10 , 40 )--卖家未阅读和部分到货
) = 2 THEN 50--部分发货
WHEN (
SELECT COUNT(1)
FROM T
WHERE T.ORDER_ID = TRD.ID AND
T.STATE IN ( 10 , 50 )--卖家未阅读和已到货
) = 2 THEN 50--部分发货
WHEN (
SELECT COUNT(1)
FROM T
WHERE T.ORDER_ID = TRD.ID AND
T.STATE IN ( 20 , 40 )--卖家已发货和部分到货
) = 2 THEN 50--部分发货
WHEN (
SELECT COUNT(1)
FROM T
WHERE T.ORDER_ID = TRD.ID AND
T.STATE IN ( 20 , 30 )--卖家已发货和处理中
) = 2 THEN 40--处理中
WHEN (
SELECT COUNT(1)
FROM T
WHERE T.ORDER_ID = TRD.ID AND
T.STATE IN ( 40 , 30 )--部分到货和处理中
) = 2 THEN 40--处理中
WHEN (
SELECT COUNT(1)
FROM T
WHERE T.ORDER_ID = TRD.ID AND
T.STATE IN ( 20 , 50 )--卖家已发货和已到货
) = 2 THEN 50--部分发货
WHEN (
SELECT COUNT(1)
FROM T
WHERE T.ORDER_ID = TRD.ID AND
T.STATE IN ( 40 , 50 )--部分到货和已到货
) = 2 THEN 50--部分发货
WHEN (
SELECT COUNT(1)
FROM T
WHERE T.ORDER_ID = TRD.ID AND
T.STATE IN ( 30 , 50 )--处理中和已到货
) = 2 THEN 40--处理中
WHEN 10=ALL ( SELECT STATE
FROM T
WHERE T.ORDER_ID = TRD.ID ) THEN 10--卖家未阅读
WHEN 20=ALL ( SELECT STATE
FROM T
WHERE T.ORDER_ID = TRD.ID ) THEN 30--卖家已发货
WHEN 40=ALL ( SELECT STATE
FROM T
WHERE T.ORDER_ID = TRD.ID ) THEN 50--部分发货
WHEN 50=ALL ( SELECT STATE
FROM T
WHERE T.ORDER_ID = TRD.ID ) THEN 60--已到货
WHEN 30=ALL ( SELECT STATE
FROM T
WHERE T.ORDER_ID = TRD.ID ) THEN 40--处理中
END
FROM TRD_ORDER TRD
JOIN T
ON T.ORDER_ID = TRD.ID
WHERE TRD.STATE <> 40 AND TRD.STATE<>80; --新加条件
--未全部到货的订单明细和订单数据状态为处理中
UPDATE TRD_ORDER_DETAIL SET STATE=30
FROM TRD_ORDERFORM_CONTENT AS T
JOIN TRD_ORDER_DETAIL AS T1
ON T.ORDER_DETAIL_ID=T1.ID
JOIN TRD_ORDER AS T2
ON T1.ORDER_ID=T2.ID
WHERE T.SEND_AMOUNT>T.OVER_AMOUNT AND T.OVER_AMOUNT<>0
UPDATE TRD_ORDER SET STATE=40
FROM TRD_ORDERFORM_CONTENT AS T
JOIN TRD_ORDER_DETAIL AS T1
ON T.ORDER_DETAIL_ID=T1.ID
JOIN TRD_ORDER AS T2
ON T1.ORDER_ID=T2.ID
WHERE T.SEND_AMOUNT>T.OVER_AMOUNT AND T.OVER_AMOUNT<>0;
--SET @MSG='到货成功。'
--赋值日志记录参数------------
SET @_opertype=20
SET @_title='按订单到货确认'
SET @_desc='订单ID为:'+CAST(@ORDERID AS VARCHAR(20)) +'的订单信息'
----------------------------
END
ELSE
BEGIN
--返回到货ID拼接字符串
SET @OFCIDS=''
DECLARE @TB TABLE (
PO_ID INT, --采购单ID
PO_ITEM_ID INT, --采购明细ID
OF_ID INT, --订单ID
OF_ITEM_ID INT, --订单明细ID
OFC_ID INT, --订单明细内容ID
PUR_CMMD_ID INT, --可采购商品ID
AMOUNT NUMERIC(18,4), --入库数量
CODE VARCHAR(32) --单号
)
INSERT INTO @TB(PO_ID,PO_ITEM_ID,OF_ID,OF_ITEM_ID,OFC_ID,PUR_CMMD_ID,AMOUNT)
SELECT TOD.PURCHASE_ID,TOD.PURCHASE_DETAIL_ID,TOD.ORDER_ID,TOD.ID,TOC.ID,TOC.PUR_CMMD_ID,CAST(TEM.AMOUNT AS NUMERIC(18,4))
FROM @TEMP AS TEM
INNER JOIN TRD_ORDERFORM_CONTENT AS TOC --订单明细内容表
ON TEM.ID=TOC.ID
INNER JOIN TRD_ORDER_DETAIL AS TOD --订单明细表
ON TOC.ORDER_DETAIL_ID=TOD.ID
--验证是否重复到货
IF EXISTS (SELECT TOP 1 1
FROM @TB AS T
INNER JOIN TRD_ORDERFORM_CONTENT AS TOC
ON T.OFC_ID=TOC.ID AND TOC.[STATE]=40
)
BEGIN
SET @MSG='该订单下包含已到货的数据，请刷新后重新到货。'
RETURN ;
END
--------------------------------批量更新订单明细内容表【开始】---------------------------------------------------------
--更新订单内容表状态为【完成】、到货数量、到货金额
UPDATE TRD_ORDERFORM_CONTENT
SET [STATE]=40,
[OVER_AMOUNT]=TB.AMOUNT,
OVER_SUM=TB.AMOUNT*TOD.TRD_PRICE,--*TOD.SEND_MEASURE_EX,
LAST_UPDATE_USER_ID=@OPER_USER_ID,
LAST_UPDATE_USER=@OPER_USER_NAME,
LAST_UPDATE_DATE=GETDATE()
FROM @TB AS TB
INNER JOIN TRD_ORDER_DETAIL AS TOD --订单明细表
ON TB.OF_ITEM_ID=TOD.ID
WHERE TRD_ORDERFORM_CONTENT.ID=TB.OFC_ID
--------------------------------批量更新订单明细内容表【结束】---------------------------------------------------------
--定义保存订单明细的表变量。到货的批次可能对应多个订单明细。
DECLARE @TB_GROUP TABLE(
OF_ID INT, --订单ID
OF_ITEM_ID INT, --订单明细ID
CON_AMOUNT NUMERIC(18,4), --入库数量的和
PO_ID INT, --采购单ID
PO_ITEM_ID INT --采购单明细ID
)
--向订单明细的表变量插入数据
INSERT INTO @TB_GROUP(OF_ID ,OF_ITEM_ID ,CON_AMOUNT ,PO_ID ,PO_ITEM_ID)
SELECT OF_ID,OF_ITEM_ID,SUM(AMOUNT) ,PO_ID ,PO_ITEM_ID
FROM @TB
GROUP BY OF_ID,OF_ITEM_ID ,PO_ID ,PO_ITEM_ID
-----------------------------------批量更新订单明细表：更新到货金额、到货数量、状态【开始】----------------------------
--批量更新订单明细
--说明：如果某订单明细下内容表数据的到到货数量大于订单明细的采购数量
-- （暂时不考虑这种情况--->>并且内容表数据的状态没有小于40的，则将订单明细状态更新为70【完成】，否则为40【部分到货】)
UPDATE TRD_ORDER_DETAIL --订单明细表
SET TRD_ORDER_DETAIL.OVER_AMOUNT= ISNULL(TRD_ORDER_DETAIL.OVER_AMOUNT,0) +TMP.CON_AMOUNT, --到货数量
TRD_ORDER_DETAIL.OVER_SUM=ISNULL(TRD_ORDER_DETAIL.OVER_SUM,0)+TRD_ORDER_DETAIL.TRD_PRICE*TMP.CON_AMOUNT,--*TRD_ORDER_DETAIL.SEND_MEASURE_EX,--到货金额
TRD_ORDER_DETAIL.[STATE]= (CASE WHEN TRD_ORDER_DETAIL.AMOUNT<=(ISNULL(TRD_ORDER_DETAIL.OVER_AMOUNT,0) +TMP.CON_AMOUNT)
--AND NOT EXISTS(SELECT TOP 1 1 FROM TRD_ORDERFORM_CONTENT WHERE ORDER_DETAIL_ID=TMP.OF_ITEM_ID AND [STATE]<40)
THEN 70 ELSE 40 END),--状态
TRD_ORDER_DETAIL.LAST_UPDATE_USER_ID = @OPER_USER_ID,
TRD_ORDER_DETAIL.LAST_UPDATE_USER = @OPER_USER_NAME,
TRD_ORDER_DETAIL.LAST_UPDATE_DATE = GETDATE()
FROM
(
SELECT SUM(CON_AMOUNT) AS CON_AMOUNT ,OF_ITEM_ID
FROM @TB_GROUP
GROUP BY OF_ITEM_ID ) AS TMP
WHERE TRD_ORDER_DETAIL.ID=TMP.OF_ITEM_ID
-----------------------------------批量更新订单明细表：更新到货金额、到货数量、状态【结束】----------------------------
-----------------------------------批量更新订单：更新到货金额、到货数量、状态【开始】-----------------------------------
/*
更新说明：
1.汇总某订单下订单明细状态为【完成】的订单明细的到货金额，然后更新某订单的到货金额。
2.如果某订单下存在<60状态的订单明细则更新该订单为【50：部分到货】状态,否则更新为【80：完成】。
*/
UPDATE TRD_ORDER --订单表
SET TRD_ORDER.OVER_ACCOUNT=TB.OVER_AllSUM,
[STATE]=CASE WHEN NOT EXISTS(SELECT TOP 1 1 FROM TRD_ORDER_DETAIL WHERE ORDER_ID=TB.ORDER_ID AND [STATE]<60) THEN 80 ELSE 50 END,
TRD_ORDER.LAST_UPDATE_USER_ID = @OPER_USER_ID,
TRD_ORDER.LAST_UPDATE_USER = @OPER_USER_NAME,
TRD_ORDER.LAST_UPDATE_DATE = GETDATE()
FROM (
SELECT TOD.ORDER_ID,SUM(ISNULL(TOD.OVER_SUM,0)) AS OVER_AllSUM
FROM TRD_ORDER_DETAIL AS TOD ,
(
SELECT TOP 1 OF_ID FROM @TB_GROUP
) AS TMP
WHERE TOD.ORDER_ID=TMP.OF_ID AND TOD.[STATE]!=60 --统计完成的订单明细到货金额
GROUP BY TOD.ORDER_ID
) AS TB
WHERE TRD_ORDER.ID=TB.ORDER_ID
-----------------------------------批量更新订单：更新到货金额、到货数量、状态【结束】------------------------------------
-----------------------------------批量更新采购单明细表采购到货数量、采购到货金额【开始】-------------------------------------
UPDATE TRD_PURCHASE_DETAIL --采购单明细表
SET TRD_PURCHASE_DETAIL.OVER_AMOUNT = TRD_PURCHASE_DETAIL.OVER_AMOUNT + TB.CON_AMOUNT,
TRD_PURCHASE_DETAIL.OVER_ACCOUNT = TRD_PURCHASE_DETAIL.OVER_ACCOUNT + TRD_PURCHASE_DETAIL.PRICE * TB.CON_AMOUNT,-- * TRD_PURCHASE_DETAIL.SEND_MEASURE_EX,
TRD_PURCHASE_DETAIL.LAST_UPDATE_USER_ID = @OPER_USER_ID,
TRD_PURCHASE_DETAIL.LAST_UPDATE_USER = @OPER_USER_NAME,
TRD_PURCHASE_DETAIL.LAST_UPDATE_DATE = GETDATE()
FROM
(
SELECT SUM(CON_AMOUNT) AS CON_AMOUNT ,PO_ITEM_ID FROM @TB_GROUP
GROUP BY PO_ITEM_ID
) AS TB
WHERE TRD_PURCHASE_DETAIL.ID=TB.PO_ITEM_ID
-----------------------------------------------------
-----------------------------------批量更新采购单明细表采购到货数量、采购到货金额【结束】-------------------------------------
-----------------------------------更新采购单主表状态【开始】-----------------------------------------------------------------
/*
说明:
获取该采购单下的所有订单，统计其订单状态的最小值，
如果最小值>=70，那么说明该采购单下的订单已完成，可以把该采购单关闭
否则，把该采购单的状态置成【50，处理中状态】
*/
UPDATE TRD_PURCHASE
SET TRD_PURCHASE.[STATE]=CASE WHEN TEMP.[STATE]>=70 THEN 60 ELSE 50 END,
TRD_PURCHASE.LAST_UPDATE_USER_ID = @OPER_USER_ID,
TRD_PURCHASE.LAST_UPDATE_USER = @OPER_USER_NAME,
TRD_PURCHASE.LAST_UPDATE_DATE = GETDATE()
FROM
(
SELECT MIN(TOR.[STATE]) AS [STATE] ,TG.PO_ID
FROM (
SELECT TOP 1 PO_ID FROM @TB_GROUP ) AS TG,TRD_ORDER AS TOR
WHERE TG.PO_ID=TOR.PURCHASE_ID
GROUP BY TG.PO_ID
) AS TEMP
WHERE TRD_PURCHASE.ID=TEMP.PO_ID
-----------------------------------更新采购单主表状态【结束】------------------------------------------------------------------
-----------------------------------插入到货单表数据【开始】------------------------------------------------------------------------
DECLARE @ASO_CODE VARCHAR(32)='' --到货单编码
DECLARE @OFC_ID INT --订单明细内容ID
--定义游标
DECLARE UPDATE_CODE_CURSOR CURSOR LOCAL FORWARD_ONLY FOR
SELECT OFC_ID FROM @TB
OPEN UPDATE_CODE_CURSOR --打开游标
FETCH NEXT FROM UPDATE_CODE_CURSOR INTO @OFC_ID
WHILE @@FETCH_STATUS=0
BEGIN
--生成到货单编码
EXEC SYS_Common_CreateCode 'TRD_ASO','DH',@ASO_CODE OUTPUT
--更新到货单单号
UPDATE @TB
SET CODE=@ASO_CODE
WHERE OFC_ID=@OFC_ID
--SET @OFCIDS=@OFCIDS+CAST(@OFC_ID AS VARCHAR(24))+',';
FETCH NEXT FROM UPDATE_CODE_CURSOR INTO @OFC_ID
END
CLOSE UPDATE_CODE_CURSOR --关闭游标
DEALLOCATE UPDATE_CODE_CURSOR --释放游标占用资源
--插入到货单
INSERT INTO TRD_ASO
(
PLAT_ID, --平台ID
[TYPE], --到货类型
CODE, --到货单编码
OF_ID, --订单ID
OF_ITEM_ID, --订单明细ID
OF_ITEM_CONTENT_ID, --订单内容明细ID
DC_BUYER_ID, --中心买方ID
BUYER_NAME, --买方名称
SENDER_ID, --配送商ID
SENDER_NAME, --配送商名称
SENDER_ABBR, --配送商简称
DC_PRODUCT_ID, --中心产品ID
PUR_CMMD_ID, --可采购商品ID
PRODUCT_NAME, --产品名
COMMERCE_NAME, --商品名
COMMON_NAME, --通用名
BRAND, --品牌
BASE_MEASURE, --基本计量单位
SPEC, --规格
BATCH_NO, --生产批号
STERILIZATION_BATCH_NO, --灭菌批号
VAILD_DATE, --失效日期
PRODUCTION_DATE, --生产日期
--DEGREE, --度数
MIRROR_BALL, --球镜
CONCAVE_CYLINDER, --柱镜
PN, --主码
SN, --次码
Invoice_No, --发票号
MANU_ID, --生产企业ID
MANU_NAME, --生产企业名称
MANU_ABBR, --生产企业简称
SEND_MEASURE, --到货计量单位
SEND_MEASURE_EX, --到货单位转换率
ARRIVE_DATE, --到货时间
CER_NO, --证书号
CER_VALID_DATE, --证书效期
REG_START_DATE, --证书有效期开始日期
CER_PRODUCT_NAME, --证书产品名称
QUALITY_LEVEL, --质量层次
DESCRIPTIONS, --产品描述
--STORE_ID, --到货库房ID
--STORE_NAME, --到货库房名称
--DEPOT_ID, --货位ID
--DEPOT_NAME, --货位名称
FACT_AMOUNT, --实际到货数量
PRICE, --采购价格
FACT_ACCOUNT, --实际到货金额
[STATE], --到货单状态
--SEND_OPERATOR_NAME, --卖方发货人
--SEND_OPERATE_DATE, --卖方发货人发货时间
INSTORE_OPERATOR_ID, --买方收货人ID
INSTORE_OPERATOR_NAME, --买方收货人
BACK_AMOUNT, --退货数量
BACK_ACCOUNT, --退货金额
MODIFIER_ID, --修改人ID
MODIFIER, --修改人
MODIFY_DATE --修改时间
)
SELECT
TOE.PLAT_ID,
20, --高值类到货单
TMP.CODE,
TOE.ID,
TOD.ID,
TOC.ID,
TOD.DC_BUYER_ID,
TOE.BUYER_NAME,
TOD.SENDER_ID,
TOD.SENDER_NAME,
TOD.SENDER_ABBR,
TOD.DC_PRODUCT_ID,
TOD.PUR_CMMD_ID,
TOD.PRODUCT_NAME,
TOD.COMMERCE_NAME,
TOD.COMMON_NAME,
TOD.BRAND,
TOD.BASE_MEASURE,
TOD.SPEC,
TOC.BATCH_NO,
TOC.STERILIZATION_BATCH_NO,
ISNULL(TOC.VAILD_DATE,'1900-01-01'),
ISNULL(TOC.PRODUCTION_DATE,'1900-01-01'),
--TOC.DEGREE,
ISNULL(TOC.MIRROR_BALL,''),
ISNULL(TOC.CONCAVE_CYLINDER,''),
ISNULL(TOC.PN,'') AS PN,
ISNULL(TOC.SN,'') AS SN,
--CASE WHEN RTRIM(LTRIM(ISNULL(TOC.PN,'')))='' THEN '0' ELSE TOC.PN END AS PN,
--CASE WHEN RTRIM(LTRIM(ISNULL(TOC.SN,'')))='' THEN '0' ELSE TOC.SN END AS SN,
TOC.INVOICE_NO,
TOD.MANU_ID,
TOD.MANU_NAME,
TOD.MANU_ABBR,
TOD.SEND_MEASURE,
TOD.SEND_MEASURE_EX,
GETDATE(),
TOD.CER_NO,
TOD.CER_VALID_DATE,
TOD.CER_START_DATE,
TOD.CER_PRODUCT_NAME,
TOD.QUALITY_LEVEL,
TOD.DESCRIPTIONS,
--@STOCK_ID,
--@STOCK_NAME,
--@DEPOT_ID,
--@DEPOT_NAME,
TMP.AMOUNT,
TOD.TRD_PRICE,
TMP.AMOUNT*TOD.TRD_PRICE,--*TOD.SEND_MEASURE_EX,
10,--完成
@OPER_USER_ID,
@OPER_USER_NAME,
0,
0,
@OPER_USER_ID,
@OPER_USER_NAME,
GETDATE()
FROM @TB AS TMP
INNER JOIN TRD_ORDERFORM_CONTENT AS TOC --订单明细内容表
ON TMP.OFC_ID=TOC.ID
INNER JOIN TRD_ORDER_DETAIL AS TOD --订单明细表
ON TOC.ORDER_DETAIL_ID=TOD.ID
INNER JOIN TRD_ORDER AS TOE --订单表
ON TOD.ORDER_ID=TOE.ID
-----------------------------------插入到货单表数据【结束】--------------------------------------------------------------------------
DECLARE @oneStroe INT --一级库ID
DECLARE @oneStroeName NVARCHAR(128) --一级库名称
DECLARE @oneDepot INT --一级库货位
DECLARE @oneDepotName NVARCHAR(128) --一级货位名称
--获取一级库房
SELECT TOP 1 @oneStroe=ID,@oneStroeName=NAME
FROM TRD_STORE
WHERE ONE_LIBRARY_FLAG=1
--获取一级库房货位
SELECT TOP 1 @oneDepot=ID,@oneDepotName=NAME
FROM TRD_DEPOT
WHERE STORE_ID=(SELECT TOP 1 ID FROM TRD_STORE WHERE ONE_LIBRARY_FLAG=1)
DECLARE @PREPARE_CODE VARCHAR(32)='' --到货单编码
--生成备货单编码
EXEC SYS_Common_CreateCode 'TRD_PREPARE','BH',@PREPARE_CODE OUTPUT
--备货单表
INSERT INTO TRD_PREPARE
(
PREPARE_CODE, --备货单号
[STATE], --状态
RELATED_ID, --关联单据ID
RELATED_CODE, --关联单据号
STORE_ID,
STORE_NAME,
SENDER_ID, --配送商ID
SENDER_NAME, --配送商名称
SENDER_ABBR, --配送商简称
AMOUNT, --数量
ACCOUNT, --金额
CONFIRMED_AMOUNT, --确认数量
CONFIRMED_ACCOUNT, --确认金额
USED_AMOUNT, --使用数量
USED_ACCOUNT, --使用金额
REJECT_AMOUNT, --退货数量
REJECT_ACCOUNT, --退货金额
IS_SETTLEMENT, --是否结算
REMARK, --备注
CREATE_USER_ID, --创建人ID
CREATE_USER, --创建人
CREATE_DATE, --创建时间
LAST_UPDATE_USER_ID, --最后修改人ID
LAST_UPDATE_USER, --最后修改人
LAST_UPDATE_DATE --最后修时间
)
SELECT TOP 1 @PREPARE_CODE,
30,
TOR.ID,
TOR.CODE,
@oneStroe,
@oneStroeName,
TOR.SENDER_ID,
TOR.SENDER_NAME,
TOR.SENDER_ABBR,
SUM(TEM.AMOUNT),
SUM(TEM.AMOUNT*TOD.TRD_PRICE),
0,
0,
0,
0,
0,
0,
1,
'采购到货插入备货单主表',
@OPER_USER_ID,
@OPER_USER_NAME,
GETDATE(),
@OPER_USER_ID,
@OPER_USER_NAME,
GETDATE()
FROM @TEMP AS TEM
INNER JOIN TRD_ORDERFORM_CONTENT AS TOC --订单明细内容表
ON TEM.ID=TOC.ID
INNER JOIN TRD_ORDER_DETAIL AS TOD -- 订单明细表
ON TOC.ORDER_DETAIL_ID=TOD.ID
INNER JOIN TRD_ORDER AS TOR --订单表
ON TOD.ORDER_ID=TOR.ID
GROUP BY TOR.ID,TOR.CODE,TOR.SENDER_ID,TOR.SENDER_NAME,TOR.SENDER_ABBR
DECLARE @rtnID INT --返回备货单主表ID
--获取新增行ID
SET @rtnID = SCOPE_IDENTITY()
SET @OFCIDS= CAST(@rtnID AS VARCHAR(24))+'﹋'
--备货单明细表
INSERT INTO TRD_PREPARE_DETAIL
(
PREPARE_ID,
[STATE], --状态
RELATED_ID, --关联单据ID
RELATED_DETAIL_ID, --关联单据明细ID
STORE_ID,
STORE_NAME,
DEPOT_ID,
DEPOT_NAME,
AMOUNT, --数量
PRICE, --单价
ACCOUNT, --金额
CONFIRMED_AMOUNT, --确认数量
CONFIRMED_ACCOUNT, --确认金额
USED_AMOUNT, --使用数量
USED_ACCOUNT, --使用金额
REJECT_AMOUNT, --退货数量
REJECT_ACCOUNT, --退货金额
MANU_ID, --生产企业ID
MANU_NAME, --生产企业名称
MANU_ABBR, --生产企业简称
REMARK, --备注
CREATE_USER_ID, --创建人ID
CREATE_USER, --创建人
CREATE_DATE, --创建时间
LAST_UPDATE_USER_ID, --最后修改人ID
LAST_UPDATE_USER, --最后修改人
LAST_UPDATE_DATE, --最后修改时间
SENDER_ID, --配送商ID
SENDER_NAME, --配送商名称
SENDER_ABBR, --配送商简称
DC_PRODUCT_ID, --中心产品ID
PUR_CMMD_ID, --可采购商品ID
PRODUCT_NAME, --产品名
COMMERCE_NAME, --商品名
COMMON_NAME, --通用名
BRAND, --品牌
BASE_MEASURE, --基本计量单位
SPEC, --规格
BATCH_NO, --生产批号
STERILIZATION_BATCH_NO, --灭菌批号
VAILD_DATE, --失效日期
PRODUCTION_DATE, --生产日期
MIRROR_BALL, --球镜
CONCAVE_CYLINDER, --柱镜
PN, --主码
SN --次码
)
SELECT @rtnID,
10,
TOR.ID,
TOC.ID,
@oneStroe,
@oneStroeName,
@oneDepot,
@oneDepotName,
TEM.AMOUNT,
TOD.TRD_PRICE,
TEM.AMOUNT*TOD.TRD_PRICE,--*TOD.SEND_MEASURE_EX,
0,
0,
0,
0,
0,
0,
TOD.MANU_ID,
TOD.MANU_NAME,
TOD.MANU_ABBR,
TEM.REMARK,
@OPER_USER_ID,
@OPER_USER_NAME,
GETDATE(),
@OPER_USER_ID,
@OPER_USER_NAME,
GETDATE(),
TOD.SENDER_ID,
TOD.SENDER_NAME,
TOD.SENDER_ABBR,
TOD.DC_PRODUCT_ID,
TOD.PUR_CMMD_ID,
TOD.PRODUCT_NAME,
TOD.COMMERCE_NAME,
TOD.COMMON_NAME,
TOD.BRAND,
TOD.BASE_MEASURE,
TOD.SPEC,
TOC.BATCH_NO,
TOC.STERILIZATION_BATCH_NO,
ISNULL(TOC.VAILD_DATE,'1900-01-01'),
ISNULL(TOC.PRODUCTION_DATE,'1900-01-01'),
TOC.MIRROR_BALL,
TOC.CONCAVE_CYLINDER,
ISNULL(TOC.PN,'') AS PN,
ISNULL(TOC.SN,'') AS SN
--CASE WHEN RTRIM(LTRIM(ISNULL(TOC.PN,'')))='' THEN '0' ELSE TOC.PN END AS PN,
--CASE WHEN RTRIM(LTRIM(ISNULL(TOC.SN,'')))='' THEN '0' ELSE TOC.SN END AS SN
FROM @TEMP AS TEM
INNER JOIN TRD_ORDERFORM_CONTENT AS TOC --订单明细内容表
ON TEM.ID=TOC.ID
INNER JOIN TRD_ORDER_DETAIL AS TOD -- 订单明细表
ON TOC.ORDER_DETAIL_ID=TOD.ID
INNER JOIN TRD_ORDER AS TOR --订单表
ON TOD.ORDER_ID=TOR.ID
--赋值日志记录参数------------
SET @_opertype=10
SET @_title='有备货，插入备货表、修改订单内容表、订单明细表、订单表、采购明细表、采购表、插入到货单表'
SET @_desc='订单ID为:'+CAST(@ORDERID AS VARCHAR(20)) +'的订单信息'
----------------------------
END
EXEC TRD_Common_TradeLog_Insert
@ORG_ID = 0,
@TITLE = '采购到货',
@PLAT_ID = 0,
@TYPE = 0,
@DESCRIPTION = @_desc,
@OPERATOR_ID = @OPER_USER_ID,
@OPERATOR = @OPER_USER_NAME
END
浙公网安备 33010602011771号