JE Spanish Output VAT Journal

Description
Categories: BI Publisher
Application: European Localizations
Source: Spanish Output VAT Journal Report
Short Name: JEESRRVR_XMLP
DB package: jg_zz_journal_ar_pkg
     SELECT
         JZVTD.rep_context_entity_name        LEGAL_ENTITY_NAME
        ,JZVTD.rep_context_entity_location_id LE_LOCATION_ID
        ,HLA.address_line_1                   ADDRESS1
        ,HLA.address_line_2                   ADDRESS2
        ,HLA.address_line_3                   ADDRESS3
        ,HLA.town_or_city                     CITY
        ,HLA.postal_code                      POSTAL_CODE
        ,HLA.telephone_number_1               TELEPHONE
        ,JZVRS.tax_registration_number        LE_TAX_REGISTRATION_NUMBER
        ,JZVTD.legal_contact_party_name       CONTACT_NAME
        ,JZVRS.tax_calendar_year              TAX_PERIOD_YEAR
        ,JZVRS.tax_calendar_period            TAX_PERIOD_NAME
        ,to_char(JZVRS.period_start_date,'DD-Mon-RRRR')             PERIOD_START_DATE
        ,to_char(JZVRS.period_end_date,'DD-Mon-RRRR')               PERIOD_END_DATE
        ,JZVTD.extract_source_ledger          EXTRACT_SOURCE_LEDGER
        ,JZVTD.event_class_code               EVENT_CLASS_CODE
        ,JZVTD.period_name                    GL_PERIOD_NAME
        ,JZVTD.doc_seq_name                   DOC_SEQUENCE_NAME
        ,JZVTD.doc_seq_value                  DOC_SEQUENCE_NUMBER
        ,to_char(JZVTD.trx_date,'DD-Mon-RRRR')                        INVOICE_DATE
        ,JZVTD.billing_tp_party_number        CUSTOMER_NUMBER
        ,JZVTD.billing_tp_taxpayer_id         CUSTOMER_TAX_PAYER_ID
        ,JZVTD.billing_tp_site_name           CUSTOMER_SITE_NAME
        ,JZVTD.billing_tp_name                CUSTOMER_NAME
        ,JZVTD.billing_tp_tax_reg_num         CUST_BILL_TAX_REG_NUM
        ,JZVTD.shipping_tp_tax_reg_num        CUST_SHIP_AX_REG_NUM
        ,JZVTD.billing_tp_site_tax_reg_num    CUST_SITE_BILL_TAX_REG_NUM
        ,JZVTD.shipping_tp_site_tax_reg_num   CUST_SITE_SHIP_TAX_REG_NUM
        ,JZVTD.trx_number                     INVOICE_NUMBER
        ,NVL(JZVTD.taxable_amt,0)             TAXABLE_AMT
        ,NVL(JZVTD.tax_amt,0)                 TAX_AMOUNT
        ,NVL(JZVTD.taxable_amt_funcl_curr,0)  TAXABLE_AMT_FUNCL_CURR
        ,NVL(JZVTD.tax_amt_funcl_curr,0)      TAX_AMOUNT_FUNCL_CURR
        ,JZVTD.trx_id                         TRX_ID
        ,to_char(JZVTD.cancelled_date,'DD-Mon-RRRR')                 TRX_CANCELLED_DATE
        ,JZVTD.cancel_flag                    TRX_CANCEL_FLAG
        ,JZVTD.offset_flag                    TAX_OFFSET_FLAG
        ,JZVTD.posted_flag                    POSTED_FLAG
        ,JZVTD.tax_recoverable_flag           TAX_RECOVERABLE_FLAG
        ,JZVTD.account_flexfield              ACCOUNT
        ,JZVTD.account_description            ACCOUNT_DESCRIPTION
        ,JZVTD.trx_line_number                LINE_NUMBER
        ,JZVTD.tax_type_code                  TAX_TYPE_CODE
        ,JZVTD.tax_type_mng                   TAX_TYPE_MEANING
        ,JZVTD.tax_rate                       TAX_RATE
        ,JZVTD.tax_rate_code                  TAX_RATE_CODE
        ,JZVTD.tax_rate_vat_trx_type_code     TAX_RATE_VAT_TRX_TYPE_CODE
        ,JZVTD.tax_rate_vat_trx_type_desc     TAX_RATE_VAT_TRX_TYPE_DESC
        ,JZVBA.taxable_box                    TAXABLE_REPORT_BOX
        ,JZVBA.tax_box                        TAX_REPORT_BOX
        ,to_char(JZVTD.accounting_date,'DD-Mon-RRRR')                GL_DATE
        ,JZVTD.applied_to_trx_number          APPLIED_TO_TRX_NUMBER
        ,VAT_REGISTERS.register_type          VAT_REGISTER_TYPE
        ,VAT_REGISTERS.register_name          VAT_REGISTER_NAME
        ,VAT_REGISTERS.db_sequence_name       VAT_DOC_SEQ_NAME
        ,XEP.name                             REPORTER
        ,JZVTD.tax_rate_code_description      TAX_RATE_DESCRIPTION
        ,JZVTD.trx_line_id                    TRX_LINE_ID
FROM   jg_zz_vat_trx_details     JZVTD
      ,jg_zz_vat_rep_status      JZVRS
      ,jg_zz_vat_box_allocs      JZVBA
      ,hr_locations_all          HLA
      ,xle_entity_profiles       XEP
      ,(SELECT JZVRV.vat_reporting_entity_id  vat_reporting_entity_id
              ,JZVRV.vat_register_id          vat_register_id
              ,JZVRV.register_type            register_type
              ,JZVRV.register_name            register_name
              ,FDS.db_sequence_name           db_sequence_name
              ,DOC_SEQ.doc_sequence_id        doc_sequence_id
        FROM   jg_zz_vat_registers_vl       JZVRV
              ,jg_zz_vat_doc_sequences      DOC_SEQ
              ,fnd_document_sequences       FDS
        WHERE JZVRV.vat_register_id = DOC_SEQ.vat_register_id
        AND   FDS.doc_sequence_id   = DOC_SEQ.doc_sequence_id
       ) VAT_REGISTERS
      WHERE  JZVRS.reporting_status_id                = JZVTD.reporting_status_id
      AND    JZVTD.vat_transaction_id                 = JZVBA.vat_transaction_id(+)
  /*  AND    VAT_REGISTERS.vat_reporting_entity_id (+)= JZVRS.mapping_vat_rep_entity_id */
      AND    VAT_REGISTERS.doc_sequence_id (+)        = JZVTD.doc_seq_id
      AND    JZVTD.rep_context_entity_location_id     = HLA.location_id
      AND    JZVTD.rep_entity_id                      = XEP.legal_entity_id
      AND    JZVRS.source                             = 'AR'
      AND    JZVRS.tax_calendar_period                = :P_PERIOD
      AND    JZVRS.vat_reporting_entity_id            = :P_VAT_REP_ENTITY_ID
	  AND    :P_REPORT_NAME is NULL
Parameter NameSQL textValidation
Calling Report
 
Chart of Account Id
 
LOV Oracle
Generate Sequence Number
 
LOV Oracle
Balancing Segment
 
Register Type
 
LOV Oracle
Tax Reporting Type Code
 
LOV Oracle
Tax Calendar Period To
 
LOV Oracle
Tax Calendar Period From
 
LOV Oracle
Reporting Identifier
 
LOV Oracle