JE Italian Sales VAT Register

Description
Categories: BI Publisher
Imported from BI Publisher
Application: European Localizations
Source: Italian Sales VAT Register
Short Name: JEITARSV_XMLP
DB package: jg_zz_journal_ar_pkg

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

     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 Name SQL text Validation
Reporting Identifier
 
LOV Oracle
Tax Calendar Period
 
LOV Oracle
Sales VAT Register
 
LOV Oracle
First Page Number
 
Number