[Home] [Help]
PACKAGE BODY: APPS.OE_BIS_ALERTS
Source
1 package body OE_BIS_ALERTS AS
2 --$Header: OEXALRTB.pls 115.1 99/07/27 19:10:28 porting shi $
3
4 /* The Calculate_Actual function calculates the actual on-time shipment percentage or the return percentage
5 depending upon the target level that is being calculated for. There are four target levels seeded in
6 Release 11i for the Customer Satisfaction report ie. SHIPVALUE_TOP, SHIPVALUE_ALL, RETVALUE_TOP and RETVALUE_ALL.
7 The code supports the calculation of only these four target levels. If additional target levels are added - we
8 would need to add code in the calculate_actual function to handle the same */
9
10 /* Seeded Target Levels Definition
11 SHIPVALUE_ALL :- On Time Shipment percentage for the following combination
12 (Set of Books id * Year * All Sales Channel's * All Product Categories * All Geographical Area's)
13 SHIPVALUE_TOP :- On Time Shipment percentage for the following combination
14 (Set of Books id * Year * Sales Channel * Product Category * Geographical Area)
15 RETVALUE_ALL :- Return percentage for the following combination
16 (Set of Books id * Year * All Sales Channel's * All Product Categories * All Geographical Area's)
17 RETVALUE_TOP :- Return percentage for the following combination
18 (Set of Books id * Year * Sales Channel * Product Category * Geographical Area) */
19
20
21 FUNCTION Calculate_Actual
22 (
23 p_set_of_books_id VARCHAR2,
24 p_sales_channel VARCHAR2,
25 p_prod_catg VARCHAR2,
26 p_area VARCHAR2,
27 p_period_set_Name VARCHAR2,
28 p_time_period VARCHAR2,
29 p_target_level_short_name VARCHAR2
30 )
31 RETURN NUMBER
32
33 IS
34 l_actual_value NUMBER;
35 l_time_period VARCHAR2(80);
36
37 BEGIN
38 l_actual_value := 0;
39
40 if p_target_level_short_name = 'SHIPVALUE_TOP' then
41 begin
42 select year_period, decode(sum(net_sales),0,0,sum(del_sales)/sum(net_sales) * 100)
43 into l_time_period, l_actual_value
44 from oe_bis_cust_sat_v
45 where set_of_books_id = p_set_of_books_id
46 and sales_channel_code = p_sales_channel
47 and category_id = p_prod_catg
48 and area = p_area
49 and period_set_name || '+' || year_period = p_period_set_name
50 group by year_period;
51 exception
52 when no_data_found then
53 l_actual_value := -1;
54 when others then
55 l_actual_value := -2;
56 end;
57 elsif p_target_level_short_name = 'RETVALUE_TOP' then
58 begin
59 select year_period, decode(sum(net_sales),0,0,sum(ret_sales)/sum(net_sales) * 100)
60 into l_time_period, l_actual_value
61 from oe_bis_cust_sat_v
62 where set_of_books_id = p_set_of_books_id
63 and sales_channel_code = p_sales_channel
64 and category_id = P_prod_catg
65 and area = p_area
66 and period_set_name || '+' || year_period = p_period_set_name
67 group by year_period;
68 exception
69 when others then
70 l_actual_value := 0;
71 end;
72 elsif p_target_level_short_name = 'SHIPVALUE_ALL' then
73 begin
74 select year_period, decode(sum(net_sales),0,0,sum(del_sales)/sum(net_sales) * 100)
75 into l_time_period, l_actual_value
76 from oe_bis_cust_sat_v
77 where set_of_books_id = p_set_of_books_id
78 and period_set_name || '+' || year_period = p_period_set_name
79 group by year_period;
80 exception
81 when others then
82 l_actual_value := 0;
83 end;
84 elsif p_target_level_short_name = 'RETVALUE_ALL' then
85 begin
86 select year_period, decode(sum(net_sales),0,0,sum(ret_sales)/sum(net_sales) * 100)
87 into l_time_period, l_actual_value
88 from oe_bis_cust_sat_v
89 where set_of_books_id = p_set_of_books_id
90 and period_set_name || '+' || year_period = p_period_set_name
91 group by year_period;
92 exception
93 when others then
94 l_actual_value := 0;
95 end;
96 end if;
97
98 RETURN l_actual_value;
99
100 EXCEPTION
101 WHEN OTHERS THEN
102 l_actual_value := -1;
103
104 END Calculate_Actual;
105
106 /* Procedure process_alerts is called from the alert run by the alert manager responsibility. The two parameters
107 that are passed are the target level short name for eg. SHIPVALUE_ALL and the time period is the accounting year
108 for eg. Accounting+FY-99 */
109
110 /* User selected KPI's from the BIS home page are also updated with the actual values. BIS provided API's are used
111 to retreive user selections and to post the actuals on the home page */
112
113 PROCEDURE process_alerts
114 ( p_target_level_short_name VARCHAR2,
115 p_time_period VARCHAR2
116 )
117 IS
118 l_period_set_name VARCHAR2(30);
119 l_period_name VARCHAR2(30);
120 l_target_short_name VARCHAR2(30);
121 l_target_level_rec BIS_TARGET_LEVEL_PUB.target_Level_Rec_Type;
122 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
123 l_actual VARCHAR2(240);
124 l_period VARCHAR2(240);
125 actual NUMBER;
126 l_target VARCHAR2(240);
127 target NUMBER;
128 l_Target_Rec BIS_TARGET_PUB.Target_Rec_Type;
129 l_Actual_Tbl BIS_ACTUAL_PUB.Actual_Tbl_Type;
130 l_user_selection_tbl BIS_INDICATOR_REGION_PUB.Indicator_Region_Tbl_Type;
131 l_workflow_process VARCHAR2(30);
132 l_notify_resp VARCHAR2(100);
133 l_responsibility_ID NUMBER;
134 l_message VARCHAR2(1000);
135 l_param VARCHAR2(1000);
136 l_set_of_books_id VARCHAR2(250);
137 l_organization_id VARCHAR2(250);
138 l_sales_channel VARCHAR2(80);
139 l_prod_catg VARCHAR2(250);
140 l_area VARCHAR2(80);
141 l_msg_data VARCHAR2(250);
142 l_subject VARCHAR2(250);
143 l_return_status VARCHAR2(1);
144 l_msg_count NUMBER;
145 l_report_name VARCHAR2(250);
146 l_exception_message VARCHAR2(250);
147 l_org_name VARCHAR2(250);
148 l_catg_name VARCHAR2(250);
149 l_start_date VARCHAR2(15);
150 l_end_date VARCHAR2(15);
151 status VARCHAR2(30);
152 dim1_name VARCHAR2(80);
153 dim2_name VARCHAR2(80);
154 dim3_name VARCHAR2(80);
155 i NUMBER := 0;
156
157 CURSOR cr_set_of_books IS
158 SELECT distinct set_of_books_id
159 FROM oe_bis_cust_sat_v;
160
161 CURSOR cr_target IS
162 SELECT tv.target_level_short_name
163 , tv.target_level_name
164 , tv.target_level_id
165 , tv.plan_name
166 , tv.org_level_value_id
167 , tv.time_level_value_id
168 , tv.dim1_level_value_id
169 , tv.dim2_level_value_id
170 , tv.dim3_level_value_id
171 , tv.target
172 , tv.range1_low
173 , tv.range1_high
174 , tv.range2_low
175 , tv.range2_high
176 , tv.range3_low
177 , tv.range3_high
178 , tv.notify_resp1_id
179 , tv.notify_resp1_short_name
180 , tv.notify_resp2_id
181 , tv.notify_resp2_short_name
182 , tv.notify_resp3_id
183 , tv.notify_resp3_short_name
184 FROM BISFV_TARGETS tv
185 WHERE tv.target_level_short_name = p_target_level_short_name
186 AND tv.time_level_value_id = p_time_period;
187
188 BEGIN
189
190 l_target_level_rec.target_Level_Short_Name
191 := p_target_Level_Short_Name;
192
193 SELECT workflow_process_short_name
194 INTO l_workflow_process
195 FROM bisfv_target_levels
196 WHERE target_level_short_name = p_target_Level_Short_Name;
197
198
199 -- Get the KPIs users have selected to monitor on their homepage
200 BIS_ACTUAL_PUB.Retrieve_User_Selections
201 ( p_api_version => 1.0
202 ,p_Target_Level_Rec => l_Target_Level_Rec
203 ,x_Indicator_Region_Tbl => l_user_selection_Tbl
204 ,x_return_status => l_return_status
205 ,x_msg_count => l_msg_count
206 ,x_msg_data => l_msg_data
207 ,x_error_Tbl => l_error_tbl
208 );
209
210 -- Calculate Actual for Set of Books
211 FOR cr in cr_set_of_books LOOP
212
213 l_organization_id := cr.set_of_books_id;
214
215 -- Post actual value for only those KPIs users have selected.
216 -- These user selected KPI's are picked up from the bis_user_ind_selections table
217
218 FOR i IN 1..l_user_selection_Tbl.COUNT LOOP
219 IF l_user_selection_tbl(i).org_level_value_id = l_organization_id THEN
220 l_Actual_Tbl(i).target_Level_Short_Name
221 := l_user_selection_tbl(i).target_level_short_name;
222 l_Actual_Tbl(i).Org_Level_value_ID := l_organization_id;
223 l_Actual_Tbl(i).time_Level_value_ID := p_time_period;
224 l_Actual_Tbl(i).dim1_Level_value_ID := l_user_selection_tbl(i).dim1_Level_value_ID;
225 l_Actual_Tbl(i).dim2_Level_value_ID := l_user_selection_tbl(i).dim2_Level_value_ID;
226 l_Actual_Tbl(i).dim3_Level_value_ID := l_user_selection_tbl(i).dim3_level_value_ID;
227
228 actual := Calculate_Actual
229 ( p_set_of_books_id => l_organization_id,
230 p_sales_channel => l_user_selection_tbl(i).dim1_level_value_ID,
231 p_prod_catg => l_user_selection_tbl(i).dim2_level_value_ID,
232 p_area => l_user_selection_tbl(i).dim3_level_value_ID,
233 p_period_set_Name => p_time_period,
234 p_time_period => p_time_period,
235 p_target_level_short_name => p_target_level_short_name
236 );
237
238 l_Actual_Tbl(i).Actual := actual;
239
240 BIS_ACTUAL_PUB.POST_ACTUAL
241 ( p_api_version => 1.0
242 ,p_Actual_Rec => l_actual_Tbl(i)
243 ,x_return_status => l_return_status
244 ,x_msg_count => l_msg_count
245 ,x_msg_data => l_msg_data
246 ,x_error_tbl => l_error_tbl
247 );
248
249 END IF;
250 END LOOP;
251
252 END LOOP;
253
254 for cr in cr_target
255 loop
256
257 target := cr.target;
258
259 actual := Calculate_Actual
260 ( p_set_of_books_id => cr.org_level_value_id,
261 p_sales_channel => cr.dim1_level_value_id,
262 p_prod_catg => cr.dim2_level_value_id,
263 p_area => cr.dim3_level_value_id,
264 p_period_set_Name => p_time_period,
265 p_time_period => p_time_period,
266 p_target_level_short_name => p_target_level_short_name
267 );
268
269
270 postactual( cr.target_level_id,
271 cr.org_level_value_id,
272 p_time_period,
273 cr.dim1_level_value_id,
274 cr.dim2_level_value_id,
275 cr.dim3_level_value_id,
276 actual,
277 p_time_period);
278
279 l_report_name := 'OEXCUSSA';
280
281
282 begin
283 select name into l_org_name
284 from gl_sets_of_books
285 where set_of_books_id = cr.org_level_value_id ;
286 exception
287 when others then
288 l_org_name := null;
289 end;
290
291 begin
292 select distinct category_desc into l_catg_name
293 from oe_bis_cust_sat_v
294 where category_id = cr.dim2_level_value_id ;
295 exception
296 when others then
297 l_catg_name := null;
298 end;
299
300
301 begin
302 select to_char(start_date, 'DD-MON-YYYY'), to_char(end_date + 1,'DD-MON-YYYY')
303 into l_start_date , l_end_date
304 from bis_years_v
305 where period_set_name = substr(p_time_period, 1, instr(p_time_period,'+')-1) and
306 period_name = substr(p_time_period,instr(p_time_period,'+')+1, length(p_time_period)) and
307 rownum = 1;
308 exception
309 when others then
310 l_start_date := null;
311 l_end_date := null;
312 end;
313
314 -- This is for the description to appear in the notifications that are sent.
315
316 if cr.dim1_level_value_id = '-1' then
317 dim1_name := 'All Sales Channels';
318 else
319 dim1_name := cr.dim1_level_value_id;
320 end if;
321
322 if cr.dim2_level_value_id = '-1' then
323 dim2_name := 'All Product Categories';
324 else
325 dim2_name := cr.dim2_level_value_id;
326 end if;
327
328 if cr.dim3_level_value_id = '-1' then
329 dim3_name := 'All Areas';
330 else
331 dim3_name := cr.dim3_level_value_id;
332 end if;
333
334 -- Messages are seeded using Oracle Apps screens - so that customer can change the heading on the messages
335 -- as per requirements
336
337 l_subject := fnd_message.get_string('OE', 'OE_BIS_SUBJECT') || ' - ' || cr.target_level_name;
338 l_set_of_books_id := fnd_message.get_string('OE', 'OE_BIS_SET_OF_BOOKS') || ': ' || cr.org_level_value_id ||
339 ' - ' || l_org_name;
340 l_area := fnd_message.get_string('OE', 'OE_BIS_AREA') || ': ' || dim3_name;
341 l_prod_catg := fnd_message.get_string('OE', 'OE_BIS_PROD_CATEGORY') || ': ' || dim2_name;
342 l_sales_channel := fnd_message.get_string('OE', 'OE_BIS_SALES_CHANNEL') || ': ' || dim1_name;
343 l_period := fnd_message.get_string('OE', 'OE_BIS_PERIOD')|| ': ' || p_time_period;
344 l_actual := fnd_message.get_string('OE', 'OE_BIS_ACTUAL')|| ': ' || round(actual, 2);
345 l_target := fnd_message.get_string('OE', 'OE_BIS_TARGET')|| ': ' || round(target, 2);
346
347 -- Reverting back because the report needs to be called with the right parameters.
348
349 if cr.dim1_level_value_id = '-1' then
350 dim1_name := '0';
351 else
352 dim1_name := cr.dim1_level_value_id;
353 end if;
354
355 if cr.dim2_level_value_id = '-1' then
356 dim2_name := '0';
357 else
358 dim2_name := cr.dim2_level_value_id;
359 end if;
360
361 if cr.dim3_level_value_id = '-1' then
362 dim3_name := '0';
363 else
364 dim3_name := cr.dim3_level_value_id;
365 end if;
366
367 -- setting up the parameter list for the url formulation
368
369 l_param := 'P_PARAM_DATE_FROM=' || l_start_date ||
370 '*P_PARAM_DATE_TO=' || l_end_date ||
371 '*P_PARAM_ORG_LEVEL=1'||
372 '*P_PARAM_ORGANIZATION=' || cr.org_level_value_id ||
373 '*P_PARAM_CUST_LEVEL=1' ||
374 '*P_PARAM_CUSTOMER=' || dim1_name ||
375 '*P_PARAM_GEO_LEVEL=1' ||
376 '*P_PARAM_GEOGRAPHY=' || dim3_name ||
377 '*P_PARAM_PROD_LEVEL=1' ||
378 '*P_PARAM_PRODUCT=' || dim2_name ||
379 '*P_PARAM_VIEW_BY=1' ||
380 '*paramform=NO' ||
381 '*paramform=NO*';
382
383 -- Actuals are compared to the targets that are set and depending upon the ranges that they fall in
384 -- respective responsibities are contacted.
385
386 IF actual < target THEN
387
388 -- Check if actual is Within the first range
389 IF actual < target - cr.range1_low
390 THEN
391 l_notify_resp := cr.Notify_Resp1_short_name;
392 l_responsibility_id := cr.Notify_Resp1_ID;
393 if cr.target_level_short_name in ('SHIPVALUE_TOP','SHIPVALUE_ALL') then
394 l_exception_message := 'A PMF target has been set to monitor Customer Satisfaction Percentages.' ||
395 ' This target has fallen short for the following :';
396 else
397 l_exception_message := 'A PMF target has been set to monitor Customer Satisfaction Percentages.' ||
398 ' We have exceeded the returns target for the following :';
399 end if;
400
401 oe_strt_wf_process(
402 p_exception_message => l_exception_message,
403 p_subject => l_subject,
404 p_sob => l_set_of_books_id,
405 p_area => l_area,
406 p_prod_cat => l_prod_catg,
407 p_sales_channel => l_sales_channel,
408 p_period => l_period,
409 p_target => l_target,
410 p_actual => l_actual,
411 p_wf_process => l_workflow_process,
412 p_role => l_notify_resp,
413 p_resp_id => l_responsibility_id,
414 p_report_name => l_report_name,
415 p_report_param => l_param,
416 x_return_status => status);
417
418 -- Check if actual is within the second range
419 ELSIF actual < target - cr.range2_low
420 THEN
421 l_notify_resp := cr.Notify_Resp2_short_name;
422 l_responsibility_id := cr.Notify_Resp2_ID;
423 if cr.target_level_short_name in ('SHIPVALUE_TOP','SHIPVALUE_ALL') then
424 l_exception_message := 'A PMF target has been set to monitor Customer Satisfaction Percentages.' ||
425 ' This target has fallen short for the following :';
426 else
427 l_exception_message := 'A PMF target has been set to monitor Customer Satisfaction Percentages.' ||
428 ' We have exceeded the returns target for the following :';
429 end if;
430
431 oe_strt_wf_process(
432 p_exception_message => l_exception_message,
433 p_subject => l_subject,
434 p_sob => l_set_of_books_id,
435 p_area => l_area,
436 p_prod_cat => l_prod_catg,
437 p_sales_channel => l_sales_channel,
438 p_period => l_period,
439 p_target => l_target,
440 p_actual => l_actual,
441 p_wf_process => l_workflow_process,
442 p_role => l_notify_resp,
443 p_resp_id => l_responsibility_id,
444 p_report_name => l_report_name,
445 p_report_param => l_param,
446 x_return_status => status);
447
448 l_notify_resp := cr.Notify_Resp1_short_name;
449 l_responsibility_id := cr.Notify_Resp1_ID;
450
451 oe_strt_wf_process(
452 p_exception_message => l_exception_message,
453 p_subject => l_subject,
454 p_sob => l_set_of_books_id,
455 p_area => l_area,
456 p_prod_cat => l_prod_catg,
457 p_sales_channel => l_sales_channel,
458 p_period => l_period,
459 p_target => l_target,
460 p_actual => l_actual,
461 p_wf_process => l_workflow_process,
462 p_role => l_notify_resp,
463 p_resp_id => l_responsibility_id,
464 p_report_name => l_report_name,
465 p_report_param => l_param,
466 x_return_status => status);
467
468 -- Check if actual is within the third range
469 ELSIF actual < target - cr.range3_low
470 THEN
471 l_notify_resp := cr.Notify_Resp3_short_name;
472 l_responsibility_id := cr.Notify_Resp3_ID;
473 if cr.target_level_short_name in ('SHIPVALUE_TOP','SHIPVALUE_ALL') then
474 l_exception_message := 'A PMF target has been set to monitor Customer Satisfaction Percentages.' ||
475 ' This target has fallen short for the following :';
476 else
477 l_exception_message := 'A PMF target has been set to monitor Customer Satisfaction Percentages.' ||
478 ' We have exceeded the returns target for the following :';
479 end if;
480
481 oe_strt_wf_process(
482 p_exception_message => l_exception_message,
483 p_subject => l_subject,
484 p_sob => l_set_of_books_id,
485 p_area => l_area,
486 p_prod_cat => l_prod_catg,
487 p_sales_channel => l_sales_channel,
488 p_period => l_period,
489 p_target => l_target,
490 p_actual => l_actual,
491 p_wf_process => l_workflow_process,
492 p_role => l_notify_resp,
493 p_resp_id => l_responsibility_id,
494 p_report_name => l_report_name,
495 p_report_param => l_param,
496 x_return_status => status);
497
498 l_notify_resp := cr.Notify_Resp2_short_name;
499 l_responsibility_id := cr.Notify_Resp2_ID;
500
501 oe_strt_wf_process(
502 p_exception_message => l_exception_message,
503 p_subject => l_subject,
504 p_sob => l_set_of_books_id,
505 p_area => l_area,
506 p_prod_cat => l_prod_catg,
507 p_sales_channel => l_sales_channel,
508 p_period => l_period,
509 p_target => l_target,
510 p_actual => l_actual,
511 p_wf_process => l_workflow_process,
512 p_role => l_notify_resp,
513 p_resp_id => l_responsibility_id,
514 p_report_name => l_report_name,
515 p_report_param => l_param,
516 x_return_status => status);
517
518 l_notify_resp := cr.Notify_Resp1_short_name;
519 l_responsibility_id := cr.Notify_Resp1_ID;
520
521 oe_strt_wf_process(
522 p_exception_message => l_exception_message,
523 p_subject => l_subject,
524 p_sob => l_set_of_books_id,
525 p_area => l_area,
526 p_prod_cat => l_prod_catg,
527 p_sales_channel => l_sales_channel,
528 p_period => l_period,
529 p_target => l_target,
530 p_actual => l_actual,
531 p_wf_process => l_workflow_process,
532 p_role => l_notify_resp,
533 p_resp_id => l_responsibility_id,
534 p_report_name => l_report_name,
535 p_report_param => l_param,
536 x_return_status => status);
537
538 ELSE
539 -- if targets have not been set by the user then resp1 is notified
540 l_notify_resp := cr.Notify_Resp1_short_name;
541 l_responsibility_id := cr.Notify_Resp1_ID;
542 if cr.target_level_short_name in ('SHIPVALUE_TOP','SHIPVALUE_ALL') then
543 l_exception_message := 'A PMF target has been set to monitor Customer Satisfaction Percentages.' ||
544 ' This target has fallen short for the following :';
545 else
546 l_exception_message := 'A PMF target has been set to monitor Customer Satisfaction Percentages.' ||
547 ' We have exceeded the returns target for the following :';
548 end if;
549
550 oe_strt_wf_process(
551 p_exception_message => l_exception_message,
552 p_subject => l_subject,
553 p_sob => l_set_of_books_id,
554 p_area => l_area,
555 p_prod_cat => l_prod_catg,
556 p_sales_channel => l_sales_channel,
557 p_period => l_period,
558 p_target => l_target,
559 p_actual => l_actual,
560 p_wf_process => l_workflow_process,
561 p_role => l_notify_resp,
562 p_resp_id => l_responsibility_id,
563 p_report_name => l_report_name,
564 p_report_param => l_param,
565 x_return_status => status);
566
567 END IF;
568
569 -- We're more on target....
570 ELSIF actual > target THEN
571
572 -- Check if actual is within the first range
573 IF actual > target + cr.range1_high
574 THEN
575 l_notify_resp := cr.Notify_Resp1_short_name;
576 l_responsibility_id := cr.Notify_Resp1_ID;
577 if cr.target_level_short_name in ('SHIPVALUE_TOP','SHIPVALUE_ALL') then
578 l_exception_message := 'A PMF target has been set to monitor Customer Satisfaction Percentages.' ||
579 ' This target has been exceeded for the following :';
580 else
581 l_exception_message := 'A PMF target has been set to monitor Customer Satisfaction Percentages.' ||
582 ' We have done well to fall short of the allowed returns for the following :';
583 end if;
584 oe_strt_wf_process(
585 p_exception_message => l_exception_message,
586 p_subject => l_subject,
587 p_sob => l_set_of_books_id,
588 p_area => l_area,
589 p_prod_cat => l_prod_catg,
590 p_sales_channel => l_sales_channel,
591 p_period => l_period,
592 p_target => l_target,
593 p_actual => l_actual,
594 p_wf_process => l_workflow_process,
595 p_role => l_notify_resp,
596 p_resp_id => l_responsibility_id,
597 p_report_name => l_report_name,
598 p_report_param => l_param,
599 x_return_status => status);
600
601 -- Check if actual is within the second range
602 ELSIF actual > target + cr.range2_high
603 THEN
604 l_notify_resp := cr.Notify_Resp2_short_name;
605 l_responsibility_id := cr.Notify_Resp2_ID;
606 if cr.target_level_short_name in ('SHIPVALUE_TOP','SHIPVALUE_ALL') then
607 l_exception_message := 'A PMF target has been set to monitor Customer Satisfaction Percentages.' ||
608 ' This target has been exceeded for the following :';
609 else
610 l_exception_message := 'A PMF target has been set to monitor Customer Satisfaction Percentages.' ||
611 ' We have done well to fall short of the allowed returns for the following :';
612 end if;
613
614 oe_strt_wf_process(
615 p_exception_message => l_exception_message,
616 p_subject => l_subject,
617 p_sob => l_set_of_books_id,
618 p_area => l_area,
619 p_prod_cat => l_prod_catg,
620 p_sales_channel => l_sales_channel,
621 p_period => l_period,
622 p_target => l_target,
623 p_actual => l_actual,
624 p_wf_process => l_workflow_process,
625 p_role => l_notify_resp,
626 p_resp_id => l_responsibility_id,
627 p_report_name => l_report_name,
628 p_report_param => l_param,
629 x_return_status => status);
630
631 l_notify_resp := cr.Notify_Resp1_short_name;
632 l_responsibility_id := cr.Notify_Resp1_ID;
633
634
635 oe_strt_wf_process(
636 p_exception_message => l_exception_message,
637 p_subject => l_subject,
638 p_sob => l_set_of_books_id,
639 p_area => l_area,
640 p_prod_cat => l_prod_catg,
641 p_sales_channel => l_sales_channel,
642 p_period => l_period,
643 p_target => l_target,
644 p_actual => l_actual,
645 p_wf_process => l_workflow_process,
646 p_role => l_notify_resp,
647 p_resp_id => l_responsibility_id,
648 p_report_name => l_report_name,
649 p_report_param => l_param,
650 x_return_status => status);
651
652 -- Check if actual is within the third range
653 ELSIF actual > target + cr.range3_high
654 THEN
655 l_notify_resp := cr.Notify_Resp3_short_name;
656 l_responsibility_id := cr.Notify_Resp3_ID;
657 if cr.target_level_short_name in ('SHIPVALUE_TOP','SHIPVALUE_ALL') then
658 l_exception_message := 'A PMF target has been set to monitor Customer Satisfaction Percentages.' ||
659 ' This target has been exceeded for the following :';
660 else
661 l_exception_message := 'A PMF target has been set to monitor Customer Satisfaction Percentages.' ||
662 ' We have done well to fall short of the allowed returns for the following :';
663 end if;
664
665 oe_strt_wf_process(
666 p_exception_message => l_exception_message,
667 p_subject => l_subject,
668 p_sob => l_set_of_books_id,
669 p_area => l_area,
670 p_prod_cat => l_prod_catg,
671 p_sales_channel => l_sales_channel,
672 p_period => l_period,
673 p_target => l_target,
674 p_actual => l_actual,
675 p_wf_process => l_workflow_process,
676 p_role => l_notify_resp,
677 p_resp_id => l_responsibility_id,
678 p_report_name => l_report_name,
679 p_report_param => l_param,
680 x_return_status => status);
681
682
683 l_notify_resp := cr.Notify_Resp2_short_name;
684 l_responsibility_id := cr.Notify_Resp2_ID;
685
686 oe_strt_wf_process(
687 p_exception_message => l_exception_message,
688 p_subject => l_subject,
689 p_sob => l_set_of_books_id,
690 p_area => l_area,
691 p_prod_cat => l_prod_catg,
692 p_sales_channel => l_sales_channel,
693 p_period => l_period,
694 p_target => l_target,
695 p_actual => l_actual,
696 p_wf_process => l_workflow_process,
697 p_role => l_notify_resp,
698 p_resp_id => l_responsibility_id,
699 p_report_name => l_report_name,
700 p_report_param => l_param,
701 x_return_status => status);
702
703
704 l_notify_resp := cr.Notify_Resp1_short_name;
705 l_responsibility_id := cr.Notify_Resp1_ID;
706
707 oe_strt_wf_process(
708 p_exception_message => l_exception_message,
709 p_subject => l_subject,
710 p_sob => l_set_of_books_id,
711 p_area => l_area,
712 p_prod_cat => l_prod_catg,
713 p_sales_channel => l_sales_channel,
714 p_period => l_period,
715 p_target => l_target,
716 p_actual => l_actual,
717 p_wf_process => l_workflow_process,
718 p_role => l_notify_resp,
719 p_resp_id => l_responsibility_id,
720 p_report_name => l_report_name,
721 p_report_param => l_param,
722 x_return_status => status);
723
724
725 ELSE
726 -- if targets have not been set by the user then resp1 is notified
727 l_notify_resp := cr.Notify_Resp1_short_name;
728 l_responsibility_id := cr.Notify_Resp1_ID;
729 if cr.target_level_short_name in ('SHIPVALUE_TOP','SHIPVALUE_ALL') then
730 l_exception_message := 'A PMF target has been set to monitor Customer Satisfaction Percentages.' ||
731 ' This target has been exceeded for the following :';
732 else
733 l_exception_message := 'A PMF target has been set to monitor Customer Satisfaction Percentages.' ||
734 ' We have done well to fall short of the allowed returns for the following :';
735 end if;
736
737 oe_strt_wf_process(
738 p_exception_message => l_exception_message,
739 p_subject => l_subject,
740 p_sob => l_set_of_books_id,
741 p_area => l_area,
742 p_prod_cat => l_prod_catg,
743 p_sales_channel => l_sales_channel,
744 p_period => l_period,
745 p_target => l_target,
746 p_actual => l_actual,
747 p_wf_process => l_workflow_process,
748 p_role => l_notify_resp,
749 p_resp_id => l_responsibility_id,
750 p_report_name => l_report_name,
751 p_report_param => l_param,
752 x_return_status => status);
753
754 END IF;
755 END IF;
756
757 END LOOP; -- ends loop to check targets
758
759 EXCEPTION
760 WHEN OTHERS THEN
761 l_message := 'Exception in Alert Procedure.';
762
763 END PROCESS_ALERTS;
764
765 -- Calls the BIS post_actual API
766
767 PROCEDURE postactual( target_level_id in number,
768 org_level_value in varchar2,
769 time_level_value in varchar2,
770 dimension1_level_value In varchar2,
771 dimension2_level_value in varchar2,
772 dimension3_level_value in varchar2,
773 actual in number,
774 period_set_name in varchar2) IS
775
776 actual_rec BIS_ACTUAL_PUB.Actual_Rec_Type;
777 x_return_status VARCHAR2(30);
778 x_msg_count NUMBER;
779 x_msg_data VARCHAR2(30);
780 x_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
781
782 BEGIN
783 actual_rec.Target_Level_ID := target_level_id;
784 actual_rec.Time_Level_Value_ID := time_level_value;
785 actual_rec.Org_Level_value_ID := org_level_value;
786 actual_rec.Dim1_Level_Value_ID := dimension1_level_value;
787 actual_rec.Dim2_Level_Value_ID := dimension2_level_value;
788 actual_rec.Dim3_Level_Value_ID := dimension3_level_value;
789 actual_rec.Actual := actual;
790
791 BIS_ACTUAL_PUB.Post_Actual( p_api_version => 1,
792 p_commit => FND_API.G_TRUE,
793 p_Actual_Rec => actual_rec,
794 x_return_status => x_return_status,
795 x_msg_count => x_msg_count,
796 x_msg_data => x_msg_data,
797 x_error_Tbl => x_error_Tbl);
798
799
800 END PostActual;
801
802 -- starting the Oracle Workflow Builder process to initiate the workflow notification process.
803
804 PROCEDURE oe_strt_wf_process(
805 p_exception_message IN varchar2,
806 p_subject IN varchar2,
807 p_sob IN varchar2,
808 p_area IN varchar2,
809 p_prod_cat IN varchar2,
810 p_sales_channel IN varchar2,
811 p_period IN varchar2,
812 p_target IN varchar2,
813 p_actual IN varchar2,
814 p_wf_process IN varchar2,
815 p_role IN varchar2,
816 p_resp_id IN number,
817 p_report_name IN varchar2,
818 p_report_param IN varchar2,
819 x_return_status OUT varchar2
820 ) IS
821 l_wf_item_key Number;
822 l_item_type Varchar2(30) := 'OEBISWF';
823 l_report_link Varchar2(500);
824 l_role_name Varchar2(80);
825 l_url1 Varchar2(2000);
826
827 cursor c_role_name is
828 select name from wf_roles
829 where name = p_role;
830
831 BEGIN
832
833
834 x_return_status := FND_API.G_RET_STS_SUCCESS;
835 if p_wf_process is null
836 or p_role is null then
837 x_return_status := FND_API.G_RET_STS_ERROR;
838 return;
839 end if;
840
841 open c_role_name;
842 fetch c_role_name into l_role_name;
843 if c_role_name%NOTFOUND then
844 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
845 return;
846 end if;
847
848 select bis_excpt_wf_s.nextval
849 into l_wf_item_key
850 from dual;
851
852 l_report_link := FND_PROFILE.value('ICX_REPORT_LINK');
853
854 if p_report_name is not null then
855 l_url1 := l_report_link || 'OracleOASIS.RunReport?report='|| p_report_name|| '¶meters='
856 || p_report_param || '&responsibility_id=' || p_resp_id;
857 end if;
858
859
860 -- create a new workflow process
861 wf_engine.CreateProcess(itemtype=>l_item_type
862 ,itemkey =>l_wf_item_key
863 ,process =>p_wf_process);
864
865 -- set the workflow attributes
866 wf_engine.SetItemAttrText(itemtype=>l_item_type
867 ,itemkey =>l_wf_item_key
868 ,aname=>'L_ROLE_NAME'
869 ,avalue=>L_ROLE_NAME);
870 wf_engine.SetItemAttrText(itemtype=>l_item_type
871 ,itemkey =>l_wf_item_key
872 ,aname=>'L_EXCEPTION_MESSAGE'
873 ,avalue=>p_exception_message);
874 wf_engine.SetItemAttrText(itemtype=>l_item_type
875 ,itemkey =>l_wf_item_key
876 ,aname=>'L_SUBJECT'
877 ,avalue=>p_subject);
878 wf_engine.SetItemAttrText(itemtype=>l_item_type
879 ,itemkey =>l_wf_item_key
880 ,aname=>'L_SOB'
881 ,avalue=>p_sob);
882 wf_engine.SetItemAttrText(itemtype=>l_item_type
883 ,itemkey =>l_wf_item_key
884 ,aname=>'L_AREA'
885 ,avalue=>p_area);
886 wf_engine.SetItemAttrText(itemtype=>l_item_type
887 ,itemkey =>l_wf_item_key
888 ,aname=>'L_PROD_CAT'
889 ,avalue=>p_prod_cat);
890 wf_engine.SetItemAttrText(itemtype=>l_item_type
891 ,itemkey =>l_wf_item_key
892 ,aname=>'L_SALES_CHANNEL'
893 ,avalue=>p_sales_channel);
894 wf_engine.SetItemAttrText(itemtype=>l_item_type
895 ,itemkey =>l_wf_item_key
896 ,aname=>'L_PERIOD'
897 ,avalue=>p_period);
898 wf_engine.SetItemAttrText(itemtype=>l_item_type
899 ,itemkey =>l_wf_item_key
900 ,aname=>'L_TARGET'
901 ,avalue=>p_target);
902 wf_engine.SetItemAttrText(itemtype=>l_item_type
903 ,itemkey =>l_wf_item_key
904 ,aname=>'L_ACTUAL'
905 ,avalue=>p_actual);
906 if l_url1 is not null then
907 wf_engine.SetItemAttrText(itemtype=>l_item_type
908 ,itemkey =>l_wf_item_key
909 ,aname=>'L_URL1'
910 ,avalue=>l_url1);
911 end if;
912
913 -- start the process
914 wf_engine.StartProcess(itemtype=>l_item_type
915 ,itemkey => l_wf_item_key);
916
917 END oe_strt_wf_process;
918
919
920 END OE_BIS_ALERTS;