[Home] [Help]
PACKAGE BODY: APPS.POS_PROFILE_CHANGE_REQUEST_PKG
Source
1 PACKAGE BODY POS_PROFILE_CHANGE_REQUEST_PKG AS
2 /* $Header: POSPCRB.pls 120.62 2011/11/19 11:36:57 ramkandu ship $ */
3
4 g_module VARCHAR2(30) := 'POS_PROFILE_CHANGE_REQUEST_PKG';
5
6 PROCEDURE approve_new_address_req
7 (p_request_rec IN pos_address_requests%ROWTYPE,
8 p_vendor_id IN NUMBER,
9 p_vendor_party_id IN NUMBER,
10 x_return_status OUT nocopy VARCHAR2,
11 x_msg_count OUT nocopy NUMBER,
12 x_msg_data OUT nocopy VARCHAR2
13 )
14 IS
15 l_party_site_id NUMBER;
16 l_location_id NUMBER;
17 l_address_request_id NUMBER;
18 l_lock_id NUMBER;
19 cursor l_cont_addr_cur is
20 select address_req_id from pos_cont_addr_requests
21 WHERE address_req_id = p_request_rec.address_request_id for update nowait;
22 l_cont_addr_rec l_cont_addr_cur%ROWTYPE;
23
24 cursor l_address_note_cur is
25 select address_req_id from pos_address_notes
26 WHERE address_req_id = p_request_rec.address_request_id for update nowait;
27 l_address_note_rec l_address_note_cur%ROWTYPE;
28
29 BEGIN
30 savepoint approve_new_address_req;
31 -- Lock the rows: This is done as part of ECO 5209555
32 BEGIN
33
34 select address_request_id into l_lock_id from pos_address_requests
35 WHERE address_request_id = p_request_rec.address_request_id for update nowait;
36
37 open l_cont_addr_cur;
38 fetch l_cont_addr_cur into l_cont_addr_rec;
39 close l_cont_addr_cur;
40
41 open l_address_note_cur;
42 fetch l_address_note_cur into l_address_note_rec;
43 close l_address_note_cur;
44
45 EXCEPTION
46
47 WHEN OTHERS THEN
48 x_return_status :='E';
49 x_msg_data := 'Cannot lock the rows';
50 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
51 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'approve_new_address_req' , ' Cannot lock the rows ');
52 END IF;
53 raise_application_error(-20049, x_msg_data, true);
54 END;
55
56 pos_supplier_address_pkg.create_supplier_address
57 (p_vendor_id => p_vendor_id,
58 p_vendor_party_id => p_vendor_party_id,
59 p_party_site_name => p_request_rec.party_site_name,
60 p_address_line1 => p_request_rec.address_line1,
61 p_address_line2 => p_request_rec.address_line2,
62 p_address_line3 => p_request_rec.address_line3,
63 p_address_line4 => p_request_rec.address_line4,
64 p_country => p_request_rec.country,
65 p_city => p_request_rec.city,
66 p_state => p_request_rec.state,
67 p_province => p_request_rec.province,
68 p_postal_code => p_request_rec.postal_code,
69 p_county => p_request_rec.county,
70 p_rfq_flag => p_request_rec.rfq_flag,
71 p_pur_flag => p_request_rec.pur_flag,
72 p_pay_flag => p_request_rec.pay_flag,
73 p_primary_pay_flag => p_request_rec.primary_pay_flag,
74 p_phone_area_code => p_request_rec.phone_area_code,
75 p_phone_number => p_request_rec.phone_number,
76 p_phone_extension => p_request_rec.phone_extension,
77 p_fax_area_code => p_request_rec.fax_area_code,
78 p_fax_number => p_request_rec.fax_number,
79 p_email_address => p_request_rec.email_address,
80 x_return_status => x_return_status,
81 x_msg_count => x_msg_count,
82 x_msg_data => x_msg_data,
83 x_party_site_id => l_party_site_id
84 );
85
86 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
87 rollback to approve_new_address_req;
88 RETURN;
89 END IF;
90
91 UPDATE pos_address_notes
92 SET party_site_id = l_party_site_id
93 WHERE address_req_id = p_request_rec.address_request_id;
94
95 UPDATE pos_address_requests
96 SET party_site_id = l_party_site_id,
97 request_status = 'APPROVED',
98 last_update_date = Sysdate,
99 last_updated_by = fnd_global.user_id,
100 last_update_login = fnd_global.login_id
101 WHERE address_request_id = p_request_rec.address_request_id;
102
103 UPDATE pos_cont_addr_requests
104 SET party_site_id = l_party_site_id,
105 last_update_date = Sysdate,
106 last_updated_by = fnd_global.user_id,
107 last_update_login = fnd_global.login_id
108 WHERE address_req_id = p_request_rec.address_request_id
109 AND request_status = 'PENDING'
110 AND party_site_id IS NULL;
111
112 -- Inform Banking about the address approval
113 -- This call enables banking to update all the account assignment
114 -- request associated with the address request to the new
115 -- tca address. This call is required for banking functionality
116 -- to work properly in R12 once an address has been approved.
117 -- It will throw an exception if null values for address_request_id
118 -- or newly created tca party_site_id are passed.
119 l_address_request_id := p_request_rec.address_request_id;
120 POS_SBD_PKG.sbd_handle_address_apv(
121 p_address_request_id => l_address_request_id
122 , p_party_site_id => l_party_site_id
123 , x_status => x_return_status
124 , x_exception_msg => x_msg_data
125 );
126
127 x_return_status := fnd_api.g_ret_sts_success;
128
129 EXCEPTION
130 WHEN OTHERS THEN
131 x_return_status :='E';
132 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
133 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'approve_new_address_req' , x_msg_data);
134 END IF;
135 rollback to approve_new_address_req;
136 raise_application_error(-20050, x_msg_data, true);
137
138 END approve_new_address_req;
139
140 PROCEDURE approve_update_address_req
141 (p_request_rec IN pos_address_requests%ROWTYPE,
142 p_vendor_id IN NUMBER,
143 p_vendor_party_id IN NUMBER,
144 x_return_status OUT nocopy VARCHAR2,
145 x_msg_count OUT nocopy NUMBER,
146 x_msg_data OUT nocopy VARCHAR2
147 )
148 IS
149 l_obj_ver hz_locations.object_version_number%TYPE;
150
151 CURSOR l_cur IS
152 SELECT object_version_number,location_id
153 from hz_locations
154 where location_id =
155 (SELECT location_id
156 FROM hz_party_sites
157 WHERE party_site_id = p_request_rec.party_site_id
158 ) FOR UPDATE;
159
160 l_rec l_cur%ROWTYPE;
161
162 CURSOR l_cur2 IS
163 select object_version_number, party_site_name
164 from hz_party_sites
165 where party_site_id = p_request_rec.party_site_id FOR UPDATE;
166
167 l_rec2 l_cur2%ROWTYPE;
168 l_lock_id NUMBER;
169 BEGIN
170 savepoint approve_update_address_req;
171
172 -- Lock the rows: This is done as part of ECO 5209555
173 BEGIN
174
175 select address_request_id into l_lock_id from pos_address_requests
176 WHERE address_request_id = p_request_rec.address_request_id for update nowait;
177
178 EXCEPTION
179
180 WHEN OTHERS THEN
181 x_return_status :='E';
182 x_msg_data := 'Cannot lock the rows';
183 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
184 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'approve_update_address_req' , ' Cannot lock the rows ');
185 END IF;
186 raise_application_error(-20051, x_msg_data, true);
187 END;
188
189
190 OPEN l_cur;
191 FETCH l_cur INTO l_rec;
192 IF l_cur%notfound THEN
193 CLOSE l_cur;
194 -- prepare err msg
195 x_return_status := fnd_api.g_ret_sts_error;
196 rollback to approve_update_address_req;
197 RETURN;
198 END IF;
199 CLOSE l_cur;
200
201 OPEN l_cur2;
202 FETCH l_cur2 INTO l_rec2;
203 IF l_cur2%notfound THEN
204 CLOSE l_cur2;
205 -- prepare err msg
206 x_return_status := fnd_api.g_ret_sts_error;
207 rollback to approve_update_address_req;
208 RETURN;
209 END IF;
210 CLOSE l_cur2;
211
212 pos_supplier_address_pkg.update_supplier_address
213 (p_vendor_id => p_vendor_id,
214 p_vendor_party_id => p_vendor_party_id,
215 p_party_site_id => p_request_rec.party_site_id,
216 p_party_site_name => p_request_rec.party_site_name,
217 p_address_line1 => p_request_rec.address_line1,
218 p_address_line2 => p_request_rec.address_line2,
219 p_address_line3 => p_request_rec.address_line3,
220 p_address_line4 => p_request_rec.address_line4,
221 p_country => p_request_rec.country,
222 p_city => p_request_rec.city,
223 p_state => p_request_rec.state,
224 p_province => p_request_rec.province,
225 p_postal_code => p_request_rec.postal_code,
226 p_county => p_request_rec.county,
227 p_rfq_flag => p_request_rec.rfq_flag,
228 p_pur_flag => p_request_rec.pur_flag,
229 p_pay_flag => p_request_rec.pay_flag,
230 p_primary_pay_flag => p_request_rec.primary_pay_flag,
231 p_phone_area_code => p_request_rec.phone_area_code,
232 p_phone_number => p_request_rec.phone_number,
233 p_phone_extension => p_request_rec.phone_extension,
234 p_fax_area_code => p_request_rec.fax_area_code,
235 p_fax_number => p_request_rec.fax_number,
236 p_email_address => p_request_rec.email_address,
237 x_return_status => x_return_status,
238 x_msg_count => x_msg_count,
239 x_msg_data => x_msg_data
240 );
241
242 UPDATE pos_address_requests
243 SET request_status = 'APPROVED',
244 last_update_date = Sysdate,
245 last_updated_by = fnd_global.user_id,
246 last_update_login = fnd_global.login_id
247 WHERE address_request_id = p_request_rec.address_request_id;
248
249 EXCEPTION
250 WHEN OTHERS THEN
251 x_return_status :='E';
252 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
253 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'approve_update_address_req' , x_msg_data);
254 END IF;
255 rollback to approve_update_address_req;
256 raise_application_error(-20052, x_msg_data, true);
257 END approve_update_address_req;
258
259 PROCEDURE approve_cont_addr_requests
260 (p_contact_request_id IN NUMBER,
261 x_return_status OUT nocopy VARCHAR2,
262 x_msg_count OUT nocopy NUMBER,
263 x_msg_data OUT nocopy VARCHAR2
264 ) IS
265 CURSOR l_cur IS
266 SELECT pcar.cont_addr_request_id,
267 pcar.request_type,
268 pv.party_id,
269 pv.vendor_id,
270 pcr.contact_party_id,
271 pcar.party_site_id
272 FROM pos_cont_addr_requests pcar,
273 pos_contact_requests pcr,
274 pos_supplier_mappings psm,
275 po_vendors pv
276 WHERE pcar.contact_req_id = p_contact_request_id
277 AND pcar.request_status = 'PENDING'
278 AND pcar.party_site_id IS NOT NULL
279 AND pcar.mapping_id = psm.mapping_id
280 AND psm.vendor_id = pv.vendor_id
281 AND pcar.contact_req_id = pcr.contact_request_id;
282 BEGIN
283 savepoint approve_cont_addr_requests;
284
285 FOR l_rec IN l_cur LOOP
286 IF l_rec.request_type = 'ADD' THEN
287
288 pos_supplier_address_pkg.assign_address_to_contact
289 (p_contact_party_id => l_rec.contact_party_id,
290 p_org_party_site_id => l_rec.party_site_id,
291 p_vendor_id => l_rec.vendor_id,
292 x_return_status => x_return_status,
293 x_msg_count => x_msg_count,
294 x_msg_data => x_msg_data
295 );
296
297 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
298 rollback to approve_cont_addr_requests;
299 RETURN;
300 END IF;
301
302 ELSIF l_rec.request_type = 'DELETE' THEN
303 pos_supplier_address_pkg.unassign_address_to_contact
304 (p_contact_party_id => l_rec.contact_party_id,
305 p_org_party_site_id => l_rec.party_site_id,
306 p_vendor_id => l_rec.vendor_id,
307 x_return_status => x_return_status,
308 x_msg_count => x_msg_count,
309 x_msg_data => x_msg_data
310 );
311
312 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
313 rollback to approve_cont_addr_requests;
314 RETURN;
315 END IF;
316
317
318 ELSE
319 x_return_status := fnd_api.g_ret_sts_error;
320 x_msg_count := 1;
321 x_msg_data := 'invalid request type ' || l_rec.request_type
322 || ' in pos_cont_addr_requests table with cont_addr_request_id = '
323 || l_rec.cont_addr_request_id;
324 rollback to approve_cont_addr_requests;
325 RETURN;
326 END IF;
327 END LOOP;
328
329 UPDATE pos_cont_addr_requests
330 SET request_status = 'APPROVED',
331 last_update_date = Sysdate,
332 last_updated_by = fnd_global.user_id,
333 last_update_login = fnd_global.login_id
334 WHERE cont_addr_request_id IN
335 (SELECT pcar.cont_addr_request_id
336 FROM pos_cont_addr_requests pcar,
337 pos_contact_requests pcr,
338 pos_supplier_mappings psm,
339 po_vendors pv
340 WHERE pcar.contact_req_id = p_contact_request_id
341 AND pcar.request_status = 'PENDING'
342 AND pcar.mapping_id = psm.mapping_id
343 AND psm.vendor_id = pv.vendor_id
344 AND pcar.contact_req_id = pcr.contact_request_id
345 );
346
347 x_return_status := fnd_api.g_ret_sts_success;
348
349 EXCEPTION
350
351 WHEN OTHERS THEN
352 x_return_status :='E';
353 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
354 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'approve_cont_addr_requests' , x_msg_data);
355 END IF;
356 rollback to approve_cont_addr_requests;
357 raise_application_error(-20053, x_msg_data, true);
358
359 END approve_cont_addr_requests;
360
361 PROCEDURE approve_new_contact_req
362 (p_request_rec IN pos_contact_requests%ROWTYPE,
363 p_vendor_id IN NUMBER,
364 p_vendor_party_id IN NUMBER,
365 p_user_name IN VARCHAR2,
366 x_return_status OUT nocopy VARCHAR2,
367 x_msg_count OUT nocopy NUMBER,
368 x_msg_data OUT nocopy VARCHAR2,
369 x_password OUT nocopy VARCHAR2,
370 p_inactive_date IN DATE DEFAULT NULL
371 )
372 IS
373 l_person_party_id NUMBER;
374 l_fnd_user_id NUMBER;
375 l_step VARCHAR2(100);
376 l_method VARCHAR2(30);
377 l_lock_id NUMBER;
378 cursor l_cont_addr_cur is
379 select contact_req_id from pos_cont_addr_requests
380 WHERE contact_req_id = p_request_rec.contact_request_id for update nowait;
381 l_cont_addr_rec l_cont_addr_cur%ROWTYPE;
382
383 BEGIN
384 SAVEPOINT approve_new_contact_req_sp;
385
386 -- Lock the rows: This is done as part of ECO 5209555
387 BEGIN
388
389 select contact_request_id into l_lock_id from pos_contact_requests
390 where contact_request_id = p_request_rec.contact_request_id for update nowait;
391
392 open l_cont_addr_cur;
393 fetch l_cont_addr_cur into l_cont_addr_rec;
394 close l_cont_addr_cur;
395
396 EXCEPTION
397
398 WHEN OTHERS THEN
399 x_return_status :='E';
400 x_msg_data := 'Cannot lock the rows';
401 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
402 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'approve_new_contact_req' , ' Cannot lock the rows ');
403 END IF;
404 raise_application_error(-20049, x_msg_data, true);
405 END;
406
407
408 l_method := 'approve_new_contact_req';
409
410 pos_supp_contact_pkg.create_supplier_contact
411 (p_vendor_party_id => p_vendor_party_id,
412 p_first_name => p_request_rec.first_name,
413 p_last_name => p_request_rec.last_name,
414 p_middle_name => p_request_rec.middle_name,
415 p_contact_title => p_request_rec.contact_title,
416 p_job_title => p_request_rec.job_title,
417 p_phone_area_code => p_request_rec.phone_area_code,
418 p_phone_number => p_request_rec.phone_number,
419 p_phone_extension => p_request_rec.phone_extension,
420 p_fax_area_code => p_request_rec.fax_area_code,
421 p_fax_number => p_request_rec.fax_number,
422 p_email_address => p_request_rec.email_address,
423 p_inactive_date => p_inactive_date,
424 x_return_status => x_return_status,
425 x_msg_count => x_msg_count,
426 x_msg_data => x_msg_data,
427 x_person_party_id => l_person_party_id,
428 p_department => p_request_rec.department,
429 p_alt_contact_name => p_request_rec.alt_contact_name,
430 p_alt_area_code => p_request_rec.alt_area_code,
431 p_alt_phone_number => p_request_rec.alt_phone_number,
432 p_url => p_request_rec.url
433 );
434
435 IF x_return_status <> fnd_api.g_ret_sts_success THEN
436 ROLLBACK TO approve_new_contact_req_sp;
437 RETURN;
438 END IF;
439
440 -- handling user account
441 IF p_request_rec.create_user_account IS NOT NULL
442 AND p_request_rec.create_user_account = 'Y' THEN
443 -- create fnd user account
444 l_step := 'call create_fnd_user';
445 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
446 fnd_log.string
447 ( fnd_log.level_statement
448 , g_module || '.' || l_method
449 , l_step
450 || ' username ' || p_request_rec.email_address
451 || ' user_email ' || p_request_rec.email_address
452 || ' party_id ' || l_person_party_id
453 );
454 END IF;
455
456 pos_user_admin_pkg.create_supplier_user_ntf
457 (p_user_name => p_user_name,
458 p_user_email => p_request_rec.email_address,
459 p_person_party_id => l_person_party_id,
460 p_password => null,
461 x_return_status => x_return_status,
462 x_msg_count => x_msg_count,
463 x_msg_data => x_msg_data,
464 x_user_id => l_fnd_user_id,
465 x_password => x_password
466 );
467
468 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
469 ROLLBACK TO approve_new_contact_req_sp;
470 RETURN;
471 END IF;
472
473 pos_user_admin_pkg.createsecattr
474 ( p_user_id => l_fnd_user_id,
475 p_attribute_code => 'ICX_SUPPLIER_ORG_ID',
476 p_app_id => 177,
477 p_number_value => p_vendor_id
478 );
479 END IF;
480
481 l_step := 'update pos_contact_request with ids';
482 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
483 fnd_log.string
484 ( fnd_log.level_statement
485 , g_module || '.' || l_method
486 , l_step
487 );
488 END IF;
489
490 UPDATE pos_contact_requests
491 SET contact_party_id = l_person_party_id,
492 request_status = 'APPROVED',
493 last_update_date = Sysdate,
494 last_updated_by = fnd_global.user_id,
495 last_update_login = fnd_global.login_id
496 WHERE contact_request_id = p_request_rec.contact_request_id;
497
498 UPDATE pos_cont_addr_requests
499 SET contact_party_id = l_person_party_id,
500 last_update_date = Sysdate,
501 last_updated_by = fnd_global.user_id,
502 last_update_login = fnd_global.login_id
503 WHERE contact_req_id = p_request_rec.contact_request_id
504 AND contact_party_id IS NULL
505 AND request_status = 'PENDING';
506
507 -- handle address contact association
508 approve_cont_addr_requests
509 (p_contact_request_id => p_request_rec.contact_request_id,
510 x_return_status => x_return_status,
511 x_msg_count => x_msg_count,
512 x_msg_data => x_msg_data
513 );
514
515 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
516 ROLLBACK TO approve_new_contact_req_sp;
517 RETURN;
518 END IF;
519
520 EXCEPTION
521 WHEN OTHERS THEN
522 x_return_status := fnd_api.g_ret_sts_unexp_error;
523 x_msg_count := 1;
524 x_msg_data := Sqlerrm;
525 ROLLBACK TO approve_new_contact_req_sp;
526 pos_log.log_sqlerrm('POSCONTB', 'in approve_new_contact_req');
527
528 END approve_new_contact_req;
529
530 PROCEDURE approve_update_contact_req
531 (p_request_rec IN pos_contact_requests%ROWTYPE,
532 p_vendor_party_id IN NUMBER,
533 x_return_status OUT nocopy VARCHAR2,
534 x_msg_count OUT nocopy NUMBER,
535 x_msg_data OUT nocopy VARCHAR2
536 )
537 IS
538 l_lock_id number;
539 p_request_inactive_date date;
540 BEGIN
541 SAVEPOINT approve_update_contact_req_sp;
542
543 -- Lock the rows: This is done as part of ECO 5209555
544 BEGIN
545
546 select contact_request_id into l_lock_id from pos_contact_requests
547 where contact_request_id = p_request_rec.contact_request_id for update nowait;
548
549 EXCEPTION
550
551 WHEN OTHERS THEN
552 x_return_status :='E';
553 x_msg_data := 'Cannot lock the rows';
554 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
555 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'approve_update_contact_req' , ' Cannot lock the rows ');
556 END IF;
557 raise_application_error(-20049, x_msg_data, true);
558 END;
559
560 IF p_request_rec.request_type <> 'UPDATE' AND
561 p_request_rec.request_type <> 'DELETE' THEN
562 x_return_status := fnd_api.g_ret_sts_error;
563 x_msg_count := 1;
564 x_msg_data := 'request_type not UPDATED for contact request id '
565 || p_request_rec.contact_request_id;
566 ROLLBACK TO approve_update_contact_req_sp;
567 RETURN;
568 END IF;
569
570 IF p_request_rec.request_status <> 'PENDING' THEN
571 x_return_status := fnd_api.g_ret_sts_error;
572 x_msg_count := 1;
573 x_msg_data := 'request_status not PENDING for contact request id '
574 || p_request_rec.contact_request_id;
575 ROLLBACK TO approve_update_contact_req_sp;
576 RETURN;
577 END IF;
578
579 IF p_request_rec.contact_party_id IS NULL THEN
580 x_return_status := fnd_api.g_ret_sts_error;
581 x_msg_count := 1;
582 x_msg_data := 'contact_party_id is NULL for contact request id '
583 || p_request_rec.contact_request_id;
584 ROLLBACK TO approve_update_contact_req_sp;
585 RETURN;
586 END IF;
587
588 IF p_request_rec.request_type = 'DELETE' THEN
589 p_request_inactive_date := sysdate;
590 ELSE
591 p_request_inactive_date := null;
592 END IF;
593
594 pos_supp_contact_pkg.update_supplier_contact
595 (p_contact_party_id => p_request_rec.contact_party_id,
596 p_vendor_party_id => p_vendor_party_id,
597 p_first_name => p_request_rec.first_name,
598 p_last_name => p_request_rec.last_name,
599 p_middle_name => p_request_rec.middle_name,
600 p_contact_title => p_request_rec.contact_title,
601 p_job_title => p_request_rec.job_title,
602 p_phone_area_code => p_request_rec.phone_area_code,
603 p_phone_number => p_request_rec.phone_number,
604 p_phone_extension => p_request_rec.phone_extension,
605 p_fax_area_code => p_request_rec.fax_area_code,
606 p_fax_number => p_request_rec.fax_number,
607 p_email_address => p_request_rec.email_address,
608 p_inactive_date => p_request_inactive_date,
609 x_return_status => x_return_status,
610 x_msg_count => x_msg_count,
611 x_msg_data => x_msg_data ,
612 p_department => p_request_rec.department,
613 p_alt_contact_name => p_request_rec.alt_contact_name,
614 p_alt_area_code => p_request_rec.alt_area_code,
615 p_alt_phone_number => p_request_rec.alt_phone_number,
616 p_url => p_request_rec.url
617 );
618
619 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
620 ROLLBACK TO approve_update_contact_req_sp;
621 RETURN;
622 END IF;
623
624 UPDATE pos_contact_requests
625 SET request_status = 'APPROVED',
626 last_update_date = Sysdate,
627 last_updated_by = fnd_global.user_id,
628 last_update_login = fnd_global.login_id
629 WHERE contact_request_id = p_request_rec.contact_request_id;
630
631 -- handle address contact association
632 approve_cont_addr_requests
633 (p_contact_request_id => p_request_rec.contact_request_id,
634 x_return_status => x_return_status,
635 x_msg_count => x_msg_count,
636 x_msg_data => x_msg_data
637 );
638
639 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
640 ROLLBACK TO approve_update_contact_req_sp;
641 RETURN;
642 END IF;
643
644 EXCEPTION
645 WHEN OTHERS THEN
646 ROLLBACK TO approve_update_contact_req_sp;
647 x_return_status := fnd_api.g_ret_sts_unexp_error;
648 x_msg_count := 1;
649 x_msg_data := Sqlerrm;
650 pos_log.log_sqlerrm('POSCONTB', 'in approve_update_contact_req');
651
652 END approve_update_contact_req;
653
654 PROCEDURE approve_new_bus_class_req
655 (p_request_rec IN pos_bus_class_reqs%ROWTYPE,
656 p_vendor_id IN NUMBER,
657 p_vendor_party_id IN NUMBER,
658 x_return_status OUT nocopy VARCHAR2,
659 x_msg_count OUT nocopy NUMBER,
660 x_msg_data OUT nocopy VARCHAR2
661 )
662 IS
663
664 l_lock_id NUMBER;
665
666 BEGIN
667 savepoint approve_new_bus_class_req;
668 -- Lock the rows: This is done as part of ECO 5209555
669 BEGIN
670
671 select bus_class_request_id into l_lock_id from pos_bus_class_reqs
672 WHERE bus_class_request_id = p_request_rec.bus_class_request_id for update nowait;
673
674 EXCEPTION
675
676 WHEN OTHERS THEN
677 x_return_status :='E';
678 x_msg_data := 'Cannot lock the rows';
679 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
680 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'approve_new_bus_class_req' , ' Cannot lock the rows ');
681 END IF;
682 raise_application_error(-20049, x_msg_data, true);
683 END;
684
685 INSERT INTO pos_bus_class_attr
686 ( classification_id
687 , party_id
688 , lookup_type
689 , lookup_code
690 , start_date_active
691 , end_date_active
692 , status
693 , ext_attr_1
694 , expiration_date
695 , certificate_number
696 , certifying_agency
697 , class_status
698 , created_by
699 , creation_date
700 , last_updated_by
701 , last_update_date
702 , last_update_login
703 , vendor_id
704 )
705 VALUES
706 (
707 pos_bus_class_attr_s.NEXTVAL
708 , p_vendor_party_id
709 , p_request_rec.lookup_type
710 , p_request_rec.lookup_code
711 , Sysdate
712 , NULL
713 , 'A'
714 , p_request_rec.ext_attr_1
715 , p_request_rec.expiration_date
716 , p_request_rec.certification_no
717 , p_request_rec.certification_agency
718 , 'APPROVED'
719 , fnd_global.user_id
720 , Sysdate
721 , fnd_global.user_id
722 , Sysdate
723 , fnd_global.login_id
724 , p_vendor_id
725 );
726
727 UPDATE pos_bus_class_reqs
728 SET request_status = 'APPROVED',
729 last_update_date = Sysdate,
730 last_updated_by = fnd_global.user_id,
731 last_update_login = fnd_global.login_id
732 WHERE bus_class_request_id = p_request_rec.bus_class_request_id;
733
734 x_return_status := fnd_api.g_ret_sts_success;
735
736 EXCEPTION
737 WHEN OTHERS THEN
738 x_return_status :='E';
739 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
740 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'approve_new_bus_class_req' , x_msg_data);
741 END IF;
742 rollback to approve_new_bus_class_req;
743 raise_application_error(-20097, x_msg_data, true);
744
745 END approve_new_bus_class_req;
746
747 PROCEDURE approve_update_bus_class_req
748 (p_request_rec IN pos_bus_class_reqs%ROWTYPE,
749 p_vendor_id IN NUMBER,
750 p_vendor_party_id IN NUMBER, x_return_status OUT nocopy VARCHAR2,
751 x_msg_count OUT nocopy NUMBER,
752 x_msg_data OUT nocopy VARCHAR2
753 )
754 IS
755
756 l_lock_id NUMBER;
757
758 BEGIN
759 savepoint approve_update_bus_class_req;
760
761 -- Lock the rows: This is done as part of ECO 5209555
762 BEGIN
763
764 select bus_class_request_id into l_lock_id from pos_bus_class_reqs
765 WHERE bus_class_request_id = p_request_rec.bus_class_request_id for update nowait;
766
767 select classification_id into l_lock_id from pos_bus_class_attr
768 WHERE classification_id = p_request_rec.classification_id for update nowait;
769
770 EXCEPTION
771
772 WHEN OTHERS THEN
773 x_return_status :='E';
774 x_msg_data := 'Cannot lock the rows';
775 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
776 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'approve_update_bus_class_req' , ' Cannot lock the rows ');
777 END IF;
778 raise_application_error(-20049, x_msg_data, true);
779 END;
780
781 UPDATE pos_bus_class_attr
782 SET ext_attr_1 = p_request_rec.ext_attr_1
783 , expiration_date = p_request_rec.expiration_date
784 , certificate_number = p_request_rec.certification_no
785 , certifying_agency = p_request_rec.certification_agency
786 , last_updated_by = fnd_global.user_id
787 , last_update_date = Sysdate
788 , last_update_login = fnd_global.login_id
789 WHERE classification_id = p_request_rec.classification_id;
790
791 UPDATE pos_bus_class_reqs
792 SET request_status = 'APPROVED',
793 last_update_date = Sysdate,
794 last_updated_by = fnd_global.user_id,
795 last_update_login = fnd_global.login_id
796 WHERE bus_class_request_id = p_request_rec.bus_class_request_id;
797
798 x_return_status := fnd_api.g_ret_sts_success;
799 EXCEPTION
800 WHEN OTHERS THEN
801 x_return_status :='E';
802 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
803 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'approve_update_bus_class_req' , x_msg_data);
804 END IF;
805 rollback to approve_update_bus_class_req;
806 raise_application_error(-20096, x_msg_data, true);
807
808 END approve_update_bus_class_req;
809
810 PROCEDURE approve_address_req
811 (p_request_id IN NUMBER,
812 x_return_status OUT nocopy VARCHAR2,
813 x_msg_count OUT nocopy NUMBER,
814 x_msg_data OUT nocopy VARCHAR2
815 )
816 IS
817 CURSOR l_cur IS
818 SELECT *
819 FROM pos_address_requests
820 WHERE address_request_id = p_request_id FOR UPDATE NOWAIT;
821 -- ECO 5209555 Add the nowait clause
822
823 l_rec l_cur%ROWTYPE;
824
825 CURSOR l_cur2 IS
826 SELECT vendor_id, party_id
827 FROM pos_supplier_mappings psm
828 WHERE mapping_id = l_rec.mapping_id;
829
830 l_rec2 l_cur2%ROWTYPE;
831 l_lock_id number;
832 BEGIN
833 savepoint approve_address_req;
834 OPEN l_cur;
835 FETCH l_cur INTO l_rec;
836 IF l_cur%notfound THEN
837 CLOSE l_cur;
838 x_return_status := fnd_api.g_ret_sts_error;
839 fnd_message.set_name('POS','POS_PRCR_BAD_ADDR_REQ_ID');
840 fnd_message.set_token('ADDRRESS_REQUEST_ID', p_request_id);
841 fnd_msg_pub.ADD;
842 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
843 rollback to approve_address_req;
844 RETURN;
845 END IF;
846 CLOSE l_cur;
847
848 IF l_rec.request_status IS NULL OR l_rec.request_status <> 'PENDING' THEN
849 x_return_status := fnd_api.g_ret_sts_error;
850 fnd_message.set_name('POS','POS_PRCR_ADDRREQ_NOT_PEND');
851 fnd_message.set_token('ADDRRESS_REQUEST_ID', p_request_id);
852 fnd_msg_pub.ADD;
853 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
854 rollback to approve_address_req;
855 RETURN;
856 END IF;
857
858 OPEN l_cur2;
859 FETCH l_cur2 INTO l_rec2;
860 IF l_cur2%notfound THEN
861 CLOSE l_cur2;
862 x_return_status := fnd_api.g_ret_sts_error;
863 fnd_message.set_name('POS','POS_PRCR_BAD_MAPPING_ID');
864 fnd_message.set_token('MAPPING_ID', l_rec.mapping_id);
865 fnd_msg_pub.ADD;
866 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
867 rollback to approve_address_req;
868 RETURN;
869 END IF;
870 CLOSE l_cur2;
871
872 IF l_rec.request_type = 'ADD' THEN
873 approve_new_address_req
874 (p_request_rec => l_rec,
875 p_vendor_id => l_rec2.vendor_id,
876 p_vendor_party_id => l_rec2.party_id,
877 x_return_status => x_return_status,
878 x_msg_count => x_msg_count,
879 x_msg_data => x_msg_data
880 );
881 ELSIF l_rec.request_type = 'UPDATE' THEN
882 approve_update_address_req
883 (p_request_rec => l_rec,
884 p_vendor_id => l_rec2.vendor_id,
885 p_vendor_party_id => l_rec2.party_id,
886 x_return_status => x_return_status,
887 x_msg_count => x_msg_count,
888 x_msg_data => x_msg_data
889 );
890
891 ELSIF l_rec.request_type = 'DELETE' then
892
893 UPDATE pos_address_requests
894 SET request_status = 'APPROVED',
895 last_update_date = Sysdate,
896 last_updated_by = fnd_global.user_id,
897 last_update_login = fnd_global.login_id
898 WHERE address_request_id = p_request_id;
899
900 ELSE
901 x_return_status := fnd_api.g_ret_sts_error;
902 x_msg_count := 1;
903 x_msg_data := l_rec.request_type || ' is not yet supported';
904 END IF;
905
906 EXCEPTION
907 WHEN OTHERS THEN
908 x_return_status :='E';
909 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
910 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'approve_address_req' , x_msg_data);
911 END IF;
912 rollback to approve_address_req;
913 raise_application_error(-20092, x_msg_data, true);
914
915 END approve_address_req;
916
917 PROCEDURE approve_contact_req
918 (p_request_id IN NUMBER,
919 x_return_status OUT nocopy VARCHAR2,
920 x_msg_count OUT nocopy NUMBER,
921 x_msg_data OUT nocopy VARCHAR2,
922 x_password OUT nocopy VARCHAR2
923 )
924 IS
925 BEGIN
926 savepoint approve_contact_req;
927 approve_contact_req
928 (p_request_id => p_request_id,
929 p_user_name => NULL, -- not passing user name to default to email address
930 x_return_status => x_return_status,
931 x_msg_count => x_msg_count,
932 x_msg_data => x_msg_data,
933 x_password => x_password
934 );
935
936 EXCEPTION
937 WHEN OTHERS THEN
938 x_return_status :='E';
939 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
940 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'approve_contact_req' , x_msg_data);
941 END IF;
942 rollback to approve_contact_req;
943 raise_application_error(-20093, x_msg_data, true);
944
945 END approve_contact_req;
946
947 -- If the request is a new contact request with user account,
948 -- x_password will have the generated password; otherwise it is null
949 --
950 PROCEDURE approve_contact_req
951 (p_request_id IN NUMBER,
952 p_user_name IN VARCHAR2,
953 x_return_status OUT nocopy VARCHAR2,
954 x_msg_count OUT nocopy NUMBER,
955 x_msg_data OUT nocopy VARCHAR2,
956 x_password OUT nocopy VARCHAR2,
957 p_inactive_date IN DATE DEFAULT NULL
958 )
959 IS
960 CURSOR l_cur IS
961 SELECT *
962 FROM pos_contact_requests
963 WHERE contact_request_id = p_request_id FOR UPDATE NOWAIT;
964 -- ECO 5209555 Add the nowait clause
965
966 l_rec l_cur%ROWTYPE;
967
968 CURSOR l_cur2 IS
969 SELECT vendor_id, party_id
970 FROM pos_supplier_mappings psm
971 WHERE mapping_id = l_rec.mapping_id;
972
973 l_rec2 l_cur2%ROWTYPE;
974
975 l_method VARCHAR2(30);
976
977 l_user_name fnd_user.user_name%TYPE;
978
979 /*Begin Supplier Hub -- Supplier Management */
980
981 /* Added to assign party usage as SUPPLIER_CONTACT for existing party
982 contacts*/
983
984 l_party_usg_rec HZ_PARTY_USG_ASSIGNMENT_PVT.party_usg_assignment_rec_type;
985 l_party_usg_validation_level NUMBER;
986
987 /*End Supplier Hub -- Supplier Management */
988
989 BEGIN
990 savepoint approve_contact_req;
991
992 l_method := 'approve_contact_req';
993 x_password := NULL;
994 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
995 fnd_log.string
996 ( fnd_log.level_procedure
997 , g_module || '.' || l_method
998 , 'start with p_request_id ' || p_request_id
999 );
1000 END IF;
1001
1002 OPEN l_cur;
1003 FETCH l_cur INTO l_rec;
1004 IF l_cur%notfound THEN
1005 CLOSE l_cur;
1006 x_return_status := fnd_api.g_ret_sts_error;
1007 fnd_message.set_name('POS','POS_PRCR_BAD_CONT_REQ_ID');
1008 fnd_message.set_token('CONTACT_REQUEST_ID', p_request_id);
1009 fnd_msg_pub.ADD;
1010 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
1011 rollback to approve_contact_req;
1012 RETURN;
1013 END IF;
1014 CLOSE l_cur;
1015
1016 IF l_rec.request_status IS NULL OR l_rec.request_status <> 'PENDING' THEN
1017 x_return_status := fnd_api.g_ret_sts_error;
1018 fnd_message.set_name('POS','POS_PRCR_CONTREQ_NOT_PEND');
1019 fnd_message.set_token('CONTACT_REQUEST_ID', p_request_id);
1020 fnd_msg_pub.ADD;
1021 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
1022 rollback to approve_contact_req;
1023 RETURN;
1024 END IF;
1025
1026 OPEN l_cur2;
1027 FETCH l_cur2 INTO l_rec2;
1028 IF l_cur2%notfound THEN
1029 CLOSE l_cur2;
1030 x_return_status := fnd_api.g_ret_sts_error;
1031 fnd_message.set_name('POS','POS_PRCR_BAD_MAPPING_ID');
1032 fnd_message.set_token('MAPPING_ID', l_rec.mapping_id);
1033 fnd_msg_pub.ADD;
1034 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
1035 rollback to approve_contact_req;
1036 RETURN;
1037 END IF;
1038 CLOSE l_cur2;
1039
1040 IF l_rec.request_type = 'ADD' THEN
1041
1042 IF p_user_name IS NOT NULL THEN
1043 l_user_name := p_user_name;
1044 ELSE
1045 l_user_name := l_rec.email_address;
1046 END IF;
1047
1048 approve_new_contact_req
1049 (p_request_rec => l_rec,
1050 p_vendor_id => l_rec2.vendor_id,
1051 p_vendor_party_id => l_rec2.party_id,
1052 p_user_name => l_user_name,
1053 x_return_status => x_return_status,
1054 x_msg_count => x_msg_count,
1055 x_msg_data => x_msg_data,
1056 x_password => x_password,
1057 p_inactive_date => p_inactive_date
1058 );
1059
1060 ELSIF l_rec.request_type = 'UPDATE' OR
1061 l_rec.request_type = 'DELETE' THEN
1062 approve_update_contact_req
1063 (p_request_rec => l_rec,
1064 p_vendor_party_id => l_rec2.party_id,
1065 x_return_status => x_return_status,
1066 x_msg_count => x_msg_count,
1067 x_msg_data => x_msg_data
1068 );
1069 /*Begin Supplier Hub -- Supplier Management */
1070 /* Added to assign partyusage as SUPPLIER_CONTACT for
1071 existing party contacts*/
1072 ELSIF l_rec.request_type = 'ADD_PARTY_CONTACT' THEN
1073 l_party_usg_validation_level :=
1074 HZ_PARTY_USG_ASSIGNMENT_PVT.G_VALID_LEVEL_NONE;
1075 l_party_usg_rec.party_id := l_rec.contact_party_id;
1076 l_party_usg_rec.party_usage_code := 'SUPPLIER_CONTACT';
1077 l_party_usg_rec.created_by_module := 'POS_SUPPLIER_MGMT';
1078
1079 HZ_PARTY_USG_ASSIGNMENT_PVT.assign_party_usage (
1080 p_validation_level => l_party_usg_validation_level,
1081 p_party_usg_assignment_rec => l_party_usg_rec,
1082 x_return_status => x_return_status,
1083 x_msg_count => x_msg_count,
1084 x_msg_data => x_msg_data);
1085
1086
1087 IF (x_return_status IS NULL OR
1088 x_return_status <> fnd_api.g_ret_sts_success) THEN
1089 ROLLBACK TO approve_new_contact_req_sp;
1090 RETURN;
1091 END IF;
1092
1093 UPDATE pos_contact_requests
1094 SET request_status = 'APPROVED',
1095 last_update_date = Sysdate,
1096 last_updated_by = fnd_global.user_id,
1097 last_update_login = fnd_global.login_id
1098 WHERE contact_request_id = l_rec.contact_request_id;
1099
1100 /*End Supplier Hub -- Supplier Management */
1101
1102 ELSE
1103
1104 x_return_status := fnd_api.g_ret_sts_error;
1105 x_msg_count := 1;
1106 x_msg_data := l_rec.request_type || ' is not yet supported';
1107 END IF;
1108
1109 EXCEPTION
1110 WHEN OTHERS THEN
1111 x_return_status :='E';
1112 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1113 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'approve_contact_req' , x_msg_data);
1114 END IF;
1115 rollback to approve_contact_req;
1116 raise_application_error(-20090, x_msg_data, true);
1117
1118 END approve_contact_req;
1119
1120 PROCEDURE approve_bus_class_req
1121 (p_request_id IN NUMBER,
1122 x_return_status OUT nocopy VARCHAR2,
1123 x_msg_count OUT nocopy NUMBER,
1124 x_msg_data OUT nocopy VARCHAR2
1125 )
1126 IS
1127 CURSOR l_cur IS
1128 SELECT *
1129 FROM pos_bus_class_reqs
1130 WHERE bus_class_request_id = p_request_id FOR UPDATE NOWAIT;
1131 -- ECO 5209555 Add the nowait clause
1132
1133 l_rec l_cur%ROWTYPE;
1134
1135 CURSOR l_cur2 IS
1136 SELECT vendor_id, party_id
1137 FROM pos_supplier_mappings psm
1138 WHERE mapping_id = l_rec.mapping_id;
1139
1140 l_rec2 l_cur2%ROWTYPE;
1141
1142 BEGIN
1143
1144 savepoint approve_bus_class_req;
1145
1146 OPEN l_cur;
1147 FETCH l_cur INTO l_rec;
1148 IF l_cur%notfound THEN
1149 CLOSE l_cur;
1150 x_return_status := fnd_api.g_ret_sts_error;
1151 fnd_message.set_name('POS','POS_PRCR_BAD_BC_REQ_ID');
1152 fnd_message.set_token('BUS_CLASS_REQUEST_ID', p_request_id);
1153 fnd_msg_pub.ADD;
1154 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
1155 rollback to approve_bus_class_req;
1156 RETURN;
1157 END IF;
1158 CLOSE l_cur;
1159
1160 IF l_rec.request_status IS NULL OR l_rec.request_status <> 'PENDING' THEN
1161 x_return_status := fnd_api.g_ret_sts_error;
1162 fnd_message.set_name('POS','POS_PRCR_BCREQ_NOT_PEND');
1163 fnd_message.set_token('BUS_CLASS_REQUEST_ID', p_request_id);
1164 fnd_msg_pub.ADD;
1165 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
1166 rollback to approve_bus_class_req;
1167 RETURN;
1168 END IF;
1169
1170 OPEN l_cur2;
1171 FETCH l_cur2 INTO l_rec2;
1172 IF l_cur2%notfound THEN
1173 CLOSE l_cur2;
1174 x_return_status := fnd_api.g_ret_sts_error;
1175 fnd_message.set_name('POS','POS_PRCR_BAD_MAPPING_ID');
1176 fnd_message.set_token('MAPPING_ID', l_rec.mapping_id);
1177 fnd_msg_pub.ADD;
1178 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
1179 rollback to approve_bus_class_req;
1180 RETURN;
1181 END IF;
1182 CLOSE l_cur2;
1183
1184 IF l_rec.request_type = 'ADD' THEN
1185 approve_new_bus_class_req
1186 (p_request_rec => l_rec,
1187 p_vendor_id => l_rec2.vendor_id,
1188 p_vendor_party_id => l_rec2.party_id,
1189 x_return_status => x_return_status,
1190 x_msg_count => x_msg_count,
1191 x_msg_data => x_msg_data
1192 );
1193
1194 POS_SUPP_CLASSIFICATION_PKG.SYNCHRONIZE_CLASS_TCA_TO_PO(
1195 pPartyId => l_rec2.party_id,
1196 pVendorId => l_rec2.vendor_id);
1197 ELSIF l_rec.request_type = 'UPDATE' THEN
1198 approve_update_bus_class_req
1199 (p_request_rec => l_rec,
1200 p_vendor_id => l_rec2.vendor_id,
1201 p_vendor_party_id => l_rec2.party_id,
1202 x_return_status => x_return_status,
1203 x_msg_count => x_msg_count,
1204 x_msg_data => x_msg_data
1205 );
1206 POS_SUPP_CLASSIFICATION_PKG.SYNCHRONIZE_CLASS_TCA_TO_PO(
1207 pPartyId => l_rec2.party_id,
1208 pVendorId => l_rec2.vendor_id);
1209
1210 ELSE
1211 x_return_status := fnd_api.g_ret_sts_error;
1212 x_msg_count := 1;
1213 x_msg_data := l_rec.request_type || ' is not yet supported';
1214 END IF;
1215
1216 EXCEPTION
1217 WHEN OTHERS THEN
1218 x_return_status :='E';
1219 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1220 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'approve_bus_class_req' , x_msg_data);
1221 END IF;
1222 rollback to approve_bus_class_req;
1223 raise_application_error(-20090, x_msg_data, true);
1224
1225 END approve_bus_class_req;
1226
1227 PROCEDURE approve_ps_req
1228 (p_request_id IN NUMBER,
1229 x_return_status OUT nocopy VARCHAR2,
1230 x_msg_count OUT nocopy NUMBER,
1231 x_msg_data OUT nocopy VARCHAR2
1232 )
1233 IS
1234 CURSOR l_cur IS
1235 SELECT *
1236 FROM pos_product_service_requests
1237 WHERE ps_request_id = p_request_id FOR UPDATE NOWAIT;
1238 -- ECO 5209555 Add the nowait clause
1239
1240 l_rec l_cur%ROWTYPE;
1241
1242 CURSOR l_cur2 IS
1243 SELECT vendor_id, party_id
1244 FROM pos_supplier_mappings psm
1245 WHERE mapping_id = l_rec.mapping_id;
1246
1247 l_rec2 l_cur2%ROWTYPE;
1248 l_lock_id number;
1249 BEGIN
1250
1251 savepoint approve_ps_req;
1252
1253 OPEN l_cur;
1254 FETCH l_cur INTO l_rec;
1255 IF l_cur%notfound THEN
1256 CLOSE l_cur;
1257 x_return_status := fnd_api.g_ret_sts_error;
1258 fnd_message.set_name('POS','POS_PRCR_BAD_PS_REQ_ID');
1259 fnd_message.set_token('PS_REQUEST_ID', p_request_id);
1260 fnd_msg_pub.ADD;
1261 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
1262 rollback to approve_ps_req;
1263 RETURN;
1264 END IF;
1265 CLOSE l_cur;
1266
1267 IF l_rec.request_status IS NULL OR l_rec.request_status <> 'PENDING' THEN
1268 x_return_status := fnd_api.g_ret_sts_error;
1269 fnd_message.set_name('POS','POS_PRCR_PSREQ_NOT_PEND');
1270 fnd_message.set_token('PS_REQUEST_ID', p_request_id);
1271 fnd_msg_pub.ADD;
1272 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
1273 rollback to approve_ps_req;
1274 RETURN;
1275 END IF;
1276
1277 IF l_rec.request_type IS NULL OR l_rec.request_type <> 'ADD' THEN
1278 x_return_status := fnd_api.g_ret_sts_error;
1279 fnd_message.set_name('POS','POS_PRCR_PSREQ_TYPE_NOT_ADD');
1280 fnd_message.set_token('PS_REQUEST_ID', p_request_id);
1281 fnd_msg_pub.ADD;
1282 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
1283 rollback to approve_ps_req;
1284 RETURN;
1285 END IF;
1286
1287 OPEN l_cur2;
1288 FETCH l_cur2 INTO l_rec2;
1289 IF l_cur2%notfound THEN
1290 CLOSE l_cur2;
1291 x_return_status := fnd_api.g_ret_sts_error;
1292 fnd_message.set_name('POS','POS_PRCR_BAD_MAPPING_ID');
1293 fnd_message.set_token('MAPPING_ID', l_rec.mapping_id);
1294 fnd_msg_pub.ADD;
1295 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
1296 rollback to approve_ps_req;
1297 RETURN;
1298 END IF;
1299 CLOSE l_cur2;
1300
1301 INSERT INTO pos_sup_products_services
1302 (
1303 classification_id
1304 , vendor_id
1305 , segment1
1306 , segment2
1307 , segment3
1308 , segment4
1309 , segment5
1310 , segment6
1311 , segment7
1312 , segment8
1313 , segment9
1314 , segment10
1315 , segment11
1316 , segment12
1317 , segment13
1318 , segment14
1319 , segment15
1320 , segment16
1321 , segment17
1322 , segment18
1323 , segment19
1324 , segment20
1325 , status
1326 , segment_definition
1327 , created_by
1328 , creation_date
1329 , last_updated_by
1330 , last_update_date
1331 , last_update_login
1332 )
1333 VALUES
1334 (
1335 pos_sup_products_services_s.NEXTVAL
1336 , l_rec2.vendor_id
1337 , l_rec.segment1
1338 , l_rec.segment2
1339 , l_rec.segment3
1340 , l_rec.segment4
1341 , l_rec.segment5
1342 , l_rec.segment6
1343 , l_rec.segment7
1344 , l_rec.segment8
1345 , l_rec.segment9
1346 , l_rec.segment10
1347 , l_rec.segment11
1348 , l_rec.segment12
1349 , l_rec.segment13
1350 , l_rec.segment14
1351 , l_rec.segment15
1352 , l_rec.segment16
1353 , l_rec.segment17
1354 , l_rec.segment18
1355 , l_rec.segment19
1356 , l_rec.segment20
1357 , 'A'
1358 , l_rec.segment_definition
1359 , fnd_global.user_id
1360 , Sysdate
1361 , fnd_global.user_id
1362 , Sysdate
1363 , fnd_global.login_id
1364 );
1365
1366 UPDATE pos_product_service_requests
1367 SET request_status = 'APPROVED',
1368 last_update_date = Sysdate,
1369 last_updated_by = fnd_global.user_id,
1370 last_update_login = fnd_global.login_id
1371 WHERE ps_request_id = p_request_id;
1372
1373 x_return_status := fnd_api.g_ret_sts_success;
1374
1375 EXCEPTION
1376 WHEN OTHERS THEN
1377 x_return_status :='E';
1378 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1379 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'approve_ps_req' , x_msg_data);
1380 END IF;
1381 rollback to approve_ps_req;
1382 raise_application_error(-20088, x_msg_data, true);
1383
1384 END approve_ps_req;
1385
1386 PROCEDURE reject_address_req
1387 (p_request_id IN NUMBER,
1388 x_return_status OUT nocopy VARCHAR2,
1389 x_msg_count OUT nocopy NUMBER,
1390 x_msg_data OUT nocopy VARCHAR2
1391 )
1392 IS
1393 l_lock_id number;
1394 cursor l_cont_addr_cur is
1395 select address_req_id from pos_cont_addr_requests
1396 WHERE address_req_id = p_request_id for update nowait;
1397 l_cont_addr_rec l_cont_addr_cur%ROWTYPE;
1398
1399 BEGIN
1400 savepoint reject_address_req;
1401 -- Lock the rows: This is done as part of ECO 5209555
1402 BEGIN
1403 select address_request_id into l_lock_id from pos_address_requests
1404 WHERE address_request_id = p_request_id for update nowait;
1405
1406 open l_cont_addr_cur;
1407 fetch l_cont_addr_cur into l_cont_addr_rec;
1408 close l_cont_addr_cur;
1409
1410 EXCEPTION
1411
1412 WHEN OTHERS THEN
1413 x_return_status :='E';
1414 x_msg_data := 'Cannot lock the rows';
1415 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1416 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'reject_address_req' , ' Cannot lock the rows ');
1417 END IF;
1418 raise_application_error(-20086, x_msg_data, true);
1419 END;
1420
1421 UPDATE pos_address_requests
1422 SET request_status = 'REJECTED',
1423 last_update_date = Sysdate,
1424 last_updated_by = fnd_global.user_id,
1425 last_update_login = fnd_global.login_id
1426 WHERE address_request_id = p_request_id;
1427
1428 UPDATE pos_cont_addr_requests
1429 SET request_status = 'REJECTED',
1430 last_update_date = Sysdate,
1431 last_updated_by = fnd_global.user_id,
1432 last_update_login = fnd_global.login_id
1433 WHERE address_req_id = p_request_id;
1434
1435 x_return_status := fnd_api.g_ret_sts_success;
1436
1437 EXCEPTION
1438 WHEN OTHERS THEN
1439 x_return_status :='E';
1440 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1441 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'reject_address_req' , x_msg_data);
1442 END IF;
1443 rollback to reject_address_req;
1444 raise_application_error(-20085, x_msg_data, true);
1445
1446 END reject_address_req;
1447
1448 PROCEDURE reject_contact_req
1449 (p_request_id IN NUMBER,
1450 x_return_status OUT nocopy VARCHAR2,
1451 x_msg_count OUT nocopy NUMBER,
1452 x_msg_data OUT nocopy VARCHAR2
1453 )
1454 IS
1455
1456 l_lock_id number;
1457
1458 BEGIN
1459
1460 savepoint reject_contact_req;
1461 -- Lock the rows: This is done as part of ECO 5209555
1462 BEGIN
1463 select contact_request_id into l_lock_id from pos_contact_requests
1464 WHERE contact_request_id = p_request_id for update nowait;
1465 EXCEPTION
1466
1467 WHEN OTHERS THEN
1468 x_return_status :='E';
1469 x_msg_data := 'Cannot lock the rows';
1470 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1471 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'reject_contact_req' , ' Cannot lock the rows ');
1472 END IF;
1473 raise_application_error(-20084, x_msg_data, true);
1474 END;
1475
1476 -- Bug # 5192274, the address requests should also be rejected
1477 update pos_cont_addr_requests
1478 set request_status = 'REJECTED',
1479 last_update_date = sysdate,
1480 last_updated_by = fnd_global.user_id,
1481 last_update_login = fnd_global.login_id
1482 where contact_req_id = p_request_id;
1483
1484 UPDATE pos_contact_requests
1485 SET request_status = 'REJECTED',
1486 last_update_date = Sysdate,
1487 last_updated_by = fnd_global.user_id,
1488 last_update_login = fnd_global.login_id
1489 WHERE contact_request_id = p_request_id;
1490
1491 x_return_status := fnd_api.g_ret_sts_success;
1492 EXCEPTION
1493 WHEN OTHERS THEN
1494 x_return_status :='E';
1495 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1496 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'reject_contact_req' , x_msg_data);
1497 END IF;
1498 rollback to reject_contact_req;
1499 raise_application_error(-20083, x_msg_data, true);
1500
1501 END reject_contact_req;
1502
1503 PROCEDURE reject_bus_class_req
1504 (p_request_id IN NUMBER,
1505 x_return_status OUT nocopy VARCHAR2,
1506 x_msg_count OUT nocopy NUMBER,
1507 x_msg_data OUT nocopy VARCHAR2
1508 )
1509 IS
1510 l_lock_id number;
1511
1512 BEGIN
1513
1514 savepoint reject_bus_class_req;
1515 -- Lock the rows: This is done as part of ECO 5209555
1516 BEGIN
1517
1518 select bus_class_request_id into l_lock_id from pos_bus_class_reqs
1519 WHERE bus_class_request_id = p_request_id for update nowait;
1520
1521 EXCEPTION
1522
1523 WHEN OTHERS THEN
1524 x_return_status :='E';
1525 x_msg_data := 'Cannot lock the rows';
1526 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1527 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'reject_bus_class_req' , ' Cannot lock the rows ');
1528 END IF;
1529 raise_application_error(-20082, x_msg_data, true);
1530 END;
1531
1532 UPDATE pos_bus_class_reqs
1533 SET request_status = 'REJECTED',
1534 last_update_date = Sysdate,
1535 last_updated_by = fnd_global.user_id,
1536 last_update_login = fnd_global.login_id
1537 WHERE bus_class_request_id = p_request_id;
1538
1539 x_return_status := fnd_api.g_ret_sts_success;
1540 EXCEPTION
1541 WHEN OTHERS THEN
1542 x_return_status :='E';
1543 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1544 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'reject_bus_class_req' , x_msg_data);
1545 END IF;
1546 rollback to reject_bus_class_req;
1547 raise_application_error(-20081, x_msg_data, true);
1548
1549 END reject_bus_class_req;
1550
1551 PROCEDURE reject_ps_req
1552 (p_request_id IN NUMBER,
1553 x_return_status OUT nocopy VARCHAR2,
1554 x_msg_count OUT nocopy NUMBER,
1555 x_msg_data OUT nocopy VARCHAR2
1556 )
1557 IS
1558 l_lock_id number;
1559 BEGIN
1560 savepoint reject_ps_req;
1561 -- Lock the rows: This is done as part of ECO 5209555
1562 BEGIN
1563
1564 select ps_request_id into l_lock_id from pos_product_service_requests
1565 WHERE ps_request_id = p_request_id for update nowait;
1566
1567 EXCEPTION
1568
1569 WHEN OTHERS THEN
1570 x_return_status :='E';
1571 x_msg_data := 'Cannot lock the rows';
1572 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1573 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'reject_ps_req' , ' Cannot lock the rows ');
1574 END IF;
1575 raise_application_error(-20080, x_msg_data, true);
1576 END;
1577
1578 UPDATE pos_product_service_requests
1579 SET request_status = 'REJECTED',
1580 last_update_date = Sysdate,
1581 last_updated_by = fnd_global.user_id,
1582 last_update_login = fnd_global.login_id
1583 WHERE ps_request_id = p_request_id;
1584
1585 x_return_status := fnd_api.g_ret_sts_success;
1586
1587 EXCEPTION
1588 WHEN OTHERS THEN
1589 x_return_status :='E';
1590 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1591 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'reject_ps_req' , x_msg_data);
1592 END IF;
1593 rollback to reject_ps_req;
1594 raise_application_error(-20079, x_msg_data, true);
1595
1596 END reject_ps_req;
1597
1598 PROCEDURE reject_mult_address_reqs
1599 ( p_req_id_tbl IN po_tbl_number,
1600 x_return_status OUT nocopy VARCHAR2,
1601 x_msg_count OUT nocopy NUMBER,
1602 x_msg_data OUT nocopy VARCHAR2
1603 )
1604 IS
1605
1606 l_lock_id number;
1607
1608 BEGIN
1609 savepoint reject_mult_address_reqs;
1610 -- Lock the rows: This is done as part of ECO 5209555
1611 BEGIN
1612 for i in 1..p_req_id_tbl.COUNT LOOP
1613
1614 select address_request_id into l_lock_id from pos_address_requests
1615 WHERE address_request_id = p_req_id_tbl(i) for update nowait;
1616
1617 END LOOP;
1618
1619 EXCEPTION
1620
1621 WHEN OTHERS THEN
1622 x_return_status :='E';
1623 x_msg_data := 'Cannot lock the rows';
1624 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1625 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'approve_mult_address_reqs' , ' Cannot lock the rows ');
1626 END IF;
1627 raise_application_error(-20078, x_msg_data, true);
1628 END;
1629
1630 for i in 1..p_req_id_tbl.COUNT LOOP
1631 reject_address_req
1632 (p_request_id => p_req_id_tbl(i),
1633 x_return_status => x_return_status,
1634 x_msg_count => x_msg_count,
1635 x_msg_data => x_msg_data
1636 );
1637 IF x_return_status IS NULL
1638 OR x_return_status <> fnd_api.g_ret_sts_success THEN
1639 rollback to reject_mult_address_reqs;
1640 RETURN;
1641 END IF;
1642 END LOOP;
1643
1644 x_return_status := FND_API.G_RET_STS_SUCCESS;
1645 EXCEPTION
1646 WHEN OTHERS THEN
1647 x_return_status :='E';
1648 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1649 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'reject_mult_address_reqs' , x_msg_data);
1650 END IF;
1651 rollback to reject_mult_address_reqs;
1652 raise_application_error(-20077, x_msg_data, true);
1653
1654 END reject_mult_address_reqs;
1655
1656 PROCEDURE reject_mult_contact_reqs
1657 ( p_req_id_tbl IN po_tbl_number,
1658 x_return_status OUT nocopy VARCHAR2,
1659 x_msg_count OUT nocopy NUMBER,
1660 x_msg_data OUT nocopy VARCHAR2
1661 )
1662 IS
1663
1664 l_lock_id number;
1665
1666 BEGIN
1667 savepoint reject_mult_contact_reqs;
1668 -- Lock the rows: This is done as part of ECO 5209555
1669 BEGIN
1670
1671 for i in 1..p_req_id_tbl.COUNT LOOP
1672 select contact_request_id into l_lock_id from pos_contact_requests
1673 where contact_request_id = p_req_id_tbl(i) for update nowait;
1674
1675 END LOOP;
1676
1677 EXCEPTION
1678
1679 WHEN OTHERS THEN
1680 x_return_status :='E';
1681 x_msg_data := 'Cannot lock the rows';
1682 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1683 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'reject_mult_contact_reqs' , ' Cannot lock the rows ');
1684 END IF;
1685 raise_application_error(-20076, x_msg_data, true);
1686 END;
1687
1688 for i in 1..p_req_id_tbl.COUNT LOOP
1689 reject_contact_req
1690 (p_request_id => p_req_id_tbl(i),
1691 x_return_status => x_return_status,
1692 x_msg_count => x_msg_count,
1693 x_msg_data => x_msg_data
1694 );
1695 IF x_return_status IS NULL
1696 OR x_return_status <> fnd_api.g_ret_sts_success THEN
1697 rollback to reject_mult_contact_reqs;
1698 RETURN;
1699 END IF;
1700 END LOOP;
1701
1702 x_return_status := FND_API.G_RET_STS_SUCCESS;
1703
1704 EXCEPTION
1705 WHEN OTHERS THEN
1706 x_return_status :='E';
1707 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1708 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'reject_mult_contact_reqs' , x_msg_data);
1709 END IF;
1710 rollback to reject_mult_contact_reqs;
1711 raise_application_error(-20075, x_msg_data, true);
1712
1713 END reject_mult_contact_reqs;
1714
1715 PROCEDURE reject_mult_bus_class_reqs
1716 ( p_req_id_tbl IN po_tbl_number,
1717 x_return_status OUT nocopy VARCHAR2,
1718 x_msg_count OUT nocopy NUMBER,
1719 x_msg_data OUT nocopy VARCHAR2
1720 )
1721 IS
1722
1723 l_lock_id number;
1724
1725 BEGIN
1726 savepoint reject_mult_bus_class_reqs;
1727 -- Lock the rows: This is done as part of ECO 5209555
1728 BEGIN
1729
1730 for i in 1..p_req_id_tbl.COUNT LOOP
1731
1732 select bus_class_request_id into l_lock_id from pos_bus_class_reqs
1733 where bus_class_request_id = p_req_id_tbl(i) for update nowait;
1734
1735 END LOOP;
1736
1737 EXCEPTION
1738
1739 WHEN OTHERS THEN
1740 x_return_status :='E';
1741 x_msg_data := 'Cannot lock the rows';
1742 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1743 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'reject_mult_bus_class_reqs' , ' Cannot lock the rows ');
1744 END IF;
1745 raise_application_error(-20074, x_msg_data, true);
1746 END;
1747
1748
1749 for i in 1..p_req_id_tbl.COUNT LOOP
1750 reject_bus_class_req
1751 (p_request_id => p_req_id_tbl(i),
1752 x_return_status => x_return_status,
1753 x_msg_count => x_msg_count,
1754 x_msg_data => x_msg_data
1755 );
1756 IF x_return_status IS NULL
1757 OR x_return_status <> fnd_api.g_ret_sts_success THEN
1758 rollback to reject_mult_bus_class_reqs;
1759 RETURN;
1760 END IF;
1761 END LOOP;
1762
1763 x_return_status := FND_API.G_RET_STS_SUCCESS;
1764
1765 EXCEPTION
1766 WHEN OTHERS THEN
1767 x_return_status :='E';
1768 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1769 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'reject_mult_bus_class_reqs' , x_msg_data);
1770 END IF;
1771 rollback to reject_mult_bus_class_reqs;
1772 raise_application_error(-20073, x_msg_data, true);
1773
1774 END reject_mult_bus_class_reqs;
1775
1776 PROCEDURE reject_mult_ps_reqs
1777 ( p_req_id_tbl IN po_tbl_number,
1778 x_return_status OUT nocopy VARCHAR2,
1779 x_msg_count OUT nocopy NUMBER,
1780 x_msg_data OUT nocopy VARCHAR2
1781 )
1782 IS
1783 l_lock_id number;
1784
1785 BEGIN
1786
1787 savepoint reject_mult_ps_reqs;
1788 -- Lock the rows: This is done as part of ECO 5209555
1789 BEGIN
1790
1791 for i in 1..p_req_id_tbl.COUNT LOOP
1792
1793 select ps_request_id into l_lock_id from pos_product_service_requests
1794 WHERE ps_request_id = p_req_id_tbl(i) for update nowait;
1795
1796 END LOOP;
1797
1798 EXCEPTION
1799
1800 WHEN OTHERS THEN
1801 x_return_status :='E';
1802 x_msg_data := 'Cannot lock the rows';
1803 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1804 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'reject_mult_ps_reqs' , ' Cannot lock the rows ');
1805 END IF;
1806 raise_application_error(-20071, x_msg_data, true);
1807 END;
1808
1809 for i in 1..p_req_id_tbl.COUNT LOOP
1810 reject_ps_req
1811 (p_request_id => p_req_id_tbl(i),
1812 x_return_status => x_return_status,
1813 x_msg_count => x_msg_count,
1814 x_msg_data => x_msg_data
1815 );
1816 IF x_return_status IS NULL
1817 OR x_return_status <> fnd_api.g_ret_sts_success THEN
1818 rollback to reject_mult_ps_reqs;
1819 RETURN;
1820 END IF;
1821 END LOOP;
1822
1823 x_return_status := FND_API.G_RET_STS_SUCCESS;
1824 EXCEPTION
1825 WHEN OTHERS THEN
1826 x_return_status :='E';
1827 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1828 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'reject_mult_ps_reqs' , x_msg_data);
1829 END IF;
1830 rollback to reject_mult_ps_reqs;
1831 raise_application_error(-20072, x_msg_data, true);
1832
1833 END reject_mult_ps_reqs;
1834
1835 PROCEDURE approve_mult_address_reqs
1836 ( p_req_id_tbl IN po_tbl_number,
1837 x_return_status OUT nocopy VARCHAR2,
1838 x_msg_count OUT nocopy NUMBER,
1839 x_msg_data OUT nocopy VARCHAR2
1840 )
1841 IS
1842
1843 l_lock_id number;
1844
1845 BEGIN
1846 savepoint approve_mult_address_reqs;
1847 -- Lock the rows: This is done as part of ECO 5209555
1848 BEGIN
1849 for i in 1..p_req_id_tbl.COUNT LOOP
1850
1851 select address_request_id into l_lock_id from pos_address_requests
1852 WHERE address_request_id = p_req_id_tbl(i) for update nowait;
1853
1854 END LOOP;
1855
1856 EXCEPTION
1857
1858 WHEN OTHERS THEN
1859 x_return_status :='E';
1860 x_msg_data := 'Cannot lock the rows';
1861 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1862 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'approve_mult_address_reqs' , ' Cannot lock the rows ');
1863 END IF;
1864 raise_application_error(-20070, x_msg_data, true);
1865 END;
1866
1867 for i in 1..p_req_id_tbl.COUNT LOOP
1868 approve_address_req
1869 (p_request_id => p_req_id_tbl(i),
1870 x_return_status => x_return_status,
1871 x_msg_count => x_msg_count,
1872 x_msg_data => x_msg_data
1873 );
1874 IF x_return_status IS NULL
1875 OR x_return_status <> fnd_api.g_ret_sts_success THEN
1876 rollback to approve_mult_address_reqs;
1877 RETURN;
1878 END IF;
1879 END LOOP;
1880
1881 x_return_status := FND_API.G_RET_STS_SUCCESS;
1882
1883 EXCEPTION
1884 WHEN OTHERS THEN
1885 x_return_status :='E';
1886 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1887 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'approve_mult_address_reqs' , x_msg_data);
1888 END IF;
1889 rollback to approve_mult_address_reqs;
1890 raise_application_error(-20069, x_msg_data, true);
1891
1892 END approve_mult_address_reqs;
1893
1894 PROCEDURE approve_mult_contact_reqs
1895 ( p_req_id_tbl IN po_tbl_number,
1896 x_return_status OUT nocopy VARCHAR2,
1897 x_msg_count OUT nocopy NUMBER,
1898 x_msg_data OUT nocopy VARCHAR2
1899 )
1900 IS
1901 l_password VARCHAR2(200);
1902 l_lock_id number;
1903
1904 /* Bug 6607254 Start */
1905
1906 l_fName VARCHAR2(150);
1907 l_lName VARCHAR2(150);
1908 l_eMail VARCHAR2(2000);
1909 l_phoneAreaCode VARCHAR2(10);
1910 l_phone VARCHAR2(40);
1911 l_phoneExtn VARCHAR2(20);
1912 l_suppPartyId NUMBER;
1913 l_contactPartyId NUMBER;
1914 l_duplicateRow NUMBER ;
1915
1916 /* Bug 6607254 End */
1917
1918 BEGIN
1919 savepoint approve_mult_contact_reqs;
1920 -- Lock the rows: This is done as part of ECO 5209555
1921 BEGIN
1922
1923 for i in 1..p_req_id_tbl.COUNT LOOP
1924 select contact_request_id into l_lock_id from pos_contact_requests
1925 where contact_request_id = p_req_id_tbl(i) for update nowait;
1926
1927 END LOOP;
1928
1929 EXCEPTION
1930
1931 WHEN OTHERS THEN
1932 x_return_status :='E';
1933 x_msg_data := 'Cannot lock the rows';
1934 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1935 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'approve_mult_contact_reqs' , ' Cannot lock the rows ');
1936 END IF;
1937 raise_application_error(-20068, x_msg_data, true);
1938 END;
1939
1940 for i in 1..p_req_id_tbl.COUNT LOOP
1941
1942 /* Bug 6607254 Start */
1943 /* The Below query selects the details of the contact request like first name,last name,email,phone number */
1944
1945 SELECT
1946 PCR.FIRST_NAME,
1947 PCR.LAST_NAME,
1948 PCR.EMAIL_ADDRESS,
1949 PCR.PHONE_AREA_CODE,
1950 PCR.PHONE_NUMBER,
1951 PCR.PHONE_EXTENSION,
1952 PCR.CONTACT_PARTY_ID,
1953 PSM.PARTY_ID
1954 INTO
1955 l_fName,
1956 l_lName,
1957 l_eMail,
1958 l_phoneAreaCode,
1959 l_phone,
1960 l_phoneExtn,
1961 l_contactPartyId,
1962 l_suppPartyId
1963 FROM
1964 POS_CONTACT_REQUESTS PCR,
1965 POS_SUPPLIER_MAPPINGS PSM
1966 WHERE CONTACT_REQUEST_ID=p_req_id_tbl(i)
1967 AND PCR.MAPPING_ID=PSM.MAPPING_ID;
1968
1969 /* Below query checks for the duplicate contacts */
1970
1971 SELECT Count(*) INTO l_duplicateRow
1972 FROM HZ_PARTIES HPC,
1973 HZ_CONTACT_POINTS HCPP,
1974 HZ_CONTACT_POINTS HCPE,
1975 HZ_RELATIONSHIPS HR
1976 WHERE HR.SUBJECT_ID = l_suppPartyId
1977 AND HCPP.OWNER_TABLE_NAME (+) = 'HZ_PARTIES'
1978 AND HCPP.OWNER_TABLE_ID (+) = HR.PARTY_ID
1979 AND HCPP.PHONE_LINE_TYPE (+) = 'GEN'
1980 AND HCPP.CONTACT_POINT_TYPE (+) = 'PHONE'
1981 AND HCPE.OWNER_TABLE_NAME (+) = 'HZ_PARTIES'
1982 AND HCPE.OWNER_TABLE_ID (+) = HR.PARTY_ID
1983 AND HCPE.CONTACT_POINT_TYPE (+) = 'EMAIL'
1984 AND HR.OBJECT_ID = HPC.PARTY_ID
1985 AND HR.SUBJECT_TYPE = 'ORGANIZATION'
1986 AND HR.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1987 AND HR.OBJECT_TABLE_NAME = 'HZ_PARTIES'
1988 AND HR.OBJECT_TYPE = 'PERSON'
1989 AND HR.RELATIONSHIP_CODE = 'CONTACT'
1990 AND HR.DIRECTIONAL_FLAG = 'B'
1991 AND HR.RELATIONSHIP_TYPE = 'CONTACT'
1992 AND ((HPC.PERSON_FIRST_NAME IS NULL
1993 AND l_fName IS NULL )
1994 OR UPPER(HPC.PERSON_FIRST_NAME) = Upper(l_fName))
1995 AND ((HPC.PERSON_LAST_NAME IS NULL
1996 AND l_lName IS NULL )
1997 OR UPPER(HPC.PERSON_LAST_NAME) = Upper(l_lName))
1998 AND ((HCPP.PHONE_AREA_CODE IS NULL
1999 AND l_phoneAreacODE IS NULL )
2000 OR UPPER(HCPP.PHONE_AREA_CODE) = Upper(l_phoneAreacODE))
2001 AND ((HCPP.PHONE_NUMBER IS NULL
2002 AND l_phone IS NULL )
2003 OR UPPER(HCPP.PHONE_NUMBER) = Upper(l_phone))
2004 AND ((HCPP.PHONE_EXTENSION IS NULL
2005 AND l_phoneExtn IS NULL )
2006 OR UPPER(HCPP.PHONE_EXTENSION) = Upper(l_phoneExtn))
2007 AND ((HCPE.EMAIL_ADDRESS IS NULL
2008 AND l_eMail IS NULL )
2009 OR UPPER(HCPE.EMAIL_ADDRESS) = Upper(l_eMail))
2010 AND (l_contactPartyId IS NULL
2011 OR l_contactPartyId <> HPC.PARTY_ID)
2012 AND ROWNUM < 2;
2013
2014 IF l_duplicateRow=1 THEN
2015 x_return_status := 'D';
2016 x_msg_data:=p_req_id_tbl(i);
2017 rollback to approve_mult_contact_reqs;
2018 RETURN;
2019 END IF ;
2020
2021 /*
2022 In the above if condition we are checking for duplicate contact
2023 entries.If we find any duplicate contact entries then rollback
2024 the changes and return from the pl/sql procedure .We put
2025 ContactRequestID into x_msg_data put parameter.This we want to
2026 get the First Name,Last Name of the contact .The return status
2027 is 'D' .
2028 */
2029
2030 /* Bug 6607254 End */
2031
2032 approve_contact_req
2033 (p_request_id => p_req_id_tbl(i),
2034 x_return_status => x_return_status,
2035 x_msg_count => x_msg_count,
2036 x_msg_data => x_msg_data,
2037 x_password => l_password
2038 );
2039 IF x_return_status IS NULL
2040 OR x_return_status <> fnd_api.g_ret_sts_success THEN
2041 rollback to approve_mult_contact_reqs;
2042 RETURN;
2043 END IF;
2044 END LOOP;
2045
2046 x_return_status := FND_API.G_RET_STS_SUCCESS;
2047
2048 EXCEPTION
2049 WHEN OTHERS THEN
2050 x_return_status :='E';
2051 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2052 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'approve_mult_contact_reqs' , x_msg_data);
2053 END IF;
2054 rollback to approve_mult_contact_reqs;
2055 raise_application_error(-20067, x_msg_data, true);
2056
2057 END approve_mult_contact_reqs;
2058
2059 PROCEDURE approve_mult_bus_class_reqs
2060 ( p_req_id_tbl IN po_tbl_number,
2061 x_return_status OUT nocopy VARCHAR2,
2062 x_msg_count OUT nocopy NUMBER,
2063 x_msg_data OUT nocopy VARCHAR2
2064 )
2065 IS
2066 l_lock_id number;
2067 BEGIN
2068 savepoint approve_mult_bus_class_reqs;
2069 -- Lock the rows: This is done as part of ECO 5209555
2070 BEGIN
2071
2072 for i in 1..p_req_id_tbl.COUNT LOOP
2073
2074 select bus_class_request_id into l_lock_id from pos_bus_class_reqs
2075 where bus_class_request_id = p_req_id_tbl(i) for update nowait;
2076
2077 END LOOP;
2078
2079 EXCEPTION
2080
2081 WHEN OTHERS THEN
2082 x_return_status :='E';
2083 x_msg_data := 'Cannot lock the rows';
2084 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2085 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'approve_mult_bus_class_reqs' , ' Cannot lock the rows ');
2086 END IF;
2087 raise_application_error(-20049, x_msg_data, true);
2088 END;
2089
2090 for i in 1..p_req_id_tbl.COUNT LOOP
2091 approve_bus_class_req
2092 (p_request_id => p_req_id_tbl(i),
2093 x_return_status => x_return_status,
2094 x_msg_count => x_msg_count,
2095 x_msg_data => x_msg_data
2096 );
2097 IF x_return_status IS NULL
2098 OR x_return_status <> fnd_api.g_ret_sts_success THEN
2099 rollback to approve_mult_bus_class_reqs;
2100 RETURN;
2101 END IF;
2102 END LOOP;
2103
2104 x_return_status := FND_API.G_RET_STS_SUCCESS;
2105
2106 EXCEPTION
2107 WHEN OTHERS THEN
2108 x_return_status :='E';
2109 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2110 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'approve_mult_bus_class_reqs' , x_msg_data);
2111 END IF;
2112 rollback to approve_mult_bus_class_reqs;
2113 raise_application_error(-20065, x_msg_data, true);
2114
2115 END approve_mult_bus_class_reqs;
2116
2117 PROCEDURE approve_mult_ps_reqs
2118 ( p_req_id_tbl IN po_tbl_number,
2119 x_return_status OUT nocopy VARCHAR2,
2120 x_msg_count OUT nocopy NUMBER,
2121 x_msg_data OUT nocopy VARCHAR2
2122 )
2123 IS
2124
2125 l_lock_id number;
2126
2127 BEGIN
2128 savepoint approve_mult_ps_reqs;
2129 -- Lock the rows: This is done as part of ECO 5209555
2130 BEGIN
2131
2132 for i in 1..p_req_id_tbl.COUNT LOOP
2133
2134 select ps_request_id into l_lock_id from pos_product_service_requests
2135 WHERE ps_request_id = p_req_id_tbl(i) for update nowait;
2136
2137 END LOOP;
2138
2139 EXCEPTION
2140
2141 WHEN OTHERS THEN
2142 x_return_status :='E';
2143 x_msg_data := 'Cannot lock the rows';
2144 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2145 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'approve_mult_ps_reqs' , ' Cannot lock the rows ');
2146 END IF;
2147 raise_application_error(-20066, x_msg_data, true);
2148 END;
2149
2150 for i in 1..p_req_id_tbl.COUNT LOOP
2151 approve_ps_req
2152 (p_request_id => p_req_id_tbl(i),
2153 x_return_status => x_return_status,
2154 x_msg_count => x_msg_count,
2155 x_msg_data => x_msg_data
2156 );
2157 IF x_return_status IS NULL
2158 OR x_return_status <> fnd_api.g_ret_sts_success THEN
2159 rollback to approve_mult_ps_reqs;
2160 RETURN;
2161 END IF;
2162 END LOOP;
2163
2164 x_return_status := FND_API.G_RET_STS_SUCCESS;
2165
2166 EXCEPTION
2167 WHEN OTHERS THEN
2168 x_return_status :='E';
2169 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2170 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'approve_mult_ps_reqs' , x_msg_data);
2171 END IF;
2172 rollback to approve_mult_ps_reqs;
2173 raise_application_error(-20065, x_msg_data, true);
2174
2175 END approve_mult_ps_reqs;
2176
2177
2178 PROCEDURE approve_update_mult_bc_reqs
2179 (
2180 p_pos_bus_rec_tbl IN pos_bus_rec_tbl,
2181 x_return_status OUT NOCOPY VARCHAR2,
2182 x_msg_count OUT NOCOPY NUMBER,
2183 x_msg_data OUT NOCOPY VARCHAR2
2184 )
2185 IS
2186 l_req_id_tbl po_tbl_number;
2187 l_lock_id NUMBER;
2188 BEGIN
2189 savepoint approve_update_mult_bc_reqs;
2190
2191 -- Lock the rows: This is done as part of ECO 5209555
2192 BEGIN
2193
2194 for i in 1..p_pos_bus_rec_tbl.COUNT LOOP
2195
2196 select BUS_CLASS_REQUEST_ID into l_lock_id from pos_bus_class_reqs
2197 WHERE BUS_CLASS_REQUEST_ID = p_pos_bus_rec_tbl(i).BUS_CLASS_REQUEST_ID for update nowait;
2198
2199 END LOOP;
2200
2201 EXCEPTION
2202
2203 WHEN OTHERS THEN
2204 x_return_status :='E';
2205 x_msg_data := 'Cannot lock the rows';
2206 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2207 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'approve_update_mult_bc_reqs' , ' Cannot lock the rows ');
2208 END IF;
2209 raise_application_error(-20064, x_msg_data, true);
2210 END;
2211
2212 l_req_id_tbl := PO_TBL_NUMBER();
2213
2214 for i in 1..p_pos_bus_rec_tbl.COUNT LOOP
2215
2216 UPDATE pos_bus_class_reqs
2217 SET CERTIFICATION_NO = p_pos_bus_rec_tbl(i).CERTIFICATION_NO,
2218 CERTIFICATION_AGENCY = p_pos_bus_rec_tbl(i).CERTIFICATION_AGENCY,
2219 EXPIRATION_DATE = p_pos_bus_rec_tbl(i).EXPIRATION_DATE
2220 WHERE BUS_CLASS_REQUEST_ID = p_pos_bus_rec_tbl(i).BUS_CLASS_REQUEST_ID;
2221
2222 l_req_id_tbl.extend;
2223 l_req_id_tbl(i) := p_pos_bus_rec_tbl(i).BUS_CLASS_REQUEST_ID;
2224
2225 END LOOP;
2226
2227 approve_mult_bus_class_reqs
2228 (p_req_id_tbl => l_req_id_tbl,
2229 x_return_status => x_return_status,
2230 x_msg_count => x_msg_count,
2231 x_msg_data => x_msg_data
2232 );
2233
2234 EXCEPTION
2235 WHEN OTHERS THEN
2236 x_return_status :='E';
2237 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2238 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'approve_update_mult_bc_reqs' , x_msg_data);
2239 END IF;
2240 rollback to approve_update_mult_bc_reqs;
2241 raise_application_error(-20063, x_msg_data, true);
2242
2243 END approve_update_mult_bc_reqs;
2244
2245
2246 PROCEDURE chg_address_req_approval
2247 (p_request_id IN NUMBER,
2248 p_party_site_name IN VARCHAR2,
2249 p_country IN VARCHAR2,
2250 p_address_line1 IN VARCHAR2,
2251 p_address_line2 IN VARCHAR2,
2252 p_address_line3 IN VARCHAR2,
2253 p_address_line4 IN VARCHAR2,
2254 p_city IN VARCHAR2,
2255 p_county IN VARCHAR2,
2256 p_state IN VARCHAR2,
2257 p_province IN VARCHAR2,
2258 p_postal_code IN VARCHAR2,
2259 p_phone_area_code IN VARCHAR2,
2260 p_phone_number IN VARCHAR2,
2261 p_fax_area_code IN VARCHAR2,
2262 p_fax_number IN VARCHAR2,
2263 p_email_address IN VARCHAR2,
2264 p_rfq_flag IN VARCHAR2,
2265 p_pay_flag IN VARCHAR2,
2266 p_pur_flag IN VARCHAR2,
2267 p_status IN VARCHAR2,
2268 x_return_status OUT nocopy VARCHAR2,
2269 x_msg_count OUT nocopy NUMBER,
2270 x_msg_data OUT nocopy VARCHAR2
2271 )
2272 IS
2273
2274 l_step NUMBER;
2275 l_party_site_id HZ_PARTY_SITES.party_site_id%TYPE;
2276 l_lock_id NUMBER;
2277
2278 BEGIN
2279
2280 savepoint chg_address_req_approval;
2281 -- Lock the rows: This is done as part of ECO 5209555
2282 BEGIN
2283 select ADDRESS_REQUEST_ID into l_lock_id from POS_ADDRESS_REQUESTS
2284 WHERE ADDRESS_REQUEST_ID = p_request_id for update nowait;
2285 EXCEPTION
2286
2287 WHEN OTHERS THEN
2288 x_return_status :='E';
2289 x_msg_data := 'Cannot lock the rows';
2290 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2291 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'chg_address_req_approval' , ' Cannot lock the rows ');
2292 END IF;
2293 raise_application_error(-20061, x_msg_data, true);
2294 END;
2295
2296 x_return_status := FND_API.G_RET_STS_SUCCESS;
2297
2298 l_step := 1;
2299
2300 UPDATE POS_ADDRESS_REQUESTS
2301 SET PARTY_SITE_NAME = p_party_site_name,
2302 COUNTRY = p_country,
2303 ADDRESS_LINE1 = p_address_line1,
2304 ADDRESS_LINE2 = p_address_line2,
2305 ADDRESS_LINE3 = p_address_line3,
2306 ADDRESS_LINE4 = p_address_line4,
2307 CITY = p_city,
2308 COUNTY = p_county,
2309 STATE = p_state,
2310 PROVINCE = p_province,
2311 POSTAL_CODE = p_postal_code,
2312 PHONE_AREA_CODE = p_phone_area_code,
2313 PHONE_NUMBER = p_phone_number,
2314 FAX_AREA_CODE = p_fax_area_code,
2315 FAX_NUMBER = p_fax_number,
2316 EMAIL_ADDRESS = p_email_address,
2317 RFQ_FLAG = p_rfq_flag,
2318 PAY_FLAG = p_pay_flag,
2319 PUR_FLAG = p_pur_flag,
2320 last_update_date = Sysdate,
2321 last_updated_by = fnd_global.user_id,
2322 last_update_login = fnd_global.login_id
2323 WHERE ADDRESS_REQUEST_ID = p_request_id;
2324
2325 l_step := 2;
2326
2327 approve_address_req
2328 (p_request_id => p_request_id,
2329 x_return_status => x_return_status,
2330 x_msg_count => x_msg_count,
2331 x_msg_data => x_msg_data
2332 );
2333
2334 l_step := 3;
2335
2336 -- The following code below supports address removal and
2337 -- incase during the change request itself the user
2338 -- decides to inactivate the address.
2339
2340 if p_status = 'I' then
2341
2342 select party_site_id
2343 into l_party_site_id
2344 from pos_address_requests
2345 where address_request_id = p_request_id;
2346
2347 l_step := 4;
2348
2349 POS_PROFILE_PKG.remove_address (
2350 p_party_site_id => l_party_site_id
2351 , x_status => x_return_status
2352 , x_exception_msg => x_msg_data
2353 );
2354
2355 end if;
2356
2357 l_step := 5;
2358
2359 EXCEPTION
2360 WHEN OTHERS THEN
2361 X_RETURN_STATUS :='E';
2362 x_msg_data := x_msg_data || ' Failure at step ' || l_step;
2363 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2364 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'chg_address_req_approval' , x_msg_data);
2365 END IF;
2366 rollback to chg_address_req_approval;
2367 raise_application_error(-20015, x_msg_data, true);
2368
2369 END chg_address_req_approval;
2370
2371 FUNCTION format_address(
2372 p_address_line1 IN VARCHAR2 DEFAULT NULL,
2373 p_address_line2 IN VARCHAR2 DEFAULT NULL,
2374 p_address_line3 IN VARCHAR2 DEFAULT NULL,
2375 p_address_line4 IN VARCHAR2 DEFAULT NULL,
2376 p_addr_city IN VARCHAR2 DEFAULT NULL,
2377 p_addr_postal_code IN VARCHAR2 DEFAULT NULL,
2378 p_addr_state IN VARCHAR2 DEFAULT NULL,
2379 p_addr_province IN VARCHAR2 DEFAULT NULL,
2380 p_addr_county IN VARCHAR2 DEFAULT NULL,
2381 p_addr_country IN VARCHAR2 DEFAULT NULL
2382 )RETURN VARCHAR2
2383 IS
2384 l_return_status VARCHAR2(1);
2385 l_msg_count NUMBER;
2386 l_msg_data NUMBER;
2387 l_formatted_address VARCHAR2(360);
2388
2389 l_tbl_cnt NUMBER;
2390 l_tbl HZ_FORMAT_PUB.string_tbl_type;
2391
2392 BEGIN
2393
2394 HZ_FORMAT_PUB.format_address (
2395 p_line_break => ' ',
2396 p_address_line_1 => p_address_line1,
2397 p_address_line_2 => p_address_line2,
2398 p_address_line_3 => p_address_line3,
2399 p_address_line_4 => p_address_line4,
2400 p_city => p_addr_city,
2401 p_postal_code => p_addr_postal_code,
2402 p_state => p_addr_state,
2403 p_province => p_addr_province,
2404 p_county => p_addr_county,
2405 p_country => p_addr_country,
2406 -- output parameters
2407 x_return_status => l_return_status,
2408 x_msg_count => l_msg_count,
2409 x_msg_data => l_msg_data,
2410 x_formatted_address => l_formatted_address,
2411 x_formatted_lines_cnt => l_tbl_cnt,
2412 x_formatted_address_tbl => l_tbl
2413 );
2414
2415 RETURN l_formatted_address;
2416
2417 EXCEPTION
2418 WHEN OTHERS THEN
2419 RETURN l_formatted_address;
2420 END format_address;
2421
2422
2423 PROCEDURE chg_contact_req_approval
2424 ( p_request_id IN NUMBER,
2425 p_contact_title IN VARCHAR2,
2426 p_first_name IN VARCHAR2,
2427 p_middle_name IN VARCHAR2,
2428 p_last_name IN VARCHAR2,
2429 p_alt_contact_name IN VARCHAR2,
2430 p_job_title IN VARCHAR2,
2431 p_department IN VARCHAR2,
2432 p_email_address IN VARCHAR2,
2433 p_url IN VARCHAR2,
2434 p_phone_area_code IN VARCHAR2,
2435 p_phone_number IN VARCHAR2,
2436 p_phone_extension IN VARCHAR2,
2437 p_alt_area_code IN VARCHAR2,
2438 p_alt_phone_number IN VARCHAR2,
2439 p_fax_area_code IN VARCHAR2,
2440 p_fax_number IN VARCHAR2,
2441 x_return_status OUT nocopy VARCHAR2,
2442 x_msg_count OUT nocopy NUMBER,
2443 x_msg_data OUT nocopy VARCHAR2
2444 )
2445 IS
2446 l_password VARCHAR2(200);
2447 l_lock_id number;
2448 BEGIN
2449 savepoint chg_contact_req_approval;
2450
2451 -- Lock the rows: This is done as part of ECO 5209555
2452 BEGIN
2453 select CONTACT_REQUEST_ID into l_lock_id from POS_CONTACT_REQUESTS
2454 WHERE CONTACT_REQUEST_ID = p_request_id for update nowait;
2455 EXCEPTION
2456
2457 WHEN OTHERS THEN
2458 x_return_status :='E';
2459 x_msg_data := 'Cannot lock the rows';
2460 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2461 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'chg_contact_req_approval' , ' Cannot lock the rows ');
2462 END IF;
2463 raise_application_error(-20061, x_msg_data, true);
2464 END;
2465
2466 x_return_status := FND_API.G_RET_STS_SUCCESS;
2467
2468
2469 UPDATE POS_CONTACT_REQUESTS
2470 SET CONTACT_TITLE = p_contact_title,
2471 FIRST_NAME = p_first_name,
2472 MIDDLE_NAME = p_middle_name,
2473 LAST_NAME = p_last_name,
2474 JOB_TITLE = p_job_title,
2475 DEPARTMENT=p_department,
2476 EMAIL_ADDRESS = p_email_address,
2477 PHONE_AREA_CODE = p_phone_area_code,
2478 PHONE_NUMBER = p_phone_number,
2479 PHONE_EXTENSION = p_phone_extension,
2480 FAX_AREA_CODE = p_fax_area_code,
2481 FAX_NUMBER = p_fax_number,
2482 ALT_CONTACT_NAME = p_alt_contact_name,
2483 ALT_AREA_CODE = p_alt_area_code,
2484 ALT_PHONE_NUMBER = p_alt_phone_number,
2485 URL = p_url,
2486 last_update_date = Sysdate,
2487 last_updated_by = fnd_global.user_id,
2488 last_update_login = fnd_global.login_id
2489 WHERE CONTACT_REQUEST_ID = p_request_id;
2490
2491 approve_contact_req
2492 (p_request_id => p_request_id,
2493 x_return_status => x_return_status,
2494 x_msg_count => x_msg_count,
2495 x_msg_data => x_msg_data,
2496 x_password => l_password
2497 );
2498
2499 EXCEPTION
2500 WHEN OTHERS THEN
2501 x_return_status :='E';
2502 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2503 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'chg_contact_req_approval' , x_msg_data);
2504 END IF;
2505 rollback to chg_contact_req_approval;
2506 raise_application_error(-20060, x_msg_data, true);
2507
2508 END chg_contact_req_approval;
2509
2510 PROCEDURE reject_mult_cont_addr_reqs
2511 ( p_cont_req_id IN NUMBER,
2512 p_req_id_tbl IN po_tbl_number,
2513 x_return_status OUT nocopy VARCHAR2,
2514 x_msg_count OUT nocopy NUMBER,
2515 x_msg_data OUT nocopy VARCHAR2
2516 )
2517 IS
2518
2519 l_lock_id number;
2520
2521 BEGIN
2522
2523 savepoint reject_mult_cont_addr_reqs;
2524
2525 -- Lock the rows: This is done as part of ECO 5209555
2526 BEGIN
2527
2528 for i in 1..p_req_id_tbl.COUNT LOOP
2529
2530 select CONT_ADDR_REQUEST_ID into l_lock_id from pos_cont_addr_requests
2531 WHERE PARTY_SITE_ID = p_req_id_tbl(i)
2532 and REQUEST_STATUS = 'PENDING'
2533 and CONTACT_REQ_ID = p_cont_req_id for update nowait;
2534
2535 END LOOP;
2536
2537 EXCEPTION
2538
2539 WHEN OTHERS THEN
2540 x_return_status :='E';
2541 x_msg_data := 'Cannot lock the rows';
2542 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2543 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'reject_mult_cont_addr_reqs' , ' Cannot lock the rows ');
2544 END IF;
2545 raise_application_error(-20059, x_msg_data, true);
2546 END;
2547
2548 for i in 1..p_req_id_tbl.COUNT LOOP
2549
2550 UPDATE pos_cont_addr_requests
2551 SET request_status = 'REJECTED'
2552 WHERE PARTY_SITE_ID = p_req_id_tbl(i)
2553 and REQUEST_STATUS = 'PENDING'
2554 and CONTACT_REQ_ID = p_cont_req_id;
2555
2556 END LOOP;
2557
2558 x_return_status := FND_API.G_RET_STS_SUCCESS;
2559
2560 EXCEPTION
2561 WHEN OTHERS THEN
2562 x_return_status :='E';
2563 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2564 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'reject_mult_cont_addr_reqs' , x_msg_data);
2565 END IF;
2566 rollback to reject_mult_cont_addr_reqs;
2567 raise_application_error(-20058, x_msg_data, true);
2568
2569 END reject_mult_cont_addr_reqs;
2570
2571 PROCEDURE new_contact_req_approval
2572 ( p_request_id IN NUMBER,
2573 p_contact_title IN VARCHAR2,
2574 p_first_name IN VARCHAR2,
2575 p_middle_name IN VARCHAR2,
2576 p_last_name IN VARCHAR2,
2577 p_job_title IN VARCHAR2,
2578 p_email_address IN VARCHAR2,
2579 p_phone_area_code IN VARCHAR2,
2580 p_phone_number IN VARCHAR2,
2581 p_phone_extension IN VARCHAR2,
2582 p_fax_area_code IN VARCHAR2,
2583 p_fax_number IN VARCHAR2,
2584 p_create_user_acc IN VARCHAR2,
2585 p_user_name IN VARCHAR2,
2586 x_user_id OUT nocopy NUMBER,
2587 x_cont_party_id OUT nocopy NUMBER,
2588 x_return_status OUT nocopy VARCHAR2,
2589 x_msg_count OUT nocopy NUMBER,
2590 x_msg_data OUT nocopy VARCHAR2,
2591 p_inactive_date IN DATE DEFAULT NULL,
2592 p_department IN VARCHAR2 DEFAULT NULL,
2593 p_alt_contact_name IN VARCHAR2 DEFAULT NULL,
2594 p_alt_area_code IN VARCHAR2 DEFAULT NULL,
2595 p_alt_phone_number IN VARCHAR2 DEFAULT NULL,
2596 p_url IN VARCHAR2 DEFAULT NULL
2597 )
2598 IS
2599 l_password VARCHAR2(200);
2600 l_lock_id number;
2601 BEGIN
2602
2603 savepoint new_contact_req_approval;
2604
2605 -- Lock the rows: This is done as part of ECO 5209555
2606 BEGIN
2607 select CONTACT_REQUEST_ID into l_lock_id from POS_CONTACT_REQUESTS
2608 WHERE CONTACT_REQUEST_ID = p_request_id for update nowait;
2609 EXCEPTION
2610
2611 WHEN OTHERS THEN
2612 x_return_status :='E';
2613 x_msg_data := 'Cannot lock the rows';
2614 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2615 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'new_contact_req_approval' , ' Cannot lock the rows ');
2616 END IF;
2617 raise_application_error(-20058, x_msg_data, true);
2618 END;
2619
2620 x_return_status := FND_API.G_RET_STS_SUCCESS;
2621 x_user_id := -1 ;
2622 x_cont_party_id := -1 ;
2623 UPDATE POS_CONTACT_REQUESTS
2624 SET CONTACT_TITLE = p_contact_title,
2625 FIRST_NAME = p_first_name,
2626 MIDDLE_NAME = p_middle_name,
2627 LAST_NAME = p_last_name,
2628 JOB_TITLE = p_job_title,
2629 DEPARTMENT = p_department,
2630 EMAIL_ADDRESS = p_email_address,
2631 PHONE_AREA_CODE = p_phone_area_code,
2632 PHONE_NUMBER = p_phone_number,
2633 PHONE_EXTENSION = p_phone_extension,
2634 FAX_AREA_CODE = p_fax_area_code,
2635 FAX_NUMBER = p_fax_number,
2636 ALT_CONTACT_NAME = p_alt_contact_name,
2637 ALT_AREA_CODE = p_alt_area_code,
2638 ALT_PHONE_NUMBER = p_alt_phone_number,
2639 URL = p_url,
2640 CREATE_USER_ACCOUNT = p_create_user_acc
2641 WHERE CONTACT_REQUEST_ID = p_request_id;
2642
2643 approve_contact_req
2644 (p_request_id => p_request_id,
2645 p_user_name => p_user_name,
2646 x_return_status => x_return_status,
2647 x_msg_count => x_msg_count,
2648 x_msg_data => x_msg_data,
2649 x_password => l_password,
2650 p_inactive_date => p_inactive_date
2651 );
2652
2653 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
2654 rollback to new_contact_req_approval;
2655 RETURN;
2656 END IF;
2657
2658 if p_create_user_acc = 'Y' then
2659
2660 SELECT fu.user_id,fu.PERSON_PARTY_ID
2661 into x_user_id, x_cont_party_id
2662 FROM fnd_user fu, pos_contact_requests pcr
2663 WHERE pcr.CONTACT_PARTY_ID = fu.PERSON_PARTY_ID
2664 and pcr.contact_request_id = p_request_id;
2665
2666 ELSE
2667 select pcr.CONTACT_PARTY_ID
2668 into x_cont_party_id
2669 from pos_contact_requests pcr
2670 where pcr.contact_request_id = p_request_id;
2671
2672 end if;
2673
2674 EXCEPTION
2675 WHEN OTHERS THEN
2676 x_return_status :='E';
2677 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2678 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'new_contact_req_approval' , x_msg_data);
2679 END IF;
2680 rollback to new_contact_req_approval;
2681 raise_application_error(-20057, x_msg_data, true);
2682
2683 END new_contact_req_approval;
2684
2685
2686 PROCEDURE assign_mult_address_to_contact
2687 ( p_site_id_tbl IN po_tbl_number,
2688 p_cont_party_id IN NUMBER,
2689 p_vendor_id IN NUMBER,
2690 x_return_status OUT nocopy VARCHAR2,
2691 x_msg_count OUT nocopy NUMBER,
2692 x_msg_data OUT nocopy VARCHAR2
2693 )
2694 IS
2695 BEGIN
2696 for i in 1..p_site_id_tbl.COUNT LOOP
2697 pos_supplier_address_pkg.assign_address_to_contact
2698 (p_contact_party_id => p_cont_party_id,
2699 p_org_party_site_id => p_site_id_tbl(i),
2700 p_vendor_id => p_vendor_id,
2701 x_return_status => x_return_status,
2702 x_msg_count => x_msg_count,
2703 x_msg_data => x_msg_data
2704 );
2705 IF x_return_status IS NULL
2706 OR x_return_status <> fnd_api.g_ret_sts_success THEN
2707 RETURN;
2708 END IF;
2709 END LOOP;
2710
2711 x_return_status := FND_API.G_RET_STS_SUCCESS;
2712 END assign_mult_address_to_contact;
2713
2714 PROCEDURE assign_user_sec_attr
2715 ( p_req_id_tbl IN po_tbl_number,
2716 p_usr_id IN NUMBER,
2717 p_code_name IN VARCHAR2,
2718 x_return_status OUT nocopy VARCHAR2,
2719 x_msg_count OUT nocopy NUMBER,
2720 x_msg_data OUT nocopy VARCHAR2
2721 )
2722 IS
2723 BEGIN
2724 for i in 1..p_req_id_tbl.COUNT LOOP
2725 POS_USER_ADMIN_PKG.CreateSecAttr
2726 (p_user_id => p_usr_id,
2727 p_attribute_code => p_code_name,
2728 p_app_id => 177,
2729 p_number_value => p_req_id_tbl(i)
2730 );
2731 END LOOP;
2732 x_return_status := FND_API.G_RET_STS_SUCCESS;
2733 END assign_user_sec_attr;
2734
2735 PROCEDURE update_addr_req_status
2736 (p_request_id IN NUMBER,
2737 p_party_site_id IN NUMBER,
2738 p_req_status IN VARCHAR2,
2739 x_return_status OUT nocopy VARCHAR2,
2740 x_msg_count OUT nocopy NUMBER,
2741 x_msg_data OUT nocopy VARCHAR2
2742 )
2743 IS
2744
2745 l_lock_id number;
2746 cursor l_cont_addr_cur is
2747 select address_req_id from pos_cont_addr_requests
2748 where address_req_id = p_request_id for update nowait;
2749 l_cont_addr_rec l_cont_addr_cur%ROWTYPE;
2750
2751 BEGIN
2752 savepoint update_addr_req_status;
2753
2754 -- Lock the rows: This is done as part of ECO 5209555
2755 BEGIN
2756
2757 select address_request_id into l_lock_id from pos_address_requests
2758 WHERE address_request_id = p_request_id for update nowait;
2759
2760 open l_cont_addr_cur;
2761 fetch l_cont_addr_cur into l_cont_addr_rec;
2762 close l_cont_addr_cur;
2763
2764 EXCEPTION
2765
2766 WHEN OTHERS THEN
2767 x_return_status :='E';
2768 x_msg_data := 'Cannot lock the rows';
2769 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2770 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'update_addr_req_status' , ' Cannot lock the rows ');
2771 END IF;
2772 raise_application_error(-20056, x_msg_data, true);
2773 END;
2774
2775 UPDATE pos_address_requests
2776 SET request_status = p_req_status,
2777 PARTY_SITE_ID = p_party_site_id,
2778 last_update_date = Sysdate,
2779 last_updated_by = fnd_global.user_id,
2780 last_update_login = fnd_global.login_id
2781 WHERE address_request_id = p_request_id;
2782
2783 UPDATE pos_cont_addr_requests
2784 SET party_site_id = p_party_site_id,
2785 last_update_date = Sysdate,
2786 last_updated_by = fnd_global.user_id,
2787 last_update_login = fnd_global.login_id
2788 WHERE address_req_id = p_request_id
2789 AND request_status = 'PENDING'
2790 AND party_site_id IS NULL;
2791
2792 -- Inform banking that the address request now has a party site.
2793 if p_request_id is not null and p_party_site_id is not null then
2794 POS_SBD_PKG.sbd_handle_address_apv(
2795 p_address_request_id => p_request_id
2796 , p_party_site_id => p_party_site_id
2797 , x_status => x_return_status
2798 , x_exception_msg => x_msg_data
2799 );
2800 end if;
2801
2802 x_return_status := fnd_api.g_ret_sts_success;
2803
2804 EXCEPTION
2805 WHEN OTHERS THEN
2806 x_return_status :='E';
2807 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2808 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'update_addr_req_status' , x_msg_data);
2809 END IF;
2810 rollback to update_addr_req_status;
2811 raise_application_error(-20055, x_msg_data, true);
2812
2813 END update_addr_req_status;
2814
2815 PROCEDURE get_ou_count
2816 (
2817 x_ou_count OUT nocopy NUMBER,
2818 x_return_status OUT nocopy VARCHAR2,
2819 x_msg_count OUT nocopy NUMBER,
2820 x_msg_data OUT nocopy VARCHAR2
2821 )
2822 IS
2823 l_ou_ids pos_security_profile_utl_pkg.number_table;
2824 BEGIN
2825 pos_security_profile_utl_pkg.get_current_ous (l_ou_ids, x_ou_count);
2826 x_return_status := fnd_api.g_ret_sts_success;
2827 END get_ou_count ;
2828
2829 PROCEDURE upd_address_to_contact_rel
2830 ( p_mapping_id IN NUMBER,
2831 p_cont_party_id IN NUMBER,
2832 p_cont_req_id IN NUMBER,
2833 p_addr_req_id IN NUMBER,
2834 p_party_site_id IN NUMBER,
2835 p_request_type IN VARCHAR2,
2836 x_return_status OUT nocopy VARCHAR2,
2837 x_msg_data OUT nocopy VARCHAR2
2838 )
2839 is
2840 l_count NUMBER;
2841 l_rec_req_type VARCHAR2(10);
2842 cursor req_rec_exists(p_mapping_id IN NUMBER,p_cont_party_id IN NUMBER, p_cont_req_id IN NUMBER,p_addr_req_id IN NUMBER,p_party_site_id IN NUMBER, p_req_type IN VARCHAR2)
2843 is
2844 select cont_addr_request_id
2845 from pos_cont_addr_requests
2846 where mapping_id = p_mapping_id
2847 and nvl(contact_party_id, -1) = nvl(p_cont_party_id, -1)
2848 and nvl(contact_req_id, -1) = nvl(p_cont_req_id, -1)
2849 and nvl(ADDRESS_REQ_ID, -1) = nvl(p_addr_req_id, -1)
2850 and request_type = p_req_type
2851 and request_status = 'PENDING'
2852 and nvl(party_site_id, -1) in
2853 (
2854 select party_site_id
2855 from hz_party_sites
2856 where location_id in
2857 (
2858 select location_id
2859 from hz_party_sites
2860 where party_site_id = nvl(p_party_site_id, -1)
2861 )
2862 ) FOR UPDATE NOWAIT;
2863 l_req_rec_exists_rec req_rec_exists%ROWTYPE;
2864 begin
2865
2866 x_return_status := 'N';
2867 if(p_request_type = 'DELETE') then
2868 l_rec_req_type := 'ADD';
2869 elsif(p_request_type = 'ADD') then
2870 l_rec_req_type := 'DELETE';
2871 end if;
2872
2873 open req_rec_exists(p_mapping_id, p_cont_party_id, p_cont_req_id, p_addr_req_id, p_party_site_id, l_rec_req_type);
2874 LOOP
2875 fetch req_rec_exists into l_req_rec_exists_rec;
2876 EXIT WHEN req_rec_exists%NOTFOUND;
2877 x_return_status := 'Y';
2878 update pos_cont_addr_requests
2879 set request_status = 'DELETED'
2880 where cont_addr_request_id = l_req_rec_exists_rec.cont_addr_request_id;
2881 END LOOP;
2882 close req_rec_exists;
2883
2884 EXCEPTION
2885
2886 WHEN OTHERS THEN
2887 x_return_status :='E';
2888 x_msg_data := 'Cannot lock the rows';
2889 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2890 fnd_log.string(fnd_log.level_statement, g_module || '.' || 'Manage Address' , ' Cannot lock the rows ');
2891 END IF;
2892 raise_application_error(-20086, x_msg_data, true);
2893
2894 END upd_address_to_contact_rel;
2895
2896 FUNCTION get_cont_req_id(
2897 p_contact_party_id IN NUMBER
2898 ) RETURN NUMBER
2899 IS
2900 l_cont_req_id NUMBER;
2901 BEGIN
2902 select contact_request_id
2903 into l_cont_req_id
2904 from pos_contact_requests
2905 where contact_party_id = p_contact_party_id
2906 and request_status = 'PENDING';
2907
2908 return l_cont_req_id ;
2909 EXCEPTION
2910 WHEN OTHERS THEN
2911 RETURN -1 ;
2912 END;
2913
2914 END pos_profile_change_request_pkg;