1 package body WF_DIRECTORY_PART_UTIL as
2 /* $Header: wfdpub.pls 120.1 2005/07/02 03:45:45 appldev noship $ */
3
4 procedure validate_display_name (
5 p_display_name in varchar2,
6 p_user_name in out nocopy varchar2) IS
7
8 l_colon NUMBER := 0;
9 l_names_count NUMBER := 0;
10 l_name VARCHAR2(320);
11 l_upper_name VARCHAR2(360);
12 l_orig_system_id NUMBER;
13 l_get_role BOOLEAN := TRUE;
14
15 role_info_tbl wf_directory.wf_local_roles_tbl_type;
16
17 --Bug 3626135
18 --Added the order by clause to recterive the record
19 --based on status and start date in ascending order
20 cursor r_name_lov is
21 select NAME
22 from WF_ROLE_LOV_VL
23 where DISPLAY_NAME = p_display_name
24 order by status, start_date;
25
26 BEGIN
27
28 /*
29 ** Make sure to blank out the internal name if the user originally
30 ** used the LOV to select the name and then blanked out the display
31 ** name then make sure here to blank out the insternal name and return
32 */
33 if (p_display_name is null) then
34
35 p_user_name := NULL;
36 return;
37
38 end if;
39
40 /*
41 ** Bug# 2236250 validating the display name to contain a valid number
42 ** after the colon to be used as a internal name for the role
43 */
44 l_colon := instr(p_display_name, ':');
45 if (l_colon > 0) then
46 begin
47 l_orig_system_id := to_number(substr(p_display_name, l_colon+1));
48 exception
49 when value_error then
50 l_get_role := FALSE;
51 when others then
52 raise;
53 end;
54 l_colon := 0;
55 end if;
56
57 /*
58 ** First look first for internal name to see if you find a match. If
59 ** there are duplicate internal names that match the criteria then
60 ** there is a problem with directory services but what can you do. Go
61 ** ahead and pick the first name so you return something
62 **
63 ** Bug# 2236250 calling Wf_Directory.GetRoleInfo2 only if the value
64 ** after ':' is numeric.
65 */
66 if (l_get_role) then
67 Wf_Directory.GetRoleInfo2(upper(p_display_name),role_info_tbl);
68 l_name := role_info_tbl(1).name;
69 end if;
70
71 /*
72 ** If you found a match on internal name then set the p_user_name
73 ** accordingly.
74 */
75 if (l_name IS NOT NULL) then
76
77 p_user_name := l_name;
78
79 /*
80 ** If there was no match on internal name then check for a display
81 ** name
82 */
83 else
84
85 open r_name_lov;
86 loop
87
88 /*
89 ** Check out how many names match the display name
90 */
91 fetch r_name_lov into l_name;
92 /*
93 ** If there are no matches for the display name then raise an error
94 */
95 if (r_name_lov%ROWCOUNT = 0) then
96 close r_name_lov;
97 -- Not displayed or internal role name, error
98 wf_core.token('ROLE', p_display_name);
99 wf_core.raise('WFNTF_ROLE');
100 end if;
101
102 exit when r_name_lov%NOTFOUND;
103
104 /*
105 ** If there is more than one match then see if the user
106 ** used the lov to select the name in which case the combination
107 ** of the display name and the user name should be unique
108 */
109 if (r_name_lov%ROWCOUNT > 1) then
110 close r_name_lov;
111
112 -- copy logic from wf_directory.getroleinfo2
113 l_colon := instr(p_user_name,':');
114
115 if (l_colon = 0) then
116 select count(1)
117 into l_names_count
118 from WF_ROLES
119 where NAME = p_user_name
120 and ORIG_SYSTEM not in ('HZ_PARTY','POS','ENG_LIST','AMV_CHN',
121 'HZ_GROUP','CUST_CONT')
122 and DISPLAY_NAME = p_display_name;
123 else
124 /*
125 ** Bug# 2236250 validate if the value after ':' is number
126 ** before using it in the query
127 */
128 begin
129 l_orig_system_id := to_number(substr(p_user_name, l_colon+1));
130 exception
131 when value_error then
132 wf_core.raise('WFNTF_ORIGSYSTEMID');
133 when others then
134 raise;
135 end;
136 select count(1)
137 into l_names_count
138 from WF_ROLES
139 where NAME = p_user_name
140 and ORIG_SYSTEM = substr(p_user_name, 1, l_colon-1)
141 and ORIG_SYSTEM_ID = l_orig_system_id
142 and DISPLAY_NAME = p_display_name;
143 end if;
144
145 if (l_names_count <> 1) then
146 wf_core.token('ROLE', p_display_name);
147 wf_core.raise('WFNTF_UNIQUE_ROLE');
148 end if;
149
150 exit;
151 end if;
152
153 /*
154 ** If there is just one match then get the internal name
155 ** and assign it.
156 */
157 p_user_name := l_name;
158 end loop;
159 if (r_name_lov%ISOPEN) then
160 close r_name_lov;
161 end if;
162 end if;
163
164 exception
165 when others then
166 if (r_name_lov%ISOPEN) then
167 close r_name_lov;
168 end if;
169 wf_core.context('Wf_Directory_Part_Util', 'validate_display_name',
170 p_display_name, p_user_name);
171 raise;
172 end validate_display_name;
173
174 end WF_DIRECTORY_PART_UTIL;