金蝶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，代码如下：

create procedure [dbo].[zp_z_ProduceCostNow]
@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
t3.fnumber as 产品代码,
t3.fname as 产品名称,
t3.fmodel as 产品规格,
u1.fentry 行号,
t1.fnumber 材料代码,
t1.Fname 材料名称,
t1.Fmodel 材料规格,
u1.fqty 用量,
t4.Fname as 材料类型,
case u1.fmatertype when 371 then '正常' when 372 then '联产品' when 376 then '返回件' end as 领料类型,
case u1.fError when 0 then '正常' when 1  then'没有BOM' end as 错误状态,
u1.FSupplyPrice 供应商维护单价,
u1.FSupplyAmount 供应商维护金额,
case u1.FSupplyPriceType when 1 then '供应商维护单价' when 2 then '最新入库价' when 3 then '计划单价' when 4 then '其它' else '无单价' end as 供应商维护价格来源,
u1.FStockPrice 最新入库单价,
u1.FStockAmount 最新入库价金额,
case u1.FstockPriceType when 1 then '供应商维护单价' when 2 then '最新入库价' when 3 then '计划单价' when 4 then '其它' else '无单价' end as 入库价价格来源
into #result
from #z_ProductCostEntry u1
inner join t_icitem t1 on t1.fitemid=u1.fitemid
inner join #z_ProductCost t2 on t2.finterid=u1.finterid
inner join t_icitem t3 on t2.fitemid=t3.fitemid
inner join t_submessage t4 on t4.Finterid=u1.Ferpclsid

insert into #result
select
t1.Fnumber,
t1.Fname,
t1.FModel,
'合计:',
t1.Fnumber,
'',
'',
0,
'',
'',
'',
0,
u1.fsupplyamount,
'',
0,
u1.fstockamount,
''
from #z_ProductCost u1
inner join t_icitem t1 on u1.fitemid=t1.fitemid

select * from #result order by 产品代码,行号

drop table #z_productCost
drop table #z_productCostEntry

4、K3查询分析工具调用存储过程，代码如下：

exec zp_z_ProduceCostNow '*ItemNo*','#ItemNo#'

开发完毕。