P_STANDARDPURCHASEORDERS

@AbapCatalog.sqlViewName: 'PSTDPURORDS'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.viewType: #COMPOSITE
@VDM.private: true
@ClientHandling.algorithm: #SESSION_VARIABLE

define view P_StandardPurchaseOrders
  with parameters
    p_days : abap.int2,
    p_date : sydatum
  as select from    I_GoodsMovementDocument                                                               as _matmovdoc
    inner join      I_DemandDrivenPurDocItem                                                              as _ekpo      on  _matmovdoc.PurchaseOrder     = _ekpo.PurchasingDocument
                                                                                                                        and _matmovdoc.PurchaseOrderItem = _ekpo.PurchasingDocumentItem
    inner join      I_DemandDrivenPurDocHeader                                                            as _ekko      on _matmovdoc.PurchaseOrder = _ekko.PurchasingDocument

    inner join      P_DemandDrivenProdslHorizon(p_days : $parameters.p_days, p_date : $parameters.p_date) as _hrzn      on  _ekpo.Material = _hrzn.Material
                                                                                                                        and _ekpo.Plant    = _hrzn.Plant
                                                                                                                        and _ekpo.MRPArea  = _hrzn.MRPArea

    left outer join P_ChangeDocumentItems                                                                  as _changedoc on  _changedoc.ChangeDocObject             = _ekko.PurchasingDocument
                                                                                                                        and _changedoc.DatabaseTable               = 'EKKO'
                                                                                                                        and _changedoc.ChangeDocDatabaseTableField = 'PROCSTAT'
                                                                                                                        and _changedoc.ChangeDocNewFieldValue      = '05'
                                                                                                                        and _changedoc.ChangeDocObjectClass        = 'EINKBELEG'




  association [1..1] to I_DemandDrivenProduct as _ddprod on  _ddprod.Material = _matmovdoc.Material
                                                         and _ddprod.Plant    = _matmovdoc.Plant
                                                         and _ddprod.MRPArea  = _ekpo.MRPArea

{
       //@ObjectModel.foreignKey.association: '_Material'

  key  _matmovdoc.Material                                                                                       as Material,
  key  _matmovdoc.Plant                                                                                          as Plant,
  key  _ekpo.MRPArea                                                                                             as MRPArea,
       _ekko.PurchasingDocument                                                                                  as PurchasingDocument,
       //  _ekko.CreationDate                                                          as ActualStartDate,

       //  _matmovdoc.PostingDate                                                      as ActualEndDate,

       cast(_ekpo.PurchasingDocumentItem as abap.char( 5 ))                                                      as PurchasingDocumentItem,
       _ekpo.PurchasingInfoRecord                                                                                as PurchasingInfoRecord,
       _ekko.PurchasingOrganization                                                                              as PurchasingOrganization,

       //_ekko.bsart as PurchasingDocumentType,

       cast('B' as abap.char( 1 ) )                                                                              as ReplenishmentType,

       _ekko.CreationDate                                                                                        as CreationDate,

       _changedoc.CreationDate                                                                                   as ReleaseDate,
       _ekko.PurchasingDocumentCategory                                                                          as PurchasingDocumentCategory,


       //       case

       //         when _ekko.CreationDate = _matmovdoc.PostingDate

       //           then 1

       //         else

       //           dats_days_between(_ekko.CreationDate,_matmovdoc.PostingDate)

       //       end                                                                               as DifferenceInDays,


       cast(
         case
         when _ddprod.LeadTimeCalculationMethod = 'HCRE'
         then
            _ekko.CreationDate
         when _ddprod.LeadTimeCalculationMethod = 'HREL'
         then
            _changedoc.CreationDate
         when _ddprod.LeadTimeCalculationMethod is null
         then
          _ekko.CreationDate
         end                                                                                  as abap.dats )     as StartDate,

       case
         when _ddprod.LeadTimeCalculationMethod = 'HCRE'
         then
           case
             when _ekko.CreationDate = _matmovdoc.PostingDate
              then
               1
             else
               dats_days_between(_ekko.CreationDate,_matmovdoc.PostingDate)
           end
         when _ddprod.LeadTimeCalculationMethod = 'HREL'
         then
             case
             when _changedoc.CreationDate = _matmovdoc.PostingDate
             then
              1
             else
              dats_days_between(_changedoc.CreationDate,_matmovdoc.PostingDate)
             end
          when _ddprod.LeadTimeCalculationMethod is null
          then
           case
             when _ekko.CreationDate = _matmovdoc.PostingDate
              then
               1
             else
               dats_days_between(_ekko.CreationDate,_matmovdoc.PostingDate)
           end
           end                                                                                                   as DifferenceInDays

}
where
          _matmovdoc.StockChangeCategory          =       'GR'
  and(    /*_ekko.bstyp = 'L' or*/






          _ekko.PurchasingDocumentCategory        =       'F'
  )
  and     _ekpo.PurchasingDocumentItemCategory    <>      '7'
  and     _matmovdoc.IsCompletelyDelivered        =       'X'
  and(
          //StartDate                            between _hrzn.StartDate and _hrzn.EndDate


          (
            _ddprod.LeadTimeCalculationMethod     =       'HCRE'
            and _ekko.CreationDate                between _hrzn.StartDate and _hrzn.EndDate
          )
    or(
          _ddprod.LeadTimeCalculationMethod       =       'HREL'
      and _changedoc.CreationDate between _hrzn.StartDate and _hrzn.EndDate
      and _changedoc.CreationDate <= _matmovdoc.PostingDate //For the old Goods movements date may be older than the current release date, in that case ignoring that record as that is not a valid business case

    )

  )
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_DEMANDDRIVENPRODUCT",
"I_DEMANDDRIVENPURDOCHEADER",
"I_DEMANDDRIVENPURDOCITEM",
"I_GOODSMOVEMENTDOCUMENT",
"P_CHANGEDOCUMENTITEMS",
"P_DEMANDDRIVENPRODSLHORIZON"
],
"ASSOCIATED":
[
"I_DEMANDDRIVENPRODUCT"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/