JE Austrian VAT Reconciliation

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