[Home] [Help]
PACKAGE BODY: APPS.ICX_CAT_R12_DATA_EXCEP_RPT_PVT
Source
1 PACKAGE BODY ICX_CAT_R12_DATA_EXCEP_RPT_PVT AS
2 /* $Header: ICXVDERB.pls 120.17 2006/07/21 10:04:28 mkohale noship $*/
3
4 --Constant for R12 used to
5 --populate Updated_by and Created_by Columns
6 UPGRADE_USER_ID NUMBER := -12;
7
8 -- Package name constant used in debug
9 g_pkg_name VARCHAR2(40) := 'ICX_CAT_R12_DATA_EXCEP_RPT_PVT';
10
11 /**
12 ** Private functions used in Populating the XML Data
13 **
14 **/
15 -- Private Functions declaration Start
16 PROCEDURE generate_header_section(p_interface_header_id IN number, x_header_XML IN OUT NOCOPY CLOB) ;
17 PROCEDURE generate_lines_section(p_interface_header_id IN NUMBER,
18 p_category_id IN NUMBER,
19 p_language IN VARCHAR2,
20 x_lines_XML IN OUT NOCOPY CLOB);
21 PROCEDURE populate_namevalue_xmltag(p_category_id IN NUMBER);
22 PROCEDURE populate_language_map;
23 PROCEDURE populate_upg_error_msgs(p_interface_header_id_tbl IN DBMS_SQL.NUMBER_TABLE);
24 -- Private Functions declaration End
25
26 /**
27 ** Procedure : cleanup_tables
28 ** Synopsis : To delete the existing data from
29 ** upgraded tables
30 **/
31 PROCEDURE cleanup_tables
32 IS
33 l_log_string varchar2(400);
34 l_module_name varchar2(200);
35 l_proc_name varchar2(100) := 'cleanup_tables';
36 l_icx_schema_name VARCHAR2(30) ;
37 l_start_date DATE;
38 l_end_date DATE;
39
40 BEGIN
41
42 l_start_date := sysdate;
43
44 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
45 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
46 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_proc_name),
47 'Started at:' || TO_CHAR(l_start_date, 'DD-MON-YYYY HH24:MI:SS'));
48 END IF;
49
50 l_icx_schema_name := ICX_CAT_UTIL_PVT.getIcxSchemaName;
51
52 --Data removed from data migration tables
53 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_icx_schema_name||'.ICX_CAT_R12_UPG_EXCEP_FILES';
54
55 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
56 l_log_string := 'ICX_CAT_R12_DATA_EXCEP_RPT_PVT.cleanup_tables deleting data
57 from icx_cat_r12_upg_error_msgs';
58 l_module_name := ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name,l_proc_name);
59 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module_name, l_log_string);
60 END IF;
61
62 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_icx_schema_name||'.ICX_CAT_R12_UPG_ERROR_MSGS';
63
64 l_end_date := sysdate;
65
66 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
67 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
68 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_proc_name),
69 ' done in:' || ICX_CAT_UTIL_PVT.getTimeStats(l_start_date, l_end_date));
70 END IF;
71 END;
72
73 /**
74 ** Procedure : populate_language_map
75 ** Synopsis : To populate the languages map
76 **/
77 PROCEDURE populate_language_map
78 IS
79 l_language_code_tbl DBMS_SQL.VARCHAR2_TABLE;
80 l_iso_territory_tbl DBMS_SQL.VARCHAR2_TABLE;
81 l_iso_language_tbl DBMS_SQL.VARCHAR2_TABLE;
82 l_nls_territory_tbl DBMS_SQL.VARCHAR2_TABLE;
83 l_nls_language_tbl DBMS_SQL.VARCHAR2_TABLE;
84 l_progress pls_integer;
85 l_log_string varchar2(400);
86 l_module_name varchar2(200);
87 l_proc_name varchar2(100) := 'populate_language_map';
88
89 BEGIN
90 l_progress := 100;
91
92 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
93 l_log_string := 'ICX_CAT_R12_DATA_EXCEP_RPT_PVT.populate_language_map started :--> '||
94 'l_progress:' ||l_progress;
95 l_module_name := ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name,l_proc_name);
96 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module_name, l_log_string);
97 END IF;
98
99 -- Populating the territories list for each language
100 -- This list is used while populating the Catalog XML File
101 SELECT iso_territory, language_code, iso_language,nls_territory,nls_language BULK COLLECT INTO
102 l_iso_territory_tbl,
103 l_language_code_tbl,
104 l_iso_language_tbl,
105 l_nls_territory_tbl,
106 l_nls_language_tbl
107 FROM fnd_languages
108 WHERE installed_flag in ('B','I');
109
110 l_progress := 110;
111
112 FOR i in 1..l_language_code_tbl.COUNT
113 LOOP
114
115 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
116 l_log_string := 'populate_language_map: language code'||l_language_code_tbl(i)||
117 'iso territory' || l_iso_territory_tbl(i)||
118 'iso language'|| l_iso_language_tbl(i)||
119 'nls territory' || l_nls_territory_tbl(i)||
120 'nls language' || l_nls_language_tbl(i)||
121 'l_progress:' ||l_progress;
122 l_module_name := ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name,l_proc_name);
123 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module_name, l_log_string);
124 END IF;
125
126 g_territories(l_language_code_tbl(i)).iso_territory := l_iso_territory_tbl(i);
127 g_territories(l_language_code_tbl(i)).iso_language := l_iso_language_tbl(i);
128 g_territories(l_language_code_tbl(i)).nls_territory := l_nls_territory_tbl(i);
129 g_territories(l_language_code_tbl(i)).nls_language := l_nls_language_tbl(i);
130
131 END LOOP;
132
133 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
134 l_log_string := 'ICX_CAT_R12_DATA_EXCEP_RPT_PVT.populate_language_map completed :--> '||
135 'l_progress:' ||l_progress;
136 l_module_name := ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name,l_proc_name);
137 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module_name, l_log_string);
138 END IF;
139
140 END;
141
142 /**
143 ** Procedure : process_data_exceptions_report
144 ** Synopsis : Populates the exceptions file table
145 **
146 ** Parameter: p_batch_id--Batch_id
147 **/
148 PROCEDURE process_data_exceptions_report(p_batch_id IN po_headers_interface.batch_id%TYPE)
149 IS
150
151 --Cursor fetching data from po_headers_interface
152 CURSOR interface_headers_cursor(p_batch_id NUMBER) IS
153 SELECT distinct pohi.interface_header_id,
154 pohi.vendor_id,
155 pohi.vendor_site_id,
156 pohi.org_id,
157 pohi.currency_code,
158 pohi.cpa_reference,
159 poai.language
160 FROM po_headers_interface pohi,
161 po_attr_values_tlp_interface poai
162 WHERE pohi.batch_id = p_batch_id
163 AND poai.interface_header_id = pohi.interface_header_id
164 AND EXISTS (SELECT 1
165 FROM po_interface_errors poie
166 WHERE poie.interface_header_id = pohi.interface_header_id
167 -- to retireve only those languages for which
168 -- corresponding lines has errors
169 AND (poie.interface_line_id IS NULL OR
170 poie.interface_line_id = poai.interface_line_id))
171 AND NOT EXISTS (SELECT 1
172 FROM icx_cat_r12_upg_excep_files
173 WHERE interface_header_id = pohi.interface_header_id
174 AND language = poai.language)
175 ORDER BY pohi.interface_header_id;
176
177 --Tables for fetching Individual values from cursor
178 l_interface_header_id_tbl DBMS_SQL.NUMBER_TABLE;
179 l_vendor_id_tbl DBMS_SQL.NUMBER_TABLE;
180 l_vendor_site_id_tbl DBMS_SQL.NUMBER_TABLE;
181 l_org_id_tbl DBMS_SQL.NUMBER_TABLE;
182 l_currency_code_tbl DBMS_SQL.VARCHAR2_TABLE;
183 l_contract_num_tbl DBMS_SQL.NUMBER_TABLE;
184 l_language_tbl DBMS_SQL.VARCHAR2_TABLE;
185 l_progress PLS_INTEGER;
186 l_log_string varchar2(400);
187 l_module_name varchar2(200);
188 l_proc_name varchar2(100) := 'process_data_exceptions_report';
189 l_start_date DATE;
190 l_end_date DATE;
191
192 BEGIN
193 l_progress := 100;
194
195 l_start_date := sysdate;
196
197 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
198 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
199 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_proc_name),
200 'Started at:' || TO_CHAR(l_start_date, 'DD-MON-YYYY HH24:MI:SS') ||
201 'Parameters: p_batch_id:' || p_batch_id);
202 END IF;
203
204 -- Set Batch Size if null
205 IF (ICX_CAT_UTIL_PVT.g_batch_size IS NULL) THEN
206 ICX_CAT_UTIL_PVT.setBatchSize();
207 END IF;
208
209 -- clear the data from the upgrade tables
210 cleanup_tables;
211
212 l_progress := 102;
213 -- populate the maps used in generating the information
214 populate_language_map;
215
216 l_progress := 104;
217 -- Prepare the attributes list for 0 category id
218 populate_namevalue_xmltag(0);
219
220 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
221 l_log_string := 'process_data_exceptions_report :'||'Processing exceptions report for Batch Id : '
222 ||p_batch_id||'l_progress:' ||l_progress;
223 l_module_name := ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name,l_proc_name);
224 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module_name, l_log_string);
225 END IF;
226
227 l_progress := 110;
228 OPEN interface_headers_cursor(p_batch_id);
229 LOOP
230 l_interface_header_id_tbl.DELETE;
231 l_vendor_id_tbl.DELETE;
232 l_vendor_site_id_tbl.DELETE;
233 l_org_id_tbl.DELETE;
234 l_currency_code_tbl.DELETE;
235 l_contract_num_tbl.DELETE;
236 l_language_tbl.DELETE;
237
238 FETCH interface_headers_cursor BULK COLLECT INTO
239 l_interface_header_id_tbl,
240 l_vendor_id_tbl,
241 l_vendor_site_id_tbl,
242 l_org_id_tbl,
243 l_currency_code_tbl,
244 l_contract_num_tbl,
245 l_language_tbl
246 LIMIT ICX_CAT_UTIL_PVT.g_batch_size;
247 EXIT WHEN l_interface_header_id_tbl.COUNT = 0;
248
249 BEGIN
250 -- Inserting the data into Upgrade Exceptions File Table
251 FORALL headers_index IN 1..l_interface_header_id_tbl.COUNT
252 INSERT INTO icx_cat_r12_upg_excep_files (interface_header_id,
253 vendor_id,
254 vendor_site_id,
255 org_id,
256 currency_code,
257 contract_num,
258 language,
259 data_file,
260 creation_date,
261 created_by,
262 last_update_date,
263 last_updated_by)
264 VALUES (l_interface_header_id_tbl(headers_index),
265 l_vendor_id_tbl(headers_index),
266 l_vendor_site_id_tbl(headers_index),
267 l_org_id_tbl(headers_index),
268 l_currency_code_tbl(headers_index),
269 l_contract_num_tbl(headers_index),
270 l_language_tbl(headers_index),
271 EMPTY_CLOB(),
272 SYSDATE,
273 UPGRADE_USER_ID,
274 SYSDATE,
275 UPGRADE_USER_ID);
276
277 l_progress := 120;
278 -- Populate the error messages table
279 populate_upg_error_msgs(l_interface_header_id_tbl);
280
281 -- invoke procedure to update the record with the XML file.
282 populate_catalog_files(l_interface_header_id_tbl,
283 l_vendor_id_tbl,
284 l_vendor_site_id_tbl,
285 l_org_id_tbl,
286 l_currency_code_tbl,
287 l_contract_num_tbl,
288 l_language_tbl);
289
290 COMMIT;
291 EXCEPTION
292 WHEN ICX_CAT_UTIL_PVT.g_snap_shot_too_old THEN
293 -- delete the data for which the xml file is not
294 -- yet generated. So that these lines can be
295 -- retrieved again
296 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
297 l_log_string := 'process_data_exceptions_report : Snapshot too old encountered'||
298 'Deleting data from ICX_CAT_R12_UPG_EXCEP_FILES table'||
299 'l_progress:' ||l_progress ||' '||SQLERRM;
300 l_module_name := ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name,l_proc_name);
301 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION, l_module_name, l_log_string);
302 END IF;
303 DELETE FROM icx_cat_r12_upg_excep_files
304 WHERE file_name is null;
305 COMMIT;
306
307 IF (interface_headers_cursor%ISOPEN) THEN
308 CLOSE interface_headers_cursor;
309 OPEN interface_headers_cursor(p_batch_id);
310 END IF;
311 END;
312 END LOOP;
313
314 IF (interface_headers_cursor%ISOPEN) THEN
315 CLOSE interface_headers_cursor;
316 END IF;
317
318 l_end_date := sysdate;
319
320 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
321 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
322 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_proc_name),
323 ' done in:' || ICX_CAT_UTIL_PVT.getTimeStats(l_start_date, l_end_date));
324 END IF;
325
326 EXCEPTION
327 WHEN OTHERS THEN
328 ICX_CAT_UTIL_PVT.logUnexpectedException(
329 g_pkg_name, l_proc_name,' --> l_progress:' ||l_progress||' '|| SQLERRM);
330 RAISE;
331 END process_data_exceptions_report;
332
333 /**
334 ** Procedure : populate_catalog_files
335 ** Synopsis : Populates the exceptions file in XML Format
336 **
337 ** Parameter:
338 ** IN p_interface_header_id_tbl
339 ** p_vendor_id_tbl
340 ** p_vendor_site_id_tbl
341 ** p_org_id_tbl
342 ** p_currency_code_tbl
343 ** p_contract_num_tbl
344 ** p_language_tbl
345 **/
346 PROCEDURE populate_catalog_files(p_interface_header_id_tbl IN DBMS_SQL.NUMBER_TABLE,
347 p_vendor_id_tbl IN DBMS_SQL.NUMBER_TABLE,
348 p_vendor_site_id_tbl IN DBMS_SQL.NUMBER_TABLE,
349 p_org_id_tbl IN DBMS_SQL.NUMBER_TABLE,
350 p_currency_code_tbl IN DBMS_SQL.VARCHAR2_TABLE,
351 p_contract_num_tbl IN DBMS_SQL.NUMBER_TABLE,
352 p_language_tbl IN DBMS_SQL.VARCHAR2_TABLE)
353 IS
354 -- List of category ids for which the line exists in
355 -- interface errors and attr values tlp tables.
356 Cursor category_ids_cursor(p_interface_header_id number,
357 p_language varchar2) is
358 SELECT distinct poli.ip_category_id
359 FROM po_lines_interface poli
360 WHERE poli.interface_header_id = p_interface_header_id
361 AND EXISTS (SELECT 1
362 FROM po_interface_errors poie
363 WHERE poie.interface_header_id = poli.interface_header_id
364 AND (poie.interface_line_id IS NULL OR
365 poie.interface_line_id = poli.interface_line_id))
366 AND EXISTS (SELECT 1
367 FROM po_attr_values_tlp_interface poai
368 WHERE poai.interface_header_id = poli.interface_header_id
369 AND poai.interface_line_id = poli.interface_line_id
370 AND poai.language = p_language);
371
372 l_ip_category_id_tbl DBMS_SQL.NUMBER_TABLE;
373 l_mainXML CLOB;
374 l_headerXML CLOB;
375 l_linesXML CLOB;
376
377 l_progress PLS_INTEGER;
378
379 l_interface_header_id NUMBER;
380 l_ip_category_id NUMBER;
381 l_language icx_cat_r12_upg_excep_files.language%TYPE;
382
383 langCount number := 1;
384 l_file_name icx_cat_r12_upg_excep_files.file_name%TYPE;
385 l_table_count NUMBER;
386 l_prev_interface_header_id NUMBER := 0;
387 l_log_string varchar2(400);
388 l_module_name varchar2(200);
389 l_proc_name varchar2(100) := 'populate_catalog_files';
390 l_start_date DATE;
391 l_end_date DATE;
392 headers_index NUMBER := 1;
393 BEGIN
394 l_progress := 100;
395
396 l_start_date := sysdate;
397
398 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
399 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
400 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_proc_name),
401 'Started at:' || TO_CHAR(l_start_date, 'DD-MON-YYYY HH24:MI:SS'));
402 END IF;
403 l_table_count := p_interface_header_id_tbl.COUNT;
404 -- iterate through the interface headers in the PL/SQL table
405 WHILE headers_index <= l_table_count LOOP
406 l_interface_header_id := p_interface_header_id_tbl(headers_index);
407 l_language := p_language_tbl(headers_index);
408
409 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
410 l_log_string := 'populate_catalog_files; '||' interface header id '|| l_interface_header_id
411 ||'Session Alter to nls language'||g_territories(l_language).nls_language
412 ||'and nls territory'|| g_territories(l_language).nls_territory;
413 l_module_name := ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name,l_proc_name);
414 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module_name, l_log_string);
415 END IF;
416
417 EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_LANGUAGE = '''|| g_territories(l_language).nls_language ||'''';
418 EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_TERRITORY ='''|| g_territories(l_language).nls_territory||'''';
419
420
421 l_progress := 120;
422 -- Prepare the XML containing header information
423 IF ( l_interface_header_id <> l_prev_interface_header_id) THEN
424 generate_header_section(l_interface_header_id, l_mainXML);
425 l_prev_interface_header_id := l_interface_header_id;
426 END IF;
427
428 l_progress := 130;
429 l_headerXML := l_mainXML;
430 l_linesXML := EMPTY_CLOB();
431
432 l_progress := 140;
433 -- Prepare Line Level XML loop through the categories
434 OPEN category_ids_cursor(l_interface_header_id, l_language);
435 LOOP
436 l_ip_category_id_tbl.DELETE;
437 FETCH category_ids_cursor BULK COLLECT INTO
438 l_ip_category_id_tbl
439 LIMIT ICX_CAT_UTIL_PVT.g_batch_size;
440
441 l_progress := 150;
442 EXIT when l_ip_category_id_tbl.COUNT=0;
443
444 l_progress := 160;
445 -- Loop through each category
446 FOR category_index in 1..l_ip_category_id_tbl.COUNT
447 LOOP
448 l_ip_category_id := l_ip_category_id_tbl(category_index);
449
450 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
451 l_log_string := 'populate_catalog_files; '||' category id '|| l_ip_category_id;
452 l_module_name := ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name,l_proc_name);
453 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module_name, l_log_string);
454 END IF;
455
456 l_progress := 170;
457 -- Generate Lines section for the lines that have the category id
458 -- as l_ip_category_id
459 generate_lines_section(l_interface_header_id, l_ip_category_id, l_language,
460 l_linesXML);
461 l_progress := 180;
462 END LOOP; -- end of categories FOR Loop
463 END LOOP; -- end of categories lines cursor
464
465 IF (category_ids_cursor%ISOPEN) THEN
466 CLOSE category_ids_cursor;
467 END IF;
468
469 l_progress := 190;
470 replace_clob('R12_ITEM_DET', l_linesXML, l_headerXML, false);
471 replace_clob('<CATALOG>',
472 '<CATALOG xml:lang="'||
473 g_territories(l_language).iso_language||'-'||
474 g_territories(l_language).iso_territory||'">',
475 l_headerXML, false);
476 l_file_name := 'ItemException_'||l_language||'.xml';
477 -- if current language is same as the language in the PL/SQL Table
478 -- then update the current row
479 l_progress := 200;
480
481 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
482 l_log_string := 'populate_catalog_files : updating existing rows';
483 l_module_name := ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name,l_proc_name);
484 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module_name, l_log_string);
485 END IF;
486 --update the table
487
488 UPDATE icx_cat_r12_upg_excep_files
489 SET file_name = l_file_name,
490 data_file = l_headerXML
491 WHERE interface_header_id = l_interface_header_id
492 AND language = l_language;
493
494 headers_index := headers_index + 1 ;
495 END LOOP; -- end of interface header ids for loop
496
497 l_end_date := sysdate;
498
499 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
500 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
501 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_proc_name),
502 ' done in:' || ICX_CAT_UTIL_PVT.getTimeStats(l_start_date, l_end_date));
503 END IF;
504
505 EXCEPTION
506 WHEN OTHERS THEN
507 ICX_CAT_UTIL_PVT.logUnexpectedException(
508 g_pkg_name, l_proc_name,' --> l_progress:' ||l_progress||' '|| SQLERRM);
509 RAISE;
510 END populate_catalog_files;
511
512 /**
513 ** Procedure : populate_upg_error_msgs
514 ** Synopsis : Procedure For Populating
515 ** ICX_CAT_R12_UPG_Error_Msgs Table.
516 ** Parameter:
517 ** IN p_interface_header_id_tbl --table of interface_header_ids
518 **/
519 PROCEDURE populate_upg_error_msgs(p_interface_header_id_tbl IN DBMS_SQL.NUMBER_TABLE)
520 IS
521 --cursor for fetching column value to be populated
522 -- as token value in the error messages table
523 CURSOR interface_errors_cursor(p_interface_header_id po_interface_errors.INTERFACE_HEADER_ID%TYPE)
524 IS
525 SELECT error_message_name,
526 LTRIM(replace(MAX(SYS_CONNECT_BY_PATH(column_value, '**R12MDIGREPL**'))
527 KEEP (DENSE_RANK LAST ORDER BY curr),'**R12MDIGREPL**', ','),',') AS token_value
528 FROM (SELECT error_message_name,
529 column_value,
530 ROW_NUMBER() OVER (PARTITION BY error_message_name ORDER BY column_value) AS curr,
531 ROW_NUMBER() OVER (PARTITION BY error_message_name ORDER BY column_value) -1 AS prev
532 FROM po_interface_errors
533 WHERE interface_header_id = p_interface_header_id
534 AND error_message_name <> 'ICX_CAT_UPG_ALL_LINES_FAILED'
535 GROUP BY error_message_name, column_value)
536 GROUP BY error_message_name
537 CONNECT BY prev = PRIOR curr AND error_message_name = PRIOR error_message_name
538 START WITH curr = 1;
539
540 l_po_msg_tbl DBMS_SQL.VARCHAR2_TABLE;
541 l_column_value_tbl DBMS_SQL.VARCHAR2_TABLE;
542 l_interface_header_id NUMBER;
543 l_prev_interface_header_id NUMBER := 0;
544 l_progress PLS_integer;
545 l_log_string varchar2(400);
546 l_module_name varchar2(200);
547 l_start_date DATE;
548 l_end_date DATE;
549 l_proc_name varchar2(100) := 'populate_upg_error_msgs';
550
551 BEGIN
552 l_progress :=100;
553
554 l_start_date := sysdate;
555
556 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
557 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
558 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_proc_name),
559 'Started at:' || TO_CHAR(l_start_date, 'DD-MON-YYYY HH24:MI:SS'));
560 END IF;
561
562 FOR header_id_index IN 1..p_interface_header_id_tbl.COUNT
563 LOOP
564 --Loop to through interface errors cursor
565 l_interface_header_id := p_interface_header_id_tbl(header_id_index);
566
567 IF (l_interface_header_id <> l_prev_interface_header_id) THEN
568
569 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
570 l_log_string := 'populate_upg_error_msgs:'||'Inserting messages for interface header id'
571 ||l_interface_header_id|| 'l_progress:' ||l_progress;
572 l_module_name := ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name,l_proc_name);
573 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module_name, l_log_string);
574 END IF;
575
576 OPEN interface_errors_cursor(p_interface_header_id_tbl(header_id_index));
577 LOOP
578 FETCH interface_errors_cursor BULK COLLECT INTO
579 l_po_msg_tbl,
580 l_column_value_tbl
581 LIMIT ICX_CAT_UTIL_PVT.g_batch_size;
582
583 l_progress := 110;
584 EXIT WHEN l_po_msg_tbl.COUNT = 0;
585
586 l_progress := 120;
587 FORALL i IN 1..l_po_msg_tbl.COUNT
588 INSERT INTO icx_cat_r12_upg_error_msgs(interface_header_id,
589 error_message_name,
590 token_value,
591 creation_date,
592 created_by,
593 last_update_date,
594 last_updated_by,
595 last_update_login)
596 VALUES (p_interface_header_id_tbl(header_id_index),
597 l_po_msg_tbl(i),
598 l_column_value_tbl(i),
599 SYSDATE,
600 UPGRADE_USER_ID,
601 SYSDATE,
602 UPGRADE_USER_ID,
603 NULL);
604 END LOOP;
605
606 IF (interface_errors_cursor%ISOPEN) THEN
607 CLOSE interface_errors_cursor;
608 END IF;
609 END IF;
610
611 l_prev_interface_header_id := l_interface_header_id;
612
613 END LOOP;
614
615 l_end_date := sysdate;
616
617 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
618 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
619 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_proc_name),
620 ' done in:' || ICX_CAT_UTIL_PVT.getTimeStats(l_start_date, l_end_date));
621 END IF;
622
623 EXCEPTION
624 WHEN OTHERS THEN
625 ICX_CAT_UTIL_PVT.logUnexpectedException(
626 g_pkg_name, l_proc_name,' --> l_progress:' ||l_progress||' '|| SQLERRM);
627 RAISE;
628 END populate_upg_error_msgs;
629
630 /**
631 ** Procedure : generate_header_section
632 ** Synopsis : Function that returns the XML containing
633 ** Header details
634 **
635 ** Parameter:
636 ** IN p_interface_header_id -- interface header id
637 ** IN OUT
638 ** x_header_XML -- Clob containing header XML
639 **/
640 PROCEDURE generate_header_section(p_interface_header_id IN number,
641 x_header_XML IN OUT NOCOPY CLOB)
642 IS
643 l_qryString VARCHAR2(4000);
644 l_params xml_bind_params;
645 l_progress PLS_INTEGER;
646 l_log_string varchar2(400);
647 l_module_name varchar2(200);
648 l_start_date DATE;
649 l_end_date DATE;
650 l_proc_name varchar2(100) := 'generate_header_section';
651
652 BEGIN
653 l_progress := 100;
654
655 l_start_date := sysdate;
656
657 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
658 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
659 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_proc_name),
660 'Started at:' || TO_CHAR(l_start_date, 'DD-MON-YYYY HH24:MI:SS') ||
661 'Parameters: p_interface_header_id:' || p_interface_header_id);
662 END IF;
663
664 l_qryString := 'SELECT XMLConcat(
665 XMLElement("ADMIN",
666 XMLElement("NAME", ''CATALOG EXT''),
667 XMLElement("INFORMATION",
668 XMLElement("SOURCE", ''RELEASE 12 UPGRADE''),
669 XMLElement("DATE", sysdate))),
670 XMLElement("DATA",
671 XMLElement("DOCUMENT",
672 XMLAttributes(''GBPA'' as "type"),
673 XMLElement("HEADER",
674 XMLElement("DOCUMENT_NUMBER", pohi.document_num),
675 XMLElement("OPERATING_UNIT",
676 XMLElement("OWNING_ORG", hro.name)),
677 XMLElement("SUPPLIER_NAME", pv.vendor_name),
678 XMLElement("SUPPLIER_SITE", pvs.vendor_site_Code),
679 XMLElement("CURRENCY", pohi.currency_code)),
680 XMLElement("LINES", ''R12_ITEM_DET'')))) AS CATALOG
681 FROM po_headers_interface pohi, HR_ALL_ORGANIZATION_UNITS hro,
682 po_vendors pv, po_vendor_sites_all pvs
683 WHERE pohi.interface_header_id = :INTERFACE_HEADER_ID
684 AND pohi.org_id = hro.organization_id (+)
685 AND pohi.vendor_id = pv.vendor_id (+)
686 AND pohi.vendor_site_id = pvs.vendor_site_id (+)';
687
688 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
689 l_log_string := 'generate_lines_section:'||'Binding parameters :interface header id'
690 ||p_interface_header_id;
691 l_module_name := ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name,l_proc_name);
692 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module_name, l_log_string);
693 END IF;
694
695 -- Set the bind parameter values
696 l_params := xml_bind_params(null);
697 l_params(1).name := 'INTERFACE_HEADER_ID';
698 l_params(1).value := to_char(p_interface_header_id);
699
700 l_progress := 110;
701 x_header_XML := get_xml(l_qryString, l_params);
702
703 l_end_date := sysdate;
704
705 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
706 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
707 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_proc_name),
708 ' done in:' || ICX_CAT_UTIL_PVT.getTimeStats(l_start_date, l_end_date));
709 END IF;
710
711 EXCEPTION
712 WHEN OTHERS THEN
713 ICX_CAT_UTIL_PVT.logUnexpectedException(
714 g_pkg_name, l_proc_name,' --> l_progress:' ||l_progress||' '|| SQLERRM);
715 RAISE;
716 END generate_header_section;
717
718 /*
719 ** Procedure : generate_lines_section
720 ** Synopsis : Function that returns the XML containing
721 ** Line Level Data
722 **
723 ** Parameter:
724 ** IN p_interface_header_id -- interface header id
725 ** p_interface_line_id -- interface line id
726 ** p_category_id -- Category id
727 ** IN OUT
728 ** x_lines_XML -- Clob containing Lines XML
729 **/
730 PROCEDURE generate_lines_section(p_interface_header_id IN NUMBER,
731 p_category_id IN NUMBER,
732 p_language IN VARCHAR2,
733 x_lines_XML IN OUT NOCOPY CLOB)
734 IS
735 l_qryString varchar2(30000);
736 l_params xml_bind_params;
737 x_result CLOB;
738 l_attr_list varchar2(18000);
739 l_progress PLS_INTEGER;
740 l_shopping_category icx_cat_categories_tl.key%TYPE;
741 l_att_list_qry varchar2(20000);
742 l_log_string varchar2(400);
743 l_module_name varchar2(200);
744 l_start_date DATE;
745 l_end_date DATE;
746 l_proc_name varchar2(100) := 'generate_lines_section';
747 BEGIN
748
749 l_start_date := sysdate;
750
751 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
752 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
753 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_proc_name),
754 'Started at:' || TO_CHAR(l_start_date, 'DD-MON-YYYY HH24:MI:SS') ||
755 'Parameters: p_interface_header_id:' || p_interface_header_id||
756 ',p_category_id'|| p_category_id||
757 ',p_language'||p_language);
758 END IF;
759
760 l_progress := 100;
761 -- Retrieve the descriptors list (NameValue string).
762 -- If the lines in two different interface headers have the
763 -- same category id then there is no need to populate the list
764 -- again for the lines of second interface header id.
765 -- Hence invoke the call to populate if data doesn't exist.
766 BEGIN
767 l_progress := 110;
768 IF (p_category_id IS NULL) THEN
769 l_attr_list := ' ';
770 ELSE
771 l_attr_list := g_descriptors_list(p_category_id);
772 END IF;
773 EXCEPTION
774 when no_data_found then
775 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
776 l_log_string := 'generate_lines_section:'||'No data found in g_descriptors_list'||
777 'Invoking populate_namevalue_xmltag for category id'||p_category_id;
778 l_module_name := ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_proc_name);
779 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION, l_module_name, l_log_string);
780 END IF;
781 populate_namevalue_xmltag(p_category_id);
782 l_attr_list := g_descriptors_list(p_category_id);
783 END;
784
785 l_progress := 120;
786 BEGIN
787 SELECT distinct icat.key
788 INTO l_shopping_category
789 FROM icx_cat_categories_tl icat
790 WHERE icat.rt_category_id = p_category_id;
791 EXCEPTION
792 when no_data_found then
793 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
794 l_log_string := 'generate_lines_section:'||'No data found in icx_cat_categories_tl'||
795 'Assigning '' '' to shopping category for category id :'||p_category_id;
796 l_module_name := ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_proc_name);
797 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION, l_module_name, l_log_string);
798 END IF;
799 l_shopping_category := ' ';
800 END;
801
802 l_att_list_qry := ' ';
803 if (length(l_attr_list) > 2) then
804 l_att_list_qry :=
805 'XMLElement("DMIG_NVP", '|| l_attr_list ||'),';
806 end if;
807
808 l_progress := 130;
809 l_qryString :=
810 'SELECT XMLAgg(
811 XMLElement("ITEM",
812 XMLAttributes(''SYNC'' as "action"),
813 XMLElement("SHOPPING_CATEGORY", :SHOPPING_CAT),
814 XMLELEMENT("SUPPLIER_PART_NUM", nvl(to_char(poli.vendor_product_num), '' '')),
815 XMLElement("SUPPLIER_PART_AUXID", nvl(decode (poli.supplier_part_auxid,''##NULL##'','' '',
816 poli.supplier_part_auxid),'' '')),
817 XMLElement("DMIG_NVP", '|| g_descriptors_list(0) ||'),
818 '|| l_att_list_qry ||'
819 XMLElement("PRICE",
820 XMLAttributes(nvl(poli.negotiated_by_preparer_flag, '' '') as "negotiated"),
821 XMLElement("UNIT_PRICE", poli.unit_price),
822 XMLElement("UNIT_OF_MEASURE", poli.uom_code)
823 ))) AS ITEMS_INF
824 FROM po_lines_interface poli, po_attr_values_tlp_interface poavti, po_attr_values_interface poavi
825 WHERE poli.interface_header_id = :INTERFACE_HEADER_ID
826 AND poli.ip_category_id = :IP_CATEGORY_ID
827 AND poavi.interface_header_id = poli.interface_header_id
828 AND poavi.interface_line_id = poli.interface_line_id
829 AND poavti.interface_header_id = poli.interface_header_id
830 AND poavti.interface_line_id = poli.interface_line_id
831 AND poavti.language = :LANGUAGE
832 AND EXISTS (SELECT 1 FROM po_interface_errors poie
833 WHERE poie.interface_header_id = poli.interface_header_id
834 AND (poie.interface_line_id IS NULL OR
835 poie.interface_line_id = poli.interface_line_id))';
836
837
838 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
839 l_log_string := 'generate_lines_section:'||'Binding parameters :interface header id'
840 ||p_interface_header_id||', IP CATEGORY ID:'|| p_category_id
841 ||', LANGUAGE'|| p_language ||', SHOPPING CATEGORY' || l_shopping_category;
842 l_module_name := ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name,l_proc_name);
843 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module_name, l_log_string);
844 END IF;
845
846 -- Set the bind parameter values
847 l_params := xml_bind_params(null, null, null, null);
848 l_params(1).name := 'SHOPPING_CAT';
849 l_params(1).value := nvl(l_shopping_category, ' ');
850
851 l_params(2).name := 'INTERFACE_HEADER_ID';
852 l_params(2).value := to_char(p_interface_header_id);
853
854 l_params(3).name := 'IP_CATEGORY_ID';
855 l_params(3).value := to_char(p_category_id);
856
857 l_params(4).name := 'LANGUAGE';
858 l_params(4).value := to_char(p_language);
859
860 l_progress := 140;
861 x_result := get_xml(l_qryString, l_params);
862
863
864 l_progress := 150;
865 -- replace the <DMIG_NVP> tags with null. These are appeneded as
866 -- a part of building the name value pair in the above query.
867 replace_clob('<DMIG_NVP>', ' ', x_result);
868 replace_clob('</DMIG_NVP>', ' ', x_result);
869 replace_clob('<ITEMS_INF>', ' ', x_result);
870 replace_clob('</ITEMS_INF>', ' ', x_result);
871 replace_clob('<?xml version="1.0"?>', ' ', x_result, false);
872
873 l_progress := 160;
874
875 IF (dbms_lob.getlength(x_lines_XML) > 0) THEN
876 dbms_lob.append(x_lines_XML, x_result);
877 ELSE
878 x_lines_XML := x_result;
879 END IF;
880
881 l_end_date := sysdate;
882
883 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
884 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
885 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_proc_name),
886 ' done in:' || ICX_CAT_UTIL_PVT.getTimeStats(l_start_date, l_end_date));
887 END IF;
888
889 EXCEPTION
890 WHEN OTHERS THEN
891 ICX_CAT_UTIL_PVT.logUnexpectedException(
892 g_pkg_name, l_proc_name,' --> l_progress:' ||l_progress||' '|| SQLERRM);
893 RAISE;
894 END generate_lines_section;
895
896 /**
897 ** Procedure : get_xml
898 ** Synopsis : To create an XML
899 **
900 ** Parameter:
901 ** IN p_qryctx -- Query String
902 ** p_bind_params -- Bind Parameters for the XML Query
903 ** p_row_tag -- Row Tag to set, default NULL
904 ** p_row_settag -- Row SetTag to set, default NULL
905 ** Retruns XML object.
906 **/
907 FUNCTION get_xml(p_qryString IN VARCHAR2,
908 p_bind_params IN xml_bind_params,
909 p_row_tag IN VARCHAR2,
910 p_row_settag IN VARCHAR2)
911 RETURN CLOB
912 IS
913 x_result CLOB;
914 l_progress PLS_INTEGER;
915 l_qryctx DBMS_XMLGEN.ctxhandle;
916 l_log_string varchar2(400);
917 l_module_name varchar2(200);
918 l_start_date DATE;
919 l_end_date DATE;
920 l_proc_name varchar2(100) := 'get_xml';
921 BEGIN
922 l_progress := 100;
923 l_start_date := sysdate;
924 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
925 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
926 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_proc_name),
927 'Started at:' || TO_CHAR(l_start_date, 'DD-MON-YYYY HH24:MI:SS'));
928 END IF;
929 -- Set the bind parameters
930 l_qryctx := DBMS_XMLGEN.newcontext(p_qryString);
931
932 FOR i in 1..p_bind_params.COUNT
933 LOOP
934 DBMS_XMLGEN.setBindValue(l_qryctx, p_bind_params(i).name,
935 p_bind_params(i).value);
936 END LOOP;
937
938 l_progress := 110;
939 -- Set the row tag and rowset tag
940 DBMS_XMLGEN.setRowTag(l_qryctx, p_row_tag);
941 DBMS_XMLGEN.setRowsetTag(l_qryctx, p_row_settag);
942
943 DBMS_XMLGEN.setNullHandling(l_qryctx, DBMS_XMLGEN.EMPTY_TAG);
944 DBMS_XMLGEN.setConvertSpecialChars(l_qryctx, TRUE);
945
946 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
947 l_log_string := 'get_xml: Invoking XMLgen for XML generation';
948 l_module_name := ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name,l_proc_name);
949 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module_name, l_log_string);
950 END IF;
951
952 l_progress := 120;
953 x_result:=DBMS_XMLGEN.getXML(l_qryctx);
954
955 l_progress := 130;
956 DBMS_XMLGEN.closecontext(l_qryctx);
957
958 l_progress := 140;
959 return x_result;
960
961 l_end_date := sysdate;
962
963 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
964 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
965 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_proc_name),
966 ' done in:' || ICX_CAT_UTIL_PVT.getTimeStats(l_start_date, l_end_date));
967 END IF;
968
969 EXCEPTION
970 WHEN OTHERS THEN
971 ICX_CAT_UTIL_PVT.logUnexpectedException(
972 g_pkg_name, l_proc_name,' --> l_progress:' ||l_progress||' '|| SQLERRM);
973 RAISE;
974 END get_xml;
975
976 /**
977 ** Procedure : populate_namevalue_xmltag
978 ** Synopsis : To Populate the Attribute Value List that contains
979 ** key, stored in columns of both the translatable
980 ** and non-translatable interface tables.
981 **
982 ** Parameter:
983 ** IN p_category_id -- Category Id
984 **/
985 PROCEDURE populate_namevalue_xmltag(p_category_id IN NUMBER)
986 IS
987 TYPE descriptors_rec_type IS RECORD(
988 key icx_cat_attributes_tl.KEY%TYPE,
989 stored_in_table icx_cat_attributes_tl.STORED_IN_TABLE%TYPE,
990 stored_in_column icx_cat_attributes_tl.STORED_IN_COLUMN%TYPE);
991
992 TYPE descriptors_tbl_type IS TABLE OF descriptors_rec_type;
993 l_descriptors_tbl descriptors_tbl_type;
994
995 l_attr_list VARCHAR2(18000);
996 l_progress PLS_INTEGER;
997 l_log_string varchar2(400);
998 l_module_name varchar2(200);
999 l_start_date DATE;
1000 l_end_date DATE;
1001 l_proc_name varchar2(100):= 'populate_namevalue_xmltag' ;
1002 BEGIN
1003 l_progress := 100;
1004
1005 l_start_date := sysdate;
1006
1007 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1008 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
1009 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_proc_name),
1010 'Started at:' || TO_CHAR(l_start_date, 'DD-MON-YYYY HH24:MI:SS'));
1011 END IF;
1012
1013 -- Query to select the attributes of a category
1014 SELECT distinct replace(key, '''', ''''''),
1015 decode(stored_in_table,
1016 'PO_ATTRIBUTE_VALUES', 'poavi',
1017 'PO_ATTRIBUTE_VALUES_TLP', 'poavti')
1018 stored_in_table,
1019 stored_in_column
1020 BULK COLLECT INTO l_descriptors_tbl
1021 FROM icx_cat_attributes_tl
1022 WHERE rt_category_id = p_category_id
1023 AND stored_in_Table is not null
1024 AND stored_in_column is not null;
1025
1026 l_progress := 110;
1027
1028 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1029 l_log_string := 'populate_namevalue_xmltag: Concatenating values to generate XML Query String';
1030 l_module_name := ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name,l_proc_name);
1031 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module_name, l_log_string);
1032 END IF;
1033
1034 IF (l_descriptors_tbl.COUNT = 0) THEN
1035 l_attr_list := ' ';
1036 ELSE
1037 l_progress := 120;
1038 -- Forming a string that contains the key, stored in columns as
1039 -- XMLElement("NAMEVALUE", XMLAttributes( key as "name),
1040 -- stored_in_table.stored_in_column)
1041 l_attr_list := 'XMLElement("NAMEVALUE", XMLAttributes('''||
1042 l_descriptors_tbl(1).key||''' as "name"),'||
1043 l_descriptors_tbl(1).stored_in_table||'.'||
1044 l_descriptors_tbl(1).stored_in_column|| ')';
1045
1046 FOR n in 2..l_descriptors_tbl.COUNT
1047 LOOP
1048 l_attr_list := l_attr_list || ', XMLElement("NAMEVALUE", XMLAttributes('''||
1049 l_descriptors_tbl(n).key||''' as "name"),'||
1050 l_descriptors_tbl(n).stored_in_table||'.'||
1051 l_descriptors_tbl(n).stored_in_column|| ')';
1052 END LOOP;
1053 END IF;
1054
1055 l_progress := 130;
1056 -- Assign this string to the descriptors list hash
1057 g_descriptors_list(p_category_id) := l_attr_list;
1058
1059 l_end_date := sysdate;
1060
1061 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1062 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
1063 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_proc_name),
1064 ' done in:' || ICX_CAT_UTIL_PVT.getTimeStats(l_start_date, l_end_date));
1065 END IF;
1066
1067 EXCEPTION
1068 WHEN OTHERS THEN
1069 ICX_CAT_UTIL_PVT.logUnexpectedException(
1070 g_pkg_name, l_proc_name,' --> l_progress:' ||l_progress||' '|| SQLERRM);
1071 RAISE;
1072 END populate_namevalue_xmltag;
1073
1074 /*
1075 ** Procedure : replace_clob
1076 ** Synopsis : Replaces the substring of the CLOB with
1077 ** given string
1078 **
1079 ** Parameter:
1080 ** IN p_replace_str -- String to be replaced
1081 ** p_replace_with -- String to replace with
1082 ** IN OUT p_src_clob -- source object
1083 **/
1084 PROCEDURE replace_clob (p_replace_str IN VARCHAR2,
1085 p_replace_with IN CLOB,
1086 p_src_clob IN OUT NOCOPY CLOB,
1087 p_replace_mutliple_occurances IN BOOLEAN)
1088 IS
1089 x_result CLOB := ' ';
1090 l_variablePosition number;
1091 l_new_variablePosition number;
1092 l_progress PLS_INTEGER;
1093 l_replace_str_length number;
1094 l_replace_with_length number;
1095 l_repeated_flag boolean := FALSE;
1096 l_log_string varchar2(400);
1097 l_module_name varchar2(200);
1098 l_proc_name varchar2(100):= 'replace_clob' ;
1099
1100 BEGIN
1101 l_progress := 110;
1102
1103 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1104 l_log_string := 'ICX_CAT_R12_DATA_EXCEP_RPT_PVT.replace_clob started:-->'||
1105 'l_progress:' ||l_progress;
1106 l_module_name := ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name,l_proc_name);
1107 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module_name, l_log_string);
1108 END IF;
1109
1110 IF (dbms_lob.getlength(p_src_clob) > 0 and length(p_replace_with) > 0)THEN
1111 dbms_lob.createtemporary(x_result, false);
1112 l_variablePosition := DBMS_LOB.INSTR(p_src_clob, p_replace_str );
1113
1114 l_progress := 120;
1115 IF (l_variablePosition >=1) THEN
1116 l_replace_str_length := length(p_replace_str);
1117 l_replace_with_length := length(p_replace_with);
1118
1119 l_progress := 130;
1120 IF (l_variablePosition > 1) THEN
1121 dbms_lob.copy(x_result, p_src_clob, l_variablePosition-1, 1, 1);
1122 END IF;
1123 l_progress := 140;
1124 dbms_lob.append(x_result,p_replace_with);
1125 l_progress := 150;
1126 l_variablePosition := l_variablePosition + l_replace_str_length;
1127
1128 IF (p_replace_mutliple_occurances) THEN
1129 LOOP
1130 l_progress := 160;
1131 l_new_variablePosition := DBMS_LOB.INSTR(p_src_clob,
1132 p_replace_str, l_variablePosition);
1133 EXIT WHEN l_new_variablePosition < 1;
1134
1135 l_progress := 170;
1136 IF (l_new_variablePosition > 1 and
1137 l_new_variablePosition <> l_variablePosition ) THEN
1138 dbms_lob.copy(x_result, p_src_clob,
1139 l_new_variablePosition - l_variablePosition,
1140 dbms_lob.getlength(x_result)+1, l_variablePosition);
1141 END IF;
1142
1143 l_progress := 180;
1144 dbms_lob.writeappend(x_result, l_replace_with_length, p_replace_with);
1145
1146 l_progress := 190;
1147 l_variablePosition := l_new_variablePosition + l_replace_str_length;
1148 l_repeated_flag := TRUE;
1149 END LOOP;
1150 IF (l_repeated_flag) THEN
1151 l_variablePosition := l_variablePosition + l_replace_str_length;
1152 END IF;
1153 END IF;
1154
1155 l_progress := 200;
1156 IF ( (dbms_lob.getlength(p_src_clob)) - (l_variablePosition) > 0) then
1157 if (l_repeated_flag) then
1158 dbms_lob.copy(x_result, p_src_clob,
1159 dbms_lob.getlength(p_src_clob)-l_variablePosition+l_replace_str_length+1,
1160 dbms_lob.getlength(x_result)+1, l_variablePosition-l_replace_str_length);
1161 else
1162 dbms_lob.copy(x_result, p_src_clob,
1163 dbms_lob.getlength(p_src_clob)-l_variablePosition+1,
1164 dbms_lob.getlength(x_result)+1, l_variablePosition);
1165 end if;
1166 END IF;
1167 l_progress := 210;
1168 p_src_clob := x_result;
1169 END IF;
1170 dbms_lob.freetemporary(x_result);
1171 END IF;
1172
1173 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1174 l_log_string := 'ICX_CAT_R12_DATA_EXCEP_RPT_PVT.replace_clob completed:-->'||
1175 'l_progress:' ||l_progress;
1176 l_module_name := ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name,l_proc_name);
1177 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module_name, l_log_string);
1178 END IF;
1179
1180 EXCEPTION
1181 WHEN OTHERS THEN
1182 ICX_CAT_UTIL_PVT.logUnexpectedException(
1183 g_pkg_name, l_proc_name,' --> l_progress:' ||l_progress||' '|| SQLERRM);
1184 RAISE;
1185 END replace_clob;
1186
1187 END ICX_CAT_R12_DATA_EXCEP_RPT_PVT;