JE Online VAT Register Reporting for Spain
Description
Categories: BI Publisher
Application: European Localizations
Source: Online VAT Register Reporting for Spain
Short Name: JEESOVRR_XMLP
DB package: je_es_online_vat_ext_pkg
Source: Online VAT Register Reporting for Spain
Short Name: JEESOVRR_XMLP
DB package: je_es_online_vat_ext_pkg
Run
JE Online VAT Register Reporting for Spain and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT data_type ,BILLING_TP_TAXPAYER_ID DECLARED_NIF ,BILLING_TP_NAME DECLARED_NAME ,BILLING_TP_TAX_REG_NUM SUPP_TRN ,to_char(TRX_DATE,'DD-MM-RRRR') ISSUE_DATE ,REPORTING_YEAR YEAR ,REPORTING_PERIOD PERIOD ,to_char(TAX_INVOICE_DATE,'DD-MM-RRRR') TRANSACTION_DATE ,DOC_SEQ_NAME INVOICE_IDENT ,DOC_SEQ_VALUE REGISTER_NUMBER ,TRANSACTION_DEADLINE TRANSACTION_DEADLINE ,LTRIM(to_char(SUM(tax_amt),'999999999990.99')) TAX_AMT ,LTRIM(to_char(tax_amt_funcl_curr,'999999999990.99') ) INV_TOTAL_AMT ,LTRIM(to_char(SUM(taxable_amt),'999999999990.99')) TAXABLE_AMT ,LTRIM(to_char(tax_paid_amt,'999999999990.99')) TRANSMISSION_PROPERTY_AMT ,LTRIM(to_char(tax_rate,'990.99')) TAX_RATE ,TRX_DESCRIPTION INV_DESC ,EVENT_CLASS_CODE EVENT_CLASS_CODE ,to_char(GL_DATE,'DD-MM-RRRR') GL_DATE ,COUNTRY_CODE COUNTRY_CODE ,KEY_TYPE KEY_TYPE ,SPECIAL_REGIME SPECIAL_REGIME ,TRX_LINE_TYPE INVOICE_TYPE ,max(RECTIFIED_TYPE) RECTIFIED_TYPE ,ADJUSTED_DOC_NUMBER RECTI_DOC_SEQ_VALUE ,to_char(ADJUSTED_DOC_DATE,'DD-MM-RRRR') RECTI_TRX_DATE ,LTRIM(to_char(RECTIFIED_TAXABLE_AMT,'999999999990.99')) RECTI_TAXABLE_AMT ,LTRIM(to_char(RECTIFIED_TAX_AMT,'999999999990.99')) RECTI_TAX_AMT ,LTRIM(to_char(NVL(RECTIFIED_SURCHARGE_AMT,0),'999999999990.99')) RECTI_SURCHARGE_AMT ,LTRIM(to_char(SUM(NVL(SURCHARGE_AMOUNT,0)),'999999999990.99')) SURCHARGE_AMT ,LTRIM(to_char(SURCHARGE_RATE,'990.99')) SURCHARGE_RATE ,tax_rate_register_type_code TAX_TYPE --S1,S2,SE ,exempt_reason_code EXEMPTION_REASON ,property_location PROP_LOC ,property_rental PROP_RENTAL ,billing_tp_tax_reporting_flag THIRD_PARTY_INV ,tax_display_type TAX_DISPLAY_TYPE ,intra_eu_trx_type_code GOODS_SERVICES ,trx_id ,rep_context_entity_name F4_LAST_INV_NUM ,LTRIM(to_char(taxable_amt_funcl_curr,'999999999990.99')) TOT_TAXABLE_AMT FROM JE_ES_ONLINE_VAT_TRX jeovt WHERE nvl(jeovt.data_type, 'X') = 'AR' AND jeovt.request_id = :P_REQUEST_ID AND jeovt.file_number = :P_FILE_NUMBER AND :P_REGISTER_TYPE = 'IIR' AND jeovt.register_type = 'IIR' AND jeovt.TRANSACTION_STATUS = :P_COMMUNICATION_TYPE AND jeovt.DOC_SEQ_NAME <> '/' -- don't select trx which doesn't have doc seq/doc seq vale GROUP BY data_type ,BILLING_TP_TAXPAYER_ID ,BILLING_TP_NAME ,BILLING_TP_TAX_REG_NUM ,to_char(TRX_DATE,'DD-MM-RRRR') ,REPORTING_YEAR ,REPORTING_PERIOD ,to_char(TAX_INVOICE_DATE,'DD-MM-RRRR') ,DOC_SEQ_NAME ,DOC_SEQ_VALUE ,TRANSACTION_DEADLINE --,to_char(TAX_AMT,'999999999990.99') ,to_char(TAX_AMT_FUNCL_CURR,'999999999990.99') -- ,to_char(TAXABLE_AMT,'999999999990.99') ,to_char(TAX_PAID_AMT,'999999999990.99') ,to_char(TAX_RATE,'990.99') ,TRX_DESCRIPTION ,EVENT_CLASS_CODE ,to_char(GL_DATE,'DD-MM-RRRR') ,COUNTRY_CODE ,KEY_TYPE ,SPECIAL_REGIME ,TRX_LINE_TYPE -- ,RECTIFIED_TYPE ,ADJUSTED_DOC_NUMBER ,to_char(ADJUSTED_DOC_DATE,'DD-MM-RRRR') ,to_char(RECTIFIED_TAXABLE_AMT,'999999999990.99') ,to_char(RECTIFIED_TAX_AMT,'999999999990.99') ,to_char(NVL(RECTIFIED_SURCHARGE_AMT,0),'999999999990.99') -- ,to_char(SURCHARGE_AMOUNT,'999999999990.99') ,to_char(SURCHARGE_RATE,'990.99') ,tax_rate_register_type_code --S1,s2,s3 ,exempt_reason_code --E1..E6 ,property_location ,property_rental ,billing_tp_tax_reporting_flag ,tax_display_type ,intra_eu_trx_type_code ,trx_id ,rep_context_entity_name ,LTRIM(to_char(taxable_amt_funcl_curr,'999999999990.99')) ORDER BY rectified_type nulls first ,invoice_ident |
| Parameter Name | SQL text | Validation | |
|---|---|---|---|
| Request ID |
|
Number | |
| Register Type |
|
LOV Oracle | |
| Communication Type |
|
LOV Oracle | |
| File Number |
|
Number | |
| Report Mode |
|
LOV Oracle |
