ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 35CCA930D2C3858DE06362FB0905660CY select /*+ push_pred(ps) */ aps.vendor_name, aps.segment1 vendor_num, hpc.party_name contact_name, hpc.creation_date contact_created, hpc.last_update_date contact_last_updated, ps.party_rel_created relationship_creation_date, ps.party_rel_last_updated relationship_last_updated, asco.creation_date address_contact_creation_date, asco.last_update_date address_contact_last_updated, ps.contact_party_id, asco.per_party_id address_contact_party_id from ap_suppliers aps, ( select hps.party_site_name address_name, pos_profile_change_request_pkg.format_address(hl.address1, hl.address2, hl.address3, hl.address4, hl.city, hl.postal_code, hl.state, hl.province, hl.county, hl.country) address_details, hzr.creation_date party_rel_created, hzr.last_update_date party_rel_last_updated, hzr.status relationship_status, hps.end_date_active supplier_party_site_end_date, hzr.relationship_id relationship_id, hzr.party_id rel_party_id, hzr.subject_id contact_party_id, hps.party_id supplier_party_id, hps.party_site_id supplier_party_site_id, hoc.org_contact_id from hz_party_sites hps, hz_locations hl, hz_relationships hzr, hz_org_contacts hoc where hps.location_id = hl.location_id and hzr.relationship_type = 'CONTACT' and hzr.relationship_code = 'CONTACT_OF' and hzr.subject_type = 'PERSON' and hzr.subject_table_name = 'HZ_PARTIES' and hzr.object_type = 'ORGANIZATION' and hzr.object_table_name = 'HZ_PARTIES' and hzr.status = 'A' and hps.party_id = hzr.object_id and hzr.relationship_id = hoc.party_relationship_id (+) and not exists (select 1 from ap_supplier_contacts ascs where (ascs.inactive_date is null or ascs.inactive_date > sysdate) and hzr.relationship_id = ascs.relationship_id and hzr.party_id = ascs.rel_party_id and hps.party_site_id = ascs.org_party_site_id and hzr.subject_id = ascs.per_party_id ) ) ps, hz_parties hpc, ap_supplier_contacts asco where aps.party_id = ps.supplier_party_id and ps.contact_party_id = hpc.party_id and -- ps.supplier_party_site_id = asco.org_party_site_id and ps.relationship_id = asco.relationship_id and ps.rel_party_id = asco.rel_party_id and -- ps.contact_party_id != asco.per_party_id order by aps.vendor_name, aps.segment1, hpc.party_name, ps.address_name US AP Missing Supplier Contact Address Associations