SELECT
pvs.vendor_site_id vendor_site_id,
pv.vendor_name vendor_name,
pvs.address_line1 address1,
pvs.address_line2 address2,
pvs.address_line3 address3,
pvs.city city,
nvl(pvs.state, pvs.province) state,
pvs.zip zip,
nvl(pvs.country,:DEFAULT_COUNTRY_CODE_1) country_code,
nvl(ft.territory_short_name,:DEFAULT_COUNTRY_NAME) country_name,
decode(pvs.attention_ar_flag, 'N', NULL, :ATTN_MESSAGE) attention,
AP_APXVDLBL_XMLP_PKG.c_address_concatenatedformula(pvs.address_line1, pvs.address_line2, pvs.address_line3, pvs.city, nvl(pvs.state, pvs.province), pvs.zip, nvl(ft.territory_short_name,:DEFAULT_COUNTRY_NAME), nvl(pvs.country,:DEFAULT_COUNTRY_CODE_1), pv.vendor_name, decode(pvs.attention_ar_flag, 'N', NULL, :ATTN_MESSAGE)) C_address_concatenated
FROM po_vendors pv , po_vendor_sites pvs, fnd_territories_vl ft
WHERE pv.vendor_id = pvs.vendor_id
AND pvs.country = ft.territory_code (+)
and pvs.vendor_site_id=:site_id |