Wednesday, February 1, 2017

Employee / Contingent Worker Termination API (Sample Script)


EMPLOYEE Termination

DECLARE
   --
   --Common Variables
   l_terminate_emp_flag             VARCHAR2 (1)                                             := 'N';
   l_terminate_msg                  VARCHAR2 (600);
   l_person_id                      NUMBER                                                   := 533285;
   l_le_terminate_emp_exception     EXCEPTION;
   --- DECLARE variables for HR_EX_EMPLOYEE_WORKER_API.actual_termination_emp
   --- IN variables
   l_effective_date                 DATE;
   l_termination_reason             per_periods_of_service.leaving_reason%TYPE               := 'XXSD_RESG5';
   -- HR_LOOKUPS (TYPE: LEAV_REAS)
   l_person_type_id                 per_person_types.person_type_id%TYPE                     := 1130;                                                                   -- 'EX_EMP'
   l_period_of_service_id           per_periods_of_service.period_of_service_id%TYPE;
   l_actual_termination_date        per_periods_of_service.actual_termination_date%TYPE      := TRUNC (SYSDATE);
   l_last_standard_process_date     per_periods_of_service.last_standard_process_date%TYPE   := TRUNC (SYSDATE + 10);
   l_object_version_number          per_periods_of_service.object_version_number%TYPE;
   l_start_date                     per_periods_of_service.date_start%TYPE;
   l_notif_term_date                DATE;
   --- OUT variables
   l_supervisor_warning             BOOLEAN                                                  := FALSE;
   l_event_warning                  BOOLEAN                                                  := FALSE;
   l_interview_warning              BOOLEAN                                                  := FALSE;
   l_review_warning                 BOOLEAN                                                  := FALSE;
   l_recruiter_warning              BOOLEAN                                                  := FALSE;
   l_asg_future_changes_warning     BOOLEAN                                                  := FALSE;
   l_entries_changed_warning        VARCHAR2 (300);
   l_pay_proposal_warning           BOOLEAN                                                  := FALSE;
   l_dod_warning                    BOOLEAN                                                  := FALSE;
   l_alu_change_warning             VARCHAR2 (300);
   --- DECLARE variables for HR_EX_EMPLOYEE_WORKER_API.final_process_emp
   --- IN variables
   l_final_process_date             per_periods_of_service.final_process_date%TYPE;
   --- OUT variables
   l_org_now_no_manager_warning     BOOLEAN                                                  := FALSE;
   l_f_asg_future_changes_warning   BOOLEAN                                                  := FALSE;
   l_f_entries_changed_warning      VARCHAR2 (300);
--
BEGIN
   --
   BEGIN
      SELECT pos.period_of_service_id,
             pos.object_version_number,
             date_start
        INTO l_period_of_service_id,
             l_object_version_number,
             l_start_date
        FROM per_periods_of_service pos
       WHERE pos.person_id = l_person_id;
   EXCEPTION
      WHEN OTHERS
      THEN
         l_terminate_msg    := 'Error while selecting employee details : ' || SUBSTR (SQLERRM, 1, 150);
         RAISE l_le_terminate_emp_exception;
   END;

   --
   SAVEPOINT terminate_employee_s1;

   --
   BEGIN
      /*
      * This API terminates an employee.
      * This API converts a person of type Employee >to a person of type
      * Ex-Employee. The person's period of service and any employee assignments are ended.
      */
      hr_ex_employee_api.actual_termination_emp (p_validate                        => FALSE,
                                                 p_effective_date                  => TRUNC (SYSDATE),
                                                 p_period_of_service_id            => l_period_of_service_id,
                                                 p_object_version_number           => l_object_version_number,
                                                 p_actual_termination_date         => l_actual_termination_date,
                                                 p_last_standard_process_date      => l_last_standard_process_date,
                                                 p_person_type_id                  => l_person_type_id,
                                                 p_leaving_reason                  => l_termination_reason,
                                                 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
                                                );

      IF l_object_version_number IS NULL
      THEN
         l_terminate_emp_flag    := 'N';
         l_terminate_msg         := 'Warning validating API: hr_ex_employee_api.actual_termination_emp';
         RAISE l_le_terminate_emp_exception;
      END IF;

      l_terminate_emp_flag    := 'Y';
   EXCEPTION
      WHEN OTHERS
      THEN
         l_terminate_msg    := 'Error validating API: hr_ex_employee_api.actual_termination_emp : ' || SUBSTR (SQLERRM, 1, 150);
         RAISE l_le_terminate_emp_exception;
   END;

   IF l_terminate_emp_flag = 'Y'
   THEN
      BEGIN
         IF l_start_date > TRUNC (SYSDATE)
         THEN
            l_notif_term_date    := l_start_date + 1;
         ELSE
            l_notif_term_date    := TRUNC (SYSDATE);
         END IF;

         /*
         * This API updates employee termination information.
         * The ex-employee must exist in the relevant business group
         */
         apps.hr_ex_employee_api.update_term_details_emp (p_validate                        => FALSE,
                                                          p_effective_date                  => TRUNC (SYSDATE),
                                                          p_period_of_service_id            => l_period_of_service_id,
                                                          p_notified_termination_date       => l_notif_term_date,
                                                          p_projected_termination_date      => l_notif_term_date,
                                                          p_object_version_number           => l_object_version_number
                                                         );
      EXCEPTION
         WHEN OTHERS
         THEN
            l_terminate_msg         := 'Error validating API: hr_ex_employee_api.update_term_details_emp : ' || SUBSTR (SQLERRM, 1, 1500);
            l_terminate_emp_flag    := 'N';
            RAISE l_le_terminate_emp_exception;
      END;

      BEGIN
         /*
         * This API set the final process date for a terminated employee.
         * This API covers the second step in terminating a period of service and all
         * current assignments for an employee. It updates the period of service
         * details and date-effectively deletes all employee assignments as of the final process date.
         */
         apps.hr_ex_employee_api.final_process_emp (p_validate                        => FALSE,
                                                    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
            l_terminate_msg    := 'Error validating API: hr_ex_employee_api.final_process_emp : ' || SUBSTR (SQLERRM, 1, 150);
            RAISE l_le_terminate_emp_exception;
      END;
   END IF;

   --
   COMMIT;
--
EXCEPTION
   WHEN l_le_terminate_emp_exception
   THEN
      DBMS_OUTPUT.put_line (l_terminate_msg);
      ROLLBACK TO terminate_employee_s1;
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Terminate Employee. Error OTHERS while validating: ' || SQLERRM);
      ROLLBACK TO terminate_employee_s1;

END;

CONTINGENT WORKER Termination

DECLARE
   --
   --Common Variables
   l_terminate_cwk_flag           VARCHAR2 (1)                                               := 'N';
   l_terminate_msg                VARCHAR2 (600);
   l_person_id                    NUMBER                                                     := 533485;
   l_le_terminate_cwk_exception   EXCEPTION;
   --- DECLARE variables for hr_contingent_worker_api.actual_termination_placement
   --- IN variables
   l_effective_date               DATE                                                       := TRUNC (SYSDATE);
   l_termination_reason           per_periods_of_placement.termination_reason%TYPE           := 'EP';
   -- select * from hr_lookups where lookup_type = 'HR_CWK_TERMINATION_REASONS'
   l_person_type_id               per_person_types.person_type_id%TYPE                       := 1129;                                                                     -- EX_CWK
   l_period_of_placement_id       per_periods_of_placement.period_of_placement_id%TYPE;
   l_actual_termination_date      per_periods_of_placement.actual_termination_date%TYPE      := TRUNC (SYSDATE);
   l_last_standard_process_date   per_periods_of_placement.last_standard_process_date%TYPE   := TRUNC (SYSDATE + 10);
   l_object_version_number        per_periods_of_placement.object_version_number%TYPE;
   l_start_date                   per_periods_of_placement.date_start%TYPE;
   l_notif_term_date              DATE;
   --- OUT variables
   l_supervisor_warning           BOOLEAN                                                    := FALSE;
   l_event_warning                BOOLEAN                                                    := FALSE;
   l_interview_warning            BOOLEAN                                                    := FALSE;
   l_review_warning               BOOLEAN                                                    := FALSE;
   l_recruiter_warning            BOOLEAN                                                    := FALSE;
   l_asg_future_changes_warning   BOOLEAN                                                    := FALSE;
   l_entries_changed_warning      VARCHAR2 (300);
   l_pay_proposal_warning         BOOLEAN                                                    := FALSE;
   l_dod_warning                  BOOLEAN                                                    := FALSE;
   --- DECLARE variables for hr_contingent_worker_api.final_process_placement
   --- IN variables
   l_final_process_date           per_periods_of_service.final_process_date%TYPE;
   --- OUT variables
   l_org_now_no_manager_warning   BOOLEAN                                                    := FALSE;
--
BEGIN
   --
   BEGIN
      SELECT pos.period_of_placement_id,
             pos.object_version_number,
             date_start
        INTO l_period_of_placement_id,
             l_object_version_number,
             l_start_date
        FROM per_periods_of_placement pos
       WHERE pos.person_id = l_person_id;
   EXCEPTION
      WHEN OTHERS
      THEN
         l_terminate_msg    := 'Error while selecting cwk details : ' || SUBSTR (SQLERRM, 1, 150);
         RAISE l_le_terminate_cwk_exception;
   END;

   --
   SAVEPOINT terminate_cwk_s1;

   --
   BEGIN
      /*
      This API covers the first step in terminating a period of placement and
      all current assignments for a cwk, identified by person_id and date_start.
      You can use the API to set the actual termination date, the last standard
      process date, the new assignment status and the new person type
      */
      hr_contingent_worker_api.actual_termination_placement (p_validate                        => FALSE,
                                                             p_effective_date                  => l_effective_date,
                                                             p_person_id                       => l_person_id,
                                                             p_date_start                      => l_start_date,
                                                             p_person_type_id                  => l_person_type_id,
                                                             p_actual_termination_date         => l_actual_termination_date,
                                                             p_termination_reason              => l_termination_reason
                                                                                                                      --In/Out
      ,
                                                             p_object_version_number           => l_object_version_number,
                                                             p_last_standard_process_date      => l_last_standard_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
                                                            );

      IF l_object_version_number IS NULL
      THEN
         l_terminate_cwk_flag    := 'N';
         l_terminate_msg         := 'Warning validating API: hr_contingent_worker_api.actual_termination_placement';
         RAISE l_le_terminate_cwk_exception;
      END IF;

      l_terminate_cwk_flag    := 'Y';
   EXCEPTION
      WHEN OTHERS
      THEN
         l_terminate_msg    := 'Error validating API: hr_contingent_worker_api.actual_termination_placement : ' || SUBSTR (SQLERRM, 1, 150);
         RAISE l_le_terminate_cwk_exception;
   END;                                                                                                                      --hr_contingent_worker_api.actual_termination_placement

   IF l_terminate_cwk_flag = 'Y'
   THEN
      BEGIN
         /*
         This API covers the second step in terminating a period of placement and
         all current assignments for an cwk.  It updates the period of placement
         details and date-effectively deletes all the contingent worker assignments
         as of the final process date.
         */
         hr_contingent_worker_api.final_process_placement (p_validate                        => FALSE,
                                                           p_person_id                       => l_person_id,
                                                           p_date_start                      => l_start_date
                                                                                                            --In/Out
         ,
                                                           p_object_version_number           => l_object_version_number,
                                                           p_final_process_date              => l_final_process_date
                                                                                                                    --Out
         ,
                                                           p_org_now_no_manager_warning      => l_org_now_no_manager_warning,
                                                           p_asg_future_changes_warning      => l_asg_future_changes_warning,
                                                           p_entries_changed_warning         => l_entries_changed_warning
                                                          );
      EXCEPTION
         WHEN OTHERS
         THEN
            l_terminate_msg    := 'Error validating API: hr_contingent_worker_api.final_process_placement : ' || SUBSTR (SQLERRM, 1, 150);
            RAISE l_le_terminate_cwk_exception;
      END;                                                                                                                        --hr_contingent_worker_api.final_process_placement
   END IF;

   --
   COMMIT;
--
EXCEPTION
   WHEN l_le_terminate_cwk_exception
   THEN
      DBMS_OUTPUT.put_line (l_terminate_msg);
      ROLLBACK TO terminate_cwk_s1;
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Terminate CWK. Error OTHERS while validating: ' || SQLERRM);
      ROLLBACK TO terminate_cwk_s1;

END;

1 comment: