Steps to Create Supplier, Supplier Site

CREATE OR REPLACE PROCEDURE   XXC_CREATE_SUPPLIER_SITES(   p_vendor_number    VARCHAR2 default null,
                                                   p_vendor_site      VARCHAR2 default null)
is
CURSOR c_site(p_vendor_number varchar2,p_vendor_site varchar2)
 IS  SELECT * FROM XXC_SUPP_STAGE WHERE  v_supp_flag='V' and v_site_flag='V' and v_contact_flag='V'
           and vendor_number=nvl(p_vendor_number,vendor_number)
           and vendor_site=nvl(p_vendor_site,vendor_site);
    lv_vendor_site_rec         AP_VENDOR_PUB_PKG.R_VENDOR_SITE_REC_TYPE;
    lv_return_status             VARCHAR2(10);
    lv_msg_count                 NUMBER;
    lv_msg_data                  VARCHAR2(1000);
    lv_vendor_site_id           NUMBER;
    lv_party_site_id              NUMBER;
    lv_location_id                 NUMBER;
BEGIN
    fnd_global.apps_initialize(user_id => 1318,resp_id => 50544 ,resp_appl_id => 200);
    --fnd_global.apps_initialize(0,20707,201);
---opening loop valid site data
FOR cp_site in c_site(p_vendor_number,p_vendor_site) LOOP
---QUERY TO GET VENDOR_ID
BEGIN
    SELECT vendor_id
    INTO lv_vendor_site_rec.vendor_id
    FROM ap_suppliers
    WHERE segment1 = cp_site.vendor_number ;  --Vendor Number
EXCEPTION  
     WHEN OTHERS THEN
    dbms_output.put_line('no supplier created'||sqlcode||'  -  '||sqlerrm);
END;
--QUERY TO GET ACCT_PAY_CODE_COMBINATION_ID
BEGIN
SELECT code_combination_id INTO
     lv_vendor_site_rec.ACCTS_PAY_CODE_COMBINATION_ID
 FROM GL_CODE_COMBINATIONS_KFV 
 WHERE concatenated_segments=cp_site.liability_account;
 EXCEPTION WHEN OTHERS
  THEN
   dbms_output.put_line('accts'||SQLCODE||SQLERRM);
END;
----
--QUERY TO GET ACCT_PAY_CODE_COMBINATION_ID
BEGIN
SELECT code_combination_id INTO
     lv_vendor_site_rec.prepay_code_combination_id
 FROM GL_CODE_COMBINATIONS_KFV 
 WHERE concatenated_segments=cp_site.prepay_account;
 EXCEPTION WHEN OTHERS
  THEN
   dbms_output.put_line('accts1'||SQLCODE||SQLERRM);
END;
----
---QUERY TO GET ORG_ID
BEGIN
SELECT   
ORGANIZATION_ID INTO  
lv_vendor_site_rec.org_id FROM 
HR_ALL_ORGANIZATION_UNITS  where name =cp_site.operating_unit_name;
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('org_id'||SQLCODE||SQLERRM);
END;
-----------------
----------Assigning Staging Table Data To  Reocrd Type :R_VENDOR_SITE_REC_TYPE

dbms_output.put_line('Vendor id is :'||lv_vendor_site_rec.vendor_id);
    lv_vendor_site_rec.vendor_site_code             :=cp_site.vendor_site; --Vendor Site Code
    lv_vendor_site_rec.address_line1                   := cp_site.address_line1;       --Address line1
    lv_vendor_site_rec.address_line2                   :=cp_site.address_line2;       --Address line2
        lv_vendor_site_rec.address_line3                   := cp_site.address_line3;       --Address line3
    lv_vendor_site_rec.address_line4                   := cp_site.address_line4;       --Address line4
    lv_vendor_site_rec.city                                  := cp_site.city;               -----city
        lv_vendor_site_rec.state                                := cp_site.state;               ----state
    lv_vendor_site_rec.zip                                   := cp_site.zip_code;              ------zip_code
    lv_vendor_site_rec.country_of_origin_code     := cp_site.country;           --Country
    lv_vendor_site_rec.country                            :=cp_site.country;
    lv_vendor_site_rec.pay_group_lookup_code    := cp_site.vendor_sites_pay_group;--pay group
    lv_vendor_site_rec.terms_name                     := cp_site.vendor_sites_payment_terms;---terms   
        lv_vendor_site_rec.org_name                         := cp_site.operating_unit_name; -- operating unit name
    lv_vendor_site_rec.payment_currency_code    := cp_site.CURRENCY_CODE;       ---.payment_currency_code
    lv_vendor_site_rec.invoice_currency_code       := cp_site.CURRENCY_CODE;      ---invoice currency code
    lv_vendor_site_rec.purchasing_site_flag          :='Y';
    lv_vendor_site_rec.pay_site_flag                     :='Y';
      --Optional
    --lv_vendor_site_rec.rfq_only_site_flag  :='N';

  ------Api to create Venodr sites

AP_VENDOR_PUB_PKG.CREATE_VENDOR_SITE  ( p_api_version             => 1, 
                                    p_vendor_site_rec       => lv_vendor_site_rec,
                                    x_return_status           => lv_return_status,
                                    x_msg_count               => lv_msg_count,
                                    x_msg_data                => lv_msg_data,
                                    x_vendor_site_id         => lv_vendor_site_id,
                                    x_party_site_id            => lv_party_site_id,
                                    x_location_id               => lv_location_id
                                    );
--             IF (lv_return_status <> 'S') THEN
--             dbms_output.put_line('Encountered ERROR in supplier site creation!!!');
--             dbms_output.put_line('--------------------------------------');
--             dbms_output.put_line(lv_msg_data);
--             IF lv_msg_count > 1 THEN
--                        FOR i IN 1..lv_msg_count LOOP
--            dbms_output.put_line(substr(FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE ),1,255));
--                         dbms_output.put_line(substr(FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE ),1,255));
--                        END LOOP;
--                      END IF;
--              END IF;
COMMIT;
dbms_output.put_line('return_status: '||lv_return_status);
dbms_output.put_line('msg_data: '||lv_msg_data);
dbms_output.put_line('msg_data: '||lv_msg_count);
dbms_output.put_line('vendor_site_id: '||lv_vendor_site_id);
dbms_output.put_line('party_site_id: '||lv_party_site_id);
dbms_output.put_line('location_id: '||lv_location_id);
END LOOP;                                       ------------------End of  sites loop
END XXC_CREATE_SUPPLIER_SITES ;
-------------END of create supplier site--------------
----------------start of supplier contact--------------------