Friday, August 8, 2014

Create Person Party and Org Contact for a Organization Pary in Oracle R12

 --It is always a good idea to initialize apps and set global policy context before using standard API

CREATE OR REPLACE PROCEDURE create_person_party (
   p_in_first_name                IN       VARCHAR2,
   p_in_org_party_id              IN       VARCHAR2,
   p_in_party_number              IN       VARCHAR2,
   p_in_cdh_person_party_number   IN       VARCHAR2,
   x_ou_ebs_per_party_id          OUT      VARCHAR2,
   x_ou_org_contact_id            OUT      VARCHAR2
)
IS
   v_person_rec         hz_party_v2pub.person_rec_type;
   v_org_contact_rec    hz_party_contact_v2pub.org_contact_rec_type;
   x_ou_party_rel_id    NUMBER;
   x_ou_party_id        NUMBER;
   x_ou_party_number    VARCHAR2 (2000);
   x_ou_profile_id      NUMBER;
   x_ou_return_status   VARCHAR2 (2000);
   x_ou_msg_count       NUMBER;
   x_ou_msg_data        VARCHAR2 (2000);
   v_message            VARCHAR2 (5000);
   v_err_message        VARCHAR2 (5000);
   v_ebs_per_party_id   NUMBER                                      := NULL;  --Party ID of the relationship
   v_org_contact_id     NUMBER                                      := NULL;
BEGIN
   fnd_msg_pub.delete_msg (NULL);
   fnd_msg_pub.initialize;
   v_person_rec.person_first_name := p_in_first_name;
   v_person_rec.created_by_module := 'TCA_V2_API';
   hz_party_v2pub.create_person (p_init_msg_list      => 'T',
                                 p_person_rec         => v_person_rec,
                                 x_party_id           => x_ou_party_id,
                                 x_party_number       => x_ou_party_number,
                                 x_profile_id         => x_ou_profile_id,
                                 x_return_status      => x_ou_return_status,
                                 x_msg_count          => x_ou_msg_count,
                                 x_msg_data           => x_ou_msg_data
                                );

   IF x_ou_return_status <> 'S'
   THEN
      FOR j IN 1 .. x_ou_msg_count
      LOOP
         fnd_msg_pub.get (j, fnd_api.g_false, x_ou_msg_data, v_message);
         v_err_message :=
              v_err_message
              || ('Msg' || TO_CHAR (j) || ': ' || x_ou_msg_data);
      END LOOP;

      DBMS_OUTPUT.put_line ('v_err_message: ' || v_err_message);
   ELSE
      v_ebs_per_party_id := x_ou_party_id;
      v_org_contact_rec.created_by_module := 'TCA_V2_API';
      v_org_contact_rec.party_rel_rec.subject_id := v_ebs_per_party_id;
      v_org_contact_rec.party_rel_rec.subject_type := 'PERSON';
      v_org_contact_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
      v_org_contact_rec.party_rel_rec.object_id := p_in_org_party_id;
      v_org_contact_rec.party_rel_rec.object_type := 'ORGANIZATION';
      v_org_contact_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
      v_org_contact_rec.party_rel_rec.relationship_code := 'CONTACT_OF';
      v_org_contact_rec.party_rel_rec.relationship_type := 'CONTACT';
      v_org_contact_rec.party_rel_rec.start_date := SYSDATE;
      v_org_contact_rec.party_rel_rec.status := 'A';
      hz_party_contact_v2pub.create_org_contact ('T',
                                                 v_org_contact_rec,
                                                 x_ou_org_contact_id,
                                                 x_ou_party_rel_id,
                                                 x_ou_party_id,
                                                 x_ou_party_number,
                                                 x_ou_return_status,
                                                 x_ou_msg_count,
                                                 x_ou_msg_data
                                                );
      COMMIT;

      IF x_ou_return_status <> 'S'
      THEN
         FOR j IN 1 .. x_ou_msg_count
         LOOP
            fnd_msg_pub.get (j, fnd_api.g_false, x_ou_msg_data, v_message);
            v_err_message :=
                  v_err_message
               || ('Msg' || TO_CHAR (j) || ': ' || x_ou_msg_data);
         END LOOP;

         DBMS_OUTPUT.put_line ('v_err_message: ' || v_err_message);
      ELSE
         DBMS_OUTPUT.put_line ('Org Contact ID = ' || x_ou_org_contact_id);
         DBMS_OUTPUT.put_line (' Party Relationship id = ' || x_ou_party_id);
         v_ebs_per_party_id := x_ou_party_id;
         v_org_contact_id := x_ou_org_contact_id;
      END IF;
   END IF;

   x_ou_org_contact_id := v_org_contact_id;
   x_ou_ebs_per_party_id := v_ebs_per_party_id;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
END create_person_party;
Reactions:

0 comments:

Post a Comment