eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.PA_PERF_NOTIFICATION_PKG

Source


1 PACKAGE BODY PA_PERF_NOTIFICATION_PKG AS
2 /* $Header: PAPFNTFB.pls 120.2.12000000.2 2007/04/26 17:15:05 vvjoshi ship $ */
3 
4 /*====================================================================
5 This API starts the WorkFlow to send e-mail Notification for exception
6 reporting.
7 =====================================================================*/
8 PROCEDURE START_PERF_NOTIFICATION_WF(
9              p_item_type	In	VARCHAR2
10 	    ,p_process_name	In	VARCHAR2
11 	    ,p_project_id	In	pa_projects_all.project_id%TYPE
12 	    ,x_item_key	        Out	NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
13 	    ,x_return_status	Out     NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
14 	    ,x_msg_count 	Out     NOCOPY NUMBER --File.Sql.39 bug 4440895
15             ,x_msg_data 	Out     NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
16 AS
17 
18 l_item_key                      NUMBER;
19 l_wf_type_code                  pa_wf_processes.wf_type_code%TYPE;
20 l_entity_key1                   pa_wf_processes.entity_key1%TYPE;
21 l_entity_key2                   pa_wf_processes.entity_key2%TYPE;
22 
23 l_msg_count                     NUMBER := 0;
24 l_data                          VARCHAR2(2000);
25 l_msg_data                      VARCHAR2(2000);
26 l_msg_index_out                 NUMBER;
27 l_debug_mode                    VARCHAR2(1);
28 l_err_code                      NUMBER;
29 l_err_stage                     VARCHAR2(30);
30 l_err_stack                     VARCHAR2(240);
31 
32 l_debug_level2                  CONSTANT NUMBER := 2;
33 l_debug_level3                  CONSTANT NUMBER := 3;
34 l_debug_level4                  CONSTANT NUMBER := 4;
35 l_debug_level5                  CONSTANT NUMBER := 5;
36 
37 l_module_name                   VARCHAR2(100) := 'pa.plsql.START_PERF_NOTIFICATION_WF';
38 Invalid_Arg_Exc_CI              Exception;
39 
40 BEGIN
41 
42      x_msg_count := 0;
43      x_return_status := FND_API.G_RET_STS_SUCCESS;
44      l_debug_mode  := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
45 
46      IF l_debug_mode = 'Y' THEN
47           pa_debug.set_curr_function( p_function   => 'START_PERF_NOTIFICATION_WF',
48                                       p_debug_mode => l_debug_mode );
49      END IF;
50 
51      -- Check for business rules violations
52 
53      IF l_debug_mode = 'Y' THEN
54           pa_debug.g_err_stage:= 'Validating input parameters';
55           pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
56           pa_debug.g_err_stage:= 'p_item_type = '|| p_item_type;
57           pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
58           pa_debug.g_err_stage:= 'p_process_name = '|| p_process_name;
59           pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
60           pa_debug.g_err_stage:= 'p_project_id = '|| p_project_id;
61           pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
62      END IF;
63 
64      IF (p_item_type IS NULL) OR
65         (p_process_name IS NULL) OR
66         (p_project_id IS NULL)
67      THEN
68           PA_UTILS.ADD_MESSAGE
69                 (p_app_short_name  => 'PA',
70                  p_msg_name        => 'PA_INV_PARAM_PASSED');
71           RAISE Invalid_Arg_Exc_CI;
72      END IF;
73 
74      l_wf_type_code := 'PERF_NOTIFICATION';
75      l_entity_key1  := p_project_id;
76      l_entity_key2  := -99;
77 
78      -- Get the item key from sequence.
79      SELECT pa_workflow_itemkey_s.nextval
80      INTO l_item_key
81      FROM dual;
82 
83      x_item_key := To_char(l_item_key);
84 
85 
86      IF l_debug_mode = 'Y' THEN
87           pa_debug.g_err_stage:= 'x_item_key = '|| x_item_key;
88           pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
89      END IF;
90 
91      -- create the workflow process
92      WF_ENGINE.CreateProcess( itemtype => p_item_type
93 	                     ,itemkey  => x_item_key
94 	                     ,process  => p_process_name);
95 
96 
97      -- The following API will set all the required attributes for the workflow to function.
98      set_perf_notify_wf_attributes(
99                 p_item_type         => p_item_type
100                ,p_process_name      => p_process_name
101                ,p_project_id        => p_project_id
102                ,p_item_key          => x_item_key
103                ,x_return_status     => x_return_status
104                ,x_msg_count         => x_msg_count
105                ,x_msg_data          => x_msg_data
106      );
107 
108      IF (x_return_status <>  FND_API.G_RET_STS_SUCCESS) THEN
109           IF l_debug_mode = 'Y' THEN
110              pa_debug.g_err_stage:= 'Error calling SET_PERF_NOTIFY_WF_ATTRIBUTES';
111              pa_debug.write('START_PERF_NOTIFICATION_WF: ' || l_module_name,pa_debug.g_err_stage,l_debug_level5);
112 
113              PA_UTILS.Add_Message( p_app_short_name => 'PA'
114                                   ,p_msg_name       => 'PA_PR_CREATE_WF_FAILED');
115           END IF;
116           RAISE Invalid_Arg_Exc_CI;
117      END IF;
118 
119      IF l_debug_mode = 'Y' THEN
120           pa_debug.g_err_stage:= 'returned from set_perf_notify_wf_attributes';
121           pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
122      END IF;
123 
124 
125      WF_ENGINE.StartProcess(itemtype => p_item_type
126 	                   ,itemkey  => x_item_key);
127 
128      PA_WORKFLOW_UTILS.Insert_WF_Processes(
129                      p_wf_type_code           => l_wf_type_code
130                     ,p_item_type              => p_item_type
131                     ,p_item_key               => l_item_key
132                     ,p_entity_key1            => l_entity_key1
133                     ,p_entity_key2            => l_entity_key2
134                     ,p_description            => p_process_name
135                     ,p_err_code               => l_err_code
136                     ,p_err_stage              => l_err_stage
137                     ,p_err_stack              => l_err_stack
138                     );
139      IF l_err_code <> 0 THEN
140 	WF_ENGINE.AbortProcess(itemtype => p_Item_Type
141        	                      ,itemkey  => l_Item_Key);
142 
143         --Log an error message and go to exception section.
144         PA_UTILS.Add_Message( p_app_short_name => 'PA'
145  		             ,p_msg_name       => 'PA_PR_CREATE_WF_FAILED');
146 	x_return_status := FND_API.G_RET_STS_ERROR;
147         Raise Invalid_Arg_Exc_CI;
148      END IF;
149 
150 
151      IF l_debug_mode = 'Y' THEN
152         pa_debug.g_err_stage:= 'Exiting START_PERF_NOTIFICATION_WF';
153         pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
154         pa_debug.reset_curr_function;
155      END IF;
156 
157 EXCEPTION
158 
159 WHEN Invalid_Arg_Exc_CI THEN
160      x_return_status := FND_API.G_RET_STS_ERROR;
161      l_msg_count := FND_MSG_PUB.count_msg;
162 
163      IF l_msg_count = 1 and x_msg_data IS NULL THEN
164           PA_INTERFACE_UTILS_PUB.get_messages
165               (p_encoded        => FND_API.G_TRUE
166               ,p_msg_index      => 1
167               ,p_msg_count      => l_msg_count
168               ,p_msg_data       => l_msg_data
169               ,p_data           => l_data
170               ,p_msg_index_out  => l_msg_index_out);
171           x_msg_data := l_data;
172           x_msg_count := l_msg_count;
173      ELSE
174           x_msg_count := l_msg_count;
175      END IF;
176      IF l_debug_mode = 'Y' THEN
177           pa_debug.reset_curr_function;
178      END IF;
179 
180      RETURN;
181 
182 WHEN others THEN
183 
184      x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
185      x_msg_count     := 1;
186      x_msg_data      := SQLERRM;
187 
188      FND_MSG_PUB.add_exc_msg
189                    ( p_pkg_name        => 'PA_PERF_EXCEPTION_PKG'
190                     ,p_procedure_name  => 'START_PERF_NOTIFICATION_WF'
191                     ,p_error_text      => x_msg_data);
192 
193      IF l_debug_mode = 'Y' THEN
194           pa_debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
195           pa_debug.write(l_module_name,pa_debug.g_err_stage,
196                               l_debug_level5);
197           pa_debug.reset_curr_function;
198      END IF;
199      RAISE;
200 END START_PERF_NOTIFICATION_WF;
201 
202 
203 
204 /*==================================================================
205    The required arguments for the workflow are set in this API. This
206    API also identifies to whom the notification has to be sent to.
207  =================================================================*/
208 PROCEDURE SET_PERF_NOTIFY_WF_ATTRIBUTES
209       (  p_item_type	    In		VARCHAR2
210 	,p_process_name	    In		VARCHAR2
211 	,p_project_id	    In		pa_projects_all.project_id%TYPE
212 	,p_item_key	    In		NUMBER
213         ,x_return_status    Out		NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
214         ,x_msg_count	    Out		NOCOPY NUMBER --File.Sql.39 bug 4440895
215         ,x_msg_data	    Out		NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
216 AS
217 
218 l_msg_count                     NUMBER := 0;
219 l_data                          VARCHAR2(2000);
220 l_msg_data                      VARCHAR2(2000);
221 l_msg_index_out                 NUMBER;
222 l_debug_mode                    VARCHAR2(1);
223 l_err_code                      NUMBER;
224 l_err_stage                     VARCHAR2(30);
225 l_err_stack                     VARCHAR2(240);
226 
227 l_debug_level2                  CONSTANT NUMBER := 2;
228 l_debug_level3                  CONSTANT NUMBER := 3;
229 l_debug_level4                  CONSTANT NUMBER := 4;
230 l_debug_level5                  CONSTANT NUMBER := 5;
231 
232 l_module_name                   VARCHAR2(100) := 'pa.plsql.set_perf_notify_wf_attributes';
233 Invalid_Arg_Exc_CI              Exception;
234 
235 l_project_name                  pa_projects_all.name%TYPE;
236 l_project_number                pa_projects_all.segment1%TYPE;
237 l_role                          varchar2(30) := NULL;
238 l_role_display_name             varchar2(30) := NULL; -- Bug 4565156.
239 l_role_users                    varchar2(30000) := NULL;
240 display_name                    VARCHAR2(2000);
241 email_address                   VARCHAR2(2000);
242 notification_preference         VARCHAR2(2000);
243 language                        VARCHAR2(2000);
244 territory                       VARCHAR2(2000);
245 l_priority_name                 pa_lookups.meaning%TYPE;
246 l_object_page_layout_id         NUMBER;
247 l_user_names                    pa_distribution_list_utils.pa_vc_1000_150 := null;
248 l_full_names                    pa_distribution_list_utils.pa_vc_1000_150 := null;
249 l_email_addresses               pa_distribution_list_utils.pa_vc_1000_150 := null;
250 
251 -- This Cursor gets the Pageid and PageType associated with the Automatic Report Type for the Project
252 CURSOR get_page( c_object_id  NUMBER)
253 IS
254   SELECT object_page_layout_id,
255          object_id,
256          object_type,
257          page_name,
258          page_id,
259          page_type_code,
260 	 report_name
261   FROM pa_progress_report_setup_v
262   WHERE object_id = c_object_id
263   AND object_type = 'PA_PROJECTS'
264   AND page_type_code='PPR'
265   AND generation_method='AUTOMATIC';
266 
267 
268 
269 BEGIN
270 
271      x_msg_count := 0;
272      x_return_status := FND_API.G_RET_STS_SUCCESS;
273      l_debug_mode  := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
274 
275      IF l_debug_mode = 'Y' THEN
276           pa_debug.set_curr_function( p_function   => 'set_perf_notify_wf_attributes',
277                                       p_debug_mode => l_debug_mode );
278      END IF;
279 
280      -- Check for business rules violations
281 
282      IF l_debug_mode = 'Y' THEN
283           pa_debug.g_err_stage:= 'Validating input parameters';
284           pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
285           pa_debug.g_err_stage:= 'p_item_type = '|| p_item_type;
286           pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level5);
287           pa_debug.g_err_stage:= 'p_process_name = '|| p_process_name;
288           pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level5);
289           pa_debug.g_err_stage:= 'p_project_id = '|| p_project_id;
290           pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level5);
291      END IF;
292 
293      IF (p_item_type IS NULL) OR
294         (p_process_name IS NULL) OR
295         (p_project_id IS NULL)
296      THEN
297 
298           PA_UTILS.ADD_MESSAGE
299                 (p_app_short_name  => 'PA',
300                  p_msg_name        => 'PA_INV_PARAM_PASSED');
301           RAISE Invalid_Arg_Exc_CI;
302      END IF;
303 
304      SELECT name, segment1 INTO l_project_name, l_project_number
305      FROM pa_projects_all
306      WHERE project_id=p_project_id;
307 
308      FOR page_var in get_page(p_project_id)
309      LOOP
310         l_object_page_layout_id := page_var.object_page_layout_id;
311         -- Set the workflow attributes.
312         wf_engine.SetItemAttrNumber( p_item_type
313                                   ,p_item_key
314                                   ,'CONF_OBJECT_ID'
315                                   ,p_project_id);
316 
317 
318         wf_engine.SetItemAttrText( p_item_type
319                                   ,p_item_key
320                                   ,'CONF_OBJECT_TYPE'
321                                   ,page_var.object_type);
322 
323 
324         wf_engine.SetItemAttrNumber( p_item_type
325                                   ,p_item_key
326                                   ,'CONF_PAGE_ID'
327                                   ,page_var.page_id);
328 
329         wf_engine.SetItemAttrText( p_item_type
330                                   ,p_item_key
331                                   ,'CONF_PAGE_TYPE'
332                                   ,page_var.page_type_code);
333 
334         wf_engine.SetItemAttrText( p_item_type
335                                   ,p_item_key
336                                   ,'OBJECT_NAME'
337                                   ,l_project_name);
338 
339         wf_engine.SetItemAttrText( p_item_type
340                                   ,p_item_key
341                                   ,'REPORT_TYPE'
342                                   ,page_var.report_name);
343 
344         wf_engine.SetItemAttrDate( p_item_type
345                                   ,p_item_key
346                                   ,'REP_GEN_DATE'
347                                   ,sysdate);
348 
349         wf_engine.SetItemAttrText( p_item_type
350                                   ,p_item_key
351                                   ,'PROJECT_NUMBER'
352                                   ,l_project_number);
353 
354      END LOOP;
355 
356 
357 
358      PA_DISTRIBUTION_LIST_UTILS.get_dist_list(
359 		   p_object_type => 'PA_OBJECT_PAGE_LAYOUT',
360 		   p_object_id   => l_object_page_layout_id,
361 		   p_access_level => 1,  -- view priv
362 		   x_user_names => l_user_names,
363 		   x_full_names => l_full_names,
364 		   x_email_addresses => l_email_addresses,
365 		   x_return_status => x_return_status,
366 		   x_msg_count => l_msg_count,
367 		   x_msg_data => l_msg_data
368 		   );
369 
370      IF (x_return_status = 'S') THEN
371         IF l_user_names is not null THEN
372           l_role := 'NOTIFY_' ||p_item_type ||p_item_key;
373 	  l_role_display_name := l_role; -- Bug 4565156.
374 
375           WF_DIRECTORY.CreateAdHocRole( role_name         => l_role
376                                      ,role_display_name => l_role_display_name -- Bug 4565156.
377                                      ,expiration_date   => sysdate+1); -- Set expiration_date for bug#5962401
378 
379           FOR i in l_user_names.First..l_user_names.LAST
380           LOOP
381             IF l_user_names(i) IS NULL THEN
382 	      l_user_names(i) := Upper(l_email_addresses(i));
383 	      l_full_names(i) := l_email_addresses(i);
384 	    END IF;
385 
386 	    IF (l_role_users is not null) THEN
387 	       l_role_users := l_role_users || ',';
388             END IF;
389 
390    	    wf_directory.getroleinfo(Upper(l_user_names(i)),
391                                      display_name,
392                                      email_address,
393                                      notification_preference,
394                                      language,
395                                      territory);
396 	    IF display_name is null THEN
397 	       WF_DIRECTORY.CreateAdHocUser( name => l_user_names(i)
398 	 	                           , display_name => l_full_names(i)
399 		 			   , EMAIL_ADDRESS =>l_email_addresses(i));
400 	    END IF;
401 	    l_role_users := l_role_users || l_user_names(i);
402           END LOOP;
403         ELSE
404 	    pa_debug.write_file('LOG','The notification access list do not have any receipients defined to send the performance status notification for the Project with project Id:'||p_project_id);
405         END IF;
406      END IF;
407 
408      IF (l_role_users is NOT NULL) THEN
409          WF_DIRECTORY.AddUsersToAdHocRole( l_role,
410 	 	                           l_role_users);
411 
412 	 wf_engine.SetItemAttrText(  p_item_type
413 	  	                   , p_item_key
414 				   , 'PERFORMER'
415 				   , l_role);
416      END IF;
417 
418      IF l_debug_mode = 'Y' THEN
419           pa_debug.g_err_stage:= 'Exiting set_perf_notify_wf_attributes';
420           pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
421           pa_debug.reset_curr_function;
422      END IF;
423 EXCEPTION
424 
425 WHEN Invalid_Arg_Exc_CI THEN
426      x_return_status := FND_API.G_RET_STS_ERROR;
427      l_msg_count := FND_MSG_PUB.count_msg;
428 
429      IF l_msg_count = 1 and x_msg_data IS NULL THEN
430           PA_INTERFACE_UTILS_PUB.get_messages
431               (p_encoded        => FND_API.G_TRUE
432               ,p_msg_index      => 1
433               ,p_msg_count      => l_msg_count
434               ,p_msg_data       => l_msg_data
435               ,p_data           => l_data
436               ,p_msg_index_out  => l_msg_index_out);
437           x_msg_data := l_data;
438           x_msg_count := l_msg_count;
439      ELSE
440           x_msg_count := l_msg_count;
441      END IF;
442      IF l_debug_mode = 'Y' THEN
443           pa_debug.reset_curr_function;
444      END IF;
445 
446      RETURN;
447 
448 WHEN others THEN
449 
450      x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
451      x_msg_count     := 1;
452      x_msg_data      := SQLERRM;
453 
454      FND_MSG_PUB.add_exc_msg
455                    ( p_pkg_name        => 'PA_PERF_EXCEPTION_PKG'
456                     ,p_procedure_name  => 'set_perf_notify_wf_attributes'
457                     ,p_error_text      => x_msg_data);
458 
459      IF l_debug_mode = 'Y' THEN
460           pa_debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
461           pa_debug.write(l_module_name,pa_debug.g_err_stage,
462                               l_debug_level5);
463           pa_debug.reset_curr_function;
464      END IF;
465      RAISE;
466 END set_perf_notify_wf_attributes;
467 
468 END PA_PERF_NOTIFICATION_PKG;