Oracle Receivable Bill to and Ship to Address Queries

SELECT DISTINCT

  hca1.account_number customer_number,

  hp1.party_name,

 

  (SELECT DISTINCT loc.address1

   FROM hz_parties hp ,

    hz_cust_accounts hca ,

    HZ_LOCATIONS LOC ,

    HZ_PARTY_SITES PARTY_SITE ,

    HZ_CUST_SITE_USES_ALL ACCT_SU ,

    HZ_CUST_ACCT_SITES_ALL ACCT_SITE

  WHERE hp.party_id             = hca.party_id

     AND loc.location_id           = party_site.location_id

     AND party_site.party_site_id  = acct_site.party_site_id

     AND acct_su.cust_acct_site_id = acct_site.cust_acct_site_id

     AND acct_su.site_use_code     = 'BILL_TO'

     AND ACCT_SU.STATUS            = 'A'

     AND ACCT_SITE.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID

     AND acct_site.status          = 'A'

     AND acct_site.org_id          = acct_site1.org_id

     AND hca.account_number        = hca1.account_number

     AND hps1.party_site_id        = party_site.party_site_id

  ) bill_to_address1,

 

  (SELECT DISTINCT loc.address2

  FROM hz_parties hp ,

    hz_cust_accounts hca ,

    HZ_LOCATIONS LOC ,

    HZ_PARTY_SITES PARTY_SITE ,

    HZ_CUST_SITE_USES_ALL ACCT_SU ,

    HZ_CUST_ACCT_SITES_ALL ACCT_SITE

  WHERE hp.party_id             = hca.party_id

     AND loc.location_id           = party_site.location_id

     AND party_site.party_site_id  = acct_site.party_site_id

     AND acct_su.cust_acct_site_id = acct_site.cust_acct_site_id

     AND acct_su.site_use_code     = 'BILL_TO'

     AND ACCT_SU.STATUS            = 'A'

     AND ACCT_SITE.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID

     AND acct_site.status          = 'A'

     AND acct_site.org_id          = acct_site1.org_id

     AND hca.account_number        = hca1.account_number

     AND hps1.party_site_id        = party_site.party_site_id

  ) bill_to_address2,

 

  (SELECT DISTINCT loc.address3

  FROM hz_parties hp ,

    hz_cust_accounts hca ,

    HZ_LOCATIONS LOC ,

    HZ_PARTY_SITES PARTY_SITE ,

    HZ_CUST_SITE_USES_ALL ACCT_SU ,

    HZ_CUST_ACCT_SITES_ALL ACCT_SITE

  WHERE hp.party_id             = hca.party_id

     AND loc.location_id           = party_site.location_id

     AND party_site.party_site_id  = acct_site.party_site_id

     AND acct_su.cust_acct_site_id = acct_site.cust_acct_site_id

     AND acct_su.site_use_code     = 'BILL_TO'

     AND ACCT_SU.STATUS            = 'A'

     AND ACCT_SITE.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID

     AND acct_site.status          = 'A'

     AND acct_site.org_id          = acct_site1.org_id

     AND hca.account_number        = hca1.account_number

     AND hps1.party_site_id        = party_site.party_site_id

  ) bill_to_address3,

 

  (SELECT DISTINCT loc.address4

  FROM hz_parties hp ,

    hz_cust_accounts hca ,

    HZ_LOCATIONS LOC ,

    HZ_PARTY_SITES PARTY_SITE ,

    HZ_CUST_SITE_USES_ALL ACCT_SU ,

    HZ_CUST_ACCT_SITES_ALL ACCT_SITE

  WHERE hp.party_id             = hca.party_id

  AND loc.location_id           = party_site.location_id

  AND party_site.party_site_id  = acct_site.party_site_id

  AND acct_su.cust_acct_site_id = acct_site.cust_acct_site_id

  AND acct_su.site_use_code     = 'BILL_TO'

  AND ACCT_SU.STATUS            = 'A'

  AND ACCT_SITE.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID

  AND acct_site.status          = 'A'

  AND acct_site.org_id          = acct_site1.org_id

  AND hca.account_number        = hca1.account_number

  AND hps1.party_site_id        = party_site.party_site_id

  ) bill_to_address4,

 

  (SELECT DISTINCT loc.address1

  FROM hz_parties hp ,

    hz_cust_accounts hca ,

    HZ_LOCATIONS LOC ,

    HZ_PARTY_SITES PARTY_SITE ,

    HZ_CUST_SITE_USES_ALL ACCT_SU ,

    HZ_CUST_ACCT_SITES_ALL ACCT_SITE

  WHERE hp.party_id             = hca.party_id

  AND loc.location_id           = party_site.location_id

  AND party_site.party_site_id  = acct_site.party_site_id

  AND acct_su.cust_acct_site_id = acct_site.cust_acct_site_id

  AND acct_su.site_use_code     = 'SHIP_TO'

  AND ACCT_SU.STATUS            = 'A'

  AND ACCT_SITE.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID

  AND acct_site.status          = 'A'

  AND acct_site.org_id          = acct_site1.org_id

  AND hca.account_number        = hca1.account_number

  AND hps1.party_site_id        = party_site.party_site_id

  ) SHIP_to_address1,

 

  (SELECT DISTINCT loc.address2

  FROM hz_parties hp ,

    hz_cust_accounts hca ,

    HZ_LOCATIONS LOC ,

    HZ_PARTY_SITES PARTY_SITE ,

    HZ_CUST_SITE_USES_ALL ACCT_SU ,

    HZ_CUST_ACCT_SITES_ALL ACCT_SITE

  WHERE hp.party_id             = hca.party_id

  AND loc.location_id           = party_site.location_id

  AND party_site.party_site_id  = acct_site.party_site_id

  AND acct_su.cust_acct_site_id = acct_site.cust_acct_site_id

  AND acct_su.site_use_code     = 'SHIP_TO'

  AND ACCT_SU.STATUS            = 'A'

  AND ACCT_SITE.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID

  AND acct_site.status          = 'A'

  AND acct_site.org_id          = acct_site1.org_id

  AND hca.account_number        = hca1.account_number

  AND hps1.party_site_id        = party_site.party_site_id

  ) SHIP_to_address2,

 

  (SELECT DISTINCT loc.address3

  FROM hz_parties hp ,

    hz_cust_accounts hca ,

    HZ_LOCATIONS LOC ,

    HZ_PARTY_SITES PARTY_SITE ,

    HZ_CUST_SITE_USES_ALL ACCT_SU ,

    HZ_CUST_ACCT_SITES_ALL ACCT_SITE

  WHERE hp.party_id             = hca.party_id

  AND loc.location_id           = party_site.location_id

  AND party_site.party_site_id  = acct_site.party_site_id

  AND acct_su.cust_acct_site_id = acct_site.cust_acct_site_id

  AND acct_su.site_use_code     = 'SHIP_TO'

  AND ACCT_SU.STATUS            = 'A'

  AND ACCT_SITE.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID

  AND acct_site.status          = 'A'

  AND acct_site.org_id          = acct_site1.org_id

  AND hca.account_number        = hca1.account_number

  AND hps1.party_site_id        = party_site.party_site_id

  ) SHIP_to_address3,

 

  (SELECT DISTINCT loc.address4

  FROM hz_parties hp ,

    hz_cust_accounts hca ,

    HZ_LOCATIONS LOC ,

    HZ_PARTY_SITES PARTY_SITE ,

    HZ_CUST_SITE_USES_ALL ACCT_SU ,

    HZ_CUST_ACCT_SITES_ALL ACCT_SITE

  WHERE hp.party_id             = hca.party_id

  AND loc.location_id           = party_site.location_id

  AND party_site.party_site_id  = acct_site.party_site_id

  AND acct_su.cust_acct_site_id = acct_site.cust_acct_site_id

  AND acct_su.site_use_code     = 'SHIP_TO'

  AND ACCT_SU.STATUS            = 'A'

  AND ACCT_SITE.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID

  AND acct_site.status          = 'A'

  AND acct_site.org_id          = acct_site1.org_id

  AND hca.account_number        = hca1.account_number

  AND hps1.party_site_id        = party_site.party_site_id

  ) SHIP_to_address4,

 

  (SELECT DISTINCT party_site.party_site_number bill_PARTY_SITE_NUMBER

  FROM hz_parties hp ,

    hz_cust_accounts hca ,

    HZ_LOCATIONS LOC ,

    HZ_PARTY_SITES PARTY_SITE ,

    HZ_CUST_SITE_USES_ALL ACCT_SU ,

    HZ_CUST_ACCT_SITES_ALL ACCT_SITE

  WHERE hp.party_id             = hca.party_id

  AND loc.location_id           = party_site.location_id

  AND party_site.party_site_id  = acct_site.party_site_id

  AND acct_su.cust_acct_site_id = acct_site.cust_acct_site_id

  AND acct_su.site_use_code     = 'BILL_TO'

  AND ACCT_SU.STATUS            = 'A'

  AND ACCT_SITE.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID

  AND acct_site.status          = 'A'

  AND acct_site.org_id          = acct_site1.org_id

  AND hca.account_number        = hca1.account_number

  AND hps1.party_site_id        = party_site.party_site_id

  ) bill_to_party_site,

 

  (SELECT DISTINCT party_site.party_site_number bill_PARTY_SITE_NUMBER

  FROM hz_parties hp ,

    hz_cust_accounts hca ,

    HZ_LOCATIONS LOC ,

    HZ_PARTY_SITES PARTY_SITE ,

    HZ_CUST_SITE_USES_ALL ACCT_SU ,

    HZ_CUST_ACCT_SITES_ALL ACCT_SITE

  WHERE hp.party_id             = hca.party_id

  AND loc.location_id           = party_site.location_id

  AND party_site.party_site_id  = acct_site.party_site_id

  AND acct_su.cust_acct_site_id = acct_site.cust_acct_site_id

  AND acct_su.site_use_code     = 'SHIP_TO'

  AND ACCT_SU.STATUS            = 'A'

  AND ACCT_SITE.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID

  AND acct_site.status          = 'A'

  AND acct_site.org_id          = acct_site1.org_id

  AND hca.account_number        = hca1.account_number

  AND hps1.party_site_id        = party_site.party_site_id

  ) ship_to_party_site

FROM hz_parties hp1,

  hz_cust_accounts hca1,

  hz_locations hl1,

  HZ_PARTY_SITES hps1,

  HZ_CUST_SITE_USES_ALL ACCT_SU1,

  HZ_CUST_ACCT_SITES_ALL ACCT_SITE1

WHERE 1                        =1

   AND hp1.party_id               = hca1.party_id

   AND hp1.party_id               = hps1.party_id

   AND hl1.location_id            = hps1.location_id

   AND hps1.party_site_id         = acct_site1.party_site_id

   AND acct_su1.cust_acct_site_id = acct_site1.cust_acct_site_id

   AND ACCT_SITE1.CUST_ACCOUNT_ID = HCA1.CUST_ACCOUNT_ID

   AND acct_site1.org_id          = 457

   --AND hca1.account_number        = 10159

   AND hca1.account_number        = 13830;

----> Sponsored By Rakesh Rapolu