JE Austrian VAT Reconciliation
Description
Categories: BI Publisher
Imported from BI Publisher
Application: European Localizations
Source: Austrian VAT Reconciliation Report
Short Name: JEATVATD
DB package: JE_AT_VAT_UTIL_PKG
Application: European Localizations
Source: Austrian VAT Reconciliation Report
Short Name: JEATVATD
DB package: JE_AT_VAT_UTIL_PKG
Run
JE Austrian VAT Reconciliation and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT fndlk.meaning Source , jg.PRL_NO Tax_Class , jg.ACCOUNTING_DATE GL_Date , jg.TRX_TAX_NATURAL_ACCOUNT , jg.ACCOUNT_DESCRIPTION , jg.TAX_RATE_CODE , jg.trx_id ID , jgvrs.tax_calendar_period period_name , DECODE( jg.EXTRACT_SOURCE_LEDGER ,'GL', TO_CHAR(DOC_SEQ_VALUE) , jg.TRX_NUMBER ) Transaction_Number , DECODE (LEAD(tax_rate_code) OVER (PARTITION BY jg.TRX_ID ORDER BY jg.TRX_ID DESC NULLS LAST) ,NULL,'' ,tax_rate_code,'' ,'*') AS SPLIT , DECODE( jg.EXTRACT_SOURCE_LEDGER ,'AR', jg.TAX_LINE_NUMBER ,'GL', jg.TRX_LINE_NUMBER ) Line_Number , jg.TRX_CURRENCY_CODE currency , DECODE( jg.EXTRACT_SOURCE_LEDGER,'GL',NVL(jg.TAXABLE_AMT,0) , NVL(jg.TRX_LINE_AMT,0)) posted_amount , jg.currency_conversion_rate , DECODE( jg.currency_conversion_rate ,NULL, DECODE( jg.EXTRACT_SOURCE_LEDGER,'GL',NVL(jg.TAXABLE_AMT,0) , NVL(jg.TRX_LINE_AMT,0)) , DECODE( jg.EXTRACT_SOURCE_LEDGER,'GL',NVL(jg.TAXABLE_AMT,0) , NVL(jg.TRX_LINE_AMT,0)) * jg.currency_conversion_rate ) AS currency_amount_posted , NVL(jg.TAX_AMT,0) posted_VAT , DECODE( jg.currency_conversion_rate ,NULL, DECODE( jg.EXTRACT_SOURCE_LEDGER,'GL',NVL(jg.TAXABLE_AMT,0) , NVL(jg.TRX_LINE_AMT,0)) * jg.TAX_RATE / 100 , DECODE( jg.EXTRACT_SOURCE_LEDGER,'GL',NVL(jg.TAXABLE_AMT,0) , NVL(jg.TRX_LINE_AMT,0)) * jg.currency_conversion_rate *jg.TAX_RATE / 100) AS calculated_VAT , DECODE( jg.currency_conversion_rate ,NULL, ABS(ABS(NVL(jg.TAX_AMT,0) - (DECODE( jg.EXTRACT_SOURCE_LEDGER,'GL',NVL(jg.TAXABLE_AMT,0) , NVL(jg.TRX_LINE_AMT,0)) * jg.TAX_RATE / 100)) - 0.01) , ABS(ABS(NVL(jg.TAX_AMT,0) - (DECODE( jg.EXTRACT_SOURCE_LEDGER,'GL',NVL(jg.TAXABLE_AMT,0) , NVL(jg.TRX_LINE_AMT,0)) * jg.currency_conversion_rate *jg.TAX_RATE / 100))) - 0.01) AS Difference FROM jg_zz_vat_trx_details jg , jg_zz_vat_rep_status jgvrs , FND_LOOKUPS fndlk , jg_zz_vat_rep_entities jgve WHERE fndlk.LOOKUP_TYPE = 'JEAT_SOURCE' -- Bug 7293559 AND jg.trx_currency_code = :P_Currency AND jg.TRX_TAX_BALANCING_SEGMENT = :P_Balancing_Segment AND fndlk.LOOKUP_CODE = jg.EXTRACT_SOURCE_LEDGER AND jg.ACCOUNTING_DATE >= :start_date AND jg.ACCOUNTING_DATE <= :end_date AND jg.ledger_id =:p_ledger_id AND jg.TAX_REGIME_CODE = jgve.TAX_REGIME_CODE AND ( ( jg.TAX_RATE = NVL(:P_Tax , jg.TAX_RATE) ) OR ( jg.TAX_RATE IS NULL ) ) AND ( ( jg.TAX_STATUS_CODE = NVL(:P_Tax_Status , jg.TAX_STATUS_CODE) ) OR ( jg.TAX_STATUS_CODE IS NULL ) ) AND ( ( jg.TAX_JURISDICTION_CODE = NVL(:P_Tax_Jurisdiction, jg.TAX_JURISDICTION_CODE) ) OR ( jg.TAX_JURISDICTION_CODE IS NULL ) ) AND ( ( jg.TAX_TYPE_CODE = NVL(:P_Tax_Type_Code , jg.TAX_TYPE_CODE) ) OR ( jg.TAX_TYPE_CODE IS NULL ) ) AND ( ( jg.TAX_RATE_CODE = NVL(:P_Tax_Rate_Code , jg.TAX_RATE_CODE) ) OR ( jg.TAX_RATE_CODE IS NULL ) ) AND ( ( jgve.TAX_REGIME_CODE = NVL(:P_Tax_Regime_Code, jgve.TAX_REGIME_CODE) ) OR ( jgve.TAX_REGIME_CODE IS NULL ) ) AND jgvrs.reporting_status_id = jg.reporting_status_id AND jg.accounting_date >= period_start_date AND jg.accounting_date <= period_end_date AND jgvrs.tax_calendar_name = jgve.tax_calendar_name AND ( ( jgvrs.vat_reporting_entity_id = NVL(:P_Reporting_Entity_Identifier, jgvrs.vat_reporting_entity_id) ) OR ( jgvrs.vat_reporting_entity_id IS NULL ) ) AND ( ( APPLICATION_ID IN (222,101) AND jg.accounting_date is not null ) OR ( APPLICATION_ID = 200 AND jg.POSTED_FLAG = 'A' ) ) ORDER BY period_name , tax_rate_code, trx_tax_natural_account |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Ledger |
|
LOV Oracle | |
Currency |
|
LOV Oracle | |
Legal Entity |
|
LOV Oracle | |
JGZZ_BALANCING_SEGMENT |
|
Char | |
Tax Registration Number |
|
LOV Oracle | |
Reporting Entity Identifier |
|
LOV Oracle | |
Period Name From |
|
LOV Oracle | |
Period Name To |
|
LOV Oracle | |
Tax Regime Code |
|
LOV Oracle | |
Tax |
|
LOV Oracle | |
Tax Status |
|
LOV Oracle | |
Tax Jurisdiction Code |
|
LOV Oracle | |
Tax Reporting Type Code |
|
LOV Oracle | |
Tax Rate Code |
|
LOV Oracle |