May 28, 2014

Query to find the Organization structure of a business

bishnupc / /
Run this script giving inventory org as the parameter
SELECT mp.organization_id,
       MP.ORGANIZATION_CODE,
       hl.address_line_1,
       hl.address_line_2,
       hl.address_line_3,
       hl.town_or_city,
       hl.country,
       hl.postal_code,
       HAOU.NAME,
       HAOUBG.NAME BUSINESS_GROUP,
       GSOB.SET_OF_BOOKS_ID,
       GSOB.NAME SOB_NAME, /*gsob.short_name,*/
       GSOB.CURRENCY_CODE,
       GSOB.PERIOD_SET_NAME,
       (SELECT SEGMENT1 || '.' || SEGMENT2 || '.' || SEGMENT3 || '.' ||
               SEGMENT4 || '.' || SEGMENT5 || '.' || SEGMENT6 || '.' ||
               SEGMENT7 || '.' || SEGMENT8 || '.' || SEGMENT9
          FROM APPS.GL_CODE_COMBINATIONS
         WHERE CODE_COMBINATION_ID = GSOB.CHART_OF_ACCOUNTS_ID) CHART_OF_ACCOUNT,
       HLE.NAME LE_NAME,
       HLE.VAT_REGISTRATION_NUMBER,
       HOU.NAME OU_NAME

  FROM APPS.HR_ALL_ORGANIZATION_UNITS   HAOU,
       APPS.HR_ORGANIZATION_INFORMATION HRI,
       APPS.MTL_PARAMETERS              MP,
       APPS.GL_SETS_OF_BOOKS            GSOB,
       APPS.HR_LEGAL_ENTITIES           HLE,
       APPS.HR_OPERATING_UNITS          HOU,
       APPS.HR_ALL_ORGANIZATION_UNITS   HAOUBG,
       APPS.HR_ORGANIZATION_INFORMATION HRIBG,
       apps.hr_locations                hl

 WHERE HRI.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
   AND HAOU.ORGANIZATION_ID = MP.ORGANIZATION_ID
   AND MP.ORGANIZATION_CODE IN ('V1')
   AND HRI.ORG_INFORMATION_CONTEXT = 'Accounting Information'
   AND GSOB.SET_OF_BOOKS_ID = HRI.ORG_INFORMATION1
   AND HLE.ORGANIZATION_ID = HRI.ORG_INFORMATION2
   AND HOU.ORGANIZATION_ID = HRI.ORG_INFORMATION3
   AND HAOUBG.ORGANIZATION_ID = HLE.BUSINESS_GROUP_ID
   AND HRIBG.ORG_INFORMATION_CONTEXT = 'Business Group Information'
   AND HRIBG.ORGANIZATION_ID = HAOUBG.ORGANIZATION_ID
   and haou.location_id = hl.location_id

;
Copyright © . Oracle Apps (SCM) . All Rights Reserved
Oracle Apps (SCM) is an online knowledge sharing blog which index online free tutorials, blogs and other sources, to get easier and accessible manner. The blog has been created keeping only one intention of sharing knowledge and for learning purpose. All trademarks, trade names, service marks, copyrighted work, logos referenced herein belong to their respective owners/companies. If any of the posting is violating copyright and you want us to remove that content. Please contact Here