[Home] [Help]
PACKAGE BODY: APPS.PO_AP_MERGE_GRP
Source
1 PACKAGE BODY PO_AP_MERGE_GRP AS
2 /* $Header: POXPVENB.pls 115.24 2004/05/26 21:58:29 mbhargav noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PO_AP_MERGE_GRP';
5
6 G_MODULE_PREFIX CONSTANT VARCHAR2(50) := 'po.plsql.'|| G_PKG_NAME|| '.';
7
8 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
9 g_debug_unexp CONSTANT BOOLEAN := PO_DEBUG.is_debug_unexp_on;
10
11
12 PO_INVALID_VENDOR_SITE_ID EXCEPTION;
13 PO_GA_REFERENCING_DOCS_EXIST EXCEPTION;
14 PO_ENABLED_OU_SITE_UNDEFINED EXCEPTION;
15 PO_GA_ENABLED_REF_DOCS_EXIST EXCEPTION;
16 PO_CONSIGNMENT_EXIST EXCEPTION;
17
18 -- <VENDOR MERGE FPJ START>
19 GA_FOR_SITE_AND_PGOA_SITE_DIFF EXCEPTION;
20 GA_FOR_SITE_W_POREF_FRM_OTR_OU EXCEPTION;
21 GA_FOR_SITE_W_POREF_DIFF_SITE EXCEPTION;
22 PGOA_FOR_SITE_AND_GA_SITE_DIFF EXCEPTION;
23 SPO_FOR_SITE_AND_GA_SITE_DIFF EXCEPTION;
24 REQ_FOR_VDR_REF_GA_IN_OTHER_OU EXCEPTION;
25 REQ_FOR_SITE_REF_GA_DIFF_SITE EXCEPTION;
26 GA_FOR_SITE_W_REQREF_DIFF_SITE EXCEPTION;
27 -- <VENDOR MERGE FPJ END>
28
29 /*=========================================================================*/
30 /*====================== SPECIFICATIONS (PRIVATE) =========================*/
31 /*=========================================================================*/
32
33
34 -- <VENDOR MERGE FPJ START>
35
36
37 PROCEDURE get_vdr_and_site_name
38 ( p_vendor_id IN NUMBER,
39 p_vendor_site_id IN NUMBER,
40 x_vendor_name OUT NOCOPY VARCHAR2,
41 x_vendor_site_code OUT NOCOPY VARCHAR2
42 );
43
44 PROCEDURE update_req_line_vdr_info
45 ( p_from_vendor_id IN NUMBER,
46 p_from_site_id IN NUMBER,
47 p_to_vendor_id IN NUMBER,
48 p_to_site_id IN NUMBER
49 );
50
51 PROCEDURE update_req_temp_vdr_info
52 ( p_from_vendor_id IN NUMBER,
53 p_from_site_id IN NUMBER,
54 p_to_vendor_id IN NUMBER,
55 p_to_site_id IN NUMBER
56 );
57
58 PROCEDURE update_fte_vdr_info
59 ( p_from_vendor_id IN NUMBER,
60 p_from_site_id IN NUMBER,
61 p_to_vendor_id IN NUMBER,
62 p_to_site_id IN NUMBER
63 );
64
65 -- bug3237045
66 PROCEDURE update_okc_info
67 ( p_from_vendor_id IN NUMBER,
68 p_from_site_id IN NUMBER,
69 p_to_vendor_id IN NUMBER,
70 p_to_site_id IN NUMBER
71 );
72
73 -- Starting from 11i FPJ we no longer need the following functions as
74 -- we will embed all logic within validate_merge
75
76 -- FUNCTION get_vendor_site_code
77 -- ( p_vendor_site_id IN PO_VENDOR_SITES.vendor_site_id%TYPE
78 -- ) RETURN VARCHAR2;
79 --
80 -- FUNCTION supplier_site_exist
81 -- ( p_org_id IN PO_VENDOR_SITES_ALL.org_id%TYPE,
82 -- p_vendor_id IN PO_VENDOR_SITES_ALL.vendor_id%TYPE,
83 -- p_vendor_site_code IN PO_VENDOR_SITES_ALL.vendor_site_code%TYPE
84 -- ) RETURN BOOLEAN;
85 --
86 --FUNCTION referencing_asl_exist
87 --( p_po_header_id IN PO_HEADERS_ALL.po_header_id%TYPE,
88 -- p_call_from IN VARCHAR2
89 --) RETURN BOOLEAN;
90 --
91 --FUNCTION referencing_docs_exist
92 --( p_po_header_id IN PO_HEADERS_ALL.po_header_id%TYPE
93 --) RETURN BOOLEAN;
94 --
95
96 -- <VENDOR MERGE FPJ END>
97
98
99 /*=========================================================================*/
100 /*========================== BODY (PUBLIC) ================================*/
101 /*=========================================================================*/
102
103 /**==========================================================================
104 *
105 * PUBLIC PROCEDURE : validate_merge <GA FPI>
106 *
107 * REQUIRES:
108 * The TO vendor_site_id must be defined in po_vendor_sites_all.
109 *
110 * MODIFIES:
111 * API Message List - any messages will be appended to the API Message List
112 *
113 * EFFECTS:
114 * Determines if it is not ok to perform a Supplier Merge.
115 *
116 * RETURNS:
117 * x_return_status - (a) FND_API.G_RET_STS_SUCCESS if validation successful
118 * (b) FND_API.G_RET_STS_ERROR if error during validation
119 * (c) FND_API.G_RET_STS_UNEXP_ERROR if unexpected error
120 *
121 * x_result - (a) FND_API.G_TRUE if restrictions are met
122 * (b) FND_API.G_FALSE if not ok to perform Supplier Merge
123 *
124 *===========================================================================
125 */
126 PROCEDURE validate_merge
127 (
128 p_api_version IN NUMBER,
129 x_return_status OUT NOCOPY VARCHAR2,
130 p_from_vendor_id IN PO_VENDORS.vendor_id%TYPE,
131 p_from_site_id IN PO_VENDOR_SITES_ALL.vendor_site_id%TYPE,
132 p_to_vendor_id IN PO_VENDORS.vendor_id%TYPE,
133 p_to_site_id IN PO_VENDOR_SITES_ALL.vendor_site_id%TYPE,
134 x_result OUT NOCOPY VARCHAR2
135 )
136 IS
137 l_api_name CONSTANT VARCHAR2(30) := 'validate_merge';
138 l_api_version CONSTANT NUMBER := 1.0;
139 l_module FND_LOG_MESSAGES.module%TYPE :=
140 G_MODULE_PREFIX || l_api_name;
141
142 l_from_site_code PO_VENDOR_SITES_ALL.vendor_site_code%TYPE;
143
144 x_validation_error VARCHAR2(80);
145 l_consigned_ret_sts VARCHAR2(1);
146 l_consigned_msg_count NUMBER;
147 l_consigned_msg_data VARCHAR2(2000);
148 l_consigned_can_merge VARCHAR2(1);
149
150 l_from_vendor_name PO_VENDORS.vendor_name%TYPE; --bug 2814321
151
152 l_pass_val VARCHAR2(1) := 'Y'; -- <VENDOR MERGE FPJ>
153
154 l_progress VARCHAR2(3);
155 BEGIN
156
157 IF (g_debug_stmt) THEN
158 PO_DEBUG.debug_begin
159 ( p_log_head => l_module
160 );
161 END IF;
162
163 x_return_status := FND_API.G_RET_STS_SUCCESS; -- Initialize return status
164
165 l_progress := '000';
166
167 -- <VENDOR MERGE FPJ START>
168
169 IF (NOT FND_API.Compatible_API_Call
170 ( p_current_version_number => l_api_version,
171 p_caller_version_number => p_api_version,
172 p_api_name => l_api_name,
173 p_pkg_name => g_pkg_name
174 )
175 ) THEN
176
177 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
178 END IF;
179
180 -- get vendor name info in case we need to report an error
181 get_vdr_and_site_name
182 ( p_vendor_id => p_from_vendor_id,
183 p_vendor_site_id => p_from_site_id,
184 x_vendor_name => l_from_vendor_name,
185 x_vendor_site_code => l_from_site_code
186 );
187
188 IF (g_debug_stmt) THEN
189 PO_DEBUG.debug_stmt
190 ( p_log_head => l_module,
191 p_token => l_progress,
192 p_message => l_progress || ': vdrname = ' || l_from_vendor_name ||
193 ', sitecode = ' || l_from_site_code
194 );
195 END IF;
196
197 l_progress := '010';
198
199 IF (p_from_vendor_id <> p_to_vendor_id) THEN
200 -- All the new checks are performed only if from vendor and to vendor
201 -- are different.
202
203 l_progress := '020';
204
205 -- Check 1
206 -- SQL What:Prevent Merge, if there is GA/GC for from supplier/site in
207 -- current OU and a purchasing site exists in PGOA which is
208 -- different from the vendor site on the doc header
209
210 BEGIN
211 SELECT 'F'
212 INTO l_pass_val
213 FROM dual
214 WHERE EXISTS (
215 SELECT NULL
216 FROM po_headers GA,
217 po_ga_org_assignments PGOA
218 WHERE GA.global_agreement_flag = 'Y'
219 AND GA.vendor_id = p_from_vendor_id
220 AND GA.vendor_site_id = p_from_site_id
221 AND GA.po_header_id = PGOA.po_header_id
222 AND PGOA.vendor_site_id <> p_from_site_id );
223
224 IF (l_pass_val = 'F') THEN
225 RAISE GA_FOR_SITE_AND_PGOA_SITE_DIFF;
226 END IF;
227 EXCEPTION
228 WHEN NO_DATA_FOUND THEN
229 NULL;
230 END;
231
232 IF (g_debug_stmt) THEN
233 PO_DEBUG.debug_stmt
234 ( p_log_head => l_module,
235 p_token => l_progress,
236 p_message => l_progress || ': passed check 1'
237 );
238 END IF;
239
240 l_progress := '030';
241
242 -- Check 2
243 -- SQL What: Prevent Merge, if there is GA/GC for from supplier/site in
244 -- current OU and there are execution docs in other OU
245 -- referencing the GA/GC
246
247 BEGIN
248 SELECT 'F'
249 INTO l_pass_val
250 FROM dual
251 WHERE EXISTS (
252 SELECT NULL
253 FROM po_headers GA,
254 po_lines_all POL
255 WHERE GA.global_agreement_flag = 'Y'
256 AND GA.vendor_id = p_from_vendor_id
257 AND GA.vendor_site_id = p_from_site_id
258 AND POL.org_id <> GA.org_id
259 AND GA.po_header_id IN (POL.contract_id,
260 POL.from_header_id));
261
262 IF (l_pass_val = 'F') THEN
263 RAISE GA_FOR_SITE_W_POREF_FRM_OTR_OU;
264 END IF;
265 EXCEPTION
266 WHEN NO_DATA_FOUND THEN
267 NULL;
268 END;
269
270 IF (g_debug_stmt) THEN
271 PO_DEBUG.debug_stmt
272 ( p_log_head => l_module,
273 p_token => l_progress,
274 p_message => l_progress || ': passed check 2'
275 );
276 END IF;
277
278 l_progress := '040';
279
280 -- Check 3:
281 -- SQL What:Prevent Merge, if there is GA/GC for from supplier/site in
282 -- current OU and there are executeion docs in current OU
283 -- referencing the GA/GC but the PO has a diff vendor site
284
285 BEGIN
286 SELECT 'F'
287 INTO l_pass_val
288 FROM dual
289 WHERE EXISTS (
290 SELECT NULL
291 FROM po_headers GA,
292 po_lines POL,
293 po_headers POH
294 WHERE GA.global_agreement_flag = 'Y'
295 AND GA.vendor_id = p_from_vendor_id
296 AND GA.vendor_site_id = p_from_site_id
297 AND GA.po_header_id IN (POL.contract_id,
298 POL.from_header_id)
299 AND POH.po_header_id = POL.po_header_id
300 AND POH.vendor_id = p_from_vendor_id
301 AND POH.vendor_site_id <> p_from_site_id);
302
303 IF (l_pass_val = 'F') THEN
304 RAISE GA_FOR_SITE_W_POREF_DIFF_SITE;
305 END IF;
306 EXCEPTION
307 WHEN NO_DATA_FOUND THEN
308 NULL;
309 END;
310
311 IF (g_debug_stmt) THEN
312 PO_DEBUG.debug_stmt
313 ( p_log_head => l_module,
314 p_token => l_progress,
315 p_message => l_progress || ': passed check 3'
316 );
317 END IF;
318
319 l_progress := '050';
320
321 -- Check 4:
322 -- SQL What:Prevent Merge, if there is a GA/GC in another OU, where
323 -- from vendor site is defined as Purchasing site.
324
325 BEGIN
326 SELECT 'F'
327 INTO l_pass_val
328 FROM dual
329 WHERE EXISTS (
330 SELECT NULL
331 FROM po_headers_all GA,
335 AND GA.po_header_id = PGOA.po_header_id
332 po_ga_org_assignments PGOA
333 WHERE GA.global_agreement_flag = 'Y'
334 AND GA.vendor_id = p_from_vendor_id
336 AND PGOA.vendor_site_id = p_from_site_id
337 AND GA.vendor_site_id <> p_from_site_id);
338
339 IF (l_pass_val = 'F') THEN
340 RAISE PGOA_FOR_SITE_AND_GA_SITE_DIFF;
341 END IF;
342
343 EXCEPTION
344 WHEN NO_DATA_FOUND THEN
345 NULL;
346 END;
347
348 IF (g_debug_stmt) THEN
349 PO_DEBUG.debug_stmt
350 ( p_log_head => l_module,
351 p_token => l_progress,
352 p_message => l_progress || ': passed check 4'
353 );
354 END IF;
355
356 l_progress := '060';
357
358 -- Check 5:
359 -- SQL What:Prevent Merge, if there are execution docs in current OU
360 -- with from supplier/site, referencing a GA/GC, but the GA/GC
361 -- has a diff vendor site on the header
362
363 BEGIN
364 SELECT 'F'
365 INTO l_pass_val
366 FROM dual
367 WHERE EXISTS (
368 SELECT NULL
369 FROM po_headers POH,
370 po_lines POL,
371 po_headers_all GA
372 WHERE POH.vendor_id = p_from_vendor_id
373 AND POH.vendor_site_id = p_from_site_id
374 AND POH.po_header_id = POL.po_header_id
375 AND GA.po_header_id IN (POL.contract_id,
376 POL.from_header_id)
377 AND GA.global_agreement_flag = 'Y'
378 AND GA.vendor_id = p_from_vendor_id
379 AND GA.vendor_site_id <> POH.vendor_site_id);
380
381 IF (l_pass_val = 'F') THEN
382 RAISE SPO_FOR_SITE_AND_GA_SITE_DIFF;
383 END IF;
384
385 EXCEPTION
386 WHEN NO_DATA_FOUND THEN
387 NULL;
388 END;
389
390 IF (g_debug_stmt) THEN
391 PO_DEBUG.debug_stmt
392 ( p_log_head => l_module,
393 p_token => l_progress,
394 p_message => l_progress || ': passed check 5'
395 );
396 END IF;
397
398 l_progress := '070';
399
400 -- Check 6:
401 -- SQL WHat:Prevent Merge, if there are requisitions in current OU
402 -- for the from vendor that references a GA/GC owned by
403 -- another OU.
404
405 BEGIN
406 SELECT 'F'
407 INTO l_pass_val
408 FROM dual
409 WHERE EXISTS (
410 SELECT NULL
411 FROM po_requisition_lines RL,
412 po_headers_all GA
413 WHERE RL.vendor_id = p_from_vendor_id
414 AND RL.blanket_po_header_id = GA.po_header_id
415 AND GA.global_agreement_flag = 'Y'
416 AND GA.org_id <> RL.org_id);
417
418 IF (l_pass_val = 'F') THEN
419 RAISE REQ_FOR_VDR_REF_GA_IN_OTHER_OU;
420 END IF;
421
422 EXCEPTION
423 WHEN NO_DATA_FOUND THEN
424 NULL;
425 END;
426
427 IF (g_debug_stmt) THEN
428 PO_DEBUG.debug_stmt
429 ( p_log_head => l_module,
430 p_token => l_progress,
431 p_message => l_progress || ': passed check 6'
432 );
433 END IF;
434
435 l_progress := '080';
436
437 -- Check 7:
438 -- SQL What:Prevent Purge, if there are reqs in any OU with the from
439 -- vendor and from vendor site, referencing GA/GC in current
443 BEGIN
440 -- OU, but the vendor/site on the GA/GC is different from
441 -- the from vendor/site
442
444 SELECT 'F'
445 INTO l_pass_val
446 FROM dual
447 WHERE EXISTS (
448 SELECT NULL
449 FROM po_requisition_lines_all RL,
450 po_headers GA
451 WHERE RL.vendor_id = p_from_vendor_id
452 AND RL.vendor_site_id = p_from_site_id
453 AND RL.blanket_po_header_id = GA.po_header_id
454 AND GA.global_agreement_flag = 'Y'
455 AND GA.vendor_id = p_from_vendor_id
456 AND GA.vendor_site_id <> p_from_site_id);
457
458 IF (l_pass_val = 'F') THEN
459 RAISE REQ_FOR_SITE_REF_GA_DIFF_SITE;
460 END IF;
461
462 EXCEPTION
463 WHEN NO_DATA_FOUND THEN
464 NULL;
465 END;
466
467 IF (g_debug_stmt) THEN
468 PO_DEBUG.debug_stmt
469 ( p_log_head => l_module,
470 p_token => l_progress,
471 p_message => l_progress || ': passed check 7'
472 );
473 END IF;
474
475 l_progress := '090';
476
477 -- Check 8:
478 -- SQL What:Prevent Merge, if there are requisitions in any OU
479 -- referencing GA/GC with the from vendor/site, but the
480 -- req may have a different suggested vendor site
481
482 BEGIN
483 SELECT 'F'
484 INTO l_pass_val
485 FROM dual
486 WHERE EXISTS (
487 SELECT NULL
488 FROM po_requisition_lines_all RL,
489 po_headers GA
490 WHERE GA.vendor_id = p_from_vendor_id
491 AND GA.vendor_site_id = p_from_site_id
492 AND GA.global_agreement_flag = 'Y'
493 AND GA.po_header_id = RL.blanket_po_header_id
494 AND RL.vendor_id = p_from_vendor_id
495 AND RL.vendor_site_id <> p_from_site_id);
496
497 IF (l_pass_val = 'F') THEN
498 RAISE GA_FOR_SITE_W_REQREF_DIFF_SITE;
499 END IF;
500
501 EXCEPTION
502 WHEN NO_DATA_FOUND THEN
503 NULL;
504 END;
505
506 IF (g_debug_stmt) THEN
507 PO_DEBUG.debug_stmt
508 ( p_log_head => l_module,
509 p_token => l_progress,
510 p_message => l_progress || ': passed check 8'
511 );
512 END IF;
513
514 END IF; -- p_from_vendor_id <> p_to_vendor_id
515
516 -- <VENDOR MERGE FPJ END>
517
518 l_progress := '100';
519
520 IF (g_debug_stmt) THEN
521 PO_DEBUG.debug_stmt
522 ( p_log_head => l_module,
523 p_token => l_progress,
524 p_message => l_progress || ': checking consignments'
525 );
526 END IF;
527
528 -- Check for any consignements for the from supplier
529
530 PO_THIRD_PARTY_STOCK_GRP.validate_supplier_merge(
531 p_api_version => 1.0
532 , p_init_msg_list => NULL
533 , p_commit => NULL
534 , p_validation_level => NULL
535 , x_return_status => l_consigned_ret_sts
536 , x_msg_count => l_consigned_msg_count
537 , x_msg_data => l_consigned_msg_data
538 , p_vendor_site_id => p_from_site_id
539 , p_vendor_id => p_from_vendor_id --bug 3649022
540 , x_can_merge => l_consigned_can_merge
541 , x_validation_error => x_validation_error
542 );
543
544 IF (l_consigned_ret_sts = FND_API.G_RET_STS_SUCCESS) THEN
545 IF (l_consigned_can_merge = FND_API.G_FALSE) THEN
546 RAISE PO_CONSIGNMENT_EXIST;
547 END IF;
548 ELSE
549 x_result := FND_API.G_FALSE;
550 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
551 END IF;
552
553 IF (g_debug_stmt) THEN
554 PO_DEBUG.debug_end
555 ( p_log_head => l_module
556 );
557 END IF;
558
559 -- If no exceptions were raised in previous loops, then validation passed
560 x_result := FND_API.G_TRUE;
561
562 EXCEPTION
563
564 WHEN GA_FOR_SITE_AND_PGOA_SITE_DIFF THEN
565 x_result := FND_API.G_FALSE;
566 x_return_status := FND_API.G_RET_STS_ERROR;
567
568 IF (g_debug_stmt) THEN
569 PO_DEBUG.debug_stmt
573 );
570 ( p_log_head => l_module,
571 p_token => l_progress,
572 p_message => l_progress || ': violated rule 1'
574 END IF;
575
576 FND_MESSAGE.set_name('PO', 'PO_MERGE_GA_PGOA_SITE_DIFF');
577 FND_MESSAGE.set_token('FROM_VENDOR', l_from_vendor_name);
578 FND_MESSAGE.set_token('FROM_VENDOR_SITE', l_from_site_code);
579
580 APP_EXCEPTION.raise_exception;
581
582 WHEN GA_FOR_SITE_W_POREF_FRM_OTR_OU THEN
583 x_result := FND_API.G_FALSE;
584 x_return_status := FND_API.G_RET_STS_ERROR;
585
586 IF (g_debug_stmt) THEN
587 PO_DEBUG.debug_stmt
588 ( p_log_head => l_module,
589 p_token => l_progress,
590 p_message => l_progress || ': violated rule 2'
591 );
592 END IF;
593
594 FND_MESSAGE.set_name('PO', 'PO_MERGE_GA_W_POREF_FRM_OTR_OU');
595 FND_MESSAGE.set_token('FROM_VENDOR', l_from_vendor_name);
596 FND_MESSAGE.set_token('FROM_VENDOR_SITE', l_from_site_code);
597
598 APP_EXCEPTION.raise_exception;
599
600 WHEN GA_FOR_SITE_W_POREF_DIFF_SITE THEN
601 x_result := FND_API.G_FALSE;
602 x_return_status := FND_API.G_RET_STS_ERROR;
603
604 IF (g_debug_stmt) THEN
605 PO_DEBUG.debug_stmt
606 ( p_log_head => l_module,
607 p_token => l_progress,
608 p_message => l_progress || ': violated rule 3'
609 );
610 END IF;
611
612 FND_MESSAGE.set_name('PO', 'PO_MERGE_GA_W_POREF_DIFF_SITE');
613 FND_MESSAGE.set_token('FROM_VENDOR', l_from_vendor_name);
614 FND_MESSAGE.set_token('FROM_VENDOR_SITE', l_from_site_code);
615
616 APP_EXCEPTION.raise_exception;
617
618 WHEN PGOA_FOR_SITE_AND_GA_SITE_DIFF THEN
619 x_result := FND_API.G_FALSE;
620 x_return_status := FND_API.G_RET_STS_ERROR;
621
622 IF (g_debug_stmt) THEN
623 PO_DEBUG.debug_stmt
624 ( p_log_head => l_module,
625 p_token => l_progress,
626 p_message => l_progress || ': violated rule 4'
627 );
628 END IF;
629
630 FND_MESSAGE.set_name('PO', 'PO_MERGE_PGOA_GA_SITE_DIFF');
631 FND_MESSAGE.set_token('FROM_VENDOR_SITE', l_from_site_code);
632
633 APP_EXCEPTION.raise_exception;
634
635 WHEN SPO_FOR_SITE_AND_GA_SITE_DIFF THEN
636 x_result := FND_API.G_FALSE;
637 x_return_status := FND_API.G_RET_STS_ERROR;
638
639 IF (g_debug_stmt) THEN
640 PO_DEBUG.debug_stmt
641 ( p_log_head => l_module,
642 p_token => l_progress,
643 p_message => l_progress || ': violated rule 5'
644 );
645 END IF;
646
647 FND_MESSAGE.set_name('PO', 'PO_MERGE_SPO_GA_SITE_DIFF');
648 FND_MESSAGE.set_token('FROM_VENDOR', l_from_vendor_name);
649 FND_MESSAGE.set_token('FROM_VENDOR_SITE', l_from_site_code);
650
651 APP_EXCEPTION.raise_exception;
652
653 WHEN REQ_FOR_VDR_REF_GA_IN_OTHER_OU THEN
654 x_result := FND_API.G_FALSE;
655 x_return_status := FND_API.G_RET_STS_ERROR;
656
657 IF (g_debug_stmt) THEN
658 PO_DEBUG.debug_stmt
659 ( p_log_head => l_module,
660 p_token => l_progress,
661 p_message => l_progress || ': violated rule 6'
662 );
663 END IF;
664
665 FND_MESSAGE.set_name('PO', 'PO_MERGE_REQ_REF_GA_IN_OTR_OU');
666 FND_MESSAGE.set_token('FROM_VENDOR', l_from_vendor_name);
667
668 APP_EXCEPTION.raise_exception;
669
670 WHEN REQ_FOR_SITE_REF_GA_DIFF_SITE THEN
671 x_result := FND_API.G_FALSE;
672 x_return_status := FND_API.G_RET_STS_ERROR;
673
674 IF (g_debug_stmt) THEN
675 PO_DEBUG.debug_stmt
676 ( p_log_head => l_module,
677 p_token => l_progress,
678 p_message => l_progress || ': violated rule 7'
679 );
680 END IF;
681
682 FND_MESSAGE.set_name('PO', 'PO_MERGE_REQ_REF_GA_DIFF_SITE');
683 FND_MESSAGE.set_token('FROM_VENDOR', l_from_vendor_name);
684 FND_MESSAGE.set_token('FROM_VENDOR_SITE', l_from_site_code);
685
686 APP_EXCEPTION.raise_exception;
687
691
688 WHEN GA_FOR_SITE_W_REQREF_DIFF_SITE THEN
689 x_result := FND_API.G_FALSE;
690 x_return_status := FND_API.G_RET_STS_ERROR;
692 IF (g_debug_stmt) THEN
693 PO_DEBUG.debug_stmt
694 ( p_log_head => l_module,
695 p_token => l_progress,
696 p_message => l_progress || ': violated rule 8'
697 );
698 END IF;
699
700 FND_MESSAGE.set_name('PO', 'PO_MERGE_GA_W_REQREF_DIFF_SITE');
701 FND_MESSAGE.set_token('FROM_VENDOR', l_from_vendor_name);
702 FND_MESSAGE.set_token('FROM_VENDOR_SITE', l_from_site_code);
703
704 APP_EXCEPTION.raise_exception;
705
706 WHEN PO_CONSIGNMENT_EXIST THEN
707 x_result := FND_API.G_FALSE;
708 FND_MESSAGE.set_name('PO', x_validation_error);
709
710 APP_EXCEPTION.raise_exception;
711
712 WHEN PO_INVALID_VENDOR_SITE_ID THEN
713
714 -- bug2814492
715 -- set x_result to G_FALSE whenever there is an error
716 x_result := FND_API.G_FALSE;
717 po_message_s.sql_error(l_api_name, l_progress || '-1', sqlcode);
718 x_return_status := FND_API.G_RET_STS_ERROR;
719
720 WHEN FND_API.G_EXC_ERROR THEN
721 -- bug2814492
722 x_result := FND_API.G_FALSE;
723 po_message_s.sql_error(l_api_name, l_progress ||'-2', sqlcode);
724 x_return_status := FND_API.G_RET_STS_ERROR;
725
726 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
727 -- bug2814492
728 x_result := FND_API.G_FALSE;
729 po_message_s.sql_error(l_api_name, l_progress || '-3', sqlcode);
730 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
731
732 WHEN OTHERS THEN
733 -- bug2814492
734 x_result := FND_API.G_FALSE;
735 po_message_s.sql_error(l_api_name, l_progress || '-4', sqlcode);
736 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
737
738 END validate_merge;
739
740 /**==========================================================================
741 *
742 * PUBLIC PROCEDURE : update_org_assignments <GA FPI>
743 *
744 * REQUIRES:
745 * validate_purge must have been successful.
746 * The To vendor_site_id must be defined in po_vendor_sites_all.
747 *
748 * MODIFIES:
749 * API Message List - any messages will be appended to the API Message List
750 *
751 * EFFECTS:
755 * RETURNS:
752 * Updates the Global Agreements' Org Assignment table - replaces every
753 * instance of the old Supplier/SiteName with the new vendor_site_id.
754 *
756 * x_return_status - (a) FND_API.G_RET_STS_SUCCESS if validation successful
757 * (b) FND_API.G_RET_STS_ERROR if error during validation
758 * (c) FND_API.G_RET_STS_UNEXP_ERROR if unexpected error
759 *
760 *===========================================================================
761 */
762 PROCEDURE update_org_assignments
763 (
764 p_api_version IN NUMBER,
765 x_return_status OUT NOCOPY VARCHAR2,
766 p_from_vendor_id IN PO_VENDORS.vendor_id%TYPE,
767 p_from_site_id IN PO_VENDOR_SITES_ALL.vendor_site_id%TYPE,
768 p_to_vendor_id IN PO_VENDORS.vendor_id%TYPE,
769 p_to_site_id IN PO_VENDOR_SITES_ALL.vendor_site_id%TYPE
770 )
771 IS
772 l_api_name CONSTANT VARCHAR2(30) := 'update_org_assignments';
773 l_module FND_LOG_MESSAGES.module%TYPE :=
774 G_MODULE_PREFIX || l_api_name;
775 l_api_version CONSTANT NUMBER := 1.0;
776
777 l_progress VARCHAR2(3);
778
779 BEGIN
780
781 IF (g_debug_stmt) THEN
782 PO_DEBUG.debug_begin
783 ( p_log_head => l_module
784 );
785 END IF;
786
787 x_return_status := FND_API.G_RET_STS_SUCCESS; -- Initialize return status
788 l_progress := '000';
789
790 IF ( NOT FND_API.compatible_api_call(l_api_version,p_api_version,l_api_name,G_PKG_NAME) ) THEN
791 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
792 END IF;
793
794 l_progress := '010';
795
796 -- <VENDOR MERGE FPJ START>
797
798 -- Starting from 11i FPJ we no longer update ga org assignments based on
799 -- vendor site code. Instead we just need to update org assignment with
800 -- vendor site id that matches p_from_vendor_id
801
802 UPDATE po_ga_org_assignments PGOA
803 SET PGOA.vendor_site_id = p_to_site_id,
804 -- Bug 3387904 START - Need to update the WHO columns:
805 PGOA.last_update_date = SYSDATE,
806 PGOA.last_updated_by = FND_GLOBAL.user_id,
807 PGOA.last_update_login = FND_GLOBAL.login_id
808 -- Bug 3387904 END
809 WHERE PGOA.vendor_site_id = p_from_site_id;
810
811 UPDATE po_ga_org_assignments_archive PGOA
815 PGOA.last_updated_by = FND_GLOBAL.user_id,
812 SET PGOA.vendor_site_id = p_to_site_id,
813 -- Bug 3387904 START - Need to update the WHO columns:
814 PGOA.last_update_date = SYSDATE,
816 PGOA.last_update_login = FND_GLOBAL.login_id
817 -- Bug 3387904 END
818 WHERE PGOA.vendor_site_id = p_from_site_id;
819
820 l_progress := '020';
821
822 update_req_line_vdr_info
823 ( p_from_vendor_id => p_from_vendor_id,
824 p_from_site_id => p_from_site_id,
825 p_to_vendor_id => p_to_vendor_id,
826 p_to_site_id => p_to_site_id
827 );
828
829 l_progress := '030';
830
831 update_req_temp_vdr_info
832 ( p_from_vendor_id => p_from_vendor_id,
833 p_from_site_id => p_from_site_id,
834 p_to_vendor_id => p_to_vendor_id,
835 p_to_site_id => p_to_site_id
836 );
837
838 l_progress := '040';
839
840 update_fte_vdr_info
841 ( p_from_vendor_id => p_from_vendor_id,
842 p_from_site_id => p_from_site_id,
843 p_to_vendor_id => p_to_vendor_id,
844 p_to_site_id => p_to_site_id
845 );
846
847 l_progress := '050';
848
849 update_okc_info
850 ( p_from_vendor_id => p_from_vendor_id,
851 p_from_site_id => p_from_site_id,
852 p_to_vendor_id => p_to_vendor_id,
853 p_to_site_id => p_to_site_id
854 );
855
856 l_progress := '060';
857
858 -- <VENDOR MERGE FPJ END>
859
860 -- Call the iSP API to handle events after merge
861
862 POS_SUP_PROF_MRG_GRP.handle_merge (
863 p_to_vendor_id ,
864 p_to_site_id ,
865 p_from_vendor_id ,
866 p_from_site_id ,
867 x_return_status
868 );
869
870 IF (g_debug_stmt) THEN
871 PO_DEBUG.debug_end
872 ( p_log_head => l_module
873 );
874 END IF;
875
876 EXCEPTION
877
878 WHEN PO_INVALID_VENDOR_SITE_ID THEN
879 x_return_status := FND_API.G_RET_STS_ERROR;
880
881 WHEN FND_API.G_EXC_ERROR THEN
882 x_return_status := FND_API.G_RET_STS_ERROR;
883
884 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
885 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
886
887 WHEN OTHERS THEN
888 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
889
890 END update_org_assignments;
891
892
893 /*=========================================================================*/
894 /*=========================== BODY (PRIVATE) ==============================*/
895 /*=========================================================================*/
896
897 -- <VENDOR MERGE FPJ START>
898
899 -----------------------------------------------------------------------
900 --Start of Comments
901 --Name: get_vdr_and_site_name
902 --Pre-reqs: p_vendor_site_id should be a site under vendor identified by
906 -- None
903 -- p_vendor_id
904 --Modifies: None
905 --Locks:
907 --Function:
908 -- Returns vendor_namd and vendor_site_code givent vendor id and
909 -- vendor site id
910 --Parameters:
911 --IN:
912 --p_vendor_id
913 -- Vendor Unique identifier
914 --p_vendor_site_id
915 -- Vendor Site unique identifier
916 --IN OUT:
917 --OUT:
918 --x_vendor_name
919 -- Name of the vendor identified by p_vendor_id
920 --x_vendor_site_code
921 -- Name of the vendor site identified by p_vendor_site_id
922 --Returns:
923 --Notes:
924 --Testing:
925 --End of Comments
926 ------------------------------------------------------------------------
927
928 PROCEDURE get_vdr_and_site_name
929 ( p_vendor_id IN NUMBER,
930 p_vendor_site_id IN NUMBER,
931 x_vendor_name OUT NOCOPY VARCHAR2,
932 x_vendor_site_code OUT NOCOPY VARCHAR2
933 ) IS
934 BEGIN
935
936 SELECT PV.vendor_name,
937 PVS.vendor_site_code
938 INTO x_vendor_name,
939 x_vendor_site_code
940 FROM po_vendors PV,
941 po_vendor_sites_all PVS
942 WHERE PV.vendor_id = p_vendor_id
943 AND PVS.vendor_site_id = p_vendor_site_id
944 AND PV.vendor_id = PVS.vendor_id;
945
946 EXCEPTION
947 WHEN OTHERS THEN
948 FND_MESSAGE.set_name('PO','PO_INVALID_VENDOR_SITE_ID');
949 FND_MESSAGE.set_token('VENDOR_SITE_ID',p_vendor_site_id);
950 APP_EXCEPTION.raise_exception;
951 END get_vdr_and_site_name;
952
953
954 -----------------------------------------------------------------------
955 --Start of Comments
956 --Name: update_req_line_vdr_info
957 --Pre-reqs:
958 --Modifies: po_requisition_lines_all
959 --Locks:
960 -- None
961 --Function:
962 -- Update requisition lines in all OU with the new supplier information
963 -- for those with the supplier/site that has been merged
964 --Parameters:
965 --IN:
966 --p_from_vendor_id
967 -- Vendor that has been merged
968 --p_from_site_id
969 -- Vendor Site that has been merged
970 --p_to_vendor_id
971 -- New Vendor for the old one
972 --p_to_vendor_site_id
973 -- New vendor site for the old one
974 --IN OUT:
975 --OUT:
976 --Returns:
977 --Notes:
978 --Testing:
979 --End of Comments
980 ------------------------------------------------------------------------
981
982 PROCEDURE update_req_line_vdr_info
983 ( p_from_vendor_id IN NUMBER,
984 p_from_site_id IN NUMBER,
985 p_to_vendor_id IN NUMBER,
986 p_to_site_id IN NUMBER
987 ) IS
988 l_api_name CONSTANT VARCHAR2(30) := 'update_req_line_vdr_info';
989 l_module FND_LOG_MESSAGES.module%TYPE :=
990 G_MODULE_PREFIX || l_api_name || '.';
991 l_progress VARCHAR2(3);
992 BEGIN
993
994 IF (g_debug_stmt) THEN
995 PO_DEBUG.debug_begin
996 ( p_log_head => l_module
997 );
998 END IF;
999
1000 l_progress := '000';
1001
1002 -- modify PO_REQUISITION_LINES_ALL to use new vendor in all OUs
1003
1004 --SQL What: Update suggested vendor information to reflect vendor merge
1005 -- on req in all OU
1006 --SQL Why: Vendor Merge functionality
1007
1008 UPDATE po_requisition_lines_all
1009 SET suggested_vendor_name =
1010 (SELECT PV.vendor_name
1011 FROM po_vendors PV
1012 WHERE PV.vendor_id = p_to_vendor_id),
1013 suggested_vendor_location =
1014 (SELECT PVS.vendor_site_code
1015 FROM po_vendor_sites PVS
1016 WHERE PVS.vendor_site_id = p_to_site_id),
1017 vendor_id = p_to_vendor_id,
1018 vendor_site_id = p_to_site_id,
1019 last_update_date = SYSDATE,
1020 last_updated_by = FND_GLOBAL.user_id,
1021 last_update_login = FND_GLOBAL.login_id
1022 WHERE vendor_id = p_from_vendor_id
1023 AND vendor_site_id = p_from_site_id;
1024
1025 IF (g_debug_stmt) THEN
1026 PO_DEBUG.debug_stmt
1027 ( p_log_head => l_module,
1028 p_token => l_progress,
1029 p_message => 'Updated rows: ' || SQL%ROWCOUNT
1030 );
1031 END IF;
1032
1033 l_progress := '010';
1034
1035 --SQL What: Update suggested vendor information to reflect vendor merge
1036 -- for reqs in all OUs, if the req does not have vendor site
1037 -- info, and the supplier becomes inactive due to vendor merge
1038 --SQL Why: Vendor Merge functionality
1039
1040 UPDATE po_requisition_lines_all
1041 SET suggested_vendor_name =
1042 (SELECT PV.vendor_name
1043 FROM po_vendors PV
1044 WHERE PV.vendor_id = p_to_vendor_id),
1045 vendor_id = p_to_vendor_id,
1046 last_update_date = SYSDATE,
1047 last_updated_by = FND_GLOBAL.user_id,
1048 last_update_login = FND_GLOBAL.login_id
1049 WHERE vendor_id = p_from_vendor_id
1050 AND vendor_site_id IS NULL
1051 AND EXISTS
1052 (SELECT vendor_id
1053 FROM po_vendors PV
1054 WHERE vendor_id = p_from_vendor_id
1055 AND NVL(PV.end_date_active, SYSDATE+1) <= SYSDATE);
1056
1057
1058 IF (g_debug_stmt) THEN
1059 PO_DEBUG.debug_stmt
1060 ( p_log_head => l_module,
1061 p_token => l_progress,
1065 PO_DEBUG.debug_end
1062 p_message => 'Updated rows: ' || SQL%ROWCOUNT
1063 );
1064
1066 ( p_log_head => l_module
1067 );
1068 END IF;
1069
1070 EXCEPTION
1071 WHEN OTHERS THEN
1072 IF (g_debug_unexp) THEN
1073 PO_DEBUG.debug_exc
1074 ( p_log_head => l_module,
1075 p_progress => l_progress
1076 );
1077 END IF;
1078
1079 RAISE;
1080
1081 END update_req_line_vdr_info;
1082
1083
1084 -----------------------------------------------------------------------
1085 --Start of Comments
1086 --Name: update_req_temp_vdr_info
1087 --Pre-reqs:
1088 --Modifies: po_reqexpress_lines_all
1089 --Locks:
1090 -- None
1091 --Function:
1092 -- Update requisition template in all OU with the new supplier information
1093 -- for those with the supplier/site that has been merged
1094 --Parameters:
1095 --IN:
1096 --p_from_vendor_id
1097 -- Vendor that has been merged
1098 --p_from_site_id
1099 -- Vendor Site that has been merged
1100 --p_to_vendor_id
1101 -- New Vendor for the old one
1102 --p_to_vendor_site_id
1103 -- New vendor site for the old one
1104 --IN OUT:
1105 --OUT:
1106 --Returns:
1107 --Notes:
1108 --Testing:
1109 --End of Comments
1110 ------------------------------------------------------------------------
1111
1112 PROCEDURE update_req_temp_vdr_info
1113 ( p_from_vendor_id IN NUMBER,
1114 p_from_site_id IN NUMBER,
1115 p_to_vendor_id IN NUMBER,
1116 p_to_site_id IN NUMBER
1117 ) IS
1118 l_api_name CONSTANT VARCHAR2(30) := 'update_req_temp_vdr_info';
1119 l_module FND_LOG_MESSAGES.module%TYPE :=
1120 G_MODULE_PREFIX || l_api_name || '.';
1121 l_progress VARCHAR2(3);
1122 BEGIN
1123
1124 IF (g_debug_stmt) THEN
1125 PO_DEBUG.debug_begin
1126 ( p_log_head => l_module
1127 );
1128 END IF;
1129
1130 l_progress := '000';
1131
1132 -- Update Req Template Records
1133
1134 UPDATE po_reqexpress_lines_all PRL
1135 SET PRL.suggested_vendor_id = p_to_vendor_id,
1136 PRL.suggested_vendor_site_id = p_to_site_id,
1137 PRL.last_update_date = SYSDATE,
1138 PRL.last_updated_by = FND_GLOBAL.user_id,
1139 PRL.last_update_login = FND_GLOBAL.login_id
1140 WHERE PRL.suggested_vendor_id = p_from_vendor_id
1141 AND PRL.suggested_vendor_site_id = p_from_site_id;
1142
1143
1144 IF (g_debug_stmt) THEN
1145 PO_DEBUG.debug_stmt
1146 ( p_log_head => l_module,
1147 p_token => l_progress,
1148 p_message => 'Updated rows: ' || SQL%ROWCOUNT
1149 );
1150 END IF;
1151
1152 l_progress := '010';
1153
1154 --SQL What: update requisition template with the new supplier if supplier
1155 -- site is null in the template, and the supplier is getting
1156 -- invalidatad because of the merge
1157 --SQL Why: If the supplier is not active after vendor merge,the records
1158 -- associated to that supplier should be moved to point to the
1159 -- new supplier
1160
1161 UPDATE po_reqexpress_lines_all PRL
1162 SET PRL.suggested_vendor_id = p_to_vendor_id,
1163 last_update_date = SYSDATE,
1164 last_updated_by = FND_GLOBAL.user_id,
1165 last_update_login = FND_GLOBAL.login_id
1166 WHERE PRL.suggested_vendor_id = p_from_vendor_id
1167 AND PRL.suggested_vendor_site_id IS NULL
1168 AND EXISTS (
1169 SELECT NULL
1170 FROM po_vendors PV
1171 WHERE PV.vendor_id = p_from_vendor_id
1172 AND NVL(PV.end_date_active, SYSDATE + 1) <= SYSDATE);
1173
1174 IF (g_debug_stmt) THEN
1175 PO_DEBUG.debug_stmt
1176 ( p_log_head => l_module,
1177 p_token => l_progress,
1178 p_message => 'Updated rows: ' || SQL%ROWCOUNT
1179 );
1180
1181 PO_DEBUG.debug_end
1182 ( p_log_head => l_module
1183 );
1184 END IF;
1185
1186 EXCEPTION
1187 WHEN OTHERS THEN
1188 IF (g_debug_unexp) THEN
1189 PO_DEBUG.debug_exc
1190 ( p_log_head => l_module,
1191 p_progress => l_progress
1192 );
1193 END IF;
1194
1195 RAISE;
1196 END update_req_temp_vdr_info;
1197
1198
1199
1200 -----------------------------------------------------------------------
1201 --Start of Comments
1202 --Name: update_fte_vdr_info
1203 --Pre-reqs:
1204 --Modifies:
1205 --Locks:
1206 -- None
1207 --Function:
1208 -- Call FTE API to notify them about the occurrent of vendor merge
1209 --Parameters:
1210 --IN:
1211 --p_from_vendor_id
1212 -- Vendor that has been merged
1213 --p_from_site_id
1214 -- Vendor Site that has been merged
1215 --p_to_vendor_id
1216 -- New Vendor for the old one
1217 --p_to_vendor_site_id
1218 -- New vendor site for the old one
1219 --IN OUT:
1220 --OUT:
1221 --Returns:
1222 --Notes:
1223 --Testing:
1224 --End of Comments
1225 ------------------------------------------------------------------------
1226
1227 PROCEDURE update_fte_vdr_info
1228 ( p_from_vendor_id IN NUMBER,
1229 p_from_site_id IN NUMBER,
1230 p_to_vendor_id IN NUMBER,
1231 p_to_site_id IN NUMBER
1232 ) IS
1233
1234 l_api_name CONSTANT VARCHAR2(30) := 'update_fte_vdr_info';
1238
1235 l_module FND_LOG_MESSAGES.module%TYPE :=
1236 G_MODULE_PREFIX || l_api_name || '.';
1237 l_progress VARCHAR2(3);
1239 l_fte_in_rec WSH_PO_INTG_TYPES_GRP.merge_in_rectype;
1240 l_fte_out_rec WSH_PO_INTG_TYPES_GRP.merge_out_rectype;
1241 l_msg_count NUMBER;
1242 l_msg_data VARCHAR2(2000);
1243 l_return_status VARCHAR2(1);
1244
1245 BEGIN
1246
1247 IF (g_debug_stmt) THEN
1248 PO_DEBUG.debug_begin
1249 ( p_log_head => l_module
1250 );
1251 END IF;
1252
1253 l_progress := '000';
1254
1255 -- Construct in record for FTE call out
1256
1257 l_fte_in_rec.caller := 'PO_VENDOR_MERGE';
1258 l_fte_in_rec.p_from_vendor_id := p_from_vendor_id;
1259 l_fte_in_rec.p_from_site_id := p_from_site_id;
1260 l_fte_in_rec.p_to_vendor_id := p_to_vendor_id;
1261 l_fte_in_rec.p_to_site_id := p_to_site_id;
1262
1263 l_progress := '010';
1264
1265 WSH_PO_INTEGRATION_GRP.vendor_merge
1266 ( p_api_version_number => 1.0,
1267 p_init_msg_list => FND_API.G_TRUE,
1268 p_commit => FND_API.G_FALSE,
1269 p_in_rec => l_fte_in_rec,
1270 x_out_rec => l_fte_out_rec,
1271 x_return_status => l_return_status,
1272 x_msg_count => l_msg_count,
1273 x_msg_data => l_msg_data
1274 );
1275
1276 l_progress := '020';
1277
1278
1279 IF (g_debug_stmt) THEN
1280 PO_DEBUG.debug_stmt
1281 ( p_log_head => l_module,
1282 p_token => l_progress,
1283 p_message => 'Called WSH Merge API. status = ' || l_return_status
1284 );
1285 END IF;
1286
1287 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1288 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1289 END IF;
1290
1291 l_progress := '030';
1292
1293 IF (g_debug_stmt) THEN
1294 PO_DEBUG.debug_end
1295 ( p_log_head => l_module
1296 );
1297 END IF;
1298
1299 EXCEPTION
1300 WHEN OTHERS THEN
1301
1302 IF (g_debug_unexp) THEN
1303
1304 IF (l_msg_count IS NOT NULL) THEN
1305 FOR i IN 1..l_msg_count LOOP
1306
1307 l_msg_data := FND_MSG_PUB.get
1308 ( p_msg_index => i,
1309 p_encoded => 'F');
1310 PO_DEBUG.debug_stmt
1311 ( p_log_head => l_module,
1312 p_token => l_progress,
1313 p_message => l_msg_data
1314 );
1315 END LOOP;
1316 END IF;
1317
1318 PO_DEBUG.debug_exc
1319 ( p_log_head => l_module,
1320 p_progress => l_progress
1321 );
1322 END IF;
1323
1324 RAISE;
1325 END update_fte_vdr_info;
1326
1327 -----------------------------------------------------------------------
1328 --Start of Comments
1329 --Name: update_okc_info
1330 --Pre-reqs:
1331 --Modifies:
1332 --Locks:
1333 -- None
1334 --Function:
1335 -- Call OKC API to notify them about the occurrence of vendor merge
1336 --Parameters:
1337 --IN:
1338 --p_from_vendor_id
1339 -- Vendor that has been merged
1340 --p_from_site_id
1341 -- Vendor Site that has been merged
1342 --p_to_vendor_id
1343 -- New Vendor for the old one
1344 --p_to_vendor_site_id
1345 -- New vendor site for the old one
1346 --IN OUT:
1347 --OUT:
1348 --Returns:
1349 --Notes:
1350 --Testing:
1351 --End of Comments
1352 ------------------------------------------------------------------------
1353
1354 PROCEDURE update_okc_info
1355 ( p_from_vendor_id IN NUMBER,
1356 p_from_site_id IN NUMBER,
1357 p_to_vendor_id IN NUMBER,
1358 p_to_site_id IN NUMBER
1359 ) IS
1360
1361 l_api_name CONSTANT VARCHAR2(30) := 'update_okc_info';
1362 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1363 G_MODULE_PREFIX || l_api_name || '.';
1364
1365 l_progress VARCHAR2(3);
1366
1367 l_msg_data VARCHAR2(2000);
1368 l_msg_count NUMBER;
1369 l_return_status VARCHAR2(1);
1370
1371 BEGIN
1372
1373 IF (g_debug_stmt) THEN
1374 PO_DEBUG.debug_begin
1375 ( p_log_head => l_module
1376 );
1377 END IF;
1378
1379 l_progress := '000';
1380
1381 IF (PO_CONTERMS_UTL_GRP.is_contracts_enabled = FND_API.G_TRUE) THEN
1382
1383 OKC_MANAGE_DELIVERABLES_GRP.updateExtPartyOnDeliverables
1384 ( p_api_version => 1.0,
1385 p_init_msg_list => FND_API.G_TRUE,
1386 p_commit => FND_API.G_FALSE,
1387 p_document_class => 'PO',
1388 p_from_external_party_id => p_from_vendor_id,
1389 p_from_external_party_site_id => p_from_site_id,
1390 p_to_external_party_id => p_to_vendor_id,
1391 p_to_external_party_site_id => p_to_site_id,
1392 x_msg_data => l_msg_data,
1393 x_msg_count => l_msg_count,
1394 x_return_status => l_return_status
1395 );
1396
1397 END IF;
1398
1399 l_progress := '010';
1400
1401
1402 IF (g_debug_stmt) THEN
1403 PO_DEBUG.debug_stmt
1404 ( p_log_head => l_module,
1405 p_token => l_progress,
1409
1406 p_message => 'Called OKC Merge API. status = ' || l_return_status
1407 );
1408 END IF;
1410 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1411 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1412 END IF;
1413
1414 IF (g_debug_stmt) THEN
1415 PO_DEBUG.debug_end
1416 ( p_log_head => l_module
1417 );
1418 END IF;
1419
1420 EXCEPTION
1421 WHEN OTHERS THEN
1422
1423 IF (g_debug_unexp) THEN
1424
1425 IF (l_msg_count IS NOT NULL) THEN
1426 FOR i IN 1..l_msg_count LOOP
1427
1428 l_msg_data := FND_MSG_PUB.get
1429 ( p_msg_index => i,
1430 p_encoded => 'F');
1431 PO_DEBUG.debug_stmt
1432 ( p_log_head => l_module,
1433 p_token => l_progress,
1434 p_message => l_msg_data
1435 );
1436 END LOOP;
1437 END IF;
1438
1439 PO_DEBUG.debug_exc
1440 ( p_log_head => l_module,
1441 p_progress => l_progress
1442 );
1443 END IF;
1444
1445 RAISE;
1446
1447 END update_okc_info;
1448
1449 -- <VENDOR MERGE FPJ END>
1450
1451 END PO_AP_MERGE_GRP;