1 PACKAGE BODY PO_VendorMerge_GRP AS
2 /* $Header: PO_VendorMerge_GRP.plb 120.7.12020000.2 2013/02/10 20:56:35 vegajula ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30):='PO_VendorMerge_GRP';
5
6 -- Start of comments
7 -- API name : Merge_Vendor
8 -- Type : Group.
9 -- Function :
10 -- Pre-reqs : None.
11 -- Parameters :
12 -- IN : p_api_version IN NUMBER Required
13 -- p_init_msg_list IN VARCHAR2 Optional
14 -- Default = FND_API.G_FALSE
15 -- p_commit IN VARCHAR2 Optional
16 -- Default = FND_API.G_FALSE
17 -- p_validation_level IN NUMBER Optional
18 -- Default = FND_API.G_VALID_LEVEL_FULL
19 -- parameter1
20 -- parameter2
21 -- .
22 -- .
23 -- OUT : x_return_status OUT VARCHAR2(1)
24 -- x_msg_count OUT NUMBER
25 -- x_msg_data OUT VARCHAR2(2000)
26 -- parameter1
27 -- parameter2
28 -- End of comments
29
30 Procedure Merge_Vendor(
31 p_api_version IN NUMBER,
32 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
33 p_commit IN VARCHAR2 default FND_API.G_FALSE,
34 p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL,
35 x_return_status OUT NOCOPY VARCHAR2,
36 x_msg_count OUT NOCOPY NUMBER,
37 x_msg_data OUT NOCOPY VARCHAR2,
38 p_vendor_id IN NUMBER,
39 p_vendor_site_id IN NUMBER,
40 p_dup_vendor_id IN NUMBER,
41 p_dup_vendor_site_id IN NUMBER,
42 p_party_id IN NUMBER default NULL,
43 p_dup_party_id IN NUMBER default NULL,
44 p_party_site_id IN NUMBER default NULL,
45 p_dup_party_site_id IN NUMBER default NULL
46 )
47
48 IS
49
50 cursor merge_autosrc_docs is
51 select distinct pad.autosource_rule_id,
52 pad.sequence_num,
53 pad.document_line_id
54 from po_autosource_documents pad
55 where pad.vendor_id = p_dup_vendor_id;
56
57 l_api_name CONSTANT VARCHAR2(30) := 'Merge_Vendor';
58 l_api_version CONSTANT NUMBER := 1.0;
59 l_row_count NUMBER;
60 l_max_seq_num number;
61 l_new_seq_num number;
62 l_seq_num number;
63 l_rule_id number;
64 l_doc_line_id number;
65 l_last_updated_by number;
66
67 d_progress NUMBER;
68 d_module VARCHAR2(60) := 'po.plsql.PO_VendorMerge_GRP.Merge_Vendor';
69
70 BEGIN
71
72 d_progress := 0;
73 IF (PO_LOG.d_proc) THEN
74 PO_LOG.proc_begin(d_module);
75 PO_LOG.proc_begin(d_module, 'p_vendor_id', p_vendor_id);
76 PO_LOG.proc_begin(d_module, 'p_vendor_site_id', p_vendor_site_id);
77 PO_LOG.proc_begin(d_module, 'p_dup_vendor_id', p_dup_vendor_id);
78 PO_LOG.proc_begin(d_module, 'p_dup_vendor_site_id', p_dup_vendor_site_id);
79 PO_LOG.proc_begin(d_module, 'p_party_id', p_party_id);
80 PO_LOG.proc_begin(d_module, 'p_dup_party_id', p_dup_party_id);
81 PO_LOG.proc_begin(d_module, 'p_party_site_id', p_party_site_id);
82 PO_LOG.proc_begin(d_module, 'p_dup_party_site_id', p_dup_party_site_id);
83 END IF;
84
85 -- Initialize API return status to success
86 x_return_status := FND_API.G_RET_STS_SUCCESS;
87
88 l_last_updated_by := FND_GLOBAL.user_id;
89 -- Check for call compatibility.
90 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
91 p_api_version ,
92 l_api_name ,
93 G_PKG_NAME )
94 THEN
95 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
96 END IF;
97
98 -- Initialize API message list if necessary.
99 -- Initialize message list if p_init_msg_list is set to TRUE.
100 IF FND_API.to_Boolean( p_init_msg_list) THEN
101 FND_MSG_PUB.initialize;
102 END IF;
103
104 d_progress := 10;
105 --<Conc Mod Project- Supplier Merge Impact>
106 --Since in CLM we have Multi-Mod for Supplier Change, Supplier Merge is not required.
107 --therefore ignore CLM documents
108 --update Commercial Modifications
109 UPDATE po_headers_draft_all pohd
110 SET pohd.vendor_id = p_vendor_id,
111 pohd.vendor_site_id = p_vendor_site_id,
112 pohd.vendor_contact_id = (SELECT vendor_contact_id
113 FROM ap_supplier_contacts
114 WHERE org_party_site_id = (SELECT party_site_id
115 FROM ap_supplier_sites_all
116 WHERE vendor_site_id = p_vendor_site_id)
117 AND per_party_id = (SELECT per_party_id
118 FROM ap_supplier_contacts
119 WHERE vendor_contact_id = pohd.vendor_contact_id)),
120 pohd.last_updated_by = l_last_updated_by,
121 pohd.last_update_date = sysdate
122 WHERE pohd.vendor_id = p_dup_vendor_id
123 AND pohd.vendor_site_id = p_dup_vendor_site_id
124 AND NOT EXISTS (SELECT 'Exclude CLM Document'
125 FROM po_doc_style_headers pods
126 WHERE pods.style_id = pohd.style_id
127 AND NVL(pods.CLM_FLAG,'N') = 'Y')
128 AND EXISTS ( SELECT 'Exclude draft_type is not equal to MOD'
129 FROM po_drafts dft
130 WHERE dft.document_id=pohd.po_header_id
131 AND dft.draft_id= pohd.draft_id
132 AND dft.draft_type='MOD');
133
134
135 UPDATE PO_GA_ORG_ASSIGN_DRAFT PGOA
136 SET PGOA.vendor_site_id = p_vendor_site_id,
137 PGOA.last_update_date = SYSDATE,
138 PGOA.last_updated_by = l_last_updated_by,
139 PGOA.last_update_login = FND_GLOBAL.login_id
140 WHERE PGOA.vendor_site_id = p_dup_vendor_site_id
141 AND NOT EXISTS (SELECT 'Exclude CLM Document'
142 FROM po_doc_style_headers pods,
143 po_headers ph
144 WHERE pods.style_id = ph.style_id
145 AND ph.PO_HEADER_ID= pgoa.PO_HEADER_ID
146 AND NVL(pods.CLM_FLAG,'N') = 'Y')
147 AND EXISTS ( SELECT 'Exclude draft_type is not equal to MOD'
148 FROM po_drafts dft
149 WHERE dft.document_id=pgoa.po_header_id
150 AND dft.draft_id= pgoa.draft_id
151 AND dft.draft_type='MOD');
152 --<Conc Mods Project - End>
153
154 --Bug 5435716 START
155 --Bug 12728157
156 UPDATE po_headers ph
157 SET ph.vendor_id = p_vendor_id,
158 ph.vendor_site_id = p_vendor_site_id,
159 ph.vendor_contact_id = (SELECT vendor_contact_id
160 FROM ap_supplier_contacts
161 WHERE org_party_site_id = (SELECT party_site_id
162 FROM ap_supplier_sites_all
163 WHERE vendor_site_id = p_vendor_site_id)
164 AND per_party_id = (SELECT per_party_id
165 FROM ap_supplier_contacts
166 WHERE vendor_contact_id = ph.vendor_contact_id)),
167 last_updated_by = l_last_updated_by,
168 last_update_date = SYSDATE
169 WHERE ph.vendor_id = p_dup_vendor_id
170 AND ph.vendor_site_id = p_dup_vendor_site_id
171 AND NOT EXISTS (SELECT 'CLM Document' --<Conc Mod Project>
172 FROM po_doc_style_headers pods
173 WHERE pods.style_id = ph.style_id
174 AND NVL(pods.CLM_FLAG,'N') = 'Y');
175
176
177 UPDATE po_rfq_vendors
178 SET vendor_id = p_vendor_id,
179 vendor_site_id = p_vendor_site_id,
180 last_updated_by = l_last_updated_by,
181 last_update_date = sysdate
182 WHERE vendor_id = p_dup_vendor_id
183 AND vendor_site_id = p_dup_vendor_site_id ;
184
185 DELETE from po_rfq_vendors prv
186 WHERE vendor_id = p_dup_vendor_id
187 AND vendor_site_id = p_dup_vendor_site_id;
188
189
190 --Bug 5435716 END
191
192 -- modify PO_HEADERS_ARCHIVE
193 UPDATE po_headers_archive pha
194 SET pha.vendor_id = p_vendor_id,
195 pha.vendor_site_id = p_vendor_site_id
196 WHERE pha.vendor_id = p_dup_vendor_id
197 AND pha.vendor_site_id = p_dup_vendor_site_id
198 AND NOT EXISTS (SELECT 'CLM Document' --<Conc Mod Project>
199 FROM po_doc_style_headers pods
200 WHERE pods.style_id = pha.style_id
201 AND NVL(pods.CLM_FLAG,'N') = 'Y');
202
203 IF (PO_LOG.d_stmt) THEN
204 PO_LOG.stmt(d_module, d_progress, 'updated PO_HEADERS_ARCHIVE');
205 END IF;
206
207
208 -- modify PO_VENDOR_LIST_ENTRIES
209 -- bug3537645 added vendor_site_id condition
210 UPDATE po_vendor_list_entries pv1
211 SET pv1.vendor_id = p_vendor_id,
212 pv1.vendor_site_id = p_vendor_site_id
213 WHERE pv1.vendor_id = p_dup_vendor_id
214 AND pv1.vendor_site_id = p_dup_vendor_site_id
215 AND not exists
216 (select vendor_id
217 from po_vendor_list_entries pv2
218 where pv2.vendor_id = p_vendor_id
219 and pv2.vendor_site_id = p_vendor_site_id
220 and pv2.vendor_list_header_id =
221 pv1.vendor_list_header_id);
222
223 -- delete the vendor_list_entry if the new vendor_id would make the
224 -- record a duplicate ie. if modify_po6 had failed
225 -- Anything not moved to the new vendor would have been a duplicate
226 -- and should be deleted
227 -- DELETE from po_vendor_list_entries pvl
228 -- WHERE vendor_id = p_dup_vendor_id
229 -- AND vendor_site_id = p_vendor_site_id;
230 -- Fix for 2086548 commented the above do_sql and wrote the below one
231 DELETE from po_vendor_list_entries pvl
232 WHERE vendor_id = p_dup_vendor_id
233 AND vendor_site_id = p_dup_vendor_site_id ;
234
235
236 IF (PO_LOG.d_stmt) THEN
237 PO_LOG.stmt(d_module, d_progress, 'updated PO_VENDOR_LIST_ENTRIES');
238 END IF;
239
240 -- modify PO_AUTOSOURCE_VENDORS
241 UPDATE po_autosource_vendors pav1
242 SET pav1.vendor_id = p_vendor_id
243 WHERE pav1.vendor_id = p_dup_vendor_id
244 AND not exists
245 (select vendor_id
246 from po_autosource_vendors pav2
247 where pav2.vendor_id = p_vendor_id
248 and pav2.autosource_rule_id =
249 pav1.autosource_rule_id) ;
250
251
252 -- modify split
253 UPDATE po_autosource_vendors pav1
254 SET pav1.split = (SELECT sum (pav3.split)
255 FROM po_autosource_vendors pav3
256 WHERE pav3.autosource_rule_id =
257 pav1.autosource_rule_id
258 AND pav3.vendor_id IN
259 (p_vendor_id, p_dup_vendor_id))
260 WHERE pav1.vendor_id = p_vendor_id
261 AND exists
262 (select pav2.vendor_id
263 from po_autosource_vendors pav2
264 where pav2.vendor_id = p_dup_vendor_id
265 and pav2.autosource_rule_id =
266 pav1.autosource_rule_id) ;
267
268
269
270 -- delete the autosource entry if the new vendor_id would make the
271 -- record a duplicate ie. if modify_po6 had failed
272 -- Anything not moved to the new vendor would have been a duplicate
273 -- and should be deleted
274
275
276 IF (p_dup_vendor_id <> p_vendor_id) THEN
277
278 DELETE from po_autosource_vendors pavl
279 WHERE vendor_id = p_dup_vendor_id ;
280
281 END IF;
282
283 IF (PO_LOG.d_stmt) THEN
284 PO_LOG.stmt(d_module, d_progress, 'updated PO_AUTOSOURCE_VENDORS');
285 END IF;
286
287 -- modify po_autosource_documents
288 --
289 open merge_autosrc_docs;
290
291 loop
292 l_max_seq_num := 0;
293
294 fetch merge_autosrc_docs into
295 l_rule_id, l_seq_num, l_doc_line_id;
296 exit when merge_autosrc_docs%notfound;
297
298 select nvl(max(sequence_num),0)
299 into l_max_seq_num
300 from po_autosource_documents
301 where autosource_rule_id = l_rule_id
302 and vendor_id = p_vendor_id;
303
304 l_new_seq_num := l_max_seq_num + 1;
305
306 update po_autosource_documents
307 set vendor_id = p_vendor_id,
308 sequence_num = l_new_seq_num
309 where autosource_rule_id = l_rule_id
310 and vendor_id = p_dup_vendor_id
311 and sequence_num = l_seq_num
312 and not exists
313 (select 'already have PAD for this rule, vendor, doc line'
314 from po_autosource_documents
315 where autosource_rule_id = l_rule_id
316 and vendor_id = p_vendor_id
317 and document_line_id = l_doc_line_id);
318
319 end loop;
320
321 close merge_autosrc_docs;
322
323 -- delete the document entry if the new vendor_id would make the
324 -- record a duplicate
325
326
327 IF (p_dup_vendor_id <> p_vendor_id) Then
328
329 delete from po_autosource_documents
330 where vendor_id = p_dup_vendor_id;
331
332 End If;
333
334 IF (PO_LOG.d_stmt) THEN
335 PO_LOG.stmt(d_module, d_progress, 'updated PO_AUTOSOURCE_DOCUMENTS');
336 END IF;
337
338 -- Starting from 11i FPJ we no longer update ga org assignments based on
339 -- vendor site code. Instead we just need to update org assignment with
340 -- vendor site id that matches p_from_vendor_id
341
342 UPDATE po_ga_org_assignments PGOA
343 SET PGOA.vendor_site_id = p_vendor_site_id,
344 PGOA.last_update_date = SYSDATE,
345 PGOA.last_updated_by = l_last_updated_by,
346 PGOA.last_update_login = FND_GLOBAL.login_id
347 WHERE PGOA.vendor_site_id = p_dup_vendor_site_id
348 AND NOT EXISTS (SELECT 'CLM Document' --<Conc Mod Project>
349 FROM po_doc_style_headers pods,
350 po_headers ph
351 WHERE pods.style_id = ph.style_id
352 AND ph.PO_HEADER_ID= pgoa.PO_HEADER_ID
353 AND NVL(pods.CLM_FLAG,'N') = 'Y');
354
355 UPDATE po_ga_org_assignments_archive PGOA
356 SET PGOA.vendor_site_id = p_vendor_site_id,
357 PGOA.last_update_date = SYSDATE,
358 PGOA.last_updated_by = l_last_updated_by,
359 PGOA.last_update_login = FND_GLOBAL.login_id
360 WHERE PGOA.vendor_site_id = p_dup_vendor_site_id
361 AND NOT EXISTS (SELECT 'CLM Document' --<Conc Mod Project>
362 FROM po_doc_style_headers pods,
363 po_headers ph
364 WHERE pods.style_id = ph.style_id
365 AND ph.PO_HEADER_ID= pgoa.PO_HEADER_ID
366 AND NVL(pods.CLM_FLAG,'N') = 'Y');
367 IF (PO_LOG.d_stmt) THEN
368 PO_LOG.stmt(d_module, d_progress, 'updated PO_GA_ORG_ASSIGNMENTS');
369 END IF;
370
371 -- modify PO_REQUISITION_LINES
372 UPDATE PO_REQUISITION_LINES prl
373 SET prl.suggested_vendor_name = (select pov1.vendor_name
374 from po_vendors pov1
375 where pov1.vendor_id =
376 p_vendor_id),
377 prl.suggested_vendor_location = (select pvs1.vendor_site_code
378 from po_vendor_sites pvs1
379 where pvs1.vendor_site_id =
380 p_vendor_site_id)
381 WHERE prl.suggested_vendor_name in (select pov2.vendor_name
382 from po_vendors pov2
383 where pov2.vendor_id =
384 p_dup_vendor_id)
385 AND prl.suggested_vendor_location in (select pvs2.vendor_site_code
386 from po_vendor_sites pvs2
387 where vendor_site_id =
388 p_dup_vendor_site_id)
389 AND NOT EXISTS (SELECT 'CLM Document'
390 FROM Po_Requisition_Headers_All prha
391 WHERE prha.Requisition_Header_Id = prl.Requisition_Header_Id
392 AND NVL(PRHA.FEDERAL_FLAG,'N')='Y');
393
394
395 UPDATE po_requisition_lines prl
396 SET prl.vendor_id = p_vendor_id,
397 prl.vendor_site_id = p_vendor_site_id,
398 prl.last_update_date = sysdate,
399 prl.last_updated_by = l_last_updated_by
400 WHERE prl.vendor_id = p_dup_vendor_id
401 AND prl.vendor_site_id = p_dup_vendor_site_id
402 AND NOT EXISTS (SELECT 'CLM Document'
403 FROM Po_Requisition_Headers_All prha
404 WHERE prha.Requisition_Header_Id = prl.Requisition_Header_Id
405 AND NVL(PRHA.FEDERAL_FLAG,'N')='Y');
406
407
408 UPDATE po_requisition_lines prl
409 SET prl.vendor_id = p_vendor_id,
410 prl.last_update_date = sysdate,
411 prl.last_updated_by = l_last_updated_by
412 WHERE prl.vendor_id = p_dup_vendor_id
413 AND prl.vendor_site_id is null
414 AND exists
415 ( select vendor_id
416 from po_vendors
417 where vendor_id = p_dup_vendor_id
418 and nvl(end_date_active, sysdate+1) <= sysdate)
419 AND NOT EXISTS (SELECT 'Exclude CLM Document'
420 FROM Po_Requisition_Headers_All prha
421 WHERE prha.Requisition_Header_Id = prl.Requisition_Header_Id
422 AND NVL(PRHA.FEDERAL_FLAG,'N')='Y');
423
424 IF (PO_LOG.d_stmt) THEN
425 PO_LOG.stmt(d_module, d_progress, 'updated PO_REQUISITION_LINES');
426 END IF;
427
428
429 -- Update Req Template Records
430 UPDATE po_reqexpress_lines_all PRL
431 SET PRL.suggested_vendor_id = p_vendor_id,
432 PRL.suggested_vendor_site_id = p_vendor_site_id,
433 PRL.last_update_date = SYSDATE,
434 PRL.last_updated_by = l_last_updated_by
435 WHERE PRL.suggested_vendor_id = p_dup_vendor_id
436 AND PRL.suggested_vendor_site_id = p_dup_vendor_site_id;
437
438
439 --SQL What: update requisition template with the new supplier if supplier
440 -- site is null in the template, and the supplier is getting
441 -- invalidatad because of the merge
442 --SQL Why: If the supplier is not active after vendor merge,the records
443 -- associated to that supplier should be moved to point to the
444 -- new supplier
445
446 UPDATE po_reqexpress_lines_all PRL
447 SET PRL.suggested_vendor_id = p_vendor_id,
448 last_update_date = SYSDATE,
449 last_updated_by = l_last_updated_by
450 WHERE PRL.suggested_vendor_id = p_dup_vendor_id
451 AND PRL.suggested_vendor_site_id IS NULL
452 AND EXISTS (
453 SELECT NULL
454 FROM po_vendors PV
455 WHERE PV.vendor_id = p_dup_vendor_id
456 AND NVL(PV.end_date_active, SYSDATE + 1) <= SYSDATE);
457
458
459 IF (PO_LOG.d_stmt) THEN
460 PO_LOG.stmt(d_module, d_progress, 'updated PO_REQEXPRESS_LINES_ALL');
461 END IF;
462
463 -- modify po_approved_supplier_list
464 UPDATE po_approved_supplier_list poasl1
465 SET poasl1.vendor_id = p_vendor_id,
466 poasl1.vendor_site_id = p_vendor_site_id
467 WHERE poasl1.vendor_id = p_dup_vendor_id
468 AND poasl1.vendor_site_id = p_dup_vendor_site_id
469 AND not exists
470 ( select vendor_id
471 from po_approved_supplier_list poasl2
472 where poasl2.vendor_id = p_vendor_id
473 and poasl2.vendor_site_id = p_vendor_site_id
474 and nvl(poasl2.item_id, -99) =
475 nvl(poasl1.item_id, -99)
476 and nvl(poasl2.category_id, -99) =
477 nvl(poasl1.category_id, -99)
478 and poasl2.using_organization_id =
479 poasl1.using_organization_id) ;
480 --1755383 Added the nvl condition so that null values does not result
481 --in success
482
483 -- delete the approved_list_entry if the new vendor_id would make the
484 -- record a duplicate.
485 -- Bug: 1494378
486 DELETE from po_approved_supplier_list poasl
487 WHERE vendor_id = p_dup_vendor_id
488 AND vendor_site_id = p_dup_vendor_site_id ;
489
490 --Bug 1755383 start
491 -- modify po_approved_supplier_list
492
493 UPDATE po_approved_supplier_list poasl1
494 SET poasl1.vendor_id = p_vendor_id
495 WHERE poasl1.vendor_id = p_dup_vendor_id
496 AND poasl1.vendor_site_id is null
497 AND exists
498 ( select vendor_id
499 from po_vendors
500 where vendor_id = p_dup_vendor_id
501 and nvl(end_date_active, sysdate+1) <= sysdate)
502 AND not exists
503 ( select vendor_id
504 from po_approved_supplier_list poasl2
505 where poasl2.vendor_id = p_vendor_id
506 and poasl2.vendor_site_id is null
507 and nvl(poasl2.item_id, -99) =
508 nvl(poasl1.item_id, -99)
509 and nvl(poasl2.category_id, -99) =
510 nvl(poasl1.category_id, -99)
511 and poasl2.using_organization_id =
512 poasl1.using_organization_id);
513
514 -- delete the approved_list_entry if the new vendor_id would make the
515 -- record a duplicate.
516
517 DELETE from po_approved_supplier_list poasl
518 WHERE vendor_id = p_dup_vendor_id
519 AND vendor_site_id is null
520 AND exists
521 ( select vendor_id
522 from po_vendors
523 where vendor_id = p_dup_vendor_id
524 and nvl(end_date_active, sysdate+1) <= sysdate);
525
526 IF (PO_LOG.d_stmt) THEN
527 PO_LOG.stmt(d_module, d_progress, 'updated PO_APPROVED_SUPPLIER_LIST');
528 END IF;
529
530 -- modify po_asl_attributes
531 UPDATE po_asl_attributes poasl1
532 SET poasl1.vendor_id = p_vendor_id,
533 poasl1.vendor_site_id = p_vendor_site_id
534 WHERE poasl1.vendor_id = p_dup_vendor_id
535 AND poasl1.vendor_site_id = p_dup_vendor_site_id
536 AND not exists
537 (select vendor_id
538 from po_asl_attributes poasl2
539 where poasl2.vendor_id = p_vendor_id
540 and poasl2.vendor_site_id = p_vendor_site_id
541 and nvl(poasl2.item_id, -99) = nvl(poasl1.item_id, -99)
542 and nvl(poasl2.category_id, -99) = nvl(poasl1.category_id, -99)
543 and poasl2.using_organization_id =
544 poasl1.using_organization_id);
545 --1755383 Added the nvl condition so that null values does not result
546 --in success
547 -- delete the approved_list_entry if the new vendor_id would make the
548 -- record a duplicate.
549 -- DELETE from po_asl_attributes poasl
550 -- WHERE vendor_id = p_dup_vendor_id
551 -- AND vendor_site_id = p_vendor_site_id ;
552 -- FIX FOR 1931927 commented the above do_sql and wrote the below one
553 DELETE from po_asl_attributes poasl
554 WHERE vendor_id = p_dup_vendor_id
555 AND vendor_site_id = p_dup_vendor_site_id ;
556
557
558 --Bug 1755383 start
559
560 UPDATE po_asl_attributes poasl1
561 SET poasl1.vendor_id = p_vendor_id
562 WHERE poasl1.vendor_id = p_dup_vendor_id
563 AND poasl1.vendor_site_id is null
564 AND exists
565 ( select vendor_id
566 from po_vendors
567 where vendor_id = p_dup_vendor_id
568 and nvl(end_date_active, sysdate+1) <= sysdate)
569 AND not exists
570 (select vendor_id
571 from po_asl_attributes poasl2
572 where poasl2.vendor_id = p_vendor_id
573 and poasl2.vendor_site_id is null
574 and nvl(poasl2.item_id, -99) = nvl(poasl1.item_id, -99)
575 and nvl(poasl2.category_id, -99) = nvl(poasl1.category_id, -99)
576 and poasl2.using_organization_id =
577 poasl1.using_organization_id);
578
579 -- delete the approved_list_entry if the new vendor_id would make the
580 -- record a duplicate.
581
582 DELETE from po_asl_attributes poasl
583 WHERE vendor_id = p_dup_vendor_id
584 AND vendor_site_id is null
585 AND exists
586 ( select vendor_id
587 from po_vendors
588 where vendor_id = p_dup_vendor_id
589 and nvl(end_date_active, sysdate+1) <= sysdate) ;
590
591 IF (PO_LOG.d_stmt) THEN
592 PO_LOG.stmt(d_module, d_progress, 'updated PO_ASL_ATTRIBUTES');
593 END IF;
594
595
596 -- Prepare message name
597 FND_MESSAGE.SET_NAME('PO','PO_ASL_ATTRIBUTES');
598 IF SQL%FOUND THEN
599 x_return_status := FND_API.G_RET_STS_SUCCESS;
600 l_row_count := SQL%ROWCOUNT;
601 ELSE
602 x_return_status := FND_API.G_RET_STS_ERROR;
603 l_row_count := 0;
604 END IF;
605 FND_MESSAGE.SET_TOKEN('ROWS_DELETED',l_row_count);
606 -- Add message to API message list.
607 FND_MSG_PUB.Add;
608
609
610 -- Get message count and if 1, return message data.
611 FND_MSG_PUB.Count_And_Get
612 ( p_count => x_msg_count,
613 p_data => x_msg_data
614 );
615
616
617
618 -- Call the iSP Vendor Merge API
619 -- Commenting out POS calll as it would be called from AP directly
620 -- POS_SUP_PROF_MRG_GRP.handle_merge (
621 -- p_new_vendor_id => p_vendor_id,
622 -- p_new_vendor_site_id => p_vendor_site_id,
623 -- p_old_vendor_id => p_dup_vendor_id,
624 -- p_old_vendor_site_id => p_dup_vendor_site_id ,
625 -- x_return_status => x_return_status
626 -- );
627
628 -- Call the iP Vendor Merge API
629
630 ICX_CAT_POPULATE_ITEM_GRP.populateVendorMerge(
631 p_api_version => 1.0,
632 p_to_vendor_id => p_vendor_id,
633 p_to_site_id => p_vendor_site_id,
634 p_from_vendor_id => p_dup_vendor_id,
635 p_from_site_id => p_dup_vendor_site_id,
636 x_return_status => x_return_status
637 );
638
639
640
641 -- Standard check of p_commit.
642 IF FND_API.To_Boolean( p_commit ) THEN
643 COMMIT WORK;
644 END IF;
645
646 IF (PO_LOG.d_proc) THEN
647 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
648 PO_LOG.proc_end(d_module);
649 END IF;
650
651
652 EXCEPTION
653
654 WHEN OTHERS THEN
655 ROLLBACK;
656 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
657
658 IF (PO_LOG.d_proc) THEN
659 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
660 PO_LOG.proc_end(d_module);
661 END IF;
662
663 FND_MSG_PUB.Count_And_Get
664 ( p_count => x_msg_count,
665 p_data => x_msg_data
666 );
667
668 END Merge_Vendor;
669
670 END PO_VendorMerge_GRP ;
671
[Home] [Help]