JG EMEA VAT: Allocations Listing
Description
Categories: BI Publisher
Application: Regional Localizations
Source: EMEA VAT: Allocations Listing
Short Name: JGZZALST
DB package:
Source: EMEA VAT: Allocations Listing
Short Name: JGZZALST
DB package:
Run
JG EMEA VAT: Allocations Listing and other Oracle EBS reports with Blitz Report™ on our demo environment
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 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Reporting Identifier |
|
LOV Oracle | |
Period Name |
|
LOV Oracle | |
Source |
|
LOV Oracle | |
VAT Transaction Type |
|
LOV Oracle | |
Document Sequence Name |
|
LOV Oracle | |
From Document Sequence Number |
|
Number | |
To Document Sequence Number |
|
Number |