[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT contact_point_type
FROM hz_contact_points
WHERE contact_point_id = p_table_id;
SELECT name INTO l_operating_unit
FROM hr_operating_units
WHERE organization_id = p_org_id;
SELECT HZ_ACCT_MERGE_OBJ(
p_customer_merge_header_id,
mh.request_id,
mh.created_by,
mh.creation_date,
mh.last_update_login,
mh.last_update_date,
mh.last_updated_by,
HZ_PARTY_ORIG_SYS_REF_OBJ(
tp.party_id,
tp.party_number,
tp.party_name,
tp.party_type,
HZ_ORIG_SYS_REF_OBJ_TBL()),
HZ_PARTY_ORIG_SYS_REF_OBJ(
fp.party_id,
fp.party_number,
fp.party_name,
fp.party_type,
HZ_ORIG_SYS_REF_OBJ_TBL()),
HZ_ACCT_ORIG_SYS_REF_OBJ(
ta.cust_account_id,
ta.account_name,
ta.account_number,
HZ_ORIG_SYS_REF_OBJ_TBL()),
CAST(MULTISET (
SELECT HZ_ACCT_ORIG_SYS_REF_OBJ(
fa.cust_account_id,
fa.account_name,
fa.account_number,
HZ_ORIG_SYS_REF_OBJ_TBL())
FROM hz_cust_accounts_m fa
WHERE fa.cust_account_id = mh.duplicate_id
) AS HZ_ACCT_ORIG_SYS_REF_OBJ_TBL)
)
FROM ra_customer_merge_headers mh, hz_cust_accounts ta, hz_parties tp, hz_cust_accounts_m fa, hz_parties fp
WHERE mh.customer_merge_header_id = p_customer_merge_header_id
AND ta.cust_account_id = mh.customer_id
AND tp.party_id = ta.party_id
AND fa.cust_account_id = mh.duplicate_id
AND fp.party_id = fa.party_id
AND rownum = 1;
SELECT HZ_ACCOUNT_MERGE_V2_OBJ(
p_customer_merge_header_id,
mh.delete_duplicate_flag,
mh.request_id,
mh.created_by,
mh.creation_date,
mh.last_update_login,
mh.last_update_date,
mh.last_updated_by,
HZ_PARTY_ORIG_SYS_REF_OBJ(
tp.party_id,
tp.party_number,
tp.party_name,
tp.party_type,
HZ_ORIG_SYS_REF_OBJ_TBL()),
HZ_PARTY_ORIG_SYS_REF_OBJ(
fp.party_id,
fp.party_number,
fp.party_name,
fp.party_type,
HZ_ORIG_SYS_REF_OBJ_TBL()),
HZ_ACCT_ORIG_SYS_REF_OBJ(
ta.cust_account_id,
ta.account_name,
ta.account_number,
HZ_ORIG_SYS_REF_OBJ_TBL()),
CAST(MULTISET (
SELECT HZ_ACCT_ORIG_SYS_REF_OBJ(
fa.cust_account_id,
fa.account_name,
fa.account_number,
HZ_ORIG_SYS_REF_OBJ_TBL())
FROM hz_cust_accounts_m fa
WHERE fa.cust_account_id = mh.duplicate_id
) AS HZ_ACCT_ORIG_SYS_REF_OBJ_TBL),
HZ_ACCT_MERGE_DETAIL_OBJ_TBL(), --ACCT_SITE_OBJS
HZ_ACCT_MERGE_DETAIL_OBJ_TBL(), --ACCT_SITE_USES_OBJS
HZ_ACCT_MERGE_DETAIL_OBJ_TBL(), --ACCT_ROLE_OBJS
HZ_ACCT_MERGE_DETAIL_OBJ_TBL(), --CUSTOMER_PROFILE_OBJS
HZ_ACCT_MERGE_DETAIL_OBJ_TBL(), --CUST_PROFILE_AMT_OBJS
HZ_ACCT_MERGE_DETAIL_OBJ_TBL() --ACCT_REL_OBJS
)
FROM ra_customer_merge_headers mh, hz_cust_accounts ta, hz_parties tp, hz_cust_accounts_m fa, hz_parties fp
WHERE mh.customer_merge_header_id = p_customer_merge_header_id
AND ta.cust_account_id = mh.customer_id
AND tp.party_id = ta.party_id
AND fa.cust_account_id = mh.duplicate_id
AND fp.party_id = fa.party_id
AND rownum = 1;
SELECT HZ_ACCOUNT_MERGE_DETAIL_OBJ(
get_object_type('HZ_CUST_ACCT_SITES', cm.duplicate_address_id),
decode(cm.customer_createsame, 'N', 'Merge', 'Y', 'Transfer'),
cm.org_id,
get_operating_unit(cm.org_id),
cm.duplicate_address_id,
CAST(MULTISET(
SELECT HZ_ORIG_SYS_REF_OBJ(
NULL,
ORIG_SYSTEM_REF_ID,
ORIG_SYSTEM,
ORIG_SYSTEM_REFERENCE,
HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
OWNER_TABLE_ID,
STATUS,
REASON_CODE,
OLD_ORIG_SYSTEM_REFERENCE,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
PROGRAM_UPDATE_DATE,
CREATED_BY_MODULE,
HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
CREATION_DATE,
LAST_UPDATE_DATE,
HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20
)
FROM HZ_ORIG_SYS_REFERENCES
WHERE OWNER_TABLE_ID = cm.duplicate_address_id
AND OWNER_TABLE_NAME = 'HZ_CUST_ACCT_SITES_ALL'
)AS HZ_ORIG_SYS_REF_OBJ_TBL),
cm.customer_address_id,
CAST(MULTISET(
SELECT HZ_ORIG_SYS_REF_OBJ(
NULL,
ORIG_SYSTEM_REF_ID,
ORIG_SYSTEM,
ORIG_SYSTEM_REFERENCE,
HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
OWNER_TABLE_ID,
STATUS,
REASON_CODE,
OLD_ORIG_SYSTEM_REFERENCE,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
PROGRAM_UPDATE_DATE,
CREATED_BY_MODULE,
HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
CREATION_DATE,
LAST_UPDATE_DATE,
HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20
)
FROM HZ_ORIG_SYS_REFERENCES
WHERE OWNER_TABLE_ID = cm.customer_address_id
AND OWNER_TABLE_NAME = 'HZ_CUST_ACCT_SITES_ALL'
)AS HZ_ORIG_SYS_REF_OBJ_TBL),
cm.duplicate_id,
CAST(MULTISET(
SELECT HZ_ORIG_SYS_REF_OBJ(
NULL,
ORIG_SYSTEM_REF_ID,
ORIG_SYSTEM,
ORIG_SYSTEM_REFERENCE,
HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
OWNER_TABLE_ID,
STATUS,
REASON_CODE,
OLD_ORIG_SYSTEM_REFERENCE,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
PROGRAM_UPDATE_DATE,
CREATED_BY_MODULE,
HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
CREATION_DATE,
LAST_UPDATE_DATE,
HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20
)
FROM HZ_ORIG_SYS_REFERENCES
WHERE OWNER_TABLE_ID = cm.duplicate_id
AND OWNER_TABLE_NAME = 'HZ_CUST_ACCOUNTS'
)AS HZ_ORIG_SYS_REF_OBJ_TBL),
cm.customer_id,
CAST(MULTISET(
SELECT HZ_ORIG_SYS_REF_OBJ(
NULL,
ORIG_SYSTEM_REF_ID,
ORIG_SYSTEM,
ORIG_SYSTEM_REFERENCE,
HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
OWNER_TABLE_ID,
STATUS,
REASON_CODE,
OLD_ORIG_SYSTEM_REFERENCE,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
PROGRAM_UPDATE_DATE,
CREATED_BY_MODULE,
HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
CREATION_DATE,
LAST_UPDATE_DATE,
HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20
)
FROM HZ_ORIG_SYS_REFERENCES
WHERE OWNER_TABLE_ID = cm.customer_id
AND OWNER_TABLE_NAME = 'HZ_CUST_ACCOUNTS'
)AS HZ_ORIG_SYS_REF_OBJ_TBL)
)
FROM (SELECT distinct duplicate_address_id, customer_address_id,
duplicate_id, customer_id, org_id, customer_createsame
FROM ra_customer_merges cm
WHERE customer_merge_header_id = p_customer_merge_header_id
AND duplicate_id <> customer_id) cm;
SELECT HZ_ACCOUNT_MERGE_DETAIL_OBJ(
get_object_type('HZ_CUST_SITE_USES', cm.duplicate_site_id),
decode(cm.customer_createsame, 'N', 'Merge', 'Y', 'Transfer'),
cm.org_id,
get_operating_unit(cm.org_id),
cm.duplicate_site_id,
CAST(MULTISET(
SELECT HZ_ORIG_SYS_REF_OBJ(
NULL,
ORIG_SYSTEM_REF_ID,
ORIG_SYSTEM,
ORIG_SYSTEM_REFERENCE,
HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
OWNER_TABLE_ID,
STATUS,
REASON_CODE,
OLD_ORIG_SYSTEM_REFERENCE,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
PROGRAM_UPDATE_DATE,
CREATED_BY_MODULE,
HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
CREATION_DATE,
LAST_UPDATE_DATE,
HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20
)
FROM HZ_ORIG_SYS_REFERENCES
WHERE OWNER_TABLE_ID = cm.duplicate_site_id
AND OWNER_TABLE_NAME = 'HZ_CUST_SITE_USES_ALL'
)AS HZ_ORIG_SYS_REF_OBJ_TBL),
cm.customer_site_id,
CAST(MULTISET(
SELECT HZ_ORIG_SYS_REF_OBJ(
NULL,
ORIG_SYSTEM_REF_ID,
ORIG_SYSTEM,
ORIG_SYSTEM_REFERENCE,
HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
OWNER_TABLE_ID,
STATUS,
REASON_CODE,
OLD_ORIG_SYSTEM_REFERENCE,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
PROGRAM_UPDATE_DATE,
CREATED_BY_MODULE,
HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
CREATION_DATE,
LAST_UPDATE_DATE,
HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20
)
FROM HZ_ORIG_SYS_REFERENCES
WHERE OWNER_TABLE_ID = cm.customer_site_id
AND OWNER_TABLE_NAME = 'HZ_CUST_SITE_USES_ALL'
)AS HZ_ORIG_SYS_REF_OBJ_TBL),
cm.duplicate_address_id,
CAST(MULTISET(
SELECT HZ_ORIG_SYS_REF_OBJ(
NULL,
ORIG_SYSTEM_REF_ID,
ORIG_SYSTEM,
ORIG_SYSTEM_REFERENCE,
HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
OWNER_TABLE_ID,
STATUS,
REASON_CODE,
OLD_ORIG_SYSTEM_REFERENCE,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
PROGRAM_UPDATE_DATE,
CREATED_BY_MODULE,
HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
CREATION_DATE,
LAST_UPDATE_DATE,
HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20
)
FROM HZ_ORIG_SYS_REFERENCES
WHERE OWNER_TABLE_ID = cm.duplicate_address_id
AND OWNER_TABLE_NAME = 'HZ_CUST_ACCT_SITES_ALL'
)AS HZ_ORIG_SYS_REF_OBJ_TBL),
cm.customer_address_id,
CAST(MULTISET(
SELECT HZ_ORIG_SYS_REF_OBJ(
NULL,
ORIG_SYSTEM_REF_ID,
ORIG_SYSTEM,
ORIG_SYSTEM_REFERENCE,
HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
OWNER_TABLE_ID,
STATUS,
REASON_CODE,
OLD_ORIG_SYSTEM_REFERENCE,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
PROGRAM_UPDATE_DATE,
CREATED_BY_MODULE,
HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
CREATION_DATE,
LAST_UPDATE_DATE,
HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20
)
FROM HZ_ORIG_SYS_REFERENCES
WHERE OWNER_TABLE_ID = cm.customer_address_id
AND OWNER_TABLE_NAME = 'HZ_CUST_ACCT_SITES_ALL'
)AS HZ_ORIG_SYS_REF_OBJ_TBL)
)
FROM ra_customer_merges cm
WHERE customer_merge_header_id = p_customer_merge_header_id;
SELECT HZ_ACCOUNT_MERGE_DETAIL_OBJ(
get_object_type('HZ_CUSTOMER_PROFILES', from_profile_id),
operation,
NULL,
NULL,
from_profile_id,
HZ_ORIG_SYS_REF_OBJ_TBL(),
to_profile_id,
HZ_ORIG_SYS_REF_OBJ_TBL(),
from_parent_id,
HZ_ORIG_SYS_REF_OBJ_TBL(),
to_parent_id,
HZ_ORIG_SYS_REF_OBJ_TBL()
)
FROM (SELECT 'Merge' operation, cpf.cust_account_profile_id from_profile_id,
cpt.cust_account_profile_id to_profile_id,
cmh.duplicate_id from_parent_id, cmh.customer_id to_parent_id
FROM ra_customer_merge_headers cmh, hz_customer_profiles_m cpf, hz_customer_profiles cpt
WHERE cmh.customer_merge_header_id = p_customer_merge_header_id
AND cmh.duplicate_id <> cmh.customer_id
AND cpf.cust_account_id = cmh.duplicate_id
AND cpf.customer_merge_header_id = p_customer_merge_header_id
AND cpt.cust_account_id = cmh.customer_id
AND cpf.site_use_id IS NULL
AND cpt.site_use_id IS NULL
UNION
SELECT decode(cm.customer_createsame, 'N', 'Merge', 'Y', 'Transfer') operation,
cpf.cust_account_profile_id from_profile_id,
cpt.cust_account_profile_id to_profile_id,
cm.duplicate_site_id from_parent_id,
cm.customer_site_id to_parent_id
FROM ra_customer_merges cm, hz_customer_profiles_m cpf, hz_customer_profiles cpt
WHERE cm.customer_merge_header_id = p_customer_merge_header_id
AND cpf.customer_merge_header_id = p_customer_merge_header_id
AND cpf.cust_account_id = cm.duplicate_id
AND cpf.site_use_id = cm.duplicate_site_id
AND cpt.cust_account_id = cm.customer_id
AND cpt.site_use_id = cm.customer_site_id);
SELECT HZ_ACCOUNT_MERGE_DETAIL_OBJ(
get_object_type('HZ_CUST_PROFILE_AMTS', from_profile_amt_id),
operation,
NULL,
NULL,
from_profile_amt_id,
HZ_ORIG_SYS_REF_OBJ_TBL(),
to_profile_amt_id,
HZ_ORIG_SYS_REF_OBJ_TBL(),
from_parent_id,
HZ_ORIG_SYS_REF_OBJ_TBL(),
to_parent_id,
HZ_ORIG_SYS_REF_OBJ_TBL()
)
FROM (SELECT 'Merge' operation, cpaf.cust_acct_profile_amt_id from_profile_amt_id,
cpat.cust_acct_profile_amt_id to_profile_amt_id,
cmh.duplicate_id from_parent_id, cmh.customer_id to_parent_id
FROM ra_customer_merge_headers cmh, hz_cust_profile_amts_m cpaf, hz_cust_profile_amts cpat
WHERE cmh.customer_merge_header_id = p_customer_merge_header_id
AND cmh.duplicate_id <> cmh.customer_id
AND cpaf.customer_merge_header_id = p_customer_merge_header_id
AND cpaf.cust_account_id = cmh.duplicate_id
AND cpat.cust_account_id = cmh.customer_id
AND cpaf.currency_code = cpat.currency_code
AND cpaf.site_use_id IS NULL
AND cpat.site_use_id IS NULL
UNION
SELECT decode(cm.customer_createsame, 'N', 'Merge', 'Y', 'Transfer') operation,
cpaf.cust_acct_profile_amt_id from_profile_amt_id,
cpat.cust_acct_profile_amt_id to_profile_amt_id,
cm.duplicate_site_id from_parent_id,
cm.customer_site_id to_parent_id
FROM ra_customer_merges cm, hz_cust_profile_amts_m cpaf, hz_cust_profile_amts cpat
WHERE cm.customer_merge_header_id = p_customer_merge_header_id
AND cpaf.customer_merge_header_id = p_customer_merge_header_id
AND cpaf.cust_account_id = cm.duplicate_id
AND cpaf.site_use_id = cm.duplicate_site_id
AND cpat.cust_account_id = cm.customer_id
AND cpat.site_use_id = cm.customer_site_id
AND cpaf.currency_code = cpat.currency_code);
SELECT HZ_ACCOUNT_MERGE_DETAIL_OBJ(
get_object_type('HZ_CUST_ACCOUNT_ROLES',from_role_id),
operation,
NULL,
NULL,
from_role_id,
HZ_ORIG_SYS_REF_OBJ_TBL(),
to_role_id,
HZ_ORIG_SYS_REF_OBJ_TBL(),
from_parent_id,
HZ_ORIG_SYS_REF_OBJ_TBL(),
to_parent_id,
HZ_ORIG_SYS_REF_OBJ_TBL()
)
FROM (SELECT DISTINCT carf.cust_account_role_id from_role_id,
carf.cust_account_role_id to_role_id,
Nvl(carf.cust_acct_site_id,cm.duplicate_id) from_parent_id,
Decode(carf.cust_acct_site_id, NULL,cm.customer_id,cm.customer_address_id) to_parent_id, 'Transfer' operation
FROM (SELECT DISTINCT duplicate_id, duplicate_address_id, customer_address_id, customer_id
FROM ra_customer_merges cm
WHERE cm.customer_merge_header_id = p_customer_merge_header_id
AND cm.duplicate_id <> cm.customer_id) cm, hz_cust_account_roles_m carf
WHERE carf.customer_merge_header_id = p_customer_merge_header_id
AND ((carf.cust_account_id = cm.duplicate_id AND carf.cust_acct_site_id = cm.duplicate_address_id)
OR (carf.cust_account_id = cm.duplicate_id AND carf.cust_acct_site_id IS NULL))
AND NOT EXISTS (SELECT 'Y'
FROM hz_cust_account_roles
WHERE cust_account_role_id = carf.cust_account_role_id
AND cust_account_id = cm.duplicate_id));
SELECT HZ_ACCOUNT_MERGE_DETAIL_OBJ(
get_object_type('HZ_CUST_ACCT_RELATE_ALL',crelf.cust_acct_relate_id),
decode(crelt.created_by_module,'HZ_TCA_CUSTOMER_MERGE','Transfer','Merge'),
crelt.org_id,
get_operating_unit(crelt.org_id),
crelf.cust_acct_relate_id,
HZ_ORIG_SYS_REF_OBJ_TBL(),
crelt.cust_acct_relate_id,
HZ_ORIG_SYS_REF_OBJ_TBL(),
duplicate_id,
CAST(MULTISET(
SELECT HZ_ORIG_SYS_REF_OBJ(
NULL,
ORIG_SYSTEM_REF_ID,
ORIG_SYSTEM,
ORIG_SYSTEM_REFERENCE,
HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
OWNER_TABLE_ID,
STATUS,
REASON_CODE,
OLD_ORIG_SYSTEM_REFERENCE,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
PROGRAM_UPDATE_DATE,
CREATED_BY_MODULE,
HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
CREATION_DATE,
LAST_UPDATE_DATE,
HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20
)
FROM HZ_ORIG_SYS_REFERENCES
WHERE OWNER_TABLE_ID = duplicate_id
AND OWNER_TABLE_NAME = 'HZ_CUST_ACCOUNTS'
)AS HZ_ORIG_SYS_REF_OBJ_TBL),
customer_id,
CAST(MULTISET(
SELECT HZ_ORIG_SYS_REF_OBJ(
NULL,
ORIG_SYSTEM_REF_ID,
ORIG_SYSTEM,
ORIG_SYSTEM_REFERENCE,
HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
OWNER_TABLE_ID,
STATUS,
REASON_CODE,
OLD_ORIG_SYSTEM_REFERENCE,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
PROGRAM_UPDATE_DATE,
CREATED_BY_MODULE,
HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
CREATION_DATE,
LAST_UPDATE_DATE,
HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20
)
FROM HZ_ORIG_SYS_REFERENCES
WHERE OWNER_TABLE_ID = customer_id
AND OWNER_TABLE_NAME = 'HZ_CUST_ACCOUNTS'
)AS HZ_ORIG_SYS_REF_OBJ_TBL)
)
FROM hz_cust_acct_relate_all_m crelf, hz_cust_acct_relate_all crelt
WHERE ((crelf.cust_account_id = duplicate_id
AND crelt.cust_account_id = customer_id
AND crelt.related_cust_account_id = crelf.related_cust_account_id)
OR
(crelf.related_cust_account_id = duplicate_id
AND crelt.related_cust_account_id = customer_id
AND crelt.cust_account_id = crelf.cust_account_id))
AND crelt.org_id IN (SELECT org_id FROM ra_customer_merges
WHERE customer_merge_header_id = p_customer_merge_header_id)
AND crelf.org_id = crelt.org_id;
SELECT customer_merge_header_id INTO l_header_id
FROM ra_customer_merge_headers
WHERE customer_merge_header_id = p_customer_merge_header_id;
SELECT site_use_id INTO l_site_use_id
FROM hz_customer_profiles_m
WHERE cust_account_profile_id = x_account_merge_v2_obj.customer_profile_objs(I).from_object_id;
SELECT site_use_id INTO l_site_use_id
FROM hz_cust_profile_amts_m
WHERE cust_acct_profile_amt_id = x_account_merge_v2_obj.cust_profile_amt_objs(I).from_object_id;
SELECT cust_acct_site_id INTO l_site_use_id
FROM hz_cust_account_roles_m
WHERE cust_account_role_id = x_account_merge_v2_obj.acct_role_objs(I).from_object_id;
SELECT HZ_PARTY_MERGE_OBJ(
mb.batch_id,
mb.batch_name,
mp.merge_type,
db.automerge_flag,
mb.created_by,
mb.creation_date,
mb.last_update_login,
mb.last_update_date,
mb.last_updated_by,
HZ_PARTY_ORIG_SYS_REF_OBJ(
tp.party_id,
tp.party_number,
tp.party_name,
tp.party_type,
HZ_ORIG_SYS_REF_OBJ_TBL()),
CAST(MULTISET(
SELECT HZ_PARTY_ORIG_SYS_REF_OBJ(
fp.party_id,
fp.party_number,
fp.party_name,
fp.party_type,
HZ_ORIG_SYS_REF_OBJ_TBL())
FROM hz_parties fp, hz_merge_parties mp1
WHERE mp1.batch_id = p_batch_id
AND mp1.to_party_id = l_merge_to_party_id
AND fp.party_id = mp1.from_party_id
AND mp1.merge_type = mp.merge_type
) AS HZ_PARTY_ORIG_SYS_REF_OBJ_TBL),
HZ_PARTY_MERGE_DETAIL_OBJ_TBL() --5093366
)
FROM hz_merge_batch mb,
(SELECT DISTINCT merge_type from hz_merge_parties where batch_id = p_batch_id and to_party_id = l_merge_to_party_id) mp,
hz_dup_batch db,
hz_dup_sets dset,
hz_parties tp
WHERE mb.batch_id = p_batch_id
AND tp.party_id = l_merge_to_party_id
AND mb.batch_id = dset.dup_set_id (+)
AND db.dup_batch_id (+)= dset.dup_batch_id
ORDER BY mp.merge_type;
SELECT HZ_PARTY_MERGE_DETAIL_OBJ(
get_object_type(md.entity_name, mph.from_entity_id),
mph.operation_type,
mph.from_entity_id,
CAST(MULTISET(
SELECT HZ_ORIG_SYS_REF_OBJ(
NULL,
ORIG_SYSTEM_REF_ID,
ORIG_SYSTEM,
ORIG_SYSTEM_REFERENCE,
HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
OWNER_TABLE_ID,
STATUS,
REASON_CODE,
OLD_ORIG_SYSTEM_REFERENCE,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
PROGRAM_UPDATE_DATE,
CREATED_BY_MODULE,
HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
CREATION_DATE,
LAST_UPDATE_DATE,
HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20
)
FROM HZ_ORIG_SYS_REFERENCES
WHERE OWNER_TABLE_ID = mph.from_entity_id
AND OWNER_TABLE_NAME = md.entity_name
)AS HZ_ORIG_SYS_REF_OBJ_TBL),
mph.to_entity_id,
CAST(MULTISET(
SELECT HZ_ORIG_SYS_REF_OBJ(
NULL,
ORIG_SYSTEM_REF_ID,
ORIG_SYSTEM,
ORIG_SYSTEM_REFERENCE,
HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
OWNER_TABLE_ID,
STATUS,
REASON_CODE,
OLD_ORIG_SYSTEM_REFERENCE,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
PROGRAM_UPDATE_DATE,
CREATED_BY_MODULE,
HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
CREATION_DATE,
LAST_UPDATE_DATE,
HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20
)
FROM HZ_ORIG_SYS_REFERENCES
WHERE OWNER_TABLE_ID = decode(mph.operation_type,'Copy',mph.from_entity_id,mph.to_entity_id)
AND OWNER_TABLE_NAME = md.entity_name
)AS HZ_ORIG_SYS_REF_OBJ_TBL),
mph.from_parent_entity_id,
CAST(MULTISET(
SELECT HZ_ORIG_SYS_REF_OBJ(
NULL,
ORIG_SYSTEM_REF_ID,
ORIG_SYSTEM,
ORIG_SYSTEM_REFERENCE,
HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
OWNER_TABLE_ID,
STATUS,
REASON_CODE,
OLD_ORIG_SYSTEM_REFERENCE,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
PROGRAM_UPDATE_DATE,
CREATED_BY_MODULE,
HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
CREATION_DATE,
LAST_UPDATE_DATE,
HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20
)
FROM HZ_ORIG_SYS_REFERENCES
WHERE OWNER_TABLE_ID = mph.from_parent_entity_id
AND OWNER_TABLE_NAME = md.parent_entity_name
)AS HZ_ORIG_SYS_REF_OBJ_TBL),
mph.to_parent_entity_id,
CAST(MULTISET(
SELECT HZ_ORIG_SYS_REF_OBJ(
NULL,
ORIG_SYSTEM_REF_ID,
ORIG_SYSTEM,
ORIG_SYSTEM_REFERENCE,
HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
OWNER_TABLE_ID,
STATUS,
REASON_CODE,
OLD_ORIG_SYSTEM_REFERENCE,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
PROGRAM_UPDATE_DATE,
CREATED_BY_MODULE,
HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
CREATION_DATE,
LAST_UPDATE_DATE,
HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20
)
FROM HZ_ORIG_SYS_REFERENCES
WHERE OWNER_TABLE_ID = mph.to_parent_entity_id
AND OWNER_TABLE_NAME = md.parent_entity_name
)AS HZ_ORIG_SYS_REF_OBJ_TBL)
)
FROM hz_merge_parties mp2,
hz_merge_party_history mph,
hz_merge_dictionary md
WHERE mp2.batch_id = p_batch_id
AND mp2.to_party_id = l_merge_to_party_id
AND mph.batch_party_id = mp2.batch_party_id
AND md.merge_dict_id = mph.merge_dict_id
AND md.dict_application_id = 222
and md.entity_name like 'HZ%';
SELECT batch_id, batch_status INTO l_batch_id, l_merge_status
FROM hz_merge_batch
WHERE batch_id = p_batch_id;
SELECT to_party_id INTO l_merge_to_party_id
FROM hz_merge_parties
WHERE batch_id = p_batch_id
AND to_party_id = p_merge_to_party_id
AND rownum = 1;
SELECT to_party_id INTO l_merge_to_party_id
FROM hz_merge_parties
WHERE batch_id = p_batch_id
AND NVL(merge_reason_code , 'DEDUPE') <> 'DUPLICATE_RELN_PARTY'
AND rownum = 1;