New Supplier Open to Opening Balance upload in EBS R12

CREATE TABLE APPS.MPFL_XXSUPPLIER_STG
(
  CODE            VARCHAR2(40 BYTE),
  SUPPLIER_NAME   VARCHAR2(250 BYTE),
  ADDRESS_LINE1   VARCHAR2(250 BYTE),
  ADDRESS_LINE2   VARCHAR2(250 BYTE),
  ADDRESS_LINE3   VARCHAR2(250 BYTE),
  ADDRESS_LINE4   VARCHAR2(250 BYTE),
  CONTACT_PERSON  VARCHAR2(250 BYTE),
  PHONE           VARCHAR2(40 BYTE),
  FAX             VARCHAR2(40 BYTE),
  SITE_CODE       VARCHAR2(20 BYTE),
  ERROR_MSG       VARCHAR2(250 BYTE)
);

SET DEFINE OFF;
Insert into MPFL_XXSUPPLIER_STG
   (CODE, SUPPLIER_NAME, ADDRESS_LINE1, ADDRESS_LINE2, ADDRESS_LINE3,
    ADDRESS_LINE4, CONTACT_PERSON, PHONE, FAX, SITE_CODE,
    ERROR_MSG)
 Values
   ('12918603', 'IOU : Md.Babul Hossain (18603)', 'Dhaka', 'HO-Badda', 'Dhaka',
    'Dhaka', 'SA-ADMIN', '01912257303', NULL, 'OFFICE',
    'Opened');
COMMIT;


CREATE OR REPLACE PROCEDURE APPS.MPFL_supplier_api_MA AS
--declare
--cursor c1 is select organization_id from hr_operating_units ;
cursor c2 is select * from MPFL_xxsupplier_stg where ERROR_MSG is null ;

l_vendor_rec                    ap_vendor_pub_pkg.r_vendor_rec_type;
l_vendor_site_rec               ap_vendor_pub_pkg.r_vendor_site_rec_type;
l_vendor_contact_rec            AP_VENDOR_PUB_PKG.R_VENDOR_CONTACT_REC_TYPE;

l_return_status             VARCHAR2(10);
l_msg_count                 NUMBER;
l_msg_data                  VARCHAR2(8000);
l_data                      VARCHAR2 (2000);
l_msg_index                 NUMBER;

l_vendor_id                 NUMBER;
l_party_id                  NUMBER;
l_vendor_site_id            NUMBER;
l_party_site_id             NUMBER;
l_location_id               NUMBER;
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_vendor_name_alt   VARCHAR2(10);--ap_supplier.VENDOR_NAME_ALT%type;

 --ap_supp_rec                   ap.ap_suppliers_int%ROWTYPE;

BEGIN
    delete from xxvendor;
    commit;
   
    FND_GLOBAL.APPS_INITIALIZE(USER_ID=>1110,RESP_ID=>NULL,RESP_APPL_ID=>NULL);
    for x2 in c2 loop
             
        /* Supplier Creation */
        --Required

        l_vendor_rec.vendor_name    := x2.supplier_name; --Supplier Name
        l_vendor_rec.segment1       := x2.code;               -- supplier code
        --Optional
        l_vendor_rec.match_option:='R' ;
        --l_venvod_rec.vendor_name_alt :=x2.supplier_name;
       
        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
            );

        IF l_return_status = fnd_api.g_ret_sts_success  THEN
            fnd_file.put_line (fnd_file.LOG,
                                  ' Vendor Id :'
                               || l_vendor_id
                               || ' Created .'
                              );
            DBMS_OUTPUT.put_line ('Vendor Id :'
                               || l_vendor_id
                               || ' Created '
                               );
         --fnd_file.put_line(fnd_file.log,'return_status: '||l_return_status);
         --fnd_file.put_line(fnd_file.log,'msg_data: '||l_msg_data);
         --DBMS_OUTPUT.put_line ('Vendor Creation: ');
         --DBMS_OUTPUT.put_line ('return_status : '||l_return_status);
         --DBMS_OUTPUT.put_line ('msg_data : '||l_msg_data);
        -- fnd_file.put_line(fnd_file.log,'Supplier: '||x2.supplier_name);
     
        /* Supplier Site Creation */
       
        --Required
        l_vendor_site_rec.vendor_id :=l_vendor_id ;
        l_vendor_site_rec.vendor_site_code :=x2.site_code ;
        l_vendor_site_rec.address_line1 :=x2.address_line1 ;
        l_vendor_site_rec.country := 'BD';
        l_vendor_site_rec.county := 'Bangladesh';
        l_vendor_site_rec.org_id  := 148;--x1.organization_id ;
       
       

        --Optional
        l_vendor_site_rec.address_line2  :=x2.address_line2;
        l_vendor_site_rec.address_line3  :=x2.address_line3;
        l_vendor_site_rec.ADDRESS_LINE4  :=x2.address_line4;
        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.vendor_name_alt               := l_vendor_site_rec.vendor_name || ' ALT';


        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
        );
       
        IF l_return_status = fnd_api.g_ret_sts_success  THEN
            fnd_file.put_line (fnd_file.LOG,
                                  ' Vendor Site Id :'
                               || l_vendor_site_id
                               || ' Created .'
                              );
            DBMS_OUTPUT.put_line ('Vendor Site Id  :'
                               || l_vendor_site_id
                               || ' Created '
                               );
       -- fnd_file.put_line(fnd_file.log,'return_status: '||l_return_status);
        --fnd_file.put_line(fnd_file.log,'msg_data: '||l_msg_data);
        --DBMS_OUTPUT.put_line ('Vendor Site Creation: ');
        --DBMS_OUTPUT.put_line ('return_status : '||l_return_status);
        --DBMS_OUTPUT.put_line ('msg_data : '||l_msg_data);
        
        /*  Supplier Contact Information */
       
          --- Required
        l_vendor_contact_rec.vendor_id              :=l_vendor_id ;
        l_vendor_contact_rec.vendor_site_id         :=l_vendor_site_id;
        l_vendor_contact_rec.party_site_id          :=l_party_site_id;
       -- l_vendor_contact_rec.person_first_name := 'B';
        l_vendor_contact_rec.person_last_name       :=x2.contact_person;
       -- l_vendor_contact_rec.org_id                   := x1.organization_id ;
        --optional
        l_vendor_contact_rec.phone                  :=x2.phone ;
        l_vendor_contact_rec.fax_phone              :=x2.fax;
       
        AP_VENDOR_PUB_PKG.CREATE_VENDOR_CONTACT
        (   p_api_version               => 1.0,
            p_init_msg_list             => fnd_api.g_false,
            p_commit                    => fnd_api.g_false,
            p_validation_level          => FND_API.G_VALID_LEVEL_FULL,
            p_vendor_contact_rec        => l_vendor_contact_rec,
            x_return_status             => l_return_status,
            x_msg_count                 => l_msg_count,
            x_msg_data                  => l_msg_data,
            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
        );   
       
        fnd_file.put_line (fnd_file.LOG,
                                  ' Vendor Contact Id :'
                               || x_vendor_contact_id
                               || ' Created .'
                              );
        DBMS_OUTPUT.put_line ('Vendor Contact Id  :'
                               || x_vendor_contact_id
                               || ' Created '
                               );
        --fnd_file.put_line(fnd_file.log,'return_status: '||l_return_status);
        -- fnd_file.put_line(fnd_file.log,'msg_data: '||l_msg_data);
        --DBMS_OUTPUT.put_line ('Vendor Contact Creation: ');
        --DBMS_OUTPUT.put_line ('return_status : '||l_return_status);
        --DBMS_OUTPUT.put_line ('msg_data : '||l_msg_data);
       
        END IF;
        DBMS_OUTPUT.put_line ('return_status : '||l_return_status);
        DBMS_OUTPUT.put_line ('msg_data : '||l_msg_data);
           
        insert into xxvendor values(l_vendor_id);
        
        update MPFL_xxsupplier_stg  set ERROR_MSG='Opened' where code = x2.code;
       
        --delete from xxsupplier_stg where code = x2.code and CODE=123456;
      END IF;
      DBMS_OUTPUT.put_line ('return_status : '||l_return_status);
      DBMS_OUTPUT.put_line ('msg_data : '||l_msg_data);
      DBMS_OUTPUT.put_line('Error In Supplier Creation: '||x2.code||substr(l_return_status,1,250));
     
      IF l_return_status <> fnd_api.g_ret_sts_success
         THEN
            FOR i IN 1 .. l_msg_count
            LOOP
               oe_msg_pub.get (p_msg_index          => i,
                               p_encoded            => fnd_api.g_false,
                               p_data               => l_data,
                               p_msg_index_out      => l_msg_index
                              );
               DBMS_OUTPUT.put_line (   'Error In Supplier Creation: '
                                     || x2.code ||l_data
                                    );
            END LOOP;
       END IF;
            
    end loop;   
    COMMIT;   
Exception
      When OTHERS then
        fnd_file.put_line(fnd_file.log,'Error In Supplier Creation: '||substr(l_return_status,1,250));
        fnd_file.put_line(fnd_file.log,RPAD('*',80,'*'));
       -- DBMS_OUTPUT.put_line('Error In Supplier Creation: '||x2.code||substr(l_return_status,1,250));
END;
/

   update ap.ap_suppliers ss set VENDOR_NAME_ALT=VENDOR_NAME, VENDOR_TYPE_LOOKUP_CODE='EMP'
  where exists
  ( select s.VENDOR_ID from ap.ap_suppliers s, apps.mpfl_XXSUPPLIER_STG tm where s.VENDOR_NAME=tm.SUPPLIER_NAME and s.VENDOR_ID=ss.VENDOR_ID
  ) ;
 
exec APPS.MPFL_supplier_api_MA();