[Home] [Help]
PACKAGE BODY: APPS.PFT_PROFCAL_VALIDX_PUB
Source
1 PACKAGE BODY PFT_PROFCAL_VALIDX_PUB AS
2 /* $Header: PFTPVIDXB.pls 120.1 2006/05/25 10:32:44 ssthiaga noship $ */
3
4 --------------------------------------------------------------------------------
5 -- Declare package constants --
6 --------------------------------------------------------------------------------
7
8 g_object_version_number CONSTANT NUMBER := 1;
9 g_pkg_name CONSTANT VARCHAR2(30) := 'PFT_PROFCAL_VALIDX_PUB';
10
11 -- Constants for p_exec_status_code
12 g_exec_status_error_rerun CONSTANT VARCHAR2(30) := 'ERROR_RERUN';
13 g_exec_status_success CONSTANT VARCHAR2(30) := 'SUCCESS';
14
15 --Constants for output table names being registered with fem_pl_pkg
16 -- API register_table method.
17 g_fem_customer_profit CONSTANT VARCHAR2(30) := 'FEM_CUSTOMER_PROFIT';
18
19 --constant for sql_stmt_type
20 g_insert CONSTANT VARCHAR2(30) := 'INSERT';
21 g_update CONSTANT VARCHAR2(30) := 'UPDATE';
22
23 g_default_fetch_limit CONSTANT NUMBER := 99999;
24
25 g_log_level_1 CONSTANT NUMBER := FND_LOG.Level_Statement;
26 g_log_level_2 CONSTANT NUMBER := FND_LOG.Level_Procedure;
27 g_log_level_3 CONSTANT NUMBER := FND_LOG.Level_Event;
28 g_log_level_4 CONSTANT NUMBER := FND_LOG.Level_Exception;
29 g_log_level_5 CONSTANT NUMBER := FND_LOG.Level_Error;
30 g_log_level_6 CONSTANT NUMBER := FND_LOG.Level_Unexpected;
31
32 g_num_rows NUMBER := -1;
33
34 --------------------------------------------------------------------------------
35 -- Declare package variables --
36 --------------------------------------------------------------------------------
37 -- Exception variables
38 gv_prg_msg VARCHAR2(2000);
39 gv_callstack VARCHAR2(2000);
40 -- Bulk Fetch Limit
41 gv_fetch_limit NUMBER;
42
43 z_master_err_state NUMBER;
44
45 --------------------------------------------------------------------------------
46 -- Declare package exceptions --
47 --------------------------------------------------------------------------------
48 -- General profit Aggregation Engine Exception
49 e_process_single_rule_error EXCEPTION;
50 USER_EXCEPTION EXCEPTION;
51
52 --------------------------------------------------------------------------------
53 -- Declare private procedures and functions --
54 --------------------------------------------------------------------------------
55
56 PROCEDURE Update_Nbr_Of_Output_Rows (
57 p_request_id IN NUMBER
58 ,p_user_id IN NUMBER
59 ,p_login_id IN NUMBER
60 ,p_rule_obj_id IN NUMBER
61 ,p_num_output_rows IN NUMBER
62 ,p_tbl_name IN VARCHAR2
63 ,p_stmt_type IN VARCHAR2
64 );
65
66 PROCEDURE Update_Obj_Exec_Step_Status (
67 p_request_id IN NUMBER
68 ,p_user_id IN NUMBER
69 ,p_login_id IN NUMBER
70 ,p_rule_obj_id IN NUMBER
71 ,p_exe_step IN VARCHAR2
72 ,p_exe_status_code IN VARCHAR2
73 );
74
75 PROCEDURE Get_Nbr_RowsTable_Request (
76 x_rows_processed OUT NOCOPY NUMBER
77 ,x_rows_loaded OUT NOCOPY NUMBER
78 ,x_rows_rejected OUT NOCOPY NUMBER
79 ,p_request_id IN NUMBER
80 );
81
82 PROCEDURE Process_Obj_Exec_Step (
83 p_request_id IN NUMBER
84 ,p_user_id IN NUMBER
85 ,p_login_id IN NUMBER
86 ,p_rule_obj_id IN NUMBER
87 ,p_exe_step IN VARCHAR2
88 ,p_exe_status_code IN VARCHAR2
89 ,p_tbl_name IN VARCHAR2
90 );
91
92 PROCEDURE Get_Put_Messages (
93 p_msg_count IN NUMBER
94 ,p_msg_data IN VARCHAR2
95 );
96
97 FUNCTION Create_Region_Cnt_Index_Stmt (
98 p_object_id IN NUMBER
99 ,p_customer_level IN NUMBER
100 ,p_output_column IN VARCHAR2
101 ,p_cal_period_id IN NUMBER
102 ,p_effective_date IN VARCHAR2
103 ,p_dataset_code IN NUMBER
104 ,p_ledger_id IN NUMBER
105 ,p_source_system_code IN NUMBER
106 ,p_condition_clause IN LONG
107 ,p_value_index_formula_id IN NUMBER
108 ,p_rel_dimension_grp_seq IN NUMBER
109 ,p_attribute_id IN NUMBER
110 ,p_version_id IN NUMBER
111 ,p_value_set_id IN NUMBER)
112 RETURN LONG;
113
114 FUNCTION Create_Profit_Pptile_Idx_Stmt (
115 p_object_id IN NUMBER
116 ,p_customer_level IN NUMBER
117 ,p_output_column IN VARCHAR2
118 ,p_cal_period_id IN NUMBER
119 ,p_effective_date IN VARCHAR2
120 ,p_dataset_code IN NUMBER
121 ,p_ledger_id IN NUMBER
122 ,p_source_system_code IN NUMBER
123 ,p_condition_clause IN LONG
124 ,p_value_index_formula_id IN NUMBER
125 ,p_rel_dimension_grp_seq IN NUMBER
126 ,p_attribute_id IN NUMBER
127 ,p_version_id IN NUMBER
128 ,p_value_set_id IN NUMBER)
129 RETURN LONG;
130
131 FUNCTION Create_Product_Id_Index_Stmt (
132 p_object_id IN NUMBER
133 ,p_customer_level IN NUMBER
134 ,p_output_column IN VARCHAR2
135 ,p_cal_period_id IN NUMBER
136 ,p_effective_date IN VARCHAR2
137 ,p_dataset_code IN NUMBER
138 ,p_ledger_id IN NUMBER
139 ,p_source_system_code IN NUMBER
140 ,p_condition_clause IN LONG
141 ,p_value_index_formula_id IN NUMBER
142 ,p_rel_dimension_grp_seq IN NUMBER
143 ,p_attribute_id IN NUMBER
144 ,p_version_id IN NUMBER
145 ,p_value_set_id IN NUMBER)
146 RETURN LONG;
147
148 PROCEDURE Update_Nbr_Of_Input_Rows (
149 p_request_id IN NUMBER
150 ,p_user_id IN NUMBER
151 ,p_last_update_login IN NUMBER
152 ,p_rule_obj_id IN NUMBER
153 ,p_num_of_input_rows IN NUMBER
154 );
155
156 PROCEDURE Register_Updated_Column(
157 p_request_id IN NUMBER
158 ,p_object_id IN NUMBER
159 ,p_user_id IN NUMBER
160 ,p_last_update_login IN NUMBER
161 ,p_table_name IN VARCHAR2
162 ,p_statement_type IN VARCHAR2
163 ,p_column_name IN VARCHAR2
164 );
165
166 /*======--=====================================================================+
167 | PROCEDURE
168 | PROCESS SINGLE RULE
169 |
170 | DESCRIPTION
171 | Main engine procedure for Value Index step in profit calcution in PFT.
172 |
173 | SCOPE - PUBLIC
174 |
175 +============================================================================*/
176
177 PROCEDURE Process_Single_Rule (p_rule_obj_id IN NUMBER
178 ,p_cal_period_id IN NUMBER
179 ,p_dataset_io_obj_def_id IN NUMBER
180 ,p_output_dataset_code IN NUMBER
181 ,p_effective_date IN VARCHAR2
182 ,p_ledger_id IN NUMBER
183 ,p_source_system_code IN NUMBER
184 ,p_value_index_formula_id IN NUMBER
185 ,p_rule_obj_def_id IN NUMBER
186 ,p_region_counting_flag IN VARCHAR2
187 ,p_proft_percentile_flag IN VARCHAR2
188 ,p_customer_level IN NUMBER
189 ,p_cond_obj_id IN NUMBER
190 ,p_output_column IN VARCHAR2
191 ,p_exec_state IN VARCHAR2
192 ,x_return_status OUT NOCOPY VARCHAR2)
193
194 IS
195
196 l_api_name CONSTANT VARCHAR2(30) := 'Process_Single_Rule';
197
198 l_process_table VARCHAR2(30) := 'FEM_CUSTOMER_PROFIT';
199 l_table_alias VARCHAR2(5) := 'FCP';
200 l_ds_where_clause LONG := NULL;
201 l_measure_type VARCHAR2(50);
202 l_err_msg VARCHAR2(255);
203 l_reuse_slices VARCHAR2(10);
204 l_msg_count NUMBER;
205 l_exception_code VARCHAR2(50);
206 l_msg_data VARCHAR2(200);
207 l_return_status VARCHAR2(50) := NULL;
208 l_product_id NUMBER;
209 l_condition_clause LONG;
210 l_rgn_cnt_sql LONG;
211 l_prof_ptile_sql LONG;
212 l_prod_cd_sql LONG;
213 l_rel_dimension_grp_seq NUMBER;
214 l_attribute_id NUMBER;
215 l_version_id NUMBER;
216 l_effective_date DATE;
217 l_region_exists BOOLEAN;
218 l_profit_exists BOOLEAN;
219 l_product_exists BOOLEAN;
220 l_chaining_flag BOOLEAN;
221 l_region_counting_flag VARCHAR2(1);
222 l_proft_percentile_flag VARCHAR2(1);
223 l_last_row NUMBER;
224 l_gvsc_id NUMBER;
225 l_err_code NUMBER := 0;
226 l_num_msg NUMBER := 0;
227 l_value_set_id NUMBER;
228
229 l_request_id NUMBER := FND_GLOBAL.Conc_Request_Id;
230 l_user_id NUMBER := FND_GLOBAL.User_Id;
231 l_login_id NUMBER := FND_GLOBAL.Login_Id;
232
233 TYPE number_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
234 l_created_by_request_id_tbl number_type;
235 l_created_by_object_id_tbl number_type;
236
237 TYPE chaining_cursor IS REF CURSOR;
238 l_cv_chains chaining_cursor;
239
240 TYPE v_msg_list_type IS VARRAY(20) OF
241 fem_mp_process_ctl_t.message%TYPE;
242 v_msg_list v_msg_list_type;
243
244 e_process_single_rule_error EXCEPTION;
245 e_register_rule_error EXCEPTION;
246
247 l_rc_chain_stmt CONSTANT LONG :=
248 'SELECT distinct r.created_by_request_id'||
249 ',r.created_by_object_id '||
250 'FROM FEM_REGION_INFO r '||
251 'where r.ledger_id = :b_ledger_id '||
252 'and r.cal_period_id = :b_cal_period_id '||
253 'and r.dataset_code = :b_output_dataset_code '||
254 'and r.source_system_code = :b_source_system_code '||
255 'and r.dimension_group_id = :b_customer_level '||
256 'and not ('||
257 'r.created_by_request_id = :b_request_id '||
258 'and r.created_by_object_id = :b_rule_obj_id '||
259 ' )'||
260 ' and not exists ('||
261 ' select 1 '||
262 ' from fem_pl_chains c '||
263 ' where c.request_id = :b_request_id '||
264 ' and c.object_id = :b_rule_obj_id '||
265 ' and c.source_created_by_request_id = r.created_by_request_id '||
266 ' and c.source_created_by_object_id = r.created_by_object_id '||
267 ' )';
268
269 l_pp_chain_stmt CONSTANT LONG :=
270 'SELECT distinct cp.last_updated_by_request_id '||
271 ',cp.created_by_object_id '||
272 'FROM FEM_CUSTOMER_PROFIT cp '||
273 'where cp.ledger_id = :b_ledger_id '||
274 'and cp.cal_period_id = :b_cal_period_id '||
275 'and cp.dataset_code = :b_output_dataset_code '||
276 'and cp.source_system_code = :b_source_system_code '||
277 'AND (SELECT customer_level FROM pft_pprof_calc_rules '||
278 'WHERE pprof_calc_obj_def_id = :b_rule_obj_defn_id) = :b_customer_level '||
279 'and not ( '||
280 'cp.last_updated_by_request_id = :b_request_id '||
281 'and cp.created_by_object_id = :b_rule_obj_id '||
282 ' )'||
283 ' and not exists ( '||
284 ' select 1 '||
285 ' from fem_pl_chains c '||
286 ' where c.request_id = :b_request_id '||
287 ' and c.object_id = :b_rule_obj_id '||
288 ' and c.source_created_by_request_id = cp.last_updated_by_request_id '||
289 ' and c.source_created_by_object_id = cp.created_by_object_id '||
290 ' ) ';
291
292 BEGIN
293
294 -- Initialize the return status to SUCCESS
295 x_return_status := FND_API.G_RET_STS_SUCCESS;
296
297 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
298 ,p_module => G_BLOCK||'.'||l_api_name
299 ,p_msg_text => 'BEGIN');
300
301 l_effective_date := FND_DATE.Canonical_To_Date(p_effective_date);
302
303 l_region_exists := TRUE;
304 l_profit_exists := TRUE;
305 l_product_exists := TRUE;
306 l_chaining_flag := FALSE;
307
308 l_last_row := 0;
309 l_region_counting_flag := p_region_counting_flag ;
310 l_proft_percentile_flag := p_proft_percentile_flag;
311
312 BEGIN
313 FEM_ENGINES_PKG.Tech_Message (
314 p_severity => g_log_level_2
315 ,p_module => G_BLOCK||'.'||l_api_name
316 ,p_msg_text => 'Value Index Formula ' || p_value_index_formula_id);
317
318 SELECT COUNT(measure_type)
319 INTO l_measure_type
320 FROM pft_val_index_ranges
321 WHERE value_index_formula_id = p_value_index_formula_id
322 AND measure_type = 'REGION_COUNTING';
323
324 IF l_measure_type = 0 THEN
325 l_region_exists := FALSE;
326
327 FEM_ENGINES_PKG.Tech_Message (
328 p_severity => g_log_level_2
329 ,p_module => G_BLOCK||'.'||l_api_name
330 ,p_msg_text => 'Region Counting Formula does not Exist');
331
332 FEM_ENGINES_PKG.User_Message (
333 p_app_name => G_PFT
334 ,p_msg_name => G_ENG_RCNT_NO_FORMULA_ERR);
335 END IF;
336
337 EXCEPTION
338 WHEN no_data_found THEN
339 l_region_exists := FALSE;
340 RAISE;
341 WHEN OTHERS THEN
342 RAISE;
343 END;
344
345 BEGIN
346 SELECT COUNT(measure_type)
347 INTO l_measure_type
348 FROM pft_val_index_ranges
349 WHERE value_index_formula_id = p_value_index_formula_id
350 AND measure_type = 'PROFIT_PERCENTILE';
351
352 IF l_measure_type = 0 THEN
353
354 l_profit_exists := FALSE;
355
356 FEM_ENGINES_PKG.Tech_Message (
357 p_severity => g_log_level_2
358 ,p_module => G_BLOCK||'.'||l_api_name
359 ,p_msg_text => 'Profit Percentile Formula does not Exist');
360
361 FEM_ENGINES_PKG.User_Message (
362 p_app_name => G_PFT
363 ,p_msg_name => G_ENG_PPTILE_NO_FORMULA_ERR);
364
365 END IF;
366
367 EXCEPTION
368 WHEN no_data_found THEN
369 l_profit_exists := FALSE;
370 RAISE;
371 WHEN OTHERS THEN
372 RAISE;
373 END;
374
375 FEM_ENGINES_PKG.Tech_Message (
376 p_severity => g_log_level_2
377 ,p_module => G_BLOCK||'.'||l_api_name
378 ,p_msg_text => 'Getting Global VS Combo ID');
379
380 l_gvsc_id := FEM_DIMENSION_UTIL_PKG.Global_VS_Combo_ID (
381 p_ledger_id => p_ledger_id
382 ,x_err_code => l_err_code
383 ,x_num_msg => l_num_msg);
384
385 IF(l_err_code <> 0)THEN
386 FEM_ENGINES_PKG.Tech_Message (
387 p_severity => g_log_level_2
388 ,p_module => G_BLOCK||'.'||l_api_name
389 ,p_msg_text => 'No GVSC Id for the Given Ledger' || p_ledger_id);
390
391 FEM_ENGINES_PKG.User_Message (
392 p_app_name => G_PFT
393 ,p_msg_name => G_ENG_INVALID_LEDGER_ERR
394 ,p_token1 => 'LEDGER_ID'
395 ,p_value1 => p_ledger_id);
396
397 RAISE e_process_single_rule_error;
398 END IF;
399
403 ,p_msg_text => 'Getting Customer Value Set Id');
400 FEM_ENGINES_PKG.Tech_Message (
401 p_severity => g_log_level_2
402 ,p_module => G_BLOCK||'.'||l_api_name
404
405 BEGIN
406 SELECT gvsc.value_set_id
407 INTO l_value_set_id
408 FROM fem_global_vs_combo_defs gvsc,
409 fem_dimensions_b dim
410 WHERE gvsc.dimension_id = dim.dimension_id
411 AND dim.dimension_varchar_label = 'CUSTOMER'
412 AND gvsc.global_vs_combo_id = l_gvsc_id;
413 EXCEPTION
414 WHEN NO_DATA_FOUND THEN
415 FEM_ENGINES_PKG.Tech_Message (
416 p_severity => g_log_level_2
417 ,p_module => G_BLOCK||'.'||l_api_name
418 ,p_msg_text => 'No Value Set Id for the Given GVSC '|| l_gvsc_id);
419
420 FEM_ENGINES_PKG.User_Message (
421 p_app_name => G_PFT
422 ,p_msg_name => G_ENG_INVALID_GVSC_ERR
423 ,p_token1 => 'GVSC_ID'
424 ,p_value1 => l_gvsc_id);
425
426 RAISE e_process_single_rule_error;
427
428 WHEN OTHERS THEN
429 RAISE;
430 END;
431
432 BEGIN
433 SELECT COUNT(product_id)
434 INTO l_product_id
435 FROM pft_val_index_counting
436 WHERE value_index_formula_id = p_value_index_formula_id;
437
438 IF l_product_id = 0 THEN
439 l_product_exists := FALSE;
440
441 FEM_ENGINES_PKG.Tech_Message (
442 p_severity => g_log_level_2
443 ,p_module => G_BLOCK||'.'||l_api_name
444 ,p_msg_text => 'Product Formula does not Exist');
445
446 --FEM_ENGINES_PKG.User_Message (
447 -- p_app_name => G_PFT
448 -- ,p_msg_name => 'Product Formula does not Exist');
449
450 END IF;
451
452 EXCEPTION
453 WHEN no_data_found THEN
454 l_product_exists := FALSE;
455 WHEN OTHERS THEN
456 RAISE;
457 END;
458
459 FEM_ENGINES_PKG.TECH_MESSAGE(
460 p_severity => g_log_level_3
461 ,p_module => G_BLOCK||'.'||l_api_name
462 ,p_msg_text => 'Register update colmn:Value Index');
463
464 Register_Updated_Column( p_request_id => l_request_id
465 ,p_object_id => p_rule_obj_id
466 ,p_user_id => l_user_id
467 ,p_last_update_login => l_login_id
468 ,p_table_name => g_fem_customer_profit
469 ,p_statement_type => g_update
470 ,p_column_name => p_output_column);
471
472 FEM_ENGINES_PKG.Tech_Message (
473 p_severity => g_log_level_2
474 ,p_module => G_BLOCK||'.'||l_api_name
475 ,p_msg_text => 'Get The Level for which the
476 Value Index has to be calculated');
477 BEGIN
478 SELECT relative_dimension_group_seq
479 INTO l_rel_dimension_grp_seq
480 FROM fem_hier_dimension_grps
481 WHERE dimension_group_id = p_customer_level
482 AND ROWNUM = 1;
483
484 EXCEPTION
485 WHEN OTHERS THEN
486 RAISE;
487 END;
488
489 IF (p_cond_obj_id IS NOT NULL) THEN
490
491 FEM_ENGINES_PKG.Tech_Message (
492 p_severity => g_log_level_3
493 ,p_module => G_BLOCK||'.'||l_api_name
494 ,p_msg_text => 'Generating the Condition where clause');
495
496 Fem_Conditions_Api.Generate_Condition_Predicate(
497 p_api_version => g_api_version,
498 p_init_msg_list => g_false,
499 p_commit => g_false,
500 p_encoded => g_true,
501 p_condition_obj_id => p_cond_obj_id,
502 p_rule_effective_date => p_effective_date,
503 p_input_fact_table_name => l_process_table,
504 p_table_alias => l_table_alias,
505 p_display_predicate => 'N', -- Display Predicate
506 p_return_predicate_type => 'BOTH',
507 p_logging_turned_on => 'Y',
508 x_return_status => l_return_status,
509 x_msg_count => l_msg_count,
510 x_msg_data => l_msg_data,
511 x_predicate_string => l_condition_clause);
512
513 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
514 Get_Put_Messages ( p_msg_count => l_msg_count
515 ,p_msg_data => l_msg_data);
516
517 FEM_ENGINES_PKG.User_Message (
518 p_app_name => G_PFT
519 ,p_msg_name => G_ENG_COND_PRED_CLAUSE_ERR
520 ,p_token1 => 'CONDITION_OBJ_ID'
521 ,p_value1 => p_cond_obj_id);
522
523 IF (l_condition_clause IS NULL) THEN
524 FEM_ENGINES_PKG.User_Message (
525 p_app_name => G_PFT
526 ,p_msg_name => G_ENG_COND_PRED_CLAUSE_ERR
527 ,p_token1 => 'CONDITION_OBJ_ID'
528 ,p_value1 => p_cond_obj_id);
529 END IF;
530 RAISE e_process_single_rule_error;
531
532 END IF;
533 END IF;
534
535 -------------------- Register the chain if required ---------------------
536 --Step :1: Set The l_Chaining_flag
537 FEM_ENGINES_PKG.Tech_Message (
538 p_severity => g_log_level_1
542
539 ,p_module => G_BLOCK||'.'||l_api_name
540 ,p_msg_text => 'Register Chain Step:1:
541 Identify whether chaining is needed ');
543 IF l_region_counting_flag = 'N' AND l_proft_percentile_flag = 'N' THEN
544
545 IF (l_region_exists AND l_profit_exists ) OR
546 (l_region_exists) OR (l_profit_exists) THEN
547 l_chaining_flag := TRUE;
548 END IF;
549
550 ELSIF l_region_counting_flag = 'Y' AND l_proft_percentile_flag = 'N' THEN
551
552 IF l_profit_exists THEN
553 l_chaining_flag := TRUE;
554 END IF;
555
556 ELSIF l_region_counting_flag = 'N' AND l_proft_percentile_flag = 'Y' THEN
557
558 IF l_region_exists THEN
559 l_chaining_flag := TRUE;
560 END IF;
561
562 END IF;
563 --------------------------------------------------------------------------
564 --Step 2: If the l_chaining flag := TRUE call Register_Chain
565 --------------------------------------------------------------------------
566 IF (l_chaining_flag) THEN
567 FEM_ENGINES_PKG.Tech_Message (
568 p_severity => g_log_level_1
569 ,p_module => G_BLOCK||'.'||l_api_name
570 ,p_msg_text => 'Register Chain Step:2:Call PL_Chains:');
571
572 IF l_region_exists THEN
573 OPEN l_cv_chains
574 FOR l_rc_chain_stmt
575 USING p_ledger_id,
576 p_cal_period_id,
577 p_output_dataset_code,
578 p_source_system_code,
579 p_customer_level,
580 l_request_id,
581 p_rule_obj_id,
582 l_request_id,
583 p_rule_obj_id;
584
585 LOOP
586 EXIT WHEN l_cv_chains%NOTFOUND;
587
588 FETCH l_cv_chains BULK COLLECT INTO l_created_by_request_id_tbl,
589 l_created_by_object_id_tbl;
590
591 l_last_row := l_created_by_object_id_tbl.COUNT;
592
593 FOR i IN 1 .. l_last_row LOOP
594 -- Call the FEM_PL_PKG.Register_Chain API procedure to register
595 -- the specified chain.
596 FEM_PL_PKG.Register_Chain (
597 p_api_version => 1.0
598 ,p_commit => FND_API.G_FALSE
599 ,p_request_id => l_request_id
600 ,p_object_id => p_rule_obj_id
601 ,p_source_created_by_request_id => l_created_by_request_id_tbl(i)
602 ,p_source_created_by_object_id => l_created_by_object_id_tbl(i)
603 ,p_user_id => l_user_id
604 ,p_last_update_login => l_login_id
605 ,x_msg_count => l_msg_count
606 ,x_msg_data => l_msg_data
607 ,x_return_status => l_return_status);
608
609 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
610 FEM_ENGINES_PKG.User_Message (
611 p_app_name => G_PFT
612 ,p_msg_name => G_PL_REG_CHAIN_ERR);
613
614 RAISE e_process_single_rule_error;
615 END IF;
616
617 END LOOP; --End for loop
618
619 END LOOP; -- End Fetch Loop
620
621 CLOSE l_cv_chains;
622
623 END IF;
624
625 --if profit percentile rule is run
626 IF l_profit_exists THEN
627
628 OPEN l_cv_chains
629 FOR l_pp_chain_stmt
630 USING p_ledger_id,
631 p_cal_period_id,
632 p_output_dataset_code,
633 p_source_system_code,
634 p_rule_obj_def_id,
635 p_customer_level,
636 l_request_id,
637 p_rule_obj_id,
638 l_request_id,
639 p_rule_obj_id;
640
641 LOOP
642 EXIT WHEN l_cv_chains%NOTFOUND;
643
644 FETCH l_cv_chains BULK COLLECT INTO l_created_by_request_id_tbl,
645 l_created_by_object_id_tbl;
646
647 l_last_row := l_created_by_object_id_tbl.COUNT;
648
649 FOR i IN 1 .. l_last_row LOOP
650 -- Call the FEM_PL_PKG.Register_Chain API procedure to register
651 -- the specified chain.
652 FEM_PL_PKG.Register_Chain (
653 p_api_version => 1.0
654 ,p_commit => FND_API.G_FALSE
655 ,p_request_id => l_request_id
656 ,p_object_id => p_rule_obj_id
657 ,p_source_created_by_request_id => l_created_by_request_id_tbl(i)
658 ,p_source_created_by_object_id => l_created_by_object_id_tbl(i)
659 ,p_user_id => l_user_id
660 ,p_last_update_login => l_login_id
661 ,x_msg_count => l_msg_count
662 ,x_msg_data => l_msg_data
663 ,x_return_status => l_return_status);
664
665 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
666 FEM_ENGINES_PKG.User_Message (
667 p_app_name => G_PFT
668 ,p_msg_name => G_PL_REG_CHAIN_ERR);
669
670 RAISE e_process_single_rule_error;
671 END IF;
675 END LOOP; -- End fetch loop
672
673 END LOOP; --End for loop
674
676
677 CLOSE l_cv_chains;
678
679 END IF; --if region counting rule is run
680
681 l_created_by_request_id_tbl.DELETE;
682 l_created_by_object_id_tbl.DELETE;
683
684 --sshanmug
685 -- This Case is to be addressed for PFT.B
686
687 -- Issue:
688 -- Throwing error when l_profit_exists/l_region_exists flags are true and
689 -- there is no corresponding Concurrent_Req_id / Obj_id for the same.
690 --Detailed description:
691 --If Rule A is created for Region counting and Profit Percentile and let us
692 --assume that the Rule A is not run or did not complete succesfully.Now if
693 --we create a Rule B, which is run for Value Index and its value index formula
694 --refers 'Region counting and Profit Percentile'(of Rule A)
695 --then the Rule B will still run but the result data has no meaning.
696 --This can be avoided by chcking the return values of the cursor 'l_cv_chains'
697 --and throw the error message when no data found so that Rule B can never run.
698
699 END IF; --if chaining exists
700
701 -- When the given formula has Region Counting Formula defined
702 IF l_region_exists THEN
703
704 FEM_ENGINES_PKG.Tech_Message (
705 p_severity => g_log_level_3
706 ,p_module => G_BLOCK||'.'||l_api_name
707 ,p_msg_text => 'Building Region Count Value Index SQL');
708
709 -- Get the attribute and Version Ids of the Region Code of Customer
710 -- Dimension
711 SELECT dim_attr.attribute_id,ver.version_id
712 INTO l_attribute_id
713 ,l_version_id
714 FROM fem_dim_attributes_b dim_attr,
715 fem_dimensions_b xdim,
716 fem_dim_attr_versions_b ver
717 WHERE dim_attr.dimension_id = xdim.dimension_id
718 AND dim_attr.attribute_id = ver.attribute_id
719 AND dim_attr.attribute_varchar_label = 'REGION_CODE'
720 AND xdim.dimension_varchar_label = 'CUSTOMER';
721
722 -- To Create the bulk SQL to calcualte Value Index
723 --based on the Region Counting details
724 l_rgn_cnt_sql := Create_Region_Cnt_Index_Stmt(
725 p_object_id => p_rule_obj_id
726 ,p_customer_level => p_customer_level
727 ,p_output_column => p_output_column
728 ,p_cal_period_id => p_cal_period_id
729 ,p_effective_date => p_effective_date
730 ,p_dataset_code => p_output_dataset_code
731 ,p_ledger_id => p_ledger_id
732 ,p_source_system_code => p_source_system_code
733 ,p_condition_clause => l_condition_clause
734 ,p_value_index_formula_id => p_value_index_formula_id
735 ,p_rel_dimension_grp_seq => l_rel_dimension_grp_seq
736 ,p_attribute_id => l_attribute_id
737 ,p_version_id => l_version_id
738 ,p_value_set_id => l_value_set_id);
739
740 IF(p_exec_state = 'RESTART') THEN
741 l_reuse_slices := 'Y';
742 ELSE
743 l_reuse_slices := 'N';
744 END IF;
745
746 FEM_ENGINES_PKG.TECH_MESSAGE(
747 p_severity => g_log_level_1
748 ,p_module => G_BLOCK ||'.' || l_api_name
749 ,p_msg_text => l_rgn_cnt_sql);
750
751 FEM_ENGINES_PKG.TECH_MESSAGE(
752 p_severity => g_log_level_3
753 ,p_module => G_BLOCK||'.'||l_api_name
754 ,p_msg_text => 'Registering step: VALUE_INDEX');
755
756 FEM_ENGINES_PKG.TECH_MESSAGE(
757 p_severity => g_log_level_3
758 ,p_module => G_BLOCK||'.'||l_api_name
759 ,p_msg_text => 'Submitting to MP Master.p_rule_id: '
760 ||p_rule_obj_id);
761
762 FEM_ENGINES_PKG.TECH_MESSAGE(
763 p_severity => g_log_level_3
764 ,p_module => G_BLOCK||'.'||l_api_name
765 ,p_msg_text => 'Submitting Region Count SQL to MP Master.p_eng_sql: '
766 ||l_rgn_cnt_sql);
767
768 FEM_MULTI_PROC_PKG.Master(
769 p_rule_id => p_rule_obj_id
770 ,p_eng_step => 'VAL_IDX'
771 ,p_eng_sql => l_rgn_cnt_sql
772 ,p_data_table => l_process_table
773 ,p_table_alias => l_table_alias
774 ,p_run_name => NULL
775 ,p_eng_prg => NULL
776 ,p_condition => NULL
777 ,p_failed_req_id => NULL
778 ,p_reuse_slices => l_reuse_slices
779 ,x_prg_stat => l_err_msg
780 ,x_Exception_code => l_exception_code);
781
782 IF (l_err_msg <> G_COMPLETE_NORMAL) THEN
783 v_msg_list := v_msg_list_type();
784
785 SELECT DISTINCT(message)
786 BULK COLLECT INTO v_msg_list
787 FROM fem_mp_process_ctl_t
788 WHERE req_id = l_request_id
789 AND status = 2;
790
791 FEM_ENGINES_PKG.Tech_Message(
792 p_severity => g_log_level_1
793 ,p_module => G_BLOCK||'.'||l_api_name
794 ,p_msg_text => 'Total Errors : ' || TO_CHAR(v_msg_list.COUNT));
795
796 -- Log all of the messages
797 FOR i IN 1..v_msg_list.COUNT LOOP
798
799 FEM_ENGINES_PKG.Tech_Message(
803
800 p_severity => g_log_level_5
801 ,p_module => G_BLOCK||'.'||l_api_name
802 ,p_msg_text => v_msg_list(i));
804 FND_FILE.Put_Line(FND_FILE.log, v_msg_list(i));
805
806
807 END LOOP;
808
809 FEM_ENGINES_PKG.User_Message (
810 p_app_name => G_PFT
811 ,p_msg_name => G_ENG_MULTI_PROC_ERR);
812
813 Process_Obj_Exec_Step(
814 p_request_id => l_request_id
815 ,p_user_id => l_user_id
816 ,p_login_id => l_login_id
817 ,p_rule_obj_id => p_rule_obj_id
818 ,p_exe_step => 'VAL_IDX'
819 ,p_exe_status_code => g_exec_status_error_rerun
820 ,p_tbl_name => 'FEM_CUSTOMER_PROFIT');
821
822 RAISE e_process_single_rule_error;
823
824 ELSIF(l_err_msg = G_COMPLETE_NORMAL) THEN
825
826 Process_Obj_Exec_Step( p_request_id => l_request_id
827 ,p_user_id => l_user_id
828 ,p_login_id => l_login_id
829 ,p_rule_obj_id => p_rule_obj_id
830 ,p_exe_step => 'VAL_IDX'
831 ,p_exe_status_code => g_exec_status_success
832 ,p_tbl_name => 'FEM_CUSTOMER_PROFIT');
833
834 -- commit the work
835 COMMIT;
836
837 -- Purge Data Slices
838 FEM_MULTI_PROC_PKG.Delete_Data_Slices (
839 p_req_id => l_request_id);
840
841 END IF;
842 END IF;
843
844 -- When the given formula has Profit Percentile formula defined
845 IF l_profit_exists THEN
846
847 FEM_ENGINES_PKG.Tech_Message (
848 p_severity => g_log_level_3
849 ,p_module => G_BLOCK||'.'||l_api_name
850 ,p_msg_text => 'Building Profit Percentile Value Index SQL');
851
852 -- To Create the bulk SQL to calcualte Value Index
853 --based on the Profit Percentile details
854 l_prof_ptile_sql := Create_Profit_Pptile_Idx_Stmt(
855 p_object_id => p_rule_obj_id
856 ,p_customer_level => p_customer_level
857 ,p_output_column => p_output_column
858 ,p_cal_period_id => p_cal_period_id
859 ,p_effective_date => p_effective_date
860 ,p_dataset_code => p_output_dataset_code
861 ,p_ledger_id => p_ledger_id
862 ,p_source_system_code => p_source_system_code
863 ,p_condition_clause => l_condition_clause
864 ,p_value_index_formula_id => p_value_index_formula_id
865 ,p_rel_dimension_grp_seq => l_rel_dimension_grp_seq
866 ,p_attribute_id => l_attribute_id
867 ,p_version_id => l_version_id
868 ,p_value_set_id => l_value_set_id);
869
870 IF(p_exec_state = 'RESTART') THEN
871 l_reuse_slices := 'Y';
872 ELSE
873 l_reuse_slices := 'N';
874 END IF;
875
876 FEM_ENGINES_PKG.TECH_MESSAGE(
877 p_severity => g_log_level_1
878 ,p_module => G_BLOCK ||'.' || l_api_name
879 ,p_msg_text => l_prof_ptile_sql);
880
881 FEM_ENGINES_PKG.TECH_MESSAGE(
882 p_severity => g_log_level_3
883 ,p_module => G_BLOCK||'.'||l_api_name
884 ,p_msg_text => 'Registering step: VALUE_INDEX');
885
886 FEM_ENGINES_PKG.TECH_MESSAGE(
887 p_severity => g_log_level_3
888 ,p_module => G_BLOCK||'.'||l_api_name
889 ,p_msg_text => 'Submitting to MP Master.p_rule_id: '
890 ||p_rule_obj_id);
891
892 FEM_ENGINES_PKG.TECH_MESSAGE(
893 p_severity => g_log_level_3
894 ,p_module => G_BLOCK||'.'||l_api_name
895 ,p_msg_text => 'Submitting Percentile SQL to MP Master.p_eng_sql: '
896 ||l_prof_ptile_sql);
897
898 FEM_MULTI_PROC_PKG.Master(
899 p_rule_id => p_rule_obj_id
900 ,p_eng_step => 'VAL_IDX'
901 ,p_eng_sql => l_prof_ptile_sql
902 ,p_data_table => l_process_table
903 ,p_table_alias => l_table_alias
904 ,p_run_name => NULL
905 ,p_eng_prg => NULL
906 ,p_condition => NULL
907 ,p_failed_req_id => NULL
908 ,p_reuse_slices => l_reuse_slices
909 ,x_prg_stat => l_err_msg
910 ,x_Exception_code => l_exception_code);
911
912 IF (l_err_msg <> G_COMPLETE_NORMAL) THEN
913 v_msg_list := v_msg_list_type();
914
915 SELECT DISTINCT(message)
916 BULK COLLECT INTO v_msg_list
917 FROM fem_mp_process_ctl_t
918 WHERE req_id = l_request_id
919 AND status = 2;
920
921 FEM_ENGINES_PKG.Tech_Message(
922 p_severity => g_log_level_1
923 ,p_module => G_BLOCK||'.'||l_api_name
924 ,p_msg_text => 'Total Errors : ' || TO_CHAR(v_msg_list.COUNT));
925
926 -- Log all of the messages
927 FOR i IN 1..v_msg_list.COUNT LOOP
928
929 FEM_ENGINES_PKG.Tech_Message(
933
930 p_severity => g_log_level_5
931 ,p_module => G_BLOCK||'.'||l_api_name
932 ,p_msg_text => v_msg_list(i));
934 FND_FILE.Put_Line(FND_FILE.log, v_msg_list(i));
935
936 END LOOP;
937
938 FEM_ENGINES_PKG.User_Message (
939 p_app_name => G_PFT
940 ,p_msg_name => G_ENG_MULTI_PROC_ERR);
941
942 Process_Obj_Exec_Step(
943 p_request_id => l_request_id
944 ,p_user_id => l_user_id
945 ,p_login_id => l_login_id
946 ,p_rule_obj_id => p_rule_obj_id
947 ,p_exe_step => 'VAL_IDX'
948 ,p_exe_status_code => g_exec_status_error_rerun
949 ,p_tbl_name => 'FEM_CUSTOMER_PROFIT');
950
951 RAISE e_process_single_rule_error;
952
953 ELSIF(l_err_msg = G_COMPLETE_NORMAL) THEN
954
955 Process_Obj_Exec_Step( p_request_id => l_request_id
956 ,p_user_id => l_user_id
957 ,p_login_id => l_login_id
958 ,p_rule_obj_id => p_rule_obj_id
959 ,p_exe_step => 'VAL_IDX'
960 ,p_exe_status_code => g_exec_status_success
961 ,p_tbl_name => 'FEM_CUSTOMER_PROFIT');
962
963 -- commit the work
964 COMMIT;
965
966 -- Purge Data Slices
967 FEM_MULTI_PROC_PKG.Delete_Data_Slices (
968 p_req_id => l_request_id);
969
970 END IF;
971 END IF;
972
973 ------- Get all the product that matches the criteria --------------------
974
975 -- When the given formula has Product formula defined
976 IF l_product_exists THEN
977
978 FEM_ENGINES_PKG.Tech_Message (
979 p_severity => g_log_level_3
980 ,p_module => G_BLOCK||'.'||l_api_name
981 ,p_msg_text => 'Building Product attribute Value Index SQL');
982
983 -- Get the Attribute and Version Ids of the Product id of Customer
984 -- Dimension
985 SELECT dim_attr.attribute_id
986 ,ver.version_id
987 INTO l_attribute_id
988 ,l_version_id
989 FROM fem_dim_attributes_b dim_attr,
990 fem_dimensions_b xdim,
991 fem_dim_attr_versions_b ver
992 WHERE dim_attr.dimension_id = xdim.dimension_id
993 AND dim_attr.attribute_id = ver.attribute_id
994 AND dim_attr.attribute_varchar_label = 'PRODUCT_ID'
995 AND xdim.dimension_varchar_label = 'CUSTOMER';
996
997 -- To Create the bulk SQL to calcualte Value Index
998 --based on the Product Dimension details
999 l_prod_cd_sql := Create_Product_Id_Index_Stmt(
1000 p_object_id => p_rule_obj_id
1001 ,p_customer_level => p_customer_level
1002 ,p_output_column => p_output_column
1003 ,p_cal_period_id => p_cal_period_id
1004 ,p_effective_date => p_effective_date
1005 ,p_dataset_code => p_output_dataset_code
1006 ,p_ledger_id => p_ledger_id
1007 ,p_source_system_code => p_source_system_code
1008 ,p_condition_clause => l_condition_clause
1009 ,p_value_index_formula_id => p_value_index_formula_id
1010 ,p_rel_dimension_grp_seq => l_rel_dimension_grp_seq
1011 ,p_attribute_id => l_attribute_id
1012 ,p_version_id => l_version_id
1013 ,p_value_set_id => l_value_set_id);
1014
1015 IF(p_exec_state = 'RESTART') THEN
1016 l_reuse_slices := 'Y';
1017 ELSE
1018 l_reuse_slices := 'N';
1019 END IF;
1020
1021 FEM_ENGINES_PKG.TECH_MESSAGE(
1022 p_severity => g_log_level_1
1023 ,p_module => G_BLOCK ||'.' || l_api_name
1024 ,p_msg_text => l_prod_cd_sql);
1025
1026 FEM_ENGINES_PKG.Tech_Message(
1027 p_severity => g_log_level_3
1028 ,p_module => G_BLOCK||'.'||l_api_name
1029 ,p_msg_text => 'Registering step: VALUE_INDEX');
1030
1031 FEM_ENGINES_PKG.Tech_Message(
1032 p_severity => g_log_level_3
1033 ,p_module => G_BLOCK||'.'||l_api_name
1034 ,p_msg_text => 'Submitting to MP Master.p_rule_id: '
1035 ||p_rule_obj_id);
1036
1037 FEM_ENGINES_PKG.Tech_Message(
1038 p_severity => g_log_level_3
1039 ,p_module => G_BLOCK||'.'||l_api_name
1040 ,p_msg_text => 'Submitting Region Count SQL to MP Master.p_eng_sql: '
1041 ||l_prod_cd_sql);
1042
1043 FEM_MULTI_PROC_PKG.Master(
1044 p_rule_id => p_rule_obj_id
1045 ,p_eng_step => 'VAL_IDX'
1046 ,p_eng_sql => l_prod_cd_sql
1047 ,p_data_table => l_process_table
1048 ,p_table_alias => l_table_alias
1049 ,p_run_name => NULL
1050 ,p_eng_prg => NULL
1051 ,p_condition => NULL
1052 ,p_failed_req_id => NULL
1053 ,p_reuse_slices => l_reuse_slices
1054 ,x_prg_stat => l_err_msg
1055 ,x_Exception_code => l_exception_code);
1056
1060 SELECT DISTINCT(message)
1057 IF (l_err_msg <> G_COMPLETE_NORMAL) THEN
1058 v_msg_list := v_msg_list_type();
1059
1061 BULK COLLECT INTO v_msg_list
1062 FROM fem_mp_process_ctl_t
1063 WHERE req_id = l_request_id
1064 AND status = 2;
1065
1066 FEM_ENGINES_PKG.Tech_Message(
1067 p_severity => g_log_level_1
1068 ,p_module => G_BLOCK||'.'||l_api_name
1069 ,p_msg_text => 'Total Errors : ' || TO_CHAR(v_msg_list.COUNT));
1070
1071 -- Log all of the messages
1072 FOR i IN 1..v_msg_list.COUNT LOOP
1073
1074 FEM_ENGINES_PKG.Tech_Message(
1075 p_severity => g_log_level_5
1076 ,p_module => G_BLOCK||'.'||l_api_name
1077 ,p_msg_text => v_msg_list(i));
1078
1079 FND_FILE.Put_Line(FND_FILE.log, v_msg_list(i));
1080
1081 END LOOP;
1082
1083 FEM_ENGINES_PKG.User_Message (
1084 p_app_name => G_PFT
1085 ,p_msg_name => G_ENG_MULTI_PROC_ERR);
1086
1087 Process_Obj_Exec_Step(
1088 p_request_id => l_request_id
1089 ,p_user_id => l_user_id
1090 ,p_login_id => l_login_id
1091 ,p_rule_obj_id => p_rule_obj_id
1092 ,p_exe_step => 'VAL_IDX'
1093 ,p_exe_status_code => g_exec_status_error_rerun
1094 ,p_tbl_name => 'FEM_CUSTOMER_PROFIT');
1095
1096 RAISE e_process_single_rule_error;
1097
1098 ELSIF(l_err_msg = G_COMPLETE_NORMAL) THEN
1099
1100 Process_Obj_Exec_Step( p_request_id => l_request_id
1101 ,p_user_id => l_user_id
1102 ,p_login_id => l_login_id
1103 ,p_rule_obj_id => p_rule_obj_id
1104 ,p_exe_step => 'VAL_IDX'
1105 ,p_exe_status_code => g_exec_status_success
1106 ,p_tbl_name => 'FEM_CUSTOMER_PROFIT');
1107
1108 -- commit the work
1109 COMMIT;
1110
1111 -- Purge Data Slices
1112 FEM_MULTI_PROC_PKG.Delete_Data_Slices (
1113 p_req_id => l_request_id);
1114
1115 END IF;
1116 END IF;
1117
1118 IF g_num_rows = 0 THEN
1119 FEM_ENGINES_PKG.User_Message (
1120 p_app_name => G_PFT
1121 ,p_msg_name => G_ENG_NO_OP_ROWS_ERR);
1122 END IF;
1123
1124 FEM_ENGINES_PKG.Tech_Message ( p_severity => G_LOG_LEVEL_2
1125 ,p_module => G_BLOCK||'.'||l_api_name
1126 ,p_msg_text => 'END');
1127
1128 EXCEPTION
1129 WHEN e_process_single_rule_error THEN
1130
1131 IF l_cv_chains%ISOPEN THEN
1132 CLOSE l_cv_chains;
1133 END IF;
1134
1135 FEM_ENGINES_PKG.Tech_Message (
1136 p_severity => g_log_level_5
1137 ,p_module => G_BLOCK||'.'||l_api_name
1138 ,p_msg_text => 'Generate Value Index Error:
1139 Process Single Rule Exception');
1140
1141 FEM_ENGINES_PKG.User_Message (p_app_name => G_PFT
1142 ,p_msg_name => G_ENG_SINGLE_RULE_ERR);
1143
1144 x_return_status := FND_API.G_RET_STS_ERROR;
1145
1146 WHEN OTHERS THEN
1147
1148 IF l_cv_chains%ISOPEN THEN
1149 CLOSE l_cv_chains;
1150 END IF;
1151
1152 FEM_ENGINES_PKG.Tech_Message (
1153 p_severity => g_log_level_5
1154 ,p_module => G_BLOCK||'.'||l_api_name
1155 ,p_msg_text => 'Generate Value Index Error:
1156 Process Single Rule Exception');
1157
1158 FEM_ENGINES_PKG.User_Message (p_app_name => G_PFT
1159 ,p_msg_name => G_ENG_SINGLE_RULE_ERR);
1160
1161 x_return_status := FND_API.G_RET_STS_ERROR;
1162
1163 END Process_Single_Rule;
1164
1165 /*============================================================================+
1166 | PROCEDURE
1167 | Update_Num_Of_Output_Rows
1168 |
1169 | DESCRIPTION
1170 | Updates the rows successfully processed by calling
1171 | fem_pl_pkg.Update_Num_Of_Output_Rows in fem_pl_tables.
1172 |
1173 | SCOPE - PRIVATE
1174 |
1175 +============================================================================*/
1176 PROCEDURE Update_Nbr_Of_Output_Rows( p_request_id IN NUMBER
1177 ,p_user_id IN NUMBER
1178 ,p_login_id IN NUMBER
1179 ,p_rule_obj_id IN NUMBER
1180 ,p_num_output_rows IN NUMBER
1181 ,p_tbl_name IN VARCHAR2
1182 ,p_stmt_type IN VARCHAR2)
1183 IS
1184
1185 l_api_name CONSTANT VARCHAR2(30) := 'Update_Num_Of_Output_Rows';
1186
1187 l_return_status VARCHAR2(2);
1188 l_msg_count NUMBER;
1189 l_msg_data VARCHAR2(240);
1190
1191 e_upd_num_output_rows_error EXCEPTION;
1192
1193 BEGIN
1194
1195 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
1196 ,p_module => G_BLOCK||'.'||l_api_name
1197 ,p_msg_text => 'BEGIN');
1198
1202 ,p_commit => FND_API.G_TRUE
1199 -- Set the number of output rows for the output table.
1200 FEM_PL_PKG.Update_Num_Of_Output_Rows(
1201 p_api_version => 1.0
1203 ,p_request_id => p_request_id
1204 ,p_object_id => p_rule_obj_id
1205 ,p_table_name => p_tbl_name
1206 ,p_statement_type => p_stmt_type
1207 ,p_num_of_output_rows => p_num_output_rows
1208 ,p_user_id => p_user_id
1209 ,p_last_update_login => p_login_id
1210 ,x_msg_count => l_msg_count
1211 ,x_msg_data => l_msg_data
1212 ,x_return_status => l_return_status);
1213
1214 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1215
1216 Get_Put_Messages( p_msg_count => l_msg_count
1217 ,p_msg_data => l_msg_data);
1218 RAISE e_upd_num_output_rows_error;
1219 END IF;
1220
1221 FEM_ENGINES_PKG.Tech_Message( p_severity => g_log_level_2
1222 ,p_module => G_BLOCK||'.'||l_api_name
1223 ,p_msg_text => 'END');
1224
1225 EXCEPTION
1226 WHEN e_upd_num_output_rows_error THEN
1227 FEM_ENGINES_PKG.Tech_Message (
1228 p_severity => g_log_level_5
1229 ,p_module => G_BLOCK||'.'||l_api_name
1230 ,p_msg_text => 'Update Rows Exception');
1231
1232 FEM_ENGINES_PKG.User_Message (
1233 p_app_name => G_PFT
1234 ,p_msg_name => G_PL_OP_UPD_ROWS_ERR);
1235
1236 RAISE e_process_single_rule_error;
1237
1238 WHEN OTHERS THEN
1239 FEM_ENGINES_PKG.User_Message (
1240 p_app_name => G_PFT
1241 ,p_msg_name => G_PL_OP_UPD_ROWS_ERR);
1242
1243 RAISE;
1244
1245 END Update_Nbr_Of_Output_Rows;
1246
1247 /*============================================================================+
1248 | PROCEDURE
1249 | Update_Obj_Exec_Step_Status
1250 |
1251 | DESCRIPTION
1252 | Updates the status of the executuon of the object by calling
1253 | fem_pl_pkg.Update_obj_exec_step_status in fem_pl_obj_steps.
1254 |
1255 | SCOPE - PRIVATE
1256 |
1257 +============================================================================*/
1258 PROCEDURE Update_Obj_Exec_Step_Status( p_request_id IN NUMBER
1259 ,p_user_id IN NUMBER
1263 ,p_exe_status_code IN VARCHAR2)
1260 ,p_login_id IN NUMBER
1261 ,p_rule_obj_id IN NUMBER
1262 ,p_exe_step IN VARCHAR2
1264 IS
1268 l_return_status VARCHAR2(1);
1265
1266 l_api_name CONSTANT VARCHAR2(30) := 'Update_Obj_Exe_Step_Status';
1267
1269 l_msg_count NUMBER;
1270 l_msg_data VARCHAR2(240);
1271
1272 e_upd_obj_exec_step_stat_error EXCEPTION;
1273
1274 BEGIN
1275
1276 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
1277 ,p_module => G_BLOCK||'.'||l_api_name
1278 ,p_msg_text => 'BEGIN');
1279
1280 --Call the FEM_PL_PKG.Update_obj_exec_step_status API procedure
1281 --to update step staus in fem_pl_obj_steps.
1282 FEM_PL_PKG.Update_obj_Exec_Step_Status(
1283 p_api_version => 1.0
1284 ,p_commit => FND_API.G_TRUE
1285 ,p_request_id => p_request_id
1286 ,p_object_id => p_rule_obj_id
1287 ,p_exec_step => p_exe_step
1288 ,p_exec_status_code => p_exe_status_code
1289 ,p_user_id => p_user_id
1290 ,p_last_update_login => p_login_id
1291 ,x_msg_count => l_msg_count
1292 ,x_msg_data => l_msg_data
1293 ,x_return_status => l_return_status);
1294
1295 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1296 Get_Put_Messages ( p_msg_count => l_msg_count
1297 ,p_msg_data => l_msg_data);
1298 RAISE e_upd_obj_exec_step_stat_error;
1299
1300 END IF;
1301
1302 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
1303 ,p_module => G_BLOCK||'.'||l_api_name
1304 ,p_msg_text => 'END');
1305
1306 EXCEPTION
1307 WHEN e_upd_obj_exec_step_stat_error THEN
1308 FEM_ENGINES_PKG.Tech_Message (
1309 p_severity => g_log_level_5
1310 ,p_module => G_BLOCK||'.'||l_api_name
1311 ,p_msg_text => 'Update Obj Exec Step API Exception');
1312
1313 FEM_ENGINES_PKG.User_Message (
1314 p_app_name => G_PFT
1315 ,p_msg_name => G_PL_UPD_EXEC_STEP_ERR
1316 ,p_token1 => 'OBJECT_ID'
1317 ,p_value1 => p_rule_obj_id);
1318
1319 RAISE e_process_single_rule_error;
1320
1321 WHEN OTHERS THEN
1322 FEM_ENGINES_PKG.User_Message (
1323 p_app_name => G_PFT
1324 ,p_msg_name => G_PL_UPD_EXEC_STEP_ERR
1325 ,p_token1 => 'OBJECT_ID'
1326 ,p_value1 => p_rule_obj_id);
1327
1328 RAISE e_process_single_rule_error;
1329
1330 END Update_Obj_Exec_Step_Status;
1331
1332 /*============================================================================+
1333 | PROCEDURE
1334 | Get_Nbr_RowsTable_For_Request
1335 |
1336 | DESCRIPTION
1337 | To find the number rows processed by the request.
1338 |
1339 | SCOPE - PRIVATE
1340 |
1341 +============================================================================*/
1342 PROCEDURE Get_Nbr_RowsTable_Request( x_rows_processed OUT NOCOPY NUMBER,
1343 x_rows_loaded OUT NOCOPY NUMBER,
1344 x_rows_rejected OUT NOCOPY NUMBER,
1345 p_request_id IN NUMBER)
1346 IS
1347
1348 l_api_name CONSTANT VARCHAR2(30) := 'Get_Nbr_RowsTable_Request';
1349
1350 BEGIN
1351
1352 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
1353 ,p_module => G_BLOCK||'.'||l_api_name
1354 ,p_msg_text => 'BEGIN');
1355
1356 --Query the fem_mp_process_ctl_t table to get the number of rows
1357 --processed per request
1358 SELECT NVL(SUM(rows_processed),0),
1359 NVL(SUM(rows_rejected),0),
1360 NVL(SUM(rows_loaded),0)
1361 INTO x_rows_processed,
1362 x_rows_rejected,
1363 x_rows_loaded
1364 FROM fem_mp_process_ctl_t t
1365 WHERE t.req_id = p_request_id
1366 AND t.process_num > 0;
1367
1368
1369 IF (x_rows_processed = 0) THEN
1370 FEM_ENGINES_PKG.Tech_Message (
1371 p_severity => g_log_level_5
1372 ,p_module => G_BLOCK||'.'||l_api_name
1373 ,p_msg_text => 'No Rows returned by the Insert Statement');
1374
1375 IF g_num_rows <= 0 THEN
1376 g_num_rows := 0;
1377 END IF;
1378
1379 ELSE
1380 g_num_rows := x_rows_processed;
1381 END IF;
1382
1383 FEM_ENGINES_PKG.Tech_Message( p_severity => g_log_level_2
1384 ,p_module => G_BLOCK||'.'||l_api_name
1385 ,p_msg_text => 'END');
1386
1387 EXCEPTION
1388 WHEN OTHERS THEN
1389
1390 RAISE;
1391 END Get_Nbr_RowsTable_Request;
1392
1393 /*============================================================================+
1394 | PROCEDURE
1395 | Process_Obj_Exec_Step
1396 | DESCRIPTION
1397 | Processes the execution of the Object.
1398 |
1399 | SCOPE - PRIVATE
1400 |
1401 +============================================================================*/
1402 PROCEDURE Process_Obj_Exec_Step( p_request_id IN NUMBER
1403 ,p_user_id IN NUMBER
1404 ,p_login_id IN NUMBER
1405 ,p_rule_obj_id IN NUMBER
1406 ,p_exe_step IN VARCHAR2
1407 ,p_exe_status_code IN VARCHAR2
1408 ,p_tbl_name IN VARCHAR2)
1409 IS
1413 l_nbr_rejected_rows NUMBER;
1410 l_api_name VARCHAR2(30);
1411 l_nbr_output_rows NUMBER;
1412 l_nbr_input_rows NUMBER;
1414 l_nbr_loaded_rows NUMBER;
1415
1416 BEGIN
1417 l_api_name := 'Process_Obj_Exec_Step';
1418 l_nbr_output_rows := NULL;
1419 l_nbr_input_rows := NULL;
1420
1421 FEM_ENGINES_PKG.TECH_MESSAGE( p_severity => g_log_level_2
1422 ,p_module => G_BLOCK||'.'||l_api_name
1423 ,p_msg_text => 'BEGIN');
1424
1425 ------------------------------------------------------------------------
1426 --Update the status of the step
1427 ------------------------------------------------------------------------
1428
1429 FEM_ENGINES_PKG.Tech_Message(
1430 p_severity => g_log_level_3
1431 ,p_module => G_BLOCK||'.'||l_api_name
1432 ,p_msg_text => 'Update the status of the step with execution status :'
1433 ||p_exe_status_code);
1434
1435 Update_Obj_Exec_Step_Status( p_request_id => p_request_id
1436 ,p_user_id => p_user_id
1437 ,p_login_id => p_login_id
1438 ,p_rule_obj_id => p_rule_obj_id
1439 ,p_exe_step => 'VAL_IDX'
1440 ,p_exe_status_code => p_exe_status_code );
1441
1442 IF (p_exe_status_code = g_exec_status_success) THEN
1443 -- query table fem_mp_process_ctl_t to get the number of rows processed
1444 Get_Nbr_RowsTable_Request( x_rows_processed => l_nbr_output_rows,
1445 x_rows_loaded => l_nbr_loaded_rows,
1446 x_rows_rejected => l_nbr_rejected_rows,
1447 p_request_id => p_request_id);
1448
1449 FEM_ENGINES_PKG.Tech_Message(
1450 p_severity => g_log_level_3
1451 ,p_module => G_BLOCK||'.'||l_api_name
1452 ,p_msg_text => 'Rows processed for registered output table :'
1453 ||p_tbl_name);
1454
1455 -- update the number of rows processed in the registered table
1456 Update_Nbr_Of_Output_Rows(p_request_id => p_request_id
1457 ,p_user_id => p_user_id
1458 ,p_login_id => p_login_id
1459 ,p_rule_obj_id => p_rule_obj_id
1460 ,p_num_output_rows => l_nbr_output_rows
1461 ,p_tbl_name => p_tbl_name
1462 ,p_stmt_type => g_update );
1463
1464 -----------------------------------------------------------------------
1465 -- Call FEM_PL_PKG.update_num_of_input_rows();
1466 -----------------------------------------------------------------------
1467 FEM_ENGINES_PKG.TECH_MESSAGE(
1468 p_severity => g_log_level_1,
1469 p_module => G_BLOCK||'.'||l_api_name,
1470 p_msg_text => 'No:of Rows processed from input table'
1474 Update_Nbr_Of_Input_Rows( p_request_id => p_request_id
1471 ||l_nbr_loaded_rows );
1472
1473 -- update the number of rows processed in the registered table
1475 ,p_user_id => p_user_id
1476 ,p_last_update_login => p_login_id
1477 ,p_rule_obj_id => p_rule_obj_id
1478 ,p_num_of_input_rows => l_nbr_output_rows);
1479
1480 IF l_nbr_output_rows > 0 THEN
1481 FEM_ENGINES_PKG.User_Message(
1482 p_app_name => G_PFT,
1483 p_msg_name => 'PFT_PPROF_VIDX_ROW_SUMMARY',
1484 p_token1 => 'ROWSP',
1485 p_value1 => l_nbr_output_rows,
1486 p_token2 => 'ROWSL',
1487 p_value2 => l_nbr_output_rows);
1488 END IF;
1489
1490 END IF;
1491
1492 FEM_ENGINES_PKG.TECH_MESSAGE( p_severity => g_log_level_2
1493 ,p_module => G_BLOCK||'.'||l_api_name
1494 ,p_msg_text => 'END');
1495
1496 EXCEPTION
1497 WHEN OTHERS THEN
1498 RAISE e_process_single_rule_error;
1499
1500 END;
1501
1502
1503 /*=============================================================================+
1504 | FUNCTION
1505 | Create Region Count Value Index Statement
1506 |
1507 | DESCRIPTION
1508 | Creates the Bulk SQL for Region Counting Value Index
1509 |
1510 | SCOPE - PRIVATE
1511 |
1512 +============================================================================*/
1513
1514 FUNCTION Create_Region_Cnt_Index_Stmt ( p_object_id IN NUMBER
1515 ,p_customer_level IN NUMBER
1516 ,p_output_column IN VARCHAR2
1517 ,p_cal_period_id IN NUMBER
1518 ,p_effective_date IN VARCHAR2
1519 ,p_dataset_code IN NUMBER
1520 ,p_ledger_id IN NUMBER
1521 ,p_source_system_code IN NUMBER
1522 ,p_condition_clause IN LONG
1523 ,p_value_index_formula_id IN NUMBER
1524 ,p_rel_dimension_grp_seq IN NUMBER
1525 ,p_attribute_id IN NUMBER
1526 ,p_version_id IN NUMBER
1527 ,p_value_set_id IN NUMBER)
1528 RETURN LONG IS
1529
1530 l_api_name CONSTANT VARCHAR2(30) := 'Create_Region_Cnt_Index_Stmt';
1531
1532 l_update_stmt LONG;
1533 l_select_stmt LONG;
1534 l_from_stmt LONG;
1535 l_where_stmt LONG;
1536 l_request_id NUMBER;
1537 l_effective_date DATE;
1538 l_user_id NUMBER;
1539 l_login_id NUMBER := FND_GLOBAL.Login_Id;
1540
1541 BEGIN
1542 l_request_id := FND_GLOBAL.Conc_Request_Id;
1543 l_user_id := FND_GLOBAL.User_Id;
1544 l_effective_date := FND_DATE.Canonical_To_Date(p_effective_date);
1545
1546
1547
1548 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
1549 ,p_module => G_BLOCK||'.'||l_api_name
1550 ,p_msg_text => 'BEGIN');
1551
1552 l_update_stmt := ' UPDATE fem_customer_profit fcp' ||
1553 ' SET fcp.' || p_output_column ||' = NVL('||
1554 p_output_column || ',0) + ' ||
1555 ' ( SELECT NVL(factor_weight,0) ' ||
1556 ' FROM pft_val_index_ranges a, ';
1557
1558 l_select_stmt := ' ( SELECT region_pct_total_cust, ' ||
1559 ' cust.customer_id ' ||
1560 ' FROM fem_customers_b cust, ' ||
1561 ' fem_region_info fri, ' ||
1562 ' fem_customers_attr fca ';
1563
1564 l_where_stmt := ' WHERE cust.dimension_group_id = fri.dimension_group_id ' ||
1565 ' AND cust.value_set_id = ' || p_value_set_id ||
1566 ' AND fri.region_code = fca.number_assign_value ' ||
1567 ' AND fca.customer_id = cust.customer_id ' ||
1568 ' AND fca.attribute_id = ' || p_attribute_id ||
1569 ' AND fca.version_id = ' || p_version_id ||
1570 ' AND fri.cal_period_id = ' || p_cal_period_id ||
1571 ' AND fri.dataset_code = ' || p_dataset_code ||
1572 ' AND fri.source_system_code = ' || p_source_system_code ||
1573 ' AND fri.ledger_id = ' || p_ledger_id ||
1574 ' AND fri.dimension_group_id = ' || p_customer_level || ' )b ' ||
1575 ' WHERE b.region_pct_total_cust BETWEEN ' ||
1576 ' low_range AND high_range ' ||
1577 ' AND measure_type = ''REGION_COUNTING''' ||
1578 ' AND value_index_formula_id = ' || p_value_index_formula_id ||
1579 ' AND fcp.ledger_id = ' || p_ledger_id ||
1580 ' AND fcp.dataset_code = ' || p_dataset_code ||
1581 ' AND fcp.source_system_code = ' || p_source_system_code ||
1582 ' AND fcp.cal_period_id = ' || p_cal_period_id ||
1583 ' AND b.customer_id = fcp.customer_id)' || ' , ' ||
1587 ' AND fcp.dataset_code = ' || p_dataset_code||
1584 ' fcp.LAST_UPDATED_BY_REQUEST_ID = ' || l_request_id || ', ' ||
1585 ' fcp.LAST_UPDATED_BY_OBJECT_ID = ' || p_object_id ||
1586 ' WHERE fcp.cal_period_id = ' || p_cal_period_id ||
1588 ' AND fcp.source_system_code = ' || p_source_system_code ||
1589 ' AND fcp.ledger_id = ' || p_ledger_id ||
1590 ' AND fcp.customer_id IN ( SELECT cust.customer_id '||
1591 ' FROM fem_customers_b cust, ' ||
1592 ' fem_region_info fri, ' ||
1593 ' fem_customers_attr fca ' ||
1594 ' WHERE cust.dimension_group_id = fri.dimension_group_id ' ||
1595 ' AND cust.value_set_id = ' || p_value_set_id ||
1596 ' AND fri.region_code = fca.number_assign_value ' ||
1597 ' AND fca.customer_id = cust.customer_id ' ||
1598 ' AND fca.attribute_id = ' || p_attribute_id ||
1599 ' AND fca.version_id = ' || p_version_id ||
1600 ' AND fri.cal_period_id = ' || p_cal_period_id ||
1601 ' AND fri.dataset_code = ' || p_dataset_code ||
1602 ' AND fri.source_system_code = ' || p_source_system_code ||
1603 ' AND fri.ledger_id = ' || p_ledger_id ||
1604 ' AND fri.dimension_group_id = ' || p_customer_level ||' ) ' ||
1605 ' AND fcp.data_aggregation_type_code = ' || '''CUSTOMER_AGGREGATION''';
1606
1607 IF p_condition_clause IS NOT NULL THEN
1608 l_where_stmt := l_where_stmt || ' AND ' || p_condition_clause;
1609 END IF;
1610
1611 -- Creates the final where clause
1612 l_where_stmt := l_where_stmt || ' AND '|| ' {{data_slice}} ';
1613
1614 -- add mapped columns
1615 RETURN l_update_stmt || ' ' || l_select_stmt || ' ' || l_where_stmt;
1616
1617 FEM_ENGINES_PKG.Tech_Message ( p_severity => G_LOG_LEVEL_2
1618 ,p_module => G_BLOCK||'.'||l_api_name
1619 ,p_msg_text => 'END');
1620
1621 EXCEPTION
1622 WHEN OTHERS THEN
1623 RAISE e_process_single_rule_error;
1624
1625 END Create_Region_Cnt_Index_Stmt;
1626
1627 /*=============================================================================+
1628 | FUNCTION
1629 | Create Profit Percentile Value Index Statement
1630 |
1631 | DESCRIPTION
1632 | Creates the Bulk SQL for Profit Percentile Value Index
1633 |
1634 | SCOPE - PRIVATE
1635 |
1636 +============================================================================*/
1637
1638 FUNCTION Create_Profit_Pptile_Idx_Stmt ( p_object_id IN NUMBER
1639 ,p_customer_level IN NUMBER
1640 ,p_output_column IN VARCHAR2
1641 ,p_cal_period_id IN NUMBER
1642 ,p_effective_date IN VARCHAR2
1643 ,p_dataset_code IN NUMBER
1644 ,p_ledger_id IN NUMBER
1645 ,p_source_system_code IN NUMBER
1646 ,p_condition_clause IN LONG
1647 ,p_value_index_formula_id IN NUMBER
1648 ,p_rel_dimension_grp_seq IN NUMBER
1649 ,p_attribute_id IN NUMBER
1650 ,p_version_id IN NUMBER
1651 ,p_value_set_id IN NUMBER)
1652 RETURN LONG IS
1653
1654 l_api_name CONSTANT VARCHAR2(30) := 'Create_Profit_Pptile_Idx_Stmt';
1655
1656 l_update_stmt LONG;
1657 l_select_stmt LONG;
1658 l_from_stmt LONG;
1659 l_where_stmt LONG;
1660 l_request_id NUMBER;
1661 l_effective_date DATE;
1662 l_user_id NUMBER;
1663 l_login_id NUMBER := FND_GLOBAL.Login_Id;
1664
1665 BEGIN
1666 l_request_id := FND_GLOBAL.Conc_Request_Id;
1667 l_user_id := FND_GLOBAL.user_Id;
1668 l_effective_date := FND_DATE.Canonical_To_Date(p_effective_date);
1669
1670 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
1671 ,p_module => G_BLOCK||'.'||l_api_name
1672 ,p_msg_text => 'BEGIN');
1673
1674 l_update_stmt := ' UPDATE fem_customer_profit fcp' ||
1675 ' SET fcp.' || p_output_column ||' = NVL('||
1676 p_output_column || ',0) + ' ||
1677 ' ( SELECT NVL(factor_weight,0) ' ||
1678 ' FROM pft_val_index_ranges a, ';
1679
1680 l_select_stmt := ' (SELECT profit_percentile,cust.customer_id' ||
1681 ' FROM fem_customers_b cust, ' ||
1682 ' fem_customer_profit fcp ';
1683
1684 l_where_stmt := ' WHERE cust.dimension_group_id = ' || p_customer_level ||
1685 ' AND cust.value_set_id = ' || p_value_set_id ||
1686 ' AND fcp.customer_id = cust.customer_id ' ||
1687 ' AND fcp.cal_period_id = ' || p_cal_period_id ||
1688 ' AND fcp.dataset_code = ' || p_dataset_code ||
1689 ' AND fcp.source_system_code = ' || p_source_system_code ||
1690 ' AND fcp.ledger_id = ' || p_ledger_id ||
1691 ' AND fcp.data_aggregation_type_code = ' ||
1695 ' AND measure_type = ''PROFIT_PERCENTILE''' ||
1692 '''CUSTOMER_AGGREGATION''' || ' )b ' ||
1693 ' WHERE b.profit_percentile BETWEEN ' ||
1694 ' low_range AND high_range ' ||
1696 ' AND value_index_formula_id = ' || p_value_index_formula_id ||
1697 ' AND b.customer_id = fcp.customer_id )' || ' , '||
1698 ' fcp.LAST_UPDATED_BY_REQUEST_ID = ' || l_request_id || ', ' ||
1699 ' fcp.LAST_UPDATED_BY_OBJECT_ID = ' || p_object_id ||
1700 ' WHERE fcp.cal_period_id = ' || p_cal_period_id ||
1701 ' AND fcp.dataset_code = ' || p_dataset_code||
1702 ' AND fcp.source_system_code = ' || p_source_system_code ||
1703 ' AND fcp.ledger_id = ' || p_ledger_id ||
1704 ' AND fcp.customer_id IN ( SELECT cust.customer_id '||
1705 ' FROM fem_customers_b cust ' ||
1706 ' WHERE cust.dimension_group_id = ' || p_customer_level ||
1707 ' AND cust.value_set_id = ' || p_value_set_id || ' ) ' ||
1708 ' AND fcp.data_aggregation_type_code = ' || '''CUSTOMER_AGGREGATION''';
1709
1710 IF p_condition_clause IS NOT NULL THEN
1711 l_where_stmt := l_where_stmt || ' AND ' || p_condition_clause;
1712 END IF;
1713
1714 -- Creates the final where clause
1715 l_where_stmt := l_where_stmt || ' AND '|| ' {{data_slice}} ';
1716
1717 -- add mapped columns
1718 RETURN l_update_stmt || ' ' || l_select_stmt || ' ' || l_where_stmt;
1719
1720 FEM_ENGINES_PKG.Tech_Message ( p_severity => G_LOG_LEVEL_2
1721 ,p_module => G_BLOCK||'.'||l_api_name
1722 ,p_msg_text => 'END');
1723
1724 EXCEPTION
1725 WHEN OTHERS THEN
1726 RAISE e_process_single_rule_error;
1727
1728 END Create_Profit_Pptile_Idx_Stmt;
1729
1730 /*=============================================================================+
1731 | FUNCTION
1732 | Create Product ID Value Index Statement
1733 |
1734 | DESCRIPTION
1735 | Creates the Bulk SQL for Product Dimension based Value Index
1736 |
1737 | SCOPE - PRIVATE
1738 |
1739 +============================================================================*/
1740
1741 FUNCTION Create_Product_Id_Index_Stmt ( p_object_id IN NUMBER
1742 ,p_customer_level IN NUMBER
1743 ,p_output_column IN VARCHAR2
1744 ,p_cal_period_id IN NUMBER
1745 ,p_effective_date IN VARCHAR2
1746 ,p_dataset_code IN NUMBER
1747 ,p_ledger_id IN NUMBER
1748 ,p_source_system_code IN NUMBER
1749 ,p_condition_clause IN LONG
1750 ,p_value_index_formula_id IN NUMBER
1751 ,p_rel_dimension_grp_seq IN NUMBER
1752 ,p_attribute_id IN NUMBER
1753 ,p_version_id IN NUMBER
1754 ,p_value_set_id IN NUMBER)
1755 RETURN LONG IS
1756
1757 l_api_name CONSTANT VARCHAR2(30) := 'Create_Product_Id_Index_Stmt';
1758
1759 l_update_stmt LONG;
1760 l_select_stmt LONG;
1761 l_where_stmt LONG;
1762 l_request_id NUMBER;
1763 l_effective_date DATE;
1764 l_user_id NUMBER;
1765 l_login_id NUMBER := FND_GLOBAL.login_id;
1766
1767 BEGIN
1768 l_request_id := FND_GLOBAL.Conc_Request_Id;
1769 l_user_id := FND_GLOBAL.User_Id;
1770 l_effective_date := FND_DATE.Canonical_To_Date(p_effective_date);
1771
1772
1773
1774 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
1775 ,p_module => G_BLOCK||'.'||l_api_name
1776 ,p_msg_text => 'BEGIN');
1777
1778 l_update_stmt := ' UPDATE fem_customer_profit fcp' ||
1779 ' SET fcp.' || p_output_column ||' = NVL('||
1780 p_output_column || ',0) + ' ||
1781 ' ( SELECT NVL(factor_weight,0) ' ||
1782 ' FROM pft_val_index_counting a, ';
1783 l_select_stmt := ' (SELECT fca.dim_attribute_numeric_member product_id'||
1784 ' , cust.customer_id ' ||
1785 ' FROM fem_customers_b cust, ' ||
1786 ' fem_customers_attr fca';
1787 l_where_stmt := ' WHERE cust.dimension_group_id = ' || p_customer_level ||
1788 ' AND cust.value_set_id = ' || p_value_set_id ||
1789 ' AND fca.customer_id = cust.customer_id ' ||
1790 ' AND fca.attribute_id = ' || p_attribute_id ||
1791 ' AND fca.version_id = ' || p_version_id || ' )b ' ||
1792 ' WHERE b.product_id = a.product_id ' ||
1793 ' AND value_index_formula_id = ' || p_value_index_formula_id ||
1794 ' AND fcp.ledger_id = ' || p_ledger_id ||
1795 ' AND fcp.dataset_code = ' || p_dataset_code ||
1796 ' AND fcp.source_system_code = ' || p_source_system_code ||
1797 ' AND fcp.cal_period_id = ' || p_cal_period_id ||
1798 ' AND b.customer_id = fcp.customer_id)' || ' , ' ||
1802 ' AND fcp.dataset_code = ' || p_dataset_code||
1799 ' fcp.LAST_UPDATED_BY_REQUEST_ID = ' || l_request_id || ', ' ||
1800 ' fcp.LAST_UPDATED_BY_OBJECT_ID = ' || p_object_id ||
1801 ' WHERE fcp.cal_period_id = ' || p_cal_period_id ||
1803 ' AND fcp.source_system_code = ' || p_source_system_code ||
1804 ' AND fcp.ledger_id = ' || p_ledger_id ||
1805 ' AND fcp.customer_id IN ( SELECT cust.customer_id '||
1806 ' FROM fem_customers_b cust, ' ||
1807 ' fem_customers_attr fca ' ||
1808 ' WHERE cust.dimension_group_id = ' || p_customer_level ||
1809 ' AND cust.value_set_id = ' || p_value_set_id ||
1810 ' AND fca.customer_id = cust.customer_id ' ||
1811 ' AND fca.attribute_id = ' || p_attribute_id ||
1812 ' AND fca.version_id = ' || p_version_id ||' ) ' ||
1813 ' AND fcp.data_aggregation_type_code = ' || '''CUSTOMER_AGGREGATION''';
1814
1815
1816 IF p_condition_clause IS NOT NULL THEN
1817 l_where_stmt := l_where_stmt || ' AND ' || p_condition_clause;
1818 END IF;
1819
1820 -- Creates the final where clause
1821 l_where_stmt := l_where_stmt || ' AND '|| ' {{data_slice}} ';
1822
1823 -- add mapped columns
1824 RETURN l_update_stmt || ' ' || l_select_stmt || ' ' || l_where_stmt;
1825
1826 FEM_ENGINES_PKG.Tech_Message ( p_severity => G_LOG_LEVEL_2
1827 ,p_module => G_BLOCK||'.'||l_api_name
1828 ,p_msg_text => 'END');
1829
1830 EXCEPTION
1831 WHEN OTHERS THEN
1832 RAISE e_process_single_rule_error;
1833
1834 END Create_Product_Id_Index_Stmt;
1835
1836 /*============================================================================+
1837 | PROCEDURE
1838 | Get_Put_Messages
1839 |
1840 | DESCRIPTION
1841 | To put the User messages,to be placed in common loader package.
1842 |
1843 | SCOPE - PRIVATE
1844 |
1845 +============================================================================*/
1846
1847 PROCEDURE Get_Put_Messages ( p_msg_count IN NUMBER
1848 ,p_msg_data IN VARCHAR2)
1849 IS
1850
1851 l_api_name CONSTANT VARCHAR2(30) := 'Get_Put_Messages';
1852 l_msg_count NUMBER;
1853 l_msg_data VARCHAR2(4000);
1854 l_msg_out NUMBER;
1855 l_message VARCHAR2(4000);
1856
1857 BEGIN
1858
1859 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
1860 ,p_module => G_BLOCK||'.'||l_api_name
1861 ,p_msg_text => 'msg_count='||p_msg_count);
1862
1863 l_msg_data := p_msg_data;
1864
1865 IF (p_msg_count = 1) THEN
1866
1867 FND_MESSAGE.Set_Encoded(l_msg_data);
1868 l_message := FND_MESSAGE.Get;
1869
1870 FEM_ENGINES_PKG.User_Message ( p_msg_text => l_message );
1871
1872 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
1873 ,p_module => G_BLOCK||'.'||l_api_name
1874 ,p_msg_text => 'msg_data='||l_message);
1875
1876 ELSIF (p_msg_count > 1) THEN
1877
1878 FOR i IN 1..p_msg_count LOOP
1879 FND_MSG_PUB.Get ( p_msg_index => i
1880 ,p_encoded => FND_API.G_FALSE
1881 ,p_data => l_message
1882 ,p_msg_index_out => l_msg_out);
1883
1884 FEM_ENGINES_PKG.User_Message ( p_msg_text => l_message );
1885
1886 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
1887 ,p_module => G_BLOCK||'.'||l_api_name
1888 ,p_msg_text => 'msg_data = '||l_message);
1889
1890 END LOOP;
1891
1892 END IF;
1893
1894 FND_MSG_PUB.Initialize;
1895
1896 END Get_Put_Messages;
1897
1898 /*============================================================================+
1899 | PROCEDURE
1900 | Register_Updated_Column
1901 |
1902 | DESCRIPTION
1903 | This procedure is used to register a column updated during object execution
1904 |
1905 | SCOPE - PRIVATE
1906 |
1907 +============================================================================*/
1908 PROCEDURE Register_Updated_Column( p_request_id IN NUMBER
1909 ,p_object_id IN NUMBER
1910 ,p_user_id IN NUMBER
1911 ,p_last_update_login IN NUMBER
1912 ,p_table_name IN VARCHAR2
1913 ,p_statement_type IN VARCHAR2
1914 ,p_column_name IN VARCHAR2)
1915 IS
1916
1917 l_api_name CONSTANT VARCHAR2(30) := 'Register_Updated_Column';
1918
1919 l_return_status VARCHAR2(2);
1920 l_msg_count NUMBER;
1921 l_msg_data VARCHAR2(240);
1922
1923 e_reg_updated_column_error EXCEPTION;
1924
1925 BEGIN
1926 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
1927 ,p_module => G_BLOCK||'.'||l_api_name
1928 ,p_msg_text => 'BEGIN');
1929
1930 -- Set the number of output rows for the output table.
1934 ,p_request_id => p_request_id
1931 FEM_PL_PKG.register_updated_column(
1932 p_api_version => 1.0
1933 ,p_commit => FND_API.G_TRUE
1935 ,p_object_id => p_object_id
1936 ,p_table_name => p_table_name
1937 ,p_statement_type => p_statement_type
1938 ,p_column_name => p_column_name
1939 ,p_user_id => p_user_id
1940 ,p_last_update_login => p_last_update_login
1941 ,x_msg_count => l_msg_count
1942 ,x_msg_data => l_msg_data
1943 ,x_return_status => l_return_status);
1944
1945 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1946
1947 Get_Put_Messages( p_msg_count => l_msg_count
1948 ,p_msg_data => l_msg_data);
1949 RAISE e_reg_updated_column_error;
1950 END IF;
1951
1952 FEM_ENGINES_PKG.Tech_Message( p_severity => g_log_level_2
1953 ,p_module => G_BLOCK||'.'||l_api_name
1954 ,p_msg_text => 'END');
1955
1956 EXCEPTION
1957 WHEN e_reg_updated_column_error THEN
1958 FEM_ENGINES_PKG.Tech_Message (
1959 p_severity => g_log_level_5
1960 ,p_module => G_BLOCK||'.'||l_api_name
1961 ,p_msg_text => 'Register_Updated_Column_Exception');
1962
1963 FEM_ENGINES_PKG.User_Message (
1964 p_app_name => G_PFT
1965 ,p_msg_name => G_PL_REG_UPD_COL_ERR
1966 ,p_token1 => 'TABLE_NAME'
1967 ,p_value1 => p_table_name
1968 ,p_token2 => 'COLUMN_NAME'
1969 ,p_value2 => p_column_name);
1970
1971 RAISE e_process_single_rule_error;
1972
1973 WHEN OTHERS THEN
1974 FEM_ENGINES_PKG.Tech_Message (
1975 p_severity => g_log_level_5
1976 ,p_module => G_BLOCK||'.'||l_api_name
1977 ,p_msg_text => 'Register_Updated_Column_Exception');
1978
1979 FEM_ENGINES_PKG.User_Message (
1980 p_app_name => G_PFT
1981 ,p_msg_name => G_PL_REG_UPD_COL_ERR
1982 ,p_token1 => 'TABLE_NAME'
1983 ,p_value1 => p_table_name
1984 ,p_token2 => 'COLUMN_NAME'
1985 ,p_value2 => p_column_name);
1986
1987 RAISE e_process_single_rule_error;
1988
1989 END Register_Updated_Column;
1990
1991 /*============================================================================+
1992 | PROCEDURE
1993 | Update_Num_Of_Input_Rows
1994 |
1995 | DESCRIPTION
1996 | This procedure logs the total number of rows used as input into
1997 | an object execution
1998 |
1999 | SCOPE - PRIVATE
2000 |
2001 +============================================================================*/
2002
2003 PROCEDURE Update_Nbr_Of_Input_Rows( p_request_id IN NUMBER
2004 ,p_user_id IN NUMBER
2005 ,p_last_update_login IN NUMBER
2006 ,p_rule_obj_id IN NUMBER
2007 ,p_num_of_input_rows IN NUMBER )
2008 IS
2009
2010 l_api_name CONSTANT VARCHAR2(30) := 'Update_Num_Of_Input_Rows';
2011
2012 l_return_status VARCHAR2(2);
2013 l_msg_count NUMBER;
2014 l_msg_data VARCHAR2(240);
2015
2016 e_upd_num_input_rows_error EXCEPTION;
2017
2018 BEGIN
2019
2020 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
2021 ,p_module => G_BLOCK||'.'||l_api_name
2022 ,p_msg_text => 'BEGIN');
2023
2024 -- Set the number of output rows for the output table.
2025 FEM_PL_PKG.Update_Num_Of_Input_Rows(
2026 p_api_version => 1.0
2027 ,p_commit => FND_API.G_TRUE
2028 ,p_request_id => p_request_id
2029 ,p_object_id => p_rule_obj_id
2030 ,p_num_of_input_rows => p_num_of_input_rows
2031 ,p_user_id => p_user_id
2032 ,p_last_update_login => p_last_update_login
2033 ,x_msg_count => l_msg_count
2034 ,x_msg_data => l_msg_data
2035 ,x_return_status => l_return_status);
2036
2037 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2038
2039 Get_Put_Messages( p_msg_count => l_msg_count
2040 ,p_msg_data => l_msg_data);
2041 RAISE e_upd_num_input_rows_error;
2042 END IF;
2043
2044 FEM_ENGINES_PKG.Tech_Message( p_severity => g_log_level_2
2045 ,p_module => G_BLOCK||'.'||l_api_name
2046 ,p_msg_text => 'END');
2047
2048 EXCEPTION
2049 WHEN e_upd_num_input_rows_error THEN
2050 FEM_ENGINES_PKG.Tech_Message (
2051 p_severity => g_log_level_5
2052 ,p_module => G_BLOCK||'.'||l_api_name
2053 ,p_msg_text => 'Update Input Rows Exception');
2054
2055 FEM_ENGINES_PKG.User_Message (
2056 p_app_name => G_PFT
2057 ,p_msg_name => G_PL_IP_UPD_ROWS_ERR);
2058
2059 RAISE e_process_single_rule_error;
2060
2061 WHEN OTHERS THEN
2062 FEM_ENGINES_PKG.Tech_Message (
2063 p_severity => g_log_level_5
2064 ,p_module => G_BLOCK||'.'||l_api_name
2065 ,p_msg_text => 'Update Input Rows Exception');
2066
2067 FEM_ENGINES_PKG.User_Message (
2068 p_app_name => G_PFT
2069 ,p_msg_name => G_PL_IP_UPD_ROWS_ERR);
2070
2071 RAISE e_process_single_rule_error;
2072
2073 END Update_Nbr_Of_Input_Rows;
2074
2075 END PFT_PROFCAL_VALIDX_PUB;