[Home] [Help]
PACKAGE BODY: APPS.PA_WORKFLOW_HISTORY
Source
1 package body pa_workflow_history as
2 /* $Header: PAWFHSUB.pls 120.3 2006/04/21 07:28:34 avaithia noship $ */
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 =============================================================================*/
25
26 G_USER_ID CONSTANT NUMBER := FND_GLOBAL.user_id;
27
28
29
30 PROCEDURE save_comment_history(
31 itemtype IN VARCHAR2
32 ,itemkey IN VARCHAR2
33 ,funcmode IN VARCHAR2
34 ,user_name IN VARCHAR2
35 ,comment IN varchar2)
36
37 IS
38
39 l_comment VARCHAR2(2000);
40 l_object_id NUMBER;
41 l_project_id NUMBER;
42 l_seq NUMBER;
43 l_full_name VARCHAR2(2000);
44 l_user_name VARCHAR2(100);
45
46 CURSOR get_old_seq
47 IS
48 SELECT MAX(sequence_number)
49 FROM pa_wf_ntf_performers
50 WHERE
51 wf_type_code = 'APPROVAL_FYI'
52 AND item_type = itemtype
53 AND item_key = itemkey
54 AND object_id2 = l_object_id;
55
56 CURSOR get_full_name
57 IS
58 select party_name
59 from (
60 select fu.user_name, hp.party_name, hp.email_address
61 from fnd_user fu,
62 hz_parties hp
63 where fu.person_party_id = hp.party_id -- Bug 4527617. Replaced customer_id with person_party_id.
64 and fu.user_name = l_user_name
65 union all
66 select fu.user_name, papf.full_name, papf.email_address
67 from fnd_user fu,
68 hz_parties hp,
69 per_all_people_f papf
70 where 'PER:' || fu.employee_id = hp.orig_system_reference
71 and fu.user_name = l_user_name
72 and trunc(sysdate)
73 between papf.EFFECTIVE_START_DATE
74 and Nvl(papf.effective_end_date, Sysdate + 1)
75 and papf.person_id = fu.employee_id);
76
77 BEGIN
78
79
80 l_object_id := wf_engine.GetItemAttrNumber
81 ( itemtype => itemtype,
82 itemkey => itemkey,
83 aname => 'WF_OBJECT_ID');
84
85 --debug_msg_s1 ('XXXXXXXXXXXXXX d' || To_char(l_object_id));
86
87 l_project_id := wf_engine.GetItemAttrNumber
88 ( itemtype => itemtype,
89 itemkey => itemkey,
90 aname => 'PROJECT_ID');
91 /*
92 OPEN get_project_id;
93 FETCH get_project_id INTO l_project_id;
94 CLOSE get_project_id;
95 */
96
97
98 OPEN get_old_seq;
99 FETCH get_old_seq INTO l_seq;
100 IF get_old_seq%notfound THEN
101 l_seq := 0;
102 END IF;
103 IF l_seq IS NULL THEN
104 l_seq := 0;
105 END IF;
106
107 CLOSE get_old_seq;
108
109 l_user_name := user_name;
110 OPEN get_full_name ;
111 FETCH get_full_name INTO l_full_name;
112 CLOSE get_full_name;
113
114 --debug_msg_s1 ('Main: Save comment history: ' ||itemtype || ':' || itemkey || ':' || l_object_id || ':' || user_name);
115
116 --debug_msg_s1 ('Main: Save comment history: ' ||itemtype || ':' || itemkey || ':' || l_object_id || ':' || l_full_name);
117
118
119 --debug_msg_s1 ('Main: Save comment history: seq ' || To_char(l_seq +1));
120 --debug_msg_s1 ('Main: Save comment history: func ' || funcmode);
121
122
123
124 INSERT INTO pa_wf_ntf_performers
125 (
126 wf_type_code,
127 item_type,
128 item_key,
129 object_id1,
130 object_id2,
131 user_name,
132 user_type,
133 action_code,
134 action_date,
135 sequence_number,
136 approver_comments
137 )
138 VALUES
139 (
140 'APPROVAL_FYI',
141 itemtype,
142 itemkey,
143 l_project_id,
144 l_object_id,
145 user_name,
146 'RESOURCE',
147 funcmode,
148 Sysdate,
149 l_seq +1 ,
150 comment
151 );
152
153
154 END;
155
156
157 PROCEDURE show_history
158 (document_id IN VARCHAR2,
159 display_type IN VARCHAR2,
160 document IN OUT NOCOPY VARCHAR2, -- 4537865
161 document_type IN OUT NOCOPY VARCHAR2) -- 4537865
162
163 IS
164
165 l_item_type VARCHAR2(30);
166 l_item_key number;
167 l_object_id NUMBER;
168 l_dummy VARCHAR2(400);
169
170
171 CURSOR get_history_info IS
172 SELECT DISTINCT
173 pw.sequence_number,
174 wu.display_name user_full_name,
175 pl.meaning action_code,
176 pw.action_date,
177 pw.approver_comments
178 FROM pa_wf_ntf_performers pw, wf_users wu, pa_lookups pl
179 WHERE --pw.wf_type_code = 'APPROVAL_FYI'
180 pw.item_type = l_item_type -- 'PAWFPPRA'
181 AND pw.item_key = l_item_key --32715
182 AND pw.object_id2 = l_object_id --10020
183 and pw.user_name = wu.name
184 and pl.lookup_type = 'PA_WF_APPROVAL_ACTION'
185 and pl.lookup_code = pw.action_code
186 ORDER BY pw.sequence_number ;
187 /*
188 SELECT
189 pw.sequence_number,
190 wu.display_name user_full_name,
191 pw.action_code,
192 pw.action_date,
193 pw.approver_comments
194 FROM pa_wf_ntf_performers pw, wf_users wu
195 WHERE --pw.wf_type_code = 'APPROVAL_FYI'
196 pw.item_type = l_item_type
197 AND pw.item_key = l_item_key
198 AND pw.object_id2 = l_object_id
199 and pw.user_name = wu.name
200 ORDER BY pw.sequence_number ;
201 */
202
203
204 l_index1 NUMBER;
205
206
207 BEGIN
208
209 --debug_msg_s1('AAAAA Project Id ' || document_id);
210 l_index1 := instr(document_id, ':');
211
212 l_item_type := substrb(document_id, 1, l_index1 - 1); -- 4537865 Changed substr to substrb
213
214
215 --debug_msg_s1 ('XXXXXXXXXXXXXX a' || To_char(l_index1));
216
217 --debug_msg_s1 ('XXXXXXXXXXXXXX b' || l_item_type);
218
219 l_item_key := To_number(substrb(document_id, l_index1 +1,
220 Length(document_id)- l_index1)); -- 4537865 Changed substr to substrb
221
222 --debug_msg_s1 ('XXXXXXXXXXXXXX c' || To_char(l_item_key));
223
224
225 l_object_id := wf_engine.GetItemAttrNumber
226 ( itemtype => l_item_type,
227 itemkey => l_item_key,
228 aname => 'WF_OBJECT_ID');
229
230 document := '<table cellpadding="0" cellspacing="0" border="0" width="100%" summary="">'
231 || '<tr><td width="100%" > <font face="Tahoma" color=#3c3c3c class="OraHeaderSub"> '
232 || '<B>Approval History</td></tr></table>' ;
233
234
235
236 document := document ||
237 '<table cellSpacing=1 cellPadding=3 width="90%" border=0 bgColor=white summary=""><tr>
238 <TH class=tableheader width=5% ALIGN=left bgcolor=#cfe0f1><font size="2" color=#3c3c3c face="Tahoma, Arial, Helvetica, Geneva">Sequence</font>
239 </TH> <TH class=tableheader width=35% ALIGN=left bgcolor=#cfe0f1><font size="2" color=#3c3c3c face="Tahoma, Arial, Helvetica, Geneva">Who</font>
240 </TH> <TH class=tableheader width=15% ALIGN=left bgcolor=#cfe0f1><font size="2" color=#3c3c3c face="Tahoma, Arial, Helvetica, Geneva">Action</font>
241 </TH> <TH class=tableheader width = 15% ALIGN=left bgcolor=#cfe0f1><font size="2" color=#3c3c3c face="Tahoma, Arial, Helvetica, Geneva">Date</font>
242 </TH> <TH class=tableheader width=55% ALIGN=left bgcolor=#cfe0f1><font size="2" color=#3c3c3c face="Tahoma, Arial, Helvetica, Geneva">Note</font>
243 </TH></TR> ';
244
245
246 FOR rec IN get_history_info LOOP
247
248
249 --debug_msg_s1('Project Id 3' || document_id);
250 document := document ||
251 '<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>';
252
253 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>';
254
255 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>';
256
257 document := document || '<TD class=approvalhistdata VALIGN=CENTER ALIGN=LEFT bgcolor=#f2f2f5> <font size="2" color=#3c3c3c face="Tahoma, Arial, Helvetica, Geneva">' || rec.action_date || '</font></TD>';
258
259 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>';
260
261
262 END LOOP;
263
264
265 document := document ||'</table><br><br>';
266
267 --debug_msg_s1('Docu = ' || document);
268
269 document_type := 'text/html';
270
271 -- 4537865
272 EXCEPTION
273 WHEN OTHERS THEN
274 document := 'An Unexpected Error has occured' ;
275 document_type := 'text/html';
276 -- RAISE not needed here.
277 END show_history;
278
279
280
281 END pa_workflow_history;
282