[Home] [Help]
PACKAGE BODY: APPS.PFT_PROFITAGG_PUB
Source
1 PACKAGE BODY PFT_PROFITAGG_PUB AS
2 /* $Header: pftpaggb.pls 120.6.12000000.3 2007/08/09 16:03:29 gdonthir ship $ */
3
4 --------------------------------------------------------------------------------
5 -- Declare package constants --
6 --------------------------------------------------------------------------------
7
8 g_object_version_number CONSTANT NUMBER := 1;
9 g_pkg_name CONSTANT VARCHAR2(30) := 'PFT_PROFITAGG_PUB';
10
11 -- Constants for p_exec_status_code
12 g_exec_status_error_rerun CONSTANT VARCHAR2(30) := 'ERROR_RERUN';
13 g_exec_status_success CONSTANT VARCHAR2(30) := 'SUCCESS';
14
15 --Constants for output table names being registered with fem_pl_pkg
16 -- API register_table method.
17 g_fem_customer_profit CONSTANT VARCHAR2(30) := 'FEM_CUSTOMER_PROFIT';
18
19 --constant for sql_stmt_type
20 g_insert CONSTANT VARCHAR2(30) := 'INSERT';
21 g_update CONSTANT VARCHAR2(30) := 'UPDATE';
22
23 g_default_fetch_limit CONSTANT NUMBER := 99999;
24
25 g_log_level_1 CONSTANT NUMBER := fnd_log.level_statement;
26 g_log_level_2 CONSTANT NUMBER := fnd_log.level_procedure;
27 g_log_level_3 CONSTANT NUMBER := fnd_log.level_event;
28 g_log_level_4 CONSTANT NUMBER := fnd_log.level_exception;
29 g_log_level_5 CONSTANT NUMBER := fnd_log.level_error;
30 g_log_level_6 CONSTANT NUMBER := fnd_log.level_unexpected;
31
32 --------------------------------------------------------------------------------
33 -- Declare package variables --
34 --------------------------------------------------------------------------------
35 -- Exception variables
36 gv_prg_msg VARCHAR2(2000);
37 gv_callstack VARCHAR2(2000);
38 -- Bulk Fetch Limit
39 z_master_err_state NUMBER;
40 l_hier_object_def_id NUMBER;
41
42 --------------------------------------------------------------------------------
43 -- Declare package exceptions --
44 --------------------------------------------------------------------------------
45 -- General profit Aggregation Engine Exception
46 e_pagg_engine_error EXCEPTION;
47 USER_EXCEPTION EXCEPTION;
48
49 --------------------------------------------------------------------------------
50 -- Declare private procedures and functions --
51 --------------------------------------------------------------------------------
52
53 PROCEDURE Eng_Master_Prep (
54 p_obj_id IN NUMBER
55 ,p_dataset_io_obj_def_id IN NUMBER
56 ,p_effective_date IN VARCHAR2
57 ,p_output_cal_period_id IN NUMBER
58 ,p_ledger_id IN NUMBER
59 ,p_continue_process_on_err_flg IN VARCHAR2
60 ,p_source_system_code IN NUMBER
61 ,x_param_rec OUT NOCOPY param_record
62 );
63
64 PROCEDURE Preprocess_Rule_Set (
65 p_param_rec IN param_record
66 );
67
68 PROCEDURE Process_Single_Rule (
69 p_param_rec IN OUT NOCOPY param_record
70 );
71
72 PROCEDURE Register_Process_Request (
73 p_param_rec IN param_record
74 );
75
76 PROCEDURE Get_Object_Definition (
77 p_object_type_code IN VARCHAR2
78 ,p_object_id IN NUMBER
79 ,p_effective_date IN DATE
80 ,x_obj_def_id OUT NOCOPY NUMBER
81 );
82
83 PROCEDURE Register_Obj_Exe_Step(
84 p_param_rec IN param_record
85 ,p_exe_step IN VARCHAR2
86 ,p_exe_status_code IN VARCHAR2
87 );
88
89 PROCEDURE Register_Table(
90 p_param_rec IN param_record
91 ,p_tbl_name IN VARCHAR2
92 ,p_num_output_rows IN NUMBER
93 ,p_stmt_type IN VARCHAR2
94 );
95
96 PROCEDURE Update_Nbr_Of_Output_Rows(
97 p_param_rec IN param_record
98 ,p_num_output_rows IN NUMBER
99 ,p_tbl_name IN VARCHAR2
100 ,p_stmt_type IN VARCHAR2
101 );
102
103 PROCEDURE Update_Obj_Exec_Step_Status(
104 p_param_rec IN param_record
105 ,p_exe_step IN VARCHAR2
106 ,p_exe_status_code IN VARCHAR2
107 );
108
109 PROCEDURE Process_Obj_Exec_Step(
110 p_param_rec IN param_record
111 ,p_exe_step IN VARCHAR2
112 ,p_exe_status_code IN VARCHAR2
113 ,p_tbl_name IN VARCHAR2
114 ,p_num_rows IN NUMBER
115 );
116
117
118 PROCEDURE Eng_Master_Post_Proc (
119 p_param_rec IN param_record
120 ,p_exec_status_code IN VARCHAR2
121 );
122
123 PROCEDURE Get_Put_Messages (
124 p_msg_count IN NUMBER
125 ,p_msg_data IN VARCHAR2
126 );
127
128 PROCEDURE add_hierarchy_details (
129 p_select_col IN OUT NOCOPY LONG
130 ,p_from_clause IN OUT NOCOPY LONG
131 ,p_where_clause IN OUT NOCOPY LONG
132 ,p_order_by_clause OUT NOCOPY VARCHAR2 );
133
134 FUNCTION is_rule_set_flattened(
135 p_request_id IN NUMBER
136 ,p_rule_set_obj_id IN NUMBER
137 )
138 RETURN NUMBER;
139
140 FUNCTION Create_Aggregation_Stmt (
141 p_rule_obj_id IN NUMBER
142 ,p_table_name IN VARCHAR2
143 ,p_cal_period_id IN NUMBER
144 ,p_dataset_io_obj_def_id IN NUMBER
145 ,p_effective_date IN VARCHAR2
146 ,p_ledger_id IN NUMBER
147 ,p_source_system_code IN NUMBER
148 ,p_condition_obj_id IN NUMBER
149 ,p_col_obj_def_id IN NUMBER
150 ,p_aggregate_customer IN VARCHAR2
151 )
152 RETURN LONG;
153
154 PROCEDURE Update_Nbr_Of_Input_Rows (
155 p_param_rec IN param_record
156 ,p_num_input_rows IN NUMBER);
157
158 PROCEDURE Register_Dependent_Objects (
159 p_param_rec IN param_record );
160
161 PROCEDURE account_aggregation (
162 p_src_alias IN VARCHAR2
163 ,p_select_col IN OUT NOCOPY LONG
164 ,p_from_clause IN OUT NOCOPY LONG
165 ,p_where_clause IN OUT NOCOPY LONG
166 ,p_order_by_clause OUT NOCOPY VARCHAR2
167 );
168 -------------------------------------------------------------------------------
169 -- Package bodies for functions/procedures
170 -------------------------------------------------------------------------------
171 /*=============================================================================+
172 | PROCEDURE
173 | PROCESS REQUEST
174 |
175 | DESCRIPTION
176 | Main engine procedure for profit aggregation step in PFT.
177 |
178 | SCOPE - PUBLIC
179 |
180 +============================================================================*/
181
182 PROCEDURE Process_Request ( Errbuf OUT NOCOPY VARCHAR2,
183 Retcode OUT NOCOPY NUMBER,
184 p_obj_id IN NUMBER,
185 p_effective_date IN VARCHAR2,
186 p_ledger_id IN NUMBER,
187 p_output_cal_period_id IN NUMBER,
188 p_dataset_grp_obj_def_id IN NUMBER,
189 p_continue_process_on_err_flg IN VARCHAR2,
190 p_source_system_code IN NUMBER)
191 IS
192
193 -----------------------
194 -- Declare constants --
195 -----------------------
196 l_api_name CONSTANT VARCHAR2(30) := 'Process_Request';
197
198 -----------------------
199 -- Declare variables --
200 -----------------------
201
202 x_return_status VARCHAR2(1000);
203 x_msg_count NUMBER;
204 x_msg_data VARCHAR2 (1000);
205 l_api_version NUMBER;
206 l_commit VARCHAR2(10);
207 l_init_msg_list VARCHAR2(10);
208 l_param_rec param_record;
209 l_object_type_code VARCHAR2(30);
210 l_next_rule_obj_id NUMBER;
211 l_next_rule_obj_def_id NUMBER;
212 l_next_rule_exec_seq NUMBER;
213 l_next_rule_exec_status VARCHAR2(30);
214 l_err_code NUMBER;
215 l_msg_count NUMBER;
216 l_err_msg VARCHAR2(500);
217 l_msg_data VARCHAR2(500);
218 l_return_status VARCHAR2(500);
219 l_ruleset_status VARCHAR2(500);
220 l_completion_status BOOLEAN;
221 l_rollup_sequence NUMBER;
222
223 ----------------------------
224 -- Declare static cursors --
225 ----------------------------
226 CURSOR l_rule_set_rules(p_request_id IN NUMBER,p_ruleset_obj_id IN NUMBER) IS
227 select rs.child_obj_id
228 ,rs.child_obj_def_id
229 ,x.exec_status_code
230 from fem_ruleset_process_data rs,
231 fem_pl_object_executions x
232 where rs.request_id = p_request_id
233 and rs.rule_set_obj_id = p_ruleset_obj_id
234 and x.request_id(+) = rs.request_id
235 and x.object_id(+) = rs.child_obj_id
236 and x.exec_object_definition_id(+) = rs.child_obj_def_id
237 order by rs.engine_execution_sequence;
238
239 -----------------------------------------------------------
240 -- Declare flags to keep track of which cursors are open --
241 -----------------------------------------------------------
242 l_rule_set_rules_is_open BOOLEAN;
243
244 /*******************************************************************************
245 * *
246 * Profit Aggregation engine *
247 * Execution BLOCK *
248 * *
249 *******************************************************************************/
250
251 BEGIN
252
253 l_api_version := 1.0;
254 l_init_msg_list := FND_API.g_false;
255 l_commit := FND_API.g_false;
256
257 --Initialize Local Parameters
258 l_rule_set_rules_is_open := FALSE;
259 z_master_err_state := FEM_UTILS.G_RSM_NO_ERR;
260 -- initialize status to success
261 x_return_status := FND_API.G_RET_STS_SUCCESS;
262
263 fem_engines_pkg.tech_message( p_severity => g_log_level_2
264 ,p_module => G_BLOCK||l_api_name
265 ,p_msg_text => 'BEGIN');
266
267 -- initialize msg stack?
268 IF FND_API.To_Boolean(NVL(l_init_msg_list,'F')) THEN
269 FND_MSG_PUB.Initialize;
270 END IF;
271
272 --------------------------------------------------------------------------
273 -- Check for the required parameters
274 --------------------------------------------------------------------------
275
276 IF (p_obj_id IS NULL OR p_dataset_grp_obj_def_id IS NULL OR
277 p_effective_date IS NULL OR p_output_cal_period_id IS NULL OR
281 p_app_name => G_FEM
278 p_ledger_id IS NULL) THEN
279
280 FEM_ENGINES_PKG.User_Message (
282 ,p_msg_name => G_ENG_BAD_CONC_REQ_PARAM_ERR);
283
284 RAISE e_pagg_engine_error;
285 END IF;
286
287 --Do the engine master prep
288 --------------------------------------------------------------------------
289 -- STEP 1: Engine Master Preparation
290 --------------------------------------------------------------------------
291
292 fem_engines_pkg.tech_message (
293 p_severity => g_log_level_1
294 ,p_module => G_BLOCK||'.'||l_api_name
295 ,p_msg_text => 'Step 1: Engine Master Preperation');
296
297 Eng_Master_Prep (
298 p_obj_id => p_obj_id
299 ,p_dataset_io_obj_def_id => p_dataset_grp_obj_def_id
300 ,p_effective_date => p_effective_date
301 ,p_output_cal_period_id => p_output_cal_period_id
302 ,p_ledger_id => p_ledger_id
303 ,p_continue_process_on_err_flg => p_continue_process_on_err_flg
304 ,p_source_system_code => p_source_system_code
305 ,x_param_rec => l_param_rec);
306
307 -----------------------------------------------------------------------------
308 -- STEP 2: registering process request for either a single rule or a rule set
309 -----------------------------------------------------------------------------
310 fem_engines_pkg.tech_message (
311 p_severity => g_log_level_3
312 ,p_module => G_BLOCK||'.'||l_api_name
313 ,p_msg_text => 'Step 2: Register Request');
314
315
316 --Register request
317 Register_Process_Request(p_param_rec => l_param_rec);
318
319 IF (l_param_rec.obj_type_code = 'PPROF_PROFIT_AGG') THEN
320 -----------------------------------------------------------------------
321 -- STEP 3: Processing for a single rule submission
322 -----------------------------------------------------------------------
323 fem_engines_pkg.tech_message (
324 p_severity => g_log_level_3
325 ,p_module => G_BLOCK||'.'||l_api_name
326 ,p_msg_text => 'Step 3: Process for a single rule ');
327
328 --Set the current processing object id
329 l_param_rec.crnt_proc_child_obj_id := l_param_rec.obj_id;
330
331
332 Process_Single_Rule( p_param_rec => l_param_rec);
333
334 fem_engines_pkg.tech_message (
335 p_severity => g_log_level_3
336 ,p_module => G_BLOCK||'.'||l_api_name
337 ,p_msg_text => 'Status After Process for a single rule '
338 || l_param_rec.return_status);
339
340 IF (l_param_rec.return_status <> FND_API.G_RET_STS_SUCCESS) THEN
341 -- For Single Aggregation Rule, raise exception to end request
342 -- immediately with a completion status of ERROR,
343 -- regardless of the value for the
344 -- continue_process_on_err_flg parameter.
345
346 RAISE e_pagg_engine_error;
347 END IF;
348
349 ELSIF (l_param_rec.obj_type_code = 'RULE_SET') THEN
350 -----------------------------------------------------------------------
351 -- STEP 4: Processing for a rule set
352 -----------------------------------------------------------------------
353 fem_engines_pkg.tech_message (
354 p_severity => G_LOG_LEVEL_1
355 ,p_module => G_BLOCK||'.'||l_api_name
356 ,p_msg_text => 'Step 4.1: Rule Set Processing');
357 -----------------------------------------------------------------------
358 -- STEP 4.1: Flattening the rule set
359 -----------------------------------------------------------------------
360
361 fem_engines_pkg.tech_message(
362 p_severity => g_log_level_3
363 ,p_module => G_BLOCK||'.'||l_api_name
364 ,p_msg_text => 'Step 4.2 Flatten the rule set');
365
366 IF (is_rule_set_flattened(l_param_rec.request_id
367 ,l_param_rec.crnt_proc_child_obj_id)<> 0) THEN
368 --------------------------------------------------------------------
369 -- STEP 4.2: Preprocess rule set
370 --------------------------------------------------------------------
371 fem_engines_pkg.tech_message(
372 p_severity => g_log_level_1
373 ,p_module => G_BLOCK||'.'||l_api_name
374 ,p_msg_text => 'Step 4.2 PreProcess rule set' );
375
376 PreProcess_Rule_Set(p_param_rec => l_param_rec);
377
378 END IF;
379 -----------------------------------------------------------------------
380 -- STEP 4.3.1: Loop through each rule in the rule set
381 -- STEP 4.3.2: Open cursor for rule set
382 -- STEP 4.3.3: Process each rule in the rule set loop
383 -- STEP 4.3.4: Execution status for rule processing
384 -----------------------------------------------------------------------
385
386 fem_engines_pkg.tech_message(
387 p_severity => g_log_level_1
388 ,p_module => G_BLOCK||'.'||l_api_name
389 ,p_msg_text => 'Step 4.3.1: Loop through all Rule Set Rules');
390
394
391 OPEN l_rule_set_rules(l_param_rec.request_id,l_param_rec.obj_id);
392
393 l_rule_set_rules_is_open := TRUE;
395 fem_engines_pkg.tech_message(
396 p_severity => g_log_level_1
397 ,p_module => G_BLOCK||'.'||l_api_name
398 ,p_msg_text => 'Step 4.3.2:Rule set loop');
399
400 LOOP
401 FETCH l_rule_set_rules INTO l_next_rule_obj_id,
402 l_next_rule_obj_def_id,
403 l_next_rule_exec_status;
404
405
406 fem_engines_pkg.tech_message(
407 p_severity => g_log_level_1
408 ,p_module => G_BLOCK||'.'||l_api_name
409 ,p_msg_text => 'Step 4.3.3: Process next rule in rule set: '||
410 l_next_rule_obj_id);
411
412 fem_engines_pkg.tech_message(
413 p_severity => g_log_level_1
414 ,p_module => G_BLOCK||'.'||l_api_name
415 ,p_msg_text => 'Step 4.3.4: Process next rule in rule set status:'
416 || l_next_rule_exec_status);
417
418 EXIT WHEN l_rule_set_rules%NOTFOUND;
419
420 l_rollup_sequence := l_rollup_sequence + 1;
421
422 --update the param rec for the current
423 --processing object_id and object_definition_id
424 l_param_rec.crnt_proc_child_obj_id := l_next_rule_obj_id;
425 l_param_rec.crnt_proc_child_obj_defn_id := l_next_rule_obj_def_id;
426
427 IF (l_next_rule_exec_status IS NULL OR
428 l_next_rule_exec_status <> 'SUCCESS') THEN
429 -----------------------------------------------------------------
430 -- STEP 4.2.3: Process Rule Set Rule
431 -----------------------------------------------------------------
432
433 fem_engines_pkg.tech_message(
434 p_severity => g_log_level_1
435 ,p_module => G_BLOCK||'.'||l_api_name
436 ,p_msg_text => 'Step 4.3.5: Process Rule Set Rule #'
437 ||TO_CHAR(l_rollup_sequence));
438
439 Process_Single_Rule( p_param_rec => l_param_rec );
440
441 fem_engines_pkg.tech_message (
442 p_severity => g_log_level_3
443 ,p_module => G_BLOCK||'.'||l_api_name
444 ,p_msg_text => 'Status After Process for a single rule '
445 || l_param_rec.return_status);
446
447 IF (l_param_rec.return_status <> FND_API.G_RET_STS_SUCCESS) THEN
448 -- Set the request status to match Aggregation_Rule's
449 -- return status.
450 l_ruleset_status := l_param_rec.return_status;
451 IF (l_param_rec.continue_process_on_err_flg = 'N') THEN
452 -- Raise exception to end request immediately with
453 -- a completion status of ERROR.
454 RAISE e_pagg_engine_error;
455 END IF;
456 END IF;
457 END IF;
458 END LOOP;
459
460 CLOSE l_rule_set_rules;
461
462 IF (l_ruleset_status <> FND_API.G_RET_STS_SUCCESS) THEN
463 -- Raise exception to end request with a completion status of ERROR,
464 -- if the rule set status is not equal to SUCCESS.
465 RAISE e_pagg_engine_error;
466 END IF;
467
468 ELSE
469 NULL;
470 END IF;
471
472 --------------------------------------------------------------------------
473 -- STEP 5: Engine Master Post Processing.
474 --------------------------------------------------------------------------
475 fem_engines_pkg.tech_message (
476 p_severity => g_log_level_3
477 ,p_module => G_BLOCK||'.'||l_api_name
478 ,p_msg_text => 'Step 5: Engine Master Post Processing');
479
480 Eng_Master_Post_Proc ( p_param_rec => l_param_rec
481 ,p_exec_status_code => g_exec_status_success);
482
483 --------------------------------------------------------------------------
484 -- STEP 6: Standard API support
485 --------------------------------------------------------------------------
486 IF FND_API.To_Boolean(NVL(l_commit,'F')) THEN
487 COMMIT WORK;
488 END IF;
489
490 IF (l_rule_set_rules%ISOPEN) THEN
491 CLOSE l_rule_set_rules;
492 END IF;
493
494 x_return_status := FND_API.G_RET_STS_SUCCESS;
495
496 fem_engines_pkg.tech_message ( p_severity => g_log_level_2
497 ,p_module => G_BLOCK||'.'||l_api_name
498 ,p_msg_text => 'END');
499
500 EXCEPTION
501 WHEN e_pagg_engine_error THEN
502 --close the open cursors
503 IF (l_rule_set_rules%ISOPEN) THEN
504 CLOSE l_rule_set_rules;
505 END IF;
506
507 Eng_Master_Post_Proc( p_param_rec => l_param_rec
508 ,p_exec_status_code => 'ERROR_UNDO');
509
510 l_completion_status := FND_CONCURRENT.Set_Completion_Status( 'ERROR'
511 ,NULL);
512
516 ,p_msg_text => 'Profit Aggregation Engine Error');
513 fem_engines_pkg.tech_message (
514 p_severity => g_log_level_5
515 ,p_module => G_BLOCK||'.'||l_api_name
517
518 --FEM_ENGINES_PKG.User_Message (
519 -- p_app_name => G_PFT
520 -- ,p_msg_text => 'Profit Aggregation Engine Error');
521
522 -- Set the return status to ERROR
523 x_return_status := FND_API.G_RET_STS_ERROR;
524
525 WHEN OTHERS THEN
526 gv_prg_msg := SQLERRM;
527 gv_callstack := DBMS_UTILITY.Format_Call_Stack;
528
529 --close the open cursors
530 IF (l_rule_set_rules%ISOPEN) THEN
531 CLOSE l_rule_set_rules;
532 END IF;
533
534 Eng_Master_Post_Proc( p_param_rec => l_param_rec
535 ,p_exec_status_code => g_exec_status_error_rerun);
536
537 l_completion_status := FND_CONCURRENT.Set_Completion_Status( 'ERROR'
538 ,NULL);
539
540 fem_engines_pkg.tech_message (
541 p_severity => g_log_level_6
542 ,p_module => G_BLOCK||'.'||l_api_name||'.Unexpected Exception'
543 ,p_msg_text => gv_prg_msg);
544
545 fem_engines_pkg.tech_message (
546 p_severity => g_log_level_6
547 ,p_module => G_BLOCK||'.'||l_api_name||'.Unexpected Exception'
548 ,p_msg_text => gv_callstack);
549
550 FEM_ENGINES_PKG.User_Message (
551 p_app_name => G_FEM
552 ,p_msg_name => G_UNEXPECTED_ERROR
553 ,p_token1 => 'ERR_MSG'
554 ,p_value1 => gv_prg_msg);
555
556 -- Set the return status to UNEXP_ERROR
557 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
558
559 END Process_Request;
560
561 /*============================================================================+
562 | PROCEDURE
563 | Eng_Master_Prep
564 |
565 | DESCRIPTION
566 | Prepares the Engine Master, Initializes all the variables.
567 |
568 | SCOPE - PRIVATE
569 |
570 +============================================================================*/
571
572 PROCEDURE Eng_Master_Prep (
573 p_obj_id IN NUMBER
574 ,p_dataset_io_obj_def_id IN NUMBER
575 ,p_effective_date IN VARCHAR2
576 ,p_output_cal_period_id IN NUMBER
577 ,p_ledger_id IN NUMBER
578 ,p_continue_process_on_err_flg IN VARCHAR2
579 ,p_source_system_code IN NUMBER
580 ,x_param_rec OUT NOCOPY param_record)
581
582 IS
583 l_api_name CONSTANT VARCHAR2(30) := 'Eng_Master_Prep';
584 l_dummy_varchar VARCHAR2(30);
585 l_dummy_date DATE;
586 l_err_code NUMBER;
587 l_err_msg VARCHAR2(30);
588 l_folder_name VARCHAR2(100);
589
590 e_eng_master_prep_error EXCEPTION;
591
592 BEGIN
593
594 fem_engines_pkg.tech_message ( p_severity => g_log_level_2
595 ,p_module => G_BLOCK||'.'||l_api_name
596 ,p_msg_text => 'BEGIN');
597
598 --------------------------------------------------------------------------
599 -- Set all the Main Parameters
600 --------------------------------------------------------------------------
601 x_param_rec.obj_id := p_obj_id;
602 x_param_rec.dataset_io_obj_def_id := p_dataset_io_obj_def_id;
603 x_param_rec.output_cal_period_id := p_output_cal_period_id;
604 x_param_rec.ledger_id := p_ledger_id;
605 x_param_rec.effective_date_varchar := p_effective_date;
606 x_param_rec.effective_date :=
607 FND_DATE.Canonical_To_Date(p_effective_date);
608 x_param_rec.source_system_code := p_source_system_code;
609 x_param_rec.continue_process_on_err_flg := p_continue_process_on_err_flg;
610
611 --------------------------------------------------------------------------
612 -- Set all the Global Parameters
613 --------------------------------------------------------------------------
614 x_param_rec.user_id := FND_GLOBAL.user_id;
615 x_param_rec.login_id := FND_GLOBAL.login_id;
616 x_param_rec.request_id := FND_GLOBAL.conc_request_id;
617 x_param_rec.resp_id := FND_GLOBAL.resp_id;
618 x_param_rec.pgm_id := FND_GLOBAL.conc_program_id;
619 x_param_rec.pgm_app_id := FND_GLOBAL.prog_appl_id;
620
621 ----------------------------------------------------------------------------
622 -- Get the object info from fem_object_catalog_b for the object_id passed in
623 ----------------------------------------------------------------------------
624 fem_engines_pkg.tech_message (
625 p_severity => g_log_level_3
626 ,p_module => G_BLOCK||'.'||l_api_name
627 ,p_msg_text => 'Getting the Object Type Code of the given Object');
628
629 BEGIN
630 SELECT object_type_code
631 ,local_vs_combo_id
632 INTO x_param_rec.obj_type_code
633 ,x_param_rec.local_vs_combo_id
637 EXCEPTION
634 FROM fem_object_catalog_b
635 WHERE object_id = x_param_rec.obj_id;
636
638 WHEN OTHERS THEN
639 FEM_ENGINES_PKG.User_Message (
640 p_app_name => G_PFT
641 ,p_msg_name => G_ENG_INVALID_OBJ_ERR
642 ,p_token1 => 'OBJECT_ID'
643 ,p_value1 => x_param_rec.obj_id);
644
645 fem_engines_pkg.tech_message (
646 p_severity => g_log_level_3
647 ,p_module => G_BLOCK||'.'||l_api_name
648 ,p_msg_text => 'Invalid Object Id' || x_param_rec.obj_id);
649
650 RAISE e_eng_master_prep_error;
651 END;
652
653 --------------------------------------------------------------------------
654 -- If this is a Rule Set Submission, check that the object_type_code and
655 -- local_vs_combo_id of the rollup rule matches the Rule Set's.
656 --------------------------------------------------------------------------
657 IF (x_param_rec.obj_type_code = 'RULE_SET') THEN
658
659 fem_engines_pkg.tech_message (
660 p_severity => g_log_level_3
661 ,p_module => G_BLOCK||'.'||l_api_name
662 ,p_msg_text => 'Obj type code is a rule set');
663
664 BEGIN
665 Get_Object_Definition(
666 p_object_type_code => x_param_rec.obj_type_code
667 ,p_object_id => x_param_rec.obj_id
668 ,p_effective_date => x_param_rec.effective_date
669 ,x_obj_def_id => x_param_rec.crnt_proc_child_obj_defn_id);
670
671 EXCEPTION
672 WHEN OTHERS THEN
673 FEM_ENGINES_PKG.User_Message (
674 p_app_name => G_PFT
675 ,p_msg_name => G_ENG_INV_OBJ_DEFN_RS_ERR
676 ,p_token1 => 'OBJECT_ID'
677 ,p_value1 => x_param_rec.obj_id
678 ,p_token2 => 'EFFECTIVE_DATE'
679 ,p_value2 => x_param_rec.effective_date);
680
681 fem_engines_pkg.tech_message (
682 p_severity => g_log_level_3
683 ,p_module => G_BLOCK||'.'||l_api_name
684 ,p_msg_text => 'No Definition found for the ruleset :'
685 || x_param_rec.obj_id || 'for the Date :'
686 || x_param_rec.effective_date);
687
688 RAISE e_eng_master_prep_error;
689 END;
690
691 ELSIF (x_param_rec.obj_type_code = 'PPROF_PROFIT_AGG') THEN
692 fem_engines_pkg.tech_message (
693 p_severity => g_log_level_3
694 ,p_module => G_BLOCK||'.'||l_api_name
695 ,p_msg_text => 'Obj type code is a single rule');
696
697 BEGIN
698 Get_Object_Definition(
699 p_object_type_code => x_param_rec.obj_type_code
700 ,p_object_id => x_param_rec.obj_id
701 ,p_effective_date => x_param_rec.effective_date
702 ,x_obj_def_id => x_param_rec.crnt_proc_child_obj_defn_id);
703
704 EXCEPTION
705 WHEN OTHERS THEN
706 FEM_ENGINES_PKG.User_Message (
707 p_app_name => G_PFT
708 ,p_msg_name => G_ENG_INVALID_OBJ_DEFN_ERR
709 ,p_token1 => 'OBJECT_ID'
710 ,p_value1 => x_param_rec.obj_id
711 ,p_token2 => 'EFFECTIVE_DATE'
712 ,p_value2 => x_param_rec.effective_date);
713
714 fem_engines_pkg.tech_message (
715 p_severity => g_log_level_3
716 ,p_module => G_BLOCK||'.'||l_api_name
717 ,p_msg_text => 'No Definition found for the Rule :'
718 || x_param_rec.obj_id || 'for the Date :'
719 || x_param_rec.effective_date);
720 RAISE e_eng_master_prep_error;
721 END;
722
723 ELSE
724 FEM_ENGINES_PKG.User_Message(
725 p_app_name => G_PFT
726 ,p_msg_name => G_ENG_INVALIDRULETYPE_ERR
727 ,p_token1 => 'OBJECT_TYPE_CODE'
728 ,p_value1 => x_param_rec.obj_type_code);
729
730 RAISE e_eng_master_prep_error;
731
732 END IF;
733
734 --------------------------------------------------------------------------
735 -- Get the Dataset Group Object ID
736 --------------------------------------------------------------------------
737 BEGIN
738 SELECT object_id
739 INTO x_param_rec.dataset_grp_obj_id
740 FROM fem_object_definition_b
741 WHERE object_definition_id = x_param_rec.dataset_io_obj_def_id;
742 EXCEPTION
743 WHEN OTHERS THEN
744 FEM_ENGINES_PKG.User_Message (
745 p_app_name => G_PFT
746 ,p_msg_name => G_ENG_INVALID_OBJ_ERR
747 ,p_token1 => 'OBJECT_ID'
748 ,p_value1 => x_param_rec.dataset_io_obj_def_id);
749
750 fem_engines_pkg.tech_message (
751 p_severity => g_log_level_3
752 ,p_module => G_BLOCK||'.'||l_api_name
753 ,p_msg_text => 'No Object found for the given Dataset Group:'
754 || x_param_rec.dataset_io_obj_def_id);
758 --------------------------------------------------------------------------
755 RAISE e_eng_master_prep_error;
756 END;
757
759 -- Get the Output Dataset Code
760 --------------------------------------------------------------------------
761 fem_engines_pkg.tech_message (
762 p_severity => g_log_level_3
763 ,p_module => G_BLOCK||'.'||l_api_name
764 ,p_msg_text => 'Getting the output DS Code for the given DS Group');
765
766 BEGIN
767 SELECT output_dataset_code
768 INTO x_param_rec.output_dataset_code
769 FROM fem_ds_input_output_defs
770 WHERE dataset_io_obj_def_id = x_param_rec.dataset_io_obj_def_id;
771 EXCEPTION
772 WHEN OTHERS THEN
773 FEM_ENGINES_PKG.User_Message (
774 p_app_name => G_PFT
775 ,p_msg_name => G_ENG_NO_OUTPUT_DS_ERR
776 ,p_token1 => 'DATASET_GROUP_OBJ_DEF_ID'
777 ,p_value1 => x_param_rec.dataset_io_obj_def_id);
778
779 fem_engines_pkg.tech_message (
780 p_severity => g_log_level_3
781 ,p_module => G_BLOCK||'.'||l_api_name
782 ,p_msg_text => 'No Output Dataset for the DS Group Definition:'
783 || x_param_rec.dataset_io_obj_def_id);
784 RAISE e_eng_master_prep_error;
785 END;
786
787 --------------------------------------------------------------------------
788 -- Get the Source System Code for PFT if a null param value was passed.
789 --------------------------------------------------------------------------
790 IF (x_param_rec.source_system_code IS NULL) THEN
791
792 -- For all Processing default the Source System Display Code to PFT
793 BEGIN
794 SELECT source_system_code
795 INTO x_param_rec.source_system_code
796 FROM fem_source_systems_b
797 WHERE source_system_display_code = G_PFT;
798 EXCEPTION
799 WHEN OTHERS THEN
800 FEM_ENGINES_PKG.User_Message (
801 p_app_name => G_PFT
802 ,p_msg_name => G_ENG_INVALID_OBJ_ERR
803 ,p_token1 => 'OBJECT_ID'
804 ,p_value1 => x_param_rec.obj_id);
805
806 RAISE e_eng_master_prep_error;
807 END;
808
809 END IF;
810
811 -- Log all Request Record Parameters if we have low level debugging
812 IF ( FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) ) THEN
813
814 fem_engines_pkg.tech_message (
815 p_severity => G_LOG_LEVEL_1
816 ,p_module => G_BLOCK||'.'||l_api_name||'.x_param_rec'
817 ,p_msg_text =>
818 ' dataset_grp_obj_def_id='||x_param_rec.dataset_io_obj_def_id||
819 ' dataset_grp_obj_id='||x_param_rec.dataset_grp_obj_id||
820 ' effective_date='||
821 FND_DATE.date_to_chardate(x_param_rec.effective_date)||
822 ' ledger_id='||x_param_rec.ledger_id||
823 ' local_vs_combo_id='||x_param_rec.local_vs_combo_id||
824 ' login_id='||x_param_rec.login_id||
825 ' output_cal_period_id='||x_param_rec.output_cal_period_id||
826 ' output_dataset_code='||x_param_rec.output_dataset_code||
827 ' pgm_app_id='||x_param_rec.pgm_app_id||
828 ' pgm_id='||x_param_rec.pgm_id||
829 ' resp_id='||x_param_rec.resp_id||
830 ' req uest_id='||x_param_rec.request_id||
831 ' obj_type_code='||x_param_rec.obj_type_code||
832 ' ruleset_obj_def_id='||x_param_rec.crnt_proc_child_obj_defn_id||
833 ' ruleset_obj_id='||x_param_rec.obj_id||
834 ' source_system_code='||x_param_rec.source_system_code||
835 ' submit_obj_id='||x_param_rec.obj_id||
836 ' submit_obj_type_code='||x_param_rec.obj_type_code||
837 ' user_id='||x_param_rec.user_id
838 );
839
840 END IF;
841
842 fem_engines_pkg.tech_message ( p_severity => G_LOG_LEVEL_2
843 ,p_module => G_BLOCK||'.'||l_api_name
844 ,p_msg_text => 'END');
845
846 EXCEPTION
847 WHEN e_eng_master_prep_error THEN
848 fem_engines_pkg.tech_message (
849 p_severity => g_log_level_5
850 ,p_module => G_BLOCK||'.'||l_api_name
851 ,p_msg_text => 'Engine Master Preperation Exception');
852 RAISE e_pagg_engine_error;
853
854 END Eng_Master_Prep;
855
856 /*============================================================================+
857 | PROCEDURE
858 | Register_Process_Request
859 |
860 | DESCRIPTION
861 | Registers the request for the object in the processing locks tables.
862 |
863 | SCOPE - PRIVATE
864 |
865 +============================================================================*/
866
867 PROCEDURE Register_Process_Request ( p_param_rec IN param_record )
868
869 IS
870
871 l_api_name CONSTANT VARCHAR2(30) := 'Register_Process_Request';
872
873 l_exec_state VARCHAR2(30); -- normal, restart, rerun
874 l_return_status VARCHAR2(1);
875 l_msg_count NUMBER;
876 l_msg_data VARCHAR2(240);
877
881
878 e_pl_register_request_error EXCEPTION;
879
880 BEGIN
882 fem_engines_pkg.tech_message ( p_severity => g_log_level_2
883 ,p_module => G_BLOCK||'.'||l_api_name
884 ,p_msg_text => 'BEGIN');
885
886 SAVEPOINT register_request_pub;
887
888 -- Call the FEM_PL_PKG.Register_Request API procedure to register
889 -- the concurrent request in FEM_PL_REQUESTS.
890 FEM_PL_PKG.Register_Request(
891 p_api_version => G_CALLING_API_VERSION
892 ,p_commit => FND_API.G_FALSE
893 ,p_cal_period_id => p_param_rec.output_cal_period_id
894 ,p_ledger_id => p_param_rec.ledger_id
895 ,p_dataset_io_obj_def_id => p_param_rec.dataset_io_obj_def_id
896 ,p_source_system_code => p_param_rec.source_system_code
897 ,p_effective_date => p_param_rec.effective_date
898 ,p_output_dataset_code => p_param_rec.output_dataset_code
899 ,p_rule_set_obj_def_id => p_param_rec.crnt_proc_child_obj_defn_id
900 ,p_request_id => p_param_rec.request_id
901 ,p_user_id => p_param_rec.user_id
902 ,p_last_update_login => p_param_rec.login_id
903 ,p_program_id => p_param_rec.pgm_id
904 ,p_program_login_id => p_param_rec.login_id
905 ,p_program_application_id => p_param_rec.pgm_app_id
906 ,p_exec_mode_code => NULL
907 ,p_dimension_id => NULL
908 ,p_table_name => NULL
909 ,p_hierarchy_name => NULL
910 ,x_msg_count => l_msg_count
911 ,x_msg_data => l_msg_data
912 ,x_return_status => l_return_status);
913
914 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
915 Get_Put_Messages ( p_msg_count => l_msg_count
916 ,p_msg_data => l_msg_data);
917
918 RAISE e_pl_register_request_error;
919 END IF;
920
921 COMMIT;
922
923 fem_engines_pkg.tech_message (
924 p_severity => g_log_level_1
925 ,p_module => G_BLOCK||'.'||l_api_name
926 ,p_msg_text => 'Request id is '||p_param_rec.request_id);
927
928 fem_engines_pkg.tech_message ( p_severity => g_log_level_2
929 ,p_module => G_BLOCK||'.'||l_api_name
930 ,p_msg_text => 'END');
931
932 EXCEPTION
933 WHEN e_pl_register_request_error THEN
934
935 ROLLBACK TO register_request_pub;
936
937 fem_engines_pkg.tech_message (
938 p_severity => g_log_level_5
939 ,p_module => G_BLOCK||'.'||l_api_name
940 ,p_msg_text => 'Register Request Exception');
941
942 FEM_ENGINES_PKG.User_Message (
943 p_app_name => G_PFT
944 ,p_msg_name => G_PL_REG_REQUEST_ERR
945 ,p_token1 => 'REQUEST_ID'
946 ,p_value1 => p_param_rec.request_id);
947
948 RAISE e_pagg_engine_error;
949
950 WHEN OTHERS THEN
951
952 ROLLBACK TO register_request_pub;
953
954 FEM_ENGINES_PKG.User_Message (
955 p_app_name => G_PFT
956 ,p_msg_name => G_PL_REG_REQUEST_ERR
957 ,p_token1 => 'REQUEST_ID'
958 ,p_value1 => p_param_rec.request_id);
959
960 RAISE e_pagg_engine_error;
961
962 END Register_Process_Request;
963
964 /*============================================================================+
965 | PROCEDURE
966 | Register_Object_Definition
967 |
968 | DESCRIPTION
969 | Registers the specified object definition in FEM_PL_OBJECT_DEFS,
970 | thus obtaining an object definition lock.
971 |
972 | SCOPE - PRIVATE
973 |
974 +============================================================================*/
975
976 PROCEDURE Register_Object_Definition ( p_param_rec IN param_record
977 ,p_object_id IN NUMBER
978 ,p_obj_def_id IN NUMBER)
979 IS
980
981 l_api_name CONSTANT VARCHAR2(30) := 'Register_Object_Definition';
982
983 l_return_status VARCHAR2(1);
984 l_msg_count NUMBER;
985 l_msg_data VARCHAR2(240);
986
987 e_register_obj_def_error EXCEPTION;
988
989 BEGIN
990
991 fem_engines_pkg.tech_message ( p_severity => g_log_level_2
992 ,p_module => G_BLOCK||'.'||l_api_name
993 ,p_msg_text => 'BEGIN');
994
995 -- Call the FEM_PL_PKG.Register_Object_Def API procedure to register
996 -- the specified object definition in FEM_PL_OBJECT_DEFS, thus obtaining
997 -- an object definition lock.
998 FEM_PL_PKG.Register_Object_Def (
999 p_api_version => 1.0
1000 ,p_commit => FND_API.G_FALSE
1001 ,p_request_id => p_param_rec.request_id
1002 ,p_object_id => p_object_id
1006 ,x_msg_count => l_msg_count
1003 ,p_object_definition_id => p_obj_def_id
1004 ,p_user_id => p_param_rec.user_id
1005 ,p_last_update_login => p_param_rec.login_id
1007 ,x_msg_data => l_msg_data
1008 ,x_return_status => l_return_status);
1009
1010 -- Object Definition Lock exists
1011 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1012
1013 Get_Put_Messages ( p_msg_count => l_msg_count
1014 ,p_msg_data => l_msg_data);
1015 RAISE e_register_obj_def_error;
1016 END IF;
1017
1018 fem_engines_pkg.tech_message ( p_severity => g_log_level_2
1019 ,p_module => G_BLOCK||'.'||l_api_name
1020 ,p_msg_text => 'END');
1021
1022 EXCEPTION
1023 WHEN e_register_obj_def_error THEN
1024 fem_engines_pkg.tech_message (
1025 p_severity => g_log_level_5
1026 ,p_module => G_BLOCK||'.'||l_api_name
1027 ,p_msg_text => 'Register Object Definition Exception');
1028
1029 FEM_ENGINES_PKG.User_Message (
1030 p_app_name => G_PFT
1031 ,p_msg_name => G_PL_OBJ_EXECLOCK_EXISTS_ERR
1032 ,p_token1 => 'REQUEST_ID'
1033 ,p_value1 => p_param_rec.request_id);
1034 RAISE e_pagg_engine_error;
1035
1036 END Register_Object_Definition;
1037
1038 /*============================================================================+
1039 | PROCEDURE
1040 | Register_Obj_Exe_Step
1041 |
1042 | DESCRIPTION
1043 | Registers the current step of execution in fem_pl_obj_steps
1044 |
1045 | SCOPE - PRIVATE
1046 |
1047 +============================================================================*/
1048 PROCEDURE Register_Obj_Exe_Step(p_param_rec IN param_record
1049 ,p_exe_step IN VARCHAR2
1050 ,p_exe_status_code IN VARCHAR2)
1051 IS
1052
1053 l_api_name CONSTANT VARCHAR2(30) := 'Register_Obj_Exe_Step';
1054
1055 l_return_status VARCHAR2(1);
1056 l_msg_count NUMBER;
1057 l_msg_data VARCHAR2(240);
1058
1059 e_register_obj_exe_step_error EXCEPTION;
1060
1061 BEGIN
1062
1063 fem_engines_pkg.tech_message ( p_severity => g_log_level_2
1064 ,p_module => G_BLOCK||'.'||l_api_name
1065 ,p_msg_text => 'BEGIN');
1066
1067 --Call the FEM_PL_PKG.Register_Object_Def API procedure to register step
1068 -- in fem_pl_obj_steps.
1069 FEM_PL_PKG.Register_Obj_Exec_Step (
1070 p_api_version => 1.0
1071 ,p_commit => FND_API.G_FALSE
1072 ,p_request_id => p_param_rec.request_id
1073 ,p_object_id => p_param_rec.crnt_proc_child_obj_id
1074 ,p_exec_step => p_exe_step
1075 ,p_exec_status_code => p_exe_status_code
1076 ,p_user_id => p_param_rec.user_id
1077 ,p_last_update_login => p_param_rec.login_id
1078 ,x_msg_count => l_msg_count
1079 ,x_msg_data => l_msg_data
1080 ,x_return_status => l_return_status);
1081
1082 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1083
1084 Get_Put_Messages ( p_msg_count => l_msg_count
1085 ,p_msg_data => l_msg_data);
1086 RAISE e_register_obj_exe_step_error;
1087 END IF;
1088
1089 fem_engines_pkg.tech_message ( p_severity => g_log_level_2
1090 ,p_module => G_BLOCK||'.'||l_api_name
1091 ,p_msg_text => 'END');
1092
1093 EXCEPTION
1094 WHEN e_register_obj_exe_step_error THEN
1095 fem_engines_pkg.tech_message (
1096 p_severity => g_log_level_5
1097 ,p_module => G_BLOCK||'.'||l_api_name
1098 ,p_msg_text => 'Register Obj Exec Step Exception');
1099
1100 FEM_ENGINES_PKG.User_Message (
1101 p_app_name => G_PFT
1102 ,p_msg_name => G_PL_REG_EXEC_STEP_ERR
1103 ,p_token1 => 'OBJECT_ID'
1104 ,p_value1 => p_param_rec.crnt_proc_child_obj_id);
1105
1106 RAISE e_pagg_engine_error;
1107
1108 WHEN OTHERS THEN
1109 FEM_ENGINES_PKG.User_Message (
1110 p_app_name => G_PFT
1111 ,p_msg_name => G_PL_REG_EXEC_STEP_ERR
1112 ,p_token1 => 'OBJECT_ID'
1113 ,p_value1 => p_param_rec.crnt_proc_child_obj_id);
1114
1115 RAISE e_pagg_engine_error;
1116
1117 END Register_Obj_Exe_Step;
1118
1119 /*============================================================================+
1120 | PROCEDURE
1121 | Register_Table
1122 |
1123 | DESCRIPTION
1124 | Registers the output Table in fem_pl_tables.
1125 |
1126 | SCOPE - PRIVATE
1127 |
1128 +============================================================================*/
1129
1130 PROCEDURE Register_Table( p_param_rec IN param_record
1131 ,p_tbl_name IN VARCHAR2
1132 ,p_num_output_rows IN NUMBER
1136 l_api_name CONSTANT VARCHAR2(30) := 'Register_Table';
1133 ,p_stmt_type IN VARCHAR2)
1134 IS
1135
1137
1138 l_return_status VARCHAR2(1);
1139 l_msg_count NUMBER;
1140 l_msg_data VARCHAR2(240);
1141
1142 e_register_table_error EXCEPTION;
1143
1144 BEGIN
1145
1146 fem_engines_pkg.tech_message ( p_severity => g_log_level_2
1147 ,p_module => G_BLOCK||'.'||l_api_name
1148 ,p_msg_text => 'BEGIN');
1149
1150 -- Call the FEM_PL_PKG.Register_Table API procedure to register
1151 -- the specified output table and the statement type that will be used.
1152 FEM_PL_PKG.Register_Table(
1153 p_api_version => 1.0
1154 ,p_commit => FND_API.G_FALSE
1155 ,p_request_id => p_param_rec.request_id
1156 ,p_object_id => p_param_rec.crnt_proc_child_obj_id
1157 ,p_table_name => p_tbl_name
1158 ,p_statement_type => p_stmt_type
1159 ,p_num_of_output_rows => p_num_output_rows
1160 ,p_user_id => p_param_rec.user_id
1161 ,p_last_update_login => p_param_rec.login_id
1162 ,x_msg_count => l_msg_count
1163 ,x_msg_data => l_msg_data
1164 ,x_return_status => l_return_status);
1165
1166 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1167
1168 Get_Put_Messages ( p_msg_count => l_msg_count
1169 ,p_msg_data => l_msg_data);
1170 RAISE e_register_table_error;
1171 END IF;
1172
1173 fem_engines_pkg.tech_message( p_severity => g_log_level_2
1174 ,p_module => G_BLOCK||'.'||l_api_name
1175 ,p_msg_text => 'END');
1176
1177 EXCEPTION
1178 WHEN e_register_table_error THEN
1179 fem_engines_pkg.tech_message(
1180 p_severity => g_log_level_5
1181 ,p_module => G_BLOCK||'.'||l_api_name
1182 ,p_msg_text => 'Register Table Exception');
1183
1184 FEM_ENGINES_PKG.User_Message (
1185 p_app_name => G_PFT
1186 ,p_msg_name => G_PL_REG_TABLE_ERR
1187 ,p_token1 => 'TABLE_NAME'
1188 ,p_value1 => p_tbl_name);
1189
1190 RAISE e_pagg_engine_error;
1191
1192 WHEN OTHERS THEN
1193 FEM_ENGINES_PKG.User_Message (
1194 p_app_name => G_PFT
1195 ,p_msg_name => G_PL_REG_TABLE_ERR
1196 ,p_token1 => 'TABLE_NAME'
1197 ,p_value1 => p_tbl_name);
1198 RAISE e_pagg_engine_error;
1199
1200 END Register_Table;
1201
1202 /*============================================================================+
1203 | PROCEDURE
1204 | Update_Num_Of_Output_Rows
1205 |
1206 | DESCRIPTION
1207 | Updates the rows successfully processed by calling
1208 | fem_pl_pkg.Update_Num_Of_Output_Rows in fem_pl_tables.
1209 |
1210 | SCOPE - PRIVATE
1211 |
1212 +============================================================================*/
1213 PROCEDURE Update_Nbr_Of_Output_Rows( p_param_rec IN param_record
1214 ,p_num_output_rows IN NUMBER
1215 ,p_tbl_name IN VARCHAR2
1216 ,p_stmt_type IN VARCHAR2)
1217 IS
1218
1219 l_api_name CONSTANT VARCHAR2(30) := 'Update_Num_Of_Output_Rows';
1220
1221 l_return_status VARCHAR2(2);
1222 l_msg_count NUMBER;
1223 l_msg_data VARCHAR2(240);
1224
1225 e_upd_num_output_rows_error EXCEPTION;
1226
1227 BEGIN
1228
1229 fem_engines_pkg.tech_message ( p_severity => g_log_level_2
1230 ,p_module => G_BLOCK||'.'||l_api_name
1231 ,p_msg_text => 'BEGIN');
1232
1233 -- Set the number of output rows for the output table.
1234 FEM_PL_PKG.Update_Num_Of_Output_Rows(
1235 p_api_version => 1.0
1236 ,p_commit => FND_API.G_FALSE
1237 ,p_request_id => p_param_rec.request_id
1238 ,p_object_id => p_param_rec.crnt_proc_child_obj_id
1239 ,p_table_name => p_tbl_name
1240 ,p_statement_type => p_stmt_type
1241 ,p_num_of_output_rows => p_num_output_rows
1242 ,p_user_id => p_param_rec.user_id
1243 ,p_last_update_login => p_param_rec.login_id
1244 ,x_msg_count => l_msg_count
1245 ,x_msg_data => l_msg_data
1246 ,x_return_status => l_return_status);
1247
1248 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1249
1250 Get_Put_Messages( p_msg_count => l_msg_count
1251 ,p_msg_data => l_msg_data);
1252 RAISE e_upd_num_output_rows_error;
1253 END IF;
1254
1255 fem_engines_pkg.tech_message( p_severity => g_log_level_2
1256 ,p_module => G_BLOCK||'.'||l_api_name
1257 ,p_msg_text => 'END');
1258
1259 EXCEPTION
1260 WHEN e_upd_num_output_rows_error THEN
1261 fem_engines_pkg.tech_message (
1262 p_severity => g_log_level_5
1263 ,p_module => G_BLOCK||'.'||l_api_name
1264 ,p_msg_text => 'Update Rows Exception');
1265
1266 FEM_ENGINES_PKG.User_Message (
1267 p_app_name => G_PFT
1268 ,p_msg_name => G_PL_OP_UPD_ROWS_ERR);
1269
1270 RAISE e_pagg_engine_error;
1271
1272 WHEN OTHERS THEN
1273 FEM_ENGINES_PKG.User_Message (
1274 p_app_name => G_PFT
1275 ,p_msg_name => G_PL_OP_UPD_ROWS_ERR);
1276
1277 RAISE e_pagg_engine_error;
1278
1279 END Update_Nbr_Of_Output_Rows;
1280
1281 /*============================================================================+
1282 | PROCEDURE
1283 | Update_Obj_Exec_Step_Status
1284 |
1285 | DESCRIPTION
1286 | Updates the status of the executuon of the object by calling
1287 | fem_pl_pkg.Update_obj_exec_step_status in fem_pl_obj_steps.
1288 |
1289 | SCOPE - PRIVATE
1290 |
1291 +============================================================================*/
1292 PROCEDURE Update_Obj_Exec_Step_Status( p_param_rec IN param_record
1293 ,p_exe_step IN VARCHAR2
1294 ,p_exe_status_code IN VARCHAR2)
1295 IS
1296
1297 l_api_name CONSTANT VARCHAR2(30) := 'Update_Obj_Exe_Step_Status';
1298
1299 l_return_status VARCHAR2(1);
1300 l_msg_count NUMBER;
1301 l_msg_data VARCHAR2(240);
1302
1303 e_upd_obj_exec_step_stat_error EXCEPTION;
1304
1305 BEGIN
1306
1307 fem_engines_pkg.tech_message ( p_severity => g_log_level_2
1308 ,p_module => G_BLOCK||'.'||l_api_name
1309 ,p_msg_text => 'BEGIN');
1310
1311 --Call the FEM_PL_PKG.Update_obj_exec_step_status API procedure
1312 --to update step staus in fem_pl_obj_steps.
1313 FEM_PL_PKG.Update_Obj_Exec_Step_Status(
1314 p_api_version => 1.0
1315 ,p_commit => FND_API.G_FALSE
1316 ,p_request_id => p_param_rec.request_id
1317 ,p_object_id => p_param_rec.crnt_proc_child_obj_id
1318 ,p_exec_step => p_exe_step
1319 ,p_exec_status_code => p_exe_status_code
1320 ,p_user_id => p_param_rec.user_id
1321 ,p_last_update_login => p_param_rec.login_id
1322 ,x_msg_count => l_msg_count
1323 ,x_msg_data => l_msg_data
1324 ,x_return_status => l_return_status);
1325
1326 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1327 Get_Put_Messages ( p_msg_count => l_msg_count
1328 ,p_msg_data => l_msg_data);
1329 RAISE e_upd_obj_exec_step_stat_error;
1330
1331 END IF;
1332
1333 fem_engines_pkg.tech_message ( p_severity => g_log_level_2
1334 ,p_module => G_BLOCK||'.'||l_api_name
1335 ,p_msg_text => 'END');
1336
1337 EXCEPTION
1338 WHEN e_upd_obj_exec_step_stat_error THEN
1339 fem_engines_pkg.tech_message (
1340 p_severity => g_log_level_5
1344 FEM_ENGINES_PKG.User_Message (
1341 ,p_module => G_BLOCK||'.'||l_api_name
1342 ,p_msg_text => 'Update Obj Exec Step API Exception');
1343
1345 p_app_name => G_PFT
1346 ,p_msg_name => G_PL_UPD_EXEC_STEP_ERR
1347 ,p_token1 => 'OBJECT_ID'
1348 ,p_value1 => p_param_rec.crnt_proc_child_obj_id);
1349
1350 RAISE e_pagg_engine_error;
1351
1352 WHEN OTHERS THEN
1353 FEM_ENGINES_PKG.User_Message (
1354 p_app_name => G_PFT
1355 ,p_msg_name => G_PL_UPD_EXEC_STEP_ERR
1356 ,p_token1 => 'OBJECT_ID'
1357 ,p_value1 => p_param_rec.crnt_proc_child_obj_id);
1358
1359 RAISE e_pagg_engine_error;
1360
1361 END Update_Obj_Exec_Step_Status;
1362
1363 /*============================================================================+
1364 | PROCEDURE
1365 | Process_Obj_Exec_Step
1366 | DESCRIPTION
1367 | Processes the execution of the Object.
1368 |
1369 | SCOPE - PRIVATE
1370 |
1371 +============================================================================*/
1372 PROCEDURE Process_Obj_Exec_Step( p_param_rec IN param_record
1373 ,p_exe_step IN VARCHAR2
1374 ,p_exe_status_code IN VARCHAR2
1375 ,p_tbl_name IN VARCHAR2
1376 ,p_num_rows IN NUMBER)
1377 IS
1378 l_api_name VARCHAR2(30);
1379 BEGIN
1380 l_api_name := 'Process_Obj_Exec_Step';
1381
1382 fem_engines_pkg.tech_message( p_severity => g_log_level_2
1383 ,p_module => G_BLOCK||'.'||l_api_name
1384 ,p_msg_text => 'BEGIN');
1385
1386 IF (p_exe_status_code = g_exec_status_success) THEN
1387 -- query table fem_mp_process_ctl_t to get the number of rows processed
1388
1389 fem_engines_pkg.tech_message(
1390 p_severity => g_log_level_3
1391 ,p_module => G_BLOCK||'.'||l_api_name
1392 ,p_msg_text => 'Rows processed for registered output table :'
1393 ||p_tbl_name);
1394
1395 -- update the number of rows processed in the registered table
1396 Update_Nbr_Of_Output_Rows(p_param_rec => p_param_rec
1397 ,p_num_output_rows => p_num_rows
1398 ,p_tbl_name => p_tbl_name
1399 ,p_stmt_type => g_insert );
1400
1401 -----------------------------------------------------------------------
1402 -- Call FEM_PL_PKG.update_num_of_input_rows();
1403 -----------------------------------------------------------------------
1404
1405 fem_engines_pkg.tech_message(
1406 p_severity => g_log_level_3,
1407 p_module => G_BLOCK||'.'||l_api_name,
1408 p_msg_text => 'Updating Rows processed from input table');
1409
1410 -- update the number of rows processed in the registered table
1411 Update_Nbr_Of_Input_Rows(p_param_rec => p_param_rec
1412 ,p_num_input_rows => p_num_rows);
1413
1414 END IF;
1415
1416 fem_engines_pkg.tech_message(
1417 p_severity => g_log_level_3
1418 ,p_module => G_BLOCK||'.'||l_api_name
1419 ,p_msg_text => 'Update the status of the step with execution status :'
1420 ||p_exe_status_code);
1421
1422 --update the status of the step
1423 Update_Obj_Exec_Step_Status( p_param_rec => p_param_rec
1424 ,p_exe_step => 'ALL'
1425 ,p_exe_status_code => p_exe_status_code );
1426
1427 fem_engines_pkg.tech_message( p_severity => g_log_level_2
1428 ,p_module => G_BLOCK||'.'||l_api_name
1429 ,p_msg_text => 'END');
1430
1431 EXCEPTION
1432 WHEN OTHERS THEN
1433 RAISE e_pagg_engine_error;
1434
1435 END;
1436
1437 /*=============================================================================+
1438 | PROCEDURE
1439 | Preprocess_Rule_set
1440 |
1441 | DESCRIPTION
1442 | Flattens THE rule SET
1443 |
1444 | SCOPE - PRIVATE
1445 |
1446 +======================================-=====================================*/
1447 PROCEDURE Preprocess_Rule_Set ( p_param_rec IN param_record )
1448
1449 IS
1450
1451 l_api_name CONSTANT VARCHAR2(30) := 'Preprocess_Rule_Set';
1452
1453 l_return_status VARCHAR2(1);
1454 l_msg_count NUMBER;
1455 l_msg_data VARCHAR2(240);
1456 e_pl_preprocess_rule_set_err EXCEPTION;
1457
1458 BEGIN
1459
1460 fem_engines_pkg.tech_message( p_severity => g_log_level_2
1461 ,p_module => G_BLOCK||'.'||l_api_name
1462 ,p_msg_text => 'BEGIN');
1463
1464 fem_engines_pkg.tech_message(
1465 p_severity => G_LOG_LEVEL_1
1466 ,p_module => G_BLOCK||'.'||l_api_name
1467 ,p_msg_text => 'Step 4.2.1: Rule Set Pre Processing');
1468
1472 ,p_commit => FND_API.G_TRUE
1469 FEM_RULE_SET_MANAGER.Fem_Preprocess_RuleSet_Pvt(
1470 p_api_version => G_CALLING_API_VERSION
1471 ,p_init_msg_list => FND_API.G_FALSE
1473 ,p_encoded => FND_API.G_TRUE
1474 ,x_return_status => l_return_status
1475 ,x_msg_count => l_msg_count
1476 ,x_msg_data => l_msg_data
1477 ,p_orig_ruleset_object_id => p_param_rec.obj_id
1478 ,p_ds_io_def_id => p_param_rec.dataset_io_obj_def_id
1479 ,p_rule_effective_date => p_param_rec.effective_date_varchar
1480 ,p_output_period_id => p_param_rec.output_cal_period_id
1481 ,p_ledger_id => p_param_rec.ledger_id
1482 ,p_continue_process_on_err_flg => p_param_rec.continue_process_on_err_flg
1483 ,p_execution_mode => 'E'-- Engine Execution Mode
1484 );
1485
1486 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1487 Get_Put_Messages ( p_msg_count => l_msg_count
1488 ,p_msg_data => l_msg_data);
1489
1490 RAISE e_pl_preprocess_rule_set_err;
1491
1492 END IF;
1493
1494 fem_engines_pkg.tech_message ( p_severity => g_log_level_2
1495 ,p_module => G_BLOCK||'.'||l_api_name
1496 ,p_msg_text => 'END');
1497
1498 EXCEPTION
1499 WHEN e_pl_preprocess_rule_set_err THEN
1500 fem_engines_pkg.tech_message (
1501 p_severity => g_log_level_5
1502 ,p_module => G_BLOCK||'.'||l_api_name
1503 ,p_msg_text => 'Preprocess Rule Set Exception' );
1504
1505 FEM_ENGINES_PKG.User_Message (
1506 p_app_name => G_PFT
1507 ,p_msg_name => G_ENG_PRE_PROC_RS_ERR
1508 ,p_token1 => 'RULE_SET_OBJ_ID'
1509 ,p_value1 => p_param_rec.obj_id);
1510 RAISE e_pagg_engine_error;
1511
1512 WHEN OTHERS THEN
1513 FEM_ENGINES_PKG.User_Message (
1514 p_app_name => G_PFT
1515 ,p_msg_name => G_ENG_PRE_PROC_RS_ERR
1516 ,p_token1 => 'RULE_SET_OBJ_ID'
1517 ,p_value1 => p_param_rec.obj_id);
1518 RAISE e_pagg_engine_error;
1519
1520 END Preprocess_Rule_Set;
1521
1522 /*======--====================================================================+
1523 | PROCEDURE
1524 | PROCESS SINGLE RULE
1525 |
1526 | DESCRIPTION
1527 | Processes Single Rule
1528 |
1529 | SCOPE - PRIVATE
1530 |
1531 +============================================================================*/
1532
1533 PROCEDURE Process_Single_Rule ( p_param_rec IN OUT NOCOPY param_record)
1534 IS
1535 l_api_name CONSTANT VARCHAR2(30) := 'Process_Single_Rule';
1536 l_process_table VARCHAR2(30);
1537 l_table_alias VARCHAR2(30);
1538 l_cond_obj_id NUMBER;
1539 l_obj_def_id NUMBER;
1540 l_col_obj_def_id NUMBER;
1541 l_col_tmplt_obj_id NUMBER;
1542 l_obj_name VARCHAR2(30);
1543 l_bulk_sql VARCHAR2(10000);
1544 l_hierarchy_obj_id NUMBER;
1545 l_aggregate_customer VARCHAR2(1);
1546 l_dimension_grp_id_from NUMBER;
1547 l_dimension_grp_id_to NUMBER;
1548 l_err_code NUMBER := 0;
1549 l_err_msg VARCHAR2(255);
1550 l_prev_req_id NUMBER;
1551 l_exec_state VARCHAR2(30);
1552 l_reuse_slices VARCHAR2(10);
1553 l_msg_count NUMBER;
1554 l_exception_code VARCHAR2(50);
1555 l_msg_data VARCHAR2(200);
1556 l_return_status VARCHAR2(50) := NULL;
1557 l_aggregation_method NUMBER;
1558 l_rel_dim_group_seq_to NUMBER;
1559 l_rel_dim_group_seq_from NUMBER;
1560 l_skip_level NUMBER;
1561 l_levels_skipped BOOLEAN := FALSE;
1562 l_src_tab_name VARCHAR2(30);
1563 l_num_rows_loaded NUMBER;
1564 l_proceed_further BOOLEAN;
1565 l_update_flag BOOLEAN := FALSE;
1566
1567
1568 TYPE v_rollup_cust_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1569 v_rollup_cust v_rollup_cust_type;
1570
1571 TYPE v_msg_list_type IS VARRAY(20) OF fem_mp_process_ctl_t.message%TYPE;
1572 v_msg_list v_msg_list_type;
1573
1574 e_process_single_rule_error EXCEPTION;
1575 e_process_undo_rule_error EXCEPTION;
1576 e_register_rule_error EXCEPTION;
1577
1578 BEGIN
1579 l_process_table := 'FEM_CUSTOMER_PROFIT';
1580 -- Initialize the return status to SUCCESS
1581 p_param_rec.return_status := fnd_api.G_RET_STS_SUCCESS;
1582
1583 fem_engines_pkg.tech_message ( p_severity => g_log_level_2
1584 ,p_module => G_BLOCK||'.'||l_api_name
1585 ,p_msg_text => 'BEGIN');
1586
1587 fem_engines_pkg.tech_message (
1588 p_severity => g_log_level_2
1589 ,p_module => G_BLOCK||'.'||l_api_name
1593 l_err_code,
1590 ,p_msg_text => 'Validate the Rule Definition');
1591
1592 fem_rule_set_manager.validate_rule_public(
1594 l_err_msg,
1595 p_param_rec.crnt_proc_child_obj_id,
1596 p_param_rec.dataset_io_obj_def_id,
1597 p_param_rec.effective_date_varchar,
1598 p_param_rec.output_cal_period_id,
1599 p_param_rec.ledger_id);
1600
1601 -- Unexpected error
1602 IF (l_err_code <> 0) THEN
1603 fem_engines_pkg.user_message (p_app_name => G_FEM
1604 ,p_msg_name => l_err_msg);
1605 RAISE e_process_single_rule_error;
1606 END IF;
1607
1608 fem_engines_pkg.tech_message (
1609 p_severity => g_log_level_3
1610 ,p_module => G_BLOCK||'.'||l_api_name
1611 ,p_msg_text => 'Getting Profit Aggregation Rule Details'||p_param_rec.crnt_proc_child_obj_defn_id);
1612
1613 BEGIN
1614 -- get the details of the rule
1615 SELECT condition_obj_id
1616 ,col_tmplt_obj_id
1617 ,hierarchy_obj_id
1618 ,aggregate_customer
1619 ,dimension_grp_id_from
1620 ,dimension_grp_id_to
1621 INTO p_param_rec.cond_obj_id
1622 ,l_col_tmplt_obj_id
1623 ,l_hierarchy_obj_id
1624 ,p_param_rec.aggregate_customer
1625 ,l_dimension_grp_id_from
1626 ,l_dimension_grp_id_to
1627 FROM pft_pprof_agg_rules
1628 WHERE pprof_agg_obj_def_id = p_param_rec.crnt_proc_child_obj_defn_id;
1629
1630 EXCEPTION
1631 WHEN no_data_found THEN
1632 fem_engines_pkg.user_message (
1633 p_app_name => G_PFT
1634 ,p_msg_name => G_ENG_INVALID_OBJ_DEFN_ERR
1635 ,p_token1 => 'OBJECT_ID'
1636 ,p_value1 => p_param_rec.crnt_proc_child_obj_id
1637 ,p_token2 => 'EFFECTIVE_DATE'
1638 ,p_value2 => p_param_rec.effective_date);
1639 RAISE e_process_single_rule_error;
1640 END;
1641
1642 l_num_rows_loaded := 0.0;
1643 l_proceed_further := TRUE;
1644
1645 BEGIN
1646 -- Call the FEM_PL_PKG.Register_Object_Execution API procedure
1647 -- to register the rollup object execution in FEM_PL_OBJECT_EXECUTIONS,
1648 -- thus obtaining an execution lock.
1649
1650 FEM_PL_PKG.Register_Object_Execution(
1651 p_api_version => G_CALLING_API_VERSION
1652 ,p_commit => fnd_api.G_TRUE
1653 ,p_request_id => p_param_rec.request_id
1654 ,p_object_id => p_param_rec.crnt_proc_child_obj_id
1655 ,p_exec_object_definition_id => p_param_rec.crnt_proc_child_obj_defn_id
1656 ,p_user_id => p_param_rec.user_id
1657 ,p_last_update_login => p_param_rec.login_id
1658 ,p_exec_mode_code => NULL
1659 ,x_exec_state => l_exec_state
1660 ,x_prev_request_id => l_prev_req_id
1661 ,x_msg_count => l_msg_count
1662 ,x_msg_data => l_msg_data
1663 ,x_return_status => l_return_status);
1664
1665 IF (l_return_status <> fnd_api.G_RET_STS_SUCCESS)THEN
1666 Get_Put_Messages ( p_msg_count => l_msg_count
1667 ,p_msg_data => l_msg_data);
1668 RAISE e_register_rule_error;
1669 END IF;
1670
1671 EXCEPTION
1672 WHEN e_register_rule_error THEN
1673
1674 fem_engines_pkg.tech_message (
1675 p_severity => G_LOG_LEVEL_6
1676 ,p_module => G_BLOCK||'.'||l_api_name
1677 ,p_msg_text => 'Register Rule Exception');
1678
1679 fem_engines_pkg.user_message (
1680 p_app_name => G_PFT
1681 ,p_msg_name => G_PL_OBJ_EXEC_LOCK_ERR);
1682
1683 p_param_rec.return_status := fnd_api.G_RET_STS_ERROR;
1684
1685 RAISE e_process_single_rule_error;
1686 END;
1687
1688 --Register object definition
1689 --Need to register Dataset Group Object
1690
1691 Register_Object_Definition (
1692 p_param_rec => p_param_rec
1693 ,p_object_id => p_param_rec.crnt_proc_child_obj_id
1694 ,p_obj_def_id => p_param_rec.dataset_io_obj_def_id);
1695
1696 --------------------------------------------------------------------------
1697 --call to FEM_PL_PKG.register_dependent_objdefs
1698 --------------------------------------------------------------------------
1699
1700 fem_engines_pkg.tech_message( p_severity => g_log_level_3
1701 ,p_module => G_BLOCK||'.'||l_api_name
1702 ,p_msg_text => 'Register_Dependent_Objects');
1703
1704 Register_Dependent_Objects(p_param_rec => p_param_rec);
1705
1706 --------------------------------------------------------------------------
1707 -- Call FEM_PL_PKG.Register_Table()
1708 --------------------------------------------------------------------------
1709
1710 fem_engines_pkg.tech_message( p_severity => g_log_level_3
1714 IF (p_param_rec.aggregate_customer = 'Y' ) THEN
1711 ,p_module => G_BLOCK||'.'||l_api_name
1712 ,p_msg_text => 'Register table ');
1713
1715 fem_engines_pkg.tech_message(
1716 p_severity => g_log_level_3
1717 ,p_module => G_BLOCK||'.'||l_api_name
1718 ,p_msg_text => 'Aggregate to customer: INSERT');
1719
1720 Register_Table(p_param_rec => p_param_rec
1721 ,p_tbl_name => g_fem_customer_profit
1722 ,p_num_output_rows => 0
1723 ,p_stmt_type => g_insert);
1724
1725 ELSIF (p_param_rec.aggregate_customer = 'N' ) THEN
1726 fem_engines_pkg.tech_message(
1727 p_severity => g_log_level_3
1728 ,p_module => G_BLOCK||'.'||l_api_name
1729 ,p_msg_text => 'Hierarchy Rollup: UPDATE');
1730
1731 Register_Table(p_param_rec => p_param_rec
1732 ,p_tbl_name => g_fem_customer_profit
1733 ,p_num_output_rows => 0
1734 ,p_stmt_type => g_insert);
1735 END IF;
1736
1737 --------------------------------------------------------------------------
1738 -- The following step may not be needed as there is only one step in
1739 -- this engine.
1740 --------------------------------------------------------------------------
1741
1742 -- Register step by passing the step name
1743 -- and the execution status of register object execution
1744
1745 Register_Obj_Exe_Step( p_param_rec => p_param_rec
1746 ,p_exe_step => 'ALL'
1747 ,p_exe_status_code => l_exec_state );
1748
1749 IF (p_param_rec.aggregate_customer = 'N') THEN
1750
1751 fem_engines_pkg.tech_message (
1752 p_severity => g_log_level_3
1753 ,p_module => G_BLOCK||'.'||l_api_name
1754 ,p_msg_text => 'Hierarchical Rollup');
1755
1756 fem_engines_pkg.tech_message (
1757 p_severity => g_log_level_3
1758 ,p_module => G_BLOCK||'.'||l_api_name
1759 ,p_msg_text => 'Getting the group seq of the given hierarchy');
1760
1761 SELECT relative_dimension_group_seq
1762 INTO l_rel_dim_group_seq_to
1763 FROM fem_hier_dimension_grps
1764 WHERE hierarchy_obj_id = l_hierarchy_obj_id
1765 AND dimension_group_id = l_dimension_grp_id_to;
1766
1767 SELECT relative_dimension_group_seq
1768 INTO l_rel_dim_group_seq_from
1769 FROM fem_hier_dimension_grps
1770 WHERE hierarchy_obj_id = l_hierarchy_obj_id
1771 AND dimension_group_id = l_dimension_grp_id_from;
1772
1773 fem_engines_pkg.tech_message (
1774 p_severity => g_log_level_3
1775 ,p_module => G_BLOCK||'.'||l_api_name
1776 ,p_msg_text => 'Rollup To Level' || l_rel_dim_group_seq_to);
1777
1778 fem_engines_pkg.tech_message (
1779 p_severity => g_log_level_3
1780 ,p_module => G_BLOCK||'.'||l_api_name
1781 ,p_msg_text => 'Rollup From Level' || l_rel_dim_group_seq_from);
1782
1783 fem_engines_pkg.tech_message (
1784 p_severity => g_log_level_3
1785 ,p_module => G_BLOCK||'.'||l_api_name
1786 ,p_msg_text => 'Getting Customer Hierarchy definition');
1787
1788 get_object_definition( p_object_type_code => 'HIERARCHY'
1789 ,p_object_id => l_hierarchy_obj_id
1790 ,p_effective_date => p_param_rec.effective_date
1791 ,x_obj_def_id => l_hier_object_def_id);
1792
1793 BEGIN
1794 -- To get the customer id to which the roll up has to be done
1795 -- and the intermediate cutomers if levels are skipped
1796 fem_engines_pkg.tech_message(
1797 p_severity => g_log_level_2
1798 ,p_module => G_BLOCK||'.'||l_api_name
1799 ,p_msg_text => 'Get all the customers between the From Level and
1800 To Level');
1801
1802 --EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
1806 SELECT customer_id
1803
1804 --SELECT /*+ PARALLEL(fem_customer_profit 4) PARALLEL(hier_cust 2)*/ customer_id
1805
1807 BULK COLLECT INTO v_rollup_cust
1808 FROM fem_customer_profit fcp,
1809 ((SELECT child_id
1810 FROM fem_customers_hier a
1811 WHERE hierarchy_obj_def_id = l_hier_object_def_id
1812 AND parent_depth_num = l_rel_dim_group_seq_to
1813 AND child_depth_num BETWEEN
1814 l_rel_dim_group_seq_to AND (l_rel_dim_group_seq_from-1))
1815 UNION (
1816 SELECT child_id
1817 FROM fem_customers_hier a
1818 WHERE hierarchy_obj_def_id = l_hier_object_def_id
1819 AND child_depth_num = l_rel_dim_group_seq_to)) hier_cust
1820 WHERE hier_cust.child_id = fcp.customer_id
1821 AND fcp.cal_period_id = p_param_rec.output_cal_period_id
1822 AND fcp.ledger_id = p_param_rec.ledger_id
1823 AND fcp.dataset_code = p_param_rec.output_dataset_code
1824 AND data_aggregation_type_code = 'CUSTOMER_AGGREGATION';
1825
1826 fem_engines_pkg.tech_message(
1827 p_severity => g_log_level_2
1828 ,p_module => G_BLOCK||'.'||l_api_name
1829 ,p_msg_text => 'Update the Table Id of the customer records
1830 which has to be updated');
1831
1832 IF v_rollup_cust.EXISTS(1) THEN
1833
1834 l_update_flag := TRUE;
1835
1836 FORALL i IN v_rollup_cust.FIRST..v_rollup_cust.LAST
1837 UPDATE fem_customer_profit
1838 SET table_id = -999999
1839 WHERE cal_period_id = p_param_rec.output_cal_period_id
1840 AND ledger_id = p_param_rec.ledger_id
1841 AND dataset_code = p_param_rec.output_dataset_code
1842 AND source_system_code = p_param_rec.source_system_code
1843 AND data_aggregation_type_code = 'CUSTOMER_AGGREGATION'
1844 AND customer_id = v_rollup_cust(i);
1845 ELSE
1846
1847 l_update_flag := FALSE;
1848
1849 SELECT customer_id
1850 BULK COLLECT INTO v_rollup_cust
1851 FROM fem_customer_profit fcp,
1852 ((SELECT child_id
1853 FROM fem_customers_hier a
1854 WHERE hierarchy_obj_def_id = l_hier_object_def_id
1855 AND parent_depth_num = l_rel_dim_group_seq_to
1856 AND child_depth_num = l_rel_dim_group_seq_from)
1857 UNION (SELECT child_id
1858 FROM fem_customers_hier a
1859 WHERE hierarchy_obj_def_id = l_hier_object_def_id
1860 AND child_depth_num = l_rel_dim_group_seq_from)) hier_cust
1861 WHERE hier_cust.child_id = fcp.customer_id
1862 AND fcp.cal_period_id = p_param_rec.output_cal_period_id
1863 AND fcp.ledger_id = p_param_rec.ledger_id
1864 AND fcp.dataset_code = p_param_rec.output_dataset_code
1865 AND data_aggregation_type_code = 'CUSTOMER_AGGREGATION';
1866
1867 IF v_rollup_cust.EXISTS(1) THEN
1868 l_proceed_further := TRUE;
1869 ELSE
1870 l_proceed_further := FALSE;
1871 END IF;
1872 END IF;
1873
1874 EXCEPTION
1878 fem_engines_pkg.user_message (
1875 WHEN OTHERS THEN
1876 gv_prg_msg := SQLERRM;
1877 gv_callstack := DBMS_UTILITY.Format_Call_Stack;
1879 p_app_name => G_FEM
1880 ,p_msg_name => G_UNEXPECTED_ERROR
1881 ,p_token1 => 'ERR_MSG'
1882 ,p_value1 => gv_prg_msg);
1883 RAISE e_process_single_rule_error;
1884 END;
1885
1886 IF l_proceed_further THEN
1887
1888 fem_engines_pkg.tech_message(
1889 p_severity => g_log_level_2
1890 ,p_module => G_BLOCK||'.'||l_api_name
1891 ,p_msg_text => 'Check whether Levels are skipped');
1892
1893 l_skip_level := l_rel_dim_group_seq_from - l_rel_dim_group_seq_to;
1894
1895 IF l_skip_level <= 1 THEN
1896
1897 fem_engines_pkg.tech_message(
1898 p_severity => g_log_level_2
1899 ,p_module => G_BLOCK||'.'||l_api_name
1900 ,p_msg_text => 'Levels are not skipped');
1901
1902 l_levels_skipped := FALSE;
1903
1904 ELSE
1905
1906 fem_engines_pkg.tech_message(
1907 p_severity => g_log_level_2
1908 ,p_module => G_BLOCK||'.'||l_api_name
1909 ,p_msg_text => 'Some Intermediate Levels in
1910 the hiearchy are skipped');
1911 l_levels_skipped := TRUE;
1912
1913 END IF;
1914
1915 END IF; -- l_proceed_further
1916
1917 END IF; -- p_aggregate_cust = 'N'
1918
1919
1920 IF l_proceed_further THEN
1921
1922 fem_engines_pkg.tech_message (
1923 p_severity => g_log_level_3
1924 ,p_module => G_BLOCK||'.'||l_api_name
1925 ,p_msg_text => 'Getting column population template definition');
1926
1927 get_object_definition( p_object_type_code => 'COL_POP_TEMPLATE'
1928 ,p_object_id => l_col_tmplt_obj_id
1929 ,p_effective_date => p_param_rec.effective_date
1930 ,x_obj_def_id => l_col_obj_def_id);
1931
1932 SELECT source_table_name
1933 INTO l_src_tab_name
1934 FROM fem_col_population_tmplt_b
1935 WHERE col_pop_templt_obj_def_id = l_col_obj_def_id
1936 AND ROWNUM = 1;
1937
1938 IF (l_src_tab_name <> l_process_table) THEN
1939 fem_engines_pkg.tech_message (
1940 p_severity => g_log_level_3
1941 ,p_module => G_BLOCK||'.'||l_api_name
1942 ,p_msg_text => 'The source table name defined in the column
1943 population template and the processing table defined
1944 in the rule should be same.');
1945
1946 fem_engines_pkg.user_message (
1947 p_app_name => G_PFT
1948 ,p_msg_name => G_ENG_GENERIC_5_ERR);
1949
1950 RAISE e_process_single_rule_error;
1951
1952 END IF;
1953
1954 BEGIN
1955 SELECT 1
1956 INTO l_aggregation_method
1957 FROM fem_col_population_tmplt_b
1958 WHERE col_pop_templt_obj_def_id = l_col_obj_def_id
1959 AND aggregation_method <> 'NOAGG'
1960 AND ROWNUM = 1;
1961 EXCEPTION
1962 WHEN no_data_found THEN
1963 l_aggregation_method := 0;
1964 END;
1965
1966 IF (l_aggregation_method <> 1) THEN
1967 fem_engines_pkg.tech_message (
1968 p_severity => g_log_level_3
1969 ,p_module => G_BLOCK||'.'||l_api_name
1970 ,p_msg_text => 'Column population template should have at least
1971 one aggregation function defined for Profit
1972 Aggregation Rule');
1973
1974 fem_engines_pkg.user_message (
1975 p_app_name => G_PFT
1976 ,p_msg_name => G_ENG_GEN_CPOP_AGG_AGG_ERR);
1977
1978 RAISE e_process_single_rule_error;
1979 END IF;
1980
1981 -- To create the INSERT statement for the Profit Aggregation Step.
1982 l_bulk_sql := Create_Aggregation_Stmt(
1983 p_rule_obj_id => p_param_rec.crnt_proc_child_obj_id
1984 ,p_table_name => l_process_table
1985 ,p_cal_period_id => p_param_rec.output_cal_period_id
1986 ,p_dataset_io_obj_def_id => p_param_rec.dataset_io_obj_def_id
1987 ,p_effective_date => p_param_rec.effective_date_varchar
1988 ,p_ledger_id => p_param_rec.ledger_id
1989 ,p_source_system_code => p_param_rec.source_system_code
1990 ,p_condition_obj_id => p_param_rec.cond_obj_id
1991 ,p_col_obj_def_id => l_col_obj_def_id
1992 ,p_aggregate_customer => p_param_rec.aggregate_customer);
1993
1994 l_table_alias := Fem_Col_Tmplt_Defn_Api_Pub.g_src_alias;
1995
1996 fem_engines_pkg.tech_message(
1997 p_severity => g_log_level_3
1998 ,p_module => G_BLOCK||'.'||l_api_name
1999 ,p_msg_text => 'Registering step: ALL');
2000
2004 fem_engines_pkg.tech_message(
2001 -- For Hierarchical Rollup and Levels are Skipped
2002 -- Submitting the MP Request for each of the Skipped Levels
2003
2005 p_severity => g_log_level_1
2006 ,p_module => G_BLOCK||'.'||l_api_name
2007 ,p_msg_text => l_bulk_sql);
2008
2009 IF l_levels_skipped AND p_param_rec.aggregate_customer = 'N' THEN
2010
2011 fem_engines_pkg.tech_message(
2012 p_severity => g_log_level_3
2013 ,p_module => G_BLOCK||'.'||l_api_name
2014 ,p_msg_text => 'Levels are skipped, Creating MP Request in Loop for
2015 all levels');
2016
2017 WHILE (l_rel_dim_group_seq_to < l_rel_dim_group_seq_from) LOOP
2018
2019 fem_engines_pkg.tech_message(
2020 p_severity => g_log_level_3
2021 ,p_module => G_BLOCK||'.'||l_api_name
2022 ,p_msg_text => 'Rolling up to level: '||l_rel_dim_group_seq_to || ' from ' || l_rel_dim_group_seq_from );
2023
2024 BEGIN
2025 EXECUTE IMMEDIATE l_bulk_sql USING l_hier_object_def_id,
2026 l_rel_dim_group_seq_to,
2027 l_rel_dim_group_seq_to,
2028 l_rel_dim_group_seq_from,
2029 l_hier_object_def_id,
2030 l_rel_dim_group_seq_to;
2031 EXCEPTION
2032 WHEN OTHERS THEN
2033 RAISE e_process_undo_rule_error;
2034 END;
2035
2036 -- Update specific customers ONLY and not the cumulative
2037
2038 l_rel_dim_group_seq_to := l_rel_dim_group_seq_to + 1;
2039
2040 l_num_rows_loaded := l_num_rows_loaded + SQL%ROWCOUNT;
2041
2042 --Updation of record_count is not needed as we are using
2043 --COUNT(*) for mapping the record_count column
2044
2045 --Update_Record_Count( p_param_rec => p_param_rec,
2046 -- p_num_rows_loaded => l_num_rows_loaded);
2047
2048 END LOOP;
2049
2050 -- If No Levels are Skipped in Hierarchy Rollup
2051 ELSIF NOT l_levels_skipped AND p_param_rec.aggregate_customer = 'N' THEN
2052
2053 fem_engines_pkg.tech_message(
2054 p_severity => g_log_level_3
2055 ,p_module => G_BLOCK||'.'||l_api_name
2056 ,p_msg_text => 'LEVELS are not skipped');
2057
2058 BEGIN
2059 EXECUTE IMMEDIATE l_bulk_sql USING l_hier_object_def_id,
2060 l_rel_dim_group_seq_to,
2061 l_rel_dim_group_seq_to,
2062 l_rel_dim_group_seq_from,
2063 l_hier_object_def_id,
2064 l_rel_dim_group_seq_to;
2065 EXCEPTION
2066 WHEN OTHERS THEN
2067 fnd_file.put_line(fnd_file.log,'Error = ' || SQLERRM);
2068 RAISE e_process_undo_rule_error;
2069 END;
2070 l_num_rows_loaded := SQL%ROWCOUNT;
2071
2072 -- When Aggregate To customer is selected.
2073 ELSE
2074
2075 fem_engines_pkg.tech_message(
2076 p_severity => g_log_level_3
2077 ,p_module => G_BLOCK||'.'||l_api_name
2078 ,p_msg_text => 'Aggregate To Customer');
2079
2080 BEGIN
2081 EXECUTE IMMEDIATE l_bulk_sql;
2082
2083 EXCEPTION
2084 WHEN OTHERS THEN
2085 fnd_file.put_line(fnd_file.log,'Error = ' || SQLERRM);
2086 RAISE e_process_undo_rule_error;
2087 END;
2088
2089 l_num_rows_loaded := SQL%ROWCOUNT;
2090
2091 --Updation of record_count is not needed as we are using
2092 --COUNT(*) for mapping the record_count column
2093
2094 --Update_Record_Count( p_param_rec => p_param_rec,
2095 -- p_num_rows_loaded => l_num_rows_loaded);
2096
2097 END IF;
2098
2099 p_param_rec.rows_loaded := NVL(l_num_rows_loaded,0);
2100
2101 Process_Obj_Exec_Step(
2102 p_param_rec => p_param_rec
2103 ,p_exe_step => 'ALL'
2104 ,p_exe_status_code => g_exec_status_success
2105 ,p_tbl_name => g_fem_customer_profit
2106 ,p_num_rows => NVL(l_num_rows_loaded,0));
2107
2108 IF (l_update_flag AND p_param_rec.aggregate_customer = 'N' ) THEN
2109 fem_engines_pkg.tech_message(
2110 p_severity => g_log_level_3
2111 ,p_module => G_BLOCK||'.'||l_api_name
2112 ,p_msg_text => 'Update the Created By Object Id and Request Id');
2113
2114 BEGIN
2115
2116 FORALL i IN v_rollup_cust.FIRST..v_rollup_cust.LAST
2117 UPDATE fem_customer_profit fcp
2118 SET (FCP.created_by_object_id
2119 ,FCP.created_by_request_id
2120 ,FCP.record_count) =
2121 (SELECT created_by_object_id,created_by_request_id,record_count
2122 FROM fem_customer_profit FCP
2123 WHERE FCP.ledger_id = p_param_rec.ledger_id
2124 AND FCP.source_system_code = p_param_rec.source_system_code
2125 AND FCP.cal_period_id = p_param_rec.output_cal_period_id
2126 AND FCP.dataset_code = p_param_rec.output_dataset_code
2127 AND FCP.data_aggregation_type_code = 'CUSTOMER_AGGREGATION'
2128 AND FCP.table_id = -999999
2129 AND FCP.CUSTOMER_ID = v_rollup_cust(i))
2130 WHERE FCP.ledger_id = p_param_rec.ledger_id
2131 AND FCP.source_system_code = p_param_rec.source_system_code
2132 AND FCP.cal_period_id = p_param_rec.output_cal_period_id
2133 AND FCP.dataset_code = p_param_rec.output_dataset_code
2134 AND FCP.data_aggregation_type_code = 'CUSTOMER_AGGREGATION'
2135 AND FCP.table_id <> -999999
2136 AND FCP.customer_id = v_rollup_cust(i);
2137
2138 EXCEPTION
2139 WHEN OTHERS THEN
2140 gv_prg_msg := SQLERRM;
2141 gv_callstack := DBMS_UTILITY.Format_Call_Stack;
2142 fem_engines_pkg.user_message (
2143 p_app_name => G_FEM
2144 ,p_msg_name => G_UNEXPECTED_ERROR
2145 ,p_token1 => 'ERR_MSG'
2146 ,p_value1 => gv_prg_msg);
2147 RAISE e_process_single_rule_error;
2148 END;
2149
2150 BEGIN
2151 fem_engines_pkg.tech_message(
2152 p_severity => g_log_level_3
2153 ,p_module => G_BLOCK||'.'||l_api_name
2154 ,p_msg_text => 'Deleting the Duplicate Rows');
2155
2156 DELETE FROM fem_customer_profit
2157 WHERE table_id = -999999;
2158
2159 EXCEPTION
2160 WHEN OTHERS THEN
2161 gv_prg_msg := SQLERRM;
2162 gv_callstack := DBMS_UTILITY.Format_Call_Stack;
2163
2164 fem_engines_pkg.user_message (
2165 p_app_name => G_FEM
2166 ,p_msg_name => G_UNEXPECTED_ERROR
2167 ,p_token1 => 'ERR_MSG'
2168 ,p_value1 => gv_prg_msg);
2169
2170 RAISE e_process_single_rule_error;
2171 END;
2172
2173 END IF;
2174
2175 ELSE
2176 Process_Obj_Exec_Step(
2177 p_param_rec => p_param_rec
2178 ,p_exe_step => 'ALL'
2179 ,p_exe_status_code => g_exec_status_success
2180 ,p_tbl_name => g_fem_customer_profit
2181 ,p_num_rows => 0);
2182
2183 END IF; -- l_proceed_further
2184
2185 COMMIT;
2186
2187 fem_engines_pkg.tech_message ( p_severity => G_LOG_LEVEL_2
2188 ,p_module => G_BLOCK||'.'||l_api_name
2189 ,p_msg_text => 'END');
2190
2191 EXCEPTION
2192
2193 WHEN e_process_undo_rule_error THEN
2194
2195 Process_Obj_Exec_Step(
2196 p_param_rec => p_param_rec
2197 ,p_exe_step => 'ALL'
2198 ,p_exe_status_code => 'ERROR_UNDO'
2199 ,p_tbl_name => g_fem_customer_profit
2200 ,p_num_rows => NULL);
2201
2202 fem_engines_pkg.tech_message (
2203 p_severity => g_log_level_5
2204 ,p_module => G_BLOCK||'.'||l_api_name
2205 ,p_msg_text => 'Process Single Rule Exception');
2206
2207 --fem_engines_pkg.user_message (p_app_name => G_PFT
2208 -- ,p_msg_name => G_ENG_SINGLE_RULE_ERR);
2209
2210 p_param_rec.return_status := fnd_api.G_RET_STS_ERROR;
2211
2212 WHEN e_process_single_rule_error THEN
2213
2214 Process_Obj_Exec_Step(
2215 p_param_rec => p_param_rec
2216 ,p_exe_step => 'ALL'
2217 ,p_exe_status_code => g_exec_status_error_rerun
2218 ,p_tbl_name => g_fem_customer_profit
2219 ,p_num_rows => NULL);
2220
2221 fem_engines_pkg.tech_message (
2222 p_severity => g_log_level_5
2223 ,p_module => G_BLOCK||'.'||l_api_name
2224 ,p_msg_text => 'Process Single Rule Exception');
2225
2226 fem_engines_pkg.user_message (p_app_name => G_PFT
2227 ,p_msg_name => G_ENG_SINGLE_RULE_ERR);
2228
2229 p_param_rec.return_status := fnd_api.G_RET_STS_ERROR;
2230
2231 WHEN OTHERS THEN
2232
2233 Process_Obj_Exec_Step(
2234 p_param_rec => p_param_rec
2235 ,p_exe_step => 'ALL'
2236 ,p_exe_status_code => g_exec_status_error_rerun
2237 ,p_tbl_name => g_fem_customer_profit
2238 ,p_num_rows => NULL);
2239
2240 fem_engines_pkg.user_message (p_app_name => G_PFT
2241 ,p_msg_name => G_ENG_SINGLE_RULE_ERR);
2242
2243 p_param_rec.return_status := fnd_api.G_RET_STS_ERROR;
2244
2245 END Process_Single_Rule;
2246
2247 /*=============================================================================+
2248 | FUNCTION
2249 | Create Profit Aggregation Statement
2250 |
2251 | DESCRIPTION
2252 | Creates the Bulk SQL for Profit Aggregation
2253 |
2254 | SCOPE - PRIVATE
2255 |
2256 +============================================================================*/
2257 FUNCTION Create_Aggregation_Stmt (p_rule_obj_id IN NUMBER,
2258 p_table_name IN VARCHAR2,
2259 p_cal_period_id IN NUMBER,
2260 p_dataset_io_obj_def_id IN NUMBER,
2261 p_effective_date IN VARCHAR2,
2262 p_ledger_id IN NUMBER,
2263 p_source_system_code IN NUMBER,
2264 p_condition_obj_id IN NUMBER,
2265 p_col_obj_def_id IN NUMBER,
2266 p_aggregate_customer IN VARCHAR2)
2267 RETURN LONG IS
2268 l_api_name CONSTANT VARCHAR2(30) := 'Create_Aggregation_Stmt';
2269 l_insert_head_stmt LONG;
2270 l_select_stmt LONG;
2271 l_from_stmt LONG;
2272 l_where_stmt LONG;
2273 l_order_by_clause VARCHAR2(100);
2274 l_cond_where_stmt LONG;
2275 l_request_id NUMBER;
2276 l_user_id NUMBER;
2277 l_selection_param NUMBER;
2278 l_condition_sel_param VARCHAR2(10);
2279 l_msg_count NUMBER;
2280 l_msg_data VARCHAR2(500);
2281 l_return_status VARCHAR2(20);
2282 l_effective_date DATE;
2283 l_object_def_id NUMBER;
2284 l_table_alias VARCHAR2(30);
2285 e_col_population_api_err EXCEPTION;
2286 BEGIN
2287
2288 l_request_id := FND_GLOBAL.conc_request_id;
2289 l_user_id := FND_GLOBAL.user_id;
2290 l_effective_date := FND_DATE.CANONICAL_TO_DATE(p_effective_date);
2291 l_condition_sel_param := 'BOTH';
2292
2293 fem_engines_pkg.tech_message ( p_severity => g_log_level_2
2294 ,p_module => G_BLOCK||'.'||l_api_name
2295 ,p_msg_text => 'BEGIN');
2296
2297 IF p_condition_obj_id IS NULL THEN
2298 l_selection_param := 1;
2299 ELSE
2300 l_selection_param := 0;
2301 END IF;
2302
2303 -- Calls Column population Template API to create SQL statement based on
2304 -- the given col population template.
2305 Fem_Col_Tmplt_Defn_Api_Pub.generate_predicates (
2306 p_api_version => g_api_version
2307 ,p_init_msg_list => g_false
2308 ,p_commit => g_false
2309 ,p_encoded => g_true
2310 ,p_object_def_id => p_col_obj_def_id
2311 ,p_selection_param => l_selection_param
2312 ,p_effective_date => p_effective_date
2313 ,p_condition_obj_id => p_condition_obj_id
2314 ,p_condition_sel_param => l_condition_sel_param
2315 ,p_load_sec_relns => NULL
2316 ,p_dataset_grp_obj_def_id => p_dataset_io_obj_def_id
2317 ,p_cal_period_id => p_cal_period_id
2318 ,p_ledger_id => p_ledger_id
2319 ,p_source_system_code => p_source_system_code
2320 ,p_created_by_object_id => p_rule_obj_id
2321 ,p_created_by_request_id => l_request_id
2322 ,p_insert_list => l_insert_head_stmt
2323 ,p_select_list => l_select_stmt
2324 ,p_from_clause => l_from_stmt
2325 ,p_where_clause => l_where_stmt
2326 ,p_con_where_clause => l_cond_where_stmt
2327 ,x_msg_count => l_msg_count
2328 ,x_msg_data => l_msg_data
2329 ,x_return_status => l_return_status);
2330
2331 IF (l_return_status <> fnd_api.G_RET_STS_SUCCESS) THEN
2332 Get_Put_Messages ( p_msg_count => l_msg_count
2333 ,p_msg_data => l_msg_data);
2334
2335 RAISE e_col_population_api_err ;
2336
2337 END IF;
2338
2339 l_table_alias := Fem_Col_Tmplt_Defn_Api_Pub.g_src_alias;
2340
2341 IF (p_aggregate_customer = 'Y') THEN
2342 fem_engines_pkg.tech_message ( p_severity => g_log_level_2
2343 ,p_module => G_BLOCK||'.'||l_api_name
2344 ,p_msg_text => 'Aggregate To Customer');
2345 -- To add the where clause to aggregate all the customer data in
2346 -- FEM_CUSTOMER_PROFIT Table
2347 account_aggregation ( p_src_alias => l_table_alias
2348 ,p_select_col => l_select_stmt
2349 ,p_from_clause => l_from_stmt
2350 ,p_where_clause => l_where_stmt
2351 ,p_order_by_clause => l_order_by_clause );
2352 ELSIF (p_aggregate_customer = 'N') THEN
2353 BEGIN
2354 -- The flattening procedure itself takes care if the hierarchy version is alreay flattened
2355 -- Not updating hierarchy flatten flag as its set at the 'Hierarchy Object Id' level (Not at hierarchy object definition level)
2356 -- Flattening hierarchy version
2357 FEM_HIER_UTILS_PVT.Flatten_Whole_Hier_Version( p_api_version => 1.0,
2358 x_return_status => l_return_status ,
2359 x_msg_count => l_msg_count,
2360 x_msg_data => l_msg_data,
2361 --
2362 p_hier_obj_defn_id => l_hier_object_def_id);
2363 EXCEPTION
2364 WHEN OTHERS THEN
2365
2366 fem_engines_pkg.tech_message ( p_severity => g_log_level_2
2367 ,p_module => G_BLOCK||'.'||l_api_name
2368 ,p_msg_text => 'Hierarchy Flattening Process');
2369
2370 RAISE;
2371 END;
2372 fem_engines_pkg.tech_message ( p_severity => g_log_level_2
2373 ,p_module => G_BLOCK||'.'||l_api_name
2374 ,p_msg_text => 'Hierarchical Rollup');
2375 -- To add the hierarchy details for the customer rollup for aggregation
2376 add_hierarchy_details(p_select_col => l_select_stmt
2380 END IF;
2377 ,p_from_clause => l_from_stmt
2378 ,p_where_clause => l_where_stmt
2379 ,p_order_by_clause => l_order_by_clause);
2381
2382
2383 -- Adds the condition statement to the prepared SQL Statement.
2384
2385 IF (l_cond_where_stmt IS NOT NULL) THEN
2386 l_where_stmt := l_where_stmt || 'AND' || l_cond_where_stmt;
2387 ELSE
2388 l_where_stmt := l_where_stmt;
2389 END IF;
2390
2391 SELECT REPLACE(l_select_stmt,'TO_NUMBER(''-987654321'')','COUNT(1)')
2392 INTO l_select_stmt
2393 FROM dual;
2394
2395 -- add mapped columns
2396
2397 RETURN l_insert_head_stmt || ' ' || l_select_stmt || ' ' || l_from_stmt
2398 || ' ' || l_where_stmt || ' ' || l_order_by_clause;
2399
2400 fem_engines_pkg.tech_message ( p_severity => G_LOG_LEVEL_2
2401 ,p_module => G_BLOCK||'.'||l_api_name
2402 ,p_msg_text => 'END');
2403 EXCEPTION
2404 WHEN e_col_population_api_err THEN
2405 fem_engines_pkg.tech_message (
2406 p_severity => g_log_level_5
2407 ,p_module => G_BLOCK||'.'||l_api_name
2408 ,p_msg_text => 'Column Population API Exception' );
2409
2410 FEM_ENGINES_PKG.User_Message (p_app_name => G_PFT
2411 ,p_msg_name => G_ENG_COL_POP_API_ERR);
2412
2413 RAISE e_pagg_engine_error;
2414
2415 WHEN OTHERS THEN
2416 RAISE;
2417
2418 END Create_Aggregation_Stmt;
2419
2420 /*=============================================================================+
2421 | FUNCTION
2422 | is_rule_set_flattened
2423 |
2424 | DESCRIPTION
2425 | To Check whether the RuleSet is flattened or not
2426 |
2427 | SCOPE - PRIVATE
2428 |
2429 +============================================================================*/
2430 FUNCTION is_rule_set_flattened( p_request_id IN NUMBER,
2431 p_rule_set_obj_id IN NUMBER)
2432
2433 RETURN NUMBER IS
2434
2435 l_api_name CONSTANT VARCHAR2(30) := 'is_rule_set_flattened';
2436
2437 l_count NUMBER;
2438
2439 BEGIN
2440 fem_engines_pkg.tech_message ( p_severity => g_log_level_2
2441 ,p_module => G_BLOCK||'.'||l_api_name
2442 ,p_msg_text => 'BEGIN');
2443 SELECT COUNT(*)
2444 INTO l_count
2445 FROM fem_ruleset_process_data p,
2446 fem_pl_object_executions x
2447 WHERE p.request_id = p_request_id AND
2448 p.request_id = x.request_id(+) AND
2449 p.rule_set_obj_id = p_rule_set_obj_id
2450 ORDER BY p.engine_execution_sequence;
2451
2452 IF (l_count = 0) THEN
2453 RETURN -1;
2454 END IF;
2455
2456 RETURN 0;
2457
2458 fem_engines_pkg.tech_message ( p_severity => G_LOG_LEVEL_2
2459 ,p_module => G_BLOCK||'.'||l_api_name
2460 ,p_msg_text => 'END');
2461 EXCEPTION
2462 WHEN no_data_found THEN
2463 RETURN -1;
2464 WHEN OTHERS THEN
2465 RAISE;
2466
2467 END is_rule_set_flattened;
2468
2469 /*============================================================================+
2470 | PROCEDURE
2471 | Eng_Master_Post_Proc
2472 |
2473 | DESCRIPTION
2474 | Updates the status of the request and object execution in the
2475 | processing locks tables.
2476 |
2477 | SCOPE - PRIVATE
2478 |
2479 +============================================================================*/
2480
2481 PROCEDURE Eng_Master_Post_Proc ( p_param_rec IN param_record
2482 ,p_exec_status_code IN VARCHAR2)
2483 IS
2484
2485 l_api_name CONSTANT VARCHAR2(30) := 'Eng_Master_Post_Proc';
2486
2487 l_return_status VARCHAR2(1);
2488 l_msg_count NUMBER;
2489 l_msg_data VARCHAR2(240);
2490 l_commit BOOLEAN;
2491
2492 e_eng_master_post_proc_error EXCEPTION;
2493
2494 BEGIN
2495
2496 fem_engines_pkg.tech_message ( p_severity => g_log_level_2
2497 ,p_module => G_BLOCK||'.'||l_api_name
2498 ,p_msg_text => 'BEGIN');
2499
2500 --------------------------------------------------------------------------
2501 -- STEP 1: Update Object Execution Status.
2502 --------------------------------------------------------------------------
2503 fem_engines_pkg.tech_message (
2504 p_severity => g_log_level_1
2505 ,p_module => G_BLOCK||'.'||l_api_name
2506 ,p_msg_text => 'Step 1: Update Object Execution Status');
2507
2508 FEM_PL_PKG.Update_Obj_Exec_Status (
2509 p_api_version => 1.0
2510 ,p_commit => FND_API.G_FALSE
2511 ,p_request_id => p_param_rec.request_id
2512 ,p_object_id => p_param_rec.crnt_proc_child_obj_id
2513 ,p_exec_status_code => p_exec_status_code
2514 ,p_user_id => p_param_rec.user_id
2515 ,p_last_update_login => p_param_rec.login_id
2516 ,x_msg_count => l_msg_count
2517 ,x_msg_data => l_msg_data
2518 ,x_return_status => l_return_status);
2519
2520
2521 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2522 Get_Put_Messages ( p_msg_count => l_msg_count
2523 ,p_msg_data => l_msg_data);
2524 RAISE e_eng_master_post_proc_error;
2525 END IF;
2526
2527 --------------------------------------------------------------------------
2528 -- STEP 2: Update Object Execution Errors.
2529 --------------------------------------------------------------------------
2530 IF (p_exec_status_code <> g_exec_status_success) THEN
2531
2532 fem_engines_pkg.tech_message (
2533 p_severity => g_log_level_1
2534 ,p_module => G_BLOCK||'.'||l_api_name
2535 ,p_msg_text => 'Step 2: Update Object Execution Errors');
2536
2537 FEM_PL_PKG.Update_Obj_Exec_Errors (
2538 p_api_version => 1.0
2539 ,p_commit => FND_API.G_FALSE
2540 ,p_request_id => p_param_rec.request_id
2541 ,p_object_id => p_param_rec.crnt_proc_child_obj_id
2542 ,p_errors_reported => 1
2543 ,p_errors_reprocessed => 0
2544 ,p_user_id => p_param_rec.user_id
2545 ,p_last_update_login => p_param_rec.login_id
2546 ,x_msg_count => l_msg_count
2547 ,x_msg_data => l_msg_data
2548 ,x_return_status => l_return_status);
2549
2550 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2551 Get_Put_Messages ( p_msg_count => l_msg_count
2552 ,p_msg_data => l_msg_data);
2553 RAISE e_eng_master_post_proc_error;
2554 END IF;
2555
2556 END IF;
2557
2558 --------------------------------------------------------------------------
2559 -- STEP 3: Update Request Status.
2560 --------------------------------------------------------------------------
2561 fem_engines_pkg.tech_message (
2562 p_severity => g_log_level_1
2563 ,p_module => G_BLOCK||'.'||l_api_name
2564 ,p_msg_text => 'Step 3: Update Request Status');
2565
2566 FEM_PL_PKG.Update_Request_Status (
2567 p_api_version => 1.0
2568 ,p_commit => FND_API.G_FALSE
2569 ,p_request_id => p_param_rec.request_id
2570 ,p_exec_status_code => p_exec_status_code
2571 ,p_user_id => p_param_rec.user_id
2572 ,p_last_update_login => p_param_rec.login_id
2573 ,x_msg_count => l_msg_count
2574 ,x_msg_data => l_msg_data
2575 ,x_return_status => l_return_status);
2576
2577 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2578 Get_Put_Messages ( p_msg_count => l_msg_count
2579 ,p_msg_data => l_msg_data);
2580
2581 RAISE e_eng_master_post_proc_error;
2582 END IF;
2583
2584 IF (p_exec_status_code = g_exec_status_success) THEN
2585 FEM_ENGINES_PKG.User_Message(p_app_name => G_PFT,
2586 p_msg_name => 'PFT_PPROF_ROW_SUMMARY',
2587 p_token1 => 'ROWSP',
2588 p_value1 => NVL(p_param_rec.rows_loaded,0),
2589 p_token2 => 'ROWSL',
2590 p_value2 => NVL(p_param_rec.rows_loaded,0));
2591
2592 IF NVL(p_param_rec.rows_loaded,0) = 0 THEN
2593 FEM_ENGINES_PKG.User_Message (
2594 p_app_name => G_PFT
2595 ,p_msg_name => G_ENG_PAG_NO_OP_ROWS_ERR);
2596 END IF;
2597
2598 END IF;
2599
2600 COMMIT;
2601
2602 fem_engines_pkg.tech_message ( p_severity => g_log_level_2
2603 ,p_module => G_BLOCK||'.'||l_api_name
2604 ,p_msg_text => 'END');
2605
2606 EXCEPTION
2607 WHEN e_eng_master_post_proc_error THEN
2608 fem_engines_pkg.tech_message (
2609 p_severity => g_log_level_5
2610 ,p_module => G_BLOCK||'.'||l_api_name
2611 ,p_msg_text => 'Engine Master Post Process Exception');
2612
2613 FEM_ENGINES_PKG.User_Message (
2614 p_app_name => G_PFT
2615 ,p_msg_name => G_ENG_ENGINE_POST_PROC_ERR
2616 ,p_token1 => 'OBJECT_ID'
2617 ,p_value1 => p_param_rec.obj_id);
2618
2619 RAISE e_pagg_engine_error;
2620
2621 END Eng_Master_Post_Proc;
2622
2626 |
2623 /*=============================================================================+
2624 | PROCEDURE
2625 | Get_Object_Definition
2627 | DESCRIPTION
2628 | Returns the Object Definition Id for the given Object Id for the
2629 | given effective Date
2630 |
2631 | SCOPE - PRIVATE
2632 |
2633 +============================================================================*/
2634
2635 PROCEDURE Get_Object_Definition ( p_object_type_code IN VARCHAR2
2636 ,p_object_id IN NUMBER
2637 ,p_effective_date IN DATE
2638 ,x_obj_def_id OUT NOCOPY NUMBER)
2639 IS
2640
2641 l_api_name CONSTANT VARCHAR2(30) := 'Get_Object_Definition';
2642
2643 BEGIN
2644
2645 fem_engines_pkg.tech_message ( p_severity => g_log_level_2
2646 ,p_module => G_BLOCK||'.'||l_api_name
2647 ,p_msg_text => 'BEGIN');
2648
2649 SELECT d.object_definition_id
2650 INTO x_obj_def_id
2651 FROM fem_object_definition_b d
2652 ,fem_object_catalog_b o
2653 WHERE o.object_id = p_object_id
2654 AND o.object_type_code = p_object_type_code
2655 AND d.object_id = o.object_id
2656 AND p_effective_date BETWEEN d.effective_start_date
2657 AND d.effective_end_date
2658 AND d.old_approved_copy_flag = 'N';
2659
2660 fem_engines_pkg.tech_message ( p_severity => g_log_level_2
2661 ,p_module => G_BLOCK||'.'||l_api_name
2662 ,p_msg_text => 'END');
2663
2664 EXCEPTION
2665 WHEN no_data_found THEN
2666 FEM_ENGINES_PKG.User_Message (
2667 p_app_name => G_PFT
2668 ,p_msg_name => G_ENG_INVALID_OBJ_DEFN_ERR
2669 ,p_token1 => 'OBJECT_ID'
2670 ,p_value1 => p_object_id
2671 ,p_token2 => 'EFFECTIVE_DATE'
2672 ,p_value2 => p_effective_date);
2673
2674 RAISE e_pagg_engine_error;
2675
2676 END Get_Object_Definition;
2677
2678 /*============================================================================+
2679 | PROCEDURE
2680 | Get_Put_Messages
2681 |
2682 | DESCRIPTION
2683 | To put the User messages,to be placed in common loader package.
2684 |
2685 | SCOPE - PRIVATE
2686 |
2687 +============================================================================*/
2688
2689 PROCEDURE Get_Put_Messages ( p_msg_count IN NUMBER
2690 ,p_msg_data IN VARCHAR2)
2691 IS
2692
2693 l_api_name CONSTANT VARCHAR2(30) := 'Get_Put_Messages';
2694 l_msg_count NUMBER;
2695 l_msg_data VARCHAR2(4000);
2696 l_msg_out NUMBER;
2697 l_message VARCHAR2(4000);
2698
2699 BEGIN
2700
2701 fem_engines_pkg.tech_message ( p_severity => g_log_level_2
2702 ,p_module => G_BLOCK||'.'||l_api_name
2703 ,p_msg_text => 'msg_count='||p_msg_count);
2704
2705 l_msg_data := p_msg_data;
2706
2707 IF (p_msg_count = 1) THEN
2708
2709 FND_MESSAGE.Set_Encoded(l_msg_data);
2710 l_message := FND_MESSAGE.Get;
2711
2712 FEM_ENGINES_PKG.User_Message ( p_msg_text => l_message );
2713
2714 fem_engines_pkg.tech_message ( p_severity => g_log_level_2
2715 ,p_module => G_BLOCK||'.'||l_api_name
2716 ,p_msg_text => 'msg_data='||l_message);
2717
2718 ELSIF (p_msg_count > 1) THEN
2719
2720 FOR i IN 1..p_msg_count LOOP
2721 FND_MSG_PUB.Get ( p_msg_index => i
2722 ,p_encoded => FND_API.G_FALSE
2723 ,p_data => l_message
2724 ,p_msg_index_out => l_msg_out);
2725
2726 FEM_ENGINES_PKG.User_Message ( p_msg_text => l_message );
2727
2728 fem_engines_pkg.tech_message (
2729 p_severity => g_log_level_2
2730 ,p_module => G_BLOCK||'.'||l_api_name
2731 ,p_msg_text => 'msg_data = '||l_message);
2732
2733 END LOOP;
2734
2735 END IF;
2736
2737 FND_MSG_PUB.Initialize;
2738
2739 END Get_Put_Messages;
2740
2741 /*============================================================================+
2742 | PROCEDURE
2743 | add_hierarchy_details
2744 |
2745 | DESCRIPTION
2746 | To add the customer hierarchy details.
2747 |
2748 | SCOPE - PRIVATE
2749 |
2750 +============================================================================*/
2751 PROCEDURE add_hierarchy_details(p_select_col IN OUT NOCOPY LONG,
2752 p_from_clause IN OUT NOCOPY LONG,
2753 p_where_clause IN OUT NOCOPY LONG,
2754 p_order_by_clause OUT NOCOPY VARCHAR2 )
2755 IS
2756 l_api_name CONSTANT VARCHAR2(30) := 'add_hierarchy_details';
2757 l_where LONG;
2758 BEGIN
2759 fem_engines_pkg.tech_message ( p_severity => g_log_level_2
2760 ,p_module => G_BLOCK||'.'||l_api_name
2764 'cust_hier_dump.customer_id' )
2761 ,p_msg_text => 'BEGIN');
2762
2763 SELECT REPLACE(p_select_col,'{{{CUSTOMER_ID}}}',
2765 INTO p_select_col
2766 FROM DUAL;
2767
2768 p_from_clause := p_from_clause ||
2769 ' , ( (SELECT child_id, parent_id customer_id ' ||
2770 ' FROM fem_customers_hier a ' ||
2771 ' WHERE hierarchy_obj_def_id = :1';
2772
2773 p_from_clause := p_from_clause || ' AND parent_depth_num = :2';
2774
2775 p_from_clause := p_from_clause || ' AND child_depth_num BETWEEN :3 AND :4)';
2776
2777 p_from_clause := p_from_clause || 'UNION';
2778
2779 p_from_clause := p_from_clause ||
2780 '(SELECT child_id, child_id customer_id ' ||
2781 ' FROM fem_customers_hier a ' ||
2782 ' WHERE hierarchy_obj_def_id = :5';
2783
2784 p_from_clause := p_from_clause || ' AND child_depth_num = :6 ) )cust_hier_dump';
2785
2786 l_where := Fem_Col_Tmplt_Defn_Api_Pub.g_src_alias ||
2787 '.Customer_Id = ' || 'cust_hier_dump.child_id';
2788
2789 p_where_clause := p_where_clause || ' AND ' || l_where;
2790 p_order_by_clause := 'GROUP BY cust_hier_dump.Customer_Id';
2791
2792 p_where_clause := p_where_clause || ' AND ' ||
2793 Fem_Col_Tmplt_Defn_Api_Pub.g_src_alias ||
2794 '.data_aggregation_type_code = ' ||
2795 '''CUSTOMER_AGGREGATION''';
2796
2797 fem_engines_pkg.tech_message ( p_severity => g_log_level_2
2798 ,p_module => G_BLOCK||'.'||l_api_name
2799 ,p_msg_text => 'END');
2800 EXCEPTION
2801 WHEN OTHERS THEN
2802 RAISE;
2803 END add_hierarchy_details;
2804
2805 --sshanmug : Added additional proc for PL Implementation
2806 /*============================================================================+
2807 | PROCEDURE
2808 | Register_Dependent_Objects
2809 |
2810 | DESCRIPTION
2811 | This procedure retrieves all objects that are dependent on the object that
2812 | is being executed, from FEM_OBJECT_DEPENDENCIES. The effective date is
2813 | used to retrieve the specific definition that will be read, for each
2814 | dependent object, and then registers the retrieved definitions. This
2815 | procedure does not validate that each dependent object has a valid
2816 | definition for the given effective date, and will not detect that a
2817 | dependent object is missing a valid definition. It is the responsibility
2818 | of the calling program to make sure that each dependent object has a
2819 | valid definition for the given effective date.
2820 |
2821 | SCOPE - PRIVATE
2822 |
2823 +============================================================================*/
2824 PROCEDURE Register_Dependent_Objects( p_param_rec IN param_record )
2825 IS
2826
2827 l_api_name CONSTANT VARCHAR2(30) := 'Register_Dependent_Objects';
2828
2829 l_return_status VARCHAR2(1);
2830 l_msg_count NUMBER;
2831 l_msg_data VARCHAR2(240);
2832
2833 e_register_dep_obj_def_error EXCEPTION;
2834
2835 BEGIN
2836 fem_engines_pkg.tech_message ( p_severity => g_log_level_2
2837 ,p_module => G_BLOCK||'.'||l_api_name
2838 ,p_msg_text => 'BEGIN');
2839
2840 -- Register all the Dependent Objects for AGG
2841 FEM_PL_PKG.Register_Dependent_ObjDefs (
2842 p_api_version => G_CALLING_API_VERSION
2843 ,p_commit => FND_API.G_TRUE
2844 ,p_request_id => p_param_rec.request_id
2845 ,p_object_id => p_param_rec.crnt_proc_child_obj_id
2846 ,p_exec_object_definition_id => p_param_rec.crnt_proc_child_obj_defn_id
2847 ,p_effective_date => p_param_rec.effective_date
2848 ,p_user_id => p_param_rec.user_id
2849 ,p_last_update_login => p_param_rec.login_id
2850 ,x_msg_count => l_msg_count
2851 ,x_msg_data => l_msg_data
2852 ,x_return_status => l_return_status);
2853
2854 -- Object Definition Lock exists
2855 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2856 Get_Put_Messages ( p_msg_count => l_msg_count
2857 ,p_msg_data => l_msg_data);
2858 RAISE e_register_dep_obj_def_error;
2859 END IF;
2860
2861 fem_engines_pkg.tech_message ( p_severity => g_log_level_2
2862 ,p_module => G_BLOCK||'.'||l_api_name
2863 ,p_msg_text => 'END');
2864
2865 EXCEPTION
2866 WHEN e_register_dep_obj_def_error THEN
2867 fem_engines_pkg.tech_message (
2868 p_severity => g_log_level_5
2869 ,p_module => G_BLOCK||'.'||l_api_name
2870 ,p_msg_text => 'Register Dependant Objects Exception');
2871
2872 FEM_ENGINES_PKG.User_Message (
2873 p_app_name => G_PFT
2874 ,p_msg_name => G_PL_DEP_OBJ_DEF_ERR
2875 ,p_token1 => 'OBJ_DEF_ID'
2876 ,p_value1 => p_param_rec.crnt_proc_child_obj_defn_id);
2877 RAISE e_pagg_engine_error;
2878
2879 WHEN OTHERS THEN
2880 fem_engines_pkg.tech_message (
2881 p_severity => g_log_level_5
2882 ,p_module => G_BLOCK||'.'||l_api_name
2883 ,p_msg_text => 'Register Dependant Objects Exception');
2884
2885 FEM_ENGINES_PKG.User_Message (
2886 p_app_name => G_PFT
2887 ,p_msg_name => G_PL_DEP_OBJ_DEF_ERR
2888 ,p_token1 => 'OBJ_DEF_ID'
2889 ,p_value1 => p_param_rec.crnt_proc_child_obj_defn_id);
2890
2891 RAISE e_pagg_engine_error;
2892
2893 END Register_Dependent_Objects;
2894
2895 /*============================================================================+
2896 | PROCEDURE
2897 | Update_Num_Of_Input_Rows
2898 |
2899 | DESCRIPTION
2900 | This procedure logs the total number of rows used as input into
2901 | an object execution
2902 |
2903 | SCOPE - PRIVATE
2904 |
2905 +============================================================================*/
2906 PROCEDURE Update_Nbr_Of_Input_Rows( p_param_rec IN param_record
2907 ,p_num_input_rows IN NUMBER)
2908 IS
2909
2910 l_api_name CONSTANT VARCHAR2(30) := 'Update_Num_Of_Input_Rows';
2911
2912 l_return_status VARCHAR2(2);
2913 l_msg_count NUMBER;
2914 l_msg_data VARCHAR2(240);
2915
2916 e_upd_num_input_rows_error EXCEPTION;
2917
2918 BEGIN
2919
2920 fem_engines_pkg.tech_message ( p_severity => g_log_level_2
2921 ,p_module => G_BLOCK||'.'||l_api_name
2922 ,p_msg_text => 'BEGIN');
2923
2924 -- Set the number of output rows for the output table.
2925 FEM_PL_PKG.Update_Num_Of_Input_Rows(
2926 p_api_version => 1.0
2927 ,p_commit => FND_API.G_TRUE
2928 ,p_request_id => p_param_rec.request_id
2929 ,p_object_id => p_param_rec.crnt_proc_child_obj_id
2930 ,p_num_of_input_rows => p_num_input_rows
2931 ,p_user_id => p_param_rec.user_id
2932 ,p_last_update_login => p_param_rec.login_id
2933 ,x_msg_count => l_msg_count
2934 ,x_msg_data => l_msg_data
2935 ,x_return_status => l_return_status);
2936
2937 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2938
2939 Get_Put_Messages( p_msg_count => l_msg_count
2940 ,p_msg_data => l_msg_data);
2941 RAISE e_upd_num_input_rows_error;
2942 END IF;
2943
2944 fem_engines_pkg.tech_message( p_severity => g_log_level_2
2945 ,p_module => G_BLOCK||'.'||l_api_name
2946 ,p_msg_text => 'END');
2947
2948 EXCEPTION
2949 WHEN e_upd_num_input_rows_error THEN
2950 fem_engines_pkg.tech_message (
2951 p_severity => g_log_level_5
2952 ,p_module => G_BLOCK||'.'||l_api_name
2953 ,p_msg_text => 'Update Input Rows Exception');
2954
2955 FEM_ENGINES_PKG.User_Message (
2956 p_app_name => G_PFT
2957 ,p_msg_name => G_PL_IP_UPD_ROWS_ERR);
2958
2959 RAISE e_pagg_engine_error;
2960
2961 WHEN OTHERS THEN
2962 fem_engines_pkg.tech_message (
2963 p_severity => g_log_level_5
2964 ,p_module => G_BLOCK||'.'||l_api_name
2965 ,p_msg_text => 'Update Input Rows Exception');
2966
2967 FEM_ENGINES_PKG.User_Message (
2968 p_app_name => G_PFT
2969 ,p_msg_name => G_PL_IP_UPD_ROWS_ERR);
2970
2971 RAISE e_pagg_engine_error;
2972
2973 END Update_Nbr_Of_Input_Rows;
2974
2975 /*============================================================================+
2976 | PROCEDURE
2977 | Account_Aggregation
2978 |
2979 | DESCRIPTION
2980 | To aggregate all the account data irrespective fo the customer hierarchy
2981 |
2982 |
2983 | SCOPE - PRIVATE
2984 |
2985 +============================================================================*/
2986
2987 PROCEDURE account_aggregation( p_src_alias IN VARCHAR2
2988 ,p_select_col IN OUT NOCOPY LONG
2989 ,p_from_clause IN OUT NOCOPY LONG
2990 ,p_where_clause IN OUT NOCOPY LONG
2991 ,p_order_by_clause OUT NOCOPY VARCHAR2 )
2992 IS
2993
2994 l_api_name CONSTANT VARCHAR2(30) := 'account_aggregation';
2995 l_object_def_id NUMBER;
2996 l_where LONG;
2997 l_dimension_grp_id_from NUMBER;
2998 l_dimension_grp_id_to NUMBER;
2999 BEGIN
3000
3001 fem_engines_pkg.tech_message ( p_severity => g_log_level_2
3002 ,p_module => G_BLOCK||'.'||l_api_name
3003 ,p_msg_text => 'BEGIN');
3004
3005 SELECT REPLACE(p_select_col,'{{{CUSTOMER_ID}}}',
3006 p_src_alias||'.customer_id')
3007 INTO p_select_col
3008 FROM DUAL;
3009
3010 p_where_clause := p_where_clause || ' AND ' ||
3011 p_src_alias || '.data_aggregation_type_code = ' ||
3012 '''ACCOUNT_RELATIONSHIP''';
3013
3014 p_order_by_clause := 'GROUP BY ' ||
3015 p_src_alias ||'.Customer_Id';
3016
3017 fem_engines_pkg.tech_message ( p_severity => g_log_level_2
3018 ,p_module => G_BLOCK||'.'||l_api_name
3019 ,p_msg_text => 'END');
3020
3021 EXCEPTION
3022 WHEN OTHERS THEN
3023 RAISE;
3024
3025 END account_aggregation;
3026
3027 END PFT_PROFITAGG_PUB;