1.
2.
3.
4.
01:建立object TYPE
CREATE OR REPLACE TYPE TP_BOM_01 AS OBJECT
--這是展BOM要用的TYPE
--
( TOP_PART VARCHAR2(50),
LAST_PART VARCHAR2(50),
UNIT varchar2(20), --單位
Type_ varchar(20), --形態 P, M
BOM_PATH VARCHAR2(4000),
UsagePerSignal decimal(18,4),
BOM_LEVEL int,
Locate varchar2(4096)
);
GO
02:將01的object 建一個table type 好回傳
/*return table type*/
CREATE OR REPLACE TYPE TP_BOM_WHERE_USE_01_TABLE AS TABLE OF GWT.TP_BOM_WHERE_USE_01
GO
03: 單一主建展bom 到最底階.
/* BOM 單一料號*/
CREATE OR REPLACE FUNCTION GWT.FN_BOM_001(
p_date in date,
p_start_with in varchar2,
p_OrgPartno in varchar2 default null, --
p_level in number default 0,
p_usage in number default 1,
p_path in varchar2 default '',
p_bmb09 in varchar2 default '' )
return TP_BOM_01_Table PIPELINED is
out_rec TP_BOM_01 := TP_BOM_01(null,null,null,null,null,null,null, null);
rowCount int;
p_OrgPartno2 varchar2(40);
begin
p_OrgPartno2 := p_OrgPartno;
for c in ( select * from bmb_file, ima_file where ima01=bmb03 and bmb01 like p_start_with and
( bmb04 <= p_date OR bmb04 IS NULL) AND ( bmb05 > p_date OR bmb05 IS NULL) )
loop
--if (( p_level >1 and c.bmb19=3 ) or ( p_level<=1)) and (( p_level >1 and c.ima08='M' ) or ( p_level<=1)) then
if p_level <=0 then
p_OrgPartno2 := c.bmb01;
end if;
select count(*) into rowCount from bmb_file where bmb01=c.bmb03 and
( ( bmb04 <= p_date OR bmb04 IS NULL) AND ( bmb05 > p_date OR bmb05 IS NULL) );
if rowCount =0 then -- and c.bmb19=3 then
out_rec.TOP_PART := p_OrgPartno2;
out_rec.LAST_PART := c.bmb03;
out_rec.UNIT := c.bmb10;
out_rec.Type_ := c.ima08;
out_rec.BOM_PATH := p_path || '>>' || c.bmb03 || '(' || p_bmb09 || ')' ;
out_rec.UsagePerSignal := c.BMB06 * p_usage ;
out_rec.BOM_LEVEL := p_level;
out_rec.Locate := '';
PIPE ROW(out_rec);
end if;
if 1=1 then --c.bmb19=3 then
for c2 in ( SELECT * from TABLE(FN_BOM_001( p_date, c.bmb03, p_OrgPartno2, p_level+1, c.BMB06 * p_usage, p_path || '>>' || p_start_with || '(' || p_bmb09 || ')' ,c.bmb09 )) )
loop
out_rec.TOP_PART := c2.TOP_PART;
out_rec.LAST_PART := c2.LAST_PART;
out_rec.BOM_PATH := c2.BOM_PATH ;
out_rec.UNIT := c2.UNIT;
out_rec.Type_ := c2.Type_;
out_rec.UsagePerSignal := c2.UsagePerSignal;
out_rec.BOM_LEVEL := c2.BOM_LEVEL;
out_rec.Locate := c2.Locate;
PIPE ROW(out_rec);
end loop;
end if;
--end if;
end loop;
end;
GO
04:一個一個展太
/*全展*/
CREATE OR REPLACE FUNCTION GWT.FN_BOM_ALL
return TP_BOM_01_Table PIPELINED is
out_rec TP_BOM_01 := TP_BOM_01(null,null,null,null,null,null,null, null);
rowCount int;
p_OrgPartno2 varchar2(40);
begin
for c in ( select * from ima_file where ( ima01 like '01%' or ima01 like '0T%' or ima01 like '13%' or ima01 like '1013%' ) and ((ima01 like '01%' and substr(ima01,12,1)<>'0' ) or (ima01 not like '01%' )) )
loop
-- SELECT * from TABLE(FN_BOM_001( to_date('16/05/18','yy/MM/dd'), '01DS102200GT', null, 0, 1, '' ))
for c2 in ( SELECT * from TABLE(FN_BOM_001( sysdate , c.ima01,null, 0,1,'', '')) )
loop
out_rec.TOP_PART := c2.TOP_PART;
out_rec.LAST_PART := c2.LAST_PART;
out_rec.BOM_PATH := c2.BOM_PATH ;
out_rec.UNIT := c2.UNIT;
out_rec.Type_ := c2.Type_;
out_rec.UsagePerSignal := c2.UsagePerSignal;
out_rec.BOM_LEVEL := c2.BOM_LEVEL;
out_rec.Locate := c2.Locate;
PIPE ROW(out_rec);
end loop;
end loop;
end;
GO
使用方式
01: 單一BOM 查詢
SELECT * from TABLE(FN_BOM_001( sysdate , 'MastPart',null, 0,1,'', ''))
02: 全部料號BOM 查詢
SELECT * FROM TABLE(FN_BOM_ALL())
03: 將查詣的BOM 轉成TABLE 存放
CREATE TABLE bom_20160606 AS
SELECT * FROM TABLE(FN_BOM_ALL())
