[Home] [Help]
PACKAGE BODY: APPS.FEM_TABLE_PUBLISH_PKG
Source
1 PACKAGE BODY FEM_TABLE_PUBLISH_PKG AS
2 /* $Header: FEMVDIPUBB.pls 120.3 2007/07/30 15:18:12 gdonthir noship $ */
3
4 PROCEDURE Generate_XML_CP(
5 x_retcode OUT NOCOPY NUMBER,
6 x_errbuff OUT NOCOPY VARCHAR2,
7 p_diObjDefId IN NUMBER,
8 p_view IN VARCHAR2,
9 p_comp_totals IN VARCHAR2
10 )
11 IS
12
13 x_xml_result CLOB;
14
15
16 BEGIN
17 Generate_XML(
18 x_retcode,
19 x_errbuff,
20 p_diObjDefId,
21 x_xml_result,
22 'OFFLINE',
23 p_view,
24 p_comp_totals);
25
26 EXCEPTION
27
28 WHEN OTHERS THEN
29 fem_engines_pkg.tech_message (p_severity => g_log_level_1,
30 p_module => g_block||'.Generate_XML_CP()',
31 p_msg_text => 'EXCEPTION BLOCK:'||sqlerrm);
32
33 x_retCode := 2;
34
35 END Generate_XML_CP;
36
37
38
39 PROCEDURE Run_Report
40 (
41 x_req_id OUT NOCOPY NUMBER,
42 x_retcode OUT NOCOPY NUMBER,
43 x_errbuff OUT NOCOPY VARCHAR2,
44 x_xml_result OUT NOCOPY CLOB,
45 p_diObjDefId IN NUMBER,
46 p_gen_mode IN VARCHAR2,
47 p_gen_format IN VARCHAR2,
48 p_gen_template IN VARCHAR2,
49 p_view IN VARCHAR2,
50 p_comp_totals IN VARCHAR2,
51 p_diQuery IN VARCHAR2
52 )
53 IS
54
55 l_xml_layout boolean:=FALSE;
56 l_req_id NUMBER;
57 l_iso_lang VARCHAR2(2);
58 l_iso_terr VARCHAR2(2);
59
60 CURSOR get_lang_terr_csr IS
61 SELECT LOWER(ISO_LANGUAGE) lang, ISO_TERRITORY terr FROM FND_LANGUAGES WHERE LANGUAGE_CODE = USERENV('LANG');
62
63
64 BEGIN
65
66 fem_engines_pkg.tech_message (p_severity => g_log_level_1,
67 p_module => g_block||'.Run_Report()',
68 p_msg_text => 'Parameters:'||p_diObjDefId||','||p_gen_mode||','||p_gen_format||','||p_gen_template||','||p_view||','||p_comp_totals||','||p_diQuery);
69
70
71 FOR lang_rec in get_lang_terr_csr LOOP
72 l_iso_lang := lang_rec.lang;
73 l_iso_terr := lang_rec.terr;
74 END LOOP;
75
76 fem_engines_pkg.tech_message (p_severity => g_log_level_1,
77 p_module => g_block||'.Run_Report()',
78 p_msg_text => 'l_iso_lang:' ||l_iso_lang ||','||'l_iso_terr:'||l_iso_terr);
79
80 IF p_gen_template = 'FEMDIREP' THEN
81
82 IF p_gen_mode = 'OFFLINE' THEN
83 l_xml_layout := FND_REQUEST.ADD_LAYOUT('FEM',p_gen_template,l_iso_lang,l_iso_terr,p_gen_format);
84 l_req_id := FND_REQUEST.SUBMIT_REQUEST
85 (application => 'FEM',
86 program => 'FEMDIREPDEF',
87 description => NULL,
88 start_time => NULL,
89 sub_request => FALSE,
90 argument1 => p_diObjDefId,
91 argument2 => p_view,
92 argument3 => p_comp_totals
93 );
94 x_req_id := l_req_id;
95 ELSE
96
97 Generate_XML(
98 x_retcode,
99 x_errbuff,
100 p_diObjDefId,
101 x_xml_result,
102 p_gen_mode,
103 p_view,
104 p_comp_totals);
105
106 END IF;
107
108 ELSE
109
110 IF p_gen_mode = 'OFFLINE' THEN
111 l_xml_layout := FND_REQUEST.ADD_LAYOUT('FEM',p_gen_template,l_iso_lang,l_iso_terr,p_gen_format);
112 l_req_id := FND_REQUEST.SUBMIT_REQUEST
113 (application => 'FEM',
114 program => 'FEMDICREPDEF',
115 description => NULL,
116 start_time => NULL,
117 sub_request => FALSE,
118 argument1 => p_diObjDefId,
119 argument2 => p_diQuery,
120 argument3 => p_view
121 );
122 x_req_id := l_req_id;
123 ELSE
124
125 Generate_Cust_XML(
126 p_diObjDefId,
127 p_diQuery,
128 p_gen_mode,
129 p_view,
130 x_xml_result
131 );
132
133 END IF;
134
135 END IF;
136 COMMIT;
137 END Run_Report;
138
139
140
141
142
143 PROCEDURE Generate_XML(
144 x_retcode OUT NOCOPY NUMBER,
145 x_errbuff OUT NOCOPY VARCHAR2,
146 p_diObjDefId IN NUMBER,
147 x_xml_result OUT NOCOPY CLOB,
148 p_mode IN VARCHAR2,
149 p_view IN VARCHAR2,
150 p_comp_totals IN VARCHAR2)
151 IS
152
153 selectclause CLOB;
154 l_query CLOB;
155 fromclause CLOB;
156 --tablename CLOB;
157 l_metadata_xml CLOB;
158 l_test_xml CLOB;
159 l_test1_xml CLOB;
160 dim VARCHAR2(20);
161 l_xmlresult CLOB;
162 --queryCtx DBMS_XMLGEN.ctxHandle;
163 queryCtx DBMS_XMLquery.ctxType;
164 l_tablename VARCHAR2(50);
165 l_clob_size NUMBER;
166 l_data_size NUMBER;
167 l_offset NUMBER;
168 l_chunk_size INTEGER;
169 l_chunk VARCHAR2(32767);
170 l_condition_Obj_id NUMBER;
171 l_condition_name VARCHAR2(100);
172 whereClause CLOB;
173 l_dataHeaderClob CLOB;
174 l_table_display_name VARCHAR2(255);
175 l_di_name VARCHAR2(255);
176 l_header varchar2(200);
177 l_data_present varchar2(4);
178 col_count NUMBER:=0;
179 P_INSTN_AMT INTEGER:=23;
180 l_eraseamt INTEGER:=3;
181 P_RT_AMT BINARY_INTEGER:=6;
182 l_bal_sum NUMBER;
183 l_bal_sum_query VARCHAR2(32767);
184 l_bal_cols_present varchar2(4):='No';
185 l_order_col VARCHAR2(30);
186 l_sort_dir_flag VARCHAR2(1);
187 l_order_by_added VARCHAR2(1):='N';
188 l_queryString VARCHAR2(32767);
189 l_xmlString VARCHAR2(32767);
190
191 --CURSOR get_required_columns_cursor(p_tablename IN VARCHAR2) IS
192 CURSOR get_required_columns_cursor IS
193 SELECT DataInspectorColumnsEO.DATA_INSPECTOR_OBJ_DEF_ID,
194 DataInspectorColumnsEO.TABLE_NAME,
195 DataInspectorColumnsEO.COLUMN_NAME,
196 DataInspectorColumnsEO.DISPLAY_SEQUENCE,
197 EnabledTableColumnEO.DISPLAY_NAME,
198 EnabledTableColumnEO.FEM_DATA_TYPE_CODE,
199 EnabledTableColumnEO.DIMENSION_ID,
200 DataInspectorColumnsEO.SORT_SEQUENCE,
201 DataInspectorColumnsEO.SORT_DIRECTION_FLAG,
202 (select member_name_col from fem_xdim_dimensions where dimension_id = EnabledTableColumnEO.DIMENSION_ID) AS
203 member_name_col,
204 (select member_display_code_col from fem_xdim_dimensions where dimension_id = EnabledTableColumnEO.DIMENSION_ID) AS
205 member_code_col,
206 (select member_vl_object_name from fem_xdim_dimensions where dimension_id = EnabledTableColumnEO.DIMENSION_ID) AS
207 member_table_name,
208 (select dimension_varchar_label from fem_dimensions_b where dimension_id = EnabledTableColumnEO.DIMENSION_ID) AS
209 dimension_varchar_label,
210 (select dimension_name from fem_dimensions_vl where dimension_id = EnabledTableColumnEO.DIMENSION_ID) AS dimension_name,
211 (select member_col from fem_xdim_dimensions where dimension_id = EnabledTableColumnEO.DIMENSION_ID) AS member_col
212 FROM FEM_DATA_INSPECTOR_COLS DataInspectorColumnsEO,
213 FEM_TAB_COLUMNS_V EnabledTableColumnEO
214 WHERE EnabledTableColumnEO.TABLE_NAME = DataInspectorColumnsEO.TABLE_NAME
215 AND EnabledTableColumnEO.COLUMN_NAME = DataInspectorColumnsEO.COLUMN_NAME
216 AND DataInspectorColumnsEO.DATA_INSPECTOR_OBJ_DEF_ID = p_diObjDefId
217 ORDER BY DataInspectorColumnsEO.DISPLAY_SEQUENCE ASC;
218
219 CURSOR column_order_csr IS
220 SELECT COLUMN_NAME,
221 SORT_SEQUENCE,
222 SORT_DIRECTION_FLAG
223 FROM FEM_DATA_INSPECTOR_COLS
224 WHERE DATA_INSPECTOR_OBJ_DEF_ID = p_diObjDefId
225 AND SORT_SEQUENCE IS NOT NULL
226 ORDER BY SORT_SEQUENCE ASC;
227
228 CURSOR get_table_name_csr IS
229 SELECT TABLE_NAME FROM FEM_DATA_INSPECTORS WHERE DATA_INSPECTOR_OBJ_DEF_ID = p_diObjDefId;
230
231 CURSOR get_condition_Obj_id_cursor IS
232 SELECT FEM_DATA_INSPECTORS.CONDITION_OBJ_ID, FEM_OBJECT_CATALOG_VL.OBJECT_NAME FROM FEM_DATA_INSPECTORS, FEM_OBJECT_CATALOG_VL
233 WHERE FEM_DATA_INSPECTORS.DATA_INSPECTOR_OBJ_DEF_ID = p_diObjDefId AND
234 FEM_OBJECT_CATALOG_VL.OBJECT_ID = FEM_DATA_INSPECTORS.CONDITION_OBJ_ID;
235
236 CURSOR get_table_display_name_cursor IS
237 SELECT DISPLAY_NAME FROM FEM_TABLES_VL WHERE TABLE_NAME = l_tablename;
238
239 cURSOR get_di_name_csr is
240 SELECT display_name from fem_object_definition_vl where object_definition_id = p_diObjDefId;
241
242 BEGIN
243
244 fem_engines_pkg.tech_message (p_severity => g_log_level_1,
245 p_module => g_block||'.Generate_XML()',
246 p_msg_text => 'BEGIN');
247
248 dim := 'DIMENSION';
249 fnd_file.put_line(fnd_file.log, '***************************Parameters****************************');
250 fnd_file.put_line(fnd_file.log, '* DI Object Def Id : ' || to_char(p_diObjDefId));
251
252 /*****************************************************************
253 Get the Table name
254 *****************************************************************/
255
256 OPEN get_table_name_csr;
257 FETCH get_table_name_csr INTO l_tableName;
258 CLOSE get_table_name_csr;
259
260 /*****************************************************************
261 Get the condition details.
262 *****************************************************************/
263
264 OPEN get_condition_obj_id_cursor;
265 FETCH get_condition_obj_id_cursor INTO l_condition_obj_id,l_condition_name ;
266 CLOSE get_condition_obj_id_cursor;
267
268 fnd_file.put_line(fnd_file.log,'* Condition Id: ' || l_condition_obj_id);
269
270 /*****************************************************************
271 Get the DI Name.
272 *****************************************************************/
273
274 OPEN get_di_name_csr;
275 FETCH get_di_name_csr INTO l_di_name;
276 CLOSE get_di_name_csr;
277
278 fnd_file.put_line(fnd_file.log,'* DI Name: ' || l_di_name);
279
280 /*****************************************************************
281 Get the Condition predicate.
282 *****************************************************************/
283
284 IF l_condition_obj_id IS NOT NULL THEN
285 getConditionPredicate(l_condition_obj_id,l_tableName,whereClause);
286 END IF;
287
288 fnd_file.put_line(fnd_file.log,'* Condition Predicate: ' || whereClause);
289
290
291 /*****************************************************************
292 Get the table display name.
293 *****************************************************************/
294
295 OPEN get_table_display_name_cursor;
296 FETCH get_table_display_name_cursor INTO l_table_display_name;
297 CLOSE get_table_display_name_cursor;
298
299 fnd_file.put_line(fnd_file.log,'* Table Display Name: ' || l_table_display_name);
300
301
302 fnd_file.put_line(fnd_file.log,'* Show: ' || p_view);
303 fnd_file.put_line(fnd_file.log,'* Compute Column Totals: ' || p_comp_totals);
304 fnd_file.put_line(fnd_file.log,'* Execute: ' || p_mode);
305 fnd_file.put_line(fnd_file.log,'***************************Parameters****************************');
306 fnd_file.put_line(fnd_file.log,' ');
307
308 /*****************************************************************
309 Add header details to CLOB
310 *****************************************************************/
311 dbms_lob.createtemporary(l_metadata_xml,TRUE);
312
313 l_xmlString := '<?xml version=''1.0''?>';
314 l_xmlString := l_xmlString || '<Root>';
315 l_xmlString := l_xmlstring || ' <TABLE_NAME>'|| l_table_display_name || '</TABLE_NAME>';
316 l_xmlString := l_xmlString || ' <CONDITION_NAME>'|| l_condition_name || '</CONDITION_NAME>';
317 l_xmlString := l_xmlString || ' <DI_NAME>'|| l_di_name || '</DI_NAME>';
318 l_xmlString := l_xmlString || ' <COL_DEFS>';
319
320 dbms_lob.writeappend(l_metadata_xml,LENGTH(l_xmlString),l_xmlString);
321 /**********************************************************
322 Prepare the query
323 ***********************************************************/
324 dbms_lob.createtemporary(selectClause,TRUE);
325 dbms_lob.createtemporary(l_query,TRUE);
326
327 FOR row_record in get_required_columns_cursor LOOP
328
329
330 l_tablename := row_record.TABLE_NAME;
331 col_count := col_count + 1;
332
333
334 l_xmlString := ' <COL_DEF>';
335 l_xmlString := l_xmlString || ' <COL_NUM>'||col_count||'</COL_NUM> ';
336 l_xmlString := l_xmlString || ' <COL_NAME>'||row_record.DISPLAY_NAME||'</COL_NAME> ' ;
337 l_xmlString := l_xmlString || ' <COL_TYPE>' || row_record.FEM_DATA_TYPE_CODE ||'</COL_TYPE> ';
338
339 IF(p_comp_totals = 'Y' AND row_record.FEM_DATA_TYPE_CODE = 'BALANCE') THEN
340 l_bal_cols_present := 'Yes';
341 l_bal_sum_query := ' SELECT SUM(' || row_record.COLUMN_NAME || ') FROM ' || row_record.TABLE_NAME;
342
343 IF whereClause IS NOT NULL THEN
344 l_bal_sum_query := l_bal_sum_query || ' WHERE ' || whereClause;
345 END IF;
346
347 fnd_file.put_line(fnd_file.log,'Bal Sum Query: ' || l_bal_sum_query);
348 fnd_file.put_line(fnd_file.log,' ');
349 EXECUTE IMMEDIATE l_bal_sum_query INTO l_bal_sum;
350 l_xmlString := l_xmlString || ' <COL_TOTAL>' || l_bal_sum ||'</COL_TOTAL> ';
351
352 ELSE
353
354 l_bal_cols_present := 'No';
355
356 END IF;
357
358 l_xmlString := l_xmlString || ' </COL_DEF>';
359
360
361 IF (row_record.FEM_DATA_TYPE_CODE = dim AND (p_view = 'Name')) THEN
362 l_queryString := ' CURSOR ( SELECT '''|| col_count || ''' as colNum,
363 '||row_record.member_table_name || '.' || row_record.member_name_col || ' as colValue ' ||' from ' ||
364 row_record.member_table_name || ', ' || l_tablename ||' b ' ||' where ' || row_record.member_table_name || '.' || row_record.member_col || '(+)=' ||
365 'b' || '.'||row_record.COLUMN_NAME || ' and a.rowid = b.rowid ' || ') as col , ';
366
367 ELSIF (row_record.FEM_DATA_TYPE_CODE = dim AND (p_view = 'Code')) THEN
368 l_queryString := ' CURSOR ( SELECT ''' || col_count || ''' as colNum,
369 '||row_record.member_table_name || '.' || row_record.member_code_col || ' as colValue ' ||' from ' ||
370 row_record.member_table_name || ', ' || l_tablename ||' b ' ||' where ' || row_record.member_table_name || '.' || row_record.member_col || '(+)=' ||
371 'b' || '.'||row_record.COLUMN_NAME || ' and a.rowid = b.rowid ' || ') as col , ' ;
372
373
374 ELSE
375
376 l_queryString := ' CURSOR ( SELECT ''' || col_count || ''' as colNum, '||
377 row_record.TABLE_NAME || '.' || ROW_RECORD.COLUMN_NAME || ' as colValue' || ' from ' || row_record.TABLE_NAME ||
378 ' where ' || row_record.TABLE_NAME || '.' || 'ROWID' || ' = ' || 'a' || '.' || 'ROWID'
379 || ' ) as col , ' ;
380
381 END IF;
382 --fnd_file.put_line(fnd_file.log,l_queryString);
383 dbms_lob.writeappend(selectClause,LENGTH(l_queryString),l_queryString);
384 dbms_lob.writeappend(l_metadata_xml,LENGTH(l_xmlString),l_xmlString);
385
386 END LOOP;
387
388 l_xmlString := ' </COL_DEFS>';
389 l_xmlString := l_xmlString || ' <BAL_COLS_PRESENT>' ||l_bal_cols_present||'</BAL_COLS_PRESENT> ';
390 dbms_lob.writeappend(l_metadata_xml,LENGTH(l_xmlString),l_xmlString);
391
392
393
394 dbms_lob.erase(selectClause,l_eraseamt,dbms_lob.getlength(selectClause)-2);
395
396 /**************************************************
397 Prepare the final query
398 **************************************************/
399
400
401 /**************************************************
402 Add Condition clause.
403 **************************************************/
404
405 --l_query := 'SELECT ' || selectclause || ' FROM ' || l_tablename || ' a';
406
407 l_queryString := 'SELECT ';
408 dbms_lob.writeappend(l_query,LENGTH(l_queryString),l_queryString);
409 --fnd_file.put_line(fnd_file.log,'After appending to l_query');
410 dbms_lob.append(l_query,selectClause);
411 l_queryString := ' FROM ' || l_tablename || ' a';
412 dbms_lob.writeappend(l_query,LENGTH(l_queryString),l_queryString);
413
414 IF whereClause IS NOT NULL THEN
415 --l_query := l_query ||' WHERE ' || whereClause;
416 l_queryString := ' WHERE ';
417 dbms_lob.writeappend(l_query,LENGTH(l_queryString),l_queryString);
418 dbms_lob.append(l_query,whereClause);
419 END IF;
420
421
422
423
424 /**************************************************
425 Add Order by Clause
426 **************************************************/
427
428
429 FOR order_record in column_order_csr LOOP
430
431 IF(l_order_by_added = 'N') THEN
432 l_queryString := ' ORDER BY ';
433 l_order_by_added := 'Y';
434 END IF;
435
436 l_order_col := order_record.COLUMN_NAME;
437 l_sort_dir_flag := order_record.SORT_DIRECTION_FLAG;
438 l_queryString := l_queryString || ' a.' || l_order_col;
439
440 IF(l_sort_dir_flag = 'A') THEN
441 l_queryString := l_queryString || ' ASC,';
442 ELSE
443 l_queryString := l_queryString || ' DESC,';
444 END IF;
445
446 END LOOP;
447
448 IF(l_order_by_added = 'Y') THEN
449 l_queryString := SUBSTR(l_queryString, 1, LENGTH(l_queryString) - 1);
450 dbms_lob.writeappend(l_query,LENGTH(l_queryString),l_queryString);
451 END IF;
452
453 fnd_file.put_line(fnd_file.log, 'The final query : ');
454 --fnd_file.put_line(fnd_file.log,l_query);
455
456
457
458 /**************************************************
459 Get the xml.
460 **************************************************/
461 fnd_file.put_line(fnd_file.log,'************************************XML******************************************************');
462
463 queryCtx := DBMS_XMLQuery.newContext(l_query);
464 DBMS_XMLQuery.setRowsetTag(queryCtx, 'Extracted_Records');
465 DBMS_XMLQuery.setRowTag(queryCtx, 'Row');
466
467 l_xmlResult := DBMS_XMLQuery.getXML(queryCtx);
468 DBMS_XMLQuery.closeContext(queryCtx);
469
470 l_data_size := dbms_lob.getlength(l_xmlResult);
471 if l_data_size > 45 then
472 l_data_present := 'Yes';
473 else
474 l_data_present := 'No';
475 end if;
476
477 l_xmlString := ' <DATA_PRESENT>' || l_data_present || '</DATA_PRESENT>';
478 dbms_lob.writeappend(l_metadata_xml,LENGTH(l_xmlString),l_xmlString);
479
480
481 /**************************************************
482 Add result xml to metadata xml after removing the processing instruction.
483 metadata xml is your final clob.
484 **************************************************/
485 dbms_lob.erase(l_xmlResult,P_INSTN_AMT,1);
486 dbms_lob.write(l_xmlResult,P_INSTN_AMT,1,'<!-- PINSTN removed -->');
487 dbms_lob.append(l_metadata_xml,l_xmlResult);
488
489 --Close off. l_metadata_xml is your final clob.
490 l_xmlString := '</Root>';
491 dbms_lob.writeappend(l_metadata_xml,LENGTH(l_xmlString),l_xmlString);
492
493
494 l_clob_size := dbms_lob.getlength(l_metadata_xml);
495 l_offset := 1;
496 l_chunk_size := 3000;
497
498 WHILE (l_clob_size > 0) LOOP
499
500 l_chunk := dbms_lob.substr (l_metadata_xml, l_chunk_size, l_offset);
501 --fnd_file.put_line(fnd_file.log,l_chunk);
502 fnd_file.put(
503 which => fnd_file.output,
504 buff => l_chunk);
505 l_clob_size := l_clob_size - l_chunk_size;
506 l_offset := l_offset + l_chunk_size;
507
508 END LOOP;
509
510 IF p_mode = 'ONLINE' THEN
511 x_xml_result := l_metadata_xml;
512 END IF;
513
514 fem_engines_pkg.tech_message (p_severity => g_log_level_1,
515 p_module => g_block||'.Generate_XML()',
516 p_msg_text => 'END');
517
518
519 dbms_lob.freetemporary(selectClause);
520 dbms_lob.freetemporary(l_query);
521 dbms_lob.freetemporary(l_metadata_xml);
522
523 EXCEPTION
524 WHEN OTHERS THEN
525 fnd_file.put_line(fnd_file.log,sqlerrm);
526 fem_engines_pkg.tech_message (p_severity => g_log_level_1,
527 p_module => g_block||'.Generate_XML()',
528 p_msg_text => 'EXCEPTION BLOCK:'||sqlerrm);
529
530 END Generate_XML;
531
532
533
534
535 PROCEDURE Generate_Cust_XML_CP
536 (
537 x_retcode OUT NOCOPY NUMBER,
538 x_errbuff OUT NOCOPY VARCHAR2,
539 p_diObjDefId IN NUMBER,
540 p_diQuery IN VARCHAR2,
541 p_view IN VARCHAR2
542 )
543 IS
544
545 x_xml_result CLOB;
546
547
548 BEGIN
549 Generate_Cust_XML(
550 p_diObjDefId,
551 p_diQuery,
552 'OFFLINE',
553 p_view,
554 x_xml_result
555 );
556
557 EXCEPTION
558
559 WHEN OTHERS THEN
560 x_retCode := 2;
561 fem_engines_pkg.tech_message (p_severity => g_log_level_1,
562 p_module => g_block||'.Generate_Cust_XML_CP()',
563 p_msg_text => 'EXCEPTION BLOCK:'||sqlerrm);
564
565 END Generate_Cust_XML_CP;
566
567
568
569
570
571
572 PROCEDURE Generate_Cust_XML
573 (
574 p_diObjDefId IN NUMBER,
575 p_diQuery IN VARCHAR2,
576 p_mode IN VARCHAR2,
577 p_view IN VARCHAR2,
578 x_xml_result OUT NOCOPY CLOB
579 )
580 IS
581
582 selectclause CLOB;
583 l_query CLOB;
584 fromclause CLOB;
585 --tablename CLOB;
586 l_metadata_xml CLOB;
587 l_test_xml CLOB;
588 l_test1_xml CLOB;
589 dim VARCHAR2(20);
590 l_xmlresult CLOB;
591 --queryCtx DBMS_XMLGEN.ctxHandle;
592 queryCtx DBMS_XMLquery.ctxType;
593 l_tablename VARCHAR2(50);
594 l_clob_size NUMBER;
595 l_data_size NUMBER;
596 l_offset NUMBER;
597 l_chunk_size INTEGER;
598 l_chunk VARCHAR2(32767);
599 trashStr VARCHAR2(32767);
600 sortseq VARCHAR2(32767);
601 l_condition_Obj_id NUMBER;
602 l_condition_name VARCHAR2(100);
603 whereClause CLOB;
604 l_dataHeaderClob CLOB;
605 l_table_display_name VARCHAR2(255);
606 l_di_name VARCHAR2(255);
607 l_header varchar2(200);
608 l_data_present varchar2(4);
609 col_count NUMBER:=0;
610 P_INSTN_AMT INTEGER:=23;
611 l_eraseamt INTEGER:=3;
612 P_RT_AMT BINARY_INTEGER:=6;
613 l_bal_sum NUMBER;
614 l_bal_sum_query VARCHAR2(32767);
615 l_bal_cols_present varchar2(4):='No';
616 l_order_col VARCHAR2(30);
617 l_sort_dir_flag VARCHAR2(1);
618 l_order_by_added VARCHAR2(1):='N';
619 l_queryString VARCHAR2(32767);
620
621 CURSOR get_required_columns_cursor IS
622 SELECT DataInspectorColumnsEO.DATA_INSPECTOR_OBJ_DEF_ID,
623 DataInspectorColumnsEO.TABLE_NAME,
624 DataInspectorColumnsEO.COLUMN_NAME,
625 DataInspectorColumnsEO.DISPLAY_SEQUENCE,
626 EnabledTableColumnEO.DISPLAY_NAME,
627 EnabledTableColumnEO.FEM_DATA_TYPE_CODE,
628 EnabledTableColumnEO.DIMENSION_ID,
629 DataInspectorColumnsEO.SORT_SEQUENCE,
630 DataInspectorColumnsEO.SORT_DIRECTION_FLAG,
631 (select member_name_col from fem_xdim_dimensions where dimension_id = EnabledTableColumnEO.DIMENSION_ID) AS
632 member_name_col,
633 (select member_display_code_col from fem_xdim_dimensions where dimension_id = EnabledTableColumnEO.DIMENSION_ID) AS
634 member_code_col,
635 (select member_vl_object_name from fem_xdim_dimensions where dimension_id = EnabledTableColumnEO.DIMENSION_ID) AS
636 member_table_name,
637 (select dimension_varchar_label from fem_dimensions_b where dimension_id = EnabledTableColumnEO.DIMENSION_ID) AS
638 dimension_varchar_label,
639 (select dimension_name from fem_dimensions_vl where dimension_id = EnabledTableColumnEO.DIMENSION_ID) AS dimension_name,
640 (select member_col from fem_xdim_dimensions where dimension_id = EnabledTableColumnEO.DIMENSION_ID) AS member_col
641 FROM FEM_DATA_INSPECTOR_COLS DataInspectorColumnsEO,
642 FEM_TAB_COLUMNS_V EnabledTableColumnEO
643 WHERE EnabledTableColumnEO.TABLE_NAME = DataInspectorColumnsEO.TABLE_NAME
644 AND EnabledTableColumnEO.COLUMN_NAME = DataInspectorColumnsEO.COLUMN_NAME
645 AND DataInspectorColumnsEO.DATA_INSPECTOR_OBJ_DEF_ID = p_diObjDefId
646 ORDER BY DataInspectorColumnsEO.DISPLAY_SEQUENCE ASC;
647
648 CURSOR get_table_name_csr IS
649 SELECT TABLE_NAME FROM FEM_DATA_INSPECTORS WHERE DATA_INSPECTOR_OBJ_DEF_ID = p_diObjDefId;
650
651 CURSOR get_condition_Obj_id_cursor IS
652 SELECT FEM_DATA_INSPECTORS.CONDITION_OBJ_ID, FEM_OBJECT_CATALOG_VL.OBJECT_NAME FROM FEM_DATA_INSPECTORS, FEM_OBJECT_CATALOG_VL
653 WHERE FEM_DATA_INSPECTORS.DATA_INSPECTOR_OBJ_DEF_ID = p_diObjDefId AND
654 FEM_OBJECT_CATALOG_VL.OBJECT_ID = FEM_DATA_INSPECTORS.CONDITION_OBJ_ID;
655
656
657 CURSOR column_order_csr IS
658 SELECT COLUMN_NAME,
659 SORT_SEQUENCE,
660 SORT_DIRECTION_FLAG
661 FROM FEM_DATA_INSPECTOR_COLS
662 WHERE DATA_INSPECTOR_OBJ_DEF_ID = p_diObjDefId
663 AND SORT_SEQUENCE IS NOT NULL
664 ORDER BY SORT_SEQUENCE ASC;
665
666
667 BEGIN
668
669 fem_engines_pkg.tech_message (p_severity => g_log_level_1,
670 p_module => g_block||'.Generate_Cust_XML()',
671 p_msg_text => 'BEGIN');
672
673
674 dim := 'DIMENSION';
675 fnd_file.put_line(fnd_file.log, 'DI Object Def Id:' || to_char(p_diObjDefId));
676 fnd_file.put_line(fnd_file.log, 'View:'||p_view);
677
678 /*****************************************************************
679 Get the Table name
680 *****************************************************************/
681
682 OPEN get_table_name_csr;
683 FETCH get_table_name_csr INTO l_tableName;
684 CLOSE get_table_name_csr;
685
686 IF p_diQuery IS NULL THEN
687
688 /*****************************************************************
689 Get the condition details.
690 *****************************************************************/
691
692 OPEN get_condition_obj_id_cursor;
693 FETCH get_condition_obj_id_cursor INTO l_condition_obj_id,l_condition_name ;
694 CLOSE get_condition_obj_id_cursor;
695
696 /*****************************************************************
697 Get the Condition predicate.
698 *****************************************************************/
699
700 IF l_condition_obj_id IS NOT NULL THEN
701 getConditionPredicate(l_condition_obj_id,l_tableName,whereClause);
702 END IF;
703
704 fnd_file.put_line(fnd_file.log,'Condition predicate is:' || whereClause);
705
706 dbms_lob.createtemporary(selectClause,TRUE);
707 dbms_lob.createtemporary(l_query,TRUE);
708 l_queryString := 'SELECT ';
709 dbms_lob.writeappend(selectClause,LENGTH(l_queryString),l_queryString);
710
711 FOR row_record in get_required_columns_cursor LOOP
712
713 --Bug#6174477: Change the query as per view option
714 IF (row_record.FEM_DATA_TYPE_CODE = dim AND (p_view = 'Name')) THEN
715 l_queryString := ' ( SELECT ' ||
716 row_record.member_table_name || '.' || row_record.member_name_col ||' from ' ||
717 row_record.member_table_name || ', ' || l_tablename ||' b ' ||' where ' || row_record.member_table_name || '.' || row_record.member_col || '(+)=' ||
718 'b' || '.'||row_record.COLUMN_NAME || ' and a.rowid = b.rowid ' || ') as ' || row_record.member_name_col || ' , ';
719
720 ELSIF (row_record.FEM_DATA_TYPE_CODE = dim AND (p_view = 'Code')) THEN
721 l_queryString := ' ( SELECT ' ||
722 row_record.member_table_name || '.' || row_record.member_code_col ||' from ' ||
723 row_record.member_table_name || ', ' || l_tablename ||' b ' ||' where ' || row_record.member_table_name || '.' || row_record.member_col || '(+)=' ||
724 'b' || '.'||row_record.COLUMN_NAME || ' and a.rowid = b.rowid ' || ') as ' || row_record.member_code_col || ' , ';
725
726 ELSE
727 l_queryString := row_record.COLUMN_NAME || ' , ';
728
729 END IF;
730
731 dbms_lob.writeappend(selectClause,LENGTH(l_queryString),l_queryString);
732
733 END LOOP;
734
735 dbms_lob.erase(selectClause,l_eraseamt,dbms_lob.getlength(selectClause)-2);
736
737 /** Prepare the final Query**/
738
739 dbms_lob.append(l_query,selectClause);
740 l_queryString := ' FROM ' || l_tablename || ' a';
741 dbms_lob.writeappend(l_query,LENGTH(l_queryString),l_queryString);
742 /**************************************************
743 Add Condition clause.
744 **************************************************/
745
746 IF whereClause IS NOT NULL THEN
747 l_queryString := ' WHERE ';
748 dbms_lob.writeappend(l_query,LENGTH(l_queryString),l_queryString);
749 dbms_lob.append(l_query,whereClause);
750 END IF;
751
752 /**************************************************
753 Add Order by Clause
754 **************************************************/
755
756
757 FOR order_record in column_order_csr LOOP
758
759 IF(l_order_by_added = 'N') THEN
760 l_queryString := ' ORDER BY ';
761 l_order_by_added := 'Y';
762 END IF;
763
764 l_order_col := order_record.COLUMN_NAME;
765 l_sort_dir_flag := order_record.SORT_DIRECTION_FLAG;
766 l_queryString := l_queryString || ' a.' || l_order_col;
767
768 IF(l_sort_dir_flag = 'A') THEN
769 l_queryString := l_queryString || ' ASC,';
770 ELSE
771 l_queryString := l_queryString || ' DESC,';
772 END IF;
773
774 END LOOP;
775
776 IF(l_order_by_added = 'Y') THEN
777 l_queryString := SUBSTR(l_queryString, 1, LENGTH(l_queryString) - 1);
778 dbms_lob.writeappend(l_query,LENGTH(l_queryString),l_queryString);
779 END IF;
780
781 ELSE
782
783 l_query := p_diQuery;
784
785 END IF;
786
787 fnd_file.put_line(fnd_file.log, 'Final query:');
788 --fnd_file.put_line(fnd_file.log,l_query);
789
790 /**************************************************
791 Get the xml.
792 **************************************************/
793 fnd_file.put_line(fnd_file.log,'************************************XML******************************************************');
794
795 queryCtx := DBMS_XMLQuery.newContext(l_query);
796 DBMS_XMLQuery.setRowsetTag(queryCtx, 'Extracted_Records');
797 DBMS_XMLQuery.setRowTag(queryCtx, 'Row');
798
799 l_xmlResult := DBMS_XMLQuery.getXML(queryCtx);
800 DBMS_XMLQuery.closeContext(queryCtx);
801
802 l_clob_size := dbms_lob.getlength(l_xmlResult);
803 l_offset := 1;
804 l_chunk_size := 3000;
805
806 WHILE (l_clob_size > 0) LOOP
807
808 l_chunk := dbms_lob.substr (l_xmlResult, l_chunk_size, l_offset);
809 --fnd_file.put_line(fnd_file.log,l_chunk);
810 fnd_file.put(
811 which => fnd_file.output,
812 buff => l_chunk);
813 l_clob_size := l_clob_size - l_chunk_size;
814 l_offset := l_offset + l_chunk_size;
815
816 END LOOP;
817
818 IF p_mode = 'ONLINE' THEN
819 x_xml_result := l_xmlResult;
820 END IF;
821
822
823 fem_engines_pkg.tech_message (p_severity => g_log_level_1,
824 p_module => g_block||'.Generate_Cust_XML()',
825 p_msg_text => 'END');
826 dbms_lob.freetemporary(selectClause);
827 dbms_lob.freetemporary(l_query);
828
829
830 EXCEPTION
831 WHEN OTHERS THEN
832 fnd_file.put_line(fnd_file.log,sqlerrm);
833 fem_engines_pkg.tech_message (p_severity => g_log_level_1,
834 p_module => g_block||'.Generate_Cust_XML()',
835 p_msg_text => 'EXCEPTION BLOCK:'||sqlerrm);
836
837
838 END Generate_Cust_XML;
839
840
841
842
843
844
845
846
847
848
849
850
851
852 /********************************
853 getConditionPredicate
854
855 ********************************/
856
857
858
859 PROCEDURE getConditionPredicate(
860 p_condObjId IN NUMBER,
861 p_tableName IN VARCHAR2,
862 p_whereClause OUT NOCOPY CLOB)
863 IS
864
865 --G_DEFAULT_EFFECTIVE_DATE_NULL constant date := FND_DATE.Canonical_To_Date('2500/01/01');
866 messageData VARCHAR2(32767);
867 effDateStr DATE;
868 returnStatus VARCHAR2(1000);
869 messageCount NUMBER;
870 effDateString VARCHAR2(32);
871 l_date_string VARCHAR2(32767);
872 l_date_value varchar2(30);
873
874 BEGIN
875 effDateString := 'FEM_EFFECTIVE_DATE';
876
877 l_date_string := FND_PROFILE.value(effDateString);
878
879 if (l_date_string is not null) then
880 --l_date_value := FND_DATE.Canonical_To_Date(l_date_string);
881 -- Remove the time component.
882 --l_date_value := FND_DATE.date_to_canonical(trunc(l_date_value));
883 l_date_value := l_date_string;
884
885 end if;
886
887 FEM_CONDITIONS_API.GENERATE_CONDITION_PREDICATE(
888 p_condition_obj_id => p_condObjId,
889 p_rule_effective_date => l_date_value,
890 p_input_fact_table_name => p_tableName,
891 p_table_alias => NULL,
892 p_display_predicate => 'N',
893 p_return_predicate_type => 'BOTH',
894 p_logging_turned_on =>'Y' ,
895 x_return_status => returnStatus,
896 x_msg_count => messageCount,
897 x_msg_data => messageData,
898 x_predicate_string => p_whereClause);
899
900 END getConditionPredicate;
901
902 END FEM_TABLE_PUBLISH_PKG;