JE UAE VAT

	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