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
	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 NameSQL textValidation
Reporting Identifier
 
LOV Oracle
Tax Calendar Period
 
LOV Oracle
Variation on Sales
 
Number
Variation on Purchase
 
Number
Download
Related Blitz Reports
JE UAE VAT JG EMEA VAT: Summary All Extract JE Portuguese Periodic VAT JE Portuguese Annual VAT JE Israeli Summary VAT to Tax Authorities JE Israeli VAT File Related to 835 JE Belgian VAT Monthly VAT Preparation JE Swiss Payables VAT JE Czech Import Tax
Blitz Report™