API to Create Contact Relationship

CREATE OR REPLACE PROCEDURE xxx_create_contact_relation_p

IS

   CURSOR get_details

   IS

      SELECT *

        FROM xxx_employee_dependent_t

       WHERE process_flag = 'N';

   l_business_group_id_gl       NUMBER := 81;

   l_err_msg                    VARCHAR2 (500) := NULL;

   l_object_version_number      NUMBER (9);

   l_effective_start_date       DATE;

   l_effective_end_date         DATE;

   l_full_name                  VARCHAR2 (100);

   l_comment_id                 NUMBER (15);

   l_name_combination_warning   BOOLEAN;

   l_orig_hire_warning          BOOLEAN;

   l_assign_payroll_warning     BOOLEAN;

   l_contact_relationship_id    NUMBER := NULL;

   l_per_person_id              NUMBER := NULL;

BEGIN

   --Initialize Session

   fnd_global.

    apps_initialize (user_id => 2234,                         -- xxx_MIGRATION

                                     resp_id => 50637,     -- xxx HRMS Manager

                                                      resp_appl_id => 800 -- Human Resouces

                                                                         );

   FOR fetch_details IN get_details

   LOOP

      l_err_msg := NULL;

      l_object_version_number := NULL;

      l_effective_start_date := NULL;

      l_effective_end_date := NULL;

      l_full_name := NULL;

      l_comment_id := NULL;

      l_name_combination_warning := NULL;

      l_orig_hire_warning := NULL;

      l_assign_payroll_warning := NULL;

      l_contact_relationship_id := NULL;

      l_per_person_id := NULL;

      BEGIN

         hr_contact_rel_api.

          create_contact (

            p_validate                    => FALSE,

            p_start_date                  => fetch_details.person_effective_date,

            p_business_group_id           => l_business_group_id_gl,

            p_person_id                   => fetch_details.person_id,

            p_contact_type                => fetch_details.relation,

            p_primary_contact_flag        => 'N',

            p_date_start                  => fetch_details.dep_effective_date,

            p_personal_flag               => 'Y',

            p_sequence_number             => fetch_details.seq,

            p_dependent_flag              => 'Y',       -- Dependent Check Box

            p_beneficiary_flag            => 'Y',      --Beneficiary Check Box

            p_last_name                   => '.',

            p_sex                         => fetch_details.gender,

            p_date_of_birth               => fetch_details.date_of_birth,

            p_first_name                  => fetch_details.contact_name,

            p_marital_status              => fetch_details.marital_status,

            p_contact_relationship_id     => l_contact_relationship_id,

            p_ctr_object_version_number   => l_object_version_number,

            p_per_person_id               => l_per_person_id,

            p_per_object_version_number   => l_object_version_number,

            p_per_effective_start_date    => l_effective_start_date,

            p_per_effective_end_date      => l_effective_end_date,

            p_full_name                   => l_full_name,

            p_per_comment_id              => l_comment_id,

            p_name_combination_warning    => l_name_combination_warning,

            p_orig_hire_warning           => l_orig_hire_warning);

         UPDATE xxx_employee_dependent_t ded

            SET ded.process_flag = 'Y', ded.err_msg = NULL

          WHERE ded.depnd_id = fetch_details.depnd_id;

         COMMIT;

      EXCEPTION

         WHEN OTHERS

         THEN

            l_err_msg := SQLERRM;

            UPDATE xxx_employee_dependent_t ded

               SET ded.process_flag = 'N', ded.err_msg = l_err_msg

             WHERE ded.depnd_id = fetch_details.depnd_id;

            COMMIT;

      END;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      l_err_msg := SQLERRM;

      DBMS_OUTPUT.put_line ('Main Exception: ' || l_err_msg);

END xxx_create_contact_relation_p;