SQL 与 orcale 数据库对BOM多级展开的实现方法

一、Oracle数据库

展开全部  

文本： Oracle数据库 OracleERP 

Oracle层次树查询

Oracle层次树是通过Connect by[条件] Start with [条件] 来实现。这一功能非常好用，比如ERP中的BOM、HR中的组织架构，就算是这类的典型应用了。不过，OracleEBS11i中好象没实现，BOM也没有使用树这种组件。

下面就做一个简单的MRP试算过程，来说明层次树的应用。

1.1 建一个简单BOM表。

create table hek_bom(master_id varchar2(20), master_name varchar2(50), sub_id varchar(20), sub_namevarchar(20) )

1.2 放入测试数据。

begin

insert into hek_bomvalues('0001','V1卡车','10001','V1发动机');

insert into hek_bomvalues('0001','V1卡车','10002','V1车架'); i

nsert into hek_bomvalues('0001','V1卡车','10003','V1车轮');

insert into hek_bomvalues('0002','V2卡车','10001','V1发动机');

insert into hek_bomvalues('0002','V2卡车','10002','V1车轮');

insert into hek_bomvalues('0002','V2卡车','10004','V2车架');

insert into hek_bomvalues('0003','V3卡车','10004','V1发动机');

insert into hek_bomvalues('0004','V4卡车','10005','V2发动机');

insert into hek_bomvalues('10001','V1发动机','10006','V1活塞');

insert into hek_bomvalues('10001','V1发动机','10007','V1火花器');

insert into hek_bomvalues('10007','V1活塞','10008','V1橡胶片');

insert into hek_bomvalues('10007','V1活塞','10009','V1螺丝');

end;

1.3 查询一下明细：

select t.* fromhek_bom t for update

------------------------

1 0001 V1卡车 10001V1发动机

2 0001 V1卡车 10002 V1车架

3 0001 V1卡车 10003 V1车轮

4 0002 V2卡车 10001V1发动机

5 0002 V2卡车 10002 V1车轮

6 0002 V2卡车 10004 V2车架

7 0003 V3卡车 10004V1发动机

8 0004 V4卡车 10005V2发动机

9 10001 V1发动机 10006V1活塞

10 10001 V1发动机 10007V1火花器

11 10006 V1活塞 10008V1橡胶片

12 10006 V1活塞 10009V1螺丝

1.4问题：求V1螺丝料品有哪几层产品用到。

select level,t.* fromhek_bom t connect by prior t.master_id=t.sub_id start with t.sub_id='10009'

--注意这条SQL语名的语法，connectby prior t.master_id=t.sub_id表示优先从子节点到父节点。

--start witht.sub_id='10009'相当于where t.sub_id='10009'

---------------------------------------------

1 10006 V1活塞 10009V1螺丝

2 10001 V1发动机 10006V1活塞

3 0001 V1卡车 10001V1发动机

4 0002 V2卡车 10001V1发动机

1.5 问题：求：V1卡车的BOM结构：

select level,t.* fromhek_bom t connect by prior t.sub_id=t.master_id start with t.master_name='V1卡车'

--connect by priort.sub_id=t.master_id表示优先从父节点查询到子节点。

---------------------------------------------------------------------------------------------

1 0001 V1卡车 10001V1发动机

2 10001 V1发动机 10006V1活塞

3 10006 V1活塞 10008V1橡胶片

4 10006 V1活塞 10009V1螺丝

5 10001 V1发动机 10007V1火花器

6 0001 V1卡车 10002 V1车架

7 0001 V1卡车 10003 V1车轮

―――――――――――――――――――――――――――――――――――――

二、SQL数据库（多极展开算标准材料成本）

文本： SQL数据库 金碟K3/10.4 ERP 

-----第二步-----**********前台用查询工具***********************************************

----1\多极展开算标准材料成本

if exists(select *from sysobjects where name ='costbom_leiyong')

drop proccostbom_leiyong

go

create proccostbom_leiyong @bomno  varchar(80),@total decimal(28, 10) output

as

withbom(indexid,levelid,fchildnumber,fchildname,fchildmodel,fchildunitname,fchildqty,fchildtype,

fstatus,fusestatus )as 

(selectrow_number()OVER (ORDER BY fchildnumber ASC) ,1 ,fchildnumber,fchildname,

fchildmodel,fchildunitname,fchildqty, fchildtype,fstatus,fusestatus 

from vicbom_leiyongwhere fnumber in ( @bomno ) and fusestatus='使用'

union all 

select 

indexid * 100 +row_number() OVER (ORDER BY v1.fchildnumber), 

v2.levelid+1,v1.fchildnumber,v1.fchildname,v1.fchildmodel,v1.fchildunitname, 

cast(v1.fchildqty *v2.fchildqty AS DECIMAL(28, 10)) as fchildqty ,v1.fchildtype,

v1.fstatus,v1.fusestatus 

from vicbom_leiyong v1inner join bom v2 on v1.fnumber=v2.fchildnumber

----展开项筛选条件

and v1.fusestatus='使用'

and v1.fnumber notin(select fnumber from t_icitem where  fmrporder=1

))

-----insert into 标准成本明细表

select 

@bomno as BOM单号,@totalas 材料总成本,

case whenv3.ferpclsid=1 or v3.fmrporder=1 then '采购申请' 

when v3.ferpclsid=2and v3.fmrporder<>1 then '生产任务' 

when v3.ferpclsid=3and v3.fmrporder<>1 then '委外任务' 

when v3.ferpclsid=5and v3.fmrporder<>1 then '虚拟跳层'

else'请联系此表原代码作者' 

end as 物料类型, 

case whenv3.ferpclsid=1 then '采购件'

when v3.ferpclsid=2then '自制件'

when v3.ferpclsid=3then '外协件'

when v3.ferpclsid=5then '虚拟件'

else'请联系此表原代码作者' 

end as 物料属性,

case whenv3.ferpclsid<>1 and v3.fnumber not in(select fnumber from vicbom_leiyongwhere fusestatus='使用') then '无bom' 

whenv3.ferpclsid<>1 and v3.fnumber in(select fnumber from vicbom_leiyongwhere fusestatus='使用') then '有bom' 

whenv3.ferpclsid=1  then '不需要' 

end as BOM状态,

v4.fchildnumber as物料代码,v4.fchildname as 物料名称,v4.fchildmodel as 规格型号,

v4.fchildunitname as单位,v4.fchildqty as 用量,v5.fprice as 单价,

v4.fchildqty*v5.fprice*v4.fchildtypeas 金额 , v4.fstatus as 上级BOM审核状态,v4.fusestatus as 上级BOM使用状态

from bom as v4 innerjoin t_icitem as v3 on v3.fnumber=v4.fchildnumber

left join 价格资料v41 asv5 on v5.fnumber=v4.fchildnumber

and 

(case whenv3.ferpclsid=1 or v3.fmrporder=1 then '采购单价' 

when v3.ferpclsid=3and v3.fmrporder<>1 then '委外加工单价' 

end) = v5.fptype 

-----筛选最终采购件 

----where(v3.ferpclsid=1 or v3.fmrporder=1)

order bycast(v4.indexid as varchar(50))