1: PACKAGE BODY AR_MCC_MERGE AS
2: /* $Header: ARXCMCRB.pls 120.0 2005/05/05 05:02:24 bdhotkar noship $ */
3: --+=======================================================================+
4: --| Copyright (c) 2001 Oracle Corporation |
5: --| Redwood Shores, CA, USA |
8: --| FILENAME |
9: --| ARXCMCRB.pls |
10: --| |
11: --| DESCRIPTION |
12: --| Package body of AR_MCC_MERGE |
13: --| |
14: --| PUBLIC PROCEDURES |
15: --| Customer_Merge |
16: --| Note: This procedure will be called by the main Customer Merge(TCA) |
23:
24: --=================
25: -- CONSTANTS
26: --=================
27: G_AR_MCC_MERGE VARCHAR2(30) := 'AR_MCC_MERGE';
28:
29: --====================
30: -- Debug log variables
31: --====================
208:
209: BEGIN
210:
211: -- initialize log
212: AR_MCC_MERGE.Log_Initialize;
213:
214: -- initialize the message stack
215: FND_MSG_PUB.Initialize;
216:
213:
214: -- initialize the message stack
215: FND_MSG_PUB.Initialize;
216:
217: arp_message.set_line('AR_MCC_MERGE.Customer_Merge()+');
218:
219: AR_MCC_MERGE.Log
220: (AR_MCC_MERGE.G_LOG_PROCEDURE
221: ,'Start of Proc:Customer Merge'
215: FND_MSG_PUB.Initialize;
216:
217: arp_message.set_line('AR_MCC_MERGE.Customer_Merge()+');
218:
219: AR_MCC_MERGE.Log
220: (AR_MCC_MERGE.G_LOG_PROCEDURE
221: ,'Start of Proc:Customer Merge'
222: );
223:
216:
217: arp_message.set_line('AR_MCC_MERGE.Customer_Merge()+');
218:
219: AR_MCC_MERGE.Log
220: (AR_MCC_MERGE.G_LOG_PROCEDURE
221: ,'Start of Proc:Customer Merge'
222: );
223:
224: IF process_mode = 'LOCK' THEN
227:
228: open check_credit_usage_cur;
229: close check_credit_usage_cur;
230:
231: AR_MCC_MERGE.Log
232: (AR_MCC_MERGE.G_LOG_EVENT
233: ,'Rows locked from HZ_CREDIT_USAGES'
234: );
235: ELSE
228: open check_credit_usage_cur;
229: close check_credit_usage_cur;
230:
231: AR_MCC_MERGE.Log
232: (AR_MCC_MERGE.G_LOG_EVENT
233: ,'Rows locked from HZ_CREDIT_USAGES'
234: );
235: ELSE
236: arp_message.set_line('AR_MCC_MERGE.Customer_Merge()+');
232: (AR_MCC_MERGE.G_LOG_EVENT
233: ,'Rows locked from HZ_CREDIT_USAGES'
234: );
235: ELSE
236: arp_message.set_line('AR_MCC_MERGE.Customer_Merge()+');
237:
238: -- Get each customer account being merged
239: FOR cust_dup IN customer_account_dup_cur LOOP
240:
238: -- Get each customer account being merged
239: FOR cust_dup IN customer_account_dup_cur LOOP
240:
241: l_duplicate_id := cust_dup.duplicate_id;
242: AR_MCC_MERGE.Log
243: (AR_MCC_MERGE.G_LOG_EVENT
244: ,'Customer Account Id:' || to_char(l_duplicate_id)
245: );
246:
239: FOR cust_dup IN customer_account_dup_cur LOOP
240:
241: l_duplicate_id := cust_dup.duplicate_id;
242: AR_MCC_MERGE.Log
243: (AR_MCC_MERGE.G_LOG_EVENT
244: ,'Customer Account Id:' || to_char(l_duplicate_id)
245: );
246:
247: -- Get Customer Merge Header Id
251: INTO l_customer_merge_header_id;
252:
253: CLOSE customer_merge_header_cur;
254:
255: AR_MCC_MERGE.Log
256: (AR_MCC_MERGE.G_LOG_EVENT
257: ,'Customer Merge Header Id:' || to_char(l_customer_merge_header_id)
258: );
259:
252:
253: CLOSE customer_merge_header_cur;
254:
255: AR_MCC_MERGE.Log
256: (AR_MCC_MERGE.G_LOG_EVENT
257: ,'Customer Merge Header Id:' || to_char(l_customer_merge_header_id)
258: );
259:
260: -- Select statement to get the columns of the credit usage belongs
287: ELSE
288: l_delete_flag := 'N';
289: END IF;
290:
291: AR_MCC_MERGE.Log
292: (AR_MCC_MERGE.G_LOG_EVENT
293: ,'Credit Usage Delete Flag:' || (l_delete_flag)
294: );
295:
288: l_delete_flag := 'N';
289: END IF;
290:
291: AR_MCC_MERGE.Log
292: (AR_MCC_MERGE.G_LOG_EVENT
293: ,'Credit Usage Delete Flag:' || (l_delete_flag)
294: );
295:
296: -- check audit account merge profile is enabled
333: , hz_utility_pub.last_update_date
334: , hz_utility_pub.last_updated_by
335: );
336:
337: AR_MCC_MERGE.Log
338: (AR_MCC_MERGE.G_LOG_EVENT
339: ,'Records inserted in LOG table'
340: );
341:
334: , hz_utility_pub.last_updated_by
335: );
336:
337: AR_MCC_MERGE.Log
338: (AR_MCC_MERGE.G_LOG_EVENT
339: ,'Records inserted in LOG table'
340: );
341:
342: END IF; -- delete flag check
355: -- summation of all %bulk_rowcount
356: g_count := sql%rowcount;
357: l_total_count := g_count;
358:
359: AR_MCC_MERGE.Log
360: (AR_MCC_MERGE.G_LOG_EVENT
361: ,'Total rows deleted: ' || to_char(l_total_count)
362: );
363:
356: g_count := sql%rowcount;
357: l_total_count := g_count;
358:
359: AR_MCC_MERGE.Log
360: (AR_MCC_MERGE.G_LOG_EVENT
361: ,'Total rows deleted: ' || to_char(l_total_count)
362: );
363:
364: -- Total number of rows deleted
370: END LOOP; -- customer account loop
371:
372: END IF; -- check for process mode
373:
374: arp_message.set_line('AR_MCC_MERGE.Customer_Merge()-');
375:
376: EXCEPTION
377: WHEN OTHERS THEN
378: arp_message.set_error('AR_MCC_MERGE.Customer_Merge');
374: arp_message.set_line('AR_MCC_MERGE.Customer_Merge()-');
375:
376: EXCEPTION
377: WHEN OTHERS THEN
378: arp_message.set_error('AR_MCC_MERGE.Customer_Merge');
379: l_errorcode := SQLCODE;
380: l_errortext := SUBSTR(SQLERRM, 1,200);
381: AR_MCC_MERGE.Log
382: (AR_MCC_MERGE.G_LOG_EXCEPTION
377: WHEN OTHERS THEN
378: arp_message.set_error('AR_MCC_MERGE.Customer_Merge');
379: l_errorcode := SQLCODE;
380: l_errortext := SUBSTR(SQLERRM, 1,200);
381: AR_MCC_MERGE.Log
382: (AR_MCC_MERGE.G_LOG_EXCEPTION
383: ,'Others:' || to_char(l_errorcode) || l_errortext
384: );
385: raise;
378: arp_message.set_error('AR_MCC_MERGE.Customer_Merge');
379: l_errorcode := SQLCODE;
380: l_errortext := SUBSTR(SQLERRM, 1,200);
381: AR_MCC_MERGE.Log
382: (AR_MCC_MERGE.G_LOG_EXCEPTION
383: ,'Others:' || to_char(l_errorcode) || l_errortext
384: );
385: raise;
386:
387: END Customer_Merge;
388:
389:
390:
391: END AR_MCC_MERGE;