[Home] [Help]
PACKAGE BODY: APPS.CSI_ACCT_MERGE_PKG
Source
1 PACKAGE BODY CSI_ACCT_MERGE_PKG AS
2 /* $Header: csiatmgb.pls 120.4 2008/05/28 08:33:20 jgootyag ship $ */
3 PROCEDURE write_to_cr_log ( p_message IN VARCHAR2);
4
5 PROCEDURE MERGE( req_id IN NUMBER,
6 set_num IN NUMBER,
7 process_mode IN VARCHAR2) IS
8
9 error_str VARCHAR2(3);
10 no_of_rows NUMBER;
11 BEGIN
12
13 arp_message.set_line('CSI_ACCT_MERGE_PKG.MERGE()+');
14 /*
15 error_str := '001';
16 write_to_cr_log( ' Processing Item Instances Merge ' );
17 CSI_ITEM_INSTANCES_MERGE(req_id, set_num, process_mode);
18 write_to_cr_log( ' Ending process for Item Instances All ' );
19
20 error_str := '002';
21 write_to_cr_log( ' Processing Systems All Merge ' );
22 CSI_SYSTEMS_B_MERGE(req_id, set_num, process_mode);
23 write_to_cr_log( ' Ending process for Systems All ' );
24
25 error_str := '003';
26 write_to_cr_log( ' Processing IP Accounts Merge ' );
27 CSI_IP_ACCOUNTS_MERGE(req_id, set_num, process_mode);
28 write_to_cr_log( ' Ending process for IP Accounts Merge ' );
29
30 error_str := '004';
31 write_to_cr_log( ' Processing Party Accounts Merge ' );
32 CSI_T_PARTY_ACCOUNTS_MERGE(req_id, set_num, process_mode);
33 write_to_cr_log( ' Ending process for Party Accounts ' );
34
35 error_str := '005';
36 write_to_cr_log( ' Processing CSI Transaction Systems Merge ' );
37 CSI_T_TXN_SYSTEMS_MERGE(req_id, set_num, process_mode);
38 write_to_cr_log( ' Ending process for CSI Transaction Systems Merge ' );
39 */
40
41 arp_message.set_line('CSI_ACCT_MERGE_PKG.MERGE()-');
42
43 EXCEPTION
44 WHEN OTHERS THEN
45 arp_message.set_line( 'CSI_ACCT_MERGE_PKG.MERGE()-');
46 RAISE;
47 END MERGE;
48
49 PROCEDURE csi_item_instances_merge( req_id IN NUMBER,
50 set_num IN NUMBER,
51 process_mode IN VARCHAR2 ) IS
52 BEGIN
53 arp_message.set_line('CSI_ACCT_MERGE_PKG.CSI_ITEM_INSTANCES_MERGE()+');
54 arp_message.set_line('CSI_ACCT_MERGE_PKG.CSI_ITEM_INSTANCES_MERGE()-');
55 EXCEPTION
56 WHEN OTHERS THEN
57 arp_message.set_line('CSI_ACCT_MERGE_PKG.CSI_ITEM_INSTANCES_MERGE()');
58 RAISE;
59 END CSI_ITEM_INSTANCES_MERGE;
60
61 PROCEDURE csi_ip_accounts_merge( req_id IN NUMBER,
62 set_num IN NUMBER,
63 process_mode IN VARCHAR2 ) IS
64
65 error_str VARCHAR2(3);
66 no_of_rows NUMBER;
67 v_transaction_type_id NUMBER;
68 v_transaction_id NUMBER;
69 v_transaction_exists VARCHAR2(1) := 'N';
70 v_instance_party_history_id NUMBER;
71 v_ip_account_history_id NUMBER;
72 v_party_source_table VARCHAR2(30) := 'HZ_PARTIES';
73 v_source_transaction_type VARCHAR2(30) := 'ACCT_MERGE';
74 l_profile_val VARCHAR2(30);
75 l_count NUMBER;
76 l_last_fetch BOOLEAN := FALSE;
77 l_veto_reason VARCHAR2(255) := 'During Account Merge, old Account Id exists in Installed Base History tables. This table stores the history of all the accounts for an item instance and hence cannot be deleted.';
78 TYPE FLAG_LIST IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
79 vetoed_list FLAG_LIST;
80
81 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
82 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
83 INDEX BY BINARY_INTEGER;
84
85 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
86
87 TYPE CUSTOMER_SITE_ID_LIST_TYPE IS TABLE OF
88 RA_CUSTOMER_MERGES.CUSTOMER_SITE_ID%TYPE
89 INDEX BY BINARY_INTEGER;
90
91 INSTALL_CUSTOMER_SITE_ID_LIST CUSTOMER_SITE_ID_LIST_TYPE;
92
93 TYPE IP_ACCOUNT_ID_LIST_TYPE IS TABLE OF
94 CSI_IP_ACCOUNTS.IP_ACCOUNT_ID%TYPE
95 INDEX BY BINARY_INTEGER;
96
97 PRIMARY_KEY_ID_LIST IP_ACCOUNT_ID_LIST_TYPE;
98
99 TYPE PARTY_ACCT_ID_LIST_TYPE IS TABLE OF
100 CSI_IP_ACCOUNTS.PARTY_ACCOUNT_ID%TYPE
101 INDEX BY BINARY_INTEGER;
102
103 NUM_COL1_ORIG_LIST PARTY_ACCT_ID_LIST_TYPE;
104 NUM_COL1_NEW_LIST PARTY_ACCT_ID_LIST_TYPE;
105
106 TYPE INSTANCE_PARTY_ID_LIST_TYPE IS TABLE OF
107 CSI_I_PARTIES.INSTANCE_PARTY_ID%TYPE
108 INDEX BY BINARY_INTEGER;
109
110 INSTANCE_PARTY_ID_LIST INSTANCE_PARTY_ID_LIST_TYPE;
111
112 TYPE INSTANCE_ID_LIST_TYPE IS TABLE OF
113 CSI_ITEM_INSTANCES.INSTANCE_ID%TYPE
114 INDEX BY BINARY_INTEGER;
115
116 INSTANCE_ID_LIST INSTANCE_ID_LIST_TYPE;
117
118 TYPE BILL_TO_ADDRESS_LIST_TYPE IS TABLE OF
119 CSI_IP_ACCOUNTS.BILL_TO_ADDRESS%TYPE
120 INDEX BY BINARY_INTEGER;
121
122 NUM_COL2_ORIG_LIST BILL_TO_ADDRESS_LIST_TYPE;
123 NUM_COL2_NEW_LIST BILL_TO_ADDRESS_LIST_TYPE;
124
125 TYPE SHIP_TO_ADDRESS_LIST_TYPE IS TABLE OF
126 CSI_IP_ACCOUNTS.SHIP_TO_ADDRESS%TYPE
127 INDEX BY BINARY_INTEGER;
128
129 NUM_COL3_ORIG_LIST SHIP_TO_ADDRESS_LIST_TYPE;
130 NUM_COL3_NEW_LIST SHIP_TO_ADDRESS_LIST_TYPE;
131
132 TYPE LOCATION_ID_LIST_TYPE IS TABLE OF
133 CSI_ITEM_INSTANCES.LOCATION_ID%TYPE
134 INDEX BY BINARY_INTEGER;
135
136 NUM_COL4_ORIG_LIST LOCATION_ID_LIST_TYPE;
137 NUM_COL4_NEW_LIST LOCATION_ID_LIST_TYPE;
138
139
140 TYPE INSTALL_LOCATION_ID_LIST_TYPE IS TABLE OF
141 CSI_ITEM_INSTANCES.INSTALL_LOCATION_ID%TYPE
142 INDEX BY BINARY_INTEGER;
143
144
145 NUM_COL5_ORIG_LIST INSTALL_LOCATION_ID_LIST_TYPE;
146 NUM_COL5_NEW_LIST INSTALL_LOCATION_ID_LIST_TYPE;
147
148 TYPE CUST_ACCT_SITE_ID_LIST_TYPE IS TABLE OF
149 HZ_CUST_ACCT_SITES_ALL .CUST_ACCT_SITE_ID%TYPE
150 INDEX BY BINARY_INTEGER;
151
152 NUM_COL6_ORIG_LIST CUST_ACCT_SITE_ID_LIST_TYPE;
153 NUM_COL6_NEW_LIST CUST_ACCT_SITE_ID_LIST_TYPE;
154
155
156 CURSOR merged_records IS
157 SELECT DISTINCT
158 m.CUSTOMER_MERGE_HEADER_ID ,
159 yt.IP_ACCOUNT_ID ,
160 yt.PARTY_ACCOUNT_ID ,
161 yt.BILL_TO_ADDRESS ,
162 yt.SHIP_TO_ADDRESS ,
163 cip.INSTANCE_PARTY_ID ,
164 cip.INSTANCE_ID
165 FROM CSI_IP_ACCOUNTS yt,
166 CSI_I_PARTIES cip,
167 CSI_ITEM_INSTANCES cii,
168 RA_CUSTOMER_MERGES m
169 WHERE ( yt.PARTY_ACCOUNT_ID = m.DUPLICATE_ID OR
170 yt.BILL_TO_ADDRESS = m.DUPLICATE_SITE_ID OR
171 yt.SHIP_TO_ADDRESS = m.DUPLICATE_SITE_ID )
172 AND yt.instance_party_id = cip.instance_party_id
173 AND ( yt.active_end_date is null OR
174 yt.active_end_date > sysdate )
175 AND cip.instance_id = cii.instance_id
176 AND ( cii.active_end_date is null OR
177 cii.active_end_date > sysdate )
178 -- TODO : the next line needs to be uncommented
179 AND m.process_flag = 'N'
180 AND m.request_id = req_id
181 AND m.set_number = set_num;
182
183 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
184 l_msg_count NUMBER;
185 l_msg_data VARCHAR2(2000);
186
187 -- Variables for 'GET ITEM INSTANCE DETAILS' API call
188
189 l_g_instance_rec csi_datastructures_pub.instance_header_rec;
190 l_g_ph_tbl csi_datastructures_pub.party_header_tbl;
191 l_g_pah_tbl csi_datastructures_pub.party_account_header_tbl;
192 l_g_ouh_tbl csi_datastructures_pub.org_units_header_tbl;
193 l_g_pa_tbl csi_datastructures_pub.pricing_attribs_tbl;
194 l_g_eav_tbl csi_datastructures_pub.extend_attrib_values_tbl;
195 l_g_ea_tbl csi_datastructures_pub.extend_attrib_tbl;
196 l_g_iah_tbl csi_datastructures_pub.instance_asset_header_tbl;
197 l_g_time_stamp date;
198
199 -- Variables for 'UPDATE ITEM INSTANCE' API call
200
201 p_u_instance_rec csi_datastructures_pub.instance_rec;
202 p_u_ext_attrib_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
203 p_u_party_tbl csi_datastructures_pub.party_tbl;
204 p_u_party_account_tbl csi_datastructures_pub.party_account_tbl;
205 p_u_pricing_attrib_tbl csi_datastructures_pub.pricing_attribs_tbl;
206 p_u_org_assignments_tbl csi_datastructures_pub.organization_units_tbl;
207 p_u_asset_assignment_tbl csi_datastructures_pub.instance_asset_tbl;
208 p_txn_rec csi_datastructures_pub.transaction_rec;
209 x_instance_id_lst csi_datastructures_pub.id_tbl;
210
211 -- local
212 l_merge_excep EXCEPTION;
213 l_install_location_type_code VARCHAR2(30);
214 l_location_type_code VARCHAR2(30);
215 l_install_customer_site_id NUMBER;
216 l_party_site_id NUMBER;
217 l_party_id NUMBER;
218 l_party_account_id NUMBER;
219 l_bill_to_address NUMBER;
220 l_ship_to_address NUMBER;
221 l_acct_idx NUMBER;
222 l_pty_idx NUMBER;
223 l_to_party_id NUMBER;
224 l_num_of_acct_records NUMBER;
225 l_acct_rec_index NUMBER;
226 l_owner_update VARCHAR2(1);
227 l_address_only_upd VARCHAR2(1);
228 l_instance_party_id NUMBER;
229 l_old_acct NUMBER;
230 l_pty_with_diff_accts VARCHAR2(1);
231 I NUMBER;
232 J NUMBER;
233 K NUMBER;
234 l_msg_ctr NUMBER;
235 l_msg_dummy NUMBER;
236 l_msg_string VARCHAR2(256);
237 l_prof_reltype_code VARCHAR2(30);
238 l_from_customer_id NUMBER;
239 l_is_vetoed VARCHAR2(1);
240
241 BEGIN -- {1
242
243 error_str := '001';
244 arp_message.set_line('CSI_ACCT_MERGE_PKG.CSI_IP_ACCOUNTS_MERGE()+');
245
246 IF (process_mode = 'LOCK') THEN -- {2
247
248 error_str := '002';
249 write_to_cr_log( 'Locking the csi_ip_accounts table' );
250
251 arp_message.set_name('AR','AR_LOCKING_TABLE');
252 arp_message.set_token('TABLE_NAME','CSI_IP_ACCOUNTS',FALSE);
253
254 OPEN merged_records;
255 CLOSE merged_records;
256
257 write_to_cr_log( 'Done Locking the csi_ip_accounts table' );
258
259 ELSE -- } 2 {
260
261 l_msg_string := 'CSI:Processing';
262 write_to_cr_log( l_msg_string );
263
264 error_str := '003';
265 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
266 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','CSI_IP_ACCOUNTS',FALSE);
267 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
268
269 error_str := '004';
270 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
271 l_prof_reltype_code := FND_PROFILE.VALUE('CSI_PARTY_ACCT_MERGE');
272 IF l_prof_reltype_code IS NULL THEN
273 l_prof_reltype_code := 'SHIP_TO';
274 END IF;
275
276 -- Initialize vetoed_list
277 vetoed_list.delete;
278
279 -- Process merge records
280 OPEN merged_records;
281 LOOP -- { 3
282
283 l_msg_string := 'CSI:merged_records cursor...';
284 write_to_cr_log( l_msg_string );
285 -- Fetch all the eligible merge records
286 FETCH merged_records BULK COLLECT INTO
287 MERGE_HEADER_ID_LIST ,
288 PRIMARY_KEY_ID_LIST ,
289 NUM_COL1_ORIG_LIST ,
290 NUM_COL2_ORIG_LIST ,
291 NUM_COL3_ORIG_LIST ,
292 INSTANCE_PARTY_ID_LIST ,
293 INSTANCE_ID_LIST
294 LIMIT 1000;
295
296 IF merged_records%NOTFOUND THEN -- { 4
297 l_last_fetch := TRUE;
298 END IF; -- } 4
299
300 IF MERGE_HEADER_ID_LIST.COUNT = 0 AND l_last_fetch THEN -- { 5
301 EXIT;
302 END IF; -- } 5
303
304 -- Get the corresponding 'merge to' ids
305 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP -- { 6
306
307 write_to_cr_log( to_char(i) || ' orig1 orig2 orig3 : '||
308 to_char(NUM_COL1_ORIG_LIST(I)) || '<>' ||
309 to_char(NUM_COL2_ORIG_LIST(I)) || '<>' ||
310 to_char(NUM_COL3_ORIG_LIST(I)) );
311
312 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
313 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
314 NUM_COL3_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL3_ORIG_LIST(I));
315
316 BEGIN
317
318 SELECT CUSTOMER_SITE_ID INTO l_install_customer_site_id
319 FROM RA_CUSTOMER_MERGES
320 WHERE CUSTOMER_MERGE_HEADER_ID=MERGE_HEADER_ID_LIST(I)
321 AND CUSTOMER_SITE_CODE='INSTALL_AT' ;
322
323 EXCEPTION
324 WHEN NO_DATA_FOUND THEN
325 l_install_customer_site_id:= null;
326
327 END;
328
329 IF l_install_customer_site_id IS NOT NULL THEN
330
331 INSTALL_CUSTOMER_SITE_ID_LIST(I):= HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(l_install_customer_site_id);
332 END IF;
333
334
335
336 --Added for Bug 6978155(FP for bug 6839035)
337
338 SELECT INSTALL_LOCATION_TYPE_CODE,INSTALL_LOCATION_ID,LOCATION_TYPE_CODE,LOCATION_ID
339 INTO l_install_location_type_code,NUM_COL5_ORIG_LIST(I),l_location_type_code,NUM_COL4_ORIG_LIST(I)
340 FROM CSI_ITEM_INSTANCES WHERE INSTANCE_ID=INSTANCE_ID_LIST(I);
341
342 IF NUM_COL5_ORIG_LIST(I) IS NOT NULL THEN
343
344 IF l_install_location_type_code = 'HZ_PARTY_SITES' THEN
345
346
347 SELECT CUST_ACCT_SITE_ID
348 INTO NUM_COL6_NEW_LIST(I) FROM hz_cust_site_uses_all
349 WHERE SITE_USE_ID= INSTALL_CUSTOMER_SITE_ID_LIST(I);
350
351 /* NUM_COL6_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL6_ORIG_LIST(I));*/
352
353 SELECT PARTY_SITE_ID INTO NUM_COL5_NEW_LIST(I)
354 FROM HZ_CUST_ACCT_SITES_ALL
355 WHERE CUST_ACCT_SITE_ID= NUM_COL6_NEW_LIST(I);
356
357 IF l_location_type_code = 'HZ_PARTY_SITES' THEN
358
359 NUM_COL4_NEW_LIST(I):= NUM_COL5_NEW_LIST(I);
360
361 ELSIF l_location_type_code = 'HZ_LOCATIONS' THEN
362
363 SELECT LOCATION_ID INTO NUM_COL4_NEW_LIST(I)
364 FROM HZ_PARTY_SITES
365 WHERE PARTY_SITE_ID = NUM_COL5_NEW_LIST(I);
366
367 END IF;
368
369 ELSIF l_install_location_type_code = 'HZ_LOCATIONS' THEN
370
371 SELECT CUST_ACCT_SITE_ID
372 INTO NUM_COL6_NEW_LIST(I) FROM hz_cust_site_uses_all
373 WHERE SITE_USE_ID= INSTALL_CUSTOMER_SITE_ID_LIST(I);
374
375 /* SELECT PARTY_ID
376 INTO l_party_id FROM HZ_CUST_ACCOUNTS
377 WHERE CUST_ACCOUNT_ID= NUM_COL1_ORIG_LIST(I);
378
379 SELECT PARTY_SITE_ID INTO l_party_site_id
380 FROM HZ_PARTY_SITES
381 WHERE PARTY_ID=l_party_id AND location_id=NUM_COL5_ORIG_LIST(I);
382
383 SELECT CUST_ACCT_SITE_ID
384 INTO NUM_COL6_ORIG_LIST(I) FROM HZ_CUST_ACCT_SITES_ALL
385 WHERE PARTY_SITE_ID= l_party_site_id
386 AND CUST_ACCOUNT_ID= NUM_COL1_ORIG_LIST(I);
387
388 NUM_COL6_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL6_ORIG_LIST(I));*/
389
390
391 SELECT PARTY_SITE_ID INTO l_party_site_id
392 FROM HZ_CUST_ACCT_SITES_ALL
393 WHERE CUST_ACCT_SITE_ID= NUM_COL6_NEW_LIST(I);
394
395 IF l_location_type_code = 'HZ_PARTY_SITES' THEN
396
397 NUM_COL4_NEW_LIST(I):= l_party_site_id;
401 WHERE PARTY_SITE_ID = l_party_site_id;
398
399 SELECT LOCATION_ID INTO NUM_COL5_NEW_LIST(I)
400 FROM HZ_PARTY_SITES
402
403
404 ELSIF l_location_type_code = 'HZ_LOCATIONS' THEN
405
406 SELECT LOCATION_ID,LOCATION_ID
407 INTO NUM_COL5_NEW_LIST(I), NUM_COL4_NEW_LIST(I)
408 FROM HZ_PARTY_SITES
409 WHERE PARTY_SITE_ID = l_party_site_id;
410
411 END IF;
412
413 END IF;
414
415
416 END IF;
417
418
419 write_to_cr_log( to_char(i) || '.new1 new2 new3 : '||
420 to_char(NUM_COL1_NEW_LIST(I)) || '<>' ||
421 to_char(NUM_COL2_NEW_LIST(I)) || '<>' ||
422 to_char(NUM_COL3_NEW_LIST(I)) );
423
424 END LOOP; -- } 6
425
426
427
428
429 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN -- { 7
430 error_str := '005';
431
432 -- Log the data into TCA 'customer merge log'
433 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
434 INSERT INTO HZ_CUSTOMER_MERGE_LOG
435 (
436 MERGE_LOG_ID ,
437 TABLE_NAME ,
438 MERGE_HEADER_ID ,
439 PRIMARY_KEY_ID ,
440 NUM_COL1_ORIG ,
441 NUM_COL1_NEW ,
442 NUM_COL2_ORIG ,
443 NUM_COL2_NEW ,
444 NUM_COL3_ORIG ,
445 NUM_COL3_NEW ,
446 ACTION_FLAG ,
447 REQUEST_ID ,
448 CREATED_BY ,
449 CREATION_DATE ,
450 LAST_UPDATE_LOGIN ,
451 LAST_UPDATE_DATE ,
452 LAST_UPDATED_BY
453 )
454 VALUES
455 (
456 HZ_CUSTOMER_MERGE_LOG_s.nextval ,
457 'CSI_IP_ACCOUNTS' ,
458 MERGE_HEADER_ID_LIST(I) ,
459 PRIMARY_KEY_ID_LIST(I) ,
460 NUM_COL1_ORIG_LIST(I) ,
461 NUM_COL1_NEW_LIST(I) ,
462 NUM_COL2_ORIG_LIST(I ),
463 NUM_COL2_NEW_LIST(I) ,
464 NUM_COL3_ORIG_LIST(I) ,
465 NUM_COL3_NEW_LIST(I) ,
466 'U' ,
467 req_id ,
468 hz_utility_pub.CREATED_BY ,
469 hz_utility_pub.CREATION_DATE ,
470 hz_utility_pub.LAST_UPDATE_LOGIN ,
471 hz_utility_pub.LAST_UPDATE_DATE ,
472 hz_utility_pub.LAST_UPDATED_BY
473 );
474 END IF; -- } 7
475
476 -- build the transaction record, if this is the first time
477 IF nvl(p_txn_rec.transaction_id, fnd_api.g_miss_num) = fnd_api.g_miss_num Then -- { 8
478 --
479 -- Check if there is a transaction record created by any other csi routine
480 -- If so use that id otherwise initialize the txn_rec attributes
481 --
482 BEGIN
483 SELECT transaction_id
484 INTO v_transaction_id
485 FROM csi_transactions
486 WHERE source_line_ref_id = req_id
487 AND source_line_ref = set_num;
488
489 p_txn_rec.transaction_id := v_transaction_id;
490
491 EXCEPTION
492 WHEN no_data_found THEN
493
494 error_str := '006';
495 SELECT transaction_type_id
496 INTO v_transaction_type_id
497 FROM csi_txn_types
498 WHERE source_transaction_type = v_source_transaction_type;
499
500 error_str := '007';
501 p_txn_rec.transaction_date := sysdate;
502 p_txn_rec.source_transaction_date := sysdate;
503 p_txn_rec.transaction_type_id := v_transaction_type_id;
504 p_txn_rec.source_line_ref_id := req_id;
505 p_txn_rec.source_line_ref := set_num;
506 END;
507
508 END IF; -- } 8
509
510 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP -- {
511
512 l_msg_string := 'CSI:------->processing next record from the cursor...';
513 write_to_cr_log( l_msg_string );
514 -- Get the account_id from csi_ip_accounts
515 error_str := '008';
516 SELECT party_account_id, bill_to_address, ship_to_address
517 INTO l_party_account_id, l_bill_to_address, l_ship_to_address
518 FROM csi_ip_accounts
519 WHERE ip_account_id = PRIMARY_KEY_ID_LIST(I);
520
521 -- TODO : remove the next line
522 --NUM_COL1_NEW_LIST(i) := 4565;
523
524 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
525 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','site_id'||INSTALL_CUSTOMER_SITE_ID_LIST(I),FALSE);
526
527 l_msg_string := 'CSI : primary_key_id_list(i), l_party_account_Id , l_bill_to_address, l_ship_to_address :'
528 || to_char(primary_key_id_list(i)) || '<>'
529 || to_char(l_party_account_id) || '<>'
530 || to_char(l_bill_to_address) || '<>'
534 l_msg_string := 'CSI : num_col1_new_list(i), new_col2_new_list(i), num_col3_new_list(i) : '
531 || to_char(l_ship_to_address) ;
532 write_to_cr_log( l_msg_string );
533
535 || to_char(NUM_COL1_NEW_LIST(i)) || '<>'
536 || to_char(NUM_COL2_NEW_LIST(i)) || '<>'
537 || to_char(NUM_COL3_NEW_LIST(i)) ;
538 write_to_cr_log( l_msg_string );
539
540 IF ( l_party_account_id <> NUM_COL1_NEW_LIST(I) OR
541 l_bill_to_address <> NUM_COL2_NEW_LIST(I) OR
542 l_ship_to_address <> NUM_COL3_NEW_LIST(I) ) THEN -- {
543
544 -- Initialize the tables
545 p_u_ext_attrib_values_tbl.delete;
546 p_u_party_tbl.delete;
547 p_u_party_account_tbl.delete;
548 p_u_pricing_attrib_tbl.delete;
549 p_u_org_assignments_tbl.delete;
550 p_u_asset_assignment_tbl.delete;
551
552 error_str := '009';
553 l_g_instance_rec.instance_id := INSTANCE_ID_LIST(I);
554 l_msg_string := 'Before get call for instance ' || to_char(l_g_instance_rec.instance_id);
555 write_to_cr_log( l_msg_string );
556
557 csi_item_instance_pub.get_item_instance_details(
558 p_api_version => 1.0,
559 p_commit => fnd_api.g_false,
560 p_init_msg_list => fnd_api.g_true,
561 p_validation_level => fnd_api.g_valid_level_full,
562 p_instance_rec => l_g_instance_rec,
563 p_get_parties => fnd_api.g_true,
564 p_party_header_tbl => l_g_ph_tbl,
565 p_get_accounts => fnd_api.g_true,
566 p_account_header_tbl => l_g_pah_tbl,
567 p_get_org_assignments => fnd_api.g_false,
568 p_org_header_tbl => l_g_ouh_tbl,
569 p_get_pricing_attribs => fnd_api.g_false,
570 p_pricing_attrib_tbl => l_g_pa_tbl,
571 p_get_ext_attribs => fnd_api.g_false,
572 p_ext_attrib_tbl => l_g_eav_tbl,
573 p_ext_attrib_def_tbl => l_g_ea_tbl,
574 p_get_asset_assignments => fnd_api.g_false,
575 p_asset_header_tbl => l_g_iah_tbl,
576 p_time_stamp => l_g_time_stamp,
577 x_return_status => l_return_status,
578 x_msg_count => l_msg_count,
579 x_msg_data => l_msg_data);
580
581 l_msg_string := 'Return status from get : '|| l_return_status;
582 write_to_cr_log( l_msg_string );
583
584 IF ( l_return_status <> 'S' ) Then
585 write_to_cr_log( 'Msg Count : '||to_char(l_msg_count));
586 FOR l_msg_ctr in 1..l_msg_count LOOP
587 fnd_msg_pub.get ( l_msg_ctr, FND_API.G_FALSE, l_msg_data, l_msg_dummy );
588 write_to_cr_log( 'Msg : '|| to_char(l_msg_ctr) || ' ' || l_msg_data );
589 END LOOP;
590 END IF;
591
592 l_msg_string := 'CSI: Party and Account Records for Inst. : ' ||
593 to_char( l_g_ph_tbl.count ) || ' : ' ||
594 to_char( l_g_pah_tbl.count ) ;
595 write_to_cr_log( l_msg_string );
596
597 -- Get the to_party_id
598 error_str := '010';
599 SELECT party_id
600 INTO l_to_party_id
601 FROM hz_cust_accounts
602 WHERE cust_account_id = NUM_COL1_NEW_LIST(I);
603
604 -- process the instance party account records
605 error_str := '011';
606
607 l_pty_idx := 0;
608 l_acct_idx := 0;
609
610 FOR J in 1..l_g_ph_tbl.count LOOP -- { party table loop
611 l_instance_party_id := l_g_ph_tbl(J).instance_party_id;
612
613 IF l_g_ph_tbl(J).party_source_table = 'HZ_PARTIES' THEN -- { party_source is HZ_PARTIES
614
615 -- Get the number of account records
616 error_str := '012';
617 l_num_of_acct_records := 0;
618 l_acct_rec_index := 0;
619 l_owner_update := 'N';
620 l_address_only_upd := 'N';
621 l_old_acct := NULL;
622 l_pty_with_diff_accts := 'N';
623
624 error_str := '013';
625 FOR K in 1..l_g_pah_tbl.count LOOP -- { K Loop to parse the account records
626
627 IF ( l_g_pah_tbl(K).instance_party_id = l_instance_party_id AND
628 l_g_pah_tbl(K).active_end_date IS NULL ) THEN -- { accounts for the same party
629 l_num_of_acct_records := l_num_of_acct_records + 1;
630 l_acct_rec_index := K;
631 /*
635 l_old_acct := l_g_pah_tbl(K).party_account_id;
632 IF l_g_pah_tbl(K).active_end_date IS NULL THEN
633
634 IF l_old_acct IS NULL THEN
636 ELSE
637 IF l_pty_with_diff_accts = 'N' AND
638 l_old_acct <> l_g_pah_tbl(K).party_account_id THEN
639 l_pty_with_diff_accts := 'Y';
640 l_msg_string := 'CSI: pty with diff accts : '||
641 to_char( l_old_acct ) || ' : ' ||
642 to_char( l_g_pah_tbl(K).party_account_id ) ;
643 write_to_cr_log( l_msg_string );
644 END IF;
645 END IF;
646
647 END IF;
648 */
649
650 IF ( NUM_COL1_ORIG_LIST(I) <> NUM_COL1_NEW_LIST(I) AND
651 l_g_pah_tbl(K).party_account_id = NUM_COL1_ORIG_LIST(I) AND
652 l_g_pah_tbl(K).relationship_type_code = 'OWNER' ) THEN
653
654 l_owner_update := 'Y';
655
656 ELSIF ( ( l_g_pah_tbl(K).bill_to_address = NUM_COL2_ORIG_LIST(I) AND
657 NUM_COL2_ORIG_LIST(I) <> NUM_COL2_NEW_LIST(I) ) OR
658 ( l_g_pah_tbl(K).ship_to_address = NUM_COL3_ORIG_LIST(I) AND
659 NUM_COL3_ORIG_LIST(I) <> NUM_COL3_NEW_LIST(I) ) ) THEN
660
661 l_address_only_upd := 'Y';
662
663 END IF;
664
665 END IF; -- } accounts for the same party
666
667 END LOOP; -- } K Loop to parse the account records
668
669 l_msg_string :=
670 'CSI: inst_party_id, no_of_acct, acct_rec_idx, pty_with_diff_Acct, l_add_upd, l_owner_upd ' ||
671 to_char(l_instance_party_id) || ' : '||
672 to_char(l_num_of_acct_records) || ' : '||
673 to_char(l_acct_rec_index) || ' : '||
674 l_pty_with_diff_accts || ' : '||
675 l_address_only_upd || ' : '||
676 l_owner_update ;
677 write_to_cr_log( l_msg_string );
678
679 error_str := '014';
680 IF l_address_only_upd = 'Y' THEN -- { if address_only_upd is true
681 -- Same Party record
682 -- move the account record to update table with changed values
683 error_str := '015';
684 l_pty_idx := l_pty_idx + 1;
685 p_u_party_tbl(l_pty_idx).instance_party_id := l_g_ph_tbl(J).instance_party_id;
686 p_u_party_tbl(l_pty_idx).instance_id := l_g_ph_tbl(J).instance_id;
687 p_u_party_tbl(l_pty_idx).party_source_table := l_g_ph_tbl(J).party_source_table;
688 p_u_party_tbl(l_pty_idx).party_id := l_g_ph_tbl(J).party_id;
689 p_u_party_tbl(l_pty_idx).relationship_type_code := l_g_ph_tbl(J).relationship_type_code;
690 p_u_party_tbl(l_pty_idx).contact_flag := l_g_ph_tbl(J).contact_flag;
691 p_u_party_tbl(l_pty_idx).contact_ip_id := l_g_ph_tbl(J).contact_ip_id;
692 p_u_party_tbl(l_pty_idx).active_start_date := FND_API.G_MISS_DATE;
693 p_u_party_tbl(l_pty_idx).active_end_date := FND_API.G_MISS_DATE;
694 p_u_party_tbl(l_pty_idx).context := l_g_ph_tbl(J).context;
695 p_u_party_tbl(l_pty_idx).attribute1 := l_g_ph_tbl(J).attribute1;
696 p_u_party_tbl(l_pty_idx).attribute2 := l_g_ph_tbl(J).attribute2;
697 p_u_party_tbl(l_pty_idx).attribute3 := l_g_ph_tbl(J).attribute3;
698 p_u_party_tbl(l_pty_idx).attribute4 := l_g_ph_tbl(J).attribute4;
699 p_u_party_tbl(l_pty_idx).attribute5 := l_g_ph_tbl(J).attribute5;
700 p_u_party_tbl(l_pty_idx).attribute6 := l_g_ph_tbl(J).attribute6;
701 p_u_party_tbl(l_pty_idx).attribute7 := l_g_ph_tbl(J).attribute7;
702 p_u_party_tbl(l_pty_idx).attribute8 := l_g_ph_tbl(J).attribute8;
703 p_u_party_tbl(l_pty_idx).attribute9 := l_g_ph_tbl(J).attribute9;
704 p_u_party_tbl(l_pty_idx).attribute10 := l_g_ph_tbl(J).attribute10;
705 p_u_party_tbl(l_pty_idx).attribute11 := l_g_ph_tbl(J).attribute11;
706 p_u_party_tbl(l_pty_idx).attribute12 := l_g_ph_tbl(J).attribute12;
707 p_u_party_tbl(l_pty_idx).attribute13 := l_g_ph_tbl(J).attribute13;
711 p_u_party_tbl(l_pty_idx).primary_flag := l_g_ph_tbl(J).primary_flag;
708 p_u_party_tbl(l_pty_idx).attribute14 := l_g_ph_tbl(J).attribute14;
709 p_u_party_tbl(l_pty_idx).attribute15 := l_g_ph_tbl(J).attribute15;
710 p_u_party_tbl(l_pty_idx).object_version_number := l_g_ph_tbl(J).object_version_number;
712 p_u_party_tbl(l_pty_idx).preferred_flag := l_g_ph_tbl(J).preferred_flag;
713 p_u_party_tbl(l_pty_idx).call_contracts := FND_API.G_FALSE;
714
715 -- move the account record to update table
716 l_acct_idx := l_acct_idx + 1;
717 p_u_party_account_tbl(l_acct_idx).ip_account_id := l_g_pah_tbl(l_acct_rec_index).ip_account_id;
718 p_u_party_account_tbl(l_acct_idx).parent_tbl_index := l_pty_idx;
719 p_u_party_account_tbl(l_acct_idx).instance_party_id := l_g_pah_tbl(l_acct_rec_index).instance_party_id;
720 IF l_g_pah_tbl(l_acct_rec_index).party_account_id = NUM_COL1_ORIG_LIST(I) THEN
721 p_u_party_account_tbl(l_acct_idx).party_account_id := NUM_COL1_NEW_LIST(I);
722 p_u_party_tbl(l_pty_idx).party_id := l_to_party_id;--fix for bug 4284460
723 ELSE
724 p_u_party_account_tbl(l_acct_idx).party_account_id := l_g_pah_tbl(l_acct_rec_index).party_account_id;
725 END IF;
726
727 IF l_g_pah_tbl(l_acct_rec_index).bill_to_address = NUM_COL2_ORIG_LIST(I) THEN
728 p_u_party_account_tbl(l_acct_idx).bill_to_address := NUM_COL2_NEW_LIST(I);
729 ELSE
730 p_u_party_account_tbl(l_acct_idx).bill_to_address := l_g_pah_tbl(l_acct_rec_index).bill_to_address;
731 END IF;
732
733 IF l_g_pah_tbl(l_acct_rec_index).ship_to_address = NUM_COL3_ORIG_LIST(I) THEN
734 p_u_party_account_tbl(l_acct_idx).ship_to_address := NUM_COL3_NEW_LIST(I);
735 ELSE
736 p_u_party_account_tbl(l_acct_idx).ship_to_address := l_g_pah_tbl(l_acct_rec_index).ship_to_address;
737 END IF;
738
739 p_u_party_account_tbl(l_acct_idx).relationship_type_code := l_g_pah_tbl(l_acct_rec_index).relationship_type_code;
740 p_u_party_account_tbl(l_acct_idx).active_start_date := FND_API.G_MISS_DATE;
741 p_u_party_account_tbl(l_acct_idx).active_end_date := FND_API.G_MISS_DATE;
742 p_u_party_account_tbl(l_acct_idx).context := l_g_pah_tbl(l_acct_rec_index).context;
743 p_u_party_account_tbl(l_acct_idx).attribute1 := l_g_pah_tbl(l_acct_rec_index).attribute1;
744 p_u_party_account_tbl(l_acct_idx).attribute2 := l_g_pah_tbl(l_acct_rec_index).attribute2;
745 p_u_party_account_tbl(l_acct_idx).attribute3 := l_g_pah_tbl(l_acct_rec_index).attribute3;
746 p_u_party_account_tbl(l_acct_idx).attribute4 := l_g_pah_tbl(l_acct_rec_index).attribute4;
747 p_u_party_account_tbl(l_acct_idx).attribute5 := l_g_pah_tbl(l_acct_rec_index).attribute5;
748 p_u_party_account_tbl(l_acct_idx).attribute6 := l_g_pah_tbl(l_acct_rec_index).attribute6;
749 p_u_party_account_tbl(l_acct_idx).attribute7 := l_g_pah_tbl(l_acct_rec_index).attribute7;
750 p_u_party_account_tbl(l_acct_idx).attribute8 := l_g_pah_tbl(l_acct_rec_index).attribute8;
751 p_u_party_account_tbl(l_acct_idx).attribute9 := l_g_pah_tbl(l_acct_rec_index).attribute9;
752 p_u_party_account_tbl(l_acct_idx).attribute10 := l_g_pah_tbl(l_acct_rec_index).attribute10;
753 p_u_party_account_tbl(l_acct_idx).attribute11 := l_g_pah_tbl(l_acct_rec_index).attribute11;
754 p_u_party_account_tbl(l_acct_idx).attribute12 := l_g_pah_tbl(l_acct_rec_index).attribute12;
755 p_u_party_account_tbl(l_acct_idx).attribute13 := l_g_pah_tbl(l_acct_rec_index).attribute13;
756 p_u_party_account_tbl(l_acct_idx).attribute14 := l_g_pah_tbl(l_acct_rec_index).attribute14;
757 p_u_party_account_tbl(l_acct_idx).attribute15 := l_g_pah_tbl(l_acct_rec_index).attribute15;
758 p_u_party_account_tbl(l_acct_idx).object_version_number := l_g_pah_tbl(l_acct_rec_index).object_version_number;
759 p_u_party_account_tbl(l_acct_idx).call_contracts := FND_API.G_FALSE;
760 ELSE -- } address_only { Otherwise
761 IF ( l_num_of_acct_records = 1 ) THEN -- { if one record for the party
762 -- IF ( l_acct_rec_index > 0 ) THEN -- if account index is known
763 IF ( l_acct_rec_index > 0 ) AND
764 ( l_g_pah_tbl(l_acct_rec_index).party_account_id = NUM_COL1_ORIG_LIST(I) )
765 THEN -- { if account index is known and it needs to be modified then
766 -- move the party record to update table with l_to_party_id
767 -- move the account record to update table with changed values
768
769 -- move the party record to update table
770 error_str := '015';
771 l_pty_idx := l_pty_idx + 1;
775 p_u_party_tbl(l_pty_idx).party_id := l_g_ph_tbl(J).party_id; --Fix for Bug 4284460
772 p_u_party_tbl(l_pty_idx).instance_party_id := l_g_ph_tbl(J).instance_party_id;
773 p_u_party_tbl(l_pty_idx).instance_id := l_g_ph_tbl(J).instance_id;
774 p_u_party_tbl(l_pty_idx).party_source_table := l_g_ph_tbl(J).party_source_table;
776 p_u_party_tbl(l_pty_idx).relationship_type_code := l_g_ph_tbl(J).relationship_type_code;
777 p_u_party_tbl(l_pty_idx).contact_flag := l_g_ph_tbl(J).contact_flag;
778 p_u_party_tbl(l_pty_idx).contact_ip_id := l_g_ph_tbl(J).contact_ip_id;
779 p_u_party_tbl(l_pty_idx).active_start_date := FND_API.G_MISS_DATE;
780 p_u_party_tbl(l_pty_idx).active_end_date := FND_API.G_MISS_DATE;
781 p_u_party_tbl(l_pty_idx).context := l_g_ph_tbl(J).context;
782 p_u_party_tbl(l_pty_idx).attribute1 := l_g_ph_tbl(J).attribute1;
783 p_u_party_tbl(l_pty_idx).attribute2 := l_g_ph_tbl(J).attribute2;
784 p_u_party_tbl(l_pty_idx).attribute3 := l_g_ph_tbl(J).attribute3;
785 p_u_party_tbl(l_pty_idx).attribute4 := l_g_ph_tbl(J).attribute4;
786 p_u_party_tbl(l_pty_idx).attribute5 := l_g_ph_tbl(J).attribute5;
787 p_u_party_tbl(l_pty_idx).attribute6 := l_g_ph_tbl(J).attribute6;
788 p_u_party_tbl(l_pty_idx).attribute7 := l_g_ph_tbl(J).attribute7;
789 p_u_party_tbl(l_pty_idx).attribute8 := l_g_ph_tbl(J).attribute8;
790 p_u_party_tbl(l_pty_idx).attribute9 := l_g_ph_tbl(J).attribute9;
791 p_u_party_tbl(l_pty_idx).attribute10 := l_g_ph_tbl(J).attribute10;
792 p_u_party_tbl(l_pty_idx).attribute11 := l_g_ph_tbl(J).attribute11;
793 p_u_party_tbl(l_pty_idx).attribute12 := l_g_ph_tbl(J).attribute12;
794 p_u_party_tbl(l_pty_idx).attribute13 := l_g_ph_tbl(J).attribute13;
795 p_u_party_tbl(l_pty_idx).attribute14 := l_g_ph_tbl(J).attribute14;
796 p_u_party_tbl(l_pty_idx).attribute15 := l_g_ph_tbl(J).attribute15;
797 p_u_party_tbl(l_pty_idx).object_version_number := l_g_ph_tbl(J).object_version_number;
798 p_u_party_tbl(l_pty_idx).primary_flag := l_g_ph_tbl(J).primary_flag;
799 p_u_party_tbl(l_pty_idx).preferred_flag := l_g_ph_tbl(J).preferred_flag;
800 p_u_party_tbl(l_pty_idx).call_contracts := FND_API.G_FALSE;
801
802 -- move the account record to update table
803 l_acct_idx := l_acct_idx + 1;
804 p_u_party_account_tbl(l_acct_idx).ip_account_id := l_g_pah_tbl(l_acct_rec_index).ip_account_id;
805 p_u_party_account_tbl(l_acct_idx).parent_tbl_index := l_pty_idx;
806 p_u_party_account_tbl(l_acct_idx).instance_party_id := l_g_pah_tbl(l_acct_rec_index).instance_party_id;
807 IF l_g_pah_tbl(l_acct_rec_index).party_account_id = NUM_COL1_ORIG_LIST(I) THEN
808 p_u_party_account_tbl(l_acct_idx).party_account_id := NUM_COL1_NEW_LIST(I);
809 p_u_party_tbl(l_pty_idx).party_id := l_to_party_id; --Fix for Bug 4284460
810 ELSE
811 p_u_party_account_tbl(l_acct_idx).party_account_id := l_g_pah_tbl(l_acct_rec_index).party_account_id;
812 END IF;
813
814 IF l_g_pah_tbl(l_acct_rec_index).bill_to_address = NUM_COL2_ORIG_LIST(I) THEN
815 p_u_party_account_tbl(l_acct_idx).bill_to_address := NUM_COL2_NEW_LIST(I);
816 ELSE
817 p_u_party_account_tbl(l_acct_idx).bill_to_address := l_g_pah_tbl(l_acct_rec_index).bill_to_address;
818 END IF;
819
820 IF l_g_pah_tbl(l_acct_rec_index).ship_to_address = NUM_COL3_ORIG_LIST(I) THEN
821 p_u_party_account_tbl(l_acct_idx).ship_to_address := NUM_COL3_NEW_LIST(I);
822 ELSE
823 p_u_party_account_tbl(l_acct_idx).ship_to_address := l_g_pah_tbl(l_acct_rec_index).ship_to_address;
824 END IF;
825
826 p_u_party_account_tbl(l_acct_idx).relationship_type_code := l_g_pah_tbl(l_acct_rec_index).relationship_type_code;
827 p_u_party_account_tbl(l_acct_idx).active_start_date := FND_API.G_MISS_DATE;
828 p_u_party_account_tbl(l_acct_idx).active_end_date := FND_API.G_MISS_DATE;
829 p_u_party_account_tbl(l_acct_idx).context := l_g_pah_tbl(l_acct_rec_index).context;
830 p_u_party_account_tbl(l_acct_idx).attribute1 := l_g_pah_tbl(l_acct_rec_index).attribute1;
831 p_u_party_account_tbl(l_acct_idx).attribute2 := l_g_pah_tbl(l_acct_rec_index).attribute2;
832 p_u_party_account_tbl(l_acct_idx).attribute3 := l_g_pah_tbl(l_acct_rec_index).attribute3;
833 p_u_party_account_tbl(l_acct_idx).attribute4 := l_g_pah_tbl(l_acct_rec_index).attribute4;
834 p_u_party_account_tbl(l_acct_idx).attribute5 := l_g_pah_tbl(l_acct_rec_index).attribute5;
838 p_u_party_account_tbl(l_acct_idx).attribute9 := l_g_pah_tbl(l_acct_rec_index).attribute9;
835 p_u_party_account_tbl(l_acct_idx).attribute6 := l_g_pah_tbl(l_acct_rec_index).attribute6;
836 p_u_party_account_tbl(l_acct_idx).attribute7 := l_g_pah_tbl(l_acct_rec_index).attribute7;
837 p_u_party_account_tbl(l_acct_idx).attribute8 := l_g_pah_tbl(l_acct_rec_index).attribute8;
839 p_u_party_account_tbl(l_acct_idx).attribute10 := l_g_pah_tbl(l_acct_rec_index).attribute10;
840 p_u_party_account_tbl(l_acct_idx).attribute11 := l_g_pah_tbl(l_acct_rec_index).attribute11;
841 p_u_party_account_tbl(l_acct_idx).attribute12 := l_g_pah_tbl(l_acct_rec_index).attribute12;
842 p_u_party_account_tbl(l_acct_idx).attribute13 := l_g_pah_tbl(l_acct_rec_index).attribute13;
843 p_u_party_account_tbl(l_acct_idx).attribute14 := l_g_pah_tbl(l_acct_rec_index).attribute14;
844 p_u_party_account_tbl(l_acct_idx).attribute15 := l_g_pah_tbl(l_acct_rec_index).attribute15;
845 p_u_party_account_tbl(l_acct_idx).object_version_number := l_g_pah_tbl(l_acct_rec_index).object_version_number;
846 p_u_party_account_tbl(l_acct_idx).call_contracts := FND_API.G_FALSE;
847
848 END IF; -- } if account index is known and account needs to be modified
849 ELSIF ( l_num_of_acct_records > 1 ) THEN -- } account records = 1 { account records for party > 1
850 IF l_owner_update = 'Y' THEN -- { l_owner_update is 'Y'
851 IF l_pty_with_diff_accts = 'Y' THEN -- { l_pty_with_diff_accts 1
852 -- move owner party record with the l_to_party_id
853 -- create a new party record with previous party_id
854 -- loop
855 -- if account is to be changed
856 -- set the instance_party_id from the l_g_ph record
857 -- else
858 -- set the instance_party_id to the newly created one
859 -- end if
860 -- move the account record
861 -- end loop
862
863 -- move the party record with l_to_party_id
864 error_str := '016';
865 l_pty_idx := l_pty_idx + 1;
866 p_u_party_tbl(l_pty_idx).instance_party_id := l_g_ph_tbl(J).instance_party_id;
867 p_u_party_tbl(l_pty_idx).instance_id := l_g_ph_tbl(J).instance_id;
868 p_u_party_tbl(l_pty_idx).party_source_table := l_g_ph_tbl(J).party_source_table;
869 p_u_party_tbl(l_pty_idx).party_id := l_to_party_id;
870 p_u_party_tbl(l_pty_idx).relationship_type_code := l_g_ph_tbl(J).relationship_type_code;
871 p_u_party_tbl(l_pty_idx).contact_flag := l_g_ph_tbl(J).contact_flag;
872 p_u_party_tbl(l_pty_idx).contact_ip_id := l_g_ph_tbl(J).contact_ip_id;
873 p_u_party_tbl(l_pty_idx).active_start_date := FND_API.G_MISS_DATE;
874 p_u_party_tbl(l_pty_idx).active_end_date := FND_API.G_MISS_DATE;
875 p_u_party_tbl(l_pty_idx).context := l_g_ph_tbl(J).context;
876 p_u_party_tbl(l_pty_idx).attribute1 := l_g_ph_tbl(J).attribute1;
877 p_u_party_tbl(l_pty_idx).attribute2 := l_g_ph_tbl(J).attribute2;
878 p_u_party_tbl(l_pty_idx).attribute3 := l_g_ph_tbl(J).attribute3;
879 p_u_party_tbl(l_pty_idx).attribute4 := l_g_ph_tbl(J).attribute4;
880 p_u_party_tbl(l_pty_idx).attribute5 := l_g_ph_tbl(J).attribute5;
881 p_u_party_tbl(l_pty_idx).attribute6 := l_g_ph_tbl(J).attribute6;
882 p_u_party_tbl(l_pty_idx).attribute7 := l_g_ph_tbl(J).attribute7;
883 p_u_party_tbl(l_pty_idx).attribute8 := l_g_ph_tbl(J).attribute8;
884 p_u_party_tbl(l_pty_idx).attribute9 := l_g_ph_tbl(J).attribute9;
885 p_u_party_tbl(l_pty_idx).attribute10 := l_g_ph_tbl(J).attribute10;
886 p_u_party_tbl(l_pty_idx).attribute11 := l_g_ph_tbl(J).attribute11;
887 p_u_party_tbl(l_pty_idx).attribute12 := l_g_ph_tbl(J).attribute12;
888 p_u_party_tbl(l_pty_idx).attribute13 := l_g_ph_tbl(J).attribute13;
889 p_u_party_tbl(l_pty_idx).attribute14 := l_g_ph_tbl(J).attribute14;
890 p_u_party_tbl(l_pty_idx).attribute15 := l_g_ph_tbl(J).attribute15;
891 p_u_party_tbl(l_pty_idx).object_version_number := l_g_ph_tbl(J).object_version_number;
892 p_u_party_tbl(l_pty_idx).primary_flag := l_g_ph_tbl(J).primary_flag;
893 p_u_party_tbl(l_pty_idx).preferred_flag := l_g_ph_tbl(J).preferred_flag;
894 p_u_party_tbl(l_pty_idx).call_contracts := FND_API.G_FALSE;
895
896 -- create new party record with l_g_ph_tbl party_id
897 l_pty_idx := l_pty_idx + 1;
901 p_u_party_tbl(l_pty_idx).party_id := l_g_ph_tbl(J).party_id;
898 p_u_party_tbl(l_pty_idx).instance_party_id := FND_API.G_MISS_NUM;
899 p_u_party_tbl(l_pty_idx).instance_id := l_g_ph_tbl(J).instance_id;
900 p_u_party_tbl(l_pty_idx).party_source_table := l_g_ph_tbl(J).party_source_table;
902 p_u_party_tbl(l_pty_idx).relationship_type_code := l_prof_reltype_code;
903 p_u_party_tbl(l_pty_idx).contact_flag := l_g_ph_tbl(J).contact_flag;
904 p_u_party_tbl(l_pty_idx).contact_ip_id := l_g_ph_tbl(J).contact_ip_id;
905 p_u_party_tbl(l_pty_idx).active_start_date := FND_API.G_MISS_DATE;
906 p_u_party_tbl(l_pty_idx).active_end_date := FND_API.G_MISS_DATE;
907 p_u_party_tbl(l_pty_idx).context := l_g_ph_tbl(J).context;
908 p_u_party_tbl(l_pty_idx).attribute1 := l_g_ph_tbl(J).attribute1;
909 p_u_party_tbl(l_pty_idx).attribute2 := l_g_ph_tbl(J).attribute2;
910 p_u_party_tbl(l_pty_idx).attribute3 := l_g_ph_tbl(J).attribute3;
911 p_u_party_tbl(l_pty_idx).attribute4 := l_g_ph_tbl(J).attribute4;
912 p_u_party_tbl(l_pty_idx).attribute5 := l_g_ph_tbl(J).attribute5;
913 p_u_party_tbl(l_pty_idx).attribute6 := l_g_ph_tbl(J).attribute6;
914 p_u_party_tbl(l_pty_idx).attribute7 := l_g_ph_tbl(J).attribute7;
915 p_u_party_tbl(l_pty_idx).attribute8 := l_g_ph_tbl(J).attribute8;
916 p_u_party_tbl(l_pty_idx).attribute9 := l_g_ph_tbl(J).attribute9;
917 p_u_party_tbl(l_pty_idx).attribute10 := l_g_ph_tbl(J).attribute10;
918 p_u_party_tbl(l_pty_idx).attribute11 := l_g_ph_tbl(J).attribute11;
919 p_u_party_tbl(l_pty_idx).attribute12 := l_g_ph_tbl(J).attribute12;
920 p_u_party_tbl(l_pty_idx).attribute13 := l_g_ph_tbl(J).attribute13;
921 p_u_party_tbl(l_pty_idx).attribute14 := l_g_ph_tbl(J).attribute14;
922 p_u_party_tbl(l_pty_idx).attribute15 := l_g_ph_tbl(J).attribute15;
923 p_u_party_tbl(l_pty_idx).object_version_number := l_g_ph_tbl(J).object_version_number;
924 p_u_party_tbl(l_pty_idx).primary_flag := 'N';
925 p_u_party_tbl(l_pty_idx).preferred_flag := l_g_ph_tbl(J).preferred_flag;
926 p_u_party_tbl(l_pty_idx).call_contracts := FND_API.G_FALSE;
927
928 FOR K in 1..l_g_pah_tbl.count LOOP -- { K Loop
929 IF ( l_g_pah_tbl(K).instance_party_id = l_instance_party_id AND
930 l_g_pah_tbl(K).active_end_date IS NULL ) THEN -- { accounts for the same party
931 -- move the account record to update table
932 l_acct_idx := l_acct_idx + 1;
933 p_u_party_account_tbl(l_acct_idx).ip_account_id := l_g_pah_tbl(K).ip_account_id;
934
935 IF ( l_g_pah_tbl(K).party_account_id = NUM_COL1_ORIG_LIST(I) OR
936 l_g_pah_tbl(K).bill_to_address = NUM_COL2_ORIG_LIST(I) OR
937 l_g_pah_tbl(K).ship_to_address = NUM_COL3_ORIG_LIST(I) ) THEN -- { if the record needs update
938 p_u_party_account_tbl(l_acct_idx).parent_tbl_index := l_pty_idx-1;
939 p_u_party_account_tbl(l_acct_idx).instance_party_id := l_g_pah_tbl(K).instance_party_id;
940 ELSE -- } if record needs update { if record needs no update
941 p_u_party_account_tbl(l_acct_idx).parent_tbl_index := l_pty_idx;
942 p_u_party_account_tbl(l_acct_idx).instance_party_id := FND_API.G_MISS_NUM;
943 END IF; -- } if the record needs no update
944
945 IF l_g_pah_tbl(K).party_account_id = NUM_COL1_ORIG_LIST(I) THEN
946 p_u_party_account_tbl(l_acct_idx).party_account_id := NUM_COL1_NEW_LIST(I);
947 ELSE
948 p_u_party_account_tbl(l_acct_idx).party_account_id := l_g_pah_tbl(K).party_account_id;
949 END IF;
950
951 IF l_g_pah_tbl(K).bill_to_address = NUM_COL2_ORIG_LIST(I) THEN
952 p_u_party_account_tbl(l_acct_idx).bill_to_address := NUM_COL2_NEW_LIST(I);
953 ELSE
954 p_u_party_account_tbl(l_acct_idx).bill_to_address := l_g_pah_tbl(K).bill_to_address;
955 END IF;
956
957 IF l_g_pah_tbl(K).ship_to_address = NUM_COL3_ORIG_LIST(I) THEN
958 p_u_party_account_tbl(l_acct_idx).ship_to_address := NUM_COL3_NEW_LIST(I);
959 ELSE
960 p_u_party_account_tbl(l_acct_idx).ship_to_address := l_g_pah_tbl(K).ship_to_address;
961 END IF;
962
966 p_u_party_account_tbl(l_acct_idx).context := l_g_pah_tbl(K).context;
963 p_u_party_account_tbl(l_acct_idx).relationship_type_code := l_g_pah_tbl(K).relationship_type_code;
964 p_u_party_account_tbl(l_acct_idx).active_start_date := FND_API.G_MISS_DATE;
965 p_u_party_account_tbl(l_acct_idx).active_end_date := FND_API.G_MISS_DATE;
967 p_u_party_account_tbl(l_acct_idx).attribute1 := l_g_pah_tbl(K).attribute1;
968 p_u_party_account_tbl(l_acct_idx).attribute2 := l_g_pah_tbl(K).attribute2;
969 p_u_party_account_tbl(l_acct_idx).attribute3 := l_g_pah_tbl(K).attribute3;
970 p_u_party_account_tbl(l_acct_idx).attribute4 := l_g_pah_tbl(K).attribute4;
971 p_u_party_account_tbl(l_acct_idx).attribute5 := l_g_pah_tbl(K).attribute5;
972 p_u_party_account_tbl(l_acct_idx).attribute6 := l_g_pah_tbl(K).attribute6;
973 p_u_party_account_tbl(l_acct_idx).attribute7 := l_g_pah_tbl(K).attribute7;
974 p_u_party_account_tbl(l_acct_idx).attribute8 := l_g_pah_tbl(K).attribute8;
975 p_u_party_account_tbl(l_acct_idx).attribute9 := l_g_pah_tbl(K).attribute9;
976 p_u_party_account_tbl(l_acct_idx).attribute10 := l_g_pah_tbl(K).attribute10;
977 p_u_party_account_tbl(l_acct_idx).attribute11 := l_g_pah_tbl(K).attribute11;
978 p_u_party_account_tbl(l_acct_idx).attribute12 := l_g_pah_tbl(K).attribute12;
979 p_u_party_account_tbl(l_acct_idx).attribute13 := l_g_pah_tbl(K).attribute13;
980 p_u_party_account_tbl(l_acct_idx).attribute14 := l_g_pah_tbl(K).attribute14;
981 p_u_party_account_tbl(l_acct_idx).attribute15 := l_g_pah_tbl(K).attribute15;
982 p_u_party_account_tbl(l_acct_idx).object_version_number := l_g_pah_tbl(K).object_version_number;
983 p_u_party_account_tbl(l_acct_idx).call_contracts := FND_API.G_FALSE;
984 END IF; -- } accounts for the same party
985 END LOOP; -- } K Loop
986
987 ELSE -- } l_pty_with_diff_accts = 'Y' 1 { l_pty_with_diff_accts = 'N' 1
988 -- move the party record to update table
989 error_str := '017';
990 l_pty_idx := l_pty_idx + 1;
991 p_u_party_tbl(l_pty_idx).instance_party_id := l_g_ph_tbl(J).instance_party_id;
992 p_u_party_tbl(l_pty_idx).instance_id := l_g_ph_tbl(J).instance_id;
993 p_u_party_tbl(l_pty_idx).party_source_table := l_g_ph_tbl(J).party_source_table;
994 p_u_party_tbl(l_pty_idx).party_id := l_g_ph_tbl(J).party_id; --Fix for Bug 4284460
995 p_u_party_tbl(l_pty_idx).relationship_type_code := l_g_ph_tbl(J).relationship_type_code;
996 p_u_party_tbl(l_pty_idx).contact_flag := l_g_ph_tbl(J).contact_flag;
997 p_u_party_tbl(l_pty_idx).contact_ip_id := l_g_ph_tbl(J).contact_ip_id;
998 p_u_party_tbl(l_pty_idx).active_start_date := FND_API.G_MISS_DATE;
999 p_u_party_tbl(l_pty_idx).active_end_date := FND_API.G_MISS_DATE;
1000 p_u_party_tbl(l_pty_idx).context := l_g_ph_tbl(J).context;
1001 p_u_party_tbl(l_pty_idx).attribute1 := l_g_ph_tbl(J).attribute1;
1002 p_u_party_tbl(l_pty_idx).attribute2 := l_g_ph_tbl(J).attribute2;
1003 p_u_party_tbl(l_pty_idx).attribute3 := l_g_ph_tbl(J).attribute3;
1004 p_u_party_tbl(l_pty_idx).attribute4 := l_g_ph_tbl(J).attribute4;
1005 p_u_party_tbl(l_pty_idx).attribute5 := l_g_ph_tbl(J).attribute5;
1006 p_u_party_tbl(l_pty_idx).attribute6 := l_g_ph_tbl(J).attribute6;
1007 p_u_party_tbl(l_pty_idx).attribute7 := l_g_ph_tbl(J).attribute7;
1008 p_u_party_tbl(l_pty_idx).attribute8 := l_g_ph_tbl(J).attribute8;
1009 p_u_party_tbl(l_pty_idx).attribute9 := l_g_ph_tbl(J).attribute9;
1010 p_u_party_tbl(l_pty_idx).attribute10 := l_g_ph_tbl(J).attribute10;
1011 p_u_party_tbl(l_pty_idx).attribute11 := l_g_ph_tbl(J).attribute11;
1012 p_u_party_tbl(l_pty_idx).attribute12 := l_g_ph_tbl(J).attribute12;
1013 p_u_party_tbl(l_pty_idx).attribute13 := l_g_ph_tbl(J).attribute13;
1014 p_u_party_tbl(l_pty_idx).attribute14 := l_g_ph_tbl(J).attribute14;
1015 p_u_party_tbl(l_pty_idx).attribute15 := l_g_ph_tbl(J).attribute15;
1016 p_u_party_tbl(l_pty_idx).object_version_number := l_g_ph_tbl(J).object_version_number;
1017 p_u_party_tbl(l_pty_idx).primary_flag := l_g_ph_tbl(J).primary_flag;
1018 p_u_party_tbl(l_pty_idx).preferred_flag := l_g_ph_tbl(J).preferred_flag;
1022 IF ( l_g_pah_tbl(K).instance_party_id = l_instance_party_id AND
1019 p_u_party_tbl(l_pty_idx).call_contracts := FND_API.G_FALSE;
1020
1021 FOR K in 1..l_g_pah_tbl.count LOOP -- { K Loop
1023 l_g_pah_tbl(K).active_end_date IS NULL AND
1024 --Fix for Bug 4284460
1025 (l_g_pah_tbl(K).party_account_id = NUM_COL1_ORIG_LIST(I) OR
1026 l_g_pah_tbl(K).bill_to_address = NUM_COL2_ORIG_LIST(I) OR -- { accounts for the same party
1027 l_g_pah_tbl(K).ship_to_address = NUM_COL3_ORIG_LIST(I))) THEN -- { accounts for the same party
1028 -- move the account record to update table
1029 l_acct_idx := l_acct_idx + 1;
1030 p_u_party_account_tbl(l_acct_idx).ip_account_id := l_g_pah_tbl(K).ip_account_id;
1031 p_u_party_account_tbl(l_acct_idx).parent_tbl_index := l_pty_idx;
1032 p_u_party_account_tbl(l_acct_idx).instance_party_id := l_g_pah_tbl(K).instance_party_id;
1033 IF l_g_pah_tbl(K).party_account_id = NUM_COL1_ORIG_LIST(I) THEN
1034 p_u_party_account_tbl(l_acct_idx).party_account_id := NUM_COL1_NEW_LIST(I);
1035 p_u_party_tbl(l_pty_idx).party_id := l_to_party_id; --Fix for Bug 4284460
1036 ELSE
1037 p_u_party_account_tbl(l_acct_idx).party_account_id := l_g_pah_tbl(K).party_account_id;
1038 END IF;
1039
1040 IF l_g_pah_tbl(K).bill_to_address = NUM_COL2_ORIG_LIST(I) THEN
1041 p_u_party_account_tbl(l_acct_idx).bill_to_address := NUM_COL2_NEW_LIST(I);
1042 ELSE
1043 p_u_party_account_tbl(l_acct_idx).bill_to_address := l_g_pah_tbl(K).bill_to_address;
1044 END IF;
1045
1046 IF l_g_pah_tbl(K).ship_to_address = NUM_COL3_ORIG_LIST(I) THEN
1047 p_u_party_account_tbl(l_acct_idx).ship_to_address := NUM_COL3_NEW_LIST(I);
1048 ELSE
1049 p_u_party_account_tbl(l_acct_idx).ship_to_address := l_g_pah_tbl(K).ship_to_address;
1050 END IF;
1051
1052 p_u_party_account_tbl(l_acct_idx).relationship_type_code := l_g_pah_tbl(K).relationship_type_code;
1053 p_u_party_account_tbl(l_acct_idx).active_start_date := FND_API.G_MISS_DATE;
1054 p_u_party_account_tbl(l_acct_idx).active_end_date := FND_API.G_MISS_DATE;
1055 p_u_party_account_tbl(l_acct_idx).context := l_g_pah_tbl(K).context;
1056 p_u_party_account_tbl(l_acct_idx).attribute1 := l_g_pah_tbl(K).attribute1;
1057 p_u_party_account_tbl(l_acct_idx).attribute2 := l_g_pah_tbl(K).attribute2;
1058 p_u_party_account_tbl(l_acct_idx).attribute3 := l_g_pah_tbl(K).attribute3;
1059 p_u_party_account_tbl(l_acct_idx).attribute4 := l_g_pah_tbl(K).attribute4;
1060 p_u_party_account_tbl(l_acct_idx).attribute5 := l_g_pah_tbl(K).attribute5;
1061 p_u_party_account_tbl(l_acct_idx).attribute6 := l_g_pah_tbl(K).attribute6;
1062 p_u_party_account_tbl(l_acct_idx).attribute7 := l_g_pah_tbl(K).attribute7;
1063 p_u_party_account_tbl(l_acct_idx).attribute8 := l_g_pah_tbl(K).attribute8;
1064 p_u_party_account_tbl(l_acct_idx).attribute9 := l_g_pah_tbl(K).attribute9;
1065 p_u_party_account_tbl(l_acct_idx).attribute10 := l_g_pah_tbl(K).attribute10;
1066 p_u_party_account_tbl(l_acct_idx).attribute11 := l_g_pah_tbl(K).attribute11;
1067 p_u_party_account_tbl(l_acct_idx).attribute12 := l_g_pah_tbl(K).attribute12;
1068 p_u_party_account_tbl(l_acct_idx).attribute13 := l_g_pah_tbl(K).attribute13;
1069 p_u_party_account_tbl(l_acct_idx).attribute14 := l_g_pah_tbl(K).attribute14;
1070 p_u_party_account_tbl(l_acct_idx).attribute15 := l_g_pah_tbl(K).attribute15;
1071 p_u_party_account_tbl(l_acct_idx).object_version_number := l_g_pah_tbl(K).object_version_number;
1072 p_u_party_account_tbl(l_acct_idx).call_contracts := FND_API.G_FALSE;
1073 END IF; -- } accounts for the same party
1074 END LOOP; -- } K Loop
1075 END IF; -- } l_pty_with_diff_accts 1
1076 ELSE -- } l_owner_update = 'Y' { l_owner_update = 'N'
1077 --
1078 -- if l_pty_with_diff_accts then
1079 -- move pty record as is
1080 -- create new party record with l_to_party
1081 -- loop
1082 -- if account is to be updated
1083 -- use the new party record index
1084 -- else
1085 -- use the old party record index
1086 -- end if
1090 -- move pty record with new l_to_party
1087 -- move acct record
1088 -- end loop
1089 -- else
1091 -- loop
1092 -- move acct records with updated account_ids
1093 -- end loop
1094 -- end if;
1095 --
1096 IF ( l_pty_with_diff_accts = 'Y' ) THEN -- { l_pty_with_diff_accts = 'Y'
1097 -- move pty record as is
1098 error_str := '018';
1099 l_pty_idx := l_pty_idx + 1;
1100 p_u_party_tbl(l_pty_idx).instance_party_id := l_g_ph_tbl(J).instance_party_id;
1101 p_u_party_tbl(l_pty_idx).instance_id := l_g_ph_tbl(J).instance_id;
1102 p_u_party_tbl(l_pty_idx).party_source_table := l_g_ph_tbl(J).party_source_table;
1103 p_u_party_tbl(l_pty_idx).party_id := l_g_ph_tbl(J).party_id;
1104 p_u_party_tbl(l_pty_idx).relationship_type_code := l_g_ph_tbl(J).relationship_type_code;
1105 p_u_party_tbl(l_pty_idx).contact_flag := l_g_ph_tbl(J).contact_flag;
1106 p_u_party_tbl(l_pty_idx).contact_ip_id := l_g_ph_tbl(J).contact_ip_id;
1107 p_u_party_tbl(l_pty_idx).active_start_date := FND_API.G_MISS_DATE;
1108 p_u_party_tbl(l_pty_idx).active_end_date := FND_API.G_MISS_DATE;
1109 p_u_party_tbl(l_pty_idx).context := l_g_ph_tbl(J).context;
1110 p_u_party_tbl(l_pty_idx).attribute1 := l_g_ph_tbl(J).attribute1;
1111 p_u_party_tbl(l_pty_idx).attribute2 := l_g_ph_tbl(J).attribute2;
1112 p_u_party_tbl(l_pty_idx).attribute3 := l_g_ph_tbl(J).attribute3;
1113 p_u_party_tbl(l_pty_idx).attribute4 := l_g_ph_tbl(J).attribute4;
1114 p_u_party_tbl(l_pty_idx).attribute5 := l_g_ph_tbl(J).attribute5;
1115 p_u_party_tbl(l_pty_idx).attribute6 := l_g_ph_tbl(J).attribute6;
1116 p_u_party_tbl(l_pty_idx).attribute7 := l_g_ph_tbl(J).attribute7;
1117 p_u_party_tbl(l_pty_idx).attribute8 := l_g_ph_tbl(J).attribute8;
1118 p_u_party_tbl(l_pty_idx).attribute9 := l_g_ph_tbl(J).attribute9;
1119 p_u_party_tbl(l_pty_idx).attribute10 := l_g_ph_tbl(J).attribute10;
1120 p_u_party_tbl(l_pty_idx).attribute11 := l_g_ph_tbl(J).attribute11;
1121 p_u_party_tbl(l_pty_idx).attribute12 := l_g_ph_tbl(J).attribute12;
1122 p_u_party_tbl(l_pty_idx).attribute13 := l_g_ph_tbl(J).attribute13;
1123 p_u_party_tbl(l_pty_idx).attribute14 := l_g_ph_tbl(J).attribute14;
1124 p_u_party_tbl(l_pty_idx).attribute15 := l_g_ph_tbl(J).attribute15;
1125 p_u_party_tbl(l_pty_idx).object_version_number := l_g_ph_tbl(J).object_version_number;
1126 p_u_party_tbl(l_pty_idx).primary_flag := l_g_ph_tbl(J).primary_flag;
1127 p_u_party_tbl(l_pty_idx).preferred_flag := l_g_ph_tbl(J).preferred_flag;
1128 p_u_party_tbl(l_pty_idx).call_contracts := FND_API.G_FALSE;
1129
1130 -- create new party record with l_to_party
1131 l_pty_idx := l_pty_idx + 1;
1132 p_u_party_tbl(l_pty_idx).instance_party_id := FND_API.G_MISS_NUM;
1133 p_u_party_tbl(l_pty_idx).instance_id := l_g_ph_tbl(J).instance_id;
1134 p_u_party_tbl(l_pty_idx).party_source_table := l_g_ph_tbl(J).party_source_table;
1135 p_u_party_tbl(l_pty_idx).party_id := l_to_party_id;
1136 p_u_party_tbl(l_pty_idx).relationship_type_code := l_prof_reltype_code;
1137 p_u_party_tbl(l_pty_idx).contact_flag := l_g_ph_tbl(J).contact_flag;
1138 p_u_party_tbl(l_pty_idx).contact_ip_id := l_g_ph_tbl(J).contact_ip_id;
1139 p_u_party_tbl(l_pty_idx).active_start_date := FND_API.G_MISS_DATE;
1140 p_u_party_tbl(l_pty_idx).active_end_date := FND_API.G_MISS_DATE;
1141 p_u_party_tbl(l_pty_idx).context := l_g_ph_tbl(J).context;
1142 p_u_party_tbl(l_pty_idx).attribute1 := l_g_ph_tbl(J).attribute1;
1143 p_u_party_tbl(l_pty_idx).attribute2 := l_g_ph_tbl(J).attribute2;
1144 p_u_party_tbl(l_pty_idx).attribute3 := l_g_ph_tbl(J).attribute3;
1145 p_u_party_tbl(l_pty_idx).attribute4 := l_g_ph_tbl(J).attribute4;
1146 p_u_party_tbl(l_pty_idx).attribute5 := l_g_ph_tbl(J).attribute5;
1147 p_u_party_tbl(l_pty_idx).attribute6 := l_g_ph_tbl(J).attribute6;
1148 p_u_party_tbl(l_pty_idx).attribute7 := l_g_ph_tbl(J).attribute7;
1149 p_u_party_tbl(l_pty_idx).attribute8 := l_g_ph_tbl(J).attribute8;
1150 p_u_party_tbl(l_pty_idx).attribute9 := l_g_ph_tbl(J).attribute9;
1154 p_u_party_tbl(l_pty_idx).attribute13 := l_g_ph_tbl(J).attribute13;
1151 p_u_party_tbl(l_pty_idx).attribute10 := l_g_ph_tbl(J).attribute10;
1152 p_u_party_tbl(l_pty_idx).attribute11 := l_g_ph_tbl(J).attribute11;
1153 p_u_party_tbl(l_pty_idx).attribute12 := l_g_ph_tbl(J).attribute12;
1155 p_u_party_tbl(l_pty_idx).attribute14 := l_g_ph_tbl(J).attribute14;
1156 p_u_party_tbl(l_pty_idx).attribute15 := l_g_ph_tbl(J).attribute15;
1157 p_u_party_tbl(l_pty_idx).object_version_number := l_g_ph_tbl(J).object_version_number;
1158 p_u_party_tbl(l_pty_idx).primary_flag := 'N';
1159 p_u_party_tbl(l_pty_idx).preferred_flag := l_g_ph_tbl(J).preferred_flag;
1160 p_u_party_tbl(l_pty_idx).call_contracts := FND_API.G_FALSE;
1161
1162 FOR K in 1..l_g_pah_tbl.count LOOP -- { K Loop
1163 IF ( l_g_pah_tbl(K).instance_party_id = l_instance_party_id AND
1164 l_g_pah_tbl(K).active_end_date IS NULL ) THEN -- { accounts for the same party
1165 -- move the account record to update table
1166 l_acct_idx := l_acct_idx + 1;
1167 p_u_party_account_tbl(l_acct_idx).ip_account_id := l_g_pah_tbl(K).ip_account_id;
1168
1169 IF ( l_g_pah_tbl(K).party_account_id = NUM_COL1_ORIG_LIST(I) OR
1170 l_g_pah_tbl(K).bill_to_address = NUM_COL2_ORIG_LIST(I) OR
1171 l_g_pah_tbl(K).ship_to_address = NUM_COL3_ORIG_LIST(I) ) THEN -- { if the record needs update
1172 p_u_party_account_tbl(l_acct_idx).parent_tbl_index := l_pty_idx;
1173 p_u_party_account_tbl(l_acct_idx).instance_party_id := FND_API.G_MISS_NUM;
1174 ELSE -- } if record needs update { if record needs no update
1175 p_u_party_account_tbl(l_acct_idx).parent_tbl_index := l_pty_idx-1;
1176 p_u_party_account_tbl(l_acct_idx).instance_party_id := l_g_pah_tbl(K).instance_party_id;
1177 END IF; -- } if the record needs no update
1178
1179 IF l_g_pah_tbl(K).party_account_id = NUM_COL1_ORIG_LIST(I) THEN
1180 p_u_party_account_tbl(l_acct_idx).party_account_id := NUM_COL1_NEW_LIST(I);
1181 ELSE
1182 p_u_party_account_tbl(l_acct_idx).party_account_id := l_g_pah_tbl(K).party_account_id;
1183 END IF;
1184
1185 IF l_g_pah_tbl(K).bill_to_address = NUM_COL2_ORIG_LIST(I) THEN
1186 p_u_party_account_tbl(l_acct_idx).bill_to_address := NUM_COL2_NEW_LIST(I);
1187 ELSE
1188 p_u_party_account_tbl(l_acct_idx).bill_to_address := l_g_pah_tbl(K).bill_to_address;
1189 END IF;
1190
1191 IF l_g_pah_tbl(K).ship_to_address = NUM_COL3_ORIG_LIST(I) THEN
1192 p_u_party_account_tbl(l_acct_idx).ship_to_address := NUM_COL3_NEW_LIST(I);
1193 ELSE
1194 p_u_party_account_tbl(l_acct_idx).ship_to_address := l_g_pah_tbl(K).ship_to_address;
1195 END IF;
1196
1197 p_u_party_account_tbl(l_acct_idx).relationship_type_code := l_g_pah_tbl(K).relationship_type_code;
1198 p_u_party_account_tbl(l_acct_idx).active_start_date := FND_API.G_MISS_DATE;
1199 p_u_party_account_tbl(l_acct_idx).active_end_date := FND_API.G_MISS_DATE;
1200 p_u_party_account_tbl(l_acct_idx).context := l_g_pah_tbl(K).context;
1201 p_u_party_account_tbl(l_acct_idx).attribute1 := l_g_pah_tbl(K).attribute1;
1202 p_u_party_account_tbl(l_acct_idx).attribute2 := l_g_pah_tbl(K).attribute2;
1203 p_u_party_account_tbl(l_acct_idx).attribute3 := l_g_pah_tbl(K).attribute3;
1204 p_u_party_account_tbl(l_acct_idx).attribute4 := l_g_pah_tbl(K).attribute4;
1205 p_u_party_account_tbl(l_acct_idx).attribute5 := l_g_pah_tbl(K).attribute5;
1206 p_u_party_account_tbl(l_acct_idx).attribute6 := l_g_pah_tbl(K).attribute6;
1207 p_u_party_account_tbl(l_acct_idx).attribute7 := l_g_pah_tbl(K).attribute7;
1208 p_u_party_account_tbl(l_acct_idx).attribute8 := l_g_pah_tbl(K).attribute8;
1209 p_u_party_account_tbl(l_acct_idx).attribute9 := l_g_pah_tbl(K).attribute9;
1210 p_u_party_account_tbl(l_acct_idx).attribute10 := l_g_pah_tbl(K).attribute10;
1211 p_u_party_account_tbl(l_acct_idx).attribute11 := l_g_pah_tbl(K).attribute11;
1212 p_u_party_account_tbl(l_acct_idx).attribute12 := l_g_pah_tbl(K).attribute12;
1213 p_u_party_account_tbl(l_acct_idx).attribute13 := l_g_pah_tbl(K).attribute13;
1217 p_u_party_account_tbl(l_acct_idx).call_contracts := FND_API.G_FALSE;
1214 p_u_party_account_tbl(l_acct_idx).attribute14 := l_g_pah_tbl(K).attribute14;
1215 p_u_party_account_tbl(l_acct_idx).attribute15 := l_g_pah_tbl(K).attribute15;
1216 p_u_party_account_tbl(l_acct_idx).object_version_number := l_g_pah_tbl(K).object_version_number;
1218 END IF; -- } accounts for the same party
1219 END LOOP; -- } K Loop
1220
1221 ELSE -- } l_pty_with_diff_accts = 'Y' { l_pty_with_diff_accts = 'N'
1222 -- move the party record to update table
1223 error_str := '019';
1224 l_pty_idx := l_pty_idx + 1;
1225 p_u_party_tbl(l_pty_idx).instance_party_id := l_g_ph_tbl(J).instance_party_id;
1226 p_u_party_tbl(l_pty_idx).instance_id := l_g_ph_tbl(J).instance_id;
1227 p_u_party_tbl(l_pty_idx).party_source_table := l_g_ph_tbl(J).party_source_table;
1228 p_u_party_tbl(l_pty_idx).party_id := l_g_ph_tbl(J).party_id; --Fix for Bug 4284460
1229 p_u_party_tbl(l_pty_idx).relationship_type_code := l_g_ph_tbl(J).relationship_type_code;
1230 p_u_party_tbl(l_pty_idx).contact_flag := l_g_ph_tbl(J).contact_flag;
1231 p_u_party_tbl(l_pty_idx).contact_ip_id := l_g_ph_tbl(J).contact_ip_id;
1232 p_u_party_tbl(l_pty_idx).active_start_date := FND_API.G_MISS_DATE;
1233 p_u_party_tbl(l_pty_idx).active_end_date := FND_API.G_MISS_DATE;
1234 p_u_party_tbl(l_pty_idx).context := l_g_ph_tbl(J).context;
1235 p_u_party_tbl(l_pty_idx).attribute1 := l_g_ph_tbl(J).attribute1;
1236 p_u_party_tbl(l_pty_idx).attribute2 := l_g_ph_tbl(J).attribute2;
1237 p_u_party_tbl(l_pty_idx).attribute3 := l_g_ph_tbl(J).attribute3;
1238 p_u_party_tbl(l_pty_idx).attribute4 := l_g_ph_tbl(J).attribute4;
1239 p_u_party_tbl(l_pty_idx).attribute5 := l_g_ph_tbl(J).attribute5;
1240 p_u_party_tbl(l_pty_idx).attribute6 := l_g_ph_tbl(J).attribute6;
1241 p_u_party_tbl(l_pty_idx).attribute7 := l_g_ph_tbl(J).attribute7;
1242 p_u_party_tbl(l_pty_idx).attribute8 := l_g_ph_tbl(J).attribute8;
1243 p_u_party_tbl(l_pty_idx).attribute9 := l_g_ph_tbl(J).attribute9;
1244 p_u_party_tbl(l_pty_idx).attribute10 := l_g_ph_tbl(J).attribute10;
1245 p_u_party_tbl(l_pty_idx).attribute11 := l_g_ph_tbl(J).attribute11;
1246 p_u_party_tbl(l_pty_idx).attribute12 := l_g_ph_tbl(J).attribute12;
1247 p_u_party_tbl(l_pty_idx).attribute13 := l_g_ph_tbl(J).attribute13;
1248 p_u_party_tbl(l_pty_idx).attribute14 := l_g_ph_tbl(J).attribute14;
1249 p_u_party_tbl(l_pty_idx).attribute15 := l_g_ph_tbl(J).attribute15;
1250 p_u_party_tbl(l_pty_idx).object_version_number := l_g_ph_tbl(J).object_version_number;
1251 p_u_party_tbl(l_pty_idx).primary_flag := l_g_ph_tbl(J).primary_flag;
1252 p_u_party_tbl(l_pty_idx).preferred_flag := l_g_ph_tbl(J).preferred_flag;
1253 p_u_party_tbl(l_pty_idx).call_contracts := FND_API.G_FALSE;
1254
1255 FOR K in 1..l_g_pah_tbl.count LOOP -- { K Loop
1256 IF ( l_g_pah_tbl(K).instance_party_id = l_instance_party_id AND
1257 l_g_pah_tbl(K).active_end_date IS NULL ) THEN -- { accounts for the same party
1258 -- move the account record to update table
1259 l_acct_idx := l_acct_idx + 1;
1260 p_u_party_account_tbl(l_acct_idx).ip_account_id := l_g_pah_tbl(K).ip_account_id;
1261 p_u_party_account_tbl(l_acct_idx).parent_tbl_index := l_pty_idx;
1262 p_u_party_account_tbl(l_acct_idx).instance_party_id := l_g_pah_tbl(K).instance_party_id;
1263 IF l_g_pah_tbl(K).party_account_id = NUM_COL1_ORIG_LIST(I) THEN
1264 p_u_party_account_tbl(l_acct_idx).party_account_id := NUM_COL1_NEW_LIST(I);
1265 p_u_party_tbl(l_pty_idx).party_id := l_to_party_id;--fix for bug 4284460
1266 ELSE
1267 p_u_party_account_tbl(l_acct_idx).party_account_id := l_g_pah_tbl(K).party_account_id;
1268 END IF;
1269
1270 IF l_g_pah_tbl(K).bill_to_address = NUM_COL2_ORIG_LIST(I) THEN
1271 p_u_party_account_tbl(l_acct_idx).bill_to_address := NUM_COL2_NEW_LIST(I);
1272 ELSE
1273 p_u_party_account_tbl(l_acct_idx).bill_to_address := l_g_pah_tbl(K).bill_to_address;
1274 END IF;
1275
1276 IF l_g_pah_tbl(K).ship_to_address = NUM_COL3_ORIG_LIST(I) THEN
1277 p_u_party_account_tbl(l_acct_idx).ship_to_address := NUM_COL3_NEW_LIST(I);
1281
1278 ELSE
1279 p_u_party_account_tbl(l_acct_idx).ship_to_address := l_g_pah_tbl(K).ship_to_address;
1280 END IF;
1282 p_u_party_account_tbl(l_acct_idx).relationship_type_code := l_g_pah_tbl(K).relationship_type_code;
1283 p_u_party_account_tbl(l_acct_idx).active_start_date := FND_API.G_MISS_DATE;
1284 p_u_party_account_tbl(l_acct_idx).active_end_date := FND_API.G_MISS_DATE;
1285 p_u_party_account_tbl(l_acct_idx).context := l_g_pah_tbl(K).context;
1286 p_u_party_account_tbl(l_acct_idx).attribute1 := l_g_pah_tbl(K).attribute1;
1287 p_u_party_account_tbl(l_acct_idx).attribute2 := l_g_pah_tbl(K).attribute2;
1288 p_u_party_account_tbl(l_acct_idx).attribute3 := l_g_pah_tbl(K).attribute3;
1289 p_u_party_account_tbl(l_acct_idx).attribute4 := l_g_pah_tbl(K).attribute4;
1290 p_u_party_account_tbl(l_acct_idx).attribute5 := l_g_pah_tbl(K).attribute5;
1291 p_u_party_account_tbl(l_acct_idx).attribute6 := l_g_pah_tbl(K).attribute6;
1292 p_u_party_account_tbl(l_acct_idx).attribute7 := l_g_pah_tbl(K).attribute7;
1293 p_u_party_account_tbl(l_acct_idx).attribute8 := l_g_pah_tbl(K).attribute8;
1294 p_u_party_account_tbl(l_acct_idx).attribute9 := l_g_pah_tbl(K).attribute9;
1295 p_u_party_account_tbl(l_acct_idx).attribute10 := l_g_pah_tbl(K).attribute10;
1296 p_u_party_account_tbl(l_acct_idx).attribute11 := l_g_pah_tbl(K).attribute11;
1297 p_u_party_account_tbl(l_acct_idx).attribute12 := l_g_pah_tbl(K).attribute12;
1298 p_u_party_account_tbl(l_acct_idx).attribute13 := l_g_pah_tbl(K).attribute13;
1299 p_u_party_account_tbl(l_acct_idx).attribute14 := l_g_pah_tbl(K).attribute14;
1300 p_u_party_account_tbl(l_acct_idx).attribute15 := l_g_pah_tbl(K).attribute15;
1301 p_u_party_account_tbl(l_acct_idx).object_version_number := l_g_pah_tbl(K).object_version_number;
1302 p_u_party_account_tbl(l_acct_idx).call_contracts := FND_API.G_FALSE;
1303 END IF; -- } accounts for the same party
1304 END LOOP; -- } K Loop
1305 END IF; -- } l_pty_with_diff_accts = 'N'
1306 END IF; -- } l_owner_update = 'N'
1307 END IF; -- } Account records for party > 1
1308 END IF; -- } Not Address only
1309 END IF; -- } for party_source table HZ_PARTIES
1310 END LOOP; -- } J Loop
1311
1312 -- Set the instance_rec for the update call
1313 error_str := '021';
1314 p_u_instance_rec.instance_id := INSTANCE_ID_LIST(I);
1315 p_u_instance_rec.object_version_number := l_g_instance_rec.object_version_number;
1316
1317 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
1318 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','location_id'||NUM_COL4_NEW_LIST(I),FALSE);
1319
1320 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
1321 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','install_location_id'||NUM_COL5_NEW_LIST(I),FALSE);
1322
1323 IF NUM_COL5_NEW_LIST(I) IS NOT NULL AND NUM_COL4_NEW_LIST(I) IS NOT NULL THEN
1324
1325 p_u_instance_rec.install_location_id := NUM_COL5_NEW_LIST(I);
1326 p_u_instance_rec.location_id := NUM_COL4_NEW_LIST(I);
1327
1328 END IF;
1329
1330
1331 --
1332 write_to_cr_log( 'CSI:Party and Account Records for update call : ' ||
1333 to_char(p_u_party_tbl.count) || ' : ' ||
1334 to_char(p_u_party_account_tbl.count) );
1335
1336 -- Now update the instance with all new data
1337 error_str := '022';
1338 csi_item_instance_pub.update_item_instance(
1339 p_api_version => 1.0,
1340 p_commit => fnd_api.g_false,
1341 p_init_msg_list => fnd_api.g_true,
1342 p_validation_level => fnd_api.g_valid_level_full,
1343 p_instance_rec => p_u_instance_rec,
1344 p_ext_attrib_values_tbl => p_u_ext_attrib_values_tbl,
1345 p_party_tbl => p_u_party_tbl,
1346 p_account_tbl => p_u_party_account_tbl,
1347 p_pricing_attrib_tbl => p_u_pricing_attrib_tbl,
1348 p_org_assignments_tbl => p_u_org_assignments_tbl,
1349 p_asset_assignment_tbl => p_u_asset_assignment_tbl,
1350 p_txn_rec => p_txn_rec,
1351 x_instance_id_lst => x_instance_id_lst,
1352 x_return_status => l_return_status,
1353 x_msg_count => l_msg_count,
1354 x_msg_data => l_msg_data);
1355 write_to_cr_log( 'return status :'||l_return_status);
1359 IF l_return_status <> 'S' Then
1356
1357 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
1358 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','return_status'||l_return_status,FALSE);
1360 write_to_cr_log( 'Msg Count : '||to_char(l_msg_count));
1361 FOR l_msg_ctr in 1..l_msg_count LOOP
1362 fnd_msg_pub.get ( l_msg_ctr, FND_API.G_FALSE, l_msg_data, l_msg_dummy );
1363 write_to_cr_log( 'Msg : '|| to_char(l_msg_ctr) || ' ' || l_msg_data );
1364 END LOOP;
1365 RAISE l_merge_excep;
1366 END IF;
1367 /* ***commented veto delete for bug 5897064**
1368 --
1369 -- Veto the delete as the history information still exists.
1370 --
1371 l_from_customer_id := num_col1_orig_list(I);
1372 BEGIN
1373 l_is_vetoed := vetoed_list( l_from_customer_id );
1374 EXCEPTION
1375 WHEN no_data_found THEN
1376 BEGIN
1377 ARP_CMERGE_MASTER.veto_delete
1378 (
1379 req_id => req_id,
1380 set_num => set_num,
1381 from_customer_id => l_from_customer_id,
1382 veto_reason => l_veto_reason
1383 );
1384 vetoed_list( l_from_customer_id ) := 'Y';
1385 END;
1386 END; */
1387 END IF; -- } Party account id retrieved is not already updated
1388
1389 END LOOP; -- } I Loop
1390
1391 l_count := l_count + SQL%ROWCOUNT;
1392 IF l_last_fetch THEN -- { 17
1393 EXIT;
1394 END IF; -- } 17
1395
1396 END LOOP; -- } 3 merge records Loop
1397
1398 CLOSE merged_records;
1399
1400 arp_message.set_name('CSI','CSI_ROWS_UPDATED');
1401 arp_message.set_token('NUM_ROWS',to_char(l_count));
1402 arp_message.set_line( 'Done with the update of CSI_IP_ACCOUNTS' );
1403 END IF; -- } 2
1404
1405 arp_message.set_line('CSI_ACCT_MERGE_PKG.CSI_IP_ACCOUNTS_MERGE()-');
1406
1407 EXCEPTION
1408 WHEN OTHERS THEN
1409 CLOSE merged_records;
1410 l_msg_data := error_str || '-' || l_msg_data;
1411 arp_message.set_error('CRM_MERGE.CUSTOMER_PRODUCTS_MERGE', l_msg_data);
1412 RAISE;
1413 END csi_ip_accounts_merge; -- } 1
1414
1415 PROCEDURE csi_systems_b_merge(req_id IN NUMBER,
1416 set_num IN NUMBER,
1417 process_mode IN VARCHAR2) IS
1418
1419 error_str VARCHAR2(3);
1420 no_of_rows NUMBER;
1421 l_system_audit_id NUMBER;
1422 v_transaction_type_id NUMBER;
1423 v_transaction_id NUMBER;
1424 l_profile_val VARCHAR2(30);
1425 v_transaction_exists VARCHAR2(1) := 'N';
1426 v_source_transaction_type VARCHAR2(30) := 'ACCT_MERGE';
1427 l_last_fetch BOOLEAN := FALSE;
1428 l_count NUMBER;
1429
1430 l_msg_string VARCHAR2(256);
1431 v_transaction_cnt NUMBER;
1432
1433 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
1434 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
1435 INDEX BY BINARY_INTEGER;
1436
1437 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
1438
1439 TYPE SYSTEM_ID_LIST_TYPE IS TABLE OF
1440 CSI_SYSTEMS_B.SYSTEM_ID%TYPE
1441 INDEX BY BINARY_INTEGER;
1442
1443 PRIMARY_KEY_ID_LIST SYSTEM_ID_LIST_TYPE;
1444
1445 TYPE CUSTOMER_ID_LIST_TYPE IS TABLE OF
1446 CSI_SYSTEMS_B.CUSTOMER_ID%TYPE
1447 INDEX BY BINARY_INTEGER;
1448
1449 NUM_COL1_ORIG_LIST CUSTOMER_ID_LIST_TYPE;
1450 NUM_COL1_NEW_LIST CUSTOMER_ID_LIST_TYPE;
1451
1452 TYPE BILL_TO_SITE_USE_ID_LIST_TYPE IS TABLE OF
1453 CSI_SYSTEMS_B.BILL_TO_SITE_USE_ID%TYPE
1454 INDEX BY BINARY_INTEGER;
1455
1456 NUM_COL2_ORIG_LIST BILL_TO_SITE_USE_ID_LIST_TYPE;
1457 NUM_COL2_NEW_LIST BILL_TO_SITE_USE_ID_LIST_TYPE;
1458
1459 TYPE SHIP_TO_SITE_USE_ID_LIST_TYPE IS TABLE OF
1460 CSI_SYSTEMS_B.SHIP_TO_SITE_USE_ID%TYPE
1461 INDEX BY BINARY_INTEGER;
1462
1463 NUM_COL3_ORIG_LIST SHIP_TO_SITE_USE_ID_LIST_TYPE;
1464 NUM_COL3_NEW_LIST SHIP_TO_SITE_USE_ID_LIST_TYPE;
1465
1466 CURSOR merged_records IS
1467 SELECT distinct
1468 CUSTOMER_MERGE_HEADER_ID ,
1469 yt.SYSTEM_ID ,
1470 yt.CUSTOMER_ID ,
1471 yt.BILL_TO_SITE_USE_ID ,
1472 yt.SHIP_TO_SITE_USE_ID
1473 FROM CSI_SYSTEMS_B yt,
1474 RA_CUSTOMER_MERGES m
1475 WHERE ( yt.CUSTOMER_ID = m.DUPLICATE_ID OR
1476 yt.BILL_TO_SITE_USE_ID = m.DUPLICATE_SITE_ID OR
1477 yt.SHIP_TO_SITE_USE_ID = m.DUPLICATE_SITE_ID )
1478 AND m.process_flag = 'N'
1479 AND m.request_id = req_id
1480 AND m.set_number = set_num;
1481
1482 I NUMBER;
1483 v_system_history_id NUMBER;
1484 BEGIN
1485
1486 arp_message.set_line('CSI_ACCT_MERGE_PKG.CSI_SYSTEMS_B_MERGE()+');
1487
1488 IF (process_mode = 'LOCK') THEN
1489
1490 write_to_cr_log( 'CSI : csi_systems_b_merge...' || process_mode ) ;
1491
1492 write_to_cr_log( 'Locking the csi_systems_b table' );
1493 arp_message.set_name('AR','AR_LOCKING_TABLE');
1494 arp_message.set_token('TABLE_NAME','CSI_SYSTEMS_B',FALSE);
1495
1499 write_to_cr_log( 'Done Locking the csi_systems_b table' );
1496 OPEN merged_records;
1497 CLOSE merged_records;
1498
1500
1501 ELSE
1502
1503 write_to_cr_log( 'CSI : csi_systems_b_merge.....' || process_mode );
1504
1505 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
1506 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','CSI_SYSTEMS_B',FALSE);
1507 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
1508 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
1509
1510 OPEN merged_records;
1511
1512 LOOP -- { Merged records Loop
1513
1514 FETCH merged_records BULK COLLECT INTO
1515 MERGE_HEADER_ID_LIST ,
1516 PRIMARY_KEY_ID_LIST ,
1517 NUM_COL1_ORIG_LIST ,
1518 NUM_COL2_ORIG_LIST ,
1519 NUM_COL3_ORIG_LIST
1520 LIMIT 1000;
1521
1522 IF merged_records%NOTFOUND THEN
1523 l_last_fetch := TRUE;
1524 END IF;
1525
1526 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
1527 EXIT;
1528 END IF;
1529
1530 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
1531
1532 write_to_cr_log( to_char(i) || 'system : orig1 orig2 orig3 : '||
1533 to_char(NUM_COL1_ORIG_LIST(I)) || '<>' ||
1534 to_char(NUM_COL2_ORIG_LIST(I)) || '<>' ||
1535 to_char(NUM_COL3_ORIG_LIST(I)) );
1536
1537 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
1538 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
1539 NUM_COL3_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL3_ORIG_LIST(I));
1540
1541 write_to_cr_log( to_char(i) || '.new1 new2 new3 : '||
1542 to_char(NUM_COL1_NEW_LIST(I)) || '<>' ||
1543 to_char(NUM_COL2_NEW_LIST(I)) || '<>' ||
1544 to_char(NUM_COL3_NEW_LIST(I)) );
1545
1546 END LOOP;
1547
1548 write_to_cr_log( 'CSI : Before Audit check ');
1549
1550 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN -- { profile value if
1551
1552 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1553 INSERT INTO HZ_CUSTOMER_MERGE_LOG
1554 (
1555 MERGE_LOG_ID ,
1556 TABLE_NAME ,
1557 MERGE_HEADER_ID ,
1558 PRIMARY_KEY_ID ,
1559 NUM_COL1_ORIG ,
1560 NUM_COL1_NEW ,
1561 NUM_COL2_ORIG ,
1562 NUM_COL2_NEW ,
1563 NUM_COL3_ORIG ,
1564 NUM_COL3_NEW ,
1565 ACTION_FLAG ,
1566 REQUEST_ID ,
1567 CREATED_BY ,
1568 CREATION_DATE ,
1569 LAST_UPDATE_LOGIN ,
1570 LAST_UPDATE_DATE ,
1571 LAST_UPDATED_BY
1572 )
1573 VALUES
1574 (
1575 HZ_CUSTOMER_MERGE_LOG_s.nextval ,
1576 'CSI_SYSTEMS_B' ,
1577 MERGE_HEADER_ID_LIST(I) ,
1578 PRIMARY_KEY_ID_LIST(I) ,
1579 NUM_COL1_ORIG_LIST(I) ,
1580 NUM_COL1_NEW_LIST(I) ,
1581 NUM_COL2_ORIG_LIST(I ),
1582 NUM_COL2_NEW_LIST(I) ,
1583 NUM_COL3_ORIG_LIST(I) ,
1584 NUM_COL3_NEW_LIST(I) ,
1585 'U' ,
1586 req_id ,
1587 hz_utility_pub.CREATED_BY ,
1588 hz_utility_pub.CREATION_DATE ,
1589 hz_utility_pub.LAST_UPDATE_LOGIN ,
1590 hz_utility_pub.LAST_UPDATE_DATE ,
1591 hz_utility_pub.LAST_UPDATED_BY
1592 );
1593
1594 END IF; -- } profile value if
1595
1596 write_to_cr_log( 'CSI : Before Update Loop check ');
1597
1598 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP -- { I Loop
1599 UPDATE CSI_SYSTEMS_B yt
1600 SET CUSTOMER_ID = NUM_COL1_NEW_LIST(I) ,
1601 BILL_TO_SITE_USE_ID = NUM_COL2_NEW_LIST(I) ,
1602 SHIP_TO_SITE_USE_ID = NUM_COL3_NEW_LIST(I) ,
1603 LAST_UPDATE_DATE = SYSDATE ,
1604 last_updated_by = arp_standard.profile.user_id ,
1605 last_update_login = arp_standard.profile.last_update_login ,
1606 REQUEST_ID = req_id ,
1607 PROGRAM_APPLICATION_ID = arp_standard.profile.program_application_id ,
1608 PROGRAM_ID = arp_standard.profile.program_id ,
1609 PROGRAM_UPDATE_DATE = SYSDATE
1610 WHERE SYSTEM_ID = PRIMARY_KEY_ID_LIST(I);
1611
1612 /*------
1613 Check for a transaction record and if not found create one
1614
1615 If transaction_inserted_flag = 'N' then
1616 1.1 if a transaction does not exist
1617 create a transaction
1618 1.2 set the transaction_inserted_flag = 'Y'
1619 ------*/
1620 SELECT count(*)
1621 INTO v_transaction_cnt
1622 FROM csi_transactions
1623 WHERE source_line_ref_id = req_id
1624 AND source_line_ref = set_num;
1628 || to_char(set_num) || '.'
1625
1626 write_to_cr_log( 'CSI : Transaction record count for '
1627 || to_char(req_id) || '.'
1629 || to_char(v_transaction_cnt) );
1630
1631 IF v_transaction_exists = 'N' Then -- { transaction exists is no
1632 BEGIN -- { select csi_transaction
1633 error_str := '005';
1634 SELECT transaction_id
1635 INTO v_transaction_id
1636 FROM csi_transactions
1637 WHERE source_line_ref_id = req_id
1638 AND source_line_ref = set_num;
1639
1640 v_transaction_exists := 'Y';
1641
1642 write_to_cr_log( 'CSI : Transaction record found.');
1643
1644 EXCEPTION
1645 WHEN no_data_found THEN
1646 BEGIN -- { insert csi_transaction
1647 error_str := '006';
1648
1649 write_to_cr_log( 'CSI : Transaction record NOT found.');
1650
1651 SELECT count(*)
1652 INTO v_transaction_cnt
1653 FROM csi_txn_types
1654 WHERE source_transaction_type = v_source_transaction_type;
1655
1656 write_to_cr_log( 'CSI : Transaction type record count '|| to_char(v_transaction_cnt) );
1657
1658 SELECT transaction_type_id
1659 INTO v_transaction_type_id
1660 FROM csi_txn_types
1661 WHERE source_transaction_type = v_source_transaction_type;
1662
1663 write_to_cr_log( 'CSI : Transaction type record id ' || to_char(v_transaction_type_id) );
1664
1665 error_str := '007';
1666 SELECT csi_transactions_s.nextval
1667 INTO v_transaction_id
1668 FROM dual;
1669
1670 write_to_cr_log( 'CSI : Transaction id from sequence ' || to_char(v_transaction_id) );
1671
1672 error_str := '008';
1673 INSERT INTO csi_transactions
1674 (
1675 transaction_id ,
1676 transaction_date ,
1677 source_transaction_date ,
1678 transaction_type_id ,
1679 source_line_ref_id ,
1680 source_line_ref ,
1681 created_by ,
1682 creation_date ,
1683 last_updated_by ,
1684 last_update_date ,
1685 last_update_login ,
1686 object_version_number
1687 )
1688 VALUES
1689 (
1690 v_transaction_id ,
1691 sysdate ,
1692 sysdate ,
1693 v_transaction_type_id ,
1694 req_id ,
1695 set_num ,
1696 arp_standard.profile.user_id ,
1697 sysdate ,
1698 arp_standard.profile.user_id ,
1699 sysdate ,
1700 arp_standard.profile.last_update_login ,
1701 1
1702 );
1703
1704 v_transaction_exists := 'Y';
1705
1706 write_to_cr_log( 'CSI : Transaction record created ' );
1707
1708 END; -- } end insert csi_transaction
1709 END; -- } end select csi_transaction
1710 END IF; -- } transaction exists is no
1711
1712 BEGIN -- { Update systems history
1713
1714 SELECT count(*)
1715 INTO v_transaction_cnt
1716 FROM csi_systems_h
1717 WHERE transaction_id = v_transaction_id
1718 AND system_id = PRIMARY_KEY_ID_LIST(I);
1719
1720 write_to_cr_log( 'CSI : Transaction history record count '|| to_char(v_transaction_cnt) );
1721
1722 error_str := '009';
1723 SELECT system_history_id
1724 INTO v_system_history_id
1725 FROM csi_systems_h
1726 WHERE transaction_id = v_transaction_id
1727 AND system_id = PRIMARY_KEY_ID_LIST(I);
1728
1729 write_to_cr_log( 'CSI : Transaction history record id '|| to_char(v_system_history_id) );
1730
1731 error_str := '010';
1732 UPDATE csi_systems_h
1733 SET old_customer_id = NUM_COL1_ORIG_LIST(I) ,
1734 new_customer_id = NUM_COL1_NEW_LIST(I) ,
1735 old_bill_to_site_use_id = NUM_COL2_ORIG_LIST(I) ,
1736 new_bill_to_site_use_id = NUM_COL2_NEW_LIST(I) ,
1737 old_ship_to_site_use_id = NUM_COL3_ORIG_LIST(I) ,
1738 new_ship_to_site_use_id = NUM_COL3_NEW_LIST(I) ,
1739 object_version_number = object_version_number + 1
1740 WHERE system_history_id = v_system_history_id ;
1741
1742 write_to_cr_log( 'CSI : updated system history record ' );
1743
1747
1744 EXCEPTION
1745 WHEN no_data_found THEN
1746 BEGIN --{ Insert systems history
1748 write_to_cr_log( 'CSI : No system history record found ' );
1749
1750 error_str := '011';
1751 INSERT INTO csi_systems_h
1752 (
1753 system_history_id ,
1754 system_id ,
1755 transaction_id ,
1756 old_customer_id ,
1757 new_customer_id ,
1758 old_bill_to_site_use_id ,
1759 new_bill_to_site_use_id ,
1760 old_ship_to_site_use_id ,
1761 new_ship_to_site_use_id ,
1762 full_dump_flag ,
1763 created_by ,
1764 creation_date ,
1765 last_updated_by ,
1766 last_update_date ,
1767 last_update_login ,
1768 object_version_number
1769 )
1770 VALUES
1771 (
1772 csi_systems_h_s.nextval ,
1773 PRIMARY_KEY_ID_LIST(I) ,
1774 v_transaction_id ,
1775 NUM_COL1_ORIG_LIST(I) ,
1776 NUM_COL1_NEW_LIST(I) ,
1777 NUM_COL2_ORIG_LIST(I) ,
1778 NUM_COL2_NEW_LIST(I) ,
1779 NUM_COL3_ORIG_LIST(I) ,
1780 NUM_COL3_NEW_LIST(I) ,
1781 'N',
1782 arp_standard.profile.user_id ,
1783 sysdate ,
1784 arp_standard.profile.user_id ,
1785 sysdate ,
1786 arp_standard.profile.last_update_login ,
1787 1
1788 );
1789 write_to_cr_log( 'CSI : inserted system history record ' );
1790 END; -- } Insert systems history
1791 END; -- } Update Systems history
1792 END LOOP; -- } I Loop
1793
1794 l_count := l_count + SQL%ROWCOUNT;
1795
1796 IF l_last_fetch THEN
1797 EXIT;
1798 END IF;
1799
1800 no_of_rows := sql%rowcount;
1801 arp_message.set_token('NUM_ROWS',to_char(no_of_rows));
1802 arp_message.set_line( 'Done with the insert of systems history' );
1803
1804 END LOOP; -- } End merged records loop
1805
1806 CLOSE merged_records;
1807
1808 END IF;
1809
1810 arp_message.set_line('CSI_ACCT_MERGE_PKG.CSI_SYSTEMS_B_MERGE()-');
1811
1812 EXCEPTION
1813 WHEN OTHERS THEN
1814 arp_message.set_line('CSI_ACCT_MERGE_PKG.CSI_SYSTEMS_B_MERGE()-');
1815 CLOSE merged_records;
1816 raise;
1817 END csi_systems_b_merge;
1818
1819 PROCEDURE csi_t_party_accounts_merge( req_id IN NUMBER,
1820 set_num IN NUMBER,
1821 process_mode IN VARCHAR2 ) IS
1822
1823 error_str VARCHAR2(3);
1824 no_of_rows NUMBER;
1825 l_profile_val VARCHAR2(30);
1826 l_last_fetch BOOLEAN := FALSE;
1827 l_count NUMBER;
1828
1829 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
1830 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
1831 INDEX BY BINARY_INTEGER;
1832
1833 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
1834
1835 TYPE TXN_ACCT_DETAIL_ID_LIST_TYPE IS TABLE OF
1836 CSI_T_PARTY_ACCOUNTS.TXN_ACCOUNT_DETAIL_ID%TYPE
1837 INDEX BY BINARY_INTEGER;
1838
1839 PRIMARY_KEY_ID_LIST TXN_ACCT_DETAIL_ID_LIST_TYPE;
1840
1841 TYPE ACCOUNT_ID_LIST_TYPE IS TABLE OF
1842 CSI_T_PARTY_ACCOUNTS.ACCOUNT_ID%TYPE
1843 INDEX BY BINARY_INTEGER;
1844
1845 NUM_COL1_ORIG_LIST ACCOUNT_ID_LIST_TYPE;
1846 NUM_COL1_NEW_LIST ACCOUNT_ID_LIST_TYPE;
1847
1848 TYPE BILL_TO_ADDRESS_ID_LIST_TYPE IS TABLE OF
1849 CSI_T_PARTY_ACCOUNTS.BILL_TO_ADDRESS_ID%TYPE
1850 INDEX BY BINARY_INTEGER;
1851
1852 NUM_COL2_ORIG_LIST BILL_TO_ADDRESS_ID_LIST_TYPE;
1853 NUM_COL2_NEW_LIST BILL_TO_ADDRESS_ID_LIST_TYPE;
1854
1855 TYPE SHIP_TO_ADDRESS_ID_LIST_TYPE IS TABLE OF
1856 CSI_T_PARTY_ACCOUNTS.SHIP_TO_ADDRESS_ID%TYPE
1857 INDEX BY BINARY_INTEGER;
1858
1859 NUM_COL3_ORIG_LIST SHIP_TO_ADDRESS_ID_LIST_TYPE;
1860 NUM_COL3_NEW_LIST SHIP_TO_ADDRESS_ID_LIST_TYPE;
1861
1862 CURSOR merged_records IS
1863 SELECT distinct CUSTOMER_MERGE_HEADER_ID ,
1864 TXN_ACCOUNT_DETAIL_ID ,
1865 ACCOUNT_ID ,
1866 BILL_TO_ADDRESS_ID ,
1867 SHIP_TO_ADDRESS_ID
1868 FROM CSI_T_PARTY_ACCOUNTS yt,
1869 RA_CUSTOMER_MERGES m
1870 WHERE ( yt.ACCOUNT_ID = m.DUPLICATE_ID OR
1871 yt.BILL_TO_ADDRESS_ID = m.DUPLICATE_SITE_ID OR
1872 yt.SHIP_TO_ADDRESS_ID = m.DUPLICATE_SITE_ID )
1873 AND m.process_flag = 'N'
1874 AND m.request_id = req_id
1875 AND m.set_number = set_num;
1876
1877 BEGIN
1878
1879 arp_message.set_line('CSI_ACCT_MERGE_PKG.CSI_T_PARTY_ACCOUNTS_MERGE()+');
1880
1881 IF (process_mode = 'LOCK') THEN
1882
1883 write_to_cr_log( 'Locking the csi_t_party_accounts' );
1884
1888 OPEN merged_records;
1885 arp_message.set_name('AR','AR_LOCKING_TABLE');
1886 arp_message.set_token('TABLE_NAME','CSI_T_PARTY_ACCOUNTS',FALSE);
1887
1889 CLOSE merged_records;
1890
1891 write_to_cr_log( 'Done Locking the csi_t_party_accounts' );
1892 ELSE
1893 write_to_cr_log( 'Starting to update the csi_t_party_accounts' );
1894
1895 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
1896 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','CSI_T_PARTY_ACCOUNTS',FALSE);
1897 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
1898 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
1899
1900 OPEN merged_records;
1901
1902 LOOP
1903 FETCH merged_records BULK COLLECT INTO
1904 MERGE_HEADER_ID_LIST ,
1905 PRIMARY_KEY_ID_LIST ,
1906 NUM_COL1_ORIG_LIST ,
1907 NUM_COL2_ORIG_LIST ,
1908 NUM_COL3_ORIG_LIST
1909 LIMIT 1000;
1910
1911 IF merged_records%NOTFOUND THEN
1912 l_last_fetch := TRUE;
1913 END IF;
1914
1915 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
1916 EXIT;
1917 END IF;
1918
1919 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
1920 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
1921 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
1922 NUM_COL3_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL3_ORIG_LIST(I));
1923 END LOOP;
1924
1925 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1926 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1927 INSERT INTO HZ_CUSTOMER_MERGE_LOG
1928 (
1929 MERGE_LOG_ID ,
1930 TABLE_NAME ,
1931 MERGE_HEADER_ID ,
1932 PRIMARY_KEY_ID ,
1933 NUM_COL1_ORIG ,
1934 NUM_COL1_NEW ,
1935 NUM_COL2_ORIG ,
1936 NUM_COL2_NEW ,
1937 NUM_COL3_ORIG ,
1938 NUM_COL3_NEW ,
1939 ACTION_FLAG ,
1940 REQUEST_ID ,
1941 CREATED_BY ,
1942 CREATION_DATE ,
1943 LAST_UPDATE_LOGIN ,
1944 LAST_UPDATE_DATE ,
1945 LAST_UPDATED_BY
1946 )
1947 VALUES
1948 (
1949 HZ_CUSTOMER_MERGE_LOG_s.nextval ,
1950 'CSI_T_PARTY_ACCOUNTS' ,
1951 MERGE_HEADER_ID_LIST(I) ,
1952 PRIMARY_KEY_ID_LIST(I) ,
1953 NUM_COL1_ORIG_LIST(I) ,
1954 NUM_COL1_NEW_LIST(I) ,
1955 NUM_COL2_ORIG_LIST(I) ,
1956 NUM_COL2_NEW_LIST(I) ,
1957 NUM_COL3_ORIG_LIST(I) ,
1958 NUM_COL3_NEW_LIST(I) ,
1959 'U' ,
1960 req_id ,
1961 hz_utility_pub.CREATED_BY ,
1962 hz_utility_pub.CREATION_DATE ,
1963 hz_utility_pub.LAST_UPDATE_LOGIN ,
1964 hz_utility_pub.LAST_UPDATE_DATE ,
1965 hz_utility_pub.LAST_UPDATED_BY
1966 );
1967 END IF;
1968
1969 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1970 UPDATE CSI_T_PARTY_ACCOUNTS yt
1971 SET ACCOUNT_ID = NUM_COL1_NEW_LIST(I) ,
1972 BILL_TO_ADDRESS_ID = NUM_COL2_NEW_LIST(I) ,
1973 SHIP_TO_ADDRESS_ID = NUM_COL3_NEW_LIST(I) ,
1974 LAST_UPDATE_DATE = SYSDATE ,
1975 last_updated_by = arp_standard.profile.user_id ,
1976 last_update_login = arp_standard.profile.last_update_login ,
1977 REQUEST_ID = req_id ,
1978 PROGRAM_APPLICATION_ID = arp_standard.profile.program_application_id ,
1979 PROGRAM_ID = arp_standard.profile.program_id ,
1980 PROGRAM_UPDATE_DATE = SYSDATE
1981 WHERE TXN_ACCOUNT_DETAIL_ID = PRIMARY_KEY_ID_LIST(I);
1982
1983 l_count := l_count + SQL%ROWCOUNT;
1984
1985 IF l_last_fetch THEN
1986 EXIT;
1987 END IF;
1988
1989 END LOOP;
1990
1991 CLOSE merged_records;
1992
1993 arp_message.set_name('AR','AR_ROWS_UPDATED');
1994 arp_message.set_token('NUM_ROWS',to_char(l_count));
1995 arp_message.set_line( 'Done with the update of csi_t_party_accounts' );
1996
1997 END IF;
1998
1999 arp_message.set_line('CSI_ACCT_MERGE_PKG.CSI_T_PARTY_ACCOUNTS_MERGE()-');
2000
2001 EXCEPTION
2002 WHEN OTHERS THEN
2003 arp_message.set_line('CSI_ACCT_MERGE_PKG.CSI_T_PARTY_ACCOUNTS_MERGE()-');
2004 CLOSE merged_records;
2005 raise;
2006 END csi_t_party_accounts_merge;
2007
2008 PROCEDURE csi_t_txn_systems_merge( req_id IN NUMBER,
2009 set_num IN NUMBER,
2010 process_mode IN VARCHAR2) IS
2011
2012 error_str VARCHAR2(3);
2013 no_of_rows NUMBER;
2014 l_profile_val VARCHAR2(30);
2015 l_last_fetch BOOLEAN := FALSE;
2016 l_count NUMBER;
2017
2018 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
2019 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
2020 INDEX BY BINARY_INTEGER;
2021
2022 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
2023
2024 TYPE TRXN_SYSTEM_ID_LIST_TYPE IS TABLE OF
2028 PRIMARY_KEY_ID_LIST TRXN_SYSTEM_ID_LIST_TYPE;
2025 CSI_T_TXN_SYSTEMS.TRANSACTION_SYSTEM_ID%TYPE
2026 INDEX BY BINARY_INTEGER;
2027
2029
2030 TYPE CUSTOMER_ID_LIST_TYPE IS TABLE OF
2031 CSI_T_TXN_SYSTEMS.CUSTOMER_ID%TYPE
2032 INDEX BY BINARY_INTEGER;
2033
2034 NUM_COL1_ORIG_LIST CUSTOMER_ID_LIST_TYPE;
2035 NUM_COL1_NEW_LIST CUSTOMER_ID_LIST_TYPE;
2036
2037 TYPE BILL_TO_SITE_USE_ID_LIST_TYPE IS TABLE OF
2038 CSI_T_TXN_SYSTEMS.BILL_TO_SITE_USE_ID%TYPE
2039 INDEX BY BINARY_INTEGER;
2040
2041 NUM_COL2_ORIG_LIST BILL_TO_SITE_USE_ID_LIST_TYPE;
2042 NUM_COL2_NEW_LIST BILL_TO_SITE_USE_ID_LIST_TYPE;
2043
2044 TYPE SHIP_TO_SITE_USE_ID_LIST_TYPE IS TABLE OF
2045 CSI_T_TXN_SYSTEMS.SHIP_TO_SITE_USE_ID%TYPE
2046 INDEX BY BINARY_INTEGER;
2047
2051 TYPE INSTALL_SITE_USE_ID_LIST_TYPE IS TABLE OF
2048 NUM_COL3_ORIG_LIST SHIP_TO_SITE_USE_ID_LIST_TYPE;
2049 NUM_COL3_NEW_LIST SHIP_TO_SITE_USE_ID_LIST_TYPE;
2050
2052 CSI_T_TXN_SYSTEMS.INSTALL_SITE_USE_ID%TYPE
2053 INDEX BY BINARY_INTEGER;
2054
2055 NUM_COL4_ORIG_LIST INSTALL_SITE_USE_ID_LIST_TYPE;
2056 NUM_COL4_NEW_LIST INSTALL_SITE_USE_ID_LIST_TYPE;
2057
2058 CURSOR merged_records IS
2059 SELECT distinct CUSTOMER_MERGE_HEADER_ID ,
2060 yt.TRANSACTION_SYSTEM_ID ,
2061 yt.CUSTOMER_ID ,
2062 yt.BILL_TO_SITE_USE_ID ,
2063 yt.SHIP_TO_SITE_USE_ID ,
2064 yt.INSTALL_SITE_USE_ID
2065 FROM CSI_T_TXN_SYSTEMS yt,
2066 RA_CUSTOMER_MERGES m
2067 WHERE ( yt.CUSTOMER_ID = m.DUPLICATE_ID OR
2068 yt.BILL_TO_SITE_USE_ID = m.DUPLICATE_SITE_ID OR
2069 yt.SHIP_TO_SITE_USE_ID = m.DUPLICATE_SITE_ID OR
2070 yt.INSTALL_SITE_USE_ID = m.DUPLICATE_SITE_ID )
2071 AND m.process_flag = 'N'
2072 AND m.request_id = req_id
2073 AND m.set_number = set_num;
2074
2075 BEGIN
2076
2077 arp_message.set_line('CSI_ACCT_MERGE_PKG.CSI_T_TXN_SYSTEMS_MERGE()+');
2078
2079 IF (process_mode = 'LOCK') THEN
2080 write_to_cr_log( 'Locking the CSI_T_TXN_SYSTEMS table' );
2081
2082 arp_message.set_name('AR','AR_LOCKING_TABLE');
2083 arp_message.set_token('TABLE_NAME','CSI_T_TXN_SYSTEMS',FALSE);
2084
2085 OPEN merged_records;
2086 CLOSE merged_records;
2087
2088 write_to_cr_log( 'Done Locking the CSI_T_TXN_SYSTEMS table' );
2089 ELSE
2090 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
2091 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','CSI_T_TXN_SYSTEMS',FALSE);
2092 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
2093 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
2094
2095 OPEN merged_records;
2096
2097 LOOP
2098 FETCH merged_records BULK COLLECT INTO
2099 MERGE_HEADER_ID_LIST ,
2100 PRIMARY_KEY_ID_LIST ,
2101 NUM_COL1_ORIG_LIST ,
2102 NUM_COL2_ORIG_LIST ,
2103 NUM_COL3_ORIG_LIST ,
2104 NUM_COL4_ORIG_LIST
2105 LIMIT 1000;
2106
2107 IF merged_records%NOTFOUND THEN
2108 l_last_fetch := TRUE;
2109 END IF;
2110
2111 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
2112 EXIT;
2113 END IF;
2114
2115 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
2116 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
2117 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
2118 NUM_COL3_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL3_ORIG_LIST(I));
2119 NUM_COL4_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL4_ORIG_LIST(I));
2120 END LOOP;
2121
2122 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
2123 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
2124 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
2125 MERGE_LOG_ID,
2126 TABLE_NAME,
2127 MERGE_HEADER_ID,
2128 PRIMARY_KEY_ID,
2129 NUM_COL1_ORIG,
2130 NUM_COL1_NEW,
2131 NUM_COL2_ORIG,
2132 NUM_COL2_NEW,
2133 NUM_COL3_ORIG,
2134 NUM_COL3_NEW,
2135 NUM_COL4_ORIG,
2136 NUM_COL4_NEW,
2137 ACTION_FLAG,
2138 REQUEST_ID,
2139 CREATED_BY,
2140 CREATION_DATE,
2141 LAST_UPDATE_LOGIN,
2142 LAST_UPDATE_DATE,
2143 LAST_UPDATED_BY)
2144 VALUES (
2145 HZ_CUSTOMER_MERGE_LOG_s.nextval,
2146 'CSI_T_TXN_SYSTEMS',
2147 MERGE_HEADER_ID_LIST(I),
2148 PRIMARY_KEY_ID_LIST(I),
2149 NUM_COL1_ORIG_LIST(I),
2150 NUM_COL1_NEW_LIST(I),
2151 NUM_COL2_ORIG_LIST(I),
2152 NUM_COL2_NEW_LIST(I),
2153 NUM_COL3_ORIG_LIST(I),
2154 NUM_COL3_NEW_LIST(I),
2155 NUM_COL4_ORIG_LIST(I),
2156 NUM_COL4_NEW_LIST(I),
2157 'U',
2158 req_id,
2159 hz_utility_pub.CREATED_BY,
2160 hz_utility_pub.CREATION_DATE,
2161 hz_utility_pub.LAST_UPDATE_LOGIN,
2162 hz_utility_pub.LAST_UPDATE_DATE,
2163 hz_utility_pub.LAST_UPDATED_BY );
2164 END IF;
2165
2166 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
2167 UPDATE CSI_T_TXN_SYSTEMS yt
2168 SET CUSTOMER_ID = NUM_COL1_NEW_LIST(I) ,
2169 BILL_TO_SITE_USE_ID = NUM_COL2_NEW_LIST(I) ,
2170 SHIP_TO_SITE_USE_ID = NUM_COL3_NEW_LIST(I) ,
2171 INSTALL_SITE_USE_ID = NUM_COL4_NEW_LIST(I) ,
2172 LAST_UPDATE_DATE = SYSDATE ,
2173 last_updated_by = arp_standard.profile.user_id ,
2174 last_update_login = arp_standard.profile.last_update_login ,
2175 REQUEST_ID = req_id ,
2176 PROGRAM_APPLICATION_ID = arp_standard.profile.program_application_id ,
2177 PROGRAM_ID = arp_standard.profile.program_id ,
2178 PROGRAM_UPDATE_DATE = SYSDATE
2179 WHERE TRANSACTION_SYSTEM_ID = PRIMARY_KEY_ID_LIST(I);
2180
2181 l_count := l_count + SQL%ROWCOUNT;
2182 IF l_last_fetch THEN
2183 EXIT;
2184 END IF;
2185
2186 END LOOP;
2187
2188 CLOSE merged_records;
2189
2190 arp_message.set_name('AR','AR_ROWS_UPDATED');
2191 arp_message.set_token('NUM_ROWS',to_char(l_count));
2195
2192 arp_message.set_line( 'Done with the update of CSI_T_TXN_SYSTEMS' );
2193
2194 END IF;
2196 arp_message.set_line('CSI_ACCT_MERGE_PKG.CSI_T_TXN_SYSTEMS_MERGE()-');
2197
2198 EXCEPTION
2199 WHEN OTHERS THEN
2200 arp_message.set_line('CSI_ACCT_MERGE_PKG.CSI_T_TXN_SYSTEMS_MERGE()-');
2201 CLOSE merged_records;
2202 raise;
2203 END csi_t_txn_systems_merge;
2204
2205 PROCEDURE write_to_cr_log ( p_message IN VARCHAR2) IS
2206 BEGIN
2207 IF csi_acct_merge_pkg.g_debug_on > 0 THEN
2208 arp_message.set_line( p_message );
2209 END IF;
2210 END write_to_cr_log;
2211
2212 END CSI_ACCT_MERGE_PKG;