Oracle EBS Latam Tax-Geography Hierarchy

Oracle EBS Latam Tax-Geography Hierarchy

Tables:-
HZ_GEOGRAPHIES
HZ_GEOGRAPHY_IDENTIFIERS
HZ_HIERARCHY_NODES

SQL Example:-

SELECT
hg.geography_element2 "Province"
,hg.geography_name  "Geography Name" 
,hg.geography_code  "Geography Code"
 ,gi.identifier_subtype "Code Type"
 ,gi.geo_data_provider  "Code Data"
 ,hg.COUNTRY_CODE "Country Code"
 ,hg.GEOGRAPHY_ELEMENT1 "Country"
 ,hg.GEOGRAPHY_ELEMENT1_CODE
 ,hg.GEOGRAPHY_ELEMENT2_CODE
 ,hg.GEOGRAPHY_ELEMENT3
 ,hg.GEOGRAPHY_ELEMENT3_CODE
 ,hg.GEOGRAPHY_ELEMENT4
 ,hg.GEOGRAPHY_ELEMENT4_CODE
 ,hg.GEOGRAPHY_ELEMENT5
 ,hg.GEOGRAPHY_ELEMENT5_CODE
 ,hg.GEOGRAPHY_ELEMENT6
 ,hg.GEOGRAPHY_ELEMENT7
 ,hg.GEOGRAPHY_ELEMENT8
 ,hg.GEOGRAPHY_ELEMENT9
 ,hg.GEOGRAPHY_ELEMENT10
 ,gi.IDENTIFIER_VALUE,gi.PRIMARY_FLAG
 FROM APPS.HZ_GEOGRAPHIES hg,
APPS.HZ_GEOGRAPHY_IDENTIFIERS gi,
APPS.HZ_HIERARCHY_NODES nd,
APPS.XLE_ENTITY_PROFILES xep,
APPS.HR_OPERATING_UNITS hou
WHERE 1=1
AND gi.geography_id (+) =hg.geography_id
AND nd.parent_table_name  = 'HZ_GEOGRAPHIES'
AND nd.hierarchy_type     = 'MASTER_REF'
AND nd.level_number       = 1
AND nd.child_id           = hg.geography_id
AND nd.child_object_type  = hg.geography_type
AND nd.child_table_name   = 'HZ_GEOGRAPHIES'
      AND gi.identifier_type(+) ='CODE'
AND gi.geography_use(+)   ='MASTER_REF'
      and hg.OBJECT_VERSION_NUMBER=gi.OBJECT_VERSION_NUMBER
AND nd.PARENT_ID=xep.geography_id
      AND xep.LEGAL_ENTITY_ID=hou.DEFAULT_LEGAL_CONTEXT_ID;