Oracle EBS Latam Tax-Source and Type Relationship

Oracle EBS Latam Tax-Source and Type Relationship

Tables:-
JG_ZZ_AR_SRC_TRX_TY_ALL

SQL Example:-
SELECT
hou.name  "OU NAME"
,(SELECT name
    FROM apps.RA_CUST_TRX_TYPES_ALL rct
      WHERE rct.cust_trx_type_id = jt.cust_trx_type_id) "Transaction Type Name"
,(SELECT name
    FROM apps.RA_BATCH_SOURCES_ALL rbs
      WHERE rbs.batch_source_id =jt.batch_source_id)  "Transaction Source Name"
,jt.Invoice_class "Invoice Class"
FROM
JG_ZZ_AR_SRC_TRX_TY_ALL jt
,hr_operating_units hou
WHERE 1=1
AND jt.org_id = hou.organization_id 
AND hou.short_code= '&&';

Oracle EBS Latam Tax-AR Application Tax Options

Oracle EBS Latam Tax-AR Application Tax Options

Tables:-
ZX_PRODUCT_OPTIONS_ALL

SQL Example:-
SELECT 
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"
,a.tax_classification_code "System Option Tax Classi"
,(SELECT Meaning
     FROM Fnd_Lookups
     WHERE Lookup_Type                  = 'ZX_TAX_METHOD_OPTIONS'
     AND Lookup_Code                    = a.TAX_METHOD_CODE
     AND START_DATE_ACTIVE             <= SYSDATE
     AND NVL(END_DATE_ACTIVE, SYSDATE) >= SYSDATE
     AND ENABLED_FLAG                   = 'Y'  ) "Tax Determination Method"
,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='AR')
AND b.name= '&&';

Oracle EBS Latam Tax-Transaction Types Codes

Oracle EBS Latam Tax-Transaction Types Codes

Tables:-
JL_AR_AP_TRX_DGI_CODES

SQL Example:-

SELECT
a.TRX_CATEGORY  "Legal Transaction Category"
,a.TRX_LETTER "Transaction Letter"
,a.DGI_CODE "Transaction Type"
,b.DESCRIPTION "Description"
FROM
APPS.JL_AR_AP_TRX_DGI_CODES a,
APPS.fnd_lookups b,
APPS.fnd_lookup_values b1,
APPS.hz_geographies C,
APPS.xle_entity_profiles xep
WHERE 1=1
AND a.DGI_CODE=b.LOOKUP_CODE
AND b.LOOKUP_TYPE='JLAR_TAX_AUTHORITY_TRX_TYPE'
AND b.LOOKUP_TYPE=b1.LOOKUP_TYPE
AND b.LOOKUP_CODE=b1.LOOKUP_CODE
AND b1.lookup_type = 'JLAR_TAX_AUTHORITY_TRX_TYPE'
AND b1.LANGUAGE='AR'
AND b1.LANGUAGE=c.COUNTRY_CODE
AND c.COUNTRY_CODE='AR'
AND c.GEOGRAPHY_ID=xep.GEOGRAPHY_ID;

Oracle EBS GST-Asia Pasific Lookups

Oracle EBS GST-Asia Pasific Lookups
Configuration:Update GSTIN Lookup,
Responsibility:Oracle Financials For India,
Navigation:Asia/Pasific Lookups

Tables:-
FND_LOOKUP_TYPES_VL
FND_APPLICATION_TL
FND_LOOKUP_VALUES

SQL Example:-
SELECT
fltv.LOOKUP_TYPE "Type"
,fltv.MEANING  "Meaning"
,fatl.APPLICATION_NAME "Application"
,fltv.DESCRIPTION  "Description"
,decode(fltv.CUSTOMIZATION_LEVEL, 'U','User','S','System','E','Extensible')  "User"
,flv.LOOKUP_CODE  "Code"
,flv.MEANING  "Meaning"
,flv.DESCRIPTION  "Description"
,flv.TAG  "Tag"
,flv.START_DATE_ACTIVE  "From"
,flv.END_DATE_ACTIVE  "To"
,flv.ENABLED_FLAG "Enable"
FROM
APPS.FND_LOOKUP_TYPES_VL  fltv,
APPS.FND_APPLICATION_TL  fatl,
APPS.FND_LOOKUP_VALUES flv
WHERE 1=1
AND fltv.APPLICATION_ID=fatl.APPLICATION_ID
AND fatl.LANGUAGE='US'
AND fltv.LOOKUP_TYPE='JAI_REGISTRATION_TYPES'
AND fltv.LOOKUP_TYPE=flv.LOOKUP_TYPE
AND flv.LANGUAGE='US';

Oracle EBS GST-Tax Rates

Oracle EBS GST-Tax Rates
Configuration:Tax Rates,
Responsibility:Oracle Financials For India,
Navigation:Tax Configuration ->Define Tax Rate

Tables:-
JAI_TAX_RATES
JAI_REGIMES
hr_operating_units
jai_tax_accounts
JAI_TAX_TYPES
JAI_TAX_RATE_DETAILS
GL_SETS_OF_BOOKS

SQL Example:-

SELECT DISTINCT 
hou.name  "Operating Unit"
,jr.REGIME_CODE "Tax Regime Code"
,jr.REGIME_NAME  "Tax Regime Name"
,jtt.TAX_TYPE_CODE  "Tax Type Code"
,jtt.TAX_TYPE_NAME  "Tax Type"
,jtr.TAX_RATE_CODE  "Tax Rate type"
,jtr.TAX_RATE_NAME  "Tax Rate Name"
,jtr.TAX_RATE_TYPE  "Tax Rate Type"
,jtr.TAX_STATUS "Tax Status"
,jtr.RECOVERY_PERCENTAGE  "Recovery Rate"
,jtr.INCLUSIVE_FLAG "Inclusive"
,jtrd.TAX_RATE_PERCENTAGE "Rate Percentage"
,jtrd.TAX_RATE_UOM  "Unit of Measure"
,jtrd.TAX_RATE_UOM_RATE "Uom Rate"
,jtrd.EFFECTIVE_FROM  "Start Date"
,jtrd.EFFECTIVE_TO  "End Date"
,jtrd.TAX_RATE_CLASSIFICATION "Tax Rate Classification"
,jtrd.STANDARD_RATE_PERCENTAGE  "Original Rate"
,jtrd.FORM_TYPE "Form Type"
,jtrd.REMARKS "Remarks"
,gsob.NAME  "Ledger"
,hou1.name "Accounting Operating Unit"
,ood.ORGANIZATION_NAME  "Inventory Organization"
,hla.DESCRIPTION  "Location"
,gcck.CONCATENATED_SEGMENTS "Interim Recovery"
,gcck.CONCATENATED_SEGMENTS "Recovery"
,gcck1.CONCATENATED_SEGMENTS "Interim Liability"
,gcck1.CONCATENATED_SEGMENTS "Liability"
,gcck2.CONCATENATED_SEGMENTS "Expense"
,gcck3.CONCATENATED_SEGMENTS "Rounding Account"
,gcck4.CONCATENATED_SEGMENTS "Suspense Account"
,gcck5.CONCATENATED_SEGMENTS "Advanced Suspense"
,jra.REPORTING_TYPE_NAME  "Reporting Type Name"
,jra.REPORTING_CODE "Reporting Code"
,jra.REPORTING_CODE_DESCRIPTION "Reporting Code Description"
,jra.EFFECTIVE_FROM "Reporting Start Date"
,jra.EFFECTIVE_TO "Reporting End Date"
FROM
APPS.JAI_TAX_RATES jtr,
APPS.JAI_REGIMES jr,
APPS.hr_operating_units  hou,
APPS.jai_tax_accounts  jta,
APPS.JAI_TAX_TYPES  jtt,
APPS.JAI_TAX_RATE_DETAILS  jtrd,
APPS.GL_SETS_OF_BOOKS  gsob,
APPS.hr_operating_units  hou1,
APPS.org_organization_definitions  ood,
APPS.hr_locations_all  hla,
APPS.GL_CODE_COMBINATIONS_KFV gcck,
APPS.GL_CODE_COMBINATIONS_KFV gcck1,
APPS.GL_CODE_COMBINATIONS_KFV gcck2,
APPS.GL_CODE_COMBINATIONS_KFV gcck3,
APPS.GL_CODE_COMBINATIONS_KFV gcck4,
APPS.GL_CODE_COMBINATIONS_KFV gcck5,
APPS.jai_reporting_associations  jra
WHERE 1=1
AND jtr.REGIME_ID=jr.REGIME_ID
AND jtr.ORG_ID=hou.ORGANIZATION_ID
AND jtr.ORG_ID=jta.org_id
AND jtr.TAX_TYPE_ID=jta.TAX_ACCOUNT_ENTITY_ID
AND jr.regime_id=jtt.regime_id
AND jtr.TAX_RATE_ID=jtrd.TAX_RATE_ID
AND jta.LEDGER_ID=gsob.SET_OF_BOOKS_ID
AND jta.ORG_ID=hou1.ORGANIZATION_ID
AND jta.ORGANIZATION_ID=ood.ORGANIZATION_ID
AND jta.LOCATION_ID=hla.LOCATION_ID
AND jta.INTERIM_RECOVERY_CCID=gcck.CODE_COMBINATION_ID(+)
AND jta.RECOVERY_CCID=gcck.CODE_COMBINATION_ID(+)
AND jta.INTERIM_LIABILITY_CCID=gcck1.CODE_COMBINATION_ID(+)
AND jta.LIABILITY_CCID=gcck1.CODE_COMBINATION_ID(+)
AND jta.EXPENSE_CCID=gcck2.CODE_COMBINATION_ID(+)
AND jta.ROUNDING_CCID=gcck3.CODE_COMBINATION_ID(+)
AND jta.SUSPENSE_CCID=gcck4.CODE_COMBINATION_ID(+)
AND jta.ADVRCPT_SUSPENSE_CCID=gcck5.CODE_COMBINATION_ID(+)
AND jtr.TAX_TYPE_ID=jra.REPORTING_TYPE_ID(+)
AND jtr.REGIME_ID=jra.REPORTING_ASSOCIATION_ID(+)
AND jtr.TAX_TYPE_ID=jra.REGIME_ID(+)
AND hou.name= '&&';