APPS.PER_EVENTS_PKG SQL Statements

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

Line: 7

g_dummy number(1);      -- dummy variable for 'select 1...' statements

Line: 25

SELECT 1
FROM   per_people_f
where  person_id= X_INTERNAL_CONTACT_PERSON_ID
AND    X_DATE_START BETWEEN
       effective_Start_date and effective_end_Date;

Line: 32

SELECT 1
FROM   hr_organization_units H
WHERE  H.business_group_id + 0 = X_BUSINESS_GROUP_ID
AND    X_DATE_START BETWEEN H.date_from
            and NVL(H.date_to , X_CTL_GLOBALS_END_OF_TIME)
AND H.ORGANIZATION_ID = X_ORGANIZATION_RUN_BY_ID;

Line: 40

SELECT 1
FROM  hr_locations l
WHERE X_DATE_START <= nvl(l.inactive_date,X_CTL_GLOBALS_END_OF_TIME)
AND   LOCATION_ID = X_LOCATION_ID;

Line: 48

SELECT 1
FROM   PER_PEOPLE_F P,
       PER_BOOKINGS B
WHERE  P.PERSON_ID = B.PERSON_ID
AND    B.EVENT_ID = X_EVENT_ID;

Line: 55

SELECT 1
FROM   PER_PEOPLE_F P,
       PER_BOOKINGS B
WHERE  P.PERSON_ID = B.PERSON_ID
AND    B.EVENT_ID = X_EVENT_ID
AND    X_DATE_START BETWEEN
       P.EFFECTIVE_START_DATE AND P.EFFECTIVE_END_DATE;  */

Line: 128

        select  1
        from    per_assignments_f
        where   assignment_id   = p_assignment_id
        and     effective_start_date    = p_event_date;

Line: 169

        select event.time_start, nvl(event.time_end,'24:00')
        from    per_events event, per_assignments assignment
        where   (p_rowid is null or p_rowid <> event.rowid)
        and     assignment.person_id = p_person_id
        and     event.assignment_id = assignment.assignment_id
        and     event.event_or_interview = 'I'
        and     p_interview_start_date  between event.date_start
                                        and nvl(event.date_end,
                                                event.date_start);

Line: 238

        select  1
        from    per_bookings
        where   event_id        = p_event_id;

Line: 254

  hr_utility.set_message (801,'PER_7517_APP_INT_DELETE');

Line: 273

        select  interview.person_id
        from    per_bookings            INTERVIEW
        where   interview.event_id      = p_event_id;

Line: 278

        select  min (effective_start_date)
        from    per_all_people_f
        where   person_id = p_person_id;

Line: 283

        select  max (effective_start_date)
        from    per_all_people_f
        where   person_id = p_person_id;

Line: 296

        select 'Y'
        from per_all_workforce_v
        where person_id = p_person_id
        and p_new_interview_start_date between effective_start_date
                and effective_end_date;

Line: 346

select null
from    per_letter_gen_statuses s
where   s.business_group_id + 0 = p_business_group_id
and     s.assignment_status_type_id = p_ASSIGNMENT_STATUS_TYPE_ID
and     s.enabled_flag = 'Y';

Line: 353

Select vacancy_id
From per_all_assignments_f
Where assignment_id = p_assignment_id
And p_session_date between effective_start_date and effective_end_date;

Line: 362

SELECT 1
FROM  PER_LETTER_REQUESTS PLR,
      PER_LETTER_GEN_STATUSES PLGS
WHERE PLGS.business_group_id + 0 = p_business_group_id
AND   PLR.business_group_id +0 = p_business_group_id
AND   PLGS.assignment_status_type_id = p_assignment_status_type_id
AND   PLR.letter_type_id = PLGS.letter_type_id
AND   PLR.auto_or_manual = 'MANUAL';

Line: 401

  insert into per_letter_requests
  (       letter_request_id
  ,       business_group_id
  ,       letter_type_id
  ,       request_status
  ,       auto_or_manual
  ,       date_from
  ,       last_update_date
  ,       last_updated_by
  ,       last_update_login
  ,       created_by
  ,       creation_date
  ,       vacancy_id)
  select  per_letter_requests_s.nextval
  ,       p_business_group_id
  ,       s.letter_type_id
  ,       'PENDING'
  ,       'AUTO'
  ,       p_session_date
  ,       sysdate
  ,       p_user
  ,       p_login_id
  ,       p_user
  ,       sysdate
  ,       l_vacancy_id
  from    per_letter_gen_statuses s
  where   s.business_group_id + 0 = p_business_group_id
  and     s.assignment_status_type_id = p_assignment_status_type_id
  and     s.enabled_flag = 'Y'
  and     not exists
          (select null
           from   per_letter_requests r
           where  r.letter_type_id = s.letter_type_id
           and    r.business_group_id + 0 = p_business_group_id
           and    r.business_group_id + 0 = s.business_group_id
           and    r.request_status = 'PENDING'
           and    r.auto_or_manual = 'AUTO'
	   and    r.vacancy_id 	= l_vacancy_id);

Line: 446

  insert into per_letter_request_lines
  (       letter_request_line_id
  ,       business_group_id
  ,       letter_request_id
  ,       person_id
  ,       assignment_id
  ,       assignment_status_type_id
  ,       date_from
  ,       last_update_date
  ,       last_updated_by
  ,       last_update_login
  ,       created_by
  ,       creation_date)
  select  per_letter_request_lines_s.nextval
  ,       p_business_group_id
  ,       r.letter_request_id
  ,       p_person_id
  ,       p_ASSIGNMENT_ID
  ,       p_ASSIGNMENT_STATUS_TYPE_ID
  ,       p_session_date
  ,       sysdate
  ,       p_user
  ,       p_login_id
  ,       p_user
  ,       sysdate
  from    per_letter_requests r
  where   exists
                  (select null
                  from    per_letter_gen_statuses s
                  where   s.letter_type_id = r.letter_type_id
                  and     s.business_group_id + 0 = p_business_group_id
                  and     s.business_group_id + 0 = r.business_group_id + 0
                  and     s.assignment_status_type_id =
                          p_ASSIGNMENT_STATUS_TYPE_ID
                  and     s.enabled_flag = 'Y')
  and     not exists
                  (select l.assignment_id
                  from    per_letter_request_lines l
                  where   l.letter_request_id = r.letter_request_id
                  and     l.business_group_id + 0 = p_business_group_id
                  and     l.assignment_id = p_ASSIGNMENT_ID
                  and     l.business_group_id +0 = r.business_group_id + 0)
  and    r.request_status = 'PENDING'
  and    r.business_group_id  = p_business_group_id   -- bug fix 3648618
  and    r.vacancy_id 	= l_vacancy_id;

Line: 494

   insert into per_letter_requests
  (       letter_request_id
  ,       business_group_id
  ,       letter_type_id
  ,       request_status
  ,       auto_or_manual
  ,       date_from
  ,       last_update_date
  ,       last_updated_by
  ,       last_update_login
  ,       created_by
  ,       creation_date
  ,       vacancy_id)
  select  per_letter_requests_s.nextval
  ,       p_business_group_id
  ,       s.letter_type_id
  ,       'PENDING'
  ,       'AUTO'
  ,       p_session_date
  ,       sysdate
  ,       p_user
  ,       p_login_id
  ,       p_user
  ,       sysdate
  ,       l_vacancy_id
  from    per_letter_gen_statuses s
  where   s.business_group_id + 0 = p_business_group_id
  and     s.assignment_status_type_id = p_assignment_status_type_id
  and     s.enabled_flag = 'Y'
  and     not exists
          (select null
           from   per_letter_requests r
           where  r.letter_type_id = s.letter_type_id
           and    r.business_group_id + 0 = p_business_group_id
           and    r.business_group_id + 0 = s.business_group_id
           and    r.request_status = 'PENDING'
           and    r.auto_or_manual = 'AUTO'
);

Line: 539

  insert into per_letter_request_lines
  (       letter_request_line_id
  ,       business_group_id
  ,       letter_request_id
  ,       person_id
  ,       assignment_id
  ,       assignment_status_type_id
  ,       date_from
  ,       last_update_date
  ,       last_updated_by
  ,       last_update_login
  ,       created_by
  ,       creation_date)
  select  per_letter_request_lines_s.nextval
  ,       p_business_group_id
  ,       r.letter_request_id
  ,       p_person_id
  ,       p_ASSIGNMENT_ID
  ,       p_ASSIGNMENT_STATUS_TYPE_ID
  ,       p_session_date
  ,       sysdate
  ,       p_user
  ,       p_login_id
  ,       p_user
  ,       sysdate
  from    per_letter_requests r
  where   exists
                  (select null
                  from    per_letter_gen_statuses s
                  where   s.letter_type_id = r.letter_type_id
                  and     s.business_group_id + 0 = p_business_group_id
                  and     s.business_group_id + 0 = r.business_group_id + 0
                  and     s.assignment_status_type_id =
                          p_ASSIGNMENT_STATUS_TYPE_ID
                  and     s.enabled_flag = 'Y')
  and     not exists
                  (select l.assignment_id
                  from    per_letter_request_lines l
                  where   l.letter_request_id = r.letter_request_id
                  and     l.business_group_id + 0 = p_business_group_id
                  and     l.assignment_id = p_ASSIGNMENT_ID
                  and     l.business_group_id +0 = r.business_group_id + 0)
  and    r.request_status = 'PENDING'
  and    r.business_group_id  = p_business_group_id   -- bug fix 3648618
 ;

Line: 589

PROCEDURE Insert_Row(X_Rowid                        IN OUT NOCOPY VARCHAR2,
                     X_Event_Id                     IN OUT NOCOPY NUMBER,
                     X_Business_Group_Id                   NUMBER,
                     X_Location_Id                         NUMBER,
                     X_Internal_Contact_Person_Id          NUMBER,
                     X_Organization_Run_By_Id              NUMBER,
                     X_Assignment_Id                       NUMBER,
                     X_Date_Start                          DATE,
                     X_Type                                VARCHAR2,
                     X_Comments                            VARCHAR2,
                     X_Contact_Telephone_Number            VARCHAR2,
                     X_Date_End                            DATE,
                     X_Emp_Or_Apl                          VARCHAR2,
                     X_Event_Or_Interview                  VARCHAR2,
                     X_External_Contact                    VARCHAR2,
                     X_Time_End                            VARCHAR2,
                     X_Time_Start                          VARCHAR2,
                     X_Attribute_Category                  VARCHAR2,
                     X_Attribute1                          VARCHAR2,
                     X_Attribute2                          VARCHAR2,
                     X_Attribute3                          VARCHAR2,
                     X_Attribute4                          VARCHAR2,
                     X_Attribute5                          VARCHAR2,
                     X_Attribute6                          VARCHAR2,
                     X_Attribute7                          VARCHAR2,
                     X_Attribute8                          VARCHAR2,
                     X_Attribute9                          VARCHAR2,
                     X_Attribute10                         VARCHAR2,
                     X_Attribute11                         VARCHAR2,
                     X_Attribute12                         VARCHAR2,
                     X_Attribute13                         VARCHAR2,
                     X_Attribute14                         VARCHAR2,
                     X_Attribute15                         VARCHAR2,
                     X_Attribute16                         VARCHAR2,
                     X_Attribute17                         VARCHAR2,
                     X_Attribute18                         VARCHAR2,
                     X_Attribute19                         VARCHAR2,
                     X_Attribute20                         VARCHAR2,
                     X_ctl_globals_end_of_time             DATE
 ) IS

 L_DUMMY  NUMBER;

Line: 634

 SELECT rowid FROM PER_EVENTS
 WHERE event_id = X_Event_Id;

Line: 638

 SELECT PER_EVENTS_S.NEXTVAL
 FROM SYS.DUAL;

Line: 642

 select 1
 from   hr_locations l
 where  l.location_id = X_Location_Id
 and    nvl(l.inactive_date,X_ctl_globals_end_of_time) >= X_date_start;

Line: 648

 select max(party_id) from per_all_people_f
 where person_id = (select asg.person_id
                    from per_all_assignments_f asg
                    where asg.assignment_id = X_Assignment_Id
                    and X_Date_Start between asg.effective_start_date
                                         and asg.effective_end_date);

Line: 697

  INSERT INTO PER_EVENTS(
          event_id,
          business_group_id,
          location_id,
          internal_contact_person_id,
          organization_run_by_id,
          assignment_id,
          date_start,
          type,
          comments,
          contact_telephone_number,
          date_end,
          emp_or_apl,
          event_or_interview,
          external_contact,
          time_end,
          time_start,
          attribute_category,
          attribute1,
          attribute2,
          attribute3,
          attribute4,
          attribute5,
          attribute6,
          attribute7,
          attribute8,
          attribute9,
          attribute10,
          attribute11,
          attribute12,
          attribute13,
          attribute14,
          attribute15,
          attribute16,
          attribute17,
          attribute18,
          attribute19,
          attribute20,
          party_id
         ) VALUES (
          X_Event_Id,
          X_Business_Group_Id,
          X_Location_Id,
          X_Internal_Contact_Person_Id,
          X_Organization_Run_By_Id,
          X_Assignment_Id,
          X_Date_Start,
          X_Type,
          X_Comments,
          X_Contact_Telephone_Number,
          X_Date_End,
          X_Emp_Or_Apl,
          X_Event_Or_Interview,
          X_External_Contact,
          X_Time_End,
          X_Time_Start,
          X_Attribute_Category,
          X_Attribute1,
          X_Attribute2,
          X_Attribute3,
          X_Attribute4,
          X_Attribute5,
          X_Attribute6,
          X_Attribute7,
          X_Attribute8,
          X_Attribute9,
          X_Attribute10,
          X_Attribute11,
          X_Attribute12,
          X_Attribute13,
          X_Attribute14,
          X_Attribute15,
          X_Attribute16,
          X_Attribute17,
          X_Attribute18,
          X_Attribute19,
          X_Attribute20,
          l_party_id
  );

Line: 782

   HR_UTILITY.SET_MESSAGE_TOKEN('PROCEDURE','INSERT_ROW');

Line: 787

END Insert_Row;

Line: 829

      SELECT *
      FROM   PER_EVENTS
      WHERE  rowid = X_Rowid
      FOR UPDATE of Event_Id   NOWAIT;

Line: 994

PROCEDURE Update_Row(X_Rowid                               VARCHAR2,
                     X_Event_Id                            NUMBER,
                     X_Business_Group_Id                   NUMBER,
                     X_Location_Id                         NUMBER,
                     X_Internal_Contact_Person_Id          NUMBER,
                     X_Organization_Run_By_Id              NUMBER,
                     X_Assignment_Id                       NUMBER,
                     X_Date_Start                          DATE,
                     X_Type                                VARCHAR2,
                     X_Comments                            VARCHAR2,
                     X_Contact_Telephone_Number            VARCHAR2,
                     X_Date_End                            DATE,
                     X_Emp_Or_Apl                          VARCHAR2,
                     X_Event_Or_Interview                  VARCHAR2,
                     X_External_Contact                    VARCHAR2,
                     X_Time_End                            VARCHAR2,
                     X_Time_Start                          VARCHAR2,
                     X_Attribute_Category                  VARCHAR2,
                     X_Attribute1                          VARCHAR2,
                     X_Attribute2                          VARCHAR2,
                     X_Attribute3                          VARCHAR2,
                     X_Attribute4                          VARCHAR2,
                     X_Attribute5                          VARCHAR2,
                     X_Attribute6                          VARCHAR2,
                     X_Attribute7                          VARCHAR2,
                     X_Attribute8                          VARCHAR2,
                     X_Attribute9                          VARCHAR2,
                     X_Attribute10                         VARCHAR2,
                     X_Attribute11                         VARCHAR2,
                     X_Attribute12                         VARCHAR2,
                     X_Attribute13                         VARCHAR2,
                     X_Attribute14                         VARCHAR2,
                     X_Attribute15                         VARCHAR2,
                     X_Attribute16                         VARCHAR2,
                     X_Attribute17                         VARCHAR2,
                     X_Attribute18                         VARCHAR2,
                     X_Attribute19                         VARCHAR2,
                     X_Attribute20                         VARCHAR2,
                     X_ctl_globals_end_of_time             DATE
) IS

 L_DUMMY NUMBER;

Line: 1038

 select 1
 from   hr_locations l
 where  l.location_id = X_Location_Id
 and    nvl(l.inactive_date,X_ctl_globals_end_of_time) >= X_Date_Start;

Line: 1075

  UPDATE PER_EVENTS
  SET

    event_id                                  =    X_Event_Id,
    business_group_id                         =    X_Business_Group_Id,
    location_id                               =    X_Location_Id,
   internal_contact_person_id                =    X_Internal_Contact_Person_Id,
    organization_run_by_id                    =    X_Organization_Run_By_Id,
    assignment_id                             =    X_Assignment_Id,
    date_start                                =    X_Date_Start,
    type                                      =    X_Type,
    comments                                  =    X_Comments,
    contact_telephone_number                  =    X_Contact_Telephone_Number,
    date_end                                  =    X_Date_End,
    emp_or_apl                                =    X_Emp_Or_Apl,
    event_or_interview                        =    X_Event_Or_Interview,
    external_contact                          =    X_External_Contact,
    time_end                                  =    X_Time_End,
    time_start                                =    X_Time_Start,
    attribute_category                        =    X_Attribute_Category,
    attribute1                                =    X_Attribute1,
    attribute2                                =    X_Attribute2,
    attribute3                                =    X_Attribute3,
    attribute4                                =    X_Attribute4,
    attribute5                                =    X_Attribute5,
    attribute6                                =    X_Attribute6,
    attribute7                                =    X_Attribute7,
    attribute8                                =    X_Attribute8,
    attribute9                                =    X_Attribute9,
    attribute10                               =    X_Attribute10,
    attribute11                               =    X_Attribute11,
    attribute12                               =    X_Attribute12,
    attribute13                               =    X_Attribute13,
    attribute14                               =    X_Attribute14,
    attribute15                               =    X_Attribute15,
    attribute16                               =    X_Attribute16,
    attribute17                               =    X_Attribute17,
    attribute18                               =    X_Attribute18,
    attribute19                               =    X_Attribute19,
    attribute20                               =    X_Attribute20
  WHERE rowid = X_rowid;

Line: 1119

    HR_UTILITY.SET_MESSAGE_TOKEN('PROCEDURE','UPDATE_ROW');

Line: 1124

END Update_Row;

Line: 1131

PROCEDURE Delete_Row(X_Rowid VARCHAR2,
                     X_Event_Id NUMBER,
                     X_Business_Group_Id NUMBER,
                     X_Message  VARCHAR2,
                     X_Form     VARCHAR2
                ) IS
L_DUMMY  NUMBER;

Line: 1141

 SELECT 1
 FROM  PER_BOOKINGS
 WHERE BUSINESS_GROUP_ID + 0 = X_Business_Group_Id
 AND   EVENT_ID = X_Event_Id;

Line: 1147

 SELECT 1
 FROM   PER_PAY_PROPOSALS PP
 WHERE  PP.EVENT_ID = X_Event_Id;

Line: 1177

  DELETE FROM PER_EVENTS
  WHERE  rowid = X_Rowid;

Line: 1182

    HR_UTILITY.SET_MESSAGE_TOKEN('PROCEDURE','DELETE_ROW');

Line: 1186

END Delete_Row;