Inventory Outbound Interface

CREATE OR REPLACE PROCEDURE RAMANA_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 RAMANA_INV_Out1;