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 |
