Oracle EBS Latam Tax-Latin Tax Lookup

Oracle EBS Latam Tax-Latin Tax Lookup

Tables:-
FND_FLEX_VALUES_VL
FND_FLEX_VALUE_SETS

SQL Example:-

SELECT
ffvv.flex_value
,ffvv.flex_value_meaning
 ,ffvv.description
 ,ffvv.enabled_flag
 ,TO_CHAR(ffvv.start_date_active,'DD-MM-YYYY')
 ,TO_CHAR(ffvv.end_date_active,'DD-MM-YYYY')
 ,ffvs.flex_value_set_name
 FROM APPS.FND_FLEX_VALUES_VL FFVV
 ,APPS.FND_FLEX_VALUE_SETS FFVS
 WHERE 1=1
 AND ffvv.flex_value_set_id = ffvs.flex_value_set_id
 AND ffvs.flex_value_set_name = '&&';

Oracle EBS Latam Tax-LAT JL CITY

Oracle EBS Latam Tax-LAT JL CITY

Tables:-
FND_LOOKUP_TYPES_VL
FND_LOOKUP_VALUES_VL
SQL Example:-

SELECT
fltv.lookup_type  "Lookup Type"
,fltv.meaning "Meaning"
 ,fav.application_name  "Application Name"
 ,fltv.description  "Description"
 ,DECODE(fltv.customization_level,'U','User','','Other')  "Access Level"
 ,flvv.lookup_code
 ,flvv.meaning
 ,flvv.description
 ,flvv.tag
 ,TO_CHAR(flvv.start_date_active,'DD-MM-YYYY')
 ,TO_CHAR(flvv.end_date_active,'DD-MM-YYYY')
 ,flvv.enabled_flag
 FROM
 APPS.FND_LOOKUP_TYPES_VL fltv
 , APPS.FND_LOOKUP_VALUES_VL flvv
 , APPS.FND_APPLICATION_VL FAV
 WHERE 1=1
 AND fltv.lookup_type  =flvv.lookup_type
 AND fltv.application_id = fav.application_id
 AND fav.application_name IN ('Latin America Localizations')
 AND fltv.lookup_type = '&&STATE_PROVINCE'; 

Oracle EBS Latam Tax-Latin Fiscal Classification

Oracle EBS Latam Tax-Latin Fiscal Classification

Tables:-
JL_ZZ_AR_TX_FSC_LOOKUP_V
JL_ZZ_AR_TX_FSC_CLS_ALL

SQL Example:-

SELECT
jzat.lookup_code
,jzat.meaning
,jzat.description
 ,TO_CHAR(jzat.start_date_active,'DD-MM-YYYY')
 ,TO_CHAR(jzat.end_date_active,'DD-MM-YYYY')
 ,hou.name,TO_CHAR(jzatf.start_date_active,'DD-MM-YYYY')
 ,TO_CHAR(jzatf.end_date_active,'DD-MM-YYYY')
 ,jzatf.tax_code,jzatf.enabled_flag
 FROM
 apps.JL_ZZ_AR_TX_FSC_LOOKUP_V jzat
 ,apps.JL_ZZ_AR_TX_FSC_CLS_ALL jzatf
 ,apps.hr_operating_units hou
 WHERE 1=1
 AND jzatf.org_id = hou.organization_id
 AND jzatf.fiscal_classification_code = jzat.lookup_code ;

Oracle EBS Latam Tax-China Golden Tax Rules

Oracle EBS Latam Tax-China Golden Tax Rules
Tables:-
AR_GTA_RULE_HEADERS_ALL
AR_GTA_RULE_TRX_TYPES_ALL

SQL Example:-

SELECT
hou.name
,TO_CHAR(agr.start_date,'DD-MM-YYYY')
 ,agr.rule_name
 ,agr.description
 ,DECODE(agr.Invoice_type,0,'Special vat Invoice',2,'Common Vat invoice')
 ,agr.primary_flag
 ,(select name from APPS.RA_CUST_TRX_TYPES_ALL RCTT where cust_trx_type_id = agrt.cust_trx_type_id)
 ,DECODE(agrt.condition_rule,'I','Include','E','Exclude')
 ,agrt.description
 ,agr.sales_list_flag
 ,agr.currency_option
 ,agr.specific_currency_code
 FROM
 apps.AR_GTA_RULE_HEADERS_ALL agr,
 apps.AR_GTA_RULE_TRX_TYPES_ALL agrt,
 apps.hr_operating_units hou
 WHERE 1=1
 AND agr.rule_header_id = agrt.rule_header_id
AND   agr.org_id =  agrt.org_id
AND   agr.org_id =  hou.organization_id ;

Oracle EBS Latam Tax-Condition Classes

Oracle EBS Latam Tax-Condition Classes

Tables:-
JL_ZZ_AR_TX_ATT_CLS_LOOKUP_V
JL_ZZ_AR_TX_ATT_CLS_V

SQL Example:-

SELECT
jzav.tax_attr_class_type  "Class Type"
,jzav.tax_attr_class_code "Class Code"
,jzav.tax_attribute_name  "Condition Code"
,jzalv.start_date_active  "Start Date Active"
,jzalv.end_date_active  "End Date Active"
,hou.name "Name"
,jzav.tax_category  "Tax Category"
,jzav.enabled_flag  "Enabled Flag"
FROM
JL_ZZ_AR_TX_ATT_CLS_LOOKUP_V jzalv
,JL_ZZ_AR_TX_ATT_CLS_V jzav
,HR_OPERATING_UNITS hou
WHERE 1=1
AND jzalv.lookup_type = jzav.tax_attr_class_type
 AND jzav.org_id = hou.organization_id
 AND jzalv.lookup_type   = UPPER('Organization_Class')
 AND hou.short_code= '&&';

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;

Oracle EBS Latam Tax-Company WTH Applicability

Oracle EBS Latam Tax-Company WTH Applicability

Tables:-
JL_ZZ_AP_COMP_AWT_TYPES

SQL Example:-

SELECT
c.name  "Operating Unit"
,b.name "Legal Entity"
,awt_type_code  "With Holding Tax Type"
,wh_agent_flag  "WH Agent Flag"
,payment_city   "Payment City"
FROM
APPS.JL_ZZ_AP_COMP_AWT_TYPES a
,APPS.XLE_ENTITY_PROFILES b
,APPS.HR_OPERATING_UNITS c
WHERE 1=1
AND a.legal_entity_id = b.legal_entity_id
AND b.legal_entity_id   = c.default_legal_context_id ;

Oracle EBS Latam Tax-Latin Tax Profiles

Oracle EBS Latam Tax-Latin Tax Profiles

Tables:-
JL_ZZ_AR_TX_CATEG_ALL
JL_ZZ_AR_TX_CUS_CLS_ALL

SQL Example:-

SELECT DISTINCT ---'JL','Q_LAT_TAX_PROFILES','DATA','E',
 hou.name "Operating Unit"
 ,tc.tax_category "Tax Category"
 ,tcac.tax_attribute_name
,SUBSTR(JL_ZZ_AR_TX_LIB_PKG.get_lookup_meaning(tcac.tax_attribute_name, 'CONTRIBUTOR_ATTRIBUTE'),1,80)  "Condition Code"
,tcac.tax_attribute_value "Value Code"
,SUBSTR(JL_ZZ_AR_TX_LIB_PKG.get_lookup_meaning(tcac.tax_attribute_value, 'JLZZ_AR_TX_ATTR_VALUE'),1,80)  "Value Meaning"
,tcac.enabled_flag  "Enabled Flag"
FROM
JL_ZZ_AR_TX_CATEG_ALL tc
,JL_ZZ_AR_TX_CUS_CLS_ALL tcac
,HR_OPERATING_UNITS hou
WHERE 1=1
AND tcac.tax_category_id = tc.tax_category_id
AND tcac.org_id = hou.organization_id; 

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;

Oracle EBS Latam Tax-State Province

Oracle EBS Latam Tax-State Province

Tables:-
FND_LOOKUP_TYPES_VL
FND_LOOKUP_VALUES_VL

SQL Example:-

SELECT
fltv.lookup_type
,fltv.meaning
,fav.application_name
,fltv.description
,DECODE(fltv.customization_level,'U','User','','Other') "Access Level"
,flvv.lookup_code
,flvv.meaning "Line Meaning"
,flvv.description "Line Desc"
,flvv.tag
,TO_CHAR(flvv.start_date_active,'DD-MM-YYYY') "Start Date Active"
,TO_CHAR(flvv.end_date_active,'DD-MM-YYYY') "End Date Active"
,flvv.enabled_flag
FROM
APPS.FND_LOOKUP_TYPES_VL fltv
,APPS.FND_LOOKUP_VALUES_VL flvv
,APPS.FND_APPLICATION_VL FAV
WHERE 1=1
AND fltv.lookup_type  =flvv.lookup_type
AND fltv.application_id = fav.application_id
AND fav.application_name IN ('Latin America Localizations')
AND fltv.lookup_type = 'JLZZ_STATE_PROVINCE';

Oracle EBS Latam Tax-China System Profiles

Oracle EBS Latam Tax-China System Profiles
Tables:-
AR_GTA_SYSTEM_PARAMETERS_ALL
AR_GTA_TAX_LIMITS_ALL

SQL Example:-

SELECT
 hou.name "OU NAME"
 ,agt.FP_tax_registration_number  "Gol Tax Registration Num"
 ,DECODE(agt.Invoice_type,0,'Special vat Invoice',2,'Common Vat invoice') "Invoice Type"
 ,agt.max_amount  "Gol Tax Max Amount"
 ,agt.max_num_of_line "Gol Tax No Line"
 ,ags.gt_currency_code  "Gol Tax Currency Code"
 ,ags.vat_tax_type_code "Gol Tax Type"
 ,ags.trx_line_split_flag "Gol Split Transaction Line"
 ,ags.auto_batch_numbering_flag "Batch Entry"
 ,ags.next_batch_number "Batch Number"
 ,ags.item_name_source_flag "Item Source flag"
 ,ags.ra_line_context_code  "Context"
 ,ags.ra_model_attribute_column "Item Model"
 ,ags.ra_tax_attribute_column   "Tax Denomination"
 FROM
 apps.AR_GTA_SYSTEM_PARAMETERS_ALL ags,
     apps.AR_GTA_TAX_LIMITS_ALL agt,
     apps.hr_operating_units hou
        WHERE 1=1
     AND agt.org_id =  ags.org_id
 AND ags.org_id = hou.organization_id;

Oracle EBS Latam Tax-AP Application Tax Options

Oracle EBS Latam Tax-AP Application Tax Options

Tables:-
ZX_PRODUCT_OPTIONS_ALL

SQL Example:-
SELECT DISTINCT
  b.name  "OU Name"
  ,(SELECT meaning
     FROM Fnd_lookups
     WHERE lookup_type                 = DECODE(a.application_id,200, 'ZX_AP_DEFAULT_HIERARCHY',222, 'ZX_AR_DEFAULT_HIERARCHY',201, 'ZX_PO_DEFAULT_HIERARCHY',275, 'ZX_PA_DEFAULT_HIERARCHY', 8407,'ZX_IGC_DEFAULT_HIERARCHY')
     AND lookup_code                   = a.def_option_hier_1_code
     AND START_DATE_ACTIVE            <= SYSDATE
     AND NVL(END_DATE_ACTIVE, SYSDATE)>= SYSDATE
     AND ENABLED_FLAG                  = 'Y') "Defaulting Order1"
,(SELECT Meaning
     FROM Fnd_lookups
     WHERE lookup_type                  = DECODE (a.application_id,200,'ZX_AP_DEFAULT_HIERARCHY',222, 'ZX_AR_DEFAULT_HIERARCHY',201, 'ZX_PO_DEFAULT_HIERARCHY',275, 'ZX_PA_DEFAULT_HIERARCHY',8407,'ZX_IGC_DEFAULT_HIERARCHY')
     AND lookup_code                    = a.DEF_OPTION_HIER_2_CODE
     AND START_DATE_ACTIVE             <= SYSDATE
     AND NVL(END_DATE_ACTIVE, SYSDATE) >= SYSDATE
     AND ENABLED_FLAG                   = 'Y') "Defaulting Order2"
,(SELECT Meaning
     FROM Fnd_lookups
     WHERE lookup_type                  = DECODE (a.application_id,200, 'ZX_AP_DEFAULT_HIERARCHY',222,'ZX_AR_DEFAULT_HIERARCHY',201, 'ZX_PO_DEFAULT_HIERARCHY',275, 'ZX_PA_DEFAULT_HIERARCHY',8407,'ZX_IGC_DEFAULT_HIERARCHY')
     AND lookup_code                    = a.DEF_OPTION_HIER_3_CODE
     AND START_DATE_ACTIVE             <= SYSDATE
     AND NVL(END_DATE_ACTIVE, SYSDATE) >= SYSDATE
     AND ENABLED_FLAG                   = 'Y') "Defaulting Order3"
,(SELECT meaning
     FROM Fnd_lookups
     WHERE lookup_type                  = DECODE (a.application_id,200, 'ZX_AP_DEFAULT_HIERARCHY',222, 'ZX_AR_DEFAULT_HIERARCHY',201,'ZX_PO_DEFAULT_HIERARCHY',275, 'ZX_PA_DEFAULT_HIERARCHY',8407,'ZX_IGC_DEFAULT_HIERARCHY')
     AND lookup_code                    = a.def_option_hier_4_code
     AND START_DATE_ACTIVE             <= SYSDATE
     AND NVL(END_DATE_ACTIVE, SYSDATE) >= SYSDATE
     AND ENABLED_FLAG                   = 'Y')  "Defaulting Order4"
,(SELECT meaning
     FROM Fnd_lookups
     WHERE lookup_type                  = DECODE (a.application_id,200, 'ZX_AP_DEFAULT_HIERARCHY',222, 'ZX_AR_DEFAULT_HIERARCHY',201, 'ZX_PO_DEFAULT_HIERARCHY',275, 'ZX_PA_DEFAULT_HIERARCHY',8407,'ZX_IGC_DEFAULT_HIERARCHY')
     AND lookup_code                    = a.DEF_OPTION_HIER_5_CODE
     AND START_DATE_ACTIVE             <= SYSDATE
     AND NVL(END_DATE_ACTIVE, SYSDATE) >= SYSDATE
     AND ENABLED_FLAG                   = 'Y')  "Defaulting Order5"
,(SELECT meaning
     FROM Fnd_lookups
     WHERE lookup_type                  = DECODE (a.application_id,200, 'ZX_AP_DEFAULT_HIERARCHY',222, 'ZX_AR_DEFAULT_HIERARCHY',201, 'ZX_PO_DEFAULT_HIERARCHY',275, 'ZX_PA_DEFAULT_HIERARCHY',8407,'ZX_IGC_DEFAULT_HIERARCHY')
     AND lookup_code                    = a.DEF_OPTION_HIER_6_CODE
     AND START_DATE_ACTIVE             <= SYSDATE
     AND NVL(END_DATE_ACTIVE, SYSDATE) >= SYSDATE
     AND ENABLED_FLAG                   = 'Y')  "Defaulting Order6"
,(SELECT meaning
     FROM Fnd_lookups
     WHERE lookup_type                  = DECODE (a.application_id,200, 'ZX_AP_DEFAULT_HIERARCHY',222, 'ZX_AR_DEFAULT_HIERARCHY',201, 'ZX_PO_DEFAULT_HIERARCHY',275, 'ZX_PA_DEFAULT_HIERARCHY',8407,'ZX_IGC_DEFAULT_HIERARCHY')
     AND lookup_code                    = a.DEF_OPTION_HIER_7_CODE
     AND START_DATE_ACTIVE             <= SYSDATE
     AND NVL(END_DATE_ACTIVE, SYSDATE) >= SYSDATE
     AND ENABLED_FLAG                   = 'Y')  "Defaulting Order7"
,a.tax_classification_code  "Fin. Option Tax Classification"
,DECODE(a.tax_method_code,'LTE',NULL,c.Meaning) "Default Order Use Status"
FROM
APPS.ZX_PRODUCT_OPTIONS_ALL a,   
APPS.HR_OPERATING_UNITS b,   
APPS.FND_LOOKUPS c
WHERE 1=1
AND a.org_id= b.organization_id 
AND c.lookup_code                    = a.use_tax_classification_flag 
AND c.lookup_type                    = 'ZX_DEFAULTING_HIERARCHY_STATUS' 
AND c.start_date_active             <= SYSDATE 
AND NVL(c.end_date_active, SYSDATE) >= SYSDATE 
AND c.enabled_flag                   = 'Y' 
AND a.APPLICATION_ID =   
(SELECT fav.application_id   
FROM apps.fnd_application_vl fav   
WHERE fav.application_short_name='SQLAP')
AND b.name= '&&';

Oracle EBS Latam Tax-Latin Tax Groups

Oracle EBS Latam Tax-Latin Tax Groups
Tables:-
JL_ZZ_AR_TX_GROUPS

SQL Example:-

 SELECT DISTINCT
 b.name "Operating Unit"
,(select TAX_CODE from APPS.AR_VAT_TAX WHERE ORG_ID=a.org_id and VAT_TAX_ID=a.GROUP_TAX_ID) "Tax Group"
,c.TAX
,(select CUS_TAX_ATTRIBUTE from JL_ZZ_AR_TX_CATEGRY where TAX_CATEGORY_ID=a.TAX_CATEGORY_ID
and ORG_ID=a.ORG_ID
and TAX_CATEGORY=c.TAX) "Contributor Condition"
,a.CONTRIBUTOR_TYPE "Contributor Value"
,(select ORG_TAX_ATTRIBUTE from JL_ZZ_AR_TX_CATEGRY where TAX_CATEGORY_ID=a.TAX_CATEGORY_ID
and ORG_ID=a.ORG_ID
and TAX_CATEGORY=c.TAX) "Organization Condition"
,a.ESTABLISHMENT_TYPE "Organization Value"
,(select TXN_TAX_ATTRIBUTE from JL_ZZ_AR_TX_CATEGRY where TAX_CATEGORY_ID=a.TAX_CATEGORY_ID
and ORG_ID=a.ORG_ID
and TAX_CATEGORY=c.TAX) "Transaction Condition"
,a.TRANSACTION_NATURE "Transaction Value"
,a.START_DATE_ACTIVE  "Effective From"
,a.END_DATE_ACTIVE  "Effective To"
,a.USE_TX_CATEG_THRESHOLDS  "Use Category Thresholds"
,a.TAX_CODE
,a.BASE_RATE
,DECODE(a.TRIBUTARY_SUBSTITUTION, NULL, 'Y', 'N') "Tributary Substitution"
,(select distinct TAX_CATEG_TO_REDUCE_DESC FROM APPS.JL_ZZ_AR_TX_GROUPS_V
WHERE ORG_ID=a.org_id and GROUP_TAX_ID=a.GROUP_TAX_ID and TAX_CATEGORY=c.TAX)  "Tax Category to Compound Base"
,a.MIN_AMOUNT
,a.MIN_TAXABLE_BASIS
,a.MIN_PERCENTAGE
,a.TAX_INCLUSIVE
,a.CALCULATE_IN_OE  "Calculate in OM"
,(select CUS_TAX_ATTRIBUTE_DESC from JL_ZZ_AR_TX_CATEGRY_V where TAX_CATEGORY_ID=a.TAX_CATEGORY_ID
and ORG_ID=a.ORG_ID
and TAX_CATEGORY=c.TAX) "Deter_Fact_Tran_Condi"
,(select ORG_TAX_ATTRIBUTE_DESC from JL_ZZ_AR_TX_CATEGRY_V where TAX_CATEGORY_ID=a.TAX_CATEGORY_ID
and ORG_ID=a.ORG_ID
and TAX_CATEGORY=c.TAX) "Deter_Fact_Org_Cond"
,(select ATTR_VAL_DESC from JL_ZZ_AR_TX_ATT_VAL_V where TAX_CATEGORY_ID=a.TAX_CATEGORY_ID
and ORG_ID=a.ORG_ID
and TAX_ATTRIBUTE_VALUE=a.ESTABLISHMENT_TYPE
and DEFAULT_TO_CLASS='Y') "Deter_Fact_Org_Value"
,(select TXN_TAX_ATTRIBUTE_DESC from JL_ZZ_AR_TX_CATEGRY_V where TAX_CATEGORY_ID=a.TAX_CATEGORY_ID
and ORG_ID=a.ORG_ID
and TAX_CATEGORY=c.TAX) "Deter_Fact_Tran_Condi"
,NVL((select TRANSACTION_NATURE_DESC from APPS.JL_ZZ_AR_TX_GROUPS_V where GROUP_TAX_ID=a.GROUP_TAX_ID
and ORG_ID=a.ORG_ID
and TAX_GROUP_RECORD_ID=a.TAX_GROUP_RECORD_ID
and TRANSACTION_NATURE=a.TRANSACTION_NATURE
and TAX_CATEGORY=c.TAX) ,a.TRANSACTION_NATURE) "Deter_Fact_Tran_Value"
,(select DESCRIPTION from APPS.AR_VAT_TAX WHERE ORG_ID=a.org_id and VAT_TAX_ID=a.GROUP_TAX_ID)  "DESCRIPTION"
,(select ATTRIBUTE1 from CLL_F043_JL_ZZ_AR_TX_GRPS_EXT
where TAX_GROUP_RECORD_ID=a.TAX_GROUP_RECORD_ID)  "Withholding Tax Type"
,c.TAX_RATE
,c.DESCRIPTION
FROM
APPS.JL_ZZ_AR_TX_GROUPS a,
APPS.HR_OPERATING_UNITS b,
APPS.AR_VAT_TAX c,
WHERE 1=1
AND a.org_id= b.organization_id
AND a.TAX_CODE=c.TAX_CODE
AND a.org_id not in ('4176')
AND a.ORG_ID=c.ORG_ID
and c.END_DATE is  null
and c.ENABLED_FLAG='Y'
and a.TAX_CATEGORY_ID=c.GLOBAL_ATTRIBUTE1;

Oracle EBS Latam Tax-Vat Document Letter Assignment

Oracle EBS Latam Tax-Vat Document Letter Assignment

Tables:-
JL_AR_AR_DOC_LETTER_ALL

SQL Example:-

SELECT
hou.name  "Operating Unit"
,vlkp1.meaning  "Organization Condition"
,vlkp2.meaning  "Organization Value"
,clkp1.meaning  "Contributor Condition"
,clkp2.meaning  "Contributor Value"
,doc.document_letter  "Document Letter"
,doc.start_date_active  "From Date"
,doc.end_date_active  "To Date"
FROM
JL_AR_AR_DOC_LETTER_ALL doc
,FND_LOOKUPS vlkp1
,FND_LOOKUPS vlkp2
,FND_LOOKUPS clkp1
,FND_LOOKUPS clkp2
,fnd_lookups dlkp
,HR_OPERATING_UNITS hou
WHERE 1=1
AND doc.org_tax_attribute_name = vlkp1.lookup_code
AND vlkp1.lookup_type = 'ORGANIZATION_ATTRIBUTE'
AND doc.org_tax_attribute_value  = vlkp2.lookup_code
AND vlkp2.lookup_type = 'JLZZ_AR_TX_ATTR_VALUE'
AND doc.con_tax_attribute_name   = clkp1.lookup_code
AND clkp1.lookup_type = 'CONTRIBUTOR_ATTRIBUTE'
AND doc.con_tax_attribute_value  = clkp2.lookup_code
AND clkp2.lookup_type = 'JLZZ_AR_TX_ATTR_VALUE'
AND doc.document_letter = dlkp.lookup_code
AND dlkp.lookup_type = 'JLAR_DOCUMENT_LETTER'
AND doc.org_id = hou.organization_id
AND hou.short_code= '&&';

SQL for AR CM & DM

SELECT rct.CUSTOMER_TRX_ID
,rct.TRX_NUMBER
,rctt.CUST_TRX_TYPE_ID
,rct.OLD_TRX_NUMBER
,rctt.NAME
,rctt.DESCRIPTION
,rctt.TYPE
FROM
APPS.RA_CUSTOMER_TRX_ALL rct,
APPS.RA_CUST_TRX_TYPES_ALL rctt
WHERE rct.CUST_TRX_TYPE_ID=rctt.CUST_TRX_TYPE_ID
and rct.LEGAL_ENTITY_ID=&&
and rctt.TYPE='DM';