[Home] [Help]
2490: | PROCEDURE
2491: | ra_cont
2492: |
2493: | DESCRIPTION
2494: | merge in HZ_CUST_ACCOUNT_ROLES with
2495: | role_type = 'CONTACT'
2496: |
2497: | SCOPE - PRIVATE
2498: |
2517: set_num NUMBER,
2518: process_mode VARCHAR2
2519: ) IS
2520:
2521: l_party_id HZ_CUST_ACCOUNT_ROLES.PARTY_ID%TYPE;
2522:
2523: CURSOR c1 IS
2524: SELECT cust_account_role_id
2525: FROM hz_cust_account_roles yt, ra_customer_merges m
2521: l_party_id HZ_CUST_ACCOUNT_ROLES.PARTY_ID%TYPE;
2522:
2523: CURSOR c1 IS
2524: SELECT cust_account_role_id
2525: FROM hz_cust_account_roles yt, ra_customer_merges m
2526: WHERE cust_acct_site_id = m.duplicate_address_id
2527: AND m.process_flag = 'N'
2528: AND m.request_id = req_id
2529: AND m.set_number = set_num
2531: FOR UPDATE NOWAIT;
2532:
2533: CURSOR c2 IS
2534: SELECT cust_account_role_id
2535: FROM hz_cust_account_roles yt, ra_customer_merges m
2536: WHERE cust_account_id = m.duplicate_id
2537: AND m.process_flag = 'N'
2538: AND m.request_id = req_id
2539: AND m.set_number = set_num
2544: -- Begin Bug No : 14201229
2545:
2546: CURSOR c2_1 IS
2547: SELECT cust_account_role_id
2548: FROM hz_cust_account_roles yt, ra_customer_merges m, hz_cust_accounts c
2549: WHERE yt.cust_account_id = m.duplicate_id
2550: AND c.cust_account_id = m.duplicate_id
2551: AND c.status <> 'A'
2552: AND m.process_flag = 'N'
2557: FOR UPDATE NOWAIT;
2558:
2559: CURSOR c3_1 is
2560: SELECT yt.party_id
2561: FROM HZ_CUST_ACCOUNT_ROLES yt,ra_customer_merges m,HZ_CUST_ACCOUNTS ca,hz_relationships rel
2562: WHERE m.customer_id=ca.cust_account_id
2563: AND ((ca.status = 'A' and yt.cust_acct_site_id is not null) or (ca.status <> 'A'))
2564: AND m.duplicate_id = yt.cust_account_id
2565: AND rel.party_id = yt.party_id
2574: -- End Bug No : 14201229
2575: ----Bug No: 5067291
2576: CURSOR c3 is
2577: SELECT yt.party_id
2578: FROM HZ_CUST_ACCOUNT_ROLES yt,ra_customer_merges m,HZ_CUST_ACCOUNTS ca,hz_relationships rel
2579: WHERE m.customer_id=ca.cust_account_id
2580: AND m.duplicate_id = yt.cust_account_id
2581: AND rel.party_id = yt.party_id
2582: AND rel.subject_type = 'PERSON'
2596: /* locking tables by opening and closing cursors */
2597: IF process_mode = 'LOCK' then
2598:
2599: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE' );
2600: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2601:
2602: OPEN c1;
2603: CLOSE c1;
2604:
2622: FETCH c3 INTO l_party_id;
2623: IF c3%FOUND THEN
2624: /*********************Inactivate account site/account role...Bug No. 5067291*********/
2625: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2626: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2627: UPDATE HZ_CUST_ACCOUNT_ROLES yt
2628: SET status = 'I',
2629: last_update_date = sysdate,
2630: last_updated_by = hz_utility_v2pub.user_id,
2623: IF c3%FOUND THEN
2624: /*********************Inactivate account site/account role...Bug No. 5067291*********/
2625: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2626: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2627: UPDATE HZ_CUST_ACCOUNT_ROLES yt
2628: SET status = 'I',
2629: last_update_date = sysdate,
2630: last_updated_by = hz_utility_v2pub.user_id,
2631: last_update_login = hz_utility_v2pub.last_update_login,
2636: WHERE party_id = l_party_id
2637: AND nvl(status,'A') ='A';
2638:
2639: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2640: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2641:
2642:
2643: g_count := sql%rowcount;
2644:
2649:
2650: /************** account site level update ************/
2651:
2652: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2653: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2654:
2655: UPDATE HZ_CUST_ACCOUNT_ROLES yt
2656: SET (cust_account_id, cust_acct_site_id) = (
2657: SELECT MIN(m.customer_id), MIN(m.customer_address_id)
2651:
2652: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2653: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2654:
2655: UPDATE HZ_CUST_ACCOUNT_ROLES yt
2656: SET (cust_account_id, cust_acct_site_id) = (
2657: SELECT MIN(m.customer_id), MIN(m.customer_address_id)
2658: FROM ra_customer_merges m
2659: WHERE yt.cust_account_id = m.duplicate_id
2684:
2685: /************** account level update ************/
2686:
2687: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
2688: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2689:
2690: UPDATE HZ_CUST_ACCOUNT_ROLES yt
2691: SET cust_account_id = (
2692: SELECT m.customer_id
2686:
2687: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
2688: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2689:
2690: UPDATE HZ_CUST_ACCOUNT_ROLES yt
2691: SET cust_account_id = (
2692: SELECT m.customer_id
2693: FROM ra_customer_merges m
2694: WHERE yt.cust_account_id = m.duplicate_id
2717: -- Start Bug 4712462
2718: FOR rec IN (
2719: SELECT min(cust_account_role_id) cust_account_role_id, cust_account_id,
2720: nvl(cust_acct_site_id,-1) cust_acct_site_id,party_id
2721: FROM hz_cust_account_roles r, ra_customer_merges m
2722: where m.request_id = req_id AND m.set_number = set_num
2723: AND r.cust_account_id = m.customer_id AND m.process_flag = 'N'
2724: GROUP BY cust_account_id,cust_acct_site_id,status,party_id
2725: HAVING NVL(STATUS,'A') ='A' AND count(1) > 1) LOOP
2722: where m.request_id = req_id AND m.set_number = set_num
2723: AND r.cust_account_id = m.customer_id AND m.process_flag = 'N'
2724: GROUP BY cust_account_id,cust_acct_site_id,status,party_id
2725: HAVING NVL(STATUS,'A') ='A' AND count(1) > 1) LOOP
2726: UPDATE hz_cust_account_roles SET status ='I'
2727: WHERE cust_account_role_id <> rec.cust_account_role_id
2728: AND cust_account_id = rec.cust_account_id
2729: AND party_id = rec.party_id
2730: AND nvl(cust_acct_site_id,-1) = rec.cust_acct_site_id
2746: FETCH c3_1 INTO l_party_id;
2747: IF c3_1%FOUND THEN
2748: /*********************Inactivate account site/account role...Bug No. 5067291*********/
2749: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2750: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2751: UPDATE HZ_CUST_ACCOUNT_ROLES yt
2752: SET status = 'I',
2753: last_update_date = sysdate,
2754: last_updated_by = hz_utility_v2pub.user_id,
2747: IF c3_1%FOUND THEN
2748: /*********************Inactivate account site/account role...Bug No. 5067291*********/
2749: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2750: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2751: UPDATE HZ_CUST_ACCOUNT_ROLES yt
2752: SET status = 'I',
2753: last_update_date = sysdate,
2754: last_updated_by = hz_utility_v2pub.user_id,
2755: last_update_login = hz_utility_v2pub.last_update_login,
2760: WHERE party_id = l_party_id
2761: AND nvl(status,'A') ='A';
2762:
2763: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2764: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2765:
2766: g_count := sql%rowcount;
2767:
2768: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2772:
2773: /************** account site level update ************/
2774:
2775: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2776: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2777:
2778: UPDATE HZ_CUST_ACCOUNT_ROLES yt
2779: SET (cust_account_id, cust_acct_site_id) = (
2780: SELECT MIN(m.customer_id), MIN(m.customer_address_id)
2774:
2775: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2776: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2777:
2778: UPDATE HZ_CUST_ACCOUNT_ROLES yt
2779: SET (cust_account_id, cust_acct_site_id) = (
2780: SELECT MIN(m.customer_id), MIN(m.customer_address_id)
2781: FROM ra_customer_merges m
2782: WHERE yt.cust_account_id = m.duplicate_id
2807:
2808: /************** account level update ************/
2809:
2810: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
2811: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2812:
2813: UPDATE HZ_CUST_ACCOUNT_ROLES yt
2814: SET cust_account_id = (
2815: SELECT m.customer_id
2809:
2810: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
2811: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2812:
2813: UPDATE HZ_CUST_ACCOUNT_ROLES yt
2814: SET cust_account_id = (
2815: SELECT m.customer_id
2816: FROM ra_customer_merges m
2817: WHERE yt.cust_account_id = m.duplicate_id
2841: -- Start Bug 4712462
2842: FOR rec IN (
2843: SELECT min(cust_account_role_id) cust_account_role_id, cust_account_id,
2844: nvl(cust_acct_site_id,-1) cust_acct_site_id,party_id
2845: FROM hz_cust_account_roles r, ra_customer_merges m
2846: where m.request_id = req_id AND m.set_number = set_num
2847: AND r.cust_account_id = m.customer_id AND m.process_flag = 'N'
2848: GROUP BY cust_account_id,cust_acct_site_id,status,party_id
2849: HAVING NVL(STATUS,'A') ='A' AND count(1) > 1) LOOP
2846: where m.request_id = req_id AND m.set_number = set_num
2847: AND r.cust_account_id = m.customer_id AND m.process_flag = 'N'
2848: GROUP BY cust_account_id,cust_acct_site_id,status,party_id
2849: HAVING NVL(STATUS,'A') ='A' AND count(1) > 1) LOOP
2850: UPDATE hz_cust_account_roles SET status ='I'
2851: WHERE cust_account_role_id <> rec.cust_account_role_id
2852: AND cust_account_id = rec.cust_account_id
2853: AND party_id = rec.party_id
2854: AND nvl(cust_acct_site_id,-1) = rec.cust_acct_site_id
3287: | PROCEDURE
3288: | do_cust_merge_contacts
3289: |
3290: | DESCRIPTION
3291: | merge in HZ_CUST_ACCOUNT_ROLES with
3292: | role_type = 'CONTACT' in account level
3293: |
3294: | SCOPE - PRIVATE
3295: |
3362: --FOR PARTY REL REC
3363: rel.relationship_id,
3364: -- FOR ORG-CONTACT REC
3365: org.org_contact_id
3366: FROM hz_cust_account_roles acct_role,
3367: hz_org_contacts org,
3368: hz_relationships rel,
3369: hz_cust_accounts acct
3370: WHERE acct_role.role_type = 'CONTACT'
3405: p_to_party_id,
3406: x_org_party_id );
3407:
3408: -- Update the customer org contact with the new org contact id.
3409: UPDATE hz_cust_account_roles
3410: SET party_id = x_org_party_id
3411: WHERE cust_account_role_id = l_cust_acct_role_id;
3412:
3413: END LOOP;
3427: | PROCEDURE
3428: | do_site_merge_contacts
3429: |
3430: | DESCRIPTION
3431: | merge in HZ_CUST_ACCOUNT_ROLES with
3432: | role_type = 'CONTACT' in account site level
3433: |
3434: | SCOPE - PRIVATE
3435: |
3521: org.org_contact_id
3522: --as per discussion with Gautam Prothia, we ignore party_site_id in customer
3523: --merge context
3524: -- org.party_site_id
3525: FROM hz_cust_account_roles acct_role,
3526: hz_org_contacts org,
3527: hz_relationships rel,
3528: hz_cust_accounts acct
3529: WHERE acct_role.role_type = 'CONTACT'
3608: p_to_party_id,
3609: x_org_party_id );
3610:
3611: -- Update the customer org contact with the new org contact id.
3612: UPDATE hz_cust_account_roles
3613: SET party_id = x_org_party_id
3614: WHERE cust_account_role_id = l_cust_acct_role_id;
3615:
3616: END LOOP;
7199: arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) inserted in HZ_CUST_ACCOUNTS_M');
7200:
7201:
7202:
7203: ---------Insert into hz_cust_account_roles_m--------------
7204: --Because roles can be set up at acct and site level, we need to select
7205: --distict duplicate and customer_merge_header_id if acct with multiple sites
7206: --have roles set up at account level.
7207:
7204: --Because roles can be set up at acct and site level, we need to select
7205: --distict duplicate and customer_merge_header_id if acct with multiple sites
7206: --have roles set up at account level.
7207:
7208: INSERT INTO hz_cust_account_roles_m(
7209: customer_merge_header_id,
7210: cust_account_role_id ,
7211: party_id ,
7212: cust_account_id ,
7352: from ra_customer_merges cm
7353: where cm.process_flag = 'N'
7354: and cm.request_id = req_id
7355: and cm.set_number = set_num
7356: and cm.duplicate_id <> cm.customer_id), hz_cust_account_roles ar
7357: WHERE ( ar.cust_account_id = duplicate_id OR
7358: ar.cust_acct_site_id = duplicate_address_id )
7359: AND ar.role_type = 'CONTACT';
7360:
7357: WHERE ( ar.cust_account_id = duplicate_id OR
7358: ar.cust_acct_site_id = duplicate_address_id )
7359: AND ar.role_type = 'CONTACT';
7360:
7361: arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) inserted in HZ_CUST_ACCOUNT_ROLES_M');
7362:
7363: ---------Insert into hz_customer_profiles_m--------------
7364: --Because profiles can be set up at acct and site use level, we need to select
7365: --distict duplicate and customer_merge_header_id if acct with multiple sites