1 PACKAGE BODY PO_VendorMerge_GRP AS
2 /* $Header: PO_VendorMerge_GRP.plb 120.2 2006/08/31 10:25:29 scolvenk noship $ */
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
106 --Bug 5435716 START
107 UPDATE po_headers
108 SET vendor_id = p_vendor_id,
109 vendor_site_id = p_vendor_site_id,
110 last_updated_by = l_last_updated_by,
111 last_update_date = sysdate
112 WHERE vendor_id = p_dup_vendor_id
113 AND vendor_site_id = p_dup_vendor_site_id ;
114
115 UPDATE po_rfq_vendors
116 SET vendor_id = p_vendor_id,
117 vendor_site_id = p_vendor_site_id,
118 last_updated_by = l_last_updated_by,
119 last_update_date = sysdate
120 WHERE vendor_id = p_dup_vendor_id
121 AND vendor_site_id = p_dup_vendor_site_id ;
122
123 DELETE from po_rfq_vendors prv
124 WHERE vendor_id = p_dup_vendor_id
125 AND vendor_site_id = p_dup_vendor_site_id;
126
127
128 --Bug 5435716 END
129
130 -- modify PO_HEADERS_ARCHIVE
131 UPDATE po_headers_archive
132 SET vendor_id = p_vendor_id,
133 vendor_site_id = p_vendor_site_id
134 WHERE vendor_id = p_dup_vendor_id
135 AND vendor_site_id = p_dup_vendor_site_id ;
136
137 IF (PO_LOG.d_stmt) THEN
138 PO_LOG.stmt(d_module, d_progress, 'updated PO_HEADERS_ARCHIVE');
139 END IF;
140
141
142 -- modify PO_VENDOR_LIST_ENTRIES
143 -- bug3537645 added vendor_site_id condition
144 UPDATE po_vendor_list_entries pv1
145 SET pv1.vendor_id = p_vendor_id,
146 pv1.vendor_site_id = p_vendor_site_id
147 WHERE pv1.vendor_id = p_dup_vendor_id
148 AND pv1.vendor_site_id = p_dup_vendor_site_id
149 AND not exists
150 (select vendor_id
151 from po_vendor_list_entries pv2
152 where pv2.vendor_id = p_vendor_id
153 and pv2.vendor_site_id = p_vendor_site_id
154 and pv2.vendor_list_header_id =
155 pv1.vendor_list_header_id);
156
157 -- delete the vendor_list_entry if the new vendor_id would make the
158 -- record a duplicate ie. if modify_po6 had failed
159 -- Anything not moved to the new vendor would have been a duplicate
160 -- and should be deleted
161 -- DELETE from po_vendor_list_entries pvl
162 -- WHERE vendor_id = p_dup_vendor_id
163 -- AND vendor_site_id = p_vendor_site_id;
164 -- Fix for 2086548 commented the above do_sql and wrote the below one
165 DELETE from po_vendor_list_entries pvl
166 WHERE vendor_id = p_dup_vendor_id
167 AND vendor_site_id = p_dup_vendor_site_id ;
168
169
170 IF (PO_LOG.d_stmt) THEN
171 PO_LOG.stmt(d_module, d_progress, 'updated PO_VENDOR_LIST_ENTRIES');
172 END IF;
173
174 -- modify PO_AUTOSOURCE_VENDORS
175 UPDATE po_autosource_vendors pav1
176 SET pav1.vendor_id = p_vendor_id
177 WHERE pav1.vendor_id = p_dup_vendor_id
178 AND not exists
179 (select vendor_id
180 from po_autosource_vendors pav2
181 where pav2.vendor_id = p_vendor_id
182 and pav2.autosource_rule_id =
183 pav1.autosource_rule_id) ;
184
185
186 -- modify split
187 UPDATE po_autosource_vendors pav1
188 SET pav1.split = (SELECT sum (pav3.split)
189 FROM po_autosource_vendors pav3
190 WHERE pav3.autosource_rule_id =
191 pav1.autosource_rule_id
192 AND pav3.vendor_id IN
193 (p_vendor_id, p_dup_vendor_id))
194 WHERE pav1.vendor_id = p_vendor_id
195 AND exists
196 (select pav2.vendor_id
197 from po_autosource_vendors pav2
198 where pav2.vendor_id = p_dup_vendor_id
199 and pav2.autosource_rule_id =
200 pav1.autosource_rule_id) ;
201
202
203
204 -- delete the autosource entry if the new vendor_id would make the
205 -- record a duplicate ie. if modify_po6 had failed
206 -- Anything not moved to the new vendor would have been a duplicate
207 -- and should be deleted
208
209
210 IF (p_dup_vendor_id <> p_vendor_id) THEN
211
212 DELETE from po_autosource_vendors pavl
213 WHERE vendor_id = p_dup_vendor_id ;
214
215 END IF;
216
217 IF (PO_LOG.d_stmt) THEN
218 PO_LOG.stmt(d_module, d_progress, 'updated PO_AUTOSOURCE_VENDORS');
219 END IF;
220
221 -- modify po_autosource_documents
222 --
223 open merge_autosrc_docs;
224
225 loop
226 l_max_seq_num := 0;
227
228 fetch merge_autosrc_docs into
229 l_rule_id, l_seq_num, l_doc_line_id;
230 exit when merge_autosrc_docs%notfound;
231
232 select nvl(max(sequence_num),0)
233 into l_max_seq_num
234 from po_autosource_documents
235 where autosource_rule_id = l_rule_id
236 and vendor_id = p_vendor_id;
237
238 l_new_seq_num := l_max_seq_num + 1;
239
240 update po_autosource_documents
241 set vendor_id = p_vendor_id,
242 sequence_num = l_new_seq_num
243 where autosource_rule_id = l_rule_id
244 and vendor_id = p_dup_vendor_id
245 and sequence_num = l_seq_num
246 and not exists
247 (select 'already have PAD for this rule, vendor, doc line'
248 from po_autosource_documents
249 where autosource_rule_id = l_rule_id
250 and vendor_id = p_vendor_id
251 and document_line_id = l_doc_line_id);
252
253 end loop;
254
255 close merge_autosrc_docs;
256
257 -- delete the document entry if the new vendor_id would make the
258 -- record a duplicate
259
260
261 IF (p_dup_vendor_id <> p_vendor_id) Then
262
263 delete from po_autosource_documents
264 where vendor_id = p_dup_vendor_id;
265
266 End If;
267
268 IF (PO_LOG.d_stmt) THEN
269 PO_LOG.stmt(d_module, d_progress, 'updated PO_AUTOSOURCE_DOCUMENTS');
270 END IF;
271
272 -- Starting from 11i FPJ we no longer update ga org assignments based on
273 -- vendor site code. Instead we just need to update org assignment with
274 -- vendor site id that matches p_from_vendor_id
275
276 UPDATE po_ga_org_assignments PGOA
277 SET PGOA.vendor_site_id = p_vendor_site_id,
278 PGOA.last_update_date = SYSDATE,
279 PGOA.last_updated_by = l_last_updated_by,
280 PGOA.last_update_login = FND_GLOBAL.login_id
281 WHERE PGOA.vendor_site_id = p_dup_vendor_site_id;
282
283 UPDATE po_ga_org_assignments_archive PGOA
284 SET PGOA.vendor_site_id = p_vendor_site_id,
285 PGOA.last_update_date = SYSDATE,
286 PGOA.last_updated_by = l_last_updated_by,
287 PGOA.last_update_login = FND_GLOBAL.login_id
288 WHERE PGOA.vendor_site_id = p_dup_vendor_site_id;
289
290 IF (PO_LOG.d_stmt) THEN
291 PO_LOG.stmt(d_module, d_progress, 'updated PO_GA_ORG_ASSIGNMENTS');
292 END IF;
293
294 -- modify PO_REQUISITION_LINES
295 UPDATE PO_REQUISITION_LINES
296 SET suggested_vendor_name = (select pov1.vendor_name
297 from po_vendors pov1
298 where pov1.vendor_id =
299 p_vendor_id),
300 suggested_vendor_location = (select pvs1.vendor_site_code
301 from po_vendor_sites pvs1
302 where pvs1.vendor_site_id =
303 p_vendor_site_id)
304 WHERE suggested_vendor_name in (select pov2.vendor_name
305 from po_vendors pov2
306 where pov2.vendor_id =
307 p_dup_vendor_id)
308 AND suggested_vendor_location in (select pvs2.vendor_site_code
309 from po_vendor_sites pvs2
310 where vendor_site_id =
311 p_dup_vendor_site_id);
312
313
314 UPDATE po_requisition_lines
315 SET vendor_id = p_vendor_id,
316 vendor_site_id = p_vendor_site_id,
317 last_update_date = sysdate,
318 last_updated_by = l_last_updated_by
319 WHERE vendor_id = p_dup_vendor_id
320 AND vendor_site_id = p_dup_vendor_site_id ;
321
322 UPDATE po_requisition_lines
323 SET vendor_id = p_vendor_id,
324 last_update_date = sysdate,
325 last_updated_by = l_last_updated_by
326 WHERE vendor_id = p_dup_vendor_id
327 AND vendor_site_id is null
328 AND exists
329 ( select vendor_id
330 from po_vendors
331 where vendor_id = p_dup_vendor_id
332 and nvl(end_date_active, sysdate+1) <= sysdate);
333
334 IF (PO_LOG.d_stmt) THEN
335 PO_LOG.stmt(d_module, d_progress, 'updated PO_REQUISITION_LINES');
336 END IF;
337
338
339 -- Update Req Template Records
340 UPDATE po_reqexpress_lines_all PRL
341 SET PRL.suggested_vendor_id = p_vendor_id,
342 PRL.suggested_vendor_site_id = p_vendor_site_id,
343 PRL.last_update_date = SYSDATE,
344 PRL.last_updated_by = l_last_updated_by
345 WHERE PRL.suggested_vendor_id = p_dup_vendor_id
346 AND PRL.suggested_vendor_site_id = p_dup_vendor_site_id;
347
348
349 --SQL What: update requisition template with the new supplier if supplier
350 -- site is null in the template, and the supplier is getting
351 -- invalidatad because of the merge
352 --SQL Why: If the supplier is not active after vendor merge,the records
353 -- associated to that supplier should be moved to point to the
354 -- new supplier
355
356 UPDATE po_reqexpress_lines_all PRL
357 SET PRL.suggested_vendor_id = p_vendor_id,
358 last_update_date = SYSDATE,
359 last_updated_by = l_last_updated_by
360 WHERE PRL.suggested_vendor_id = p_dup_vendor_id
361 AND PRL.suggested_vendor_site_id IS NULL
362 AND EXISTS (
363 SELECT NULL
364 FROM po_vendors PV
365 WHERE PV.vendor_id = p_dup_vendor_id
366 AND NVL(PV.end_date_active, SYSDATE + 1) <= SYSDATE);
367
368
369 IF (PO_LOG.d_stmt) THEN
370 PO_LOG.stmt(d_module, d_progress, 'updated PO_REQEXPRESS_LINES_ALL');
371 END IF;
372
373 -- modify po_approved_supplier_list
374 UPDATE po_approved_supplier_list poasl1
375 SET poasl1.vendor_id = p_vendor_id,
376 poasl1.vendor_site_id = p_vendor_site_id
377 WHERE poasl1.vendor_id = p_dup_vendor_id
378 AND poasl1.vendor_site_id = p_dup_vendor_site_id
379 AND not exists
380 ( select vendor_id
381 from po_approved_supplier_list poasl2
382 where poasl2.vendor_id = p_vendor_id
383 and poasl2.vendor_site_id = p_vendor_site_id
384 and nvl(poasl2.item_id, -99) =
385 nvl(poasl1.item_id, -99)
386 and nvl(poasl2.category_id, -99) =
387 nvl(poasl1.category_id, -99)
388 and poasl2.using_organization_id =
389 poasl1.using_organization_id) ;
390 --1755383 Added the nvl condition so that null values does not result
391 --in success
392
393 -- delete the approved_list_entry if the new vendor_id would make the
394 -- record a duplicate.
395 -- Bug: 1494378
396 DELETE from po_approved_supplier_list poasl
397 WHERE vendor_id = p_dup_vendor_id
398 AND vendor_site_id = p_dup_vendor_site_id ;
399
400 --Bug 1755383 start
401 -- modify po_approved_supplier_list
402
403 UPDATE po_approved_supplier_list poasl1
404 SET poasl1.vendor_id = p_vendor_id
405 WHERE poasl1.vendor_id = p_dup_vendor_id
406 AND poasl1.vendor_site_id is null
407 AND exists
408 ( select vendor_id
409 from po_vendors
410 where vendor_id = p_dup_vendor_id
411 and nvl(end_date_active, sysdate+1) <= sysdate)
412 AND not exists
413 ( select vendor_id
414 from po_approved_supplier_list poasl2
415 where poasl2.vendor_id = p_vendor_id
416 and poasl2.vendor_site_id is null
417 and nvl(poasl2.item_id, -99) =
418 nvl(poasl1.item_id, -99)
419 and nvl(poasl2.category_id, -99) =
420 nvl(poasl1.category_id, -99)
421 and poasl2.using_organization_id =
422 poasl1.using_organization_id);
423
424 -- delete the approved_list_entry if the new vendor_id would make the
425 -- record a duplicate.
426
427 DELETE from po_approved_supplier_list poasl
428 WHERE vendor_id = p_dup_vendor_id
429 AND vendor_site_id is null
430 AND exists
431 ( select vendor_id
432 from po_vendors
433 where vendor_id = p_dup_vendor_id
434 and nvl(end_date_active, sysdate+1) <= sysdate);
435
436 IF (PO_LOG.d_stmt) THEN
437 PO_LOG.stmt(d_module, d_progress, 'updated PO_APPROVED_SUPPLIER_LIST');
438 END IF;
439
440 -- modify po_asl_attributes
441 UPDATE po_asl_attributes poasl1
442 SET poasl1.vendor_id = p_vendor_id,
443 poasl1.vendor_site_id = p_vendor_site_id
444 WHERE poasl1.vendor_id = p_dup_vendor_id
445 AND poasl1.vendor_site_id = p_dup_vendor_site_id
446 AND not exists
447 (select vendor_id
448 from po_asl_attributes poasl2
449 where poasl2.vendor_id = p_vendor_id
450 and poasl2.vendor_site_id = p_vendor_site_id
451 and nvl(poasl2.item_id, -99) = nvl(poasl1.item_id, -99)
452 and nvl(poasl2.category_id, -99) = nvl(poasl1.category_id, -99)
453 and poasl2.using_organization_id =
454 poasl1.using_organization_id);
455 --1755383 Added the nvl condition so that null values does not result
456 --in success
457 -- delete the approved_list_entry if the new vendor_id would make the
458 -- record a duplicate.
459 -- DELETE from po_asl_attributes poasl
460 -- WHERE vendor_id = p_dup_vendor_id
461 -- AND vendor_site_id = p_vendor_site_id ;
462 -- FIX FOR 1931927 commented the above do_sql and wrote the below one
463 DELETE from po_asl_attributes poasl
464 WHERE vendor_id = p_dup_vendor_id
465 AND vendor_site_id = p_dup_vendor_site_id ;
466
467
468 --Bug 1755383 start
469
470 UPDATE po_asl_attributes poasl1
471 SET poasl1.vendor_id = p_vendor_id
472 WHERE poasl1.vendor_id = p_dup_vendor_id
473 AND poasl1.vendor_site_id is null
474 AND exists
475 ( select vendor_id
476 from po_vendors
477 where vendor_id = p_dup_vendor_id
478 and nvl(end_date_active, sysdate+1) <= sysdate)
479 AND not exists
480 (select vendor_id
481 from po_asl_attributes poasl2
482 where poasl2.vendor_id = p_vendor_id
483 and poasl2.vendor_site_id is null
484 and nvl(poasl2.item_id, -99) = nvl(poasl1.item_id, -99)
485 and nvl(poasl2.category_id, -99) = nvl(poasl1.category_id, -99)
486 and poasl2.using_organization_id =
487 poasl1.using_organization_id);
488
489 -- delete the approved_list_entry if the new vendor_id would make the
490 -- record a duplicate.
491
492 DELETE from po_asl_attributes poasl
493 WHERE vendor_id = p_dup_vendor_id
494 AND vendor_site_id is null
495 AND exists
496 ( select vendor_id
497 from po_vendors
498 where vendor_id = p_dup_vendor_id
499 and nvl(end_date_active, sysdate+1) <= sysdate) ;
500
501 IF (PO_LOG.d_stmt) THEN
502 PO_LOG.stmt(d_module, d_progress, 'updated PO_ASL_ATTRIBUTES');
503 END IF;
504
505
506 -- Prepare message name
507 FND_MESSAGE.SET_NAME('PO','PO_ASL_ATTRIBUTES');
508 IF SQL%FOUND THEN
509 x_return_status := FND_API.G_RET_STS_SUCCESS;
510 l_row_count := SQL%ROWCOUNT;
511 ELSE
512 x_return_status := FND_API.G_RET_STS_ERROR;
513 l_row_count := 0;
514 END IF;
515 FND_MESSAGE.SET_TOKEN('ROWS_DELETED',l_row_count);
516 -- Add message to API message list.
517 FND_MSG_PUB.Add;
518
519
520 -- Get message count and if 1, return message data.
521 FND_MSG_PUB.Count_And_Get
522 ( p_count => x_msg_count,
523 p_data => x_msg_data
524 );
525
526
527
528 -- Call the iSP Vendor Merge API
529 -- Commenting out POS calll as it would be called from AP directly
530 -- POS_SUP_PROF_MRG_GRP.handle_merge (
531 -- p_new_vendor_id => p_vendor_id,
532 -- p_new_vendor_site_id => p_vendor_site_id,
533 -- p_old_vendor_id => p_dup_vendor_id,
534 -- p_old_vendor_site_id => p_dup_vendor_site_id ,
535 -- x_return_status => x_return_status
536 -- );
537
538 -- Call the iP Vendor Merge API
539
540 ICX_CAT_POPULATE_ITEM_GRP.populateVendorMerge(
541 p_api_version => 1.0,
542 p_to_vendor_id => p_vendor_id,
543 p_to_site_id => p_vendor_site_id,
544 p_from_vendor_id => p_dup_vendor_id,
545 p_from_site_id => p_dup_vendor_site_id,
546 x_return_status => x_return_status
547 );
548
549
550
551 -- Standard check of p_commit.
552 IF FND_API.To_Boolean( p_commit ) THEN
553 COMMIT WORK;
554 END IF;
555
556 IF (PO_LOG.d_proc) THEN
557 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
558 PO_LOG.proc_end(d_module);
559 END IF;
560
561
562 EXCEPTION
563
564 WHEN OTHERS THEN
565 ROLLBACK;
566 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
567
568 IF (PO_LOG.d_proc) THEN
569 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
570 PO_LOG.proc_end(d_module);
571 END IF;
572
573 FND_MSG_PUB.Count_And_Get
574 ( p_count => x_msg_count,
575 p_data => x_msg_data
576 );
577
578 END Merge_Vendor;
579
580 END PO_VendorMerge_GRP ;
581
[Home] [Help]