P_ProcmtDocSubcontrg

@AbapCatalog.sqlViewName: 'PSUBCONTRG'
@VDM.private: true
@VDM.viewType: #COMPOSITE

@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #PRIVILEGED_ONLY
@ClientHandling.algorithm: #SESSION_VARIABLE

@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.dataClass: #TRANSACTIONAL

@Metadata.ignorePropagatedAnnotations: true

define view P_ProcmtDocSubcontrg
with parameters
     P_ReqdDelivDteRngeFrmDte: vdm_shippingdate,
     P_ReqdDelivDteRngeToDte: vdm_shippingdate 
  as select distinct from I_ReservationItem   // 3094739


  association [0..1] to I_Productplantsales         as _ProductPlantSales          on  $projection.MaterialComponent = _ProductPlantSales.Product
                                                                                   and $projection.Plant             = _ProductPlantSales.Plant


  //All materials need to have an MRP Area defined in order to enable automatic stock transfer reservations

  association [1..1] to P_StkTransfResvn            as _StkTransfResvn             on  $projection.MaterialComponent = _StkTransfResvn.Material
                                                                                   and $projection.Plant             = _StkTransfResvn.Plant
                                                                                   and $projection.Subcontractor     = _StkTransfResvn.Supplier

  association [0..1] to I_ProposedShipgPointPerPlnt as _ProposedShipgPointsPerPlnt on  $projection.Plant                           = _ProposedShipgPointsPerPlnt.Plant
                                                                                   and _ProposedShipgPointsPerPlnt.StorageLocation = ' '
                                                                                   and $projection.loadinggroup                    = _ProposedShipgPointsPerPlnt.LoadingGroup
                                                                                   and $projection.ShippingCondition               = _ProposedShipgPointsPerPlnt.ShippingCondition

{

      key Material                                                                                 as MaterialComponent,

      key Plant,

      key Supplier                                                                                 as Subcontractor,

      //----------------------------------------------------------------------------------

      // Logical Key

      //----------------------------------------------------------------------------------

      cast(concat(concat(concat(concat(I_ReservationItem.Material,'/'),
       I_ReservationItem.Plant),'/'),
       I_ReservationItem.Supplier) as abap.char( 250 ))                                        as SuplrMatlPlnt,

      cast(concat(concat(concat(concat(I_ReservationItem.Material,'/'),
      I_ReservationItem.Plant),'/'),
      I_ReservationItem.Supplier) as abap.char( 250 ))                                         as ProdPlntSupplierConcatenatedID,

      //----------------------------------------------------------------------------------

      // Header Fields

      //----------------------------------------------------------------------------------


      cast ('' as etenr)                                                                       as ScheduleLine,

      cast ('' as rsnum)                                                                       as Reservation,

      cast ('' as pph_rspos)                                                                   as ReservationItem,

      cast('' as   abap.char( 250 ))                                                           as MasterDataNode,

      cast('' as abap.char( 250 ))                                                             as ParentNode,

      cast('0' as abap.char(1))                                                                as HierarchyNodeLevel,

      cast('expanded' as abap.char(8))                                                         as HierarchyDrillState,

      //----------------------------------------------------------------------------------

      // Item Fields

      //----------------------------------------------------------------------------------


      cast('' as abap.char(15))                                                                as PurchaseOrder,

      cast('' as abap.char(5))                                                                 as PurchaseOrderItem,

      cast('' as abap.char(16))                                                                as FormattedPurchaseOrderItem,

      cast ('00000000' as bdter)                                                               as ShipmentCompletionDate,

      cast('' as abap.char(2))                                                                 as ShippingStatus,

      cast('' as matnr)                                                                        as Assembly,

      cast('00000000' as eindt)                                                                as ScheduleLineDeliveryDate,


      //----------------------------------------------------------------------------------

      // Filter Fields

      //----------------------------------------------------------------------------------


      cast('' as ekgrp)                                                                        as PurchasingGroup,

      cast('' as ekorg)                                                                        as PurchasingOrganization,

      cast('' as charg_d)                                                                      as Batch,

      cast('' as reswk)                                                                        as SupplyingPlant,


      //----------------------------------------------------------------------------------

      // Calculated Fields

      //----------------------------------------------------------------------------------


      cast(0 as abap.quan( 13, 3 ))                                                            as StillToBeDeliveredQuantity,

      cast ('' as meins)                                                                       as OrderQuantityUnit,

      //_SubContrCalc.StockBalanceQuantity,



      //----------------------------------------------------------------------------------

      // Additional Output fields

      //----------------------------------------------------------------------------------


      cast ('' as pph_lgort)                                                                   as StorageLocation,

      PurchaseRequisition,

      PurchaseRequisitionItem,

      _ProductPlantSales.LoadingGroup,

      //Fields for association

      cast('' as vtweg)                                                                        as DistributionChannel,

      cast ('' as vkorg)                                                                       as SalesOrganization,

      cast ( '' as spaiv)                                                                      as Division,

      cast ('' as vsbed)                                                                       as ShippingCondition,
   
      @EndUserText.label: 'Count'
      cast( 0 as abap.int4)                                                                    as NumberOfItems,

      cast ('' as bstyp)                                                                       as PurchasingDocumentType,

      cast('' as bwart)                                                                        as GoodsMovementType,

      cast (0 as abap.quan( 13, 3 ))                                                           as RequiredQuantity,

      _PurchasingDocument.PurchasingDocumentCategory                                           as PurchasingDocumentCategory,

      _Material.MaterialGroup,

      _Material._MaterialGroup._Text[1: Language = $session.system_language].MaterialGroupName as MaterialGroupName,

      _Supplier.SupplierName,

      cast ('' as maktx)                                                                       as AssemblyName,

      _Material._Text[1: Language = $session.system_language].MaterialName                     as MaterialComponentName,

      cast('' as lgort_name)                                                                   as StorageLocationName,

      _Plant.PlantName                                                                         as PlantName,

      cast('' as mm_a_purg_grp_name )                                                          as PurchasingGroupName,

      cast( '' as mm_a_purg_org_name )                                                         as PurchasingOrganizationName,

      RequirementType,

      DebitCreditCode,

      //Fields for DCL

      cast ('' as bukrs)                                                                       as CompanyCode,

      _Supplier.AuthorizationGroup,

      _Supplier.SupplierAccountGroup,

      _Supplier.Customer,

      @Consumption.hidden: true
      _SupplierCompanyByPlant,

      _ProposedShipgPointsPerPlnt,
      
      @Consumption.hidden: true
      _Supplier
}

where
            I_ReservationItem.Supplier           <> ''
  and       I_ReservationItem.Material           <> ''
  and       I_ReservationItem.Plant              <> ''
  and       PurchaseRequisition                  =  ''
  and       I_ReservationItem.MatlCompRequirementDate >= $parameters.P_ReqdDelivDteRngeFrmDte
  and       I_ReservationItem.MatlCompRequirementDate <= $parameters.P_ReqdDelivDteRngeToDte


  //Conditions adopted from ME2ON report

  and       ReservationItemIsFinallyIssued       =  ' '
  and       MaterialComponentIsPhantomItem       =  ' '
  and       DebitCreditCode                      =  'H'
  and       IsBulkMaterialComponent              =  ' '
  and       MatlCompIsTextItem                   =  ' '
  and       InventorySpecialStockType            =  ' '


  and(
    (
      (
            //Subcontracting PO and SA - adopted from I_SubcontractingComponent

            RecordType                           =  ' '
        and RequirementType                      =  'BB'
        and I_ReservationItem.PurchasingDocument <> ''

        //Removing Blocked supplier                                        // 3094739

        and _PurchasingDocumentItem.PurchasingDocumentDeletionCode =  ''   // 3094739

        and _PurchasingDocumentItem.IsCompletelyDelivered          =  ''   // 3094739

        and _PurchasingDocument.PurchasingDocumentDeletionCode     =  ''   // 3094739


        //DCL fields

        and _PurchasingDocument.CompanyCode      <> ''
      )
      or(
            // Subcontracting Stock Transfer Reservations

            IssgOrRcvgSpclStockInd               =  'O'
        //Check for MRP Area

        and _StkTransfResvn.MRPArea              <> ''
      )
    )
  )


union all select from I_ReservationItem


association [0..1] to I_ProposedShipgPointPerPlnt as _ProposedShipgPointsPerPlnt on  $projection.Plant                           = _ProposedShipgPointsPerPlnt.Plant
                                                                                 and _ProposedShipgPointsPerPlnt.StorageLocation = ' '
                                                                                 and $projection.LoadingGroup                    = _ProposedShipgPointsPerPlnt.LoadingGroup
                                                                                 and $projection.ShippingCondition               = _ProposedShipgPointsPerPlnt.ShippingCondition

association [0..1] to P_PendingQuantity           as _PendingQuantity            on  I_ReservationItem.Reservation      = _PendingQuantity.Reservation
                                                                                 and $projection.ReservationItem        = _PendingQuantity.ReservationItem
                                                                                 and $projection.MaterialComponent      = _PendingQuantity.Material
                                                                                 and $projection.Plant                  = _PendingQuantity.Plant
                                                                                 and $projection.Subcontractor          = _PendingQuantity.Supplier
                                                                                 and $projection.ShipmentCompletionDate = _PendingQuantity.RequirementDate

association        to P_ShippingStatusCalc        as _ShippingStatusCalc         on  I_ReservationItem.Reservation = _ShippingStatusCalc.Reservation
                                                                                 and $projection.ReservationItem   = _ShippingStatusCalc.ReservationItem
                                                                                 and $projection.MaterialComponent = _ShippingStatusCalc.MaterialComponent
                                                                                 and $projection.Plant             = _ShippingStatusCalc.Plant
                                                                                 and $projection.Subcontractor     = _ShippingStatusCalc.Subcontractor

association [1..1] to I_PurgDocScheduleLine       as _ScheduleLine               on  I_ReservationItem.PurchasingDocument     = _ScheduleLine.PurchasingDocument
                                                                                 and I_ReservationItem.PurchasingDocumentItem = _ScheduleLine.PurchasingDocumentItem
                                                                                 and $projection.ScheduleLine                 = _ScheduleLine.ScheduleLine

//All materials need to have an MRP Area defined in order to enable automatic stock transfer reservations

association [1..1] to P_StkTransfResvn            as _StkTransfResvn             on  $projection.MaterialComponent = _StkTransfResvn.Material
                                                                                 and $projection.Plant             = _StkTransfResvn.Plant
                                                                                 and $projection.Subcontractor     = _StkTransfResvn.Supplier


association [0..1] to I_Productplantsales         as _ProductPlantSales          on  $projection.MaterialComponent = _ProductPlantSales.Product
                                                                                 and $projection.Plant             = _ProductPlantSales.Plant



{

  key I_ReservationItem.Material                                                                   as MaterialComponent,

  key I_ReservationItem.Plant,

  key I_ReservationItem.Supplier                                                                   as Subcontractor,
      //----------------------------------------------------------------------------------

      // Logical Key

      //---------------------------------------------------------------------------------


   cast ((concat(concat(concat(concat(concat(concat(concat(concat(
  I_ReservationItem.Material, '/'),
  I_ReservationItem.Plant), '/'),
  I_ReservationItem.Supplier), '/'),
  I_ReservationItem.Reservation),'/'),
  I_ReservationItem.ReservationItem)) as abap.char(250))                                           as SuplrMatlPlnt,

      cast ((concat(concat(concat(concat(concat(concat(concat(concat(
      I_ReservationItem.Material, '/'),
      I_ReservationItem.Plant), '/'),
      I_ReservationItem.Supplier), '/'),
      I_ReservationItem.Reservation),'/'),
      I_ReservationItem.ReservationItem)) as abap.char(250))                                       as ProdPlntSupplierConcatenatedID,



      //----------------------------------------------------------------------------------

      // Header Fields

      //----------------------------------------------------------------------------------


      I_ReservationItem.ScheduleLine                                                               as ScheduleLine,

      I_ReservationItem.Reservation                                                                as Reservation,

      I_ReservationItem.ReservationItem                                                            as ReservationItem,

      cast ((concat(concat(concat(concat(concat(concat(concat(concat(
      I_ReservationItem.Material, '/'),
      I_ReservationItem.Plant), '/'),
      I_ReservationItem.Supplier), '/'),
      I_ReservationItem.Reservation),'/'),
      I_ReservationItem.ReservationItem)) as abap.char(250))                                       as MasterDataNode,

      cast(concat(concat(concat(concat(I_ReservationItem.Material,'/'),
      I_ReservationItem.Plant),'/'),
      I_ReservationItem.Supplier) as abap.char( 250 ))                                             as ParentNode,

      cast('1' as abap.char(1))                                                                    as HierarchyNodeLevel,

      cast('leaf' as abap.char(8))                                                                 as HierarchyDrillState,


      //----------------------------------------------------------------------------------

      // Item Fields

      //----------------------------------------------------------------------------------


      case when RequirementType = 'MR'
        then cast (Reservation as abap.char(15))
        else  cast (PurchasingDocument as abap.char(15))
        end                                                                                        as PurchaseOrder,


      case when RequirementType = 'MR'
        then cast (ReservationItem as abap.char(5))
        else  cast (PurchasingDocumentItem as abap.char(5))
        end                                                                                        as PurchaseOrderItem,


      case when RequirementType = 'MR'
        then cast(concat(concat(I_ReservationItem.Reservation,'/'),
                  I_ReservationItem.ReservationItem) as abap.char(30))
        else  cast(concat(concat(PurchasingDocument,'/'),
                PurchasingDocumentItem) as abap.char(30))
        end                                                                                        as FormattedPurchaseOrderItem,

      MatlCompRequirementDate                                                                      as ShipmentCompletionDate,

      case when _ShippingStatusCalc.ShippingStatus is not null
      then _ShippingStatusCalc.ShippingStatus
      else ''
      end                                                                                          as ShippingStatus,

      case when _PurchasingDocumentItem.Material is not null
      then _PurchasingDocumentItem.Material
      else ''
      end                                                                                          as Assembly,

      case when RequirementType = 'MR'
       then cast('00000000' as eindt)
       else  _ScheduleLine.ScheduleLineDeliveryDate
       end                                                                                         as ScheduleLineDeliveryDate,


      //----------------------------------------------------------------------------------

      // Filter Fields

      //----------------------------------------------------------------------------------


      case when _PurchasingDocument.PurchasingGroup is not null
      then _PurchasingDocument.PurchasingGroup
      else ''
      end                                                                                          as PurchasingGroup,

      case when _PurchasingDocument.PurchasingOrganization is not null
      then _PurchasingDocument.PurchasingOrganization
      else ''
      end                                                                                          as PurchasingOrganization,

      case when I_ReservationItem.Batch is not null
      then I_ReservationItem.Batch
      else ''
      end                                                                                          as Batch,

      case when _PurchasingDocument.SupplyingPlant is not null
      then _PurchasingDocument.SupplyingPlant
      else ''
      end                                                                                          as SupplyingPlant,

      //----------------------------------------------------------------------------------

      // Calculated Fields

      //----------------------------------------------------------------------------------


      _PendingQuantity.StillToBeDeliveredQuantity                                                  as StillToBeDeliveredQuantity,

      _PendingQuantity.OrderQuantityUnit                                                           as OrderQuantityUnit,

      //----------------------------------------------------------------------------------

      // Additional Output fields

      //----------------------------------------------------------------------------------


      case when StorageLocation is not null
      then StorageLocation
      else ''
      end                                                                                          as StorageLocation,

      PurchaseRequisition,

      PurchaseRequisitionItem,

      case when _ProductPlantSales.LoadingGroup is null
        then cast (' ' as ladgr)
        else _ProductPlantSales.LoadingGroup
        end                                                                                        as LoadingGroup,

      //Fields for association

      _Plant.DistributionChannel                                                                   as DistributionChannel,

      _Plant.SalesOrganization                                                                     as SalesOrganization,

      _Plant.Division                                                                              as Division,

       cast ('' as vsbed)                                                                           as ShippingCondition,
    
      @EndUserText.label: 'Count'
      cast( 1 as abap.int4)                                                                        as NumberOfItems,

      case when RequirementType = 'MR'
        then cast('R' as abap.char(1))
        else  _PurchasingDocument.PurchasingDocumentCategory
        end                                                                                        as PurchasingDocumentType,


      GoodsMovementType,

      ResvnItmRequiredQtyInBaseUnit                                                                as RequiredQuantity,

      case when RequirementType = 'MR'
        then cast('R' as abap.char(1))
        else  _PurchasingDocument.PurchasingDocumentCategory
        end                                                                                        as PurchasingDocumentCategory,

      _Material.MaterialGroup,

      _Material._MaterialGroup._Text[1: Language = $session.system_language].MaterialGroupName,

      _Supplier.SupplierName,

      _PurchasingDocumentItem._Material._Text[1: Language = $session.system_language].MaterialName as AssemblyName,

      _Material._Text[1: Language = $session.system_language].MaterialName                         as MaterialComponentName,

      _StorageLocation.StorageLocationName,

      _Plant.PlantName,

      _PurchasingDocument._PurchasingGroup.PurchasingGroupName,

      _PurchasingDocument._PurchasingOrganization.PurchasingOrganizationName,

      RequirementType,

      DebitCreditCode,

      //Fields for DCL

      _PurchasingDocument.CompanyCode,

      _Supplier.AuthorizationGroup,

      _Supplier.SupplierAccountGroup,

      _Supplier.Customer,

      @Consumption.hidden: true
      _SupplierCompanyByPlant,

      _ProposedShipgPointsPerPlnt,
      
      @Consumption.hidden: true
      _Supplier

}

where
            I_ReservationItem.Supplier                             <> ''
  and       I_ReservationItem.Material                             <> ''
  and       I_ReservationItem.Plant                                <> ''
  and       PurchaseRequisition                                    =  ''
  and       I_ReservationItem.MatlCompRequirementDate >= $parameters.P_ReqdDelivDteRngeFrmDte
  and       I_ReservationItem.MatlCompRequirementDate <= $parameters.P_ReqdDelivDteRngeToDte

  and       ResvnItmRequiredQtyInEntryUnit                         >  ResvnItmWithdrawnQtyInBaseUnit

  and       ReservationItemIsFinallyIssued                         =  ' '
  and       MaterialComponentIsPhantomItem                         =  ' '
  and       DebitCreditCode                                        =  'H'
  and       IsBulkMaterialComponent                                =  ' '
  and       MatlCompIsTextItem                                     =  ' '
  and       InventorySpecialStockType                              =  ' '


  and(
    (
      (
            //Subcontracting PO and SA - adopted from I_SubcontractingComponent

            RecordType                                             =  ' '
        and RequirementType                                        =  'BB'
        and I_ReservationItem.PurchasingDocument                   <> ''
        //Conditions for Scheduling Agreement - JIT


        //Removing Blocked supplier

        and _PurchasingDocumentItem.PurchasingDocumentDeletionCode =  ''

        and _PurchasingDocumentItem.IsCompletelyDelivered          =  ''

        and _PurchasingDocument.PurchasingDocumentDeletionCode     =  ''

        //DCL fields

        and _PurchasingDocument.CompanyCode                        <> ''

      )
      or(
            // Subcontracting Stock Transfer Reservations

            IssgOrRcvgSpclStockInd                                 =  'O'
        //Check for MRP Area

        and _StkTransfResvn.MRPArea                                <> ''

      )
    )
  )
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_MATERIAL",
"I_MATERIALGROUP",
"I_MATERIALGROUPTEXT",
"I_MATERIALTEXT",
"I_PLANT",
"I_PRODUCTPLANTSALES",
"I_PURCHASINGDOCUMENT",
"I_PURCHASINGDOCUMENTITEM",
"I_PURCHASINGGROUP",
"I_PURCHASINGORGANIZATION",
"I_PURGDOCSCHEDULELINE",
"I_RESERVATIONITEM",
"I_STORAGELOCATION",
"I_SUPPLIER",
"P_PENDINGQUANTITY",
"P_SHIPPINGSTATUSCALC",
"P_STKTRANSFRESVN"
],
"ASSOCIATED":
[
"I_PRODUCTPLANTSALES",
"I_PROPOSEDSHIPGPOINTPERPLNT",
"I_SUPPLIER",
"I_SUPPLIERCOMPANYBYPLANT",
"P_STKTRANSFRESVN"
],
"BASE":
[
"I_RESERVATIONITEM"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/