SELECT jgvtd.trx_id trx_id
,jgvtd.trx_number trx_number
,jgvtd.tax_rate_code tax_rate_code
,jgvtd.tax_rate_code_description tax_rate_code_description
,jgvtd.tax_rate tax_rate
,jgvtd.tax_rate_register_type_code tax_rate_register_type_code
,jgvtd.trx_class_mng trx_class_mng
,jgvtd.tax_recovery_rate tax_recovery_rate
,jgvtd.offset_tax_rate_code offset_tax_rate_code
,jgvrs.source source
,jgvtd.tax_type_code tax_type_code
,decode(jgvtd.extract_source_ledger,'AP',decode(jgvtd.tax_amt_included_flag,'N',jgvtd.orig_taxable_amt_tax_curr,jgvtd.TAXABLE_AMT_FUNCL_CURR),
jgvtd.taxable_amt_funcl_curr) taxable_amt_funcl_curr
,jgvtd.tax_amt_funcl_curr tax_amt_funcl_curr
,decode(jgvtd.tax_recoverable_flag, 'Y', (decode(jgvtd.tax_amt_included_flag,'N',jgvtd.orig_taxable_amt_tax_curr,jgvtd.TAXABLE_AMT_FUNCL_CURR) * jgvtd.tax_recovery_rate/100),0) taxable_rec
,decode(jgvtd.tax_recoverable_flag, 'N', (decode(jgvtd.tax_amt_included_flag,'N',jgvtd.orig_taxable_amt_tax_curr,jgvtd.TAXABLE_AMT_FUNCL_CURR) * jgvtd.tax_recovery_rate/100),0) taxable_non_rec
,decode(jgvtd.tax_type_code, 'I', decode(jgvtd.tax_amt_included_flag,'N',jgvtd.orig_taxable_amt_tax_curr,jgvtd.TAXABLE_AMT_FUNCL_CURR),0) taxable_debit
,decode(jgvtd.tax_type_code, 'O', decode(jgvtd.tax_amt_included_flag,'N',jgvtd.orig_taxable_amt_tax_curr,jgvtd.TAXABLE_AMT_FUNCL_CURR),0) taxable_credit
,decode(jgvtd.tax_recoverable_flag, 'Y', jgvtd.tax_amt_funcl_curr,0) tax_rec
,decode(jgvtd.tax_recoverable_flag, 'N', jgvtd.tax_amt_funcl_curr,0) tax_non_rec
,decode(jgvtd.tax_type_code, 'I', jgvtd.tax_amt_funcl_curr,0) tax_debit
,decode(jgvtd.tax_type_code, 'O', jgvtd.tax_amt_funcl_curr,0) tax_credit
,jgvtd.nrec_tax_amt_tax_curr nrec_tax_amt_tax_curr
,jgvtd.tax_rate_vat_trx_type_desc tax_rate_vat_trx_type_desc
,jgvtd.reporting_code reporting_code
,jgvtd.functional_currency_code functional_currency_code
,jgvtd.tax_recoverable_flag tax_recoverable_flag
,jgvrs.tax_calendar_period tax_calendar_period
,jgvtd.application_id application_id
,FND_PROFILE.value('JEIT_EXEMPT_TAX_TAG') exempt_value
,glp.period_num period_num
FROM jg_zz_vat_trx_details jgvtd
,jg_zz_vat_rep_entities jgvre
,jg_zz_vat_rep_status jgvrs
,gl_periods glp
WHERE jgvrs.reporting_status_id = jgvtd.reporting_status_id (+)
AND jgvrs.vat_reporting_entity_id = jgvre.VAT_REPORTING_ENTITY_ID
AND jgvre.vat_reporting_entity_id = :P_REPORTING_ENTITY_ID
AND jgvrs.final_reporting_status_flag = 'S'
AND jgvrs.tax_calendar_year = glp.period_year
AND jgvrs.tax_calendar_name = glp.period_set_name
AND jgvrs.tax_calendar_period = glp.period_name
AND jgvrs.tax_calendar_year = :P_TAX_CALENDAR_YEAR
ORDER BY jgvtd.application_id DESC NULLS LAST |
