JG EMEA VAT: Allocation Errors Listing

Description
Categories: BI Publisher
Application: Regional Localizations
Source: EMEA VAT: Allocation Errors Listing
Short Name: JGZZAERL
DB package: JG_ZZ_AERL_DT_PKG
      select
              1 dummyForCount   -- dummy column to count the number of records fetched by query , brathod
            , jzvtd.extract_source_ledger source
            , jzvtd.doc_seq_name
            , jzvtd.doc_seq_value
            , jzvtd.billing_tp_name
            , jzvtd.trx_number
            , jzvtd.trx_line_number
            , jzvtd.trx_line_amt
            , jzvtd.accounting_date
            , jzvtd.posted_flag
            , substr(jzvbe.allocation_error_code,7) allocation_error_code
            , jzvbe.period_type
            --, null financial_document_type                           -- jzvar.financial_document_type
	      , JG_ZZ_AERL_DT_PKG.get_financial_document_type(jzvtd.trx_id , jzvtd.trx_type_id , jzvrs.source , jzvtd.entity_code ) financial_document_type                           
            , jzvtd.tax_rate_vat_trx_type_code vat_transaction_type  --jzvar.vat_transaction_type
            , jzvtd.tax tax_code                                     -- jzvar.tax_code
            , jzvtd.tax_status_code tax_status                       -- jzvar.tax_status
            , jzvtd.tax_jurisdiction_code                            --jzvar.tax_jurisdiction_code
            , jzvtd.tax_rate_id                                      --jzvar.tax_rate_id
            , jzvtd.tax_rate_code                                    -- jzvar.tax_rate_code
            , jzvrs.tax_calendar_period
            , jzvrs.tax_registration_number
            , jzvre.entity_identifier
            , JG_ZZ_AERL_DT_PKG.cf_batch_name(jzvtd.trx_id, jzvtd.extract_source_ledger) cf_batch_name
			,nvl(jzvtd.self_assessed_flag,'N')  self_assessed_flag
      from   /* jg_zz_vat_alloc_rules jzvar
             , ra_batches            rabatch , */
              jg_zz_vat_box_errors  jzvbe
            , jg_zz_vat_rep_status  jzvrs
            , jg_zz_vat_trx_details jzvtd
            , jg_zz_vat_rep_entities jzvre
      where jzvbe.vat_transaction_id            = jzvtd.vat_transaction_id
      and   jzvrs.reporting_status_id           = jzvtd.reporting_status_id
      and   jzvre.vat_reporting_entity_id       = jzvrs.vat_reporting_entity_id
      /* this piece of filtering is commented because rules table is removed in the FROM CLAUSE
        and   ((jzvre.entity_type_code            = 'ACCOUNTING'
              and
              jzvre.mapping_vat_rep_entity_id   = jzvar.vat_reporting_entity_id)
              OR
              (jzvre.entity_type_code           = 'LEGAL'
               and
               jzvre.vat_reporting_entity_id    = jzvar.vat_reporting_entity_id))
      */
      and   jzvrs.vat_reporting_entity_id       = :p_vat_reporting_entity_id
      and   jzvrs.tax_calendar_period           = :P_PERIOD_NAME
      and   ((:P_SOURCE = 'ALL') or (jzvrs.source    = nvl(:P_SOURCE, jzvrs.source)))
      and   jzvbe.allocation_error_code is not null
Parameter Name SQL text Validation
Source
 
LOV Oracle
Period Name
 
LOV Oracle
Reporting Identifier
 
LOV Oracle