[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT CRMINTEG_HZ_MERGE_OBJ(
decode(mh.customer_type, 'CUSTOMER_ORG', 'ORGANIZATION', 'CUSTOMER_PERSON', 'PERSON'),-- party_type
mh.duplicate_id, -- from cust_acct_id
ca.party_id, -- from party_id
null, -- from common_obj_id
mh.customer_id, -- to cust_acct_id
ca2.party_id, -- to party_id
null, -- to common_obj_id
'N', -- keep account flag
CAST(MULTISET(
SELECT CRMINTEG_HZ_MRGDTIL_OBJ(
'ADDRESS',
'N',
rm.duplicate_address_id, -- from cust_acct_site_id
cas.party_site_id, -- from party_site_id
null, -- from common_obj_id
rm.customer_address_id, -- to cust_acct_site_id
cas2.party_site_id, -- to party_site_id
null ) -- to common_obj_id
from RA_CUSTOMER_MERGES rm, HZ_CUST_ACCT_SITES_ALL cas, HZ_CUST_ACCT_SITES_ALL cas2
where rm.customer_merge_header_id = p_customer_merge_header_id
and rm.duplicate_address_id = cas.cust_acct_site_id(+)
and rm.customer_address_id = cas2.cust_acct_site_id(+)
group by rm.duplicate_address_id, rm.customer_address_id,
cas.party_site_id, cas2.party_site_id
) AS CRMINTEG_HZ_MRGDTIL_OBJ_TBL ),
CAST(MULTISET(
SELECT CRMINTEG_HZ_MRGDTIL_OBJ(
'CONTACT',
'N',
carm.cust_account_role_id, -- from cust_acct_role_id
ocm.org_contact_id, -- from org_contact_id
null, -- from common_obj_id
car.cust_account_role_id, -- to cust_acct_role_id
oc.org_contact_id, -- to org_contact_id
null ) -- to common_obj_id
from RA_CUSTOMER_MERGES rm, HZ_CUST_ACCOUNT_ROLES_M carm, HZ_CUST_ACCOUNT_ROLES car, HZ_RELATIONSHIPS relm, HZ_RELATIONSHIPS rel, HZ_ORG_CONTACTS ocm, HZ_ORG_CONTACTS oc
where rm.customer_merge_header_id = p_customer_merge_header_id
and rm.customer_merge_header_id = carm.customer_merge_header_id(+)
and carm.party_id = relm.party_id(+)
and relm.relationship_id = ocm.party_relationship_id(+)
and carm.cust_account_role_id = car.cust_account_role_id(+)
and car.party_id = rel.party_id(+)
and rel.relationship_id = oc.party_relationship_id(+)
group by carm.cust_account_role_id, car.cust_account_role_id,
ocm.org_contact_id, oc.org_contact_id
) AS CRMINTEG_HZ_MRGDTIL_OBJ_TBL ))
from RA_CUSTOMER_MERGE_HEADERS mh, HZ_CUST_ACCOUNTS ca, HZ_CUST_ACCOUNTS ca2
where mh.customer_merge_header_id = l_customer_merge_header_id
and mh.process_flag = 'Y'
and mh.duplicate_id = ca.cust_account_id(+)
and mh.customer_id = ca2.cust_account_id;
SELECT 'Y'
FROM HZ_CUST_ACCT_SITES_ALL
WHERE cust_account_id = l_ca_id
AND cust_acct_site_id <> l_cas_id
AND party_site_id = l_ps_id
AND rownum = 1;
SELECT decode(status, 'A', 'Y', 'N')
FROM HZ_CUST_ACCOUNTS
WHERE cust_account_id = l_cust_acct_id;
CURSOR get_deleted_acct_pid(l_cmhdr_id NUMBER, l_cust_acct_id NUMBER) IS
SELECT party_id
FROM HZ_CUST_ACCOUNTS_M
WHERE cust_account_id = l_cust_acct_id
AND customer_merge_header_id = l_cmhdr_id
AND rownum = 1;
CURSOR get_deleted_cs_psid(l_cmhdr_id NUMBER, l_cust_acct_site_id NUMBER) IS
SELECT party_site_id
FROM HZ_CUST_ACCT_SITES_ALL_M
WHERE cust_acct_site_id = l_cust_acct_site_id
AND customer_merge_header_id = l_cmhdr_id
AND rownum = 1;
OPEN get_deleted_acct_pid(p_customer_merge_header_id, x_account_merge_obj.from_cust_acct_id);
FETCh get_deleted_acct_pid INTO x_account_merge_obj.from_party_id;
CLOSE get_deleted_acct_pid;
OPEN get_deleted_cs_psid(p_customer_merge_header_id, x_account_merge_obj.merge_address_objs(k).from_acct_object_id);
FETCH get_deleted_cs_psid INTO x_account_merge_obj.merge_address_objs(k).from_party_object_id;
CLOSE get_deleted_cs_psid;
SELECT distinct cr.cust_account_id, r2.object_id
from HZ_MERGE_BATCH mb, HZ_MERGE_PARTIES mp, HZ_MERGE_PARTY_HISTORY mph,
HZ_MERGE_DICTIONARY md, HZ_CUST_ACCOUNT_ROLES cr, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc,
HZ_RELATIONSHIPS r2, HZ_ORG_CONTACTS oc2
where mb.batch_id = l_batch_id
and mb.batch_id = mp.batch_id
and mp.merge_reason_code = 'DUPLICATE_RELN_PARTY'
and mp.batch_party_id = mph.batch_party_id
and mph.merge_dict_id = md.merge_dict_id
and cr.cust_account_role_id = mph.from_entity_id
and r.party_id = mp.from_party_id
and r.relationship_id = oc.party_relationship_id
and r.subject_Type = 'PERSON' and r.object_type = 'ORGANIZATION'
and r2.party_id = mp.to_party_id
and r2.relationship_id = oc2.party_relationship_id
and r2.subject_Type = 'PERSON' and r2.object_type = 'ORGANIZATION'
and md.entity_name = 'HZ_CUST_ACCOUNT_ROLES';
SELECT party_type
FROM HZ_PARTIES
WHERE party_id = p_merge_to_party_id;
SELECT p.party_type, from_parent_entity_id, from_entity_id, to_parent_entity_id
from HZ_MERGE_BATCH mb, HZ_MERGE_PARTIES mp, HZ_MERGE_PARTY_HISTORY mph, HZ_MERGE_DICTIONARY md, HZ_PARTIES p
where mb.batch_id = l_batch_id
and mb.batch_id = mp.batch_id
and mp.batch_party_id = mph.batch_party_id
and mph.merge_dict_id = md.merge_dict_id
and p.party_id = mph.to_parent_entity_id
and md.entity_name = 'HZ_CUST_ACCOUNTS';
SELECT CRMINTEG_HZ_MERGE_OBJ(
l_party_type, -- party_type
l_fcaid, -- from cust_acct_id
l_fpid, -- from party_id
null, -- from common_obj_id
l_fcaid, -- to cust_acct_id
l_tpid, -- to party_id
null, -- to common_obj_id
'N',
CAST(MULTISET(
SELECT CRMINTEG_HZ_MRGDTIL_OBJ(
'ADDRESS',
'N',
cs.cust_account_id, -- from cust_acct_id
mph.from_parent_entity_id, -- from party_site_id
null, -- from common_obj_id
cs.cust_account_id, -- to cust_acct_id
mph.to_parent_entity_id, -- to party_site_id
null ) -- to common_obj_id
from HZ_MERGE_BATCH mb, HZ_MERGE_PARTIES mp, HZ_MERGE_PARTY_HISTORY mph,
HZ_MERGE_DICTIONARY md, HZ_CUST_ACCT_SITES_ALL cs
where mb.batch_id = l_batch_id
and mb.batch_id = mp.batch_id
and mp.batch_party_id = mph.batch_party_id
and mph.merge_dict_id = md.merge_dict_id
and cs.cust_acct_site_id = mph.from_entity_id
and cs.cust_account_id = l_fcaid
and md.entity_name = 'HZ_CUST_ACCT_SITES_ALL'
) AS CRMINTEG_HZ_MRGDTIL_OBJ_TBL ),
CAST(MULTISET(
SELECT CRMINTEG_HZ_MRGDTIL_OBJ(
'CONTACT',
'N',
cr.cust_account_id, -- from cust_acct_site_id
oc.org_contact_id, -- from party_site_id
null, -- from common_obj_id
cr.cust_account_id, -- to cust_acct_site_id
oc2.org_contact_id, -- to party_site_id
null ) -- to common_obj_id
from HZ_MERGE_BATCH mb, HZ_MERGE_PARTIES mp, HZ_MERGE_PARTY_HISTORY mph,
HZ_MERGE_DICTIONARY md, HZ_CUST_ACCOUNT_ROLES cr, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc,
HZ_RELATIONSHIPS r2, HZ_ORG_CONTACTS oc2
where mb.batch_id = l_batch_id
and mb.batch_id = mp.batch_id
and mp.batch_party_id = mph.batch_party_id
and mph.merge_dict_id = md.merge_dict_id
and cr.cust_account_role_id = mph.from_entity_id
and cr.cust_account_id = l_fcaid
and r.party_id = mph.from_parent_entity_id
and r.relationship_id = oc.party_relationship_id
and r.subject_Type = 'PERSON' and r.object_type = 'ORGANIZATION'
and r2.party_id = mph.to_parent_entity_id
and r2.relationship_id = oc2.party_relationship_id
and r2.subject_Type = 'PERSON' and r2.object_type = 'ORGANIZATION'
and md.entity_name = 'HZ_CUST_ACCOUNT_ROLES'
) AS CRMINTEG_HZ_MRGDTIL_OBJ_TBL ))
from dual;
SELECT CRMINTEG_HZ_MERGE_OBJ(
l_party_type, -- party_type
null, -- from cust_acct_id
null, -- from party_id
null, -- from common_obj_id
null, -- to cust_acct_id
null, -- to party_id
null, -- to common_obj_id
'N',
CRMINTEG_HZ_MRGDTIL_OBJ_TBL(),
CAST(MULTISET(
SELECT CRMINTEG_HZ_MRGDTIL_OBJ(
'CONTACT',
'N',
cr.cust_account_id, -- from cust_acct_site_id
oc.org_contact_id, -- from org_contact_id
null, -- from common_obj_id
cr.cust_account_id, -- to cust_acct_site_id
oc2.org_contact_id, -- to org_contact_id
r2.object_id) -- to party_id
from HZ_MERGE_BATCH mb, HZ_MERGE_PARTIES mp, HZ_MERGE_PARTY_HISTORY mph,
HZ_MERGE_DICTIONARY md, HZ_CUST_ACCOUNT_ROLES cr, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc,
HZ_RELATIONSHIPS r2, HZ_ORG_CONTACTS oc2
where mb.batch_id = l_batch_id
and mb.batch_id = mp.batch_id
and mp.merge_reason_code = 'DUPLICATE_RELN_PARTY'
and mp.batch_party_id = mph.batch_party_id
and mph.merge_dict_id = md.merge_dict_id
and cr.cust_account_role_id = mph.from_entity_id
and r.party_id = mp.from_party_id
and r.relationship_id = oc.party_relationship_id
and r.subject_Type = 'PERSON' and r.object_type = 'ORGANIZATION'
and r2.party_id = mp.to_party_id
and r2.relationship_id = oc2.party_relationship_id
and r2.subject_Type = 'PERSON' and r2.object_type = 'ORGANIZATION'
and md.entity_name = 'HZ_CUST_ACCOUNT_ROLES'
) AS CRMINTEG_HZ_MRGDTIL_OBJ_TBL ))
from dual;
PROCEDURE sync_acct_update(
p_validate_bo_flag IN VARCHAR2 := fnd_api.g_true,
p_org_cust_obj IN HZ_ORG_CUST_BO,
p_created_by_module IN VARCHAR2,
p_obj_source IN VARCHAR2 := null,
x_return_status OUT NOCOPY VARCHAR2,
x_messages OUT NOCOPY HZ_MESSAGE_OBJ_TBL,
x_return_obj OUT NOCOPY HZ_ORG_CUST_BO
) IS
l_organization_id NUMBER;
SELECT contact_point_id
FROM HZ_CONTACT_POINTS
WHERE owner_table_name = 'HZ_PARTIES'
AND contact_point_type = 'WEB'
AND owner_table_id = l_org_id
AND primary_flag = 'Y';
SELECT cp.contact_point_id
FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r, HZ_CONTACT_POINTS cp
WHERE cp.owner_table_name = 'HZ_PARTIES'
AND cp.contact_point_type = 'EMAIL'
AND cp.owner_table_id = r.party_id
AND cp.primary_flag = 'Y'
AND oc.org_contact_id = l_oc_id
AND oc.party_relationship_id = r.relationship_id
AND rownum = 1;
SELECT cust_acct_site_id
FROM HZ_CUST_ACCT_SITES_ALL
WHERE cust_account_id = l_ca_id
AND party_site_id = l_ps_id
AND org_id = l_org_id
AND rownum = 1;
SELECT site_use_id
FROM HZ_CUST_SITE_USES_ALL
WHERE cust_acct_site_id = l_cas_id
AND site_use_code = l_su_code
AND status = 'A'
AND org_id = l_org_id
AND rownum = 1;
SAVEPOINT do_sync_acct_update;
hz_utility_v2pub.debug(p_message=>'sync_acct_update(+)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
HZ_ORGANIZATION_BO_PUB.update_organization_bo(
p_organization_obj => l_org_obj,
p_created_by_module => p_created_by_module,
p_obj_source => p_obj_source,
p_return_obj_flag => fnd_api.g_true,
x_return_status => x_return_status,
x_messages => x_messages,
x_return_obj => l_return_org_obj,
x_organization_id => l_organization_id,
x_organization_os => l_organization_os,
x_organization_osr => l_organization_osr
);
HZ_ORG_CUST_BO_PUB.update_org_cust_bo(
p_org_cust_obj => l_org_cust_bo,
p_created_by_module => p_created_by_module,
p_obj_source => p_obj_source,
p_return_obj_flag => fnd_api.g_true,
x_return_status => x_return_status,
x_messages => x_messages,
x_return_obj => l_return_obj,
x_organization_id => l_organization_id
);
hz_utility_v2pub.debug(p_message=>'sync_acct_update(-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO do_sync_acct_update;
hz_utility_v2pub.debug(p_message=>'sync_acct_update(-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO do_sync_acct_update;
hz_utility_v2pub.debug(p_message=>'sync_acct_update(-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO do_sync_acct_update;
hz_utility_v2pub.debug(p_message=>'sync_acct_update(-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
END sync_acct_update;
SELECT contact_point_id
FROM HZ_CONTACT_POINTS
WHERE owner_table_name = 'HZ_PARTIES'
AND contact_point_type = 'WEB'
AND owner_table_id = l_org_id
AND primary_flag = 'Y';
SELECT cp.contact_point_id
FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r, HZ_CONTACT_POINTS cp
WHERE cp.owner_table_name = 'HZ_PARTIES'
AND cp.contact_point_type = 'EMAIL'
AND cp.owner_table_id = r.party_id
AND cp.primary_flag = 'Y'
AND oc.org_contact_id = l_oc_id
AND oc.party_relationship_id = r.relationship_id
AND rownum = 1;
SELECT cust_acct_site_id
FROM HZ_CUST_ACCT_SITES_ALL
WHERE cust_account_id = l_ca_id
AND party_site_id = l_ps_id
AND org_id = l_org_id
AND rownum = 1;
SELECT site_use_id
FROM HZ_CUST_SITE_USES_ALL
WHERE cust_acct_site_id = l_cas_id
AND site_use_code = l_su_code
AND status = 'A'
AND org_id = l_org_id
AND rownum = 1;
SELECT car.cust_account_role_id
FROM HZ_CUST_ACCOUNT_ROLES car, HZ_RELATIONSHIPS r
WHERE car.cust_account_id = l_ca_id
AND car.party_id = r.party_id
AND r.subject_id = l_per_id
AND r.subject_type = 'PERSON'
AND r.object_type = 'ORGANIZATION'
AND car.cust_acct_site_id IS NULL
AND rownum = 1;
UPDATE HZ_PARTIES
set ORG_CUST_BO_VERSION = ( SELECT BO_VERSION_NUMBER
FROM HZ_BUS_OBJ_DEFINITIONS
WHERE BUSINESS_OBJECT_CODE = 'ORG_CUST'
AND ENTITY_NAME = 'HZ_PARTIES'
AND CHILD_BO_CODE IS NULL )
WHERE party_id = l_organization_id;