JE Israeli VAT AP Detail Register

Description
Categories: BI Publisher
Application: European Localizations
Source: Israeli VAT AP Detail Register Report
Short Name: JEILAPVR_XMLP
DB package: JG_ZZ_SUMMARY_AP_PKG
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
Calling Report
 
Tax Transaction Type
 
LOV Oracle
Tax Calendar Period
 
LOV Oracle
Reporting Identifier
 
LOV Oracle