P_PurReqnOpenQuantCalc

@AbapCatalog.sqlViewName: 'PPROPENQUANCALC'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #PRIVILEGED_ONLY
@EndUserText.label: 'P view for Open Quantity Calculation'
@VDM.private: true
@VDM.viewType: #CONSUMPTION
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.sizeCategory: #L
@ObjectModel.usageType.dataClass: #MIXED
@ClientHandling.algorithm: #SESSION_VARIABLE

define view P_PurReqnOpenQuantCalc
  as select from I_Purchaserequisitionitem as _Purchaserequisitionitem

  //  association [0..1] to I_Supplier                     as _Supplier                    on  $projection.Supplier = _Supplier.Supplier


  //association [0..1] to I_MaterialGroup               as _MaterialGroup             on  $projection.MaterialGroup = _MaterialGroup.MaterialGroup // 2741424


  association [0..1] to I_PurgDocumentItemCategory    as _PurgDocumentItemCategory    on  $projection.PurchasingDocumentItemCategory = _PurgDocumentItemCategory.PurchasingDocumentItemCategory

  ////  association [0..1] to I_Supplier                     as _DesiredSupplier             on  $projection.ExtDesiredSupplierForPurg = _DesiredSupplier.Supplier


  association [0..1] to I_PRReleaseIndicatorDesc      as _ReleaseIndicatorDesc        on  _ReleaseIndicatorDesc.PurchasingReleaseStatus = $projection.ReleaseCode
                                                                                      and _ReleaseIndicatorDesc.Language                = $session.system_language

  association [0..1] to C_PurReqnCreationInd          as _PurchaseReqnOrigin          on  _PurchaseReqnOrigin.DomainValue = $projection.PurReqnOrigin

  association [0..1] to I_User                        as _User                        on  _User.UserID = $projection.CreatedByUser

  association [0..1] to I_PurchasingCategoryMatlGroup as _PurchasingCategoryMatlGroup on  _Purchaserequisitionitem.MaterialGroup = _PurchasingCategoryMatlGroup.MaterialGroup //2741424


  //  association[1..1] to I_ProcurementDomainValues                     as _DomainText                  on  _DomainText.Name = 'VDM_BLOCKED_SUPPLIER'

  //                                                                                      and  _DomainText.Language = $session.system_language

  //                                                                                      and  _DomainText.DomainValue = 'X'


  association [0..1] to I_ProcmtBlockedSupplier       as _ProcmtBlockedSupplier       on  _ProcmtBlockedSupplier.PurchasingIsBlockedForSupplier = 'X'

  association [0..*] to I_ReleaseStrategyDesc         as _ReleaseStrategyDesc         on  _ReleaseStrategyDesc.PurchasingReleaseStrategy = $projection.ReleaseStrategy
                                                                                      and _ReleaseStrategyDesc.ReleaseGroup              = $projection.ReleaseGroup

  association [0..1] to C_PurchaseReqnSourceType      as _PurchaseReqnSourceType      on  _PurchaseReqnSourceType.DomainValue = $projection.PurchasingSourceType

  //  association [1..1] to P_PurReqOpenValCalc            as _PurReqOpenValCalc          on  _PurReqOpenValCalc.PurchaseRequisition     = $projection.PurchaseRequisition

  //                                                                                      and _PurReqOpenValCalc.PurchaseRequisitionItem = $projection.PurchaseRequisitionItem


  association [1..1] to I_CalendarDate                as _CalendarDate                on  _CalendarDate.CalendarDate = $session.system_date

{

  key _Purchaserequisitionitem.PurchaseRequisition,

  key _Purchaserequisitionitem.PurchaseRequisitionItem,

      _PurchasingCategoryMatlGroup.PurgCatUUID,

      _Purchaserequisitionitem.MaterialGroup,

      _PurchasingCategoryMatlGroup._PurchasingCategory.PurchasingCategory,

      //_PurchasingCategoryMatlGroup._MaterialGroup,


      _Purchaserequisitionitem.PurchasingOrganization,

      _Purchaserequisitionitem.PurchasingDocumentCategory,

      _Purchaserequisitionitem.PurchaseRequisitionType,

      _Purchaserequisitionitem.FixedSupplier, //// as Supplier ,


      _Purchaserequisitionitem.SupplyingPlant,

      _Purchaserequisitionitem.AccountAssignmentCategory,

      _Purchaserequisitionitem.Material,

      _Purchaserequisitionitem.Plant,

      _Purchaserequisitionitem.BaseUnit,

      _Purchaserequisitionitem.PurchasingDocumentItemCategory,

      _Purchaserequisitionitem.PurchasingDocument,

      _Purchaserequisitionitem.PurchasingGroup,


      //      case when  _Purchaserequisitionitem.DeliveryDate < $session.system_date and _Purchaserequisitionitem.DeliveryDate is not null and _Purchaserequisitionitem.DeliveryDate <> '00000000'

      //         then '1'

      //         else '2'

      //         end    as  IsOverdue,

      _Purchaserequisitionitem.DeliveryDate,

      _Purchaserequisitionitem.RequestedQuantity,

      _Purchaserequisitionitem.PurchaseRequisitionPrice,

      _Purchaserequisitionitem.PurReqnPriceQuantity,

      _Purchaserequisitionitem.PurReqnItemCurrency,

      _PurgDocumentItemCategory._Text[1: Language = $session.system_language ].PurgDocExternalItemCategory,

      _PurgDocumentItemCategory._Text[1: Language = $session.system_language ].PurgDocItemCategoryName,

      _Purchaserequisitionitem.RequirementTracking,
      _Purchaserequisitionitem.PurchaseRequisitionItemText,

      _Purchaserequisitionitem.Supplier, //// as ExtDesiredSupplierForPurg ,


      _DesiredSupplier.SupplierName, ////  as PreferredSupplierName,


      case PurReqnPriceQuantity
          when 0 then cast(PurchaseRequisitionPrice * RequestedQuantity as abap.curr( 25, 2 ))
          else
             case when RequestedQuantity = 0
               then PurchaseRequisitionPrice
             else
              cast(division(
                                cast(PurchaseRequisitionPrice * RequestedQuantity as abap.dec( 25, 2 )),
                                cast(PurReqnPriceQuantity as abap.dec( 25, 2 )),2
                                ) as abap.curr( 25, 2 ))
              end
          end                                                                             as PurReqnTotalAmountInDspCrcy,

      _Purchaserequisitionitem.RequestedQuantity-_Purchaserequisitionitem.OrderedQuantity as OpenQuantity,
      _Purchaserequisitionitem.PurReqnReleaseStatus,
      _Purchaserequisitionitem.PurchaseRequisitionReleaseDate,
      _Purchaserequisitionitem.PurReqCreationDate,
      _Purchaserequisitionitem.ProcessingStatus,
      _Purchaserequisitionitem.ReleaseCode,
      _ReleaseIndicatorDesc.PurchasingReleaseIndicatorDesc,
      _Purchaserequisitionitem.StorageLocation,
      _Purchaserequisitionitem.PurchasingInfoRecord,
      //_Purchaserequisitionitem.PurReqnSSPRequestor,

      _Purchaserequisitionitem.PurReqnRequestor,
      _Purchaserequisitionitem.RequisitionerName,
      _Purchaserequisitionitem.MRPController,
      _Purchaserequisitionitem.Batch,
      _Purchaserequisitionitem.PurReqnOrigin,
      _PurchaseReqnOrigin.DomainText,
      _Purchaserequisitionitem.ReleaseStrategy,
      _ReleaseStrategyDesc.PurchasingReleaseStrategyDesc,
      _Purchaserequisitionitem.ReleaseGroup,
      _Purchaserequisitionitem.CreatedByUser,
      _User.UserDescription,
      _Purchaserequisitionitem.CreationDate,
      _Purchaserequisitionitem.IsClosed,
      _Purchaserequisitionitem.IsDeleted,
      _Purchaserequisitionitem.PurchasingDocumentSubtype,
      _Purchaserequisitionitem.IsPurReqnBlocked,
      _Purchaserequisitionitem.IsPurReqnCmplt,
      _Purchaserequisitionitem.CompanyCode,
      _Purchaserequisitionitem._PurchaseOrder._CompanyCode.CompanyCodeName,
      _Purchaserequisitionitem.OrderedQuantity,
      _Purchaserequisitionitem.PurchaseRequisitionStatus,
      _Purchaserequisitionitem.ConsumptionPosting,
      _Purchaserequisitionitem._PurchasingDocumentType._Text[1: Language = $session.system_language ].PurchasingDocumentTypeName,
      _DesiredSupplier.PurchasingIsBlocked,
      //      _Supplier.PurchasingIsBlocked,

      case when
      _DesiredSupplier.PurchasingIsBlocked = 'X'
      //      _Supplier.PurchasingIsBlocked = 'X'

      then
      _ProcmtBlockedSupplier._Text[1: Language = $session.system_language].SupplierName
      // _DomainText.DomainText

      else
      ' '
      end                                                                                 as PurchasingIsBlockedForSupplier,

      //visual filter case statements

      cast(case when
      (_Purchaserequisitionitem.PurReqnReleaseStatus = '03' and  _Purchaserequisitionitem.ProcessingStatus = 'N') or (_Purchaserequisitionitem.PurReqnReleaseStatus = '04' and  _Purchaserequisitionitem.ProcessingStatus = 'N') or (_Purchaserequisitionitem.PurReqnReleaseStatus = '02' and  _Purchaserequisitionitem.ProcessingStatus = 'N' and _Purchaserequisitionitem.PurReqnCmpltnsCat = 'A')
      then '1' //In Approval

      when (_Purchaserequisitionitem.PurReqnReleaseStatus = '02' and  _Purchaserequisitionitem.ProcessingStatus = 'N') or
      (_Purchaserequisitionitem.PurReqnReleaseStatus = '05' and  _Purchaserequisitionitem.ProcessingStatus = 'N')
      then '2' //without-follow on documents

      else '3' //others

      end as abap.char( 10 )) as PurchasingDocumentStatus,


      //Newly added fields as part of code clean up based on scope document

      ProductType,
      SupplierMaterialNumber,
      MaterialRevisionLevel,
      ServicePerformer,
      MaterialPlannedDeliveryDurn,
      PerformancePeriodStartDate,
      PerformancePeriodEndDate,
      PurchaseRequisitionIsFixed,
      MaterialGoodsReceiptDuration,
      GoodsReceiptIsExpected,
      InvoiceIsExpected,
      GoodsReceiptIsNonValuated,
      SourceOfSupplyIsAssigned,
      LastChangedDate,

      case
       when PurchaseContract <> ''
         then concat(concat(PurchaseContract, '/'), PurchaseContractItem)
       when PurchasingInfoRecord <> ''
          then PurchasingInfoRecord
       else
           ' '
      end                                                                                 as SourceDocument,

      case
       when PurchaseContractItem <> '00000'
         then 'AG'
       when PurchasingInfoRecord <> ''
         then  'IR'
       else
         ''
       end                                                                                as PurchasingSourceType,

      _SupplyingPlant.PlantName                                                           as SupplyingPlantName,

      ////        _PurReqOpenValCalc.IsOverdue,

      ////

      ////        _PurReqOpenValCalc.NetValueAmount,

      ////

      ////        _PurReqOpenValCalc.DurationToNextStatusInDays,


      //Logic for sun to sat

      dats_add_days(_CalendarDate.FirstDayOfWeekDate,-1,'NULL')                           as weekstart,
      dats_add_days(_CalendarDate.FirstDayOfWeekDate,5,'NULL')                            as weekend,
      dats_add_days(_CalendarDate.FirstDayOfWeekDate,12,'NULL')                           as secweekend,
      dats_add_days(_CalendarDate.FirstDayOfWeekDate,26,'NULL')                           as fourthweekend,

      //_CalendarDate.WeekDay,

      cast(cast(_CalendarDate.CalendarWeek as abap.int1( 3 )) - 1 as abap.char( 12 ))     as calweek,
      7-cast(_CalendarDate.WeekDay as abap.int8)                                          as weekday1,
      14-cast(_CalendarDate.WeekDay as abap.int8)                                         as weekday2,
      28-cast(_CalendarDate.WeekDay as abap.int8)                                         as weekday4,
      dats_add_days(_CalendarDate.FirstDayOfWeekDate,-1,'NULL')                           as weekday5,

      //Carbon footprint UUID

      PFMTransDataFootprintUUID,

      //Associations

      _PurchaseReqnSourceType,
      _PurchasingCategoryMatlGroup._PurchasingCategory,
      // _PurchasingCategoryMatlGroup._MaterialGroup,

      _AccAssgnmtCategory,
      _PurchasingOrganization,
      _PurchasingGroup,
      _Plant,
      _Material,
      _PurchasingInfoRecord,
      _StorageLocation,
      _PurgDocumentItemCategory,
      _Supplier,
      _PurReqnAcctAssgmt,
      //      _Supplier as _FixedSupplier,

      _DesiredSupplier,
      _ReleaseIndicatorDesc,
      _PurchaseReqnOrigin,
      _User,
      _ProcmtBlockedSupplier,
      // _DomainText,

      _ReleaseStrategyDesc,
      _PurchasingCategoryMatlGroup,
      _Purchaserequisitionitem._MaterialGroup                                             as _MaterialGroup // 2741424


}
where
                PurchasingDocumentCategory                              =  'B'
  and           IsPurReqnBlocked                                        =  ' '
  and(
                IsPurReqnCmplt                                          =  ' '
    or(
                IsPurReqnCmplt                                          =  'X'
      and       PurReqnCmpltnsCat                                       =  'A'
    )
  )
  and(
    (
                _Purchaserequisitionitem.Supplier                       =  ' '
    )
    or(
                _DesiredSupplier.IsBusinessPurposeCompleted             =  ' '
    )
  )
  and(
    (
                FixedSupplier                                           =  ' '
    )
    or(
                _Supplier.IsBusinessPurposeCompleted                    =  ' '
    )
  )
  and           IsDeleted                                               =  ' '
  and           IsClosed                                                =  ' '
  and(
    (
      (
        (
          (
                OrderedQuantity                                         <  RequestedQuantity
          )
          or(
                RequestedQuantity                                       =  0
            and _Purchaserequisitionitem.PurchasingDocumentItemCategory =  'A'
          )
        )
        or      PurchasingDocumentItemCategory                          =  '8'
      )
      and       PurchasingDocumentSubtype                               <> 'R'
    )
    or(
                PurchasingDocumentSubtype                               =  'R'
      and       ProcessingStatus                                        <> 'K'
      and       ProcessingStatus                                        <> 'L'
    )
  )
  and           IsOutline                                               =  ' '
  and           ProcurementHubSourceSystem                              =  ' '