Oracle ERP R12. – Ensure that no pending interface data to change status to "Completion No-Charge" with Personalization.

有鑑於許多公司因為製造流程及特性之需求,其standard function並無法滿足user,故其IT人員常會自行客制工單模組等功能,並透過Oracle 所提供之API / Interface來將帳流補回Oracle ERP系統內,但常遇到interface裡的資料尚未處理,而就有user誤將該工單狀態予以變更為Completion No-Charge,這將導至interface執行的失敗.相信許多先進,都有其一套解決方式.而作者我就以透過Personalization的方法來避免user誤將工單狀態變更為Completion No-Charge的出發點來跟各位分享.

構想說明:

1.       建立一DB Function <XX_WIP_CHK_STATUS> : 用來檢查是否有Pending interface資料

2.       由Personalization來呼叫上述function,以判斷是否要將Status欄位限制為read only .

Step 1. Create function : XXX_WIP_CHK_STATUS

CREATE OR REPLACE FUNCTION XXX_WIP_CHK_STATUS(I_ORGANIZATION_ID IN NUMBER,

                                              I_WIP_ENTITY_ID   IN NUMBER) RETURN VARCHAR2

IS

  /*

  When     Who     What

  ==================================

  20110815 WenShen Call by WIPDJMDF - Personalization

  */

  T_CNT NUMBER;                                          

  T_CRLF VARCHAR2(10) := CHR(10)||CHR(13); 

  T_RESULT VARCHAR2(100) := 'OK';

BEGIN

  SELECT COUNT(*)

    INTO T_CNT

    FROM WIP_MOVE_TXN_INTERFACE WMTI

   WHERE WMTI.ORGANIZATION_ID = I_ORGANIZATION_ID

     AND WMTI.WIP_ENTITY_ID = I_WIP_ENTITY_ID;

  --

  IF T_CNT = 0 THEN

    SELECT COUNT(*)

      INTO T_CNT

      FROM MTL_TRANSACTIONS_INTERFACE MTI

     WHERE MTI.ORGANIZATION_ID = I_ORGANIZATION_ID

       AND MTI.TRANSACTION_SOURCE_ID = I_WIP_ENTITY_ID;

    --

    IF T_CNT != 0 THEN

      T_RESULT := '<XXX_WIP_CHK_STATUS>'||T_CRLF||

                                    'Some data in interface (MTL_TRANSACTIONS_INTERFACE) !';     

    END IF;

  ELSE

    T_RESULT := '<XXX_WIP_CHK_STATUS>'||T_CRLF||

                 'Some data in interface (WIP_MOVE_TXN_INTERFACE) !';

  END IF;

  --

  RETURN T_RESULT;

END HPX_WIP_CHK_STATUS;

Step 2. 建立Personalization : Disable Status if some data in interface - W_JOBS.M_STATUS_TYPE_DISP

Step 2.1. 

Step 2.2. 

 Step 2.3. 該部分可省略.

Step 3.  建立Personalization : Disable Status if some data in interface - W_JOBS.STATUS_TYPE_DISP

Step 3.1. ~ 3.3. 同Step 2.1. ~ 2.3.