Query to get List of Custom Form List

SELECT  DISTINCT forms.form_name,
          formstl.user_form_name,
          func.function_name,
          func.user_function_name,
          fm.menu_name,
          menu.prompt menu_prompt,
          menu.description,
          restl.responsibility_name
   FROM   fnd_form FORMS,
          fnd_form_tl FORMSTL,
          fnd_form_functions_VL FUNC,
          fnd_menu_entries_VL MENU,
          FND_MENUS FM,
          fnd_responsibility RES,
          fnd_responsibility_tl RESTL
  WHERE   1=1
          and forms.form_id = formstl.form_id
          and func.form_id = forms.form_id
          and menu.function_id = func.function_id
          and menu.menu_id=fm.menu_id
          and res.menu_id = menu.menu_id
          and res.responsibility_id = restl.responsibility_id
          and UPPER(forms.form_name) like '%XXCUST%'      
          order by 1;

Query to get List of Custom Concurrent Programs

SELECT DISTINCT fcp.user_concurrent_program_name "Concurrent Program Name",
 fa.application_name,
 fcp.concurrent_program_name conc_short_name,
 decode( fef.execution_method_code, 'H','Host',
 'I', 'PLSQL Stored Procedure',
 'P', 'Report',
 'L', 'SQL Loader',
 'Q','SQL*Plus',
 'A','Spawned',
 'K','Java Concurrent Program',
 'B','Request Set Stage Function',
 'S','Immediate',
 'J','Java Stored Procedure',
 'M','Multi-Language Function',
 fef.execution_method_code) "execution method",
 fef.executable_name,
 fcp.description "Concurrent Program Description",
 fef.executable_name"Executable Name",
 fef.description "Executable Description",
 fef.execution_file_name ,
 fcp.enabled_flag
 ,fcp.creation_date
FROM fnd_executables_form_v fef,
     fnd_concurrent_programs_VL fcp,
     fnd_application_tl fa
WHERE fcp.application_id=fef.application_id
  AND fa.application_id=fcp.application_id
  AND fef.executable_id=fcp.executable_id
  AND UPPER(fcp.user_concurrent_program_name) like '%XXCUST%'
ORDER BY 4;

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;