JG ECE Receivables VAT Register
Description
- Categories
- BI Publisher
- Application
- Regional Localizations
- Source
- ECE Receivables VAT Register
- Short Name
- JGZZARVR_XMLP
- DB package
- JG_ZZ_SUMMARY_AR_PKG
Imported from BI Publisher
ECE Receivables VAT Register Report
ECE Receivables VAT Register Report
SQL 133 lines
SELECT JZVTD.reporting_code REPORTING_CODE ,JZVTD.trx_id INVOICE_ID ,DECODE(JZVTD.trx_line_class ,'ADJUSTMENT',JZVTD.applied_to_trx_number , JZVTD.trx_number) INVOICE_NUMBER ,NULL EXPORT_NUMBER ,TO_CHAR(JZVTD.trx_date,'DD-Mon-RRRR') INVOICE_DATE ,JZVTD.trx_line_class TRX_LINE_CLASS_CODE ,JZVTD.tax_rate_code_vat_trx_type_mng VAT_CODE ,JZVTD.tax_rate_vat_trx_type_desc VAT_DESC ,JZVTD.tax_rate_vat_trx_type_code VAT_TYPE ,JZVTD.posted_flag POSTED_FLAG ,SUM(NVL( JZVTD.tax_amt_funcl_curr,JZVTD.tax_amt) + NVL( JZVTD.taxable_amt_funcl_curr,JZVTD.TAXABLE_AMT) ) TRX_AMOUNT ,SUM(NVL(JZVTD.tax_amt_funcl_curr,0) + NVL(JZVTD.taxable_amt_funcl_curr,0) ) FUNC_AMOUNT ,SUM(DECODE(JZVTD.reporting_code,'IL_VAT_EXEMPT', 0 ,'VAT-EXEMPT',0 ,'VAT-0',0 ,(DECODE(JZVTD.TAX_RATE,0,0, NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt))) ) ) TAXABLE_AMOUNT ,SUM(DECODE(JZVTD.reporting_code,'IL_VAT_EXEMPT', 0 ,'VAT-EXEMPT',0 ,'VAT-0',0 ,(DECODE(JZVTD.TAX_RATE,0,0, NVL( JZVTD.tax_amt_funcl_curr,JZVTD.tax_amt))) ) ) TAX_AMOUNT ,SUM((DECODE(JZVTD.reporting_code,'IL_VAT_EXEMPT',NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt) ,'VAT-EXEMPT',NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt) ,'VAT-0',NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt) ,DECODE(JZVTD.tax_rate,0,NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt),0) ) ) ) EXEMPT_AMOUNT ,SUM(DECODE(JZVTD.reporting_code,'IL_VAT_EXEMPT', 0 ,'VAT-EXEMPT',0 ,'VAT-0',0 ,(DECODE(JZVTD.TAX_RATE,0,0, NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt))) ) ) + SUM(DECODE(JZVTD.reporting_code,'IL_VAT_EXEMPT', 0 ,'VAT-EXEMPT',0 ,'VAT-0',0 ,(DECODE(JZVTD.TAX_RATE,0,0, NVL( JZVTD.tax_amt_funcl_curr,JZVTD.tax_amt))) ) ) + SUM((DECODE(JZVTD.reporting_code,'IL_VAT_EXEMPT',NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt) ,'VAT-EXEMPT',NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt) ,'VAT-0',NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt) ,DECODE(JZVTD.tax_rate,0,NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt),0) ) ) ) DERIVED_TOT_AMT -- bug 22480577 ,NVL(JZVTD.billing_tp_taxpayer_id,'N') TAX_PAY_ID FROM JG_ZZ_VAT_TRX_DETAILS JZVTD ,JG_ZZ_VAT_REP_STATUS JZVRS WHERE :P_CALLINGREPORT = 'JEILARDR' AND JZVRS.vat_reporting_entity_id = :P_VAT_REP_ENTITY_ID AND JZVTD.reporting_status_id = JZVRS.REPORTING_STATUS_ID AND JZVRS.tax_calendar_period = :P_PERIOD AND (JZVTD.tax_rate_vat_trx_type_code = :P_VAT_TRX_TYPE OR :P_VAT_TRX_TYPE IS NULL) AND JZVRS.source = 'AR' AND JZVTD.reporting_code NOT IN ('VAT-NO-REP','VAT-Y') GROUP BY JZVTD.reporting_code ,JZVTD.trx_id ,DECODE(JZVTD.trx_line_class ,'ADJUSTMENT',JZVTD.applied_to_trx_number , JZVTD.trx_number) ,JZVTD.trx_date ,JZVTD.trx_line_class ,JZVTD.tax_rate_code_vat_trx_type_mng ,JZVTD.tax_rate_vat_trx_type_desc ,JZVTD.tax_rate_vat_trx_type_code ,JZVTD.posted_flag ,NVL(JZVTD.billing_tp_taxpayer_id,'N') UNION ALL SELECT JZVTD.reporting_code REPORTING_CODE ,JZVTD.trx_id INVOICE_ID ,DECODE(JZVTD.trx_line_class ,'ADJUSTMENT',JZVTD.applied_to_trx_number , JZVTD.trx_number) INVOICE_NUMBER ,NVL(RCTLA.global_attribute1,'999999999') EXPORT_NUMBER ,TO_CHAR(DECODE(LENGTH(NVL(RCTLA.global_attribute2,'A')), 1,JZVTD.TRX_DATE, 19,TO_DATE(RCTLA.global_attribute2,'RRRR/MM/DD HH24:MI:SS'), TO_DATE(RCTLA.global_attribute2, 'DD-MM-RRRR') ),'DD-Mon-RRRR') INVOICE_DATE ,JZVTD.trx_line_class TRX_LINE_CLASS_CODE ,JZVTD.tax_rate_code_vat_trx_type_mng VAT_CODE ,JZVTD.tax_rate_vat_trx_type_desc VAT_DESC ,JZVTD.tax_rate_vat_trx_type_code VAT_TYPE ,JZVTD.posted_flag POSTED_FLAG ,SUM(NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt)+ NVL( JZVTD.tax_amt_funcl_curr,JZVTD.tax_amt) ) TRX_AMOUNT ,SUM(NVL(JZVTD.tax_amt_funcl_curr,0) + NVL(JZVTD.taxable_amt_funcl_curr,0) ) FUNC_AMOUNT ,0 TAXABLE_AMOUNT ,0 TAX_AMOUNT ,SUM(NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt)) EXEMPT_AMOUNT ,SUM(NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt)) DERIVED_TOT_AMT -- bug22480577 ,'N/A' TAX_PAY_ID FROM JG_ZZ_VAT_TRX_DETAILS JZVTD ,JG_ZZ_VAT_REP_STATUS JZVRS ,RA_CUSTOMER_TRX_ALL RCTA ,RA_CUSTOMER_TRX_LINES_ALL RCTLA WHERE :P_CALLINGREPORT = 'JEILARDR' AND JZVRS.vat_reporting_entity_id = :P_VAT_REP_ENTITY_ID AND JZVTD.reporting_status_id = JZVRS.REPORTING_STATUS_ID AND RCTA.customer_trx_id = RCTLA.customer_trx_id AND RCTA.customer_trx_id = JZVTD.trx_id AND RCTLA.customer_trx_line_id = JZVTD.trx_line_id AND JZVRS.tax_calendar_period = :P_PERIOD AND (JZVTD.tax_rate_vat_trx_type_code = :P_VAT_TRX_TYPE OR :p_vat_trx_type IS NULL) AND JZVRS.source = 'AR' AND JZVTD.reporting_code = 'VAT-Y' GROUP BY JZVTD.reporting_code ,JZVTD.trx_id ,DECODE(JZVTD.trx_line_class ,'ADJUSTMENT',JZVTD.applied_to_trx_number , JZVTD.trx_number) ,NVL(RCTLA.global_attribute1,'999999999') ,DECODE(LENGTH(NVL(RCTLA.global_attribute2,'A')), 1,JZVTD.TRX_DATE, 19,TO_DATE(RCTLA.global_attribute2,'RRRR/MM/DD HH24:MI:SS'), TO_DATE(RCTLA.global_attribute2, 'DD-MM-RRRR')) ,JZVTD.trx_line_class ,JZVTD.tax_rate_code_vat_trx_type_mng ,JZVTD.tax_rate_vat_trx_type_desc ,JZVTD.tax_rate_vat_trx_type_code ,JZVTD.posted_flag ORDER BY 6,1,4 |
Parameters 4
| Parameter | Type | List of values SQL |
|---|---|---|
| Reporting Identifier | LOV Oracle | |
| Tax Calendar Period | LOV Oracle | |
| VAT Transaction Type | LOV Oracle | |
| Exclude VAT Transaction Type | LOV Oracle |