[Home] [Help]
PACKAGE BODY: APPS.POR_CANCEL_NOTIF_PVT
Source
1 PACKAGE BODY por_cancel_notif_pvt AS
2 /* $Header: PORCNNTB.pls 115.3 2004/05/08 00:23:58 mahmad noship $ */
3
4 -- Read the profile option that enables/disables the debug log
5 g_po_wf_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_SET_DEBUG_WORKFLOW_ON'),'N');
6 g_pkg_name CONSTANT VARCHAR2(50) := 'POR_CANCEL_NOTIF_PVT';
7 g_module_prefix CONSTANT VARCHAR2(50) := 'por.plsql.' || g_pkg_name || '.';
8
9 /*==========================================================================*
10 * Starts Contractor Requisition Cancellation WF *
11 *==========================================================================*/
12 FUNCTION Start_WF_Process(reqLineId NUMBER, contractorStatus VARCHAR2)
13 RETURN VARCHAR2 IS
14
15 l_itemtype wf_items.item_type%TYPE := 'PORCNWF';
16 l_itemkey wf_items.item_key%TYPE;
17 l_wf_created NUMBER := 0;
18 l_wf_process varchar2(100) := 'POR_CONT_CANCEL_WF';
19 l_user_id NUMBER := 0;
20 l_responsibility_id NUMBER := 0;
21 l_application_id NUMBER := 0;
22 l_progress varchar2(200) := '';
23 l_api_name varchar2(50) := 'START_WF_PROCESS';
24 BEGIN
25
26 IF (reqLineId IS NOT NULL) THEN
27
28 -- set item key
29 SELECT to_char(reqLineId) || '-' || to_char(POR_CANCEL_NOTIF_ITEMKEY_S.nextval)
30 INTO l_itemkey
31 FROM dual;
32
33 l_progress := 'POR_CANCEL_NOTIFICATION_PKG.Start_WF_Process: 01';
34
35 IF (g_po_wf_debug = 'Y') THEN
36 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,g_module_prefix || l_api_name || l_progress);
37 END IF;
38
39 -- Check if any process created before with the same wf item type and
40 -- wf item key
41 SELECT count(*)
42 INTO l_wf_created
43 FROM wf_items
44 WHERE
45 item_type=l_itemtype AND
46 item_key = l_itemkey;
47
48 IF (l_wf_created = 0) THEN
49
50 l_progress := 'POR_CANCEL_NOTIFICATION_PKG.Start_WF_Process: 02';
51 IF (g_po_wf_debug = 'Y') THEN
52 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,g_module_prefix || l_api_name || l_progress);
53 END IF;
54 wf_engine.CreateProcess( ItemType => l_itemtype,
55 ItemKey => l_itemkey,
56 process => l_wf_process );
57
58 wf_engine.SetItemAttrNumber ( itemtype => l_itemtype,
59 itemkey => l_itemkey,
60 aname => 'REQ_LINE_ID',
61 avalue => reqLineId);
62
63 wf_engine.SetItemAttrText (itemtype => l_itemtype,
64 itemkey => l_itemkey,
65 aname => 'CONTRACTOR_STATUS',
66 avalue => contractorStatus);
67
68 wf_engine.StartProcess(ItemType => l_itemtype,
69 ItemKey => l_itemkey);
70
71 RETURN 'Y';
72
73 ELSE
74
75 RETURN 'N';
76
77 END IF;
78
79 END IF;
80
81 END Start_WF_Process;
82
83
84 /*==========================================================================*
85 * Checks whether suppliers of the corresponding requisition *
86 * has been notified before by Supplier Notification *
87 *==========================================================================*/
88 PROCEDURE Is_any_supplier_notified(itemtype in varchar2,
89 itemkey in varchar2,
90 actid in number,
91 funcmode in varchar2,
92 resultout out NOCOPY varchar2)
93 IS
94
95 l_progress varchar2(200);
96 l_supplier_notified_flag po_requisition_headers.supplier_notified_flag%type;
97 l_req_line_id NUMBER;
98 l_contractor_status po_requisition_lines.contractor_status%type;
99 l_api_name varchar2(50) := 'IS_ANY_SUPPLIER_NOTIFIED';
100 BEGIN
101
102 l_progress := 'POR_CANCEL_NOTIFICATION_PVT.Is_any_supplier_notified: 01';
103 IF (g_po_wf_debug = 'Y') THEN
104 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,g_module_prefix || l_api_name || l_progress);
105 END IF;
106
107 -- Do nothing in cancel or timeout mode
108 --
109 if (funcmode <> wf_engine.eng_run) then
110 resultout := wf_engine.eng_null;
111 return;
112 end if;
113
114 l_req_line_id := wf_engine.GetItemAttrNumber
115 (itemtype => itemtype,
116 itemkey => itemkey,
117 aname => 'REQ_LINE_ID');
118
119 l_progress := 'POR_CANCEL_NOTIFICATION_PVT.Is_any_supplier_notified: 02';
120
121 select nvl(prh.supplier_notified_flag, 'N'), prl.contractor_status
122 into l_supplier_notified_flag, l_contractor_status
123 from
124 po_requisition_headers_all prh,
125 po_requisition_lines_all prl
126 where
127 prh.requisition_header_id = prl.requisition_header_id and
128 requisition_line_id = l_req_line_id;
129
130 l_progress := 'POR_CANCEL_NOTIFICATION_PVT.Is_any_supplier_notified: 03';
131
132 -- if (l_supplier_notified_flag = 'Y' and
133 -- (l_contractor_status = 'ASSIGNED' or l_contractor_status = 'PENDING')) then
134 if (l_supplier_notified_flag = 'Y') then
135 resultout := wf_engine.eng_completed || ':' || 'Y' ;
136 else
137 resultout := wf_engine.eng_completed || ':' || 'N' ;
138 end if;
139
140 EXCEPTION
141 WHEN OTHERS THEN
142 wf_core.context('POR_CANCEL_NOTIF_PVT','Is_any_supplier_notified',l_progress);
143 raise;
144
145 END Is_any_supplier_notified;
146
147
148 /*==========================================================================*
149 * Returns the company name *
150 *==========================================================================*/
151 FUNCTION get_company_name return varchar2 IS
152
153 party_name VARCHAR2(100) := '';
154 exception_msg VARCHAR2(100) := '';
155 status VARCHAR2(100) := '';
156
157 BEGIN
158
159 POS_ENTERPRISE_UTIL_PKG.GET_ENTERPRISE_PARTY_NAME(party_name, exception_msg, status);
160
161 IF (status = 'S') THEN
162 RETURN party_name;
163 END IF;
164
165 RETURN '';
166
167 END get_company_name;
168
169 /*==========================================================================*
170 * Returns user name who cancel the requisition line *
171 *==========================================================================*/
172 FUNCTION get_user_name return varchar2 IS
173
174 l_user_name VARCHAR2(100) := '';
175 l_user_id varchar2(100) := '';
176
177 BEGIN
178
179 FND_PROFILE.GET('USER_ID', l_user_id);
180
181 select user_name
182 into l_user_name
183 from fnd_user
184 where user_id = to_number(l_user_id);
185
186 RETURN l_user_name;
187
188 EXCEPTION
189
190 WHEN OTHERS THEN
191 RETURN '';
192
193 END get_user_name;
194
195
196 /*==========================================================================*
197 * Initialize notification message attributes before it is sent *
198 *==========================================================================*/
199 PROCEDURE set_notification_attributes(itemtype in varchar2,
200 itemkey in varchar2,
201 actid in number,
202 funcmode in varchar2,
203 resultout out NOCOPY varchar2)
204 IS
205
206 l_progress varchar2(200);
207 l_req_line_id NUMBER;
208 l_job_name po_job_associations_tl.JOB_DESCRIPTION%TYPE;
209 l_contact_info PO_REQUISITION_LINES.CONTACT_INFORMATION%TYPE;
210 l_start_date DATE;
211 l_req_info VARCHAR2(100) := '';
212 l_cont_status PO_REQUISITION_LINES.CONTRACTOR_STATUS%TYPE;
213 l_api_name varchar2(50) := 'SET_NOTIFICATION_ATTRIBUTES';
214 BEGIN
215
216 l_progress := 'POR_CANCEL_NOTIFICATION_PVT.set_notification_attributes: 01';
217 IF (g_po_wf_debug = 'Y') THEN
218 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,g_module_prefix || l_api_name || l_progress);
219 END IF;
220
221
222 -- Do nothing in cancel or timeout mode
223 --
224 if (funcmode <> wf_engine.eng_run) then
225 resultout := wf_engine.eng_null;
226 return;
227 end if;
228
229 l_req_line_id := wf_engine.GetItemAttrNumber
230 (itemtype => itemtype,
231 itemkey => itemkey,
232 aname => 'REQ_LINE_ID');
233
234 l_cont_status := wf_engine.GetItemAttrText
235 (itemtype => itemtype,
236 itemkey => itemkey,
237 aname => 'CONTRACTOR_STATUS');
238
239 l_progress := 'POR_CANCEL_NOTIFICATION_PVT.set_notification_attributes: 02';
240
241 SELECT pja.job_description, prl.contact_information,
242 prl.assignment_start_date, prh.segment1 || ' / ' || to_char(prl.line_num)
243 INTO l_job_name, l_contact_info, l_start_date, l_req_info
244 FROM
245 po_requisition_headers_all prh,
246 po_requisition_lines_all prl,
247 po_job_associations pja
248 WHERE prl.requisition_line_id = l_req_line_id AND
249 prl.job_id = pja.job_id AND
250 prh.requisition_header_id = prl.requisition_header_id;
251
252 l_progress := 'POR_CANCEL_NOTIFICATION_PVT.set_notification_attributes: 03';
253
254 IF (g_po_wf_debug = 'Y') THEN
255 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,g_module_prefix || l_api_name || l_progress);
256 END IF;
257
258 l_progress := 'POR_CANCEL_NOTIFICATION_PVT.set_notification_attributes: 05';
259
260 -- Set Subject attributes
261 wf_engine.SetItemAttrText (itemtype => itemtype,
262 itemkey => itemkey,
263 aname => 'JOB_NAME',
264 avalue => l_job_name);
265
266 wf_engine.SetItemAttrDate (itemtype => itemtype,
267 itemkey => itemkey,
268 aname => 'START_DATE',
269 avalue => l_start_date);
270
271 -- Set Notification Header attributes
272 wf_engine.SetItemAttrText(itemtype => itemtype,
273 itemkey => itemkey,
274 aname => 'FORWARD_FROM_USER_NAME',
275 avalue => get_user_name());
276
277 wf_engine.SetItemAttrText(itemtype => itemtype,
278 itemkey => itemkey,
279 aname => 'REQ_LINE_INFO',
280 avalue => l_req_info);
281
282 wf_engine.SetItemAttrText(itemtype => itemtype,
283 itemkey => itemkey,
284 aname => 'COMPANY_NAME',
285 avalue => get_company_name());
286
287 -- set attributes for message body
288 wf_engine.SetItemAttrText(itemtype => itemtype,
289 itemkey => itemkey,
290 aname => 'CONTACT_INFO',
291 avalue => l_contact_info);
292
293
294 IF (g_po_wf_debug = 'Y') THEN
295 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,g_module_prefix || l_api_name || l_progress || l_cont_status);
296 END IF;
297
298 IF (l_cont_status = 'PENDING') THEN
299 update po_requisition_suppliers
300 set SUPPLIER_NOTIFIED_FLAG = 'N'
301 where requisition_line_id = l_req_line_id;
302 END IF;
303
304 EXCEPTION
305 WHEN OTHERS THEN
306 wf_core.context('POR_CANCEL_NOTIF_PVT','set_notification_attributes',l_progress);
307 raise;
308
309 END set_notification_attributes;
310
311 /*==========================================================================*
312 * Sets supplier and supplier role *
313 *==========================================================================*/
314 PROCEDURE set_supplier(itemtype in varchar2,
315 itemkey in varchar2,
316 actid in number,
317 funcmode in varchar2,
318 resultout out NOCOPY varchar2)
319 IS
320
321 l_progress varchar2(200);
322 l_api_name varchar2(50) := 'SET_SUPPLIER';
323 l_req_line_id NUMBER := 0;
324 l_cont_status po_requisition_lines.CONTRACTOR_STATUS%type;
325 l_requisition_supplier_id NUMBER := 0;
326 l_performer WF_USER_ROLES.ROLE_NAME%TYPE;
327 l_supplier_exists VARCHAR2(50);
328
329 BEGIN
330
331 l_progress := 'POR_CANCEL_NOTIFICATION_PVT.set_supplier: 01';
332 IF (g_po_wf_debug = 'Y') THEN
333 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,g_module_prefix || l_api_name || l_progress);
334 END IF;
335
336 -- Do nothing in cancel or timeout mode
337 --
338 if (funcmode <> wf_engine.eng_run) then
339 resultout := wf_engine.eng_null;
340 return;
341 end if;
342
343 l_req_line_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
344 itemkey => itemkey,
345 aname => 'REQ_LINE_ID');
346
347 l_progress := 'POR_CANCEL_NOTIFICATION_PVT.set_supplier: 02';
348
349
350 l_cont_status := wf_engine.GetItemAttrText (itemtype => itemtype,
351 itemkey => itemkey,
352 aname => 'CONTRACTOR_STATUS');
353
354 l_progress := 'POR_CANCEL_NOTIFICATION_PVT.set_supplier: 03';
355
356
357 IF (l_cont_status = 'PENDING') THEN
358
359 SELECT max(requisition_supplier_id)
360 INTO l_requisition_supplier_id
361 FROM po_requisition_suppliers
362 WHERE
363 requisition_line_id = l_req_line_id AND
364 nvl(supplier_notified_flag, 'N') = 'N';
365
366 l_progress := 'POR_CANCEL_NOTIFICATION_PVT.set_supplier: 04';
367
368 IF (l_requisition_supplier_id is NULL) THEN
369 l_supplier_exists := 'NO_SUPPLIER';
370 ELSE
371 PO_REQAPPROVAL_INIT1.LOCATE_NOTIFIER(l_requisition_supplier_id, 'RS', l_performer);
372
373 l_progress := 'POR_CANCEL_NOTIFICATION_PVT.set_supplier: 05';
374
375 IF g_po_wf_debug = 'Y' THEN
376 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
377 g_module_prefix || l_api_name || 'PERFORMER: ' || l_performer);
378 END IF;
379
380 IF (l_performer IS NULL) THEN
381 l_performer := POR_CONTRACTOR_NOTIFY.GET_ADHOC_EMAIL_ROLE(l_requisition_supplier_id, null, itemtype, itemkey);
382
383 l_progress := 'POR_CANCEL_NOTIFICATION_PVT.set_supplier: 06';
384
385 IF g_po_wf_debug = 'Y' THEN
386 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
387 g_module_prefix || l_api_name || 'PERFORMER: ' || l_performer);
388 END IF;
389 END IF;
390
391 IF (l_performer is not null) THEN
392 wf_engine.SetItemAttrText (itemtype => itemtype,
393 itemkey => itemkey,
394 aname => 'SUPPLIER_ROLE',
395 avalue => l_performer);
396 END IF;
397
398 wf_engine.SetItemAttrNumber (itemtype => itemtype,
399 itemkey => itemkey,
400 aname => 'SUPPLIER_ID',
401 avalue => l_requisition_supplier_id);
402 l_supplier_exists := 'SUPPLIER_EXIST';
403 END IF;
404
405 ELSIF (l_cont_status = 'ASSIGNED') THEN -- only one supplier
406
407 SELECT max(vendor_id)
408 INTO l_requisition_supplier_id
409 FROM po_requisition_lines
410 WHERE
411 requisition_line_id = l_req_line_id AND
412 nvl(supplier_notified_for_cancel, 'N')='N';
413
414 l_progress := 'POR_CANCEL_NOTIFICATION_PVT.set_supplier: 07';
415
416 IF (l_requisition_supplier_id is NULL) THEN
417 l_supplier_exists := 'NO_SUPPLIER';
418 ELSE
419 PO_REQAPPROVAL_INIT1.LOCATE_NOTIFIER(l_req_line_id, 'RQ', l_performer);
420 l_progress := 'POR_CANCEL_NOTIFICATION_PVT.set_supplier: 08';
421
422 IF g_po_wf_debug = 'Y' THEN
423 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
424 g_module_prefix || l_api_name || 'PERFORMER: ' || l_performer);
425 END IF;
426
427 IF (l_performer IS NULL) THEN
428 l_performer := POR_CONTRACTOR_NOTIFY.GET_ADHOC_EMAIL_ROLE(null, l_req_line_id, itemtype, itemkey);
429
430 l_progress := 'POR_CANCEL_NOTIFICATION_PVT.set_supplier: 09';
431
432 IF g_po_wf_debug = 'Y' THEN
433 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
434 g_module_prefix || l_api_name || 'PERFORMER: ' || l_performer);
435 END IF;
436 END IF;
437
438 IF (l_performer is not null) THEN
439 wf_engine.SetItemAttrText (itemtype => itemtype,
440 itemkey => itemkey,
441 aname => 'SUPPLIER_ROLE',
442 avalue => l_performer);
443 END IF;
444
445 wf_engine.SetItemAttrNumber (itemtype => itemtype,
446 itemkey => itemkey,
447 aname => 'SUPPLIER_ID',
448 avalue => l_requisition_supplier_id);
449 l_supplier_exists := 'SUPPLIER_EXIST';
450 END IF;
451
452 END IF;
453
454 resultout := WF_ENGINE.ENG_COMPLETED || ':' || l_supplier_exists;
455
456 EXCEPTION
457 WHEN OTHERS THEN
458 wf_core.context('POR_CANCEL_NOTIF_PVT','set_supplier',l_progress);
459 raise;
460
461 END set_supplier;
462
463
464 /*==========================================================================*
465 * Updates SUPPLIER_NOTIFIED_FOR_CANCEL flag in po_requisition_lines *
466 *==========================================================================*/
467 PROCEDURE post_notification_process(itemtype in varchar2,
468 itemkey in varchar2,
469 actid in number,
470 funcmode in varchar2,
471 resultout out NOCOPY varchar2)
472 IS
473
474 l_progress varchar2(200);
475 l_req_line_id NUMBER := 0;
476 l_supplier_id NUMBER := 0;
477 l_cont_status po_requisition_lines.contractor_status%type;
478 l_api_name varchar2(50) := 'POST_NOTIFICATION_PROCESS';
479
480 BEGIN
481
482 l_progress := 'POR_CANCEL_NOTIFICATION_PVT.post_notification_process: 01';
483 IF (g_po_wf_debug = 'Y') THEN
484 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,g_module_prefix || l_api_name || l_progress);
485 END IF;
486
487
488 -- Do nothing in cancel or timeout mode
489 --
490 if (funcmode <> wf_engine.eng_run) then
491 resultout := wf_engine.eng_null;
492 return;
493 end if;
494
495 l_req_line_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
496 itemkey => itemkey,
497 aname => 'REQ_LINE_ID');
498
499 l_progress := 'POR_CANCEL_NOTIFICATION_PVT.post_notification_process: 02';
500
501 UPDATE po_requisition_lines_all
502 SET supplier_notified_for_cancel ='Y'
503 WHERE requisition_line_id = l_req_line_id;
504
505 l_cont_status := wf_engine.GetItemAttrText (itemtype => itemtype,
506 itemkey => itemkey,
507 aname => 'CONTRACTOR_STATUS');
508
509 IF (l_cont_status = 'PENDING') THEN
510
511 l_supplier_id := wf_engine.GetItemAttrNumber
512 (itemtype => itemtype,
513 itemkey => itemkey,
514 aname => 'SUPPLIER_ID');
515
516 UPDATE po_requisition_suppliers
517 SET supplier_notified_flag='Y'
518 WHERE requisition_supplier_id = l_supplier_id;
519
520 END IF;
521
522 EXCEPTION
523 WHEN OTHERS THEN
524 wf_core.context('POR_CANCEL_NOTIF_PVT','post_notification_process',l_progress);
525 raise;
526
527 END post_notification_process;
528
529
530 end por_cancel_notif_pvt;