eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.HRI_STRUCT_SUMMARY SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 12


g_default_last_update        DATE := to_date('01-01-2000','DD-MM-YYYY');

Line: 25


  INSERT INTO HRI.HRI_DEBUG
  (Text1
  ,Text2
  ,time_date)
  values
  (Text1
  ,Text2
  ,sysdate);

Line: 89


  SELECT ghn.entity_id    top_entity_id
  FROM  per_gen_hierarchy_nodes   ghn
  WHERE ghn.hierarchy_version_id = cp_gen_hierarchy_version_id
  AND   ghn.parent_hierarchy_node_id is null;

Line: 203


  SELECT ghr.business_group_id
       , ghr.type
       , ghr.hierarchy_id
       , ghv.hierarchy_version_id
       , ghv.date_from
       , ghv.date_to
       , ghv.version_number
       , ghr.name
    FROM per_gen_hierarchy ghr
       , per_gen_hierarchy_versions ghv
   WHERE ghr.hierarchy_id = ghv.hierarchy_id
     AND ghr.business_group_id = ghv.business_group_id
     AND ghr.type = 'FEDREP'; -- bug 2492438

Line: 226


  SELECT  ghn.business_group_id        business_group_id
        , ghn.hierarchy_node_id        hierarchy_node_id
        , ghn.entity_id                entity_id
        , LEVEL                        entity_level
        , ghn.node_type                node_type
   FROM per_gen_hierarchy_nodes ghn
   WHERE ghn.hierarchy_version_id = cp_gen_hierarchy_version_id
   START WITH ghn.entity_id = cp_top_entity_id
   CONNECT BY  ghn.parent_hierarchy_node_id   =  PRIOR ghn.hierarchy_node_id
   -- Note: this looks excessive but making sure both
   -- the prior record and the current record
   -- are both using the specified org struct version
   AND PRIOR ghn.hierarchy_version_id = cp_gen_hierarchy_version_id
   AND ghn.hierarchy_version_id = cp_gen_hierarchy_version_id
  ;

Line: 248


  SELECT  ghn.business_group_id        business_group_id
        , ghn.hierarchy_node_id        hierarchy_node_id
        , ghn.entity_id                entity_id
        , LEVEL -1                     entity_level
        , ghn.node_type                node_type
   FROM per_gen_hierarchy_nodes ghn
   WHERE ghn.hierarchy_version_id = cp_gen_hierarchy_version_id
   START WITH ghn.entity_id = cp_top_entity_id
   CONNECT BY  ghn.parent_hierarchy_node_id   =  PRIOR ghn.hierarchy_node_id
   -- Note: this looks excessive but making sure both
   -- the prior record and the current record
   -- are both using the specified org struct version
   AND PRIOR ghn.hierarchy_version_id = cp_gen_hierarchy_version_id
   AND ghn.hierarchy_version_id = cp_gen_hierarchy_version_id
 ;

Line: 326


      BEGIN -- Handle Gen Children tree walk exceptions and Insert exceptions
            -- 3. Loop through and insert a row for each Parnet Child Entity
            --    combination
      l_this_parent_entity := l_gen_parent_rec.entity_id;

Line: 341


        INSERT INTO
          HRI_GEN_HRCHY_SUMMARY
           (hierarchy_id
           ,hierarchy_version_id
           ,business_group_id
           ,hierarchy_node_id
           ,entity_id
           ,entity_level
           ,node_type
           ,sub_entity_bg_id
           ,sub_hierarchy_node_id
           ,sub_entity_id
           ,sub_entity_level
           ,sub_node_type
           /* -- This done by trigger HRI_GEN_HRCHY_SUMMARY_WHO
           ,created_by
           ,creation_date
           ,last_updated_by
           ,last_update_login
           ,last_update_date/**/
           )
          VALUES
           (l_gen_struct_ver_rec.hierarchy_id
           ,l_gen_struct_ver_rec.hierarchy_version_id
           ,l_gen_parent_rec.business_group_id
           ,l_gen_parent_rec.hierarchy_node_id
           ,l_gen_parent_rec.entity_id
           ,l_gen_parent_rec.entity_level
           ,l_gen_parent_rec.node_type
           ,l_entity_child_rec.business_group_id
           ,l_entity_child_rec.hierarchy_node_id
           ,l_entity_child_rec.entity_id
           ,l_gen_parent_rec.entity_level + l_entity_child_rec.entity_level
           ,l_entity_child_rec.node_type
           /* -- This done by trigger HRI_GEN_HRCHY_SUMMARY_WHO
           ,-1 --created_by
           ,sysdate
           ,-1 --last_updated_by
           ,-1 --last_update_login
           ,sysdate --last_update_date/**/
           );

Line: 388


          output( 'Child or Insert Exception ');

Line: 526


  TYPE l_sup_updates_tabtype IS TABLE OF DATE INDEX BY BINARY_INTEGER;

Line: 527


  l_sup_updates_tab   l_sup_updates_tabtype;

Line: 544


  SELECT asg.person_id
       , asg.business_group_id
       , 0 supervisor_hier_level
       , NVL(asg.last_update_date, g_default_last_update)
                                     last_ptntl_change_date
    FROM per_all_assignments_f asg
   WHERE  DECODE(cp_primary_ass_only, 'Y', asg.primary_flag, 1)
       =  DECODE(cp_primary_ass_only, 'Y', 'Y', 1) -- primary assignments only
     AND asg.supervisor_id IS NULL -- not supervised themselves.
     AND asg.assignment_type = 'E'
     AND cp_date BETWEEN asg.effective_start_date AND asg.effective_end_date
     AND asg.person_id IN
          (SELECT DISTINCT asg.supervisor_id
             FROM per_all_assignments_f asg
            WHERE DECODE(cp_primary_ass_only, 'Y', asg.primary_flag, 1)
               =  DECODE(cp_primary_ass_only, 'Y', 'Y', 1) -- primary assignments only
              AND asg.supervisor_id IS NOT NULL
              AND asg.assignment_type = 'E'
              AND cp_date BETWEEN
                     asg.effective_start_date AND asg.effective_end_date
           ) --
     AND asg.business_group_id
          = NVL(cp_business_group_id, asg.business_group_id);

Line: 579


  SELECT hier.business_group_id
       , hier.person_id
       , hier.assignment_id
       , hier.primary_flag
       , LEVEL-1 supervisor_level
       , hier.assignment_id supv_asg_id
       , NVL(hier.last_update_date, g_default_last_update)
                                      last_ptntl_change_date
    FROM per_all_assignments_f  hier
   WHERE cp_date  BETWEEN
           hier.effective_start_date AND  hier.effective_end_date
     AND hier.assignment_type = 'E'
     AND  DECODE(cp_primary_ass_only, 'Y', hier.primary_flag, 1)
       =  DECODE(cp_primary_ass_only,'Y', 'Y', 1) -- primary assignments only
   START WITH hier.person_id     =  cp_supervisor_id
   CONNECT BY hier.supervisor_id = PRIOR hier.person_id
          AND hier.assignment_type = 'E'
          AND cp_date BETWEEN
                PRIOR hier.effective_start_date
                AND PRIOR hier.effective_end_date
          AND  DECODE(cp_primary_ass_only, 'Y', hier.primary_flag, 1)
            =  DECODE(cp_primary_ass_only,'Y', 'Y', 1); -- primary assignments only

Line: 609


  SELECT hier.business_group_id
       , hier.person_id
       , hier.assignment_id
       , hier.primary_flag
       , LEVEL-1 subordinate_level
       , NVL(hier.last_update_date, g_default_last_update)
                              last_ptntl_change_date
    FROM per_all_assignments_f  hier
   WHERE cp_date BETWEEN
           hier.effective_start_date AND  hier.effective_end_date
     AND hier.assignment_type = 'E'
     AND  DECODE(cp_primary_ass_only, 'Y', hier.primary_flag, 1)
       =  DECODE(cp_primary_ass_only,'Y', 'Y', 1) -- primary assignments only
   START WITH hier.person_id    =  cp_supervisor_id
   CONNECT BY hier.supervisor_id = PRIOR hier.person_id
       AND hier.assignment_type = 'E'
       AND cp_date BETWEEN
           PRIOR hier.effective_start_date AND PRIOR hier.effective_end_date
       AND  DECODE(cp_primary_ass_only, 'Y', hier.primary_flag, 1)
         =  DECODE(cp_primary_ass_only,'Y', 'Y', 1); -- primary assignments only

Line: 647


    l_sup_updates_tab(cur_people_rec.person_id) := cur_people_rec.last_ptntl_change_date;

Line: 661


        IF (l_sup_updates_tab(cur_supervisors_rec.person_id) >
                            cur_reports_rec.last_ptntl_change_date) THEN
          l_sup_updates_tab(cur_reports_rec.person_id) :=
                    l_sup_updates_tab(cur_supervisors_rec.person_id);

Line: 666


          l_sup_updates_tab(cur_reports_rec.person_id) :=
                    cur_reports_rec.last_ptntl_change_date;

Line: 678


            INSERT INTO hri_supv_hrchy_summary(
                  supv_business_group_id
                , supv_person_id
                , supv_assignment_id
                , supv_level
                , supv_last_ptntl_change
                , sub_business_group_id
                , sub_person_id
                , sub_assignment_id
                , sub_primary_asg_flag
                , sub_level
                , sub_last_ptntl_change
              /* -- This done by trigger HRI_SUPV_HRCHY_SUMMARY_WHO
                , creation_date
                , created_by
                , last_update_date
                , last_updated_by
                , last_update_login
              /**/
            )
            VALUES(
                  cur_supervisors_rec.business_group_id
                , cur_supervisors_rec.person_id
                , cur_supervisors_rec.supv_asg_id
                , cur_supervisors_rec.supervisor_level
                , l_sup_updates_tab(cur_supervisors_rec.person_id)
                , cur_reports_rec.business_group_id
                , cur_reports_rec.person_id
                , cur_reports_rec.assignment_id
                , cur_reports_rec.primary_flag
                , cur_reports_rec.subordinate_level + cur_supervisors_rec.supervisor_level
                , l_sup_updates_tab(cur_reports_rec.person_id)
              /* -- This done by trigger HRI_SUPV_HRCHY_SUMMARY_WHO
                , SYSDATE
                , -1
                , SYSDATE
                , -1
                , -1
              /**/
            );