Friday 24 June 2011

Supplier API

SUPPLIER CREATION

DECLARE
   p_api_version        NUMBER;
   p_init_msg_list      VARCHAR2 (200);
   p_commit             VARCHAR2 (200);
   p_validation_level   NUMBER;
   x_return_status      VARCHAR2 (200);
   x_msg_count          NUMBER;
   x_msg_data           VARCHAR2 (200);
   p_vendor_rec         apps.ap_vendor_pub_pkg.r_vendor_rec_type;
   x_vendor_id          NUMBER;
   x_party_id           NUMBER;
   V_MSG_INDEX_OUT      NUMBER;
BEGIN
   p_api_version := 1.0;
   p_init_msg_list :=FND_API.G_TRUE;
   p_commit := FND_API.G_TRUE;
   p_validation_level := FND_API.G_VALID_LEVEL_FULL;
   x_return_status := NULL;
   x_msg_count := NULL;
   x_msg_data := NULL;
   p_vendor_rec.vendor_name := 'A.V.K';
   p_vendor_rec.vendor_type_lookup_code  := 'Internal';
   p_vendor_rec.women_owned_flag := 'N';
   p_vendor_rec.small_business_flag := 'Y'; 
   p_vendor_rec.segment1 :='865'; --(insert non duplicate number)
   x_vendor_id := NULL;
   x_party_id := NULL;
   apps.ap_vendor_pub_pkg.create_vendor (p_api_version,
                                         p_init_msg_list,
                                         p_commit,
                                         p_validation_level,
                                         x_return_status,
                                         x_msg_count,
                                         x_msg_data,
                                         p_vendor_rec,
                                         x_vendor_id,
                                         x_party_id
                                        );
   DBMS_OUTPUT.put_line ('X_RETURN_STATUS = ' || x_return_status);
   DBMS_OUTPUT.put_line ('X_MSG_COUNT = ' || TO_CHAR (x_msg_count));
   DBMS_OUTPUT.put_line ('X_MSG_DATA = ' || x_msg_data);
   DBMS_OUTPUT.put_line ('X_VENDOR_ID = ' || TO_CHAR (x_vendor_id));
   DBMS_OUTPUT.put_line ('X_PARTY_ID = ' || TO_CHAR (x_party_id));
   DBMS_OUTPUT.put_line ('');
   COMMIT;

  IF x_return_status = 'S' THEN
    COMMIT;
  ELSE
    ROLLBACK;
  END IF;
  IF x_msg_count > 0 THEN
    FOR v_index IN 1 .. x_msg_count
    LOOP
      fnd_msg_pub.get (p_msg_index => v_index, p_encoded => 'F', p_data => x_msg_data, p_msg_index_out => v_msg_index_out);
      x_msg_data := SUBSTR (x_msg_data, 1, 200);
      DBMS_OUTPUT.put_line (x_msg_data);
    
    END LOOP;
  END IF;
END;

SUPPLIER SITE CREATION

DECLARE
   p_api_version        NUMBER;
   p_init_msg_list      VARCHAR2 (200);
   p_commit             VARCHAR2 (200);
   p_validation_level   NUMBER;
   x_return_status      VARCHAR2 (200);
   x_msg_count          NUMBER;
   x_msg_data           VARCHAR2 (200);
   p_vendor_site_rec    apps.ap_vendor_pub_pkg.r_vendor_site_rec_type;
   x_vendor_site_id     NUMBER;
   x_party_site_id      NUMBER;
   x_location_id        NUMBER;
   v_msg_index_out    NUMBER;
BEGIN
   p_api_version := 1.0;
   p_init_msg_list := FND_API.G_TRUE;
   p_commit := FND_API.G_TRUE;
   p_validation_level := FND_API.G_VALID_LEVEL_FULL;
   x_return_status := NULL;
   x_msg_count := NULL;
   x_msg_data := NULL;
   P_VENDOR_SITE_REC.AREA_CODE := '';
   P_VENDOR_SITE_REC.PHONE := '';
   P_VENDOR_SITE_REC.FAX_AREA_CODE := '';
   P_VENDOR_SITE_REC.FAX := '';
   P_VENDOR_SITE_REC.VENDOR_ID :=89183;
   P_VENDOR_SITE_REC.VENDOR_SITE_CODE := 'HOME';
   P_VENDOR_SITE_REC.ORG_ID := 204;
   P_VENDOR_SITE_REC.address_line1 := 'Ammam Kovil Street';
   P_VENDOR_SITE_REC.address_line2 :='Vadapalani';
   P_VENDOR_SITE_REC.address_line3 := 'Chennai';     
   P_VENDOR_SITE_REC.org_name := 'Vision Operations';
   P_VENDOR_SITE_REC.country := 'US';       
   x_vendor_site_id := NULL;
   x_party_site_id := NULL;
   x_location_id := NULL;
   apps.ap_vendor_pub_pkg.create_vendor_site (p_api_version,
                                              p_init_msg_list,
                                              p_commit,
                                              p_validation_level,
                                              x_return_status,
                                              x_msg_count,
                                              x_msg_data,
                                              p_vendor_site_rec,
                                              x_vendor_site_id,
                                              x_party_site_id,
                                              x_location_id
                                             );
   DBMS_OUTPUT.put_line ('X_RETURN_STATUS = ' || x_return_status);
   DBMS_OUTPUT.put_line ('X_MSG_COUNT = ' || TO_CHAR (x_msg_count));
   DBMS_OUTPUT.put_line ('X_MSG_DATA = ' || x_msg_data);
   DBMS_OUTPUT.put_line ('X_VENDOR_SITE_ID = ' || TO_CHAR (x_vendor_site_id));
   DBMS_OUTPUT.put_line ('X_PARTY_SITE_ID = ' || TO_CHAR (x_party_site_id));
   DBMS_OUTPUT.put_line ('X_LOCATION_ID = ' || TO_CHAR (x_location_id));
   DBMS_OUTPUT.put_line ('');
   COMMIT;
  
 
  IF x_return_status = 'S' THEN
    COMMIT;
  ELSE
    ROLLBACK;
  END IF;
  IF x_msg_count > 0 THEN
    FOR v_index IN 1 .. x_msg_count
    LOOP
      fnd_msg_pub.get (p_msg_index => v_index, p_encoded => 'F', p_data => x_msg_data, p_msg_index_out => v_msg_index_out);
      x_msg_data := SUBSTR (x_msg_data, 1, 200);
      DBMS_OUTPUT.put_line (x_msg_data);
     
    END LOOP;
  END IF;

EXCEPTION
WHEN OTHERS THEN
      DBMS_OUTPUT.put_line ('SQL Error' || SQLERRM);
END;

SUPPLIER CONTACT CREATION

DECLARE
   p_api_version          NUMBER;
   p_init_msg_list        VARCHAR2 (200);
   p_commit               VARCHAR2 (200);
   p_validation_level     NUMBER;
   x_return_status        VARCHAR2 (200);
   x_msg_count            NUMBER;
   x_msg_data             VARCHAR2 (200);
   p_vendor_contact_rec   apps.ap_vendor_pub_pkg.r_vendor_contact_rec_type;
   x_vendor_contact_id    NUMBER;
   x_per_party_id         NUMBER;
   x_rel_party_id         NUMBER;
   x_rel_id               NUMBER;
   x_org_contact_id       NUMBER;
   x_party_site_id        NUMBER;
   v_msg_index_out          NUMBER;
BEGIN
   p_api_version := 1.0;
   p_init_msg_list := 'T';
   p_commit := 'T';
   p_validation_level := FND_API.G_VALID_LEVEL_FULL;
   x_return_status := NULL;
   x_msg_count := NULL;
   x_msg_data := NULL;
   p_vendor_contact_rec.vendor_contact_id := po_vendor_contacts_s.NEXTVAL;
   DBMS_OUTPUT.put_line ('po_vendor_contacts_s.NEXTVAL = ' || po_vendor_contacts_s.NEXTVAL);
   P_VENDOR_CONTACT_REC.vendor_site_id :=7953;
   P_VENDOR_CONTACT_REC.PERSON_FIRST_NAME := 'VIMAL';
   P_VENDOR_CONTACT_REC.PERSON_LAST_NAME := 'KUMAR';
   P_VENDOR_CONTACT_REC.PHONE           := '9840395136';
   P_VENDOR_CONTACT_REC.EMAIL_ADDRESS := 'vimalkumar.a@chain-sys.com';
   P_VENDOR_CONTACT_REC.URL := 'www.chain-sys.com';
   P_VENDOR_CONTACT_REC.org_id := 204;
   p_vendor_contact_rec.party_site_id  := 304651;
   p_vendor_contact_rec.org_party_site_id := 304651;
   p_vendor_contact_rec.VENDOR_ID := 89183;          
   x_vendor_contact_id := NULL;
   x_per_party_id := NULL;
   x_rel_party_id :=NULL;
   x_rel_id := NULL;
   x_org_contact_id := NULL;
   x_party_site_id := NULL;
   apps.ap_vendor_pub_pkg.create_vendor_contact (p_api_version,
                                                 p_init_msg_list,
                                                 p_commit,
                                                 p_validation_level,
                                                 x_return_status,
                                                 x_msg_count,
                                                 x_msg_data,
                                                 p_vendor_contact_rec,
                                                 x_vendor_contact_id,
                                                 x_per_party_id,
                                                 x_rel_party_id,
                                                 x_rel_id,
                                                 x_org_contact_id,
                                                 x_party_site_id
                                                );
   DBMS_OUTPUT.put_line ('X_RETURN_STATUS = ' || x_return_status);
   DBMS_OUTPUT.put_line ('X_MSG_COUNT = ' || TO_CHAR (x_msg_count));
   DBMS_OUTPUT.put_line ('X_MSG_DATA = ' || x_msg_data);
   DBMS_OUTPUT.put_line ('X_VENDOR_CONTACT_ID = ' || TO_CHAR (x_vendor_contact_id));
   DBMS_OUTPUT.put_line ('X_PER_PARTY_ID = ' || TO_CHAR (x_per_party_id));
   DBMS_OUTPUT.put_line ('X_REL_PARTY_ID = ' || TO_CHAR (x_rel_party_id));
   DBMS_OUTPUT.put_line ('X_REL_ID = ' || TO_CHAR (x_rel_id));
   DBMS_OUTPUT.put_line ('X_ORG_CONTACT_ID = ' || TO_CHAR (x_org_contact_id));
   DBMS_OUTPUT.put_line ('X_PARTY_SITE_ID = ' || TO_CHAR (x_party_site_id));
   DBMS_OUTPUT.put_line ('');
   COMMIT;

    IF x_return_status = 'S' THEN
    COMMIT;
  ELSE
    ROLLBACK;
  END IF;
  IF x_msg_count > 0 THEN
    FOR v_index IN 1 .. x_msg_count
    LOOP
      fnd_msg_pub.get (p_msg_index => v_index, p_encoded => 'F', p_data => x_msg_data, p_msg_index_out => v_msg_index_out);
      x_msg_data := SUBSTR (x_msg_data, 1, 200);
      DBMS_OUTPUT.put_line (x_msg_data);
     
    END LOOP;
  END IF;
END;

TABLES:


1.select * from po_vendors where vendor_name = 'A.V.K';

2.select segment1 from po_vendors where segment1='865';

3.select * from po_vendor_sites_all where vendor_id =89183;

4.select vendor_site_id from po_vendor_sites_all where vendor_id =89183;

5.select party_site_id from po_vendor_sites_all  where vendor_id = 89183;

6.select vendor_site_id from po_vendor_sites_all where vendor_id = 89182;

7.select * from po_vendor_contacts WHERE party_site_id=304651;


Posted by at

6 comments:

  1. Arivazhagan27 January 2013 at 05:25

    Hi
    I am unable to create vendor contact using the API.
    The return status (x_return_status)from the API is 'U' and x_msg_data is 'FND'.
    I also came across this metalink note that says API is not the best way to create vendor contact
    --How To Create Supplier Contacts Using Supplier Contact Import? [ID 1186663.1]
    -- My code below

    ReplyDelete
    Replies
    1. Arivazhagan27 January 2013 at 05:31

      DECLARE
      l_vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;
      l_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;
      l_vendor_contact_rec ap_vendor_pub_pkg.r_vendor_contact_rec_type;
      x_return_status VARCHAR2 (1);
      x_msg_count NUMBER;
      x_msg_data VARCHAR2 (2000);
      x_vendor_id NUMBER;
      x_party_id NUMBER;
      x_vendor_site_id NUMBER;
      l_party_site_id NUMBER;
      x_location_id NUMBER;
      l_vendor_contact_id NUMBER;
      l_per_party_id NUMBER;
      l_rel_party_id NUMBER;
      l_rel_id NUMBER;
      l_org_contact_id NUMBER;

      CURSOR l_vendor_contact
      IS
      SELECT *
      FROM xxtwg_ap_supp_sites_stg@apollo_to_apps
      WHERE vendor_name = 'ABC INTERNATIONAL LTD';
      BEGIN
      FOR contact_rec IN l_vendor_contact
      LOOP
      IF contact_rec.contact_last_name IS NOT NULL
      THEN
      l_vendor_contact_rec.org_id := fnd_profile.value('ORG_ID');
      l_vendor_contact_rec.vendor_id := 7055;
      l_vendor_contact_rec.vendor_site_id := 7038;
      l_vendor_contact_rec.person_first_name :=
      contact_rec.contact_first_name;
      l_vendor_contact_rec.person_last_name :=
      contact_rec.contact_last_name;
      l_vendor_contact_rec.phone := contact_rec.phone;
      l_vendor_contact_rec.email_address :=
      contact_rec.email_address;
      l_vendor_contact_rec.fax_phone := contact_rec.fax;
      ap_vendor_pub_pkg.create_vendor_contact
      (p_api_version => 1.0,
      x_return_status => x_return_status,
      x_msg_count => x_msg_count,
      x_msg_data => x_msg_data,
      p_vendor_contact_rec => l_vendor_contact_rec,
      x_vendor_contact_id => l_vendor_contact_id,
      x_per_party_id => l_per_party_id,
      x_rel_party_id => l_rel_party_id,
      x_rel_id => l_rel_id,
      x_org_contact_id => l_org_contact_id,
      x_party_site_id => l_party_site_id
      );

      IF (x_return_status <> 'S')
      THEN
      DBMS_OUTPUT.put_line
      (SUBSTR (fnd_msg_pub.get (p_encoded => fnd_api.g_false),
      1,
      500
      )
      );
      IF x_msg_count >= 1
      THEN
      FOR i IN 1 .. x_msg_count
      LOOP
      DBMS_OUTPUT.put_line (SUBSTR (fnd_msg_pub.get (p_encoded =>fnd_api.g_false),1,255));END LOOP;END IF;
      ELSE DBMS_OUTPUT.put_line ('Supplier Contact Created/Updated!!!');END IF; END IF;END LOOP;
      EXCEPTION WHEN OTHERS THEN
      DBMS_OUTPUT.put_line (SQLERRM);END;

      Delete
  2. Sambit20 October 2013 at 09:24

    This comment has been removed by the author.

    ReplyDelete
  3. Sambit20 October 2013 at 09:25

    I am also getting the same error.
    Any clue on how to come out of this ???

    ReplyDelete
  4. Vamshi Surapaneni23 June 2017 at 10:49

    when i am creating i am getting below error can you please look on below

    THE sample output is :-
    ETest1
    THE Error is :-This Site name already exists for this supplier.

    Re-enter. Supplier sites API Error
    This Site name already exists for this supplier.

    Re-enter. Supplier sites API Error Test2
    THE Error is :-Supplier Site Info is duplicate Supplier sites API Error
    Supplier Site Info is duplicate Supplier sites API Error Test2
    THE Error is :-PARTY_SITE_ID is invalid Supplier sites API Error
    PARTY_SITE_ID is invalid Supplier sites API Error Test2

    ReplyDelete
    Replies
    1. Vamshi Surapaneni23 June 2017 at 10:50

      Need to know why it is throwing PARTY_SITE_ID is invalid

      Delete

Subscribe to: Post Comments (Atom)