JG EMEA VAT: Allocations Listing

Description
Categories: BI Publisher
Columns: 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 ...
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