Supplier API's

API's for Create Supplier, Create Supplier Site, Create Supplier Site Contacts
--=======================================================================

-------------Supplier API -------------

1. First Write a CURSOR for Required Data
2. FND_GLOBAL.APPS_INITIALIZE
3. Pass required Values in Proper way.
4. API :- For Supplier Create (pos_vendor_pub_pkg.create_vendor)
   API :- For Supplier Site Create (pos_vendor_pub_pkg.create_vendor_site)
   API :- For Supplier Create Contact (ap_vendor_pub_pkg.create_vendor_contact)
5. Run API
6. Checking

------------------------------  Query For APPS_INITIALIZE   ---------------------------------------------

select 'USER_ID' REMARKS, user_name NAME, user_id ID
from fnd_user where user_name like 'DEVELOPER'
union
select 'RESPONSIBILITY_ID' NAME, responsibility_name, RESPONSIBILITY_ID
from fnd_responsibility_tl where responsibility_name like 'Payables Manager'
union
select 'APPLICATION_ID' NAME, APPLICATION_NAME, APPLICATION_ID
from fnd_application_tl where  APPLICATION_NAME = 'Payables'

------------------------------  Create Supplier API with Example -------------------------------------------

DECLARE

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;
l_msg          varchar2(200);

CURSOR C1 IS

select VENDOR_NAME SUPPLIER_NAME, VENDOR_TYPE SUPPLIER_TYPE
from (
SELECT 'TEST_10' VENDOR_NAME, 'VENDOR' VENDOR_TYPE FROM DUAL 
--SELECT 'Ashok L Pathak' VENDOR_NAME, 'EMPLOYEE' VENDOR_TYPE FROM DUAL 
);


BEGIN

FND_GLOBAL.APPS_INITIALIZE(
      user_id      => 15790  /* USER_ID  => DEVELOPER*/
   ,  resp_id      => 20639  /* RESPONSIBILITY_ID =>   Payables Manager*/
   ,  resp_appl_id => 200  /* APPLICATION_ID  => Payables*/
        );


FOR I IN C1 LOOP

--Required
l_vendor_rec.vendor_name := I.SUPPLIER_NAME;
l_vendor_rec.VENDOR_TYPE_LOOKUP_CODE:= I.SUPPLIER_TYPE;

--Optional
l_vendor_rec.match_option:='R' ;


pos_vendor_pub_pkg.create_vendor
(
p_vendor_rec => l_vendor_rec,
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('return_status: '||l_return_status);
dbms_output.put_line('Msg_Count = ' || l_msg_count);
dbms_output.put_line('msg_data: '||l_msg_data);
dbms_output.put_line('vendor_id: '||l_vendor_id);
dbms_output.put_line('party_id: '||l_party_id);

 
IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
  THEN     
    FOR i IN 1..FND_MSG_PUB.COUNT_MSG LOOP
      l_msg := FND_MSG_PUB.get( p_msg_index => i,
                                p_encoded   => FND_API.G_FALSE
                              );   
       dbms_output.put_line('The API call failed with error '||l_msg);
    END LOOP;
  ELSE
    dbms_output.put_line('The API call ended with SUCESSS status');
  END IF; 
 
 
END LOOP;

END;


--===================  Checking ========================

select * from ap_suppliers 

where vendor_id = :VENDOR_ID

------------------------------  Create Supplier SIte  API with Example -------------------------------------

DECLARE

l_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;
l_return_status     VARCHAR2(10);
l_msg_count     NUMBER;
l_msg_data     VARCHAR2(1000);
l_vendor_site_id     NUMBER;
l_party_site_id     NUMBER;
l_location_id     NUMBER;
l_msg          varchar2(200);

CURSOR C1 IS


/* SITE FOR  VENDOR  */
select '1960255' VENDOR_ID,
'ORACLE_DELHI' SITE_CODE ,
'Building 123'SITE_ADDRESS_LINE_1,
'First Floor' SITE_ADDRESS_LINE_2,
'Tower-A' SITE_ADDRESS_LINE_3,
NULL SITE_CITY_TOWN,
'Delhi' SITE_STATE,
'101010'  SITE_PIN,
'82' OPERATING_UNIT,
'1022' PREPAYMENT_ACCOUNT,
'1021' LIABILITY_ACCOUNT
from dual;

BEGIN
 
FND_GLOBAL.APPS_INITIALIZE(
      user_id      => 15790  /* USER_ID  => DEVELOPER*/
   ,  resp_id      => 20639  /* RESPONSIBILITY_ID =>   Payables Manager*/
   ,  resp_appl_id => 200  /* APPLICATION_ID  => Payables*/
        );

 
FOR I IN C1 LOOP
 
--Required
l_vendor_site_rec.vendor_id :=I.VENDOR_ID;
l_vendor_site_rec.vendor_site_code := I.SITE_CODE;
l_vendor_site_rec.address_line1 :=I.SITE_ADDRESS_LINE_1;
l_vendor_site_rec.address_line2 := I.SITE_ADDRESS_LINE_2;
l_vendor_site_rec.address_line3 :=I.SITE_ADDRESS_LINE_3;
l_vendor_site_rec.CITY := I.SITE_CITY_TOWN;
l_vendor_site_rec.STATE :=I.SITE_STATE;
l_vendor_site_rec.ZIP :=I.SITE_PIN;
l_vendor_site_rec.country := 'IN';
l_vendor_site_rec.org_id := I.OPERATING_UNIT;
l_vendor_site_rec.ATTRIBUTE10 := I.SITE_CODE;
l_vendor_site_rec.ACCTS_PAY_CODE_COMBINATION_ID:= I.LIABILITY_ACCOUNT; -- Libility--
l_vendor_site_rec.PREPAY_CODE_COMBINATION_ID :=I.PREPAYMENT_ACCOUNT; -- Prepayment--

--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';
l_vendor_site_rec.MATCH_OPTION :='R';

pos_vendor_pub_pkg.create_vendor_site
(
p_vendor_site_rec => l_vendor_site_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_vendor_site_id => l_vendor_site_id,
x_party_site_id => l_party_site_id,
x_location_id => l_location_id
);

COMMIT;

dbms_output.put_line('return_status: '||l_return_status);
dbms_output.put_line('msg_data: '||l_msg_data);
dbms_output.put_line('vendor_site_id: '||l_vendor_site_id);
dbms_output.put_line('party_site_id: '||l_party_site_id);
dbms_output.put_line('location_id: '||l_location_id);

    IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
        FOR i IN 1 .. fnd_msg_pub.count_msg LOOP
          l_msg := fnd_msg_pub.get(p_msg_index => i,
                                   p_encoded   => fnd_api.g_false);
          DBMS_OUTPUT.put_line('The API call failed with error ' || l_msg);
        END LOOP;
      ELSE
        DBMS_OUTPUT.put_line('The API call ended with SUCESSS status');
    END IF;

COMMIT;

END LOOP;

END;


--===================  Checking ========================

select * from ap_supplier_sites_all

where vendor_id =

select * from ap_supplier_sites_all

where vendor_Site_id =

------------------------------  Create Supplier Site Contacts API with Example -------------------------


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);
  lr_vend_cont        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;
  l_msg               VARCHAR2(200);

CURSOR C1 IS

select
 '1960255' vendor_id
, '2125734' vendor_site_id
, 'Kuldeep1' first_name
, 'Kumar1' last_name
, 'abc1@gmail.com' email_address  
, '011' AREA_CODE
, '3256242' PHONE
, 82 org_id
FROM DUAL;

BEGIN

  -- Initialize apps session
FND_GLOBAL.APPS_INITIALIZE(
      user_id      => 15790  /* USER_ID  => DEVELOPER*/
   ,  resp_id      => 20639  /* RESPONSIBILITY_ID =>   Payables Manager*/
   ,  resp_appl_id => 200  /* APPLICATION_ID  => Payables*/
        );

  mo_global.init('SQLAP');
 

  FOR I IN C1 LOOP
 
  -- Assign Basic Values
  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;

  -- Assign Contact Details
  lr_vend_cont.vendor_id                    := I.VENDOR_ID;
  lr_vend_cont.vendor_site_id             := I.VENDOR_SITE_ID;
  lr_vend_cont.person_first_name      := I.FIRST_NAME;
  lr_vend_cont.person_last_name      := I.LAST_NAME;
  lr_vend_cont.email_address                 := I.EMAIL_ADDRESS;
  lr_vend_cont.org_id                            := I.ORG_ID;
  lr_vend_cont.AREA_CODE := I.AREA_CODE;
  lr_vend_cont.PHONE := I.PHONE;

  ap_vendor_pub_pkg.create_vendor_contact(p_api_version        => p_api_version,
                                          p_init_msg_list      => p_init_msg_list,
                                          p_commit             => p_commit,
                                          p_validation_level   => p_validation_level,
                                          x_return_status      => x_return_status,
                                          x_msg_count          => x_msg_count,
                                          x_msg_data           => x_msg_data,
                                          p_vendor_contact_rec => lr_vend_cont,
                                          x_vendor_contact_id  => x_vendor_contact_id,
                                          x_per_party_id       => x_per_party_id,
                                          x_rel_party_id       => x_rel_party_id,
                                          x_rel_id             => x_rel_id,
                                          x_org_contact_id     => x_org_contact_id,
                                          x_party_site_id      => x_party_site_id);

  DBMS_OUTPUT.put_line('X_RETURN_STATUS = ' || x_return_status);
  DBMS_OUTPUT.put_line('X_MSG_COUNT = ' || x_msg_count);
  DBMS_OUTPUT.put_line('X_MSG_DATA = ' || x_msg_data);
  DBMS_OUTPUT.put_line('X_VENDOR_CONTACT_ID = ' || x_vendor_contact_id);
  DBMS_OUTPUT.put_line('X_PER_PARTY_ID = ' || x_per_party_id);
  DBMS_OUTPUT.put_line('X_REL_PARTY_ID = ' || x_rel_party_id);
  DBMS_OUTPUT.put_line('X_REL_ID = ' || x_rel_id);
  DBMS_OUTPUT.put_line('X_ORG_CONTACT_ID = ' || x_org_contact_id);
  DBMS_OUTPUT.put_line('X_PARTY_SITE_ID = ' || x_party_site_id);

  IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
    FOR i IN 1 .. fnd_msg_pub.count_msg LOOP
      l_msg := fnd_msg_pub.get(p_msg_index => i,
                               p_encoded   => fnd_api.g_false);
      DBMS_OUTPUT.put_line('The API call failed with error ' || l_msg);
    END LOOP;
  ELSE
    DBMS_OUTPUT.put_line('The API call ended with SUCESSS status');
  END IF;
 

COMMIT;

END LOOP;
 
END;


--===================  Checking ========================

SELECT PERSON.person_first_name, PERSON.person_last_name, PTY_REL.address1,
PTY_REL.City, PTY_REL.state, PTY_REL.country,
PTY_REL.Primary_phone_area_code, PTY_REL.primary_phone_number
FROM hz_parties PERSON, hz_parties PTY_REL, ap_supplier_contacts APSC
WHERE APSC.per_party_id = PERSON.party_id
AND APSC.rel_party_id = PTY_REL.party_id
AND APSC.CREATION_DATE > SYSDATE-1
and APSC.VENDOR_CONTACT_ID = 1438030


select * from ap_supplier_contacts
where VENDOR_CONTACT_ID = 1438030




Comments

  1. Unknown12 September 2019 at 20:50

    hi

    ReplyDelete
  2. oracle e-business Suite R125 January 2022 at 21:26

    Please share the Customer API

    ReplyDelete
  3. Kalyana Chakravarthy11 May 2023 at 06:06

    The vendor site contacts got created. However in SELECT * FROM AP_SUPPLIER_CONTACTS
    where VENDOR_CONTACT_ID , the vendor_site_id column is null

    ReplyDelete

Post a Comment

Popular posts from this blog

E-Text Report In Fusion | Types of E-Text reports

 WHAT IS Oracle Fusion E-Text Report ? An eText template is an RTF-based template that is used to generate text output  for Electronic Funds Transfer (EFT) and Electronic Data Interchange (EDI). How it generate the output for User or Bank? At runtime, BI Publisher applies this template to an input XML data file to create an output text file. that can be transmitted to a bank or other customer.  Because the output is intended for electronic communication, the eText templates must follow very specific format instructions for exact placement of data. Example :- Bank payment file , Bank MT940 File .. etc what is look and feel of this file ? Note:  There are two types of eText templates:  1. fixed-position based (EFT templates)  2. Delimiter-based (EDI templates) What IS EFT File? An EFT is an electronic transmission of financial data and payments to banks in a specific fixed-position format flat file (text). Link for download:- EFT :-  https://docs.google.com/document/d/14xAYceq2hx8WIy3Iux
Read more

How to pass default Parameter in cursor

--==================================================== --Parameterized Cursors /* Syntax of Parameterized Cursor :- CURSOR cur _ name (parameter list may be one, two, or more or Default ) IS SELECT statement; OPEN cur _ name (Parameter value) */ EMPNO ENAME HIREDATE DOB GENDER SAL DEPTNO COMM 1000 Sachin 11-Sep-85 01-Mar-20 M 25000 20 1002 Rena 01-Aug-82 11-Mar-20 F 125000 20 1008 Sonu 11-Sep-85 01-Mar-20 M 2500 10 100 1003 Chavi 25-Mar-81 15-Feb-20 F 5000 20 1006 Ravi 11-Sep-89 01-Mar-19 M 2500 10 100 1005 Vinod 18-Sep-86 01-Mar-20
Read more