[Home] [Help]
PACKAGE BODY: APPS.POR_CONTRACTOR_NOTIFY
Source
1 PACKAGE BODY POR_CONTRACTOR_NOTIFY AS
2 /* $Header: PORGCNTB.pls 120.1 2011/03/25 09:35:05 mmaramga ship $*/
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_CONTRACTOR_NOTIFY';
7 g_module_prefix CONSTANT VARCHAR2(50) := 'por.plsql.' || g_pkg_name || '.';
8
9 /*===========================================================================
10 PROCEDURE NAME: SUPPLIER_NEED_NOTIFY
11 DESCRIPTION: Checks if supplier for this requisition needs to be notified
12 ===========================================================================*/
13
14 PROCEDURE SUPPLIER_NEED_NOTIFY (ITEMTYPE IN VARCHAR2,
15 ITEMKEY IN VARCHAR2,
16 ACTID IN NUMBER,
17 FUNCMODE IN VARCHAR2,
18 RESULTOUT OUT NOCOPY VARCHAR2 )
19 IS
20
21 L_REQ_HEADER_ID Number;
22 L_CONTR_ASSIGN_REQD VARCHAR2(1) := 'N';
23 L_CONTRACTOR_STATUS PO_REQUISITION_HEADERS_ALL.CONTRACTOR_STATUS%TYPE;
24 L_SUPPLIER_NOTIFIED_FLAG PO_REQUISITION_HEADERS_ALL.SUPPLIER_NOTIFIED_FLAG%TYPE;
25
26 BEGIN
27
28 L_REQ_HEADER_ID := WF_ENGINE.GETITEMATTRNUMBER
29 (ITEMTYPE => ITEMTYPE,
30 ITEMKEY => ITEMKEY,
31 ANAME => 'DOCUMENT_ID');
32
33 --Query the supplires which need to be notified for this requisition
34 SELECT CONTRACTOR_STATUS, SUPPLIER_NOTIFIED_FLAG
35 INTO L_CONTRACTOR_STATUS, L_SUPPLIER_NOTIFIED_FLAG
36 FROM PO_REQUISITION_HEADERS_ALL
37 WHERE REQUISITION_HEADER_ID = L_REQ_HEADER_ID;
38
39 IF g_po_wf_debug = 'Y' THEN
40 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
41 g_module_prefix || 'SUPPLIER_NEED_NOTIFY' ||
42 'L_CONTRACTOR_STATUS, L_SUPPLIER_NOTIFIED_FLAG: ' ||
43 L_CONTRACTOR_STATUS|| ',' || L_SUPPLIER_NOTIFIED_FLAG);
44 end if;
45
46 IF L_CONTRACTOR_STATUS = 'PENDING' AND nvl(L_SUPPLIER_NOTIFIED_FLAG,'N') = 'N' THEN
47 L_CONTR_ASSIGN_REQD := 'Y';
48 ELSE
49 L_CONTR_ASSIGN_REQD := 'N';
50 END IF;
51
52 RESULTOUT := WF_ENGINE.ENG_COMPLETED || ':' || L_CONTR_ASSIGN_REQD;
53
54 EXCEPTION
55 WHEN OTHERS THEN
56 IF g_po_wf_debug = 'Y' THEN
57 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, g_module_prefix || 'SUPPLIER_NEED_NOTIFY' || sqlerrm);
58 END IF;
59
60 END SUPPLIER_NEED_NOTIFY;
61
62
63 PROCEDURE SELECT_SUPPLIER_NOTIFY (ITEMTYPE IN VARCHAR2,
64 ITEMKEY IN VARCHAR2,
65 ACTID IN NUMBER,
66 FUNCMODE IN VARCHAR2,
67 RESULTOUT OUT NOCOPY VARCHAR2 )
68 IS
69
70 L_REQUISITION_SUPPLIER_ID PO_REQUISITION_SUPPLIERS.REQUISITION_SUPPLIER_ID%TYPE;
71 L_REQ_HEADER_ID PO_REQUISITION_HEADERS_ALL.REQUISITION_HEADER_ID%TYPE;
72 L_SUPPLIER_EXISTS VARCHAR2(50);
73 L_NOTIFIER WF_USER_ROLES.ROLE_NAME%TYPE;
74 L_START_DATE PO_REQUISITION_LINES_ALL.ASSIGNMENT_START_DATE%TYPE;
75 L_JOB_DESCRIPTION PO_REQUISITION_LINES_ALL.ITEM_DESCRIPTION%TYPE;
76 L_COMPANY_NAME varchar2(100);
77 L_REQ_NUM_LINE_NUM varchar2(100);
78 L_STATUS varchar2(1);
79 L_EXP_MSG varchar2(100);
80 L_REQUISITION_LINE_ID PO_REQUISITION_LINES_ALL.REQUISITION_LINE_ID%TYPE;
81 L_LINE_NUM PO_REQUISITION_LINES_ALL.LINE_NUM%TYPE;
82 L_VENDOR_NAME PO_VENDORS.VENDOR_NAME%TYPE;
83
84 BEGIN
85
86 L_REQ_HEADER_ID := WF_ENGINE.GETITEMATTRNUMBER
87 (ITEMTYPE => ITEMTYPE,
88 ITEMKEY => ITEMKEY,
89 ANAME => 'DOCUMENT_ID');
90 --To be removed
91 if L_REQ_HEADER_ID is not null then
92 UPDATE PO_REQUISITION_HEADERS_ALL
93 SET SUPPLIER_NOTIFIED_FLAG = 'Y'
94 WHERE REQUISITION_HEADER_ID = L_REQ_HEADER_ID;
95 end if;
96
97 --Query the suppliers which need to be notified for this requisition
98 BEGIN
99
100 SELECT MAX(PLS.REQUISITION_SUPPLIER_ID)
101 INTO L_REQUISITION_SUPPLIER_ID
102 FROM PO_REQUISITION_SUPPLIERS PLS,
103 PO_REQUISITION_LINES_ALL PORL
104 WHERE NVL(PLS.SUPPLIER_NOTIFIED_FLAG,'N') = 'N'
105 AND PORL.REQUISITION_LINE_ID = PLS.REQUISITION_LINE_ID
106 AND PORL.REQUISITION_HEADER_ID = L_REQ_HEADER_ID;
107
108 EXCEPTION
109 WHEN NO_DATA_FOUND THEN
110 L_REQUISITION_SUPPLIER_ID := NULL;
111 END;
112
113 IF L_REQUISITION_SUPPLIER_ID IS NULL THEN
114
115 L_SUPPLIER_EXISTS := 'ALL_SUPPLIER_NOTIFIED';
116 UPDATE PO_REQUISITION_HEADERS_ALL
117 SET SUPPLIER_NOTIFIED_FLAG = 'Y'
118 WHERE REQUISITION_HEADER_ID = L_REQ_HEADER_ID;
119
120 ELSE
121
122 SELECT PORL.ASSIGNMENT_START_DATE,
123 PORL.ITEM_DESCRIPTION, PORH.SEGMENT1 || ' / ' || PORL.LINE_NUM, PORL.REQUISITION_LINE_ID
124 INTO L_START_DATE, L_JOB_DESCRIPTION, L_REQ_NUM_LINE_NUM, L_REQUISITION_LINE_ID
125 FROM PO_REQUISITION_SUPPLIERS PLS,
126 PO_REQUISITION_LINES_ALL PORL,
127 PO_REQUISITION_HEADERS_ALL PORH
128 WHERE PORL.REQUISITION_LINE_ID = PLS.REQUISITION_LINE_ID
129 AND PLS.REQUISITION_SUPPLIER_ID = L_REQUISITION_SUPPLIER_ID
130 AND PORL.REQUISITION_HEADER_ID = PORH.REQUISITION_HEADER_ID;
131
132 IF g_po_wf_debug = 'Y' THEN
133 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
134 g_module_prefix || 'SELECT_SUPPLIER_NOTIFY' ||
135 'L_START_DATE, L_JOB_DESCRIPTION, L_REQUISITION_SUPPLIER_ID: ' ||
136 L_START_DATE || ',' || L_JOB_DESCRIPTION || ',' ||
137 L_REQUISITION_SUPPLIER_ID);
138 end if;
139
140 WF_ENGINE.SETITEMATTRNUMBER
141 (ITEMTYPE => ITEMTYPE,
142 ITEMKEY => ITEMKEY,
143 ANAME => 'REQUISITION_SUPPLIER_ID',
144 AVALUE => L_REQUISITION_SUPPLIER_ID);
145
146 WF_ENGINE.SETITEMATTRTEXT
147 (ITEMTYPE => ITEMTYPE,
148 ITEMKEY => ITEMKEY,
149 ANAME => 'JOB_DESCRIPTION',
150 AVALUE => L_JOB_DESCRIPTION);
151
152 WF_ENGINE.SETITEMATTRDATE
153 (ITEMTYPE => ITEMTYPE,
154 ITEMKEY => ITEMKEY,
155 ANAME => 'START_DATE',
156 AVALUE => L_START_DATE);
157
158 POS_ENTERPRISE_UTIL_PKG.GET_ENTERPRISE_PARTY_NAME(L_COMPANY_NAME, L_EXP_MSG, L_STATUS);
159
160 WF_ENGINE.SETITEMATTRTEXT
161 (ITEMTYPE => ITEMTYPE,
162 ITEMKEY => ITEMKEY,
163 ANAME => 'COMPANY_DISPLAY_NAME',
164 AVALUE => L_COMPANY_NAME);
165
166 WF_ENGINE.SETITEMATTRTEXT
167 (ITEMTYPE => ITEMTYPE,
168 ITEMKEY => ITEMKEY,
169 ANAME => 'REQ_NUM_LINE_NUM',
170 AVALUE => L_REQ_NUM_LINE_NUM);
171
172 WF_ENGINE.SetItemAttrDocument(itemtype => itemtype,
173 itemkey => itemkey,
174 aname => 'LINE_ATTACHMENT',
175 documentid =>
176 'FND:entity=REQ_LINES' || '&' || 'pk1name=REQUISITION_LINE_ID' ||
177 '&' || 'pk1value='|| L_REQUISITION_LINE_ID ||
178 '&' || 'categories=Vendor');
179
180 PO_REQAPPROVAL_INIT1.LOCATE_NOTIFIER(L_REQUISITION_SUPPLIER_ID, 'RS', L_NOTIFIER);
181
182 IF g_po_wf_debug = 'Y' THEN
183 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
184 g_module_prefix || 'SELECT_SUPPLIER_NOTIFY' || 'L_NOTIFIER: ' || L_NOTIFIER || ' L_REQUISITION_SUPPLIER_ID :' || L_REQUISITION_SUPPLIER_ID);
185 end if;
186
187 IF (L_NOTIFIER IS NULL) THEN
188 L_NOTIFIER := GET_ADHOC_EMAIL_ROLE(L_REQUISITION_SUPPLIER_ID, NULL, ITEMTYPE, ITEMKEY);
189 END IF;
190
191 IF (L_NOTIFIER IS NOT NULL) THEN
192
193 WF_ENGINE.SETITEMATTRTEXT (ITEMTYPE => ITEMTYPE,
194 ITEMKEY => ITEMKEY,
195 ANAME => 'STAFF_SUPPLIER_NAME',
196 AVALUE => L_NOTIFIER);
197
198 L_SUPPLIER_EXISTS := 'SUPPLIER_EMAIL_EXISTS';
199
200 IF g_po_wf_debug = 'Y' THEN
201 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
202 g_module_prefix || 'SELECT_SUPPLIER_NOTIFY' || 'L_SUPPLIER_EXISTS: ' || L_SUPPLIER_EXISTS);
203 END IF;
204
205 else
206
207 L_SUPPLIER_EXISTS := 'SUPPLIER_EMAIL_NOT_AVAILABLE';
208
209 IF g_po_wf_debug = 'Y' THEN
210 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
211 g_module_prefix || 'SELECT_SUPPLIER_NOTIFY' || 'L_SUPPLIER_EXISTS: ' || L_SUPPLIER_EXISTS);
212 END IF;
213
214 SELECT PORL.LINE_NUM, POV.VENDOR_NAME
215 INTO L_LINE_NUM, L_VENDOR_NAME
216 FROM PO_REQUISITION_SUPPLIERS PLS,
217 PO_REQUISITION_LINES_ALL PORL,
218 PO_VENDORS POV
219 WHERE PORL.REQUISITION_LINE_ID = PLS.REQUISITION_LINE_ID
220 AND PLS.REQUISITION_SUPPLIER_ID = L_REQUISITION_SUPPLIER_ID
221 AND PLS.VENDOR_ID = POV.VENDOR_ID;
222
223 IF g_po_wf_debug = 'Y' THEN
224 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
225 g_module_prefix || 'SELECT_SUPPLIER_NOTIFY L_LINE_NUM:L_VENDOR_NAME'
226 || L_LINE_NUM || ':' || L_VENDOR_NAME);
227 END IF;
228
229 WF_ENGINE.SETITEMATTRTEXT
230 (ITEMTYPE => ITEMTYPE,
231 ITEMKEY => ITEMKEY,
232 ANAME => 'VENDOR_DISPLAY_NAME',
233 AVALUE => L_VENDOR_NAME);
234
235 WF_ENGINE.SETITEMATTRNUMBER
236 (ITEMTYPE => ITEMTYPE,
237 ITEMKEY => ITEMKEY,
238 ANAME => 'REQUISITION_LINE_NUM',
239 AVALUE => L_LINE_NUM);
240
241 WF_ENGINE.SETITEMATTRTEXT
242 (ITEMTYPE => ITEMTYPE,
243 ITEMKEY => ITEMKEY,
244 ANAME => 'IS_SUPPLIER_EMAIL_NOT_AVAIL',
245 AVALUE => 'Y');
246 END IF;
247
248 END IF;
249
250 RESULTOUT := WF_ENGINE.ENG_COMPLETED || ':' || L_SUPPLIER_EXISTS;
251
252 EXCEPTION
253 WHEN OTHERS THEN
254 IF g_po_wf_debug = 'Y' THEN
255 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, g_module_prefix || 'SELECT_SUPPLIER_NOTIFY' || sqlerrm);
256 END IF;
257
258 END SELECT_SUPPLIER_NOTIFY;
259
260 PROCEDURE UPDATE_NOTIFY_SUPPLIER (ITEMTYPE IN VARCHAR2,
261 ITEMKEY IN VARCHAR2,
262 ACTID IN NUMBER,
263 FUNCMODE IN VARCHAR2,
264 RESULTOUT OUT NOCOPY VARCHAR2 )
265 IS
266
267 L_REQUISITION_SUPPLIER_ID Number;
268
269 BEGIN
270
271 L_REQUISITION_SUPPLIER_ID := WF_ENGINE.GETITEMATTRNUMBER
272 (ITEMTYPE => ITEMTYPE,
273 ITEMKEY => ITEMKEY,
274 ANAME => 'REQUISITION_SUPPLIER_ID');
275 IF g_po_wf_debug = 'Y' THEN
276 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
277 g_module_prefix || 'UPDATE_NOTIFY_SUPPLIER' || 'L_REQUISITION_SUPPLIER_ID : ' || L_REQUISITION_SUPPLIER_ID);
278 end if;
279
280 UPDATE PO_REQUISITION_SUPPLIERS
281 SET SUPPLIER_NOTIFIED_FLAG = 'Y', SUPPLIER_NOTIFIED_DATE = SYSDATE
282 WHERE REQUISITION_SUPPLIER_ID = L_REQUISITION_SUPPLIER_ID;
283
284 RETURN;
285
286 EXCEPTION
287 WHEN OTHERS THEN
288 IF g_po_wf_debug = 'Y' THEN
289 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, g_module_prefix || 'UPDATE_NOTIFY_SUPPLIER' || sqlerrm);
290 END IF;
291
292 END UPDATE_NOTIFY_SUPPLIER;
293
294
295 FUNCTION GET_ADHOC_EMAIL_ROLE(L_REQ_SUPPLIER_ID NUMBER,
296 L_REQ_LINE_ID NUMBER,
297 ITEMTYPE VARCHAR2,
298 ITEMKEY VARCHAR2)
299 RETURN varchar2
300 IS
301
302 X_PROGRESS VARCHAR2(300);
303 L_DOC_STRING VARCHAR2(200);
304 L_VENDOR_SITE_CODE PO_VENDOR_SITES.VENDOR_SITE_CODE%TYPE;
305 L_VENDOR_SITE_ID NUMBER;
306 L_VENDOR_CONTACT_ID NUMBER;
307 L_VENDOR_SITE_LANG PO_VENDOR_SITES.LANGUAGE%TYPE;
308 L_ADHOCUSER_LANG WF_LANGUAGES.NLS_LANGUAGE%TYPE;
309 L_ADHOCUSER_TERRITORY WF_LANGUAGES.NLS_TERRITORY%TYPE;
310 L_EMAIL_PERFORMER WF_USER_ROLES.ROLE_NAME%TYPE := NULL;
311 L_EMAIL_ADDRESS VARCHAR2(2000) := NULL;
312 L_DISPLAY_NAME VARCHAR2(80);
313 L_PERFORMER_EXISTS NUMBER;
314 L_NOTIFICATION_PREFERENCE VARCHAR2(20) := 'MAILHTML';
315
316 BEGIN
317
318 IF (G_PO_WF_DEBUG = 'Y') THEN
319 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE,ITEMKEY,'POR_CONTRACTOR_NOTIFY.GET_ADHOC_EMAIL_ROLE: L_REQ_SUPPLIER_ID:' || L_REQ_SUPPLIER_ID);
320 END IF;
321
322 BEGIN
323 IF (L_REQ_SUPPLIER_ID IS NOT NULL) THEN
324
325 SELECT PRS.VENDOR_SITE_ID, PRS.VENDOR_CONTACT_ID, PVS.VENDOR_SITE_CODE, PVS.LANGUAGE
326 INTO L_VENDOR_SITE_ID, L_VENDOR_CONTACT_ID, L_VENDOR_SITE_CODE, L_VENDOR_SITE_LANG
327 FROM PO_REQUISITION_SUPPLIERS PRS, PO_VENDOR_SITES_ALL PVS
328 WHERE PRS.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
329 AND PRS.REQUISITION_SUPPLIER_ID = L_REQ_SUPPLIER_ID;
330
331 ELSIF (L_REQ_LINE_ID IS NOT NULL) THEN
332
333 SELECT PRL.VENDOR_SITE_ID, PRL.VENDOR_CONTACT_ID, PVS.VENDOR_SITE_CODE, PVS.LANGUAGE
334 INTO L_VENDOR_SITE_ID, L_VENDOR_CONTACT_ID, L_VENDOR_SITE_CODE, L_VENDOR_SITE_LANG
335 FROM PO_REQUISITION_LINES PRL, PO_VENDOR_SITES_ALL PVS
336 WHERE PRL.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
337 AND PRL.REQUISITION_LINE_ID = L_REQ_LINE_ID;
338
339 END IF;
340
341 EXCEPTION
342 WHEN NO_DATA_FOUND THEN
343 IF (G_PO_WF_DEBUG = 'Y') THEN
344 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE,ITEMKEY,'POR_CONTRACTOR_NOTIFY.GET_ADHOC_EMAIL_ROLE: SUPPLIER SITE DOES NOT EXIST');
345 END IF;
346 RETURN NULL;
347 END;
348
349 IF (G_PO_WF_DEBUG = 'Y') THEN
350 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE,ITEMKEY,' L_VENDOR_SITE_ID, L_VENDOR_CONTACT_ID, L_VENDOR_SITE_CODE, L_VENDOR_SITE_LANG:'
351 || L_VENDOR_SITE_ID || '*' || L_VENDOR_CONTACT_ID || '*' || L_VENDOR_SITE_CODE || '*' || L_VENDOR_SITE_LANG);
352 END IF;
353
354 --GET EMAIL ADDRESS FROM SUPPLIER SITE
355 BEGIN
356 SELECT EMAIL_ADDRESS
357 INTO L_EMAIL_ADDRESS
358 FROM PO_VENDOR_SITES_ALL
359 WHERE VENDOR_SITE_ID = L_VENDOR_SITE_ID;
360 EXCEPTION
361 WHEN NO_DATA_FOUND THEN
362 L_EMAIL_ADDRESS := NULL;
363 END;
364
365 IF (G_PO_WF_DEBUG = 'Y') THEN
366 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE,ITEMKEY,'POR_CONTRACTOR_NOTIFY.GET_ADHOC_EMAIL_ROLE: L_EMAIL_ADDRESS FROM SUPPLIER SITE:' || L_EMAIL_ADDRESS || '*');
367 END IF;
368
369 --GET EMAIL ADDRESS FROM VENDOR CONTACTS
370 IF L_EMAIL_ADDRESS IS NULL AND L_VENDOR_CONTACT_ID IS NOT NULL THEN
371 BEGIN
372 SELECT EMAIL_ADDRESS
373 INTO L_EMAIL_ADDRESS
374 FROM PO_VENDOR_CONTACTS
375 WHERE VENDOR_CONTACT_ID = L_VENDOR_CONTACT_ID
376 AND VENDOR_SITE_ID = L_VENDOR_SITE_ID;
377
378 IF (G_PO_WF_DEBUG = 'Y') THEN
379 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE,ITEMKEY,'POR_CONTRACTOR_NOTIFY.GET_ADHOC_EMAIL_ROLE: L_EMAIL_ADDRESS FROM VENDOR CONTACT:' || L_EMAIL_ADDRESS);
380 END IF;
381
382 EXCEPTION
383 WHEN NO_DATA_FOUND THEN
384 L_EMAIL_ADDRESS := NULL;
385 END;
386 END IF;
387
388 IF L_EMAIL_ADDRESS IS NULL THEN
389 IF (G_PO_WF_DEBUG = 'Y') THEN
390 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE,ITEMKEY,'POR_CONTRACTOR_NOTIFY.GET_ADHOC_EMAIL_ROLE: L_EMAIL_ADDRESS IS NULL');
391 END IF;
392 RETURN NULL;
393 END IF;
394
395 L_EMAIL_PERFORMER := L_VENDOR_SITE_CODE || SUBSTR(L_VENDOR_SITE_ID, 1, 15);
396 L_DISPLAY_NAME := L_VENDOR_SITE_CODE || SUBSTR(L_VENDOR_SITE_ID, 1, 15);
397
398 --ALSO RETRIEVE LANGUAGE TO SET THE ADHOCUSER LANGUAGE TO SUPPLIER SITE PREFERRED LANGUAGE
399
400 IF L_VENDOR_SITE_LANG IS NOT NULL THEN
401
402 SELECT WFL.NLS_LANGUAGE, WFL.NLS_TERRITORY INTO L_ADHOCUSER_LANG, L_ADHOCUSER_TERRITORY
403 FROM WF_LANGUAGES WFL, FND_LANGUAGES_VL FLV
404 WHERE WFL.CODE = FLV.LANGUAGE_CODE
405 AND FLV.NLS_LANGUAGE = L_VENDOR_SITE_LANG;
406
407 ELSE
408
409 SELECT WFL.NLS_LANGUAGE, WFL.NLS_TERRITORY INTO L_ADHOCUSER_LANG, L_ADHOCUSER_TERRITORY
410 FROM WF_LANGUAGES WFL, FND_LANGUAGES_VL FLV
411 WHERE WFL.CODE = FLV.LANGUAGE_CODE
412 AND FLV.INSTALLED_FLAG = 'B';
413
414 END IF;
415
416 SELECT COUNT(*)
417 INTO L_PERFORMER_EXISTS
418 FROM WF_USERS
419 WHERE NAME = L_EMAIL_PERFORMER;
420
421 X_PROGRESS := '003';
422
423 IF (L_PERFORMER_EXISTS = 0) THEN
424
425 WF_DIRECTORY.CREATEADHOCUSER(L_EMAIL_PERFORMER, L_DISPLAY_NAME, L_ADHOCUSER_LANG,
426 L_ADHOCUSER_TERRITORY, NULL, L_NOTIFICATION_PREFERENCE, L_EMAIL_ADDRESS, NULL,
427 'ACTIVE', NULL);
428
429 ELSE
430
431 WF_DIRECTORY.SETADHOCUSERATTR(L_EMAIL_PERFORMER, L_DISPLAY_NAME, L_NOTIFICATION_PREFERENCE,
432 L_ADHOCUSER_LANG, L_ADHOCUSER_TERRITORY, L_EMAIL_ADDRESS, NULL);
433
434 END IF;
435
436 X_PROGRESS := 'POR_CONTRACTOR_NOTIFY.GET_ADHOC_EMAIL_ROLE: 02';
437 IF (G_PO_WF_DEBUG = 'Y') THEN
438 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE,ITEMKEY,X_PROGRESS);
439 END IF;
440
441 RETURN L_EMAIL_PERFORMER;
442
443 EXCEPTION
444 WHEN OTHERS THEN
445 IF g_po_wf_debug = 'Y' THEN
446 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, g_module_prefix || 'GET_ADHOC_EMAIL_ROLE' || sqlerrm);
447 END IF;
448
449 END GET_ADHOC_EMAIL_ROLE;
450
451 /*===========================================================================
452 PROCEDURE NAME: SET_REQSINPOOL_FLAG
453 DESCRIPTION: Sets the REQS_IN_POOL flag in the po_requisition_lines_all table to 'Y'
454 ===========================================================================*/
455
456 PROCEDURE SET_REQSINPOOL_FLAG (ITEMTYPE IN VARCHAR2,
457 ITEMKEY IN VARCHAR2,
458 ACTID IN NUMBER,
459 FUNCMODE IN VARCHAR2,
460 RESULTOUT OUT NOCOPY VARCHAR2 )
461 is
462 L_REQ_HEADER_ID PO_REQUISITION_HEADERS_ALL.REQUISITION_HEADER_ID%TYPE;
463
464 BEGIN
465
466 L_REQ_HEADER_ID := WF_ENGINE.GETITEMATTRNUMBER
467 (ITEMTYPE => ITEMTYPE,
468 ITEMKEY => ITEMKEY,
469 ANAME => 'DOCUMENT_ID');
470
471 IF L_REQ_HEADER_ID is not null then
472 UPDATE PO_REQUISITION_LINES_ALL
473 SET REQS_IN_POOL_FLAG = 'Y'
474 WHERE REQUISITION_HEADER_ID = L_REQ_HEADER_ID
475 AND NVL(CONTRACTOR_REQUISITION_FLAG, 'N') = 'Y';
476 END IF;
477
478 EXCEPTION
479 WHEN OTHERS THEN
480 IF g_po_wf_debug = 'Y' THEN
481 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, g_module_prefix || 'SET_REQSINPOOL_FLAG' || sqlerrm);
482 END IF;
483
484 END SET_REQSINPOOL_FLAG;
485
486 END POR_CONTRACTOR_NOTIFY;