Oracle R12 SQL Query for Supplier Addresses on the Address Book Suppliers Entry/Update Screen

SELECT DISTINCT pv.segment1 vendor_number,
                  pv.vendor_name,
                  hps.party_site_name address_name,
                  hou.name operating_unit,
                  hcp2.email_address,
                  DECODE (hps.status,  'A', 'Active',  'I', 'Inactive') status
    FROM apps.po_vendors pv,
         apps.po_vendor_sites_all pvsa,
         apps.hr_operating_units hou,
         apps.hz_contact_points hcp2,
         apps.hz_party_sites hps
   WHERE     1 = 1
         AND pv.vendor_id = pvsa.vendor_id
         AND pvsa.org_id = hou.organization_id
         AND hcp2.owner_table_id(+) = pvsa.party_site_id
         AND hcp2.contact_point_type(+) = 'EMAIL'
         AND hcp2.status(+) = 'A'
         AND hcp2.owner_table_name(+) = 'HZ_PARTY_SITES'
         AND pvsa.party_site_id = hps.party_site_id
         AND (pv.end_date_active IS NULL OR pv.end_date_active > SYSDATE)
ORDER BY pv.segment1

Oracle PA (Projects) Expenditure Details Interfaced from OTL SQL Query

SELECT p.segment1 project_number,
       p.name project_name,
       pt.project_type,
       pt.project_type_class_code,
       p.project_id,
       ei.task_id,
       t.task_number,
       t.task_name,
       ei.expenditure_item_date,
       ei.expenditure_type,
       ei.quantity,
       DECODE (ei.unit_of_measure,
               NULL, pa_utils4.get_unit_of_measure (ei.expenditure_type),
               ei.unit_of_measure)
          unit_of_measure,
       ei.burden_cost,
       (SELECT expenditure_category
          FROM pa_expenditure_types
         WHERE expenditure_type = ei.expenditure_type)
          expenditure_category,
       (SELECT revenue_category_code
          FROM pa_expenditure_types
         WHERE expenditure_type = ei.expenditure_type)
          revenue_category_code,
       x.incurred_by_person_id,
       (SELECT p.full_name
          FROM per_all_people_f p
         WHERE     p.person_id = x.incurred_by_person_id
               AND ei.expenditure_item_date BETWEEN p.effective_start_date
                                                AND p.effective_end_date)
          employee_name,
          (SELECT u.user_name
          FROM fnd_user u
         WHERE     u.employee_id = x.incurred_by_person_id) Username
  FROM pa_projects_all p,
       pa_tasks t,
       pa_expenditure_items_all ei,
       pa_expenditures_all x,
       pa_project_types_all pt,
       hr_all_organization_units_tl haot
 WHERE     t.project_id = p.project_id
       AND ei.project_id = p.project_id
       AND p.project_type = pt.project_type
       AND p.org_id = pt.org_id
       AND ei.task_id = t.task_id
       AND ei.expenditure_id = x.expenditure_id
       AND NVL (ei.override_to_organization_id,
                x.incurred_by_organization_id) = haot.organization_id
       AND haot.language = USERENV ('LANG')     
      AND x.incurred_by_person_id = :X_PERSON_ID -- Person Id or Resource Id of the employee for which time is entered
       AND ei.expenditure_item_date BETWEEN :Week_Start_Date AND :Week_End_Date

Oracle R12 Suppliers Contact Information SQL Query

SELECT DISTINCT asu.segment1 vendor_number,
                  asu.vendor_name,
                  asu.start_date_active vendor_start_date_active,
                  asu.end_date_active vendor_end_date_active,
                  assa.vendor_site_code,
                  assa.inactive_date vendor_site_inactive_date,
                  hou.name Operating_Unit_Name,
                  assa.address_line1,
                  assa.city,
                  assa.state,
                  assa.zip,
                  hpc.party_name Contact_Name,
                  hpr.primary_phone_country_code Contact_phone_country_code,
                  hpr.primary_phone_area_code Contact_phone_area,
                  hpr.primary_phone_number Contact_phone_number,
                  hpr.email_address Contact_email_address,
                  hpcp.status contact_status
    FROM ap_suppliers asu,
         ap_supplier_sites_all assa,
         hz_relationships hr,
         ap_supplier_contacts asco,
         hz_org_contacts hoc,
         hz_parties hpc,
         hz_parties hpr,
         hz_contact_points hpcp,
         hr_operating_units hou
   WHERE     1 = 1
         AND asu.vendor_id = assa.vendor_id
         AND assa.org_id = hou.organization_id
         AND assa.party_site_id = asco.org_party_site_id(+)
         AND asco.relationship_id = hoc.party_relationship_id(+)
         AND hoc.party_relationship_id = hr.relationship_id(+)
         AND asu.party_id = hr.subject_id(+)
         AND hr.relationship_code(+) = 'CONTACT'
         AND hr.object_table_name(+) = 'HZ_PARTIES'
         AND hr.object_id = hpc.party_id(+)
         AND hr.party_id = hpr.party_id(+)
         AND hpr.party_type(+) = 'PARTY_RELATIONSHIP'
         AND hpr.party_id = hpcp.owner_table_id(+)
         AND hpcp.owner_table_name(+) = 'HZ_PARTIES'
ORDER BY asu.segment1