[Home] [Help]
PACKAGE BODY: APPS.BEN_DM_CREATE_TRANSFER_FILE
Source
1 PACKAGE BODY ben_dm_create_transfer_file AS
2 /* $Header: benfdmcrfl.pkb 120.0 2006/06/13 14:54:30 nkkrishn noship $ */
3
4 g_package varchar2(60) := 'ben_dm_create_transfer_file';
5
6 procedure main
7 (
8 p_dir_name in varchar2,
9 p_file_name in varchar2,
10 p_delimiter in varchar2
11 ) is
12
13 cursor c_inp_file is
14 select 'BEN_DM_INPUT_FILE'||p_delimiter||
15 input_file_id||p_delimiter||
16 source_business_group_name||p_delimiter||
17 source_national_identifier||p_delimiter||
18 source_person_id||p_delimiter||
19 target_person_id||p_delimiter||
20 target_business_group_name||p_delimiter||
21 target_national_identifier||p_delimiter||
22 group_order||p_delimiter||
23 person_type||p_delimiter||
24 data_source||p_delimiter||
25 status||p_delimiter
26 from ben_dm_input_file;
27 l_inp_rec c_inp_file%rowtype;
28
29 cursor c_resolve_map is
30 select 'BEN_DM_RESOLVE_MAPPINGS'||p_delimiter||
31 resolve_mapping_id||p_delimiter||
32 table_name||p_delimiter||
33 column_name||p_delimiter||
34 source_id||p_delimiter||
35 source_key||p_delimiter||
36 target_id||p_delimiter||
37 business_group_name||p_delimiter||
38 mapping_type||p_delimiter||
39 resolve_mapping_id1||p_delimiter||
40 resolve_mapping_id2||p_delimiter||
41 resolve_mapping_id3||p_delimiter||
42 resolve_mapping_id4||p_delimiter||
43 resolve_mapping_id5||p_delimiter||
44 resolve_mapping_id6||p_delimiter||
45 resolve_mapping_id7||p_delimiter
46 from ben_dm_resolve_mappings;
47
48 l_proc varchar2(255) := g_package||'main';
49 l_file_handle utl_file.file_type;
50 l_max_ext number := 32767;
51 l_text varchar2(32767);
52
53 begin
54 ben_dm_utility.message('ROUT','entry:'||l_proc, 5);
55 --
56 -- get the file handle
57 --
58 l_file_handle := utl_file.fopen(p_dir_name,p_file_name,'w',l_max_ext);
59 --
60 open c_inp_file;
61 loop
62 fetch c_inp_file into l_text;
63 if c_inp_file%notfound then
64 exit;
65 end if;
66
67 utl_file.put_line(l_file_handle,l_text);
68
69 end loop;
70 close c_inp_file;
71
72 open c_resolve_map;
73 loop
74 fetch c_resolve_map into l_text;
75 if c_resolve_map%notfound then
76 exit;
77 end if;
78
79 utl_file.put_line(l_file_handle,l_text);
80
81 end loop;
82 close c_resolve_map;
83
84 ben_dm_utility.message('ROUT','exit:'||l_proc, 5);
85
86 exception
87 when utl_file.invalid_path then
88 fnd_message.set_name('BEN', 'BEN_91874_EXT_DRCTRY_ERR'); --9999
89 fnd_file.put_line(fnd_file.log, fnd_message.get);
90 if utl_file.is_open(l_file_handle) then
91 utl_file.fclose(l_file_handle);
92 end if;
93 fnd_message.raise_error;
94 --
95 when utl_file.invalid_mode then
96 fnd_message.set_name('BEN', 'BEN_92249_UTL_INVLD_MODE');
97 fnd_file.put_line(fnd_file.log, fnd_message.get);
98 if utl_file.is_open(l_file_handle) then
99 utl_file.fclose(l_file_handle);
100 end if;
101 fnd_message.raise_error;
102 --
103 when utl_file.invalid_filehandle then
104 fnd_message.set_name('BEN', 'BEN_92250_UTL_INVLD_FILEHANDLE');
105 fnd_file.put_line(fnd_file.log, fnd_message.get);
106 if utl_file.is_open(l_file_handle) then
107 utl_file.fclose(l_file_handle);
108 end if;
109 fnd_message.raise_error;
110 --
111 when utl_file.invalid_operation then
112 fnd_message.set_name('BEN', 'BEN_92251_UTL_INVLD_OPER');
113 fnd_file.put_line(fnd_file.log, fnd_message.get);
114 if utl_file.is_open(l_file_handle) then
115 utl_file.fclose(l_file_handle);
116 end if;
117 fnd_message.raise_error;
118 --
119 when utl_file.read_error then
120 fnd_message.set_name('BEN', 'BEN_92252_UTL_READ_ERROR');
121 fnd_file.put_line(fnd_file.log, fnd_message.get);
122 if utl_file.is_open(l_file_handle) then
123 utl_file.fclose(l_file_handle);
124 end if;
125 fnd_message.raise_error;
126 --
127 when utl_file.internal_error then
128 fnd_message.set_name('BEN', 'BEN_92253_UTL_INTRNL_ERROR');
129 fnd_file.put_line(fnd_file.log, fnd_message.get);
130 if utl_file.is_open(l_file_handle) then
131 utl_file.fclose(l_file_handle);
132 end if;
133 fnd_message.raise_error;
134 --
135 when utl_file.invalid_maxlinesize then
136 fnd_message.set_name ('BEN' ,'BEN_92492_UTL_LINESIZE_ERROR');
137 fnd_file.put_line(fnd_file.log , fnd_message.get );
138 if utl_file.is_open(l_file_handle) then
139 utl_file.fclose(l_file_handle);
140 end if;
141 fnd_message.raise_error ;
142 --
143 when others then
144 if utl_file.is_open(l_file_handle) then
145 utl_file.fclose(l_file_handle);
146 end if;
147 raise;
148
149 end main;
150
151 end ben_dm_create_transfer_file;