1 PACKAGE BODY igs_he_create_extract_pkg AS
2 /* $Header: IGSHE28B.pls 120.3 2006/05/22 00:11:18 jbaber noship $*/
3
4 -- Field stat record stores field length and closed_flag
5 TYPE field_stats_rec IS RECORD (length NUMBER, closed VARCHAR2(1));
6
7 -- Lookup table for field stat record
8 TYPE field_lookup_tbl IS TABLE OF field_stats_rec INDEX BY BINARY_INTEGER;
9
10
11 PROCEDURE create_directory ( p_directory OUT NOCOPY VARCHAR2 ) AS
12 /******************************************************************
13 Created By : ayedubat
14 Date Created By : 24-Dec-02
15 Purpose: Private procedure created for finding the Directory of the filename.
16 This will check the value of profile 'UTL_FILE_OUT'
17 (output dir for export data file) matches with the value in v$parameter
18 for the name utl_file_dir.
19 Known limitations,enhancements,remarks:
20 Change History
21 Who When What
22 *******************************************************************/
23
24 l_start_str_index NUMBER := 1;
25 l_end_comma_index NUMBER := 1;
26 l_start_comma_index NUMBER := 1;
27 l_fndvalue VARCHAR2(80);
28 l_dbvalue V$PARAMETER.VALUE%TYPE;
29 l_db_directory V$PARAMETER.VALUE%TYPE;
30
31 CURSOR cur_parameter_value IS
32 SELECT LTRIM(RTRIM(value))
33 FROM V$PARAMETER
34 WHERE name ='utl_file_dir';
35
36 CURSOR cur_db_dir IS
37 SELECT DECODE( INSTR(l_dbvalue,',',l_start_str_index),0,LENGTH(l_dbvalue)+1, INSTR(l_dbvalue,',',l_start_str_index) )
38 FROM DUAL ;
39
40 BEGIN
41
42 -- Initialize the OUT variable
43 p_directory := NULL ;
44
45 -- Fetch the Profile value for the directory name used to export flat file
46 l_fndvalue := LTRIM(RTRIM(FND_PROFILE.VALUE('UTL_FILE_OUT')));
47
48 -- If the profile is NULL, return the procedure by assigning the NULL value to p_directory
49 IF l_fndvalue IS NULL THEN
50 p_directory := NULL ;
51 RETURN ;
52 END IF ;
53
54 -- Fetch the Value of the Database parameter, utl_file_dir
55 -- which contains list of out put directories seperated by comma
56 OPEN cur_parameter_value ;
57 FETCH cur_parameter_value INTO l_dbvalue ;
58
59 IF cur_parameter_value%FOUND AND l_dbvalue IS NOT NULL THEN
60
61 -- Find the starting position of the Profile value with in the database parameter directory's list
62 -- If not found, return the procedure by assigning the NULL value to p_directory
63 l_start_str_index := INSTR(l_dbvalue,l_fndvalue,l_end_comma_index) ;
64
65 IF l_start_str_index = 0 THEN
66 p_directory := NULL ;
67 CLOSE cur_parameter_value ;
68 RETURN ;
69 END IF;
70
71 -- Find the position of the comma, which is just before the
72 -- required directory(Profile value) in the database parameter value
73 -- If no comma found( if it first in the list), then returns 1
74 l_start_comma_index := INSTR(SUBSTR(l_dbvalue,1,l_start_str_index),',',-1) + 1 ;
75
76 -- Find the position of the comma, which is just after the
77 -- required directory(Profile value) in the database parameter value
78 -- If no comma found( if it last in the list), then returns the length of database parameter value
79 OPEN cur_db_dir ;
80 FETCH cur_db_dir INTO l_end_comma_index ;
81 CLOSE cur_db_dir ;
82
83 -- Find the value of the Database parameter value between l_start_comma_index and l_end_comma_index
84 l_db_directory := LTRIM(RTRIM(SUBSTR(l_dbvalue,l_start_comma_index, l_end_comma_index-l_start_comma_index))) ;
85
86 -- If the Profile Value is with in the Database parameter sirectory list, return the value
87 IF l_db_directory = l_fndvalue THEN
88 p_directory := l_fndvalue ;
89 END IF;
90
91 ELSE
92 p_directory := NULL ;
93
94 END IF ;
95 CLOSE cur_parameter_value ;
96
97 EXCEPTION
98 WHEN OTHERS THEN
99 p_directory := NULL ;
100 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
101 FND_MESSAGE.SET_TOKEN('NAME','IGS_HE_CREATE_EXTRACT_PKG.CREATE_DIRECTORY'||' - '||SQLERRM);
102 IGS_GE_MSG_STACK.ADD;
103 App_Exception.Raise_Exception;
104
105 END create_directory ;
106
107
108 PROCEDURE create_file (errbuf OUT NOCOPY VARCHAR2,
109 retcode OUT NOCOPY NUMBER,
110 p_extract_run_id IN NUMBER,
111 p_file_format IN VARCHAR2,
112 p_use_overrides IN VARCHAR2,
113 p_person_id_grp IN NUMBER,
114 p_program_group IN VARCHAR2,
115 p_created_since_date IN VARCHAR2 ) IS
116 /******************************************************************
117 Created By : Jonathan Baber
118 Date Created By : 23-Nov-05
119 Purpose : Creates extract file
120 Known limitations,enhancements,remarks:
121 Change History
122 Who When What
123 anwest 13-FEB-2006 Bug# 4950285 R12 Disable OSS Mandate
124 *******************************************************************/
125
126 -- Get extract run details
127 CURSOR c_extract_dtls IS
128 SELECT SUBSTR(srt.record_id,4,2) recid,
129 dtls.submission_name,
130 dtls.user_return_subclass,
131 dtls.return_name,
132 dtls.file_location,
133 dtls.file_name,
134 shd.enrolment_start_date,
135 shd.enrolment_end_date
136 FROM igs_he_ext_run_dtls dtls,
137 igs_he_submsn_header shd,
138 igs_he_submsn_return srt
139 WHERE dtls.extract_run_id = p_extract_run_id
140 AND shd.submission_name = dtls.submission_name
141 AND srt.submission_name = dtls.submission_name
142 AND srt.user_return_subclass = dtls.user_return_subclass
143 AND srt.return_name = dtls.return_name;
144
145 -- Get length and closed_flag of each field in return
146 CURSOR c_field_stats (cp_user_return_subclass igs_he_usr_rtn_clas.user_return_subclass%TYPE) IS
147 SELECT sfld.field_number,
148 NVL(sfld.length,0) length,
149 sfld.closed_flag
150 FROM igs_he_sys_rt_cl_fld sfld,
151 igs_he_usr_rt_cl_fld ufld,
152 igs_he_usr_rtn_clas uclas
153 WHERE uclas.user_return_subclass = cp_user_return_subclass
154 AND sfld.system_return_class_type = uclas.system_return_class_type
155 AND ufld.field_number = sfld.field_number
156 AND ufld.user_return_subclass = uclas.user_return_subclass;
157
158 -- Get every field in an extract for given line
159 CURSOR c_extract_field(cp_line_number igs_he_ex_rn_dat_ln.line_number%type) IS
160 SELECT value, override_value, field_number
161 FROM igs_he_ex_rn_dat_fd
162 WHERE extract_run_id = p_extract_run_id
163 AND line_number = cp_line_number
164 ORDER BY field_number ASC;
165
166 -- Determine type (static or dynamic) of persion id group
167 CURSOR c_group_type IS
168 SELECT group_type
169 FROM igs_pe_persid_group_v
170 WHERE group_id = p_person_id_grp;
171
172
173 TYPE ref_extract_line IS REF CURSOR;
174 c_extract_line ref_extract_line;
175
176
177 l_filepath VARCHAR2(270);
178 l_field_stat field_stats_rec;
179 l_field_lookup field_lookup_tbl;
180 l_line_count NUMBER := 0;
181 l_final_value VARCHAR2(200);
182 l_field_length igs_he_sys_rt_cl_fld.length%TYPE;
183 l_value_field_length NUMBER(3);
184 l_prs_grp_status VARCHAR2(1) := NULL;
185 l_group_type igs_pe_persid_group_v.group_type%TYPE;
186 l_file utl_file.file_type := NULL;
187 l_sqlstmt VARCHAR2(32767);
188 l_prs_grp_sql VARCHAR2(32767);
189 l_extract_dtls c_extract_dtls%ROWTYPE;
190 l_line_number igs_he_ex_rn_dat_ln.line_number%TYPE;
191
192 l_bind2 NUMBER;
193 l_bind3 DATE;
194 l_bind4 DATE;
195 l_bind6 DATE;
196 l_bind5 VARCHAR2(10);
197
198 BEGIN
199
200 --anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
201 IGS_GE_GEN_003.SET_ORG_ID;
202
203 -- Get extract run details
204 OPEN c_extract_dtls;
205 FETCH c_extract_dtls INTO l_extract_dtls;
206 CLOSE c_extract_dtls;
207
208
209 -- Log Header
210 fnd_message.set_name('IGS','IGS_HE_PROC_SUBM');
211 fnd_message.set_token('SUBMISSION_NAME',l_extract_dtls.submission_name);
212 fnd_message.set_token('USER_RETURN_SUBCLASS',l_extract_dtls.user_return_subclass);
213 fnd_message.set_token('RETURN_NAME',l_extract_dtls.return_name);
214 fnd_message.set_token('ENROLMENT_START_DATE',l_extract_dtls.enrolment_start_date);
215 fnd_message.set_token('ENROLMENT_END_DATE',l_extract_dtls.enrolment_end_date);
216 fnd_file.put_line(fnd_file.log,fnd_message.get());
217
218
219 -- Get file directory
220 create_directory(l_filepath);
221
222 -- If directory is NULL return with error
223 IF l_filepath IS NULL THEN
224 fnd_message.set_name('IGS','IGS_HE_FILE_DIR_ERROR');
225 errbuf := fnd_message.get;
226 fnd_file.put_line(fnd_file.log, errbuf);
227 retcode:=2;
228 RETURN;
229 END IF ;
230
231
232 -- Open file
233 BEGIN
234 l_file := NULL;
235 l_file := UTL_FILE.FOPEN (l_filepath, l_extract_dtls.file_name, 'w');
236 EXCEPTION
237 WHEN OTHERS THEN
238 fnd_message.set_name('IGS','IGS_HE_FILE_OPEN_ERROR');
239 fnd_message.set_token('1', l_filepath || '/' || l_extract_dtls.file_name);
240 errbuf := fnd_message.get;
241 fnd_file.put_line(fnd_file.log, errbuf);
242 retcode := 2;
243 RETURN;
244 END;
245
246
247 -- Construct Line SQL statement.
248 l_sqlstmt := ' SELECT line_number ' ||
249 ' FROM igs_he_ex_rn_dat_ln ln ' ||
250 ' WHERE ln.extract_run_id = :BIND1' ||
251 ' AND ln.exclude_from_file = ''N'' ';
252
253
254
255 -- Include person group criteria if required
256 -- Does not apply to MODULE return
257 IF p_person_id_grp IS NOT NULL AND l_extract_dtls.recid <> '13' THEN
258
259 -- Determine type (static or dynamic) of person id group
260 OPEN c_group_type;
261 FETCH c_group_type INTO l_group_type;
262 CLOSE c_group_type;
263
264 IF l_group_type = 'STATIC' THEN
265
266 -- Could also use the library file to get static group members but the library
267 -- uses sysdate instead of enrolment start and end date.
268
269 l_prs_grp_sql := ' AND EXISTS ' ||
270 ' (SELECT ''X'' ' ||
271 ' FROM igs_pe_prsid_grp_mem_all a ' ||
272 ' WHERE a.person_id = ln.person_id ' ||
273 ' AND a.group_id = :BIND2 ' ||
274 ' AND (a.start_date IS NULL OR a.start_date <= :BIND3) ' ||
275 ' AND (a.end_date IS NULL OR a.end_date >= :BIND4) ' ||
276 ' )';
277
278 l_sqlstmt := l_sqlstmt || l_prs_grp_sql;
279
280 -- Set bind variables
281 l_bind2 := p_person_id_grp;
282 l_bind3 := l_extract_dtls.enrolment_end_date;
283 l_bind4 := l_extract_dtls.enrolment_start_date;
284
285 ELSE
286 -- Use library to get dynamic persion id group members
287 l_prs_grp_sql := IGS_PE_DYNAMIC_PERSID_GROUP.IGS_GET_DYNAMIC_SQL(p_person_id_grp, l_prs_grp_status);
288
289 IF l_prs_grp_status <> 'S' THEN
290 fnd_message.set_name('IGS','IGS_HE_UT_PRSN_ID_GRP_ERR');
291 fnd_message.set_token('PRSNIDGRP',p_person_id_grp);
292 errbuf := fnd_message.get();
293 fnd_file.put_line(fnd_file.log, errbuf); -- this message need to be displayed to user.
294 retcode := '2';
295 RETURN;
296 END IF;
297
298 l_sqlstmt := l_sqlstmt || 'AND ln.person_id IN ('|| l_prs_grp_sql || ')';
299
300 -- Need to add consistent number of bind variables even if not used
301 l_sqlstmt := l_sqlstmt || ' AND :BIND2 IS NULL AND :BIND3 IS NULL AND :BIND4 IS NULL ';
302
303 END IF; -- Static / Dynamic
304
305 ELSE
306 -- No person_id_grp criteria, but need to add some to have consistent number of bind variables
307 l_sqlstmt := l_sqlstmt || ' AND :BIND2 IS NULL AND :BIND3 IS NULL AND :BIND4 IS NULL ';
308
309
310 END IF; -- Person ID Group Criteria
311
312
313 -- Include program group criteria if required
314 -- only applies to COMBINED or STUDENT return
315 IF p_program_group IS NOT NULL AND l_extract_dtls.recid IN ('11','12') THEN
316
317 l_sqlstmt := l_sqlstmt ||
318 ' AND EXISTS ' ||
319 ' (SELECT ''X'' ' ||
320 ' FROM igs_ps_grp_mbr b ' ||
321 ' WHERE b.course_cd = ln.course_cd ' ||
322 ' AND b.version_number = ln.crv_version_number ' ||
323 ' AND b.course_group_cd = :BIND5 ' ||
324 ' )';
325
326 l_bind5 := p_program_group;
327
328 ELSE
329
330 -- No progam_grp criteria, but need to have consistent number of bind variables
331 l_sqlstmt := l_sqlstmt || ' AND :BIND5 IS NULL ';
332
333 END IF; -- Program Group Criteria
334
335
336 -- Include created since date criteria if required
337 IF p_created_since_date IS NOT NULL THEN
338
339 l_sqlstmt := l_sqlstmt || ' AND ln.last_update_date >= :BIND6 ';
340
341 -- Created since date bind variable is formatted according to concurrent request format.
342 l_bind6 := TO_DATE(p_created_since_date,'yyyy/mm/dd HH24:MI:SS');
343
344 ELSE
345
346 -- No created since criteria criteria, but need to have consistent number of bind variables
347 l_sqlstmt := l_sqlstmt || ' AND :BIND6 IS NULL ';
348
349 END IF;
350
351 -- Finish constructing Line SQL statement
352 l_sqlstmt := l_sqlstmt || ' ORDER BY line_number ASC ';
353
354
355
356 OPEN c_extract_line FOR l_sqlstmt USING p_extract_run_id, l_bind2, l_bind3, l_bind4, l_bind5, l_bind6;
357 FETCH c_extract_line INTO l_line_number;
358
359 -- Make sure there are some lines in the extract otherwise no point in continuing
360 IF c_extract_line%NOTFOUND THEN
361 CLOSE c_extract_line;
362 fnd_message.set_name('IGS','IGS_HE_NO_DTLS_TO_OP');
363 errbuf := fnd_message.get;
364 fnd_file.put_line(fnd_file.log, errbuf);
365 retcode := 1;
366 RETURN;
367
368 ELSE
369
370 -- Get length and closed_flag of each field
371 -- Store in l_field_lookup
372 -- Should use BULK COLLECT in post 8i database
373 FOR l_field IN c_field_stats(l_extract_dtls.user_return_subclass) LOOP
374 l_field_stat.length := l_field.length;
375 l_field_stat.closed := l_field.closed_flag;
376 l_field_lookup(l_field.field_number) := l_field_stat;
377 END LOOP;
378
379
380 -- For every line in the extract
381 WHILE c_extract_line%FOUND LOOP
382
383 FOR l_extract_field in c_extract_field(l_line_number) LOOP
384
385 -- Detemine value of field
386 -- If field is closed, value = NULL
387 -- If overrides are to be used and override value not blank, value = overide
388 -- Otherwise just get value
389 IF l_field_lookup(l_extract_field.field_number).closed = 'Y' THEN
390 l_final_value := NULL;
391 ELSIF p_use_overrides = 'Y' AND l_extract_field.override_value IS NOT NULL THEN
392 IF l_extract_field.override_value = 'NULL' THEN
393 l_final_value := NULL;
394 ELSE
395 l_final_value := ltrim(rtrim(REPLACE(l_extract_field.override_value,'"','') ));
396 END IF;
397 ELSE
398 l_final_value := ltrim(rtrim(REPLACE(l_extract_field.value,'"','') ));
399 END IF;
400
401
402 -- Get length of value and length of field
403 l_value_field_length := NVL(LENGTH(l_final_value),0) ;
404 l_field_length := l_field_lookup(l_extract_field.field_number).length;
405
406
407 -- Truncate value to field length
408 IF ( l_value_field_length > l_field_length) THEN
409 l_final_value := SUBSTR( l_final_value,1,l_field_length) ;
410 END IF;
411
412
413 -- If file format = fixed then pad value
414 IF p_file_format = 'FIX' AND ( l_value_field_length < l_field_length) THEN
415 l_final_value := RPAD( NVL(l_final_value,' '),l_field_length) ;
416 END IF;
417
418
419 -- If file format = csv then prepend ',' on all but first field
420 IF p_file_format = 'CSV' THEN
421 l_final_value := ltrim(rtrim( REPLACE(l_final_value,',',' ') )) ;
422 IF (c_extract_field%ROWCOUNT > 1) THEN
423 l_final_value := ',' || l_final_value;
424 END IF;
425 END IF;
426
427 -- Write field to file
428 BEGIN
429 utl_file.put (l_file, l_final_value );
430 EXCEPTION
431 WHEN UTL_FILE.WRITE_ERROR THEN
432 fnd_message.set_name('IGS', 'IGS_HE_FILE_WRITE_ERROR');
433 fnd_message.set_token('1', l_filepath || '/' || l_extract_dtls.file_name);
434 fnd_file.put_line(fnd_file.log, fnd_message.get);
435 RAISE;
436 END;
437
438 l_final_value :='';
439
440 END LOOP; -- c_extract_field
441
442 -- Append a new line character at the end and write to file
443 utl_file.new_line (l_file);
444 utl_file.fflush (l_file);
445
446 -- Get next line
447 FETCH c_extract_line INTO l_line_number;
448
449 END LOOP; -- c_extract_line
450
451 -- Get line count
452 l_line_count := c_extract_line%ROWCOUNT;
453
454 END IF;
455
456 CLOSE c_extract_line;
457
458 -- Close file
459 BEGIN
460 IF (utl_file.is_open( l_file )) THEN
461 utl_file.fclose( l_file );
462 END IF;
463 EXCEPTION
464 WHEN OTHERS THEN
465 fnd_message.set_name('IGS','IGS_HE_FILE_CLOSE_ERROR');
466 fnd_message.set_token('1', l_filepath || '/' || l_extract_dtls.file_name);
467 fnd_file.put_line(fnd_file.log, fnd_message.get);
468 errbuf := fnd_message.get ;
469 retcode := 2;
470 RETURN;
471 END;
472
473 -- Update extract criteria with location file has been written to
474 UPDATE igs_he_ext_run_dtls
475 SET file_location = l_filepath
476 WHERE extract_run_id = p_extract_run_id;
477
478 -- Log lines written and filename
479 fnd_message.set_name('IGS','IGS_HE_FILE_LINES_WRITTEN');
480 fnd_message.set_token('NUMLINES', l_line_count);
481 fnd_message.set_token('FILE', l_filepath || '/' || l_extract_dtls.file_name);
482 fnd_file.put_line(fnd_file.log, fnd_message.get);
483
484
485 EXCEPTION
486 WHEN OTHERS THEN
487
488 -- Close any open cursors
489 IF c_extract_field%ISOPEN THEN
490 CLOSE c_extract_field;
491 END IF;
492
493 IF c_extract_line%ISOPEN THEN
494 CLOSE c_extract_line;
495 END IF;
496
497 retcode := 2;
498 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
499 Fnd_Message.Set_Token('NAME','igs_he_create_extract_pkg.create_file - ' ||SQLERRM);
500 fnd_file.put_line(fnd_file.log, fnd_message.get);
501 App_Exception.Raise_Exception;
502
503 END create_file;
504
505
506 END igs_he_create_extract_pkg ;
[Home] [Help]