JG EMEA VAT: Summary AP Extract

➜ Back to Library
Description
Categories: BI Publisher
Application: Regional Localizations
Source: EMEA VAT: Summary AP Extract
Short Name: JGZZSUMMARYAP_XMLP
DB package: JG_ZZ_SUMMARY_AP_PKG
Run JG EMEA VAT: Summary AP Extract and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT GLP.period_name                      period_name
       ,GLP.period_num		         period_num
       ,GLP.period_year			    period_year
      ,JG_INFO_V22                           doc_sequence_value
      ,TO_CHAR(jg_info_d1,'DD-Mon-RRRR')    tax_date
      ,TO_CHAR(jg_info_d3,'DD-Mon-RRRR')    accounting_date
      ,TO_CHAR(jg_info_d2,'DD-Mon-RRRR')    invoice_date
      ,jg_info_v1                           invoice_num
      ,jg_info_v2                           vendor_name
      ,jg_info_n3                           tax_rate
      ,jg_info_v3                           name
      ,jg_info_n5                           invoice_id
      ,jg_info_v4                           invoice_type
      ,jg_info_v5                           currency
      ,jg_info_v6                           tax_code_desc
      ,SUM(jg_info_n2)                      taxable_amount
      ,SUM(jg_info_n4)                      tax
      ,SUM(jg_info_n6)                      rec_tax
      ,SUM(jg_info_n7)                      non_rec_tax
      ,SUM(jg_info_n8)                      functional_amount
      ,SUM(jg_info_n10)                     Transaction_amount
      ,SUM(jg_info_n13)                     Taxable_entered_amount
      ,jg_info_n9                           acct_flexfield
      ,jg_info_v7                           tax_code_vat_trx_type
      ,jg_info_v8                           merchant_number
      ,jg_info_v10                          doc_seq_name
      ,jg_info_v11                          tax_reg_num
      ,jg_info_v13                          tax_code_vat_trx_type_meaning
      ,jg_info_v12                          tax_code_vat_trx_type_desc
      ,jg_info_v14                          tax_code
      /* UT TESTING ,jg_info_n11 . No value is getting populated into this field */
      ,decode(jg_info_v15, 'OFFSET','XOFFSET','VAT')  tax_code_type_code1
      ,JG_ZZ_SUMMARY_AP_PKG.CF_Tax_Code_Type_Code(decode(jg_info_v15, 'OFFSET','XOFFSET','VAT')) Tax_Code_Type_Cod_Is_Offset
      ,decode(jg_info_v15, 'OFFSET','XOFFSET','VAT') tax_code_type_code2
      ,jg_info_n14                          non_rec_unpaid_amt
      ,jg_info_v17                          offset_flag
      ,jg_info_v18                          offset_tax_rate_code
      ,SUM(DECODE(jg_info_v17,'Y', jg_info_n13 , DECODE(jg_info_v15, 'OFFSET', jg_info_n13, DECODE(jg_info_n15,-9999,jg_info_n13,jg_info_n10) )))  CF_trans_VAT_or_Offset
      ,SUM(DECODE(jg_info_v17,'Y',jg_info_n2, DECODE(jg_info_v15, 'OFFSET',jg_info_n2, DECODE(jg_info_n15,-9999,jg_info_n2,jg_info_n8) )))  CF_func_VAT_or_Offset
  FROM JG_ZZ_VAT_TRX_GT
      ,GL_PERIODS GLP
	  ,jg_zz_vat_rep_status JZVRS
 WHERE jg_info_v9 = 'M'
 AND JZVRS.vat_reporting_entity_id = :P_VAT_REPORTING_ENTITY_ID
 AND jzvrs.tax_calendar_period = :P_TAX_CALENDAR_PERIOD
 AND jg_info_v16 = jzvrs.tax_calendar_period 
 AND GLP.period_set_name = jzvrs.tax_calendar_name
 AND GLP.adjustment_period_flag = 'N' /* Added for bug 5915657 */
 AND jg_info_d1 between glp.start_date and glp.end_date
 AND jzvrs.source = 'AP'
 AND ((:P_CALLING_REPORT = 'JGZZAPVR' ) OR :P_CALLING_REPORT = 'JEHRVITR')   /* UT TEST */
 AND   NVL(jg_info_v30,'#') <> 'H'
 GROUP BY decode(jg_info_v15, 'OFFSET','XOFFSET','VAT')  /* tax_code_type_code1 */
      	 ,period_num
		 ,period_year
         ,period_name
         ,jg_info_v12  /*tax_code_vat_trx_type_desc*/
         ,jg_info_v13  /*tax_code_vat_trx_type_meaning*/
         ,jg_info_v14  /* tax_code */
         ,jg_info_v6    /* tax_code_desc */
         ,jg_info_v10  /* doc_seq_name */
         ,JG_INFO_V22   /* doc sequence value */
         ,jg_info_v4   /* invoice type */
         ,jg_info_n5   /* invoice id */
         ,jg_info_v5   /* currency */
         ,jg_info_d1   /* tax date */
         ,jg_info_d3   /*accounting date */
         ,jg_info_d2   /* invoice date */
         ,jg_info_v1   /* invoice num */
         ,jg_info_v2   /* vendor name */
         ,jg_info_n3    /* tax rate */
         ,jg_info_v3   /* name */
         -- commented for bug 6884757,jg_info_n2   /* taxable_amount */
         ,jg_info_n9   /*acct_flexfield*/
         ,jg_info_v7   /*tax_code_vat_trx_type */
         ,jg_info_v8   /*merchant_number*/
     /* UT Bug#5258868 ,jg_info_n11 */
         ,jg_info_v11  /*tax_reg_num */
     /* UT Bug#5258868  ,jg_info_n11 */
         ,jg_info_n14   /* non_rec_unpaid_amt */
         ,JG_ZZ_SUMMARY_AP_PKG.CF_Tax_Code_Type_Code(decode(jg_info_v15, 'OFFSET','XOFFSET','VAT'))
         ,decode(jg_info_v15, 'OFFSET','XOFFSET','VAT')
         ,jg_info_v17   /* offset_flag */
         ,jg_info_v18   /*offset_tax_rate_code*/
 ORDER BY tax_code_type_code1
         ,Tax_Code_Type_Cod_Is_Offset
         ,period_year desc
		 ,period_num desc
		 ,jg_info_v7
		 ,tax_code_vat_trx_type_meaning
         ,tax_code_vat_trx_type_desc
         ,tax_code_desc
         ,tax_code
         ,tax_date
         ,doc_sequence_value
         ,invoice_num
Parameter Name SQL text Validation
Reporting Identifier 
 
 LOV Oracle
Tax Calendar Period 
 
 LOV Oracle
Download
Related Blitz Reports
JG ECE Payables VAT Register JE Israeli VAT File Non Related to 835 JE Israeli VAT AP Detail Register JE Croatian Supplier Invoice Tax JE Italian Annual Supplier Listing JE Portuguese Suppliers Recapitulative JG EMEA VAT: Turnover AP Non TRL Extract JG EMEA VAT: Audit GL Extract JG EMEA VAT: Yearly Extract
Blitz Report™

Blitz Report™ provides multiple benefits: