1 PACKAGE UMX_REG_REQUESTS_PVT AS
2 /* $Header: UMXVRRSS.pls 120.2 2006/09/11 18:35:53 kching noship $ */
3
4 --
5 -- RECORD: REG_REQUEST_TYPE
6 -- REG_REQUEST_ID : Registration Request ID
7 -- REG_SERVICE_TYPE : The code of the registration service type
8 -- code.
9 -- STATUS_CODE : Request Status Code (UMX_REQUEST_STATUS
10 -- FND Lookup)
11 -- REQUESTED_BY_USER_ID : The user id of the user who requested this
12 -- registration request.
13 -- REQUESTED_FOR_USER_ID : The user_id of the user who this registration
14 -- request is requested for.
15 -- REQUESTED_FOR_PARTY_ID : The Person Party ID of the person who this
16 -- request is requested for.
17 -- REQUESTED_USERNAME : The username requested for a user account
18 -- WF_ROLE_NAME : The name of the WF access role
19 -- REG_SERVICE_CODE : This is the Registration Service Code.
20 -- This is a null value ONLY when an access role
21 -- doesn't have the relationship with
22 -- registration service code.
23 -- AME_APPLICATION_ID : Application ID of the AME Transaction.
24 -- AME_TRANSACTION_TYPE_ID : AME's Transaction Type ID.
25 -- JUSTIFICATION : Justification
26 -- WF_EVENT_NAME : The event name of the notification event
27 -- EMAIL_VERIFICATION_FLAG : Flag for email verfication
28 --
29 TYPE REG_REQUEST_TYPE IS RECORD (
30 REG_REQUEST_ID UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE,
31 REG_SERVICE_TYPE UMX_REG_REQUESTS.REG_SERVICE_TYPE%TYPE,
32 STATUS_CODE UMX_REG_REQUESTS.STATUS_CODE%TYPE,
33 REQUESTED_BY_USERID UMX_REG_REQUESTS.REQUESTED_BY_USER_ID%TYPE DEFAULT NULL,
34 REQUESTED_FOR_USER_ID UMX_REG_REQUESTS.REQUESTED_FOR_USER_ID%TYPE DEFAULT NULL,
35 REQUESTED_FOR_PARTY_ID UMX_REG_REQUESTS.REQUESTED_FOR_PARTY_ID%TYPE DEFAULT NULL,
36 REQUESTED_USERNAME UMX_REG_REQUESTS.REQUESTED_USERNAME%TYPE DEFAULT NULL,
37 REQUESTED_START_DATE DATE DEFAULT SYSDATE,
38 REQUESTED_END_DATE DATE DEFAULT NULL,
39 WF_ROLE_NAME UMX_REG_REQUESTS.WF_ROLE_NAME%TYPE DEFAULT NULL,
40 REG_SERVICE_CODE UMX_REG_REQUESTS.REG_SERVICE_CODE%TYPE DEFAULT NULL,
41 AME_APPLICATION_ID UMX_REG_REQUESTS.AME_APPLICATION_ID%TYPE DEFAULT NULL,
42 AME_TRANSACTION_TYPE_ID UMX_REG_REQUESTS.AME_TRANSACTION_TYPE_ID%TYPE DEFAULT NULL,
43 JUSTIFICATION UMX_REG_REQUESTS.JUSTIFICATION%TYPE DEFAULT NULL,
44 WF_EVENT_NAME WF_EVENTS.NAME%TYPE DEFAULT NULL,
45 EMAIL_VERIFICATION_FLAG UMX_REG_SERVICES_B.EMAIL_VERIFICATION_FLAG%TYPE DEFAULT 'N'
46 );
47
48 --
49 -- Function : is_username_available
50 -- Type : PRIVATE
51 -- Pre_reqs : None
52 -- Description : It will query if username is being used in
53 -- FND_USER table.
54 -- input parameters :
55 -- @param p_username
56 -- Description: username to perform the check
57 -- Required : Y
58 -- output :
59 -- Description : It will output boolean value of true or false.
60 -- true - username is available
61 -- false - username is not available
62 --
63 function is_username_available (
64 p_username in FND_USER.USER_NAME%TYPE
65 ) return boolean;
66
67 --
68 -- Procedure : update_reg_request
69 -- Type : Private
70 -- Pre_reqs : None
71 -- Description : This API will create a registration request
72 -- into the UMX_REG_REQUESTS table.
73 -- Before registration request can be inserted into
74 -- UMX_REG_REQUESTS table, this API will check to see
75 -- if the requester already have a valid association to
76 -- this access role in wf_local_user_role. This API will
77 -- return null if there is a valid access role.
78 -- Input Parameters (Mandatory):
79 -- p_reg_request.reg_type_code: The code of the registration service type
80 -- code.
81 --
82 -- At least one of the below parameter needs to be passed in as an input
83 -- parameter:
84 -- p_reg_request.wf_role_name: The user_id of the user who this
85 -- registration request is requested for.
86 -- p_reg_request.reg_service_code: The Person Party ID of the person who
87 -- this request is requested for.
88 -- Input Parameters (non-Mandatory):
89 -- p_extra_check: Check if user already has an association with the role.
90 -- Output Parameters:
91 -- x_reg_request_id: Registration Request ID
92 --
93 procedure update_reg_request (
94 p_reg_request in out NOCOPY REG_REQUEST_TYPE
95 );
96
97 --
98 -- Procedure : delete_reg_request
99 -- Type : Private
100 -- Pre_reqs : None
101 -- Description : This API will delete a registration request
102 -- into the UMX_REG_REQUESTS table.
103 -- Before registration request can be inserted into
104 -- UMX_REG_REQUESTS table, this API will check to see
105 -- if the requester already have a valid association to
106 -- this access role in wf_local_user_role. This API will
107 -- return null if there is a valid access role.
108 -- Input Parameters (Mandatory):
109 -- p_reg_request.reg_type_code: The code of the registration service type
110 -- code.
111 --
112 -- At least one of the below parameter needs to be passed in as an input
113 -- parameter:
114 -- p_reg_request.wf_role_name: The user_id of the user who this
115 -- registration request is requested for.
116 -- p_reg_request.reg_service_code: The Person Party ID of the person who
117 -- this request is requested for.
118 -- Input Parameters (non-Mandatory):
119 -- p_extra_check: Check if user already has an association with the role.
120 -- Output Parameters:
121 -- x_reg_request_id: Registration Request ID
122 --
123 procedure delete_reg_request (
124 p_reg_request_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE
125 );
126
127 --
128 -- Function : reserve_username
129 -- Type : Private
130 -- Pre_reqs : None
131 -- Description : This API will ...
132 -- 1) Call fnd_user_pkg.reserve_username API to reserve
133 -- the requested username.
134 -- 2) Update the UMX_REG_REQUESTS table with the
135 -- requested for username and requested by username
136 -- (if requested by is null).
137 --
138 -- This API should be called when user requests a user
139 -- account
140 --
141 -- Input Parameters (Mandatory):
142 -- p_reg_request_id : Registration Request ID
143 -- p_username : username to be reserved
144 -- p_owner : 'SEED', 'CUST' (customer) or NULL
145 -- (fnd_global.user_id)
146 -- p_unencrypted_password : Unencrypted password
147 -- Output Parameters:
148 -- Description : It will either return the user ID if the username is
149 -- successfully reserved or null if otherwise.
150 --
151 function reserve_username (
152 p_reg_request_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE,
153 p_username in FND_USER.USER_NAME%TYPE,
154 p_owner in varchar2 default null,
155 p_unencrypted_password in varchar2,
156 p_session_number in number default 0,
157 p_last_logon_date in date default null,
158 p_description in varchar2 default null,
159 p_password_date in date default null,
160 p_password_accesses_left in number default null,
161 p_password_lifespan_accesses in number default null,
162 p_password_lifespan_days in number default null,
163 p_email_address in FND_USER.EMAIL_ADDRESS%TYPE default null,
164 p_fax in varchar2 default null,
165 p_person_party_id in FND_USER.PERSON_PARTY_ID%TYPE default null
166 ) return fnd_user.user_id%type;
167
168 --
169 -- Procedure : approve_username_reg_request
170 -- Type : Private
171 -- Pre_reqs : None
172 -- Description : This API will ...
173 -- 1) Activiate the user account by calling
174 -- fnd_user_pkg.activate_user_account.
175 -- 2) Set the status code to "APPROVED" in
176 -- UMX_REG_REQUESTS table.
177 --
178 -- This API should be called from Self-Service Registration or
179 -- Admin Creation.
180 -- Input Parameters :
181 -- @param p_reg_request_id
182 -- Description : ID for the registration request
183 -- Required : Yes
184 -- @param p_user_id
185 -- Description : ID of the user account
186 -- Required : Yes
187 -- @param p_start_date
188 -- Description : Starting active date of the user account.
189 -- Required : No
190 -- @param p_end_date
191 -- Description : Inactive date of the user account.
192 -- Required : No
193 -- @param p_person_party_id
194 -- Description : The person party ID of the user account.
195 -- Required : No
196
197 -- Output :
198 -- None
199 -- Description :
200 --
201 Procedure approve_username_reg_request (
202 p_reg_request_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE,
203 p_username in FND_USER.USER_NAME%TYPE,
204 p_person_party_id in FND_USER.PERSON_PARTY_ID%TYPE,
205 p_start_date in FND_USER.START_DATE%TYPE default sysdate,
206 p_end_date in FND_USER.END_DATE%TYPE default null
207 );
208
209 --
210 -- Procedure : reject_username_reg_request
211 -- Type : Private
212 -- Pre_reqs : None
213 -- Description : This API will call reject_cancel_username_reg_req
214 -- with status code = "REJECTED".
215 --
216 -- This API should be called from Self-Service
217 -- Registration or Admin Creation.
218 -- Input Parameters :
219 -- @param p_reg_request_id
220 -- Description : ID for the registration request
221 -- Required : Yes
222 -- @param p_user_id
223 -- Description : User ID of the user account
224 -- Required : Yes
225 -- @param p_username
226 -- Description : Username of the account
227 -- Required : Yes
228 -- Output :
229 -- None
230 -- Description :
231 --
232 Procedure reject_username_reg_request (
233 p_reg_request_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE,
234 p_user_id in FND_USER.USER_ID%TYPE,
235 p_username in FND_USER.USER_NAME%TYPE
236 );
237
238 --
239 -- Procedure : cancel_username_reg_request
240 -- Type : Private
241 -- Pre_reqs : None
242 -- Description : This API will call reject_cancel_username_reg_req API
243 -- with status_code = 'CANCELLED'.
244 --
245 -- This API should be called from Self-Service Registration or
246 -- Admin Creation.
247 -- Input Parameters :
248 -- @param p_reg_request_id
249 -- Description : ID for the registration request
250 -- Required : Yes
251 -- @param p_user_id
252 -- Description : ID of the user account
253 -- Required : Yes
254 -- @param p_username
255 -- Description : Username of the account
256 -- Required : Yes
257 -- Output :
258 -- None
259 -- Description :
260 --
261 Procedure cancel_username_reg_request (
262 p_reg_request_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE,
263 p_user_id in FND_USER.USER_ID%TYPE,
264 p_username in FND_USER.USER_NAME%TYPE
265 );
266
267 --
268 -- Procedure : approve_reg_request
269 -- Type : Private
270 -- Pre_reqs : None
271 -- Description : This API will approve_reject_reg_request to approve
272 -- the Reg Request in UMX_REG_REQUESTS table.
273 --
274 -- This API should be called from ART or SMART.
275 -- Input Parameters :
276 -- @param p_reg_request_id
277 -- Description : ID for the registration request
278 -- Required : Yes
279 -- Output :
280 -- None
281 -- Description :
282 --
283 Procedure approve_reg_request (
284 p_reg_request_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE
285 );
286
287 --
288 -- Procedure : reject_reg_request
289 -- Type : Private
290 -- Pre_reqs : None
291 -- Description : This API will approve_reject_reg_request to reject
292 -- the Reg Request in UMX_REG_REQUESTS table.
293 --
294 -- This API should be called from ART or SMART.
295 -- Input Parameters :
296 -- @param p_reg_request_id
297 -- Description : ID for the registration request
298 -- Required : Yes
299 -- Output :
300 -- None
301 -- Description :
302 --
303 Procedure reject_reg_request (
304 p_reg_request_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE
305 );
306
307 --
308 -- Procedure : get_pend_acct_info_with_userid
309 -- Type : Private
310 -- Pre_reqs : None
311 -- Description : This API will return the current approver's username
312 -- by calling ame_api.getNextApprover and email address.
313 -- Active from and Active to from UMX's Workflow
314 -- Input Parameters :
315 -- @param p_requester_user_id
316 -- Description : Requester user ID
317 -- Required : Yes
318 -- Output :
319 -- x_reg_request_id
320 -- Description: Reg Request ID
321 -- x_requested_for_username
322 -- Description: Requested for Username
323 -- x_approver_name
324 -- Description: Name of the current approver
325 -- x_approver_email_address
326 -- Description: Email address of the current approver
327 -- x_status_code
328 -- Description: Status code of the request
329 -- x_active_from
330 -- Description: The string version of the user account's start date.
331 -- If the start date is before the sysdate, then it will
332 -- return "Date of approval".
333 -- x_active_to
334 -- Description: The string version of the user account's end date .
335 -- If the end date is null or x_active_to is "Date of approval",
336 -- then it will return null.
337 -- x_justification
338 -- Description: Justification
339 --
340 Procedure get_pend_acct_info_with_userid (
341 p_requester_user_id in FND_USER.USER_ID%TYPE,
342 x_reg_request_id out NOCOPY UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE,
343 x_requested_for_username out NOCOPY FND_USER.USER_NAME%TYPE,
344 x_approver_name out NOCOPY varchar2,
345 x_approver_email_address out NOCOPY FND_USER.EMAIL_ADDRESS%TYPE,
346 x_status_code out NOCOPY UMX_REG_REQUESTS.STATUS_CODE%TYPE,
347 x_active_from out NOCOPY varchar2,
348 x_active_to out NOCOPY varchar2,
349 x_justification out NOCOPY UMX_REG_REQUESTS.JUSTIFICATION%TYPE);
350
351 --
352 -- Procedure : get_pend_acct_info_with_reqid
353 -- Type : Private
357 -- Input Parameters :
354 -- Pre_reqs : None
355 -- Description : This API will return the current approver's username
356 -- by calling ame_api.getNextApprover and email address.
358 -- @param x_reg_request_id
359 -- Description : Reg Request ID
360 -- Required : Yes
361 -- Output :
362 -- x_approver_name
363 -- Description: Name of the current approver
364 -- x_approver_email_address
365 -- Description: Email address of the current approver
366 -- x_status_code
367 -- Description: Status code of the request
368 -- x_active_from
369 -- Description: The string version of the user account's start date.
370 -- If the start date is before the sysdate, then it will
371 -- return "Date of approval".
372 -- x_active_to
373 -- Description: The string version of the user account's end date .
374 -- If the end date is null or x_active_to is "Date of approval",
375 -- then it will return null.
376 -- x_justification
377 -- Description: Justification
378 --
379 Procedure get_pend_acct_info_with_reqid (
380 p_reg_request_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE,
381 x_approver_name out NOCOPY varchar2,
382 x_approver_email_address out NOCOPY FND_USER.EMAIL_ADDRESS%TYPE,
383 x_status_code out NOCOPY UMX_REG_REQUESTS.STATUS_CODE%TYPE,
384 x_active_from out NOCOPY varchar2,
385 x_active_to out NOCOPY varchar2,
386 x_justification out NOCOPY UMX_REG_REQUESTS.JUSTIFICATION%TYPE);
387
388 --
389 -- Procedure : get_error_wf_info
390 -- Type : Private
391 -- Pre_reqs : None
392 -- Description : This API will call wf_engine.iteminfo to get the status
393 -- of the main UMX Workflow and all its event subscribers.
394 -- Input Parameters (Mandatory):
395 -- p_reg_request_id: Registration Request ID
396 --
397 -- Output Parameters:
398 -- x_itemtype: Workflow's Item Type
399 -- x_itemkey: Workflow's Item Key
400 -- x_status: Workflow's Status
401 -- x_result: Result
402 -- x_actid: Activity ID
403 -- x_errname: Error Name
404 -- x_errmsg: Error Message
405 -- x_errstack: Error Stack
406 --
407 --
408 procedure get_error_wf_info (p_reg_request_id in wf_items.item_type%type,
409 x_itemtype out nocopy wf_items.item_type%type,
410 x_itemkey out nocopy wf_items.item_key%type,
411 x_status out nocopy varchar2,
412 x_result out nocopy varchar2,
413 x_actid out nocopy number,
414 x_errname out nocopy varchar2,
415 x_errmsg out nocopy varchar2,
416 x_errstack out nocopy varchar2);
417
418 --
419 -- Procedure : get_error_wf_info
420 -- Type : Private
421 -- Pre_reqs : None
422 -- Description : This API will call wf_engine.iteminfo to get the status
423 -- of the main UMX Workflow and all its event subscribers.
424 -- Input Parameters (Mandatory):
425 -- p_user_id: User ID of the requester
426 --
427 -- Output Parameters:
428 -- x_itemtype: Workflow's Item Type
429 -- x_itemkey: Workflow's Item Key
430 -- x_status: Workflow's Status
431 -- x_result: Result
432 -- x_actid: Activity ID
433 -- x_errname: Error Name
434 -- x_errmsg: Error Message
435 -- x_errstack: Error Stack
436 --
437 --
438 procedure get_error_wf_info (p_user_id in fnd_user.user_id%type,
439 x_itemtype out nocopy wf_items.item_type%type,
440 x_itemkey out nocopy wf_items.item_key%type,
441 x_status out nocopy varchar2,
442 x_result out nocopy varchar2,
443 x_actid out nocopy number,
444 x_errname out nocopy varchar2,
445 x_errmsg out nocopy varchar2,
446 x_errstack out nocopy varchar2);
447
448 --
449 -- Function : is_pend_request_error
450 -- Type : Private
451 -- Pre_reqs : None
452 -- Description : This API will call wf_engine.iteminfo to get the status
453 -- of the main UMX Workflow and all its event subscribers.
454 -- It will return 'Y' if account is in error stage and 'N' if otherwise.
455 -- Input Parameters (Mandatory):
456 -- p_reg_request_id: Registration Request ID
457 --
458 -- Output Parameter:
459 -- It will return 'Y' if pending account has error and 'N' if otherwise.
460 --
461 function is_pend_request_error (p_reg_request_id in umx_reg_requests.reg_request_id%type) return varchar2;
462
463 --
464 -- Function : is_pend_account_error
465 -- Type : Private
466 -- Pre_reqs : None
467 -- Description : This API will call wf_engine.iteminfo to get the status
471 -- p_user_id: User ID of the requester
468 -- of the main UMX Workflow and all its event subscribers.
469 -- It will return 'Y' if account is in error stage and 'N' if otherwise.
470 -- Input Parameters (Mandatory):
472 --
473 -- Output Parameter:
474 -- It will return 'Y' if pending account has error and 'N' if otherwise.
475 --
476 function is_pend_account_error (p_user_id in fnd_user.user_id%type) return varchar2;
477
478 -- Function
479 -- getNextApproverPvt
480 --
481 -- Description
482 -- Private API that will call the ame_api2.GetNextApprover API
483 -- IN
484 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
485 -- itemkey - A string generated from the application object's primary key.
486 -- OUT
487 -- l_next_approver - result of the process based on which the next step is followed
488 function getNextApproverPvt (p_ame_application_id in varchar2,
489 p_ame_transaction_type_id in varchar2,
490 p_reg_request_id in varchar2) return ame_util.approverRecord2;
491 END UMX_REG_REQUESTS_PVT;
[Home] [Help]