1 PACKAGE BODY IBY_MERGE AS
2 /*$Header: IBYMERGB.pls 120.4.12020000.5 2013/03/15 09:31:30 sgogula ship $ */
3
4 /*-------------------------------------------------------------
5 | FUNCTION
6 | GET_EXTERNAL_PAYER_ID
7 | RETURN NUMBER
8 | DESCRIPTION :
9 | FUNCTION TO RETURN A EXTERNAL PAYER ID
10 | FOR A GIVEN CUST ACCOUNT ID AND ACCOUNT SITE USE ID
11 |--------------------------------------------------------------*/
12
13 FUNCTION GET_EXTERNAL_PAYER_ID
14 (
15 account_id iby_external_payers_all.cust_account_id%TYPE,
16 account_site_use_id iby_external_payers_all.acct_site_use_id%TYPE
17 ) RETURN NUMBER IS
18 ext_payer_id iby_external_payers_all.ext_payer_id%TYPE;
19 BEGIN
20 OPEN cur_get_ext_payer_id(account_id,account_site_use_id);
21 FETCH cur_get_ext_payer_id INTO ext_payer_id;
22 CLOSE cur_get_ext_payer_id;
23 RETURN ext_payer_id;
24 END GET_EXTERNAL_PAYER_ID;
25
26 /*-------------------------------------------------------------
27 |
28 | PROCEDURE
29 | TRX_SUMMARY_MERGE
30 | DESCRIPTION :
31 | Account merge procedure for the table, TRX_SUMMARY_MERGE
32 |--------------------------------------------------------------*/
33 PROCEDURE TRX_SUMMARY_MERGE (
34 req_id NUMBER,
35 set_num NUMBER,
36 process_mode VARCHAR2) IS
37
38 TYPE merge_header_id_list_type IS TABLE OF
39 ra_customer_merge_headers.customer_merge_header_id%TYPE
40 INDEX BY BINARY_INTEGER;
41 merge_header_id_list merge_header_id_list_type;
42
43 TYPE trxnmid_list_type IS TABLE OF
44 iby_trxn_summaries_all.trxnmid%TYPE
45 INDEX BY BINARY_INTEGER;
46 primary_key_id_list trxnmid_list_type;
47
48 TYPE cust_account_id_list_type IS TABLE OF
49 iby_trxn_summaries_all.cust_account_id%TYPE
50 INDEX BY BINARY_INTEGER;
51 num_col1_orig_list cust_account_id_list_type;
52 num_col1_new_list cust_account_id_list_type;
53
54 TYPE acct_site_id_list_type IS TABLE OF
55 iby_trxn_summaries_all.acct_site_id%TYPE
56 INDEX BY BINARY_INTEGER;
57 num_col2_orig_list acct_site_id_list_type;
58 num_col2_new_list acct_site_id_list_type;
59
60 TYPE acct_site_use_id_list_type IS TABLE OF
61 iby_trxn_summaries_all.acct_site_use_id%TYPE
62 INDEX BY BINARY_INTEGER;
63 num_col3_orig_list acct_site_use_id_list_type;
64 num_col3_new_list acct_site_use_id_list_type;
65
66 ext_payer_from_list ext_payer_id_list_type;
67 ext_payer_to_list ext_payer_id_list_type;
68
69 l_profile_val VARCHAR2(30);
70 CURSOR merged_records IS
71 SELECT DISTINCT customer_merge_header_id
72 ,trxnmid
73 ,cust_account_id
74 ,acct_site_id
75 ,acct_site_use_id
76 FROM iby_trxn_summaries_all yt, ra_customer_merges m
77 WHERE (
78 yt.cust_account_id = m.duplicate_id
79 OR yt.acct_site_id = m.duplicate_address_id
80 OR yt.acct_site_use_id = m.duplicate_site_id
81 ) AND m.process_flag = 'N'
82 AND m.request_id = req_id
83 AND m.set_number = set_num;
84 l_last_fetch BOOLEAN := FALSE;
85 l_count NUMBER;
86 BEGIN
87 IF process_mode='LOCK' THEN
88 NULL;
89 ELSE
90 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
91 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','IBY_TRXN_SUMMARIES_ALL',FALSE);
92 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
93 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
94
95 open merged_records;
96 LOOP
97 FETCH merged_records BULK COLLECT INTO
98 merge_header_id_list
99 , primary_key_id_list
100 , num_col1_orig_list
101 , num_col2_orig_list
102 , num_col3_orig_list
103 ;
104 IF merged_records%NOTFOUND THEN
105 l_last_fetch := TRUE;
106 END IF;
107 IF merge_header_id_list.COUNT = 0 and l_last_fetch then
108 exit;
109 END IF;
110 FOR i in 1..merge_header_id_list.COUNT LOOP
111 num_col1_new_list(i) := hz_acct_merge_util.getdup_account(num_col1_orig_list(i));
112 num_col2_new_list(i) := hz_acct_merge_util.getdup_site(num_col2_orig_list(i));
113 num_col3_new_list(i) := hz_acct_merge_util.getdup_site_use(num_col3_orig_list(i));
114 /*
115 For a given Account and Site Combination, there can be a single Payer.
116 Get the Payer Id for both the duplicate and Target Account
117 */
118 ext_payer_from_list(i) := get_external_payer_id(num_col1_orig_list(i),num_col3_orig_list(i));
119 ext_payer_to_list(i) := get_external_payer_id(num_col1_new_list(i),num_col3_new_list(i));
120 END LOOP;
121 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
122 FORALL I in 1..merge_header_id_list.COUNT
123 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
124 merge_log_id,
125 table_name,
126 merge_header_id,
127 primary_key_id,
128 num_col1_orig,
129 num_col1_new,
130 num_col2_orig,
131 num_col2_new,
132 num_col3_orig,
133 num_col3_new,
134 action_flag,
135 request_id,
136 created_by,
137 creation_date,
138 last_update_login,
139 last_update_date,
140 last_updated_by
141 ) VALUES ( hz_customer_merge_log_s.nextval,
142 'IBY_TRXN_SUMMARIES_ALL',
143 merge_header_id_list(i),
144 primary_key_id_list(i),
145 num_col1_orig_list(i),
146 num_col1_new_list(i),
147 num_col2_orig_list(i),
148 num_col2_new_list(i),
149 num_col3_orig_list(i),
150 num_col3_new_list(i),
151 'U',
152 req_id,
153 hz_utility_pub.created_by,
154 hz_utility_pub.creation_date,
155 hz_utility_pub.last_update_login,
156 hz_utility_pub.last_update_date,
157 hz_utility_pub.last_updated_by
158 );
159
160 END IF;
161 /*
162 Update the Payement Instrument Assignment Id with the new value
163 From the Payment Instrument Use Id, get the Instrument Id
164 Check if the same instrument id exists for the payer of the
165 target account.If exists, update the record with the new Payment Instrument Use Id
166 */
167 FORALL i in 1..ext_payer_from_list.COUNT
168 UPDATE iby_trxn_summaries_all DUP
169 SET payer_instr_assignment_id =
170 (SELECT to_instr.instrument_payment_use_id FROM
171 iby_pmt_instr_uses_all from_inst,
172 iby_pmt_instr_uses_all to_instr
173 WHERE from_inst.instrument_payment_use_id = dup.payer_instr_assignment_id
174 AND to_instr.ext_pmt_party_id = ext_payer_to_list(i)
175 AND to_instr.instrument_id = from_inst.instrument_id
176 AND from_inst.ext_pmt_party_id = ext_payer_from_list(i)
177 AND to_instr.end_date > sysdate)
178 WHERE payer_instr_assignment_id IS NOT NULL
179 AND ext_payer_from_list(i) IS NOT NULL
180 AND ext_payer_to_list(i) IS NOT NULL
181 AND reqtype IN ('ORAPMTREQ','ORAPMTBATCHREQ')
182 AND status <> 0
183 AND EXISTS
184 (
185 SELECT to_instr.instrument_payment_use_id FROM
186 iby_pmt_instr_uses_all from_inst,
187 iby_pmt_instr_uses_all to_instr
188 WHERE from_inst.instrument_payment_use_id = dup.payer_instr_assignment_id
189 AND to_instr.ext_pmt_party_id = ext_payer_to_list(i)
190 AND to_instr.instrument_id = from_inst.instrument_id
191 AND from_inst.ext_pmt_party_id = ext_payer_from_list(i)
192 AND to_instr.end_date > sysdate
193 );
194 /*
195 If the Instrument type is credit card, check if the duplicate and target
196 payers are having creditcards with similar hashcode1 and hashcode2
197 If exists, update the record with the new Payment Instrument Use Id
198 */
199 FORALL i in 1..ext_payer_from_list.COUNT
200 UPDATE iby_trxn_summaries_all dup
201 SET payer_instr_assignment_id =
202 (SELECT to_uses.instrument_payment_use_id FROM
203 iby_pmt_instr_uses_all from_uses,
204 iby_pmt_instr_uses_all to_uses,
205 iby_creditcard from_cards,
206 iby_creditcard to_cards
207 WHERE from_uses.instrument_payment_use_id = dup.payer_instr_assignment_id
208 AND from_uses.instrument_id = from_cards.instrid
209 AND to_cards.cc_number_hash1 = from_cards.cc_number_hash1
210 AND to_cards.cc_number_hash2 = from_cards.cc_number_hash2
211 AND to_uses.instrument_id = to_cards.instrid
212 AND to_uses.ext_pmt_party_id = ext_payer_to_list(i)
213 AND from_uses.ext_pmt_party_id = ext_payer_from_list(i))
214 WHERE payer_instr_assignment_id IS NOT NULL
215 AND reqtype in ('ORAPMTREQ','ORAPMTBATCHREQ')
216 AND status <> 0
217 AND ext_payer_from_list(i) is not null
218 AND ext_payer_to_list(i) is not null
219 AND EXISTS
220 (
221 SELECT To_uses.instrument_payment_use_id FROM
222 iby_pmt_instr_uses_all from_uses,
223 iby_pmt_instr_uses_all to_uses,
224 iby_creditcard from_cards,
225 iby_creditcard to_cards
226 WHERE from_uses.instrument_payment_use_id = dup.payer_instr_assignment_id
227 AND from_uses.instrument_type = 'CREDITCARD'
228 AND from_uses.instrument_id = from_cards.instrid
229 AND to_cards.cc_number_hash1 = from_cards.cc_number_hash1
230 AND to_cards.cc_number_hash2 = from_cards.cc_number_hash2
231 AND to_uses.instrument_id = to_cards.instrid
232 AND to_uses.ext_pmt_party_id = ext_payer_to_list(i)
233 AND from_uses.ext_pmt_party_id = ext_payer_from_list(i)
234 ) ;
235 /*
236 If the Instrument type is Bank Account, check if the duplicate and target
237 payers are having same hash values.
238 If exists, update the record with the new Payment Instrument Use Id
239 */
240 FORALL i in 1..ext_payer_from_list.COUNT
241 UPDATE iby_trxn_summaries_all dup
242 SET payer_instr_assignment_id =
243 (
244 SELECT To_uses.instrument_payment_use_id FROM
245 iby_pmt_instr_uses_all from_uses,
246 iby_pmt_instr_uses_all to_uses,
247 iby_ext_bank_accounts from_accounts,
248 iby_ext_bank_accounts to_accounts
249 WHERE from_uses.instrument_payment_use_id = dup.payer_instr_assignment_id
250 AND from_uses.instrument_type = 'BANKACCOUNT'
251 AND from_uses.instrument_id = from_accounts.ext_bank_account_id
252 AND to_accounts.iban_hash1 = from_accounts.iban_hash1
253 AND to_accounts.iban_hash2 = from_accounts.iban_hash2
254 AND to_uses.instrument_id = to_accounts.ext_bank_account_id
255 AND to_uses.ext_pmt_party_id = ext_payer_to_list(i)
256 AND from_uses.ext_pmt_party_id = ext_payer_from_list(i)
257 )
258 WHERE payer_instr_assignment_id IS NOT NULL
259 AND reqtype in ('ORAPMTREQ','ORAPMTBATCHREQ')
260 AND status <> 0
261 AND ext_payer_from_list(i) is not null
262 AND ext_payer_to_list(i) is not null
263 AND EXISTS
264 (
265 SELECT To_uses.instrument_payment_use_id FROM
266 iby_pmt_instr_uses_all from_uses,
267 iby_pmt_instr_uses_all to_uses,
268 iby_ext_bank_accounts from_accounts,
269 iby_ext_bank_accounts to_accounts
270 WHERE from_uses.instrument_payment_use_id = dup.payer_instr_assignment_id
271 AND from_uses.instrument_type = 'BANKACCOUNT'
272 AND from_uses.instrument_id = from_accounts.ext_bank_account_id
273 AND to_accounts.iban_hash1 = from_accounts.iban_hash1
274 AND to_accounts.iban_hash2 = from_accounts.iban_hash2
275 AND to_uses.instrument_id = to_accounts.ext_bank_account_id
276 AND to_uses.ext_pmt_party_id = ext_payer_to_list(i)
277 AND from_uses.ext_pmt_party_id = ext_payer_from_list(i)
278 ) ;
279 /*
280 Update the Cust Account and Site Use with the new values
281 */
282 FORALL i in 1..merge_header_id_list.COUNT
283 UPDATE iby_trxn_summaries_all yt SET
284 cust_account_id=num_col1_new_list(i)
285 ,acct_site_id=num_col2_new_list(i)
286 ,acct_site_use_id=num_col3_new_list(i)
287 , last_update_date=SYSDATE
288 , last_updated_by=arp_standard.profile.user_id
289 , last_update_login=arp_standard.profile.last_update_login
290 WHERE trxnmid=primary_key_id_list(i)
291 AND reqtype IN ('ORAPMTREQ','ORAPMTBATCHREQ')
292 AND status <> 0
293
294 ;
295 l_count := l_count + SQL%ROWCOUNT;
296 IF l_last_fetch THEN
297 EXIT;
298 END IF;
299 END LOOP;
300
301 arp_message.set_name('AR','AR_ROWS_UPDATED');
302 arp_message.set_token('NUM_ROWS',to_char(l_count));
303 END IF;
304 EXCEPTION
305 WHEN OTHERS THEN
306 arp_message.set_line( 'TRX_SUMMARY_MERGE');
307 RAISE;
308 END TRX_SUMMARY_MERGE;
309
310 /*-------------------------------------------------------------
311 |
312 | PROCEDURE
313 | TRANSACTIONS_EXT_MERGE
314 | DESCRIPTION :
315 | Account merge procedure for the table, IBY_FNDCPT_TX_EXTENSIONS
316 |--------------------------------------------------------------*/
317
318 PROCEDURE TRANSACTIONS_EXT_MERGE
319 (
320 ext_payer_from_list ext_payer_id_list_type,
321 ext_payer_to_list ext_payer_id_list_type
322 ) IS
323 BEGIN
324 /*Merge Instruments Use only if the same instrument exists at both the levels
325 We need to merge only those records which are not yer captured
326 */
327
328 iby_debug_pub.Add('Updating the instrument Assignment Id...');
329 FORALL i in 1..ext_payer_from_list.COUNT
330 UPDATE IBY_FNDCPT_TX_EXTENSIONS DUP
331 SET instr_assignment_id =
332 (SELECT to_instr.instrument_payment_use_id FROM
333 iby_pmt_instr_uses_all from_inst,
334 iby_pmt_instr_uses_all to_instr
335 WHERE from_inst.instrument_payment_use_id = dup.instr_assignment_id
336 AND to_instr.ext_pmt_party_id = ext_payer_to_list(i)
337 AND to_instr.instrument_id = from_inst.instrument_id
338 AND to_instr.end_date > sysdate)
339 WHERE ext_payer_id = ext_payer_from_list(i)
340 AND ext_payer_from_list(i) IS NOT NULL
341 AND ext_payer_to_list(i) IS NOT NULL
342 AND instr_assignment_id IS NOT NULL
343 AND ( EXISTS (
344 SELECT 1
345 FROM iby_trxn_summaries_all s
346 ,iby_fndcpt_tx_operations o
347 WHERE o.trxn_extension_id = dup.trxn_extension_id
348 AND s.transactionid = o.transactionid
349 AND s.reqtype IN ('ORAPMTREQ','ORAPMTBATCHREQ')
350 AND s.status <> 0 )
351 OR NOT EXISTS
352 (
353 Select 1 from iby_fndcpt_tx_operations o
354 where o.trxn_extension_id = dup.trxn_extension_id
355 )
356 )
357 AND EXISTS
358 (
359 SELECT to_instr.instrument_payment_use_id FROM
360 iby_pmt_instr_uses_all from_inst,
361 iby_pmt_instr_uses_all to_instr
362 WHERE from_inst.instrument_payment_use_id = dup.instr_assignment_id
363 AND to_instr.ext_pmt_party_id = ext_payer_to_list(i)
364 AND to_instr.instrument_id = from_inst.instrument_id
365 AND to_instr.end_date > sysdate
366 );
367
368
369 /* For credit cards we need to check the Hashcodes. Same hashcodes mean the same instrument*/
370 /*
371 iby_debug_pub.Add('Modifying instrument use id for credit cards');
372 FORALL i in 1..ext_payer_from_list.COUNT
373 UPDATE iby_fndcpt_tx_extensions dup
374 SET instr_assignment_id =
375 (SELECT to_uses.instrument_payment_use_id FROM
376 iby_pmt_instr_uses_all from_uses,
377 iby_pmt_instr_uses_all to_uses,
378 iby_creditcard from_cards,
379 iby_creditcard to_cards
380 WHERE from_uses.instrument_payment_use_id = dup.instr_assignment_id
381 AND from_uses.instrument_id = from_cards.instrid
382 AND to_cards.cc_number_hash1 = from_cards.cc_number_hash1
383 AND to_cards.cc_number_hash2 = from_cards.cc_number_hash2
384 AND to_uses.instrument_id = to_cards.instrid
385 AND to_uses.ext_pmt_party_id = ext_payer_to_list(i) )
386 WHERE ext_payer_id = ext_payer_from_list(i)
387 AND ext_payer_from_list(i) IS NOT NULL
388 AND ext_payer_to_list(i) IS NOT NULL
389 AND instr_assignment_id IS NOT NULL
390 AND ( EXISTS (
391 SELECT 1
392 FROM iby_trxn_summaries_all s
393 ,iby_fndcpt_tx_operations o
394 WHERE o.trxn_extension_id = dup.trxn_extension_id
395 AND s.transactionid = o.transactionid
396 AND s.reqtype IN ('ORAPMTREQ','ORAPMTBATCHREQ')
397 AND s.status <> 0 )
398 OR NOT EXISTS
399 (
400 Select 1 from iby_fndcpt_tx_operations o
401 where o.trxn_extension_id = dup.trxn_extension_id
402 )
403 )
404 AND EXISTS
405 (
406
407 SELECT to_uses.instrument_payment_use_id FROM
408 iby_pmt_instr_uses_all from_uses,
409 iby_pmt_instr_uses_all to_uses,
410 iby_creditcard from_cards,
411 iby_creditcard to_cards
412 WHERE from_uses.instrument_payment_use_id = dup.instr_assignment_id
413 AND from_uses.instrument_type = 'CREDITCARD'
414 AND from_uses.instrument_id = from_cards.instrid
415 AND to_cards.cc_number_hash1 = from_cards.cc_number_hash1
416 AND to_cards.cc_number_hash2 = from_cards.cc_number_hash2
417 AND to_uses.instrument_id = to_cards.instrid
418 AND to_uses.ext_pmt_party_id = ext_payer_to_list(i)
419 ) ; */
420
421 /* For Bank Accounts we need to check the Hashcodes. Same hashcodes mean the same instrument*/
422 /*
423 iby_debug_pub.Add('Modifying instrument use id for bank accounts');
424 FORALL i in 1..ext_payer_from_list.COUNT
425 UPDATE iby_fndcpt_tx_extensions dup
426 SET instr_assignment_id =
427 (SELECT to_uses.instrument_payment_use_id FROM
428 iby_pmt_instr_uses_all from_uses,
429 iby_pmt_instr_uses_all to_uses,
430 iby_ext_bank_accounts from_accounts,
431 iby_ext_bank_accounts to_accounts
432 WHERE from_uses.instrument_payment_use_id = dup.instr_assignment_id
433 AND from_uses.instrument_type = 'BANKACCOUNT'
434 AND from_uses.instrument_id = from_accounts.ext_bank_account_id
435 AND to_accounts.iban_hash1 = from_accounts.iban_hash1
436 AND to_accounts.iban_hash2 = from_accounts.iban_hash2
437 AND to_uses.instrument_id = to_accounts.ext_bank_account_id
438 AND to_uses.ext_pmt_party_id = ext_payer_to_list(i) )
439 WHERE ext_payer_id = ext_payer_from_list(i)
440 AND ext_payer_from_list(i) IS NOT NULL
441 AND ext_payer_to_list(i) IS NOT NULL
442 AND instr_assignment_id IS NOT NULL
443 AND ( EXISTS (
444 SELECT 1
445 FROM iby_trxn_summaries_all s
446 ,iby_fndcpt_tx_operations o
447 WHERE o.trxn_extension_id = dup.trxn_extension_id
448 AND s.transactionid = o.transactionid
449 AND s.reqtype IN ('ORAPMTREQ','ORAPMTBATCHREQ')
450 AND s.status <> 0 )
451 OR NOT EXISTS
452 (
453 Select 1 from iby_fndcpt_tx_operations o
454 where o.trxn_extension_id = dup.trxn_extension_id
455 )
456 )
457 AND EXISTS
458 (
459
460 SELECT to_uses.instrument_payment_use_id FROM
461 iby_pmt_instr_uses_all from_uses,
462 iby_pmt_instr_uses_all to_uses,
463 iby_ext_bank_accounts from_accounts,
464 iby_ext_bank_accounts to_accounts
465 WHERE from_uses.instrument_payment_use_id = dup.instr_assignment_id
466 AND from_uses.instrument_type = 'BANKACCOUNT'
467 AND from_uses.instrument_id = from_accounts.ext_bank_account_id
468 AND to_accounts.iban_hash1 = from_accounts.iban_hash1
469 AND to_accounts.iban_hash2 = from_accounts.iban_hash2
470 AND to_uses.instrument_id = to_accounts.ext_bank_account_id
471 AND to_uses.ext_pmt_party_id = ext_payer_to_list(i)
472 ) ; */
473
474 /* Update the Payer with the new value */
475 iby_debug_pub.Add('Modifying the Payer value');
476 FORALL i in 1..ext_payer_from_list.COUNT
477 UPDATE iby_fndcpt_tx_extensions dup
478 SET ext_payer_id = ext_payer_to_list(i)
479 WHERE ext_payer_id = ext_payer_from_list(i)
480 AND ext_payer_from_list(i) IS NOT NULL
481 AND ext_payer_to_list(i) IS NOT NULL
482 AND
483 ( EXISTS (
484 SELECT 1
485 FROM iby_trxn_summaries_all s
486 ,iby_fndcpt_tx_operations o
487 WHERE o.trxn_extension_id = dup.trxn_extension_id
488 AND s.transactionid = o.transactionid
489 AND s.reqtype IN ('ORAPMTREQ','ORAPMTBATCHREQ')
490 AND s.status <> 0 )
491 OR NOT EXISTS
492 (
493 Select 1 from iby_fndcpt_tx_operations o
494 where o.trxn_extension_id = dup.trxn_extension_id
495 )
496 );
497
498 iby_debug_pub.Add('After Modifying the Payer value');
499
500 EXCEPTION
501 WHEN OTHERS THEN
502 arp_message.set_line( 'TRANSACTIONS_EXT_MERGE');
503 RAISE;
504
505 END TRANSACTIONS_EXT_MERGE;
506
507 /*-------------------------------------------------------------
508 |
509 | PROCEDURE
510 | PAYMENT_METHODS_MERGE
511 | DESCRIPTION :
512 | Account merge procedure for the table, IBY_EXT_PARTY_PMT_MTHDS
513 |--------------------------------------------------------------*/
514 PROCEDURE PAYMENT_METHODS_MERGE
515 (
516 ext_payer_from_list ext_payer_id_list_type,
517 ext_payer_to_list ext_payer_id_list_type
518 ) IS
519
520 BEGIN
521 /*
522 At target account, if there exists a payer method for an instrument
523 similar to that of the target account, inactivate the payment method
524 for the target account, else transfer the same
525 */
526 iby_debug_pub.Add('Merging the Payment Methods');
527 FORALL i in 1..ext_payer_from_list.COUNT
528 UPDATE iby_ext_party_pmt_mthds dup
529 SET ext_pmt_party_id = ext_payer_to_list(i),
530 last_update_date = SYSDATE,
531 last_updated_by = arp_standard.profile.user_id,
532 last_update_login = arp_standard.profile.last_update_login
533 WHERE ext_pmt_party_id = ext_payer_from_list(i)
534 AND ext_payer_from_list(i) IS NOT NULL
535 AND ext_payer_to_list(i) IS NOT NULL
536 AND NOT EXISTS
537 (
538 SELECT ext_party_pmt_mthd_id
539 FROM iby_ext_party_pmt_mthds mto
540 WHERE mto.ext_pmt_party_id = ext_payer_to_list(i)
541 AND mto.payment_method_code = dup.payment_method_code
542 AND mto.payment_flow = dup.payment_flow
543 AND mto.payment_function = dup.payment_function
544 );
545 iby_debug_pub.Add('Transferring the Payment Methods');
546 FORALL i in 1..ext_payer_from_list.COUNT
547 UPDATE iby_ext_party_pmt_mthds dup
548 SET inactive_date = SYSDATE,
549 last_update_date = SYSDATE,
550 last_updated_by = arp_standard.profile.user_id,
551 last_update_login = arp_standard.profile.last_update_login
552 WHERE ext_pmt_party_id = ext_payer_from_list(i)
553 AND ext_payer_from_list(i) IS NOT NULL
554 AND ext_payer_to_list(i) IS NOT NULL
555 AND EXISTS
556 (
557 SELECT ext_party_pmt_mthd_id
558 FROM iby_ext_party_pmt_mthds mto
559 WHERE mto.ext_pmt_party_id = ext_payer_to_list(i)
560 AND mto.payment_method_code = dup.payment_method_code
561 AND mto.payment_flow = dup.payment_flow
562 AND mto.payment_function = dup.payment_function
563 );
564
565 EXCEPTION
566 WHEN OTHERS THEN
567 arp_message.set_line( 'PAYMENT_METHODS_MERGE');
568 RAISE;
569
570 END PAYMENT_METHODS_MERGE;
571
572 /*-------------------------------------------------------------
573 |
574 | PROCEDURE
575 | INSTRUMENT_MERGE
576 | DESCRIPTION :
577 | Account merge procedure for the table, IBY_PMT_INSTR_USES_ALL
578 |--------------------------------------------------------------*/
579 PROCEDURE INSTRUMENT_MERGE
580 (
581 ext_payer_from_list ext_payer_id_list_type,
582 ext_payer_to_list ext_payer_id_list_type
583 ) IS
584 BEGIN
585 /*
586 If there exists a similar instrument at both the accounts,
587 inactivate the payment method for the target account, else transfer the same
588 */
589 iby_debug_pub.Add('Transferring the Instruments');
590 FORALL i in 1..ext_payer_from_list.COUNT
591 UPDATE iby_pmt_instr_uses_all dup
592 SET ext_pmt_party_id = ext_payer_to_list(i),
593 last_update_date = SYSDATE,
594 last_updated_by = arp_standard.profile.user_id,
595 last_update_login = arp_standard.profile.last_update_login
596 WHERE ext_pmt_party_id = ext_payer_from_list(i)
597 AND ext_payer_from_list(i) IS NOT NULL
598 AND ext_payer_to_list(i) IS NOT NULL
599 AND payment_flow = 'FUNDS_CAPTURE'
600 AND payment_function = 'CUSTOMER_PAYMENT'
601 AND NOT EXISTS
602 (
603 SELECT instrument_id
604 FROM iby_pmt_instr_uses_all mto
605 WHERE mto.ext_pmt_party_id = ext_payer_to_list(i)
606 AND mto.instrument_id = dup.instrument_id
607 AND mto.payment_flow = dup.payment_flow
608 AND mto.payment_function = dup.payment_function
609 AND mto.instrument_type = dup.instrument_type
610 );
611 /*
612 If two Credit cards carries the same hash codes , it means there
613 exists a single instrument., in which case, we need to transfer
614 */
615 iby_debug_pub.Add('Transferring the Credit Cards');
616 FORALL i in 1..ext_payer_from_list.COUNT
617 UPDATE iby_pmt_instr_uses_all dup
618 SET ext_pmt_party_id = ext_payer_to_list(i),
619 last_update_date = SYSDATE,
620 last_updated_by = arp_standard.profile.user_id,
621 last_update_login = arp_standard.profile.last_update_login
622 WHERE ext_pmt_party_id = ext_payer_from_list(i)
623 AND ext_payer_from_list(i) IS NOT NULL
624 AND ext_payer_to_list(i) IS NOT NULL
625 AND dup.instrument_type = 'CREDITCARD'
626 AND payment_flow = 'FUNDS_CAPTURE'
627 AND payment_function = 'CUSTOMER_PAYMENT'
628 AND NOT EXISTS
629 (
630 SELECT 1 FROM
631 iby_creditcard from_card,
632 iby_creditcard to_card,
633 iby_pmt_instr_uses_all uses
634 WHERE from_card.instrid = dup.instrument_id
635 AND to_card.cc_number_hash1 = from_card.cc_number_hash1
636 AND to_card.cc_number_hash2 = from_card.cc_number_hash2
637 AND to_card.instrid = uses.instrument_id
638 AND uses.ext_pmt_party_id = ext_payer_to_list(i)
639
640 );
641
642 /*
643 If two Bank Accounts carries the same hash codes , it means there
644 exists a single instrument., in which case, we need to transfer
645 */
646 iby_debug_pub.Add('Transferring the bank accounts');
647 FORALL i in 1..ext_payer_from_list.COUNT
648 UPDATE iby_pmt_instr_uses_all dup
649 SET ext_pmt_party_id = ext_payer_to_list(i),
650 last_update_date = SYSDATE,
651 last_updated_by = arp_standard.profile.user_id,
652 last_update_login = arp_standard.profile.last_update_login
653 WHERE ext_pmt_party_id = ext_payer_from_list(i)
654 AND ext_payer_from_list(i) IS NOT NULL
655 AND ext_payer_to_list(i) IS NOT NULL
656 AND dup.instrument_type = 'BANKACCOUNT'
657 AND payment_flow = 'FUNDS_CAPTURE'
658 AND payment_function = 'CUSTOMER_PAYMENT'
659 AND NOT EXISTS
660 (
661 SELECT 1 FROM
662 iby_ext_bank_accounts from_account,
663 iby_ext_bank_accounts to_account,
664 iby_pmt_instr_uses_all uses
665 WHERE from_account.ext_bank_account_id = dup.instrument_id
666 AND to_account.iban_hash1 = from_account.iban_hash1
667 AND to_account.iban_hash2 = from_account.iban_hash2
668 AND to_account.ext_bank_account_id = uses.instrument_id
669 AND uses.ext_pmt_party_id = ext_payer_to_list(i)
670
671 );
672
673
674
675 iby_debug_pub.Add('Inactivating the Instruments for duplicat account');
676 FORALL i in 1..ext_payer_from_list.COUNT
677 UPDATE iby_pmt_instr_uses_all dup
678 SET end_date = SYSDATE,
679 last_update_date = SYSDATE,
680 last_updated_by = arp_standard.profile.user_id,
681 last_update_login = arp_standard.profile.last_update_login
682 WHERE ext_pmt_party_id = ext_payer_from_list(i)
683 AND ext_payer_from_list(i) IS NOT NULL
684 AND ext_payer_to_list(i) IS NOT NULL
685 AND payment_flow = 'FUNDS_CAPTURE'
686 AND payment_function = 'CUSTOMER_PAYMENT'
687 AND EXISTS
688 (
689 SELECT instrument_id
690 FROM iby_pmt_instr_uses_all mto
691 WHERE mto.ext_pmt_party_id = ext_payer_to_list(i)
692 AND mto.instrument_id = dup.instrument_id
693 AND mto.payment_flow = dup.payment_flow
694 AND mto.payment_function = dup.payment_function
695 AND mto.instrument_type = dup.instrument_type
696 );
697
698 iby_debug_pub.Add('Inactivating the Credit cards for dupliacte account');
699 FORALL i in 1..ext_payer_from_list.COUNT
700 UPDATE iby_pmt_instr_uses_all dup
701 SET end_date = SYSDATE,
702 last_update_date = SYSDATE,
703 last_updated_by = arp_standard.profile.user_id,
704 last_update_login = arp_standard.profile.last_update_login
705 WHERE ext_pmt_party_id = ext_payer_from_list(i)
706 AND ext_payer_from_list(i) IS NOT NULL
707 AND ext_payer_to_list(i) IS NOT NULL
708 AND payment_flow = 'FUNDS_CAPTURE'
709 AND payment_function = 'CUSTOMER_PAYMENT'
710 AND dup.instrument_type = 'CREDITCARD'
711 AND EXISTS
712 (
713 SELECT 1 FROM
714 iby_creditcard from_card,
715 iby_creditcard to_card,
716 iby_pmt_instr_uses_all uses
717 WHERE from_card.instrid = dup.instrument_id
718 AND to_card.cc_number_hash1 = from_card.cc_number_hash1
719 AND to_card.cc_number_hash2 = from_card.cc_number_hash2
720 AND to_card.instrid = uses.instrument_id
721 AND uses.ext_pmt_party_id = ext_payer_to_list(i)
722 );
723
724 iby_debug_pub.Add('Inactivating the Bank Accounts for dupliacte account');
725 FORALL i in 1..ext_payer_from_list.COUNT
726 UPDATE iby_pmt_instr_uses_all dup
727 SET end_date = SYSDATE,
728 last_update_date = SYSDATE,
729 last_updated_by = arp_standard.profile.user_id,
730 last_update_login = arp_standard.profile.last_update_login
731 WHERE ext_pmt_party_id = ext_payer_from_list(i)
732 AND ext_payer_from_list(i) IS NOT NULL
733 AND ext_payer_to_list(i) IS NOT NULL
734 AND payment_flow = 'FUNDS_CAPTURE'
735 AND payment_function = 'CUSTOMER_PAYMENT'
736 AND dup.instrument_type = 'BANKACCOUNT'
737 AND EXISTS
738 (
739 SELECT 1 FROM
740 iby_ext_bank_accounts from_account,
741 iby_ext_bank_accounts to_account,
742 iby_pmt_instr_uses_all uses
743 WHERE from_account.ext_bank_account_id = dup.instrument_id
744 AND to_account.iban_hash1 = from_account.iban_hash1
745 AND to_account.iban_hash2 = from_account.iban_hash2
746 AND to_account.ext_bank_account_id = uses.instrument_id
747 AND uses.ext_pmt_party_id = ext_payer_to_list(i)
748 );
749
750
751
752 EXCEPTION
753 WHEN OTHERS THEN
754 arp_message.set_line( 'INSTRUMENT_MERGE');
755 RAISE;
756
757 END INSTRUMENT_MERGE;
758
759 /*-------------------------------------------------------------
760 |
761 | PROCEDURE
762 | MERGE
763 | DESCRIPTION :
764 | Account merge procedure for the table, IBY_EXTERNAL_PAYERS_ALL
765 |
766 | ******************************
767 |
768 |--------------------------------------------------------------*/
769
770 PROCEDURE MERGE (
771 req_id NUMBER,
772 set_num NUMBER,
773 process_mode VARCHAR2) IS
774
775 TYPE merge_header_id_list_type IS TABLE OF
776 ra_customer_merge_headers.customer_merge_header_id%TYPE
777 INDEX BY BINARY_INTEGER;
778 merge_header_id_list merge_header_id_list_type;
779
780 primary_key_id_list ext_payer_id_list_type;
781
782 TYPE cust_account_id_list_type IS TABLE OF
783 iby_external_payers_all.cust_account_id%TYPE
784 INDEX BY BINARY_INTEGER;
785 num_col1_orig_list cust_account_id_list_type;
786 num_col1_new_list cust_account_id_list_type;
787
788 TYPE acct_site_use_id_list_type IS TABLE OF
789 iby_external_payers_all.acct_site_use_id%TYPE
790 INDEX BY BINARY_INTEGER;
791 num_col2_orig_list acct_site_use_id_list_type;
792 num_col2_new_list acct_site_use_id_list_type;
793
794 ext_payer_from_list ext_payer_id_list_type;
795 ext_payer_to_list ext_payer_id_list_type;
796
797
798 l_profile_val VARCHAR2(30);
799
800 -- 16482356: RPC-FEB13:CUSTOMER SITE MERGE END DATES ALL INSTRUMENTS
801
802 CURSOR merged_records IS
803 SELECT DISTINCT customer_merge_header_id
804 ,ext_payer_id
805 ,cust_account_id
806 ,acct_site_use_id
807 FROM iby_external_payers_all yt, ra_customer_merges m
808 WHERE (
809 ( yt.cust_account_id = m.duplicate_id
810 AND yt.acct_site_use_id is null
811 AND m.customer_id <> m.duplicate_id )
812 OR
813 ( yt.cust_account_id = m.duplicate_id
814 AND yt.acct_site_use_id = m.duplicate_site_id )
815 )
816 AND m.process_flag = 'N'
817 AND m.request_id = req_id
818 AND m.set_number = set_num;
819
820 l_last_fetch BOOLEAN := FALSE;
821 l_count NUMBER;
822 l_payer_transferred BOOLEAN;
823
824 l_dup_payer IBY_EXTERNAL_PAYERS_ALL.ext_payer_id%TYPE;
825 l_payer_count NUMBER;
826
827 BEGIN
828 IF process_mode='LOCK' THEN
829 NULL;
830 ELSE
831 arp_message.set_name('AR','AR_UPDATING_TABLE');
832 arp_message.set_token('TABLE_NAME','IBY_EXTERNAL_PAYERS_ALL',FALSE);
833 hz_acct_merge_util.load_set(set_num, req_id);
834 l_profile_val := fnd_profile.value('HZ_AUDIT_ACCT_MERGE');
835
836 open merged_records;
837 LOOP
838 FETCH merged_records BULK COLLECT INTO
839 merge_header_id_list
840 , primary_key_id_list
841 , num_col1_orig_list
842 , num_col2_orig_list
843 ;
844 IF merged_records%NOTFOUND THEN
845 l_last_fetch := TRUE;
846 END IF;
847 IF merge_header_id_list.COUNT = 0 and l_last_fetch then
848 exit;
849 END IF;
850 FOR I in 1..merge_header_id_list.COUNT LOOP
851 num_col1_new_list(i) := hz_acct_merge_util.getdup_account(num_col1_orig_list(i));
852 num_col2_new_list(i) := hz_acct_merge_util.getdup_site_use(num_col2_orig_list(i));
853 /*
854 For a given Account and Site Combination, there can be a single Payer.
855 Get the Payer Id for both the duplicate and Target Account
856 */
857 ext_payer_from_list(i) := get_external_payer_id(num_col1_orig_list(i),num_col2_orig_list(i));
858 ext_payer_to_list(i) := get_external_payer_id(num_col1_new_list(i),num_col2_new_list(i));
859 -- added debug
860 iby_debug_pub.Add( '*** ext_payer_from_list(i) added ' || ext_payer_from_list(i));
861 iby_debug_pub.Add( '*** ext_payer_to_list(i) added ' || ext_payer_to_list(i));
862
863
864 END LOOP;
865 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
866 FORALL I in 1..merge_header_id_list.COUNT
867 INSERT INTO hz_customer_merge_log (
868 merge_log_id,
869 table_name,
870 merge_header_id,
871 primary_key_id,
872 num_col1_orig,
873 num_col1_new,
874 num_col2_orig,
875 num_col2_new,
876 action_flag,
877 request_id,
878 created_by,
879 creation_date,
880 last_update_login,
881 last_update_date,
882 last_updated_by
883 ) VALUES ( hz_customer_merge_log_s.nextval,
884 'IBY_EXTERNAL_PAYERS_ALL',
885 merge_header_id_list(i),
886 primary_key_id_list(i),
887 num_col1_orig_list(i),
888 num_col1_new_list(i),
889 num_col2_orig_list(i),
890 num_col2_new_list(i),
891 'U',
892 req_id,
893 hz_utility_pub.created_by,
894 hz_utility_pub.creation_date,
895 hz_utility_pub.last_update_login,
896 hz_utility_pub.last_update_date,
897 hz_utility_pub.last_updated_by
898 );
899
900 END IF;
901 iby_debug_pub.Add('Transferring the Payer Records');
902 /*
903 If there is a payer at the duplicate account and if there doesnt exist any
904 payer at the target account, transfer the payer by updating the
905 account and site use with the new values
906 */
907 FOR i in 1..merge_header_id_list.COUNT
908 LOOP
909 l_dup_payer := NULL;
910 l_payer_count := 0;
911
912 iby_debug_pub.Add( 'checking if a site is already updated');
913
914 iby_debug_pub.Add( '*** num_col1_new_list(i) ' || num_col1_new_list(i));
915 iby_debug_pub.Add( '*** num_col2_new_list(i) ' || num_col2_new_list(i));
916
917 SELECT count(ext_payer_id) into l_payer_count
918 FROM IBY_EXTERNAL_PAYERS_ALL
919 WHERE cust_account_id = num_col1_new_list(i)
920 AND acct_site_use_id = num_col2_new_list(i)
921 AND ext_payer_from_list(i) is not null
922 AND ext_payer_to_list(i) is null;
923
924 iby_debug_pub.Add( 'l_payer_count after fetching: '|| l_payer_count);
925
926
927 IF( l_payer_count > 0)
928 THEN
929 SELECT ext_payer_id into l_dup_payer
930 FROM IBY_EXTERNAL_PAYERS_ALL
931 WHERE cust_account_id = num_col1_new_list(i)
932 AND acct_site_use_id = num_col2_new_list(i)
933 AND ext_payer_from_list(i) is not null
934 AND ext_payer_to_list(i) is null;
935
936 iby_debug_pub.Add( 'l_dup_payer after fetching: '|| l_dup_payer);
937
938 ext_payer_to_list(i) := l_dup_payer;
939
940 iby_debug_pub.Add( 'ext_payer_to_list:'||ext_payer_to_list(i));
941
942 END IF;
943
944 iby_debug_pub.Add( 'After checking of a site already updated');
945
946 iby_debug_pub.Add( '*** primary_key_id_list(i) ' || primary_key_id_list(i));
947 iby_debug_pub.Add( '*** ext_payer_from_list(i) ' || ext_payer_from_list(i));
948 iby_debug_pub.Add( '*** ext_payer_to_list(i) added ' || ext_payer_to_list(i));
949
950 UPDATE IBY_EXTERNAL_PAYERS_ALL yt SET
951 cust_account_id=num_col1_new_list(i)
952 ,acct_site_use_id=num_col2_new_list(i)
953 , last_update_date=SYSDATE
954 , last_updated_by=arp_standard.profile.user_id
955 , last_update_login=arp_standard.profile.last_update_login
956 WHERE ext_payer_id=primary_key_id_list(i)
957 AND primary_key_id_list(i) = ext_payer_from_list(i)
958 AND ext_payer_from_list(i) is not null
959 AND ext_payer_to_list(i) is null;
960 END LOOP;
961
962 iby_debug_pub.Add('Performing Transaction summary Merge');
963 trx_summary_merge(req_id,set_num,process_mode);
964 iby_debug_pub.Add('Performing Transaction Extensiopns Merge');
965 transactions_ext_merge(ext_payer_from_list,ext_payer_to_list);
966 iby_debug_pub.Add('Performing Instruments Merge');
967 instrument_merge(ext_payer_from_list,ext_payer_to_list);
968 iby_debug_pub.Add('Performing Payment Methods Merge');
969 payment_methods_merge(ext_payer_from_list,ext_payer_to_list);
970 l_count := l_count + SQL%ROWCOUNT;
971 IF l_last_fetch THEN
972 EXIT;
973 END IF;
974 END LOOP;
975
976 arp_message.set_name('AR','AR_ROWS_UPDATED');
977 arp_message.set_token('NUM_ROWS',to_char(l_count));
978 END IF;
979 EXCEPTION
980 WHEN OTHERS THEN
981 arp_message.set_line( 'MERGE');
982 RAISE;
983 END MERGE;
984
985
986 END IBY_MERGE;
[Home] [Help]