JE German VAT for On-Account Receipts
Description
Categories: BI Publisher
Imported from BI Publisher
Description: German VAT for On-Account Receipts Report
Application: European Localizations
Source: German VAT for On-Account Receipts
Short Name: JEDEDVOR_XMLP
DB package: JG_ZZ_SUMMARY_AR_PKG
Description: German VAT for On-Account Receipts Report
Application: European Localizations
Source: German VAT for On-Account Receipts
Short Name: JEDEDVOR_XMLP
DB package: JG_ZZ_SUMMARY_AR_PKG
Run
JE German VAT for On-Account Receipts and other Oracle EBS reports with Blitz Report™ on our demo environment
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 |
| Parameter Name | SQL text | Validation | |
|---|---|---|---|
| Reporting Level |
|
LOV Oracle | |
| Legal Entity |
|
LOV Oracle | |
| Ledger |
|
LOV Oracle | |
| Balancing Segment |
|
Char | |
| Regime Code |
|
LOV Oracle | |
| Tax |
|
LOV Oracle | |
| Tax Status |
|
LOV Oracle | |
| Jurisdiction |
|
LOV Oracle | |
| Tax Rate |
|
LOV Oracle | |
| GL Start Date |
|
Date | |
| GL End Date |
|
Date |
