TIPTOP BOM on ORACLE store procedure

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())