APPS.PER_POS_STRUCTURE_VERSIONS_PKG SQL Statements

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

Line: 9

  select nvl(max(psv.version_number),0) + 1
   into   l_next_free_no
   from per_pos_structure_versions psv
   where psv.Position_Structure_Id = X_Position_Structure_Id;

Line: 25

   select null
   into l_dummy
   from sys.dual
   where exists (select 1
                  from    per_pos_structure_versions      psv
                  where   psv.position_structure_id  = X_Position_Structure_Id
                  and     psv.version_number         = X_Version_Number
                  and     (psv.rowid                <> X_Rowid
                  or      X_Rowid is null));

Line: 62

  select max(psv.date_to)
  into   l_max_end_date
  from   per_pos_structure_versions psv
  where  psv.date_from < X_Date_From
  --
  -- Bug 608815: add missing clause to restrict the hierarchy, or the code
  -- looks for the max date across all hierarchies. RMF 09-Jan-98.
  --
  and	psv.position_structure_id = X_Position_Structure_Id
  and   (psv.rowid <> X_Rowid
     or  X_Rowid is null);

Line: 76

        select min(psv.date_from)
		  into   l_min_start_date
        from   per_pos_structure_versions psv
        where  psv.position_structure_id = X_Position_Structure_Id
        and    psv.date_from > X_Date_To
        and   (psv.rowid <> X_Rowid
        or    psv.rowid is null);

Line: 112

      select null
      into l_dummy
      from sys.dual
      where exists
         (select 1
         from per_pos_structure_versions psv
         where psv.position_structure_id = X_Position_Structure_Id
         and   X_Date_From > psv.date_from
         and   psv.date_to is null);

Line: 128

         update per_pos_structure_versions psv
         set psv.date_to = (X_date_from - 1)
         where psv.position_structure_id = X_Position_Structure_Id
         and   psv.date_to is null
         and   (psv.rowid <> X_Rowid
         or    X_Rowid is null);

Line: 147

      select null
      into   l_dummy
      from sys.dual
      where exists
         (select 1
         from per_pos_structure_versions psv
         where psv.date_from <= nvl(X_Date_To, X_End_Of_Time)
         and   nvl(psv.date_to,X_End_Of_Time) >= X_Date_From
         and   psv.position_structure_id = X_Position_Structure_Id
         and   (psv.rowid <> X_Rowid
         or    X_Rowid is null));

Line: 174

select *
from per_pos_structure_elements pse
where pse.pos_structure_version_id = X_Copy_Structure_Version_Id;

Line: 185

INSERT INTO PER_POS_STRUCTURE_ELEMENTS(
          pos_structure_element_id,
          business_group_id,
          pos_structure_version_id,
          subordinate_position_id,
          parent_position_id
         )
select per_pos_structure_elements_s.nextval,
          business_group_id,
          x_pos_structure_version_id,
          subordinate_position_id,
          parent_position_id
from per_pos_structure_elements pse
where pse.pos_structure_version_id = X_Copy_Structure_Version_Id;

Line: 204

      PER_POS_STRUCTURE_ELEMENTS_PKG.Insert_Row(
      X_Rowid => l_Rowid
      ,X_Pos_Structure_Element_Id =>l_Structure_element_id
      ,X_Business_Group_Id => ele_record.Business_Group_Id
      ,X_Pos_Structure_Version_Id => X_Pos_Structure_Version_Id
      ,X_Subordinate_Position_Id => ele_record.Subordinate_Position_Id
      ,X_Parent_Position_Id => ele_record.Parent_Position_Id);

Line: 222

PROCEDURE pre_delete_checks(X_Pos_Structure_Version_Id      NUMBER,
                            X_Business_Group_Id             NUMBER,
                            X_Position_Structure_Id         NUMBER,
                            X_Hr_Installed                  VARCHAR2) IS
--
-- Local Variable
--
l_dummy VARCHAR2(1);

Line: 232

     select null
     into   l_dummy
     from sys.dual
     where exists( select null
     from PER_POS_STRUCTURE_ELEMENTS PSE
     where PSE.POS_STRUCTURE_VERSION_ID = X_Pos_Structure_Version_Id);

Line: 247

       select null
       into l_dummy
       from sys.dual
       where exists(select null
       from per_security_profiles
       where business_group_id + 0 = X_Business_Group_Id
       and   position_structure_id = X_Position_Structure_Id);

Line: 262

end pre_delete_checks;

Line: 264

PROCEDURE update_copies(X_Pos_Structure_Version_Id NUMBER) IS
--
--
--
cursor ele_update is
select rowid
from per_pos_structure_versions psv
where psv.copy_structure_version_id = X_Pos_Structure_Version_Id
for update of psv.copy_structure_version_id nowait;

Line: 278

   open ele_update;

Line: 279

   fetch ele_update into l_Rowid;

Line: 281

      exit when ele_update%NOTFOUND;

Line: 282

		update per_pos_structure_versions psv
		set psv.copy_structure_version_id = NULL
		where psv.rowid = l_Rowid;

Line: 286

      fetch ele_update into l_Rowid;

Line: 288

	close ele_update;

Line: 291

PROCEDURE Insert_Row(X_Rowid                        IN OUT NOCOPY VARCHAR2,
                     X_Pos_Structure_Version_Id            IN OUT NOCOPY NUMBER,
                     X_Business_Group_Id                   NUMBER,
                     X_Position_Structure_Id               NUMBER,
                     X_Date_From                           DATE,
                     X_Version_Number                      NUMBER,
                     X_Copy_Structure_Version_Id           NUMBER ,
                     X_Date_To                             DATE ,
							X_end_of_time                         DATE,
							X_Next_no_free                 IN OUT NOCOPY NUMBER,
							X_closedown_warning            IN OUT NOCOPY VARCHAR2,
							X_gap_warning                  IN OUT NOCOPY VARCHAR2
 ) IS
   CURSOR C IS SELECT rowid FROM PER_POS_STRUCTURE_VERSIONS
             WHERE pos_structure_version_id = X_Pos_Structure_Version_Id;

Line: 306

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

Line: 333

  INSERT INTO PER_POS_STRUCTURE_VERSIONS(
          pos_structure_version_id,
          business_group_id,
          position_structure_id,
          date_from,
          version_number,
          copy_structure_version_id,
          date_to
         ) VALUES (
          X_Pos_Structure_Version_Id,
          X_Business_Group_Id,
          X_Position_Structure_Id,
          X_Date_From,
          X_Version_Number,
          X_Copy_Structure_Version_Id,
          X_Date_To
  );

Line: 364

end Insert_Row;

Line: 376

      SELECT *
      FROM   PER_POS_STRUCTURE_VERSIONS
      WHERE  rowid = X_Rowid
      FOR UPDATE of Pos_Structure_Version_Id NOWAIT;

Line: 419

PROCEDURE Update_Row(X_Rowid                               VARCHAR2,
                     X_Pos_Structure_Version_Id            NUMBER,
                     X_Business_Group_Id                   NUMBER,
                     X_Position_Structure_Id               NUMBER,
                     X_Date_From                           DATE,
                     X_Version_Number                      NUMBER,
                     X_Copy_Structure_Version_Id           NUMBER ,
                     X_Date_To                             DATE ,
                     X_end_of_time                         DATE,
                     X_Next_no_free                 IN OUT NOCOPY NUMBER,
                     X_closedown_warning            IN OUT NOCOPY VARCHAR2,
                     X_gap_warning                  IN OUT NOCOPY VARCHAR2
) IS
BEGIN
   PER_POS_STRUCTURE_VERSIONS_PKG.check_version_number(
                        X_Position_Structure_Id=> X_Position_Structure_Id
                        ,X_Version_Number => X_Version_Number
                        ,X_Rowid => X_Rowid);

Line: 452

  UPDATE PER_POS_STRUCTURE_VERSIONS
  SET
    pos_structure_version_id                  =    X_Pos_Structure_Version_Id,
    business_group_id                         =    X_Business_Group_Id,
    position_structure_id                     =    X_Position_Structure_Id,
    date_from                                 =    X_Date_From,
    version_number                            =    X_Version_Number,
    copy_structure_version_id                 =    X_Copy_Structure_Version_Id,
    date_to                                   =    X_Date_To
  WHERE rowid = X_rowid;

Line: 467

END Update_Row;

Line: 469

PROCEDURE Delete_Row(X_Rowid VARCHAR2,
                     X_Pos_Structure_Version_Id NUMBER,
                     X_Business_Group_Id NUMBER,
                     X_Position_Structure_Id NUMBER,
                     X_Hr_Installed VARCHAR2,
                     X_Next_no_free                 IN OUT NOCOPY NUMBER,
                     X_closedown_warning            IN OUT NOCOPY VARCHAR2) IS
BEGIN
  pre_delete_checks(X_Pos_Structure_Version_Id => X_Pos_Structure_Version_Id,
                    X_Business_Group_Id        => X_Business_Group_Id,
                    X_Position_Structure_Id => X_Position_Structure_Id,
                    X_Hr_Installed             => X_Hr_Installed);

Line: 482

  DELETE FROM PER_POS_STRUCTURE_VERSIONS
  WHERE  rowid = X_Rowid;

Line: 489

END Delete_Row;