56: BEGIN
57: IF process_mode='LOCK' THEN
58: NULL;
59: ELSE
60: ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
61: ARP_MESSAGE.SET_TOKEN('TABLE_NAME','AR_CMGT_CASE_FOLDERS',FALSE);
62: HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
63: l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
64:
57: IF process_mode='LOCK' THEN
58: NULL;
59: ELSE
60: ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
61: ARP_MESSAGE.SET_TOKEN('TABLE_NAME','AR_CMGT_CASE_FOLDERS',FALSE);
62: HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
63: l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
64:
65: open merged_records;
143: EXIT;
144: END IF;
145: END LOOP;
146:
147: arp_message.set_name('AR','AR_ROWS_UPDATED');
148: arp_message.set_token('NUM_ROWS',to_char(l_count));
149: END IF;
150: EXCEPTION
151: WHEN OTHERS THEN
144: END IF;
145: END LOOP;
146:
147: arp_message.set_name('AR','AR_ROWS_UPDATED');
148: arp_message.set_token('NUM_ROWS',to_char(l_count));
149: END IF;
150: EXCEPTION
151: WHEN OTHERS THEN
152: arp_message.set_line( 'CASE_FOLDER_ACCOUNT_MERGE');
148: arp_message.set_token('NUM_ROWS',to_char(l_count));
149: END IF;
150: EXCEPTION
151: WHEN OTHERS THEN
152: arp_message.set_line( 'CASE_FOLDER_ACCOUNT_MERGE');
153: RAISE;
154: END CASE_FOLDER_ACCOUNT_MERGE;
155:
156: /*-------------------------------------------------------------
208: BEGIN
209: IF process_mode='LOCK' THEN
210: NULL;
211: ELSE
212: ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
213: ARP_MESSAGE.SET_TOKEN('TABLE_NAME','AR_CMGT_CREDIT_REQUESTS',FALSE);
214: HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
215: l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
216:
209: IF process_mode='LOCK' THEN
210: NULL;
211: ELSE
212: ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
213: ARP_MESSAGE.SET_TOKEN('TABLE_NAME','AR_CMGT_CREDIT_REQUESTS',FALSE);
214: HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
215: l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
216:
217: open merged_records;
281: EXIT;
282: END IF;
283: END LOOP;
284:
285: arp_message.set_name('AR','AR_ROWS_UPDATED');
286: arp_message.set_token('NUM_ROWS',to_char(l_count));
287: END IF;
288: EXCEPTION
289: WHEN OTHERS THEN
282: END IF;
283: END LOOP;
284:
285: arp_message.set_name('AR','AR_ROWS_UPDATED');
286: arp_message.set_token('NUM_ROWS',to_char(l_count));
287: END IF;
288: EXCEPTION
289: WHEN OTHERS THEN
290: arp_message.set_line( 'CREDIT_REQUEST_ACCOUNT_MERGE');
286: arp_message.set_token('NUM_ROWS',to_char(l_count));
287: END IF;
288: EXCEPTION
289: WHEN OTHERS THEN
290: arp_message.set_line( 'CREDIT_REQUEST_ACCOUNT_MERGE');
291: RAISE;
292: END CREDIT_REQUEST_ACCOUNT_MERGE;
293:
294: /*-------------------------------------------------------------
469: BEGIN
470: IF process_mode='LOCK' THEN
471: NULL;
472: ELSE
473: ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
474: ARP_MESSAGE.SET_TOKEN('TABLE_NAME','AR_TRX_BAL_SUMMARY',FALSE);
475: HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
476: l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
477:
470: IF process_mode='LOCK' THEN
471: NULL;
472: ELSE
473: ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
474: ARP_MESSAGE.SET_TOKEN('TABLE_NAME','AR_TRX_BAL_SUMMARY',FALSE);
475: HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
476: l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
477:
478: open merged_records;
521: END IF;
522: FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
523: NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
524: NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
525: arp_message.set_line( 'Request Id ='||req_id);
526: arp_message.set_line( 'NUM_COL1_NEW_LIST(I)='||NUM_COL1_NEW_LIST(I));
527: arp_message.set_line( 'NUM_COL2_NEW_LIST(I)='||NUM_COL2_NEW_LIST(I));
528: END LOOP;
529: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
522: FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
523: NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
524: NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
525: arp_message.set_line( 'Request Id ='||req_id);
526: arp_message.set_line( 'NUM_COL1_NEW_LIST(I)='||NUM_COL1_NEW_LIST(I));
527: arp_message.set_line( 'NUM_COL2_NEW_LIST(I)='||NUM_COL2_NEW_LIST(I));
528: END LOOP;
529: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
530: arp_message.set_line( 'Inserting into HZ_CUSTOMER_MERGE_LOG');
523: NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
524: NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
525: arp_message.set_line( 'Request Id ='||req_id);
526: arp_message.set_line( 'NUM_COL1_NEW_LIST(I)='||NUM_COL1_NEW_LIST(I));
527: arp_message.set_line( 'NUM_COL2_NEW_LIST(I)='||NUM_COL2_NEW_LIST(I));
528: END LOOP;
529: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
530: arp_message.set_line( 'Inserting into HZ_CUSTOMER_MERGE_LOG');
531: FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
526: arp_message.set_line( 'NUM_COL1_NEW_LIST(I)='||NUM_COL1_NEW_LIST(I));
527: arp_message.set_line( 'NUM_COL2_NEW_LIST(I)='||NUM_COL2_NEW_LIST(I));
528: END LOOP;
529: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
530: arp_message.set_line( 'Inserting into HZ_CUSTOMER_MERGE_LOG');
531: FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
532: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
533: MERGE_LOG_ID,
534: TABLE_NAME,
621: hz_utility_pub.LAST_UPDATE_DATE,
622: hz_utility_pub.LAST_UPDATED_BY
623: );
624:
625: arp_message.set_line( 'after Insert into HZ_CUSTOMER_MERGE_LOG');
626: END IF;
627: arp_message.set_line( 'before UPDATE AR_TRX_BAL_SUMMARY ');
628: FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
629: UPDATE AR_TRX_BAL_SUMMARY yt SET
623: );
624:
625: arp_message.set_line( 'after Insert into HZ_CUSTOMER_MERGE_LOG');
626: END IF;
627: arp_message.set_line( 'before UPDATE AR_TRX_BAL_SUMMARY ');
628: FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
629: UPDATE AR_TRX_BAL_SUMMARY yt SET
630: (BEST_CURRENT_RECEIVABLES
631: ,TOTAL_DSO_DAYS_CREDIT
816: AND yt.SITE_USE_ID=NUM_COL2_NEW_LIST(I)
817: AND yt.CURRENCY=PRIMARY_KEY3_LIST(I)
818: AND yt.ORG_ID=PRIMARY_KEY4_LIST(I) ;
819:
820: arp_message.set_line( 'after UPDATE AR_TRX_BAL_SUMMARY ');
821: arp_message.set_line( 'before DELETE AR_TRX_BAL_SUMMARY');
822: FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
823: DELETE AR_TRX_BAL_SUMMARY yt
824: WHERE yt.cust_account_id = PRIMARY_KEY1_LIST(I)
817: AND yt.CURRENCY=PRIMARY_KEY3_LIST(I)
818: AND yt.ORG_ID=PRIMARY_KEY4_LIST(I) ;
819:
820: arp_message.set_line( 'after UPDATE AR_TRX_BAL_SUMMARY ');
821: arp_message.set_line( 'before DELETE AR_TRX_BAL_SUMMARY');
822: FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
823: DELETE AR_TRX_BAL_SUMMARY yt
824: WHERE yt.cust_account_id = PRIMARY_KEY1_LIST(I)
825: AND yt.SITE_USE_ID=PRIMARY_KEY2_LIST(I)
831: AND yt2.SITE_USE_ID=NUM_COL2_NEW_LIST(I)
832: AND yt2.CURRENCY=PRIMARY_KEY3_LIST(I)
833: AND yt2.ORG_ID=PRIMARY_KEY4_LIST(I) );
834:
835: arp_message.set_line( 'after DELETE AR_TRX_BAL_SUMMARY');
836:
837: arp_message.set_line( 'before UPDATE AR_TRX_BAL_SUMMARY again');
838: /* bug4727614: Added not exists clause to prevent unique index error */
839: FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
833: AND yt2.ORG_ID=PRIMARY_KEY4_LIST(I) );
834:
835: arp_message.set_line( 'after DELETE AR_TRX_BAL_SUMMARY');
836:
837: arp_message.set_line( 'before UPDATE AR_TRX_BAL_SUMMARY again');
838: /* bug4727614: Added not exists clause to prevent unique index error */
839: FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
840: UPDATE AR_TRX_BAL_SUMMARY yt SET
841: CUST_ACCOUNT_ID=NUM_COL1_NEW_LIST(I)
853: AND yt2.SITE_USE_ID=NUM_COL2_NEW_LIST(I)
854: AND yt2.CURRENCY=PRIMARY_KEY3_LIST(I)
855: AND yt2.ORG_ID=PRIMARY_KEY4_LIST(I) );
856:
857: arp_message.set_line( 'after UPDATE AR_TRX_BAL_SUMMARY again');
858: l_count := l_count + SQL%ROWCOUNT;
859: IF l_last_fetch THEN
860: EXIT;
861: END IF;
860: EXIT;
861: END IF;
862: END LOOP;
863:
864: arp_message.set_name('AR','AR_ROWS_UPDATED');
865: arp_message.set_token('NUM_ROWS',to_char(l_count));
866: END IF;
867: EXCEPTION
868: WHEN OTHERS THEN
861: END IF;
862: END LOOP;
863:
864: arp_message.set_name('AR','AR_ROWS_UPDATED');
865: arp_message.set_token('NUM_ROWS',to_char(l_count));
866: END IF;
867: EXCEPTION
868: WHEN OTHERS THEN
869: arp_message.set_line( 'TRX_BAL_SUMMARY_ACCOUNT_MERGE');
865: arp_message.set_token('NUM_ROWS',to_char(l_count));
866: END IF;
867: EXCEPTION
868: WHEN OTHERS THEN
869: arp_message.set_line( 'TRX_BAL_SUMMARY_ACCOUNT_MERGE');
870: arp_message.set_line( 'TRX_BAL_SUMMARY_ACCOUNT_MERGE: SQLERRM : ' || SQLERRM);
871: RAISE;
872: END TRX_BAL_SUMMARY_ACCOUNT_MERGE;
873:
866: END IF;
867: EXCEPTION
868: WHEN OTHERS THEN
869: arp_message.set_line( 'TRX_BAL_SUMMARY_ACCOUNT_MERGE');
870: arp_message.set_line( 'TRX_BAL_SUMMARY_ACCOUNT_MERGE: SQLERRM : ' || SQLERRM);
871: RAISE;
872: END TRX_BAL_SUMMARY_ACCOUNT_MERGE;
873:
874: /*-------------------------------------------------------------
1021: BEGIN
1022: IF process_mode='LOCK' THEN
1023: NULL;
1024: ELSE
1025: ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
1026: ARP_MESSAGE.SET_TOKEN('TABLE_NAME','AR_TRX_SUMMARY',FALSE);
1027: HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
1028: l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
1029:
1022: IF process_mode='LOCK' THEN
1023: NULL;
1024: ELSE
1025: ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
1026: ARP_MESSAGE.SET_TOKEN('TABLE_NAME','AR_TRX_SUMMARY',FALSE);
1027: HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
1028: l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
1029:
1030: open merged_records;
1498: EXIT;
1499: END IF;
1500: END LOOP;
1501:
1502: arp_message.set_name('AR','AR_ROWS_UPDATED');
1503: arp_message.set_token('NUM_ROWS',to_char(l_count));
1504: END IF;
1505: EXCEPTION
1506: WHEN OTHERS THEN
1499: END IF;
1500: END LOOP;
1501:
1502: arp_message.set_name('AR','AR_ROWS_UPDATED');
1503: arp_message.set_token('NUM_ROWS',to_char(l_count));
1504: END IF;
1505: EXCEPTION
1506: WHEN OTHERS THEN
1507: arp_message.set_line( 'TRX_SUMMARY_ACCOUNT_MERGE');
1503: arp_message.set_token('NUM_ROWS',to_char(l_count));
1504: END IF;
1505: EXCEPTION
1506: WHEN OTHERS THEN
1507: arp_message.set_line( 'TRX_SUMMARY_ACCOUNT_MERGE');
1508: RAISE;
1509: END TRX_SUMMARY_ACCOUNT_MERGE;
1510:
1511: END AR_CMGT_ACCOUNT_MERGE;