APPS.PER_ORG_STRUCTURE_ELEMENTS_PKG SQL Statements

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

Line: 20

   select  nvl( count(ose.org_structure_element_id), 0)
   into l_count
   from    per_org_structure_elements      ose
   where   ((nvl(p_view_all_orgs,'Y') <> 'Y'
      and EXISTS
         (select '1'
         from    hr_organization_units hru
         where   hru.organization_id = ose.organization_id_child
         ))
      or nvl(p_view_all_orgs,'Y') = 'Y')
   connect by
      prior ose.organization_id_child = ose.organization_id_parent
      and     ose.org_structure_version_id    = p_org_structure_version_id
   start with
      ose.organization_id_parent      = p_org_id_parent
      and     ose.org_structure_version_id    = p_org_structure_version_id;

Line: 54

PROCEDURE insert_rows(p_security_profile_id NUMBER
                     ,p_organization_id  NUMBER
                     ) is
--
begin
    hr_security.add_organization(p_organization_id,
                                 p_security_profile_id);

Line: 69

end insert_rows;

Line: 84

   select  null
   into l_temp
   from    sys.dual
   where exists (select null
                  from sys.dual
                  where   p_organization_id in
                     (select a.organization_id_child
                     from   per_org_structure_elements a
                     where  (  a.business_group_id + 0 = p_business_group_id
                            or (   a.business_group_id is null
                               and p_business_group_id is null))
                     and    a.org_structure_version_id in
                        ( select x.org_structure_version_id
                        from   per_org_structure_versions x
                        where x.organization_structure_id =
                           p_sec_org_structure_id)
                  connect by prior
                     a.organization_id_child = a.organization_id_parent
                     and    a.org_structure_version_id in
                        ( select x.org_structure_version_id
                        from   per_org_structure_versions x
                        where x.organization_structure_id =
                           p_sec_org_structure_id)
                     and    (  a.business_group_id + 0 = p_business_group_id
                            or (   a.business_group_id is null
                               and p_business_group_id is null))
                  start with
                     a.organization_id_parent = p_sec_org_id
                     and    a.org_structure_version_id in
                           ( select x.org_structure_version_id
                           from   per_org_structure_versions x
                           where x.organization_structure_id =
                              p_sec_org_structure_id)
                           and    (  a.business_group_id + 0 = p_business_group_id
                                  or (   a.business_group_id is null
                                     and p_business_group_id is null)))
                  );

Line: 138

      select  x.view_all_organizations_flag
      ,       x.organization_structure_id
      ,       x.organization_id
      ,       x.include_top_organization_flag
      into    l_sec_view_all_orgs_flag
      ,       l_sec_org_structure_id
      ,       l_sec_org_id
      ,       l_sec_include_top_org_flag
      from    per_security_profiles x
      where   (  x.business_group_id + 0     = p_business_group_id
              or x.business_group_id is null)
      and     x.security_profile_id   = p_security_profile_id;

Line: 163

      insert_rows(p_security_profile_id
         ,p_organization_id
         );

Line: 171

            insert_rows(p_security_profile_id
               ,p_organization_id
               );

Line: 176

      insert_rows(p_security_profile_id
         ,p_organization_id
         );

Line: 183

      insert_rows(p_security_profile_id
         ,p_organization_id
         );

Line: 189

PROCEDURE delete_validation(p_org_structure_version_id NUMBER
                           ,p_org_structure_element_id NUMBER
                           ,p_org_id_child NUMBER
                           ,p_org_id_parent NUMBER
                           ,p_hr_installed VARCHAR2
                           ,p_pa_installed VARCHAR2) is
--
l_temp VARCHAR2(1);

Line: 204

      select  null
      into l_temp
      from sys.dual
      where exists (select null
                     from per_org_structure_elements      ose
                     where   ose.org_structure_version_id    =
                                          p_org_structure_version_id
                     and     ose.organization_id_parent      =
                                          p_org_id_child);

Line: 235

         select null
         into l_temp
         from sys.dual
         where exists( select null
                     from per_security_profiles psp
                     where   psp.include_top_organization_flag = 'Y'
                     and     psp.organization_structure_id     =
                           (select osv.organization_structure_id
                           from    per_org_structure_versions osv
                           where   osv.org_structure_version_id =
                                 p_org_structure_version_id)
                     and   ((psp.organization_id = p_org_id_child)
                        or(psp.organization_id = p_org_id_parent
                           and not exists (
                              select  null
                              from    per_org_structure_elements ose
                              where   ose.org_structure_version_id  =
                                       p_org_structure_version_id
                              and     ose.organization_id_child     =
                                       p_org_id_parent
                              )
                           and     not exists (
                              select  null
                              from    per_org_structure_elements ose
                              where   ose.org_structure_version_id  =
                                       p_org_structure_version_id
                              and     ose.org_structure_element_id  <>
                                       p_org_structure_element_id
                              and     ose.organization_id_parent    =
                                       p_org_id_parent
                              )
                           )
                        ) );

Line: 289

PROCEDURE perwsdor_delete_check(p_org_structure_version_id NUMBER
                               ,p_org_structure_element_id NUMBER
                               ,p_org_id_child NUMBER
                               ,p_org_id_parent NUMBER
                               ,p_business_group_id NUMBER
                               ,p_hr_installed VARCHAR2
                               ,p_pa_installed VARCHAR2) is
cursor c1 is select 'Y'
 from   per_org_structure_elements pos
 where  pos.org_structure_version_id = p_org_structure_version_id
 and   (not exists
           (select null
            from   per_org_structure_elements pos2
            where  pos2.org_structure_version_id
                   = p_org_structure_version_id
            and    pos2.organization_id_child =
                    p_org_id_parent
           )
and     1 =(select count(pos3.ORG_STRUCTURE_VERSION_ID)
            from   per_org_structure_elements pos3
            where  pos3.org_structure_version_id
                   = p_org_structure_version_id
            and    pos3.organization_id_parent =
                 p_org_id_parent
           )
OR     (not exists
        (select null
         from   per_org_structure_elements pos3
         where  pos3.org_structure_version_id =
                p_org_structure_version_id
         and    pos3.organization_id_parent = p_org_id_child)
      )
     );

Line: 324

select 'Y'
from   per_security_profiles psp
,      per_org_structure_versions posv
where  (  psp.business_group_id         =p_business_group_id
       or (   psp.business_group_id is null
          and p_business_group_id is null))
and    psp.organization_id           = p_org_id_child
and    (  psp.business_group_id         = posv.business_group_id
       or (   psp.business_group_id is null
          and posv.business_group_id is null))
and    psp.organization_structure_id = posv.organization_structure_id
and    posv.org_structure_version_id = p_org_structure_version_id;

Line: 343

   hr_utility.set_message('801','HR_6740_ORG_HIER_CANT_DELETE');

Line: 373

   select null
   into l_temp
   from sys.dual
   where exists( select  null
               from    per_org_structure_elements      ose
               where   ose.org_structure_version_id    =
                        p_org_structure_version_id
               and     ose.organization_id_child       =
                        p_org_structure_element_id);

Line: 404

   select null
   into l_temp
   from sys.dual
   where exists(select  null
               from    hr_all_organization_units org
               where   org.organization_id     = p_org_id_parent
               and     p_date_from between
                     org.date_from and nvl(org.date_to, p_end_of_time)
               );

Line: 429

select str.position_control_structure_flg
from per_organization_structures str,
     per_org_structure_versions osv
where osv.org_structure_version_id = p_org_structure_version_id
and osv.organization_structure_id = str.organization_structure_id;

Line: 462

FUNCTION post_delete_check(p_org_structure_version_id NUMBER
                           ,p_organization_id NUMBER) return BOOLEAN is
--
l_temp VARCHAR2(1);

Line: 472

   select null
   into l_temp
   from sys.dual
   where exists( select null
               from    per_org_structure_elements      ose
               where   ose.org_structure_version_id    =
                     p_org_structure_version_id
               and     (ose.organization_id_parent     = p_organization_id
                  or       ose.organization_id_child      = p_organization_id)
               );

Line: 490

end post_delete_check;

Line: 492

PROCEDURE Insert_Row(p_Rowid                        IN OUT NOCOPY VARCHAR2,
                     p_Org_Structure_Element_Id            IN OUT NOCOPY NUMBER,
                     p_Business_Group_Id                   NUMBER,
                     p_Organization_Id_Parent              NUMBER,
                     p_Org_Structure_Version_Id            NUMBER,
                     p_Organization_Id_Child               NUMBER,
                     p_date_from                           DATE,
                     p_security_profile_id                 NUMBER,
                     p_view_all_orgs                       VARCHAR2,
                     p_end_of_time                         DATE,
                     p_pos_control_enabled_flag            VARCHAR2
 ) IS
--
l_warning_raised VARCHAR2(1);

Line: 507

  Insert_Row(p_Rowid          => p_Rowid,
  p_Org_Structure_Element_Id  => p_Org_Structure_Element_Id,
  p_Business_Group_Id         => p_Business_Group_Id,
  p_Organization_Id_Parent    => p_Organization_Id_Parent,
  p_Org_Structure_Version_Id  => p_Org_Structure_Version_Id,
  p_Organization_Id_Child     => p_Organization_Id_Child,
  p_date_from                 => p_date_from,
  p_security_profile_id       => p_security_profile_id,
  p_view_all_orgs             => p_view_all_orgs,
  p_end_of_time               => p_end_of_time,
  p_pos_control_enabled_flag  => p_pos_control_enabled_flag,
  p_warning_raised            => l_warning_raised);

Line: 521

PROCEDURE Insert_Row(p_Rowid                         IN OUT NOCOPY VARCHAR2,
                     p_Org_Structure_Element_Id             IN OUT NOCOPY NUMBER,
                     p_Business_Group_Id                    NUMBER,
                     p_Organization_Id_Parent               NUMBER,
                     p_Org_Structure_Version_Id             NUMBER,
                     p_Organization_Id_Child                NUMBER,
                     p_date_from                            DATE,
                     p_security_profile_id                  NUMBER,
                     p_view_all_orgs                        VARCHAR2,
                     p_end_of_time                          DATE,
                     p_pos_control_enabled_flag             VARCHAR2,
                     p_warning_raised                IN OUT NOCOPY VARCHAR2
                     ) IS
--
   CURSOR C IS SELECT rowid FROM PER_ORG_STRUCTURE_ELEMENTS
       WHERE org_structure_element_id = p_Org_Structure_Element_Id;

Line: 537

   CURSOR C2 IS SELECT per_org_structure_elements_s.nextval FROM sys.dual;

Line: 539

cursor get_details is select rowid,org_structure_element_id,
business_group_id,organization_id_parent,org_structure_version_id,
organization_id_child, position_control_enabled_flag
from per_org_structure_elements
where organization_id_child = p_Organization_Id_Child
and   org_structure_version_id = p_org_structure_version_id;

Line: 582

PER_ORG_STRUCTURE_ELEMENTS_PKG.Update_Row(p_Rowid=> Recinfo.ROWID,
                p_Org_Structure_Element_Id  =>Recinfo.Org_Structure_Element_Id,
                p_Business_Group_Id  => Recinfo.Business_Group_Id,
                p_Organization_Id_Parent =>p_Organization_Id_Parent,
                p_Org_Structure_Version_Id =>Recinfo.Org_Structure_Version_Id,
                p_Organization_Id_Child    =>Recinfo.Organization_Id_Child,
                p_pos_control_enabled_flag => Recinfo.position_control_enabled_flag);

Line: 606

   INSERT INTO PER_ORG_STRUCTURE_ELEMENTS(
      org_structure_element_id,
      business_group_id,
      organization_id_parent,
      org_structure_version_id,
      organization_id_child,
      position_control_enabled_flag
   ) VALUES (
      p_Org_Structure_Element_Id,
      p_Business_Group_Id,
      p_Organization_Id_Parent,
      p_Org_Structure_Version_Id,
      p_Organization_Id_Child,
      p_pos_control_enabled_flag
   );

Line: 640

END Insert_Row;

Line: 652

      SELECT *
      FROM   PER_ORG_STRUCTURE_ELEMENTS
      WHERE  rowid = p_Rowid
      FOR UPDATE of Org_Structure_Element_Id NOWAIT;

Line: 693

PROCEDURE Update_Row(p_Rowid                               VARCHAR2,
                     p_Org_Structure_Element_Id            NUMBER,
                     p_Business_Group_Id                   NUMBER,
                     p_Organization_Id_Parent              NUMBER,
                     p_Org_Structure_Version_Id            NUMBER,
                     p_Organization_Id_Child               NUMBER,
                     p_pos_control_enabled_flag            VARCHAR2
) IS
BEGIN
                Update_Row(p_Rowid=>p_Rowid,
                     p_Org_Structure_Element_Id=>p_Org_Structure_Element_Id,
                     p_Business_Group_Id=>p_Business_Group_Id,
                     p_Organization_Id_Parent=>p_Organization_Id_Parent,
                     p_Org_Structure_Version_Id=>p_Org_Structure_Version_Id,
                     p_Organization_Id_Child  =>p_Organization_Id_Child,
                     p_pos_control_enabled_flag=>p_pos_control_enabled_flag,
                     p_pa_installed=>'N');

Line: 710

END Update_Row;

Line: 711

PROCEDURE Update_Row(p_Rowid                               VARCHAR2,
                     p_Org_Structure_Element_Id            NUMBER,
                     p_Business_Group_Id                   NUMBER,
                     p_Organization_Id_Parent              NUMBER,
                     p_Org_Structure_Version_Id            NUMBER,
                     p_Organization_Id_Child               NUMBER,
                     p_pos_control_enabled_flag            VARCHAR2,
                     p_pa_installed                       VARCHAR2
) IS
--bug no 5912009 ends here
BEGIN
   check_position_flag (
                    p_org_structure_version_id => p_org_structure_version_id
                   ,p_pos_control_enabled_flag => p_pos_control_enabled_flag);

Line: 732

   UPDATE PER_ORG_STRUCTURE_ELEMENTS
   SET
      org_structure_element_id                  =    p_Org_Structure_Element_Id,
      business_group_id                         =    p_Business_Group_Id,
      organization_id_parent                    =    p_Organization_Id_Parent,
      org_structure_version_id                  =    p_Org_Structure_Version_Id,
      organization_id_child                     =    p_Organization_Id_Child,
      position_control_enabled_flag             =    p_pos_control_enabled_flag
   WHERE rowid = p_rowid;

Line: 745

END Update_Row;

Line: 747

PROCEDURE Delete_Row(p_Rowid VARCHAR2
                     ,p_org_structure_version_id NUMBER
                     ,p_org_structure_element_id NUMBER
                     ,p_organization_id_child NUMBER
                     ,p_organization_id_parent NUMBER
                     ,p_hr_installed VARCHAR2
                     ,p_exists_in_hierarchy IN OUT NOCOPY VARCHAR2
                     ,p_pa_installed VARCHAR2) IS
--
BEGIN
   --
   -- Do pre-delete validation
   --
   per_org_structure_elements_pkg.delete_validation(
                     p_org_structure_version_id =>p_org_structure_version_id
                     ,p_org_structure_element_id => p_org_structure_element_id
                     ,p_org_id_child => p_organization_id_child
                     ,p_org_id_parent => p_organization_id_parent
                     ,p_hr_installed => p_hr_installed
                     ,p_pa_installed => p_pa_installed);

Line: 769

   DELETE FROM PER_ORG_STRUCTURE_ELEMENTS
   WHERE  rowid = p_Rowid;

Line: 778

   if per_org_structure_elements_pkg.post_delete_check(
                        p_org_structure_version_id => p_org_structure_version_id
                        ,p_organization_id => p_organization_id_parent) then
      p_exists_in_hierarchy :='Y';

Line: 785

END Delete_Row;