[Home] [Help]
PACKAGE BODY: APPS.IGS_EN_NSC_FILE_PRNT_PKG
Source
1 PACKAGE BODY IGS_EN_NSC_FILE_PRNT_PKG AS
2 /* $Header: IGSEN89B.pls 115.6 2002/11/29 00:12:39 nsidana noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IGS_EN_NSC_FILE_PRNT_PKG';
5 g_prod VARCHAR2(3) := 'IGS';
6 g_debug_mode BOOLEAN := TRUE;
7
8 -- Type of group list description
9 TYPE group_list_type IS RECORD (
10 pk_attrib_id igs_en_attrib_values.attrib_id%TYPE, --PK attrib which provides versions for the loop
11 ord_by_attrib_id igs_en_attrib_values.attrib_id%TYPE, --Odrer by attrib id
12 attrib_amount NUMBER, --Amount of the described attribs in the table
13 attrib_start_index NUMBER ); --Start index in the table with the attrib info
14
15
16 -- Type of table of group list description
17
18 TYPE group_list_tbl_type IS TABLE OF group_list_type
19 INDEX BY BINARY_INTEGER;
20
21 -- Type of attrib list description
22 TYPE attrib_list_type IS RECORD (
23 attrib_id igs_en_attrib_values.attrib_id%TYPE, --ID of the attribute. If 0 - then attrib is a constant and has a default value
24 len NUMBER (10), -- Lenght of the full value
25 format_mask VARCHAR2(30), -- Not used
26 empty_space_fill VARCHAR2(1) , -- If the value length is less then provided then the rest is filled with this character
27 pre_attrib_char VARCHAR2(1) , -- Character added before the value
28 post_attrib_char VARCHAR2(1) , -- Character added after the value
29 align VARCHAR2(1) , -- Alighnment currently only 'L'eft or 'R'ight supported
30 default_val VARCHAR2(255)); --Default value of the value is NULL
31
32 -- Type of table of attrib list description
33
34 TYPE attrib_list_tbl_type IS TABLE OF attrib_list_type
35 INDEX BY BINARY_INTEGER;
36
37 PROCEDURE Put_Debug_Msg (
38 p_debug_message IN VARCHAR2
39 );
40
41 FUNCTION Format_Attrib (
42 p_obj_type_id IN igs_en_attrib_values.obj_type_id%TYPE ,
43 p_obj_id IN igs_en_attrib_values.obj_id%TYPE ,
44 p_version IN igs_en_attrib_values.version%TYPE ,
45 p_attr_def IN attrib_list_type
46 )RETURN VARCHAR2;
47
48
49
50 PROCEDURE Init_format_data (
51 p_form_id IN NUMBER, --Not used in the current implementation
52 x_total_groups OUT NOCOPY NUMBER,
53 x_group_list OUT NOCOPY group_list_tbl_type,
54 x_attr_list OUT NOCOPY attrib_list_tbl_type
55 );
56
57 /* Main public procedure which is called for the printing */
58
59 PROCEDURE Generate_file(
60 p_api_version IN NUMBER,
61 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
62 p_commit IN VARCHAR2 := FND_API.G_FALSE,
63 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
64 x_return_status OUT NOCOPY VARCHAR2,
65 x_msg_count OUT NOCOPY NUMBER,
66 x_msg_data OUT NOCOPY VARCHAR2,
67 p_obj_type_id IN NUMBER,
68 p_doc_inst_id IN NUMBER,
69 p_dirpath IN VARCHAR2,
70 p_file_name IN VARCHAR2,
71 p_form_id IN NUMBER ,
72 p_debug_mode IN VARCHAR2 := FND_API.G_FALSE
73 )
74 IS
75
76 l_api_name CONSTANT VARCHAR2(30) := 'Generate_file';
77 l_file_ptr UTL_FILE.FILE_TYPE;
78 l_attr_list attrib_list_tbl_type;
79 l_group_list group_list_tbl_type;
80 l_total_groups NUMBER(10);
81 l_group_count NUMBER(10);
82 l_attrib_count NUMBER(10);
83 l_line VARCHAR2(2000);
84 l_attr_val VARCHAR2(255);
85
86 CURSOR c_group_vers (c_pk_id NUMBER, c_ord_id NUMBER) IS
87 SELECT pk_tbl.version
88 FROM igs_en_attrib_values pk_tbl,
89 igs_en_attrib_values ord_tbl
90 WHERE pk_tbl.obj_type_id = ord_tbl.obj_type_id (+)
91 AND pk_tbl.obj_id = ord_tbl.obj_id (+)
92 AND pk_tbl.version = ord_tbl.version (+)
93 AND pk_tbl.obj_id = p_doc_inst_id
94 AND pk_tbl.obj_type_id = p_obj_type_id
95 AND pk_tbl.attrib_id = c_pk_id
96 AND ord_tbl.attrib_id = c_ord_id
97 ORDER BY ord_tbl.value;
98
99 BEGIN
100 -- Standard Start of API savepoint
101 SAVEPOINT Generate_file;
102
103 -- Standard call to check for call compatibility.
104 IF NOT FND_API.Compatible_API_Call ('1.0',
105 p_api_version,
106 l_api_name,
107 G_PKG_NAME)
108 THEN
109 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
110 END IF;
111
112 -- Initialize message list if p_init_msg_list is set to TRUE.
113
114 x_return_status := FND_API.G_RET_STS_SUCCESS;
115
116 IF FND_API.to_Boolean (p_init_msg_list) THEN
117 FND_MSG_PUB.initialize;
118 END IF;
119
120 g_debug_mode := FND_API.TO_BOOLEAN(p_debug_mode);
121 --Opening file
122 Put_Debug_Msg('Opening file: '||p_dirpath||p_file_name);
123
124 l_file_ptr := UTL_FILE.FOPEN ( p_dirpath, p_file_name, 'a' );
125
126 Put_Debug_Msg('Init visual data');
127 --Init visual data
128 Init_format_data (
129 p_form_id => 1 ,
130 x_total_groups => l_total_groups,
131 x_group_list => l_group_list ,
132 x_attr_list => l_attr_list );
133
134
135 -- Group Loop
136 Put_Debug_Msg('Loop through all groups');
137 FOR l_group_count IN 1..l_total_groups LOOP
138
139 -- Loop throug all version of the PK attrib in the group
140
141 FOR c_group_vers_rec IN
142 c_group_vers( l_group_list(l_group_count).pk_attrib_id,l_group_list(l_group_count).ord_by_attrib_id )
143 LOOP -- Versions list
144
145
146 --Init the line - max 200 characters
147 l_line := '';
148
149 --Loop through all attributes in the group
150
151 FOR l_attrib_count IN l_group_list(l_group_count).attrib_start_index..(
152 l_group_list(l_group_count).attrib_start_index + l_group_list(l_group_count).attrib_amount -1)
153 LOOP --List of all attributes
154
155 -- Get formatted value
156
157 l_attr_val := Format_Attrib (
158 p_obj_type_id => p_obj_type_id,
159 p_obj_id => p_doc_inst_id,
160 p_version => c_group_vers_rec.version,
161 p_attr_def => l_attr_list(l_attrib_count));
162
163 l_line:= l_line||l_attr_val;
164
165 END LOOP; --End of list of attributes
166
167 -- Put line into the file
168
169 UTL_FILE.PUT_LINE ( l_file_ptr, l_line );
170 UTL_FILE.FFLUSH ( l_file_ptr );
171
172 END LOOP; --End of versions list
173 END LOOP; -- End of Group Loop
174
175 Put_Debug_Msg('Closing file');
176
177 IF (UTL_FILE.IS_OPEN ( l_file_ptr )) THEN
178 UTL_FILE.FCLOSE ( l_file_ptr );
179 END IF;
180
181 EXCEPTION
182 WHEN UTL_FILE.INVALID_PATH THEN
183 ROLLBACK TO Generate_file;
184 x_return_status := FND_API.G_RET_STS_ERROR;
185 FND_MESSAGE.set_name('IGS', 'IGS_EN_INVALID_PATH');
186 FND_MSG_PUB.Add;
187
188 WHEN UTL_FILE.WRITE_ERROR THEN
189 ROLLBACK TO Generate_file;
190 x_return_status := FND_API.G_RET_STS_ERROR;
191 FND_MESSAGE.set_name('IGS', 'IGS_EN_WRITE_ERROR');
192 FND_MSG_PUB.Add;
193
194 WHEN UTL_FILE.INVALID_FILEHANDLE THEN
195 ROLLBACK TO Generate_file;
196 x_return_status := FND_API.G_RET_STS_ERROR;
197 FND_MESSAGE.set_name('IGS', 'IGS_EN_INVALID_FILEHANDLE');
198 FND_MSG_PUB.Add;
199
200 WHEN FND_API.G_EXC_ERROR THEN
201
202 ROLLBACK TO Generate_file;
203 Put_Debug_Msg('EXC_ERROR exception');
204 x_return_status := FND_API.G_RET_STS_ERROR;
205 IF (UTL_FILE.IS_OPEN ( l_file_ptr )) THEN
206 UTL_FILE.FCLOSE ( l_file_ptr );
207 END IF;
208 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
209 p_data => x_msg_data );
210
211 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
212
213 ROLLBACK TO Generate_file;
214 Put_Debug_Msg('UNEXPECTED_ERROR exception');
215
216 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
217 IF (UTL_FILE.IS_OPEN ( l_file_ptr )) THEN
218 UTL_FILE.FCLOSE ( l_file_ptr );
219 END IF;
220 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
221 p_data => x_msg_data );
222
223 WHEN OTHERS THEN
224
225 ROLLBACK TO Generate_file;
226 Put_Debug_Msg('Others exception');
227 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
228
229 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
230 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
231 END IF;
232
233 IF (UTL_FILE.IS_OPEN ( l_file_ptr )) THEN
234 UTL_FILE.FCLOSE ( l_file_ptr );
235 END IF;
236
237 END Generate_file;
238
239 /* Procedure retrieves and formats the particular attribute value */
240
241 FUNCTION Format_Attrib (
242 p_obj_type_id IN igs_en_attrib_values.obj_type_id%TYPE ,
243 p_obj_id IN igs_en_attrib_values.obj_id%TYPE ,
244 p_version IN igs_en_attrib_values.version%TYPE ,
245 p_attr_def IN attrib_list_type
246 )RETURN VARCHAR2
247 IS
248 l_value VARCHAR2(255);
249 l_ind NUMBER(5);
250 l_len NUMBER(5);
251 l_post_len NUMBER(5);
252 BEGIN
253
254 --Getting value
255 IF p_attr_def.attrib_id <> 0 THEN
256 -- If the attrib ID is null -the it's not stored in the value table and
257 -- represents the constant, written in the default value
258
259 l_value := IGS_EN_GS_ATTRIB_VAL.Get_Value (
260 p_obj_type_id => p_obj_type_id,
261 p_obj_id => p_obj_id,
262 p_attrib_id => p_attr_def.attrib_id,
263 p_version => p_version
264 ) ;
265 END IF;
266
267 -- Assigning default value if no value is found
268 IF l_value IS NULL THEN
269 l_value := p_attr_def.default_val;
270 END IF;
271
272 -- Check if the retrieved value plus lenght of the post and pre charachters in not more then given
273
274 IF (NVL(length (l_value),0)+NVL(length (p_attr_def.post_attrib_char),0)+NVL(length (p_attr_def.pre_attrib_char),0) ) > p_attr_def.len THEN
275 l_value := substr (l_value,1,(p_attr_def.len-NVL(length (p_attr_def.post_attrib_char),0)-NVL(length (p_attr_def.pre_attrib_char),0)));
276 END IF;
277
278
279 --Checking the alignment and adding the char to the right side
280 IF p_attr_def.align = 'R' THEN
281 l_value := l_value ||p_attr_def.post_attrib_char;
282 l_post_len := NVL(length (p_attr_def.pre_attrib_char),0);
283 ELSE
284 l_value := p_attr_def.pre_attrib_char || l_value;
285 l_post_len := NVL(length (p_attr_def.post_attrib_char),0);
286 END IF;
287
288 l_len := NVL(length (l_value),0);
289
290
291 --Adding characters to have the given length
292
293 IF (l_len+l_post_len) < p_attr_def.len THEN
294 FOR l_ind IN (l_len+1+l_post_len)..p_attr_def.len LOOP
295
296 IF p_attr_def.align = 'R' THEN
297 --Adding characters to the left side
298 l_value := NVL(p_attr_def.empty_space_fill,' ')||l_value;
299 ELSE
300 l_value := l_value||NVL(p_attr_def.empty_space_fill,' ');
301 END IF;
302
303 END LOOP;
304 END IF;
305
306 IF p_attr_def.align = 'R' THEN
307 l_value := p_attr_def.pre_attrib_char || l_value;
308 ELSE
309 l_value := l_value ||p_attr_def.post_attrib_char;
310 END IF;
311 -- One more check to make sure that left and rigt characters length is not more then the total
312
313 RETURN substr(l_value,1,p_attr_def.len );
314
315 END Format_Attrib;
316
317
318 /* This procedure initializes the visual attributes of the output file */
319 /* For the Id list and visual representation see HLD and DLD */
320 PROCEDURE Init_format_data (
321 p_form_id IN NUMBER, --Not used in the current implementation
322 x_total_groups OUT NOCOPY NUMBER,
323 x_group_list OUT NOCOPY group_list_tbl_type,
324 x_attr_list OUT NOCOPY attrib_list_tbl_type
325 ) IS
326 BEGIN
327
328 x_total_groups := 3; --Total 3 groups: Header, Body Trailer
329
330 x_group_list(1).pk_attrib_id := 1;
331 x_group_list(1).ord_by_attrib_id := 1;
332 x_group_list(1).attrib_start_index := 1;
333 x_group_list(1).attrib_amount := 8;
334
335 x_group_list(2).pk_attrib_id := 20;
336 x_group_list(2).ord_by_attrib_id := 20;
337 x_group_list(2).attrib_start_index := 30;
338 x_group_list(2).attrib_amount := 23;
339
340 x_group_list(3).pk_attrib_id := 10;
341 x_group_list(3).ord_by_attrib_id := 10;
342 x_group_list(3).attrib_start_index := 10;
343 x_group_list(3).attrib_amount := 11;
344
345 --Init header record
346 x_attr_list(1).attrib_id := 0 ;
347 x_attr_list(2).attrib_id := 1 ;
348 x_attr_list(3).attrib_id := 2 ;
349 x_attr_list(4).attrib_id := 3 ;
350 x_attr_list(5).attrib_id := 5 ;
351 x_attr_list(6).attrib_id := 6 ;
352 x_attr_list(7).attrib_id := 0 ;
353 x_attr_list(8).attrib_id := 0 ;
354
355 x_attr_list(1).len := 2 ;
356 x_attr_list(2).len := 6 ;
357 x_attr_list(3).len := 2 ;
358 x_attr_list(4).len := 15 ;
359 x_attr_list(5).len := 1 ;
360 x_attr_list(6).len := 8 ;
361 x_attr_list(7).len := 1 ;
362 x_attr_list(8).len := 215 ;
363
364 x_attr_list(1).align := 'L' ;
365 x_attr_list(2).align := 'L' ;
366 x_attr_list(3).align := 'L' ;
367 x_attr_list(4).align := 'L' ;
368 x_attr_list(5).align := 'L' ;
369 x_attr_list(6).align := 'L' ;
370 x_attr_list(7).align := 'L' ;
371 x_attr_list(8).align := 'L' ;
372
373 x_attr_list(1).default_val := 'A1';
374 x_attr_list(7).default_val := 'F';
375
376 --Trailer
377 x_attr_list(10).attrib_id := 0 ;
378 x_attr_list(11).attrib_id := 10 ;
379 x_attr_list(12).attrib_id := 11 ;
380 x_attr_list(13).attrib_id := 12 ;
381 x_attr_list(14).attrib_id := 13 ;
382 x_attr_list(15).attrib_id := 14 ;
383 x_attr_list(16).attrib_id := 15 ;
384 x_attr_list(17).attrib_id := 16 ;
385 x_attr_list(18).attrib_id := 17 ;
386 x_attr_list(19).attrib_id := 18 ;
387 x_attr_list(20).attrib_id := 0 ;
388
389 x_attr_list(10).len := 2 ;
390 x_attr_list(11).len := 6 ;
391 x_attr_list(12).len := 6 ;
392 x_attr_list(13).len := 6 ;
393 x_attr_list(14).len := 6 ;
394 x_attr_list(15).len := 6 ;
395 x_attr_list(16).len := 6 ;
396 x_attr_list(17).len := 6 ;
397 x_attr_list(18).len := 6 ;
398 x_attr_list(19).len := 8 ;
399 x_attr_list(20).len := 192 ;
400
401 x_attr_list(10).align := 'L' ;
402 x_attr_list(11).align := 'L' ;
403 x_attr_list(12).align := 'L' ;
404 x_attr_list(13).align := 'L' ;
405 x_attr_list(14).align := 'L' ;
406 x_attr_list(15).align := 'L' ;
407 x_attr_list(16).align := 'L' ;
408 x_attr_list(17).align := 'L' ;
409 x_attr_list(18).align := 'L' ;
410 x_attr_list(19).align := 'L' ;
411 x_attr_list(20).align := 'L' ;
412
413 x_attr_list(10).default_val := 'T1';
414
415 --Body
416 x_attr_list(30).attrib_id := 0 ;
417 x_attr_list(31).attrib_id := 20;
418 x_attr_list(32).attrib_id := 21;
419 x_attr_list(33).attrib_id := 22;
420 x_attr_list(34).attrib_id := 23;
421 x_attr_list(35).attrib_id := 24;
422 x_attr_list(36).attrib_id := 25;
423 x_attr_list(37).attrib_id := 26;
424 x_attr_list(38).attrib_id := 27;
425 x_attr_list(39).attrib_id := 28;
426 x_attr_list(40).attrib_id := 29;
427 x_attr_list(41).attrib_id := 30;
428 x_attr_list(42).attrib_id := 31;
429 x_attr_list(43).attrib_id := 32;
430 x_attr_list(44).attrib_id := 33;
431 x_attr_list(45).attrib_id := 34;
432 x_attr_list(46).attrib_id := 35;
433 x_attr_list(47).attrib_id := 36;
434 x_attr_list(48).attrib_id := 37;
435 x_attr_list(49).attrib_id := 38;
436 x_attr_list(50).attrib_id := 39;
437 x_attr_list(51).attrib_id := 40;
438 x_attr_list(52).attrib_id := 0;
439
440 x_attr_list(30).len := 2 ;
441 x_attr_list(31).len := 9 ;
442 x_attr_list(32).len := 20;
443 x_attr_list(33).len := 1 ;
444 x_attr_list(34).len := 20;
445 x_attr_list(35).len := 5 ;
446 x_attr_list(36).len := 9 ;
447 x_attr_list(37).len := 20;
448 x_attr_list(38).len := 1 ;
449 x_attr_list(39).len := 8 ;
450 x_attr_list(40).len := 30;
451 x_attr_list(41).len := 30;
452 x_attr_list(42).len := 20;
453 x_attr_list(43).len := 2 ;
454 x_attr_list(44).len := 9 ;
455 x_attr_list(45).len := 15;
456 x_attr_list(46).len := 8 ;
457 x_attr_list(47).len := 8 ;
458 x_attr_list(48).len := 8 ;
459 x_attr_list(49).len := 8 ;
460 x_attr_list(50).len := 1 ;
461 x_attr_list(51).len := 1 ;
462 x_attr_list(52).len := 15;
463
464 x_attr_list(30).align := 'L' ;
465 x_attr_list(31).align := 'L' ;
466 x_attr_list(32).align := 'L' ;
467 x_attr_list(33).align := 'L' ;
468 x_attr_list(34).align := 'L' ;
469 x_attr_list(35).align := 'L' ;
470 x_attr_list(36).align := 'L' ;
471 x_attr_list(37).align := 'L' ;
472 x_attr_list(38).align := 'L' ;
473 x_attr_list(39).align := 'L' ;
474 x_attr_list(40).align := 'L' ;
475 x_attr_list(41).align := 'L' ;
476 x_attr_list(42).align := 'L' ;
477 x_attr_list(43).align := 'L' ;
478 x_attr_list(44).align := 'L' ;
479 x_attr_list(45).align := 'L' ;
480 x_attr_list(46).align := 'L' ;
481 x_attr_list(47).align := 'L' ;
482 x_attr_list(48).align := 'L' ;
483 x_attr_list(49).align := 'L' ;
484 x_attr_list(50).align := 'L' ;
485 x_attr_list(51).align := 'L' ;
486 x_attr_list(52).align := 'L' ;
487
488 x_attr_list(30).default_val := 'D1';
489 x_attr_list(16).empty_space_fill := '0'; --Zero fill required for this column
490
491 END Init_format_data;
492
493
494
495 PROCEDURE Put_Debug_Msg (
496 p_debug_message IN VARCHAR2
497 ) IS
498 l_api_name CONSTANT VARCHAR2(30) := 'Put_Debug_Message';
499 BEGIN
500 IF g_debug_mode THEN
501 fnd_file.put_line(FND_FILE.LOG,p_debug_message);
502 END IF;
503 EXCEPTION
504 WHEN OTHERS THEN
505 g_debug_mode := FALSE;
506 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
507 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
508 END IF;
509 RETURN;
510
511 END Put_Debug_Msg;
512
513 END IGS_EN_NSC_FILE_PRNT_PKG;