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