JG EMEA VAT: Allocations Listing

Description
Categories: BI Publisher
Application: Regional Localizations
Source: EMEA VAT: Allocations Listing
Short Name: JGZZALST
DB package:
 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
To Document Sequence Number
 
Number
From Document Sequence Number
 
Number
Document Sequence Name
 
LOV Oracle
VAT Transaction Type
 
LOV Oracle
Source
 
LOV Oracle
Period Name
 
LOV Oracle
Reporting Identifier
 
LOV Oracle