[Home] [Help]
PACKAGE BODY: APPS.PFT_PPROFCAL_MASTER_PUB
Source
1 PACKAGE BODY PFT_PPROFCAL_MASTER_PUB AS
2 /* $Header: PFTPCAMB.pls 120.2.12000000.3 2007/08/09 16:09:56 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_PPROFCAL_MASTER_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 g_fem_region_info CONSTANT VARCHAR2(30) := 'FEM_REGION_INFO';
19 g_fem_customers_attr CONSTANT VARCHAR2(30) := 'FEM_CUSTOMERS_ATTR';
20
21 --constant for sql_stmt_type
22 g_insert CONSTANT VARCHAR2(30) := 'INSERT';
23 g_update CONSTANT VARCHAR2(30) := 'UPDATE';
24
25
26 g_default_fetch_limit CONSTANT NUMBER := 99999;
27
28 g_log_level_1 CONSTANT NUMBER := fnd_log.level_statement;
29 g_log_level_2 CONSTANT NUMBER := fnd_log.level_procedure;
30 g_log_level_3 CONSTANT NUMBER := fnd_log.level_event;
31 g_log_level_4 CONSTANT NUMBER := fnd_log.level_exception;
32 g_log_level_5 CONSTANT NUMBER := fnd_log.level_error;
33 g_log_level_6 CONSTANT NUMBER := fnd_log.level_unexpected;
34
35
36 --------------------------------------------------------------------------------
37 -- Declare package variables --
38 --------------------------------------------------------------------------------
39 -- Exception variables
40 gv_prg_msg VARCHAR2(2000);
41 gv_callstack VARCHAR2(2000);
42 -- Bulk Fetch Limit
43 gv_fetch_limit NUMBER;
44
45 z_master_err_state NUMBER;
46
47 --------------------------------------------------------------------------------
48 -- Declare package exceptions --
49 --------------------------------------------------------------------------------
50 -- General profit calculation Engine Exception
51 e_pc_engine_error EXCEPTION;
52 USER_EXCEPTION EXCEPTION;
53
54
55 --------------------------------------------------------------------------------
56 -- Declare private procedures and functions --
57 --------------------------------------------------------------------------------
58
59 PROCEDURE Eng_Master_Prep (
60 p_obj_id IN NUMBER
61 ,p_dataset_io_obj_def_id IN NUMBER
62 ,p_effective_date IN VARCHAR2
63 ,p_output_cal_period_id IN NUMBER
64 ,p_ledger_id IN NUMBER
65 ,p_continue_process_on_err_flg IN VARCHAR2
66 ,p_source_system_code IN NUMBER
67 ,x_param_rec OUT NOCOPY param_record
68 );
69
70 PROCEDURE Preprocess_Rule_Set (
71 p_param_rec IN param_record
72 );
73
74 PROCEDURE Process_Single_Rule (
75 p_param_rec IN OUT NOCOPY param_record
76 );
77
78 PROCEDURE Register_Process_Request (
79 p_param_rec IN param_record
80 );
81
82 PROCEDURE Get_Object_Definition (
83 p_object_type_code IN VARCHAR2
84 ,p_object_id IN NUMBER
85 ,p_effective_date IN DATE
86 ,x_obj_def_id OUT NOCOPY NUMBER
87 );
88
89 PROCEDURE Register_Object_Definition(
90 p_param_rec IN param_record
91 ,p_object_id IN NUMBER
92 ,p_obj_def_id IN NUMBER
93 );
94
95 PROCEDURE Register_Obj_Exe_Step(
96 p_param_rec IN param_record
97 ,p_exe_step IN VARCHAR2
98 ,p_exe_status_code IN VARCHAR2
99 );
100
101 PROCEDURE Eng_Master_Post_Proc (
102 p_param_rec IN param_record
103 ,p_exec_status_code IN VARCHAR2
104 );
105
106 PROCEDURE Get_Put_Messages (
107 p_msg_count IN NUMBER
108 ,p_msg_data IN VARCHAR2
109 );
110
111 FUNCTION is_rule_set_flattened(
112 p_request_id IN NUMBER
113 ,p_rule_set_obj_id IN NUMBER
114 )
115
116 RETURN NUMBER;
117
118 PROCEDURE Update_Nbr_Of_Input_Rows(
119 p_param_rec IN param_record
120 ,p_num_input_rows IN NUMBER
121 );
122
123 PROCEDURE Register_Dependent_Objects(
124 p_param_rec IN param_record
125 );
126
127 PROCEDURE Register_Table(
128 p_param_rec IN param_record
129 ,p_tbl_name IN VARCHAR2
130 ,p_num_output_rows IN NUMBER
131 ,p_stmt_type IN VARCHAR2
132 );
133
134 PROCEDURE Register_Updated_Column(
135 p_param_rec IN param_record
136 ,p_table_name IN VARCHAR2
137 ,p_statement_type IN VARCHAR2
138 ,p_column_name IN VARCHAR2);
139
140 -----------------------------------------------------------------------------
141 -- Package bodies for functions/procedures
142 -----------------------------------------------------------------------------
143 /*==========================================================================+
144 | PROCEDURE
145 | PROCESS REQUEST
146 |
147 | DESCRIPTION
148 | Main engine procedure for Profit Calculation step in PFT.
149 |
150 | SCOPE - PUBLIC
151 |
152 +==========================================================================*/
153
154
155 PROCEDURE Process_Request( Errbuf OUT NOCOPY VARCHAR2,
156 Retcode OUT NOCOPY NUMBER,
157 p_obj_id IN NUMBER,
158 p_effective_date IN VARCHAR2,
159 p_ledger_id IN NUMBER,
160 p_output_cal_period_id IN NUMBER,
161 p_dataset_grp_obj_def_id IN NUMBER,
162 p_continue_process_on_err_flg IN VARCHAR2,
163 p_source_system_code IN NUMBER)
164 IS
165
166 -----------------------
167 -- Declare constants --
168 -----------------------
169 l_api_name VARCHAR2(30) := 'Process_Request';
170 l_api_version NUMBER;
171 l_commit VARCHAR2(10);
172 l_init_msg_list VARCHAR2(10);
173
174 -----------------------
175 -- Declare variables --
176 -----------------------
177
178 x_return_status VARCHAR2(1000);
179 x_msg_count NUMBER;
180 x_msg_data VARCHAR2 (1000);
181 l_param_rec param_record;
182 l_proc_param_rec param_record;
183 l_object_type_code VARCHAR2(30);
184 l_rule_set_obj_def_id NUMBER;
185 l_rule_set_name VARCHAR2(255);
186 l_next_rule_obj_id NUMBER;
187 l_next_rule_obj_def_id NUMBER;
188 l_next_rule_exec_seq NUMBER;
189 l_next_rule_exec_status VARCHAR2(30);
190 l_err_code NUMBER;
191 l_msg_count NUMBER;
192 l_err_msg VARCHAR2(500);
193 l_msg_data VARCHAR2(500);
194 l_return_status VARCHAR2(500);
195 l_ruleset_status VARCHAR2(500);
196 l_completion_status BOOLEAN;
197 l_rollup_sequence NUMBER;
198
199 l_err_buf VARCHAR2(50);
200 l_ret_code NUMBER;
201
202 ----------------------------
203 -- Declare static cursors --
204 ----------------------------
205 CURSOR l_rule_set_rules(p_request_id IN NUMBER,p_ruleset_obj_id IN NUMBER) IS
206 select rs.child_obj_id
207 ,rs.child_obj_def_id
208 ,x.exec_status_code
209 from fem_ruleset_process_data rs,
210 fem_pl_object_executions x
211 where rs.request_id = p_request_id
212 and rs.rule_set_obj_id = p_ruleset_obj_id
213 and x.request_id(+) = rs.request_id
214 and x.object_id(+) = rs.child_obj_id
215 and x.exec_object_definition_id(+) = rs.child_obj_def_id
216 order by rs.engine_execution_sequence;
217 -----------------------------------------------------------
218 -- Declare flags to keep track of which cursors are open --
219 -----------------------------------------------------------
220 l_rule_set_rules_is_open BOOLEAN;
221
222 /*******************************************************************************
223 * *
224 * Profit Calculation Master *
225 * Execution BLOCK *
226 * *
227 *******************************************************************************/
228
229 BEGIN
230
231 l_api_version := 1.0;
232 l_init_msg_list := FND_API.G_FALSE;
233 l_commit := FND_API.G_FALSE;
234
235 --Initialize Local Parameters
236 l_rule_set_rules_is_open := FALSE;
237 z_master_err_state := FEM_UTILS.G_RSM_NO_ERR;
238 -- initialize status to success
239 x_return_status := FND_API.G_RET_STS_SUCCESS;
240
241 FEM_ENGINES_PKG.Tech_Message( p_severity => g_log_level_2
242 ,p_module => G_BLOCK||l_api_name
243 ,p_msg_text => 'BEGIN');
244
245 fnd_log_repository.init;
246 fnd_msg_pub.initialize;
247
248 --------------------------------------------------------------------------
249 -- Check for the required parameters
250 --------------------------------------------------------------------------
251
252 IF (p_obj_id IS NULL OR p_dataset_grp_obj_def_id IS NULL OR
253 p_effective_date IS NULL OR p_output_cal_period_id IS NULL OR
254 p_ledger_id IS NULL) THEN
255
256 FEM_ENGINES_PKG.User_Message (
257 p_app_name => G_FEM
258 ,p_msg_name => G_ENG_BAD_CONC_REQ_PARAM_ERR);
259
260 RAISE e_pc_engine_error;
261 END IF;
262
263 --Do the engine master prep
264 --------------------------------------------------------------------------
265 -- STEP 1: Engine Master Preparation
266 --------------------------------------------------------------------------
267
268 FEM_ENGINES_PKG.tech_message (
269 p_severity => g_log_level_2
270 ,p_module => G_BLOCK||'.'||l_api_name
271 ,p_msg_text => 'Step 1: Engine Master Preperation');
272
273 Eng_Master_Prep (
274 p_obj_id => p_obj_id
275 ,p_effective_date => p_effective_date
276 ,p_ledger_id => p_ledger_id
277 ,p_output_cal_period_id => p_output_cal_period_id
278 ,p_dataset_io_obj_def_id => p_dataset_grp_obj_def_id
279 ,p_continue_process_on_err_flg => p_continue_process_on_err_flg
280 ,p_source_system_code => p_source_system_code
281 ,x_param_rec => l_param_rec);
282
283 -----------------------------------------------------------------------------
284 -- STEP 2: registering process request for either a single rule or a rule set
285 -----------------------------------------------------------------------------
286 FEM_ENGINES_PKG.tech_message (
287 p_severity => g_log_level_3
288 ,p_module => G_BLOCK||'.'||l_api_name
289 ,p_msg_text => 'Step 2: Register Request');
290
291 --Register request
292 Register_Process_Request(p_param_rec => l_param_rec);
293
294 IF (l_param_rec.obj_type_code = 'PPROF_PROFIT_CALC') THEN
295 -----------------------------------------------------------------------
296 -- STEP 3: Processing for a single rule submission
297 -----------------------------------------------------------------------
298 FEM_ENGINES_PKG.tech_message (
299 p_severity => g_log_level_3
300 ,p_module => G_BLOCK||'.'||l_api_name
301 ,p_msg_text => 'Step 3: Process for a single rule ');
302
303 --Set the current processing object id
304 l_param_rec.crnt_proc_child_obj_id := l_param_rec.obj_id;
305
306 Process_Single_Rule(p_param_rec => l_param_rec);
307
308 FEM_ENGINES_PKG.tech_message (
309 p_severity => g_log_level_3
310 ,p_module => G_BLOCK||'.'||l_api_name
311 ,p_msg_text => 'Status After Process for a single rule '
312 || l_param_rec.return_status);
313
314 IF (l_param_rec.return_status <> FND_API.G_RET_STS_SUCCESS) THEN
315 -- For Single Consolidation Rule, raise exception to end request
316 -- immediately with a completion status of ERROR, regardless of the
317 -- value for the continue_process_on_err_flg parameter.
318 RAISE e_pc_engine_error;
319 END IF;
320
321 ELSIF (l_param_rec.obj_type_code = 'RULE_SET') THEN
322 -----------------------------------------------------------------------
323 -- STEP 4: Processing for a rule set
324 -----------------------------------------------------------------------
325 -----------------------------------------------------------------------
326 -- STEP 4.1: Flattening the rule set
327 -----------------------------------------------------------------------
328 FEM_ENGINES_PKG.Tech_Message(
329 p_severity => g_log_level_3
330 ,p_module => G_BLOCK||'.'||l_api_name
331 ,p_msg_text => 'Step 3.1 Flatten the rule set');
332
333 IF(is_rule_set_flattened(l_param_rec.request_id
334 ,l_param_rec.crnt_proc_child_obj_id ) <> 0)THEN
335 --------------------------------------------------------------------
336 -- STEP 4.2: Preprocess rule set
337 --------------------------------------------------------------------
338 FEM_ENGINES_PKG.Tech_Message(
339 p_severity => g_log_level_1
340 ,p_module => G_BLOCK||'.'||l_api_name
341 ,p_msg_text => 'Step 4.2 PreProcess rule set' );
342
343 Preprocess_Rule_Set( p_param_rec => l_param_rec);
344
345 END IF;
346 -----------------------------------------------------------------------
347 -- STEP 4.3.1: Loop through each rule in the rule set
348 -- STEP 4.3.2: Open cursor for rule set
349 -- STEP 4.3.3: Process each rule in the rule set loop
350 -- STEP 4.3.4: Execution status for rule processing
351 -----------------------------------------------------------------------
352 FEM_ENGINES_PKG.Tech_Message(
353 p_severity => g_log_level_1
354 ,p_module => G_BLOCK||'.'||l_api_name
355 ,p_msg_text => 'Step 4.3.1: Loop through all Rule Set Rules');
356
357 OPEN l_rule_set_rules(l_param_rec.request_id,l_param_rec.obj_id);
358
359 l_rule_set_rules_is_open := TRUE;
360
361 FEM_ENGINES_PKG.Tech_Message(
362 p_severity => g_log_level_1
366 LOOP
363 ,p_module => G_BLOCK||'.'||l_api_name
364 ,p_msg_text => 'Step 4.3.2:Rule set loop');
365
367 FETCH l_rule_set_rules INTO l_next_rule_obj_id,
368 l_next_rule_obj_def_id,
369 l_next_rule_exec_status;
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.3.3: Process next rule in rule set: '
375 ||l_next_rule_obj_id);
376
377 FEM_ENGINES_PKG.Tech_Message(
378 p_severity => g_log_level_1
379 ,p_module => G_BLOCK||'.'||l_api_name
380 ,p_msg_text => 'Step 4.3.4: Process next rule in rule set status:'
381 || l_next_rule_exec_status);
382
383 EXIT WHEN l_rule_set_rules%NOTFOUND;
384
385 l_rollup_sequence := l_rollup_sequence + 1;
386
387 --update the param rec for the current
388 --processing object_id and object_definition_id
389 l_param_rec.crnt_proc_child_obj_id := l_next_rule_obj_id;
390 l_param_rec.crnt_proc_child_obj_defn_id := l_next_rule_obj_def_id;
391
392 IF (l_next_rule_exec_status IS NULL OR
393 l_next_rule_exec_status <> 'SUCCESS') THEN
394 -----------------------------------------------------------------
395 -- STEP 4.2.3: Process Rule Set Rule
396 -----------------------------------------------------------------
397 FEM_ENGINES_PKG.Tech_Message(
398 p_severity => g_log_level_1
399 ,p_module => G_BLOCK||'.'||l_api_name
400 ,p_msg_text => 'Step 4.3.5: Process Rule Set Rule #'
401 ||TO_CHAR(l_rollup_sequence));
402
403 Process_Single_Rule(p_param_rec => l_param_rec );
404
405 FEM_ENGINES_PKG.tech_message (
406 p_severity => g_log_level_3
407 ,p_module => G_BLOCK||'.'||l_api_name
408 ,p_msg_text => 'Status After Process for a single rule '
409 || l_param_rec.return_status);
410
411 IF (l_param_rec.return_status <> FND_API.G_RET_STS_SUCCESS) THEN
412 -- Set the request status to match Calculation Rule's
413 -- return status.
414 l_ruleset_status := l_param_rec.return_status;
415 IF (l_param_rec.continue_process_on_err_flg = 'N') THEN
416 -- Raise exception to end request immediately with
417 -- a completion status of ERROR.
418 RAISE e_pc_engine_error;
419 END IF;
420 END IF;
421 END IF;
422 END LOOP;
423
424 CLOSE l_rule_set_rules;
425
426 IF (l_ruleset_status <> FND_API.G_RET_STS_SUCCESS) THEN
427 -- Raise exception to end request with a completion status of ERROR,
428 -- if the rule set status is not equal to SUCCESS.
429 RAISE e_pc_engine_error;
430 END IF;
431
432 ELSE
433 NULL;
434 END IF;
435
436 --------------------------------------------------------------------------
437 -- STEP 5: Engine Master Post Processing.
438 --------------------------------------------------------------------------
439 FEM_ENGINES_PKG.Tech_Message (
440 p_severity => g_log_level_3
441 ,p_module => G_BLOCK||'.'||l_api_name
442 ,p_msg_text => 'Step 5: Engine Master Post Processing');
443
444 Eng_Master_Post_Proc ( p_param_rec => l_param_rec
445 ,p_exec_status_code => g_exec_status_success);
446
447 --------------------------------------------------------------------------
448 -- STEP 6: Standard API support
449 --------------------------------------------------------------------------
450 IF FND_API.To_Boolean(NVL(l_commit,'F')) THEN
451 COMMIT WORK;
452 END IF;
453
454 /* IF (l_rule_set_rules_is_open) THEN
455 CLOSE l_rule_set_rules;
456 END IF;*/
457 IF (l_rule_set_rules%ISOPEN) THEN
458 CLOSE l_rule_set_rules;
459 END IF;
460
461 x_return_status := FND_API.G_RET_STS_SUCCESS;
462
463 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
464 ,p_module => G_BLOCK||'.'||l_api_name
465 ,p_msg_text => 'END');
466
467 EXCEPTION
468 WHEN e_pc_engine_error THEN
469 --close the open cursors
470 /*IF (l_rule_set_rules_is_open) THEN
471 CLOSE l_rule_set_rules;
472 END IF;*/
473
474 IF (l_rule_set_rules%ISOPEN) THEN
475 CLOSE l_rule_set_rules;
476 END IF;
477
478 FEM_ENGINES_PKG.Tech_Message (
479 p_severity => g_log_level_3
480 ,p_module => G_BLOCK||'.'||l_api_name
481 ,p_msg_text => 'Step 5: Engine Master Post Processing: ERROR');
482
483 Eng_Master_Post_Proc( p_param_rec => l_param_rec
484 ,p_exec_status_code => g_exec_status_error_rerun);
485
486 l_completion_status := FND_CONCURRENT.Set_Completion_Status('ERROR'
487 ,NULL);
488
492
489 -- FEM_ENGINES_PKG.User_Message (
490 -- p_app_name => G_PFT
491 -- ,p_msg_name => 'Profit Calculation Engine Error');
493 -- Set the return status to ERROR
494 x_return_status := 'E';
495
496 FEM_ENGINES_PKG.Tech_Message (
497 p_severity => g_log_level_5
498 ,p_module => G_BLOCK||'.'||l_api_name
499 ,p_msg_text => 'Profit Calculation Engine Error');
500 WHEN OTHERS THEN
501 gv_prg_msg := SQLERRM;
502 gv_callstack := DBMS_UTILITY.Format_Call_Stack;
503
504 --close the open cursors
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 FEM_ENGINES_PKG.Tech_Message (
513 p_severity => g_log_level_3
514 ,p_module => G_BLOCK||'.'||l_api_name
515 ,p_msg_text => 'Step 5: Engine Master Post Processing: ERROR');
516
517 Eng_Master_Post_Proc( p_param_rec => l_param_rec
518 ,p_exec_status_code => g_exec_status_error_rerun);
519
520 l_completion_status := FND_CONCURRENT.Set_Completion_Status('ERROR'
521 ,NULL);
522
523 FEM_ENGINES_PKG.Tech_Message (
524 p_severity => g_log_level_6
525 ,p_module => G_BLOCK||'.'||l_api_name||'.Unexpected Exception'
526 ,p_msg_text => gv_prg_msg);
527
528 FEM_ENGINES_PKG.Tech_Message (
529 p_severity => g_log_level_6
530 ,p_module => G_BLOCK||'.'||l_api_name||'.Unexpected Exception'
531 ,p_msg_text => gv_callstack);
532
533 FEM_ENGINES_PKG.User_Message (
534 p_app_name => G_FEM
535 ,p_msg_name => G_UNEXPECTED_ERROR
536 ,p_token1 => 'ERR_MSG'
537 ,p_value1 => gv_prg_msg);
538
539 -- Set the return status to UNEXP_ERROR
540 x_return_status := 'U';
541
542 END Process_Request;
543
544 /*============================================================================+
545 |PROCEDURE
546 | Eng_Master_Prep
547 |DESCRIPTION
548 | Prepares the Engine Master, Initializes all the variables.
549 |
550 | SCOPE - PRIVATE
551 |
552 +============================================================================*/
553
554 PROCEDURE Eng_Master_Prep (
555 p_obj_id IN NUMBER
556 ,p_dataset_io_obj_def_id IN NUMBER
557 ,p_effective_date IN VARCHAR2
558 ,p_output_cal_period_id IN NUMBER
559 ,p_ledger_id IN NUMBER
560 ,p_continue_process_on_err_flg IN VARCHAR2
561 ,p_source_system_code IN NUMBER
562 ,x_param_rec OUT NOCOPY param_record)
563 IS
564
565 l_api_name CONSTANT VARCHAR2(30) := 'Eng_Master_Prep';
566
567 e_eng_master_prep_error EXCEPTION;
568
569 BEGIN
570
571 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
572 ,p_module => G_BLOCK||'.'||l_api_name
573 ,p_msg_text => 'BEGIN');
574
575 --------------------------------------------------------------------------
576 -- Set all the Main Parameters
577 --------------------------------------------------------------------------
578 x_param_rec.obj_id := p_obj_id;
579 x_param_rec.dataset_io_obj_def_id := p_dataset_io_obj_def_id;
580 x_param_rec.output_cal_period_id := p_output_cal_period_id;
581 x_param_rec.ledger_id := p_ledger_id;
582 x_param_rec.effective_date_varchar := p_effective_date;
583 x_param_rec.effective_date :=
584 FND_DATE.Canonical_To_Date(p_effective_date);
585 x_param_rec.source_system_code := p_source_system_code;
586 x_param_rec.continue_process_on_err_flg := p_continue_process_on_err_flg;
587
588 --------------------------------------------------------------------------
589 -- Set all the Global Parameters
590 --------------------------------------------------------------------------
591 x_param_rec.user_id := FND_GLOBAL.User_Id;
592 x_param_rec.login_id := FND_GLOBAL.Login_Id;
593 x_param_rec.request_id := FND_GLOBAL.Conc_Request_Id;
594 x_param_rec.resp_id := FND_GLOBAL.Resp_Id;
595 x_param_rec.pgm_id := FND_GLOBAL.Conc_Program_Id;
596 x_param_rec.pgm_app_id := FND_GLOBAL.Prog_Appl_Id;
597
598 -- Get the limit for bulk fetches
599 gv_fetch_limit := NVL (FND_PROFILE.Value_Specific ('FEM_BULK_FETCH_LIMIT'
600 ,x_param_rec.user_id
601 ,NULL
602 ,NULL)
603 ,g_default_fetch_limit);
604
605 ----------------------------------------------------------------------------
606 -- Get the object info from fem_object_catalog_b for the object_id passed in
607 ----------------------------------------------------------------------------
611 ,p_msg_text => 'Getting the Object Type Code of the given Object');
608 FEM_ENGINES_PKG.Tech_Message (
609 p_severity => g_log_level_3
610 ,p_module => G_BLOCK||'.'||l_api_name
612
613 BEGIN
614 SELECT object_type_code
615 ,local_vs_combo_id
616 INTO x_param_rec.obj_type_code
617 ,x_param_rec.local_vs_combo_id
618 FROM fem_object_catalog_b
619 WHERE object_id = x_param_rec.obj_id;
620
621 EXCEPTION
622 WHEN OTHERS THEN
623 FEM_ENGINES_PKG.User_Message (
624 p_app_name => G_PFT
625 ,p_msg_name => G_ENG_INVALID_OBJ_ERR
626 ,p_token1 => 'OBJECT_ID'
627 ,p_value1 => x_param_rec.obj_id);
628
629 FEM_ENGINES_PKG.Tech_Message (
630 p_severity => g_log_level_3
631 ,p_module => G_BLOCK||'.'||l_api_name
632 ,p_msg_text => 'Invalid Object Id' || x_param_rec.obj_id);
633
634 RAISE e_eng_master_prep_error;
635 END;
636
637 --------------------------------------------------------------------------
638 -- If this is a Rule Set Submission, check that the object_type_code and
639 -- local_vs_combo_id of the rule matches the Rule Set's.
640 --------------------------------------------------------------------------
641 IF (x_param_rec.obj_type_code = 'RULE_SET') THEN
642
643 FEM_ENGINES_PKG.Tech_Message (
644 p_severity => g_log_level_3
645 ,p_module => G_BLOCK||'.'||l_api_name
646 ,p_msg_text => 'Obj type code is a rule set');
647
648 BEGIN
649 Get_Object_Definition(
650 p_object_type_code => x_param_rec.obj_type_code
651 ,p_object_id => x_param_rec.obj_id
652 ,p_effective_date => x_param_rec.effective_date
653 ,x_obj_def_id => x_param_rec.crnt_proc_child_obj_defn_id);
654
655 EXCEPTION
656 WHEN OTHERS THEN
657 FEM_ENGINES_PKG.User_Message (
658 p_app_name => G_PFT
659 ,p_msg_name => G_ENG_INV_OBJ_DEFN_RS_ERR
660 ,p_token1 => 'OBJECT_ID'
661 ,p_value1 => x_param_rec.obj_id
662 ,p_token2 => 'EFFECTIVE_DATE'
663 ,p_value2 => x_param_rec.effective_date);
664
665 FEM_ENGINES_PKG.Tech_Message (
666 p_severity => g_log_level_3
667 ,p_module => G_BLOCK||'.'||l_api_name
668 ,p_msg_text => 'No Definition found for the ruleset :'
669 || x_param_rec.obj_id || 'for the Date :'
670 || x_param_rec.effective_date);
671
672 RAISE e_eng_master_prep_error;
673 END;
674
675 ELSIF (x_param_rec.obj_type_code = 'PPROF_PROFIT_CALC') THEN
676
677 FEM_ENGINES_PKG.Tech_Message (
678 p_severity => g_log_level_3
679 ,p_module => G_BLOCK||'.'||l_api_name
680 ,p_msg_text => 'Obj type code is a single rule');
681
682 BEGIN
683 Get_Object_Definition(
684 p_object_type_code => x_param_rec.obj_type_code
685 ,p_object_id => x_param_rec.obj_id
686 ,p_effective_date => x_param_rec.effective_date
687 ,x_obj_def_id => x_param_rec.crnt_proc_child_obj_defn_id);
688
689 EXCEPTION
690 WHEN OTHERS THEN
691 FEM_ENGINES_PKG.User_Message (
692 p_app_name => G_PFT
693 ,p_msg_name => G_ENG_INVALID_OBJ_DEFN_ERR
694 ,p_token1 => 'OBJECT_ID'
695 ,p_value1 => x_param_rec.obj_id
696 ,p_token2 => 'EFFECTIVE_DATE'
697 ,p_value2 => x_param_rec.effective_date);
698
699 FEM_ENGINES_PKG.Tech_Message (
700 p_severity => g_log_level_3
701 ,p_module => G_BLOCK||'.'||l_api_name
702 ,p_msg_text => 'No Definition found for the Rule :'
703 || x_param_rec.obj_id || 'for the Date :'
704 || x_param_rec.effective_date);
705 RAISE e_eng_master_prep_error;
706 END;
707 ELSE
708 FEM_ENGINES_PKG.User_Message(
709 p_app_name => G_PFT
710 ,p_msg_name => G_ENG_INVALIDRULETYPE_ERR
711 ,p_token1 => 'OBJECT_TYPE_CODE'
712 ,p_value1 => x_param_rec.obj_type_code);
713
714 RAISE e_eng_master_prep_error;
715 END IF;
716
717 --------------------------------------------------------------------------
718 -- Get the Dataset Group Object ID
719 --------------------------------------------------------------------------
720 BEGIN
721 SELECT object_id
722 INTO x_param_rec.dataset_grp_obj_id
723 FROM fem_object_definition_b
724 WHERE object_definition_id = x_param_rec.dataset_io_obj_def_id;
725 EXCEPTION
726 WHEN OTHERS THEN
727 FEM_ENGINES_PKG.User_Message (
728 p_app_name => G_PFT
729 ,p_msg_name => G_ENG_INVALID_OBJ_ERR
730 ,p_token1 => 'OBJECT_ID'
731 ,p_value1 => x_param_rec.dataset_io_obj_def_id);
732
733 FEM_ENGINES_PKG.Tech_Message (
737 || x_param_rec.dataset_io_obj_def_id);
734 p_severity => g_log_level_3
735 ,p_module => G_BLOCK||'.'||l_api_name
736 ,p_msg_text => 'No Object found for the given Dataset Group:'
738 RAISE e_eng_master_prep_error;
739 END;
740 --------------------------------------------------------------------------
741 -- Get the Output Dataset Code
742 --------------------------------------------------------------------------
743 BEGIN
744 SELECT output_dataset_code
745 INTO x_param_rec.output_dataset_code
746 FROM fem_ds_input_output_defs
747 WHERE dataset_io_obj_def_id = x_param_rec.dataset_io_obj_def_id;
748
749 EXCEPTION
750 WHEN OTHERS THEN
751 FEM_ENGINES_PKG.User_Message (
752 p_app_name => G_PFT
753 ,p_msg_name => G_ENG_NO_OUTPUT_DS_ERR
754 ,p_token1 => 'DATASET_GROUP_OBJ_DEF_ID'
755 ,p_value1 => x_param_rec.dataset_io_obj_def_id);
756
757 FEM_ENGINES_PKG.Tech_Message (
758 p_severity => g_log_level_3
759 ,p_module => G_BLOCK||'.'||l_api_name
760 ,p_msg_text => 'No Output Dataset for the DS Group Definition:'
761 || x_param_rec.dataset_grp_name);
762
763 RAISE e_eng_master_prep_error;
764 END;
765
766
767 --------------------------------------------------------------------------
768 -- Get the Source System Code for PFT if a null param value was passed.
769 --------------------------------------------------------------------------
770 IF (x_param_rec.source_system_code IS NULL) THEN
771
772 -- For all Processing default the Source System Display Code to PFT
773 BEGIN
774 SELECT source_system_code
775 INTO x_param_rec.source_system_code
776 FROM fem_source_systems_b
777 WHERE source_system_display_code = G_PFT;
778
779 EXCEPTION
780 WHEN OTHERS THEN
781 FEM_ENGINES_PKG.User_Message (
782 p_app_name => G_PFT
783 ,p_msg_name => G_ENG_INVALID_OBJ_ERR
784 ,p_token1 => 'OBJECT_ID'
785 ,p_value1 => x_param_rec.obj_id);
786
787 RAISE e_eng_master_prep_error;
788 END;
789
790 END IF;
791
792 -- Log all Request Record Parameters if we have low level debugging
793 IF ( FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) ) THEN
794
795 FEM_ENGINES_PKG.Tech_Message (
796 p_severity => G_LOG_LEVEL_1
797 ,p_module => G_BLOCK||'.'||l_api_name||'.x_param_rec'
798 ,p_msg_text =>
799 ' dataset_grp_obj_def_id='||x_param_rec.dataset_io_obj_def_id||
800 ' dataset_grp_obj_id='||x_param_rec.dataset_grp_obj_id||
801 ' effective_date='||
802 FND_DATE.date_to_chardate(x_param_rec.effective_date)||
803 ' ledger_id='||x_param_rec.ledger_id||
804 ' local_vs_combo_id='||x_param_rec.local_vs_combo_id||
805 ' login_id='||x_param_rec.login_id||
806 ' output_cal_period_id='||x_param_rec.output_cal_period_id||
807 ' output_dataset_code='||x_param_rec.output_dataset_code||
808 ' pgm_app_id='||x_param_rec.pgm_app_id||
809 ' pgm_id='||x_param_rec.pgm_id||
810 ' resp_id='||x_param_rec.resp_id||
811 ' request_id='||x_param_rec.request_id||
812 ' obj_type_code='||x_param_rec.obj_type_code||
813 ' ruleset_obj_def_id='||x_param_rec.crnt_proc_child_obj_defn_id||
814 ' ruleset_obj_id='||x_param_rec.obj_id||
815 ' source_system_code='||x_param_rec.source_system_code||
816 ' submit_obj_id='||x_param_rec.obj_id||
817 ' submit_obj_type_code='||x_param_rec.obj_type_code||
818 ' user_id='||x_param_rec.user_id
819 );
820
821 END IF;
822
823 FEM_ENGINES_PKG.Tech_Message ( p_severity => G_LOG_LEVEL_2
824 ,p_module => G_BLOCK||'.'||l_api_name
825 ,p_msg_text => 'END');
826
827 EXCEPTION
828 WHEN e_eng_master_prep_error THEN
829
830 FEM_ENGINES_PKG.Tech_Message (
831 p_severity => g_log_level_5
832 ,p_module => G_BLOCK||'.'||l_api_name
833 ,p_msg_text => 'Engine Master Preperation Exception');
834 RAISE e_pc_engine_error;
835
836 END Eng_Master_Prep;
837
838 /*============================================================================+
839 | PROCEDURE
840 | Register_Process_Request
841 |
842 | DESCRIPTION
843 | Registers the request for the object in processing locks tables.
844 |
845 | SCOPE - PRIVATE
846 |
847 +============================================================================*/
848
849 PROCEDURE Register_Process_Request ( p_param_rec IN param_record )
850
851 IS
852
853 l_api_name CONSTANT VARCHAR2(30) := 'Register_Process_Request';
854
855 l_return_status VARCHAR2(1);
856 l_msg_count NUMBER;
857 l_msg_data VARCHAR2(240);
858
859 e_pl_register_request_error EXCEPTION;
860
861 BEGIN
862
866
863 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
864 ,p_module => G_BLOCK||'.'||l_api_name
865 ,p_msg_text => 'BEGIN');
867 SAVEPOINT register_request_pub;
868
869 -- Call the FEM_PL_PKG.Register_Request API procedure to register
870 -- the concurrent request in FEM_PL_REQUESTS.
871 FEM_PL_PKG.Register_Request(
872 p_api_version => G_CALLING_API_VERSION
873 ,p_commit => FND_API.G_FALSE
874 ,p_cal_period_id => p_param_rec.output_cal_period_id
875 ,p_ledger_id => p_param_rec.ledger_id
876 ,p_dataset_io_obj_def_id => p_param_rec.dataset_io_obj_def_id
877 ,p_source_system_code => p_param_rec.source_system_code
878 ,p_effective_date => p_param_rec.effective_date
879 ,p_output_dataset_code => p_param_rec.output_dataset_code
880 ,p_rule_set_obj_def_id => p_param_rec.crnt_proc_child_obj_defn_id
881 ,p_request_id => p_param_rec.request_id
882 ,p_user_id => p_param_rec.user_id
883 ,p_last_update_login => p_param_rec.login_id
884 ,p_program_id => p_param_rec.pgm_id
885 ,p_program_login_id => p_param_rec.login_id
886 ,p_program_application_id => p_param_rec.pgm_app_id
887 ,p_exec_mode_code => NULL
888 ,p_dimension_id => NULL
889 ,p_table_name => NULL
890 ,x_msg_count => l_msg_count
891 ,x_msg_data => l_msg_data
892 ,x_return_status => l_return_status);
893
894 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
895 Get_Put_Messages ( p_msg_count => l_msg_count
896 ,p_msg_data => l_msg_data);
897
898 RAISE e_pl_register_request_error;
899 END IF;
900
901 COMMIT;
902
903 FEM_ENGINES_PKG.Tech_Message (
904 p_severity => g_log_level_1
905 ,p_module => G_BLOCK||'.'||l_api_name
906 ,p_msg_text => 'Request id is '||p_param_rec.request_id);
907
908 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
909 ,p_module => G_BLOCK||'.'||l_api_name
910 ,p_msg_text => 'END');
911
912 EXCEPTION
913 WHEN e_pl_register_request_error THEN
914
915 ROLLBACK TO register_request_pub;
916
917 FEM_ENGINES_PKG.Tech_Message (
918 p_severity => g_log_level_5
919 ,p_module => G_BLOCK||'.'||l_api_name
920 ,p_msg_text => 'Register Request Exception');
921
922 FEM_ENGINES_PKG.User_Message (
923 p_app_name => G_PFT
924 ,p_msg_name => G_PL_REG_REQUEST_ERR
925 ,p_token1 => 'REQUEST_ID'
926 ,p_value1 => p_param_rec.request_id);
927
928 RAISE e_pc_engine_error;
929
930 WHEN OTHERS THEN
931
932 ROLLBACK TO register_request_pub;
933
934 FEM_ENGINES_PKG.User_Message (
935 p_app_name => G_PFT
936 ,p_msg_name => G_PL_REG_REQUEST_ERR
937 ,p_token1 => 'REQUEST_ID'
938 ,p_value1 => p_param_rec.request_id);
939
940 RAISE e_pc_engine_error;
941
942 END Register_Process_Request;
943
944
945 /*============================================================================+
946 | PROCEDURE
947 | Register_Object_Definition
948 |
949 | DESCRIPTION
950 | Registers the specified object definition in FEM_PL_OBJECT_DEFS,
951 | thus obtaining an object definition lock.
952 |
953 | SCOPE - PRIVATE
954 |
955 +============================================================================*/
956
957 PROCEDURE Register_Object_Definition ( p_param_rec IN param_record
958 ,p_object_id IN NUMBER
959 ,p_obj_def_id IN NUMBER)
960 IS
961
962 l_api_name CONSTANT VARCHAR2(30) := 'Register_Object_Definition';
963
964 l_return_status VARCHAR2(1);
965 l_msg_count NUMBER;
966 l_msg_data VARCHAR2(240);
967
968 e_register_obj_def_error EXCEPTION;
969
970 BEGIN
971
972 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
973 ,p_module => G_BLOCK||'.'||l_api_name
974 ,p_msg_text => 'BEGIN');
975
976 -- Call the FEM_PL_PKG.Register_Object_Def API procedure to register
977 -- the specified object definition in FEM_PL_OBJECT_DEFS, thus obtaining
978 -- an object definition lock.
979 FEM_PL_PKG.Register_Object_Def (
980 p_api_version => 1.0
981 ,p_commit => FND_API.G_FALSE
982 ,p_request_id => p_param_rec.request_id
983 ,p_object_id => p_object_id
984 ,p_object_definition_id => p_obj_def_id
985 ,p_user_id => p_param_rec.user_id
986 ,p_last_update_login => p_param_rec.login_id
987 ,x_msg_count => l_msg_count
988 ,x_msg_data => l_msg_data
989 ,x_return_status => l_return_status);
990
991 -- Object Definition Lock exists
992 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
993
997 END IF;
994 Get_Put_Messages ( p_msg_count => l_msg_count
995 ,p_msg_data => l_msg_data);
996 RAISE e_register_obj_def_error;
998
999 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
1000 ,p_module => G_BLOCK||'.'||l_api_name
1001 ,p_msg_text => 'END');
1002
1003 EXCEPTION
1004 WHEN e_register_obj_def_error THEN
1005 FEM_ENGINES_PKG.Tech_Message (
1006 p_severity => g_log_level_5
1007 ,p_module => G_BLOCK||'.'||l_api_name
1008 ,p_msg_text => 'Register Object Definition Exception');
1009
1010 FEM_ENGINES_PKG.User_Message (
1011 p_app_name => G_PFT
1012 ,p_msg_name => G_PL_OBJ_EXECLOCK_EXISTS_ERR
1013 ,p_token1 => 'REQUEST_ID'
1014 ,p_value1 => p_param_rec.request_id);
1015
1016 RAISE e_pc_engine_error;
1017
1018 END Register_Object_Definition;
1019
1020 /*============================================================================+
1021 | PROCEDURE
1022 | Register_Obj_Exe_Step
1023 |
1024 | DESCRIPTION
1025 | Registers the current step of execution in fem_pl_obj_steps
1026 |
1027 | SCOPE - PRIVATE
1028 |
1029 +============================================================================*/
1030 PROCEDURE Register_Obj_Exe_Step(p_param_rec IN param_record
1031 ,p_exe_step IN VARCHAR2
1032 ,p_exe_status_code IN VARCHAR2)
1033 IS
1034
1035 l_api_name CONSTANT VARCHAR2(30) := 'Register_Obj_Exe_Step';
1036
1037 l_return_status VARCHAR2(1);
1038 l_msg_count NUMBER;
1039 l_msg_data VARCHAR2(240);
1040
1041 e_register_obj_exe_step_error EXCEPTION;
1042
1043 BEGIN
1044
1045 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
1046 ,p_module => G_BLOCK||'.'||l_api_name
1047 ,p_msg_text => 'BEGIN');
1048
1049 -- Call the FEM_PL_PKG.Register_Object_Def API procedure to register step
1050 -- in fem_pl_obj_steps.
1051 FEM_PL_PKG.Register_Obj_Exec_Step (
1052 p_api_version => 1.0
1053 ,p_commit => FND_API.G_FALSE
1054 ,p_request_id => p_param_rec.request_id
1055 ,p_object_id => p_param_rec.crnt_proc_child_obj_id
1056 ,p_exec_step => p_exe_step
1057 ,p_exec_status_code => p_exe_status_code
1058 ,p_user_id => p_param_rec.user_id
1059 ,p_last_update_login => p_param_rec.login_id
1060 ,x_msg_count => l_msg_count
1061 ,x_msg_data => l_msg_data
1062 ,x_return_status => l_return_status);
1063
1064 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1065
1066 Get_Put_Messages ( p_msg_count => l_msg_count
1067 ,p_msg_data => l_msg_data);
1068 RAISE e_register_obj_exe_step_error;
1069 END IF;
1070
1071 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
1072 ,p_module => G_BLOCK||'.'||l_api_name
1073 ,p_msg_text => 'END');
1074
1075 EXCEPTION
1076 WHEN e_register_obj_exe_step_error THEN
1077 FEM_ENGINES_PKG.Tech_Message (
1078 p_severity => g_log_level_5
1079 ,p_module => G_BLOCK||'.'||l_api_name
1080 ,p_msg_text => 'Register Obj Exec Step Exception');
1081
1082 FEM_ENGINES_PKG.User_Message (
1083 p_app_name => G_PFT
1084 ,p_msg_name => G_PL_REG_EXEC_STEP_ERR
1085 ,p_token1 => 'OBJECT_ID'
1086 ,p_value1 => p_param_rec.crnt_proc_child_obj_id);
1087
1088 RAISE e_pc_engine_error;
1089
1090 WHEN OTHERS THEN
1091 FEM_ENGINES_PKG.User_Message (
1092 p_app_name => G_PFT
1093 ,p_msg_name => G_PL_REG_EXEC_STEP_ERR
1094 ,p_token1 => 'OBJECT_ID'
1095 ,p_value1 => p_param_rec.crnt_proc_child_obj_id);
1096
1097 RAISE e_pc_engine_error;
1098
1099 END Register_Obj_Exe_Step;
1100
1101 /*=============================================================================+
1102 | PROCEDURE
1103 | Preprocess_Rule_set
1104 |
1105 | DESCRIPTION
1106 | Flattens the rule set
1107 |
1108 |
1109 | SCOPE - PRIVATE
1110 |
1111 +============================================================================*/
1112 PROCEDURE Preprocess_Rule_Set ( p_param_rec IN param_record )
1113
1114 IS
1115
1116 l_api_name CONSTANT VARCHAR2(30) := 'Preprocess_Rule_Set';
1117
1118 l_return_status VARCHAR2(1);
1119 l_msg_count NUMBER;
1120 l_msg_data VARCHAR2(240);
1121 e_pl_preprocess_rule_set_err EXCEPTION;
1122
1123 BEGIN
1124
1125 FEM_ENGINES_PKG.Tech_Message( p_severity => g_log_level_2
1126 ,p_module => G_BLOCK||'.'||l_api_name
1127 ,p_msg_text => 'BEGIN');
1128
1129 FEM_RULE_SET_MANAGER.Fem_Preprocess_RuleSet_Pvt(
1130 p_api_version => G_CALLING_API_VERSION
1131 ,p_init_msg_list => FND_API.G_FALSE
1135 ,p_ds_io_def_id => p_param_rec.dataset_io_obj_def_id
1132 ,p_commit => FND_API.G_TRUE
1133 ,p_encoded => FND_API.G_TRUE
1134 ,p_orig_ruleset_object_id => p_param_rec.obj_id
1136 ,p_rule_effective_date => p_param_rec.effective_date_varchar
1137 ,p_output_period_id => p_param_rec.output_cal_period_id
1138 ,p_ledger_id => p_param_rec.ledger_id
1139 ,p_continue_process_on_err_flg => p_param_rec.continue_process_on_err_flg
1140 ,p_execution_mode => 'E'-- Engine Execution Mode
1141 ,x_return_status => l_return_status
1142 ,x_msg_count => l_msg_count
1143 ,x_msg_data => l_msg_data);
1144
1145 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1146 Get_Put_Messages ( p_msg_count => l_msg_count
1147 ,p_msg_data => l_msg_data);
1148
1149 RAISE e_pl_preprocess_rule_set_err ;
1150
1151 END IF;
1152
1153 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
1154 ,p_module => G_BLOCK||'.'||l_api_name
1155 ,p_msg_text => 'END');
1156
1157 EXCEPTION
1158 WHEN e_pl_preprocess_rule_set_err THEN
1159 FEM_ENGINES_PKG.Tech_Message (
1160 p_severity => g_log_level_5
1161 ,p_module => G_BLOCK||'.'||l_api_name
1162 ,p_msg_text => 'Preprocess Rule Set Exception' );
1163
1164 FEM_ENGINES_PKG.User_Message (
1165 p_app_name => G_PFT
1166 ,p_msg_name => G_ENG_PRE_PROC_RS_ERR
1167 ,p_token1 => 'RULE_SET_OBJ_ID'
1168 ,p_value1 => p_param_rec.obj_id);
1169
1170 RAISE e_pc_engine_error;
1171
1172 WHEN OTHERS THEN
1173 FEM_ENGINES_PKG.User_Message (
1174 p_app_name => G_PFT
1175 ,p_msg_name => G_ENG_PRE_PROC_RS_ERR
1176 ,p_token1 => 'RULE_SET_OBJ_ID'
1177 ,p_value1 => p_param_rec.obj_id);
1178
1179 RAISE e_pc_engine_error;
1180
1181 END Preprocess_Rule_Set;
1182
1183 /*=============================================================================+
1184 | PROCEDURE
1185 | PROCESS SINGLE RULE
1186 |
1187 | DESCRIPTION
1188 | Process the Single Account consolidation Rule
1189 |
1190 |
1191 | SCOPE - PRIVATE
1192 |
1193 +============================================================================*/
1194
1195 PROCEDURE Process_Single_Rule ( p_param_rec IN OUT NOCOPY param_record)
1196
1197 IS
1198
1199 l_api_name CONSTANT VARCHAR2(30) := 'Process_Single_Rule';
1200
1201 l_process_table VARCHAR2(30);
1202 l_err_code NUMBER := 0;
1203 l_err_msg VARCHAR2(255);
1204 l_prev_req_id NUMBER;
1205 l_exec_state VARCHAR2(30);
1206 l_reuse_slices VARCHAR2(10);
1207 l_msg_count NUMBER;
1208 l_msg_data VARCHAR2(200);
1209 l_return_status VARCHAR2(50):= NULL;
1210 l_ds_where_clause LONG := NULL;
1211 l_region_counting_flag VARCHAR2(1);
1212 l_proft_percentile_flag VARCHAR2(1);
1213 l_value_index_flag VARCHAR2(1);
1214 l_prospect_ident_flag VARCHAR2(1);
1215 l_customer_level NUMBER;
1216 l_output_column VARCHAR2(30);
1217 l_value_index_formula_id NUMBER;
1218 l_region_percent NUMBER;
1219 l_profit_percentile NUMBER;
1220
1221 TYPE v_percentile_type IS TABLE OF
1222 fem_customer_profit.profit_percentile%TYPE;
1223 v_percentile v_percentile_type;
1224
1225 e_process_single_rule_error EXCEPTION;
1226 e_register_rule_error EXCEPTION;
1227
1228 BEGIN
1229 -- Initialize the return status to SUCCESS
1230 p_param_rec.return_status := FND_API.G_RET_STS_SUCCESS;
1231
1232 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
1233 ,p_module => G_BLOCK||'.'||l_api_name
1234 ,p_msg_text => 'BEGIN');
1235
1236 FEM_ENGINES_PKG.Tech_Message (
1237 p_severity => g_log_level_2
1238 ,p_module => G_BLOCK||'.'||l_api_name
1239 ,p_msg_text => 'Validate the Rule Definition');
1240
1241 FEM_RULE_SET_MANAGER.Validate_Rule_Public(
1242 x_Err_Code => l_err_code
1243 ,x_Err_Msg => l_err_msg
1244 ,p_Rule_Object_ID => p_param_rec.crnt_proc_child_obj_id
1245 ,p_DS_IO_Def_ID => p_param_rec.dataset_io_obj_def_id
1246 ,p_Rule_Effective_Date => p_param_rec.effective_date_varchar
1247 ,p_Reference_Period_ID => p_param_rec.output_cal_period_id
1248 ,p_Ledger_ID => p_param_rec.ledger_id);
1249
1250 -- Unexpected error
1251 IF (l_err_code <> 0) THEN
1252 FEM_ENGINES_PKG.User_Message (p_app_name => G_PFT
1253 ,p_msg_name => l_err_msg);
1254
1255 RAISE e_process_single_rule_error;
1256
1257 END IF;
1258
1259 FEM_ENGINES_PKG.Tech_Message (
1260 p_severity => g_log_level_1
1261 ,p_module => G_BLOCK||'.'||l_api_name
1262 ,p_msg_text => 'PROCESSING OBJECT ID: '
1263 ||p_param_rec.crnt_proc_child_obj_id
1264 ||'AND PROCESSING OBJECT DEFINITION ID: '
1265 ||p_param_rec.crnt_proc_child_obj_defn_id);
1266
1267 FEM_ENGINES_PKG.Tech_Message (
1268 p_severity => g_log_level_3
1269 ,p_module => G_BLOCK||'.'||l_api_name
1270 ,p_msg_text => 'Getting Profit Calculation Rule Details');
1271
1272
1273 BEGIN
1274 -- get the details of the rule
1275 SELECT value_index_formula_id,
1276 condition_obj_id,
1277 region_counting_flag,
1278 proft_percentile_flag,
1279 value_index_flag,
1280 prospect_ident_flag,
1281 customer_level,
1282 output_column
1283 INTO l_value_index_formula_id,
1284 p_param_rec.cond_obj_id,
1285 l_region_counting_flag,
1286 l_proft_percentile_flag,
1287 l_value_index_flag,
1288 l_prospect_ident_flag,
1289 l_customer_level,
1290 l_output_column
1291 FROM pft_pprof_calc_rules
1292 WHERE pprof_calc_obj_def_id = p_param_rec.crnt_proc_child_obj_defn_id;
1293
1294 EXCEPTION
1295 WHEN no_data_found THEN
1296 FEM_ENGINES_PKG.User_Message (
1297 p_app_name => G_PFT
1298 ,p_msg_name => G_ENG_INVALID_OBJ_DEFN_ERR
1299 ,p_token1 => 'OBJECT_ID'
1300 ,p_value1 => p_param_rec.crnt_proc_child_obj_id
1301 ,p_token2 => 'EFFECTIVE_DATE'
1302 ,p_value2 => p_param_rec.effective_date);
1303
1304 RAISE e_process_single_rule_error;
1305
1306 END;
1307
1308 -- Call the FEM_PL_PKG.Register_Object_Execution API procedure
1309 -- to register the rollup object execution in FEM_PL_OBJECT_EXECUTIONS,
1310 -- thus obtaining an execution lock.
1311 BEGIN
1312 SAVEPOINT register_rule_pub;
1313
1314 FEM_PL_PKG.Register_Object_Execution(
1315 p_api_version => G_CALLING_API_VERSION
1316 ,p_commit => FND_API.G_TRUE
1317 ,p_request_id => p_param_rec.request_id
1318 ,p_object_id => p_param_rec.crnt_proc_child_obj_id
1319 ,p_exec_object_definition_id => p_param_rec.crnt_proc_child_obj_defn_id
1320 ,p_user_id => p_param_rec.user_id
1321 ,p_last_update_login => p_param_rec.login_id
1322 ,p_exec_mode_code => NULL
1323 ,x_exec_state => l_exec_state
1324 ,x_prev_request_id => l_prev_req_id
1325 ,x_msg_count => l_msg_count
1326 ,x_msg_data => l_msg_data
1327 ,x_return_status => l_return_status);
1328
1329 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1330 Get_Put_Messages (p_msg_count => l_msg_count
1331 ,p_msg_data => l_msg_data);
1332
1333 RAISE e_register_rule_error;
1334
1335 END IF;
1336
1337 EXCEPTION
1338 WHEN e_register_rule_error THEN
1339 ROLLBACK TO register_rule_pub;
1340
1341 FEM_ENGINES_PKG.Tech_Message (
1342 p_severity => G_LOG_LEVEL_6
1343 ,p_module => G_BLOCK||'.'||l_api_name
1344 ,p_msg_text => 'Register Rule Exception');
1345
1346 FEM_ENGINES_PKG.User_Message (
1347 p_app_name => G_PFT
1348 ,p_msg_name => G_PL_OBJ_EXEC_LOCK_ERR);
1349
1350 p_param_rec.return_status := FND_API.G_RET_STS_ERROR;
1351
1352 RAISE e_process_single_rule_error;
1353 END;
1354
1355 --------------------------------------------------------------------------
1356 --call to FEM_PL_PKG.register_dependent_objdefs
1357 --------------------------------------------------------------------------
1358 FEM_ENGINES_PKG.Tech_Message( p_severity => g_log_level_3
1359 ,p_module => G_BLOCK||'.'||l_api_name
1360 ,p_msg_text => 'Register_Dependent_Objects');
1361
1362 Register_Dependent_Objects( p_param_rec => p_param_rec);
1363
1364 ---------------------------------------------------------------------------
1365 --Register the dependant object 'Dataset Group' object
1366 ---------------------------------------------------------------------------
1367 FEM_ENGINES_PKG.Tech_Message( p_severity => g_log_level_3
1368 ,p_module => G_BLOCK||'.'||l_api_name
1369 ,p_msg_text => 'Register_Dataset_Grp_Object');
1370
1374 ,p_obj_def_id => p_param_rec.dataset_io_obj_def_id );
1371 Register_Object_Definition (
1372 p_param_rec => p_param_rec
1373 ,p_object_id => p_param_rec.crnt_proc_child_obj_id
1375
1376 -- CHECKPOINT RESTART
1377 -- check executed state and jump to appropriate statement
1378 -- depending on which step was last executed successfully
1379 IF(l_exec_state = 'RESTART') THEN
1380 l_reuse_slices := 'Y';
1381 ELSE
1382 l_reuse_slices := 'N';
1383 END IF;
1384 --------------------------------------------------------------------------
1385 --Check the Calculation Options selected in the rule and call appropriate
1386 --Engines.
1387 --------------------------------------------------------------------------
1388 --Check whether Region Counting is selected
1389 IF (l_region_counting_flag = 'Y') THEN
1390 -- Region Counting should be performed only once for a period
1391 BEGIN
1392 SELECT COUNT (region_pct_total_cust)
1393 INTO l_region_percent
1394 FROM fem_region_info
1395 WHERE cal_period_id = p_param_rec.output_cal_period_id
1396 AND ledger_id = p_param_rec.ledger_id
1397 AND dataset_code = p_param_rec.output_dataset_code
1398 AND source_system_code = p_param_rec.source_system_code
1399 AND dimension_group_id = l_customer_level;
1400 EXCEPTION
1401 WHEN no_data_found THEN
1402 FEM_ENGINES_PKG.Tech_Message (
1403 p_severity => g_log_level_3
1404 ,p_module => G_BLOCK||'.'||l_api_name
1405 ,p_msg_text => 'Region Counting is Already
1406 Performed for the Specified Period');
1407
1408 FEM_ENGINES_PKG.User_Message (
1409 p_app_name => G_PFT
1410 ,p_msg_name => G_ENG_RGN_CNT_DONE_ERR
1411 ,p_token1 => 'CAL_PERIOD_ID'
1412 ,p_value1 => p_param_rec.output_cal_period_id);
1413
1414 END;
1415
1416 IF l_region_percent = 0 THEN
1417 -----------------------------------------------------------------------
1418 -- Call FEM_PL_PKG.Register_Table()
1419 -----------------------------------------------------------------------
1420 FEM_ENGINES_PKG.Tech_Message(
1421 p_severity => g_log_level_3
1422 ,p_module => G_BLOCK||'.'||l_api_name
1423 ,p_msg_text => 'Register Table:REGION_COUNTING');
1424
1425 Register_Table( p_param_rec => p_param_rec
1426 ,p_tbl_name => g_fem_region_info
1427 ,p_num_output_rows => 0
1428 ,p_stmt_type => g_insert);
1429
1430 -----------------------------------------------------------------------
1431 -- Register this step
1432 -----------------------------------------------------------------------
1433 FEM_ENGINES_PKG.Tech_Message( p_severity => g_log_level_3
1434 ,p_module => G_BLOCK||'.'||l_api_name
1435 ,p_msg_text => 'Register the step ');
1436
1437 Register_Obj_Exe_Step( p_param_rec => p_param_rec
1438 ,p_exe_step => 'RGN_CNT'
1439 ,p_exe_status_code => l_exec_state );
1440
1441 PFT_PROFCAL_RGNCNT_PUB.Process_Single_Rule (
1442 p_rule_obj_id => p_param_rec.crnt_proc_child_obj_id
1443 ,p_cal_period_id => p_param_rec.output_cal_period_id
1444 ,p_dataset_io_obj_def_id => p_param_rec.dataset_io_obj_def_id
1445 ,p_output_dataset_code => p_param_rec.output_dataset_code
1446 ,p_effective_date => p_param_rec.effective_date_varchar
1447 ,p_ledger_id => p_param_rec.ledger_id
1448 ,p_source_system_code => p_param_rec.source_system_code
1449 ,p_customer_level => l_customer_level
1450 ,p_exec_state => l_exec_state
1451 ,x_return_status => l_return_status);
1452
1453 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1454 Get_Put_Messages (p_msg_count => l_msg_count
1455 ,p_msg_data => l_msg_data);
1456
1457 FEM_ENGINES_PKG.User_Message (
1458 p_app_name => G_PFT
1459 ,p_msg_name => 'Region Counting Engine Exception');
1460
1461 RAISE e_process_single_rule_error;
1462 END IF;
1463
1464 p_param_rec.return_status := FND_API.G_RET_STS_SUCCESS;
1465
1466 ELSE
1467 FEM_ENGINES_PKG.Tech_Message (
1468 p_severity => g_log_level_3
1469 ,p_module => G_BLOCK||'.'||l_api_name
1470 ,p_msg_text => 'Region Counting is Already
1471 Performed for the Specified Period');
1472
1473 FEM_ENGINES_PKG.User_Message (
1474 p_app_name => G_PFT
1475 ,p_msg_name => G_ENG_RGN_CNT_DONE_ERR
1476 ,p_token1 => 'CAL_PERIOD_ID'
1477 ,p_value1 => p_param_rec.output_cal_period_id);
1478
1479 END IF;
1480 END IF;
1481
1482 --Check whether Customer Profit Percentile is selected
1483 IF (l_proft_percentile_flag = 'Y') THEN
1487 INTO l_profit_percentile
1484 -- Profit Percentile should be performed only once for a period
1485 BEGIN
1486 SELECT COUNT (profit_percentile)
1488 FROM fem_customer_profit
1489 WHERE cal_period_id = p_param_rec.output_cal_period_id
1490 AND ledger_id = p_param_rec.ledger_id
1491 AND dataset_code = p_param_rec.output_dataset_code
1492 AND source_system_code = p_param_rec.source_system_code
1493 AND data_aggregation_type_code = 'CUSTOMER_AGGREGATION';
1494
1495 EXCEPTION
1496 WHEN no_data_found THEN
1497 FEM_ENGINES_PKG.Tech_Message (
1498 p_severity => g_log_level_3
1499 ,p_module => G_BLOCK||'.'||l_api_name
1500 ,p_msg_text => 'Profit Percentile is Already
1501 Performed for the Specified Period');
1502
1503 FEM_ENGINES_PKG.User_Message (
1504 p_app_name => G_PFT
1505 ,p_msg_name => G_ENG_PPTILE_DONE_ERR
1506 ,p_token1 => 'CAL_PERIOD_ID'
1507 ,p_value1 => p_param_rec.output_cal_period_id);
1508
1509 END;
1510
1511 -- Start of Bug # 4906275
1512 IF l_profit_percentile <> 0 THEN
1513 v_percentile := v_percentile_type();
1514 BEGIN
1515 SELECT profit_percentile
1516 BULK COLLECT INTO v_percentile
1517 FROM fem_customer_profit
1518 WHERE cal_period_id = p_param_rec.output_cal_period_id
1519 AND ledger_id = p_param_rec.ledger_id
1520 AND dataset_code = p_param_rec.output_dataset_code
1521 AND source_system_code = p_param_rec.source_system_code
1522 AND data_aggregation_type_code = 'CUSTOMER_AGGREGATION';
1523
1524 FOR i IN 1..v_percentile.COUNT LOOP
1525 IF v_percentile(i) = 0 THEN
1526 l_profit_percentile := 0;
1527 END IF;
1528 END LOOP;
1529 END;
1530
1531 END IF;
1532 -- End of Bug # 4906275
1533
1534 IF l_profit_percentile = 0 THEN
1535 -----------------------------------------------------------------------
1536 -- Call FEM_PL_PKG.Register_Table()
1537 -----------------------------------------------------------------------
1538 FEM_ENGINES_PKG.Tech_Message( p_severity => g_log_level_3
1539 ,p_module => G_BLOCK||'.'||l_api_name
1540 ,p_msg_text => 'Register table:CUST_PPTILE');
1541
1542 Register_Table( p_param_rec => p_param_rec
1543 ,p_tbl_name => g_fem_customer_profit
1544 ,p_num_output_rows => 0
1545 ,p_stmt_type => g_update);
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 => 'Register updt colmn:CUST_PPTILE');
1551
1552 Register_Updated_Column( p_param_rec => p_param_rec
1553 ,p_table_name => g_fem_customer_profit
1554 ,p_statement_type => g_update
1555 ,p_column_name => 'PROFIT_PERCENTILE');
1556
1557 Register_Updated_Column( p_param_rec => p_param_rec
1558 ,p_table_name => g_fem_customer_profit
1559 ,p_statement_type => g_update
1560 ,p_column_name => 'PROFIT_DECILE');
1561
1562 -----------------------------------------------------------------------
1563 -- Register this step
1564 -----------------------------------------------------------------------
1565 FEM_ENGINES_PKG.Tech_Message(
1566 p_severity => g_log_level_3
1567 ,p_module => G_BLOCK||'.'||l_api_name
1568 ,p_msg_text => 'Register the step: CUST_PPTILE');
1569
1570 Register_Obj_Exe_Step( p_param_rec => p_param_rec
1571 ,p_exe_step => 'CUST_PPTILE'
1572 ,p_exe_status_code => l_exec_state );
1573
1574 PFT_PROFCAL_CUST_PPTILE_PUB.Process_Single_Rule (
1575 p_rule_obj_id => p_param_rec.crnt_proc_child_obj_id
1576 ,p_cal_period_id => p_param_rec.output_cal_period_id
1577 ,p_dataset_io_obj_def_id => p_param_rec.dataset_io_obj_def_id
1578 ,p_output_dataset_code => p_param_rec.output_dataset_code
1579 ,p_effective_date => p_param_rec.effective_date_varchar
1580 ,p_ledger_id => p_param_rec.ledger_id
1581 ,p_source_system_code => p_param_rec.source_system_code
1582 ,p_customer_level => l_customer_level
1583 ,p_exec_state => l_exec_state
1584 ,x_return_status => l_return_status);
1585
1586 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1587 Get_Put_Messages (p_msg_count => l_msg_count
1588 ,p_msg_data => l_msg_data);
1589
1590 FEM_ENGINES_PKG.User_Message (
1591 p_app_name => G_PFT
1592 ,p_msg_name => 'Profit Percentile Engine Exception');
1593
1594 RAISE e_process_single_rule_error;
1598 ELSE
1595 END IF;
1596
1597 p_param_rec.return_status := FND_API.G_RET_STS_SUCCESS;
1599 FEM_ENGINES_PKG.Tech_Message (
1600 p_severity => g_log_level_3
1601 ,p_module => G_BLOCK||'.'||l_api_name
1602 ,p_msg_text => 'Profit Percentile is Already
1603 Performed for the Specified Period');
1604
1605 FEM_ENGINES_PKG.User_Message (
1606 p_app_name => G_PFT
1607 ,p_msg_name => G_ENG_PPTILE_DONE_ERR
1608 ,p_token1 => 'CAL_PERIOD_ID'
1609 ,p_value1 => p_param_rec.output_cal_period_id);
1610
1611 END IF;
1612 END IF;
1613
1614 --Check whether Caluclate Value Index is selected
1615 IF (l_value_index_flag = 'Y') THEN
1616 --SSHANMUG .. A special case which needs to be handled later
1617
1618 /*IF(l_proft_percentile_flag = 'N' AND l_region_counting_flag = 'N') THEN
1619 -- WIP
1620 -- Call Register_Chaining();
1621 --
1622 END IF;*/
1623
1624 -----------------------------------------------------------------------
1625 -- Call FEM_PL_PKG.Register_Table()
1626 -----------------------------------------------------------------------
1627 FEM_ENGINES_PKG.Tech_Message( p_severity => g_log_level_3
1628 ,p_module => G_BLOCK||'.'||l_api_name
1629 ,p_msg_text => 'Register table:Value Index');
1630
1631 Register_Table( p_param_rec => p_param_rec
1632 ,p_tbl_name => g_fem_customer_profit
1633 ,p_num_output_rows => 0
1634 ,p_stmt_type => g_update);
1635
1636 -----------------------------------------------------------------------
1637 -- Register this step
1638 -----------------------------------------------------------------------
1639 FEM_ENGINES_PKG.Tech_Message(
1640 p_severity => g_log_level_3
1641 ,p_module => G_BLOCK||'.'||l_api_name
1642 ,p_msg_text => 'Register the step:VALUE_INDEX');
1643
1644 Register_Obj_Exe_Step( p_param_rec => p_param_rec
1645 ,p_exe_step => 'VAL_IDX'
1646 ,p_exe_status_code => l_exec_state );
1647
1648 PFT_PROFCAL_VALIDX_PUB.Process_Single_Rule (
1649 p_rule_obj_id => p_param_rec.crnt_proc_child_obj_id
1650 ,p_cal_period_id => p_param_rec.output_cal_period_id
1651 ,p_dataset_io_obj_def_id => p_param_rec.dataset_io_obj_def_id
1652 ,p_output_dataset_code => p_param_rec.output_dataset_code
1653 ,p_effective_date => p_param_rec.effective_date_varchar
1654 ,p_ledger_id => p_param_rec.ledger_id
1655 ,p_source_system_code => p_param_rec.source_system_code
1656 ,p_value_index_formula_id => l_value_index_formula_id
1657 ,p_rule_obj_def_id => p_param_rec.crnt_proc_child_obj_defn_id
1658 ,p_region_counting_flag => l_region_counting_flag
1659 ,p_proft_percentile_flag => l_proft_percentile_flag
1660 ,p_customer_level => l_customer_level
1661 ,p_cond_obj_id => p_param_rec.cond_obj_id
1662 ,p_output_column => l_output_column
1663 ,p_exec_state => l_exec_state
1664 ,x_return_status => l_return_status);
1665
1666 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1667 Get_Put_Messages (p_msg_count => l_msg_count
1668 ,p_msg_data => l_msg_data);
1669
1670 FEM_ENGINES_PKG.User_Message (
1671 p_app_name => G_PFT
1672 ,p_msg_name => 'Value Index Engine Exception');
1673
1674 RAISE e_process_single_rule_error;
1675
1676 END IF;
1677
1678 p_param_rec.return_status := FND_API.G_RET_STS_SUCCESS;
1679
1680 END IF;
1681
1682 --Check whether Prospect Identification is selected
1683 /* IF (l_prospect_ident_flag = 'Y') THEN
1684 -----------------------------------------------------------------------
1685 -- Call FEM_PL_PKG.Register_Table()
1686 -----------------------------------------------------------------------
1687 FEM_ENGINES_PKG.Tech_Message(
1688 p_severity => g_log_level_3
1689 ,p_module => G_BLOCK||'.'||l_api_name
1690 ,p_msg_text => 'Register table:Prospect Identification');
1691
1692 Register_Table( p_param_rec => p_param_rec
1693 ,p_tbl_name => g_fem_customers_attr
1694 ,p_num_output_rows => 0
1695 ,p_stmt_type => g_update);
1696
1697 Register_Updated_Column( p_param_rec => p_param_rec
1698 ,p_table_name => g_fem_customers_attr
1699 ,p_statement_type => g_update
1700 ,p_column_name => 'VARCHAR_ASSIGN_VALUE');
1701
1702 -----------------------------------------------------------------------
1703 -- Register this step
1704 -----------------------------------------------------------------------
1705 FEM_ENGINES_PKG.Tech_Message(
1706 p_severity => g_log_level_3
1710 Register_Obj_Exe_Step( p_param_rec => p_param_rec
1707 ,p_module => G_BLOCK||'.'||l_api_name
1708 ,p_msg_text => 'Register the step:PROSP_IDENT');
1709
1711 ,p_exe_step => 'PROSP_IDENT'
1712 ,p_exe_status_code => l_exec_state );
1713
1714 PFT_PROFCAL_PROSP_IDENT_PUB.Process_Single_Rule (
1715 p_rule_obj_id => p_param_rec.crnt_proc_child_obj_id
1716 ,p_cal_period_id => p_param_rec.output_cal_period_id
1717 ,p_dataset_io_obj_def_id => p_param_rec.dataset_io_obj_def_id
1718 ,p_output_dataset_code => p_param_rec.output_dataset_code
1719 ,p_effective_date => p_param_rec.effective_date_varchar
1720 ,p_ledger_id => p_param_rec.ledger_id
1721 ,p_source_system_code => p_param_rec.source_system_code
1722 ,p_exec_state => l_exec_state
1723 ,x_return_status => l_return_status);
1724
1725 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1726 Get_Put_Messages (p_msg_count => l_msg_count
1727 ,p_msg_data => l_msg_data);
1728
1729 FEM_ENGINES_PKG.User_Message (
1730 p_app_name => G_PFT
1731 ,p_msg_name => 'Prospect Identification Engine Exception');
1732
1733 RAISE e_process_single_rule_error;
1734
1735 END IF;
1736
1737 END IF; */
1738 EXCEPTION
1739 WHEN e_process_single_rule_error THEN
1740 FEM_ENGINES_PKG.Tech_Message (
1741 p_severity => g_log_level_5
1742 ,p_module => G_BLOCK||'.'||l_api_name
1743 ,p_msg_text => 'Process Single Rule Exception');
1744
1745 -- FEM_ENGINES_PKG.User_Message (p_app_name => G_PFT
1746 -- ,p_msg_name => G_ENG_SINGLE_RULE_ERR);
1747
1748 p_param_rec.return_status := FND_API.G_RET_STS_ERROR;
1749
1750 WHEN OTHERS THEN
1751
1752 FEM_ENGINES_PKG.User_Message (p_app_name => G_PFT
1753 ,p_msg_name => G_ENG_SINGLE_RULE_ERR);
1754
1755 p_param_rec.return_status := FND_API.G_RET_STS_ERROR;
1756
1757 END Process_Single_Rule;
1758
1759 /*===========================================================================+==
1760 | FUNCTION
1761 | is_ruleset_flattened
1762 |
1763 | DESCRIPTION
1764 | To check whether the rule set is flattened or not
1765 |
1766 | SCOPE - PRIVATE
1767 |
1768 +============================================================================*/
1769
1770 FUNCTION is_rule_set_flattened( p_request_id IN NUMBER,
1771 p_rule_set_obj_id IN NUMBER)
1772
1773 RETURN NUMBER IS
1774
1775 l_api_name CONSTANT VARCHAR2(30) := 'is_rule_set_flattened';
1776 l_count NUMBER;
1777
1778 BEGIN
1779 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
1780 ,p_module => G_BLOCK||'.'||l_api_name
1781 ,p_msg_text => 'BEGIN');
1782 SELECT COUNT(*)
1783 INTO l_count
1784 FROM fem_ruleset_process_data p,
1785 fem_pl_object_executions x
1786 WHERE p.request_id = p_request_id AND
1787 p.request_id = x.request_id(+) AND
1788 p.rule_set_obj_id = p_rule_set_obj_id
1789 ORDER BY p.engine_execution_sequence;
1790
1791 IF (l_count = 0) THEN
1792 RETURN -1;
1793 END IF;
1794
1795 RETURN 0;
1796 FEM_ENGINES_PKG.Tech_Message ( p_severity => G_LOG_LEVEL_2
1797 ,p_module => G_BLOCK||'.'||l_api_name
1798 ,p_msg_text => 'END');
1799 EXCEPTION
1800 WHEN no_data_found THEN
1801 RETURN -1;
1802 WHEN OTHERS THEN
1803 RAISE;
1804
1805 END is_rule_set_flattened;
1806
1807 /*============================================================================+
1808 | PROCEDURE
1809 | Eng_Master_Post_Proc
1810 |
1811 | DESCRIPTION
1812 | Updates the status of the request and object execution in the
1813 | processing locks tables.
1814 |
1815 | SCOPE - PRIVATE
1816 |
1817 +============================================================================*/
1818
1819 PROCEDURE Eng_Master_Post_Proc ( p_param_rec IN param_record
1820 ,p_exec_status_code IN VARCHAR2)
1821 IS
1822
1823 l_api_name CONSTANT VARCHAR2(30) := 'Eng_Master_Post_Proc';
1824
1825 l_return_status VARCHAR2(1);
1826 l_msg_count NUMBER;
1827 l_msg_data VARCHAR2(240);
1828
1829 e_eng_master_post_proc_error EXCEPTION;
1830
1831
1832 BEGIN
1833
1834 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
1835 ,p_module => G_BLOCK||'.'||l_api_name
1836 ,p_msg_text => 'BEGIN');
1837
1838 --------------------------------------------------------------------------
1839 -- STEP 1: Update Object Execution Status.
1840 --------------------------------------------------------------------------
1841 FEM_ENGINES_PKG.Tech_Message (
1842 p_severity => g_log_level_1
1846 FEM_PL_PKG.Update_Obj_Exec_Status (
1843 ,p_module => G_BLOCK||'.'||l_api_name
1844 ,p_msg_text => 'Step 1: Update Object Execution Status');
1845
1847 p_api_version => 1.0
1848 ,p_commit => FND_API.G_FALSE
1849 ,p_request_id => p_param_rec.request_id
1850 ,p_object_id => p_param_rec.crnt_proc_child_obj_id
1851 ,p_exec_status_code => p_exec_status_code
1852 ,p_user_id => p_param_rec.user_id
1853 ,p_last_update_login => p_param_rec.login_id
1854 ,x_msg_count => l_msg_count
1855 ,x_msg_data => l_msg_data
1856 ,x_return_status => l_return_status);
1857
1858 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1859 Get_Put_Messages ( p_msg_count => l_msg_count
1860 ,p_msg_data => l_msg_data);
1861 RAISE e_eng_master_post_proc_error;
1862 END IF;
1863
1864 --------------------------------------------------------------------------
1865 -- STEP 2: Update Object Execution Errors.
1866 --------------------------------------------------------------------------
1867 IF (p_exec_status_code <> g_exec_status_success) THEN
1868
1869 FEM_ENGINES_PKG.Tech_Message (
1870 p_severity => g_log_level_1
1871 ,p_module => G_BLOCK||'.'||l_api_name
1872 ,p_msg_text => 'Step 2: Update Object Execution Errors');
1873
1874 FEM_PL_PKG.Update_Obj_Exec_Errors (
1875 p_api_version => 1.0
1876 ,p_commit => FND_API.G_FALSE
1877 ,p_request_id => p_param_rec.request_id
1878 ,p_object_id => p_param_rec.crnt_proc_child_obj_id
1879 ,p_errors_reported => 1 --todo: verify
1880 ,p_errors_reprocessed => 0 --todo: verify
1881 ,p_user_id => p_param_rec.user_id
1882 ,p_last_update_login => p_param_rec.login_id
1883 ,x_msg_count => l_msg_count
1884 ,x_msg_data => l_msg_data
1885 ,x_return_status => l_return_status);
1886
1887 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1888 Get_Put_Messages ( p_msg_count => l_msg_count
1889 ,p_msg_data => l_msg_data);
1890 RAISE e_eng_master_post_proc_error;
1891 END IF;
1892
1893 END IF;
1894
1895 --------------------------------------------------------------------------
1896 -- STEP 3: Update Request Status.
1897 --------------------------------------------------------------------------
1898 FEM_ENGINES_PKG.Tech_Message (
1899 p_severity => g_log_level_1
1900 ,p_module => G_BLOCK||'.'||l_api_name
1901 ,p_msg_text => 'Step 3: Update Request Status');
1902
1903 FEM_PL_PKG.Update_Request_Status (
1904 p_api_version => 1.0
1905 ,p_commit => FND_API.G_FALSE
1906 ,p_request_id => p_param_rec.request_id
1907 ,p_exec_status_code => p_exec_status_code
1908 ,p_user_id => p_param_rec.user_id
1909 ,p_last_update_login => p_param_rec.login_id
1910 ,x_msg_count => l_msg_count
1911 ,x_msg_data => l_msg_data
1912 ,x_return_status => l_return_status);
1913
1914 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1915 Get_Put_Messages ( p_msg_count => l_msg_count
1916 ,p_msg_data => l_msg_data);
1917 RAISE e_eng_master_post_proc_error;
1918 END IF;
1919
1920 COMMIT;
1921
1922 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
1923 ,p_module => G_BLOCK||'.'||l_api_name
1924 ,p_msg_text => 'END');
1925
1926 EXCEPTION
1927 WHEN e_eng_master_post_proc_error THEN
1928 FEM_ENGINES_PKG.Tech_Message (
1929 p_severity => g_log_level_5
1930 ,p_module => G_BLOCK||'.'||l_api_name
1931 ,p_msg_text => 'Engine Master Post Process Exception');
1932
1933
1934 FEM_ENGINES_PKG.User_Message (
1935 p_app_name => G_PFT
1936 ,p_msg_name => G_ENG_ENGINE_POST_PROC_ERR
1937 ,p_token1 => 'OBJECT_ID'
1938 ,p_value1 => p_param_rec.obj_id);
1939
1940 RAISE e_pc_engine_error;
1941
1942 END Eng_Master_Post_Proc;
1943
1944 /*=============================================================================+
1945 | PROCEDURE
1946 | Get_Object_Definition
1947 |
1948 | DESCRIPTION
1949 | Returns the Object Definition Id for the given Object Id for the
1950 | given effective Date
1951 |
1952 | SCOPE - PRIVATE
1953 |
1954 +============================================================================*/
1955
1956 PROCEDURE Get_Object_Definition ( p_object_type_code IN VARCHAR2
1957 ,p_object_id IN NUMBER
1958 ,p_effective_date IN DATE
1959 ,x_obj_def_id OUT NOCOPY NUMBER)
1960
1961 IS
1962
1963 l_api_name CONSTANT VARCHAR2(30) := 'Get_Object_Definition';
1964
1965 BEGIN
1966
1967 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
1968 ,p_module => G_BLOCK||'.'||l_api_name
1969 ,p_msg_text => 'BEGIN');
1970
1971 SELECT d.object_definition_id
1975 WHERE o.object_id = p_object_id
1972 INTO x_obj_def_id
1973 FROM fem_object_definition_b d
1974 ,fem_object_catalog_b o
1976 AND o.object_type_code = p_object_type_code
1977 AND d.object_id = o.object_id
1978 AND p_effective_date BETWEEN d.effective_start_date AND d.effective_end_date
1979 AND d.old_approved_copy_flag = 'N';
1980
1981 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
1982 ,p_module => G_BLOCK||'.'||l_api_name
1983 ,p_msg_text => 'END');
1984
1985 EXCEPTION
1986 WHEN no_data_found THEN
1987 FEM_ENGINES_PKG.User_Message (
1988 p_app_name => G_PFT
1989 ,p_msg_name => G_ENG_INVALID_OBJ_DEFN_ERR
1990 ,p_token1 => 'OBJECT_ID'
1991 ,p_value1 => p_object_id
1992 ,p_token2 => 'EFFECTIVE_DATE'
1993 ,p_value2 => p_effective_date);
1994
1995 RAISE e_pc_engine_error;
1996
1997 END Get_Object_Definition;
1998
1999 /*============================================================================+
2000 | PROCEDURE
2001 | Get_Put_Messages
2002 |
2003 | DESCRIPTION
2004 | To put the User messages,to be placed in common loader package.
2005 |
2006 | SCOPE - PRIVATE
2007 |
2008 +============================================================================*/
2009
2010 PROCEDURE Get_Put_Messages ( p_msg_count IN NUMBER
2011 ,p_msg_data IN VARCHAR2)
2012 IS
2013
2014 l_api_name CONSTANT VARCHAR2(30) := 'Get_Put_Messages';
2015 l_msg_count NUMBER;
2016 l_msg_data VARCHAR2(4000);
2017 l_msg_out NUMBER;
2018 l_message VARCHAR2(4000);
2019
2020 BEGIN
2021
2022 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
2023 ,p_module => G_BLOCK||'.'||l_api_name
2024 ,p_msg_text => 'msg_count='||p_msg_count);
2025
2026 l_msg_data := p_msg_data;
2027
2028 IF (p_msg_count = 1) THEN
2029
2030 FND_MESSAGE.Set_Encoded(l_msg_data);
2031 l_message := FND_MESSAGE.Get;
2032
2033 FEM_ENGINES_PKG.User_Message ( p_msg_text => l_message );
2034
2035 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
2036 ,p_module => G_BLOCK||'.'||l_api_name
2037 ,p_msg_text => 'msg_data='||l_message);
2038
2039 ELSIF (p_msg_count > 1) THEN
2040
2041 FOR i IN 1..p_msg_count LOOP
2042
2043 FND_MSG_PUB.Get ( p_msg_index => i
2044 ,p_encoded => FND_API.G_FALSE
2045 ,p_data => l_message
2046 ,p_msg_index_out => l_msg_out);
2047
2048 FEM_ENGINES_PKG.User_Message ( p_msg_text => l_message );
2049
2050 FEM_ENGINES_PKG.Tech_Message (
2051 p_severity => g_log_level_2
2052 ,p_module => G_BLOCK||'.'||l_api_name
2053 ,p_msg_text => 'msg_data='||l_message);
2054
2055 END LOOP;
2056
2057 END IF;
2058
2059 FND_MSG_PUB.Initialize;
2060
2061 END Get_Put_Messages;
2062
2063 --SSHANMUG : Added additional proc for PL Implementation
2064 /*============================================================================+
2065 | PROCEDURE
2066 | Register_dependent_objdefs
2067 |
2068 | DESCRIPTION
2069 | This procedure retrieves all objects that are dependent on the object that
2070 | is being executed, from FEM_OBJECT_DEPENDENCIES. The effective date is
2071 | used to retrieve the specific definition that will be read, for each
2072 | dependent object, and then registers the retrieved definitions. This
2073 | procedure does not validate that each dependent object has a valid
2074 | definition for the given effective date, and will not detect that a
2075 | dependent object is missing a valid definition. It is the responsibility
2076 | of the calling program to make sure that each dependent object has a
2077 | valid definition for the given effective date.
2078 |
2079 | SCOPE - PRIVATE
2080 |
2081 +============================================================================*/
2082
2083 PROCEDURE Register_Dependent_Objects( p_param_rec IN param_record )
2084 IS
2085
2086 l_api_name CONSTANT VARCHAR2(30) := 'Register_Dependent_Objects';
2087
2088 l_return_status VARCHAR2(1);
2089 l_msg_count NUMBER;
2090 l_msg_data VARCHAR2(240);
2091
2092 e_register_dep_obj_def_error EXCEPTION;
2093
2094 BEGIN
2095
2096 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
2097 ,p_module => G_BLOCK||'.'||l_api_name
2098 ,p_msg_text => 'BEGIN');
2099
2100 -- Register all the Dependent Objects for CALC
2101 FEM_PL_PKG.Register_Dependent_ObjDefs (
2102 p_api_version => G_CALLING_API_VERSION
2103 ,p_commit => FND_API.G_TRUE
2104 ,p_request_id => p_param_rec.request_id
2108 ,p_user_id => p_param_rec.user_id
2105 ,p_object_id => p_param_rec.crnt_proc_child_obj_id
2106 ,p_exec_object_definition_id => p_param_rec.crnt_proc_child_obj_defn_id
2107 ,p_effective_date => p_param_rec.effective_date
2109 ,p_last_update_login => p_param_rec.login_id
2110 ,x_msg_count => l_msg_count
2111 ,x_msg_data => l_msg_data
2112 ,x_return_status => l_return_status);
2113
2114 -- Object Definition Lock exists
2115 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2116 Get_Put_Messages ( p_msg_count => l_msg_count
2117 ,p_msg_data => l_msg_data);
2118 RAISE e_register_dep_obj_def_error;
2119 END IF;
2120
2121 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
2122 ,p_module => G_BLOCK||'.'||l_api_name
2123 ,p_msg_text => 'END');
2124
2125 EXCEPTION
2126 WHEN e_register_dep_obj_def_error THEN
2127 FEM_ENGINES_PKG.Tech_Message (
2128 p_severity => g_log_level_5
2129 ,p_module => G_BLOCK||'.'||l_api_name
2130 ,p_msg_text => 'Register Dependant Objects Exception');
2131
2132 FEM_ENGINES_PKG.User_Message (
2133 p_app_name => G_PFT
2134 ,p_msg_name => G_PL_DEP_OBJ_DEF_ERR
2135 ,p_token1 => 'OBJ_DEF_ID'
2136 ,p_value1 => p_param_rec.crnt_proc_child_obj_defn_id);
2137
2138 RAISE e_pc_engine_error;
2139
2140 WHEN OTHERS THEN
2141 FEM_ENGINES_PKG.Tech_Message (
2142 p_severity => g_log_level_5
2143 ,p_module => G_BLOCK||'.'||l_api_name
2144 ,p_msg_text => 'Register Dependant Objects Exception');
2145
2146 FEM_ENGINES_PKG.User_Message (
2147 p_app_name => G_PFT
2148 ,p_msg_name => G_PL_DEP_OBJ_DEF_ERR
2149 ,p_token1 => 'OBJ_DEF_ID'
2150 ,p_value1 => p_param_rec.crnt_proc_child_obj_defn_id);
2151
2152 RAISE e_pc_engine_error;
2153
2154 END Register_Dependent_Objects;
2155
2156 /*============================================================================+
2157 | PROCEDURE
2158 | Update_Num_Of_Input_Rows
2159 |
2160 | DESCRIPTION
2161 | This procedure logs the total number of rows used as input into
2162 | an object execution
2163 |
2164 | SCOPE - PRIVATE
2165 |
2166 +============================================================================*/
2167 PROCEDURE Update_Nbr_Of_Input_Rows( p_param_rec IN param_record
2168 ,p_num_input_rows IN NUMBER)
2169 IS
2170
2171 l_api_name CONSTANT VARCHAR2(30) := 'Update_Num_Of_Input_Rows';
2172
2173 l_return_status VARCHAR2(2);
2174 l_msg_count NUMBER;
2175 l_msg_data VARCHAR2(240);
2176
2177 e_upd_num_input_rows_error EXCEPTION;
2178
2179 BEGIN
2180
2181 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
2182 ,p_module => G_BLOCK||'.'||l_api_name
2183 ,p_msg_text => 'BEGIN');
2184
2185 -- Set the number of output rows for the output table.
2186 FEM_PL_PKG.Update_Num_Of_Input_Rows(
2187 p_api_version => 1.0
2188 ,p_commit => FND_API.G_TRUE
2189 ,p_request_id => p_param_rec.request_id
2190 ,p_object_id => p_param_rec.crnt_proc_child_obj_id
2191 ,p_num_of_input_rows => p_num_input_rows
2192 ,p_user_id => p_param_rec.user_id
2193 ,p_last_update_login => p_param_rec.login_id
2194 ,x_msg_count => l_msg_count
2195 ,x_msg_data => l_msg_data
2196 ,x_return_status => l_return_status);
2197
2198 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2199
2200 Get_Put_Messages( p_msg_count => l_msg_count
2201 ,p_msg_data => l_msg_data);
2202 RAISE e_upd_num_input_rows_error;
2203 END IF;
2204
2205 FEM_ENGINES_PKG.Tech_Message( p_severity => g_log_level_2
2206 ,p_module => G_BLOCK||'.'||l_api_name
2207 ,p_msg_text => 'END');
2208
2209 EXCEPTION
2210 WHEN e_upd_num_input_rows_error THEN
2211 FEM_ENGINES_PKG.Tech_Message (
2212 p_severity => g_log_level_5
2213 ,p_module => G_BLOCK||'.'||l_api_name
2214 ,p_msg_text => 'Update Input Rows Exception');
2215
2216 FEM_ENGINES_PKG.User_Message (
2217 p_app_name => G_PFT
2218 ,p_msg_name => G_PL_IP_UPD_ROWS_ERR);
2219
2220 RAISE e_pc_engine_error;
2221
2222 WHEN OTHERS THEN
2223 FEM_ENGINES_PKG.Tech_Message (
2224 p_severity => g_log_level_5
2225 ,p_module => G_BLOCK||'.'||l_api_name
2226 ,p_msg_text => 'Update Input Rows Exception');
2227
2228 FEM_ENGINES_PKG.User_Message (
2229 p_app_name => G_PFT
2230 ,p_msg_name => G_PL_IP_UPD_ROWS_ERR);
2231
2232 RAISE e_pc_engine_error;
2233
2234 END Update_Nbr_Of_Input_Rows;
2235
2236 /*============================================================================+
2237 | PROCEDURE
2238 | Register_Table
2239 |
2240 | DESCRIPTION
2241 | Registers the output Table in fem_pl_tables.
2242 |
2243 | SCOPE - PRIVATE
2244 |
2245 +============================================================================*/
2246
2247 PROCEDURE Register_Table( p_param_rec IN param_record
2248 ,p_tbl_name IN VARCHAR2
2249 ,p_num_output_rows IN NUMBER
2250 ,p_stmt_type IN VARCHAR2)
2251 IS
2252
2253 l_api_name CONSTANT VARCHAR2(30) := 'Register_Table';
2254
2255 l_return_status VARCHAR2(1);
2256 l_msg_count NUMBER;
2257 l_msg_data VARCHAR2(240);
2258
2259 e_register_table_error EXCEPTION;
2260
2261 BEGIN
2262
2263 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
2264 ,p_module => G_BLOCK||'.'||l_api_name
2265 ,p_msg_text => 'BEGIN');
2266
2267 -- Call the FEM_PL_PKG.Register_Table API procedure to register
2268 -- the specified output table and the statement type that will be used.
2269 FEM_PL_PKG.Register_Table(
2270 p_api_version => 1.0
2271 ,p_commit => FND_API.G_FALSE
2272 ,p_request_id => p_param_rec.request_id
2273 ,p_object_id => p_param_rec.crnt_proc_child_obj_id
2274 ,p_table_name => p_tbl_name
2275 ,p_statement_type => p_stmt_type
2276 ,p_num_of_output_rows => p_num_output_rows
2277 ,p_user_id => p_param_rec.user_id
2278 ,p_last_update_login => p_param_rec.login_id
2279 ,x_msg_count => l_msg_count
2280 ,x_msg_data => l_msg_data
2281 ,x_return_status => l_return_status);
2282
2283 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2284
2285 Get_Put_Messages ( p_msg_count => l_msg_count
2286 ,p_msg_data => l_msg_data);
2287 RAISE e_register_table_error;
2288 END IF;
2289
2290 FEM_ENGINES_PKG.Tech_Message( p_severity => g_log_level_2
2291 ,p_module => G_BLOCK||'.'||l_api_name
2292 ,p_msg_text => 'END');
2293
2294 EXCEPTION
2295 WHEN e_register_table_error THEN
2296 FEM_ENGINES_PKG.Tech_Message(
2297 p_severity => g_log_level_5
2298 ,p_module => G_BLOCK||'.'||l_api_name
2299 ,p_msg_text => 'Register Table Exception');
2300
2301 FEM_ENGINES_PKG.User_Message (
2302 p_app_name => G_PFT
2303 ,p_msg_name => G_PL_REG_TABLE_ERR
2304 ,p_token1 => 'TABLE_NAME'
2305 ,p_value1 => p_tbl_name);
2306
2307 RAISE e_pc_engine_error;
2308
2309 WHEN OTHERS THEN
2310 FEM_ENGINES_PKG.User_Message (
2311 p_app_name => G_PFT
2312 ,p_msg_name => G_PL_REG_TABLE_ERR
2313 ,p_token1 => 'TABLE_NAME'
2314 ,p_value1 => p_tbl_name);
2315
2316 RAISE e_pc_engine_error;
2317
2318 END Register_Table;
2319
2320 /*============================================================================+
2321 | PROCEDURE
2322 | Register_Updated_Column
2323 |
2324 | DESCRIPTION
2325 | This procedure is used to register a column updated during object execution
2326 |
2327 |
2328 | SCOPE - PRIVATE
2329 |
2330 +============================================================================*/
2331 PROCEDURE Register_Updated_Column( p_param_rec IN param_record
2332 ,p_table_name IN VARCHAR2
2333 ,p_statement_type IN VARCHAR2
2337 l_api_name CONSTANT VARCHAR2(30) := 'Register_Updated_Column';
2334 ,p_column_name IN VARCHAR2)
2335 IS
2336
2338
2339 l_return_status VARCHAR2(2);
2340 l_msg_count NUMBER;
2341 l_msg_data VARCHAR2(240);
2342
2343 e_reg_updated_column_error EXCEPTION;
2344
2345 BEGIN
2346
2347 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
2348 ,p_module => G_BLOCK||'.'||l_api_name
2349 ,p_msg_text => 'BEGIN');
2350
2351 -- Set the number of output rows for the output table.
2352 FEM_PL_PKG.register_updated_column(
2353 p_api_version => 1.0
2354 ,p_commit => FND_API.G_TRUE
2355 ,p_request_id => p_param_rec.request_id
2356 ,p_object_id => p_param_rec.crnt_proc_child_obj_id
2357 ,p_table_name => p_table_name
2358 ,p_statement_type => p_statement_type
2359 ,p_column_name => p_column_name
2360 ,p_user_id => p_param_rec.user_id
2361 ,p_last_update_login => p_param_rec.login_id
2362 ,x_msg_count => l_msg_count
2363 ,x_msg_data => l_msg_data
2364 ,x_return_status => l_return_status);
2365
2366 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2367
2368 Get_Put_Messages( p_msg_count => l_msg_count
2369 ,p_msg_data => l_msg_data);
2370 RAISE e_reg_updated_column_error;
2371 END IF;
2372
2373 FEM_ENGINES_PKG.Tech_Message( p_severity => g_log_level_2
2374 ,p_module => G_BLOCK||'.'||l_api_name
2375 ,p_msg_text => 'END');
2376
2377 EXCEPTION
2378 WHEN e_reg_updated_column_error THEN
2379 FEM_ENGINES_PKG.Tech_Message (
2380 p_severity => g_log_level_5
2381 ,p_module => G_BLOCK||'.'||l_api_name
2382 ,p_msg_text => 'Register_Updated_Column_Exception');
2383
2384 FEM_ENGINES_PKG.User_Message (
2385 p_app_name => G_PFT
2386 ,p_msg_name => G_PL_REG_UPD_COL_ERR
2387 ,p_token1 => 'TABLE_NAME'
2388 ,p_value1 => p_table_name
2389 ,p_token2 => 'COLUMN_NAME'
2390 ,p_value2 => p_column_name);
2391
2392 RAISE e_pc_engine_error;
2393
2394 WHEN OTHERS THEN
2395 FEM_ENGINES_PKG.Tech_Message (
2396 p_severity => g_log_level_5
2397 ,p_module => G_BLOCK||'.'||l_api_name
2398 ,p_msg_text => 'Register_Updated_Column_Exception');
2399
2400 FEM_ENGINES_PKG.User_Message (
2401 p_app_name => G_PFT
2402 ,p_msg_name => G_PL_REG_UPD_COL_ERR
2403 ,p_token1 => 'TABLE_NAME'
2404 ,p_value1 => p_table_name
2405 ,p_token2 => 'COLUMN_NAME'
2406 ,p_value2 => p_column_name);
2407
2408 RAISE e_pc_engine_error;
2409
2410 END Register_Updated_Column;
2411
2412 /*============================================================================+
2413 | PROCEDURE
2414 | Register_Chaining
2415 |
2416 | DESCRIPTION
2417 | This procedure is used to register the chain if this rule uses any
2418 | previously executed rules.
2419 |
2420 |
2421 | SCOPE - PRIVATE
2422 |
2423 +=============================================================================+
2424 PROCEDURE Register_Chaining()
2425 IS
2426
2427 BEGIN
2428
2429 Requirement :
2430 -------------
2431 If a new rule (say V1) is created with Value Index formula refers to 'Region Counting' and 'Profit Percentile'
2432 which is executed by some other rule(say R1), we need to call 'fem_pl_pkg.register_chain' to register this chain
2433 so that when ever R1 is undone (using 'UNDO UI'), V1 should also be undone.
2434
2435 Logic:
2436 ------
2437 Get the concurrent req Id and Object Id of Rule R1 and call fem_pl_pkg.register_chain.
2438
2439 EXCEPTION
2440
2441 END Register_Chaining */
2442
2443 END PFT_PPROFCAL_MASTER_PUB;