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';