[Home] [Help]
PACKAGE BODY: APPS.IGI_IMP_IAC_EXPORT_PKG
Source
1 PACKAGE BODY igi_imp_iac_export_pkg AS
2 /* $Header: igiimepb.pls 120.7.12000000.1 2007/08/01 16:20:55 npandya ship $ */
3
4 --===========================FND_LOG.START=====================================
5
6 g_state_level NUMBER := FND_LOG.LEVEL_STATEMENT;
7 g_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
8 g_event_level NUMBER := FND_LOG.LEVEL_EVENT;
9 g_excep_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
10 g_error_level NUMBER := FND_LOG.LEVEL_ERROR;
11 g_unexp_level NUMBER := FND_LOG.LEVEL_UNEXPECTED;
12 g_path VARCHAR2(100) := 'IGI.PLSQL.igiimepb.igi_imp_iac_export_pkg.';
13
14 --===========================FND_LOG.END====================================
15
16 FUNCTION trim_invalid_chars (p_validation_string varchar2) return varchar2 is
17 l_string_length number; -- Variable to hold the length of the string passed to be validated
18 l_validated_string varchar2(2000); -- Variable which holds the final string after trimming unwanted characters
19 l_converted_string varchar2(2000); -- Variable to hold the string converted to ASCII using the convert function
20 begin
21 -- First obtain the length of the string to be validated
22
23 l_string_length := NVL(length(p_validation_string), 0);
24
25 -- Since the string passed to be validated can be in any language depending on the user environment parameter, first
26 -- convert the string to ASCII and check for the invalid characters. After trimming off the invalids reconvert the string back
27 -- to the users language
28
29 l_converted_string := convert(p_validation_string, 'US7ASCII', substr(userenv('LANGUAGE'), instr(userenv('LANGUAGE'), '.') + 1));
30
31 -- Validate each character in the string against alphabets and integers. If the character is not an alphabet/integer simply ignore it.
32 -- The test to check whether the character entered is an alphabet/integer is done using the ASCII value of the character
33
34 FOR cntr IN 1..l_string_length
35 LOOP
36 IF (ASCII(SUBSTR(l_converted_string, cntr, 1)) BETWEEN 48 AND 57) -- Check for integers (0-9)
37 OR (ASCII(SUBSTR(l_converted_string, cntr, 1)) BETWEEN 65 AND 90) -- Check for uppercase alphabets ('A'-'Z')
38 OR (ASCII(SUBSTR(l_converted_string, cntr, 1)) BETWEEN 97 AND 122) THEN -- Check for lowercase alphabets ('a'-'z')
39 l_validated_string := l_validated_string||SUBSTR(l_converted_string, cntr, 1);
40 END IF;
41 END LOOP;
42
43 -- If the validated string still holds the value of NULL, assign a default value as the return value. Such a case is rare and can occur only if
44 -- the string to be validated is passed as a NULL or if it contains all invalids
45
46 IF (l_validated_string IS NULL) THEN
47 l_validated_string := 'DEFAULT';
48 END IF;
49
50 l_validated_string := convert(l_validated_string, substr(userenv('LANGUAGE'), instr(userenv('LANGUAGE'), '.') + 1), 'US7ASCII');
51
52 return l_validated_string;
53 EXCEPTION
54 WHEN OTHERS THEN
55 return convert('DEFAULT', substr(userenv('LANGUAGE'), instr(userenv('LANGUAGE'), '.') + 1), 'US7ASCII');
56 END trim_invalid_chars;
57
58
59 /* Main Process */
60 PROCEDURE Export_data_process(
61 errbuf OUT NOCOPY varchar2 ,
62 retcode OUT NOCOPY number,
63 p_book IN IGI_IMP_IAC_INTERFACE.book_type_code%type,
64 p_category_id IN IGI_IMP_IAC_INTERFACE.category_id%type,
65 category_name IN varchar2) IS
66
67 /* Cursor to get all the groups for a book and category_id */
68 Cursor c_get_groups is
69 Select distinct group_id from igi_imp_iac_interface
70 where book_type_code = p_book and
71 category_id = p_category_id;
72
73
74 l_get_groups c_get_groups%rowtype;
75 l_filename varchar2(1000);
76 l_fname_with_locn varchar2(1000);
77 l_request_id NUMBER;
78 l_message varchar2(1000);
79 l_gp_present_chk_flag varchar2(1);
80 -- l_bookname_sans_spaces IGI_IMP_IAC_INTERFACE.book_type_code%type; -- Bug 2843747 (Tpradhan) - Commented since no longer required
81 l_file_loc varchar2(1000);
82
83 IGI_IMP_IAC_GROUP_NOTFOUND Exception;
84 IGI_IAC_REQUEST_SUB_ERR Exception;
85 IGI_IAC_NOT_ENABLED Exception;
86 l_path_name VARCHAR2(150) := g_path||'export_data_process';
87
88 Begin
89 /* Check whether the IAC Option is enabled */
90 if NOT igi_gen.is_req_installed('IAC') then
91 raise IGI_IAC_NOT_ENABLED;
92 END IF;
93
94
95 /* Flag to check whther groups are present for the given Book and category */
96 l_gp_present_chk_flag := 'N';
97
98
99
100 /* Remove all spaces in the book type code*/
101 -- l_bookname_sans_spaces:= remove_spaces(p_book); -- Bug No. 2843747 (Tpradhan) - Commented since this check is now performed using trim_invalid_chars function
102
103 l_file_loc:='';
104 /* Get the Directory from the profile IGI_IMP_IAC_FILE_LOCN */
105 fnd_profile.get('IGI_IMP_IAC_FILE_LOCN',l_file_loc);
106
107 for l_get_groups in c_get_groups loop
108 l_gp_present_chk_flag :='Y';
109
110 -- Bug 2843747 (Tpradhan) - Included the trim_invalid_chars function for the book name and category name so that any spaces or additional characters
111 -- can be removed. These characters lead to a failure in spooling the file.
112
113 l_filename := trim_invalid_chars(p_book)||'_'||trim_invalid_chars(category_name)||'_'||to_char(l_get_groups.group_id)||'_'||'out.csv';
114 l_fname_with_locn := l_file_loc||l_filename;
115
116 /* call the concurrent process to run igiimpef.sql*/
117 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
118 p_full_path => l_path_name,
119 p_string => ' Group_id is ' || l_get_groups.group_id
120 || ' File : ' || l_filename);
121
122
123 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
124 APPLICATION => 'IGI',
125 PROGRAM => 'IGIIMPED',
126 DESCRIPTION => 'Inflation Accounting : Write data To Data file',
127 START_TIME => NULL,
128 SUB_REQUEST => FALSE,
129 ARGUMENT1 => p_book,
130 ARGUMENT2 => p_category_id,
131 ARGUMENT3 => l_get_groups.group_id,
132 ARGUMENT4 => l_fname_with_locn,
133 ARGUMENT5 => CHR(0),
134 ARGUMENT6 => NULL,
135 ARGUMENT7 => NULL,
136 ARGUMENT8 => NULL,
137 ARGUMENT9 => NULL,
138 ARGUMENT10 => NULL,
139 ARGUMENT11 => NULL,
140 ARGUMENT12 => NULL,
141 ARGUMENT13 => NULL,
142 ARGUMENT14 => NULL,
143 ARGUMENT15 => NULL,
144 ARGUMENT16 => NULL,
145 ARGUMENT17 => NULL,
146 ARGUMENT18 => NULL,
147 ARGUMENT19 => NULL,
148 ARGUMENT20 => NULL,
149 ARGUMENT21 => NULL,
150 ARGUMENT22 => NULL,
151 ARGUMENT23 => NULL,
152 ARGUMENT24 => NULL,
153 ARGUMENT25 => NULL,
154 ARGUMENT26 => NULL,
155 ARGUMENT27 => NULL,
156 ARGUMENT28 => NULL,
157 ARGUMENT29 => NULL,
158 ARGUMENT30 => NULL,
159 ARGUMENT31 => NULL,
160 ARGUMENT32 => NULL,
161 ARGUMENT33 => NULL,
162 ARGUMENT34 => NULL,
163 ARGUMENT35 => NULL,
164 ARGUMENT36 => NULL,
165 ARGUMENT37 => NULL,
166 ARGUMENT38 => NULL,
167 ARGUMENT39 => NULL,
168 ARGUMENT40 => NULL,
169 ARGUMENT41 => NULL,
170 ARGUMENT42 => NULL,
171 ARGUMENT43 => NULL,
172 ARGUMENT44 => NULL,
173 ARGUMENT45 => NULL,
174 ARGUMENT46 => NULL,
175 ARGUMENT47 => NULL,
176 ARGUMENT48 => NULL,
177 ARGUMENT49 => NULL,
178 ARGUMENT50 => NULL,
179 ARGUMENT51 => NULL,
180 ARGUMENT52 => NULL,
181 ARGUMENT53 => NULL,
182 ARGUMENT54 => NULL,
183 ARGUMENT55 => NULL,
184 ARGUMENT56 => NULL,
185 ARGUMENT57 => NULL,
186 ARGUMENT58 => NULL,
187 ARGUMENT59 => NULL,
188 ARGUMENT60 => NULL,
189 ARGUMENT61 => NULL,
190 ARGUMENT62 => NULL,
191 ARGUMENT63 => NULL,
192 ARGUMENT64 => NULL,
193 ARGUMENT65 => NULL,
194 ARGUMENT66 => NULL,
195 ARGUMENT67 => NULL,
196 ARGUMENT68 => NULL,
197 ARGUMENT69 => NULL,
198 ARGUMENT70 => NULL,
199 ARGUMENT71 => NULL,
200 ARGUMENT72 => NULL,
201 ARGUMENT73 => NULL,
202 ARGUMENT74 => NULL,
203 ARGUMENT75 => NULL,
204 ARGUMENT76 => NULL,
205 ARGUMENT77 => NULL,
206 ARGUMENT78 => NULL,
207 ARGUMENT79 => NULL,
208 ARGUMENT80 => NULL,
209 ARGUMENT81 => NULL,
210 ARGUMENT82 => NULL,
211 ARGUMENT83 => NULL,
212 ARGUMENT84 => NULL,
213 ARGUMENT85 => NULL,
214 ARGUMENT86 => NULL,
215 ARGUMENT87 => NULL,
216 ARGUMENT88 => NULL,
217 ARGUMENT89 => NULL,
218 ARGUMENT90 => NULL,
219 ARGUMENT91 => NULL,
220 ARGUMENT92 => NULL,
221 ARGUMENT93 => NULL,
222 ARGUMENT94 => NULL,
223 ARGUMENT95 => NULL,
224 ARGUMENT96 => NULL,
225 ARGUMENT97 => NULL,
226 ARGUMENT98 => NULL,
227 ARGUMENT99 => NULL,
228 ARGUMENT100 => NULL
229 );
230
231 IF l_request_id = 0 THEN
232 raise IGI_IAC_REQUEST_SUB_ERR;
233 ELSE
234 commit;
235 /* Bug No : 2497429 sowsubra start storing the entire directory path for export file instead of just the file name */
236 /* Update the igi_imp_iac_interface table with the export details */
237 update igi_imp_iac_interface set export_file=l_fname_with_locn ,export_date=trunc(sysdate)
238 where book_type_code = p_book and
239 category_id = p_category_id and
240 group_id =l_get_groups.group_id;
241 /* Bug No:2497429 sowsubra end */
242 commit;
243 END IF;
244
245
246 end loop;
247
248 /* If No groups are present Raise exception */
249 If l_gp_present_chk_flag = 'N' Then
250 Raise IGI_IMP_IAC_GROUP_NOTFOUND;
251 End IF;
252
253
254 EXCEPTION
255
256 WHEN IGI_IAC_NOT_ENABLED Then
257 fnd_message.set_name('IGI','IGI_GEN_PROD_NOT_INSTALLED');
258 fnd_message.set_token('OPTION_NAME','IAC');
259 igi_iac_debug_pkg.debug_other_msg(p_level => g_error_level,
260 p_full_path => l_path_name,
261 p_remove_from_stack => FALSE);
262 Errbuf:=fnd_message.get;
263 fnd_file.put_line(fnd_file.log, errbuf);
264 retcode :=2;
265
266 WHEN IGI_IMP_IAC_GROUP_NOTFOUND Then
267 fnd_message.set_name('IGI','IGI_IMP_IAC_NO_GROUPS');
268 igi_iac_debug_pkg.debug_other_msg(p_level => g_error_level,
269 p_full_path => l_path_name,
270 p_remove_from_stack => FALSE);
271 Errbuf:=fnd_message.get;
272 fnd_file.put_line(fnd_file.log, errbuf);
273 retcode:=2;
274
275 WHEN IGI_IAC_REQUEST_SUB_ERR then
276 fnd_message.retrieve(l_message);
277 igi_iac_debug_pkg.debug_other_msg(p_level => g_error_level,
278 p_full_path => l_path_name,
279 p_remove_from_stack => FALSE);
280 retcode:=2;
281 errbuf:=l_message;
282 fnd_file.put_line(fnd_file.log, errbuf);
283
284 End Export_data_process;
285
286
287 END igi_imp_iac_export_pkg;