utl file creation for inventory item in oracle apps R12

utl file creation for inventory item in oracle apps R12

CREATE OR REPLACE procedure GE_INV_Out_BAL(Errbuf OUT varchar2,
  Retcode ouT varchar2,
f_id in number,
t_id in varchar2
--,dir_name 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.inventory_item_id=63
and ood.organization_name='Vision Operations'
and msi.organization_id between f_id and t_id;

x_id utl_file.file_type;

l_count number(5) default 0;

path varchar2(1000);

--path V$PARAMETER.value%type;

begin
x_id:=utl_file.fopen('Select value into path from V$PARAMETER Where NAME like 'user_dump_dest'','invoutdata10.txt','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 transferred 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 GE_INV_Out_BAL;