create or replace PACKAGE  XX_AVI_OOO_CD
AS
  PROCEDURE Enter_OOO_CD (
     p_employee_number   VARCHAR2,
     p_email_address     VARCHAR2,
     p_contact_number   VARCHAR2,
     p_return_date       VARCHAR2,
     p_instruct_1        VARCHAR2 DEFAULT NULL,
     p_instruct_2        VARCHAR2 DEFAULT NULL,
     p_instruct_3        VARCHAR2 DEFAULT NULL,
     p_instruct_4        VARCHAR2 DEFAULT NULL
     );
END XX_AVI_OOO_CD;  
/
create or replace PACKAGE BODY   XX_AVI_OOO_CD
AS
  PROCEDURE Enter_OOO_CD (
     p_employee_number   VARCHAR2,
     p_email_address     VARCHAR2,
     p_contact_number   VARCHAR2,
     p_return_date       VARCHAR2,
     p_instruct_1        VARCHAR2 DEFAULT NULL,
     p_instruct_2        VARCHAR2 DEFAULT NULL,
     p_instruct_3        VARCHAR2 DEFAULT NULL,
     p_instruct_4        VARCHAR2 DEFAULT NULL   
  )
  IS
     l_person_extra_info_id    NUMBER;
     l_object_version_number   NUMBER;
     l_information_type        VARCHAR2 (100) := 'XX_AVI_OOO_CD'; /* This is the EIT Code*/
     l_person_id               NUMBER;
     l_error_flag             boolean  := false;
     e_no_emp_no             EXCEPTION;
     e_no_details                EXCEPTION;
     e_invalid_emp             exception;
  BEGIN
     IF p_employee_number IS NULL
     THEN
        l_error_flag := true;
        RAISE e_no_emp_no;
     ELSE
        BEGIN
           SELECT person_id
             INTO l_person_id
             FROM per_all_people_f
            WHERE employee_number = p_employee_number
              AND business_group_id = fnd_global.per_business_group_id
              AND TRUNC (SYSDATE) BETWEEN effective_start_date
                                      AND effective_end_date;
        EXCEPTION
           WHEN NO_DATA_FOUND
           THEN
              l_error_flag := true;
              RAISE e_invalid_emp;
        END;
     END IF;
     
     IF p_email_address IS NULL OR p_contact_number IS NULL OR p_return_date IS NULL
     THEN
        l_error_flag := true;
        RAISE e_no_details;
     END IF;

     IF l_error_flag = false
     THEN
        hr_person_extra_info_api.create_person_extra_info
                         (p_validate                      => FALSE,
                          p_person_id                     => l_person_id,
                          p_information_type              => l_information_type,
                          p_pei_information_category      => l_information_type,
                          p_pei_information1              => p_email_address,
                          p_pei_information2              => p_contact_number,
                          p_pei_information3              => p_return_date,
                          p_person_extra_info_id          => l_person_extra_info_id,
                          p_object_version_number         => l_object_version_number
                         );
     END IF;
  EXCEPTION
     WHEN e_no_emp_no
     THEN
        raise_application_error (-20000, 'Please Enter the employee number, it can not be null'
                                );
     WHEN e_invalid_emp
     THEN
        raise_application_error (-20000, 'Oops!! Employee number you have entered does not exist in the system');
     WHEN e_no_details
     THEN
        raise_application_error
            (-20000, 'Please check and confirm Employee Number, Employee Email Address, Contact Number and Return Date all the mebdatory field.');
  END Enter_OOO_CD;
END XX_AVI_OOO_CD; 
/