JG EMEA VAT: Allocation Rules Listing

      select  jzvre.entity_identifier
             ,map_jzvre.tax_calendar_name
             ,map_jzvre.tax_regime_code
             ,map_jzvre.threshold_amount
			 ,map_jzvre.legal_entity_id  le_id
             ,jzvar.allocation_rule_id
             ,jzvar.source
             ,nvl2(jzvar.vat_transaction_type,
			 jzvar.vat_transaction_type||' - '||flv.description,jzvar.vat_transaction_type) vat_transaction_type
             ,jzvar.financial_document_type
             ,jzvar.tax_rec_sign_flag
             ,jzvar.tax_non_rec_sign_flag
             ,jzvar.taxable_rec_sign_flag
             ,jzvar.taxable_non_rec_sign_flag
             ,jzvar.tax_code
             ,jzvar.tax_status
             ,jzvar.tax_jurisdiction_code
             ,jzvar.tax_box_recoverable
             ,jzvar.tax_box_non_recoverable
             ,jzvar.taxable_box_recoverable
             ,jzvar.taxable_box_non_recoverable
             ,jzvar.effective_from_date
             ,jzvar.effective_to_date
             ,jzvar.period_type
             ,jzvar.effective_to_date
             ,jzvar.period_type
             ,jzvar.tax_rate_code
			 ,nvl(jzvar.self_assessed_flag,'N') self_assessed_flag 
			 ,jzvar.tax_box_self_assess_liability           
			 ,jzvar.tax_self_assess_liab_sign_flag          
      from    jg_zz_vat_alloc_rules jzvar
            , jg_zz_vat_rep_entities jzvre
            , jg_zz_vat_rep_entities map_jzvre
			, fnd_lookup_values flv
      where   jzvre.vat_reporting_entity_id     = :p_vat_reporting_entity_id
      and    ((jzvre.entity_type_code          = 'ACCOUNTING'
               and
               jzvre.mapping_vat_rep_entity_id = jzvar.vat_reporting_entity_id
               and
               jzvre.mapping_vat_rep_entity_id = map_jzvre.vat_reporting_entity_id
               )
             OR
              (jzvre.entity_type_code          = 'LEGAL'
               and
               jzvre.vat_reporting_entity_id   = jzvar.vat_reporting_entity_id
               and
               jzvre.vat_reporting_entity_id   = map_jzvre.vat_reporting_entity_id
               ))
      and    jzvar.vat_transaction_type        = flv.lookup_code(+)
	  and    flv.lookup_type(+)             like  'ZX_JEBE_VAT_TRANS_TYPE'
	  and    flv.language(+)                   =  userenv('LANG')
      and    (:P_SOURCE is null             or    jzvar.source  = :P_SOURCE   or :P_SOURCE = 'ALL')
      and    (:P_FIN_TRAN_TYPE is null      or    jzvar.financial_document_type = :P_FIN_TRAN_TYPE)
      and    (:P_VAT_TRAN_TYPE is null      or    jzvar.vat_transaction_type = :P_VAT_TRAN_TYPE)
      and    (:P_TAX_CODE is null           or    jzvar.tax_code = :P_TAX_CODE)
      and    (:P_TAX_STATUS is null         or    jzvar.tax_status = JG_ZZ_JGZZALRL_PKG.get_tax_status_code)
      and    (:P_TAX_JURISDICTION is null   or    jzvar.tax_jurisdiction_code   = :P_TAX_JURISDICTION)
      and    (:P_TAX_RATE_ID is null        or    jzvar.tax_rate_code = JG_ZZ_JGZZALRL_PKG.get_tax_rate_code)
      and    (:P_TAX_BOX  is null           or    jzvar.tax_box_recoverable = :P_TAX_BOX)
      and    (:P_NON_REC_TAX_BOX is null    or    jzvar.tax_box_non_recoverable = :P_NON_REC_TAX_BOX)
      and    (:P_TAXABLE_BOX is null        or    jzvar.taxable_box_recoverable = :P_TAXABLE_BOX)
      and    (:P_NON_REC_TAXABLE_BOX is null    or    jzvar.taxable_box_non_recoverable = :P_NON_REC_TAXABLE_BOX)
      order by entity_identifier
             , vat_transaction_type
             , source