1 package POS_REG_WF_PKG as
2 /* $Header: POSREGWS.pls 120.1 2006/06/28 23:03:49 jpasala noship $ */
3
4 V_PACKAGE_NAME CONSTANT ALL_OBJECTS.OBJECT_NAME%TYPE := 'POS_REG_WF_PKG';
5
6 /*----------------------------------------
7
8 public PROCEDURE LockReg
9
10 Workflow activity function. Lock the registration record to prevent
11 spontaneous responses to the same invitation.
12
13 PARAMS:
14 WF Standard API.
15
16 RETURN:
17 COMPLETE:SUCCESS - if the record is successfully locked.
18 COMPLETE:ERROR - if the record's status has changed when getting the lock
19
20 ----------------------------------------*/
21
22 PROCEDURE LockReg(
23 itemtype IN VARCHAR2
24 , itemkey IN VARCHAR2
25 , actid IN NUMBER
26 , funcmode IN VARCHAR2
27 , resultout OUT NOCOPY VARCHAR2
28 );
29
30 /*----------------------------------------
31
32 public PROCEDURE LockApprv
33
34 Workflow activity function. Lock the registration record (to be approved)
35 to prevent spontaneous approving process.
36
37 PARAMS:
38 WF Standard API.
39
40 RETURN:
41 COMPLETE:SUCCESS - if the record is successfully locked.
42 COMPLETE:ERROR - if the record's status has changed when getting the lock
43
44 ----------------------------------------*/
45
46 PROCEDURE LockApprv(
47 itemtype IN VARCHAR2
48 , itemkey IN VARCHAR2
49 , actid IN NUMBER
50 , funcmode IN VARCHAR2
51 , resultout OUT NOCOPY VARCHAR2
52 );
53
54 /*----------------------------------------
55
56 public PROCEDURE LockRjct
57
58 Workflow activity function. Lock the registration record (to be rejected)
59 to prevent spontaneous rejection.
60
61 PARAMS:
62 WF Standard API.
63
64 RETURN:
65 COMPLETE:SUCCESS - if the record is successfully locked.
66 COMPLETE:ERROR - if the record's status has changed when getting the lock
67
68 ----------------------------------------*/
69
70 PROCEDURE LockRjct(
71 itemtype IN VARCHAR2
72 , itemkey IN VARCHAR2
73 , actid IN NUMBER
74 , funcmode IN VARCHAR2
75 , resultout OUT NOCOPY VARCHAR2
76 );
77
78 /*----------------------------------------
79
80 public PROCEDURE UpdInvTypeKey
81
82 Workflow activity function. Update registration details with the itemtype
83 and item key
84
85 PARAMS:
86 WF Standard API.
87
88 RETURN:
89 COMPLETE : always return COMPLETE. do not raise errors.
90
91 ----------------------------------------*/
92
93 PROCEDURE UpdInvTypeKey(
94 itemtype IN VARCHAR2
95 , itemkey IN VARCHAR2
96 , actid IN NUMBER
97 , funcmode IN VARCHAR2
98 , resultout OUT NOCOPY VARCHAR2
99 );
100
101 /*----------------------------------------
102
103 public PROCEDURE UpdRegTypeKey
104
105 Workflow activity function. Update registration details with the itemtype
106 and item key
107
108 PARAMS:
109 WF Standard API.
110
111 RETURN:
112 COMPLETE : always return COMPLETE. do not raise errors.
113
114 ----------------------------------------*/
115
116 PROCEDURE UpdRegTypeKey(
117 itemtype IN VARCHAR2
118 , itemkey IN VARCHAR2
119 , actid IN NUMBER
120 , funcmode IN VARCHAR2
121 , resultout OUT NOCOPY VARCHAR2
122 );
123
124 /*----------------------------------------
125
126 public PROCEDURE UpdApprvTypeKey
127
128 Workflow activity function. Update registration details with the itemtype
129 and item key
130
131 PARAMS:
132 WF Standard API.
133
134 RETURN:
135 COMPLETE : always return COMPLETE. do not raise errors.
136
137 ----------------------------------------*/
138
139 PROCEDURE UpdApprvTypeKey(
140 itemtype IN VARCHAR2
141 , itemkey IN VARCHAR2
142 , actid IN NUMBER
143 , funcmode IN VARCHAR2
144 , resultout OUT NOCOPY VARCHAR2
145 );
146
147 /*----------------------------------------
148
149 public PROCEDURE UpdRjctTypeKey
150
151 Workflow activity function. Update registration details with the itemtype
152 and item key
153
154 PARAMS:
155 WF Standard API.
156
157 RETURN:
158 COMPLETE : always return COMPLETE. do not raise errors.
159
160 ----------------------------------------*/
161
162 PROCEDURE UpdRjctTypeKey(
163 itemtype IN VARCHAR2
164 , itemkey IN VARCHAR2
165 , actid IN NUMBER
166 , funcmode IN VARCHAR2
167 , resultout OUT NOCOPY VARCHAR2
168 );
169
170 /*----------------------------------------
171
172 public PROCEDURE SetInvItemAttrValues
173
174 Workflow activity function. Set item attribute values in 'USER_INVITED'
175 process.
176 Following attributes are set:
177
178 * REGISTRANT_LANGUAGE
179 * REGISTRANT_EMAIL * ADHOC_USER_NAME
180 * ENTERPRISE_NAME * REG_PAGE_URL
181
182 PARAMS:
183 WF Standard API.
184
185 RETURN:
186 COMPLETE:SUCCESS - if everything is ok.
187 COMPLETE:ERROR - if critical attribute cannot be set.
188
189 ----------------------------------------*/
190
191 PROCEDURE SetInvItemAttrValues(
192 itemtype IN VARCHAR2
193 , itemkey IN VARCHAR2
194 , actid IN NUMBER
195 , funcmode IN VARCHAR2
196 , resultout OUT NOCOPY VARCHAR2
197 );
198
199 /*----------------------------------------
200
201 public PROCEDURE SetRegItemAttrValues
202
203 Workflow activity function. Set item attribute values in 'USER_REGISTERED'
204 process.
205 Following attributes are set:
206
207 * APPROVER_ROLE
208 * FIRST_NAME * LAST_NAME
209 * VENDOR_NAME * APPROVAL_PAGE_URL
210
211 PARAMS:
212 WF Standard API.
213
214 RETURN:
215 COMPLETE:SUCCESS - if everything is ok.
216 COMPLETE:ERROR - if critical attribute cannot be set.
217
218 ----------------------------------------*/
219
220 PROCEDURE SetRegItemAttrValues(
221 itemtype IN VARCHAR2
222 , itemkey IN VARCHAR2
223 , actid IN NUMBER
224 , funcmode IN VARCHAR2
225 , resultout OUT NOCOPY VARCHAR2
226 );
227
228 /*----------------------------------------
229
230 public PROCEDURE SetApprvItemAttrValues
231
232 Workflow activity function. Set item attribute values in 'USER_APPROVED'
233 process.
234 Following attributes are set:
235
236 * ENTERPRISE_NAME * LOGON_PAGE_URL
237 * CONTACT_EMAIL * IS_INVITED
238 * ADHOC_USER_NAME * REQUESTED_USER_NAME
239 * REGISTRANT_EMAIL * VENDOR_ID
240 * POS_SELECTED * PON_SELECTED
241 * FIRST_NAME * LAST_NAME
242
243 PARAMS:
244 WF Standard API.
245
246 RETURN:
247 COMPLETE:SUCCESS - if everything is ok.
248 COMPLETE:ERROR - if critical attribute cannot be set.
249
250 ----------------------------------------*/
251
252 PROCEDURE SetApprvItemAttrValues(
253 itemtype IN VARCHAR2
254 , itemkey IN VARCHAR2
255 , actid IN NUMBER
256 , funcmode IN VARCHAR2
257 , resultout OUT NOCOPY VARCHAR2
258 );
259
260 /*----------------------------------------
261
262 public PROCEDURE SetRjctItemAttrValues
263
264 Workflow activity function. Set item attribute values in 'USER_REJECTED'
265 process.
266 Following attributes are set:
267
268 * REGISTRANT_LANGUAGE * IS_INVITED
269 * REGISTRANT_EMAIL * ADHOC_USER_NAME
270 * ENTERPRISE_NAME * CONTACT_EMAIL
271
272 PARAMS:
273 WF Standard API.
274
275 RETURN:
276 COMPLETE:SUCCESS - if everything is ok.
277 COMPLETE:ERROR - if critical attribute cannot be set.
278
279 ----------------------------------------*/
280
281 PROCEDURE SetRjctItemAttrValues(
282 itemtype IN VARCHAR2
283 , itemkey IN VARCHAR2
284 , actid IN NUMBER
285 , funcmode IN VARCHAR2
286 , resultout OUT NOCOPY VARCHAR2
287 );
288
289 /*----------------------------------------
290
291 public PROCEDURE CreateLocalUser
292
293 Workflow activity function. Create a workflow ad-hoc (local) user.
294
295 PARAMS:
296 WF Standard API.
297
298 RETURN:
299 COMPLETE:SUCCESS - if everything is ok
300 COMPLETE:ERROR - if anything wrong
301
302 ----------------------------------------*/
303
304 PROCEDURE CreateLocalUser(
305 itemtype IN VARCHAR2
306 , itemkey IN VARCHAR2
307 , actid IN NUMBER
308 , funcmode IN VARCHAR2
309 , resultout OUT NOCOPY VARCHAR2
310 );
311
312 /*----------------------------------------
313
314 public PROCEDURE DeleteLocalUser
315
316 Workflow activity function. Delete a workflow ad-hoc (local) user.
317
318 PARAMS:
319 WF Standard API.
320
321 RETURN:
322 WF Standard API.
323
324 ----------------------------------------*/
325
326 PROCEDURE DeleteLocalUser(
327 itemtype IN VARCHAR2
328 , itemkey IN VARCHAR2
329 , actid IN NUMBER
330 , funcmode IN VARCHAR2
331 , resultout OUT NOCOPY VARCHAR2
332 );
333
334 /*----------------------------------------
335
336 public PROCEDURE CreateUser
337
338 Workflow activity function. Actually create the user in FND_USER.
339 Following activities are done:
340 * generate random user password
341 * insert FND_USER
342 * insert TCA
343 * assign user responsibility
344 * set user security attributes
345
346 Two item attribute values are set:
347
348 * ASSIGNED_USER_NAME * FIRST_LOGON_KEY
349
350 PARAMS:
351 WF Standard API.
352
353 RETURN:
354 WF Standard API.
355
356 ----------------------------------------*/
357
358 PROCEDURE CreateUser(
359 itemtype IN VARCHAR2
360 , itemkey IN VARCHAR2
361 , actid IN NUMBER
362 , funcmode IN VARCHAR2
363 , resultout OUT NOCOPY VARCHAR2
364 );
365
366 /*----------------------------------------
367
368 public PROCEDURE IsInvited
369
370 Workflow activity function. Check whether the registration is invited.
371
372 PARAMS:
373 WF Standard API.
374
375 RETURN:
376 COMPLETE:Y - if the registration is invited;
377 COMPLETE:N - otherwise
378
379 ----------------------------------------*/
380
381 PROCEDURE IsInvited(
382 itemtype IN VARCHAR2
383 , itemkey IN VARCHAR2
384 , actid IN NUMBER
385 , funcmode IN VARCHAR2
386 , resultout OUT NOCOPY VARCHAR2
387 );
388
389 /*----------------------------------------
390
391 public PROCEDURE CheckRejectMailSent
392
393 Workflow activity function. Check whether the rejection email has been
394 sent or not
395
396 PARAMS:
397 WF Standard API.
398
399 RETURN:
400 COMPLETE:Y - if the mail has been sent
401 COMPLETE:N - otherwise
402
403 ----------------------------------------*/
404
405 PROCEDURE CheckRejectMailSent(
406 itemtype IN VARCHAR2
407 , itemkey IN VARCHAR2
408 , actid IN NUMBER
409 , funcmode IN VARCHAR2
410 , resultout OUT NOCOPY VARCHAR2
411 );
412
413 /*----------------------------------------
414
415 public PROCEDURE MarkSuccess
416
417 Workflow activity function. Mark the success of this workflow process
418 by pushing "success" message.
419
420 PARAMS:
421 WF Standard API.
422
423 RETURN:
424 WF Standard API.
425
426 ----------------------------------------*/
427
428 PROCEDURE MarkSuccess(
429 itemtype IN VARCHAR2
430 , itemkey IN VARCHAR2
431 , actid IN NUMBER
432 , funcmode IN VARCHAR2
433 , resultout OUT NOCOPY VARCHAR2
434 );
435
436
437 /*----------------------------------------
438
439 PROCEDURE check_isp_resp_sec_attr
440
441 This procedure will create iSP securing attributes
442 (ICX_SUPPLIER_ORG_ID, ICX_SUPPLIER_SITE_ID, ICX_SUPPLIER_CONTACT_ID)
443 for the responsibility, if they are not defined. It will also
444 set the default value (-9999) for the securing attributes
445 ICX_SUPPLIER_SITE_ID and ICX_SUPPLIER_CONTACT_ID for the responsibility,
446 if they are not defined.
447
448 PARAM:
449 p_resp_id IN NUMBER - responsibility id
450 p_resp_appl_id IN NUMBER - responsibility application id
451
452 EXCEPTION:
453
454 Raise exceptions.
455
456 ----------------------------------------*/
457
458 PROCEDURE check_isp_resp_sec_attr
459 (p_resp_id IN NUMBER,
460 p_resp_appl_id IN NUMBER);
461
462
463 /*----------------------------------------
464
465 private PROCEDURE check_isp_resp_sec_attr
466
467 Overload check_isp_resp_sec_attr(NUMBER, NUMBER) to take the responsibility
468 key.
469
470 PARAM:
471 p_resp_key IN VARCHAR2 - the responsibility key
472 p_resp_appl_id IN NUMBER - responsibility application id
473
474 EXCEPTION:
475
476 Raise exceptions.
477
478 ----------------------------------------*/
479
480 PROCEDURE check_isp_resp_sec_attr
481 (p_resp_key IN VARCHAR2,
482 p_resp_appl_id IN NUMBER);
483
484 /*----------------------------------------
485
486 PROCEDURE AssginResp
487
488 Assgin responsibility to user
489
490 PARAM:
491 p_user_id IN NUMBER - the FND_USER id of the assignee
492 p_resp_app_id IN NUMBER - the application id associated with the resp
493 p_resp_key IN VARCHAR2 - the responsibility key
494
495 EXCEPTION:
496 none. Do not raise exception.
497
498 ----------------------------------------*/
499 PROCEDURE AssginResp(
500 p_user_id IN NUMBER
501 , p_resp_app_id IN NUMBER
502 , p_resp_key IN VARCHAR2
503 );
504
505 /*----------------------------------------
506
507 PROCEDURE SetSecAttr
508
509 Set security atttribute to the user.
510
511 PARAM:
512 p_user_id IN NUMBER - the FND_USER id of the assignee
513 p_attribute_code IN VARCHAR2 - the security attribute code
517 p_date_value IN DATE - date value DEFAULT NULL
514 p_app_id IN NUMBER - the application id associated with the
515 security code
516 p_varchar2_value IN VARCHAR2 - the VARCHAR2 value DEFAULT NULL
518 p_number_value IN NUMBER - number value DEFAULT NULL
519
520 Note: one of the three values must be NOT NULL.
521
522 EXCEPTION:
523
524 Raise exceptions.
525
526 ----------------------------------------*/
527 PROCEDURE SetSecAttr(
528 p_user_id IN NUMBER
529 , p_attribute_code IN VARCHAR2
530 , p_app_id IN NUMBER
531 , p_varchar2_value IN VARCHAR2 DEFAULT NULL
532 , p_date_value IN DATE DEFAULT NULL
533 , p_number_value IN NUMBER DEFAULT NULL
534 );
535
536 FUNCTION isPasswordChangeable ( username in varchar2) return varchar2;
537
538 /*----------------------------------------
539 function set_initial_password
540 set the initial password for osn
541 registration request, so the user
542 doesn't need to change password at
543 first logon
544 ----------------------------------------*/
545 function set_initial_password(l_reg_id NUMBER) return varchar2;
546
547
548 /*----------------------------------------
549
550 public PROCEDURE IsOsnRequest
551
552 Workflow activity function. Check whether the registration is invited.
553
554 PARAMS:
555 WF Standard API.
556
557 RETURN:
558 COMPLETE:Y - if the registration request is from Oracle Supplier Network;
559 COMPLETE:N - otherwise
560
561 ----------------------------------------*/
562
563 PROCEDURE IsOsnRequest(
564 itemtype IN VARCHAR2
565 , itemkey IN VARCHAR2
566 , actid IN NUMBER
567 , funcmode IN VARCHAR2
568 , resultout OUT NOCOPY VARCHAR2
569 );
570
571
572 PROCEDURE set_profile_opt_ext_user
573 (p_userid in number);
574
575 END POS_REG_WF_PKG;