定时任务

最新推荐文章于 2023-07-07 14:17:10 发布

雁过留香

最新推荐文章于 2023-07-07 14:17:10 发布

阅读量286

点赞数

分类专栏： SQL存储过程

本文链接：https://blog.csdn.net/qq_41969460/article/details/87880368

版权

这是一个SQL脚本，用于检查近三个月内七大部件（面料、扶手、底盘、椅脚、气杆、木板、背框、纸箱）的入库状态。脚本创建了多个临时表，通过比较库存量与需求量，判断每个部件是否已入库，并进行合并处理，最后更新入库单状态。

摘要由CSDN通过智能技术生成

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[gf_updatesczjhd]
as
---面料是否入库
if object_id('tempdb..#ml') is not null Begin
drop table #ml
end
select * into #ml from (
select q2.FSourceBillNo as 'ddh',t1.FitemID as FitemID,
case when c1.FStockQty/c1.fqty*r.fqty >=
case when r.fqty is not null then r.fqty when r.fqty is null and c2.Fqty is not null then c2.Fqty else 0 end*0.90
or
c2.FStockQty >=
case when r.fqty is not null then r.fqty when r.fqty is null and c2.Fqty is not null then c2.Fqty else 0 end*0.90 then '是'
else '否' end as 'ml'
from porequestentry q2
inner join t_icitem t2 on q2.FitemID = t2.FitemID
inner join t_icitem t3 on q2.FEntrySelfP0132 = t3.FNumber
inner join seorderentry s2 on q2.FSourceinterID = s2.FinterID and q2.FSourceEntryID = s2.FEntryID
inner join t_bossczjhdentry t1 on s2.FinterID = t1.FID_SRC and s2.FEntryID = t1.FEntryID_SRC
left join
( select FMultiinterID,FMultiEntryID,FBillID,FDestEntryID,fqty from ICBillRelations_S01ToP01 where FDestEntryID > '0' and fqty > 0 ) r
on q2.FInterID = r.FMultiinterID and q2.FEntryID = r.FMultiEntryID
left join poorderentry c1 on r.FBillID = c1.FinterID and R.FDestEntryID = c1.FEntryID
left join poorderentry c2 on q2.FinterID = c2.FsourceinterID and q2.FentryID = c2.FsourceentryID
where q2.FSourceBillNo in (select b.FBillNo_SRC from t_bossczjhdentry b inner join t_bossczjhd a on b.FID = a.FID
where month(b.FDateyh) in (MONTH(GETDATE()),MONTH(DATEADD(MONTH,1,GETDATE())),MONTH(DATEADD(MONTH,-1,GETDATE())))
and a.FMultiCheckStatus = '16')
and left(t2.fnumber,4) in ('1001','1002') and t2.FName not like '%白色拉布%'
and q2.FMRPAutoClosed = 1
)
b ;
--select * from #ml

---多个面料时合并
if object_id('tempdb..#gfml') is not null Begin
drop table #gfml
end
select * into #gfml from (
select ddh,fitemid,
case when ml like '%否%' then '否' else '是' end as 'ml'
from (
select ddh,fitemid ,
(SELECT ml+',' FROM
#ml b
WHERE b.ddh=a.ddh and b.fitemid = a.fitemid For XML Path('')) AS 'ml'
from
#ml
as a
group by ddh,fitemid ) a) a
--select * from #gfml

----扶手是否入库
if object_id('tempdb..#fs') is not null Begin
drop table #fs
end
select * into #fs from (
select q2.FSourceBillNo as 'ddh',t1.FitemID as FitemID,
case when c1.FStockQty/c1.fqty*r.fqty >=
case when r.fqty is not null then r.fqty when r.fqty is null and c2.Fqty is not null then c2.Fqty else 0 end*0.95
or
c2.FStockQty >=
case when r.fqty is not null then r.fqty when r.fqty is null and c2.Fqty is not null then c2.Fqty else 0 end*0.95 then '是'
else '否' end as 'fs'
from porequestentry q2
inner join t_icitem t2 on q2.FitemID = t2.FitemID
inner join t_icitem t3 on q2.FEntrySelfP0132 = t3.FNumber
inner join seorderentry s2 on q2.FSourceinterID = s2.FinterID and q2.FSourceEntryID = s2.FEntryID
inner join t_bossczjhdentry t1 on s2.FinterID = t1.FID_SRC and s2.FEntryID = t1.FEntryID_SRC
left join
( select FMultiinterID,FMultiEntryID,FBillID,FDestEntryID,fqty from ICBillRelations_S01ToP01 where FDestEntryID > '0' and fqty > 0 ) r
on q2.FInterID = r.FMultiinterID and q2.FEntryID = r.FMultiEntryID
left join poorderentry c1 on r.FBillID = c1.FinterID and R.FDestEntryID = c1.FEntryID
left join poorderentry c2 on q2.FinterID = c2.FsourceinterID and q2.FentryID = c2.FsourceentryID
where q2.FSourceBillNo in (select b.FBillNo_SRC from t_bossczjhdentry b inner join t_bossczjhd a on b.FID = a.FID
where month(b.FDateyh) in (MONTH(GETDATE()),MONTH(DATEADD(MONTH,1,GETDATE())),MONTH(DATEADD(MONTH,-1,GETDATE())))
and a.FMultiCheckStatus = '16')
and left(t2.fnumber,4) in ('1005') and t2.FName not like '%毛坯%' and t2.FName not like '%扶手盖%'
and q2.FMRPAutoClosed = 1
)
b ;
--select * from #fs

---多个扶手时合并
if object_id('tempdb..#gffs') is not null Begin
drop table #gffs
end
select * into #gffs from (
select ddh,fitemid,
case when fs like '%否%' then '否' else '是' en