金蝶K3 SQL报表系列-BOM成本明细表

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

 

 
  1. create view [dbo].[z_view_SupplyRatePrice]

  2. as

  3. select

  4. u1.*,

  5. t1.FValueAddRate,

  6. t2.FCoefficient,

  7. t3.FExchangeRate

  8. from t_supplyentry u1

  9. inner join t_supplier t1 on u1.fsupid=t1.fitemid

  10. inner join t_MeasureUnit t2 on t2.FItemID=u1.Funitid

  11. inner join t_currency t3 on t3.Fcurrencyid=u1.FCyID

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

 

 

 
  1. create view

  2. [dbo].[z_view_OrderPrice]

  3. as

  4. select

  5. t1.finterid,

  6. t1.fdate,

  7. case when Forderinterid>0 then 1 else 0 end as Forderinterid,

  8. t2.Fitemid,

  9. case t1.ftrantype when 1 then t2.Fprice when 5 then FProcessprice end as Fprice

  10. from

  11. ICStockBill t1

  12. inner join ICStockBillEntry t2 on t1.finterid=t2.finterid

  13. inner join t_MeasureUnit t3 on t3.Fitemid=t2.Funitid

  14. where t1.ftrantype in (1,5) and t1.FROB=1

3、创建存储过程zp_z_ProduceCostNow,代码如下:

 

 
  1. create procedure [dbo].[zp_z_ProduceCostNow]

  2. @starFnumber nvarchar(50),

  3. @endFNumber nvarchar(50)

  4. as

  5. set nocount on

  6. /*

  7. declare @Fitemid int

  8. set @Fitemid=(select top 1 Fitemid from t_icitem where Fnumber=@Fnumber)

  9. */

  10.  
  11.  
  12. create table #z_ProductCost

  13. (

  14. Finterid int not null IDENTITY (1, 1) primary key,

  15. FItemid int,

  16. FSupplyAmount decimal(18,6) default 0,

  17. FStockAmount decimal(18,6) default 0,

  18. FError int default 0

  19. )

  20.  
  21. create table #z_ProductCostEntry

  22. (

  23. id int not null identity(1,1) primary key,

  24. Finterid int not null,

  25. FEntry nvarchar(50) not null,

  26. Fitemid int,

  27. Fqty decimal(18,6),

  28. Flevel int,

  29. FerpClsID int,

  30. FMaterType int,

  31. FError int default 0,

  32. FSupplyPrice decimal(18,6) default 0,

  33. FSupplyAmount decimal(18,6) default 0,

  34. FSupplyPriceType int,

  35. FStockPrice decimal(18,6) default 0,

  36. FStockAmount decimal(18,6) default 0,

  37. FMinStockPrice decimal(18,6) default 0,

  38. FStockPriceType int

  39. )

  40.  
  41.  
  42.  
  43. insert into #z_ProductCost

  44. (FItemid)

  45. select fitemid

  46. from t_icitem where fnumber>=@starFnumber and fnumber<=@endFNumber and FerpClsid in (2,3)

  47.  
  48.  
  49.  
  50. create table #Product(

  51. FParentID int,

  52. sn nvarchar(50),

  53. Fitemid int,

  54. FQty decimal(18,6),

  55. FErpClsID int

  56. )

  57.  
  58. create table #ProductEntry(

  59. FParentID int,

  60. sn nvarchar(50),

  61. fitemid int,

  62. fqty decimal(18,6),

  63. FMaterType int,

  64. FErpClsID int,

  65. FError int default 0

  66. )

  67.  
  68.  
  69. update #z_ProductCost

  70. set FError=1

  71. where not exists (select finterid from icbom where #z_ProductCost.Fitemid=icbom.fitemid and icbom.FUsestatus=1072)

  72.  
  73.  
  74. insert into #Product

  75. (FParentID,sn,Fitemid,Fqty,FerpClsID)

  76. select

  77. FInterid,'001',Fitemid,1,2

  78. from #z_ProductCost

  79.  
  80. declare @Level int

  81. set @Level=1

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

  84.  
  85. begin

  86.  
  87. insert into #ProductEntry

  88. (FParentID,

  89. sn,

  90. Fitemid,

  91. Fqty,

  92. FMaterType,

  93. FerpClsID)

  94. select

  95. u1.FParentID,

  96. u1.sn+'.'+right('000'+cast(t2.fentryid as nvarchar),3),

  97. t2.fitemid,

  98. u1.fqty*(t2.fqty/t1.fqty)*(1+FScrap/100),

  99. t2.FMaterielType,

  100. t3.FerpClsID

  101. from #Product u1

  102. inner join icbom t1 on t1.fitemid=u1.fitemid and t1.FUsestatus=1072

  103. inner join icbomchild t2 on t1.finterid=t2.finterid

  104. inner join t_icitem t3 on t2.fitemid=t3.fitemid

  105.  
  106.  
  107. update

  108. #ProductEntry

  109. set FError=1

  110. where not exists(select * from icbom u1 where u1.fitemid=#ProductEntry.fitemid and u1.FUsestatus=1072)

  111. and FerpClsid in (2,3) and FMaterType=371

  112.  
  113.  
  114. insert into #z_ProductCostEntry

  115. (Finterid,

  116. FEntry,

  117. Fitemid,

  118. Fqty,

  119. FMaterType,

  120. Flevel,

  121. FerpClsID,

  122. FError)

  123. select

  124. FParentID,sn,fitemid,fqty,FMaterType,@level,ferpclsid,Ferror

  125. from #ProductEntry

  126.  
  127.  
  128. delete #Product

  129.  
  130. insert into #Product

  131. (FParentID,sn,Fitemid,Fqty,FerpClsID)

  132. select

  133. FParentID,sn,Fitemid,Fqty,FerpClsID

  134. from #ProductEntry where FerpClsid in (2,3,5) and Ferror=0 and FMaterType=371

  135.  
  136. delete #ProductEntry

  137.  
  138. set @level=@level+1

  139. end

  140.  
  141. drop table #Product

  142. drop table #ProductEntry

  143.  
  144. update

  145. #z_ProductCostEntry

  146. set FError=0

  147. from #z_ProductCostEntry,#z_ProductCostEntry t2 ,#z_ProductCost t3

  148. where

  149. #z_ProductCostEntry.fmatertype=371 and t2.fmatertype=372

  150. and #z_ProductCostEntry.finterid=t2.finterid

  151. and #z_ProductCostEntry.fitemid=t2.fitemid and #z_ProductCostEntry.fqty=t2.fqty

  152.  
  153.  
  154. update

  155. #z_ProductCost

  156. set FError=1

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

  158.  
  159.  
  160.  
  161. update #z_ProductCostEntry

  162. set FSupplyPrice=

  163. (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),

  164. FSupplyPriceType=1

  165. from #z_ProductCost t1,#z_ProductCostEntry t2

  166. where t1.finterid=t2.finterid and t2.FSupplyPrice=0 and FMaterType=371

  167. and exists(select top 1 fprice from z_view_SupplyRatePrice t10 where t10.fitemid=t2.fitemid and fdisabledate>getdate() and fquotetime<getdate())

  168.  
  169. update #z_ProductCostEntry

  170. set FSupplyPrice=

  171. (select top 1 Fprice from z_view_OrderPrice t9 where t9.fitemid=t2.fitemid order by Fdate desc),

  172. FSupplyPriceType=2

  173. from #z_ProductCost t1,#z_ProductCostEntry t2

  174. where t1.finterid=t2.finterid and t2.FSupplyprice=0 and FMaterType=371

  175. and exists(select top 1 Fprice from z_view_OrderPrice t10 where t10.fitemid=t2.fitemid)

  176.  
  177.  
  178. update #z_ProductCostEntry

  179. set FStockPrice=

  180. (select top 1 Fprice from z_view_OrderPrice t9 where t9.fitemid=t2.fitemid order by Forderinterid desc ,Fdate desc),

  181. FStockPriceType=2

  182. from #z_ProductCost t1,#z_ProductCostEntry t2

  183. where t1.finterid=t2.finterid and t2.FStockPrice=0 and FMaterType=371

  184. and exists(select top 1 Fprice from z_view_OrderPrice t10 where t10.fitemid=t2.fitemid)

  185.  
  186.  
  187. update #z_ProductCostEntry

  188. set FStockPrice=

  189. (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),

  190. FStockPriceType=1

  191. from #z_ProductCost t1,#z_ProductCostEntry t2

  192. where t1.finterid=t2.finterid and t2.FStockPrice=0 and FMaterType=371

  193. and exists(select top 1 fprice from z_view_SupplyRatePrice t10 where t10.fitemid=t2.fitemid and fdisabledate>getdate() and fquotetime<getdate())

  194.  
  195.  
  196.  
  197.  
  198. update #z_ProductCostEntry

  199. set FSupplyAmount=fqty*FSupplyPrice,

  200. FStockAmount=Fqty*FStockPrice

  201. from #z_ProductCost t1,#z_ProductCostEntry t2

  202. where t1.finterid=t2.finterid

  203.  
  204.  
  205. update #z_ProductCost

  206. set FSupplyAmount=(select top 1 FSupplyAmount from (

  207. select

  208. finterid,

  209. sum(FSupplyAmount) as FSupplyAmount,

  210. sum(FStockAmount) as FStockAmount,

  211. min(FSupplyPrice) as FMinSupplyPrice,

  212. min (FStockPrice) as FminStockPrice

  213. from #z_productCostEntry

  214. where FMaterType=371 --and FerpClsid in (1,3)

  215. group by finterid

  216.  
  217. ) t1 where t1.Finterid=#z_ProductCost.Finterid),

  218. FStockAmount=(select top 1 FStockAmount from (

  219.  
  220. select

  221. finterid,

  222. sum(FSupplyAmount) as FSupplyAmount,

  223. sum(FStockAmount) as FStockAmount,

  224. min(FSupplyPrice) as FMinSupplyPrice,

  225. min (FStockPrice) as FminStockPrice

  226. from #z_productCostEntry

  227. where FMaterType=371 --and FerpClsid in (1,3)

  228. group by finterid

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

  231.  
  232.  
  233.  
  234. update t1

  235. set t1.FError=t1.FError+2

  236. from #z_ProductCost t1 ,#z_productCostEntry t2

  237. where t1.finterid=t2.finterid and t2.FMinStockPrice=0

  238.  
  239.  
  240.  
  241.  
  242. select

  243. t3.fnumber as 产品代码,

  244. t3.fname as 产品名称,

  245. t3.fmodel as 产品规格,

  246. u1.fentry 行号,

  247. t1.fnumber 材料代码,

  248. t1.Fname 材料名称,

  249. t1.Fmodel 材料规格,

  250. u1.fqty 用量,

  251. t4.Fname as 材料类型,

  252. case u1.fmatertype when 371 then '正常' when 372 then '联产品' when 376 then '返回件' end as 领料类型,

  253. case u1.fError when 0 then '正常' when 1 then'没有BOM' end as 错误状态,

  254. u1.FSupplyPrice 供应商维护单价,

  255. u1.FSupplyAmount 供应商维护金额,

  256. case u1.FSupplyPriceType when 1 then '供应商维护单价' when 2 then '最新入库价' when 3 then '计划单价' when 4 then '其它' else '无单价' end as 供应商维护价格来源,

  257. u1.FStockPrice 最新入库单价,

  258. u1.FStockAmount 最新入库价金额,

  259. case u1.FstockPriceType when 1 then '供应商维护单价' when 2 then '最新入库价' when 3 then '计划单价' when 4 then '其它' else '无单价' end as 入库价价格来源

  260. into #result

  261. from #z_ProductCostEntry u1

  262. inner join t_icitem t1 on t1.fitemid=u1.fitemid

  263. inner join #z_ProductCost t2 on t2.finterid=u1.finterid

  264. inner join t_icitem t3 on t2.fitemid=t3.fitemid

  265. inner join t_submessage t4 on t4.Finterid=u1.Ferpclsid

  266.  
  267. insert into #result

  268. select

  269. t1.Fnumber,

  270. t1.Fname,

  271. t1.FModel,

  272. '合计:',

  273. t1.Fnumber,

  274. '',

  275. '',

  276. 0,

  277. '',

  278. '',

  279. '',

  280. 0,

  281. u1.fsupplyamount,

  282. '',

  283. 0,

  284. u1.fstockamount,

  285. ''

  286. from #z_ProductCost u1

  287. inner join t_icitem t1 on u1.fitemid=t1.fitemid

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

  290.  
  291. drop table #z_productCost

  292. drop table #z_productCostEntry


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

 

 

exec zp_z_ProduceCostNow '*ItemNo*','#ItemNo#'
上一篇:PAT甲级 1006 Sign In and Sign Out (25 分)


下一篇:Auto.js微信抢红包脚本