[Home] [Help]
PACKAGE BODY: APPS.UMX_USER_SEARCH_PVT
Source
1 PACKAGE BODY UMX_USER_SEARCH_PVT AS
2 /* $Header: UMXUSRSB.pls 120.3 2006/03/22 12:10:26 cmehta noship $ */
3
4 FUNCTION getStatusCode(p_user_id IN varchar2,
5 p_start_date IN date,
6 p_end_date IN date ) RETURN VARCHAR2 IS
7
8 l_lookup_code varchar2(30) := 'UNASSIGNED';
9 cursor get_pwd is select ENCRYPTED_USER_PASSWORD from fnd_user
10 where user_id = p_user_id;
11 l_encrypted_user_password FND_USER.ENCRYPTED_USER_PASSWORD%TYPE;
12
13 BEGIN
14
15 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
16 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
17 'fnd.plsql.UMXUSRSB.getStatusCode.begin',
18 'userId: ' || p_user_id ||
19 ' | startDate: ' || p_start_date ||
20 ' | endDate: ' || p_end_date);
21 end if;
22
23 open get_pwd;
24 fetch get_pwd into l_encrypted_user_password;
25 close get_pwd;
26
27 if ( p_user_id is null ) then
28 l_lookup_code := 'UNASSIGNED';
29 elsif ( to_char( p_start_date, 'MM/DD/YYYY HH24:MI' ) = to_char( FND_API.G_MISS_DATE, 'MM/DD/YYYY HH24:MI' )
30 and to_char( p_end_date, 'MM/DD/YYYY HH24:MI' ) = to_char( FND_API.G_MISS_DATE, 'MM/DD/YYYY HH24:MI' ) ) then
31 l_lookup_code := 'PENDING';
32 elsif l_encrypted_user_password = 'INVALID' then
33 l_lookup_code := 'LOCKED';
34 elsif (p_start_date is not null and p_start_date <= sysdate) and
35 (p_end_date is null or p_end_date > sysdate ) then
36 l_lookup_code := 'ACTIVE';
37 else
38 l_lookup_code := 'INACTIVE';
39 end if;
40
41 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
42 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
43 'fnd.plsql.UMXUSRSB.getStatusCode.end',
44 'lookupCode: ' || l_lookup_code);
45 end if;
46
47 return l_lookup_code;
48
49 END getStatusCode;
50
51 FUNCTION canResetPassword(userName IN varchar2 default null,
52 funcName in varchar2,
53 object_name varchar2,
54 obj_pk_val varchar2 ) RETURN varchar2 IS
55 "Return Value" BOOLEAN;
56 l_return_value_str varchar2 (5);
57 BEGIN
58 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
59 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
60 'fnd.plsql.UMXUSRSB.canResetPassword.begin',
61 'userName: ' || userName ||
62 ' | funcName: ' || funcName ||
63 ' | objectName: ' || object_name ||
64 ' | objPkVal: ' || obj_pk_val);
65 end if;
66
67 "Return Value" := fnd_function.test_instance (funcName,
68 object_name,
69 obj_pk_val,
70 NULL,
71 NULL,
72 NULL,
73 NULL,
74 userName);
75
76 if ("Return Value") then
77 l_return_value_str := 'TRUE';
78 else
79 l_return_value_str := 'FALSE';
80 end if;
81
82 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
83 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
84 'fnd.plsql.UMXUSRSB.canResetPassword.end',
85 'returnValue: ' || l_return_value_str);
86 end if;
87
88 -- Output the results
89 return l_return_value_str;
90 END;
91
92 FUNCTION getAccountStatus(p_user_id IN varchar2,
93 p_start_date IN date,
94 p_end_date IN date ) RETURN varchar2 IS
95 l_status varchar2(30) := 'UNKNOWN';
96 l_lookup_code varchar2 (100);
97
98 cursor get_lookup_meaning (lookup_code in varchar2) is
99 select meaning
100 from fnd_lookup_values_vl
101 where lookup_type = 'UMX_USER_ACC_DET_STATUS'
102 and lookup_code = l_lookup_code;
103
104 BEGIN
105
106 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
107 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
108 'fnd.plsql.UMXUSRSB.getAccountStatus.begin',
109 'userId: ' || p_user_id ||
110 ' | startDate: ' || p_start_date ||
111 ' | endDate: ' || p_end_date);
112 end if;
113
114 l_lookup_code := getStatusCode (p_user_id => p_user_id,
115 p_start_date => p_start_date,
116 p_end_date => p_end_date);
117
118 open get_lookup_meaning (l_lookup_code);
119 fetch get_lookup_meaning into l_status;
120 close get_lookup_meaning;
121
122 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
123 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
124 'fnd.plsql.UMXUSRSB.getAccountStatus.end',
125 'status: ' || l_status);
126 end if;
127
128 if ( l_status is not null ) then
129 return l_status;
130 end if;
131 END;
132
133 FUNCTION getAccountStatusCode(p_user_id IN varchar2,
134 p_start_date IN date,
135 p_end_date IN date ) RETURN varchar2 IS
136
137 begin
138 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
139 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
140 'fnd.plsql.UMXUSRSB.getAccountStatusCode.begin',
141 'userId: ' || p_user_id ||
142 ' | startDate: ' || p_start_date ||
143 ' | endDate: ' || p_end_date);
144 end if;
145
146 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
147 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
148 'fnd.plsql.UMXUSRSB.getAccountStatusCode.end', '');
149 end if;
150
151 return getStatusCode(p_user_id => p_user_id,
152 p_start_date => p_start_date,
153 p_end_date =>p_end_date);
154
155 end getAccountStatusCode;
156
157 END UMX_USER_SEARCH_PVT;