金蝶K3 SQL报表系列-BOM成本明细表
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了金蝶K3 SQL报表系列-BOM成本明细表，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含8658字，纯文字阅读大概需要13分钟。
内容图文
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#'内容总结
以上是互联网集市为您收集整理的金蝶K3 SQL报表系列-BOM成本明细表全部内容，希望文章能够帮你解决金蝶K3 SQL报表系列-BOM成本明细表所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。