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;