PO Supplier Extract Query

SELECT   pv.vendor_id,
                   pvs.org_id,
                   pvs.vendor_site_id,
                   pv.VENDOR_NAME,
                   pv.segment1 SUPPLIER_NUMBER,
                   pvs.VENDOR_SITE_CODE SITE_NAME,
                   hou.name OPEARTING_UNIT,
                   pvs.ADDRESS_LINE1,
                   pvs.ADDRESS_LINE2,
                   pvs.ADDRESS_LINE3,
                   pvs.CITY,
                   pvs.STATE,
                   pvs.ZIP,
                   pvs.PROVINCE,
                   pvs.COUNTRY,
                   pv.START_DATE_ACTIVE,
                   pv.end_DATE_ACTIVE,
                   pvs.purchasing_site_flag MANAGE_SITE_PUR,
                   pvs.rfq_only_site_flag MANAGE_SITE_RFQ,
                   pvs.pay_site_flag MANAGE_SITE_PAY,
                   qrslt.pay_flag ADDRESS_PURPOSE_PAY_FLAG,
                   qrslt.pur_flag ADDRESS_PURPOSE_PUR_FLAG,
                   qrslt.rfq_flag ADDRESS_PURPOSE_RFQ_FLAG,
                   fl.MEANING HEADER_PAYMENT_METHOD,
                   pv.INVOICE_CURRENCY_CODE HEADER_INVOICE_CURRENCY_CODE,
                   pv.PAYMENT_CURRENCY_CODE HEADER_PAYMENT_CURRENCY_CODE,
                   (SELECT   name
                      FROM   ap_terms
                     WHERE   term_id = pv.terms_id)
                      HEADER_TERM_NAME,
                   fl.MEANING SITE_PAYMENT_METHOD,
                   pvs.INVOICE_CURRENCY_CODE SITE_INVOICE_CURRENCY_CODE,
                   pvs.PAYMENT_CURRENCY_CODE SITE_PAYMENT_CURRENCY_CODE,
                   (SELECT   name
                      FROM   ap_terms
                     WHERE   term_id = pvs.terms_id)
                      SITE_TERM_NAME
            --INACTIVE_DATE
            FROM   ap_suppliers pv,
                   ap_supplier_sites_all pvs,
                   hr_operating_units hou,
                   fnd_lookups fl,
                   fnd_lookups fl1,
                   (SELECT   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,
                             hps.status AS status
                      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
                     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 hps.party_site_id=1488182
                             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')
                   QRSLT
           WHERE       pv.vendor_id = pvs.vendor_id
                   AND QRSLT.party_site_id = pvs.party_site_id
                   AND pvs.org_id = hou.organization_id
                   AND fl.LOOKUP_TYPE = 'OKL_AP_PAYMENT_METHOD'
                   AND pv.PAYMENT_METHOD_LOOKUP_CODE = fl.LOOKUP_CODE(+)
                   AND fl1.LOOKUP_TYPE = 'OKL_AP_PAYMENT_METHOD'
                   AND pvs.PAYMENT_METHOD_LOOKUP_CODE = fl1.LOOKUP_CODE(+)
                   AND NVL (pv.end_DATE_ACTIVE, SYSDATE) >= SYSDATE--and pv.end_DATE_ACTIVE <= nvl(sysdate, pv.end_DATE_ACTIVE)
--  AND pv.SEGMENT1 = '156336'
--and pvs.org_id =111