1 PACKAGE BODY AP_PartyMerge_GRP AS
2 /* $Header: apgsmrgb.pls 120.3 2006/10/17 19:36:18 schitlap noship $ */
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 and pav1.party_site_id = p_to_fk_id);
232
233 IF l_mismatch_merge_sites > 0 THEN
234
235 fnd_message.set_name('SQLAP','AP_PARTYSITE_VETO5_FAIL');
236 fnd_message.set_token('PARTY_SITE_ID', p_from_fk_id);
237 fnd_msg_pub.ADD;
238 x_return_status := FND_API.G_RET_STS_ERROR;
239
240 END IF;
241 END IF;
242
243 EXCEPTION
244 WHEN OTHERS THEN
245 IF (SQLCODE <> -20001) THEN
246 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
247 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
248 -- FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
249 FND_MESSAGE.SET_TOKEN('PARAMETERS',
250 'Entity Name = '|| p_Entity_name
251 ||', From Id = '|| to_char(p_from_id )
252 ||', To Id = '|| to_char(p_to_id )
253 ||', From Foreign Key = '|| to_char(p_From_FK_id)
254 ||', To Foreign Key = '|| to_char(p_To_FK_id)
255 ||', Parent Entity Name = '|| p_Parent_Entity_name
256 ||', Batch Id = '|| to_char(p_batch_id)
257 ||', Batch Party Id = '|| to_char(p_Batch_Party_id));
258 FND_MSG_PUB.ADD;
259 END IF;
260
261 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
262
263 END Veto_PartySiteMerge;
264
265 --
266 -- Procedure Veto_PartyMerge
267 --
268
269 Procedure Veto_PartyMerge
270 (p_Entity_name IN VARCHAR2,
271 p_from_id IN NUMBER,
272 p_to_id IN OUT NOCOPY NUMBER,
273 p_From_FK_id IN NUMBER,
274 p_To_FK_id IN NUMBER,
275 p_Parent_Entity_name IN VARCHAR2,
276 p_batch_id IN NUMBER,
277 p_Batch_Party_id IN NUMBER,
278 x_return_status IN OUT NOCOPY VARCHAR2) IS
279
280 l_unpaid_invoices NUMBER;
281 l_po_unchecked_sites NUMBER;
282 l_no_mergedto_site NUMBER;
283 l_mismatch_merge_sites NUMBER;
284 l_vndrsites_not_merged NUMBER;
285 l_api_name CONSTANT VARCHAR2(30) := 'Veto_PartyMerge';
286 l_debug_info VARCHAR2(2000);
287
288 BEGIN
289
290 x_return_status := FND_API.G_RET_STS_SUCCESS;
291
292 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
293 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
294 'p_Entity_name: '|| p_Entity_name);
295 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
296 'p_from_id: '|| p_from_id);
297 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
298 'p_to_id: '|| p_to_id);
299 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
300 'p_From_FK_id: '|| p_From_FK_id);
301 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
302 'p_To_FK_id: '|| p_To_FK_id);
303 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
304 'p_Parent_Entity_name: '|| p_Parent_Entity_name);
305 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
306 'p_batch_id: '|| p_batch_id);
307 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
308 'p_Batch_Party_id: '|| p_Batch_Party_id);
309 END IF;
310
311 ------------------------------------------------------------------------
312 l_debug_info := 'Validating Veto Rule One';
313 ------------------------------------------------------------------------
314 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
315 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
316 END IF;
317
318 -- Veto Rule 1
319 -- there could be four cases
320 -- case 1: data in HZ_PARTIES + PAV + PVS + ADV
321 -- case 2: data in HZ_PARTIES + PAV + PVS
325 -- transactions to merge if there are not sites for a vendor)
322 -- case 3: data in HZ_PARTIES + PAV
323 -- case 4: data in HZ_PARTIES
324 -- we do not need to do anything for case 3 (there won't be any
326 -- case 4.
327 -- The logic is if the vendor_sites exist then there must be records
328 -- in adv for these vendor sites and all such records should have
329 -- process_flag = 'Y' i.e. there should not be any row in adv with
330 -- process_flag <> 'Y'. When data is missing in pov or/and pvs
331 -- the sql below will return coun(*) as zero i.e. nothing to merge
332 -- (go ahead) . when data is there in pov and pvs but not in adv,
333 -- the outerjoin on adv will bring NULL on adv.process_flag hence
334 -- nvl(adv.process_flag,'Y') <> 'Y' is counted .
335 -- when data is there in all three pov, pvs and adv, all such rows
336 -- will be counted by the sql where adv.process_flag <> 'Y' i.e.
337 -- site has not yet been successfully merged.
338
339 SELECT count(*)
340 INTO l_vndrsites_not_merged
341 FROM ap_suppliers pov,
342 ap_supplier_sites_all pvs,
343 ap_duplicate_vendors_all adv
344 WHERE pov.party_id = p_from_fk_id
345 AND pov.vendor_id = pvs.vendor_id
346 AND pvs.vendor_site_id = adv.duplicate_vendor_site_id (+)
347 AND nvl(adv.process_flag, 'N') <> 'Y';
348
349 IF l_vndrsites_not_merged > 0 THEN
350
351 fnd_message.set_name('SQLAP','PARTY_VETO_FAIL');
352 fnd_message.set_token('PARTY_ID', p_from_fk_id);
353 fnd_msg_pub.ADD;
354 x_return_status := FND_API.G_RET_STS_ERROR;
355
356 END IF;
357
358 ------------------------------------------------------------------------
359 l_debug_info := 'Validating Veto Rule Two';
360 ------------------------------------------------------------------------
361 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
362 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
363 END IF;
364
365 IF x_return_status <> FND_API.G_RET_STS_ERROR THEN
366
367 -- Veto Rule 2
368 -- Checking for unpaid(including partially paid invoices) invoices
369 -- associated with the party_site being merged
370 -- We are not using the data from ap_duplicate_vendors for finding
371 -- this because 3.1.2.1 of veto rules HLD
372 -- as we are using count(*), in case if data not being there in any
373 -- of the tables ai, pov, pvs, sql will return zero i.e. go ahead
374
375 SELECT COUNT(*)
376 INTO l_unpaid_invoices
377 FROM ap_invoices_all ai,
378 ap_suppliers pov,
379 ap_supplier_sites_all pvs
380 WHERE ai.vendor_site_id = pvs.vendor_site_id
381 AND pvs.vendor_id = pov.vendor_id
382 AND pov.party_id = p_from_fk_id
383 AND nvl(ai.payment_status_flag,'N') <> 'Y';
384
385 IF l_unpaid_invoices > 0 THEN
386
387 fnd_message.set_name('SQLAP','AP_PARTY_VETO_FAIL');
388 fnd_message.set_token('PARTY_ID', p_from_fk_id);
389 fnd_msg_pub.ADD;
390 x_return_status := FND_API.G_RET_STS_ERROR;
391
392 END IF;
393 END IF;
394
395 ------------------------------------------------------------------------
396 l_debug_info := 'Validating Veto Rule Three';
397 ------------------------------------------------------------------------
398 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
399 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
400 END IF;
401
402 IF x_return_status <> FND_API.G_RET_STS_ERROR THEN
403
404 -- Veto Rule 3
405 -- Checking if the user had not checked the Transfer PO Checkbox on
406 -- the Supplier Merge Form
407 -- Following results have been found on the Supplier Merge Form
408 -- Invoices Option PO Copy PROCESS FLAG
409 -- All Y Y B
410 -- Unpaid Y Y B
411 -- None Y Y P
412 -- All N Y I
413 -- Unpaid N Y I
414 -- None N Y I
415 -- All Y N B
416 -- Unpaid Y N B
417 -- None Y N P
418 -- All N N I
419 -- Unpaid N N I
420 -- None N N I
421 -- Based on above results, it is assumed that Process Flag value 'I'
422 -- implies PO checkbox value 'N'
423
424 SELECT COUNT(*)
425 INTO l_po_unchecked_sites
426 FROM ap_duplicate_vendors_all adv,
427 ap_supplier_sites_all pvs,
428 ap_suppliers pov
429 WHERE pov.party_id = p_from_fk_id
430 and pov.vendor_id = pvs.vendor_id
431 and pvs.vendor_site_id = adv.duplicate_vendor_site_id
432 and nvl(adv.process, 'N') = 'I';
433
434 IF l_po_unchecked_sites > 0 THEN
435
436 fnd_message.set_name('SQLAP','AP_PARTY_VETO_FAIL');
437 fnd_message.set_token('PARTY_ID', p_from_fk_id);
438 fnd_msg_pub.ADD;
439 x_return_status := FND_API.G_RET_STS_ERROR;
440
441 END IF;
442 END IF;
443
444 ------------------------------------------------------------------------
448 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
445 l_debug_info := 'Validating Veto Rule Four';
446 ------------------------------------------------------------------------
447 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
449 END IF;
450
451 IF x_return_status <> FND_API.G_RET_STS_ERROR THEN
452
453 -- Veto Rule 4
454 -- A Supplier/Supplier Site is associated with the merged-from
455 -- Party/Party Site but there is no Supplier/Supplier Site
456 -- associated with the merged-to Party/Party Site.
457 -- we will check for existence of vendor_sites because in AP
458 -- you cannot have transactions until you have vendor sites
459 -- i.e. just having vendors will not help.
460
461 SELECT count(*)
462 INTO l_no_mergedto_site
463 FROM ap_supplier_sites_all pvs,
464 ap_suppliers pov
465 WHERE pov.party_id = p_from_fk_id
466 AND pov.vendor_id = pvs.vendor_id
467 AND NOT EXISTS
468 (select vendor_site_id
469 from ap_supplier_sites_all pvs1,
470 ap_suppliers pov1
471 where pov1.party_id = p_to_fk_id
472 and pov1.vendor_id = pvs1.vendor_id);
473
474 IF l_no_mergedto_site > 0 THEN
475
476 fnd_message.set_name('SQLAP','AP_PARTY_SUPP_MISS_VETO_FAIL');
477 fnd_message.set_token('PARTY_ID', p_to_fk_id);
478 fnd_msg_pub.ADD;
479 x_return_status := FND_API.G_RET_STS_ERROR;
480
481 END IF;
482 END IF;
483
484 ------------------------------------------------------------------------
485 l_debug_info := 'Validating Veto Rule Five';
486 ------------------------------------------------------------------------
487 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
488 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
489 END IF;
490
491 IF x_return_status <> FND_API.G_RET_STS_ERROR THEN
492
493 -- Veto Rule 5
494 -- Payables must confirm that the merged-from Party/Party Site
495 -- and merged-to Party/Party Site are correlated with the same
496 -- merged-from Supplier/Supplier Site and merged-to
497 -- Supplier/Supplier Site. For example, if Supplier A is merged
498 -- into Supplier B and Supplier B is then merged into Supplier C,
499 -- the user cannot merge Party A into Party C. In this case,
500 -- the corresponding merged-from Party and merged-to Party are
501 -- not the same.
502 -- perf bug 5055689 - removed MJC by going to base tables
503 SELECT count(*)
504 INTO l_mismatch_merge_sites
505 FROM ap_duplicate_vendors_all adv,
506 ap_supplier_sites_all apss,
507 ap_suppliers aps
508 WHERE aps.party_id = p_from_fk_id
509 AND aps.vendor_id = apss.vendor_id
510 AND apss.vendor_site_id = adv.duplicate_vendor_site_id
511 AND NOT EXISTS
512 (select adv1.vendor_site_id
513 from ap_duplicate_vendors_all adv1,
514 ap_supplier_sites_all apss1,
515 ap_suppliers aps1
516 where adv1.vendor_site_id = apss1.vendor_site_id
517 and apss1.vendor_id = aps1.vendor_id
518 and aps1.party_id = p_to_fk_id);
519
520 IF l_mismatch_merge_sites > 0 THEN
521
522 fnd_message.set_name('SQLAP','AP_PARTY_VETO5_FAIL');
523 fnd_message.set_token('PARTY_ID', p_from_id);
524 fnd_msg_pub.ADD;
525 x_return_status := FND_API.G_RET_STS_ERROR;
526
527 END IF;
528 END IF;
529
530 EXCEPTION
531 WHEN OTHERS THEN
532
533 IF (SQLCODE <> -20001) THEN
534 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
535 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
536 -- FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
537 FND_MESSAGE.SET_TOKEN('PARAMETERS',
538 'Entity Name = '|| p_Entity_name
539 ||', From Id = '|| to_char(p_from_id )
540 ||', To Id = '|| to_char(p_to_id )
541 ||', From Foreign Key = '|| to_char(p_From_FK_id)
542 ||', To Foreign Key = '|| to_char(p_To_FK_id)
543 ||', Parent Entity Name = '|| p_Parent_Entity_name
544 ||', Batch Id = '|| to_char(p_batch_id)
545 ||', Batch Party Id = '|| to_char(p_Batch_Party_id));
546 FND_MSG_PUB.ADD;
547 END IF;
548
549 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
550
551 END Veto_PartyMerge;
552
553 --
554 -- Procedure Update_PerPartyid
555 --
556
557 Procedure Update_PerPartyid
558 (p_Entity_name IN VARCHAR2,
559 p_from_id IN NUMBER,
560 p_to_id IN NUMBER,
561 p_From_Fk_id IN NUMBER,
562 p_To_Fk_id IN NUMBER,
563 p_Parent_Entity_name IN VARCHAR2,
564 p_batch_id IN NUMBER,
565 p_Batch_Party_id IN NUMBER,
566 x_return_status IN OUT NOCOPY VARCHAR2) IS
567
568 new_per_party_id NUMBER := p_to_fk_id;
569 old_per_party_id NUMBER := p_from_fk_id;
570 l_api_name CONSTANT VARCHAR2(30) := 'Update_PerPartyid';
571 l_debug_info VARCHAR2(2000);
572
573 BEGIN
574
575 x_return_status := FND_API.G_RET_STS_SUCCESS;
576
577 ------------------------------------------------------------------------
578 l_debug_info := 'Updating po_vendor_contacts';
579 ------------------------------------------------------------------------
583
580 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
581 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
582 END IF;
584 UPDATE ap_supplier_contacts
585 --po_vendor_contacts
586 SET per_party_id = new_per_party_id
587 WHERE per_party_id = old_per_party_id;
588
589 EXCEPTION
590 WHEN OTHERS THEN
591 IF (SQLCODE <> -20001) THEN
592 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
593 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
594 -- FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
595 FND_MESSAGE.SET_TOKEN('PARAMETERS',
596 'Entity Name = '|| p_Entity_name
597 ||', From Id = '|| to_char(p_from_id )
598 ||', To Id = '|| to_char(p_to_id )
599 ||', From Foreign Key = '|| to_char(p_From_FK_id)
600 ||', To Foreign Key = '|| to_char(p_To_FK_id)
601 ||', Parent Entity Name = '|| p_Parent_Entity_name
602 ||', Batch Id = '|| to_char(p_batch_id)
603 ||', Batch Party Id = '|| to_char(p_Batch_Party_id));
604 FND_MSG_PUB.ADD;
605 END IF;
606
607 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
608
609 END Update_PerPartyid ;
610
611 --
612 -- Procedure Update_RelPartyid
613 --
614
615 Procedure Update_RelPartyid
616 (p_Entity_name IN VARCHAR2,
617 p_from_id IN NUMBER,
618 p_to_id IN NUMBER,
619 p_From_Fk_id IN NUMBER,
620 p_To_Fk_id IN NUMBER,
621 p_Parent_Entity_name IN VARCHAR2,
622 p_batch_id IN NUMBER,
623 p_Batch_Party_id IN NUMBER,
624 x_return_status IN OUT NOCOPY VARCHAR2) IS
625
626 new_rel_party_id NUMBER := p_to_fk_id;
627 old_rel_party_id NUMBER := p_from_fk_id;
628 l_api_name CONSTANT VARCHAR2(30) := 'Update_RelPartyid';
629 l_debug_info VARCHAR2(2000);
630
631 BEGIN
632
633 x_return_status := FND_API.G_RET_STS_SUCCESS;
634
635 ------------------------------------------------------------------------
636 l_debug_info := 'Updating po_vendor_contacts';
637 ------------------------------------------------------------------------
638 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
639 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
640 END IF;
641
642 UPDATE ap_supplier_contacts
643 --po_vendor_contacts
644 SET rel_party_id = new_rel_party_id
645 WHERE rel_party_id = old_rel_party_id;
646
647 EXCEPTION
648 WHEN OTHERS THEN
649 IF (SQLCODE <> -20001) THEN
650 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
651 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
652 -- FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
653 FND_MESSAGE.SET_TOKEN('PARAMETERS',
654 'Entity Name = '|| p_Entity_name
655 ||', From Id = '|| to_char(p_from_id )
656 ||', To Id = '|| to_char(p_to_id )
657 ||', From Foreign Key = '|| to_char(p_From_FK_id)
658 ||', To Foreign Key = '|| to_char(p_To_FK_id)
659 ||', Parent Entity Name = '|| p_Parent_Entity_name
660 ||', Batch Id = '|| to_char(p_batch_id)
661 ||', Batch Party Id = '|| to_char(p_Batch_Party_id));
662 FND_MSG_PUB.ADD;
663 END IF;
664
665 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
666
667 END Update_RelPartyid;
668
669 --
670 -- Procedure Update_PartySiteid
671 --
672
673 Procedure Update_PartySiteid
674 (p_Entity_name IN VARCHAR2,
675 p_from_id IN NUMBER,
676 p_to_id IN NUMBER,
677 p_From_Fk_id IN NUMBER,
678 p_To_Fk_id IN NUMBER,
679 p_Parent_Entity_name IN VARCHAR2,
680 p_batch_id IN NUMBER,
681 p_Batch_Party_id IN NUMBER,
682 x_return_status IN OUT NOCOPY VARCHAR2) IS
683
684 new_party_site_id NUMBER := p_to_fk_id;
685 old_party_site_id NUMBER := p_from_fk_id;
686 l_api_name CONSTANT VARCHAR2(30) := 'Update_PartySiteid';
687 l_debug_info VARCHAR2(2000);
688
689 BEGIN
690
691 x_return_status := FND_API.G_RET_STS_SUCCESS;
692
693 ------------------------------------------------------------------------
694 l_debug_info := 'Updating po_vendor_contacts';
695 ------------------------------------------------------------------------
696 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
697 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
698 END IF;
699
700 UPDATE ap_supplier_contacts
701 -- po_vendor_contacts
702 SET party_site_id = new_party_site_id
703 WHERE party_site_id = old_party_site_id;
704
705 EXCEPTION
706 WHEN OTHERS THEN
707 IF (SQLCODE <> -20001) THEN
708 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
709 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
710 -- FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
711 FND_MESSAGE.SET_TOKEN('PARAMETERS',
712 'Entity Name = '|| p_Entity_name
713 ||', From Id = '|| to_char(p_from_id )
714 ||', To Id = '|| to_char(p_to_id )
715 ||', From Foreign Key = '|| to_char(p_From_FK_id)
716 ||', To Foreign Key = '|| to_char(p_To_FK_id)
717 ||', Parent Entity Name = '|| p_Parent_Entity_name
718 ||', Batch Id = '|| to_char(p_batch_id)
719 ||', Batch Party Id = '|| to_char(p_Batch_Party_id));
720 FND_MSG_PUB.ADD;
721 END IF;
722
723 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
724
725 END Update_PartySiteid ;
726
727 END AP_PartyMerge_GRP;
728
[Home] [Help]