Introduction:

Suppliers are individuals and companies from whom you purchase goods and services.

Goal:

Create New suppliers using “pos_vendor_pub_pkg.create_vendor” API

Forms Or Pages:

Example:

API to create ap supplier in oracle apps r12

API IN/OUT Parameters :

• p_vendor_rec IN Record Required
• x_return_status IN VARCHAR2 Out
• x_msg_count IN NUMBER Out
• x_msg_data IN VARCHAR2 Out
• x_vendor_id IN NUMBER Out
• x_party_id IN NUMBER Out

API Parameters clarifications:

• @param p_vendor_rec This is a PL SQL record type, it is a ROWTYPE of “Ap_Suppliers_Int” table.
• @param x_return_status Return status of the procedure call
• @param x_msg_count Count of the return messages that API returns
• @param x_msg_data The collection of the messages.
• @param x_vendor_id The internal supplier identifier for the supplier.
• @param x_party_id Party is a generic concept about an entity that can establish relationships with another entity.

API Calling example:

---*** Create a Table To hold your Values and Errors ***---
CREATE TABLE Supp_Basic_Data
(
   Supp_ID       VARCHAR2 (200),
   supp_name     VARCHAR2 (200),
   Status        VARCHAR2 (10),
   n_vendor_id   NUMBER (5),
   ERROR_MSG     VARCHAR2 (600)
);

INSERT INTO Supp_Basic_Data (Supp_ID, supp_name)
     VALUES ('1', 'General Motors');
INSERT INTO Supp_Basic_Data (Supp_ID, supp_name)
     VALUES ('2', 'Ford Motors');
INSERT INTO Supp_Basic_Data (Supp_ID, supp_name)
     VALUES ('3', 'BMW Group');
INSERT INTO Supp_Basic_Data (Supp_ID, supp_name)
     VALUES ('4', 'Nissan Group');
INSERT INTO Supp_Basic_Data (Supp_ID, supp_name)
     VALUES ('5', 'Hyundai Motor Group');
INSERT INTO Supp_Basic_Data (Supp_ID, supp_name)
     VALUES ('6', 'Nissan Group');
INSERT INTO Supp_Basic_Data (Supp_ID, supp_name)
     VALUES ('7', 'Hyundai Motor Group');
INSERT INTO Supp_Basic_Data (Supp_ID, supp_name)
     VALUES ('8', 'Honda Motors');
INSERT INTO Supp_Basic_Data (Supp_ID, supp_name)
     VALUES ('9', 'Mercedes-Benz');
INSERT INTO Supp_Basic_Data (Supp_ID, supp_name)
     VALUES ('10', 'Jaguar');

--*** this is the base of "ap_vendor_pub_pkg.r_vendor_rec_type" ***--
    CURSOR vendor_int_cur IS
    SELECT *
    FROM Ap_Suppliers_Int
    WHERE import_request_id = l_request_id
    AND   vendor_interface_id IS NOT NULL
    ORDER BY segment1;
    vendor_int_rec             vendor_int_cur%ROWTYPE;
    vendor_rec                 r_vendor_rec_type;

--*** To ensure your correct insertion of data ***--
SELECT *
FROM   ap_suppliers
WHERE  vendor_id IN (SELECT n_vendor_id FROM supp_basic_data);

DECLARE
  v_api_error       VARCHAR2 (500);
  v_error_msg       VARCHAR2 (500) := NULL;
  l_vendor_rec      ap_vendor_pub_pkg.r_vendor_rec_type;
  l_return_status   VARCHAR2 (10);
  l_msg_count       NUMBER;
  l_msg_data        VARCHAR2 (1000);
  l_vendor_id       NUMBER;
  l_party_id        NUMBER;
  CURSOR xx_supp
  IS
    SELECT * FROM supp_basic_data;
BEGIN
  FOR supp_data IN xx_supp
  LOOP
    BEGIN
      l_vendor_rec.vendor_name   := supp_data.supp_name; -- Required
      pos_vendor_pub_pkg.create_vendor ( 
      -- -------------------------
      -- Input Parameters
      -- -------------------------
      p_vendor_rec      => l_vendor_rec
     , -- ----------------------------
       -- Output Parameters
       -- ----------------------------
      x_return_status   => l_return_status
     ,x_msg_count       => l_msg_count
     ,x_msg_data        => l_msg_data
     ,x_vendor_id       => l_vendor_id
     ,x_party_id        => l_party_id);
      COMMIT;
      dbms_output.put_line (l_vendor_id);
      --To ensure that the data inserted successfully
      BEGIN
        UPDATE supp_basic_data
        SET    status = 'Done', n_vendor_id = l_vendor_id, error_msg = NULL
        WHERE  supp_id = supp_data.supp_id;
      EXCEPTION
        WHEN OTHERS THEN
          NULL;
      END;
    -- To handle unexpected errors and unsuccessful data insertion
    EXCEPTION
      WHEN OTHERS THEN
        v_api_error   := sqlerrm;
        v_error_msg   := v_error_msg || '   ' || v_api_error;
        UPDATE supp_basic_data
        SET    error_msg = v_error_msg, status = 'Error'
        WHERE  supp_id = supp_data.supp_id;
        COMMIT;
    END;
  END LOOP;
END;

Final Result: