K3欠料计算存储过程

Create PROCEDURE [dbo].[ZZsp_mymrp_must_sumt_1]  @i INT   
AS      
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