[Home] [Help]
1: PACKAGE BODY UMX_REG_REQUESTS_PVT AS
2: /* $Header: UMXVRRSB.pls 120.11 2011/02/17 08:11:07 spakanat ship $ */
3:
4: -- procedure
5: --
51:
52: if ( p_reg_request.requested_for_user_id is null) then
53: fnd_message.set_name('FND','UMX_COMMON_MISS_PARAM_MSG');
54: fnd_message.set_token('PARAM', 'p_reg_request.requested_for_user_id');
55: fnd_message.set_token('API', 'UMX_REG_REQUESTS_PVT.populateRegRecord');
56: raise_application_error ('-20000', fnd_message.get);
57:
58: else
59:
63: if (getPersonPartyId%notfound) then
64: close getPersonPartyId;
65: fnd_message.set_name('FND','UMX_COMMON_MISS_PARAM_MSG');
66: fnd_message.set_token('PARAM', 'getPersonPartyId');
67: fnd_message.set_token('API', 'UMX_REG_REQUESTS_PVT.populateRegRecord');
68: raise_application_error ('-20000', fnd_message.get);
69: end if;
70:
71: close getPersonPartyId;
94: elsif (p_reg_request.wf_role_name is null) then
95: -- this is a smart request and role name should be passed
96: fnd_message.set_name('FND','UMX_COMMON_MISS_PARAM_MSG');
97: fnd_message.set_token('PARAM', 'p_reg_request.wf_role_name');
98: fnd_message.set_token('API', 'UMX_REG_REQUESTS_PVT.populateRegRecord');
99: raise_application_error ('-20000', fnd_message.get);
100: end if;
101:
102: if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
188: -- Procedure : update_reg_request
189: -- Type : Private
190: -- Pre_reqs : None
191: -- Description : This API will create a registration request
192: -- into the UMX_REG_REQUESTS table.
193: -- Before registration request can be inserted into
194: -- UMX_REG_REQUESTS table, this API will check to see
195: -- if the requester already have a valid association to
196: -- this access role in wf_local_user_role. This API will
190: -- Pre_reqs : None
191: -- Description : This API will create a registration request
192: -- into the UMX_REG_REQUESTS table.
193: -- Before registration request can be inserted into
194: -- UMX_REG_REQUESTS table, this API will check to see
195: -- if the requester already have a valid association to
196: -- this access role in wf_local_user_role. This API will
197: -- return null if there is a valid access role.
198: -- Input Parameters (Mandatory):
216: FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
217: 'fnd.plsql.UMXVRRSB.update_reg_request.begin', 'Begin');
218: end if;
219:
220: UMX_REG_REQUESTS_PKG.update_row (
221: X_REG_REQUEST_ID => p_reg_request.reg_request_id,
222: X_STATUS_CODE => p_reg_request.status_code,
223: X_REQUESTED_BY_USER_ID => fnd_global.user_id,
224: X_REQUESTED_FOR_USER_ID => p_reg_request.requested_for_user_id,
243: -- Procedure : delete_reg_request
244: -- Type : Private
245: -- Pre_reqs : None
246: -- Description : This API will delete a registration request
247: -- into the UMX_REG_REQUESTS table.
248: -- Before registration request can be inserted into
249: -- UMX_REG_REQUESTS table, this API will check to see
250: -- if the requester already have a valid association to
251: -- this access role in wf_local_user_role. This API will
245: -- Pre_reqs : None
246: -- Description : This API will delete a registration request
247: -- into the UMX_REG_REQUESTS table.
248: -- Before registration request can be inserted into
249: -- UMX_REG_REQUESTS table, this API will check to see
250: -- if the requester already have a valid association to
251: -- this access role in wf_local_user_role. This API will
252: -- return null if there is a valid access role.
253: -- Input Parameters (Mandatory):
265: -- Output Parameters:
266: -- x_reg_request_id: Registration Request ID
267: --
268: procedure delete_reg_request (
269: p_reg_request_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE
270: ) is
271: BEGIN
272: if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
273: FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
274: 'fnd.plsql.UMXVRRSB.delete_reg_request.begin',
275: 'regRequestId: ' || p_reg_request_id);
276: end if;
277:
278: UMX_REG_REQUESTS_PKG.DELETE_ROW (X_REG_REQUEST_ID => p_reg_request_id);
279:
280: if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
281: FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
282: 'fnd.plsql.UMXVRRSB.delete_reg_request.end', 'End');
322: if (p_username is null) or (l_username is null) then
323: -- Username is a required input parameter.
324: fnd_message.set_name('FND','UMX_COMMON_MISS_PARAM_MSG');
325: fnd_message.set_token('PARAM', 'p_username');
326: fnd_message.set_token('API', 'UMX_REG_REQUESTS_PVT.is_username_available');
327: raise_application_error ('-20000', fnd_message.get);
328: end if;
329:
330: open getUserFromFNDUSER (l_username);
364: -- Pre_reqs : None
365: -- Description : This API will ...
366: -- 1) Call fnd_user_pkg.reserve_username API to reserve
367: -- the requested username.
368: -- 2) Update the UMX_REG_REQUESTS table with the
369: -- requested for username and requested by username
370: -- (if requested by is null).
371: --
372: -- This API should be called when user requests a user
382: -- Description : It will either return the user ID if the username is
383: -- successfully reserved or null if otherwise.
384: --
385: function reserve_username (
386: p_reg_request_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE,
387: p_username in FND_USER.USER_NAME%TYPE,
388: p_owner in varchar2 default null,
389: p_unencrypted_password in varchar2,
390: p_session_number in number default 0,
399: p_person_party_id in FND_USER.PERSON_PARTY_ID%TYPE default null
400: ) return fnd_user.user_id%type is
401:
402: l_user_id fnd_user.user_id%type;
403: l_requested_by_user_id UMX_reg_requests.requested_by_user_id%type;
404:
405: cursor getRequestedByUserId (p_reg_request_id in UMX_reg_requests.reg_request_id%type) is
406: select requested_by_user_id
407: from umx_reg_requests
401:
402: l_user_id fnd_user.user_id%type;
403: l_requested_by_user_id UMX_reg_requests.requested_by_user_id%type;
404:
405: cursor getRequestedByUserId (p_reg_request_id in UMX_reg_requests.reg_request_id%type) is
406: select requested_by_user_id
407: from umx_reg_requests
408: where reg_request_id = p_reg_request_id;
409:
403: l_requested_by_user_id UMX_reg_requests.requested_by_user_id%type;
404:
405: cursor getRequestedByUserId (p_reg_request_id in UMX_reg_requests.reg_request_id%type) is
406: select requested_by_user_id
407: from umx_reg_requests
408: where reg_request_id = p_reg_request_id;
409:
410: begin
411:
460: open getRequestedByUserId (p_reg_request_id);
461: fetch getRequestedByUserId into l_requested_by_user_id;
462: if (getRequestedByUserId%notfound) then
463: close getRequestedByUserId;
464: raise_application_error ('-20000', '<
466: close getRequestedByUserId;
467:
468: if (l_requested_by_user_id is null) then
470: l_requested_by_user_id := l_user_id;
471: end if;
472:
473: -- Update the Reg Requests table
474: UMX_REG_REQUESTS_PKG.update_row (
475: X_REG_REQUEST_ID => p_reg_request_id,
476: X_REQUESTED_BY_USER_ID => l_requested_by_user_id,
477: X_REQUESTED_FOR_USER_ID => l_USER_ID,
478: X_REQUESTED_USERNAME => p_username,
495: -- Description : This API will ...
496: -- 1) Activiate the user account by calling
497: -- fnd_user_pkg.activate_user_account.
498: -- 2) Set the status code to "APPROVED" in
499: -- UMX_REG_REQUESTS table.
500: --
501: -- This API should be called from Self-Service Registration or
502: -- Admin Creation.
503: -- Input Parameters :
521: -- None
522: -- Description :
523: --
524: Procedure approve_username_reg_request (
525: p_reg_request_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE,
526: p_username in FND_USER.USER_NAME%TYPE,
527: p_person_party_id in FND_USER.PERSON_PARTY_ID%TYPE,
528: p_start_date in FND_USER.START_DATE%TYPE default sysdate,
529: p_end_date in FND_USER.END_DATE%TYPE default null) is
702:
703:
704:
705: -- Update the record in the Reg Requests table with status and party id
706: UMX_REG_REQUESTS_PKG.update_row (
707: X_REG_REQUEST_ID => p_reg_request_id,
708: X_STATUS_CODE => 'APPROVED',
709: X_REQUESTED_FOR_PARTY_ID => p_person_party_id);
710:
722: -- Description : This API will ...
723: -- 1) Release the username by calling
724: -- fnd_user_pkg.release_username API.
725: -- 2) Set the status code to "REJECT" or "CANCELLED" in
726: -- UMX_REG_REQUESTS table.
727: -- 3) Remove the REQUESTED_FOR_USER_ID
728: -- 4) If the REQUESTED_BY_USER_ID is the same as the
729: -- REQUESTED_FOR_USER_ID, remove the
730: -- REQUESTED_BY_USER_ID
746: -- None
747: -- Description :
748: --
749: Procedure reject_cancel_username_reg_req (
750: p_reg_request_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE,
751: p_username in FND_USER.USER_NAME%TYPE,
752: p_user_id in FND_USER.USER_ID%TYPE,
753: p_status_code in UMX_REG_REQUESTS.STATUS_CODE%TYPE) is
754:
749: Procedure reject_cancel_username_reg_req (
750: p_reg_request_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE,
751: p_username in FND_USER.USER_NAME%TYPE,
752: p_user_id in FND_USER.USER_ID%TYPE,
753: p_status_code in UMX_REG_REQUESTS.STATUS_CODE%TYPE) is
754:
755: l_requested_by_user_id umx_reg_requests.requested_by_user_id%type;
756:
757: cursor getRequestedByUserID (p_reg_request_id in UMX_reg_requests.reg_request_id%type) is
751: p_username in FND_USER.USER_NAME%TYPE,
752: p_user_id in FND_USER.USER_ID%TYPE,
753: p_status_code in UMX_REG_REQUESTS.STATUS_CODE%TYPE) is
754:
755: l_requested_by_user_id umx_reg_requests.requested_by_user_id%type;
756:
757: cursor getRequestedByUserID (p_reg_request_id in UMX_reg_requests.reg_request_id%type) is
758: select requested_by_user_id
759: from umx_reg_requests
753: p_status_code in UMX_REG_REQUESTS.STATUS_CODE%TYPE) is
754:
755: l_requested_by_user_id umx_reg_requests.requested_by_user_id%type;
756:
757: cursor getRequestedByUserID (p_reg_request_id in UMX_reg_requests.reg_request_id%type) is
758: select requested_by_user_id
759: from umx_reg_requests
760: where reg_request_id = p_reg_request_id;
761:
755: l_requested_by_user_id umx_reg_requests.requested_by_user_id%type;
756:
757: cursor getRequestedByUserID (p_reg_request_id in UMX_reg_requests.reg_request_id%type) is
758: select requested_by_user_id
759: from umx_reg_requests
760: where reg_request_id = p_reg_request_id;
761:
762: begin
763:
781: 'fnd.plsql.UMXVRRSB.reject_cancel_username_reg_req.begin',
782: 'After calling fnd_user_pkg.RemovePendingUser');
783: end if;
784:
785: -- Query the requested_by_user_id from UMX_reg_requests table
786: -- If the requested_by_user_id is equal to the requested_for_user_id,
787: -- make requested_by_user_id null to avoid dangling foreign key.
788: open getRequestedByUserID (p_reg_request_id);
789: fetch getRequestedByUserID into l_requested_by_user_id;
798: -- requested_by_user_id to null.
799: l_requested_by_user_id := fnd_api.g_miss_num;
800: end if;
801:
802: UMX_REG_REQUESTS_PKG.update_row (
803: X_REG_REQUEST_ID => p_reg_request_id,
804: X_STATUS_CODE => p_status_code,
805: X_REQUESTED_FOR_USER_ID => fnd_api.g_miss_num,
806: X_REQUESTED_BY_USER_ID => l_requested_by_user_id);
835: -- None
836: -- Description :
837: --
838: Procedure reject_username_reg_request (
839: p_reg_request_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE,
840: p_user_id in FND_USER.USER_ID%TYPE,
841: p_username in FND_USER.USER_NAME%TYPE) is
842:
843: begin
887: -- None
888: -- Description :
889: --
890: Procedure cancel_username_reg_request (
891: p_reg_request_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE,
892: p_user_id in FND_USER.USER_ID%TYPE,
893: p_username in FND_USER.USER_NAME%TYPE) is
894:
895: begin
920: -- Procedure : approve_reject_reg_request
921: -- Type : Private
922: -- Pre_reqs : None
923: -- Description : This API will set the status code of a record in
924: -- UMX_REG_REQUESTS table.
925: --
926: -- Input Parameters :
927: -- @param p_reg_request_id
928: -- Description : ID for the registration request
927: -- @param p_reg_request_id
928: -- Description : ID for the registration request
929: -- Required : Yes
930: -- @param p_status_code
931: -- Description : Status code of the record in UMX_REG_REQUESTS table
932: -- Required : Yes
933: -- Output :
934: -- None
935: -- Description :
934: -- None
935: -- Description :
936: --
937: Procedure approve_reject_reg_request (
938: p_reg_request_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE,
939: p_status_code in UMX_REG_REQUESTS.STATUS_CODE%type) is
940:
941: begin
942:
935: -- Description :
936: --
937: Procedure approve_reject_reg_request (
938: p_reg_request_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE,
939: p_status_code in UMX_REG_REQUESTS.STATUS_CODE%type) is
940:
941: begin
942:
943: if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
947: ' | statusCode: ' || p_status_code);
948: end if;
949:
950: -- update the record in the Reg Requests table with status to status_code
951: UMX_REG_REQUESTS_PKG.update_row (X_REG_REQUEST_ID => p_reg_request_id,
952: X_STATUS_CODE => p_status_code);
953:
954: if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
955: FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
962: -- Procedure : approve_reg_request
963: -- Type : Private
964: -- Pre_reqs : None
965: -- Description : This API will approve_reject_reg_request to approve
966: -- the Reg Request in UMX_REG_REQUESTS table.
967: --
968: -- This API should be called from ART or SMART.
969: -- Input Parameters :
970: -- @param p_reg_request_id
973: -- Output :
974: -- None
975: -- Description :
976: --
977: Procedure approve_reg_request (p_reg_request_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE) is
978: begin
979:
980: if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
981: FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
983: 'regRequestId: ' || p_reg_request_id);
984: end if;
985:
986: -- Call approve_reject_reg_request to update the record in
987: -- UMX_REG_REQUESTS table.
988: approve_reject_reg_request (p_reg_request_id => p_reg_request_id,
989: p_status_code => 'APPROVED');
990:
991: if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
999: -- Procedure : reject_reg_request
1000: -- Type : Private
1001: -- Pre_reqs : None
1002: -- Description : This API will approve_reject_reg_request to reject
1003: -- the Reg Request in UMX_REG_REQUESTS table.
1004: --
1005: -- This API should be called from ART or SMART.
1006: -- Input Parameters :
1007: -- @param p_reg_request_id
1010: -- Output :
1011: -- None
1012: -- Description :
1013: --
1014: Procedure reject_reg_request (p_reg_request_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE) is
1015: begin
1016:
1017: if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1018: FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1020: 'regRequestId: ' || p_reg_request_id);
1021: end if;
1022:
1023: -- Call approve_reject_reg_request to update the record in
1024: -- UMX_REG_REQUESTS table.
1025: approve_reject_reg_request (p_reg_request_id => p_reg_request_id,
1026: p_status_code => 'REJECTED');
1027:
1028: if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1122: p_transaction_type_id in varchar2 default null,
1123: x_approver_name out nocopy varchar2,
1124: x_approver_email out nocopy varchar2) is
1125:
1126: cursor get_req_request_info (p_reg_request_id in umx_reg_requests.reg_request_id%type) is
1127: select ame_application_id, ame_transaction_type_id
1128: from umx_reg_requests
1129: where reg_request_id = p_reg_request_id;
1130:
1124: x_approver_email out nocopy varchar2) is
1125:
1126: cursor get_req_request_info (p_reg_request_id in umx_reg_requests.reg_request_id%type) is
1127: select ame_application_id, ame_transaction_type_id
1128: from umx_reg_requests
1129: where reg_request_id = p_reg_request_id;
1130:
1131: l_current_approver ame_util.approverRecord2;
1132: l_application_id UMX_REG_REQUESTS.ame_application_id%type;
1128: from umx_reg_requests
1129: where reg_request_id = p_reg_request_id;
1130:
1131: l_current_approver ame_util.approverRecord2;
1132: l_application_id UMX_REG_REQUESTS.ame_application_id%type;
1133: l_transaction_type_id UMX_REG_REQUESTS.ame_transaction_type_id%type;
1134:
1135: l_role_info_tbl wf_directory.wf_local_roles_tbl_type;
1136:
1129: where reg_request_id = p_reg_request_id;
1130:
1131: l_current_approver ame_util.approverRecord2;
1132: l_application_id UMX_REG_REQUESTS.ame_application_id%type;
1133: l_transaction_type_id UMX_REG_REQUESTS.ame_transaction_type_id%type;
1134:
1135: l_role_info_tbl wf_directory.wf_local_roles_tbl_type;
1136:
1137: Begin
1149:
1150: -- Try to get the required parameters if they are not being passed when calling this API.
1151: if (l_application_id is null or
1152: l_transaction_type_id is null) then
1153: -- If any of these required variable is NULL, then we will query from UMX_REG_REQUESTS table
1154: if (p_reg_request_id is not null) then
1155: -- OK, we can query and get the required info.
1156: open get_req_request_info (p_reg_request_id);
1157: fetch get_req_request_info into l_application_id, l_transaction_type_id;
1242: -- Description: Justification
1243: --
1244: Procedure get_pend_acct_info (
1245: p_requester_user_id in FND_USER.USER_ID%TYPE default null,
1246: x_reg_request_id in out NOCOPY UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE,
1247: x_requested_for_username out NOCOPY FND_USER.USER_NAME%TYPE,
1248: x_approver_name out NOCOPY varchar2,
1249: x_approver_email_address out NOCOPY FND_USER.EMAIL_ADDRESS%TYPE,
1250: x_status_code out NOCOPY UMX_REG_REQUESTS.STATUS_CODE%TYPE,
1246: x_reg_request_id in out NOCOPY UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE,
1247: x_requested_for_username out NOCOPY FND_USER.USER_NAME%TYPE,
1248: x_approver_name out NOCOPY varchar2,
1249: x_approver_email_address out NOCOPY FND_USER.EMAIL_ADDRESS%TYPE,
1250: x_status_code out NOCOPY UMX_REG_REQUESTS.STATUS_CODE%TYPE,
1251: x_active_from out NOCOPY varchar2,
1252: x_active_to out NOCOPY varchar2,
1253: x_justification out NOCOPY UMX_REG_REQUESTS.JUSTIFICATION%TYPE) is
1254:
1249: x_approver_email_address out NOCOPY FND_USER.EMAIL_ADDRESS%TYPE,
1250: x_status_code out NOCOPY UMX_REG_REQUESTS.STATUS_CODE%TYPE,
1251: x_active_from out NOCOPY varchar2,
1252: x_active_to out NOCOPY varchar2,
1253: x_justification out NOCOPY UMX_REG_REQUESTS.JUSTIFICATION%TYPE) is
1254:
1255: l_application_id AME_CALLING_APPS.FND_APPLICATION_ID%TYPE;
1256: l_transaction_type_id AME_CALLING_APPS.TRANSACTION_TYPE_ID%TYPE;
1257:
1257:
1258: cursor get_reg_req_info_from_userid (p_user_id in FND_USER.USER_ID%TYPE) is
1259: select reg_request_id, status_code, ame_application_id,
1260: ame_transaction_type_id, requested_username, justification
1261: from umx_reg_requests
1262: where requested_for_user_id = p_user_id
1263: and requested_username is not null;
1264:
1265: cursor get_reg_req_info_from_regid (p_reg_req_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE) is
1261: from umx_reg_requests
1262: where requested_for_user_id = p_user_id
1263: and requested_username is not null;
1264:
1265: cursor get_reg_req_info_from_regid (p_reg_req_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE) is
1266: select status_code, ame_application_id, ame_transaction_type_id, justification
1267: from umx_reg_requests
1268: where reg_request_id = p_reg_req_id;
1269:
1263: and requested_username is not null;
1264:
1265: cursor get_reg_req_info_from_regid (p_reg_req_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE) is
1266: select status_code, ame_application_id, ame_transaction_type_id, justification
1267: from umx_reg_requests
1268: where reg_request_id = p_reg_req_id;
1269:
1270: cursor getUserName (l_user_id in fnd_user.user_id%type) is
1271: select user_name
1281: ' | regRequestId: ' || x_reg_request_id);
1282: end if;
1283:
1284: -- The first thing we have to do is to get the pending information from the
1285: -- UMX_REG_REQUESTS table.
1286: if (p_requester_user_id is not null) then
1287:
1288: -- Since the requester_user_id is not null, which means the request is a
1289: -- user account, query the reg req table base on the requester_user_id
1327: else
1328:
1329: -- There is an error while calling this API:
1330: -- All required input parameters are null
1331: raise_application_error ('-20000', 'Both p_requester_user_id and x_reg_request_id is null while calling UMX_REG_REQUESTS_PVT.get_pend_acct_info API.');
1332:
1333: end if;
1334:
1335: if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1340:
1341: -- Get Current Approver name and email address
1342: if (x_status_code = 'PENDING') then
1343: -- Status code could be 'VERIFYING', in that case, don't get the next approver.
1344: UMX_REG_REQUESTS_PVT.get_current_approver_info (p_reg_request_id => x_reg_request_id,
1345: p_application_id => l_application_id,
1346: p_transaction_type_id => l_transaction_type_id,
1347: x_approver_name => x_approver_name,
1348: x_approver_email => x_approver_email_address);
1426: -- Description: Justification
1427: --
1428: Procedure get_pend_acct_info_with_userid (
1429: p_requester_user_id in FND_USER.USER_ID%TYPE,
1430: x_reg_request_id out NOCOPY UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE,
1431: x_requested_for_username out NOCOPY FND_USER.USER_NAME%TYPE,
1432: x_approver_name out NOCOPY varchar2,
1433: x_approver_email_address out NOCOPY FND_USER.EMAIL_ADDRESS%TYPE,
1434: x_status_code out NOCOPY UMX_REG_REQUESTS.STATUS_CODE%TYPE,
1430: x_reg_request_id out NOCOPY UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE,
1431: x_requested_for_username out NOCOPY FND_USER.USER_NAME%TYPE,
1432: x_approver_name out NOCOPY varchar2,
1433: x_approver_email_address out NOCOPY FND_USER.EMAIL_ADDRESS%TYPE,
1434: x_status_code out NOCOPY UMX_REG_REQUESTS.STATUS_CODE%TYPE,
1435: x_active_from out NOCOPY varchar2,
1436: x_active_to out NOCOPY varchar2,
1437: x_justification out NOCOPY UMX_REG_REQUESTS.JUSTIFICATION%TYPE) is
1438:
1433: x_approver_email_address out NOCOPY FND_USER.EMAIL_ADDRESS%TYPE,
1434: x_status_code out NOCOPY UMX_REG_REQUESTS.STATUS_CODE%TYPE,
1435: x_active_from out NOCOPY varchar2,
1436: x_active_to out NOCOPY varchar2,
1437: x_justification out NOCOPY UMX_REG_REQUESTS.JUSTIFICATION%TYPE) is
1438:
1439: l_reg_request_id UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE;
1440:
1441: begin
1435: x_active_from out NOCOPY varchar2,
1436: x_active_to out NOCOPY varchar2,
1437: x_justification out NOCOPY UMX_REG_REQUESTS.JUSTIFICATION%TYPE) is
1438:
1439: l_reg_request_id UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE;
1440:
1441: begin
1442:
1443: if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1445: 'fnd.plsql.UMXVRRSB.get_pend_acct_info_with_userid.begin',
1446: 'p_requester_user_id: ' || p_requester_user_id);
1447: end if;
1448:
1449: UMX_REG_REQUESTS_PVT.get_pend_acct_info (
1450: p_requester_user_id => p_requester_user_id,
1451: x_reg_request_id => x_reg_request_id,
1452: x_requested_for_username => x_requested_for_username,
1453: x_approver_name => x_approver_name,
1500: -- x_justification
1501: -- Description: Justification
1502: --
1503: Procedure get_pend_acct_info_with_reqid (
1504: p_reg_request_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE,
1505: x_approver_name out NOCOPY varchar2,
1506: x_approver_email_address out NOCOPY FND_USER.EMAIL_ADDRESS%TYPE,
1507: x_status_code out NOCOPY UMX_REG_REQUESTS.STATUS_CODE%TYPE,
1508: x_active_from out NOCOPY varchar2,
1503: Procedure get_pend_acct_info_with_reqid (
1504: p_reg_request_id in UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE,
1505: x_approver_name out NOCOPY varchar2,
1506: x_approver_email_address out NOCOPY FND_USER.EMAIL_ADDRESS%TYPE,
1507: x_status_code out NOCOPY UMX_REG_REQUESTS.STATUS_CODE%TYPE,
1508: x_active_from out NOCOPY varchar2,
1509: x_active_to out NOCOPY varchar2,
1510: x_justification out NOCOPY UMX_REG_REQUESTS.JUSTIFICATION%TYPE) is
1511:
1506: x_approver_email_address out NOCOPY FND_USER.EMAIL_ADDRESS%TYPE,
1507: x_status_code out NOCOPY UMX_REG_REQUESTS.STATUS_CODE%TYPE,
1508: x_active_from out NOCOPY varchar2,
1509: x_active_to out NOCOPY varchar2,
1510: x_justification out NOCOPY UMX_REG_REQUESTS.JUSTIFICATION%TYPE) is
1511:
1512: l_reg_request_id UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE;
1513: l_requested_for_username FND_USER.USER_NAME%TYPE;
1514:
1508: x_active_from out NOCOPY varchar2,
1509: x_active_to out NOCOPY varchar2,
1510: x_justification out NOCOPY UMX_REG_REQUESTS.JUSTIFICATION%TYPE) is
1511:
1512: l_reg_request_id UMX_REG_REQUESTS.REG_REQUEST_ID%TYPE;
1513: l_requested_for_username FND_USER.USER_NAME%TYPE;
1514:
1515: begin
1516:
1521: end if;
1522:
1523: l_reg_request_id := p_reg_request_id;
1524:
1525: UMX_REG_REQUESTS_PVT.get_pend_acct_info (
1526: x_reg_request_id => l_reg_request_id,
1527: x_requested_for_username => l_requested_for_username,
1528: x_approver_name => x_approver_name,
1529: x_approver_email_address => x_approver_email_address,
1671: x_errstack out nocopy varchar2) IS
1672:
1673: cursor get_reg_req_id_with_user_id (l_user_id in fnd_user.user_id%type) is
1674: select reg_request_id
1675: from umx_reg_requests
1676: where requested_for_user_id = l_user_id;
1677:
1678: l_reg_req_id umx_reg_requests.reg_request_id%type;
1679:
1674: select reg_request_id
1675: from umx_reg_requests
1676: where requested_for_user_id = l_user_id;
1677:
1678: l_reg_req_id umx_reg_requests.reg_request_id%type;
1679:
1680: BEGIN
1681:
1682: if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1740: --
1741: -- Output Parameter:
1742: -- It will return 'Y' if pending account has error and 'N' if otherwise.
1743: --
1744: function is_pend_request_error (p_reg_request_id in umx_reg_requests.reg_request_id%type) return varchar2 is
1745:
1746: l_itemtype wf_items.item_type%type;
1747: l_itemkey wf_items.item_key%type;
1748: l_status varchar2(8);
1839: end if;
1840:
1841: END is_pend_account_error;
1842:
1843: END UMX_REG_REQUESTS_PVT;