1 PACKAGE BODY FEM_MAPPING_PREVIEW_UTIL_PKG AS
2 /* $Header: fem_mapping_preview_util_pkg.plb 120.5 2008/02/08 22:06:54 gcheng ship $ */
3
4 -------------------------------------------------------------------------------
5 -- PRIVATE CONSTANTS
6 -------------------------------------------------------------------------------
7
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'FEM_MAPPING_PREVIEW_UTIL_PKG';
9 G_FACT_ALIAS CONSTANT VARCHAR2(1) := 'F';
10 G_DIM_ALIAS CONSTANT VARCHAR2(1) := 'D';
11 G_STAT_ALIAS CONSTANT VARCHAR2(1) := 'S';
12 G_MATCH_ALIAS CONSTANT VARCHAR2(1) := 'M';
13 G_DIM_TEMPLATE_TABLE CONSTANT VARCHAR2(30) := 'FEM_DIM_TEMPLATE';
14 G_SOURCE CONSTANT FEM_ALLOC_PREVIEW_STATS.preview_row_group%TYPE
15 := 'SOURCE';
16 G_DRIVER CONSTANT FEM_ALLOC_PREVIEW_STATS.preview_row_group%TYPE
17 := 'DRIVER';
18 G_DEBIT CONSTANT FEM_ALLOC_PREVIEW_STATS.preview_row_group%TYPE
19 := 'DEBIT';
20 G_CREDIT CONSTANT FEM_ALLOC_PREVIEW_STATS.preview_row_group%TYPE
21 := 'CREDIT';
22 G_ACCT_TRANS_TYPE CONSTANT VARCHAR2(30) := 'ACCOUNT_TRANS';
23 G_LEDGER_TYPE CONSTANT VARCHAR2(30) := 'LEDGER';
24 G_OTHER_TABLE_TYPE CONSTANT VARCHAR2(30) := 'OTHER_TABLE_TYPE';
25 G_RETRIEVE_STAT CONSTANT FEM_ALLOC_BR_OBJECTS.map_rule_type_code%TYPE
26 := 'RETRIEVE_STATISTICS';
27 G_BY_DIMENSION CONSTANT FEM_ALLOC_BR_OBJECTS.map_rule_type_code%TYPE
28 := 'DIMENSION';
29 G_FACTOR_TABLE CONSTANT FEM_ALLOC_BR_OBJECTS.map_rule_type_code%TYPE
30 := 'FACTOR_TABLE';
31 G_NUMBER_TYPE CONSTANT VARCHAR2(15) := 'NUMBER';
32 G_VARCHAR_TYPE CONSTANT VARCHAR2(15) := 'VARCHAR';
33 G_UNSUPPORTED_TYPE CONSTANT VARCHAR2(15) := 'UNSUPPORTED';
34 G_LEDGER_AMOUNT_COL CONSTANT VARCHAR2(30) := 'XTD_BALANCE_F';
35
36 -------------------------------------------------------------------------------
37 -- PRIVATE SPECIFICATIONS
38 -------------------------------------------------------------------------------
39
40 PROCEDURE GetSelectClause(
41 p_preview_obj_def_id IN NUMBER,
42 p_preview_obj_id IN NUMBER,
43 p_request_id IN NUMBER,
44 p_preview_row_group IN VARCHAR2,
45 p_fact_table_name IN VARCHAR2,
46 x_select_clause OUT NOCOPY VARCHAR2);
47
48 PROCEDURE GetMapTableType(
49 p_table_name IN VARCHAR2,
50 x_map_table_type OUT NOCOPY VARCHAR2);
51
52 PROCEDURE GetOutputMatchingTable(
53 p_preview_obj_def_id IN NUMBER,
54 x_output_match_temp_table OUT NOCOPY VARCHAR2,
55 x_output_match_fact_table OUT NOCOPY VARCHAR2);
56
57 PROCEDURE GetFromClause(
58 p_fact_table_name IN VARCHAR2,
59 x_from_clause OUT NOCOPY VARCHAR2);
60
61 PROCEDURE GetWhereClause(
62 p_preview_obj_def_id IN NUMBER,
63 p_preview_row_group IN VARCHAR2,
64 p_map_obj_def_id IN NUMBER,
65 p_map_rule_type IN VARCHAR2,
66 p_function_cd IN VARCHAR2,
67 p_sub_obj_id IN NUMBER,
68 p_fact_table_name IN VARCHAR2,
69 p_request_id IN NUMBER,
70 p_preview_obj_id IN NUMBER,
71 x_map_where_clause OUT NOCOPY VARCHAR2,
72 x_where_clause OUT NOCOPY VARCHAR2);
73
74 PROCEDURE GetInputWhereClause(
75 p_preview_obj_def_id IN NUMBER,
76 p_preview_row_group IN VARCHAR2,
77 p_map_obj_def_id IN NUMBER,
78 p_map_rule_type IN VARCHAR2,
79 p_fact_table_name IN VARCHAR2,
80 p_sub_obj_id IN NUMBER,
81 p_request_id IN NUMBER,
82 p_preview_obj_id IN NUMBER,
83 x_map_where_clause OUT NOCOPY VARCHAR2,
84 x_where_clause OUT NOCOPY VARCHAR2);
85
86 PROCEDURE GetOutputWhereClause(
87 p_preview_obj_def_id IN NUMBER,
88 p_map_obj_def_id IN NUMBER,
89 p_function_cd IN VARCHAR2,
90 p_fact_table_name IN VARCHAR2,
91 x_where_clause OUT NOCOPY VARCHAR2);
92
93 PROCEDURE CreateTempTable(
94 p_temp_table_seq IN NUMBER,
95 p_preview_obj_def_id IN NUMBER,
96 p_preview_obj_id IN NUMBER,
97 p_preview_row_group IN VARCHAR2,
98 p_preview_display_seq IN NUMBER,
99 p_request_id IN NUMBER,
100 p_map_obj_id IN VARCHAR2,
101 p_map_obj_def_id IN VARCHAR2,
102 p_map_rule_type IN VARCHAR2,
103 p_fact_table_name IN VARCHAR2,
104 p_function_cd IN VARCHAR2,
105 p_sub_obj_id IN NUMBER,
106 x_map_where_clause OUT NOCOPY VARCHAR2,
107 x_temp_table_name OUT NOCOPY VARCHAR2);
108
109 PROCEDURE CreatePreviewStats(
110 p_preview_obj_def_id IN NUMBER,
111 p_preview_row_group IN VARCHAR2,
112 p_preview_display_seq IN NUMBER,
113 p_fact_table_name IN VARCHAR2,
114 p_temp_table_name IN VARCHAR2,
115 p_map_where_clause IN VARCHAR2,
116 p_preview_obj_id IN NUMBER,
117 p_request_id IN NUMBER);
118
119 PROCEDURE CreatePreviewMaps(
120 p_preview_obj_def_id IN NUMBER,
121 p_preview_row_group IN VARCHAR2,
122 p_fact_table_name IN VARCHAR2,
123 p_preview_obj_id IN NUMBER,
124 p_request_id IN NUMBER);
125
126 PROCEDURE UpdatePreviewStats(
127 p_preview_obj_def_id IN NUMBER,
128 p_preview_row_group IN VARCHAR2,
129 p_temp_table_name IN VARCHAR2,
130 p_map_table_type IN VARCHAR2,
131 p_map_obj_def_id IN NUMBER,
132 p_ledger_id IN NUMBER,
133 p_cal_period_id IN NUMBER);
134
135 PROCEDURE GetPreviewAmount(
136 p_preview_obj_def_id IN NUMBER,
137 p_preview_row_group IN VARCHAR2,
138 p_temp_table_name IN VARCHAR2,
139 p_map_table_type IN VARCHAR2,
140 p_map_obj_def_id IN NUMBER,
141 p_ledger_id IN NUMBER,
142 p_cal_period_id IN NUMBER,
143 x_functional_currency OUT NOCOPY VARCHAR2,
144 x_preview_amount_total OUT NOCOPY NUMBER);
145
146 PROCEDURE GetPreviewRowCount(
147 p_temp_table_name IN VARCHAR2,
148 x_preview_row_count OUT NOCOPY NUMBER);
149
150 PROCEDURE CleanOutputTable(
151 p_temp_table_name IN VARCHAR2,
152 p_fact_table_name IN VARCHAR2,
153 p_map_table_type IN VARCHAR2,
154 p_preview_row_group IN VARCHAR2,
155 p_preview_obj_id IN NUMBER,
156 p_request_id IN NUMBER);
157
158 PROCEDURE PopulateDimensionNames(
159 p_preview_obj_def_id IN NUMBER,
160 p_preview_row_group IN VARCHAR2,
161 p_temp_table_name IN VARCHAR2,
162 p_fact_table_name IN VARCHAR2,
163 p_ledger_id IN NUMBER);
164
165 PROCEDURE GetByDimParams(
166 p_preview_obj_def_id IN NUMBER,
167 p_preview_row_group IN VARCHAR2,
168 p_map_obj_def_id IN NUMBER,
169 p_map_rule_type IN VARCHAR2,
170 p_fact_table_name IN VARCHAR2,
171 x_by_dimension_column OUT NOCOPY VARCHAR2,
172 x_by_dimension_id OUT NOCOPY VARCHAR2,
173 x_by_dimension_value OUT NOCOPY VARCHAR2);
174
175 -------------------------------------------------------------------------------
176 -- PUBLIC BODIES
177 -------------------------------------------------------------------------------
178
179 -------------------------------------------------------------------------------
180 --
181 -- PROCEDURE
182 -- Remove_Results
183 --
184 -- DESCRIPTION
185 -- This procedure removes the results generated by a Preview execution.
186 -- It deletes data from the FEM_ALLOC_PREVIEW_STATS and
187 -- FEM_ALLOC_PREVIEW_MAPS tables. It also calls
188 -- FEM_UD_PKG.Remove_Process_Locks to remove the Process Lock
189 -- registration data, and along with it the Preview temporary tables.
190 --
191 -- The Preview UI and FEM_BR_MAPPING_PREVIEW_PVT.DeleteObjectDefinition
192 -- call this API to remove existing Preview results.
193 --
194 -- IN
195 -- p_object_id - Preview rule identifier
196 --
197 -------------------------------------------------------------------------------
198 PROCEDURE Remove_Results(
199 p_api_version IN NUMBER,
200 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
201 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
202 p_encoded IN VARCHAR2 DEFAULT FND_API.G_TRUE,
203 x_return_status OUT NOCOPY VARCHAR2,
204 x_msg_count OUT NOCOPY NUMBER,
205 x_msg_data OUT NOCOPY VARCHAR2,
206 p_preview_obj_def_id IN NUMBER)
207 -------------------------------------------------------------------------------
208 IS
209
210 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
211 'fem.plsql.fem_mapping_preview_util_pkg.remove_results';
212 C_API_NAME CONSTANT VARCHAR2(30) := 'Remove_Results';
213 C_API_VERSION CONSTANT NUMBER := 1.0;
214 --
215 e_api_error EXCEPTION;
216 v_request_id NUMBER;
217 v_preview_obj_id FEM_OBJECT_DEFINITION_B.object_id%TYPE;
218 --
219 -- Gets all object executions for a given preview rule.
220 -- In some cases, only fem_pl_requests gets registered and so
221 -- in that case, cannot rely on fem_pl_object_executions - hence the UNION.
222 CURSOR c_prvw_execs(cv_obj_def_id NUMBER, cv_request_id NUMBER) IS
223 SELECT request_id
224 FROM fem_pl_object_executions
225 WHERE exec_object_definition_id = cv_obj_def_id
226 UNION
227 SELECT cv_request_id
228 FROM dual
229 ORDER BY request_id;
230 --
231 BEGIN
232 --
233 -- Standard Start of API savepoint
234 SAVEPOINT remove_results_pub;
235
236 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
237 FEM_ENGINES_PKG.TECH_MESSAGE(
238 p_severity => FND_LOG.level_procedure,
239 p_module => C_MODULE,
240 p_msg_text => 'Begin Procedure');
241 END IF;
242
243 -- Initialize return status to unexpected error
244 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
245
246 -- Check for call compatibility.
247 IF NOT FND_API.Compatible_API_Call (C_API_VERSION,
248 p_api_version,
249 C_API_NAME,
250 G_PKG_NAME)
251 THEN
252 IF FND_LOG.level_unexpected >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
253 FEM_ENGINES_PKG.TECH_MESSAGE(
254 p_severity => FND_LOG.level_unexpected,
255 p_module => C_MODULE,
256 p_msg_text => 'INTERNAL ERROR: API Version ('||C_API_VERSION
257 ||') not compatible with '
258 ||'passed in version ('||p_api_version||')');
259 END IF;
260 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
261 END IF;
262
263 -- Initialize FND message queue
264 IF p_init_msg_list = FND_API.G_TRUE then
265 FND_MSG_PUB.Initialize;
266 END IF;
267
268 -- Log procedure param values
269 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
270 FEM_ENGINES_PKG.TECH_MESSAGE(
271 p_severity => FND_LOG.level_statement,
272 p_module => C_MODULE,
273 p_msg_text => 'p_preview_obj_def_id = '||to_char(p_preview_obj_def_id));
274 END IF;
275
276 -- See if Preview has run yet
277 BEGIN
278 SELECT request_id
279 INTO v_request_id
280 FROM fem_alloc_previews
281 WHERE preview_obj_def_id = p_preview_obj_def_id;
282 EXCEPTION
283 WHEN no_data_found THEN
284 v_request_id := -1;
285 END;
286
287 -- If Preview has been run, remove the last execution, as well as
288 -- any straglers out there due to errors or what not.
289 IF v_request_id > -1 THEN
290 -- get preview object id
291 v_preview_obj_id := FEM_BUSINESS_RULE_PVT.GetObjectId(
292 p_obj_def_id => p_preview_obj_def_id);
293
294 -- Loop through all preview executions for a given preview version
295 FOR prvw_execs IN c_prvw_execs(cv_obj_def_id => p_preview_obj_def_id,
296 cv_request_id => v_request_id) LOOP
297
298 -- Remove process locks and temporary tables created by Preview execution
299 FEM_UD_PKG.Remove_Process_Locks(
300 p_api_version => 1.0,
301 p_init_msg_list => FND_API.G_FALSE,
302 p_commit => FND_API.G_FALSE,
303 p_encoded => p_encoded,
304 x_return_status => x_return_status,
305 x_msg_count => x_msg_count,
306 x_msg_data => x_msg_data,
307 p_request_id => prvw_execs.request_id,
308 p_object_id => v_preview_obj_id);
309
310 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
311 IF FND_LOG.level_unexpected >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
312 FEM_ENGINES_PKG.TECH_MESSAGE(
313 p_severity => FND_LOG.level_unexpected,
314 p_module => C_MODULE,
315 p_msg_text => 'INTERNAL ERROR: Call to'
316 ||' FEM_UD_PKG.Remove_Process_Locks'
317 ||' failed with return status: '||x_return_status);
318 END IF;
319
320 RAISE e_api_error;
321 END IF;
322
323 END LOOP; -- FOR prvw_execs...
324
325 END IF; -- IF v_request_id > -1 THEN
326
327 -- Now delete all data created by the preview execution in the
328 -- persistent preview output tables
329
330 DELETE FROM fem_alloc_preview_stats
331 WHERE preview_obj_def_id = p_preview_obj_def_id;
332
333 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
334 FEM_ENGINES_PKG.TECH_MESSAGE(
335 p_severity => FND_LOG.level_statement,
336 p_module => C_MODULE,
337 p_msg_text => 'Deleted '||SQL%ROWCOUNT
338 ||' rows from FEM_ALLOC_PREVIEW_STATS');
339 END IF;
340
341 DELETE FROM fem_alloc_preview_maps
342 WHERE preview_obj_def_id = p_preview_obj_def_id;
343
344 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
345 FEM_ENGINES_PKG.TECH_MESSAGE(
346 p_severity => FND_LOG.level_statement,
347 p_module => C_MODULE,
348 p_msg_text => 'Deleted '||SQL%ROWCOUNT
349 ||' rows from FEM_ALLOC_PREVIEW_MAPS');
350 END IF;
351
352 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
353 p_count => x_msg_count,
354 p_data => x_msg_data);
355
356 x_return_status := FND_API.G_RET_STS_SUCCESS;
357
358 IF (p_commit = FND_API.G_TRUE) THEN
359 COMMIT;
360 END IF;
361
362 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
363 FEM_ENGINES_PKG.TECH_MESSAGE(
364 p_severity => FND_LOG.level_procedure,
365 p_module => C_MODULE,
366 p_msg_text => 'End Procedure');
367 END IF;
368 --
369 EXCEPTION
370 -- Since this procedure drops temp tables, rollback segments are lost
371 -- and so instead of rolling back to specific save point, just rollback
372 -- completely.
373 WHEN e_api_error THEN
374 -- When a call to an API fails, just exit because all return params
375 -- have already been set by the API itself.
376 ROLLBACK;
377 WHEN others THEN
378 ROLLBACK;
379
380 IF FND_LOG.level_unexpected >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
381 FEM_ENGINES_PKG.TECH_MESSAGE(
382 p_severity => FND_LOG.level_unexpected,
383 p_module => C_MODULE,
384 p_msg_text => 'Unexpected error: '||SQLERRM);
385 END IF;
386 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
387 FEM_ENGINES_PKG.TECH_MESSAGE(
388 p_severity => FND_LOG.level_procedure,
389 p_module => C_MODULE,
390 p_msg_text => 'End Procedure');
391 END IF;
392
393 -- Log the Oracle error message to the stack.
394 FEM_ENGINES_PKG.USER_MESSAGE(
395 p_app_name =>'FEM',
396 p_msg_name => 'FEM_UNEXPECTED_ERROR',
397 p_token1 => 'ERR_MSG',
398 p_value1 => SQLERRM);
399
400 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
401 p_count => x_msg_count,
402 p_data => x_msg_data);
403
404 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
405 --
406 END Remove_Results;
407 -------------------------------------------------------------------------------
408
409 -------------------------------------------------------------------------------
410 --
411 -- PROCEDURE
412 -- Pre_Process
413 --
414 -- DESCRIPTION
415 -- This procedure is responsible for the pre-processing steps
416 -- to prepare for CCE to run in preview mode.
417 -- For each "preview row group", e.g. SOURCE, DRIVER, CREDIT, DEBIT,
418 -- create a temporary table that mirrors the corresponding fact table
419 -- and store those table names in the FEM_ALLOC_PREVIEW_STATS table.
420 --
421 -- IN
422 -- p_obj_def_id - Preview rule ID
423 -- p_request_id - Preview execution concurrent request ID
424 --
425 -------------------------------------------------------------------------------
426 PROCEDURE Pre_Process(
427 p_api_version IN NUMBER,
428 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
429 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
430 p_encoded IN VARCHAR2 DEFAULT FND_API.G_TRUE,
431 x_return_status OUT NOCOPY VARCHAR2,
432 x_msg_count OUT NOCOPY NUMBER,
433 x_msg_data OUT NOCOPY VARCHAR2,
434 p_preview_obj_def_id IN NUMBER,
435 p_request_id IN NUMBER)
436 -------------------------------------------------------------------------------
437 IS
438
439 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
440 'fem.plsql.fem_mapping_preview_util_pkg.pre_process';
441 C_API_NAME CONSTANT VARCHAR2(30) := 'Pre_Process';
442 C_API_VERSION CONSTANT NUMBER := 1.0;
443 --
444 v_preview_obj_id FEM_OBJECT_DEFINITION_B.object_id%TYPE;
445 v_map_obj_def_id FEM_OBJECT_DEFINITION_B.object_definition_id%TYPE;
446 v_map_obj_id FEM_OBJECT_DEFINITION_B.object_id%TYPE;
447 v_stat_obj_def_id FEM_OBJECT_DEFINITION_B.object_definition_id%TYPE;
448 v_fact_table_name FEM_ALLOC_PREVIEW_STATS.fact_table_name%TYPE;
449 v_temp_table_name FEM_ALLOC_PREVIEW_STATS.temp_table_name%TYPE;
450 v_map_rule_type FEM_ALLOC_BR_OBJECTS.map_rule_type_code%TYPE;
451 v_temp_table_seq NUMBER;
452 v_map_where_clause VARCHAR2(16000);
453 v_create_temp_table BOOLEAN;
454 v_debit_table_name FEM_ALLOC_PREVIEW_STATS.fact_table_name%TYPE;
455 --
456 -- Gets information related to each preview row group needed to
457 -- create the temporary tables.
458 -- p_obj_def_id is the rule version of the parent mapping rule.
459 CURSOR c_row_group_info (cv_obj_def_id NUMBER) IS
460 SELECT f.table_name, f.function_cd, f.sub_object_id,
461 m.preview_row_group, m.preview_row_group_display_seq
462 FROM fem_alloc_br_formula f, fem_function_cd_mapping m
463 WHERE f.function_cd = m.function_cd
464 AND f.object_definition_id = cv_obj_def_id
465 AND nvl(f.enable_flg,'Y') = 'Y'
466 ORDER BY m.preview_row_group_process_seq;
467 --
468 BEGIN
469 --
470 -- Standard Start of API savepoint
471 SAVEPOINT pre_process_pub;
472
473 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
474 FEM_ENGINES_PKG.TECH_MESSAGE(
475 p_severity => FND_LOG.level_procedure,
476 p_module => C_MODULE,
477 p_msg_text => 'Begin Procedure');
478 END IF;
479
480 -- Initialize return status to unexpected error
481 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
482
483 -- Check for call compatibility.
484 IF NOT FND_API.Compatible_API_Call (C_API_VERSION,
485 p_api_version,
486 C_API_NAME,
487 G_PKG_NAME)
488 THEN
489 IF FND_LOG.level_unexpected >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
490 FEM_ENGINES_PKG.TECH_MESSAGE(
491 p_severity => FND_LOG.level_unexpected,
492 p_module => C_MODULE,
493 p_msg_text => 'INTERNAL ERROR: API Version ('||C_API_VERSION
494 ||') not compatible with '
495 ||'passed in version ('||p_api_version||')');
496 END IF;
497 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
498 END IF;
499
500 -- Initialize FND message queue
501 IF p_init_msg_list = FND_API.G_TRUE then
502 FND_MSG_PUB.Initialize;
503 END IF;
504
505 -- Log procedure param values
506 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
507 FEM_ENGINES_PKG.TECH_MESSAGE(
508 p_severity => FND_LOG.level_statement,
509 p_module => C_MODULE,
510 p_msg_text => 'p_preview_obj_def_id = '||to_char(p_preview_obj_def_id));
511 FEM_ENGINES_PKG.TECH_MESSAGE(
512 p_severity => FND_LOG.level_statement,
513 p_module => C_MODULE,
514 p_msg_text => 'p_request_id = '||to_char(p_request_id));
515 END IF;
516
517 -- Get preview object_id
518 v_preview_obj_id := FEM_BUSINESS_RULE_PVT.GetObjectId(
519 p_obj_def_id => p_preview_obj_def_id);
520
521 -- Get mapping object definition id
522 SELECT object_definition_id, object_id
523 INTO v_map_obj_def_id, v_map_obj_id
524 FROM fem_objdef_helper_rules
525 WHERE helper_obj_def_id = p_preview_obj_def_id
526 AND helper_object_type_code = 'MAPPING_PREVIEW';
527
528 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
529 FEM_ENGINES_PKG.TECH_MESSAGE(
530 p_severity => FND_LOG.level_statement,
531 p_module => C_MODULE,
532 p_msg_text => 'v_map_obj_id = '||to_char(v_map_obj_id));
533 FEM_ENGINES_PKG.TECH_MESSAGE(
534 p_severity => FND_LOG.level_statement,
535 p_module => C_MODULE,
536 p_msg_text => 'v_map_obj_def_id = '||to_char(v_map_obj_def_id));
537 END IF;
538
539 -- Get map rule type
540 SELECT map_rule_type_code
541 INTO v_map_rule_type
542 FROM fem_alloc_br_objects
543 WHERE map_rule_object_id = v_map_obj_id;
544
545 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
546 FEM_ENGINES_PKG.TECH_MESSAGE(
547 p_severity => FND_LOG.level_statement,
548 p_module => C_MODULE,
549 p_msg_text => 'v_map_rule_type = '||v_map_rule_type);
550 END IF;
551
552 -- Preview does not support Factor Table rules yet...
553 IF (v_map_rule_type = G_FACTOR_TABLE) THEN
554 IF FND_LOG.level_unexpected >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
555 FEM_ENGINES_PKG.TECH_MESSAGE(
556 p_severity => FND_LOG.level_unexpected,
557 p_module => C_MODULE,
558 p_msg_text => 'Preview does not support Factor Table rules!');
559 END IF;
560 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
561 END IF;
562
563 -- Counter for number of temp objects being created
564 v_temp_table_seq := 0;
565
566 -- For each preview group, create the temporary table
567 -- and insert the temporary table information in FEM_ALLOC_PREVIEW_STATS.
568 FOR row_group IN c_row_group_info(cv_obj_def_id => v_map_obj_def_id) LOOP
569
570 -- If the Mapping Rule Type is By Dimension and
571 -- Preview Row Group is DRIVER, do nothing. By Dimension rule
572 -- does not have a DRIVER from the Preview perspective.
573 --
574 -- The reason is that CCE only looks at the "SOURCE" table when processing
575 -- the By Dimension rule type. It issues a
576 -- SUM(DECODE(by_dim_col, by_dim_val, 0, balances_f)),
577 -- SUM(DECODE(by_dim_col, by_dim_val, balances_f, 0))
578 -- statement to get the non-By Dim value and the By Dim value in one SQL
579 -- Therefore, the By Dimension temporary source table needs to contain
580 -- both By Dim and non-By Dim value.
581
582 IF (v_map_rule_type = G_BY_DIMENSION) AND
583 (row_group.preview_row_group = G_DRIVER) THEN
584
585 null;
586
587 ELSE
588
589 v_temp_table_seq := v_temp_table_seq + 1;
590
591 -- First, assume fact table name is from FEM_ALLOC_BR_FORMULA.TABLE_NAME
592 v_fact_table_name := row_group.table_name;
593
594 -- If this is the driver and the mapping rule type is Retrieve Stats,
595 -- get the fact table name from FEM_STAT_LOOKUPS.STAT_LOOKUP_TABLE
596 -- instead of FEM_ALLOC_BR_FORMULA.TABLE_NAME.
597 IF row_group.preview_row_group = G_DRIVER AND
598 v_map_rule_type = G_RETRIEVE_STAT THEN
599
600 -- First get Stat Lookup obj def id
601 SELECT object_definition_id
602 INTO v_stat_obj_def_id
603 FROM fem_object_definition_b
604 WHERE object_id = row_group.sub_object_id;
605
606 SELECT stat_lookup_table
607 INTO v_fact_table_name
608 FROM fem_stat_lookups
609 WHERE stat_lookup_obj_def_id = v_stat_obj_def_id;
610
611 END IF;
612
613 CreatePreviewMaps(
614 p_preview_obj_def_id => p_preview_obj_def_id,
615 p_preview_row_group => row_group.preview_row_group,
616 p_fact_table_name => v_fact_table_name,
617 p_preview_obj_id => v_preview_obj_id,
618 p_request_id => p_request_id);
619
620 -- Only create one temp table for each target fact table because
621 -- if the same fact table is designated for both credit and debit,
622 -- it does not make sense to write out to separate temp tables.
623 --
624 -- Since we know from
625 -- FEM_FUNCTION_CD_MAPPING.preview_row_group_process_seq
626 -- that DEBIT is processed first, we will only check if CREDIT table
627 -- is the same as the DEBIT table. If there was no DEBIT table,
628 -- then we automatically need to create a CREDIT table.
629 v_create_temp_table := FALSE;
630 IF (row_group.preview_row_group = G_DEBIT) THEN
631 v_debit_table_name := v_fact_table_name;
632 v_create_temp_table := TRUE;
633 ELSIF (row_group.preview_row_group = G_CREDIT) THEN
634 IF ((v_debit_table_name IS NOT NULL) AND
635 (v_fact_table_name = v_debit_table_name)) THEN
636 v_create_temp_table := FALSE;
637 ELSE
638 v_create_temp_table := TRUE;
639 END IF;
640 ELSE
641 v_create_temp_table := TRUE;
642 END IF;
643
644 IF (v_create_temp_table) THEN
645 CreateTempTable(
646 p_temp_table_seq => v_temp_table_seq,
647 p_preview_obj_def_id => p_preview_obj_def_id,
648 p_preview_obj_id => v_preview_obj_id,
649 p_preview_row_group => row_group.preview_row_group,
650 p_preview_display_seq => row_group.preview_row_group_display_seq,
651 p_request_id => p_request_id,
652 p_map_obj_id => v_map_obj_id,
653 p_map_obj_def_id => v_map_obj_def_id,
654 p_map_rule_type => v_map_rule_type,
655 p_fact_table_name => v_fact_table_name,
656 p_function_cd => row_group.function_cd,
657 p_sub_obj_id => row_group.sub_object_id,
658 x_map_where_clause => v_map_where_clause,
659 x_temp_table_name => v_temp_table_name);
660 END IF;
661
662 CreatePreviewStats(
663 p_preview_obj_def_id => p_preview_obj_def_id,
664 p_preview_row_group => row_group.preview_row_group,
665 p_preview_display_seq => row_group.preview_row_group_display_seq,
666 p_fact_table_name => v_fact_table_name,
667 p_temp_table_name => v_temp_table_name,
668 p_map_where_clause => v_map_where_clause,
669 p_preview_obj_id => v_preview_obj_id,
670 p_request_id => p_request_id);
671
672 END IF; -- IF (v_map_rule_tyep = G_BY_DIMENSION) AND ...
673
674 END LOOP;
675
676 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
677 p_count => x_msg_count,
678 p_data => x_msg_data);
679
680 x_return_status := FND_API.G_RET_STS_SUCCESS;
681
682 IF (p_commit = FND_API.G_TRUE) THEN
683 COMMIT;
684 END IF;
685
686 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
687 FEM_ENGINES_PKG.TECH_MESSAGE(
688 p_severity => FND_LOG.level_procedure,
689 p_module => C_MODULE,
690 p_msg_text => 'End Procedure');
691 END IF;
692 --
693 EXCEPTION
694 -- Since this procedure drops temp tables, rollback segments are lost
695 -- and so instead of rolling back to specific save point, just rollback
696 -- completely.
697 WHEN others THEN
698 ROLLBACK;
699
700 IF FND_LOG.level_unexpected >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
701 FEM_ENGINES_PKG.TECH_MESSAGE(
702 p_severity => FND_LOG.level_unexpected,
703 p_module => C_MODULE,
704 p_msg_text => 'Unexpected error: '||SQLERRM);
705 END IF;
706 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
707 FEM_ENGINES_PKG.TECH_MESSAGE(
708 p_severity => FND_LOG.level_procedure,
709 p_module => C_MODULE,
710 p_msg_text => 'End Procedure');
711 END IF;
712
713 -- Log the Oracle error message to the stack.
714 FEM_ENGINES_PKG.USER_MESSAGE(
715 p_app_name =>'FEM',
716 p_msg_name => 'FEM_UNEXPECTED_ERROR',
717 p_token1 => 'ERR_MSG',
718 p_value1 => SQLERRM);
719
720 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
721 p_count => x_msg_count,
722 p_data => x_msg_data);
723
724 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
725 --
726 END Pre_Process;
727 -------------------------------------------------------------------------------
728
729 -------------------------------------------------------------------------------
730 --
731 -- PROCEDURE
732 -- Post_Process
733 --
734 -- DESCRIPTION
735 -- This procedure is responsible for the post-processing steps of:
736 -- 1. generating the preview statistics
737 -- 2. populating the dimension name columns in the temporary tables
738 --
739 -- IN
740 -- p_preview_obj_def_id - Preview rule ID
741 -- p_request_id - Preview execution concurrent request ID
742 --
743 -------------------------------------------------------------------------------
744 PROCEDURE Post_Process(
745 p_api_version IN NUMBER,
746 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
747 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
748 p_encoded IN VARCHAR2 DEFAULT FND_API.G_TRUE,
749 x_return_status OUT NOCOPY VARCHAR2,
750 x_msg_count OUT NOCOPY NUMBER,
751 x_msg_data OUT NOCOPY VARCHAR2,
752 p_preview_obj_def_id IN NUMBER,
753 p_request_id IN NUMBER)
754 -------------------------------------------------------------------------------
755 IS
756
757 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
758 'fem.plsql.fem_mapping_preview_util_pkg.post_process';
759 C_API_NAME CONSTANT VARCHAR2(30) := 'Post_Process';
760 C_API_VERSION CONSTANT NUMBER := 1.0;
761 v_map_obj_def_id FEM_OBJECT_DEFINITION_B.object_definition_id%TYPE;
762 v_map_obj_id FEM_OBJECT_DEFINITION_B.object_id%TYPE;
763 v_preview_obj_id FEM_OBJECT_DEFINITION_B.object_id%TYPE;
764 v_ledger_id FEM_ALLOC_PREVIEWS.ledger_id%TYPE;
765 v_cal_period_id FEM_ALLOC_PREVIEWS.cal_period_id%TYPE;
766 v_map_table_type VARCHAR2(30);
767 --
768 -- Gets information related to each preview row group needed to
769 -- update the Preview Stats and populate the dimension names.
770 CURSOR c_row_group_info (cv_preview_obj_def_id NUMBER) IS
771 SELECT fact_table_name, temp_table_name, preview_row_group
772 FROM fem_alloc_preview_stats
773 WHERE preview_obj_def_id = cv_preview_obj_def_id
774 ORDER BY preview_row_group_display_seq;
775 --
776 BEGIN
777 --
778 -- Standard Start of API savepoint
779 SAVEPOINT post_process_pub;
780
781 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
782 FEM_ENGINES_PKG.TECH_MESSAGE(
783 p_severity => FND_LOG.level_procedure,
784 p_module => C_MODULE,
785 p_msg_text => 'Begin Procedure');
786 END IF;
787
788 -- Initialize return status to unexpected error
789 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
790
791 -- Check for call compatibility.
792 IF NOT FND_API.Compatible_API_Call (C_API_VERSION,
793 p_api_version,
794 C_API_NAME,
795 G_PKG_NAME)
796 THEN
797 IF FND_LOG.level_unexpected >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
798 FEM_ENGINES_PKG.TECH_MESSAGE(
799 p_severity => FND_LOG.level_unexpected,
800 p_module => C_MODULE,
801 p_msg_text => 'INTERNAL ERROR: API Version ('||C_API_VERSION
802 ||') not compatible with '
803 ||'passed in version ('||p_api_version||')');
804 END IF;
805 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
806 END IF;
807
808 -- Initialize FND message queue
809 IF p_init_msg_list = FND_API.G_TRUE then
810 FND_MSG_PUB.Initialize;
811 END IF;
812
813 -- Log procedure param values
814 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
815 FEM_ENGINES_PKG.TECH_MESSAGE(
816 p_severity => FND_LOG.level_statement,
817 p_module => C_MODULE,
818 p_msg_text => 'p_preview_obj_def_id = '||to_char(p_preview_obj_def_id));
819 END IF;
820
821 -- Get the corresponding Mapping object id and object definition id
822 SELECT object_id, object_definition_id
823 INTO v_map_obj_id, v_map_obj_def_id
824 FROM fem_objdef_helper_rules
825 WHERE helper_obj_def_id = p_preview_obj_def_id
826 AND helper_object_type_code = 'MAPPING_PREVIEW';
827
828 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
829 FEM_ENGINES_PKG.TECH_MESSAGE(
830 p_severity => FND_LOG.level_statement,
831 p_module => C_MODULE,
832 p_msg_text => 'v_map_obj_def_id = '||to_char(v_map_obj_def_id));
833 END IF;
834
835 -- Get the preview object id
836 v_preview_obj_id := FEM_BUSINESS_RULE_PVT.GetObjectId(
837 p_obj_def_id => p_preview_obj_def_id);
838
839 -- Get preview parameter values
840 SELECT ledger_id, cal_period_id
841 INTO v_ledger_id, v_cal_period_id
842 FROM fem_alloc_previews
843 WHERE preview_obj_def_id = p_preview_obj_def_id;
844
845 FOR row_group IN c_row_group_info(p_preview_obj_def_id) LOOP
846
847 GetMapTableType(
848 p_table_name => row_group.fact_table_name,
849 x_map_table_type => v_map_table_type);
850
851 CleanOutputTable(
852 p_temp_table_name => row_group.temp_table_name,
853 p_fact_table_name => row_group.fact_table_name,
854 p_map_table_type => v_map_table_type,
855 p_preview_row_group => row_group.preview_row_group,
856 p_preview_obj_id => v_preview_obj_id,
857 p_request_id => p_request_id);
858
859 UpdatePreviewStats(
860 p_preview_obj_def_id => p_preview_obj_def_id,
861 p_preview_row_group => row_group.preview_row_group,
862 p_temp_table_name => row_group.temp_table_name,
863 p_map_table_type => v_map_table_type,
864 p_map_obj_def_id => v_map_obj_def_id,
865 p_ledger_id => v_ledger_id,
866 p_cal_period_id => v_cal_period_id);
867
868 PopulateDimensionNames(
869 p_preview_obj_def_id => p_preview_obj_def_id,
870 p_preview_row_group => row_group.preview_row_group,
871 p_temp_table_name => row_group.temp_table_name,
872 p_fact_table_name => row_group.fact_table_name,
873 p_ledger_id => v_ledger_id);
874
875 END LOOP;
876
877 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
878 p_count => x_msg_count,
879 p_data => x_msg_data);
880
881 x_return_status := FND_API.G_RET_STS_SUCCESS;
882
883 IF (p_commit = FND_API.G_TRUE) THEN
884 COMMIT;
885 END IF;
886
887 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
888 FEM_ENGINES_PKG.TECH_MESSAGE(
889 p_severity => FND_LOG.level_procedure,
890 p_module => C_MODULE,
891 p_msg_text => 'End Procedure');
892 END IF;
893 --
894 EXCEPTION
895 -- Since this procedure drops temp tables, rollback segments are lost
896 -- and so instead of rolling back to specific save point, just rollback
897 -- completely.
898 WHEN others THEN
899 ROLLBACK;
900
901 IF FND_LOG.level_unexpected >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
902 FEM_ENGINES_PKG.TECH_MESSAGE(
903 p_severity => FND_LOG.level_unexpected,
904 p_module => C_MODULE,
905 p_msg_text => 'Unexpected error: '||SQLERRM);
906 END IF;
907 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
908 FEM_ENGINES_PKG.TECH_MESSAGE(
909 p_severity => FND_LOG.level_procedure,
910 p_module => C_MODULE,
911 p_msg_text => 'End Procedure');
912 END IF;
913
914 -- Log the Oracle error message to the stack.
915 FEM_ENGINES_PKG.USER_MESSAGE(
916 p_app_name =>'FEM',
917 p_msg_name => 'FEM_UNEXPECTED_ERROR',
918 p_token1 => 'ERR_MSG',
919 p_value1 => SQLERRM);
920
921 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
922 p_count => x_msg_count,
923 p_data => x_msg_data);
924
925 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
926 --
927 END Post_Process;
928 -------------------------------------------------------------------------------
929
930 -------------------------------------------------------------------------------
931 -- PRIVATE BODIES
932 -------------------------------------------------------------------------------
933
934 -------------------------------------------------------------------------------
935 --
936 -- PROCEDURE
937 -- CreateTempTable
938 --
939 -- DESCRIPTION
940 -- Creates the temporary table based on the fact table being passed in.
941 -- Also create an index on the temporary table based on the
942 -- fact table processing key.
943 --
944 -------------------------------------------------------------------------------
945 PROCEDURE CreateTempTable(
946 p_temp_table_seq IN NUMBER,
947 p_preview_obj_def_id IN NUMBER,
948 p_preview_obj_id IN NUMBER,
949 p_preview_row_group IN VARCHAR2,
950 p_preview_display_seq IN NUMBER,
951 p_request_id IN NUMBER,
952 p_map_obj_id IN VARCHAR2,
953 p_map_obj_def_id IN VARCHAR2,
954 p_map_rule_type IN VARCHAR2,
955 p_fact_table_name IN VARCHAR2,
956 p_function_cd IN VARCHAR2,
957 p_sub_obj_id IN NUMBER,
958 x_map_where_clause OUT NOCOPY VARCHAR2,
959 x_temp_table_name OUT NOCOPY VARCHAR2
960 )
961 -------------------------------------------------------------------------------
962 IS
963 --
964 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
965 'fem.plsql.fem_mapping_preview_util_pkg.CreateTempTable';
966 v_select_clause VARCHAR2(16383);
967 v_from_clause VARCHAR2(4095);
968 v_where_clause VARCHAR2(32767);
969 v_return_status VARCHAR2(1);
970 v_msg_count NUMBER;
971 v_msg_data VARCHAR2(4000);
972 v_index_name VARCHAR2(30);
973 v_index_columns VARCHAR2(4000);
974 --
975 CURSOR c_index_cols (cv_table_name VARCHAR2) IS
976 SELECT c.column_name
977 FROM dba_ind_columns c, user_synonyms s, fem_tables_b t
978 WHERE t.table_name = cv_table_name
979 AND t.table_name = s.synonym_name
980 AND s.table_name = c.table_name
981 AND t.proc_key_index_owner = c.table_owner
982 AND t.proc_key_index_name = c.index_name
983 AND t.proc_key_index_owner = c.index_owner
984 ORDER BY column_position;
985 --
986 BEGIN
987 --
988 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
989 FEM_ENGINES_PKG.TECH_MESSAGE(
990 p_severity => FND_LOG.level_procedure,
991 p_module => C_MODULE,
992 p_msg_text => 'Begin Procedure');
993 END IF;
994
995 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
996 FEM_ENGINES_PKG.TECH_MESSAGE(
997 p_severity => FND_LOG.level_statement,
998 p_module => C_MODULE,
999 p_msg_text => 'p_fact_table_name = '||p_fact_table_name);
1000 FEM_ENGINES_PKG.TECH_MESSAGE(
1001 p_severity => FND_LOG.level_statement,
1002 p_module => C_MODULE,
1003 p_msg_text => 'p_preview_row_group = '||p_preview_row_group);
1004 FEM_ENGINES_PKG.TECH_MESSAGE(
1005 p_severity => FND_LOG.level_statement,
1006 p_module => C_MODULE,
1007 p_msg_text => 'p_function_cd = '||p_function_cd);
1008 FEM_ENGINES_PKG.TECH_MESSAGE(
1009 p_severity => FND_LOG.level_statement,
1010 p_module => C_MODULE,
1011 p_msg_text => 'p_sub_obj_id = '||p_sub_obj_id);
1012
1013 END IF;
1014
1015 -- First get a unique temp table name
1016 FEM_DATABASE_UTIL_PKG.Get_Unique_Temp_Name(
1017 p_api_version => 1.0,
1018 p_init_msg_list => FND_API.G_FALSE,
1019 p_commit => FND_API.G_FALSE,
1020 p_encoded => FND_API.G_TRUE,
1021 x_return_status => v_return_status,
1022 x_msg_count => v_msg_count,
1023 x_msg_data => v_msg_data,
1024 p_temp_type => 'TABLE',
1025 p_request_id => p_request_id,
1026 p_object_id => p_preview_obj_id,
1027 p_table_seq => p_temp_table_seq,
1028 x_temp_name => x_temp_table_name);
1029
1030 IF v_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1031 IF FND_LOG.level_unexpected >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1032 FEM_ENGINES_PKG.TECH_MESSAGE(
1033 p_severity => FND_LOG.level_unexpected,
1034 p_module => C_MODULE,
1035 p_msg_text => 'INTERNAL ERROR: Call to'
1036 ||' FEM_DATABASE_UTIL_PKG.Get_Unique_Temp_Name'
1037 ||' failed with return status: '||v_return_status);
1038 END IF;
1039
1040 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1041 END IF;
1042
1043 -- Then build the SQL to create the temporary SQL
1044 GetSelectClause(
1045 p_preview_obj_def_id => p_preview_obj_def_id,
1046 p_preview_obj_id => p_preview_obj_id,
1047 p_request_id => p_request_id,
1048 p_preview_row_group => p_preview_row_group,
1049 p_fact_table_name => p_fact_table_name,
1050 x_select_clause => v_select_clause);
1051
1052 GetFromClause(
1053 p_fact_table_name => p_fact_table_name,
1054 x_from_clause => v_from_clause);
1055
1056 GetWhereClause(
1057 p_preview_obj_def_id => p_preview_obj_def_id,
1058 p_preview_row_group => p_preview_row_group,
1059 p_map_obj_def_id => p_map_obj_def_id ,
1060 p_map_rule_type => p_map_rule_type,
1061 p_function_cd => p_function_cd,
1062 p_sub_obj_id => p_sub_obj_id,
1063 p_fact_table_name => p_fact_table_name ,
1064 p_request_id => p_request_id,
1065 p_preview_obj_id => p_preview_obj_id,
1066 x_map_where_clause => x_map_where_clause,
1067 x_where_clause => v_where_clause);
1068
1069 -- Create the temp table
1070 FEM_DATABASE_UTIL_PKG.Create_Temp_Table(
1071 p_api_version => 1.0,
1072 p_init_msg_list => FND_API.G_FALSE,
1073 p_commit => FND_API.G_FALSE,
1074 p_encoded => FND_API.G_TRUE,
1075 x_return_status => v_return_status,
1076 x_msg_count => v_msg_count,
1077 x_msg_data => v_msg_data,
1078 p_request_id => p_request_id,
1079 p_object_id => p_preview_obj_id,
1080 p_pb_object_id => p_map_obj_id,
1081 p_table_name => x_temp_table_name,
1082 p_table_def => 'AS '||v_select_clause||' '
1083 ||v_from_clause||' '||v_where_clause);
1084
1085 IF v_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1086 IF FND_LOG.level_unexpected >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1087 FEM_ENGINES_PKG.TECH_MESSAGE(
1088 p_severity => FND_LOG.level_unexpected,
1089 p_module => C_MODULE,
1090 p_msg_text => 'INTERNAL ERROR: Call to'
1091 ||' FEM_DATABASE_UTIL_PKG.Create_Temp_Table'
1092 ||' failed with return status: '||v_return_status);
1093 END IF;
1094
1095 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1096 END IF;
1097
1098 -- First see if there are any index columns
1099 FOR index_col IN c_index_cols(cv_table_name => p_fact_table_name) LOOP
1100 IF v_index_columns IS NULL THEN
1101 v_index_columns := index_col.column_name;
1102 ELSE
1103 v_index_columns := v_index_columns||','||index_col.column_name;
1104 END IF;
1105 END LOOP;
1106
1107 IF v_index_columns IS NOT NULL THEN
1108
1109 -- Get a unique temp index name before creating it
1110 FEM_DATABASE_UTIL_PKG.Get_Unique_Temp_Name(
1111 p_api_version => 1.0,
1112 p_init_msg_list => FND_API.G_FALSE,
1113 p_commit => FND_API.G_FALSE,
1114 p_encoded => FND_API.G_TRUE,
1115 x_return_status => v_return_status,
1116 x_msg_count => v_msg_count,
1117 x_msg_data => v_msg_data,
1118 p_temp_type => 'INDEX',
1119 p_request_id => p_request_id,
1120 p_object_id => p_preview_obj_id,
1121 p_table_seq => p_temp_table_seq,
1122 x_temp_name => v_index_name);
1123
1124 IF v_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1125 IF FND_LOG.level_unexpected >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1126 FEM_ENGINES_PKG.TECH_MESSAGE(
1127 p_severity => FND_LOG.level_unexpected,
1128 p_module => C_MODULE,
1129 p_msg_text => 'INTERNAL ERROR: Call to'
1130 ||' FEM_DATABASE_UTIL_PKG.Get_Unique_Temp_Name'
1131 ||' failed with return status: '||v_return_status);
1132 END IF;
1133
1134 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1135 END IF;
1136
1137 FEM_DATABASE_UTIL_PKG.Create_Temp_Index(
1138 p_api_version => 1.0,
1139 p_init_msg_list => FND_API.G_FALSE,
1140 p_commit => FND_API.G_FALSE,
1141 p_encoded => FND_API.G_TRUE,
1142 x_return_status => v_return_status,
1143 x_msg_count => v_msg_count,
1144 x_msg_data => v_msg_data,
1145 p_request_id => p_request_id,
1146 p_object_id => p_preview_obj_id,
1147 p_pb_object_id => p_map_obj_id,
1148 p_table_name => x_temp_table_name,
1149 p_index_name => v_index_name,
1150 p_index_columns => v_index_columns,
1151 p_unique_flag => 'Y');
1152
1153 IF v_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1154 IF FND_LOG.level_unexpected >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1155 FEM_ENGINES_PKG.TECH_MESSAGE(
1156 p_severity => FND_LOG.level_unexpected,
1157 p_module => C_MODULE,
1158 p_msg_text => 'INTERNAL ERROR: Call to'
1159 ||' FEM_DATABASE_UTIL_PKG.Create_Temp_Index'
1160 ||' failed with return status: '||v_return_status);
1161 END IF;
1162
1163 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1164 END IF;
1165
1166 END IF; -- IF v_index_columns IS NOT NULL
1167
1168
1169 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1170 FEM_ENGINES_PKG.TECH_MESSAGE(
1171 p_severity => FND_LOG.level_procedure,
1172 p_module => C_MODULE,
1173 p_msg_text => 'End Procedure');
1174 END IF;
1175 --
1176 END CreateTemptable;
1177 -------------------------------------------------------------------------------
1178
1179 -------------------------------------------------------------------------------
1180 --
1181 -- PROCEDURE
1182 -- CreatePreviewStats
1183 --
1184 -- DESCRIPTION
1185 -- Inserts a row into FEM_ALLOC_PREVIEW_STATS with the temp table
1186 -- information. In Post_Process, the stats rows will be updated with
1187 -- the preview statistics.
1188 --
1189 -------------------------------------------------------------------------------
1190 PROCEDURE CreatePreviewStats(
1191 p_preview_obj_def_id IN NUMBER,
1192 p_preview_row_group IN VARCHAR2,
1193 p_preview_display_seq IN NUMBER,
1194 p_fact_table_name IN VARCHAR2,
1195 p_temp_table_name IN VARCHAR2,
1196 p_map_where_clause IN VARCHAR2,
1197 p_preview_obj_id IN NUMBER,
1198 p_request_id IN NUMBER
1199 )
1200 -------------------------------------------------------------------------------
1201 IS
1202 --
1203 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1204 'fem.plsql.fem_mapping_preview_util_pkg.CreatePreviewStats';
1205 v_row_count NUMBER;
1206 v_sql VARCHAR2(32767);
1207 --
1208 BEGIN
1209 --
1210 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1211 FEM_ENGINES_PKG.TECH_MESSAGE(
1212 p_severity => FND_LOG.level_procedure,
1213 p_module => C_MODULE,
1214 p_msg_text => 'Begin Procedure');
1215 END IF;
1216
1217 -- Determine the value to store in the ESTIMATED_ROWS column.
1218 -- It represents the number of rows that CCE would pull into the
1219 -- calculations if the mapping rule were running in a normal execution
1220 -- (i.e. not Preview mode). This only applies to source and driver data.
1221 IF p_preview_row_group IN (G_SOURCE, G_DRIVER) THEN
1222
1223 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1224 FEM_ENGINES_PKG.TECH_MESSAGE(
1225 p_severity => FND_LOG.level_statement,
1226 p_module => C_MODULE,
1227 p_msg_text => 'p_map_where_clause = '||p_map_where_clause);
1228 END IF;
1229
1230 v_sql := 'SELECT count(*)'
1231 ||' FROM '||p_fact_table_name||' '||G_FACT_ALIAS;
1232
1233 IF (p_map_where_clause IS NOT NULL) THEN
1234 v_sql := v_sql || ' WHERE '||p_map_where_clause;
1235 END IF;
1236
1237 EXECUTE IMMEDIATE v_sql INTO v_row_count;
1238
1239 END IF;
1240
1241 -- Insert the temp table information into FEM_ALLOC_PREVIEW_STATS
1242 INSERT INTO fem_alloc_preview_stats (
1243 preview_obj_def_id, preview_row_group, preview_row_group_display_seq,
1244 fact_table_name, temp_table_name, estimated_rows, created_by_request_id,
1245 created_by_object_id, last_updated_by_request_id, last_updated_by_object_id)
1246 VALUES (
1247 p_preview_obj_def_id, p_preview_row_group, p_preview_display_seq,
1248 p_fact_table_name, p_temp_table_name, v_row_count, p_request_id,
1249 p_preview_obj_id, p_request_id, p_preview_obj_id);
1250
1251
1252 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1253 FEM_ENGINES_PKG.TECH_MESSAGE(
1254 p_severity => FND_LOG.level_procedure,
1255 p_module => C_MODULE,
1256 p_msg_text => 'End Procedure');
1257 END IF;
1258 --
1259 END CreatePreviewStats;
1260 -------------------------------------------------------------------------------
1261
1262 -------------------------------------------------------------------------------
1263 --
1264 -- PROCEDURE
1265 -- CreatePreviewMaps
1266 --
1267 -- DESCRIPTION
1268 -- Inserts the dimension member column and dimension
1269 -- member name column names in FEM_ALLOC_PREVIEW_MAPS.
1270 --
1271 -------------------------------------------------------------------------------
1272 PROCEDURE CreatePreviewMaps(
1273 p_preview_obj_def_id IN NUMBER,
1274 p_preview_row_group IN VARCHAR2,
1275 p_fact_table_name IN VARCHAR2,
1276 p_preview_obj_id IN NUMBER,
1277 p_request_id IN NUMBER
1278 )
1279 -------------------------------------------------------------------------------
1280 IS
1281 --
1282 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1283 'fem.plsql.fem_mapping_preview_util_pkg.CreatePreviewMaps';
1284 v_row_count NUMBER;
1285 v_sql VARCHAR2(32767);
1286 --
1287 BEGIN
1288 --
1289 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1290 FEM_ENGINES_PKG.TECH_MESSAGE(
1291 p_severity => FND_LOG.level_procedure,
1292 p_module => C_MODULE,
1293 p_msg_text => 'Begin Procedure');
1294 END IF;
1295
1296 -- Insert the dimension member column and dimension
1297 -- member name column names in FEM_ALLOC_PREVIEW_MAPS.
1298 INSERT INTO fem_alloc_preview_maps(
1299 preview_obj_def_id, preview_row_group, dim_member_column_name,
1300 dim_name_column_name, created_by_request_id, created_by_object_id,
1301 last_updated_by_request_id, last_updated_by_object_id)
1302 SELECT p_preview_obj_def_id, p_preview_row_group, tc.column_name,
1303 substr('FEM'||rownum||'_'||p_request_id, 1, 30),
1304 p_request_id, p_preview_obj_id,
1305 p_request_id, p_preview_obj_id
1306 FROM fem_tab_columns_v tc
1307 WHERE tc.table_name = p_fact_table_name
1308 AND tc.fem_data_type_code = 'DIMENSION'
1309 AND tc.column_name IN
1310 (SELECT tcp.column_name
1311 FROM fem_tab_column_prop tcp
1312 WHERE tcp.table_name = tc.table_name
1313 AND tcp.column_property_code IN
1314 ('MAPPING_UI_INPUT', 'PROCESSING_KEY'))
1315 AND tc.column_name <> 'LEDGER_ID';
1316
1317
1318 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1319 FEM_ENGINES_PKG.TECH_MESSAGE(
1320 p_severity => FND_LOG.level_procedure,
1321 p_module => C_MODULE,
1322 p_msg_text => 'End Procedure');
1323 END IF;
1324 --
1325 END CreatePreviewMaps;
1326 -------------------------------------------------------------------------------
1327
1328 -------------------------------------------------------------------------------
1329 --
1330 -- PROCEDURE
1331 -- GetSelectClause
1332 --
1333 -- DESCRIPTION
1334 -- Constructs the SELECT clause to create the temporary table.
1335 -- The structure of the SELECT clause is the same irrespective
1336 -- of the mapping rule type or whether it is for an input or output table:
1337 -- SELECT F.*, D.template_dim_name AS fem1_1234567,
1338 -- D.template_dim_name AS fem2_1234567, ...
1339 --
1340 -------------------------------------------------------------------------------
1341 PROCEDURE GetSelectClause(
1342 p_preview_obj_def_id IN NUMBER,
1343 p_preview_obj_id IN NUMBER,
1344 p_request_id IN NUMBER,
1345 p_preview_row_group IN VARCHAR2,
1346 p_fact_table_name IN VARCHAR2,
1347 x_select_clause OUT NOCOPY VARCHAR2
1348 )
1349 -------------------------------------------------------------------------------
1350 IS
1351 --
1352 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1353 'fem.plsql.fem_mapping_preview_util_pkg.GetSelectClause';
1354 --
1355 CURSOR c_dim (cv_preview_obj_def_id IN NUMBER,
1356 cv_preview_row_group IN VARCHAR2) IS
1357 SELECT dim_name_column_name
1358 FROM fem_alloc_preview_maps
1359 WHERE preview_obj_def_id = cv_preview_obj_def_id
1360 AND preview_row_group = cv_preview_row_group;
1361 --
1362 BEGIN
1363 --
1364 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1365 FEM_ENGINES_PKG.TECH_MESSAGE(
1366 p_severity => FND_LOG.level_procedure,
1367 p_module => C_MODULE,
1368 p_msg_text => 'Begin Procedure');
1369 END IF;
1370
1371
1372 -- Start off the SELECT clause.
1373 -- i.e. 'SELECT F.*'
1374 x_select_clause := 'SELECT '||G_FACT_ALIAS||'.*';
1375
1376 -- Then loop through FEM_ALLOC_PREVIEW_MAPS to get the
1377 -- dimension member name column names to append to the SELECT clause.
1378 -- i.e. ||', D.template_dim_name AS fem1_1234567'
1379 FOR dims IN c_dim(cv_preview_obj_def_id => p_preview_obj_def_id,
1380 cv_preview_row_group => p_preview_row_group) LOOP
1381
1382 x_select_clause := x_select_clause||', '||G_DIM_ALIAS||'.'
1383 ||'template_dim_name AS '
1384 ||dims.dim_name_column_name;
1385
1386 END LOOP;
1387
1388
1389 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1390 FEM_ENGINES_PKG.TECH_MESSAGE(
1391 p_severity => FND_LOG.level_statement,
1392 p_module => C_MODULE,
1393 p_msg_text => 'x_select_clause = '||x_select_clause);
1394 END IF;
1395
1396 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1397 FEM_ENGINES_PKG.TECH_MESSAGE(
1398 p_severity => FND_LOG.level_procedure,
1399 p_module => C_MODULE,
1400 p_msg_text => 'End Procedure');
1401 END IF;
1402 --
1403 END GetSelectClause;
1404 -------------------------------------------------------------------------------
1405
1406 -------------------------------------------------------------------------------
1407 --
1408 -- PROCEDURE
1409 -- GetMapTableType
1410 --
1411 -- DESCRIPTION
1412 -- Gets the Mapping Table Type given a fact table.
1413 -- The possibilities are:
1414 -- Ledger type (G_LEDGER_TYPE)
1415 -- Account or Transaction type (G_ACCT_TRANS_TYPE)
1416 -- Other type (G_OTHER_TABLE_TYPE), such as statistic or factor table
1417 -------------------------------------------------------------------------------
1418 PROCEDURE GetMapTableType(
1419 p_table_name IN VARCHAR2,
1420 x_map_table_type OUT NOCOPY VARCHAR2
1421 )
1422 -------------------------------------------------------------------------------
1423 IS
1424 --
1425 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1426 'fem.plsql.fem_mapping_preview_util_pkg.GetMapTableType';
1427 v_is_ledger VARCHAR2(1);
1428 v_is_acct_trans VARCHAR2(1);
1429 --
1430 -- Returns T if table is a "ledger" table and F otherwise
1431 CURSOR c_is_ledger(cv_table_name VARCHAR2) IS
1432 SELECT decode(count(*),0,'F','T')
1433 FROM fem_table_class_assignmt_v
1434 WHERE table_name = cv_table_name
1435 AND substr(table_classification_code,-6) = 'LEDGER';
1436
1437 -- Returns T if table is an "account or transaction" table and F otherwise
1438 CURSOR c_is_acct_trans(cv_table_name VARCHAR2) IS
1439 SELECT decode(count(*),0,'F','T')
1440 FROM fem_table_class_assignmt_v
1441 WHERE table_name = cv_table_name
1442 AND table_classification_code IN ('ACCOUNT_PROFITABILITY',
1443 'TRANSACTION_PROFITABILITY');
1444 --
1445 BEGIN
1446 --
1447 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1448 FEM_ENGINES_PKG.TECH_MESSAGE(
1449 p_severity => FND_LOG.level_procedure,
1450 p_module => C_MODULE,
1451 p_msg_text => 'Begin Procedure');
1452 END IF;
1453
1454 -- See if table is a ledger table first
1455 OPEN c_is_ledger(p_table_name);
1456 FETCH c_is_ledger INTO v_is_ledger;
1457 CLOSE c_is_ledger;
1458
1459 IF v_is_ledger = 'T' THEN
1460 x_map_table_type := G_LEDGER_TYPE;
1461 ELSE
1462 -- If table is not a ledger table,
1463 -- see if table is an account or transaction table.
1464 OPEN c_is_acct_trans(p_table_name);
1465 FETCH c_is_acct_trans INTO v_is_acct_trans;
1466 CLOSE c_is_acct_trans;
1467
1468 IF v_is_acct_trans = 'T' THEN
1469 x_map_table_type := G_ACCT_TRANS_TYPE;
1470 ELSE
1471 -- Since it is not an account or transaction table,
1472 -- it must be some other type that we don't care about yet...
1473 x_map_table_type := G_OTHER_TABLE_TYPE;
1474 END IF;
1475 END IF;
1476
1477 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1478 FEM_ENGINES_PKG.TECH_MESSAGE(
1479 p_severity => FND_LOG.level_statement,
1480 p_module => C_MODULE,
1481 p_msg_text => 'x_map_table_type = '||x_map_table_type);
1482 END IF;
1483
1484 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1485 FEM_ENGINES_PKG.TECH_MESSAGE(
1486 p_severity => FND_LOG.level_procedure,
1487 p_module => C_MODULE,
1488 p_msg_text => 'End Procedure');
1489 END IF;
1490 --
1491 END GetMapTableType;
1492 -------------------------------------------------------------------------------
1493
1494 -------------------------------------------------------------------------------
1495 --
1496 -- PROCEDURE
1497 -- GetOutputMatchingTable
1498 --
1499 -- BACKGROUND
1500 -- Unlike ledger tables, if the output table is an account or transaction
1501 -- table, the corresponding temporary table needs to be created with data
1502 -- from the output fact table already populated in it. The reason why
1503 -- data needs to exist in the temporary output table before Preview
1504 -- begins processing is that CCE only performs updates on the output table
1505 -- if it is an account or transaction table. In contrast, CCE always
1506 -- inserts into ledger tables. Therefore, if the output table is
1507 -- a ledger table, the temporary table does not need to be created with
1508 -- data already populated in it.
1509 --
1510 -- Instead of creating a complete copy of the output fact table when
1511 -- creating the corresponding temporary table, only preload the temporary
1512 -- output table with the set of data that will be updated.
1513 -- However, since determining the exact set of output data that will be
1514 -- updated is rewriting a good portion of the CCE, the preloaded data
1515 -- will just be based on the data that match with the corresponding
1516 -- input account or transaction table, or the "output matching table".
1517 --
1518 -- DESCRIPTION
1519 -- The purpose of this procedure is to determine the "output matching table".
1520 -- The "output matching table" is the temporary driver table,
1521 -- unless the driver is a ledger table or there is no driver table.
1522 -- Otherwise, the "output matching table" is the temporary source table.
1523 -- The statistic table is not considered a driver table for the purpose
1524 -- of determining the "output matching table". In other words,
1525 -- for a Retrieve Statistic rule type, the "output matching table"
1526 -- is the temporary source table.
1527 --
1528 -- ASSUMPTION
1529 -- Before this API can be called, the temporary tables for the source
1530 -- and driver tables have to be first created. That is the purpose
1531 -- of the FEM_FUNCTION_CD_MAPPING.PREVIEW_ROW_GROUP_PROCESS_SEQ column.
1532 -- The Pre_Process procedure relies on that column to determine the
1533 -- order in which to create the temporary tables.
1534 --
1535 -------------------------------------------------------------------------------
1536 PROCEDURE GetOutputMatchingTable(
1537 p_preview_obj_def_id IN NUMBER,
1538 x_output_match_temp_table OUT NOCOPY VARCHAR2,
1539 x_output_match_fact_table OUT NOCOPY VARCHAR2
1540 )
1541 -------------------------------------------------------------------------------
1542 IS
1543 --
1544 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1545 'fem.plsql.fem_mapping_preview_util_pkg.GetOutputMatchingTable';
1546 v_map_rule_type FEM_ALLOC_BR_OBJECTS.map_rule_type_code%TYPE;
1547 v_fact_table_name FEM_ALLOC_PREVIEW_STATS.fact_table_name%TYPE;
1548 v_temp_table_name FEM_ALLOC_PREVIEW_STATS.temp_table_name%TYPE;
1549 v_map_table_type VARCHAR2(30);
1550 v_temp_table_group FEM_ALLOC_PREVIEW_STATS.preview_row_group%TYPE;
1551 --
1552 -- Retrieves the temporary and fact table names
1553 -- given the Preview Row Group and Preview rule version
1554 CURSOR c_preview_tables (cv_preview_obj_def_id IN NUMBER,
1555 cv_preview_row_group IN VARCHAR2) IS
1556 SELECT s.temp_table_name, s.fact_table_name
1557 FROM fem_alloc_preview_stats s
1558 WHERE s.preview_obj_def_id = cv_preview_obj_def_id
1559 AND s.preview_row_group = cv_preview_row_group;
1560 --
1561 BEGIN
1562 --
1563 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1564 FEM_ENGINES_PKG.TECH_MESSAGE(
1565 p_severity => FND_LOG.level_procedure,
1566 p_module => C_MODULE,
1567 p_msg_text => 'Begin Procedure');
1568 END IF;
1569
1570 -- First assume the temp table group is DRIVER
1571 v_temp_table_group := G_DRIVER;
1572
1573 -- See if a driver table exists
1574 OPEN c_preview_tables(p_preview_obj_def_id, v_temp_table_group);
1575 FETCH c_preview_tables INTO v_temp_table_name, v_fact_table_name;
1576 CLOSE c_preview_tables;
1577
1578 IF v_temp_table_name IS NULL THEN
1579 -- If no driver, then output match table must be the TEMP SOURCE table
1580 v_temp_table_group := G_SOURCE;
1581 ELSE
1582 -- If a driver table exists, check the mapping table type.
1583 GetMapTableType(
1584 p_table_name => v_fact_table_name,
1585 x_map_table_type => v_map_table_type);
1586
1587 -- If driver table type is not account/trans, then output match table
1588 -- is the TEMP SOURCE table.
1589 IF v_map_table_type <> G_ACCT_TRANS_TYPE THEN
1590 v_temp_table_group := G_SOURCE;
1591 END IF;
1592 END IF;
1593
1594 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1595 FEM_ENGINES_PKG.TECH_MESSAGE(
1596 p_severity => FND_LOG.level_statement,
1597 p_module => C_MODULE,
1598 p_msg_text => 'v_temp_table_group = '||v_temp_table_group);
1599 END IF;
1600
1601 -- If the temp table group is no longer DRIVER, then get the
1602 -- temp table name associated with the new temp table group.
1603 IF v_temp_table_group <> G_DRIVER THEN
1604 OPEN c_preview_tables(p_preview_obj_def_id, v_temp_table_group);
1605 FETCH c_preview_tables INTO v_temp_table_name, v_fact_table_name;
1606 CLOSE c_preview_tables;
1607 END IF;
1608
1609 x_output_match_temp_table := v_temp_table_name;
1610 x_output_match_fact_table := v_fact_table_name;
1611
1612 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1613 FEM_ENGINES_PKG.TECH_MESSAGE(
1614 p_severity => FND_LOG.level_statement,
1615 p_module => C_MODULE,
1616 p_msg_text => 'x_output_match_temp_table = '||x_output_match_temp_table);
1617 FEM_ENGINES_PKG.TECH_MESSAGE(
1618 p_severity => FND_LOG.level_statement,
1619 p_module => C_MODULE,
1620 p_msg_text => 'x_output_match_fact_table = '||x_output_match_fact_table);
1621 END IF;
1622
1623 -- An output match table should always be found.
1624 -- If not, then raise unexpected error...
1625 IF x_output_match_temp_table IS NULL THEN
1626 IF FND_LOG.level_unexpected >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1627 FEM_ENGINES_PKG.TECH_MESSAGE(
1628 p_severity => FND_LOG.level_unexpected,
1629 p_module => C_MODULE,
1630 p_msg_text => 'x_output_match_temp_table should NOT be NULL!');
1631 END IF;
1632 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1633 END IF;
1634
1635 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1636 FEM_ENGINES_PKG.TECH_MESSAGE(
1637 p_severity => FND_LOG.level_procedure,
1638 p_module => C_MODULE,
1639 p_msg_text => 'End Procedure');
1640 END IF;
1641 --
1642 END GetOutputMatchingTable;
1643 -------------------------------------------------------------------------------
1644
1645 -------------------------------------------------------------------------------
1646 --
1647 -- PROCEDURE
1648 -- GetFromClause
1649 --
1650 -- DESCRIPTION
1651 -- Constructs the FROM clause to create the temporary table.
1652 -- The FROM clause looks like this for all table types:
1653 -- FROM <fact table> F, fem_dim_template D
1654 --
1655 -------------------------------------------------------------------------------
1656 PROCEDURE GetFromClause(
1657 p_fact_table_name IN VARCHAR2,
1658 x_from_clause OUT NOCOPY VARCHAR2
1659 )
1660 -------------------------------------------------------------------------------
1661 IS
1662 --
1663 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1664 'fem.plsql.fem_mapping_preview_util_pkg.GetFromClause';
1665 --
1666 BEGIN
1667 --
1668 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1669 FEM_ENGINES_PKG.TECH_MESSAGE(
1670 p_severity => FND_LOG.level_procedure,
1671 p_module => C_MODULE,
1672 p_msg_text => 'Begin Procedure');
1673 END IF;
1674
1675 -- Set the FROM clause in the form of:
1676 -- FROM <output fact table> a, fem_dim_template b
1677 x_from_clause := ' FROM '||p_fact_table_name||' '||G_FACT_ALIAS||', '
1678 ||G_DIM_TEMPLATE_TABLE||' '||G_DIM_ALIAS;
1679
1680 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1681 FEM_ENGINES_PKG.TECH_MESSAGE(
1682 p_severity => FND_LOG.level_statement,
1683 p_module => C_MODULE,
1684 p_msg_text => 'x_from_clause = '||x_from_clause);
1685 END IF;
1686
1687 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1688 FEM_ENGINES_PKG.TECH_MESSAGE(
1689 p_severity => FND_LOG.level_procedure,
1690 p_module => C_MODULE,
1691 p_msg_text => 'End Procedure');
1692 END IF;
1693 --
1694 END GetFromClause;
1695 -------------------------------------------------------------------------------
1696
1697 -------------------------------------------------------------------------------
1698 --
1699 -- PROCEDURE
1700 -- GetInputWhereClause
1701 --
1702 -- DESCRIPTION
1703 -- Constructs the WHERE clause to create the temporary input table.
1704 -- The general structure of the WHERE clause is:
1705 -- WHERE <condition filter in mapping rule>
1706 -- AND <parameter filter>
1707 -- AND <preview filter>
1708 -- AND rownum <= <maximum number of rows parameter>
1709 -- If the table is a Statistic table and the row group is DRIVER,
1710 -- then the WHERE CLAUSE looks like:
1711 -- WHERE <statistic condition filter>
1712 -- AND <constant columns>
1713 -- AND <preview filter>
1714 -- AND rownum <= <maximum number of rows parameter>
1715 --
1716 -------------------------------------------------------------------------------
1717 PROCEDURE GetInputWhereClause(
1718 p_preview_obj_def_id IN NUMBER,
1719 p_preview_row_group IN VARCHAR2,
1720 p_map_obj_def_id IN NUMBER,
1721 p_map_rule_type IN VARCHAR2,
1722 p_fact_table_name IN VARCHAR2,
1723 p_sub_obj_id IN NUMBER,
1724 p_request_id IN NUMBER,
1725 p_preview_obj_id IN NUMBER,
1726 x_map_where_clause OUT NOCOPY VARCHAR2,
1727 x_where_clause OUT NOCOPY VARCHAR2
1728 )
1729 -------------------------------------------------------------------------------
1730 IS
1731 --
1732 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1733 'fem.plsql.fem_mapping_preview_util_pkg.GetInputWhereClause';
1734 v_map_table_type VARCHAR2(30);
1735 v_condition_filter VARCHAR2(16000);
1736 v_return_status VARCHAR2(1);
1737 v_msg_count NUMBER;
1738 v_msg_data VARCHAR2(4000);
1739 v_effective_date VARCHAR2(30);
1740 v_ledger_id FEM_ALLOC_PREVIEWS.ledger_id%TYPE;
1741 v_cal_period_id FEM_ALLOC_PREVIEWS.cal_period_id %TYPE;
1742 v_dsg_obj_def_id FEM_ALLOC_PREVIEWS.dsg_obj_def_id%TYPE;
1743 v_query_row_limit FEM_ALLOC_PREVIEWS.query_row_limit%TYPE;
1744 v_stat_obj_def_id FEM_OBJECT_DEFINITION_B.object_definition_id%TYPE;
1745 v_stat_cond_obj_def_id FEM_STAT_LOOKUPS.condition_obj_def_id%TYPE;
1746 v_preview_cond_obj_id FEM_ALLOC_PREVIEWS.source_condition_obj_id%TYPE;
1747 v_by_dimension_column FEM_ALLOC_BR_DIMENSIONS.alloc_dim_col_name%TYPE;
1748 v_by_dimension_id FEM_DIMENSIONS_B.dimension_id%TYPE;
1749 v_by_dimension_value VARCHAR2(38); -- max size of number
1750 --
1751 CURSOR c_stat_cols (cv_stat_obj_def_id NUMBER) IS
1752 SELECT stat_lookup_tbl_col, relational_operand, value
1753 FROM fem_stat_lookup_rel s
1754 WHERE s.stat_lookup_obj_def_id = cv_stat_obj_def_id
1755 AND s.value IS NOT NULL;
1756 --
1757 BEGIN
1758 --
1759 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1760 FEM_ENGINES_PKG.TECH_MESSAGE(
1761 p_severity => FND_LOG.level_procedure,
1762 p_module => C_MODULE,
1763 p_msg_text => 'Begin Procedure');
1764 END IF;
1765
1766 -- Get various preview attributes
1767 SELECT FND_DATE.Date_To_Canonical(effective_date),
1768 ledger_id, cal_period_id, dsg_obj_def_id, query_row_limit,
1769 decode(p_preview_row_group, G_SOURCE, source_condition_obj_id,
1770 driver_condition_obj_id)
1771 INTO v_effective_date, v_ledger_id, v_cal_period_id, v_dsg_obj_def_id,
1772 v_query_row_limit, v_preview_cond_obj_id
1773 FROM fem_alloc_previews
1774 WHERE preview_obj_def_id = p_preview_obj_def_id;
1775
1776 -- The general structure of the WHERE clause is:
1777 -- WHERE <condition filter in mapping rule>
1778 -- AND <parameter filter>
1779 -- AND <preview filter>
1780 -- AND rownum <= <maximum number of rows parameter>
1781 IF (p_map_rule_type <> G_RETRIEVE_STAT) OR
1782 (p_preview_row_group <> G_DRIVER) THEN
1783
1784 -- Set the by dimension parameters to pass into the Predicate procedure
1785 GetByDimParams(
1786 p_preview_obj_def_id => p_preview_obj_def_id,
1787 p_preview_row_group => p_preview_row_group,
1788 p_map_obj_def_id => p_map_obj_def_id,
1789 p_map_rule_type => p_map_rule_type,
1790 p_fact_table_name => p_fact_table_name,
1791 x_by_dimension_column => v_by_dimension_column,
1792 x_by_dimension_id => v_by_dimension_id,
1793 x_by_dimension_value => v_by_dimension_value);
1794
1795 -- Get <condition filter + parameter filter>
1796 FEM_ASSEMBLER_PREDICATE_API.Generate_Assembler_Predicate(
1797 x_predicate_string => x_where_clause,
1798 x_return_status => v_return_status,
1799 x_msg_count => v_msg_count,
1800 x_msg_data => v_msg_data,
1801 p_condition_obj_id => p_sub_obj_id,
1802 p_rule_effective_date => v_effective_date,
1803 p_DS_IO_Def_ID => v_dsg_obj_def_id,
1804 p_Output_Period_ID => v_cal_period_id,
1805 p_Request_ID => p_request_id,
1806 p_Object_ID => p_preview_obj_id,
1807 p_Ledger_ID => v_ledger_id,
1808 p_by_dimension_column => v_by_dimension_column,
1809 p_by_dimension_id => v_by_dimension_id,
1810 p_by_dimension_value => v_by_dimension_value,
1811 p_fact_table_name => p_fact_table_name,
1812 p_table_alias => G_FACT_ALIAS,
1813 p_Ledger_Flag => 'N',
1814 p_api_version => 1.0,
1815 p_init_msg_list => FND_API.G_FALSE,
1816 p_commit => FND_API.G_FALSE,
1817 p_encoded => FND_API.G_TRUE);
1818
1819 IF v_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1820 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1821 FEM_ENGINES_PKG.TECH_MESSAGE(
1822 p_severity => FND_LOG.level_statement,
1823 p_module => C_MODULE,
1824 p_msg_text => 'INTERNAL ERROR: Call to'
1825 ||' FEM_ASSEMBLER_PREDICATE_API.Generate_Assembler_Predicate'
1826 ||' failed with return status: '||v_return_status);
1827 END IF;
1828 END IF;
1829
1830 -- Assembler API should always generate some WHERE clause
1831 IF x_where_clause IS NULL THEN
1832 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1833 END IF;
1834
1835 ELSE
1836 -- If the table is a Statistic table and the row group is DRIVER,
1837 -- then the WHERE CLAUSE looks like:
1838 -- WHERE <statistic condition filter>
1839 -- AND <constant columns>
1840 -- AND <preview filter>
1841 -- AND rownum <= <maximum number of rows parameter>
1842
1843 -- First get Stat Lookup obj def id
1844 SELECT object_definition_id
1845 INTO v_stat_obj_def_id
1846 FROM fem_object_definition_b
1847 WHERE object_id = p_sub_obj_id;
1848
1849 -- Then build the WHERE clause for the stat columns that is bound to
1850 -- defined values as part of the stat definition.
1851 FOR stat_col IN c_stat_cols(cv_stat_obj_def_id => v_stat_obj_def_id) LOOP
1852
1853 IF x_where_clause IS NOT NULL THEN
1854 x_where_clause := x_where_clause||' AND ';
1855 END IF;
1856
1857 x_where_clause := x_where_clause||G_FACT_ALIAS||'.'
1858 ||stat_col.stat_lookup_tbl_col
1859 ||stat_col.relational_operand||''''
1860 ||stat_col.value||'''';
1861
1862 END LOOP;
1863
1864 -- Get <statistic condition filter> if one exists
1865 SELECT condition_obj_def_id
1866 INTO v_stat_cond_obj_def_id
1867 FROM fem_stat_lookups
1868 WHERE stat_lookup_obj_def_id = v_stat_obj_def_id;
1869
1870 IF v_stat_cond_obj_def_id IS NOT NULL THEN
1871 FEM_CONDITIONS_API.generate_condition_predicate(
1872 p_api_version => 1.0,
1873 p_init_msg_list => FND_API.G_FALSE,
1874 p_commit => FND_API.G_FALSE,
1875 p_encoded => FND_API.G_TRUE,
1876 p_condition_obj_id => FEM_BUSINESS_RULE_PVT.GetObjectId(
1877 p_obj_def_id => v_stat_cond_obj_def_id),
1878 p_rule_effective_date => v_effective_date,
1879 p_input_fact_table_name => p_fact_table_name,
1880 p_table_alias => G_FACT_ALIAS,
1881 p_display_predicate => 'N',
1882 p_return_predicate_type => 'BOTH',
1883 p_logging_turned_on => 'Y',
1884 x_return_status => v_return_status,
1885 x_msg_count => v_msg_count,
1886 x_msg_data => v_msg_data,
1887 x_predicate_string => v_condition_filter);
1888
1889 IF v_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1890 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1891 FEM_ENGINES_PKG.TECH_MESSAGE(
1892 p_severity => FND_LOG.level_statement,
1893 p_module => C_MODULE,
1894 p_msg_text => 'INTERNAL ERROR: Call to'
1895 ||' FEM_CONDITIONS_API.generate_condition_predicate'
1896 ||' failed with return status: '||v_return_status);
1897 END IF;
1898
1899 -- Only raise error if return status is Unexpected Error because
1900 -- the Condition API can return with error even if the issue
1901 -- is that a dimension/column does not exist on the table that
1902 -- the condition applied to. This is not an error condition from
1903 -- the CCE perspective.
1904 IF v_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1905 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1906 END IF;
1907 END IF;
1908
1909 -- Only append if condition filter is not null
1910 IF v_condition_filter IS NOT NULL THEN
1911 x_where_clause := x_where_clause||' AND '||v_condition_filter;
1912 END IF;
1913 END IF; -- IF v_stat_cond_obj_def_id IS NOT NULL THEN
1914
1915 END IF; -- IF p_map_rule_type <> G_RETRIEVE_STAT THEN
1916
1917 -- Before appending the additional preview filter, store the
1918 -- WHERE clause just based on the parameters and mapping rule definition.
1919 -- This will be used later when creating the Preview Statistics
1920 -- to get the number of rows that CCE would pull into the calculations
1921 -- if the mapping rule were running in a normal execution
1922 -- (i.e. not Preview mode).
1923 x_map_where_clause := x_where_clause;
1924
1925 -- Get <preview filter> if one exists
1926 IF v_preview_cond_obj_id IS NOT NULL THEN
1927 FEM_CONDITIONS_API.generate_condition_predicate(
1928 p_api_version => 1.0,
1929 p_init_msg_list => FND_API.G_FALSE,
1930 p_commit => FND_API.G_FALSE,
1931 p_encoded => FND_API.G_TRUE,
1932 p_condition_obj_id => v_preview_cond_obj_id,
1933 p_rule_effective_date => v_effective_date,
1934 p_input_fact_table_name => p_fact_table_name,
1935 p_table_alias => G_FACT_ALIAS,
1936 p_display_predicate => 'N',
1937 p_return_predicate_type => 'BOTH',
1938 p_logging_turned_on => 'Y',
1939 x_return_status => v_return_status,
1940 x_msg_count => v_msg_count,
1941 x_msg_data => v_msg_data,
1942 x_predicate_string => v_condition_filter);
1943
1944 IF v_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1945 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1946 FEM_ENGINES_PKG.TECH_MESSAGE(
1947 p_severity => FND_LOG.level_statement,
1948 p_module => C_MODULE,
1949 p_msg_text => 'INTERNAL ERROR: Call to'
1950 ||' FEM_CONDITIONS_API.generate_condition_predicate'
1951 ||' failed with return status: '||v_return_status);
1952 END IF;
1953
1954 -- Only raise error if return status is Unexpected Error because
1955 -- the Condition API can return with error even if the issue
1956 -- is that a dimension/column does not exist on the table that
1957 -- the condition applied to. This is not an error condition from
1958 -- the CCE perspective.
1959 IF v_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1960 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1961 END IF;
1962 END IF;
1963
1964 -- Only append if condition filter is not null
1965 IF v_condition_filter IS NOT NULL THEN
1966 x_where_clause := x_where_clause||' AND '||v_condition_filter;
1967 END IF;
1968
1969 END IF; -- IF p_preview_cond_obj_id IS NOT NULL THEN
1970
1971 -- Finally, add the WHERE keyword and query row limit
1972 IF x_where_clause IS NULL THEN
1973 x_where_clause := 'WHERE '||'rownum <= '||v_query_row_limit;
1974 ELSE
1975 x_where_clause := 'WHERE '||x_where_clause
1976 ||' AND '||'rownum <= '||v_query_row_limit;
1977 END IF;
1978
1979 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1980 FEM_ENGINES_PKG.TECH_MESSAGE(
1981 p_severity => FND_LOG.level_procedure,
1982 p_module => C_MODULE,
1983 p_msg_text => 'End Procedure');
1984 END IF;
1985 --
1986 END GetInputWhereClause;
1987 -------------------------------------------------------------------------------
1988
1989 -------------------------------------------------------------------------------
1990 --
1991 -- PROCEDURE
1992 -- GetOutputWhereClause
1993 --
1994 -- DESCRIPTION
1995 -- Constructs the WHERE clause to create the temporary output table.
1996 -- If the table is a Ledger table, the WHERE clause is:
1997 -- WHERE 1=0
1998 -- If the table is an account/transaction table,
1999 -- then the WHERE clause looks like:
2000 -- WHERE <same as value filter>
2001 -- AND <matching table filter>
2002 --
2003 -- Matching table filter:
2004 -- (PK_COL1, PK_COL2, ... ) IN (SELECT PK_COL1, PK_COL2, ...
2005 -- FROM <output matching table>)
2006 -------------------------------------------------------------------------------
2007 PROCEDURE GetOutputWhereClause(
2008 p_preview_obj_def_id IN NUMBER,
2009 p_map_obj_def_id IN NUMBER,
2010 p_function_cd IN VARCHAR2,
2011 p_fact_table_name IN VARCHAR2,
2012 x_where_clause OUT NOCOPY VARCHAR2
2013 )
2014 -------------------------------------------------------------------------------
2015 IS
2016 --
2017 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
2018 'fem.plsql.fem_mapping_preview_util_pkg.GetOutputWhereClause';
2019 v_map_table_type VARCHAR2(30);
2020 v_output_match_temp_table VARCHAR2(30);
2021 v_output_match_fact_table VARCHAR2(30);
2022 v_output_pk_sql VARCHAR2(8000);
2023 v_match_pk_sql VARCHAR2(8000);
2024 --
2025 CURSOR c_value_cols (cv_map_obj_def_id NUMBER, cv_function_cd VARCHAR2) IS
2026 SELECT alloc_dim_col_name, nvl(to_char(dimension_value),
2027 dimension_value_char) dim_value
2028 FROM fem_alloc_br_dimensions
2029 WHERE object_definition_id = cv_map_obj_def_id
2030 AND function_cd = cv_function_cd
2031 AND alloc_dim_usage_code = 'VALUE';
2032
2033 CURSOR c_match_cols (cv_fact_table VARCHAR2, cv_match_fact_table VARCHAR2,
2034 cv_map_obj_def_id NUMBER, cv_function_cd VARCHAR) IS
2035 SELECT o.column_name output_col, m.column_name match_col
2036 FROM fem_tab_column_prop o, fem_tab_columns_v m
2037 WHERE o.table_name = cv_fact_table
2038 AND o.column_property_code = 'PROCESSING_KEY'
2039 AND m.table_name = cv_match_fact_table
2040 AND o.column_name = m.column_name
2041 AND o.column_name NOT IN
2042 (SELECT alloc_dim_col_name
2043 FROM fem_alloc_br_dimensions
2044 WHERE object_definition_id = cv_map_obj_def_id
2045 AND function_cd = cv_function_cd
2046 AND alloc_dim_usage_code = 'VALUE');
2047
2048 --
2049 BEGIN
2050 --
2051 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2052 FEM_ENGINES_PKG.TECH_MESSAGE(
2053 p_severity => FND_LOG.level_procedure,
2054 p_module => C_MODULE,
2055 p_msg_text => 'Begin Procedure');
2056 END IF;
2057
2058 -- First get mapping table type
2059 GetMapTableType(
2060 p_table_name => p_fact_table_name,
2061 x_map_table_type => v_map_table_type);
2062
2063 IF v_map_table_type = G_LEDGER_TYPE THEN
2064
2065 x_where_clause := '1=0';
2066
2067 ELSIF v_map_table_type = G_ACCT_TRANS_TYPE THEN
2068
2069 -- First restrict based on those columns with values already defined
2070 FOR value_col IN c_value_cols(cv_map_obj_def_id => p_map_obj_def_id,
2071 cv_function_cd => p_function_cd) LOOP
2072 IF value_col.dim_value IS NOT NULL THEN
2073
2074 IF x_where_clause IS NOT NULL THEN
2075 x_where_clause := x_where_clause||' AND ';
2076 END IF;
2077
2078 x_where_clause := x_where_clause||G_FACT_ALIAS||'.'
2079 ||value_col.alloc_dim_col_name
2080 ||'='''||value_col.dim_value||'''';
2081 END IF;
2082 END LOOP;
2083
2084 -- Get matching output table information
2085 GetOutputMatchingTable(
2086 p_preview_obj_def_id => p_preview_obj_def_id,
2087 x_output_match_temp_table => v_output_match_temp_table,
2088 x_output_match_fact_table => v_output_match_fact_table);
2089
2090 -- Then restrict based on matching processing key columns between
2091 -- output table and matching output table.
2092 FOR match_col IN c_match_cols(cv_fact_table => p_fact_table_name,
2093 cv_match_fact_table => v_output_match_fact_table,
2094 cv_map_obj_def_id => p_map_obj_def_id,
2095 cv_function_cd => p_function_cd) LOOP
2096
2097 IF v_output_pk_sql IS NOT NULL THEN
2098 v_output_pk_sql := v_output_pk_sql||',';
2099 v_match_pk_sql := v_match_pk_sql||',';
2100 END IF;
2101
2102 v_output_pk_sql := v_output_pk_sql
2103 ||G_FACT_ALIAS||'.'||match_col.output_col;
2104 v_match_pk_sql := v_match_pk_sql
2105 ||G_MATCH_ALIAS||'.'||match_col.match_col;
2106
2107 END LOOP;
2108
2109 -- Create <matching table filter>:
2110 -- (PK_COL1, PK_COL2, ... ) IN (SELECT PK_COL1, PK_COL2, ...
2111 -- FROM <output matching table>)
2112 IF v_output_pk_sql IS NOT NULL THEN
2113 IF x_where_clause IS NOT NULL THEN
2114 x_where_clause := x_where_clause||' AND ';
2115 END IF;
2116
2117 x_where_clause := x_where_clause||'('||v_output_pk_sql||')'
2118 ||' IN (SELECT '||v_match_pk_sql||' FROM '
2119 ||v_output_match_temp_table||' '||G_MATCH_ALIAS||')';
2120 END IF;
2121
2122 ELSE
2123
2124 IF FND_LOG.level_unexpected >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2125 FEM_ENGINES_PKG.TECH_MESSAGE(
2126 p_severity => FND_LOG.level_unexpected,
2127 p_module => C_MODULE,
2128 p_msg_text => 'Unsupported output table type: '||v_map_table_type);
2129 END IF;
2130
2131 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2132
2133 END IF;
2134
2135 -- Finally, add the WHERE keyword
2136 IF x_where_clause IS NOT NULL THEN
2137 x_where_clause := 'WHERE '||x_where_clause;
2138 END IF;
2139
2140 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2141 FEM_ENGINES_PKG.TECH_MESSAGE(
2142 p_severity => FND_LOG.level_procedure,
2143 p_module => C_MODULE,
2144 p_msg_text => 'End Procedure');
2145 END IF;
2146 --
2147 END GetOutputWhereClause;
2148 -------------------------------------------------------------------------------
2149
2150 -------------------------------------------------------------------------------
2151 --
2152 -- PROCEDURE
2153 -- GetWhereClause
2154 --
2155 -- DESCRIPTION
2156 -- Constructs the WHERE clause to create the temporary table.
2157 -- The structure of the WHERE clause differs depending on the
2158 -- mapping rule type or whether it is for an input or output table.
2159 --
2160 -------------------------------------------------------------------------------
2161 PROCEDURE GetWhereClause(
2162 p_preview_obj_def_id IN NUMBER,
2163 p_preview_row_group IN VARCHAR2,
2164 p_map_obj_def_id IN NUMBER,
2165 p_map_rule_type IN VARCHAR2,
2166 p_function_cd IN VARCHAR2,
2167 p_sub_obj_id IN NUMBER,
2168 p_fact_table_name IN VARCHAR2,
2169 p_request_id IN NUMBER,
2170 p_preview_obj_id IN NUMBER,
2171 x_map_where_clause OUT NOCOPY VARCHAR2,
2172 x_where_clause OUT NOCOPY VARCHAR2
2173 )
2174 -------------------------------------------------------------------------------
2175 IS
2176 --
2177 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
2178 'fem.plsql.fem_mapping_preview_util_pkg.GetWhereClause';
2179 v_map_table_type VARCHAR2(30);
2180 --
2181 BEGIN
2182 --
2183 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2184 FEM_ENGINES_PKG.TECH_MESSAGE(
2185 p_severity => FND_LOG.level_procedure,
2186 p_module => C_MODULE,
2187 p_msg_text => 'Begin Procedure');
2188 END IF;
2189
2190 IF p_preview_row_group IN (G_SOURCE, G_DRIVER) THEN
2191
2192 GetInputWhereClause(
2193 p_preview_obj_def_id => p_preview_obj_def_id,
2194 p_preview_row_group => p_preview_row_group,
2195 p_map_obj_def_id => p_map_obj_def_id,
2196 p_map_rule_type => p_map_rule_type,
2197 p_fact_table_name => p_fact_table_name ,
2198 p_sub_obj_id => p_sub_obj_id,
2199 p_request_id => p_request_id,
2200 p_preview_obj_id => p_preview_obj_id,
2201 x_map_where_clause => x_map_where_clause,
2202 x_where_clause => x_where_clause);
2203
2204 ELSE
2205
2206 GetOutputWhereClause(
2207 p_preview_obj_def_id => p_preview_obj_def_id,
2208 p_map_obj_def_id => p_map_obj_def_id,
2209 p_function_cd => p_function_cd,
2210 p_fact_table_name => p_fact_table_name,
2211 x_where_clause => x_where_clause);
2212
2213 END IF;
2214
2215 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2216 FEM_ENGINES_PKG.TECH_MESSAGE(
2217 p_severity => FND_LOG.level_statement,
2218 p_module => C_MODULE,
2219 p_msg_text => 'x_where_clause = '||x_where_clause);
2220 END IF;
2221
2222 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2223 FEM_ENGINES_PKG.TECH_MESSAGE(
2224 p_severity => FND_LOG.level_procedure,
2225 p_module => C_MODULE,
2226 p_msg_text => 'End Procedure');
2227 END IF;
2228 --
2229 END GetWhereClause;
2230 -------------------------------------------------------------------------------
2231
2232 -------------------------------------------------------------------------------
2233 --
2234 -- PROCEDURE
2235 -- UpdatePreviewStats
2236 --
2237 -- DESCRIPTION
2238 -- Populate the following columns in FEM_ALLOC_PREVIEW_STATS:
2239 -- 1. PREVIEW_AMOUNT_TOTAL: Sum of the source, driver, debit and credit
2240 -- amounts that CCE used or generated during the Preview run.
2241 -- 2. PREVIEW_ROWS: Number of rows for the source and driver data that
2242 -- CCE used during the Preview run.
2243 --
2244 -------------------------------------------------------------------------------
2245 PROCEDURE UpdatePreviewStats(
2246 p_preview_obj_def_id IN NUMBER,
2247 p_preview_row_group IN VARCHAR2,
2248 p_temp_table_name IN VARCHAR2,
2249 p_map_table_type IN VARCHAR2,
2250 p_map_obj_def_id IN NUMBER,
2251 p_ledger_id IN NUMBER,
2252 p_cal_period_id IN NUMBER
2253 )
2254 -------------------------------------------------------------------------------
2255 IS
2256 --
2257 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
2258 'fem.plsql.fem_mapping_preview_util_pkg.UpdatePreviewStats';
2259 v_functional_currency FEM_ALLOC_PREVIEW_STATS.amount_currency_code%TYPE;
2260 v_amount_total FEM_ALLOC_PREVIEW_STATS.preview_amount_total%TYPE;
2261 v_row_count FEM_ALLOC_PREVIEW_STATS.preview_rows%TYPE;
2262 --
2263 BEGIN
2264 --
2265 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2266 FEM_ENGINES_PKG.TECH_MESSAGE(
2267 p_severity => FND_LOG.level_procedure,
2268 p_module => C_MODULE,
2269 p_msg_text => 'Begin Procedure');
2270 END IF;
2271
2272 GetPreviewAmount(
2273 p_preview_obj_def_id => p_preview_obj_def_id,
2274 p_preview_row_group => p_preview_row_group,
2275 p_temp_table_name => p_temp_table_name,
2276 p_map_table_type => p_map_table_type,
2277 p_map_obj_def_id => p_map_obj_def_id,
2278 p_ledger_id => p_ledger_id,
2279 p_cal_period_id => p_cal_period_id,
2280 x_functional_currency => v_functional_currency,
2281 x_preview_amount_total => v_amount_total);
2282
2283 GetPreviewRowCount(
2284 p_temp_table_name => p_temp_table_name,
2285 x_preview_row_count => v_row_count);
2286
2287 UPDATE fem_alloc_preview_stats
2288 SET preview_amount_total = v_amount_total,
2289 amount_currency_code = v_functional_currency,
2290 preview_rows = v_row_count
2291 WHERE preview_obj_def_id = p_preview_obj_def_id
2292 AND preview_row_group = p_preview_row_group;
2293
2294 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2295 FEM_ENGINES_PKG.TECH_MESSAGE(
2296 p_severity => FND_LOG.level_procedure,
2297 p_module => C_MODULE,
2298 p_msg_text => 'End Procedure');
2299 END IF;
2300 --
2301 END UpdatePreviewStats;
2302 -------------------------------------------------------------------------------
2303
2304 -------------------------------------------------------------------------------
2305 --
2306 -- PROCEDURE
2307 -- GetPreviewAmount
2308 --
2309 -- DESCRIPTION
2310 -- Returns the summed amount and its associated currency for a given
2311 -- temporary table. If the individual amounts are represented in
2312 -- currencies other than the functional currency, this procedure will
2313 -- convert those amounts to the functional currency amount before
2314 -- summing them.
2315 --
2316 -------------------------------------------------------------------------------
2317 PROCEDURE GetPreviewAmount(
2318 p_preview_obj_def_id IN NUMBER,
2319 p_preview_row_group IN VARCHAR2,
2320 p_temp_table_name IN VARCHAR2,
2321 p_map_table_type IN VARCHAR2,
2322 p_map_obj_def_id IN NUMBER,
2323 p_ledger_id IN NUMBER,
2324 p_cal_period_id IN NUMBER,
2325 x_functional_currency OUT NOCOPY VARCHAR2,
2326 x_preview_amount_total OUT NOCOPY NUMBER
2327 )
2328 -------------------------------------------------------------------------------
2329 IS
2330 --
2331 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
2332 'fem.plsql.fem_mapping_preview_util_pkg.GetPreviewAmount';
2333 v_dim_id FEM_DIMENSIONS_B.dimension_id%TYPE;
2334 v_dim_attr_id FEM_DIM_ATTRIBUTES_B.attribute_id%TYPE;
2335 v_dim_attr_ver_id FEM_DIM_ATTR_VERSIONS_B.version_id%TYPE;
2336 v_return_code NUMBER;
2337 v_cal_per_end_date FEM_CAL_PERIODS_ATTR.date_assign_value%TYPE;
2338 v_amount_column FEM_ALLOC_BR_FORMULA.column_name%TYPE;
2339 v_sql VARCHAR2(4000);
2340 v_amount NUMBER;
2341 v_conv_amount NUMBER;
2342 v_amount_currency FEM_BALANCES.currency_code%TYPE;
2343 v_denom NUMBER;
2344 v_numer NUMBER;
2345 v_rate NUMBER;
2346
2347 TYPE RefCurTyp IS REF CURSOR; -- define weak REF CURSOR type
2348 amount_cv RefCurTyp;
2349 --
2350 BEGIN
2351 --
2352 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2353 FEM_ENGINES_PKG.TECH_MESSAGE(
2354 p_severity => FND_LOG.level_procedure,
2355 p_module => C_MODULE,
2356 p_msg_text => 'Begin Procedure');
2357 END IF;
2358
2359 -- Amount total does not make sense for DRIVER data.
2360 -- Therefore, this procedure will simply set the amount and currency
2361 -- OUT params to NULL and return when preview row group is DRIVER.
2362 IF p_preview_row_group = G_DRIVER THEN
2363 x_functional_currency := NULL;
2364 x_preview_amount_total := NULL;
2365 RETURN;
2366 END IF;
2367
2368 --
2369 -- First get functional currency
2370 --
2371 SELECT dimension_id
2372 INTO v_dim_id
2373 FROM fem_dimensions_b
2374 WHERE dimension_varchar_label = 'LEDGER';
2375
2376 FEM_DIMENSION_UTIL_PKG.get_dim_attr_id_ver_id
2377 (p_dim_id => v_dim_id,
2378 p_attr_label => 'LEDGER_FUNCTIONAL_CRNCY_CODE',
2379 x_attr_id => v_dim_attr_id,
2380 x_ver_id => v_dim_attr_ver_id,
2381 x_err_code => v_return_code);
2382
2383 IF v_return_code <> 0 THEN -- if not success
2384 IF FND_LOG.level_unexpected >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2385 FEM_ENGINES_PKG.TECH_MESSAGE(
2386 p_severity => FND_LOG.level_unexpected,
2387 p_module => C_MODULE,
2388 p_msg_text => 'INTERNAL ERROR: Call to'
2389 ||' FEM_DIMENSION_UTIL_PKG.get_dim_attr_id_ver_id'
2390 ||' to get LEDGER_FUNCTIONAL_CRNCY_CODE attribute'
2391 ||' information failed with return code: '||v_return_code);
2392 END IF;
2393 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2394 END IF;
2395
2396 SELECT dim_attribute_varchar_member
2397 INTO x_functional_currency
2398 FROM fem_ledgers_attr
2399 WHERE attribute_id = v_dim_attr_id
2400 AND version_id = v_dim_attr_ver_id
2401 AND ledger_id = p_ledger_id;
2402
2403 IF x_functional_currency IS NULL THEN
2404 IF FND_LOG.level_unexpected >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2405 FEM_ENGINES_PKG.TECH_MESSAGE(
2406 p_severity => FND_LOG.level_unexpected,
2407 p_module => C_MODULE,
2408 p_msg_text => 'Functional currency does not exist for the ledger'
2409 ||' id: '||p_ledger_id);
2410 END IF;
2411 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2412 END IF;
2413
2414 --
2415 -- Then get calendar period end date.
2416 --
2417 SELECT dimension_id
2418 INTO v_dim_id
2419 FROM fem_dimensions_b
2420 WHERE dimension_varchar_label = 'CAL_PERIOD';
2421
2422 FEM_DIMENSION_UTIL_PKG.get_dim_attr_id_ver_id
2423 (p_dim_id => v_dim_id,
2424 p_attr_label => 'CAL_PERIOD_END_DATE',
2425 x_attr_id => v_dim_attr_id,
2426 x_ver_id => v_dim_attr_ver_id,
2427 x_err_code => v_return_code);
2428
2429 IF v_return_code <> 0 THEN -- if not success
2430 IF FND_LOG.level_unexpected >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2431 FEM_ENGINES_PKG.TECH_MESSAGE(
2432 p_severity => FND_LOG.level_unexpected,
2433 p_module => C_MODULE,
2434 p_msg_text => 'INTERNAL ERROR: Call to'
2435 ||' FEM_DIMENSION_UTIL_PKG.get_dim_attr_id_ver_id'
2436 ||' to get CAL_PERIOD_END_DATE attribute'
2437 ||' information failed with return code: '||v_return_code);
2438 END IF;
2439 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2440 END IF;
2441
2442 SELECT date_assign_value
2443 INTO v_cal_per_end_date
2444 FROM fem_cal_periods_attr
2445 WHERE attribute_id = v_dim_attr_id
2446 AND version_id = v_dim_attr_ver_id
2447 AND cal_period_id = p_cal_period_id;
2448
2449 IF v_cal_per_end_date IS NULL THEN
2450 IF FND_LOG.level_unexpected >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2451 FEM_ENGINES_PKG.TECH_MESSAGE(
2452 p_severity => FND_LOG.level_unexpected,
2453 p_module => C_MODULE,
2454 p_msg_text => 'Calendar Period End Date does not exist for this'
2455 ||' calendar period: '||p_cal_period_id);
2456 END IF;
2457 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2458 END IF;
2459
2460 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2461 FEM_ENGINES_PKG.Tech_Message(
2462 p_severity => FND_LOG.level_statement,
2463 p_module => C_MODULE,
2464 p_msg_text => 'The Calendar Period End Date is '
2465 ||FND_DATE.date_to_displayDT(v_cal_per_end_date));
2466 END IF;
2467
2468
2469 --
2470 -- Find the amount column for the table.
2471 --
2472
2473 -- For Ledger tables, the amount column is always XTD_BALANCE_F.
2474 -- For Account/Transaction tables, the amount column is stored in
2475 -- FEM_ALLOC_BR_FORMULA.column_name
2476 IF p_map_table_type = G_LEDGER_TYPE THEN
2477
2478 v_amount_column := G_LEDGER_AMOUNT_COL;
2479
2480 ELSIF p_map_table_type = G_ACCT_TRANS_TYPE THEN
2481
2482 SELECT f.column_name
2483 INTO v_amount_column
2484 FROM fem_alloc_br_formula f, fem_function_cd_mapping m
2485 WHERE f.object_definition_id = p_map_obj_def_id
2486 AND f.function_cd = m.function_cd
2487 AND m.preview_row_group = p_preview_row_group;
2488
2489 IF v_amount_column IS NULL THEN
2490 IF FND_LOG.level_unexpected >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2491 FEM_ENGINES_PKG.TECH_MESSAGE(
2492 p_severity => FND_LOG.level_unexpected,
2493 p_module => C_MODULE,
2494 p_msg_text => 'Amount column is null for this preview group: '
2495 ||p_preview_row_group);
2496 END IF;
2497 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2498 END IF;
2499
2500 ELSE
2501
2502 IF FND_LOG.level_unexpected >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2503 FEM_ENGINES_PKG.TECH_MESSAGE(
2504 p_severity => FND_LOG.level_unexpected,
2505 p_module => C_MODULE,
2506 p_msg_text => 'Unexpected table classification type: '
2507 ||p_map_table_type);
2508 END IF;
2509 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2510
2511 END IF; -- IF p_map_table_type = G_LEDGER_TYPE THEN
2512
2513 --
2514 -- Sum amount column, grouped by currency
2515 --
2516 v_sql := 'SELECT SUM('||v_amount_column||'), currency_code'
2517 ||' FROM '||p_temp_table_name
2518 ||' GROUP BY currency_code';
2519
2520 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2521 FEM_ENGINES_PKG.TECH_MESSAGE(
2522 p_severity => FND_LOG.level_statement,
2523 p_module => C_MODULE,
2524 p_msg_text => 'Preview amount SQL = '||v_sql);
2525 END IF;
2526
2527 -- Initialize amount
2528 x_preview_amount_total := 0;
2529
2530 OPEN amount_cv FOR v_sql;
2531
2532 LOOP
2533
2534 FETCH amount_cv INTO v_amount, v_amount_currency;
2535 EXIT WHEN amount_cv%NOTFOUND;
2536
2537 -- If the amount is NULL for some reason, take that to mean zero.
2538 IF v_amount IS NULL THEN
2539 v_amount := 0;
2540 END IF;
2541
2542 BEGIN
2543 GL_CURRENCY_API.Convert_Closest_Amount(
2544 x_from_currency => v_amount_currency,
2545 x_to_currency => x_functional_currency,
2546 x_conversion_date => v_cal_per_end_date,
2547 x_conversion_type => 'CORPORATE',
2548 x_user_rate => null,
2549 x_amount => v_amount,
2550 x_max_roll_days => 730, -- based on hardcoded value in CCE
2551 x_converted_amount => v_conv_amount,
2552 x_denominator => v_denom,
2553 x_numerator => v_numer,
2554 x_rate => v_rate);
2555 EXCEPTION
2556 -- If there are any issues getting the rate,
2557 -- do no conversion and push a warning message in the message stack.
2558 WHEN others THEN
2559 FEM_ENGINES_PKG.PUT_MESSAGE(
2560 p_app_name => 'FEM',
2561 p_msg_name => 'NO_XLATE_RATE_FOUND',
2562 p_token1 => 'FROM_CURRENCY',
2563 p_value1 => v_amount_currency,
2564 p_token2 => 'TO_CURRENCY',
2565 p_value2 => x_functional_currency);
2566
2567 v_conv_amount := v_amount;
2568 END;
2569
2570 x_preview_amount_total := x_preview_amount_total + v_conv_amount;
2571
2572 END LOOP;
2573
2574 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2575 FEM_ENGINES_PKG.TECH_MESSAGE(
2576 p_severity => FND_LOG.level_procedure,
2577 p_module => C_MODULE,
2578 p_msg_text => 'End Procedure');
2579 END IF;
2580 --
2581 END GetPreviewAmount;
2582 -------------------------------------------------------------------------------
2583
2584 -------------------------------------------------------------------------------
2585 --
2586 -- PROCEDURE
2587 -- GetPreviewRowCount
2588 --
2589 -- DESCRIPTION
2590 -- Gets the row count for a Preview Row Group. Instead of passing in
2591 -- the row group, pass in the temporary table associated with the
2592 -- row group. This way, this API just needs to perform a SELECT count(*)
2593 -- against it to obtain the row count.
2594 -- No additional WHERE clause is needed because whatever is in the
2595 -- temporary table is what is part of the Preview execution.
2596 --
2597 -- Caveat: For output data (debit/credit), if the table is an
2598 -- account or transaction table, this API needs to first clear out
2599 -- all rows that the CCE did not update as part of its processing.
2600 -- The reason this is needed is because initially, when the output
2601 -- temporary tables were created (by the Pre_Process procedure),
2602 -- the initial data populated was most likely more than what CCE
2603 -- was going to write out to (actually, update). It was done to
2604 -- simplify the logic in the Pre_Process procedure. Without that
2605 -- simplification, the Pre_Process procedure would need to basically
2606 -- implement the logic in CCE to determine exactly which rows CCE
2607 -- was going to update --- way too big a task.
2608 --
2609 -------------------------------------------------------------------------------
2610 PROCEDURE GetPreviewRowCount(
2611 p_temp_table_name IN VARCHAR2,
2612 x_preview_row_count OUT NOCOPY NUMBER
2613 )
2614 -------------------------------------------------------------------------------
2615 IS
2616 --
2617 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
2618 'fem.plsql.fem_mapping_preview_util_pkg.GetPreviewRowCount';
2619 v_sql VARCHAR2(4000);
2620 --
2621 BEGIN
2622 --
2623 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2624 FEM_ENGINES_PKG.TECH_MESSAGE(
2625 p_severity => FND_LOG.level_procedure,
2626 p_module => C_MODULE,
2627 p_msg_text => 'Begin Procedure');
2628 END IF;
2629
2630 -- Get row count
2631 v_sql := 'SELECT count(*) FROM '||p_temp_table_name;
2632
2633 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2634 FEM_ENGINES_PKG.TECH_MESSAGE(
2635 p_severity => FND_LOG.level_statement,
2636 p_module => C_MODULE,
2637 p_msg_text => 'Preview Row Count SQL = '||v_sql);
2638 END IF;
2639
2640 EXECUTE IMMEDIATE v_sql INTO x_preview_row_count;
2641
2642 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2643 FEM_ENGINES_PKG.TECH_MESSAGE(
2644 p_severity => FND_LOG.level_procedure,
2645 p_module => C_MODULE,
2646 p_msg_text => 'End Procedure');
2647 END IF;
2648 --
2649 END GetPreviewRowCount;
2650 -------------------------------------------------------------------------------
2651
2652 -------------------------------------------------------------------------------
2653 --
2654 -- PROCEDURE
2655 -- CleanOutputTable
2656 --
2657 -- DESCRIPTION
2658 -- If the output table is an account or transaction table, this procedure
2659 -- needs to first clear out all rows that the CCE did not update as
2660 -- part of its processing.
2661 --
2662 -- The reason this is needed is initially, when the output
2663 -- temporary tables were created (by the Pre_Process procedure),
2664 -- the initial data populated was most likely more than what CCE
2665 -- was going to write out to (actually, update). It was done to
2666 -- simplify the logic in the Pre_Process procedure. Without that
2667 -- simplification, the Pre_Process procedure would need to basically
2668 -- implement the logic in CCE to determine exactly which rows CCE
2669 -- was going to update --- way too big a task.
2670 --
2671 -------------------------------------------------------------------------------
2672 PROCEDURE CleanOutputTable(
2673 p_temp_table_name IN VARCHAR2,
2674 p_fact_table_name IN VARCHAR2,
2675 p_map_table_type IN VARCHAR2,
2676 p_preview_row_group IN VARCHAR2,
2677 p_preview_obj_id IN NUMBER,
2678 p_request_id IN NUMBER
2679 )
2680 -------------------------------------------------------------------------------
2681 IS
2682 --
2683 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
2684 'fem.plsql.fem_mapping_preview_util_pkg.CleanOutputTable';
2685 v_sql VARCHAR2(4000);
2686 v_map_table_type VARCHAR2(30);
2687 --
2688 BEGIN
2689 --
2690 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2691 FEM_ENGINES_PKG.TECH_MESSAGE(
2692 p_severity => FND_LOG.level_procedure,
2693 p_module => C_MODULE,
2694 p_msg_text => 'Begin Procedure');
2695 END IF;
2696
2697 -- If this is an output table and account/transaction table,
2698 -- delete data from the temporary table that the CCE did not process.
2699 IF ((p_preview_row_group IN (G_DEBIT, G_CREDIT)) AND
2700 (p_map_table_type = G_ACCT_TRANS_TYPE)) THEN
2701
2702 v_sql := 'DELETE FROM '||p_temp_table_name
2703 ||' WHERE last_updated_by_request_id <> :1'
2704 ||' AND last_updated_by_object_id <> :2';
2705
2706 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2707 FEM_ENGINES_PKG.TECH_MESSAGE(
2708 p_severity => FND_LOG.level_statement,
2709 p_module => C_MODULE,
2710 p_msg_text => 'Delete unprocessed data SQL = '||v_sql);
2711 END IF;
2712
2713 EXECUTE IMMEDIATE v_sql USING p_request_id, p_preview_obj_id;
2714
2715 END IF;
2716
2717 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2718 FEM_ENGINES_PKG.TECH_MESSAGE(
2719 p_severity => FND_LOG.level_procedure,
2720 p_module => C_MODULE,
2721 p_msg_text => 'End Procedure');
2722 END IF;
2723 --
2724 END CleanOutputTable;
2725 -------------------------------------------------------------------------------
2726
2727 -------------------------------------------------------------------------------
2728 --
2729 -- PROCEDURE
2730 -- PopulateDimensionNames
2731 --
2732 -- DESCRIPTION
2733 -- The temporary tables contain not just the dimension id/code columns
2734 -- but also the dimension name columns for each of those dimension
2735 -- id/code columns. After CCE has gone through processing (and
2736 -- we have cleaned out the output tables via CleanOutputTable),
2737 -- the temporary tables are close to its final state. All that needs
2738 -- to be done is to populate those empty dimension name columns.
2739 --
2740 -- This procedure populates those columns by issue one large update
2741 -- statement that looks like this:
2742 -- UPDATE <temporary_table> t
2743 -- SET
2744 -- t.<dim1_name_column> =
2745 -- Nvl((SELECT d1.<dim_name>
2746 -- FROM <dim1_vl_view> d1
2747 -- WHERE d1.<dim_member_column> = t.<dim1_member_column>
2748 -- -- only needed for VSR dimensions
2749 -- AND d1.value_set_id = <dim1_value_set_id>),
2750 -- DECODE(t.<dim1_member_column>,NULL,NULL,
2751 -- REPLACE('Dimension name missing: FEMDIMNAMETOKEN',
2752 -- 'FEMDIMNAMETOKEN', t.<dim1_member_column>))
2753 -- ),
2754 -- t.<dim2_name_column> =
2755 -- Nvl((SELECT d2.<dim_name>
2756 -- FROM <dim2_vl_view> d2
2757 -- WHERE d2.<dim_member_column> = t.<dim2_member_column>),
2758 -- DECODE(t.<dim2_member_column>,NULL,NULL,
2759 -- REPLACE('Dimension name missing: FEMDIMNAMETOKEN',
2760 -- 'FEMDIMNAMETOKEN', t.<dim2_member_column>))
2761 -- ),
2762 -- <etc>
2763 --
2764 -------------------------------------------------------------------------------
2765 PROCEDURE PopulateDimensionNames(
2766 p_preview_obj_def_id IN NUMBER,
2767 p_preview_row_group IN VARCHAR2,
2768 p_temp_table_name IN VARCHAR2,
2769 p_fact_table_name IN VARCHAR2,
2770 p_ledger_id IN NUMBER
2771 )
2772 -------------------------------------------------------------------------------
2773 IS
2774 --
2775 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
2776 'fem.plsql.fem_mapping_preview_util_pkg.PopulateDimensionNames';
2777 C_TOKEN CONSTANT VARCHAR2(20) := 'FEMDIMNAMETOKEN';
2778 v_return_status VARCHAR2(1);
2779 v_msg_count NUMBER;
2780 v_msg_data VARCHAR2(4000);
2781 v_global_vs_combo_id FEM_GLOBAL_VS_COMBO_DEFS.global_vs_combo_id%TYPE;
2782 v_warning FEM_DIM_TEMPLATE.template_dim_name%TYPE;
2783 v_sql VARCHAR2(32767);
2784 --
2785 CURSOR c_dim_info (cv_preview_obj_def_id NUMBER,
2786 cv_preview_row_group VARCHAR2,
2787 cv_fact_table VARCHAR2,
2788 cv_global_vs_combo_id NUMBER) IS
2789 SELECT pm.dim_member_column_name, pm.dim_name_column_name,
2790 xd.member_name_col, xd.member_vl_object_name, xd.member_col,
2791 gv.value_set_id
2792 FROM fem_alloc_preview_maps pm, fem_tab_columns_v tc,
2793 fem_xdim_dimensions xd, fem_global_vs_combo_defs gv
2794 WHERE pm.preview_obj_def_id = cv_preview_obj_def_id
2795 AND pm.preview_row_group = cv_preview_row_group
2796 AND pm.dim_member_column_name = tc.column_name
2797 AND tc.table_name = cv_fact_table
2798 AND xd.dimension_id = tc.dimension_id
2799 AND xd.dimension_id = gv.dimension_id (+)
2800 AND gv.global_vs_combo_id (+) = cv_global_vs_combo_id;
2801 --
2802 BEGIN
2803 --
2804 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2805 FEM_ENGINES_PKG.TECH_MESSAGE(
2806 p_severity => FND_LOG.level_procedure,
2807 p_module => C_MODULE,
2808 p_msg_text => 'Begin Procedure');
2809 END IF;
2810
2811 -- lookup the global value set combination id tied to the ledger
2812 v_global_vs_combo_id := FEM_DIMENSION_UTIL_PKG.global_vs_combo_id
2813 (p_encoded => FND_API.G_FALSE,
2814 x_return_status => v_return_status,
2815 x_msg_count => v_msg_count,
2816 x_msg_data => v_msg_data,
2817 p_ledger_id => p_ledger_id);
2818
2819 IF v_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2820 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2821 FEM_ENGINES_PKG.TECH_MESSAGE(
2822 p_severity => FND_LOG.level_statement,
2823 p_module => C_MODULE,
2824 p_msg_text => 'INTERNAL ERROR: Call to'
2825 ||' FEM_DIMENSION_UTIL_PKG.global_vs_combo_id'
2826 ||' failed with return status: '||v_return_status);
2827 END IF;
2828 END IF;
2829
2830 -- Get the text to store in the dimension name column if
2831 -- no dimension name was found.
2832 FND_MESSAGE.set_name('FEM','FEM_PREVIEW_DIM_NAME_MISSING');
2833 v_warning := FND_MESSAGE.get;
2834
2835 -- Start building the repeating section of the UPDATE sql to
2836 -- popluate the dimension names.
2837 FOR dim IN c_dim_info(cv_preview_obj_def_id => p_preview_obj_def_id,
2838 cv_preview_row_group => p_preview_row_group,
2839 cv_fact_table => p_fact_table_name,
2840 cv_global_vs_combo_id => v_global_vs_combo_id) LOOP
2841
2842 -- Add the comma separate between columns being updated if this is
2843 -- not the first column being updated.
2844 IF v_sql IS NOT NULL THEN
2845 v_sql := v_sql||',';
2846 END IF;
2847
2848 v_sql := v_sql||G_FACT_ALIAS||'.'||dim.dim_name_column_name||'='
2849 ||'NVL((SELECT '||G_DIM_ALIAS||'.'||dim.member_name_col
2850 ||' FROM '||dim.member_vl_object_name||' '||G_DIM_ALIAS
2851 ||' WHERE '||G_DIM_ALIAS||'.'||dim.member_col||'='
2852 ||G_FACT_ALIAS||'.'||dim.dim_member_column_name;
2853
2854 -- Add the value set filter if it applies to the dimension
2855 IF dim.value_set_id IS NOT NULL THEN
2856 v_sql := v_sql||' AND '||G_DIM_ALIAS||'.value_set_id='||dim.value_set_id;
2857 END IF;
2858
2859 v_sql := v_sql||'),'
2860 ||'DECODE('||G_FACT_ALIAS||'.'||dim.dim_member_column_name
2861 ||',NULL,NULL,'
2862 ||'REPLACE('''||v_warning||''','''||C_TOKEN||''','
2863 ||G_FACT_ALIAS||'.'||dim.dim_member_column_name||')))';
2864
2865 END LOOP;
2866
2867 -- Only run SQL if there are dimensions to be updated
2868 IF v_sql IS NOT NULL THEN
2869
2870 -- Add the beginning part of the UPDATE sql to popluate the dimension names
2871 v_sql := 'UPDATE '||p_temp_table_name||' '||G_FACT_ALIAS||' SET '||v_sql;
2872
2873 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2874 FEM_ENGINES_PKG.TECH_MESSAGE(
2875 p_severity => FND_LOG.level_statement,
2876 p_module => C_MODULE,
2877 p_msg_text => v_sql);
2878 END IF;
2879
2880 EXECUTE IMMEDIATE v_sql;
2881
2882 END IF;
2883
2884 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2885 FEM_ENGINES_PKG.TECH_MESSAGE(
2886 p_severity => FND_LOG.level_procedure,
2887 p_module => C_MODULE,
2888 p_msg_text => 'End Procedure');
2889 END IF;
2890 --
2891 END PopulateDimensionNames;
2892 -------------------------------------------------------------------------------
2893
2894 -------------------------------------------------------------------------------
2895 --
2896 -- PROCEDURE
2897 -- GetByDimParams
2898 --
2899 -- DESCRIPTION
2900 -- Get the three By Dimension specific parameters that needs to
2901 -- passed into FEM_ASSEMBLER_PREDICATE_API.Generate_Assembler_Predicate.
2902 --
2903 -- If mapping rule type is not By Dimension or preview group is not Source,
2904 -- set all parameters to NULL.
2905 --
2906 -- If mapping rule type is By Dimension and preview group is Source,
2907 -- then get the parameter values from these tables:
2908 -- FEM_ALLOC_BR_DIMENSIONS, FEM_TAB_COLUMNS_B, and FEM_XDIM_DIMENSIONS
2909 --
2910 -------------------------------------------------------------------------------
2911 PROCEDURE GetByDimParams(
2912 p_preview_obj_def_id IN NUMBER,
2913 p_preview_row_group IN VARCHAR2,
2914 p_map_obj_def_id IN NUMBER,
2915 p_map_rule_type IN VARCHAR2,
2916 p_fact_table_name IN VARCHAR2,
2917 x_by_dimension_column OUT NOCOPY VARCHAR2,
2918 x_by_dimension_id OUT NOCOPY VARCHAR2,
2919 x_by_dimension_value OUT NOCOPY VARCHAR2
2920 )
2921 -------------------------------------------------------------------------------
2922 IS
2923 --
2924 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
2925 'fem.plsql.fem_mapping_preview_util_pkg.GetByDimParams';
2926 C_BYDIM_FUNCIONCD CONSTANT VARCHAR2(10) := 'LEAFFUNC';
2927 --
2928 BEGIN
2929 --
2930 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2931 FEM_ENGINES_PKG.TECH_MESSAGE(
2932 p_severity => FND_LOG.level_procedure,
2933 p_module => C_MODULE,
2934 p_msg_text => 'Begin Procedure');
2935 END IF;
2936
2937 -- If mapping rule type is By Dimensions, get by dim param values.
2938 -- Else, set them to null.
2939 IF ((p_map_rule_type = G_BY_DIMENSION) AND
2940 (p_preview_row_group = G_SOURCE)) THEN
2941 SELECT abd.alloc_dim_col_name, xd.dimension_id,
2942 decode(xd.member_data_type_code,'NUMBER',abd.dimension_value,
2943 abd.dimension_value_char)
2944 INTO x_by_dimension_column, x_by_dimension_id, x_by_dimension_value
2945 FROM fem_alloc_br_dimensions abd, fem_tab_columns_b tc,
2946 fem_xdim_dimensions xd
2947 WHERE abd.object_definition_id = p_map_obj_def_id
2948 AND abd.function_cd = C_BYDIM_FUNCIONCD
2949 AND tc.table_name = p_fact_table_name
2950 AND abd.alloc_dim_col_name = tc.column_name
2951 AND tc.dimension_id = xd.dimension_id;
2952 ELSE
2953 x_by_dimension_column := NULL;
2954 x_by_dimension_id := NULL;
2955 x_by_dimension_value := NULL;
2956 END IF;
2957
2958 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2959 FEM_ENGINES_PKG.TECH_MESSAGE(
2960 p_severity => FND_LOG.level_procedure,
2961 p_module => C_MODULE,
2962 p_msg_text => 'End Procedure');
2963 END IF;
2964 --
2965 END GetByDimParams;
2966 -------------------------------------------------------------------------------
2967
2968 -------------------------------------------------------------------------------
2969
2970 END FEM_MAPPING_PREVIEW_UTIL_PKG;
[Home] [Help]