Oracle Application TCA | Supplier API | Sample

Hi guys

I’m posting a sample script for creating suppliers, sites and contacts. I’ve referred multiple sample scripts and believe the below code block is a fine tuned one, however standing refinement at all levels. Please note, I haven’t added the API block for creating banks for suppliers. Will, and update the scripts as I make advancements.

/* Formatted on 10/5/2015 11:12:16 AM (QP5 v5.163.1008.3004) */
SET DEFINE OFF;
SET SERVEROUTPUT ON;

DECLARE
   --For supplier parameters

   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;

   --Site parameters

   l_vendor_site_rec      ap_vendor_pub_pkg.r_vendor_site_rec_type;
   lc_return_status       VARCHAR2 (10);
   ln_msg_count           NUMBER;
   lc_msg_data            VARCHAR2 (1000);
   ln_vendor_site_id      NUMBER;
   ln_party_site_id       NUMBER;
   ln_location_id         NUMBER;


   --Contact parameters
   
   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;


   --General exception

   local_exception        EXCEPTION;
   local_failed_at        VARCHAR2 (10);
   
     p_vendor_number VARCHAR2(30) := NULL;
     
     
BEGIN
--Please note: This API was tested against Release 12 (12.0.6)
--You are warned against undesired results, if tried against unsupported application releases

--Initialize application 
--"Master Data" responsibility details
   mo_global.init ('SQLAP');
   fnd_global.apps_initialize (user_id        => 1353,
                               resp_id        => 50997,
                               resp_appl_id   => 200);
   fnd_global.set_nls_context ('AMERICAN');


   mo_global.set_policy_context ('S', 101);
   
   

   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 := 'WINDOWS7BUGS BLOG';
   p_vendor_rec.vendor_type_lookup_code := 'VENDOR'; --Vendor type supplier
   p_vendor_rec.SUMMARY_FLAG := 'N';
   p_vendor_rec.ENABLED_FLAG := 'Y';
--  p_vendor_rec.women_owned_flag := 'N';
--  p_vendor_rec.small_business_flag := 'Y';

-- Supplier MUST have a global level payment method
-- So that individual companies can defer the default payment method while sites are created
-- I have tried the following @ site levels, didn't work until at supplier level assigned. You may post corrections with
-- Comments section

   p_vendor_rec.ext_payee_rec.Exclusive_Pay_Flag:='N';  
   p_vendor_rec.ext_payee_rec.default_pmt_method := 'CHECK'; 

-- if the Payable System setup is set automatic numbering for the suppliers  (table ->AP_PRODUCT_SETUP Column -> SUPPLIER_NUMBERING_METHOD = 'AUTOMATIC')
-- You can get the next number from column NEXT_AUTO_SUPPLIER_NUM
-- if you are following manual numbering (Alpha Numeric ) 
-- p_vendor_rec.segment1 :='865'; --(insert non duplicate number, in case if the supplier numbers are not fetched from a sequence, check your setups)
 
-- We do have an automatic numbering for suppliers, hence the below block is used
-- If your setups are not as explained above
-- Comment from BEING until   p_vendor_rec.segment1 := p_vendor_number;
   
   BEGIN
   Select NEXT_AUTO_SUPPLIER_NUM into p_vendor_number from AP_PRODUCT_SETUP
   where SUPPLIER_NUMBERING_METHOD= 'AUTOMATIC';
   EXCEPTION
   WHEN NO_DATA_FOUND then
   local_failed_at := 'NUMBER';
   RAISE local_exception;
   END;
   
   p_vendor_rec.segment1 := p_vendor_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 ('Supplier Number = ' || p_vendor_number);
   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 ('');


   IF x_return_status <> 'S'
   THEN
      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;

      local_failed_at := 'SUPPLIER';
      RAISE local_exception;
   END IF;

   --Create Site
   l_vendor_site_rec.vendor_id := x_vendor_id;                     -- 1117549;
   l_vendor_site_rec.vendor_site_code := 'Kuwait';
   l_vendor_site_rec.address_line1 := 'Office Address line 1';
   l_vendor_site_rec.city := 'Kuwait';
   l_vendor_site_rec.country := 'KW';
   l_vendor_site_rec.org_id := 101;

   l_vendor_site_rec.ext_payee_rec.default_pmt_method := 'CHECK';

   -- --------------
   -- Optional
   -- --------------
   l_vendor_site_rec.purchasing_site_flag := 'Y';
   l_vendor_site_rec.pay_site_flag := 'Y';
   l_vendor_site_rec.rfq_only_site_flag := 'N';


   pos_vendor_pub_pkg.create_vendor_site (
      -- ------------------------------
      -- Input data elements
      -- ------------------------------
      p_vendor_site_rec   => l_vendor_site_rec,
      -- ---------------------------------
      -- Output data elements
      -- ---------------------------------
      x_return_status     => lc_return_status,
      x_msg_count         => ln_msg_count,
      x_msg_data          => lc_msg_data,
      x_vendor_site_id    => ln_vendor_site_id,
      x_party_site_id     => ln_party_site_id,
      x_location_id       => ln_location_id);

   IF (lc_return_status <> 'S')
   THEN
      IF ln_msg_count > 1
      THEN
         FOR i IN 1 .. ln_msg_count
         LOOP
            DBMS_OUTPUT.put_line (
               SUBSTR (FND_MSG_PUB.Get (p_encoded => FND_API.G_FALSE), 1, 255));
         END LOOP;
      END IF;

      local_failed_at := 'SITE';
      RAISE local_exception;
   ELSE
      DBMS_OUTPUT.put_line ('Vendor Site Id: ' || ln_vendor_site_id);
      DBMS_OUTPUT.put_line ('Party Site Id: ' || ln_party_site_id);
      DBMS_OUTPUT.put_line ('Location Id: ' || ln_location_id);
   END IF;

   --Create Contact

   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 := ln_vendor_site_id;  --OPTIONAL  If you want to attach the contact to a particular site         
   P_VENDOR_CONTACT_REC.PERSON_FIRST_NAME := 'windows7bugs';
   P_VENDOR_CONTACT_REC.PERSON_LAST_NAME := 'blog'; -- Mandatory
   P_VENDOR_CONTACT_REC.PHONE := '22445566';
   P_VENDOR_CONTACT_REC.EMAIL_ADDRESS := 'admin@nocom.com.kw';
   P_VENDOR_CONTACT_REC.URL := 'https://windows7bugs.wordpress.com';
   P_VENDOR_CONTACT_REC.org_id := 101; -- Security Organization Id
   p_vendor_contact_rec.party_site_id := ln_party_site_id;  
--  p_vendor_contact_rec.org_party_site_id := 2273595; --optional, system autofills the column with party_site_id used
   p_vendor_contact_rec.VENDOR_ID := x_vendor_id;                  
   p_vendor_contact_rec.prefix := 'MR.';
   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);

   IF x_return_status <> 'S'
   THEN
      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;

      local_failed_at := 'CONTACT';
      RAISE local_exception;
   ELSE
      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 ('');
   END IF;
   
   COMMIT;
   
EXCEPTION
   WHEN local_exception
   THEN
      IF local_failed_at = 'SUPPLIER'
      THEN
         DBMS_OUTPUT.put_line ('API failed at Supplier Creation');
      ELSIF local_failed_at = 'SITE'
      THEN
         DBMS_OUTPUT.put_line ('API failed at Site Creation');
      ELSIF local_failed_at = 'CONTACT'
      THEN
         DBMS_OUTPUT.put_line ('API failed at Contact Creation');
         ELSIF local_failed_at = 'NUMBER'
      THEN
         DBMS_OUTPUT.put_line ('API failed at getting Supplier Number');
      END IF;
      
      ROLLBACK;
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE (SQLERRM);
      ROLLBACK;
END;

You can download the .sql file from here

Please post your comments, if you come across issues.

regards,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: