API to Create Employee Contracts in Oracle HRMS

Step1: Create Temporary Table to store employee contract details
"xx_contracts_temp"

Step2: Create Custom Procedure to Call API


CREATE OR REPLACE PROCEDURE xx_create_emp_contract
IS
   CURSOR c_contract
   IS
      SELECT pap.employee_number, pap.person_id, con.REFERENCE, con.TYPE,
             con.status, con.DURATION, con.units, con.profession,
             con.employment_status, con.moc_attribute1, con.error_message,
             con.process_flag, con.contract_start_date
        FROM xx_contracts_temp con, per_all_people_f pap
       WHERE pap.employee_number = con.employee_number
         AND TRUNC (SYSDATE) BETWEEN pap.effective_start_date
                                 AND pap.effective_end_date;

   lc_c_contract             c_contract%ROWTYPE;
   l_contract_id             NUMBER;
   l_effective_start_date    DATE;
   l_effective_end_date      DATE;
   l_object_version_number   NUMBER;
   error_desc                VARCHAR2 (240);
   lv_contract_flag          CHAR (1);
   l_seq                     VARCHAR2 (20);
   v_type                    VARCHAR2 (80);
   v_status                  VARCHAR2 (30);
   v_emp_status              VARCHAR2 (80);
BEGIN
   OPEN c_contract;

   LOOP
      FETCH c_contract
       INTO lc_c_contract;

      EXIT WHEN c_contract%NOTFOUND;

      /***************Column Values Validation******************************/
      BEGIN
         SELECT lookup_code
           INTO v_type
           FROM fnd_lookup_values_vl
          WHERE lookup_type = 'CONTRACT_TYPE' AND meaning = lc_c_contract.TYPE;
      EXCEPTION
         WHEN OTHERS
         THEN
            error_desc := 'Invalid Contract Type-' || SQLERRM;

            UPDATE xx_contracts_temp
               SET error_message = error_desc,
                   process_flag = 'N'
             WHERE employee_number = lc_c_contract.employee_number;
      END;

      BEGIN
         SELECT lookup_code
           INTO v_status
           FROM fnd_lookup_values_vl
          WHERE lookup_type = 'CONTRACT_STATUS'
            AND meaning = lc_c_contract.status;
      EXCEPTION
         WHEN OTHERS
         THEN
            error_desc := 'Invalid Status-' || SQLERRM;

            UPDATE xx_contracts_temp
               SET error_message = error_desc,
                   process_flag = 'N'
             WHERE employee_number = lc_c_contract.employee_number;
      END;

      BEGIN
         SELECT lookup_code
           INTO v_emp_status
           FROM fnd_lookup_values_vl
          WHERE lookup_type = 'SA_EMPLOYMENT_STATUS'
            AND meaning = lc_c_contract.employment_status;
      EXCEPTION
         WHEN OTHERS
         THEN
            error_desc := 'Invalid Employment Status-' || SQLERRM;

            UPDATE xx_contracts_temp
               SET error_message = error_desc,
                   process_flag = 'N'
             WHERE employee_number = lc_c_contract.employee_number;
      END;

/*============================API Starts===================================*/
      BEGIN
         hr_contract_api.create_contract
                        (p_validate                   => FALSE,
                         p_effective_date             => lc_c_contract.contract_start_date,
                         p_person_id                  => lc_c_contract.person_id,
                         p_reference                  => lc_c_contract.REFERENCE,
                         p_type                       => v_type,
                         p_status                     => v_status,
                         p_duration                   => lc_c_contract.DURATION,
                         p_duration_units             => lc_c_contract.units,
                         p_contractual_job_title      => lc_c_contract.profession,
                         --p_ctr_information_category      => 'SA',
                         p_ctr_information1           => v_emp_status,
                         p_attribute1                 => lc_c_contract.moc_attribute1,
                         p_contract_id                => l_contract_id,
                         p_effective_start_date       => l_effective_start_date,
                         p_effective_end_date         => l_effective_end_date,
                         p_object_version_number      => l_object_version_number
                        );

         IF l_contract_id IS NOT NULL
         THEN
            lv_contract_flag := 'Y';
            error_desc := 'NO ERROR';
         END IF;

         IF lv_contract_flag = 'Y'
         THEN
            UPDATE xx_contracts_temp
               SET error_message = error_desc,
                   process_flag = lv_contract_flag
             WHERE employee_number = lc_c_contract.employee_number;
         ELSE
            error_desc := error_desc || SQLERRM;

            UPDATE xx_contracts_temp
               SET error_message = error_desc,
                   process_flag = 'N'
             WHERE employee_number = lc_c_contract.employee_number;
         END IF;

         DBMS_OUTPUT.put_line ('Contracts Uploaded Successfully:! ');
         DBMS_OUTPUT.put_line ('CONTRACT ID    : ' || l_contract_id);
      EXCEPTION
         WHEN OTHERS
         THEN
            lv_contract_flag := 'N';
            error_desc := error_desc || SQLERRM;

            UPDATE xx_contracts_temp
               SET error_message = error_desc,
                   process_flag = lv_contract_flag
             WHERE employee_number = lc_c_contract.employee_number;

            DBMS_OUTPUT.put_line (SQLERRM);
      END;

      l_contract_id := '';
      l_object_version_number := '';
      l_effective_start_date := '';
      l_effective_end_date := '';
      error_desc := '';
      lv_contract_flag := '';
   --COMMIT;
   END LOOP;

   CLOSE c_contract;
--DBMS_OUTPUT.PUT_LINE('LOCATION ID        : '||L_LOCATION_ID);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
END;
/