APPS.PER_POS_STRUCT_ELEMENTS_PKG2 SQL Statements

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

Line: 19

  select '1'
  into   l_dummy
  from    per_pos_structure_elements a
  where   a.business_group_id + 0 = X_Business_Group_Id
  and     a.POS_structure_version_id = X_Pos_Structure_Version_Id;

Line: 42

     select '1'
     into l_dummy
     from    per_pos_structure_elements ose
     where   ose.business_group_id + 0 = X_Business_Group_Id
     and     ose.POS_structure_version_id = X_Pos_Structure_Version_Id
     and     exists
            (select null
             from per_pos_structure_elements a
             where a.POS_structure_version_id = X_Pos_Structure_Version_Id
             and a.parent_position_id    = X_Position_Id)
     and     not exists
            (select null
            from per_pos_structure_elements b
            where b.POS_structure_version_id = X_Pos_Structure_Version_Id
            and b.subordinate_position_id     = X_Position_Id);

Line: 71

PROCEDURE parent_insert_update_checks (X_Business_Group_Id        NUMBER,
                                       X_Pos_Structure_Version_Id NUMBER,
                                       X_Position_Id              NUMBER,
                                       X_Parent_Position_Id       NUMBER,
                                       X_Subordinate_Position_Id  NUMBER) is
          l_root boolean;

Line: 91

        select '1'
        into l_dummy
        from   per_pos_structure_elements ose
        where  (ose.subordinate_position_id = X_Parent_Position_Id
        or      ose.parent_position_id = X_Parent_Position_Id)
        and    ose.POS_structure_version_id = X_Pos_Structure_Version_Id
        and    ose.business_group_id + 0 = X_Business_Group_Id;

Line: 115

        select '1'
        into l_dummy
        from   per_pos_structure_elements ose
        where  (ose.subordinate_position_id = X_Parent_Position_Id
        or      ose.parent_position_id = X_Parent_Position_Id)
        and    ose.POS_structure_version_id = X_Pos_Structure_Version_Id
        and    ose.business_group_id + 0 = X_Business_Group_Id;

Line: 139

          select '1'
          into l_dummy
          from   per_pos_structure_elements ose
          where
                 ose.POS_structure_version_id = X_Pos_Structure_Version_Id
          and    ose.business_group_id + 0 = X_Business_Group_Id
          and    X_Parent_Position_Id in
          (
           select ose.subordinate_position_id
           from per_pos_structure_elements ose
           where ose.POS_structure_version_id = X_Pos_Structure_Version_Id
             and ose.business_group_id + 0        = X_Business_Group_Id
           connect by
           prior ose.subordinate_position_id   = ose.parent_position_id
             and ose.POS_structure_version_id  = X_Pos_Structure_Version_Id
             and ose.business_group_id + 0         = X_Business_Group_Id
           start with
             ose.parent_position_id            = X_Subordinate_Position_Id
             and ose.POS_structure_version_id  = X_Pos_Structure_Version_Id
             and ose.business_group_id + 0         = X_Business_Group_Id
          );

Line: 183

          select '1'
          from per_pos_structure_elements tab
          where (( X_Rowid is not null
          and tab.rowid <> X_Rowid)
          or  X_Rowid is null)
          and X_Pos_Structure_Version_Id = tab.pos_structure_version_id
          and (X_Subordinate_Position_Id = tab.subordinate_position_id)
          and  X_Business_Group_Id = tab.business_group_id + 0;

Line: 212

     select position_structure_id
     into l_Position_Structure_Id
     from per_pos_structure_versions
     where pos_structure_version_id = X_Pos_Structure_Version_Id;

Line: 221

     SELECT '1'
     into l_dummy
     FROM   PER_SECURITY_PROFILES PSP
     WHERE  PSP.business_group_id + 0     = X_Business_Group_Id
     AND    (PSP.POSITION_ID           = X_Subordinate_Position_Id
     OR      PSP.POSITION_ID = X_Parent_Position_Id)
     AND     PSP.POSITION_STRUCTURE_ID = l_Position_Structure_Id;

Line: 239

        SELECT '1'
        into l_dummy
        FROM PER_SECURITY_PROFILES PSP
        WHERE PSP.business_group_id + 0 = X_Business_Group_Id
        AND PSP.POSITION_ID = X_Position_Id
        AND PSP.POSITION_STRUCTURE_ID = l_position_structure_id;

Line: 268

          select '1'
          into l_dummy
          from    per_pos_structure_elements ose
          where   ose.business_group_id + 0 = X_Business_Group_Id
          and     ose.POS_structure_version_id = X_Pos_Structure_Version_Id
          and     not exists
          (select null
           from per_pos_structure_elements b
           where b.POS_structure_version_id = X_Pos_Structure_Version_Id
           and b.subordinate_position_id     = X_Parent_Position_Id)
          and     not exists
          (select null
           from per_pos_structure_elements c
           where c.pos_structure_version_id = X_Pos_Structure_Version_Id
           and   c.parent_position_id    = X_Parent_Position_Id
           and   c.subordinate_position_id <> X_Subordinate_Position_Id);

Line: 292

            SELECT '1'
            into l_dummy
            FROM   PER_POS_STRUCTURE_ELEMENTS OSE
            WHERE  OSE.POS_STRUCTURE_VERSION_ID = X_Pos_Structure_Version_Id
            AND    OSE.PARENT_POSITION_ID = X_Subordinate_Position_Id;

Line: 311

PROCEDURE Insert_Row(X_Rowid                        IN OUT NOCOPY VARCHAR2,
                     X_Pos_Structure_Element_Id            IN OUT NOCOPY NUMBER,
                     X_Business_Group_Id                   NUMBER,
                     X_Pos_Structure_Version_Id            NUMBER,
                     X_Subordinate_Position_Id             NUMBER,
                     X_Parent_Position_Id                  NUMBER,
                     X_Position_Id                         NUMBER,
                     X_Security_Profile_Id                 NUMBER,
                     X_View_All_Positions                  VARCHAR2,
                     X_End_of_time                         DATE,
                     X_Session_Date                        DATE,
                     X_hr_ins                              VARCHAR2
 ) IS
   CURSOR C IS SELECT rowid FROM PER_POS_STRUCTURE_ELEMENTS

             WHERE pos_structure_element_id = X_Pos_Structure_Element_Id;

Line: 332

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

Line: 346

  Parent_insert_update_checks(X_Business_Group_Id,
                              X_Pos_Structure_Version_Id,
                              X_Position_Id,
                              X_Parent_Position_Id,
                              X_Subordinate_Position_Id);

Line: 352

  INSERT INTO PER_POS_STRUCTURE_ELEMENTS(
          pos_structure_element_id,
          business_group_id,
          pos_structure_version_id,
          subordinate_position_id,
          parent_position_id
         ) VALUES (
          X_Pos_Structure_Element_Id,
          X_Business_Group_Id,
          X_Pos_Structure_Version_Id,
          X_Subordinate_Position_Id,
          X_Parent_Position_Id
  );

Line: 371

    hr_utility.set_message_token('PROCEDURE','Insert_row');

Line: 377

END Insert_Row;

Line: 387

      SELECT *
      FROM   PER_POS_STRUCTURE_ELEMENTS
      WHERE  rowid = X_Rowid
      FOR UPDATE of Pos_Structure_Element_Id NOWAIT;

Line: 424

PROCEDURE Update_Row(X_Rowid                               VARCHAR2,
                     X_Pos_Structure_Element_Id            NUMBER,
                     X_Business_Group_Id                   NUMBER,
                     X_Pos_Structure_Version_Id            NUMBER,
                     X_Subordinate_Position_Id             NUMBER,
                     X_Parent_Position_Id                  NUMBER,
                     X_Position_Id                         NUMBER
) IS
BEGIN

 Parent_insert_update_checks(X_Business_Group_Id,
                             X_Pos_Structure_Version_Id,
                             X_Position_Id,
                             X_Parent_Position_Id,
                             X_Subordinate_Position_Id);

Line: 441

  UPDATE PER_POS_STRUCTURE_ELEMENTS
  SET

    pos_structure_element_id                  =    X_Pos_Structure_Element_Id,
    business_group_id                         =    X_Business_Group_Id,
    pos_structure_version_id                  =    X_Pos_Structure_Version_Id,
    subordinate_position_id                   =    X_Subordinate_Position_Id,
    parent_position_id                        =    X_Parent_Position_Id
  WHERE rowid = X_rowid;

Line: 453

    hr_utility.set_message_token('PROCEDURE','update_row');

Line: 458

END Update_Row;

Line: 460

PROCEDURE Delete_Row(X_Rowid VARCHAR2,
                     X_Business_Group_Id        NUMBER,
                     X_Pos_Structure_Version_Id NUMBER,
                     X_Parent_Position_Id       NUMBER,
                     X_Subordinate_Position_Id  NUMBER,
                     X_hr_ins VARCHAR2,
                     X_Position_Id              NUMBER) IS
BEGIN

check_if_child_is_parent(X_Business_Group_Id,
                         X_Pos_Structure_Version_Id,
                         X_Parent_Position_Id,
                         X_Subordinate_Position_Id);

Line: 482

  DELETE FROM PER_POS_STRUCTURE_ELEMENTS
  WHERE  rowid = X_Rowid;

Line: 487

    hr_utility.set_message_token('PROCEDURE','delete_row');

Line: 491

END Delete_Row;