Extract Full HR Location Info Through SQL

It’s time for a little bit of SQL again. In the past I needed a little dump of all the HR Locations which were defined in the system. This dump required all the information recorded with such a location.

So information whether the location is local or global, the name, full address details including the used address style, shipping details and the ship-to location and the inventory and edi location from the Other Details tab. Finally the extract had to show whether the extra information location flexfield was used. For this I added a small count to the script counting the use of the location in table HR_LOCATION_EXTRA_INFO.

Find below the full script I’ve written – of course, as always, change the script where needed to fit your needs :-).

SELECT DISTINCT 
    L.LOCATION_ID AS "MAIN - LOCATION ID"
    , L.CREATION_DATE AS "CREATION DATE"
    , CREATEUSER.USER_NAME AS "CREATED BY"
    , L.LAST_UPDATE_DATE AS "LAST UPDATE DATE"
    , UPDATEUSER.USER_NAME AS "LAST UPDATED BY"
    , DECODE (NVL (L.BUSINESS_GROUP_ID, 1), 1, 'Y','N') AS "MAIN - SCOPE - GLOBAL"
    , DECODE (NVL (L.BUSINESS_GROUP_ID, 1), 0, 'Y', 'N') AS "MAIN - SCOPE - LOCAL"
    , TL.LOCATION_CODE AS "MAIN - NAME"
    , TL.DESCRIPTION AS "MAIN - DESCRIPTION"
    , L.INACTIVE_DATE AS "MAIN - INACTIVE DATE"
    , L.LEGAL_ADDRESS_FLAG AS "MAIN - LEGAL ADDRESS"
    -- BEGIN ADDRESS (STYLE)
    , L.STYLE AS "AD - ADDRESS STYLE CODE"
    , FND.DESCRIPTIVE_FLEX_CONTEXT_NAME AS "AD - ADDRESS STYLE"
    , L.ADDRESS_LINE_1 AS "ADDRESS LINE 1"
    , L.ADDRESS_LINE_2 AS "ADDRESS LINE 2"
    , L.ADDRESS_LINE_3 AS "ADDRESS LINE 3"
    , L.TOWN_OR_CITY AS "TOWN OR CITY"
    , L.REGION_1 AS "REGION 1"
    , L.REGION_2 AS "REGION 2"
    , L.REGION_3 AS "REGION 3"
    , L.POSTAL_CODE AS "POSTAL CODE"
    , L.COUNTRY AS "COUNTRY"
    , L.TELEPHONE_NUMBER_1 AS "TELEPHONE NUMBER 1"
    , L.TELEPHONE_NUMBER_2 AS "TELEPHONE NUMBER 2"
    , L.TELEPHONE_NUMBER_3 AS "TELEPHONE NUMBER 3"
    , L.LOC_INFORMATION13 AS "LOC INFORMATION 13"
    , L.LOC_INFORMATION14 AS "LOC INFORMATION 14"
    , L.LOC_INFORMATION15 AS "LOC INFORMATION 15"
    , L.LOC_INFORMATION16 AS "LOC INFORMATION 16"
    , L.LOC_INFORMATION17 AS "LOC INFORMATION 17"
    , L.LOC_INFORMATION18 AS "LOC INFORMATION 18"
    , L.LOC_INFORMATION19 AS "LOC INFORMATION 19"
    , L.LOC_INFORMATION20 AS "LOC INFORMATION 20"
    , L.TIMEZONE_CODE AS "AD - TIME ZONE"
    -- END ADDRESS (STYLE)
    , PPF.FULL_NAME AS "SD - CONTACT"
    , L2TL.LOCATION_CODE AS "SD - SHIP-TO LOCATION"
    , NVL(L.SHIP_TO_SITE_FLAG, 'N') AS "SD - SHIP-TO SITE" 
    , NVL(L.BILL_TO_SITE_FLAG, 'N') AS "SD - BILL-TO SITE"
    , NVL(L.RECEIVING_SITE_FLAG, 'N') AS "SD - RECEIVING SITE"
    , NVL(L.OFFICE_SITE_FLAG, 'N') AS "SD - OFFICE SITE"
    , NVL(L.IN_ORGANIZATION_FLAG, 'N') AS "SD - INTERNAL SITE"
    , INVORG.NAME AS "OD - INVENTORY ORGANIZATION"
    , L.TAX_NAME AS "OD - TAX CODE"
    , L.ECE_TP_LOCATION_CODE AS "OD - EDI LOCATION"
    , DECODE(LOC_EXTRA_INFO.EXTRA_INFO_COUNT,NULL,'N','Y') AS "EXTRA INFO AVAILABLE"
FROM HR_LOCATIONS_ALL L,
          HR_LOCATIONS_ALL_TL TL,
          HR_LOCATIONS_ALL_TL L2TL,
          PER_ALL_PEOPLE_F PPF, 
          HR_ALL_ORGANIZATION_UNITS INVORG,
          APPS.FND_DESCR_FLEX_CONTEXTS_TL FND,
          FND_USER CREATEUSER,
          FND_USER UPDATEUSER,
          (
            SELECT 
                LEI.LOCATION_ID LOCATION_ID
                , COUNT(LEI.LOCATION_ID) AS EXTRA_INFO_COUNT
            FROM
                HR_LOCATION_INFO_TYPES LIT
                , FND_DESCR_FLEX_CONTEXTS_VL FLV
                , HR_LOCATION_EXTRA_INFO LEI 
            WHERE
                LEI.INFORMATION_TYPE  = LIT.INFORMATION_TYPE 
                AND LIT.INFORMATION_TYPE = FLV.DESCRIPTIVE_FLEX_CONTEXT_CODE
                AND FLV.DESCRIPTIVE_FLEXFIELD_NAME = 'EXTRA LOCATION INFO DDF'
                AND LIT.ACTIVE_INACTIVE_FLAG = 'Y'
                AND FLV.ENABLED_FLAG = 'Y'
            GROUP BY
                LEI.LOCATION_ID) LOC_EXTRA_INFO
      WHERE L2TL.LOCATION_ID(+) = L.SHIP_TO_LOCATION_ID
      AND L.STYLE = FND.DESCRIPTIVE_FLEX_CONTEXT_CODE(+)
      AND PPF.PERSON_ID(+) = L.DESIGNATED_RECEIVER_ID
      AND LOC_EXTRA_INFO.LOCATION_ID(+) = L.LOCATION_ID
      AND INVORG.ORGANIZATION_ID(+) = L.INVENTORY_ORGANIZATION_ID
      AND CREATEUSER.USER_ID(+) = L.CREATED_BY
      AND UPDATEUSER.USER_ID(+) = L.LAST_UPDATED_BY
      AND FND.APPLICATION_ID(+) = 800
      AND FND.DESCRIPTIVE_FLEXFIELD_NAME(+) = 'ADDRESS LOCATION'
      AND FND.LANGUAGE(+) = 'US'
      AND L.LOCATION_ID = TL.LOCATION_ID
      AND TL.LANGUAGE = 'US'
      AND NVL (L.BUSINESS_GROUP_ID,
               NVL (HR_GENERAL.GET_BUSINESS_GROUP_ID, -99)) =
             NVL (HR_GENERAL.GET_BUSINESS_GROUP_ID,
                  NVL (L.BUSINESS_GROUP_ID, -99)
                 )
      AND DECODE (L2TL.LOCATION_ID, NULL, '1', L2TL.LANGUAGE) =
                        DECODE (L2TL.LOCATION_ID,
                                NULL, '1',
                                'US'
                               );