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 |
