[Home] [Help]
PACKAGE BODY: APPS.PA_WORKFLOW_HISTORY
Source
1 package body pa_workflow_history as
2 /* $Header: PAWFHSUB.pls 120.5.12020000.2 2012/08/10 12:15:30 svmohamm ship $ */
3 /*============================================================================+
4 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
5 | All rights reserved. |
6 | Oracle Manufacturing |
7 +=============================================================================+
8
9 FILE NAME : PAWFHSUB.pls
10 DESCRIPTION :
11
12
13
14 HISTORY : 08/19/02 SYAO Initial Creation
15 05-Aug-2005 raluthra Bug 4527617: Replaced fnd_user.customer_id with
16 fnd_user.person_party_id for R12 ATG Mandate Fix.
17 18-Aug-2005 avaithia Bug 4537865 : NOCOPY Mandate Changes.
18 21-Apr-2006 avaithia Bug 5178531 : SWAN Changes
19 Changed these color codes :
20 replaced #cccc99 with #cfe0f1
21 Replaced #336699 with #3c3c3c
22 Replaced #f7f7e7 with #f2f2f5
23 Default Font Preference made as Tahoma
24 23-Jul-2009 rmandali Bug 7538477 : Modified the PROCEDURE show_history
25 to incorporate the Hijrah/Thai calendar support.
26 12-Oct-2009 rmandali Bug 8974192 : Added a nvl condition for the
27 calendar support.
28 10-Aug-12 svmohamm Bug 14308345: Ci Approval history changes
29 =============================================================================*/
30
31 G_USER_ID CONSTANT NUMBER := FND_GLOBAL.user_id;
32
33
34
35 PROCEDURE save_comment_history(
36 itemtype IN VARCHAR2
37 ,itemkey IN VARCHAR2
38 ,funcmode IN VARCHAR2
39 ,user_name IN VARCHAR2
40 ,comment IN varchar2)
41
42 IS
43
44 l_comment VARCHAR2(2000);
45 l_object_id NUMBER;
46 l_project_id NUMBER;
47 l_seq NUMBER;
48 l_full_name VARCHAR2(2000);
49 l_user_name VARCHAR2(100);
50
51 CURSOR get_old_seq
52 IS
53 SELECT MAX(sequence_number)
54 FROM pa_wf_ntf_performers
55 WHERE
56 wf_type_code = 'APPROVAL_FYI'
57 AND item_type = itemtype
58 AND item_key = itemkey
59 AND object_id2 = l_object_id;
60
61 CURSOR get_full_name
62 IS
63 select party_name
64 from (
65 select fu.user_name, hp.party_name, hp.email_address
66 from fnd_user fu,
67 hz_parties hp
68 where fu.person_party_id = hp.party_id -- Bug 4527617. Replaced customer_id with person_party_id.
69 and fu.user_name = l_user_name
70 union all
71 select fu.user_name, papf.full_name, papf.email_address
72 from fnd_user fu,
73 hz_parties hp,
74 per_all_people_f papf
75 where 'PER:' || fu.employee_id = hp.orig_system_reference
76 and fu.user_name = l_user_name
77 and trunc(sysdate)
78 between papf.EFFECTIVE_START_DATE
79 and Nvl(papf.effective_end_date, Sysdate + 1)
80 and papf.person_id = fu.employee_id);
81
82 BEGIN
83
84
85 l_object_id := wf_engine.GetItemAttrNumber
86 ( itemtype => itemtype,
87 itemkey => itemkey,
88 aname => 'WF_OBJECT_ID');
89
90 --debug_msg_s1 ('XXXXXXXXXXXXXX d' || To_char(l_object_id));
91
92 l_project_id := wf_engine.GetItemAttrNumber
93 ( itemtype => itemtype,
94 itemkey => itemkey,
95 aname => 'PROJECT_ID');
96 /*
97 OPEN get_project_id;
98 FETCH get_project_id INTO l_project_id;
99 CLOSE get_project_id;
100 */
101
102
103 OPEN get_old_seq;
104 FETCH get_old_seq INTO l_seq;
105 IF get_old_seq%notfound THEN
106 l_seq := 0;
107 END IF;
108 IF l_seq IS NULL THEN
109 l_seq := 0;
110 END IF;
111
112 CLOSE get_old_seq;
113
114 l_user_name := user_name;
115 OPEN get_full_name ;
116 FETCH get_full_name INTO l_full_name;
117 CLOSE get_full_name;
118
119 --debug_msg_s1 ('Main: Save comment history: ' ||itemtype || ':' || itemkey || ':' || l_object_id || ':' || user_name);
120
121 --debug_msg_s1 ('Main: Save comment history: ' ||itemtype || ':' || itemkey || ':' || l_object_id || ':' || l_full_name);
122
123
124 --debug_msg_s1 ('Main: Save comment history: seq ' || To_char(l_seq +1));
125 --debug_msg_s1 ('Main: Save comment history: func ' || funcmode);
126
127
128
129 INSERT INTO pa_wf_ntf_performers
130 (
131 wf_type_code,
132 item_type,
133 item_key,
134 object_id1,
135 object_id2,
136 user_name,
137 user_type,
138 action_code,
139 action_date,
140 sequence_number,
141 approver_comments
142 )
143 VALUES
144 (
145 'APPROVAL_FYI',
146 itemtype,
147 itemkey,
148 l_project_id,
149 l_object_id,
150 user_name,
151 'RESOURCE',
152 funcmode,
153 Sysdate,
154 l_seq +1 ,
155 comment
156 );
157
158
159 END;
160
161
162 PROCEDURE show_history
163 (document_id IN VARCHAR2,
164 display_type IN VARCHAR2,
165 document IN OUT NOCOPY VARCHAR2, -- 4537865
166 document_type IN OUT NOCOPY VARCHAR2) -- 4537865
167
168 IS
169
170 l_item_type VARCHAR2(30);
171 l_item_key number;
172 l_object_id NUMBER;
173 l_dummy VARCHAR2(400);
174 l_action_date VARCHAR2(60) := NULL;
175
176
177 CURSOR get_history_info IS
178 SELECT DISTINCT
179 pw.sequence_number,
180 wu.display_name user_full_name,
181 pl.meaning action_code,
182 pw.action_date,
183 pw.approver_comments
184 FROM pa_wf_ntf_performers pw, wf_users wu, pa_lookups pl
185 WHERE --pw.wf_type_code = 'APPROVAL_FYI'
186 pw.item_type = l_item_type -- 'PAWFPPRA'
187 AND pw.item_key = l_item_key --32715
188 AND pw.object_id2 = l_object_id --10020
189 and pw.user_name = wu.name
190 and pl.lookup_type = 'PA_WF_APPROVAL_ACTION'
191 and pl.lookup_code = pw.action_code
192 ORDER BY pw.sequence_number ;
193 /*
194 SELECT
195 pw.sequence_number,
196 wu.display_name user_full_name,
197 pw.action_code,
198 pw.action_date,
199 pw.approver_comments
200 FROM pa_wf_ntf_performers pw, wf_users wu
201 WHERE --pw.wf_type_code = 'APPROVAL_FYI'
202 pw.item_type = l_item_type
203 AND pw.item_key = l_item_key
204 AND pw.object_id2 = l_object_id
205 and pw.user_name = wu.name
206 ORDER BY pw.sequence_number ;
207 */
208
209
210 l_index1 NUMBER;
211
212
213 BEGIN
214
215 --debug_msg_s1('AAAAA Project Id ' || document_id);
216 l_index1 := instr(document_id, ':');
217
218 l_item_type := substrb(document_id, 1, l_index1 - 1); -- 4537865 Changed substr to substrb
219
220
221 --debug_msg_s1 ('XXXXXXXXXXXXXX a' || To_char(l_index1));
222
223 --debug_msg_s1 ('XXXXXXXXXXXXXX b' || l_item_type);
224
225 l_item_key := To_number(substrb(document_id, l_index1 +1,
226 Length(document_id)- l_index1)); -- 4537865 Changed substr to substrb
227
228 --debug_msg_s1 ('XXXXXXXXXXXXXX c' || To_char(l_item_key));
229
230
231 l_object_id := wf_engine.GetItemAttrNumber
232 ( itemtype => l_item_type,
233 itemkey => l_item_key,
234 aname => 'WF_OBJECT_ID');
235
236 document := '<table cellpadding="0" cellspacing="0" border="0" width="100%" summary="">'
237 || '<tr><td width="100%" > <font face="Tahoma" color=#3c3c3c class="OraHeaderSub"> '
238 || '<B>Approval History</td></tr></table>' ;
239
240
241
242 document := document ||
243 '<table cellSpacing=1 cellPadding=3 width="90%" border=0 bgColor=white summary=""><tr>
244 <TH class=tableheader width=5% ALIGN=left bgcolor=#cfe0f1><font size="2" color=#3c3c3c face="Tahoma, Arial, Helvetica, Geneva">Sequence</font>
245 </TH> <TH class=tableheader width=35% ALIGN=left bgcolor=#cfe0f1><font size="2" color=#3c3c3c face="Tahoma, Arial, Helvetica, Geneva">Who</font>
246 </TH> <TH class=tableheader width=15% ALIGN=left bgcolor=#cfe0f1><font size="2" color=#3c3c3c face="Tahoma, Arial, Helvetica, Geneva">Action</font>
247 </TH> <TH class=tableheader width = 15% ALIGN=left bgcolor=#cfe0f1><font size="2" color=#3c3c3c face="Tahoma, Arial, Helvetica, Geneva">Date</font>
248 </TH> <TH class=tableheader width=55% ALIGN=left bgcolor=#cfe0f1><font size="2" color=#3c3c3c face="Tahoma, Arial, Helvetica, Geneva">Note</font>
249 </TH></TR> ';
250
251
252 FOR rec IN get_history_info LOOP
253 /* Added for Bug 7538477 Start */
254 if (FND_RELEASE.MAJOR_VERSION = 12 and FND_RELEASE.minor_version >= 1 and FND_RELEASE.POINT_VERSION >= 1 )
255 or (FND_RELEASE.MAJOR_VERSION > 12) then
256 if (display_type = wf_notification.doc_html) then
257 l_action_date := '<BDO DIR="LTR">' ||
258 to_char(rec.action_date ,
259 FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', G_user_id),
260 'NLS_CALENDAR = ''' || nvl(FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', G_user_id),'GREGORIAN') || '''') /* Modified for Bug 8974192 */
261 || '</BDO>';
262 else
263 l_action_date := to_char(rec.action_date ,
264 FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', G_user_id),
265 'NLS_CALENDAR = ''' || nvl(FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', G_user_id),'GREGORIAN') || ''''); /* Modified for Bug 8974192 */
266 end if;
267 else
268 l_action_date := to_char(rec.action_date);
269 end if;
270 /* Added for Bug 7538477 End */
271
272 --debug_msg_s1('Project Id 3' || document_id);
273 document := document ||
274 '<TR BGCOLOR="#ffffff" ><TD class=approvalhistdata VALIGN=CENTER ALIGN=LEFT bgcolor=#f2f2f5> <font size="2" color=#3c3c3c face="Tahoma, Arial, Helvetica, Geneva">' || rec.sequence_number || '</font></TD>';
275
276 document := document || '<TD class=approvalhistdata VALIGN=CENTER ALIGN=LEFT bgcolor=#f2f2f5> <font size="2" color=#3c3c3c face="Tahoma, Arial, Helvetica, Geneva">' || rec.user_full_name || '</font></TD>';
277
278 document := document || '<TD class=approvalhistdata VALIGN=CENTER ALIGN=LEFT bgcolor=#f2f2f5> <font size="2" color=#3c3c3c face="Tahoma, Arial, Helvetica, Geneva">' || rec.action_code || '</font></TD>';
279
280 document := document || '<TD class=approvalhistdata VALIGN=CENTER ALIGN=LEFT bgcolor=#f2f2f5> <font size="2" color=#3c3c3c face="Tahoma, Arial, Helvetica, Geneva">' || l_action_date || '</font></TD>'; /* Modified for Bug 7538477 */
281
282 document := document || '<TD class=approvalhistdata VALIGN=CENTER ALIGN=LEFT bgcolor=#f2f2f5> <font size="2" color=#3c3c3c face="Tahoma, Arial, Helvetica, Geneva">' || rec.approver_comments || '</font></TD></tr>';
283
284 l_action_date := NULL; /* Added for Bug 7538477 */
285
286
287 END LOOP;
288
289
290 document := document ||'</table><br><br>';
291
292 --debug_msg_s1('Docu = ' || document);
293
294 document_type := 'text/html';
295
296 -- 4537865
297 EXCEPTION
298 WHEN OTHERS THEN
299 document := 'An Unexpected Error has occured' ;
300 document_type := 'text/html';
301 -- RAISE not needed here.
302 END show_history;
303
304 --Bug 14308345 start.
305 PROCEDURE show_ci_history
306 (document_id IN VARCHAR2,
307 display_type IN VARCHAR2,
308 document IN OUT NOCOPY VARCHAR2, -- 4537865
309 document_type IN OUT NOCOPY VARCHAR2) -- 4537865
310
311 IS
312
313 l_item_type VARCHAR2(30);
314 l_item_key number;
315 l_object_id NUMBER;
316 l_dummy VARCHAR2(400);
317 l_action_date VARCHAR2(60) := NULL;
318 seq number := 1;
319
320 CURSOR get_history_info IS
321 SELECT DISTINCT
322 wu.display_name user_full_name,
323 pl.meaning action_code,
324 pw.action_date,
325 pw.approver_comments
326 FROM pa_wf_ntf_performers pw, wf_users wu, pa_lookups pl
327 WHERE --pw.wf_type_code = 'APPROVAL_FYI'
328 pw.item_type = l_item_type -- 'PAWFPPRA'
329 AND pw.item_key = l_item_key --32715
330 AND pw.object_id2 = l_object_id --10020
331 and pw.user_name = wu.name
332 and pl.lookup_type = 'PA_WF_APPROVAL_ACTION'
333 and pl.lookup_code = pw.action_code
334 and pl.lookup_code = 'SUBMIT' ;
335 l_index1 NUMBER;
336
337 CURSOR get_approver_info IS
338 select distinct
339 pl.meaning action_code,
340 ias.end_date action_date,
341 wu.display_name user_full_name
342 from wf_item_activity_statuses ias,
343 wf_process_activities pa,
344 wf_users wu,pa_lookups pl
345 where ias.item_type = l_item_type
346 and ias.item_key = l_item_key
347 and pa.instance_label = 'CI_APPROVAL_REQUEST'
348 and ias.process_activity = pa.instance_id
349 and wu.name = ias.assigned_user
350 and pl.lookup_type = 'PA_WF_APPROVAL_ACTION'
351 and pl.lookup_code = ias.activity_result_code;
352
353
354 BEGIN
355
356 --debug_msg_s1('AAAAA Project Id ' || document_id);
357 l_index1 := instr(document_id, ':');
358
359 l_item_type := substrb(document_id, 1, l_index1 - 1); -- 4537865 Changed substr to substrb
360 l_item_key := To_number(substrb(document_id, l_index1 +1,
361 Length(document_id)- l_index1)); -- 4537865 Changed substr to substrb
362
363 --debug_msg_s1 ('XXXXXXXXXXXXXX c' || To_char(l_item_key));
364
365 l_object_id := wf_engine.GetItemAttrNumber
366 ( itemtype => l_item_type,
367 itemkey => l_item_key,
368 aname => 'WF_OBJECT_ID');
369 document := '<table cellpadding="0" cellspacing="0" border="0" width="100%" summary="">'
370 || '<tr><td width="100%" > <font face="Tahoma" color=#3c3c3c class="OraHeaderSub"> '
371 || '<B>Approval History</td></tr></table>' ;
372
373
374
375 document := document ||
376 '<table cellSpacing=1 cellPadding=3 width="90%" border=0 bgColor=white summary=""><tr>
377 <TH class=tableheader width=5% ALIGN=left bgcolor=#cfe0f1><font size="2" color=#3c3c3c face="Tahoma, Arial, Helvetica, Geneva">Sequence</font>
378 </TH> <TH class=tableheader width=35% ALIGN=left bgcolor=#cfe0f1><font size="2" color=#3c3c3c face="Tahoma, Arial, Helvetica, Geneva">Who</font>
379 </TH> <TH class=tableheader width=15% ALIGN=left bgcolor=#cfe0f1><font size="2" color=#3c3c3c face="Tahoma, Arial, Helvetica, Geneva">Action</font>
380 </TH> <TH class=tableheader width = 15% ALIGN=left bgcolor=#cfe0f1><font size="2" color=#3c3c3c face="Tahoma, Arial, Helvetica, Geneva">Date</font>
381 </TH> <TH class=tableheader width=55% ALIGN=left bgcolor=#cfe0f1><font size="2" color=#3c3c3c face="Tahoma, Arial, Helvetica, Geneva">Note</font>
382 </TH></TR> ';
383
384
385 FOR rec IN get_history_info LOOP
386 /* Added for Bug 7538477 Start */
387 if (FND_RELEASE.MAJOR_VERSION = 12 and FND_RELEASE.minor_version >= 1 and FND_RELEASE.POINT_VERSION >= 1 )
388 or (FND_RELEASE.MAJOR_VERSION > 12) then
389 if (display_type = wf_notification.doc_html) then
390 l_action_date := '<BDO DIR="LTR">' ||
391 to_char(rec.action_date ,
392 FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', G_user_id),
393 'NLS_CALENDAR = ''' || nvl(FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', G_user_id),'GREGORIAN') || '''') /* Modified for Bug 8974192 */
394 || '</BDO>';
395 else
396 l_action_date := to_char(rec.action_date ,
397 FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', G_user_id),
398 'NLS_CALENDAR = ''' || nvl(FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', G_user_id),'GREGORIAN') || ''''); /* Modified for Bug 8974192 */
399 end if;
400 else
401 l_action_date := to_char(rec.action_date);
402 end if;
403 /* Added for Bug 7538477 End */
404
405 --debug_msg_s1('Project Id 3' || document_id);
406 document := document ||
407 '<TR BGCOLOR="#ffffff" ><TD class=approvalhistdata VALIGN=CENTER ALIGN=LEFT bgcolor=#f2f2f5> <font size="2" color=#3c3c3c face="Tahoma, Arial, Helvetica, Geneva">' || seq || '</font></TD>';
408
409 document := document || '<TD class=approvalhistdata VALIGN=CENTER ALIGN=LEFT bgcolor=#f2f2f5> <font size="2" color=#3c3c3c face="Tahoma, Arial, Helvetica, Geneva">' || rec.user_full_name || '</font></TD>';
410
411 document := document || '<TD class=approvalhistdata VALIGN=CENTER ALIGN=LEFT bgcolor=#f2f2f5> <font size="2" color=#3c3c3c face="Tahoma, Arial, Helvetica, Geneva">' || rec.action_code || '</font></TD>';
412
413 document := document || '<TD class=approvalhistdata VALIGN=CENTER ALIGN=LEFT bgcolor=#f2f2f5> <font size="2" color=#3c3c3c face="Tahoma, Arial, Helvetica, Geneva">' || l_action_date || '</font></TD>'; /* Modified for Bug 7538477 */
414
415 document := document || '<TD class=approvalhistdata VALIGN=CENTER ALIGN=LEFT bgcolor=#f2f2f5> <font size="2" color=#3c3c3c face="Tahoma, Arial, Helvetica, Geneva">' || rec.approver_comments || '</font></TD></tr>';
416 l_action_date := to_char(rec.action_date);
417 l_action_date := NULL; /* Added for Bug 7538477 */
418 seq := seq+1;
419
420 END LOOP;
421 -- Added for bug#14308345
422
423 FOR rec IN get_approver_info LOOP
424 /* Added for Bug 7538477 Start */
425 if (FND_RELEASE.MAJOR_VERSION = 12 and FND_RELEASE.minor_version >= 1 and FND_RELEASE.POINT_VERSION >= 1 )
426 or (FND_RELEASE.MAJOR_VERSION > 12) then
427 if (display_type = wf_notification.doc_html) then
428 l_action_date := '<BDO DIR="LTR">' ||
429 to_char(rec.action_date ,
430 FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', G_user_id),
431 'NLS_CALENDAR = ''' || nvl(FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', G_user_id),'GREGORIAN') || '''') /* Modified for Bug 8974192 */
432 || '</BDO>';
433 else
434 l_action_date := to_char(rec.action_date ,
435 FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', G_user_id),
436 'NLS_CALENDAR = ''' || nvl(FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', G_user_id),'GREGORIAN') || ''''); /* Modified for Bug 8974192 */
437 end if;
438 else
439 l_action_date := to_char(rec.action_date);
440 end if;
441 /* Added for Bug 7538477 End */
442
443 --debug_msg_s1('Project Id 3' || document_id);
444 document := document ||
445 '<TR BGCOLOR="#ffffff" ><TD class=approvalhistdata VALIGN=CENTER ALIGN=LEFT bgcolor=#f2f2f5> <font size="2" color=#3c3c3c face="Tahoma, Arial, Helvetica, Geneva">' || seq || '</font></TD>';
446
447 document := document || '<TD class=approvalhistdata VALIGN=CENTER ALIGN=LEFT bgcolor=#f2f2f5> <font size="2" color=#3c3c3c face="Tahoma, Arial, Helvetica, Geneva">' || rec.user_full_name || '</font></TD>';
448
449 document := document || '<TD class=approvalhistdata VALIGN=CENTER ALIGN=LEFT bgcolor=#f2f2f5> <font size="2" color=#3c3c3c face="Tahoma, Arial, Helvetica, Geneva">' || rec.action_code || '</font></TD>';
450
451 document := document || '<TD class=approvalhistdata VALIGN=CENTER ALIGN=LEFT bgcolor=#f2f2f5> <font size="2" color=#3c3c3c face="Tahoma, Arial, Helvetica, Geneva">' || l_action_date || '</font></TD>'; /* Modified for Bug 7538477 */
452
453 document := document || '<TD class=approvalhistdata VALIGN=CENTER ALIGN=LEFT bgcolor=#f2f2f5> <font size="2" color=#3c3c3c face="Tahoma, Arial, Helvetica, Geneva"></font></TD></tr>'; -- approver comments will always null for CI
454 l_action_date := to_char(rec.action_date);
455 l_action_date := NULL; /* Added for Bug 7538477 */
456 seq := seq+1;
457 END LOOP;
458 -- bug#14308345 end.
459
460 document := document ||'</table><br><br>';
461
462 --debug_msg_s1('Docu = ' || document);
463
464 document_type := 'text/html';
465
466 -- 4537865
467 EXCEPTION
468 WHEN OTHERS THEN
469 document := 'An Unexpected Error has occured' ;
470 document_type := 'text/html';
471 -- RAISE not needed here.
472 END show_ci_history;
473 --Bug 14308345 end.
474 END pa_workflow_history;
475