JG EMEA VAT: Yearly Extract

Description
Categories: BI Publisher
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
Report Submission Mode
 
LOV Oracle
VAT Calendar Year
 
LOV Oracle
Reporting Identifier
 
LOV Oracle