eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.UMX_REGISTRATION_PVT

Source


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