JE Italian Payables Summary VAT

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Application: European Localizations
Source: Italian Payables Summary VAT Report
Short Name: JEITPSSR_XMLP
DB package: jg_zz_summary_all_pkg
Run JE Italian Payables Summary VAT and other Oracle EBS reports with Blitz Report™ on our demo environment
	SELECT   CASE
    WHEN ( NVL(PVS.GLOBAL_ATTRIBUTE15,'x') IN ('9','92','93')
    or NVL(PVSS.GLOBAL_ATTRIBUTE15,'x') IN ('9','92','93') )
    AND SUM(NVL(ABS(JZVTD.TAX_AMT_FUNCL_CURR),ABS(JZVTD.TAX_AMT))) >=1
    THEN 1
    WHEN ( NVL(PVS.GLOBAL_ATTRIBUTE15,'x') NOT IN ('9','92','93')
     AND NVL(PVSS.GLOBAL_ATTRIBUTE15,'x') NOT IN ('9','92','93') )
    THEN 1
  END COUNT_INV,
  JZVTD.TRX_ID INV_ID,
	  DECODE(JZVTD.REPORTING_CODE, 'VAT-RA',NVL(apinvl.GLOBAL_ATTRIBUTE14,apinv.invoice_num),'VAT-RS',NVL(apinvl.GLOBAL_ATTRIBUTE14,apinv.invoice_num),NULL) IMPORT_NUMBER,	
	  SUM(
	   CASE 
	   WHEN JZVTD.REPORTING_CODE IN ('VAT-A','VAT-RA') THEN 
			NVL(JZVTD.TAX_AMT_FUNCL_CURR,   JZVTD.TAX_AMT)
	   ELSE 0
	   END)  VAT_ON_FA,
	  SUM(
	   CASE 
	   WHEN JZVTD.REPORTING_CODE IN ('VAT-S','VAT-RS','VAT-C','VAT-P','VAT-H') THEN 
			NVL(JZVTD.TAX_AMT_FUNCL_CURR,   JZVTD.TAX_AMT)
	   ELSE 0
	   END)  VAT_ON_OTHER_TRX,
	  0 EXCEEDED_VAT_ON_FA,
  	  0 EXCEEDED_VAT_ON_OTHER_TRX,
	    PVS.GLOBAL_ATTRIBUTE15,
        PVSS.GLOBAL_ATTRIBUTE15
	FROM JG_ZZ_VAT_TRX_DETAILS JZVTD,
	     JG_ZZ_VAT_REP_STATUS JZVRS,
         ap_invoices_all  apinv,
         ap_invoice_lines_all apinvl,
		 AP_SUPPLIERS PVS,
         AP_SUPPLIER_SITES_ALL PVSS,
         zx_lines zxl
	WHERE JZVRS.REPORTING_STATUS_ID = JZVTD.REPORTING_STATUS_ID
	 AND :P_REPORT_NAME = 'JEILSVAT'
	 AND JZVRS.SOURCE = 'AP'
	 AND JZVRS.VAT_REPORTING_ENTITY_ID = :P_VAT_REP_ENTITY_ID
	 AND JZVRS.TAX_CALENDAR_PERIOD = :P_PERIOD
	 AND JZVTD.TAX_RATE_REGISTER_TYPE_CODE = 'TAX'
	 AND(:P_VAT_TRX_TYPE IS NULL OR JZVTD.TAX_RATE_VAT_TRX_TYPE_CODE = :P_VAT_TRX_TYPE)
	 AND JZVTD.REPORTING_CODE IN('VAT-A','VAT-S','VAT-RA','VAT-RS','VAT-C','VAT-P','VAT-H')
         AND apinv.invoice_id = jzvtd.trx_id
		 AND APINV.VENDOR_ID         =PVS.VENDOR_ID
         AND APINV.VENDOR_SITE_ID    =PVSS.VENDOR_SITE_ID
         AND PVS.VENDOR_ID           =PVSS.VENDOR_ID
         AND zxl.trx_id = apinv.invoice_id          
         AND zxl.trx_id = apinvl.invoice_id(+) 
         AND zxl.tax_line_id =jzvtd.tax_line_id
         AND zxl.summary_tax_line_id = apinvl.summary_tax_line_id(+)         
	GROUP BY JZVTD.REPORTING_CODE, 
        JZVTD.TRX_ID,
        DECODE(JZVTD.REPORTING_CODE, 'VAT-RA',NVL(apinvl.GLOBAL_ATTRIBUTE14,apinv.invoice_num),'VAT-RS',NVL(apinvl.GLOBAL_ATTRIBUTE14,apinv.invoice_num),NULL)
		,PVS.GLOBAL_ATTRIBUTE15
        ,PVSS.GLOBAL_ATTRIBUTE15
	UNION ALL
	SELECT   CASE
    WHEN ( NVL(PVS.GLOBAL_ATTRIBUTE15,'x') IN ('9','92','93')
    or NVL(PVSS.GLOBAL_ATTRIBUTE15,'x') IN ('9','92','93') )
    AND SUM(NVL(ABS(JZVTD.TAX_AMT_FUNCL_CURR),ABS(JZVTD.TAX_AMT))) >=1
    THEN 1
    WHEN ( NVL(PVS.GLOBAL_ATTRIBUTE15,'x') NOT IN ('9','92','93')
     AND NVL(PVSS.GLOBAL_ATTRIBUTE15,'x') NOT IN ('9','92','93') )
    THEN 1
  END COUNT_INV,
  JZVTD.TRX_ID INV_ID, NULL IMPORT_NUMBER,
	  SUM(DECODE(JZVTD.REPORTING_CODE,'VAT-KA',NVL(JZVTD.TAX_AMT_FUNCL_CURR,   JZVTD.TAX_AMT),0)) VAT_ON_FA,
	  SUM(DECODE(JZVTD.REPORTING_CODE,'VAT-KS',NVL(JZVTD.TAX_AMT_FUNCL_CURR,   JZVTD.TAX_AMT),0)) VAT_ON_OTHER_TRX,
	  0 EXCEEDED_VAT_ON_FA,
  	  0 EXCEEDED_VAT_ON_OTHER_TRX
	  ,PVS.GLOBAL_ATTRIBUTE15
      ,PVSS.GLOBAL_ATTRIBUTE15
	FROM JG_ZZ_VAT_TRX_DETAILS JZVTD,
	  JG_ZZ_VAT_REP_STATUS JZVRS
	     ,ap_invoices_all  apinv
		 ,AP_SUPPLIERS PVS
         ,AP_SUPPLIER_SITES_ALL PVSS
	WHERE :P_REPORT_NAME = 'JEILSVAT'
	 AND JZVRS.SOURCE = 'AP'
	 AND JZVRS.VAT_REPORTING_ENTITY_ID = :P_VAT_REP_ENTITY_ID
	 AND JZVTD.IL_VAT_REP_STATUS_ID = &l_vat_rep_status_id
	 AND JZVRS.REPORTING_STATUS_ID = JZVTD.REPORTING_STATUS_ID
         AND JZVRS.PERIOD_END_DATE <= (SELECT period_end_date  --bug 12606816
                                         FROM jg_zz_vat_rep_status jgzz
                                        WHERE jgzz.vat_reporting_entity_id = :P_VAT_REP_ENTITY_ID
                                          AND jgzz.source = 'AP'
                                          AND jgzz.tax_calendar_period = :P_PERIOD)
	 AND JZVTD.TAX_RATE_REGISTER_TYPE_CODE = 'TAX'
	 AND(:P_VAT_TRX_TYPE IS NULL OR JZVTD.TAX_RATE_VAT_TRX_TYPE_CODE = :P_VAT_TRX_TYPE)
	 AND JZVTD.REPORTING_CODE IN ('VAT-KS','VAT-KA')
	 AND apinv.invoice_id = jzvtd.trx_id
	 AND APINV.VENDOR_ID         =PVS.VENDOR_ID
     AND APINV.VENDOR_SITE_ID    =PVSS.VENDOR_SITE_ID
     AND PVS.VENDOR_ID           =PVSS.VENDOR_ID
	GROUP BY JZVTD.REPORTING_CODE, JZVTD.TRX_ID
	,PVS.GLOBAL_ATTRIBUTE15
    ,PVSS.GLOBAL_ATTRIBUTE15
	UNION ALL	
	SELECT 
	   CASE
    WHEN ( NVL(PVS.GLOBAL_ATTRIBUTE15,'x') IN ('9','92','93')
    or NVL(PVSS.GLOBAL_ATTRIBUTE15,'x') IN ('9','92','93') )
    AND SUM(NVL(ABS(JZVTD.TAX_AMT_FUNCL_CURR),ABS(JZVTD.TAX_AMT))) >=1
    THEN 1
    WHEN ( NVL(PVS.GLOBAL_ATTRIBUTE15,'x') NOT IN ('9','92','93')
     AND NVL(PVSS.GLOBAL_ATTRIBUTE15,'x') NOT IN ('9','92','93') )
    THEN 1
    END COUNT_INV,
   JZVTD.TRX_ID INV_ID,NULL IMPORT_NUMBER,
	  0 VAT_ON_FA,
	  0 VAT_ON_OTHER_TRX,
	  SUM(DECODE(JZVTD.REPORTING_CODE,'VAT-KA',NVL(JZVTD.TAX_AMT_FUNCL_CURR,JZVTD.TAX_AMT),0)) EXCEEDED_VAT_ON_FA,
  	  SUM(DECODE(JZVTD.REPORTING_CODE,'VAT-KS',NVL(JZVTD.TAX_AMT_FUNCL_CURR,JZVTD.TAX_AMT),0)) EXCEEDED_VAT_ON_OTHER_TRX
	  	,PVS.GLOBAL_ATTRIBUTE15
    ,PVSS.GLOBAL_ATTRIBUTE15
	FROM JG_ZZ_VAT_TRX_DETAILS JZVTD,
	  JG_ZZ_VAT_REP_STATUS JZVRS
	 ,ap_invoices_all  apinv
	 ,AP_SUPPLIERS PVS
     ,AP_SUPPLIER_SITES_ALL PVSS
	WHERE :P_REPORT_NAME = 'JEILSVAT'
	 AND JZVRS.SOURCE = 'AP'
	 AND JZVRS.VAT_REPORTING_ENTITY_ID = :P_VAT_REP_ENTITY_ID
	 AND JZVTD.IL_VAT_REP_STATUS_ID = -999
	 AND JZVRS.REPORTING_STATUS_ID = JZVTD.REPORTING_STATUS_ID
     AND JZVRS.PERIOD_END_DATE <= (SELECT period_end_date  --bug 12606816
                                         FROM jg_zz_vat_rep_status jgzz
                                        WHERE jgzz.vat_reporting_entity_id = :P_VAT_REP_ENTITY_ID
                                          AND jgzz.source = 'AP'
                                          AND jgzz.tax_calendar_period = :P_PERIOD)
	 AND JZVTD.TAX_RATE_REGISTER_TYPE_CODE = 'TAX'
	 AND(:P_VAT_TRX_TYPE IS NULL OR JZVTD.TAX_RATE_VAT_TRX_TYPE_CODE = :P_VAT_TRX_TYPE)
	 AND JZVTD.REPORTING_CODE IN ('VAT-KS','VAT-KA')
	 AND apinv.invoice_id = jzvtd.trx_id
	 AND APINV.VENDOR_ID         =PVS.VENDOR_ID
     AND APINV.VENDOR_SITE_ID    =PVSS.VENDOR_SITE_ID
     AND PVS.VENDOR_ID           =PVSS.VENDOR_ID
	GROUP BY JZVTD.REPORTING_CODE,JZVTD.TRX_ID
    ,PVS.GLOBAL_ATTRIBUTE15
    ,PVSS.GLOBAL_ATTRIBUTE15
Parameter Name SQL text Validation
Reporting Identifier 
 
 LOV Oracle
Tax Calendar Period 
 
 LOV Oracle
Variation on Sales 
 
 Number
Variation on Purchase 
 
 Number
Download
Related Blitz Reports
JE Israeli Summary VAT to Tax Authorities JE Israeli VAT File Related to 835 JE Belgian VAT Monthly VAT Preparation JG EMEA VAT: Summary All Extract JE Portuguese Annual VAT JE UAE VAT JE Portuguese Periodic VAT JE Spanish Inter-EU Invoice Format (Documento Equivalente) JE Czech Import Tax
Blitz Report™

Blitz Report™ provides multiple benefits: