JG EMEA VAT: Allocation Rules Listing

Description
Categories: BI Publisher
Application: Regional Localizations
Source: EMEA VAT: Allocation Rules Listing
Short Name: JGZZALRL
DB package: JG_ZZ_JGZZALRL_PKG
      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
Parameter Name SQL text Validation
JGZZ_TAX_REGIME
 
LOV Oracle
Recoverable Taxable Box
 
LOV Oracle
Non Recoverable Tax Box
 
LOV Oracle
Recoverable Tax Box
 
LOV Oracle
Tax Rate Code
 
LOV Oracle
Tax Jurisdiction
 
LOV Oracle
Tax Status
 
LOV Oracle
Tax
 
LOV Oracle
VAT Transaction Type
 
LOV Oracle
Financial Transaction Type
 
LOV Oracle
Source
 
LOV Oracle
Reporting Identifier
 
LOV Oracle