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