将K3所有订单需求、MRP运算、库存及在途数据等数据整体展示,适合计划部门根据报表数据统筹考量物料供给情况,特制作此报表及Excel集成工具,以减少手工汇总统计的工作量。
本代码适用于金蝶K3 WISE 12.2版本
1、K3后台mssql数据库创建存储过程:
-
create procedure [dbo].[sp_mymrp_must_sum]
-
as
-
set nocount on
-
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,
-
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,
-
FMyStuats 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,
-
FSourceType int,
-
FSourceBillNO nvarchar(200),
-
FSourceEntryid int,
-
FAppItemInfo nvarchar(max) default '',
-
FUnDistributeQty decimal(21,10) 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
-
)
-
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)
-
)
-
--导入任务单
-
--下达下任务单,物料不等于5
-
insert into #icmo
-
(FTranType,FOrgInterID,FOrgEntryID,FInterID,FEntryid,Ftype,FBillNo,FItemid,Fdate,Fqty,FDoQty,FStatus,FOrderInterid,FOrderType)
-
select * from
-
(
-
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.FPlanCommitDate as Fdate,
-
fqty as Fqty,
-
Fstockqty as FDoQty,
-
t1.FStatus as FStatus,
-
FOrderinterid+FPPorderinterid as FOrderInterid,
-
case when FOrderinterid<>0 then 81 when FPPorderinterid<>0 then 87 else 0 end as FOrderType
-
from icmo t1
-
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
-
union
-
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.FQty,
-
t2.FStockQty as FDoQty,
-
t1.FStatus as FStatus,
-
0 as FOrderInterid,
-
0 as FOrderType
-
from ICSubContract t1
-
inner join ICSubContractEntry t2 on t1.Finterid=t2.Finterid
-
where FMrpClosed=0 and Fstatus in (1,2) and FCancellation=0
-
) a
-
order by a.Fdate
-
insert into #icmo
-
(FTranType,FOrgInterID,FOrgEntryID,FInterID,FEntryid,Ftype,FBillNo,FItemid,Fdate,Fqty,FDoQty,FStatus,FOrderInterid,FOrderType)
-
select * from (
-
select
-
t1.FClassTypeID as FTranType,
-
t1.FInterID as FOrgInterID,
-
t2.FEntryID as FOrgEntryID,
-
t1.Finterid,
-
t2.FEntryID,
-
1067 as Ftype,
-
t1.FBillNO,
-
t2.FItemID,
-
t2.FPayShipDate as Fdate,
-
t2.FQty,
-
FStockQty as FDoQty,
-
t1.FStatus,
-
0 as FOrderInterid,
-
0 as FOrderType
-
from ICSubContract t1
-
inner join ICSubContractEntry t2 on t1.Finterid=t2.Finterid
-
inner join t_icitem t3 on t2.Fitemid=t3.fitemid
-
where FMrpClosed=0 and Fstatus=0 and FCancellation=0
-
) a
-
order by a.Fdate
-
--计划状态下任务单
-
insert into #ICMO
-
(FTranType,FOrgInterID,FOrgEntryID,Finterid,FEntryid,Ftype,FBillNo,FItemid,Fdate,Fqty,FDoQty,FStatus,FOrderInterid,FOrderType)
-
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.FQty,
-
t2.FCommitQty,
-
0,
-
t2.FSourceInterid,
-
t2.FSourceTrantype
-
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 t1.FMrpclosed=0 and t2.FMrpclosed=0 and t1.FBizType=12511
-
order by t2.FFetchTime
-
--展开BOM表
-
insert into #icbom
-
(FItemid)
-
select
-
distinct Fitemid from #icmo where FStatus=0
-
declare @FLevel int
-
set @FLevel=0
-
insert into #icbomEntry
-
(
-
FParentID,FItemID,FLevel,FQty
-
)
-
select
-
u1.FID,t2.FItemID,@FLevel,(t2.FQty/t1.FQty)*(1+t2.FScrap/100) from #icbom u1
-
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)
-
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
-
--生产需发料明细
-
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,t2.FqtyMust-t2.FStockQty+t2.FDiscardQty 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
-
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
-
group by u1.id,u1.FDate,t2.FItemID
-
) a
-
order by a.id
-
--产品预测单
-
insert into #icmo
-
(Finterid,FEntryid,Ftype,FBillno,Fitemid,Fdate,Fqty,Fdoqty,FStatus)
-
select
-
Finterid,0,Ftrantype,fbillno,0,Fdate,0,0,0
-
from pporder
-
where isnull(FHeadSelfY0123,0)=40077 and Finterid in
-
(
-
select Finterid from pporderentry where FOrderClosed=0
-
)
-
insert into #icmolist
-
(FIcmoInterid,FDate,FItemid,FUnitQty,FQty,FDistributeQty)
-
select
-
u1.id,t1.FNeedDate,t1.Fitemid,1,t1.Fqty,t1.Fqty
-
from #icmo u1
-
inner join pporderentry t1 on u1.Finterid=t1.Finterid and u1.FType=87
-
where t1.FOrderClosed=0
-
--替代清单调整
-
--处理任务和委外替代清单
-
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=1
-
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 ICSubsItemBillEntry t4 on t4.fid=t2.FID and t4.FGroupID=1
-
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=1
-
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=1
-
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 ICSubsItemBillEntry t4 on t4.fid=t2.FID and t4.FGroupID=1
-
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=1
-
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=1
-
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 ICSubsItemBillEntry t4 on t4.fid=t2.FID and t4.FGroupID=1
-
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=1
-
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=1
-
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 ICSubsItemBillEntry t4 on t4.fid=t2.FID and t4.FGroupID=1
-
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=1
-
where t4.FSubsQty>=t1.FOrgQty and t2.FStatus=1 and t1.FFromType=1
-
--导入资源
-
--导入库存
-
insert into
-
#Source
-
(FSourceType,Fdate,Fitemid,Fqty,FDistributedQty)
-
select
-
1,
-
'1988-08-08',
-
t1.FItemID,
-
sum(t1.Fqty) as Fqty,
-
sum(t1.Fqty)
-
from ICInventory t1
-
inner join t_Stock t3 on t1.FStockID=t3.FItemID
-
where t3.FMRPAvail=1
-
group by t1.FItemID
-
insert into
-
#Source
-
(FSourceType,Fstatus,FItemid,Fdate,Fqty,FDistributedQty,FSourceBillNo,FSourceEntryid)
-
select
-
4,
-
t2.Fstatus,
-
t1.FItemID,
-
t1.Fdate,
-
t1.fqty-t1.fstockqty,
-
t1.fqty-t1.fstockqty,
-
t2.FbillNO,
-
t1.Fentryid
-
from poorder t2
-
inner join poorderentry t1 on t1.finterid=t2.finterid
-
inner join t_icitem t3 on t1.Fitemid=t3.Fitemid
-
where t2.FCancellation=0 and t1.FMrpClosed=0 and t1.Fqty-t1.FStockQty<>0
-
order by t1.FDate
-
insert into
-
#Source
-
(FSourceType,FItemid,Fdate,Fqty,FDistributedQty,FSourceBillNo,FSourceEntryid)
-
select
-
6,
-
t2.FItemID,
-
t2.FFetchTime,
-
t2.FQty-t2.FCommitQty,
-
t2.FQty-t2.FCommitQty,
-
t1.Fbillno,
-
t2.Fentryid
-
from porequest t1
-
inner join porequestEntry t2 on t1.Finterid=t2.Finterid
-
where FCancellation=0 and t2.FMrpclosed=0 and t1.FBizType=12510
-
insert into
-
#Source
-
(FSourceType,FStatus,FItemid,Fdate,Fqty,FDistributedQty,FSourceBillNo)
-
select
-
5,
-
t1.FStatus,
-
t1.FItemID,
-
t1.FPlanFinishDate,
-
fqty-FstockQty,
-
fqty-FstockQty,
-
t1.Fbillno
-
from icmo t1
-
where t1.FClosed=0 and FMrpClosed=0 and t1.FCancellation=0 and t1.Fstatus in (1,2,5)
-
order by t1.Fstatus,t1.FPlanFinishDate
-
insert into
-
#Source
-
(FSourceType,FStatus,FItemid,Fdate,Fqty,FDistributedQty,FSourceBillNo)
-
select
-
5,
-
t1.FStatus,
-
t1.FItemID,
-
t1.FPlanFinishDate,
-
fqty-FstockQty,
-
fqty-FstockQty,
-
t1.Fbillno
-
from icmo t1
-
where t1.FClosed=0 and FMrpClosed=0 and t1.FCancellation=0 and t1.Fstatus=0
-
order by t1.Fstatus,t1.FPlanFinishDate
-
--委外订单
-
insert into
-
#Source
-
(FSourceType,FItemid,Fdate,Fqty,FDistributedQty,FSourceBillNo)
-
select
-
7,
-
t2.FItemID,
-
t2.FFetchDate,
-
t2.FQty-FStockQty,
-
t2.FQty-FStockQty,
-
t1.FBillNO
-
from ICSubContract t1
-
inner join ICSubContractEntry t2 on t1.Finterid=t2.Finterid
-
where FClosed=0 and FMrpClosed=0 and FCancellation=0
-
--委外申请
-
insert into
-
#Source
-
(FSourceType,FItemid,Fdate,Fqty,FDistributedQty,FSourceBillNo)
-
select
-
8,
-
t2.FItemID,
-
t2.FFetchTime,
-
t2.FQty-t2.FCommitQty,
-
t2.FQty-t2.FCommitQty,
-
t1.FBillNo
-
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 t1.FMrpclosed=0 and t2.FMrpclosed=0 and t1.FBizType=12511
-
--分配资源
-
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.FMyStuats=1
-
from #icmo u1
-
inner join
-
(
-
select FIcmoInterid,max(Fdistributeqty) as 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
-
)
-
)
-
)
-
--跟踪表
-
select
-
t1.Findex,
-
isnull(t8.fbillno,'')+isnull(t9.fbillno,'') as 订单编号,
-
case u1.Ftype when 1067 then '委外订单' when 1054 then '生产任务单' when 70 then '委外申请' when 500 then '计划订单' when 87 then '预测单' else '' end as 单据类型,
-
u1.Fbillno as 单据编号,
-
t7_1.FName as 生产车间,
-
t501.Fname as 产品PMC负责人,
-
t1.Fdate as 计划开工时间,
-
isnull(t6.F_122,0) as 提前期偏置,
-
year(dbo.FN_getPreWorkDay(t1.Fdate,abs(isnull(t6.F_122,0)))) as FYear,
-
month(dbo.FN_getPreWorkDay(t1.Fdate,abs(isnull(t6.F_122,0)))) as FMonth,
-
t7.FPlanFinishdate as 预计完工日期,
-
t5.Fnumber as 产品代码,
-
t5.Fname as 产品名称,
-
t5.Fmodel as 产品规格型号,
-
u1.Fqty as 生产数量,
-
u1.Fdoqty as 已生产数量,
-
case u1.Fstatus when 0 then '计划' when 1 then '下达' when 2 then '下达' when 3 then '结案' when 5 then '确认' else '' end as 状态,
-
case when u1.FMyStuats=1 then '齐料' else '' end 齐料状态,
-
t6.Fnumber as 子项物料代码,
-
t6.Fname as 子项物料名称,
-
t6.Fmodel as 子项规格型号,
-
case t6.FerpClsID when 1 then '外购' when 2 then '自制' when 3 then '委外加工' else '' end as 物料类型,
-
t1.Funitqty as 单位用量,
-
t1.Fqty as 需发料数量,
-
t1.FUnDistributeQty 欠料数,
-
t1.Fdistributeqty as 未分配数量,
-
a.Fqty as 未分配总数,
-
t11.FQty as 在检数量,
-
case t4.FSourceType when 1 then '' when 2 then '在检' when 3 then '电芯搁置' when 4 then '采购订单' when 5 then '生产任务单' when 6 then '采购申请' when 7 then '委外订单' when 8 then '委外申请' else '' end as 源单据类型,
-
t4.Fsourcebillno as 单据编码,
-
t4.FSourceEntryid as 行号,
-
a1.Fdate as 下单日期,
-
b2.FBillNO as 申购单号,
-
b2.Fdate as 申购日期,
-
b1.FFEtchTime as PR到料日期 ,
-
t4.Fdate as 交货日期,
-
a2.FRKReplayDate 交期回复,
-
a2.FRKReplayNote 交期备注,
-
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 '' end as 源单状态,
-
t4.Fqty as 可分配数量,
-
t4.Fdistributedqty as 剩余数量,
-
t3.Fqty as 分配数量,
-
a3.Fname as 供应商,
-
--a2.FPrice as 单价,
-
a2.FNote as 备注信息,
-
t4.FSourceType
-
into #report
-
from #icmo u1
-
inner join #icmolist t1 on u1.id=t1.FIcmoInterid
-
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_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=1054--增加1055返工产品
-
left join t_department t7_1 on t7_1.Fitemid=t7.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 #result t3 on t3.FicmolistID=t1.id
-
left join #Source t4 on t4.id=t3.FSourceID
-
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
-
(
-
select Fitemid,sum(FQty) as FQty from poinventory where FQty>0 group by Fitemid
-
) t11 on t11.Fitemid=t1.FItemID
-
where t1.Fqty<>0 and not ((isnull(t4.FSourceType,0)=1 and isnull(t1.Fdistributeqty,0)=0) or (isnull(t4.FSourceType,0)=1 and t1.Fqty-isnull(t1.Fdistributeqty,0)<>t3.Fqty))
-
order by u1.id,t4.FSourceType
-
alter table #report
-
add 欠料总计 nvarchar(100)
-
insert into #report
-
(Findex,订单编号,单据类型,子项物料代码,子项物料名称,子项规格型号,欠料数,状态,齐料状态,物料类型,源单据类型,源单状态,在检数量,欠料总计,FYear,FMonth,提前期偏置)
-
select
-
1,'',
-
case t2.Ftype when 1067 then '委外订单' when 1054 then '生产任务单' when 70 then '委外申请' when 500 then '计划订单' when 87 then '预测单' else '' end as 单据类型,
-
t1.FNumber,t1.FName,t1.FModel,sum(FUnDistributeQty),'','','','','',isnull(t11.Fqty,0),
-
convert(nvarchar(50),year(dbo.FN_getPreWorkDay(u1.Fdate,abs(isnull(t1.F_122,0)))))+right('00'+convert(nvarchar(50),month(dbo.FN_getPreWorkDay(u1.Fdate,abs(isnull(t1.F_122,0))))),2)+'小计:',
-
year(dbo.FN_getPreWorkDay(u1.Fdate,abs(isnull(t1.F_122,0)))),month(dbo.FN_getPreWorkDay(u1.Fdate,abs(isnull(t1.F_122,0)))),0
-
from #icmolist u1
-
inner join t_ICItem t1 on u1.FItemid=t1.FItemID
-
inner join #icmo t2 on t2.id=u1.FIcmoInterid
-
left join
-
(
-
select Fitemid,sum(FQty) as FQty from poinventory where FQty>0 group by Fitemid
-
) t11 on t11.Fitemid=t1.FItemID
-
where u1.FQty<>0 and FUnDistributeQty>0
-
group by case t2.Ftype when 1067 then '委外订单' when 1054 then '生产任务单' when 70 then '委外申请' when 500 then '计划订单' when 87 then '预测单' else '' end,year(dbo.FN_getPreWorkDay(u1.Fdate,abs(isnull(t1.F_122,0)))),month(dbo.FN_getPreWorkDay(u1.Fdate,abs(isnull(t1.F_122,0)))),t1.FNumber,t1.FName,t1.FModel,isnull(t11.Fqty,0)
-
insert into #report
-
(Findex,订单编号,单据类型,子项物料代码,子项物料名称,子项规格型号,欠料数,状态,齐料状态,物料类型,源单据类型,源单状态,在检数量,欠料总计,提前期偏置)
-
select
-
2,'',
-
'',t1.FNumber,t1.FName,t1.FModel,sum(FUnDistributeQty),'','','','','',isnull(t11.Fqty,0),'总计:',0
-
from #icmolist u1
-
inner join t_ICItem t1 on u1.FItemid=t1.FItemID
-
inner join #icmo t2 on t2.id=u1.FIcmoInterid
-
left join
-
(
-
select Fitemid,sum(FQty) as FQty from poinventory where FQty>0 group by Fitemid
-
) t11 on t11.Fitemid=t1.FItemID
-
where u1.FQty<>0 and FUnDistributeQty>0
-
group by t1.FNumber,t1.FName,t1.FModel,isnull(t11.Fqty,0)
-
select
-
单据类型,
-
单据编号,
-
计划开工时间,
-
提前期偏置,
-
dbo.FN_getPreWorkDay(计划开工时间,abs(提前期偏置)) as 要求到料日期,
-
预计完工日期,
-
产品代码,
-
产品名称,
-
生产数量,
-
已生产数量,
-
状态,
-
欠料总计,
-
子项物料代码,
-
子项物料名称,
-
物料类型,
-
单位用量,
-
需发料数量,
-
欠料数,
-
在检数量,
-
未分配数量,
-
未分配总数,
-
源单据类型,
-
单据编码,
-
行号,
-
下单日期,
-
申购单号,
-
申购日期,
-
PR到料日期,
-
交货日期,
-
交期回复,
-
交期备注,
-
源单状态,
-
可分配数量,
-
剩余数量,
-
分配数量,
-
供应商,
-
备注信息
-
from #report order by 子项物料代码,isnull(Fyear,2050),isnull(Fmonth,1),case when Findex=2 then 1 else 0 end asc,单据类型,Findex asc,dbo.FN_getPreWorkDay(计划开工时间,abs(提前期偏置)),FSourceType
-
drop table #ICMO
-
drop table #ICMOList
-
drop table #Source
-
drop table #sn
-
drop table #result
-
drop table #icbom
-
drop table #icbomEntry
-
drop table #report
2、新建一个Excel,点击“数据”——“自其他来源”——“来自sql server”;
3、输入K3数据库IP和连接账号,密码,下一步;
4、连接成功后,随便选一个表,点击下一步;
5、点击完成;
6、现有工作表位置选择整个sheet,确定;
7、重新点击连接图标,更改sql连接字符串;
8、点击属性;
9、将命令类型更改为SQL,,将原来选择的表更改为存储视图sp_mymrp_must_sum;
10、弹出警告,选择是;
11、输入sql密码,点击确定;
12、数据更新完毕,欠料报表存储过程数据已经从sql刷新到Excel,可以对数据做后期的加工运算等操作。