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 |
