[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cust_account_id, party_id, account_number, orig_system_reference, status
FROM HZ_CUST_ACCOUNTS
WHERE cust_account_id = c_account_id
AND status in ('A','I');
SELECT cust_account_id, party_id, account_number, orig_system_reference , status
INTO l_account_id, x_party_id,l_account_number, l_account_osr, x_account_status
FROM HZ_CUST_ACCOUNTS
WHERE account_number = p_account_number
AND status in ('A','I');
SELECT cust_acct_site_id, cust_account_id, party_site_id, status, org_id, orig_system_reference
FROM HZ_CUST_ACCT_SITES_ALL
WHERE cust_acct_site_id = c_acct_site_id;
SELECT party_site_number, location_id INTo l_party_site_number, x_location_id
FROM hz_party_sites
WHERE party_site_id = l_party_site_id;
SELECT site_use_id, cust_acct_site_id, site_use_code, status, location, primary_flag
FROM hz_cust_site_uses_all
WHERE site_use_id = c_site_use_id;
SELECT party_name, party_type, person_first_name, person_last_name, orig_system_reference
FROM hz_parties
WHERE party_id = c_party_id;
SELECT 'Y'
FROM hz_cust_site_uses_all
WHERE cust_acct_site_id = c_acct_site_id
AND rownum = 1;
g_location_id.DELETE;
g_site_use_location.DELETE;
select 'Y' , cmh.duplicate_number, cmh.customer_number
into l_exist, tmp_duplicate_number, tmp_customer_number
from ra_customer_merges cm, ra_customer_merge_headers cmh , hz_cust_acct_sites_all cas
where cas.cust_account_id = cm.duplicate_id
and (cmh.process_flag NOT IN ('FAILED' ,'Y','DISCARD') )
and cmh.customer_merge_header_id = cm.customer_merge_header_id
and cas.cust_account_id = l_account_merge_request_obj.merge_from_account.account_id
and cas.org_id = cm.org_id
and ( (cas.cust_acct_site_id = cm.duplicate_address_id) OR
(cas.cust_acct_site_id = cm.customer_address_id) )
and ROWNUM = 1;
FND_MESSAGE.set_name ('AR', 'HZ_PROMPT_SELECTED_ACCOUNT');
/* Insert Merge header */
l_header_id := ra_customer_merge_headers_s.nextval;
INSERT INTO RA_CUSTOMER_MERGE_HEADERS(
CUSTOMER_MERGE_HEADER_ID
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, CUSTOMER_ID
, CUSTOMER_NAME
, CUSTOMER_NUMBER
, CUSTOMER_REF
, DUPLICATE_ID
, DUPLICATE_NAME
, DUPLICATE_NUMBER
, DUPLICATE_REF
, DELETE_DUPLICATE_FLAG
, PROCESS_FLAG
, CUSTOMER_FIRST_NAME
, CUSTOMER_LAST_NAME
, CUSTOMER_TYPE
, DUPLICATE_FIRST_NAME
, DUPLICATE_LAST_NAME
, DUPLICATE_TYPE
, MERGE_REASON_CODE
, PRIORITY
, ORG_ID)
VALUES(l_header_id
, sysdate
, HZ_UTILITY_V2PUB.CREATED_BY
, sysdate
, HZ_UTILITY_V2PUB.LAST_UPDATED_BY
, HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN
, l_account_merge_request_obj.merge_to_account.account_id
, l_to_customer_name
, l_account_merge_request_obj.merge_to_account.account_number
, l_to_party_osr
, l_account_merge_request_obj.merge_from_account.account_id
, l_from_customer_name
, l_account_merge_request_obj.merge_from_account.account_number
, l_from_party_osr
, NVL(l_account_merge_request_obj.delete_after_merge_flag, 'N')
, 'SAVED'
, l_to_cust_first_name
, l_to_cust_last_name
, l_to_customer_type
, l_from_cust_first_name
, l_from_cust_last_name
, l_from_customer_type
, NVL(l_account_merge_request_obj.merge_reason_code, 'DEDUP')
, NVL(l_account_merge_request_obj.priority, 'P3')
, l_account_merge_request_obj.org_id);
select substr(ARP_ADDR_PKG.FORMAT_ADDRESS(LOC.ADDRESS_STYLE
, LOC.ADDRESS1
, LOC.ADDRESS2
, LOC.ADDRESS3
, LOC.ADDRESS4
, LOC.CITY
, LOC.COUNTY
, LOC.STATE
, LOC.PROVINCE
, LOC.POSTAL_CODE
, TERR.TERRITORY_SHORT_NAME ),1,240) INTO l_from_address
FROM hz_locations loc,
FND_TERRITORIES_VL TERR
WHERE loc.location_id = l_from_location_id
and LOC.COUNTRY = TERR.TERRITORY_CODE (+);
select substr(ARP_ADDR_PKG.FORMAT_ADDRESS(LOC.ADDRESS_STYLE
, LOC.ADDRESS1
, LOC.ADDRESS2
, LOC.ADDRESS3
, LOC.ADDRESS4
, LOC.CITY
, LOC.COUNTY
, LOC.STATE
, LOC.PROVINCE
, LOC.POSTAL_CODE
, TERR.TERRITORY_SHORT_NAME ),1,240) INTO l_to_address
FROM hz_locations loc,
FND_TERRITORIES_VL TERR
WHERE loc.location_id = l_to_location_id
and LOC.COUNTRY = TERR.TERRITORY_CODE (+);
INSERT INTO RA_CUSTOMER_MERGES(
CUSTOMER_MERGE_ID
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, PROCESS_FLAG
, CUSTOMER_ID
, CUSTOMER_NAME
, CUSTOMER_NUMBER
, CUSTOMER_REF
, CUSTOMER_ADDRESS_ID
, CUSTOMER_ADDRESS
, CUSTOMER_SITE_ID
, CUSTOMER_SITE_CODE
, CUSTOMER_LOCATION
, CUSTOMER_PRIMARY_FLAG
, DELETE_DUPLICATE_FLAG
, DUPLICATE_ADDRESS
, DUPLICATE_ADDRESS_ID
, DUPLICATE_ID
, DUPLICATE_LOCATION
, DUPLICATE_NAME
, DUPLICATE_NUMBER
, DUPLICATE_PRIMARY_FLAG
, DUPLICATE_REF
, DUPLICATE_SITE_CODE
, DUPLICATE_SITE_ID
, SET_NUMBER
, CUSTOMER_MERGE_HEADER_ID
, CUSTOMER_FIRST_NAME
, CUSTOMER_LAST_NAME
, CUSTOMER_TYPE
, DUPLICATE_FIRST_NAME
, DUPLICATE_LAST_NAME
, DUPLICATE_TYPE
, CUSTOMER_CREATESAME
, DUPLICATE_SITE_NUMBER
, CUSTOMER_SITE_NUMBER
, ORG_ID)
VALUES( ra_customer_merges_s.nextval /*CUSTOMER_MERGE_ID*/
, sysdate /*CREATION_DATE*/
, HZ_UTILITY_V2PUB.CREATED_BY /*CREATED_BY*/
, sysdate /*LAST_UPDATE_DATE*/
, HZ_UTILITY_V2PUB.LAST_UPDATED_BY /*LAST_UPDATED_BY*/
, HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN /*LAST_UPDATE_LOGIN*/
, 'SAVED' /*PROCESS_FLAG*/
, l_account_merge_request_obj.merge_to_account.account_id /*CUSTOMER_ID*/
, l_to_customer_name /*CUSTOMER_NAME*/
, l_account_merge_request_obj.merge_to_account.account_number /*CUSTOMER_NUMBER*/
, l_account_merge_request_obj.site_merge_details(i).merge_to_account_site_obj.account_site_orig_sys_ref /*CUSTOMER_REF*/
, l_account_merge_request_obj.site_merge_details(i).merge_to_account_site_obj.account_site_id /*CUSTOMER_ADDRESS_ID*/
, l_to_address /*CUSTOMER_ADDRESS*/
, l_account_merge_request_obj.site_merge_details(i).site_use_merge_details(j).merge_to_site_use_obj.acct_site_use_id /*CUSTOMER_SITE_ID*/
, l_account_merge_request_obj.site_merge_details(i).site_use_merge_details(j).merge_to_site_use_obj.acct_site_use_type /*CUSTOMER_SITE_CODE*/
, l_to_site_use_location /*CUSTOMER_LOCATION*/
, g_primary_flag(l_account_merge_request_obj.site_merge_details(i).site_use_merge_details(j).merge_to_site_use_obj.acct_site_use_id) /*CUSTOMER_PRIMARY_FLAG*/
, NVL(l_account_merge_request_obj.delete_after_merge_flag, 'N') /*DELETE_DUPLICATE_FLAG*/
, l_from_address /* DUPLICATE_ADDRESS*/
, l_account_merge_request_obj.site_merge_details(i).merge_from_account_site_obj.account_site_id /*DUPLICATE_ADDRESS_ID*/
, l_account_merge_request_obj.merge_from_account.account_id/* DUPLICATE_ID*/
, l_from_site_use_location /* DUPLICATE_LOCATION*/
, l_from_customer_name/* DUPLICATE_NAME*/
, l_account_merge_request_obj.merge_from_account.account_number/* DUPLICATE_NUMBER*/
, g_primary_flag(l_account_merge_request_obj.site_merge_details(i).site_use_merge_details(j).merge_from_site_use_obj.acct_site_use_id) /*DUPLICATE_PRIMARY_FLAG*/
, l_account_merge_request_obj.site_merge_details(i).merge_from_account_site_obj.account_site_orig_sys_ref/* DUPLICATE_REF*/
, l_account_merge_request_obj.site_merge_details(i).site_use_merge_details(j).merge_from_site_use_obj.acct_site_use_type /*DUPLICATE_SITE_CODE*/
, l_account_merge_request_obj.site_merge_details(i).site_use_merge_details(j).merge_from_site_use_obj.acct_site_use_id /*DUPLICATE_SITE_ID*/
, 1 /* SET_NUMBER*/
, l_header_id /* CUSTOMER_MERGE_HEADER_ID*/
, l_to_cust_first_name /* CUSTOMER_FIRST_NAME*/
, l_to_cust_last_name /* CUSTOMER_LAST_NAME*/
, l_to_customer_type /* CUSTOMER_TYPE*/
, l_from_cust_first_name /* DUPLICATE_FIRST_NAME*/
, l_from_cust_last_name /* DUPLICATE_LAST_NAME */
, l_from_customer_type /* DUPLICATE_TYPE */
, 'N' /* CUSTOMER_CREATESAME*/
, l_account_merge_request_obj.site_merge_details(i).merge_from_account_site_obj.SITE_NUMBER /*DUPLICATE_SITE_NUMBER*/
, l_account_merge_request_obj.site_merge_details(i).merge_to_account_site_obj.SITE_NUMBER /*CUSTOMER_SITE_NUMBER */
, l_account_merge_request_obj.site_merge_details(i).org_id /*ORG_ID*/
);
INSERT INTO RA_CUSTOMER_MERGES(
CUSTOMER_MERGE_ID
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, PROCESS_FLAG
, CUSTOMER_ID
, CUSTOMER_NAME
, CUSTOMER_NUMBER
, CUSTOMER_REF
, CUSTOMER_ADDRESS_ID
, CUSTOMER_ADDRESS
, CUSTOMER_SITE_ID
, CUSTOMER_SITE_CODE
, CUSTOMER_LOCATION
, CUSTOMER_PRIMARY_FLAG
, DELETE_DUPLICATE_FLAG
, DUPLICATE_ADDRESS
, DUPLICATE_ADDRESS_ID
, DUPLICATE_ID
, DUPLICATE_LOCATION
, DUPLICATE_NAME
, DUPLICATE_NUMBER
, DUPLICATE_PRIMARY_FLAG
, DUPLICATE_REF
, DUPLICATE_SITE_CODE
, DUPLICATE_SITE_ID
, SET_NUMBER
, CUSTOMER_MERGE_HEADER_ID
, CUSTOMER_FIRST_NAME
, CUSTOMER_LAST_NAME
, CUSTOMER_TYPE
, DUPLICATE_FIRST_NAME
, DUPLICATE_LAST_NAME
, DUPLICATE_TYPE
, CUSTOMER_CREATESAME
, DUPLICATE_SITE_NUMBER
, CUSTOMER_SITE_NUMBER
, ORG_ID)
VALUES( ra_customer_merges_s.nextval /*CUSTOMER_MERGE_ID*/
, sysdate /*CREATION_DATE*/
, HZ_UTILITY_V2PUB.CREATED_BY /*CREATED_BY*/
, sysdate /*LAST_UPDATE_DATE*/
, HZ_UTILITY_V2PUB.LAST_UPDATED_BY /*LAST_UPDATED_BY*/
, HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN /*LAST_UPDATE_LOGIN*/
, 'SAVED' /*PROCESS_FLAG*/
, l_account_merge_request_obj.merge_to_account.account_id /*CUSTOMER_ID*/
, l_to_customer_name /*CUSTOMER_NAME*/
, l_account_merge_request_obj.merge_to_account.account_number /*CUSTOMER_NUMBER*/
, l_account_merge_request_obj.site_merge_details(i).merge_to_account_site_obj.account_site_orig_sys_ref /*CUSTOMER_REF*/
, l_account_merge_request_obj.site_merge_details(i).merge_to_account_site_obj.account_site_id /*CUSTOMER_ADDRESS_ID*/
, l_to_address /*CUSTOMER_ADDRESS*/
, -99 /*CUSTOMER_SITE_ID*/
, 'NONE' /*CUSTOMER_SITE_CODE*/
, NULL /*CUSTOMER_LOCATION*/
, 'N' /*CUSTOMER_PRIMARY_FLAG*/
, NVL(l_account_merge_request_obj.delete_after_merge_flag, 'N') /*DELETE_DUPLICATE_FLAG*/
, l_from_address /* DUPLICATE_ADDRESS*/
, l_account_merge_request_obj.site_merge_details(i).merge_from_account_site_obj.account_site_id /*DUPLICATE_ADDRESS_ID*/
, l_account_merge_request_obj.merge_from_account.account_id /* DUPLICATE_ID*/
, NULL /* DUPLICATE_LOCATION*/
, l_from_customer_name/* DUPLICATE_NAME*/
, l_account_merge_request_obj.merge_from_account.account_number/* DUPLICATE_NUMBER*/
, 'N' /*DUPLICATE_PRIMARY_FLAG*/
, l_account_merge_request_obj.site_merge_details(i).merge_to_account_site_obj.account_site_orig_sys_ref /* DUPLICATE_REF*/
, 'NONE' /*DUPLICATE_SITE_CODE*/
, -99 /*DUPLICATE_SITE_ID*/
, 1 /* SET_NUMBER*/
, l_header_id /* CUSTOMER_MERGE_HEADER_ID*/
, l_to_cust_first_name /* CUSTOMER_FIRST_NAME*/
, l_to_cust_last_name /* CUSTOMER_LAST_NAME*/
, l_to_customer_type /* CUSTOMER_TYPE*/
, l_from_cust_first_name /* DUPLICATE_FIRST_NAME*/
, l_from_cust_last_name /* DUPLICATE_LAST_NAME */
, l_from_customer_type /* DUPLICATE_TYPE */
, 'N' /* CUSTOMER_CREATESAME*/
, l_account_merge_request_obj.site_merge_details(i).merge_from_account_site_obj.SITE_NUMBER /*DUPLICATE_SITE_NUMBER*/
, l_account_merge_request_obj.site_merge_details(i).merge_to_account_site_obj.SITE_NUMBER /*CUSTOMER_SITE_NUMBER */
, l_account_merge_request_obj.site_merge_details(i).org_id /*ORG_ID*/
);
END LOOP; -- Insert site_merge_details
END IF; -- Insert site_merge_details
SELECT 1 INTO l_site_cnt
FROM hz_cust_acct_sites_all
WHERE cust_account_id = l_account_merge_request_obj.merge_from_account.account_id
AND rownum = 1;
INSERT INTO RA_CUSTOMER_MERGES(
CUSTOMER_MERGE_ID
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, PROCESS_FLAG
, CUSTOMER_ID
, CUSTOMER_NAME
, CUSTOMER_NUMBER
, CUSTOMER_ADDRESS_ID
, CUSTOMER_SITE_ID
, DELETE_DUPLICATE_FLAG
, DUPLICATE_ADDRESS_ID
, DUPLICATE_ID
, DUPLICATE_NAME
, DUPLICATE_NUMBER
, DUPLICATE_SITE_ID
, SET_NUMBER
, CUSTOMER_MERGE_HEADER_ID
, CUSTOMER_FIRST_NAME
, CUSTOMER_LAST_NAME
, CUSTOMER_TYPE
, DUPLICATE_FIRST_NAME
, DUPLICATE_LAST_NAME
, DUPLICATE_TYPE
, CUSTOMER_CREATESAME
, ORG_ID)
VALUES( ra_customer_merges_s.nextval /*CUSTOMER_MERGE_ID*/
, sysdate /*CREATION_DATE*/
, HZ_UTILITY_V2PUB.CREATED_BY /*CREATED_BY*/
, sysdate /*LAST_UPDATE_DATE*/
, HZ_UTILITY_V2PUB.LAST_UPDATED_BY /*LAST_UPDATED_BY*/
, HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN /*LAST_UPDATE_LOGIN*/
, 'SAVED' /*PROCESS_FLAG*/
, l_account_merge_request_obj.merge_to_account.account_id /*CUSTOMER_ID*/
, l_to_customer_name /*CUSTOMER_NAME*/
, l_account_merge_request_obj.merge_to_account.account_number /*CUSTOMER_NUMBER*/
, -1 /*CUSTOMER_ADDRESS_ID*/
, -1 /*CUSTOMER_SITE_ID*/
, NVL(l_account_merge_request_obj.delete_after_merge_flag, 'N') /*DELETE_DUPLICATE_FLAG*/
, -1 /*DUPLICATE_ADDRESS_ID*/
, l_account_merge_request_obj.merge_from_account.account_id /*DUPLICATE_ID*/
, l_from_customer_name/* DUPLICATE_NAME*/
, l_account_merge_request_obj.merge_from_account.account_number /*DUPLICATE_NUMBER*/
, -1 /*DUPLICATE_SITE_ID*/
, 1 /*SET_NUMBER*/
, l_header_id /*CUSTOMER_MERGE_HEADER_ID*/
, l_to_cust_first_name /*CUSTOMER_FIRST_NAME*/
, l_to_cust_last_name /* CUSTOMER_LAST_NAME*/
, l_to_customer_type /* CUSTOMER_TYPE*/
, l_from_cust_first_name /* DUPLICATE_FIRST_NAME*/
, l_from_cust_last_name /* DUPLICATE_LAST_NAME */
, l_from_customer_type /*DUPLICATE_TYPE */
, 'N' /*CUSTOMER_CREATESAME*/
, l_account_merge_request_obj.org_id);
INSERT INTO RA_CUSTOMER_MERGES(
CUSTOMER_MERGE_ID
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, PROCESS_FLAG
, CUSTOMER_ID
, CUSTOMER_NAME
, CUSTOMER_NUMBER
, CUSTOMER_REF
, CUSTOMER_ADDRESS_ID
, CUSTOMER_ADDRESS
, CUSTOMER_SITE_ID
, CUSTOMER_SITE_CODE
, CUSTOMER_LOCATION
, CUSTOMER_PRIMARY_FLAG
, DELETE_DUPLICATE_FLAG
, DUPLICATE_ADDRESS
, DUPLICATE_ADDRESS_ID
, DUPLICATE_ID
, DUPLICATE_LOCATION
, DUPLICATE_NAME
, DUPLICATE_NUMBER
, DUPLICATE_PRIMARY_FLAG
, DUPLICATE_REF
, DUPLICATE_SITE_CODE
, DUPLICATE_SITE_ID
, SET_NUMBER
, CUSTOMER_MERGE_HEADER_ID
, CUSTOMER_FIRST_NAME
, CUSTOMER_LAST_NAME
, CUSTOMER_TYPE
, DUPLICATE_FIRST_NAME
, DUPLICATE_LAST_NAME
, DUPLICATE_TYPE
, CUSTOMER_CREATESAME
, DUPLICATE_SITE_NUMBER
, CUSTOMER_SITE_NUMBER
, ORG_ID)
select ra_customer_merges_s.nextval /*CUSTOMER_MERGE_ID*/
, sysdate /*CREATION_DATE*/
, HZ_UTILITY_V2PUB.CREATED_BY /*CREATED_BY*/
, sysdate /*LAST_UPDATE_DATE*/
, HZ_UTILITY_V2PUB.LAST_UPDATED_BY /*LAST_UPDATED_BY*/
, HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN /*LAST_UPDATE_LOGIN*/
, 'SAVED' /*PROCESS_FLAG*/
, l_account_merge_request_obj.merge_to_account.account_id /*CUSTOMER_ID*/
, l_to_customer_name /*CUSTOMER_NAME*/
, l_account_merge_request_obj.merge_to_account.account_number /*CUSTOMER_NUMBER*/
, null /*CUSTOMER_REF*/
, -1 /*CUSTOMER_ADDRESS_ID*/
, substrb(a.concatenated_address, 1, 240) /*CUSTOMER_ADDRESS*/
, -1 /*CUSTOMER_SITE_ID*/
, a.site_use_code /*CUSTOMER_SITE_CODE*/
, a.location /*CUSTOMER_LOCATION*/
, a.primary_flag /*CUSTOMER_PRIMARY_FLAG*/
, NVL(l_account_merge_request_obj.delete_after_merge_flag, 'N') /*DELETE_DUPLICATE_FLAG*/
, substrb(a.concatenated_address, 1, 240) /*DUPLICATE_ADDRESS*/
, a.address_id /*DUPLICATE_ADDRESS_ID*/
, l_account_merge_request_obj.merge_from_account.account_id /*DUPLICATE_ID*/
, a.location /*DUPLICATE_LOCATION*/
, l_from_customer_name/* DUPLICATE_NAME*/
, l_account_merge_request_obj.merge_from_account.account_number /*DUPLICATE_NUMBER*/
, a.primary_flag /*DUPLICATE_PRIMARY_FLAG*/
, a.orig_system_reference /*DUPLICATE_REF*/
, a.site_use_code /*DUPLICATE_SITE_CODE*/
, a.SITE_USE_ID /*DUPLICATE_SITE_ID*/
, 1 /*SET_NUMBER*/
, l_header_id /*CUSTOMER_MERGE_HEADER_ID*/
, l_to_cust_first_name /*CUSTOMER_FIRST_NAME*/
, l_to_cust_last_name /* CUSTOMER_LAST_NAME*/
, l_to_customer_type /* CUSTOMER_TYPE*/
, l_from_cust_first_name /* DUPLICATE_FIRST_NAME*/
, l_from_cust_last_name /* DUPLICATE_LAST_NAME */
, l_from_customer_type /*DUPLICATE_TYPE */
, 'Y' /*CUSTOMER_CREATESAME*/
, a.site_number /*DUPLICATE_SITE_NUMBER*/
, NULL /*CUSTOMER_SITE_NUMBER*/
, a.org_id /*ORG_ID*/
from hz_merge_addresses_v a
where customer_id = l_account_merge_request_obj.merge_from_account.account_id
and a.org_id = decode(l_account_merge_request_obj.org_id,-1,a.org_id,l_account_merge_request_obj.org_id)
and ((a.site_use_id <> -99 AND a.site_use_id not in (select duplicate_site_id
from ra_customer_merges m
where m.customer_merge_header_id = l_header_id))
OR
(a.site_use_id = -99 AND a.address_id not in (select duplicate_address_id
from ra_customer_merges m
where m.customer_merge_header_id = l_header_id
and m.duplicate_site_id = -99))
);
UPDATE ra_customer_merges m
SET customer_location = duplicate_location||'-C'
WHERE m.customer_merge_header_id = l_header_id
AND m.customer_createsame = 'Y'
AND m.duplicate_site_id <> -99
AND exists (SELECT 'Y'
FROM hz_cust_site_uses_all use,hz_cust_acct_sites_all site
WHERE site.cust_account_id = m.customer_id
AND site.org_id = m.org_id --SSUptake
AND site.cust_acct_site_id = use.cust_acct_site_id
AND site.org_id = use.org_id --SSUptake
AND use.site_use_code = m.duplicate_site_code
AND use.location = m.duplicate_location
AND ROWNUM = 1);
SELECT 1 INTO l_site_cnt
FROM ra_customer_merges
WHERE customer_merge_header_id = l_header_id
AND rownum = 1;
select hr.name into l_ou_name
from hr_operating_units hr
where hr.organization_id = l_account_merge_request_obj.org_id;
UPDATE ra_customer_merge_headers
SET request_id = x_account_merge_request_id
WHERE customer_merge_header_id = l_header_id;
UPDATE ra_customer_merges
SET request_id = x_account_merge_request_id
WHERE customer_merge_header_id = l_header_id;