Oracle EBS Latam Tax-Geography Management

Oracle EBS Latam Tax-Geography Management

Tables:-
HZ_GEO_STRUCT_MAP_DTL
HZ_GEO_STRUCTURE_LEVELS
HZ_GEOGRAPHY_TYPES_VL
HZ_GEOGRAPHIES
HZ_GEO_STRUCT_MAP

SQL Example:-

SELECT
hg.geography_name "Geography Name"
,a.COUNTRY_CODE "Country Code"
,a.LOC_TBL_NAME "Source Table"
,a.ADDRESS_STYLE  "Address Style"
,decode(a.ADDRESS_STYLE,null,( select meaning from apps.ar_lookups where lookup_type = 'HZ_GEO_NO_STYLE'and lookup_code = 'NOSTYLE')
,decode( a.LOC_TBL_NAME,'HZ_LOCATIONS',nvl(( select descriptive_flex_context_name from apps.fnd_descr_flex_contexts_vl where application_id in ( '222')
and descriptive_flexfield_name ='Remit Address HZ' and descriptive_flex_context_code = a.ADDRESS_STYLE)
,a.ADDRESS_STYLE),nvl((select descriptive_flex_context_name from apps.fnd_descr_flex_contexts_vl where application_id in ('800')
and descriptive_flexfield_name ='Address Location' and descriptive_flex_context_code =a.ADDRESS_STYLE),
a.ADDRESS_STYLE ))) "Address Style Desc"
,decode( a.LOC_TBL_NAME, 'HZ_LOCATIONS', 'Remit Address HZ', 'Address Location') "Desc Flex Name"
,(select meaning from apps.ar_lookups where lookup_type = 'HZ_GEO_NO_STYLE' and lookup_code = 'NOSTYLE')  "Default Address Style"
,decode(a.LOC_TBL_NAME, 'HZ_LOCATIONS',decode(a.ADDRESS_STYLE, null, 'N', 'Y'), 'Y')  "Delete enabled"
,hgt.geography_type_name  "Geography Type Name"
,b.loc_component  "Geo Valid"
,hg.geography_name  "Geography Name"
,hgt.geography_type_name  "Geography Type"
,b.loc_component  "LOC Componenet"
,HZ_GEO_UI_UTIL_PUB.check_geo_tax_valid( b.map_id, b.geography_type, 'TAX') "Tax Valid"
,HZ_GEO_UI_UTIL_PUB.check_geo_tax_valid( b.map_id, b.geography_type, 'GEOGRAPHY') "Geo Valid"
FROM
APPS.HZ_GEO_STRUCT_MAP_DTL b,
APPS.HZ_GEO_STRUCTURE_LEVELS lvl,
APPS.HZ_GEOGRAPHY_TYPES_VL hgt,
APPS.HZ_GEOGRAPHIES hg,
APPS.XLE_ENTITY_PROFILES xep,
apps.HZ_GEO_STRUCT_MAP a,
APPS.XLE_REGISTRATIONS reg,
APPS.HR_OPERATING_UNITS hou
WHERE 1=1
AND b.geography_type (+) = lvl.geography_type
AND b.geography_type = lvl.geography_type
AND hgt.geography_type     = lvl.geography_type
AND hg.geography_id = lvl.geography_id
AND  hg.geography_id =xep.geography_id
AND a.COUNTRY_CODE = hg.country_code
AND b.map_id = a.map_id
 AND xep.legal_entity_id = reg.source_id
AND reg.source_table = 'XLE_ENTITY_PROFILES'
AND xep.legal_entity_id = hou.default_legal_context_id;