[Home] [Help]
PACKAGE BODY: APPS.UMX_REGISTRATION_PVT
Source
1 PACKAGE BODY UMX_REGISTRATION_PVT AS
2 /* $Header: UMXVREGB.pls 120.6.12000000.2 2007/04/10 04:56:16 vimohan ship $ */
3
4 PROCEDURE populateRegData(p_registration_data IN OUT NOCOPY UMX_REGISTRATION_DATA_TBL,
5 p_reg_request_id IN NUMBER) IS
6
7 CURSOR FIND_REG_DATA IS
8 SELECT REG.REQUESTED_FOR_USER_ID, FU.USER_NAME, FU.PERSON_PARTY_ID,
9 REG.STATUS_CODE
10 FROM FND_USER FU, UMX_REG_REQUESTS REG
11 WHERE REG.REG_REQUEST_ID = p_reg_request_id
12 AND REG.REQUESTED_FOR_USER_ID = FU.USER_ID;
13
14 l_user_id UMX_REG_REQUESTS.REQUESTED_FOR_USER_ID%TYPE;
15 l_user_name FND_USER.USER_NAME%TYPE;
16 l_party_id FND_USER.PERSON_PARTY_ID%TYPE;
17 l_status_code UMX_REG_REQUESTS.STATUS_CODE%TYPE;
18 j NUMBER;
19 l_index_to_add NUMBER;
20
21 BEGIN
22
23 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
24 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
25 'fnd.plsql.UMXVREGB.populateRegData.begin',
26 'regRequestId: ' || p_reg_request_id);
27 end if;
28
29 OPEN FIND_REG_DATA;
30 FETCH FIND_REG_DATA INTO l_user_id, l_user_name, l_party_id, l_status_code;
31 CLOSE FIND_REG_DATA;
32
33 j := p_registration_data.FIRST;
34
35 while (j <= p_registration_data.LAST) loop
36
37 if p_registration_data(j).ATTR_NAME = 'requested_for_user_id' then
38 p_registration_data(j).ATTR_VALUE := l_user_id;
39 l_user_id := null;
40 end if;
41
42 if p_registration_data(j).ATTR_NAME = 'requested_username' then
43 p_registration_data(j).ATTR_VALUE := l_user_name;
44 l_user_name := null;
45 end if;
46
47 j := j + 1;
48
49 end loop;
50
51 l_index_to_add := p_registration_data.last;
52
53 if l_user_id is not null then
54 l_index_to_add := l_index_to_add + 1;
55 p_registration_data(l_index_to_add).ATTR_NAME := 'requested_for_user_id';
56 p_registration_data(l_index_to_add).ATTR_VALUE := l_user_id;
57 end if;
58
59 if l_user_name is not null then
60 l_index_to_add := l_index_to_add + 1;
61 p_registration_data(l_index_to_add).ATTR_NAME := 'requested_username';
62 p_registration_data(l_index_to_add).ATTR_VALUE := l_user_name;
63 end if;
64
65 l_index_to_add := l_index_to_add + 1;
66 p_registration_data(l_index_to_add).ATTR_NAME := 'person_party_id';
67 p_registration_data(l_index_to_add).ATTR_VALUE := l_party_id;
68
69 l_index_to_add := l_index_to_add + 1;
70 p_registration_data(l_index_to_add).ATTR_NAME := 'status_code';
71 p_registration_data(l_index_to_add).ATTR_VALUE := l_status_code;
72
73 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
74 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE, 'fnd.plsql.UMXVREGB.populateRegData.end', '');
75 end if;
76
77 END populateRegData;
78
79 PROCEDURE DO_PROCESS_REQUEST(p_registration_data IN OUT NOCOPY UMX_REGISTRATION_DATA_TBL) IS
80
81 l_parameter_list wf_parameter_list_t;
82 j number;
83 l_item_key varchar2(2000);
84 l_requested_by_user_id varchar2(2000);
85 l_event_name WF_EVENTS_VL.NAME%type;
86 l_reg_service_type UMX_REG_SERVICES_B.REG_SERVICE_TYPE%type;
87 l_index_to_add number;
88
89 l_status varchar2(8);
90 l_result varchar2(30);
91 l_errname varchar2(30);
92 l_errmsg varchar2(2000);
93 l_errstack varchar2(4000);
94 l_actid number;
95
96 BEGIN
97
98 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
99 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
100 'fnd.plsql.UMXVREGB.doProcessRequest.begin', '');
101 end if;
102
103 POPULATE_REG_DATA (p_registration_data => p_registration_data);
104
105 j := p_registration_data.FIRST;
106
107 while (j <= p_registration_data.LAST) loop
108 -- Add parameter to the event object only if
109 -- the attribute name is not null
110 if p_registration_data(j).ATTR_NAME is not null then
111 wf_event.addParametertoList(UPPER(p_registration_data(j).ATTR_NAME), p_registration_data(j).ATTR_VALUE,l_parameter_list);
112 end if;
113 --insert into chirag_test values(p_registration_data(j).ATTR_NAME, p_registration_data(j).ATTR_VALUE);
114 if p_registration_data(j).ATTR_NAME = 'reg_service_type' then
115 l_reg_service_type := p_registration_data(j).ATTR_VALUE;
116 end if;
117
118 if p_registration_data(j).ATTR_NAME = 'reg_request_id' then
119 l_item_key := p_registration_data(j).ATTR_VALUE;
120 end if;
121
122 if p_registration_data(j).ATTR_NAME = 'requested_by_user_id' then
123 l_requested_by_user_id := p_registration_data(j).ATTR_VALUE;
124 end if;
125
126 j := j + 1;
127 end loop;
128
129 if (l_reg_service_type = 'ADDITIONAL_ACCESS') or
130 (l_reg_service_type = 'ADMIN_ADDITIONAL_ACCESS') then
131 l_event_name := 'oracle.apps.fnd.umx.startaccessrequestwf';
132 else
133 l_event_name := 'oracle.apps.fnd.umx.startaccountrequestwf';
134 end if;
135
136 if l_item_key is null then
137 select UMX_REG_REQUESTS_S.nextval into l_item_key from dual;
138 l_index_to_add := p_registration_data.last + 1;
139 p_registration_data(l_index_to_add).ATTR_NAME := 'reg_request_id';
140 p_registration_data(l_index_to_add).ATTR_VALUE := l_item_key;
141 end if;
142
143 --if l_requested_by_user_id is null then
144 wf_event.addParametertoList('REQUESTED_BY_USER_ID', FND_GLOBAL.USER_ID, l_parameter_list);
145 --end if;
146
147 wf_event.addParametertoList('UMX_PARENT_ITEM_TYPE', UMX_REGISTRATION_UTIL.G_ITEM_TYPE, l_parameter_list);
148 wf_event.addParametertoList('UMX_PARENT_ITEM_KEY', l_item_key, l_parameter_list);
149 wf_event.raise(l_event_name,l_item_key,null,l_parameter_list,sysdate);
150
151 wf_engine.iteminfo (itemtype => 'UMXREGWF',
152 itemkey => l_item_key,
153 status => l_status,
154 result => l_result,
155 actid => l_actid,
156 errmsg => l_errmsg,
157 errname => l_errname,
158 errstack => l_errstack);
159
160 if (l_status = 'ERROR') then
161 raise_application_error ('-20000',l_errmsg);
162 else
163 populateRegData(p_registration_data, l_item_key);
164 end if;
165
166 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
167 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
168 'fnd.plsql.UMXVREGB.doProcessRequest.end', '');
169 end if;
170
171 END do_process_request;
172
173 /** Procedure : UMX_PROCESS_REG_REQUEST
174 * Type : Private
175 * Pre_reqs : None
176 * Description : Invokes Workflow process after registration flow
177 * This API will return an error if the size of the
178 * that WF can accept to raise an event
179 * Parameters :
180 * input parameters
181 * @param p_registration_data
182 * description: This is of type UMX_REGISTRATION_PVT.UMX_REGISTRATION_DATA
183 * required : Y
184 * validation : None
185 */
186 PROCEDURE UMX_PROCESS_REG_REQUEST (p_registration_data IN OUT NOCOPY UMX_REGISTRATION_DATA_TBL) IS
187
188 BEGIN
189
190 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
191 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE, 'fnd.plsql.UMXVREGB.umxProcessRegRequest.begin', '');
192 end if;
193
194 DO_PROCESS_REQUEST(p_registration_data);
195
196 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
197 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE, 'fnd.plsql.UMXVREGB.umxProcessRegRequest.end', '');
198 end if;
199
200 END UMX_PROCESS_REG_REQUEST;
201
202 function format_address_lov(p_party_id number) return varchar2 IS
203
204 cursor get_address_info is
205 select ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, CITY,
206 POSTAL_CODE, PROVINCE, STATE, COUNTY, COUNTRY
207 from hz_parties
208 where party_id = p_party_id;
209
210 l_address1 HZ_PARTIES.ADDRESS1%TYPE;
211 l_address2 HZ_PARTIES.ADDRESS2%TYPE;
212 l_address3 HZ_PARTIES.ADDRESS3%TYPE;
213 l_address4 HZ_PARTIES.ADDRESS4%TYPE;
214 l_city HZ_PARTIES.CITY%TYPE;
215 l_postal_code HZ_PARTIES.POSTAL_CODE%TYPE;
216 l_province HZ_PARTIES.PROVINCE%TYPE;
217 l_state HZ_PARTIES.STATE%TYPE;
218 l_county HZ_PARTIES.COUNTY%TYPE;
219 l_country HZ_PARTIES.COUNTRY%TYPE;
220
221 l_return_status VARCHAR2(1);
222 l_msg_count NUMBER;
223 l_msg_data NUMBER;
224 l_formatted_address VARCHAR2(360);
225
226 l_tbl_cnt NUMBER;
227 l_tbl hz_format_pub.string_tbl_type;
228
229 begin
230
231 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
232 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
233 'fnd.plsql.UMXVREGB.formatAddressLov.begin',
234 'partyId: ' || p_party_id);
235 end if;
236
237 open get_address_info;
238 fetch get_address_info into l_address1, l_address2, l_address3, l_address4,
239 l_city, l_postal_code, l_province, l_state,
240 l_county, l_country;
241 close get_address_info;
242
243 IF l_country IS NULL THEN
244 RETURN NULL;
245 END IF;
246
247 hz_format_pub.format_address (
248 p_style_code => 'POSTAL_ADDR',
249 p_line_break => ', ',
250 p_space_replace => ' ',
251 p_address_line_1 => l_address1,
252 p_address_line_2 => l_address2,
253 p_address_line_3 => l_address3,
254 p_address_line_4 => l_address4,
255 p_city => l_city,
256 p_postal_code => l_postal_code,
257 p_state => l_state,
258 p_province => l_province,
259 p_county => l_county,
260 p_country => l_country,
261 p_address_lines_phonetic => null,
262 -- output parameters
263 x_return_status => l_return_status,
264 x_msg_count => l_msg_count,
265 x_msg_data => l_msg_data,
266 x_formatted_address => l_formatted_address,
267 x_formatted_lines_cnt => l_tbl_cnt,
268 x_formatted_address_tbl => l_tbl);
269
270 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
271 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
272 'fnd.plsql.UMXVREGB.formatAddressLov.end',
273 'formattedAddress: ' || l_formatted_address);
274 end if;
275
276 RETURN l_formatted_address;
277
278 EXCEPTION
279 WHEN OTHERS THEN
280 RETURN l_formatted_address;
281
282 end format_address_lov;
283
284
285 function get_event_name (p_event_guid WF_EVENTS.guid%type) return wf_events.name%type is
286
287 CURSOR get_event_name_cursor is
288 select name
289 from wf_events
290 where guid = HEXTORAW(p_event_guid);
291
292 x_event_name wf_events.name%type;
293
294 begin
295 open get_event_name_cursor;
296 fetch get_event_name_cursor into x_event_name;
297 close get_event_name_cursor;
298
299 return x_event_name;
300 end get_event_name;
301
302
303 procedure get_req_data_from_req_id (
304 p_reg_request_id in UMX_REG_REQUESTS.REG_REQUEST_ID%type,
305 p_reg_service_code in UMX_REG_SERVICES_B.REG_SERVICE_CODE%type,
306 x_reg_service_type out nocopy UMX_REG_SERVICES_VL.REG_SERVICE_TYPE%type,
307 x_requested_by_user_id out nocopy UMX_REG_REQUESTS.REQUESTED_BY_USER_ID%type,
308 x_requested_for_user_id out nocopy UMX_REG_REQUESTS.REQUESTED_FOR_USER_ID%type,
309 x_requested_for_party_id out nocopy UMX_REG_REQUESTS.REQUESTED_FOR_PARTY_ID%type,
310 x_requested_username out nocopy UMX_REG_REQUESTS.REQUESTED_USERNAME%type,
311 x_wf_role_name out nocopy UMX_REG_SERVICES_VL.WF_ROLE_NAME%type,
312 x_ame_application_id out nocopy UMX_REG_SERVICES_VL.AME_APPLICATION_ID%type,
313 x_ame_transaction_type_id out nocopy UMX_REG_SERVICES_VL.AME_TRANSACTION_TYPE_ID%type,
314 x_justification out nocopy UMX_REG_REQUESTS.JUSTIFICATION%type,
315 x_wf_notification_event_name out nocopy WF_EVENTS.NAME%type,
316 x_email_verification_flag out nocopy UMX_REG_SERVICES_VL.EMAIL_VERIFICATION_FLAG%type,
317 x_application_id out nocopy UMX_REG_SERVICES_VL.APPLICATION_ID%type,
318 x_reg_function_name out nocopy FND_FORM_FUNCTIONS.FUNCTION_NAME%type,
319 x_display_name out nocopy UMX_REG_SERVICES_VL.DISPLAY_NAME%type,
320 x_description out nocopy UMX_REG_SERVICES_VL.DESCRIPTION%type,
321 x_wf_bus_logic_event_name out nocopy WF_EVENTS.NAME%type) is
322
323 CURSOR get_request_data is
324 SELECT regreq.WF_ROLE_NAME, regreq.REG_SERVICE_TYPE, regser.APPLICATION_ID,
325 regreq.AME_APPLICATION_ID, regreq.AME_TRANSACTION_TYPE_ID,
326 regser.EMAIL_VERIFICATION_FLAG, func.FUNCTION_NAME, regser.DISPLAY_NAME,
327 regser.DESCRIPTION, regreq.JUSTIFICATION, regreq.REQUESTED_BY_USER_ID,
328 regreq.REQUESTED_FOR_USER_ID, regreq.REQUESTED_FOR_PARTY_ID,
329 regreq.REQUESTED_USERNAME, regser.wf_notification_event_guid,
330 regser.wf_bus_logic_event_guid
331 FROM UMX_REG_SERVICES_VL regser, fnd_form_functions func,
332 UMX_REG_REQUESTS regreq
333 WHERE regser.reg_function_id = func.function_id (+)
334 AND regreq.REG_SERVICE_CODE = regser.REG_SERVICE_CODE
335 AND regreq.reg_request_id = p_reg_request_id;
336
337 l_wf_notification_event_guid wf_events.guid%type;
338 l_wf_bus_logic_event_guid wf_events.guid%type;
339
340 begin
341
342 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
343 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
344 'fnd.plsql.UMXVREGB.getReqDataFromReqId.begin',
345 'regRequestId: ' || p_reg_request_id ||
346 ' | regServiceCode: ' || p_reg_service_code);
347 end if;
348
349 open get_request_data;
350 fetch get_request_data into x_wf_role_name, x_reg_service_type, x_application_id,
351 x_ame_application_id, x_ame_transaction_type_id,
352 x_email_verification_flag, x_reg_function_name,
353 x_display_name, x_description, x_justification,
354 x_requested_by_user_id, x_requested_for_user_id,
355 x_requested_for_party_id, x_requested_username,
356 l_wf_notification_event_guid, l_wf_bus_logic_event_guid;
357 close get_request_data;
358
359 x_wf_notification_event_name := get_event_name (l_wf_notification_event_guid);
360 x_wf_bus_logic_event_name := get_event_name (l_wf_bus_logic_event_guid);
361
362 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
363 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
364 'fnd.plsql.UMXVREGB.getReqDataFromReqId.end',
365 'regServiceType: ' || x_reg_service_type ||
366 ' | requestedByUserId: ' || x_requested_by_user_id ||
367 ' | requestedForUserId: ' || x_requested_for_user_id ||
368 ' | requestedForPartyId: ' || x_requested_for_party_id ||
369 ' | requestedUsername: ' || x_requested_username ||
370 ' | wfRoleName: ' || x_wf_role_name ||
371 ' | ameApplicationId: ' || x_ame_application_id ||
372 ' | ameTransactionTypeId: ' || x_ame_transaction_type_id ||
373 ' | justification: ' || x_justification ||
374 ' | wfNotificationEventName: ' || x_wf_notification_event_name ||
375 ' | emailVerificationFlag: ' || x_email_verification_flag ||
376 ' | applicationId: ' || x_application_id ||
377 ' | regFunctionName: ' || x_reg_function_name ||
378 ' | displayName: ' || x_display_name ||
379 ' | description: ' || x_description ||
380 ' | wfBusLogicEventName: ' || x_wf_bus_logic_event_name);
381 end if;
382
383 end get_req_data_from_req_id;
384
385
386 procedure get_req_data_from_req_sv_code (
387 p_reg_service_code in UMX_REG_SERVICES_B.REG_SERVICE_CODE%type,
388 x_reg_service_type out nocopy UMX_REG_SERVICES_VL.REG_SERVICE_TYPE%type,
389 x_wf_role_name out nocopy UMX_REG_SERVICES_VL.WF_ROLE_NAME%type,
390 x_ame_application_id out nocopy UMX_REG_SERVICES_VL.AME_APPLICATION_ID%type,
391 x_ame_transaction_type_id out nocopy UMX_REG_SERVICES_VL.AME_TRANSACTION_TYPE_ID%type,
392 x_wf_notification_event_name out nocopy WF_EVENTS.NAME%type,
393 x_email_verification_flag out nocopy UMX_REG_SERVICES_VL.EMAIL_VERIFICATION_FLAG%type,
394 x_application_id out nocopy UMX_REG_SERVICES_VL.APPLICATION_ID%type,
395 x_reg_function_name out nocopy FND_FORM_FUNCTIONS.FUNCTION_NAME%type,
396 x_display_name out nocopy UMX_REG_SERVICES_VL.DISPLAY_NAME%type,
397 x_description out nocopy UMX_REG_SERVICES_VL.DESCRIPTION%type,
398 x_wf_bus_logic_event_name out nocopy WF_EVENTS.NAME%type) is
399
400 CURSOR get_req_svc_data IS
401 SELECT regser.WF_ROLE_NAME, regser.REG_SERVICE_TYPE, regser.APPLICATION_ID,
402 regser.wf_notification_event_guid, regser.AME_APPLICATION_ID,
403 regser.AME_TRANSACTION_TYPE_ID, regser.EMAIL_VERIFICATION_FLAG,
404 func.FUNCTION_NAME, regser.DISPLAY_NAME, regser.DESCRIPTION,
405 regser.wf_bus_logic_event_guid
406 FROM UMX_REG_SERVICES_VL regser, fnd_form_functions func
407 WHERE regser.reg_function_id = func.function_id (+)
408 AND REG_SERVICE_CODE = p_reg_service_code;
409
410 l_wf_notification_event_guid wf_events.guid%type;
411 l_wf_bus_logic_event_guid wf_events.guid%type;
412
413 begin
414
415 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
416 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
417 'fnd.plsql.UMXVREGB.getReqDataFromReqSvCode.begin',
418 'regServiceCode: ' || p_reg_service_code);
419 end if;
420
421 open get_req_svc_data;
422 fetch get_req_svc_data into x_wf_role_name, x_reg_service_type, x_application_id,
423 l_wf_notification_event_guid, x_ame_application_id,
424 x_ame_transaction_type_id, x_email_verification_flag,
425 x_reg_function_name, x_display_name, x_description,
426 l_wf_bus_logic_event_guid;
427 close get_req_svc_data;
428
429 x_wf_notification_event_name := get_event_name (l_wf_notification_event_guid);
430 x_wf_bus_logic_event_name := get_event_name (l_wf_bus_logic_event_guid);
431
432 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
433 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
434 'fnd.plsql.UMXVREGB.getReqDataFromReqSvCode.end',
435 'regServiceType: ' || x_reg_service_type ||
436 ' | wfRoleName: ' || x_wf_role_name ||
437 ' | ameApplicationId: ' || x_ame_application_id ||
438 ' | ameTransactionTypeId: ' || x_ame_transaction_type_id ||
439 ' | wfNotificationEventName: ' || x_wf_notification_event_name ||
440 ' | emailVerificationFlag: ' || x_email_verification_flag ||
441 ' | applicationId: ' || x_application_id ||
442 ' | regFunctionName: ' || x_reg_function_name ||
443 ' | displayName: ' || x_display_name ||
444 ' | description: ' || x_description ||
445 ' | wfBusLogicEventName:' || x_wf_bus_logic_event_name);
446 end if;
447 end get_req_data_from_req_sv_code;
448
449 procedure get_req_data_from_role_name (
450 p_wf_role_name in UMX_REG_SERVICES_VL.WF_ROLE_NAME%type,
451 x_reg_service_code out nocopy UMX_REG_SERVICES_B.REG_SERVICE_CODE%type,
452 x_reg_service_type out nocopy UMX_REG_SERVICES_VL.REG_SERVICE_TYPE%type,
453 x_ame_application_id out nocopy UMX_REG_SERVICES_VL.AME_APPLICATION_ID%type,
454 x_ame_transaction_type_id out nocopy UMX_REG_SERVICES_VL.AME_TRANSACTION_TYPE_ID%type,
455 x_wf_notification_event_name out nocopy WF_EVENTS.NAME%type,
456 x_email_verification_flag out nocopy UMX_REG_SERVICES_VL.EMAIL_VERIFICATION_FLAG%type,
457 x_reg_function_name out nocopy FND_FORM_FUNCTIONS.FUNCTION_NAME%type,
458 x_wf_bus_logic_event_name out nocopy WF_EVENTS.NAME%type) is
459
460 CURSOR get_reg_svc_code_from_role (p_reg_serivce_type in varchar2) IS
461 select REG_SERVICE_TYPE, REG_SERVICE_CODE, AME_APPLICATION_ID,
462 AME_TRANSACTION_TYPE_ID, REG_FUNCTION_ID,
463 wf_notification_event_guid, EMAIL_VERIFICATION_FLAG,
464 wf_bus_logic_event_guid
465 from UMX_REG_SERVICES_B
466 where WF_ROLE_NAME = p_wf_role_name
467 and reg_service_type = p_reg_serivce_type;
468
469 l_wf_notification_event_guid wf_events.guid%type;
470 l_wf_bus_logic_event_guid wf_events.guid%type;
471
472 begin
473
474 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
475 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
476 'fnd.plsql.UMXVREGB.getReqDataFromRoleName.begin',
477 'wfRoleName: ' || p_wf_role_name);
478 end if;
479
480 open get_reg_svc_code_from_role ('ADMIN_ADDITIONAL_ACCESS');
481 fetch get_reg_svc_code_from_role into x_reg_service_type, x_reg_service_code, x_ame_application_id,
482 x_ame_transaction_type_id, x_reg_function_name,
483 l_wf_notification_event_guid, x_email_verification_flag,
484 l_wf_bus_logic_event_guid;
485 if (get_reg_svc_code_from_role%notfound) then
486 close get_reg_svc_code_from_role;
487 open get_reg_svc_code_from_role ('ADDITIONAL_ACCESS');
488 fetch get_reg_svc_code_from_role into x_reg_service_type, x_reg_service_code, x_ame_application_id,
489 x_ame_transaction_type_id, x_reg_function_name,
490 l_wf_notification_event_guid, x_email_verification_flag,
491 l_wf_bus_logic_event_guid;
492 end if;
493 close get_reg_svc_code_from_role;
494
495 x_wf_notification_event_name := get_event_name (l_wf_notification_event_guid);
496 x_wf_bus_logic_event_name := get_event_name (l_wf_bus_logic_event_guid);
497
498 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
499 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
500 'fnd.plsql.UMXVREGB.getReqDataFromRoleName.end',
501 'regServiceCode: ' || x_reg_service_code ||
502 ' | regServiceType: ' || x_reg_service_type ||
503 ' | ameApplicationId: ' || x_ame_application_id ||
504 ' | ameTransactionTypeId: ' || x_ame_transaction_type_id ||
505 ' | wfNotificationEventName: ' || x_wf_notification_event_name ||
506 ' | emailVerificationFlag: ' || x_email_verification_flag ||
507 ' | regFunctionName: ' || x_reg_function_name ||
508 ' | wfBusLogicEventName: ' || x_wf_bus_logic_event_name);
509 end if;
510
511 end get_req_data_from_role_name;
512
513 procedure POPULATE_REG_DATA (p_registration_data IN OUT NOCOPY UMX_REGISTRATION_DATA_TBL) IS
514 l_reg_request_id UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE;
515 l_reg_service_code UMX_REG_SERVICES_B.REG_SERVICE_CODE%TYPE;
516 j number;
517 l_wf_role_name UMX_REG_SERVICES_VL.WF_ROLE_NAME%TYPE;
518 l_reg_service_type UMX_REG_SERVICES_VL.REG_SERVICE_TYPE%TYPE;
519 l_application_id UMX_REG_SERVICES_VL.APPLICATION_ID%TYPE;
520 l_wf_notification_event_name wf_events.name%TYPE;
521 l_ame_application_id UMX_REG_SERVICES_VL.AME_APPLICATION_ID%TYPE;
522 l_ame_transaction_type_id UMX_REG_SERVICES_VL.AME_TRANSACTION_TYPE_ID%TYPE;
523 l_email_verification_flag UMX_REG_SERVICES_VL.EMAIL_VERIFICATION_FLAG%TYPE;
524 l_reg_function_name fnd_form_functions.FUNCTION_NAME%TYPE;
525 l_display_name UMX_REG_SERVICES_VL.DISPLAY_NAME%TYPE;
526 l_description UMX_REG_SERVICES_VL.DESCRIPTION%TYPE;
527 l_wf_bus_logic_event_name wf_events.name%TYPE;
528 l_justification UMX_REG_REQUESTS.JUSTIFICATION%TYPE;
529 l_requested_by_user_id UMX_REG_REQUESTS.REQUESTED_BY_USER_ID%TYPE;
530 l_requested_for_user_id UMX_REG_REQUESTS.REQUESTED_FOR_USER_ID%TYPE;
531 l_requested_for_party_id UMX_REG_REQUESTS.REQUESTED_FOR_PARTY_ID%type;
532 l_requested_username UMX_REG_REQUESTS.REQUESTED_USERNAME%TYPE;
533
534 l_index_to_add number;
535 l_count number := 0;
536
537 BEGIN
538
539 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
540 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
541 'fnd.plsql.UMXVREGB.populateRegData.begin', '');
542 end if;
543
544 j := p_registration_data.FIRST;
545
546 -- We first have to get the Registration Service Code and Registration Request ID.
547 while ((j <= p_registration_data.LAST) and (l_count < 2)) loop
548
549 if p_registration_data(j).ATTR_NAME = G_REG_SERVICE_CODE then
550 l_reg_service_code := p_registration_data(j).ATTR_VALUE;
551 l_count := l_count + 1;
552 end if;
553
554 if p_registration_data(j).ATTR_NAME = G_REG_REQUEST_ID then
555 l_reg_request_id := p_registration_data(j).ATTR_VALUE;
556 l_count := l_count + 1;
557 end if;
558
559 j := j + 1;
560
561 end loop;
562
563 if l_reg_request_id is null then
564 -- Possibly from Self Service
565 get_req_data_from_req_sv_code (l_reg_service_code, l_reg_service_type, l_wf_role_name,
566 l_ame_application_id, l_ame_transaction_type_id,
567 l_wf_notification_event_name, l_email_verification_flag,
568 l_application_id, l_reg_function_name,
569 l_display_name, l_description, l_wf_bus_logic_event_name);
570 else
571 -- Possibly from ART or SMART
572 get_req_data_from_req_id (l_reg_request_id, l_reg_service_code,
573 l_reg_service_type, l_requested_by_user_id,
574 l_requested_for_user_id, l_requested_for_party_id,
575 l_requested_username, l_wf_role_name,
576 l_ame_application_id, l_ame_transaction_type_id,
577 l_justification, l_wf_notification_event_name,
578 l_email_verification_flag, l_application_id,
579 l_reg_function_name, l_display_name, l_description,
580 l_wf_bus_logic_event_name);
581 end if;
582
583 -- Requested by User ID must be filled with some value. If it is null, then
584 -- get the value from the current logged in user.
585 if (l_requested_by_user_id is null) then
586 l_requested_by_user_id := fnd_global.user_id;
587 end if;
588
589 -- Look for existing data. If we already find the metadata in the table
590 -- we will add into its value.
591
592 j := p_registration_data.FIRST;
593
594 while (j <= p_registration_data.LAST) loop
595
596 if p_registration_data(j).ATTR_NAME = G_WF_ROLE_NAME then
597 if (p_registration_data(j).ATTR_VALUE is not null) then
598 l_wf_role_name := null;
599 end if;
600 end if;
601
602 if p_registration_data(j).ATTR_NAME = G_REG_SERVICE_TYPE then
603 if (p_registration_data(j).ATTR_VALUE is not null) then
604 l_reg_service_type := null;
605 end if;
606 end if;
607
608 if p_registration_data(j).ATTR_NAME = G_REG_SERVICE_APP_ID then
609 if (p_registration_data(j).ATTR_VALUE is not null) then
610 l_application_id := null;
611 end if;
612 end if;
613
614 if p_registration_data(j).ATTR_NAME = G_WF_NOTIFICATION_EVENT then
615 if (p_registration_data(j).ATTR_VALUE is not null) then
616 l_wf_notification_event_name := null;
617 end if;
618 end if;
619
620 if p_registration_data(j).ATTR_NAME = G_WF_BUS_LOGIC_EVENT then
621 if (p_registration_data(j).ATTR_VALUE is not null) then
622 l_wf_bus_logic_event_name := null;
623 end if;
624 end if;
625
626 if p_registration_data(j).ATTR_NAME = G_AME_APPLICATION_ID then
627 if (p_registration_data(j).ATTR_VALUE is not null) then
628 l_ame_application_id := null;
629 end if;
630 end if;
631
632 if p_registration_data(j).ATTR_NAME = G_AME_TXN_TYPE_ID then
633 if (p_registration_data(j).ATTR_VALUE is not null) then
634 l_ame_transaction_type_id := null;
635 end if;
636 end if;
637
638 if p_registration_data(j).ATTR_NAME = G_IDENTITY_VERIFY_REQD then
639 if (p_registration_data(j).ATTR_VALUE is not null) then
640 l_email_verification_flag := null;
641 end if;
642 end if;
643
644 if p_registration_data(j).ATTR_NAME = G_REG_FUNCTION_NAME then
645 if (p_registration_data(j).ATTR_VALUE is not null) then
646 l_reg_function_name := null;
647 end if;
648 end if;
649
650 if p_registration_data(j).ATTR_NAME = G_REG_SERVICE_DISP_NAME then
651 if (p_registration_data(j).ATTR_VALUE is not null) then
652 l_display_name := null;
653 end if;
654 end if;
655
656 if p_registration_data(j).ATTR_NAME = G_REG_SERVICE_DESCRIPTION then
657 if (p_registration_data(j).ATTR_VALUE is not null) then
658 l_description := null;
659 end if;
660 end if;
661
662 if p_registration_data(j).ATTR_NAME = G_JUSTIFICATION then
663 if (p_registration_data(j).ATTR_VALUE is not null) then
664 l_justification := null;
665 end if;
666 end if;
667
668 if p_registration_data(j).ATTR_NAME = G_REQUESTED_BY_USER_ID then
669 if (p_registration_data(j).ATTR_NAME is not null) then
670 l_requested_by_user_id := null;
671 end if;
672 end if;
673
674 if p_registration_data(j).ATTR_NAME = G_REQUESTED_FOR_USER_ID then
675 if (p_registration_data(j).ATTR_VALUE is not null) then
676 l_requested_for_user_id := null;
677 end if;
678 end if;
679
680 if p_registration_data(j).ATTR_NAME = G_REQUESTED_FOR_PARTY_ID then
681 if (p_registration_data(j).ATTR_VALUE is not null) then
682 l_requested_for_party_id := null;
683 end if;
684 end if;
685
686 if p_registration_data(j).ATTR_NAME = G_REQUESTED_USERNAME then
687 if (p_registration_data(j).ATTR_VALUE is not null) then
688 l_requested_username := null;
689 end if;
690 end if;
691
692 j := j + 1;
693
694 end loop;
695
696 -- Add meta data that did not exist
697 l_index_to_add := p_registration_data.last + 1;
698
699 if l_wf_role_name is not null then
700 p_registration_data(l_index_to_add).ATTR_NAME := G_WF_ROLE_NAME;
701 p_registration_data(l_index_to_add).ATTR_VALUE := l_wf_role_name;
702 l_index_to_add := l_index_to_add + 1;
703 end if;
704
705 if l_reg_service_type is not null then
706 p_registration_data(l_index_to_add).ATTR_NAME := G_REG_SERVICE_TYPE;
707 p_registration_data(l_index_to_add).ATTR_VALUE := l_reg_service_type;
708 l_index_to_add := l_index_to_add + 1;
709 end if;
710
711 if l_application_id is not null then
712 p_registration_data(l_index_to_add).ATTR_NAME := G_REG_SERVICE_APP_ID;
713 p_registration_data(l_index_to_add).ATTR_VALUE := l_application_id;
714 l_index_to_add := l_index_to_add + 1;
715 end if;
716
717 if l_wf_notification_event_name is not null then
718 p_registration_data(l_index_to_add).ATTR_NAME := G_WF_NOTIFICATION_EVENT;
719 p_registration_data(l_index_to_add).ATTR_VALUE := l_wf_notification_event_name;
720 l_index_to_add := l_index_to_add + 1;
721 end if;
722
723 if l_wf_bus_logic_event_name is not null then
724 p_registration_data(l_index_to_add).ATTR_NAME := G_WF_BUS_LOGIC_EVENT;
725 p_registration_data(l_index_to_add).ATTR_VALUE := l_wf_bus_logic_event_name;
726 l_index_to_add := l_index_to_add + 1;
727 end if;
728
729 if l_ame_application_id is not null then
730 p_registration_data(l_index_to_add).ATTR_NAME := G_AME_APPLICATION_ID;
731 p_registration_data(l_index_to_add).ATTR_VALUE := l_ame_application_id;
732 l_index_to_add := l_index_to_add + 1;
733 end if;
734
735 if l_ame_transaction_type_id is not null then
736 p_registration_data(l_index_to_add).ATTR_NAME := G_AME_TXN_TYPE_ID;
737 p_registration_data(l_index_to_add).ATTR_VALUE := l_ame_transaction_type_id;
738 l_index_to_add := l_index_to_add + 1;
739 end if;
740
741 if l_email_verification_flag is not null then
742 p_registration_data(l_index_to_add).ATTR_NAME := G_IDENTITY_VERIFY_REQD;
743 p_registration_data(l_index_to_add).ATTR_VALUE := l_email_verification_flag;
744 l_index_to_add := l_index_to_add + 1;
745 end if;
746
747 if l_reg_function_name is not null then
748 p_registration_data(l_index_to_add).ATTR_NAME := G_REG_FUNCTION_NAME;
749 p_registration_data(l_index_to_add).ATTR_VALUE := l_reg_function_name;
750 l_index_to_add := l_index_to_add + 1;
751 end if;
752
753 if l_display_name is not null then
754 p_registration_data(l_index_to_add).ATTR_NAME := G_REG_SERVICE_DISP_NAME;
755 p_registration_data(l_index_to_add).ATTR_VALUE := l_display_name;
756 l_index_to_add := l_index_to_add + 1;
757 end if;
758
759 if l_description is not null then
760 p_registration_data(l_index_to_add).ATTR_NAME := G_REG_SERVICE_DESCRIPTION;
761 p_registration_data(l_index_to_add).ATTR_VALUE := l_description;
762 l_index_to_add := l_index_to_add + 1;
763 end if;
764
765 if l_justification is not null then
766 p_registration_data(l_index_to_add).ATTR_NAME := G_JUSTIFICATION;
767 p_registration_data(l_index_to_add).ATTR_VALUE := l_justification;
768 l_index_to_add := l_index_to_add + 1;
769 end if;
770
771 if l_requested_by_user_id is not null then
772 p_registration_data(l_index_to_add).ATTR_NAME := G_REQUESTED_BY_USER_ID;
773 p_registration_data(l_index_to_add).ATTR_VALUE := l_requested_by_user_id;
774 l_index_to_add := l_index_to_add + 1;
775 end if;
776
777 if l_requested_for_user_id is not null then
778 p_registration_data(l_index_to_add).ATTR_NAME := G_REQUESTED_FOR_USER_ID;
779 p_registration_data(l_index_to_add).ATTR_VALUE := l_requested_for_user_id;
780 l_index_to_add := l_index_to_add + 1;
781 end if;
782
783 if l_requested_for_party_id is not null then
784 p_registration_data(l_index_to_add).ATTR_NAME := G_REQUESTED_FOR_PARTY_ID;
785 p_registration_data(l_index_to_add).ATTR_VALUE := l_requested_for_party_id;
786 l_index_to_add := l_index_to_add + 1;
787 end if;
788
789 if l_requested_username is not null then
790 p_registration_data(l_index_to_add).ATTR_NAME := G_REQUESTED_USERNAME;
791 p_registration_data(l_index_to_add).ATTR_VALUE := l_requested_username;
792 l_index_to_add := l_index_to_add + 1;
793 end if;
794
795 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
796 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
797 'fnd.plsql.UMXVREGB.populateRegData.end', '');
798 end if;
799
800 END POPULATE_REG_DATA;
801
802 --
803 -- Procedure : assign_role
804 -- Type : Private
805 -- Pre_reqs : None
806 -- Description : This API will assign or launch wf to assign role.
807 -- Input Parameters (Mandatory):
808 -- p_registration_data : Table of record type of UMX_REGISTRATION_DATA
809 -- Output Parameters:
810 -- p_registration_data : Table of record type of UMX_REGISTRATION_DATA
811 --
812 procedure assign_role (p_registration_data in out NOCOPY UMX_REGISTRATION_DATA_TBL) IS
813
814 l_parameter_list wf_parameter_list_t;
815 l_event_key varchar2 (240);
816 i number;
817
818 l_reg_request_id_i number;
819 l_reg_service_type_i number;
820 l_status_code_i number;
821 l_requested_for_party_id_i number;
822 l_wf_role_name_i number;
823 l_reg_service_code_i number;
824 l_ame_application_id_i number;
825 l_ame_transaction_type_id_i number;
826 l_wf_notification_event_i number;
827 l_wf_bus_logic_event_i number;
828 l_email_verification_flag_i number;
829 l_application_id_i number;
830 l_reg_function_name_i number;
831 l_display_name_i number;
832 l_description_i number;
833
834 l_reg_request_id umx_reg_requests.reg_request_id%type;
835 l_reg_service_type umx_reg_requests.reg_service_type%type;
836 l_status_code umx_reg_requests.status_code%type;
837 l_requested_start_date umx_reg_requests.requested_start_date%type;
838 l_requested_by_user_id umx_reg_requests.requested_by_user_id%type;
839 l_requested_for_user_id umx_reg_requests.requested_for_user_id%type;
840 l_requested_for_user_name fnd_user.user_name%type;
841 l_requested_for_party_id umx_reg_requests.requested_for_party_id%type;
842 l_requested_username umx_reg_requests.requested_username%type;
843 l_requested_end_date umx_reg_requests.requested_end_date%type;
844 l_wf_role_name umx_reg_requests.wf_role_name%type;
845 l_reg_service_code umx_reg_requests.reg_service_code%type;
846 l_ame_application_id umx_reg_requests.ame_application_id%type;
847 l_ame_transaction_type_id umx_reg_requests.ame_transaction_type_id%type;
848 l_justification umx_reg_requests.justification%type;
849 l_wf_notification_event_name wf_events.name%TYPE;
850 l_wf_bus_logic_event_name wf_events.name%TYPE;
851 l_email_verification_flag UMX_REG_SERVICES_VL.EMAIL_VERIFICATION_FLAG%TYPE;
852 l_application_id UMX_REG_SERVICES_VL.APPLICATION_ID%TYPE;
853 l_reg_function_name fnd_form_functions.FUNCTION_NAME%TYPE;
854 l_display_name UMX_REG_SERVICES_VL.DISPLAY_NAME%TYPE;
855 l_description UMX_REG_SERVICES_VL.DESCRIPTION%TYPE;
856
857 cursor get_username_from_userid (l_userid in fnd_user.user_id%type) is
858 select user_name
859 from fnd_user
860 where user_id = l_userid;
861
862 cursor get_requested_for_party_id (l_userid in fnd_user.user_id%type) is
863 select person_party_id
864 from fnd_user
865 where user_id = l_userid;
866
867 BEGIN
868
869 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
870 -- Print all the name and value in p_registration_data.
871 i := p_registration_data.first;
872 while (i <= p_registration_data.last) loop
873 if (p_registration_data(i).attr_name is not null) then
874 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
875 'fnd.plsql.UMXVREGB.assignRole.begin',
876 'p_registration_data(' || i || ').attr_name = ' || p_registration_data(i).attr_name ||
877 ' | attr_value = ' || p_registration_data(i).attr_value);
878 else
879 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
880 'fnd.plsql.UMXVREGB.assignRole.begin', 'p_registration_data(' || i || ').attr_name is null');
881 end if;
882 i := i + 1;
883 end loop;
884 end if;
885
886 i := p_registration_data.first;
887
888 -- Save the index of each attribute for later use
889 while (i <= p_registration_data.last) loop
890 if (p_registration_data(i).attr_name = G_REG_REQUEST_ID) then
891 l_reg_request_id_i := i;
892 l_reg_request_id := p_registration_data(i).attr_value;
893 elsif (p_registration_data(i).attr_name = G_REG_SERVICE_TYPE) then
894 l_reg_service_type_i := i;
895 elsif (p_registration_data(i).attr_name = G_REG_REQUEST_STATUS) then
896 l_status_code_i := i;
897 elsif (p_registration_data(i).attr_name = G_REQUESTED_START_DATE) then
898 if (p_registration_data(i).attr_value is not null) then
899 l_requested_start_date := fnd_date.canonical_to_date (p_registration_data(i).attr_value);
900 else
901 l_requested_start_date := sysdate;
902 p_registration_data(i).attr_value := fnd_date.date_to_canonical (l_requested_start_date);
903 end if;
904 elsif (p_registration_data(i).attr_name = G_REQUESTED_BY_USER_ID) then
905 if (p_registration_data(i).attr_value is null) then
906 l_requested_by_user_id := fnd_global.user_id;
907 p_registration_data(i).attr_value := l_requested_by_user_id;
908 else
909 l_requested_by_user_id := p_registration_data(i).attr_value;
910 end if;
911 elsif (p_registration_data(i).attr_name = G_REQUESTED_FOR_USER_ID) then
912 l_requested_for_user_id := p_registration_data(i).attr_value;
913 elsif (p_registration_data(i).attr_name = G_REQUESTED_FOR_PARTY_ID) then
914 l_requested_for_party_id_i := i;
915 elsif (p_registration_data(i).attr_name = G_REQUESTED_USERNAME) then
916 l_requested_username := p_registration_data(i).attr_value;
917 elsif (p_registration_data(i).attr_name = G_REQUESTED_END_DATE) then
918 if (p_registration_data(i).attr_value is not null) then
919 l_requested_end_date := fnd_date.canonical_to_date (p_registration_data(i).attr_value);
920 end if;
921 elsif (p_registration_data(i).attr_name = G_WF_ROLE_NAME) then
922 l_wf_role_name_i := i;
923 elsif (p_registration_data(i).attr_name = G_REG_SERVICE_CODE) then
924 l_reg_service_code_i := i;
925 elsif (p_registration_data(i).attr_name = G_AME_APPLICATION_ID) then
926 l_ame_application_id_i := i;
927 elsif (p_registration_data(i).attr_name = G_AME_TXN_TYPE_ID) then
928 l_ame_transaction_type_id_i := i;
929 elsif (p_registration_data(i).attr_name = G_JUSTIFICATION) then
930 l_justification := p_registration_data(i).attr_value;
931 elsif (p_registration_data(i).attr_name = G_WF_NOTIFICATION_EVENT) then
932 l_wf_notification_event_i := i;
933 elsif (p_registration_data(i).attr_name = G_WF_BUS_LOGIC_EVENT) then
934 l_wf_bus_logic_event_i := i;
935 elsif (p_registration_data(i).attr_name = G_IDENTITY_VERIFY_REQD) then
936 l_email_verification_flag_i := i;
937 elsif (p_registration_data(i).attr_name = G_REG_SERVICE_APP_ID) then
938 l_application_id_i := i;
939 elsif (p_registration_data(i).attr_name = G_REG_FUNCTION_NAME) then
940 l_reg_function_name_i := i;
941 l_reg_function_name := p_registration_data(i).attr_value;
942 elsif (p_registration_data(i).attr_name = G_REG_SERVICE_DISP_NAME) then
943 l_display_name_i := i;
944 elsif (p_registration_data(i).attr_name = G_REG_SERVICE_DESCRIPTION) then
945 l_description_i := i;
946 end if;
947 i := i + 1;
948 end loop;
949
950 -- Get the 'requested for party id'
951 if (l_requested_for_user_id is not null) then
952 open get_requested_for_party_id (l_requested_for_user_id);
953 fetch get_requested_for_party_id into l_requested_for_party_id;
954 close get_requested_for_party_id;
955
956 if (l_requested_for_party_id_i is null) then
957 l_requested_for_party_id_i := p_registration_data.last + 1;
958 p_registration_data(l_requested_for_party_id_i).attr_name := G_REQUESTED_FOR_PARTY_ID;
959 end if;
960
961 if (l_requested_for_party_id is not null) then
962 p_registration_data(l_requested_for_party_id_i).attr_value := l_requested_for_party_id;
963 end if;
964 end if;
965
966
967 if (l_reg_service_code_i is null) then
968 -- We need to add the Reg Service Code into the p_registration_data
969 l_reg_service_code_i := p_registration_data.last + 1;
970 p_registration_data(l_reg_service_code_i).attr_name := G_REG_SERVICE_CODE;
971 end if;
972
973 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
974 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
975 'fnd.plsql.UMXVREGB.assignRole',
976 'regServiceCode:' || p_registration_data(l_reg_service_code_i).attr_value);
977 end if;
978
979 -- Query the rest of the Reg Service meta data.
980 if (p_registration_data(l_reg_service_code_i).attr_value is null) then
981 -- Calling from SMART
982 l_wf_role_name := p_registration_data(l_wf_role_name_i).attr_value;
983 get_req_data_from_role_name (l_wf_role_name, l_reg_service_code, l_reg_service_type,
984 l_ame_application_id, l_ame_transaction_type_id,
985 l_wf_notification_event_name, l_email_verification_flag,
986 l_reg_function_name, l_wf_bus_logic_event_name);
987 else
988 l_reg_service_code := p_registration_data(l_reg_service_code_i).attr_value;
989
990 get_req_data_from_req_sv_code (l_reg_service_code, l_reg_service_type, l_wf_role_name,
991 l_ame_application_id, l_ame_transaction_type_id,
992 l_wf_notification_event_name, l_email_verification_flag,
993 l_application_id, l_reg_function_name,
994 l_display_name, l_description, l_wf_bus_logic_event_name);
995 end if;
996
997 -- Set the queried data into p_registration_data only if the value is null.
998 if (l_wf_role_name_i is null) then
999 l_wf_role_name_i := p_registration_data.last + 1;
1000 p_registration_data(l_wf_role_name_i).attr_name := G_WF_ROLE_NAME;
1001 p_registration_data(l_wf_role_name_i).attr_value := l_wf_role_name;
1002 else
1003 if (p_registration_data(l_wf_role_name_i).attr_value is null) then
1004 p_registration_data(l_wf_role_name_i).attr_value := l_wf_role_name;
1005 else
1006 l_wf_role_name := p_registration_data(l_wf_role_name_i).attr_value;
1007 end if;
1008 end if;
1009
1010 if (l_reg_service_type_i is null) then
1011 l_reg_service_type_i := p_registration_data.last + 1;
1012 p_registration_data(l_reg_service_type_i).attr_name := G_REG_SERVICE_TYPE;
1013 p_registration_data(l_reg_service_type_i).attr_value := l_reg_service_type;
1014 else
1015 if (p_registration_data(l_reg_service_type_i).attr_value is null) then
1016 p_registration_data(l_reg_service_type_i).attr_value := l_reg_service_type;
1017 else
1018 l_reg_service_type := p_registration_data(l_reg_service_type_i).attr_value;
1019 end if;
1020 end if;
1021
1022 if (l_application_id_i is null) then
1023 l_application_id_i := p_registration_data.last + 1;
1024 p_registration_data(l_application_id_i).attr_name := G_REG_SERVICE_APP_ID;
1025 p_registration_data(l_application_id_i).attr_value := l_application_id;
1026 else
1027 if (p_registration_data(l_application_id_i).attr_value is null) then
1028 p_registration_data(l_application_id).attr_value := l_application_id;
1029 else
1030 l_application_id := p_registration_data(l_application_id).attr_value;
1031 end if;
1032 end if;
1033
1034 if (l_wf_notification_event_i is null) then
1035 l_wf_notification_event_i := p_registration_data.last + 1;
1036 p_registration_data(l_wf_notification_event_i).attr_name := G_WF_NOTIFICATION_EVENT;
1037 p_registration_data(l_wf_notification_event_i).attr_value := l_wf_notification_event_name;
1038 else
1039 if (p_registration_data(l_wf_notification_event_i).attr_value is null) then
1040 p_registration_data(l_wf_notification_event_i).attr_value := l_wf_notification_event_name;
1041 else
1042 l_wf_notification_event_name := p_registration_data(l_wf_notification_event_i).attr_value;
1043 end if;
1044 end if;
1045
1046 if (l_wf_bus_logic_event_i is null) then
1047 l_wf_bus_logic_event_i := p_registration_data.last + 1;
1048 p_registration_data(l_wf_bus_logic_event_i).attr_name := G_WF_BUS_LOGIC_EVENT;
1049 p_registration_data(l_wf_bus_logic_event_i).attr_value := l_wf_bus_logic_event_name;
1050 else
1051 if (p_registration_data(l_wf_bus_logic_event_i).attr_value is null) then
1052 p_registration_data(l_wf_bus_logic_event_i).attr_value := l_wf_bus_logic_event_name;
1053 else
1054 l_wf_bus_logic_event_name := p_registration_data(l_wf_bus_logic_event_i).attr_value;
1055 end if;
1056 end if;
1057
1058 if (l_ame_application_id_i is null) then
1059 l_ame_application_id_i := p_registration_data.last + 1;
1060 p_registration_data(l_ame_application_id_i).attr_name := G_AME_APPLICATION_ID;
1061 p_registration_data(l_ame_application_id_i).attr_value := l_ame_application_id;
1062 else
1063 if (p_registration_data(l_ame_application_id_i).attr_value is null) then
1064 p_registration_data(l_ame_application_id_i).attr_value := l_ame_application_id;
1065 else
1066 l_ame_application_id := p_registration_data(l_ame_application_id_i).attr_value;
1067 end if;
1068 end if;
1069
1070 if (l_ame_transaction_type_id_i is null) then
1071 l_ame_transaction_type_id_i := p_registration_data.last + 1;
1072 p_registration_data(l_ame_transaction_type_id_i).attr_name := G_AME_TXN_TYPE_ID;
1073 p_registration_data(l_ame_transaction_type_id_i).attr_value := l_ame_transaction_type_id;
1074 else
1075 if (p_registration_data(l_ame_transaction_type_id_i).attr_value is null) then
1076 p_registration_data(l_ame_transaction_type_id_i).attr_value := l_ame_transaction_type_id;
1077 else
1078 l_ame_transaction_type_id := p_registration_data(l_ame_transaction_type_id_i).attr_value;
1079 end if;
1080 end if;
1081
1082 if (l_email_verification_flag_i is null) then
1083 l_email_verification_flag_i := p_registration_data.last + 1;
1084 p_registration_data(l_email_verification_flag_i).attr_name := G_IDENTITY_VERIFY_REQD;
1085 p_registration_data(l_email_verification_flag_i).attr_value := l_email_verification_flag;
1086 else
1087 if (p_registration_data(l_email_verification_flag_i).attr_value is null) then
1088 p_registration_data(l_email_verification_flag_i).attr_value := l_email_verification_flag;
1089 else
1090 l_email_verification_flag := p_registration_data(l_email_verification_flag_i).attr_value;
1091 end if;
1092 end if;
1093
1094 if (l_reg_function_name_i is null) then
1095 l_reg_function_name_i := p_registration_data.last + 1;
1096 p_registration_data(l_reg_function_name_i).attr_name := G_REG_FUNCTION_NAME;
1097 p_registration_data(l_reg_function_name_i).attr_value := l_reg_function_name;
1098 else
1099 if (p_registration_data(l_reg_function_name_i).attr_value is null) then
1100 p_registration_data(l_reg_function_name_i).attr_value := l_reg_function_name;
1101 else
1102 l_reg_function_name := p_registration_data(l_reg_function_name_i).attr_value;
1103 end if;
1104 end if;
1105
1106 if (l_display_name_i is null) then
1107 l_display_name_i := p_registration_data.last + 1;
1108 p_registration_data(l_display_name_i).attr_name := G_REG_SERVICE_DISP_NAME;
1109 p_registration_data(l_display_name_i).attr_value := l_display_name;
1110 else
1111 if (p_registration_data(l_display_name_i).attr_value is null) then
1112 p_registration_data(l_display_name_i).attr_value := l_display_name;
1113 else
1114 l_display_name := p_registration_data(l_display_name_i).attr_value;
1115 end if;
1116 end if;
1117
1118 if (l_description_i is null) then
1119 l_description_i := p_registration_data.last + 1;
1120 p_registration_data(l_description_i).attr_name := G_REG_SERVICE_DESCRIPTION;
1121 p_registration_data(l_description_i).attr_value := l_description;
1122 else
1123 if (p_registration_data(l_description_i).attr_value is null) then
1124 p_registration_data(l_description_i).attr_value := l_description;
1125 else
1126 l_description := p_registration_data(l_description_i).attr_value;
1127 end if;
1128 end if;
1129
1130 open get_username_from_userid (l_requested_for_user_id);
1131 fetch get_username_from_userid into l_requested_for_user_name;
1132 close get_username_from_userid;
1133
1134 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1135 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1136 'fnd.plsql.UMXVREGB.assignRole',
1137 'regServiceType: ' || l_reg_service_type ||
1138 ' | wfRoleName: ' || l_wf_role_name);
1139 end if;
1140
1141 -- The request will be directly assigned if:
1142 -- 1) The registration service type is set to have DIRECT_ASSIGNED
1143 -- 2) The requested by and requested for is different AND the registration
1144 -- service type is ADDITIONAL_ACCESS.
1145 -- 3) The request does not has a registration process.
1146 if (l_reg_service_type = 'DIRECT_ASSIGNED') or
1147 ((l_reg_service_type = 'ADDITIONAL_ACCESS') and (l_requested_by_user_id <> l_requested_for_user_id)) or
1148 ((l_reg_service_type is null) and (l_wf_role_name is not null)) then
1149
1150 l_reg_service_type := 'DIRECT_ASSIGNED';
1151 p_registration_data(l_reg_service_type_i).attr_value := l_reg_service_type;
1152
1153 i := p_registration_data.first;
1154 while (i <= p_registration_data.last) loop
1155 wf_event.addParametertoList (upper(p_registration_data(i).ATTR_NAME),
1156 p_registration_data(i).ATTR_VALUE,
1157 l_parameter_list);
1158 i := i + 1;
1159 end loop;
1160
1161 -- Raise WF Event
1162 select umx_events_s.nextval into l_event_key from dual;
1163 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1164 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1165 'fnd.plsql.UMXVREGB.assignRole',
1166 'Raising oracle.apps.fnd.umx.rolerequested event with l_event_key=' || l_event_key);
1167 end if;
1168 wf_event.raise (p_event_name => 'oracle.apps.fnd.umx.rolerequested',
1169 p_event_key => l_event_key,
1170 p_event_data => null,
1171 p_parameters => l_parameter_list,
1172 p_send_date => sysdate);
1173
1174 -- Launch the custom event if the custom event name is not null.
1175 if (l_wf_bus_logic_event_name is not null) then
1176 -- Set the custom event context
1177 wf_event.addParametertoList ('UMX_CUSTOM_EVENT_CONTEXT', 'ROLE APPROVED', l_parameter_list);
1178 -- Finally, raise the custom event.
1179 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1180 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1181 'fnd.plsql.UMXVREGB.assignRole',
1182 'Raising ' || l_wf_bus_logic_event_name || ' event with l_event_key=' || l_event_key);
1183 end if;
1184 wf_event.raise (p_event_name => l_wf_bus_logic_event_name,
1185 p_event_key => l_event_key,
1186 p_event_data => null,
1187 p_parameters => l_parameter_list,
1188 p_send_date => sysdate);
1189 end if;
1190
1191 -- populate the wf_local_user_roles table and update the reg table
1192 -- with status approved
1193 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1194 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1195 'fnd.plsql.UMXVREGB.assignRole',
1196 'Calling wf_local_synch.propagateUserRole (p_user_name => ' || l_requested_for_user_name ||
1197 ', p_role_name => ' || l_wf_role_name ||
1198 ', p_start_date => ' || l_requested_start_date ||
1199 ', p_expiration_date => ' || l_requested_end_date ||
1200 ', p_raiseErrors => true' ||
1201 ', p_assignmentReason => ' || l_justification);
1202 end if;
1203 wf_local_synch.propagateUserRole (
1204 p_user_name => l_requested_for_user_name,
1205 p_role_name => l_wf_role_name,
1206 p_start_date => l_requested_start_date,
1207 p_expiration_date => l_requested_end_date,
1208 p_raiseErrors => true,
1209 p_assignmentReason => l_justification);
1210
1211 l_status_code := 'APPROVED';
1212
1213 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1214 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1215 'fnd.plsql.UMXVREGB.assignRole',
1216 'Raising oracle.apps.fnd.umx.requestapproved event with l_event_key=' || l_event_key);
1217 end if;
1218 wf_event.raise (p_event_name => 'oracle.apps.fnd.umx.requestapproved',
1219 p_event_key => l_event_key,
1220 p_event_data => null,
1221 p_parameters => l_parameter_list,
1222 p_send_date => sysdate);
1223
1224 else
1225
1226 -- Any requests (Additional Access or Admin Additional Access) that have
1227 -- a page flow defined the status code will be UNASSIGNED.
1228 if (l_reg_function_name is not null) then
1229 l_status_code := 'UNASSIGNED';
1230 else
1231 l_status_code := 'PENDING';
1232 end if;
1233 end if;
1234
1235 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1236 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1237 'fnd.plsql.UMXVREGB.assignRole',
1238 'statusCode: ' || l_status_code);
1239 end if;
1240
1241 -- Set the status code now into the registration data
1242 if (l_status_code_i is null) then
1243 l_status_code_i := p_registration_data.last + 1;
1244 p_registration_data(l_status_code_i).attr_name := G_REG_REQUEST_STATUS;
1245 p_registration_data(l_status_code_i).attr_value := l_status_code;
1246 else
1247 p_registration_data(l_status_code_i).attr_value := l_status_code;
1248 end if;
1249
1250 UMX_REG_REQUESTS_PKG.insert_row (
1251 x_reg_request_id => l_reg_request_id ,
1252 x_reg_service_type => l_reg_service_type,
1253 x_status_code => l_status_code,
1254 x_requested_by_user_id => l_requested_by_user_id,
1255 x_requested_for_user_id => l_requested_for_user_id,
1256 x_requested_for_party_id => l_requested_for_party_id,
1257 x_requested_username => l_requested_username,
1258 x_requested_start_date => l_requested_start_date,
1259 x_requested_end_date => l_requested_end_date,
1260 x_wf_role_name => l_wf_role_name,
1261 x_reg_service_code => l_reg_service_code,
1262 x_ame_application_id => l_ame_application_id,
1263 x_ame_transaction_type_id => l_ame_transaction_type_id,
1264 x_justification => l_justification);
1265
1266 if (l_reg_request_id_i is null) then
1267 l_reg_request_id_i := p_registration_data.last + 1;
1268 p_registration_data(l_reg_request_id_i).attr_name := G_REG_REQUEST_ID;
1269 p_registration_data(l_reg_request_id_i).attr_value := l_reg_request_id;
1270 else
1271 p_registration_data(l_reg_request_id_i).attr_value := l_reg_request_id;
1272 end if;
1273
1274 --check for reg_function_id if no page flow and request type is additional access
1275 --call the workflow launcher api here.
1276 -- else set the status in reg table as unassigned
1277 if (l_reg_function_name is null and
1278 l_status_code <> 'APPROVED') then
1279 DO_PROCESS_REQUEST (p_registration_data);
1280 end if;
1281
1282 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1283 -- Print all the name and value in p_registration_data.
1284 i := p_registration_data.first;
1285 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1286 'fnd.plsql.UMXVREGB.assignRole.end', 'p_registration_data.last=' || p_registration_data.last);
1287 while (i <= p_registration_data.last) loop
1288 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1289 'fnd.plsql.UMXVREGB.assignRole.end',
1290 'p_registration_data(' || i || ').attr_name = ' || p_registration_data(i).attr_name ||
1291 ' | attr_value = ' || p_registration_data(i).attr_value);
1292 i := i + 1;
1293 end loop;
1294 end if;
1295 END assign_role;
1296
1297 /**
1298 * Please refer to the package specifications for details
1299 */
1300
1301 function GET_PHONE_NUMBER(p_person_id in per_all_people_f.person_id%type) return varchar2 IS
1302
1303 l_phone_number per_phones.phone_number%type;
1304 cursor find_phone_number is
1305 select perph.phone_number
1306 from per_phones perph
1307 where perph.phone_type = 'W1'
1308 and perph.parent_id = p_person_id
1309 and perph.parent_table = 'PER_ALL_PEOPLE_F'
1310 and perph.date_from <= sysdate
1311 and nvl(perph.date_to, sysdate + 1) > sysdate;
1312
1313
1314 begin
1315
1316 open find_phone_number;
1317 fetch find_phone_number into l_phone_number;
1318 close find_phone_number;
1319
1320 return l_phone_number;
1321
1322 END GET_PHONE_NUMBER;
1323 /**
1324 * Please refer to the package specifications for details
1325 */
1326
1327 function GET_PERSON_ID (p_party_id in hz_parties.party_id%type) return number IS
1328 l_matches varchar2(5);
1329 l_person_id per_all_people_f.person_id%type;
1330 l_cursorid integer;
1331 l_blockstr varchar2(1000);
1332 l_dummy integer;
1333
1334 -- Use dynamamic SQL - Bug 4653519
1335 begin -- Outer Begin
1336 begin -- Inner begin for dynamic SQL
1337 l_cursorid := dbms_sql.open_cursor;
1338 l_blockstr :=
1339 'BEGIN
1340 hr_tca_utility.get_person_id(p_party_id => :l_party_id,
1341 p_person_id => :l_person_id,
1342 p_matches => :l_matches);
1343 END;';
1344
1345 dbms_sql.parse(l_cursorid, l_blockstr, dbms_sql.v7);
1346
1347 dbms_sql.bind_variable(l_cursorid, ':l_party_id', p_party_id);
1348 dbms_sql.bind_variable(l_cursorid, ':l_person_id', l_person_id);
1349 dbms_sql.bind_variable(l_cursorid, ':l_matches', l_matches, 1);
1350
1351 l_dummy := dbms_sql.execute(l_cursorid);
1352
1353 dbms_sql.variable_value(l_cursorid, ':l_person_id', l_person_id);
1354 dbms_sql.variable_value(l_cursorid, ':l_matches', l_matches);
1355 dbms_sql.close_cursor(l_cursorid);
1356
1357 exception
1358 when others then
1359 l_person_id := null;
1360 dbms_sql.close_cursor(l_cursorid);
1361 end;
1362 return l_person_id;
1363 end GET_PERSON_ID;
1364
1365 /**
1366 * Please refer to the package specifications for details
1367 */
1368
1369 function GET_MANAGER_NAME(p_person_id in per_all_people_f.person_id%type) return varchar2 IS
1370
1371 l_manager_name per_all_people_f.full_name%type;
1372 cursor find_mgr_name is
1373 select mgr.full_name
1374 from per_all_assignments_f emp, per_all_people_f mgr
1375 where emp.supervisor_id = mgr.person_id
1376 and emp.person_id = p_person_id
1377 and emp.effective_start_date <= sysdate
1378 and nvl(emp.effective_end_date, sysdate + 1) > sysdate
1379 and mgr.effective_start_date <= sysdate
1380 and nvl(mgr.effective_end_date, sysdate + 1) > sysdate;
1381
1382 begin
1383
1384 open find_mgr_name;
1385 fetch find_mgr_name into l_manager_name;
1386 close find_mgr_name;
1387
1388 return l_manager_name;
1389
1390 END GET_MANAGER_NAME;
1391
1392 /**
1393 * Please refer to the package specifications for details
1394 */
1395
1396 function GET_JOB_TITLE(p_person_id in per_all_people_f.person_id%type) return varchar2 IS
1397
1398 l_title per_jobs_vl.name%type;
1399 cursor find_title is
1400 select name
1401 from per_all_assignments_f emp, per_jobs_vl jobs
1402 where emp.person_id = p_person_id
1403 and emp.effective_start_date <= sysdate
1404 and nvl(emp.effective_end_date, sysdate + 1) > sysdate
1405 and emp.job_id = jobs.job_id
1406 and jobs.date_from <= sysdate
1407 and nvl(jobs.date_to, sysdate + 1) > sysdate;
1408
1409 begin
1410
1411 open find_title;
1412 fetch find_title into l_title;
1413 close find_title;
1414
1415 return l_title;
1416
1417 END GET_JOB_TITLE;
1418
1419
1420 end UMX_REGISTRATION_PVT;