select extract_source_ledger,
doc_seq_name,
doc_seq_value,
trx_id,
trx_number,
trx_line_number,
taxable_line_amt,
tax_line_amt,
accounting_date,
tax_box_recoverable,
tax_box_non_recoverable,
taxable_box,
output_box,
period_type,
vat_box_allocation_id,
financial_document_type,
vat_transaction_type,
tax_code,
tax_status,
tax_jurisdiction_code,
tax_rate_id,
tax_rate_code,
final_reporting_id,
tax_registration_number,
entity_identifier,
tax_line_id,
allocation_rule_id,
cancel_flag,
reverse_flag,
taxable_item_source_id,
recoverable_flag,
self_assessed_flag
from (
select
jzvtd.extract_source_ledger
, jzvtd.doc_seq_name
, jzvtd.doc_seq_value
, jzvtd.trx_id
, jzvtd.trx_number
, jzvtd.trx_line_number
, sum(decode(jzvtd.tax_recoverable_flag,'N',0,nvl(jzvtd.taxable_amt_funcl_curr,jzvtd.taxable_amt) * decode(jzvar.taxable_non_rec_sign_flag, '-', -1, 1))) taxable_line_amt
, sum (NVL(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt), 0) * decode(jzvtd.extract_source_ledger, 'AR', decode(jzvar.tax_rec_sign_flag, '-', -1, 1),
'AP', decode(jzvba.tax_recoverable_flag, 'Y', decode(jzvar.tax_rec_sign_flag, '-', -1, 1),
decode(jzvar.tax_non_rec_sign_flag, '-', -1, 1)),
decode(jzvar.tax_non_rec_sign_flag, '-', -1, 1)) )tax_line_amt
, jzvtd.accounting_date
, '-XX' tax_box_recoverable
, '-XX' tax_box_non_recoverable
, '-XX' taxable_box
,max( decode(jzvtd.extract_source_ledger,'AP',decode(jzvar.self_assessed_flag,'Y',nvl(jzvba.output_box,'-XX'),'-XX'),'-XX')) output_box
, jzvba.period_type
,max( jzvba.vat_box_allocation_id) vat_box_allocation_id
, jzvar.financial_document_type
, jzvar.vat_transaction_type
, jzvar.tax_code
, jzvar.tax_status
, jzvar.tax_jurisdiction_code
, jzvar.tax_rate_id
, jzvar.tax_rate_code
, jzvrs.final_reporting_process_id final_reporting_id
, jzvrs.tax_registration_number
, jzvre.entity_identifier
, jzvtd.tax_line_id
, jzvba.allocation_rule_id
, nvl(jzvtd.cancel_flag,'N') cancel_flag
, nvl(jzvtd.reverse_flag,'N') reverse_flag
, jzvtd.taxable_item_source_id
, nvl(jzvtd.tax_recoverable_flag,'Y') recoverable_flag
,nvl(jzvtd.self_assessed_flag,'N') self_assessed_flag
from jg_zz_vat_alloc_rules jzvar
, jg_zz_vat_box_allocs jzvba
, jg_zz_vat_rep_status jzvrs
, jg_zz_vat_trx_details jzvtd
, jg_zz_vat_rep_entities jzvre
where jzvba.vat_transaction_id = jzvtd.vat_transaction_id
and jzvre.vat_reporting_entity_id = :p_vat_reporting_entity_id
and jzvre.vat_reporting_entity_id = jzvrs.vat_reporting_entity_id
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.reporting_status_id = jzvtd.reporting_status_id
and jzvrs.source = jzvtd.extract_source_ledger
and jzvba.allocation_rule_id = jzvar.allocation_rule_id
and jzvrs.tax_calendar_period = :P_PERIOD_NAME
and (:p_source is null or :p_source = 'ALL' or jzvtd.extract_source_ledger = :P_SOURCE)
and (:p_vat_tran_type is null or jzvar.vat_transaction_type = :P_VAT_TRAN_TYPE)
and (:p_doc_sequence_name is null or jzvtd.doc_seq_id = :p_doc_sequence_name)
and (:p_from_doc_seq_number is null or jzvtd.doc_seq_value >= :p_from_doc_seq_number)
and (:p_to_doc_seq_number is null or jzvtd.doc_seq_value <= :p_to_doc_seq_number)
and not exists (select 1 from jg_zz_vat_box_errors where vat_transaction_id = jzvba.vat_transaction_id)
and jzvtd.self_assessed_flag ='Y'
and jzvar.self_assessed_flag ='Y'
and jzvtd.extract_source_ledger = 'AP'
Group by jzvtd.extract_source_ledger
, jzvtd.doc_seq_name
, jzvtd.doc_seq_value
, jzvtd.trx_id
, jzvtd.trx_number
, jzvtd.trx_line_number
-- , nvl(jzvtd.taxable_amt_funcl_curr,jzvtd.taxable_amt) * decode(jzvar.taxable_non_rec_sign_flag, '-', -1, 1)
, jzvtd.accounting_date
,
jzvba.period_type
-- , jzvba.vat_box_allocation_id
, jzvar.financial_document_type
, jzvar.vat_transaction_type
, jzvar.tax_code
, jzvar.tax_status
, jzvar.tax_jurisdiction_code
, jzvar.tax_rate_id
, jzvar.tax_rate_code
, jzvrs.final_reporting_process_id
, jzvrs.tax_registration_number
, jzvre.entity_identifier
, jzvtd.tax_line_id
, jzvba.allocation_rule_id
, nvl(jzvtd.cancel_flag,'N')
, nvl(jzvtd.reverse_flag,'N')
, jzvtd.taxable_item_source_id
, nvl(jzvtd.tax_recoverable_flag,'Y')
,jzvtd.self_assessed_flag
union
select
jzvtd.extract_source_ledger
, jzvtd.doc_seq_name
, jzvtd.doc_seq_value
, jzvtd.trx_id
, jzvtd.trx_number
, jzvtd.trx_line_number
, nvl(jzvtd.taxable_amt_funcl_curr,jzvtd.taxable_amt) * decode(jzvar.taxable_non_rec_sign_flag, '-', -1, 1) taxable_line_amt
, NVL(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt), 0) * decode(jzvtd.extract_source_ledger, 'AR', decode(jzvar.tax_rec_sign_flag, '-', -1, 1),
'AP', decode(jzvba.tax_recoverable_flag, 'Y', decode(jzvar.tax_rec_sign_flag, '-', -1, 1),
decode(jzvar.tax_non_rec_sign_flag, '-', -1, 1)),
decode(jzvar.tax_non_rec_sign_flag, '-', -1, 1)) tax_line_amt
, jzvtd.accounting_date
, decode(decode(jzvtd.extract_source_ledger,'AR','Y',nvl(jzvba.tax_recoverable_flag,'N'))
,'Y',nvl(jzvba.tax_box,'-XX'),'-XX') tax_box_recoverable
, decode(decode(jzvtd.extract_source_ledger,'AR','Y',nvl(jzvba.tax_recoverable_flag,'N'))
,'N',nvl(jzvba.tax_box,'-XX'),'-XX') tax_box_non_recoverable
, nvl(jzvba.taxable_box,'-XX') taxable_box
, '-XX' output_box
, jzvba.period_type
, jzvba.vat_box_allocation_id
, jzvar.financial_document_type
, jzvar.vat_transaction_type
, jzvar.tax_code
, jzvar.tax_status
, jzvar.tax_jurisdiction_code
, jzvar.tax_rate_id
, jzvar.tax_rate_code
, jzvrs.final_reporting_process_id final_reporting_id
, jzvrs.tax_registration_number
, jzvre.entity_identifier
, jzvtd.tax_line_id
, jzvba.allocation_rule_id
, nvl(jzvtd.cancel_flag,'N') cancel_flag
, nvl(jzvtd.reverse_flag,'N') reverse_flag
, jzvtd.taxable_item_source_id
, nvl(jzvtd.tax_recoverable_flag,'Y') recoverable_flag -- For AR, this flag will be null
,nvl(jzvtd.self_assessed_flag,'N') self_assessed_flag
from jg_zz_vat_alloc_rules jzvar
, jg_zz_vat_box_allocs jzvba
, jg_zz_vat_rep_status jzvrs
, jg_zz_vat_trx_details jzvtd
, jg_zz_vat_rep_entities jzvre
where jzvba.vat_transaction_id = jzvtd.vat_transaction_id
and jzvre.vat_reporting_entity_id = :p_vat_reporting_entity_id
and jzvre.vat_reporting_entity_id = jzvrs.vat_reporting_entity_id
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.reporting_status_id = jzvtd.reporting_status_id
and jzvrs.source = jzvtd.extract_source_ledger
and jzvba.allocation_rule_id = jzvar.allocation_rule_id
and jzvrs.tax_calendar_period = :P_PERIOD_NAME
and (:p_source is null or :p_source = 'ALL' or jzvtd.extract_source_ledger = :P_SOURCE)
and (:p_vat_tran_type is null or jzvar.vat_transaction_type = :P_VAT_TRAN_TYPE)
and (:p_doc_sequence_name is null or jzvtd.doc_seq_id = :p_doc_sequence_name)
and (:p_from_doc_seq_number is null or jzvtd.doc_seq_value >= :p_from_doc_seq_number)
and (:p_to_doc_seq_number is null or jzvtd.doc_seq_value <= :p_to_doc_seq_number)
and not exists (select 1 from jg_zz_vat_box_errors where vat_transaction_id = jzvba.vat_transaction_id))
order by 14,25,24,1,5,4,26,27,30,28,29,31 desc |
