Excel+MSSQL开发金蝶K3欠料报表

最新推荐文章于 2020-12-27 14:24:04 发布

宝贝啊！

最新推荐文章于 2020-12-27 14:24:04 发布

阅读量974

点赞数

原文链接：https://blog.csdn.net/hzfw2008/article/details/75369547

版权

将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，可以对数据做后期的加工运算等操作。

宝贝啊！

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
Excel+MSSQL开发金蝶K3欠料报表

将K3所有订单需求、MRP运算、库存及在途数据等数据整体展示，适合计划部门根据报表数据统筹考量物料供给情况，特制作此报表及Excel集成工具，以减少手工汇总统计的工作量。本代码适用于金蝶K3 WISE 12.2版本1、K3后台mssql数据库创建存储过程： create procedure [dbo].[sp_mymrp_must_sum] as set nocount on create table #icmo ( FT...
复制链接

扫一扫