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;