How to delete a concurrent program in APPS

Oracle application allows creation of concurrent programs but does not allow deletion of the concurrent programs. Through form user can disable a concurrent program.

We can use API for deleting concurrent programs and executables:

Delete the concurrent program. The script is

BEGIN
   fnd_program.delete_program (program_short_name   =>'XXPMSJB',
                                                 application          =>'XXPMS');
   COMMIT;
END;

The above concurrent program is deleted and it cannot be queried.


The executable exists. It can be deleted also using PL/SQL,


BEGIN
   fnd_program.delete_executable (executable_short_name   =>'XXPMSJB',
                                  application          =>'XXPMS');
   COMMIT;
END;

SQL Developer: Set default date format to date and time

SQL Developer default display date field data without time:

We can set this default date format to date and time:

• Open SQL Developer, Open Menu Tools => Preferences
• From the Preferences dialog, Select option Database=> Click NLS
• NLS paremeters will display right side
• Update the "Date Format" parameter value to "DD-MON-RR HH24:MI:SS"
• Click "Ok" button to save and close the preference dialog

You have done, Now run the select statement and see the date filed values.

Terminate Employees using API

Following API's are used to terminating employees:

hr_ex_employee_api.actual_termination_emp
hr_ex_employee_api.update_term_details_emp
hr_ex_employee_api.final_process_emp

The API's have to be executed in the above order:


Example Code:

Declare
   Cursor c_emp_cur Is
         Select ppos.period_of_service_id
               ,ppos.object_version_number
               ,papf.person_type_id
               ,a.end_date
         From per_all_people_f papf
             ,per_periods_of_service ppos
             ,xxhr_resign_people_interface a
         Where papf.person_id = ppos.person_id
         And sysdate Between papf.effective_start_date and papf.effective_end_date
         And sysdate between ppos.date_start And coalesce(ppos.projected_termination_date,actual_termination_date,sysdate)
         And papf.business_group_id = 700
         And a.person_id = papf.person_id
         And a.end_date < '01-apr-2013';
   l_validate Boolean := False;
   l_period_of_service_id Number;
   l_object_version_number Number;
   l_actual_notice_period_date Date;
   l_effective_date date;
   l_supervisor_warning Boolean;
   l_event_warning Boolean;
   l_interview_warning Boolean;
   l_review_warning Boolean;
   l_recruiter_warning Boolean;
   l_asg_future_changes_warning Boolean;
   l_f_asg_future_changes_warning Boolean;
   l_pay_proposal_warning Boolean;
   l_dod_warning Boolean;
   l_final_process_date Date;
   l_org_now_no_manager_warning Boolean;
   l_entries_changed_warning Varchar2(255);
   l_f_entries_changed_warning Varchar2(255);
   l_alu_change_warning Varchar2(255);
   l_person_type_id Number;
   l_last_std_process_date_out Date;
For c_emp_rec In c_emp_cur
  Loop
     l_period_of_service_id := c_emp_rec.period_of_service_id;
     l_object_version_number := c_emp_rec.object_version_number;
     l_actual_notice_period_date := c_emp_rec.end_date;
     l_effective_date := c_emp_rec.end_date;
     l_person_type_id := c_emp_rec.person_type_id;
     Begin
     hr_ex_employee_api.actual_termination_emp
           (p_validate => l_validate
           ,p_effective_date => l_effective_date
           ,p_period_of_service_id => l_period_of_service_id
           ,p_object_version_number => l_object_version_number
           ,p_actual_termination_date => l_actual_notice_period_date
           ,p_last_standard_process_date => l_actual_notice_period_date
     --      ,p_person_type_id => l_person_type_id
           ,p_leaving_reason => 'RESS'
           ,p_last_std_process_date_out => l_last_std_process_date_out
           ,p_supervisor_warning => l_supervisor_warning
           ,p_event_warning => l_event_warning
           ,p_interview_warning => l_interview_warning
           ,p_review_warning => l_review_warning
           ,p_recruiter_warning => l_recruiter_warning
           ,p_asg_future_changes_warning => l_asg_future_changes_warning
           ,p_entries_changed_warning => l_entries_changed_warning
           ,p_pay_proposal_warning => l_pay_proposal_warning
           ,p_dod_warning => l_dod_warning
           ,p_alu_change_warning => l_alu_change_warning
           );
     hr_ex_employee_api.update_term_details_emp
          (p_validate    => l_validate
          ,p_effective_date          => l_effective_date
          ,p_period_of_service_id        => l_period_of_service_id
          ,p_object_version_number      => l_object_version_number
          ,p_accepted_termination_date  => l_effective_date
          ,p_leaving_reason           => 'RESS'
          ,p_notified_termination_date   => l_effective_date
          ,p_projected_termination_date  => l_effective_date
          );
     l_final_process_date := l_effective_date;

 hr_ex_employee_api.final_process_emp

         (p_validate  => l_validate

         ,p_period_of_service_id  => l_period_of_service_id

         ,p_object_version_number => l_object_version_number

         ,p_final_process_date    => l_final_process_date

         ,p_org_now_no_manager_warning => l_org_now_no_manager_warning

         ,p_asg_future_changes_warning => l_f_asg_future_changes_warning

         ,p_entries_changed_warning => l_f_entries_changed_warning

         );

     Exception

        When Others Then null;

     End;

  End Loop;

  commit;

End;

Cleaning FND_NODES Tables

Cleaning FND_NODES table

To clean the FND_NODES table use the following:

1. Shutdown all the services.
2. EXEC FND_CONC_CLONE.SETUP_CLEAN;
COMMIT;
EXIT;

run autoconfig on all the nodes starting with DB node.