[Home] [Help]
PACKAGE BODY: APPS.PFT_PROFCAL_PROSP_IDENT_PUB
Source
1 PACKAGE BODY PFT_PROFCAL_PROSP_IDENT_PUB AS
2 /* $Header: PFTPIDNTB.pls 120.1 2006/05/25 10:36:55 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_PROSP_IDENT_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_customers_attr CONSTANT VARCHAR2(30) := 'FEM_CUSTOMERS_ATTR';
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
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_Prospect_Ident_Stmt (
98 p_rule_obj_id IN NUMBER
99 ,p_table_name IN VARCHAR2
100 ,p_cal_period_id IN NUMBER
101 ,p_effective_date IN VARCHAR2
102 ,p_dataset_code IN NUMBER
103 ,p_ledger_id IN NUMBER
104 ,p_source_system_code IN NUMBER
105 ,p_value_set_id IN NUMBER)
106
107 RETURN LONG;
108
109 PROCEDURE Update_Nbr_Of_Input_Rows (
110 p_request_id IN NUMBER
111 ,p_user_id IN NUMBER
112 ,p_last_update_login IN NUMBER
113 ,p_rule_obj_id IN NUMBER
114 ,p_num_of_input_rows IN NUMBER
115 );
116
117 /*======--=====================================================================+
118 | PROCEDURE
119 | PROCESS SINGLE RULE
120 |
121 | DESCRIPTION
122 | Main engine procedure for region counting step in profit calcution in PFT.
123 |
124 | SCOPE - PUBLIC
125 |
126 +============================================================================*/
127
128 PROCEDURE Process_Single_Rule ( p_rule_obj_id IN NUMBER
129 ,p_cal_period_id IN NUMBER
130 ,p_dataset_io_obj_def_id IN NUMBER
131 ,p_output_dataset_code IN NUMBER
132 ,p_effective_date IN VARCHAR2
133 ,p_ledger_id IN NUMBER
134 ,p_source_system_code IN NUMBER
135 ,p_exec_state IN VARCHAR2
136 ,x_return_status OUT NOCOPY VARCHAR2)
137
138 IS
139
140 l_api_name CONSTANT VARCHAR2(30) := 'Process_Single_Rule';
141
142 l_process_table VARCHAR2(30) := 'FEM_CUSTOMER_PROFIT';
143 l_table_alias VARCHAR2(5) := 'FCP';
144 l_gvsc_id NUMBER;
145 l_ds_where_clause LONG := NULL;
146 l_bulk_sql LONG;
147 l_effective_date DATE;
148 l_err_code NUMBER := 0;
149 l_num_msg NUMBER := 0;
150 l_err_msg VARCHAR2(255);
151 l_reuse_slices VARCHAR2(10);
152 l_msg_count NUMBER;
153 l_exception_code VARCHAR2(50);
154 l_msg_data VARCHAR2(200);
155 l_return_status VARCHAR2(50) := NULL;
156 l_value_set_id NUMBER;
157 l_request_id NUMBER := FND_GLOBAL.Conc_Request_Id;
158 l_user_id NUMBER := FND_GLOBAL.User_Id;
159 l_login_id NUMBER := FND_GLOBAL.Login_Id;
160
161 TYPE v_msg_list_type IS VARRAY(20) OF
162 fem_mp_process_ctl_t.message%TYPE;
163 v_msg_list v_msg_list_type;
164
165 e_process_single_rule_error EXCEPTION;
166 e_register_rule_error EXCEPTION;
167
168 BEGIN
169 -- Initialize the return status to SUCCESS
170 x_return_status := FND_API.G_RET_STS_SUCCESS;
171
172 l_effective_date := FND_DATE.Canonical_To_Date(p_effective_date);
173
174 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
175 ,p_module => G_BLOCK||'.'||l_api_name
176 ,p_msg_text => 'BEGIN');
177
178 -- CHECKPOINT RESTART
179 -- check executed state and jump to appropriate statement
180 -- depending on which step was last executed successfully
181 IF(p_exec_state = 'RESTART') THEN
182 l_reuse_slices := 'Y';
183 ELSE
184 l_reuse_slices := 'N';
185 END IF;
186
187 -- Get Value Set Id for the given LEDGER.
188 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
189 ,p_module => G_BLOCK||'.'||l_api_name
190 ,p_msg_text => 'Getting Global VS Combo ID');
191
192 l_gvsc_id := FEM_DIMENSION_UTIL_PKG.Global_VS_Combo_ID (
193 p_ledger_id => p_ledger_id
194 ,x_err_code => l_err_code
195 ,x_num_msg => l_num_msg);
196
197 IF(l_err_code <> 0)THEN
198 FEM_ENGINES_PKG.Tech_Message (
199 p_severity => g_log_level_2
200 ,p_module => G_BLOCK||'.'||l_api_name
201 ,p_msg_text => 'No GVSC Id for the Given Ledger' || p_ledger_id);
202
203 FEM_ENGINES_PKG.User_Message (
204 p_app_name => G_PFT
205 ,p_msg_name => G_ENG_INVALID_LEDGER_ERR
206 ,p_token1 => 'LEDGER_ID'
207 ,p_value1 => p_ledger_id);
208
209 RAISE e_process_single_rule_error;
210 END IF;
211
212 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
213 ,p_module => G_BLOCK||'.'||l_api_name
214 ,p_msg_text => 'Getting Customer Value Set Id');
215 BEGIN
216 SELECT gvsc.value_set_id
217 INTO l_value_set_id
218 FROM fem_global_vs_combo_defs gvsc,fem_dimensions_b dim
219 WHERE gvsc.dimension_id = dim.dimension_id
220 AND dim.dimension_varchar_label = 'CUSTOMER'
221 AND gvsc.global_vs_combo_id = l_gvsc_id;
222 EXCEPTION
223 WHEN no_data_found THEN
224 FEM_ENGINES_PKG.Tech_Message (
225 p_severity => g_log_level_2
226 ,p_module => G_BLOCK||'.'||l_api_name
227 ,p_msg_text => 'No Value Set Id for the Given GVSC ' ||l_gvsc_id);
228
229 FEM_ENGINES_PKG.User_Message (
230 p_app_name => G_PFT
231 ,p_msg_name => G_ENG_INVALID_GVSC_ERR
232 ,p_token1 => 'GVSC_ID'
233 ,p_value1 => l_gvsc_id);
234
235 RAISE e_process_single_rule_error;
236
237 WHEN OTHERS THEN
238 RAISE;
239 END;
240
241 -- To create the INSERT statement for the Prospect Identification Step.
242 l_bulk_sql := Create_Prospect_Ident_Stmt(
243 p_rule_obj_id => p_rule_obj_id
244 ,p_table_name => l_process_table
245 ,p_cal_period_id => p_cal_period_id
246 ,p_effective_date => p_effective_date
247 ,p_dataset_code => p_output_dataset_code
248 ,p_ledger_id => p_ledger_id
249 ,p_source_system_code => p_source_system_code
250 ,P_value_set_id => l_value_set_id);
251
252 FEM_ENGINES_PKG.TECH_MESSAGE(
253 p_severity => g_log_level_3
254 ,p_module => G_BLOCK||'.'||l_api_name
255 ,p_msg_text => 'Registering step: PROSP_IDENT');
256
257 FEM_ENGINES_PKG.TECH_MESSAGE(
258 p_severity => g_log_level_3
259 ,p_module => G_BLOCK||'.'||l_api_name
260 ,p_msg_text => 'Submitting to MP Master.p_rule_id: '
261 ||p_rule_obj_id);
262
263 FEM_ENGINES_PKG.TECH_MESSAGE(
264 p_severity => g_log_level_3
265 ,p_module => G_BLOCK||'.'||l_api_name
266 ,p_msg_text => 'Submitting Prospect Ident SQL to MP Master.p_eng_sql: '
267 ||l_bulk_sql);
268
269 FEM_MULTI_PROC_PKG.Master(
270 p_rule_id => p_rule_obj_id
271 ,p_eng_step => 'PROSP_IDENT'
272 ,p_eng_sql => l_bulk_sql
273 ,p_data_table => l_process_table
274 ,p_table_alias => l_table_alias
275 ,p_run_name => NULL
276 ,p_eng_prg => NULL
277 ,p_condition => NULL
278 ,p_failed_req_id => NULL
279 ,p_reuse_slices => l_reuse_slices
280 ,x_prg_stat => l_err_msg
281 ,x_Exception_code => l_exception_code);
282
283 IF (l_err_msg <> G_COMPLETE_NORMAL) THEN
284 v_msg_list := v_msg_list_type();
285
286 SELECT DISTINCT(message)
287 BULK COLLECT INTO v_msg_list
288 FROM fem_mp_process_ctl_t
289 WHERE req_id = l_request_id
290 AND status = 2;
291
292 FEM_ENGINES_PKG.Tech_Message(p_severity => g_log_level_1
293 ,p_module => G_BLOCK||'.'||l_api_name
294 ,p_msg_text => 'Total Errors : ' ||
295 TO_CHAR(v_msg_list.COUNT));
296
297 -- Log all of the messages
298 FOR i IN 1..v_msg_list.COUNT LOOP
299
300 FEM_ENGINES_PKG.Tech_Message( p_severity => g_log_level_5
301 ,p_module => G_BLOCK||'.'||l_api_name
302 ,p_msg_text => v_msg_list(i));
303
304 FND_FILE.Put_Line(FND_FILE.log, v_msg_list(i));
305 END LOOP;
306
307 FEM_ENGINES_PKG.User_Message (
308 p_app_name => G_PFT
309 ,p_msg_name => G_ENG_MULTI_PROC_ERR);
310
311 Process_Obj_Exec_Step(
312 p_request_id => l_request_id
313 ,p_user_id => l_user_id
314 ,p_login_id => l_login_id
315 ,p_rule_obj_id => p_rule_obj_id
316 ,p_exe_step => 'PROSP_IDENT'
317 ,p_exe_status_code => g_exec_status_error_rerun
318 ,p_tbl_name => 'FEM_REGION_INFO');
319
320 RAISE e_process_single_rule_error;
321
322 ELSIF(l_err_msg = G_COMPLETE_NORMAL) THEN
323
324 Process_Obj_Exec_Step( p_request_id => l_request_id
325 ,p_user_id => l_user_id
326 ,p_login_id => l_login_id
327 ,p_rule_obj_id => p_rule_obj_id
328 ,p_exe_step => 'PROSP_IDENT'
329 ,p_exe_status_code => g_exec_status_success
330 ,p_tbl_name => 'FEM_REGION_INFO');
331
332 -- commit the work
333 COMMIT;
334
335 -- Purge Data Slices
336 FEM_MULTI_PROC_PKG.Delete_Data_Slices (
337 p_req_id => l_request_id);
338
339 END IF;
340
341 -- commit the work
342 COMMIT;
343
344 FEM_ENGINES_PKG.Tech_Message ( p_severity => G_LOG_LEVEL_2
345 ,p_module => G_BLOCK||'.'||l_api_name
346 ,p_msg_text => 'END');
347
348 EXCEPTION
349 WHEN e_process_single_rule_error THEN
350
351 FEM_ENGINES_PKG.Tech_Message (
352 p_severity => g_log_level_5
353 ,p_module => G_BLOCK||'.'||l_api_name
354 ,p_msg_text => 'Prospect Identification Error:
355 Process Single Rule Exception');
356
357 FEM_ENGINES_PKG.User_Message (p_app_name => G_PFT
358 ,p_msg_name => G_ENG_SINGLE_RULE_ERR);
359
360 x_return_status := FND_API.G_RET_STS_ERROR;
361
362 WHEN OTHERS THEN
363
364 FEM_ENGINES_PKG.Tech_Message (
365 p_severity => g_log_level_5
366 ,p_module => G_BLOCK||'.'||l_api_name
367 ,p_msg_text => 'Prospect Identification Error:
368 Process Single Rule Exception');
369
370 FEM_ENGINES_PKG.User_Message (p_app_name => G_PFT
371 ,p_msg_name => G_ENG_SINGLE_RULE_ERR);
372
373 x_return_status := FND_API.G_RET_STS_ERROR;
374
375 END Process_Single_Rule;
376
377 /*============================================================================+
378 | PROCEDURE
379 | Update_Num_Of_Output_Rows
380 |
381 | DESCRIPTION
382 | Updates the rows successfully processed by calling
383 | fem_pl_pkg.Update_Num_Of_Output_Rows in fem_pl_tables.
384 |
385 | SCOPE - PRIVATE
386 |
387 +============================================================================*/
388 PROCEDURE Update_Nbr_Of_Output_Rows( p_request_id IN NUMBER
389 ,p_user_id IN NUMBER
390 ,p_login_id IN NUMBER
391 ,p_rule_obj_id IN NUMBER
392 ,p_num_output_rows IN NUMBER
393 ,p_tbl_name IN VARCHAR2
394 ,p_stmt_type IN VARCHAR2)
395 IS
396
397 l_api_name CONSTANT VARCHAR2(30) := 'Update_Num_Of_Output_Rows';
398
399 l_return_status VARCHAR2(2);
400 l_msg_count NUMBER;
401 l_msg_data VARCHAR2(240);
402
403 e_upd_num_output_rows_error EXCEPTION;
404
405 BEGIN
406
407 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
408 ,p_module => G_BLOCK||'.'||l_api_name
409 ,p_msg_text => 'BEGIN');
410
411 -- Set the number of output rows for the output table.
412 FEM_PL_PKG.Update_Num_Of_Output_Rows(
413 p_api_version => 1.0
414 ,p_commit => FND_API.G_TRUE
415 ,p_request_id => p_request_id
416 ,p_object_id => p_rule_obj_id
417 ,p_table_name => p_tbl_name
418 ,p_statement_type => p_stmt_type
419 ,p_num_of_output_rows => p_num_output_rows
420 ,p_user_id => p_user_id
421 ,p_last_update_login => p_login_id
422 ,x_msg_count => l_msg_count
423 ,x_msg_data => l_msg_data
424 ,x_return_status => l_return_status);
425
426 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
427
428 Get_Put_Messages( p_msg_count => l_msg_count
429 ,p_msg_data => l_msg_data);
430 RAISE e_upd_num_output_rows_error;
431 END IF;
432
433 FEM_ENGINES_PKG.Tech_Message( p_severity => g_log_level_2
434 ,p_module => G_BLOCK||'.'||l_api_name
435 ,p_msg_text => 'END');
436
437 EXCEPTION
438 WHEN e_upd_num_output_rows_error THEN
439 FEM_ENGINES_PKG.Tech_Message (
440 p_severity => g_log_level_5
441 ,p_module => G_BLOCK||'.'||l_api_name
442 ,p_msg_text => 'Update Rows Exception');
443
444 FEM_ENGINES_PKG.User_Message (
445 p_app_name => G_PFT
446 ,p_msg_name => G_PL_OP_UPD_ROWS_ERR);
447
448 RAISE e_process_single_rule_error;
449
450 WHEN OTHERS THEN
451 FEM_ENGINES_PKG.User_Message (
452 p_app_name => G_PFT
453 ,p_msg_name => G_PL_OP_UPD_ROWS_ERR);
454
455 RAISE e_process_single_rule_error;
456
457 END Update_Nbr_Of_Output_Rows;
458
459 /*============================================================================+
460 | PROCEDURE
461 | Update_Obj_Exec_Step_Status
462 |
463 | DESCRIPTION
464 | Updates the status of the executuon of the object by calling
465 | fem_pl_pkg.Update_obj_exec_step_status in fem_pl_obj_steps.
466 |
467 | SCOPE - PRIVATE
468 |
469 +============================================================================*/
470 PROCEDURE Update_Obj_Exec_Step_Status( p_request_id IN NUMBER
471 ,p_user_id IN NUMBER
472 ,p_login_id IN NUMBER
473 ,p_rule_obj_id IN NUMBER
474 ,p_exe_step IN VARCHAR2
475 ,p_exe_status_code IN VARCHAR2)
476 IS
477
478 l_api_name CONSTANT VARCHAR2(30) := 'Update_Obj_Exe_Step_Status';
479
480 l_return_status VARCHAR2(1);
481 l_msg_count NUMBER;
482 l_msg_data VARCHAR2(240);
483
484 e_upd_obj_exec_step_stat_error EXCEPTION;
485
486 BEGIN
487
488 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
489 ,p_module => G_BLOCK||'.'||l_api_name
490 ,p_msg_text => 'BEGIN');
491
492 --Call the FEM_PL_PKG.Update_obj_exec_step_status API procedure
493 --to update step staus in fem_pl_obj_steps.
494 FEM_PL_PKG.Update_Obj_Exec_Step_Status(
495 p_api_version => 1.0
496 ,p_commit => FND_API.G_TRUE
497 ,p_request_id => p_request_id
498 ,p_object_id => p_rule_obj_id
499 ,p_exec_step => p_exe_step
500 ,p_exec_status_code => p_exe_status_code
501 ,p_user_id => p_user_id
502 ,p_last_update_login => p_login_id
503 ,x_msg_count => l_msg_count
504 ,x_msg_data => l_msg_data
505 ,x_return_status => l_return_status);
506
507 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
508 Get_Put_Messages ( p_msg_count => l_msg_count
509 ,p_msg_data => l_msg_data);
510 RAISE e_upd_obj_exec_step_stat_error;
511
512 END IF;
513
514 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
515 ,p_module => G_BLOCK||'.'||l_api_name
516 ,p_msg_text => 'END');
517
518 EXCEPTION
519 WHEN e_upd_obj_exec_step_stat_error THEN
520 FEM_ENGINES_PKG.Tech_Message (
521 p_severity => g_log_level_5
522 ,p_module => G_BLOCK||'.'||l_api_name
523 ,p_msg_text => 'Update Obj Exec Step API Exception');
524
525 FEM_ENGINES_PKG.User_Message (
526 p_app_name => G_PFT
527 ,p_msg_name => G_PL_UPD_EXEC_STEP_ERR
528 ,p_token1 => 'OBJECT_ID'
529 ,p_value1 => p_rule_obj_id);
530
531 RAISE e_process_single_rule_error;
532
533 WHEN OTHERS THEN
534 FEM_ENGINES_PKG.User_Message (
535 p_app_name => G_PFT
536 ,p_msg_name => G_PL_UPD_EXEC_STEP_ERR
537 ,p_token1 => 'OBJECT_ID'
538 ,p_value1 => p_rule_obj_id);
539
540 RAISE e_process_single_rule_error;
541
542 END Update_Obj_Exec_Step_Status;
543
544 /*============================================================================+
545 | PROCEDURE
546 | Get_Nbr_RowsTable_For_Request
547 |
548 | DESCRIPTION
549 | To find the number rows processed by the request.
550 |
551 | SCOPE - PRIVATE
552 |
553 +============================================================================*/
554 PROCEDURE Get_Nbr_RowsTable_Request( x_rows_processed OUT NOCOPY NUMBER,
555 x_rows_loaded OUT NOCOPY NUMBER,
556 x_rows_rejected OUT NOCOPY NUMBER,
557 p_request_id IN NUMBER)
558 IS
559
560 l_api_name CONSTANT VARCHAR2(30) := 'Get_Nbr_RowsTable_Request';
561
562 BEGIN
563
564 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
565 ,p_module => G_BLOCK||'.'||l_api_name
566 ,p_msg_text => 'BEGIN');
567
568 --Query the fem_mp_process_ctl_t table to get the number of rows
569 --processed per request
570 SELECT NVL(SUM(rows_processed),0),
571 NVL(SUM(rows_rejected),0),
572 NVL(SUM(rows_loaded),0)
573 INTO x_rows_processed,
574 x_rows_rejected,
575 x_rows_loaded
576 FROM fem_mp_process_ctl_t t
577 WHERE t.req_id = p_request_id
578 AND t.process_num > 0;
579
580 IF (x_rows_processed = 0) THEN
581 FEM_ENGINES_PKG.Tech_Message (
582 p_severity => g_log_level_5
583 ,p_module => G_BLOCK||'.'||l_api_name
584 ,p_msg_text => 'No Rows returned by the SQL');
585
586 FEM_ENGINES_PKG.User_Message (
587 p_app_name => G_PFT
588 ,p_msg_name => G_ENG_NO_OP_ROWS_ERR);
589
590 RAISE e_process_single_rule_error;
591 END IF;
592
593 FEM_ENGINES_PKG.Tech_Message( p_severity => g_log_level_2
594 ,p_module => G_BLOCK||'.'||l_api_name
595 ,p_msg_text => 'END');
596
597 EXCEPTION
598 WHEN OTHERS THEN
599
600 RAISE;
601 END Get_Nbr_RowsTable_Request;
602
603 /*============================================================================+
604 | PROCEDURE
605 | Process_Obj_Exec_Step
606 | DESCRIPTION
607 | Processes the execution of the Object.
608 |
609 | SCOPE - PRIVATE
610 |
611 +============================================================================*/
612 PROCEDURE Process_Obj_Exec_Step( p_request_id IN NUMBER
613 ,p_user_id IN NUMBER
614 ,p_login_id IN NUMBER
615 ,p_rule_obj_id IN NUMBER
616 ,p_exe_step IN VARCHAR2
617 ,p_exe_status_code IN VARCHAR2
618 ,p_tbl_name IN VARCHAR2)
619 IS
620
621 l_api_name VARCHAR2(30);
622 l_nbr_output_rows NUMBER;
623 l_nbr_input_rows NUMBER;
624 l_nbr_rejected_rows NUMBER;
625 l_nbr_loaded_rows NUMBER;
626
627 BEGIN
628 l_api_name := 'Process_Obj_Exec_Step';
629 l_nbr_output_rows := NULL;
630 l_nbr_input_rows := NULL;
631
632 FEM_ENGINES_PKG.TECH_MESSAGE( p_severity => g_log_level_2
633 ,p_module => G_BLOCK||'.'||l_api_name
634 ,p_msg_text => 'BEGIN');
635
636 FEM_ENGINES_PKG.TECH_MESSAGE(
637 p_severity => g_log_level_3
638 ,p_module => G_BLOCK||'.'||l_api_name
639 ,p_msg_text => 'Update the status of the step with execution status :'
640 ||p_exe_status_code);
641
642 --update the status of the step
643 Update_Obj_Exec_Step_Status( p_request_id => p_request_id
644 ,p_user_id => p_user_id
645 ,p_login_id => p_login_id
646 ,p_rule_obj_id => p_rule_obj_id
647 ,p_exe_step => 'PROSP_IDENT'
648 ,p_exe_status_code => p_exe_status_code );
649
650 IF (p_exe_status_code = g_exec_status_success) THEN
651 -- query table fem_mp_process_ctl_t to get the number of rows processed
652 Get_Nbr_RowsTable_Request(x_rows_processed => l_nbr_output_rows,
653 x_rows_loaded => l_nbr_loaded_rows,
654 x_rows_rejected => l_nbr_rejected_rows,
655 p_request_id => p_request_id);
656
657 --update the number of output rows processed succesfully
658 --in the registered table
659 FEM_ENGINES_PKG.TECH_MESSAGE(
660 p_severity => g_log_level_3
661 ,p_module => G_BLOCK||'.'||l_api_name
662 ,p_msg_text => 'Rows processed for registered output table :'
663 ||p_tbl_name);
664
665 -- update the number of rows processed in the registered table
666 Update_Nbr_Of_Output_Rows(p_request_id => p_request_id
667 ,p_user_id => p_user_id
668 ,p_login_id => p_login_id
669 ,p_rule_obj_id => p_rule_obj_id
670 ,p_num_output_rows => l_nbr_loaded_rows
671 ,p_tbl_name => p_tbl_name
672 ,p_stmt_type => g_insert );
673
674 -----------------------------------------------------------------------
675 -- Call FEM_PL_PKG.update_num_of_input_rows();
676 -----------------------------------------------------------------------
677 FEM_ENGINES_PKG.TECH_MESSAGE(
678 p_severity => g_log_level_1,
679 p_module => G_BLOCK||'.'||l_api_name,
680 p_msg_text => 'No:of Rows processed from input table'
681 ||l_nbr_input_rows);
682
683 -- update the number of rows processed in the registered table
684 Update_Nbr_Of_Input_Rows( p_request_id => p_request_id
685 ,p_user_id => p_user_id
686 ,p_last_update_login => p_login_id
687 ,p_rule_obj_id => p_rule_obj_id
688 ,p_num_of_input_rows => l_nbr_input_rows);
689
690 FEM_ENGINES_PKG.User_Message(p_app_name => G_PFT,
691 p_msg_name => 'PFT_PPROF_PIDNT_ROW_SUMMARY',
692 p_token1 => 'ROWSP',
693 p_value1 => l_nbr_output_rows,
694 p_token2 => 'ROWSL',
695 p_value2 => l_nbr_loaded_rows);
696
697 END IF;
698
699 FEM_ENGINES_PKG.TECH_MESSAGE( p_severity => g_log_level_2
700 ,p_module => G_BLOCK||'.'||l_api_name
701 ,p_msg_text => 'END');
702
703 EXCEPTION
704 WHEN OTHERS THEN
705 RAISE e_process_single_rule_error;
706
707 END;
708
709 /*=============================================================================+
710 | FUNCTION
711 | Create Prospect Identification Statement
712 |
713 | DESCRIPTION
714 | Creates the Bulk SQL for Prospect Identification
715 |
716 | SCOPE - PRIVATE
717 |
718 +============================================================================*/
719
720 FUNCTION Create_Prospect_Ident_Stmt ( p_rule_obj_id IN NUMBER
721 ,p_table_name IN VARCHAR2
722 ,p_cal_period_id IN NUMBER
723 ,p_effective_date IN VARCHAR2
724 ,p_dataset_code IN NUMBER
725 ,p_ledger_id IN NUMBER
726 ,p_source_system_code IN NUMBER
727 ,p_value_set_id IN NUMBER)
728 RETURN LONG IS
729
730 l_api_name CONSTANT VARCHAR2(30) := 'Create_Prospect_Ident_Stmt';
731
732 l_update_head_stmt LONG;
733 l_select_stmt LONG;
734 l_where_stmt LONG;
735 l_sub_query_stmt LONG;
736 l_request_id NUMBER;
737 l_msg_count NUMBER;
738 l_msg_data VARCHAR2(500);
739 l_return_status VARCHAR2(20);
740 l_effective_date DATE;
741 l_user_id NUMBER;
742 l_login_id NUMBER := FND_GLOBAL.login_id;
743
744 BEGIN
745 l_request_id := FND_GLOBAL.Conc_Request_Id;
746 l_user_id := FND_GLOBAL.User_Id;
747 l_effective_date := FND_DATE.Canonical_To_Date(p_effective_date);
748
749 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
750 ,p_module => G_BLOCK||'.'||l_api_name
751 ,p_msg_text => 'BEGIN');
752
753 l_update_head_stmt := ' UPDATE FEM_CUSTOMERS_ATTR attr' ||
754 ' SET ' ||
755 ' attr.VARCHAR_ASSIGN_VALUE '||
756 ' = ' ||
757 'fem_prospect_ident_s.NEXTVAL';
758
759 l_where_stmt := ' WHERE ' ||
760 ' CUSTOMER_ID IN ( ';
761
762 l_select_stmt := ' SELECT ' ||
763 ' CUSTOMER_ID ' ||
764 ' FROM FEM_CUSTOMERS_B FCB'||
765 ' WHERE FCB.CUSTOMER_ID NOT IN ( '||
766 ' SELECT DISTINCT CUSTOMER_ID '||
767 ' FROM FEM_CUSTOMER_PROFIT FCP'||
768 ' WHERE LEDGER_ID =' || p_ledger_id ||
769 ' AND SOURCE_SYSTEM_CODE ='|| p_source_system_code ||
770 ' AND cal_period_id = ' || p_cal_period_id ||
771 ' AND dataset_code = ' || p_dataset_code ||
772 ' AND DATA_AGGREGATION_TYPE_CODE =
773 ''CUSTOMER_AGGREGATION''';
774
775 l_sub_query_stmt:= ' ) ' ||
776 ' AND FCB.VALUE_SET_ID = '|| p_value_set_id || ' )'||
777 ' AND attr.ATTRIBUTE_ID = ( '||
778 ' SELECT att.ATTRIBUTE_ID '||
779 ' FROM FEM_CUSTOMERS_ATTR att, '||
780 ' FEM_DIM_ATTRIBUTES_B dim, '||
781 ' FEM_DIMENSIONS_B xdim '||
782 ' WHERE att.ATTRIBUTE_ID = dim.ATTRIBUTE_ID '||
783 ' AND dim.dimension_id = xdim.dimension_id '||
784 ' AND dim.ATTRIBUTE_VARCHAR_LABEL =
785 ''FEM_PROSPECT_IDENT''' ||
786 ' AND xdim.dimension_varchar_label = ''CUSTOMER'''||
787 ') ';
788
789 -- Creates the final where clause
790 l_select_stmt := l_select_stmt || ' AND {{data_slice}} ';
791
792 -- add mapped columns
793 RETURN l_update_head_stmt || ' ' || l_where_stmt || ' ' || l_select_stmt || ' ' ||
794 l_sub_query_stmt;
795
796 FEM_ENGINES_PKG.Tech_Message ( p_severity => G_LOG_LEVEL_2
797 ,p_module => G_BLOCK||'.'||l_api_name
798 ,p_msg_text => 'END');
799
800 EXCEPTION
801 WHEN OTHERS THEN
802 RAISE;
803
804 END Create_Prospect_Ident_Stmt;
805
806 /*============================================================================+
807 | PROCEDURE
808 | Get_Put_Messages
809 |
810 | DESCRIPTION
811 | To put the User messages,to be placed in common loader package.
812 |
813 | SCOPE - PRIVATE
814 |
815 +============================================================================*/
816
817 PROCEDURE Get_Put_Messages ( p_msg_count IN NUMBER
818 ,p_msg_data IN VARCHAR2)
819 IS
820
821 l_api_name CONSTANT VARCHAR2(30) := 'Get_Put_Messages';
822 l_msg_count NUMBER;
823 l_msg_data VARCHAR2(4000);
824 l_msg_out NUMBER;
825 l_message VARCHAR2(4000);
826
827 BEGIN
828
829 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
830 ,p_module => G_BLOCK||'.'||l_api_name
831 ,p_msg_text => 'msg_count='||p_msg_count);
832
833 l_msg_data := p_msg_data;
834
835 IF (p_msg_count = 1) THEN
836
837 FND_MESSAGE.Set_Encoded(l_msg_data);
838 l_message := FND_MESSAGE.Get;
839
840 FEM_ENGINES_PKG.User_Message ( p_msg_text => l_message );
841
842 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
843 ,p_module => G_BLOCK||'.'||l_api_name
844 ,p_msg_text => 'msg_data='||l_message);
845
846 ELSIF (p_msg_count > 1) THEN
847
848 FOR i IN 1..p_msg_count LOOP
849 FND_MSG_PUB.Get ( p_msg_index => i
850 ,p_encoded => FND_API.G_FALSE
851 ,p_data => l_message
852 ,p_msg_index_out => l_msg_out);
853
854 FEM_ENGINES_PKG.User_Message ( p_msg_text => l_message );
855
856 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
857 ,p_module => G_BLOCK||'.'||l_api_name
858 ,p_msg_text => 'msg_data = '||l_message);
859
860 END LOOP;
861
862 END IF;
863
864 FND_MSG_PUB.Initialize;
865
866 END Get_Put_Messages;
867
868 /*============================================================================+
869 | PROCEDURE
870 | Update_Num_Of_Input_Rows
871 |
872 | DESCRIPTION
873 | This procedure logs the total number of rows used as input into
874 | an object execution
875 |
876 | SCOPE - PRIVATE
877 |
878 +============================================================================*/
879
880 PROCEDURE Update_Nbr_Of_Input_Rows( p_request_id IN NUMBER
881 ,p_user_id IN NUMBER
882 ,p_last_update_login IN NUMBER
883 ,p_rule_obj_id IN NUMBER
884 ,p_num_of_input_rows IN NUMBER)
885 IS
886
887 l_api_name CONSTANT VARCHAR2(30) := 'Update_Num_Of_Input_Rows';
888
889 l_return_status VARCHAR2(2);
890 l_msg_count NUMBER;
891 l_msg_data VARCHAR2(240);
892
893 e_upd_num_input_rows_error EXCEPTION;
894
895 BEGIN
896
897 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
898 ,p_module => G_BLOCK||'.'||l_api_name
899 ,p_msg_text => 'BEGIN');
900
901 -- Set the number of output rows for the output table.
902 FEM_PL_PKG.Update_Num_Of_Input_Rows(
903 p_api_version => 1.0
904 ,p_commit => FND_API.G_TRUE
905 ,p_request_id => p_request_id
906 ,p_object_id => p_rule_obj_id
907 ,p_num_of_input_rows => p_num_of_input_rows
908 ,p_user_id => p_user_id
909 ,p_last_update_login => p_last_update_login
910 ,x_msg_count => l_msg_count
911 ,x_msg_data => l_msg_data
912 ,x_return_status => l_return_status);
913
914 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
915
916 Get_Put_Messages( p_msg_count => l_msg_count
917 ,p_msg_data => l_msg_data);
918 RAISE e_upd_num_input_rows_error;
919 END IF;
920
921 FEM_ENGINES_PKG.Tech_Message( p_severity => g_log_level_2
922 ,p_module => G_BLOCK||'.'||l_api_name
923 ,p_msg_text => 'END');
924
925 EXCEPTION
926 WHEN e_upd_num_input_rows_error THEN
927 FEM_ENGINES_PKG.Tech_Message (
928 p_severity => g_log_level_5
929 ,p_module => G_BLOCK||'.'||l_api_name
930 ,p_msg_text => 'Update Input Rows Exception');
931
932 FEM_ENGINES_PKG.User_Message (
933 p_app_name => G_PFT
934 ,p_msg_name => G_PL_IP_UPD_ROWS_ERR);
935
936 RAISE e_process_single_rule_error;
937
938 WHEN OTHERS THEN
939 FEM_ENGINES_PKG.Tech_Message (
940 p_severity => g_log_level_5
941 ,p_module => G_BLOCK||'.'||l_api_name
942 ,p_msg_text => 'Update Input Rows Exception');
943
944 FEM_ENGINES_PKG.User_Message (
945 p_app_name => G_PFT
946 ,p_msg_name => G_PL_IP_UPD_ROWS_ERR);
947
948 RAISE e_process_single_rule_error;
949
950 END Update_Nbr_Of_Input_Rows;
951
952 END PFT_PROFCAL_PROSP_IDENT_PUB;