Oracle eBS經驗分享

今天請教天翔Oracle ERP中有無查看SOB、ORG關係的View？

厲害的天翔告訴我就是這一個=>  ORG_ORGANIZATION_DEFINITIONS

提供給大家參考，為了方便串所有的Org releationship 我改寫如下：

• 
  SELECT hou.business_group_id
       , gsob.set_of_books_id set_of_books_id
       , gsob.short_name
       , gsob.NAME
       , gsob.chart_of_accounts_id chart_of_accounts_id
       , hou.organization_id organization_id
       -- , hou.date_from user_definition_enable_date
         --, hou.date_to disable_date
  ,      mp.organization_code organization_code
       , hou.NAME organization_name
       -- , hoi1.org_information2 inventory_enabled_flag
  ,      DECODE (fpg.multi_org_flag
               , 'Y', DECODE (hoi2.org_information_context
                            , 'Accounting Information', TO_NUMBER
                                                          (hoi2.org_information3)
                            , TO_NUMBER (NULL)
                             )
               , TO_NUMBER (NULL)
                ) operating_unit
       , hou2.NAME ou_name
       , DECODE (hoi2.org_information_context
               , 'Accounting Information', TO_NUMBER (hoi2.org_information2)
               , NULL
                ) legal_entity
       , hou3.NAME le_name
    FROM hr_organization_units hou
       , hr_organization_units hou2
       , hr_organization_units hou3
       , hr_organization_information hoi1
       , hr_organization_information hoi2
       , mtl_parameters mp
       , gl_sets_of_books gsob
       , fnd_product_groups fpg
   WHERE hou.organization_id = hoi1.organization_id
     AND hou.organization_id = hoi2.organization_id
     AND hou.organization_id = mp.organization_id
     AND hou2.organization_id =
           DECODE (fpg.multi_org_flag
                 , 'Y', DECODE (hoi2.org_information_context
                              , 'Accounting Information', TO_NUMBER
                                                          (hoi2.org_information3)
                              , TO_NUMBER (NULL)
                               )
                 , TO_NUMBER (NULL)
                  )
     AND hou3.organization_id =
           DECODE (hoi2.org_information_context
                 , 'Accounting Information', TO_NUMBER (hoi2.org_information2)
                 , NULL
                  )
     AND hoi1.org_information1 = 'INV'
     AND hoi1.org_information2 = 'Y'
     AND (hoi1.org_information_context || '') = 'CLASS'
     AND (hoi2.org_information_context || '') = 'Accounting Information'
     AND hoi2.org_information1 = TO_CHAR (gsob.set_of_books_id)