Teamcenter 根据顶层展开BOM到中间表--whb、zgy

create or replace procedure ZL_COCKPIT_OF_BOM is

v_PRODUCT_NAME VARCHAR2(128); --名称
p_cnt number;
product_cnt number;
CURSOR CRS IS
select PRODUCTID from ZL_COCKPIT_PRODUCTS ;

--CURSOR CRS_ITEM IS
-- select RELS.PNAME, WORKOBJ.PQN3_DOC_TYPE2 AS PQN3_DOC_TYPE2,WORKOBJ.POBJECT_NAME,WORKOBJ.PUID ,ITEM.PITEM_ID AS PITEM_ID ,ITEMREV.PITEM_REVISION_ID from INFODBA.PRELEASESTATUS RELS , INFODBA.PRELEASE_STATUS_LIST STARTLIST ,
-- INFODBA.PWORKSPACEOBJECT WORKOBJ , INFODBA.PITEM ITEM, INFODBA.PITEMREVISION ITEMREV
-- WHERE TO_DATE(TO_CHAR(RELS.PDATE_RELEASED,'YYYY/MM/DD'),'YYYY-MM-DD')= TO_DATE(TO_CHAR(SYSDATE,'YYYY/MM/DD'),'YYYY-MM-DD')
-- AND RELS.PUID = STARTLIST.PVALU_0
-- AND WORKOBJ.POBJECT_TYPE = 'QN3PartRevision'
-- AND WORKOBJ.PUID = STARTLIST.PUID
-- AND WORKOBJ.PUID = ITEMREV.PUID
-- AND ITEM.PUID = ITEMREV.RITEMS_TAGU;
CURSOR CRS_ITEM IS
select products.PROD_ID from zoomlion.jsc_products products where
TO_DATE(TO_CHAR(products.date_now,'YYYY/MM/DD'),'YYYY-MM-DD')= TO_DATE(TO_CHAR(SYSDATE-1,'YYYY/MM/DD'),'YYYY-MM-DD');

CURSOR CRS_PRODECTIDS(ITEMID VARCHAR2) IS
SELECT PRO.PRODUCTID FROM ZOOMLION.ZL_COCKPIT_ITEM PRO WHERE PRO.ITEM_ID = ITEMID;
begin
-- delete from ZL_COCKPIT_ITEM;
-- commit;
product_cnt :=0;
select count(1) into product_cnt from ZOOMLION.ZL_COCKPIT_PRODUCTS pros ;
INSERT INTO JSC_ZL_BOM_INSERT_LOG(COUNTOFPRO,INSERT_TYPE) VALUES (product_cnt,'ALL');
COMMIT;
FOR CR_S IN CRS_ITEM LOOP
p_cnt:=0;
--if CR_S.PQN3_DOC_TYPE2 = '总成或整机' THEN
-- select count(1) into p_cnt from ZOOMLION.ZL_COCKPIT_PRODUCTS pros where pros.PRODUCTID = CR_S.PITEM_ID;
-- if p_cnt = 0 then
-- INSERT INTO ZOOMLION.ZL_COCKPIT_PRODUCTS( PRODUCTID ) VALUES (CR_S.PITEM_ID);
-- COMMIT;
-- end if;
-- else
-- FOR ITEMIDE IN CRS_PRODECTIDS(CR_S.PITEM_ID) LOOP
-- select count(1) into p_cnt from ZOOMLION.ZL_COCKPIT_PRODUCTS pros where pros.PRODUCTID = ITEMIDE.PRODUCTID;
-- if p_cnt = 0 then
-- INSERT INTO ZOOMLION.ZL_COCKPIT_PRODUCTS( PRODUCTID ) VALUES (ITEMIDE.PRODUCTID);
-- COMMIT;
-- end if;
-- END LOOP;
--end if;
v_PRODUCT_NAME := '';
select po.POBJECT_NAME
into v_PRODUCT_NAME
from INFODBA.PITEM p, INFODBA.PWORKSPACEOBJECT po
where p.PUID = po.PUID
and p.PITEM_ID = CR_S.PROD_ID;
-- 删除旧数据
delete from ZOOMLION.ZL_COCKPIT_ITEM dsde where dsde.productid = CR_S.PROD_ID;
COMMIT;
-- 递归新数据
ZL_COCKPIT_OF_CHILDREN(PRODUCT_ITEMID => CR_S.PROD_ID,
V_ITEMID => CR_S.PROD_ID,
PRODUCT_NAME => v_PRODUCT_NAME);
INSERT INTO JSC_ZL_BOM_INSERT_LOG(PRODUCTID,INSERT_TYPE) VALUES (CR_S.PROD_ID,'EVERY');
COMMIT;

END LOOP;

--递归入口
FOR CR_1 IN CRS LOOP
v_PRODUCT_NAME:='';
select po.POBJECT_NAME
into v_PRODUCT_NAME
from INFODBA.PITEM pitem, INFODBA.PWORKSPACEOBJECT po
where pitem.PUID = po.PUID
and pitem.PITEM_ID = CR_1.PRODUCTID;
dbms_output.put_line( CR_1.PRODUCTID || '==' || v_PRODUCT_NAME);
-- 删除旧数据
delete from ZOOMLION.ZL_COCKPIT_ITEM dsde where dsde.productid = CR_1.PRODUCTID;
COMMIT;
-- 递归新数据
ZL_COCKPIT_OF_CHILDREN(PRODUCT_ITEMID => CR_1.PRODUCTID,
V_ITEMID => CR_1.PRODUCTID,
PRODUCT_NAME => v_PRODUCT_NAME);
INSERT INTO JSC_ZL_BOM_INSERT_LOG(PRODUCTID,INSERT_TYPE) VALUES (CR_1.PRODUCTID,'EVERY');
COMMIT;
delete from ZOOMLION.ZL_COCKPIT_PRODUCTS products where products.PRODUCTID = CR_1.PRODUCTID;
COMMIT;
END LOOP;

--删除重复项
delete from ZL_COCKPIT_ITEM t
where (t.productid, t.item_id) in
(select t.productid, t.item_id
from ZL_COCKPIT_ITEM t
group by t.productid, t.item_id
having count(*) > 1)
and t.rowid not in (select min(rowid)
from ZL_COCKPIT_ITEM t
group by t.productid, t.item_id
having count(*) > 1);
COMMIT;
--删除父子相同的数据
delete from ZL_COCKPIT_ITEM it where it.productid = it.item_id;
end ZL_COCKPIT_OF_BOM;

create or replace procedure ZL_COCKPIT_OF_CHILDREN(PRODUCT_ITEMID IN VARCHAR2,
V_ITEMID IN VARCHAR2,
PRODUCT_NAME IN VARCHAR2) is

V_RPARENT_BVRU VARCHAR2(30); --父级的bomview对象puid
V_MAXITEMREV VARCHAR2(32); --最新版本号
V_ITEM_TYPE VARCHAR2(32); --item的objecttype
V_ITEMREV_PUID VARCHAR2(30); --版本的puid
V_INFODBA VARCHAR2(32); --message
V_OBJECT_NAME VARCHAR2(128); --名称
V_Compart_number VARCHAR2(256); --外购件代号
V_Compart_Spec VARCHAR2(256); --外购件规格
V_Compart_Standard VARCHAR2(8); --外购件是否为标准件
V_COUNT NUMBER; --检查ID是否存在，取条目数量

--遍历层级的游标
cursor crs is
select p.pitem_id
from infodba.ppsoccurrence pps, infodba.pitem p
where pps.rparent_bvru = V_RPARENT_BVRU
and pps.rchild_itemu = p.puid
order by p.pitem_id;

begin

--取最新的版本号、版本puid、版本对象类型、版本名称
select pr.pitem_revision_id, pr.puid, po.pobject_type, po.pobject_name
into V_MAXITEMREV, V_ITEMREV_PUID, V_ITEM_TYPE, V_OBJECT_NAME
from infodba.pitem p,
infodba.pitemrevision pr,
infodba.pworkspaceobject po
where p.pitem_id = V_ITEMID
and p.puid = pr.ritems_tagu
and pr.puid = po.puid
and pr.pitem_revision_id =
(select pitem_revision_id
from (select pr.ritems_tagu, pr.pitem_revision_id
from infodba.pitemrevision pr,
infodba.pworkspaceobject po
where pr.puid = po.puid
and po.pactive_seq <> 0
order by pr.ritems_tagu,
length(pitem_revision_id) desc,
pitem_revision_id desc) a
where a.ritems_tagu = pr.ritems_tagu
and rownum = 1)
and po.pactive_seq <> 0;

if V_ITEMREV_PUID is not null then
begin
--取bomview对象
begin
select pstr.pvalu_0
into V_RPARENT_BVRU
from infodba.pstructure_revisions pstr
where pstr.puid = V_ITEMREV_PUID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
V_INFODBA := '';
end;
--向表中插入自制件信息
if V_ITEM_TYPE = 'QN3PartRevision' THEN
insert into ZL_COCKPIT_ITEM
(PRODUCTID,
ITEM_ID,
OBJECT_TYPE,
PART_TYPE,
OBJECT_NAME,
PRODUCT_NAME,
compart_number,
compart_spec,
compart_standard,DATE_NOW)
VALUES
(PRODUCT_ITEMID,
V_ITEMID,
V_ITEM_TYPE,
NULL,
V_OBJECT_NAME,
PRODUCT_NAME,
NULL,
NULL,
NULL,SYSDATE());
COMMIT;
end if;
--向表中插入采购件信息
if V_ITEM_TYPE = 'QN3CommPartRevision' THEN
select c.pqn3_bzdh, c.pqn3_clgg, c.pqn3_sfbzj
into V_Compart_number, V_Compart_Spec, V_Compart_Standard
from infodba.pqn3commpartrevision c
where c.puid = V_ITEMREV_PUID;

insert into ZL_COCKPIT_ITEM
(PRODUCTID,
ITEM_ID,
OBJECT_TYPE,
PART_TYPE,
OBJECT_NAME,
PRODUCT_NAME,
compart_number,
compart_spec,
compart_standard,DATE_NOW)
VALUES
(PRODUCT_ITEMID,
V_ITEMID,
V_ITEM_TYPE,
NULL,
V_OBJECT_NAME,
PRODUCT_NAME,
V_Compart_number,
V_Compart_Spec,
V_Compart_Standard,SYSDATE());
COMMIT;
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN
V_MAXITEMREV := '';
end;
end if;
for cr_1 in crs loop
ZL_COCKPIT_OF_CHILDREN(PRODUCT_ITEMID => PRODUCT_ITEMID,
V_ITEMID => CR_1.PITEM_ID,
PRODUCT_NAME => PRODUCT_NAME);
end loop;

end ZL_COCKPIT_OF_CHILDREN;