[Home] [Help]
PACKAGE BODY: APPS.FEM_RU_ENGINE_PVT
Source
1 PACKAGE BODY FEM_RU_ENGINE_PVT AS
2 /* $Header: FEMVRUEB.pls 120.6 2006/09/21 08:27:32 nmartine noship $ */
3
4
5 -------------------------------
6 -- Declare package constants --
7 -------------------------------
8
9 -- Constants for p_exec_status_code
10 G_EXEC_STATUS_RUNNING constant varchar2(30) := 'RUNNING';
11 G_EXEC_STATUS_SUCCESS constant varchar2(30) := 'SUCCESS';
12 G_EXEC_STATUS_ERROR_UNDO constant varchar2(30) := 'ERROR_UNDO';
13 G_EXEC_STATUS_ERROR_RERUN constant varchar2(30) := 'ERROR_RERUN';
14
15 -- Default Fetch Limit if none is specified in Profile Options
16 G_DEFAULT_FETCH_LIMIT constant number := 99999;
17
18 -- Log Level Constants
19 G_LOG_LEVEL_1 constant number := FND_LOG.Level_Statement;
20 G_LOG_LEVEL_2 constant number := FND_LOG.Level_Procedure;
21 G_LOG_LEVEL_3 constant number := FND_LOG.Level_Event;
22 G_LOG_LEVEL_4 constant number := FND_LOG.Level_Exception;
23 G_LOG_LEVEL_5 constant number := FND_LOG.Level_Error;
24 G_LOG_LEVEL_6 constant number := FND_LOG.Level_Unexpected;
25
26 -- Seeded Financial Element IDs
27 G_FIN_ELEM_ID_STATISTIC constant number := 10000;
28 G_FIN_ELEM_ID_ACTIVITY_RATE constant number := 5005;
29
30
31 ------------------------------
32 -- Declare package messages --
33 ------------------------------
34 G_EXEC_RERUN constant varchar2(30) := 'FEM_EXEC_RERUN';
35 G_EXEC_SUCCESS constant varchar2(30) := 'FEM_EXEC_SUCCESS';
36 G_UNEXPECTED_ERROR constant varchar2(30) := 'FEM_UNEXPECTED_ERROR';
37 G_NO_TABLE_CLASS_ERR constant varchar2(30) := 'FEM_NO_TABLE_CLASS_ERR';
38
39 G_ENG_BAD_CURRENCY_ERR constant varchar2(30) := 'FEM_ENG_BAD_CURRENCY_ERR';
40 G_ENG_BAD_DS_WCLAUSE_ERR constant varchar2(30) := 'FEM_ENG_BAD_DS_WCLAUSE_ERR';
41 G_ENG_BAD_HIER_DIM_ERR constant varchar2(30) := 'FEM_ENG_BAD_HIER_DIM_ERR';
42 G_ENG_BAD_LCL_VS_COMBO_ERR constant varchar2(30) := 'FEM_ENG_BAD_LCL_VS_COMBO_ERR';
43 G_ENG_BAD_OBJ_TYPE_ERR constant varchar2(30) := 'FEM_ENG_BAD_OBJ_TYPE_ERR';
44 G_ENG_BAD_RS_OBJ_TYPE_ERR constant varchar2(30) := 'FEM_ENG_BAD_RS_OBJ_TYPE_ERR';
45 G_ENG_COND_WHERE_CLAUSE_ERR constant varchar2(30) := 'FEM_ENG_COND_WHERE_CLAUSE_ERR';
46 G_ENG_CREATE_SEQUENCE_ERR constant varchar2(30) := 'FEM_ENG_CREATE_SEQUENCE_ERR';
47 G_ENG_DROP_SEQUENCE_WRN constant varchar2(30) := 'FEM_ENG_DROP_SEQUENCE_WRN';
48 G_ENG_NO_DIM_ATTR_VAL_ERR constant varchar2(30) := 'FEM_ENG_NO_DIM_ATTR_VAL_ERR';
49 G_ENG_NO_DIM_ATTR_VER_ERR constant varchar2(30) := 'FEM_ENG_NO_DIM_ATTR_VER_ERR';
50 G_ENG_NO_DIM_DTL_ERR constant varchar2(30) := 'FEM_ENG_NO_DIM_DTL_ERR';
51 G_ENG_NO_DIM_MEMBER_ERR constant varchar2(30) := 'FEM_ENG_NO_DIM_MEMBER_ERR';
52 G_ENG_NO_EXCH_RATE_ERR constant varchar2(30) := 'FEM_ENG_NO_EXCH_RATE_ERR';
53 G_ENG_NO_OBJ_ERR constant varchar2(30) := 'FEM_ENG_NO_OBJ_ERR';
54 G_ENG_NO_OBJ_DEF_DTL_ERR constant varchar2(30) := 'FEM_ENG_NO_OBJ_DEF_DTL_ERR';
55 G_ENG_NO_OBJ_DEF_ERR constant varchar2(30) := 'FEM_ENG_NO_OBJ_DEF_ERR';
56 G_ENG_NO_OBJ_DTL_ERR constant varchar2(30) := 'FEM_ENG_NO_OBJ_DTL_ERR';
57 G_ENG_NO_OUTPUT_DS_ERR constant varchar2(30) := 'FEM_ENG_NO_OUTPUT_DS_ERR';
58 G_ENG_NO_PROF_OPTION_VAL_ERR constant varchar2(30) := 'FEM_ENG_NO_PROF_OPTION_VAL_ERR';
59 G_ENG_NO_SUBMIT_OBJ_ERR constant varchar2(30) := 'FEM_ENG_NO_SUBMIT_OBJ_ERR';
60 G_ENG_REQ_POST_PROC_ERR constant varchar2(30) := 'FEM_ENG_REQ_POST_PROC_ERR';
61 G_ENG_RS_RULE_PROCESSING_TXT constant varchar2(30) := 'FEM_ENG_RS_RULE_PROCESSING_TXT';
62 G_ENG_RULE_POST_PROC_ERR constant varchar2(30) := 'FEM_ENG_RULE_POST_PROC_ERR';
63
64 G_RU_COND_NODES_LEAFS_ERR constant varchar2(30) := 'FEM_RU_COND_NODES_LEAFS_ERR';
65 G_RU_HIER_CIRC_REF_ERR constant varchar2(30) := 'FEM_RU_HIER_CIRC_REF_ERR';
66 G_RU_NO_ROLLUP_DIM_ERR constant varchar2(30) := 'FEM_RU_NO_ROLLUP_DIM_ERR';
67 G_RU_NO_COND_NODES_FOUND_ERR constant varchar2(30) := 'FEM_RU_NO_COND_NODES_FOUND_ERR';
68 G_RU_NO_ROOT_NODES_FOUND_ERR constant varchar2(30) := 'FEM_RU_NO_ROOT_NODES_FOUND_ERR';
69 G_RU_UNCOSTED_NODES_ERR constant varchar2(30) := 'FEM_RU_UNCOSTED_NODES_ERR';
70
71
72 --------------------------------------
73 -- Declare package type definitions --
74 --------------------------------------
75 t_return_status varchar2(1);
76 t_msg_count number;
77 t_msg_data varchar2(2000);
78
79 -------------------------------
80 -- Declare package variables --
81 -------------------------------
82 -- Exception variables
83 g_prg_msg varchar2(2000);
84 g_callstack varchar2(2000);
85
86 -- Bulk Fetch Limit
87 g_fetch_limit number;
88
89 -- Track Event Chains
90 g_track_event_chains boolean;
91
92 -- Track Event Chains
93 g_currency_conv_type varchar2(30);
94
95 -- Ledger Variables
96 g_ledger_dimension_id number;
97 g_ledger_curr_attr_id number;
98 g_ledger_curr_attr_version_id number;
99
100 -- Cross Ledger Table
101 g_xledger_tbl ledger_table;
102
103
104 --------------------------------
105 -- Declare package exceptions --
106 --------------------------------
107 -- General Rollup Request Exception
108 g_rollup_request_error exception;
109
110 -- Connect By Loop Exception
111 g_connect_by_loop_error exception;
112 pragma exception_init (g_connect_by_loop_error, -1436);
113
114
115 -----------------------------------------------
116 -- Declare private procedures and functions --
117 -----------------------------------------------
118 PROCEDURE Request_Prep (
119 p_obj_id in number
120 ,p_effective_date_varchar in varchar2
121 ,p_ledger_id in number
122 ,p_output_cal_period_id in number
123 ,p_dataset_grp_obj_def_id in number
124 ,p_continue_process_on_err_flg in varchar2
125 ,p_source_system_code in number
126 ,x_request_rec out nocopy request_record
127 ,x_rollup_rule_def_stmt out nocopy long
128 ,x_input_ds_b_where_clause out nocopy long
129 ,x_input_ds_q_where_clause out nocopy long
130 );
131
132 PROCEDURE Get_Object_Definition (
133 p_object_type_code in varchar2
134 ,p_object_id in number
135 ,p_effective_date in date
136 ,x_obj_def_id out nocopy number
137 );
138
139 PROCEDURE Get_Dimension_Record (
140 p_dimension_varchar_label in varchar2
141 ,x_dimension_rec out nocopy dimension_record
142 );
143
144 PROCEDURE Get_Dim_Attribute_Value (
145 p_dimension_varchar_label in varchar2
146 ,p_attribute_varchar_label in varchar2
147 ,p_member_id in number
148 ,x_dim_attribute_varchar_member out nocopy varchar2
149 ,x_date_assign_value out nocopy date
150 );
151
152 PROCEDURE Get_Ledger_Currency_Code (
153 p_ledger_id in varchar2
154 ,x_currency_code out nocopy varchar2
155 );
156
157 PROCEDURE Get_Dim_Attribute (
158 p_dimension_varchar_label in varchar2
159 ,p_attribute_varchar_label in varchar2
160 ,x_dimension_rec out nocopy dimension_record
161 ,x_attribute_id out nocopy number
162 ,x_attr_version_id out nocopy number
163 );
164
165 PROCEDURE Sql_Stmts_Prep (
166 p_request_rec in request_record
167 ,x_sql_rec out nocopy sql_record
168 );
169
170 PROCEDURE Register_Request (
171 p_request_rec in request_record
172 );
173
174 PROCEDURE Rollup_Rule (
175 p_request_rec in request_record
176 ,p_sql_rec in sql_record
177 ,p_rollup_obj_id in number
178 ,p_rollup_obj_def_id in number
179 ,p_rollup_sequence in number
180 ,p_rollup_rule_def_stmt in long
181 ,p_input_ds_b_where_clause in long
182 ,p_input_ds_q_where_clause in long
183 ,x_return_status out nocopy varchar2
184 );
185
186 PROCEDURE Rule_Prep (
187 p_request_rec in request_record
188 ,p_rollup_obj_id in number
189 ,p_rollup_obj_def_id in number
190 ,p_rollup_sequence in number
191 ,p_rollup_rule_def_stmt in long
192 ,x_rule_rec out nocopy rule_record
193 );
194
195 PROCEDURE Sql_Stmts_Build (
196 p_request_rec in request_record
197 ,p_rule_rec in rule_record
198 ,p_sql_rec in sql_record
199 ,p_input_ds_b_where_clause in long
200 ,p_input_ds_q_where_clause in long
201 ,x_find_children_stmt out nocopy long
202 ,x_rollup_parent_stmt out nocopy long
203 ,x_find_child_chains_stmt out nocopy long
204 ,x_num_of_input_rows_stmt out nocopy long
205 );
206
207 PROCEDURE Register_Rule (
208 p_request_rec in request_record
209 ,p_rule_rec in rule_record
210 );
211
212 PROCEDURE Register_Object_Definition (
213 p_request_rec in request_record
214 ,p_rule_rec in rule_record
215 ,p_obj_def_id in number
216 );
217
218 PROCEDURE Register_Table (
219 p_request_rec in request_record
220 ,p_rule_rec in rule_record
221 ,p_table_name in varchar2
222 ,p_statement_type in varchar2
223 );
224
225 PROCEDURE Create_Temp_Objects (
226 p_request_rec in request_record
227 ,p_rule_rec in rule_record
228 );
229
230 PROCEDURE Drop_Temp_Objects (
231 p_request_rec in request_record
232 ,p_rule_rec in rule_record
233 );
234
235 PROCEDURE Find_Condition_Nodes (
236 p_request_rec in request_record
237 ,p_rule_rec in rule_record
238 );
239
240 PROCEDURE Find_Root_Nodes (
241 p_request_rec in request_record
242 ,p_rule_rec in rule_record
243 );
244
245 PROCEDURE Rollup_Top_Node (
246 p_request_rec in request_record
247 ,p_rule_rec in rule_record
248 ,p_find_children_stmt in long
249 ,p_rollup_parent_stmt in long
250 ,p_find_child_chains_stmt in long
251 ,p_input_ds_b_where_clause in long
252 ,p_top_node_id in number
253 );
254
255 PROCEDURE Rollup_Parent_Node (
256 p_request_id in number
257 ,p_rollup_obj_id in number
258 ,p_hier_obj_def_id in number
259 ,p_dimension_varchar_label in varchar2
260 ,p_rollup_type_code in varchar2
261 ,p_cond_exists in boolean
262 ,p_sequence_name in varchar2
263 ,p_source_system_code in number
264 ,p_ledger_id in number
265 ,p_parent_id in number
266 ,p_parent_depth_num in number
267 ,p_statistic_basis_id in number
268 ,p_find_children_stmt in long
269 ,p_rollup_parent_stmt in long
270 ,p_find_child_chains_stmt in long
271 ,p_output_dataset_code in number
272 ,p_output_cal_period_id in number
273 ,p_exch_rate_date in date
274 ,p_functional_currency_code in varchar2
275 ,p_entered_currency_code in varchar2
276 ,p_entered_exch_rate_num in number
277 ,p_entered_exch_rate_den in number
278 ,p_user_id in number
279 ,p_login_id in number
280 );
281
282 PROCEDURE Register_Child_Chains (
283 p_request_id in number
284 ,p_rollup_obj_id in number
285 ,p_dimension_varchar_label in varchar2
286 ,p_rollup_type_code in varchar2
287 ,p_ledger_id in number
288 ,p_statistic_basis_id in number
289 ,p_find_child_chains_stmt in long
290 ,p_child_id in number
291 ,p_user_id in number
292 ,p_login_id in number
293 );
294
295 PROCEDURE Rule_Post_Proc (
296 p_request_rec in request_record
297 ,p_rule_rec in rule_record
298 ,p_num_of_input_rows_stmt in long
299 ,p_exec_status_code in varchar2
300 );
301
302 PROCEDURE Request_Post_Proc (
303 p_request_rec in request_record
304 ,p_exec_status_code in varchar2
305 );
306
307 FUNCTION Get_Lookup_Meaning (
308 p_lookup_type in varchar2
309 ,p_lookup_code in varchar2
310 )
311 RETURN varchar2;
312
313 FUNCTION Get_Object_Type_Name (
314 p_object_type_code in varchar2
315 )
316 RETURN varchar2;
317
318 PROCEDURE Get_Put_Messages (
319 p_msg_count in number
320 ,p_msg_data in varchar2
321 );
322
323
324 --------------------------------------------------------------------------------
325 -- Package bodies for functions/procedures
326 --------------------------------------------------------------------------------
327
328 /*============================================================================+
329 | PROCEDURE
330 | Rollup_Request
331 |
332 | DESCRIPTION
333 | Main engine procedure for rollup processing.
334 |
335 | SCOPE - PUBLIC
336 |
337 | MODIFICATION HISTORY
338 | nmartine 13-JUL-2004 Created
339 |
340 +============================================================================*/
341
342 PROCEDURE Rollup_Request (
343 errbuf out nocopy varchar2
344 ,retcode out nocopy varchar2
345 ,p_obj_id in number
346 ,p_effective_date in varchar2
347 ,p_ledger_id in number
348 ,p_output_cal_period_id in number
349 ,p_dataset_grp_obj_def_id in number
350 ,p_continue_process_on_err_flg in varchar2
351 ,p_source_system_code in number
352 )
353 IS
354
355 -----------------------
356 -- Declare constants --
357 -----------------------
358 l_api_name constant varchar2(30) := 'Rollup_Request';
359
360 -----------------------
361 -- Declare variables --
362 -----------------------
363 l_request_rec request_record;
364 l_sql_rec sql_record;
365
366 l_rollup_obj_id number;
367 l_rollup_obj_def_id number;
368 l_rollup_exec_status_code varchar2(30);
369 l_rollup_sequence number;
370
371 l_rollup_rule_def_stmt long;
372 l_input_ds_b_where_clause long;
373 l_input_ds_q_where_clause long;
374
375 l_completion_status boolean;
376
377 l_ruleset_status varchar2(1);
378
379 l_return_status t_return_status%TYPE;
380 l_msg_count t_msg_count%TYPE;
381 l_msg_data t_msg_data%TYPE;
382
383 l_err_code number;
384 l_err_msg varchar2(30);
385
386 ----------------------------
387 -- Declare static cursors --
388 ----------------------------
389 cursor l_ruleset_rules_csr (
390 p_request_id in number
391 ,p_ruleset_obj_id in number
392 ) is
393 select rs.child_obj_id
394 ,rs.child_obj_def_id
395 ,x.exec_status_code
396 from fem_ruleset_process_data rs
397 ,fem_pl_object_executions x
398 where rs.request_id = p_request_id
399 and rs.rule_set_obj_id = p_ruleset_obj_id
400 and x.request_id (+) = rs.request_id
401 and x.object_id (+) = rs.child_obj_id
402 and x.exec_object_definition_id (+) = rs.child_obj_def_id
403 order by rs.engine_execution_sequence;
404
405 /******************************************************************************
406 * *
407 * Rollup Request *
408 * Execution Block *
409 * *
410 ******************************************************************************/
411
412 BEGIN
413
414 -- Initialize Message Stack on FND_MSG_PUB
415 FND_MSG_PUB.Initialize;
416
417 FEM_ENGINES_PKG.Tech_Message (
418 p_severity => G_LOG_LEVEL_2
419 ,p_module => G_BLOCK||'.'||l_api_name
420 ,p_msg_text => 'BEGIN'
421 );
422
423 ------------------------------------------------
424 -- Initialize Package and Procedure Variables --
425 ------------------------------------------------
426
427 -- Ledger Variables
428 g_ledger_dimension_id := null;
429 g_ledger_curr_attr_id := null;
430 g_ledger_curr_attr_version_id := null;
431
432 -- Cross Ledger Table
433 g_xledger_tbl.DELETE;
434
435 ------------------------------------------------------------------------------
436 -- STEP 1: Request Preparation
437 ------------------------------------------------------------------------------
438 FEM_ENGINES_PKG.tech_message (
439 p_severity => G_LOG_LEVEL_1
440 ,p_module => G_BLOCK||'.'||l_api_name
441 ,p_msg_text => 'Step 1: Request Preperation'
442 );
443
444 Request_Prep (
445 p_obj_id => p_obj_id
446 ,p_effective_date_varchar => p_effective_date
447 ,p_ledger_id => p_ledger_id
448 ,p_output_cal_period_id => p_output_cal_period_id
449 ,p_dataset_grp_obj_def_id => p_dataset_grp_obj_def_id
450 ,p_continue_process_on_err_flg => p_continue_process_on_err_flg
451 ,p_source_system_code => p_source_system_code
452 ,x_request_rec => l_request_rec
453 ,x_rollup_rule_def_stmt => l_rollup_rule_def_stmt
454 ,x_input_ds_b_where_clause => l_input_ds_b_where_clause
455 ,x_input_ds_q_where_clause => l_input_ds_q_where_clause
456 );
457
458 ------------------------------------------------------------------------------
459 -- STEP 2: Dynamic SQL Statments Preparation
460 ------------------------------------------------------------------------------
461 FEM_ENGINES_PKG.Tech_Message (
462 p_severity => G_LOG_LEVEL_1
463 ,p_module => G_BLOCK||'.'||l_api_name
464 ,p_msg_text => 'Step 2: Dynamic SQL Statments Preparation'
465 );
466
467 Sql_Stmts_Prep (
468 p_request_rec => l_request_rec
469 ,x_sql_rec => l_sql_rec
470 );
471
472 ------------------------------------------------------------------------------
473 -- STEP 3: Register Request
474 ------------------------------------------------------------------------------
475 FEM_ENGINES_PKG.Tech_Message (
476 p_severity => G_LOG_LEVEL_1
477 ,p_module => G_BLOCK||'.'||l_api_name
478 ,p_msg_text => 'Step 3: Register Request'
479 );
480
481 Register_Request (
482 p_request_rec => l_request_rec
483 );
484
485 ------------------------------------------------------------------------------
486 -- STEP 4: Start Rollup Processing
487 ------------------------------------------------------------------------------
488 FEM_ENGINES_PKG.Tech_Message (
489 p_severity => G_LOG_LEVEL_1
490 ,p_module => G_BLOCK||'.'||l_api_name
491 ,p_msg_text => 'Step 4: Start Rollup Processing'
492 );
493
494 -- Initialize the rollup sequence to 0
495 -- For Single Rule Submit, the sequence will remain at 0.
496 -- For Rule Set Submit, the sequence for rule processing will be 1 to n.
497 l_rollup_sequence := 0;
498
499 if (l_request_rec.submit_obj_type_code <> 'RULE_SET') then
500
501 ----------------------------------------------------------------------------
502 -- STEP 4.1: Single Rule Submit Processing
503 ----------------------------------------------------------------------------
504 FEM_ENGINES_PKG.Tech_Message (
505 p_severity => G_LOG_LEVEL_1
506 ,p_module => G_BLOCK||'.'||l_api_name
507 ,p_msg_text => 'Step 4.1: Single Rule Submit Processing'
508 );
509
510 l_rollup_obj_id := l_request_rec.submit_obj_id;
511 l_rollup_obj_def_id := null;
512
513 ----------------------------------------------------------------------------
514 -- STEP 4.1.1: Validate Single Rule Submit
515 ----------------------------------------------------------------------------
516 FEM_ENGINES_PKG.Tech_Message (
517 p_severity => G_LOG_LEVEL_1
518 ,p_module => G_BLOCK||'.'||l_api_name
519 ,p_msg_text => 'Step 4.1.1: Single Rule Submit Processing'
520 );
521
522 FEM_RULE_SET_MANAGER.Validate_Rule_Public (
523 x_err_code => l_err_code
524 ,x_err_msg => l_err_msg
525 ,p_rule_object_id => l_rollup_obj_id
526 ,p_ds_io_def_id => l_request_rec.dataset_grp_obj_def_id
527 ,p_rule_effective_date => l_request_rec.effective_date_varchar
528 ,p_reference_period_id => l_request_rec.output_cal_period_id
529 ,p_ledger_id => l_request_rec.ledger_id
530 );
531
532 if (l_err_code <> 0) then
533 FEM_ENGINES_PKG.User_Message (
534 p_app_name => G_FEM
535 ,p_msg_name => l_err_msg
536 );
537 raise g_rollup_request_error;
538 end if;
539
540 ----------------------------------------------------------------------------
541 -- STEP 4.1.2: Rollup Single Rule
542 ----------------------------------------------------------------------------
543 FEM_ENGINES_PKG.Tech_Message (
544 p_severity => G_LOG_LEVEL_1
545 ,p_module => G_BLOCK||'.'||l_api_name
546 ,p_msg_text => 'Step 4.1.2: Rollup Single Rule'
547 );
548
549 Rollup_Rule (
550 p_request_rec => l_request_rec
551 ,p_sql_rec => l_sql_rec
552 ,p_rollup_obj_id => l_rollup_obj_id
553 ,p_rollup_obj_def_id => l_rollup_obj_def_id
554 ,p_rollup_sequence => l_rollup_sequence
555 ,p_rollup_rule_def_stmt => l_rollup_rule_def_stmt
556 ,p_input_ds_b_where_clause => l_input_ds_b_where_clause
557 ,p_input_ds_q_where_clause => l_input_ds_q_where_clause
558 ,x_return_status => l_return_status
559 );
560
561 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
562 -- For Single Rule Rollup, raise exception to end request immediately
563 -- with a completion status of ERROR, regardless of the value for
564 -- the continue_process_on_err_flg parameter.
565 raise g_rollup_request_error;
566 end if;
567
568 else
569
570 ----------------------------------------------------------------------------
571 -- STEP 4.2: Rule Set Processing
572 ----------------------------------------------------------------------------
573 FEM_ENGINES_PKG.Tech_Message (
574 p_severity => G_LOG_LEVEL_1
575 ,p_module => G_BLOCK||'.'||l_api_name
576 ,p_msg_text => 'Step 4.2: Rule Set Processing'
577 );
578
579 ----------------------------------------------------------------------------
580 -- STEP 4.2.1: Rule Set Pre Processing
581 ----------------------------------------------------------------------------
582 FEM_ENGINES_PKG.Tech_Message (
583 p_severity => G_LOG_LEVEL_1
584 ,p_module => G_BLOCK||'.'||l_api_name
585 ,p_msg_text => 'Step 4.2.1: Rule Set Pre Processing'
586 );
587
588 FEM_RULE_SET_MANAGER.FEM_Preprocess_RuleSet_PVT (
589 p_api_version => 1.0
590 ,p_init_msg_list => FND_API.G_FALSE
591 ,p_commit => FND_API.G_TRUE
592 ,p_encoded => FND_API.G_TRUE
593 ,x_return_status => l_return_status
594 ,x_msg_count => l_msg_count
595 ,x_msg_data => l_msg_data
596 ,p_orig_ruleset_object_id => l_request_rec.ruleset_obj_id
597 ,p_ds_io_def_id => l_request_rec.dataset_grp_obj_def_id
598 ,p_rule_effective_date => l_request_rec.effective_date_varchar
599 ,p_output_period_id => l_request_rec.output_cal_period_id
600 ,p_ledger_id => l_request_rec.ledger_id
601 ,p_continue_process_on_err_flg => l_request_rec.continue_process_on_err_flg
602 ,p_execution_mode => 'E' -- Engine Execution Mode
603 );
604
605 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
606 Get_Put_Messages (
607 p_msg_count => l_msg_count
608 ,p_msg_data => l_msg_data
609 );
610 raise g_rollup_request_error;
611 end if;
612
613 ----------------------------------------------------------------------------
614 -- STEP 4.2.2: Loop through all Rule Set Rules
615 ----------------------------------------------------------------------------
616 FEM_ENGINES_PKG.Tech_Message (
617 p_severity => G_LOG_LEVEL_1
618 ,p_module => G_BLOCK||'.'||l_api_name
619 ,p_msg_text => 'Step 4.2.2: Loop through all Rule Set Rules'
620 );
621
622 -- Initialize the rule set status to SUCCESS.
623 l_ruleset_status := FND_API.G_RET_STS_SUCCESS;
624
625 open l_ruleset_rules_csr (
626 p_request_id => l_request_rec.request_id
627 ,p_ruleset_obj_id => l_request_rec.ruleset_obj_id
628 );
629
630 loop
631
632 fetch l_ruleset_rules_csr
633 into l_rollup_obj_id
634 ,l_rollup_obj_def_id
635 ,l_rollup_exec_status_code;
636
637 exit when l_ruleset_rules_csr%NOTFOUND;
638
639 l_rollup_sequence := l_rollup_sequence + 1;
640
641 -- Do not process rule set rollup rules that completed successfully
642 if ( (l_rollup_exec_status_code is null)
643 or (l_rollup_exec_status_code <> 'SUCCESS') ) then
644
645 ------------------------------------------------------------------------
646 -- STEP 4.2.3: Rollup Rule Set Rule
647 ------------------------------------------------------------------------
648 FEM_ENGINES_PKG.Tech_Message (
649 p_severity => G_LOG_LEVEL_1
650 ,p_module => G_BLOCK||'.'||l_api_name
651 ,p_msg_text => 'Step 4.2.3: Rollup Rule Set Rule #'||to_char(l_rollup_sequence)
652 );
653
654 Rollup_Rule (
655 p_request_rec => l_request_rec
656 ,p_sql_rec => l_sql_rec
657 ,p_rollup_obj_id => l_rollup_obj_id
658 ,p_rollup_obj_def_id => l_rollup_obj_def_id
659 ,p_rollup_sequence => l_rollup_sequence
660 ,p_rollup_rule_def_stmt => l_rollup_rule_def_stmt
661 ,p_input_ds_b_where_clause => l_input_ds_b_where_clause
662 ,p_input_ds_q_where_clause => l_input_ds_q_where_clause
663 ,x_return_status => l_return_status
664 );
665
666 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
667 -- Set the request status to match Rollup_Rule's return status.
668 l_ruleset_status := l_return_status;
669 if (l_request_rec.continue_process_on_err_flg = 'N') then
670 -- Raise exception to end request immediately with a completion
671 -- status of ERROR.
672 raise g_rollup_request_error;
673 end if;
674 end if;
675
676 end if;
677
678 end loop;
679
680 close l_ruleset_rules_csr;
681
682 if (l_ruleset_status <> FND_API.G_RET_STS_SUCCESS) then
683 -- Raise exception to end request with a completion status of ERROR,
684 -- if the rule set status is not equal to SUCCESS.
685 raise g_rollup_request_error;
686 end if;
687
688 end if;
689
690 ------------------------------------------------------------------------------
691 -- STEP 5: Request Post Processing.
692 ------------------------------------------------------------------------------
693 FEM_ENGINES_PKG.Tech_Message (
694 p_severity => G_LOG_LEVEL_1
695 ,p_module => G_BLOCK||'.'||l_api_name
696 ,p_msg_text => 'Step 5: Request Post Processing'
697 );
698
699 Request_Post_Proc (
700 p_request_rec => l_request_rec
701 ,p_exec_status_code => G_EXEC_STATUS_SUCCESS
702 );
703
704 FEM_ENGINES_PKG.Tech_Message (
705 p_severity => G_LOG_LEVEL_2
706 ,p_module => G_BLOCK||'.'||l_api_name
707 ,p_msg_text => 'END'
708 );
709
710 EXCEPTION
711
712 when g_rollup_request_error then
713
714 if (l_ruleset_rules_csr%ISOPEN) then
715 close l_ruleset_rules_csr;
716 end if;
717
718 Request_Post_Proc (
719 p_request_rec => l_request_rec
720 ,p_exec_status_code => G_EXEC_STATUS_ERROR_UNDO
721 );
722
723 l_completion_status := FND_CONCURRENT.Set_Completion_Status('ERROR',null);
724
725 FEM_ENGINES_PKG.Tech_Message (
726 p_severity => G_LOG_LEVEL_6
727 ,p_module => G_BLOCK||'.'||l_api_name
728 ,p_msg_text => 'Rollup Request Exception'
729 );
730
731 when others then
732
733 g_prg_msg := SQLERRM;
734 g_callstack := DBMS_UTILITY.Format_Call_Stack;
735
736 if (l_ruleset_rules_csr%ISOPEN) then
737 close l_ruleset_rules_csr;
738 end if;
739
740 Request_Post_Proc (
741 p_request_rec => l_request_rec
742 ,p_exec_status_code => G_EXEC_STATUS_ERROR_UNDO
743 );
744
745 l_completion_status := FND_CONCURRENT.Set_Completion_Status('ERROR',null);
746
747 FEM_ENGINES_PKG.Tech_Message (
748 p_severity => G_LOG_LEVEL_6
749 ,p_module => G_BLOCK||'.'||l_api_name||'.Unexpected_Exception'
750 ,p_msg_text => g_prg_msg
751 );
752
753 FEM_ENGINES_PKG.Tech_Message (
754 p_severity => G_LOG_LEVEL_6
755 ,p_module => G_BLOCK||'.'||l_api_name||'.Unexpected_Exception'
756 ,p_msg_text => g_callstack
757 );
758
759 FEM_ENGINES_PKG.User_Message (
760 p_app_name => G_FEM
761 ,p_msg_name => G_UNEXPECTED_ERROR
762 ,p_token1 => 'ERR_MSG'
763 ,p_value1 => g_prg_msg
764 );
765
766 END Rollup_Request;
767
768
769
770 /*============================================================================+
771 | PROCEDURE
772 | Request_Prep
773 |
774 | DESCRIPTION
775 | Rollup Request Preparation. Populates the request record and parameters
776 | that are common to all rollup rules that will be processed.
777 |
778 | SCOPE - PRIVATE
779 |
780 +============================================================================*/
781
782 PROCEDURE Request_Prep (
783 p_obj_id in number
784 ,p_effective_date_varchar in varchar2
785 ,p_ledger_id in number
786 ,p_output_cal_period_id in number
787 ,p_dataset_grp_obj_def_id in number
788 ,p_continue_process_on_err_flg in varchar2
789 ,p_source_system_code in number
790 ,x_request_rec out nocopy request_record
791 ,x_rollup_rule_def_stmt out nocopy long
792 ,x_input_ds_b_where_clause out nocopy long
793 ,x_input_ds_q_where_clause out nocopy long
794 )
795 IS
796
797 l_api_name constant varchar2(30) := 'Request_Prep';
798
799 l_object_name varchar2(150);
800 l_folder_name varchar2(150);
801
802 l_dimension_varchar_label varchar2(30);
803 l_dummy_varchar varchar2(30);
804 l_dummy_date date;
805
806 l_return_status t_return_status%TYPE;
807 l_msg_count t_msg_count%TYPE;
808 l_msg_data t_msg_data%TYPE;
809
810 l_request_prep_error exception;
811
812 BEGIN
813
814 FEM_ENGINES_PKG.Tech_Message (
815 p_severity => G_LOG_LEVEL_2
816 ,p_module => G_BLOCK||'.'||l_api_name
817 ,p_msg_text => 'BEGIN'
818 );
819
820 ------------------------------------------------------------
821 -- Set all the Submitted Parameters on the Request Record --
822 ------------------------------------------------------------
823 x_request_rec.submit_obj_id := p_obj_id;
824 x_request_rec.effective_date_varchar := p_effective_date_varchar;
825 x_request_rec.effective_date :=
826 FND_DATE.Canonical_To_Date(p_effective_date_varchar);
827 x_request_rec.ledger_id := p_ledger_id;
828 x_request_rec.output_cal_period_id := p_output_cal_period_id;
829 x_request_rec.dataset_grp_obj_def_id := p_dataset_grp_obj_def_id;
830 x_request_rec.continue_process_on_err_flg := p_continue_process_on_err_flg;
831 x_request_rec.source_system_code := p_source_system_code;
832
833 -------------------------------------------------------------
834 -- Set all the FND Global Parameters on the Request Record --
835 -------------------------------------------------------------
836 x_request_rec.user_id := FND_GLOBAL.user_id;
837 x_request_rec.login_id := FND_GLOBAL.login_id;
838 x_request_rec.request_id := FND_GLOBAL.conc_request_id;
839 x_request_rec.resp_id := FND_GLOBAL.resp_id;
840 x_request_rec.pgm_id := FND_GLOBAL.conc_program_id;
841 x_request_rec.pgm_app_id := FND_GLOBAL.prog_appl_id;
842
843 ---------------------------------------------------------
844 -- Get the limit for bulk fetches from profile options --
845 ---------------------------------------------------------
846 g_fetch_limit := nvl (
847 FND_PROFILE.Value_Specific (
848 'FEM_BULK_FETCH_LIMIT'
849 ,x_request_rec.user_id
850 ,x_request_rec.resp_id
851 ,x_request_rec.pgm_app_id
852 )
853 ,G_DEFAULT_FETCH_LIMIT
854 );
855
856 ----------------------------------------------------------
857 -- Get the track event chains flag from profile options --
858 ----------------------------------------------------------
859 g_track_event_chains :=
860 ('Y' =
861 FND_PROFILE.Value_Specific (
862 'FEM_TRACK_EVENT_CHAINS'
863 ,x_request_rec.user_id
864 ,x_request_rec.resp_id
865 ,x_request_rec.pgm_app_id)
866 );
867
868 -----------------------------------------------------------
869 -- Get the currency conversion type from profile options --
870 -----------------------------------------------------------
871 g_currency_conv_type :=
872 FND_PROFILE.Value_Specific (
873 'FEM_CURRENCY_CONVERSION_TYPE'
874 ,x_request_rec.user_id
875 ,x_request_rec.resp_id
876 ,x_request_rec.pgm_app_id
877 );
878
879 if (g_currency_conv_type is null) then
880 FEM_ENGINES_PKG.User_Message (
881 p_app_name => G_FEM
882 ,p_msg_name => G_ENG_NO_PROF_OPTION_VAL_ERR
883 ,p_token1 => 'PROFILE_OPTION_NAME'
884 ,p_value1 => 'FEM_CURRENCY_CONVERSION_TYPE'
885 );
886 raise l_request_prep_error;
887 end if;
888
889
890 ------------------------------------------------------------------------------
891 -- Get the object type code to determine if this is a rule set or single
892 -- rule submit submission
893 ------------------------------------------------------------------------------
894 begin
895 select object_type_code
896 ,local_vs_combo_id
897 into x_request_rec.submit_obj_type_code
898 ,x_request_rec.local_vs_combo_id
899 from fem_object_catalog_b
900 where object_id = x_request_rec.submit_obj_id;
901 exception
902 when others then
903 FEM_ENGINES_PKG.User_Message (
904 p_app_name => G_FEM
905 ,p_msg_name => G_ENG_NO_SUBMIT_OBJ_ERR
906 ,p_token1 => 'OBJECT_ID'
907 ,p_value1 => x_request_rec.submit_obj_id
908 );
909 raise l_request_prep_error;
910 end;
911
912 if (x_request_rec.submit_obj_type_code = 'RULE_SET') then
913
914 x_request_rec.ruleset_obj_id := x_request_rec.submit_obj_id;
915
916 begin
917 select object_name
918 into x_request_rec.ruleset_obj_name
919 from fem_object_catalog_vl
920 where object_id = x_request_rec.ruleset_obj_id;
921 exception
922 when others then
923 FEM_ENGINES_PKG.User_Message (
924 p_app_name => G_FEM
925 ,p_msg_name => G_ENG_NO_OBJ_ERR
926 ,p_token1 => 'OBJECT_TYPE_MEANING'
927 ,p_value1 => Get_Object_Type_Name('RULE_SET')
928 ,p_token2 => 'OBJECT_ID'
929 ,p_value2 => x_request_rec.ruleset_obj_id
930 );
931 raise l_request_prep_error;
932 end;
933
934 Get_Object_Definition (
935 p_object_type_code => x_request_rec.submit_obj_type_code
936 ,p_object_id => x_request_rec.ruleset_obj_id
937 ,p_effective_date => x_request_rec.effective_date
938 ,x_obj_def_id => x_request_rec.ruleset_obj_def_id
939 );
940
941 -- Set the Object Type Code for the Rollup Process
942 begin
943 select rule_set_object_type_code
944 into x_request_rec.rollup_obj_type_code
945 from fem_rule_sets
946 where rule_set_obj_def_id = x_request_rec.ruleset_obj_def_id;
947 exception
948 when others then
949 FEM_ENGINES_PKG.User_Message (
950 p_app_name => G_FEM
951 ,p_msg_name => G_ENG_NO_OBJ_DEF_DTL_ERR
952 ,p_token1 => 'TABLE_NAME'
953 ,p_value1 => 'FEM_RULE_SETS'
954 ,p_token2 => 'OBJECT_TYPE_MEANING'
955 ,p_value2 => Get_Object_Type_Name('RULE_SET')
956 ,p_token3 => 'OBJECT_ID'
957 ,p_value3 => x_request_rec.ruleset_obj_id
958 ,p_token4 => 'OBJECT_DEF_ID'
959 ,p_value4 => x_request_rec.ruleset_obj_def_id
960 );
961 raise l_request_prep_error;
962 end;
963
964 else
965
966 x_request_rec.ruleset_obj_id := null;
967 x_request_rec.ruleset_obj_name := null;
968 x_request_rec.ruleset_obj_def_id := null;
969
970 -- Set the Object Type Code for the Rollup Process
971 x_request_rec.rollup_obj_type_code := x_request_rec.submit_obj_type_code;
972
973 end if;
974
975 ------------------------------------------------------------------------------
976 -- Set rollup parameters depending on the Rollup Rule's Object Type Code
977 ------------------------------------------------------------------------------
978 if (x_request_rec.rollup_obj_type_code = 'COUC_ROLLUP') then
979
980 l_dimension_varchar_label := 'COST_OBJECT';
981 x_request_rec.rollup_type_code := 'COST';
982 x_request_rec.rollup_rule_def_table := 'PFT_COUC_ROLLUP_RULES';
983 x_rollup_rule_def_stmt :=
984 ' select cost_object_hier_obj_id'||
985 ' ,condition_obj_id'||
986 ' ,currency_code'||
987 ' ,null'||
988 ' from pft_couc_rollup_rules'||
989 ' where couc_rollup_obj_def_id = :b_rollup_obj_def_id';
990
991 elsif (x_request_rec.rollup_obj_type_code = 'ACT_COST_ROLLUP') then
992
993 l_dimension_varchar_label := 'ACTIVITY';
994 x_request_rec.rollup_type_code := 'COST';
995 x_request_rec.rollup_rule_def_table := 'PFT_ACTIVITY_COST_RU';
996 x_rollup_rule_def_stmt :=
997 ' select activity_hier_obj_id'||
998 ' ,condition_obj_id'||
999 ' ,currency_code'||
1000 ' ,null'||
1001 ' from pft_activity_cost_ru'||
1002 ' where cost_rollup_obj_def_id = :b_rollup_obj_def_id';
1003
1004 elsif (x_request_rec.rollup_obj_type_code = 'ACT_STAT_ROLLUP') then
1005
1006 l_dimension_varchar_label := 'ACTIVITY';
1007 x_request_rec.rollup_type_code := 'STAT';
1008 x_request_rec.rollup_rule_def_table := 'PFT_ACTIVITY_STAT_RU';
1009 x_rollup_rule_def_stmt :=
1010 ' select activity_hier_obj_id'||
1011 ' ,condition_obj_id'||
1012 ' ,''STAT'''||
1013 ' ,statistic_basis_id'||
1014 ' from pft_activity_stat_ru'||
1015 ' where stat_rollup_obj_def_id = :b_rollup_obj_def_id';
1016
1017 else
1018
1019 FEM_ENGINES_PKG.User_Message (
1020 p_app_name => G_FEM
1021 ,p_msg_name => G_ENG_BAD_OBJ_TYPE_ERR
1022 ,p_token1 => 'OBJECT_TYPE_CODE'
1023 ,p_value1 => x_request_rec.rollup_obj_type_code
1024 );
1025 raise l_request_prep_error;
1026
1027 end if;
1028
1029 ------------------------------------------------------------------------------
1030 -- Get Dimension Metadata
1031 ------------------------------------------------------------------------------
1032 Get_Dimension_Record (
1033 p_dimension_varchar_label => l_dimension_varchar_label
1034 ,x_dimension_rec => x_request_rec.dimension_rec
1035 );
1036
1037 ------------------------------------------------------------------------------
1038 -- Check that FEM_BALANCES has the ABM_LEDGER table classification.
1039 ------------------------------------------------------------------------------
1040 -- Check added with bug 4510785
1041 begin
1042 select 'Y'
1043 into l_dummy_varchar
1044 from fem_table_class_assignmt_v
1045 where table_classification_code = 'ABM_LEDGER'
1046 and table_name = 'FEM_BALANCES';
1047 exception
1048 when no_data_found then
1049 FEM_ENGINES_PKG.User_Message (
1050 p_app_name => G_FEM
1051 ,p_msg_name => G_NO_TABLE_CLASS_ERR
1052 ,p_token1 => 'TABLE_NAME'
1053 ,p_value1 => 'FEM_BALANCES'
1054 ,p_token2 => 'TABLE_CLASSIFICATION'
1055 ,p_value2 => Get_Lookup_Meaning('FEM_TABLE_CLASSIFICATION_DSC','ABM_LEDGER')
1056 );
1057 raise l_request_prep_error;
1058 end;
1059
1060 ------------------------------------------------------------------------------
1061 -- Validate the Processing Key on FEM_BALANCES to make sure that it can
1062 -- handle rollup processing on the appropriate composite dimension.
1063 ------------------------------------------------------------------------------
1064 -- Validation added with bug 4475839
1065 FEM_SETUP_PKG.Validate_Proc_Key (
1066 p_api_version => 1.0
1067 ,p_init_msg_list => FND_API.G_FALSE
1068 ,p_commit => FND_API.G_FALSE
1069 ,p_encoded => FND_API.G_TRUE
1070 ,x_return_status => l_return_status
1071 ,x_msg_count => l_msg_count
1072 ,x_msg_data => l_msg_data
1073 ,p_dimension_varchar_label => l_dimension_varchar_label
1074 ,p_table_name => 'FEM_BALANCES'
1075 );
1076
1077 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1078 Get_Put_Messages (
1079 p_msg_count => l_msg_count
1080 ,p_msg_data => l_msg_data
1081 );
1082 raise l_request_prep_error;
1083 end if;
1084
1085 ------------------------------------------------------------------------------
1086 -- Get the Source System Code for PFT if a null param value was passed.
1087 ------------------------------------------------------------------------------
1088 if (x_request_rec.source_system_code is null) then
1089
1090 -- For all Rollup Processing default the Source System Display Code to PFT
1091 begin
1092 select source_system_code
1093 into x_request_rec.source_system_code
1094 from fem_source_systems_b
1095 where source_system_display_code = G_PFT_SOURCE_SYSTEM_DC;
1096 exception
1097 when others then
1098 FEM_ENGINES_PKG.User_Message (
1099 p_app_name => G_FEM
1100 ,p_msg_name => G_ENG_NO_DIM_MEMBER_ERR
1101 ,p_token1 => 'TABLE_NAME'
1102 ,p_value1 => 'FEM_SOURCE_SYSTEMS_B'
1103 ,p_token2 => 'MEMBER_DISPLAY_CODE'
1104 ,p_value2 => G_PFT_SOURCE_SYSTEM_DC
1105 );
1106 raise l_request_prep_error;
1107 end;
1108
1109 end if;
1110
1111 ------------------------------------------------------------------------------
1112 -- Get the Dataset Group Object ID
1113 ------------------------------------------------------------------------------
1114 begin
1115 select object_id
1116 into x_request_rec.dataset_grp_obj_id
1117 from fem_object_definition_b
1118 where object_definition_id = x_request_rec.dataset_grp_obj_def_id;
1119 exception
1120 when others then
1121 FEM_ENGINES_PKG.User_Message (
1122 p_app_name => G_FEM
1123 ,p_msg_name => G_ENG_NO_OBJ_ERR
1124 ,p_token1 => 'OBJECT_TYPE_MEANING'
1125 ,p_value1 => Get_Object_Type_Name('DATASET_IO_DEFINITION')
1126 ,p_token2 => 'OBJECT_ID'
1127 ,p_value2 => x_request_rec.dataset_grp_obj_id
1128 );
1129 raise l_request_prep_error;
1130 end;
1131
1132 ------------------------------------------------------------------------------
1133 -- Get the Output Dataset Code
1134 ------------------------------------------------------------------------------
1135 begin
1136 select output_dataset_code
1137 into x_request_rec.output_dataset_code
1138 from fem_ds_input_output_defs
1139 where dataset_io_obj_def_id = x_request_rec.dataset_grp_obj_def_id;
1140 exception
1141 when others then
1142
1143 select obj.object_name
1144 ,f.folder_name
1145 into l_object_name
1146 ,l_folder_name
1147 from fem_object_catalog_vl obj
1148 ,fem_folders_vl f
1149 where obj.object_id = x_request_rec.dataset_grp_obj_id
1150 and f.folder_id = obj.folder_id;
1151
1152 FEM_ENGINES_PKG.User_Message (
1153 p_app_name => G_FEM
1154 ,p_msg_name => G_ENG_NO_OUTPUT_DS_ERR
1155 ,p_token1 => 'FOLDER_NAME'
1156 ,p_value1 => l_folder_name
1157 ,p_token2 => 'DATASET_GRP_NAME'
1158 ,p_value2 => l_object_name
1159 );
1160 raise l_request_prep_error;
1161 end;
1162
1163 ------------------------------------------------------------------------------
1164 -- Call the Where Clause Generator for source data in FEM_BALANCES
1165 ------------------------------------------------------------------------------
1166 FEM_DS_WHERE_CLAUSE_GENERATOR.FEM_Gen_DS_WClause_PVT (
1167 p_api_version => 1.0
1168 ,p_init_msg_list => FND_API.G_FALSE
1169 ,p_encoded => FND_API.G_TRUE
1170 ,x_return_status => l_return_status
1171 ,x_msg_count => l_msg_count
1172 ,x_msg_data => l_msg_data
1173 ,p_ds_io_def_id => x_request_rec.dataset_grp_obj_def_id
1174 ,p_output_period_id => x_request_rec.output_cal_period_id
1175 ,p_table_name => 'FEM_BALANCES'
1176 ,p_table_alias => 'B'
1177 ,p_ledger_id => x_request_rec.ledger_id
1178 ,p_where_clause => x_input_ds_b_where_clause
1179 );
1180
1181 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1182 Get_Put_Messages (
1183 p_msg_count => l_msg_count
1184 ,p_msg_data => l_msg_data
1185 );
1186 raise l_request_prep_error;
1187 end if;
1188
1189 if (x_input_ds_b_where_clause is null) then
1190 FEM_ENGINES_PKG.User_Message (
1191 p_app_name => G_FEM
1192 ,p_msg_name => G_ENG_BAD_DS_WCLAUSE_ERR
1193 ,p_token1 => 'DATASET_GRP_OBJ_DEF_ID'
1194 ,p_value1 => x_request_rec.dataset_grp_obj_def_id
1195 ,p_token2 => 'OUTPUT_CAL_PERIOD_ID'
1196 ,p_value2 => x_request_rec.output_cal_period_id
1197 ,p_token3 => 'TABLE_NAME'
1198 ,p_value3 => 'FEM_BALANCES'
1199 ,p_token4 => 'LEDGER_ID'
1200 ,p_value4 => x_request_rec.ledger_id
1201 );
1202 raise l_request_prep_error;
1203 end if;
1204
1205 ------------------------------------------------------------------------------
1206 -- Call the Where Clause Generator for source data in FEM_COST_OBJECT_HIER_QTY
1207 ------------------------------------------------------------------------------
1208 if (x_request_rec.dimension_rec.dimension_varchar_label = 'COST_OBJECT') then
1209
1210 FEM_DS_WHERE_CLAUSE_GENERATOR.FEM_Gen_DS_WClause_PVT (
1211 p_api_version => 1.0
1212 ,p_init_msg_list => FND_API.G_FALSE
1213 ,p_encoded => FND_API.G_TRUE
1214 ,x_return_status => l_return_status
1215 ,x_msg_count => l_msg_count
1216 ,x_msg_data => l_msg_data
1217 ,p_ds_io_def_id => x_request_rec.dataset_grp_obj_def_id
1218 ,p_output_period_id => x_request_rec.output_cal_period_id
1219 ,p_table_name => 'FEM_COST_OBJECT_HIER_QTY'
1220 ,p_table_alias => 'Q'
1221 ,p_ledger_id => x_request_rec.ledger_id
1222 ,p_where_clause => x_input_ds_q_where_clause
1223 );
1224
1225 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1226 Get_Put_Messages (
1227 p_msg_count => l_msg_count
1228 ,p_msg_data => l_msg_data
1229 );
1230 raise l_request_prep_error;
1231 end if;
1232
1233 if (x_input_ds_q_where_clause is null) then
1234 FEM_ENGINES_PKG.User_Message (
1235 p_app_name => G_FEM
1236 ,p_msg_name => G_ENG_BAD_DS_WCLAUSE_ERR
1237 ,p_token1 => 'DATASET_GRP_OBJ_DEF_ID'
1238 ,p_value1 => x_request_rec.dataset_grp_obj_def_id
1239 ,p_token2 => 'OUTPUT_CAL_PERIOD_ID'
1240 ,p_value2 => x_request_rec.output_cal_period_id
1241 ,p_token3 => 'TABLE_NAME'
1242 ,p_value3 => 'FEM_COST_OBJECT_HIER_QTY'
1243 ,p_token4 => 'LEDGER_ID'
1244 ,p_value4 => x_request_rec.ledger_id
1245 );
1246 raise l_request_prep_error;
1247 end if;
1248
1249 end if;
1250
1251 ------------------------------------------------------------------------------
1252 -- Get Ledger information for Cost and Statistic Rollups
1253 ------------------------------------------------------------------------------
1254 Get_Dim_Attribute_Value (
1255 p_dimension_varchar_label => 'LEDGER'
1256 ,p_attribute_varchar_label => 'ENTERED_CRNCY_ENABLE_FLAG'
1257 ,p_member_id => x_request_rec.ledger_id
1258 ,x_dim_attribute_varchar_member => x_request_rec.entered_currency_flag
1259 ,x_date_assign_value => l_dummy_date
1260 );
1261
1262 if (x_request_rec.rollup_type_code = 'COST') then
1263
1264 Get_Ledger_Currency_Code (
1265 p_ledger_id => x_request_rec.ledger_id
1266 ,x_currency_code => x_request_rec.functional_currency_code
1267 );
1268
1269 elsif (x_request_rec.rollup_type_code = 'STAT') then
1270
1271 x_request_rec.functional_currency_code := 'STAT';
1272
1273 end if;
1274
1275 ------------------------------------------------------------------------------
1276 -- Set the exchange rate date
1277 ------------------------------------------------------------------------------
1278 if (x_request_rec.entered_currency_flag = 'Y') then
1279
1280 Get_Dim_Attribute_Value (
1281 p_dimension_varchar_label => 'CAL_PERIOD'
1282 ,p_attribute_varchar_label => 'CAL_PERIOD_END_DATE'
1283 ,p_member_id => x_request_rec.output_cal_period_id
1284 ,x_dim_attribute_varchar_member => l_dummy_varchar
1285 ,x_date_assign_value => x_request_rec.exch_rate_date
1286 );
1287
1288 else
1289
1290 x_request_rec.exch_rate_date := null;
1291
1292 end if;
1293
1294 -- Log all Request Record Parameters if we have low level debugging
1295 if ( FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) ) then
1296
1297 FEM_ENGINES_PKG.Tech_Message (
1298 p_severity => G_LOG_LEVEL_1
1299 ,p_module => G_BLOCK||'.'||l_api_name||'.x_request_rec'
1300 ,p_msg_text =>
1301 ' dataset_grp_obj_def_id='||x_request_rec.dataset_grp_obj_def_id||
1302 ' dataset_grp_obj_id='||x_request_rec.dataset_grp_obj_id||
1303 ' dimension_varchar_label='||x_request_rec.dimension_rec.dimension_varchar_label||
1304 ' effective_date='||FND_DATE.date_to_chardate(x_request_rec.effective_date)||
1305 ' entered_currency_flag='||x_request_rec.entered_currency_flag||
1306 ' exch_rate_date='||FND_DATE.date_to_chardate(x_request_rec.exch_rate_date)||
1307 ' functional_currency_code='||x_request_rec.functional_currency_code||
1308 ' ledger_id='||x_request_rec.ledger_id||
1309 ' local_vs_combo_id='||x_request_rec.local_vs_combo_id||
1310 ' login_id='||x_request_rec.login_id||
1311 ' output_cal_period_id='||x_request_rec.output_cal_period_id||
1312 ' output_dataset_code='||x_request_rec.output_dataset_code||
1313 ' pgm_app_id='||x_request_rec.pgm_app_id||
1314 ' pgm_id='||x_request_rec.pgm_id||
1315 ' resp_id='||x_request_rec.resp_id||
1316 ' request_id='||x_request_rec.request_id||
1317 ' rollup_obj_type_code='||x_request_rec.rollup_obj_type_code||
1318 ' rollup_type_code='||x_request_rec.rollup_type_code||
1319 ' ruleset_obj_def_id='||x_request_rec.ruleset_obj_def_id||
1320 ' ruleset_obj_id='||x_request_rec.ruleset_obj_id||
1321 ' ruleset_obj_name='||x_request_rec.ruleset_obj_name||
1322 ' source_system_code='||x_request_rec.source_system_code||
1323 ' submit_obj_id='||x_request_rec.submit_obj_id||
1324 ' submit_obj_type_code='||x_request_rec.submit_obj_type_code||
1325 ' user_id='||x_request_rec.user_id
1326 );
1327
1328 end if;
1329
1330 FEM_ENGINES_PKG.Tech_Message (
1331 p_severity => G_LOG_LEVEL_2
1332 ,p_module => G_BLOCK||'.'||l_api_name
1333 ,p_msg_text => 'END'
1334 );
1335
1336 EXCEPTION
1337
1338 when l_request_prep_error then
1339
1340 FEM_ENGINES_PKG.Tech_Message (
1341 p_severity => G_LOG_LEVEL_6
1342 ,p_module => G_BLOCK||'.'||l_api_name
1343 ,p_msg_text => 'Request Preperation Exception'
1344 );
1345
1346 raise g_rollup_request_error;
1347
1348 END Request_Prep;
1349
1350
1351
1352 /*============================================================================+
1353 | PROCEDURE
1354 | Get_Object_Definition
1355 |
1356 | DESCRIPTION
1357 | Get the object definition id for the specified object type code, object id
1358 | and effective date.
1359 |
1360 | SCOPE - PRIVATE
1361 |
1362 +============================================================================*/
1363
1364 PROCEDURE Get_Object_Definition (
1365 p_object_type_code in varchar2
1366 ,p_object_id in number
1367 ,p_effective_date in date
1368 ,x_obj_def_id out nocopy number
1369 )
1370 IS
1371
1372 l_api_name constant varchar2(30) := 'Get_Object_Definition';
1373
1374 l_object_name varchar2(150);
1375 l_object_type_code varchar2(30);
1376
1377 BEGIN
1378
1379 FEM_ENGINES_PKG.Tech_Message (
1380 p_severity => G_LOG_LEVEL_2
1381 ,p_module => G_BLOCK||'.'||l_api_name
1382 ,p_msg_text => 'BEGIN'
1383 );
1384
1385 select d.object_definition_id
1386 into x_obj_def_id
1387 from fem_object_definition_b d
1388 ,fem_object_catalog_b o
1389 where o.object_id = p_object_id
1390 and o.object_type_code = p_object_type_code
1391 and d.object_id = o.object_id
1392 and p_effective_date between d.effective_start_date and d.effective_end_date
1393 and d.old_approved_copy_flag = 'N';
1394
1395 FEM_ENGINES_PKG.Tech_Message (
1396 p_severity => G_LOG_LEVEL_2
1397 ,p_module => G_BLOCK||'.'||l_api_name
1398 ,p_msg_text => 'END'
1399 );
1400
1401 EXCEPTION
1402
1403 when no_data_found then
1404
1405 select object_name
1406 ,object_type_code
1407 into l_object_name
1408 ,l_object_type_code
1409 from fem_object_catalog_vl
1410 where object_id = p_object_id;
1411
1412 FEM_ENGINES_PKG.User_Message (
1413 p_app_name => G_FEM
1414 ,p_msg_name => G_ENG_NO_OBJ_DEF_ERR
1415 ,p_token1 => 'OBJECT_TYPE_MEANING'
1416 ,p_value1 => Get_Object_Type_Name(l_object_type_code)
1417 ,p_token2 => 'OBJECT_NAME'
1418 ,p_value2 => l_object_name
1419 ,p_token3 => 'EFFECTIVE_DATE'
1420 ,p_value3 => FND_DATE.date_to_chardate(p_effective_date)
1421 );
1422
1423 raise g_rollup_request_error;
1424
1425 END Get_Object_Definition;
1426
1427
1428
1429 /*============================================================================+
1430 | PROCEDURE
1431 | Get_Dimension_Record
1432 |
1433 | DESCRIPTION
1434 | Validates the input dimension and returns a dimension record containing
1435 | dimension metadata
1436 |
1437 | SCOPE - PRIVATE
1438 |
1439 +============================================================================*/
1440
1441 PROCEDURE Get_Dimension_Record (
1442 p_dimension_varchar_label in varchar2
1443 ,x_dimension_rec out nocopy dimension_record
1444 )
1445 IS
1446
1447 l_api_name constant varchar2(30) := 'Get_Dimension_Record';
1448
1449 BEGIN
1450
1451 FEM_ENGINES_PKG.Tech_Message (
1452 p_severity => G_LOG_LEVEL_2
1453 ,p_module => G_BLOCK||'.'||l_api_name
1454 ,p_msg_text => 'BEGIN'
1455 );
1456
1457 select dimension_id
1458 ,dimension_varchar_label
1459 ,composite_dimension_flag
1460 ,member_col
1461 ,member_b_table_name
1462 ,attribute_table_name as attr_table
1463 ,hierarchy_table_name as hier_table
1464 ,null as hier_rollup_table
1465 ,hier_versioning_type_code
1466 into x_dimension_rec
1467 from fem_xdim_dimensions_vl
1468 where dimension_varchar_label = p_dimension_varchar_label;
1469
1470 -- Manually set the hierarchy rollup tables
1471 if (x_dimension_rec.dimension_varchar_label = 'COST_OBJECT') then
1472
1473 -- Used for all COUC Rollups
1474 x_dimension_rec.hier_rollup_table := 'FEM_RU_COST_OBJ_HIER_T';
1475
1476 elsif (x_dimension_rec.dimension_varchar_label = 'ACTIVITY') then
1477
1478 -- Used for Activity Cost/Statistic Rollups that have a Condition
1479 x_dimension_rec.hier_rollup_table := 'FEM_RU_ACTIVITIES_HIER_T';
1480
1481 end if;
1482
1483 FEM_ENGINES_PKG.Tech_Message (
1484 p_severity => G_LOG_LEVEL_2
1485 ,p_module => G_BLOCK||'.'||l_api_name
1486 ,p_msg_text => 'END'
1487 );
1488
1489 EXCEPTION
1490
1491 when no_data_found then
1492
1493 FEM_ENGINES_PKG.User_Message (
1494 p_app_name => G_FEM
1495 ,p_msg_name => G_ENG_NO_DIM_DTL_ERR
1496 ,p_token1 => 'DIMENSION_VARCHAR_LABEL'
1497 ,p_value1 => p_dimension_varchar_label
1498 );
1499
1500 raise g_rollup_request_error;
1501
1502 END Get_Dimension_Record;
1503
1504
1505
1506 /*============================================================================+
1507 | PROCEDURE
1508 | Get_Dim_Attribute_Value
1509 |
1510 | DESCRIPTION
1511 | Get Dimension Attribute Value for the specified dimension label, attribute
1512 | label and member id.
1513 |
1514 | SCOPE - PRIVATE
1515 |
1516 +============================================================================*/
1517
1518 PROCEDURE Get_Dim_Attribute_Value (
1519 p_dimension_varchar_label in varchar2
1520 ,p_attribute_varchar_label in varchar2
1521 ,p_member_id in number
1522 ,x_dim_attribute_varchar_member out nocopy varchar2
1523 ,x_date_assign_value out nocopy date
1524 )
1525 IS
1526
1527 l_api_name constant varchar2(30) := 'Get_Dim_Attribute_Value';
1528
1529 l_dimension_rec dimension_record;
1530
1531 l_dimension_id number;
1532 l_attribute_id number;
1533 l_attr_version_id number;
1534
1535 l_get_dim_attr_val_error exception;
1536
1537 BEGIN
1538
1539 FEM_ENGINES_PKG.Tech_Message (
1540 p_severity => G_LOG_LEVEL_2
1541 ,p_module => G_BLOCK||'.'||l_api_name
1542 ,p_msg_text => 'BEGIN'
1543 );
1544
1545 Get_Dim_Attribute (
1546 p_dimension_varchar_label => p_dimension_varchar_label
1547 ,p_attribute_varchar_label => p_attribute_varchar_label
1548 ,x_dimension_rec => l_dimension_rec
1549 ,x_attribute_id => l_attribute_id
1550 ,x_attr_version_id => l_attr_version_id
1551 );
1552
1553 begin
1554 execute immediate
1555 ' select dim_attribute_varchar_member'||
1556 ' ,date_assign_value'||
1557 ' from '||l_dimension_rec.attr_table||
1558 ' where attribute_id = :b_attribute_id'||
1559 ' and version_id = :b_attr_version_id'||
1560 ' and '||l_dimension_rec.member_col||' = :b_member_id'
1561 into x_dim_attribute_varchar_member
1562 ,x_date_assign_value
1563 using l_attribute_id
1564 ,l_attr_version_id
1565 ,p_member_id;
1566 exception
1567 when others then
1568 FEM_ENGINES_PKG.User_Message (
1569 p_app_name => G_FEM
1570 ,p_msg_name => G_ENG_NO_DIM_ATTR_VAL_ERR
1571 ,p_token1 => 'DIMENSION_VARCHAR_LABEL'
1572 ,p_value1 => p_dimension_varchar_label
1573 ,p_token2 => 'ATTRIBUTE_VARCHAR_LABEL'
1574 ,p_value2 => p_attribute_varchar_label
1575 );
1576 raise l_get_dim_attr_val_error;
1577 end;
1578
1579 FEM_ENGINES_PKG.Tech_Message (
1580 p_severity => G_LOG_LEVEL_2
1581 ,p_module => G_BLOCK||'.'||l_api_name
1582 ,p_msg_text => 'END'
1583 );
1584
1585 EXCEPTION
1586
1587 when l_get_dim_attr_val_error then
1588
1589 FEM_ENGINES_PKG.Tech_Message (
1590 p_severity => G_LOG_LEVEL_6
1591 ,p_module => G_BLOCK||'.'||l_api_name
1592 ,p_msg_text => 'Get Dimension Attribute Value Exception'
1593 );
1594
1595 raise g_rollup_request_error;
1596
1597 END Get_Dim_Attribute_Value;
1598
1599
1600
1601 /*============================================================================+
1602 | PROCEDURE
1603 | Get_Ledger_Currency_Code
1604 |
1605 | DESCRIPTION
1606 | Get the currency code for the specified ledger id.
1607 |
1608 | SCOPE - PRIVATE
1609 |
1610 +============================================================================*/
1611
1612 PROCEDURE Get_Ledger_Currency_Code (
1613 p_ledger_id in varchar2
1614 ,x_currency_code out nocopy varchar2
1615 )
1616 IS
1617
1618 l_api_name constant varchar2(30) := 'Get_Ledger_Currency_Code';
1619
1620 l_dimension_rec dimension_record;
1621
1622 l_dimension_id number;
1623 l_attribute_id number;
1624 l_attr_version_id number;
1625
1626 l_get_ledger_curr_code_error exception;
1627
1628 BEGIN
1629
1630 FEM_ENGINES_PKG.Tech_Message (
1631 p_severity => G_LOG_LEVEL_2
1632 ,p_module => G_BLOCK||'.'||l_api_name
1633 ,p_msg_text => 'BEGIN'
1634 );
1635
1636 if (g_ledger_dimension_id is null) then
1637
1638 Get_Dim_Attribute (
1639 p_dimension_varchar_label => 'LEDGER'
1640 ,p_attribute_varchar_label => 'LEDGER_FUNCTIONAL_CRNCY_CODE'
1641 ,x_dimension_rec => l_dimension_rec
1642 ,x_attribute_id => g_ledger_curr_attr_id
1643 ,x_attr_version_id => g_ledger_curr_attr_version_id
1644 );
1645
1646 g_ledger_dimension_id := l_dimension_rec.dimension_id;
1647
1648 end if;
1649
1650 begin
1651 select dim_attribute_varchar_member
1652 into x_currency_code
1653 from fem_ledgers_attr
1654 where attribute_id = g_ledger_curr_attr_id
1655 and version_id = g_ledger_curr_attr_version_id
1656 and ledger_id = p_ledger_id;
1657 exception
1658 when others then
1659 FEM_ENGINES_PKG.User_Message (
1660 p_app_name => G_FEM
1661 ,p_msg_name => G_ENG_NO_DIM_ATTR_VAL_ERR
1662 ,p_token1 => 'DIMENSION_VARCHAR_LABEL'
1663 ,p_value1 => 'LEDGER'
1664 ,p_token2 => 'ATTRIBUTE_VARCHAR_LABEL'
1665 ,p_value2 => 'LEDGER_FUNCTIONAL_CRNCY_CODE'
1666 );
1667 raise l_get_ledger_curr_code_error;
1668 end;
1669
1670 FEM_ENGINES_PKG.Tech_Message (
1671 p_severity => G_LOG_LEVEL_2
1672 ,p_module => G_BLOCK||'.'||l_api_name
1673 ,p_msg_text => 'END'
1674 );
1675
1676 EXCEPTION
1677
1678 when l_get_ledger_curr_code_error then
1679
1680 FEM_ENGINES_PKG.Tech_Message (
1681 p_severity => G_LOG_LEVEL_6
1682 ,p_module => G_BLOCK||'.'||l_api_name
1683 ,p_msg_text => 'Get Ledger Currency Code Exception'
1684 );
1685
1686 raise g_rollup_request_error;
1687
1688 END Get_Ledger_Currency_Code;
1689
1690
1691
1692 /*============================================================================+
1693 | PROCEDURE
1694 | Get_Dim_Attribute
1695 |
1696 | DESCRIPTION
1697 | Get the dimension and attribute information for the specified dimension
1698 | label and attribute label.
1699 |
1700 | SCOPE - PRIVATE
1701 |
1702 +============================================================================*/
1703
1704 PROCEDURE Get_Dim_Attribute (
1705 p_dimension_varchar_label in varchar2
1706 ,p_attribute_varchar_label in varchar2
1707 ,x_dimension_rec out nocopy dimension_record
1708 ,x_attribute_id out nocopy number
1709 ,x_attr_version_id out nocopy number
1710 )
1711 IS
1712
1713 l_api_name constant varchar2(30) := 'Get_Dim_Attribute';
1714
1715 l_get_dim_attr_error exception;
1716
1717 BEGIN
1718
1719 FEM_ENGINES_PKG.Tech_Message (
1720 p_severity => G_LOG_LEVEL_2
1721 ,p_module => G_BLOCK||'.'||l_api_name
1722 ,p_msg_text => 'BEGIN'
1723 );
1724
1725 Get_Dimension_Record (
1726 p_dimension_varchar_label => p_dimension_varchar_label
1727 ,x_dimension_rec => x_dimension_rec
1728 );
1729
1730 begin
1731 select att.attribute_id
1732 ,ver.version_id
1733 into x_attribute_id
1734 ,x_attr_version_id
1735 from fem_dim_attributes_b att
1736 ,fem_dim_attr_versions_b ver
1737 where att.dimension_id = x_dimension_rec.dimension_id
1738 and att.attribute_varchar_label = p_attribute_varchar_label
1739 and ver.attribute_id = att.attribute_id
1740 and ver.default_version_flag = 'Y';
1741 exception
1742 when others then
1743 FEM_ENGINES_PKG.User_Message (
1744 p_app_name => G_FEM
1745 ,p_msg_name => G_ENG_NO_DIM_ATTR_VER_ERR
1746 ,p_token1 => 'DIMENSION_VARCHAR_LABEL'
1747 ,p_value1 => p_dimension_varchar_label
1748 ,p_token2 => 'ATTRIBUTE_VARCHAR_LABEL'
1749 ,p_value2 => p_attribute_varchar_label
1750 );
1751 raise l_get_dim_attr_error;
1752 end;
1753
1754 FEM_ENGINES_PKG.Tech_Message (
1755 p_severity => G_LOG_LEVEL_2
1756 ,p_module => G_BLOCK||'.'||l_api_name
1757 ,p_msg_text => 'END'
1758 );
1759
1760 EXCEPTION
1761
1762 when l_get_dim_attr_error then
1763
1764 FEM_ENGINES_PKG.Tech_Message (
1765 p_severity => G_LOG_LEVEL_6
1766 ,p_module => G_BLOCK||'.'||l_api_name
1767 ,p_msg_text => 'Get Dimension Attribute Value Exception'
1768 );
1769
1770 raise g_rollup_request_error;
1771
1772 END Get_Dim_Attribute;
1773
1774
1775
1776 /*============================================================================+
1777 | PROCEDURE
1778 | Sql_Stmts_Prep
1779 |
1780 | DESCRIPTION
1781 | Dynamic SQL statement preparation.
1782 |
1783 | SCOPE - PRIVATE
1784 |
1785 +============================================================================*/
1786
1787 PROCEDURE Sql_Stmts_Prep (
1788 p_request_rec in request_record
1789 ,x_sql_rec out nocopy sql_record
1790 )
1791 IS
1792
1793 l_api_name constant varchar2(30) := 'Sql_Stmts_Prep';
1794
1795 l_comp_dim_req_col varchar2(30);
1796 l_column_name varchar2(30);
1797 l_proc_key_flag varchar2(1);
1798
1799 l_comp_dim_comp_cols_using long;
1800 l_comp_dim_data_cols_using long;
1801 l_comp_dim_data_cols_on long;
1802 l_comp_dim_comp_cols_insert long;
1803 l_comp_dim_data_cols_insert long;
1804 l_comp_dim_comp_cols_values long;
1805 l_comp_dim_data_cols_values long;
1806
1807 l_comp_dim_cols_csr dynamic_cursor;
1808 l_comp_dim_cols_stmt long;
1809
1810 l_sql_stmts_prep_error exception;
1811
1812 BEGIN
1813
1814 FEM_ENGINES_PKG.Tech_Message (
1815 p_severity => G_LOG_LEVEL_2
1816 ,p_module => G_BLOCK||'.'||l_api_name
1817 ,p_msg_text => 'BEGIN'
1818 );
1819
1820 if (p_request_rec.dimension_rec.dimension_varchar_label = 'COST_OBJECT') then
1821
1822 l_comp_dim_req_col := 'cost_obj';
1823
1824 elsif (p_request_rec.dimension_rec.dimension_varchar_label = 'ACTIVITY') then
1825
1826 l_comp_dim_req_col := 'activity';
1827
1828 else
1829
1830 FEM_ENGINES_PKG.User_Message (
1831 p_app_name => G_FEM
1832 ,p_msg_name => G_RU_NO_ROLLUP_DIM_ERR
1833 ,p_token1 => 'DIMENSION_VARCHAR_LABEL'
1834 ,p_value1 => p_request_rec.dimension_rec.dimension_varchar_label
1835 );
1836 raise l_sql_stmts_prep_error;
1837
1838 end if;
1839
1840 --todo: check if any user dims have been reassigned in FEM_BALANCES and that
1841 --are also a component dimension.
1842
1843 -- First find all the component dimension columns
1844 l_comp_dim_cols_stmt :=
1845 ' select reqs.column_name'||
1846 ' from fem_column_requiremnt_b reqs'||
1847 ' ,fem_tab_columns_v cols'||
1848 ' where reqs.'||l_comp_dim_req_col||'_dim_requirement_code is not null'||
1849 ' and reqs.'||l_comp_dim_req_col||'_dim_component_flag = ''Y'''||
1850 ' and reqs.dimension_id is not null'||
1851 ' and cols.table_name = ''FEM_BALANCES'''||
1852 ' and cols.column_name = reqs.column_name'||
1853 ' and cols.dimension_id = reqs.dimension_id';
1854
1855 open l_comp_dim_cols_csr
1856 for l_comp_dim_cols_stmt;
1857
1858 loop
1859
1860 fetch l_comp_dim_cols_csr into
1861 l_column_name;
1862
1863 exit when l_comp_dim_cols_csr%NOTFOUND;
1864
1865 -- build the Component cimension column string used in the Using clause
1866 l_comp_dim_comp_cols_using := l_comp_dim_comp_cols_using ||
1867 ' ,parent.'||l_column_name;
1868
1869 -- build the Component dimension column string used in the Insert clause
1870 l_comp_dim_comp_cols_insert := l_comp_dim_comp_cols_insert ||
1871 ' ,bp.'||l_column_name;
1872
1873 -- build the Component dimension column string used in the Values clause
1874 l_comp_dim_comp_cols_values := l_comp_dim_comp_cols_values ||
1875 ' ,bc.'||l_column_name;
1876
1877 end loop;
1878
1879 close l_comp_dim_cols_csr;
1880
1881 -- Then find all the dimension columns that are not part of the composite
1882 -- dimension definition, thus making them data dimension columns in
1883 -- FEM_BALANCES
1884 l_comp_dim_cols_stmt :=
1885 ' select reqs.column_name'||
1886 ' ,decode(cols.column_name,props.column_name,''Y'',''N'') as proc_key_flag'||
1887 ' from fem_column_requiremnt_b reqs'||
1888 ' ,fem_tab_columns_v cols'||
1889 ' ,fem_tab_column_prop props'||
1890 ' where reqs.'||l_comp_dim_req_col||'_dim_component_flag = ''N'''||
1891 ' and reqs.dimension_id is not null'||
1892 ' and cols.table_name = ''FEM_BALANCES'''||
1893 ' and cols.column_name = reqs.column_name'||
1894 ' and cols.dimension_id is not null'||
1895 ' and cols.fem_data_type_code = ''DIMENSION'''||
1896 ' and props.table_name (+) = cols.table_name'||
1897 ' and props.column_name (+) = cols.column_name'||
1898 ' and props.column_property_code (+) = ''PROCESSING_KEY'''||
1899 ' and ('||
1900 ' reqs.'||l_comp_dim_req_col||'_dim_requirement_code is not null'||
1901 ' or ('||
1902 ' reqs.'||l_comp_dim_req_col||'_dim_requirement_code is null'||
1903 ' and cols.column_name not in ('||
1904 ' ''ACTIVITY_ID'''||
1905 ' ,''COST_OBJECT_ID'''||
1906 ' ,''CREATED_BY_OBJECT_ID'''||
1907 ' ,''LAST_UPDATED_BY_OBJECT_ID'''||
1908 ' ,''CURRENCY_TYPE_CODE'''||
1909 ' ,''CURRENCY_CODE'''||
1910 ' ,''DATASET_CODE'''||
1911 ' ,''CAL_PERIOD_ID'''||
1912 ' ,''LEDGER_ID'''||
1913 ' ,''SOURCE_SYSTEM_CODE'''||
1914 ' )'||
1915 ' )'||
1916 ' )';
1917
1918 open l_comp_dim_cols_csr
1919 for l_comp_dim_cols_stmt;
1920
1921 loop
1922
1923 fetch l_comp_dim_cols_csr into
1924 l_column_name
1925 ,l_proc_key_flag;
1926
1927 exit when l_comp_dim_cols_csr%NOTFOUND;
1928
1929 -- build the Data dimension column string used in the Using clause
1930 l_comp_dim_data_cols_using := l_comp_dim_data_cols_using ||
1931 ' ,b.'||l_column_name;
1932
1933 -- build the Data dimension column string used in the On clause
1934 if (l_proc_key_flag = 'Y') then
1935 -- If column is part of processing key, then column values cannot be null.
1936 -- Cannot use nvl() function as it will affect performance as the column
1937 -- will be part of the table's processing key unique index.
1938 l_comp_dim_data_cols_on := l_comp_dim_data_cols_on ||
1939 ' and bp.'||l_column_name||' = bc.'||l_column_name;
1940 else
1941 -- If column is not part of processing key, then column values can be
1942 -- null. Must use nvl() function to handle these null values. It will
1943 -- not greatly affect performance as the column is not be part of the
1944 -- table's processing key unique index.
1945 l_comp_dim_data_cols_on := l_comp_dim_data_cols_on ||
1946 ' and nvl(bp.'||l_column_name||',-1) = nvl(bc.'||l_column_name||',-1)';
1947 end if;
1948
1949 -- build the Data dimension column string used in the Insert clause
1950 l_comp_dim_data_cols_insert := l_comp_dim_data_cols_insert ||
1951 ' ,bp.'||l_column_name;
1952
1953 -- build the Data dimension column string used in the Values clause
1954 l_comp_dim_data_cols_values := l_comp_dim_data_cols_values ||
1955 ' ,bc.'||l_column_name;
1956
1957 end loop;
1958
1959 close l_comp_dim_cols_csr;
1960
1961 -- Populate SQL record
1962 x_sql_rec.comp_dim_comp_cols_using := l_comp_dim_comp_cols_using;
1963 x_sql_rec.comp_dim_data_cols_using := l_comp_dim_data_cols_using;
1964 x_sql_rec.comp_dim_data_cols_on := l_comp_dim_data_cols_on;
1965 x_sql_rec.comp_dim_comp_cols_insert := l_comp_dim_comp_cols_insert;
1966 x_sql_rec.comp_dim_data_cols_insert := l_comp_dim_data_cols_insert;
1967 x_sql_rec.comp_dim_comp_cols_values := l_comp_dim_comp_cols_values;
1968 x_sql_rec.comp_dim_data_cols_values := l_comp_dim_data_cols_values;
1969
1970 FEM_ENGINES_PKG.Tech_Message (
1971 p_severity => G_LOG_LEVEL_2
1972 ,p_module => G_BLOCK||'.'||l_api_name
1973 ,p_msg_text => 'END'
1974 );
1975
1976 EXCEPTION
1977
1978 when l_sql_stmts_prep_error then
1979
1980 FEM_ENGINES_PKG.Tech_Message (
1981 p_severity => G_LOG_LEVEL_6
1982 ,p_module => G_BLOCK||'.'||l_api_name
1983 ,p_msg_text => 'SQL Statements Preparation Exception'
1984 );
1985
1986 raise g_rollup_request_error;
1987
1988 END Sql_Stmts_Prep;
1989
1990
1991
1992 /*============================================================================+
1993 | PROCEDURE
1994 | Register_Request
1995 |
1996 | DESCRIPTION
1997 | Registers the request in the processing locks tables.
1998 |
1999 | SCOPE - PRIVATE
2000 |
2001 +============================================================================*/
2002
2003 PROCEDURE Register_Request (
2004 p_request_rec in request_record
2005 )
2006 IS
2007
2008 l_api_name constant varchar2(30) := 'Register_Request';
2009
2010 l_return_status t_return_status%TYPE;
2011 l_msg_count t_msg_count%TYPE;
2012 l_msg_data t_msg_data%TYPE;
2013
2014 l_register_request_error exception;
2015
2016 BEGIN
2017
2018 FEM_ENGINES_PKG.Tech_Message (
2019 p_severity => G_LOG_LEVEL_2
2020 ,p_module => G_BLOCK||'.'||l_api_name
2021 ,p_msg_text => 'BEGIN'
2022 );
2023
2024 savepoint register_request_pub;
2025
2026 -- Call the FEM_PL_PKG.Register_Request API procedure to register
2027 -- the concurrent request in FEM_PL_REQUESTS.
2028 FEM_PL_PKG.Register_Request (
2029 p_api_version => 1.0
2030 ,p_commit => FND_API.G_FALSE
2031 ,p_cal_period_id => p_request_rec.output_cal_period_id
2032 ,p_ledger_id => p_request_rec.ledger_id
2033 ,p_dataset_io_obj_def_id => p_request_rec.dataset_grp_obj_def_id
2034 ,p_output_dataset_code => p_request_rec.output_dataset_code
2035 ,p_source_system_code => p_request_rec.source_system_code
2036 ,p_effective_date => p_request_rec.effective_date
2037 ,p_rule_set_obj_def_id => p_request_rec.ruleset_obj_def_id
2038 ,p_rule_set_name => p_request_rec.ruleset_obj_name
2039 ,p_request_id => p_request_rec.request_id
2040 ,p_user_id => p_request_rec.user_id
2041 ,p_last_update_login => p_request_rec.login_id
2042 ,p_program_id => p_request_rec.pgm_id
2043 ,p_program_login_id => p_request_rec.login_id
2044 ,p_program_application_id => p_request_rec.pgm_app_id
2045 ,p_exec_mode_code => null
2046 ,p_dimension_id => null
2047 ,p_table_name => null
2048 ,p_hierarchy_name => null
2049 ,x_msg_count => l_msg_count
2050 ,x_msg_data => l_msg_data
2051 ,x_return_status => l_return_status
2052 );
2053
2054 -- Request Lock exists
2055 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
2056 Get_Put_Messages (
2057 p_msg_count => l_msg_count
2058 ,p_msg_data => l_msg_data
2059 );
2060 raise l_register_request_error;
2061 end if;
2062
2063 commit;
2064
2065 FEM_ENGINES_PKG.Tech_Message (
2066 p_severity => G_LOG_LEVEL_2
2067 ,p_module => G_BLOCK||'.'||l_api_name
2068 ,p_msg_text => 'END'
2069 );
2070
2071 EXCEPTION
2072
2073 when l_register_request_error then
2074
2075 rollback to register_request_pub;
2076
2077 FEM_ENGINES_PKG.Tech_Message (
2078 p_severity => G_LOG_LEVEL_6
2079 ,p_module => G_BLOCK||'.'||l_api_name
2080 ,p_msg_text => 'Register Request Exception'
2081 );
2082
2083 raise g_rollup_request_error;
2084
2085 when g_rollup_request_error then
2086
2087 rollback to register_request_pub;
2088 raise g_rollup_request_error;
2089
2090 when others then
2091
2092 rollback to register_request_pub;
2093 raise;
2094
2095 END Register_Request;
2096
2097
2098
2099 /*============================================================================+
2100 | PROCEDURE
2101 | Rollup_Rule
2102 |
2103 | DESCRIPTION
2104 | Main procedure for rollup processing on a rule.
2105 |
2106 | SCOPE - PRIVATE
2107 |
2108 +============================================================================*/
2109
2110 PROCEDURE Rollup_Rule (
2111 p_request_rec in request_record
2112 ,p_sql_rec in sql_record
2113 ,p_rollup_obj_id in number
2114 ,p_rollup_obj_def_id in number
2115 ,p_rollup_sequence in number
2116 ,p_rollup_rule_def_stmt in long
2117 ,p_input_ds_b_where_clause in long
2118 ,p_input_ds_q_where_clause in long
2119 ,x_return_status out nocopy varchar2
2120 )
2121 IS
2122
2123 l_api_name constant varchar2(30) := 'Rollup_Rule';
2124
2125 l_rule_rec rule_record;
2126
2127 l_completion_status boolean;
2128 l_uncosted_node_count number;
2129
2130 l_find_children_stmt long;
2131 l_rollup_parent_stmt long;
2132 l_find_child_chains_stmt long;
2133 l_num_of_input_rows_stmt long;
2134
2135 -------------------------------------
2136 -- Declare bulk collection columns --
2137 -------------------------------------
2138 l_top_node_id_tbl number_table;
2139
2140 ----------------------------
2141 -- Declare static cursors --
2142 ----------------------------
2143 cursor l_get_root_nodes_csr (
2144 p_request_id in number
2145 ,p_object_id in number
2146 ) is
2147 select node_id
2148 from fem_ru_nodes_t
2149 where created_by_request_id = p_request_id
2150 and created_by_object_id = p_object_id
2151 and root_flag = 'Y'
2152 and costed_flag = 'N';
2153
2154 cursor l_get_cond_nodes_csr (
2155 p_request_id in number
2156 ,p_object_id in number
2157 ) is
2158 select node_id
2159 from fem_ru_nodes_t
2160 where created_by_request_id = p_request_id
2161 and created_by_object_id = p_object_id
2162 and condition_flag = 'Y'
2163 and costed_flag = 'N';
2164
2165 -----------------------------------------------------------
2166 -- Index indicating last row number for a cursor.
2167 -----------------------------------------------------------
2168 l_get_root_nodes_last_row number;
2169 l_get_cond_nodes_last_row number;
2170
2171 l_rollup_rule_error exception;
2172
2173 BEGIN
2174
2175 FEM_ENGINES_PKG.Tech_Message (
2176 p_severity => G_LOG_LEVEL_2
2177 ,p_module => G_BLOCK||'.'||l_api_name
2178 ,p_msg_text => 'BEGIN'
2179 );
2180
2181 -- Initialize the return status to SUCCESS
2182 x_return_status := FND_API.G_RET_STS_SUCCESS;
2183
2184 ------------------------------------------------------------------------------
2185 -- STEP 1: Rule Pre Processing
2186 ------------------------------------------------------------------------------
2187 FEM_ENGINES_PKG.tech_message (
2188 p_severity => G_LOG_LEVEL_1
2189 ,p_module => G_BLOCK||'.'||l_api_name
2190 ,p_msg_text => 'Step 1: Rule Pre Processing'
2191 );
2192
2193 Rule_Prep (
2194 p_request_rec => p_request_rec
2195 ,p_rollup_obj_id => p_rollup_obj_id
2196 ,p_rollup_obj_def_id => p_rollup_obj_def_id
2197 ,p_rollup_sequence => p_rollup_sequence
2198 ,p_rollup_rule_def_stmt => p_rollup_rule_def_stmt
2199 ,x_rule_rec => l_rule_rec
2200 );
2201
2202 ------------------------------------------------------------------------------
2203 -- STEP 2: Build Dynamic SQL
2204 ------------------------------------------------------------------------------
2205 FEM_ENGINES_PKG.tech_message (
2206 p_severity => G_LOG_LEVEL_1
2207 ,p_module => G_BLOCK||'.'||l_api_name
2208 ,p_msg_text => 'Step 2: Build Dynamic SQL'
2209 );
2210
2211 Sql_Stmts_Build (
2212 p_request_rec => p_request_rec
2213 ,p_rule_rec => l_rule_rec
2214 ,p_sql_rec => p_sql_rec
2215 ,p_input_ds_b_where_clause => p_input_ds_b_where_clause
2216 ,p_input_ds_q_where_clause => p_input_ds_q_where_clause
2217 ,x_find_children_stmt => l_find_children_stmt
2218 ,x_rollup_parent_stmt => l_rollup_parent_stmt
2219 ,x_find_child_chains_stmt => l_find_child_chains_stmt
2220 ,x_num_of_input_rows_stmt => l_num_of_input_rows_stmt
2221 );
2222
2223 ------------------------------------------------------------------------------
2224 -- STEP 3: Register Rule under the same parent request
2225 ------------------------------------------------------------------------------
2226 FEM_ENGINES_PKG.tech_message (
2227 p_severity => G_LOG_LEVEL_1
2228 ,p_module => G_BLOCK||'.'||l_api_name
2229 ,p_msg_text => 'Step 3: Register Rule'
2230 );
2231
2232 Register_Rule (
2233 p_request_rec => p_request_rec
2234 ,p_rule_rec => l_rule_rec
2235 );
2236
2237 ------------------------------------------------------------------------------
2238 -- STEP 4: Create Temporary Objects
2239 ------------------------------------------------------------------------------
2240 FEM_ENGINES_PKG.tech_message (
2241 p_severity => G_LOG_LEVEL_1
2242 ,p_module => G_BLOCK||'.'||l_api_name
2243 ,p_msg_text => 'Step 4: Create Temporary Objects'
2244 );
2245
2246 Create_Temp_Objects (
2247 p_request_rec => p_request_rec
2248 ,p_rule_rec => l_rule_rec
2249 );
2250
2251 if (l_rule_rec.cond_exists) then
2252
2253 ----------------------------------------------------------------------------
2254 -- STEP 5.1: Find Condition Nodes
2255 ----------------------------------------------------------------------------
2256 FEM_ENGINES_PKG.Tech_Message (
2257 p_severity => G_LOG_LEVEL_1
2258 ,p_module => G_BLOCK||'.'||l_api_name
2259 ,p_msg_text => 'Step 5.1: Find Condition Nodes'
2260 );
2261
2262 Find_Condition_Nodes (
2263 p_request_rec => p_request_rec
2264 ,p_rule_rec => l_rule_rec
2265 );
2266
2267 else
2268
2269 ----------------------------------------------------------------------------
2270 -- STEP 5.2: Find Root Nodes
2271 ----------------------------------------------------------------------------
2272 FEM_ENGINES_PKG.Tech_Message (
2273 p_severity => G_LOG_LEVEL_1
2274 ,p_module => G_BLOCK||'.'||l_api_name
2275 ,p_msg_text => 'Step 5.2: Find Root Nodes'
2276 );
2277
2278 Find_Root_Nodes (
2279 p_request_rec => p_request_rec
2280 ,p_rule_rec => l_rule_rec
2281 );
2282
2283 end if;
2284
2285 ------------------------------------------------------------------------------
2286 -- STEP 6: Rollup Root Nodes
2287 ------------------------------------------------------------------------------
2288 FEM_ENGINES_PKG.Tech_Message (
2289 p_severity => G_LOG_LEVEL_1
2290 ,p_module => G_BLOCK||'.'||l_api_name
2291 ,p_msg_text => 'Step 6: Rollup Root Nodes'
2292 );
2293
2294 -- Cursor query to get all root nodes
2295 open l_get_root_nodes_csr (
2296 p_request_id => p_request_rec.request_id
2297 ,p_object_id => l_rule_rec.rollup_obj_id
2298 );
2299
2300 loop
2301
2302 fetch l_get_root_nodes_csr
2303 bulk collect into
2304 l_top_node_id_tbl
2305 limit g_fetch_limit;
2306
2307 l_get_root_nodes_last_row := l_top_node_id_tbl.LAST;
2308 if (l_get_root_nodes_last_row is null) then
2309 exit;
2310 end if;
2311
2312 -- Perform rollup on all root nodes
2313 for i in 1..l_get_root_nodes_last_row loop
2314
2315 Rollup_Top_Node (
2316 p_request_rec => p_request_rec
2317 ,p_rule_rec => l_rule_rec
2318 ,p_find_children_stmt => l_find_children_stmt
2319 ,p_rollup_parent_stmt => l_rollup_parent_stmt
2320 ,p_find_child_chains_stmt => l_find_child_chains_stmt
2321 ,p_input_ds_b_where_clause => p_input_ds_b_where_clause
2322 ,p_top_node_id => l_top_node_id_tbl(i)
2323 );
2324
2325 end loop;
2326
2327 l_top_node_id_tbl.DELETE;
2328
2329 end loop;
2330
2331 close l_get_root_nodes_csr;
2332
2333 ------------------------------------------------------------------------------
2334 -- STEP 7: Rollup Condition Nodes
2335 ------------------------------------------------------------------------------
2336 if (l_rule_rec.cond_exists) then
2337
2338 FEM_ENGINES_PKG.Tech_Message (
2339 p_severity => G_LOG_LEVEL_1
2340 ,p_module => G_BLOCK||'.'||l_api_name
2341 ,p_msg_text => 'Step 7: Rollup Condition Nodes'
2342 );
2343
2344 -- Cursor query to get all condition nodes
2345 open l_get_cond_nodes_csr (
2346 p_request_id => p_request_rec.request_id
2347 ,p_object_id => l_rule_rec.rollup_obj_id
2348 );
2349
2350 loop
2351
2352 fetch l_get_cond_nodes_csr
2353 bulk collect into
2354 l_top_node_id_tbl
2355 limit g_fetch_limit;
2356
2357 l_get_cond_nodes_last_row := l_top_node_id_tbl.LAST;
2358 if (l_get_cond_nodes_last_row is null) then
2359 exit;
2360 end if;
2361
2362 -- Perform rollup on all root nodes
2363 for i in 1..l_get_cond_nodes_last_row loop
2364
2365 Rollup_Top_Node (
2366 p_request_rec => p_request_rec
2367 ,p_rule_rec => l_rule_rec
2368 ,p_find_children_stmt => l_find_children_stmt
2369 ,p_rollup_parent_stmt => l_rollup_parent_stmt
2370 ,p_find_child_chains_stmt => l_find_child_chains_stmt
2371 ,p_input_ds_b_where_clause => p_input_ds_b_where_clause
2372 ,p_top_node_id => l_top_node_id_tbl(i)
2373 );
2374
2375 end loop;
2376
2377 l_top_node_id_tbl.DELETE;
2378
2379 end loop;
2380
2381 close l_get_cond_nodes_csr;
2382
2383 end if;
2384
2385 ------------------------------------------------------------------------------
2386 -- STEP 8: Check for Uncosted Nodes
2387 ------------------------------------------------------------------------------
2388 FEM_ENGINES_PKG.Tech_Message (
2389 p_severity => G_LOG_LEVEL_1
2390 ,p_module => G_BLOCK||'.'||l_api_name
2391 ,p_msg_text => 'Step 8: Check for Uncosted Nodes'
2392 );
2393
2394 -- query to count number of uncosted nodes
2395 select count(*)
2396 into l_uncosted_node_count
2397 from fem_ru_nodes_t
2398 where created_by_request_id = p_request_rec.request_id
2399 and created_by_object_id = l_rule_rec.rollup_obj_id
2400 and costed_flag = 'N';
2401
2402 if (l_uncosted_node_count > 0) then
2403
2404 FEM_ENGINES_PKG.User_Message(
2405 p_app_name => G_FEM
2406 ,p_msg_name => G_RU_UNCOSTED_NODES_ERR
2407 );
2408 raise l_rollup_rule_error;
2409
2410 end if;
2411
2412 ------------------------------------------------------------------------------
2413 -- STEP 9: Rule Post Processing
2414 ------------------------------------------------------------------------------
2415 FEM_ENGINES_PKG.Tech_Message (
2416 p_severity => G_LOG_LEVEL_1
2417 ,p_module => G_BLOCK||'.'||l_api_name
2418 ,p_msg_text => 'Step 9: Rule Post Processing'
2419 );
2420
2421 Rule_Post_Proc (
2422 p_request_rec => p_request_rec
2423 ,p_rule_rec => l_rule_rec
2424 ,p_num_of_input_rows_stmt => l_num_of_input_rows_stmt
2425 ,p_exec_status_code => G_EXEC_STATUS_SUCCESS
2426 );
2427
2428 FEM_ENGINES_PKG.Tech_Message (
2429 p_severity => G_LOG_LEVEL_2
2430 ,p_module => G_BLOCK||'.'||l_api_name
2431 ,p_msg_text => 'END'
2432 );
2433
2434 EXCEPTION
2435
2436 when l_rollup_rule_error then
2437
2438 FEM_ENGINES_PKG.Tech_Message (
2439 p_severity => G_LOG_LEVEL_6
2440 ,p_module => G_BLOCK||'.'||l_api_name
2441 ,p_msg_text => 'Rollup Rule Exception'
2442 );
2443
2444 if (l_get_root_nodes_csr%ISOPEN) then
2445 close l_get_root_nodes_csr;
2446 end if;
2447
2448 if (l_get_cond_nodes_csr%ISOPEN) then
2449 close l_get_cond_nodes_csr;
2450 end if;
2451
2452 -- Rule Post Processing
2453 Rule_Post_Proc (
2454 p_request_rec => p_request_rec
2455 ,p_rule_rec => l_rule_rec
2456 ,p_num_of_input_rows_stmt => l_num_of_input_rows_stmt
2457 ,p_exec_status_code => G_EXEC_STATUS_ERROR_UNDO
2458 );
2459
2460 -- Commented out properly handle continue_process_on_err_flg
2461 --raise g_rollup_request_error;
2462
2463 -- Set the return status to ERROR
2464 x_return_status := FND_API.G_RET_STS_ERROR;
2465
2466 when g_rollup_request_error then
2467
2468 if (l_get_root_nodes_csr%ISOPEN) then
2469 close l_get_root_nodes_csr;
2470 end if;
2471
2472 if (l_get_cond_nodes_csr%ISOPEN) then
2473 close l_get_cond_nodes_csr;
2474 end if;
2475
2476 -- Rule Post Processing
2477 Rule_Post_Proc (
2478 p_request_rec => p_request_rec
2479 ,p_rule_rec => l_rule_rec
2480 ,p_num_of_input_rows_stmt => l_num_of_input_rows_stmt
2481 ,p_exec_status_code => G_EXEC_STATUS_ERROR_UNDO
2482 );
2483
2484 -- Commented out properly handle continue_process_on_err_flg
2485 --raise g_rollup_request_error;
2486
2487 -- Set the return status to ERROR
2488 x_return_status := FND_API.G_RET_STS_ERROR;
2489
2490 when others then
2491
2492 g_prg_msg := SQLERRM;
2493 g_callstack := DBMS_UTILITY.Format_Call_Stack;
2494
2495 FEM_ENGINES_PKG.Tech_Message (
2496 p_severity => G_LOG_LEVEL_6
2497 ,p_module => G_BLOCK||'.'||l_api_name||'.Unexpected_Exception'
2498 ,p_msg_text => g_prg_msg
2499 );
2500
2501 FEM_ENGINES_PKG.User_Message (
2502 p_app_name => G_FEM
2503 ,p_msg_name => G_UNEXPECTED_ERROR
2504 ,p_token1 => 'ERR_MSG'
2505 ,p_value1 => g_prg_msg
2506 );
2507
2508 if (l_get_root_nodes_csr%ISOPEN) then
2509 close l_get_root_nodes_csr;
2510 end if;
2511
2512 if (l_get_cond_nodes_csr%ISOPEN) then
2513 close l_get_cond_nodes_csr;
2514 end if;
2515
2516 -- Rule Post Processing
2517 Rule_Post_Proc (
2518 p_request_rec => p_request_rec
2519 ,p_rule_rec => l_rule_rec
2520 ,p_num_of_input_rows_stmt => l_num_of_input_rows_stmt
2521 ,p_exec_status_code => G_EXEC_STATUS_ERROR_UNDO
2522 );
2523
2524 -- Commented out properly handle continue_process_on_err_flg
2525 --raise g_rollup_request_error;
2526
2527 -- Set the return status to UNEXP_ERROR
2528 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2529
2530 END Rollup_Rule;
2531
2532
2533
2534 /*============================================================================+
2535 | PROCEDURE
2536 | Rule_Prep
2537 |
2538 | DESCRIPTION
2539 | Rollup Rule Preparation. Populates the rule record that will be processed.
2540 |
2541 | SCOPE - PRIVATE
2542 |
2543 +============================================================================*/
2544
2545 PROCEDURE Rule_Prep (
2546 p_request_rec in request_record
2547 ,p_rollup_obj_id in number
2548 ,p_rollup_obj_def_id in number
2549 ,p_rollup_sequence in number
2550 ,p_rollup_rule_def_stmt in long
2551 ,x_rule_rec out nocopy rule_record
2552 )
2553 IS
2554
2555 l_api_name constant varchar2(30) := 'Rule_Prep';
2556
2557 l_dimension_id number;
2558
2559 l_rule_prep_error exception;
2560
2561 BEGIN
2562
2563 FEM_ENGINES_PKG.Tech_Message (
2564 p_severity => G_LOG_LEVEL_2
2565 ,p_module => G_BLOCK||'.'||l_api_name
2566 ,p_msg_text => 'BEGIN'
2567 );
2568
2569 x_rule_rec.rollup_obj_id := p_rollup_obj_id;
2570 x_rule_rec.rollup_obj_def_id := p_rollup_obj_def_id;
2571 x_rule_rec.rollup_sequence := p_rollup_sequence;
2572
2573 ------------------------------------------------------------------------------
2574 -- Get the object info from FEM_OBJECT_CATALOG_B for the Rollup Object ID.
2575 ------------------------------------------------------------------------------
2576 begin
2577 select object_type_code
2578 ,object_name
2579 ,local_vs_combo_id
2580 into x_rule_rec.rollup_obj_type_code
2581 ,x_rule_rec.rollup_obj_name
2582 ,x_rule_rec.local_vs_combo_id
2583 from fem_object_catalog_vl
2584 where object_id = x_rule_rec.rollup_obj_id;
2585 exception
2586 when others then
2587 FEM_ENGINES_PKG.User_Message (
2588 p_app_name => G_FEM
2589 ,p_msg_name => G_ENG_NO_OBJ_ERR
2590 ,p_token1 => 'OBJECT_TYPE_MEANING'
2591 ,p_value1 => Get_Object_Type_Name(p_request_rec.rollup_obj_type_code)
2592 ,p_token2 => 'OBJECT_ID'
2593 ,p_value2 => x_rule_rec.rollup_obj_id
2594 );
2595 raise l_rule_prep_error;
2596 end;
2597
2598 ------------------------------------------------------------------------------
2599 -- If this is a Rule Set Submission, check that the object_type_code and
2600 -- local_vs_combo_id of the rollup rule matches the Rule Set's.
2601 ------------------------------------------------------------------------------
2602 if (p_request_rec.submit_obj_type_code = 'RULE_SET') then
2603
2604 -- For rule sets processing, post to log file when starting to process a
2605 -- rule set rule.
2606 FEM_ENGINES_PKG.User_Message (
2607 p_app_name => G_FEM
2608 ,p_msg_name => G_ENG_RS_RULE_PROCESSING_TXT
2609 ,p_token1 => 'RULE_NAME'
2610 ,p_value1 => x_rule_rec.rollup_obj_name
2611 );
2612
2613 if (p_request_rec.rollup_obj_type_code <> x_rule_rec.rollup_obj_type_code) then
2614
2615 FEM_ENGINES_PKG.User_Message (
2616 p_app_name => G_FEM
2617 ,p_msg_name => G_ENG_BAD_RS_OBJ_TYPE_ERR
2618 ,p_token1 => 'RS_OBJECT_TYPE_CODE'
2619 ,p_value1 => p_request_rec.rollup_obj_type_code
2620 ,p_token2 => 'OBJECT_TYPE_CODE'
2621 ,p_value2 => x_rule_rec.rollup_obj_type_code
2622 ,p_token3 => 'OBJECT_ID'
2623 ,p_value3 => x_rule_rec.rollup_obj_id
2624 );
2625 raise l_rule_prep_error;
2626
2627 end if;
2628
2629 if (p_request_rec.local_vs_combo_id <> x_rule_rec.local_vs_combo_id) then
2630
2631 FEM_ENGINES_PKG.User_Message (
2632 p_app_name => G_FEM
2633 ,p_msg_name => G_ENG_BAD_LCL_VS_COMBO_ERR
2634 ,p_token1 => 'OBJECT_TYPE_MEANING'
2635 ,p_value1 => Get_Object_Type_Name(x_rule_rec.rollup_obj_type_code)
2636 ,p_token2 => 'OBJECT_ID'
2637 ,p_value2 => x_rule_rec.rollup_obj_id
2638 );
2639 raise l_rule_prep_error;
2640
2641 end if;
2642
2643 end if;
2644
2645 ------------------------------------------------------------------------------
2646 -- Get the Rollup Object Definition ID
2647 ------------------------------------------------------------------------------
2648 if (x_rule_rec.rollup_obj_def_id is null) then
2649
2650 Get_Object_Definition (
2651 p_object_type_code => x_rule_rec.rollup_obj_type_code
2652 ,p_object_id => x_rule_rec.rollup_obj_id
2653 ,p_effective_date => p_request_rec.effective_date
2654 ,x_obj_def_id => x_rule_rec.rollup_obj_def_id
2655 );
2656
2657 end if;
2658
2659 ------------------------------------------------------------------------------
2660 -- Get Rollup Rule Definition Info
2661 ------------------------------------------------------------------------------
2662 begin
2663 execute immediate p_rollup_rule_def_stmt
2664 into x_rule_rec.hier_obj_id
2665 ,x_rule_rec.cond_obj_id
2666 ,x_rule_rec.entered_currency_code
2667 ,x_rule_rec.statistic_basis_id
2668 using x_rule_rec.rollup_obj_def_id;
2669 exception
2670 when others then
2671 FEM_ENGINES_PKG.User_Message (
2672 p_app_name => G_FEM
2673 ,p_msg_name => G_ENG_NO_OBJ_DEF_DTL_ERR
2674 ,p_token1 => 'TABLE_NAME'
2675 ,p_value1 => p_request_rec.rollup_rule_def_table
2676 ,p_token2 => 'OBJECT_TYPE_MEANING'
2677 ,p_value2 => Get_Object_Type_Name(x_rule_rec.rollup_obj_type_code)
2678 ,p_token3 => 'OBJECT_ID'
2679 ,p_value3 => x_rule_rec.rollup_obj_id
2680 ,p_token4 => 'OBJECT_DEF_ID'
2681 ,p_value4 => x_rule_rec.rollup_obj_def_id
2682 );
2683 raise l_rule_prep_error;
2684 end;
2685
2686 ------------------------------------------------------------------------------
2687 -- Get the Hierarchy Object Definition ID
2688 ------------------------------------------------------------------------------
2689 Get_Object_Definition (
2690 p_object_type_code => 'HIERARCHY'
2691 ,p_object_id => x_rule_rec.hier_obj_id
2692 ,p_effective_date => p_request_rec.effective_date
2693 ,x_obj_def_id => x_rule_rec.hier_obj_def_id
2694 );
2695
2696 ------------------------------------------------------------------------------
2697 -- Get the Condition Object Definition ID (if specified)
2698 ------------------------------------------------------------------------------
2699 x_rule_rec.cond_exists := (x_rule_rec.cond_obj_id is not null);
2700 if (x_rule_rec.cond_exists) then
2701
2702 Get_Object_Definition (
2703 p_object_type_code => 'CONDITION'
2704 ,p_object_id => x_rule_rec.cond_obj_id
2705 ,p_effective_date => p_request_rec.effective_date
2706 ,x_obj_def_id => x_rule_rec.cond_obj_def_id
2707 );
2708
2709 end if;
2710
2711 ------------------------------------------------------------------------------
2712 -- Get Dimension Id from FEM_HIERARCHIES
2713 ------------------------------------------------------------------------------
2714 begin
2715 select h.dimension_id
2716 into l_dimension_id
2717 from fem_hierarchies h
2718 where h.hierarchy_obj_id = x_rule_rec.hier_obj_id;
2719 exception
2720 when others then
2721 FEM_ENGINES_PKG.User_Message (
2722 p_app_name => G_FEM
2723 ,p_msg_name => G_ENG_NO_OBJ_DTL_ERR
2724 ,p_token1 => 'TABLE_NAME'
2725 ,p_value1 => 'FEM_HIERARCHIES'
2726 ,p_token2 => 'OBJECT_TYPE_MEANING'
2727 ,p_value2 => Get_Object_Type_Name(x_rule_rec.rollup_obj_type_code)
2728 ,p_token3 => 'OBJECT_ID'
2729 ,p_value3 => x_rule_rec.hier_obj_id
2730 );
2731 raise l_rule_prep_error;
2732 end;
2733
2734 -- Check that the dimension Id matches that of the Request
2735 if (p_request_rec.dimension_rec.dimension_id <> l_dimension_id) then
2736
2737 FEM_ENGINES_PKG.User_Message (
2738 p_app_name => G_FEM
2739 ,p_msg_name => G_ENG_BAD_HIER_DIM_ERR
2740 ,p_token1 => 'OBJECT_TYPE_MEANING'
2741 ,p_value1 => Get_Object_Type_Name(x_rule_rec.rollup_obj_type_code)
2742 ,p_token2 => 'OBJECT_ID'
2743 ,p_value2 => x_rule_rec.rollup_obj_id
2744 ,p_token3 => 'OBJECT_DEF_ID'
2745 ,p_value3 => x_rule_rec.rollup_obj_def_id
2746 );
2747 raise l_rule_prep_error;
2748
2749 end if;
2750
2751 if (p_request_rec.dimension_rec.dimension_varchar_label = 'COST_OBJECT') then
2752
2753 -- Use the hierarchy rollup table for the Cost Object Dimension. This
2754 -- is necessary as the Cost Object hierarchy is a DAG and needs a temporary
2755 -- table for flattening.
2756 x_rule_rec.hier_rollup_table := p_request_rec.dimension_rec.hier_rollup_table;
2757
2758 elsif (p_request_rec.dimension_rec.dimension_varchar_label = 'ACTIVITY') then
2759
2760 if (x_rule_rec.cond_exists) then
2761
2762 -- Use the hierarchy rollup table for the Activity Dimension if a
2763 -- Condition is specified. This is necessary as the Condition would
2764 -- restrict rollup processing to smaller subset of the entire Activity
2765 -- hierarchy.
2766 x_rule_rec.hier_rollup_table := p_request_rec.dimension_rec.hier_rollup_table;
2767
2768 else
2769
2770 -- Use the hierarchy table for the Activity Dimension if no Condition
2771 -- is specified. With no condition, the rollup processes the entire
2772 -- Activity hierarchy.
2773 x_rule_rec.hier_rollup_table := p_request_rec.dimension_rec.hier_table;
2774
2775 end if;
2776
2777 end if;
2778
2779 ------------------------------------------------------------------------------
2780 -- Set the Temporary Sequence Name for performing Rollup Processing in the
2781 -- FEM_BALANCES table.
2782 ------------------------------------------------------------------------------
2783 x_rule_rec.sequence_name :=
2784 'fem_ru_'||
2785 to_char(p_request_rec.request_id)||
2786 '_'||
2787 to_char(x_rule_rec.rollup_sequence)||
2788 '_s';
2789
2790 ------------------------------------------------------------------------------
2791 -- Set the Entered Currency Code and Exchange Rate params
2792 ------------------------------------------------------------------------------
2793 if (p_request_rec.entered_currency_flag = 'N') then
2794
2795 -- Set the Entered Currency to the Ledger's Functional Currency as the
2796 -- Ledger does not allow Entered Balances.
2797 -- Also default the exchange rate to null, so that all entered balances will
2798 -- result to null.
2799 x_rule_rec.entered_currency_code := p_request_rec.functional_currency_code;
2800 x_rule_rec.entered_exch_rate_num := null;
2801 x_rule_rec.entered_exch_rate_den := null;
2802 x_rule_rec.entered_exch_rate := null;
2803
2804 else
2805
2806 --todo: find what the "Functional" currency code string value will be.
2807 if (x_rule_rec.entered_currency_code = 'FUNCTIONAL' ) then
2808
2809 -- Set the Entered Currency to the Ledger's Functional Currency
2810 -- Also default the exchange rate to 1.
2811 x_rule_rec.entered_currency_code := p_request_rec.functional_currency_code;
2812 x_rule_rec.entered_exch_rate_num := 1;
2813 x_rule_rec.entered_exch_rate_den := 1;
2814 x_rule_rec.entered_exch_rate := 1;
2815
2816 else
2817
2818 if (x_rule_rec.entered_currency_code = p_request_rec.functional_currency_code) then
2819
2820 -- Default the exchange rate to 1 as the Entered Currency is the same as
2821 -- the Ledger's Functional Currency
2822 x_rule_rec.entered_exch_rate_num := 1;
2823 x_rule_rec.entered_exch_rate_den := 1;
2824 x_rule_rec.entered_exch_rate := 1;
2825
2826 else
2827
2828 begin
2829 GL_CURRENCY_API.Get_Triangulation_Rate (
2830 x_from_currency => p_request_rec.functional_currency_code
2831 ,x_to_currency => x_rule_rec.entered_currency_code
2832 ,x_conversion_date => p_request_rec.exch_rate_date
2833 ,x_conversion_type => g_currency_conv_type
2834 ,x_numerator => x_rule_rec.entered_exch_rate_num
2835 ,x_denominator => x_rule_rec.entered_exch_rate_den
2836 ,x_rate => x_rule_rec.entered_exch_rate
2837 );
2838 exception
2839 when GL_CURRENCY_API.NO_RATE then
2840 FEM_ENGINES_PKG.User_Message (
2841 p_app_name => G_FEM
2842 ,p_msg_name => G_ENG_NO_EXCH_RATE_ERR
2843 ,p_token1 => 'FROM_CURRENCY_CODE'
2844 ,p_value1 => p_request_rec.functional_currency_code
2845 ,p_token2 => 'TO_CURRENCY_CODE'
2846 ,p_value2 => x_rule_rec.entered_currency_code
2847 ,p_token3 => 'CONVERSION_DATE'
2848 ,p_value3 => FND_DATE.date_to_chardate(p_request_rec.exch_rate_date)
2849 ,p_token4 => 'CONVERSION_TYPE'
2850 ,p_value4 => g_currency_conv_type
2851 );
2852 raise l_rule_prep_error;
2853
2854 when GL_CURRENCY_API.INVALID_CURRENCY then
2855 FEM_ENGINES_PKG.User_Message (
2856 p_app_name => G_FEM
2857 ,p_msg_name => G_ENG_BAD_CURRENCY_ERR
2858 ,p_token1 => 'FROM_CURRENCY_CODE'
2859 ,p_value1 => p_request_rec.functional_currency_code
2860 ,p_token2 => 'TO_CURRENCY_CODE'
2861 ,p_value2 => x_rule_rec.entered_currency_code
2862 ,p_token3 => 'CONVERSION_DATE'
2863 ,p_value3 => FND_DATE.date_to_chardate(p_request_rec.exch_rate_date)
2864 ,p_token4 => 'CONVERSION_TYPE'
2865 ,p_value4 => g_currency_conv_type
2866 );
2867 raise l_rule_prep_error;
2868
2869 end;
2870
2871 end if;
2872
2873 end if;
2874
2875 end if;
2876
2877 -- Log all Rule Record Parameters if we have low level debugging
2878 if ( FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) ) then
2879
2880 FEM_ENGINES_PKG.Tech_Message (
2881 p_severity => G_LOG_LEVEL_1
2882 ,p_module => G_BLOCK||'.'||l_api_name||'.x_rule_rec'
2883 ,p_msg_text =>
2884 ' cond_obj_def_id='||x_rule_rec.cond_obj_def_id||
2885 ' cond_obj_id='||x_rule_rec.cond_obj_id||
2886 ' entered_currency_code='||x_rule_rec.entered_currency_code||
2887 ' entered_exch_rate='||x_rule_rec.entered_exch_rate||
2888 ' entered_exch_rate_den='||x_rule_rec.entered_exch_rate_den||
2889 ' entered_exch_rate_num='||x_rule_rec.entered_exch_rate_num||
2890 ' hier_obj_def_id='||x_rule_rec.hier_obj_def_id||
2891 ' hier_obj_id='||x_rule_rec.hier_obj_id||
2892 ' hier_rollup_table='||x_rule_rec.hier_rollup_table||
2893 ' local_vs_combo_id='||x_rule_rec.local_vs_combo_id||
2894 ' rollup_obj_def_id='||x_rule_rec.rollup_obj_def_id||
2895 ' rollup_obj_id='||x_rule_rec.rollup_obj_id||
2896 ' rollup_obj_type_code='||x_rule_rec.rollup_obj_type_code||
2897 ' rollup_sequence='||x_rule_rec.rollup_sequence||
2898 ' sequence_name='||x_rule_rec.sequence_name||
2899 ' statistic_basis_id='||x_rule_rec.statistic_basis_id
2900 );
2901
2902 end if;
2903
2904 FEM_ENGINES_PKG.Tech_Message (
2905 p_severity => G_LOG_LEVEL_2
2906 ,p_module => G_BLOCK||'.'||l_api_name
2907 ,p_msg_text => 'END'
2908 );
2909
2910 EXCEPTION
2911
2912 when l_rule_prep_error then
2913
2914 FEM_ENGINES_PKG.Tech_Message (
2915 p_severity => G_LOG_LEVEL_6
2916 ,p_module => G_BLOCK||'.'||l_api_name
2917 ,p_msg_text => 'Rule Preparation Exception'
2918 );
2919
2920 raise g_rollup_request_error;
2921
2922 END Rule_Prep;
2923
2924
2925
2926 /*============================================================================+
2927 | PROCEDURE
2928 | Sql_Stmts_Build
2929 |
2930 | DESCRIPTION
2931 | Dynamic SQL statement building for use in a rollup rule.
2932 |
2933 | SCOPE - PRIVATE
2934 |
2935 +============================================================================*/
2936
2937 PROCEDURE Sql_Stmts_Build (
2938 p_request_rec in request_record
2939 ,p_rule_rec in rule_record
2940 ,p_sql_rec in sql_record
2941 ,p_input_ds_b_where_clause in long
2942 ,p_input_ds_q_where_clause in long
2943 ,x_find_children_stmt out nocopy long
2944 ,x_rollup_parent_stmt out nocopy long
2945 ,x_find_child_chains_stmt out nocopy long
2946 ,x_num_of_input_rows_stmt out nocopy long
2947 )
2948 IS
2949
2950 l_api_name constant varchar2(30) := 'Sql_Stmts_Build';
2951
2952 l_dimension_rec dimension_record;
2953
2954 l_financial_elem_id_clause varchar2(255);
2955 l_line_item_id_clause varchar2(255);
2956
2957 l_sql_stmts_build_error exception;
2958
2959 BEGIN
2960
2961 FEM_ENGINES_PKG.Tech_Message (
2962 p_severity => G_LOG_LEVEL_2
2963 ,p_module => G_BLOCK||'.'||l_api_name
2964 ,p_msg_text => 'BEGIN'
2965 );
2966
2967 l_dimension_rec := p_request_rec.dimension_rec;
2968
2969 if (l_dimension_rec.dimension_varchar_label = 'COST_OBJECT') then
2970
2971 -- Build SQL statement for finding all Parent-Child Relationships
2972 x_find_children_stmt :=
2973 ' select h.child_id'||
2974 ' ,sum(nvl(q.child_qty/decode(q.parent_qty,0,null,q.parent_qty)/decode(q.yield_percentage,0,null,q.yield_percentage),0.00)) as weighting_pct'||
2975 ' ,h.child_ledger_id'||
2976 ' from '||p_rule_rec.hier_rollup_table||' h'||
2977 ' ,fem_cost_obj_hier_qty q'||
2978 ' where h.created_by_request_id = :b_request_id'||
2979 ' and h.created_by_object_id = :b_rollup_obj_id'||
2980 ' and h.parent_id = :b_parent_id'||
2981 ' and h.parent_depth_num = :b_parent_depth_num'||
2982 ' and q.relationship_id = h.relationship_id'||
2983 ' and '||p_input_ds_q_where_clause||
2984 ' group by h.child_ledger_id'||
2985 ' ,h.child_id';
2986
2987 -- Build SQL statement for performing the rollup
2988 --todo: precision 38?
2989 x_rollup_parent_stmt :=
2990 ' merge into fem_balances bp'||
2991 ' using ('||
2992 ' select :b_source_system_code as source_system_code'||--new
2993 ' ,:b_currency_code as currency_code'||--new
2994 ' ,b.currency_type_code'||
2995 ' ,parent.cost_object_id'||
2996 p_sql_rec.comp_dim_comp_cols_using||
2997 p_sql_rec.comp_dim_data_cols_using||
2998 ' ,sum(b.xtd_balance_f) xtd_balance_f'||
2999 ' from fem_balances b'||
3000 ' ,fem_cost_objects parent'||
3001 ' where b.cost_object_id = :b_child_id'||
3002 ' and parent.cost_object_id = :b_parent_id'||
3003 ' and b.currency_type_code = ''ENTERED'''||
3004 ' and '||p_input_ds_b_where_clause||
3005 ' group by b.currency_type_code'||
3006 ' ,parent.cost_object_id'||
3007 p_sql_rec.comp_dim_comp_cols_using||
3008 p_sql_rec.comp_dim_data_cols_using||
3009 ' ) bc'||
3010 ' on ('||
3011 ' bp.source_system_code = bc.source_system_code'||
3012 ' and bp.currency_code = bc.currency_code'||
3013 ' and bp.currency_type_code = bc.currency_type_code'||
3014 ' and bp.cost_object_id = bc.cost_object_id'||
3015 p_sql_rec.comp_dim_data_cols_on||
3016 ' and bp.dataset_code = :b_output_dataset_code'||
3017 ' and bp.cal_period_id = :b_output_cal_period_id'||
3018 ' and bp.created_by_request_id = :b_request_id'||
3019 ' and bp.created_by_object_id = :b_rollup_obj_id'||
3020 ' )'||
3021 ' when matched then'||
3022 ' update set'||
3023 ' bp.xtd_balance_e = bp.xtd_balance_e + ( round((bc.xtd_balance_f * :b_weighting_pct),37) / :b_child_exch_rate_den * :b_child_exch_rate_num / :b_entered_exch_rate_den * :b_entered_exch_rate_num)'||
3024 ' ,bp.xtd_balance_f = bp.xtd_balance_f + ( round((bc.xtd_balance_f * :b_weighting_pct),37) / :b_child_exch_rate_den * :b_child_exch_rate_num )'||
3025 ' ,bp.last_updated_by_request_id = :b_request_id'||
3026 ' ,bp.last_updated_by_object_id = :b_rollup_obj_id'||
3027 ' when not matched then'||
3028 ' insert ('||
3029 ' bp.dataset_code'||
3030 ' ,bp.cal_period_id'||
3031 ' ,bp.creation_row_sequence'||
3032 ' ,bp.source_system_code'||
3033 ' ,bp.currency_code'||
3034 ' ,bp.currency_type_code'||
3035 ' ,bp.cost_object_id'||
3036 p_sql_rec.comp_dim_comp_cols_insert||
3037 p_sql_rec.comp_dim_data_cols_insert||
3038 ' ,bp.created_by_request_id'||
3039 ' ,bp.created_by_object_id'||
3040 ' ,bp.last_updated_by_request_id'||
3041 ' ,bp.last_updated_by_object_id'||
3042 ' ,bp.xtd_balance_e'||
3043 ' ,bp.xtd_balance_f'||
3044 ' )'||
3045 ' values'||
3046 ' ('||
3047 ' :b_output_dataset_code'||
3048 ' ,:b_output_cal_period_id'||
3049 ' ,'||p_rule_rec.sequence_name||'.NEXTVAL'||
3050 ' ,bc.source_system_code'||
3051 ' ,bc.currency_code'||
3052 ' ,bc.currency_type_code'||
3053 ' ,bc.cost_object_id'||
3054 p_sql_rec.comp_dim_comp_cols_values||
3055 p_sql_rec.comp_dim_data_cols_values||
3056 ' ,:b_request_id'||
3057 ' ,:b_rollup_obj_id'||
3058 ' ,:b_request_id'||
3059 ' ,:b_rollup_obj_id'||
3060 ' ,round((bc.xtd_balance_f * :b_weighting_pct),37) / :b_child_exch_rate_den * :b_child_exch_rate_num / :b_entered_exch_rate_den * :b_entered_exch_rate_num'||
3061 ' ,round((bc.xtd_balance_f * :b_weighting_pct),37) / :b_child_exch_rate_den * :b_child_exch_rate_num'||
3062 ' )';
3063
3064 x_find_child_chains_stmt :=
3065 ' select distinct created_by_request_id'||
3066 ' ,created_by_object_id'||
3067 ' from fem_balances b'||
3068 ' where b.currency_type_code = ''ENTERED'''||
3069 ' and '||p_input_ds_b_where_clause||
3070 ' and b.cost_object_id = :b_child_id'||
3071 ' and not ('||
3072 ' b.created_by_request_id = :b_request_id'||
3073 ' and b.created_by_object_id = :b_rollup_obj_id'||
3074 ' )'||
3075 ' and not exists ('||
3076 ' select 1'||
3077 ' from fem_pl_chains c'||
3078 ' where c.request_id = :b_request_id'||
3079 ' and c.object_id = :b_rollup_obj_id'||
3080 ' and c.source_created_by_request_id = b.created_by_request_id'||
3081 ' and c.source_created_by_object_id = b.created_by_object_id'||
3082 ' )';
3083
3084 x_num_of_input_rows_stmt :=
3085 ' select count(*)'||
3086 ' from fem_balances b'||
3087 ' where b.currency_type_code = ''ENTERED'''||
3088 ' and '||p_input_ds_b_where_clause||
3089 ' and not ('||
3090 ' created_by_request_id = :b_request_id'||
3091 ' and created_by_object_id = :b_rollup_obj_id'||
3092 ' )'||
3093 ' and exists ('||
3094 ' select 1'||
3095 ' from fem_ru_nodes_t n'||
3096 ' where n.created_by_request_id = :b_request_id'||
3097 ' and n.created_by_object_id = :b_rollup_obj_id'||
3098 ' and n.node_id = b.cost_object_id'||
3099 ' and n.costed_flag = ''Y'''||
3100 ' and n.root_flag = ''N'''||
3101 ' and n.condition_flag = ''N'''||
3102 ' )';
3103
3104 elsif (l_dimension_rec.dimension_varchar_label = 'ACTIVITY') then
3105
3106 -- Build SQL statement for finding all Parent-Child Relationships
3107 x_find_children_stmt :=
3108 ' select h.child_id'||
3109 ' ,nvl(h.weighting_pct,1.00) as weighting_pct'||
3110 ' ,null as child_ledger_id'||
3111 ' from '||p_rule_rec.hier_rollup_table||' h'||
3112 ' where h.hierarchy_obj_def_id = :b_hier_obj_def_id'||
3113 ' and h.child_id <> h.parent_id'||
3114 ' and h.single_depth_flag = ''Y'''||
3115 ' and h.parent_id = :b_parent_id'||
3116 ' and h.parent_depth_num = :b_parent_depth_num';
3117
3118 -- If condition exists, we must include request_id and rollup_obj_id in the
3119 -- where clause for querying FEM_RU_ACTIVITIES_HIER_T
3120 if (p_rule_rec.cond_exists) then
3121
3122 x_find_children_stmt := x_find_children_stmt ||
3123 ' and h.created_by_request_id = :b_request_id'||
3124 ' and h.created_by_object_id = :b_rollup_obj_id';
3125
3126 end if;
3127
3128 if (p_request_rec.rollup_type_code = 'COST') then
3129 l_financial_elem_id_clause := 'b.financial_elem_id not in ('||
3130 G_FIN_ELEM_ID_STATISTIC||','||G_FIN_ELEM_ID_ACTIVITY_RATE||')';
3131 l_line_item_id_clause := '1=1';
3132 elsif (p_request_rec.rollup_type_code = 'STAT') then
3133 l_financial_elem_id_clause := 'b.financial_elem_id = '||
3134 G_FIN_ELEM_ID_STATISTIC;
3135 l_line_item_id_clause := 'b.line_item_id = :b_statistic_basis_id';
3136 end if;
3137
3138 -- Build SQL statement for performing the rollup
3139 x_rollup_parent_stmt :=
3140 ' merge into fem_balances bp'||
3141 ' using ('||
3142 ' select :b_source_system_code as source_system_code'||--new
3143 ' ,:b_currency_code as currency_code'||--new
3144 ' ,b.currency_type_code'||
3145 ' ,b.ledger_id'||
3146 ' ,parent.activity_id'||
3147 p_sql_rec.comp_dim_comp_cols_using||
3148 p_sql_rec.comp_dim_data_cols_using||
3149 ' ,sum(b.xtd_balance_f) xtd_balance_f'||
3150 ' from fem_balances b'||
3151 ' ,fem_activities parent'||
3152 ' where b.activity_id = :b_child_id'||
3153 ' and parent.activity_id = :b_parent_id'||
3154 ' and b.ledger_id = :b_ledger_id'||
3155 ' and b.currency_type_code = ''ENTERED'''||
3156 ' and '||l_financial_elem_id_clause||
3157 ' and '||l_line_item_id_clause||
3158 ' and '||p_input_ds_b_where_clause||
3159 ' group by b.currency_type_code'||
3160 ' ,b.ledger_id'||
3161 ' ,parent.activity_id'||
3162 p_sql_rec.comp_dim_comp_cols_using||
3163 p_sql_rec.comp_dim_data_cols_using||
3164 ' ) bc'||
3165 ' on ('||
3166 ' bp.source_system_code = bc.source_system_code'||
3167 ' and bp.currency_code = bc.currency_code'||
3168 ' and bp.currency_type_code = bc.currency_type_code'||
3169 ' and bp.ledger_id = bc.ledger_id'||
3170 ' and bp.activity_id = bc.activity_id'||
3171 p_sql_rec.comp_dim_data_cols_on||
3172 ' and bp.dataset_code = :b_output_dataset_code'||
3173 ' and bp.cal_period_id = :b_output_cal_period_id'||
3174 ' and bp.created_by_request_id = :b_request_id'||
3175 ' and bp.created_by_object_id = :b_rollup_obj_id'||
3176 ' )'||
3177 ' when matched then'||
3178 ' update set'||
3179 ' bp.xtd_balance_e = bp.xtd_balance_e + ( bc.xtd_balance_f * :b_weighting_pct / :b_entered_exch_rate_den * :b_entered_exch_rate_num)'||
3180 ' ,bp.xtd_balance_f = bp.xtd_balance_f + ( bc.xtd_balance_f * :b_weighting_pct)'||
3181 ' ,bp.last_updated_by_request_id = :b_request_id'||
3182 ' ,bp.last_updated_by_object_id = :b_rollup_obj_id'||
3183 ' when not matched then'||
3184 ' insert ('||
3185 ' bp.dataset_code'||
3186 ' ,bp.cal_period_id'||
3187 ' ,bp.creation_row_sequence'||
3188 ' ,bp.source_system_code'||
3189 ' ,bp.currency_code'||
3190 ' ,bp.currency_type_code'||
3191 ' ,bp.ledger_id'||
3192 ' ,bp.activity_id'||
3193 p_sql_rec.comp_dim_comp_cols_insert||
3194 p_sql_rec.comp_dim_data_cols_insert||
3195 ' ,bp.created_by_request_id'||
3196 ' ,bp.created_by_object_id'||
3197 ' ,bp.last_updated_by_request_id'||
3198 ' ,bp.last_updated_by_object_id'||
3199 ' ,bp.xtd_balance_e'||
3200 ' ,bp.xtd_balance_f'||
3201 ' )'||
3202 ' values'||
3203 ' ('||
3204 ' :b_output_dataset_code'||
3205 ' ,:b_output_cal_period_id'||
3206 ' ,'||p_rule_rec.sequence_name||'.NEXTVAL'||
3207 ' ,bc.source_system_code'||
3208 ' ,bc.currency_code'||
3209 ' ,bc.currency_type_code'||
3210 ' ,bc.ledger_id'||
3211 ' ,bc.activity_id'||
3212 p_sql_rec.comp_dim_comp_cols_values||
3213 p_sql_rec.comp_dim_data_cols_values||
3214 ' ,:b_request_id'||
3215 ' ,:b_rollup_obj_id'||
3216 ' ,:b_request_id'||
3217 ' ,:b_rollup_obj_id'||
3218 ' ,bc.xtd_balance_f * :b_weighting_pct / :b_entered_exch_rate_den * :b_entered_exch_rate_num'||
3219 ' ,bc.xtd_balance_f * :b_weighting_pct'||
3220 ' )';
3221
3222 x_find_child_chains_stmt :=
3223 ' select distinct created_by_request_id'||
3224 ' ,created_by_object_id'||
3225 ' from fem_balances b'||
3226 ' where b.ledger_id = :b_ledger_id'||
3227 ' and b.currency_type_code = ''ENTERED'''||
3228 ' and '||l_financial_elem_id_clause||
3229 ' and '||l_line_item_id_clause||
3230 ' and '||p_input_ds_b_where_clause||
3231 ' and b.activity_id = :b_child_id'||
3232 ' and not ('||
3233 ' b.created_by_request_id = :b_request_id'||
3234 ' and b.created_by_object_id = :b_rollup_obj_id'||
3235 ' )'||
3236 ' and not exists ('||
3237 ' select 1'||
3238 ' from fem_pl_chains c'||
3239 ' where c.request_id = :b_request_id'||
3240 ' and c.object_id = :b_rollup_obj_id'||
3241 ' and c.source_created_by_request_id = b.created_by_request_id'||
3242 ' and c.source_created_by_object_id = b.created_by_object_id'||
3243 ' )';
3244
3245 x_num_of_input_rows_stmt :=
3246 ' select count(*)'||
3247 ' from fem_balances b'||
3248 ' where b.ledger_id = :b_ledger_id'||
3249 ' and b.currency_type_code = ''ENTERED'''||
3250 ' and '||l_financial_elem_id_clause||
3251 ' and '||l_line_item_id_clause||
3252 ' and '||p_input_ds_b_where_clause||
3253 ' and not ('||
3254 ' created_by_request_id = :b_request_id'||
3255 ' and created_by_object_id = :b_rollup_obj_id'||
3256 ' )'||
3257 ' and exists ('||
3258 ' select 1'||
3259 ' from fem_ru_nodes_t n'||
3260 ' where n.created_by_request_id = :b_request_id'||
3261 ' and n.created_by_object_id = :b_rollup_obj_id'||
3262 ' and n.node_id = b.activity_id'||
3263 ' and n.costed_flag = ''Y'''||
3264 ' and n.root_flag = ''N'''||
3265 ' and n.condition_flag = ''N'''||
3266 ' )';
3267
3268 end if;
3269
3270 FEM_ENGINES_PKG.Tech_Message (
3271 p_severity => G_LOG_LEVEL_1
3272 ,p_module => G_BLOCK||'.'||l_api_name||'.x_find_children_stmt'
3273 ,p_msg_text => x_find_children_stmt
3274 );
3275
3276 FEM_ENGINES_PKG.Tech_Message (
3277 p_severity => G_LOG_LEVEL_1
3278 ,p_module => G_BLOCK||'.'||l_api_name||'.x_rollup_parent_stmt'
3279 ,p_msg_text => x_rollup_parent_stmt
3280 );
3281
3282 FEM_ENGINES_PKG.Tech_Message (
3283 p_severity => G_LOG_LEVEL_1
3284 ,p_module => G_BLOCK||'.'||l_api_name||'.x_find_child_chains_stmt'
3285 ,p_msg_text => x_find_child_chains_stmt
3286 );
3287
3288 FEM_ENGINES_PKG.Tech_Message (
3289 p_severity => G_LOG_LEVEL_1
3290 ,p_module => G_BLOCK||'.'||l_api_name||'.x_num_of_input_rows_stmt'
3291 ,p_msg_text => x_num_of_input_rows_stmt
3292 );
3293
3294 FEM_ENGINES_PKG.Tech_Message (
3295 p_severity => G_LOG_LEVEL_2
3296 ,p_module => G_BLOCK||'.'||l_api_name
3297 ,p_msg_text => 'END'
3298 );
3299
3300 EXCEPTION
3301
3302 when l_sql_stmts_build_error then
3303
3304 FEM_ENGINES_PKG.Tech_Message (
3305 p_severity => G_LOG_LEVEL_6
3306 ,p_module => G_BLOCK||'.'||l_api_name
3307 ,p_msg_text => 'SQL Statements Build Exception'
3308 );
3309
3310 raise g_rollup_request_error;
3311
3312 END Sql_Stmts_Build;
3313
3314
3315
3316 /*============================================================================+
3317 | PROCEDURE
3318 | Register_Rule
3319 |
3320 | DESCRIPTION
3321 | Registers the rule in the processing locks tables. This includes
3322 | registering the rollup object execution, its dependent object
3323 | definitions, and any output tables.
3324 |
3325 | SCOPE - PRIVATE
3326 |
3327 +============================================================================*/
3328
3329 PROCEDURE Register_Rule (
3330 p_request_rec in request_record
3331 ,p_rule_rec in rule_record
3332 )
3333 IS
3334
3335 l_api_name constant varchar2(30) := 'Register_Rule';
3336
3337 l_exec_state varchar2(30); -- normal, restart, rerun
3338 l_prev_request_id number;
3339
3340 l_return_status t_return_status%TYPE;
3341 l_msg_count t_msg_count%TYPE;
3342 l_msg_data t_msg_data%TYPE;
3343
3344 l_register_rule_error exception;
3345
3346 BEGIN
3347
3348 FEM_ENGINES_PKG.Tech_Message (
3349 p_severity => G_LOG_LEVEL_2
3350 ,p_module => G_BLOCK||'.'||l_api_name
3351 ,p_msg_text => 'BEGIN'
3352 );
3353
3354 savepoint register_rule_pub;
3355
3356 -- Call the FEM_PL_PKG.Register_Object_Execution API procedure to register
3357 -- the rollup object execution in FEM_PL_OBJECT_EXECUTIONS, thus obtaining
3358 -- an execution lock.
3359 FEM_PL_PKG.Register_Object_Execution (
3360 p_api_version => 1.0
3361 ,p_commit => FND_API.G_FALSE
3362 ,p_request_id => p_request_rec.request_id
3363 ,p_object_id => p_rule_rec.rollup_obj_id
3364 ,p_exec_object_definition_id => p_rule_rec.rollup_obj_def_id
3365 ,p_user_id => p_request_rec.user_id
3366 ,p_last_update_login => p_request_rec.login_id
3367 ,p_exec_mode_code => null
3368 ,x_exec_state => l_exec_state
3369 ,x_prev_request_id => l_prev_request_id
3370 ,x_msg_count => l_msg_count
3371 ,x_msg_data => l_msg_data
3372 ,x_return_status => l_return_status
3373 );
3374
3375 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
3376 Get_Put_Messages (
3377 p_msg_count => l_msg_count
3378 ,p_msg_data => l_msg_data
3379 );
3380 raise l_register_rule_error;
3381 end if;
3382
3383 -- Register the Dataset Group Object Definition
3384 Register_Object_Definition (
3385 p_request_rec => p_request_rec
3386 ,p_rule_rec => p_rule_rec
3387 ,p_obj_def_id => p_request_rec.dataset_grp_obj_def_id
3388 );
3389
3390 -- Register all the Dependent Objects for the Rollup Object Definition
3391 FEM_PL_PKG.Register_Dependent_ObjDefs (
3392 p_api_version => 1.0
3393 ,p_commit => FND_API.G_FALSE
3394 ,p_request_id => p_request_rec.request_id
3395 ,p_object_id => p_rule_rec.rollup_obj_id
3396 ,p_exec_object_definition_id => p_rule_rec.rollup_obj_def_id
3397 ,p_effective_date => p_request_rec.effective_date
3398 ,p_user_id => p_request_rec.user_id
3399 ,p_last_update_login => p_request_rec.login_id
3400 ,x_msg_count => l_msg_count
3401 ,x_msg_data => l_msg_data
3402 ,x_return_status => l_return_status
3403 );
3404
3405 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
3406 Get_Put_Messages (
3407 p_msg_count => l_msg_count
3408 ,p_msg_data => l_msg_data
3409 );
3410 raise l_register_rule_error;
3411 end if;
3412
3413 -- Register the data location for the FEM_BALANCES output table
3414 FEM_DIMENSION_UTIL_PKG.Register_Data_Location (
3415 p_request_id => p_request_rec.request_id
3416 ,p_object_id => p_rule_rec.rollup_obj_id
3417 ,p_table_name => 'FEM_BALANCES'
3418 ,p_ledger_id => p_request_rec.ledger_id
3419 ,p_cal_per_id => p_request_rec.output_cal_period_id
3420 ,p_dataset_cd => p_request_rec.output_dataset_code
3421 ,p_source_cd => p_request_rec.source_system_code
3422 ,p_load_status => null
3423 );
3424
3425 -- Register the FEM_BALANCES output table as INSERT.
3426 --
3427 -- NOTE: Eventhough we create output data in FEM_BALANCES through a MERGE
3428 -- statement, we are not updating records from other CREATED_BY_REQUEST_ID
3429 -- and CREATED_BY_OBJECT_ID combinations. We are using the MERGE statement
3430 -- to insert or update rows such that:
3431 --
3432 -- CREATED_BY_REQUEST_ID = p_request_rec.request_id
3433 -- and CREATED_BY_OBJECT_ID = p_rule_rec.rollup_obj_id
3434 --
3435 -- We must therefore register the FEM_BALANCES output table as INSERT so that
3436 -- Undo will simply delete all output records, rather than zero the balance
3437 -- columns. And since we are registering FEM_BALANCES as INSERT, we do not
3438 -- need to register the updated columns for the Undo functionality.
3439 Register_Table (
3440 p_request_rec => p_request_rec
3441 ,p_rule_rec => p_rule_rec
3442 ,p_table_name => 'FEM_BALANCES'
3443 ,p_statement_type => 'INSERT'
3444 );
3445
3446 -- Register the FEM_RU_NODES_T processing table as INSERT. This is needed
3447 -- in the event of a engine failure where the only way to purge these records
3448 -- is through the Undo Process
3449 Register_Table (
3450 p_request_rec => p_request_rec
3451 ,p_rule_rec => p_rule_rec
3452 ,p_table_name => 'FEM_RU_NODES_T'
3453 ,p_statement_type => 'INSERT'
3454 );
3455
3456 if (p_request_rec.dimension_rec.dimension_varchar_label = 'COST_OBJECT') then
3457
3458 -- Register the FEM_RU_COST_OBJ_HIER_T processing table as INSERT.
3459 -- This is needed in the event of a engine failure where the only way to
3460 -- purge these records is through the Undo Process
3461 Register_Table (
3462 p_request_rec => p_request_rec
3463 ,p_rule_rec => p_rule_rec
3464 ,p_table_name => 'FEM_RU_COST_OBJ_HIER_T'
3465 ,p_statement_type => 'INSERT'
3466 );
3467
3468 elsif ( (p_request_rec.dimension_rec.dimension_varchar_label = 'ACTIVITY')
3469 and (p_rule_rec.cond_exists) ) then
3470
3471 -- Register the FEM_RU_ACTIVITIES_HIER_T processing table as INSERT.
3472 -- This is needed in the event of a engine failure where the only way to
3473 -- purge these records is through the Undo Process
3474 Register_Table (
3475 p_request_rec => p_request_rec
3476 ,p_rule_rec => p_rule_rec
3477 ,p_table_name => 'FEM_RU_ACTIVITIES_HIER_T'
3478 ,p_statement_type => 'INSERT'
3479 );
3480
3481 end if;
3482
3483 commit;
3484
3485 FEM_ENGINES_PKG.Tech_Message (
3486 p_severity => G_LOG_LEVEL_2
3487 ,p_module => G_BLOCK||'.'||l_api_name
3488 ,p_msg_text => 'END'
3489 );
3490
3491 EXCEPTION
3492
3493 when l_register_rule_error then
3494
3495 rollback to register_rule_pub;
3496
3497 FEM_ENGINES_PKG.Tech_Message (
3498 p_severity => G_LOG_LEVEL_6
3499 ,p_module => G_BLOCK||'.'||l_api_name
3500 ,p_msg_text => 'Register Rule Exception'
3501 );
3502
3503 raise g_rollup_request_error;
3504
3505 when g_rollup_request_error then
3506
3507 rollback to register_rule_pub;
3508 raise g_rollup_request_error;
3509
3510 when others then
3511
3512 rollback to register_rule_pub;
3513 raise;
3514
3515 END Register_Rule;
3516
3517
3518
3519 /*============================================================================+
3520 | PROCEDURE
3521 | Register_Object_Definition
3522 |
3523 | DESCRIPTION
3524 | Registers the specified object definition.
3525 |
3526 | SCOPE - PRIVATE
3527 |
3528 +============================================================================*/
3529
3530 PROCEDURE Register_Object_Definition (
3531 p_request_rec in request_record
3532 ,p_rule_rec in rule_record
3533 ,p_obj_def_id in number
3534 )
3535 IS
3536
3537 l_api_name constant varchar2(30) := 'Register_Object_Definition';
3538
3539 l_return_status t_return_status%TYPE;
3540 l_msg_count t_msg_count%TYPE;
3541 l_msg_data t_msg_data%TYPE;
3542
3543 l_register_obj_def_error exception;
3544
3545 BEGIN
3546
3547 FEM_ENGINES_PKG.Tech_Message (
3548 p_severity => G_LOG_LEVEL_2
3549 ,p_module => G_BLOCK||'.'||l_api_name
3550 ,p_msg_text => 'BEGIN'
3551 );
3552
3553 -- Call the FEM_PL_PKG.Register_Object_Def API procedure to register
3554 -- the specified object definition in FEM_PL_OBJECT_DEFS, thus obtaining
3555 -- an object definition lock.
3556 FEM_PL_PKG.Register_Object_Def (
3557 p_api_version => 1.0
3558 ,p_commit => FND_API.G_FALSE
3559 ,p_request_id => p_request_rec.request_id
3560 ,p_object_id => p_rule_rec.rollup_obj_id
3561 ,p_object_definition_id => p_obj_def_id
3562 ,p_user_id => p_request_rec.user_id
3563 ,p_last_update_login => p_request_rec.login_id
3564 ,x_msg_count => l_msg_count
3565 ,x_msg_data => l_msg_data
3566 ,x_return_status => l_return_status
3567 );
3568
3569 -- Object Definition Lock exists
3570 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
3571 Get_Put_Messages (
3572 p_msg_count => l_msg_count
3573 ,p_msg_data => l_msg_data
3574 );
3575 raise l_register_obj_def_error;
3576 end if;
3577
3578 FEM_ENGINES_PKG.Tech_Message (
3579 p_severity => G_LOG_LEVEL_2
3580 ,p_module => G_BLOCK||'.'||l_api_name
3581 ,p_msg_text => 'END'
3582 );
3583
3584 EXCEPTION
3585
3586 when l_register_obj_def_error then
3587
3588 FEM_ENGINES_PKG.Tech_Message (
3589 p_severity => G_LOG_LEVEL_6
3590 ,p_module => G_BLOCK||'.'||l_api_name
3591 ,p_msg_text => 'Register Object Definition Exception'
3592 );
3593
3594 raise g_rollup_request_error;
3595
3596 END Register_Object_Definition;
3597
3598
3599
3600 /*============================================================================+
3601 | PROCEDURE
3602 | Register_Table
3603 |
3604 | DESCRIPTION
3605 | Registers the specified table name and statement type
3606 |
3607 | SCOPE - PRIVATE
3608 |
3609 +============================================================================*/
3610
3611 PROCEDURE Register_Table (
3612 p_request_rec in request_record
3613 ,p_rule_rec in rule_record
3614 ,p_table_name in varchar2
3615 ,p_statement_type in varchar2
3616 )
3617 IS
3618
3619 l_api_name constant varchar2(30) := 'Register_Table';
3620
3621 l_return_status t_return_status%TYPE;
3622 l_msg_count t_msg_count%TYPE;
3623 l_msg_data t_msg_data%TYPE;
3624
3625 l_register_table_error exception;
3626
3627 BEGIN
3628
3629 FEM_ENGINES_PKG.Tech_Message (
3630 p_severity => G_LOG_LEVEL_2
3631 ,p_module => G_BLOCK||'.'||l_api_name
3632 ,p_msg_text => 'BEGIN'
3633 );
3634
3635 -- Call the FEM_PL_PKG.Register_Table API procedure to register
3636 -- the specified output table and the statement type that will be used.
3637 FEM_PL_PKG.Register_Table (
3638 p_api_version => 1.0
3639 ,p_commit => FND_API.G_FALSE
3640 ,p_request_id => p_request_rec.request_id
3641 ,p_object_id => p_rule_rec.rollup_obj_id
3642 ,p_table_name => p_table_name
3643 ,p_statement_type => p_statement_type
3644 ,p_num_of_output_rows => 0
3645 ,p_user_id => p_request_rec.user_id
3646 ,p_last_update_login => p_request_rec.login_id
3647 ,x_msg_count => l_msg_count
3648 ,x_msg_data => l_msg_data
3649 ,x_return_status => l_return_status
3650 );
3651
3652 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
3653 Get_Put_Messages (
3654 p_msg_count => l_msg_count
3655 ,p_msg_data => l_msg_data
3656 );
3657 raise l_register_table_error;
3658 end if;
3659
3660 FEM_ENGINES_PKG.Tech_Message (
3661 p_severity => G_LOG_LEVEL_2
3662 ,p_module => G_BLOCK||'.'||l_api_name
3663 ,p_msg_text => 'END'
3664 );
3665
3666 EXCEPTION
3667
3668 when l_register_table_error then
3669
3670 FEM_ENGINES_PKG.Tech_Message (
3671 p_severity => G_LOG_LEVEL_6
3672 ,p_module => G_BLOCK||'.'||l_api_name
3673 ,p_msg_text => 'Register Table Exception'
3674 );
3675
3676 raise g_rollup_request_error;
3677
3678 END Register_Table;
3679
3680
3681
3682 /*============================================================================+
3683 | PROCEDURE
3684 | Create_Temp_Objects
3685 |
3686 | DESCRIPTION
3687 | Creates all the temporary objects necessary for processing a rollup rule.
3688 |
3689 | SCOPE - PRIVATE
3690 |
3691 +============================================================================*/
3692
3693 PROCEDURE Create_Temp_Objects (
3694 p_request_rec in request_record
3695 ,p_rule_rec in rule_record
3696 )
3697 IS
3698
3699 l_api_name constant varchar2(30) := 'Create_Temp_Objects';
3700
3701 l_return_status t_return_status%TYPE;
3702 l_msg_count t_msg_count%TYPE;
3703 l_msg_data t_msg_data%TYPE;
3704
3705 l_create_temp_objects_error exception;
3706
3707 BEGIN
3708
3709 FEM_ENGINES_PKG.Tech_Message (
3710 p_severity => G_LOG_LEVEL_2
3711 ,p_module => G_BLOCK||'.'||l_api_name
3712 ,p_msg_text => 'BEGIN'
3713 );
3714
3715 ------------------------------------------------------------------------------
3716 -- Create Temporary Sequence for peforming Rollup Processing in FEM_BALANCES.
3717 ------------------------------------------------------------------------------
3718 begin
3719 -- Temporary sequence is in the default APPS schema as GSCC does not
3720 -- allow hardcoded schemas.
3721 execute immediate 'create sequence '||p_rule_rec.sequence_name;
3722 exception
3723 when others then
3724 FEM_ENGINES_PKG.User_Message (
3725 p_app_name => G_FEM
3726 ,p_msg_name => G_ENG_CREATE_SEQUENCE_ERR
3727 ,p_token1 => 'SEQUENCE_NAME'
3728 ,p_value1 => p_rule_rec.sequence_name
3729 );
3730 raise l_create_temp_objects_error;
3731 end;
3732
3733 -- Register Temp Sequence in PL Framework
3734 FEM_PL_PKG.Register_Temp_Object (
3735 p_api_version => 1.0
3736 ,p_commit => FND_API.G_FALSE
3737 ,p_request_id => p_request_rec.request_id
3738 ,p_object_id => p_rule_rec.rollup_obj_id
3739 ,p_object_type => 'SEQUENCE'
3740 ,p_object_name => p_rule_rec.sequence_name
3741 ,p_user_id => p_request_rec.user_id
3742 ,p_last_update_login => p_request_rec.login_id
3743 ,x_return_status => l_return_status
3744 ,x_msg_count => l_msg_count
3745 ,x_msg_data => l_msg_data
3746 );
3747
3748 -- Check return status
3749 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
3750 Get_Put_Messages (
3751 p_msg_count => l_msg_count
3752 ,p_msg_data => l_msg_data
3753 );
3754 raise l_create_temp_objects_error;
3755 end if;
3756
3757 commit;
3758
3759 FEM_ENGINES_PKG.Tech_Message (
3760 p_severity => G_LOG_LEVEL_2
3761 ,p_module => G_BLOCK||'.'||l_api_name
3762 ,p_msg_text => 'END'
3763 );
3764
3765 EXCEPTION
3766
3767 when l_create_temp_objects_error then
3768
3769 FEM_ENGINES_PKG.Tech_Message (
3770 p_severity => G_LOG_LEVEL_6
3771 ,p_module => G_BLOCK||'.'||l_api_name
3772 ,p_msg_text => 'Create Temporary Objects Exception'
3773 );
3774
3775 raise g_rollup_request_error;
3776
3777 END Create_Temp_Objects;
3778
3779
3780
3781 /*============================================================================+
3782 | PROCEDURE
3783 | Drop_Temp_Objects
3784 |
3785 | DESCRIPTION
3786 | Drops all the temporary objects that were created for processing a rollup
3787 | rule.
3788 |
3789 | SCOPE - PRIVATE
3790 |
3791 +============================================================================*/
3792
3793 PROCEDURE Drop_Temp_Objects (
3794 p_request_rec in request_record
3795 ,p_rule_rec in rule_record
3796 )
3797 IS
3798
3799 l_api_name constant varchar2(30) := 'Drop_Temp_Objects';
3800
3801 l_object_exists_flag varchar2(1);
3802 l_completion_status boolean;
3803
3804 l_drop_temp_objects_error exception;
3805
3806 BEGIN
3807
3808 FEM_ENGINES_PKG.Tech_Message (
3809 p_severity => G_LOG_LEVEL_2
3810 ,p_module => G_BLOCK||'.'||l_api_name
3811 ,p_msg_text => 'BEGIN'
3812 );
3813
3814 ------------------------------------------------------------------------------
3815 -- Drop Temporary Sequence for peforming Rollup Processing on FEM_BALANCES.
3816 ------------------------------------------------------------------------------
3817 begin
3818 select 'Y'
3819 into l_object_exists_flag
3820 from fem_pl_temp_objects
3821 where request_id = p_request_rec.request_id
3822 and object_id = p_rule_rec.rollup_obj_id
3823 and object_type = 'SEQUENCE'
3824 and object_name = p_rule_rec.sequence_name;
3825 exception
3826 when no_data_found then
3827 l_object_exists_flag := 'N';
3828 end;
3829
3830 if (l_object_exists_flag = 'Y') then
3831
3832 begin
3833
3834 -- Temporary sequence is in the default APPS schema as GSCC does not
3835 -- allow hardcoded schemas.
3836 execute immediate 'drop sequence '||p_rule_rec.sequence_name;
3837
3838 delete from fem_pl_temp_objects
3839 where request_id = p_request_rec.request_id
3840 and object_id = p_rule_rec.rollup_obj_id
3841 and object_type = 'SEQUENCE'
3842 and object_name = p_rule_rec.sequence_name;
3843
3844 exception
3845 when others then
3846 l_completion_status := FND_CONCURRENT.Set_Completion_Status('WARNING',null);
3847 FEM_ENGINES_PKG.User_Message (
3848 p_app_name => G_FEM
3849 ,p_msg_name => G_ENG_DROP_SEQUENCE_WRN
3850 ,p_token1 => 'SEQUENCE_NAME'
3851 ,p_value1 => p_rule_rec.sequence_name
3852 );
3853 end;
3854
3855 commit;
3856
3857 end if;
3858
3859 FEM_ENGINES_PKG.Tech_Message (
3860 p_severity => G_LOG_LEVEL_2
3861 ,p_module => G_BLOCK||'.'||l_api_name
3862 ,p_msg_text => 'END'
3863 );
3864
3865 EXCEPTION
3866
3867 when l_drop_temp_objects_error then
3868
3869 FEM_ENGINES_PKG.Tech_Message (
3870 p_severity => G_LOG_LEVEL_6
3871 ,p_module => G_BLOCK||'.'||l_api_name
3872 ,p_msg_text => 'Drop Temp Objects Exception'
3873 );
3874
3875 raise g_rollup_request_error;
3876
3877 END Drop_Temp_Objects;
3878
3879
3880
3881 /*============================================================================+
3882 | PROCEDURE
3883 | Find_Condition_Nodes
3884 |
3885 | DESCRIPTION
3886 | Finds all the nodes in the rollup hierarchy that satisfies the rollup
3887 | rule's condition, and stores them in the FEM_RU_NODES_T table. If any of
3888 | condition nodes are hierarchies, these are labeled as such in the
3889 | FEM_RU_NODES_T table.
3890 |
3891 | SCOPE - PRIVATE
3892 |
3893 +============================================================================*/
3894
3895 PROCEDURE Find_Condition_Nodes (
3896 p_request_rec in request_record
3897 ,p_rule_rec in rule_record
3898 )
3899 IS
3900
3901 l_api_name constant varchar2(30) := 'Find_Condition_Nodes';
3902
3903 l_dimension_rec dimension_record;
3904 l_find_cond_node_stmt long;
3905 l_find_cond_leaf_stmt long;
3906 l_find_cond_root_stmt long;
3907 l_cond_where_clause long;
3908 l_node_count number;
3909 l_leaf_count number;
3910
3911 l_return_status t_return_status%TYPE;
3912 l_msg_count t_msg_count%TYPE;
3913 l_msg_data t_msg_data%TYPE;
3914
3915 l_find_cond_nodes_error exception;
3916
3917 BEGIN
3918
3919 FEM_ENGINES_PKG.Tech_Message (
3920 p_severity => G_LOG_LEVEL_2
3921 ,p_module => G_BLOCK||'.'||l_api_name
3922 ,p_msg_text => 'BEGIN'
3923 );
3924
3925 l_dimension_rec := p_request_rec.dimension_rec;
3926
3927 ------------------------------------------------------------------------------
3928 -- STEP 1: Generate Condition Where Clause Predicate
3929 ------------------------------------------------------------------------------
3930 FEM_ENGINES_PKG.Tech_Message (
3931 p_severity => G_LOG_LEVEL_1
3932 ,p_module => G_BLOCK||'.'||l_api_name
3933 ,p_msg_text => 'Step 1: Generate Condition Where Clause Predicate'
3934 );
3935
3936 FEM_CONDITIONS_API.Generate_Condition_Predicate (
3937 p_api_version => 1.0
3938 ,p_init_msg_list => FND_API.G_FALSE
3939 ,p_commit => FND_API.G_FALSE
3940 ,p_encoded => FND_API.G_TRUE
3941 ,x_return_status => l_return_status
3942 ,x_msg_count => l_msg_count
3943 ,x_msg_data => l_msg_data
3944 ,p_condition_obj_id => p_rule_rec.cond_obj_id
3945 ,p_rule_effective_date => p_request_rec.effective_date_varchar
3946 ,p_input_fact_table_name => l_dimension_rec.member_b_table
3947 ,p_table_alias => 'm'
3948 ,p_display_predicate => 'N'
3949 ,p_return_predicate_type => 'DIM'
3950 ,p_logging_turned_on => 'N'
3951 ,x_predicate_string => l_cond_where_clause
3952 );
3953
3954 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
3955 Get_Put_Messages (
3956 p_msg_count => l_msg_count
3957 ,p_msg_data => l_msg_data
3958 );
3959 raise l_find_cond_nodes_error;
3960 end if;
3961
3962 if (l_cond_where_clause is null) then
3963
3964 FEM_ENGINES_PKG.User_Message (
3965 p_app_name => G_FEM
3966 ,p_msg_name => G_ENG_COND_WHERE_CLAUSE_ERR
3967 ,p_token1 => 'CONDITION_OBJECT_ID'
3968 ,p_value1 => p_rule_rec.cond_obj_id
3969 ,p_token2 => 'EFFECTIVE_DATE'
3970 ,p_value2 => FND_DATE.date_to_chardate(p_request_rec.effective_date)
3971 ,p_token3 => 'CONDITION_TABLE_NAME'
3972 ,p_value3 => l_dimension_rec.member_b_table
3973 );
3974 raise l_find_cond_nodes_error;
3975
3976 end if;
3977
3978 ------------------------------------------------------------------------------
3979 -- STEP 2: Find "Parent" Condition Nodes
3980 ------------------------------------------------------------------------------
3981 FEM_ENGINES_PKG.Tech_Message (
3982 p_severity => G_LOG_LEVEL_1
3983 ,p_module => G_BLOCK||'.'||l_api_name
3984 ,p_msg_text => 'Step 2: Find "Parent" Condition Nodes'
3985 );
3986
3987 -- Build SQL statement for finding and inserting "Parent" Condition Nodes
3988 -- into FEM_RU_NODES_T
3989 l_find_cond_node_stmt :=
3990 ' insert into fem_ru_nodes_t ('||
3991 ' created_by_request_id'||
3992 ' ,created_by_object_id'||
3993 ' ,node_id'||
3994 ' ,costed_flag'||
3995 ' ,root_flag'||
3996 ' ,condition_flag'||
3997 ' )'||
3998 ' select :b_request_id'||
3999 ' ,:b_rollup_obj_id'||
4000 ' ,m.'||l_dimension_rec.member_col||
4001 ' ,''N'''||
4002 ' ,''N'''||
4003 ' ,''Y'''||
4004 ' from '||l_dimension_rec.member_b_table||' m'||
4005 ' where m.local_vs_combo_id = :b_local_vs_combo_id'||
4006 -- ' and {{data_slice}}'||
4007 ' and '||l_cond_where_clause||
4008 ' and not exists ('||
4009 ' select 1'||
4010 ' from fem_ru_nodes_t n'||
4011 ' where n.created_by_request_id = :b_request_id'||
4012 ' and n.created_by_object_id = :b_rollup_obj_id'||
4013 ' and n.node_id = m.'||l_dimension_rec.member_col||
4014 ' )';
4015
4016 if (l_dimension_rec.dimension_varchar_label = 'COST_OBJECT') then
4017
4018 l_find_cond_node_stmt := l_find_cond_node_stmt ||
4019 ' and m.ledger_id = :b_ledger_id'|| --must restrict by ledger for x-ledger
4020 ' and exists ('||
4021 ' select 1'||
4022 ' from '||l_dimension_rec.hier_table||' h'||
4023 ' where h.hierarchy_obj_id = :b_hier_obj_id'||
4024 ' and :b_effective_date between h.effective_start_date and h.effective_end_date'||
4025 ' and h.parent_id = m.'||l_dimension_rec.member_col||
4026 ' )';
4027
4028 --todo: MP integration
4029 -- Execute SQL for finding all "Parent" Condition Nodes for the specified
4030 -- hierarchy object and effective date
4031 execute immediate l_find_cond_node_stmt
4032 using p_request_rec.request_id
4033 ,p_rule_rec.rollup_obj_id
4034 ,p_rule_rec.local_vs_combo_id
4035 ,p_request_rec.request_id
4036 ,p_rule_rec.rollup_obj_id
4037 ,p_request_rec.ledger_id
4038 ,p_rule_rec.hier_obj_id
4039 ,p_request_rec.effective_date;
4040
4041 elsif (l_dimension_rec.dimension_varchar_label = 'ACTIVITY') then
4042
4043 l_find_cond_node_stmt := l_find_cond_node_stmt ||
4044 ' and exists ('||
4045 ' select 1'||
4046 ' from '||l_dimension_rec.hier_table||' h'||
4047 ' where h.hierarchy_obj_def_id = :b_hier_obj_def_id'||
4048 ' and h.single_depth_flag = ''Y'''||
4049 ' and h.parent_id = m.'||l_dimension_rec.member_col||
4050 ' )';
4051
4052 --todo: MP integration
4053 -- Execute SQL for finding all "Parent" Condition Nodes for the specified
4054 -- hierarchy object definition
4055 execute immediate l_find_cond_node_stmt
4056 using p_request_rec.request_id
4057 ,p_rule_rec.rollup_obj_id
4058 ,p_rule_rec.local_vs_combo_id
4059 ,p_request_rec.request_id
4060 ,p_rule_rec.rollup_obj_id
4061 ,p_rule_rec.hier_obj_def_id;
4062
4063 else
4064
4065 FEM_ENGINES_PKG.User_Message (
4066 p_app_name => G_FEM
4067 ,p_msg_name => G_RU_NO_ROLLUP_DIM_ERR
4068 ,p_token1 => 'DIMENSION_VARCHAR_LABEL'
4069 ,p_value1 => l_dimension_rec.dimension_varchar_label
4070 );
4071 raise l_find_cond_nodes_error;
4072
4073 end if;
4074
4075 commit;
4076
4077 ------------------------------------------------------------------------------
4078 -- STEP 3: Count "Parent" Conditions Nodes
4079 ------------------------------------------------------------------------------
4080 FEM_ENGINES_PKG.Tech_Message (
4081 p_severity => G_LOG_LEVEL_1
4082 ,p_module => G_BLOCK||'.'||l_api_name
4083 ,p_msg_text => 'Step 3: Count "Parent" Condition Nodes'
4084 );
4085
4086 -- Count the number of "Parent" Condition Nodes in FEM_RU_NODES_T
4087 select count(*)
4088 into l_node_count
4089 from fem_ru_nodes_t
4090 where created_by_request_id = p_request_rec.request_id
4091 and created_by_object_id = p_rule_rec.rollup_obj_id;
4092
4093 if (l_node_count = 0) then
4094
4095 ----------------------------------------------------------------------------
4096 -- STEP 3.a: Count "Leaf" Condition Nodes
4097 ----------------------------------------------------------------------------
4098 FEM_ENGINES_PKG.Tech_Message (
4099 p_severity => G_LOG_LEVEL_1
4100 ,p_module => G_BLOCK||'.'||l_api_name
4101 ,p_msg_text => 'Step 3.a: Count "Leaf" Condition Nodes'
4102 );
4103
4104 -- Build SQL statement for counting "Leaf" Condition Nodes
4105 l_find_cond_leaf_stmt :=
4106 ' select count(*)'||
4107 ' from '||l_dimension_rec.member_b_table||' m'||
4108 ' where m.local_vs_combo_id = :b_local_vs_combo_id'||
4109 ' and '||l_cond_where_clause;
4110
4111 if (l_dimension_rec.dimension_varchar_label = 'COST_OBJECT') then
4112
4113 l_find_cond_leaf_stmt := l_find_cond_leaf_stmt ||
4114 ' and m.ledger_id = :b_ledger_id'|| --must restrict by ledger for x-ledger
4115 ' and exists ('||
4116 ' select 1'||
4117 ' from '||l_dimension_rec.hier_table||' h'||
4118 ' where h.hierarchy_obj_id = :b_hier_obj_id'||
4119 ' and :b_effective_date between h.effective_start_date and h.effective_end_date'||
4120 ' and h.child_id = m.'||l_dimension_rec.member_col||
4121 ' )';
4122
4123 -- Execute SQL for counting all "Leaf" Condition Nodes for the specified
4124 -- hierarchy object and effective date
4125 execute immediate l_find_cond_leaf_stmt
4126 into l_leaf_count
4127 using p_rule_rec.local_vs_combo_id
4128 ,p_request_rec.ledger_id
4129 ,p_rule_rec.hier_obj_id
4130 ,p_request_rec.effective_date;
4131
4132 elsif (l_dimension_rec.dimension_varchar_label = 'ACTIVITY') then
4133
4134 l_find_cond_leaf_stmt := l_find_cond_leaf_stmt ||
4135 ' and exists ('||
4136 ' select 1'||
4137 ' from '||l_dimension_rec.hier_table||' h'||
4138 ' where h.hierarchy_obj_def_id = :b_hier_obj_def_id'||
4139 ' and h.single_depth_flag = ''Y'''||
4140 ' and h.child_id = m.'||l_dimension_rec.member_col||
4141 ' )';
4142
4143 -- Execute SQL for counting all "Leaf" Condition Nodes for the specified
4144 -- hierarchy object definition
4145 execute immediate l_find_cond_leaf_stmt
4146 into l_leaf_count
4147 using p_rule_rec.local_vs_combo_id
4148 ,p_rule_rec.hier_obj_def_id;
4149
4150 else
4151
4152 FEM_ENGINES_PKG.User_Message (
4153 p_app_name => G_FEM
4154 ,p_msg_name => G_RU_NO_ROLLUP_DIM_ERR
4155 ,p_token1 => 'DIMENSION_VARCHAR_LABEL'
4156 ,p_value1 => l_dimension_rec.dimension_varchar_label
4157 );
4158 raise l_find_cond_nodes_error;
4159
4160 end if;
4161
4162 if (l_leaf_count = 0) then
4163
4164 -- No Condition Nodes were found in the specified hierarchy
4165 FEM_ENGINES_PKG.User_Message (
4166 p_app_name => G_FEM
4167 ,p_msg_name => G_RU_NO_COND_NODES_FOUND_ERR
4168 );
4169 raise l_find_cond_nodes_error;
4170
4171 else
4172
4173 -- Only "Leaf" Condition Nodes were found in the specified hierarchy.
4174 -- Rollup is not necessary on leaf nodes.
4175 FEM_ENGINES_PKG.User_Message (
4176 p_app_name => G_FEM
4177 ,p_msg_name => G_RU_COND_NODES_LEAFS_ERR
4178 );
4179 raise l_find_cond_nodes_error;
4180
4181 end if;
4182
4183 end if;
4184
4185 ------------------------------------------------------------------------------
4186 -- STEP 4: Find "Root" Condition Nodes
4187 ------------------------------------------------------------------------------
4188 FEM_ENGINES_PKG.Tech_Message (
4189 p_severity => G_LOG_LEVEL_1
4190 ,p_module => G_BLOCK||'.'||l_api_name
4191 ,p_msg_text => 'Step 4: Find "Root" Condition Nodes'
4192 );
4193
4194 -- Build SQL statement for finding any possible "Root" Condition Nodes
4195 -- in FEM_RU_NODES_T and update the Root Node Flag to Y.
4196 l_find_cond_root_stmt :=
4197 ' update fem_ru_nodes_t n'||
4198 ' set root_flag = ''Y'''||
4199 ' where created_by_request_id = :b_request_id'||
4200 ' and created_by_object_id = :b_rollup_obj_id';
4201 -- ' and {{data_slice}}';
4202
4203 if (l_dimension_rec.dimension_varchar_label = 'COST_OBJECT') then
4204
4205 l_find_cond_root_stmt := l_find_cond_root_stmt ||
4206 ' and not exists ('||
4207 ' select 1'||
4208 ' from '||l_dimension_rec.hier_table||' h'||
4209 ' ,'||l_dimension_rec.member_b_table||' parent'||
4210 ' where h.hierarchy_obj_id = :b_hier_obj_id'||
4211 ' and :b_effective_date between h.effective_start_date and h.effective_end_date'||
4212 ' and h.child_id = n.node_id'||
4213 ' and parent.'||l_dimension_rec.member_col||' = h.parent_id'||
4214 ' and parent.ledger_id = :b_ledger_id'|| --must restrict by ledger for x-ledger
4215 ' )';
4216
4217 --todo: MP integration
4218 -- Execute SQL for finding all "Root" Condition Nodes for the specified
4219 -- hierarchy object and effective date and update Root Node Flag to Y.
4220 execute immediate l_find_cond_root_stmt
4221 using p_request_rec.request_id
4222 ,p_rule_rec.rollup_obj_id
4223 ,p_rule_rec.hier_obj_id
4224 ,p_request_rec.effective_date
4225 ,p_request_rec.ledger_id;
4226
4227 elsif (l_dimension_rec.dimension_varchar_label = 'ACTIVITY') then
4228
4229 l_find_cond_root_stmt := l_find_cond_root_stmt ||
4230 ' and exists ('||
4231 ' select 1'||
4232 ' from '||l_dimension_rec.hier_table||' h'||
4233 ' where h.hierarchy_obj_def_id = :b_hier_obj_def_id'||
4234 ' and h.child_id = h.parent_id'||
4235 ' and h.single_depth_flag = ''Y'''||
4236 ' and h.parent_id = n.node_id'||
4237 ' )';
4238
4239 --todo: MP integration
4240 -- Execute SQL for finding all "Root" Condition Nodes for the specified
4241 -- hierarchy object definition and update Root Node Flag to Y
4242 execute immediate l_find_cond_root_stmt
4243 using p_request_rec.request_id
4244 ,p_rule_rec.rollup_obj_id
4245 ,p_rule_rec.hier_obj_def_id;
4246
4247 end if;
4248
4249 commit;
4250
4251 FEM_ENGINES_PKG.Tech_Message (
4252 p_severity => G_LOG_LEVEL_2
4253 ,p_module => G_BLOCK||'.'||l_api_name
4254 ,p_msg_text => 'END'
4255 );
4256
4257 EXCEPTION
4258
4259 when l_find_cond_nodes_error then
4260
4261 FEM_ENGINES_PKG.Tech_Message (
4262 p_severity => G_LOG_LEVEL_6
4263 ,p_module => G_BLOCK||'.'||l_api_name
4264 ,p_msg_text => 'Find Condition Nodes Exception'
4265 );
4266
4267 raise g_rollup_request_error;
4268
4269 END Find_Condition_Nodes;
4270
4271
4272
4273 /*============================================================================+
4274 | PROCEDURE
4275 | Find_Root_Nodes
4276 |
4277 | DESCRIPTION
4278 | Finds all the root nodes in the rollup hierarchy, and stores them in the
4279 | FEM_RU_NODES_T table.
4280 |
4281 | SCOPE - PRIVATE
4282 |
4283 +============================================================================*/
4284
4285 PROCEDURE Find_Root_Nodes (
4286 p_request_rec in request_record
4287 ,p_rule_rec in rule_record
4288 )
4289 IS
4290
4291 l_api_name constant varchar2(30) := 'Find_Root_Nodes';
4292
4293
4294 l_dimension_rec dimension_record;
4295 l_node_count number;
4296 l_find_root_node_stmt long;
4297
4298 l_find_root_nodes_error exception;
4299
4300 BEGIN
4301
4302 FEM_ENGINES_PKG.Tech_Message (
4303 p_severity => G_LOG_LEVEL_2
4304 ,p_module => G_BLOCK||'.'||l_api_name
4305 ,p_msg_text => 'BEGIN'
4306 );
4307
4308 l_dimension_rec := p_request_rec.dimension_rec;
4309
4310 ------------------------------------------------------------------------------
4311 -- STEP 1: Find Root Nodes
4312 ------------------------------------------------------------------------------
4313 FEM_ENGINES_PKG.Tech_Message (
4314 p_severity => G_LOG_LEVEL_1
4315 ,p_module => G_BLOCK||'.'||l_api_name
4316 ,p_msg_text => 'Step 1: Find Root Nodes'
4317 );
4318
4319 -- Build SQL statement for finding and inserting Root Nodes into
4320 -- FEM_RU_NODES_T
4321 l_find_root_node_stmt :=
4322 ' insert into fem_ru_nodes_t ('||
4323 ' created_by_request_id'||
4324 ' ,created_by_object_id'||
4325 ' ,node_id'||
4326 ' ,costed_flag'||
4327 ' ,root_flag'||
4328 ' ,condition_flag'||
4329 ' )'||
4330 ' select :b_request_id'||
4331 ' ,:b_rollup_obj_id'||
4332 ' ,m.'||l_dimension_rec.member_col||
4333 ' ,''N'''||
4334 ' ,''Y'''||
4335 ' ,''N'''||
4336 ' from '||l_dimension_rec.member_b_table||' m'||
4337 ' where m.local_vs_combo_id = :b_local_vs_combo_id'||
4338 -- ' and {{data_slice}}'||
4339 ' and not exists ('||
4340 ' select 1'||
4341 ' from fem_ru_nodes_t n'||
4342 ' where n.created_by_request_id = :b_request_id'||
4343 ' and n.created_by_object_id = :b_rollup_obj_id'||
4344 ' and n.node_id = m.'||l_dimension_rec.member_col||
4345 ' )';
4346
4347 if (l_dimension_rec.dimension_varchar_label = 'COST_OBJECT') then
4348
4349 l_find_root_node_stmt := l_find_root_node_stmt ||
4350 ' and m.ledger_id = :b_ledger_id'|| --must restrict by ledger for x-ledger
4351 ' and exists ('||
4352 ' select 1'||
4353 ' from '||l_dimension_rec.hier_table||' h'||
4354 ' where h.hierarchy_obj_id = :b_hier_obj_id'||
4355 ' and :b_effective_date between h.effective_start_date and h.effective_end_date'||
4356 ' and h.parent_id = m.'||l_dimension_rec.member_col||
4357 ' )'||
4358 ' and not exists ('||
4359 ' select 1'||
4360 ' from '||l_dimension_rec.hier_table||' h'||
4361 ' ,'||l_dimension_rec.member_b_table||' parent'||
4362 ' where h.hierarchy_obj_id = :b_hier_obj_id'||
4363 ' and :b_effective_date between h.effective_start_date and h.effective_end_date'||
4364 ' and h.child_id = m.'||l_dimension_rec.member_col||
4365 ' and parent.'||l_dimension_rec.member_col||' = h.parent_id'||
4366 ' and parent.ledger_id = m.ledger_id'|| --must restrict by ledger for x-ledger
4367 ' )';
4368
4369 --todo: MP integration
4370 -- Execute SQL for finding all Root Nodes for the specified
4371 -- hierarchy object and effective date
4372 execute immediate l_find_root_node_stmt
4373 using p_request_rec.request_id
4374 ,p_rule_rec.rollup_obj_id
4375 ,p_rule_rec.local_vs_combo_id
4376 ,p_request_rec.request_id
4377 ,p_rule_rec.rollup_obj_id
4378 ,p_request_rec.ledger_id
4379 ,p_rule_rec.hier_obj_id
4380 ,p_request_rec.effective_date
4381 ,p_rule_rec.hier_obj_id
4382 ,p_request_rec.effective_date;
4383
4384 elsif (l_dimension_rec.dimension_varchar_label = 'ACTIVITY') then
4385
4386 l_find_root_node_stmt := l_find_root_node_stmt ||
4387 ' and exists ('||
4388 ' select 1'||
4389 ' from '||l_dimension_rec.hier_table||' h'||
4390 ' where h.hierarchy_obj_def_id = :b_hier_obj_def_id'||
4391 ' and h.child_id = h.parent_id'||
4392 ' and h.single_depth_flag = ''Y'''||
4393 ' and h.parent_id = m.'||l_dimension_rec.member_col||
4394 ' )';
4395
4396 --todo: MP integration
4397 -- Execute SQL for finding all Root Nodes for the specified
4398 -- hierarchy object definition
4399 execute immediate l_find_root_node_stmt
4400 using p_request_rec.request_id
4401 ,p_rule_rec.rollup_obj_id
4402 ,p_rule_rec.local_vs_combo_id
4403 ,p_request_rec.request_id
4404 ,p_rule_rec.rollup_obj_id
4405 ,p_rule_rec.hier_obj_def_id;
4406
4407 else
4408
4409 FEM_ENGINES_PKG.User_Message (
4410 p_app_name => G_FEM
4411 ,p_msg_name => G_RU_NO_ROLLUP_DIM_ERR
4412 ,p_token1 => 'DIMENSION_VARCHAR_LABEL'
4413 ,p_value1 => l_dimension_rec.dimension_varchar_label
4414 );
4415 raise l_find_root_nodes_error;
4416
4417 end if;
4418
4419 commit;
4420
4421 ------------------------------------------------------------------------------
4422 -- STEP 2: Count Root Nodes
4423 ------------------------------------------------------------------------------
4424 FEM_ENGINES_PKG.Tech_Message (
4425 p_severity => G_LOG_LEVEL_1
4426 ,p_module => G_BLOCK||'.'||l_api_name
4427 ,p_msg_text => 'Step 2: Count Root Nodes'
4428 );
4429
4430 select count(*)
4431 into l_node_count
4432 from fem_ru_nodes_t
4433 where created_by_request_id = p_request_rec.request_id
4434 and created_by_object_id = p_rule_rec.rollup_obj_id
4435 and root_flag = 'Y';
4436
4437 if (l_node_count = 0) then
4438
4439 FEM_ENGINES_PKG.User_Message (
4440 p_app_name => G_FEM
4441 ,p_msg_name => G_RU_NO_ROOT_NODES_FOUND_ERR
4442 );
4443 raise l_find_root_nodes_error;
4444
4445 end if;
4446
4447 FEM_ENGINES_PKG.Tech_Message (
4448 p_severity => G_LOG_LEVEL_2
4449 ,p_module => G_BLOCK||'.'||l_api_name
4450 ,p_msg_text => 'END'
4451 );
4452
4453 EXCEPTION
4454
4455 when l_find_root_nodes_error then
4456
4457 FEM_ENGINES_PKG.Tech_Message (
4458 p_severity => G_LOG_LEVEL_6
4459 ,p_module => G_BLOCK||'.'||l_api_name
4460 ,p_msg_text => 'Find Root Nodes Exception'
4461 );
4462
4463 raise g_rollup_request_error;
4464
4465 END Find_Root_Nodes;
4466
4467
4468
4469 /*============================================================================+
4470 | PROCEDURE
4471 | Rollup_Top_Node
4472 |
4473 | DESCRIPTION
4474 | Peforms multi-level rollup on the specified top node of a rollup hierarchy.
4475 |
4476 | Flattening of the rollup hierarchy is necessary when performing rollups on
4477 | the cost object hierarchy (regardless if a condition is specified or not),
4478 | or on the activity hierarchy if and only if a condition is specified. The
4479 | flattened hierarchy is store in FEM_RU_COST_OBJ_HIER_T for the cost object
4480 | hierarchy, and in FEM_RU_ACTIVITIES_HIER_T for the activity hierarchy.
4481 | After finishing all rollup processing for a top node, the flattened
4482 | hierarchies need to be purged.
4483 |
4484 | Rollup processing is done on a level by level basis, starting with the
4485 | top node's deepest level, then climbing up to the next level and so forth,
4486 | until the top node's level is reached. At each level, we query
4487 | for all the uncosted parent nodes that exist at that level, so that we
4488 | can then call Rollup_Parent_Node() on each of these uncosted parent nodes.
4489 |
4490 | SCOPE - PRIVATE
4491 |
4492 +============================================================================*/
4493
4494 PROCEDURE Rollup_Top_Node (
4495 p_request_rec in request_record
4496 ,p_rule_rec in rule_record
4497 ,p_find_children_stmt in long
4498 ,p_rollup_parent_stmt in long
4499 ,p_find_child_chains_stmt in long
4500 ,p_input_ds_b_where_clause in long
4501 ,p_top_node_id in number
4502 )
4503 IS
4504
4505 l_api_name constant varchar2(30) := 'Rollup_Top_Node';
4506
4507 l_dimension_rec dimension_record;
4508
4509 l_costed_flag varchar2(1);
4510
4511 l_parent_depth_num number;
4512 l_min_parent_depth_num number;
4513 l_max_parent_depth_num number;
4514
4515 l_parent_id_tbl number_table;
4516
4517 l_flatten_rollup_table_stmt long;
4518 l_min_parent_node_depth_stmt long;
4519 l_max_parent_node_depth_stmt long;
4520
4521 l_find_parent_nodes_csr dynamic_cursor;
4522 l_find_parent_nodes_stmt long;
4523 l_find_parent_nodes_last_row number;
4524
4525 l_find_child_nodes_stmt long;
4526
4527 l_source_table_query_stmt long;
4528 l_source_table_query_param1 number;
4529 l_source_table_query_param2 number;
4530
4531 l_return_status t_return_status%TYPE;
4532 l_msg_count t_msg_count%TYPE;
4533 l_msg_data t_msg_data%TYPE;
4534
4535 l_rollup_top_node_error exception;
4536
4537 BEGIN
4538
4539 FEM_ENGINES_PKG.Tech_Message (
4540 p_severity => G_LOG_LEVEL_2
4541 ,p_module => G_BLOCK||'.'||l_api_name
4542 ,p_msg_text => 'BEGIN'
4543 );
4544
4545 l_dimension_rec := p_request_rec.dimension_rec;
4546
4547 ------------------------------------------------------------------------------
4548 -- STEP 1: Check to see if the top has been costed
4549 ------------------------------------------------------------------------------
4550 FEM_ENGINES_PKG.Tech_Message (
4551 p_severity => G_LOG_LEVEL_1
4552 ,p_module => G_BLOCK||'.'||l_api_name
4553 ,p_msg_text => 'Step 1: Check if Top Node has been costed'
4554 );
4555
4556 select costed_flag
4557 into l_costed_flag
4558 from fem_ru_nodes_t
4559 where created_by_request_id = p_request_rec.request_id
4560 and created_by_object_id = p_rule_rec.rollup_obj_id
4561 and node_id = p_top_node_id;
4562
4563 if (l_costed_flag = 'Y') then
4564 return;
4565 end if;
4566
4567 if (l_dimension_rec.dimension_varchar_label = 'COST_OBJECT') then
4568
4569 ----------------------------------------------------------------------------
4570 -- STEP 1.a: Flatten the Cost Object hierarchy into the
4571 -- FEM_RU_COST_OBJ_HIER_T table.
4572 ----------------------------------------------------------------------------
4573 FEM_ENGINES_PKG.Tech_Message (
4574 p_severity => G_LOG_LEVEL_1
4575 ,p_module => G_BLOCK||'.'||l_api_name
4576 ,p_msg_text => 'Step 1.a: Flatten Cost Object Hierarchy'
4577 );
4578
4579 begin
4580
4581 -- Flatten Cost Object hierarchy. Can't use MP as this is a connect by
4582 -- query.
4583 insert into fem_ru_cost_obj_hier_t (
4584 created_by_request_id
4585 ,created_by_object_id
4586 ,relationship_id
4587 ,parent_id
4588 ,parent_depth_num
4589 ,child_id
4590 ,child_sequence_num
4591 ,child_depth_num
4592 ,child_ledger_id
4593 )
4594 select ru.created_by_request_id
4595 ,ru.created_by_object_id
4596 ,ru.relationship_id
4597 ,ru.parent_id
4598 ,ru.parent_depth_num
4599 ,ru.child_id
4600 ,ru.child_sequence_num
4601 ,ru.child_depth_num
4602 ,decode(child.ledger_id,p_request_rec.ledger_id,null,child.ledger_id)
4603 from (
4604 select created_by_request_id
4605 ,created_by_object_id
4606 ,relationship_id
4607 ,parent_id
4608 ,level as parent_depth_num
4609 ,child_id
4610 ,child_sequence_num
4611 ,(level + 1) as child_depth_num
4612 from (
4613 select nvl(n.created_by_request_id,p_request_rec.request_id) as created_by_request_id
4614 ,nvl(n.created_by_object_id,p_rule_rec.rollup_obj_id) as created_by_object_id
4615 ,h.relationship_id
4616 ,h.parent_id
4617 ,h.child_id
4618 ,h.child_sequence_num
4619 ,nvl(n.costed_flag,'N') as costed_flag
4620 from fem_cost_objects_hier h
4621 ,fem_cost_objects parent
4622 ,fem_ru_nodes_t n
4623 where h.hierarchy_obj_id = p_rule_rec.hier_obj_id
4624 and p_request_rec.effective_date between h.effective_start_date and h.effective_end_date
4625 and parent.cost_object_id = h.parent_id
4626 and parent.ledger_id = p_request_rec.ledger_id
4627 and n.created_by_request_id (+) = p_request_rec.request_id
4628 and n.created_by_object_id (+) = p_rule_rec.rollup_obj_id
4629 and n.node_id (+) = h.parent_id
4630 )
4631 start with parent_id = p_top_node_id
4632 connect by prior child_id = parent_id
4633 and prior costed_flag = 'N'
4634 ) ru
4635 ,fem_cost_objects child
4636 where child.cost_object_id = ru.child_id;
4637
4638 exception
4639 when g_connect_by_loop_error then
4640 FEM_ENGINES_PKG.User_Message (
4641 p_app_name => G_FEM
4642 ,p_msg_name => G_RU_HIER_CIRC_REF_ERR
4643 );
4644 raise l_rollup_top_node_error;
4645 end;
4646
4647 commit;
4648
4649 elsif ( (l_dimension_rec.dimension_varchar_label = 'ACTIVITY')
4650 and (p_rule_rec.cond_exists) ) then
4651
4652 ----------------------------------------------------------------------------
4653 -- STEP 1.b: Flatten the Activity hierarchy into the FEM_RU_ACTIVITIES_HIER_T
4654 -- table if condition exists.
4655 ----------------------------------------------------------------------------
4656 FEM_ENGINES_PKG.Tech_Message (
4657 p_severity => G_LOG_LEVEL_1
4658 ,p_module => G_BLOCK||'.'||l_api_name
4659 ,p_msg_text => 'Step 1.b: Flatten Activity Hierarchy'
4660 );
4661
4662 begin
4663
4664 -- Flatten Activity hierarchy. Can't use MP as this is a connect by
4665 -- query.
4666 insert into fem_ru_activities_hier_t (
4667 created_by_request_id
4668 ,created_by_object_id
4669 ,hierarchy_obj_def_id
4670 ,parent_id
4671 ,parent_depth_num
4672 ,child_id
4673 ,child_depth_num
4674 ,single_depth_flag
4675 ,weighting_pct
4676 )
4677 select created_by_request_id
4678 ,created_by_object_id
4679 ,hierarchy_obj_def_id
4680 ,parent_id
4681 ,level
4682 ,child_id
4683 ,level + 1
4684 ,single_depth_flag
4685 ,weighting_pct
4686 from (
4687 select nvl(n.created_by_request_id,p_request_rec.request_id) as created_by_request_id
4688 ,nvl(n.created_by_object_id,p_rule_rec.rollup_obj_id) as created_by_object_id
4689 ,h.hierarchy_obj_def_id
4690 ,h.parent_id
4691 ,h.child_id
4692 ,h.single_depth_flag
4693 ,h.weighting_pct
4694 ,nvl(n.costed_flag,'N') as costed_flag
4695 from fem_activities_hier h
4696 ,fem_ru_nodes_t n
4697 where h.hierarchy_obj_def_id = p_rule_rec.hier_obj_def_id
4698 and h.child_id <> h.parent_id
4699 and h.single_depth_flag = 'Y'
4700 and n.created_by_request_id (+) = p_request_rec.request_id
4701 and n.created_by_object_id (+) = p_rule_rec.rollup_obj_id
4702 and n.node_id (+) = h.parent_id
4703 )
4704 start with parent_id = p_top_node_id
4705 connect by prior child_id = parent_id
4706 and prior costed_flag = 'N';
4707
4708 exception
4709 when g_connect_by_loop_error then
4710 FEM_ENGINES_PKG.User_Message (
4711 p_app_name => G_FEM
4712 ,p_msg_name => G_RU_HIER_CIRC_REF_ERR
4713 );
4714 raise l_rollup_top_node_error;
4715 end;
4716
4717 commit;
4718
4719 end if;
4720
4721 ------------------------------------------------------------------------------
4722 -- STEP 2: Find Child Nodes not in FEM_RU_NODES_T table
4723 ------------------------------------------------------------------------------
4724 FEM_ENGINES_PKG.Tech_Message (
4725 p_severity => G_LOG_LEVEL_1
4726 ,p_module => G_BLOCK||'.'||l_api_name
4727 ,p_msg_text => 'Step 2: Find Child Nodes'
4728 );
4729
4730 -- Build SQL statement for finding and inserting Child Nodes into
4731 -- FEM_RU_NODES_T
4732 l_find_child_nodes_stmt :=
4733 ' insert into fem_ru_nodes_t ('||
4734 ' created_by_request_id'||
4735 ' ,created_by_object_id'||
4736 ' ,node_id'||
4737 ' ,costed_flag'||
4738 ' ,root_flag'||
4739 ' ,condition_flag'||
4740 ' )';
4741
4742 if (l_dimension_rec.dimension_varchar_label = 'COST_OBJECT') then
4743
4744 l_find_child_nodes_stmt := l_find_child_nodes_stmt ||
4745 ' select distinct h.created_by_request_id'||
4746 ' ,h.created_by_object_id'||
4747 ' ,h.child_id'||
4748 ' ,''N'''||
4749 ' ,''N'''||
4750 ' ,''N'''||
4751 ' from '||p_rule_rec.hier_rollup_table||' h'||
4752 ' where h.created_by_request_id = :b_request_id'||
4753 ' and h.created_by_object_id = :b_rollup_obj_id'||
4754 ' and not exists ('||
4755 ' select 1'||
4756 ' from fem_ru_nodes_t n'||
4757 ' where n.created_by_request_id = h.created_by_request_id'||
4758 ' and n.created_by_object_id = h.created_by_object_id'||
4759 ' and n.node_id = h.child_id'||
4760 ' )';
4761
4762 --todo: MP integration (???)
4763 -- Execute SQL for finding all Child Nodes for the specified hierarchy
4764 execute immediate l_find_child_nodes_stmt
4765 using p_request_rec.request_id
4766 ,p_rule_rec.rollup_obj_id;
4767
4768 elsif (l_dimension_rec.dimension_varchar_label = 'ACTIVITY') then
4769
4770 -- If condition exists, we must include request_id and rollup_obj_id in the
4771 -- where clause for querying FEM_RU_ACTIVITIES_HIER_T
4772 if (p_rule_rec.cond_exists) then
4773
4774 l_find_child_nodes_stmt := l_find_child_nodes_stmt ||
4775 ' select h.created_by_request_id'||
4776 ' ,h.created_by_object_id'||
4777 ' ,h.child_id'||
4778 ' ,''N'''||
4779 ' ,''N'''||
4780 ' ,''N'''||
4781 ' from '||p_rule_rec.hier_rollup_table||' h'||
4782 ' where h.created_by_request_id = :b_request_id'||
4783 ' and h.created_by_object_id = :b_rollup_obj_id'||
4784 ' and h.hierarchy_obj_def_id = :b_hier_obj_def_id'||
4785 ' and h.child_id <> h.parent_id'||
4786 ' and h.single_depth_flag = ''Y'''||
4787 ' and not exists ('||
4788 ' select 1'||
4789 ' from fem_ru_nodes_t n'||
4790 ' where n.created_by_request_id = h.created_by_request_id'||
4791 ' and n.created_by_object_id = h.created_by_object_id'||
4792 ' and n.node_id = h.child_id'||
4793 ' )';
4794
4795 --todo: MP integration (???)
4796 -- Execute SQL for finding all Child Nodes for the specified
4797 -- hierarchy object definition
4798 execute immediate l_find_child_nodes_stmt
4799 using p_request_rec.request_id
4800 ,p_rule_rec.rollup_obj_id
4801 ,p_rule_rec.hier_obj_def_id;
4802
4803 else
4804
4805 l_find_child_nodes_stmt := l_find_child_nodes_stmt ||
4806 ' select :b_request_id'||
4807 ' ,:b_rollup_obj_id'||
4808 ' ,h.child_id'||
4809 ' ,''N'''||
4810 ' ,''N'''||
4811 ' ,''N'''||
4812 ' from '||p_rule_rec.hier_rollup_table||' h'||
4813 ' where h.hierarchy_obj_def_id = :b_hier_obj_def_id'||
4814 ' and h.child_id <> h.parent_id'||
4815 ' and h.single_depth_flag = ''Y'''||
4816 ' and not exists ('||
4817 ' select 1'||
4818 ' from fem_ru_nodes_t n'||
4819 ' where n.created_by_request_id = :b_request_id'||
4820 ' and n.created_by_object_id = :b_rollup_obj_id'||
4821 ' and n.node_id = h.child_id'||
4822 ' )';
4823
4824 --todo: MP integration (???)
4825 -- Execute SQL for finding all Child Nodes for the specified
4826 -- hierarchy object definition
4827 execute immediate l_find_child_nodes_stmt
4828 using p_request_rec.request_id
4829 ,p_rule_rec.rollup_obj_id
4830 ,p_rule_rec.hier_obj_def_id
4831 ,p_request_rec.request_id
4832 ,p_rule_rec.rollup_obj_id;
4833
4834 end if;
4835
4836 else
4837
4838 FEM_ENGINES_PKG.User_Message (
4839 p_app_name => G_FEM
4840 ,p_msg_name => G_RU_NO_ROLLUP_DIM_ERR
4841 ,p_token1 => 'DIMENSION_VARCHAR_LABEL'
4842 ,p_value1 => l_dimension_rec.dimension_varchar_label
4843 );
4844 raise l_rollup_top_node_error;
4845
4846 end if;
4847
4848 commit;
4849
4850 ------------------------------------------------------------------------------
4851 -- STEP 3: Pre-Populate COST_OBJECT_ID or ACTIVITY_ID on FEM_BALANCES
4852 ------------------------------------------------------------------------------
4853 FEM_ENGINES_PKG.Tech_Message (
4854 p_severity => G_LOG_LEVEL_1
4855 ,p_module => G_BLOCK||'.'||l_api_name
4856 ,p_msg_text => 'Step 3: Pre-Populate COST_OBJECT_ID or ACTIVITY_ID on FEM_BALANCES'
4857 );
4858
4859 if (l_dimension_rec.dimension_varchar_label = 'COST_OBJECT') then
4860
4861 -- The source table query comes from the list of uncosted nodes in
4862 -- FEM_RU_NODES_T. A join to FEM_COST_OBJECTS is necessary for
4863 -- Populate_Cost_Object_Id to work properly as it needs all the
4864 -- component dimension columns for the join on FEM_BALANCES.
4865 l_source_table_query_stmt :=
4866 ' select co.cost_object_id'||
4867 ' from fem_cost_objects co'||
4868 ' ,fem_ru_nodes_t n'||
4869 ' where n.created_by_request_id = :b_request_id'||
4870 ' and n.created_by_object_id = :b_rollup_obj_id'||
4871 ' and n.costed_flag = ''N'''||
4872 ' and n.node_id = co.cost_object_id';
4873
4874 l_source_table_query_param1 := p_request_rec.request_id;
4875 l_source_table_query_param2 := p_rule_rec.rollup_obj_id;
4876
4877 --todo: MP integration (???)
4878 FEM_COMPOSITE_DIM_UTILS_PVT.Populate_Cost_Object_Id (
4879 p_api_version => 1.0
4880 ,p_init_msg_list => FND_API.G_FALSE
4881 ,p_commit => FND_API.G_TRUE
4882 ,x_return_status => l_return_status
4883 ,x_msg_count => l_msg_count
4884 ,x_msg_data => l_msg_data
4885 ,p_object_type_code => p_rule_rec.rollup_obj_type_code
4886 ,p_source_table_query => l_source_table_query_stmt
4887 ,p_source_table_query_param1 => l_source_table_query_param1
4888 ,p_source_table_query_param2 => l_source_table_query_param2
4889 ,p_source_table_alias => 'co'
4890 ,p_target_table_name => 'FEM_BALANCES'
4891 ,p_target_table_alias => 'b'
4892 ,p_target_dsg_where_clause => p_input_ds_b_where_clause
4893 );
4894
4895 elsif (l_dimension_rec.dimension_varchar_label = 'ACTIVITY') then
4896
4897 if (p_rule_rec.cond_exists) then
4898
4899 -- If a condition exists, the source table query comes from the list of
4900 -- uncosted nodes in FEM_RU_NODES_T. A join to FEM_ACTIVITIES is
4901 -- necessary for Populate_Activity_Id to work properly as it needs all the
4902 -- component dimension columns for the join on FEM_BALANCES.
4903 l_source_table_query_stmt :=
4904 ' select act.activity_id'||
4905 ' from fem_activities act'||
4906 ' ,fem_ru_nodes_t n'||
4907 ' where n.created_by_request_id = :b_request_id'||
4908 ' and n.created_by_object_id = :b_rollup_obj_id'||
4909 ' and n.costed_flag = ''N'''||
4910 ' and n.node_id = act.activity_id';
4911
4912 l_source_table_query_param1 := p_request_rec.request_id;
4913 l_source_table_query_param2 := p_rule_rec.rollup_obj_id;
4914
4915 else
4916
4917 -- If no condition exists, the source table query comes from all the
4918 -- nodes that exist in the Activity hierarchy, including the root node.
4919 -- A join to FEM_ACTIVITIES is necessary for Populate_Activity_Id to work
4920 -- properly as it needs all the component dimension columns for the join
4921 -- on FEM_BALANCES.
4922 l_source_table_query_stmt :=
4923 ' select act.activity_id'||
4924 ' from fem_activities act'||
4925 ' ,'||p_rule_rec.hier_rollup_table||' h'||
4926 ' where h.hierarchy_obj_def_id = :b_hier_obj_def_id'||
4927 ' and h.child_id = act.activity_id'||
4928 ' and h.single_depth_flag = ''Y''';
4929
4930 l_source_table_query_param1 := p_rule_rec.hier_obj_def_id;
4931 l_source_table_query_param2 := null;
4932
4933 end if;
4934
4935 --todo: MP integration (???)
4936 FEM_COMPOSITE_DIM_UTILS_PVT.Populate_Activity_Id (
4937 p_api_version => 1.0
4938 ,p_init_msg_list => FND_API.G_FALSE
4939 ,p_commit => FND_API.G_TRUE
4940 ,x_return_status => l_return_status
4941 ,x_msg_count => l_msg_count
4942 ,x_msg_data => l_msg_data
4943 ,p_object_type_code => p_rule_rec.rollup_obj_type_code
4944 ,p_source_table_query => l_source_table_query_stmt
4945 ,p_source_table_query_param1 => l_source_table_query_param1
4946 ,p_source_table_query_param2 => l_source_table_query_param2
4947 ,p_source_table_alias => 'act'
4948 ,p_target_table_name => 'FEM_BALANCES'
4949 ,p_target_table_alias => 'b'
4950 ,p_target_dsg_where_clause => p_input_ds_b_where_clause
4951 ,p_ledger_id => p_request_rec.ledger_id
4952 ,p_statistic_basis_id => p_rule_rec.statistic_basis_id
4953 );
4954
4955 end if;
4956
4957 -- Check the return status after calling FEM_COMPOSITE_DIM_UTILS_PVT API's
4958 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
4959 Get_Put_Messages (
4960 p_msg_count => l_msg_count
4961 ,p_msg_data => l_msg_data
4962 );
4963 raise l_rollup_top_node_error;
4964 end if;
4965
4966 ------------------------------------------------------------------------------
4967 -- STEP 4: Get the Minimum and Maximum Parent Node Depths for the Top Node
4968 ------------------------------------------------------------------------------
4969 FEM_ENGINES_PKG.Tech_Message (
4970 p_severity => G_LOG_LEVEL_1
4971 ,p_module => G_BLOCK||'.'||l_api_name
4972 ,p_msg_text => 'Step 4: Get the Minimum and Maximum Parent Node Depths for the Top Node'
4973 );
4974
4975 if (l_dimension_rec.dimension_varchar_label = 'COST_OBJECT') then
4976
4977 select min(h.parent_depth_num)
4978 into l_min_parent_depth_num
4979 from fem_ru_cost_obj_hier_t h
4980 where h.created_by_request_id = p_request_rec.request_id
4981 and h.created_by_object_id = p_rule_rec.rollup_obj_id
4982 and h.parent_id = p_top_node_id;
4983
4984 select max(h.parent_depth_num)
4985 into l_max_parent_depth_num
4986 from fem_ru_cost_obj_hier_t h
4987 where h.created_by_request_id = p_request_rec.request_id
4988 and h.created_by_object_id = p_rule_rec.rollup_obj_id;
4989
4990 elsif (l_dimension_rec.dimension_varchar_label = 'ACTIVITY') then
4991
4992 -- If condition exists, we must include request_id and rollup_obj_id in the
4993 -- where clause for querying FEM_RU_ACTIVITIES_HIER_T
4994 if (p_rule_rec.cond_exists) then
4995
4996 select min(h.parent_depth_num)
4997 into l_min_parent_depth_num
4998 from fem_ru_activities_hier_t h
4999 where h.created_by_request_id = p_request_rec.request_id
5000 and h.created_by_object_id = p_rule_rec.rollup_obj_id
5001 and h.hierarchy_obj_def_id = p_rule_rec.hier_obj_def_id
5002 and h.child_id <> h.parent_id
5003 and h.single_depth_flag = 'Y'
5004 and h.parent_id = p_top_node_id;
5005
5006 select max(h.parent_depth_num)
5007 into l_max_parent_depth_num
5008 from fem_ru_activities_hier_t h
5009 where h.created_by_request_id = p_request_rec.request_id
5010 and h.created_by_object_id = p_rule_rec.rollup_obj_id
5011 and h.hierarchy_obj_def_id = p_rule_rec.hier_obj_def_id
5012 and h.child_id <> h.parent_id
5013 and h.single_depth_flag = 'Y';
5014
5015 else
5016
5017 select min(h.parent_depth_num)
5018 into l_min_parent_depth_num
5019 from fem_activities_hier h
5020 where h.hierarchy_obj_def_id = p_rule_rec.hier_obj_def_id
5021 and h.child_id <> h.parent_id
5022 and h.single_depth_flag = 'Y'
5023 and h.parent_id = p_top_node_id;
5024
5025 select max(h.parent_depth_num)
5026 into l_max_parent_depth_num
5027 from fem_activities_hier h
5028 where h.hierarchy_obj_def_id = p_rule_rec.hier_obj_def_id
5029 and h.child_id <> h.parent_id
5030 and h.single_depth_flag = 'Y';
5031
5032 end if;
5033
5034 else
5035
5036 FEM_ENGINES_PKG.User_Message (
5037 p_app_name => G_FEM
5038 ,p_msg_name => G_RU_NO_ROLLUP_DIM_ERR
5039 ,p_token1 => 'DIMENSION_VARCHAR_LABEL'
5040 ,p_value1 => l_dimension_rec.dimension_varchar_label
5041 );
5042 raise l_rollup_top_node_error;
5043
5044 end if;
5045
5046 ------------------------------------------------------------------------------
5047 -- STEP 5: Build Parent Node Query at specified Parent Depth Number
5048 ------------------------------------------------------------------------------
5049 FEM_ENGINES_PKG.Tech_Message (
5050 p_severity => G_LOG_LEVEL_1
5051 ,p_module => G_BLOCK||'.'||l_api_name
5052 ,p_msg_text => 'Step 5: Build Parent Node Query at specified Parent Depth Number'
5053 );
5054
5055 -- Build SQL statement for retrieving all parent nodes of a hierarchy at the
5056 -- specified parent depth number that have not been costed
5057 l_find_parent_nodes_stmt :=
5058 ' select n.node_id'||
5059 ' from fem_ru_nodes_t n'||
5060 ' where n.created_by_request_id = :b_request_id'||
5061 ' and n.created_by_object_id = :b_rollup_obj_id'||
5062 ' and n.costed_flag = ''N''';
5063 -- ' and {{data_slice}}'||
5064
5065 if (l_dimension_rec.dimension_varchar_label = 'COST_OBJECT') then
5066
5067 l_find_parent_nodes_stmt := l_find_parent_nodes_stmt ||
5068 ' and exists ('||
5069 ' select 1'||
5070 ' from '||p_rule_rec.hier_rollup_table||' h'||
5071 ' where h.created_by_request_id = n.created_by_request_id'||
5072 ' and h.created_by_object_id = n.created_by_object_id'||
5073 ' and h.parent_id = n.node_id'||
5074 ' and h.parent_depth_num = :b_parent_depth_num'||
5075 ' )';
5076
5077 elsif (l_dimension_rec.dimension_varchar_label = 'ACTIVITY') then
5078
5079 -- If condition exists, we must include request_id and rollup_obj_id in the
5080 -- where clause for querying FEM_RU_ACTIVITIES_HIER_T
5081 if (p_rule_rec.cond_exists) then
5082
5083 l_find_parent_nodes_stmt := l_find_parent_nodes_stmt ||
5084 ' and exists ('||
5085 ' select 1'||
5086 ' from '||p_rule_rec.hier_rollup_table||' h'||
5087 ' where h.created_by_request_id = n.created_by_request_id'||
5088 ' and h.created_by_object_id = n.created_by_object_id'||
5089 ' and h.hierarchy_obj_def_id = :b_hier_obj_def_id'||
5090 ' and h.single_depth_flag = ''Y'''||
5091 ' and h.parent_id = n.node_id'||
5092 ' and h.parent_depth_num = :b_parent_depth_num'||
5093 ' )';
5094
5095 else
5096
5097 l_find_parent_nodes_stmt := l_find_parent_nodes_stmt ||
5098 ' and exists ('||
5099 ' select 1'||
5100 ' from '||p_rule_rec.hier_rollup_table||' h'||
5101 ' where h.hierarchy_obj_def_id = :b_hier_obj_def_id'||
5102 ' and h.single_depth_flag = ''Y'''||
5103 ' and h.parent_id = n.node_id'||
5104 ' and h.parent_depth_num = :b_parent_depth_num'||
5105 ' )';
5106
5107 end if;
5108
5109 else
5110
5111 FEM_ENGINES_PKG.User_Message (
5112 p_app_name => G_FEM
5113 ,p_msg_name => G_RU_NO_ROLLUP_DIM_ERR
5114 ,p_token1 => 'DIMENSION_VARCHAR_LABEL'
5115 ,p_value1 => l_dimension_rec.dimension_varchar_label
5116 );
5117 raise l_rollup_top_node_error;
5118
5119 end if;
5120
5121 ------------------------------------------------------------------------------
5122 -- STEP 6: Loop through all Parent Depth Levels
5123 ------------------------------------------------------------------------------
5124 FEM_ENGINES_PKG.Tech_Message (
5125 p_severity => G_LOG_LEVEL_1
5126 ,p_module => G_BLOCK||'.'||l_api_name
5127 ,p_msg_text => 'Step 6: Loop through all Parent Depth Levels'
5128 );
5129
5130 l_parent_depth_num := l_max_parent_depth_num;
5131
5132 while (l_parent_depth_num >= l_min_parent_depth_num) loop
5133
5134 --todo: MP integration
5135 -- Execute SQL for retrieving all parent nodes of a hierarchy at the
5136 -- specified parent depth number that have not been costed
5137 if (l_dimension_rec.dimension_varchar_label = 'COST_OBJECT') then
5138
5139 open l_find_parent_nodes_csr
5140 for l_find_parent_nodes_stmt
5141 using p_request_rec.request_id
5142 ,p_rule_rec.rollup_obj_id
5143 ,l_parent_depth_num;
5144
5145 elsif (l_dimension_rec.dimension_varchar_label = 'ACTIVITY') then
5146
5147 open l_find_parent_nodes_csr
5148 for l_find_parent_nodes_stmt
5149 using p_request_rec.request_id
5150 ,p_rule_rec.rollup_obj_id
5151 ,p_rule_rec.hier_obj_def_id
5152 ,l_parent_depth_num;
5153
5154 end if;
5155
5156 loop
5157
5158 fetch l_find_parent_nodes_csr
5159 bulk collect into
5160 l_parent_id_tbl
5161 limit g_fetch_limit;
5162
5163 l_find_parent_nodes_last_row := l_parent_id_tbl.LAST;
5164 if (l_find_parent_nodes_last_row is null) then
5165 exit;
5166 end if;
5167
5168 for i in 1..l_find_parent_nodes_last_row loop
5169
5170 --todo: MP integration
5171 Rollup_Parent_Node (
5172 p_request_id => p_request_rec.request_id
5173 ,p_rollup_obj_id => p_rule_rec.rollup_obj_id
5174 ,p_hier_obj_def_id => p_rule_rec.hier_obj_def_id
5175 ,p_dimension_varchar_label => l_dimension_rec.dimension_varchar_label
5176 ,p_rollup_type_code => p_request_rec.rollup_type_code
5177 ,p_cond_exists => p_rule_rec.cond_exists
5178 ,p_sequence_name => p_rule_rec.sequence_name
5179 ,p_source_system_code => p_request_rec.source_system_code
5180 ,p_ledger_id => p_request_rec.ledger_id
5181 ,p_parent_id => l_parent_id_tbl(i)
5182 ,p_parent_depth_num => l_parent_depth_num
5183 ,p_statistic_basis_id => p_rule_rec.statistic_basis_id
5184 ,p_find_children_stmt => p_find_children_stmt
5185 ,p_rollup_parent_stmt => p_rollup_parent_stmt
5186 ,p_find_child_chains_stmt => p_find_child_chains_stmt
5187 ,p_output_dataset_code => p_request_rec.output_dataset_code
5188 ,p_output_cal_period_id => p_request_rec.output_cal_period_id
5189 ,p_exch_rate_date => p_request_rec.exch_rate_date
5190 ,p_functional_currency_code => p_request_rec.functional_currency_code
5191 ,p_entered_currency_code => p_rule_rec.entered_currency_code
5192 ,p_entered_exch_rate_num => p_rule_rec.entered_exch_rate_num
5193 ,p_entered_exch_rate_den => p_rule_rec.entered_exch_rate_den
5194 ,p_user_id => p_request_rec.user_id
5195 ,p_login_id => p_request_rec.login_id
5196 );
5197
5198 end loop;
5199
5200 l_parent_id_tbl.DELETE;
5201
5202 end loop;
5203
5204 close l_find_parent_nodes_csr;
5205
5206 -- Go to the next parent depth number
5207 l_parent_depth_num := l_parent_depth_num - 1;
5208
5209 end loop;
5210
5211 if (l_dimension_rec.dimension_varchar_label = 'COST_OBJECT') then
5212
5213 ----------------------------------------------------------------------------
5214 -- STEP 7.a: Purge all records in FEM_RU_COST_OBJ_HIER_T
5215 ----------------------------------------------------------------------------
5216 FEM_ENGINES_PKG.Tech_Message (
5217 p_severity => G_LOG_LEVEL_1
5218 ,p_module => G_BLOCK||'.'||l_api_name
5219 ,p_msg_text => 'Step 7.a: Purge all records in FEM_RU_COST_OBJ_HIER_T'
5220 );
5221
5222 delete from fem_ru_cost_obj_hier_t
5223 where created_by_request_id = p_request_rec.request_id
5224 and created_by_object_id = p_rule_rec.rollup_obj_id;
5225
5226 commit;
5227
5228 elsif ( (l_dimension_rec.dimension_varchar_label = 'ACTIVITY')
5229 and (p_rule_rec.cond_exists) ) then
5230
5231 ----------------------------------------------------------------------------
5232 -- STEP 7.b: Purge all records in FEM_RU_ACTIVITIES_HIER_T
5233 ----------------------------------------------------------------------------
5234 FEM_ENGINES_PKG.Tech_Message (
5235 p_severity => G_LOG_LEVEL_1
5236 ,p_module => G_BLOCK||'.'||l_api_name
5237 ,p_msg_text => 'Step 7.b: Purge all records in FEM_RU_ACTIVITIES_HIER_T'
5238 );
5239
5240 delete from fem_ru_activities_hier_t
5241 where created_by_request_id = p_request_rec.request_id
5242 and created_by_object_id = p_rule_rec.rollup_obj_id;
5243
5244 commit;
5245
5246 end if;
5247
5248 FEM_ENGINES_PKG.Tech_Message (
5249 p_severity => G_LOG_LEVEL_2
5250 ,p_module => G_BLOCK||'.'||l_api_name
5251 ,p_msg_text => 'END'
5252 );
5253
5254 EXCEPTION
5255
5256 when l_rollup_top_node_error then
5257
5258 if (l_find_parent_nodes_csr%ISOPEN) then
5259 close l_find_parent_nodes_csr;
5260 end if;
5261
5262 FEM_ENGINES_PKG.Tech_Message (
5263 p_severity => G_LOG_LEVEL_6
5264 ,p_module => G_BLOCK||'.'||l_api_name
5265 ,p_msg_text => 'Rollup Top Node Exception'
5266 );
5267
5268 raise g_rollup_request_error;
5269
5270 when g_rollup_request_error then
5271
5272 if (l_find_parent_nodes_csr%ISOPEN) then
5273 close l_find_parent_nodes_csr;
5274 end if;
5275
5276 raise g_rollup_request_error;
5277
5278 when others then
5279
5280 g_prg_msg := SQLERRM;
5281 g_callstack := DBMS_UTILITY.Format_Call_Stack;
5282
5283 if (l_find_parent_nodes_csr%ISOPEN) then
5284 close l_find_parent_nodes_csr;
5285 end if;
5286
5287 FEM_ENGINES_PKG.Tech_Message (
5288 p_severity => G_LOG_LEVEL_6
5289 ,p_module => G_BLOCK||'.'||l_api_name||'.Unexpected_Exception'
5290 ,p_msg_text => g_prg_msg
5291 );
5292
5293 FEM_ENGINES_PKG.Tech_Message (
5294 p_severity => G_LOG_LEVEL_6
5295 ,p_module => G_BLOCK||'.'||l_api_name||'.Unexpected_Exception'
5296 ,p_msg_text => g_callstack
5297 );
5298
5299 FEM_ENGINES_PKG.User_Message (
5300 p_app_name => G_FEM
5301 ,p_msg_name => G_UNEXPECTED_ERROR
5302 ,p_token1 => 'ERR_MSG'
5303 ,p_value1 => g_prg_msg
5304 );
5305
5306 raise g_rollup_request_error;
5307
5308 END Rollup_Top_Node;
5309
5310
5311
5312 /*============================================================================+
5313 | PROCEDURE
5314 | Rollup_Parent_Node
5315 |
5316 | DESCRIPTION
5317 | Peforms single-level rollup on the specified parent node and parent depth
5318 | of a rollup hierarchy.
5319 |
5320 | Rollup processing is done by querying for all the child nodes that exist
5321 | for a the specified parent node and parent depth. For each child node,
5322 | we rollup all the data records in FEM_BALANCES to the parent node.
5323 |
5324 | If the track events flag is set, then we must register the chain
5325 | dependency of all the child node data records in FEM_BALANCES with respect
5326 | to the rollup parent node data records in FEM_BALANCES.
5327 |
5328 | For cost object hierarchies, special processing is necessary to handle
5329 | cross ledger child nodes. A cross ledger may have a functional currency
5330 | code that differs from the request's ledger currency code. If that's the
5331 | case, the appropriate exchange rate must be used for rolling up all the
5332 | child node's data records in FEM_BALANCES to the parent node.
5333 |
5334 | SCOPE - PRIVATE
5335 |
5336 +============================================================================*/
5337
5338 PROCEDURE Rollup_Parent_Node (
5339 p_request_id in number
5340 ,p_rollup_obj_id in number
5341 ,p_hier_obj_def_id in number
5342 ,p_dimension_varchar_label in varchar2
5343 ,p_rollup_type_code in varchar2
5344 ,p_cond_exists in boolean
5345 ,p_sequence_name in varchar2
5346 ,p_source_system_code in number
5347 ,p_ledger_id in number
5348 ,p_parent_id in number
5349 ,p_parent_depth_num in number
5350 ,p_statistic_basis_id in number
5351 ,p_find_children_stmt in long
5352 ,p_rollup_parent_stmt in long
5353 ,p_find_child_chains_stmt in long
5354 ,p_output_dataset_code in number
5355 ,p_output_cal_period_id in number
5356 ,p_exch_rate_date in date
5357 ,p_functional_currency_code in varchar2
5358 ,p_entered_currency_code in varchar2
5359 ,p_entered_exch_rate_num in number
5360 ,p_entered_exch_rate_den in number
5361 ,p_user_id in number
5362 ,p_login_id in number
5363 )
5364 IS
5365
5366 l_api_name constant varchar2(30) := 'Rollup_Parent_Node';
5367
5368 l_costed_flag varchar2(1);
5369 l_xledger_id number;
5370 l_child_exch_rate_den number;
5371 l_child_exch_rate_num number;
5372 l_dummy_date date;
5373
5374 l_child_id_tbl number_table;
5375 l_weighting_pct_tbl number_table;
5376 l_xledger_id_tbl number_table;
5377
5378 l_find_children_csr dynamic_cursor;
5379 l_find_children_last_row number;
5380
5381 l_rollup_parent_node_error exception;
5382
5383 BEGIN
5384
5385 FEM_ENGINES_PKG.Tech_Message (
5386 p_severity => G_LOG_LEVEL_2
5387 ,p_module => G_BLOCK||'.'||l_api_name
5388 ,p_msg_text => 'BEGIN'
5389 );
5390
5391 if (p_dimension_varchar_label = 'COST_OBJECT') then
5392
5393 ----------------------------------------------------------------------------
5394 -- STEP 1: Check to see if the parent has been costed in a Cost Object
5395 -- hierarchy
5396 ----------------------------------------------------------------------------
5397 FEM_ENGINES_PKG.Tech_Message (
5398 p_severity => G_LOG_LEVEL_1
5399 ,p_module => G_BLOCK||'.'||l_api_name
5400 ,p_msg_text => 'Step 1: Check if Parent Cost Object has been costed'
5401 );
5402
5403 select costed_flag
5404 into l_costed_flag
5405 from fem_ru_nodes_t
5406 where created_by_request_id = p_request_id
5407 and created_by_object_id = p_rollup_obj_id
5408 and node_id = p_parent_id;
5409
5410 if (l_costed_flag = 'Y') then
5411 return;
5412 end if;
5413
5414 end if;
5415
5416 ------------------------------------------------------------------------------
5417 -- STEP 2: Find All Parent-Child Relationships for a hierarchy at the
5418 -- specified parent node id and parent depth num
5419 ------------------------------------------------------------------------------
5420 FEM_ENGINES_PKG.Tech_Message (
5421 p_severity => G_LOG_LEVEL_1
5422 ,p_module => G_BLOCK||'.'||l_api_name
5423 ,p_msg_text => 'Step 2: Find All Parent-Child Relationships'
5424 );
5425
5426 -- Build SQL statement for finding all Parent-Child Relationships
5427 if (p_dimension_varchar_label = 'COST_OBJECT') then
5428
5429 open l_find_children_csr
5430 for p_find_children_stmt
5431 using p_request_id
5432 ,p_rollup_obj_id
5433 ,p_parent_id
5434 ,p_parent_depth_num;
5435
5436 elsif (p_dimension_varchar_label = 'ACTIVITY') then
5437
5438 -- If condition exists, we must include request_id and rollup_obj_id in
5439 -- the where clause for querying FEM_RU_ACTIVITIES_HIER_T
5440 if (p_cond_exists) then
5441
5442 open l_find_children_csr
5443 for p_find_children_stmt
5444 using p_hier_obj_def_id
5445 ,p_parent_id
5446 ,p_parent_depth_num
5447 ,p_request_id
5448 ,p_rollup_obj_id;
5449
5450 else
5451
5452 open l_find_children_csr
5453 for p_find_children_stmt
5454 using p_hier_obj_def_id
5455 ,p_parent_id
5456 ,p_parent_depth_num;
5457
5458 end if;
5459
5460 else
5461
5462 FEM_ENGINES_PKG.User_Message (
5463 p_app_name => G_FEM
5464 ,p_msg_name => G_RU_NO_ROLLUP_DIM_ERR
5465 ,p_token1 => 'DIMENSION_VARCHAR_LABEL'
5466 ,p_value1 => p_dimension_varchar_label
5467 );
5468 raise l_rollup_parent_node_error;
5469
5470 end if;
5471
5472 loop
5473
5474 fetch l_find_children_csr
5475 bulk collect into
5476 l_child_id_tbl
5477 ,l_weighting_pct_tbl
5478 ,l_xledger_id_tbl
5479 limit g_fetch_limit;
5480
5481 l_find_children_last_row := l_child_id_tbl.LAST;
5482 if (l_find_children_last_row is null) then
5483 exit;
5484 end if;
5485
5486 for i in 1..l_find_children_last_row loop
5487
5488 if (p_dimension_varchar_label = 'COST_OBJECT') then
5489
5490 if (l_xledger_id_tbl(i) is not null) then
5491
5492 ----------------------------------------------------------------------
5493 -- STEP 3: Child Cross Ledger Processing
5494 ----------------------------------------------------------------------
5495 FEM_ENGINES_PKG.Tech_Message (
5496 p_severity => G_LOG_LEVEL_1
5497 ,p_module => G_BLOCK||'.'||l_api_name
5498 ,p_msg_text => 'Step 3: Child Cross Ledger Processing'
5499 );
5500
5501 -- Set the cross ledger
5502 l_xledger_id := l_xledger_id_tbl(i);
5503
5504 -- If the cross ledger id does not exist in the cross ledger table,
5505 -- then get the cross ledger's currency code.
5506 if ( not g_xledger_tbl.EXISTS(l_xledger_id) ) then
5507
5508 Get_Ledger_Currency_Code (
5509 p_ledger_id => l_xledger_id
5510 ,x_currency_code => g_xledger_tbl(l_xledger_id).currency_code
5511 );
5512
5513 -- If the cross ledger's currency code is the same as the request's
5514 -- functional currency code , then set all the exchange rate
5515 -- variables to 1. If they differ, then call the GL Currency API
5516 -- to obtain the appropriate values for the exchange rate variables.
5517 if (g_xledger_tbl(l_xledger_id).currency_code = p_functional_currency_code) then
5518
5519 -- Default the exchange rate to 1 as the cross ledger currency
5520 -- is the same as the request's ledger functional currency
5521 g_xledger_tbl(l_xledger_id).exch_rate_den := 1;
5522 g_xledger_tbl(l_xledger_id).exch_rate_num := 1;
5523 g_xledger_tbl(l_xledger_id).exch_rate := 1;
5524
5525 else
5526
5527 begin
5528 GL_CURRENCY_API.Get_Triangulation_Rate (
5529 x_from_currency => g_xledger_tbl(l_xledger_id).currency_code
5530 ,x_to_currency => p_functional_currency_code
5531 ,x_conversion_date => p_exch_rate_date
5532 ,x_conversion_type => g_currency_conv_type
5533 ,x_numerator => g_xledger_tbl(l_xledger_id).exch_rate_num
5534 ,x_denominator => g_xledger_tbl(l_xledger_id).exch_rate_den
5535 ,x_rate => g_xledger_tbl(l_xledger_id).exch_rate
5536 );
5537 exception
5538 when GL_CURRENCY_API.NO_RATE then
5539 FEM_ENGINES_PKG.User_Message (
5540 p_app_name => G_FEM
5541 ,p_msg_name => G_ENG_NO_EXCH_RATE_ERR
5542 ,p_token1 => 'FROM_CURRENCY_CODE'
5543 ,p_value1 => g_xledger_tbl(l_xledger_id).currency_code
5544 ,p_token2 => 'TO_CURRENCY_CODE'
5545 ,p_value2 => p_functional_currency_code
5546 ,p_token3 => 'CONVERSION_DATE'
5547 ,p_value3 => FND_DATE.date_to_chardate(p_exch_rate_date)
5548 ,p_token4 => 'CONVERSION_TYPE'
5549 ,p_value4 => g_currency_conv_type
5550 );
5551 raise l_rollup_parent_node_error;
5552 when GL_CURRENCY_API.INVALID_CURRENCY then
5553 FEM_ENGINES_PKG.User_Message (
5554 p_app_name => G_FEM
5555 ,p_msg_name => G_ENG_BAD_CURRENCY_ERR
5556 ,p_token1 => 'FROM_CURRENCY_CODE'
5557 ,p_value1 => g_xledger_tbl(l_xledger_id).currency_code
5558 ,p_token2 => 'TO_CURRENCY_CODE'
5559 ,p_value2 => p_functional_currency_code
5560 ,p_token3 => 'CONVERSION_DATE'
5561 ,p_value3 => FND_DATE.date_to_chardate(p_exch_rate_date)
5562 ,p_token4 => 'CONVERSION_TYPE'
5563 ,p_value4 => g_currency_conv_type
5564 );
5565 raise l_rollup_parent_node_error;
5566 end;
5567
5568 end if;
5569
5570 end if;
5571
5572 -- Set the local exchange rate variables from the cross ledger table
5573 -- for rolling up a child to its parent.
5574 l_child_exch_rate_den := g_xledger_tbl(l_xledger_id).exch_rate_den;
5575 l_child_exch_rate_num := g_xledger_tbl(l_xledger_id).exch_rate_num;
5576
5577 else
5578
5579 -- Set all the local exchange rate variables to 1 for rolling up a
5580 -- child to its parent.
5581 l_child_exch_rate_den := 1;
5582 l_child_exch_rate_num := 1;
5583
5584 end if;
5585
5586 end if;
5587
5588 if (g_track_event_chains) then
5589
5590 ------------------------------------------------------------------------
5591 -- STEP 4: Register Child Chains
5592 ------------------------------------------------------------------------
5593 FEM_ENGINES_PKG.Tech_Message (
5594 p_severity => G_LOG_LEVEL_1
5595 ,p_module => G_BLOCK||'.'||l_api_name
5596 ,p_msg_text => 'Step 4: Register Child Chains'
5597 );
5598
5599 Register_Child_Chains (
5600 p_request_id => p_request_id
5601 ,p_rollup_obj_id => p_rollup_obj_id
5602 ,p_dimension_varchar_label => p_dimension_varchar_label
5603 ,p_rollup_type_code => p_rollup_type_code
5604 ,p_ledger_id => p_ledger_id
5605 ,p_statistic_basis_id => p_statistic_basis_id
5606 ,p_find_child_chains_stmt => p_find_child_chains_stmt
5607 ,p_child_id => l_child_id_tbl(i)
5608 ,p_user_id => p_user_id
5609 ,p_login_id => p_login_id
5610 );
5611
5612 end if;
5613
5614 --------------------------------------------------------------------------
5615 -- STEP 5: Rollup to the specified parent all data records in FEM_BALANCES
5616 -- of the specified child
5617 --------------------------------------------------------------------------
5618 FEM_ENGINES_PKG.Tech_Message (
5619 p_severity => G_LOG_LEVEL_1
5620 ,p_module => G_BLOCK||'.'||l_api_name
5621 ,p_msg_text => 'Step 5.'||to_char(i)||': Rollup Child ID = '||to_char(l_child_id_tbl(i))
5622 );
5623
5624 if (p_dimension_varchar_label = 'COST_OBJECT') then
5625
5626 execute immediate p_rollup_parent_stmt
5627 using p_source_system_code
5628 ,p_entered_currency_code
5629 ,l_child_id_tbl(i)
5630 ,p_parent_id
5631 ,p_output_dataset_code
5632 ,p_output_cal_period_id
5633 ,p_request_id
5634 ,p_rollup_obj_id
5635 ,l_weighting_pct_tbl(i)
5636 ,l_child_exch_rate_den
5637 ,l_child_exch_rate_num
5638 ,p_entered_exch_rate_den
5639 ,p_entered_exch_rate_num
5640 ,l_weighting_pct_tbl(i)
5641 ,l_child_exch_rate_den
5642 ,l_child_exch_rate_num
5643 ,p_request_id
5644 ,p_rollup_obj_id
5645 ,p_output_dataset_code
5646 ,p_output_cal_period_id
5647 ,p_request_id
5648 ,p_rollup_obj_id
5649 ,p_request_id
5650 ,p_rollup_obj_id
5651 ,l_weighting_pct_tbl(i)
5652 ,l_child_exch_rate_den
5653 ,l_child_exch_rate_num
5654 ,p_entered_exch_rate_den
5655 ,p_entered_exch_rate_num
5656 ,l_weighting_pct_tbl(i)
5657 ,l_child_exch_rate_den
5658 ,l_child_exch_rate_num;
5659
5660 elsif (p_dimension_varchar_label = 'ACTIVITY') then
5661
5662 if (p_rollup_type_code = 'COST') then
5663
5664 execute immediate p_rollup_parent_stmt
5665 using p_source_system_code
5666 ,p_entered_currency_code
5667 ,l_child_id_tbl(i)
5668 ,p_parent_id
5669 ,p_ledger_id
5670 ,p_output_dataset_code
5671 ,p_output_cal_period_id
5672 ,p_request_id
5673 ,p_rollup_obj_id
5674 ,l_weighting_pct_tbl(i)
5675 ,p_entered_exch_rate_den
5676 ,p_entered_exch_rate_num
5677 ,l_weighting_pct_tbl(i)
5678 ,p_request_id
5679 ,p_rollup_obj_id
5680 ,p_output_dataset_code
5681 ,p_output_cal_period_id
5682 ,p_request_id
5683 ,p_rollup_obj_id
5684 ,p_request_id
5685 ,p_rollup_obj_id
5686 ,l_weighting_pct_tbl(i)
5687 ,p_entered_exch_rate_den
5688 ,p_entered_exch_rate_num
5689 ,l_weighting_pct_tbl(i);
5690
5691 elsif (p_rollup_type_code = 'STAT') then
5692
5693 execute immediate p_rollup_parent_stmt
5694 using p_source_system_code
5695 ,p_entered_currency_code
5696 ,l_child_id_tbl(i)
5697 ,p_parent_id
5698 ,p_ledger_id
5699 ,p_statistic_basis_id
5700 ,p_output_dataset_code
5701 ,p_output_cal_period_id
5702 ,p_request_id
5703 ,p_rollup_obj_id
5704 ,l_weighting_pct_tbl(i)
5705 ,p_entered_exch_rate_den
5706 ,p_entered_exch_rate_num
5707 ,l_weighting_pct_tbl(i)
5708 ,p_request_id
5709 ,p_rollup_obj_id
5710 ,p_output_dataset_code
5711 ,p_output_cal_period_id
5712 ,p_request_id
5713 ,p_rollup_obj_id
5714 ,p_request_id
5715 ,p_rollup_obj_id
5716 ,l_weighting_pct_tbl(i)
5717 ,p_entered_exch_rate_den
5718 ,p_entered_exch_rate_num
5719 ,l_weighting_pct_tbl(i);
5720
5721 end if;
5722
5723 end if;
5724
5725 commit;
5726
5727 end loop;
5728
5729 ----------------------------------------------------------------------------
5730 -- STEP 6: Mark all the uncosted children that were processes as costed
5731 ----------------------------------------------------------------------------
5732 FEM_ENGINES_PKG.Tech_Message (
5733 p_severity => G_LOG_LEVEL_1
5734 ,p_module => G_BLOCK||'.'||l_api_name
5735 ,p_msg_text => 'Step 6: Mark All Uncosted Children Just Processed as Costed'
5736 );
5737
5738 forall i in 1..l_find_children_last_row
5739 update fem_ru_nodes_t
5740 set costed_flag = 'Y'
5741 where created_by_request_id = p_request_id
5742 and created_by_object_id = p_rollup_obj_id
5743 and node_id = l_child_id_tbl(i)
5744 and costed_flag = 'N';
5745
5746 l_child_id_tbl.DELETE;
5747 l_weighting_pct_tbl.DELETE;
5748 l_xledger_id_tbl.DELETE;
5749
5750 end loop;
5751
5752 close l_find_children_csr;
5753
5754 ------------------------------------------------------------------------------
5755 -- STEP 7: Mark the parent as costed
5756 ------------------------------------------------------------------------------
5757 FEM_ENGINES_PKG.Tech_Message (
5758 p_severity => G_LOG_LEVEL_1
5759 ,p_module => G_BLOCK||'.'||l_api_name
5760 ,p_msg_text => 'Step 7: Mark the Parent as Costed'
5761 );
5762
5763 update fem_ru_nodes_t
5764 set costed_flag = 'Y'
5765 where created_by_request_id = p_request_id
5766 and created_by_object_id = p_rollup_obj_id
5767 and node_id = p_parent_id;
5768
5769 commit;
5770
5771 FEM_ENGINES_PKG.Tech_Message (
5772 p_severity => G_LOG_LEVEL_2
5773 ,p_module => G_BLOCK||'.'||l_api_name
5774 ,p_msg_text => 'END'
5775 );
5776
5777 EXCEPTION
5778
5779 when l_rollup_parent_node_error then
5780
5781 if (l_find_children_csr%ISOPEN) then
5782 close l_find_children_csr;
5783 end if;
5784
5785 FEM_ENGINES_PKG.Tech_Message (
5786 p_severity => G_LOG_LEVEL_6
5787 ,p_module => G_BLOCK||'.'||l_api_name
5788 ,p_msg_text => 'Rollup Parent Node Exception'
5789 );
5790
5791 raise g_rollup_request_error;
5792
5793 when g_rollup_request_error then
5794
5795 if (l_find_children_csr%ISOPEN) then
5796 close l_find_children_csr;
5797 end if;
5798
5799 raise g_rollup_request_error;
5800
5801 when others then
5802
5803 g_prg_msg := SQLERRM;
5804 g_callstack := DBMS_UTILITY.Format_Call_Stack;
5805
5806 if (l_find_children_csr%ISOPEN) then
5807 close l_find_children_csr;
5808 end if;
5809
5810 FEM_ENGINES_PKG.Tech_Message (
5811 p_severity => G_LOG_LEVEL_6
5812 ,p_module => G_BLOCK||'.'||l_api_name||'.Unexpected_Exception'
5813 ,p_msg_text => g_prg_msg
5814 );
5815
5816 FEM_ENGINES_PKG.Tech_Message (
5817 p_severity => G_LOG_LEVEL_6
5818 ,p_module => G_BLOCK||'.'||l_api_name||'.Unexpected_Exception'
5819 ,p_msg_text => g_callstack
5820 );
5821
5822 FEM_ENGINES_PKG.User_Message (
5823 p_app_name => G_FEM
5824 ,p_msg_name => G_UNEXPECTED_ERROR
5825 ,p_token1 => 'ERR_MSG'
5826 ,p_value1 => g_prg_msg
5827 );
5828
5829 raise g_rollup_request_error;
5830
5831 END Rollup_Parent_Node;
5832
5833
5834
5835 /*============================================================================+
5836 | PROCEDURE
5837 | Register_Child_Chains
5838 |
5839 | DESCRIPTION
5840 | Registers the child chains in the processing locks table.
5841 |
5842 | SCOPE - PRIVATE
5843 |
5844 +============================================================================*/
5845 PROCEDURE Register_Child_Chains (
5846 p_request_id in number
5847 ,p_rollup_obj_id in number
5848 ,p_dimension_varchar_label in varchar2
5849 ,p_rollup_type_code in varchar2
5850 ,p_ledger_id in number
5851 ,p_statistic_basis_id in number
5852 ,p_find_child_chains_stmt in long
5853 ,p_child_id in number
5854 ,p_user_id in number
5855 ,p_login_id in number
5856 )
5857 IS
5858
5859 l_api_name constant varchar2(30) := 'Register_Child_Chains';
5860
5861 l_created_by_request_id_tbl number_table;
5862 l_created_by_object_id_tbl number_table;
5863
5864 l_find_child_chains_csr dynamic_cursor;
5865 l_find_child_chains_last_row number;
5866
5867 l_return_status t_return_status%TYPE;
5868 l_msg_count t_msg_count%TYPE;
5869 l_msg_data t_msg_data%TYPE;
5870
5871 l_register_child_chains_error exception;
5872
5873 BEGIN
5874
5875 FEM_ENGINES_PKG.Tech_Message (
5876 p_severity => G_LOG_LEVEL_2
5877 ,p_module => G_BLOCK||'.'||l_api_name
5878 ,p_msg_text => 'BEGIN'
5879 );
5880
5881 if (p_dimension_varchar_label = 'COST_OBJECT') then
5882
5883 open l_find_child_chains_csr
5884 for p_find_child_chains_stmt
5885 using p_child_id
5886 ,p_request_id
5887 ,p_rollup_obj_id
5888 ,p_request_id
5889 ,p_rollup_obj_id;
5890
5891 elsif (p_dimension_varchar_label = 'ACTIVITY') then
5892
5893 if (p_rollup_type_code = 'COST') then
5894
5895 open l_find_child_chains_csr
5896 for p_find_child_chains_stmt
5897 using p_ledger_id
5898 ,p_child_id
5899 ,p_request_id
5900 ,p_rollup_obj_id
5901 ,p_request_id
5902 ,p_rollup_obj_id;
5903
5904 elsif (p_rollup_type_code = 'STAT') then
5905
5906 open l_find_child_chains_csr
5907 for p_find_child_chains_stmt
5908 using p_ledger_id
5909 ,p_statistic_basis_id
5910 ,p_child_id
5911 ,p_request_id
5912 ,p_rollup_obj_id
5913 ,p_request_id
5914 ,p_rollup_obj_id;
5915
5916 end if;
5917
5918 end if;
5919
5920 loop
5921
5922 fetch l_find_child_chains_csr
5923 bulk collect into
5924 l_created_by_request_id_tbl
5925 ,l_created_by_object_id_tbl
5926 limit g_fetch_limit;
5927
5928 l_find_child_chains_last_row := l_created_by_request_id_tbl.LAST;
5929 if (l_find_child_chains_last_row is null) then
5930 exit;
5931 end if;
5932
5933 for i in 1..l_find_child_chains_last_row loop
5934
5935 -- Call the FEM_PL_PKG.Register_Chain API procedure to register
5936 -- the specified chain.
5937 FEM_PL_PKG.Register_Chain (
5938 p_api_version => 1.0
5939 ,p_commit => FND_API.G_FALSE
5940 ,p_request_id => p_request_id
5941 ,p_object_id => p_rollup_obj_id
5942 ,p_source_created_by_request_id => l_created_by_request_id_tbl(i)
5943 ,p_source_created_by_object_id => l_created_by_object_id_tbl(i)
5944 ,p_user_id => p_user_id
5945 ,p_last_update_login => p_login_id
5946 ,x_msg_count => l_msg_count
5947 ,x_msg_data => l_msg_data
5948 ,x_return_status => l_return_status
5949 );
5950
5951 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
5952 Get_Put_Messages (
5953 p_msg_count => l_msg_count
5954 ,p_msg_data => l_msg_data
5955 );
5956 raise l_register_child_chains_error;
5957 end if;
5958
5959 end loop;
5960
5961 l_created_by_request_id_tbl.DELETE;
5962 l_created_by_object_id_tbl.DELETE;
5963
5964 commit;
5965
5966 end loop;
5967
5968 close l_find_child_chains_csr;
5969
5970 FEM_ENGINES_PKG.Tech_Message (
5971 p_severity => G_LOG_LEVEL_2
5972 ,p_module => G_BLOCK||'.'||l_api_name
5973 ,p_msg_text => 'END'
5974 );
5975
5976 EXCEPTION
5977
5978 when l_register_child_chains_error then
5979
5980 if (l_find_child_chains_csr%ISOPEN) then
5981 close l_find_child_chains_csr;
5982 end if;
5983
5984 FEM_ENGINES_PKG.Tech_Message (
5985 p_severity => G_LOG_LEVEL_6
5986 ,p_module => G_BLOCK||'.'||l_api_name
5987 ,p_msg_text => 'Register Child Chains Exception'
5988 );
5989
5990 raise g_rollup_request_error;
5991
5992 when g_rollup_request_error then
5993
5994 if (l_find_child_chains_csr%ISOPEN) then
5995 close l_find_child_chains_csr;
5996 end if;
5997
5998 raise g_rollup_request_error;
5999
6000 when others then
6001
6002 g_prg_msg := SQLERRM;
6003 g_callstack := DBMS_UTILITY.Format_Call_Stack;
6004
6005 if (l_find_child_chains_csr%ISOPEN) then
6006 close l_find_child_chains_csr;
6007 end if;
6008
6009 FEM_ENGINES_PKG.Tech_Message (
6010 p_severity => G_LOG_LEVEL_6
6011 ,p_module => G_BLOCK||'.'||l_api_name||'.Unexpected_Exception'
6012 ,p_msg_text => g_prg_msg
6013 );
6014
6015 FEM_ENGINES_PKG.Tech_Message (
6016 p_severity => G_LOG_LEVEL_6
6017 ,p_module => G_BLOCK||'.'||l_api_name||'.Unexpected_Exception'
6018 ,p_msg_text => g_callstack
6019 );
6020
6021 FEM_ENGINES_PKG.User_Message (
6022 p_app_name => G_FEM
6023 ,p_msg_name => G_UNEXPECTED_ERROR
6024 ,p_token1 => 'ERR_MSG'
6025 ,p_value1 => g_prg_msg
6026 );
6027
6028 raise g_rollup_request_error;
6029
6030 END Register_Child_Chains;
6031
6032
6033
6034 /*============================================================================+
6035 | PROCEDURE
6036 | Rule_Post_Proc
6037 |
6038 | DESCRIPTION
6039 | Updates the status of the object execution in the
6040 | processing locks tables.
6041 |
6042 | SCOPE - PRIVATE
6043 |
6044 +============================================================================*/
6045
6046 PROCEDURE Rule_Post_Proc (
6047 p_request_rec in request_record
6048 ,p_rule_rec in rule_record
6049 ,p_num_of_input_rows_stmt in long
6050 ,p_exec_status_code in varchar2
6051 )
6052 IS
6053
6054 l_api_name constant varchar2(30) := 'Rule_Post_Proc';
6055
6056 l_dimension_rec dimension_record;
6057
6058 l_num_of_input_rows number;
6059 l_num_of_output_rows number;
6060
6061 l_return_status t_return_status%TYPE;
6062 l_msg_count t_msg_count%TYPE;
6063 l_msg_data t_msg_data%TYPE;
6064
6065 l_rule_post_proc_error exception;
6066
6067 BEGIN
6068
6069 FEM_ENGINES_PKG.Tech_Message (
6070 p_severity => G_LOG_LEVEL_2
6071 ,p_module => G_BLOCK||'.'||l_api_name
6072 ,p_msg_text => 'BEGIN'
6073 );
6074
6075 l_dimension_rec := p_request_rec.dimension_rec;
6076
6077 ------------------------------------------------------------------------------
6078 -- STEP 1: Drop all Temp Objects created for the Rollup Rule
6079 ------------------------------------------------------------------------------
6080 FEM_ENGINES_PKG.Tech_Message (
6081 p_severity => G_LOG_LEVEL_1
6082 ,p_module => G_BLOCK||'.'||l_api_name
6083 ,p_msg_text => 'Step 1: Drop all Temp Objects'
6084 );
6085
6086 Drop_Temp_Objects (
6087 p_request_rec => p_request_rec
6088 ,p_rule_rec => p_rule_rec
6089 );
6090
6091 if (p_exec_status_code = G_EXEC_STATUS_SUCCESS) then
6092
6093 ----------------------------------------------------------------------------
6094 -- STEP 2: If a successful object execution, update number of input rows in
6095 -- FEM_BALANCES before purging FEM_RU_NODES_T.
6096 ----------------------------------------------------------------------------
6097 FEM_ENGINES_PKG.Tech_Message (
6098 p_severity => G_LOG_LEVEL_1
6099 ,p_module => G_BLOCK||'.'||l_api_name
6100 ,p_msg_text => 'Step 2: Update Number of Input Rows'
6101 );
6102
6103 if (l_dimension_rec.dimension_varchar_label = 'COST_OBJECT') then
6104
6105 execute immediate p_num_of_input_rows_stmt
6106 into l_num_of_input_rows
6107 using p_request_rec.request_id
6108 ,p_rule_rec.rollup_obj_id
6109 ,p_request_rec.request_id
6110 ,p_rule_rec.rollup_obj_id;
6111
6112 elsif (l_dimension_rec.dimension_varchar_label = 'ACTIVITY') then
6113
6114 if (p_request_rec.rollup_type_code = 'COST') then
6115
6116 execute immediate p_num_of_input_rows_stmt
6117 into l_num_of_input_rows
6118 using p_request_rec.ledger_id
6119 ,p_request_rec.request_id
6120 ,p_rule_rec.rollup_obj_id
6121 ,p_request_rec.request_id
6122 ,p_rule_rec.rollup_obj_id;
6123
6124 elsif (p_request_rec.rollup_type_code = 'STAT') then
6125
6126 execute immediate p_num_of_input_rows_stmt
6127 into l_num_of_input_rows
6128 using p_request_rec.ledger_id
6129 ,p_rule_rec.statistic_basis_id
6130 ,p_request_rec.request_id
6131 ,p_rule_rec.rollup_obj_id
6132 ,p_request_rec.request_id
6133 ,p_rule_rec.rollup_obj_id;
6134
6135 end if;
6136
6137 end if;
6138
6139 FEM_PL_PKG.Update_Num_Of_Input_Rows (
6140 p_api_version => 1.0
6141 ,p_commit => FND_API.G_FALSE
6142 ,p_request_id => p_request_rec.request_id
6143 ,p_object_id => p_rule_rec.rollup_obj_id
6144 ,p_num_of_input_rows => l_num_of_input_rows
6145 ,p_user_id => p_request_rec.user_id
6146 ,p_last_update_login => p_request_rec.login_id
6147 ,x_msg_count => l_msg_count
6148 ,x_msg_data => l_msg_data
6149 ,x_return_status => l_return_status
6150 );
6151
6152 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
6153 Get_Put_Messages (
6154 p_msg_count => l_msg_count
6155 ,p_msg_data => l_msg_data
6156 );
6157 raise l_rule_post_proc_error;
6158 end if;
6159
6160 commit;
6161
6162 end if;
6163
6164 ------------------------------------------------------------------------------
6165 -- STEP 3: Delete all records in the FEM_RU_NODES_T table
6166 ------------------------------------------------------------------------------
6167 --todo: should only delete records for p_exec_status_code = SUCCESS. But
6168 --until we bring in error reprocessing, must always delete temp data.
6169 FEM_ENGINES_PKG.Tech_Message (
6170 p_severity => G_LOG_LEVEL_1
6171 ,p_module => G_BLOCK||'.'||l_api_name
6172 ,p_msg_text => 'Step 3: Purging Records in FEM_RU_NODES_T'
6173 );
6174
6175 delete from fem_ru_nodes_t
6176 where created_by_request_id = p_request_rec.request_id
6177 and created_by_object_id = p_rule_rec.rollup_obj_id;
6178
6179 commit;
6180
6181 -- Only need to purge the hierarchy rollup tables if there was an error
6182 if (p_exec_status_code <> G_EXEC_STATUS_SUCCESS) then
6183
6184 if (l_dimension_rec.dimension_varchar_label = 'COST_OBJECT') then
6185
6186 --------------------------------------------------------------------------
6187 -- STEP 4.a: For a COUC rollup, need to delete all records for this
6188 -- request id in the FEM_RU_COST_OBJ_HIER_T table
6189 --------------------------------------------------------------------------
6190 --todo: Until we bring in error reprocessing, must all ways delete temp data.
6191 FEM_ENGINES_PKG.Tech_Message (
6192 p_severity => G_LOG_LEVEL_1
6193 ,p_module => G_BLOCK||'.'||l_api_name
6194 ,p_msg_text => 'Step 4.a: Purging Records in FEM_RU_COST_OBJ_HIER_T'
6195 );
6196
6197 delete from fem_ru_cost_obj_hier_t
6198 where created_by_request_id = p_request_rec.request_id
6199 and created_by_object_id = p_rule_rec.rollup_obj_id;
6200
6201 commit;
6202
6203 elsif ( (l_dimension_rec.dimension_varchar_label = 'ACTIVITY')
6204 and (p_rule_rec.cond_exists) ) then
6205
6206 --------------------------------------------------------------------------
6207 -- STEP 4.b: For an Activity Cost/Stat rollup with a Condition, need to
6208 -- delete all records for this request id in the FEM_RU_ACTIVITIES_HIER_T
6209 -- table.
6210 --------------------------------------------------------------------------
6211 --todo: Until we bring in error reprocessing, must all ways delete temp data.
6212 FEM_ENGINES_PKG.Tech_Message (
6213 p_severity => G_LOG_LEVEL_1
6214 ,p_module => G_BLOCK||'.'||l_api_name
6215 ,p_msg_text => 'Step 4.b: Purging Records in FEM_RU_ACTIVITIES_HIER_T'
6216 );
6217
6218 delete from fem_ru_activities_hier_t
6219 where created_by_request_id = p_request_rec.request_id
6220 and created_by_object_id = p_rule_rec.rollup_obj_id;
6221
6222 commit;
6223
6224 end if;
6225
6226 end if;
6227
6228 ------------------------------------------------------------------------------
6229 -- STEP 5: Update Number of Output Rows.
6230 ------------------------------------------------------------------------------
6231 FEM_ENGINES_PKG.Tech_Message (
6232 p_severity => G_LOG_LEVEL_1
6233 ,p_module => G_BLOCK||'.'||l_api_name
6234 ,p_msg_text => 'Step 5: Update Number of Output Rows'
6235 );
6236
6237 select count(*)
6238 into l_num_of_output_rows
6239 from fem_balances
6240 where dataset_code = p_request_rec.output_dataset_code
6241 and cal_period_id = p_request_rec.output_cal_period_id
6242 and created_by_request_id = p_request_rec.request_id
6243 and created_by_object_id = p_rule_rec.rollup_obj_id
6244 and ledger_id = p_request_rec.ledger_id;
6245
6246 -- Unregister the data location for the FEM_BALANCES output table if no
6247 -- output rows were created.
6248 if (l_num_of_output_rows = 0) then
6249
6250 FEM_DIMENSION_UTIL_PKG.Unregister_Data_Location (
6251 p_request_id => p_request_rec.request_id
6252 ,p_object_id => p_rule_rec.rollup_obj_id
6253 );
6254
6255 end if;
6256
6257 -- Set the number of output rows for the FEM_BALANCES output table.
6258 FEM_PL_PKG.Update_Num_Of_Output_Rows (
6259 p_api_version => 1.0
6260 ,p_commit => FND_API.G_FALSE
6261 ,p_request_id => p_request_rec.request_id
6262 ,p_object_id => p_rule_rec.rollup_obj_id
6263 ,p_table_name => 'FEM_BALANCES'
6264 ,p_statement_type => 'INSERT'
6265 ,p_num_of_output_rows => l_num_of_output_rows
6266 ,p_user_id => p_request_rec.user_id
6267 ,p_last_update_login => p_request_rec.login_id
6268 ,x_msg_count => l_msg_count
6269 ,x_msg_data => l_msg_data
6270 ,x_return_status => l_return_status
6271 );
6272
6273 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
6274 Get_Put_Messages (
6275 p_msg_count => l_msg_count
6276 ,p_msg_data => l_msg_data
6277 );
6278 raise l_rule_post_proc_error;
6279 end if;
6280
6281 ------------------------------------------------------------------------------
6282 -- STEP 6: Update Object Execution Status.
6283 ------------------------------------------------------------------------------
6284 FEM_ENGINES_PKG.Tech_Message (
6285 p_severity => G_LOG_LEVEL_1
6286 ,p_module => G_BLOCK||'.'||l_api_name
6287 ,p_msg_text => 'Step 6: Update Object Execution Status'
6288 );
6289
6290 FEM_PL_PKG.Update_Obj_Exec_Status (
6291 p_api_version => 1.0
6292 ,p_commit => FND_API.G_FALSE
6293 ,p_request_id => p_request_rec.request_id
6294 ,p_object_id => p_rule_rec.rollup_obj_id
6295 ,p_exec_status_code => p_exec_status_code
6296 ,p_user_id => p_request_rec.user_id
6297 ,p_last_update_login => p_request_rec.login_id
6298 ,x_msg_count => l_msg_count
6299 ,x_msg_data => l_msg_data
6300 ,x_return_status => l_return_status
6301 );
6302
6303 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
6304 Get_Put_Messages (
6305 p_msg_count => l_msg_count
6306 ,p_msg_data => l_msg_data
6307 );
6308 raise l_rule_post_proc_error;
6309 end if;
6310
6311 if (p_exec_status_code <> G_EXEC_STATUS_SUCCESS) then
6312
6313 ----------------------------------------------------------------------------
6314 -- STEP 7: Update Object Execution Errors.
6315 ----------------------------------------------------------------------------
6316 FEM_ENGINES_PKG.Tech_Message (
6317 p_severity => G_LOG_LEVEL_1
6318 ,p_module => G_BLOCK||'.'||l_api_name
6319 ,p_msg_text => 'Step 7: Update Object Execution Errors'
6320 );
6321
6322 -- A Rollup Rule is an all or nothing deal, so only 1 error can be reported
6323 FEM_PL_PKG.Update_Obj_Exec_Errors (
6324 p_api_version => 1.0
6325 ,p_commit => FND_API.G_FALSE
6326 ,p_request_id => p_request_rec.request_id
6327 ,p_object_id => p_rule_rec.rollup_obj_id
6328 ,p_errors_reported => 1
6329 ,p_errors_reprocessed => 0
6330 ,p_user_id => p_request_rec.user_id
6331 ,p_last_update_login => p_request_rec.login_id
6332 ,x_msg_count => l_msg_count
6333 ,x_msg_data => l_msg_data
6334 ,x_return_status => l_return_status
6335 );
6336
6337 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
6338 Get_Put_Messages (
6339 p_msg_count => l_msg_count
6340 ,p_msg_data => l_msg_data
6341 );
6342 raise l_rule_post_proc_error;
6343 end if;
6344
6345 end if;
6346
6347 commit;
6348
6349 FEM_ENGINES_PKG.Tech_Message (
6350 p_severity => G_LOG_LEVEL_2
6351 ,p_module => G_BLOCK||'.'||l_api_name
6352 ,p_msg_text => 'END'
6353 );
6354
6355 EXCEPTION
6356
6357 when l_rule_post_proc_error then
6358
6359 FEM_ENGINES_PKG.Tech_Message (
6360 p_severity => G_LOG_LEVEL_6
6361 ,p_module => G_BLOCK||'.'||l_api_name
6362 ,p_msg_text => 'Rule Post Process Exception'
6363 );
6364
6365 FEM_ENGINES_PKG.User_Message (
6366 p_app_name => G_FEM
6367 ,p_msg_name => G_ENG_RULE_POST_PROC_ERR
6368 ,p_token1 => 'OBJECT_ID'
6369 ,p_value1 => p_rule_rec.rollup_obj_id
6370 );
6371
6372 raise g_rollup_request_error;
6373
6374 END Rule_Post_Proc;
6375
6376
6377
6378 /*============================================================================+
6379 | PROCEDURE
6380 | Request_Post_Proc
6381 |
6382 | DESCRIPTION
6383 | Updates the status of the request in the processing locks tables.
6384 |
6385 | SCOPE - PRIVATE
6386 |
6387 +============================================================================*/
6388
6389 PROCEDURE Request_Post_Proc (
6390 p_request_rec in request_record
6391 ,p_exec_status_code in varchar2
6392 )
6393 IS
6394
6395 l_api_name constant varchar2(30) := 'Request_Post_Proc';
6396
6397 l_return_status t_return_status%TYPE;
6398 l_msg_count t_msg_count%TYPE;
6399 l_msg_data t_msg_data%TYPE;
6400
6401 l_request_post_proc_error exception;
6402
6403 BEGIN
6404
6405 FEM_ENGINES_PKG.Tech_Message (
6406 p_severity => G_LOG_LEVEL_2
6407 ,p_module => G_BLOCK||'.'||l_api_name
6408 ,p_msg_text => 'BEGIN'
6409 );
6410
6411 if (p_request_rec.submit_obj_type_code = 'RULE_SET') then
6412
6413 ----------------------------------------------------------------------------
6414 -- STEP 1: Purge RULE_SET_PROCESS_DATA table
6415 ----------------------------------------------------------------------------
6416 FEM_ENGINES_PKG.Tech_Message (
6417 p_severity => G_LOG_LEVEL_1
6418 ,p_module => G_BLOCK||'.'||l_api_name
6419 ,p_msg_text => 'Step 1: Purge RULE_SET_PROCESS_DATA table'
6420 );
6421
6422 FEM_RULE_SET_MANAGER.FEM_DeleteFlatRuleList_PVT (
6423 p_api_version => 1.0
6424 ,p_init_msg_list => FND_API.G_FALSE
6425 ,p_commit => FND_API.G_TRUE
6426 ,p_encoded => FND_API.G_TRUE
6427 ,x_return_status => l_return_status
6428 ,x_msg_count => l_msg_count
6429 ,x_msg_data => l_msg_data
6430 ,p_ruleset_object_id => p_request_rec.ruleset_obj_id
6431 );
6432
6433 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
6434 Get_Put_Messages (
6435 p_msg_count => l_msg_count
6436 ,p_msg_data => l_msg_data
6437 );
6438 raise l_request_post_proc_error;
6439 end if;
6440
6441 end if;
6442
6443 ------------------------------------------------------------------------------
6444 -- STEP 2: Update Request Status.
6445 ------------------------------------------------------------------------------
6446 FEM_ENGINES_PKG.Tech_Message (
6447 p_severity => G_LOG_LEVEL_1
6448 ,p_module => G_BLOCK||'.'||l_api_name
6449 ,p_msg_text => 'Step 2: Update Request Status'
6450 );
6451
6452 FEM_PL_PKG.Update_Request_Status (
6453 p_api_version => 1.0
6454 ,p_commit => FND_API.G_FALSE
6455 ,p_request_id => p_request_rec.request_id
6456 ,p_exec_status_code => p_exec_status_code
6457 ,p_user_id => p_request_rec.user_id
6458 ,p_last_update_login => p_request_rec.login_id
6459 ,x_msg_count => l_msg_count
6460 ,x_msg_data => l_msg_data
6461 ,x_return_status => l_return_status
6462 );
6463
6464 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
6465 Get_Put_Messages (
6466 p_msg_count => l_msg_count
6467 ,p_msg_data => l_msg_data
6468 );
6469 raise l_request_post_proc_error;
6470 end if;
6471
6472 commit;
6473
6474 ------------------------------------------------------------------------------
6475 -- STEP 3: Set the final execution status message in the log file.
6476 ------------------------------------------------------------------------------
6477 FEM_ENGINES_PKG.Tech_Message (
6478 p_severity => G_LOG_LEVEL_1
6479 ,p_module => G_BLOCK||'.'||l_api_name
6480 ,p_msg_text => 'Step 3: Set the final execution message in the Log File'
6481 );
6482
6483 if (p_exec_status_code = G_EXEC_STATUS_SUCCESS) then
6484 FEM_ENGINES_PKG.user_message (
6485 p_app_name => G_FEM
6486 ,p_msg_name => G_EXEC_SUCCESS
6487 );
6488 else
6489 FEM_ENGINES_PKG.user_message (
6490 p_app_name => G_FEM
6491 ,p_msg_name => G_EXEC_RERUN
6492 );
6493 end if;
6494
6495 FEM_ENGINES_PKG.Tech_Message (
6496 p_severity => G_LOG_LEVEL_2
6497 ,p_module => G_BLOCK||'.'||l_api_name
6498 ,p_msg_text => 'END'
6499 );
6500
6501 EXCEPTION
6502
6503 when l_request_post_proc_error then
6504
6505 FEM_ENGINES_PKG.Tech_Message (
6506 p_severity => G_LOG_LEVEL_6
6507 ,p_module => G_BLOCK||'.'||l_api_name
6508 ,p_msg_text => 'Request Post Process Exception'
6509 );
6510
6511 FEM_ENGINES_PKG.User_Message (
6512 p_app_name => G_FEM
6513 ,p_msg_name => G_ENG_REQ_POST_PROC_ERR
6514 );
6515
6516 raise g_rollup_request_error;
6517
6518 END Request_Post_Proc;
6519
6520
6521
6522 /*============================================================================+
6523 | FUNCTION
6524 | Get_Lookup_Meaning
6525 |
6526 | DESCRIPTION
6527 | Utility function to return the meaning for the specified lookup type and
6528 | lookup code.
6529 |
6530 | SCOPE - PRIVATE
6531 |
6532 +============================================================================*/
6533
6534 FUNCTION Get_Lookup_Meaning (
6535 p_lookup_type in varchar2
6536 ,p_lookup_code in varchar2
6537 )
6538 RETURN varchar2
6539 IS
6540
6541 l_api_name constant varchar2(30) := 'Get_Lookup_Meaning';
6542
6543 l_meaning varchar2(80);
6544
6545 BEGIN
6546
6547 select meaning
6548 into l_meaning
6549 from fnd_lookup_values_vl
6550 where lookup_type = p_lookup_type
6551 and lookup_code = p_lookup_code
6552 and view_application_id = 274;
6553
6554 return l_meaning;
6555
6556 EXCEPTION
6557
6558 when others then
6559 return null;
6560
6561 END Get_Lookup_Meaning;
6562
6563
6564
6565 /*============================================================================+
6566 | FUNCTION
6567 | Get_Object_Type_Name
6568 |
6569 | DESCRIPTION
6570 | Utility function to return the name for the specified object type code.
6571 |
6572 | SCOPE - PRIVATE
6573 |
6574 +============================================================================*/
6575
6576 FUNCTION Get_Object_Type_Name (
6577 p_object_type_code in varchar2
6578 )
6579 RETURN varchar2
6580 IS
6581
6582 l_api_name constant varchar2(30) := 'Get_Object_Type_Name';
6583
6584 l_object_type_name varchar2(150);
6585
6586 BEGIN
6587
6588 select object_type_name
6589 into l_object_type_name
6590 from fem_object_types_vl
6591 where object_type_code = p_object_type_code;
6592
6593 return l_object_type_name;
6594
6595 EXCEPTION
6596
6597 when others then
6598 return null;
6599
6600 END Get_Object_Type_Name;
6601
6602
6603
6604 /*============================================================================+
6605 | PROCEDURE
6606 | Get_Put_Messages
6607 |
6608 | DESCRIPTION
6609 | Copied from FEM_DATAX_LOADER_PKG. Will be replaced when Get_Put_Messages
6610 | is placed in the common loader package.
6611 |
6612 | SCOPE - PRIVATE
6613 |
6614 +============================================================================*/
6615
6616 PROCEDURE Get_Put_Messages (
6617 p_msg_count in number
6618 ,p_msg_data in varchar2
6619 )
6620 IS
6621
6622 l_api_name constant varchar2(30) := 'Get_Put_Messages';
6623
6624 l_msg_count t_msg_count%TYPE;
6625 l_msg_data t_msg_data%TYPE;
6626 l_msg_out t_msg_count%TYPE;
6627 l_message t_msg_data%TYPE;
6628
6629 BEGIN
6630
6631 FEM_ENGINES_PKG.Tech_Message (
6632 p_severity => G_LOG_LEVEL_2
6633 ,p_module => G_BLOCK||'.'||l_api_name
6634 ,p_msg_text => 'msg_count='||p_msg_count
6635 );
6636
6637 l_msg_data := p_msg_data;
6638
6639 if (p_msg_count = 1) then
6640
6641 FND_MESSAGE.Set_Encoded(l_msg_data);
6642 l_message := FND_MESSAGE.Get;
6643
6644 FEM_ENGINES_PKG.User_Message (
6645 p_msg_text => l_message
6646 );
6647
6648 FEM_ENGINES_PKG.Tech_Message (
6649 p_severity => G_LOG_LEVEL_2
6650 ,p_module => G_BLOCK||'.'||l_api_name
6651 ,p_msg_text => 'msg_data='||l_message
6652 );
6653
6654 elsif (p_msg_count > 1) then
6655
6656 for i in 1..p_msg_count loop
6657
6658 FND_MSG_PUB.Get (
6659 p_msg_index => i
6660 ,p_encoded => FND_API.G_FALSE
6661 ,p_data => l_message
6662 ,p_msg_index_out => l_msg_out
6663 );
6664
6665 FEM_ENGINES_PKG.User_Message (
6666 p_msg_text => l_message
6667 );
6668
6669 FEM_ENGINES_PKG.Tech_Message (
6670 p_severity => G_LOG_LEVEL_2
6671 ,p_module => G_BLOCK||'.'||l_api_name
6672 ,p_msg_text => 'msg_data='||l_message
6673 );
6674
6675 end loop;
6676
6677 end if;
6678
6679 FND_MSG_PUB.Initialize;
6680
6681 END Get_Put_Messages;
6682
6683
6684
6685
6686 END FEM_RU_ENGINE_PVT;