eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.IBY_PARTY_MERGE

Source


1 PACKAGE BODY IBY_PARTY_MERGE AS
2 /* $Header: ibyptymergb.pls 120.0.12010000.5 2010/03/24 11:16:01 sgogula noship $ */
3 
4   G_DEBUG_MODULE CONSTANT VARCHAR2(100) := 'iby.plsql.IBY_PARTY_MERGE';
5 
6 
7   PROCEDURE acct_owner_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_account_owners.account_owner_party_id%TYPE := NULL;
20      l_merge_reason   VARCHAR2(30);
21      l_dbg_mod        VARCHAR2(100) := G_DEBUG_MODULE || '.acct_owner_merge';
22      g_mesg           VARCHAR2(1000) := '';
23 
24     CURSOR c_dupeba
25     (ci_owner_id IN iby_account_owners.account_owner_id%TYPE,
26      ci_party_id IN iby_account_owners.account_owner_party_id%TYPE)
27     IS
28       SELECT account_owner_id
29       FROM iby_account_owners mto,
30         ( SELECT ext_bank_account_id
31           FROM iby_account_owners
32 	  WHERE (account_owner_id = ci_owner_id) ) mfrom
33       WHERE mto.account_owner_party_id = ci_party_id
34       AND mto.ext_bank_account_id = mfrom.ext_bank_account_id;
35 
36    BEGIN
37 
38      g_mesg := 'Enter';
39      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
40 
41      g_mesg := 'p_entity_name' || p_entity_name;
42      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
43      g_mesg := 'p_from_id' || p_from_id;
44      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
45      g_mesg := 'p_to_id' || p_to_id;
46      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
47      g_mesg := 'p_from_fk_id' || p_from_fk_id;
48      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
49      g_mesg := 'p_to_fk_id' || p_to_fk_id;
50      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
51      g_mesg := 'p_parent_entity_name' || p_parent_entity_name;
52      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
53      g_mesg := 'p_batch_id' || p_batch_id;
54      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
55 
56      x_return_status := FND_API.G_RET_STS_SUCCESS;
57 
58      IF c_dupeba%ISOPEN THEN CLOSE c_dupeba; END IF;
59 
60      IF (p_from_fk_id = p_to_fk_id) THEN
61        p_to_id := p_from_id;
62        RETURN;
63      END IF;
64 
65      SELECT merge_reason_code
66      INTO l_merge_reason
67      FROM hz_merge_batch
68      WHERE batch_id = p_batch_id;
69 
70    IF (p_parent_entity_name = 'HZ_PARTIES') THEN
71 
72      IF ( (l_merge_reason <> 'DUPLICATE') AND
73           (l_merge_reason <> 'MERGER') AND
74           (l_merge_reason <> 'DEDUPE') AND
75           (l_merge_reason <> 'DUPLICATE_RELN_PARTY') AND
76           (l_merge_reason <> 'De-duplication Merge') )
77      THEN
78        g_mesg := 'cannot unify non-duplicate bank account owners';
79        fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
80 
81        fnd_message.set_name('IBY','IBY_PARTY_UNIFY_BA_VETO');
82        fnd_msg_pub.ADD;
83        x_return_status := fnd_api.g_ret_sts_error;
84        RETURN;
85      END IF;
86 
87      OPEN c_dupeba(p_from_id,p_to_fk_id);
88      FETCH c_dupeba INTO l_dup_id;
89      CLOSE c_dupeba;
90 
91      g_mesg := 'duplicate account owner:='|| l_dup_id;
92      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
93 
94      UPDATE iby_account_owners
95      SET account_owner_party_id = DECODE(l_dup_id, NULL,p_to_fk_id, account_owner_party_id),
96        end_date = DECODE(l_dup_id, NULL,end_date, SYSDATE),
97        last_update_date = hz_utility_pub.last_update_date,
98        last_updated_by = hz_utility_pub.user_id,
99        last_update_login = hz_utility_pub.last_update_login
100      WHERE (account_owner_id = p_from_id);
101 
102      IF (l_dup_id IS NULL) THEN
103        p_to_id := p_from_id;
104      ELSE
105        p_to_id := l_dup_id;
106      END IF;
107 
108    END IF;
109      g_mesg := 'Exit';
110      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
111 
112      EXCEPTION
113      WHEN OTHERS THEN
114 
115      g_mesg := 'Unexpected error:=' || SQLERRM;
116      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
117 
118      FND_MESSAGE.SET_NAME('IBY', 'IBY_9999');
119      FND_MESSAGE.SET_TOKEN('MESSAGE_TEXT' ,SQLERRM);
120      FND_MSG_PUB.ADD;
121      x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
122    END acct_owner_merge;
123 
124 
125 
126   PROCEDURE credit_card_merge
127   (p_entity_name   IN     VARCHAR2,
128    p_from_id       IN     NUMBER,
129    p_to_id         IN     OUT NOCOPY NUMBER,
130    p_from_fk_id    IN     NUMBER,
131    p_to_fk_id      IN     NUMBER,
132    p_parent_entity_name IN VARCHAR2,
133    p_batch_id      IN     NUMBER,
134    p_batch_party_id IN    NUMBER,
135    x_return_status IN     OUT NOCOPY VARCHAR2
136    )
137    IS
138 
139     CURSOR c_dupecc
140     (ci_instr_id IN iby_creditcard.instrid%TYPE,
141      ci_owner_id IN iby_creditcard.card_owner_id%TYPE)
142     IS
143       SELECT instrid
144       FROM iby_creditcard mto,
145         (SELECT cc_number_hash1, cc_number_hash2
146          FROM iby_creditcard WHERE (instrid = ci_instr_id)) mfrom
147       WHERE
148         mto.card_owner_id = ci_owner_id
149         AND mto.cc_number_hash1 = mfrom.cc_number_hash1
150         AND mto.cc_number_hash2 = mfrom.cc_number_hash2
151         AND NVL(mto.active_flag,'Y') = 'Y'
152         AND (NVL(mto.inactive_date,SYSDATE+10) > SYSDATE);
153 
154     l_merge_reason    VARCHAR2(30);
155     l_dup_id          iby_creditcard.instrid%TYPE := NULL;
156     l_dbg_mod         VARCHAR2(100) := G_DEBUG_MODULE || '.credit_card_merge';
157     g_mesg           VARCHAR2(1000) := '';
158 
159    BEGIN
160 
161      g_mesg := 'Enter';
162      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
163 
164      g_mesg := 'p_entity_name' || p_entity_name;
165      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
166      g_mesg := 'p_from_id' || p_from_id;
167      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
168      g_mesg := 'p_to_id' || p_to_id;
169      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
170      g_mesg := 'p_from_fk_id' || p_from_fk_id;
171      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
172      g_mesg := 'p_to_fk_id' || p_to_fk_id;
173      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
174      g_mesg := 'p_parent_entity_name' || p_parent_entity_name;
175      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
176      g_mesg := 'p_batch_id' || p_batch_id;
177      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
178 
179      x_return_status := FND_API.G_RET_STS_SUCCESS;
180 
181      IF (c_dupecc%ISOPEN) THEN CLOSE c_dupecc; END IF;
182 
183      SELECT merge_reason_code
184      INTO l_merge_reason
185      FROM hz_merge_batch
186      WHERE batch_id = p_batch_id;
187 
188      IF (p_from_fk_id = p_to_fk_id) THEN
189        p_to_id := p_from_id;
190        RETURN;
191      END IF;
192 
193      IF (p_parent_entity_name = 'HZ_PARTIES') THEN
194 
195      IF ( (l_merge_reason <> 'DUPLICATE') AND
196           (l_merge_reason <> 'MERGER') AND
197           (l_merge_reason <> 'DEDUPE') AND
198           (l_merge_reason <> 'DUPLICATE_RELN_PARTY') AND
199           (l_merge_reason <> 'De-duplication Merge') )
200       THEN
201 	  g_mesg :='cannot unify non-duplicate credit cards';
202 	  fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
203 
204           fnd_message.set_name('IBY','IBY_PARTY_UNIFY_CC_VETO');
205           fnd_msg_pub.ADD;
206          x_return_status := fnd_api.g_ret_sts_error;
207 	  RETURN;
208        END IF;
209 
210 
211        OPEN c_dupecc(p_from_id,p_to_fk_id);
212        FETCH c_dupecc INTO l_dup_id;
213        CLOSE c_dupecc;
214 
215        g_mesg :='duplicate card:='|| l_dup_id;
216        fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
217 
218        UPDATE iby_creditcard
219        SET card_owner_id = DECODE(l_dup_id, NULL,p_to_fk_id, card_owner_id),
220          active_flag = DECODE(l_dup_id, NULL,active_flag, 'N'),
221          inactive_date = DECODE(l_dup_id, NULL,inactive_date, SYSDATE),
222          last_update_date = hz_utility_pub.last_update_date,
223          last_updated_by = hz_utility_pub.user_id,
224          last_update_login = hz_utility_pub.last_update_login,
225          request_id =  hz_utility_pub.request_id,
226          program_application_id = hz_utility_pub.program_application_id,
227          program_id = hz_utility_pub.program_id,
228          program_update_date = sysdate
229        WHERE (instrid = p_from_id);
230 
231        p_to_id := NVL(l_dup_id,p_from_id);
232 
233      ELSIF (p_parent_entity_name = 'HZ_PARTY_SITE_USES') THEN
234 
235        UPDATE iby_creditcard
236        SET addressid = p_to_fk_id,
237          last_update_date = hz_utility_pub.last_update_date,
238          last_updated_by = hz_utility_pub.user_id,
239          last_update_login = hz_utility_pub.last_update_login,
240          request_id =  hz_utility_pub.request_id,
241          program_application_id = hz_utility_pub.program_application_id,
242          program_id = hz_utility_pub.program_id,
243          program_update_date = sysdate
244        WHERE (instrid = p_from_id);
245 
246        p_to_id := p_from_id;
247 
248      END IF;
249 
250      g_mesg :='Exit';
251      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
252 
253    EXCEPTION
254      WHEN OTHERS THEN
255 
256      g_mesg :='Unexpected error:=' || SQLERRM;
257      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
258 
259      FND_MESSAGE.SET_NAME('IBY', 'IBY_9999');
260      FND_MESSAGE.SET_TOKEN('MESSAGE_TEXT' ,SQLERRM);
261      FND_MSG_PUB.ADD;
262      x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
263 
264    END credit_card_merge;
265 
266 
267   PROCEDURE external_payer_merge
268   (p_entity_name   IN     VARCHAR2,
269    p_from_id       IN     NUMBER,
270    p_to_id         IN     OUT NOCOPY NUMBER,
271    p_from_fk_id    IN     NUMBER,
272    p_to_fk_id      IN     NUMBER,
273    p_parent_entity_name IN VARCHAR2,
274    p_batch_id      IN     NUMBER,
275    p_batch_party_id IN    NUMBER,
276    x_return_status IN     OUT NOCOPY VARCHAR2
277    )
278   IS
279 
280     CURSOR c_dupepayer
281     (ci_payer_id IN iby_external_payers_all.ext_payer_id%TYPE,
282      ci_party_id IN iby_external_payers_all.party_id%TYPE)
283     IS
284       SELECT ext_payer_id
285       FROM iby_external_payers_all mto,
286         (SELECT payment_function,ci_party_id,cust_account_id,acct_site_use_id,
287            org_type,org_id
288          FROM iby_external_payers_all
289          WHERE (ext_payer_id = ci_payer_id)) mfrom
290       WHERE mto.ext_payer_id <> ci_payer_id
291         AND mto.payment_function = mfrom.payment_function
292         AND mto.party_id = ci_party_id
293         AND NVL(mto.cust_account_id,-99) = NVL(mfrom.cust_account_id,-99)
294         AND NVL(mto.acct_site_use_id,-99) = NVL(mfrom.acct_site_use_id,-99)
295         AND NVL(mto.org_type,'!') = NVL(mfrom.org_type,'!')
296         AND NVL(mto.org_id,-99) = NVL(mfrom.org_id,-99);
297 
298     l_dbg_mod         VARCHAR2(100) := G_DEBUG_MODULE || '.external_payer_merge';
299     l_dup_id          iby_external_payers_all.ext_payer_id%TYPE;
300     g_mesg            VARCHAR2(1000) := '';
301 
302   BEGIN
303 
304      g_mesg := 'Enter';
305      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
306 
307      g_mesg := 'p_entity_name' || p_entity_name;
308      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
309      g_mesg := 'p_from_id' || p_from_id;
310      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
311      g_mesg := 'p_to_id' || p_to_id;
312      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
313      g_mesg := 'p_from_fk_id' || p_from_fk_id;
314      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
315      g_mesg := 'p_to_fk_id' || p_to_fk_id;
316      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
317      g_mesg := 'p_parent_entity_name' || p_parent_entity_name;
318      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
319      g_mesg := 'p_batch_id' || p_batch_id;
320      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
321 
322      x_return_status := FND_API.G_RET_STS_SUCCESS;
323 
324      IF (c_dupepayer%ISOPEN) THEN CLOSE c_dupepayer; END IF;
325 
326      IF (p_parent_entity_name = 'HZ_PARTIES') THEN
327 
328        IF (p_from_fk_id = p_to_fk_id) THEN
329          p_to_id := p_from_id;
330          RETURN;
331        END IF;
332 
333        OPEN c_dupepayer(p_from_id,p_to_fk_id);
334        FETCH c_dupepayer INTO l_dup_id;
335        CLOSE c_dupepayer;
336 
337        g_mesg := 'duplicate payer:='|| l_dup_id;
338        fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
339 
340        IF (l_dup_id IS NULL) THEN
341          UPDATE iby_external_payers_all
342          SET party_id = p_to_fk_id,
343            last_update_date = hz_utility_pub.last_update_date,
344            last_updated_by = hz_utility_pub.user_id,
345            last_update_login = hz_utility_pub.last_update_login
346          WHERE ext_payer_id = p_from_id;
347          p_to_id := p_from_id;
348        ELSE
349          p_to_id := l_dup_id;
350        END IF;
351      END IF;
352 
353      g_mesg := 'Exit';
354      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
355 
356    EXCEPTION
357      WHEN OTHERS THEN
358 
359        g_mesg := 'Unexpected error:=' || SQLERRM;
360        fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
361 
362        FND_MESSAGE.SET_NAME('IBY', 'IBY_9999');
363        FND_MESSAGE.SET_TOKEN('MESSAGE_TEXT' ,SQLERRM);
364        FND_MSG_PUB.ADD;
365        x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
366 
367   END external_payer_merge;
368 
369   PROCEDURE pmt_instrument_use_merge
370   (p_entity_name   IN     VARCHAR2,
371    p_from_id       IN     NUMBER,
372    p_to_id         IN     OUT NOCOPY NUMBER,
373    p_from_fk_id    IN     NUMBER,
374    p_to_fk_id      IN     NUMBER,
375    p_parent_entity_name IN VARCHAR2,
376    p_batch_id      IN     NUMBER,
377    p_batch_party_id IN    NUMBER,
378    x_return_status IN     OUT NOCOPY VARCHAR2
379    )
380    IS
381 
382      l_dbg_mod   VARCHAR2(100) := G_DEBUG_MODULE || '.pmt_instrument_use_merge';
383      l_dup_id    iby_pmt_instr_uses_all.instrument_payment_use_id%TYPE := NULL;
384      l_flow_type iby_pmt_instr_uses_all.payment_flow%TYPE;
385      g_mesg           VARCHAR2(1000) := '';
386 
387      CURSOR c_dupinstr
388     (ci_use_id   IN iby_pmt_instr_uses_all.instrument_payment_use_id%TYPE,
389      ci_instr_id IN iby_pmt_instr_uses_all.instrument_id%TYPE,
390      ci_instr_type IN iby_pmt_instr_uses_all.instrument_type%TYPE)
391      IS
392       SELECT mto.instrument_payment_use_id
393       FROM iby_pmt_instr_uses_all mto,
394         (SELECT ext_pmt_party_id,payment_flow
395          FROM iby_pmt_instr_uses_all
396          WHERE instrument_payment_use_id = ci_use_id) mfrom
397       WHERE
398          mto.instrument_payment_use_id <> ci_use_id
399          AND mto.payment_flow = mfrom.payment_flow
400          AND mto.ext_pmt_party_id = mfrom.ext_pmt_party_id
401          AND mto.instrument_type = ci_instr_type
402          AND mto.instrument_id = ci_instr_id;
403 
404      CURSOR c_dupeparty
405     (ci_use_id   IN iby_pmt_instr_uses_all.instrument_payment_use_id%TYPE,
406      ci_party_id IN iby_pmt_instr_uses_all.ext_pmt_party_id%TYPE,
407      ci_flow_type IN iby_pmt_instr_uses_all.payment_flow%TYPE)
408     IS
409       SELECT mto.instrument_payment_use_id
410       FROM iby_pmt_instr_uses_all mto,
411         (SELECT instrument_type,instrument_id
412          FROM iby_pmt_instr_uses_all
413          WHERE instrument_payment_use_id = ci_use_id) mfrom
414       WHERE
415          mto.instrument_payment_use_id <> ci_use_id
416          AND mto.payment_flow = ci_flow_type
417          AND mto.ext_pmt_party_id = ci_party_id
418          AND mto.instrument_type = mfrom.instrument_type
419          AND mto.instrument_id = mfrom.instrument_id;
420 
421    BEGIN
422 
423      g_mesg := 'Enter';
424      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
425 
426      g_mesg := 'p_entity_name' || p_entity_name;
427      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
428      g_mesg := 'p_from_id' || p_from_id;
429      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
430      g_mesg := 'p_to_id' || p_to_id;
431      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
432      g_mesg := 'p_from_fk_id' || p_from_fk_id;
433      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
434      g_mesg := 'p_to_fk_id' || p_to_fk_id;
435      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
436      g_mesg := 'p_parent_entity_name' || p_parent_entity_name;
437      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
438      g_mesg := 'p_batch_id' || p_batch_id;
439      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
440 
441      x_return_status := FND_API.G_RET_STS_SUCCESS;
442 
443      IF (c_dupinstr%ISOPEN) THEN CLOSE c_dupinstr; END IF;
444      IF (c_dupeparty%ISOPEN) THEN CLOSE c_dupeparty; END IF;
445 
446      IF (p_parent_entity_name = 'IBY_CREDITCARD') THEN
447 
448        IF (p_from_fk_id = p_to_fk_id) THEN
449          p_to_id := p_from_id;
450          RETURN;
451        END IF;
452 
453        OPEN c_dupinstr(p_from_id,p_to_fk_id,'CREDITCARD');
454        FETCH c_dupinstr INTO l_dup_id;
455        CLOSE c_dupinstr;
456 
457        g_mesg := 'duplicate instr use:='|| l_dup_id;
458        fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
459 
460        UPDATE iby_pmt_instr_uses_all
461        SET instrument_id = DECODE(l_dup_id, NULL,p_to_fk_id, instrument_id),
462          end_date = DECODE(l_dup_id, NULL,end_date, SYSDATE),
463          last_update_date = hz_utility_pub.last_update_date,
464          last_updated_by = hz_utility_pub.user_id,
465          last_update_login = hz_utility_pub.last_update_login
466        WHERE (instrument_payment_use_id = p_from_id);
467 
468        IF (l_dup_id IS NULL) THEN
469          p_to_id := p_from_id;
470        ELSE
471          p_to_id := l_dup_id;
472        END IF;
473 
474      ELSIF (p_parent_entity_name = 'IBY_EXTERNAL_PAYERS_ALL')
475      THEN
476 
477        IF (p_from_fk_id = p_to_fk_id) THEN
478          p_to_id := p_from_id;
479          RETURN;
480        END IF;
481 
482        l_flow_type := 'FUNDS_CAPTURE';
483 
484        OPEN c_dupeparty(p_from_id,p_to_fk_id,l_flow_type);
485        FETCH c_dupeparty INTO l_dup_id;
486        CLOSE c_dupeparty;
487 
488        g_mesg := 'duplicate instr use:='|| l_dup_id;
489        fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
490 
491        UPDATE iby_pmt_instr_uses_all
492        SET ext_pmt_party_id = DECODE(l_dup_id, NULL,p_to_fk_id, ext_pmt_party_id),
493          end_date = DECODE(l_dup_id, NULL,end_date, SYSDATE),
494          last_update_date = hz_utility_pub.last_update_date,
495          last_updated_by = hz_utility_pub.user_id,
496          last_update_login = hz_utility_pub.last_update_login
497        WHERE (instrument_payment_use_id = p_from_id);
498 
499        IF (l_dup_id IS NULL) THEN
500          p_to_id := p_from_id;
501        ELSE
502          p_to_id := l_dup_id;
503        END IF;
504 
505      END IF;
506 
507      g_mesg := 'Exit';
508      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
509 
510    EXCEPTION
511      WHEN OTHERS THEN
512        g_mesg := 'Unexpected error:=' || SQLERRM;
513        fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
514 
515        FND_MESSAGE.SET_NAME('IBY', 'IBY_9999');
516        FND_MESSAGE.SET_TOKEN('MESSAGE_TEXT' ,SQLERRM);
517        FND_MSG_PUB.ADD;
518        x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
519 
520    END pmt_instrument_use_merge;
521 
522 
523   PROCEDURE party_pmt_methods_merge
524   (p_entity_name   IN     VARCHAR2,
525    p_from_id       IN     NUMBER,
526    p_to_id         IN     OUT NOCOPY NUMBER,
527    p_from_fk_id    IN     NUMBER,
528    p_to_fk_id      IN     NUMBER,
529    p_parent_entity_name IN VARCHAR2,
530    p_batch_id      IN     NUMBER,
531    p_batch_party_id IN    NUMBER,
532    x_return_status IN     OUT NOCOPY VARCHAR2
533    )
534    IS
535      l_dbg_mod        VARCHAR2(100) := G_DEBUG_MODULE || '.party_pmt_methods_merge';
536      l_dup_id         iby_ext_party_pmt_mthds.ext_party_pmt_mthd_id%TYPE := NULL;
537      l_flow_type      iby_ext_party_pmt_mthds.payment_flow%TYPE;
538      g_mesg           VARCHAR2(1000) := '';
539 
540      CURSOR c_dupemth
541      (ci_mth_id   IN iby_ext_party_pmt_mthds.ext_party_pmt_mthd_id%TYPE,
542       ci_party_id IN iby_ext_party_pmt_mthds.ext_pmt_party_id%TYPE,
543       ci_flow_type IN iby_ext_party_pmt_mthds.payment_flow%TYPE)
544      IS
545        SELECT ext_party_pmt_mthd_id
546        FROM iby_ext_party_pmt_mthds mto,
547          (SELECT payment_method_code
548           FROM iby_ext_party_pmt_mthds
549           WHERE (ext_party_pmt_mthd_id = ci_mth_id)) mfrom
550        WHERE
551          mto.ext_pmt_party_id = ci_party_id
552          AND mto.payment_flow = ci_flow_type
553          AND mto.payment_method_code = mfrom.payment_method_code;
554 
555 
556    BEGIN
557 
558      g_mesg := 'Enter';
559      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
560 
561      g_mesg := 'p_entity_name' || p_entity_name;
562      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
563      g_mesg := 'p_from_id' || p_from_id;
564      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
565      g_mesg := 'p_to_id' || p_to_id;
566      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
567      g_mesg := 'p_from_fk_id' || p_from_fk_id;
568      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
569      g_mesg := 'p_to_fk_id' || p_to_fk_id;
570      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
571      g_mesg := 'p_parent_entity_name' || p_parent_entity_name;
572      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
573      g_mesg := 'p_batch_id' || p_batch_id;
574      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
575 
576      x_return_status := FND_API.G_RET_STS_SUCCESS;
577 
578      IF (p_from_fk_id = p_to_fk_id) THEN
579        p_to_id := p_from_id;
580        RETURN;
581      END IF;
582 
583      IF (p_parent_entity_name = 'IBY_EXTERNAL_PAYERS_ALL') THEN
584        l_flow_type := 'FUNDS_CAPTURE';
585 
586        OPEN c_dupemth(p_from_id,p_to_fk_id,l_flow_type);
587        FETCH c_dupemth INTO l_dup_id;
588        CLOSE c_dupemth;
589 
590        g_mesg := 'duplicate pmt method:='|| l_dup_id;
591        fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
592 
593        UPDATE iby_ext_party_pmt_mthds
594        SET ext_pmt_party_id = DECODE(l_dup_id, NULL,p_to_fk_id, ext_pmt_party_id),
595          inactive_date = DECODE(l_dup_id, NULL,inactive_date, SYSDATE),
596          last_update_date = hz_utility_pub.last_update_date,
597          last_updated_by = hz_utility_pub.user_id,
598          last_update_login = hz_utility_pub.last_update_login
599        WHERE (ext_party_pmt_mthd_id = p_from_id);
600 
601        IF (l_dup_id IS NULL) THEN
602          p_to_id := p_from_id;
603        ELSE
604          p_to_id := l_dup_id;
605        END IF;
606      END IF;
607 
608      g_mesg := 'Exit';
609      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
610 
611    EXCEPTION
612      WHEN OTHERS THEN
613        g_mesg := 'Unexpected error:=' || SQLERRM;
614        fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
615 
616        FND_MESSAGE.SET_NAME('IBY', 'IBY_9999');
617        FND_MESSAGE.SET_TOKEN('MESSAGE_TEXT' ,SQLERRM);
618        FND_MSG_PUB.ADD;
619        x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
620 
621    END party_pmt_methods_merge;
622 
623   PROCEDURE fc_tx_extensions_merge
624   (p_entity_name   IN     VARCHAR2,
625    p_from_id       IN     NUMBER,
626    p_to_id         IN     OUT NOCOPY 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
633    )
634    IS
635      l_dbg_mod        VARCHAR2(100) := G_DEBUG_MODULE || '.fc_tx_extensions_merge';
636      g_mesg           VARCHAR2(1000) := '';
637 
638    BEGIN
639      g_mesg := 'Enter';
640      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
641 
642      g_mesg := 'p_entity_name' || p_entity_name;
643      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
644      g_mesg := 'p_from_id' || p_from_id;
645      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
646      g_mesg := 'p_to_id' || p_to_id;
647      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
648      g_mesg := 'p_from_fk_id' || p_from_fk_id;
649      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
650      g_mesg := 'p_to_fk_id' || p_to_fk_id;
651      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
652      g_mesg := 'p_parent_entity_name' || p_parent_entity_name;
653      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
654      g_mesg := 'p_batch_id' || p_batch_id;
655      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
656 
657      x_return_status := FND_API.G_RET_STS_SUCCESS;
658 
659      -- extension is a transactional entity,it is never merged
660      p_to_id := p_from_id;
661 
662      IF (p_from_fk_id = p_to_fk_id) THEN
663        p_to_id := p_from_id;
664        RETURN;
665      END IF;
666 
667      IF (p_parent_entity_name = 'IBY_EXTERNAL_PAYERS_ALL') THEN
668 
669        UPDATE iby_fndcpt_tx_extensions
670        SET ext_payer_id = p_to_fk_id,
671          last_update_date = hz_utility_pub.last_update_date,
672          last_updated_by = hz_utility_pub.user_id,
673          last_update_login = hz_utility_pub.last_update_login
674        WHERE (trxn_extension_id = p_from_id);
675 
676      ELSIF (p_parent_entity_name = 'IBY_PMT_INSTR_USES_ALL') THEN
677 
678        UPDATE iby_fndcpt_tx_extensions
679        SET instr_assignment_id = p_to_fk_id,
680          last_update_date = hz_utility_pub.last_update_date,
681          last_updated_by = hz_utility_pub.user_id,
682          last_update_login = hz_utility_pub.last_update_login
683        WHERE (trxn_extension_id = p_from_id);
684 
685      END IF;
686 
687      g_mesg := 'Exit';
688      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
689 
690    EXCEPTION
691      WHEN OTHERS THEN
692        g_mesg := 'Unexpected error:=' || SQLERRM;
693        fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
694 
695        FND_MESSAGE.SET_NAME('IBY', 'IBY_9999');
696        FND_MESSAGE.SET_TOKEN('MESSAGE_TEXT' ,SQLERRM);
697        FND_MSG_PUB.ADD;
698        x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
699    END fc_tx_extensions_merge;
700 
701 
702   PROCEDURE txn_summ_all_merge
703   (p_entity_name   IN     VARCHAR2,
704    p_from_id       IN     NUMBER,
705    p_to_id         IN     OUT NOCOPY NUMBER,
706    p_from_fk_id    IN     NUMBER,
707    p_to_fk_id      IN     NUMBER,
708    p_parent_entity_name IN VARCHAR2,
709    p_batch_id      IN     NUMBER,
710    p_batch_party_id IN    NUMBER,
711    x_return_status IN     OUT NOCOPY VARCHAR2
712    )
713    IS
714      l_dbg_mod        VARCHAR2(100) := G_DEBUG_MODULE || '.txn_summ_all_merge';
715      g_mesg           VARCHAR2(1000) := '';
716 
717    BEGIN
718      g_mesg := 'Enter';
719      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
720 
721      g_mesg := 'p_entity_name' || p_entity_name;
722      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
723      g_mesg := 'p_from_id' || p_from_id;
724      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
725      g_mesg := 'p_to_id' || p_to_id;
726      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
727      g_mesg := 'p_from_fk_id' || p_from_fk_id;
728      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
729      g_mesg := 'p_to_fk_id' || p_to_fk_id;
730      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
731      g_mesg := 'p_parent_entity_name' || p_parent_entity_name;
732      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
733      g_mesg := 'p_batch_id' || p_batch_id;
734      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
735 
736      x_return_status := FND_API.G_RET_STS_SUCCESS;
737 
738      -- summaries is a transactional entity,it is never merged
739      p_to_id := p_from_id;
740 
741      IF (p_from_fk_id = p_to_fk_id) THEN
742        p_to_id := p_from_id;
743        RETURN;
744      END IF;
745 
746      IF (p_parent_entity_name = 'HZ_PARTIES') THEN
747 
748        UPDATE iby_trxn_summaries_all
749        SET payerid = p_to_fk_id,
750          last_update_date = hz_utility_pub.last_update_date,
751          last_updated_by = hz_utility_pub.user_id,
752          last_update_login = hz_utility_pub.last_update_login
753        WHERE (trxnmid = p_from_id);
754 
755      ELSIF (p_parent_entity_name = 'IBY_PMT_INSTR_USES_ALL') THEN
756 
757        UPDATE iby_trxn_summaries_all
758        SET payer_instr_assignment_id = p_to_fk_id,
759          last_update_date = hz_utility_pub.last_update_date,
760          last_updated_by = hz_utility_pub.user_id,
761          last_update_login = hz_utility_pub.last_update_login
762        WHERE (trxnmid = p_from_id);
763 
764      ELSIF (p_parent_entity_name = 'IBY_CREDITCARD') THEN
765 
766        UPDATE iby_trxn_summaries_all
767        SET payerinstrid = p_to_fk_id,
768          last_update_date = hz_utility_pub.last_update_date,
769          last_updated_by = hz_utility_pub.user_id,
770          last_update_login = hz_utility_pub.last_update_login
771        WHERE (trxnmid = p_from_id)
772          AND (instrtype = 'CREDITCARD');
773 
774      END IF;
775 
776      g_mesg := 'Exit';
777      fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
778 
779    EXCEPTION
780      WHEN OTHERS THEN
781        g_mesg := 'Unexpected error:=' || SQLERRM;
782        fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
783 
784        FND_MESSAGE.SET_NAME('IBY', 'IBY_9999');
785        FND_MESSAGE.SET_TOKEN('MESSAGE_TEXT' ,SQLERRM);
786        FND_MSG_PUB.ADD;
787        x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
788 
789    END txn_summ_all_merge;
790 
791 
792 END IBY_PARTY_MERGE;
793