Oracle ERP R12. Add WIP_ENTITY ATTACHMENTS with FND_DOCUMENTS_PKG (API)

以下程式碼範例是用來在WIP_ENTITY (工單)裡加入附件迴紋針說明的用法,分享給各位讀者:

 CREATE PROCEDURE WO_SHORTTEXT(I_ORGANIZATION_ID IN NUMBER,

                         I_WIP_ENTITY_ID   IN NUMBER,

                         I_TITLE           IN VARCHAR2, --PGM_NAME

                         I_DESCRIPTION     IN VARCHAR2, --TITLE DESCRIPTION

                         I_TEXT            IN VARCHAR2,

                         O_DOCUMENT_ID     OUT NUMBER,

                         O_ATTACHED_DOC_ID OUT NUMBER,

                         O_MEDIA_ID        OUT NUMBER)

  IS

    /*

      說明 :

      1. 輸入參數

      I_ORGANIZATION_ID ==> ORGANIZATION_ID

      I_WIP_ENTITY_ID   ==> 工單的WIP_ENTITY_ID

      I_TITLE ==> 迴紋針的Title

      I_DESCRIPTION   ==> 迴紋針的說明

      I_TEXT ==> 欲附加的文字內容

      2. 輸出變數

      O_DOCUMENT_ID / O_ATTACHED_DOC_ID / O_MEDIA_ID        

    */

    T_ROWID           ROWID;

    T_SEQ_NUM NUMBER;    

    T_FND_USER_ID NUMBER := 0; --DEFAULT SYSADMIN

    T_SHORT_DATATYPE_ID NUMBER := 1; --SHORT_TEXT

    T_CATEGORY_ID       NUMBER := 6; --WIP Job/Schedule Attachments

  BEGIN

    FND_PROFILE.GET('USER_ID',T_FND_USER_ID); --初始化環境變數

    --找出工單的CATEGORY ID

    SELECT A.CATEGORY_ID

      INTO T_CATEGORY_ID

      FROM FND_DOCUMENT_CATEGORIES A

     WHERE A.NAME = 'WIP_ENTITY_ATTACHMENTS';

    --

    IF T_FND_USER_ID IS NULL THEN

      T_FND_USER_ID := 0;

    END IF;

    --找出目前最後一個的文件序號再加一為新增的文件序號

    SELECT NVL(MAX(FAD.SEQ_NUM),0)+1

      INTO T_SEQ_NUM

      FROM FND_ATTACHED_DOCUMENTS FAD

     WHERE 1=1

       AND FAD.PK1_VALUE = TO_CHAR(I_WIP_ENTITY_ID)

       AND FAD.PK2_VALUE = TO_CHAR(I_ORGANIZATION_ID);

      

    --呼叫API

    FND_DOCUMENTS_PKG.INSERT_ROW(

    X_Rowid => T_ROWID,

    X_document_id => O_DOCUMENT_ID,    

    X_creation_date => SYSDATE,

    X_created_by => T_FND_USER_ID,

    X_last_update_date => SYSDATE,

    X_last_updated_by => T_FND_USER_ID,

    X_datatype_id => T_SHORT_DATATYPE_ID,

    X_create_doc => 'Y',

    X_category_id => T_CATEGORY_ID,

    X_security_type => 1,

    X_security_id => I_ORGANIZATION_ID,

    X_description => I_DESCRIPTION,

    X_media_id => O_MEDIA_ID,

    X_title => I_TITLE,

    X_language => 'US',

    X_publish_flag => 'N', --This flag allow the file to share across multiple organization 

    X_usage_type => 'S');

    --

    IF O_MEDIA_ID IS NOT NULL THEN

      --寫入內容

      INSERT INTO FND_DOCUMENTS_SHORT_TEXT(MEDIA_ID,SHORT_TEXT)

      VALUES(O_MEDIA_ID,I_TEXT);

      --

      SELECT FND_ATTACHED_DOCUMENTS_S.NEXTVAL

        INTO O_ATTACHED_DOC_ID 

        FROM DUAL;

      --建立文件內容與迴紋針的關聯

      INSERT INTO FND_ATTACHED_DOCUMENTS(

      ATTACHED_DOCUMENT_ID,

      DOCUMENT_ID,

      CREATION_DATE,

      CREATED_BY,

      LAST_UPDATE_DATE,

      LAST_UPDATED_BY,

      SEQ_NUM,

      ENTITY_NAME,

      PK1_VALUE,

      PK2_VALUE,

      AUTOMATICALLY_ADDED_FLAG,

      CATEGORY_ID)

      VALUES(

      O_ATTACHED_DOC_ID,

      O_DOCUMENT_ID,

      SYSDATE,

      T_FND_USER_ID,

      SYSDATE,

      T_FND_USER_ID,

      T_SEQ_NUM,

      'WIP_DISCRETE_JOBS',

      TO_CHAR(I_WIP_ENTITY_ID),

      TO_CHAR(I_ORGANIZATION_ID),

      'N',

      T_CATEGORY_ID);      

    END IF;

  END;