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
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
Report Mode
 
LOV Oracle
File Number
 
Number
Communication Type
 
LOV Oracle
Register Type
 
LOV Oracle
Request ID
 
Number