Oralce Fusion: SQL to Get Concatenated Address example

Inventory Organization Location

=================================

SELECT z.organization_code,
       z.organization_name,
       b.location_id,
       b.active_status,
       b.effective_start_date,
       b.effective_end_date,
       b.internal_location_code,
       b.location_use,
       b.location_code,
       b.location_name,
       b.description,
       b.style,
       Concat(Concat(Concat(Concat(Concat(Concat(
                     Concat(Concat(b.address_line_1 || ', ',
                     Nvl2(b.address_line_2, b.address_line_2 || ', ', b.address_line_2)),
                     Nvl2(b.address_line_3, b.address_line_3 || ', ', b.address_line_3)),
                     Nvl2(b.address_line_4, b.address_line_4 || ', ', b.address_line_4)),
                     Nvl2(b.town_or_city, b.town_or_city || ', ', b.town_or_city)),
                     Nvl2(b.region_1, b.region_1 || ', ', b.region_1)),
                     Nvl2(b.region_2, b.region_2 || ', ', b.region_2)),
                     Nvl2(b.country, country || ', ', b.country)), b.postal_code) concatenated_address
FROM   inv_organization_definitions_v z,
       hr_locations b
WHERE  z.location_id = b.location_id;

 

Party/Customer/HZ Location

=================================

SELECT  concat(concat(concat(concat(concat(concat(concat(concat(address1||', ',NVL2(address2,address2||', ',address2)),NVL2(address3,address3||', ',address3)),NVL2(address4,address4||', ',address4)),NVL2(city,city||', ',city)),NVL2(county,county||', ',county)),NVL2(state,state||', ',state)),NVL2(province, province||', ',province)),postal_code) concatenated_address
  FROM hz_locations
 WHERE country ='US';  

SELECT hzp.party_name
       || ' '
       || hzp.party_number,
       hzp.party_id,
       hzps.party_site_number "ShipToPartySiteNumber",
       HZA.account_number,
       HZA.account_name,
       hza.cust_account_id,
       HZA.status "Account Status",
       hzp.status "Party Status",
       hzps.status  "Party Site Status",
       hzps.party_site_id  "PARTY SITE ID - for SHIP_TO",
       hzcasa.status "Account Site Status",
       hzcsua.site_use_id "Account Site ID - for BILL_TO",
       hzcasa.start_date,
       hzcasa.end_date,
       hzcsua.site_use_code,
       hzcasa.bill_to_flag,
       hzcasa.ship_to_flag,
       hzcsua.primary_flag,
       hzcsua.status "Account Site USE Status",
       hzcsua.location,
     concat(concat(concat(concat(concat(concat(concat(concat(hzl.address1||', ', NVL2(hzl.address2, hzl.address2||', ', hzl.address2)), NVL2(hzl.address3, hzl.address3||', ', hzl.address3)), NVL2(hzl.address4, hzl.address4||', ', hzl.address4)), NVL2(hzl.city, hzl.city||', ', hzl.city)), NVL2(hzl.county, hzl.county||', ', hzl.county)), NVL2(hzl.state, hzl.state||', ', hzl.state)), NVL2(hzl.province, hzl.province||', ', hzl.province)), hzl.postal_code) concatenated_address,
       hzl.location_id
FROM   fusion.hz_parties HZP,
       fusion.hz_party_sites hzps,
       fusion.hz_cust_accounts HZA,
       fusion.hz_cust_acct_sites_all hzcasa,
       fusion.hz_cust_site_uses_all hzcsua,
       fusion.hz_locations HZL
WHERE  hzP.party_id = HZA.party_id (+)
       AND hza.cust_account_id = hzcasa.cust_account_id (+)
       AND hzcasa.party_site_id = hzps.party_site_id (+)
       AND hzcasa.cust_acct_site_id = hzcsua.cust_acct_site_id (+)
       AND hzps.location_id = hzl.location_id (+)
       AND hzp.party_number = :p_party_number
       AND hzcsua.primary_flag = 'Y'
ORDER  BY hzp.party_number,
          hza.account_number,
          hzl.location_id