Oracle ERP R12. - 如何停止PO Output for Communication

很多公司會客制自動更新PO內容的程式，其更新後再呼叫API進行Apporval，而系統的標準行為則會自動呼叫［PO Output for Communication］，其將造成系統資源的浪費。
Oracle TAR : 387853.1 的回覆如下 :

It is not possible to stop the 'PO Output for Communication' program from launching automatically based on how the PO Approval process triggered (whether it is submitted from Approval form or Change API).

Development in Bug # 4632841 already mentioned that:
'Approval is common code line regardless whether it is submitted from Approval form or Change API.
Hence during approval this concurrent request is submitted always. This is more essential if the supplier needs to be communicated.


It is less likely that development will work on this requirement as an enhancement, as it is a standard practice to communicate the PO creation & changes to supplier based on the communication method setting in the supplier and supplier site definition form.

WF is very flexible and can be easily customizable' to achieve this kind of 'deviations from the standard practice' or special requirements.
If po output format is set to 'PDF' irrespective of the communication set.
PO approval launches request to generate pdf document. This is done every time so that PO PDF document can be viewed using view documents special menu option. This doesn't depend on the communication method set at the vendor level and it can't be disabled unless PO output format in purchasing options is changed to text (Which have effect on entire operating unit and not a great workaround)
or approval workflow need to be customized.

Behavior is as per the design.

However EDI concurrent program still communicates the document based on EDI setup at the supplier when it is run.
A possible workaround that can be considered if it meets the Business Requirements is below:
Purchasing Super User - Navigate to Purchasing Options form
(Setup / Organizations / Purchasing Options)

Choose to use Text instead of PDF for the PO Output field. Then PO Output for Communication is not launched via PO Approval

其主要籍由設定Purchasing Options的Output Format為Text，造成與［PO Output for Communication ］的報表格式不一致，進而無法自動執行該程式。

Oracle ERP R12. 人員帳號停用Vs. PR Interface 注意事項

以下為本人Import PR時發現的錯誤，經分析其又關聯到人員資料及品號主檔。

狀況如下：

假設品號主檔內有設定Default的Buyer，其若該Buyer的狀態已失效（可能是是帳號停用或是Buyer角色失效）。

若PO_REQUISITIONS_INTERFACE_ALL沒有特別指定目前尚生效中的Buyer，則PR Interface會主動將MTL_SYSTEM_ITEMS_B.BUYER_ID的值帶入PO_REQUISITIONS_INTERFACE_ALL.SUGGESTED_BUYER_ID，這將造成Import PR失敗。

其錯誤訊息為：

The suggested buyer is not a valid buyer or worker

Cause:        You entered an invalid buyer.  

Action:        Enter a valid buyer.

Solution : 

Step 1. Update PO_REQUISITIONS_INTERFACE_ALL

SUGGESTED_BUYER_ID  = 將其修正為目前尚生效的Buyer id

TRANSACTION_ID = NULL

PROCESS_FLAG = 1

REQUISITION_LINE_ID = NULL

REQ_DISTRIBUTION_ID = NULL

PROCESS_FLAG = 1

TRANSACTION_TYPE = UPDATE

Step 2. 執行Requisition Import ==> 重新Import

為避免類似狀況再發生，則建議以下動作亦需執行：

1. 品號主檔該失效的Buyer Id品號，皆需更新為生效中的Buyer Id，方法如下：

Step 1. 

INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE(ORGANIZATION_ID,

                                       INVENTORY_ITEM_ID,

                                       BUYER_ID,

                                       PROCESS_FLAG,

                                       TRANSACTION_TYPE)

SELECT MSI.ORGANIZATION_ID,

       MSI.INVENTORY_ITEM_ID,

       <新Buyer ID>,

       1,

       'UPDATE'

  FROM MTL_SYSTEM_ITEMS_B MSI

 WHERE MSI.ORGANIZATION_ID = <所屬Organization ID>

   AND MSI.BUYER_ID = <舊Buyer ID>

Step 2. Import Item (Update only)

2. 修正公司的人員異動/離職手續內容，資訊人員除了關閉帳號外，亦需執行上述步驟。

  

Oracle ERP R12. SQL For 查詢SOB/LE/ORG/ORGANIZATION

有鑑於客製程式常需要取得SOB/LE/ORG_ID(OU Level)/Organization_id(Inventory Level)等資料.
為了方便及減少重覆撰寫相同SQL的狀況,故本人會將以下SQL建立為View,供其它開發人員直接調用.


SELECT /*
       FOR TRACE OU ORG ID VS. INV ORGANIZATION ID
       與ORG_ORGANIZATION_DEFINITIONS雷同,但資料更為完整
       */
       C.LEGAL_ENTITY LE_ID,
       C.OPERATING_UNIT ORG_ID,
       HOU.NAME ORG_NAME,
       A.ORGANIZATION_ID ORGANIZATION_ID,
       B.ORGANIZATION_CODE,
       D.LANGUAGE,
       D.NAME SOB_NAME,
       D.SHORT_NAME SOB_SNAME,
       D.CHART_OF_ACCOUNTS_ID,
       D.CURRENCY_CODE,
       D.PERIOD_SET_NAME,
       D.CHART_OF_ACCOUNTS_NAME,
       D.SET_OF_BOOKS_ID,
       B.MASTER_ORGANIZATION_ID,
       E.NAME ORGANIZATION_NAME,
       F.ADDRESS_LINE_1,
       F.ADDRESS_LINE_2,
       F.ADDRESS_LINE_3,
       F.COUNTRY,
       F.TELEPHONE_NUMBER_1,
       F.TELEPHONE_NUMBER_2,
       F.TELEPHONE_NUMBER_3,
       D.DESCRIPTION SOB_DESCRIPTION
  FROM HR_ORGANIZATION_INFORMATION A
  JOIN MTL_PARAMETERS B
    ON A.ORGANIZATION_ID = B.ORGANIZATION_ID
  JOIN INV_ORGANIZATION_INFO_V C
    ON A.ORGANIZATION_ID = C.ORGANIZATION_ID
  JOIN (SELECT SOB.SET_OF_BOOKS_ID,
               SOB.CURRENCY_CODE,
               SOB.CHART_OF_ACCOUNTS_ID,
               FIFS.ID_FLEX_STRUCTURE_NAME CHART_OF_ACCOUNTS_NAME,
               FIFS.LANGUAGE,
               SOB.NAME NAME,
               SOB.PERIOD_SET_NAME,
               SOB.ACCOUNTED_PERIOD_TYPE,
               SOB.SHORT_NAME,
               SOB.LATEST_OPENED_PERIOD_NAME,
               SOB.DESCRIPTION
          FROM FND_ID_FLEX_STRUCTURES_TL FIFS,
               GL_SETS_OF_BOOKS SOB
         WHERE FIFS.ID_FLEX_NUM = SOB.CHART_OF_ACCOUNTS_ID
           AND FIFS.APPLICATION_ID = 101
           AND FIFS.ID_FLEX_CODE = 'GL#') D
    ON C.SET_OF_BOOKS_ID = D.SET_OF_BOOKS_ID
  JOIN HR_ORGANIZATION_UNITS E
    ON E.ORGANIZATION_ID = A.ORGANIZATION_ID
  JOIN HR_OPERATING_UNITS HOU
    ON HOU.ORGANIZATION_ID = C.OPERATING_UNIT
  LEFT JOIN HR_LOCATIONS F
    ON E.LOCATION_ID = F.LOCATION_ID
 where A.ORG_INFORMATION_CONTEXT = 'Accounting Information';

Oracle ERP R12. FND_PROFILE.VALUE('GL_SET_OF_BKS_ID')取值順序

Oracle ERP 的Profile分為以下層級:
Site : 整個DB Level
Application :  By模組
Responsibility : By Responsibility
User : By User獨立設定


但是並不是每個Profile都會有4個Level,這要看該Profile設定最低到那一階才會有作用.
例如 : 帳本,就不會By User設定,一般是By Responsibility才合理


一般程式開發人員認知的取值順序為
User ==> Responsibility ==> Application ==> Site


但需注意的是,若你是呼叫FND_PROFILE.VALUE('GL_SET_OF_BKS_ID')取得Set Of Book ID的話,
其取值順序不是User ==> Responsibility ==> Application ==> Site,而是Site ==> Application ==> Responsibility.


故若你有自行客制的程式必需要取得Responsibility的SOB_ID的話,則遇到Multi-Org (多SOB_ID)時,可能用FND_PROFILE.VALUE('GL_SET_OF_BKS_ID'),就會取到錯的值.


建議改用以下SQL取得:

SELECT TO_NUMBER(O2.ORG_INFORMATION3)

  FROM HR_ORGANIZATION_INFORMATION O1,

       HR_ORGANIZATION_INFORMATION O2,

       GL_LEDGERS_PUBLIC_V         GL

 WHERE O1.ORGANIZATION_ID = O2.ORGANIZATION_ID

   AND O1.ORGANIZATION_ID = :ORG_ID --在此輸入OU Level的Org ID

   AND O1.ORG_INFORMATION_CONTEXT = 'CLASS'

   AND O2.ORG_INFORMATION_CONTEXT = 'Operating Unit Information'

   AND O1.ORG_INFORMATION1 = 'OPERATING_UNIT'

   AND O1.ORG_INFORMATION2 = 'Y'

   AND O2.ORG_INFORMATION3 = GL.LEDGER_ID;