1 PACKAGE BODY POS_PARTY_MANAGEMENT_PKG as
2 --$Header: POSPMNGB.pls 120.9.12010000.2 2008/09/11 19:12:21 jburugul ship $
3
4 PROCEDURE classify_party
5 ( p_party_id IN NUMBER
6 , p_category IN VARCHAR2
7 , p_code IN VARCHAR2
8 , p_primary_flag IN VARCHAR2
9 , x_code_assignment_id OUT NOCOPY NUMBER
10 , x_status OUT NOCOPY VARCHAR2
11 , x_exception_msg OUT NOCOPY VARCHAR2
12 )
13 IS
14 l_return_status VARCHAR2(1);
15 l_msg_count NUMBER;
16 l_msg_data VARCHAR2(3000);
17 l_number NUMBER;
18
19 l_code_assignment_rec hz_classification_v2pub.code_assignment_rec_type;
20
21 CURSOR l_cur IS
22 SELECT code_assignment_id
23 FROM hz_code_assignments
24 WHERE owner_table_name = 'HZ_PARTIES'
25 AND owner_table_id = p_party_id
26 AND class_category = p_category
27 AND class_code = p_code
28 AND status = 'A'
29 AND (end_date_active is null or end_date_active > sysdate);
30
31 BEGIN
32 --fnd_client_info.set_org_context('-3113');
33 OPEN l_cur;
34 FETCH l_cur INTO x_code_assignment_id;
35 IF l_cur%found THEN
36 CLOSE l_cur;
37 x_status := fnd_api.g_ret_sts_success;
38 RETURN;
39 END IF;
40 CLOSE l_cur;
41
42 l_code_assignment_rec.owner_table_name := 'HZ_PARTIES';
43 l_code_assignment_rec.owner_table_id := p_party_id;
44 l_code_assignment_rec.class_category := p_category;
45 l_code_assignment_rec.class_code := p_code;
46 l_code_assignment_rec.primary_flag := p_primary_flag;
47 l_code_assignment_rec.content_source_type := 'USER_ENTERED';
48 l_code_assignment_rec.start_date_active := Sysdate;
49 l_code_assignment_rec.status := 'A';
50 l_code_assignment_rec.created_by_module := 'POS_SUPPLIER_MGMT';
51 l_code_assignment_rec.application_id := 177;
52
53 hz_classification_v2pub.create_code_assignment
54 ( FND_API.G_FALSE,
55 l_code_assignment_rec,
56 l_return_status,
57 l_msg_count,
58 l_msg_data,
59 x_code_assignment_id
60 );
61
62 pos_log.log_call_result
63 ( p_module => 'POSPMNGB',
64 p_prefix => 'in classify_party',
65 p_return_status => l_return_status,
66 p_msg_count => l_msg_count,
67 p_msg_data => l_msg_data
68 );
69
70 x_status := l_return_status;
71 IF l_msg_count = 1 THEN
72 x_exception_msg := l_msg_data;
73 ELSIF l_msg_count > 1 THEN
74 pos_log.combine_fnd_msg(l_msg_count, x_exception_msg);
75 END IF;
76 END classify_party;
77
78 PROCEDURE classify_party
79 ( p_party_id IN NUMBER
80 , p_category IN VARCHAR2
81 , p_code IN VARCHAR2
82 , x_status OUT NOCOPY VARCHAR2
83 , x_exception_msg OUT NOCOPY VARCHAR2
84 )
85 IS
86 l_code_assignment_id number;
87 BEGIN
88
89 classify_party
90 (p_party_id,
91 p_category,
92 p_code,
93 'Y',
94 l_code_assignment_id ,
95 x_status,
96 x_exception_msg
97 );
98
99 END classify_party;
100
101 PROCEDURE pos_create_organization
102 (p_organization_name IN VARCHAR2,
103 p_duns_number IN NUMBER DEFAULT NULL,
104 p_corp_hq_flag IN VARCHAR2 DEFAULT NULL,
105 p_sic_code IN VARCHAR2 DEFAULT NULL,
106 x_org_party_id OUT NOCOPY NUMBER,
107 x_org_party_number OUT NOCOPY VARCHAR2,
108 x_profile_id OUT NOCOPY NUMBER,
109 x_exception_msg OUT NOCOPY VARCHAR2,
110 x_status OUT NOCOPY VARCHAR2
111 )
112 IS
113 l_org_rec hz_party_v2pub.organization_rec_type;
114 l_return_status VARCHAR2(1);
115 l_msg_count NUMBER;
116 l_msg_data VARCHAR2(3000);
117
118 /* Added for bug 7366321 */
119 l_hzprofile_value varchar2(20);
120 l_hzprofile_changed varchar2(1) := 'N';
121 /* End */
122
123 BEGIN
124 /* Added for bug 7366321 */
125 l_hzprofile_value := fnd_profile.value('HZ_GENERATE_PARTY_NUMBER');
126 if nvl(l_hzprofile_value, 'Y') = 'N' then
127 fnd_profile.put('HZ_GENERATE_PARTY_NUMBER', 'Y');
128 l_hzprofile_changed := 'Y';
129 end if;
130 /* End */
131 /* commented for bug 7366321
132 fnd_profile.put('HZ_GENERATE_PARTY_NUMBER','Y');
133 */
134 l_org_rec.organization_name := p_organization_name;
135 l_org_rec.duns_number_c := p_duns_number;
136 l_org_rec.hq_branch_ind := p_corp_hq_flag;
137 l_org_rec.sic_code := p_sic_code;
138 l_org_rec.created_by_module := 'POS_SUPPLIER_MGMT';
139
140 hz_party_v2pub.create_organization
141 (p_init_msg_list => fnd_api.g_false,
142 p_organization_rec => l_org_rec,
143 x_return_status => l_return_status,
144 x_msg_count => l_msg_count,
145 x_msg_data => l_msg_data,
146 x_party_id => x_org_party_id,
147 x_party_number => x_org_party_number,
148 x_profile_id => x_profile_id
149 );
150 /* Added for bug 7366321 */
151 if nvl(l_hzprofile_changed,'N') = 'Y' then
152 fnd_profile.put('HZ_GENERATE_PARTY_NUMBER', l_hzprofile_value);
153 l_hzprofile_changed := 'N';
154 end if;
155 /* End */
156 pos_log.log_call_result
157 (p_module => 'POSENTRB',
158 p_prefix => 'call hz_party_v2pub.create_organization',
159 p_return_status => l_return_status,
160 p_msg_count => l_msg_count,
161 p_msg_data => l_msg_data
162 );
163
164 IF l_return_status IS NULL OR l_return_status <> fnd_api.g_ret_sts_success THEN
165 /* Added for bug 7366321 */
166 if nvl(l_hzprofile_changed,'N') = 'Y' then
167 fnd_profile.put('HZ_GENERATE_PARTY_NUMBER', l_hzprofile_value);
168 l_hzprofile_changed := 'N';
169 end if;
170 /* End */
171 IF l_msg_count = 1 THEN
172 x_exception_msg := l_msg_data;
173 ELSIF l_msg_count > 1 THEN
174 pos_log.combine_fnd_msg(l_msg_count, x_exception_msg);
175 ELSE
176 x_exception_msg := 'unknown error when calling hz_party_v2pub.create_organization';
177 END IF;
178 raise_application_error(-20001, x_exception_msg, true);
179 END IF;
180
181 classify_party (x_org_party_id,
182 'POS_CLASSIFICATION',
183 'PROCUREMENT_ORGANIZATION',
184 x_status,
185 x_exception_msg
186 );
187
188 IF (x_status <> 'S') THEN
189 raise_application_error(-20002, x_exception_msg, TRUE);
190 END IF;
191
192 x_status := 'S';
193 x_exception_msg := NULL;
194 RETURN;
195
196 END pos_create_organization;
197
198 PROCEDURE create_email_address
199 ( p_partyid IN NUMBER
200 , p_emailaddress IN VARCHAR2
201 )
202 IS
203 l_contact_point_id NUMBER;
204 l_contact_points_rec hz_contact_point_v2pub.contact_point_rec_type;
205 l_email_rec hz_contact_point_v2pub.email_rec_type;
206 l_return_status VARCHAR2(1);
207 l_msg_count NUMBER;
208 l_msg_data VARCHAR2(3000);
209
210 CURSOR l_cur IS
211 SELECT 1
212 FROM hz_contact_points
213 WHERE owner_table_id = p_partyid
214 AND upper(email_address) = upper(p_emailaddress)
215 AND owner_table_name = 'HZ_PARTIES'
216 AND status = 'A'
217 AND contact_point_type = 'EMAIL';
218
219 l_number NUMBER;
220 BEGIN
221 IF ( p_emailaddress IS NULL ) THEN
222 RETURN;
223 END IF;
224
225 OPEN l_cur;
226 FETCH l_cur INTO l_number;
227 IF l_cur%found THEN
228 CLOSE l_cur;
229 RETURN; -- kind of strange to return here but this is 11.5.10 logic
230 END IF;
231 CLOSE l_cur;
232
233 l_email_rec.email_format := 'MAILTEXT';
234 l_email_rec.email_address := p_emailaddress;
235 l_contact_points_rec.contact_point_type := 'EMAIL';
236 l_contact_points_rec.status := 'A';
237 l_contact_points_rec.owner_table_name := 'HZ_PARTIES';
238 l_contact_points_rec.owner_table_id := p_partyId;
239 l_contact_points_rec.created_by_module := 'POS_SUPPLIER_MGMT';
240
241 hz_contact_point_v2pub.create_contact_point
242 ( p_init_msg_list => FND_API.G_FALSE,
243 p_contact_point_rec => l_contact_points_rec,
244 p_email_rec => l_email_rec,
245 x_contact_point_id => l_contact_point_id,
246 x_return_status => l_return_status,
247 x_msg_count => l_msg_count,
248 x_msg_data => l_msg_data
249 );
250
251 pos_log.log_call_result
252 ( p_module => 'POSPMNGB',
253 p_prefix => 'in create_email_address',
254 p_return_status => l_return_status,
255 p_msg_count => l_msg_count,
256 p_msg_data => l_msg_data
257 );
258
259 IF l_return_status IS NULL OR l_return_status <> fnd_api.g_ret_sts_success THEN
260 IF l_msg_count > 1 THEN
261 pos_log.combine_fnd_msg(l_msg_count, l_msg_data);
262 ELSE
263 l_msg_data := 'unknown error when calling hz_party_v2pub.create_organization';
264 END IF;
265 raise_application_error(-20001, l_msg_data, true);
266 END IF;
267 END create_email_address;
268
269 -- in release 12, this procedure should not be used to create supplier user
270 -- it should be used for boot strap enterprise user for Sourcing
271 PROCEDURE pos_create_user
272 (p_username IN VARCHAR2,
273 p_firstname IN VARCHAR2,
274 p_lastname IN VARCHAR2,
275 p_emailaddress IN VARCHAR2,
276 x_party_id OUT NOCOPY NUMBER, -- party id of the user
277 x_exception_msg OUT NOCOPY VARCHAR2,
278 x_status OUT NOCOPY VARCHAR2
279 )
280 IS
281 l_lastname varchar2(240);
282 l_return_status VARCHAR2(1);
283 l_msg_count NUMBER;
284 l_msg_data VARCHAR2(3000);
285 BEGIN
286
287 IF( (p_firstname IS null) AND (p_lastname IS null) ) THEN
288 l_lastname := '__SUPPLIER__';
289 ELSE
290 l_lastname := p_lastname;
291 END IF;
292
293 -- Create the user without mail and then we set the e-mail.
294 -- The reason is because the TCA tries to find the user with
295 -- same e-mail address, and will reuse the party.
296 -- This will cause problem in our case as the vendor users typically
297 -- could share e-mail addresses ( as the e-mail is stored in site
298 -- tables but not in po_contact tables).
299 hz_user_party_utils.get_user_party_id
300 (p_username,
301 p_firstname,
302 l_lastname,
303 NULL,
304 x_party_id,
305 x_status
306 );
307
308 IF x_status IS NULL OR x_status <> 'S' THEN
309 l_msg_count := fnd_msg_pub.count_msg;
310 pos_log.combine_fnd_msg(l_msg_count, x_exception_msg);
311 raise_application_error(-20001, x_exception_msg, true);
312 END IF;
313
314 fnd_user_pkg.updateuser
315 ( x_user_name => p_username,
316 x_owner => NULL,
317 x_customer_id => x_party_id
318 );
319
320 classify_party
321 (x_party_id,
322 'POS_CLASSIFICATION',
323 'PROCUREMENT_USER',
324 x_status,
325 x_exception_msg
326 );
327
328 IF x_status IS NULL OR x_status <> 'S' THEN
329 x_exception_msg := 'Setting the party as PROCUREMENT_USER for party_id= '
330 || x_party_id || ' ' || x_exception_msg;
331 raise_application_error(-20001, x_exception_msg, true);
332 END IF;
333
334 create_email_address(x_party_id, p_emailaddress);
335 x_status := 'S';
336 x_exception_msg := NULL;
337
338 END pos_create_user;
339
340 -- return party_id of the supplier party of a supplier user;
341 -- if the user has more than one supplier parties, return -2
342 -- if the user has no supplier party, return -1
343 FUNCTION check_for_vendor_user(p_username IN VARCHAR2)
344 RETURN NUMBER IS
345 l_count NUMBER;
346 l_party_id NUMBER;
347 BEGIN
348 l_count := 0;
349 FOR x IN (SELECT DISTINCT vendor_party_id
350 FROM pos_supplier_users_v
351 WHERE user_name = p_username
352 ORDER BY 1
353 ) LOOP
354
355 l_count := l_count + 1;
356 l_party_id := x.vendor_party_id;
357 IF l_count > 1 THEN
358 EXIT;
359 END IF;
360 END LOOP;
361
362 IF l_count = 1 THEN
363 RETURN l_party_id;
364 END IF;
365
366 IF l_count = 0 THEN
367 RETURN -1;
368 END IF;
369
370 IF l_count > 1 THEN
371 RETURN -2;
372 END IF;
373 END check_for_vendor_user;
374
375 FUNCTION check_for_enterprise_user(p_username IN VARCHAR2)
376 RETURN NUMBER IS
377 l_party_id number;
378 BEGIN
379 select distinct hz2.party_id
380 into l_party_id
381 from fnd_user f, hz_parties hz1, hz_parties hz2,
382 hz_relationships hzr1, hz_code_assignments hca
383 where f.person_party_id = hz1.party_id
384 and f.user_name = p_username
385 and hzr1.object_id = hz2.party_id
386 and hzr1.subject_id = hz1.party_id
387 and hzr1.relationship_type = 'POS_EMPLOYMENT'
388 and hzr1.relationship_code = 'EMPLOYEE_OF'
389 and hzr1.start_date <= sysdate
390 and hzr1.end_date >= sysdate
391 and hca.owner_table_id = hz2.party_id
392 and hca.owner_table_name = 'HZ_PARTIES'
393 and hca.class_category = 'POS_PARTICIPANT_TYPE'
394 and hca.class_code = 'ENTERPRISE';
395
396 return l_party_id;
397
398 EXCEPTION
399 WHEN NO_DATA_FOUND THEN
400 return -1;
401 END check_for_enterprise_user;
402
403 -- return Y if the person_id is a current employee or contingent worker;
404 -- N otherwise
405 FUNCTION is_person_employee_cont_worker(p_person_id IN NUMBER)
406 RETURN VARCHAR2 IS
407
408 l_hr_agent fnd_profile_option_values.profile_option_value%TYPE;
409 l_number NUMBER;
410
411 -- the following query is based on view per_employees_current_x
412 -- we can not use the view based the view is a secured view
413 -- (by HR: Security Profile profile option)
414 CURSOR l_current_employee_cur (p_person_id IN NUMBER) IS
415 SELECT 1
416 FROM per_people_f p,
417 per_all_assignments_f a,
418 per_periods_of_service b
419 WHERE a.person_id = p.person_id
420 AND a.primary_flag = 'Y'
421 AND a.assignment_type = 'E'
422 AND a.period_of_service_id = b.period_of_service_id
423 AND trunc(sysdate) BETWEEN p.effective_start_date AND p.effective_end_date
424 AND trunc(sysdate) BETWEEN a.effective_start_date AND a.effective_end_date
425 AND (b.actual_termination_date>= trunc(sysdate) OR b.actual_termination_date IS NULL)
426 AND p.employee_number IS NOT NULL
427 AND p.person_id = p_person_id;
428
429 -- the following query is based on view per_cont_workers_current_x
430 -- we can not use the view based the view is a secured view
431 -- (by HR: Security Profile profile option)
432 CURSOR l_contingent_worker_cur (p_person_id IN NUMBER) IS
433 SELECT 1
434 FROM per_people_f p,
435 per_all_assignments_f a,
436 per_periods_of_placement pp
437 WHERE a.person_id = p.person_id
438 AND a.person_id = pp.person_id
439 AND a.primary_flag = 'Y'
440 AND a.assignment_type = 'C'
441 AND a.period_of_placement_date_start = pp.date_start
442 AND trunc(sysdate) between p.effective_start_date AND p.effective_end_date
443 AND trunc(sysdate) between a.effective_start_date AND a.effective_end_date
444 AND (pp.actual_termination_date>= trunc(sysdate) OR pp.actual_termination_date IS NULL)
445 AND p.npw_number IS NOT NULL
446 AND p.person_id = p_person_id;
447
448 BEGIN
449
450 OPEN l_current_employee_cur(p_person_id);
451 FETCH l_current_employee_cur INTO l_number;
452 IF l_current_employee_cur%found THEN
453 CLOSE l_current_employee_cur;
454 RETURN 'Y';
455 END IF;
456 CLOSE l_current_employee_cur;
457
458 fnd_profile.get('HR_TREAT_CWK_AS_EMP', l_hr_agent);
459
460 IF l_hr_agent IS NULL OR l_hr_agent <> 'Y' THEN
461 RETURN 'N';
462 END IF;
463
464 OPEN l_contingent_worker_cur(p_person_id);
465 FETCH l_contingent_worker_cur INTO l_number;
466 IF l_contingent_worker_cur%found THEN
467 CLOSE l_contingent_worker_cur;
468 RETURN 'Y';
469 END IF;
470 CLOSE l_contingent_worker_cur;
471 RETURN 'N';
472
473 END is_person_employee_cont_worker;
474
475 -- return person_party_id of the fnd user if the user
476 -- is a current employee or contingent worker; otherwise return null
477 FUNCTION get_emp_or_ctgt_wrkr_pty_id (p_userid IN NUMBER)
478 RETURN NUMBER IS
479
480 l_party_id NUMBER;
481 l_employee_id NUMBER;
482
483 CURSOR l_fnd_user_cur IS
484 SELECT employee_id, person_party_id
485 FROM fnd_user WHERE user_id = p_userid;
486
487 BEGIN
488
489 OPEN l_fnd_user_cur;
490 FETCH l_fnd_user_cur INTO l_employee_id, l_party_id;
491 IF l_fnd_user_cur%notfound OR l_employee_id IS NULL THEN
492 CLOSE l_fnd_user_cur;
493 RETURN NULL;
494 END IF;
495 CLOSE l_fnd_user_cur;
496
497 IF is_person_employee_cont_worker(l_employee_id) = 'Y' THEN
498 RETURN l_party_id;
499 ELSE
500 RETURN NULL;
501 END IF;
502
503 END get_emp_or_ctgt_wrkr_pty_id;
504
505 FUNCTION is_user_employee_cont_worker(p_userid IN NUMBER)
506 RETURN VARCHAR2 IS
507 l_employee_id NUMBER;
508
509 CURSOR l_fnd_user_cur IS
510 SELECT employee_id
511 FROM fnd_user
512 WHERE user_id = p_userid;
513 BEGIN
514 OPEN l_fnd_user_cur;
515 FETCH l_fnd_user_cur INTO l_employee_id;
516 IF l_fnd_user_cur%notfound OR l_employee_id IS NULL THEN
517 CLOSE l_fnd_user_cur;
518 RETURN 'N';
519 END IF;
520 CLOSE l_fnd_user_cur;
521
522 IF is_person_employee_cont_worker(l_employee_id) = 'Y' THEN
523 RETURN 'Y';
524 ELSE
525 RETURN 'N';
526 END IF;
527 END is_user_employee_cont_worker;
528
529 -- bitang: the implementation here works for release 11.5.10 but might be changed for r12
530 -- due to TCA Supplier project.
531 -- this procedure is used in POSISPAB.pls. need to find out whether the
532 -- caller passes in a supplier username or internal user name
533 FUNCTION get_job_title_for_user (p_user_id IN NUMBER )
534 RETURN VARCHAR2
535 IS
536 l_job_title HZ_PARTIES.PERSON_TITLE%TYPE;
537 BEGIN
538 SELECT hp.person_title
539 INTO l_job_title
540 FROM hz_parties hp, fnd_user fu
541 WHERE hp.party_id = fu.person_party_id
542 AND fu.user_id = p_user_id;
543
544 return l_job_title;
545
546 EXCEPTION
547 WHEN OTHERS THEN
548 RETURN NULL;
549 END get_job_title_for_user;
550
551 END POS_PARTY_MANAGEMENT_PKG;
[Home] [Help]