[Home] [Help]
PACKAGE BODY: APPS.PA_DCTN_APRV_NOTIFICATION
Source
1 PACKAGE BODY PA_DCTN_APRV_NOTIFICATION as
2 /* $Header: PADTNWFB.pls 120.1.12010000.1 2009/07/21 10:59:39 sosharma noship $ */
3
4 p_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
5 g_error_message VARCHAR2(1000) :='';
6 g_error_stack VARCHAR2(500) :='';
7 g_error_stage VARCHAR2(100) :='';
8
9 PROCEDURE Start_Dctn_Aprv_Wf( p_dctn_req_id IN NUMBER
10 ,x_err_stack IN OUT NOCOPY VARCHAR2
11 ,x_err_stage IN OUT NOCOPY VARCHAR2
12 ,x_err_code OUT NOCOPY NUMBER
13 ) IS
14
15 CURSOR c_starter_name(l_starter_user_id NUMBER) IS
16 SELECT user_name
17 FROM FND_USER
18 WHERE user_id = l_starter_user_id;
19
20 CURSOR c_starter_full_name(l_starter_user_id NUMBER) IS
21 SELECT e.first_name||' '||e.last_name
22 FROM FND_USER f, PER_ALL_PEOPLE_F e
23 WHERE f.user_id = l_starter_user_id
24 AND f.employee_id = e.person_id
25 AND e.effective_end_date = ( SELECT MAX(papf.effective_end_date)
26 FROM per_all_people_f papf
27 WHERE papf.person_id = e.person_id);
28 CURSOR c_wf_started_date IS
29 SELECT SYSDATE FROM SYS.DUAL;
30
31 CURSOR c_vendor_info (c_vendor_id NUMBER) IS
32 SELECT pov.vendor_name Vendor_Name
33 FROM PO_VENDORS pov
34 WHERE pov.vendor_id = c_vendor_id;
35
36 l_proj_info_rec c_proj_info%ROWTYPE;
37
38 itemkey VARCHAR2(30);
39 l_wf_started_date DATE;
40 l_workflow_started_by_id NUMBER;
41 l_user_full_name VARCHAR(400);
42 l_user_name VARCHAR(240);
43 l_resp_id NUMBER;
44 l_err_code NUMBER := 0;
45 l_err_stack VARCHAR2(2000);
46 l_err_stage VARCHAR2(2000);
47 l_content_id NUMBER;
48 l_vendor_name PO_VENDORS.vendor_name%TYPE;
49
50 itemtype CONSTANT VARCHAR2(15) := 'PADCTNWF';
51 l_process CONSTANT VARCHAR2(20) := 'DEDUCTION_REQ_NTFY';
52
53 c_dctn_hdr_rec c_dctn_hdr%ROWTYPE;
54
55 BEGIN
56
57 l_content_id := 0;
58
59 -- Fetch Receipt id and invoice_id
60 OPEN c_dctn_hdr(p_dctn_req_id);
61 FETCH c_dctn_hdr INTO c_dctn_hdr_rec;
62 IF c_dctn_hdr%NOTFOUND THEN
63 x_err_code := 100;
64 x_err_stage := 10;
65 x_err_stack := 'PA_DCTN_HDR_NOT_EXISTS';
66 CLOSE c_dctn_hdr;
67 return;
68 END IF;
69 CLOSE c_dctn_hdr;
70
71 OPEN c_vendor_info(c_dctn_hdr_rec.vendor_id);
72 FETCH c_vendor_info INTO l_vendor_name;
73 CLOSE c_vendor_info;
74
75 x_err_code := 0;
76
77 --get the unique identifier for this specific workflow
78 SELECT pa_workflow_itemkey_s.nextval
79 INTO itemkey
80 FROM DUAL;
81
82 -- Need this to populate the attribute information in Workflow
83 l_workflow_started_by_id := FND_GLOBAL.user_id;
84 l_resp_id := FND_GLOBAL.resp_id;
85
86 -- Create a new Wf process
87 WF_ENGINE.CreateProcess( itemtype => itemtype,
88 itemkey => itemkey,
89 process => l_process);
90
91
92 -- Fetch all required info to populate Wf Attributes
93 OPEN c_starter_name(l_workflow_started_by_id );
94 FETCH c_starter_name INTO l_user_name;
95 IF c_starter_name%NOTFOUND THEN
96 x_err_code := 100;
97 x_err_stage := 20;
98 END IF;
99 CLOSE c_starter_name;
100
101 OPEN c_starter_full_name(l_workflow_started_by_id );
102 FETCH c_starter_full_name INTO l_user_full_name;
103 IF c_starter_full_name%NOTFOUND THEN
104 x_err_code := 100;
105 x_err_stage:= 30;
106 END IF;
107 CLOSE c_starter_full_name;
108
109 OPEN c_wf_started_date;
110 FETCH c_wf_started_date INTO l_wf_started_date;
111 CLOSE c_wf_started_date;
112
113 OPEN c_proj_info( c_dctn_hdr_rec.project_id);
114 FETCH c_proj_info INTO l_proj_info_rec;
115 IF c_proj_info%NOTFOUND THEN
116 x_err_code := 100;
117 x_err_stage:= 40;
118 END IF;
119 CLOSE c_proj_info;
120
121 IF x_err_code = 0 THEN
122 Generate_Dctn_Aprv_Notify(p_item_type => itemtype
123 ,p_item_key => itemkey
124 ,p_dctn_hdr_rec => c_dctn_hdr_rec
125 ,p_proj_info_rec => l_proj_info_rec
126 ,x_content_id => l_content_id
127 );
128 END IF;
129
130 IF l_proj_info_rec.project_id IS NOT NULL THEN
131 WF_ENGINE.SetItemAttrNumber (itemtype => itemtype
132 ,itemkey => itemkey
133 ,aname => 'PROJECT_ID'
134 ,avalue => l_proj_info_rec.project_id
135 );
136 END IF;
137
138 IF l_proj_info_rec.project_number IS NOT NULL THEN
139 WF_ENGINE.SetItemAttrText (itemtype => itemtype
140 ,itemkey => itemkey
141 ,aname => 'PROJECT_NUMBER'
142 ,avalue => l_proj_info_rec.project_number
143 );
144 END IF;
145
146 IF l_proj_info_rec.project_name IS NOT NULL THEN
147 WF_ENGINE.SetItemAttrText (itemtype => itemtype
148 ,itemkey => itemkey
149 ,aname => 'PROJECT_NAME'
150 ,avalue => l_proj_info_rec.project_name
151 );
152 END IF;
153
154 IF c_dctn_hdr_rec.deduction_req_num IS NOT NULL THEN
155 WF_ENGINE.SetItemAttrText (itemtype => itemtype
156 ,itemkey => itemkey
157 ,aname => 'DEDUCTION_REQ_NUM'
158 ,avalue => c_dctn_hdr_rec.deduction_req_num
159 );
160 END IF;
161
162 IF c_dctn_hdr_rec.currency_code IS NOT NULL THEN
163 WF_ENGINE.SetItemAttrText (itemtype => itemtype
164 ,itemkey => itemkey
165 ,aname => 'CURRENCY_CODE'
166 ,avalue => c_dctn_hdr_rec.currency_code
167 );
168 END IF;
169
170 IF c_dctn_hdr_rec.total_amount IS NOT NULL THEN
171 WF_ENGINE.SetItemAttrNumber (itemtype => itemtype
172 ,itemkey => itemkey
173 ,aname => 'APPLIED_AMOUNT'
174 ,avalue => c_dctn_hdr_rec.total_amount
175 );
176 END IF;
177
178 IF c_dctn_hdr_rec.debit_memo_num IS NOT NULL THEN
179 WF_ENGINE.SetItemAttrText (itemtype => itemtype
180 ,itemkey => itemkey
181 ,aname => 'DEBIT_MEMO_NUM'
182 ,avalue => c_dctn_hdr_rec.debit_memo_num
183 );
184 END IF;
185
186 IF c_dctn_hdr_rec.vendor_id IS NOT NULL THEN
187
188 WF_ENGINE.SetItemAttrNumber (itemtype => itemtype
189 ,itemkey => itemkey
190 ,aname => 'VENDOR_ID'
191 ,avalue => c_dctn_hdr_rec.vendor_id
192 );
193
194 WF_ENGINE.SetItemAttrText (itemtype => itemtype
195 ,itemkey => itemkey
196 ,aname => 'VENDOR_NAME'
197 ,avalue => l_vendor_name
198 );
199 END IF;
200
201
202 IF l_content_id IS NOT NULL THEN
203 WF_ENGINE.SetItemAttrNumber (itemtype => itemtype
204 ,itemkey => itemkey
205 ,aname => 'CONTENT_ID'
206 ,avalue => l_content_id
207 );
208 END IF;
209
210 IF l_workflow_started_by_id IS NOT NULL THEN
211 WF_ENGINE.SetItemAttrNumber (itemtype => itemtype
212 ,itemkey => itemkey
213 ,aname => 'WORKFLOW_STARTED_BY_ID'
214 ,avalue => l_workflow_started_by_id
215 );
216 END IF;
217
218 IF l_user_name IS NOT NULL THEN
219 WF_ENGINE.SetItemAttrText (itemtype => itemtype
220 ,itemkey => itemkey
221 ,aname => 'WORKFLOW_STARTED_BY_NAME'
222 ,avalue => l_user_name
223 );
224 END IF;
225
226 IF l_user_full_name IS NOT NULL THEN
227 WF_ENGINE.SetItemAttrText (itemtype => itemtype
228 ,itemkey => itemkey
229 ,aname => 'WORKFLOW_STARTED_BY_FULL_NAME'
230 ,avalue => l_user_full_name
231 );
232 END IF;
233
234 IF l_resp_id IS NOT NULL THEN
235 WF_ENGINE.SetItemAttrNumber (itemtype => itemtype
236 ,itemkey => itemkey
237 ,aname => 'RESPONSIBILITY_ID'
238 ,avalue => l_resp_id
239 );
240 END IF;
241
242 IF l_wf_started_date IS NOT NULL THEN
243 WF_ENGINE.SetItemAttrText (itemtype => itemtype
244 ,itemkey => itemkey
245 ,aname => 'WF_STARTED_DATE'
246 ,avalue => l_wf_started_date
247 );
248 END IF;
249
250 WF_ENGINE.StartProcess (itemtype => itemtype
251 ,itemkey => itemkey
252 );
253
254
255 IF x_err_code = 0 THEN
256 PA_WORKFLOW_UTILS.Insert_WF_Processes (p_wf_type_code => 'PADCTNWF'
257 ,p_item_type => ItemType
258 ,p_item_key => ItemKey
259 ,p_entity_key1 => c_dctn_hdr_rec.deduction_req_id
260 ,p_description => c_dctn_hdr_rec.description
261 ,p_err_code => l_err_code
262 ,p_err_stage => l_err_stage
263 ,p_err_stack => l_err_stack
264 );
265 END IF;
266
267 IF l_err_code <> 0 THEN
268 x_err_code := l_err_code;
269 x_err_stage := l_err_stage;
270 x_err_stack := l_err_stack;
271 END IF;
272
273 EXCEPTION
274 WHEN FND_API.G_EXC_ERROR THEN
275 WF_CORE.CONTEXT('PA_DCTN_APRV_NOTIFICATION ','Start_Dctn_Aprv_Wf');
276 RAISE;
277 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
278 x_err_code := SQLCODE;
279 WF_CORE.CONTEXT('PA_DCTN_APRV_NOTIFICATION','Start_Dctn_Aprv_Wf');
280 RAISE;
281 WHEN OTHERS THEN
282 x_err_code := SQLCODE;
283 WF_CORE.CONTEXT('PA_DCTN_APRV_NOTIFICATION','Start_Dctn_Aprv_Wf');
284 RAISE;
285 END Start_Dctn_Aprv_Wf;
286
287 PROCEDURE Generate_Dctn_Aprv_Notify (p_item_type IN VARCHAR2
288 ,p_item_key IN VARCHAR2
289 ,p_dctn_hdr_rec IN c_dctn_hdr%ROWTYPE
290 ,p_proj_info_rec IN c_proj_info%ROWTYPE
291 ,x_content_id OUT NOCOPY NUMBER) IS
292
293 CURSOR c_orgz_info (p_carrying_out_organization_id NUMBER) IS
294 SELECT name organization_name
295 FROM HR_ORGANIZATION_UNITS
296 WHERE organization_id = p_carrying_out_organization_id;
297
298 CURSOR c_vendor_info (c_vendor_id NUMBER, c_vendor_site_id NUMBER) IS
299 SELECT pov.vendor_name Vendor_Name,
300 povs.vendor_site_code Vendor_Site
301 FROM PO_VENDORS pov,
302 PO_VENDOR_SITES_ALL povs
303 WHERE pov.vendor_id = povs.vendor_id
304 AND pov.vendor_id = c_vendor_id
305 AND povs.vendor_site_id = c_vendor_site_id;
306
307 CURSOR c_ci_info IS
308 SELECT description
309 FROM PA_CONTROL_ITEMS
310 WHERE ci_id = p_dctn_hdr_rec.ci_id;
311
312 l_vend_info_rec c_vendor_info%ROWTYPE;
313 l_orgz_info_rec c_orgz_info%ROWTYPE;
314 l_proj_manager_rec c_proj_manager%ROWTYPE;
315 l_manager_rec c_manager%ROWTYPE;
316
317 l_clob CLOB;
318 l_text VARCHAR2(32767);
319 l_index NUMBER;
320 x_return_status VARCHAR2(1);
321 x_msg_count NUMBER;
322 x_msg_data VARCHAR2(250);
323 l_err_code NUMBER := 0;
324 l_err_stack VARCHAR2(630);
325 l_err_stage VARCHAR2(80);
326 l_page_content_id NUMBER :=0;
327 l_ci_type_class_code VARCHAR2(15);
328 l_ci_description PA_CONTROL_ITEMS.description%TYPE;
329
330 PRAGMA AUTONOMOUS_TRANSACTION;
331
332 BEGIN
333
334 OPEN c_orgz_info(p_proj_info_rec.organization_id);
335 FETCH c_orgz_info INTO l_orgz_info_rec;
336 CLOSE c_orgz_info;
337
338 OPEN c_proj_manager(p_proj_info_rec.project_id);
339 FETCH c_proj_manager INTO l_proj_manager_rec;
340 IF (c_proj_manager%FOUND) THEN
341 OPEN c_manager(l_proj_manager_rec.manager_employee_id);
342 FETCH c_manager INTO l_manager_rec;
343 IF c_manager%ISOPEN THEN
344 CLOSE c_manager;
345 END IF;
346 END IF;
347 CLOSE c_proj_manager;
348
349 OPEN c_vendor_info(p_dctn_hdr_rec.vendor_id, p_dctn_hdr_rec.vendor_site_id);
350 FETCH c_vendor_info INTO l_vend_info_rec;
351 CLOSE c_vendor_info;
352
353 x_content_id := 0;
354
355 PA_PAGE_CONTENTS_PUB.Create_Page_Contents(p_init_msg_list => fnd_api.g_false
356 ,p_validate_only => fnd_api.g_false
357 ,p_object_type => 'PA_DCTN_APRV_NOTIFY'
358 ,p_pk1_value => p_dctn_hdr_rec.deduction_req_id
359 ,p_pk2_value => NULL
360 ,x_page_content_id => l_page_content_id
361 ,x_return_status => x_return_status
362 ,x_msg_count => x_msg_count
363 ,x_msg_data => x_msg_data);
364 x_content_id := l_page_content_id;
365
366 BEGIN
367 SELECT page_content
368 INTO l_clob
369 FROM PA_PAGE_CONTENTS
370 WHERE page_content_id = l_page_content_id FOR UPDATE NOWAIT;
371 EXCEPTION
372 WHEN NO_DATA_FOUND THEN
373 RAISE;
374 END;
375
376 l_text := '';
377
378 --Starting the page content
379 l_text := '<table width="100%" border="0" cellspacing="0" cellpadding="0"><tr><td>';
380 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
381
382 -- START : Project Information Section
383 l_text := '<table cellpadding="0" cellspacing="0" border= "0" width="100%"><tr><td><img src="/OA_HTML/cabo/images/swan/t.gif" /></td></tr>';
384 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
385
386 --Heading
387 l_text := '<tr><td height="12"><table cellpadding="0" cellspacing="0" border="0" width="100%" style="background-image:url(/OA_HTML/cabo/images/swan/headingBarBg.gif);">';
388 l_text := l_text || '<tr><td width="100%"><h2 valign="middle" marginheight="0" style="padding:0px 0px 0px 8px;margin:5px 0px 0px 0px;margin-top:1px;margin-bottom:0px;border-bottom:1px ';
389 l_text := l_text || 'solid #aabed5"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#3C3C3C" size="2"><b>Project Information</b></font></h2></td></tr></table></td></tr>';
390 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
391
392 l_text := '<tr><td height="8" bgcolor="#EAEFF5"></td></tr><tr><td><table cellpadding="0" cellspacing="0" border="0" width="100%"><tr><td bgcolor="#EAEFF5">';
393 l_text := l_text || '<table border="0" cellspacing="0" cellpadding="0"><tr><td width="5%"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top">';
394 l_text := l_text || '<table border="0" cellspacing="0" cellpadding="0">';
395 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
396
397 --Project name
398 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Project Name</font>';
399 l_text := l_text || '</td><td width="12"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" ';
400 l_text := l_text || 'color="#000000" size="2"><b>' || p_proj_info_rec.project_name || '</b><img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="15" /></font></td>';
401 l_text := l_text || '</tr><tr><td height="3"></td><td></td><td></td></tr>';
402 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
403
404 --Project Number
405 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Project Number</font>';
406 l_text := l_text || '</td><td width="12"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" ';
407 l_text := l_text || 'color="#000000" size="2"><b>' || p_proj_info_rec.project_number || '</b><img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="15" />';
408 l_text := l_text || '</font></td></tr><tr><td height="3"></td><td></td><td></td></tr>';
409 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
410
411 --Organization
412 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Organization</font>';
413 l_text := l_text || '</td><td width="12"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" ';
414 l_text := l_text || 'color="#000000" size="2"><b>' || l_orgz_info_rec.organization_name || '</b><img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="15" />';
415 l_text := l_text || '</font></td></tr><tr><td height="3"></td><td></td><td></td></tr>';
416 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
417
418 --project type
419 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Project Type</font>';
420 l_text := l_text || '</td><td width="12"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" ';
421 l_text := l_text || 'color="#000000" size="2"><b>' || p_proj_info_rec.project_type || '</b><img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="15" /></font></td></tr>';
422 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
423
424 l_text := '</table></td><td width="5%"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><table border="0" cellspacing="0" cellpadding="0">';
425 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
426
427 --Project Manager
428 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Project Manager';
429 l_text := l_text || '</font></td><td width="12"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" ';
430 l_text := l_text || 'color="#000000" size="2"><b>' || l_manager_rec.full_name || '</b><img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="5" /></font></td></tr><tr><td height="3">';
431 l_text := l_text || '</td><td></td><td></td></tr>';
432 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
433
434 --project start date
435 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Start Date</font></td>';
436 l_text := l_text || '<td width="12"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" ';
437 l_text := l_text || 'color="#000000" size="2"><b>' || p_proj_info_rec.start_date || '</b><img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="15" />';
438 l_text := l_text || '</font></td></tr><tr><td height="3"></td><td></td><td></td></tr>';
439 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
440
441 --Project finish date
442 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Finish Date</font></td>';
443 l_text := l_text || '<td width="12"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" ';
444 l_text := l_text || 'size="2"><b>' || p_proj_info_rec.end_date || '</b><img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="15" /></font></td></tr><tr><td height="3"></td><td></td><td></td></tr>';
445 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
446
447 -- project status
448 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Status</font></td>';
449 l_text := l_text || '<td width="12"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" ';
450 l_text := l_text || 'color="#000000" size="2"><b>' || p_proj_info_rec.project_status || '</b><img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="15" /></font></td></tr>';
451 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
452
453 l_text := '</table></td></tr></table></td></tr></table></td></tr><tr><td height="8" bgcolor="#EAEFF5"></td></tr></table>';
454 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
455
456 -- END : Project Information Section
457
458 l_text := '<table cellpadding="0" cellspacing="0" border= "0" width="100%"><tr><td height="10"><img src="/OA_HTML/cabo/images/swan/t.gif" /></td></tr>';
459 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
460
461 --Heading
462 l_text := '<tr><td><table cellpadding="0" cellspacing="0" border="0" width="100%" style="background-image:url(/OA_HTML/cabo/images/swan/headingBarBg.gif);"><tr>';
463 l_text := l_text || '<td width="100%"><h2 valign="middle" marginheight="0" style="padding:0px 0px 0px 8px;margin:5px 0px 0px 0px;margin-top:1px;margin-bottom:0px;border-bottom:1px solid #aabed5">';
464 l_text := l_text || '<font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#3C3C3C" size="2"><b>Deduction Request Information</b></font></h2></td></tr></table></td></tr>';
465 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
466
467 l_text := '<tr><td height="8" bgcolor="#EAEFF5"></td></tr><tr><td><table cellpadding="0" cellspacing="0" border="0" width="100%"><tr><td bgcolor="#EAEFF5">';
468 l_text := l_text || '<table border="0" cellspacing="0" cellpadding="0"><tr><td width="5%"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top">';
469 l_text := l_text || '<table border="0" cellspacing="0" cellpadding="0">';
470 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
471
472 --Deduction Request num
473 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Deduction Request Number</font></td>';
474 l_text := l_text || '<td width="12"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" ';
475 l_text := l_text || 'size="2"><b>' || p_dctn_hdr_rec.deduction_req_num || '</b><img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="15" /></font></td></tr><tr><td height="3">';
476 l_text := l_text || '</td><td></td><td></td></tr>';
477 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
478
479 --Deduction Request date
480 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Deduction Request Date</font>';
481 l_text := l_text || '</td><td width="12"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" ';
482 l_text := l_text || 'size="2"><b>' || p_dctn_hdr_rec.deduction_req_date || '</b><img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="15" /></font></td></tr><tr><td height="3">';
483 l_text := l_text || '</td><td></td><td></td></tr>';
484 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
485
486 -- Supplier
487 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Supplier</font>';
488 l_text := l_text || '</td><td width="12"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" ';
489 l_text := l_text || 'size="2"><b>' || l_vend_info_rec.Vendor_Name || '</b><img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="15" /></font></td></tr>';
490 l_text := l_text || '<tr><td height="3"></td><td></td><td></td></tr>';
491 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
492
493 --Supplier Site
494 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Supplier Site</font></td>';
495 l_text := l_text || '<td width="12"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" ';
496 l_text := l_text || 'size="2"><b>' || l_vend_info_rec.Vendor_Site || '</b><img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="15" /></font></td></tr><tr>';
497 l_text := l_text || '<td height="3"></td><td></td><td></td></tr>';
498 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
499
500 --PO Number
501 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">PO Number</font>';
502 l_text := l_text || '</td><td width="12"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" ';
503 l_text := l_text || 'color="#000000" size="2"><b>' || p_dctn_hdr_rec.po_number || '</b><img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="15" />';
504 l_text := l_text || '</font></td></tr><tr><td height="3"></td><td></td><td></td></tr>';
505 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
506
507 IF p_dctn_hdr_rec.document_type = 'C' THEN
508 -- Deduction Request Description
509 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Description</font>';
510 l_text := l_text || '</td><td width="12"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" ';
511 l_text := l_text || 'color="#000000" size="2"><b>' || p_dctn_hdr_rec.description || '</b></font></td></tr><tr><td height="3"></td><td></td><td></td></tr>';
512 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
513 END IF;
514
515 l_text := '</table></td><td width="5%"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><table border="0" cellspacing="0" cellpadding="0">';
516 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
517
518 --Debit memo number
519 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Debit Memo Number</font>';
520 l_text := l_text || '</td><td width="12"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" ';
521 l_text := l_text || 'color="#000000" size="2"><b>' || p_dctn_hdr_rec.Debit_memo_num || '</b></font></td></tr><tr><td height="3"></td><td></td><td></td></tr>';
522 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
523
524 --Debit Memo Date
525 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Debit Memo Date</font>';
526 l_text := l_text || '</td><td width="12"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" ';
527 l_text := l_text || 'color="#000000" size="2"><b>' || p_dctn_hdr_rec.debit_memo_date || '</b></font></td></tr><tr><td height="3"></td><td></td><td></td></tr>';
528 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
529
530 --Debit Memo amt
531 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Debit Memo Amount</font>';
532 l_text := l_text || '</td><td width="12"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" ';
533 l_text := l_text || 'color="#000000" size="2"><b>' || p_dctn_hdr_rec.total_amount || '</b><img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="15" />';
534 l_text := l_text || '</font></td></tr><tr><td height="3"></td><td></td><td></td></tr>';
535 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
536
537 --Debit Memo Currency
538 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Debit Memo Currency</font>';
539 l_text := l_text || '</td><td width="12"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" ';
540 l_text := l_text || 'size="2"><b>' || p_dctn_hdr_rec.currency_code || '</b><img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="15" /></font></td></tr>';
541 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
542
543 IF p_dctn_hdr_rec.document_type = 'C' THEN
544
545 OPEN c_ci_info;
546 FETCH c_ci_info INTO l_ci_description;
547 CLOSE c_ci_info;
548
549 --Change Doc Number
550 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Change Document Number</font>';
551 l_text := l_text || '</td><td width="12"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" ';
552 l_text := l_text || 'color="#000000" size="2"><b>' || p_dctn_hdr_rec.change_doc_num || '</b><img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="15" /></font></td></tr>';
553 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
554
555 --Change Doc Type
556 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Change Document Type</font>';
557 l_text := l_text || '</td><td width="12"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" ';
558 l_text := l_text || 'color="#000000" size="2"><b>' || p_dctn_hdr_rec.change_doc_type || '</b><img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="15" /></font></td></tr>';
559 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
560
561 --Description
562 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Change Document Description</font>';
563 l_text := l_text || '</td><td width="12"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" ';
564 l_text := l_text || 'color="#000000" size="2"><b>' || l_ci_description || '</b><img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="15" /></font></td></tr>';
565 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
566
567 ELSE
568 -- Deduction Request Description
569 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Description</font>';
570 l_text := l_text || '</td><td width="12"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" ';
571 l_text := l_text || 'color="#000000" size="2"><b>' || p_dctn_hdr_rec.description || '</b></font></td></tr><tr><td height="3"></td><td></td><td></td></tr>';
572 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
573 END IF;
574
575 --This cell is Empty
576 l_text := '<tr><td height="3"></td><td></td><td></td></tr><tr><td height="3"></td><td></td><td></td></tr>';
577 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
578
579 l_text := '</table></td></tr></table></td></tr></table></td></tr><tr><td height="8" bgcolor="#EAEFF5"></td></tr></table>';
580 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
581
582 --START : References Section
583 l_text := '<table cellpadding="0" cellspacing="0" border= "0" width="100%"><tr><td height="10"><img src="/OA_HTML/cabo/images/swan/t.gif" /></td></tr>';
584 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
585
586 --Header
587 l_text := '<tr><td><table cellpadding="0" cellspacing="0" border="0" width="100%" style="background-image:url(/OA_HTML/cabo/images/swan/headingBarBg.gif);">';
588 l_text := l_text || '<tr><td width="100%"><h2 valign="middle" marginheight="0" style="padding:0px 0px 0px 8px;margin:5px 0px 0px 0px;margin-top:1px;margin-bottom:0px;border-bottom:1px ';
589 l_text := l_text || 'solid #aabed5"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#3C3C3C" size="2"><b>Refrences</b></font></h2></td></tr></table></td></tr>';
590 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
591
592 --URL Section to view deduction request
593 l_text := '<tr><td height="8" bgcolor="#EAEFF5"></td></tr><tr><td> <div><div><table cellpadding="0" cellspacing="0" border="0" width="100%"><tr>';
594 l_text := l_text || '<td bgcolor="#EAEFF5"><table border="0" cellspacing="0" cellpadding="0"><tr><td width="5%"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td>';
595 l_text := l_text || '<td valign="top"><table border="0" cellspacing="0" cellpadding="0"><tr><td align="right" valign="top" nowrap="nowrap"><span align="right">';
596 l_text := l_text || '<img src="/OA_MEDIA/fwkhp_formsfunc.gif" alt="Deduction Request" width="16" height="16" border="0"></span></td><td width="12">';
597 l_text := l_text || '<img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">';
598 l_text := l_text || '<a href="OA.jsp?page=/oracle/apps/pa/subcontractor/webui/PaViewDeductionsPG&_ri=275&addBreadCrumb=RS&DED_REQ_NUM='||p_dctn_hdr_rec.deduction_req_num||'&DED_REQ_ID='||p_dctn_hdr_rec.deduction_req_id||'">Deduction Request </a>';
599 l_text := l_text || '<img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="5" /></font></td></tr><tr>';
600 l_text := l_text || '<td height="3"></td><td></td><td></td></tr></table></tr></table></td></tr></table></td></tr>';
601 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
602
603 IF p_dctn_hdr_rec.document_type = 'C' THEN
604 IF nvl(p_dctn_hdr_rec.change_doc_type,'Change Order') = 'Change Order' THEN
605 l_ci_type_class_code := 'CHANGE_ORDER';
606 ELSE
607 l_ci_type_class_code := 'CHANGE_REQUEST';
608 END IF;
609
610 --URL Section to view change order request
611 l_text := '<tr><td height="8" bgcolor="#EAEFF5"></td></tr><tr><td> <div><div><table cellpadding="0" cellspacing="0" border="0" width="100%"><tr>';
612 l_text := l_text || '<td bgcolor="#EAEFF5"><table border="0" cellspacing="0" cellpadding="0"><tr><td width="5%"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td>';
613 l_text := l_text || '<td valign="top"><table border="0" cellspacing="0" cellpadding="0"><tr><td align="right" valign="top" nowrap="nowrap"><span align="right">';
614 l_text := l_text || '<img src="/OA_MEDIA/fwkhp_formsfunc.gif" alt="Change Document" width="16" height="16" border="0"></span></td><td width="12">';
615 l_text := l_text || '<img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">';
616 l_text := l_text || '<a href="OA.jsp?_rc=PA_CI_CI_REVIEW_LAYOUT&addBreadCrumb=RP&_ri=275&paProjectId=' || p_dctn_hdr_rec.project_id || '&paCiId=' ||p_dctn_hdr_rec.ci_id|| '&paCITypeClassCode='||l_ci_type_class_code||'">Change Document </a>';
617 l_text := l_text || '<img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="5" /></font></td></tr><tr>';
618 l_text := l_text || '<td height="3"></td><td></td><td></td></tr></table></tr></table></td></tr></table></td></tr>';
619 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
620
621 END IF;
622
623 l_text := '<tr><td height="8" bgcolor="#EAEFF5"></td></tr></table>';
624 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
625 --END : References Section
626
627 --Closing the page content
628 l_text := '</td></tr></table>';
629 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
630
631 COMMIT;
632 l_text := '';
633
634 EXCEPTION
635 WHEN OTHERS THEN
636 RAISE;
637 END Generate_Dctn_Aprv_Notify;
638
639 PROCEDURE Select_Project_Manager (itemtype IN VARCHAR2
640 ,itemkey IN VARCHAR2
641 ,actid IN NUMBER
642 ,funcmode IN VARCHAR2
643 ,resultout OUT NOCOPY VARCHAR2)
644 IS
645
646 l_err_code NUMBER := 0;
647 l_resp_id NUMBER;
648 l_project_id NUMBER;
649 l_workflow_started_by_id NUMBER;
650 l_manager_employee_id NUMBER;
651 l_manager_user_id NUMBER;
652 l_manager_user_name VARCHAR2(240);
653 l_manager_full_name VARCHAR2(400);
654 l_return_status NUMBER := 0;
655 l_project_manager_id NUMBER := 0;
656
657 BEGIN
658 --
659 -- Return if WF Not Running
660 --
661 IF (funcmode <> wf_engine.eng_run) THEN
662 resultout := wf_engine.eng_null;
663 RETURN;
664 END IF;
665
666 l_resp_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
667 ,itemkey => itemkey
668 ,aname => 'RESPONSIBILITY_ID');
669
670 l_project_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
671 ,itemkey => itemkey
672 ,aname => 'PROJECT_ID');
673
674 l_workflow_started_by_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
675 ,itemkey => itemkey
676 ,aname => 'WORKFLOW_STARTED_BY_ID');
677
678 -- Based on the Responsibility, Intialize the Application
679 PA_WORKFLOW_UTILS.Set_Global_Attr (p_item_type => itemtype
680 ,p_item_key => itemkey
681 ,p_err_code => l_err_code);
682
683
684 PA_CE_AR_NOTIFY_WF.Select_Project_Manager (p_project_id => l_project_id
685 ,p_project_manager_id => l_manager_employee_id
686 ,p_return_status => l_return_status);
687
688 IF ( l_return_status = 0 ) THEN
689 OPEN c_proj_manager(l_project_id);
690 FETCH c_proj_manager INTO l_manager_employee_id;
691 IF c_proj_manager%ISOPEN THEN
692 CLOSE c_proj_manager;
693 END IF;
694 END IF;
695
696
697 IF (l_manager_employee_id IS NOT NULL ) THEN
698
699 OPEN c_manager( l_manager_employee_id );
700 FETCH c_manager INTO l_manager_user_id
701 ,l_manager_user_name
702 ,l_manager_full_name;
703
704 IF (c_manager%FOUND) THEN
705 IF c_manager%ISOPEN THEN
706 CLOSE c_manager;
707 END IF;
708 WF_ENGINE.SetItemAttrNumber (itemtype => itemtype
709 ,itemkey => itemkey
710 ,aname => 'PROJECT_MANAGER_ID'
711 ,avalue => l_manager_user_id );
712 WF_ENGINE.SetItemAttrText (itemtype => itemtype
713 ,itemkey => itemkey
714 ,aname => 'PROJECT_MANAGER_NAME'
715 ,avalue => l_manager_user_name);
716 WF_ENGINE.SetItemAttrText (itemtype => itemtype
717 ,itemkey => itemkey
718 ,aname => 'PROJECT_MANAGER_FULL_NAME'
719 ,avalue => l_manager_full_name);
720
721 resultout := WF_ENGINE.eng_completed||':'||'T';
722 ELSE
723 IF c_manager%ISOPEN THEN
724 CLOSE c_manager;
725 END IF;
726 resultout := WF_ENGINE.eng_completed||':'||'F';
727 END IF;
728 ELSE
729 resultout := WF_ENGINE.eng_completed||':'||'F';
730 END IF;
731
732 EXCEPTION
733 WHEN FND_API.G_EXC_ERROR THEN
734 WF_CORE.CONTEXT('PA_PWP_NOTIFICATION','SELECT_PROJECT_MANAGER',itemtype, itemkey, to_char(actid), funcmode);
735 RAISE;
736
737 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
738 WF_CORE.CONTEXT('PA_PWP_NOTIFICATION','SELECT_PROJECT_MANAGER',itemtype, itemkey, to_char(actid), funcmode);
739 RAISE;
740
741 WHEN OTHERS THEN
742 WF_CORE.CONTEXT('PA_PWP_NOTIFICATION','SELECT_PROJECT_MANAGER',itemtype, itemkey, to_char(actid), funcmode);
743 RAISE;
744 END Select_Project_Manager;
745
746
747 PROCEDURE SHOW_PWP_NOTIFY_PREVIEW(document_id IN VARCHAR2
748 ,display_type IN VARCHAR2
749 ,document IN OUT NOCOPY CLOB
750 ,document_type IN OUT NOCOPY VARCHAR2) IS
751
752 l_content CLOB;
753
754 CURSOR c_pwp_preview_info IS
755 SELECT page_content
756 FROM PA_PAGE_CONTENTS
757 WHERE page_content_id = document_id
758 AND object_type = 'PA_DCTN_APRV_NOTIFY'
759 AND pk2_value IS NULL;
760
761 l_size number;
762 l_chunk_size PLS_INTEGER:=10000;
763 l_copy_size INT;
764 l_pos INT := 0;
765 l_line VARCHAR2(30000) := '';
766 l_return_status VARCHAR2(1);
767 l_msg_count NUMBER;
768 l_msg_data VARCHAR2(2000);
769
770
771 BEGIN
772
773 OPEN c_pwp_preview_info;
774 FETCH c_pwp_preview_info INTO l_content;
775 IF (c_pwp_preview_info%FOUND) THEN
776 IF c_pwp_preview_info%ISOPEN THEN
777 CLOSE c_pwp_preview_info;
778 END IF;
779 l_size := dbms_lob.getlength(l_content);
780 l_pos := 1;
781 l_copy_size := 0;
782 WHILE (l_copy_size < l_size) LOOP
783 DBMS_LOB.READ(l_content,l_chunk_size,l_pos,l_line);
784 DBMS_LOB.WRITE(document,l_chunk_size,l_pos,l_line);
785 l_copy_size := l_copy_size + l_chunk_size;
786 l_pos := l_pos + l_chunk_size;
787 END LOOP;
788
789 PA_WORKFLOW_UTILS.modify_wf_clob_content(p_document => document
790 ,x_return_status => l_return_status
791 ,x_msg_count => l_msg_count
792 ,x_msg_data => l_msg_data);
793
794 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
795 WF_NOTIFICATION.WriteToClob(document, 'Content Generation failed');
796 DBMS_LOB.writeappend(document, 255, SUBSTR(SQLERRM, 255));
797 END IF;
798 ELSE
799 IF c_pwp_preview_info%ISOPEN THEN
800 CLOSE c_pwp_preview_info;
801 END IF;
802 END IF;
803
804 document_type := 'text/html';
805
806 EXCEPTION
807 WHEN OTHERS THEN
808 WF_NOTIFICATION.WriteToClob(document, 'Content Generation failed');
809 dbms_lob.writeappend(document, 255, substrb(Sqlerrm, 255));
810 NULL;
811 END SHOW_PWP_NOTIFY_PREVIEW;
812
813
814 PROCEDURE APPEND_VARCHAR_TO_CLOB(p_varchar IN varchar2
815 ,p_clob IN OUT NOCOPY CLOB) IS
816
817 l_chunkSize INTEGER;
818 v_offset INTEGER := 0;
819 l_clob clob;
820 l_length INTEGER;
821
822 v_size NUMBER;
823 v_text VARCHAR2(3000);
824
825 BEGIN
826
827 l_chunksize := length(p_varchar);
828 l_length := dbms_lob.getlength(p_clob);
829
830 DBMS_LOB.write(p_clob
831 ,l_chunksize
832 ,l_length+1
833 ,p_varchar);
834 v_size := 1000;
835 DBMS_LOB.read(p_clob, v_size, 1, v_text);
836
837 END APPEND_VARCHAR_TO_CLOB;
838
839 PROCEDURE Submit (itemtype IN VARCHAR2
840 ,itemkey IN VARCHAR2
841 ,actid IN NUMBER
842 ,funcmode IN VARCHAR2
843 ,resultout OUT NOCOPY VARCHAR2) IS
844
845 l_deduction_req_num PA_DEDUCTIONS_ALL.deduction_req_num%TYPE;
846 l_dctn_rec_info PA_DEDUCTIONS.cur_dctn_hdr_info%ROWTYPE;
847
848 CURSOR C1(c_dctn_req_num PA_DEDUCTIONS_ALL.deduction_req_num%TYPE) IS
849 SELECT * FROM PA_DEDUCTIONS_ALL WHERE deduction_req_num = c_dctn_req_num;
850
851 l_msg_count NUMBER;
852 l_msg_data VARCHAR2(4000);
853 l_return_status VARCHAR2(4000);
854 BEGIN
855 resultout := 'COMPLETE : SUCCESS';
856 l_deduction_req_num:=
857 WF_ENGINE.GetItemAttrText
858 (itemtype => itemtype
859 ,itemkey => itemkey
860 ,aname => 'DEDUCTION_REQ_NUM' );
861
862 OPEN C1(l_deduction_req_num);
863 FETCH C1 INTO l_dctn_rec_info;
864 CLOSE C1;
865
866 PA_DEDUCTIONS.Submit_For_DebitMemo
867 (l_dctn_rec_info
868 ,l_msg_count
869 ,l_msg_data
870 ,l_return_status);
871 IF l_return_status <> 'S' THEN
872 resultout := 'COMPLETE : FAILURE'||'Test';
873 END IF;
874 EXCEPTION
875 WHEN OTHERS THEN
876 resultout := 'COMPLETE : FAILURE'||SQLCODE;
877 END;
878
879 FUNCTION show_error(p_error_stack IN VARCHAR2,
880 p_error_stage IN VARCHAR2,
881 p_error_message IN VARCHAR2,
882 p_arg1 IN VARCHAR2 DEFAULT null,
883 p_arg2 IN VARCHAR2 DEFAULT null) RETURN VARCHAR2 IS
884
885 l_result FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
886
887 BEGIN
888 g_error_message := nvl(p_error_message,SUBSTRB(SQLERRM,1,1000));
889
890 fnd_message.set_name('PA','PA_WF_FATAL_ERROR');
891 fnd_message.set_token('ERROR_STACK',p_error_stack);
892 fnd_message.set_token('ERROR_STAGE',p_error_stage);
893 fnd_message.set_token('ERROR_MESSAGE',g_error_message);
894 fnd_message.set_token('ERROR_ARG1',p_arg1);
895 fnd_message.set_token('ERROR_ARG2',p_arg2);
896
897 l_result := fnd_message.get_encoded;
898
899 g_error_message := NULL;
900
901 RETURN l_result;
902 EXCEPTION WHEN OTHERS
903 THEN
904 raise;
905 END show_error;
906
907 END PA_DCTN_APRV_NOTIFICATION;