rrp_e_GrPo

@AbapCatalog.sqlViewName: 'RRP_V_GR_PO'
@EndUserText.label: 'RRP: Select Purchase Orders as Goods Receipts'
@AccessControl.authorizationCheck: #NOT_REQUIRED    -- currently no DCL role existing or required
@ObjectModel.usageType.serviceQuality: #X           -- Code Pushdown
@ObjectModel.usageType.sizeCategory:   #XL
@ObjectModel.usageType.dataClass:      #MIXED
@ClientHandling.algorithm: #SESSION_VARIABLE
// view SELECTs Purchase Order items as Goods Receipts and calculates the open quantities in order UoM

// incl. the conversion into base UoM


// view returns positive GR quantities in open_rec_quant and

//              negative GI quantities in open_issue_quant (from return PO's)

//  per PO schedule line  =>  either open_rec_quant or open_issue_quant  is filled per item


// note: because of possible reduction by DABMG the calling view has to check that open_rec_quant is not negative


// JOIN with storloc_class to check if storage location in EKPO-LGORT is Repl. relevant  (storage location of receiving site)


define view rrp_e_GrPo
  as select from    wrrp_doc_type       as doc_type
    inner join      ekko                as ekko          on(
               doc_type.doc_cat      = ekko.bstyp
               and doc_type.doc_type = ekko.bsart
             )
    inner join      ekpo                as ekpo          on(
               ekko.ebeln = ekpo.ebeln
             )
    inner join      wrrp_kntp_sbkz      as knttp_sobkz // Account Assignment Category and Special Stock Indicator => in standard only blank allowed, can be enhanced in BAdI

                                                         on(
                                                           ekpo.knttp     = knttp_sobkz.knttp
                                                           and ekpo.sobkz = knttp_sobkz.sobkz
                                                         )
    inner join      wrrp_ctrl           as ctrl          on(
               ekpo.werks     = ctrl.werks
               and ekpo.matnr = ctrl.matnr
             ) // receipts -> repl. site in EKPO-WERKS

    inner join      wrrp_paramset       as po_param      on(
           ctrl.mandt           = po_param.mandt
           and ctrl.process_id  = po_param.process_id
           and ctrl.paramset_id = po_param.paramset_id
         )
    inner join      eket                as eket          on(
               ekpo.ebeln     = eket.ebeln
               and ekpo.ebelp = eket.ebelp
             ) // select all related schedule lines

    left outer join V_WRF_STORLOC_CLASS as storloc_class on( //MRP Area Adjustment

      storloc_class.matnr     = ekpo.matnr
      and storloc_class.werks = ekpo.werks // check on storloc_class whether storage location is repl. relevant

      and storloc_class.lgort = ekpo.lgort
    )
{
  ctrl.process_id,
  ekpo.werks,
  ekpo.matnr,
  // calculate open Receipt (!) quantity for 'normal' orders (if conversion factor is maintained correctly)

  case
    when ekpo.umren = 0             // factor missing -> set quantity to 0

      then 0
    when storloc_class.lgort is null or storloc_class.retail_stock_class = ' '
      then case ekpo.retpo
           when 'X' then 0                    // return PO -> quantity still to be received will be 0

           else case ctrl.use_conf_gr_po      // standard PO  -> use order or confirmed quant, additionally reduced by DABMG if requested

                  when 'X'
                    then case ekko.reswk        //  use confirmed quantity for STO's

                         when ' ' then
                             case
                               when eket.wemng >=  eket.dabmg or ctrl.use_dabmg = 0
                                   then ( ( eket.menge - eket.wemng ) * division( ekpo.umrez, ekpo.umren, 2 ) ) // external PO -> use order quantity

                                   else ( ( eket.menge - ( eket.dabmg*ctrl.use_dabmg ) ) * division( ekpo.umrez, ekpo.umren, 2 ) ) //convert to base UoM

                             end
                         else
                             case
                               when eket.wemng >=  eket.dabmg or ctrl.use_dabmg = 0
                                   then ( ( eket.mng02 - eket.wemng ) * division( ekpo.umrez, ekpo.umren, 2 ) ) // stock transfer order -> use confirmed quantity

                                   else ( ( eket.mng02 - ( eket.dabmg*ctrl.use_dabmg ) ) * division( ekpo.umrez, ekpo.umren, 2 ) ) //convert to base UoM

                             end
                         end
                   else
                       case
                           when eket.wemng >=  eket.dabmg or ctrl.use_dabmg = 0
                              then ( ( eket.menge - eket.wemng ) * division( ekpo.umrez, ekpo.umren, 2 ) ) // use order quantity for ext. PO and STO

                              else ( ( eket.menge - ( eket.dabmg*ctrl.use_dabmg ) ) * division( ekpo.umrez, ekpo.umren, 2 ) ) //convert to base UoM

                       end
                end
           end
    else 0
  end as open_rec_quant, // note: because of possible reduction by DABMG the calling view has to check that open_rec_quant is not negative

                         //       ( that ( MENGE/MNG02 - WEMNG ) is not negative is ensured by related WHERE condition )

  // calculate open Issue (!) quantity for return orders (if conversion factor is maintained correctly)

  case
    when ekpo.umren = 0                                  // factor missing -> set quantity to 0

      then 0
    when storloc_class.lgort is null or ( storloc_class.retail_stock_class = ' ' )
      then case ekpo.retpo
           when 'X'                             // return PO -> use order quantity and regard the quantity as issue

             then ( ( 0 - ( eket.menge - eket.wemng ) )   // -> open order quant. will be negative (eket.wemng < eket.menge is in WHERE clause, see below)

                  * division( ekpo.umrez, ekpo.umren, 2) )   //convert to base UoM

           else 0                               // standard PO -> quantity still to be issued will be 0

           end
    else 0
  end as open_issue_quant
}
where
          doc_type.process_id       =  ctrl.process_id
  and     doc_type.doc_cat          =  'F'
  and     doc_type.rel_gr           =  'X'
  and     eket.nodisp               =  ' '
  and(
    (
          ctrl.read_gr_po           =  'X'
      and ekpo.retpo                <> 'X'
    )
    or(
          ctrl.read_ret_gr_po       =  'X'
      and ekpo.retpo                =  'X'
    )
  )
  and // return orders only read if requested

  //  select EKKO according to header status

  (
          ekko.bstyp                =  'F'
    and   ekko.loekz                =  ' '
  )
  and
  //  select EKPO according to item status

  (
    (
          ekpo.loekz                =  ' '
      or  ekpo.loekz                =  'S'
    )
    and   ekpo.stapo                =  ' '
    and   ekpo.elikz                =  ' '
  )
  and
  //  select EKET according to order date in schedule line => different 'start date' for returns (EKPO-RETPO = 'X')

  (
    (
          ekpo.retpo                <> 'X'
      and po_param.start_date_gr_po <= eket.eindt
      and eket.eindt                <= po_param.gr_date
    )
    or(
          ekpo.retpo                =  'X'
      and po_param.start_date_gi_po <= eket.eindt
      and eket.eindt                <= po_param.gr_date
    )
  )
  and
  //  select only schedule lines where received quant. < ordered/confirmed quant.

  (
          eket.wemng                <  eket.menge
    or(
          ctrl.use_conf_gr_po       =  'X'
      and ekko.reswk                <> ' '
      and ekpo.retpo                <> 'X'
      and eket.wemng                <  eket.mng02
    )
  )
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"V_WRF_STORLOC_CLASS",
"EKET",
"EKKO",
"EKPO",
"WRRP_CTRL",
"WRRP_DOC_TYPE",
"WRRP_KNTP_SBKZ",
"WRRP_PARAMSET"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/