P_CnsldtnUnitOverview

@AbapCatalog.sqlViewName: 'PCSCUOVERVIEW'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@VDM.viewType: #COMPOSITE
@VDM.private:true
@ObjectModel.usageType: {
  dataClass: #MASTER,
  serviceQuality: #D,
  sizeCategory: #M
}
@ClientHandling: {
  type:      #CLIENT_DEPENDENT,
  algorithm: #SESSION_VARIABLE
}
@AccessControl.authorizationCheck: #NOT_REQUIRED

define view P_CnsldtnUnitOverview
  as select from            I_CnsldtnUnit_3            as _CnsldtnUnit

    cross join              I_CnsldtnVersion           as _ConsolidationVersion

  // retrieve all assignments

    left outer to many join I_CnsldtnUnitByTime        as _CnsldtnUnitByTime        on  _CnsldtnUnit.ConsolidationDimension = _CnsldtnUnitByTime.ConsolidationDimension
                                                                                    and _CnsldtnUnit.ConsolidationUnit      = _CnsldtnUnitByTime.ConsolidationUnit

    left outer to many join I_CnsldtnUnitByTimeVersion as _CnsldtnUnitByTimeVersion on  _CnsldtnUnit.ConsolidationDimension               = _CnsldtnUnitByTimeVersion.ConsolidationDimension
                                                                                    and _CnsldtnUnit.ConsolidationUnit                    = _CnsldtnUnitByTimeVersion.ConsolidationUnit
                                                                                    and _ConsolidationVersion.CnsldtnUnitAttributeVersion = _CnsldtnUnitByTimeVersion.CnsldtnUnitAttributeVersion

    left outer to many join P_CnsldtnAcctgIntegSetting as _CnsldtnAcctgIntegSetting on _CnsldtnAcctgIntegSetting.SequenceNumber = '1'
                                                                                    or _CnsldtnAcctgIntegSetting.SequenceNumber = '2'     
    
{
  key _CnsldtnUnit.ConsolidationDimension,
  key _CnsldtnUnit.ConsolidationUnit,
  key _ConsolidationVersion.ConsolidationVersion,

      // case statements required for items without assignment

  key case
        when _CnsldtnUnitByTime.FromFiscalYearPeriod is null and _CnsldtnUnitByTimeVersion.FromFiscalYearPeriod is null and _CnsldtnAcctgIntegSetting.FromFiscalYearPeriod is null
        then cast( '0000000' as fincs_fsi_fiscalyearper_from)

        when _CnsldtnUnitByTime.FromFiscalYearPeriod is null and _CnsldtnUnitByTimeVersion.FromFiscalYearPeriod is null
        then _CnsldtnAcctgIntegSetting.FromFiscalYearPeriod

        when _CnsldtnUnitByTime.FromFiscalYearPeriod is null and _CnsldtnAcctgIntegSetting.FromFiscalYearPeriod is null
        then _CnsldtnUnitByTimeVersion.FromFiscalYearPeriod

        when _CnsldtnUnitByTimeVersion.FromFiscalYearPeriod is null and _CnsldtnAcctgIntegSetting.FromFiscalYearPeriod is null
        then _CnsldtnUnitByTime.FromFiscalYearPeriod

        when _CnsldtnUnitByTime.FromFiscalYearPeriod is null
        then case
               when _CnsldtnUnitByTimeVersion.FromFiscalYearPeriod >= _CnsldtnAcctgIntegSetting.FromFiscalYearPeriod
               then _CnsldtnUnitByTimeVersion.FromFiscalYearPeriod
               else _CnsldtnAcctgIntegSetting.FromFiscalYearPeriod
             end

        when _CnsldtnUnitByTimeVersion.FromFiscalYearPeriod is null
        then case
               when _CnsldtnUnitByTime.FromFiscalYearPeriod >= _CnsldtnAcctgIntegSetting.FromFiscalYearPeriod
               then _CnsldtnUnitByTime.FromFiscalYearPeriod
               else _CnsldtnAcctgIntegSetting.FromFiscalYearPeriod
             end

        when _CnsldtnAcctgIntegSetting.FromFiscalYearPeriod is null
        then case
               when _CnsldtnUnitByTimeVersion.FromFiscalYearPeriod >= _CnsldtnUnitByTime.FromFiscalYearPeriod
               then _CnsldtnUnitByTimeVersion.FromFiscalYearPeriod
               else _CnsldtnUnitByTime.FromFiscalYearPeriod
             end

        when _CnsldtnUnitByTime.FromFiscalYearPeriod >= _CnsldtnUnitByTimeVersion.FromFiscalYearPeriod and _CnsldtnUnitByTime.FromFiscalYearPeriod >= _CnsldtnAcctgIntegSetting.FromFiscalYearPeriod
        then _CnsldtnUnitByTime.FromFiscalYearPeriod

        when _CnsldtnUnitByTimeVersion.FromFiscalYearPeriod >= _CnsldtnUnitByTime.FromFiscalYearPeriod and _CnsldtnUnitByTimeVersion.FromFiscalYearPeriod >= _CnsldtnAcctgIntegSetting.FromFiscalYearPeriod
        then _CnsldtnUnitByTimeVersion.FromFiscalYearPeriod
        else  _CnsldtnAcctgIntegSetting.FromFiscalYearPeriod

      end                                                                                                                            as FromFiscalYearPeriod,

      case
        when _CnsldtnUnitByTime.ToFiscalYearPeriod is null and _CnsldtnUnitByTimeVersion.ToFiscalYearPeriod is null and _CnsldtnAcctgIntegSetting.ToFiscalYearPeriod is null
        then cast( '9999999' as fincs_fsi_fiscalyearper_to)

        when _CnsldtnUnitByTime.ToFiscalYearPeriod is null and _CnsldtnUnitByTimeVersion.ToFiscalYearPeriod is null
        then _CnsldtnAcctgIntegSetting.ToFiscalYearPeriod

        when _CnsldtnUnitByTime.ToFiscalYearPeriod is null and _CnsldtnAcctgIntegSetting.ToFiscalYearPeriod is null
        then _CnsldtnUnitByTimeVersion.ToFiscalYearPeriod

        when _CnsldtnUnitByTimeVersion.ToFiscalYearPeriod is null and _CnsldtnAcctgIntegSetting.ToFiscalYearPeriod is null
        then _CnsldtnUnitByTime.ToFiscalYearPeriod

        when _CnsldtnUnitByTime.ToFiscalYearPeriod is null
        then case
               when _CnsldtnUnitByTimeVersion.ToFiscalYearPeriod <= _CnsldtnAcctgIntegSetting.ToFiscalYearPeriod
               then _CnsldtnUnitByTimeVersion.ToFiscalYearPeriod
               else _CnsldtnAcctgIntegSetting.ToFiscalYearPeriod
             end

        when _CnsldtnUnitByTimeVersion.ToFiscalYearPeriod is null
        then case
               when _CnsldtnUnitByTime.ToFiscalYearPeriod <= _CnsldtnAcctgIntegSetting.ToFiscalYearPeriod
               then _CnsldtnUnitByTime.ToFiscalYearPeriod
               else _CnsldtnAcctgIntegSetting.ToFiscalYearPeriod
             end

        when _CnsldtnAcctgIntegSetting.ToFiscalYearPeriod is null
        then case
               when _CnsldtnUnitByTimeVersion.ToFiscalYearPeriod <= _CnsldtnUnitByTime.ToFiscalYearPeriod
               then _CnsldtnUnitByTimeVersion.ToFiscalYearPeriod
               else _CnsldtnUnitByTime.ToFiscalYearPeriod
             end

        when _CnsldtnUnitByTime.ToFiscalYearPeriod <= _CnsldtnUnitByTimeVersion.ToFiscalYearPeriod and _CnsldtnUnitByTime.ToFiscalYearPeriod <= _CnsldtnAcctgIntegSetting.ToFiscalYearPeriod
        then _CnsldtnUnitByTime.ToFiscalYearPeriod

        when _CnsldtnUnitByTimeVersion.ToFiscalYearPeriod <= _CnsldtnUnitByTime.ToFiscalYearPeriod and _CnsldtnUnitByTimeVersion.ToFiscalYearPeriod <= _CnsldtnAcctgIntegSetting.ToFiscalYearPeriod
        then _CnsldtnUnitByTimeVersion.ToFiscalYearPeriod
        else  _CnsldtnAcctgIntegSetting.ToFiscalYearPeriod

      end                                                                                                                            as ToFiscalYearPeriod,

      _ConsolidationVersion.CnsldtnUnitAttributeVersion,

      _CnsldtnUnit.Country,
      _CnsldtnUnit.ConsolidationUnitLink,
      _CnsldtnUnit.ConsolidationUnitLinkLabel,

      _CnsldtnUnitByTime.Company,
      _CnsldtnUnitByTime.ConsolidationUnitLocalCurrency,

      cast ( coalesce( _CnsldtnUnitByTimeVersion.DocumentEntryIsInGroupCurrency, '' ) as fincs_gcind preserving type )               as DocumentEntryIsInGroupCurrency,

      cast ( coalesce( _CnsldtnUnitByTimeVersion.CnsldtnUnitLocalCrcySource, '' )     as fincs_local_curr_source preserving type )   as CnsldtnUnitLocalCrcySource,

      cast ( coalesce( _CnsldtnUnitByTimeVersion.CnsldtnUnitGroupCrcySource, '' )     as fincs_group_curr_source preserving type )   as CnsldtnUnitGroupCrcySource,

      cast ( coalesce( _CnsldtnUnitByTimeVersion.FiscalYearVariant, '' )              as fincs_bunit_periv preserving type )         as FiscalYearVariant,

      cast ( coalesce( _CnsldtnUnitByTimeVersion.CnsldtnDataTransferMethod, '' )      as fincs_datatransfermethod preserving type )  as CnsldtnDataTransferMethod,

      cast ( coalesce( _CnsldtnUnitByTimeVersion.CnsldtnUploadMethod, '' )            as fincs_upload_method preserving type )       as CnsldtnUploadMethod,

      cast ( coalesce( _CnsldtnUnitByTimeVersion.CnsldtnCrcyTranslationMethod, '' )   as fincs_crcy_trnsltn_method preserving type ) as CnsldtnCrcyTranslationMethod,

      cast ( coalesce( _CnsldtnUnitByTimeVersion.TaxRate, 0 )                         as fc_taxrt preserving type )                  as TaxRate,

      cast ( coalesce( _CnsldtnUnitByTimeVersion.ConsolidationUnitIsPartnerOnly, '' ) as fincs_bunit_partner_only preserving type )  as ConsolidationUnitIsPartnerOnly,

      cast ( coalesce( _CnsldtnUnitByTimeVersion.CnsldtnUnitDataCollection, '' )      as fincs_unitdatacollection preserving type )  as CnsldtnUnitDataCollection,

      cast ( coalesce( _CnsldtnUnitByTimeVersion.ConsolidationUnitPartner, '' )       as fincs_unitpartner preserving type )         as ConsolidationUnitPartner,

      cast ( coalesce( _CnsldtnUnitByTimeVersion.ConsolidationUnitPostingRule, '' )   as fincs_unitpostingrule preserving type )     as ConsolidationUnitPostingRule,

      cast ( coalesce( _CnsldtnUnitByTimeVersion.ConsolidationUnitValidation, '' )    as fincs_unitvalidation preserving type )      as ConsolidationUnitValidation,

      _CnsldtnAcctgIntegSetting.CnsldtnFrmYrForAcctgInteg,     

      //    purpose: association to extension include view in higher layers of view stack

      cast( _CnsldtnUnitByTimeVersion.FromFiscalYearPeriod as fins_fyearperiod)                                                      as FiscalYearPeriod

}