[Home] [Help]
PACKAGE BODY: APPS.IGP_WZ_PUBLISH_TEMPLATE_PKG
Source
1 PACKAGE BODY igp_wz_publish_template_pkg AS
2 /* $Header: IGSPWZAB.pls 120.1 2005/12/23 02:47:06 jnalam noship $ */
3
4 /******************************************************************
5 Created By : Prabhat Patel
6 Date Created By : 20-Feb-2004
7 Purpose : Procedures for workflow template approval to publish
8 remarks :
9 Change History
10 Who When What
11 ******************************************************************/
12
13 PROCEDURE submit_approval( p_template_id igp_wz_templates.template_id%TYPE,
14 p_template_name igp_wz_templates.template_name%TYPE,
15 p_user_id fnd_user.user_id%TYPE) AS
16 /******************************************************************
17 Created By : Prabhat Patel
18 Date Created By : 20-Feb-2004
19 Purpose : Procedure for raising the business event for template approval
20 remarks :
21 Change History
22 Who When What
23 ******************************************************************/
24 CURSOR c_seq_num IS
25 SELECT igp_wz_temp_approve_s.NEXTVAL
26 FROM dual;
27 ln_seq_val NUMBER;
28 l_event_t wf_event_t;
29 l_parameter_list_t wf_parameter_list_t;
30
31 BEGIN
32
33 -- initialize the parameter list.
34 wf_event_t.Initialize(l_event_t);
35
36 -- set the parameters.
37 wf_event.AddParameterToList ( p_name => 'P_TEMPLATE_ID', p_value => p_template_id, p_parameterlist => l_parameter_list_t);
38 wf_event.AddParameterToList ( p_name => 'P_USER_ID' , p_value => p_user_id, p_parameterlist => l_parameter_list_t);
39 wf_event.AddParameterToList ( p_name => 'P_TEMPLATE_NAME' , p_value => p_template_name, p_parameterlist => l_parameter_list_t);
40
41 -- get the sequence value to be added to EVENT KEY to make it unique.
42 OPEN c_seq_num;
43 FETCH c_seq_num INTO ln_seq_val ;
44 CLOSE c_seq_num ;
45
46 -- raise event
47 WF_EVENT.RAISE (p_event_name => 'oracle.apps.igs.igp.wz.publish_template',
48 p_event_key => 'IGPWZ001'|| p_template_name ||ln_seq_val,
49 p_parameters => l_parameter_list_t
50 );
51
52 END submit_approval;
53
54 PROCEDURE update_template (
55 p_template_id igp_wz_templates.template_id%TYPE,
56 p_status VARCHAR2) IS
57 /******************************************************************
58 Created By : Prabhat Patel
59 Date Created By : 25-Feb-2004
60 Purpose : Procedure for Updating status of the Template
61 remarks :
62 Change History
63 Who When What
64 ******************************************************************/
65 l_var NUMBER;
66
67 CURSOR lock_temp_cur(cp_template_id igp_wz_templates.template_id%TYPE) IS
68 SELECT 1
69 FROM igp_wz_templates
70 WHERE template_id = cp_template_id;
71 BEGIN
72
73 OPEN lock_temp_cur(p_template_id);
74 FETCH lock_temp_cur INTO l_var;
75 CLOSE lock_temp_cur;
76
77 UPDATE igp_wz_templates
78 SET template_status_code = p_status
79 WHERE template_id = p_template_id;
80
81 END update_template;
82
83 PROCEDURE pending_status (
84 p_template_id igp_wz_templates.template_id%TYPE) IS
85 /******************************************************************
86 Created By : Prabhat Patel
87 Date Created By : 25-Feb-2004
88 Purpose : Procedure for updating to Pending status.
89 remarks :
90 Change History
91 Who When What
92 ******************************************************************/
93 PRAGMA AUTONOMOUS_TRANSACTION;
94 BEGIN
95 update_template(p_template_id, 'PENDING');
96 COMMIT;
97 END pending_status;
98
99 PROCEDURE draft_status (itemtype IN VARCHAR2,
100 itemkey IN VARCHAR2,
101 actid IN NUMBER,
102 funcmode IN VARCHAR2,
103 resultout OUT NOCOPY VARCHAR2) AS
104 /******************************************************************
105 Created By : Prabhat Patel
106 Date Created By : 25-Feb-2004
107 Purpose : Procedure for updating to Draft status.
108 remarks :
109 Change History
110 Who When What
111 ******************************************************************/
112 l_template_id igp_wz_templates.template_id%TYPE;
113 l_error_message VARCHAR2(500);
114 BEGIN
115 l_template_id := wf_engine.GetItemAttrText( itemtype => itemtype,
116 itemkey => itemkey,
117 aname => 'P_TEMPLATE_ID');
118 update_template(l_template_id, 'DRAFT');
119
120 EXCEPTION
121 WHEN OTHERS THEN
122 l_error_message := SQLERRM;
123 wf_core.context('igp_wz_publish_template_pkg','draft_status',itemtype,itemkey ,l_error_message);
124 RAISE;
125 END draft_status;
126
127 PROCEDURE publish_status (itemtype IN VARCHAR2,
128 itemkey IN VARCHAR2,
129 actid IN NUMBER,
130 funcmode IN VARCHAR2,
131 resultout OUT NOCOPY VARCHAR2) AS
132 /******************************************************************
133 Created By : Prabhat Patel
134 Date Created By : 25-Feb-2004
135 Purpose : Procedure for updating to Publish status.
136 remarks :
137 Change History
138 Who When What
139 ******************************************************************/
140 l_template_id igp_wz_templates.template_id%TYPE;
141 l_error_message VARCHAR2(500);
142 BEGIN
143 l_template_id := wf_engine.GetItemAttrText( itemtype => itemtype,
144 itemkey => itemkey,
145 aname => 'P_TEMPLATE_ID');
146 update_template(l_template_id, 'PUBLISH');
147
148 EXCEPTION
149 WHEN OTHERS THEN
150 l_error_message := SQLERRM;
151 wf_core.context('igp_wz_publish_template_pkg','publish_status',itemtype,itemkey ,l_error_message);
152 RAISE;
153 END publish_status;
154
155 PROCEDURE create_tempdtl_message(
156 document_id IN VARCHAR2,
157 display_type IN VARCHAR2,
158 document IN OUT NOCOPY CLOB,
159 document_type IN OUT NOCOPY VARCHAR2
160 ) AS
161 /******************************************************************
162 Created By : Prabhat Patel
163 Date Created By : 20-Feb-2004
164 Purpose : Procedure for creating the CLOB message body
165 remarks :
166 Change History
167 Who When What
168 ******************************************************************/
169
170 BEGIN
171 null;
172 END create_tempdtl_message;
173
174 PROCEDURE template_preprocess (itemtype IN VARCHAR2,
175 itemkey IN VARCHAR2,
176 actid IN NUMBER,
177 funcmode IN VARCHAR2,
178 resultout OUT NOCOPY VARCHAR2) AS
179 /******************************************************************
180 Created By : Prabhat Patel
181 Date Created By : 20-Feb-2004
182 Purpose : Procedure for setting all the item attributes and validating all the error conditions
183 remarks :
184 Change History
185 Who When What
186 ******************************************************************/
187 l_approver fnd_user.user_name%TYPE;
188 l_approver_name hz_parties.party_name%TYPE;
189 l_login_user_id fnd_user.user_id%TYPE;
190 l_login_user_name fnd_user.user_name%TYPE;
191 l_item_key wf_items.item_key%TYPE;
192 l_item_exists VARCHAR2(1);
193 l_template_id igp_wz_templates.template_id%TYPE;
194 l_template_name igp_wz_templates.template_name%TYPE;
195 l_error_message VARCHAR2(500);
196
197 nbsp VARCHAR2(10);
198 l_preview_link VARCHAR2(500);
199 l_protocol_port VARCHAR2(240);
200 l_virtual_path VARCHAR2(240);
201 l_protocol_port_value VARCHAR2(240);
202
203 CURSOR login_person_cur (cp_user_id fnd_user.user_id%TYPE) IS
204 SELECT usr.user_name, hz.person_last_name||', '||hz.person_first_name person_name, usr.email_address
205 FROM fnd_user usr, hz_parties hz
206 WHERE usr.user_id = cp_user_id AND
207 usr.person_party_id = hz.party_id;
208
209 CURSOR approver_name_cur (cp_approver fnd_user.user_name%TYPE) IS
210 SELECT hz.party_name
211 FROM fnd_user usr, hz_parties hz
212 WHERE usr.user_name = cp_approver AND
213 usr.person_party_id = hz.party_id;
214
215 CURSOR temp_dtl_cur(cp_template_id IN NUMBER, cp_lookup_type IN VARCHAR2) IS
216 SELECT temp.template_name name,
217 NVL(temp.template_title,nbsp) title,
218 lk.meaning type,
219 NVL(temp.description,nbsp) description,
220 NVL(TO_CHAR(temp.expiry_date),nbsp) expiry_date,
221 created_by
222 FROM igp_wz_templates temp, igp_lookup_values lk
223 WHERE temp.template_id = cp_template_id AND
224 temp.template_type_code = lk.lookup_code AND
225 lk.lookup_type = cp_lookup_type;
226
227 temp_dtl_rec temp_dtl_cur%ROWTYPE;
228 login_person_rec login_person_cur%ROWTYPE;
229 l_email_address VARCHAR2(500);
230 BEGIN
231
232 IF (funcmode = 'RUN') THEN
233
234 nbsp := fnd_global.local_chr(38) || 'nbsp;';
235
236 l_login_user_id := wf_engine.GetItemAttrText( itemtype => itemtype,
237 itemkey => itemkey,
238 aname => 'P_USER_ID');
239
240 l_template_id := wf_engine.GetItemAttrText( itemtype => itemtype,
241 itemkey => itemkey,
242 aname => 'P_TEMPLATE_ID');
243
244 l_template_name := wf_engine.GetItemAttrText( itemtype => itemtype,
245 itemkey => itemkey,
246 aname => 'P_TEMPLATE_NAME');
247
248 OPEN login_person_cur(l_login_user_id);
249 FETCH login_person_cur INTO login_person_rec;
250 CLOSE login_person_cur;
251
252 wf_engine.setitemattrtext(
253 itemType => itemtype,
254 itemKey => itemkey,
255 aname => 'REQUESTOR_NAME',
256 avalue => login_person_rec.person_name);
257
258 wf_engine.setitemattrtext(
259 itemType => itemtype,
260 itemKey => itemkey,
261 aname => 'REQUESTOR',
262 avalue => login_person_rec.user_name);
263
264 OPEN temp_dtl_cur(l_template_id,'IGP_WZ_TEMP_TYPE');
265 FETCH temp_dtl_cur INTO temp_dtl_rec;
266 CLOSE temp_dtl_cur;
267
268 l_protocol_port := FND_PROFILE.VALUE('ICX_FORMS_LAUNCHER');
269 l_virtual_path := FND_PROFILE.VALUE('ICX_OA_HTML');
270
271 -- Create the preview template link dynamically from the profiles
272 /* l_protocol_port_value := SUBSTR(l_protocol_port,1,INSTR(l_protocol_port,'/',1,3));
273 l_preview_link := l_protocol_port_value || l_virtual_path ||'/'||'OA.jsp?OAFunc=IGP_WZ_PREVIEW_TEMP_PAGE'||l_var||'tempId='||l_template_id;
274
275 l_preview_link := '<a href='''||l_preview_link||'''>'||temp_dtl_rec.name||'</a>';*/
276
277 wf_engine.setitemattrtext(
278 itemType => itemtype,
279 itemKey => itemkey,
280 aname => 'P_TEMPLATE_NAME',
281 avalue => temp_dtl_rec.name);
282
283 wf_engine.setitemattrtext(
284 itemType => itemtype,
285 itemKey => itemkey,
286 aname => 'LINK_TEMP_NAME',
287 avalue => temp_dtl_rec.name);
288
289 wf_engine.setitemattrtext(
290 itemType => itemtype,
291 itemKey => itemkey,
292 aname => 'TEMP_TITLE',
293 avalue => temp_dtl_rec.title);
294
295 wf_engine.setitemattrtext(
296 itemType => itemtype,
297 itemKey => itemkey,
298 aname => 'TEMP_TYPE',
299 avalue => temp_dtl_rec.type);
300
301 wf_engine.setitemattrtext(
302 itemType => itemtype,
303 itemKey => itemkey,
304 aname => 'TEMP_DESC',
305 avalue => temp_dtl_rec.description);
306
307 wf_engine.setitemattrtext(
308 itemType => itemtype,
309 itemKey => itemkey,
310 aname => 'TEMP_EXP_DT',
311 avalue => temp_dtl_rec.expiry_date);
312
313 OPEN login_person_cur(temp_dtl_rec.created_by);
314 FETCH login_person_cur INTO login_person_rec;
315 CLOSE login_person_cur;
316
317 wf_engine.setitemattrtext(
318 itemType => itemtype,
319 itemKey => itemkey,
320 aname => 'TEMP_AUTHOR',
321 avalue => login_person_rec.person_name);
322
323 l_email_address := '<a href=mailto:'||login_person_rec.email_address||'>'||login_person_rec.email_address||'</a>';
324
325 wf_engine.setitemattrtext(
326 itemType => itemtype,
327 itemKey => itemkey,
328 aname => 'TEMP_AUTHOR_EMAIL',
329 avalue => l_email_address);
330
331 -- Check that the profile for approver is set properly.
332 -- If not set throw an error
333 l_approver := FND_PROFILE.VALUE('IGP_WZ_TEMP_APPROVER');
334
335 IF l_approver IS NULL THEN
336
337 FND_MESSAGE.SET_NAME('IGS','IGP_WZ_TEMP_APPROVE_NO_PROF');
338
339 Wf_Engine.SetItemAttrText(
340 itemType => itemtype,
341 itemKey => itemkey,
342 aname => 'ERROR_MSG',
343 avalue => FND_MESSAGE.GET());
344
345 resultout := 'COMPLETE:F';
346 RETURN;
347 END IF;
348
349 Wf_Engine.SetItemAttrText(
350 itemType => itemtype,
351 itemKey => itemkey,
352 aname => 'APPROVER',
353 avalue => l_approver);
354
355 OPEN approver_name_cur(l_approver);
356 FETCH approver_name_cur INTO l_approver_name;
357 CLOSE approver_name_cur;
358
359 Wf_Engine.SetItemAttrText(
360 itemType => itemtype,
361 itemKey => itemkey,
362 aname => 'APPROVER_NAME',
363 avalue => l_approver_name);
364
365 -- If everything is fine call the procedure to update the status to pending
366 pending_status (l_template_id);
367
368 resultout := 'COMPLETE:S';
369
370 END IF;
371 EXCEPTION
372 WHEN OTHERS THEN
373 l_error_message := SQLERRM;
374 wf_core.context('igp_wz_publish_template_pkg','publish_status',itemtype,itemkey ,l_error_message);
375 RAISE;
376
377 END template_preprocess;
378
379 END igp_wz_publish_template_pkg;