JE Austrian VAT Reconciliation

Description
Categories: BI Publisher
Application: European Localizations
Source: Austrian VAT Reconciliation Report
Short Name: JEATVATD
DB package: JE_AT_VAT_UTIL_PKG
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
Chart Of Accounts Id
 
Number
Tax Rate Code
 
LOV Oracle
Tax Reporting Type Code
 
LOV Oracle
Tax Jurisdiction Code
 
LOV Oracle
Tax Status
 
LOV Oracle
Tax
 
LOV Oracle
Tax Regime Code
 
LOV Oracle
Period Name To
 
LOV Oracle
Period Name From
 
LOV Oracle
Reporting Entity Identifier
 
LOV Oracle
Tax Registration Number
 
LOV Oracle
JGZZ_BALANCING_SEGMENT
 
Legal Entity
 
LOV Oracle
Currency
 
LOV Oracle
Ledger
 
LOV Oracle
Ask a question