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