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.PQH_PROCESS_EMP_REVIEW SQL Statements

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

Line: 5


Select user_name
 From fnd_user
 Where employee_id = p_person_id;

Line: 11


  ,p_show_deleted        in varchar2 default NULL ) RETURN ref_cursor IS

csr REF_CURSOR;

Line: 17


  SELECT
      to_number(reviewer.event_id) event_id,
      to_number(reviewer.booking_id) Booking_id,
      pf.full_name employee_name,
      reviewer.employee_no employee_number,
      to_number(reviewer.person_id) person_id,
      rownum -1 row_index,
      reviewer.comments,
      reviewer.status,
      reviewer.business_group_id
  FROM (
      SELECT hr_transaction_api.get_varchar2_value (p_transaction_step_id , 'P_EVENT_ID'||x_row) event_id,
             hr_transaction_api.get_varchar2_value (p_transaction_step_id , 'P_BOOKING_ID'||x_row) booking_id,
             hr_transaction_api.get_varchar2_value (p_transaction_step_id , 'P_EMPLOYEE_NO'||x_row) employee_no,
             hr_transaction_api.get_varchar2_value (p_transaction_step_id , 'P_PERSON_ID'||x_row)   person_id,
             hr_transaction_api.get_varchar2_value (p_transaction_step_id , 'P_COMMENTS'||x_row)    comments,
             hr_transaction_api.get_varchar2_value (p_transaction_step_id , 'P_STATUS'||x_row)      status,
             hr_transaction_api.get_varchar2_value (p_transaction_step_id , 'P_BUSINESS_GROUP_ID'||x_row) business_group_id
      FROM  ( select substr(abc.name,14) x_row
              from  hr_api_transaction_values abc
              where abc.transaction_step_id = p_transaction_step_id
              and   abc.name like 'P_EMPLOYEE_NO%'
             ) x
    )     reviewer,
          per_all_people_f  pf
    WHERE pf.person_id = reviewer.person_id
    AND   (p_show_deleted = 'Y' or NVL(status,'E') <> 'D' )    -- Don't show deleted reviewers
    AND   SYSDATE BETWEEN pf.effective_start_date AND pf.effective_end_date;

Line: 57


    SELECT
       to_number(review.event_id)                     event_id,
       review.Type,
       hl.meaning,
       fnd_date.canonical_to_date(review.date_start)  date_start,
       fnd_date.canonical_to_date(review.date_end)    date_end,
       review.time_start                              time_start,
       review.time_end                                time_end  ,
       to_number(review.location_id)                  location_id  ,
       loc.location_code                              location,
       review.comments      ,
       to_number(review.assignment_id)                assignment_id ,
       review.notify_flag,
       review.business_group_id
    FROM (
      SELECT
             max(event_id)          event_id,
             max(Type)              type,
             max(date_start)        date_start,
             max(date_end)          date_end,
             max(time_start)        time_start,
             max(time_end)          time_end,
             max(location_id)       location_id,
             max(comments)          comments,
             max(assignment_id)     assignment_id,
             max(notify_flag)       notify_flag,
             max(business_group_id) business_group_id
      FROM (
          SELECT
                 decode(a.name, 'P_EVENT_ID'          , a.varchar2_value ,null) Event_Id,
                 decode(a.name, 'P_TYPE'              , a.varchar2_value ,null) Type,
                 decode(a.name, 'P_DATE_START'        , a.varchar2_value ,null) Date_Start,
                 decode(a.name, 'P_DATE_END'          , a.varchar2_value ,null) Date_End,
                 decode(a.name, 'P_TIME_START'        , a.varchar2_value ,null) Time_Start,
                 decode(a.name, 'P_TIME_END'          , a.varchar2_value ,null) Time_End,
                 decode(a.name, 'P_LOCATION_ID'       , a.varchar2_value ,null) Location_Id,
                 decode(a.name, 'P_COMMENTS'          , a.varchar2_value ,null) Comments,
                 decode(a.name, 'P_ASSIGNMENT_ID'     , a.varchar2_value ,null) Assignment_Id,
                 decode(a.name, 'P_NOTIFY_FLAG'       , a.varchar2_value ,null) Notify_Flag,
                 decode(a.name, 'P_BUSINESS_GROUP_ID' , a.varchar2_value ,null) Business_Group_Id
           FROM hr_api_transaction_steps  s,
                hr_api_transaction_values a
           WHERE s.transaction_step_id = a.transaction_step_id
           AND   s.transaction_step_id = p_transaction_step_id
           AND   s.api_name            = 'PQH_PROCESS_EMP_REVIEW.PROCESS_API'
           )
          )  review ,
             hr_lookups   hl,
             hr_locations loc
      where  hl.lookup_type  = 'EMP_INTERVIEW_TYPE'
      AND    hl.lookup_code  = review.type
      AND    loc.location_id (+) = review.location_id
      AND    sysdate         <= nvl(loc.inactive_date, sysdate);

Line: 262


 x_total_deleted_rows   OUT NOCOPY NUMBER) is

 cursor Rec_Count(p_step_id varchar2) is
 select count(*)
 from hr_api_transaction_values
 where transaction_step_id = p_step_id
 and name like 'P_EMPLOYEE_NO_';

Line: 270


 cursor Rec_Count_Deleted(p_step_id varchar2) is
 select count(*)
 from hr_api_transaction_values
 where transaction_step_id = p_step_id
 and name like 'P_EMPLOYEE_NO%D';

Line: 288


open Rec_Count_Deleted(x_transaction_step_id);

Line: 290


 Fetch Rec_Count_Deleted into x_total_deleted_rows;

Line: 292


close Rec_Count_Deleted;

Line: 298


 x_total_deleted_rows   := null;

Line: 743


select rowid from per_events
where event_id = c_event_id;

Line: 748


select wf_engine.GetItemAttrText(item_type, item_key, 'CURRENT_PERSON_DISPLAY_NAME')
from hr_api_transaction_steps
where transaction_step_id = p_transaction_step_id;

Line: 817


  PER_EVENTS_PKG.Update_row(X_Rowid 		 => l_Row_id,
                       X_Event_Id   		 => l_Event_id,
                       X_Business_Group_Id	 => l_Business_Group_Id,
                       X_Location_Id             => l_Location_id,
                       X_Internal_Contact_Person_Id => null,
                       X_Organization_Run_By_Id     => null,
                       X_Assignment_Id              => l_Assignment_Id,
                       X_Date_Start 		    => fnd_date.chardt_to_date(l_Date_Start),
                       X_Type                       => l_Type,
                       X_Comments                   => l_Comments,
                       X_Contact_Telephone_Number   => null ,
                       X_Date_End                   => fnd_date.chardt_to_date(l_Date_End ),
                       X_Emp_Or_Apl                 => 'E',
                       X_Event_Or_Interview         => 'I' ,
                       X_External_Contact           => null ,
                       X_Time_End                   => l_Time_End,
                       X_Time_Start                 => l_Time_Start,
                       X_Attribute_Category         => null,
                       X_Attribute1                 => null,
                       X_Attribute2                 => null,
                       X_Attribute3                 => null,
                       X_Attribute4                 => null,
                       X_Attribute5		=> null,
                       X_Attribute6		=> null,
                       X_Attribute7		=> null,
                       X_Attribute8		=> null,
                       X_Attribute9		=> null,
                       X_Attribute10   		=> null,
                       X_Attribute11		=> null,
                       X_Attribute12		=> null,
                       X_Attribute13		=> null,
                       X_Attribute14		=> null,
                       X_Attribute15		=> null,
                       X_Attribute16		=> null,
                       X_Attribute17		=> null,
                       X_Attribute18		=> null,
                       X_Attribute19		=> null,
                       X_Attribute20		=> null,
                     X_ctl_globals_end_of_time  => fnd_date.chardt_to_date(l_Date_Start));

Line: 859


   PER_EVENTS_PKG.Insert_row(X_Rowid 		  	 => l_Row_id,
                          X_Event_Id   			 => l_Event_id,
                          X_Business_Group_Id		 => l_Business_Group_Id,
                          X_Location_Id           	 => l_Location_id,
                          X_Internal_Contact_Person_Id => null,
                          X_Organization_Run_By_Id     => null,
                          X_Assignment_Id              => l_Assignment_Id,
                          X_Date_Start 		       => fnd_date.chardt_to_date(l_Date_Start),
                          X_Type                       => l_Type,
                          X_Comments                   => l_Comments,
                          X_Contact_Telephone_Number   => null ,
                          X_Date_End                   => fnd_date.chardt_to_date(l_Date_End ),
                          X_Emp_Or_Apl                 => 'E',
                          X_Event_Or_Interview         => 'I' ,
                          X_External_Contact           => null ,
                          X_Time_End                   => l_Time_End,
                          X_Time_Start                 => l_Time_Start,
                          X_Attribute_Category         => null,
                          X_Attribute1                 => null,
                          X_Attribute2                 => null,
                          X_Attribute3                 => null,
                          X_Attribute4                 => null,
                          X_Attribute5		=> null,
                          X_Attribute6		=> null,
                          X_Attribute7		=> null,
                          X_Attribute8		=> null,
                          X_Attribute9		=> null,
                          X_Attribute10   	=> null,
                          X_Attribute11		=> null,
                          X_Attribute12		=> null,
                          X_Attribute13		=> null,
                          X_Attribute14		=> null,
                          X_Attribute15		=> null,
                          X_Attribute16		=> null,
                          X_Attribute17		=> null,
                          X_Attribute18		=> null,
                          X_Attribute19		=> null,
                          X_Attribute20		=> null,
                     X_ctl_globals_end_of_time  => fnd_date.chardt_to_date(l_Date_Start));

Line: 992


    select rowid
    from per_bookings
    where Booking_id = p_Booking_id;

Line: 997


     Select rowid
     From per_bookings
     Where person_id =p_personId and event_id = p_event_id;

Line: 1002


   Select count(*)
   from per_bookings
   where person_id = x_Person_Id and event_Id = x_Event_Id;

Line: 1012


    hr_utility.set_location('p_personId '||p_personId||'Delete Place'||p_status,10);

Line: 1020


       hr_utility.set_location('Row Id in Delete '||l_rowId,10);

Line: 1021


       per_bookings_pkg.Delete_Row(X_Rowid  => l_rowId);

Line: 1022


       hr_utility.set_location(' After Row Id in Delete '||l_rowId,10);

Line: 1035


       PER_BOOKINGS_PKG.Update_Row(X_Rowid       => l_rowId,
                            X_Booking_Id         => p_Booking_Id ,
                            X_Business_Group_Id  => p_Business_Group_Id,
                            X_Person_Id          => p_personId,
                            X_Event_Id           => p_event_id,
                            X_Message	         => null,
       	                    X_Token              => null,
                            X_Comments           => p_Comments,
                            X_Attribute_Category => null,
                            X_Attribute1         => null,
                            X_Attribute2         => null,
                            X_Attribute3         => null,
                            X_Attribute4         => null,
                            X_Attribute5         => null,
                            X_Attribute6         => null,
                            X_Attribute7         => null,
                            X_Attribute8         => null,
                            X_Attribute9         => null,
                            X_Attribute10        => null,
                            X_Attribute11        => null,
                            X_Attribute12        => null,
                            X_Attribute13        => null,
                            X_Attribute14        => null,
                            X_Attribute15        => null,
                            X_Attribute16        => null,
                            X_Attribute17        => null,
                            X_Attribute18        => null,
                            X_Attribute19        => null,
                            X_Attribute20        => null);

Line: 1074


      PER_BOOKINGS_PKG.Insert_Row(X_Rowid       => l_Row_Id,
                              X_Booking_Id         => l_Booking_Id ,
                              X_Business_Group_Id  => p_Business_Group_Id,
                              X_Person_Id          => p_personId,
                              X_Event_Id           => pkg_event_id,
                              X_Message	    	   => null,
         	              X_Token              => null,
                              X_Comments           => p_Comments,
                              X_Attribute_Category => null,
                              X_Attribute1         => null,
                              X_Attribute2         => null,
                              X_Attribute3         => null,
                              X_Attribute4         => null,
                              X_Attribute5         => null,
                              X_Attribute6         => null,
                              X_Attribute7         => null,
                              X_Attribute8         => null,
                              X_Attribute9         => null,
                              X_Attribute10        => null,
                              X_Attribute11        => null,
                              X_Attribute12        => null,
                              X_Attribute13        => null,
                              X_Attribute14        => null,
                              X_Attribute15        => null,
                              X_Attribute16        => null,
                              X_Attribute17        => null,
                              X_Attribute18        => null,
                              X_Attribute19        => null,
                          X_Attribute20        => null);