[Home] [Help]
PACKAGE BODY: APPS.PFT_PROFCAL_RGNCNT_PUB
Source
1 PACKAGE BODY PFT_PROFCAL_RGNCNT_PUB AS
2 /* $Header: PFTPRCNTB.pls 120.3 2006/08/25 07:30:25 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_RGNCNT_PUB';
10
11 -- Constants for p_exec_status_code
12 g_exec_status_error_rerun CONSTANT VARCHAR2(30) := 'ERROR_RERUN';
13 g_exec_status_success CONSTANT VARCHAR2(30) := 'SUCCESS';
14
15 --Constants for output table names being registered with fem_pl_pkg
16 -- API register_table method.
17 g_fem_customer_profit CONSTANT VARCHAR2(30) := 'FEM_CUSTOMER_PROFIT';
18 g_fem_region_info CONSTANT VARCHAR2(30) := 'FEM_REGION_INFO';
19
20 --constant for sql_stmt_type
21 g_insert CONSTANT VARCHAR2(30) := 'INSERT';
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 --------------------------------------------------------------------------------
33 -- Declare package variables --
34 --------------------------------------------------------------------------------
35 -- Exception variables
36 gv_prg_msg VARCHAR2(2000);
37 gv_callstack VARCHAR2(2000);
38 -- Bulk Fetch Limit
39 gv_fetch_limit NUMBER;
40
41 z_master_err_state NUMBER;
42
43 --------------------------------------------------------------------------------
44 -- Declare package exceptions --
45 --------------------------------------------------------------------------------
46 -- General profit Aggregation Engine Exception
47 e_process_single_rule_error EXCEPTION;
48 USER_EXCEPTION EXCEPTION;
49
50 --------------------------------------------------------------------------------
51 -- Declare private procedures and functions --
52 --------------------------------------------------------------------------------
53
54 PROCEDURE Update_Nbr_Of_Output_Rows (
55 p_request_id IN NUMBER
56 ,p_user_id IN NUMBER
57 ,p_login_id IN NUMBER
58 ,p_rule_obj_id IN NUMBER
59 ,p_num_output_rows IN NUMBER
60 ,p_tbl_name IN VARCHAR2
61 ,p_stmt_type IN VARCHAR2
62 );
63
64 PROCEDURE Update_Obj_Exec_Step_Status (
65 p_request_id IN NUMBER
66 ,p_user_id IN NUMBER
67 ,p_login_id IN NUMBER
68 ,p_rule_obj_id IN NUMBER
69 ,p_exe_step IN VARCHAR2
70 ,p_exe_status_code IN VARCHAR2
71 );
72
73 PROCEDURE Get_Nbr_RowsTable_Request (
74 x_rows_processed OUT NOCOPY NUMBER
75 ,x_rows_loaded OUT NOCOPY NUMBER
76 ,x_rows_rejected OUT NOCOPY NUMBER
77 ,p_request_id IN NUMBER
78 );
79
80 PROCEDURE Process_Obj_Exec_Step (
81 p_request_id IN NUMBER
82 ,p_user_id IN NUMBER
83 ,p_login_id IN NUMBER
84 ,p_rule_obj_id IN NUMBER
85 ,p_exe_step IN VARCHAR2
86 ,p_exe_status_code IN VARCHAR2
87 ,p_tbl_name IN VARCHAR2
88 ,p_num_rows IN NUMBER
89 );
90
91 PROCEDURE Get_Put_Messages (
92 p_msg_count IN NUMBER
93 ,p_msg_data IN VARCHAR2
94 );
95
96 FUNCTION Create_Region_Count_Stmt (
97 p_rule_obj_id IN NUMBER
98 ,p_table_name IN VARCHAR2
99 ,p_cal_period_id IN NUMBER
100 ,p_effective_date IN VARCHAR2
101 ,p_dataset_code IN NUMBER
102 ,p_ledger_id IN NUMBER
103 ,p_source_system_code IN NUMBER
104 ,p_total_customers IN NUMBER
105 ,p_customer_level IN NUMBER
106 ,p_value_set_id IN NUMBER
107 ,p_ds_where_clause IN LONG)
108
109 RETURN LONG;
110
111 PROCEDURE Update_Nbr_Of_Input_Rows (
112 p_request_id IN NUMBER
113 ,p_user_id IN NUMBER
114 ,p_last_update_login IN NUMBER
115 ,p_rule_obj_id IN NUMBER
116 ,p_num_of_input_rows IN NUMBER
117 );
118
119 FUNCTION Create_Rgn_Cnt_Wo_RCode_Stmt (
120 p_rule_obj_id IN NUMBER
121 ,p_table_name IN VARCHAR2
122 ,p_cal_period_id IN NUMBER
123 ,p_effective_date IN VARCHAR2
124 ,p_dataset_code IN NUMBER
125 ,p_ledger_id IN NUMBER
126 ,p_source_system_code IN NUMBER
127 ,p_total_customers IN NUMBER
128 ,p_cust_wo_rgn_code IN NUMBER
129 ,p_customer_level IN NUMBER
130 ,p_value_set_id IN NUMBER
131 ,p_ds_where_clause IN LONG)
132 RETURN LONG;
133
134 /*======--=====================================================================+
135 | PROCEDURE
136 | PROCESS SINGLE RULE
137 |
138 | DESCRIPTION
139 | Main engine procedure for region counting step in profit calculation in PFT.
140 |
141 | SCOPE - PUBLIC
142 |
143 +============================================================================*/
144
145 PROCEDURE Process_Single_Rule ( p_rule_obj_id IN NUMBER
146 ,p_cal_period_id IN NUMBER
147 ,p_dataset_io_obj_def_id IN NUMBER
148 ,p_output_dataset_code IN NUMBER
149 ,p_effective_date IN VARCHAR2
150 ,p_ledger_id IN NUMBER
151 ,p_source_system_code IN NUMBER
152 ,p_customer_level IN NUMBER
153 ,p_exec_state IN VARCHAR2
154 ,x_return_status OUT NOCOPY VARCHAR2)
155 IS
156
157 l_api_name CONSTANT VARCHAR2(30) := 'Process_Single_Rule';
158
159 l_process_table VARCHAR2(30) := 'FEM_CUSTOMERS_ATTR';
160 l_table_alias VARCHAR2(5) := 'FCA';
161 l_effective_date DATE;
162 l_dimension_grp_id NUMBER;
163 l_ds_where_clause LONG := NULL;
164 l_gvsc_id NUMBER;
165 l_value_set_id NUMBER;
166 l_dim_grp_id NUMBER;
167 l_attribute_id NUMBER;
168 l_num_rows_loaded NUMBER := 0;
169 l_err_code NUMBER := 0;
170 l_num_msg NUMBER := 0;
171 l_bulk_sql LONG;
172 l_bulk_sql1 LONG;
173 l_err_msg VARCHAR2(255);
174 l_reuse_slices VARCHAR2(10);
175 l_msg_count NUMBER;
176 l_exception_code VARCHAR2(50);
177 l_msg_data VARCHAR2(200);
178 l_return_status VARCHAR2(50) := NULL;
179 l_total_customers NUMBER;
180 l_region_code NUMBER;
181 l_object_def_id NUMBER;
182 l_cust_wo_rgn_code NUMBER;
183 l_request_id NUMBER := FND_GLOBAL.Conc_Request_Id;
184 l_user_id NUMBER := FND_GLOBAL.User_Id;
185 l_login_id NUMBER := FND_GLOBAL.Login_Id;
186
187 TYPE v_msg_list_type IS VARRAY(20) OF
188 fem_mp_process_ctl_t.message%TYPE;
189 v_msg_list v_msg_list_type;
190
191 e_process_single_rule_error EXCEPTION;
192 e_register_rule_error EXCEPTION;
193
194 BEGIN
195 -- Initialize the return status to SUCCESS
196 x_return_status := FND_API.G_RET_STS_SUCCESS;
197
198 l_effective_date := FND_DATE.Canonical_To_Date(p_effective_date);
199
200 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
201 ,p_module => G_BLOCK||'.'||l_api_name
202 ,p_msg_text => 'BEGIN');
203
204 FEM_ENGINES_PKG.Tech_Message (
205 p_severity => g_log_level_2
206 ,p_module => G_BLOCK||'.'||l_api_name
207 ,p_msg_text => 'Get The Level for which the
208 Region Counting has to be performed');
209
210 BEGIN
211 SELECT relative_dimension_group_seq
212 INTO l_dimension_grp_id
213 FROM fem_hier_dimension_grps
214 WHERE dimension_group_id = p_customer_level
215 AND ROWNUM = 1;
216
217 EXCEPTION
218 WHEN OTHERS THEN
219 RAISE;
220 END;
221
222 FEM_ENGINES_PKG.Tech_Message (
223 p_severity => g_log_level_2
224 ,p_module => G_BLOCK||'.'||l_api_name
225 ,p_msg_text => 'Getting Global VS Combo ID');
226
227 l_gvsc_id := FEM_DIMENSION_UTIL_PKG.Global_VS_Combo_ID (
228 p_ledger_id => p_ledger_id
229 ,x_err_code => l_err_code
230 ,x_num_msg => l_num_msg);
231
232 IF(l_err_code <> 0)THEN
233 FEM_ENGINES_PKG.Tech_Message (
234 p_severity => g_log_level_2
235 ,p_module => G_BLOCK||'.'||l_api_name
236 ,p_msg_text => 'No GVSC Id for the Given Ledger' || p_ledger_id);
237
238 FEM_ENGINES_PKG.User_Message (
239 p_app_name => G_PFT
240 ,p_msg_name => G_ENG_INVALID_LEDGER_ERR
241 ,p_token1 => 'LEDGER_ID'
242 ,p_value1 => p_ledger_id);
243
244 RAISE e_process_single_rule_error;
245 END IF;
246
247 FEM_ENGINES_PKG.Tech_Message (
248 p_severity => g_log_level_2
249 ,p_module => G_BLOCK||'.'||l_api_name
250 ,p_msg_text => 'Getting Customer Value Set Id');
251
252 BEGIN
253 SELECT gvsc.value_set_id
254 INTO l_value_set_id
255 FROM fem_global_vs_combo_defs gvsc,fem_dimensions_b dim
256 WHERE gvsc.dimension_id = dim.dimension_id
257 AND dim.dimension_varchar_label = 'CUSTOMER'
258 AND gvsc.global_vs_combo_id = l_gvsc_id;
259 EXCEPTION
260 WHEN no_data_found THEN
261 FEM_ENGINES_PKG.Tech_Message (
262 p_severity => g_log_level_2
263 ,p_module => G_BLOCK||'.'||l_api_name
264 ,p_msg_text => 'No Value Set Id for the Given GVSC '|| l_gvsc_id);
265
266 FEM_ENGINES_PKG.User_Message (
267 p_app_name => G_PFT
268 ,p_msg_name => G_ENG_INVALID_GVSC_ERR
269 ,p_token1 => 'GVSC_ID'
270 ,p_value1 => l_gvsc_id);
271
272 RAISE e_process_single_rule_error;
273
274 WHEN OTHERS THEN
275 RAISE;
276 END;
277
278 -- Get the total no of customers at the given level
279 SELECT COUNT(*)
280 INTO l_total_customers
281 FROM fem_customers_b
282 WHERE value_set_id = l_value_set_id
283 AND dimension_group_id = p_customer_level;
284
285 FEM_ENGINES_PKG.Tech_Message (
286 p_severity => g_log_level_3
287 ,p_module => G_BLOCK||'.'||l_api_name
288 ,p_msg_text => 'Generating the dataset where clause');
289
290 FEM_DS_WHERE_CLAUSE_GENERATOR.Fem_Gen_DS_WClause_Pvt(
291 p_api_version => G_CALLING_API_VERSION
292 ,p_init_msg_list => FND_API.G_TRUE
293 ,p_encoded => FND_API.G_TRUE
294 ,p_ds_io_def_id => p_dataset_io_obj_def_id
295 ,p_output_period_id => p_cal_period_id
296 ,p_table_alias => l_process_table
297 ,p_table_name => l_table_alias
298 ,p_ledger_id => p_ledger_id
299 ,p_where_clause => l_ds_where_clause
300 ,x_return_status => l_return_status
301 ,x_msg_count => l_msg_count
302 ,x_msg_data => l_msg_data);
303
304 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
305 Get_Put_Messages ( p_msg_count => l_msg_count
306 ,p_msg_data => l_msg_data);
307
308 FEM_ENGINES_PKG.User_Message (
309 p_app_name => G_PFT
310 ,p_msg_name => G_ENG_DS_WHERE_CLAUSE_ERR
311 ,p_token1 => 'OUTPUT_DS_CODE'
312 ,p_value1 => p_dataset_io_obj_def_id
313 ,p_token2 => 'CAL_PERIOD_ID'
314 ,p_value2 => p_cal_period_id);
315
316 IF (l_ds_where_clause IS NULL) THEN
317 FEM_ENGINES_PKG.User_Message (
318 p_app_name => G_PFT
319 ,p_msg_name => G_ENG_DS_WHERE_CLAUSE_ERR
320 ,p_token1 => 'OUTPUT_DS_CODE'
321 ,p_value1 => p_dataset_io_obj_def_id
322 ,p_token2 => 'CAL_PERIOD_ID'
323 ,p_value2 => p_cal_period_id);
324 END IF;
325 RAISE e_process_single_rule_error;
326
327 END IF;
328
329 -- CHECKPOINT RESTART
330 -- check executed state and jump to appropriate statement
331 -- depending on which step was last executed successfully
332 IF(p_exec_state = 'RESTART') THEN
333 l_reuse_slices := 'Y';
334 ELSE
335 l_reuse_slices := 'N';
336 END IF;
337
338 FEM_ENGINES_PKG.Tech_Message (
339 p_severity => g_log_level_3
340 ,p_module => G_BLOCK||'.'||l_api_name
341 ,p_msg_text => 'Getting the region code attribute id');
342
343 -- Get the attribute id for the region code attribute
344 SELECT dim_attr.attribute_id
345 INTO l_attribute_id
346 FROM fem_dim_attributes_b dim_attr,fem_dimensions_b xdim
347 WHERE dim_attr.dimension_id = xdim.dimension_id
348 AND dim_attr.attribute_varchar_label = 'REGION_CODE'
349 AND xdim.dimension_varchar_label = 'CUSTOMER';
350
351 FEM_ENGINES_PKG.Tech_Message (
352 p_severity => g_log_level_3
353 ,p_module => G_BLOCK||'.'||l_api_name
354 ,p_msg_text => 'Get the total no. of customers who doesnt have a region code in the given level');
355
356 -- Get all the customers in the level for whom region code is not assigned
357 SELECT COUNT(customer_id)
358 INTO l_cust_wo_rgn_code
359 FROM fem_customers_b
360 WHERE dimension_group_id = p_customer_level
361 AND value_set_id = l_value_set_id
362 AND customer_id NOT IN(SELECT customer_id
363 FROM fem_customers_attr
364 WHERE attribute_id = l_attribute_id);
365
366 FEM_ENGINES_PKG.Tech_Message (
367 p_severity => g_log_level_3
368 ,p_module => G_BLOCK||'.'||l_api_name
369 ,p_msg_text => 'Check whether region counting is already done for the given level and parameters');
370
371 -- Region Counting has to be done only once for a level
372 SELECT COUNT( dimension_group_id )
373 INTO l_dim_grp_id
374 FROM fem_region_info
375 WHERE dimension_group_id = p_customer_level
376 AND ledger_id = p_ledger_id
377 AND cal_period_id = p_cal_period_id
378 AND dataset_code = p_output_dataset_code;
379
380 IF l_dim_grp_id = 0 THEN
381 -- To create the INSERT statement for the customers with region code.
382 l_bulk_sql := Create_Region_Count_Stmt(
383 p_rule_obj_id => p_rule_obj_id
384 ,p_table_name => l_process_table
385 ,p_cal_period_id => p_cal_period_id
386 ,p_effective_date => p_effective_date
387 ,p_dataset_code => p_output_dataset_code
388 ,p_ledger_id => p_ledger_id
389 ,p_source_system_code => p_source_system_code
390 ,p_total_customers => l_total_customers
391 ,p_customer_level => p_customer_level
392 ,p_value_set_id => l_value_set_id
393 ,p_ds_where_clause => l_ds_where_clause);
394
395 IF l_cust_wo_rgn_code <> 0 THEN
396 -- To create the INSERT statement for the customers with out region code
397 l_bulk_sql1 := Create_Rgn_Cnt_Wo_RCode_Stmt(
398 p_rule_obj_id => p_rule_obj_id
399 ,p_table_name => l_process_table
400 ,p_cal_period_id => p_cal_period_id
401 ,p_effective_date => p_effective_date
402 ,p_dataset_code => p_output_dataset_code
403 ,p_ledger_id => p_ledger_id
404 ,p_source_system_code => p_source_system_code
405 ,p_total_customers => l_total_customers
406 ,p_cust_wo_rgn_code => l_cust_wo_rgn_code
407 ,p_customer_level => p_customer_level
408 ,p_value_set_id => l_value_set_id
409 ,p_ds_where_clause => l_ds_where_clause);
410 END IF;
411
412 -- Perform region counting for the customers for whom Region code is
413 -- assigned
414 FEM_ENGINES_PKG.Tech_Message (
415 p_severity => g_log_level_3
416 ,p_module => G_BLOCK||'.'||l_api_name
417 ,p_msg_text => 'Perform Region Counting for the region code assigned customers');
418
419 FEM_ENGINES_PKG.Tech_Message (
420 p_severity => g_log_level_3
421 ,p_module => G_BLOCK||'.'||l_api_name
422 ,p_msg_text => 'SQL:' ||l_bulk_sql );
423
424 BEGIN
425 EXECUTE IMMEDIATE l_bulk_sql;
426
427 EXCEPTION
428 WHEN OTHERS THEN
429 fnd_file.put_line(fnd_file.log,'Error = ' || SQLERRM);
430
431 Process_Obj_Exec_Step(
432 p_request_id => l_request_id
433 ,p_user_id => l_user_id
434 ,p_login_id => l_login_id
435 ,p_rule_obj_id => p_rule_obj_id
436 ,p_exe_step => 'RGN_CNT'
437 ,p_exe_status_code => g_exec_status_error_rerun
438 ,p_tbl_name => 'FEM_REGION_INFO'
439 ,p_num_rows => l_num_rows_loaded);
440
441 RAISE e_process_single_rule_error;
442 END;
443
444 l_num_rows_loaded := SQL%ROWCOUNT;
445
446 IF l_cust_wo_rgn_code <> 0 THEN
447 -- Perform region counting for the customers for whom Region code is
448 -- not assigned(NULL)
449 FEM_ENGINES_PKG.Tech_Message (
450 p_severity => g_log_level_3
451 ,p_module => G_BLOCK||'.'||l_api_name
452 ,p_msg_text => 'Perform Region Counting for the customers region code is null');
453
454 FEM_ENGINES_PKG.Tech_Message (
455 p_severity => g_log_level_3
456 ,p_module => G_BLOCK||'.'||l_api_name
457 ,p_msg_text => 'SQL:' ||l_bulk_sql1 );
458 BEGIN
459 EXECUTE IMMEDIATE l_bulk_sql1;
460
461 EXCEPTION
462 WHEN OTHERS THEN
463 fnd_file.put_line(fnd_file.log,'Error = ' || SQLERRM);
464
465 Process_Obj_Exec_Step(
466 p_request_id => l_request_id
467 ,p_user_id => l_user_id
468 ,p_login_id => l_login_id
469 ,p_rule_obj_id => p_rule_obj_id
470 ,p_exe_step => 'RGN_CNT'
471 ,p_exe_status_code => g_exec_status_error_rerun
472 ,p_tbl_name => 'FEM_REGION_INFO'
473 ,p_num_rows => l_num_rows_loaded);
474
475 RAISE e_process_single_rule_error;
476 END;
477
478 l_num_rows_loaded := l_num_rows_loaded+SQL%ROWCOUNT;
479 END IF;
480
481 l_num_rows_loaded := NVL(l_num_rows_loaded,0);
482
483 Process_Obj_Exec_Step( p_request_id => l_request_id
484 ,p_user_id => l_user_id
485 ,p_login_id => l_login_id
486 ,p_rule_obj_id => p_rule_obj_id
487 ,p_exe_step => 'RGN_CNT'
488 ,p_exe_status_code => g_exec_status_success
489 ,p_tbl_name => 'FEM_REGION_INFO'
490 ,p_num_rows => l_num_rows_loaded);
491 -- commit the work
492 COMMIT;
493
494 ELSE
495 FEM_ENGINES_PKG.Tech_Message (
496 p_severity => G_LOG_LEVEL_2
497 ,p_module => G_BLOCK||'.'||l_api_name
498 ,p_msg_text => 'Region Counting is already performed for this Level');
499 END IF;
500
501 FEM_ENGINES_PKG.Tech_Message ( p_severity => G_LOG_LEVEL_2
502 ,p_module => G_BLOCK||'.'||l_api_name
503 ,p_msg_text => 'END');
504
505 EXCEPTION
506 WHEN e_process_single_rule_error THEN
507
508 FEM_ENGINES_PKG.Tech_Message (
509 p_severity => g_log_level_5
510 ,p_module => G_BLOCK||'.'||l_api_name
511 ,p_msg_text => 'Generate Region Counting Error:
512 Process Single Rule Exception');
513
514 FEM_ENGINES_PKG.User_Message (p_app_name => G_PFT
515 ,p_msg_name => G_ENG_SINGLE_RULE_ERR);
516
517 x_return_status := FND_API.G_RET_STS_ERROR;
518
519 WHEN OTHERS THEN
520
521 FEM_ENGINES_PKG.Tech_Message (
522 p_severity => g_log_level_5
523 ,p_module => G_BLOCK||'.'||l_api_name
524 ,p_msg_text => 'Generate Region Counting Error:
525 Process Single Rule Exception');
526
527 FEM_ENGINES_PKG.User_Message (p_app_name => G_PFT
528 ,p_msg_name => G_ENG_SINGLE_RULE_ERR);
529
530 x_return_status := FND_API.G_RET_STS_ERROR;
531
532 END Process_Single_Rule;
533
534 /*============================================================================+
535 | PROCEDURE
536 | Update_Num_Of_Output_Rows
537 |
538 | DESCRIPTION
539 | Updates the rows successfully processed by calling
540 | fem_pl_pkg.Update_Num_Of_Output_Rows in fem_pl_tables.
541 |
542 | SCOPE - PRIVATE
543 |
544 +============================================================================*/
545 PROCEDURE Update_Nbr_Of_Output_Rows( p_request_id IN NUMBER
546 ,p_user_id IN NUMBER
547 ,p_login_id IN NUMBER
548 ,p_rule_obj_id IN NUMBER
549 ,p_num_output_rows IN NUMBER
550 ,p_tbl_name IN VARCHAR2
551 ,p_stmt_type IN VARCHAR2)
552 IS
553
554 l_api_name CONSTANT VARCHAR2(30) := 'Update_Num_Of_Output_Rows';
555
556 l_return_status VARCHAR2(2);
557 l_msg_count NUMBER;
558 l_msg_data VARCHAR2(240);
559
560 e_upd_num_output_rows_error EXCEPTION;
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 -- Set the number of output rows for the output table.
569 FEM_PL_PKG.Update_Num_Of_Output_Rows(
570 p_api_version => 1.0
571 ,p_commit => FND_API.G_TRUE
572 ,p_request_id => p_request_id
573 ,p_object_id => p_rule_obj_id
574 ,p_table_name => p_tbl_name
575 ,p_statement_type => p_stmt_type
576 ,p_num_of_output_rows => p_num_output_rows
577 ,p_user_id => p_user_id
578 ,p_last_update_login => p_login_id
579 ,x_msg_count => l_msg_count
580 ,x_msg_data => l_msg_data
581 ,x_return_status => l_return_status);
582
583 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
584
585 Get_Put_Messages( p_msg_count => l_msg_count
586 ,p_msg_data => l_msg_data);
587
588 RAISE e_upd_num_output_rows_error;
589 END IF;
590
591 FEM_ENGINES_PKG.Tech_Message( p_severity => g_log_level_2
592 ,p_module => G_BLOCK||'.'||l_api_name
593 ,p_msg_text => 'END');
594
595 EXCEPTION
596 WHEN e_upd_num_output_rows_error THEN
597 FEM_ENGINES_PKG.Tech_Message (
598 p_severity => g_log_level_5
599 ,p_module => G_BLOCK||'.'||l_api_name
600 ,p_msg_text => 'Update Rows Exception');
601
602 FEM_ENGINES_PKG.User_Message (
603 p_app_name => G_PFT
604 ,p_msg_name => G_PL_OP_UPD_ROWS_ERR);
605
606 RAISE e_process_single_rule_error;
607
608 WHEN OTHERS THEN
609 FEM_ENGINES_PKG.User_Message (
610 p_app_name => G_PFT
611 ,p_msg_name => G_PL_OP_UPD_ROWS_ERR);
612
613 RAISE e_process_single_rule_error;
614
615 END Update_Nbr_Of_Output_Rows;
616
617 /*============================================================================+
618 | PROCEDURE
619 | Update_Obj_Exec_Step_Status
620 |
621 | DESCRIPTION
622 | Updates the status of the executuon of the object by calling
623 | fem_pl_pkg.Update_obj_exec_step_status in fem_pl_obj_steps.
624 |
625 | SCOPE - PRIVATE
626 |
627 +============================================================================*/
628 PROCEDURE Update_Obj_Exec_Step_Status( p_request_id IN NUMBER
629 ,p_user_id IN NUMBER
630 ,p_login_id IN NUMBER
631 ,p_rule_obj_id IN NUMBER
632 ,p_exe_step IN VARCHAR2
633 ,p_exe_status_code IN VARCHAR2)
634 IS
635
636 l_api_name CONSTANT VARCHAR2(30) := 'Update_Obj_Exe_Step_Status';
637
638 l_return_status VARCHAR2(1);
639 l_msg_count NUMBER;
640 l_msg_data VARCHAR2(240);
641
642 e_upd_obj_exec_step_stat_error EXCEPTION;
643
644 BEGIN
645
646 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
647 ,p_module => G_BLOCK||'.'||l_api_name
648 ,p_msg_text => 'BEGIN');
649
650 --Call the FEM_PL_PKG.Update_obj_exec_step_status API procedure
651 --to update step staus in fem_pl_obj_steps.
652 FEM_PL_PKG.Update_Obj_Exec_Step_Status(
653 p_api_version => 1.0
654 ,p_commit => FND_API.G_TRUE
655 ,p_request_id => p_request_id
656 ,p_object_id => p_rule_obj_id
657 ,p_exec_step => p_exe_step
658 ,p_exec_status_code => p_exe_status_code
659 ,p_user_id => p_user_id
660 ,p_last_update_login => p_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 Get_Put_Messages ( p_msg_count => l_msg_count
667 ,p_msg_data => l_msg_data);
668 RAISE e_upd_obj_exec_step_stat_error;
669
670 END IF;
671
672 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
673 ,p_module => G_BLOCK||'.'||l_api_name
674 ,p_msg_text => 'END');
675
676 EXCEPTION
677 WHEN e_upd_obj_exec_step_stat_error THEN
678 FEM_ENGINES_PKG.Tech_Message (
679 p_severity => g_log_level_5
680 ,p_module => G_BLOCK||'.'||l_api_name
681 ,p_msg_text => 'Update Obj Exec Step API Exception');
682
683 FEM_ENGINES_PKG.User_Message (
684 p_app_name => G_PFT
685 ,p_msg_name => G_PL_UPD_EXEC_STEP_ERR
686 ,p_token1 => 'OBJECT_ID'
687 ,p_value1 => p_rule_obj_id);
688
689 RAISE e_process_single_rule_error;
690
691 WHEN OTHERS THEN
692 FEM_ENGINES_PKG.User_Message (
693 p_app_name => G_PFT
694 ,p_msg_name => G_PL_UPD_EXEC_STEP_ERR
695 ,p_token1 => 'OBJECT_ID'
696 ,p_value1 => p_rule_obj_id);
697
698 RAISE e_process_single_rule_error;
699
700 END Update_Obj_Exec_Step_Status;
701
702 /*============================================================================+
703 | PROCEDURE
704 | Get_Nbr_RowsTable_For_Request
705 |
706 | DESCRIPTION
707 | To find the number rows processed by the request.
708 |
709 | SCOPE - PRIVATE
710 |
711 +============================================================================*/
712 PROCEDURE Get_Nbr_RowsTable_Request( x_rows_processed OUT NOCOPY NUMBER,
713 x_rows_loaded OUT NOCOPY NUMBER,
714 x_rows_rejected OUT NOCOPY NUMBER,
715 p_request_id IN NUMBER)
716 IS
717
718 l_api_name CONSTANT VARCHAR2(30) := 'Get_Nbr_RowsTable_Request';
719
720 BEGIN
721
722 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
723 ,p_module => G_BLOCK||'.'||l_api_name
724 ,p_msg_text => 'BEGIN');
725
726 --Query the fem_mp_process_ctl_t table to get the number of rows
727 --processed per request
728 SELECT NVL(SUM(rows_processed),0),
729 NVL(SUM(rows_rejected),0),
730 NVL(SUM(rows_loaded),0)
731 INTO x_rows_processed,
732 x_rows_rejected,
733 x_rows_loaded
734 FROM fem_mp_process_ctl_t t
735 WHERE t.req_id = p_request_id
736 AND t.process_num > 0;
737
738 IF (x_rows_processed = 0) THEN
739 FEM_ENGINES_PKG.Tech_Message (
740 p_severity => g_log_level_5
741 ,p_module => G_BLOCK||'.'||l_api_name
742 ,p_msg_text => 'No Rows returned by the Insert Statement');
743
744 FEM_ENGINES_PKG.User_Message (
745 p_app_name => G_PFT
746 ,p_msg_name => G_ENG_RCNT_NO_OP_ROWS_ERR);
747
748 RAISE e_process_single_rule_error;
749 END IF;
750
751 FEM_ENGINES_PKG.Tech_Message( p_severity => g_log_level_2
752 ,p_module => G_BLOCK||'.'||l_api_name
753 ,p_msg_text => 'END');
754
755 EXCEPTION
756 WHEN OTHERS THEN
757
758 RAISE;
759 END Get_Nbr_RowsTable_Request;
760
761 /*============================================================================+
762 | PROCEDURE
763 | Process_Obj_Exec_Step
764 | DESCRIPTION
765 | Processes the execution of the Object.
766 |
767 | SCOPE - PRIVATE
768 |
769 +============================================================================*/
770 PROCEDURE Process_Obj_Exec_Step( p_request_id IN NUMBER
771 ,p_user_id IN NUMBER
772 ,p_login_id IN NUMBER
773 ,p_rule_obj_id IN NUMBER
774 ,p_exe_step IN VARCHAR2
775 ,p_exe_status_code IN VARCHAR2
776 ,p_tbl_name IN VARCHAR2
777 ,p_num_rows IN NUMBER)
778 IS
779 l_api_name VARCHAR2(30);
780
781 BEGIN
782 l_api_name := 'Process_Obj_Exec_Step';
783
784 FEM_ENGINES_PKG.Tech_Message( p_severity => g_log_level_2
785 ,p_module => G_BLOCK||'.'||l_api_name
786 ,p_msg_text => 'BEGIN');
787 ------------------------------------------------------------------------
788 --Update the status of the step
789 ------------------------------------------------------------------------
790 FEM_ENGINES_PKG.Tech_Message(
791 p_severity => g_log_level_3
792 ,p_module => G_BLOCK||'.'||l_api_name
793 ,p_msg_text => 'Update the status of the step with execution status :'
794 ||p_exe_status_code);
795
796 --update the status of the step
797 Update_Obj_Exec_Step_Status( p_request_id => p_request_id
798 ,p_user_id => p_user_id
799 ,p_login_id => p_login_id
800 ,p_rule_obj_id => p_rule_obj_id
801 ,p_exe_step => 'RGN_CNT'
802 ,p_exe_status_code => p_exe_status_code );
803
804 IF (p_exe_status_code = g_exec_status_success) THEN
805 /* -- query table fem_mp_process_ctl_t to get the number of rows processed
806 Get_Nbr_RowsTable_Request(x_rows_processed => l_nbr_output_rows,
807 x_rows_loaded => l_nbr_loaded_rows,
808 x_rows_rejected => l_nbr_rejected_rows,
809 p_request_id => p_request_id);*/
810
811 FEM_ENGINES_PKG.Tech_Message(
812 p_severity => g_log_level_3
813 ,p_module => G_BLOCK||'.'||l_api_name
814 ,p_msg_text => 'Rows processed for registered output table :'
815 ||p_tbl_name);
816
817 -- update the number of rows processed in the registered table
818 Update_Nbr_Of_Output_Rows(
819 p_request_id => p_request_id
820 ,p_user_id => p_user_id
821 ,p_login_id => p_login_id
822 ,p_rule_obj_id => p_rule_obj_id
823 ,p_num_output_rows => p_num_rows
824 ,p_tbl_name => g_fem_region_info
825 ,p_stmt_type => g_insert );
826
827 -----------------------------------------------------------------------
828 -- Call FEM_PL_PKG.update_num_of_input_rows();
829 -----------------------------------------------------------------------
830 FEM_ENGINES_PKG.TECH_MESSAGE(
831 p_severity => g_log_level_1,
832 p_module => G_BLOCK||'.'||l_api_name,
833 p_msg_text => 'No:of Rows processed from input table'
834 ||p_num_rows );
835
836 -- update the number of rows processed in the registered table
837 Update_Nbr_Of_Input_Rows(
838 p_request_id => p_request_id
839 ,p_user_id => p_user_id
840 ,p_last_update_login => p_login_id
841 ,p_rule_obj_id => p_rule_obj_id
842 ,p_num_of_input_rows => p_num_rows);
843
844 FEM_ENGINES_PKG.User_Message(p_app_name => G_PFT,
845 p_msg_name => 'PFT_PPROF_RCNT_ROW_SUMMARY',
846 p_token1 => 'ROWSP',
847 p_value1 => p_num_rows,
848 p_token2 => 'ROWSL',
849 p_value2 => p_num_rows);
850 END IF;
851
852 FEM_ENGINES_PKG.Tech_Message( p_severity => g_log_level_2
853 ,p_module => G_BLOCK||'.'||l_api_name
854 ,p_msg_text => 'END');
855
856 EXCEPTION
857 WHEN OTHERS THEN
858 RAISE e_process_single_rule_error;
859
860 END;
861
862 /*============================================================================+
863 | FUNCTION
864 | Create Region Count Statement
865 |
866 | DESCRIPTION
867 | Creates the Bulk SQL for Region Counting step for the customers who have
868 | region code attribute defined
869 |
870 | SCOPE - PRIVATE
871 |
872 +============================================================================*/
873
874 FUNCTION Create_Region_Count_Stmt ( p_rule_obj_id IN NUMBER,
875 p_table_name IN VARCHAR2,
876 p_cal_period_id IN NUMBER,
877 p_effective_date IN VARCHAR2,
878 p_dataset_code IN NUMBER,
879 p_ledger_id IN NUMBER,
880 p_source_system_code IN NUMBER,
881 p_total_customers IN NUMBER,
882 p_customer_level IN NUMBER,
883 p_value_set_id IN NUMBER,
884 p_ds_where_clause IN LONG)
885 RETURN LONG IS
886
887 l_api_name CONSTANT VARCHAR2(30) := 'Create_Region_Count_Stmt';
888
889 l_insert_head_stmt LONG;
890 l_select_stmt LONG;
891 l_from_stmt LONG;
892 l_where_stmt LONG;
893 l_group_by_stmt VARCHAR2(100);
894 l_rel_dimension_grp_seq NUMBER;
895 l_request_id NUMBER;
896 l_msg_count NUMBER;
897 l_msg_data VARCHAR2(500);
898 l_return_status VARCHAR2(20);
899 l_effective_date DATE;
900 l_user_id NUMBER;
901 l_login_id NUMBER := FND_GLOBAL.Login_Id;
902
903 BEGIN
904 l_request_id := FND_GLOBAL.Conc_Request_Id;
905 l_user_id := FND_GLOBAL.User_Id;
906 l_effective_date := FND_DATE.Canonical_To_Date(p_effective_date);
907
908 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
909 ,p_module => G_BLOCK||'.'||l_api_name
910 ,p_msg_text => 'BEGIN');
911
912 l_insert_head_stmt := ' INSERT INTO FEM_REGION_INFO ( ' ||
913 ' CAL_PERIOD_ID, ' ||
914 ' DATASET_CODE, ' ||
915 ' DIMENSION_GROUP_ID, ' ||
916 ' SOURCE_SYSTEM_CODE, ' ||
917 ' REGION_CODE, ' ||
918 ' LEDGER_ID, ' ||
919 ' REGION_PCT_TOTAL_CUST, ' ||
920 ' NUMBER_OF_CUSTOMERS, ' ||
921 ' CREATED_BY_OBJECT_ID, ' ||
922 ' CREATED_BY_REQUEST_ID, ' ||
923 ' LAST_UPDATED_BY_OBJECT_ID, ' ||
924 ' LAST_UPDATED_BY_REQUEST_ID ';
925
926 l_select_stmt := ' ) SELECT '||
927 p_cal_period_id || ' , ' ||
928 p_dataset_code || ' , ' ||
929 p_customer_level || ' , ' ||
930 p_source_system_code || ' , ' ||
931 'fca.number_assign_value, ' ||
932 p_ledger_id || ' , ' ||
933 ' 100 * (COUNT(fca.number_assign_value)/'
934 || p_total_customers || ') , ' ||
935 'COUNT(fca.number_assign_value)' || ' , ' ||
936 p_rule_obj_id || ' , ' ||
937 l_request_id || ' , ' ||
938 l_user_id ||' , ' ||
939 l_request_id;
940
941 l_from_stmt := ' FROM ' ||
942 ' FEM_CUSTOMERS_ATTR fca , ' ||
943 ' (' || ' SELECT dim_attr.attribute_id ' ||
944 ' FROM fem_dim_attributes_b dim_attr, ' ||
945 ' fem_dimensions_b xdim ' ||
946 ' WHERE dim_attr.dimension_id = xdim.dimension_id'||
947 ' AND dim_attr.attribute_varchar_label = ' ||
948 '''REGION_CODE''' ||
949 ' AND xdim.dimension_varchar_label = ''CUSTOMER'''||
950 ' )' || 'T1 ';
951 l_where_stmt := ' WHERE ' ||
952 ' fca.attribute_id = T1.attribute_id ' ||
953 ' AND fca.customer_id IN ( ' ||
954 ' SELECT customer_id ' ||
955 ' FROM fem_customers_b ' ||
956 ' WHERE dimension_group_id = ' ||
957 p_customer_level ||
958 ' AND value_set_id = ' ||
959 p_value_set_id || ' ) ';
960
961 l_group_by_stmt := ' GROUP BY ' ||
962 ' fca.number_assign_value ';
963
964 FEM_ENGINES_PKG.Tech_Message ( p_severity => G_LOG_LEVEL_2
965 ,p_module => G_BLOCK||'.'||l_api_name
966 ,p_msg_text => 'END');
967 -- add mapped columns
968 RETURN l_insert_head_stmt || ' ' || l_select_stmt || ' ' || l_from_stmt
969 || ' ' || l_where_stmt || ' ' || l_group_by_stmt;
970
971 EXCEPTION
972 WHEN OTHERS THEN
973 RAISE;
974
975 END Create_Region_Count_Stmt;
976
977 /*============================================================================+
978 | PROCEDURE
979 | Get_Put_Messages
980 |
981 | DESCRIPTION
982 | To put the User messages,to be placed in common loader package.
983 |
984 | SCOPE - PRIVATE
985 |
986 +============================================================================*/
987
988 PROCEDURE Get_Put_Messages ( p_msg_count IN NUMBER
989 ,p_msg_data IN VARCHAR2)
990 IS
991
992 l_api_name CONSTANT VARCHAR2(30) := 'Get_Put_Messages';
993 l_msg_count NUMBER;
994 l_msg_data VARCHAR2(4000);
995 l_msg_out NUMBER;
996 l_message VARCHAR2(4000);
997
998 BEGIN
999
1000 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
1001 ,p_module => G_BLOCK||'.'||l_api_name
1002 ,p_msg_text => 'msg_count='||p_msg_count);
1003
1004 l_msg_data := p_msg_data;
1005
1006 IF (p_msg_count = 1) THEN
1007
1008 FND_MESSAGE.Set_Encoded(l_msg_data);
1009 l_message := FND_MESSAGE.Get;
1010
1011 FEM_ENGINES_PKG.User_Message ( p_msg_text => l_message );
1012
1013 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
1014 ,p_module => G_BLOCK||'.'||l_api_name
1015 ,p_msg_text => 'msg_data='||l_message);
1016
1017 ELSIF (p_msg_count > 1) THEN
1018
1019 FOR i IN 1..p_msg_count LOOP
1020 FND_MSG_PUB.Get ( p_msg_index => i
1021 ,p_encoded => FND_API.G_FALSE
1022 ,p_data => l_message
1023 ,p_msg_index_out => l_msg_out);
1024
1025 FEM_ENGINES_PKG.User_Message ( p_msg_text => l_message );
1026
1027 FEM_ENGINES_PKG.Tech_Message (
1028 p_severity => g_log_level_2
1029 ,p_module => G_BLOCK||'.'||l_api_name
1030 ,p_msg_text => 'msg_data = '||l_message);
1031
1032 END LOOP;
1033
1034 END IF;
1035
1036 FND_MSG_PUB.Initialize;
1037
1038 END Get_Put_Messages;
1039
1040 /*============================================================================+
1041 | PROCEDURE
1042 | Update_Num_Of_Input_Rows
1043 |
1044 | DESCRIPTION
1045 | This procedure logs the total number of rows used as input into
1046 | an object execution
1047 |
1048 | SCOPE - PRIVATE
1049 |
1050 +============================================================================*/
1051 PROCEDURE Update_Nbr_Of_Input_Rows( p_request_id IN NUMBER
1052 ,p_user_id IN NUMBER
1053 ,p_last_update_login IN NUMBER
1054 ,p_rule_obj_id IN NUMBER
1055 ,p_num_of_input_rows IN NUMBER )
1056 IS
1057
1058 l_api_name CONSTANT VARCHAR2(30) := 'Update_Num_Of_Input_Rows';
1059
1060 l_return_status VARCHAR2(2);
1061 l_msg_count NUMBER;
1062 l_msg_data VARCHAR2(240);
1063
1064 e_upd_num_input_rows_error EXCEPTION;
1065
1066 BEGIN
1067
1068 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
1069 ,p_module => G_BLOCK||'.'||l_api_name
1070 ,p_msg_text => 'BEGIN');
1071
1072 -- Set the number of output rows for the output table.
1073 FEM_PL_PKG.Update_Num_Of_Input_Rows(
1074 p_api_version => 1.0
1075 ,p_commit => FND_API.G_TRUE
1076 ,p_request_id => p_request_id
1077 ,p_object_id => p_rule_obj_id
1078 ,p_num_of_input_rows => p_num_of_input_rows
1079 ,p_user_id => p_user_id
1080 ,p_last_update_login => p_last_update_login
1081 ,x_msg_count => l_msg_count
1082 ,x_msg_data => l_msg_data
1083 ,x_return_status => l_return_status);
1084
1085 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1086 Get_Put_Messages( p_msg_count => l_msg_count
1087 ,p_msg_data => l_msg_data);
1088 RAISE e_upd_num_input_rows_error;
1089 END IF;
1090
1091 FEM_ENGINES_PKG.Tech_Message( p_severity => g_log_level_2
1092 ,p_module => G_BLOCK||'.'||l_api_name
1093 ,p_msg_text => 'END');
1094
1095 EXCEPTION
1096 WHEN e_upd_num_input_rows_error THEN
1097 FEM_ENGINES_PKG.Tech_Message (
1098 p_severity => g_log_level_5
1099 ,p_module => G_BLOCK||'.'||l_api_name
1100 ,p_msg_text => 'Update Input Rows Exception');
1101
1102 FEM_ENGINES_PKG.User_Message (
1103 p_app_name => G_PFT
1104 ,p_msg_name => G_PL_IP_UPD_ROWS_ERR);
1105
1106 RAISE e_process_single_rule_error;
1107
1108 WHEN OTHERS THEN
1109 FEM_ENGINES_PKG.Tech_Message (
1110 p_severity => g_log_level_5
1111 ,p_module => G_BLOCK||'.'||l_api_name
1112 ,p_msg_text => 'Update Input Rows Exception');
1113
1114 FEM_ENGINES_PKG.User_Message (
1115 p_app_name => G_PFT
1116 ,p_msg_name => G_PL_IP_UPD_ROWS_ERR);
1117
1118 RAISE e_process_single_rule_error;
1119
1120 END Update_Nbr_Of_Input_Rows;
1121
1122 /*============================================================================+
1123 | FUNCTION
1124 | Create Region Count Statement
1125 |
1126 | DESCRIPTION
1127 | Creates the Bulk SQL for Region Counting step for the customers who doesn't
1128 | have region code attribute defined
1129 |
1130 | SCOPE - PRIVATE
1131 |
1132 +============================================================================*/
1133
1134 FUNCTION Create_Rgn_Cnt_Wo_RCode_Stmt ( p_rule_obj_id IN NUMBER,
1135 p_table_name IN VARCHAR2,
1136 p_cal_period_id IN NUMBER,
1137 p_effective_date IN VARCHAR2,
1138 p_dataset_code IN NUMBER,
1139 p_ledger_id IN NUMBER,
1140 p_source_system_code IN NUMBER,
1141 p_total_customers IN NUMBER,
1142 p_cust_wo_rgn_code IN NUMBER,
1143 p_customer_level IN NUMBER,
1144 p_value_set_id IN NUMBER,
1145 p_ds_where_clause IN LONG)
1146 RETURN LONG IS
1147
1148 l_api_name CONSTANT VARCHAR2(30) := 'Create_Region_Count_Stmt';
1149
1150 l_insert_head_stmt LONG;
1151 l_select_stmt LONG;
1152 l_from_stmt LONG;
1153 l_where_stmt LONG;
1154 l_group_by_stmt VARCHAR2(100);
1155 l_rel_dimension_grp_seq NUMBER;
1156 l_request_id NUMBER;
1157 l_msg_count NUMBER;
1158 l_msg_data VARCHAR2(500);
1159 l_return_status VARCHAR2(20);
1160 l_effective_date DATE;
1161 l_user_id NUMBER;
1162 l_login_id NUMBER := FND_GLOBAL.Login_Id;
1163
1164 BEGIN
1165 l_request_id := FND_GLOBAL.Conc_Request_Id;
1166 l_user_id := FND_GLOBAL.User_Id;
1167 l_effective_date := FND_DATE.Canonical_To_Date(p_effective_date);
1168
1169 FEM_ENGINES_PKG.Tech_Message ( p_severity => g_log_level_2
1170 ,p_module => G_BLOCK||'.'||l_api_name
1171 ,p_msg_text => 'BEGIN');
1172
1173 l_insert_head_stmt := ' INSERT INTO FEM_REGION_INFO ( ' ||
1174 ' CAL_PERIOD_ID, ' ||
1175 ' DATASET_CODE, ' ||
1176 ' DIMENSION_GROUP_ID, ' ||
1177 ' SOURCE_SYSTEM_CODE, ' ||
1178 ' REGION_CODE, ' ||
1179 ' LEDGER_ID, ' ||
1180 ' REGION_PCT_TOTAL_CUST, ' ||
1181 ' NUMBER_OF_CUSTOMERS, ' ||
1182 ' CREATED_BY_OBJECT_ID, ' ||
1183 ' CREATED_BY_REQUEST_ID, ' ||
1184 ' LAST_UPDATED_BY_OBJECT_ID, ' ||
1185 ' LAST_UPDATED_BY_REQUEST_ID ';
1186
1187 l_select_stmt := ' ) SELECT '||
1188 p_cal_period_id || ' , ' ||
1189 p_dataset_code || ' , ' ||
1190 p_customer_level || ' , ' ||
1191 p_source_system_code || ' , ' ||
1192 ' NULL, ' ||
1193 p_ledger_id || ' , ' ||
1194 ' 100 * (' ||p_cust_wo_rgn_code || '/'
1195 || p_total_customers || ') , ' ||
1196 p_cust_wo_rgn_code || ' , ' ||
1197 p_rule_obj_id || ' , ' ||
1198 l_request_id || ' , ' ||
1199 l_user_id ||' , ' ||
1200 l_request_id;
1201
1202 l_from_stmt := ' FROM ' ||
1203 ' DUAL';
1204
1205 FEM_ENGINES_PKG.Tech_Message ( p_severity => G_LOG_LEVEL_2
1206 ,p_module => G_BLOCK||'.'||l_api_name
1207 ,p_msg_text => 'END');
1208 -- add mapped columns
1209 RETURN l_insert_head_stmt || ' ' || l_select_stmt || ' ' || l_from_stmt;
1210
1211
1212 EXCEPTION
1213 WHEN OTHERS THEN
1214 RAISE;
1215
1216 END Create_Rgn_Cnt_Wo_RCode_Stmt;
1217
1218
1219
1220 END PFT_PROFCAL_RGNCNT_PUB;