SELECT proj.segment1 project#, proj.NAME proj_name,
hc_bill.account_number bill_to_customer#,
hp_bill.party_name bill_to_customer,
hc_ship.account_number ship_to_customer#,
hp_ship.party_name ship_to_customer,
hps_bill.party_site_number bill_to_site#,
hl_bill.address1 bill_to_addr1, hl_bill.city bill_to_city,
hl_bill.county bill_to_county, hl_bill.state bill_to_state,
hl_bill.postal_code bill_to_postal_code,
hps_ship.party_site_number ship_to_site#,
hl_ship.address1 ship_to_addr1, hl_ship.city ship_to_city,
hl_ship.county ship_to_county, hl_ship.state ship_to_state,
hl_ship.postal_code ship_to_postal_code
FROM pa_projects_all proj,
pa_project_customers pc,
hz_cust_accounts hc_bill,
hz_parties hp_bill,
hz_cust_accounts hc_ship,
hz_parties hp_ship,
hz_cust_acct_sites_all hcs_bill,
hz_party_sites hps_bill,
hz_locations hl_bill,
hz_cust_acct_sites_all hcs_ship,
hz_party_sites hps_ship,
hz_locations hl_ship
WHERE TRUNC (SYSDATE) <= NVL (completion_date, TRUNC (SYSDATE))
AND proj.project_status_code <> 'CLOSED'
AND proj.project_id = pc.project_id
AND pc.bill_to_customer_id = hc_bill.cust_account_id
AND hc_bill.party_id = hp_bill.party_id
AND pc.ship_to_customer_id = hc_ship.cust_account_id
AND hc_ship.party_id = hp_ship.party_id
AND pc.bill_to_address_id = hcs_bill.cust_acct_site_id
AND hcs_bill.party_site_id = hps_bill.party_site_id
AND hps_bill.location_id = hl_bill.location_id
AND pc.ship_to_address_id = hcs_ship.cust_acct_site_id
AND hcs_ship.party_site_id = hps_ship.party_site_id
AND hps_ship.location_id = hl_ship.location_id;
