[Home] [Help]
PACKAGE BODY: APPS.PFT_PROFCAL_CUST_PPTILE_PUB
Source
1 PACKAGE BODY PFT_PROFCAL_CUST_PPTILE_PUB AS
2 /* $Header: PFTPPCTB.pls 120.1 2006/05/25 10:26: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_CUST_PPTILE_PUB';
10
11 -- Constants for p_exec_status_code
12 g_exec_status_error_rerun CONSTANT VARCHAR2(30) := 'ERROR_RERUN';
13 g_exec_status_success CONSTANT VARCHAR2(30) := 'SUCCESS';
14
15 --Constants for output table names being registered with fem_pl_pkg
16 -- API register_table method.
17 g_fem_customer_profit CONSTANT VARCHAR2(30) := 'FEM_CUSTOMER_PROFIT';
18 g_fem_customer_percentile_gt CONSTANT VARCHAR2(30) := 'FEM_CUSTOMER_PERCENTILE_GT';
19
20 --constant for sql_stmt_type
21 g_insert CONSTANT VARCHAR2(30) := 'INSERT';
22 g_update CONSTANT VARCHAR2(30) := 'UPDATE';
23
24 g_default_fetch_limit CONSTANT NUMBER := 99999;
25
26 g_log_level_1 CONSTANT NUMBER := FND_LOG.Level_Statement;
27 g_log_level_2 CONSTANT NUMBER := FND_LOG.Level_Procedure;
28 g_log_level_3 CONSTANT NUMBER := FND_LOG.Level_Event;
29 g_log_level_4 CONSTANT NUMBER := FND_LOG.Level_Exception;
30 g_log_level_5 CONSTANT NUMBER := FND_LOG.Level_Error;
31 g_log_level_6 CONSTANT NUMBER := FND_LOG.Level_Unexpected;
32
33 --------------------------------------------------------------------------------
34 -- Declare package variables --
35 --------------------------------------------------------------------------------
36 -- Exception variables
37 gv_prg_msg VARCHAR2(2000);
38 gv_callstack VARCHAR2(2000);
39 -- Bulk Fetch Limit
40 gv_fetch_limit NUMBER;
41
42 z_master_err_state NUMBER;
43
44 --------------------------------------------------------------------------------
45 -- Declare package exceptions --
46 --------------------------------------------------------------------------------
47 -- General profit Aggregation Engine Exception
48 e_process_single_rule_error EXCEPTION;
49 USER_EXCEPTION EXCEPTION;
50
51 --------------------------------------------------------------------------------
52 -- Declare private procedures and functions --
53 --------------------------------------------------------------------------------
54
55 PROCEDURE Update_Nbr_Of_Output_Rows(
56 p_request_id IN NUMBER
57 ,p_user_id IN NUMBER
58 ,p_login_id IN NUMBER
59 ,p_rule_obj_id IN NUMBER
60 ,p_num_output_rows IN NUMBER
61 ,p_tbl_name IN VARCHAR2
62 ,p_stmt_type IN VARCHAR2
63 );
64
65 PROCEDURE Update_Obj_Exec_Step_Status(
66 p_request_id IN NUMBER
67 ,p_user_id IN NUMBER
68 ,p_login_id IN NUMBER
69 ,p_rule_obj_id IN NUMBER
70 ,p_exe_step IN VARCHAR2
71 ,p_exe_status_code IN VARCHAR2
72 );
73
74 PROCEDURE Process_Obj_Exec_Step(
75 p_request_id IN NUMBER
76 ,p_user_id IN NUMBER
77 ,p_login_id IN NUMBER
78 ,p_rule_obj_id IN NUMBER
79 ,p_exe_step IN VARCHAR2
80 ,p_exe_status_code IN VARCHAR2
81 ,p_tbl_name IN VARCHAR2
82 ,p_num_rows IN NUMBER
83 );
84
85 PROCEDURE Get_Put_Messages (
86 p_msg_count IN NUMBER
87 ,p_msg_data IN VARCHAR2
88 );
89
90 FUNCTION Create_Pptile_Update_Stmt (
91 p_rule_obj_id IN NUMBER
92 ,p_table_name IN VARCHAR2
93 ,p_cal_period_id IN NUMBER
94 ,p_effective_date IN VARCHAR2
95 ,p_dataset_code IN NUMBER
96 ,p_ledger_id IN NUMBER
97 ,p_source_system_code IN NUMBER
98 ,p_ds_where_clause IN LONG)
99
100 RETURN LONG;
101
102 PROCEDURE Update_Nbr_Of_Input_Rows(
103 p_request_id IN NUMBER
104 ,p_user_id IN NUMBER
105 ,p_last_update_login IN NUMBER
106 ,p_rule_obj_id IN NUMBER
107 ,p_num_of_input_rows IN NUMBER
108 );
109
110 /*======--=====================================================================+
111 | PROCEDURE
112 | PROCESS SINGLE RULE
113 |
114 | DESCRIPTION
115 | Main engine procedure for region counting step in profit calcution in PFT.
116 |
117 | SCOPE - PUBLIC
118 |
119 +============================================================================*/
120
121 PROCEDURE Process_Single_Rule ( p_rule_obj_id IN NUMBER
122 ,p_cal_period_id IN NUMBER
123 ,p_dataset_io_obj_def_id IN NUMBER
124 ,p_output_dataset_code IN NUMBER
125 ,p_effective_date IN VARCHAR2
126 ,p_ledger_id IN NUMBER
127 ,p_source_system_code IN NUMBER
128 ,p_customer_level IN NUMBER
129 ,p_exec_state IN VARCHAR2
130 ,x_return_status OUT NOCOPY VARCHAR2)
131
132 IS
133
134 l_api_name CONSTANT VARCHAR2(30) := 'Process_Single_Rule';
135
136 l_process_table VARCHAR2(30) := 'FEM_CUSTOMER_PROFIT';
137 l_table_alias VARCHAR2(5) := 'FCP';
138 l_ds_where_clause LONG := NULL;
139 l_insert_sql LONG;
140 l_update_sql LONG;
141 l_err_msg VARCHAR2(255);
142 l_reuse_slices VARCHAR2(10);
143 l_msg_count NUMBER;
144 l_exception_code VARCHAR2(50);
145 l_msg_data VARCHAR2(200);
146 l_return_status VARCHAR2(50) := NULL;
147 l_null_string VARCHAR2(10) := NULL;
148 l_request_id NUMBER := FND_GLOBAL.Conc_Request_Id;
149 l_user_id NUMBER := FND_GLOBAL.User_Id;
150 l_login_id NUMBER := FND_GLOBAL.Login_Id;
151 l_num_rows_loaded NUMBER;
152
153 TYPE v_msg_list_type IS VARRAY(20) OF
154 fem_mp_process_ctl_t.message%TYPE;
155 v_msg_list v_msg_list_type;
156
157 e_register_rule_error EXCEPTION;
158
159 BEGIN
160 -- Initialize the return status to SUCCESS
161 x_return_status := FND_API.G_RET_STS_SUCCESS;
162
163 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
164 ,p_module => G_BLOCK||'.'||l_api_name
165 ,p_msg_text => 'BEGIN');
166
167 FEM_ENGINES_PKG.Tech_Message (
168 p_severity => g_log_level_3
169 ,p_module => G_BLOCK||'.'||l_api_name
170 ,p_msg_text => 'Generating the dataset where clause');
171
172 FEM_DS_WHERE_CLAUSE_GENERATOR.Fem_Gen_Ds_WClause_Pvt(
173 p_api_version => G_CALLING_API_VERSION
174 ,p_init_msg_list => FND_API.G_TRUE
175 ,p_encoded => FND_API.G_TRUE
176 ,x_return_status => l_return_status
177 ,x_msg_count => l_msg_count
178 ,x_msg_data => l_msg_data
179 ,p_ds_io_def_id => p_dataset_io_obj_def_id
180 ,p_output_period_id => p_cal_period_id
181 ,p_table_alias => l_table_alias
182 ,p_table_name => l_process_table
183 ,p_ledger_id => p_ledger_id
184 ,p_where_clause => l_ds_where_clause);
185
186 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
187 Get_Put_Messages ( p_msg_count => l_msg_count
188 ,p_msg_data => l_msg_data);
189 FEM_ENGINES_PKG.User_Message (
190 p_app_name => G_PFT
191 ,p_msg_name => G_ENG_DS_WHERE_CLAUSE_ERR
192 ,p_token1 => 'OUTPUT_DS_CODE'
193 ,p_value1 => p_dataset_io_obj_def_id
194 ,p_token2 => 'CAL_PERIOD_ID'
195 ,p_value2 => p_cal_period_id);
196
197 IF (l_ds_where_clause IS NULL) THEN
198 FEM_ENGINES_PKG.User_Message (
199 p_app_name => G_PFT
200 ,p_msg_name => G_ENG_DS_WHERE_CLAUSE_ERR
201 ,p_token1 => 'OUTPUT_DS_CODE'
202 ,p_value1 => p_dataset_io_obj_def_id
203 ,p_token2 => 'CAL_PERIOD_ID'
204 ,p_value2 => p_cal_period_id);
205 END IF;
206 RAISE e_process_single_rule_error;
207
208 END IF;
209
210 -- CHECKPOINT RESTART
211 -- check executed state and jump to appropriate statement
212 -- depending on which step was last executed successfully
213 IF(p_exec_state = 'RESTART') THEN
214 l_reuse_slices := 'Y';
215 ELSE
216 l_reuse_slices := 'N';
217 END IF;
218
219 FEM_ENGINES_PKG.Tech_Message (
220 p_severity => g_log_level_3
221 ,p_module => G_BLOCK||'.'||l_api_name
222 ,p_msg_text => 'Building Update SQL');
223
224 -- To create the UPDATE statement for the Region Counting Step.
225 l_update_sql := Create_Pptile_Update_Stmt(
226 p_rule_obj_id => p_rule_obj_id
227 ,p_table_name => l_process_table
228 ,p_cal_period_id => p_cal_period_id
229 ,p_effective_date => p_effective_date
230 ,p_dataset_code => p_output_dataset_code
231 ,p_ledger_id => p_ledger_id
232 ,p_source_system_code => p_source_system_code
233 ,p_ds_where_clause => l_ds_where_clause);
234
235 FEM_ENGINES_PKG.TECH_MESSAGE(
236 p_severity => g_log_level_3
237 ,p_module => G_BLOCK||'.'||l_api_name
238 ,p_msg_text => 'Update Sql'|| l_update_sql);
239
240 BEGIN
241 FEM_ENGINES_PKG.TECH_MESSAGE(
242 p_severity => g_log_level_3
243 ,p_module => G_BLOCK||'.'||l_api_name
244 ,p_msg_text => 'Issuing the Execute Immediate Stmt');
245
246 EXECUTE IMMEDIATE l_update_sql;
247
248 l_num_rows_loaded := SQL%ROWCOUNT;
249 EXCEPTION
250 WHEN OTHERS THEN
251 gv_prg_msg := SQLERRM;
252
253 FEM_ENGINES_PKG.Tech_Message (
254 p_severity => g_log_level_3
255 ,p_module => G_BLOCK||'.'||l_api_name
256 ,p_msg_text => 'UPDATE STATEMENT ERROR');
257
258 FEM_ENGINES_PKG.Tech_Message (
259 p_severity => g_log_level_6
260 ,p_module => G_BLOCK||'.'||l_api_name||'.Unexpected Exception'
261 ,p_msg_text => gv_prg_msg);
262
263 FEM_ENGINES_PKG.User_Message (
264 p_app_name => G_FEM
265 ,p_msg_name => G_UNEXPECTED_ERROR
266 ,p_token1 => 'ERR_MSG'
267 ,p_value1 => gv_prg_msg);
268
269 Process_Obj_Exec_Step(
270 p_request_id => l_request_id
271 ,p_user_id => l_user_id
272 ,p_login_id => l_login_id
273 ,p_rule_obj_id => p_rule_obj_id
274 ,p_exe_step => 'CUST_PPTILE'
275 ,p_exe_status_code => g_exec_status_error_rerun
276 ,p_tbl_name => g_fem_customer_profit
277 ,p_num_rows => NULL);
278
279 RAISE e_process_single_rule_error;
280 END;
281
282 Process_Obj_Exec_Step( p_request_id => l_request_id
283 ,p_user_id => l_user_id
284 ,p_login_id => l_login_id
285 ,p_rule_obj_id => p_rule_obj_id
286 ,p_exe_step => 'CUST_PPTILE'
287 ,p_exe_status_code => g_exec_status_success
288 ,p_tbl_name => g_fem_customer_profit
289 ,p_num_rows => NVL(l_num_rows_loaded,0));
290
291 -- commit the work
292 COMMIT;
293
297
294 FEM_ENGINES_PKG.Tech_Message ( p_severity => G_LOG_LEVEL_2
295 ,p_module => G_BLOCK||'.'||l_api_name
296 ,p_msg_text => 'END');
298 EXCEPTION
299 WHEN e_process_single_rule_error THEN
300
301 FEM_ENGINES_PKG.Tech_Message (
302 p_severity => g_log_level_5
303 ,p_module => G_BLOCK||'.'||l_api_name
304 ,p_msg_text => 'Process Single Rule Exception');
305
306 FEM_ENGINES_PKG.User_Message (p_app_name => G_PFT
307 ,p_msg_name => G_ENG_SINGLE_RULE_ERR);
308
309 x_return_status := FND_API.G_RET_STS_ERROR;
310
311 WHEN OTHERS THEN
312 FEM_ENGINES_PKG.User_Message (p_app_name => G_PFT
313 ,p_msg_name => G_ENG_SINGLE_RULE_ERR);
314
315 x_return_status := FND_API.G_RET_STS_ERROR;
316
317 END Process_Single_Rule;
318
319 /*============================================================================+
320 | PROCEDURE
321 | Update_Num_Of_Output_Rows
322 |
323 | DESCRIPTION
324 | Updates the rows successfully processed by calling
325 | fem_pl_pkg.Update_Num_Of_Output_Rows in fem_pl_tables.
326 |
327 | SCOPE - PRIVATE
328 |
329 +============================================================================*/
330 PROCEDURE Update_Nbr_Of_Output_Rows( p_request_id IN NUMBER
331 ,p_user_id IN NUMBER
332 ,p_login_id IN NUMBER
333 ,p_rule_obj_id IN NUMBER
334 ,p_num_output_rows IN NUMBER
335 ,p_tbl_name IN VARCHAR2
336 ,p_stmt_type IN VARCHAR2)
337 IS
338
339 l_api_name CONSTANT VARCHAR2(30) := 'Update_Num_Of_Output_Rows';
340
341 l_return_status VARCHAR2(2);
342 l_msg_count NUMBER;
343 l_msg_data VARCHAR2(240);
344
345 e_upd_num_output_rows_error EXCEPTION;
346
347 BEGIN
348
349 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
350 ,p_module => G_BLOCK||'.'||l_api_name
351 ,p_msg_text => 'BEGIN');
352
353 -- Set the number of output rows for the output table.
354 FEM_PL_PKG.Update_Num_Of_Output_Rows(
355 p_api_version => 1.0
356 ,p_commit => FND_API.G_TRUE
357 ,p_request_id => p_request_id
358 ,p_object_id => p_rule_obj_id
359 ,p_table_name => p_tbl_name
360 ,p_statement_type => p_stmt_type
361 ,p_num_of_output_rows => p_num_output_rows
362 ,p_user_id => p_user_id
363 ,p_last_update_login => p_login_id
364 ,x_msg_count => l_msg_count
365 ,x_msg_data => l_msg_data
366 ,x_return_status => l_return_status);
367
368 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
369
370 Get_Put_Messages( p_msg_count => l_msg_count
371 ,p_msg_data => l_msg_data);
372
373 RAISE e_upd_num_output_rows_error;
374 END IF;
375
376 FEM_ENGINES_PKG.Tech_Message( p_severity => g_log_level_2
377 ,p_module => G_BLOCK||'.'||l_api_name
378 ,p_msg_text => 'END');
379
380 EXCEPTION
381 WHEN e_upd_num_output_rows_error THEN
382 FEM_ENGINES_PKG.Tech_Message (
383 p_severity => g_log_level_5
384 ,p_module => G_BLOCK||'.'||l_api_name
385 ,p_msg_text => 'Update Rows Exception');
386
387 FEM_ENGINES_PKG.User_Message (
388 p_app_name => G_PFT
389 ,p_msg_name => G_PL_OP_UPD_ROWS_ERR);
390
391 RAISE e_process_single_rule_error;
392
393 WHEN OTHERS THEN
394 FEM_ENGINES_PKG.User_Message (
395 p_app_name => G_PFT
396 ,p_msg_name => G_PL_OP_UPD_ROWS_ERR);
397
398 RAISE e_process_single_rule_error;
399
400 END Update_Nbr_Of_Output_Rows;
401
402 /*============================================================================+
403 | PROCEDURE
404 | Update_Obj_Exec_Step_Status
405 |
406 | DESCRIPTION
407 | Updates the status of the executuon of the object by calling
408 | fem_pl_pkg.Update_obj_exec_step_status in fem_pl_obj_steps.
409 |
410 | SCOPE - PRIVATE
411 |
412 +============================================================================*/
413 PROCEDURE Update_Obj_Exec_Step_Status( p_request_id IN NUMBER
414 ,p_user_id IN NUMBER
415 ,p_login_id IN NUMBER
416 ,p_rule_obj_id IN NUMBER
417 ,p_exe_step IN VARCHAR2
418 ,p_exe_status_code IN VARCHAR2)
419 IS
420
421 l_api_name CONSTANT VARCHAR2(30) := 'Update_Obj_Exe_Step_Status';
422
423 l_return_status VARCHAR2(1);
424 l_msg_count NUMBER;
425 l_msg_data VARCHAR2(240);
426
427 e_upd_obj_exec_step_stat_error EXCEPTION;
428
429 BEGIN
430
431 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
435 --Call the FEM_PL_PKG.Update_obj_exec_step_status API procedure
432 ,p_module => G_BLOCK||'.'||l_api_name
433 ,p_msg_text => 'BEGIN');
434
436 --to update step staus in fem_pl_obj_steps.
437 FEM_PL_PKG.Update_Obj_Exec_Step_Status(
438 p_api_version => 1.0
439 ,p_commit => FND_API.G_TRUE
440 ,p_request_id => p_request_id
441 ,p_object_id => p_rule_obj_id
442 ,p_exec_step => p_exe_step
443 ,p_exec_status_code => p_exe_status_code
444 ,p_user_id => p_user_id
445 ,p_last_update_login => p_login_id
446 ,x_msg_count => l_msg_count
447 ,x_msg_data => l_msg_data
448 ,x_return_status => l_return_status);
449
450 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
451 Get_Put_Messages ( p_msg_count => l_msg_count
452 ,p_msg_data => l_msg_data);
453 RAISE e_upd_obj_exec_step_stat_error;
454
455 END IF;
456
457 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
458 ,p_module => G_BLOCK||'.'||l_api_name
459 ,p_msg_text => 'END');
460
461 EXCEPTION
462 WHEN e_upd_obj_exec_step_stat_error THEN
463 FEM_ENGINES_PKG.Tech_Message (
464 p_severity => g_log_level_5
465 ,p_module => G_BLOCK||'.'||l_api_name
466 ,p_msg_text => 'Update Obj Exec Step API Exception');
467
468 FEM_ENGINES_PKG.User_Message (
469 p_app_name => G_PFT
470 ,p_msg_name => G_PL_UPD_EXEC_STEP_ERR
471 ,p_token1 => 'OBJECT_ID'
472 ,p_value1 => p_rule_obj_id);
473
474 RAISE e_process_single_rule_error;
475
476 WHEN OTHERS THEN
477 FEM_ENGINES_PKG.User_Message (
478 p_app_name => G_PFT
479 ,p_msg_name => G_PL_UPD_EXEC_STEP_ERR
480 ,p_token1 => 'OBJECT_ID'
481 ,p_value1 => p_rule_obj_id);
482
483 RAISE e_process_single_rule_error;
484
485 END Update_Obj_Exec_Step_Status;
486
487 /*============================================================================+
488 | PROCEDURE
489 | Process_Obj_Exec_Step
490 | DESCRIPTION
491 | Processes the execution of the Object.
492 |
493 | SCOPE - PRIVATE
494 |
495 +============================================================================*/
496 PROCEDURE Process_Obj_Exec_Step( p_request_id IN NUMBER
497 ,p_user_id IN NUMBER
498 ,p_login_id IN NUMBER
499 ,p_rule_obj_id IN NUMBER
500 ,p_exe_step IN VARCHAR2
501 ,p_exe_status_code IN VARCHAR2
502 ,p_tbl_name IN VARCHAR2
503 ,p_num_rows IN NUMBER)
504 IS
505 l_api_name VARCHAR2(30);
506 l_nbr_output_rows NUMBER;
507 l_nbr_input_rows NUMBER;
508
509 BEGIN
510 l_api_name := 'Process_Obj_Exec_Step';
511
512 FEM_ENGINES_PKG.Tech_Message( p_severity => g_log_level_2
513 ,p_module => G_BLOCK||'.'||l_api_name
514 ,p_msg_text => 'BEGIN');
515
516 --------------------------------------------------------------------------
517 --update the status of the step
518 --------------------------------------------------------------------------
519 FEM_ENGINES_PKG.Tech_Message(
520 p_severity => g_log_level_3
521 ,p_module => G_BLOCK||'.'||l_api_name
522 ,p_msg_text => 'Update the status of the step with execution status :'
523 ||p_exe_status_code);
524
525 Update_Obj_Exec_Step_Status( p_request_id => p_request_id
526 ,p_user_id => p_user_id
527 ,p_login_id => p_login_id
528 ,p_rule_obj_id => p_rule_obj_id
529 ,p_exe_step => 'CUST_PPTILE'
530 ,p_exe_status_code => p_exe_status_code );
531
532 IF (p_exe_status_code = g_exec_status_success) THEN
533
534 --update the number of output rows processed succesfully
535 --in the registered table
536 FEM_ENGINES_PKG.Tech_Message(
537 p_severity => g_log_level_3
538 ,p_module => G_BLOCK||'.'||l_api_name
539 ,p_msg_text => 'Rows processed for registered output table :'
540 ||p_tbl_name);
541
542 -- update the number of rows processed in the registered table
543 Update_Nbr_Of_Output_Rows( p_request_id => p_request_id
544 ,p_user_id => p_user_id
545 ,p_login_id => p_login_id
546 ,p_rule_obj_id => p_rule_obj_id
547 ,p_num_output_rows => p_num_rows
548 ,p_tbl_name => p_tbl_name
549 ,p_stmt_type => g_update );
550
551 -----------------------------------------------------------------------
552 -- Call FEM_PL_PKG.update_num_of_input_rows();
553 -----------------------------------------------------------------------
554
555 FEM_ENGINES_PKG.TECH_MESSAGE(
556 p_severity => g_log_level_1,
560
557 p_module => G_BLOCK||'.'||l_api_name,
558 p_msg_text => 'No.of Rows processed from input table :'
559 || p_num_rows);
561 -- update the number of rows processed in the registered table
562 Update_Nbr_Of_Input_Rows( p_request_id => p_request_id
563 ,p_user_id => p_user_id
564 ,p_last_update_login => p_login_id
565 ,p_rule_obj_id => p_rule_obj_id
566 ,p_num_of_input_rows => p_num_rows);
567
568 FEM_ENGINES_PKG.User_Message(
569 p_app_name => G_PFT
570 ,p_msg_name => 'PFT_PPROF_PPTILE_ROW_SUMMARY'
571 ,p_token1 => 'ROWSP'
572 ,p_value1 => NVL(p_num_rows,0)
573 ,p_token2 => 'ROWSL'
574 ,p_value2 => NVL(p_num_rows,0));
575
576 END IF;
577
578 FEM_ENGINES_PKG.Tech_Message( p_severity => g_log_level_2
579 ,p_module => G_BLOCK||'.'||l_api_name
580 ,p_msg_text => 'END');
581
582 EXCEPTION
583 WHEN OTHERS THEN
584 RAISE e_process_single_rule_error;
585
586 END;
587
588 /*============================================================================+
589 | FUNCTION
590 | Create Profit Percentile Update Statement
591 |
592 | DESCRIPTION
593 | Creates the Bulk SQL for Profit Percentile
594 | (To Update Fem_Customer_Profit Table).
595 |
596 | SCOPE - PRIVATE
597 |
598 +============================================================================*/
599
600 FUNCTION Create_Pptile_Update_Stmt ( p_rule_obj_id IN NUMBER
601 ,p_table_name IN VARCHAR2
602 ,p_cal_period_id IN NUMBER
603 ,p_effective_date IN VARCHAR2
604 ,p_dataset_code IN NUMBER
605 ,p_ledger_id IN NUMBER
606 ,p_source_system_code IN NUMBER
607 ,p_ds_where_clause IN LONG)
608 RETURN LONG IS
609
610 l_api_name CONSTANT VARCHAR2(30) := 'Create_Pptile_Update_Stmt';
611
612 l_update_head_stmt LONG;
613 l_select_stmt LONG;
614 l_from_stmt LONG;
615 l_where_stmt LONG;
616 l_request_id NUMBER;
617 l_msg_count NUMBER;
618 l_msg_data VARCHAR2(500);
619 l_return_status VARCHAR2(20);
620 l_effective_date DATE;
621 l_user_id NUMBER;
622 l_login_id NUMBER := FND_GLOBAL.Login_Id;
623 l_gvsc_id NUMBER;
624 l_err_code NUMBER := 0;
625 l_num_msg NUMBER := 0;
626 l_value_set_id NUMBER;
627
628 BEGIN
629 l_request_id := FND_GLOBAL.Conc_Request_Id;
630 l_user_id := FND_GLOBAL.User_Id;
631 l_effective_date := FND_DATE.Canonical_To_Date(p_effective_date);
632
633 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
634 ,p_module => G_BLOCK||'.'||l_api_name
635 ,p_msg_text => 'BEGIN');
636
637 FEM_ENGINES_PKG.Tech_Message (
638 p_severity => g_log_level_2
639 ,p_module => G_BLOCK||'.'||l_api_name
640 ,p_msg_text => 'Getting Global VS Combo ID');
641
642 l_gvsc_id := FEM_DIMENSION_UTIL_PKG.Global_VS_Combo_ID (
643 p_ledger_id => p_ledger_id
644 ,x_err_code => l_err_code
645 ,x_num_msg => l_num_msg);
646
647 IF(l_err_code <> 0)THEN
648 FEM_ENGINES_PKG.Tech_Message (
649 p_severity => g_log_level_2
650 ,p_module => G_BLOCK||'.'||l_api_name
651 ,p_msg_text => 'No GVSC Id for the Given Ledger' || p_ledger_id);
652
653 FEM_ENGINES_PKG.User_Message (
654 p_app_name => G_PFT
655 ,p_msg_name => G_ENG_INVALID_LEDGER_ERR
656 ,p_token1 => 'LEDGER_ID'
657 ,p_value1 => p_ledger_id);
658
659 RAISE e_process_single_rule_error;
660 END IF;
661
662 FEM_ENGINES_PKG.Tech_Message (
663 p_severity => g_log_level_2
664 ,p_module => G_BLOCK||'.'||l_api_name
665 ,p_msg_text => 'Getting Customer Value Set Id');
666
667 BEGIN
668 SELECT gvsc.value_set_id
669 INTO l_value_set_id
670 FROM fem_global_vs_combo_defs gvsc,fem_dimensions_b dim
671 WHERE gvsc.dimension_id = dim.dimension_id
672 AND dim.dimension_varchar_label = 'CUSTOMER'
673 AND gvsc.global_vs_combo_id = l_gvsc_id;
674 EXCEPTION
675 WHEN no_data_found THEN
676 FEM_ENGINES_PKG.Tech_Message (
677 p_severity => g_log_level_2
678 ,p_module => G_BLOCK||'.'||l_api_name
679 ,p_msg_text => 'No Value Set Id for the Given GVSC '|| l_gvsc_id);
680
681 FEM_ENGINES_PKG.User_Message (
682 p_app_name => G_PFT
683 ,p_msg_name => G_ENG_INVALID_GVSC_ERR
684 ,p_token1 => 'GVSC_ID'
685 ,p_value1 => l_gvsc_id);
686
687 RAISE e_process_single_rule_error;
688
689 WHEN OTHERS THEN
690 RAISE;
691 END;
695 ' FCP.PROFIT_PERCENTILE, '||
692
693 l_update_head_stmt := ' UPDATE FEM_CUSTOMER_PROFIT FCP' ||
694 ' SET (' ||
696 ' FCP.PROFIT_DECILE, '||
697 ' FCP.LAST_UPDATED_BY_OBJECT_ID, ' ||
698 ' FCP.LAST_UPDATED_BY_REQUEST_ID ' ||
699 ' ) = ';
700
701 l_select_stmt := ' ( SELECT '||
702 ' PROFIT_PERCENTILE,' ||
703 ' PROFIT_DECILE, ' ||
704 p_rule_obj_id || ' , ' ||
705 l_request_id ;
706
707 l_from_stmt := ' FROM' ||
708 ' ( SELECT BUS_REL_ID, CUSTOMER_ID, ' ||
709 ' NTILE(100) OVER ' ||
710 ' (PARTITION BY dimension_group_id ' ||
711 ' ORDER BY PROFIT_CONTRIB ASC NULLS FIRST) ' ||
712 ' AS PROFIT_PERCENTILE, ' ||
713 ' NTILE(10) OVER ' ||
714 ' (PARTITION BY dimension_group_id ' ||
715 ' ORDER BY PROFIT_CONTRIB ASC NULLS FIRST) ' ||
716 ' AS PROFIT_DECILE ' ||
717 ' FROM (SELECT FCP.BUS_REL_ID,FCP.CUSTOMER_ID, ' ||
718 ' DIMENSION_GROUP_ID, ' ||
719 ' PROFIT_CONTRIB FROM FEM_CUSTOMERS_B FCB, ' ||
720 ' FEM_CUSTOMER_PROFIT fcp ' ||
721 ' WHERE FCP.LEDGER_ID = ' || p_ledger_id ||
722 ' AND FCP.SOURCE_SYSTEM_CODE = ' || p_source_system_code ||
723 ' AND DATA_AGGREGATION_TYPE_CODE = ' || '''CUSTOMER_AGGREGATION''' ||
724 ' AND FCP.CUSTOMER_ID = FCB.CUSTOMER_ID' ||
725 ' AND FCB.VALUE_SET_ID = ' || l_value_set_id;
726
727 l_where_stmt := ' )) dump WHERE' ||
728 ' dump.BUS_REL_ID = FCP.BUS_REL_ID'||
729 ' AND dump.CUSTOMER_ID = FCP.CUSTOMER_ID ) ' ||
730 ' WHERE FCP.LEDGER_ID =' || p_ledger_id ||
731 ' AND FCP.SOURCE_SYSTEM_CODE = ' || p_source_system_code ||
732 ' AND FCP.DATA_AGGREGATION_TYPE_CODE = ' || '''CUSTOMER_AGGREGATION''';
733
734 IF (p_ds_where_clause IS NOT NULL) THEN
735
736 l_from_stmt := l_from_stmt || ' AND ' || p_ds_where_clause;
737
738 l_where_stmt := l_where_stmt || ' AND ' || p_ds_where_clause;
739
740 END IF;
741
742 -- add mapped columns
743 RETURN l_update_head_stmt || ' ' || l_select_stmt || ' ' || l_from_stmt
744 || ' ' || l_where_stmt ;
745
746 FEM_ENGINES_PKG.Tech_Message ( p_severity => G_LOG_LEVEL_2
747 ,p_module => G_BLOCK||'.'||l_api_name
748 ,p_msg_text => 'END');
749
750 EXCEPTION
751 WHEN OTHERS THEN
752 RAISE;
753
754 END Create_Pptile_Update_Stmt;
755
756 /*============================================================================+
757 | PROCEDURE
758 | Get_Put_Messages
759 |
760 | DESCRIPTION
761 | To put the User messages,to be placed in common loader package.
762 |
763 | SCOPE - PRIVATE
764 |
765 +============================================================================*/
766
767 PROCEDURE Get_Put_Messages ( p_msg_count IN NUMBER
768 ,p_msg_data IN VARCHAR2)
769 IS
770
771 l_api_name CONSTANT VARCHAR2(30) := 'Get_Put_Messages';
772 l_msg_count NUMBER;
773 l_msg_data VARCHAR2(4000);
774 l_msg_out NUMBER;
775 l_message VARCHAR2(4000);
776
777 BEGIN
778
779 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
780 ,p_module => G_BLOCK||'.'||l_api_name
781 ,p_msg_text => 'msg_count='||p_msg_count);
782
783 l_msg_data := p_msg_data;
784
785 IF (p_msg_count = 1) THEN
786
787 FND_MESSAGE.Set_Encoded(l_msg_data);
788
789 l_message := FND_MESSAGE.Get;
790
791 FEM_ENGINES_PKG.User_Message ( p_msg_text => l_message );
792
793 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
794 ,p_module => G_BLOCK||'.'||l_api_name
795 ,p_msg_text => 'msg_data='||l_message);
796
797 ELSIF (p_msg_count > 1) THEN
798
799 FOR i IN 1..p_msg_count LOOP
800 FND_MSG_PUB.Get ( p_msg_index => i
801 ,p_encoded => FND_API.G_FALSE
802 ,p_data => l_message
803 ,p_msg_index_out => l_msg_out);
804
805 FEM_ENGINES_PKG.User_Message ( p_msg_text => l_message );
806
807 FEM_ENGINES_PKG.Tech_Message (
808 p_severity => g_log_level_2
809 ,p_module => G_BLOCK||'.'||l_api_name
810 ,p_msg_text => 'msg_data = '||l_message);
811
812 END LOOP;
813
814 END IF;
815
816 FND_MSG_PUB.Initialize;
817
818 END Get_Put_Messages;
819
820 /*============================================================================+
821 | PROCEDURE
822 | Update_Num_Of_Input_Rows
823 |
824 | DESCRIPTION
825 | This procedure logs the total number of rows used as input into
829 |
826 | an object execution
827 |
828 | SCOPE - PRIVATE
830 +============================================================================*/
831
832 PROCEDURE Update_Nbr_Of_Input_Rows( p_request_id IN NUMBER
833 ,p_user_id IN NUMBER
834 ,p_last_update_login IN NUMBER
835 ,p_rule_obj_id IN NUMBER
836 ,p_num_of_input_rows IN NUMBER)
837 IS
838
839 l_api_name CONSTANT VARCHAR2(30) := 'Update_Num_Of_Input_Rows';
840
841 l_return_status VARCHAR2(2);
842 l_msg_count NUMBER;
843 l_msg_data VARCHAR2(240);
844
845 e_upd_num_input_rows_error EXCEPTION;
846
847 BEGIN
848
849 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
850 ,p_module => G_BLOCK||'.'||l_api_name
851 ,p_msg_text => 'BEGIN');
852
853 -- Set the number of output rows for the output table.
854 FEM_PL_PKG.Update_Num_Of_Input_Rows(
855 p_api_version => 1.0
856 ,p_commit => FND_API.G_TRUE
857 ,p_request_id => p_request_id
858 ,p_object_id => p_rule_obj_id
859 ,p_num_of_input_rows => p_num_of_input_rows
860 ,p_user_id => p_user_id
861 ,p_last_update_login => p_last_update_login
862 ,x_msg_count => l_msg_count
863 ,x_msg_data => l_msg_data
864 ,x_return_status => l_return_status);
865
866 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
867 Get_Put_Messages( p_msg_count => l_msg_count
868 ,p_msg_data => l_msg_data);
869 RAISE e_upd_num_input_rows_error;
870 END IF;
871
872 FEM_ENGINES_PKG.Tech_Message( p_severity => g_log_level_2
873 ,p_module => G_BLOCK||'.'||l_api_name
874 ,p_msg_text => 'END');
875
876 EXCEPTION
877 WHEN e_upd_num_input_rows_error THEN
878 FEM_ENGINES_PKG.Tech_Message (
879 p_severity => g_log_level_5
880 ,p_module => G_BLOCK||'.'||l_api_name
881 ,p_msg_text => 'Update Input Rows Exception');
882
883 FEM_ENGINES_PKG.User_Message (
884 p_app_name => G_PFT
885 ,p_msg_name => G_PL_IP_UPD_ROWS_ERR);
886
887 RAISE e_process_single_rule_error;
888
889 WHEN OTHERS THEN
890 FEM_ENGINES_PKG.Tech_Message (
891 p_severity => g_log_level_5
892 ,p_module => G_BLOCK||'.'||l_api_name
893 ,p_msg_text => 'Update Input Rows Exception');
894
895 FEM_ENGINES_PKG.User_Message (
896 p_app_name => G_PFT
897 ,p_msg_name => G_PL_IP_UPD_ROWS_ERR);
898
899 RAISE e_process_single_rule_error;
900
901 END Update_Nbr_Of_Input_Rows;
902
903 END PFT_PROFCAL_CUST_PPTILE_PUB;