pp_mrp_read_planned_order_comp

@AbapCatalog.sqlViewName: 'V_MRP_PLD_COMP'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType: {serviceQuality: #C, sizeCategory: #XL, dataClass: #MIXED}
@EndUserText.label: 'Read Planned Order Components'
define view pp_mrp_read_planned_order_comp
  as select
    res.rsnum                                              as Reservation,
    res.rspos                                              as ReservationItem,
    res.bdart                                              as RequirementType,
    res.matnr                                              as Material,
    res.werks                                              as MRPPlant,
    res.umwrk                                              as SupplyingPlant,
    res.bdter                                              as RequirementDate,
    res.bdmng                                              as RequiredQuantity,
    res.enmng                                              as WithdrawnQuantity,
    res.bdmng - res.enmng                                  as OpenQuantity,
    res.meins                                              as MaterialBaseUnit,
    res.lifnr                                              as Vendor,
    ven.name1                                              as VendorName,
    mat.ekgrp                                              as PurchasingGroup,
    pgr.eknam                                              as PurchasingGroupName,
    case pgr.tel_extens
      when '' then pgr.ektel
      else concat(pgr.tel_number, concat('-', pgr.tel_extens))
    end                                                    as PurchasingGroupPhoneNumber,
    pgr.smtp_addr                                          as PurchasingGroupEmailAddress,
    ord.beskz                                              as MaterialProcurementCategory,
    ord.sobes                                              as MaterialProcurementType,
    ord.webaz                                              as MaterialGoodsReceiptDuration,
    mat.plifz                                              as MaterialPlannedDeliveryDurn,
    mat.dzeit                                              as MaterialPlannedProductionDurn,
    mat.wzeit                                              as MatTotalReplenishmentLeadDurn,
    mat.bstfe                                              as MaterialFixedLotSizeQuantity,
    mat.bstmi                                              as MaterialMinLotSizeQuantity,
    mat.bstma                                              as MaterialMaxLotSizeQuantity,
    mat.bstrf                                              as MaterialRoundingQuantity,
    mat.rdprf                                              as MaterialRoundingProfile,
    ord.plnum                                              as PlannedOrder,
    ord.paart                                              as PlannedOrderType,
    ord.pwwrk                                              as ProductionPlant,
    ord.dispo                                              as MRPController,
    ord.plgrp                                              as ProductionSupervisor,
    ord.verid                                              as ProductionVersion,
    prv.text1                                              as ProductionVersionText,
    prv.mdv01                                              as ProductionLine,
    top.plnum                                              as TopLevelOrder,
    top.matnr                                              as TopLevelOrderMaterial,
    ord.sobkz                                              as InventorySpecialStock,
    ord.kdauf                                              as SalesOrder,
    ord.kdpos                                              as SalesOrderItem,
    vbk.auart                                              as SalesOrderType,
    vbp.matnr                                              as SalesOrderItemMaterial,
    vbk.kunnr                                              as Customer,
    cus.name1                                              as CustomerName,
    ord.pspel                                              as WBSElementInternalID,
    psp.posid                                              as WBSElement,
    psp.post1                                              as WBSDescription,
    psp.vernr                                              as WBSResponsiblePerson,
    psp.verna                                              as WBSResponsiblePersonName,
    psp.psphi                                              as ProjectInternalID,
    pro.pspid                                              as Project,
    pro.post1                                              as ProjectDescription,
    ord.pertr                                              as PlannedOrderOpeningDate,
    ord.psttr                                              as PlannedOrderStartDate,
    ord.pedtr                                              as PlannedOrderEndDate,
    ord.gsmng                                              as PlannedOrderTotalQty,
    ord.avmng                                              as PlannedOrderScrapQty,
    (ord.gsmng - ord.avmng - ord.wemng)                    as PlannedOrderOpenQty,
    ord.wemng                                              as GoodsReceiptQuantity,
    case ord.arsnr
      when '0000000000' then case ord.vrpla
        when '' then'X'
        else ''
      end
      when '' then case ord.vrpla
        when '' then'X'
        else ''
      end
      else ''
    end                                                    as MRPElementIsEditable,
    '045'                                                  as QuickviewCategory
  from                  plaf  as ord
    inner join          resb  as res on  res.mandt = ord.mandt
                                     and res.rsnum = ord.rsnum
                                     and res.bdart = 'SB'
                                     and res.kzkup = ''
                                     and res.xloek = ''
    left outer join     marc  as mat // Plant material

                                     on  mat.mandt = ord.mandt
                                     and mat.werks = ord.pwwrk
                                     and mat.matnr = ord.matnr
    left outer join     mkal  as prv // Production version

                                     on  ord.mandt = prv.mandt
                                     and ord.matnr = prv.matnr
                                     and ord.pwwrk = prv.werks
                                     and ord.verid = prv.verid
    left outer join     lfa1  as ven // Vendor

                                     on  ven.mandt = res.mandt
                                     and ven.lifnr = res.lifnr
    left outer join     plaf  as top // Top level order

                                     on  top.mandt = ord.mandt
                                     and top.rsnum = ord.arsnr
                                     and top.arsnr = '0000000000'
  /*  additional joins to sales orders or projects */
    left outer join(
                        prps  as psp
        inner join      proj  as pro on  psp.mandt = pro.mandt
                                     and psp.psphi = pro.pspnr
    )
    on  ord.mandt = psp.mandt
    and ord.pspel = psp.pspnr
    left outer join(
                        vbak  as vbk
        inner join      vbap  as vbp on  vbk.mandt = vbp.mandt
                                     and vbk.vbeln = vbp.vbeln
        left outer join kna1  as cus on  vbk.mandt = cus.mandt
                                     and vbk.kunnr = cus.kunnr
    )
    on  ord.mandt = vbp.mandt
    and ord.kdauf = vbp.vbeln
    and ord.kdpos = vbp.posnr
    left outer join     t024  as pgr // Purchasing group

                                     on  pgr.mandt = mat.mandt
                                     and pgr.ekgrp = mat.ekgrp;