5.移转 Aras PLM大小事-Agile 汇出 Document

Agile 汇出 Document

本节汇出文件Document资料和料号雷同，同样继承Item物件
item.class = 9000 即为文件类别
如果不确定Class ID，可以先使用Agile本身的汇出报表来检视

select 
item.item_number item_number -- 文件编号
        ,rev.description name  --文件名称
        ,nb.description classification  --文件类别
        ,usr.last_name owned_by_id --建立者
        ,(select agile_flex.text from agile_flex 
            where agile_flex.row_id=item.id and agile_flex.attid=2017) description --文件说明
        ,NVL(list11.entryvalue,'1') cn_factory -- 厂别
        ,NVL(rev.rev_number,'00') cn_revision --JPC文件版本
        ,replace(p2.text11,'','N/A') cn_project   --专案代号
        , (TO_CHAR (rev.release_date,'YYYY-MM-DD') || 'T' || TO_CHAR (rev.release_date,'HH24:MI:SS'))  released_date--Released Date
        ,GETDOCREVTYPE(rev.release_type) cn_lifecycle -- Lifecycle
       ,split(product_lines) cn_product_line -- Product Line(s)
        ,p2.text14 iso_number   --ISO编号
        ,item.id item_id
from item
inner join nodetable nb on nb.id = item.subclass
inner join rev rev on rev.item = item.id
inner join item_p2 p2 on p2.item_number = item.item_number
inner join agileuser usr on usr.id=p2.create_user
left join listentry list11 on list11.entryid = p2.list11
where item.class = 9000
and rev.LATEST_FLAG=1
order by item.item_number

转换文件类别GETDOCREVTYPE

create or replace FUNCTION GETDOCREVTYPE 
(
  RELEASED_TYPE IN VARCHAR2 
) RETURN VARCHAR2
IS
    state_type  varchar2(32767) :='';
BEGIN
    if RELEASED_TYPE = '22507' then
        state_type:= 'Released';
    ELSIF RELEASED_TYPE = '25096' then
        state_type:= 'Obsolete';
    ELSE
        state_type:='Preliminary';
    end if;
  RETURN state_type;
END GETDOCREVTYPE;