JE Italian Payables Summary VAT
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
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 Name | SQL text | Validation | |
---|---|---|---|
Reporting Identifier | LOV Oracle | ||
Tax Calendar Period | LOV Oracle | ||
Variation on Sales | Number | ||
Variation on Purchase | Number |