133: END IF;
134:
135: SELECT PARTY.PARTY_NAME INTO x_contact_name
136: FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
137: HZ_PARTIES PARTY,
138: HZ_CUST_ACCOUNTS ACCT,
139: HZ_RELATIONSHIPS REL,
140: HZ_PARTIES REL_PARTY
141: WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_contact_id
136: FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
137: HZ_PARTIES PARTY,
138: HZ_CUST_ACCOUNTS ACCT,
139: HZ_RELATIONSHIPS REL,
140: HZ_PARTIES REL_PARTY
141: WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_contact_id
142: AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID
143: AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
144: AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
140: HZ_PARTIES REL_PARTY
141: WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_contact_id
142: AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID
143: AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
144: AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
145: AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
146: AND REL.SUBJECT_ID = PARTY.PARTY_ID
147: AND REL.PARTY_ID = REL_PARTY.PARTY_ID
148: AND REL.OBJECT_ID = ACCT.PARTY_ID
141: WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_contact_id
142: AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID
143: AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
144: AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
145: AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
146: AND REL.SUBJECT_ID = PARTY.PARTY_ID
147: AND REL.PARTY_ID = REL_PARTY.PARTY_ID
148: AND REL.OBJECT_ID = ACCT.PARTY_ID
149: AND ACCT.CUST_ACCOUNT_ID = ACCT_ROLE.CUST_ACCOUNT_ID;
246: hz_cust_acct_sites_all acct_site,
247: hz_party_sites party_site,
248: hz_locations loc,
249: hz_cust_accounts cust_acct,
250: hz_parties party,
251: hz_Person_profiles hp
252: WHERE
253: site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
254: AND acct_site.cust_account_id = cust_acct.cust_account_id
307: decode(party.party_type,'ORGANIZATION',party.organization_name_phonetic
308: ,hp.person_name_phonetic) alternate_name -- bug4231894
309:
310: FROM
311: hz_parties party,
312: hz_cust_accounts cust_acct,
313: hz_locations loc,
314: hz_party_sites party_site,
315: hz_Person_profiles hp