JG EMEA VAT: Allocations Listing

➜ Back to Library
Description
Categories: BI Publisher
Application: Regional Localizations
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
Download
Related Blitz Reports
JG EMEA VAT: Allocation Errors Listing JE Belgian VAT Sales Journal JG EMEA VAT: Journal AR Extract JE Spanish Output VAT Journal JE Italian Sales VAT Register JG EMEA VAT: Allocation Rules Listing JE Italian Receivables Deferred VAT Register JE Austrian VAT Reconciliation JG EMEA VAT: Yearly Extract
Blitz Report™

Blitz Report™ provides multiple benefits: