1 PACKAGE BODY AP_PartyMerge_GRP AS
2 /* $Header: apgsmrgb.pls 120.5 2011/05/04 09:40:52 kpasikan ship $ */
3
4 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
6 G_MODULE_NAME CONSTANT VARCHAR2(100) := 'AP.PLSQL.AP_PartyMerge_PKG';
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AP_PartyMerge_GRP';
8
9 --
10 -- Procedure Veto_PartySiteMerge
11 --
12
13 PROCEDURE Veto_PartySiteMerge
14 (p_Entity_name IN VARCHAR2,
15 p_from_id IN NUMBER,
16 p_to_id IN OUT NOCOPY NUMBER,
17 p_From_FK_id IN NUMBER,
18 p_To_FK_id IN NUMBER,
19 p_Parent_Entity_name IN VARCHAR2,
20 p_batch_id IN NUMBER,
21 p_Batch_Party_id IN NUMBER,
22 x_return_status IN OUT NOCOPY VARCHAR2) IS
23
24 l_vndrsites_not_merged NUMBER;
25 l_unpaid_invoices NUMBER;
26 l_po_unchecked_sites NUMBER;
27 l_no_mergedto_site NUMBER;
28 l_mismatch_merge_sites NUMBER;
29 l_debug_info VARCHAR2(2000);
30 l_api_name CONSTANT VARCHAR2(30) := 'Veto_PartySiteMerge';
31
32 BEGIN
33
34 x_return_status := FND_API.G_RET_STS_SUCCESS;
35
36 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
37 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
38 'p_Entity_name: '|| p_Entity_name);
39 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
40 'p_from_id: '|| p_from_id);
41 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
42 'p_to_id: '|| p_to_id);
43 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
44 'p_From_FK_id: '|| p_From_FK_id);
45 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
46 'p_To_FK_id: '|| p_To_FK_id);
47 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
48 'p_Parent_Entity_name: '|| p_Parent_Entity_name);
49 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
50 'p_batch_id: '|| p_batch_id);
51 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
52 'p_Batch_Party_id: '|| p_Batch_Party_id);
53 END IF;
54
55 ------------------------------------------------------------------------
56 l_debug_info := 'Validating Veto Rule One';
57 ------------------------------------------------------------------------
58 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
59 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
60 END IF;
61
62 -- Veto Rule 1
63 -- there could be three cases
64 -- case 1: data in HZ_PARTY_SITES + PAV + ADV
65 -- case 2: data in HZ_PARTY_SITES + PAV
66 -- case 3: data in HZ_PARTY_SITES
67 -- the outerjoin takes care of case 2 and because we are using count(*)
68 -- case 3 will be taken care of(resulting in count(*) = 0)
69
70 SELECT count(*)
71 INTO l_vndrsites_not_merged
72 FROM ap_supplier_sites_all pav,
73 ap_duplicate_vendors_all adv
74 WHERE pav.vendor_site_id = adv.duplicate_vendor_site_id(+)
75 AND pav.party_site_id = p_from_fk_id
76 AND nvl(adv.process_flag,'N') <> 'Y';
77
78
79 IF l_vndrsites_not_merged > 0 THEN
80
81 fnd_message.set_name('SQLAP','AP_PARTYSITE_VETO_FAIL');
82 fnd_message.set_token('PARTY_SITE_ID', p_from_fk_id);
83 fnd_msg_pub.ADD;
84 x_return_status := FND_API.G_RET_STS_ERROR;
85
86 END IF;
87
88 ------------------------------------------------------------------------
89 l_debug_info := 'Validating Veto Rule Two';
90 ------------------------------------------------------------------------
91 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
92 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
93 END IF;
94
95 IF x_return_status <> FND_API.G_RET_STS_ERROR THEN
96
97 -- Veto Rule 2
98 -- Checking for unpaid(including partially paid invoices) invoices
99 -- associated with the party_site being merged
100 -- We are not using the data from ap_duplicate_vendors for finding
101 -- this because 3.1.2.1 of veto rules HLD
102
103 SELECT COUNT(*)
104 INTO l_unpaid_invoices
105 FROM ap_invoices_all ai,
106 ap_supplier_sites_all pav
107 WHERE ai.vendor_site_id = pav.vendor_site_id
108 AND pav.party_site_id = p_from_fk_id
109 AND nvl(ai.payment_status_flag,'N') <> 'Y';
110
111 IF l_unpaid_invoices > 0 THEN
112
113 fnd_message.set_name('SQLAP','AP_PARTYSITE_VETO_FAIL');
114 fnd_message.set_token('PARTY_SITE_ID', p_from_fk_id);
115 fnd_msg_pub.ADD;
116 x_return_status := FND_API.G_RET_STS_ERROR;
117
118 END IF;
119 END IF;
120
121 ------------------------------------------------------------------------
122 l_debug_info := 'Validating Veto Rule Three';
123 ------------------------------------------------------------------------
124 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
125 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
126 END IF;
127
128 IF x_return_status <> FND_API.G_RET_STS_ERROR THEN
129
130 -- Veto Rule 3
131 -- Checking if the user had not checked the Transfer PO Checkbox on
132 -- the Supplier Merge Form
133 -- Following results have been found on the Supplier Merge Form
134 -- Invoices Option PO Copy PROCESS FLAG
135 -- All Y Y B
136 -- Unpaid Y Y B
137 -- None Y Y P
138 -- All N Y I
139 -- Unpaid N Y I
140 -- None N Y I
141 -- All Y N B
142 -- Unpaid Y N B
143 -- None Y N P
144 -- All N N I
145 -- Unpaid N N I
146 -- None N N I
147 -- Based on above results, it is assumed that Process Flag value 'I'
148 -- implies PO checkbox value 'N'
149
150 SELECT COUNT(*)
151 INTO l_po_unchecked_sites
152 FROM ap_duplicate_vendors_all adv,
153 ap_supplier_sites_all pav
154 WHERE pav.vendor_site_id = adv.duplicate_vendor_site_id
155 AND pav.party_site_id = p_from_fk_id
156 AND nvl(adv.process, 'N') = 'I';
157
158 IF l_po_unchecked_sites > 0 THEN
159
160 fnd_message.set_name('SQLAP','AP_PARTYSITE_VETO_FAIL');
161 fnd_message.set_token('PARTY_SITE_ID', p_from_fk_id);
162 fnd_msg_pub.ADD;
163 x_return_status := FND_API.G_RET_STS_ERROR;
164
165 END IF;
166 END IF;
167
168 ------------------------------------------------------------------------
169 l_debug_info := 'Validating Veto Rule Four';
170 ------------------------------------------------------------------------
171 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
172 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
173 END IF;
174
175 IF x_return_status <> FND_API.G_RET_STS_ERROR THEN
176
177 -- Veto Rule 4
178 -- A Supplier/Supplier Site is associated with the merged-from
179 -- Party/Party Site but there is no Supplier/Supplier Site
180 -- associated with the merged-to Party/Party Site.
181
182 SELECT count(*)
183 INTO l_no_mergedto_site
184 FROM ap_supplier_sites_all pav
185 WHERE pav.party_site_id = p_from_fk_id
186 AND NOT EXISTS
187 (select vendor_site_id
188 from ap_supplier_sites_all pav1
189 where pav1.party_site_id = p_to_fk_id);
190
191 IF l_no_mergedto_site > 0 THEN
192
193 fnd_message.set_name('SQLAP','AP_PARTY_SUPP_MISS_VETO_FAIL');
194 fnd_message.set_token('PARTY_SITE_ID', p_to_fk_id);
195 fnd_msg_pub.ADD;
196 x_return_status := FND_API.G_RET_STS_ERROR;
197
198 END IF;
199 END IF;
200
201 ------------------------------------------------------------------------
202 l_debug_info := 'Validating Veto Rule Five';
203 ------------------------------------------------------------------------
204 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
205 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
206 END IF;
207
208 IF x_return_status <> FND_API.G_RET_STS_ERROR THEN
209
210 -- Veto Rule 5
211 -- Payables must confirm that the merged-from Party/Party Site
212 -- and merged-to Party/Party Site are correlated th the same
213 -- merged-from Supplier/Supplier Site and merged-to
214 -- Supplier/Supplier Site. For example, if Supplier A is merged
215 -- into Supplier B and Supplier B is then merged into Supplier C,
216 -- the user cannot merge Party A into Party C. In this case,
217 -- the corresponding merged-from Party and merged-to Party are
218 -- not the same.
219
220 SELECT count(*)
221 INTO l_mismatch_merge_sites
222 FROM ap_duplicate_vendors_all adv,
223 ap_supplier_sites_all pav
224 WHERE pav.party_site_id = p_from_fk_id
225 AND pav.vendor_site_id = adv.duplicate_vendor_site_id
226 AND NOT EXISTS
227 (select adv1.vendor_site_id
228 from ap_duplicate_vendors_all adv1,
229 ap_supplier_sites_all pav1
230 where (adv1.vendor_site_id = pav1.vendor_site_id
231 or -- when 'from_fk' site is merged to 'to_fk' site
232 adv1.duplicate_vendor_site_id = pav.vendor_site_id
233 and adv1.vendor_id = pav1.vendor_id
234 and adv1.keep_site_flag = 'Y') --8888020
235 and pav1.party_site_id = p_to_fk_id);
236
237 IF l_mismatch_merge_sites > 0 THEN
238
239 fnd_message.set_name('SQLAP','AP_PARTYSITE_VETO5_FAIL');
240 fnd_message.set_token('PARTY_SITE_ID', p_from_fk_id);
241 fnd_msg_pub.ADD;
242 x_return_status := FND_API.G_RET_STS_ERROR;
243
244 END IF;
245 END IF;
246
247 EXCEPTION
248 WHEN OTHERS THEN
249 IF (SQLCODE <> -20001) THEN
250 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
251 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
252 -- FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
253 FND_MESSAGE.SET_TOKEN('PARAMETERS',
254 'Entity Name = '|| p_Entity_name
255 ||', From Id = '|| to_char(p_from_id )
256 ||', To Id = '|| to_char(p_to_id )
257 ||', From Foreign Key = '|| to_char(p_From_FK_id)
258 ||', To Foreign Key = '|| to_char(p_To_FK_id)
259 ||', Parent Entity Name = '|| p_Parent_Entity_name
260 ||', Batch Id = '|| to_char(p_batch_id)
261 ||', Batch Party Id = '|| to_char(p_Batch_Party_id));
262 FND_MSG_PUB.ADD;
263 END IF;
264
265 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
266
267 END Veto_PartySiteMerge;
268
269 --
270 -- Procedure Veto_PartyMerge
271 --
272
273 Procedure Veto_PartyMerge
274 (p_Entity_name IN VARCHAR2,
275 p_from_id IN NUMBER,
276 p_to_id IN OUT NOCOPY NUMBER,
277 p_From_FK_id IN NUMBER,
278 p_To_FK_id IN NUMBER,
279 p_Parent_Entity_name IN VARCHAR2,
280 p_batch_id IN NUMBER,
281 p_Batch_Party_id IN NUMBER,
282 x_return_status IN OUT NOCOPY VARCHAR2) IS
283
284 l_unpaid_invoices NUMBER;
285 l_po_unchecked_sites NUMBER;
286 l_no_mergedto_site NUMBER;
287 l_mismatch_merge_sites NUMBER;
288 l_vndrsites_not_merged NUMBER;
289 l_api_name CONSTANT VARCHAR2(30) := 'Veto_PartyMerge';
290 l_debug_info VARCHAR2(2000);
291
292 BEGIN
293
294 x_return_status := FND_API.G_RET_STS_SUCCESS;
295
296 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
297 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
298 'p_Entity_name: '|| p_Entity_name);
302 'p_to_id: '|| p_to_id);
299 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
300 'p_from_id: '|| p_from_id);
301 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
303 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
304 'p_From_FK_id: '|| p_From_FK_id);
305 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
306 'p_To_FK_id: '|| p_To_FK_id);
307 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
308 'p_Parent_Entity_name: '|| p_Parent_Entity_name);
309 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
310 'p_batch_id: '|| p_batch_id);
311 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
312 'p_Batch_Party_id: '|| p_Batch_Party_id);
313 END IF;
314
315 ------------------------------------------------------------------------
316 l_debug_info := 'Validating Veto Rule One';
317 ------------------------------------------------------------------------
318 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
319 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
320 END IF;
321
322 -- Veto Rule 1
323 -- there could be four cases
324 -- case 1: data in HZ_PARTIES + PAV + PVS + ADV
325 -- case 2: data in HZ_PARTIES + PAV + PVS
326 -- case 3: data in HZ_PARTIES + PAV
327 -- case 4: data in HZ_PARTIES
328 -- we do not need to do anything for case 3 (there won't be any
329 -- transactions to merge if there are not sites for a vendor)
330 -- case 4.
331 -- The logic is if the vendor_sites exist then there must be records
332 -- in adv for these vendor sites and all such records should have
333 -- process_flag = 'Y' i.e. there should not be any row in adv with
334 -- process_flag <> 'Y'. When data is missing in pov or/and pvs
335 -- the sql below will return coun(*) as zero i.e. nothing to merge
336 -- (go ahead) . when data is there in pov and pvs but not in adv,
337 -- the outerjoin on adv will bring NULL on adv.process_flag hence
338 -- nvl(adv.process_flag,'Y') <> 'Y' is counted .
339 -- when data is there in all three pov, pvs and adv, all such rows
340 -- will be counted by the sql where adv.process_flag <> 'Y' i.e.
341 -- site has not yet been successfully merged.
342
343 SELECT count(*)
344 INTO l_vndrsites_not_merged
345 FROM ap_suppliers pov,
346 ap_supplier_sites_all pvs,
347 ap_duplicate_vendors_all adv
348 WHERE pov.party_id = p_from_fk_id
349 AND pov.vendor_id = pvs.vendor_id
350 AND pvs.vendor_site_id = adv.duplicate_vendor_site_id (+)
351 AND nvl(adv.process_flag, 'N') <> 'Y';
352
353 IF l_vndrsites_not_merged > 0 THEN
354
355 fnd_message.set_name('SQLAP','PARTY_VETO_FAIL');
356 fnd_message.set_token('PARTY_ID', p_from_fk_id);
357 fnd_msg_pub.ADD;
358 x_return_status := FND_API.G_RET_STS_ERROR;
359
360 END IF;
361
362 ------------------------------------------------------------------------
363 l_debug_info := 'Validating Veto Rule Two';
364 ------------------------------------------------------------------------
365 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
366 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
367 END IF;
368
369 IF x_return_status <> FND_API.G_RET_STS_ERROR THEN
370
371 -- Veto Rule 2
372 -- Checking for unpaid(including partially paid invoices) invoices
373 -- associated with the party_site being merged
374 -- We are not using the data from ap_duplicate_vendors for finding
375 -- this because 3.1.2.1 of veto rules HLD
376 -- as we are using count(*), in case if data not being there in any
377 -- of the tables ai, pov, pvs, sql will return zero i.e. go ahead
378
379 SELECT COUNT(*)
380 INTO l_unpaid_invoices
381 FROM ap_invoices_all ai,
382 ap_suppliers pov,
383 ap_supplier_sites_all pvs
384 WHERE ai.vendor_site_id = pvs.vendor_site_id
385 AND pvs.vendor_id = pov.vendor_id
386 AND pov.party_id = p_from_fk_id
387 AND nvl(ai.payment_status_flag,'N') <> 'Y';
388
389 IF l_unpaid_invoices > 0 THEN
390
391 fnd_message.set_name('SQLAP','AP_PARTY_VETO_FAIL');
392 fnd_message.set_token('PARTY_ID', p_from_fk_id);
393 fnd_msg_pub.ADD;
394 x_return_status := FND_API.G_RET_STS_ERROR;
395
396 END IF;
397 END IF;
398
399 ------------------------------------------------------------------------
400 l_debug_info := 'Validating Veto Rule Three';
401 ------------------------------------------------------------------------
402 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
403 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
404 END IF;
405
406 IF x_return_status <> FND_API.G_RET_STS_ERROR THEN
407
408 -- Veto Rule 3
409 -- Checking if the user had not checked the Transfer PO Checkbox on
410 -- the Supplier Merge Form
411 -- Following results have been found on the Supplier Merge Form
412 -- Invoices Option PO Copy PROCESS FLAG
413 -- All Y Y B
414 -- Unpaid Y Y B
415 -- None Y Y P
416 -- All N Y I
420 -- Unpaid Y N B
417 -- Unpaid N Y I
418 -- None N Y I
419 -- All Y N B
421 -- None Y N P
422 -- All N N I
423 -- Unpaid N N I
424 -- None N N I
425 -- Based on above results, it is assumed that Process Flag value 'I'
426 -- implies PO checkbox value 'N'
427
428 SELECT COUNT(*)
429 INTO l_po_unchecked_sites
430 FROM ap_duplicate_vendors_all adv,
431 ap_supplier_sites_all pvs,
432 ap_suppliers pov
433 WHERE pov.party_id = p_from_fk_id
434 and pov.vendor_id = pvs.vendor_id
435 and pvs.vendor_site_id = adv.duplicate_vendor_site_id
436 and nvl(adv.process, 'N') = 'I';
437
438 IF l_po_unchecked_sites > 0 THEN
439
440 fnd_message.set_name('SQLAP','AP_PARTY_VETO_FAIL');
441 fnd_message.set_token('PARTY_ID', p_from_fk_id);
442 fnd_msg_pub.ADD;
443 x_return_status := FND_API.G_RET_STS_ERROR;
444
445 END IF;
446 END IF;
447
448 ------------------------------------------------------------------------
449 l_debug_info := 'Validating Veto Rule Four';
450 ------------------------------------------------------------------------
451 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
452 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
453 END IF;
454
455 IF x_return_status <> FND_API.G_RET_STS_ERROR THEN
456
457 -- Veto Rule 4
458 -- A Supplier/Supplier Site is associated with the merged-from
459 -- Party/Party Site but there is no Supplier/Supplier Site
460 -- associated with the merged-to Party/Party Site.
461 -- we will check for existence of vendor_sites because in AP
462 -- you cannot have transactions until you have vendor sites
463 -- i.e. just having vendors will not help.
464
465 SELECT count(*)
466 INTO l_no_mergedto_site
467 FROM ap_supplier_sites_all pvs,
468 ap_suppliers pov
469 WHERE pov.party_id = p_from_fk_id
470 AND pov.vendor_id = pvs.vendor_id
471 AND NOT EXISTS
472 (select vendor_site_id
473 from ap_supplier_sites_all pvs1,
474 ap_suppliers pov1
475 where pov1.party_id = p_to_fk_id
476 and pov1.vendor_id = pvs1.vendor_id);
477
478 IF l_no_mergedto_site > 0 THEN
479
480 fnd_message.set_name('SQLAP','AP_PARTY_SUPP_MISS_VETO_FAIL');
481 fnd_message.set_token('PARTY_ID', p_to_fk_id);
482 fnd_msg_pub.ADD;
483 x_return_status := FND_API.G_RET_STS_ERROR;
484
485 END IF;
486 END IF;
487
488 ------------------------------------------------------------------------
489 l_debug_info := 'Validating Veto Rule Five';
490 ------------------------------------------------------------------------
491 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
492 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
493 END IF;
494
495 IF x_return_status <> FND_API.G_RET_STS_ERROR THEN
496
497 -- Veto Rule 5
498 -- Payables must confirm that the merged-from Party/Party Site
499 -- and merged-to Party/Party Site are correlated with the same
500 -- merged-from Supplier/Supplier Site and merged-to
501 -- Supplier/Supplier Site. For example, if Supplier A is merged
502 -- into Supplier B and Supplier B is then merged into Supplier C,
503 -- the user cannot merge Party A into Party C. In this case,
504 -- the corresponding merged-from Party and merged-to Party are
505 -- not the same.
506 -- perf bug 5055689 - removed MJC by going to base tables
507 SELECT count(*)
508 INTO l_mismatch_merge_sites
509 FROM ap_duplicate_vendors_all adv,
510 ap_supplier_sites_all apss,
511 ap_suppliers aps
512 WHERE aps.party_id = p_from_fk_id
513 AND aps.vendor_id = apss.vendor_id
514 AND apss.vendor_site_id = adv.duplicate_vendor_site_id
515 AND NOT EXISTS
516 (select adv1.vendor_site_id
517 from ap_duplicate_vendors_all adv1,
518 ap_supplier_sites_all apss1,
519 ap_suppliers aps1
520 where (adv1.vendor_site_id = apss1.vendor_site_id
521 or -- when 'from_fk' site is merged to 'to_fk' site
522 adv1.duplicate_vendor_site_id = apss.vendor_site_id
523 and adv1.vendor_id = apss1.vendor_id
524 and adv1.keep_site_flag = 'Y') --8888020
525 and apss1.vendor_id = aps1.vendor_id
526 and aps1.party_id = p_to_fk_id);
527
528 IF l_mismatch_merge_sites > 0 THEN
529
530 fnd_message.set_name('SQLAP','AP_PARTY_VETO5_FAIL');
531 fnd_message.set_token('PARTY_ID', p_from_id);
532 fnd_msg_pub.ADD;
533 x_return_status := FND_API.G_RET_STS_ERROR;
534
535 END IF;
536 END IF;
537
538 EXCEPTION
539 WHEN OTHERS THEN
540
541 IF (SQLCODE <> -20001) THEN
542 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
543 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
544 -- FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
545 FND_MESSAGE.SET_TOKEN('PARAMETERS',
549 ||', From Foreign Key = '|| to_char(p_From_FK_id)
546 'Entity Name = '|| p_Entity_name
547 ||', From Id = '|| to_char(p_from_id )
548 ||', To Id = '|| to_char(p_to_id )
550 ||', To Foreign Key = '|| to_char(p_To_FK_id)
551 ||', Parent Entity Name = '|| p_Parent_Entity_name
552 ||', Batch Id = '|| to_char(p_batch_id)
553 ||', Batch Party Id = '|| to_char(p_Batch_Party_id));
554 FND_MSG_PUB.ADD;
555 END IF;
556
557 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
558
559 END Veto_PartyMerge;
560
561 --
562 -- Procedure Update_PerPartyid
563 --
564
565 Procedure Update_PerPartyid
566 (p_Entity_name IN VARCHAR2,
567 p_from_id IN NUMBER,
568 p_to_id IN NUMBER,
569 p_From_Fk_id IN NUMBER,
570 p_To_Fk_id IN NUMBER,
571 p_Parent_Entity_name IN VARCHAR2,
572 p_batch_id IN NUMBER,
573 p_Batch_Party_id IN NUMBER,
574 x_return_status IN OUT NOCOPY VARCHAR2) IS
575
576 new_per_party_id NUMBER := p_to_fk_id;
577 old_per_party_id NUMBER := p_from_fk_id;
578 l_api_name CONSTANT VARCHAR2(30) := 'Update_PerPartyid';
579 l_debug_info VARCHAR2(2000);
580
581 BEGIN
582
583 x_return_status := FND_API.G_RET_STS_SUCCESS;
584
585 ------------------------------------------------------------------------
586 l_debug_info := 'Updating po_vendor_contacts';
587 ------------------------------------------------------------------------
588 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
589 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
590 END IF;
591
592 UPDATE ap_supplier_contacts
593 --po_vendor_contacts
594 SET per_party_id = new_per_party_id
595 WHERE per_party_id = old_per_party_id;
596
597 EXCEPTION
598 WHEN OTHERS THEN
599 IF (SQLCODE <> -20001) THEN
600 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
601 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
602 -- FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
603 FND_MESSAGE.SET_TOKEN('PARAMETERS',
604 'Entity Name = '|| p_Entity_name
605 ||', From Id = '|| to_char(p_from_id )
606 ||', To Id = '|| to_char(p_to_id )
607 ||', From Foreign Key = '|| to_char(p_From_FK_id)
608 ||', To Foreign Key = '|| to_char(p_To_FK_id)
609 ||', Parent Entity Name = '|| p_Parent_Entity_name
610 ||', Batch Id = '|| to_char(p_batch_id)
611 ||', Batch Party Id = '|| to_char(p_Batch_Party_id));
612 FND_MSG_PUB.ADD;
613 END IF;
614
615 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
616
617 END Update_PerPartyid ;
618
619 --
620 -- Procedure Update_RelPartyid
621 --
622
623 Procedure Update_RelPartyid
624 (p_Entity_name IN VARCHAR2,
625 p_from_id IN NUMBER,
626 p_to_id IN NUMBER,
627 p_From_Fk_id IN NUMBER,
628 p_To_Fk_id IN NUMBER,
629 p_Parent_Entity_name IN VARCHAR2,
630 p_batch_id IN NUMBER,
631 p_Batch_Party_id IN NUMBER,
632 x_return_status IN OUT NOCOPY VARCHAR2) IS
633
634 new_rel_party_id NUMBER := p_to_fk_id;
635 old_rel_party_id NUMBER := p_from_fk_id;
636 l_api_name CONSTANT VARCHAR2(30) := 'Update_RelPartyid';
637 l_debug_info VARCHAR2(2000);
638
639 BEGIN
640
641 x_return_status := FND_API.G_RET_STS_SUCCESS;
642
643 ------------------------------------------------------------------------
644 l_debug_info := 'Updating po_vendor_contacts';
645 ------------------------------------------------------------------------
646 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
647 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
648 END IF;
649
650 UPDATE ap_supplier_contacts
651 --po_vendor_contacts
652 SET rel_party_id = new_rel_party_id
653 WHERE rel_party_id = old_rel_party_id;
654
655 EXCEPTION
656 WHEN OTHERS THEN
657 IF (SQLCODE <> -20001) THEN
658 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
659 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
660 -- FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
661 FND_MESSAGE.SET_TOKEN('PARAMETERS',
662 'Entity Name = '|| p_Entity_name
663 ||', From Id = '|| to_char(p_from_id )
664 ||', To Id = '|| to_char(p_to_id )
665 ||', From Foreign Key = '|| to_char(p_From_FK_id)
666 ||', To Foreign Key = '|| to_char(p_To_FK_id)
667 ||', Parent Entity Name = '|| p_Parent_Entity_name
668 ||', Batch Id = '|| to_char(p_batch_id)
669 ||', Batch Party Id = '|| to_char(p_Batch_Party_id));
670 FND_MSG_PUB.ADD;
671 END IF;
672
673 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
674
675 END Update_RelPartyid;
676
677 --
678 -- Procedure Update_PartySiteid
679 --
680
681 Procedure Update_PartySiteid
682 (p_Entity_name IN VARCHAR2,
683 p_from_id IN NUMBER,
684 p_to_id IN NUMBER,
685 p_From_Fk_id IN NUMBER,
686 p_To_Fk_id IN NUMBER,
687 p_Parent_Entity_name IN VARCHAR2,
688 p_batch_id IN NUMBER,
689 p_Batch_Party_id IN NUMBER,
693 old_party_site_id NUMBER := p_from_fk_id;
690 x_return_status IN OUT NOCOPY VARCHAR2) IS
691
692 new_party_site_id NUMBER := p_to_fk_id;
694 l_api_name CONSTANT VARCHAR2(30) := 'Update_PartySiteid';
695 l_debug_info VARCHAR2(2000);
696
697 BEGIN
698
699 x_return_status := FND_API.G_RET_STS_SUCCESS;
700
701 ------------------------------------------------------------------------
702 l_debug_info := 'Updating po_vendor_contacts';
703 ------------------------------------------------------------------------
704 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
705 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
706 END IF;
707
708 UPDATE ap_supplier_contacts
709 -- po_vendor_contacts
710 SET party_site_id = new_party_site_id
711 WHERE party_site_id = old_party_site_id;
712
713 EXCEPTION
714 WHEN OTHERS THEN
715 IF (SQLCODE <> -20001) THEN
716 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
717 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
718 -- FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
719 FND_MESSAGE.SET_TOKEN('PARAMETERS',
720 'Entity Name = '|| p_Entity_name
721 ||', From Id = '|| to_char(p_from_id )
722 ||', To Id = '|| to_char(p_to_id )
723 ||', From Foreign Key = '|| to_char(p_From_FK_id)
724 ||', To Foreign Key = '|| to_char(p_To_FK_id)
725 ||', Parent Entity Name = '|| p_Parent_Entity_name
726 ||', Batch Id = '|| to_char(p_batch_id)
727 ||', Batch Party Id = '|| to_char(p_Batch_Party_id));
728 FND_MSG_PUB.ADD;
729 END IF;
730
731 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
732
733 END Update_PartySiteid ;
734
735 /*bug12389663*/
736 Procedure Update_RelationshipId
737 (p_Entity_name IN VARCHAR2,
738 p_from_id IN NUMBER,
739 p_to_id IN NUMBER,
740 p_From_Fk_id IN NUMBER,
741 p_To_Fk_id IN NUMBER,
742 p_Parent_Entity_name IN VARCHAR2,
743 p_batch_id IN NUMBER,
744 p_Batch_Party_id IN NUMBER,
745 x_return_status IN OUT NOCOPY VARCHAR2) IS
746
747 new_relship_id NUMBER := p_to_fk_id;
748 old_relship_id NUMBER := p_from_fk_id;
749 l_api_name CONSTANT VARCHAR2(30) := 'Update_RelationshipId';
750 l_debug_info VARCHAR2(2000);
751
752 BEGIN
753
754 x_return_status := FND_API.G_RET_STS_SUCCESS;
755
756 ------------------------------------------------------------------------
757 l_debug_info := 'Updating po_vendor_contacts';
758 ------------------------------------------------------------------------
759 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
760 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
761 END IF;
762
763 UPDATE ap_supplier_contacts
764 SET relationship_id = new_relship_id
765 WHERE relationship_id = old_relship_id;
766
767 EXCEPTION
768 WHEN OTHERS THEN
769 IF (SQLCODE <> -20001) THEN
770 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
771 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
772 -- FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
773 FND_MESSAGE.SET_TOKEN('PARAMETERS',
774 'Entity Name = '|| p_Entity_name
775 ||', From Id = '|| to_char(p_from_id )
776 ||', To Id = '|| to_char(p_to_id )
777 ||', From Foreign Key = '|| to_char(p_From_FK_id)
778 ||', To Foreign Key = '|| to_char(p_To_FK_id)
779 ||', Parent Entity Name = '|| p_Parent_Entity_name
780 ||', Batch Id = '|| to_char(p_batch_id)
781 ||', Batch Party Id = '|| to_char(p_Batch_Party_id));
782 FND_MSG_PUB.ADD;
783 END IF;
784
785 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
786
787 END Update_RelationshipId ;
788
789 /*bug12389663*/
790 Procedure Update_OrgContactId
791 (p_Entity_name IN VARCHAR2,
792 p_from_id IN NUMBER,
793 p_to_id IN NUMBER,
794 p_From_Fk_id IN NUMBER,
795 p_To_Fk_id IN NUMBER,
796 p_Parent_Entity_name IN VARCHAR2,
797 p_batch_id IN NUMBER,
798 p_Batch_Party_id IN NUMBER,
799 x_return_status IN OUT NOCOPY VARCHAR2) IS
800
801 new_orgcontact_id NUMBER := p_to_fk_id;
802 old_orgcontact_id NUMBER := p_from_fk_id;
803 l_api_name CONSTANT VARCHAR2(30) := 'Update_OrgContactId';
804 l_debug_info VARCHAR2(2000);
805
806 BEGIN
807
808 x_return_status := FND_API.G_RET_STS_SUCCESS;
809
810 ------------------------------------------------------------------------
811 l_debug_info := 'Updating po_vendor_contacts';
812 ------------------------------------------------------------------------
813 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
814 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
815 END IF;
816
817 UPDATE ap_supplier_contacts
818 SET org_contact_id = new_orgcontact_id
819 WHERE org_contact_id = old_orgcontact_id;
820
821 EXCEPTION
822 WHEN OTHERS THEN
823 IF (SQLCODE <> -20001) THEN
824 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
825 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
826 -- FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
827 FND_MESSAGE.SET_TOKEN('PARAMETERS',
831 ||', From Foreign Key = '|| to_char(p_From_FK_id)
828 'Entity Name = '|| p_Entity_name
829 ||', From Id = '|| to_char(p_from_id )
830 ||', To Id = '|| to_char(p_to_id )
832 ||', To Foreign Key = '|| to_char(p_To_FK_id)
833 ||', Parent Entity Name = '|| p_Parent_Entity_name
834 ||', Batch Id = '|| to_char(p_batch_id)
835 ||', Batch Party Id = '|| to_char(p_Batch_Party_id));
836 FND_MSG_PUB.ADD;
837 END IF;
838
839 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
840
841 END Update_OrgContactId ;
842
843
844 END AP_PartyMerge_GRP;
845
[Home] [Help]