Oracle ERP R12. 使用interface批量變更品號的operation code . (Mass changes item's operation code use BOM_OP_SEQUENCES_INTERFACE. )

Sample code :


DECLARE
  --
  T_OLD_OP VARCHAR2(10) := '2T02';  --**** old operation code
  T_NEW_OP VARCHAR2(10) := '2S02'; --**** new operation code
  T_NEW_OP_ID NUMBER;
  T_ORGANIZATION_ID NUMBER := 85; --organization id
  T_DISABLE_DATE DATE := SYSDATE;
  T_EFFECT_DATE DATE := SYSDATE+1/86400;  
  --
  CURSOR C_LIST  --item list ==> user define 
  IS
  SELECT D.ORGANIZATION_ID,
         D.INVENTORY_ITEM_ID,
         A.ROUTING_SEQUENCE_ID,
         B.OPERATION_SEQUENCE_ID,
         B.OPERATION_SEQ_NUM,
         C.STANDARD_OPERATION_ID
    FROM BOM_OPERATIONAL_ROUTINGS A
    JOIN BOM_OPERATION_SEQUENCES_V B--BOM_OPERATION_SEQUENCES B
      ON A.COMMON_ROUTING_SEQUENCE_ID = B.ROUTING_SEQUENCE_ID
    LEFT JOIN BOM_STANDARD_OPERATIONS C
      ON B.STANDARD_OPERATION_ID = C.STANDARD_OPERATION_ID
    JOIN MTL_SYSTEM_ITEMS_B D
      ON A.ORGANIZATION_ID = D.ORGANIZATION_ID
     AND A.ASSEMBLY_ITEM_ID = D.INVENTORY_ITEM_ID
    JOIN BOM_DEPARTMENTS F
      ON F.DEPARTMENT_ID = B.DEPARTMENT_ID
     AND F.ORGANIZATION_ID = A.ORGANIZATION_ID
   WHERE A.ORGANIZATION_ID = T_ORGANIZATION_ID
     AND C.OPERATION_CODE = T_OLD_OP
     AND (B.DISABLE_DATE IS NULL OR
          B.DISABLE_DATE >= SYSDATE);
BEGIN
  --Find the new operation id
  SELECT BSO.STANDARD_OPERATION_ID
    INTO T_NEW_OP_ID
    FROM BOM_STANDARD_OPERATIONS BSO
   WHERE BSO.ORGANIZATION_ID = T_ORGANIZATION_ID
     AND BSO.OPERATION_CODE = T_NEW_OP;
   
  FOR C1 IN C_LIST LOOP
    --STEP 1. Disable old operation
    BEGIN
      INSERT INTO BOM_OP_SEQUENCES_INTERFACE
      (ASSEMBLY_ITEM_ID,  
       ORGANIZATION_ID,
       ROUTING_SEQUENCE_ID,
       OPERATION_SEQUENCE_ID,
       DISABLE_DATE,
       TRANSACTION_TYPE,
       PROCESS_FLAG,
       BATCH_ID)
      VALUES(C1.INVENTORY_ITEM_ID,
             C1.ORGANIZATION_ID,
             C1.ROUTING_SEQUENCE_ID,
             C1.OPERATION_SEQUENCE_ID,
             T_DISABLE_DATE,
             'Update',
             1,
             1);
    END;
 
    --STEP 2. Add new operation
    BEGIN
      INSERT INTO BOM_OP_SEQUENCES_INTERFACE(
       ASSEMBLY_ITEM_ID,  
       ORGANIZATION_ID,
       ROUTING_SEQUENCE_ID,
       OPERATION_SEQUENCE_ID,
       OPERATION_SEQ_NUM,    
       EFFECTIVITY_DATE,
       STANDARD_OPERATION_ID,
       REFERENCE_FLAG,
       TRANSACTION_TYPE,
       PROCESS_FLAG,
       BATCH_ID)
      VALUES(C1.INVENTORY_ITEM_ID,
             C1.ORGANIZATION_ID,
             C1.ROUTING_SEQUENCE_ID,
             C1.OPERATION_SEQUENCE_ID,
             C1.OPERATION_SEQ_NUM,
             T_EFFECT_DATE,
             T_NEW_OP_ID,
             1,
             'Create',
             1,
             2);
    END;  
  END LOOP;
END;

Step 3. Run BOM & Routing Import (Bill and Routing Interface) ==> batch id is set to 1 (For disable old operation code)

Step 4. Run BOM & Routing Import (Bill and Routing Interface) ==> batch id is set to 2 (For add new operation code)

Oracle ERP R12. Receiving Transaction can not find the RT number .

Receipt number(RT No.) can not find the experience to share with you in this.
If you are sure that points to receipt also learned that the receipt number(RT No.), but acceptance and delivery can not be found the receipt number(RT No.) in the Receiving Transaction Function. Please check the RCV_TRANSACTIONS_INTERFACE , and if so, then in the confirmation after such interface delete. After re-entering the Receiving Transaction should be able to find the receipt number (RT No.).
The Function of Receiving Transaction Receipt Number LOV will exclude the Interface are still part of the data, in order to ensure that the data would not have repeated transactions.


在這跟各位分享一下收料單找不到的經驗。
若你確定已進行點收也得知收料單，但是卻在Receiving Transaction該Function裡找不到該收料點進行驗收及入庫動作時。 請檢查RCV_TRANSACTIONS_INTERFACE 是否有該收料單Pending的資料， 若有則在確認後將該筆interface刪除。之後再進入Receiving Transaction就應該可以找到該收料點了。
因為Receiving Transaction該Function的Receipt Number LOV會排除掉Interface尚有資料的部分，以確保同一筆資料不會有重覆的交易。

Oracle ERP R12. Delivery is closed but SO Line status is 'Picked'.

今天User反應一狀況，其表示有一筆Delivery 已做Ship Confirm ，而且Delivery 的狀態也是呈現Closed ，但是訂單項次(SO Line)的Status卻仍停在Picked !

解法如下 (執行Interface Trip Stop - SRS):
OM Module => Shipping => Interface => Run => Interface Trip Stop - SRS

Oracle ERP R12. How to use the interface tables update attribute to null?

一般而言透過interface table匯入資料時,其欄位值若為空值(null),則代表該欄位不匯入,也就是不會去動該欄位.
故若該欄位原本有資料,也不會將其清空為空值(null).
但若需要透過interface table來將該欄位值清空,那要怎麼做呢?
其實很簡單,方法如下:
1. 若為數字欄位,則輸入 -999999
2. 若為文字欄位,則輸入 "!".
====================================
If a field for numeric fields, fill -999999.
If a field for character fields, fill "!".

Oracle ERP R12. Add SO_SHORTTEXT with FND_DOCUMENTS_PKG (API)

以下程式碼範例是用來在SO (訂單)裡加入附件迴紋針說明的用法,分享給各位讀者.該範例是例用SHORTTEXT來紀錄訂單的正面麥頭及側面麥頭

CREATE PROCEDURE SO_SHORTTEXT(I_ORG_ID IN NUMBER,
                         I_HEADER_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
    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 := 326; --Documents
    T_EXISTS VARCHAR2(10);
  BEGIN
    FND_PROFILE.GET('USER_ID',T_FND_USER_ID);
    --
    IF T_FND_USER_ID IS NULL THEN
      T_FND_USER_ID := 0;
    END IF;
    --
    SELECT DECODE(COUNT(*),
                  0,'N',
                  'Y')
      INTO T_EXISTS
      FROM HPX_OE_ORDER_MARK_V HOOM
     WHERE HOOM.HEADER_ID = I_HEADER_ID
       AND HOOM.ORG_ID = I_ORG_ID
       AND HOOM.DOCUMENT_DESCRIPTION = I_TITLE;
    --
    IF I_TITLE = '訂單正麥' THEN
      T_SEQ_NUM := 10;
    ELSIF I_TITLE = '訂單側麥' THEN
      T_SEQ_NUM := 20;
    ELSE
      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_HEADER_ID);
    END IF;
    --
    IF T_EXISTS = 'N' THEN
      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 => 4,
      X_security_id => NULL,
      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 => 'O'); --O:可被修改 / S:不能修改
    ELSE
      SELECT MAX(HOOM.media_id)
      INTO O_MEDIA_ID
      FROM HPX_OE_ORDER_MARK_V HOOM
     WHERE HOOM.HEADER_ID = I_HEADER_ID
       AND HOOM.ORG_ID = I_ORG_ID
       AND HOOM.DOCUMENT_DESCRIPTION = I_TITLE;
    END IF;    
    --    
    IF O_MEDIA_ID IS NOT NULL AND T_EXISTS = 'N' 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,
      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,
      'OE_ORDER_HEADERS',
      TO_CHAR(I_HEADER_ID),
      'N',
      T_CATEGORY_ID);      
    ELSIF T_EXISTS = 'Y' THEN
      UPDATE FND_DOCUMENTS_SHORT_TEXT FST
         SET FST.SHORT_TEXT = I_TEXT             
       WHERE FST.MEDIA_ID = O_MEDIA_ID;
      --
      UPDATE FND_DOCUMENTS FD
         SET FD.USAGE_TYPE = 'O' --O:可被修改 / S:不能修改
       WHERE FD.MEDIA_ID = O_MEDIA_ID;
    END IF;
  END;  

Oracle ERP R12. Add PO_SHORTTEXT with FND_DOCUMENTS_PKG (API)

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

Create PROCEDURE PO_SHORTTEXT(I_ORGANIZATION_ID IN NUMBER,
                         I_PO_HEADER_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
    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 := 1000504; --Documents
  BEGIN
    FND_PROFILE.GET('USER_ID',T_FND_USER_ID);
    --
    SELECT A.CATEGORY_ID
      INTO T_CATEGORY_ID
      FROM FND_DOCUMENT_CATEGORIES_TL A
     WHERE A.USER_NAME = 'Documents';
    --
    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_PO_HEADER_ID);      
    --
    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,
      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,
      'PO_HEADERS',
      TO_CHAR(I_PO_HEADER_ID),
      'N',
      T_CATEGORY_ID);    
    END IF;
  END;

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;

Oracle ERP R12. <經驗分享> FA Mass Transfer

當公司有同仁離職時,其所保管的固定資產常會用Mass Transfer功能進行大量移轉.

請注意,若執行後出現移轉失敗的情況,請先檢查這二位(離職人員及接數人員)是否已失效.

若有任何一方失效都會造成移轉的失敗. 請先將其生效,待其資產移轉完後再行失效.

Ps : 一般常見的是,離職人員已失效所造成的.

Oracle ERP R12. - Desktop Integration Manager is not a valid responsibility for the current user.

Step 1. Make sure this module installed in your instance.
Step 2. "ADI: Use Function Security" is applied then set to "No"