Location Address Formatting

The purpose of this page is to help you format addresses. There are times when you need to format address for exporting data and for print formats (especially Jasper where the system does not resolve an ID into an address for you). The following statement takes a c_location_id and outputs a formatted address:

CREATE OR REPLACE FUNCTION adempiere.chuboe_address_loc(loc_id numeric, sep_val text DEFAULT '
'::text)
 RETURNS text
 LANGUAGE sql
 SET search_path TO 'adempiere'
AS $function$
select concat_ws(sep_val,
        loc.address1::text,
        loc.address2::text,
        loc.address3::text,
        loc.address4::text
) || sep_val|| coalesce(loc.city, c.name, '') || ', ' ||
        coalesce(r.name, loc.regionname, '') || ' '
        || coalesce(loc.postal, '')
        || case when loc.postal_add is not null then '-'||loc.postal_add else '' end
        || case when loc.c_country_id <> 100 then sep_val|| co.name else '' end
from c_location loc
left outer join c_region r on loc.c_region_id = r.c_region_id
left outer join c_city c on loc.c_city_id = c.c_city_id
left outer join c_country co on loc.c_country_id = co.c_country_id
where c_location_id = loc_id
$function$

The next function is similar to the above; however, it formats an address based on the C_BPartner_Location_ID instead of the C_Location_ID:

CREATE OR REPLACE FUNCTION adempiere.chuboe_address_bploc(bp_loc_id numeric, sep_val text DEFAULT '
'::text)
 RETURNS text
 LANGUAGE sql
 SET search_path TO 'adempiere'
AS $function$
select ChuBoe_address_loc (bploc.c_location_id, sep_val)
from c_bpartner_location bploc
where bploc.c_bpartner_location_id = bp_loc_id
$function$

There is a function that will format an address to be viewed in google maps:

CREATE OR REPLACE FUNCTION adempiere.chuboe_googlemap_location(character varying)
 RETURNS character varying
 LANGUAGE sql
AS $function$SELECT 'https://maps.google.com/?q='|| REGEXP_REPLACE(REGEXP_REPLACE($1, '[.,]', '', 'g'), ' ', '+', 'g')
;$function$

Here is a Google Maps example use in the Quick Info Widget (Status Line window):

select array_to_string(array(
select concat('<a href="',chuboe_googlemap_location(chuboe_address_bploc(x.c_bpartner_location_id, ' ')),'" target="_blank">',chuboe_address_bploc(x.c_bpartner_location_id, ' '),'</a>') from c_bpartner_location x where c_bpartner_id = @C_BPartner_ID@
),'<br>')

Here is a Google Earth example use in Quick Info (note this is for a custom table - you will need to adapt to your table/details):

SELECT '<a href="https://earth.google.com/web/'||chr(64)||zlead.chuboe_gpslat_str||','||zlead.chuboe_gpslong_str||'" target="_blank">'||zlead.chuboe_project_seqno_sub||'_'||zlead.name||'_'||zlead.chuboe_gpslat_str||','||zlead.chuboe_gpslong_str||'</a>'
from chuboe_lead zlead
where zlead.chuboe_lead_id = @Chuboe_Lead_ID@
),'<br>')

Here are other examples using the above functions:

select *,
ChuBoe_address_bploc (bill_location_id) as bill_address_long,
ChuBoe_address_bploc (c_bpartner_location_id) as ship_address_long from c_order_header_v;
select ChuBoe_address_bploc (c_bpartner_location_id, '<br>') as ship_address_long from c_order_header_v;