API for Supplier & Supplier Site Creation in R12

API for Supplier & Supplier Site Creation in R12

In this tutorial, we will see how to create Supplier , Supplier sites in the Oracle apps through Oracle Standard API. Supplier master conversion includes loading Suppliers, Supplier sites and Supplier contacts details to the Oracle system.

CREATE PACKAGE xxdcb_supplier_imp
AS
   PROCEDURE create_supplier 
                            (
                              p_supplier_number        IN     VARCHAR2,
                              p_supplier_name          IN     VARCHAR2,
                              p_supplier_site_code     IN     VARCHAR2,
                              p_ou                     IN     NUMBER,
                              p_addr1                  IN     VARCHAR2,
                              p_country                IN     VARCHAR2,
                              p_purchasing_site_flag   IN     VARCHAR2,
                              p_pay_site_flag          IN     VARCHAR2,
                              p_status                 OUT    VARCHAR2,
                              p_ret_msg                OUT    VARCHAR2
                           );
END;

CREATE OR REPLACE PACKAGE BODY APPS.xxdcb_supplier_imp
AS
   PROCEDURE create_supplier (p_supplier_number        IN     VARCHAR2,
                              p_supplier_name          IN     VARCHAR2,
                              p_supplier_site_code     IN     VARCHAR2,
                              p_ou                     IN     NUMBER,
                              p_addr1                  IN     VARCHAR2,
                              p_country                IN     VARCHAR2,
                              p_purchasing_site_flag   IN     VARCHAR2,
                              p_pay_site_flag          IN     VARCHAR2,
                              p_status                 OUT    VARCHAR2,
                              p_ret_msg                OUT    VARCHAR2)
   IS
      l_vendor_rec        AP_VENDOR_PUB_PKG.r_vendor_rec_type;
      l_vendor_site_rec   AP_VENDOR_PUB_PKG.r_vendor_site_rec_type;
      x_return_status     VARCHAR2 (2000);
      x_msg_count         NUMBER;
      x_msg_data          VARCHAR2 (2000);
      x_vendor_id         NUMBER;
      x_party_id          NUMBER;
      x_vendor_site_id    NUMBER;
      x_party_site_id     NUMBER;
      x_location_id       NUMBER;
      lc_found            VARCHAR2 (1) := 'N';
      lc_ou_name          VARCHAR2 (240);
   BEGIN
      l_vendor_rec.SEGMENT1 := p_supplier_number;
      l_vendor_rec.VENDOR_NAME := p_supplier_name;
      l_vendor_rec.SUMMARY_FLAG := 'N';
      l_vendor_rec.ENABLED_FLAG := 'Y';
      l_vendor_rec.start_date_active := SYSDATE - 1;
      mo_global.set_policy_context ('S', p_ou);
      FND_GLOBAL.APPS_INITIALIZE (USER_ID        => 1318, -- User Id
                                  RESP_ID        => 63234,-- Responsibility Id
                                  RESP_APPL_ID   => 200); -- Responsibility Application Id (200 is for SQLAP Module)

      BEGIN
         SELECT 'Y'
           INTO lc_found
           FROM ap.ap_Suppliers
          WHERE    segment1 = p_supplier_number
                OR LOWER (vendor_name) = LOWER (p_supplier_name);
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line ('Supplier does not exists');
      END;

      BEGIN
         SELECT name
           INTO lc_ou_name
           FROM hr_organization_units
          WHERE organization_id = p_ou;
      END;

      IF (lc_found = 'Y')
      THEN
         p_status := 'E';
         p_ret_msg :=
            'Supplier already Exists with the given name or supplier number. please check.';
      ELSE
         AP_VENDOR_PUB_PKG.Create_Vendor (
            p_api_version     => 1,
            x_return_status   => x_return_status,
            x_msg_count       => x_msg_count,
            x_msg_data        => x_msg_data,
            p_vendor_rec      => l_vendor_rec,
            x_vendor_id       => x_vendor_id,
            x_party_id        => x_party_id);

         p_status := x_return_status;

         IF (x_return_status <> 'S')
         THEN
            DBMS_OUTPUT.put_line ('ERROR in supplier creation!!');
            DBMS_OUTPUT.put_line (x_msg_data);

            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));
                  p_ret_msg :=
                        p_ret_msg
                     || SUBSTR (
                           FND_MSG_PUB.Get (p_encoded => FND_API.G_FALSE),
                           1,
                           255);
               END LOOP;
            END IF;
         ELSE
            l_vendor_site_rec.vendor_id := x_vendor_id;
            l_vendor_site_rec.VENDOR_SITE_CODE := p_supplier_site_code;
            l_vendor_site_rec.org_id := p_ou;
            l_vendor_site_rec.COUNTRY := p_country;
            l_vendor_site_rec.ADDRESS_LINE1 := p_addr1;
            l_vendor_site_rec.PURCHASING_SITE_FLAG := p_purchasing_site_flag;
            l_vendor_site_rec.PAY_SITE_FLAG := p_pay_site_flag;
            l_vendor_site_rec.org_name := lc_ou_name;


            AP_VENDOR_PUB_PKG.Create_Vendor_Site (
               p_api_version       => 1,
               x_return_status     => x_return_status,
               x_msg_count         => x_msg_count,
               x_msg_data          => x_msg_data,
               p_vendor_site_rec   => l_vendor_site_rec,
               x_vendor_site_id    => x_vendor_site_id,
               x_party_site_id     => x_party_site_id,
               x_location_id       => x_location_id);

            IF (x_return_status <> 'S')
            THEN
               DBMS_OUTPUT.put_line ('ERROR in supplier site creation!!!');
               DBMS_OUTPUT.put_line (x_msg_data);

               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));
                     p_ret_msg :=
                           p_ret_msg
                        || SUBSTR (
                              FND_MSG_PUB.Get (p_encoded => FND_API.G_FALSE),
                              1,
                              255);
                  END LOOP;
               END IF;
            ELSE
               DBMS_OUTPUT.put_line ('Supplier Site Created!!!');
               p_ret_msg:='Supplier,Supplier Site got Created Successfully..! ';
               commit;
            END IF;
         END IF;
      END IF;
   END;
END xxdcb_supplier_imp;
/

Output: 

Front End :