Package is to Enable the Purchsaing Site for all the OU's

CREATE OR REPLACE PACKAGE BODY APPS.XXTYCO_SUPPLIER_PUR_SITE
IS
   --------------------------------------------------------------------
   --
   -- File Name:     XXTYCO_SUPPLIER_PUR_SITE.pck
   -- Author's name: RAMANA REDDY
   -- Description:   This Package is to Enable the Purchsaing Site for all the OU's exception TIMCO, WW and Security HQ
   -- Change History:
   --
   -- Date        Name              Change
   -- ----------- --------          -----------------------------------------
   -- 10-NOV-2010  Ramana Reddy      Initial development.
   --
   --
   --------------------------------------------------------------------

   PROCEDURE ENABLE_PUR_SITE (p_vendor_site_id NUMBER, p_vendor_site_code    VARCHAR2)
   IS
      lv_return_status     VARCHAR2 (1);
      lv_msg_count         NUMBER;
      lv_msg_data          VARCHAR2 (2000);
      lv_vendor_site_id    ap_supplier_sites.vendor_site_id%TYPE;
      lv_vendor_site_rec   AP_VENDOR_PUB_PKG.r_vendor_site_rec_type;
   BEGIN
      lv_vendor_site_id := p_vendor_site_id;
      lv_vendor_site_rec.purchasing_site_flag := 'Y';
      --
      AP_VENDOR_PUB_PKG.Update_Vendor_Site (
         p_api_version        => 1.0,
         p_init_msg_list      => fnd_api.g_true,
         p_commit             => fnd_api.g_false,
         p_validation_level   => fnd_api.g_valid_level_full,
         x_return_status      => lv_return_status,
         x_msg_count          => lv_msg_count,
         x_msg_data           => lv_msg_data,
         p_vendor_site_rec    => lv_vendor_site_rec,
         p_vendor_site_id     => lv_vendor_site_id
      );
     
      DBMS_OUTPUT.put_line(   'VENDOR SITE CODE: '|| p_VENDOR_SITE_CODE
                           || '-'
                           || lv_return_status
                           || '-'
                           || lv_msg_count
                           || '-'
                           || lv_msg_data);
                          
      fnd_file.put_line (fnd_file.LOG,    'VENDOR SITE ID: '|| p_VENDOR_SITE_ID
                           || '-'
                           || lv_return_status
                           || '-'
                           || lv_msg_count
                           || '-'
                           || lv_msg_data);
     
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG, 'ERROR:' || SQLERRM);
         --DBMS_OUTPUT.PUT_LINE ('ERROR:' || SQLERRM);
         fnd_file.put_line (fnd_file.LOG, 'VENDOR SITE ID: '|| p_VENDOR_SITE_ID|| ' Has errored out , Please check.' );

   END;
  
   PROCEDURE Main (p_errbuf              OUT NOCOPY VARCHAR2,
                   p_retcode             OUT NOCOPY NUMBER,       
                   p_draft_Mode       IN            VARCHAR2,
                   p_vendor           IN            VARCHAR2 DEFAULT NULL
                   )
   IS
      lv_return_status   VARCHAR2 (1);
      lv_msg_count       NUMBER;
      lv_msg_data        VARCHAR2 (2000);
      lv_draft_Mode      VARCHAR2 (2) := p_draft_Mode;
           
      CURSOR Vendors
      IS
        SELECT   VENDOR_NAME,
         SEGMENT1,
         PURCHASING_SITE_FLAG,
         VENDOR_SITE_CODE,
         VENDOR_SITE_ID,
         NAME ORGANIZATION
  FROM   po_vendors p, po_vendor_sites_all s,
  hr_operating_units ou
 WHERE       p.vendor_id = s.vendor_id
     and s.org_id = ou.ORGANIZATION_ID
         AND org_id NOT IN (81, 82, 121)
         AND END_DATE_ACTIVE IS NULL
         AND INACTIVE_DATE IS NULL
         AND NVL(PURCHASING_SITE_FLAG, 'N') = 'N'
         AND P.SEGMENT1 = NVL(P_VENDOR,P.SEGMENT1)
         order by VENDOR_NAME;
        
         BEGIN
             fnd_file.put_line (fnd_file.LOG,'List Of Enabled Supplier Sites');
             fnd_file.put_line (fnd_file.LOG,'');
             fnd_file.put_line (fnd_file.LOG,'');
             fnd_file.put_line (fnd_file.OUTPUT,'                           ');
             --fnd_file.put_line (fnd_file.OUTPUT,'***********************************************************************************');
             fnd_file.put_line (fnd_file.OUTPUT,'                           LIST OF ENABLED SUPPLIER SITES ');
             fnd_file.put_line (fnd_file.OUTPUT,'                           ------------------------------------');
             fnd_file.put_line (fnd_file.OUTPUT,'                           ');
             fnd_file.put_line (fnd_file.OUTPUT,'  Parameters:                                                                Run Date: '||sysdate);
             fnd_file.put_line (fnd_file.OUTPUT,'       Draft Mode Only                   :' || p_draft_Mode);
             fnd_file.put_line (fnd_file.OUTPUT,'       Vendor Number                     :' || p_vendor);
             fnd_file.put_line (fnd_file.OUTPUT,'                           ');

              fnd_file.put_line (fnd_file.OUTPUT,
                                     'VENDOR NO'
                                     || '      '
                                     || 'VENDOR NAME         '
                                     || '              '
                                     || 'VENDOR SITE CODE     '
                                     || ' '
                                     || 'PURCHASING SITE FLAG'
                                     || ' '
                                     || 'ORGANIZATION'
                                    );
              fnd_file.put_line (fnd_file.OUTPUT,
                                     '---------'
                                     || '      '
                                     || '-----------         '
                                     || '              '
                                     || '----------------     '
                                     || ' '
                                     || '--------------------'
                                     || ' '
                                     || '------------'
                                    );

      fnd_global.apps_initialize (0, 20639, 200);
      mo_global.init ('S');

FOR c_vendors_rec IN Vendors
      LOOP
         BEGIN
        
         IF lv_draft_mode = 'Y'
               THEN
              
               fnd_file.put_line (
                     fnd_file.OUTPUT,
                        RPAD (c_vendors_rec.segment1, 10)
                     || '     '
                     || RPAD (c_vendors_rec.Vendor_name, 32)
                     || '  '
                     || RPAD (c_vendors_rec.Vendor_site_code, 20)
                     || '  '
                     || '                    '
                     || ' '
                     || RPAD (c_vendors_rec.ORGANIZATION, 36)
                  );

        ELSE
       
        ENABLE_PUR_SITE (c_vendors_rec.vendor_site_id, c_vendors_rec.vendor_site_code);
       
                    fnd_file.put_line (
                     fnd_file.OUTPUT,
                        RPAD (c_vendors_rec.segment1, 10)
                     || '     '
                     || RPAD (c_vendors_rec.Vendor_name, 32)
                     || '  '
                     || RPAD (c_vendors_rec.Vendor_site_code, 20)
                     || '  '
                     || '        Y           '
                     || ' '
                     || RPAD (c_vendors_rec.ORGANIZATION, 36)
                  );
         END IF;
       
  EXCEPTION
            WHEN OTHERS
            THEN
               fnd_file.put_line (fnd_file.LOG, 'ERROR:' || SQLERRM);
               DBMS_OUTPUT.PUT_LINE ('ERROR:' || SQLERRM);
               fnd_file.put_line (fnd_file.LOG,'VENDOR: '|| c_vendors_rec.segment1 || ', SITE CODE: '|| c_vendors_rec.Vendor_site_code|| ' Has errored out , Please check.' );

         END;
      --
      END LOOP;

      --

      COMMIT;
   --
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG, 'ERROR:' || SQLERRM);
         DBMS_OUTPUT.PUT_LINE ('ERROR:' || SQLERRM);
   --

   END Main;
  
END XXTYCO_SUPPLIER_PUR_SITE;
/