Wednesday, August 22, 2012

How to join Projects and Customers, sites using select stmt

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;