本文主要是介绍K3欠料计算存储过程，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
Create PROCEDURE [dbo].[ZZsp_mymrp_must_sumt_1] @i INTAS
SET NOCOUNT ON
IF @i=1
BEGIN
SELECT 1 id,'物料分配明细表 ' 表名
UNION ALL
SELECT 2,'物料追踪表 '
UNION ALL
SELECT 3,'物料齐套'
UNION ALL
SELECT 4,'交期表'
UNION ALL
SELECT 5,'物料汇总'
UNION ALL
SELECT 6,'物料汇总分周'
RETURN
END
CREATE TABLE #icmo
(
FTranType INT,
FOrgInterID INT,
FOrgEntryID INT,
id INT IDENTITY(1,1),
FInterid INT DEFAULT 0,
FEntryid INT DEFAULT 0,
FStatus INT DEFAULT 0,
FBillno NVARCHAR(200),
FDate DATETIME,
FEndDate DATETIME,
FType INT DEFAULT 0,
FItemid INT DEFAULT 0,
FQty DECIMAL(18,6) DEFAULT 0,
FDoQty DECIMAL(18,6) DEFAULT 0,
FOrderInterid INT DEFAULT 0,
FOrderType INT DEFAULT 0,
ForderEntryID INT DEFAULT 0,
FMyStuats INT,
FWorkShop INT
)
CREATE TABLE #icmolist
(
FIndex INT DEFAULT 0,
FFromType INT,
id INT IDENTITY(1,1),
FIcmoInterid INT,
FDate DATETIME,
FItemid INT,
FUnitQty DECIMAL(18,6) DEFAULT 0,
FQty DECIMAL(18,6) DEFAULT 0,
FOrgQty DECIMAL(21,10) DEFAULT 0,
FStockedQty DECIMAL(18,6) DEFAULT 0,
FDistributeQty DECIMAL(18,6) DEFAULT 0,
FStockQty DECIMAL(29,18) DEFAULT 0,
FUnStockQty DECIMAL(29,18) DEFAULT 0,
FIcmoQty DECIMAL(29,18) DEFAULT 0,
FICSubQty DECIMAL(29,18) DEFAULT 0,
FPOQty DECIMAL(29,18) DEFAULT 0,
FPRQty DECIMAL(29,18) DEFAULT 0,
FPlanQty DECIMAL(29,18) DEFAULT 0,
FSourceType INT,
FSourceBillNO NVARCHAR(200),
FSourceEntryid INT,
FAppItemInfo NVARCHAR(MAX) DEFAULT '',
FUnDistributeQty DECIMAL(21,10),
FStockTotal DECIMAL(29,18) DEFAULT 0,
FPOQtyTotal DECIMAL(29,18) DEFAULT 0,
FJYQtyTotal DECIMAL(29,18) DEFAULT 0
)
CREATE TABLE #Source
(
id INT IDENTITY(1,1),
FSourceType INT,--1 库存 2采购 3生产任务 4申请
FSourceBillNo NVARCHAR(200),
FDate DATETIME,
FItemid INT,
FQty DECIMAL(18,6),
FDistributedQty DECIMAL(18,6),
FDistributeQty DECIMAL(18,6),
FSourceEntryid INT,
FStatus INT,
FOrderType INT DEFAULT 0,
FUserID INT,
FSupplyID INT,
FDateType INT DEFAULT 0,
Fdtlei NVARCHAR(12) DEFAULT '订单日期'
)
CREATE TABLE #result
(
id INT IDENTITY(1,1),
FicmolistID INT,
FSourceID INT,
FQty DECIMAL(18,6)
)
CREATE TABLE #icbom
(
FID INT IDENTITY(1,1),
FItemid INT
)
CREATE TABLE #icbomEntry
(
FID INT IDENTITY(1,1),
FParentID INT,
FItemID INT,
FLevel INT,
FQty DECIMAL(21,10)
)
create table #iczjsl --在检数量
(
FID int identity(1,1),
FItemID int,
FQtyQJ decimal(21,10),
FQtyWG decimal(21,10),
FQty decimal(21,10)
)
--导入任务单
--下达下任务单，物料不等于5
insert into #icmo
(FTranType,FOrgInterID,FOrgEntryID,FInterID,FEntryid,Ftype,FBillNo,FItemid,Fdate,FEndDate,Fqty,FDoQty,FStatus,FOrderInterid,FOrderType,FWorkShop,ForderEntryID)
select * from
(
select
t1.Ftrantype, --任务单事务类型
t1.Finterid as FOrgInterID, --任务单内码
0 as FOrgEntryID, --任务单行号
t1.Finterid, --任务单内码2
0 as FEntryid, --任务单内码
Ftype as Ftype, --生产类型属性
t1.Fbillno as FBillNO, --生产任务单号
t1.FItemID as FItemid, --生产物料
t1.FPlanCommitDate as Fdate,--计划开工日期
t1.FPlanFinishDate as FEndDate, --计划完工日期
fqty as Fqty, --生产数量
Fstockqty as FDoQty, --入库基本单位数量
t1.FStatus as FStatus, --单据状态
isnull(FOrderinterid,0)+isnull(FPPorderinterid,0) as FOrderInterid, --销售订单号
case when isnull(FOrderinterid,0)<>0 then 81 when isnull(FPPorderinterid,0)<>0 then 87 else 0 end as FOrderType,--销售订单/生产预测单
t1.FWorkShop, --生产车间
t1.FSourceEntryID--源单行号
from icmo t1 -- select distinct FType from icmo select * from t_SubMessage where fid='LX6'
inner join t_icitem t2 on t1.Fitemid=t2.Fitemid
where FMrpClosed=0 --and (t1.FStatus in (1,2,5) or (t1.FStatus=0 and t1.FType<>1055))
and t1.FCancellation=0 and t1.FType<> 11059 AND ISNULL(t1.FPlanCategory,0)<>2
union all
select
t1.FClasstypeID as FTrantype,--事务类型
t1.FInterid as FOrgInterID, --委外单内码
t2.FEntryID as FOrgEntryID, --委外单行号
t1.Finterid, --委外单内码
t2.FEntryID, --委外单行号
1067 as FType,
t1.FBillNO as FBillNO, --委外单号
t2.FItemID, --委外物料
t2.FPayShipDate as Fdate, --委外开始日期
t2.FFetchDate as FEndDate, --委外结束日期
t2.FQty, --委外数量
t2.FStockQty as FDoQty, -- --入库基本单位数量
isnull(t1.FStatus,0) as FStatus, --委外单状态
case when t2.FInterIDOrder_SRC>0 then t2.FInterIDOrder_SRC when t2.FPORInterID>0 then t2.FPORInterID else 0 end as FOrderInterid, --销售订单/生产预测单
case when len(t2.FOrderNo)>0 then 81 when len(t2.FPORNumber)>0 then 87 else 0 end as FOrderType,
t1.FSupplyID,--供应商ID
t2.FEntryID_SRC --源单行号
from ICSubContract t1
inner join ICSubContractEntry t2 on t1.Finterid=t2.Finterid
where FMrpClosed=0 --and Fstatus in (1,2)
and FCancellation=0 and t1.FInterID>0
union all
select
t1.Ftrantype,
t1.Finterid as FOrgInterID,
0 as FOrgEntryID,
t1.Finterid,
0 as FEntryid,
1054 as Ftype,
t1.Fbillno as FBillNO,
t1.FItemID as FItemid,
t1.FPlanBeginDate as Fdate,
t1.FPlanEndDate as FEndDate,
Fplanqty as Fqty,
0 as FDoQty,
t1.FStatus as FStatus,
FOrgSaleInterID+FOrgPPOInterID as FOrderInterid,
case when FOrgSaleInterID<>0 then 81 when FOrgPPOInterID<>0 then 87 else 0 end as FOrderType,
t1.FSourceID as FWorkShop,t1.FOrgEntyrID
from icmrpresult t1 --计划订单
inner join t_icitem t2 on t1.Fitemid=t2.Fitemid
where FMrpClosed=0 and FStatus>0
and t1.FCancellation=0 and t1.FWorkTypeID=55
union all
select
t1.FTrantype,
t1.FinterID as FOrgInterID,
t2.FEntryID as FOrgEntryID,
t1.Finterid,
t2.Fentryid,
70 as Ftype,
t1.FBillNo,
t2.FItemID,
t2.FAPurchTime as FDate,
t2.FFetchTime as FEndDate,
t2.FQty,
t2.FCommitQty,
0,
t2.FSourceInterid,
t2.FSourceTrantype,
0 as FWorkShop,t2.FSourceEntryID
from porequest t1 --采购申请单
inner join porequestEntry t2 on t1.Finterid=t2.Finterid
inner join t_icitem t3 on t3.Fitemid=t2.fitemid
where FCancellation=0 and t2.FMrpclosed=0 and t1.FBizType=12511
UNION
select
t1.FTrantype,t1.FinterID as FOrgInterID,t2.FEntryID as FOrgEntryID,t1.Finterid,t2.Fentryid,81 as Ftype,t1.FBillNo,t2.FItemID,t2.FAdviceConsignDate as FDate,
t2.FDate as FEndDate,t2.FQty-FStockQty,0,t1.fstatus,t2.FSourceInterid,t2.FSourceTrantype,0 as FWorkShop,t2.FSourceEntryID
from SEOrder t1 --销售订单
inner join SEOrderEntry t2 on t1.Finterid=t2.Finterid
inner join t_ICItem t3 on t3.FItemID=t2.FItemID and t3.FPlanTrategy=321
--INNER JOIN t_Organization t3 ON t3.FItemID=t1.FCustID AND (t3.FNumber='81' OR t3.FNumber='K65') --select * from t_Organization where fnumber IN ('81','K65')
where FCancellation=0 and t2.FMrpclosed=0 and t1.fstatus>0
UNION
select
t1.FTrantype,t1.FinterID as FOrgInterID,t2.FEntryID as FOrgEntryID,t1.Finterid,t2.Fentryid,87 as Ftype,t1.FBillNo,t2.FItemID,t2.FNeedDate as FDate,
t2.FNeedDateEnd as FEndDate,case when t2.FQty-FSaleQty<0 then 0 else t2.FQty-FSaleQty end,0,t1.fstatus,t2.FSourceInterid,t2.FSourceTrantype,
0 as FWorkShop,t2.FSourceEntryID
from ppOrder t1 --产品预测单
inner join ppOrderEntry t2 on t1.Finterid=t2.Finterid
inner join t_ICItem t3 on t3.FItemID=t2.FItemID and t3.FPlanTrategy=321
where FCancellation=0 and t1.fstatus>0 and t2.FOrderClosed=0
) a
order by a.Fdate
--展开BOM表
insert into #icbom
(FItemid)
select
distinct Fitemid from #icmo
declare @FLevel int
set @FLevel=0
insert into #icbomEntry
(
FParentID,--父相ID
FItemID, --子物料ID
FLevel,--层次
FQty --用量
)
select
u1.FID,
t2.FItemID,
@FLevel,
(t2.FQty/t1.FQty)/(1-t2.FScrap/100)
from #icbom u1 --将父物料ID,子件物料ID提取
inner join ICBOM t1 on u1.FItemid=t1.FItemID
inner join ICBOMChild t2 on t2.FInterID=t1.FInterID and t1.FUseStatus=1072
while @FLevel<20
and exists
(select 1 from #icbomEntry u1 where u1.Fitemid in
(select Fitemid from icbom where Fbomskip=1058)
and u1.FLevel=@FLevel) --判断#icbomEntry的物料是否在ICBOM里存在，而且#icbomEntry的Level是不是等于同一层
begin
set @FLevel=@FLevel+1
insert into #icbomEntry
(
FParentID,FItemID,FLevel,FQty
)
select
u1.FParentID,t2.FItemID,
@FLevel,
u1.FQty*(t2.FQty/t1.FQty)*(1+t2.FScrap/100)
from #icbomEntry u1
inner join ICBOM t1 on u1.FItemid=t1.FItemID and t1.FUseStatus=1072
inner join ICBOMChild t2 on t2.FInterID=t1.FInterID
where
u1.FLevel=@FLevel-1 and
t1.FBomSkip=1058
delete u1
from #icbomEntry u1
inner join ICBOM t1 on u1.FItemid=t1.FItemID and t1.FUseStatus=1072
where u1.FLevel=@FLevel-1 and t1.FBomSkip=1058
end
declare @ScrapFormat int
select @ScrapFormat=FValue from t_SystemProfile where FCategory='SH' and FKey='FSCRAP_FORMULA'
--生产需发料明细
insert into #icmolist
(FFromType,FIcmoInterid,FDate,FItemid,FUnitQty,FQty,FDistributeQty,FOrgQty)
select FFromType,id,FDate,FItemID,FUnitQty,FQty,FQty,FQty from
(
select
0 as FFromType,u1.id,u1.FDate,t2.FItemid,
t2.FqtyScrap*(1+t2.Fscrap/100) as FUnitQty, --单位用量*（1+不良率/100） 为单位用量
t2.FqtyMust-t2.FStockQty+t2.FQtySupply as FQty -- 计划投料数量-已领料数量+补料数量 为需求数量
from #icmo u1
inner join PPBOM t1 on u1.FInterid=t1.FICMOInterID and u1.FEntryid=t1.FOrderEntryID and u1.FType=t1.FType
inner join PPBOMEntry t2 on t1.FInterID=t2.FInterID
where u1.FStatus<>0 and t2.FMaterielType=371 and u1.FTranType in (85,1007105)
union all
select
1 as FFromType,u1.id,u1.FDate,t2.FItemID,sum(t2.Fqty) as FUnitQty,sum(t2.FQty*(u1.FQty-u1.FDoQty)) as FQty
from #icmo u1
inner join #ICBOM t1 on u1.FItemid=t1.FItemID
inner join #icbomEntry t2 on t1.FID=t2.FParentID
inner join t_icitem t3 on t3.Fitemid=t2.Fitemid
where (u1.FStatus=0 and t3.Ferpclsid<>5 and u1.FTranType in (85,1007105))
or u1.FTranType=70
group by u1.id,u1.FDate,t2.FItemID
union all
select
1 as FFromType,u1.id,u1.FDate,t2.FItemID,sum(t2.Fqty) as FUnitQty,
sum(case when t3.FPutInteger=1 then CEILING(t2.FQty*(u1.FQty-u1.FDoQty)) else round(t2.FQty*(u1.FQty-u1.FDoQty),t3.FQtyDecimal) end) as FQty
from #icmo u1
inner join #ICBOM t1 on u1.FItemid=t1.FItemID
inner join #icbomEntry t2 on t1.FID=t2.FParentID
inner join t_icitem t3 on t3.Fitemid=t2.Fitemid
where t3.Ferpclsid<>5 and u1.FTranType=500
group by u1.id,u1.FDate,t2.FItemID
union all
select 0 as FFromType,u1.id,u1.FDate,u1.FItemID,1 as FUnitQty,sum(u1.FQty) as FQty
from #icmo u1
inner join t_icitem t3 on t3.Fitemid=u1.Fitemid
where t3.Ferpclsid<>5 and u1.FTranType=81
group by u1.id,u1.FDate,u1.FItemID
union all
select 0 as FFromType,u1.id,u1.FDate,u1.FItemID,1 as FUnitQty,sum(u1.FQty) as FQty
from #icmo u1
inner join t_icitem t3 on t3.Fitemid=u1.Fitemid
where t3.Ferpclsid<>5 and u1.FTranType=87
group by u1.id,u1.FDate,u1.FItemID
) a
order by a.id
--替代清单调整
--处理任务和委外替代清单
insert into #icmolist
(FFromType,FicmointerID,FDate,FitemID,FUnitQty,FQty,FDistributeQty,FSourceType,FSourceBillno,FsourceEntryID)
select
t1.FFromType,t1.FIcmoInterid,t1.FDate,t4.FSubsItemID,t1.Funitqty*t4.FSubsQty/t1.Fqty,t4.FSubsQty,t4.FSubsQty,t1.FSourceType,t1.FSourceBillNO,t1.FSourceEntryid
from #icmo u1
inner join #icmolist t1 on u1.id=t1.FicmoInterID
inner join ICSubsItemBill t2 on t2.FUpperBillID=u1.FOrgInterID and t2.FUpperType in (85)
inner join ICSubsItemBillEntry t3 on t3.FID=t2.FID and t3.FGroupID=0 and t3.FItemID=t1.FItemid
inner join ICSubsItemBillEntry t4 on t4.fid=t2.FID and t4.FGroupID<>0
where t4.FSubsQty<=t1.FOrgQty and t2.FStatus=1 and t1.FFromType=1
update t1
set t1.FQty=t1.FQty-t4.FSubsQty,
t1.FUnitQty=t1.FUnitQty*(t1.FQty-t4.FSubsQty)/t1.FQty,
t1.FDistributeQty=t1.FQty-t4.FSubsQty
from #icmo u1
inner join #icmolist t1 on u1.id=t1.FicmoInterID
inner join ICSubsItemBill t2 on t2.FUpperBillID=u1.FOrgInterID and t2.FUpperType in (85)
inner join ICSubsItemBillEntry t3 on t3.FID=t2.FID and t3.FGroupID=0 and t3.FItemID=t1.FItemid
inner join (select fid,sum(FsubsQty) as FsubsQty from ICSubsItemBillEntry where FGroupID<>0 group by fid )t4 on t4.fid=t2.FID
where t4.FSubsQty<=t1.FOrgQty and t2.FStatus=1 and t1.FFromType=1
update t1
set t1.FItemid=t4.FSubsItemID
from #icmo u1
inner join #icmolist t1 on u1.id=t1.FicmoInterID
inner join ICSubsItemBill t2 on t2.FUpperBillID=u1.FOrgInterID and t2.FUpperType in (85)
inner join ICSubsItemBillEntry t3 on t3.FID=t2.FID and t3.FGroupID=0 and t3.FItemID=t1.FItemid
inner join ICSubsItemBillEntry t4 on t4.fid=t2.FID and t4.FGroupID<>0
where t4.FSubsQty>=t1.FOrgQty and t2.FStatus=1 and t1.FFromType=1
--委外
insert into #icmolist
(FFromType,FicmointerID,FDate,FitemID,FUnitQty,FQty,FDistributeQty,FSourceType,FSourceBillno,FsourceEntryID)
select
t1.FFromType,t1.FIcmoInterid,t1.FDate,t4.FSubsItemID,t1.Funitqty*t4.FSubsQty/t1.Fqty,t4.FSubsQty,t4.FSubsQty,t1.FSourceType,t1.FSourceBillNO,t1.FSourceEntryid
from #icmo u1
inner join #icmolist t1 on u1.id=t1.FicmoInterID
inner join ICSubsItemBill t2 on t2.FUpperBillID=u1.FOrgInterID and t2.FUpperType in (1007105) and t2.FUpperEntryID=u1.FOrgEntryID
inner join ICSubsItemBillEntry t3 on t3.FID=t2.FID and t3.FGroupID=0 and t3.FItemID=t1.FItemid
inner join ICSubsItemBillEntry t4 on t4.fid=t2.FID and t4.FGroupID<>0
where t4.FSubsQty<t1.FOrgQty and t2.FStatus=1 and t1.FFromType=1
update t1
set t1.FQty=t1.FQty-t4.FSubsQty,t1.FUnitQty=t1.FUnitQty*(t1.FQty-t4.FSubsQty)/t1.FQty,
t1.FDistributeQty=t1.FQty-t4.FSubsQty
from #icmo u1
inner join #icmolist t1 on u1.id=t1.FicmoInterID
inner join ICSubsItemBill t2 on t2.FUpperBillID=u1.FOrgInterID and t2.FUpperType in (1007105) and t2.FUpperEntryID=u1.FOrgEntryID
inner join ICSubsItemBillEntry t3 on t3.FID=t2.FID and t3.FGroupID=0 and t3.FItemID=t1.FItemid
inner join (select fid,sum(FsubsQty) as FsubsQty from ICSubsItemBillEntry where FGroupID<>0 group by fid )t4 on t4.fid=t2.FID
where t4.FSubsQty<t1.FOrgQty and t2.FStatus=1 and t1.FFromType=1
update t1
set t1.FItemid=t4.FSubsItemID
from #icmo u1
inner join #icmolist t1 on u1.id=t1.FicmoInterID
inner join ICSubsItemBill t2 on t2.FUpperBillID=u1.FOrgInterID and t2.FUpperType in (1007105) and t2.FUpperEntryID=u1.FOrgEntryID
inner join ICSubsItemBillEntry t3 on t3.FID=t2.FID and t3.FGroupID=0 and t3.FItemID=t1.FItemid
inner join ICSubsItemBillEntry t4 on t4.fid=t2.FID and t4.FGroupID<>0
where t4.FSubsQty>=t1.FOrgQty and t2.FStatus=1 and t1.FFromType=1
--计划订单
insert into #icmolist
(FFromType,FicmointerID,FDate,FitemID,FUnitQty,FQty,FDistributeQty,FSourceType,FSourceBillno,FsourceEntryID)
select
t1.FFromType,t1.FIcmoInterid,t1.FDate,t4.FSubsItemID,t1.Funitqty*t4.FSubsQty/t1.Fqty,t4.FSubsQty,t4.FSubsQty,t1.FSourceType,t1.FSourceBillNO,t1.FSourceEntryid
from #icmo u1
inner join #icmolist t1 on u1.id=t1.FicmoInterID
left join ICMO t5 on t5.FInterID=u1.FOrgInterID and t5.FTranType=u1.FTranType
inner join ICSubsItemBill t2 on (t2.FUpperBillID=u1.FOrgInterID or t2.FUpperBillID=t5.FPlanOrderInterID)
and t2.FUpperType in (500)
inner join ICSubsItemBillEntry t3 on t3.FID=t2.FID and t3.FGroupID=0 and t3.FItemID=t1.FItemid
inner join ICSubsItemBillEntry t4 on t4.fid=t2.FID and t4.FGroupID<>0
where t4.FSubsQty<t1.FOrgQty and t2.FStatus=1 and t1.FFromType=1
update t1
set t1.FQty=t1.FQty-t4.FSubsQty,t1.FUnitQty=t1.FUnitQty*(t1.FQty-t4.FSubsQty)/t1.FQty,
t1.FDistributeQty=t1.FQty-t4.FSubsQty
from #icmo u1
inner join #icmolist t1 on u1.id=t1.FicmoInterID
left join ICMO t5 on t5.FInterID=u1.FOrgInterID and t5.FTranType=u1.FTranType
inner join ICSubsItemBill t2 on (t2.FUpperBillID=u1.FOrgInterID or t2.FUpperBillID=t5.FPlanOrderInterID)
and t2.FUpperType in (500)
inner join ICSubsItemBillEntry t3 on t3.FID=t2.FID and t3.FGroupID=0 and t3.FItemID=t1.FItemid
inner join (select fid,sum(FsubsQty) as FsubsQty from ICSubsItemBillEntry where FGroupID<>0 group by fid )t4 on t4.fid=t2.FID
where t4.FSubsQty<t1.FOrgQty and t2.FStatus=1 and t1.FFromType=1
update t1
set t1.FItemid=t4.FSubsItemID
from #icmo u1
inner join #icmolist t1 on u1.id=t1.FicmoInterID
left join ICMO t5 on t5.FInterID=u1.FOrgInterID and t5.FTranType=u1.FTranType
inner join ICSubsItemBill t2 on (t2.FUpperBillID=u1.FOrgInterID or t2.FUpperBillID=t5.FPlanOrderInterID)
and t2.FUpperType in (500)
inner join ICSubsItemBillEntry t3 on t3.FID=t2.FID and t3.FGroupID=0 and t3.FItemID=t1.FItemid
inner join ICSubsItemBillEntry t4 on t4.fid=t2.FID and t4.FGroupID<>0
where t4.FSubsQty>=t1.FOrgQty and t2.FStatus=1 and t1.FFromType=1
--采购申请
insert into #icmolist
(FFromType,FicmointerID,FDate,FitemID,FUnitQty,FQty,FDistributeQty,FSourceType,FSourceBillno,FsourceEntryID)
select
t1.FFromType,t1.FIcmoInterid,t1.FDate,t4.FSubsItemID,t1.Funitqty*t4.FSubsQty/t1.Fqty,t4.FSubsQty,t4.FSubsQty,t1.FSourceType,t1.FSourceBillNO,t1.FSourceEntryid
from #icmo u1
inner join #icmolist t1 on u1.id=t1.FicmoInterID
left join ICSubContractEntry t5 on t5.FInterID=u1.FOrgInterID and 1007105=u1.FTranType
inner join ICSubsItemBill t2 on (
(t2.FUpperBillID=u1.FOrgInterID and t2.FUpperEntryID=u1.FOrgEntryID and u1.FTranType=70)
or
(t2.FUpperBillID=t5.FInterID_SRC and t2.FUpperEntryID=t5.FEntryID_SRC and t5.FClassTypeID_SRC=-70)
)
and t2.FUpperType in (70)
inner join ICSubsItemBillEntry t3 on t3.FID=t2.FID and t3.FGroupID=0 and t3.FItemID=t1.FItemid
inner join ICSubsItemBillEntry t4 on t4.fid=t2.FID and t4.FGroupID<>0
where t4.FSubsQty<t1.FOrgQty and t2.FStatus=1 and t1.FFromType=1
update t1
set t1.FQty=t1.FQty-t4.FSubsQty,t1.FUnitQty=t1.FUnitQty*(t1.FQty-t4.FSubsQty)/t1.FQty,
t1.FDistributeQty=t1.FQty-t4.FSubsQty
from #icmo u1
inner join #icmolist t1 on u1.id=t1.FicmoInterID
left join ICSubContractEntry t5 on t5.FInterID=u1.FOrgInterID and 1007105=u1.FTranType
inner join ICSubsItemBill t2 on (
(t2.FUpperBillID=u1.FOrgInterID and t2.FUpperEntryID=u1.FOrgEntryID and u1.FTranType=70)
or
(t2.FUpperBillID=t5.FInterID_SRC and t2.FUpperEntryID=t5.FEntryID_SRC and t5.FClassTypeID_SRC=-70)
)
and t2.FUpperType in (70)
inner join ICSubsItemBillEntry t3 on t3.FID=t2.FID and t3.FGroupID=0 and t3.FItemID=t1.FItemid
inner join (select fid,sum(FsubsQty) as FsubsQty from ICSubsItemBillEntry where FGroupID<>0 group by fid )t4 on t4.fid=t2.FID
where t4.FSubsQty<t1.FOrgQty and t2.FStatus=1 and t1.FFromType=1
update t1
set t1.FItemid=t4.FSubsItemID
from #icmo u1
inner join #icmolist t1 on u1.id=t1.FicmoInterID
left join ICSubContractEntry t5 on t5.FInterID=u1.FOrgInterID and 1007105=u1.FTranType
inner join ICSubsItemBill t2 on (
(t2.FUpperBillID=u1.FOrgInterID and t2.FUpperEntryID=u1.FOrgEntryID and u1.FTranType=70)
or
(t2.FUpperBillID=t5.FInterID_SRC and t2.FUpperEntryID=t5.FEntryID_SRC and t5.FClassTypeID_SRC=-70)
)
and t2.FUpperType in (70)
inner join ICSubsItemBillEntry t3 on t3.FID=t2.FID and t3.FGroupID=0 and t3.FItemID=t1.FItemid
inner join ICSubsItemBillEntry t4 on t4.fid=t2.FID and t4.FGroupID<>0
where t4.FSubsQty>=t1.FOrgQty and t2.FStatus=1 and t1.FFromType=1
----处理委外仓库多出的物料 LEFT join t_Stock t1 on F_102=a.FSupplyID AND ISNULL(F_102,0)>0 -----每个委外商只可以有一个委外仓库
select FSupplyID,isnull(A.FItemID,t2.FItemID) Fitemid,isnull(t2.FKCqty,0) FKCqty,isnull(A.FWWQty,0) FWWQty,
ISNULL(t2.FKCqty,0)-isnull(A.FWWQty,0) Fqty,
CASE WHEN isnull(t2.FKCqty,0)>isnull(A.FWWQty,0) THEN isnull(t2.FKCqty,0)-isnull(A.FWWQty,0) ELSE 0 END Ffllqty
into #tan160526
from (select a.FSupplyID,c.FItemID,SUM(c.FQtyMust+FQtySupply-c.FStockQty) FWWQty
from ICSubContract a
inner join ICSubContractEntry b on a.FInterID=b.FInterID and a.FClosed=0 and b.FMrpClosed=0 and a.FCancellation=0
inner join PPBOMEntry c on c.FICMOInterID=b.FInterID and c.FOrderEntryID=b.FEntryID and c.FQtyMust+FQtySupply-c.FStockQty<>0
GROUP BY a.FSupplyID,c.FItemID
) A
full join (select t1.FItemID,sum(Fqty) FKCqty
from ICInventory t1
inner join t_Stock t2 on t1.FStockID=t2.FItemID
group by t1.FItemID
having sum(Fqty)>0
) t2 on A.FItemID=t2.FItemID
--select * from #icbom
--select * from #icbomEntry
--select * from #icmo
--select * from #icmolist
--导入资源
--导入库存
insert into
#Source
(FSourceType,Fdate,Fitemid,Fqty,FDistributedQty)
select FSourceType,Fdate,A.Fitemid,A.Fqty-ISNULL(T4.FQTy,0),FDistributedQty
from ( select 1 FSourceType,null Fdate,t1.FItemID,sum(t1.Fqty) as Fqty,sum(t1.Fqty) FDistributedQty
from ICInventory t1
inner join t_Stock t3 on t1.FStockID=t3.FItemID
where t3.FMRPAvail=1
group by t1.FItemID
) A
left join ( select SUM(Ffllqty) FQTy,Fitemid
from #tan160526
group by Fitemid
HAVING SUM(Ffllqty)>0
) t4 on t4.Fitemid=A.FItemID
insert into
#Source
(FSourceType,Fstatus,FItemid,Fdate,Fqty,FDistributedQty,FSourceBillNo,FSourceEntryid,FDateType,FUserID,FSupplyID,Fdtlei)
select
4,FStatus,FItemID,FDate,FOrgQty,FOrgQty,FBillNo,FEntryID,FDateType,FbillerID,FSupplyID,Fdtlei
from (
select
u1.FTranType,t1.FInterID,t1.FEntryID,1 as FPriorityLevel,t1.FItemID,
case when t1.FQty-t1.FStockQty-0-0>0 then 0
else t1.FQty-t1.FStockQty-0-0 end as FOrgQty,
t1.FDate as FDate,1 as FDateType,t1.FNote as FNote,t1.FQty,t1.FStockQty,u1.FBillNo,u1.FStatus,
u1.FbillerID,u1.FSupplyID,'回复交期1' as Fdtlei,u1.FDate DDdate
from POOrder u1
inner join POOrderEntry t1 on u1.FInterID=t1.FInterID and u1.FPlanCategory=1
where u1.FCancellation=0 and t1.FMrpClosed=0 and t1.FQty-t1.FStockQty>0
and t1.FQty-t1.FStockQty-0-0>0
union all
select
u1.FTranType,t1.FInterID,t1.FEntryID,1 as FPriorityLevel,t1.FItemID,
case when t1.FQty-t1.FStockQty-0>0 then 0
else t1.FQty-t1.FStockQty-0 end as FOrgQty,
t1.FDate as FDate,1 as FDateType,t1.FNote as FNote,t1.FQty,t1.FStockQty,u1.FBillNo,u1.FStatus,
u1.FbillerID,u1.FSupplyID,'回复交期2' as fdtlei,u1.FDate DDdate
from POOrder u1
inner join POOrderEntry t1 on u1.FInterID=t1.FInterID and u1.FPlanCategory=1
where u1.FCancellation=0 and t1.FMrpClosed=0 and t1.FQty-t1.FStockQty>0
and t1.FQty-t1.FStockQty-0>0
union all
select
u1.FTranType,t1.FInterID,t1.FEntryID,1 as FPriorityLevel,t1.FItemID,
case when t1.FQty-t1.FStockQty>0 then 0
else t1.FQty-t1.FStockQty end as FOrgQty,
t1.FDate as FDate,1 as FDateType,t1.FNote as FNote,t1.FQty,t1.FStockQty,u1.FBillNo,u1.FStatus,
u1.FbillerID,u1.FSupplyID,'回复交期3' as fdtlei ,u1.FDate DDdate
from POOrder u1
inner join POOrderEntry t1 on u1.FInterID=t1.FInterID and u1.FPlanCategory=1
where u1.FCancellation=0 and t1.FMrpClosed=0 and t1.FQty-t1.FStockQty>0
union all
select
u1.FTranType,t1.FInterID,t1.FEntryID,2 as FPriorityLevel,t1.FItemID,
case when t1.FQty-t1.FStockQty>0
then t1.FQty-t1.FStockQty
else 0 end as FOrgQty,
u1.Fdate as FDate,0 as FDateType,t1.FNote as FNote,t1.FQty,t1.FStockQty,u1.FBillNo,u1.FStatus,
u1.FbillerID,u1.FSupplyID,'订单日期' as fdtlei,u1.FDate DDdate
from POOrder u1
inner join POOrderEntry t1 on u1.FInterID=t1.FInterID and u1.FPlanCategory=1
where u1.FCancellation=0 and t1.FMrpClosed=0
and t1.FQty-t1.FStockQty>0
) a
order by FStatus desc,a.FPriorityLevel,ddDate
insert into
#Source
(FSourceType,FItemid,Fdate,Fqty,FDistributedQty,FSourceBillNo,FSourceEntryid,FUserID,FStatus)
select
6,
t2.FItemID,
t2.FFetchTime,
t2.FQty-t2.FcommitQty,
t2.FQty-t2.FcommitQty,
t1.Fbillno,
t2.Fentryid,
t1.FbillerID,
t1.FStatus
from porequest t1
inner join porequestEntry t2 on t1.Finterid=t2.Finterid and t1.FPlanCategory=1
where FCancellation=0 and t2.FMrpclosed=0 and t1.FBizType=12510 and t2.FQty-t2.FcommitQty>0
order by FStatus desc,FDate
insert into
#Source
(FSourceType,FStatus,FItemid,Fdate,Fqty,FDistributedQty,FSourceBillNo,FUserID,FSupplyID)
select
5,
t1.FStatus,
t1.FItemID, t1.FPlanFinishDate,
fqty-FstockQty,
fqty-FstockQty,
t1.Fbillno,
t1.FBillerID,
t1.FWorkShop
from icmo t1
where t1.FClosed=0 and FMrpClosed=0 and t1.FCancellation=0 and t1.Fstatus in (0,1,2,5)
order by t1.Fstatus,t1.FPlanFinishDate
--委外订单
insert into
#Source
(FSourceType,FItemid,Fdate,Fqty,FDistributedQty,FSourceBillNo,FUserID,FSupplyID,FStatus,FSourceEntryid)
select
7,
t2.FItemID,
t2.FFetchDate,
t2.FQty-FStockQty,
t2.FQty-FStockQty,
t1.FBillNO,
t1.FBillerID,
t1.FSupplyID,
t1.FStatus,
t2.FEntryID
from ICSubContract t1
inner join ICSubContractEntry t2 on t1.Finterid=t2.Finterid
where FClosed=0 and FMrpClosed=0 and FCancellation=0 and t1.FInterID>0 and FClassTypeID>0
--委外申请
insert into
#Source
(FSourceType,FItemid,Fdate,Fqty,FDistributedQty,FSourceBillNo,FUserID,FStatus,FSourceEntryid)
select
8,
t2.FItemID,
t2.FFetchTime,
t2.FQty-t2.FCommitQty ,
t2.FQty-t2.FCommitQty ,
t1.FBillNo,
t1.FBillerID,
t1.FStatus,
t2.FEntryID
from porequest t1
inner join porequestEntry t2 on t1.Finterid=t2.Finterid
where FCancellation=0 and t2.FMrpclosed=0 and t1.FBizType=12511
and t2.FQty-t2.FCommitQty>0
insert into
#Source
(FSourceType,FStatus,FItemid,Fdate,Fqty,FDistributedQty,FSourceBillNo,FSourceEntryid)
select
9,
t1.FStatus,
t1.FItemID,
t1.FPlanEndDate,
FplanQty,
FplanQty,
t1.Fbillno,
0 as FSourceEntryID
from ICMrpResult t1
where FMrpClosed=0 and t1.FCancellation=0 and t1.Fstatus=1
order by t1.Fstatus,t1.FPlanEndDate
--分配资源
create table #sn
(id int identity(1,1),
FItemid int default 0)
insert #sn(FItemid)
select distinct
FItemid
from
(
select Fitemid from #Source
union
select Fitemid from #icmolist
) a
declare @source_sn int
declare @source_id int
declare @source_qty decimal(18,6)
declare @source_fetch_next int
declare @source_fetch_status int
declare @dts_sn int
declare @dts_id int
declare @dts_qty decimal(18,6)
declare @dts_fetch_next int
declare @dts_fetch_status int
declare cur_source cursor for
select t1.id,u1.id as FDtsID,u1.FQty from #Source u1
inner join #sn t1 on u1.FItemid=t1.FItemid
where u1.Fqty>0
order by t1.id,u1.id
declare cur_dts cursor for
select t1.id,u1.id as FSourceID,u1.FQty from #icmolist u1
inner join #sn t1 on u1.FItemid=t1.FItemid
where u1.Fqty>0
order by t1.id,u1.id
open cur_source
open cur_dts
fetch next from cur_source into @source_sn,@source_id,@source_qty
set @source_fetch_status=@@FETCH_STATUS
set @source_fetch_next=0
fetch next from cur_dts into @dts_sn,@dts_id,@dts_qty
set @dts_fetch_status=@@FETCH_STATUS
set @dts_fetch_next=0
while @source_fetch_status=0 and @dts_fetch_status=0
begin
if @dts_sn=@source_sn and @dts_sn<>0 and @source_sn<>0
begin
if @dts_qty>@source_qty and @dts_qty>0 and @source_qty>0
begin
insert into #result
(FicmolistID,FSourceID,FQty)
values
(@dts_id,@source_id,@source_qty)
set @dts_qty=@dts_qty-@source_qty
set @source_qty=0
set @source_id=0
set @source_sn=0
set @source_fetch_next=1
end
if @dts_qty=@source_qty and @dts_qty>0 and @source_qty>0
begin
insert into #result
(FicmolistID,FSourceID,FQty)
values
(@dts_id,@source_id,@source_qty)
set @source_qty=0
set @source_id=0
set @source_sn=0
set @dts_qty=0
set @dts_id=0
set @dts_sn=0
set @source_fetch_next=1
set @dts_fetch_next=1
end
if @dts_qty<@source_qty and @dts_qty>0 and @source_qty>0
begin
insert into #result
(FicmolistID,FSourceID,FQty)
values
(@dts_id,@source_id,@dts_qty)
set @source_qty=@source_qty-@dts_qty
set @dts_qty=0
set @dts_sn=0
set @dts_id=0
set @dts_fetch_next=1
end
end
if @dts_sn>@source_sn
begin
set @source_fetch_next=1
end
if @dts_sn<@source_sn
begin
set @dts_fetch_next=1
end
if @source_qty<=0 or @source_sn=0
begin
set @source_fetch_next=1
end
if @dts_qty<=0 or @dts_sn=0
begin
set @dts_fetch_next=1
end
if @source_fetch_next=1
begin
fetch next from cur_source into @source_sn,@source_id,@source_qty
set @source_fetch_status=@@FETCH_STATUS
set @source_fetch_next=0
end
if @dts_fetch_next=1
begin
fetch next from cur_dts into @dts_sn,@dts_id,@dts_qty
set @dts_fetch_status=@@FETCH_STATUS
set @dts_fetch_next=0
end
end
close cur_source
close cur_dts
deallocate cur_source
deallocate cur_dts
update u1
set u1.Fdistributedqty=u1.FQty-t1.FQty
from #Source u1
inner join
(
select FSourceID,SUM(FQty) as FQty from #result group by FSourceID
) t1 on u1.id=t1.FSourceID
update u1
set u1.FUnDistributeQty=u1.FQty-isnull(t1.FQty,0)
from #icmolist u1
left join
(
select FicmolistID,SUM(FQty) as FQty from #result
where FSourceID in
(select id from #Source where FSourceType=1)
group by FicmolistID
) t1 on u1.id=t1.FicmolistID
update u1
set u1.FDistributeQty=u1.FQty-isnull(t1.FQty,0)
from #icmolist u1
left join
(
select FicmolistID,SUM(FQty) as FQty from #result group by FicmolistID
) t1 on u1.id=t1.FicmolistID
update u1
set u1.FStockQty=isnull(t1.FStockQty,0),
u1.FUnStockQty=isnull(u1.Fqty,0)-isnull(t1.FStockQty,0),
u1.FICSubQty=isnull(t1.FICSubQty,0),
u1.FPOQty=isnull(t1.FPOQty,0),
u1.FPRQty=isnull(t1.FPRQty,0),
u1.FPlanQty=isnull(t1.FPlanQty,0),
u1.FIcmoQty =ISNULL(t1.FICMOQty ,0)
from #icmolist u1
left join
(
select FicmolistID,
sum(case when t1.FSourceType=1 then u1.FQty else 0 end) as FStockQty,
sum(case when t1.FSourceType=4 then u1.FQty else 0 end) as FPOQty,
sum(case when t1.FSourceType=5 then u1.FQty else 0 end) as FICMOQty,
sum(case when t1.FSourceType in (6,8) then u1.FQty else 0 end) as FPRQty,
sum(case when t1.FSourceType=7 then u1.FQty else 0 end) as FICSubQty,
sum(case when t1.FSourceType=9 then u1.FQty else 0 end) as FPlanQty
from #result u1
inner join #Source t1 on u1.FSourceID=t1.id
group by FicmolistID
)t1 on u1.id=t1.FicmolistID
update u1
set u1.FMyStuats=1
from #icmo u1
inner join (select FIcmoInterid,max(Fdistributeqty) FMinDistributeQty from #icmolist group by FIcmoInterid
) t1 on u1.id=t1.FIcmoInterid and isnull(t1.FMinDistributeQty,0)=0 and u1.id not in
(select FIcmoInterid from #icmolist where id in
(
select FicmolistID from #result where FSourceID in (select id from #Source where FSourceType<>1)
)
)
update u1
set u1.FMyStuats=2
from #icmo u1
INNER JOIN (SELECT max(a.FQtyMust+FQtySupply-a.FStockQty) FQTY,a.FICMOInterID,0 FENTryid
FROM PPBOMentry a
INNER JOIN icmo t1 ON a.FICMOInterID=t1.FInterID AND t1.FMrpClosed=0 AND t1.FCancellation=0
GROUP BY a.FICMOInterID
UNION ALL
SELECT max(a.FQtyMust+a.FQtySupply-a.FStockQty) FQTY,a.FICMOInterID,a.FSourceEntryID
from PPBOMEntry a
inner join ICSubContractEntry t2 on t2.FInterID=a.FICMOInterID and t2.FDetailID=a.FSourceEntryID
inner join ICSubContract t2_1 on t2_1.FInterID=t2.FInterID AND t2.FMrpClosed=0 AND t2_1.FCancellation=0
GROUP BY a.FICMOInterID,a.FSourceEntryID
) t1 ON t1.FICMOInterID=u1.FOrgInterID AND t1.FENTryid=u1.FOrgEntryID AND t1.FQTY=0 AND (u1.FTranType=85 OR u1.FTranType=1007105)
--add by lgq 2015-05-20
update t1
set FStockTotal=t2.fqty ,
FPOQtyTotal=t3.fpoqty
from #icmolist t1
left join (select Fitemid,sum(FQty) as fqty from #Source where FSourceType=1 group by FItemid) t2 on t1.fitemid=t2.fitemid
left join (select Fitemid,sum(FQty) as fpoqty from #Source where FSourceType=4 or FSourceType=7 group by FItemid) t3 on t1.FItemid =t3.FItemID
--加入请检数量到表
insert into #iczjsl (FItemID,FQtyQJ,FQtyWG,FQty)
select aa.Fitemid,sum(isnull(FQtyQJ,0)) FQtyQJ,sum(isnull(FQTYRK,0)) FQTYRK,sum(isnull(FQtyQJ,0))-sum(isnull(FQTYRK,0)) FQty
from (
select p2.Fitemid,max(isnull(p2.FQty-FBackQty,0)) FQtyQJ, sum(isnull(case when b1_1.FStatus>0 then b1.fqty else 0 end,0)) FQTYRK,p1.FInterID,p2.FEntryID
from POInStock p1 --请检单
inner join POInStockEntry P2 on p1.FInterID=p2.FInterID and p2.FQty>0 and P2.FOrderType=71 AND FTranType=72 AND p1.FCancellation=0 --请检单
inner join POOrderEntry p on p.FInterID=FOrderInterID and p.FEntryID=FOrderEntryID and p.FMrpClosed=0 --采购订单
left join ICStockBillEntry b1 on b1.FSourceInterId=P2.FInterID and b1.FSourceEntryID=p2.FEntryID and b1.FSourceTranType=72
left join ICStockBill b1_1 on b1_1.FInterID=b1.FInterID AND b1_1.FTranType=1
Where isnull(b1_1.FTranType,1) =1
group by p2.Fitemid,p1.FInterID,p2.FEntryID
UNION ALL
select p2.Fitemid,max(isnull(p2.FQty-FBackQty,0)) FQtyQJ, sum(isnull(case when b1_1.FStatus>0 then b1.fqty else 0 end,0)) FQTYRK,p1.FInterID,p2.FEntryID
from POInStock p1 --请检单
inner join POInStockEntry P2 on p1.FInterID=p2.FInterID and p2.FQty>0 and P2.FOrderType=1007105 and FTranType=72 AND p1.FCancellation=0 --请检单
inner join ICSubContractEntry p4 on p4.FInterID=FOrderInterID and p4.FMrpClosed=0 and p4.FEntryID=FOrderEntryID --委外订单分录
left join ICStockBillEntry b1 on b1.FSourceInterId=P2.FInterID and b1.FSourceEntryID=p2.FEntryID and b1.FSourceTranType=72
left join ICStockBill b1_1 on b1_1.FInterID=b1.FInterID AND b1_1.FTranType=5
Where isnull(b1_1.FTranType,5) =5
group by p2.Fitemid,p1.FInterID,p2.FEntryID
) AA
group by aa.Fitemid
--SELECT FStatus,* FROM poorder WHERE FBillNo='PO160701393'
--SELECT * FROM dbo.POOrderEntry WHERE FInterID=47120
--明细表 物料分配明细表
select
isnull(t8.fbillno,'')+isnull(t9.fbillno,'') as 订单编号,
u1.ForderEntryID 订单行号,
case u1.FTranType when 1007105 then '委外订单' when 85 then '生产任务单' when 70 then '委外申请' when 87 then '预测单'
when 500 then '计划订单' WHEN 81 THEN '销售订单' else '' end as 单据类型,
u1.Fbillno as 单据编号,
ISNULL(t7.FGMPBatchNo,'') 批次,
u1.FOrgEntryID 单据行号,
t7_1.FName as 生产车间,
u1.Fdate as 预计生产日期,
u1.FEndDate as 预计完工日期,
t5.Fnumber as 产品代码,
t5.Fname as 产品名称,
t5.Fmodel as 产品规格型号,
u1.Fqty as 生产数量,
u1.Fdoqty as 已生产数量,
case when u1.FTranType=85 then
case u1.Fstatus when 0 then '计划' when 1 then '下达' when 2 then '下达' when 3 then '结案' when 5 then '确认' else '' end
when u1.FTranType<>85 then
case when u1.Fstatus=0 then '未审核' when u1.FStatus>0 then '审核' else '' end
else '' end
as 状态,
case when u1.FMyStuats=1 then '齐料' else '' end 齐料状态,
t6.Fnumber as 子项物料代码,
t6.Fname as 子项物料名称,
t6.Fmodel as 子项规格型号,
t601.Fname as 计划负责人,
case t6.FerpClsID when 1 then '外购' when 2 then '自制' when 3 then '委外加工' else '' end as 物料类型,
t1.Funitqty as 单位用量,
case when t1.Fqty<0 then 0 else t1.Fqty end as 需发料数量,
--t1.FUnDistributeQty 欠料数,
t1.FStockQty 库存数量,
case when FUnStockQty<0 then 0 else FUnStockQty end 欠库存数量,
FIcmoQty 任务单数量,
FICSubQty 委外订单数量,
FPOQty PO数量,
FPRQty PR数量,
FPlanQty 计划订单数量,
case when t1.Fdistributeqty<0 then 0 else t1.Fdistributeqty end as 需下单数量,
case when a.Fqty<0 then 0 else a.Fqty end as 需下单总数,
t11.FQty as 在检数量,
case t4.FSourceType when 1 then '库存' when 2 then '在检' when 4 then '采购订单' when 5 then '生产任务单' when 6 then '采购申请' when 7 then '委外订单' when 8 then '委外申请' when 9 then '计划订单' else '' end as 源单据类型,
t4.Fsourcebillno as 单据编码,
t4.FSourceEntryid as 行号,
b3.Fname as 制单人,
a1.Fdate as 下单日期,
b2.FBillNO as 申购单号,
b2.Fdate as 申购日期,
t4.Fdate as 交期,
case t4.FSourceType when 5 then case t4.Fstatus when 0 then '计划' when 1 then '下达' when 2 then '下达'
when 3 then '结案' when 5 then '确认' else '' end else case when t4.FStatus>=1 then '审核' when t4.FStatus=0 then '未审核' else '' end end as 源单状态,
t4.Fqty as 可分配数量,
t4.Fdistributedqty as 剩余数量,
t3.Fqty as 分配数量,
a3.Fname as 供给单位,
a2.FNote as 备注信息
into #tan1
from #icmo u1
inner join #icmolist t1 on u1.id=t1.FIcmoInterid
left join #result t3 on t3.FicmolistID=t1.id
left join #Source t4 on t4.id=t3.FSourceID
left join (select Fitemid,sum(FDistributeQty) as Fqty from #icmolist group by FItemid) a on a.FItemid=t1.FItemid
left join t_ICItem t5 on t5.FItemID=u1.FItemid
left join t_ICItem t6 on t6.FItemID=t1.FItemid
left join t_Item t601 on t601.FItemID=t6.FPlanner
left join t_Item t602 on t602.FItemID=t6.FOrderRector
left join ICMO t7 on t7.FInterID=u1.FInterid and u1.FType=t7.FType
left join t_item t7_1 on t7_1.Fitemid=u1.FWorkShop
left join PPOrder t8 on u1.FOrderInterid=t8.FInterID and u1.FOrderType=87
left join SEOrder t9 on u1.FOrderInterid=t9.FInterID and u1.FOrderType=81
left join POOrder a1 on a1.FBillNo=t4.FSourceBillNo and t4.FSourceType=4 and a1.FCancellation=0
left join t_Item a3 on a3.FItemID=a1.FSupplyID
left join POOrderEntry a2 on a1.FInterID=a2.FInterID and a2.FEntryID=t4.FSourceEntryid
left join PORequestEntry b1 on b1.FInterID=a2.FSourceInterId and a2.FSourceEntryID=b1.FEntryID
left join PORequest b2 on b1.FInterID=b2.FInterID and b2.FTranType=a2.FSourceTranType
left join t_User b3 on b3.Fuserid=t4.FuserID
left join #iczjsl t11 on t11.Fitemid=t1.FItemID
order by u1.id,t4.FSourceType
select * from #tan1
-- drop table #TDLB
select *,row_number() over (order by 主料) ID
into #TDLB
from (
select distinct T91_1.FNumber 主料,t92_1.FNumber 替代料,t94_1.FNumber 成品,替代料更新=0
from ICSubsItem_MainItems t91 --主料
inner join ICSubsItem_SubItems t92 on t91.fid=t92.fid --替代料
inner join ICSubsItem_Head t93 on t93.fid=t92.fid --表头
inner join ICSubsItem_Conditions t94 on t94.FID=t91.FID and t94.FGroupID=t92.FGroupID--BOM
left join ICBOM t95 on t95.FInterID=t94.FBOMInterID
left join t_ICItem T91_1 on T91_1.FItemID=t91.FItemID --主料
left join t_ICItem t92_1 on t92_1.FItemID=t92.FItemID --替代料
left join t_ICItem t94_1 on t94_1.FItemID=t95.FItemID --BOM
where t93.FUseStatus=1072 and FDiscontinued=1059 ) a
--where 主料='4.01.03.200006910'
alter table #Tdlb
alter column 替代料 varchar(2000)
declare @TDL varchar(300),@LH varchar(300)
while 1=1
begin
set @TDL=''
set @LH=''
select @LH=成品+主料 from #TDLB where 替代料更新=0
if len(@LH)<=0 break
select @TDL=@TDL+替代料+','
from #TDLB u1
where u1.成品+u1.主料=@LH
set @TDL=left(@TDL,len(@TDL)-1)
update u1
set 替代料=@TDL,替代料更新=1
from #TDLB u1
where 成品+主料=@LH
end
-- select distinct 主料,成品,替代料 from #TDLB
--物料追踪表
select *
from(
select
isnull(t8.fbillno,'')+isnull(t9.fbillno,'') as 订单编号,
u1.ForderEntryID 订单行号,
case u1.FTranType when 1007105 then '委外订单' when 85 then '生产任务单' when 70 then '委外申请' when 87 then '预测单'
when 500 then '计划订单' WHEN 81 THEN '销售订单' else '' end as 单据类型,
u1.Fbillno as 单据编号,
ISNULL(t7.FGMPBatchNo,'') 批次,
u1.FOrgEntryID 单据行号,
t7_1.FName as 生产车间,
t501.Fname as 产品PMC负责人,
u1.Fdate as 预计生产日期,
u1.FEndDate as 预计完工日期,
case when (t12.FIcmoInterid is null and t11.FIcmoInterid is null) then t10.Fdate end 齐套日期,
t5.Fnumber as 产品代码,
t5.Fname as 产品名称,
t5.Fmodel as 产品规格型号,
u1.Fqty as 生产数量,
u1.Fdoqty as 已生产数量,
case when u1.FTranType=85 then
case u1.Fstatus when 0 then '计划' when 1 then '下达' when 2 then '下达' when 3 then '结案' when 5 then '确认' else '' end
when u1.FTranType<>85 then
case when u1.Fstatus=0 then '未审核' when u1.FStatus>0 then '审核' else '' end
else '' end
as 状态,
case when u1.FMyStuats=1 then '齐料' else '' end 齐料状态,
t6.Fnumber as 子项物料代码,
t6.Fname as 子项物料名称,
t6.Fmodel as 子项规格型号,
isnull(t16.替代料,'') 替代料,
case t6.FerpClsID when 1 then '外购' when 2 then '自制' when 3 then '委外加工' else '' end as 物料类型,
t1.Funitqty as 单位用量,
t1.Fqty as 需发料数量,
--t1.FUnDistributeQty 欠料数,
t1.FStockQty 库存数量,
FUnStockQty 欠库存数量,
t1.FStockTotal as 库存总数,
t1.FPOQtyTotal as 在途PO总数,
t15.FQty as 在检数量,
FIcmoQty 任务单数量,
FICSubQty 委外订单数量,
FPOQty PO数量,
FPRQty PR数量,
FPlanQty 计划订单数量,
t1.Fdistributeqty as 需下单数量
from #icmo u1
inner join #icmolist t1 on u1.id=t1.FIcmoInterid
left join t_ICItem t5 on t5.FItemID=u1.FItemid
left join t_Item t501 on t501.FItemID=t5.FPlanner
left join t_ICItem t6 on t6.FItemID=t1.FItemid
left join t_Item t601 on t601.FItemID=t6.FPlanner
left join t_Item t602 on t602.FItemID=t6.FOrderRector
left join ICMO t7 on t7.FInterID=u1.FInterid and u1.FType=t7.FType
left join ICSubContractEntry t71 on t71.FInterID=u1.FInterid and u1.FType=1067
left join t_item t7_1 on t7_1.Fitemid=u1.FWorkShop
left join PPOrder t8 on u1.FOrderInterid=t8.FInterID and u1.FOrderType=87
left join SEOrder t9 on u1.FOrderInterid=t9.FInterID and u1.FOrderType=81
left join #iczjsl t15 on t15.FItemID=t1.FItemid
left join (select distinct u1.主料,u1.成品,u1.替代料 from #TDLB u1) t16 on t16.成品=t5.FNumber and t16.主料=t6.FNumber
left join (
select k1.FIcmoInterid,max(k3.FDate) as FDate
from #icmolist k1
inner join #result k2 on k1.id=k2.FicmolistID
inner join #Source k3 on k3.id=k2.FSourceID
where k3.FSourceType=4 and k3.Fdtlei<>'订单日期'
group by k1.FIcmoInterid
) t10 on t10.FIcmoInterid=u1.id
left join (
select k1.FIcmoInterid,max(k3.FDate) as FDate
from #icmolist k1
inner join #result k2 on k1.id=k2.FicmolistID
inner join #Source k3 on k3.id=k2.FSourceID
where (k3.FSourceType=4 and k3.Fdtlei='订单日期') or k3.FSourceType=6
group by k1.FIcmoInterid
) t11 on t11.FIcmoInterid=u1.id
left join
(
select k1.FIcmoInterid,max(id) as ID
from #icmolist k1
where Fdistributeqty>0
group by k1.FIcmoInterid
) t12 on t12.FIcmoInterid=u1.id
where isnull(t1.fqty,0)>0 ) u2
drop table #TDLB
select distinct
isnull(t8.fbillno,'')+isnull(t9.fbillno,'') as 订单编号,
u1.ForderEntryID 订单行号,
case u1.FTranType when 1007105 then '委外订单' when 85 then '生产任务单' when 70 then '委外申请' when 87 then '预测单'
when 500 then '计划订单' WHEN 81 THEN '销售订单' else '' end as 单据类型,
u1.Fbillno as 单据编号,
ISNULL(t7.FGMPBatchNo,'') 批次,
u1.FOrgEntryID 单据行号,
t7_1.FName as 生产车间,
t501.Fname as 产品PMC负责人,
u1.Fdate as 预计生产日期,
u1.FDate as 预计完工日期,
t5.Fnumber as 产品代码,
t5.Fname as 产品名称,
t5.Fmodel as 产品规格型号,
u1.Fqty as 生产数量,
u1.Fdoqty as 已生产数量,
case when u1.FTranType=85 then
case u1.Fstatus when 0 then '计划' when 1 then '下达' when 2 then '下达' when 3 then '结案' when 5 then '确认' else '' end
when u1.FTranType<>85 then
case when u1.Fstatus=0 then '未审核' when u1.FStatus>0 then '审核' else '' end
else '' end
as 状态,
case when u1.FMyStuats=1 then '齐料' WHEN u1.FMyStuats=2 THEN '完全领料' else '' end 齐料状态,
case when (t12.FIcmoInterid is null and t11.FIcmoInterid is null) then t10.Fdate end 齐套日期,
case when t11.FIcmoInterid is null then 'N' else 'Y' end 是否有订单日期,
case when t12.FIcmoInterid is null then 'N' else 'Y' end 是否有未下单物料,
case when (u1.Fdate>t10.Fdate and t11.FIcmoInterid is null and t12.FIcmoInterid is null) or u1.FMyStuats=1 or u1.FMyStuats=2 then 'Y' else 'N' end 是否满足
from #icmo u1
left join t_ICItem t5 on t5.FItemID=u1.FItemid
left join t_Item t501 on t501.FItemID=t5.FPlanner
left join ICMO t7 on t7.FInterID=u1.FInterid and u1.FType=1054
left join t_item t7_1 on t7_1.Fitemid=u1.FWorkShop
left join PPOrder t8 on u1.FOrderInterid=t8.FInterID and u1.FOrderType=87
left join SEOrder t9 on u1.FOrderInterid=t9.FInterID and u1.FOrderType=81
left join (
select k1.FIcmoInterid,max(k3.FDate) as FDate
from #icmolist k1
inner join #result k2 on k1.id=k2.FicmolistID
inner join #Source k3 on k3.id=k2.FSourceID
where k3.FSourceType=4 and k3.Fdtlei<>'订单日期'
group by k1.FIcmoInterid
) t10 on t10.FIcmoInterid=u1.id
left join (
select k1.FIcmoInterid,max(k3.FDate) as FDate
from #icmolist k1
inner join #result k2 on k1.id=k2.FicmolistID
inner join #Source k3 on k3.id=k2.FSourceID
where (k3.FSourceType=4 and k3.Fdtlei='订单日期') or k3.FSourceType=6
group by k1.FIcmoInterid
) t11 on t11.FIcmoInterid=u1.id
left join
(
select k1.FIcmoInterid,max(id) as ID
from #icmolist k1
where Fdistributeqty>0
group by k1.FIcmoInterid
) t12 on t12.FIcmoInterid=u1.id
--交期表
select
case u1.FSourceType when 1 then '库存' when 2 then '在检' when 4 then '采购订单' when 5 then '生产任务单' when 6 then '采购申请'
when 7 then '委外订单' when 8 then '委外申请' when 9 then '计划订单' else '' end as 源单据类型,
u1.Fsourcebillno as 资源编号,
u1.FSourceEntryid as 资源行号,
isnull(t12.FQty,0)-isnull(t12.FStockQty,0) 资源编号未交数量,
t6.FNumber 材料代码,
t6.FName 材料名称,
t6.FModel 材料规格,
t2.FPOQtyTotal as PO未交总数,
t15.fqty as 在检数量,
--t6.F_131 收货提前天数, select * from t_itempropdesc where fitemclassid=4 and fname like '%采购%'
t61.FName 采购负责人,
t7.FName 计划员,
t4.Fname as 制单人,
u1.Fdate as 交期,
case u1.FSourceType when 5 then case u1.Fstatus when 0 then '计划' when 1 then '下达' when 2 then '下达'
when 3 then '结案' when 5 then '确认' else '' end else
case when u1.FStatus>=1 then '审核' when isnull(u1.FStatus,0)=0 then '未审核' else '' end end as 源单状态,
u1.Fqty as 可分配数量,
u1.Fdistributedqty as 剩余数量,
t1.Fqty as 本行占用数量,
CAST(t3.FDate-1 AS DATETIME) 需求日期,
t5.Fname as 供给单位,
case when u1.FDateType=1 and t3.FDate>=u1.Fdate then 'Y' else 'N' end 是否满足,
--case when u1.FDateType=1 then '回复日期' else '' end,
t3.Fbillno as 需求单据,
t3.FOrgEntryID 需求单据行号,
ISNULL(t73.FGMPBatchNo,'') 批次,
t8.Fnumber as 产品代码,
t8.Fname as 产品名称,
t8.Fmodel as 产品规格,
isnull(t9.Fbillno,'')+isnull(t10.Fbillno,'') as 订单单号,
t3.ForderEntryID 订单行号,
'' 回复交期,
'' 回复数量,
--t12.FRKReplyNote as 回复备注,
u1.Fdtlei 日期类型
--t6.F_105 物料类别,
--t6.F_115
into #tan2
from
#Source u1
inner join #result t1 on u1.id=t1.FSourceID
inner join #icmolist t2 on t2.id=t1.FicmolistID
inner join #icmo t3 on t3.id=t2.FIcmoInterid
left join ICMO t73 on t73.FInterID=t3.FInterid and t3.FType=1054
left join t_User t4 on t4.FUserID=u1.FUserID
left join t_item t5 on t5.FItemID=u1.FSupplyID
left join t_ICItem t6 on t6.FItemID=u1.FItemid
left join t_Emp t61 on t6.FOrderRector=t61.FItemID and t61.FItemID >0
left join t_Emp t7 on t6.FPlanner=t7.FItemID and t7.FItemID >0
left join t_icitem t8 on t8.FItemID=t3.FItemid
left join SEOrder t9 on t9.FInterID=t3.FOrderInterid and t3.FOrderType=t9.FTranType
left join PPOrder t10 on t10.FInterID=t3.FOrderInterid and t3.FOrderType=t10.FTranType
left join POOrder t11 on u1.FSourceBillNo =t11.FBillNo and u1.FSourceType =4 and t11.FCancellation=0
left join POOrderEntry t12 on t11.FInterID =t12.FInterID and u1.FSourceEntryid=t12.FEntryID and u1.FSourceType =4
left join #iczjsl t15 on t15.FItemID=u1.FItemid
where u1.FSourceType in (4,7,6)
select * from #tan2
------------------------------------增加汇总20171004 by zzq
SELECT * INTO #TMP_TAN2 FROM #TAN2
--ALTER TABLE #TMP_TAN2 ADD sum_qty FLOAT NOT NULL DEFAULT(0)
--需求日期小于当前日期的更新为当前日期
UPDATE #TMP_TAN2 SET 需求日期=CONVERT(VARCHAR(10),GETDATE(),120) WHERE 需求日期<GETDATE()
DECLARE
@FDate DATETIME
SELECT @FDate = MIN(需求日期) FROM #TMP_TAN2
-- SELECT 资源编号,资源行号,材料代码,SUM(本行占用数量) AS sum_qty,MIN(需求日期-FDate)) AS ddate,
-- row_number() over(PARTITION by 资源编号,资源行号,材料代码 ORDER BY 资源编号,资源行号,材料代码,MIN(需求日期-FDate)))as id2,
-- CASE
-- WHEN 需求日期 BETWEEN @FDate AND DATEADD(dd,ISNULL(FDate,0),@FDate) THEN @FDate
-- WHEN 需求日期 BETWEEN DATEADD(dd,ISNULL(FDate,0)+1,@FDate) AND DATEADD(dd,ISNULL(FDate,0)*2,@FDate) THEN DATEADD(dd,ISNULL(FDate,0)*2,@FDate)
-- WHEN 需求日期 BETWEEN DATEADD(dd,ISNULL(FDate,0)*2+1,@FDate) AND DATEADD(dd,ISNULL(FDate,0)*3,@FDate) THEN DATEADD(dd,ISNULL(FDate,0)*3,@FDate)
-- END FDate into #zzq
-- FROM #TMP_TAN2
--GROUP BY 资源编号,资源行号,材料代码,
-- CASE
-- WHEN 需求日期 BETWEEN @FDate AND DATEADD(dd,ISNULL(f_115,0),@FDate) THEN @FDate
-- WHEN 需求日期 BETWEEN DATEADD(dd,ISNULL(f_115,0)+1,@FDate) AND DATEADD(dd,ISNULL(f_115,0)*2,@FDate) THEN DATEADD(dd,ISNULL(f_115,0)*2,@FDate)
-- WHEN 需求日期 BETWEEN DATEADD(dd,ISNULL(f_115,0)*2+1,@FDate) AND DATEADD(dd,ISNULL(f_115,0)*3,@FDate) THEN DATEADD(dd,ISNULL(f_115,0)*3,@FDate)
-- END
--ORDER BY 资源编号,资源行号,材料代码,MIN(需求日期-ISNULL(f_115,0))
--update #zzq set ddate=CONVERT(VARCHAR(10),GETDATE(),120)
-- where ddate<CONVERT(VARCHAR(10),GETDATE(),120) or 材料代码 like '%-K%'
--SELECT *FROM #ZZQ WHERE 材料代码 like '%-K%'
--select * from #zzq where 材料代码='21011016001' and 资源编号='PO170705107'
--drop table #zzq
--UPDATE a SET a.sum_qty=b.FBatchAppendQty
--FROM #ZZQ a INNER JOIN t_ICItem b on a.材料代码=b.fnumber
--WHERE a.sum_qty<ISNULL(b.FBatchAppendQty,0) AND ISNULL(b.FBatchAppendQty,0)>0 AND id2=1
-- UPDATE a SET a.sum_qty= (a.sum_qty/ISNULL(b.FBatchAppendQty,0))*ISNULL(b.FBatchAppendQty,0)+ISNULL(b.FBatchAppendQty,0)
--FROM #ZZQ a INNER JOIN t_ICItem b on a.材料代码=b.fnumber
--WHERE a.sum_qty<ISNULL(b.FBatchAppendQty,0) AND ISNULL(b.FBatchAppendQty,0)>0
--AND id2=1
-- SELECT FBatchAppendQty,* FROM dbo.t_ICItem WHERE FNumber='1.30.01.04216'
--SELECT 4900/650
--一厂
--update POOrderEntry set FEntrySelfP0291=null,FEntrySelfP0292=null,FEntrySelfP0293=null,FEntrySelfP0294=null
--UPDATE b SET FEntrySelfP0291=c.ddate,FEntrySelfP0292=c.sum_qty
--FROM dbo.POOrder a INNER JOIN dbo.POOrderEntry b ON b.FInterID = a.FInterID
--INNER JOIN dbo.t_ICItem d ON d.FItemID=b.FItemID
--INNER JOIN #zzq c ON c.资源编号=a.FBillNo AND c.资源行号=b.FEntryID AND c.材料代码=d.fnumber
--WHERE id2=1
-- UPDATE b SET FEntrySelfP0293=c.ddate,FEntrySelfP0294=c.sum_qty
--FROM dbo.POOrder a INNER JOIN dbo.POOrderEntry b ON b.FInterID = a.FInterID
--INNER JOIN dbo.t_ICItem d ON d.FItemID=b.FItemID
--INNER JOIN #zzq c ON c.资源编号=a.FBillNo AND c.资源行号=b.FEntryID AND c.材料代码=d.fnumber
--WHERE id2=2 and c.材料代码 not like '%-K%'
--UPDATE b SET FEntrySelfP0292=FEntrySelfP0292+c.sum_qty,FEntrySelfP0293=NULL
--FROM dbo.POOrder a INNER JOIN dbo.POOrderEntry b ON b.FInterID = a.FInterID
--INNER JOIN dbo.t_ICItem d ON d.FItemID=b.FItemID
--INNER JOIN #zzq c ON c.资源编号=a.FBillNo AND c.资源行号=b.FEntryID AND c.材料代码=d.fnumber
--WHERE id2=2 and c.材料代码 like '%-K%'
--物料追踪表--物料汇总
select t1.FNumber 品号,t1.FName 品名,t1.FModel 规格,需发料数量,库存数量,欠库存数量,库存总数,
在途PO总数,在检数量,分配数量,需下单数量
from (
select
t1.FItemid as 子项物料代码,
sum(t1.Fqty) as 需发料数量,
sum(t1.FStockQty) 库存数量,
sum(FUnStockQty) 欠库存数量,
max(t1.FStockTotal) as 库存总数,
max(t1.FPOQtyTotal) as 在途PO总数,
max(t15.FQty) as 在检数量,
sum(FIcmoQty)+ -- 任务单数量,
sum(FICSubQty)+ -- 委外订单数量,
sum(FPOQty)+ -- PO数量,
sum(FPRQty)+ -- PR数量,
sum(FPlanQty) 分配数量,
sum(t1.Fdistributeqty) as 需下单数量
from #icmo u1
inner join #icmolist t1 on u1.id=t1.FIcmoInterid
left join #iczjsl t15 on t15.FItemID=t1.FItemid
where isnull(t1.fqty,0)>0
group by t1.FItemid
) H
left join t_ICItem t1 on t1.FItemID=h.子项物料代码
order by 子项物料代码
--SELECT DateName(week,'2016-7-22')
--SELECT DateName(week,'2016-7-27')
--物料追踪表--物料汇总分周
select t1.FNumber 品号,t1.FName 品名,t1.FModel 规格,年,周,需发料数量,库存数量,欠库存数量,库存总数,
在途PO总数,在检数量,分配数量,需下单数量 from (
select
t1.FItemid as 子项物料代码,
DateName(year,u1.Fdate) 年,
DateName(week,u1.Fdate) 周,
sum(t1.Fqty) 需发料数量,
max(t1.FStockQty) 库存数量,
sum(FUnStockQty) 欠库存数量,
max(t1.FStockTotal) 库存总数,
max(t1.FPOQtyTotal) 在途PO总数,
max(t15.FQty) 在检数量,
sum(FIcmoQty)+-- 任务单数量,
sum(FICSubQty)+-- 委外订单数量,
sum(FPOQty)+-- PO数量,
sum(FPRQty)+-- PR数量,
sum(FPlanQty) 分配数量,
sum(t1.Fdistributeqty) 需下单数量
from #icmo u1
inner join #icmolist t1 on u1.id=t1.FIcmoInterid
left join #iczjsl t15 on t15.FItemID=t1.FItemid
where isnull(t1.fqty,0)>0
group by t1.FItemid,DateName(week,u1.Fdate),DateName(year,u1.Fdate)
) H
left join t_ICItem t1 on t1.FItemID=h.子项物料代码
order by 子项物料代码,周
---取消订单
select 源单据类型,资源编号,资源行号,源单数量,取消数量,批次,t.FNumber 材料代码,t.FName 材料名称,t.FModel 材料规格,cast(交期 as varchar) 交期,t1.FName 制单人,
t2.FName 供应商,源单状态,计划类别
into #tan160708
from ( select '采购订单' 源单据类型,a.FBillNo 资源编号,b.FEntryID 资源行号,b.FQty 源单数量,b.FQty-b.FStockQty 取消数量,'' 批次,
b.FItemID,b.FDate 交期,a.FBillerID,a.FSupplyID,case FStatus when 0 then '未审核' else '审核' end 源单状态,
case FPlanCategory when 1 then '计划内' else '计划外' end 计划类别
from POOrder a
inner join POOrderEntry b on a.FInterID=b.FInterID and a.FClosed=0 and a.FCancellation=0 and b.FMrpClosed=0 and b.FQty-b.FStockQty>0
--union all
--select '采购申请',a.FBillNo,b.FEntryID,b.FQty,b.FQty-b.FCommitQty,'',b.FItemID,b.FAPurchTime,a.FBillerID,b.FSupplyID,
-- case FStatus when 0 then '未审核' else '审核' end 源单状态 ,case FPlanCategory when 1 then '计划内' else '计划外' end 计划类别
--from PORequest a
--inner join PORequestEntry b on a.FInterID=b.FInterID and a.FClosed=0 and a.FCancellation=0 and b.FMrpClosed=0 and b.FQty-b.FCommitQty>0
--union all
--select '生产任务单',a.FBillNo,0,a.FQty,a.FQty-a.FCommitQty,'',a.FItemID,a.FPlanFinishDate,a.FBillerID,a.FSupplyID
-- ,case FStatus when 0 then '计划' when 1 then '下达' when 2 then '下达' when 3 then '结案' when 5 then '确认' else '' end 源单状态,
-- case FPlanCategory when 1 then '计划内' else '计划外' end 计划类别
--from ICMO a
--where a.FClosed=0 and a.FCancellation=0 and a.FMrpClosed=0 and a.FQty-a.FCommitQty>0
--union all
--select '委外加工',a.FBillNo,0,b.FQty,b.FQty-b.FCommitQty,'',b.FItemID,b.FFetchDate,a.FBillerID,a.FSupplyID,
-- case FStatus when 0 then '未审核' else '审核' end 源单状态 ,case FPlanCategory when 1 then '计划内' else '计划外' end 计划类别
--from ICSubContract a
--inner join ICSubContractEntry b on a.FInterID=b.FInterID and a.FClosed=0 and a.FCancellation=0 and b.FMrpClosed=0 AND a.finterid>0 and b.FQty-b.FCommitQty>0
--union all
--select '计划订单',a.FBillNo,0,a.FPlanQty,FPlanQty,'',a.FItemID,a.FPlanEndDate,a.FBillerID,a.FSupplyID,
-- case FStatus when 0 then '未审核' else '审核' end 源单状态 ,case FPlanCategory when 1 then '计划内' else '计划外' end 计划类别
--from ICMrpResult a
--where a.FStatus=1 and a.FCancellation=0 and a.FMrpClosed=0
) s
inner join t_ICItem t on t.FItemID=s.FItemID and LEFT(FNumber,1)<>'8'
left join t_user t1 on t1.FUserID=s.FBillerID
left join t_Supplier t2 on t2.FItemID=s.FSupplyID
delete u
from #tan160708 u
inner join #tan1 t1 on u.资源编号=t1.单据编码 and isnull(u.资源行号,0)=isnull(t1.行号,0)
insert into #tan160708(源单据类型,资源编号,资源行号,源单数量,取消数量,批次,材料代码,材料名称,材料规格,交期,制单人,供应商,源单状态,计划类别)
select a.源单据类型,a.单据编码,isnull(a.行号,0),a.可分配数量,a.剩余数量,'',a.子项物料代码,a.子项物料名称,a.子项规格型号,
cast(a.交期 as varchar),isnull(a.制单人,''),isnull(a.供给单位,''),a.源单状态,'计划内'
from #tan1 a
inner join t_ICItem t on t.FNumber=a.子项物料代码 and LEFT(FNumber,1)<>'8' and a.剩余数量>0 and a.源单据类型='采购订单'
select distinct a.*,isnull(b.FQtyMin,0) 最小订购量,isnull(b.FBatchAppendQty,1) 最小包装
from #tan160708 a
inner join t_ICItem b on a.材料代码=b.FNumber
where 取消数量>0
---子件开工日期更新
select distinct a.源单据类型,a.单据编号,a.订单编号,a.产品代码,a.子项物料代码,a.子项物料名称,a.子项规格型号,a.批次,a.生产数量,
a.已生产数量,a.预计生产日期,b.调整完工日期
from #tan1 a
inner join (select a.产品代码,a.子项物料代码,min(a.预计生产日期)-1 调整完工日期
from #tan1 a
group by 产品代码,子项物料代码
) b on a.产品代码=b.产品代码 and a.子项物料代码=b.子项物料代码 and b.调整完工日期 is not null
where a.源单据类型 in ('生产任务单','计划订单','委外订单')
--增加到货计划表
select a.源单据类型,供给单位,资源编号,资源行号,材料代码,b.FBatchAppendQty 最小包装,a.资源编号未交数量,需求日期 需求日期,sum(a.本行占用数量) 需求数量,
DateName(week,需求日期) 周,CAST(0 AS dec(18,4)) 周需求数
INTO #tan21
from #tan2 a
inner join t_icitem b on a.材料代码=b.FNumber
where a.源单据类型<>'采购申请'
group by a.源单据类型,供给单位,资源编号,资源行号,材料代码,a.资源编号未交数量,需求日期,DateName(week,需求日期) ,FBatchAppendQty
UPDATE a
SET a.周需求数=CEILING(b.需求数量/CASE WHEN isnull(最小包装,0)=0 THEN 1 ELSE 最小包装 END)*CASE WHEN isnull(最小包装,0)=0 THEN 1 ELSE 最小包装 END
from #tan21 a
INNER JOIN (SELECT SUM(需求数量) 需求数量,材料代码,周 FROM #tan21 GROUP BY 材料代码,周) b ON a.周=b.周 AND a.材料代码=b.材料代码
SELECT * FROM #tan21
DROP TABLE #tan21
--委外仓库多余库存表
select t1.FName 供应商,t2.FNumber 物料代码,t2.FName 物料名称,t2.FModel 规格型号,a.FKCqty 库存,a.FWWQty 委外未领,a.Fqty 库存余量,
FBatchAppendQty 最小包装,CASE WHEN a.Fqty-FBatchAppendQty>0 THEN a.Fqty-FBatchAppendQty ELSE 0 end 余量,
isnull(t3.fdate,'半年以上') 最后调入,isnull(t4.fdate,'半年以上')最后出库
from #tan160526 a
inner join t_Supplier t1 on t1.FItemID=a.FSupplyID
inner join t_ICItem t2 on t2.FItemID=a.Fitemid
left join (select convert(varchar(10),Max(FDate),121) fdate,b.FItemID
from ICStockBill a
inner join ICStockBillEntry b on a.FInterID=b.FInterID and a.FTranType=41 and FStatus>0 and FCancellation=0 and GETDATE()-FDate<=180
inner join t_Stock t1 on t1.FItemID=b.FDCStockID
--inner join t_Supplier t2 on t2.FItemID=
group by b.FItemID) t3 on t3.FItemID=a.Fitemid
left join (select convert(varchar(10),Max(FDate),121) fdate,b.FItemID
from ICStockBill a
inner join ICStockBillEntry b on a.FInterID=b.FInterID and a.FTranType=28 and FStatus>0 and FCancellation=0 and GETDATE()-FDate<=180
inner join t_Stock t1 on t1.FItemID=b.FDCStockID
--inner join t_Supplier t2 on t2.FItemID=t1.F_102
group by b.FItemID) t4 on t4.FItemID=a.Fitemid
where a.Fqty>0
select a.资源编号,材料代码,材料名称,材料规格,isnull(FBatchAppendQty,1) 最小包装,
case when 需求日期-convert(varchar(10),GETDATE(),121)<=7 then 1
when (需求日期-convert(varchar(10),GETDATE(),121)>7 and 需求日期-convert(varchar(10),GETDATE(),121)<=14) then 2
else 3
end 周,
MAX(需求日期) 最大需求日期,SUM(本行占用数量) 需求数量,
CEILING(cast(SUM(本行占用数量) AS dec(18,2))/CASE WHEN isnull(FBatchAppendQty,0)=0 THEN 1 ELSE FBatchAppendQty END)*CASE WHEN isnull(FBatchAppendQty,0)=0 THEN 1 ELSE FBatchAppendQty END 增量调整,cast(0 as dec(18,2)) 双周数量,cast(0 as dec(18,2)) 双周增量调整,
isnull(FQtyMin,0) 最小订购量
into #tan160721
from #tan2 a
inner join t_ICItem t1 on a.材料代码=t1.FNumber
where a.源单据类型='采购订单' and a.源单状态='未审核'
group by a.资源编号,材料代码,材料名称,材料规格,
case when 需求日期-convert(varchar(10),GETDATE(),121)<=7 then 1 when (需求日期-convert(varchar(10),GETDATE(),121)>7 and 需求日期-convert(varchar(10),GETDATE(),121)<=14) then 2 else 3 end,
FBatchAppendQty,isnull(FQtyMin,0)
update u1
set u1.双周数量=isnull(t1.Fqty,0),最小包装=case when 最小包装=0 then 1 else 最小包装 end
from #tan160721 u1
left join (select SUM(需求数量) Fqty,资源编号,材料代码 from #tan160721 where 周<=DateName(week,GETDATE())+1 group by 资源编号,材料代码
) t1 on t1.材料代码=u1.材料代码 and t1.资源编号=u1.资源编号
where 周<=DateName(week,GETDATE())+1
update #tan160721 set 双周增量调整=CEILING(双周数量/case when 最小包装=0 then 1 else 最小包装 end)*case when 最小包装=0 then 1 else 最小包装 end
update #tan160721 set 双周增量调整=case when 双周增量调整<最小订购量 then 最小订购量 else 双周增量调整 end where 双周增量调整>0
update #tan160721 set 增量调整=case when 增量调整<最小订购量 then 最小订购量 else 增量调整 end where 增量调整>0
select 资源编号+材料代码,* from #tan160721
drop table #tan160721
--select * from ICTemplateEntry where FID='P02'
UPDATE #tan2 SET 需求日期=CASE WHEN 需求日期<convert(varchar(10),GETDATE(),121) THEN convert(varchar(10),GETDATE(),121) ELSE 需求日期 END
--UPDATE poorderentry SET FEntrySelfP0269=NULL,FEntrySelfP0284=null,FEntrySelfP0268=null,FEntrySelfP0283=null,FEntrySelfP0286=null,FEntrySelfP0287=null,FEntrySelfP0288=0 WHERE fmrpclosed=0
----更新总须求数量--------
--UPDATE t2
--SET t2.FEntrySelfP0288=t2.FQty-t2.FStockQty-a.fqty
--FROM ( SELECT SUM(本行占用数量) FQty,max(需求日期) 需求日期,资源编号,资源行号
-- FROM #tan2 WHERE 源单据类型='采购订单'
-- GROUP BY 资源编号,资源行号
-- ) a
--INNER JOIN poorder t1 ON t1.fbillno=a.资源编号
--INNER JOIN poorderentry t2 ON t1.finterid=t2.finterid AND t2.fentryid=a.资源行号
--WHERE a.FQty>0
--update POOrderEntry set FEntrySelfP0288=0
--update POOrderEntry set FEntrySelfP0286=0 WHERE FEntrySelfP0286 IS NULL
--update POOrderEntry set FEntrySelfP0288=fqty where isnull(FEntrySelfP0286,0)=0 and FQty>0
--FEntrySelfP0288：本行多余数量
--FEntrySelfP0286：本行总需求数量
----更新第一周日期 select * from ictemplateentry where fid='P01'
--UPDATE t2
--SET t2.FEntrySelfP0269=b.最小日期,t2.FEntrySelfP0268=a.fqty,
----case when t1.fstatus>0 then a.fqty ELSE CEILING(cast(a.fqty AS dec(18,2))/CASE WHEN isnull(FBatchAppendQty,0)=0 THEN 1 ELSE FBatchAppendQty END)*FBatchAppendQty END,
--t2.fdate=b.最小日期
----SELECT a.fqty,a.资源编号,a.资源行号,b.最小日期
--FROM ( SELECT SUM(本行占用数量) FQty,DateName(week,需求日期) 周,资源编号,资源行号
-- FROM #tan2 WHERE 源单据类型='采购订单'
-- GROUP BY DateName(week,需求日期),资源编号,资源行号
-- ) a
--INNER JOIN (SELECT MIN(DateName(week,需求日期)) 周,CAST(MIN(需求日期) AS DATETIME) 最小日期,资源编号,资源行号
-- FROM #tan2 WHERE 源单据类型='采购订单'
-- GROUP BY 资源编号,资源行号
-- ) b ON a.周=b.周 AND a.资源编号=b.资源编号 AND a.资源行号=b.资源行号
--INNER JOIN poorder t1 ON t1.fbillno=a.资源编号
--INNER JOIN poorderentry t2 ON t1.finterid=t2.finterid AND t2.fentryid=a.资源行号
--INNER JOIN dbo.t_ICItem t3 ON t3.FItemID=t2.FItemID
--WHERE a.FQty>0
------删除第一周数据
--delete a
--FROM #tan2 a
--INNER JOIN (SELECT MIN(DateName(week,需求日期)) 周,资源编号,资源行号
-- FROM #tan2 WHERE 源单据类型='采购订单'
-- GROUP BY 资源编号,资源行号
-- ) b ON DateName(week,a.需求日期)=b.周 AND a.资源编号=b.资源编号 AND a.资源行号=b.资源行号
------更新第二周须求日期
--UPDATE t2
--SET t2.FEntrySelfP0284=convert(varchar(10),b.最小日期,121),t2.FEntrySelfP0283=a.fqty
----case when t1.fstatus>0 then a.fqty ELSE CEILING(cast(a.fqty AS dec(18,2))/CASE WHEN isnull(FBatchAppendQty,0)=0 THEN 1 ELSE FBatchAppendQty END)*FBatchAppendQty END
--FROM ( SELECT SUM(本行占用数量) FQty,DateName(week,需求日期) 周,资源编号,资源行号
-- FROM #tan2 WHERE 源单据类型='采购订单'
-- GROUP BY DateName(week,需求日期),资源编号,资源行号
-- ) a
--INNER JOIN (SELECT MIN(DateName(week,需求日期)) 周,CAST(MIN(需求日期) AS DATETIME) 最小日期,资源编号,资源行号
-- FROM #tan2 WHERE 源单据类型='采购订单'
-- GROUP BY 资源编号,资源行号
-- ) b ON a.周=b.周 AND a.资源编号=b.资源编号 AND a.资源行号=b.资源行号
--INNER JOIN poorder t1 ON t1.fbillno=a.资源编号
--INNER JOIN poorderentry t2 ON t1.finterid=t2.finterid AND t2.fentryid=a.资源行号
--INNER JOIN dbo.t_ICItem t3 ON t3.FItemID=t2.FItemID
--WHERE a.FQty>0
----=========================更新采购申请=======================================
--UPDATE porequestentry SET FEntrySelfP0141=NULL,FEntrySelfP0143=null,FEntrySelfP0142=null,FEntrySelfP0144=null,FEntrySelfP0146=null,FEntrySelfP0147=0 WHERE fmrpclosed=0
------更新总须求数量--------
--UPDATE t2
--SET t2.FEntrySelfP0146=ISNULL(a.fqty,0),t2.FEntrySelfP0147=ISNULL(t2.FQty-t2.FCommitQty-a.FQty,0)
--FROM ( SELECT SUM(本行占用数量) FQty,资源编号,资源行号
-- FROM #tan2 WHERE 源单据类型='采购申请'
-- GROUP BY 资源编号,资源行号
-- ) a
--INNER JOIN porequest t1 ON t1.fbillno=a.资源编号
--INNER JOIN porequestentry t2 ON t1.finterid=t2.finterid AND t2.fentryid=a.资源行号
--WHERE a.FQty>0
------更新第一周日期 select * from ictemplateentry where fid='P01'
--UPDATE t2
--SET t2.FEntrySelfP0141=b.最小日期,t2.FEntrySelfP0142=a.fqty,
----case when t1.fstatus>0 then a.fqty ELSE CEILING(cast(a.fqty AS dec(18,2))/CASE WHEN isnull(FBatchAppendQty,0)=0 THEN 1 ELSE FBatchAppendQty END)*FBatchAppendQty END ,
--t2.FFetchTime=b.最小日期
----SELECT a.fqty,a.资源编号,a.资源行号,b.最小日期
--FROM ( SELECT SUM(本行占用数量) FQty,DateName(week,需求日期) 周,资源编号,资源行号
-- FROM #tan2 WHERE 源单据类型='采购申请'
-- GROUP BY DateName(week,需求日期),资源编号,资源行号
-- ) a
--INNER JOIN (SELECT MIN(DateName(week,需求日期)) 周,CAST(MIN(需求日期) AS DATETIME) 最小日期,资源编号,资源行号
-- FROM #tan2 WHERE 源单据类型='采购申请'
-- GROUP BY 资源编号,资源行号
-- ) b ON a.周=b.周 AND a.资源编号=b.资源编号 AND a.资源行号=b.资源行号
--INNER JOIN porequest t1 ON t1.fbillno=a.资源编号
--INNER JOIN porequestentry t2 ON t1.finterid=t2.finterid AND t2.fentryid=a.资源行号
--INNER JOIN dbo.t_ICItem t3 ON t3.FItemID=t2.FItemID
--WHERE a.FQty>0
------删除第一周数据
--delete a
--FROM #tan2 a
--INNER JOIN (SELECT MIN(DateName(week,需求日期)) 周,资源编号,资源行号
-- FROM #tan2 WHERE 源单据类型='采购申请'
-- GROUP BY 资源编号,资源行号
-- ) b ON DateName(week,a.需求日期)=b.周 AND a.资源编号=b.资源编号 AND a.资源行号=b.资源行号
------更新第二周须求日期
--UPDATE t2
--SET t2.FEntrySelfP0143=convert(varchar(10),b.最小日期,121),t2.FEntrySelfP0144=a.fqty
----case when t1.fstatus>0 then a.fqty ELSE CEILING(cast(a.fqty AS dec(18,2))/CASE WHEN isnull(FBatchAppendQty,0)=0 THEN 1 ELSE FBatchAppendQty END)*FBatchAppendQty END
--FROM ( SELECT SUM(本行占用数量) FQty,DateName(week,需求日期) 周,资源编号,资源行号
-- FROM #tan2 WHERE 源单据类型='采购申请'
-- GROUP BY DateName(week,需求日期),资源编号,资源行号
-- ) a
--INNER JOIN (SELECT MIN(DateName(week,需求日期)) 周,CAST(MIN(需求日期) AS DATETIME) 最小日期,资源编号,资源行号
-- FROM #tan2 WHERE 源单据类型='采购申请'
-- GROUP BY 资源编号,资源行号
-- ) b ON a.周=b.周 AND a.资源编号=b.资源编号 AND a.资源行号=b.资源行号
--INNER JOIN porequest t1 ON t1.fbillno=a.资源编号
--INNER JOIN porequestentry t2 ON t1.finterid=t2.finterid AND t2.fentryid=a.资源行号
--INNER JOIN dbo.t_ICItem t3 ON t3.FItemID=t2.FItemID
--WHERE a.FQty>0
drop table #tan1
drop table #tan2
drop table #tan160526
drop table #ICMO
drop table #ICMOList
drop table #Source
drop table #sn
drop table #result
drop table #icbom
drop table #icbomEntry
drop table #iczjsl
set nocount off
select * from #tan1
select * from #tan2
select * from #tan160526
select * from #ICMO
select * from #ICMOList
select * from #Source
select * from #sn
select * from #result
select * from #icbom
select * from #icbomEntry
select * from #iczjsl
这篇关于K3欠料计算存储过程的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
