P_ContrWithRelevantValidity

@AbapCatalog.sqlViewName: 'PCRELVAL'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@VDM.private: true
@VDM.viewType: #COMPOSITE

@ObjectModel.usageType.serviceQuality: #B
@ObjectModel.usageType.sizeCategory: #L
@ObjectModel.usageType.dataClass: #MIXED
define view P_ContrWithRelevantValidity
  as select from I_PurchaseContractItem      as _PurchaseContractItem
 // association        to R_PurchaseContract   as _PurContract on $projection.PurchaseContract = _PurContract.PurchaseContract

  association        to I_PurContractClassification as _PurContractClassification on  $projection.PurchaseContractType = _PurContractClassification.PurchasingDocumentType
  //1905 - Consumption & Relevance

  association [1..*] to I_PurchaseContractHistory   as _PurchaseContractHistory   on  $projection.PurchaseContract                       = _PurchaseContractHistory.PurchaseContract
                                                                                  and $projection.PurchaseContractItem                   = _PurchaseContractHistory.PurchaseContractItem
                                                                                  and _PurchaseContractHistory.ReleaseOrderItemIsDeleted = ''
{
  key _PurchaseContractItem.PurchaseContract,

  key _PurchaseContractItem.PurchaseContractItem,

  key _PurchaseContractItem._PurchaseContractHistory.ReleaseOrder,

  key _PurchaseContractItem._PurchaseContractHistory.ReleaseOrderItem,

      _PurchaseContractItem.PurchasingObjectCategory                                                                                   as Purchasingdocumentcategory, 
      
      _PurchaseContractItem.PurchaseContractType,

      @Semantics.currencyCode:true
      _PurchaseContractItem.DocumentCurrency,

     _PurchaseContractItem.OutlineAgreementTargetAmount                                                                                 as PurchaseContractTargetAmount,

      @Semantics.quantity.unitOfMeasure: 'ReleaseOrderItemQuantityUnit'
      //      _PurchaseContractItem._PurchaseContractHistory.ReleaseOrderItemOrderQuantity,

      _PurchaseContractItem._PurchaseContractHistory.ReleaseOrderItemOrderQuantity,

      _PurContractClassification.PurchaseContractClassification,

      case
         when _PurContractClassification.PurchaseContractClassification = '1'
          then
              _PurchaseContractItem._PurchaseContractHistory.ReleaseOrderItemNetAmount
         else 0
       end                                                                                                                               as ConsumedValueForQuantity,
      
      _PurchaseContractItem.TargetQuantity,
      
      _PurchaseContractItem.TargetAmount,

      @Semantics.currencyCode:true
      _PurchaseContractItem.DocumentCurrency                                                                                                 as ReleaseOrderCurrency,

      @Semantics.amount.currencyCode: '_PurContract.DocumentCurrency'
      @DefaultAggregation:#SUM
      cast( currency_conversion
      (
        amount             => _PurchaseContractItem._PurchaseContractHistory.ReleaseOrderItemNetAmount,
        source_currency    => _PurchaseContractItem._PurchaseContractHistory.ReleaseOrderCurrency,
        target_currency    => _PurchaseContractItem.DocumentCurrency,
        exchange_rate_date => _PurchaseContractItem._PurchaseContractHistory.ReleaseOrderItemLastChgDate,
        exchange_rate_type => 'M',
        error_handling     => 'SET_TO_NULL'               // 3347297

      ) as abap.curr( 28, 3 ) )                                                                                                          as ReleaseOrderItemNetAmount,

      _PurchaseContractItem._PurchaseContractHistory.ReleaseOrderItemIsDeleted,

      _PurchaseContractItem._PurchaseContractHistory.ReleaseOrderItemLastChgDate,

      _PurchaseContractItem._PurchaseContractHistory.ReleaseOrderItemQuantityUnit,

      _PurContractClassification,

      _PurchaseContractItem.PurchaseContractCreatedByUser                                                                               as CreatedByUser,

      case
      when _PurchaseContractItem.PurContrValidityEndDate is initial   then '99991231'
      else _PurchaseContractItem.PurContrValidityEndDate
      end                                                                                                                                as ValidityEndDate,

      _PurchaseContractItem.PurContrValidityStartDate                                                                                    as ValidityStartDate,

      case
      when _PurchaseContractItem.PurContrValidityEndDate is initial
      then dats_days_between(cast( substring( cast( tstmp_current_utctimestamp() as abap.char(17) ), 1, 8 ) as abap.dats), cast ('99991231' as abap.dats))
      else dats_days_between(cast( substring( cast( tstmp_current_utctimestamp() as abap.char(17) ), 1, 8 ) as abap.dats), _PurchaseContractItem.PurContrValidityEndDate)
      end                                                                                                                                as TimeVarianceInDays,

      dats_days_between(cast( substring( cast( tstmp_current_utctimestamp() as abap.char(17) ), 1, 8 ) as abap.dats), _PurchaseContractItem.PurContrValidityStartDate) as PurContrValidityDateDifference,

      case 
      when _PurchaseContractItem.PurContrValidityEndDate is initial
      then dats_days_between(cast ('99991231' as abap.dats),_PurchaseContractItem.PurContrValidityStartDate)
      else dats_days_between(_PurchaseContractItem.PurContrValidityEndDate, _PurchaseContractItem.PurContrValidityStartDate)
      end                                                                                                                                as PurContrValidityTotalDays, 
  
      3650                                                                                                                               as Max_notexpired,
      0                                                                                                                                  as Min_expired,
      _PurchaseContractItem.PurchasingGroup,
      
      _PurchaseContractItem.ActivePurchasingDocument,

      //Assosiations

      _PurchaseContractHistory
}
where
  _PurchaseContractItem._PurchaseContract.PurchasingDocumentDeletionCode = ''