JG EMEA VAT: Yearly Extract

Description
Categories: BI Publisher
Columns: Trx Id, Trx Number, Tax Rate Code, Tax Rate Code Description, Tax Rate, Tax Rate Register Type Code, Trx Class Mng, Tax Recovery Rate, Offset Tax Rate Code, Source ...
Application: Regional Localizations
Source: EMEA VAT: Yearly Extract Report
Short Name: JGZZYEXR
DB package: JG_ZZ_VAT_YEARLY_EXT_PKG
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
Parameter Name SQL text Validation
Reporting Identifier
 
LOV Oracle
VAT Calendar Year
 
LOV Oracle
Report Submission Mode
 
LOV Oracle