pp_mrp_read_mfg_order

@AbapCatalog.sqlViewName: 'V_MRP_MFG_ORDER'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.compiler.compareFilter:true
@AbapCatalog.preserveKey: true
@ObjectModel.usageType: {serviceQuality: #C, sizeCategory: #XL, dataClass: #MIXED}
@EndUserText.label: 'Read Manufacturing Order'
define view pp_mrp_read_mfg_order
  as select
    ord.aufnr                                       as ManufacturingOrder,
    pos.posnr                                       as ManufacturingOrderItem,
    ord.autyp                                       as ManufacturingOrderCategory,
    ord.auart                                       as ManufacturingOrderType,
    pos.matnr                                       as Material,
    pos.pwerk                                       as MRPPlant,
    pos.dwerk                                       as ProductionPlant,
    pos.verid                                       as ProductionVersion,
    prv.text1                                       as ProductionVersionText,
    prv.mdv01                                       as ProductionLine,
    case ord.autyp
      when '10' then ''
      when '40' then ord.rsnid
    end                                             as ResourceNetwork,
    pos.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,
    mat.ekgrp                                       as PurchasingGroup,
    mat.sernp                                       as SerialNumberProfile,
    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,
    pos.sobkz                                       as InventorySpecialStock,
    // Top level order

    ord.prodnet                                     as IsPartOfCollectiveOrder,
    ord.maufnr                                      as SuperiorOrder,
    top.aufnr                                       as TopLevelOrder,
    tpp.matnr                                       as TopLevelOrderMaterial,
    // Sales Order

    pos.kdauf                                       as SalesOrder,
    pos.kdpos                                       as SalesOrderItem,
    vbk.auart                                       as SalesOrderType,
    vbp.matnr                                       as SalesOrderItemMaterial,
    vbk.kunnr                                       as Customer,
    cus.name1                                       as CustomerName,
    // WBS Element

    pos.projn                                       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,
    // Other Data

    ord.fevor                                       as ProductionSupervisor,
    ord.dispo                                       as MRPController,
    ord.gstrp                                       as PlannedStartDate,
    ord.gltrp                                       as PlannedEndDate,
    ord.gstri                                       as ActualStartDate,
    ord.getri                                       as ActualEndDate,
    ord.ftrmi                                       as ActualReleaseDate,
    ord.gstrs                                       as ScheduledStartDate,
    ord.gltrs                                       as ScheduledEndDate,
    ord.ftrms                                       as ScheduledReleaseDate,
    pos.amein                                       as ProductionUnit,
    ord.gamng                                       as PlannedTotalQty,
    ord.gasmg                                       as PlannedScrapQty,
    pos.iamng                                       as ExpectedDeviationQty,
    pos.wemng                                       as DeliveredQuantity,
    (ord.gamng - ord.gasmg - pos.iamng - pos.wemng) as OpenQuantity,
    ord.aprio                                       as OrderImportance,
    case
     when ord.aedat = '00000000'
      then dats_tims_to_tstmp( ord.erdat, ord.erfzeit,
                        abap_system_timezone( $session.client,'NULL' ),
                        $session.client,'NULL' )  
      else dats_tims_to_tstmp( ord.aedat, ord.aezeit,
                        abap_system_timezone( $session.client,'NULL' ),
                        $session.client,'NULL' )                     
      end                                          as LastChangeDateTime,
    case ord.autyp
      when '10' then '031'
      when '40' then '033'
      else '000'
    end                                             as QuickviewCategory
  from                     caufv as ord
    inner join             afpo  as pos on  pos.mandt = ord.mandt
                                        and pos.aufnr = ord.aufnr
                                        and pos.dnrel = ''
    left outer to one join mkal  as prv // Production version

                                        on  prv.mandt = pos.mandt
                                        and prv.werks = pos.pwerk
                                        and prv.matnr = pos.matnr
                                        and prv.verid = pos.verid
    left outer to one join marc  as mat // Plant material

                                        on  mat.mandt = pos.mandt
                                        and mat.werks = pos.pwerk
                                        and mat.matnr = pos.matnr
  /*  additional joins to top level order, sales orders and projects */
    left outer to one join(
                           afko  as top
        inner join         afpo  as tpp on  tpp.mandt = top.mandt
                                        and tpp.aufnr = top.aufnr
                                        and tpp.posnr = '0001'
    )
    on  top.mandt   = ord.mandt
    and top.rsnum   = ord.arsnr
    and top.stufe   = 0
    and top.prodnet = 'X'
    left outer to one join(
                          prps   as psp
        inner join        proj   as pro on  psp.mandt = pro.mandt
                                        and psp.psphi = pro.pspnr
    )
    on  pos.mandt = psp.mandt
    and pos.projn = psp.pspnr
    left outer to one join(
                           vbak     as vbk
        inner join         vbap     as vbp on  vbk.mandt = vbp.mandt
                                           and vbk.vbeln = vbp.vbeln
        left outer to one join kna1 as cus on  vbk.mandt = cus.mandt
                                           and vbk.kunnr = cus.kunnr
    )
    on  pos.mandt = vbp.mandt
    and pos.kdauf = vbp.vbeln
    and pos.kdpos = vbp.posnr
    left outer to one join t024   as pgr // Purchasing group

                                        on  pgr.mandt = mat.mandt
                                        and pgr.ekgrp = mat.ekgrp
  where
    (
         ord.autyp = '10'
      or ord.autyp = '40'
    );
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"AFKO",
"AFPO",
"KNA1",
"MARC",
"MKAL",
"PROJ",
"PRPS",
"T024",
"VBAK",
"VBAP",
"CAUFV"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/