Tuesday, July 8, 2014

Oracle Supplier Creation with API

DECLARE
   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_type  AP_VENDOR_PUB_PKG.r_vendor_contact_rec_type;
  
   l_return_status     VARCHAR2 (2000);
   l_msg_count         NUMBER;
   l_msg_data          VARCHAR2 (2000);
  
   l_vendor_id         NUMBER;
   l_party_id          NUMBER;
  
   l_vendor_site_id    NUMBER;
   l_location_id       NUMBER;
   l_party_site_id     NUMBER;
  
   l_vendor_contact_id NUMBER;
   l_per_party_id      NUMBER;
   l_rel_party_id      NUMBER;
   l_rel_id            NUMBER;
   l_org_contact_id    NUMBER;
  
   --->> Supplier Header Data
   -- p_vendor_number     VARCHAR2(240) := '1234';
   p_vendor_name       VARCHAR2(240) := 'OracleApps88 - 100001';
   p_enabled_flag      VARCHAR2(240) := 'Y';
   p_vendor_type_code  VARCHAR2(240) := 'SUPPLIER';
   p_invoice_currency  VARCHAR2(240) := 'USD';
   p_payment_currency  VARCHAR2(240) := 'USD';
   p_term_id           NUMBER        := 10002;
   p_payment_method    VARCHAR2(240) := 'Check';
  
   --->> Supplier Sites Data
   -- p_vendor_site_code  VARCHAR2(240) := '';
   p_address_line1     VARCHAR2(240) := 'Habsiguda';
   p_address_line2     VARCHAR2(240) := 'Tharnaka';
   p_county            VARCHAR2(240) := 'Hyderabad';
   p_city              VARCHAR2(240) := 'Hyderabad';
   p_state             VARCHAR2(240) := 'Telangana';
   p_country           VARCHAR2(240) := 'IN';
   p_zip               NUMBER        := 5000078;
   p_org_id            NUMBER        := 204;

   --->> Supplier Contacts Data
   p_person_first_name VARCHAR2(240) := 'Oracle';
   p_person_last_name  VARCHAR2(240) := 'Apps';
   p_email_address     VARCHAR2(240) := p_person_first_name||'.'||p_person_last_name||'88@Yahoo.com';
  
   API_ERROR           EXCEPTION;
  
BEGIN
      FND_GLOBAL.APPS_INITIALIZE(1318,50554,200);
      -- fnd_msg_pub.initialize;
   
      -- l_vendor_rec.segment1                            := p_vendor_number;
      l_vendor_rec.vendor_name                      := p_vendor_name;
      l_vendor_rec.vendor_type_lookup_code          := p_vendor_type_code;
      l_vendor_rec.enabled_flag                     := p_enabled_flag;
      l_vendor_rec.start_date_active                := SYSDATE;
      l_vendor_rec.invoice_currency_code            := p_invoice_currency;
      l_vendor_rec.terms_id                         := p_term_id;
      l_vendor_rec.payment_currency_code            := p_payment_currency;
      l_vendor_rec.ext_payee_rec.default_pmt_method := p_payment_method;
     
     
      AP_VENDOR_PUB_PKG.CREATE_VENDOR (p_api_version        => 1.0,
                                       p_init_msg_list      => 'F',
                                       p_commit             => 'T',
                                       x_return_status      => l_return_status,
                                       x_msg_count          => l_msg_count,
                                       x_msg_data           => l_msg_data,
                                       p_vendor_rec         => l_vendor_rec,
                                       x_vendor_id          => l_vendor_id,
                                       x_party_id           => l_party_id
                                      );

   IF l_return_status != 'S'
       THEN
          RAISE API_ERROR;
   ELSE
         DBMS_OUTPUT.put_line ('Supplier Created, Vendor ID : ' ||l_vendor_id ||', Party ID : '||l_party_id);
     
        l_vendor_site_rec.vendor_id        := l_vendor_id;
        l_vendor_site_rec.vendor_site_code := l_vendor_id||'_SUPPSITE'; -- p_vendor_site_code;
        l_vendor_site_rec.address_line1    := p_address_line1;
        l_vendor_site_rec.ADDRESS_LINE2    := p_address_line2;
        l_vendor_site_rec.country          := p_country;
        l_vendor_site_rec.county           := p_county;
        l_vendor_site_rec.city             := p_city;
        l_vendor_site_rec.state            := p_state;
        l_vendor_site_rec.zip              := p_zip;
        l_vendor_site_rec.org_id           := p_org_id;

      AP_VENDOR_PUB_PKG.Create_Vendor_Site
            (
             p_api_version       => 1.0,
             p_init_msg_list     => 'F',
             p_commit            => 'T',
             x_return_status     => l_return_status,
             x_msg_count         => l_msg_count,
             x_msg_data          => l_msg_data,
             p_vendor_site_rec   => l_vendor_site_rec,
             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 != 'S'
          THEN
            RAISE API_ERROR;
      ELSE
        DBMS_OUTPUT.put_line ('Supplier Site Created, Vendor Site ID : '||l_vendor_site_id||', Party Site ID : '||l_party_site_id||', Location ID : '||l_location_id);
       
            l_vendor_contact_rec_type.vendor_id         := l_vendor_id;
            l_vendor_contact_rec_type.vendor_site_id    := l_vendor_site_id;
            l_vendor_contact_rec_type.person_first_name := p_person_first_name;
            l_vendor_contact_rec_type.person_last_name  := p_person_last_name;
            l_vendor_contact_rec_type.email_address     := p_email_address;
            l_vendor_contact_rec_type.org_id            := p_org_id;
           
          AP_VENDOR_PUB_PKG.CREATE_VENDOR_CONTACT
            (
              p_api_version          => 1.0,
              p_init_msg_list        => 'F',
              p_commit               => 'T',
              x_return_status        => l_return_status,
              x_msg_count            => l_msg_count,
              x_msg_data             => l_msg_data,
              p_vendor_contact_rec   => l_vendor_contact_rec_type,
              x_vendor_contact_id    => l_vendor_contact_id,
              x_per_party_id         => l_per_party_id,
              x_rel_party_id         => l_rel_party_id,
              x_rel_id               => l_rel_id,
              x_org_contact_id       => l_org_contact_id,
              x_party_site_id        => l_party_site_id
            );
           
          IF l_return_status != 'S'
            THEN
                RAISE API_ERROR;
          ELSE
            DBMS_OUTPUT.put_line ('Supplier Contact Created'
                                    ||', Vendor Contact ID : '   || l_vendor_contact_id
                                    ||', Per Party ID : '        ||l_per_party_id
                                    ||', Rel Party ID : '        ||l_rel_party_id
                                    ||', Rel ID : '              ||l_rel_id
                                    ||', Org Contact ID : '      ||l_org_contact_id
                                    ||', Party Site ID : '       ||l_party_site_id
                                 );
          END IF;
         
      END IF;
     
   END IF;
EXCEPTION
   WHEN API_ERROR
   THEN
      FOR i IN 1 .. l_msg_count
      LOOP
         DBMS_OUTPUT.put_line (SUBSTR (FND_MSG_PUB.Get (p_encoded => FND_API.G_FALSE),1,255));
         DBMS_OUTPUT.put_line ('Error Msg : ' || l_msg_data);
      END LOOP;
     
   WHEN OTHERS
   THEN
      FOR i IN 1 .. l_msg_count
      LOOP
         DBMS_OUTPUT.put_line (SUBSTR (FND_MSG_PUB.Get (p_encoded => FND_API.G_FALSE),1,255));
         DBMS_OUTPUT.put_line ('Error Msg : ' || l_msg_data);
      END LOOP;


END;

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect