Important HR tables

Error Accessing the System Registry while opening Oracle workflow Builder - Solved

1.  Right click on Error Accessing the System Registry
2. Go to Properties
3. Click on Compatibility tab
4. Check in "Run this program in compatibility mode for: "
5. Press button Apply or OK
6. Run Oracle Workflow Builder.

Re hire employee using api hr_employee_api.re_hire_ex_employee

create or replace procedure xx_re_hire_emp
 (
   p_person_id number,
   p_hire_date date;
 )
 is   
  lp_assignment_id                     per_all_assignments_f.assignment_id%type;
  lp_asg_object_version_number         number;
  lp_per_effective_start_date          date;
  lp_per_effective_end_date            date;
  lp_assignment_sequence               per_all_assignments_f.assignment_sequence%type;
  lp_assignment_number                 per_all_assignments_f.assignment_number%type;
  lp_assign_payroll_warning            boolean;
  lp_object_version_number             per_all_people_f.object_version_number%TYPE ;
 
  cursor cur_get_ovn is
    SELECT object_version_number
      FROM apps.per_people_x
     WHERE person_id= p_person_id;
 
begin

 open  cur_get_ovn;
  fetch cur_get_ovn into lp_object_version_number;
 close cur_get_ovn;

hr_employee_api.re_hire_ex_employee
(

  --input data --
 
   p_validate                      => false
  ,p_hire_date                     => p_hire_date
  ,p_person_id                     => p_person_id
  ,p_rehire_reason                 => 'Hire'
 
  --output data --
 
  ,p_assignment_id                 => lp_assignment_id
  ,p_asg_object_version_number     => lp_asg_object_version_number
  ,p_per_effective_start_date      => lp_per_effective_start_date
  ,p_per_effective_end_date        => lp_per_effective_end_date
  ,p_assignment_sequence           => lp_assignment_sequence
  ,p_assignment_number             => lp_assignment_number
  ,p_assign_payroll_warning        => lp_assign_payroll_warning
  ,p_per_object_version_number     => lp_object_version_number 

);
commit;

end;

Create employee using Api hr_employee_api.create_employee

 --Firt of all create procedure
 create or replace procedure xx_create_employee
 (
   lp_hire_date         date
  ,lp_business_group_id number
  ,lp_first_name        varchar2
  ,lp_last_name         varchar2
  ,lp_sex               varchar2
  ,lp_date_of_birth     date
  ,lp_known_as          varchar2
 )
 as
 l_employee_number                   per_all_people_f.employee_number%type;
 l_person_id                         per_all_people_f.person_id%type;
 l_assignment_id                     number;
 l_per_object_version_number         number;
 l_asg_object_version_number         number;
 l_per_effective_start_date          date;
 l_per_effective_end_date            date;
 l_full_name                         per_all_people_f.full_name%type;
 l_per_comment_id                    number;
 l_assignment_sequence               number;
 l_assignment_number                 per_all_assignments_f.assignment_number%type;
 l_name_combination_warning          boolean;
 l_assign_payroll_warning            boolean;
 l_orig_hire_warning                 boolean;

begin
hr_employee_api.create_employee
(
--Input data--
   p_hire_date                   => lp_hire_date
  ,p_business_group_id           => lp_business_group_id
  ,p_last_name                   => lp_last_name
  ,p_sex                         => lp_sex
  ,p_first_name                  => lp_first_name
  ,p_middle_names                => null
  ,p_date_of_birth               => lp_date_of_birth
  ,p_known_as                    => lp_known_as
--Output Date--                
 ,p_employee_number              => l_employee_number
 ,p_person_id                    => l_person_id
 ,p_assignment_id                => l_assignment_id
 ,p_per_object_version_number    => l_per_object_version_number
 ,p_asg_object_version_number    => l_asg_object_version_number
 ,p_per_effective_start_date     => l_per_effective_start_date
 ,p_per_effective_end_date       => l_per_effective_end_date
 ,p_full_name                    => l_full_name
 ,p_per_comment_id               => l_per_comment_id 
 ,p_assignment_sequence          => l_assignment_sequence
 ,p_assignment_number            => l_assignment_number
 ,p_name_combination_warning     => l_name_combination_warning
 ,p_assign_payroll_warning       => l_assign_payroll_warning
 ,p_orig_hire_warning            => l_orig_hire_warning
) ;
 commit;

end;
--Execute procedure --
exec xx_create_employee(TO_DATE('01-JUN-2011'), 626, 'Celin','Deon', 'F',  TO_DATE('08-JUN-1991'), 'Celin');

--select created employee from table  to make sure it saved successfully --
select known_as, employee_number from per_all_people_f
where initcap(known_as) = 'Celin';

Delete pending leaves using reference table PER_ABSENCE_ATTENDANCES

create or replace procedure xx_delete_pending_leaves as

   CURSOR c1 IS

      SELECT hat.transaction_id, hats.transaction_step_id
        FROM hr_api_transactions hat, hr_api_transaction_steps hats
       WHERE hat.transaction_ref_table = 'PER_ABSENCE_ATTENDANCES'
         AND hat.transaction_group = 'ABSENCE_MGMT'
         AND hat.transaction_identifier = 'ABSENCES'
         AND hat.transaction_ref_id IS NOT NULL
         AND hat.status = 'Y'
         AND hat.transaction_id = hats.transaction_id;
        
   l_transaction_id        NUMBER;
   l_transaction_step_id   NUMBER;
   l_error                 VARCHAR2 (300);

BEGIN

  OPEN c1;

   LOOP

      FETCH c1 INTO l_transaction_id, l_transaction_step_id;
       EXIT WHEN c1%NOTFOUND;

      IF l_transaction_step_id IS NOT NULL  THEN

         DELETE FROM hr_api_transaction_values
          WHERE transaction_step_id = l_transaction_step_id;

         DBMS_OUTPUT.put_line

                     (   'Deleted Transaction Value of transaction step id: '

                      || l_transaction_step_id

                     );

      END IF;

      IF l_transaction_id IS NOT NULL THEN    

         DELETE FROM hr_api_transaction_steps
               WHERE transaction_id = l_transaction_id;
         DBMS_OUTPUT.put_line

                           (  
                           'Deleted Transaction step of transaction id: '|| l_transaction_id
                           );

         DELETE FROM hr_api_transactions
               WHERE transaction_id = l_transaction_id;        
         DBMS_OUTPUT.put_line ('Deleted Transaction ID: ' || l_transaction_id);
      END IF;
   END LOOP;
 CLOSE c1;
   COMMIT;

 EXCEPTION

   WHEN OTHERS THEN

      l_error := SUBSTR (SQLERRM, 1, 200);

      DBMS_OUTPUT.put_line (   'Other Error in xx_delete_pending_leaves- ' || l_error);

END xx_delete_pending_leaves;

Delete leave/absence using hr_person_absence_api.delete_person_absence

Step 1:

Create procedure

create or replace procedure apps.xx_absence_delete
 (
   p_absence_attendance_id number
 )
 as
l_object_version_number number;
begin

select object_version_number into l_object_version_number
  from per_absence_attendances
 where absence_attendance_id = p_absence_attendance_id;

hr_person_absence_api.delete_person_absence
(p_validate => FALSE
,p_absence_attendance_id => p_absence_attendance_id--30504
,p_object_version_number => l_object_version_number
);
exception
when others then null;
commit;
end;

Step 2:

Execute procedure

exec xx_absence_delete (30504);

Step 3:

Check deleted leave/absence

select object_version_number
  from per_absence_attendances
 where absence_attendance_id = 30504;

Enter Absence/Leave in Oracle HRMS using API

First of All we will create procedure.

CREATE OR REPLACE procedure APPS.xx_absence_insert
(
p_person_id                  number,
p_business_group_id          number,
p_absence_attendance_type_id number,
p_abs_attendance_reason_id   number,
p_effective_date             varchar2,
p_date_start                 varchar2,
p_date_end                   varchar2,
p_absence_days               number
)
as

l_absence_hours               Number;
l_absence_attendance_id       Number;                                               
l_object_version_number       Number;                                                
l_occurance                   Number;                                                
l_dur_dys_less_warning        boolean;                                               
l_dur_hrs_less_warning        boolean;                                               
l_exceeds_pto_entit_warning   boolean;                                               
l_exceeds_run_total_warning   boolean;                                               
l_abs_overlap_warning         boolean;                                               
l_abs_day_after_warning       boolean;                                               
l_dur_overwritten_warning     boolean;
l_absence_days                Number := p_absence_days;

BEGIN

HR_PERSON_ABSENCE_API.create_person_absence                                                                 
  (
   p_validate                    => FALSE                                
  ,p_effective_date              => to_date(p_effective_date, 'DD-MON-YYYY')                                            
  ,p_person_id                   => p_person_id                                          
  ,p_business_group_id           => p_business_group_id                                        
  ,p_absence_attendance_type_id  => p_absence_attendance_type_id                                          
  ,p_abs_attendance_reason_id    => p_abs_attendance_reason_id                                                              
  ,p_date_start                  => to_date(p_date_start , 'DD-MON-YYYY')                                                                   
  ,p_date_end                    => to_date(p_date_end, 'DD-MON-YYYY')                                                            
  ,p_absence_days                => l_absence_days                                             
  ,p_absence_hours               => l_absence_hours                                                                                  
  ,p_absence_attendance_id       => l_absence_attendance_id                                               
  ,p_object_version_number       => l_object_version_number                                                 
  ,p_occurrence                  => l_occurance                                                
  ,p_dur_dys_less_warning        => l_dur_dys_less_warning                                              
  ,p_dur_hrs_less_warning        => l_dur_hrs_less_warning                                              
  ,p_exceeds_pto_entit_warning   => l_exceeds_pto_entit_warning                                               
  ,p_exceeds_run_total_warning   => l_exceeds_run_total_warning                                               
  ,p_abs_overlap_warning         => l_abs_overlap_warning                                               
  ,p_abs_day_after_warning       => l_abs_day_after_warning  
  ,p_dur_overwritten_warning     => l_dur_overwritten_warning                                              
  );
  commit; 
                                                                                          
END;

Now we will execute procedure

exec xx_absence_insert(373, 626, 51, 78, '28-APR-2002','28-APR-2002','29-APR-2002',to_number(to_date('29-APR-2002','DD-MON-YYYY')-to_date('28-APR-2002','DD-MON-YYYY'))+1);

Now we will confirm the leave entry by using select statement

select distinct papf.full_name,
       paa.date_start,
       paa.date_end,
       paa.absence_days,
       paa.creation_date
 from per_absence_attendances paa, per_all_people_f papf
where papf.person_id = paa.person_id
  and paa.business_group_id = 626
  /*and paa.person_id          = 373*/
  and paa.absence_attendance_type_id = 51
  and trunc(sysdate) = trunc(paa.creation_date)
order by paa.creation_date desc;