P_Konvd_NetDry_Prep_Data

//@EndUserText.label: 'Base View for NetDry Reports'

@ClientHandling.algorithm: #SESSION_VARIABLE
@AccessControl.authorizationCheck: #NOT_REQUIRED
@AbapCatalog: { sqlViewName: 'PKONVDNDPREPDT',
                compiler.compareFilter: true }
@VDM: { viewType: #COMPOSITE,
        private: true }
@ObjectModel.usageType:{  sizeCategory: #XL,
                          serviceQuality: #D,
                          dataClass: #TRANSACTIONAL }

@AbapCatalog.preserveKey:true


define view P_Konvd_NetDry_Prep_Data
  with parameters
    P_DisplayView           : cds_view_uom,
    P_WeightedAvgReportType : cds_netdry_report_type,
    P_WeightedAvgFromDate   : cds_from_date,
    P_WeightedAvgFromTime   : cds_from_time,
    P_WeightedAvgToDate     : cds_to_date,
    P_WeightedAvgToTime     : cds_to_time

  as select from    P_Konvd_NetDry_SelectData_TF as Konvd
    left outer to one join I_VersionedLogisticDocument  as Vlogp        on  Vlogp.PricingKey          = Konvd.PricingKey
                                                                 and Vlogp.Version             = '0000000000'
                                                                 and Vlogp.PricingDocumentItem = Konvd.PricingDocumentItem
                                                                 and Vlogp.ConditionType       = Konvd.ConditionType
                                                                 and Vlogp.SubConditionType    = Konvd.SubConditionType

    left outer to many join I_DiscPremQltySched          as DPQS         on  DPQS.DiscPremQtyScheduleUniqueID   = Konvd.DiscPremQtyScheduleUniqueID
                                                                 and DPQS.DiscPremQtyScheduleVersion    = Konvd.DiscPremQtyScheduleVersion
                                                                 and DPQS.DiscPremQtySchedValdtyVersion = '001'

    left outer to many join P_NetDry_NonNumeric_Attr_TF  as NonNumAttrib on  NonNumAttrib.QuantityRepositoryUUID    = Konvd.QuantityRepositoryUUID
                                                                 and NonNumAttrib.QuantityRepositoryVersion = Konvd.QuantityRepositoryVersion


{
  key Konvd.PricingKey,
  key Konvd.PricingDocumentItem,
  key Konvd.ConditionType,
  key Konvd.SubConditionType,
  key Konvd.VersStockRecordUUID,
      Konvd.MTMDataSourceType,
      Konvd.SourceTransactionEventDateTime,
      Konvd.SourceTransacEventEndDateTime,


      @Semantics.unitOfMeasure: true
      MaterialBaseUnit,
      @Semantics.unitOfMeasure: true
      Konvd.CommodityUnit,
      @Semantics.unitOfMeasure: true
      Konvd.CmmdtyPriceExpsrMassUnit,
      @Semantics.unitOfMeasure: true
      Konvd.CmmdtyPriceExpsrVolumeUnit,

      @Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
      @DefaultAggregation:#NONE
      Konvd.CommodityQuantityInBaseUnit,
      @Semantics.quantity.unitOfMeasure: 'CommodityUnit'
      @DefaultAggregation:#NONE
      Konvd.CommodityQuantity,
      @Semantics.quantity.unitOfMeasure: 'CmmdtyPriceExpsrMassUnit'
      @DefaultAggregation:#NONE
      Konvd.CmmdtyPriceExpsrQtyInMassUnit,
      @Semantics.quantity.unitOfMeasure: 'CmmdtyPriceExpsrVolumeUnit'
      @DefaultAggregation:#NONE
      Konvd.CmmdtyPriceExpsrQtyInVolUnit,

      @Semantics.quantity.unitOfMeasure: 'CommodityExposureUnit'
      @DefaultAggregation:#NONE
      case $parameters.P_DisplayView
        when 'P' -- Physical Commodity UoM
          then Konvd.CommodityQuantity
        when 'M' -- Mass UoM
           then Konvd.CmmdtyPriceExpsrQtyInMassUnit
        when 'V' -- Volume UoM
           then Konvd.CmmdtyPriceExpsrQtyInVolUnit
        else Konvd.CommodityQuantity
      end                                                                                                                     as CommodityExposureQuantity,

      @Semantics.unitOfMeasure: true
      case $parameters.P_DisplayView
         when 'P' -- Physical Commodity UoM
           then Konvd.CommodityUnit
         when 'M' -- Mass Uom
           then    Konvd.CmmdtyPriceExpsrMassUnit
         when 'V' -- Volume Uom
      then   Konvd.CmmdtyPriceExpsrVolumeUnit
         else Konvd.CommodityUnit
       end                                                                                                                    as CommodityExposureUnit,
      Konvd.QuantitySign,
      Konvd.SourceDocument,
      Konvd.SourceDocumentShortNumber,
      Konvd.SourceDocumentItem,
      Konvd.SourceDocumentYear,
      Konvd.SourceDocumentBusObjType,
      Konvd.MTMConditionGroup,
      Konvd.MTMConditionGroupCategory,
      GoodsMovementType,
      Konvd.StockRefDocument,
      Konvd.StockRefDocumentItem,
      Konvd.StockRefDocType,
      Konvd.RefDocumentYear,

      --Include Structure for Net Dry Quantity (Weighted Average)
      --(Structure: /ACCGO/ACM_S_KONVD_NETDRY)
      Konvd.LoadDataCaptureObjectID,
      Konvd.LoadDataCaptureEventType,
      Konvd.QuantityRepositoryUUID,
      Konvd.QuantityRepositoryVersion,
      @Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
      @DefaultAggregation:#NONE
      NetPriceExpsrQtyInBaseUnit                                                                                              as NetPriceExpsrQtyInBaseUnit,
      @Semantics.quantity.unitOfMeasure: 'CommodityUnit'
      @DefaultAggregation:#NONE
      Konvd.NetPriceExpsrQuantity                                                                                             as NetPriceExpsrQuantity,

      @Semantics.quantity.unitOfMeasure: 'CmmdtyPriceExpsrMassUnit'
      @DefaultAggregation:#NONE
      Konvd.NetPriceExpsrQtyInMassUnit                                                                                        as NetPriceExpsrQtyInMassUnit,
      @Semantics.quantity.unitOfMeasure: 'CmmdtyPriceExpsrVolumeUnit'
      @DefaultAggregation:#NONE
      Konvd.NetPriceExpsrQtyInVolumeUnit                                                                                      as NetPriceExpsrQtyInVolumeUnit,
      @Semantics.quantity.unitOfMeasure: 'CommodityExposureUnit'
      @DefaultAggregation:#NONE
      case $parameters.P_DisplayView
        when 'P' -- Physical Commodity UoM
          then Konvd.NetPriceExpsrQuantity
        when 'M' -- Mass UoM
           then NetPriceExpsrQtyInMassUnit
        when 'V' -- Volume UoM
           then NetPriceExpsrQtyInVolumeUnit
        else Konvd.CommodityQuantity
      end                                                                                                                     as NetExposureQuantity,
      Konvd.DiscPremQtyScheduleUniqueID,
      Konvd.DiscPremQtyScheduleVersion,
      Konvd.DiscPremVolSchedEvalIsRqd,
      Konvd.RecordIsLogicallyDeleted,
      Konvd.NetDryErrorCode,
      Konvd.DiscPremQtyScheduleType,
      Konvd.ACMSourceDocument,

      -- Parameters
      $parameters.P_WeightedAvgReportType                                                                                     as NetDryReportType,
      $parameters.P_WeightedAvgFromDate                                                                                       as FromDate,
      $parameters.P_WeightedAvgFromTime                                                                                       as FromTime,
      $parameters.P_WeightedAvgToDate                                                                                         as ToDate,
      $parameters.P_WeightedAvgToTime                                                                                         as ToTime,

      -- Creating Timestamps from Parameter Date and Time
      dats_tims_to_tstmp( $parameters.P_WeightedAvgFromDate,
                          $parameters.P_WeightedAvgFromTime,
                          'UTC',
                          $session.client,
                          'NULL' )                                                                                            as NetDryReportFromTimeStamp,

      dats_tims_to_tstmp( $parameters.P_WeightedAvgToDate,
                          $parameters.P_WeightedAvgToTime,
                          'UTC',
                          $session.client,
                          'NULL' )                                                                                            as NetDryReportToTimeStamp,

      -- CMM_VLOGP Data
      Vlogp.Plant,
      Vlogp.Material,
      Vlogp.StorageLocation,
      Vlogp.PhysicalCommodity,
      Vlogp.CompanyCode,
      Vlogp.EndOfDayBusinessEntityKey,
      Vlogp.QuotationKeyType,
      Vlogp.MTMCalculationGroup,

      -- DPQS Fields
      DPQS.DiscPremQtyScheduleName                                                                                            as DPQSValueScheduleName,
      DPQS.DPQSVolumeSchedule                                                                                                 as DPQSVolumeSchedule,

      cast(NonNumAttrib.NonNumericAttrib1 as abap.char( 30 ))                                                                 as NonNumericAttrib1,
      cast(NonNumAttrib.NonNumericAttribValue1 as abap.char( 70 ))                                                            as NonNumericAttribValue1,
      cast(NonNumAttrib.NonNumericAttrib2 as abap.char( 30 ))                                                                 as NonNumericAttrib2,
      cast(NonNumAttrib.NonNumericAttribValue2 as abap.char( 70 ))                                                            as NonNumericAttribValue2,
      cast(NonNumAttrib.NonNumericAttrib3 as abap.char( 30 ))                                                                 as NonNumericAttrib3,
      cast(NonNumAttrib.NonNumericAttribValue3 as abap.char( 70 ))                                                            as NonNumericAttribValue3,
      cast(NonNumAttrib.NonNumericAttrib4 as abap.char( 30 ))                                                                 as NonNumericAttrib4,
      cast(NonNumAttrib.NonNumericAttribValue4 as abap.char( 70 ))                                                            as NonNumericAttribValue4,
      cast(NonNumAttrib.NonNumericAttrib5 as abap.char( 30 ))                                                                 as NonNumericAttrib5,
      cast(NonNumAttrib.NonNumericAttribValue5 as abap.char( 70 ))                                                            as NonNumericAttribValue5,
      cast(NonNumAttrib.NonNumericAttrib6 as abap.char( 30 ))                                                                 as NonNumericAttrib6,
      cast(NonNumAttrib.NonNumericAttribValue6 as abap.char( 70 ))                                                            as NonNumericAttribValue6,
      cast(NonNumAttrib.NonNumericAttrib7 as abap.char( 30 ))                                                                 as NonNumericAttrib7,
      cast(NonNumAttrib.NonNumericAttribValue7 as abap.char( 70 ))                                                            as NonNumericAttribValue7,
      cast(NonNumAttrib.NonNumericAttrib8 as abap.char( 30 ))                                                                 as NonNumericAttrib8,
      cast(NonNumAttrib.NonNumericAttribValue8 as abap.char( 70 ))                                                            as NonNumericAttribValue8,
      cast(NonNumAttrib.NonNumericAttrib9 as abap.char( 30 ))                                                                 as NonNumericAttrib9,
      cast(NonNumAttrib.NonNumericAttribValue9 as abap.char( 70 ))                                                            as NonNumericAttribValue9,
      cast(NonNumAttrib.NonNumericAttrib10 as abap.char( 30 ))                                                                as NonNumericAttrib10,
      cast(NonNumAttrib.NonNumericAttribValue10 as abap.char( 70 ))                                                           as NonNumericAttribValue10,

      NonNumAttrib.NonNumericAttribCount,

      cast(concat(NonNumAttrib.NonNumericAttrib1 , concat( ': ', NonNumAttrib.NonNumericAttribValue1)) as abap.char( 102 ))   as ConcatenatedNonNumericAttrib1,
      cast(concat(NonNumAttrib.NonNumericAttrib2 , concat( ': ', NonNumAttrib.NonNumericAttribValue2)) as abap.char( 102 ))   as ConcatenatedNonNumericAttrib2,
      cast(concat(NonNumAttrib.NonNumericAttrib3 , concat( ': ', NonNumAttrib.NonNumericAttribValue3)) as abap.char( 102 ))   as ConcatenatedNonNumericAttrib3,
      cast(concat(NonNumAttrib.NonNumericAttrib4 , concat( ': ', NonNumAttrib.NonNumericAttribValue4)) as abap.char( 102 ))   as ConcatenatedNonNumericAttrib4,
      cast(concat(NonNumAttrib.NonNumericAttrib5 , concat( ': ', NonNumAttrib.NonNumericAttribValue5)) as abap.char( 102 ))   as ConcatenatedNonNumericAttrib5,
      cast(concat(NonNumAttrib.NonNumericAttrib6 , concat( ': ', NonNumAttrib.NonNumericAttribValue6)) as abap.char( 102 ))   as ConcatenatedNonNumericAttrib6,
      cast(concat(NonNumAttrib.NonNumericAttrib7 , concat( ': ', NonNumAttrib.NonNumericAttribValue7)) as abap.char( 102 ))   as ConcatenatedNonNumericAttrib7,
      cast(concat(NonNumAttrib.NonNumericAttrib8 , concat( ': ', NonNumAttrib.NonNumericAttribValue8)) as abap.char( 102 ))   as ConcatenatedNonNumericAttrib8,
      cast(concat(NonNumAttrib.NonNumericAttrib9 , concat( ': ', NonNumAttrib.NonNumericAttribValue9)) as abap.char( 102 ))   as ConcatenatedNonNumericAttrib9,
      cast(concat(NonNumAttrib.NonNumericAttrib10 , concat( ': ', NonNumAttrib.NonNumericAttribValue10)) as abap.char( 102 )) as ConcatenatedNonNumericAttrib10,

      cast(NonNumAttrib.AllNonNumericAttrib as abap.char( 1333 ))                                                             as AllNonNumericAttrib,
      PreviousVersStockRecordUUID
}
where
  (
       Konvd.MTMDataSourceType        = '02'
    or Konvd.MTMDataSourceType        = 'A2'
  )
  and  Konvd.RecordIsLogicallyDeleted = ' '
  and(
       Konvd.SourceDocumentBusObjType = 'BUS2017'
    or Konvd.SourceDocumentBusObjType = 'BUS2028'
    or Konvd.SourceDocumentBusObjType = ' '
  )
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_DISCPREMQLTYSCHED",
"I_VERSIONEDLOGISTICDOCUMENT"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/