[Home] [Help]
PACKAGE BODY: APPS.PA_PWP_NOTIFICATION
Source
1 PACKAGE BODY PA_PWP_NOTIFICATION as
2 /* $Header: PAPWPWFB.pls 120.0.12010000.9 2009/10/08 10:49:15 atshukla noship $ */
3
4 -------------------------------------------------------------------------------
5 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
6
7
8 -------------------------------------------------------------------------------
9 -- Procedure : log_message -
10 -- Type : Private -
11 -- Purpose : To create debug log. -
12 -- Note : To create debug log. -
13 -- Assumptions : None. -
14 -- Parameters Type Required Description and Purpose-
15 -- --------------------------- ------ -------- ------------------------
16 -- p_log_msg VARCHAR2 YES Message -
17 -- debug_level NUMBER YES Debug Level -
18 -------------------------------------------------------------------------------
19 PROCEDURE log_message (p_log_msg IN VARCHAR2, debug_level IN NUMBER)
20 IS
21 BEGIN
22 IF P_DEBUG_MODE = 'Y' THEN
23 pa_debug.write('log_message: ' || 'PA PWP Notification: ', 'log: ' || p_log_msg, debug_level);
24 END IF;
25 NULL;
26 END log_message;
27
28
29 -------------------------------------------------------------------------------
30 -- Procedure : Receive_BE -
31 -- Type : Public -
32 -- Purpose : Function for receiving AR Business Event -
33 -- oracle.apps.ar.applications.CashApp.apply -
34 -- Note : To Integrate the PA Workflow with Apply -
35 -- Receipt Event in AR -
36 -- Assumptions : AR Business Event will always be raised upon -
37 -- applying a receipt. -
38 -- Parameters Type Required Description and Purpose-
39 -- --------------------------- ------ -------- ------------------------
40 -- p_subscription_guid RAW YES Subscription details -
41 -- p_event WF_EVENT_T YES Event Details -
42 --(Refer ARU file wftype2s.sql) -
43 -------------------------------------------------------------------------------
44 --
45 FUNCTION Receive_BE(p_subscription_guid In RAW
46 ,p_event IN OUT NOCOPY WF_EVENT_T) RETURN VARCHAR2
47 IS
48 l_org_id NUMBER;
49 l_user_id NUMBER;
50 l_resp_id NUMBER;
51 l_application_id NUMBER;
52 l_security_gr_id NUMBER;
53 l_receivable_application_id NUMBER;
54 l_invoice_status VARCHAR2(30);
55 x_return_status VARCHAR2(30);
56 l_err_code NUMBER := 0;
57 l_err_stage VARCHAR2(2000);
58 l_err_stack VARCHAR2(2000);
59
60 BEGIN
61 log_message('Receive_BE: Subscription Triggered', 3);
62 --Capture parameters from AR Bussiness Event
63 -- Listed below are all 7 variables that we can get from AR BE, Some are commented because they are not required as of now, please uncomment, if required.
64 l_receivable_application_id := p_event.GetValueForParameter('RECEIVABLE_APPLICATION_ID');
65 --l_invoice_status := p_event.GetValueForParameter('TRX_PS_STATUS');
66 --l_org_id := p_event.GetValueForParameter('ORG_ID');
67 l_user_id := p_event.GetValueForParameter('USER_ID');
68 l_resp_id := p_event.GetValueForParameter('RESP_ID');
69 l_application_id := p_event.GetValueForParameter('RESP_APPL_ID');
70 --l_security_gr_id := p_event.GetValueForParameter('SECURITY_GROUP_ID');
71
72 log_message('Receive_BE: Subscription Parameters recieved: receivable_application_id=' || l_receivable_application_id || '*', 3);
73
74 --set the application context.
75 --fnd_global.apps_initialize(l_user_id,l_resp_id,l_application_id); -- 8993957 : Commneted : As this will execute in context set by AR.
76
77 log_message('Receive_BE: Before Calling Wf: receivable_application_id=' || l_receivable_application_id || '*', 3);
78
79 -- Call Wf initiator...
80 PA_PWP_NOTIFICATION.START_AR_NOTIFY_WF (p_receivable_application_id => l_receivable_application_id
81 ,x_err_stack => l_err_stack
82 ,x_err_stage => l_err_stage
83 ,x_err_code => l_err_code);
84
85 log_message('Receive_BE: After Calling Wf: l_err_code=' || l_err_code || '*', 3);
86
87 IF l_err_code = 0 THEN
88 log_message('Receive_BE: Normal Exit', 3);
89 Return 'SUCCESS';
90 ELSE
91 RETURN 'ERROR';
92 END IF;
93
94 EXCEPTION
95 WHEN OTHERS THEN
96 WF_CORE.CONTEXT('PA_PWP_NOTIFICATION', 'RECEIVE_BE', p_event.getEventName(), p_subscription_guid);
97 WF_EVENT.setErrorInfo(p_event, 'ERROR');
98 RETURN 'ERROR';
99
100 END Receive_BE;
101
102
103
104 -------------------------------------------------------------------------------
105 -- Procedure : START_AR_NOTIFY_WF -
106 -- Purpose : Function for Invoking the Workflow. -
107 -- Note : To send notifiactions based on various conditions -
108 -- Assumptions : Parameter receivable_application_id will be passed -
109 -- Parameters Type Required Description and Purpose-
110 -- --------------------------- ------ -------- ------------------------
111 -- p_receivable_application_id NUMBER YES Receipt application Id -
112 -------------------------------------------------------------------------------
113
114 PROCEDURE START_AR_NOTIFY_WF (p_receivable_application_id IN NUMBER,
115 x_err_stack IN OUT NOCOPY VARCHAR2,
116 x_err_stage IN OUT NOCOPY VARCHAR2,
117 x_err_code OUT NOCOPY NUMBER)
118 IS
119
120 CURSOR c_invoice (l_receivable_application_id NUMBER)
121 IS
122 SELECT CASH_RECEIPT_ID rcpt_id
123 ,Applied_customer_trx_id inv_id
124 FROM ar_receivable_applications
125 Where receivable_application_id = l_receivable_application_id;
126
127 CURSOR c_ar_notify_flag (l_inv_id Number)
128 IS
129 SELECT PPA.ar_rec_notify_flag ar_rec_notify_flag
130 FROM ra_customer_trx RCTRX
131 ,pa_projects PPA
132 WHERE RCTRX.customer_trx_id = l_inv_id
133 AND PPA.Segment1 = RCTRX.interface_header_attribute1;
134
135 CURSOR c_starter_name( l_starter_user_id NUMBER )
136 IS
137 SELECT user_name
138 FROM fnd_user
139 WHERE user_id = l_starter_user_id;
140
141 CURSOR c_starter_full_name(l_starter_user_id NUMBER )
142 IS
143 SELECT e.first_name||' '||e.last_name
144 FROM fnd_user f, per_all_people_f e
145 WHERE f.user_id = l_starter_user_id
146 AND f.employee_id = e.person_id
147 AND e.effective_end_date = ( SELECT MAX(papf.effective_end_date)
148 FROM per_all_people_f papf
149 WHERE papf.person_id = e.person_id);
150
151 -- Get System Date for Worflow-Started-Date
152 CURSOR c_wf_started_date
153 IS
154 SELECT sysdate
155 FROM sys.dual;
156
157
158 l_inv_info_rec c_inv_info%ROWTYPE;
159 l_inv_rec c_invoice%ROWTYPE;
160 l_ar_notify_flag_rec c_ar_notify_flag%ROWTYPE;
161 l_proj_info_rec c_proj_info%ROWTYPE;
162
163
164 ItemKey varchar2(30);
165 l_wf_started_date DATE;
166 l_workflow_started_by_id NUMBER;
167 l_user_full_name VARCHAR(400);
168 l_user_name VARCHAR(240);
169 l_resp_id NUMBER;
170 l_err_code NUMBER := 0;
171 l_err_stack VARCHAR2(2000);
172 l_err_stage VARCHAR2(2000);
173 l_content_id NUMBER;
174
175 ItemType CONSTANT VARCHAR2(15) := 'PAPWPAR';
176 l_process CONSTANT VARCHAR2(20) := 'PRO_AR_NOTIFICATION';
177
178
179 BEGIN
180
181 l_content_id := 0;
182
183 log_message('START_AR_NOTIFY_WF: Start: receivable_application_id=' || p_receivable_application_id || '*', 3);
184
185 -- Fetch Receipt id and invoice_id
186 OPEN c_invoice (p_receivable_application_id);
187 FETCH c_invoice INTO l_inv_rec;
188 IF c_invoice%NOTFOUND THEN
189 x_err_code := 10;
190 END IF;
191 IF c_invoice%ISOPEN THEN
192 CLOSE c_invoice;
193 END IF;
194
195 --Fetch AR Receipt Notification Flag at project Level.
196 OPEN c_ar_notify_flag (l_inv_rec.inv_id);
197 FETCH c_ar_notify_flag INTO l_ar_notify_flag_rec;
198 IF c_ar_notify_flag%NOTFOUND THEN
199 x_err_code := 10;
200 END IF;
201 IF c_ar_notify_flag%ISOPEN THEN
202 CLOSE c_ar_notify_flag;
203 END IF;
204
205
206 log_message('START_AR_NOTIFY_WF: Check Project level notify flag: ar_rec_notify_flag=' || l_ar_notify_flag_rec.ar_rec_notify_flag || '*', 3);
207 -- Check for AR Receipt Notification Flag at project Level.
208 IF l_ar_notify_flag_rec.ar_rec_notify_flag = 'Y' THEN
209 x_err_code := 0;
210
211 --get the unique identifier for this specific workflow
212 SELECT pa_workflow_itemkey_s.nextval
213 INTO ItemKey
214 from dual;
215
216 -- Need this to populate the attribute information in Workflow
217 l_workflow_started_by_id := FND_GLOBAL.user_id;
218 l_resp_id := FND_GLOBAL.resp_id;
219
220 -- Create a new Wf process
221 wf_engine.CreateProcess( ItemType => ItemType,
222 ItemKey => ItemKey,
223 process => l_process);
224
225 log_message('START_AR_NOTIFY_WF: Workflow Process created with ItemKey=' || ItemKey || '*', 3);
226
227 -- Fetch all required info to populate Wf Attributes
228 OPEN c_starter_name(l_workflow_started_by_id );
229 FETCH c_starter_name INTO l_user_name;
230 IF c_starter_name%NOTFOUND THEN
231 x_err_code := 10;
232 log_message('START_AR_NOTIFY_WF: Cursor c_starter_name failed to fetch Standard WHO data', 3);
233 END IF;
234 IF c_starter_name%ISOPEN THEN
235 CLOSE c_starter_name;
236 END IF;
237
238 OPEN c_starter_full_name(l_workflow_started_by_id );
239 FETCH c_starter_full_name INTO l_user_full_name;
240 IF c_starter_full_name%NOTFOUND THEN
241 x_err_code := 10;
242 log_message('START_AR_NOTIFY_WF: Cursor c_starter_full_name failed to fetch Standard WHO data', 3);
243 END IF;
244 IF c_starter_full_name%ISOPEN THEN
245 CLOSE c_starter_full_name;
246 END IF;
247
248 OPEN c_wf_started_date;
249 FETCH c_wf_started_date INTO l_wf_started_date;
250 IF c_wf_started_date%ISOPEN THEN
251 CLOSE c_wf_started_date;
252 END IF;
253
254 OPEN c_inv_info(p_receivable_application_id);
255 FETCH c_inv_info INTO l_inv_info_rec;
256 IF c_inv_info%NOTFOUND THEN
257 x_err_code := 10;
258 log_message('START_AR_NOTIFY_WF: Cursor c_inv_info failed to fetch Invoice data', 3);
259 END IF;
260 IF c_inv_info%ISOPEN THEN
261 CLOSE c_inv_info;
262 END IF;
263
264 OPEN c_proj_info( l_inv_info_rec.Project_Number );
265 FETCH c_proj_info INTO l_proj_info_rec;
266 IF c_proj_info%NOTFOUND THEN
267 x_err_code := 10;
268 log_message('START_AR_NOTIFY_WF: Cursor c_proj_info failed to fetch Project data', 3);
269 END IF;
270 IF c_proj_info%ISOPEN THEN
271 CLOSE c_proj_info;
272 END IF;
273
274 log_message('START_AR_NOTIFY_WF: Before Calling Generate_PWP_Notify_Page: x_err_code=' || x_err_code || '*', 3);
275 IF x_err_code = 0 THEN
276 --Generate the page
277 Generate_PWP_Notify_Page(p_item_type => Itemtype
278 ,p_item_Key => Itemkey
279 ,p_inv_info_rec => l_inv_info_rec
280 ,p_proj_info_rec => l_proj_info_rec
281 ,x_content_id => l_content_id
282 );
283 log_message('START_AR_NOTIFY_WF: After Calling Generate_PWP_Notify_Page: Generation Successful.', 3);
284 END IF;
285
286 -- Set the Wf Attributes
287 IF l_proj_info_rec.project_id IS NOT NULL THEN
288 wf_engine.SetItemAttrNumber (itemtype => itemtype
289 ,itemkey => itemKey
290 ,aname => 'PROJECT_ID'
291 ,avalue => l_proj_info_rec.project_id
292 );
293 END IF;
294
295 IF l_proj_info_rec.project_number IS NOT NULL THEN
296 wf_engine.SetItemAttrText (itemtype => itemtype
297 ,itemkey => itemkey
298 ,aname => 'PROJECT_NUMBER'
299 ,avalue => l_proj_info_rec.project_number
300 );
301 END IF;
302
303 IF l_proj_info_rec.project_name IS NOT NULL THEN
304 wf_engine.SetItemAttrText (itemtype => itemtype
305 ,itemkey => itemkey
306 ,aname => 'PROJECT_NAME'
307 ,avalue => l_proj_info_rec.project_name
308 );
309 END IF;
310
311 IF l_inv_info_rec.receipt_number IS NOT NULL THEN
312 wf_engine.SetItemAttrText (itemtype => itemtype
313 ,itemkey => itemkey
314 ,aname => 'RECEIPT_NUMBER'
315 ,avalue => l_inv_info_rec.receipt_number
316 );
317 END IF;
318
319 IF l_inv_info_rec.receipt_currency_code IS NOT NULL THEN
320 wf_engine.SetItemAttrText (itemtype => itemtype
321 ,itemkey => itemkey
322 ,aname => 'RECEIPT_CURRENCY_CODE'
323 ,avalue => l_inv_info_rec.receipt_currency_code
324 );
325 END IF;
326
327 IF l_inv_info_rec.amount_applied IS NOT NULL THEN
328 wf_engine.SetItemAttrText (itemtype => itemtype
329 ,itemkey => itemkey
330 ,aname => 'APPLIED_AMOUNT'
331 ,avalue => l_inv_info_rec.amount_applied
332 );
333 END IF;
334
335 IF l_inv_info_rec.ar_invoice_no IS NOT NULL THEN
336 wf_engine.SetItemAttrText (itemtype => itemtype
337 ,itemkey => itemkey
338 ,aname => 'AR_INVOICE_NUMBER'
339 ,avalue => l_inv_info_rec.ar_invoice_no
340 );
341 END IF;
342
343 IF l_content_id IS NOT NULL THEN
344 wf_engine.SetItemAttrNumber (itemtype => itemtype
345 ,itemkey => itemkey
346 ,aname => 'CONTENT_ID'
347 ,avalue => l_content_id
348 );
349 END IF;
350
351 --Set the standard WHO Attributes of the workflow
352 IF l_workflow_started_by_id IS NOT NULL THEN
353 wf_engine.SetItemAttrNumber (itemtype => itemtype
354 ,itemkey => itemkey
355 ,aname => 'WORKFLOW_STARTED_BY_ID'
356 ,avalue => l_workflow_started_by_id
357 );
358 END IF;
359
360 IF l_user_name IS NOT NULL THEN
361 wf_engine.SetItemAttrText (itemtype => itemtype
362 ,itemkey => itemkey
363 ,aname => 'WORKFLOW_STARTED_BY_NAME'
364 ,avalue => l_user_name
365 );
366 END IF;
367
368 IF l_user_full_name IS NOT NULL THEN
369 wf_engine.SetItemAttrText (itemtype => itemtype
370 ,itemkey => itemkey
371 ,aname => 'WORKFLOW_STARTED_BY_FULL_NAME'
372 ,avalue => l_user_full_name
373 );
374 END IF;
375
376 IF l_resp_id IS NOT NULL THEN
377 wf_engine.SetItemAttrNumber (itemtype => itemtype
378 ,itemkey => itemkey
379 ,aname => 'RESPONSIBILITY_ID'
380 ,avalue => l_resp_id
381 );
382 END IF;
383
384 IF l_wf_started_date IS NOT NULL THEN
385 wf_engine.SetItemAttrText (itemtype => itemtype
386 ,itemkey => itemkey
387 ,aname => 'WF_STARTED_DATE'
388 ,avalue => l_wf_started_date
389 );
390 END IF;
391 -- Attribute assignment done
392
393 log_message('START_AR_NOTIFY_WF: Before starting the Wf', 3);
394 -- Start the Wf
395 wf_engine.StartProcess (itemtype => itemtype
396 ,itemkey => itemkey
397 );
398
399
400 IF l_err_code = 0 THEN
401 log_message('START_AR_NOTIFY_WF: Wf Started, Inserting in PA_WF_PROCESSES: ItemKey=' || ItemKey || 'l_inv_rec.rcpt_id=' || l_inv_rec.rcpt_id || 'l_inv_rec.inv_id=' || l_inv_rec.inv_id || '*', 3);
402 PA_WORKFLOW_UTILS.Insert_WF_Processes (p_wf_type_code => 'PAPWPARN'
403 ,p_item_type => ItemType
404 ,p_item_key => ItemKey
405 ,p_entity_key1 => l_inv_rec.rcpt_id
406 ,p_description => l_inv_rec.inv_id
407 ,p_err_code => l_err_code
408 ,p_err_stage => l_err_stage
409 ,p_err_stack => l_err_stack
410 );
411 END IF;
412
413 END IF; /* ar_rec_notify_flag */
414
415 log_message('START_AR_NOTIFY_WF: Normal Exit', 3);
416
417 EXCEPTION
418 WHEN FND_API.G_EXC_ERROR THEN
419 WF_CORE.CONTEXT('PA_PWP_NOTIFICATION ','START_AR_NOTIFY_WF');
420 RAISE;
421 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
422 x_err_code := SQLCODE;
423 WF_CORE.CONTEXT('PA_PWP_NOTIFICATION ','START_AR_NOTIFY_WF');
424 RAISE;
425 WHEN OTHERS THEN
426 x_err_code := SQLCODE;
427 WF_CORE.CONTEXT('PA_PWP_NOTIFICATION','START_AR_NOTIFY_WF');
428 RAISE;
429
430 END START_AR_NOTIFY_WF;
431
432
433 -------------------------------------------------------------------------------
434 -- Procedure : Generate_PWP_Notify_Page -
435 -- Purpose : Function for Generating the webpage. -
436 -- Note : To Generate the content of Notification Mail -
437 -- Assumptions : None -
438 -- Parameters Type Required Description and Purpose-
439 -- --------------------------- ------ -------- ------------------------
440 -- p_item_type VARCHAR2 YES Itemtype for workflow -
441 -- p_item_Key VARCHAR2 YES ItemKey for workflow -
442 -- p_inv_info_rec c_inv_info YES Data to Prepare Page -
443 -- p_proj_info_rec c_proj_info YES Data to Prepare Page -
444 -------------------------------------------------------------------------------
445
446 Procedure Generate_PWP_Notify_Page (p_item_type IN VARCHAR2
447 ,p_item_Key IN VARCHAR2
448 ,p_inv_info_rec IN c_inv_info%ROWTYPE
449 ,p_proj_info_rec IN c_proj_info%ROWTYPE
450 ,x_content_id OUT NOCOPY NUMBER)
451 IS
452
453 CURSOR c_linked_invoice (l_project_id NUMBER
454 ,l_draft_inv_num NUMBER)
455 IS
456 SELECT ap_inv.invoice_num invoice_number
457 ,to_char(NVL(linked_inv.invoice_amount,0),fnd_currency.GET_FORMAT_MASK(linked_inv.proj_currency_code, 20)) invoice_amount
458 ,ap_inv.invoice_date invoice_date
459 ,po_vend.vendor_name supplier_name
460 FROM (
461 SELECT ap_invoice_id Invoice_id
462 ,ei.project_currency_code proj_currency_code
463 ,SUM(ei.raw_cost) Invoice_Amount
464 FROM pa_pwp_linked_invoices pwp
465 ,pa_expenditure_items ei
466 WHERE pwp.ap_invoice_id = ei.document_header_id
467 AND pwp.project_id = ei.project_id
468 AND pwp.project_id = l_project_id
469 AND pwp.draft_invoice_num = l_draft_inv_num
470 GROUP BY pwp.ap_invoice_id
471 ,ei.project_currency_code
472 UNION ALL
473 SELECT DISTINCT ei.document_header_id Invoice_Id
474 ,ei.project_currency_code proj_currency_code
475 ,SUM(ei.raw_cost) Invoice_Amount
476 FROM pa_draft_invoices pdi
477 ,pa_draft_invoice_items pdii
478 ,pa_cust_rev_dist_lines crdl
479 ,pa_expenditure_items ei
480 WHERE pdi.project_id = pdii.project_id
481 AND pdi.draft_invoice_num = pdii.draft_invoice_num
482 AND pdii.project_id = crdl.project_id
483 AND pdii.draft_invoice_num = crdl.draft_invoice_num
484 AND pdii.line_num = crdl.draft_invoice_item_line_num
485 AND crdl.expenditure_item_id = ei.expenditure_item_id
486 AND ei.document_header_id IS NOT NULL
487 AND ei.system_linkage_function = 'VI'
488 AND pdi.project_id = l_project_id
489 AND pdi.draft_invoice_num = l_draft_inv_num
490 GROUP BY ei.document_header_id
491 ,ei.project_currency_code
492 ) linked_inv
493 ,ap_invoices ap_inv
494 ,po_vendors po_vend
495 WHERE linked_inv.invoice_id = ap_inv.invoice_id
496 AND ap_inv.vendor_id = po_vend.vendor_id
497 AND EXISTS (Select 1
498 from ap_holds h
499 where h.invoice_id = ap_inv.invoice_id
500 and release_reason is not null
501 and hold_lookup_code in ('PO Deliverable', 'Pay When Paid')
502 );
503
504 CURSOR c_orgz_info ( p_carrying_out_organization_id NUMBER )
505 IS
506 SELECT name Organization_Name
507 FROM hr_organization_units
508 WHERE organization_id = p_carrying_out_organization_id;
509
510 l_orgz_info_rec c_orgz_info%ROWTYPE;
511 l_proj_manager_rec c_proj_manager%ROWTYPE;
512 l_manager_rec c_manager%ROWTYPE;
513 l_linked_inv_rec c_linked_invoice%ROWTYPE;
514
515 l_clob clob;
516 l_text VARCHAR2(32767);
517 l_index NUMBER;
518 x_return_status VARCHAR2(1);
519 x_msg_count NUMBER;
520 x_msg_data VARCHAR2(250);
521 l_err_code NUMBER:= 0;
522 l_err_stack VARCHAR2(630);
523 l_err_stage VARCHAR2(80);
524 l_page_content_id Number:=0;
525
526 PRAGMA AUTONOMOUS_TRANSACTION;
527
528 BEGIN
529
530 log_message('Generate_PWP_Notify_Page: START (AUTONOMOUS_TRANSACTION)', 3);
531
532 OPEN c_orgz_info( p_proj_info_rec.Organization_Id );
533 FETCH c_orgz_info INTO l_orgz_info_rec;
534 IF c_orgz_info%ISOPEN THEN
535 CLOSE c_orgz_info;
536 END IF;
537
538 OPEN c_proj_manager(p_proj_info_rec.project_id);
539 FETCH c_proj_manager INTO l_proj_manager_rec;
540
541
542 IF (c_proj_manager%FOUND) THEN
543 OPEN c_manager( l_proj_manager_rec.manager_employee_id );
544 FETCH c_manager INTO l_manager_rec;
545 IF c_manager%ISOPEN THEN
546 CLOSE c_manager;
547 END IF;
548 END IF;
549
550 IF c_proj_manager%ISOPEN THEN
551 CLOSE c_proj_manager;
552 END IF;
553
554 x_content_id := 0;
555
556 log_message('Generate_PWP_Notify_Page: Before Calling CREATE_PAGE_CONTENTS: p_inv_info_rec.ra_id=' || p_inv_info_rec.ra_id || '*', 3);
557 PA_PAGE_CONTENTS_PUB.CREATE_PAGE_CONTENTS(p_init_msg_list => fnd_api.g_false
558 ,p_validate_only => fnd_api.g_false
559 ,p_object_type => 'PA_PWP_AR_NOTIFY'
560 ,p_pk1_value => p_inv_info_rec.ra_id
561 ,p_pk2_value => NULL
562 ,x_page_content_id => l_page_content_id
563 ,x_return_status => x_return_status
564 ,x_msg_count => x_msg_count
565 ,x_msg_data => x_msg_data
566 );
567 log_message('Generate_PWP_Notify_Page: After Calling CREATE_PAGE_CONTENTS: x_return_status=' || x_return_status || '*', 3);
568
569 x_content_id := l_page_content_id;
570
571 BEGIN
572 --create notification page
573 SELECT page_content
574 INTO l_clob
575 FROM pa_page_contents
576 WHERE page_content_id = l_page_content_id FOR UPDATE NOWAIT;
577 EXCEPTION
578 WHEN NO_DATA_FOUND THEN
579 log_message('Generate_PWP_Notify_Page: Failed to Select CLOB with: l_page_content_id=' || l_page_content_id || '*', 5);
580 RAISE;
581 END;
582
583 l_text := '';
584
585 --Starting the page content
586 l_text := '<table width="100%" border="0" cellspacing="0" cellpadding="0"><tr><td>';
587 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
588
589 -- START : Project Information Section
590 l_text := '<table cellpadding="0" cellspacing="0" border= "0" width="100%"><tr><td><img src="/OA_HTML/cabo/images/swan/t.gif" /></td></tr>';
591 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
592
593 --Heading
594 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);">';
595 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 ';
596 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>';
597 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
598
599 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">';
600 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">';
601 l_text := l_text || '<table border="0" cellspacing="0" cellpadding="0">';
602 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
603
604 --Project name
605 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>';
606 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" ';
607 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>';
608 l_text := l_text || '</tr><tr><td height="3"></td><td></td><td></td></tr>';
609 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
610
611 --Project Number
612 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>';
613 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" ';
614 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" />';
615 l_text := l_text || '</font></td></tr><tr><td height="3"></td><td></td><td></td></tr>';
616 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
617
618 --Organization
619 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Organization</font>';
620 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" ';
621 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" />';
622 l_text := l_text || '</font></td></tr><tr><td height="3"></td><td></td><td></td></tr>';
623 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
624
625 --project type
626 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>';
627 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" ';
628 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>';
629 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
630
631 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">';
632 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
633
634 --Project Manager
635 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Project Manager';
636 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" ';
637 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">';
638 l_text := l_text || '</td><td></td><td></td></tr>';
639 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
640
641 --project start date
642 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>';
643 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" ';
644 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" />';
645 l_text := l_text || '</font></td></tr><tr><td height="3"></td><td></td><td></td></tr>';
646 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
647
648 --Project finish date
649 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>';
650 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" ';
651 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>';
652 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
653
654 -- project status
655 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>';
656 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" ';
657 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>';
658 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
659
660 --l_text := '</table></td></tr></table></td></tr></table></td></tr></table>';
661 l_text := '</table></td></tr></table></td></tr></table></td></tr><tr><td height="8" bgcolor="#EAEFF5"></td></tr></table>';
662 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
663
664 -- END : Project Information Section
665
666 --START : Receipt and Invoice Information Section
667 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>';
668 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
669
670 --Heading
671 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>';
672 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">';
673 l_text := l_text || '<font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#3C3C3C" size="2"><b>Receipt and AR Invoice Details</b></font></h2></td></tr></table></td></tr>';
674 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
675
676 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">';
677 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">';
678 l_text := l_text || '<table border="0" cellspacing="0" cellpadding="0">';
679 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
680
681 --Receipt num
682 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Receipt Number</font></td>';
683 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" ';
684 l_text := l_text || 'size="2"><b>' || p_inv_info_rec.receipt_number || '</b><img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="15" /></font></td></tr><tr><td height="3">';
685 l_text := l_text || '</td><td></td><td></td></tr>';
686 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
687
688 --Receipt date
689 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Receipt Date</font>';
690 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" ';
691 l_text := l_text || 'size="2"><b>' || p_inv_info_rec.receipt_date || '</b><img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="15" /></font></td></tr><tr><td height="3">';
692 l_text := l_text || '</td><td></td><td></td></tr>';
693 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
694
695 --Receipt amt
696 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Receipt Amount</font>';
697 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" ';
698 l_text := l_text || 'color="#000000" size="2"><b>' || p_inv_info_rec.receipt_amount || '</b><img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="15" />';
699 l_text := l_text || '</font></td></tr><tr><td height="3"></td><td></td><td></td></tr>';
700 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
701
702 --Applied amt
703 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Applied Amount</font>';
704 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" ';
705 l_text := l_text || 'color="#000000" size="2"><b>' || p_inv_info_rec.amount_applied || '</b></font></td></tr><tr><td height="3"></td><td></td><td></td></tr>';
706 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
707
708 -- Receipt Currency
709 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Receipt Currency</font>';
710 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" ';
711 l_text := l_text || 'size="2"><b>' || p_inv_info_rec.receipt_currency_code || '</b><img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="15" /></font></td></tr>';
712 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
713
714
715 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">';
716 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
717
718 -- Inv num
719 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">AR Invoice Number</font>';
720 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" ';
721 l_text := l_text || 'size="2"><b>' || p_inv_info_rec.ar_invoice_no || '</b><img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="15" /></font></td></tr>';
722 l_text := l_text || '<tr><td height="3"></td><td></td><td></td></tr>';
723 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
724
725 --Inv Date
726 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Invoice Date</font></td>';
727 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" ';
728 l_text := l_text || 'size="2"><b>' || p_inv_info_rec.ar_invoice_date || '</b><img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="15" /></font></td></tr><tr>';
729 l_text := l_text || '<td height="3"></td><td></td><td></td></tr>';
730 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
731
732 --Inv amt
733 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Invoice Amount</font>';
734 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" ';
735 l_text := l_text || 'color="#000000" size="2"><b>' || p_inv_info_rec.ar_invoice_amount || '</b><img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="15" />';
736 l_text := l_text || '</font></td></tr><tr><td height="3"></td><td></td><td></td></tr>';
737 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
738
739 --Inv Currency
740 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">AR Invoice Currency</font>';
741 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" ';
742 l_text := l_text || 'color="#000000" size="2"><b>' || p_inv_info_rec.ar_invoice_currency_code || '</b><img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="15" /></font></td></tr>';
743 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
744
745 --This cell is Empty
746 l_text := '<tr><td height="3"></td><td></td><td></td></tr><tr><td height="3"></td><td></td><td></td></tr>';
747 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
748
749 l_text := '</table></td></tr></table></td></tr></table></td></tr><tr><td height="8" bgcolor="#EAEFF5"></td></tr></table>';
750 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
751 --END : Receipt and Invoice Information Section
752
753 log_message('Generate_PWP_Notify_Page: Done Generating Project and Receipt Section: l_page_content_id=' || l_page_content_id || '*', 3);
754
755 OPEN c_linked_invoice( p_proj_info_rec.project_id, p_inv_info_rec.draft_invoice_number );
756 FETCH c_linked_invoice INTO l_linked_inv_rec;
757 --Check if there are linked SupplierInvoices
758 IF c_linked_invoice%FOUND THEN
759 -- Check if Invoice is FULLY PAID
760 IF p_inv_info_rec.invoice_status = 'OP' THEN
761 --START : Invoices on HOLD Section
762 l_text := '<table cellpadding="0" cellspacing="0" border= "0" width="100%"><tr><td><table cellpadding="0" cellspacing="0" border= "0" width="100%"><tr>';
763 l_text := l_text || '<td height="10"><img src="/OA_HTML/cabo/images/swan/t.gif" /></td></tr><tr><td>';
764 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
765
766 --heading
767 l_text := '<table cellpadding="0" cellspacing="0" border="0" width="100%" style="background-image:url(/OA_HTML/cabo/images/swan/headingBarBg.gif);"><tr>';
768 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 ';
769 l_text := l_text || 'solid #aabed5"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#3C3C3C" size="2"><b>Supplier Invoices on Payment Hold</b></font></h2></td></tr></table>';
770 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
771
772 l_text := '</td></tr><tr><td><div><table cellpadding="0" cellspacing="0" border="0" width="100%"><tr><td height="8" bgcolor="#EAEFF5"></td></tr><tr><td>';
773 l_text := l_text || '<table cellpadding="0" cellspacing="0" border="0" width="100%"><tr><td bgcolor="#EAEFF5"><table border="0" cellspacing="0" cellpadding="0"><tr><td width="5%">';
774 l_text := l_text || '</td><td valign="top"><table border="0" cellspacing="0" cellpadding="0">';
775 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
776
777 -- Text
778 l_text := '<tr><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">';
779 l_text := l_text || 'The following Supplier Invoices are linked to the AR Invoices given above. These Supplier Invoices are on Payment Hold due to partial payment of AR Invoice.';
780 l_text := l_text || '</font></td></tr>';
781 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
782
783 l_text := '<tr><td height="8"><img src="/OA_HTML/cabo/images/swan/t.gif" /></td></tr><tr><td height="3"></td><td></td><td></td></tr><tr>';
784 l_text := l_text || '<td align="center" valign="top" width="100%"><table cellpadding="0" cellspacing="0" border="0" width="75%"><tr><td><table cellpadding="1" cellspacing="0" ';
785 l_text := l_text || 'border="0" width="100%" style="BORDER-COLLAPSE: collapse">';
786 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
787
788 --Header Row of table
789 l_text := '<tr><th style="BORDER-LEFT: #f2f2f5 1px solid" valign="bottom" align="left" bgcolor="#CFE0F1" scope="col">';
790 l_text := l_text || '<font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#3C3C3C" size="2"><b><span bgcolor="#CFE0F1">Supplier Name</span></b></font></th>';
791 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
792
793 l_text := '<th style="BORDER-LEFT: #f2f2f5 1px solid" valign="bottom" align="left" bgcolor="#CFE0F1" scope="col">';
794 l_text := l_text || '<font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#3C3C3C" size="2"><b><span bgcolor="#CFE0F1">Invoice Number</span></b></font></th>';
795 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
796
797 l_text := '<th style="BORDER-LEFT: #f2f2f5 1px solid" valign="bottom" align="left" bgcolor="#CFE0F1" scope="col">';
798 l_text := l_text || '<font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#3C3C3C" size="2"><b><span bgcolor="#CFE0F1">Invoice Date</span></b></font></th>';
799 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
800
801 l_text := '<th style="BORDER-LEFT: #f2f2f5 1px solid" valign="bottom" align="right" bgcolor="#CFE0F1" scope="col">';
802 l_text := l_text || '<font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#3C3C3C" size="2"><b><span bgcolor="#CFE0F1">Invoice Amount</span></b></font></th></tr>';
803 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
804
805 WHILE c_linked_invoice%FOUND
806 LOOP
807 --Supplier Name
808 l_text := '<tr><td style="BORDER-RIGHT: #cfe0f1 1px solid; BORDER-TOP: #cfe0f1 1px solid; BORDER-LEFT: #cfe0f1 1px solid; BORDER-BOTTOM: #cfe0f1 1px solid" ';
809 l_text := l_text || 'valign="baseline" nowrap="nowrap" bgcolor="#F2F2F5"><font class="OraTableCellText" face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">';
810 l_text := l_text || '<span>' || l_linked_inv_rec.supplier_name || '</span></font></td>';
811 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
812
813 --Invoice Number
814 l_text := '<td style="BORDER-RIGHT: #cfe0f1 1px solid; BORDER-TOP: #cfe0f1 1px solid; BORDER-LEFT: #cfe0f1 1px solid; BORDER-BOTTOM: #cfe0f1 1px solid" ';
815 l_text := l_text || 'valign="baseline" nowrap="nowrap" bgcolor="#F2F2F5"><font class="OraTableCellText" face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">';
816 l_text := l_text || '<span>' || l_linked_inv_rec.invoice_number || '</span></font></td>';
817 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
818
819 -- Invoice Date
820 l_text := '<td style="BORDER-RIGHT: #cfe0f1 1px solid; BORDER-TOP: #cfe0f1 1px solid; BORDER-LEFT: #cfe0f1 1px solid; BORDER-BOTTOM: #cfe0f1 1px solid" ';
821 l_text := l_text || 'valign="baseline" nowrap="nowrap" bgcolor="#F2F2F5"><font class="OraTableCellText" face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">';
822 l_text := l_text || '<span>' || l_linked_inv_rec.invoice_date || '</span></font></td>';
823 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
824
825 --Invoice Amount
826 l_text := '<td style="BORDER-RIGHT: #cfe0f1 1px solid; BORDER-TOP: #cfe0f1 1px solid; BORDER-LEFT: #cfe0f1 1px solid; BORDER-BOTTOM: #cfe0f1 1px solid" ';
827 l_text := l_text || 'valign="baseline" align="right" nowrap="nowrap" bgcolor="#F2F2F5"><font class="OraTableCellText" face="Tahoma,Arial,Helvetica,Geneva,sans-serif" ';
828 l_text := l_text || 'color="#000000" size="2"><span>' || l_linked_inv_rec.invoice_amount || '</span></font></td></tr>';
829 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
830
831 FETCH c_linked_invoice INTO l_linked_inv_rec;
832 END LOOP;
833
834 l_text := '</table></td></tr></table></td></tr><tr><td height="8"><img src="/OA_HTML/cabo/images/swan/t.gif" /></td></tr></table></td></tr></table></td></tr>';
835 l_text := l_text || '</table></td></tr></table></td></tr></table></td></tr><tr><td height="8" bgcolor="#EAEFF5"></td></tr></table>';
836 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
837 --END : Invoices on HOLD Section
838
839 ELSE
840
841 --START : Invoices on ready for release Section
842 l_text := '<table cellpadding="0" cellspacing="0" border= "0" width="100%"><tr><td><table cellpadding="0" cellspacing="0" border= "0" width="100%"><tr>';
843 l_text := l_text || '<td height="10"><img src="/OA_HTML/cabo/images/swan/t.gif" /></td></tr><tr><td>';
844 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
845
846 --heading
847 l_text := '<table cellpadding="0" cellspacing="0" border="0" width="100%" style="background-image:url(/OA_HTML/cabo/images/swan/headingBarBg.gif);"><tr>';
848 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 ';
849 l_text := l_text || 'solid #aabed5"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#3C3C3C" size="2"><b>Supplier Invoices Available for Payment Release</b></font></h2></td></tr></table>';
850 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
851
852 l_text := '</td></tr><tr><td><div><table cellpadding="0" cellspacing="0" border="0" width="100%"><tr><td height="8" bgcolor="#EAEFF5"></td></tr><tr><td>';
853 l_text := l_text || '<table cellpadding="0" cellspacing="0" border="0" width="100%"><tr><td bgcolor="#EAEFF5"><table border="0" cellspacing="0" cellpadding="0"><tr><td width="5%">';
854 l_text := l_text || '</td><td valign="top"><table border="0" cellspacing="0" cellpadding="0">';
855 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
856
857 -- Text
858 l_text := '<tr><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">';
859 l_text := l_text || 'The following Supplier Invoices are linked to the AR Invoices given above. These Supplier Invoices can be released for Processing of Payment.';
860 l_text := l_text || '</font></td></tr>';
861 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
862
863 l_text := '<tr><td height="8"><img src="/OA_HTML/cabo/images/swan/t.gif" /></td></tr><tr><td height="3"></td><td></td><td></td></tr><tr>';
864 l_text := l_text || '<td align="center" valign="top" width="100%"><table cellpadding="0" cellspacing="0" border="0" width="75%"><tr><td>';
865 l_text := l_text || '<table cellpadding="1" cellspacing="0" border="0" width="100%" style="BORDER-COLLAPSE: collapse">';
866 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
867
868 --Header Row of table
869 l_text := '<tr><th style="BORDER-LEFT: #f2f2f5 1px solid" valign="bottom" align="left" bgcolor="#CFE0F1" scope="col">';
870 l_text := l_text || '<font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#3C3C3C" size="2"><b><span bgcolor="#CFE0F1">Supplier Name</span></b></font></th>';
871 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
872
873 l_text := '<th style="BORDER-LEFT: #f2f2f5 1px solid" valign="bottom" align="left" bgcolor="#CFE0F1" scope="col">';
874 l_text := l_text || '<font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#3C3C3C" size="2"><b><span bgcolor="#CFE0F1">Invoice Number</span></b></font></th>';
875 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
876
877 l_text := '<th style="BORDER-LEFT: #f2f2f5 1px solid" valign="bottom" align="left" bgcolor="#CFE0F1" scope="col">';
878 l_text := l_text || '<font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#3C3C3C" size="2"><b><span bgcolor="#CFE0F1">Invoice Date</span></b></font></th>';
879 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
880
881 l_text := '<th style="BORDER-LEFT: #f2f2f5 1px solid" valign="bottom" align="right" bgcolor="#CFE0F1" scope="col">';
882 l_text := l_text || '<font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#3C3C3C" size="2"><b><span bgcolor="#CFE0F1">Invoice Amount</span></b></font></th></tr>';
883 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
884
885 WHILE c_linked_invoice%FOUND
886 LOOP
887
888 --Supplier Name
889 l_text := '<tr><td style="BORDER-RIGHT: #cfe0f1 1px solid; BORDER-TOP: #cfe0f1 1px solid; BORDER-LEFT: #cfe0f1 1px solid; BORDER-BOTTOM: #cfe0f1 1px solid" ';
890 l_text := l_text || 'valign="baseline" nowrap="nowrap" bgcolor="#F2F2F5"><font class="OraTableCellText" face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">';
891 l_text := l_text || '<span>' || l_linked_inv_rec.supplier_name || '</span></font></td>';
892 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
893
894 --Invoice Number
895 l_text := '<td style="BORDER-RIGHT: #cfe0f1 1px solid; BORDER-TOP: #cfe0f1 1px solid; BORDER-LEFT: #cfe0f1 1px solid; BORDER-BOTTOM: #cfe0f1 1px solid" ';
896 l_text := l_text || 'valign="baseline" nowrap="nowrap" bgcolor="#F2F2F5"><font class="OraTableCellText" face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">';
897 l_text := l_text || '<span>' || l_linked_inv_rec.invoice_number || '</span></font></td>';
898 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
899
900 -- Invoice Date
901 l_text := '<td style="BORDER-RIGHT: #cfe0f1 1px solid; BORDER-TOP: #cfe0f1 1px solid; BORDER-LEFT: #cfe0f1 1px solid; BORDER-BOTTOM: #cfe0f1 1px solid" ';
902 l_text := l_text || 'valign="baseline" nowrap="nowrap" bgcolor="#F2F2F5"><font class="OraTableCellText" face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">';
903 l_text := l_text || '<span>' || l_linked_inv_rec.invoice_date || '</span></font></td>';
904 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
905
906 --Invoice Amount
907 l_text := '<td style="BORDER-RIGHT: #cfe0f1 1px solid; BORDER-TOP: #cfe0f1 1px solid; BORDER-LEFT: #cfe0f1 1px solid; BORDER-BOTTOM: #cfe0f1 1px solid" ';
908 l_text := l_text || 'valign="baseline" align="right" nowrap="nowrap" bgcolor="#F2F2F5"><font class="OraTableCellText" face="Tahoma,Arial,Helvetica,Geneva,sans-serif" ';
909 l_text := l_text || 'color="#000000" size="2"><span>' || l_linked_inv_rec.invoice_amount || '</span></font></td></tr>';
910 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
911
912 FETCH c_linked_invoice INTO l_linked_inv_rec;
913 END LOOP;
914
915 l_text := '</table></td></tr></table></td></tr><tr><td height="8"><img src="/OA_HTML/cabo/images/swan/t.gif" /></td></tr></table></td></tr></table>';
916 l_text := l_text || '</td></tr></table></td></tr><tr><td height="8" bgcolor="#EAEFF5"></td></tr></table></td></tr></table></td></tr></table>';
917 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
918 --END : Invoices on ready for release Section
919
920 END IF; -- Invoice Status
921 END IF; -- Linked Invoices
922
923 --START : References Section
924 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>';
925 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
926
927 --Header
928 l_text := '<tr><td><table cellpadding="0" cellspacing="0" border="0" width="100%" style="background-image:url(/OA_HTML/cabo/images/swan/headingBarBg.gif);">';
929 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 ';
930 l_text := l_text || 'solid #aabed5"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#3C3C3C" size="2"><b>References</b></font></h2></td></tr></table></td></tr>';
931 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
932
933 --URL Section
934 l_text := '<tr><td height="8" bgcolor="#EAEFF5"></td></tr><tr><td> <div><div><table cellpadding="0" cellspacing="0" border="0" width="100%"><tr>';
935 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>';
936 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">';
937 l_text := l_text || '<img src="/OA_MEDIA/fwkhp_formsfunc.gif" alt="Open Supplier Summary" width="16" height="16" border="0"></span></td><td width="12">';
938 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">';
939 l_text := l_text || '<a href="OA.jsp?page=/oracle/apps/pa/subcontractor/webui/SubContractSummPG&_ri=275&addBreadCrumb=RS&paProjectId=' || p_proj_info_rec.project_id || '">Open Supplier Summary </a>';
940 l_text := l_text || '<img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="5" /></font></td></tr><tr>';
941 l_text := l_text || '<td height="3"></td><td></td><td></td></tr></table></tr></table></td></tr></table></td></tr>';
942 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
943
944 l_text := '<tr><td height="8" bgcolor="#EAEFF5"></td></tr></table>';
945 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
946 --END : References Section
947
948 --Closing the page content
949 l_text := '</td></tr></table>';
950 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
951
952 log_message('Generate_PWP_Notify_Page: Page generation Done...', 3);
953
954 COMMIT;
955 l_text := '';
956
957 log_message('Generate_PWP_Notify_Page: Normal Exit', 3);
958 EXCEPTION
959 WHEN OTHERS THEN
960 log_message('Generate_PWP_Notify_Page: Exit With Error', 5);
961 RAISE;
962 END Generate_PWP_Notify_Page;
963
964
965
966 -------------------------------------------------------------------------------
967 -- Procedure : Select_Project_Manager -
968 -- Purpose : Select Project Manger, will be called from Wf. -
969 -- Note : Select Project Manger, will be called from Wf. -
970 -- Assumptions : None -
971 -- Parameters Type Required Description and Purpose-
972 -- --------------------------- ------ -------- ------------------------
973 -- p_item_type VARCHAR2 YES Itemtype for workflow -
974 -- p_item_Key VARCHAR2 YES ItemKey for workflow -
975 -- actid NUMBER YES Activity Id of workflow-
976 -- funcmode VARCHAR2 YES func call Mode of WF -
977 -- resultout VARCHAR2 YES Execution result for WF-
978 -------------------------------------------------------------------------------
979
980 PROCEDURE Select_Project_Manager (itemtype IN VARCHAR2
981 ,itemkey IN VARCHAR2
982 ,actid IN NUMBER
983 ,funcmode IN VARCHAR2
984 ,resultout OUT NOCOPY VARCHAR2)
985 IS
986
987 l_err_code NUMBER := 0;
988 l_resp_id NUMBER;
989 l_project_id NUMBER;
990 l_workflow_started_by_id NUMBER;
991 l_manager_employee_id NUMBER;
992 l_manager_user_id NUMBER;
993 l_manager_user_name VARCHAR2(240);
994 l_manager_full_name VARCHAR2(400);
995 l_return_status NUMBER := 0;
996 l_project_manager_id NUMBER := 0;
997
998 BEGIN
999 --
1000 -- Return if WF Not Running
1001 --
1002 IF (funcmode <> wf_engine.eng_run) THEN
1003 resultout := wf_engine.eng_null;
1004 RETURN;
1005 END IF;
1006
1007 l_resp_id := wf_engine.GetItemAttrNumber(itemtype => itemtype
1008 ,Itemkey => itemkey
1009 ,aname => 'RESPONSIBILITY_ID' );
1010
1011 l_project_id := wf_engine.GetItemAttrNumber(itemtype => itemtype
1012 ,itemkey => itemkey
1013 ,aname => 'PROJECT_ID' );
1014
1015 l_workflow_started_by_id := wf_engine.GetItemAttrNumber(itemtype => itemtype
1016 ,itemkey => itemkey
1017 ,aname => 'WORKFLOW_STARTED_BY_ID' );
1018
1019 -- Based on the Responsibility, Intialize the Application
1020 PA_WORKFLOW_UTILS.Set_Global_Attr (p_item_type => itemtype
1021 ,p_item_key => itemkey
1022 ,p_err_code => l_err_code);
1023
1024
1025 PA_CE_AR_NOTIFY_WF.Select_Project_Manager (p_project_id => l_project_id
1026 ,p_project_manager_id => l_manager_employee_id
1027 ,p_return_status => l_return_status);
1028
1029 IF ( l_return_status = 0 ) THEN
1030 OPEN c_proj_manager(l_project_id);
1031 FETCH c_proj_manager INTO l_manager_employee_id;
1032 IF c_proj_manager%ISOPEN THEN
1033 CLOSE c_proj_manager;
1034 END IF;
1035 END IF;
1036
1037
1038 IF (l_manager_employee_id IS NOT NULL ) THEN
1039
1040 OPEN c_manager( l_manager_employee_id );
1041 FETCH c_manager INTO l_manager_user_id
1042 ,l_manager_user_name
1043 ,l_manager_full_name;
1044
1045 IF (c_manager%FOUND) THEN
1046 IF c_manager%ISOPEN THEN
1047 CLOSE c_manager;
1048 END IF;
1049 wf_engine.SetItemAttrNumber (itemtype => itemtype
1050 ,itemkey => itemkey
1051 ,aname => 'PROJECT_MANAGER_ID'
1052 ,avalue => l_manager_user_id );
1053 wf_engine.SetItemAttrText (itemtype => itemtype
1054 ,itemkey => itemkey
1055 ,aname => 'PROJECT_MANAGER_NAME'
1056 ,avalue => l_manager_user_name);
1057 wf_engine.SetItemAttrText (itemtype => itemtype
1058 ,itemkey => itemkey
1059 ,aname => 'PROJECT_MANAGER_FULL_NAME'
1060 ,avalue => l_manager_full_name);
1061
1062 resultout := wf_engine.eng_completed||':'||'T';
1063 ELSE
1064 IF c_manager%ISOPEN THEN
1065 CLOSE c_manager;
1066 END IF;
1067 resultout := wf_engine.eng_completed||':'||'F';
1068 END IF;
1069 ELSE
1070 resultout := wf_engine.eng_completed||':'||'F';
1071 END IF;
1072
1073 EXCEPTION
1074 WHEN FND_API.G_EXC_ERROR THEN
1075 WF_CORE.CONTEXT('PA_PWP_NOTIFICATION','SELECT_PROJECT_MANAGER',itemtype, itemkey, to_char(actid), funcmode);
1076 RAISE;
1077
1078 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1079 WF_CORE.CONTEXT('PA_PWP_NOTIFICATION','SELECT_PROJECT_MANAGER',itemtype, itemkey, to_char(actid), funcmode);
1080 RAISE;
1081
1082 WHEN OTHERS THEN
1083 WF_CORE.CONTEXT('PA_PWP_NOTIFICATION','SELECT_PROJECT_MANAGER',itemtype, itemkey, to_char(actid), funcmode);
1084 RAISE;
1085 END Select_Project_Manager;
1086
1087
1088 -------------------------------------------------------------------------------
1089 -- Procedure : SHOW_PWP_NOTIFY_PREVIEW -
1090 -- Purpose : Fetch the mail document, will be called from Wf. -
1091 -- Note : Fetch the mail document, will be called from Wf. -
1092 -- Assumptions : None -
1093 -- Parameters Type Required Description and Purpose-
1094 -- --------------------------- ------ -------- ------------------------
1095 -- document_id VARCHAR2 YES Document Id -
1096 -- display_type VARCHAR2 YES Display type text/html -
1097 -- document CLOB YES Document Content -
1098 -- document_type VARCHAR2 YES Document type text/html-
1099 -------------------------------------------------------------------------------
1100
1101 PROCEDURE SHOW_PWP_NOTIFY_PREVIEW(document_id IN VARCHAR2
1102 ,display_type IN VARCHAR2
1103 ,document IN OUT NOCOPY CLOB
1104 ,document_type IN OUT NOCOPY VARCHAR2)
1105 IS
1106
1107 l_content CLOB;
1108
1109 CURSOR c_pwp_preview_info IS
1110 SELECT page_content
1111 FROM PA_PAGE_CONTENTS
1112 WHERE page_content_id = document_id
1113 AND object_type = 'PA_PWP_AR_NOTIFY'
1114 AND pk2_value IS NULL;
1115
1116 l_size number;
1117 l_chunk_size PLS_INTEGER:=10000;
1118 l_copy_size INT;
1119 l_pos INT := 0;
1120 l_line VARCHAR2(30000) := '';
1121 l_return_status VARCHAR2(1);
1122 l_msg_count NUMBER;
1123 l_msg_data VARCHAR2(2000);
1124
1125
1126 BEGIN
1127
1128 OPEN c_pwp_preview_info;
1129 FETCH c_pwp_preview_info INTO l_content;
1130 IF (c_pwp_preview_info%FOUND) THEN
1131 IF c_pwp_preview_info%ISOPEN THEN
1132 CLOSE c_pwp_preview_info;
1133 END IF;
1134 l_size := dbms_lob.getlength(l_content);
1135 l_pos := 1;
1136 l_copy_size := 0;
1137 WHILE (l_copy_size < l_size) LOOP
1138 dbms_lob.READ(l_content,l_chunk_size,l_pos,l_line);
1139 dbms_lob.WRITE(document,l_chunk_size,l_pos,l_line);
1140 l_copy_size := l_copy_size + l_chunk_size;
1141 l_pos := l_pos + l_chunk_size;
1142 END LOOP;
1143
1144 pa_workflow_utils.modify_wf_clob_content(p_document => document
1145 ,x_return_status => l_return_status
1146 ,x_msg_count => l_msg_count
1147 ,x_msg_data => l_msg_data);
1148
1149 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1150 WF_NOTIFICATION.WriteToClob(document, 'Content Generation failed');
1151 dbms_lob.writeappend(document, 255, SUBSTR(SQLERRM, 255));
1152 END IF;
1153 ELSE
1154 IF c_pwp_preview_info%ISOPEN THEN
1155 CLOSE c_pwp_preview_info;
1156 END IF;
1157 END IF;
1158
1159 document_type := 'text/html';
1160
1161 EXCEPTION
1162 WHEN OTHERS THEN
1163 WF_NOTIFICATION.WriteToClob(document, 'Content Generation failed');
1164 dbms_lob.writeappend(document, 255, substrb(Sqlerrm, 255));
1165 NULL;
1166 END SHOW_PWP_NOTIFY_PREVIEW;
1167
1168
1169
1170 -------------------------------------------------------------------------------
1171 -- Procedure : APPEND_VARCHAR_TO_CLOB -
1172 -- Purpose : Append generated content to CLOB -
1173 -- Note : Append generated content to CLOB -
1174 -- Assumptions : None -
1175 -- Parameters Type Required Description and Purpose-
1176 -- --------------------------- ------ -------- ------------------------
1177 -- p_varchar VARCHAR2 YES Content to append -
1178 -- p_clob CLOB YES CLOB -
1179 -------------------------------------------------------------------------------
1180
1181 PROCEDURE APPEND_VARCHAR_TO_CLOB(p_varchar IN varchar2
1182 ,p_clob IN OUT NOCOPY CLOB)
1183 IS
1184 l_chunkSize INTEGER;
1185 v_offset INTEGER := 0;
1186 l_clob clob;
1187 l_length INTEGER;
1188
1189 v_size NUMBER;
1190 v_text VARCHAR2(3000);
1191 BEGIN
1192 l_chunksize := length(p_varchar);
1193 l_length := dbms_lob.getlength(p_clob);
1194
1195 dbms_lob.write(p_clob
1196 ,l_chunksize
1197 ,l_length+1
1198 ,p_varchar);
1199 v_size := 1000;
1200 dbms_lob.read(p_clob, v_size, 1, v_text);
1201 END APPEND_VARCHAR_TO_CLOB;
1202 ------------------------------
1203
1204 END PA_PWP_NOTIFICATION;