Query to Get Organization Hierarchy

SELECT *

  FROM (SELECT haou.name,

               (    SELECT LISTAGG (

                              ' Level: ' || LEVEL || ' ' || posev.d_parent_name,

                              CHR (10))

                           WITHIN GROUP (ORDER BY LEVEL)

                      FROM per_organization_structures pos,

                           per_org_structure_versions posv,

                           per_org_structure_elements_v posev

                     WHERE     pos.business_group_id = haou.business_group_id

                           AND UPPER (pos.name) = UPPER (:l_hierarchy_name)

                           AND posv.organization_structure_id =

                                  pos.organization_structure_id

                           AND posv.org_structure_version_id =

                                  posev.org_structure_version_id

                           AND TRUNC (SYSDATE) BETWEEN date_from

                                                   AND NVL (

                                                          date_to,

                                                          hr_general.end_of_time)

                START WITH posev.organization_id_child = haou.organization_id

                CONNECT BY posev.organization_id_child =

                              PRIOR posev.organization_id_parent)

                  hierarchy

          FROM hr_all_organization_units haou

         WHERE business_group_id = :l_business_group_id)

 WHERE hierarchy IS NOT NULL

API to Load Arabic Values in HR Lookups

DECLARE

   CURSOR get_lookup_details

   IS

      SELECT ltype.application_id,

             ltype.customization_level,

             ltype.creation_date,

             ltype.created_by,

             ltype.last_update_date,

             ltype.last_updated_by,

             ltype.last_update_login,

             tl.lookup_type,

             tl.security_group_id,

             tl.view_application_id,

             tl.description,

             tl.meaning

        FROM fnd_lookup_types_tl tl, fnd_lookup_types ltype

       WHERE     ltype.lookup_type = 'XX POSITIONS'

             AND ltype.lookup_type = tl.lookup_type

             AND language = 'US';

   CURSOR get_value

   IS

      SELECT DISTINCT

             hl.lookup_code, pos.position_name_arabic, position_name_english

        FROM xxhr_position_t pos, hr_lookups hl

       WHERE     hl.meaning = pos.position_name_english

             AND hl.lookup_type = 'XX POSITIONS'

--             AND lookup_code= '321'

             AND lkp_process_flag_ar = 'N';

   l_err_msg   VARCHAR2 (1000) := NULL;

   l_db_nls_language       VARCHAR2 (500) := NULL;

BEGIN

   FOR i IN get_lookup_details

   LOOP

      FOR j IN get_value

      LOOP

         -- Set NLS_LANG to Arabic to insert  Arabic Values

         fnd_global.set_nls (

            p_nls_language                => 'ARABIC',

            p_nls_date_format             => NULL,

            p_nls_date_language           => NULL,

            p_nls_numeric_characters      => NULL,

            p_nls_sort                    => NULL,

            p_nls_territory               => NULL,

            p_db_nls_language             => l_db_nls_language,

            p_db_nls_date_format          => l_db_nls_language,

            p_db_nls_date_language        => l_db_nls_language,

            p_db_nls_numeric_characters   => l_db_nls_language,

            p_db_nls_sort                 => l_db_nls_language,

            p_db_nls_territory            => l_db_nls_language,

            p_db_nls_charset              => l_db_nls_language);

         l_err_msg := NULL;

        

        dbms_output.put_line('l_db_nls_language: ' || l_db_nls_language);

         BEGIN

            fnd_lookup_values_pkg.translate_row (

               x_lookup_type           => i.lookup_type,

               x_security_group_id     => i.security_group_id,

               x_view_application_id   => i.view_application_id,

               x_owner                 => 49431, --> User ID

               x_meaning               => j.position_name_arabic,

               x_description           => j.position_name_arabic,

               x_lookup_code           => j.lookup_code);

            UPDATE xxhr_position_t --> Stagging Table to Log Status

               SET lkp_process_flag_ar = 'Y', lkp_err_msg = NULL

             WHERE UPPER (position_name_english) =

                      UPPER (j.position_name_english);

            COMMIT;

         EXCEPTION

            WHEN OTHERS

            THEN

               l_err_msg := SQLERRM;

               UPDATE xxhr_position_t

                  SET lkp_process_flag_ar = 'N', lkp_err_msg = l_err_msg

                WHERE UPPER (position_name_english) =

                         UPPER (j.position_name_english);

               COMMIT;

         END;

      END LOOP;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);

END;