[Home] [Help]
PACKAGE BODY: APPS.FEM_COL_TMPLT_DEFN_API_PUB
Source
1 PACKAGE BODY Fem_Col_Tmplt_Defn_Api_Pub AS
2 /* $Header: FEMCOLTMPLTB.pls 120.10 2006/06/01 17:38:28 ssthiaga noship $ */
3
4 --------------------------------------------
5 -- get_alias returns an alias for a table --
6 -- Ex: Input: FEM_COMMERCIAL_LOANS
7 -- Output: FCL
8 --------------------------------------------
9
10
11 FUNCTION get_alias(p_tab_name IN VARCHAR2,
12 p_alias IN VARCHAR2 )
13
14 RETURN VARCHAR2 IS
15
16 l_alias VARCHAR2(10);
17 l_tab_name VARCHAR2(30);
18
19 BEGIN
20
21 fem_engines_pkg.tech_message (p_severity => g_log_level_1
22 ,p_module => g_block||'.get_alias(FUNCTION)'
23 ,p_msg_text => 'BEGIN..for table ' || p_tab_name);
24
25 l_alias := p_alias || SUBSTR(p_tab_name,1,1);
26
27 IF INSTR(p_tab_name,'_') > 0 THEN
28 l_tab_name := SUBSTR(p_tab_name,INSTR(p_tab_name,'_')+1,LENGTH(p_tab_name));
29 l_alias := get_alias(l_tab_name,l_alias);
30 END IF;
31
32 fem_engines_pkg.tech_message (p_severity => g_log_level_1
33 ,p_module => g_block||'.get_alias(FUNCTION)'
34 ,p_msg_text => 'END..alias ' || p_alias);
35
36 RETURN l_alias;
37
38 END get_alias;
39
40 ------------------------------------------------
41 -- end get_alias returns an alias for a table --
42 ------------------------------------------------
43
44 --------------------------------------------
45 -- get_alias returns number of times a table
46 -- has been repeated in FROM clause
47 -- concatenated with table alias
48 -- Output: FCL2
49 --------------------------------------------
50
51 PROCEDURE get_alias(p_attr_detail_rec IN OUT NOCOPY attr_list_arr,
52 p_tab_name IN VARCHAR2,
53 p_alias OUT NOCOPY VARCHAR2)
54 IS
55
56 i NUMBER;
57 l_count NUMBER;
58 l_where NUMBER;
59
60 BEGIN
61
62 fem_engines_pkg.tech_message (p_severity => g_log_level_2
63 ,p_module => g_block||'.get_alias(PROCEDURE)'
64 ,p_msg_text => 'BEGIN..for table ' || p_tab_name);
65
66 i := 0;
67 l_count := 1;
68 l_where := 1;
69
70 IF p_attr_detail_rec.EXISTS(1) THEN
71 FOR i IN p_attr_detail_rec.FIRST .. p_attr_detail_rec.LAST LOOP
72 IF p_attr_detail_rec(i).attribute_tab_name = p_tab_name THEN
73 l_count := p_attr_detail_rec(i).attribute_tab_count + 1;
74 l_where := i;
75 EXIT;
76 ELSE
77 l_where := l_where + 1;
78 END IF;
79 END LOOP;
80 END IF;
81
82 p_attr_detail_rec(l_where).attribute_tab_name := p_tab_name;
83
84 p_attr_detail_rec(l_where).attribute_tab_count := l_count;
85
86 p_alias := get_alias(p_attr_detail_rec(l_where).attribute_tab_name,'') || TO_CHAR(l_count);
87
88 fem_engines_pkg.tech_message (p_severity => g_log_level_2
89 ,p_module => g_block||'.get_alias(PROCEDURE)'
90 ,p_msg_text => 'END..alias ' || p_alias);
91
92 END get_alias;
93
94 ---------------------------------------------------
95 -- end get_alias --
96 ---------------------------------------------------
97
98 --------------------------------------------
99 -- This function is used to return the
100 -- db data type, perform the type cast
101 -- if required and return the value
102 --------------------------------------------
103
104 FUNCTION get_param_value(p_column_name IN VARCHAR2,
105 p_param_val IN VARCHAR2 )
106
107 RETURN VARCHAR2
108 IS
109 CURSOR get_db_data_type_cur IS
110 SELECT DECODE(data_type,'NUMBER','TO_NUMBER','')
111 FROM fem_column_requiremnt_vl
112 WHERE column_name = p_column_name;
113
114 l_ret_type VARCHAR2(100);
115
116 BEGIN
117
118 fem_engines_pkg.tech_message (p_severity => g_log_level_2
119 ,p_module => g_block||'.get_param_value'
120 ,p_msg_text => 'BEGIN');
121
122 OPEN get_db_data_type_cur;
123 FETCH get_db_data_type_cur INTO l_ret_type;
124 CLOSE get_db_data_type_cur;
125
126 IF l_ret_type = 'TO_NUMBER' THEN
127 l_ret_type := l_ret_type || '(' || '''' || p_param_val || '''' || ')';
128 ELSE
129 l_ret_type := '''' || p_param_val || '''';
130 END IF;
131
132 fem_engines_pkg.tech_message (p_severity => g_log_level_2
133 ,p_module => g_block||'.get_param_value'
134 ,p_msg_text => 'END');
135
136 RETURN l_ret_type;
137
138 END get_param_value;
139
140 --------------------------------------------
141 -- end get_param_value
142 --------------------------------------------
143
144 --------------------------------------------------------------------------
145 -- This is the main procedure that actually builds the SELECT, FROM, WHERE
146 -- clause.
147 --
148 -- This is the main procedure that actually builds the SELECT, FROM, WHERE
149 -- clause.
150 --
151 -- When the column template is defined, the user can map the source
152 -- to target column in 3 ways:
153 -- 1. Attribute Lookup (Dimension)
154 -- 2. Source Column
155 -- 3. Constant (Number/Varchar2/Date)
156 -- 4. Unassigned (this has no bearing when the engine builds the code,
157 -- all columns with this type are ignored)
158 --
159 -- There are also some special columns like
160 -- 1. Engine Processing Parameters - Columns who derive values at
161 -- runtime. (pass the parameter values{SRS} to target table)
162 -- 2. Parameter - Certain columns marked as parameter_flag = 'Y' need
163 -- constant values to be passed to fem_customer_profit.
164 -- Ex: record_count => number of records processed by the engine.
165 --
166 -- Aggregtion Methods apart from Average, Sum not supported at this point,
167 -- even though they are visible in the UI.
168 ----------------------------------------------------------------------------
169
170 PROCEDURE get_from_where_clause(p_api_version IN NUMBER,
171 p_init_msg_list IN VARCHAR2,
172 p_commit IN VARCHAR2,
173 p_encoded IN VARCHAR2,
174 p_object_def_id IN NUMBER,
175 p_load_sec_relns IN BOOLEAN,
176 p_dataset_grp_obj_def_id IN NUMBER,
177 p_cal_period_id IN NUMBER,
178 p_ledger_id IN NUMBER,
179 p_source_system_code IN NUMBER,
180 p_created_by_object_id IN NUMBER,
181 p_created_by_request_id IN NUMBER,
182 p_insert_list OUT NOCOPY LONG,
183 p_select_list OUT NOCOPY LONG,
184 p_from_clause OUT NOCOPY LONG,
185 p_where_clause OUT NOCOPY LONG,
186 x_msg_count OUT NOCOPY NUMBER,
187 x_msg_data OUT NOCOPY VARCHAR2,
188 x_return_status OUT NOCOPY VARCHAR2)
189 IS
190
191 CURSOR get_table_list_cur IS
192 SELECT c.source_table_name,
193 c.source_column_name,
194 c.target_table_name,
195 c.target_column_name,
196 c.dimension_id,
197 c.attribute_id,
198 c.attribute_version_id,
199 c.aggregation_method,
200 c.constant_numeric_value,
201 c.constant_alphanumeric_value,
202 c.constant_date_value,
203 c.data_population_method_code,
204 DECODE( c.aggregation_method,
205 'AVERAGE', 'AVG',
206 'SUM', 'SUM',
207 'AVERAGE_BY_DAYS_WEIGHTED', 'AVG',
208 'BEGINNING','MIN',
209 'LAST','MAX',
210 NULL) agg_method,
211 c.eng_proc_param,
212 c.parameter_flag
213 FROM fem_col_population_tmplt_b c
214 WHERE c.col_pop_templt_obj_def_id = p_object_def_id
215 AND ( c.data_population_method_code <> 'UNASSIGNED' OR
216 (c.data_population_method_code = 'UNASSIGNED' AND parameter_flag = 'Y')
217 )
218 ORDER BY data_population_method_code;
219
220 i NUMBER;
221
222 found BOOLEAN;
223 param_exists BOOLEAN;
224 agg_present BOOLEAN;
225
226 l_alias_attr VARCHAR2(10);
227 l_alias_mem VARCHAR2(10);
228 l_attr_tab_name VARCHAR2(30);
229 l_source_col_name VARCHAR2(30);
230 l_data_pop_method VARCHAR2(30);
231 l_agg_method VARCHAR2(30);
232 l_member_col VARCHAR2(30);
233 l_attr_val_col VARCHAR2(30);
234 l_member_tab_name VARCHAR2(30);
235 l_param_val VARCHAR2(100);
236 l_fem_data_type_code VARCHAR2(30);
237 l_op_dataset_code NUMBER;
238 l_acct_ownership_id NUMBER;
239 l_disp_code VARCHAR2(10);
240 l_convert_condition VARCHAR2(1000);
241
242 l_select_col VARCHAR2(1000);
243
244 attr_list_tbl attr_list_arr;
245
246 l_api_version NUMBER;
247 l_init_msg_list VARCHAR2(1);
248 l_commit VARCHAR2(1);
249 l_encoded VARCHAR2(1);
253 eng_proc_where_generated BOOLEAN;
250
251 l_where VARCHAR2(1000);
252
254
255 e_ds_wclause_error EXCEPTION;
256
257 CURSOR fetch_dim_attr_cur(p_attr_id IN NUMBER) IS
258 SELECT dimension_id,
259 attribute_dimension_id,
260 attribute_value_column_name,
261 attribute_data_type_code
262 FROM fem_dim_attributes_vl
263 WHERE attribute_id = p_attr_id;
264
265 fetch_dim_attr_rec fetch_dim_attr_cur%ROWTYPE;
266
267 CURSOR fetch_dim_member_cur(p_dim_id IN NUMBER) IS
268 SELECT member_col,
269 member_data_type_code,
270 member_vl_object_name,
271 attribute_table_name
272 FROM fem_xdim_dimensions
273 WHERE dimension_id = p_dim_id;
274
275 fetch_dim_member_rec fetch_dim_member_cur%ROWTYPE;
276
277 CURSOR fetch_tab_class_code_cur IS
278 SELECT usage_code
279 FROM fem_table_class_assignmt a,
280 fem_table_class_usages b
281 WHERE a.table_classification_code = b.table_classification_code
282 AND a.table_name = g_src_tab_name;
283
284 CURSOR get_table_id_cur(c_src_tab_name IN VARCHAR2)IS
285 SELECT table_id
286 FROM fem_tables_b
287 WHERE table_name = c_src_tab_name;
288
289 CURSOR get_fem_data_type_code(c_source_col_name IN VARCHAR2) IS
290 SELECT fem_data_type_code
291 FROM fem_tab_columns_vl
292 WHERE table_name = g_src_tab_name
293 AND column_name = c_source_col_name;
294
295 PROCEDURE initialize
296 IS
297 l_attribute_varchar_label VARCHAR2(30);
298 l_member_id VARCHAR2(150);
299 l_attribute_id NUMBER;
300 l_attr_version_id NUMBER;
301
302 l_get_dim_attr_error EXCEPTION;
303 l_no_exch_prof_val EXCEPTION;
304 l_get_exch_rate_error EXCEPTION;
305 l_get_ledger_curr_error EXCEPTION;
306 l_op_dataset_code_error EXCEPTION;
307 l_acct_ownership_error EXCEPTION;
308
309 BEGIN
310 fem_engines_pkg.tech_message (p_severity => g_log_level_2
311 ,p_module => g_block||'.get_from_where_clause.initialize'
315 g_tgt_alias := get_alias(g_tgt_tab_name,'');
312 ,p_msg_text => 'BEGIN');
313
314 g_src_alias := get_alias(g_src_tab_name,'');
316 g_sec_alias := get_alias('FEM_SECONDARY_OWNERS','');
317
318 -- For Account Consolidation we would have to set the
319 -- table_id to be passed back to engine;
320 -- Table_id is required only if the secondary
321 -- relationships have to be loaded.
322
323 -- The above comments are historical!!!!
324
325 -- Now that fem_customer_profit has table_id as a not null
326 -- column; opening the cursor to the entire code
327
328 OPEN get_table_id_cur(g_src_tab_name);
329 FETCH get_table_id_cur INTO g_table_id;
330 CLOSE get_table_id_cur;
331
332 fem_engines_pkg.tech_message (p_severity => g_log_level_2
333 ,p_module => g_block||'.get_from_where_clause.initialize'
334 ,p_msg_text => 'Retieved alias and table_id');
335
336 -----------------------------------------------------------
337 -- Get the currency conversion type FROM profile options --
338 -----------------------------------------------------------
339
340 g_curr_conv_type := fnd_profile.value_specific (
341 'FEM_CURRENCY_CONVERSION_TYPE'
342 ,fnd_global.user_id
343 ,fnd_global.resp_id
344 ,fnd_global.prog_appl_id);
345
346 IF (g_curr_conv_type IS NULL) THEN
347 fem_engines_pkg.user_message (
348 p_app_name => 'FEM'
349 ,p_msg_name => G_INV_EXCHG_RATE_TYPE_ERR);
350 RAISE l_no_exch_prof_val;
351 END IF;
352
353 fem_engines_pkg.tech_message (
354 p_severity => g_log_level_2
355 ,p_module => g_block||'.get_from_where_clause.initialize'
356 ,p_msg_text => 'Retieved currency conversion type..type '
357 || g_curr_conv_type);
358
359 -----------------------------
360 -- Set the exchange rate date
361 -----------------------------
362
363 FOR dim_rec IN (SELECT dimension_id,
364 dimension_varchar_label
365 FROM fem_xdim_dimensions_vl
366 WHERE dimension_varchar_label IN ('CAL_PERIOD','LEDGER'))
367 LOOP
368
369 IF dim_rec.dimension_varchar_label = 'CAL_PERIOD' THEN
370 l_attribute_varchar_label := 'CAL_PERIOD_END_DATE';
371 l_member_id := p_cal_period_id;
372 ELSE
373 l_attribute_varchar_label := 'LEDGER_FUNCTIONAL_CRNCY_CODE';
374 l_member_id := p_ledger_id;
375 END IF;
376
377 BEGIN
378 SELECT att.attribute_id
379 ,ver.version_id
380 INTO l_attribute_id
381 ,l_attr_version_id
382 FROM fem_dim_attributes_b att
383 ,fem_dim_attr_versions_b ver
384 WHERE att.dimension_id = dim_rec.dimension_id
385 AND att.attribute_varchar_label = l_attribute_varchar_label
386 AND ver.attribute_id = att.attribute_id
387 AND ver.default_version_flag = 'Y';
388
389 EXCEPTION
390 WHEN OTHERS THEN
391 fem_engines_pkg.user_message(
392 p_app_name => 'FEM'
396 ,p_token2 => 'DIMENSION_VARCHAR_LABEL'
393 ,p_msg_name => G_NO_ATTR_VER_ERR
394 ,p_token1 => 'DIMENSION_ID'
395 ,p_value1 => dim_rec.dimension_id
397 ,p_value2 => dim_rec.dimension_varchar_label
398 ,p_token3 => 'ATTRIBUTE_VARCHAR_LABEL'
399 ,p_value3 => l_attribute_varchar_label);
400 RAISE l_get_dim_attr_error;
401 END;
402
403 fem_engines_pkg.tech_message (
404 p_severity => g_log_level_2
405 ,p_module => g_block||'.get_from_where_clause.initialize'
406 ,p_msg_text => 'Retrieved attribute and version for.. '
407 || l_attribute_varchar_label);
408
409 IF dim_rec.dimension_varchar_label = 'CAL_PERIOD' THEN
410
411 BEGIN
412 SELECT date_assign_value
413 INTO g_exch_rate_date
414 FROM Fem_Cal_Periods_Attr
415 WHERE attribute_id = l_attribute_id
416 AND version_id = l_attr_version_id
417 AND cal_period_id = l_member_id;
418
419 EXCEPTION
420 WHEN OTHERS THEN
421 fem_engines_pkg.user_message(
422 p_app_name => 'FEM'
423 ,p_msg_name => G_NO_EXCHG_RATE_ERR);
424
425 RAISE l_get_exch_rate_error;
426 END;
427
428 ELSE
429
430 BEGIN
431 SELECT dim_attribute_varchar_member
432 INTO g_func_curr_code
433 FROM Fem_Ledgers_Attr
434 WHERE attribute_id = l_attribute_id
435 AND version_id = l_attr_version_id
436 AND ledger_id = l_member_id;
437
438 EXCEPTION
439 WHEN OTHERS THEN
440 fem_engines_pkg.user_message (
441 p_app_name => 'FEM'
442 ,p_msg_name => G_NO_FUNCTIONAL_CURR_ERR);
443
444 RAISE l_get_ledger_curr_error;
445 END;
446
447 END IF;
448
449 END LOOP;
450
451 fem_engines_pkg.tech_message (
452 p_severity => g_log_level_2
453 ,p_module => g_block||'.get_from_where_clause.initialize'
454 ,p_msg_text => 'Retrieved currency exchange date '
458 ------------------------------
455 || g_exch_rate_date|| ' and functional currency '
456 || g_func_curr_code);
457
459 -- Get the output dataset code
460 ------------------------------
461
462 BEGIN
463 SELECT output_dataset_code
464 INTO l_op_dataset_code
465 FROM fem_ds_input_output_defs
466 WHERE dataset_io_obj_def_id = p_dataset_grp_obj_def_id;
467
468 EXCEPTION
469 WHEN OTHERS THEN
470 fem_engines_pkg.user_message (
471 p_app_name => 'FEM'
472 ,p_msg_name => G_INVALID_DATASET_GRP_ERR
473 ,p_token1 => 'DATASET_IO_OBJ_DEF_ID'
474 ,p_value1 => p_dataset_grp_obj_def_id);
475 RAISE l_op_dataset_code_error;
476
477 END;
478
479 fem_engines_pkg.tech_message (
480 p_severity => g_log_level_2
481 ,p_module => g_block||'.get_from_where_clause.initialize'
482 ,p_msg_text => 'Retrieved dataset code ');
483
484 ---------------------------------
485 -- Get the account ownership code
486 ---------------------------------
487
488 IF NOT agg_present THEN
489 IF p_load_sec_relns THEN
490 l_disp_code := 'SECONDARY';
491 ELSE
492 l_disp_code := 'PRIMARY';
493 END IF;
494 ELSE
495 l_disp_code := 'Default';
496 END IF;
497
498 BEGIN
499
500 SELECT acct_ownership_id
501 INTO l_acct_ownership_id
502 FROM fem_acct_ownshp_b
503 WHERE acct_ownership_display_code = l_disp_code;
504
505 EXCEPTION
506 WHEN OTHERS THEN
507 fem_engines_pkg.tech_message (
508 p_severity => g_log_level_6
509 ,p_module => g_block||'.get_from_where_clause.initialize'
510 ,p_msg_text => 'Error fetching account ownership
511 display code for ' || l_disp_code);
512
513 fem_engines_pkg.user_message (
514 p_app_name => 'FEM'
515 ,p_msg_name => G_INVALID_ACCT_OWNER_ID_ERR
516 ,p_token1 => 'ACCOUNT_OWNERSHIP_DISPLAY_CODE'
517 ,p_value1 => l_disp_code);
518 RAISE l_acct_ownership_error;
519
520 END;
521
522 l_disp_code := NULL;
523
524 fem_engines_pkg.tech_message (
525 p_severity => g_log_level_2
526 ,p_module => g_block||'.get_from_where_clause.initialize'
527 ,p_msg_text => 'Retrieved acct_ownshp_id '
528 || TO_CHAR(l_acct_ownership_id) );
529
530 fem_engines_pkg.tech_message (
531 p_severity => g_log_level_2
532 ,p_module => g_block||'.get_from_where_clause.initialize'
533 ,p_msg_text => 'END');
534
535 EXCEPTION
536
537 WHEN l_no_exch_prof_val THEN
538 x_return_status := c_error;
539 fem_engines_pkg.tech_message (
540 p_severity => g_log_level_6
541 ,p_module => g_block||'.initialize'
542 ,p_msg_text => 'Initialize Exception - no exch rate type');
543
544 WHEN l_get_dim_attr_error THEN
545 x_return_status := c_error;
546 fem_engines_pkg.tech_message (
547 p_severity => g_log_level_6
548 ,p_module => g_block||'.initialize'
549 ,p_msg_text => 'Initialize Exception - no attributes');
550
551 WHEN l_get_exch_rate_error THEN
552 x_return_status := c_error;
553 fem_engines_pkg.tech_message (
554 p_severity => g_log_level_6
555 ,p_module => g_block||'.initialize'
556 ,p_msg_text => 'Initialize Exception: getting the exch rate');
557
558 WHEN l_get_ledger_curr_error THEN
559 x_return_status := c_error;
560 fem_engines_pkg.tech_message (
561 p_severity => g_log_level_6
562 ,p_module => g_block||'.initialize'
563 ,p_msg_text => 'Initialize Exception: getting the func curr');
564
565 WHEN l_op_dataset_code_error THEN
566 x_return_status := c_error;
567 fem_engines_pkg.tech_message (
571
568 p_severity => g_log_level_6
569 ,p_module => g_block||'.initialize'
570 ,p_msg_text => 'Initialize Exception: Invalid dataset group');
572 WHEN l_acct_ownership_error THEN
573 x_return_status := c_error;
574 fem_engines_pkg.tech_message (
575 p_severity => g_log_level_6
576 ,p_module => g_block||'.initialize'
577 ,p_msg_text => 'Initialize Exception:Account ownership code');
578
579 END initialize;
580
581 BEGIN
582
583 fem_engines_pkg.tech_message (
584 p_severity => g_log_level_2
585 ,p_module => g_block||'.get_from_where_clause'
586 ,p_msg_text => 'BEGIN');
587
588 l_api_version := NVL(p_api_version, c_api_version);
589 l_init_msg_list := NVL(p_init_msg_list, c_false);
590 l_commit := NVL(p_commit, c_false);
591 l_encoded := NVL(p_encoded, c_true);
592
593 i := 1;
594 found := FALSE;
595 param_exists := FALSE;
596 agg_present := TRUE;
597 eng_proc_where_generated := FALSE;
598
599 l_param_val := NULL;
600
601 x_return_status := c_success;
602
603 -- If agg_present implies profit aggregation
604 agg_present := is_aggregation_present(p_object_def_id);
605
606 FOR get_table_list_rec IN get_table_list_cur LOOP
607
608 l_source_col_name := get_table_list_rec.source_column_name;
609 l_data_pop_method := get_table_list_rec.data_population_method_code;
610
611 IF l_data_pop_method LIKE 'CONSTANT%' THEN
612 l_source_col_name := get_table_list_rec.target_column_name;
616 -- Make call to initialize procedure to set the
613 END IF;
614
615 IF g_src_tab_name IS NULL THEN
617 -- global variables
618
619 -- The currency exch rate,type need to be evaluated
620 -- as well
621
622 fem_engines_pkg.tech_message (
623 p_severity => g_log_level_2
624 ,p_module => g_block||'.get_from_where_clause'
625 ,p_msg_text => 'Initializing global variables');
626
627 g_src_tab_name := get_table_list_rec.source_table_name;
628 g_tgt_tab_name := get_table_list_rec.target_table_name;
629 initialize;
630
631 fem_engines_pkg.tech_message (
632 p_severity => g_log_level_2
633 ,p_module => g_block||'.get_from_where_clause'
634 ,p_msg_text => 'After initializing global variables');
635
636 END IF;
637
638 IF agg_present THEN
639 l_agg_method := NVL(get_table_list_rec.agg_method,'MIN');
640 END IF;
641
642 fem_engines_pkg.tech_message (
643 p_severity => g_log_level_1
644 ,p_module => g_block||'.get_from_where_clause'
645 ,p_msg_text => 'Return status after initialize = '||x_return_status);
646
647 IF x_return_status = c_success THEN
648
649 IF get_table_list_rec.parameter_flag = 'N' THEN
650
651 fem_engines_pkg.tech_message(
652 p_severity => g_log_level_1
653 ,p_module => g_block||'.get_from_where_clause'
654 ,p_msg_text => 'Inside While, Parameter = N, Population Method = '
655 ||l_data_pop_method||' for column = '
656 ||l_source_col_name );
657
658 IF l_data_pop_method = 'DIMENSION_LOOKUP' THEN
659
660 l_param_val := NULL;
661
662 OPEN fetch_dim_attr_cur(get_table_list_rec.attribute_id);
663 FETCH fetch_dim_attr_cur INTO fetch_dim_attr_rec;
664 CLOSE fetch_dim_attr_cur;
665
666 l_attr_val_col := fetch_dim_attr_rec.attribute_value_column_name;
667
668 OPEN fetch_dim_member_cur(fetch_dim_attr_rec.dimension_id);
669 FETCH fetch_dim_member_cur INTO fetch_dim_member_rec;
670 CLOSE fetch_dim_member_cur;
671
672 l_attr_tab_name := fetch_dim_member_rec.attribute_table_name;
673
674 get_alias(attr_list_tbl,l_attr_tab_name,l_alias_attr);
675
676 p_from_clause := p_from_clause || ',' || l_attr_tab_name
677 || ' ' || l_alias_attr;
678
679 p_where_clause := p_where_clause || ' AND '
680 || g_src_alias ||'.'|| l_source_col_name
681 || ' = ' || l_alias_attr || '.'
682 || l_source_col_name || ' AND '
683 || l_alias_attr || '.attribute_id = '
684 || '' || get_table_list_rec.attribute_id
685 || '' || ' AND ' || l_alias_attr
686 || '.version_id = ' || ''
687 || get_table_list_rec.attribute_version_id
688 || '';
689
690 IF fetch_dim_attr_rec.attribute_data_type_code = 'DIMENSION' THEN
691 -- Get the dimension_attribute_numeric_member/varchar_member
692
693 OPEN fetch_dim_member_cur
694 (fetch_dim_attr_rec.attribute_dimension_id);
695 FETCH fetch_dim_member_cur
696 INTO fetch_dim_member_rec;
697 CLOSE fetch_dim_member_cur;
698
699 l_member_tab_name := fetch_dim_member_rec.member_vl_object_name;
700 l_member_col := fetch_dim_member_rec.member_col;
701
702 -- Need to build SQL statement dynamically for querying
703 -- on the dimension attribute table. Ex: Fem_Products_Attr
704 -- is stored in attribute_table_name,
705 -- Fem_Products_VL is stored in member_vl_object_name.
706
707 get_alias(attr_list_tbl,l_member_tab_name,l_alias_mem);
708 l_select_col := l_alias_mem || '.' || l_member_col;
709 p_from_clause := p_from_clause || ',' || l_member_tab_name || ' ' || l_alias_mem;
710 p_where_clause := p_where_clause || ' AND ' || l_alias_attr || '.' || l_attr_val_col || ' = '
711 || l_alias_mem || '.' || l_member_col;
712
713 ELSE
717 IF agg_present THEN
714 l_select_col := l_alias_attr || '.' || l_attr_val_col;
715 END IF;
716
718 l_select_col := l_agg_method || '(' || l_select_col || ')';
719 END IF;
720
721 ELSIF ((l_data_pop_method = 'DEFINED_COLUMN' AND l_source_col_name IS NOT NULL) OR
722 (l_data_pop_method LIKE 'CONSTANT%' AND
723 get_table_list_rec.eng_proc_param IS NOT NULL)) THEN
724
725 -- Agg_present = TRUE for Profit aggregation engine
726 IF get_table_list_rec.eng_proc_param IS NOT NULL THEN
727
728 l_param_val := 'Y';
729
730 CASE l_source_col_name
731
732 WHEN 'DATASET_CODE' THEN
733 l_select_col := get_param_value(l_source_col_name, l_op_dataset_code);
734 WHEN 'CAL_PERIOD_ID' THEN
735 l_select_col := get_param_value(l_source_col_name, p_cal_period_id);
736 WHEN 'LEDGER_ID' THEN
737 l_select_col := get_param_value(l_source_col_name, p_ledger_id);
738 ELSE
739 l_select_col := g_src_alias || '.' || l_source_col_name;
740
741 END CASE;
742
743 -- Things to do:
744 -- Replace the fem_ds_where_clause_generator API with
745 -- fem_assembler_predicate_api.generate_assembler_predicate.
746 --
747 -- This would return the complete WHERE clause comprising of
748 -- dataset_code, cal_period_id, ledger_id.
749 --
750 -- At the moment ledger_id inclusion in where clause is being
751 -- done manually.
752
756 ,p_module => g_block||'.get_from_where_clause'
753 IF NOT eng_proc_where_generated THEN
754
755 fem_engines_pkg.tech_message (p_severity => g_log_level_1
757 ,p_msg_text => 'Before generating the where ');
758
759 fem_ds_where_clause_generator.fem_gen_ds_wclause_pvt(p_api_version => l_api_version
760 ,p_init_msg_list => FND_API.G_TRUE
761 ,p_encoded => FND_API.G_TRUE
762 ,x_return_status => x_return_status
763 ,x_msg_count => x_msg_count
764 ,x_msg_data => x_msg_data
765 ,p_ds_io_def_id => p_dataset_grp_obj_def_id
766 ,p_output_period_id => p_cal_period_id
767 ,p_table_alias => g_src_alias
768 ,p_table_name => g_src_tab_name
769 ,p_ledger_id => p_ledger_id
770 ,p_where_clause => l_where);
771
772 eng_proc_where_generated := TRUE;
773
774 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
775
776 eng_proc_where_generated := FALSE;
777
778 FEM_ENGINES_PKG.User_Message (
779 p_app_name => 'FEM'
780 ,p_msg_name => G_DS_WHERE_PREDICATE_ERR);
781
782 IF (l_where IS NULL) THEN
783 FEM_ENGINES_PKG.User_Message (
784 p_app_name => 'FEM'
785 ,p_msg_name => G_DS_WHERE_PREDICATE_ERR);
786 END IF;
787 RAISE e_ds_wclause_error;
788
789 END IF;
790
791 fem_engines_pkg.tech_message (p_severity => g_log_level_1
792 ,p_module => g_block||'.get_from_where_clause'
793 ,p_msg_text => 'DS WHERE clause generated, where = ' || l_where);
794
795 p_where_clause := p_where_clause || ' AND ' || l_where;
796
797 END IF; -- eng_proc_where_generated
798
799 IF l_source_col_name = 'LEDGER_ID' THEN
800 p_where_clause := p_where_clause || ' AND ' || g_src_alias || '.' || l_source_col_name;
801 p_where_clause := p_where_clause || ' = ' || p_ledger_id;
802 END IF;
803
804 ELSE -- eng_proc_param
805
806 l_fem_data_type_code := NULL;
807
808 OPEN get_fem_data_type_code(l_source_col_name);
809 FETCH get_fem_data_type_code INTO l_fem_data_type_code;
810 CLOSE get_fem_data_type_code;
811
812 -- For all columns that are of data_type = BALANCE
816 IF l_fem_data_type_code = 'BALANCE' THEN
813 -- we need to convert the values into functional currency
814 -- in case they are not in func. curr
815
817
818 l_convert_condition := 'gl_currency_api.get_rate(' || '''' || g_func_curr_code || '''' || ',' ||
819 g_src_alias || '.currency_code' || ',' || 'fnd_date.canonical_to_date'||
820 '(fnd_date.date_to_canonical(' || '''' || g_exch_rate_date || ''''
821 || '))' || ',' || '''' || g_curr_conv_type || '''' || ')*' ||
822 g_src_alias||'.'||l_source_col_name;
823
824 l_select_col := 'DECODE(' || g_src_alias || '.currency_code,' || '''' || g_func_curr_code
825 || '''' || ',' || g_src_alias ||'.'|| l_source_col_name || ',' ||
826 l_convert_condition || ')';
827
828 ELSE
829 l_select_col := g_src_alias || '.' || l_source_col_name;
830 END IF;
831
832 END IF; -- eng_proc_param
833
834 IF agg_present AND l_param_val IS NULL THEN
835 l_select_col := l_agg_method || '(' || l_select_col || ')';
836 END IF;
837
838 -- l_param_val is set to NULL after cal_period_id OR dataset_code OR ledger_id
839 -- columns have been processed.
840
841 l_param_val := NULL;
842
843 ELSE
844 l_select_col := get_table_list_rec.constant_numeric_value ||
845 get_table_list_rec.constant_alphanumeric_value ||
846 get_table_list_rec.constant_date_value ;
847
848 IF get_table_list_rec.constant_numeric_value IS NULL THEN
852 END IF; -- dimension_lookup
849 l_select_col := '''' || l_select_col || '''';
850 END IF;
851
853
854 ELSE -- parameter_flag
855
856 fem_engines_pkg.tech_message (p_severity => g_log_level_1
857 ,p_module => g_block||'.get_from_where_clause'
858 ,p_msg_text => 'Step1: Parameter = Y, for column = ' || l_source_col_name );
859
860 -- Implies value derived at runtime
861 CASE get_table_list_rec.target_column_name --l_source_col_name
862
863 WHEN 'ACCT_OWNERSHIP_ID' THEN
864 l_select_col := 'TO_NUMBER(' || '''' || l_acct_ownership_id || '''' || ')';
865 WHEN 'CREATED_BY_OBJECT_ID' THEN
866 l_select_col := 'TO_NUMBER(' || '''' || p_created_by_object_id || '''' || ')';
867 WHEN 'CREATED_BY_REQUEST_ID' THEN
868 l_select_col := 'TO_NUMBER(' || '''' || p_created_by_request_id || '''' || ')';
869
870 -- Member table for DATA_AGGN_TYPE_CODE = FEM_DATA_AGGS_B
871 -- The table has no ID column but has these 3 values seeded
872 -- Default
873 -- ACCOUNT_RELATIONSHIP
874 -- CUSTOMER_AGGREGATION
875 -- Having an extra cursor to select this value is of no use, hence hard-coding the value
876 -- might revisit later for FEM.E
877
878 WHEN 'DATA_AGGREGATION_TYPE_CODE' THEN
879 IF NOT agg_present THEN
880 l_select_col := '''' || 'ACCOUNT_RELATIONSHIP' || '''';
881 ELSE
882 l_select_col := '''' || 'CUSTOMER_AGGREGATION' || '''';
883 END IF;
884 WHEN 'LAST_UPDATED_BY_OBJECT_ID' THEN
885 l_select_col := 'TO_NUMBER(' || '''' || p_created_by_object_id || '''' || ')';
886 WHEN 'LAST_UPDATED_BY_REQUEST_ID' THEN
887 l_select_col := 'TO_NUMBER(' || '''' || p_created_by_request_id || '''' || ')';
888 WHEN 'PRI_ACCOUNTS' THEN
889 IF NOT agg_present THEN
890 IF p_load_sec_relns THEN
891 l_select_col := 'TO_NUMBER(''0'')';
892 ELSE
893 l_select_col := 'TO_NUMBER(''1'')';
894 END IF;
895 ELSE
896 l_select_col := 'TO_NUMBER(''1'')';
897 END IF;
898 -- Record count info has to be changed after aggregation; this info. can be derived only
899 -- after the aggregation engine has completed execution.
900 -- The engine can make use of additional check of -987654321 while updating
901 -- the target table.
902 WHEN 'RECORD_COUNT' THEN
903 IF agg_present THEN
904 l_select_col := 'TO_NUMBER(''-987654321'')';
905 ELSE
906 l_select_col := 'TO_NUMBER(''1'')';
907 END IF;
908 WHEN 'SOURCE_SYSTEM_CODE' THEN l_select_col := 'TO_NUMBER(' || '''' || p_source_system_code || '''' || ')';
909 WHEN 'TABLE_ID' THEN l_select_col := 'TO_NUMBER(' || '''' || g_table_id || '''' || ')';
910
911 ELSE l_select_col := l_source_col_name;
912
913 END CASE;
914
915 END IF; -- parameter_flag
916
917 CASE l_source_col_name
918
919 -- Currency always to be stored in functional currency
920 -- Ignore the mapping in the template
921 WHEN 'CURRENCY_CODE' THEN
922 l_select_col := '''' || g_func_curr_code || '''';
923
927 p_where_clause := p_where_clause || ' = ' || p_source_system_code;*/
924 /* -- Source system code should also be used in filtering the records from Source table
925 WHEN 'SOURCE_SYSTEM_CODE' THEN
926 p_where_clause := p_where_clause || ' AND ' || g_src_alias || '.' || l_source_col_name;
928
929 -- For aggregation and while loading secondary relationship during consolidation we are
930 -- not sure of from which table the customer_id info. is being picked up from. Hence,
931 -- this change would be better of from the calling engines.
932 WHEN 'CUSTOMER_ID' THEN
933 IF l_data_pop_method = 'DEFINED_COLUMN' AND ( agg_present OR p_load_sec_relns ) THEN
934 l_select_col := '{{{CUSTOMER_ID}}}';
935 END IF;
936
937 ELSE NULL;
938
939 END CASE;
940
941 p_insert_list := p_insert_list || ', ' || get_table_list_rec.target_column_name;
942 p_select_list := p_select_list || ', ' || l_select_col;
943
944 END IF; -- x_return_status = c_success
945
946 EXIT WHEN x_return_status <> c_success;
947
948 END LOOP;
949
950 IF x_return_status = c_success THEN
951
952 p_insert_list := LTRIM(p_insert_list,',');
953 p_select_list := LTRIM(p_select_list,',');
954 p_from_clause := ' FROM ' || g_src_tab_name || ' ' || g_src_alias || p_from_clause;
955 p_where_clause := ' WHERE ' || LTRIM(p_where_clause,' AND ');
956
957 -- If the WHERE clause did not get generated correctly the string would contain only
958 -- ' WHERE '; in such case remove the WHERE clause
959 -- Would not encounter this situation at all, the check is redundant; will remove it
960 -- for FEM.E timeframe
961
962 IF p_where_clause = ' WHERE ' THEN
963 p_where_clause := '';
964 END IF;
965
966 -- If there has been error then init all the p_**** variables to NULL.
967 -- This might not be necessary
968 ELSE
969 p_insert_list := NULL;
970 p_select_list := NULL;
971 p_from_clause := NULL;
972 p_where_clause := NULL;
973 END IF;
974
975 fem_engines_pkg.tech_message (p_severity => g_log_level_1
976 ,p_module => g_block||'.get_from_where_clause'
977 ,p_msg_text => 'p_insert_list ' || p_insert_list);
978
979 fem_engines_pkg.tech_message (p_severity => g_log_level_1
980 ,p_module => g_block||'.get_from_where_clause'
981 ,p_msg_text => 'p_select_list ' || p_select_list);
982
983 fem_engines_pkg.tech_message (p_severity => g_log_level_1
984 ,p_module => g_block||'.get_from_where_clause'
985 ,p_msg_text => 'p_from_clause ' || p_from_clause);
986
987 fem_engines_pkg.tech_message (p_severity => g_log_level_1
988 ,p_module => g_block||'.get_from_where_clause'
989 ,p_msg_text => 'p_where_clause ' || p_where_clause);
990
991 EXCEPTION
992
993 WHEN e_ds_wclause_error THEN
994 x_return_status := c_error;
995
996 fem_engines_pkg.tech_message (p_severity => g_log_level_5
997 ,p_module => g_block||'.get_from_where_clause'
998 ,p_msg_text => 'Dataset Where Clause Generate Exception');
999 FEM_ENGINES_PKG.User_Message (
1000 p_app_name => 'FEM'
1001 ,p_msg_name => G_GENERATE_WHERE_CLAUSE_ERR);
1002
1003 fnd_msg_pub.count_and_get(p_encoded => p_encoded,
1004 p_count => x_msg_count,
1005 p_data => x_msg_data);
1006
1007 WHEN OTHERS THEN
1008 x_return_status := c_error;
1009
1013 FEM_ENGINES_PKG.User_Message (
1010 fem_engines_pkg.tech_message (p_severity => g_log_level_5
1011 ,p_module => g_block||'.get_from_where_clause'
1012 ,p_msg_text => 'get_from_where_clause: General_Exception');
1014 p_app_name => 'FEM'
1015 ,p_msg_name => G_GENERATE_WHERE_CLAUSE_ERR);
1016
1017 fnd_msg_pub.count_and_get(p_encoded => p_encoded,
1018 p_count => x_msg_count,
1019 p_data => x_msg_data);
1020
1021
1022 END get_from_where_clause;
1023
1024 -----------------------------
1025 -- end get_from_where_clause;
1026 -----------------------------
1027
1028 -----------------------------------------------------
1029 -- Returns whether aggregation is included as part of
1030 -- column template definition.
1031 -----------------------------------------------------
1032
1033 FUNCTION is_aggregation_present(p_object_def_id IN NUMBER)
1034 RETURN BOOLEAN IS
1035
1036 dummy VARCHAR2(1);
1037 retval BOOLEAN;
1038
1039 CURSOR chk_for_agg_cur IS
1040 SELECT 1
1041 FROM dual
1042 WHERE EXISTS
1043 (SELECT aggregation_method
1044 FROM fem_col_population_tmplt_vl
1045 WHERE col_pop_templt_obj_def_id = p_object_def_id
1046 AND aggregation_method <> 'NOAGG');
1047 BEGIN
1048
1049 fem_engines_pkg.tech_message (p_severity => g_log_level_2
1050 ,p_module => g_block||'.is_aggregation_present'
1051 ,p_msg_text => 'BEGIN');
1052
1053 retval := TRUE;
1054
1055 OPEN chk_for_agg_cur;
1056 FETCH chk_for_agg_cur INTO dummy;
1057
1058 IF chk_for_agg_cur%NOTFOUND THEN
1059 retval := FALSE;
1060 END IF;
1061
1062 CLOSE chk_for_agg_cur;
1063
1064 fem_engines_pkg.tech_message (p_severity => g_log_level_2
1065 ,p_module => g_block||'.is_aggregation_present'
1066 ,p_msg_text => 'END');
1067
1068 RETURN retval;
1069
1070 END is_aggregation_present;
1071
1072 -----------------------------
1073 -- end is_aggregation_present
1074 -----------------------------
1075
1076 -------------------------------------------------------------------
1077 -- Public API used to return the SELECT, insert, FROM, where
1078 -- , condition clauses
1079 --
1080 -- p_selection_param = 0 -> Gives the SQL with conditions predicate
1081 --
1082 -- p_condition_sel_param
1083 -- 'DIM' -> Returns the dimension component
1084 -- 'DATA' -> Returns the data component
1085 -- 'BOTH' -> Returns both the components
1086 -------------------------------------------------------------------
1087
1088 PROCEDURE generate_predicates(
1089 p_api_version IN NUMBER,
1090 p_init_msg_list IN VARCHAR2,
1091 p_commit IN VARCHAR2,
1092 p_encoded IN VARCHAR2,
1093 p_object_def_id IN NUMBER,
1094 p_selection_param IN NUMBER,
1095 p_effective_date IN VARCHAR2,
1096 p_condition_obj_id IN NUMBER,
1097 p_condition_sel_param IN VARCHAR2,
1098 p_load_sec_relns IN VARCHAR2,
1099 p_dataset_grp_obj_def_id IN NUMBER,
1100 p_cal_period_id IN NUMBER,
1101 p_ledger_id IN NUMBER,
1102 p_source_system_code IN NUMBER,
1103 p_created_by_object_id IN NUMBER,
1104 p_created_by_request_id IN NUMBER,
1105 p_insert_list OUT NOCOPY LONG,
1106 p_select_list OUT NOCOPY LONG,
1107 p_from_clause OUT NOCOPY LONG,
1108 p_where_clause OUT NOCOPY LONG,
1109 p_con_where_clause OUT NOCOPY LONG,
1110 x_msg_count OUT NOCOPY NUMBER,
1111 x_msg_data OUT NOCOPY VARCHAR2,
1112 x_return_status OUT NOCOPY VARCHAR2)
1113 IS
1114
1115 l_err_code NUMBER;
1119 l_insert_list LONG;
1116 l_err_msg VARCHAR2(100);
1117 l_api_name CONSTANT VARCHAR2(30) := 'generate_predicates';
1118 l_select_list LONG;
1120 l_from_clause LONG;
1121 l_where_clause LONG;
1122
1123 l_load_sec_relns BOOLEAN;
1124
1125 l_api_version NUMBER;
1126 l_init_msg_list VARCHAR2(1);
1127 l_commit VARCHAR2(1);
1128 l_encoded VARCHAR2(1);
1129
1130 e_cond_wclause_error EXCEPTION;
1131 BEGIN
1132
1133 fem_engines_pkg.tech_message (p_severity => g_log_level_2
1134 ,p_module => g_block||'.'||l_api_name
1135 ,p_msg_text => 'BEGIN');
1136
1137 l_api_version := NVL(p_api_version, c_api_version);
1138 l_init_msg_list := NVL(p_init_msg_list, c_false);
1139 l_commit := NVL(p_commit, c_false);
1140 l_encoded := NVL(p_encoded, c_true);
1141
1142 x_return_status := c_success;
1143
1144 --g_effective_date := fnd_profile.value('FEM_EFFECTIVE_DATE');
1145
1146 g_obj_def_id := p_object_def_id;
1147
1148 fem_engines_pkg.tech_message (
1149 p_severity => g_log_level_1
1150 ,p_module => g_block||'.'||l_api_name
1151 ,p_msg_text => 'Calling get_from_where_clause procedure');
1152
1153 IF NVL(p_load_sec_relns,'N') = 'N' THEN
1154 l_load_sec_relns := FALSE;
1155 ELSE
1156 l_load_sec_relns := TRUE;
1157 END IF;
1158
1159 get_from_where_clause(p_api_version,
1160 p_init_msg_list,
1161 p_commit,
1162 p_encoded,
1163 p_object_def_id,
1164 l_load_sec_relns,
1165 p_dataset_grp_obj_def_id,
1166 p_cal_period_id,
1167 p_ledger_id,
1168 p_source_system_code,
1169 p_created_by_object_id,
1170 p_created_by_request_id,
1171 l_insert_list,
1172 l_select_list,
1173 p_from_clause,
1174 p_where_clause,
1175 x_msg_count,
1176 x_msg_data,
1177 x_return_status);
1178
1179 IF (x_return_status = fnd_api.g_ret_sts_success) THEN
1180
1181 p_insert_list := 'INSERT INTO ' || g_tgt_tab_name || '(' || l_insert_list || ')';
1182
1183 p_select_list := 'SELECT ' || l_select_list;
1184
1185 IF p_selection_param = 0 THEN
1186
1187 fem_engines_pkg.tech_message (p_severity => g_log_level_1
1188 ,p_module => g_block||'.'||l_api_name
1189 ,p_msg_text => 'Step 2: Condition Predicate Preparation');
1190
1191 Fem_Conditions_Api.Generate_Condition_Predicate
1192 (l_api_version,
1193 l_init_msg_list,
1194 l_commit,
1195 l_encoded,
1196 p_condition_obj_id,
1197 p_effective_date,
1198 g_src_tab_name,
1199 g_src_alias,
1200 'N', -- Display Predicate
1201 p_condition_sel_param,
1202 'Y',
1203 x_return_status,
1204 x_msg_count,
1205 x_msg_data,
1206 p_con_where_clause);
1207
1208 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1209
1210 IF (p_con_where_clause IS NULL) THEN
1211 FEM_ENGINES_PKG.User_Message (
1212 p_app_name => 'FEM'
1213 ,p_msg_name => G_CONDITION_PREDICATE_ERR
1214 ,p_token1 => 'COND_OBJ_ID'
1215 ,p_value1 => p_condition_obj_id);
1216 END IF;
1217
1218 RAISE e_cond_wclause_error;
1219
1220 END IF;
1221
1222 fem_engines_pkg.tech_message (p_severity => g_log_level_1
1223 ,p_module => g_block||'.'||l_api_name
1224 ,p_msg_text => 'Condition Predicate: ' || p_con_where_clause);
1225
1226 fem_engines_pkg.tech_message (p_severity => g_log_level_1
1227 ,p_module => g_block||'.'||l_api_name
1228 ,p_msg_text => 'Step 2: After Condition Predicate Preparation');
1229 END IF;
1230
1231 fnd_msg_pub.count_and_get(p_encoded => p_encoded,
1232 p_count => x_msg_count,
1233 p_data => x_msg_data);
1234
1235 END IF;
1236
1237 fem_engines_pkg.tech_message (p_severity => g_log_level_2
1238 ,p_module => g_block||'.'||l_api_name
1239 ,p_msg_text => 'END');
1240
1241 EXCEPTION
1242
1243 WHEN e_cond_wclause_error THEN
1244 fem_engines_pkg.tech_message (p_severity => g_log_level_5
1245 ,p_module => g_block||'.'||l_api_name
1246 ,p_msg_text => 'Condition Where Clause Exception');
1247 FEM_ENGINES_PKG.User_Message (
1248 p_app_name => 'FEM'
1249 ,p_msg_name => G_GENERATE_PREDICATES_ERR);
1250
1251 fnd_msg_pub.count_and_get(p_encoded => p_encoded,
1252 p_count => x_msg_count,
1253 p_data => x_msg_data);
1254
1255 x_return_status := fnd_api.g_ret_sts_error;
1256
1257 WHEN OTHERS THEN
1258 fem_engines_pkg.tech_message (p_severity => g_log_level_5
1259 ,p_module => g_block||'.'||l_api_name
1260 ,p_msg_text => 'Generate_predicates: General_Exception');
1261
1262 FEM_ENGINES_PKG.User_Message (
1263 p_app_name => 'FEM'
1264 ,p_msg_name => G_GENERATE_PREDICATES_ERR);
1265
1266 fnd_msg_pub.count_and_get(p_encoded => p_encoded,
1267 p_count => x_msg_count,
1268 p_data => x_msg_data);
1269
1270 x_return_status := fnd_api.g_ret_sts_error;
1271
1272 END generate_predicates;
1273
1274 -----------------------------
1275 -- end generate_predicates
1276 -----------------------------
1277
1278 END Fem_Col_Tmplt_Defn_Api_Pub;