9: set_num NUMBER,
10: process_mode VARCHAR2
11: );
12:
13: --merge hz_customer_profiles
14: procedure ar_cp (
15: req_id NUMBER,
16: set_num NUMBER,
17: process_mode VARCHAR2
60: BEGIN
61:
62: arp_message.set_line( 'ARP_CMERGE_ARCPF.MERGE()+' );
63:
64: --merge hz_customer_profiles
65: ar_cp( req_id, set_num, process_mode );
66:
67: --merge hz_customer_profile_amts
68: ar_cpa( req_id, set_num, process_mode );
204: | PROCEDURE
205: | ar_cp
206: |
207: | DESCRIPTION
208: | merge in HZ_CUSTOMER_PROFILES
209: |
210: | SCOPE - PRIVATE
211: |
212: | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
238:
239: --cursor c1 and c2 work in inactive mode.
240: CURSOR c1 is
241: SELECT cust_account_profile_id
242: FROM hz_customer_profiles, ra_customer_merges m
243: WHERE site_use_id = m.duplicate_site_id
244: AND m.process_flag = 'N'
245: AND m.request_id = req_id
246: AND m.set_number = set_num
247: FOR UPDATE NOWAIT;
248:
249: CURSOR c2 is
250: SELECT cust_account_profile_id
251: FROM hz_customer_profiles yt, ra_customer_merges m
252: WHERE cust_account_id = m.duplicate_id
253: AND m.process_flag = 'N'
254: AND m.request_id = req_id
255: AND m.set_number = set_num
264:
265: --cursor c3 work in 'delete' mode.
266: CURSOR c3 is
267: SELECT cust_account_profile_id
268: FROM hz_customer_profiles yt, ra_customer_merges m
269: WHERE cust_account_id = m.duplicate_id
270: AND m.process_flag = 'N'
271: AND m.request_id = req_id
272: AND m.set_number = set_num
285:
286: IF process_mode = 'LOCK' THEN
287:
288: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE' );
289: arp_message.set_token( 'TABLE_NAME', 'HZ_CUSTOMER_PROFILES', FALSE );
290:
291: OPEN c1;
292: CLOSE c1;
293:
302: /*************** 'inactivate' mode ***************/
303:
304: --site level inactivate
305: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
306: arp_message.set_token( 'TABLE_NAME', 'HZ_CUSTOMER_PROFILES', FALSE );
307:
308: UPDATE hz_customer_profiles yt
309: SET status = 'I',
310: last_update_date = sysdate,
304: --site level inactivate
305: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
306: arp_message.set_token( 'TABLE_NAME', 'HZ_CUSTOMER_PROFILES', FALSE );
307:
308: UPDATE hz_customer_profiles yt
309: SET status = 'I',
310: last_update_date = sysdate,
311: last_updated_by = hz_utility_v2pub.user_id,
312: last_update_login = hz_utility_v2pub.last_update_login,
328: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
329:
330: --customer level inactivate
331: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
332: arp_message.set_token( 'TABLE_NAME', 'HZ_CUSTOMER_PROFILES', FALSE );
333:
334: UPDATE hz_customer_profiles yt
335: SET status = 'I',
336: last_update_date = sysdate,
330: --customer level inactivate
331: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
332: arp_message.set_token( 'TABLE_NAME', 'HZ_CUSTOMER_PROFILES', FALSE );
333:
334: UPDATE hz_customer_profiles yt
335: SET status = 'I',
336: last_update_date = sysdate,
337: last_updated_by = hz_utility_v2pub.user_id,
338: last_update_login = hz_utility_v2pub.last_update_login,
364: --Physically delete them after merge.
365:
366: --site level 'delete'
367: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
368: arp_message.set_token( 'TABLE_NAME', 'HZ_CUSTOMER_PROFILES', FALSE );
369:
370: UPDATE hz_customer_profiles
371: SET status = 'D'
372: WHERE site_use_id IN (
366: --site level 'delete'
367: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
368: arp_message.set_token( 'TABLE_NAME', 'HZ_CUSTOMER_PROFILES', FALSE );
369:
370: UPDATE hz_customer_profiles
371: SET status = 'D'
372: WHERE site_use_id IN (
373: SELECT m.duplicate_site_id
374: FROM ra_customer_merges m
383: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
384:
385: --customer level 'delete'
386: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
387: arp_message.set_token( 'TABLE_NAME', 'HZ_CUSTOMER_PROFILES', FALSE );
388:
389: UPDATE hz_customer_profiles yt
390: SET status = 'D'
391: WHERE cust_account_id IN (
385: --customer level 'delete'
386: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
387: arp_message.set_token( 'TABLE_NAME', 'HZ_CUSTOMER_PROFILES', FALSE );
388:
389: UPDATE hz_customer_profiles yt
390: SET status = 'D'
391: WHERE cust_account_id IN (
392: SELECT m.duplicate_id
393: FROM ra_customer_merges m
601: ) IS
602:
603: CURSOR profiles IS
604: SELECT cust_account_profile_id
605: FROM HZ_CUSTOMER_PROFILES, ra_customer_merges m
606: WHERE cust_account_id = m.duplicate_id
607: AND m.process_flag = 'N'
608: AND m.request_id = req_id
609: AND m.set_number = set_num
652:
653: /*****************************************************/
654:
655: arp_message.set_name( 'AR', 'AR_DELETING_TABLE');
656: arp_message.set_token( 'TABLE_NAME', 'HZ_CUSTOMER_PROFILES', FALSE );
657:
658: OPEN profiles;
659: CLOSE profiles;
660:
657:
658: OPEN profiles;
659: CLOSE profiles;
660:
661: DELETE FROM HZ_CUSTOMER_PROFILES
662: WHERE cust_account_id IN (
663: SELECT m.duplicate_id
664: FROM ra_customer_merges m
665: WHERE m.process_flag = 'N'