JE Portuguese Annual VAT

Description
Categories: BI Publisher
Application: European Localizations
Source: Portuguese Annual VAT Report
Short Name: JEPTAVAT_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 Name SQL text Validation
Calling Report
 
Portuguese Tax Locations
 
LOV Oracle
VAT Report Box High
 
Number
VAT Report Box Low
 
Number
Tax Calendar Year
 
LOV Oracle
Reporting Identifier
 
LOV Oracle