[Home] [Help]
PACKAGE BODY: APPS.BIS_CORRECTIVE_ACTION_PVT
Source
1 PACKAGE BODY BIS_CORRECTIVE_ACTION_PVT AS
2 /* $Header: BISVCACB.pls 120.0 2005/06/01 14:57:15 appldev noship $ */
3 /*
4 REM +=======================================================================+
5 REM | Copyright (c) 1998 Oracle Corporation, Redwood Shores, CA, USA |
6 REM | All rights reserved. |
7 REM +=======================================================================+
8 REM | FILENAME |
9 REM | BISVCACB.pls |
10 REM | |
11 REM | DESCRIPTION |
12 REM | Private API for the Corrective Action
13 REM |
14 REM | NOTES |
15 REM | |
16 REM | HISTORY |
17 REM | APR-2000 irchen Creation |
18 REM | OCT-2000 hhchi JSP notification to Workflow Notification |
19 REM | JAN-2001 hhchi fix bug#1567627 in Get_Result_Owners |
20 REM | DEC-2001 rchandra fix bug#1880142,1736261 for alert messgs |
21 REM | JAN-2002 sashaik fix for enhancement 2164190. Modified procedure |
22 REM | send_alert to get report url from bis_actual_values |
23 REM | table. |
24 REM | JAN-2002 sashaik Made changes w.r.t the following procs for 1740789:|
25 REM | Retrieve_Org_level_value, Is_Previous_Time_Period |
26 REM | Is_Current_Time_Period, Get_Time_From, Get_Time_To|
27 REM | 17-JUN-2002 rchandra added bis_utilities_pub.encode | --
28 REM | for encoding for bug 2418741 |
29 REM | NOV-15 sashaik Unsubscribe alerts 1898436
30 REM | DEC-12 sashaik 2684836
31 REM | 23-JAN-03 mahrao For having different local variables for IN and OUT
32 REM | parameters.
33 REM | MAR-03 sashaik 2837974
34 REM | 09-APR-03 smuruges Bug# 2871017 |
35 REM | - Removed the html tags that were set in the |
36 REM | item attributes. |
37 REM | - For the following attributes the HTML tags |
38 REM | cannot be removed. Hence, used the following |
39 REM | new item attributes and set the values devoid |
40 REM | of tags in these attributes. |
41 REM | BIS_DIMENSION_REGION : |
42 REM | BIS_DIMENSION_REGION_TXT |
43 REM | L_RECEPIENT : L_RECEPIENT_TXT |
44 REM | L_ALERT_SCHEDULE_MSG : |
45 REM | L_ALERT_SCHEDULE_MSG_TXT |
46 REM | - The API GenerateAlert is set to the Document |
47 REM | item attribute BIS_ALERT_DOC. |
48 REM | - Defined the package body for the procedure |
49 REM | GenerateAlert. |
50 REM | 21-APR-03 mahrao Bug# 2905588 |
51 REM | - A new text attribute L_RELATED_INFORAMTION_TXT|
52 REM | has been used in text text messages section of|
53 REM | GenerateAlerts procedure. |
54 REM | Tokens are passed for following messages |
55 REM | 1. BIS_ALERT_SCHEDULE_MSG2 |
56 REM | 2. BIS_ALERT_SCHEDULE_MSG1 |
57 REM | 05-JUL-03 rchandra Bug# 2929282 |
58 REM | Unsubscribe link will be generated only for |
59 REM | subscribers and not for owners of the Target |
60 REM | Removed the unwanted Functions |
61 REM | isRoleTargetOwner and isTargetOwner |
62 REM | 07-NOV-03 ankgoel Added conversion for ATG Timezone Project |
63 REM | 14-NOV-03 ankgoel Modified for bug# 3153918 |
64 REM | 10-DEC-03 ankgoel Modified for bug# 3309374. To comply |
65 REM | BLAF standards. |
66 REM | 15-Dec-2003 arhegde enh# 3148615 Change/Target based alerting. |
67 REM | 09-Jan-2004 ankgoel Modified for WF_HEADER_ATTR for Measure |
68 REM | 14-Jan-2004 jxyu Modified for bug#3374352 |
69 REM | 25-Jan-2004 ankgoel Modified Next Run Date format for bug#3083617 |
70 REM | 11-Feb-2004 gramasam Blaf Enhancement for TwoCol Layout |
71 REM | 10-Jun-2004 ankgoel Modified for OAC violations |
72 REM | 10-Sep-2004 rpenneru Modified for bug#3611608 |
73 REM | 27-Oct-2004 ankgoel Modified for bug#3651600 |
74 REM +=======================================================================+
75 */
76
77 G_PKG_NAME CONSTANT VARCHAR2(30):= 'BIS_CORRECTIVE_ACTION_PVT';
78 G_BREAK CONSTANT VARCHAR2(200) := '<BR>';
79 G_RPT_ERROR CONSTANT VARCHAR2(200) := 'DEFAULT URL';
80 G_NOTIFICATION_JSP_PAGE CONSTANT VARCHAR2(200) := 'bisalrbk.jsp';
81
82 --
83 -- Procedures
84 --
85
86 PROCEDURE Get_User_List_From_Role -- 2684836
87 ( p_recipient_short_name IN VARCHAR2
88 , x_user_tbl OUT NOCOPY wf_directory.UserTable
89 , x_return_status OUT NOCOPY VARCHAR2
90 , x_return_msg OUT NOCOPY VARCHAR2
91 ) ;
92
93 FUNCTION Get_Line RETURN VARCHAR2;
94
95 FUNCTION Get_Style_Class RETURN VARCHAR2;
96
97 FUNCTION Get_Header
98 ( p_item_type IN VARCHAR2
99 ,p_wf_item_key IN WF_ITEM_ATTRIBUTE_VALUES.ITEM_KEY%TYPE
100 ,p_attribute_name IN VARCHAR2
101 ) RETURN VARCHAR2 ;
102
103 FUNCTION Get_Label
104 ( p_item_type IN VARCHAR2
105 ,p_wf_item_key IN WF_ITEM_ATTRIBUTE_VALUES.ITEM_KEY%TYPE
106 ,p_attribute_name IN VARCHAR2
107 ) RETURN VARCHAR2 ;
108
109 FUNCTION Get_Text
110 ( p_item_type IN VARCHAR2
111 ,p_wf_item_key IN WF_ITEM_ATTRIBUTE_VALUES.ITEM_KEY%TYPE
112 ,p_attribute_name IN VARCHAR2
113 ) RETURN VARCHAR2 ;
114
115 FUNCTION Get_Servlet_Agent RETURN VARCHAR2; /* returns the servlet agent dir */
116 FUNCTION getDate RETURN VARCHAR2; /* returns formated send date */
117
118 --FUNCTION Get_Display_Unit(p_unit_code IN VARCHAR2) RETURN VARCHAR2;
119
120 FUNCTION getAdHocRole
121 ( p_Alert_recipients_tbl IN BIS_UTILITIES_PUB.BIS_VARCHAR_TBL )RETURN VARCHAR2;
122 FUNCTION Format_Message(p_message_tbl IN BIS_UTILITIES_PUB.BIS_VARCHAR_TBL)
123 RETURN VARCHAR2;
124
125 FUNCTION Generate_parameter_string
126 ( p_target_id IN VARCHAR2
127 , p_comparison_result IN VARCHAR2
128 , p_role IN VARCHAR2
129 , p_date IN VARCHAR2
130 , p_schedule_date IN VARCHAR2
131 , P_schedule_time IN VARCHAR2
132 , p_schedule_freq_unit IN VARCHAR2
133 , p_next_run_date IN VARCHAR2
134 , p_next_run_time IN VARCHAR2
135 , p_description IN VARCHAR2
136 ) RETURN VARCHAR2;
137
138 Procedure Get_Performance_Measure_Msg
139 ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
140 , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
141 , p_comparison_result IN VARCHAR2
142 , x_message_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
143 );
144
145 Procedure Get_Message_Banner
146 ( p_Sent_Date IN VARCHAR2
147 , p_Item IN VARCHAR2
148 , p_to IN VARCHAR2
149 , x_Message_Banner_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
150 );
151
152 Procedure Get_Message_Intro
153 ( p_Sent_Date IN VARCHAR2
154 , x_Message_Intro_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
155 );
156
157 Procedure Get_Message_Body
158 ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
159 , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
160 , p_comparison_result IN VARCHAR2
161 , x_message_body_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
162 );
163
164 Procedure Get_Alert_Information
165 ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
166 , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
167 , x_Alert_Information_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
168 );
169
170 Procedure Get_Related_Links
171 ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
172 , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
173 , x_Related_Links_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
174 );
175
176 Procedure Get_Report_Attachement
177 ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
178 , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
179 , x_attachement_url_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
180 );
181
182 Procedure Get_Alert_Recipients
183 ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
184 , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
185 , p_comparison_result IN VARCHAR2
186 , x_Alert_recipients_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
187 , x_Alert_recipients_sh_nm_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
188 , x_number_out_of_range OUT NOCOPY NUMBER
189 );
190
191 Procedure Get_Alert_Message
192 ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
193 , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
194 , x_Alert_Message_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
195 );
196
197 Procedure Set_Message
198 ( p_message_tbl IN BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
199 , x_return_status OUT NOCOPY VARCHAR2
200 );
201
202 Function Get_Role
203 ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
204 , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
205 , p_comparison_result IN VARCHAR2
206 ) RETURN VARCHAR2 ;
207
208 PROCEDURE Get_Result_Owners
209 ( p_target_Owners_rec IN BIS_TARGET_PUB.Target_Owners_Rec_Type
210 , p_comparison_result IN VARCHAR2
211 , x_owners_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
212 , x_owners_sh_nm_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
213 , x_number_out_of_range OUT NOCOPY NUMBER
214 );
215
216 Procedure Get_Workflow_Info
217 ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
218 , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
219 , p_comparison_result IN VARCHAR2
220 , x_item_type OUT NOCOPY VARCHAR2
221 , x_process OUT NOCOPY VARCHAR2
222 );
223
224 -- Starts the corrective action workflow
225 --
226 Procedure Start_Corrective_Action
227 ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
228 , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
229 , p_comparison_result IN VARCHAR2
230 )
231 IS
232
233 l_item_type VARCHAR2(32000);
234 l_process VARCHAR2(32000);
235 l_Alert_recipients_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
236 l_Alert_recipients_sh_nm_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
237 l_target_level_rec BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type;
238
239 l_message VARCHAR2(32000);
240 l_message_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
241 l_schedule_date VARCHAR2(32000);
242 l_schedule_time VARCHAR2(32000);
243 l_schedule_unit VARCHAR2(32000);
244 l_schedule_freq VARCHAR2(32000);
245 l_schedule_freq_unit VARCHAR2(32000);
246 l_next_run_date VARCHAR2(32000);
247 l_next_run_time VARCHAR2(32000);
248 l_description VARCHAR2(32000);
249 l_date VARCHAR2(32000);
250 l_number_out_of_range NUMBER;
251
252 l_return_status VARCHAR2(32000);
253
254 BEGIN
255
256 --BIS_UTILITIES_PUB.put_line(p_text =>'Starting corrective action workflow');
257 Get_Request_Info
258 ( p_measure_instance => p_measure_instance
259 , x_schedule_date => l_schedule_date
260 , x_schedule_time => l_schedule_time
261 , x_schedule_unit => l_schedule_unit
262 , x_schedule_freq => l_schedule_freq
263 , x_next_run_date => l_next_run_date
264 , x_next_run_time => l_next_run_time
265 , x_description => l_description
266 , x_return_status => l_return_status
267 );
268
269 Get_Alert_Recipients
270 ( p_measure_instance => p_measure_instance
271 , p_dim_level_value_tbl => p_dim_level_value_tbl
272 , p_comparison_result => p_comparison_result
273 , x_Alert_recipients_tbl => l_Alert_recipients_tbl
274 , x_Alert_recipients_sh_nm_tbl => l_Alert_recipients_sh_nm_tbl
275 , x_number_out_of_range => l_number_out_of_range
276 );
277
278 Get_Workflow_Info
279 ( p_measure_instance => p_measure_instance
280 , p_dim_level_value_tbl => p_dim_level_value_tbl
281 , p_comparison_result => p_comparison_result
282 , x_item_type => l_item_type
283 , x_process => l_process
284 );
285
286 l_date := getDate;
287
288 Get_Performance_Measure_Msg
289 ( p_measure_instance => p_measure_instance
290 , p_dim_level_value_tbl => p_dim_level_value_tbl
291 , p_comparison_result => p_comparison_result
292 , x_message_tbl => l_message_tbl
293 );
294
295 l_message := Format_Message(p_message_tbl => l_message_tbl);
296
297 --BIS_UTILITIES_PUB.put_line(p_text =>'Corrective action message: '||l_message);
298
299 FOR i IN 1..l_Alert_recipients_sh_nm_tbl.COUNT LOOP
300 BIS_UTIL.Start_Workflow_Engine
301 ( p_exception_message => l_message
302 , p_msg_subject => l_description
303 , p_exception_date => l_date
304 , p_item_type => l_item_type
305 , p_wf_process => l_process
306 , p_notify_resp_name => l_Alert_recipients_sh_nm_tbl(i) -- 2628529
307 , x_return_status => l_return_status
308 );
309
310 BIS_UTILITIES_PUB.put_line(p_text =>'Started workflow Process '||i||'. item type: '||l_item_type
311 ||', notified: '||l_Alert_recipients_tbl(i) || ' Status ' || nvl(l_return_status, 'x') );
312
313 commit;
314 END LOOP;
315
316 END Start_Corrective_Action;
317
318
319
320
321 -- Sends the Alert notification
322 --
323 Procedure Send_Alert
324 ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
325 , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
326 , p_comparison_result IN VARCHAR2
327 )
328 IS
329
330 l_wf_item_key NUMBER;
331 l_item_type VARCHAR2(32000)
332 := BIS_CORRECTIVE_ACTION_PUB.G_BIS_GEN_WORKFLOW_ITEM_TYPE;
333 l_process VARCHAR2(32000)
334 := BIS_CORRECTIVE_ACTION_PUB.G_BIS_ALR_WORKFLOW_PROCESS;
335
336 l_Alert_recipients_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
337 l_Alert_recipients_sh_nm_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
338
339 l_document_type_value VARCHAR2(32000);
340 l_message varchar2(32000);
341 l_document_type varchar2(32000);
342 l_return_status varchar2(32000);
343 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
344 l_date VARCHAR2(32000);
345 l_role VARCHAR2(32000);
346 l_schedule_date VARCHAR2(32000);
347 l_schedule_time VARCHAR2(32000);
348 l_schedule_unit VARCHAR2(32000);
349 l_schedule_freq VARCHAR2(32000);
350 l_schedule_freq_unit VARCHAR2(32000);
351 l_next_run_date VARCHAR2(32000);
352 l_next_run_time VARCHAR2(32000);
353 l_description VARCHAR2(32000);
354 l_alert_details VARCHAR2(32000);
355 l_alert_details1 VARCHAR2(32000);
356 l_label VARCHAR2(32000);
357 l_label1 VARCHAR2(32000);
358 l_target_rec BIS_TARGET_PUB.Target_rec_type;
359 l_actual_rec BIS_ACTUAL_PUB.Actual_rec_type;
360 l_actual_rec1 BIS_ACTUAL_PUB.Actual_rec_type; -- 2164190 sashaik
361 l_dimension_level_rec BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
362 l_dim_level_value_rec BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type;
363 l_target_level_rec BIS_TARGET_LEVEL_PUB.TARGET_LEVEL_REC_TYPE;
364 l_measure_rec BIS_MEASURE_PUB.Measure_Rec_Type;
365
366 l_Dim_Level_Value_Rec_oltp BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type;
367 l_dimension_level_number_oltp NUMBER;
368 l_Org_Level_Value_ID VARCHAR2(40); -- := '204';
369 l_Org_Level_Short_name VARCHAR2(40);
370
371 l_msg VARCHAR2(32000);
372 l_msg1 VARCHAR2(32000);
373 l_link VARCHAR2(32000);
374 l_alert_details_label VARCHAR2(32000);
375 l_sequence_no NUMBER;
376 l_time_level_id NUMBER;
377 l_time_level_short_name VARCHAR2(32000);
378 l_time_level_name VARCHAR2(32000);
379 l_from VARCHAR2(32000);
380 l_to VARCHAR2(32000);
381 l_many NUMBER;
382 l_dim_level_value_tbl BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type;
383 l_dimlevel_short_name VARCHAR2(32000);
384 l_select_String VARCHAR2(32000);
385 l_table_name VARCHAR2(32000);
386 l_value_name VARCHAR2(32000);
387 l_id_name VARCHAR2(32000);
388 l_level_name VARCHAR2(32000);
389 l_msg_Count NUMBER;
390 l_msg_data VARCHAR2(32000);
391
392 l_Parm1Level_short_name VARCHAR2(240);
393 l_Parm1Value_name VARCHAR2(240);
394 l_Parm2Level_short_name VARCHAR2(240);
395 l_Parm2Value_name VARCHAR2(240);
396 l_Parm3Level_short_name VARCHAR2(240);
397 l_Parm3Value_name VARCHAR2(240);
398 l_Parm4Level_short_name VARCHAR2(240);
399 l_Parm4Value_name VARCHAR2(240);
400 l_Parm5Level_short_name VARCHAR2(240);
401 l_Parm5Value_name VARCHAR2(240);
402 l_Parm6Level_short_name VARCHAR2(240);
403 l_Parm6Value_name VARCHAR2(240);
404 l_Parm7Level_short_name VARCHAR2(240);
405 l_Parm7Value_name VARCHAR2(240);
406 l_param_count NUMBER;
407
408 l_formatted_actual varchar2(300);
409
410 l_number_out_of_range NUMBER;
411
412 TYPE t_dim_Value IS TABLE OF VARCHAR2(32000)
413 INDEX BY BINARY_INTEGER;
414 TYPE t_dim_value_level IS TABLE OF VARCHAR2(32000)
415 INDEX BY BINARY_INTEGER;
416 TYPE t_dim_level_input IS TABLE OF VARCHAR2(32000)
417 INDEX BY BINARY_INTEGER;
418 TYPE t_dim_value_input IS TABLE OF VARCHAR2(32000)
419 INDEX BY BINARY_INTEGER;
420
421 v_dim_level_input t_dim_level_input;
422 v_dim_value_input t_dim_value_input;
423 v_dim_value_level t_dim_value_level;
424 v_dim_level t_dim_value_level;
425 v_dim_value t_dim_value;
426
427 l_rolling_period_end_date DATE;
428 l_rolling_period_start_date DATE;
429 l_is_rolling_period NUMBER;
430 l_start_label VARCHAR2(30);
431 l_end_label VARCHAR2(30);
432
433 l_unscubscribe_url VARCHAR2(32000) := NULL; -- begin 1898436
434 l_notifiers_code bis_pmf_alert_parameters.notifiers_code%TYPE;
435 l_Param_Set_Rec BIS_PMF_ALERT_REG_PUB.parameter_set_rec_type; -- end 1898436
436
437 l_wf_user_table wf_directory.UserTable; -- 2684836
438 l_return_message VARCHAR2(32000);
439 k NUMBER;
440
441 l_dimension_level_rec_p BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
442 l_dim_level_value_rec_p BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type;
443 l_target_level_rec_p BIS_TARGET_LEVEL_PUB.TARGET_LEVEL_REC_TYPE;
444 l_measure_rec_p BIS_MEASURE_PUB.Measure_Rec_Type;
445
446 ll_message VARCHAR2(500);
447
448 l_NL VARCHAR2(1) := fnd_global.newline;
449
450 BEGIN
451
452
453 BIS_UTILITIES_PUB.put_line(p_text =>' ........... START: Notification email ........... ');
454
455 Get_Request_Info
456 ( p_measure_instance => p_measure_instance
457 , x_schedule_date => l_schedule_date
458 , x_schedule_time => l_schedule_time
459 , x_schedule_unit => l_schedule_unit
460 , x_schedule_freq => l_schedule_freq
461 , x_next_run_date => l_next_run_date
462 , x_next_run_time => l_next_run_time
463 , x_description => l_description
464 , x_return_status => l_return_status
465 );
466
467 BIS_CONCURRENT_MANAGER_PVT.Format_Schedule_Freq_Unit
468 ( p_schedule_unit => l_schedule_unit
469 , p_schedule_freq => l_schedule_freq
470 , x_schedule_freq_unit => l_schedule_freq_unit
471 );
472
473 Get_Alert_Recipients
474 ( p_measure_instance => p_measure_instance
475 , p_dim_level_value_tbl => p_dim_level_value_tbl
476 , p_comparison_result => p_comparison_result
477 , x_Alert_recipients_tbl => l_Alert_recipients_tbl
478 , x_Alert_recipients_sh_nm_tbl => l_Alert_recipients_sh_nm_tbl
479 , x_number_out_of_range => l_number_out_of_range
480 );
481
482 /*
483 BIS_UTILITIES_PUB.put_line(p_text => ' recip count = ' || l_Alert_recipients_tbl.count );
484 BIS_UTILITIES_PUB.put_line(p_text => ' Recipt count = ' || l_Alert_recipients_sh_nm_tbl.count );
485
486 for i in 1..l_Alert_recipients_tbl.count loop
487 BIS_UTILITIES_PUB.put_line(p_text => ' alert recip i = ' || i || ' val = ' || l_Alert_recipients_tbl(i) );
488 end loop;
489
490 for i in 1..l_Alert_recipients_sh_nm_tbl.count loop
491 BIS_UTILITIES_PUB.put_line(p_text => ' Alert recipt i = ' || i || ' val = ' || l_Alert_recipients_sh_nm_tbl(i) );
492 end loop;
493 */
494
495 l_measure_rec.measure_id := p_measure_instance.measure_id;
496
497 l_measure_rec_p := l_measure_rec;
498 BIS_MEASURE_PUB.Retrieve_Measure
499 ( p_api_version => 1.0
500 , p_measure_rec => l_measure_rec_p
501 , x_measure_rec => l_measure_rec
502 , x_return_status => l_return_status
503 , x_error_tbl => l_error_tbl
504 );
505
506 --BIS_UTILITIES_PUB.put_line(p_text =>'Performance Measure: '||l_measure_rec.measure_short_name);
507 BIS_PMF_DATA_SOURCE_PUB.Retrieve_Target
508 ( p_measure_instance => p_measure_instance
509 , p_dim_level_value_tbl => p_dim_level_value_tbl
510 , x_target_rec => l_target_rec
511 );
512
513 --BIS_UTILITIES_PUB.put_line(p_text =>'Retrive_Target: '||l_target_rec.Target_Id);
514
515 l_target_level_rec.target_level_id := l_target_rec.target_level_id;
516
517 l_target_level_rec.measure_id := l_measure_rec.measure_id;
518
519 l_target_level_rec_p := l_target_level_rec;
520 BIS_TARGET_LEVEL_PUB.Retrieve_target_level
521 (p_api_version => 1.0
522 ,p_target_level_rec => l_target_level_rec_p
523 ,x_target_level_rec => l_target_level_rec
524 ,x_return_status => l_return_status
525 ,x_error_tbl => l_error_tbl);
526
527 BIS_PMF_DEFINER_WRAPPER_PVT.Get_Time_Level_Id
528 ( p_performance_measure_id => l_measure_rec.measure_id
529 , p_target_level_id => l_target_rec.Target_Level_Id
530 , x_sequence_no => l_sequence_no
531 , x_dim_level_id => l_time_level_id
532 , x_dim_level_short_name => l_time_level_short_name
533 , x_dim_level_name => l_time_level_name
534 , x_return_status => l_return_status
535 );
536
537
538 l_dim_level_value_tbl := p_dim_level_value_tbl;
539 For i IN 1..7 Loop
540 l_dim_level_value_rec.Dimension_Level_Value_id := p_dim_level_value_tbl(i).Dimension_Level_Value_id;
541 if (i=1) then
542 l_dim_level_value_rec.dimension_level_id := l_target_level_rec.dimension1_level_id;
543 l_dimension_level_rec.dimension_level_id := l_target_level_rec.dimension1_level_id;
544 l_dim_level_value_tbl(i).dimension_level_id := l_dim_level_value_rec.dimension_level_id;
545 end if;
546 if (i=2) then
547 l_dim_level_value_rec.dimension_level_id := l_target_level_rec.dimension2_level_id;
548 l_dimension_level_rec.dimension_level_id := l_target_level_rec.dimension2_level_id;
549 l_dim_level_value_tbl(i).dimension_level_id := l_dim_level_value_rec.dimension_level_id;
550 end if;
551 if (i=3) then
552 l_dim_level_value_rec.dimension_level_id := l_target_level_rec.dimension3_level_id;
553 l_dimension_level_rec.dimension_level_id := l_target_level_rec.dimension3_level_id;
554 l_dim_level_value_tbl(i).dimension_level_id := l_dim_level_value_rec.dimension_level_id;
555 end if;
556 if (i=4) then
557 l_dim_level_value_rec.dimension_level_id := l_target_level_rec.dimension4_level_id;
558 l_dimension_level_rec.dimension_level_id := l_target_level_rec.dimension4_level_id;
559 l_dim_level_value_tbl(i).dimension_level_id := l_dim_level_value_rec.dimension_level_id;
560 end if;
561 if (i=5) then
562 l_dim_level_value_rec.dimension_level_id := l_target_level_rec.dimension5_level_id;
563 l_dimension_level_rec.dimension_level_id := l_target_level_rec.dimension5_level_id;
564 l_dim_level_value_tbl(i).dimension_level_id := l_dim_level_value_rec.dimension_level_id;
565 end if;
566 if (i=6) then
567 l_dim_level_value_rec.dimension_level_id := l_target_level_rec.dimension6_level_id;
568 l_dimension_level_rec.dimension_level_id := l_target_level_rec.dimension6_level_id;
569 l_dim_level_value_tbl(i).dimension_level_id := l_dim_level_value_rec.dimension_level_id;
570 end if;
571 if (i=7) then
572 l_dim_level_value_rec.dimension_level_id := l_target_level_rec.dimension7_level_id;
573 l_dimension_level_rec.dimension_level_id := l_target_level_rec.dimension7_level_id;
574 l_dim_level_value_tbl(i).dimension_level_id := l_dim_level_value_rec.dimension_level_id;
575 end if;
576
577
578 IF (l_dim_level_value_rec.Dimension_Level_value_Id IS NOT NULL) then
579
580 l_dimension_level_rec_p := l_dimension_level_rec;
581 BIS_DIMENSION_LEVEL_PVT.Retrieve_Dimension_Level
582 ( p_api_version => 1.0
583 , p_dimension_level_rec => l_dimension_level_rec_p
584 , x_dimension_level_rec => l_dimension_level_rec
585 , x_return_status => l_return_status
586 , x_error_tbl => l_error_tbl
587 );
588
589 v_dim_value_level(i) := l_dimension_level_rec.Dimension_Level_Name;
590
591 v_dim_level(i) := l_dimension_level_rec.Dimension_Level_Short_Name;
592
593 l_dim_level_value_rec_p := l_dim_level_value_rec;
594 BIS_DIM_LEVEL_VALUE_PVT.DimensionX_ID_To_Value
595 ( p_api_version => 1.0
596 , p_dim_level_value_rec => l_dim_level_value_rec_p
597 , x_dim_level_value_rec => l_dim_level_value_rec
598 , x_return_status => l_return_status
599 , x_error_tbl => l_error_tbl
600 );
601
602 v_dim_value(i) := l_dim_level_value_rec.Dimension_Level_Value_Name;
603
604 l_many := i;
605
606 End If;
607
608 End Loop;
609
610
611 BIS_PMF_DATA_SOURCE_PUB.Retrieve_Actual
612 ( p_measure_instance => p_measure_instance
613 , p_dim_level_value_tbl => l_dim_level_value_tbl
614 , x_actual_rec => l_actual_rec
615 );
616
617 BIS_UTILITIES_PUB.put_line(p_text =>'Retrieve_Actual: '||l_actual_rec.Actual);
618
619
620
621 begin -- Begin addition 2164190 sashaik
622
623 bis_actual_pvt.Retrieve_Actual
624 ( p_api_version => 1.0
625 ,p_all_info => 'F'
626 ,p_Actual_Rec => l_actual_rec
627 ,x_Actual_Rec => l_actual_rec1
628 ,x_return_Status => l_return_Status
629 ,x_error_tbl => l_error_tbl
630 );
631
632 l_formatted_actual := bis_indicator_region_ui_pvt.getAKFormatValue
633 ( p_measure_id => l_measure_rec.measure_id
634 , p_val => l_actual_rec.Actual
635 );
636
637 l_link := l_actual_rec1.Report_URL;
638
639 exception
640 when others then
641 BIS_UTILITIES_PUB.put_line(p_text =>'Error in Procedure BISVIEWER.GET_NOTIFY_RPT_URL : '||sqlerrm);
642
643 end; -- End addition 2164190 sashaik
644
645 BIS_UTILITIES_PUB.put_line(p_text =>'Link: '||l_link);
646
647
648
649 l_role := getAdHocRole(l_Alert_recipients_tbl);
650 l_date := sysdate; --getDate;
651 l_document_type_value
652 := Generate_parameter_string
653 ( p_target_id => p_measure_instance.target_id
654 , p_comparison_result => p_comparison_result
655 , p_role => l_role
656 , p_date => l_date
657 , p_schedule_date => l_schedule_date
658 , P_schedule_time => l_schedule_time
659 , p_schedule_freq_unit => l_schedule_freq_unit
660 , p_next_run_date => l_next_run_date
661 , p_next_run_time => l_next_run_time
662 , p_description => l_description
663 );
664
665 -- Generate_Alert_Message
666 -- ( document_id => l_document_type_value
667 -- , display_type => 'TEST_DISPLAY'
668 -- , document => l_message
669 -- , document_type => l_document_type
670 -- );
671
672 -- BIS_UTILITIES_PUB.put_line(p_text =>'Passing parameters length: '||length(l_document_type_value));
673 -- BIS_UTILITIES_PUB.put_line(p_text =>'Passing parameters: '||substr(l_document_type_value,0,200));
674 -- BIS_UTILITIES_PUB.put_line(p_text =>'generated message: '||substr(l_message,0,200));
675
676 FOR i IN 1..l_Alert_recipients_tbl.COUNT LOOP
677
678 BIS_UTILITIES_PUB.put_line(p_text =>'Starting item type: '||l_item_type||', process: '||l_process);
679
680 SELECT bis_excpt_wf_s.nextval
681 INTO l_wf_item_key
682 FROM dual;
683
684 BIS_UTILITIES_PUB.put_line(p_text =>'wf_item_key: '||l_wf_item_key);
685 --BIS_UTILITIES_PUB.put_line(p_text =>'document_type_value: '||l_document_type_value);
686
687 -- create a new workflow process
688 --
689
690 wf_engine.CreateProcess
691 ( itemtype => l_item_type
692 , itemkey => l_wf_item_key
693 , process => l_process
694 );
695
696 -- JSP to Workflow notification HC 10/24/2000
697 -- set the workflow attributes
698
699
700 if l_time_level_short_name is not NULL then
701
702 l_is_rolling_period := bis_utilities_pvt.Is_Rolling_Period_Level
703 ( p_level_short_name => l_time_level_short_name );
704
705 IF ( l_is_rolling_period = 1 ) THEN
706
707 WF_ENGINE.SetItemAttrText
708 ( itemtype => l_item_type
709 , itemkey => l_wf_item_key
710 , aname => 'L_SUBJECT'
711 , avalue => l_measure_rec.Measure_Name||' '||v_dim_value_level(l_sequence_no)
712 );
713
714 l_rolling_period_end_date := sysdate;
715 l_rolling_period_start_date := bis_utilities_pvt.get_Roll_Period_Start_Date
716 ( p_level_short_name => l_time_level_short_name
717 , p_end_date => l_rolling_period_end_date
718 );
719
720 l_start_label := BIS_UTILITIES_PVT.Get_FND_Message('BIS_ROLLING_START');
721 l_end_label := BIS_UTILITIES_PVT.Get_FND_Message('BIS_ROLLING_END');
722
723 ELSE
724
725 WF_ENGINE.SetItemAttrText
726 ( itemtype => l_item_type
727 , itemkey => l_wf_item_key
728 , aname => 'L_SUBJECT'
729 , avalue => l_measure_rec.Measure_Name||' '||v_dim_value(l_sequence_no)
730 );
731
732 END IF;
733
734 else
735
736 WF_ENGINE.SetItemAttrText
737 ( itemtype => l_item_type
738 , itemkey => l_wf_item_key
739 , aname => 'L_SUBJECT'
740 , avalue => l_measure_rec.Measure_Name
741 );
742 end if;
743
744
745
746
747 l_label := BIS_UTILITIES_PVT.getPrompt
748 (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_SUBJECT')
749 ); -- BIS_UTILITIES_PUB.put_line(p_text =>'Subject'||l_label);
750
751 WF_ENGINE.SetItemAttrText
752 ( itemtype => l_item_type
753 , itemkey => l_wf_item_key
754 , aname => 'L_SUBJECT_LABEL'
755 , avalue => l_label
756 ); -- BIS_UTILITIES_PUB.put_line(p_text =>'subject'||l_label );
757
758
759 -- set the workflow attributes
760 --
761 l_label := BIS_UTILITIES_PVT.getPrompt
762 (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_DETAILS')
763 ); -- BIS_UTILITIES_PUB.put_line(p_text =>'Details'||l_label);
764
765 WF_ENGINE.SetItemAttrText
766 ( itemtype => l_item_type
767 , itemkey => l_wf_item_key
768 , aname => 'L_ALERT_DETAILS_LABEL'
769 , avalue => l_label
770 );
771
772 l_label := BIS_UTILITIES_PVT.getPrompt
773 (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_MSG')
774 );
775 -- BIS_UTILITIES_PUB.put_line(p_text =>'Alert Msg'||l_label);
776 WF_ENGINE.SetItemAttrText
777 ( itemtype => l_item_type
778 , itemkey => l_wf_item_key
779 , aname => 'L_ALERT_MSG'
780 , avalue => l_label
781 );
782
783 l_label := BIS_UTILITIES_PVT.getPrompt
784 (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_RUN_DATE')
785 );
786 -- BIS_UTILITIES_PUB.put_line(p_text =>'Run Date'||l_label);
787 WF_ENGINE.SetItemAttrText
788 ( itemtype => l_item_type
789 , itemkey => l_wf_item_key
790 , aname => 'L_RUN_DATE_LABEL'
791 , avalue => l_label
792 );
793
794 WF_ENGINE.SetItemAttrText
795 ( itemtype => l_item_type
796 , itemkey => l_wf_item_key
797 , aname => 'BIS_RUN_DATE'
798 , avalue => l_date
799 );
800
801
802 For counter IN 1..v_dim_value_level.count Loop
803
804 IF (
805 ( counter = l_sequence_no )
806 AND ( l_is_rolling_period = 1 )
807 AND ( v_dim_value_level ( counter ) IS NOT NULL )
808 ) THEN
809
810 l_alert_details_label := l_alert_details_label || v_dim_value_level(counter) || ' ' || l_start_label || '<br>';
811 l_alert_details := l_alert_details || l_rolling_period_start_date || '<br>';
812 l_alert_details_label := l_alert_details_label || v_dim_value_level(counter) || ' ' || l_end_label || '<br>';
813 l_alert_details := l_alert_details || l_rolling_period_end_date || '<br>';
814 l_alert_details1 := l_alert_details1 || v_dim_value_level(counter) || ' ' || l_start_label || ' : ' || l_rolling_period_start_date || l_NL || v_dim_value_level(counter) || ' ' || l_end_label || ' : ' || l_rolling_period_end_date || l_NL;
815 ELSE
816
817 if (v_dim_value(counter) IS NOT NULL ) and(v_dim_value_level(counter) IS NOT NULL) then
818 -- BIS_UTILITIES_PUB.put_line(p_text =>'Level: '||v_dim_value_level(counter)||' '||v_dim_value(Counter));
819 -- rolling change 2 if time level and if rolling period.
820
821 l_alert_details_label := l_alert_details_label||v_dim_value_level(counter)||'<br>';
822 l_alert_details := l_alert_details||v_dim_value(counter)||'<br>';
823
824 l_alert_details1 := l_alert_details1||v_dim_value_level(counter)||' : ' || v_dim_value(counter) || l_NL;
825
826 END If;
827
828 END IF;
829
830
831 END Loop;
832
833
834 wf_engine.SetItemAttrText
835 ( itemtype => l_item_type
836 , itemkey => l_wf_item_key
837 , aname => 'BIS_DIMENSION_REGION_LABEL'
838 , avalue => l_alert_details_label
839 );
840 l_alert_details_label := null;
841 wf_engine.SetItemAttrText
842 ( itemtype => l_item_type
843 , itemkey => l_wf_item_key
844 , aname => 'BIS_DIMENSION_REGION'
845 , avalue => l_alert_details
846 );
847 l_alert_details := null;
848
849 wf_engine.SetItemAttrText
850 ( itemtype => l_item_type
851 , itemkey => l_wf_item_key
852 , aname => 'BIS_DIMENSION_REGION_TXT'
853 , avalue => l_alert_details1
854 );
855 l_alert_details1 := null;
856
857 BIS_UTILITIES_PUB.put_line(p_text =>'Dimension: '||l_alert_details);
858
859 l_label := BIS_UTILITIES_PVT.getPrompt
860 (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_PERF_MEASURE')
861 );
862 BIS_UTILITIES_PUB.put_line(p_text =>'Perf Measure'||l_label);
863 wf_engine.SetItemAttrText
864 ( itemtype => l_item_type
865 , itemkey => l_wf_item_key
866 , aname => 'L_PERFORMANCE_MEASURE_LABEL'
867 , avalue => l_label);
868
869 wf_engine.SetItemAttrText
870 ( itemtype => l_item_type
871 , itemkey => l_wf_item_key
872 , aname => 'L_PERFORMANCE_MEASURE'
873 , avalue => l_measure_rec.Measure_Name
874 );
875
876 l_label := BIS_UTILITIES_PVT.getPrompt
877 (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_UNIT_MEASURE')
878 );
879 BIS_UTILITIES_PUB.put_line(p_text =>'Unit Of Measure'||l_label);
880 wf_engine.SetItemAttrText
881 ( itemtype => l_item_type
882 , itemkey => l_wf_item_key
883 , aname => 'L_UNITOFMEASURE_LABEL'
884 , avalue => l_label);
885
886 wf_engine.SetItemAttrText
887 ( itemtype => l_item_type
888 , itemkey => l_wf_item_key
889 , aname => 'L_UNITOFMEASURE'
890 , avalue => l_measure_rec.Unit_Of_Measure_Class
891 );
892
893 --BIS_UTILITIES_PUB.put_line(p_text =>l_label||': '||p_measure_instance.Measure_Name);
894
895 l_label := BIS_UTILITIES_PVT.getPrompt
896 (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_TARGET')
897 );
898 BIS_UTILITIES_PUB.put_line(p_text =>'Target'||l_label);
899 wf_engine.SetItemAttrText
900 ( itemtype => l_item_type
901 , itemkey => l_wf_item_key
902 , aname => 'L_TARGET_LABEL'
903 , avalue => l_label);
904
905 wf_engine.SetItemAttrText
906 ( itemtype => l_item_type
907 , itemkey => l_wf_item_key
908 , aname => 'L_TARGET'
909 , avalue => l_target_rec.Target
910 );
911
912 --BIS_UTILITIES_PUB.put_line(p_text =>l_label||': ' ||l_target_rec.Target);
913
914 if (l_number_out_of_range >= 100) then
915
916 l_label := BIS_UTILITIES_PVT.getPrompt
917 (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_TOLERANCE_RANGE1')
918 );
919 BIS_UTILITIES_PUB.put_line(p_text =>'Tolerance Range 1'||l_label);
920
921 wf_engine.SetItemAttrText
922 ( itemtype => l_item_type
923 , itemkey => l_wf_item_key
924 , aname => 'L_TOLERANCE_RANGE_1_LABEL'
925 , avalue => l_label);
926
927 wf_engine.SetItemAttrText
928 ( itemtype => l_item_type
929 , itemkey => l_wf_item_key
930 , aname => 'L_TOLERANCE_RANGE_1'
931 , avalue => l_target_rec.Range1_low || fnd_message.get_string('BIS', 'BIS_ALERT_BELOW') || ' '||l_target_rec.Range1_high||fnd_message.get_string('BIS', 'BIS_ALERT_ABOVE')||' '||l_target_rec.Notify_Resp1_Name); -- 1880142
932 --1880142 , avalue => l_target_rec.Range1_low || ' - ' || l_target_rec.Range1_high||' - '||l_target_rec.Notify_Resp1_Name);
933
934 --BIS_UTILITIES_PUB.put_line(p_text =>l_label||': '||l_target_rec.Range1_low || ' - ' || l_target_rec.Range1_high);
935
936 end if;
937
938 if ( mod(l_number_out_of_range, 100) >= 10 ) then
939
940 l_label := BIS_UTILITIES_PVT.getPrompt
941 (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_TOLERANCE_RANGE2')
942 );
943 BIS_UTILITIES_PUB.put_line(p_text =>'Tolerance Range 2'||l_label);
944
945 wf_engine.SetItemAttrText
946 ( itemtype => l_item_type
947 , itemkey => l_wf_item_key
948 , aname => 'L_TOLERANCE_RANGE_2_LABEL'
949 , avalue => l_label);
950
951 wf_engine.SetItemAttrText
952 ( itemtype => l_item_type
953 , itemkey => l_wf_item_key
954 , aname => 'L_TOLERANCE_RANGE_2'
955 , avalue => l_target_rec.Range2_low || fnd_message.get_string('BIS', 'BIS_ALERT_BELOW') || ' '||l_target_rec.Range2_high||fnd_message.get_string('BIS', 'BIS_ALERT_ABOVE')||' '||l_target_rec.Notify_Resp2_Name); -- 1880142
956 -- 1880142, avalue => l_target_rec.Range2_low || ' - ' || l_target_rec.Range2_high||' - '||l_target_rec.Notify_Resp2_Name);
957
958 --BIS_UTILITIES_PUB.put_line(p_text =>l_label||': '||l_target_rec.Range2_low || ' - ' || l_target_rec.Range2_high);
959
960 end if;
961
962 if ( mod(l_number_out_of_range, 100) = 1 ) or ( mod(l_number_out_of_range, 10) = 1 ) then
963
964 l_label := BIS_UTILITIES_PVT.getPrompt
965 (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_TOLERANCE_RANGE3')
966 );
967
968 BIS_UTILITIES_PUB.put_line(p_text =>'Tolerance Range 3'||l_label);
969
970 wf_engine.SetItemAttrText
971 ( itemtype => l_item_type
972 , itemkey => l_wf_item_key
973 , aname => 'L_TOLERANCE_RANGE_3_LABEL'
974 , avalue => l_label);
975
976 wf_engine.SetItemAttrText
977 ( itemtype => l_item_type
978 , itemkey => l_wf_item_key
979 , aname => 'L_TOLERANCE_RANGE_3'
980 , avalue => l_target_rec.Range3_low || fnd_message.get_string('BIS', 'BIS_ALERT_BELOW') || ' '||l_target_rec.Range3_high||fnd_message.get_string('BIS', 'BIS_ALERT_ABOVE')||' '||l_target_rec.Notify_Resp3_Name); -- 1880142
981 --1880142 , avalue => l_target_rec.Range3_low || ' - ' || l_target_rec.Range3_high||' - '||l_target_rec.Notify_Resp3_Name);
982
983 --BIS_UTILITIES_PUB.put_line(p_text =>l_label||': '||l_target_rec.Range3_low || ' - ' || l_target_rec.Range3_high);
984
985 end if;
986
987
988 l_label := BIS_UTILITIES_PVT.getPrompt
989 (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_ACTUAL')
990 );
991 BIS_UTILITIES_PUB.put_line(p_text =>'Actual'||l_label);
992 wf_engine.SetItemAttrText
993 ( itemtype => l_item_type
994 , itemkey => l_wf_item_key
995 , aname => 'L_ACTUAL_LABEL'
996 , avalue => l_label);
997
998 wf_engine.SetItemAttrText
999 ( itemtype => l_item_type
1000 , itemkey => l_wf_item_key
1001 , aname => 'L_ACTUAL'
1002 , avalue => l_formatted_actual
1003 );
1004
1005 --BIS_UTILITIES_PUB.put_line(p_text =>l_label||': '||l_actual_rec.Actual);
1006
1007 l_label := BIS_UTILITIES_PVT.getPrompt
1008 (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_INFO')
1009 );
1010 BIS_UTILITIES_PUB.put_line(p_text =>'info'||l_label);
1011 wf_engine.SetItemAttrText
1012 ( itemtype => l_item_type
1013 , itemkey => l_wf_item_key
1014 , aname => 'L_RELATED_INFO_LABEL'
1015 , avalue => l_label
1016 );
1017
1018 If ( l_link is null ) THEN -- UPPER(l_link) = G_RPT_ERROR then
1019 l_label := BIS_UTILITIES_PVT.getPrompt
1020 (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_NORPT1')
1021 );
1022
1023 wf_engine.SetItemAttrText
1024 ( itemtype => l_item_type
1025 , itemkey => l_wf_item_key
1026 , aname => 'L_RELATE_INFO_LABEL'
1027 , avalue => l_label
1028 );
1029 l_label := BIS_UTILITIES_PVT.getPrompt
1030 (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_NORPT2')
1031 );
1032
1033 wf_engine.SetItemAttrText
1034 ( itemtype => l_item_type
1035 , itemkey => l_wf_item_key
1036 , aname => 'L_RELATED_INFORMATION'
1037 , avalue => l_label
1038 );
1039
1040 wf_engine.SetItemAttrText
1041 ( itemtype => l_item_type
1042 , itemkey => l_wf_item_key
1043 , aname => 'L_RELATED_INFORMATION_TXT'
1044 , avalue => l_label
1045 );
1046
1047 Else
1048
1049
1050 l_label := BIS_UTILITIES_PVT.getPrompt
1051 (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_INFO_MSG')
1052 );
1053
1054 wf_engine.SetItemAttrText
1055 ( itemtype => l_item_type
1056 , itemkey => l_wf_item_key
1057 , aname => 'L_RELATE_INFO_LABEL'
1058 , avalue => l_label
1059 );
1060 --l_link := 'www.oracle.com';
1061 wf_engine.SetItemAttrText
1062 ( itemtype => l_item_type
1063 , itemkey => l_wf_item_key
1064 , aname => 'L_RELATED_INFORMATION'
1065 , avalue => '<A HREF = '||'"'||l_link||'">'||'BIS REPORT </A>'
1066 );
1067
1068 wf_engine.SetItemAttrText
1069 ( itemtype => l_item_type
1070 , itemkey => l_wf_item_key
1071 , aname => 'L_RELATED_INFORMATION_TXT'
1072 , avalue => 'BIS REPORT'||L_NL||l_link
1073 );
1074
1075 End If;
1076
1077 l_label := BIS_UTILITIES_PVT.getPrompt
1078 (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_RECIPIENTS')
1079 );
1080 BIS_UTILITIES_PUB.put_line(p_text =>'Recipients'||l_label);
1081 wf_engine.SetItemAttrText
1082 ( itemtype => l_item_type
1083 , itemkey => l_wf_item_key
1084 , aname => 'L_ALERT_RECIPIENTS_LABEL'
1085 , avalue => l_label
1086 );
1087
1088 --BIS_UTILITIES_PUB.put_line(p_text =>l_label);
1089 l_label := BIS_UTILITIES_PVT.getPrompt
1090 (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_REC_MSG')
1091 );
1092 BIS_UTILITIES_PUB.put_line(p_text =>'Rec Msg'||l_label);
1093 wf_engine.SetItemAttrText
1094 ( itemtype => l_item_type
1095 , itemkey => l_wf_item_key
1096 , aname => 'L_ALERT_RECIPIENTS_MSG'
1097 , avalue => l_label
1098 );
1099 --BIS_UTILITIES_PUB.put_line(p_text =>l_label);
1100
1101 wf_engine.SetItemAttrText
1102 ( itemtype => l_item_type
1103 , itemkey => l_wf_item_key
1104 , aname => 'L_ROLE_NAME'
1105 , avalue => l_Alert_recipients_sh_nm_tbl(i)
1106 );
1107
1108 --BIS_UTILITIES_PUB.put_line(p_text =>'Role: '||l_Alert_recipients_tbl(i));
1109 l_msg := null;
1110
1111 FOR j IN 1..l_Alert_recipients_tbl.COUNT LOOP -- 2684836
1112
1113 Get_User_List_From_Role
1114 ( p_recipient_short_name => l_Alert_recipients_tbl(j)
1115 , x_user_tbl => l_wf_user_table
1116 , x_return_status => l_return_status
1117 , x_return_msg => l_return_message
1118 ) ;
1119
1120 IF (l_wf_user_table.COUNT = 0) THEN
1121 l_msg := l_msg||'<SPACER TYPE=horizantal SIZE=16>'||l_Alert_recipients_tbl(j)||'<br>';
1122 l_msg1 := l_msg1||l_Alert_recipients_tbl(j)|| l_NL;
1123 ELSE
1124 FOR k IN 1..l_wf_user_table.COUNT LOOP
1125 l_msg := l_msg||'<SPACER TYPE=horizantal SIZE=16>'||l_wf_user_table(k)||'<br>';
1126 l_msg1 := l_msg1||l_wf_user_table(k)||l_NL;
1127 END LOOP;
1128 END IF;
1129
1130 END LOOP;
1131
1132 wf_engine.SetItemAttrText
1133 ( itemtype => l_item_type
1134 , itemkey => l_wf_item_key
1135 , aname => 'L_RECEPIENT'
1136 , avalue => l_msg
1137 );
1138
1139 wf_engine.SetItemAttrText
1140 ( itemtype => l_item_type
1141 , itemkey => l_wf_item_key
1142 , aname => 'L_RECEPIENT_TXT'
1143 , avalue => l_msg1
1144 );
1145 l_label := BIS_UTILITIES_PVT.getPrompt
1146 (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_SCHEDULE')
1147 );
1148
1149 wf_engine.SetItemAttrText
1150 ( itemtype => l_item_type
1151 , itemkey => l_wf_item_key
1152 , aname => 'L_ALERT_SCHEDULE'
1153 , avalue => l_label
1154 );
1155 --BIS_UTILITIES_PUB.put_line(p_text =>'SS');
1156 BIS_UTILITIES_PUB.put_line(p_text =>'Schedule Date: '||l_schedule_date);
1157 BIS_UTILITIES_PUB.put_line(p_text =>'Next Run Date: '||l_next_run_date);
1158
1159 l_msg := null;
1160 l_label := null; -- 1880142
1161 if UPPER(l_schedule_unit) <> 'ONCE' then
1162 if (l_next_run_date IS NOT NULL) then
1163 wf_engine.SetItemAttrText
1164 ( itemtype => l_item_type
1165 , itemkey => l_wf_item_key
1166 , aname => 'NEXT_RUN_DATE'
1167 , avalue => l_next_run_date
1168 );
1169 l_label := BIS_UTILITIES_PVT.getPrompt
1170 (p_attribute_code => BIS_UTILITIES_PVT.Get_FND_Message(
1171 p_message_name => 'BIS_ALERT_SCHEDULE_MSG1'
1172 , p_msg_param1 => 'NEXT_RUN_DATE'
1173 , p_msg_param1_val => l_next_run_date
1174 )
1175 );
1176 --l_msg := 'The next scheduled run time is '||l_next_run_date||'. ';
1177 end if;
1178 if (l_schedule_freq_unit IS NOT NULL) then
1179 wf_engine.SetItemAttrText
1180 ( itemtype => l_item_type
1181 , itemkey => l_wf_item_key
1182 , aname => 'SCHEDULE_INFO'
1183 -- 1880142, avalue => l_schedule_freq ||' '||l_schedule_freq_unit
1184 , avalue => l_schedule_unit -- 1880142
1185 );
1186
1187 l_label := l_label || ' ' || BIS_UTILITIES_PVT.getPrompt
1188 (p_attribute_code => BIS_UTILITIES_PVT.Get_FND_Message(
1189 p_message_name => 'BIS_ALERT_SCHEDULE_MSG2'
1190 , p_msg_param1 => 'SCHEDULE_INFO'
1191 , p_msg_param1_val => l_schedule_unit
1192 )
1193 );
1194 --l_msg := l_msg||'It is scheduled to run on every '||l_schedule_freq||' '||l_schedule_freq_unit||' basis. ';
1195 end if;
1196
1197 else
1198
1199 l_label := BIS_UTILITIES_PVT.getPrompt(p_attribute_code=> fnd_message.get_string('BIS', 'BIS_ALERT_NOT_REPEAT'));
1200
1201 end if;
1202
1203 --l_msg := l_msg||'You will only receive an alert if the actual performane measure is outside of the tolerance range.';
1204 l_label := l_label || ' ' || BIS_UTILITIES_PVT.getPrompt
1205 (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_SCHEDULE_MSG3')
1206 );
1207
1208
1209 -- Begin addition 1898436
1210
1211 BIS_PMF_ALERT_REG_PVT.Form_Param_Set_Rec
1212 ( p_measure_instance => p_measure_instance
1213 , p_dim_level_value_tbl => l_dim_level_value_tbl
1214 , x_Param_Set_Rec => l_Param_Set_Rec
1215 );
1216
1217 BIS_PMF_ALERT_REG_PVT.Retrieve_Notifiers_Code
1218 ( p_api_version => 1.0
1219 , p_Param_Set_rec => l_Param_Set_rec
1220 , x_Notifiers_Code => l_Notifiers_Code
1221 , x_return_status => l_return_status
1222 );
1223
1224 l_unscubscribe_url := FND_WEB_CONFIG.PLSQL_AGENT
1225 || 'bis_corrective_action_pvt.unsub_launch_jsp'
1226 || '?'
1227 || 'pMeasureId=' || l_param_set_rec.PERFORMANCE_MEASURE_ID
1228 || '&' || 'pTargetLevelId=' || l_param_set_rec.TARGET_LEVEL_ID
1229 || '&' || 'pTargetId=' || l_target_rec.target_id
1230 || '&' || 'pTimeDimensionLevelId=' || l_time_level_id
1231 || '&' || 'pPlanId=' || l_param_set_rec.PLAN_ID
1232 || '&' || 'pNotifiersCode=' || l_param_set_rec.NOTIFIERS_CODE
1233 || '&' || 'pParameter1Value=' || l_param_set_rec.PARAMETER1_VALUE
1234 || '&' || 'pParameter2Value=' || l_param_set_rec.PARAMETER2_VALUE
1235 || '&' || 'pParameter3Value=' || l_param_set_rec.PARAMETER3_VALUE
1236 || '&' || 'pParameter4Value=' || l_param_set_rec.PARAMETER4_VALUE
1237 || '&' || 'pParameter5Value=' || l_param_set_rec.PARAMETER5_VALUE
1238 || '&' || 'pParameter6Value=' || l_param_set_rec.PARAMETER6_VALUE
1239 || '&' || 'pParameter7Value=' || l_param_set_rec.PARAMETER7_VALUE;
1240
1241
1242 l_label := l_label || ' ' || fnd_message.get_string('BIS', 'BIS_PMF_ALERT_UNSUB') ;
1243 l_label1 := l_label;
1244 l_label := l_label || ' '
1245 || '<a href="'
1246 || l_unscubscribe_url
1247 || '">'
1248 || fnd_message.get_string('BIS', 'BIS_PMF_UNSUBSCRIBE')
1249 || '</a>.';
1250
1251 l_label1 := l_label1 || ' ' ||
1252 fnd_message.get_string('BIS', 'BIS_PMF_UNSUBSCRIBE') || l_NL ||
1253 l_unscubscribe_url;
1254
1255 -- End addition 1898436
1256
1257
1258 BIS_UTILITIES_PUB.put_line(p_text =>'schedule msg'||l_label);
1259 wf_engine.SetItemAttrText
1260 ( itemtype => l_item_type
1261 , itemkey => l_wf_item_key
1262 , aname => 'L_ALERT_SCHEDULE_MSG'
1263 , avalue => l_label
1264 );
1265
1266 --BIS_UTILITIES_PUB.put_line(p_text =>l_label);
1267 wf_engine.SetItemAttrText
1268 ( itemtype => l_item_type
1269 , itemkey => l_wf_item_key
1270 , aname => 'L_ALERT_SCHEDULE_MSG'
1271 , avalue => l_label
1272 );
1273
1274 wf_engine.SetItemAttrText
1275 ( itemtype => l_item_type
1276 , itemkey => l_wf_item_key
1277 , aname => 'L_ALERT_SCHEDULE_MSG_TXT'
1278 , avalue => l_label1
1279 );
1280
1281 wf_engine.SetItemAttrText
1282 ( itemtype => l_item_type
1283 , itemkey => l_wf_item_key
1284 , aname => 'BIS_ALERT_DOC'
1285 , avalue => 'PLSQLCLOB:BIS_CORRECTIVE_ACTION_PVT.GenerateAlerts/'||l_item_type || ':' || l_wf_item_key);
1286
1287 wf_engine.StartProcess
1288 ( itemtype => l_item_type
1289 , itemkey => l_wf_item_key
1290 );
1291
1292 /*
1293 BIS_UTILITIES_PUB.put_line(p_text =>'Started workflow '||i||'. item type: '||l_item_type
1294 ||', process: '||l_process
1295 ||', item key: '||l_wf_item_key
1296 ||', notified: '||l_Alert_recipients_tbl(i));
1297 */
1298
1299 commit;
1300
1301 END LOOP;
1302
1303 BIS_UTILITIES_PUB.put_line(p_text =>' ........... END : Notification email ........... ');
1304
1305 EXCEPTION
1306 when FND_API.G_EXC_ERROR then
1307 BIS_UTILITIES_PUB.put_line(p_text =>'exception 1 at Send_Alert: '||sqlerrm);
1308 wf_core.context
1309 ( 'BIS_CORRECTIVE_ACTION_PVT'
1310 , 'Send_Alert'
1311 , l_item_type
1312 , l_wf_item_key
1313 );
1314 RETURN;
1315 when FND_API.G_EXC_UNEXPECTED_ERROR then
1316 BIS_UTILITIES_PUB.put_line(p_text =>'exception 2 at Send_Alert: '||sqlerrm);
1317 wf_core.context
1318 ( 'BIS_CORRECTIVE_ACTION_PVT'
1319 , 'Send_Alert'
1320 , l_item_type
1321 , l_wf_item_key
1322 );
1323 RETURN;
1324 when others then
1325 BIS_UTILITIES_PUB.put_line(p_text =>'exception 3 at Send_Alert: '||sqlerrm);
1326 wf_core.context
1327 ( 'BIS_CORRECTIVE_ACTION_PVT'
1328 , 'Send_Alert'
1329 , l_item_type
1330 , l_wf_item_key
1331 );
1332 RETURN;
1333
1334 END Send_Alert;
1335
1336
1337
1338 PROCEDURE Get_User_List_From_Role -- 2684836
1339 ( p_recipient_short_name IN VARCHAR2
1340 , x_user_tbl OUT NOCOPY wf_directory.UserTable
1341 , x_return_status OUT NOCOPY VARCHAR2
1342 , x_return_msg OUT NOCOPY VARCHAR2
1343 )
1344 IS
1345
1346 l_user_tbl wf_directory.UserTable;
1347 l_num_users NUMBER := 0;
1348
1349 BEGIN
1350
1351 IF (
1352 ( BIS_UTILITIES_PUB.Value_Missing(p_recipient_short_name) = FND_API.G_TRUE ) OR
1353 ( BIS_UTILITIES_PUB.Value_Null(p_recipient_short_name) = FND_API.G_TRUE )
1354 ) THEN
1355 x_return_status := FND_API.G_RET_STS_ERROR;
1356 x_return_msg := 'Recipient short name is null or missing.';
1357 RETURN;
1358 END IF;
1359
1360
1361 wf_directory.GetRoleUsers(p_recipient_short_name, l_user_tbl);
1362 x_user_tbl := l_user_tbl;
1363 x_return_status := FND_API.G_RET_STS_SUCCESS;
1364
1365
1366 EXCEPTION
1367 WHEN OTHERS THEN
1368 x_return_status := FND_API.G_RET_STS_ERROR;
1369 x_return_msg := 'Exception occurred in BISVCACB.Get_User_List_From_Role : ' || sqlerrm;
1370 END Get_User_List_From_Role;
1371
1372
1373 Function Get_Role
1374 ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
1375 , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
1376 , p_comparison_result IN VARCHAR2
1377 )
1378 RETURN VARCHAR2
1379 IS
1380
1381 BEGIN
1382 return null;
1383 END Get_Role;
1384
1385 Procedure Get_Alert_Recipients
1386 ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
1387 , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
1388 , p_comparison_result IN VARCHAR2
1389 , x_Alert_recipients_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
1390 , x_Alert_recipients_sh_nm_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
1391 , x_number_out_of_range OUT NOCOPY NUMBER
1392 )
1393 IS
1394
1395 l_Param_Set_Rec BIS_PMF_ALERT_REG_PUB.parameter_set_rec_type;
1396 l_Param_Set_Tbl BIS_PMF_ALERT_REG_PUB.parameter_set_tbl_type;
1397 l_Target_owners_rec BIS_TARGET_PUB.Target_Owners_Rec_Type;
1398 l_Alert_recipients_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
1399 l_Alert_recipients_sh_nm_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
1400 l_notifiers_code VARCHAR2(32000);
1401 l_user_tbl wf_directory.UserTable;
1402 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1403 l_return_status VARCHAR2(32000);
1404
1405 BEGIN
1406
1407 BIS_PMF_ALERT_REG_PVT.Form_Param_Set_Rec
1408 ( p_measure_instance => p_measure_instance
1409 , p_dim_level_value_tbl => p_dim_level_value_tbl
1410 , x_Param_Set_Rec => l_Param_Set_Rec
1411 );
1412
1413 /*
1414 BIS_UTILITIES_PUB.put_line(p_text =>'Formed Param set ');
1415 BIS_UTILITIES_PUB.put_line(p_text =>'REGISTRATION_ID : '||l_param_set_rec.REGISTRATION_ID);
1416 BIS_UTILITIES_PUB.put_line(p_text =>'PERFORMANCE_MEASURE_ID: '
1417 ||l_param_set_rec.PERFORMANCE_MEASURE_ID );
1418 BIS_UTILITIES_PUB.put_line(p_text =>'TARGET_LEVEL_ID : '||l_param_set_rec.TARGET_LEVEL_ID);
1419 BIS_UTILITIES_PUB.put_line(p_text =>'TIME_DIMENSION_LEVEL_ID: '
1420 ||l_param_set_rec.TIME_DIMENSION_LEVEL_ID);
1421 BIS_UTILITIES_PUB.put_line(p_text =>'PLAN_ID : '||l_param_set_rec.PLAN_ID);
1422 BIS_UTILITIES_PUB.put_line(p_text =>'NOTIFIERS_CODE : '||l_param_set_rec.NOTIFIERS_CODE);
1423 BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER1_VALUE : '||l_param_set_rec.PARAMETER1_VALUE);
1424 BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER2_VALUE : '||l_param_set_rec.PARAMETER2_VALUE);
1425 BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER3_VALUE : '||l_param_set_rec.PARAMETER3_VALUE);
1426 BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER4_VALUE : '||l_param_set_rec.PARAMETER4_VALUE);
1427 BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER5_VALUE : '||l_param_set_rec.PARAMETER5_VALUE);
1428 BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER6_VALUE : '||l_param_set_rec.PARAMETER6_VALUE);
1429 BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER7_VALUE : '||l_param_set_rec.PARAMETER7_VALUE);
1430 BIS_UTILITIES_PUB.put_line(p_text =>'NOTIFY_OWNER_FLAG: '||l_param_set_rec.NOTIFY_OWNER_FLAG);
1431 */
1432
1433 BIS_PMF_ALERT_REG_PVT.Retrieve_Parameter_set
1434 ( p_api_version => 1.0
1435 , p_Param_Set_Rec => l_param_set_rec
1436 , x_Param_Set_tbl => l_param_set_tbl
1437 , x_return_status => l_return_status
1438 , x_error_Tbl => l_error_Tbl
1439 );
1440 /*
1441 FOR i IN l_param_set_tbl.FIRST..l_param_set_tbl.LAST LOOP
1442 BIS_UTILITIES_PUB.put_line(p_text =>' Param set '||i);
1443 BIS_UTILITIES_PUB.put_line(p_text =>'REGISTRATION_ID : '||l_param_set_tbl(i).REGISTRATION_ID);
1444 BIS_UTILITIES_PUB.put_line(p_text =>'PERFORMANCE_MEASURE_ID: '
1445 ||l_param_set_tbl(i).PERFORMANCE_MEASURE_ID );
1446 BIS_UTILITIES_PUB.put_line(p_text =>'TARGET_LEVEL_ID : '||l_param_set_tbl(i).TARGET_LEVEL_ID);
1447 BIS_UTILITIES_PUB.put_line(p_text =>'TIME_DIMENSION_LEVEL_ID: '
1448 ||l_param_set_tbl(i).TIME_DIMENSION_LEVEL_ID);
1449 BIS_UTILITIES_PUB.put_line(p_text =>'PLAN_ID : '||l_param_set_tbl(i).PLAN_ID);
1450 BIS_UTILITIES_PUB.put_line(p_text =>'NOTIFIERS_CODE : '||l_param_set_tbl(i).NOTIFIERS_CODE);
1451 BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER1_VALUE : '||l_param_set_tbl(i).PARAMETER1_VALUE);
1452 BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER2_VALUE : '||l_param_set_tbl(i).PARAMETER2_VALUE);
1453 BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER3_VALUE : '||l_param_set_tbl(i).PARAMETER3_VALUE);
1454 BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER4_VALUE : '||l_param_set_tbl(i).PARAMETER4_VALUE);
1455 BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER5_VALUE : '||l_param_set_tbl(i).PARAMETER5_VALUE);
1456 BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER6_VALUE : '||l_param_set_tbl(i).PARAMETER6_VALUE);
1457 BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER7_VALUE : '||l_param_set_tbl(i).PARAMETER7_VALUE);
1458 BIS_UTILITIES_PUB.put_line(p_text =>'NOTIFY_OWNER_FLAG: '||l_param_set_tbl(i).NOTIFY_OWNER_FLAG);
1459 END LOOP;
1460 */
1461
1462 IF l_param_set_tbl.COUNT <> 1 THEN
1463 BIS_UTILITIES_PUB.put_line(p_text =>'odd number of param set found in Get_Alert_Recipients: '
1464 ||l_param_set_tbl.count);
1465 return;
1466 ELSE
1467 BIS_UTILITIES_PUB.put_line(p_text =>'Getting Target owners');
1468 BIS_UTILITIES_PUB.put_line(p_text =>'Check: '||p_measure_instance.target_level_id);
1469 BIS_UTILITIES_PUB.put_line(p_text =>'Check2: '||p_measure_instance.range1_owner_short_name);
1470 BIS_PMF_DATA_SOURCE_PUB.Retrieve_Target_Owners
1471 ( p_measure_instance => p_measure_instance
1472 , p_dim_level_value_tbl => p_dim_level_value_tbl
1473 , p_all_info => FND_API.G_FALSE
1474 , x_target_Owners_rec => l_target_Owners_rec
1475 );
1476 Get_Result_Owners
1477 ( p_target_Owners_rec => l_target_owners_rec
1478 , p_comparison_result => p_comparison_result
1479 , x_owners_tbl => l_Alert_recipients_tbl
1480 , x_owners_sh_nm_tbl => l_Alert_recipients_sh_nm_tbl
1481 , x_number_out_of_range => x_number_out_of_range
1482 );
1483
1484 BIS_UTILITIES_PUB.put_line(p_text =>'total number of owners: '||l_Alert_recipients_tbl.count);
1485
1486 l_notifiers_code := l_param_set_tbl(l_param_set_tbl.FIRST).NOTIFIERS_CODE;
1487 wf_directory.GetRoleUsers(l_notifiers_code,l_user_tbl);
1488 IF l_user_tbl.COUNT > 0 THEN
1489 l_Alert_recipients_tbl(l_Alert_recipients_tbl.COUNT+1) := l_notifiers_code;
1490 l_Alert_recipients_sh_nm_tbl(l_Alert_recipients_sh_nm_tbl.COUNT+1) := l_notifiers_code;
1491 BIS_UTILITIES_PUB.put_line(p_text =>'Recipients include '||l_user_tbl.COUNT||' subscriber(s)');
1492 /*
1493 FOR i IN 1..l_user_tbl.COUNT LOOP
1494 BIS_UTILITIES_PUB.put_line(p_text =>i||')'||l_user_tbl(i));
1495 END LOOP;
1496 */
1497 ELSE
1498 BIS_UTILITIES_PUB.put_line(p_text =>'No subscribers for this alert.');
1499 null;
1500 END IF;
1501 END IF;
1502
1503 --BIS_UTILITIES_PUB.put_line(p_text =>'get alert recipients returned: '||l_Alert_recipients_tbl.COUNT);
1504 FOR i IN 1..l_Alert_recipients_sh_nm_tbl.COUNT LOOP
1505 BIS_UTILITIES_PUB.put_line(p_text =>'Notification will be sent to: '|| l_Alert_recipients_sh_nm_tbl(i));
1506 END LOOP;
1507
1508 x_Alert_recipients_tbl := l_Alert_recipients_tbl;
1509 x_Alert_recipients_sh_nm_tbl := l_Alert_recipients_sh_nm_tbl;
1510
1511 EXCEPTION
1512 WHEN OTHERS THEN
1513 BIS_UTILITIES_PUB.put_line(p_text =>'exception at Get_Alert_Recipients: '||sqlerrm);
1514 END Get_Alert_Recipients;
1515
1516 PROCEDURE Get_Result_Owners
1517 ( p_target_Owners_rec IN BIS_TARGET_PUB.Target_Owners_Rec_Type
1518 , p_comparison_result IN VARCHAR2
1519 , x_owners_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
1520 , x_owners_sh_nm_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
1521 , x_number_out_of_range OUT NOCOPY NUMBER
1522 )
1523 IS
1524 l_owners_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
1525 l_owners_sh_nm_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
1526
1527 l_number_out_of_range NUMBER := 0;
1528 l_first_out_of_range NUMBER := 0;
1529 l_second_out_of_range NUMBER := 0;
1530 l_third_out_of_range NUMBER := 0;
1531
1532 BEGIN
1533
1534 /*
1535 BIS_UTILITIES_PUB.put_line(p_text => ' owner 1 name = ' || p_target_Owners_rec.Range1_Owner_Name ) ;
1536 BIS_UTILITIES_PUB.put_line(p_text => ' owner 1 short name = ' || p_target_Owners_rec.Range1_Owner_short_Name ) ;
1537 BIS_UTILITIES_PUB.put_line(p_text => ' owner 2 name = ' || p_target_Owners_rec.Range2_Owner_Name ) ;
1538 BIS_UTILITIES_PUB.put_line(p_text => ' owner 2 short name = ' || p_target_Owners_rec.Range2_Owner_short_Name ) ;
1539 BIS_UTILITIES_PUB.put_line(p_text => ' owner 3 name = ' || p_target_Owners_rec.Range3_Owner_Name ) ;
1540 BIS_UTILITIES_PUB.put_line(p_text => ' owner 3 short name = ' || p_target_Owners_rec.Range3_Owner_short_Name ) ;
1541 */
1542
1543 l_owners_tbl := x_owners_tbl;
1544
1545 IF
1546 p_comparison_result >= BIS_GENERIC_PLANNER_PVT.G_COMP_RESULT_OUT_OF_RANGE1
1547 --p_target_Owners_rec.Range1_Owner_Name is not null
1548 THEN
1549 l_owners_tbl(l_owners_tbl.COUNT+1) := p_target_Owners_rec.Range1_Owner_Name;
1550 l_owners_sh_nm_tbl(l_owners_sh_nm_tbl.COUNT+1) := p_target_Owners_rec.Range1_Owner_short_Name;
1551 l_first_out_of_range := 1;
1552
1553 END IF;
1554 IF
1555 p_comparison_result >= BIS_GENERIC_PLANNER_PVT.G_COMP_RESULT_OUT_OF_RANGE2
1556 --p_target_Owners_rec.Range2_Owner_Name is not null
1557 THEN
1558 l_owners_tbl(l_owners_tbl.COUNT+1) := p_target_Owners_rec.Range2_Owner_Name;
1559 l_owners_sh_nm_tbl(l_owners_sh_nm_tbl.COUNT+1) := p_target_Owners_rec.Range2_Owner_short_Name;
1560 l_second_out_of_range := 1;
1561
1562 END IF;
1563 IF
1564 p_comparison_result >= BIS_GENERIC_PLANNER_PVT.G_COMP_RESULT_OUT_OF_RANGE3
1565 --p_target_Owners_rec.Range3_Owner_Name is not null
1566 THEN
1567 l_owners_tbl(l_owners_tbl.COUNT+1) := p_target_Owners_rec.Range3_Owner_Name;
1568 l_owners_sh_nm_tbl(l_owners_sh_nm_tbl.COUNT+1) := p_target_Owners_rec.Range3_Owner_short_Name;
1569 l_third_out_of_range := 1;
1570 END IF;
1571
1572 x_owners_tbl := l_owners_tbl;
1573 x_owners_sh_nm_tbl := l_owners_sh_nm_tbl;
1574
1575 -- BIS_UTILITIES_PUB.put_line(p_text => ' get result owners owners count ' || l_owners_tbl.COUNT || ' sh nm dt ' || l_owners_sh_nm_tbl.COUNT ) ;
1576
1577 l_number_out_of_range := 100*l_first_out_of_range + 10*l_second_out_of_range + l_third_out_of_range ;
1578 x_number_out_of_range := l_number_out_of_range;
1579
1580 EXCEPTION
1581 WHEN OTHERS THEN
1582 BIS_UTILITIES_PUB.put_line(p_text =>'exception at Get_Result_Owners: '||sqlerrm);
1583
1584 END Get_Result_Owners;
1585
1586 Procedure Get_Performance_Measure_Msg
1587 ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
1588 , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
1589 , p_comparison_result IN VARCHAR2
1590 , x_message_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
1591 )
1592 IS
1593
1594 l_target_level_rec BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type;
1595 l_target_rec BIS_TARGET_PUB.Target_Rec_Type;
1596
1597 l_message_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
1598 l_Message_Banner_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
1599 l_Message_Intro_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
1600 l_Message_body_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
1601 l_notify VARCHAR2(32000);
1602 l_sent_date VARCHAR2(32000);
1603
1604 BEGIN
1605
1606 -- Retrieve Performance Target information
1607 --
1608 BIS_PMF_DATA_SOURCE_PUB.Retrieve_Target_Level
1609 ( p_measure_instance => p_measure_instance
1610 , p_dim_level_value_tbl => p_dim_level_value_tbl
1611 , p_all_info => FND_API.G_TRUE
1612 , x_target_level_rec => l_target_level_rec
1613 );
1614 BIS_PMF_DATA_SOURCE_PUB.Retrieve_Target
1615 ( p_measure_instance => p_measure_instance
1616 , p_dim_level_value_tbl => p_dim_level_value_tbl
1617 , p_all_info => FND_API.G_TRUE
1618 , x_target_rec => l_target_rec
1619 );
1620 l_sent_Date := getdate; -- to_char(sysdate,'DD-MON-YYYY HH24:MM:SS'); -- 2837974
1621
1622 IF
1623 p_comparison_result = BIS_CORRECTIVE_ACTION_PUB.G_MSG_TYPE_EXCEPTION_RANGE1
1624 THEN
1625 l_notify := l_target_rec.Notify_Resp1_Name;
1626 ELSIF
1627 p_comparison_result = BIS_CORRECTIVE_ACTION_PUB.G_MSG_TYPE_EXCEPTION_RANGE2
1628 THEN
1629 l_notify := l_target_rec.Notify_Resp2_Name;
1630 ELSIF
1631 p_comparison_result = BIS_CORRECTIVE_ACTION_PUB.G_MSG_TYPE_EXCEPTION_RANGE3
1632 THEN
1633 l_notify := l_target_rec.Notify_Resp3_Name;
1634 END IF;
1635
1636 Get_Message_Banner
1637 ( p_Sent_Date => l_sent_date
1638 , p_Item => l_target_level_rec.Workflow_Process_Name
1639 , p_to => l_notify
1640 , x_Message_Banner_tbl => l_Message_Banner_tbl
1641 );
1642
1643 Get_Message_Intro
1644 ( p_Sent_Date => l_sent_date
1645 , x_Message_Intro_tbl => l_Message_Intro_tbl
1646 );
1647
1648 Get_Message_Body
1649 ( p_measure_instance => p_measure_instance
1650 , p_dim_level_value_tbl => p_dim_level_value_tbl
1651 , p_comparison_result => p_comparison_result
1652 , x_message_body_tbl => l_message_body_tbl
1653 );
1654
1655 -- Build message;
1656 --
1657 FOR i IN 1..l_Message_Banner_tbl.COUNT LOOP
1658 l_message_tbl(l_message_tbl.COUNT+1) := l_Message_Banner_tbl(i);
1659 END LOOP;
1660
1661 FOR i IN 1..l_Message_intro_tbl.COUNT LOOP
1662 l_message_tbl(l_message_tbl.COUNT+1) := l_Message_intro_tbl(i);
1663 END LOOP;
1664
1665 FOR i IN 1..l_Message_body_tbl.COUNT LOOP
1666 l_message_tbl(l_message_tbl.COUNT+1) := l_Message_body_tbl(i);
1667 END LOOP;
1668
1669 x_message_tbl := l_message_tbl;
1670
1671 EXCEPTION
1672 WHEN OTHERS THEN
1673 BIS_UTILITIES_PUB.put_line(p_text =>'error in Get_Performance_Measure_Msg: '||sqlerrm);
1674
1675 END Get_Performance_Measure_Msg;
1676
1677 Procedure Get_Message_Banner
1678 ( p_Sent_Date IN VARCHAR2
1679 , p_Item IN VARCHAR2
1680 , p_to IN VARCHAR2
1681 , x_Message_Banner_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
1682 )
1683 IS
1684
1685 l_Message_Banner_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
1686
1687 BEGIN
1688
1689 x_message_banner_tbl := l_message_banner_tbl;
1690
1691 END Get_Message_Banner;
1692
1693 Procedure Get_Message_Intro
1694 ( p_Sent_Date IN VARCHAR2
1695 , x_Message_Intro_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
1696 )
1697 IS
1698
1699 BEGIN
1700 null;
1701 END Get_Message_Intro;
1702
1703 Procedure Get_Message_Body
1704 ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
1705 , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
1706 , p_comparison_result IN VARCHAR2
1707 , x_message_body_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
1708 )
1709 IS
1710
1711 BEGIN
1712 null;
1713 END Get_Message_Body;
1714
1715 Procedure Get_Alert_Information
1716 ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
1717 , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
1718 , x_Alert_Information_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
1719 )
1720 IS
1721
1722 BEGIN
1723 null;
1724 END Get_Alert_Information;
1725
1726 Procedure Get_Related_Links
1727 ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
1728 , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
1729 , x_Related_Links_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
1730 )
1731 IS
1732
1733 BEGIN
1734 null;
1735 END Get_Related_Links;
1736
1737 Procedure Get_Report_Attachement
1738 ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
1739 , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
1740 , x_attachement_url_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
1741 )
1742 IS
1743
1744 BEGIN
1745
1746 null;
1747
1748 EXCEPTION
1749 when FND_API.G_EXC_ERROR then
1750 RETURN;
1751 when FND_API.G_EXC_UNEXPECTED_ERROR then
1752 RETURN;
1753 when others then
1754 BIS_UTILITIES_PVT.Add_Error_Message
1755 ( p_error_msg_id => SQLCODE
1756 , p_error_description => SQLERRM
1757 , p_error_proc_name => G_PKG_NAME||'.Get_Report_Attachement'
1758 );
1759 RETURN;
1760
1761 END Get_Report_Attachement;
1762
1763
1764 Procedure Get_Alert_Message
1765 ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
1766 , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
1767 , x_Alert_Message_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
1768 )
1769 IS
1770
1771 BEGIN
1772 null;
1773 END Get_Alert_Message;
1774
1775 Procedure Set_Message
1776 ( p_message_tbl IN BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
1777 , x_return_status OUT NOCOPY VARCHAR2
1778 )
1779 IS
1780
1781 BEGIN
1782 null;
1783 END Set_Message;
1784
1785 Procedure Generate_Alert_Message
1786 ( document_id IN VARCHAR2
1787 , display_type IN VARCHAR2
1788 , document IN OUT NOCOPY VARCHAR2
1789 , document_type IN OUT NOCOPY VARCHAR2
1790 )
1791 IS
1792
1793 l_message varchar2(32000) := NULL;
1794 l_redirect varchar2(32000) := NULL;
1795 l_servlet_agent varchar2(32000) := NULL;
1796 l_encrypted_session_id VARCHAR2(1000);
1797 l_session_id NUMBER;
1798 l_document varchar2(32000);
1799 l_amp varchar2(10) := '&';
1800 l_target_id varchar2(32000);
1801 l_wait_msg varchar2(32000) :=
1802 BIS_UTILITIES_PVT.Get_FND_Message
1803 ( p_message_name => 'BIS_ALERT_RETRIEVING_MSG' );
1804
1805 BEGIN
1806
1807 l_servlet_agent := Get_Servlet_Agent;
1808 --l_target_id := document_id;
1809 l_session_id := icx_sec.getsessioncookie;
1810 l_encrypted_session_id :=
1811 icx_call.encrypt3(icx_sec.getID(icx_Sec.PV_SESSION_ID));
1812
1813 l_redirect :=
1814 '<HEAD> <META HTTP-EQUIV="Refresh" '
1815 ||'CONTENT="1;URL='||l_servlet_agent||G_NOTIFICATION_JSP_PAGE
1816 ||'?dbc='||FND_WEB_CONFIG.DATABASE_ID
1817 ||l_amp||'sessionid='||l_encrypted_session_id
1818 ||document_id
1819 ||'">'
1820 ||bis_utilities_pub.encode(l_wait_msg) -- 2418741
1821 ||' </HEAD> ';
1822
1823 l_message := l_redirect;
1824 --BIS_UTILITIES_PUB.put_line(p_text =>'notification redirect: '||l_message);
1825
1826 document := l_message;
1827 document_type := 'text/html';
1828 return;
1829
1830 EXCEPTION
1831 when others then
1832 BIS_UTILITIES_PUB.put_line(p_text =>'exception at Generate_Alert_Message;: '||sqlerrm);
1833 RETURN;
1834
1835 END Generate_Alert_Message;
1836
1837 FUNCTION Get_Servlet_Agent RETURN VARCHAR2
1838 IS
1839
1840 l_servlet_agent varchar2(32000) := NULL;
1841 l_dir_tmp varchar2(32000);
1842
1843 BEGIN
1844
1845 l_servlet_agent :=
1846 FND_WEB_CONFIG.TRAIL_SLASH(fnd_profile.value('APPS_SERVLET_AGENT'));
1847
1848 RETURN l_servlet_agent;
1849
1850 EXCEPTION
1851 WHEN NO_DATA_FOUND THEN
1852 BIS_UTILITIES_PUB.put_line(p_text =>'No such agent defined at site level.');
1853
1854 WHEN OTHERS THEN
1855 BIS_UTILITIES_PUB.put_line(p_text =>'General exception while getting servlet agent: '||sqlerrm);
1856
1857 End Get_Servlet_Agent;
1858
1859 Procedure Get_Request_Info
1860 ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
1861 , x_schedule_date OUT NOCOPY VARCHAR2
1862 , x_schedule_time OUT NOCOPY VARCHAR2
1863 , x_schedule_unit OUT NOCOPY VARCHAR2
1864 , x_schedule_freq OUT NOCOPY VARCHAR2
1865 , x_next_run_date OUT NOCOPY VARCHAR2
1866 , x_next_run_time OUT NOCOPY VARCHAR2
1867 , x_description OUT NOCOPY VARCHAR2
1868 , x_return_status OUT NOCOPY VARCHAR2
1869 )
1870 IS
1871
1872 l_Param_Set_Rec BIS_PMF_ALERT_REG_PUB.parameter_set_rec_type;
1873
1874 BEGIN
1875 BIS_PMF_ALERT_REG_PVT.Form_Param_Set_Rec
1876 ( p_measure_instance => p_measure_instance
1877 , x_Param_Set_Rec => l_Param_Set_Rec
1878 );
1879
1880 /*
1881 BIS_UTILITIES_PUB.put_line(p_text =>'Getting request information. measure id: '
1882 ||l_Param_Set_Rec.performance_measure_id||', target level: '
1883 ||l_Param_Set_Rec.target_level_id);
1884 */
1885
1886 BIS_CONCURRENT_MANAGER_PVT.Get_Request_Schedule_Info
1887 ( p_Param_Set_rec => l_Param_Set_rec
1888 , x_schedule_date => x_schedule_date
1889 , x_schedule_time => x_schedule_time
1890 , x_schedule_unit => x_schedule_unit
1891 , x_schedule_freq => x_schedule_freq
1892 , x_schedule_freq_unit => x_schedule_unit
1893 , x_schedule_end_date => x_schedule_date
1894 , x_schedule_end_time => x_schedule_time
1895 , x_next_run_date => x_next_run_date
1896 , x_next_run_time => x_next_run_time
1897 , x_description => x_description
1898 , x_return_status => x_return_status
1899 );
1900
1901 BIS_UTILITIES_PUB.put_line(p_text =>'Request description: '||x_description);
1902 BIS_UTILITIES_PUB.put_line(p_text =>'Schedule Date: '||x_schedule_date);
1903
1904 EXCEPTION
1905 WHEN OTHERS THEN
1906 BIS_UTILITIES_PUB.put_line(p_text =>'error at Get_Request_Info: '||sqlerrm);
1907
1908 END Get_Request_Info;
1909
1910 FUNCTION getDate
1911 RETURN VARCHAR2
1912 IS
1913
1914 l_date_format VARCHAR2(32000);
1915 l_date VARCHAR2(32000);
1916
1917 BEGIN
1918
1919 fnd_profile.get('ICX_DATE_FORMAT_MASK',l_date_format);
1920 select to_char(sysdate,l_date_format)
1921 into l_date
1922 from dual;
1923
1924 return l_date;
1925
1926 EXCEPTION
1927 WHEN OTHERS THEN
1928 l_date := sysdate;
1929 RETURN l_date;
1930
1931 END getDate;
1932
1933 FUNCTION getAdHocRole
1934 ( p_Alert_recipients_tbl IN BIS_UTILITIES_PUB.BIS_VARCHAR_TBL )
1935 RETURN VARCHAR2
1936 IS
1937 l_role VARCHAR2(32000);
1938 l_bis_alert VARCHAR2(32000);
1939 colon NUMBER;
1940 BEGIN
1941
1942 FOR i IN 1..p_Alert_recipients_tbl.COUNT LOOP
1943 colon := instr(p_Alert_recipients_tbl(i), ':');
1944 l_bis_alert := substr(p_Alert_recipients_tbl(i),0,colon-1);
1945
1946 IF (l_bis_alert = BIS_PMF_ALERT_REG_PVT.G_BIS_ALERT_ROLE) THEN
1947 l_role := p_Alert_recipients_tbl(i);
1948 BIS_UTILITIES_PUB.put_line(p_text =>'Got AddHocRole: '||l_role);
1949 END IF;
1950 END LOOP;
1951
1952 return l_role;
1953
1954 EXCEPTION
1955 WHEN OTHERS THEN
1956 l_role := NULL;
1957 RETURN l_role;
1958
1959 END getAdHocRole;
1960
1961 FUNCTION Format_Message(p_message_tbl IN BIS_UTILITIES_PUB.BIS_VARCHAR_TBL)
1962 RETURN VARCHAR2
1963 IS
1964
1965 msg_len NUMBER;
1966 new_len NUMBER;
1967 acceptable_len NUMBER;
1968 l_message VARCHAR2(32000);
1969
1970 BEGIN
1971
1972 FOR i IN 1..p_message_tbl.COUNT LOOP
1973 msg_len := length(l_message);
1974 new_len := length(p_message_tbl(i));
1975 acceptable_len := 32000-msg_len;
1976 IF msg_len < 32000 AND new_len <= acceptable_len
1977 THEN
1978 l_message := l_message||p_message_tbl(i);
1979 ELSE
1980 exit;
1981 END IF;
1982 END LOOP;
1983
1984 Return l_message;
1985
1986 EXCEPTION
1987 WHEN OTHERS THEN
1988 BIS_UTILITIES_PUB.put_line(p_text =>'Exception while formatting message: '||sqlerrm);
1989
1990 END Format_Message;
1991
1992 Procedure Get_Workflow_Info
1993 ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
1994 , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
1995 , p_comparison_result IN VARCHAR2
1996 , x_item_type OUT NOCOPY VARCHAR2
1997 , x_process OUT NOCOPY VARCHAR2
1998 )
1999 IS
2000
2001 l_target_level_rec BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type;
2002
2003 BEGIN
2004
2005 BIS_PMF_DATA_SOURCE_PUB.Retrieve_Target_Level
2006 ( p_measure_instance => p_measure_instance
2007 , p_dim_level_value_tbl => p_dim_level_value_tbl
2008 , p_all_info => FND_API.G_FALSE
2009 , x_target_level_rec => l_target_level_rec
2010 );
2011 x_item_type := l_target_level_rec.Workflow_Item_Type;
2012 x_process := l_target_level_rec.Workflow_Process_Short_Name;
2013
2014 EXCEPTION
2015 WHEN OTHERS THEN
2016 BIS_UTILITIES_PUB.put_line(p_text =>'Exception while getting workflow info: '||sqlerrm);
2017
2018 END Get_Workflow_Info;
2019
2020 FUNCTION Generate_parameter_string
2021 ( p_target_id IN VARCHAR2
2022 , p_comparison_result IN VARCHAR2
2023 , p_role IN VARCHAR2
2024 , p_date IN VARCHAR2
2025 , p_schedule_date IN VARCHAR2
2026 , P_schedule_time IN VARCHAR2
2027 , p_schedule_freq_unit IN VARCHAR2
2028 , p_next_run_date IN VARCHAR2
2029 , p_next_run_time IN VARCHAR2
2030 , p_description IN VARCHAR2
2031 ) RETURN VARCHAR2
2032 IS
2033
2034 l_parameters VARCHAR2(32000);
2035 l_amp VARCHAR2(10) := '&';
2036 l_target_id VARCHAR2(1000);
2037 l_comparison_result VARCHAR2(1000);
2038 l_role VARCHAR2(1000);
2039 l_date VARCHAR2(1000);
2040 l_schedule_date VARCHAR2(1000);
2041 l_schedule_time VARCHAR2(1000);
2042 l_schedule_freq_unit VARCHAR2(1000);
2043 l_next_run_date VARCHAR2(1000);
2044 l_next_run_time VARCHAR2(1000);
2045 l_description VARCHAR2(32000);
2046
2047 BEGIN
2048 -- 2280993 starts
2049 /*
2050 l_target_id := wfa_html.conv_special_url_chars(p_target_id);
2051 l_comparison_result := wfa_html.conv_special_url_chars(p_comparison_result);
2052 l_role := wfa_html.conv_special_url_chars(p_role);
2053 l_date := wfa_html.conv_special_url_chars(p_date);
2054 l_schedule_date := wfa_html.conv_special_url_chars(p_schedule_date);
2055 L_schedule_time := wfa_html.conv_special_url_chars(P_schedule_time);
2056 l_schedule_freq_unit:= wfa_html.conv_special_url_chars(p_schedule_freq_unit);
2057 l_next_run_date := wfa_html.conv_special_url_chars(p_next_run_date);
2058 l_next_run_time := wfa_html.conv_special_url_chars(p_next_run_time);
2059 l_description := wfa_html.conv_special_url_chars(p_description);
2060 */
2061 l_target_id := BIS_UTILITIES_PUB.encode(p_target_id);
2062 l_comparison_result := BIS_UTILITIES_PUB.encode(p_comparison_result);
2063 l_role := BIS_UTILITIES_PUB.encode(p_role);
2064 l_date := BIS_UTILITIES_PUB.encode(p_date);
2065 l_schedule_date := BIS_UTILITIES_PUB.encode(p_schedule_date);
2066 L_schedule_time := BIS_UTILITIES_PUB.encode(P_schedule_time);
2067 l_schedule_freq_unit := BIS_UTILITIES_PUB.encode(p_schedule_freq_unit);
2068 l_next_run_date := BIS_UTILITIES_PUB.encode(p_next_run_date);
2069 l_next_run_time := BIS_UTILITIES_PUB.encode(p_next_run_time);
2070 l_description := BIS_UTILITIES_PUB.encode(p_description);
2071
2072 -- 2280993 ends
2073
2074 l_parameters
2075 := l_amp||'target_id='||l_target_id
2076 ||l_amp||'compResult='||l_comparison_result
2077 ||l_amp||'adHocRole='||l_role
2078 ||l_amp||'sentDate='||l_date
2079 ||l_amp||'scheduleDate='||l_schedule_date
2080 ||l_amp||'scheduleTime='||l_schedule_time
2081 ||l_amp||'scheduleUnit='||l_schedule_freq_unit
2082 ||l_amp||'nextRunDate='||l_next_run_date
2083 ||l_amp||'nextRunTime='||l_next_run_time
2084 ||l_amp||'alertDesc='||l_description
2085 ;
2086 return l_parameters;
2087
2088 EXCEPTION
2089 WHEN OTHERS THEN
2090 BIS_UTILITIES_PUB.put_line(p_text =>'Exception while generating parameter string: '||sqlerrm);
2091
2092 END Generate_parameter_string;
2093
2094
2095
2096
2097 PROCEDURE unsub_launch_jsp -- 1898436
2098 ( pMeasureId IN VARCHAR2 := NULL
2099 , pTargetLevelId IN VARCHAR2 := NULL
2100 , pTargetId IN VARCHAR2 := NULL
2101 , pTimeDimensionLevelId IN VARCHAR2 := NULL
2102 , pPlanId IN VARCHAR2 := NULL
2103 , pNotifiersCode IN VARCHAR2 := NULL
2104 , pParameter1Value IN VARCHAR2 := NULL
2105 , pParameter2Value IN VARCHAR2 := NULL
2106 , pParameter3Value IN VARCHAR2 := NULL
2107 , pParameter4Value IN VARCHAR2 := NULL
2108 , pParameter5Value IN VARCHAR2 := NULL
2109 , pParameter6Value IN VARCHAR2 := NULL
2110 , pParameter7Value IN VARCHAR2 := NULL
2111 )
2112 IS
2113 l_url VARCHAR2(32000) := NULL;
2114 l_bis_url VARCHAR2(5000) := NULL;
2115 l_jsp_name VARCHAR2(500) := NULL;
2116 l_parameter VARCHAR2(32000) := NULL;
2117 l_server_port VARCHAR2(80) := NULL;
2118
2119 BEGIN
2120
2121 IF icx_sec.validateSession THEN
2122
2123 l_jsp_name := 'bisunsub.jsp';
2124
2125 l_bis_url := bis_utilities_pvt.get_bis_jsp_path;
2126
2127 l_parameter := 'measureId='||pMeasureId -- 487
2128 || '&' || 'targetLevelId=' || pTargetLevelId -- 1964
2129 || '&' || 'targetId=' || pTargetId -- 602
2130 || '&' || 'timeDimensionLevelId=' || pTimeDimensionLevelId -- 4
2131 || '&' || 'planId=' || pPlanId -- 2
2132 || '&' || 'notifiersCode=' || pNotifiersCode
2133 || '&' || 'parameter1Value=' || pParameter1Value -- 204
2134 || '&' || 'parameter2Value=' || pParameter2Value -- 204
2135 || '&' || 'parameter3Value=' || pParameter3Value -- 204
2136 || '&' || 'parameter4Value=' || pParameter4Value -- 204
2137 || '&' || 'parameter5Value=' || pParameter5Value -- 204
2138 || '&' || 'parameter6Value=' || pParameter6Value -- 204
2139 || '&' || 'parameter7Value=' || pParameter7Value -- 204
2140 || '&' || 'warnMsg=BIS_PMF_UNSUBSCRIBE_HDR'
2141 || '&' || 'warnMsgDtl=BIS_PMF_UNSUBSCRIBE_CONF'
2142 || '&' || 'errorMsg=BIS_PMF_UNSUB_ERR_HDR'
2143 || '&' || 'errorMsgDtl=BIS_PMF_UNSUB_ERROR_MSG'
2144 || '&' || 'pageTitle=BIS_PMF_UNSUB_TITLE';
2145
2146
2147 l_url:= l_bis_url || l_jsp_name || '?' || l_parameter; -- l_unsub_params;
2148
2149 owa_util.redirect_url(l_url);
2150
2151 END IF;
2152
2153
2154 EXCEPTION
2155 WHEN OTHERS THEN
2156 NULL;
2157 END unsub_launch_jsp ;
2158
2159
2160 PROCEDURE unSubscribeFromAlerts -- 1898436
2161 (p_measure_Id IN VARCHAR2 := NULL
2162 ,p_target_Level_Id IN VARCHAR2 := NULL
2163 ,p_time_Dimension_Level_Id IN VARCHAR2 := NULL
2164 ,p_plan_Id IN VARCHAR2 := NULL
2165 ,p_notifiers_Code IN VARCHAR2 := NULL
2166 ,p_parameter1_Value IN VARCHAR2 := NULL
2167 ,p_parameter2_Value IN VARCHAR2 := NULL
2168 ,p_parameter3_Value IN VARCHAR2 := NULL
2169 ,p_parameter4_Value IN VARCHAR2 := NULL
2170 ,p_parameter5_Value IN VARCHAR2 := NULL
2171 ,p_parameter6_Value IN VARCHAR2 := NULL
2172 ,p_parameter7_Value IN VARCHAR2 := NULL
2173 ,x_return_status OUT NOCOPY VARCHAR2
2174 ,x_msg_count OUT NOCOPY NUMBER
2175 ,x_msg_data OUT NOCOPY VARCHAR2
2176 ) IS
2177 l_RoleName VARCHAR2(30);
2178 l_UserName VARCHAR2(100);
2179 l_UserExists NUMBER;
2180 l_Count NUMBER := 0;
2181 l_notifiers_code VARCHAR2(300);
2182 l_return_status VARCHAR2(100);
2183 l_users_table Wf_Directory.UserTable;
2184 l_Param_Set_rec BIS_PMF_ALERT_REG_PUB.parameter_set_rec_type;
2185 l_user_in_role BOOLEAN := FALSE;
2186 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
2187 i NUMBER;
2188 l_user_tbl wf_directory.UserTable;
2189 l_role VARCHAR2(300) := NULL; -- 'BIS_ALERT880';
2190 l_error_Tbl_p BIS_UTILITIES_PUB.Error_Tbl_Type;
2191
2192
2193 BEGIN
2194
2195 x_msg_count := 0;
2196 x_return_status := FND_API.G_RET_STS_SUCCESS;
2197
2198 SAVEPOINT unSubscribeFromAlerts_pvt;
2199
2200 l_username := FND_GLOBAL.USER_NAME; -- 'SYSADMIN'; -- 'SADIQ'; --
2201
2202 l_Param_Set_rec.performance_measure_id := p_measure_id;
2203 l_Param_Set_rec.target_level_id := p_target_level_id;
2204 l_Param_Set_rec.time_dimension_level_id := p_time_dimension_level_id;
2205 l_Param_Set_rec.plan_id := p_plan_id;
2206 l_Param_Set_rec.parameter1_value := p_parameter1_value;
2207 l_Param_Set_rec.parameter2_value := p_parameter2_value;
2208 l_Param_Set_rec.parameter3_value := p_parameter3_value;
2209 l_Param_Set_rec.parameter4_value := p_parameter4_value;
2210 l_Param_Set_rec.parameter5_value := p_parameter5_value;
2211 l_Param_Set_rec.parameter6_value := p_parameter6_value;
2212 l_Param_Set_rec.parameter7_value := p_parameter7_value;
2213
2214 BIS_PMF_ALERT_REG_PVT.Retrieve_Notifiers_Code
2215 ( p_api_version => 1.0
2216 , p_Param_Set_rec => l_Param_Set_rec
2217 , x_Notifiers_Code => l_Notifiers_Code
2218 , x_return_status => l_return_status
2219 );
2220
2221 wf_directory.GetRoleUsers ( l_Notifiers_Code, l_users_table);
2222
2223 FOR i IN 1..l_users_table.count LOOP
2224 IF ( l_users_table(i) = l_UserName ) THEN
2225 l_user_in_role := TRUE;
2226 EXIT;
2227 END IF;
2228 END LOOP;
2229
2230 IF l_user_in_role THEN
2231 wf_directory.RemoveUsersFromAdHocRole( l_notifiers_code , l_UserName);
2232 END IF;
2233
2234 COMMIT;
2235
2236 EXCEPTION
2237
2238 WHEN FND_API.G_EXC_ERROR THEN
2239 ROLLBACK TO unSubscribeFromAlerts_pvt;
2240 x_return_status := FND_API.G_RET_STS_ERROR ;
2241 FND_MSG_PUB.Count_And_Get
2242 (p_count => x_msg_count,
2243 p_data => x_msg_data
2244 );
2245
2246 WHEN OTHERS THEN
2247 ROLLBACK TO unSubscribeFromAlerts_pvt;
2248 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2249 x_msg_count := 1;
2250 x_msg_data := 'Error in BISVCACB.pls in unSubscribeFromAlerts' || sqlerrm;
2251 l_error_tbl_p := l_error_tbl;
2252 BIS_UTILITIES_PVT.Add_Error_Message
2253 ( p_error_msg_id => SQLCODE
2254 , p_error_description => SQLERRM
2255 , p_error_proc_name => G_PKG_NAME||'.unSubscribeFromAlerts'
2256 , p_error_table => l_error_tbl_p
2257 , x_error_table => l_error_tbl
2258 );
2259 END unSubscribeFromAlerts;
2260
2261
2262
2263 -- Bug 2871017.
2264 -- This procedure is invoked by the workflow notification system to
2265 -- generate the mail content.
2266 -- This procedure obtains the values set in the item attributes and the
2267 -- message body is returned as a CLOB. HTML/TXT content is determined based
2268 -- on the content type.
2269
2270 PROCEDURE GenerateAlerts
2271 ( document_id IN VARCHAR2,
2272 content_type IN VARCHAR2,
2273 document IN OUT NOCOPY CLOB,
2274 document_type IN OUT NOCOPY VARCHAR2)
2275 IS
2276
2277 l_NL VARCHAR2(1) := fnd_global.newline;
2278 l_sp VARCHAR2(32);
2279 l_document VARCHAR2(32000);
2280 l_item_type VARCHAR2(300) := document_id;
2281 l_wf_item_key WF_ITEM_ATTRIBUTE_VALUES.ITEM_KEY%TYPE := '';
2282 l_document_id NUMBER ;
2283 l_label VARCHAR2(32000);
2284 l_server_date DATE;
2285 l_client_date DATE;
2286 l_server_code varchar2(50);
2287 l_client_code varchar2(50);
2288 l_run_date varchar2(50);
2289 l_next_run_date varchar2(50);
2290 l_sys_run_date varchar2(50);
2291 l_sys_next_run_date varchar2(50);
2292 l_unit varchar2(100);
2293 l_freq varchar2(100);
2294 l_alert_message varchar2(32000);
2295 l_date_format varchar2(50);
2296 l_hdr_support varchar2(100);
2297 l_alert varchar2(50);
2298 l_dbi_measure varchar2(50) := 'DBI_MEASURE';
2299 l_alert_attribute varchar2(100);
2300 l_alert_attribute_label varchar2(100);
2301
2302 Begin
2303
2304 l_document := '';
2305 l_alert_message := '';
2306 l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
2307 l_wf_item_key := substr(document_id, instr(document_id, ':') + 1,
2308 length(document_id) - 2);
2309 l_sp := '<SPACER TYPE=horizontal SIZE=72>';
2310
2311 l_date_format := fnd_profile.value('ICX_DATE_FORMAT_MASK');
2312 l_date_format := l_date_format || ' HH24:MI:SS';
2313
2314 l_sys_run_date := WF_ENGINE.GetItemAttrText (
2315 itemtype => l_item_type,
2316 itemkey => l_wf_item_key,
2317 aname => 'BIS_RUN_DATE' );
2318
2319 l_server_code := fnd_timezones.get_server_timezone_code;
2320 l_client_code := fnd_timezones.get_client_timezone_code;
2321
2322 l_server_date := to_date(l_sys_run_date,'DD/MM/RRRR HH24:MI:SS');
2323
2324 /*
2325 l_client_date := fnd_timezones_pvt.adjust_datetime(
2326 date_time => l_server_date
2327 ,from_tz => l_server_code
2328 ,to_tz => l_client_code
2329 );
2330 */
2331
2332 l_client_date := Adjust_Datetime
2333 ( p_date_time => l_server_date
2334 , p_from_tz => l_server_code
2335 , p_to_tz => l_client_code
2336 ) ;
2337
2338 l_run_date := to_char(l_client_date, l_date_format);
2339
2340 l_unit := WF_ENGINE.GetItemAttrText (
2341 itemtype => l_item_type,
2342 itemkey => l_wf_item_key,
2343 aname => 'SCHEDULE_INFO' );
2344
2345 l_freq := WF_ENGINE.GetItemAttrText (
2346 itemtype => l_item_type,
2347 itemkey => l_wf_item_key,
2348 aname => 'SCHEDULE_FREQ' );
2349
2350 IF ((l_unit IS NOT NULL) AND (l_unit <> 'ONCE')) THEN
2351 IF (l_run_date IS NOT NULL) THEN
2352
2353 l_sys_next_run_date := WF_ENGINE.GetItemAttrText (
2354 itemtype => l_item_type,
2355 itemkey => l_wf_item_key,
2356 aname => 'NEXT_RUN_DATE' );
2357
2358 l_server_date := to_date(l_sys_next_run_date, 'DD/MM/RRRR HH24:MI:SS');
2359
2360 /*
2361 l_client_date := fnd_timezones_pvt.adjust_datetime(
2362 date_time => l_server_date
2363 ,from_tz => l_server_code
2364 ,to_tz => l_client_code
2365 );
2366 */
2367
2368 l_client_date := Adjust_Datetime
2369 ( p_date_time => l_server_date
2370 , p_from_tz => l_server_code
2371 , p_to_tz => l_client_code
2372 ) ;
2373
2374 l_next_run_date := to_char(l_client_date, l_date_format);
2375
2376 l_alert_message := l_alert_message || BIS_UTILITIES_PVT.Get_FND_Message(
2377 p_message_name => 'BIS_ALERT_SCHEDULE_MSG1'
2378 ,p_msg_param1 => 'NEXT_RUN_DATE'
2379 ,p_msg_param1_val => l_next_run_date
2380 );
2381 END IF;
2382
2383 IF (l_freq IS NOT NULL) THEN
2384 l_alert_message := l_alert_message || BIS_UTILITIES_PVT.Get_FND_Message(
2385 p_message_name => 'BIS_ALERT_SCHEDULE_MSG2'
2386 ,p_msg_param1 => 'SCHEDULE_INFO'
2387 ,p_msg_param1_val => l_freq
2388 );
2389 END IF;
2390 ELSE
2391 l_alert_message := l_alert_message || BIS_UTILITIES_PVT.Get_FND_Message(
2392 p_message_name => 'BIS_ALERT_NOT_REPEAT'
2393 );
2394 END IF;
2395
2396 l_alert_message := l_alert_message || BIS_UTILITIES_PVT.Get_FND_Message(
2397 p_message_name => 'BIS_ALERT_SCHEDULE_MSG3'
2398 );
2399
2400 l_hdr_support := wf_core.translate('WF_HEADER_ATTR');
2401
2402 l_alert := WF_ENGINE.GetItemAttrText (
2403 itemtype => l_item_type,
2404 itemkey => l_wf_item_key,
2405 aname => 'ALERT_BASED_ON' );
2406
2407 IF (l_alert = BIS_CONCURRENT_MANAGER_PVT.C_ALERT_BASED_ON_CHANGE) THEN
2408 l_alert_attribute := 'L_CHANGE';
2409 l_alert_attribute_label := 'L_CHANGE_LABEL';
2410 ELSE
2411 l_alert_attribute := 'L_TARGET';
2412 l_alert_attribute_label := 'L_TARGET_LABEL';
2413 END IF;
2414
2415 IF (content_type = Wf_Notification.doc_html) THEN
2416
2417 l_document := l_document || '<body>' ;
2418
2419 l_document := l_document || Get_Style_Class;
2420
2421 l_document := l_document || Get_Header(
2422 p_item_type => l_item_type,
2423 p_wf_item_key => l_wf_item_key,
2424 p_attribute_name => 'L_ALERT_DETAILS_LABEL');
2425
2426 l_document := l_document || '<span class="label">';
2427
2428 l_label := WF_ENGINE.GetItemAttrText (
2429 itemtype => l_item_type,
2430 itemkey => l_wf_item_key,
2431 aname => 'L_ALERT_MSG' );
2432
2433 l_document := l_document || l_label;
2434 l_document := l_document || '</span><br>' ;
2435 l_document := l_document || '<br>' ;
2436 l_document := l_document || '<table valign="top" cellpadding="0" cellspacing="0" border="0">' ;
2437
2438 l_document := l_document || '<tr>' ;
2439
2440
2441 l_document := l_document || '<td valign="top">' ;
2442 l_document := l_document || '<table border="0" cellspacing="0" cellpadding="1">' ;
2443
2444 IF (l_hdr_support <> 'Y') THEN
2445 l_document := l_document || '<tr>' ;
2446 l_document := l_document || Get_Label(
2447 p_item_type => l_item_type,
2448 p_wf_item_key => l_wf_item_key,
2449 p_attribute_name => 'L_PERFORMANCE_MEASURE_LABEL');
2450 l_document := l_document || Get_Text(
2451 p_item_type => l_item_type,
2452 p_wf_item_key => l_wf_item_key,
2453 p_attribute_name => 'L_PERFORMANCE_MEASURE');
2454
2455 l_document := l_document || '</tr>' ;
2456
2457 END IF;
2458
2459 l_document := l_document || '<tr>' ;
2460 l_document := l_document || Get_Label(
2461 p_item_type => l_item_type,
2462 p_wf_item_key => l_wf_item_key,
2463 p_attribute_name => 'L_UNITOFMEASURE_LABEL');
2464
2465 l_document := l_document || Get_Text(
2466 p_item_type => l_item_type,
2467 p_wf_item_key => l_wf_item_key,
2468 p_attribute_name => 'L_UNITOFMEASURE');
2469
2470 l_document := l_document || '</tr>' ;
2471
2472 l_document := l_document || '<tr>' ;
2473 l_document := l_document || Get_Label(
2474 p_item_type => l_item_type,
2475 p_wf_item_key => l_wf_item_key,
2476 p_attribute_name => l_alert_attribute_label);
2477
2478 l_document := l_document || Get_Text(
2479 p_item_type => l_item_type,
2480 p_wf_item_key => l_wf_item_key,
2481 p_attribute_name => l_alert_attribute);
2482 l_document := l_document || '</tr>' ;
2483
2484 IF (l_hdr_support <> 'Y') THEN
2485 l_document := l_document || '<tr>' ;
2486 l_document := l_document || Get_Label(
2487 p_item_type => l_item_type,
2488 p_wf_item_key => l_wf_item_key,
2489 p_attribute_name => 'L_ACTUAL_LABEL');
2490
2491 l_document := l_document || Get_Text(
2492 p_item_type => l_item_type,
2493 p_wf_item_key => l_wf_item_key,
2494 p_attribute_name => 'L_ACTUAL');
2495
2496 l_document := l_document || '</tr>' ;
2497 END IF;
2498
2499 l_document := l_document || '<tr>' ;
2500 l_document := l_document || Get_Label(
2501 p_item_type => l_item_type,
2502 p_wf_item_key => l_wf_item_key,
2503 p_attribute_name => 'L_TOLERANCE_RANGE_1_LABEL');
2504
2505 l_document := l_document || Get_Text(
2506 p_item_type => l_item_type,
2507 p_wf_item_key => l_wf_item_key,
2508 p_attribute_name => 'L_TOLERANCE_RANGE_1');
2509
2510 l_document := l_document || '</tr>' ;
2511 l_document := l_document || '<tr>' ;
2512
2513 l_document := l_document || Get_Label(
2514 p_item_type => l_item_type,
2515 p_wf_item_key => l_wf_item_key,
2516 p_attribute_name => 'L_TOLERANCE_RANGE_2_LABEL');
2517
2518 l_document := l_document || Get_Text(
2519 p_item_type => l_item_type,
2520 p_wf_item_key => l_wf_item_key,
2521 p_attribute_name => 'L_TOLERANCE_RANGE_2');
2522
2523 l_document := l_document || '</tr>' ;
2524 l_document := l_document || '<tr>' ;
2525 l_document := l_document || Get_Label(
2526 p_item_type => l_item_type,
2527 p_wf_item_key => l_wf_item_key,
2528 p_attribute_name => 'L_TOLERANCE_RANGE_3_LABEL');
2529
2530 l_document := l_document || Get_Text(
2531 p_item_type => l_item_type,
2532 p_wf_item_key => l_wf_item_key,
2533 p_attribute_name => 'L_TOLERANCE_RANGE_3');
2534
2535 l_document := l_document || '</tr>' ;
2536 l_document := l_document || '<tr>' ;
2537 l_document := l_document || '<td><SPACER TYPE=horizonal SIZE=4></td>' ;
2538 l_document := l_document || '</tr>' ;
2539
2540 l_document := l_document || '</table><br>' ;
2541
2542 l_document := l_document || '</td><td width="5%"></td>' ;
2543 l_document := l_document || '<td valign="top" nowrap>' ;
2544 l_document := l_document || '<table border="0" cellspacing="0" cellpadding="1">' ;
2545 l_document := l_document || '<tr>' ;
2546 l_document := l_document || Get_Label(
2547 p_item_type => l_item_type,
2548 p_wf_item_key => l_wf_item_key,
2549 p_attribute_name => 'L_RUN_DATE_LABEL');
2550
2551 l_document := l_document || '<td VALIGN=TOP NOWRAP><span class="text">' ;
2552 l_document := l_document || ' ' ;
2553 l_document := l_document || l_run_date;
2554 l_document := l_document || '</span></td>' ;
2555 l_document := l_document || '</tr>' ;
2556 l_document := l_document || '<tr nowrap>' ;
2557 l_document := l_document || Get_Label(
2558 p_item_type => l_item_type,
2559 p_wf_item_key => l_wf_item_key,
2560 p_attribute_name => 'BIS_DIMENSION_REGION_LABEL');
2561
2562 l_document := l_document || '<td nowrap>' ;
2563 l_document := l_document || '<span class="text">';
2564
2565 l_label := WF_ENGINE.GetItemAttrText (
2566 itemtype => l_item_type,
2567 itemkey => l_wf_item_key,
2568 aname => 'BIS_DIMENSION_REGION' );
2569
2570 l_document := l_document || l_label;
2571 l_document := l_document || '</span>';
2572 l_document := l_document || '</td>' ;
2573 l_document := l_document || '</tr>' ;
2574 l_document := l_document || '</table>' ;
2575 l_document := l_document || '</td>' ;
2576 l_document := l_document || '</tr>' ;
2577 l_document := l_document || '</table><br>' ;
2578
2579 l_document := l_document || Get_Header(
2580 p_item_type => l_item_type,
2581 p_wf_item_key => l_wf_item_key,
2582 p_attribute_name => 'L_RELATED_INFO_LABEL');
2583
2584 WF_NOTIFICATION.WriteToClob(document, l_document);
2585
2586 Generate_Report(document_id,
2587 content_type,
2588 document,
2589 document_type);
2590
2591 l_document := '';
2592
2593 l_document := l_document || Get_Header(
2594 p_item_type => l_item_type,
2595 p_wf_item_key => l_wf_item_key,
2596 p_attribute_name =>'L_ALERT_RECIPIENTS_LABEL');
2597
2598 l_document := l_document || '<span class="label">';
2599
2600 l_label := WF_ENGINE.GetItemAttrText (
2601 itemtype => l_item_type,
2602 itemkey => l_wf_item_key,
2603 aname => 'L_ALERT_RECIPIENTS_MSG' );
2604
2605 l_document := l_document || l_label;
2606 l_document := l_document || '</span>';
2607 l_document := l_document || '<br><SPACER TYPE=horizonal SIZE=16>' ;
2608 l_document := l_document || '<br>' ;
2609 l_document := l_document || '<span class="label">';
2610
2611 l_label := WF_ENGINE.GetItemAttrText (
2612 itemtype => l_item_type,
2613 itemkey => l_wf_item_key,
2614 aname => 'L_RECEPIENT' );
2615
2616 l_document := l_document || l_label;
2617 l_document := l_document || '</span>';
2618
2619 l_document := l_document || Get_Header(
2620 p_item_type => l_item_type,
2621 p_wf_item_key => l_wf_item_key,
2622 p_attribute_name => 'L_ALERT_SCHEDULE');
2623
2624 l_document := l_document || '<span class="label">';
2625
2626 l_document := l_document || l_alert_message;
2627
2628 l_label := WF_ENGINE.GetItemAttrText (
2629 itemtype => l_item_type,
2630 itemkey => l_wf_item_key,
2631 aname => 'L_ALERT_SCHEDULE_MSG' );
2632
2633 l_document := l_document || l_label;
2634 l_document := l_document || '</span>';
2635 l_document := l_document || '<br>' ;
2636
2637 l_document := l_document || '</body>' ;
2638
2639 WF_NOTIFICATION.WriteToClob(document, l_document);
2640
2641 ELSIF (content_type = Wf_Notification.Doc_Text) THEN
2642
2643 l_label := WF_ENGINE.GetItemAttrText (
2644 itemtype => l_item_type,
2645 itemkey => l_wf_item_key,
2646 aname => 'L_ALERT_DETAILS_LABEL' );
2647
2648
2649 l_document := l_document || l_label ;
2650 l_document := l_document || l_NL ;
2651 l_document := l_document || '---------------------------------------------------------------------------' ;
2652 l_document := l_document || l_NL ;
2653
2654 l_label := WF_ENGINE.GetItemAttrText (
2655 itemtype => l_item_type,
2656 itemkey => l_wf_item_key,
2657 aname => 'L_ALERT_MSG' );
2658
2659 l_document := l_document || l_label;
2660 l_document := l_document || l_NL ;
2661 l_document := l_document || l_NL ;
2662
2663 l_label := WF_ENGINE.GetItemAttrText (
2664 itemtype => l_item_type,
2665 itemkey => l_wf_item_key,
2666 aname => 'L_RUN_DATE_LABEL' );
2667
2668 l_document := l_document || l_label;
2669 l_document := l_document || ' : ' ;
2670
2671 l_document := l_document || l_run_date;
2672 l_document := l_document || l_NL ;
2673
2674 l_label := WF_ENGINE.GetItemAttrText (
2675 itemtype => l_item_type,
2676 itemkey => l_wf_item_key,
2677 aname => 'BIS_DIMENSION_REGION_TXT' );
2678
2679 l_document := l_document || l_label;
2680 l_document := l_document || l_NL ;
2681
2682 l_label := WF_ENGINE.GetItemAttrText (
2683 itemtype => l_item_type,
2684 itemkey => l_wf_item_key,
2685 aname => 'L_PERFORMANCE_MEASURE_LABEL' );
2686
2687 l_document := l_document || l_label;
2688 l_document := l_document || ' : ';
2689
2690 l_label := WF_ENGINE.GetItemAttrText (
2691 itemtype => l_item_type,
2692 itemkey => l_wf_item_key,
2693 aname => 'L_PERFORMANCE_MEASURE' );
2694
2695 l_document := l_document || l_label;
2696 l_document := l_document || l_NL ;
2697
2698 l_label := WF_ENGINE.GetItemAttrText (
2699 itemtype => l_item_type,
2700 itemkey => l_wf_item_key,
2701 aname => 'L_UNITOFMEASURE_LABEL' );
2702
2703 l_document := l_document || l_label;
2704 l_document := l_document || ' : ' ;
2705
2706 l_label := WF_ENGINE.GetItemAttrText (
2707 itemtype => l_item_type,
2708 itemkey => l_wf_item_key,
2709 aname => 'L_UNITOFMEASURE' );
2710
2711 l_document := l_document || l_label;
2712 l_document := l_document || l_NL ;
2713
2714 l_label := WF_ENGINE.GetItemAttrText (
2715 itemtype => l_item_type,
2716 itemkey => l_wf_item_key,
2717 aname => l_alert_attribute_label );
2718
2719 l_document := l_document || l_label;
2720
2721 l_document := l_document || ' : ' ;
2722
2723 l_label := WF_ENGINE.GetItemAttrText (
2724 itemtype => l_item_type,
2725 itemkey => l_wf_item_key,
2726 aname => l_alert_attribute );
2727
2728 l_document := l_document || l_label;
2729 l_document := l_document || l_NL ;
2730
2731 l_label := WF_ENGINE.GetItemAttrText (
2732 itemtype => l_item_type,
2733 itemkey => l_wf_item_key,
2734 aname => 'L_ACTUAL_LABEL' );
2735
2736 l_document := l_document || l_label;
2737 l_document := l_document || ' : ' ;
2738
2739 l_label := WF_ENGINE.GetItemAttrText (
2740 itemtype => l_item_type,
2741 itemkey => l_wf_item_key,
2742 aname => 'L_ACTUAL' );
2743
2744 l_document := l_document || l_label;
2745 l_document := l_document || l_NL ;
2746
2747 l_label := WF_ENGINE.GetItemAttrText (
2748 itemtype => l_item_type,
2749 itemkey => l_wf_item_key,
2750 aname => 'L_TOLERANCE_RANGE_1_LABEL' );
2751
2752 l_document := l_document || l_label;
2753 IF ( l_label IS NOT NULL ) THEN
2754 l_document := l_document || ' : ' ;
2755 END IF;
2756
2757 l_label := WF_ENGINE.GetItemAttrText (
2758 itemtype => l_item_type,
2759 itemkey => l_wf_item_key,
2760 aname => 'L_TOLERANCE_RANGE_1' );
2761
2762 l_document := l_document || l_label;
2763 l_document := l_document || l_NL ;
2764
2765 l_label := WF_ENGINE.GetItemAttrText (
2766 itemtype => l_item_type,
2767 itemkey => l_wf_item_key,
2768 aname => 'L_TOLERANCE_RANGE_2_LABEL' );
2769
2770 l_document := l_document || l_label;
2771 IF ( l_label IS NOT NULL ) THEN
2772 l_document := l_document || ' : ' ;
2773 END IF;
2774
2775 l_label := WF_ENGINE.GetItemAttrText (
2776 itemtype => l_item_type,
2777 itemkey => l_wf_item_key,
2778 aname => 'L_TOLERANCE_RANGE_2' );
2779
2780 l_document := l_document || l_label;
2781 l_document := l_document || l_NL ;
2782
2783 l_label := WF_ENGINE.GetItemAttrText (
2784 itemtype => l_item_type,
2785 itemkey => l_wf_item_key,
2786 aname => 'L_TOLERANCE_RANGE_3_LABEL' );
2787
2788 l_document := l_document || l_label;
2789 IF ( l_label IS NOT NULL ) THEN
2790 l_document := l_document || ' : ' ;
2791 END IF;
2792
2793 l_label := WF_ENGINE.GetItemAttrText (
2794 itemtype => l_item_type,
2795 itemkey => l_wf_item_key,
2796 aname => 'L_TOLERANCE_RANGE_3' );
2797
2798 l_document := l_document || l_label;
2799 l_document := l_document || l_NL ;
2800
2801 l_label := WF_ENGINE.GetItemAttrText (
2802 itemtype => l_item_type,
2803 itemkey => l_wf_item_key,
2804 aname => 'L_RELATED_INFO_LABEL' );
2805
2806 l_document := l_document || l_label;
2807 l_document := l_document || l_NL ;
2808 l_document := l_document || '---------------------------------------------------------------------------' ;
2809 l_document := l_document || l_NL ;
2810
2811 l_label := WF_ENGINE.GetItemAttrText (
2812 itemtype => l_item_type,
2813 itemkey => l_wf_item_key,
2814 aname => 'L_RELATE_INFO_LABEL' );
2815
2816 l_document := l_document || l_label;
2817
2818 l_document := l_document || ' ';
2819
2820 l_label := WF_ENGINE.GetItemAttrText (
2821 itemtype => l_item_type,
2822 itemkey => l_wf_item_key,
2823 aname => 'L_RELATED_INFORMATION_TXT' );
2824
2825
2826 l_document := l_document || l_label;
2827 l_document := l_document || l_NL ;
2828 l_document := l_document || l_NL ;
2829 l_document := l_document || l_NL ;
2830
2831 l_label := WF_ENGINE.GetItemAttrText (
2832 itemtype => l_item_type,
2833 itemkey => l_wf_item_key,
2834 aname => 'L_ALERT_RECIPIENTS_LABEL' );
2835
2836
2837 l_document := l_document || l_label;
2838 l_document := l_document || l_NL ;
2839 l_document := l_document || '---------------------------------------------------------------------------' ;
2840 l_document := l_document || l_NL ;
2841
2842 l_label := WF_ENGINE.GetItemAttrText (
2843 itemtype => l_item_type,
2844 itemkey => l_wf_item_key,
2845 aname => 'L_ALERT_RECIPIENTS_MSG' );
2846
2847
2848 l_document := l_document || l_label;
2849 l_document := l_document || l_NL ;
2850 l_document := l_document || l_NL ;
2851 l_document := l_document || l_NL ;
2852
2853 l_label := WF_ENGINE.GetItemAttrText (
2854 itemtype => l_item_type,
2855 itemkey => l_wf_item_key,
2856 aname => 'L_RECEPIENT_TXT' );
2857
2858
2859 l_document := l_document || l_label;
2860 l_document := l_document || l_NL ;
2861 l_document := l_document || l_NL ;
2862 l_document := l_document || l_NL ;
2863
2864 l_label := WF_ENGINE.GetItemAttrText (
2865 itemtype => l_item_type,
2866 itemkey => l_wf_item_key,
2867 aname => 'L_ALERT_SCHEDULE' );
2868
2869
2870 l_document := l_document || l_label;
2871 l_document := l_document || l_NL ;
2872 l_document := l_document || '---------------------------------------------------------------------------' ;
2873 l_document := l_document || l_NL ;
2874
2875 l_document := l_document || l_alert_message;
2876
2877 l_label := WF_ENGINE.GetItemAttrText (
2878 itemtype => l_item_type,
2879 itemkey => l_wf_item_key,
2880 aname => 'L_ALERT_SCHEDULE_MSG_TXT' );
2881
2882 l_document := l_document || l_label;
2883 l_document := l_document || l_NL ;
2884
2885 WF_NOTIFICATION.WriteToClob(document, l_document);
2886
2887 END IF; -- Check for content type
2888
2889 End GenerateAlerts;
2890 --=============================================================================
2891
2892 PROCEDURE Generate_Report
2893 ( document_id IN VARCHAR2,
2894 content_type IN VARCHAR2,
2895 document IN OUT NOCOPY CLOB,
2896 document_type IN OUT NOCOPY VARCHAR2)
2897 IS
2898
2899 l_document VARCHAR2(32000);
2900 l_item_type VARCHAR2(300) := document_id;
2901 l_wf_item_key WF_ITEM_ATTRIBUTE_VALUES.ITEM_KEY%TYPE := '';
2902 l_document_id NUMBER ;
2903 l_label VARCHAR2(32000);
2904 vHTMLPieces utl_http.html_pieces;
2905 l_html_pieces varchar2(32000);
2906
2907 BEGIN
2908 l_document := '';
2909 l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
2910 l_wf_item_key := substr(document_id, instr(document_id, ':') + 1,
2911 length(document_id) - 2);
2912
2913 l_label := WF_ENGINE.GetItemAttrText (
2914 itemtype => l_item_type,
2915 itemkey => l_wf_item_key,
2916 aname => 'L_REPORT_URL' );
2917
2918 IF (l_label IS NULL OR l_label = '') THEN
2919 l_label := WF_ENGINE.GetItemAttrText (
2920 itemtype => l_item_type,
2921 itemkey => l_wf_item_key,
2922 aname => 'L_RELATE_INFO_LABEL' );
2923
2924 l_document := l_document || '<span class="label">';
2925
2926 l_document := l_document || l_label;
2927
2928 l_document := l_document || ' ';
2929
2930 l_label := WF_ENGINE.GetItemAttrText (
2931 itemtype => l_item_type,
2932 itemkey => l_wf_item_key,
2933 aname => 'L_RELATED_INFORMATION' );
2934
2935 l_document := l_document || l_label;
2936 l_document := l_document || '</span>';
2937
2938 WF_NOTIFICATION.WriteToClob(document,l_document);
2939
2940 ELSE
2941
2942 vHTMLPieces := utl_http.request_pieces(url => l_label,
2943 max_pieces => 32000);
2944
2945 FOR i IN 1 .. vHTMLPieces.count loop
2946
2947 l_html_pieces := vHTMLpieces(i);
2948 WF_NOTIFICATION.WriteToClob(document,l_html_pieces);
2949 END LOOP;
2950
2951 END IF;
2952
2953 EXCEPTION
2954 when others then
2955 l_document := sqlerrm||' Inside Exception';
2956 End Generate_Report;
2957
2958 --=============================================================================
2959
2960 FUNCTION Get_Line
2961 RETURN VARCHAR2 IS
2962
2963 l_line VARCHAR2(500);
2964
2965 BEGIN
2966 l_line := '<TABLE CELLSPACING=0 CELLPADDING=0 BORDER=0 WIDTH="100%">
2967 <TR BGCOLOR="#cccc99"><TD><IMG SRC="http://qapache.us.oracle.com:26700/OA_MEDIA/bisspace.gif" WIDTH="1" HEIGHT="1" BORDER="0"
2968 ALT="------------------------------------------------------------"></TD></TR></TABLE>' ;
2969 RETURN l_line;
2970 END Get_Line;
2971
2972 FUNCTION Get_Style_Class
2973 RETURN VARCHAR2 IS
2974
2975 l_style VARCHAR2(500);
2976
2977 BEGIN
2978 l_style := '<STYLE TYPE="text/css" >
2979 <!--
2980 p .header {font-family: Arial; font-size:13.0pt;font-weight:bold; color:#6699cc}
2981 -->
2982 <!--
2983 p .label {font-size:10.0pt; font-family:Arial; color:#000000}
2984 -->
2985 <!--
2986 p .text {font-size:10.0pt; font-family:Arial; font-weight:bold; color:#000000}
2987 -->
2988 </STYLE>' ;
2989 RETURN l_style;
2990 END Get_Style_Class;
2991
2992 FUNCTION Get_Header(
2993 p_item_type IN VARCHAR2
2994 ,p_wf_item_key IN WF_ITEM_ATTRIBUTE_VALUES.ITEM_KEY%TYPE
2995 ,p_attribute_name IN VARCHAR2
2996 ) RETURN VARCHAR2 IS
2997
2998 l_header_html VARCHAR2(1000);
2999 l_label VARCHAR2(32000);
3000
3001 BEGIN
3002 l_header_html := '' ;
3003 l_header_html := l_header_html || '<p><span class="header">' ;
3004
3005 l_label := WF_ENGINE.GetItemAttrText (
3006 itemtype => p_item_type,
3007 itemkey => p_wf_item_key,
3008 aname => p_attribute_name );
3009
3010 l_header_html := l_header_html || l_label ;
3011 l_header_html := l_header_html || '</span>';
3012
3013 l_header_html := l_header_html || Get_Line;
3014
3015 RETURN l_header_html;
3016 END Get_Header;
3017
3018 FUNCTION Get_Label(
3019 p_item_type IN VARCHAR2
3020 ,p_wf_item_key IN WF_ITEM_ATTRIBUTE_VALUES.ITEM_KEY%TYPE
3021 ,p_attribute_name IN VARCHAR2
3022 ) RETURN VARCHAR2 IS
3023
3024 l_label_html VARCHAR2(1000);
3025 l_label VARCHAR2(32000);
3026
3027 BEGIN
3028 l_label_html := '';
3029
3030 l_label := WF_ENGINE.GetItemAttrText (
3031 itemtype => p_item_type,
3032 itemkey => p_wf_item_key,
3033 aname => p_attribute_name );
3034
3035 IF (l_label IS NOT NULL) THEN
3036 l_label_html := l_label_html || '<td valign="top" nowrap>';
3037 l_label_html := l_label_html || '<div align=right>';
3038 l_label_html := l_label_html || '<span class="label">';
3039 l_label_html := l_label_html || l_label;
3040 l_label_html := l_label_html || '</span>';
3041 l_label_html := l_label_html || '<SPACER TYPE=horizonal SIZE=4></div>' ;
3042 l_label_html := l_label_html || '</td>' ;
3043 END IF;
3044
3045 RETURN l_label_html;
3046
3047 END Get_Label;
3048
3049 FUNCTION Get_Text(
3050 p_item_type IN VARCHAR2
3051 ,p_wf_item_key IN WF_ITEM_ATTRIBUTE_VALUES.ITEM_KEY%TYPE
3052 ,p_attribute_name IN VARCHAR2
3053 ) RETURN VARCHAR2 IS
3054
3055 l_text_html VARCHAR2(1000);
3056 l_label VARCHAR2(32000);
3057
3058 BEGIN
3059 l_text_html := '';
3060
3061 l_label := WF_ENGINE.GetItemAttrText (
3062 itemtype => p_item_type,
3063 itemkey => p_wf_item_key,
3064 aname => p_attribute_name );
3065
3066 IF (l_label IS NOT NULL) THEN
3067 l_text_html := l_text_html || '<td>' ;
3068 l_text_html := l_text_html || '<span class="text">';
3069 l_text_html := l_text_html || ' ' ;
3070 l_text_html := l_text_html || l_label;
3071 l_text_html := l_text_html || '</span>';
3072 l_text_html := l_text_html || '</td>' ;
3073 END IF;
3074
3075 RETURN l_text_html;
3076
3077 END Get_Text;
3078
3079 FUNCTION Adjust_Datetime
3080 ( p_date_time IN DATE
3081 , p_from_tz IN VARCHAR2
3082 , p_to_tz IN VARCHAR2
3083 ) RETURN DATE
3084 IS
3085 l_db_ver NUMBER;
3086 l_sql_stmt VARCHAR2(400);
3087 l_client_date DATE;
3088
3089 BEGIN
3090 l_db_ver := BIS_UTILITIES_PUB.Get_DB_Version;
3091 l_client_date := p_date_time;
3092
3093 l_sql_stmt := 'begin :1 := fnd_timezones_pvt.adjust_datetime(:2, :3, :4); end;';
3094
3095 IF l_db_ver > 8 THEN
3096 execute immediate l_sql_stmt using OUT l_client_date , IN p_date_time, IN p_from_tz, IN p_to_tz;
3097 END IF;
3098
3099 /*
3100 l_client_date := fnd_timezones_pvt.adjust_datetime(
3101 date_time => p_date_time
3102 ,from_tz => p_from_tz
3103 ,to_tz => p_to_tz
3104 );
3105 */
3106
3107 RETURN l_client_date;
3108
3109 END Adjust_Datetime;
3110
3111 END BIS_CORRECTIVE_ACTION_PVT;