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;