[Home] [Help]
PACKAGE BODY: APPS.POS_USER_REG_HELPER_PKG
Source
1 PACKAGE BODY pos_user_reg_helper_pkg AS
2 /* $Header: POSUSRHB.pls 120.0.12020000.2 2013/04/03 10:27:44 svalampa ship $ */
3
4 PROCEDURE gen_reg_key
5 (p_registration_id IN NUMBER,
6 x_registration_key OUT NOCOPY VARCHAR2,
7 x_return_status OUT NOCOPY VARCHAR2,
8 x_error OUT NOCOPY VARCHAR2
9 )
10 IS
11 l_count NUMBER;
12 l_random VARCHAR2(1000);
13 l_max_run INTEGER;
14
15 CURSOR l_cur (p_reg_key IN VARCHAR2) IS
16 SELECT registration_id
17 FROM fnd_registrations
18 WHERE registration_key = p_reg_key;
19
20 l_id NUMBER;
21 l_registration_key fnd_registrations.registration_key%TYPE;
22
23 BEGIN
24
25 -- This procedure uses wf_core to generate the registration key.
26 -- This approach is different from the code in java/schema/FndRegistrationEOImpl.java.
27 -- We can not use that approach as this is in PLSQL.
28
29 -- loop 20 times to generate registration key
30 l_count := 0;
31 l_max_run := 20;
32
33 WHILE TRUE LOOP
34 l_count := l_count + 1;
35 IF l_count > l_max_run THEN
36 -- it is unlikely we can not find a unique reg key after several tries, but raise exception if it happens
37 x_return_status := 'E';
38 x_error := 'Can not generate a unique registration key';
39 END IF;
40
41 l_random := wf_core.random;
42
43 -- the registration key is varchar2(100) in fnd_registrations table
44
45 l_registration_key := Substr(Substr(l_random,1,4) || To_char(Sysdate,'MMDDYYYYMISS') ||
46 To_char(p_registration_id) || wf_core.random,1,100);
47
48 -- check if the registration key already exists, and if not, we got a good key
49 OPEN l_cur(l_registration_key);
50 FETCH l_cur INTO l_id;
51 IF l_cur%found THEN
52 CLOSE l_cur;
53 ELSE
54 CLOSE l_cur;
55 EXIT;
56 END IF;
57 END LOOP;
58
59 -- dbms_output.put_line('get ' || l_registration_key || ' for id ' || l_registration_id);
60
61 x_registration_key := l_registration_key;
62 x_return_status := 'S';
63
64 END gen_reg_key;
65
66 PROCEDURE invite_supplier_user
67 (p_vendor_id IN NUMBER,
68 p_email_address IN VARCHAR2,
69 p_isp_flag IN VARCHAR2 DEFAULT 'Y',
70 p_sourcing_flag IN VARCHAR2 DEFAULT 'N',
71 p_cp_flag IN VARCHAR2 DEFAULT 'N',
72 p_note IN VARCHAR2 DEFAULT NULL,
73 x_return_status OUT NOCOPY VARCHAR2,
74 x_error OUT NOCOPY VARCHAR2,
75 x_registration_id OUT NOCOPY NUMBER
76 )
77 IS
78
79 l_reg_type VARCHAR2(10);
80 l_app_id NUMBER;
81
82 l_registration_id NUMBER;
83 l_registration_key fnd_registrations.registration_key%TYPE;
84 l_supplier_name po_vendors.vendor_name%TYPE;
85 l_return_status VARCHAR2(100);
86
87 BEGIN
88
89 SAVEPOINT pos_user_reg_helper_sp1;
90
91 BEGIN
92 SELECT vendor_name INTO l_supplier_name FROM po_vendors WHERE vendor_id = p_vendor_id;
93 EXCEPTION
94 WHEN no_data_found THEN
95 dbms_output.put_line('Error: can not find record in po_vendors for vendor id ' || p_vendor_id);
96 RETURN;
97 END;
98
99 l_reg_type := 'POS_REG';
100 l_app_id := 177;
101
102 -- FP of the bug 16390037
103 -- Add p_language_code parameter to the below call
104
105 l_registration_id := fnd_registration_pkg.insert_fnd_reg
106 (
107 p_application_id => l_app_id,
108 p_party_id => NULL,
109 p_registration_type => l_reg_type,
110 p_requested_user_name => NULL,
111 p_assigned_user_name => NULL,
112 p_registration_status => 'INVITED',
113 p_exists_in_fnd_user_flag => 'N',
114 p_email => p_email_address,
115 p_language_code => USERENV('LANG')
116 );
117
118 gen_reg_key (p_registration_id => l_registration_id,
119 x_registration_key => l_registration_key,
120 x_return_status => l_return_status,
121 x_error => x_error
122 );
123
124 IF l_return_status IS NULL OR l_return_status <> 'S' THEN
125 x_return_status := 'E';
126 x_error := 'Unable to generate registration key';
127 ROLLBACK TO pos_user_reg_helper_sp1;
128 RETURN;
129 END IF;
130
131 UPDATE fnd_registrations SET registration_key = l_registration_key WHERE registration_id = l_registration_id;
132
133 -- create details rows similar to the invite supplier user UI
134 -- Note: some rows are created with null values as in the UI
135 -- Dont think it matters but just to be consistent
136
137 fnd_registration_pkg.insert_fnd_reg_details
138 (
139 p_registration_id => l_registration_id,
140 p_application_id => l_app_id,
141 p_registration_type => l_reg_type,
142 p_field_name => 'Supplier Name',
143 p_field_type => NULL,
144 p_field_format => NULL,
145 p_field_value_string => l_supplier_name,
146 p_field_value_number => NULL,
147 p_field_value_date => NULL
148 );
149
150 -- FP of the bug 16390037
151 -- pass p_vendor_id as parameter
152
153 fnd_registration_pkg.insert_fnd_reg_details
154 (
155 p_registration_id => l_registration_id,
156 p_application_id => l_app_id,
157 p_registration_type => l_reg_type,
158 p_field_name => 'Supplier Number',
159 p_field_type => NULL,
160 p_field_format => NULL,
161 p_field_value_string => NULL,
162 p_field_value_number => p_vendor_id,
163 p_field_value_date => NULL
164 );
165
166 fnd_registration_pkg.insert_fnd_reg_details
167 (
168 p_registration_id => l_registration_id,
169 p_application_id => l_app_id,
170 p_registration_type => l_reg_type,
171 p_field_name => 'Sourcing',
172 p_field_type => NULL,
173 p_field_format => NULL,
174 p_field_value_string => p_sourcing_flag,
175 p_field_value_number => NULL,
176 p_field_value_date => NULL
177 );
178
179 fnd_registration_pkg.insert_fnd_reg_details
180 (
181 p_registration_id => l_registration_id,
182 p_application_id => l_app_id,
183 p_registration_type => l_reg_type,
184 p_field_name => 'ISP',
185 p_field_type => NULL,
186 p_field_format => NULL,
187 p_field_value_string => p_isp_flag,
188 p_field_value_number => NULL,
189 p_field_value_date => NULL
190 );
191
192 fnd_registration_pkg.insert_fnd_reg_details
193 (
194 p_registration_id => l_registration_id,
195 p_application_id => l_app_id,
196 p_registration_type => l_reg_type,
197 p_field_name => 'CollaborativePlanning',
198 p_field_type => NULL,
199 p_field_format => 'Y|N',
200 p_field_value_string => p_cp_flag,
201 p_field_value_number => NULL,
202 p_field_value_date => NULL
203 );
204
205 fnd_registration_pkg.insert_fnd_reg_details
206 (
207 p_registration_id => l_registration_id,
208 p_application_id => l_app_id,
209 p_registration_type => l_reg_type,
210 p_field_name => 'Job Title',
211 p_field_type => NULL,
212 p_field_format => NULL,
213 p_field_value_string => NULL,
214 p_field_value_number => NULL,
215 p_field_value_date => NULL
216 );
217
218 fnd_registration_pkg.insert_fnd_reg_details
219 (
220 p_registration_id => l_registration_id,
221 p_application_id => l_app_id,
222 p_registration_type => l_reg_type,
223 p_field_name => 'Note',
224 p_field_type => NULL,
225 p_field_format => NULL,
226 p_field_value_string => p_note,
227 p_field_value_number => NULL,
228 p_field_value_date => NULL
229 );
230
231 fnd_registration_pkg.insert_fnd_reg_details
232 (
233 p_registration_id => l_registration_id,
234 p_application_id => l_app_id,
235 p_registration_type => l_reg_type,
236 p_field_name => 'User Access',
237 p_field_type => NULL,
238 p_field_format => NULL,
239 p_field_value_string => NULL,
240 p_field_value_number => NULL,
241 p_field_value_date => NULL
242 );
243
244 fnd_registration_pkg.insert_fnd_reg_details
245 (
246 p_registration_id => l_registration_id,
247 p_application_id => l_app_id,
248 p_registration_type => l_reg_type,
249 p_field_name => 'Restrict Access',
250 p_field_type => NULL,
251 p_field_format => NULL,
252 p_field_value_string => NULL,
253 p_field_value_number => NULL,
254 p_field_value_date => NULL
255 );
256
257 fnd_registration_pkg.insert_fnd_reg_details
258 (
259 p_registration_id => l_registration_id,
260 p_application_id => l_app_id,
261 p_registration_type => l_reg_type,
262 p_field_name => 'Approver ID',
263 p_field_type => NULL,
264 p_field_format => NULL,
265 p_field_value_string => NULL,
266 p_field_value_number => fnd_global.user_id,
267 p_field_value_date => NULL
268 );
269
270 fnd_registration_pkg.insert_fnd_reg_details
271 (
272 p_registration_id => l_registration_id,
273 p_application_id => l_app_id,
274 p_registration_type => l_reg_type,
275 p_field_name => 'Invited Flag',
276 p_field_type => NULL,
277 p_field_format => NULL,
278 p_field_value_string => 'Y',
279 p_field_value_number => NULL,
280 p_field_value_date => NULL
281 );
282
283 dbms_output.put_line('reg id ' || l_registration_id);
284
285 l_return_status := fnd_registration_utils_pkg.publish_invitation_event(l_registration_id);
286
287 IF l_return_status IS NOT NULL AND l_return_status = 'Y' THEN
288 x_return_status := 'S';
289 x_error := NULL;
290 x_registration_id := l_registration_id;
291 ELSE
292 ROLLBACK TO pos_user_reg_helper_sp1;
293 x_return_status := 'E';
294 x_error := 'Unable to publish invitation event';
295 END IF;
296
297 RETURN;
298
299 EXCEPTION
300 WHEN OTHERS THEN
301 ROLLBACK TO pos_user_reg_helper_sp1;
302 RAISE;
303 END;
304
305 END pos_user_reg_helper_pkg;