[Home] [Help]
149: hz_contact_points cont_point,
150: hz_cust_account_roles car
151: where
152: car.party_id = cont_point.owner_table_id
153: and cont_point.owner_table_name = 'HZ_PARTIES'
154: and cont_point.contact_point_type not in ('EDI','EMAIL','WEB')
155: and car.cust_acct_site_id = address_id
156: and ( car.cust_account_role_id = c_contact_id
157: OR not exists ( select 'x'
172: hz_contact_points cont_point,
173: hz_cust_account_roles car
174: where
175: car.party_id = cont_point.owner_table_id
176: and cont_point.owner_table_name = 'HZ_PARTIES'
177: and cont_point.contact_point_type not in ('EDI','EMAIL','WEB')
178: and car.cust_account_id = customer_id
179: and not exists (
180: select 'x'
230: l_contact_name,
231: l_contact_id
232: from
233: hz_cust_account_roles acct_role,
234: hz_parties party,
235: hz_relationships rel,
236: hz_role_responsibility role_res
237: where
238: acct_role.cust_account_role_id = role_res.cust_account_role_id
238: acct_role.cust_account_role_id = role_res.cust_account_role_id
239: and acct_role.party_id = rel.party_id
240: and acct_role.role_type = 'CONTACT'
241: and rel.subject_id = party.party_id
242: and rel.subject_table_name = 'HZ_PARTIES'
243: and rel.object_table_name = 'HZ_PARTIES'
244: and rel.directional_flag = 'F'
245: and role_res.responsibility_type = 'BILL_TO'
246: and acct_role.cust_acct_site_id = p_address_id
239: and acct_role.party_id = rel.party_id
240: and acct_role.role_type = 'CONTACT'
241: and rel.subject_id = party.party_id
242: and rel.subject_table_name = 'HZ_PARTIES'
243: and rel.object_table_name = 'HZ_PARTIES'
244: and rel.directional_flag = 'F'
245: and role_res.responsibility_type = 'BILL_TO'
246: and acct_role.cust_acct_site_id = p_address_id
247: and rownum = 1