金蝶K3 SQL报表系列-BOM成本汇总表

1、创建供应商维护价格视图z_view_SupplyRatePrice，代码如下：

create view [dbo].[z_view_SupplyRatePrice]
as
select 
u1.*,
t1.FValueAddRate,
t2.FCoefficient,
t3.FExchangeRate
from t_supplyentry u1
inner join t_supplier t1 on u1.fsupid=t1.fitemid
inner join t_MeasureUnit t2 on t2.FItemID=u1.Funitid
inner join t_currency t3 on t3.Fcurrencyid=u1.FCyID

2、创建实际入库价格视图z_view_OrderPrice，代码如下：

create  view
[dbo].[z_view_OrderPrice]
as
select 
t1.finterid,
t1.fdate,
case when Forderinterid>0 then 1 else 0 end as Forderinterid,
t2.Fitemid,
case t1.ftrantype when 1 then t2.Fprice when 5 then FProcessprice end as Fprice
from
ICStockBill t1
inner join ICStockBillEntry t2 on t1.finterid=t2.finterid
inner join t_MeasureUnit t3 on t3.Fitemid=t2.Funitid
where t1.ftrantype in (1,5) and t1.FROB=1

3、创建存储过程：zp_z_ProduceCostNow_sum，代码如下：

create   procedure [dbo].[zp_z_ProduceCostNow_sum]
@starFnumber nvarchar(50),
@endFNumber nvarchar(50)
as
set nocount on
/*
declare @Fitemid int
set @Fitemid=(select top 1 Fitemid from t_icitem where Fnumber=@Fnumber)
*/


create table #z_ProductCost
(
Finterid int not null IDENTITY (1, 1) primary key,
FItemid int,
FSupplyAmount decimal(18,6) default 0,
FStockAmount decimal(18,6) default 0,
FError int default 0
)

create table #z_ProductCostEntry
(
id int not null identity(1,1) primary key,
Finterid int not null,
FEntry nvarchar(50) not null,
Fitemid int,
Fqty decimal(18,6),
Flevel int,
FerpClsID int,
FMaterType int,
FError  int default 0,
FSupplyPrice decimal(18,6) default 0,
FSupplyAmount decimal(18,6) default 0,
FSupplyPriceType int,
FStockPrice decimal(18,6) default 0,
FStockAmount decimal(18,6) default 0,
FMinStockPrice decimal(18,6) default 0,
FStockPriceType int
)



insert into #z_ProductCost
(FItemid)
select fitemid
from t_icitem where fnumber>=@starFnumber and fnumber<=@endFNumber and FerpClsid in (2,3)



create table #Product(
FParentID int,
sn nvarchar(50),
Fitemid int,
FQty decimal(18,6),
FErpClsID int 
)

create table #ProductEntry(
FParentID int,
sn nvarchar(50),
fitemid int,
fqty decimal(18,6),
FMaterType int,
FErpClsID int,
FError int default 0
)


update #z_ProductCost
set FError=1
where not exists (select finterid from icbom where #z_ProductCost.Fitemid=icbom.fitemid and icbom.FUsestatus=1072)


insert into #Product
(FParentID,sn,Fitemid,Fqty,FerpClsID)
select
FInterid,'001',Fitemid,1,2
from #z_ProductCost

declare @Level int
set @Level=1

while @level<20 and exists(select * from #Product)

begin

insert into #ProductEntry
(FParentID,
sn,
Fitemid,
Fqty,
FMaterType,
FerpClsID)
select 
u1.FParentID,
u1.sn+'.'+right('000'+cast(t2.fentryid as nvarchar),3),
t2.fitemid,
u1.fqty*(t2.fqty/t1.fqty)*(1+FScrap/100),
t2.FMaterielType,
t3.FerpClsID
 from #Product u1
inner join icbom t1 on t1.fitemid=u1.fitemid and t1.FUsestatus=1072
inner join icbomchild t2 on t1.finterid=t2.finterid
inner join t_icitem t3 on t2.fitemid=t3.fitemid


update
#ProductEntry
set FError=1
where not exists(select * from icbom u1 where u1.fitemid=#ProductEntry.fitemid and u1.FUsestatus=1072)
 and FerpClsid in (2,3) and FMaterType=371


insert into #z_ProductCostEntry
(Finterid,
FEntry,
Fitemid,
Fqty,
FMaterType,
Flevel,
FerpClsID,
FError)
select
FParentID,sn,fitemid,fqty,FMaterType,@level,ferpclsid,Ferror
from #ProductEntry


delete #Product

insert into #Product
(FParentID,sn,Fitemid,Fqty,FerpClsID)
select 
FParentID,sn,Fitemid,Fqty,FerpClsID
from #ProductEntry where FerpClsid in (2,3,5) and Ferror=0 and FMaterType=371

delete #ProductEntry

set @level=@level+1
end

drop table #Product
drop table #ProductEntry

update 
#z_ProductCostEntry
set FError=0
from #z_ProductCostEntry,#z_ProductCostEntry t2 ,#z_ProductCost t3
where 
 #z_ProductCostEntry.fmatertype=371 and t2.fmatertype=372 
and #z_ProductCostEntry.finterid=t2.finterid
 and #z_ProductCostEntry.fitemid=t2.fitemid and #z_ProductCostEntry.fqty=t2.fqty


update
#z_ProductCost
set FError=1
where exists(select id from #z_ProductCostEntry t1 where #z_ProductCost.finterid=t1.finterid and t1.Ferror=1 and FmaterType=371)



update #z_ProductCostEntry
set FSupplyPrice=
(select top 1  fprice*FExchangeRate/(1+FValueAddRate/100)/FCoefficient from z_view_SupplyRatePrice t9 where t9.fitemid=t2.fitemid and fdisabledate>getdate() and fquotetime<getdate() order by Fquotetime desc),
FSupplyPriceType=1
from #z_ProductCost t1,#z_ProductCostEntry t2
where t1.finterid=t2.finterid  and t2.FSupplyPrice=0  and FMaterType=371
and exists(select top 1  fprice from z_view_SupplyRatePrice t10 where t10.fitemid=t2.fitemid and fdisabledate>getdate() and fquotetime<getdate())

update #z_ProductCostEntry
set FSupplyPrice=
(select top 1 Fprice from z_view_OrderPrice t9 where t9.fitemid=t2.fitemid order by Fdate desc),
FSupplyPriceType=2
from #z_ProductCost t1,#z_ProductCostEntry t2
where t1.finterid=t2.finterid  and t2.FSupplyprice=0  and FMaterType=371
and exists(select top 1 Fprice from z_view_OrderPrice t10 where t10.fitemid=t2.fitemid)


update #z_ProductCostEntry
set FStockPrice=
(select top 1 Fprice from z_view_OrderPrice t9 where t9.fitemid=t2.fitemid order by Forderinterid desc ,Fdate desc),
FStockPriceType=2
from #z_ProductCost t1,#z_ProductCostEntry t2
where t1.finterid=t2.finterid   and t2.FStockPrice=0  and FMaterType=371
and exists(select top 1 Fprice from z_view_OrderPrice t10 where t10.fitemid=t2.fitemid)


update #z_ProductCostEntry
set FStockPrice=
(select top 1  fprice*FExchangeRate/(1+FValueAddRate/100)/FCoefficient from z_view_SupplyRatePrice t9 where t9.fitemid=t2.fitemid and fdisabledate>getdate() and fquotetime<getdate() order by Fquotetime desc),
FStockPriceType=1
from #z_ProductCost t1,#z_ProductCostEntry t2
where t1.finterid=t2.finterid  and t2.FStockPrice=0  and FMaterType=371
and exists(select top 1  fprice from z_view_SupplyRatePrice t10 where t10.fitemid=t2.fitemid and fdisabledate>getdate() and fquotetime<getdate())




update #z_ProductCostEntry
set FSupplyAmount=fqty*FSupplyPrice,
FStockAmount=Fqty*FStockPrice
from #z_ProductCost t1,#z_ProductCostEntry t2
where t1.finterid=t2.finterid  


update #z_ProductCost
set FSupplyAmount=(select top 1 FSupplyAmount from (
select 
finterid,
sum(FSupplyAmount) as FSupplyAmount,
sum(FStockAmount) as FStockAmount,
min(FSupplyPrice) as FMinSupplyPrice,
min (FStockPrice) as FminStockPrice
from #z_productCostEntry
where FMaterType=371 --and FerpClsid in (1,3)
group by finterid

) t1 where t1.Finterid=#z_ProductCost.Finterid),
FStockAmount=(select top 1 FStockAmount from (

select 
finterid,
sum(FSupplyAmount) as FSupplyAmount,
sum(FStockAmount) as FStockAmount,
min(FSupplyPrice) as FMinSupplyPrice,
min (FStockPrice) as FminStockPrice
from #z_productCostEntry
where FMaterType=371 --and FerpClsid in (1,3)
group by finterid

) t1 where t1.Finterid=#z_ProductCost.Finterid)



update t1
set t1.FError=t1.FError+2
from #z_ProductCost t1 ,#z_productCostEntry t2
where t1.finterid=t2.finterid and t2.FMinStockPrice=0



select
t1.Fnumber as 物料代码,
t1.Fname as 物料名称,
t1.FModel as 规格型号,
u1.fsupplyamount as 供应商维护价格合计,
u1.fstockamount as 最新入库价合计
into #result
from #z_ProductCost u1
inner join t_icitem t1 on u1.fitemid=t1.fitemid

select * from #result

drop table #z_productCost
drop table #z_productCostEntry

2、K3查询分析工具，调用存储过程；

exec zp_z_ProduceCostNow_sum '*ItemNo*','#ItemNo#'

开发完毕。