[Home] [Help]
PACKAGE BODY: APPS.PFT_AR_ENGINE_PVT
Source
1 PACKAGE BODY PFT_AR_ENGINE_PVT AS
2 /* $Header: PFTVAREB.pls 120.12 2006/09/20 06:28:00 navekuma noship $ */
3
4
5 -------------------------------
6 -- Declare package constants --
7 -------------------------------
8
9 -- Constants for p_exec_status_code
10 G_EXEC_STATUS_RUNNING constant varchar2(30) := 'RUNNING';
11 G_EXEC_STATUS_SUCCESS constant varchar2(30) := 'SUCCESS';
12 G_EXEC_STATUS_ERROR_UNDO constant varchar2(30) := 'ERROR_UNDO';
13 G_EXEC_STATUS_ERROR_RERUN constant varchar2(30) := 'ERROR_RERUN';
14
15 -- Default Fetch Limit if none is specified in Profile Options
16 G_DEFAULT_FETCH_LIMIT constant number := 99999;
17
18 -- Seeded Financial Element IDs
19 G_FIN_ELEM_ID_STATISTIC constant number := 10000;
20 G_FIN_ELEM_ID_ACTIVITY_RATE constant number := 5005;
21
22 -- Log Level Constants
23 G_LOG_LEVEL_1 constant number := fnd_log.level_statement;
24 G_LOG_LEVEL_2 constant number := fnd_log.level_procedure;
25 G_LOG_LEVEL_3 constant number := fnd_log.level_event;
26 G_LOG_LEVEL_4 constant number := fnd_log.level_exception;
27 G_LOG_LEVEL_5 constant number := fnd_log.level_error;
28 G_LOG_LEVEL_6 constant number := fnd_log.level_unexpected;
29
30 -- MP Constants
31 G_MP_ENABLED constant boolean := false;
32 G_COMPLETE_NORMAL constant varchar2(30) := 'COMPLETE:NORMAL';
33
34
35 ------------------------------
36 -- Declare package messages --
37 ------------------------------
38 G_EXEC_RERUN constant varchar2(30) := 'FEM_EXEC_RERUN';
39 G_EXEC_SUCCESS constant varchar2(30) := 'FEM_EXEC_SUCCESS';
40 G_UNEXPECTED_ERROR constant varchar2(30) := 'FEM_UNEXPECTED_ERROR';
41
42 -- Common FEM Engine Messages
43 G_ENG_NO_OUTPUT_DS_ERR constant varchar2(30) := 'FEM_ENG_NO_OUTPUT_DS_ERR';
44 G_ENG_NO_DS_GRP_OBJ_ERR constant varchar2(30) := 'FEM_ENG_NO_OBJ_ERR';
45 G_ENG_NO_SUBMIT_OBJ_ERR constant varchar2(30) := 'FEM_ENG_NO_SUBMIT_OBJ_ERR';
46 G_ENG_NO_ACT_RATE_OBJ_ERR constant varchar2(30) := 'FEM_ENG_NO_OBJ_ERR';
47 G_ENG_RS_NO_OBJ_ERR constant varchar2(30) := 'FEM_ENG_NO_OBJ_ERR';
48 G_ENG_RS_NO_OBJ_DEF_ERR constant varchar2(30) := 'FEM_ENG_NO_OBJ_DEF_DTL_ERR';
49 G_ENG_RS_BAD_LCL_VS_COMBO_ERR constant varchar2(30) := 'FEM_ENG_BAD_LCL_VS_COMBO_ERR';
50 G_ENG_NO_ACT_RATE_OBJ_DTL_ERR constant varchar2(30) := 'FEM_ENG_NO_OBJ_DEF_DTL_ERR';
51 G_ENG_NO_OBJ_DTL_ERR constant varchar2(30) := 'FEM_ENG_NO_OBJ_DTL_ERR';
52 G_ENG_BAD_HIER_DIM_ERR constant varchar2(30) := 'FEM_ENG_BAD_HIER_DIM_ERR';
53 G_ENG_BAD_DS_WCLAUSE_ERR constant varchar2(30) := 'FEM_ENG_BAD_DS_WCLAUSE_ERR';
54 G_ENG_NO_EXCH_RATE_FOUND_ERR constant varchar2(30) := 'FEM_ENG_NO_EXCH_RATE_ERR';
55 G_ENG_BAD_CURRENCY_ERR constant varchar2(30) := 'FEM_ENG_BAD_CURRENCY_ERR';
56 G_ENG_NO_DIM_ATTR_VER_ERR constant varchar2(30) := 'FEM_ENG_NO_DIM_ATTR_VER_ERR';
57 G_ENG_NO_DIM_ATTR_VAL_ERR constant varchar2(30) := 'FEM_ENG_NO_DIM_ATTR_VAL_ERR';
58 G_ENG_NO_DIM_DTL_ERR constant varchar2(30) := 'FEM_ENG_NO_DIM_DTL_ERR';
59 G_ENG_NO_OBJ_DEF_ERR constant varchar2(30) := 'FEM_ENG_NO_OBJ_DEF_ERR';
60 G_ENG_COND_WHERE_CLAUSE_ERR constant varchar2(30) := 'FEM_ENG_COND_WHERE_CLAUSE_ERR';
61 G_ENG_REQ_POST_PROC_ERR constant varchar2(30) := 'FEM_ENG_REQ_POST_PROC_ERR';
62 G_ENG_RULE_POST_PROC_ERR constant varchar2(30) := 'FEM_ENG_RULE_POST_PROC_ERR';
63 G_ENG_NO_DIM_MEMBER_ERR constant varchar2(30) := 'FEM_ENG_NO_DIM_MEMBER_ERR';
64 G_ENG_CREATE_SEQUENCE_ERR constant varchar2(30) := 'FEM_ENG_CREATE_SEQUENCE_ERR';
65 G_ENG_DROP_SEQUENCE_WRN constant varchar2(30) := 'FEM_ENG_DROP_SEQUENCE_WRN';
66 G_ENG_BAD_RS_OBJ_TYPE_ERR constant varchar2(30) := 'FEM_ENG_BAD_RS_OBJ_TYPE_ERR';
67 G_ENG_NO_CURR_CONV_TYPE_ERR constant varchar2(30) := 'FEM_ENG_NO_PROF_OPTION_VAL_ERR';
68 G_ENG_BAD_CONC_REQ_PARAM_ERR constant varchar2(30) := 'FEM_ENG_BAD_CONC_REQ_PARAM_ERR';
69
70 G_ENG_RS_RULE_PROCESSING_TXT constant varchar2(30) := 'FEM_ENG_RS_RULE_PROCESSING_TXT';
71
72 -- PFT Engine Messages
73 G_AR_INSERT_ACT_DRIV_ERR constant varchar2(30) := 'PFT_AR_INSERT_ACT_DRIV_ERR';
74 G_AR_NO_DRIVER_ERR constant varchar2(30) := 'PFT_AR_NO_DRIVER_ERR';
75 G_AR_ALL_INV_DRIV_ERR constant varchar2(30) := 'PFT_AR_ALL_INV_DRIV_ERR';
76 G_AR_NO_DRV_TBL_CLASSF_ERR constant varchar2(30) := 'PFT_AR_NO_DRV_TBL_CLASSF_ERR';
77 G_AR_ZERO_DRV_VAL_ERR constant varchar2(30) := 'PFT_AR_ZERO_DRV_VAL_ERR';
78 G_AR_UNEXP_DRV_VAL_ERR constant varchar2(30) := 'PFT_AR_UNEXP_DRV_VAL_ERR';
79
80 -------------------------------
81 -- Declare package variables --
82 -------------------------------
83 -- Exception variables
84 g_prg_msg varchar2(2000);
85 g_callstack varchar2(2000);
86
87 -- Bulk Fetch Limit
88 g_fetch_limit number;
89
90 -- Track Event Chains
91 g_track_event_chains boolean;
92
93 -- Currency Conversion Type
94 g_currency_conv_type varchar2(30);
95
96 --------------------------------
97 -- Declare package exceptions --
98 --------------------------------
99 -- General Activity Rate Request Exception
100 g_act_rate_request_error exception;
101
102 ------------------------------
103 -- Global PL/SQL types
104 ------------------------------
105
106 type g_request_id_table is table of PFT_AR_DRIVERS_T.CREATED_BY_REQUEST_ID%TYPE
107 index by BINARY_INTEGER;
108
109 type g_object_id_table is table of PFT_AR_DRIVERS_T.CREATED_BY_OBJECT_ID%TYPE
110 index by BINARY_INTEGER;
111
112 type g_drv_table_name_table is table of PFT_AR_DRIVERS_T.SOURCE_TABLE_NAME%TYPE
113 index by BINARY_INTEGER;
114
115 type g_column_name_table is table of PFT_AR_DRIVERS_T.COLUMN_NAME%TYPE
116 index by BINARY_INTEGER;
117
118 type g_statistic_basis_id_table is table of PFT_AR_DRIVERS_T.STATISTIC_BASIS_ID%TYPE
119 index by BINARY_INTEGER;
120
121 type g_condition_obj_id_table is table of PFT_AR_DRIVERS_T.CONDITION_OBJ_ID%TYPE
122 index by BINARY_INTEGER;
123
124 type g_valid_flag_table is table of PFT_AR_DRIVERS_T.VALID_FLAG%TYPE
125 index by BINARY_INTEGER;
126
127 type g_driver_value_table is table of PFT_AR_DRIVERS_T.DRIVER_VALUE%TYPE
128 index by BINARY_INTEGER;
129
130 type g_last_update_date_table is table of PFT_AR_DRIVERS_T.LAST_UPDATE_DATE%TYPE
131 index by BINARY_INTEGER;
132
133 type g_invalid_reason_table is table of PFT_AR_DRIVERS_T.INVALID_REASON%TYPE
134 index by BINARY_INTEGER;
135
136 -----------------------------------------------
137 -- Declare private procedures and functions --
138 -----------------------------------------------
139 PROCEDURE Request_Prep (
140 p_obj_id in number
141 ,p_effective_date_varchar in varchar2
142 ,p_ledger_id in number
143 ,p_output_cal_period_id in number
144 ,p_dataset_grp_obj_def_id in number
145 ,p_continue_process_on_err_flg in varchar2
146 ,p_source_system_code in number
147 ,x_request_rec out nocopy request_record
148 ,x_input_ds_b_where_clause out nocopy long
149 );
150
151 PROCEDURE Get_Object_Definition (
152 p_object_type_code in varchar2
153 ,p_object_id in number
154 ,p_effective_date in date
155 ,x_obj_def_id out nocopy number
156 );
157
158 PROCEDURE Get_Dimension_Record (
159 p_dimension_varchar_label in varchar2
160 ,x_dimension_rec out nocopy dimension_record
161 );
162
163 PROCEDURE Get_Dim_Attribute_Value (
164 p_dimension_varchar_label in varchar2
165 ,p_attribute_varchar_label in varchar2
166 ,p_member_id in number
167 ,x_dim_attribute_varchar_member out nocopy varchar
168 ,x_date_assign_value out nocopy date
169 );
170
171 PROCEDURE Register_Request (
172 p_request_rec in request_record
173 );
174
175 PROCEDURE Act_Rate_Rule (
176 p_request_rec in request_record
177 ,p_act_rate_obj_id in number
178 ,p_act_rate_obj_def_id in number
179 ,p_act_rate_sequence in number
180 ,p_input_ds_b_where_clause in long
181 ,x_return_status out nocopy varchar2
182 );
183
184 PROCEDURE Rule_Prep (
185 p_request_rec in request_record
186 ,p_act_rate_obj_id in number
187 ,p_act_rate_obj_def_id in number
188 ,p_act_rate_sequence in number
189 ,x_rule_rec out nocopy rule_record
190 );
191
192 PROCEDURE Register_Rule (
193 p_request_rec in request_record
194 ,p_rule_rec in rule_record
195 );
196
197 PROCEDURE Register_Object_Definition (
198 p_request_rec in request_record
199 ,p_rule_rec in rule_record
200 ,p_obj_def_id in number
201 );
202
203 PROCEDURE Register_Table (
204 p_request_rec in request_record
205 ,p_rule_rec in rule_record
206 ,p_table_name in varchar2
207 ,p_statement_type in varchar2
208 );
209
210 PROCEDURE Register_Obj_Exec_Step (
211 p_request_rec in request_record
212 ,p_rule_rec in rule_record
213 ,p_exec_step in varchar2
214 ,p_exec_status_code in varchar2
215 );
216
217 PROCEDURE Update_Obj_Exec_Step_Status (
218 p_request_rec in request_record
219 ,p_rule_rec in rule_record
220 ,p_exec_step in varchar2
221 ,p_exec_status_code in varchar2
222 );
223
224 PROCEDURE Create_Temp_Objects (
225 p_request_rec in request_record
226 ,p_rule_rec in rule_record
227 );
228
229 PROCEDURE Drop_Temp_Objects (
230 p_request_rec in request_record
231 ,p_rule_rec in rule_record
232 );
233
234 PROCEDURE Process_Drivers(
235 p_request_rec in request_record
236 ,p_rule_rec in rule_record
237 ,p_insert_count out nocopy number
238 );
239
240 PROCEDURE Calc_Act_Rate(
241 p_request_rec in request_record
242 ,p_rule_rec in rule_record
243 ,p_input_ds_b_where_clause in long
244 );
245
246 PROCEDURE Register_Driver_Chains (
247 p_request_id in number
248 ,p_ledger_id in number
249 ,p_user_id in number
250 ,p_login_id in number
251 ,p_act_rate_obj_id in number
252 ,p_drv_table_name in varchar2
253 ,p_statistic_basis_id in number
254 ,p_drv_cond_where_clause in long
255 ,p_input_ds_d_where_clause in long
256 );
257
258 PROCEDURE Register_Source_Chains (
259 p_request_id in number
260 ,p_act_rate_obj_id in number
261 ,p_ledger_id in number
262 ,p_input_ds_b_where_clause in long
263 ,p_user_id in number
264 ,p_login_id in number
265 );
266
267 PROCEDURE Rule_Post_Proc (
268 p_request_rec in request_record
269 ,p_rule_rec in rule_record
270 ,p_input_ds_b_where_clause in long
271 ,p_exec_status_code in varchar2
272 );
273
274 PROCEDURE Request_Post_Proc (
275 p_request_rec in request_record
276 ,p_exec_status_code in varchar2
277 );
278
279 FUNCTION Get_Lookup_Meaning (
280 p_lookup_type in varchar2
281 ,p_lookup_code in varchar2
282 )
283 RETURN varchar2;
284
285 PROCEDURE Get_Put_Messages (
286 p_msg_count in number
287 ,p_msg_data in varchar2
288 );
289
290
291 --------------------------------------------------------------------------------
292 -- Package bodies for functions/procedures
293 --------------------------------------------------------------------------------
294
295 /*===========================================================================+
296 | PROCEDURE
297 | Act_Rate_Request
298 |
299 | DESCRIPTION
300 | Main engine procedure for activity rate processing in PFT
301 |
302 | SCOPE - PUBLIC
303 |
304 | MODIFICATION HISTORY
305 | ammittal 01-NOV-2004 Created
306 |
307 +===========================================================================*/
308
309 PROCEDURE Act_Rate_Request (
310 errbuf out nocopy varchar2
311 ,retcode out nocopy varchar2
312 ,p_obj_id in number
313 ,p_effective_date in varchar2
314 ,p_ledger_id in number
315 ,p_output_cal_period_id in number
316 ,p_dataset_grp_obj_def_id in number
317 ,p_continue_process_on_err_flg in varchar2
318 ,p_source_system_code in number
319 )
320 IS
321
322 -----------------------
323 -- Declare constants --
324 -----------------------
325 L_API_NAME constant varchar2(30) := 'Act_Rate_Request';
326 L_API_VERSION constant number := 1.0;
327
328 -----------------------
329 -- Declare variables --
330 -----------------------
331 l_request_rec request_record;
332
333 l_act_rate_obj_id number;
334 l_act_rate_obj_def_id number;
335 l_act_rate_sequence number;
336
337 l_act_rate_rule_def_stmt long;
338 l_input_ds_b_where_clause long;
339
340 l_completion_status boolean;
341
342 l_act_rate_exec_status_code varchar2(30);
343
344 l_ruleset_status varchar2(1);
345
346 l_return_status varchar2(1);
347 l_msg_count number;
348 l_msg_data varchar2(240);
349
350 l_err_code number;
351 l_err_msg varchar2(30);
352
353 l_request_params_error exception;
354
355 ----------------------------
356 -- Declare static cursors --
357 ----------------------------
358 cursor l_ruleset_rules_csr (
359 p_request_id in number
360 ,p_ruleset_obj_id in number
361 ) is
362 select rs.child_obj_id
363 ,rs.child_obj_def_id
364 ,x.exec_status_code
365 from fem_ruleset_process_data rs,
366 fem_pl_object_executions x
367 where rs.request_id = p_request_id
368 and rs.rule_set_obj_id = p_ruleset_obj_id
369 and x.request_id(+) = rs.request_id
370 and x.object_id(+) = rs.child_obj_id
371 and x.exec_object_definition_id(+) = rs.child_obj_def_id
372 order by rs.engine_execution_sequence;
373
374
375 /*******************************************************************************
376 * *
377 * Act_Rate_Request *
378 * Execution Block *
379 * *
380 *******************************************************************************/
381
382 BEGIN
383
384 -- Initialize Message Stack on FND_MSG_PUB
385 FND_MSG_PUB.Initialize;
386
387 FEM_ENGINES_PKG.Tech_Message (
388 p_severity => G_LOG_LEVEL_2
389 ,p_module => G_BLOCK||'.'||L_API_NAME
390 ,p_msg_text => 'BEGIN'
391 );
392
393 ------------------------------------------------------------------------------
394 -- Check for the required parameters
395 ------------------------------------------------------------------------------
396
397 IF (p_obj_id IS NULL OR p_dataset_grp_obj_def_id IS NULL OR
398 p_effective_date IS NULL OR p_output_cal_period_id IS NULL OR
399 p_ledger_id IS NULL) THEN
400
401 FEM_ENGINES_PKG.User_Message (
402 p_app_name => G_FEM
403 ,p_msg_name => G_ENG_BAD_CONC_REQ_PARAM_ERR
404 );
405 raise g_act_rate_request_error;
406 END IF;
407
408 ------------------------------------------------------------------------------
409 -- STEP 1: Request Preparation
410 ------------------------------------------------------------------------------
411 FEM_ENGINES_PKG.tech_message (
412 p_severity => G_LOG_LEVEL_1
413 ,p_module => G_BLOCK||'.'||L_API_NAME
414 ,p_msg_text => 'Step 1: Request Preperation'
415 );
416
417 Request_Prep (
418 p_obj_id => p_obj_id
419 ,p_effective_date_varchar => p_effective_date
420 ,p_ledger_id => p_ledger_id
421 ,p_output_cal_period_id => p_output_cal_period_id
422 ,p_dataset_grp_obj_def_id => p_dataset_grp_obj_def_id
423 ,p_continue_process_on_err_flg => p_continue_process_on_err_flg
424 ,p_source_system_code => p_source_system_code
425 ,x_request_rec => l_request_rec
426 ,x_input_ds_b_where_clause => l_input_ds_b_where_clause
427 );
428
429 ------------------------------------------------------------------------------
430 -- STEP 2: Register Request
431 ------------------------------------------------------------------------------
432 FEM_ENGINES_PKG.Tech_Message (
433 p_severity => G_LOG_LEVEL_1
434 ,p_module => G_BLOCK||'.'||L_API_NAME
435 ,p_msg_text => 'Step 2: Register Request'
436 );
437
438 Register_Request (
439 p_request_rec => l_request_rec
440 );
441
442 ------------------------------------------------------------------------------
443 -- STEP 3: Start Activity Rate Processing
444 ------------------------------------------------------------------------------
445 FEM_ENGINES_PKG.Tech_Message (
446 p_severity => G_LOG_LEVEL_1
447 ,p_module => G_BLOCK||'.'||L_API_NAME
448 ,p_msg_text => 'Step 3: Start Activity Rate Processing'
449 );
450
451 -- Initialize the activity rate sequence to 0
452 -- For Single Rule Submit, the sequence will remain at 0.
453 -- For Rule Set Submit, the sequence for rule processing will be 1 to n.
454 l_act_rate_sequence := 0;
455
456 if (l_request_rec.submit_obj_type_code <> 'RULE_SET') then
457
458 ------------------------------------------------------------------------------
459 -- STEP 3.1: Single Rule Submit Processing
460 ------------------------------------------------------------------------------
461 FEM_ENGINES_PKG.Tech_Message (
462 p_severity => G_LOG_LEVEL_1
463 ,p_module => G_BLOCK||'.'||L_API_NAME
464 ,p_msg_text => 'Step 3.1: Single Rule Submit Processing'
465 );
466
467 l_act_rate_obj_id := l_request_rec.submit_obj_id;
468 l_act_rate_obj_def_id := null;
469
470 ----------------------------------------------------------------------------
471 -- STEP 3.1.a: Validate Single Rule Submit
472 ----------------------------------------------------------------------------
473 FEM_ENGINES_PKG.Tech_Message (
474 p_severity => G_LOG_LEVEL_1
475 ,p_module => G_BLOCK||'.'||L_API_NAME
476 ,p_msg_text => 'Step 3.1.a: Single Rule Submit Processing'
477 );
478
479 -- Bug fix 4426460 - ammittal 06/21/05 - The code has been uncommented
480 FEM_RULE_SET_MANAGER.Validate_Rule_Public (
481 x_err_code => l_err_code
482 ,x_err_msg => l_err_msg
483 ,p_rule_object_id => l_act_rate_obj_id
484 ,p_ds_io_def_id => l_request_rec.dataset_grp_obj_def_id
485 ,p_rule_effective_date => l_request_rec.effective_date_varchar
486 ,p_reference_period_id => l_request_rec.output_cal_period_id
487 ,p_ledger_id => l_request_rec.ledger_id
488 );
489
490 if (l_err_code <> 0) then
491 FEM_ENGINES_PKG.User_Message (
492 p_app_name => G_FEM
493 ,p_msg_name => l_err_msg
494 );
495 raise g_act_rate_request_error;
496 end if;
497 -- End of Bug fix 4426460
498
499 ----------------------------------------------------------------------------
500 -- STEP 3.1.b: Calculate Activity Rate for Single Rule
501 ----------------------------------------------------------------------------
502 FEM_ENGINES_PKG.Tech_Message (
503 p_severity => G_LOG_LEVEL_1
504 ,p_module => G_BLOCK||'.'||L_API_NAME
505 ,p_msg_text => 'Step 3.1.b: Calculate Activity Rate for Single Rule'
506 );
507
508 Act_Rate_Rule (
509 p_request_rec => l_request_rec
510 ,p_act_rate_obj_id => l_act_rate_obj_id
511 ,p_act_rate_obj_def_id => l_act_rate_obj_def_id
512 ,p_act_rate_sequence => l_act_rate_sequence
513 ,p_input_ds_b_where_clause => l_input_ds_b_where_clause
514 ,x_return_status => l_return_status
515 );
516
517 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
518 raise g_act_rate_request_error;
519 end if;
520
521 else
522
523 ----------------------------------------------------------------------------
524 -- STEP 3.2: Rule Set Processing
525 ----------------------------------------------------------------------------
526 FEM_ENGINES_PKG.Tech_Message (
527 p_severity => G_LOG_LEVEL_1
528 ,p_module => G_BLOCK||'.'||L_API_NAME
529 ,p_msg_text => 'Step 3.2: Rule Set Processing'
530 );
531
532 ----------------------------------------------------------------------------
533 -- STEP 3.2.a: Rule Set Pre Processing
534 ----------------------------------------------------------------------------
535 FEM_ENGINES_PKG.Tech_Message (
536 p_severity => G_LOG_LEVEL_1
540
537 ,p_module => G_BLOCK||'.'||L_API_NAME
538 ,p_msg_text => 'Step 3.2.a: Rule Set Pre Processing'
539 );
541 FEM_RULE_SET_MANAGER.FEM_Preprocess_RuleSet_PVT (
542 p_api_version => 1.0
543 ,p_init_msg_list => FND_API.G_FALSE
544 ,p_commit => FND_API.G_TRUE
545 ,p_encoded => FND_API.G_TRUE
546 ,x_return_status => l_return_status
547 ,x_msg_count => l_msg_count
548 ,x_msg_data => l_msg_data
549 ,p_orig_ruleset_object_id => l_request_rec.ruleset_obj_id
550 ,p_ds_io_def_id => l_request_rec.dataset_grp_obj_def_id
551 ,p_rule_effective_date => l_request_rec.effective_date_varchar
552 ,p_output_period_id => l_request_rec.output_cal_period_id
553 ,p_ledger_id => l_request_rec.ledger_id
554 ,p_continue_process_on_err_flg => l_request_rec.continue_process_on_err_flg
555 ,p_execution_mode => 'E' --This is engine execution mode
556 );
557
558 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
559 Get_Put_Messages (
560 p_msg_count => l_msg_count
561 ,p_msg_data => l_msg_data
562 );
563 raise g_act_rate_request_error;
564 end if;
565
566 ----------------------------------------------------------------------------
567 -- STEP 3.2.b: Loop through all Rule Set Rules
568 ----------------------------------------------------------------------------
569 FEM_ENGINES_PKG.Tech_Message (
570 p_severity => G_LOG_LEVEL_1
571 ,p_module => G_BLOCK||'.'||L_API_NAME
572 ,p_msg_text => 'Step 3.2.b: Loop through all Rule Set Rules'
573 );
574
575 -- Initialize the rule set status to SUCCESS
576 l_ruleset_status := FND_API.G_RET_STS_SUCCESS;
577
578 open l_ruleset_rules_csr (
579 p_request_id => l_request_rec.request_id
580 ,p_ruleset_obj_id => l_request_rec.ruleset_obj_id
581 );
582
583 loop
584
585 fetch l_ruleset_rules_csr
586 into l_act_rate_obj_id
587 ,l_act_rate_obj_def_id
588 ,l_act_rate_exec_status_code;
589
590 exit when l_ruleset_rules_csr%NOTFOUND;
591
592 l_act_rate_sequence := l_act_rate_sequence + 1;
593
594 -- Do not process rule set rollup rules that completed successfully
595
596 if (l_act_rate_exec_status_code is null)
597 or (l_act_rate_exec_status_code <> 'SUCCESS') then
598
599 --------------------------------------------------------------------------
600 -- STEP 3.2.c: Activity Rate Rule Set Rule
601 --------------------------------------------------------------------------
602 FEM_ENGINES_PKG.Tech_Message (
603 p_severity => G_LOG_LEVEL_1
604 ,p_module => G_BLOCK||'.'||L_API_NAME
605 ,p_msg_text => 'Step 3.2.c: Activity Rate Rule Set Rule #'||to_char(l_act_rate_sequence)
606 );
607
608 Act_Rate_Rule (
609 p_request_rec => l_request_rec
610 ,p_act_rate_obj_id => l_act_rate_obj_id
611 ,p_act_rate_obj_def_id => l_act_rate_obj_def_id
612 ,p_act_rate_sequence => l_act_rate_sequence
613 ,p_input_ds_b_where_clause => l_input_ds_b_where_clause
614 ,x_return_status => l_return_status
615 );
616
617 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
618 l_ruleset_status := l_return_status;
619 if (l_request_rec.continue_process_on_err_flg = 'N') then
620 raise g_act_rate_request_error;
621 end if;
622 end if;
623
624 end if;
625
626 end loop;
627
628 close l_ruleset_rules_csr;
629
630 if (l_ruleset_status <> FND_API.G_RET_STS_SUCCESS) then
631 raise g_act_rate_request_error;
632 end if;
633
634 end if;
635
636 ------------------------------------------------------------------------------
637 -- STEP 4: Request Post Processing.
638 ------------------------------------------------------------------------------
639 FEM_ENGINES_PKG.Tech_Message (
640 p_severity => G_LOG_LEVEL_1
644
641 ,p_module => G_BLOCK||'.'||L_API_NAME
642 ,p_msg_text => 'Step 4: Request Post Processing'
643 );
645 Request_Post_Proc (
646 p_request_rec => l_request_rec
647 ,p_exec_status_code => G_EXEC_STATUS_SUCCESS
648 );
649
650 FEM_ENGINES_PKG.Tech_Message (
651 p_severity => G_LOG_LEVEL_2
652 ,p_module => G_BLOCK||'.'||L_API_NAME
653 ,p_msg_text => 'END'
654 );
655
656 EXCEPTION
660 if (l_ruleset_rules_csr%ISOPEN) then
657
658 when g_act_rate_request_error then
659
661 close l_ruleset_rules_csr;
662 end if;
663
664 Request_Post_Proc (
665 p_request_rec => l_request_rec
666 ,p_exec_status_code => G_EXEC_STATUS_ERROR_UNDO
667 );
668
669 l_completion_status := FND_CONCURRENT.Set_Completion_Status('ERROR',null);
670
671 FEM_ENGINES_PKG.Tech_Message (
672 p_severity => g_log_level_6
673 ,p_module => G_BLOCK||'.'||L_API_NAME
674 ,p_msg_text => 'Activity Rate Request Exception'
675 );
676
677 when others then
678
679 g_prg_msg := SQLERRM;
680 g_callstack := DBMS_UTILITY.Format_Call_Stack;
681
682 if (l_ruleset_rules_csr%ISOPEN) then
683 close l_ruleset_rules_csr;
684 end if;
685
686 Request_Post_Proc (
687 p_request_rec => l_request_rec
688 ,p_exec_status_code => G_EXEC_STATUS_ERROR_UNDO
689 );
690
691 l_completion_status := FND_CONCURRENT.Set_Completion_Status('ERROR',null);
692
693 FEM_ENGINES_PKG.Tech_Message (
694 p_severity => g_log_level_6
695 ,p_module => G_BLOCK||'.'||L_API_NAME||'.Unexpected_Exception'
696 ,p_msg_text => g_prg_msg
697 );
698
699 FEM_ENGINES_PKG.Tech_Message (
700 p_severity => g_log_level_6
701 ,p_module => G_BLOCK||'.'||L_API_NAME||'.Unexpected_Exception'
702 ,p_msg_text => g_callstack
703 );
704
705 FEM_ENGINES_PKG.User_Message (
706 p_app_name => G_FEM
707 ,p_msg_name => G_UNEXPECTED_ERROR
708 ,p_token1 => 'ERR_MSG'
709 ,p_value1 => g_prg_msg
710 );
711
712 END Act_Rate_Request;
713
714
715
716 /*===========================================================================+
717 | PROCEDURE
718 | Request_Prep
719 |
720 | DESCRIPTION
721 | This procedure takes the i/p variables from concurrent manager
722 | and prepares the engine with relevant information
723 |
724 | SCOPE - PRIVATE
725 |
726 +===========================================================================*/
727
728 PROCEDURE Request_Prep (
729 p_obj_id in number
730 ,p_effective_date_varchar in varchar2
731 ,p_ledger_id in number
732 ,p_output_cal_period_id in number
733 ,p_dataset_grp_obj_def_id in number
734 ,p_continue_process_on_err_flg in varchar2
735 ,p_source_system_code in number
736 ,x_request_rec out nocopy request_record
737 ,x_input_ds_b_where_clause out nocopy long
738 )
739 IS
740
741 L_API_NAME constant varchar2(30) := 'Request_Prep';
742
743 l_dimension_varchar_label varchar2(30) := 'ACTIVITY';
744 l_dummy_varchar varchar2(30);
745 l_dummy_date date;
746
747 l_object_name varchar2(150);
748 l_object_type_code varchar2(30);
749 l_folder_name varchar2(150);
750
751 l_return_status varchar2(1);
752 l_msg_count number;
753 l_msg_data varchar2(240);
754
755 l_request_prep_error exception;
756
757 BEGIN
758
759 FEM_ENGINES_PKG.Tech_Message (
760 p_severity => G_LOG_LEVEL_2
761 ,p_module => G_BLOCK||'.'||L_API_NAME
762 ,p_msg_text => 'BEGIN'
763 );
764
765 ------------------------------------------------------------
766 -- Set all the Submitted Parameters on the Request Record --
767 ------------------------------------------------------------
768 x_request_rec.submit_obj_id := p_obj_id;
769 x_request_rec.effective_date_varchar := p_effective_date_varchar;
770 x_request_rec.effective_date :=
771 FND_DATE.Canonical_To_Date(p_effective_date_varchar);
772 x_request_rec.ledger_id := p_ledger_id;
773 x_request_rec.output_cal_period_id := p_output_cal_period_id;
774 x_request_rec.dataset_grp_obj_def_id := p_dataset_grp_obj_def_id;
775 x_request_rec.continue_process_on_err_flg := p_continue_process_on_err_flg;
776 x_request_rec.source_system_code := p_source_system_code;
777
778 -------------------------------------------------------------
779 -- Set all the FND Global Parameters on the Request Record --
780 -------------------------------------------------------------
781 x_request_rec.user_id := FND_GLOBAL.user_id;
782 x_request_rec.login_id := FND_GLOBAL.login_id;
783 x_request_rec.request_id := FND_GLOBAL.conc_request_id;
784 x_request_rec.resp_id := FND_GLOBAL.resp_id;
785 x_request_rec.pgm_id := FND_GLOBAL.conc_program_id;
786 x_request_rec.pgm_app_id := FND_GLOBAL.prog_appl_id;
787
788 ---------------------------------------------------------
789 -- Get the limit for bulk fetches from profile options --
790 ---------------------------------------------------------
791 g_fetch_limit := nvl (
792 FND_PROFILE.Value_Specific (
793 'FEM_BULK_FETCH_LIMIT'
794 ,x_request_rec.user_id
795 ,x_request_rec.resp_id
796 ,x_request_rec.pgm_app_id)
797 ,G_DEFAULT_FETCH_LIMIT
798 );
799
800 ----------------------------------------------------------
801 -- Get the track event chains flag from profile options --
802 ----------------------------------------------------------
803 g_track_event_chains :=
804 ('Y' =
805 FND_PROFILE.Value_Specific (
806 'FEM_TRACK_EVENT_CHAINS'
807 ,x_request_rec.user_id
808 ,x_request_rec.resp_id
809 ,x_request_rec.pgm_app_id)
810 );
811
812 -----------------------------------------------------------
813 -- Get the currency conversion type from profile options --
814 -----------------------------------------------------------
815 g_currency_conv_type :=
816 FND_PROFILE.Value_Specific (
817 'FEM_CURRENCY_CONVERSION_TYPE'
818 ,x_request_rec.user_id
819 ,x_request_rec.resp_id
820 ,x_request_rec.pgm_app_id
821 );
822
823 if (g_currency_conv_type is null) then
827 ,p_token1 => 'PROFILE_OPTION_NAME'
824 FEM_ENGINES_PKG.User_Message (
825 p_app_name => G_FEM
826 ,p_msg_name => G_ENG_NO_CURR_CONV_TYPE_ERR
828 ,p_value1 => 'FEM_CURRENCY_CONVERSION_TYPE'
829 );
830 raise l_request_prep_error;
831 end if;
832
833
834 ------------------------------------------------------------------------------
835 -- Get the object type code to determine if this is a rule set or single
836 -- rule submit submission
837 ------------------------------------------------------------------------------
838 begin
839 select object_type_code
840 ,local_vs_combo_id
841 into x_request_rec.submit_obj_type_code
842 ,x_request_rec.local_vs_combo_id
843 from fem_object_catalog_b
844 where object_id = x_request_rec.submit_obj_id;
845 exception
846 when others then
847 FEM_ENGINES_PKG.User_Message (
851 ,p_value1 => x_request_rec.submit_obj_id
848 p_app_name => G_FEM
849 ,p_msg_name => G_ENG_NO_SUBMIT_OBJ_ERR
850 ,p_token1 => 'OBJECT_ID'
852 );
853 raise l_request_prep_error;
854 end;
855
856 if (x_request_rec.submit_obj_type_code = 'RULE_SET') then
857
858 x_request_rec.ruleset_obj_id := x_request_rec.submit_obj_id;
859
860 begin
861 select object_name
862 into x_request_rec.ruleset_obj_name
863 from fem_object_catalog_vl
864 where object_id = x_request_rec.ruleset_obj_id;
865 exception
866 when others then
867 FEM_ENGINES_PKG.User_Message (
868 p_app_name => G_FEM
869 ,p_msg_name => G_ENG_RS_NO_OBJ_ERR
870 ,p_token1 => 'OBJECT_TYPE_MEANING'
871 ,p_value1 => Get_Lookup_Meaning('FEM_OBJECT_TYPE_DSC','RULE_SET')
872 ,p_token2 => 'OBJECT_ID'
873 ,p_value2 => x_request_rec.ruleset_obj_id
874 );
875 raise l_request_prep_error;
876 end;
877
878 Get_Object_Definition (
879 p_object_type_code => x_request_rec.submit_obj_type_code
880 ,p_object_id => x_request_rec.ruleset_obj_id
881 ,p_effective_date => x_request_rec.effective_date
882 ,x_obj_def_id => x_request_rec.ruleset_obj_def_id
883 );
884
885 -- Set the Object Type Code for the Activity Rate Request
886 begin
887 select rule_set_object_type_code
888 into x_request_rec.act_rate_obj_type_code
889 from fem_rule_sets
890 where rule_set_obj_def_id = x_request_rec.ruleset_obj_def_id;
891 exception
892 when others then
893
894 select object_name
895 ,object_type_code
896 into l_object_name
897 ,l_object_type_code
898 from fem_object_catalog_vl
899 where object_id = p_obj_id;
900
901 FEM_ENGINES_PKG.User_Message (
902 p_app_name => G_FEM
903 ,p_msg_name => G_ENG_RS_NO_OBJ_DEF_ERR
904 ,p_token1 => 'OBJECT_TYPE_MEANING'
905 ,p_value1 => Get_Lookup_Meaning('FEM_OBJECT_TYPE_DSC',l_object_type_code)
906 ,p_token2 => 'OBJECT_NAME'
907 ,p_value2 => l_object_name
908 ,p_token3 => 'EFFECTIVE_DATE'
909 ,p_value3 => FND_DATE.date_to_chardate(x_request_rec.effective_date)
910 );
911 raise l_request_prep_error;
912 end;
913
914 else
915
916 x_request_rec.ruleset_obj_id := null;
917 x_request_rec.ruleset_obj_name := null;
918 x_request_rec.ruleset_obj_def_id := null;
919
920 -- Set the Object Type Code for the Activity Rate Process
921 x_request_rec.act_rate_obj_type_code := x_request_rec.submit_obj_type_code;
922
923 end if;
924
925 ------------------------------------------------------------------------------
926 -- Get Dimension Metadata
927 ------------------------------------------------------------------------------
928 Get_Dimension_Record (
929 p_dimension_varchar_label => l_dimension_varchar_label
930 ,x_dimension_rec => x_request_rec.dimension_rec
931 );
932
933 ------------------------------------------------------------------------------
934 -- Validate the Processing Key on FEM_BALANCES to make sure that it can
935 -- handle rollup processing on the appropriate composite dimension.
936 ------------------------------------------------------------------------------
937 -- Validation added with bug 4475839
938 FEM_SETUP_PKG.Validate_Proc_Key (
939 p_api_version => 1.0
940 ,p_init_msg_list => FND_API.G_FALSE
941 ,p_commit => FND_API.G_FALSE
942 ,p_encoded => FND_API.G_TRUE
943 ,x_return_status => l_return_status
944 ,x_msg_count => l_msg_count
945 ,x_msg_data => l_msg_data
946 ,p_dimension_varchar_label => l_dimension_varchar_label
947 ,p_table_name => 'FEM_BALANCES'
948 );
949
950 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
951 Get_Put_Messages (
952 p_msg_count => l_msg_count
953 ,p_msg_data => l_msg_data
954 );
955 raise l_request_prep_error;
956 end if;
957
958 ------------------------------------------------------------------------------
959 -- Get the Source System Code for PFT if a null param has been passed
960 ------------------------------------------------------------------------------
961
962 if (x_request_rec.source_system_code is null) then
963 -- For all Activity Rate Processing default the Source System Display Code to PFT
964 begin
965 select source_system_code
966 into x_request_rec.source_system_code
967 from fem_source_systems_b
968 where source_system_display_code = G_PFT_SOURCE_SYSTEM_DC;
969 exception
970 when others then
971 FEM_ENGINES_PKG.User_Message (
972 p_app_name => G_FEM
973 ,p_msg_name => G_ENG_NO_DIM_MEMBER_ERR
974 ,p_token1 => 'TABLE_NAME'
975 ,p_value1 => 'FEM_SOURCE_SYSTEMS_B'
976 ,p_token2 => 'MEMBER_DISPLAY_CODE'
977 ,p_value2 => G_PFT_SOURCE_SYSTEM_DC
978 );
979 raise l_request_prep_error;
980 end;
981
982 end if;
983
984 ------------------------------------------------------------------------------
985 -- Get the Output Dataset Code
986 ------------------------------------------------------------------------------
987 begin
988 select output_dataset_code
989 into x_request_rec.output_dataset_code
990 from fem_ds_input_output_defs
994
991 where dataset_io_obj_def_id = x_request_rec.dataset_grp_obj_def_id;
992 exception
993 when others then
995 select obj.object_name
996 ,f.folder_name
997 into l_object_name
998 ,l_folder_name
999 from fem_object_catalog_vl obj
1000 ,fem_folders_vl f
1001 where obj.object_id = x_request_rec.dataset_grp_obj_id
1002 and f.folder_id = obj.folder_id;
1003
1004 FEM_ENGINES_PKG.User_Message (
1005 p_app_name => G_FEM
1006 ,p_msg_name => G_ENG_NO_OUTPUT_DS_ERR
1007 ,p_token1 => 'FOLDER_NAME'
1008 ,p_value1 => l_folder_name
1009 ,p_token2 => 'DATASET_GRP_NAME'
1010 ,p_value2 => l_object_name
1011 );
1012 raise l_request_prep_error;
1013 end;
1014
1015 ------------------------------------------------------------------------------
1016 -- Get the Dataset Group Object ID
1017 ------------------------------------------------------------------------------
1018 begin
1019 select object_id
1020 into x_request_rec.dataset_grp_obj_id
1021 from fem_object_definition_b
1022 where object_definition_id = x_request_rec.dataset_grp_obj_def_id;
1023 exception
1024 when others then
1025 FEM_ENGINES_PKG.User_Message (
1026 p_app_name => G_FEM
1027 ,p_msg_name => G_ENG_NO_DS_GRP_OBJ_ERR
1028 ,p_token1 => 'OBJECT_TYPE_MEANING'
1029 ,p_value1 => Get_Lookup_Meaning('FEM_OBJECT_TYPE_DSC','RULE_SET')
1030 ,p_token2 => 'OBJECT_ID'
1031 ,p_value2 => x_request_rec.ruleset_obj_id
1032 );
1033 raise l_request_prep_error;
1034 end;
1035
1036 ------------------------------------------------------------------------------
1037 -- Call the Where Clause Generator for source data in FEM_BALANCES
1038 ------------------------------------------------------------------------------
1039 FEM_DS_WHERE_CLAUSE_GENERATOR.FEM_Gen_DS_WClause_PVT (
1040 p_api_version => 1.0
1041 ,p_init_msg_list => FND_API.G_FALSE
1042 ,p_encoded => FND_API.G_TRUE
1043 ,x_return_status => l_return_status
1047 ,p_output_period_id => x_request_rec.output_cal_period_id
1044 ,x_msg_count => l_msg_count
1045 ,x_msg_data => l_msg_data
1046 ,p_ds_io_def_id => x_request_rec.dataset_grp_obj_def_id
1048 ,p_table_name => 'FEM_BALANCES'
1049 ,p_table_alias => 'B'
1050 ,p_ledger_id => x_request_rec.ledger_id
1051 ,p_where_clause => x_input_ds_b_where_clause
1052 );
1053
1054 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1055 Get_Put_Messages (
1056 p_msg_count => l_msg_count
1057 ,p_msg_data => l_msg_data
1058 );
1059 raise l_request_prep_error;
1060 end if;
1061
1062 if (x_input_ds_b_where_clause is null) then
1063 FEM_ENGINES_PKG.User_Message (
1064 p_app_name => G_FEM
1065 ,p_msg_name => G_ENG_BAD_DS_WCLAUSE_ERR
1066 ,p_token1 => 'DATASET_GRP_OBJ_DEF_ID'
1067 ,p_value1 => x_request_rec.dataset_grp_obj_def_id
1068 ,p_token2 => 'OUTPUT_CAL_PERIOD_ID'
1069 ,p_value2 => x_request_rec.output_cal_period_id
1070 ,p_token3 => 'TABLE_NAME'
1071 ,p_value3 => 'FEM_BALANCES'
1072 ,p_token4 => 'LEDGER_ID'
1073 ,p_value4 => x_request_rec.ledger_id
1074 );
1075 raise l_request_prep_error;
1076 end if;
1077
1078 ------------------------------------------------------------------------------
1079 -- Get Ledger information
1080 ------------------------------------------------------------------------------
1081 Get_Dim_Attribute_Value (
1082 p_dimension_varchar_label => 'LEDGER'
1083 ,p_attribute_varchar_label => 'ENTERED_CRNCY_ENABLE_FLAG'
1084 ,p_member_id => x_request_rec.ledger_id
1085 ,x_dim_attribute_varchar_member => x_request_rec.entered_currency_flag
1086 ,x_date_assign_value => l_dummy_date
1087 );
1088
1089 Get_Dim_Attribute_Value (
1090 p_dimension_varchar_label => 'LEDGER'
1091 ,p_attribute_varchar_label => 'LEDGER_FUNCTIONAL_CRNCY_CODE'
1092 ,p_member_id => x_request_rec.ledger_id
1093 ,x_dim_attribute_varchar_member => x_request_rec.functional_currency_code
1094 ,x_date_assign_value => l_dummy_date
1095 );
1096
1097 ------------------------------------------------------------------------------
1098 -- Set the exchange rate date
1099 ------------------------------------------------------------------------------
1100 if (x_request_rec.entered_currency_flag = 'Y') then
1101
1102 Get_Dim_Attribute_Value (
1103 p_dimension_varchar_label => 'CAL_PERIOD'
1104 ,p_attribute_varchar_label => 'CAL_PERIOD_END_DATE'
1105 ,p_member_id => x_request_rec.output_cal_period_id
1106 ,x_dim_attribute_varchar_member => l_dummy_varchar
1107 ,x_date_assign_value => x_request_rec.entered_exch_rate_date
1108 );
1109
1110 else
1111
1112 x_request_rec.entered_exch_rate_date := null;
1113
1114 end if;
1115
1116 -- Log all Request Record Parameters if we have low level debugging
1117 if ( FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) ) then
1118
1119 FEM_ENGINES_PKG.Tech_Message (
1120 p_severity => G_LOG_LEVEL_1
1121 ,p_module => G_BLOCK||'.'||L_API_NAME||'.x_request_rec'
1122 ,p_msg_text =>
1123 ' act_rate_obj_type_code='||x_request_rec.act_rate_obj_type_code||
1124 ' dataset_grp_obj_def_id='||x_request_rec.dataset_grp_obj_def_id||
1125 ' dataset_grp_obj_id ='||x_request_rec.dataset_grp_obj_id ||
1126 ' dimension_varchar_label='||x_request_rec.dimension_rec.dimension_varchar_label||
1127 ' effective_date='||FND_DATE.date_to_chardate(x_request_rec.effective_date)||
1128 ' entered_exch_rate_date='||FND_DATE.date_to_chardate(x_request_rec.entered_exch_rate_date)||
1129 ' entered_currency_flag ='||x_request_rec.entered_currency_flag ||
1130 ' functional_currency_code ='||x_request_rec.functional_currency_code ||
1131 ' ledger_id='||x_request_rec.ledger_id||
1132 ' local_vs_combo_id='||x_request_rec.local_vs_combo_id||
1133 ' login_id='||x_request_rec.login_id||
1134 ' output_cal_period_id='||x_request_rec.output_cal_period_id||
1135 ' output_dataset_code='||x_request_rec.output_dataset_code||
1136 ' pgm_id='||x_request_rec.pgm_id||
1137 ' pgm_app_id='||x_request_rec.pgm_app_id||
1138 ' request_id='||x_request_rec.request_id||
1139 ' resp_id='||x_request_rec.resp_id||
1140 ' ruleset_obj_def_id='||x_request_rec.ruleset_obj_def_id||
1141 ' ruleset_obj_id='||x_request_rec.ruleset_obj_id||
1147 );
1142 ' ruleset_obj_name='||x_request_rec.ruleset_obj_name||
1143 ' source_system_code='||x_request_rec.source_system_code||
1144 ' submit_obj_id='||x_request_rec.submit_obj_id||
1145 ' submit_obj_type_code='||x_request_rec.submit_obj_type_code||
1146 ' user_id='||x_request_rec.user_id
1148
1149 end if;
1150
1151 FEM_ENGINES_PKG.Tech_Message (
1152 p_severity => G_LOG_LEVEL_2
1153 ,p_module => G_BLOCK||'.'||L_API_NAME
1154 ,p_msg_text => 'END'
1155 );
1156
1157 EXCEPTION
1158
1159 when l_request_prep_error then
1160
1161 FEM_ENGINES_PKG.Tech_Message (
1162 p_severity => G_LOG_LEVEL_6
1163 ,p_module => G_BLOCK||'.'||L_API_NAME
1164 ,p_msg_text => 'Request Preperation Exception'
1165 );
1166
1167 raise g_act_rate_request_error;
1168
1169 END Request_Prep;
1170
1171
1172
1173 /*===========================================================================+
1174 | PROCEDURE
1175 | Get_Object_Definition
1176 |
1177 | DESCRIPTION
1178 | Get the object definition from object id
1179 |
1180 | SCOPE - PRIVATE
1181 |
1182 +===========================================================================*/
1183
1184 PROCEDURE Get_Object_Definition (
1185 p_object_type_code in varchar2
1186 ,p_object_id in number
1187 ,p_effective_date in date
1188 ,x_obj_def_id out nocopy number
1189 )
1190 IS
1191
1192 L_API_NAME constant varchar2(30) := 'Get_Object_Definition';
1193
1194 l_object_name varchar2(150);
1195
1196 BEGIN
1197
1198 FEM_ENGINES_PKG.Tech_Message (
1199 p_severity => G_LOG_LEVEL_2
1200 ,p_module => G_BLOCK||'.'||L_API_NAME
1201 ,p_msg_text => 'BEGIN'
1202 );
1203
1204 select d.object_definition_id
1205 into x_obj_def_id
1206 from fem_object_definition_b d
1207 ,fem_object_catalog_b o
1208 where o.object_id = p_object_id
1209 and o.object_type_code = p_object_type_code
1210 and d.object_id = o.object_id
1211 and p_effective_date between d.effective_start_date and d.effective_end_date
1212 and d.old_approved_copy_flag = 'N';
1213
1214 FEM_ENGINES_PKG.Tech_Message (
1215 p_severity => G_LOG_LEVEL_2
1216 ,p_module => G_BLOCK||'.'||L_API_NAME
1217 ,p_msg_text => 'END'
1218 );
1219
1220 EXCEPTION
1221
1222 when no_data_found then
1223
1224 select object_name
1225 into l_object_name
1226 from fem_object_catalog_vl
1227 where object_id = p_object_id;
1228
1229 FEM_ENGINES_PKG.User_Message (
1230 p_app_name => G_FEM
1231 ,p_msg_name => G_ENG_NO_OBJ_DEF_ERR
1232 ,p_token1 => 'OBJECT_TYPE_CODE'
1233 ,p_value1 => p_object_type_code
1234 ,p_token2 => 'OBJECT_NAME'
1235 ,p_value2 => l_object_name
1236 ,p_token3 => 'EFFECTIVE_DATE'
1237 ,p_value3 => FND_DATE.date_to_chardate(p_effective_date)
1238 );
1239
1240 raise g_act_rate_request_error;
1241
1242 END Get_Object_Definition;
1243
1244
1245
1246 /*===========================================================================+
1247 | PROCEDURE
1248 | Get_Dimension_Record
1249 |
1250 | DESCRIPTION
1251 | Validates the input dimension and returns a dimension record containing
1252 | dimension metadata
1253 |
1254 | SCOPE - PRIVATE
1255 |
1256 +===========================================================================*/
1257
1258 PROCEDURE Get_Dimension_Record (
1259 p_dimension_varchar_label in varchar2
1260 ,x_dimension_rec out nocopy dimension_record
1261 )
1262 IS
1263
1264 L_API_NAME constant varchar2(30) := 'Get_Dimension_Record';
1265
1266 BEGIN
1267
1268 FEM_ENGINES_PKG.Tech_Message (
1269 p_severity => G_LOG_LEVEL_2
1270 ,p_module => G_BLOCK||'.'||L_API_NAME
1271 ,p_msg_text => 'BEGIN'
1272 );
1273
1274 select dimension_id
1275 ,dimension_varchar_label
1276 ,composite_dimension_flag
1277 ,member_col
1278 ,member_b_table_name
1279 ,attribute_table_name as attr_table
1280 ,hierarchy_table_name as hier_table
1281 ,hier_versioning_type_code
1282 into x_dimension_rec
1283 from fem_xdim_dimensions_vl
1284 where dimension_varchar_label = p_dimension_varchar_label;
1285
1286 FEM_ENGINES_PKG.Tech_Message (
1287 p_severity => G_LOG_LEVEL_2
1288 ,p_module => G_BLOCK||'.'||L_API_NAME
1289 ,p_msg_text => 'END'
1290 );
1291
1292 EXCEPTION
1293
1294 when no_data_found then
1295
1296 FEM_ENGINES_PKG.User_Message (
1297 p_app_name => G_FEM
1298 ,p_msg_name => G_ENG_NO_DIM_DTL_ERR
1299 ,p_token1 => 'DIMENSION_VARCHAR_LABEL'
1300 ,p_value1 => p_dimension_varchar_label
1301 );
1302
1303 raise g_act_rate_request_error;
1304
1305 END Get_Dimension_Record;
1306
1307
1308
1309 /*===========================================================================+
1310 | PROCEDURE
1311 | Get_Dim_Attribute_Value
1312 |
1313 | DESCRIPTION
1314 | Det Dimension Attribute Values
1315 |
1316 | SCOPE - PRIVATE
1317 |
1318 +===========================================================================*/
1319
1320 PROCEDURE Get_Dim_Attribute_Value (
1321 p_dimension_varchar_label in varchar2
1322 ,p_attribute_varchar_label in varchar2
1323 ,p_member_id in number
1324 ,x_dim_attribute_varchar_member out nocopy varchar
1325 ,x_date_assign_value out nocopy date
1326 )
1327 IS
1328
1329 L_API_NAME constant varchar2(30) := 'Get_Dim_Attribute_Value';
1330
1331 l_dimension_rec dimension_record;
1332
1333 l_attribute_id number;
1334 l_attr_version_id number;
1335
1336 l_get_dim_att_val_error exception;
1337
1338 BEGIN
1339
1340 FEM_ENGINES_PKG.Tech_Message (
1341 p_severity => G_LOG_LEVEL_2
1342 ,p_module => G_BLOCK||'.'||L_API_NAME
1343 ,p_msg_text => 'BEGIN'
1344 );
1345
1346 Get_Dimension_Record (
1347 p_dimension_varchar_label => p_dimension_varchar_label
1348 ,x_dimension_rec => l_dimension_rec
1349 );
1350
1351 begin
1352 select att.attribute_id
1353 ,ver.version_id
1354 into l_attribute_id
1355 ,l_attr_version_id
1356 from fem_dim_attributes_b att
1360 and ver.attribute_id = att.attribute_id
1357 ,fem_dim_attr_versions_b ver
1358 where att.dimension_id = l_dimension_rec.dimension_id
1359 and att.attribute_varchar_label = p_attribute_varchar_label
1361 and ver.default_version_flag = 'Y';
1362 exception
1363 when others then
1364 FEM_ENGINES_PKG.User_Message (
1365 p_app_name => G_FEM
1366 ,p_msg_name => G_ENG_NO_DIM_ATTR_VER_ERR
1367 ,p_token1 => 'DIMENSION'
1368 ,p_value1 => p_dimension_varchar_label
1369 ,p_token2 => 'ATTRIBUTE'
1370 ,p_value2 => p_attribute_varchar_label
1371 );
1372 raise l_get_dim_att_val_error;
1373 end;
1374
1375 begin
1376 execute immediate
1377 ' select dim_attribute_varchar_member'||
1378 ' ,date_assign_value'||
1379 ' from '||l_dimension_rec.attr_table||
1380 ' where attribute_id = :b_attribute_id'||
1384 ,x_date_assign_value
1381 ' and version_id = :b_attr_version_id'||
1382 ' and '||l_dimension_rec.member_col||' = :b_member_id'
1383 into x_dim_attribute_varchar_member
1385 using l_attribute_id
1386 ,l_attr_version_id
1387 ,p_member_id;
1388 exception
1389 when others then
1390 FEM_ENGINES_PKG.User_Message (
1391 p_app_name => G_FEM
1392 ,p_msg_name => G_ENG_NO_DIM_ATTR_VAL_ERR
1393 ,p_token1 => 'DIMENSION'
1394 ,p_value1 => p_dimension_varchar_label
1395 ,p_token2 => 'ATTRIBUTE'
1396 ,p_value2 => p_attribute_varchar_label
1397 );
1398 raise l_get_dim_att_val_error;
1399 end;
1400
1401 FEM_ENGINES_PKG.Tech_Message (
1402 p_severity => G_LOG_LEVEL_2
1403 ,p_module => G_BLOCK||'.'||L_API_NAME
1404 ,p_msg_text => 'END'
1405 );
1406
1407 EXCEPTION
1408
1409 when l_get_dim_att_val_error then
1410
1411 FEM_ENGINES_PKG.Tech_Message (
1412 p_severity => g_log_level_6
1413 ,p_module => G_BLOCK||'.'||L_API_NAME
1414 ,p_msg_text => 'Get Dimension Attribute Value Exception'
1415 );
1416
1417 raise g_act_rate_request_error;
1418
1419 END Get_Dim_Attribute_Value;
1420
1421 /*===========================================================================+
1422 | PROCEDURE
1423 | Register_Request
1424 |
1425 | DESCRIPTION
1426 | Registers the request in the processing locks tables.
1427 |
1428 | SCOPE - PRIVATE
1429 |
1430 +===========================================================================*/
1431
1432 PROCEDURE Register_Request (
1433 p_request_rec in request_record
1434 )
1435 IS
1436
1437 L_API_NAME constant varchar2(30) := 'Register_Request';
1438
1439 l_return_status varchar2(1);
1440 l_msg_count number;
1441 l_msg_data varchar2(240);
1442
1443 l_register_request_error exception;
1444
1445 BEGIN
1446
1447 FEM_ENGINES_PKG.Tech_Message (
1448 p_severity => G_LOG_LEVEL_2
1449 ,p_module => G_BLOCK||'.'||L_API_NAME
1450 ,p_msg_text => 'BEGIN'
1451 );
1452
1453 savepoint register_request_pub;
1454
1455 -- Call the FEM_PL_PKG.Register_Request API procedure to register
1456 -- the concurrent request in FEM_PL_REQUESTS.
1457 FEM_PL_PKG.Register_Request (
1458 p_api_version => 1.0
1459 ,p_commit => FND_API.G_FALSE
1460 ,p_cal_period_id => p_request_rec.output_cal_period_id
1461 ,p_ledger_id => p_request_rec.ledger_id
1462 ,p_dataset_io_obj_def_id => p_request_rec.dataset_grp_obj_def_id
1463 ,p_output_dataset_code => p_request_rec.output_dataset_code
1464 ,p_source_system_code => p_request_rec.source_system_code
1465 ,p_effective_date => p_request_rec.effective_date
1466 ,p_rule_set_obj_def_id => p_request_rec.ruleset_obj_def_id
1467 ,p_rule_set_name => p_request_rec.ruleset_obj_name
1468 ,p_request_id => p_request_rec.request_id
1469 ,p_user_id => p_request_rec.user_id
1470 ,p_last_update_login => p_request_rec.login_id
1471 ,p_program_id => p_request_rec.pgm_id
1472 ,p_program_login_id => p_request_rec.login_id
1473 ,p_program_application_id => p_request_rec.pgm_app_id
1474 ,p_exec_mode_code => null
1475 ,p_dimension_id => null
1476 ,p_table_name => null
1477 ,p_hierarchy_name => null
1478 ,x_msg_count => l_msg_count
1479 ,x_msg_data => l_msg_data
1480 ,x_return_status => l_return_status
1481 );
1482
1483 -- Request Lock exists
1484 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1485 Get_Put_Messages (
1486 p_msg_count => l_msg_count
1487 ,p_msg_data => l_msg_data
1488 );
1489 raise l_register_request_error;
1490 end if;
1491
1492 commit;
1493
1494 FEM_ENGINES_PKG.Tech_Message (
1495 p_severity => G_LOG_LEVEL_2
1496 ,p_module => G_BLOCK||'.'||L_API_NAME
1497 ,p_msg_text => 'END'
1498 );
1499
1500 EXCEPTION
1501
1502 when l_register_request_error then
1503
1504 rollback to register_request_pub;
1505
1506 FEM_ENGINES_PKG.Tech_Message (
1507 p_severity => G_LOG_LEVEL_6
1508 ,p_module => G_BLOCK||'.'||L_API_NAME
1509 ,p_msg_text => 'Register Request Exception'
1510 );
1511
1512 raise g_act_rate_request_error;
1513
1514 when g_act_rate_request_error then
1515
1516 rollback to register_request_pub;
1517 raise g_act_rate_request_error;
1518
1519 when others then
1520
1521 rollback to register_request_pub;
1522 raise;
1523
1524 END Register_Request;
1525
1526
1527
1528 /*===========================================================================+
1529 | PROCEDURE
1530 | Act_Rate_Rule
1531 |
1532 | DESCRIPTION
1533 | Calculate Activity Rate
1534 |
1535 | SCOPE - PRIVATE
1536 |
1537 +===========================================================================*/
1538
1539 PROCEDURE Act_Rate_Rule (
1540 p_request_rec in request_record
1541 ,p_act_rate_obj_id in number
1542 ,p_act_rate_obj_def_id in number
1543 ,p_act_rate_sequence in number
1544 ,p_input_ds_b_where_clause in long
1545 ,x_return_status out nocopy varchar2
1546 )
1547 IS
1548
1549 L_API_NAME constant varchar2(30) := 'Act_Rate_Rule';
1550
1551 l_rule_rec rule_record;
1552
1556 l_rollup_parent_stmt long;
1553 l_completion_status boolean;
1554
1555 l_find_children_stmt long;
1557 l_find_child_chains_stmt long;
1558 l_num_of_input_rows_stmt long;
1559
1560 l_insert_count number;
1561
1562 -------------------------------------
1563 -- Declare bulk collection columns --
1564 -------------------------------------
1565 t_top_node_id number_type;
1566
1567 -----------------------------------------------------------
1568 -- Index indicating last row number for a cursor.
1569 -----------------------------------------------------------
1570 l_get_root_nodes_last_row number;
1571 l_get_cond_nodes_last_row number;
1572
1573 l_act_rate_rule_error exception;
1574
1575 BEGIN
1576
1577 FEM_ENGINES_PKG.Tech_Message (
1578 p_severity => G_LOG_LEVEL_2
1579 ,p_module => G_BLOCK||'.'||L_API_NAME
1580 ,p_msg_text => 'BEGIN'
1581 );
1582
1583 -- Initialize the return status to SUCCESS
1584
1585 x_return_status := FND_API.G_RET_STS_SUCCESS;
1586
1587 ------------------------------------------------------------------------------
1588 -- STEP 1: Rule Pre Processing
1589 ------------------------------------------------------------------------------
1590 FEM_ENGINES_PKG.tech_message (
1591 p_severity => G_LOG_LEVEL_1
1592 ,p_module => G_BLOCK||'.'||L_API_NAME
1593 ,p_msg_text => 'Step 1: Rule Pre Processing'
1594 );
1595
1596 Rule_Prep (
1597 p_request_rec => p_request_rec
1598 ,p_act_rate_obj_id => p_act_rate_obj_id
1599 ,p_act_rate_obj_def_id => p_act_rate_obj_def_id
1600 ,p_act_rate_sequence => p_act_rate_sequence
1601 ,x_rule_rec => l_rule_rec
1602 );
1603
1604 ------------------------------------------------------------------------------
1605 -- STEP 2: Register Rule under the same parent request
1606 ------------------------------------------------------------------------------
1607 FEM_ENGINES_PKG.tech_message (
1608 p_severity => G_LOG_LEVEL_1
1609 ,p_module => G_BLOCK||'.'||L_API_NAME
1610 ,p_msg_text => 'Step 2: Register Rule'
1611 );
1612
1613 Register_Rule (
1614 p_request_rec => p_request_rec
1615 ,p_rule_rec => l_rule_rec
1616 );
1617
1618 ------------------------------------------------------------------------------
1619 -- STEP 3: Create Temporary Objects
1620 ------------------------------------------------------------------------------
1621 FEM_ENGINES_PKG.tech_message (
1622 p_severity => G_LOG_LEVEL_1
1623 ,p_module => G_BLOCK||'.'||L_API_NAME
1624 ,p_msg_text => 'Step 3: Create Temporary Objects'
1625 );
1626
1627 Create_Temp_Objects (
1628 p_request_rec => p_request_rec
1629 ,p_rule_rec => l_rule_rec
1630 );
1631
1632 ----------------------------------------------------------------------------
1633 -- STEP 4: Call Process Drivers
1634 ----------------------------------------------------------------------------
1635 FEM_ENGINES_PKG.Tech_Message (
1636 p_severity => G_LOG_LEVEL_1
1637 ,p_module => G_BLOCK||'.'||L_API_NAME
1638 ,p_msg_text => 'Step 4: Call Process Drivers'
1639 );
1640
1641 Process_Drivers (
1642 p_request_rec => p_request_rec
1643 ,p_rule_rec => l_rule_rec
1644 ,p_insert_count => l_insert_count
1645 );
1646
1647 ------------------------------------------------------------------------------
1648 -- STEP 5: Calculate Activity Rate
1652 ,p_module => G_BLOCK||'.'||L_API_NAME
1649 ------------------------------------------------------------------------------
1650 FEM_ENGINES_PKG.Tech_Message (
1651 p_severity => G_LOG_LEVEL_1
1653 ,p_msg_text => 'Step 5: Calculate Activity Rate'
1654 );
1655
1656 Calc_Act_Rate (
1657 p_request_rec => p_request_rec
1658 ,p_rule_rec => l_rule_rec
1659 ,p_input_ds_b_where_clause => p_input_ds_b_where_clause
1660 );
1661
1662 if (g_track_event_chains) then
1663
1664 ----------------------------------------------------------------------------
1665 -- STEP 6: Register Source Chains
1666 ----------------------------------------------------------------------------
1667 FEM_ENGINES_PKG.Tech_Message (
1668 p_severity => G_LOG_LEVEL_1
1669 ,p_module => G_BLOCK||'.'||l_api_name
1670 ,p_msg_text => 'Step 6: Register Source Chains'
1671 );
1672
1673 Register_Source_Chains (
1674 p_request_id => p_request_rec.request_id
1675 ,p_act_rate_obj_id => l_rule_rec.act_rate_obj_id
1676 ,p_ledger_id => p_request_rec.ledger_id
1677 ,p_input_ds_b_where_clause => p_input_ds_b_where_clause
1678 ,p_user_id => p_request_rec.user_id
1679 ,p_login_id => p_request_rec.login_id
1680 );
1681
1682 end if;
1683
1684 ------------------------------------------------------------------------------
1685 -- STEP 7: Rule Post Processing
1686 ------------------------------------------------------------------------------
1687 FEM_ENGINES_PKG.Tech_Message (
1688 p_severity => G_LOG_LEVEL_1
1689 ,p_module => G_BLOCK||'.'||L_API_NAME
1690 ,p_msg_text => 'Step 7: Rule Post Processing'
1691 );
1692
1693 Rule_Post_Proc (
1694 p_request_rec => p_request_rec
1695 ,p_rule_rec => l_rule_rec
1696 ,p_input_ds_b_where_clause => p_input_ds_b_where_clause
1697 ,p_exec_status_code => G_EXEC_STATUS_SUCCESS
1698 );
1699
1700 FEM_ENGINES_PKG.Tech_Message (
1701 p_severity => G_LOG_LEVEL_2
1702 ,p_module => G_BLOCK||'.'||L_API_NAME
1703 ,p_msg_text => 'END'
1704 );
1705
1706 EXCEPTION
1707
1708 when l_act_rate_rule_error then
1709
1710 FEM_ENGINES_PKG.Tech_Message (
1711 p_severity => g_log_level_6
1712 ,p_module => G_BLOCK||'.'||L_API_NAME
1713 ,p_msg_text => 'Activity Rate Rule Exception'
1714 );
1715
1716 -- Rule Post Processing
1717 Rule_Post_Proc (
1718 p_request_rec => p_request_rec
1719 ,p_rule_rec => l_rule_rec
1720 ,p_input_ds_b_where_clause => p_input_ds_b_where_clause
1721 ,p_exec_status_code => G_EXEC_STATUS_ERROR_UNDO
1722 );
1723
1724 -- Proper handling of continue processing on error
1725 -- raise g_act_rate_request_error;
1726
1727 x_return_status := FND_API.G_RET_STS_ERROR;
1728
1729 when g_act_rate_request_error then
1730
1734 ,p_rule_rec => l_rule_rec
1731 -- Rule Post Processing
1732 Rule_Post_Proc (
1733 p_request_rec => p_request_rec
1735 ,p_input_ds_b_where_clause => p_input_ds_b_where_clause
1736 ,p_exec_status_code => G_EXEC_STATUS_ERROR_UNDO
1737 );
1738
1739 -- Proper handling of continue processing on error
1740 -- raise g_act_rate_request_error;
1741
1742 x_return_status := FND_API.G_RET_STS_ERROR;
1743
1744 when others then
1745
1746 g_prg_msg := SQLERRM;
1747 g_callstack := DBMS_UTILITY.Format_Call_Stack;
1748
1749 FEM_ENGINES_PKG.Tech_Message (
1750 p_severity => g_log_level_6
1751 ,p_module => G_BLOCK||'.'||L_API_NAME||'.Unexpected_Exception'
1752 ,p_msg_text => g_prg_msg
1753 );
1754
1755 FEM_ENGINES_PKG.User_Message (
1756 p_app_name => G_FEM
1757 ,p_msg_name => G_UNEXPECTED_ERROR
1758 ,p_token1 => 'ERR_MSG'
1759 ,p_value1 => g_prg_msg
1760 );
1761
1762 -- Rule Post Processing
1763 Rule_Post_Proc (
1764 p_request_rec => p_request_rec
1765 ,p_rule_rec => l_rule_rec
1766 ,p_input_ds_b_where_clause => p_input_ds_b_where_clause
1767 ,p_exec_status_code => G_EXEC_STATUS_ERROR_UNDO
1768 );
1769
1770
1771 -- Proper handling of continue processing on error
1772 -- raise g_act_rate_request_error;
1773
1774 x_return_status := FND_API.G_RET_STS_ERROR;
1775
1776 END Act_Rate_Rule;
1777
1778
1779
1783 |
1780 /*===========================================================================+
1781 | PROCEDURE
1782 | Calc_Act_Rate
1784 | DESCRIPTION
1785 | ammittal - Calculate Activity Rates
1786 |
1787 | SCOPE - PRIVATE
1788 |
1789 +===========================================================================*/
1790
1791 PROCEDURE Calc_Act_Rate (
1792 p_request_rec in request_record
1793 ,p_rule_rec in rule_record
1794 ,p_input_ds_b_where_clause in long
1795 )
1796 IS
1797
1798 L_API_NAME constant varchar2(30) := 'Calc_Act_Rate';
1799
1800 L_CALC_ACT_RATE_VALUES constant varchar2(30) := 'CALC_ACT_RATE_VALUES';
1801 L_CALC_ACT_RATE_FACTORS constant varchar2(30) := 'CALC_ACT_RATE_FACTORS';
1802
1803 l_mp_prog_status varchar2(30);
1804 l_mp_exception_code varchar2(30);
1805
1806 l_act_rate_stmt long;
1807 l_calc_fctrs_stmt long;
1808 l_drv_vals_tbl_where_clause long;
1809 l_source_table_query_stmt long;
1810 l_source_table_query_param1 number;
1811 l_source_table_query_param2 number;
1812
1813 l_return_status varchar2(1);
1814 l_msg_count number;
1815 l_msg_data varchar2(240);
1816
1817 l_calc_act_rate_error exception;
1818
1819 BEGIN
1820
1821 FEM_ENGINES_PKG.Tech_Message (
1822 p_severity => G_LOG_LEVEL_2
1823 ,p_module => G_BLOCK||'.'||L_API_NAME
1824 ,p_msg_text => 'BEGIN'
1825 );
1826
1827 -- Initialize MP variables
1828 l_mp_prog_status := G_COMPLETE_NORMAL;
1829
1830 ------------------------------------------------------------------------------
1831 -- STEP 1: Update Activity Id in Balances Table
1832 ------------------------------------------------------------------------------
1833
1834 FEM_ENGINES_PKG.Tech_Message (
1835 p_severity => G_LOG_LEVEL_1
1836 ,p_module => G_BLOCK||'.'||L_API_NAME
1837 ,p_msg_text => 'Step 1: Update Activity Id in FEM_BALANCES Table'
1838 );
1839
1840 -- A join to FEM_ACTIVITIES is
1841 -- necessary for Populate_Activity_Id to work properly as it needs all the
1842 -- component dimension columns for the join on FEM_BALANCES.
1843
1844 l_source_table_query_stmt :=
1845 ' select act.activity_id'||
1846 ' from fem_activities act'||
1847 ' ,pft_ar_driver_values_t drv'||
1848 ' where drv.CREATED_BY_REQUEST_ID = :b_request_id'||
1849 ' and drv.CREATED_BY_OBJECT_ID = :b_act_rate_obj_id'||
1850 ' and drv.activity_id = act.activity_id';
1851
1852 l_source_table_query_param1 := p_request_rec.request_id;
1853 l_source_table_query_param2 := p_rule_rec.act_rate_obj_id;
1854
1855 --todo: MP integration (???)
1856 FEM_COMPOSITE_DIM_UTILS_PVT.Populate_Activity_Id (
1857 p_api_version => 1.0
1858 ,p_init_msg_list => FND_API.G_FALSE
1859 ,p_commit => FND_API.G_TRUE
1860 ,x_return_status => l_return_status
1861 ,x_msg_count => l_msg_count
1862 ,x_msg_data => l_msg_data
1863 ,p_object_type_code => p_rule_rec.act_rate_obj_type_code
1864 ,p_source_table_query => l_source_table_query_stmt
1865 ,p_source_table_query_param1 => l_source_table_query_param1
1866 ,p_source_table_query_param2 => l_source_table_query_param2
1867 ,p_source_table_alias => 'act'
1868 ,p_target_table_name => 'FEM_BALANCES'
1869 ,p_target_table_alias => 'b'
1870 ,p_target_dsg_where_clause => p_input_ds_b_where_clause
1871 ,p_ledger_id => p_request_rec.ledger_id
1872 );
1873
1874 -- Check the return status after calling FEM_COMPOSITE_DIM_UTILS_PVT API's
1875 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1876 Get_Put_Messages (
1877 p_msg_count => l_msg_count
1878 ,p_msg_data => l_msg_data
1879 );
1880 raise l_calc_act_rate_error;
1881 end if;
1882
1883 ------------------------------------------------------------------------------
1884 -- STEP 2: Bulk Insert into Balances Table
1885 ------------------------------------------------------------------------------
1886
1887 FEM_ENGINES_PKG.Tech_Message (
1888 p_severity => G_LOG_LEVEL_1
1889 ,p_module => G_BLOCK||'.'||L_API_NAME
1890 ,p_msg_text => 'Step 2: Bulk Insert into FEM_BALANCES Table'
1891 );
1892
1893
1894 -- l_drv_vals_tbl_where_clause cannot have aliases, otherwise
1895 -- FEM_MULTI_PROC_PKG throws exception when building data slices.
1896 l_drv_vals_tbl_where_clause :=
1897 ' created_by_request_id = '||p_request_rec.request_id||
1898 ' and created_by_object_id = '||p_rule_rec.act_rate_obj_id;
1899
1900 IF ((p_rule_rec.entered_exch_rate_den IS NOT NULL) AND (p_rule_rec.entered_exch_rate_num IS NOT NULL)) THEN
1901
1902 l_act_rate_stmt :=
1903 ' insert into FEM_BALANCES ('||
1904 ' dataset_code'||
1905 ' ,cal_period_id'||
1906 ' ,creation_row_sequence'||
1910 ' ,ledger_id'||
1907 ' ,source_system_code'||
1908 ' ,currency_code'||
1909 ' ,currency_type_code'||
1911 ' ,financial_elem_id'||
1912 ' ,activity_id'||
1913 ' ,task_id'||
1914 ' ,product_id'||
1915 ' ,company_cost_center_org_id'||
1916 ' ,customer_id'||
1917 ' ,channel_id'||
1918 ' ,project_id'||
1919 ' ,user_dim1_id'||
1920 ' ,user_dim2_id'||
1921 ' ,user_dim3_id'||
1922 ' ,user_dim4_id'||
1923 ' ,user_dim5_id'||
1924 ' ,user_dim6_id'||
1925 ' ,user_dim7_id'||
1926 ' ,user_dim8_id'||
1927 ' ,user_dim9_id'||
1928 ' ,user_dim10_id'||
1929 ' ,natural_account_id'||
1930 ' ,line_item_id'||
1931 ' ,entity_id'||
1932 ' ,intercompany_id'||
1933 ' ,created_by_request_id'||
1934 ' ,created_by_object_id'||
1935 ' ,last_updated_by_request_id'||
1936 ' ,last_updated_by_object_id'||
1937 ' ,xtd_balance_e'||
1938 ' ,xtd_balance_f'||
1939 ' )'||
1940 ' select '||p_request_rec.output_dataset_code||
1941 ' ,'||p_request_rec.output_cal_period_id||
1942 ' ,'||p_rule_rec.rate_sequence_name||'.NEXTVAL'||
1943 ' ,'||p_request_rec.source_system_code||
1944 ' ,'''||p_rule_rec.entered_currency_code||''''||
1945 ' ,currency_type_code'||
1946 ' ,ledger_id'||
1947 ' ,'||G_FIN_ELEM_ID_ACTIVITY_RATE||
1948 ' ,activity_id'||
1949 ' ,task_id'||
1950 ' ,product_id'||
1951 ' ,company_cost_center_org_id'||
1952 ' ,customer_id'||
1953 ' ,channel_id'||
1954 ' ,project_id'||
1955 ' ,user_dim1_id'||
1956 ' ,user_dim2_id'||
1957 ' ,user_dim3_id'||
1958 ' ,user_dim4_id'||
1959 ' ,user_dim5_id'||
1960 ' ,user_dim6_id'||
1961 ' ,user_dim7_id'||
1962 ' ,user_dim8_id'||
1963 ' ,user_dim9_id'||
1964 ' ,user_dim10_id'||
1965 ' ,natural_account_id'||
1966 ' ,statistic_basis_id'||
1967 ' ,entity_id'||
1968 ' ,intercompany_id'||
1969 ' ,'||p_request_rec.request_id||
1970 ' ,'||p_rule_rec.act_rate_obj_id||
1971 ' ,'||p_request_rec.request_id||
1972 ' ,'||p_rule_rec.act_rate_obj_id||
1973 ' ,act_rate_value / '||p_rule_rec.entered_exch_rate_den||' * '||p_rule_rec.entered_exch_rate_num||
1974 ' ,act_rate_value'||
1975 ' from ('||
1976 ' select b.currency_type_code'||
1977 ' ,b.ledger_id'||
1978 ' ,b.activity_id'||
1979 ' ,b.task_id'||
1980 ' ,b.product_id'||
1981 ' ,b.company_cost_center_org_id'||
1982 ' ,b.customer_id'||
1983 ' ,b.channel_id'||
1984 ' ,b.project_id'||
1985 ' ,b.user_dim1_id'||
1986 ' ,b.user_dim2_id'||
1987 ' ,b.user_dim3_id'||
1988 ' ,b.user_dim4_id'||
1989 ' ,b.user_dim5_id'||
1990 ' ,b.user_dim6_id'||
1991 ' ,b.user_dim7_id'||
1992 ' ,b.user_dim8_id'||
1993 ' ,b.user_dim9_id'||
1994 ' ,b.user_dim10_id'||
1995 ' ,b.natural_account_id'||
1996 ' ,max(drv.statistic_basis_id) as statistic_basis_id'||
1997 ' ,b.entity_id'||
1998 ' ,b.intercompany_id'||
1999 ' ,sum(b.xtd_balance_f) / max(abs(drv.driver_value)) as act_rate_value'||
2000 ' from fem_balances b'||
2001 ' ,pft_ar_driver_values_t {{table_partition}} drv'||
2002 ' where b.ledger_id = '||p_request_rec.ledger_id||
2003 ' and b.financial_elem_id not in ('||G_FIN_ELEM_ID_STATISTIC||','||G_FIN_ELEM_ID_ACTIVITY_RATE||')'||
2004 ' and b.currency_type_code = ''ENTERED'''||
2005 ' and '||p_input_ds_b_where_clause||
2006 ' and not ('||
2007 ' b.created_by_request_id = '||p_request_rec.request_id||
2008 ' and b.created_by_object_id = '||p_rule_rec.act_rate_obj_id||
2009 ' )'||
2010 ' and drv.activity_id = b.activity_id'||
2011 -- l_drv_vals_tbl_where_clause with aliases (start)
2012 ' and drv.created_by_request_id = '||p_request_rec.request_id||
2013 ' and drv.created_by_object_id = '||p_rule_rec.act_rate_obj_id||
2014 -- l_drv_vals_tbl_where_clause with aliases (end)
2015 ' and {{data_slice}} '||
2016 ' group by b.currency_type_code'||
2017 ' ,b.currency_type_code'||
2018 ' ,b.ledger_id'||
2019 ' ,b.activity_id'||
2020 ' ,b.task_id'||
2021 ' ,b.product_id'||
2022 ' ,b.company_cost_center_org_id'||
2023 ' ,b.customer_id'||
2024 ' ,b.channel_id'||
2025 ' ,b.project_id'||
2026 ' ,b.user_dim1_id'||
2027 ' ,b.user_dim2_id'||
2028 ' ,b.user_dim3_id'||
2029 ' ,b.user_dim4_id'||
2030 ' ,b.user_dim5_id'||
2031 ' ,b.user_dim6_id'||
2032 ' ,b.user_dim7_id'||
2033 ' ,b.user_dim8_id'||
2034 ' ,b.user_dim9_id'||
2035 ' ,b.user_dim10_id'||
2036 ' ,b.natural_account_id'||
2037 -- Bug fix 4619775 - ammittal 10/07/05 - The code has been commented
2038 -- to avoid grouping by line item id as the statistic basis id from
2039 -- drivers table is always used in the select statement
2040 -- ' ,b.line_item_id'||
2041 ' ,b.entity_id'||
2042 ' ,b.intercompany_id'||
2043 ' )';
2044
2045 ELSE
2046
2047 l_act_rate_stmt :=
2048 ' insert into FEM_BALANCES ('||
2049 ' dataset_code'||
2050 ' ,cal_period_id'||
2051 ' ,creation_row_sequence'||
2052 ' ,source_system_code'||
2053 ' ,currency_code'||
2054 ' ,currency_type_code'||
2055 ' ,ledger_id'||
2056 ' ,financial_elem_id'||
2057 ' ,activity_id'||
2058 ' ,task_id'||
2059 ' ,product_id'||
2060 ' ,company_cost_center_org_id'||
2061 ' ,customer_id'||
2062 ' ,channel_id'||
2063 ' ,project_id'||
2064 ' ,user_dim1_id'||
2065 ' ,user_dim2_id'||
2066 ' ,user_dim3_id'||
2067 ' ,user_dim4_id'||
2068 ' ,user_dim5_id'||
2069 ' ,user_dim6_id'||
2070 ' ,user_dim7_id'||
2071 ' ,user_dim8_id'||
2072 ' ,user_dim9_id'||
2073 ' ,user_dim10_id'||
2074 ' ,natural_account_id'||
2075 ' ,line_item_id'||
2076 ' ,entity_id'||
2077 ' ,intercompany_id'||
2078 ' ,created_by_request_id'||
2079 ' ,created_by_object_id'||
2080 ' ,last_updated_by_request_id'||
2081 ' ,last_updated_by_object_id'||
2082 ' ,xtd_balance_e'||
2083 ' ,xtd_balance_f'||
2084 ' )'||
2085 ' select '||p_request_rec.output_dataset_code||
2086 ' ,'||p_request_rec.output_cal_period_id||
2087 ' ,'||p_rule_rec.rate_sequence_name||'.NEXTVAL'||
2088 ' ,'||p_request_rec.source_system_code||
2089 ' ,'''||p_rule_rec.entered_currency_code||''''||
2090 ' ,currency_type_code'||
2091 ' ,ledger_id'||
2092 ' ,'||G_FIN_ELEM_ID_ACTIVITY_RATE||
2093 ' ,activity_id'||
2094 ' ,task_id'||
2095 ' ,product_id'||
2096 ' ,company_cost_center_org_id'||
2097 ' ,customer_id'||
2098 ' ,channel_id'||
2099 ' ,project_id'||
2103 ' ,user_dim4_id'||
2100 ' ,user_dim1_id'||
2101 ' ,user_dim2_id'||
2102 ' ,user_dim3_id'||
2104 ' ,user_dim5_id'||
2105 ' ,user_dim6_id'||
2106 ' ,user_dim7_id'||
2107 ' ,user_dim8_id'||
2108 ' ,user_dim9_id'||
2109 ' ,user_dim10_id'||
2110 ' ,natural_account_id'||
2111 ' ,statistic_basis_id'||
2112 ' ,entity_id'||
2113 ' ,intercompany_id'||
2114 ' ,'||p_request_rec.request_id||
2115 ' ,'||p_rule_rec.act_rate_obj_id||
2116 ' ,'||p_request_rec.request_id||
2117 ' ,'||p_rule_rec.act_rate_obj_id||
2118 ' ,null'||
2119 ' ,act_rate_value'||
2120 ' from ('||
2121 ' select b.currency_type_code'||
2122 ' ,b.ledger_id'||
2123 ' ,b.activity_id'||
2124 ' ,b.task_id'||
2125 ' ,b.product_id'||
2126 ' ,b.company_cost_center_org_id'||
2127 ' ,b.customer_id'||
2128 ' ,b.channel_id'||
2129 ' ,b.project_id'||
2130 ' ,b.user_dim1_id'||
2131 ' ,b.user_dim2_id'||
2132 ' ,b.user_dim3_id'||
2133 ' ,b.user_dim4_id'||
2134 ' ,b.user_dim5_id'||
2135 ' ,b.user_dim6_id'||
2136 ' ,b.user_dim7_id'||
2137 ' ,b.user_dim8_id'||
2138 ' ,b.user_dim9_id'||
2139 ' ,b.user_dim10_id'||
2140 ' ,b.natural_account_id'||
2141 ' ,max(drv.statistic_basis_id) as statistic_basis_id'||
2142 ' ,b.entity_id'||
2143 ' ,b.intercompany_id'||
2144 ' ,sum(b.xtd_balance_f) / max(abs(drv.driver_value)) as act_rate_value'||
2145 ' from fem_balances b'||
2146 ' ,pft_ar_driver_values_t {{table_partition}} drv'||
2147 ' where b.ledger_id = '||p_request_rec.ledger_id||
2148 ' and b.financial_elem_id not in ('||G_FIN_ELEM_ID_STATISTIC||','||G_FIN_ELEM_ID_ACTIVITY_RATE||')'||
2149 ' and b.currency_type_code = ''ENTERED'''||
2150 ' and '||p_input_ds_b_where_clause||
2151 ' and not ('||
2152 ' b.created_by_request_id = '||p_request_rec.request_id||
2153 ' and b.created_by_object_id = '||p_rule_rec.act_rate_obj_id||
2154 ' )'||
2155 ' and drv.activity_id = b.activity_id'||
2156 -- l_drv_vals_tbl_where_clause with aliases (start)
2157 ' and drv.created_by_request_id = '||p_request_rec.request_id||
2158 ' and drv.created_by_object_id = '||p_rule_rec.act_rate_obj_id||
2159 -- l_drv_vals_tbl_where_clause with aliases (end)
2160 ' and {{data_slice}} '||
2161 ' group by b.currency_type_code'||
2162 ' ,b.currency_type_code'||
2163 ' ,b.ledger_id'||
2164 ' ,b.activity_id'||
2165 ' ,b.task_id'||
2166 ' ,b.product_id'||
2167 ' ,b.company_cost_center_org_id'||
2168 ' ,b.customer_id'||
2169 ' ,b.channel_id'||
2170 ' ,b.project_id'||
2171 ' ,b.user_dim1_id'||
2172 ' ,b.user_dim2_id'||
2173 ' ,b.user_dim3_id'||
2174 ' ,b.user_dim4_id'||
2175 ' ,b.user_dim5_id'||
2176 ' ,b.user_dim6_id'||
2177 ' ,b.user_dim7_id'||
2178 ' ,b.user_dim8_id'||
2179 ' ,b.user_dim9_id'||
2180 ' ,b.user_dim10_id'||
2181 ' ,b.natural_account_id'||
2182 -- Bug fix 4619775 - ammittal 10/07/05 - The code has been commented
2183 -- to avoid grouping by line item id as the statistic basis id from
2184 -- drivers table is always used in the select statement
2185 -- ' ,b.line_item_id'||
2186 ' ,b.entity_id'||
2187 ' ,b.intercompany_id'||
2188 ' )';
2189
2190
2191 END IF;
2192
2193 -- Register Object Execution Step
2194 Register_Obj_Exec_Step (
2195 p_request_rec => p_request_rec
2199 );
2196 ,p_rule_rec => p_rule_rec
2197 ,p_exec_step => L_CALC_ACT_RATE_VALUES
2198 ,p_exec_status_code => G_EXEC_STATUS_RUNNING
2200
2201 -- Call MP API if MP is enabled, otherwise execute SQL directly
2202 if (G_MP_ENABLED) then
2203
2204 -- Call MP Master (Pull Processing)
2205 FEM_MULTI_PROC_PKG.Master (
2206 x_prg_stat => l_mp_prog_status
2207 ,x_exception_code => l_mp_exception_code
2208 ,p_rule_id => p_rule_rec.act_rate_obj_id
2209 ,p_eng_step => L_CALC_ACT_RATE_VALUES
2210 ,p_data_table => 'PFT_AR_DRIVER_VALUES_T'
2211 ,p_eng_sql => l_act_rate_stmt
2212 ,p_table_alias => 'drv'
2213 ,p_run_name => L_CALC_ACT_RATE_VALUES
2214 ,p_eng_prg => null
2215 ,p_condition => l_drv_vals_tbl_where_clause
2216 ,p_failed_req_id => null
2217 ,p_reuse_slices => 'N' -- New data slice
2218 );
2219
2220 else
2221
2222 -- Replace the data slice and table partition tokens
2223 l_act_rate_stmt := REPLACE(l_act_rate_stmt,'{{data_slice}}',' 1=1 ');
2224 l_act_rate_stmt := REPLACE(l_act_rate_stmt,'{{table_partition}}',' ');
2225
2226 execute immediate l_act_rate_stmt;
2227
2228 end if;
2229
2230 if (l_mp_prog_status <> G_COMPLETE_NORMAL) then
2231
2232 if (l_mp_exception_code is not null) then
2233 FEM_ENGINES_PKG.User_Message (
2234 p_app_name => G_FEM
2235 ,p_msg_name => l_mp_exception_code
2236 );
2237 end if;
2238
2239 Update_Obj_Exec_Step_Status(
2240 p_request_rec => p_request_rec
2241 ,p_rule_rec => p_rule_rec
2242 ,p_exec_step => L_CALC_ACT_RATE_VALUES
2243 ,p_exec_status_code => G_EXEC_STATUS_ERROR_UNDO
2244 );
2245
2246 raise l_calc_act_rate_error;
2247
2248 else
2249
2250 Update_Obj_Exec_Step_Status(
2251 p_request_rec => p_request_rec
2252 ,p_rule_rec => p_rule_rec
2253 ,p_exec_step => L_CALC_ACT_RATE_VALUES
2254 ,p_exec_status_code => G_EXEC_STATUS_SUCCESS
2255 );
2256
2257 end if;
2258
2259 commit;
2260
2261 if (G_MP_ENABLED) then
2262 -- Purge Data Slices
2263 FEM_MULTI_PROC_PKG.Delete_Data_Slices (
2264 p_req_id => p_request_rec.request_id
2265 );
2266 end if;
2267
2268 ------------------------------------------------------------------------------
2269 -- STEP 3: Bulk Insert into FEM_BALANCES_CALC_FCTRS Table
2270 ------------------------------------------------------------------------------
2271
2272 FEM_ENGINES_PKG.Tech_Message (
2273 p_severity => G_LOG_LEVEL_1
2274 ,p_module => G_BLOCK||'.'||L_API_NAME
2275 ,p_msg_text => 'Step 3: Bulk Insert into FEM_BALANCES_CALC_FCTRS Table'
2276 );
2277
2278 -- Prepare the insert statement for FEM_BALANCES_CALC_FCTRS
2279 -- ammittal 03/30/06 - Bug # 5074996 - Updating the code below to uptake changes in FEM_BALANCES_CALC_FCTRS table
2280 l_calc_fctrs_stmt :=
2281 ' insert into FEM_BALANCES_CALC_FCTRS ('||
2282 ' created_by_request_id'||
2283 ' ,created_by_object_id'||
2284 ' ,creation_row_sequence'||
2285 ' ,factor'||
2289 ' )'||
2286 ' ,output_type'||
2287 ' ,last_updated_by_object_id'||
2288 ' ,last_updated_by_request_id'||
2290 ' select b.created_by_request_id'||
2291 ' ,b.created_by_object_id'||
2292 ' ,b.creation_row_sequence'||
2293 ' ,1/drv.driver_value'||
2294 ' ,''N/A'''||
2295 ' ,b.last_updated_by_object_id'||
2296 ' ,b.last_updated_by_request_id'||
2297 ' from fem_balances b'||
2298 ' ,pft_ar_driver_values_t {{table_partition}} drv'||
2302 ' and b.created_by_request_id = drv.created_by_request_id'||
2299 ' where b.ledger_id = '||p_request_rec.ledger_id||
2300 ' and b.dataset_code = '||p_request_rec.output_dataset_code||
2301 ' and b.cal_period_id = '||p_request_rec.output_cal_period_id||
2303 ' and b.created_by_object_id = drv.created_by_object_id'||
2304 ' and drv.activity_id = b.activity_id'||
2305 -- l_drv_vals_tbl_where_clause with aliases (start)
2306 ' and drv.created_by_request_id = '||p_request_rec.request_id||
2307 ' and drv.created_by_object_id = '||p_rule_rec.act_rate_obj_id||
2308 -- l_drv_vals_tbl_where_clause with aliases (end)
2309 ' and {{data_slice}} ';
2310
2311 -- Register Object Execution Step
2312 Register_Obj_Exec_Step (
2313 p_request_rec => p_request_rec
2314 ,p_rule_rec => p_rule_rec
2315 ,p_exec_step => L_CALC_ACT_RATE_FACTORS
2316 ,p_exec_status_code => G_EXEC_STATUS_RUNNING
2317 );
2318
2319 -- Call MP API if MP is enabled, otherwise execute SQL directly
2320 if (G_MP_ENABLED) then
2321
2322 -- Call MP Master (Pull Processing)
2323 FEM_MULTI_PROC_PKG.Master (
2324 x_prg_stat => l_mp_prog_status
2325 ,x_exception_code => l_mp_exception_code
2326 ,p_rule_id => p_rule_rec.act_rate_obj_id
2327 ,p_eng_step => L_CALC_ACT_RATE_FACTORS
2328 ,p_data_table => 'PFT_AR_DRIVER_VALUES_T'
2329 ,p_eng_sql => l_calc_fctrs_stmt
2330 ,p_table_alias => 'drv'
2331 ,p_run_name => L_CALC_ACT_RATE_FACTORS
2332 ,p_eng_prg => null
2333 ,p_condition => l_drv_vals_tbl_where_clause
2334 ,p_failed_req_id => null
2335 ,p_reuse_slices => 'N' -- New data slice
2336 );
2337
2338 else
2339
2340 -- Replace the data slice and table partition tokens
2341 l_calc_fctrs_stmt := REPLACE(l_calc_fctrs_stmt,'{{data_slice}}',' 1=1 ');
2342 l_calc_fctrs_stmt := REPLACE(l_calc_fctrs_stmt,'{{table_partition}}',' ');
2343
2344 execute immediate l_calc_fctrs_stmt;
2345
2346 end if;
2347
2348 if (l_mp_prog_status <> G_COMPLETE_NORMAL) then
2349
2350 if (l_mp_exception_code is not null) then
2351 FEM_ENGINES_PKG.User_Message (
2352 p_app_name => G_FEM
2353 ,p_msg_name => l_mp_exception_code
2354 );
2355 end if;
2356
2357 Update_Obj_Exec_Step_Status(
2358 p_request_rec => p_request_rec
2359 ,p_rule_rec => p_rule_rec
2360 ,p_exec_step => L_CALC_ACT_RATE_FACTORS
2361 ,p_exec_status_code => G_EXEC_STATUS_ERROR_UNDO
2362 );
2363
2364 raise l_calc_act_rate_error;
2365
2366 else
2367
2368 Update_Obj_Exec_Step_Status(
2369 p_request_rec => p_request_rec
2370 ,p_rule_rec => p_rule_rec
2371 ,p_exec_step => L_CALC_ACT_RATE_FACTORS
2372 ,p_exec_status_code => G_EXEC_STATUS_SUCCESS
2373 );
2374
2375 end if;
2376
2377 commit;
2378
2379 if (G_MP_ENABLED) then
2380 -- Purge Data Slices
2381 FEM_MULTI_PROC_PKG.Delete_Data_Slices (
2382 p_req_id => p_request_rec.request_id
2383 );
2384 end if;
2385
2386 FEM_ENGINES_PKG.Tech_Message (
2387 p_severity => G_LOG_LEVEL_2
2388 ,p_module => G_BLOCK||'.'||L_API_NAME
2389 ,p_msg_text => 'END'
2390 );
2391
2392 EXCEPTION
2393
2394 when l_calc_act_rate_error then
2395
2396 FEM_ENGINES_PKG.Tech_Message (
2397 p_severity => g_log_level_6
2398 ,p_module => G_BLOCK||'.'||L_API_NAME
2399 ,p_msg_text => 'Calculate Activity Rate Exception'
2400 );
2401
2402 raise g_act_rate_request_error;
2403
2404 END Calc_Act_Rate;
2405
2406
2407
2408 /*===========================================================================+
2409 | PROCEDURE
2410 | Rule_Prep
2411 |
2412 | DESCRIPTION
2413 | Rule Preperation
2414 |
2415 | SCOPE - PRIVATE
2416 |
2417 +===========================================================================*/
2418
2419 PROCEDURE Rule_Prep (
2420 p_request_rec in request_record
2421 ,p_act_rate_obj_id in number
2422 ,p_act_rate_obj_def_id in number
2423 ,p_act_rate_sequence in number
2424 ,x_rule_rec out nocopy rule_record
2425 )
2426 IS
2427
2428 L_API_NAME constant varchar2(30) := 'Rule_Prep';
2429
2430 l_dimension_id number;
2431
2432 l_rule_prep_error exception;
2433
2434 BEGIN
2435
2436 FEM_ENGINES_PKG.Tech_Message (
2437 p_severity => G_LOG_LEVEL_2
2438 ,p_module => G_BLOCK||'.'||L_API_NAME
2439 ,p_msg_text => 'BEGIN'
2440 );
2441
2442 x_rule_rec.act_rate_obj_id := p_act_rate_obj_id;
2443 x_rule_rec.act_rate_obj_def_id := p_act_rate_obj_def_id;
2444 x_rule_rec.act_rate_sequence := p_act_rate_sequence;
2445
2446 ------------------------------------------------------------------------------
2447 -- Get the object info from FEM_OBJECT_CATALOG_B for the activity rate Object ID.
2448 ------------------------------------------------------------------------------
2449 begin
2450 select object_type_code
2451 ,object_name
2452 ,local_vs_combo_id
2453 into x_rule_rec.act_rate_obj_type_code
2454 ,x_rule_rec.act_rate_obj_name
2455 ,x_rule_rec.local_vs_combo_id
2456 from fem_object_catalog_vl
2457 where object_id = x_rule_rec.act_rate_obj_id;
2458 exception
2459 when others then
2460 FEM_ENGINES_PKG.User_Message (
2461 p_app_name => G_FEM
2462 ,p_msg_name => G_ENG_NO_ACT_RATE_OBJ_ERR
2463 ,p_token1 => 'OBJECT_ID'
2464 ,p_value1 => x_rule_rec.act_rate_obj_id
2465 );
2466 raise l_rule_prep_error;
2467 end;
2468
2469 ------------------------------------------------------------------------------
2470 -- If this is a Rule Set Submission, check that the object_type_code and
2471 -- local_vs_combo_id of the activity rate rule matches the Rule Set's.
2472 ------------------------------------------------------------------------------
2476 p_app_name => G_FEM
2473 if (p_request_rec.submit_obj_type_code = 'RULE_SET') then
2474
2475 FEM_ENGINES_PKG.User_Message (
2477 ,p_msg_name => G_ENG_RS_RULE_PROCESSING_TXT
2478 ,p_token1 => 'RULE_NAME'
2479 ,p_value1 => x_rule_rec.act_rate_obj_name
2480 );
2481
2482 if (p_request_rec.act_rate_obj_type_code <> x_rule_rec.act_rate_obj_type_code) then
2483
2484 FEM_ENGINES_PKG.User_Message (
2485 p_app_name => G_FEM
2486 ,p_msg_name => G_ENG_BAD_RS_OBJ_TYPE_ERR
2487 ,p_token1 => 'OBJECT_TYPE_CODE'
2488 ,p_value1 => p_request_rec.act_rate_obj_type_code
2489 );
2490 raise l_rule_prep_error;
2491
2492 end if;
2493
2494 if (p_request_rec.local_vs_combo_id <> x_rule_rec.local_vs_combo_id) then
2495
2496 FEM_ENGINES_PKG.User_Message (
2497 p_app_name => G_FEM
2498 ,p_msg_name => G_ENG_RS_BAD_LCL_VS_COMBO_ERR
2499 ,p_token1 => 'OBJECT_TYPE_MEANING'
2500 ,p_value1 => Get_Lookup_Meaning('FEM_OBJECT_TYPE_DSC',x_rule_rec.act_rate_obj_type_code)
2501 ,p_token2 => 'OBJECT_ID'
2502 ,p_value2 => x_rule_rec.act_rate_obj_id
2503 );
2504 raise l_rule_prep_error;
2505
2506 end if;
2507
2508 end if;
2509
2510 ------------------------------------------------------------------------------
2511 -- Get the Activity Rate Object Definition ID
2512 ------------------------------------------------------------------------------
2513 if (x_rule_rec.act_rate_obj_def_id is null) then
2514
2515 Get_Object_Definition (
2516 p_object_type_code => x_rule_rec.act_rate_obj_type_code
2517 ,p_object_id => x_rule_rec.act_rate_obj_id
2518 ,p_effective_date => p_request_rec.effective_date
2519 ,x_obj_def_id => x_rule_rec.act_rate_obj_def_id
2520 );
2521
2522 end if;
2523
2524 begin
2525 select activity_hier_obj_id,
2526 currency_code,
2527 condition_obj_id,
2528 top_nodes_flag,
2529 output_to_rate_stat_flag
2530 into x_rule_rec.hier_obj_id
2531 ,x_rule_rec.entered_currency_code
2532 ,x_rule_rec.cond_obj_id
2533 ,x_rule_rec.top_node_flag
2534 ,x_rule_rec.output_to_rate_stat_flag
2535 from PFT_ACTIVITY_RATES
2536 where activity_rate_obj_def_id = x_rule_rec.act_rate_obj_def_id;
2537 exception
2538 when others then
2539 FEM_ENGINES_PKG.User_Message (
2540 p_app_name => G_FEM
2541 ,p_msg_name => G_ENG_NO_ACT_RATE_OBJ_DTL_ERR
2542 ,p_token1 => 'TABLE_NAME'
2543 ,p_value1 => 'PFT_ACTIVITY_RATES'
2544 ,p_token2 => 'OBJECT_TYPE_MEANING'
2545 ,p_value2 => Get_Lookup_Meaning('FEM_OBJECT_TYPE_DSC',x_rule_rec.act_rate_obj_type_code)
2546 ,p_token3 => 'OBJECT_ID'
2547 ,p_value3 => x_rule_rec.act_rate_obj_id
2548 ,p_token4 => 'OBJECT_DEF_ID'
2549 ,p_value4 => x_rule_rec.act_rate_obj_def_id
2550 );
2551 raise l_rule_prep_error;
2552 end;
2553 ------------------------------------------------------------------------------
2554 -- Get the Hierarchy Object Definition ID
2555 ------------------------------------------------------------------------------
2556 Get_Object_Definition (
2557 p_object_type_code => 'HIERARCHY'
2558 ,p_object_id => x_rule_rec.hier_obj_id
2559 ,p_effective_date => p_request_rec.effective_date
2560 ,x_obj_def_id => x_rule_rec.hier_obj_def_id
2561 );
2562
2563 ------------------------------------------------------------------------------
2564 -- Get the Condition Object Definition ID (if specified)
2565 ------------------------------------------------------------------------------
2566 x_rule_rec.cond_exists := (x_rule_rec.cond_obj_id is not null);
2567 if (x_rule_rec.cond_exists) then
2568
2569 Get_Object_Definition (
2570 p_object_type_code => 'CONDITION'
2571 ,p_object_id => x_rule_rec.cond_obj_id
2572 ,p_effective_date => p_request_rec.effective_date
2573 ,x_obj_def_id => x_rule_rec.cond_obj_def_id
2574 );
2575
2576 end if;
2577
2581 begin
2578 ------------------------------------------------------------------------------
2579 -- Get Dimension Id from FEM_HIERARCHIES
2580 ------------------------------------------------------------------------------
2582 select h.dimension_id
2583 into l_dimension_id
2584 from fem_hierarchies h
2585 where h.hierarchy_obj_id = x_rule_rec.hier_obj_id;
2586 exception
2587 when others then
2588 FEM_ENGINES_PKG.User_Message (
2589 p_app_name => G_FEM
2590 ,p_msg_name => G_ENG_NO_OBJ_DTL_ERR
2591 ,p_token1 => 'TABLE_NAME'
2592 ,p_value1 => 'FEM_HIERARCHIES'
2593 ,p_token2 => 'OBJECT_TYPE_MEANING'
2594 ,p_value2 => Get_Lookup_Meaning('FEM_OBJECT_TYPE_DSC',x_rule_rec.act_rate_obj_type_code)
2595 ,p_token3 => 'OBJECT_ID'
2596 ,p_value3 => x_rule_rec.hier_obj_id
2597 );
2598 raise l_rule_prep_error;
2599 end;
2600
2601 -- Check that the dimension Id matches that of the Request
2602 if (p_request_rec.dimension_rec.dimension_id <> l_dimension_id) then
2603
2604 FEM_ENGINES_PKG.User_Message (
2605 p_app_name => G_FEM
2606 ,p_msg_name => G_ENG_BAD_HIER_DIM_ERR
2607 ,p_token1 => 'OBJECT_TYPE_MEANING'
2608 ,p_value1 => Get_Lookup_Meaning('FEM_OBJECT_TYPE_DSC',x_rule_rec.act_rate_obj_type_code)
2609 ,p_token2 => 'OBJECT_ID'
2610 ,p_value2 => x_rule_rec.act_rate_obj_id
2611 ,p_token3 => 'OBJECT_DEF_ID'
2612 ,p_value3 => x_rule_rec.act_rate_obj_def_id
2613 );
2614 raise l_rule_prep_error;
2615
2616 end if;
2617
2618 ------------------------------------------------------------------------------
2619 -- Set the Temporary Sequence Names for performing Processing in the
2620 -- FEM_BALANCES table.
2621 ------------------------------------------------------------------------------
2622 x_rule_rec.rate_sequence_name :=
2623 'pft_ar_rate_'||
2624 to_char(p_request_rec.request_id)||
2625 '_'||
2626 to_char(x_rule_rec.act_rate_sequence)||
2627 '_s';
2628
2629 x_rule_rec.drv_sequence_name :=
2630 'pft_ar_drv_'||
2631 to_char(p_request_rec.request_id)||
2632 '_'||
2633 to_char(x_rule_rec.act_rate_sequence)||
2634 '_s';
2635
2636 ------------------------------------------------------------------------------
2637 -- Set the Entered Currency Code and Exchange Rate params
2638 ------------------------------------------------------------------------------
2639 if (p_request_rec.entered_currency_flag = 'N') then
2640
2641 -- Set the Entered Currency to the Ledger's Functional Currency as the
2642 -- Ledger does not allow Entered Balances.
2643 -- Also default the exchange rate to null, so that all entered balances will
2644 -- result to null.
2645 x_rule_rec.entered_currency_code := p_request_rec.functional_currency_code;
2646 x_rule_rec.entered_exch_rate_num := null;
2647 x_rule_rec.entered_exch_rate_den := null;
2648 x_rule_rec.entered_exch_rate := null;
2649
2650 else
2651
2652 -- ammittal todo: find what the "Functional" currency code string value will be.
2653 if (x_rule_rec.entered_currency_code = 'FUNCTIONAL' ) then
2654
2655 -- Set the Entered Currency to the Ledger's Functional Currency
2656 -- Also default the exchange rate to 1.
2657 x_rule_rec.entered_currency_code := p_request_rec.functional_currency_code;
2658 x_rule_rec.entered_exch_rate_num := 1;
2659 x_rule_rec.entered_exch_rate_den := 1;
2660 x_rule_rec.entered_exch_rate := 1;
2661
2662 else
2663
2664 if (x_rule_rec.entered_currency_code = p_request_rec.functional_currency_code) then
2665
2666 -- Default the exchange rate to 1 as the Entered Currency is the same as
2667 -- the Ledger's Functional Currency
2668 x_rule_rec.entered_exch_rate_num := 1;
2669 x_rule_rec.entered_exch_rate_den := 1;
2670 x_rule_rec.entered_exch_rate := 1;
2671
2672 else
2673
2674 begin
2675 GL_CURRENCY_API.Get_Triangulation_Rate (
2676 x_from_currency => p_request_rec.functional_currency_code
2677 ,x_to_currency => x_rule_rec.entered_currency_code
2678 ,x_conversion_date => p_request_rec.entered_exch_rate_date
2679 ,x_conversion_type => g_currency_conv_type
2680 ,x_numerator => x_rule_rec.entered_exch_rate_num
2681 ,x_denominator => x_rule_rec.entered_exch_rate_den
2682 ,x_rate => x_rule_rec.entered_exch_rate
2683 );
2684 exception
2685 when GL_CURRENCY_API.NO_RATE then
2686 FEM_ENGINES_PKG.User_Message (
2687 p_app_name => G_FEM
2688 ,p_msg_name => G_ENG_NO_EXCH_RATE_FOUND_ERR
2689 ,p_token1 => 'FROM_CURRENCY_CODE'
2690 ,p_value1 => p_request_rec.functional_currency_code
2691 ,p_token2 => 'TO_CURRENCY_CODE'
2692 ,p_value2 => x_rule_rec.entered_currency_code
2693 ,p_token3 => 'CONVERSION_DATE'
2694 ,p_value3 => FND_DATE.date_to_chardate(p_request_rec.entered_exch_rate_date)
2695 ,p_token4 => 'CONVERSION_TYPE'
2696 ,p_value4 => g_currency_conv_type
2697 );
2698 raise l_rule_prep_error;
2699
2700 when GL_CURRENCY_API.INVALID_CURRENCY then
2701 FEM_ENGINES_PKG.User_Message (
2702 p_app_name => G_FEM
2703 ,p_msg_name => G_ENG_BAD_CURRENCY_ERR
2704 ,p_token1 => 'FROM_CURRENCY_CODE'
2705 ,p_value1 => p_request_rec.functional_currency_code
2706 ,p_token2 => 'TO_CURRENCY_CODE'
2707 ,p_value2 => x_rule_rec.entered_currency_code
2708 ,p_token3 => 'CONVERSION_DATE'
2712 );
2709 ,p_value3 => FND_DATE.date_to_chardate(p_request_rec.entered_exch_rate_date)
2710 ,p_token4 => 'CONVERSION_TYPE'
2711 ,p_value4 => g_currency_conv_type
2713 raise l_rule_prep_error;
2714
2715 end;
2716
2717 end if;
2718
2719 end if;
2720
2721 end if;
2722
2723 -- Log all Rule Record Parameters if we have low level debugging
2724 if ( FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) ) then
2725
2726
2727 FEM_ENGINES_PKG.Tech_Message (
2728 p_severity => G_LOG_LEVEL_1
2729 ,p_module => G_BLOCK||'.'||L_API_NAME||'.x_rule_rec'
2730 ,p_msg_text =>
2731 ' act_rate_obj_id='||x_rule_rec.act_rate_obj_id||
2732 ' act_rate_obj_def_id='||x_rule_rec.act_rate_obj_def_id||
2733 ' act_rate_obj_type_code ='||x_rule_rec.act_rate_obj_type_code||
2734 ' act_rate_sequence ='||x_rule_rec.act_rate_sequence||
2735 ' cond_obj_def_id ='||x_rule_rec.cond_obj_def_id||
2736 ' cond_obj_id ='||x_rule_rec.cond_obj_id||
2737 ' entered_currency_code ='||x_rule_rec.entered_currency_code ||
2738 ' entered_exch_rate ='||x_rule_rec.entered_exch_rate ||
2739 ' entered_exch_rate_den ='||x_rule_rec.entered_exch_rate_den ||
2740 ' entered_exch_rate_num ='||x_rule_rec.entered_exch_rate_num ||
2741 ' hier_obj_def_id ='||x_rule_rec.hier_obj_def_id ||
2742 ' local_vs_combo_id ='||x_rule_rec.local_vs_combo_id ||
2743 ' rate_sequence_name ='||x_rule_rec.rate_sequence_name ||
2744 ' drv_sequence_name ='||x_rule_rec.drv_sequence_name ||
2745 ' output_to_rate_stat_flag ='||x_rule_rec.output_to_rate_stat_flag ||
2746 ' top_node_flag ='||x_rule_rec.top_node_flag
2747 );
2748
2749 FEM_ENGINES_PKG.Tech_Message (
2750 p_severity => G_LOG_LEVEL_1
2751 ,p_module => G_BLOCK||'.'||l_api_name||'.x_rule_rec'
2752 ,p_msg_text => null
2753 );
2754
2755 end if;
2756
2757 FEM_ENGINES_PKG.Tech_Message (
2758 p_severity => G_LOG_LEVEL_2
2759 ,p_module => G_BLOCK||'.'||L_API_NAME
2760 ,p_msg_text => 'END'
2761 );
2762
2763 EXCEPTION
2764
2765 when l_rule_prep_error then
2766
2767 FEM_ENGINES_PKG.Tech_Message (
2768 p_severity => g_log_level_6
2769 ,p_module => G_BLOCK||'.'||L_API_NAME
2770 ,p_msg_text => 'Rule Preparation Exception'
2771 );
2772
2773 raise g_act_rate_request_error;
2774
2775 END Rule_Prep;
2776
2777
2778
2779 /*===========================================================================+
2780 | PROCEDURE
2781 | Register_Rule
2782 |
2783 | DESCRIPTION
2784 | Register Objects - Called from Act_Rate_Rule
2785 |
2786 | SCOPE - PRIVATE
2787 |
2788 +===========================================================================*/
2789
2790 PROCEDURE Register_Rule (
2791 p_request_rec in request_record
2792 ,p_rule_rec in rule_record
2793 )
2794 IS
2795
2796 L_API_NAME constant varchar2(30) := 'Register_Rule';
2797
2798 l_exec_state varchar2(30); -- normal, restart, rerun
2799 l_prev_request_id number;
2800
2801 l_return_status varchar2(1);
2802 l_msg_count number;
2803 l_msg_data varchar2(240);
2804
2805 l_register_rule_error exception;
2806
2807 BEGIN
2808
2809 FEM_ENGINES_PKG.Tech_Message (
2810 p_severity => G_LOG_LEVEL_2
2811 ,p_module => G_BLOCK||'.'||L_API_NAME
2812 ,p_msg_text => 'BEGIN'
2813 );
2814
2815 savepoint register_rule_pub;
2816
2817 -- Call the FEM_PL_PKG.Register_Object_Execution API procedure to register
2818 -- the activity rate object execution in FEM_PL_OBJECT_EXECUTIONS, thus obtaining
2819 -- an execution lock.
2820 FEM_PL_PKG.Register_Object_Execution (
2821 p_api_version => 1.0
2822 ,p_commit => FND_API.G_FALSE
2823 ,p_request_id => p_request_rec.request_id
2824 ,p_object_id => p_rule_rec.act_rate_obj_id
2825 ,p_exec_object_definition_id => p_rule_rec.act_rate_obj_def_id
2826 ,p_user_id => p_request_rec.user_id
2827 ,p_last_update_login => p_request_rec.login_id
2828 ,p_exec_mode_code => null
2829 ,x_exec_state => l_exec_state
2830 ,x_prev_request_id => l_prev_request_id
2831 ,x_msg_count => l_msg_count
2832 ,x_msg_data => l_msg_data
2833 ,x_return_status => l_return_status
2834 );
2835
2839 ,p_msg_data => l_msg_data
2836 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
2837 Get_Put_Messages (
2838 p_msg_count => l_msg_count
2840 );
2841 raise l_register_rule_error;
2842 end if;
2843
2844 -- Register the Dataset Group Object Definition
2845 Register_Object_Definition (
2846 p_request_rec => p_request_rec
2847 ,p_rule_rec => p_rule_rec
2848 ,p_obj_def_id => p_request_rec.dataset_grp_obj_def_id
2849 );
2850
2851 -- Register all the Dependent Objects for the activity rate Object Definition
2852 FEM_PL_PKG.Register_Dependent_ObjDefs (
2853 p_api_version => 1.0
2854 ,p_commit => FND_API.G_FALSE
2855 ,p_request_id => p_request_rec.request_id
2856 ,p_object_id => p_rule_rec.act_rate_obj_id
2857 ,p_exec_object_definition_id => p_rule_rec.act_rate_obj_def_id
2858 ,p_effective_date => p_request_rec.effective_date
2859 ,p_user_id => p_request_rec.user_id
2860 ,p_last_update_login => p_request_rec.login_id
2861 ,x_msg_count => l_msg_count
2862 ,x_msg_data => l_msg_data
2863 ,x_return_status => l_return_status
2864 );
2865
2866 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
2867 Get_Put_Messages (
2868 p_msg_count => l_msg_count
2869 ,p_msg_data => l_msg_data
2870 );
2871 raise l_register_rule_error;
2872 end if;
2873
2874 -- Register the data location for the FEM_BALANCES output table
2875 FEM_DIMENSION_UTIL_PKG.Register_Data_Location (
2876 p_request_id => p_request_rec.request_id
2877 ,p_object_id => p_rule_rec.act_rate_obj_id
2878 ,p_table_name => 'FEM_BALANCES'
2879 ,p_ledger_id => p_request_rec.ledger_id
2880 ,p_cal_per_id => p_request_rec.output_cal_period_id
2881 ,p_dataset_cd => p_request_rec.output_dataset_code
2882 ,p_source_cd => p_request_rec.source_system_code
2883 ,p_load_status => null
2884 );
2885
2886 -- Register the FEM_BALANCES output table as INSERT so that Undo will
2887 -- delete all output records
2888 Register_Table (
2889 p_request_rec => p_request_rec
2890 ,p_rule_rec => p_rule_rec
2891 ,p_table_name => 'FEM_BALANCES'
2892 ,p_statement_type => 'INSERT'
2893 );
2894
2895 -- Register the PFT_AR_DRIVERS_T processing table as INSERT. This is needed
2896 -- in the event of a engine failure where the only way to purge these records
2897 -- is through the Undo Process
2898 Register_Table (
2899 p_request_rec => p_request_rec
2900 ,p_rule_rec => p_rule_rec
2901 ,p_table_name => 'PFT_AR_DRIVERS_T'
2902 ,p_statement_type => 'INSERT'
2903 );
2904
2905 -- Register the PFT_AR_DRIVER_VALUES_T processing table as INSERT. This is needed
2906 -- in the event of a engine failure where the only way to purge these records
2907 -- is through the Undo Process
2908 Register_Table (
2909 p_request_rec => p_request_rec
2910 ,p_rule_rec => p_rule_rec
2911 ,p_table_name => 'PFT_AR_DRIVER_VALUES_T'
2912 ,p_statement_type => 'INSERT'
2913 );
2914
2915 commit;
2916
2917 FEM_ENGINES_PKG.Tech_Message (
2918 p_severity => G_LOG_LEVEL_2
2919 ,p_module => G_BLOCK||'.'||L_API_NAME
2920 ,p_msg_text => 'END'
2921 );
2922
2923 EXCEPTION
2924
2925 when l_register_rule_error then
2926
2927 rollback to register_rule_pub;
2928
2929 FEM_ENGINES_PKG.Tech_Message (
2930 p_severity => G_LOG_LEVEL_6
2931 ,p_module => G_BLOCK||'.'||L_API_NAME
2932 ,p_msg_text => 'Register Rule Exception'
2933 );
2934
2935 raise g_act_rate_request_error;
2936
2937 when g_act_rate_request_error then
2938
2939 rollback to register_rule_pub;
2940 raise g_act_rate_request_error;
2941
2942 when others then
2943
2944 rollback to register_rule_pub;
2945 raise;
2946
2947 END Register_Rule;
2948
2949
2950
2951 /*===========================================================================+
2952 | PROCEDURE
2953 | Register_Object_Definition
2954 |
2955 | DESCRIPTION
2956 | Register Object Definition - Called from Act_Rate_Rule
2957 |
2958 | SCOPE - PRIVATE
2959 |
2960 +===========================================================================*/
2961
2962 PROCEDURE Register_Object_Definition (
2963 p_request_rec in request_record
2964 ,p_rule_rec in rule_record
2965 ,p_obj_def_id in number
2966 )
2967 IS
2968
2969 L_API_NAME constant varchar2(30) := 'Register_Object_Definition';
2970
2971 l_return_status varchar2(1);
2972 l_msg_count number;
2973 l_msg_data varchar2(240);
2974
2975 l_register_obj_def_error exception;
2976
2977 BEGIN
2978
2979 FEM_ENGINES_PKG.Tech_Message (
2980 p_severity => G_LOG_LEVEL_2
2981 ,p_module => G_BLOCK||'.'||L_API_NAME
2982 ,p_msg_text => 'BEGIN'
2983 );
2984
2985 -- Call the FEM_PL_PKG.Register_Object_Def API procedure to register
2986 -- the specified object definition in FEM_PL_OBJECT_DEFS, thus obtaining
2987 -- an object definition lock.
2991 ,p_request_id => p_request_rec.request_id
2988 FEM_PL_PKG.Register_Object_Def (
2989 p_api_version => 1.0
2990 ,p_commit => FND_API.G_FALSE
2992 ,p_object_id => p_rule_rec.act_rate_obj_id
2993 ,p_object_definition_id => p_obj_def_id
2994 ,p_user_id => p_request_rec.user_id
2995 ,p_last_update_login => p_request_rec.login_id
2996 ,x_msg_count => l_msg_count
2997 ,x_msg_data => l_msg_data
2998 ,x_return_status => l_return_status
2999 );
3000
3001 -- Object Definition Lock exists
3002 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
3003 Get_Put_Messages (
3004 p_msg_count => l_msg_count
3005 ,p_msg_data => l_msg_data
3006 );
3007 raise l_register_obj_def_error;
3008 end if;
3009
3010 FEM_ENGINES_PKG.Tech_Message (
3011 p_severity => G_LOG_LEVEL_2
3012 ,p_module => G_BLOCK||'.'||L_API_NAME
3013 ,p_msg_text => 'END'
3014 );
3015
3016 EXCEPTION
3017
3018 when l_register_obj_def_error then
3019
3020 FEM_ENGINES_PKG.Tech_Message (
3021 p_severity => g_log_level_6
3022 ,p_module => G_BLOCK||'.'||L_API_NAME
3023 ,p_msg_text => 'Register Object Definition Exception'
3024 );
3025
3026 raise g_act_rate_request_error;
3027
3028 END Register_Object_Definition;
3029
3030
3031
3032 /*===========================================================================+
3033 | PROCEDURE
3034 | Register_Table
3035 |
3036 | DESCRIPTION
3037 | Register tables
3038 |
3039 | SCOPE - PRIVATE
3040 |
3041 +===========================================================================*/
3042
3043 PROCEDURE Register_Table (
3044 p_request_rec in request_record
3045 ,p_rule_rec in rule_record
3046 ,p_table_name in varchar2
3047 ,p_statement_type in varchar2
3048 )
3049 IS
3050
3051 L_API_NAME constant varchar2(30) := 'Register_Table';
3052
3053 l_return_status varchar2(1);
3054 l_msg_count number;
3055 l_msg_data varchar2(240);
3056
3057 l_register_table_error exception;
3058
3059 BEGIN
3060
3061 FEM_ENGINES_PKG.Tech_Message (
3062 p_severity => G_LOG_LEVEL_2
3063 ,p_module => G_BLOCK||'.'||L_API_NAME
3064 ,p_msg_text => 'BEGIN'
3065 );
3066
3067 -- Call the FEM_PL_PKG.Register_Table API procedure to register
3068 -- the specified output table and the statement type that will be used.
3069 FEM_PL_PKG.Register_Table (
3070 p_api_version => 1.0
3071 ,p_commit => FND_API.G_FALSE
3072 ,p_request_id => p_request_rec.request_id
3073 ,p_object_id => p_rule_rec.act_rate_obj_id
3074 ,p_table_name => p_table_name
3075 ,p_statement_type => p_statement_type
3076 ,p_num_of_output_rows => 0
3077 ,p_user_id => p_request_rec.user_id
3078 ,p_last_update_login => p_request_rec.login_id
3079 ,x_msg_count => l_msg_count
3080 ,x_msg_data => l_msg_data
3081 ,x_return_status => l_return_status
3082 );
3083
3084 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
3085 Get_Put_Messages (
3086 p_msg_count => l_msg_count
3087 ,p_msg_data => l_msg_data
3088 );
3089 raise l_register_table_error;
3090 end if;
3091
3092 FEM_ENGINES_PKG.Tech_Message (
3093 p_severity => G_LOG_LEVEL_2
3094 ,p_module => G_BLOCK||'.'||L_API_NAME
3095 ,p_msg_text => 'END'
3096 );
3097
3098 EXCEPTION
3099
3100 when l_register_table_error then
3101
3102 FEM_ENGINES_PKG.Tech_Message (
3103 p_severity => g_log_level_6
3104 ,p_module => G_BLOCK||'.'||L_API_NAME
3105 ,p_msg_text => 'Register Table Exception'
3106 );
3107
3108 raise g_act_rate_request_error;
3109
3110 END Register_Table;
3111
3112
3113
3114 /*===========================================================================+
3115 | PROCEDURE
3116 | Register_Obj_Exec_Step
3117 |
3118 | DESCRIPTION
3119 | Register Object Execution Step
3120 |
3121 | SCOPE - PRIVATE
3122 |
3123 +===========================================================================*/
3124
3125 PROCEDURE Register_Obj_Exec_Step (
3126 p_request_rec in request_record
3127 ,p_rule_rec in rule_record
3128 ,p_exec_step in varchar2
3129 ,p_exec_status_code in varchar2
3130 )
3131 IS
3132
3133 L_API_NAME constant varchar2(30) := 'Register_Obj_Exec_Step';
3134
3135 l_return_status varchar2(1);
3136 l_msg_count number;
3137 l_msg_data varchar2(240);
3138
3139 l_register_obj_exec_step_error exception;
3140
3141 BEGIN
3142
3143 FEM_ENGINES_PKG.Tech_Message (
3144 p_severity => G_LOG_LEVEL_2
3145 ,p_module => G_BLOCK||'.'||L_API_NAME
3146 ,p_msg_text => 'BEGIN'
3147 );
3148
3149 FEM_PL_PKG.Register_Obj_Exec_Step (
3150 p_api_version => 1.0
3151 ,p_commit => FND_API.G_FALSE
3152 ,p_request_id => p_request_rec.request_id
3153 ,p_object_id => p_rule_rec.act_rate_obj_id
3154 ,p_exec_step => p_exec_step
3155 ,p_exec_status_code => p_exec_status_code
3156 ,p_user_id => p_request_rec.user_id
3157 ,p_last_update_login => p_request_rec.login_id
3158 ,x_msg_count => l_msg_count
3159 ,x_msg_data => l_msg_data
3160 ,x_return_status => l_return_status
3161 );
3162
3163 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
3164 Get_Put_Messages (
3165 p_msg_count => l_msg_count
3166 ,p_msg_data => l_msg_data
3167 );
3168 raise l_register_obj_exec_step_error;
3169 end if;
3170
3171 commit;
3172
3173 FEM_ENGINES_PKG.Tech_Message (
3174 p_severity => G_LOG_LEVEL_2
3175 ,p_module => G_BLOCK||'.'||L_API_NAME
3176 ,p_msg_text => 'END'
3177 );
3178
3179 EXCEPTION
3180
3181 when l_register_obj_exec_step_error then
3182
3183 FEM_ENGINES_PKG.Tech_Message (
3184 p_severity => g_log_level_6
3185 ,p_module => G_BLOCK||'.'||L_API_NAME
3186 ,p_msg_text => 'Register Object Execution Step Exception'
3187 );
3188
3192
3189 raise g_act_rate_request_error;
3190
3191 END Register_Obj_Exec_Step;
3193
3194
3195 /*===========================================================================+
3196 | PROCEDURE
3197 | Update_Obj_Exec_Step_Status
3198 |
3199 | DESCRIPTION
3200 | Update Object Execution Step Status
3201 |
3202 | SCOPE - PRIVATE
3203 |
3204 +===========================================================================*/
3205
3206 PROCEDURE Update_Obj_Exec_Step_Status (
3207 p_request_rec in request_record
3208 ,p_rule_rec in rule_record
3209 ,p_exec_step in varchar2
3210 ,p_exec_status_code in varchar2
3211 )
3212 IS
3213
3214 L_API_NAME constant varchar2(30) := 'Update_Obj_Exec_Step_Status';
3215
3216 l_return_status varchar2(1);
3217 l_msg_count number;
3218 l_msg_data varchar2(240);
3219
3220 l_upd_obj_exec_step_stat_error exception;
3221
3222 BEGIN
3223
3224 FEM_ENGINES_PKG.Tech_Message (
3225 p_severity => G_LOG_LEVEL_2
3226 ,p_module => G_BLOCK||'.'||L_API_NAME
3227 ,p_msg_text => 'BEGIN'
3228 );
3229
3230 FEM_PL_PKG.Update_Obj_Exec_Step_Status (
3231 p_api_version => 1.0
3232 ,p_commit => FND_API.G_FALSE
3233 ,p_request_id => p_request_rec.request_id
3234 ,p_object_id => p_rule_rec.act_rate_obj_id
3235 ,p_exec_step => p_exec_step
3236 ,p_exec_status_code => p_exec_status_code
3237 ,p_user_id => p_request_rec.user_id
3238 ,p_last_update_login => p_request_rec.login_id
3239 ,x_msg_count => l_msg_count
3240 ,x_msg_data => l_msg_data
3241 ,x_return_status => l_return_status
3242 );
3243
3244 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
3245 Get_Put_Messages (
3246 p_msg_count => l_msg_count
3247 ,p_msg_data => l_msg_data
3248 );
3249 raise l_upd_obj_exec_step_stat_error;
3250 end if;
3251
3252 commit;
3253
3254 FEM_ENGINES_PKG.Tech_Message (
3255 p_severity => G_LOG_LEVEL_2
3256 ,p_module => G_BLOCK||'.'||L_API_NAME
3257 ,p_msg_text => 'END'
3258 );
3259
3260 EXCEPTION
3261
3262 when l_upd_obj_exec_step_stat_error then
3263
3264 FEM_ENGINES_PKG.Tech_Message (
3265 p_severity => g_log_level_6
3266 ,p_module => G_BLOCK||'.'||L_API_NAME
3267 ,p_msg_text => 'Register Object Execution Step Exception'
3268 );
3269
3270 raise g_act_rate_request_error;
3271
3272 END Update_Obj_Exec_Step_Status;
3273
3274
3275
3276 /*===========================================================================+
3277 | PROCEDURE
3278 | Create_Temp_Objects
3279 |
3280 | DESCRIPTION
3281 | Create Temporary Objects - Sequences
3282 |
3283 | SCOPE - PRIVATE
3284 |
3288 p_request_rec in request_record
3285 +===========================================================================*/
3286
3287 PROCEDURE Create_Temp_Objects (
3289 ,p_rule_rec in rule_record
3290 )
3291 IS
3292
3293 L_API_NAME constant varchar2(30) := 'Create_Temp_Objects';
3294
3295 l_return_status varchar2(1);
3296 l_msg_count number;
3297 l_msg_data varchar2(240);
3298
3299 l_create_temp_objects_error exception;
3300
3301 BEGIN
3302
3303 FEM_ENGINES_PKG.Tech_Message (
3304 p_severity => G_LOG_LEVEL_2
3305 ,p_module => G_BLOCK||'.'||L_API_NAME
3306 ,p_msg_text => 'BEGIN'
3307 );
3308
3309 ------------------------------------------------------------------------------
3310 -- Create Activity Rate Sequence for peforming Activity Rate Processing in
3311 -- FEM_BALANCES.
3312 ------------------------------------------------------------------------------
3313 begin
3314 -- Temporary sequence is in the default APPS schema as GSCC does not
3315 -- allow hardcoded schemas.
3316 execute immediate 'create sequence '||p_rule_rec.rate_sequence_name;
3317 exception
3318 when others then
3319 FEM_ENGINES_PKG.User_Message (
3320 p_app_name => G_FEM
3321 ,p_msg_name => G_ENG_CREATE_SEQUENCE_ERR
3322 ,p_token1 => 'SEQUENCE_NAME'
3323 ,p_value1 => p_rule_rec.rate_sequence_name
3324 );
3325 raise l_create_temp_objects_error;
3326 end;
3327
3328 -- Register Temp Sequence in PL Framework
3329 FEM_PL_PKG.Register_Temp_Object (
3330 p_api_version => 1.0
3331 ,p_commit => FND_API.G_FALSE
3332 ,p_request_id => p_request_rec.request_id
3333 ,p_object_id => p_rule_rec.act_rate_obj_id
3334 ,p_object_type => 'SEQUENCE'
3335 ,p_object_name => p_rule_rec.rate_sequence_name
3336 ,p_user_id => p_request_rec.user_id
3337 ,p_last_update_login => p_request_rec.login_id
3338 ,x_return_status => l_return_status
3339 ,x_msg_count => l_msg_count
3340 ,x_msg_data => l_msg_data
3341 );
3342
3343 -- Check return status
3344 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
3345 Get_Put_Messages (
3346 p_msg_count => l_msg_count
3347 ,p_msg_data => l_msg_data
3348 );
3349 raise l_create_temp_objects_error;
3350 end if;
3351
3352 commit;
3353
3354 ------------------------------------------------------------------------------
3355 -- Create Driver Sequence for peforming Activity Rate Driver Processing in
3356 -- PFT_AR_DRIVERS_T.
3357 ------------------------------------------------------------------------------
3358 begin
3359 -- Temporary sequence is in the default APPS schema as GSCC does not
3360 -- allow hardcoded schemas.
3361 execute immediate 'create sequence '||p_rule_rec.drv_sequence_name;
3362 exception
3363 when others then
3364 FEM_ENGINES_PKG.User_Message (
3365 p_app_name => G_FEM
3366 ,p_msg_name => G_ENG_CREATE_SEQUENCE_ERR
3367 ,p_token1 => 'SEQUENCE_NAME'
3368 ,p_value1 => p_rule_rec.drv_sequence_name
3369 );
3370 raise l_create_temp_objects_error;
3371 end;
3372
3373 -- Register Temp Sequence in PL Framework
3374 FEM_PL_PKG.Register_Temp_Object (
3375 p_api_version => 1.0
3376 ,p_commit => FND_API.G_FALSE
3377 ,p_request_id => p_request_rec.request_id
3378 ,p_object_id => p_rule_rec.act_rate_obj_id
3379 ,p_object_type => 'SEQUENCE'
3380 ,p_object_name => p_rule_rec.drv_sequence_name
3381 ,p_user_id => p_request_rec.user_id
3382 ,p_last_update_login => p_request_rec.login_id
3383 ,x_return_status => l_return_status
3384 ,x_msg_count => l_msg_count
3385 ,x_msg_data => l_msg_data
3386 );
3387
3388 -- Check return status
3389 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
3390 Get_Put_Messages (
3391 p_msg_count => l_msg_count
3392 ,p_msg_data => l_msg_data
3393 );
3394 raise l_create_temp_objects_error;
3395 end if;
3396
3397 commit;
3398
3399 FEM_ENGINES_PKG.Tech_Message (
3400 p_severity => G_LOG_LEVEL_2
3401 ,p_module => G_BLOCK||'.'||L_API_NAME
3402 ,p_msg_text => 'END'
3403 );
3404
3405 EXCEPTION
3406
3407 when l_create_temp_objects_error then
3408
3409 FEM_ENGINES_PKG.Tech_Message (
3410 p_severity => g_log_level_6
3411 ,p_module => G_BLOCK||'.'||L_API_NAME
3412 ,p_msg_text => 'Create Temporary Objects Exception'
3416
3413 );
3414
3415 raise g_act_rate_request_error;
3417 END Create_Temp_Objects;
3418
3419
3420
3421 /*===========================================================================+
3422 | PROCEDURE
3423 | Drop_Temp_Objects
3424 |
3425 | DESCRIPTION
3426 | Drop Temporary Objects - Sequences
3427 |
3428 | SCOPE - PRIVATE
3429 |
3430 +===========================================================================*/
3431
3432 PROCEDURE Drop_Temp_Objects (
3433 p_request_rec in request_record
3434 ,p_rule_rec in rule_record
3435 )
3436 IS
3437
3438 L_API_NAME constant varchar2(30) := 'Drop_Temp_Objects';
3439
3440 l_object_exists_flag varchar(1);
3441 l_completion_status boolean;
3442
3443 l_return_status varchar2(1);
3444 l_msg_count number;
3445 l_msg_data varchar2(240);
3446
3447 l_drop_temp_objects_error exception;
3448
3449 BEGIN
3450
3451 FEM_ENGINES_PKG.Tech_Message (
3452 p_severity => G_LOG_LEVEL_2
3453 ,p_module => G_BLOCK||'.'||L_API_NAME
3454 ,p_msg_text => 'BEGIN'
3455 );
3456
3457 ------------------------------------------------------------------------------
3458 -- Drop Activity Rate Sequence for peforming Activity Rate Processing in
3459 -- FEM_BALANCES.
3460 ------------------------------------------------------------------------------
3461 begin
3462 select 'Y'
3463 into l_object_exists_flag
3464 from fem_pl_temp_objects
3465 where request_id = p_request_rec.request_id
3466 and object_id = p_rule_rec.act_rate_obj_id
3467 and object_type = 'SEQUENCE'
3468 and object_name = p_rule_rec.rate_sequence_name;
3469 exception
3470 when no_data_found then
3471 l_object_exists_flag := 'N';
3472 end;
3473
3474 if (l_object_exists_flag = 'Y') then
3475
3476 begin
3477 -- Temporary sequence is in the default APPS schema as GSCC does not
3478 -- allow hardcoded schemas.
3479 execute immediate 'drop sequence '||p_rule_rec.rate_sequence_name;
3480
3481 delete from fem_pl_temp_objects
3482 where request_id = p_request_rec.request_id
3483 and object_id = p_rule_rec.act_rate_obj_id
3484 and object_type = 'SEQUENCE'
3485 and object_name = p_rule_rec.rate_sequence_name;
3486
3487 exception
3488 when others then
3489 l_completion_status := FND_CONCURRENT.Set_Completion_Status('WARNING',null);
3490 FEM_ENGINES_PKG.User_Message (
3491 p_app_name => G_FEM
3492 ,p_msg_name => G_ENG_DROP_SEQUENCE_WRN
3493 ,p_token1 => 'SEQUENCE_NAME'
3494 ,p_value1 => p_rule_rec.rate_sequence_name
3495 );
3496 end;
3497
3498 commit;
3499
3500 end if;
3501
3502 ------------------------------------------------------------------------------
3503 -- Create Driver Sequence for peforming Activity Rate Driver Processing in
3504 -- PFT_AR_DRIVERS_T.
3505 ------------------------------------------------------------------------------
3506 begin
3507 select 'Y'
3508 into l_object_exists_flag
3509 from fem_pl_temp_objects
3510 where request_id = p_request_rec.request_id
3511 and object_id = p_rule_rec.act_rate_obj_id
3512 and object_type = 'SEQUENCE'
3513 and object_name = p_rule_rec.drv_sequence_name;
3514 exception
3515 when no_data_found then
3516 l_object_exists_flag := 'N';
3517 end;
3518
3519 if (l_object_exists_flag = 'Y') then
3520
3521 begin
3522 -- Temporary sequence is in the default APPS schema as GSCC does not
3523 -- allow hardcoded schemas.
3524 execute immediate 'drop sequence '||p_rule_rec.drv_sequence_name;
3525
3526 delete from fem_pl_temp_objects
3527 where request_id = p_request_rec.request_id
3528 and object_id = p_rule_rec.act_rate_obj_id
3529 and object_type = 'SEQUENCE'
3530 and object_name = p_rule_rec.drv_sequence_name;
3531
3532 exception
3533 when others then
3534 l_completion_status := FND_CONCURRENT.Set_Completion_Status('WARNING',null);
3535 FEM_ENGINES_PKG.User_Message (
3536 p_app_name => G_FEM
3537 ,p_msg_name => G_ENG_DROP_SEQUENCE_WRN
3538 ,p_token1 => 'SEQUENCE_NAME'
3539 ,p_value1 => p_rule_rec.drv_sequence_name
3540 );
3541 end;
3542
3543 commit;
3544
3545 end if;
3546
3547 FEM_ENGINES_PKG.Tech_Message (
3548 p_severity => G_LOG_LEVEL_2
3549 ,p_module => G_BLOCK||'.'||L_API_NAME
3550 ,p_msg_text => 'END'
3551 );
3552
3553 EXCEPTION
3554
3555 when l_drop_temp_objects_error then
3556
3557 FEM_ENGINES_PKG.Tech_Message (
3558 p_severity => g_log_level_6
3559 ,p_module => G_BLOCK||'.'||L_API_NAME
3560 ,p_msg_text => 'Drop Temp Objects Exception'
3561 );
3562
3563 raise g_act_rate_request_error;
3564
3565 END Drop_Temp_Objects;
3566
3567
3568
3569 /*===========================================================================+
3570 | PROCEDURE
3571 | Process_Drivers
3572 |
3573 | DESCRIPTION
3574 | Process drivers
3575 |
3576 | SCOPE - PRIVATE
3577 |
3578 +===========================================================================*/
3579
3580 PROCEDURE Process_Drivers (
3581 p_request_rec in request_record
3585 IS
3582 ,p_rule_rec in rule_record
3583 ,p_insert_count out nocopy number
3584 )
3586
3587 L_API_NAME constant varchar2(30) := 'Process_Drivers';
3588
3589 L_CALC_DRIVER_VALUES constant varchar2(30) := 'CALC_DRIVER_VALUES';
3590
3591 l_mp_prog_status varchar2(30);
3592 l_mp_exception_code varchar2(30);
3593
3594 l_return_status varchar2(1);
3595 l_msg_count number;
3596 l_msg_data varchar2(240);
3597
3598 l_valid_drv_count number;
3599
3600 l_dimension_rec dimension_record;
3601 l_act_cond_where_clause long;
3602
3603 l_err_code number;
3604 l_err_msg varchar2(30);
3605
3606 l_act_hier_where_clause long;
3607
3608 l_process_drivers_proc_error exception;
3609 l_no_driver_error exception;
3610 l_all_driver_invalid_error exception;
3611
3612 BEGIN
3613
3614 FEM_ENGINES_PKG.Tech_Message (
3615 p_severity => G_LOG_LEVEL_2
3616 ,p_module => G_BLOCK||'.'||L_API_NAME
3617 ,p_msg_text => 'BEGIN'
3618 );
3619
3620 -- Intialize variables
3621 l_mp_prog_status := G_COMPLETE_NORMAL;
3622
3623 ------------------------------------------------------------------------------
3624 -- STEP 1: Assign the driver where clause
3625 ------------------------------------------------------------------------------
3626
3627 FEM_ENGINES_PKG.Tech_Message (
3628 p_severity => G_LOG_LEVEL_1
3629 ,p_module => G_BLOCK||'.'||L_API_NAME
3630 ,p_msg_text => 'Step 1: Assign Driver Where Clause'
3631 );
3632
3633 IF (p_rule_rec.top_node_flag = 'Y') THEN
3634
3635 l_act_hier_where_clause :=
3636 ' EXISTS ('||
3637 ' SELECT 1'||
3638 ' FROM FEM_ACTIVITIES_HIER H'||
3639 ' WHERE H.HIERARCHY_OBJ_DEF_ID = :b_hier_obj_def_id'||
3640 ' AND H.CHILD_ID = H.PARENT_ID'||
3641 ' AND H.PARENT_ID = ACTS.ACTIVITY_ID'||
3642 ' AND H.SINGLE_DEPTH_FLAG = ''Y'''||
3643 ' )';
3644
3645 ELSE
3646
3647 l_act_hier_where_clause :=
3648 ' EXISTS ('||
3649 ' SELECT 1'||
3650 ' FROM FEM_ACTIVITIES_HIER H'||
3651 ' WHERE H.HIERARCHY_OBJ_DEF_ID = :b_hier_obj_def_id'||
3652 ' AND H.CHILD_ID = ACTS.ACTIVITY_ID'||
3653 ' AND H.SINGLE_DEPTH_FLAG = ''Y'''||
3654 ' )';
3655
3656 -- (H.PARENT_ID = ACTS.ACTIVITY_ID'||
3657 -- ' OR H.CHILD_ID = ACTS.ACTIVITY_ID)'||
3658
3659
3660 END IF;
3661
3662 ------------------------------------------------------------------------------
3663 -- STEP 2: Generate Conditions Predicate for Activity Rate Rule
3664 ------------------------------------------------------------------------------
3665
3666 FEM_ENGINES_PKG.Tech_Message (
3667 p_severity => G_LOG_LEVEL_1
3668 ,p_module => G_BLOCK||'.'||L_API_NAME
3669 ,p_msg_text => 'Step 2: Generate Conditions Predicate for Activity Rate Rule'
3670 );
3671
3672 l_dimension_rec := p_request_rec.dimension_rec;
3673
3674 if (p_rule_rec.cond_exists) then
3675
3676 FEM_CONDITIONS_API.Generate_Condition_Predicate (
3677 p_api_version => 1.0
3678 ,p_init_msg_list => FND_API.G_FALSE
3679 ,p_commit => FND_API.G_FALSE
3680 ,p_encoded => FND_API.G_TRUE
3681 ,x_return_status => l_return_status
3682 ,x_msg_count => l_msg_count
3683 ,x_msg_data => l_msg_data
3684 ,p_condition_obj_id => p_rule_rec.cond_obj_id
3685 ,p_rule_effective_date => p_request_rec.effective_date_varchar
3686 ,p_input_fact_table_name => l_dimension_rec.member_b_table
3687 ,p_table_alias => 'acts'
3688 ,p_display_predicate => 'N'
3689 ,p_return_predicate_type => 'DIM'
3690 ,p_logging_turned_on => 'N'
3691 ,x_predicate_string => l_act_cond_where_clause
3692 );
3693
3694 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
3695 Get_Put_Messages (
3696 p_msg_count => l_msg_count
3697 ,p_msg_data => l_msg_data
3698 );
3699 raise l_process_drivers_proc_error;
3700 end if;
3701
3702 if (l_act_cond_where_clause is null) then
3703 FEM_ENGINES_PKG.User_Message (
3704 p_app_name => G_FEM
3705 ,p_msg_name => G_ENG_COND_WHERE_CLAUSE_ERR
3706 ,p_token1 => 'CONDITION_OBJECT_ID'
3707 ,p_value1 => p_rule_rec.cond_obj_id
3708 ,p_token2 => 'EFFECTIVE_DATE'
3709 ,p_value2 => FND_DATE.date_to_chardate(p_request_rec.effective_date)
3710 ,p_token3 => 'CONDITION_TABLE_NAME'
3714 end if;
3711 ,p_value3 => l_dimension_rec.member_b_table
3712 );
3713 raise l_process_drivers_proc_error;
3715
3716 l_act_cond_where_clause := ' AND '|| l_act_cond_where_clause;
3717
3718 end if;
3719
3720 ------------------------------------------------------------------------------
3721 -- STEP 3: Populate data into the RATE DRIVER table
3722 ------------------------------------------------------------------------------
3723
3724 FEM_ENGINES_PKG.Tech_Message (
3725 p_severity => G_LOG_LEVEL_1
3726 ,p_module => G_BLOCK||'.'||L_API_NAME
3727 ,p_msg_text => 'Step 3: Populate data into the RATE DRIVER table'
3728 );
3729
3730 BEGIN
3731
3732 EXECUTE IMMEDIATE
3733 ' INSERT INTO PFT_AR_DRIVERS_T('||
3734 ' CREATED_BY_REQUEST_ID'||
3735 ' ,CREATED_BY_OBJECT_ID'||
3736 ' ,SEQ_ID'||
3737 ' ,SOURCE_TABLE_NAME'||
3738 ' ,COLUMN_NAME'||
3739 ' ,STATISTIC_BASIS_ID'||
3740 ' ,CONDITION_OBJ_ID'||
3741 ' ,LAST_UPDATE_DATE'||
3742 ' )'||
3743 ' SELECT'||
3744 ' :b_request_id'||
3745 ' ,:b_act_rate_obj_id'||
3746 ' ,' || p_rule_rec.drv_sequence_name||'.nextval'||
3747 ' ,SOURCE_TABLE_NAME'||
3748 ' ,COLUMN_NAME'||
3749 ' ,STATISTIC_BASIS_ID'||
3750 ' ,CONDITION_OBJ_ID'||
3751 ' ,sysdate'||
3752 ' FROM ('||
3753 ' SELECT distinct assgn.SOURCE_TABLE_NAME'||
3754 ' ,assgn.COLUMN_NAME'||
3755 ' ,assgn.STATISTIC_BASIS_ID'||
3756 ' ,assgn.CONDITION_OBJ_ID'||
3757 ' FROM PFT_ACTIVITY_DRIVER_ASGN assgn'||
3758 ' WHERE EXISTS ('||
3759 ' SELECT activity_id'||
3760 ' FROM fem_activities acts'||
3761 ' WHERE acts.local_vs_combo_id = :b_local_vs_combo_id'||
3762 ' AND acts.activity_id = assgn.ACTIVITY_ID'||
3766 ' AND ACTIVITY_RATE_OBJ_DEF_ID = :b_act_rate_obj_def_id'||
3763 ' AND '||l_act_hier_where_clause||
3764 l_act_cond_where_clause||
3765 ' )'||
3767 ' )'
3768 USING
3769 p_request_rec.request_id
3770 , p_rule_rec.act_rate_obj_id
3771 , p_request_rec.local_vs_combo_id
3772 , p_rule_rec.hier_obj_def_id
3773 , p_rule_rec.act_rate_obj_def_id;
3774
3775 p_insert_count := SQL%ROWCOUNT;
3776
3777 commit;
3778
3779 if (p_insert_count > 0) then
3780
3781 -- Register Object Execution Step
3782 Register_Obj_Exec_Step (
3783 p_request_rec => p_request_rec
3784 ,p_rule_rec => p_rule_rec
3785 ,p_exec_step => L_CALC_DRIVER_VALUES
3786 ,p_exec_status_code => G_EXEC_STATUS_RUNNING
3787 );
3788
3789 -- Call MP API if MP is enabled, otherwise call PL/SQL procedure directly
3790 if (G_MP_ENABLED) then
3791
3792 -- Call Calulate_Driver_Values through MP API (Push Processing)
3793 FEM_MULTI_PROC_PKG.Master (
3794 x_prg_stat => l_mp_prog_status
3795 ,x_exception_code => l_mp_exception_code
3796 ,p_rule_id => p_rule_rec.act_rate_obj_id
3797 ,p_eng_step => L_CALC_DRIVER_VALUES
3798 ,p_data_table => 'PFT_AR_DRIVERS_T'
3799 ,p_eng_sql => null
3800 ,p_table_alias => 'drv'
3801 ,p_run_name => L_CALC_DRIVER_VALUES
3802 ,p_eng_prg => 'PFT_AR_ENGINE_PVT.Calc_Driver_Values'
3803 ,p_condition => null
3804 ,p_failed_req_id => null
3805 ,p_reuse_slices => 'N' -- New data slice
3806 ,p_arg1 => p_request_rec.request_id
3807 ,p_arg2 => p_request_rec.dataset_grp_obj_def_id
3808 ,p_arg3 => p_request_rec.effective_date_varchar
3809 ,p_arg4 => p_request_rec.output_cal_period_id
3810 ,p_arg5 => p_request_rec.ledger_id
3811 ,p_arg6 => p_request_rec.local_vs_combo_id
3812 ,p_arg7 => p_request_rec.user_id
3813 ,p_arg8 => p_request_rec.login_id
3814 ,p_arg9 => p_rule_rec.act_rate_obj_id
3815 ,p_arg10 => p_rule_rec.act_rate_obj_def_id
3816 ,p_arg11 => p_rule_rec.hier_obj_def_id
3817 ,p_arg12 => l_act_hier_where_clause
3818 ,p_arg13 => l_act_cond_where_clause
3819 );
3820
3821 else
3822
3823 -- Call Calulate_Driver_Values directly
3824 Calc_Driver_Values (
3825 p_eng_sql => null
3826 ,p_slc_pred => null
3827 ,p_proc_num => null
3828 ,p_part_code => null
3829 ,p_fetch_limit => null
3830 ,p_request_id => p_request_rec.request_id
3831 ,p_dataset_grp_obj_def_id => p_request_rec.dataset_grp_obj_def_id
3832 ,p_effective_date_varchar => p_request_rec.effective_date_varchar
3833 ,p_output_cal_period_id => p_request_rec.output_cal_period_id
3834 ,p_ledger_id => p_request_rec.ledger_id
3835 ,p_local_vs_combo_id => p_request_rec.local_vs_combo_id
3836 ,p_user_id => p_request_rec.user_id
3837 ,p_login_id => p_request_rec.login_id
3838 ,p_act_rate_obj_id => p_rule_rec.act_rate_obj_id
3839 ,p_act_rate_obj_def_id => p_rule_rec.act_rate_obj_def_id
3840 ,p_hier_obj_def_id => p_rule_rec.hier_obj_def_id
3841 ,p_act_hier_where_clause => l_act_hier_where_clause
3842 ,p_act_cond_where_clause => l_act_cond_where_clause
3843 );
3844
3845 end if;
3846
3847 if (l_mp_prog_status <> G_COMPLETE_NORMAL) then
3848
3849 if (l_mp_exception_code is not null) then
3850 FEM_ENGINES_PKG.User_Message (
3851 p_app_name => G_FEM
3852 ,p_msg_name => l_mp_exception_code
3853 );
3854 end if;
3855
3856 Update_Obj_Exec_Step_Status (
3857 p_request_rec => p_request_rec
3858 ,p_rule_rec => p_rule_rec
3859 ,p_exec_step => L_CALC_DRIVER_VALUES
3860 ,p_exec_status_code => G_EXEC_STATUS_ERROR_UNDO
3861 );
3862
3863 raise l_process_drivers_proc_error;
3864
3865 else
3866
3867 Update_Obj_Exec_Step_Status (
3868 p_request_rec => p_request_rec
3869 ,p_rule_rec => p_rule_rec
3870 ,p_exec_step => L_CALC_DRIVER_VALUES
3871 ,p_exec_status_code => G_EXEC_STATUS_SUCCESS
3872 );
3873
3874 end if;
3875
3876 commit;
3877
3878 if (G_MP_ENABLED) then
3879 -- Purge Data Slices
3880 FEM_MULTI_PROC_PKG.Delete_Data_Slices (
3881 p_req_id => p_request_rec.request_id
3882 );
3883 end if;
3884
3885 else
3886
3887 raise l_no_driver_error;
3888
3889 end if;
3890
3891 -- IF all drivers are invalid then report DRIVER_NOT_VALID error
3892 SELECT count(*)
3893 INTO l_valid_drv_count
3894 FROM PFT_AR_DRIVERS_T
3895 WHERE CREATED_BY_REQUEST_ID = p_request_rec.request_id
3896 AND CREATED_BY_OBJECT_ID = p_rule_rec.act_rate_obj_id
3897 AND VALID_FLAG = 'Y';
3898
3899 IF (l_valid_drv_count = 0) THEN
3900 raise l_all_driver_invalid_error;
3901 END IF;
3902
3903 -- ammittal - commented to let the exception fall through
3904 -- EXCEPTION
3905
3906 -- WHEN OTHERS THEN
3907 -- FEM_ENGINES_PKG.User_Message (
3911 -- raise l_process_drivers_proc_error;
3908 -- p_app_name => G_FEM
3909 -- ,p_msg_name => G_AR_INSERT_ACT_DRIV_ERR
3910 -- );
3912
3913 END;
3914
3915 FEM_ENGINES_PKG.Tech_Message (
3916 p_severity => G_LOG_LEVEL_2
3917 ,p_module => G_BLOCK||'.'||L_API_NAME
3918 ,p_msg_text => 'END'
3919 );
3920
3921 EXCEPTION
3922
3923 when l_all_driver_invalid_error then
3924
3925 FEM_ENGINES_PKG.Tech_Message (
3926 p_severity => G_LOG_LEVEL_6
3927 ,p_module => G_BLOCK||'.'||L_API_NAME
3928 ,p_msg_text => 'Process Driver All Invalid Driver Exception'
3929 );
3930
3931 FEM_ENGINES_PKG.User_Message (
3932 p_app_name => G_PFT
3933 ,p_msg_name => G_AR_ALL_INV_DRIV_ERR
3934 ,p_token1 => 'TABLE_NAME'
3935 ,p_value1 => 'PFT_AR_DRIVERS_T'
3936 ,p_token2 => 'OBJECT_ID'
3937 ,p_value2 => p_rule_rec.act_rate_obj_id
3938 );
3939
3940 raise g_act_rate_request_error;
3941
3942 when l_no_driver_error then
3943
3944 FEM_ENGINES_PKG.Tech_Message (
3945 p_severity => G_LOG_LEVEL_6
3946 ,p_module => G_BLOCK||'.'||L_API_NAME
3947 ,p_msg_text => 'Process Driver No Driver Exception'
3948 );
3949
3950 FEM_ENGINES_PKG.User_Message (
3951 p_app_name => G_PFT
3952 ,p_msg_name => G_AR_NO_DRIVER_ERR
3953 ,p_token1 => 'TABLE_NAME'
3954 ,p_value1 => 'PFT_ACTIVITY_DRIVER_ASGN'
3955 ,p_token2 => 'OBJECT_ID'
3956 ,p_value2 => p_rule_rec.act_rate_obj_id
3957 );
3958
3959 raise g_act_rate_request_error;
3960
3961
3962 when l_process_drivers_proc_error then
3963
3964 FEM_ENGINES_PKG.Tech_Message (
3965 p_severity => G_LOG_LEVEL_6
3966 ,p_module => G_BLOCK||'.'||L_API_NAME
3967 ,p_msg_text => 'Rule Post Process Exception'
3968 );
3969
3970 FEM_ENGINES_PKG.User_Message (
3971 p_app_name => G_FEM
3972 ,p_msg_name => G_ENG_RULE_POST_PROC_ERR
3973 );
3974
3975 raise g_act_rate_request_error;
3976
3977 END Process_Drivers;
3978
3979
3980
3981 /*===========================================================================+
3982 | PROCEDURE
3983 | Calc_Driver_Values
3984 |
3985 | DESCRIPTION
3986 | This procedure is called by the Multi-Processing Engine so that driver
3987 | calculation can be done in parallel through multiple subrequests.
3988 |
3989 | SCOPE - PUBLIC
3990 |
3991 +===========================================================================*/
3992
3993 PROCEDURE Calc_Driver_Values (
3994 p_eng_sql in varchar2
3995 ,p_slc_pred in varchar2
3996 ,p_proc_num in number
3997 ,p_part_code in number
4001 ,p_effective_date_varchar in varchar2
3998 ,p_fetch_limit in number
3999 ,p_request_id in number
4000 ,p_dataset_grp_obj_def_id in number
4002 ,p_output_cal_period_id in number
4003 ,p_ledger_id in number
4004 ,p_local_vs_combo_id in number
4005 ,p_user_id in number
4006 ,p_login_id in number
4007 ,p_act_rate_obj_id in number
4008 ,p_act_rate_obj_def_id in number
4009 ,p_hier_obj_def_id in number
4010 ,p_act_hier_where_clause in long
4011 ,p_act_cond_where_clause in long
4012 )
4013 IS
4014
4015 L_API_NAME constant varchar2(30) := 'Calc_Driver_Values';
4016
4017 -- Bulk fetch limit for cursors
4018 l_fetch_limit number;
4019
4020 -- MP partition variables
4021 l_slc_id number;
4022 l_slc_val1 number;
4023 l_slc_val2 number;
4024 l_slc_val3 number;
4025 l_slc_val4 number;
4026 l_num_vals number;
4027 l_part_name varchar2(30);
4028
4029 -- MP status and output variables
4030 l_status number;
4031 l_message varchar2(30);
4032 l_rows_processed number;
4033 l_rows_loaded number;
4034
4035 l_return_status varchar2(1);
4036 l_msg_count number;
4037 l_msg_data varchar2(240);
4038
4039 l_dummy number;
4040
4041 l_err_code number;
4042 l_err_msg varchar2(30);
4043
4044 l_driver_where_clause long;
4045 l_drv_cond_where_clause long;
4046 l_input_ds_d_where_clause long;
4047
4048 l_ar_drivers_csr dynamic_cursor;
4049 l_ar_drivers_stmt long;
4050 l_calc_drv_stmt long;
4051 l_ar_driver_values_stmt long;
4052
4053 -- PL/SQL tables to fetch details from appropriate queries.
4054 l_rowid_tbl rowid_type;
4055 l_drv_table_name_tbl g_drv_table_name_table;
4056 l_column_name_tbl g_column_name_table;
4057 l_statistic_basis_id_tbl g_statistic_basis_id_table;
4058 l_drv_condition_obj_id_tbl g_condition_obj_id_table;
4059 l_valid_flag_tbl g_valid_flag_table;
4060 l_driver_value_tbl g_driver_value_table;
4061 l_last_update_date_tbl g_last_update_date_table;
4062 l_invalid_reason_tbl g_invalid_reason_table;
4063
4064 BEGIN
4065
4066 FEM_ENGINES_PKG.Tech_Message (
4067 p_severity => G_LOG_LEVEL_2
4068 ,p_module => G_BLOCK||'.'||L_API_NAME
4069 ,p_msg_text => 'BEGIN'
4070 );
4071
4072 -- Initialize Variables
4073 l_status := 0;
4074 l_message := 'COMPLETE:NORMAL';
4075
4076 l_rows_processed := 0;
4077 l_rows_loaded := 0;
4078
4079 l_num_vals := 0;
4080 l_part_name := null;
4081
4082 -- Set the cursor fetch limit
4083 l_fetch_limit := p_fetch_limit;
4084 if (l_fetch_limit is null) then
4085 l_fetch_limit := G_DEFAULT_FETCH_LIMIT;
4086 end if;
4087
4088 ------------------------------------------------------------------------------
4089 -- STEP 1: Build MP SQL Statement for fetching rows in PFT_AR_DRIVERS_T
4090 ------------------------------------------------------------------------------
4091
4092 FEM_ENGINES_PKG.Tech_Message (
4093 p_severity => G_LOG_LEVEL_1
4094 ,p_module => G_BLOCK||'.'||L_API_NAME
4095 ,p_msg_text => 'Step 1: Build MP SQL Statement for fetching rows in PFT_AR_DRIVERS_T'
4096 );
4097
4098 if (G_MP_ENABLED) then
4099 -- Get the Data Slice
4100 FEM_MULTI_PROC_PKG.Get_Data_Slice (
4101 p_req_id => p_request_id
4102 ,p_proc_num => p_proc_num
4103 ,x_slc_id => l_slc_id
4104 ,x_slc_val1 => l_slc_val1
4105 ,x_slc_val2 => l_slc_val2
4106 ,x_slc_val3 => l_slc_val3
4107 ,x_slc_val4 => l_slc_val4
4108 ,x_num_vals => l_num_vals
4109 ,x_part_name => l_part_name
4110 );
4111 end if;
4112
4113 -- Build tokenized SQL statement
4114 l_ar_drivers_stmt :=
4115 ' select rowid'||
4116 ' ,source_table_name'||
4117 ' ,column_name'||
4118 ' ,statistic_basis_id'||
4119 ' ,condition_obj_id'||
4120 ' ,valid_flag'||
4121 ' ,driver_value'||
4122 ' ,last_update_date'||
4123 ' ,invalid_reason'||
4124 ' from pft_ar_drivers_t {{table_partition}} drv'||
4125 ' where created_by_request_id = '||p_request_id||
4126 ' and created_by_object_id = '||p_act_rate_obj_id||
4127 ' and {{data_slice}} ';
4128
4129 -- Replace the data slice token with the slice predicate (if it exists)
4130 if (p_slc_pred is null) then
4131 l_ar_drivers_stmt := REPLACE(l_ar_drivers_stmt
4132 ,'{{data_slice}}',' 1=1 ');
4133 else
4134 l_ar_drivers_stmt := REPLACE(l_ar_drivers_stmt
4135 ,'{{data_slice}}',p_slc_pred);
4136 end if;
4137
4138 -- Replace the partition token with the partition table name (if it exists)
4139 if (l_part_name is null) then
4140 l_ar_drivers_stmt := REPLACE(l_ar_drivers_stmt
4141 ,'{{table_partition}}',' ');
4142 else
4143 l_ar_drivers_stmt := REPLACE(l_ar_drivers_stmt
4144 ,'{{table_partition}}',' PARTITION('||l_part_name||') ');
4145 end if;
4146
4147 FEM_ENGINES_PKG.Tech_Message (
4148 p_severity => G_LOG_LEVEL_1
4149 ,p_module => G_BLOCK||'.'||L_API_NAME
4153 ------------------------------------------------------------------------------
4150 ,p_msg_text => 'l_ar_drivers_stmt = '||l_ar_drivers_stmt
4151 );
4152
4154 -- STEP 2: Open MP Cursor for fetching rows in PFT_AR_DRIVERS_T
4155 ------------------------------------------------------------------------------
4156
4157 FEM_ENGINES_PKG.Tech_Message (
4158 p_severity => G_LOG_LEVEL_1
4159 ,p_module => G_BLOCK||'.'||L_API_NAME
4160 ,p_msg_text => 'Step 2: Open MP Cursor for fetching rows in PFT_AR_DRIVERS_T'
4161 );
4162
4163 -- Execute the built SQL statement for opening the cursor
4164 if (l_num_vals = 4) then
4165 open l_ar_drivers_csr for l_ar_drivers_stmt
4166 using l_slc_val1, l_slc_val2, l_slc_val3, l_slc_val4;
4167 elsif (l_num_vals = 3) then
4168 open l_ar_drivers_csr for l_ar_drivers_stmt
4169 using l_slc_val1, l_slc_val2, l_slc_val3;
4170 elsif (l_num_vals = 2) then
4171 open l_ar_drivers_csr for l_ar_drivers_stmt
4172 using l_slc_val1, l_slc_val2;
4173 elsif (l_num_vals = 1) then
4174 open l_ar_drivers_csr for l_ar_drivers_stmt
4175 using l_slc_val1;
4176 elsif (l_num_vals = 0) then
4177 -- no data slice
4178 open l_ar_drivers_csr for l_ar_drivers_stmt;
4179 end if;
4180
4181 loop
4182
4183 fetch l_ar_drivers_csr
4184 bulk collect into
4185 l_rowid_tbl
4186 ,l_drv_table_name_tbl
4187 ,l_column_name_tbl
4188 ,l_statistic_basis_id_tbl
4189 ,l_drv_condition_obj_id_tbl
4190 ,l_valid_flag_tbl
4191 ,l_driver_value_tbl
4192 ,l_last_update_date_tbl
4193 ,l_invalid_reason_tbl
4194 limit l_fetch_limit;
4195
4196 if l_rowid_tbl.count = 0 then
4197 exit;
4198 end if;
4199
4200 for i in 1..l_rowid_tbl.count loop
4201
4202 <<next_driver>>
4203 loop
4204
4205 FEM_ENGINES_PKG.Tech_Message (
4206 p_severity => G_LOG_LEVEL_1
4207 ,p_module => G_BLOCK||'.'||L_API_NAME
4208 ,p_msg_text =>
4209 'Step 3.'||to_char(i)||
4210 ': Process Driver from '||l_drv_table_name_tbl(i)||
4211 ' with statistic_basis_id = '||l_statistic_basis_id_tbl(i)
4212 );
4213
4214 begin
4215 select 1
4216 into l_dummy
4217 from fem_table_class_assignmt_v
4218 where table_classification_code in ('STATISTIC', 'PFT_LEDGER')
4219 and table_name = l_drv_table_name_tbl(i);
4220 exception
4221 when too_many_rows then
4222 null;
4223 when no_data_found then
4224 l_valid_flag_tbl(i) := 'N';
4225 l_invalid_reason_tbl(i) := G_AR_NO_DRV_TBL_CLASSF_ERR;
4226 exit next_driver;
4227 end;
4228
4229 ------------------------------------------------------------------------
4230 -- Call the Where Clause Generator for source data in FEM_BALANCES
4231 ------------------------------------------------------------------------
4232 FEM_DS_WHERE_CLAUSE_GENERATOR.FEM_Gen_DS_WClause_PVT (
4233 p_api_version => 1.0
4234 ,p_init_msg_list => FND_API.G_FALSE
4235 ,p_encoded => FND_API.G_TRUE
4236 ,x_return_status => l_return_status
4237 ,x_msg_count => l_msg_count
4238 ,x_msg_data => l_msg_data
4239 ,p_ds_io_def_id => p_dataset_grp_obj_def_id
4240 ,p_output_period_id => p_output_cal_period_id
4241 ,p_table_name => l_drv_table_name_tbl(i)
4242 ,p_table_alias => 'D'
4243 ,p_ledger_id => p_ledger_id
4244 ,p_where_clause => l_input_ds_d_where_clause
4245 );
4246
4247 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
4248 Get_Put_Messages (
4249 p_msg_count => l_msg_count
4250 ,p_msg_data => l_msg_data
4251 );
4252 l_valid_flag_tbl(i) := 'N';
4253 exit next_driver;
4254 end if;
4255
4256 if (l_input_ds_d_where_clause is null) then
4257 FEM_ENGINES_PKG.User_Message (
4258 p_app_name => G_FEM
4259 ,p_msg_name => G_ENG_BAD_DS_WCLAUSE_ERR
4260 );
4261 l_valid_flag_tbl(i) := 'N';
4262 exit next_driver;
4263 end if;
4264
4265 if (l_drv_condition_obj_id_tbl(i) is not null) then
4266
4267 FEM_CONDITIONS_API.Generate_Condition_Predicate (
4268 p_api_version => 1.0
4269 ,p_init_msg_list => FND_API.G_FALSE
4270 ,p_commit => FND_API.G_FALSE
4271 ,p_encoded => FND_API.G_TRUE
4272 ,x_return_status => l_return_status
4273 ,x_msg_count => l_msg_count
4274 ,x_msg_data => l_msg_data
4275 ,p_condition_obj_id => l_drv_condition_obj_id_tbl(i)
4276 ,p_rule_effective_date => p_effective_date_varchar
4277 ,p_input_fact_table_name => l_drv_table_name_tbl(i)
4278 ,p_table_alias => 'D'
4279 ,p_display_predicate => 'N'
4280 ,p_return_predicate_type => 'DIM'
4281 ,p_logging_turned_on => 'N'
4282 ,x_predicate_string => l_drv_cond_where_clause
4283 );
4284
4285 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
4286 Get_Put_Messages (
4287 p_msg_count => l_msg_count
4288 ,p_msg_data => l_msg_data
4289 );
4290 end if;
4291
4292 end if;
4293
4297 ' where d.line_item_id = :b_statistic_basis_id'||
4294 l_calc_drv_stmt :=
4295 ' select sum(d.'||l_column_name_tbl(i)||')'||
4296 ' from '||l_drv_table_name_tbl(i)|| ' d' ||
4298 ' and '|| l_input_ds_d_where_clause||
4299 ' and d.ledger_id = :b_ledger_id';
4300
4301 -- ammittal - Do not allow Activity Rate data to be the driver of
4302 -- another activity rate
4303 if (l_drv_table_name_tbl(i) = 'FEM_BALANCES') then
4304 l_calc_drv_stmt := l_calc_drv_stmt||
4305 ' and d.financial_elem_id <> :b_act_rate_fin_elem_id';
4306 end if;
4307
4308 if (l_drv_cond_where_clause is not null) then
4309 l_calc_drv_stmt := l_calc_drv_stmt||
4310 ' and '||l_drv_cond_where_clause;
4311 end if;
4312
4313 begin
4314
4315 if (l_drv_table_name_tbl(i) = 'FEM_BALANCES') then
4316 execute immediate l_calc_drv_stmt
4317 into l_driver_value_tbl(i)
4318 using l_statistic_basis_id_tbl(i)
4319 ,p_ledger_id
4320 ,G_FIN_ELEM_ID_ACTIVITY_RATE;
4321 else
4322 execute immediate l_calc_drv_stmt
4323 into l_driver_value_tbl(i)
4324 using l_statistic_basis_id_tbl(i)
4325 ,p_ledger_id;
4326 end if;
4327
4328 -- Bug fix 4626068 - ammittal 06/21/05 - added the code for null
4329 -- as the statistic value can be null in FEM_BALANCES
4330 if ((l_driver_value_tbl(i) = 0)
4331 OR (l_driver_value_tbl(i) IS NULL)) then
4332
4333 FEM_ENGINES_PKG.User_Message (
4334 p_app_name => G_PFT
4335 ,p_msg_name => G_AR_ZERO_DRV_VAL_ERR
4336 ,p_token1 => 'SOURCE_TABLE_NAME'
4337 ,p_value1 => l_drv_table_name_tbl(i)
4338 ,p_token2 => 'SOURCE_COLUMN_NAME'
4339 ,p_value2 => l_column_name_tbl(i)
4340 ,p_token3 => 'STATISTIC_BASIS_ID'
4341 ,p_value3 => l_statistic_basis_id_tbl(i)
4342 ,p_token4 => 'CONDITION_OBJ_ID'
4343 ,p_value4 => l_drv_condition_obj_id_tbl(i)
4344 );
4345
4346 l_valid_flag_tbl(i) := 'N';
4347 l_invalid_reason_tbl(i) := G_AR_ZERO_DRV_VAL_ERR;
4348 exit next_driver;
4349 else
4350 l_valid_flag_tbl(i) := 'Y';
4351 end if;
4352
4353 exception
4354 when others then
4355 FEM_ENGINES_PKG.User_Message (
4356 p_app_name => G_PFT
4357 ,p_msg_name => G_AR_INSERT_ACT_DRIV_ERR
4358 ,p_token1 => 'TABLE_NAME'
4359 ,p_value1 => l_statistic_basis_id_tbl(i)
4360 ,p_token2 => 'OBJECT_ID'
4361 ,p_value2 => p_act_rate_obj_id
4362 );
4363 l_valid_flag_tbl(i) := 'N';
4364 l_invalid_reason_tbl(i) := G_AR_UNEXP_DRV_VAL_ERR;
4365 exit next_driver;
4366
4367 end;
4368
4369 if (g_track_event_chains) then
4370
4371 Register_Driver_Chains (
4372 p_request_id => p_request_id
4373 ,p_ledger_id => p_ledger_id
4374 ,p_user_id => p_user_id
4375 ,p_login_id => p_login_id
4376 ,p_act_rate_obj_id => p_act_rate_obj_id
4377 ,p_drv_table_name => l_drv_table_name_tbl(i)
4378 ,p_statistic_basis_id => l_statistic_basis_id_tbl(i)
4379 ,p_drv_cond_where_clause => l_drv_cond_where_clause
4380 ,p_input_ds_d_where_clause => l_input_ds_d_where_clause
4381 );
4382
4383 end if;
4384
4385 -- Always exit to ensure one pass in the next_driver loop
4386 exit next_driver;
4387
4388 end loop; --next_driver
4389
4390 end loop;
4391
4392 ----------------------------------------------------------------------------
4393 -- STEP 4: Bulk Update PFT_AR_DRIVERS_T Table
4394 ----------------------------------------------------------------------------
4395
4396 FEM_ENGINES_PKG.Tech_Message (
4397 p_severity => G_LOG_LEVEL_1
4398 ,p_module => G_BLOCK||'.'||L_API_NAME
4399 ,p_msg_text => 'Step 4: Bulk Update PFT_AR_DRIVERS_T Table'
4400 );
4401
4402 forall rec_num in l_rowid_tbl.FIRST..l_rowid_tbl.LAST
4403 update pft_ar_drivers_t
4404 set driver_value = l_driver_value_tbl(rec_num)
4405 ,valid_flag = l_valid_flag_tbl(rec_num)
4406 ,invalid_reason = l_invalid_reason_tbl(rec_num)
4407 where rowid = l_rowid_tbl(rec_num);
4408
4409 -- Update row counts for MP Slice Post Processing
4410 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
4411 l_rows_loaded := l_rows_loaded + l_rowid_tbl.count;
4412
4416
4413 ----------------------------------------------------------------------------
4414 -- STEP 5: Build MP SQL Statement for bulk insert into PFT_AR_DRIVER_VALUES_T
4415 ----------------------------------------------------------------------------
4417 FEM_ENGINES_PKG.Tech_Message (
4418 p_severity => G_LOG_LEVEL_1
4419 ,p_module => G_BLOCK||'.'||L_API_NAME
4420 ,p_msg_text => 'Step 5: Build MP SQL Statement for bulk insert into PFT_AR_DRIVER_VALUES_T'
4421 );
4422
4423 -- ammittal 11/23/04 - Need to look at where clauses for hierarchy and
4424 -- conditions. Turn Value and Driver table population around?
4425
4426 -- Build tokenized SQL statement
4427 l_ar_driver_values_stmt :=
4428 ' insert into pft_ar_driver_values_t ('||
4429 ' created_by_request_id'||
4430 ' ,created_by_object_id'||
4431 ' ,activity_id'||
4432 ' ,driver_value'||
4433 ' ,statistic_basis_id'||
4434 ' )'||
4435 ' select '||p_request_id||
4436 ' ,'||p_act_rate_obj_id||
4437 ' ,assgn.activity_id'||
4438 ' ,drv.driver_value'||
4439 ' ,assgn.statistic_basis_id'||
4440 ' from pft_ar_drivers_t {{table_partition}} drv'||
4441 ' ,pft_activity_driver_asgn assgn'||
4442 ' where drv.created_by_request_id = '||p_request_id||
4443 ' and drv.created_by_object_id = '||p_act_rate_obj_id||
4444 ' and drv.valid_flag = ''Y'''||
4445 ' and assgn.activity_rate_obj_def_id = '||p_act_rate_obj_def_id||
4446 ' and assgn.source_table_name = drv.source_table_name'||
4447 ' and assgn.column_name = drv.column_name'||
4448 ' and assgn.statistic_basis_id = drv.statistic_basis_id'||
4449 ' and exists ('||
4450 ' select activity_id'||
4451 ' from fem_activities acts'||
4452 ' where acts.local_vs_combo_id = '||p_local_vs_combo_id||
4453 ' and acts.activity_id = assgn.activity_id'||
4454 ' and '||p_act_hier_where_clause||
4455 p_act_cond_where_clause||
4456 ' )'||
4457 ' and nvl(assgn.condition_obj_id, -1) = nvl(drv.condition_obj_id, -1)'||
4458 ' and {{data_slice}} ';
4459
4460 -- Replace the data slice token with the slice predicate (if it exists)
4461 if (p_slc_pred is null) then
4462 l_ar_driver_values_stmt := REPLACE(l_ar_driver_values_stmt
4463 ,'{{data_slice}}',' 1=1 ');
4464 else
4465 l_ar_driver_values_stmt := REPLACE(l_ar_driver_values_stmt,
4466 '{{data_slice}}',p_slc_pred);
4467 end if;
4468
4469 -- Replace the partition token with the partition table name (if it exists)
4470 if (l_part_name is null) then
4471 l_ar_driver_values_stmt := REPLACE(l_ar_driver_values_stmt
4472 ,'{{table_partition}}',' ');
4473 else
4474 l_ar_driver_values_stmt := REPLACE(l_ar_driver_values_stmt
4475 ,'{{table_partition}}',' PARTITION('||l_part_name||') ');
4476 end if;
4477
4478 ------------------------------------------------------------------------------
4479 -- STEP 6: Execute MP SQL Statement for bulk insert into PFT_AR_DRIVER_VALUES_T
4480 ------------------------------------------------------------------------------
4481
4482 FEM_ENGINES_PKG.Tech_Message (
4483 p_severity => G_LOG_LEVEL_1
4484 ,p_module => G_BLOCK||'.'||L_API_NAME
4485 ,p_msg_text => 'Step 6: Execute MP SQL Statement for bulk insert into PFT_AR_DRIVER_VALUES_T'
4486 );
4487
4488 -- Execute the built SQL statement for bulk insert into PFT_AR_DRIVER_VALUES_T
4489 if (l_num_vals = 4) then
4490 execute immediate l_ar_driver_values_stmt
4491 using p_hier_obj_def_id
4492 ,l_slc_val1, l_slc_val2, l_slc_val3, l_slc_val4;
4493 elsif (l_num_vals = 3) then
4494 execute immediate l_ar_driver_values_stmt
4495 using p_hier_obj_def_id
4496 ,l_slc_val1, l_slc_val2, l_slc_val3;
4497 elsif (l_num_vals = 2) then
4498 execute immediate l_ar_driver_values_stmt
4499 using p_hier_obj_def_id
4500 ,l_slc_val1, l_slc_val2;
4501 elsif (l_num_vals = 1) then
4502 execute immediate l_ar_driver_values_stmt
4503 using p_hier_obj_def_id
4504 ,l_slc_val1;
4505 elsif (l_num_vals = 0) then
4506 -- no data slice
4507 execute immediate l_ar_driver_values_stmt
4508 using p_hier_obj_def_id;
4509 end if;
4510
4511 -- Purge pl/sql tables
4512 l_rowid_tbl.DELETE;
4513 l_drv_table_name_tbl.DELETE;
4514 l_column_name_tbl.DELETE;
4515 l_statistic_basis_id_tbl.DELETE;
4516 l_drv_condition_obj_id_tbl.DELETE;
4517 l_valid_flag_tbl.DELETE;
4518 l_driver_value_tbl.DELETE;
4519 l_last_update_date_tbl.DELETE;
4520 l_invalid_reason_tbl.DELETE;
4521
4522 commit;
4523
4524 end loop;
4525
4526 close l_ar_drivers_csr;
4527
4528 if (G_MP_ENABLED) then
4529 -- MP Post Processing on processed data slice
4530 FEM_MULTI_PROC_PKG.Post_Data_Slice (
4531 p_req_id => p_request_id
4532 ,p_slc_id => l_slc_id
4533 ,p_status => l_status
4534 ,p_message => l_message
4535 ,p_rows_processed => l_rows_processed
4536 ,p_rows_loaded => l_rows_loaded
4537 ,p_rows_rejected => l_rows_loaded - l_rows_processed
4538 );
4539 end if;
4540
4541 FEM_ENGINES_PKG.Tech_Message (
4542 p_severity => G_LOG_LEVEL_2
4543 ,p_module => G_BLOCK||'.'||L_API_NAME
4544 ,p_msg_text => 'END'
4545 );
4546
4547 EXCEPTION
4548
4549 when others then
4550
4551 g_prg_msg := SQLERRM;
4552 g_callstack := DBMS_UTILITY.Format_Call_Stack;
4553
4554 if (l_ar_drivers_csr%ISOPEN) then
4555 close l_ar_drivers_csr;
4556 end if;
4557
4558 l_status:= 2;
4559 l_message := 'COMPLETE:ERROR';
4560
4564 ,p_slc_id => l_slc_id
4561 if (G_MP_ENABLED) then
4562 FEM_MULTI_PROC_PKG.Post_Data_Slice (
4563 p_req_id => p_request_id
4565 ,p_status => l_status
4566 ,p_message => l_message
4567 ,p_rows_processed => l_rows_processed
4568 ,p_rows_loaded => l_rows_loaded
4569 ,p_rows_rejected => l_rows_loaded - l_rows_processed
4570 );
4571 end if;
4572
4573 FEM_ENGINES_PKG.Tech_Message (
4574 p_severity => G_LOG_LEVEL_6
4575 ,p_module => G_BLOCK||'.'||L_API_NAME||'.Unexpected_Exception'
4576 ,p_msg_text => g_prg_msg
4577 );
4578
4579 FEM_ENGINES_PKG.Tech_Message (
4580 p_severity => G_LOG_LEVEL_6
4581 ,p_module => G_BLOCK||'.'||L_API_NAME||'.Unexpected_Exception'
4582 ,p_msg_text => g_callstack
4583 );
4584
4585 FEM_ENGINES_PKG.User_Message (
4586 p_app_name => G_FEM
4587 ,p_msg_name => G_UNEXPECTED_ERROR
4588 ,p_token1 => 'ERR_MSG'
4589 ,p_value1 => g_prg_msg
4590 );
4591
4592 raise g_act_rate_request_error;
4593
4594 END Calc_Driver_Values;
4595
4596
4597
4598 /*===========================================================================+
4599 | PROCEDURE
4600 | Register_Driver_Chains
4601 |
4602 | DESCRIPTION
4603 | Register Driver Chains - Called from Process Drivers
4604 |
4605 | SCOPE - PRIVATE
4606 |
4607 +===========================================================================*/
4608
4609 PROCEDURE Register_Driver_Chains (
4610 p_request_id in number
4611 ,p_ledger_id in number
4612 ,p_user_id in number
4613 ,p_login_id in number
4614 ,p_act_rate_obj_id in number
4615 ,p_drv_table_name in varchar2
4616 ,p_statistic_basis_id in number
4617 ,p_drv_cond_where_clause in long
4618 ,p_input_ds_d_where_clause in long
4619 )
4620 IS
4621
4622 L_API_NAME constant varchar2(30) := 'Register_Driver_Chains';
4623
4624 l_dummy number;
4625 l_find_driver_chains_last_row number;
4626 l_created_by_request_id_tbl number_table;
4627 l_created_by_object_id_tbl number_table;
4628
4629 l_drv_chain_csr dynamic_cursor;
4630 l_drv_chain_stmt long;
4631
4632 l_return_status varchar2(1);
4633 l_msg_count number;
4634 l_msg_data varchar2(240);
4635
4636 l_register_driver_chains_error exception;
4637
4638 BEGIN
4639
4640 FEM_ENGINES_PKG.Tech_Message (
4641 p_severity => G_LOG_LEVEL_2
4642 ,p_module => G_BLOCK||'.'||L_API_NAME
4643 ,p_msg_text => 'BEGIN'
4644 );
4645
4646 l_drv_chain_stmt :=
4647 ' select distinct d.created_by_request_id'||
4648 ' ,d.created_by_object_id'||
4649 ' from ' || p_drv_table_name || ' d'||
4650 ' where d.line_item_id = :b_statistic_basis_id'||
4651 ' and '|| p_input_ds_d_where_clause||
4652 ' and d.ledger_id = :b_ledger_id'||
4653 ' and not ('||
4654 ' d.created_by_request_id = :b_request_id'||
4655 ' and d.created_by_object_id = :b_act_rate_obj_id'||
4656 ' )'||
4657 ' and not exists ('||
4658 ' select 1'||
4659 ' from fem_pl_chains c'||
4660 ' where c.request_id = :b_request_id'||
4661 ' and c.object_id = :b_act_rate_obj_id'||
4662 ' and c.source_created_by_request_id = d.created_by_request_id'||
4663 ' and c.source_created_by_object_id = d.created_by_object_id'||
4664 ' )';
4665
4666 IF (p_drv_cond_where_clause IS NOT NULL) THEN
4667 l_drv_chain_stmt := l_drv_chain_stmt || ' AND '|| p_drv_cond_where_clause;
4668 END IF;
4669
4670 open l_drv_chain_csr
4671 for l_drv_chain_stmt
4672 using p_statistic_basis_id
4673 ,p_ledger_id
4677 ,p_act_rate_obj_id;
4674 ,p_request_id
4675 ,p_act_rate_obj_id
4676 ,p_request_id
4678 loop
4679
4680 fetch l_drv_chain_csr
4681 bulk collect into
4682 l_created_by_request_id_tbl
4683 ,l_created_by_object_id_tbl
4684 limit g_fetch_limit;
4685
4686 l_find_driver_chains_last_row := l_created_by_request_id_tbl.LAST;
4687
4688 if (l_find_driver_chains_last_row is null) then
4689 exit;
4690 end if;
4691
4692 for i in 1..l_find_driver_chains_last_row loop
4693
4694 -- Call the FEM_PL_PKG.Register_Chain API procedure to register
4695 -- the specified chain.
4696 FEM_PL_PKG.Register_Chain (
4697 p_api_version => 1.0
4698 ,p_commit => FND_API.G_FALSE
4699 ,p_request_id => p_request_id
4700 ,p_object_id => p_act_rate_obj_id
4701 ,p_source_created_by_request_id => l_created_by_request_id_tbl(i)
4702 ,p_source_created_by_object_id => l_created_by_object_id_tbl(i)
4703 ,p_user_id => p_user_id
4704 ,p_last_update_login => p_login_id
4705 ,x_msg_count => l_msg_count
4706 ,x_msg_data => l_msg_data
4707 ,x_return_status => l_return_status
4708 );
4709
4710 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
4711 Get_Put_Messages (
4712 p_msg_count => l_msg_count
4713 ,p_msg_data => l_msg_data
4714 );
4715 raise l_register_driver_chains_error;
4716 end if;
4717 end loop;
4718
4719 l_created_by_request_id_tbl.DELETE;
4720 l_created_by_object_id_tbl.DELETE;
4721
4722 commit;
4723
4724 end loop;
4725
4726 close l_drv_chain_csr;
4727
4728 FEM_ENGINES_PKG.Tech_Message (
4729 p_severity => G_LOG_LEVEL_2
4730 ,p_module => G_BLOCK||'.'||L_API_NAME
4731 ,p_msg_text => 'END'
4732 );
4733
4734 EXCEPTION
4735
4736 when l_register_driver_chains_error then
4737
4738 if (l_drv_chain_csr%ISOPEN) then
4739 close l_drv_chain_csr;
4740 end if;
4741
4742 FEM_ENGINES_PKG.Tech_Message (
4743 p_severity => G_LOG_LEVEL_6
4744 ,p_module => G_BLOCK||'.'||L_API_NAME
4745 ,p_msg_text => 'Register Driver Chains Exception'
4746 );
4747
4748 raise g_act_rate_request_error;
4749
4750 when g_act_rate_request_error then
4751
4752 if (l_drv_chain_csr%ISOPEN) then
4753 close l_drv_chain_csr;
4754 end if;
4755
4756 raise g_act_rate_request_error;
4757
4758 when others then
4759
4760 g_prg_msg := SQLERRM;
4761 g_callstack := DBMS_UTILITY.Format_Call_Stack;
4762
4763 if (l_drv_chain_csr%ISOPEN) then
4764 close l_drv_chain_csr;
4765 end if;
4766
4767 FEM_ENGINES_PKG.Tech_Message (
4768 p_severity => G_LOG_LEVEL_6
4769 ,p_module => G_BLOCK||'.'||L_API_NAME||'.Unexpected_Exception'
4770 ,p_msg_text => g_prg_msg
4771 );
4772
4773 FEM_ENGINES_PKG.Tech_Message (
4774 p_severity => G_LOG_LEVEL_6
4775 ,p_module => G_BLOCK||'.'||L_API_NAME||'.Unexpected_Exception'
4776 ,p_msg_text => g_callstack
4777 );
4778
4779 FEM_ENGINES_PKG.User_Message (
4780 p_app_name => G_FEM
4781 ,p_msg_name => G_UNEXPECTED_ERROR
4782 ,p_token1 => 'ERR_MSG'
4783 ,p_value1 => g_prg_msg
4784 );
4785
4786 raise g_act_rate_request_error;
4787
4788 END Register_Driver_Chains;
4789
4790
4791
4792 /*===========================================================================+
4793 | PROCEDURE
4794 | Register_Source_Chains
4795 |
4796 | DESCRIPTION
4797 | Register Source (FEM_BALANCES) Chains - Called from Act_Rate_Rule
4798 |
4799 | SCOPE - PRIVATE
4800 |
4801 +===========================================================================*/
4802
4803 PROCEDURE Register_Source_Chains (
4804 p_request_id in number
4805 ,p_act_rate_obj_id in number
4806 ,p_ledger_id in number
4807 ,p_input_ds_b_where_clause in long
4808 ,p_user_id in number
4809 ,p_login_id in number
4810 )
4811 IS
4812
4813 L_API_NAME constant varchar2(30) := 'Register_Source_Chains';
4814
4815 t_created_by_request_id number_type;
4816 t_created_by_object_id number_type;
4817
4818 l_find_source_chains_csr dynamic_cursor;
4819 l_find_source_chains_stmt long;
4820 l_find_source_chains_last_row number;
4821
4822 l_return_status varchar2(1);
4823 l_msg_count number;
4824 l_msg_data varchar2(240);
4825
4826 l_register_source_chains_error exception;
4827
4828 BEGIN
4829
4830 FEM_ENGINES_PKG.Tech_Message (
4831 p_severity => G_LOG_LEVEL_2
4832 ,p_module => G_BLOCK||'.'||L_API_NAME
4833 ,p_msg_text => 'BEGIN'
4834 );
4835
4836 l_find_source_chains_stmt :=
4837 ' select distinct created_by_request_id'||
4838 ' ,created_by_object_id'||
4839 ' from fem_balances b'||
4840 ' where b.ledger_id = :b_ledger_id'||
4841 ' and b.financial_elem_id not in (:b_stat_fin_elem_id, :b_act_rate_fin_elem_id)'||
4845 ' b.created_by_request_id = :b_request_id'||
4842 ' and b.currency_type_code = ''ENTERED'''||
4843 ' and '||p_input_ds_b_where_clause||
4844 ' and not ('||
4846 ' and b.created_by_object_id = :b_act_rate_obj_id'||
4847 ' )'||
4848 ' and exists ('||
4849 ' select 1'||
4850 ' from pft_ar_driver_values_t drv'||
4851 ' where drv.created_by_request_id = :b_request_id'||
4852 ' and drv.created_by_object_id = :b_act_rate_obj_id'||
4853 ' and drv.activity_id = b.activity_id'||
4854 ' )'||
4855 ' and not exists ('||
4856 ' select 1'||
4857 ' from fem_pl_chains c'||
4858 ' where c.request_id = :b_request_id'||
4859 ' and c.object_id = :b_act_rate_obj_id'||
4860 ' and c.source_created_by_request_id = b.created_by_request_id'||
4861 ' and c.source_created_by_object_id = b.created_by_object_id'||
4862 ' )';
4863
4864 open l_find_source_chains_csr
4865 for l_find_source_chains_stmt
4866 using p_ledger_id
4867 ,G_FIN_ELEM_ID_STATISTIC
4868 ,G_FIN_ELEM_ID_ACTIVITY_RATE
4869 ,p_request_id
4870 ,p_act_rate_obj_id
4871 ,p_request_id
4872 ,p_act_rate_obj_id
4873 ,p_request_id
4874 ,p_act_rate_obj_id;
4875
4876 loop
4877
4878 fetch l_find_source_chains_csr
4879 bulk collect into
4880 t_created_by_request_id
4881 ,t_created_by_object_id
4882 limit g_fetch_limit;
4883
4884 l_find_source_chains_last_row := t_created_by_request_id.LAST;
4885 if (l_find_source_chains_last_row is null) then
4886 exit;
4887 end if;
4888
4889 for i in 1..l_find_source_chains_last_row loop
4890
4891 -- Call the FEM_PL_PKG.Register_Chain API procedure to register
4892 -- the specified chain.
4893 FEM_PL_PKG.Register_Chain (
4894 p_api_version => 1.0
4895 ,p_commit => FND_API.G_FALSE
4896 ,p_request_id => p_request_id
4897 ,p_object_id => p_act_rate_obj_id
4898 ,p_source_created_by_request_id => t_created_by_request_id(i)
4899 ,p_source_created_by_object_id => t_created_by_object_id(i)
4900 ,p_user_id => p_user_id
4901 ,p_last_update_login => p_login_id
4902 ,x_msg_count => l_msg_count
4903 ,x_msg_data => l_msg_data
4904 ,x_return_status => l_return_status
4905 );
4906
4907 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
4908 Get_Put_Messages (
4909 p_msg_count => l_msg_count
4910 ,p_msg_data => l_msg_data
4911 );
4912 raise l_register_source_chains_error;
4913 end if;
4914
4915 end loop;
4916
4917 t_created_by_request_id.DELETE;
4918 t_created_by_object_id.DELETE;
4919
4920 commit;
4921
4922 end loop;
4923
4924 close l_find_source_chains_csr;
4925
4926 FEM_ENGINES_PKG.Tech_Message (
4927 p_severity => G_LOG_LEVEL_2
4928 ,p_module => G_BLOCK||'.'||L_API_NAME
4929 ,p_msg_text => 'END'
4930 );
4931
4932 EXCEPTION
4933
4934 when l_register_source_chains_error then
4935
4936 if (l_find_source_chains_csr%ISOPEN) then
4937 close l_find_source_chains_csr;
4938 end if;
4939
4940 FEM_ENGINES_PKG.Tech_Message (
4941 p_severity => g_log_level_6
4942 ,p_module => G_BLOCK||'.'||L_API_NAME
4943 ,p_msg_text => 'Register Source Chains Exception'
4944 );
4945
4946 raise g_act_rate_request_error;
4947
4948 when g_act_rate_request_error then
4949
4950 if (l_find_source_chains_csr%ISOPEN) then
4951 close l_find_source_chains_csr;
4952 end if;
4953
4954 raise g_act_rate_request_error;
4955
4956 when others then
4957
4958 g_prg_msg := SQLERRM;
4959 g_callstack := DBMS_UTILITY.Format_Call_Stack;
4960
4961 if (l_find_source_chains_csr%ISOPEN) then
4962 close l_find_source_chains_csr;
4963 end if;
4964
4965 FEM_ENGINES_PKG.Tech_Message (
4966 p_severity => g_log_level_6
4967 ,p_module => G_BLOCK||'.'||L_API_NAME||'.Unexpected_Exception'
4968 ,p_msg_text => g_prg_msg
4969 );
4970
4971 FEM_ENGINES_PKG.Tech_Message (
4972 p_severity => g_log_level_6
4973 ,p_module => G_BLOCK||'.'||L_API_NAME||'.Unexpected_Exception'
4974 ,p_msg_text => g_callstack
4975 );
4976
4977 FEM_ENGINES_PKG.User_Message (
4978 p_app_name => G_FEM
4979 ,p_msg_name => G_UNEXPECTED_ERROR
4980 ,p_token1 => 'ERR_MSG'
4981 ,p_value1 => g_prg_msg
4982 );
4983
4984 raise g_act_rate_request_error;
4985
4986 END Register_Source_Chains;
4987
4988
4989
4990 /*===========================================================================+
4991 | PROCEDURE
4992 | Rule_Post_Proc
4993 |
4994 | DESCRIPTION
4995 | Updates the status of the object execution in the
4996 | processing locks tables.
4997 |
4998 | SCOPE - PRIVATE
4999 |
5000 +===========================================================================*/
5001
5002 PROCEDURE Rule_Post_Proc (
5003 p_request_rec in request_record
5004 ,p_rule_rec in rule_record
5005 ,p_input_ds_b_where_clause in long
5006 ,p_exec_status_code in varchar2
5007 )
5008 IS
5009
5010 L_API_NAME constant varchar2(30) := 'Rule_Post_Proc';
5011
5012 l_num_of_input_rows_stmt long;
5013 l_num_of_input_rows number;
5014 l_num_of_output_rows number;
5015
5016 l_return_status varchar2(1);
5017 l_msg_count number;
5018 l_msg_data varchar2(240);
5019
5020 l_rule_post_proc_error exception;
5021
5022 BEGIN
5023
5024 FEM_ENGINES_PKG.Tech_Message (
5025 p_severity => G_LOG_LEVEL_2
5026 ,p_module => G_BLOCK||'.'||L_API_NAME
5027 ,p_msg_text => 'BEGIN'
5028 );
5029
5030 ------------------------------------------------------------------------------
5031 -- STEP 1: Drop all Temp Objects created for the Rollup Rule
5032 ------------------------------------------------------------------------------
5033 FEM_ENGINES_PKG.Tech_Message (
5034 p_severity => G_LOG_LEVEL_1
5035 ,p_module => G_BLOCK||'.'||L_API_NAME
5036 ,p_msg_text => 'Step 1: Drop all Temp Objects'
5037 );
5038
5039 Drop_Temp_Objects (
5040 p_request_rec => p_request_rec
5041 ,p_rule_rec => p_rule_rec
5042 );
5043
5047 ------------------------------------------------------------------------------
5044 ------------------------------------------------------------------------------
5045 -- STEP 2: If a successful object execution, update number of input rows in
5046 -- FEM_BALANCES before purging PFT_AR_DRIVER_VALUES_T.
5048 if (p_exec_status_code = G_EXEC_STATUS_SUCCESS) then
5049
5050 FEM_ENGINES_PKG.Tech_Message (
5051 p_severity => G_LOG_LEVEL_1
5052 ,p_module => G_BLOCK||'.'||L_API_NAME
5053 ,p_msg_text => 'Step 2: Update Number of Input Rows'
5054 );
5055
5056 l_num_of_input_rows_stmt :=
5057 ' select count(*)'||
5058 ' from fem_balances b'||
5059 ' where b.ledger_id = :b_ledger_id'||
5060 ' and b.financial_elem_id not in (:b_stat_fin_elem_id,:b_act_rate_fin_elem_id)'||
5061 ' and b.currency_type_code = ''ENTERED'''||
5062 ' and '||p_input_ds_b_where_clause||
5063 ' and not ('||
5064 ' b.created_by_request_id = :b_request_id'||
5065 ' and b.created_by_object_id = :b_act_rate_obj_id'||
5066 ' )'||
5067 ' and exists ('||
5068 ' select 1'||
5069 ' from pft_ar_driver_values_t drv'||
5070 ' where drv.created_by_request_id = :b_request_id'||
5071 ' and drv.created_by_object_id = :b_act_rate_obj_id'||
5072 ' and drv.activity_id = b.activity_id'||
5073 ' )';
5074
5075 execute immediate l_num_of_input_rows_stmt
5076 into l_num_of_input_rows
5077 using p_request_rec.ledger_id
5078 ,G_FIN_ELEM_ID_STATISTIC
5079 ,G_FIN_ELEM_ID_ACTIVITY_RATE
5080 ,p_request_rec.request_id
5081 ,p_rule_rec.act_rate_obj_id
5082 ,p_request_rec.request_id
5083 ,p_rule_rec.act_rate_obj_id;
5084
5085 FEM_PL_PKG.Update_Num_Of_Input_Rows (
5086 p_api_version => 1.0
5087 ,p_commit => FND_API.G_FALSE
5088 ,p_request_id => p_request_rec.request_id
5089 ,p_object_id => p_rule_rec.act_rate_obj_id
5090 ,p_num_of_input_rows => l_num_of_input_rows
5091 ,p_user_id => p_request_rec.user_id
5092 ,p_last_update_login => p_request_rec.login_id
5093 ,x_msg_count => l_msg_count
5094 ,x_msg_data => l_msg_data
5095 ,x_return_status => l_return_status
5096 );
5097
5098 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
5099 Get_Put_Messages (
5100 p_msg_count => l_msg_count
5101 ,p_msg_data => l_msg_data
5102 );
5103 raise l_rule_post_proc_error;
5104 end if;
5105
5106 commit;
5107
5108 end if;
5109
5110 if (p_exec_status_code = G_EXEC_STATUS_SUCCESS) then
5111 ----------------------------------------------------------------------------
5112 -- STEP 2.1: Delete all records in the PFT_AR_DRIVER_VALUES_T table
5113 ----------------------------------------------------------------------------
5114 FEM_ENGINES_PKG.Tech_Message (
5115 p_severity => G_LOG_LEVEL_1
5116 ,p_module => G_BLOCK||'.'||L_API_NAME
5117 ,p_msg_text => 'Step 2.1: Purging Records in PFT_AR_DRIVER_VALUES_T'
5118 );
5119
5120 delete from pft_ar_driver_values_t
5121 where created_by_request_id = p_request_rec.request_id
5122 and created_by_object_id = p_rule_rec.act_rate_obj_id;
5123
5124 commit;
5125
5126 ----------------------------------------------------------------------------
5127 -- STEP 2.2: Delete all records in the PFT_AR_DRIVERS_T table
5128 ----------------------------------------------------------------------------
5129
5130 FEM_ENGINES_PKG.Tech_Message (
5131 p_severity => G_LOG_LEVEL_1
5132 ,p_module => G_BLOCK||'.'||L_API_NAME
5133 ,p_msg_text => 'Step 2.2: Purging Records in PFT_AR_DRIVERS_T'
5134 );
5135
5136 delete from pft_ar_drivers_t
5137 where created_by_request_id = p_request_rec.request_id
5141
5138 and created_by_object_id = p_rule_rec.act_rate_obj_id;
5139
5140 commit;
5142 end if;
5143
5144 ------------------------------------------------------------------------------
5145 -- STEP 3: Update Number of Output Rows.
5146 ------------------------------------------------------------------------------
5147 FEM_ENGINES_PKG.Tech_Message (
5148 p_severity => G_LOG_LEVEL_1
5149 ,p_module => G_BLOCK||'.'||L_API_NAME
5150 ,p_msg_text => 'Step 3: Update Number of Output Rows'
5151 );
5152
5153 select count(*)
5154 into l_num_of_output_rows
5155 from fem_balances
5156 where dataset_code = p_request_rec.output_dataset_code
5157 and cal_period_id = p_request_rec.output_cal_period_id
5158 and created_by_request_id = p_request_rec.request_id
5159 and created_by_object_id = p_rule_rec.act_rate_obj_id
5160 and ledger_id = p_request_rec.ledger_id;
5161
5162 -- Unregister the data location for the FEM_BALANCES output table if no
5163 -- output rows were created.
5164 if (l_num_of_output_rows = 0) then
5165
5166 FEM_DIMENSION_UTIL_PKG.Unregister_Data_Location (
5167 p_request_id => p_request_rec.request_id
5168 ,p_object_id => p_rule_rec.act_rate_obj_id
5169 );
5170
5171 end if;
5172
5173 -- Set the number of output rows for the FEM_BALANCES output table.
5174 FEM_PL_PKG.Update_Num_Of_Output_Rows (
5175 p_api_version => 1.0
5176 ,p_commit => FND_API.G_FALSE
5177 ,p_request_id => p_request_rec.request_id
5178 ,p_object_id => p_rule_rec.act_rate_obj_id
5179 ,p_table_name => 'FEM_BALANCES'
5180 ,p_statement_type => 'INSERT'
5181 ,p_num_of_output_rows => l_num_of_output_rows
5182 ,p_user_id => p_request_rec.user_id
5183 ,p_last_update_login => p_request_rec.login_id
5184 ,x_msg_count => l_msg_count
5185 ,x_msg_data => l_msg_data
5186 ,x_return_status => l_return_status
5187 );
5188
5189 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
5190 Get_Put_Messages (
5191 p_msg_count => l_msg_count
5192 ,p_msg_data => l_msg_data
5193 );
5194 raise l_rule_post_proc_error;
5195 end if;
5196
5197 ------------------------------------------------------------------------------
5198 -- STEP 4: Update Object Execution Status.
5199 ------------------------------------------------------------------------------
5200 FEM_ENGINES_PKG.Tech_Message (
5201 p_severity => G_LOG_LEVEL_1
5202 ,p_module => G_BLOCK||'.'||L_API_NAME
5203 ,p_msg_text => 'Step 4: Update Object Execution Status'
5204 );
5205
5206 FEM_PL_PKG.Update_Obj_Exec_Status (
5207 p_api_version => 1.0
5208 ,p_commit => FND_API.G_FALSE
5209 ,p_request_id => p_request_rec.request_id
5210 ,p_object_id => p_rule_rec.act_rate_obj_id
5211 ,p_exec_status_code => p_exec_status_code
5212 ,p_user_id => p_request_rec.user_id
5213 ,p_last_update_login => p_request_rec.login_id
5214 ,x_msg_count => l_msg_count
5215 ,x_msg_data => l_msg_data
5216 ,x_return_status => l_return_status
5217 );
5218
5219 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
5220 Get_Put_Messages (
5221 p_msg_count => l_msg_count
5222 ,p_msg_data => l_msg_data
5223 );
5224 raise l_rule_post_proc_error;
5225 end if;
5226
5227 ------------------------------------------------------------------------------
5228 -- STEP 5: Update Object Execution Errors.
5229 ------------------------------------------------------------------------------
5230 if (p_exec_status_code <> G_EXEC_STATUS_SUCCESS) then
5231
5232 FEM_ENGINES_PKG.Tech_Message (
5233 p_severity => G_LOG_LEVEL_1
5234 ,p_module => G_BLOCK||'.'||L_API_NAME
5235 ,p_msg_text => 'Step 5: Update Object Execution Errors'
5236 );
5237
5238 -- An Activity Rate Rule is an all or nothing deal, so only 1 error can be reported
5239 FEM_PL_PKG.Update_Obj_Exec_Errors (
5240 p_api_version => 1.0
5241 ,p_commit => FND_API.G_FALSE
5242 ,p_request_id => p_request_rec.request_id
5243 ,p_object_id => p_rule_rec.act_rate_obj_id
5244 ,p_errors_reported => 1
5245 ,p_errors_reprocessed => 0
5246 ,p_user_id => p_request_rec.user_id
5247 ,p_last_update_login => p_request_rec.login_id
5248 ,x_msg_count => l_msg_count
5249 ,x_msg_data => l_msg_data
5250 ,x_return_status => l_return_status
5251 );
5252
5253 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
5254 Get_Put_Messages (
5255 p_msg_count => l_msg_count
5256 ,p_msg_data => l_msg_data
5257 );
5258 raise l_rule_post_proc_error;
5259 end if;
5260
5261 end if;
5262
5263 commit;
5264
5265 FEM_ENGINES_PKG.Tech_Message (
5266 p_severity => G_LOG_LEVEL_2
5267 ,p_module => G_BLOCK||'.'||L_API_NAME
5268 ,p_msg_text => 'END'
5269 );
5270
5271 EXCEPTION
5272
5273 when l_rule_post_proc_error then
5274
5275 FEM_ENGINES_PKG.Tech_Message (
5276 p_severity => g_log_level_6
5277 ,p_module => G_BLOCK||'.'||L_API_NAME
5278 ,p_msg_text => 'Rule Post Process Exception'
5279 );
5280
5281 FEM_ENGINES_PKG.User_Message (
5282 p_app_name => G_FEM
5283 ,p_msg_name => G_ENG_RULE_POST_PROC_ERR
5284 );
5285
5286 raise g_act_rate_request_error;
5287
5288 END Rule_Post_Proc;
5289
5290
5291
5295 |
5292 /*===========================================================================+
5293 | PROCEDURE
5294 | Request_Post_Proc
5296 | DESCRIPTION
5297 | Updates the status of the request in the processing locks tables.
5298 |
5299 | SCOPE - PRIVATE
5300 |
5301 +===========================================================================*/
5302
5303 PROCEDURE Request_Post_Proc (
5304 p_request_rec in request_record
5305 ,p_exec_status_code in varchar2
5306 )
5307 IS
5308
5309 L_API_NAME constant varchar2(30) := 'Request_Post_Proc';
5310
5311 l_return_status varchar2(1);
5312 l_msg_count number;
5313 l_msg_data varchar2(240);
5314
5315 l_request_post_proc_error exception;
5316
5317 BEGIN
5318
5319 FEM_ENGINES_PKG.Tech_Message (
5320 p_severity => G_LOG_LEVEL_2
5321 ,p_module => G_BLOCK||'.'||L_API_NAME
5322 ,p_msg_text => 'BEGIN'
5323 );
5324
5325 if (p_request_rec.submit_obj_type_code = 'RULE_SET') then
5326
5327 ----------------------------------------------------------------------------
5328 -- STEP 1: Purge RULE_SET_PROCESS_DATA table
5329 ----------------------------------------------------------------------------
5330 FEM_ENGINES_PKG.Tech_Message (
5331 p_severity => G_LOG_LEVEL_1
5332 ,p_module => G_BLOCK||'.'||L_API_NAME
5333 ,p_msg_text => 'Step 1: Purge RULE_SET_PROCESS_DATA table'
5334 );
5335
5336 FEM_RULE_SET_MANAGER.FEM_DeleteFlatRuleList_PVT (
5337 p_api_version => 1.0
5338 ,p_init_msg_list => FND_API.G_FALSE
5339 ,p_commit => FND_API.G_TRUE
5340 ,p_encoded => FND_API.G_TRUE
5341 ,x_return_status => l_return_status
5342 ,x_msg_count => l_msg_count
5343 ,x_msg_data => l_msg_data
5344 ,p_ruleset_object_id => p_request_rec.ruleset_obj_id
5345 );
5346
5347 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
5348 Get_Put_Messages (
5349 p_msg_count => l_msg_count
5350 ,p_msg_data => l_msg_data
5351 );
5352 raise l_request_post_proc_error;
5353 end if;
5354
5355 end if;
5356
5357 ------------------------------------------------------------------------------
5358 -- STEP 2: Update Request Status.
5359 ------------------------------------------------------------------------------
5360 FEM_ENGINES_PKG.Tech_Message (
5361 p_severity => G_LOG_LEVEL_1
5362 ,p_module => G_BLOCK||'.'||L_API_NAME
5363 ,p_msg_text => 'Step 2: Update Request Status'
5364 );
5365
5366 FEM_PL_PKG.Update_Request_Status (
5367 p_api_version => 1.0
5368 ,p_commit => FND_API.G_FALSE
5369 ,p_request_id => p_request_rec.request_id
5370 ,p_exec_status_code => p_exec_status_code
5371 ,p_user_id => p_request_rec.user_id
5372 ,p_last_update_login => p_request_rec.login_id
5373 ,x_msg_count => l_msg_count
5374 ,x_msg_data => l_msg_data
5375 ,x_return_status => l_return_status
5376 );
5377
5378 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
5379 Get_Put_Messages (
5380 p_msg_count => l_msg_count
5381 ,p_msg_data => l_msg_data
5382 );
5383 raise l_request_post_proc_error;
5384 end if;
5385
5386 commit;
5387
5388 ------------------------------------------------------------------------------
5389 -- STEP 3: Set the final execution status message in the log file.
5390 ------------------------------------------------------------------------------
5391 FEM_ENGINES_PKG.Tech_Message (
5392 p_severity => G_LOG_LEVEL_1
5393 ,p_module => G_BLOCK||'.'||L_API_NAME
5394 ,p_msg_text => 'Step 3: Set the final execution message in the Log File'
5395 );
5396
5397 if (p_exec_status_code = G_EXEC_STATUS_SUCCESS) then
5398 FEM_ENGINES_PKG.user_message (
5399 p_app_name => G_FEM
5400 ,p_msg_name => G_EXEC_SUCCESS
5401 );
5402 else
5403 FEM_ENGINES_PKG.user_message (
5404 p_app_name => G_FEM
5405 ,p_msg_name => G_EXEC_RERUN
5406 );
5407 end if;
5408
5409 FEM_ENGINES_PKG.Tech_Message (
5410 p_severity => G_LOG_LEVEL_2
5411 ,p_module => G_BLOCK||'.'||L_API_NAME
5412 ,p_msg_text => 'END'
5413 );
5414
5415 EXCEPTION
5416
5417 when l_request_post_proc_error then
5418
5419 FEM_ENGINES_PKG.Tech_Message (
5420 p_severity => g_log_level_6
5421 ,p_module => G_BLOCK||'.'||L_API_NAME
5422 ,p_msg_text => 'Request Post Process Exception'
5423 );
5424
5425 FEM_ENGINES_PKG.User_Message (
5426 p_app_name => G_FEM
5427 ,p_msg_name => G_ENG_REQ_POST_PROC_ERR
5428 );
5429
5430 raise g_act_rate_request_error;
5431
5432 END Request_Post_Proc;
5433
5434 /*============================================================================+
5435 | FUNCTION
5436 | Get_Lookup_Meaning
5437 |
5438 | DESCRIPTION
5439 | Utility function to return the meaning for the specified lookup type and
5440 | lookup code.
5441 |
5442 | SCOPE - PRIVATE
5443 |
5444 +============================================================================*/
5445
5446 FUNCTION Get_Lookup_Meaning (
5447 p_lookup_type in varchar2
5448 ,p_lookup_code in varchar2
5449 )
5450 RETURN varchar2
5451 IS
5452
5453 l_api_name constant varchar2(30) := 'Get_Lookup_Meaning';
5457
5454 l_meaning varchar2(80);
5455
5456 BEGIN
5458 select meaning
5459 into l_meaning
5460 from fnd_lookup_values
5461 where lookup_type = p_lookup_type
5462 and lookup_code = p_lookup_code
5463 and view_application_id = 274
5464 and language = userenv('LANG');
5465
5466 return l_meaning;
5467
5468 EXCEPTION
5469
5470 when others then
5471 return null;
5472
5473 END Get_Lookup_Meaning;
5474
5475
5476
5477 /*===========================================================================+
5478 | PROCEDURE
5479 | Get_Put_Messages
5480 |
5481 | DESCRIPTION
5482 | Copied from FEM_DATAX_LOADER_PKG. Will be replaced when Get_Put_Messages
5483 | is placed in the common loader package.
5484 |
5485 | SCOPE - PRIVATE
5486 |
5487 +===========================================================================*/
5488
5489 PROCEDURE Get_Put_Messages (
5490 p_msg_count in number
5491 ,p_msg_data in varchar2
5492 )
5493 IS
5494
5495 L_API_NAME constant varchar2(30) := 'Get_Put_Messages';
5496
5497 l_msg_count number;
5498 l_msg_data varchar2(4000);
5499 l_msg_out number;
5500 l_message varchar2(4000);
5501
5502 BEGIN
5503
5504 FEM_ENGINES_PKG.Tech_Message (
5505 p_severity => G_LOG_LEVEL_2
5506 ,p_module => G_BLOCK||'.'||L_API_NAME
5507 ,p_msg_text => 'msg_count='||p_msg_count
5508 );
5509
5510 l_msg_data := p_msg_data;
5511
5512 if (p_msg_count = 1) then
5513
5514 FND_MESSAGE.Set_Encoded(l_msg_data);
5515 l_message := FND_MESSAGE.Get;
5516
5517 FEM_ENGINES_PKG.User_Message (
5518 p_msg_text => l_message
5519 );
5520
5521 FEM_ENGINES_PKG.Tech_Message (
5522 p_severity => G_LOG_LEVEL_2
5523 ,p_module => G_BLOCK||'.'||L_API_NAME
5524 ,p_msg_text => 'msg_data='||l_message
5525 );
5526
5527 elsif (p_msg_count > 1) then
5528
5529 for i in 1..p_msg_count loop
5530
5531 FND_MSG_PUB.Get (
5532 p_msg_index => i
5533 ,p_encoded => FND_API.G_FALSE
5534 ,p_data => l_message
5535 ,p_msg_index_out => l_msg_out
5536 );
5537
5538 FEM_ENGINES_PKG.User_Message (
5539 p_msg_text => l_message
5540 );
5541
5542 FEM_ENGINES_PKG.Tech_Message (
5543 p_severity => G_LOG_LEVEL_2
5544 ,p_module => G_BLOCK||'.'||L_API_NAME
5545 ,p_msg_text => 'msg_data='||l_message
5546 );
5547
5548 end loop;
5549
5550 end if;
5551
5552 FND_MSG_PUB.Initialize;
5553
5554 END Get_Put_Messages;
5555
5556
5557
5558 END PFT_AR_ENGINE_PVT;