Inventory Outbound Interface

Inventory Outbound Interface

                                Inventory Outbound Interface


CREATE OR REPLACE PROCEDURE bhaskar_inv_out1 (
   errbuf    OUT      VARCHAR2,
   retcode   OUT      VARCHAR2,
   f_id      IN       NUMBER,
   t_id      IN       VARCHAR2
)
AS
   CURSOR c1
   IS
      SELECT msi.segment1 item, msi.inventory_item_id itemid,
             msi.description itemdesc, msi.primary_uom_code uom,
             ood.organization_name NAME, ood.organization_id ID,
             mc.segment1 || ',' || mc.segment2 CATEGORY
        FROM mtl_system_items_b msi,
             org_organization_definitions ood,
             mtl_item_categories mic,
             mtl_categories mc
       WHERE msi.organization_id = ood.organization_id
         AND msi.inventory_item_id = mic.inventory_item_id
         AND msi.organization_id = mic.organization_id
         AND mic.category_id = mc.category_id
         AND msi.purchasing_item_flag = 'Y'
         AND msi.organization_id BETWEEN f_id AND t_id;


   x_id      UTL_FILE.file_type;
   l_count   NUMBER (5)         DEFAULT 0;
BEGIN
   x_id :=UTL_FILE.fopen ('d:\oracle\proddb\8.1.7\plsql\temp',
                      'invoutdata.dat',
                      'W'
                     );


   --select * from v$parameter where name like '%utl_file%'
   FOR x1 IN c1
   LOOP
      l_count := l_count + 1;
      UTL_FILE.put_line (x_id,
                            x1.item
                         || '-'
                         || x1.itemid
                         || '-'
                         || x1.itemdesc
                         || '-'
                         || x1.uom
                         || '-'
                         || x1.NAME
                         || '-'
                         || x1.ID
                         || '-'
                         || x1.CATEGORY
                        );
   END LOOP;
   UTL_FILE.fclose (x_id);
   fnd_file.put_line (fnd_file.output,
                      'No of Records transfered to the data file :' || l_count
                     );
   fnd_file.put_line (fnd_file.output, ' ');
   fnd_file.put_line (fnd_file.output,
                      'Submitted User name  '
                      || fnd_profile.VALUE ('USERNAME')
                     );
   fnd_file.put_line (fnd_file.output, ' ');
   fnd_file.put_line (fnd_file.output,
                         'Submitted Responsibility name '
                      || fnd_profile.VALUE ('RESP_NAME')
                     );
   fnd_file.put_line (fnd_file.output, ' ');
   fnd_file.put_line (fnd_file.output, 'Submission Date :' || SYSDATE);
EXCEPTION
   WHEN UTL_FILE.invalid_operation
   THEN
      fnd_file.put_line (fnd_file.LOG, 'invalid operation');
      UTL_FILE.fclose_all;
   WHEN UTL_FILE.invalid_path
   THEN
      fnd_file.put_line (fnd_file.LOG, 'invalid path');
      UTL_FILE.fclose_all;
   WHEN UTL_FILE.invalid_mode
   THEN
      fnd_file.put_line (fnd_file.LOG, 'invalid mode');
      UTL_FILE.fclose_all;
   WHEN UTL_FILE.invalid_filehandle
   THEN
      fnd_file.put_line (fnd_file.LOG, 'invalid filehandle');
      UTL_FILE.fclose_all;
   WHEN UTL_FILE.read_error
   THEN
      fnd_file.put_line (fnd_file.LOG, 'read error');
      UTL_FILE.fclose_all;
   WHEN UTL_FILE.internal_error
   THEN
      fnd_file.put_line (fnd_file.LOG, 'internal error');
      UTL_FILE.fclose_all;
   WHEN OTHERS
   THEN
      fnd_file.put_line (fnd_file.LOG, 'other error');
      UTL_FILE.fclose_all;
END bhaskar_inv_out1;