[Home] [Help]
PACKAGE BODY: APPS.M4U_XML_EXTN_UTILS
Source
1 PACKAGE BODY m4u_xml_extn_utils AS
2 /* $Header: M4UXUTLB.pls 120.1 2006/06/09 08:13:36 bsaratna noship $ */
3
4 g_success_code VARCHAR2(30); -- cache FND code
5 g_err_code VARCHAR2(30); -- cache FND code
6 g_unexp_err_code VARCHAR2(30); -- cache FND code
7 g_log_lvl NUMBER; -- CLN log level
8 g_log_dir VARCHAR2(800); -- CLN log directory
9
10
11 -- helper routine
12 -- escapes predefined xml entities from value string
13 -- ampersand, apos, quot, lt, gt
14 FUNCTION escape_entities(a_val IN varchar2)
15 RETURN VARCHAR2 AS
16 l_cntr NUMBER;
17 l_size NUMBER;
18 l_char CHAR(1);
19 l_ret_val VARCHAR2(32767);
20 BEGIN
21 IF a_val IS NULL THEN
22 l_ret_val := NULL;
23 ELSE
24 l_size := length(a_val);
25 l_cntr := 1;
26 LOOP
27 EXIT WHEN l_cntr > l_size;
28
29 l_char := substr(a_val,l_cntr,1);
30
31 IF l_char = '<' THEN
32 l_ret_val := l_ret_val || fnd_global.Local_Chr(38) || 'lt;';
33 ELSIF l_char = '>' THEN
34 l_ret_val := l_ret_val || fnd_global.Local_Chr(38) || 'gt;';
35 ELSIF l_char = '"' THEN
36 l_ret_val := l_ret_val || fnd_global.Local_Chr(38) || 'quot;';
37 ELSIF l_char = '''' THEN
38 l_ret_val := l_ret_val || fnd_global.Local_Chr(38) || 'apos;';
39 ELSIF l_char = fnd_global.Local_Chr(38) THEN
40 l_ret_val := l_ret_val || fnd_global.Local_Chr(38) || 'amp;';
41 ELSE
42 l_ret_val := l_ret_val || l_char;
43 END IF;
44 l_cntr := l_cntr+1;
45 END LOOP;
46 END IF;
47
48 RETURN l_ret_val;
49 END escape_entities;
50
51
52 -- Helper procedure to handle exception
53 -- creates translatable message based on error and context
54 -- sets x_ret_sts to error and x_ret_msg to message
55 -- logs error
56 PROCEDURE handle_exception(
57 a_sql_code IN NUMBER,
58 a_sql_errm IN VARCHAR2,
59 a_actn IN VARCHAR2,
60 a_proc IN VARCHAR2,
61 x_ret_sts OUT NOCOPY VARCHAR2,
62 x_ret_msg OUT NOCOPY VARCHAR2)
63 AS
64 BEGIN
65 IF g_log_lvl <= 6 THEN
66 cln_debug_pub.add('Unexpected error occured in - ' || a_proc,6);
67 cln_debug_pub.add('Exception - ' || SQLCODE || ' - ' || SQLERRM,6);
68 END IF;
69
70 FND_MESSAGE.SET_NAME('CLN','M4U_XFWK_UNEXP_ERR');
71 FND_MESSAGE.SET_TOKEN('API',a_proc);
72 FND_MESSAGE.SET_TOKEN('ERR',SQLCODE||'-'||SQLERRM);
73 FND_MESSAGE.SET_TOKEN('ACTION',a_actn);
74 x_ret_msg := FND_MESSAGE.GET;
75 x_ret_sts := g_unexp_err_code;
76
77 EXCEPTION
78 WHEN OTHERS THEN
79 x_ret_sts := g_unexp_err_code;
80 x_ret_msg := a_actn;
81 END handle_exception;
82
83 -- Helper procedure logs generated XML
84 -- Create .txt file containing XML fragment
85 -- File is created in CLN_DEBUG_LOG_DIRECTORY
86 -- Returns failure on exception
87 PROCEDURE log_xml(a_xml IN VARCHAR2,
88 x_ret_sts OUT NOCOPY VARCHAR2,
89 x_ret_msg OUT NOCOPY VARCHAR2)
90 IS
91 l_buff VARCHAR2(400);
92 l_lin_len NUMBER;
93 l_xml VARCHAR2(32767);
94 l_file_ptr utl_file.file_type;
95 l_log_fil VARCHAR2(100);
96 l_progress VARCHAR2(4000);
97 BEGIN
98 l_progress := 'Log generated XML';
99 IF g_log_lvl <= 2 THEN
100 cln_debug_pub.add('Entering m4u_xml_extn_utils.log_xml',2);
101 END IF;
102
103
104 -- obtains logfile name
105 SELECT substr('M4UXGEN-' || to_char(sysdate,'dd-mon-yyyy') || '-' || lpad(cln_debug_s.nextval,8,'0'),1,28)
106 INTO l_log_fil
107 FROM dual;
108 l_log_fil := l_log_fil || '.txt';
109
110
111 IF g_log_lvl <= 1 THEN
112 cln_debug_pub.add('l_log_fil - ' || l_log_fil,1);
113 cln_debug_pub.add('g_log_dir - ' || g_log_dir,1);
114 END IF;
115
116 -- obtain UTL file for logging
117 l_file_ptr := utl_file.fopen(g_log_dir, l_log_fil, 'a');
118
119 -- break XML into pieces of 80 and write to log file
120 l_xml := a_xml;
121 IF a_xml is NULL THEN
122 l_xml := '<M4U>Empty or NULL XML generated</M4U>';
123 END IF;
124 l_lin_len := 80;
125 l_buff := NULL;
126
127 WHILE LENGTH(l_xml) >= 1 LOOP
128 l_buff := SUBSTR(l_xml,1,l_lin_len);
129 l_xml := SUBSTR(l_xml,l_lin_len+1);
130 utl_file.put_line(l_file_ptr,l_buff);
131 utl_file.fflush(l_file_ptr);
132 END LOOP;
133
134 -- Done, close the log file
135 utl_file.fclose(l_file_ptr);
136 -- Return success
137 x_ret_sts := g_success_code;
138 x_ret_msg := NULL;
139
140 IF g_log_lvl <= 2 THEN
141 cln_debug_pub.add('Exiting m4u_xml_extn_utils.log_xml - Normal',2);
142 cln_debug_pub.add('x_ret_msg - ' || x_ret_msg,2);
143 cln_debug_pub.add('x_ret_sts - ' || x_ret_sts,2);
144 END IF;
145
146 RETURN;
147
148 EXCEPTION
149 WHEN OTHERS THEN
150 handle_exception(SQLCODE,SQLERRM,l_progress,'m4u_xml_extn_utils.log_xml',
151 x_ret_sts,x_ret_msg);
152 END;
153
154 /*FUNCTIONS/PROCEDURES DEFINED BELOW ARE USED IN XML VALIDATION */
155
156 -- Procuedure for DTD validation
157 -- Not tested!
158 -- Validates DTD using xmldom return success or error information
159 PROCEDURE dtd_validation(a_xml IN VARCHAR2,
160 x_ret_sts OUT NOCOPY VARCHAR2,
161 x_ret_msg OUT NOCOPY VARCHAR2)
162 IS
163 l_parser xmlparser.Parser;
164 l_doc_typ xmldom.domdocumenttype;
165 l_progress VARCHAR2(4000);
166 BEGIN
167 IF g_log_lvl <= 2 THEN
168 cln_debug_pub.add('Entering m4u_xml_extn_utils.dtd_validation',2);
169 END IF;
170
171 l_progress := 'XML DTD validation';
172
173 BEGIN
174 -- Obtain XML parser instance
175 l_parser := xmlparser.newparser;
176
177 IF g_log_lvl <= 1 THEN
178 cln_debug_pub.add('xmlparser.parser instance obtained',1);
179 END IF;
180
181 -- Set parser base directory for resolving external entities
182 xmlparser.setBaseDIR(l_parser,g_hdr_rec.dtd_base_dir);
183
184 IF g_log_lvl <= 1 THEN
185 cln_debug_pub.add('xmlparser.setBaseURL successful',1);
186 END IF;
187
188 -- Parse DTD document
189 xmlparser.parseDTD(l_parser,g_hdr_rec.dtd_schma_loc,g_hdr_rec.xml_root_node);
190
191 IF g_log_lvl <= 1 THEN
192 cln_debug_pub.add('xmlparser.parseDTD successful',1);
193 END IF;
194
195 -- Get the DTD as a xmldom.domdocumenttype instance
196 l_doc_typ := xmlparser.getDoctype(l_parser);
197
198 IF g_log_lvl <= 1 THEN
199 cln_debug_pub.add('xmlparser.getDoctype successful',1);
200 END IF;
201
202 -- Set parser to DTD validation mode
203 xmlparser.setValidationMode(l_parser,true);
204
205 IF g_log_lvl <= 1 THEN
206 cln_debug_pub.add('xmlparser.setValidationMode successful',1);
207 END IF;
208
209 -- Set Doc type to parsed DTD
210 xmlparser.setDoctype(l_parser,l_doc_typ);
211
212 IF g_log_lvl <= 1 THEN
213 cln_debug_pub.add('xmlparser.setDoctype successful',1);
214 END IF;
215
216 -- Parse input XML and validate against DTD
217 xmlparser.parseBuffer(l_parser,a_xml);
218
219 IF g_log_lvl <= 1 THEN
220 cln_debug_pub.add('xmlparser.parseBuffer successful',1);
221 END IF;
222
223 -- No exeception means validation is successful
224 -- Exit with success code
225 xmlparser.freeParser(l_parser);
226
227 IF g_log_lvl <= 1 THEN
228 cln_debug_pub.add('xmlparser.freeParser successful',1);
229 END IF;
230
231 x_ret_sts := g_success_code;
232 x_ret_msg := NULL;
233
234
235 EXCEPTION
236 WHEN OTHERS THEN
237 -- Exception has occured during DTD validation
238 -- Create translatable user message
239 -- Embed DTD error within
240 FND_MESSAGE.SET_NAME('CLN','M4U_XFWK_VALDTN_ERR');
241 FND_MESSAGE.SET_TOKEN('VALIDATION_TYPE','DTD');
242 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
243 FND_MESSAGE.SET_TOKEN('ERRMESG',SQLERRM);
244
245 -- return failure and DTD errors
246 x_ret_msg := FND_MESSAGE.GET;
247 x_ret_sts := g_err_code;
248
249 END;
250
251 -- Done parsing, return results
252 IF g_log_lvl <= 2 THEN
253 cln_debug_pub.add('Exiting m4u_xml_extn_utils.dtd_validation - Normal',2);
254 cln_debug_pub.add('x_ret_msg - ' || x_ret_msg,2);
255 cln_debug_pub.add('x_ret_sts - ' || x_ret_sts,2);
256 END IF;
257
258 RETURN;
259
260 EXCEPTION
261 WHEN OTHERS THEN
262 handle_exception(SQLCODE,SQLERRM,l_progress,'m4u_xml_extn_utils.dtd_validation',
263 x_ret_sts,x_ret_msg);
264 END dtd_validation;
265
266 -- Java procedure call for Schema Validation
267 -- Note this requires APPS schema to have 9.0.4 XDK version or above.
268 FUNCTION java_schema_validate(a_xml IN VARCHAR2, a_xsd_file IN VARCHAR2)
269 RETURN VARCHAR2
270 IS LANGUAGE JAVA NAME 'oracle.apps.cln.m4u.SchemaUtil.validation(oracle.sql.CHAR,oracle.sql.CHAR) returns java.lang.String';
271
272 -- Procedure for Schema Validation
273 -- Makes Java API call top SchemaUtil.validation
274 -- Requires XDK 9.0.4 version or above to be installed in APPS schema
275 PROCEDURE schema_validation(a_xml IN VARCHAR2,
276 x_ret_sts OUT NOCOPY VARCHAR2,
277 x_ret_msg OUT NOCOPY VARCHAR2)
278 IS
279 l_ret_msg VARCHAR2(4000);
280 l_progress VARCHAR2(4000);
281 BEGIN
282 IF g_log_lvl <= 2 THEN
283 cln_debug_pub.add('Entering m4u_xml_extn_utils.schema_validation',2);
284 END IF;
285
286 l_progress := 'XML XSD validation';
287
288 -- make call to Java API to validate XML
289 l_ret_msg := java_schema_validate(a_xml,g_hdr_rec.dtd_schma_loc);
290
291 -- Return=success, return success
292 IF l_ret_msg = 'SUCCESS' THEN
293 x_ret_msg := NULL;
294 x_ret_sts := g_success_code;
295 ELSE
296 -- Return=failure, return translatable user message
297 FND_MESSAGE.SET_NAME('CLN','M4U_XFWK_VALDTN_ERR');
298 FND_MESSAGE.SET_TOKEN('VALIDATION_TYPE','XSD');
299 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
300 FND_MESSAGE.SET_TOKEN('ERRMESG',SQLERRM);
301
302 x_ret_msg := FND_MESSAGE.GET;
303 x_ret_sts := g_err_code;
304
305 END IF;
306 -- Done, bye
307 IF g_log_lvl <= 2 THEN
308 cln_debug_pub.add('Exiting m4u_xml_extn_utils.schema_validation - Normal',2);
309 cln_debug_pub.add('x_ret_msg - ' || x_ret_msg,2);
310 cln_debug_pub.add('x_ret_sts - ' || x_ret_sts,2);
311 END IF;
312
313 RETURN;
314
315 EXCEPTION
316 WHEN OTHERS THEN
317 handle_exception(SQLCODE,SQLERRM,l_progress,'m4u_xml_extn_utils.schema_validation',
318 x_ret_sts,x_ret_msg);
319 END schema_validation;
320
321 -- Checks if fragment produced is a "Well-formed"XML document
322 -- Creates DOM instance and returns any error
323 -- No "validation" performed
324 PROCEDURE no_validation(a_xml IN VARCHAR2,
325 x_ret_sts OUT NOCOPY VARCHAR2,
326 x_ret_msg OUT NOCOPY VARCHAR2)
327 IS
328 l_parser xmlparser.Parser;
329 l_progress VARCHAR2(4000);
330 BEGIN
331 IF g_log_lvl <= 2 THEN
332 cln_debug_pub.add('Entering m4u_xml_extn_utils.no_validation',2);
333 END IF;
334
335 l_progress := 'XML well-formedness check';
336
337 BEGIN
338
339 --Obtain parser instance
340 l_parser := xmlparser.newparser;
341
342 IF g_log_lvl <= 1 THEN
343 cln_debug_pub.add('xmlparser.parser instance obtained',1);
344 END IF;
345
346 -- Parse XML buffer
347 xmlparser.parseBuffer(l_parser,a_xml);
351 END IF;
348
349 IF g_log_lvl <= 1 THEN
350 cln_debug_pub.add('xmlparser.parseBuffer successful',1);
352
353 -- Free parser
354 xmlparser.freeParser(l_parser);
355
356 IF g_log_lvl <= 1 THEN
357 cln_debug_pub.add('xmlparser.freeParser successful',1);
358 END IF;
359
360 -- set retcode to success
361 x_ret_sts := g_success_code;
362 x_ret_msg := NULL;
363
364 EXCEPTION
365 WHEN OTHERS THEN
366 -- creates and return translatable error
367 FND_MESSAGE.SET_NAME('CLN','M4U_XFWK_VALDTN_ERR');
368 FND_MESSAGE.SET_TOKEN('VALIDATION_TYPE','NONE');
369 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
370 FND_MESSAGE.SET_TOKEN('ERRMESG',SQLERRM);
371
372 x_ret_msg := FND_MESSAGE.GET;
373 x_ret_sts := g_err_code;
374 END;
375
376 -- Done, bye
377 IF g_log_lvl <= 2 THEN
378 cln_debug_pub.add('Exiting m4u_xml_extn_utils.no_validation - Normal',2);
379 cln_debug_pub.add('x_ret_msg - ' || x_ret_msg,2);
380 cln_debug_pub.add('x_ret_sts - ' || x_ret_sts,2);
381 END IF;
382
383 RETURN;
384
385 EXCEPTION
386 WHEN OTHERS THEN
387 handle_exception(SQLCODE,SQLERRM,l_progress,'m4u_xml_extn_utils.no_validation',
388 x_ret_sts,x_ret_msg);
389 END no_validation;
390
391
392 -- Generic validation procedure
393 -- XSD validation - Requires XDK .9.0.4 in APPS schema
394 -- DTD validation - Not tested
395 -- Defaulting validation to NONE for current release
396 PROCEDURE validate(
397 a_xml IN VARCHAR2,
398 x_valdtn_sts OUT NOCOPY VARCHAR2,
399 x_valdtn_msg OUT NOCOPY VARCHAR2,
400 x_api_ret_sts OUT NOCOPY VARCHAR2,
401 x_api_ret_msg OUT NOCOPY VARCHAR2)
402 AS
403 l_progress VARCHAR2(4000);
404 BEGIN
405
406 IF g_log_lvl <= 2 THEN
407 cln_debug_pub.add('Entering m4u_xml_extn_utils.validate',2);
408 cln_debug_pub.add('g_hdr_rec.valdtn_typ - ' || g_hdr_rec.valdtn_typ,2);
409 END IF;
410
411 -- Default to NONE.
412 g_hdr_rec.valdtn_typ := c_valdtn_none;
413 l_progress := 'XML validation';
414
415 x_valdtn_msg := NULL;
416 x_valdtn_sts := g_success_code;
417 x_api_ret_sts := g_success_code;
418 x_api_ret_msg := NULL;
419
420 -- Based on validation type call required procedure
421 -- x_valdtn_sts contains validation status
422 -- x_valdtn_msg contains validation error
423 -- x_api_ret_msg contains other errors in procedure
424 IF g_hdr_rec.valdtn_typ = c_valdtn_dtd THEN
425
426 dtd_validation(a_xml,x_valdtn_sts,x_valdtn_msg);
427 ELSIF g_hdr_rec.valdtn_typ = c_valdtn_xsd THEN
428
429 schema_validation(a_xml,x_valdtn_sts,x_valdtn_msg);
430 ELSIF g_hdr_rec.valdtn_typ = c_valdtn_none THEN
431
432 no_validation(a_xml,x_valdtn_sts,x_valdtn_msg);
433 ELSE
434 FND_MESSAGE.SET_NAME('CLN','M4U_XFWK_INV_PARAM');
435 FND_MESSAGE.SET_TOKEN('PARAM','M4U_XML_EXTENSIONS.VALIDATION_TYPE');
436 FND_MESSAGE.SET_TOKEN('VALUE',g_hdr_rec.valdtn_typ);
437 x_api_ret_msg := FND_MESSAGE.GET;
438 END IF;
439
440 IF g_log_lvl <= 2 THEN
441 cln_debug_pub.add('Exiting m4u_xml_extn_utils.validate - Normal',2);
442 cln_debug_pub.add('x_api_ret_msg - ' || x_api_ret_msg,1);
443 cln_debug_pub.add('x_api_ret_sts - ' || x_api_ret_sts,1);
444 cln_debug_pub.add('x_valdtn_ret_msg - ' || x_valdtn_msg,1);
445 cln_debug_pub.add('x_valdtn_ret_sts - ' || x_valdtn_sts,1);
446 END IF;
447
448 RETURN;
449
450 EXCEPTION
451 WHEN OTHERS THEN
452 handle_exception(SQLCODE,SQLERRM,l_progress,'m4u_xml_extn_utils.validate',
453 x_api_ret_sts,x_api_ret_msg);
454 END validate;
455
456
457 /*FUNCTIONS/PROCEDURES DEFINED BELOW ARE USED IN LOADING MAPPING */
458 /*META-DATA INTO MEMORY DATA-STRUCTURES */
459 -- called by init map, loads header info to memory
460 -- if a_dflt_tp = true then look for M4U_DEFAULT_TP if
461 -- tp specific mapping is not present
462 -- if a_dflt_tp = false then only look for tp specific mapping
463 PROCEDURE init_hdr(
464 a_extn_name IN VARCHAR2,
465 a_tp_id IN VARCHAR2,
466 a_dflt_tp IN BOOLEAN,
467 x_ret_sts OUT NOCOPY VARCHAR2,
471 l_progress VARCHAR2(4000);
468 x_ret_msg OUT NOCOPY VARCHAR2)
469 AS
470 l_extn_found BOOLEAN;
472 BEGIN
473 l_progress := 'Load header information';
474 -- log inputs
475 IF g_log_lvl <= 2 THEN
476 cln_debug_pub.add('Entering m4u_xml_extn_utils.init_hdr',2);
477 cln_debug_pub.add('a_extn_name - ' || a_extn_name,2);
478 cln_debug_pub.add('a_tp_id - ' || a_tp_id,2);
479 IF a_dflt_tp THEN
480 cln_debug_pub.add('a_dflt_tp - ' || 'Y',2);
481 ELSE
482 cln_debug_pub.add('a_dflt_tp - ' || 'N',2);
483 END IF;
484 END IF;
485
486 l_extn_found := false;
487
488 BEGIN
489 -- Query mapping for corresponding to trading_partner_id = a_tp_id
490 -- set l_extn_found = true if data is found else false
491 SELECT UPPER(TRIM(extn_name)), TRIM(trading_partner_id),
492 UPPER(TRIM(validation_type)), dtd_or_schema_location,
493 root_node, base_dir
494 INTO g_hdr_rec.extn_name, g_hdr_rec.tp_id, g_hdr_rec.valdtn_typ, g_hdr_rec.dtd_schma_loc,
495 g_hdr_rec.xml_root_node, g_hdr_rec.dtd_base_dir
496 FROM m4u_xml_extensions
497 WHERE extn_name = a_extn_name
498 AND trading_partner_id = a_tp_id;
499 l_extn_found := true;
500
501 IF g_log_lvl <= 1 THEN
502 cln_debug_pub.add('TP specific mapping found',1);
503 END IF;
504
505 EXCEPTION
506 WHEN NO_DATA_FOUND THEN
507 l_extn_found := false;
508 IF g_log_lvl <= 1 THEN
509 cln_debug_pub.add('TP specific mapping not-found',1);
510 END IF;
511 END;
512
513
514 -- If TP defaulting is allowed look for default tp mapping
515 IF l_extn_found = false and a_dflt_tp = true THEN
516 BEGIN
517 -- Query mapping for corresponding to M4U_DEFAULT_TP
518 SELECT UPPER(TRIM(extn_name)), TRIM(trading_partner_id),
519 UPPER(TRIM(validation_type)), dtd_or_schema_location,
520 root_node, base_dir
521 INTO g_hdr_rec.extn_name, g_hdr_rec.tp_id, g_hdr_rec.valdtn_typ, g_hdr_rec.dtd_schma_loc,
522 g_hdr_rec.xml_root_node, g_hdr_rec.dtd_base_dir
523 FROM m4u_xml_extensions
524 WHERE extn_name = a_extn_name
525 AND trading_partner_id = 'M4U_DEFAULT_TP';
526 IF g_log_lvl <= 1 THEN
527 cln_debug_pub.add('Default mapping found',1);
528 END IF;
529 l_extn_found := true;
530 EXCEPTION
531 WHEN NO_DATA_FOUND THEN
532 l_extn_found := false;
533 IF g_log_lvl <= 1 THEN
534 cln_debug_pub.add('Default mapping not-found',1);
535 END IF;
536 END;
537 END IF;
538
539 -- header information is loaded
540 -- return success
541 IF l_extn_found THEN
542 x_ret_msg := NULL;
543 x_ret_sts := g_success_code;
544 ELSE
545 -- neither tp specific nor default mapping found
546 -- return failue
547 FND_MESSAGE.SET_NAME('CLN','M4U_XFWK_NO_MAP');
548 FND_MESSAGE.SET_TOKEN('EXTN_NAME',a_extn_name);
549 FND_MESSAGE.SET_TOKEN('TP_ID',a_tp_id);
550 x_ret_msg := FND_MESSAGE.GET;
551 x_ret_sts := g_err_code;
552 END IF;
553
554 IF g_log_lvl <= 2 THEN
555 cln_debug_pub.add('Exiting m4u_xml_extn_utils.init_hdr - Normal',2);
556 cln_debug_pub.add('x_ret_msg - ' || x_ret_msg,2);
557 cln_debug_pub.add('x_ret_sts - ' || x_ret_sts,2);
558 END IF;
559
560 RETURN;
561
562 EXCEPTION
563 WHEN OTHERS THEN
564 handle_exception(SQLCODE,SQLERRM,l_progress,'m4u_xml_extn_utils.init_hdr',
565 x_ret_sts,x_ret_msg);
566 END init_hdr;
567
568 -- Load data from m4u_element_mappings for
569 -- given extn, tp_id
570 -- store level in g_elmnt_tab index by level-id
571 PROCEDURE load_elmnt_mapping(
572 x_ret_sts OUT NOCOPY VARCHAR2,
573 x_ret_msg OUT NOCOPY VARCHAR2)
574 IS
575
576 l_tmp_rec elmnt_rec_typ;
577 CURSOR l_cur_elmnt_map(
578 a_extn_name IN VARCHAR2,
579 a_tp_id IN VARCHAR2)
580 IS
584 UPPER(TRIM(variable_name)), constant_val
581 SELECT node_id, node_sequence, node_name, UPPER(TRIM(node_type)),
582 level_id, parent_node_id, UPPER(TRIM(mapping_type)),
583 UPPER(TRIM(view_name)), UPPER(TRIM(column_name)), view_level_id,
585 FROM m4u_element_mappings
586 WHERE extn_name = a_extn_name
587 AND NVL(trading_partner_id,'@@') = NVL(a_tp_id,'@@')
588 AND ignore_mapping <> 'Y'
589 ORDER BY node_sequence ASC;
590 l_progress VARCHAR2(4000);
591 BEGIN
592 l_progress := 'Loading m4u_element_mappings';
593 IF g_log_lvl <= 2 THEN
594 cln_debug_pub.add('Entering m4u_xml_extn_utils.load_elmnt_mapping',2);
595 END IF;
596
597 OPEN l_cur_elmnt_map(g_hdr_rec.extn_name,g_hdr_rec.tp_id);
598
599 IF g_log_lvl <= 1 THEN
600 cln_debug_pub.add('cursor processing begins',1);
601 END IF;
602
603 g_elmnt_map.DELETE;
604 IF g_log_lvl <= 1 THEN
605 cln_debug_pub.add('deleted g_elmnt_map',1);
606 END IF;
607 g_elmnt_count := 0;
608
609 LOOP
610
611 IF g_log_lvl <= 1 THEN
612 cln_debug_pub.add('loop> g_elmnt_count - ' || g_elmnt_count,1);
613 END IF;
614
615 -- read record-by-record and fetch into g_elmnt_map;
616 -- read data into memory
617 FETCH l_cur_elmnt_map
618 INTO l_tmp_rec.id, l_tmp_rec.seq, l_tmp_rec.name, l_tmp_rec.type,
619 l_tmp_rec.lvl_id, l_tmp_rec.parent_id, l_tmp_rec.map_typ,
620 l_tmp_rec.view_nam, l_tmp_rec.col, l_tmp_rec.view_lvl,
621 l_tmp_rec.var, l_tmp_rec.const;
622
623
624 EXIT WHEN l_cur_elmnt_map%NOTFOUND;
625
626 g_elmnt_count := g_elmnt_count + 1;
627
628
629 IF g_log_lvl <= 1 THEN
630 cln_debug_pub.add('l_tmp_rec.id - ' || l_tmp_rec.id,1);
631 END IF;
632
633 -- store data by index in order of sequence
634 g_elmnt_map(g_elmnt_count) := l_tmp_rec;
635 IF g_log_lvl <= 1 THEN
636 cln_debug_pub.add('Assinged record',1);
637 END IF;
638 END LOOP;
639
640 --Close open cursor
641 CLOSE l_cur_elmnt_map;
642
643 IF g_log_lvl <= 1 THEN
644 cln_debug_pub.add('Loop end - g_elmnt_count: ' || g_elmnt_count,1);
645 END IF;
646
647 -- Error, no mapping found
648 IF g_elmnt_count <= 0 THEN
649 FND_MESSAGE.SET_NAME('CLN','M4U_XFWK_NO_MAP');
650 FND_MESSAGE.SET_TOKEN('EXTN_NAME',g_hdr_rec.extn_name);
651 FND_MESSAGE.SET_TOKEN('TP_ID',g_hdr_rec.tp_id);
652 x_ret_msg := FND_MESSAGE.GET;
653 x_ret_sts := g_err_code;
654 ELSE
655 x_ret_msg := NULL;
656 x_ret_sts := g_success_code;
657 END IF;
658
659 IF g_log_lvl <= 2 THEN
660 cln_debug_pub.add('Exiting m4u_xml_extn_utils.load_elmnt_mapping - Normal',2);
661 cln_debug_pub.add('x_ret_msg - ' || x_ret_msg,2);
662 cln_debug_pub.add('x_ret_sts - ' || x_ret_sts,2);
663 END IF;
664
665 RETURN;
666
667 EXCEPTION
668 WHEN OTHERS THEN
669 handle_exception(SQLCODE,SQLERRM,l_progress,'m4u_xml_extn_utils.load_elmnt_mapping',
670 x_ret_sts,x_ret_msg);
671 END load_elmnt_mapping;
672
673 -- load level information from db
674 -- do static initialization of level information
675 -- store level in g_lvl_tab index by level-id
676 PROCEDURE load_lvls(x_ret_sts OUT NOCOPY VARCHAR2,
677 x_ret_msg OUT NOCOPY VARCHAR2)
678 IS
679 l_lvl_id NUMBER;
680 l_lvl_rec lvl_rec_typ;
681 CURSOR l_cur_lvls(a_extn_name IN VARCHAR2,
682 a_tp_id IN VARCHAR2)
683 IS
684 SELECT DISTINCT level_id
685 FROM m4u_element_mappings
686 WHERE extn_name = a_extn_name
687 AND NVL(trading_partner_id,'@@') = NVL(a_tp_id,'@@');
688 l_progress VARCHAR2(4000);
689 BEGIN
690 l_progress := 'Load XML levels';
691 IF g_log_lvl <= 2 THEN
692 cln_debug_pub.add('Entering m4u_xml_extn_utils.load_lvls',2);
693 END IF;
694
695 g_lvl_rec_tab.delete;
696
697 IF g_log_lvl <= 1 THEN
698 cln_debug_pub.add('Deleted g_lvl_rec_tab',1);
699 END IF;
700 -- open curosr
701 OPEN l_cur_lvls(g_hdr_rec.extn_name,g_hdr_rec.tp_id);
702
703 LOOP
704 IF g_log_lvl <= 1 THEN
708 FETCH l_cur_lvls
705 cln_debug_pub.add('in the loop',1);
706 END IF;
707
709 INTO l_lvl_id;
710
711 EXIT WHEN l_cur_lvls%NOTFOUND;
712
713 IF g_log_lvl <= 1 THEN
714 cln_debug_pub.add('l_lvl_id - ' || l_lvl_id,1);
715 END IF;
716
717 -- store level-ids into g_lvl_rec_tab
718 l_lvl_rec.id := l_lvl_id;
719 l_lvl_rec.view_count := 0;
720 l_lvl_rec.rpt_count := 1;
721 l_lvl_rec.is_mapped := false;
722 l_lvl_rec.end_tag_stk_ptr:= 0;
723 l_lvl_rec.vals.delete;
724
725 g_lvl_rec_tab(l_lvl_id) := l_lvl_rec;
726
727 IF g_log_lvl <= 1 THEN
728 cln_debug_pub.add('l_lvl_id - ' || l_lvl_id ,1);
729 END IF;
730
731
732 END LOOP;
733
734 -- return success
735 x_ret_sts := g_success_code;
736 x_ret_msg := NULL;
737
738 -- 5299569
739 CLOSE l_cur_lvls;
740
741 IF g_log_lvl <= 2 THEN
742 cln_debug_pub.add('Exiting m4u_xml_extn_utils.load_lvls - Normal',2);
743 cln_debug_pub.add('x_ret_msg - ' || x_ret_msg,2);
744 cln_debug_pub.add('x_ret_sts - ' || x_ret_sts,2);
745 END IF;
746 RETURN;
747
748 EXCEPTION
749 WHEN OTHERS THEN
750 handle_exception(SQLCODE,SQLERRM,l_progress,'m4u_xml_extn_utils.load_lvls',
751 x_ret_sts,x_ret_msg);
752 END load_lvls;
753
754 -- loop through g_lvl_rec_tab
755 -- for each level loop load view data
756 PROCEDURE load_lvl_views(
757 x_ret_sts OUT NOCOPY VARCHAR2,
758 x_ret_msg OUT NOCOPY VARCHAR2
759 )
760 IS
761 l_lvl_id NUMBER;
762 l_view_rec view_rec_typ;
763 l_lvl_rec lvl_rec_typ;
764
765 CURSOR l_cur_lvl_id(
766 a_extn_name IN VARCHAR2,
767 a_tp_id IN VARCHAR2)
768 IS
769 SELECT level_id, view_name, where_clause
770 FROM m4u_level_views
771 WHERE extn_name = a_extn_name
772 AND NVL(trading_partner_id,'@@') = NVL(a_tp_id,'@@');
773 l_progress VARCHAR2(4000);
774 BEGIN
775 l_progress := 'Load view sequels';
776 IF g_log_lvl <= 2 THEN
777 cln_debug_pub.add('Entering m4u_xml_extn_utils.load_lvl_views',2);
778 END IF;
779
780 -- Fetch views corresponding to each level
781 OPEN l_cur_lvl_id(g_hdr_rec.extn_name,g_hdr_rec.tp_id);
782
783 IF g_log_lvl <= 1 THEN
784 cln_debug_pub.add('Cursor opened',1);
785 END IF;
786
787
788 LOOP
789
790 IF g_log_lvl <= 1 THEN
791 cln_debug_pub.add('in the loop',1);
792 END IF;
793
794 -- clean l_view_rec
795 l_view_rec.rowcount := 0;
796 l_view_rec.bind_count := 0;
797 l_view_rec.col_count := 0;
798 l_view_rec.bind_tab.DELETE;
799 l_view_rec.col_tab.DELETE;
800
801 --
802 FETCH l_cur_lvl_id
803 INTO l_lvl_id, l_view_rec.view_nam, l_view_rec.whr_claus;
804
805 EXIT WHEN l_cur_lvl_id%NOTFOUND;
806
807 IF trim(l_view_rec.whr_claus) IS NOT NULL THEN
808 l_view_rec.whr_claus := 'WHERE 1=1 AND ' || l_view_rec.whr_claus;
809 ELSE
810 l_view_rec.whr_claus := 'WHERE 1=1';
811 END IF;
812
813 IF g_log_lvl <= 1 THEN
814 cln_debug_pub.add('Fectched data',1);
815 cln_debug_pub.add('l_lvl_id - ' ||l_lvl_id,1);
816 cln_debug_pub.add('l_view_rec.view_nam - ' ||l_view_rec.view_nam,1);
817 cln_debug_pub.add('l_view_rec.whr_claus - ' ||l_view_rec.whr_claus,1);
818 END IF;
819
820 -- load binding corresponding to l_lvl_id, l_view_rec
821 load_bindings(l_lvl_id, l_view_rec,x_ret_sts,x_ret_msg);
822
823 IF g_log_lvl <= 1 THEN
824 cln_debug_pub.add('returned from load_bindings',1);
825 cln_debug_pub.add('x_ret_sts - ' || x_ret_sts,1);
826 cln_debug_pub.add('x_ret_msg - ' || x_ret_msg,1);
827 END IF;
828
829 IF NVL(x_ret_sts,'F') <> g_success_code THEN
830 EXIT;
831 END IF;
832
833 -- load view_cols corresponding to l_lvl_id, l_view_rec
837 cln_debug_pub.add('returned from load_view_cols',1);
834 load_view_cols(l_lvl_id, l_view_rec,x_ret_sts,x_ret_msg);
835
836 IF g_log_lvl <= 1 THEN
838 cln_debug_pub.add('x_ret_sts - ' || x_ret_sts,1);
839 cln_debug_pub.add('x_ret_msg - ' || x_ret_msg,1);
840 cln_debug_pub.add('l_view_rec.exec_sql - ' || l_view_rec.exec_sql,1);
841 END IF;
842
843 IF NVL(x_ret_sts,'F') <> g_success_code THEN
844 EXIT;
845 END IF;
846
847 l_lvl_rec := g_lvl_rec_tab(l_lvl_id);
848 IF g_log_lvl <= 1 THEN
849 cln_debug_pub.add('Obtained l_lvl_rec',1);
850 END IF;
851 l_lvl_rec.view_tab(l_lvl_rec.view_count+1) := l_view_rec;
852 IF g_log_lvl <= 1 THEN
853 cln_debug_pub.add('set view_rec',1);
854 END IF;
855 l_lvl_rec.is_mapped := true;
856 l_lvl_rec.view_count := l_lvl_rec.view_count + 1;
857 l_lvl_rec.rpt_count := 0;
858 g_lvl_rec_tab(l_lvl_id) := l_lvl_rec;
859
860 IF g_log_lvl <= 1 THEN
861 cln_debug_pub.add('l_lvl_rec.id - ' || l_lvl_rec.id,1);
862 cln_debug_pub.add('l_lvl_rec.view_count - ' || l_lvl_rec.view_count,1);
863 END IF;
864
865
866 END LOOP;
867
868 IF g_log_lvl <= 1 THEN
869 cln_debug_pub.add('Closing cursor - ' || l_lvl_rec.view_count,1);
870 END IF;
871
872 CLOSE l_cur_lvl_id;
873
874 x_ret_sts := g_success_code;
875 x_ret_msg := NULL;
876
877 IF g_log_lvl <= 2 THEN
878 cln_debug_pub.add('Exiting m4u_xml_extn_utils.load_lvl_views - Normal',2);
879 cln_debug_pub.add('x_ret_msg - ' || x_ret_msg,2);
880 cln_debug_pub.add('x_ret_sts - ' || x_ret_sts,2);
881 END IF;
882
883 RETURN;
884
885 EXCEPTION
886 WHEN OTHERS THEN
887 handle_exception(SQLCODE,SQLERRM,l_progress,'m4u_xml_extn_utils.load_lvl_views',
888 x_ret_sts,x_ret_msg);
889 END load_lvl_views;
890
891 -- load list of columns to be fetched for each view
892 -- create exec_sql dynamically
893 PROCEDURE load_view_cols(
894 a_lvl_id IN NUMBER,
895 x_view_rec IN OUT NOCOPY view_rec_typ,
896 x_ret_sts OUT NOCOPY VARCHAR2,
897 x_ret_msg OUT NOCOPY VARCHAR2)
898 IS
899 CURSOR l_cur_view_cols(
900 a_extn_name IN VARCHAR2,
901 a_tp_id IN VARCHAR2,
902 a_lvl_id IN VARCHAR2,
903 a_view_nam IN VARCHAR2
904 )
905 IS
906 SELECT DISTINCT UPPER(TRIM(column_name)) col
907 FROM m4u_element_mappings
908 WHERE extn_name = a_extn_name AND NVL(trading_partner_id,'@@') = a_tp_id
909 AND view_level_id = a_lvl_id AND view_name = a_view_nam
910 AND TRIM(column_name) IS NOT NULL
911 UNION
912 SELECT DISTINCT UPPER(TRIM(source_column)) col
913 FROM m4u_view_binding
914 WHERE extn_name = a_extn_name AND NVL(trading_partner_id,'@@') = a_tp_id
915 AND source_level_id = a_lvl_id AND source_view = a_view_nam
916 AND TRIM(source_column) IS NOT NULL;
917
918
919 l_col_nam m4u_element_mappings.column_name%TYPE;
920 i NUMBER;
921 l_progress VARCHAR2(4000);
922 BEGIN
923 l_progress := 'Load columns for view sequels';
924
925 IF g_log_lvl <= 2 THEN
926 cln_debug_pub.add('Entering m4u_xml_extn_utils.load_view_cols',2);
927 cln_debug_pub.add('a_lvl_id - ' || a_lvl_id,2);
928 cln_debug_pub.add('x_view_rec.view_nam - ' || x_view_rec.view_nam,2);
929 END IF;
930
931 OPEN l_cur_view_cols(g_hdr_rec.extn_name,g_hdr_rec.tp_id,a_lvl_id,x_view_rec.view_nam);
932
933 IF g_log_lvl <= 1 THEN
934 cln_debug_pub.add('Cursor opened',1);
935 END IF;
936
937 x_view_rec.col_tab.delete;
938 IF g_log_lvl <= 1 THEN
939 cln_debug_pub.add('Delete - x_view_rec.col_tab',1);
940 END IF;
941 -- have minimum of 1 column
942 -- create SQL statement while looping through column list
943 x_view_rec.col_tab(1) := '1';
944 x_view_rec.col_count := 1;
945 x_view_rec.exec_sql := 'SELECT 1';
946
947 LOOP
948
949 IF g_log_lvl <= 1 THEN
953 FETCH l_cur_view_cols
950 cln_debug_pub.add('in the loop - ' || x_view_rec.col_count,1);
951 END IF;
952
954 INTO l_col_nam;
955
956 EXIT WHEN l_cur_view_cols%NOTFOUND;
957
958
959 IF g_log_lvl <= 1 THEN
960 cln_debug_pub.add('Fectched data',1);
961 cln_debug_pub.add('l_col_nam - ' ||l_col_nam,1);
962 END IF;
963
964
965 x_view_rec.col_count := x_view_rec.col_count+1;
966 x_view_rec.col_tab(x_view_rec.col_count) := l_col_nam;
967
968 x_view_rec.exec_sql := x_view_rec.exec_sql || ', ' || l_col_nam;
969
970
971
972 END LOOP;
973
974 x_view_rec.exec_sql := x_view_rec.exec_sql || ' FROM ' || x_view_rec.view_nam || ' V';
975 x_view_rec.exec_sql := x_view_rec.exec_sql || ' ' || x_view_rec.whr_claus;
976
977 IF g_log_lvl <= 1 THEN
978 cln_debug_pub.add('x_view_rec.col_count - '|| x_view_rec.col_count,1);
979 cln_debug_pub.add('x_view_rec.exec_sql - '|| x_view_rec.exec_sql ,1);
980 cln_debug_pub.add('Closing cursor',1);
981 END IF;
982
983
984
985 x_ret_msg := NULL;
986 x_ret_sts := g_success_code;
987
988 CLOSE l_cur_view_cols;
989
990 IF g_log_lvl <= 2 THEN
991 cln_debug_pub.add('Exiting m4u_xml_extn_utils.load_view_cols - Normal',2);
992 cln_debug_pub.add('x_ret_msg - ' || x_ret_msg,2);
993 cln_debug_pub.add('x_ret_sts - ' || x_ret_sts,2);
994 END IF;
995
996 RETURN;
997
998 EXCEPTION
999 WHEN OTHERS THEN
1000 handle_exception(SQLCODE,SQLERRM,l_progress,'m4u_xml_extn_utils.load_view_cols',
1001 x_ret_sts,x_ret_msg);
1002 END load_view_cols;
1003
1004 -- load list of bind variable to be supplied for each where clause
1005 -- load mapping info for each bind variable
1006 PROCEDURE load_bindings(
1007 a_lvl_id IN NUMBER,
1008 x_view_rec IN OUT NOCOPY view_rec_typ,
1009 x_ret_sts OUT NOCOPY VARCHAR2,
1010 x_ret_msg OUT NOCOPY VARCHAR2
1011 )
1012 IS
1013 CURSOR l_cur_bindngs(
1014 a_extn_name IN VARCHAR2,
1015 a_tp_id IN VARCHAR2,
1016 a_lvl_id IN NUMBER,
1017 a_view_nam IN VARCHAR2)
1018 IS
1019 SELECT TRIM(bind_variable), UPPER(TRIM(bind_type)),
1020 UPPER(TRIM(source_view)), UPPER(TRIM(source_column)),
1021 UPPER(TRIM(source_var)), source_level_id
1022 FROM m4u_view_binding
1023 WHERE extn_name = a_extn_name
1024 AND NVL(trading_partner_id,'@@') = NVL(a_tp_id,'@@')
1025 AND view_name = a_view_nam
1026 AND level_id = a_lvl_id;
1027
1028 l_bind_rec bind_rec_typ;
1029 l_progress VARCHAR2(4000);
1030
1031 BEGIN
1032 l_progress := 'Load bind-information for view sequels';
1033
1034 IF g_log_lvl <= 2 THEN
1035 cln_debug_pub.add('Entering m4u_xml_extn_utils.load_bindings',2);
1036 END IF;
1037
1038 OPEN l_cur_bindngs(g_hdr_rec.extn_name,g_hdr_rec.tp_id,a_lvl_id,x_view_rec.view_nam);
1039
1040 IF g_log_lvl <= 1 THEN
1041 cln_debug_pub.add('cursor opened',1);
1042 END IF;
1043
1044 x_view_rec.bind_tab.delete;
1045 IF g_log_lvl <= 1 THEN
1046 cln_debug_pub.add('bind_tab deleted',1);
1047 END IF;
1048
1049 x_view_rec.bind_count := 0;
1050 LOOP
1051 IF g_log_lvl <= 1 THEN
1052 cln_debug_pub.add('in the loop - ' || x_view_rec.bind_count,1);
1053 END IF;
1054
1055 FETCH l_cur_bindngs
1056 INTO l_bind_rec.nam, l_bind_rec.typ, l_bind_rec.src_view, l_bind_rec.src_col,
1057 l_bind_rec.src_var, l_bind_rec.src_lvl_id;
1058
1059 EXIT WHEN l_cur_bindngs%NOTFOUND;
1060
1061 IF g_log_lvl <= 1 THEN
1062 cln_debug_pub.add('Fectched data',1);
1063 cln_debug_pub.add('l_bind_rec.nam - ' ||l_bind_rec.nam,1);
1064 cln_debug_pub.add('l_bind_rec.typ - ' ||l_bind_rec.typ,1);
1065 cln_debug_pub.add('l_bind_rec.src_view - ' ||l_bind_rec.src_view,1);
1066 cln_debug_pub.add('l_bind_rec.src_col - ' ||l_bind_rec.src_col,1);
1067 cln_debug_pub.add('l_bind_rec.src_var - ' ||l_bind_rec.src_var,1);
1068 cln_debug_pub.add('l_bind_rec.src_lvl_id - ' ||l_bind_rec.src_lvl_id,1);
1069 END IF;
1070
1074 END LOOP;
1071 x_view_rec.bind_count := x_view_rec.bind_count + 1;
1072
1073 x_view_rec.bind_tab(x_view_rec.bind_count) := l_bind_rec;
1075
1076 IF g_log_lvl <= 1 THEN
1077 cln_debug_pub.add('Closing cursor - ' || x_view_rec.bind_count,1);
1078 END IF;
1079
1080 CLOSE l_cur_bindngs;
1081
1082 x_ret_sts := g_success_code;
1083 x_ret_msg := NULL;
1084
1085 IF g_log_lvl <= 2 THEN
1086 cln_debug_pub.add('Exiting m4u_xml_extn_utils.load_bindings - Normal',2);
1087 cln_debug_pub.add('x_ret_msg - ' || x_ret_msg,2);
1088 cln_debug_pub.add('x_ret_sts - ' || x_ret_sts,2);
1089 END IF;
1090
1091 RETURN;
1092
1093
1094 EXCEPTION
1095 WHEN OTHERS THEN
1096 handle_exception(SQLCODE,SQLERRM,l_progress,'m4u_xml_extn_utils.load_bindings',
1097 x_ret_sts,x_ret_msg);
1098 END load_bindings;
1099
1100 -- load global variable from input wf_parameter_list_t
1101 -- into index by VARCHAR2(varname) table
1102 -- variable names are case-insensitive
1103 PROCEDURE load_global_var(a_param_lst IN wf_parameter_list_t,
1104 x_ret_sts OUT NOCOPY VARCHAR2,
1105 x_ret_msg OUT NOCOPY VARCHAR2)
1106 IS
1107 i NUMBER;
1108 l_nam VARCHAR2(100);
1109 l_val VARCHAR2(4000);
1110 l_progress VARCHAR2(4000);
1111 BEGIN
1112 l_progress := 'Read global variable for mapping';
1113 IF g_log_lvl <= 2 THEN
1114 cln_debug_pub.add('Entering m4u_xml_extn_utils.load_global_var',2);
1115 END IF;
1116
1117 -- delete any old-data
1118 g_glb_var_tab.DELETE;
1119 IF g_log_lvl <= 1 THEN
1120 cln_debug_pub.add('Deleted g_glb_var_tab',1);
1121 END IF;
1122
1123 i := 0;
1124 WHILE i < a_param_lst.count LOOP
1125 l_nam := a_param_lst(i+1).getName();
1126 l_val := a_param_lst(i+1).getValue();
1127
1128 IF g_log_lvl <= 1 THEN
1129 cln_debug_pub.add('i - ' || i,1);
1130 cln_debug_pub.add('l_nam - ' || l_nam,1);
1131 cln_debug_pub.add('l_val - ' || l_val,1);
1132 END IF;
1133
1134 g_glb_var_tab(UPPER(l_nam)) := l_val;
1135
1136 i := i+1;
1137 END LOOP;
1138
1139 -- return success
1140 x_ret_msg := NULL;
1141 x_ret_sts := g_success_code;
1142
1143 IF g_log_lvl <= 2 THEN
1144 cln_debug_pub.add('Exiting m4u_xml_extn_utils.load_global_var - Normal',2);
1145 cln_debug_pub.add('x_ret_msg - ' || x_ret_msg,2);
1146 cln_debug_pub.add('x_ret_sts - ' || x_ret_sts,2);
1147 END IF;
1148 EXCEPTION
1149 WHEN OTHERS THEN
1150 handle_exception(SQLCODE,SQLERRM,l_progress,'m4u_xml_extn_utils.load_global_var',
1151 x_ret_sts,x_ret_msg);
1152 END load_global_var;
1153
1154 -- init map
1155 -- 1. load header info from m4u_xml_extensions
1156 -- 2. element mapping from m4u_element_mappings
1157 -- 3. level-ids from m4u_element_mappings
1158 -- 4. views for each level
1159 -- 5. columns for each view
1160 -- 6. bind variable and mapping info for each view
1161 PROCEDURE init_map(a_extn_name IN VARCHAR2,
1162 a_tp_id IN VARCHAR2,
1163 a_dflt_tp IN BOOLEAN,
1164 a_param_list IN wf_parameter_list_t,
1165 a_log_lvl IN NUMBER,
1166 x_ret_sts OUT NOCOPY VARCHAR2,
1167 x_ret_msg OUT NOCOPY VARCHAR2)
1168
1169 IS
1170 l_progress VARCHAR2(2000);
1171 BEGIN
1172 -- a_log_lvl is supplied override profile value
1173 g_log_lvl := NVL(a_log_lvl,g_log_lvl);
1174 -- log inputs
1175 IF g_log_lvl <= 2 THEN
1176 cln_debug_pub.add('Entering m4u_xml_extn_utils.init_map',2);
1177 cln_debug_pub.add('a_extn_name - ' || a_extn_name,2);
1178 cln_debug_pub.add('a_tp_id - ' || a_tp_id,2);
1179 IF a_dflt_tp THEN
1180 cln_debug_pub.add('a_dflt_tp - ' || 'Y',2);
1181 ELSE
1182 cln_debug_pub.add('a_dflt_tp - ' || 'N',2);
1183 END IF;
1184 END IF;
1185
1186 l_progress := 'Initialize XML mapping information';
1187
1188 -- load header data
1189 init_hdr(a_extn_name,a_tp_id,a_dflt_tp,x_ret_sts,x_ret_msg);
1190 IF g_log_lvl <= 1 THEN
1191 cln_debug_pub.add('Returned from m4u_xml_extn_utils.init_hdr',1);
1192 cln_debug_pub.add('x_ret_sts - ' || x_ret_sts, 1);
1193 cln_debug_pub.add('x_ret_msg - ' || x_ret_msg, 1);
1194 END IF;
1198 IF x_ret_sts = g_success_code THEN
1195
1196 -- load element mapping
1197 l_progress := 'Read from m4u_element_mappings';
1199 load_elmnt_mapping(x_ret_sts,x_ret_msg);
1200 IF g_log_lvl <= 1 THEN
1201 cln_debug_pub.add('Returned from m4u_xml_extn_utils.load_elmnt_mapping',1);
1202 cln_debug_pub.add('x_ret_sts - ' || x_ret_sts, 1);
1203 cln_debug_pub.add('x_ret_msg - ' || x_ret_msg, 1);
1204 END IF;
1205 END IF;
1206
1207
1208 -- load level ids, initial g_lvl_rec.tab
1209 l_progress := 'Load XML levels';
1210 IF x_ret_sts = g_success_code THEN
1211 load_lvls(x_ret_sts,x_ret_msg);
1212
1213 IF g_log_lvl <= 1 THEN
1214 cln_debug_pub.add('Returned from m4u_xml_extn_utils.load_lvls',1);
1215 cln_debug_pub.add('x_ret_sts - ' || x_ret_sts, 1);
1216 cln_debug_pub.add('x_ret_msg - ' || x_ret_msg, 1);
1217 END IF;
1218 END IF;
1219
1220
1221 -- load views for each level
1222 l_progress := 'Load DB view queries';
1223 IF x_ret_sts = g_success_code THEN
1224 load_lvl_views(x_ret_sts,x_ret_msg);
1225 IF g_log_lvl <= 1 THEN
1226 cln_debug_pub.add('Returned from m4u_xml_extn_utils.load_lvl_views',1);
1227 cln_debug_pub.add('x_ret_sts - ' || x_ret_sts, 1);
1228 cln_debug_pub.add('x_ret_msg - ' || x_ret_msg, 1);
1229 END IF;
1230 END IF;
1231
1232 -- load global vairables from a_param_list
1233 l_progress := 'Read global variable for mapping';
1234 IF x_ret_sts = g_success_code THEN
1235 load_global_var(a_param_list,x_ret_sts,x_ret_msg);
1236
1237 IF g_log_lvl <= 1 THEN
1238 cln_debug_pub.add('Returned from m4u_xml_extn_utils.load_global_var',1);
1239 cln_debug_pub.add('x_ret_sts - ' || x_ret_sts, 1);
1240 cln_debug_pub.add('x_ret_msg - ' || x_ret_msg, 1);
1241 END IF;
1242 END IF;
1243
1244 -- Done! all mapping information is in-memory
1245 -- ready to being processing
1246 IF g_log_lvl <= 2 THEN
1247 cln_debug_pub.add('Exiting m4u_xml_extn_utils.init_map - Normal',2);
1248 cln_debug_pub.add('x_ret_msg - ' || x_ret_msg,2);
1249 cln_debug_pub.add('x_ret_sts - ' || x_ret_sts,2);
1250 END IF;
1251
1252 EXCEPTION
1253 WHEN OTHERS THEN
1254 handle_exception(SQLCODE,SQLERRM,l_progress,'m4u_xml_extn_utils.init_map',
1255 x_ret_sts,x_ret_msg);
1256 END init_map;
1257
1258
1259 -- Helper procedure used in XML generation
1260 -- Purpose is to
1261 -- Find if input level_id is currently stacked on runtime level stack
1262 -- If yes, lookup level record in g_lvl_tab
1263 -- If no, return error_sts and NULL
1264 FUNCTION find_lvl_stack(a_lvl_id IN NUMBER,
1265 x_ret_sts OUT NOCOPY VARCHAR2,
1266 x_ret_msg OUT NOCOPY VARCHAR2)
1267 RETURN lvl_rec_typ
1268 IS
1269 l_idx NUMBER;
1270 l_lvl_id NUMBER;
1271 l_lvl_rec m4u_xml_extn_utils.lvl_rec_typ;
1272 l_progress VARCHAR2(4000);
1273 BEGIN
1274 l_progress := 'Find level on stack - ' || a_lvl_id;
1275 IF g_log_lvl <= 2 THEN
1276 cln_debug_pub.add('Entering m4u_xml_extn.find_lvl_stack', 2);
1277 cln_debug_pub.add('a_lvl_id - ' || a_lvl_id, 2);
1278 END IF;
1279
1280 -- start searching the stack from top
1281 -- g_lvl_stk_ptr points to Top of stack
1282 l_idx := g_lvl_stk_ptr;
1283
1284 IF g_log_lvl <= 1 THEN
1285 cln_debug_pub.add('g_lvl_stk_ptr - ' || g_lvl_stk_ptr,1);
1286 END IF;
1287
1288 -- Loop element by element
1289 WHILE l_idx > 0 LOOP
1290
1291 IF g_log_lvl <= 1 THEN
1292 cln_debug_pub.add('l_idx - ' || l_idx ,1);
1293 END IF;
1294
1295 -- get level_id
1296 l_lvl_id := g_lvl_stk(l_idx);
1297 IF g_log_lvl <= 1 THEN
1298 cln_debug_pub.add('l_lvl_id - ' || l_lvl_id ,1);
1299 END IF;
1300
1301 -- if lvl_id is same as input level
1302 -- lookup level record and return success
1303 IF l_lvl_id = a_lvl_id THEN
1304 l_lvl_rec := m4u_xml_extn_utils.g_lvl_rec_tab(l_lvl_id);
1305 IF g_log_lvl <= 2 THEN
1306 cln_debug_pub.add('Found level rec, exiting',2);
1307 END IF;
1308 x_ret_sts := g_success_code;
1309 x_ret_msg := NULL;
1310 RETURN l_lvl_rec;
1311 END IF;
1312 l_idx := l_idx - 1;
1316 -- return error an null
1313 END LOOP;
1314
1315 -- Did not find level on the stack
1317 x_ret_sts := g_err_code;
1318 x_ret_msg := 'Level not found on stack';
1319 l_lvl_rec := null;
1320
1321
1322 IF g_log_lvl <= 2 THEN
1323 cln_debug_pub.add('m4u_xml_extn.find_lvl_stack exiting - failure' ,2);
1324 END IF;
1325
1326 RETURN l_lvl_rec;
1327 EXCEPTION
1328 WHEN OTHERS THEN
1329 m4u_xml_extn_utils.handle_exception(SQLCODE,SQLERRM,l_progress,
1330 'm4u_xml_extn.find_lvl_stack',x_ret_sts,x_ret_msg);
1331 l_lvl_rec := null;
1332 RETURN l_lvl_rec;
1333 END find_lvl_stack;
1334
1335
1336 /*FUNCTIONS/PROCEDURES DEFINED BELOW ARE USED IN XML GENERATION*/
1337
1338
1339 -- Helper procedure to lookup view value
1340 -- Finds input level on the run-time stack
1341 -- And looks up view.column data corresponding to current
1342 -- iteration of the run-time level.
1343 -- Note, if view returns 5 rows level is iterated 5 times
1344 FUNCTION lookup_view_value( a_view IN VARCHAR2 ,
1345 a_col IN VARCHAR2 ,
1346 a_lvl IN NUMBER ,
1347 x_ret_sts OUT NOCOPY VARCHAR2,
1348 x_ret_msg OUT NOCOPY VARCHAR2 )
1349 RETURN VARCHAR2
1350 IS
1351 l_tmp_val VARCHAR2(32767);
1352 l_lvl_rec lvl_rec_typ;
1353 l_idx NUMBER;
1354 l_size NUMBER;
1355 l_key VARCHAR2(100);
1356 l_progress VARCHAR2(4000);
1357 BEGIN
1358 l_progress := 'Lookup value returned by ' || a_view || '.' || a_col;
1359 -- log inputs
1360 IF g_log_lvl <= 2 THEN
1361 cln_debug_pub.add('Entering m4u_xml_extn_utils.lookup_view_value',2);
1362 cln_debug_pub.add('a_view - ' || a_view, 2);
1363 cln_debug_pub.add('a_col - ' || a_col, 2);
1364 cln_debug_pub.add('a_lvl - ' || a_lvl, 2);
1365 END IF;
1366
1367 -- To fetch view.column data for a level, first find level_record from stack
1368 -- If level is not found, return failure
1369 l_lvl_rec:= find_lvl_stack(a_lvl,x_ret_sts,x_ret_msg);
1370 IF x_ret_sts <> g_success_code THEN
1371 IF g_log_lvl <= 1 THEN
1372 cln_debug_pub.add('find_lvl_stack - returns failure' ,6);
1373 END IF;
1374 l_tmp_val := null;
1375 RETURN l_tmp_val;
1376 END IF;
1377
1378 -- key to find values table for corresponding column
1379 l_key := a_view || '.' || a_col;
1380 -- log current level count and key
1381 IF g_log_lvl <= 1 THEN
1382 cln_debug_pub.add('l_lvl_rec.cntr - ' || l_lvl_rec.cntr,1);
1383 cln_debug_pub.add('key - ' || l_key);
1384 END IF;
1385
1386 BEGIN
1387 l_size := l_lvl_rec.vals(l_key).count;
1388 IF g_log_lvl <= 1 THEN
1389 cln_debug_pub.add('l_size - ' || l_size,1);
1390 END IF;
1391 EXCEPTION
1392 WHEN NO_DATA_FOUND THEN
1393 IF g_log_lvl <= 6 THEN
1394 cln_debug_pub.add('Value table not found for ' || l_key,6);
1395 END IF;
1396 x_ret_sts := g_err_code;
1397 x_ret_msg := NULL;
1398 RAISE;
1399 END;
1400
1401 -- l_lvl_rec.cntr loops from 0 to l_lvl_rec.rpt_count-1
1402 -- return data corresding to l_lvl_rec.cntr rec for the view.
1403 -- if view records coundf < number of repetitions of level
1404 -- (this is possible when a level has multiple views)
1405 -- retrive the last record for view
1406 l_idx := l_lvl_rec.cntr + 1;
1407 IF l_idx > l_size THEN
1408 l_idx := l_size;
1409 END IF;
1410
1411 -- l_idx is now points to the view record being accessed for data
1412 IF g_log_lvl <= 1 THEN
1413 cln_debug_pub.add('l_idx - ' || l_idx,1);
1414 END IF;
1415
1416 -- if l_idx > 0, then lookup value and return
1417 -- else return NULL
1418 IF l_idx > 0 THEN
1419 l_tmp_val := l_lvl_rec.vals(l_key)(l_idx);
1420 ELSE
1421 l_tmp_val := NULL;
1422 END IF;
1423
1424 -- exit with success
1425 x_ret_sts := g_success_code;
1426 x_ret_msg := NULL;
1427
1428 -- log value and return
1429 IF g_log_lvl <= 2 THEN
1430 cln_debug_pub.add('Exiting m4u_xml_extn.lookup_view_value - Normal',2);
1431 cln_debug_pub.add('ret_val - ' || substr(l_tmp_val,1,255), 2);
1432 END IF;
1433
1434 RETURN l_tmp_val;
1435
1436 EXCEPTION
1437 WHEN OTHERS THEN
1441 RETURN l_tmp_val;
1438 m4u_xml_extn_utils.handle_exception(SQLCODE,SQLERRM,l_progress,
1439 'm4u_xml_extn_utils.lookup_view_value',x_ret_sts,x_ret_msg);
1440 l_tmp_val := null;
1442 END lookup_view_value;
1443
1444
1445 -- Helper procedure to pop lvl stack
1446 -- retunrs l_lvl_id popped from runtime level stack
1447 FUNCTION pop_lvl_stack( x_ret_sts OUT NOCOPY VARCHAR2,
1448 x_ret_msg OUT NOCOPY VARCHAR2)
1449 RETURN NUMBER
1450 IS
1451 l_lvl_id NUMBER;
1452 l_progress VARCHAR2(4000);
1453 BEGIN
1454
1455 l_progress := 'Pop level from stack';
1456 IF g_log_lvl <= 2 THEN
1457 cln_debug_pub.add('Entering pop_lvl_stack', 2);
1458 cln_debug_pub.add('g_lvl_stk_ptr - ' || g_lvl_stk_ptr, 2);
1459 END IF;
1460
1461 -- stack is empty return NULL
1462 -- else, return TOS and decrement stack
1463 IF g_lvl_stk_ptr < 0 THEN
1464 IF g_log_lvl <= 1 THEN
1465 cln_debug_pub.add('g_lvl_stk_ptr < 0',1);
1466 END IF;
1467 l_lvl_id := null;
1468 ELSE
1469 l_lvl_id := g_lvl_stk(g_lvl_stk_ptr);
1470 g_lvl_stk_ptr := g_lvl_stk_ptr - 1;
1471 END IF;
1472
1473 -- Done
1474 IF g_log_lvl <= 2 THEN
1475 cln_debug_pub.add('Exiting pop_lvl_stack - Normal', 2);
1476 cln_debug_pub.add('l_lvl_id - ' || l_lvl_id, 2);
1477 cln_debug_pub.add('g_lvl_stk_ptr - ' || g_lvl_stk_ptr, 2);
1478 END IF;
1479
1480 x_ret_sts := g_success_code;
1481 x_ret_msg := NULL;
1482 RETURN l_lvl_id;
1483
1484 EXCEPTION
1485 WHEN OTHERS THEN
1486 m4u_xml_extn_utils.handle_exception(SQLCODE,SQLERRM,l_progress,
1487 'm4u_xml_extn.pop_lvl_stack',x_ret_sts,x_ret_msg);
1488 END pop_lvl_stack;
1489
1490
1491 -- Push input level id to top of run-time stack
1492 PROCEDURE push_lvl_stack(l_lvl_id IN NUMBER,
1493 x_ret_sts OUT NOCOPY VARCHAR2,
1494 x_ret_msg OUT NOCOPY VARCHAR2)
1495 IS
1496 l_progress VARCHAR2(4000);
1497 BEGIN
1498 l_progress := 'Push level to stack - ' || l_lvl_id;
1499
1500 IF g_log_lvl <= 2 THEN
1501 cln_debug_pub.add('Entering push_lvl_stack', 2);
1502 cln_debug_pub.add('l_lvl_id - '|| l_lvl_id,2);
1503 cln_debug_pub.add('g_lvl_stk_ptr - '|| g_lvl_stk_ptr,2);
1504 END IF;
1505
1506
1507 g_lvl_stk_ptr := g_lvl_stk_ptr + 1;
1508 g_lvl_stk(g_lvl_stk_ptr) := l_lvl_id;
1509
1510 x_ret_sts := g_success_code;
1511 x_ret_msg := NULL;
1512 RETURN;
1513
1514 IF g_log_lvl <= 2 THEN
1515 cln_debug_pub.add('Exiting push_lvl_stack', 2);
1516 cln_debug_pub.add('g_lvl_stk_ptr - ' || g_lvl_stk_ptr, 2);
1517 END IF;
1518
1519 EXCEPTION
1520 WHEN OTHERS THEN
1521 m4u_xml_extn_utils.handle_exception(SQLCODE,SQLERRM,l_progress,
1522 'm4u_xml_extn.push_lvl_stack',x_ret_sts,x_ret_msg);
1523 END push_lvl_stack;
1524
1525 -- Get value for bind-variable
1526 -- Mapping for bind-variables is from m4u_view_binding
1527 -- This function is called for each bind variable
1528 FUNCTION get_bind_value(
1529 a_bind_rec IN bind_rec_typ,
1530 x_ret_sts OUT NOCOPY VARCHAR2,
1531 x_ret_msg OUT NOCOPY VARCHAR2)
1532 RETURN VARCHAR2 AS
1533 l_tmp_val VARCHAR2(32767);
1534 l_progress VARCHAR2(4000);
1535 BEGIN
1536 l_progress := 'Fetch value of bind-variable - ' || a_bind_rec.nam;
1537 -- Log input api parameters
1538 IF g_log_lvl <= 2 THEN
1539 cln_debug_pub.add('Entering m4u_xml_extn.get_bind_value',2);
1540 cln_debug_pub.add('a_bind_rec.nam - ' || a_bind_rec.nam,2);
1541 cln_debug_pub.add('a_bind_rec.typ - ' || a_bind_rec.typ,2);
1542 END IF;
1543
1544 l_tmp_val := NULL;
1545
1546 -- conditional processing based on a_bind_rec.typ
1547 IF a_bind_rec.typ = m4u_xml_extn_utils.c_maptyp_var THEN
1548
1549 -- Lookup global variable table
1550 -- if global variable table does not contain
1551 -- the required var, return error, message
1552 BEGIN
1553 l_tmp_val := g_glb_var_tab(a_bind_rec.src_var);
1554 EXCEPTION
1555 WHEN NO_DATA_FOUND THEN
1556 FND_MESSAGE.SET_NAME('CLN','M4U_XFWK_INV_PARAM');
1557 FND_MESSAGE.SET_TOKEN('PARAM','Global variable');
1558 FND_MESSAGE.SET_TOKEN('VALUE',a_bind_rec.src_var);
1559 x_ret_msg := FND_MESSAGE.GET;
1560 x_ret_sts := g_err_code;
1564 RAISE FND_API.G_EXC_ERROR;
1561 IF g_log_lvl <= 1 THEN
1562 cln_debug_pub.add('Fetch global variable error',1);
1563 END IF;
1565 END;
1566
1567 ELSIF a_bind_rec.typ = m4u_xml_extn_utils.c_maptyp_view THEN
1568 -- make call to lookup view value
1569 -- pass mapping info to m4u_xml_extn_utils.lookup_view_value
1570 -- if error, bailout
1571 l_tmp_val := m4u_xml_extn_utils.lookup_view_value
1572 (a_bind_rec.src_view,a_bind_rec.src_col,
1573 a_bind_rec.src_lvl_id,x_ret_sts, x_ret_msg);
1574
1575 IF x_ret_sts <> g_success_code THEN
1576 IF g_log_lvl <= 6 THEN
1577 cln_debug_pub.add('lookup_view_value returns error',6);
1578 END IF;
1579 RAISE FND_API.G_EXC_ERROR;
1580 END IF;
1581
1582 ELSE
1583 -- Bad data, only mapping types supported are
1584 -- view and variables
1585 -- Constants can be directly used in sequels
1586 IF g_log_lvl <= 6 THEN
1587 cln_debug_pub.add('Mapping type not-found',6);
1588 END IF;
1589 FND_MESSAGE.SET_NAME('CLN','M4U_XFWK_INV_PARAM');
1590 FND_MESSAGE.SET_TOKEN('PARAM','Bind-variable mapping type');
1591 FND_MESSAGE.SET_TOKEN('VALUE',a_bind_rec.typ);
1592 x_ret_msg := FND_MESSAGE.GET;
1593 x_ret_sts := g_err_code;
1594 RAISE FND_API.G_EXC_ERROR;
1595
1596 END IF;
1597
1598 x_ret_sts := g_success_code;
1599 x_ret_msg := NULL;
1600 -- exit success
1601 IF g_log_lvl <= 2 THEN
1602 cln_debug_pub.add('Exiting m4u_xml_extn_utils.get_bind_value - Normal',2);
1603 cln_debug_pub.add('ret_val - ' || substr(l_tmp_val,1,255), 2);
1604 END IF;
1605
1606 RETURN l_tmp_val;
1607 EXCEPTION
1608 -- return error information present in x_ret_msg
1609 WHEN FND_API.G_EXC_ERROR THEN
1610 IF g_log_lvl <= 6 THEN
1611 cln_debug_pub.add('Exiting m4u_xml_extn_utils.get_bind_value - Error',6);
1612 cln_debug_pub.add('x_ret_msg - ' || x_ret_msg,6);
1613 END IF;
1614 x_ret_sts := g_err_code;
1615 l_tmp_val := null;
1616 RETURN l_tmp_val;
1617 -- unknow error has occured, created unknow message and return
1618 WHEN OTHERS THEN
1619 m4u_xml_extn_utils.handle_exception(SQLCODE,SQLERRM,l_progress,
1620 'm4u_xml_extn_utils.get_bind_value',x_ret_sts,x_ret_msg);
1621 l_tmp_val := null;
1622 RETURN l_tmp_val;
1623 END get_bind_value;
1624
1625 -- fetch view data
1626 -- this function fetches view data corresponding to a view_rec
1627 -- and stores into in the lvl_rec
1628 -- lvl_rec.vals contains data, indexed by (view.col)(rownum)
1629 PROCEDURE fetch_data(a_view_rec IN OUT NOCOPY view_rec_typ,
1630 a_lvl_rec IN OUT NOCOPY lvl_rec_typ,
1631 x_ret_sts OUT NOCOPY VARCHAR2,
1632 x_ret_msg OUT NOCOPY VARCHAR2)
1633 IS
1634 l_cursor NUMBER;
1635 l_bind_val VARCHAR2(4000);
1636 l_idx NUMBER;
1637 l_bind_rec bind_rec_typ;
1638 l_key VARCHAR2(100);
1639 l_discard NUMBER;
1640 l_tmp_var VARCHAR2(4000);
1641 l_rowcount NUMBER;
1642 l_vals g_vals_typ;
1643 l_progress VARCHAR2(4000);
1644 BEGIN
1645 l_progress := 'Fetch rows for view - ' || a_view_rec.view_nam;
1646 IF g_log_lvl <= 2 THEN
1647 cln_debug_pub.add('Entering m4u_xml_extn_utils.fetch_data',2);
1648 cln_debug_pub.add('a_view_rec.view_nam - ' || a_view_rec.view_nam,2);
1649 END IF;
1650
1651 IF g_log_lvl <= 1 THEN
1652 cln_debug_pub.add('a_view_rec.exec_sql - ' || a_view_rec.exec_sql,1);
1653 END IF;
1654
1655 -- create dbms_sql cursor
1656 l_cursor := dbms_sql.open_cursor;
1657 dbms_sql.parse(l_cursor,a_view_rec.exec_sql,dbms_sql.NATIVE);
1658
1659 IF g_log_lvl <= 1 THEN
1660 cln_debug_pub.add('a_view_rec.bind_count ' || a_view_rec.bind_count,1);
1661 END IF;
1662
1663 l_idx := 1;
1664 -- loop through list of bind variables for each view
1665 WHILE l_idx <= a_view_rec.bind_count LOOP
1666 -- for a bind record
1667 l_bind_rec := a_view_rec.bind_tab(l_idx);
1668
1669 IF g_log_lvl <= 1 THEN
1670 cln_debug_pub.add('l_bind_rec.nam - ' || l_bind_rec.nam,1);
1671 END IF;
1672 -- fetch the bind value
1676 cln_debug_pub.add('get_bind_value x_ret_sts - ' || x_ret_sts,1);
1673 l_bind_val := get_bind_value(l_bind_rec,x_ret_sts,x_ret_msg);
1674
1675 IF g_log_lvl <= 1 THEN
1677 END IF;
1678 IF x_ret_sts <> g_success_code THEN
1679 RAISE FND_API.G_EXC_ERROR;
1680 END IF;
1681 -- bind it to the sql
1682 dbms_sql.bind_variable(l_cursor,l_bind_rec.nam,l_bind_val);
1683
1684 l_idx := l_idx + 1;
1685 END LOOP;
1686
1687 IF g_log_lvl <= 1 THEN
1688 cln_debug_pub.add('a_view_rec.col_count - ' || a_view_rec.col_count,1);
1689 END IF;
1690
1691
1692 l_idx := 1;
1693 l_vals.delete;
1694 WHILE l_idx <= a_view_rec.col_count LOOP
1695
1696 -- key for data-lookup = view.column
1697 l_key := a_view_rec.view_nam || '.'|| a_view_rec.col_tab(l_idx);
1698
1699 IF g_log_lvl <= 1 THEN
1700 cln_debug_pub.add('define array l_key - ' || l_key,1);
1701 END IF;
1702 -- initialize storage for each view.colunm
1703 a_lvl_rec.vals(l_key) := l_vals;
1704
1705 -- define column in cursor
1706 dbms_sql.define_column(l_cursor,l_idx,l_tmp_var,4000);
1707 l_idx := l_idx + 1;
1708 END LOOP;
1709
1710 IF g_log_lvl <= 1 THEN
1711 cln_debug_pub.add('dbms_sql.define_column: done ',1);
1712 END IF;
1713
1714 l_discard := dbms_sql.execute(l_cursor);
1715
1716 IF g_log_lvl <= 1 THEN
1717 cln_debug_pub.add('Executed cursor',1);
1718 END IF;
1719
1720 l_rowcount := 0;
1721 LOOP
1722 l_discard := dbms_sql.fetch_rows(l_cursor);
1723
1724 IF g_log_lvl <= 1 THEN
1725 cln_debug_pub.add('l_discard - ' || l_discard,1);
1726 cln_debug_pub.add('l_rowcount - ' || l_rowcount,1);
1727 END IF;
1728
1729 IF l_discard = 0 THEN
1730 EXIT;
1731 END IF;
1732 l_rowcount := l_rowcount + 1;
1733 l_idx := 1;
1734 WHILE l_idx <= a_view_rec.col_count LOOP
1735
1736 IF g_log_lvl <= 1 THEN
1737 cln_debug_pub.add('l_idx - ' || l_idx,1);
1738 END IF;
1739
1740 l_key := a_view_rec.view_nam || '.'|| a_view_rec.col_tab(l_idx);
1741
1742 IF g_log_lvl <= 1 THEN
1743 cln_debug_pub.add('l_key - ' || l_key,1);
1744 END IF;
1745
1746 dbms_sql.column_value(l_cursor,l_idx,l_tmp_var);
1747
1748 IF g_log_lvl <= 1 THEN
1749 cln_debug_pub.add('column_value - ' || substr(l_tmp_var,1,255),1);
1750 END IF;
1751
1752 -- store data into the level record
1753 -- a_lvl_rec.vals(l_key) has been initialized in prev loop
1754 a_lvl_rec.vals(l_key)(l_rowcount) := l_tmp_var;
1755
1756 l_idx := l_idx + 1;
1757 END LOOP;
1758
1759 IF g_log_lvl <= 1 THEN
1760 cln_debug_pub.add('Rows fectched - ' || l_rowcount,1);
1761 END IF;
1762 END LOOP;
1763
1764 a_view_rec.rowcount := l_rowcount;
1765
1766
1767 IF g_log_lvl <= 1 THEN
1768 cln_debug_pub.add('Fetching view data complete ',1);
1769 cln_debug_pub.add('a_lvl_rec.rpt_count - ' || a_lvl_rec.rpt_count);
1770 END IF;
1771
1772 -- 5299569
1773 dbms_sql.close_cursor(l_cursor);
1774 -- this makes sense when there are mutliple views for a level
1775 -- level to repeat itself based on view with max records
1776 IF a_lvl_rec.rpt_count < a_view_rec.rowcount THEN
1777 a_lvl_rec.rpt_count := a_view_rec.rowcount;
1778 END IF;
1779
1780 IF g_log_lvl <= 1 THEN
1781 cln_debug_pub.add('a_lvl_rec.rpt_count - ' || a_lvl_rec.rpt_count);
1782 END IF;
1783
1784 -- no execptions, succcess!
1785 x_ret_sts := g_success_code;
1786 x_ret_msg := NULL;
1787 IF g_log_lvl <= 2 THEN
1788 cln_debug_pub.add('Exiting m4u_xml_extn_utils.fetch_data - Normal',2);
1789 cln_debug_pub.add('x_ret_msg - ' || x_ret_msg,2);
1790 cln_debug_pub.add('x_ret_sts - ' || x_ret_sts,2);
1791 cln_debug_pub.add('a_view_rec.rowcount - ' || a_view_rec.rowcount,2);
1792 END IF;
1793 RETURN;
1794 EXCEPTION
1795 WHEN OTHERS THEN
1796 handle_exception(SQLCODE,SQLERRM,l_progress,'m4u_xml_extn_utils.fetch_data',
1797 x_ret_sts,x_ret_msg);
1798 END fetch_data;
1802 -- Initializes level before placing on run-time stack
1799
1800
1801
1803 -- execute view queried and load data into vals
1804 -- level level cnt to zero and rpt_count to max(view count)
1805 -- initialize level end-tag-stack
1806 -- level has no associated views, then set rpt_count = 1 and view_count=0
1807 -- (redundant since we already do that while loading level)
1808 PROCEDURE init_level(a_lvl_id IN VARCHAR2,
1809 x_lvl_rec OUT NOCOPY lvl_rec_typ,
1810 x_ret_sts OUT NOCOPY VARCHAR2,
1811 x_ret_msg OUT NOCOPY VARCHAR2)
1812 IS
1813 l_lvl_rec lvl_rec_typ;
1814 l_cntr NUMBER;
1815 l_view_rec view_rec_typ;
1816 l_progress VARCHAR2(4000);
1817 BEGIN
1818 l_progress := 'Initialize level - ' || a_lvl_id;
1819 IF g_log_lvl <= 2 THEN
1820 cln_debug_pub.add('Entering m4u_xml_extn_utils.init_level',2);
1821 cln_debug_pub.add('a_lvl_id - ' || a_lvl_id ,2);
1822 END IF;
1823
1824 x_ret_sts := g_success_code;
1825
1826 --code to initialize level-record
1827 l_lvl_rec := g_lvl_rec_tab(a_lvl_id);
1828 l_lvl_rec.cntr := 0;
1829 l_lvl_rec.end_tag_stk_ptr := 0;
1830 l_lvl_rec.end_tag_stk.delete;
1831
1832
1833 IF g_log_lvl <= 1 THEN
1834 cln_debug_pub.add('Cleaned l_lvl_rec.id - ' || l_lvl_rec.id,1);
1835 END IF;
1836
1837
1838 IF g_log_lvl <= 1 THEN
1839 cln_debug_pub.add('l_lvl_rec.view_count - ' || l_lvl_rec.view_count,1);
1840 END IF;
1841
1842 IF l_lvl_rec.view_count = 0 THEN
1843 l_lvl_rec.is_mapped := false;
1844 l_lvl_rec.rpt_count := 1;
1845 ELSE
1846 l_cntr := 1;
1847 l_lvl_rec.rpt_count := 0;
1848 l_lvl_rec.is_mapped := true;
1849 l_lvl_rec.vals.delete;
1850
1851 -- loop through each view for the level
1852 WHILE l_cntr <= l_lvl_rec.view_count LOOP
1853 -- obtain view rec
1854 l_view_rec := l_lvl_rec.view_tab(l_cntr);
1855
1856 IF g_log_lvl <=1 THEN
1857 cln_debug_pub.add('l_view_rec.view_nam - ' || l_view_rec.view_nam,1);
1858 END IF;
1859
1860 -- fetch data for view
1861 -- l_view_rec contains rowcount for view
1862 -- l_level_rec.vals contain data indexed by (view.colum)(rownum)
1863 -- rpt count is set to number of times the level repeats
1864 fetch_data(l_view_rec,l_lvl_rec,x_ret_sts,x_ret_msg);
1865 -- store data back into level record
1866 l_lvl_rec.view_tab(l_cntr) := l_view_rec;
1867
1868 IF g_log_lvl <= 1 THEN
1869 cln_debug_pub.add('fetch_data x_ret_sts - ' || x_ret_sts,1);
1870 cln_debug_pub.add('fetch_data x_ret_msg - ' || x_ret_msg,1);
1871 END IF;
1872 -- failure, no point procesing further
1873 IF x_ret_sts <> g_success_code THEN
1874 EXIT;
1875 END IF;
1876 l_cntr := l_cntr + 1;
1877
1878 END LOOP;
1879
1880 IF g_log_lvl <= 1 THEN
1881 cln_debug_pub.add('Exited loop',1);
1882 END IF;
1883 END IF;
1884
1885 -- store level in table and return it
1886 g_lvl_rec_tab(a_lvl_id) := l_lvl_rec;
1887 x_lvl_rec := l_lvl_rec;
1888
1889 IF x_ret_sts = g_success_code THEN
1890 x_ret_msg := NULL;
1891 END IF;
1892 IF g_log_lvl <= 2 THEN
1893 cln_debug_pub.add('Exiting m4u_xml_extn_utils.init_level - Normal',2);
1894 cln_debug_pub.add('x_ret_msg - ' || x_ret_msg,2);
1895 cln_debug_pub.add('x_ret_sts - ' || x_ret_sts,2);
1896 END IF;
1897 RETURN;
1898 EXCEPTION
1899 WHEN OTHERS THEN
1900 handle_exception(SQLCODE,SQLERRM,l_progress,'m4u_xml_extn_utils.init_level',
1901 x_ret_sts,x_ret_msg);
1902 END init_level;
1903
1904 -- cleans up run-time info of level
1905 -- this is important
1906 -- since same level can be brought back onto stack multiple times
1907 -- so make sure data from previous run of level is not carried
1908 PROCEDURE un_init_level(a_lvl_id IN NUMBER,
1909 x_ret_sts OUT NOCOPY VARCHAR2,
1910 x_ret_msg OUT NOCOPY VARCHAR2)
1911 IS
1912 l_lvl_rec lvl_rec_typ;
1913 l_progress VARCHAR2(4000);
1914 BEGIN
1915 l_progress := 'Free level - ' || a_lvl_id;
1916 IF g_log_lvl <= 2 THEN
1917 cln_debug_pub.add('Entering m4u_xml_extn_utils.un_init_level',2);
1918 cln_debug_pub.add('a_lvl_id - ' || a_lvl_id,2);
1919 END IF;
1920
1921 l_lvl_rec := g_lvl_rec_tab(a_lvl_id);
1922 IF g_log_lvl <= 1 THEN
1923 cln_debug_pub.add('Obtained level record',1);
1924 END IF;
1925
1926 l_lvl_rec.cntr := 0;
1927 l_lvl_rec.end_tag_stk_ptr := 0;
1928 l_lvl_rec.end_tag_stk.delete;
1929
1930 IF g_log_lvl <= 1 THEN
1931 cln_debug_pub.add('deleted l_lvl_rec.end_tag_stk',1);
1932 END IF;
1933
1934
1935 IF l_lvl_rec.is_mapped THEN
1936 l_lvl_rec.rpt_count := 0;
1937 l_lvl_rec.vals.delete;
1938 IF g_log_lvl <= 1 THEN
1939 cln_debug_pub.add('deleted l_lvl_rec.vals',1);
1940 END IF;
1941 END IF;
1942
1943 -- store changes back into level table
1944 g_lvl_rec_tab(a_lvl_id) := l_lvl_rec;
1945 x_ret_sts := g_success_code;
1946 x_ret_msg := null;
1947 IF g_log_lvl <= 2 THEN
1948 cln_debug_pub.add('Exiting m4u_xml_extn_utils.un_init_level - Normal',2);
1949 cln_debug_pub.add('x_ret_msg - ' || x_ret_msg,2);
1950 cln_debug_pub.add('x_ret_sts - ' || x_ret_sts,2);
1951 END IF;
1952 RETURN;
1953 EXCEPTION
1954 WHEN OTHERS THEN
1955 handle_exception(SQLCODE,SQLERRM,l_progress,'m4u_xml_extn_utils.un_init_level',
1956 x_ret_sts,x_ret_msg);
1957 END un_init_level;
1958
1959 -- cleans up any memory held by map
1960 PROCEDURE un_init_map(x_ret_sts OUT NOCOPY VARCHAR2,
1961 x_ret_msg OUT NOCOPY VARCHAR2)
1962 IS
1963 l_progress VARCHAR2(4000);
1964 BEGIN
1965 IF g_log_lvl <= 2 THEN
1966 cln_debug_pub.add('Entering m4u_xml_extn_utils.un_init_map',2);
1967 END IF;
1968
1969 l_progress := 'Un-load mapping information';
1970 -- dont leave stray variables in session
1971 -- they may affect the next run
1972 g_hdr_rec.extn_name := NULL;
1973 g_hdr_rec.tp_id := NULL;
1974 g_hdr_rec.valdtn_typ := NULL;
1975 g_hdr_rec.dtd_schma_loc := NULL;
1976 g_hdr_rec.dtd_base_dir := NULL;
1977 g_hdr_rec.xml_root_node := NULL;
1978 g_elmnt_count := 0;
1979 g_lvl_stk_ptr := 0;
1980
1981 -- important! delete all collections
1982 g_lvl_stk.delete;
1983 g_elmnt_map.delete;
1984 g_glb_var_tab.delete;
1985 g_lvl_rec_tab.delete;
1986
1987 x_ret_sts := g_success_code;
1988 x_ret_msg := null;
1989
1990 IF g_log_lvl <= 2 THEN
1991 cln_debug_pub.add('Exiting m4u_xml_extn_utils.un_init_map - Normal',2);
1992 cln_debug_pub.add('x_ret_msg - ' || x_ret_msg,2);
1993 cln_debug_pub.add('x_ret_sts - ' || x_ret_sts,2);
1994 END IF;
1995 RETURN;
1996 EXCEPTION
1997 WHEN OTHERS THEN
1998 handle_exception(SQLCODE,SQLERRM,l_progress,'m4u_xml_extn_utils.un_init_map',
1999 x_ret_sts,x_ret_msg);
2000 END un_init_map;
2001
2002
2003 BEGIN
2004 -- initialize constants, profile dependant values
2005 g_success_code := FND_API.G_RET_STS_SUCCESS;
2006 g_err_code := FND_API.G_RET_STS_ERROR;
2007 g_unexp_err_code := FND_API.G_RET_STS_UNEXP_ERROR;
2008 g_log_lvl := NVL(FND_PROFILE.VALUE('CLN_DEBUG_LEVEL'),5);
2009 g_log_dir := NVL(FND_PROFILE.VALUE('CLN_DEBUG_LOG_DIRECTORY'),'/tmp');
2010 END m4u_xml_extn_utils;