[Home] [Help]
PACKAGE BODY: APPS.OKE_K_APPROVAL_WF2
Source
1 PACKAGE BODY OKE_K_APPROVAL_WF2 AS
2 /* $Header: OKEWKA2B.pls 120.5 2011/03/02 09:18:06 skuchima ship $ */
3
4 --
5 -- Global Variables
6 --
7 CR VARCHAR2(10) := FND_GLOBAL.newline;
8 BS VARCHAR2(10) := ' ';
9
10 --
11 -- Private Functions and Procedures
12 --
13
14 --
15 -- Public Functions and Procedures
16 --
17
18 --
19 -- Name : Contract_Number_Link
20 -- Pre-reqs : Must be called from WF activity
21 -- Function : This PL/SQL document procedure returns the contract
22 -- number with a link to contract flowdown viewer
23 --
24 -- Parameters :
25 -- IN : ItemType
26 -- ItemKey
27 -- ActID
28 -- FuncMode
29 -- OUT NOCOPY /* file.sql.39 change */ : ResultOut ( None )
30 --
31 -- Returns : None
32 --
33 PROCEDURE Contract_Number_Link
34 ( Document_ID IN VARCHAR2
35 , Display_Type IN VARCHAR2
36 , Document OUT NOCOPY /* file.sql.39 change */ VARCHAR2
37 , Document_Type IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
38 ) IS
39
40 ItemType WF_ITEMS.item_type%TYPE;
41 ItemKey WF_ITEMS.item_key%TYPE;
42
43 L_K_Header_ID NUMBER;
44 L_K_Number VARCHAR2(240);
45
46 FlowdownURL VARCHAR2(2000);
47
48 BEGIN
49
50 ItemType := substr( Document_ID , 1 , instr(Document_ID , ':') - 1 );
51 ItemKey := substr( Document_ID , instr(Document_ID , ':') + 1
52 , length(Document_ID) - 2);
53
54 L_K_Header_ID := WF_ENGINE.GetItemAttrNumber(ItemType , ItemKey , 'CONTRACT_ID');
55 L_K_Number := WF_ENGINE.GetItemAttrText(ItemType , ItemKey , 'K_NUMBER');
56
57 IF ( Display_Type = 'text/plain' ) THEN
58
59 Document := 'L_K_Number';
60
61 ELSE
62
63 FlowdownURL := OKE_FLOWDOWN_UTILS.Flowdown_URL
64 ( X_Business_Area => 'APPROVAL'
65 , X_Object_Name => 'OKE_K_HEADERS'
66 , X_PK1 => L_K_Header_ID
67 , X_PK2 => NULL );
68
69 Document := '<a href="' || FlowdownURL || '">' || L_K_Number || '</a>';
70 Document_Type := 'text/html';
71
72 END IF;
73
74 END Contract_Number_Link;
75
76
77 --
78 -- Name : Show_Approval_History
79 -- Pre-reqs : Must be called from WF activity
80 -- Function : This PL/SQL document procedure returns the approval
81 -- history as maintained in SET_APPROVAL_HISTORY() for
82 -- use in various notifications
83 --
84 -- Parameters :
85 -- IN : Document_ID ( ItemType:ItemKey )
86 -- Display_Type
87 -- Document_Type
88 -- OUT NOCOPY /* file.sql.39 change */ : Document
89 -- Document_Type
90 --
91 -- Returns : None
92 --
93 PROCEDURE Show_Approval_History
94 ( Document_ID IN VARCHAR2
95 , Display_Type IN VARCHAR2
96 , Document OUT NOCOPY /* file.sql.39 change */ VARCHAR2
97 , Document_Type IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
98 ) IS
99
100 ItemType WF_ITEMS.item_type%TYPE;
101 ItemKey WF_ITEMS.item_key%TYPE;
102 DocOut VARCHAR2(32767);
103
104 L_K_Header_ID NUMBER;
105
106 CURSOR h ( C_Header_ID NUMBER ) IS
107 SELECT ah.action_code
108 , ac.action_name
109 , ah.action_date
110 , ah.approver_role_id
111 , pr.meaning approver_role
112 , ah.performer
113 , ah.note
114 FROM oke_approval_history ah
115 , ( select lookup_code action_code
116 , meaning action_name
117 from fnd_lookup_values
118 where view_application_id = 777
119 and lookup_type = 'APPROVAL_ACTION'
120 and language = userenv('LANG') ) ac
121 , pa_project_role_types pr
122 WHERE ah.k_header_id = C_Header_ID
123 AND ah.chg_request_id IS NULL
124 AND ac.action_code = ah.action_code
125 AND pr.project_role_id (+) = ah.approver_role_id
126 ORDER BY action_sequence DESC;
127 hrec h%rowtype;
128
129 CURSOR r ( C_role_name VARCHAR2 ) IS
130 SELECT nvl(p.full_name , u.user_name)
131 , u.email_address
132 FROM fnd_user u
133 , per_all_people_f p
134 WHERE u.user_name = C_role_name
135 AND p.person_id = u.employee_id
136 AND trunc(sysdate) BETWEEN p.effective_start_date AND p.effective_end_date;
137
138 Performer_Display_Name VARCHAR2(240);
139 Performer_Email_Address VARCHAR2(240);
140
141 l_action_date_text varchar2(100);
142 l_user_id number;
143
144 BEGIN
145
146 ItemType := substr( Document_ID , 1 , instr(Document_ID , ':') - 1 );
147 ItemKey := substr( Document_ID , instr(Document_ID , ':') + 1
148 , length(Document_ID) - 2);
149
150 L_K_Header_ID := WF_ENGINE.GetItemAttrNumber(ItemType , ItemKey , 'CONTRACT_ID');
151
152 IF ( Display_Type = 'text/plain' ) THEN
153
154 Document := '';
155
156 ELSE
157 DocOut := CR || CR || '<!-- SHOW_APPROVAL_HISTORY -->' || CR || CR;
158 --
159 -- Section Header
160 --
161 DocOut := DocOut
162 || '<table border=0 cellspacing=2 cellpadding=2 width=100%>'
163 || '<tr><td class=OraHeader>' || fnd_message.get_string('OKE' , 'OKE_WFNTF_APPROVAL_HISTORY')
164 || '</td></tr>' || CR
165 || '<tr><td class=OraBGAccentDark></td></tr>' || CR;
166
167 --
168 -- Table Header
169 --
170 DocOut := DocOut || '<tr><td>' || CR;
171 DocOut := DocOut
172 || '<table class=OraTable border=0 cellspacing=2 cellpadding=2 width=100%>' || CR || '<tr>' || CR;
173 DocOut := DocOut
174 || '<th class=OraTableColumnHeader width=15%>'
175 || fnd_message.get_string('OKE' , 'OKE_WFNTF_ACTION')
176 || '</th>' || CR;
177 DocOut := DocOut
178 || '<th class=OraTableColumnHeader width=20%>'
179 || fnd_message.get_string('OKE' , 'OKE_WFNTF_PERFORMER')
180 || '</th>' || CR;
181 DocOut := DocOut
182 || '<th class=OraTableColumnHeader width=20%>'
183 || fnd_message.get_string('OKE' , 'OKE_WFNTF_ROLE')
184 || '</th>' || CR;
185 DocOut := DocOut
186 || '<th class=OraTableColumnHeader width=15%>'
187 || fnd_message.get_string('OKE' , 'OKE_WFNTF_DATE')
188 || '</th>' || CR;
189 DocOut := DocOut
190 || '<th class=OraTableColumnHeader width=30%>'
191 || fnd_message.get_string('OKE' , 'OKE_WFNTF_NOTE')
192 || '</th>' || CR || '</tr>' || CR;
193
194 select fnd_global.user_id into l_user_id from dual;
195
196 FOR hrec IN h ( L_K_Header_ID ) LOOP
197
198 OPEN r ( hrec.Performer );
199 FETCH r INTO Performer_Display_Name , Performer_Email_Address;
200 CLOSE r;
201
202 IF ( Performer_Display_Name IS NULL ) THEN
203 Performer_Display_Name := hrec.Performer;
204 END IF;
205
206 DocOut := DocOut || '<tr>' || CR;
207 DocOut := DocOut
208 || '<td class=OraTableCellText>'
209 || hrec.Action_Name
210 || '</td>' || CR;
211 IF ( Performer_Email_Address IS NOT NULL ) THEN
212 DocOut := DocOut
213 || '<td class=OraTableCellText>'
214 || '<a href="mailto:' || Performer_Email_Address || '">'
215 || Performer_Display_Name || '</a>'
216 || '</td>' || CR;
217 ELSE
218 DocOut := DocOut
219 || '<td class=OraTableCellText>'
220 || Performer_Display_Name
221 || '</td>' || CR;
222 END IF;
223 DocOut := DocOut
224 || '<td class=OraTableCellText>'
225 || nvl(hrec.Approver_Role , BS)
226 || '</td>' || CR;
227
228 /* Modfied if for bug 11824547 */
229 IF (
230 ( FND_RELEASE.MAJOR_VERSION = 12 AND FND_RELEASE.minor_version = 1 AND FND_RELEASE.POINT_VERSION >= 1 )
231 OR (fnd_release.major_version = 12 and fnd_release.minor_version >= 2)
232 OR (FND_RELEASE.MAJOR_VERSION > 12)
233 )
234 THEN
235 l_action_date_text := '<BDO DIR="LTR">'
236 || to_char(hrec.Action_Date,FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', l_user_id),'NLS_CALENDAR = '''
237 || nvl ( FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', l_user_id) , 'GREGORIAN' ) || '''')
238 || '</BDO>';
239
240 ELSE
241 l_action_date_text := fnd_date.date_to_displaydate(hrec.Action_Date,2);
242 END IF;
243
244
245 DocOut := DocOut
246 || '<td class=OraTableCellText>'
247 || l_action_date_text
248 || '</td>' || CR;
249
250 DocOut := DocOut
251 || '<td class=OraTableCellText>'
252 || nvl(hrec.Note , BS)
253 || '</td>' || CR || '</tr>' || CR;
254
255 END LOOP;
256
257 DocOut := DocOut
258 || '</td></tr></table>' || CR
259 || '</table>' || CR || '<p>' || CR;
260
261 Document := DocOut;
262 Document_Type := 'text/html';
263
264 END IF;
265
266 END Show_Approval_History;
267
268
269 END OKE_K_APPROVAL_WF2;