P_LegalTransactionCube

@AbapCatalog.sqlViewName: 'PLCMPLGLTR'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Cube - View for Legal Transaction'
@AbapCatalog.preserveKey:true
@VDM.viewType: #CONSUMPTION
@ClientHandling.algorithm: #SESSION_VARIABLE
@Metadata.ignorePropagatedAnnotations:true
@Metadata.allowExtensions:false
@VDM.private:true
@AccessControl.privilegedAssociations:  [ '_CreatedBy',  [ '_ChangedBy' ] ]
@ObjectModel.usageType: {serviceQuality: #D, sizeCategory: #XL, dataClass: #MIXED}
@ObjectModel.representativeKey: 'LegalTransactionUUID'

define view P_LegalTransactionCube

  as select from I_LegalTransaction

  association [0..1] to I_LegalTransactionDateTP   as _LegalTransactionDateTP     on  $projection.LegalTransactionUUID          = _LegalTransactionDateTP.LegalTransactionUUID
                                                                                  and _LegalTransactionDateTP.LglCntntMDateType = 'SAP1'
  association [0..1] to I_LegalTransactionDateTP   as _LegalTransactionEndDateTP  on  $projection.LegalTransactionUUID             = _LegalTransactionEndDateTP.LegalTransactionUUID
                                                                                  and _LegalTransactionEndDateTP.LglCntntMDateType = 'SAP2'
  association [0..1] to I_LegalTransactionDateTP   as _LegalTransactionNxtTermnTP on  $projection.LegalTransactionUUID              = _LegalTransactionNxtTermnTP.LegalTransactionUUID
                                                                                  and _LegalTransactionNxtTermnTP.LglCntntMDateType = 'SAP3'
  association [0..1] to I_LegalTransactionDateTP   as _LegalTransactionNxtRnwlTP  on  $projection.LegalTransactionUUID             = _LegalTransactionNxtRnwlTP.LegalTransactionUUID
                                                                                  and _LegalTransactionNxtRnwlTP.LglCntntMDateType = 'SAP4'
  association [0..1] to I_LegalTransRnwlAndTermnTP as _LTRnwlAndTermn             on  $projection.LegalTransactionUUID = _LTRnwlAndTermn.LegalTransactionUUID
  association [0..1] to C_LegalTrLinkedObjectsTP   as _LegalTrLinkedObjectsTP     on  $projection.LegalTransactionUUID                     = _LegalTrLinkedObjectsTP.LegalTransactionUUID
                                                                                  and _LegalTrLinkedObjectsTP.LegalTransactionIsTriggerObj = 'X'
  association [0..1] to I_LCMEntityTypeText        as _LCMEntityTypeText          on  $projection.LglCntntMEntityType = _LCMEntityTypeText.LglCntntMEntityType
                                                                                  and _LCMEntityTypeText.Language     = $session.system_language

  association [0..1] to I_LegalTrEntityAggr        as _LegalTrEntityAggr          on  $projection.LegalTransactionUUID = _LegalTrEntityAggr.LegalTransactionUUID
                                                                                  and $projection.LglCntntMEntity      = _LegalTrEntityAggr.LglCntntMEntity

  association [1..1] to I_LCMUserAddressAssignment as _CreatedBy                  on  $projection.LglCntntMCreatedByUser = _CreatedBy.ContactCardID

  association [1..1] to I_LCMUserAddressAssignment as _ChangedBy                  on  $projection.LglCntntMChangedByUser = _ChangedBy.ContactCardID

  association [1..1] to I_SalesOrganization        as _SalesOrganization          on  $projection.LglCntntMMainOrgSalesOrg = _SalesOrganization.SalesOrganization

  association [1..1] to I_CalendarDate             as _CalendarDate               on  $projection.LglCntntMCreatedDate = _CalendarDate.CalendarDate

{

  key LegalTransactionUUID,
      LegalTransaction,
      LegalTransactionTitle,
      LegalTransactionHealth,
      LglCntntMLanguage,
      LglCntntMCreatedByUser,
      _CreatedBy.FullName                                                                                  as CreatedByName,
      cast(SUBSTRING(cast( LglCntntMCreatedUTCDateTime as abap.char(28)),1,8)   as ecm_creationdate)       as LglCntntMCreatedDate,
      LglCntntMChangedByUser,
      _ChangedBy.FullName                                                                                  as ChangedByName,
      LglCntntMChangedUTCDateTime,
      LglCntntMProfile,
      LegalTransactionSource,
      LglCntntMRtntnPerdStartDate,
      _Context.LglCntntMContext                                                                            as LglCntntMContext,
      _Context.LglCntntMContextTitle                                                                       as LglCntntMContextTitle,
      _LegalTransactionDateTP.LglCntntMFromDate                                                            as LglCntntMFromDate,
      _LegalTransactionEndDateTP.LglCntntMFromDate                                                         as LglCntntMToDate,
      LglCntntMGovLaw,
      _LglCntntMGovLawText[1: Language = $session.system_language].LglCntntMGovLawText                     as LglCntntMGovLawText,
      LglTransAccessLvl,
      _LglCntntMAccessLvlText[1: Language = $session.system_language].LglCntntMAccessLvlName               as LglCntntMAccessLvlName,
      LglCntntMMainOrgType,
      LglCntntMMainOrgCoCode,
      _SalesOrganizationText[1: Language = $session.system_language].SalesOrganizationName                 as SalesOrganizationName,
      LglCntntMMainOrgSalesOrg,

      LglCntntMMainOrgPurOrg,
      LglCntntMIntegrationType,
      _LegalTransactionHealthText.LegalTransactionHealthName                                               as LegalTransactionHealthName,
      _LglCntntMMainOrgTypeText.LglCntntMMainOrgTypeName                                                   as LglCntntMMainOrgTypeName,
      _MainLegalTransactionEntity.LglCntntMEntityType                                                      as LglCntntMEntityType,
      _MainLegalTransactionEntity.LglCntntMEntityTypeName                                                  as LglCntntMEntityTypeName,
      _MainLegalTransactionEntity.LglCntntMEntity                                                          as LglCntntMEntity,
      //   _LegalTrEntityAggr.LglCntntMEntityName                                                               as LglCntntMEntityName,

      cast(case
        when _LegalTransactionEndDateTP.LglCntntMFromDate is initial or _LegalTransactionEndDateTP.LglCntntMFromDate = '00000000'
        or _LegalTransactionEndDateTP.LglCntntMFromDate < $session.system_date then
        cast ( -1 as lcm_ltexpired_days )
        else

        dats_days_between( cast($session.system_date as lcm_valid_from  preserving type ) ,_LegalTransactionEndDateTP.LglCntntMFromDate )
        end as lcm_ltexpired_days preserving type  )                                                       as LegalTransExpirationInDays,

      case
      when I_LegalTransaction.LglCntntMMainOrgType = '04' then
           _SalesOrganizationText.SalesOrganizationName
      when I_LegalTransaction.LglCntntMMainOrgType = '05' then
           _PurchasingOrganization.PurchasingOrganizationName
        else
           _CompanyCode.CompanyCodeName
        end                                                                                                as LglCntntMMainOrgName,

      @DefaultAggregation: #SUM
      case
       when  LegalTransactionHealth = '0006' or LegalTransactionHealth = '0005' or LegalTransactionHealth = '0004' then
         cast (1 as abap.int4(10) )
       else
         cast (0 as abap.int4(10) )
       end                                                                                                 as LegalTransTotNmbrOfFinalStatus,

      @DefaultAggregation: #SUM
      case
       when  LegalTransactionHealth = '0002' or  LegalTransactionHealth = '0003'  then
         cast (1 as abap.int4(10) )
       else
         cast (0 as abap.int4(10) )
       end                                                                                                 as LegalTransTotNrOfOpenStatus,


      @DefaultAggregation: #SUM
      case
      when  LegalTransactionHealth = '0001' or  LegalTransactionHealth = '0002' or  LegalTransactionHealth = '0003'  then
         cast (1 as abap.int4(10) )
       else
         cast (0 as abap.int4(10) )
       end                                                                                                 as TotalNrOfActiveLglTransactions,

      @DefaultAggregation: #SUM
      case
       when  LegalTransactionHealth = '0001'  then
         cast (1 as abap.int4(10) )
       else
         cast (0 as abap.int4(10) )
       end                                                                                                 as LegalTransTotNmbrOfOnTrackSts,


      cast( 1 as  lcm_totallt )                                                                            as TotalNumberOfLegalTransactions,

      @DefaultAggregation: #SUM
      case
       when _LTRnwlAndTermn.LglCntntMNmbrOfRenewals > 0  then
         cast (1 as abap.int4(10) )
       else
         cast (0 as abap.int4(10) )
       end                                                                                                 as LglTransTotNmbrOfAutoRenewed,

      cast(
        case
            when _LegalTransactionNxtTermnTP.LglCntntMFromDate is initial
              or _LegalTransactionNxtTermnTP.LglCntntMFromDate = '00000000'
              or _LegalTransactionNxtTermnTP.LglCntntMFromDate < $session.system_date
                then cast ( -1 as lcm_lttermination_days )
              else
                dats_days_between( cast($session.system_date as lcm_valid_from  preserving type ) ,_LegalTransactionNxtTermnTP.LglCntntMFromDate )
        end as lcm_lttermination_days preserving type  )                                                   as LegalTransTerminationInDays,

      cast(
        case
            when (
                  ( _LTRnwlAndTermn.LglCntntMRnwlType = '01' and (
                    _LegalTransactionNxtRnwlTP.LglCntntMFromDate is initial
                 or _LegalTransactionNxtRnwlTP.LglCntntMFromDate = '00000000'
                 or _LegalTransactionNxtRnwlTP.LglCntntMFromDate < $session.system_date ) )
               or ( _LTRnwlAndTermn.LglCntntMRnwlType = '02' and (
                    _LegalTransactionEndDateTP.LglCntntMFromDate is initial
                 or _LegalTransactionEndDateTP.LglCntntMFromDate = '00000000'
                 or _LegalTransactionEndDateTP.LglCntntMFromDate < $session.system_date ) )
                 ) then cast ( -1 as lcm_ltexpired_days )

            when _LTRnwlAndTermn.LglCntntMRnwlType = '01' then
                    dats_days_between( cast($session.system_date as lcm_valid_from  preserving type ) ,_LegalTransactionNxtRnwlTP.LglCntntMFromDate )

            when _LTRnwlAndTermn.LglCntntMRnwlType = '02'  then
                    dats_days_between( cast($session.system_date as lcm_valid_from  preserving type ) ,_LegalTransactionEndDateTP.LglCntntMFromDate )
            else
                cast ( -1 as lcm_ltexpired_days )
        end as lcm_ltexpired_days preserving type  )                                                       as LegalTransRenewalInDays,

      @DefaultAggregation: #SUM
      case _LTRnwlAndTermn.LglCntntMNmbrOfRenewals + _LTRnwlAndTermn.LglCntntMNrOfActiveRenewal
       when 0  then
         cast (0 as lcm_tot_nmbr_of_renewed )
       else
        case LegalTransactionHealth
            when '0001'
                then
                cast (1 as lcm_tot_nmbr_of_renewed )
            when '0002'
                then
                cast (1 as lcm_tot_nmbr_of_renewed )
            when '0003'
                then
                cast (1 as lcm_tot_nmbr_of_renewed )
            else
                cast (0 as lcm_tot_nmbr_of_renewed )
            end
       end                                                                                                 as LegalTransTotNmbrOfRenewed,

      cast(case
        when _LTRnwlAndTermn.LglCntntMRnwlType = '01' 
         and LegalTransactionHealth = '0006'
            then 1
            else 0
        end as lcm_tot_exp_without_renewal )                                                               as TotNrOfExprdLglTransWthoutRnwl,

      _LTRnwlAndTermn._LglCntntMRnwlTypeText[1: Language = $session.system_language].LglCntntMRnwlTypeName as LglCntntMRnwlTypeName,
      _LTRnwlAndTermn.LglCntntMRnwlType                                                                    as LglCntntMRnwlType,
      _LegalTrLinkedObjectsTP.LglCntntMLinkdObjTypeName                                                    as LglCntntMLinkdObjTypeName,
      _ChangedBy,
      _CompanyCode,
      _CreatedBy,
      _LanguageText,
      _LegalTransactionHealthText,
      _LglCntntMGovLawText,
      _LglCntntMMainOrgTypeText,
      _MainLegalTransactionEntity,
      _PurchasingOrganization,
      _SalesOrganizationText,
      _SalesOrganization,
      _LTRnwlAndTermn,
      _LegalTrEntityAggr,
      _LCMEntityTypeText,
      _CalendarDate,
      _LegalTrLinkedObjectsTP,
      _LegalTransactionEndDateTP,
      _LegalTransactionDateTP
}
where
  IsEndOfPurposeBlocked = ''