Supplier Address Book

//

SELECT pv.vendor_name,
hps.party_site_id,
hps.party_site_name,
DECODE (pay.site_use_type, NULL, ‘N’, ‘Y’) AS pay_flag,
DECODE (pur.site_use_type, NULL, ‘N’, ‘Y’) AS pur_flag,
DECODE (rfq.site_use_type, NULL, ‘N’, ‘Y’) AS rfq_flag,
hps.last_update_date,
hps.end_date_active,
hps.start_date_active,
p_notes.notes,
hcp1.phone_area_code,
hcp1.phone_number,
hcp1.contact_point_id AS phone_contact_id,
hcp1.object_version_number AS phone_object_version_number,
hcp2.email_address AS hcp_email,
hcp2.contact_point_id AS email_contact_id,
hcp2.object_version_number AS email_object_version_number,
hcp3.object_version_number AS fax_object_version_number,
hcp3.phone_area_code AS fax_area_code,
hcp3.phone_number AS fax_number,
hcp3.contact_point_id AS fax_contact_id,
hzl.address1,
hzl.address2,
hzl.address3,
hzl.address4,
hzl.city,
hzl.state,
hzl.province,
hzl.county,
hzl.country,
hzl.postal_plus4_code,
hzl.postal_code,
hzl.location_id,
hps.party_id AS party_id
FROM hz_party_sites hps,
hz_party_site_uses pay,
hz_party_site_uses pur,
hz_party_site_uses rfq,
pos_address_notes p_notes,
hz_contact_points hcp1,
hz_contact_points hcp2,
hz_contact_points hcp3,
hz_locations hzl,
po_vendors pv,
po_vendor_sites_all vs
WHERE hps.location_id = hzl.location_id
AND NVL (hps.end_date_active, SYSDATE) >= SYSDATE
AND pay.party_site_id(+) = hps.party_site_id
AND pur.party_site_id(+) = hps.party_site_id
AND rfq.party_site_id(+) = hps.party_site_id
AND p_notes.party_site_id(+) = hps.party_site_id
AND pay.status(+) = ‘A’
AND pur.status(+) = ‘A’
AND rfq.status(+) = ‘A’
AND NVL (pay.end_date(+), SYSDATE) >= SYSDATE
AND NVL (pur.end_date(+), SYSDATE) >= SYSDATE
AND NVL (rfq.end_date(+), SYSDATE) >= SYSDATE
AND NVL (pay.begin_date(+), SYSDATE) <= SYSDATE
AND NVL (pur.begin_date(+), SYSDATE) <= SYSDATE
AND NVL (rfq.begin_date(+), SYSDATE) <= SYSDATE
AND pay.site_use_type(+) = ‘PAY’
AND pur.site_use_type(+) = ‘PURCHASING’
AND rfq.site_use_type(+) = ‘RFQ’
AND hcp1.owner_table_id(+) = hps.party_site_id
AND hcp1.CONTACT_POINT_TYPE(+) = ‘PHONE’
AND hcp1.phone_line_type(+) = ‘GEN’
AND hcp1.status(+) = ‘A’
AND hcp1.owner_table_name(+) = ‘HZ_PARTY_SITES’
AND hcp1.primary_flag(+) = ‘Y’
AND hcp2.owner_table_id(+) = hps.party_site_id
AND hcp2.CONTACT_POINT_TYPE(+) = ‘EMAIL’
AND hcp2.status(+) = ‘A’
AND hcp2.owner_table_name(+) = ‘HZ_PARTY_SITES’
AND hcp2.primary_flag(+) = ‘Y’
AND hcp3.owner_table_id(+) = hps.party_site_id
AND hcp3.CONTACT_POINT_TYPE(+) = ‘PHONE’
AND hcp3.phone_line_type(+) = ‘FAX’
AND hcp3.status(+) = ‘A’
AND hcp3.owner_table_name(+) = ‘HZ_PARTY_SITES’
AND hps.party_site_id = vs.party_site_id
AND vs.vendor_id = pv.vendor_id
AND pv.VENDOR_TYPE_LOOKUP_CODE = ‘INTERCOMPANY’
AND vs.INACTIVE_DATE IS NULL
AND DECODE (pur.site_use_type, NULL, ‘N’, ‘Y’)=’Y’
AND vs.org_id = 135;