mysql创建bom表_五、K3 WISE 开发插件《直接SQL报表开发新手指导 - BOM成本报表》...

========================

目录：

1、直接SQL报表

========================

1、直接SQL报表

以BOM成本报表为例，在销售模块部署，需要购买【金蝶 K3 BOS集成开发工具】使用许可

I、本报表采用存储过程的形式编写，需要在数据库执行存储过程。

/****** Object: StoredProcedure [dbo].[pro_bobang_BOMCost] Script Date: 07/29/2015 16:09:11 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create PROC [dbo].[pro_bobang_BOMCost]

@FBomNumber1 varchar(50), --bom单号

@FBomNumber2 varchar(50) --bom单号

as

begin

SET ANSI_WARNINGS OFF

set nocount on

--1.--业务员查找某个BOM单,包含所有BOM(已使用,未使用,已审核,未审核)

;with cte as (

select convert(varchar(100),'') as cen,Finterid,FBOMNumber as fppbomnumber,convert(varchar(50),'') as fpbomnumber,Fbomnumber AS FCbomnumber,fitemid,fitemid as fpitemid,0 as fpinterid,convert(decimal(18,4),1) as FBomQty,convert(varchar(500),RIGHT('000000'+CONVERT(varchar(10),Finterid),6)) as code from ICBOM

where (1=1)

and FBOMNumber >= @FBomNumber1

and FBOMNumber <= case when @FBomNumber2='' then (select MAX(FBOMNumber) from ICBOM) else @FBomNumber2 end

union all

select convert(varchar(100),cen+'------'),a.finterid,c.fppbomnumber,convert(varchar(50),c.FCbomnumber) as fpbomnumber,a.fbomnumber as fcbomnumber,a.fitemid,c.fpitemid,a.fpinterid,convert(decimal(18,4),a.FAuxQty) as FBomQty,convert(varchar(