按订单跟踪物料的采购情况

zhashutiao

版本:k3 v10.4
前提:MRP计算的时候只能分录合并,不可以整单合并,在相应的单据上增加销售订单号、产品代码、产品名称
create trigger tr_update on icmrpresult
for insert,update
as
          declare @fstatus int
          select @fstatus=fstatus from inserted
          if @fstatus=3
          begin
            update t1 set t1.fentryselfp0126=t4.fnumber,t1.fentryselfp0127=t4.fname
            from porequestentry t1
                inner join icmrpresult t2 on t1.fplanorderinterid=t2.fplanorderinterid
                inner join seorderentry t3 on t3.finterid=t2.forgsaleinterid and t3.fentryid=t2.forgentyrid
                inner join t_icitem t4 on t3.fitemid=t4.fitemid
           
   end


CREATE PROCEDURE PR_SALES
@FOrderBillNo varchar(20)
AS
CREATE TABLE #SalesCount(
FOrderBillno varchar(10),
FNumber varchar(20),
FName varchar(20),
FCNumber varchar(20),
FCName varchar(20),
FXQty decimal(28,13),
FSQty decimal(28,13),
FCQty decimal(28,13),
FTQty decimal(28,13),
FInQty decimal(28,13),
FOutQty decimal(28,13)

)
--插入投料单需求数量
Insert into #Salescount(FOrderBillNo,FNumber,FName,FCNumber,FCName,FXQty,FOutQty)
Select t1.FOrderBillno,t3.FNumber,t3.Fname,t4.FNumber,t4.FName,t2.FAuxQtyPick,t2.FQty
From PPBom t1
Inner Join PPBomEntry t2 on t1.FInterID=t2.FInterID
Inner Join t_ICItem t3 on t3.FItemID=t1.FItemID
Inner Join t_ICItem t4 on t4.FItemID=t2.FItemID
Where t1.FOrderBillno like [email=]'%'+@FOrderBillNo+'%'[/email]

--插入采购申请单数量
Insert into #Salescount(FOrderBillNo,FNumber,FName,FCNumber,FCName,FSQty)
Select t2.FSourceBillNo,t2.FEntrySelfP0128,t2.FEntrySelfP0129,t3.FNumber,t3.FName,t2.FQty
From PORequest t1
Inner Join PORequestEntry t2 on t1.FInterID=t2.FInterID
Inner Join t_ICItem t3 on t2.FItemID=t3.FItemID
where t2.FSourceBillNo [email=like]like'%'+@FOrderBillNo+'%'[/email]
--插入采购订单数量
Insert into #Salescount(FOrderBillNo,FNumber,FName,FCNumber,FCName,FCQty)
Select t2.FEntrySelfP0247,t2.FEntrySelfP0248,t2.FEntrySelfP0249,t3.FNumber,t3.FName,t2.FQty
From POOrder t1
Inner Join POOrderEntry t2 on t1.FInterID=t2.FInterID
Inner Join t_ICItem t3 on t3.FItemID=t2.FItemID

where t2.FEntrySelfP0247 [email=like]like'%'+@FOrderBillNo+'%'[/email]
--插入收料通知单数量
Insert into #Salescount(FOrderBillNo,FNumber,FName,FCNumber,FCName,FTQty)
Select t2.FEntrySelfP0338,t2.FEntrySelfP0339,t2.FEntrySelfP0340,t3.FNumber,t3.FName,t2.FQty
From POInStock t1
Inner Join POInStockEntry t2 on t1.FInterID=t2.FInterID
Inner Join t_ICItem t3 on t2.FItemID=t2.FItemID
where t2.FEntrySelfP0338 [email=like]like'%'+@FOrderBillNo+'%'[/email]
--插入外购入库单数量
Insert into #Salescount(FOrderBillNo,FNumber,FName,FCNumber,FCName,FInQty)
Select t2.FEntrySelfA0155,t2.FEntrySelfA0156,t2.FEntrySelfA0157,t3.FNumber,t3.FName,t2.FQty
From ICStockbill t1
Inner Join ICStockbillEntry t2 on t1.FInterID=t2.FInterID
Inner Join t_ICItem t3 on t2.FItemID=t2.FItemID
where t1.FTranType=1 and t2.FEntrySelfA0155 [email=like]like'%'+@FOrderBillNo+'%'[/email]
--数据汇总
Select FOrderBillNo,FNumber,FName,FCNumber,FCName,sum(FXQty) as FXQty,sum(FSQty) as FSQty,sum(FCQty) as FCQty,sum(FTQty) as FTQty,
sum(FInQty) as FInQty,sum(FOutQty) as FOutQty
From #salescount
Group By FOrderBillNo,FNumber,FName,FCNumber,FCName
Drop Table #SalesCount