[Home] [Help]
PACKAGE BODY: APPS.IBY_HZ_UNIFICATION_PKG
Source
1 PACKAGE BODY IBY_HZ_UNIFICATION_PKG AS
2 /* $Header: ibyhzufb.pls 120.1 2006/08/24 23:19:07 jleybovi noship $ */
3
4 G_DEBUG_MODULE CONSTANT VARCHAR2(100) := 'iby.plsql.IBY_HZ_UNIFICATION_PKG';
5
6
7 PROCEDURE external_payer_merge
8 (p_entity_name IN VARCHAR2,
9 p_from_id IN NUMBER,
10 p_to_id IN OUT NOCOPY NUMBER,
11 p_from_fk_id IN NUMBER,
12 p_to_fk_id IN NUMBER,
13 p_parent_entity_name IN VARCHAR2,
14 p_batch_id IN NUMBER,
15 p_batch_party_id IN NUMBER,
16 x_return_status IN OUT NOCOPY VARCHAR2
17 )
18 IS
19 l_dup_id iby_external_payers_all.ext_payer_id%TYPE;
20
21 CURSOR c_dupepayer
22 (ci_payer_id IN iby_external_payers_all.ext_payer_id%TYPE,
23 ci_party_id IN iby_external_payers_all.party_id%TYPE)
24 IS
25 SELECT ext_payer_id
26 FROM iby_external_payers_all mto,
27 (SELECT payment_function,ci_party_id,cust_account_id,acct_site_use_id,
28 org_type,org_id
29 FROM iby_external_payers_all
30 WHERE (ext_payer_id = ci_payer_id)) mfrom
31 WHERE mto.ext_payer_id <> ci_payer_id
32 AND mto.payment_function = mfrom.payment_function
33 AND mto.party_id = ci_party_id
34 AND NVL(mto.cust_account_id,-99) = NVL(mfrom.cust_account_id,-99)
35 AND NVL(mto.acct_site_use_id,-99) = NVL(mfrom.acct_site_use_id,-99)
36 AND NVL(mto.org_type,'!') = NVL(mfrom.org_type,'!')
37 AND NVL(mto.org_id,-99) = NVL(mfrom.org_id,-99);
38
39 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.external_payer_merge';
40 BEGIN
41 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
42 x_return_status := FND_API.G_RET_STS_SUCCESS;
43
44 IF (c_dupepayer%ISOPEN) THEN CLOSE c_dupepayer; END IF;
45
46 IF (p_parent_entity_name = 'HZ_PARTIES') THEN
47
48 IF (p_from_fk_id = p_to_fk_id) THEN
49 p_to_id := p_from_id;
50 RETURN;
51 END IF;
52
53 OPEN c_dupepayer(p_from_id,p_to_fk_id);
54 FETCH c_dupepayer INTO l_dup_id;
55 CLOSE c_dupepayer;
56
57 iby_debug_pub.add('duplicate payer:='|| l_dup_id,
58 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
59
60 IF (l_dup_id IS NULL) THEN
61 UPDATE iby_external_payers_all
62 SET party_id = p_to_fk_id,
63 last_update_date = hz_utility_pub.last_update_date,
64 last_updated_by = hz_utility_pub.user_id,
65 last_update_login = hz_utility_pub.last_update_login
66 WHERE ext_payer_id = p_from_id;
67 p_to_id := p_from_id;
68 ELSE
69 p_to_id := l_dup_id;
70 END IF;
71 END IF;
72
73 iby_debug_pub.add('Exit',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
74
75 EXCEPTION
76 WHEN OTHERS THEN
77 iby_debug_pub.add('Unexpected error:=' || SQLERRM,
78 iby_debug_pub.G_LEVEL_UNEXPECTED,l_dbg_mod);
79
80 FND_MESSAGE.SET_NAME('IBY', 'IBY_9999');
81 FND_MESSAGE.SET_TOKEN('MESSAGE_TEXT' ,SQLERRM);
82 FND_MSG_PUB.ADD;
83 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
84 END external_payer_merge;
85
86 PROCEDURE external_payee_merge
87 (p_entity_name IN VARCHAR2,
88 p_from_id IN NUMBER,
89 p_to_id IN OUT NOCOPY NUMBER,
90 p_from_fk_id IN NUMBER,
91 p_to_fk_id IN NUMBER,
92 p_parent_entity_name IN VARCHAR2,
93 p_batch_id IN NUMBER,
94 p_batch_party_id IN NUMBER,
95 x_return_status IN OUT NOCOPY VARCHAR2
96 )
97 IS
98 l_dup_id iby_external_payees_all.ext_payee_id%TYPE;
99 l_new_party_id iby_external_payees_all.payee_party_id%TYPE;
100
101 CURSOR c_dupeparty
102 (ci_payee_id IN iby_external_payees_all.ext_payee_id%TYPE,
103 ci_party_id IN iby_external_payees_all.payee_party_id%TYPE)
104 IS
105 SELECT ext_payee_id
106 FROM iby_external_payees_all mto,
107 (SELECT payment_function,payee_party_id,party_site_id,
108 supplier_site_id,org_type,org_id
109 FROM iby_external_payees_all
110 WHERE (ext_payee_id = ci_payee_id)) mfrom
111 WHERE
112 mto.ext_payee_id <> ci_payee_id
113 AND mto.payment_function = mfrom.payment_function
114 AND mto.payee_party_id = ci_party_id
115 AND NVL(mto.party_site_id,-99) = NVL(mfrom.party_site_id,-99)
116 AND NVL(mto.supplier_site_id,-99) = NVL(mfrom.supplier_site_id,-99)
117 AND NVL(mto.org_type,'!') = NVL(mfrom.org_type,'!')
118 AND NVL(mto.org_id,-99) = NVL(mfrom.org_id,-99);
119
120 CURSOR c_dupesite
121 (ci_payee_id IN iby_external_payees_all.ext_payee_id%TYPE,
122 ci_site_id IN iby_external_payees_all.party_site_id%TYPE)
123 IS
124 SELECT ext_payee_id, np.party_id
125 FROM iby_external_payees_all mto,
126 (SELECT payment_function,payee_party_id,party_site_id,
127 supplier_site_id,org_type,org_id
128 FROM iby_external_payees_all
129 WHERE (ext_payee_id = ci_payee_id)) mfrom,
130 (SELECT party_id FROM hz_party_sites WHERE party_site_id = ci_site_id) np
131 WHERE
132 mto.ext_payee_id <> ci_payee_id
133 AND mto.payment_function = mfrom.payment_function
134 --
135 -- party site may be merged/transferred directly, in which case party
136 -- merge will not occur and party id of the merge-to entity
137 -- may be different
138 --
139 --AND mto.payee_party_id = mfrom.payee_party_id
140 AND mto.party_site_id = ci_site_id
141 AND NVL(mto.supplier_site_id,-99) = NVL(mfrom.supplier_site_id,-99)
142 AND NVL(mto.org_type,'!') = NVL(mfrom.org_type,'!')
143 AND NVL(mto.org_id,-99) = NVL(mfrom.org_id,-99);
144
145 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.external_payee_merge';
146 BEGIN
147 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
148 x_return_status := FND_API.G_RET_STS_SUCCESS;
149
150 IF (c_dupeparty%ISOPEN) THEN CLOSE c_dupeparty; END IF;
151
152 p_to_id := p_from_id;
153
154 IF (p_parent_entity_name = 'HZ_PARTIES') THEN
155
156 IF (p_from_fk_id = p_to_fk_id) THEN
157 p_to_id := p_from_id;
158 RETURN;
159 END IF;
160
161 OPEN c_dupeparty(p_from_id,p_to_fk_id);
162 FETCH c_dupeparty INTO l_dup_id;
163 CLOSE c_dupeparty;
164
165 iby_debug_pub.add('duplicate payee:='|| l_dup_id,
166 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
167
168 UPDATE iby_external_payees_all
169 SET payee_party_id = DECODE(l_dup_id, NULL,p_to_fk_id, payee_party_id),
170 inactive_date = DECODE(l_dup_id, NULL,inactive_date, SYSDATE),
171 last_update_date = hz_utility_pub.last_update_date,
172 last_updated_by = hz_utility_pub.user_id,
173 last_update_login = hz_utility_pub.last_update_login
174 WHERE ext_payee_id = p_from_id;
175
176 IF (l_dup_id IS NULL) THEN
177 p_to_id := p_from_id;
178 ELSE
179 p_to_id := l_dup_id;
180 END IF;
181 ELSIF (p_parent_entity_name = 'HZ_PARTY_SITES') THEN
182
183 OPEN c_dupesite(p_from_id,p_to_fk_id);
184 FETCH c_dupesite INTO l_dup_id, l_new_party_id;
185 CLOSE c_dupesite;
186
187 iby_debug_pub.add('duplicate payee:='|| l_dup_id,
188 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
189
190 UPDATE iby_external_payees_all
191 SET party_site_id = DECODE(l_dup_id, NULL,p_to_fk_id, party_site_id),
192 --
193 -- update the denormalized party id to the new party in the
194 -- case of either merge or transfer of the site
195 --
196 payee_party_id = l_new_party_id,
197 inactive_date = DECODE(l_dup_id, NULL,inactive_date, SYSDATE),
198 last_update_date = hz_utility_pub.last_update_date,
199 last_updated_by = hz_utility_pub.user_id,
200 last_update_login = hz_utility_pub.last_update_login
201 WHERE ext_payee_id = p_from_id;
202
203 IF (l_dup_id IS NULL) THEN
204 p_to_id := p_from_id;
205 ELSE
206 p_to_id := l_dup_id;
207 END IF;
208 END IF;
209
210 iby_debug_pub.add('Exit',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
211
212 EXCEPTION
213 WHEN OTHERS THEN
214 iby_debug_pub.add('Unexpected error:=' || SQLERRM,
215 iby_debug_pub.G_LEVEL_UNEXPECTED,l_dbg_mod);
216
217 FND_MESSAGE.SET_NAME('IBY', 'IBY_9999');
218 FND_MESSAGE.SET_TOKEN('MESSAGE_TEXT' ,SQLERRM);
219 FND_MSG_PUB.ADD;
220 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
221 END external_payee_merge;
222
223 PROCEDURE credit_card_merge
224 (p_entity_name IN VARCHAR2,
225 p_from_id IN NUMBER,
226 p_to_id IN OUT NOCOPY NUMBER,
227 p_from_fk_id IN NUMBER,
228 p_to_fk_id IN NUMBER,
229 p_parent_entity_name IN VARCHAR2,
230 p_batch_id IN NUMBER,
231 p_batch_party_id IN NUMBER,
232 x_return_status IN OUT NOCOPY VARCHAR2
233 )
234 IS
235 CURSOR c_dupecc
236 (ci_instr_id IN iby_creditcard.instrid%TYPE,
237 ci_owner_id IN iby_creditcard.card_owner_id%TYPE)
238 IS
239 SELECT instrid
240 FROM iby_creditcard mto,
241 (SELECT cc_number_hash1, cc_number_hash2
242 FROM iby_creditcard WHERE (instrid = ci_instr_id)) mfrom
243 WHERE
244 mto.card_owner_id = ci_owner_id
245 AND mto.cc_number_hash1 = mfrom.cc_number_hash1
246 AND mto.cc_number_hash2 = mfrom.cc_number_hash2
247 AND NVL(mto.active_flag,'Y') = 'Y'
248 AND (NVL(mto.inactive_date,SYSDATE+10) > SYSDATE);
249
250 l_merge_reason VARCHAR2(30);
251 l_dup_id iby_creditcard.instrid%TYPE := NULL;
252
253 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.credit_card_merge';
254 BEGIN
255 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
256 x_return_status := FND_API.G_RET_STS_SUCCESS;
257
258 IF (c_dupecc%ISOPEN) THEN CLOSE c_dupecc; END IF;
259
260 iby_debug_pub.add('batch id:='|| p_batch_id,
261 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
262
263 SELECT merge_reason_code
264 INTO l_merge_reason
265 FROM hz_merge_batch
266 WHERE batch_id = p_batch_id;
267
268 IF (p_from_fk_id = p_to_fk_id) THEN
269 p_to_id := p_from_id;
270 RETURN;
271 END IF;
272
273
274 IF (p_parent_entity_name = 'HZ_PARTIES') THEN
275
276 IF (l_merge_reason <> 'DUPLICATE') THEN
277 iby_debug_pub.add('cannot unify non-duplicate credit cards',
278 iby_debug_pub.G_LEVEL_ERROR,l_dbg_mod);
279
280 fnd_message.set_name('IBY','IBY_PARTY_UNIFY_CC_VETO');
281 fnd_msg_pub.ADD;
282 x_return_status := fnd_api.g_ret_sts_error;
283 END IF;
284
285
286 OPEN c_dupecc(p_from_id,p_to_fk_id);
287 FETCH c_dupecc INTO l_dup_id;
288 CLOSE c_dupecc;
289
290 iby_debug_pub.add('duplicate card:='|| l_dup_id,
291 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
292
293 UPDATE iby_creditcard
294 SET card_owner_id = DECODE(l_dup_id, NULL,p_to_fk_id, card_owner_id),
295 active_flag = DECODE(l_dup_id, NULL,active_flag, 'N'),
296 inactive_date = DECODE(l_dup_id, NULL,inactive_date, SYSDATE),
297 last_update_date = hz_utility_pub.last_update_date,
298 last_updated_by = hz_utility_pub.user_id,
299 last_update_login = hz_utility_pub.last_update_login,
300 request_id = hz_utility_pub.request_id,
301 program_application_id = hz_utility_pub.program_application_id,
302 program_id = hz_utility_pub.program_id,
303 program_update_date = sysdate
304 WHERE (instrid = p_from_id);
305
306 p_to_id := NVL(l_dup_id,p_from_id);
307
308 ELSIF (p_parent_entity_name = 'HZ_PARTY_SITE_USES') THEN
309
310 UPDATE iby_creditcard
311 SET addressid = p_to_fk_id,
312 last_update_date = hz_utility_pub.last_update_date,
313 last_updated_by = hz_utility_pub.user_id,
314 last_update_login = hz_utility_pub.last_update_login,
315 request_id = hz_utility_pub.request_id,
316 program_application_id = hz_utility_pub.program_application_id,
317 program_id = hz_utility_pub.program_id,
318 program_update_date = sysdate
319 WHERE (instrid = p_from_id);
320
321 p_to_id := p_from_id;
322
323 END IF;
324
325 iby_debug_pub.add('Exit',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
326
327 EXCEPTION
328 WHEN OTHERS THEN
329 iby_debug_pub.add('Unexpected error:=' || SQLERRM,
330 iby_debug_pub.G_LEVEL_UNEXPECTED,l_dbg_mod);
331
332 FND_MESSAGE.SET_NAME('IBY', 'IBY_9999');
333 FND_MESSAGE.SET_TOKEN('MESSAGE_TEXT' ,SQLERRM);
334 FND_MSG_PUB.ADD;
335 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
336 END credit_card_merge;
337
338 PROCEDURE pmt_instrument_use_merge
339 (p_entity_name IN VARCHAR2,
340 p_from_id IN NUMBER,
341 p_to_id IN OUT NOCOPY NUMBER,
342 p_from_fk_id IN NUMBER,
343 p_to_fk_id IN NUMBER,
344 p_parent_entity_name IN VARCHAR2,
345 p_batch_id IN NUMBER,
346 p_batch_party_id IN NUMBER,
347 x_return_status IN OUT NOCOPY VARCHAR2
348 )
349 IS
350 l_dup_id iby_pmt_instr_uses_all.instrument_payment_use_id%TYPE := NULL;
351 l_flow_type iby_pmt_instr_uses_all.payment_flow%TYPE;
352
353 CURSOR c_dupinstr
354 (ci_use_id IN iby_pmt_instr_uses_all.instrument_payment_use_id%TYPE,
355 ci_instr_id IN iby_pmt_instr_uses_all.instrument_id%TYPE,
356 ci_instr_type IN iby_pmt_instr_uses_all.instrument_type%TYPE)
357 IS
358 SELECT mto.instrument_payment_use_id
359 FROM iby_pmt_instr_uses_all mto,
360 (SELECT ext_pmt_party_id,payment_flow
361 FROM iby_pmt_instr_uses_all
362 WHERE instrument_payment_use_id = ci_use_id) mfrom
363 WHERE
364 mto.instrument_payment_use_id <> ci_use_id
365 AND mto.payment_flow = mfrom.payment_flow
366 AND mto.ext_pmt_party_id = mfrom.ext_pmt_party_id
367 AND mto.instrument_type = ci_instr_type
368 AND mto.instrument_id = ci_instr_id;
369
370 CURSOR c_dupeparty
371 (ci_use_id IN iby_pmt_instr_uses_all.instrument_payment_use_id%TYPE,
372 ci_party_id IN iby_pmt_instr_uses_all.ext_pmt_party_id%TYPE,
373 ci_flow_type IN iby_pmt_instr_uses_all.payment_flow%TYPE)
374 IS
375 SELECT mto.instrument_payment_use_id
376 FROM iby_pmt_instr_uses_all mto,
377 (SELECT instrument_type,instrument_id
378 FROM iby_pmt_instr_uses_all
382 AND mto.payment_flow = ci_flow_type
379 WHERE instrument_payment_use_id = ci_use_id) mfrom
380 WHERE
381 mto.instrument_payment_use_id <> ci_use_id
383 AND mto.ext_pmt_party_id = ci_party_id
384 AND mto.instrument_type = mfrom.instrument_type
385 AND mto.instrument_id = mfrom.instrument_id;
386
387 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.pmt_instrument_use_merge';
388 BEGIN
389 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
390 x_return_status := FND_API.G_RET_STS_SUCCESS;
391
392 IF (c_dupinstr%ISOPEN) THEN CLOSE c_dupinstr; END IF;
393
394 IF (p_parent_entity_name = 'IBY_CREDITCARD') THEN
395
396 IF (p_from_fk_id = p_to_fk_id) THEN
397 p_to_id := p_from_id;
398 RETURN;
399 END IF;
400
401 OPEN c_dupinstr(p_from_id,p_to_fk_id,'CREDITCARD');
402 FETCH c_dupinstr INTO l_dup_id;
403 CLOSE c_dupinstr;
404
405 iby_debug_pub.add('duplicate instr use:='|| l_dup_id,
406 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
407
408 UPDATE iby_pmt_instr_uses_all
409 SET instrument_id = DECODE(l_dup_id, NULL,p_to_id, instrument_id),
410 end_date = DECODE(l_dup_id, NULL,end_date, SYSDATE),
411 last_update_date = hz_utility_pub.last_update_date,
412 last_updated_by = hz_utility_pub.user_id,
413 last_update_login = hz_utility_pub.last_update_login
414 WHERE (instrument_payment_use_id = p_from_id);
415
416 IF (l_dup_id IS NULL) THEN
417 p_to_id := p_from_id;
418 ELSE
419 p_to_id := l_dup_id;
420 END IF;
421
422 ELSIF (p_parent_entity_name = 'IBY_EXTERNAL_PAYEES_ALL')
423 OR (p_parent_entity_name = 'IBY_EXTERNAL_PAYERS_ALL')
424 THEN
425
426 IF (p_from_fk_id = p_to_fk_id) THEN
427 p_to_id := p_from_id;
428 RETURN;
429 END IF;
430
431 IF (p_parent_entity_name = 'IBY_EXTERNAL_PAYERS_ALL') THEN
432 l_flow_type := 'FUNDS_CAPTURE';
433 ELSE
434 l_flow_type := 'DISBURSEMENTS';
435 END IF;
436
437 OPEN c_dupinstr(p_from_id,p_to_fk_id,l_flow_type);
438 FETCH c_dupinstr INTO l_dup_id;
439 CLOSE c_dupinstr;
440
441 iby_debug_pub.add('duplicate instr use:='|| l_dup_id,
442 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
443
444 UPDATE iby_pmt_instr_uses_all
445 SET ext_pmt_party_id = DECODE(l_dup_id, NULL,p_to_id, ext_pmt_party_id),
446 end_date = DECODE(l_dup_id, NULL,end_date, SYSDATE),
447 last_update_date = hz_utility_pub.last_update_date,
448 last_updated_by = hz_utility_pub.user_id,
449 last_update_login = hz_utility_pub.last_update_login
450 WHERE (instrument_payment_use_id = p_from_id);
451
452 IF (l_dup_id IS NULL) THEN
453 p_to_id := p_from_id;
454 ELSE
455 p_to_id := l_dup_id;
456 END IF;
457 END IF;
458
459 iby_debug_pub.add('Exit',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
460 EXCEPTION
461 WHEN OTHERS THEN
462 iby_debug_pub.add('Unexpected error:=' || SQLERRM,
463 iby_debug_pub.G_LEVEL_UNEXPECTED,l_dbg_mod);
464
465 FND_MESSAGE.SET_NAME('IBY', 'IBY_9999');
466 FND_MESSAGE.SET_TOKEN('MESSAGE_TEXT' ,SQLERRM);
467 FND_MSG_PUB.ADD;
468 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
469 END pmt_instrument_use_merge;
470
471 PROCEDURE bank_acct_owner_merge
472 (p_entity_name IN VARCHAR2,
473 p_from_id IN NUMBER,
474 p_to_id IN OUT NOCOPY NUMBER,
475 p_from_fk_id IN NUMBER,
476 p_to_fk_id IN NUMBER,
477 p_parent_entity_name IN VARCHAR2,
478 p_batch_id IN NUMBER,
479 p_batch_party_id IN NUMBER,
480 x_return_status IN OUT NOCOPY VARCHAR2
481 )
482 IS
483 l_dup_id iby_account_owners.account_owner_party_id%TYPE := NULL;
484 l_merge_reason VARCHAR2(30);
485
486 CURSOR c_dupeba
487 (ci_owner_id IN iby_account_owners.account_owner_id%TYPE,
488 ci_party_id IN iby_account_owners.account_owner_party_id%TYPE)
489 IS
490 SELECT account_owner_id
491 FROM iby_account_owners mto,
492 (SELECT ext_bank_account_id
493 FROM iby_account_owners WHERE (account_owner_id = ci_owner_id)) mfrom
494 WHERE
495 mto.account_owner_party_id = ci_party_id
496 AND mto.ext_bank_account_id = mfrom.ext_bank_account_id;
497 --AND (NVL(mto.end_date,SYSDATE+10) > SYSDATE);
498
499
500 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.bank_acct_owner_merge';
501 BEGIN
502 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
503 x_return_status := FND_API.G_RET_STS_SUCCESS;
504
505 IF c_dupeba%ISOPEN THEN CLOSE c_dupeba; END IF;
506
507 IF (p_from_fk_id = p_to_fk_id) THEN
508 p_to_id := p_from_id;
509 RETURN;
510 END IF;
511
512 SELECT merge_reason_code
513 INTO l_merge_reason
514 FROM hz_merge_batch
515 WHERE batch_id = p_batch_id;
516
517 IF (l_merge_reason <> 'DUPLICATE') THEN
518 iby_debug_pub.add('cannot unify non-duplicate bank account owners',
519 iby_debug_pub.G_LEVEL_ERROR,l_dbg_mod);
520
521 fnd_message.set_name('IBY','IBY_PARTY_UNIFY_BA_VETO');
522 fnd_msg_pub.ADD;
523 x_return_status := fnd_api.g_ret_sts_error;
524 END IF;
525
526 OPEN c_dupeba(p_from_id,p_to_fk_id);
527 FETCH c_dupeba INTO l_dup_id;
531 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
528 CLOSE c_dupeba;
529
530 iby_debug_pub.add('duplicate account owner:='|| l_dup_id,
532
533 UPDATE iby_account_owners
534 SET account_owner_party_id = DECODE(l_dup_id, NULL,p_to_id, account_owner_party_id),
535 end_date = DECODE(l_dup_id, NULL,end_date, SYSDATE),
536 last_update_date = hz_utility_pub.last_update_date,
537 last_updated_by = hz_utility_pub.user_id,
538 last_update_login = hz_utility_pub.last_update_login
539 WHERE (account_owner_id = p_from_id);
540
541 IF (l_dup_id IS NULL) THEN
542 p_to_id := p_from_id;
543 ELSE
544 p_to_id := l_dup_id;
545 END IF;
546
547 iby_debug_pub.add('Exit',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
548 EXCEPTION
549 WHEN OTHERS THEN
550 iby_debug_pub.add('Unexpected error:=' || SQLERRM,
551 iby_debug_pub.G_LEVEL_UNEXPECTED,l_dbg_mod);
552
553 FND_MESSAGE.SET_NAME('IBY', 'IBY_9999');
554 FND_MESSAGE.SET_TOKEN('MESSAGE_TEXT' ,SQLERRM);
555 FND_MSG_PUB.ADD;
556 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
557 END bank_acct_owner_merge;
558
559 PROCEDURE party_pmt_methods_merge
560 (p_entity_name IN VARCHAR2,
561 p_from_id IN NUMBER,
562 p_to_id IN OUT NOCOPY NUMBER,
563 p_from_fk_id IN NUMBER,
564 p_to_fk_id IN NUMBER,
565 p_parent_entity_name IN VARCHAR2,
566 p_batch_id IN NUMBER,
567 p_batch_party_id IN NUMBER,
568 x_return_status IN OUT NOCOPY VARCHAR2
569 )
570 IS
571 l_dup_id iby_ext_party_pmt_mthds.ext_party_pmt_mthd_id%TYPE := NULL;
572 l_flow_type iby_ext_party_pmt_mthds.payment_flow%TYPE;
573
574 CURSOR c_dupemth
575 (ci_mth_id IN iby_ext_party_pmt_mthds.ext_party_pmt_mthd_id%TYPE,
576 ci_party_id IN iby_ext_party_pmt_mthds.ext_pmt_party_id%TYPE,
577 ci_flow_type IN iby_ext_party_pmt_mthds.payment_flow%TYPE)
578 IS
579 SELECT ext_party_pmt_mthd_id
580 FROM iby_ext_party_pmt_mthds mto,
581 (SELECT payment_method_code
582 FROM iby_ext_party_pmt_mthds
583 WHERE (ext_party_pmt_mthd_id = ci_mth_id)) mfrom
584 WHERE
585 mto.ext_pmt_party_id = ci_party_id
586 AND mto.payment_flow = ci_flow_type
587 AND mto.payment_method_code = mfrom.payment_method_code;
588 --AND (NVL(mto.inactive_date,SYSDATE+10) > SYSDATE);
589
590 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.party_pmt_methods_merge';
591 BEGIN
592 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
593 x_return_status := FND_API.G_RET_STS_SUCCESS;
594
595 IF (p_from_fk_id = p_to_fk_id) THEN
596 p_to_id := p_from_id;
597 RETURN;
598 END IF;
599
600 IF (p_parent_entity_name = 'IBY_EXTERNAL_PAYERS_ALL') THEN
601 l_flow_type := 'FUNDS_CAPTURE';
602 ELSE
603 l_flow_type := 'DISBURSEMENTS';
604 END IF;
605
606 OPEN c_dupemth(p_from_id,p_to_fk_id,l_flow_type);
607 FETCH c_dupemth INTO l_dup_id;
608 CLOSE c_dupemth;
609
610 iby_debug_pub.add('duplicate pmt method:='|| l_dup_id,
611 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
612
613 UPDATE iby_ext_party_pmt_mthds
614 SET ext_pmt_party_id = DECODE(l_dup_id, NULL,p_to_id, ext_pmt_party_id),
615 inactive_date = DECODE(l_dup_id, NULL,inactive_date, SYSDATE),
616 last_update_date = hz_utility_pub.last_update_date,
617 last_updated_by = hz_utility_pub.user_id,
618 last_update_login = hz_utility_pub.last_update_login
619 WHERE (ext_party_pmt_mthd_id = p_from_id);
620
621 IF (l_dup_id IS NULL) THEN
622 p_to_id := p_from_id;
623 ELSE
624 p_to_id := l_dup_id;
625 END IF;
626
627 iby_debug_pub.add('Exit',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
628 EXCEPTION
629 WHEN OTHERS THEN
630 iby_debug_pub.add('Unexpected error:=' || SQLERRM,
631 iby_debug_pub.G_LEVEL_UNEXPECTED,l_dbg_mod);
632
633 FND_MESSAGE.SET_NAME('IBY', 'IBY_9999');
634 FND_MESSAGE.SET_TOKEN('MESSAGE_TEXT' ,SQLERRM);
635 FND_MSG_PUB.ADD;
636 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
637 END party_pmt_methods_merge;
638
639 PROCEDURE fc_tx_extensions_merge
640 (p_entity_name IN VARCHAR2,
641 p_from_id IN NUMBER,
642 p_to_id IN OUT NOCOPY NUMBER,
643 p_from_fk_id IN NUMBER,
644 p_to_fk_id IN NUMBER,
645 p_parent_entity_name IN VARCHAR2,
646 p_batch_id IN NUMBER,
647 p_batch_party_id IN NUMBER,
648 x_return_status IN OUT NOCOPY VARCHAR2
649 )
650 IS
651 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.fc_tx_extensions_merge';
652 BEGIN
653 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
654 x_return_status := FND_API.G_RET_STS_SUCCESS;
655
656 -- transactional entities never merged
657 p_to_id := p_from_id;
658
659 IF (p_from_fk_id = p_to_fk_id) THEN
660 p_to_id := p_from_id;
661 RETURN;
662 END IF;
663
664 IF (p_parent_entity_name = 'IBY_EXTERNAL_PAYERS_ALL') THEN
665
666 UPDATE iby_fndcpt_tx_extensions
667 SET ext_payer_id = p_to_fk_id,
668 last_update_date = hz_utility_pub.last_update_date,
669 last_updated_by = hz_utility_pub.user_id,
670 last_update_login = hz_utility_pub.last_update_login
671 WHERE (trxn_extension_id = p_from_id);
672
676 SET instr_assignment_id = p_to_fk_id,
673 ELSIF (p_parent_entity_name = 'IBY_PMT_INSTR_USES_ALL') THEN
674
675 UPDATE iby_fndcpt_tx_extensions
677 last_update_date = hz_utility_pub.last_update_date,
678 last_updated_by = hz_utility_pub.user_id,
679 last_update_login = hz_utility_pub.last_update_login
680 WHERE (trxn_extension_id = p_from_id);
681
682 END IF;
683
684 iby_debug_pub.add('Exit',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
685 EXCEPTION
686 WHEN OTHERS THEN
687 iby_debug_pub.add('Unexpected error:=' || SQLERRM,
688 iby_debug_pub.G_LEVEL_UNEXPECTED,l_dbg_mod);
689
690 FND_MESSAGE.SET_NAME('IBY', 'IBY_9999');
691 FND_MESSAGE.SET_TOKEN('MESSAGE_TEXT' ,SQLERRM);
692 FND_MSG_PUB.ADD;
693 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
694 END fc_tx_extensions_merge;
695
696 PROCEDURE txn_summ_all_merge
697 (p_entity_name IN VARCHAR2,
698 p_from_id IN NUMBER,
699 p_to_id IN OUT NOCOPY NUMBER,
700 p_from_fk_id IN NUMBER,
701 p_to_fk_id IN NUMBER,
702 p_parent_entity_name IN VARCHAR2,
703 p_batch_id IN NUMBER,
704 p_batch_party_id IN NUMBER,
705 x_return_status IN OUT NOCOPY VARCHAR2
706 )
707 IS
708 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.txn_summ_all_merge';
709 BEGIN
710 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
711 x_return_status := FND_API.G_RET_STS_SUCCESS;
712
713 -- transactional entities never merged
714 p_to_id := p_from_id;
715
716 IF (p_from_fk_id = p_to_fk_id) THEN
717 p_to_id := p_from_id;
718 RETURN;
719 END IF;
720
721 IF (p_parent_entity_name = 'HZ_PARTIES') THEN
722
723 UPDATE iby_trxn_summaries_all
724 SET payerid = p_to_fk_id,
725 last_update_date = hz_utility_pub.last_update_date,
726 last_updated_by = hz_utility_pub.user_id,
727 last_update_login = hz_utility_pub.last_update_login
728 WHERE (trxnmid = p_from_id);
729
730 ELSIF (p_parent_entity_name = 'IBY_PMT_INSTR_USES_ALL') THEN
731
732 UPDATE iby_trxn_summaries_all
733 SET payer_instr_assignment_id = p_to_fk_id,
734 last_update_date = hz_utility_pub.last_update_date,
735 last_updated_by = hz_utility_pub.user_id,
736 last_update_login = hz_utility_pub.last_update_login
737 WHERE (trxnmid = p_from_id);
738
739 ELSIF (p_parent_entity_name = 'IBY_CREDITCARD') THEN
740
741 UPDATE iby_trxn_summaries_all
742 SET payerinstrid = p_to_fk_id,
743 last_update_date = hz_utility_pub.last_update_date,
744 last_updated_by = hz_utility_pub.user_id,
745 last_update_login = hz_utility_pub.last_update_login
746 WHERE (trxnmid = p_from_id)
747 AND (instrtype = 'CREDITCARD');
748
749 END IF;
750
751 iby_debug_pub.add('Exit',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
752 EXCEPTION
753 WHEN OTHERS THEN
754 iby_debug_pub.add('Unexpected error:=' || SQLERRM,
755 iby_debug_pub.G_LEVEL_UNEXPECTED,l_dbg_mod);
756
757 FND_MESSAGE.SET_NAME('IBY', 'IBY_9999');
758 FND_MESSAGE.SET_TOKEN('MESSAGE_TEXT' ,SQLERRM);
759 FND_MSG_PUB.ADD;
760 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
761 END txn_summ_all_merge;
762
763 PROCEDURE doc_payable_merge
764 (p_entity_name IN VARCHAR2,
765 p_from_id IN NUMBER,
766 p_to_id IN OUT NOCOPY NUMBER,
767 p_from_fk_id IN NUMBER,
768 p_to_fk_id IN NUMBER,
769 p_parent_entity_name IN VARCHAR2,
770 p_batch_id IN NUMBER,
771 p_batch_party_id IN NUMBER,
772 x_return_status IN OUT NOCOPY VARCHAR2
773 )
774 IS
775 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.doc_payable_all_merge';
776 BEGIN
777 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
778 x_return_status := FND_API.G_RET_STS_SUCCESS;
779
780 -- transactional entities never merged
781 p_to_id := p_from_id;
782
783 IF (p_from_fk_id = p_to_fk_id) THEN
784 p_to_id := p_from_id;
785 RETURN;
786 END IF;
787
788 IF (p_parent_entity_name = 'HZ_PARTIES') THEN
789
790 UPDATE iby_docs_payable_all
791 SET payee_party_id = p_to_fk_id,
792 last_update_date = hz_utility_pub.last_update_date,
793 last_updated_by = hz_utility_pub.user_id,
794 last_update_login = hz_utility_pub.last_update_login
795 WHERE (document_payable_id = p_from_id)
796 AND (payee_party_id = p_from_fk_id);
797
798 UPDATE iby_docs_payable_all
799 SET beneficiary_party = p_to_fk_id,
800 last_update_date = hz_utility_pub.last_update_date,
801 last_updated_by = hz_utility_pub.user_id,
802 last_update_login = hz_utility_pub.last_update_login
803 WHERE (document_payable_id = p_from_id)
804 AND (beneficiary_party = p_from_fk_id);
805
806 ELSIF (p_parent_entity_name = 'IBY_EXTERNAL_PAYERS_ALL') THEN
807
808 UPDATE iby_docs_payable_all
809 SET ext_payee_id = p_to_fk_id,
810 last_update_date = hz_utility_pub.last_update_date,
811 last_updated_by = hz_utility_pub.user_id,
812 last_update_login = hz_utility_pub.last_update_login
813 WHERE (document_payable_id = p_from_id);
814
815 ELSIF (p_parent_entity_name = 'HZ_PARTY_SITES') THEN
816
817 UPDATE iby_docs_payable_all
821 DECODE(payee_party_id, NULL,payee_party_id,
818 SET party_site_id = p_to_fk_id,
819 -- party site may be transferred to a new party
820 payee_party_id =
822 (SELECT party_id FROM hz_party_sites
823 WHERE party_site_id = p_to_fk_id) ),
824 last_update_date = hz_utility_pub.last_update_date,
825 last_updated_by = hz_utility_pub.user_id,
826 last_update_login = hz_utility_pub.last_update_login
827 WHERE (document_payable_id = p_from_id);
828
829 END IF;
830
831 iby_debug_pub.add('Exit',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
832 EXCEPTION
833 WHEN OTHERS THEN
834 iby_debug_pub.add('Unexpected error:=' || SQLERRM,
835 iby_debug_pub.G_LEVEL_UNEXPECTED,l_dbg_mod);
836
837 FND_MESSAGE.SET_NAME('IBY', 'IBY_9999');
838 FND_MESSAGE.SET_TOKEN('MESSAGE_TEXT' ,SQLERRM);
839 FND_MSG_PUB.ADD;
840 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
841 END doc_payable_merge;
842
843 PROCEDURE payments_all_merge
844 (p_entity_name IN VARCHAR2,
845 p_from_id IN NUMBER,
846 p_to_id IN OUT NOCOPY NUMBER,
847 p_from_fk_id IN NUMBER,
848 p_to_fk_id IN NUMBER,
849 p_parent_entity_name IN VARCHAR2,
850 p_batch_id IN NUMBER,
851 p_batch_party_id IN NUMBER,
852 x_return_status IN OUT NOCOPY VARCHAR2
853 )
854 IS
855 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.payments_all_merge';
856 BEGIN
857 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
858 x_return_status := FND_API.G_RET_STS_SUCCESS;
859
860 -- transactional entities never merged
861 p_to_id := p_from_id;
862
863 IF (p_from_fk_id = p_to_fk_id) THEN
864 p_to_id := p_from_id;
865 RETURN;
866 END IF;
867
868 IF (p_parent_entity_name = 'HZ_PARTIES') THEN
869
870 UPDATE iby_payments_all
871 SET payee_party_id = p_to_fk_id,
872 last_update_date = hz_utility_pub.last_update_date,
873 last_updated_by = hz_utility_pub.user_id,
874 last_update_login = hz_utility_pub.last_update_login
875 WHERE (payment_id = p_from_id)
876 AND (payee_party_id = p_from_fk_id);
877
878 UPDATE iby_payments_all
879 SET beneficiary_party = p_to_fk_id,
880 last_update_date = hz_utility_pub.last_update_date,
881 last_updated_by = hz_utility_pub.user_id,
882 last_update_login = hz_utility_pub.last_update_login
883 WHERE (payment_id = p_from_id)
884 AND (beneficiary_party = p_from_fk_id);
885
886 UPDATE iby_payments_all
887 SET payer_party_id = p_to_fk_id,
888 last_update_date = hz_utility_pub.last_update_date,
889 last_updated_by = hz_utility_pub.user_id,
890 last_update_login = hz_utility_pub.last_update_login
891 WHERE (payment_id = p_from_id)
892 AND (payer_party_id = p_from_fk_id);
893
894 ELSIF (p_parent_entity_name = 'IBY_EXTERNAL_PAYERS_ALL') THEN
895
896 UPDATE iby_payments_all
897 SET ext_payee_id = p_to_fk_id,
898 last_update_date = hz_utility_pub.last_update_date,
899 last_updated_by = hz_utility_pub.user_id,
900 last_update_login = hz_utility_pub.last_update_login
901 WHERE (payment_id = p_from_id);
902
903 ELSIF (p_parent_entity_name = 'HZ_PARTY_SITES') THEN
904
905 UPDATE iby_payments_all
906 SET party_site_id = p_to_fk_id,
907 -- party site may be transferred to a new party
908 payee_party_id =
909 DECODE(payee_party_id, NULL,payee_party_id,
910 (SELECT party_id FROM hz_party_sites
911 WHERE party_site_id = p_to_fk_id) ),
912 last_update_date = hz_utility_pub.last_update_date,
913 last_updated_by = hz_utility_pub.user_id,
914 last_update_login = hz_utility_pub.last_update_login
915 WHERE (payment_id = p_from_id);
916
917 END IF;
918
919 iby_debug_pub.add('Exit',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
920 EXCEPTION
921 WHEN OTHERS THEN
922 iby_debug_pub.add('Unexpected error:=' || SQLERRM,
923 iby_debug_pub.G_LEVEL_UNEXPECTED,l_dbg_mod);
924
925 FND_MESSAGE.SET_NAME('IBY', 'IBY_9999');
926 FND_MESSAGE.SET_TOKEN('MESSAGE_TEXT' ,SQLERRM);
927 FND_MSG_PUB.ADD;
928 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
929 END payments_all_merge;
930
931 END IBY_HZ_UNIFICATION_PKG;
932