ARUN_I_SO_REQMT_PREV

@AbapCatalog.sqlViewName: 'ARUN_V_SO_REQ_P'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ObjectModel.usageType.sizeCategory: #XXL
@ObjectModel.usageType.serviceQuality: #X
@AccessControl.personalData.blocking: #REQUIRED
@ObjectModel.usageType.dataClass:#TRANSACTIONAL
@ClientHandling.algorithm: #AUTOMATED
@EndUserText.label: 'Sales Order selection from Preview table'
define view ARUN_I_SO_REQMT_PREV
  with parameters
    p_langu : abap.lang
  as select from    vbak                                        as h
    inner join      tvak                                        as tv   on(
        h.auart = tv.auart
      )
    inner join      kna1                                        as kna1 on(
      kna1.kunnr         = h.kunnr
      and kna1.cvp_xblck = ''
    )
    inner join      Arun_I_So_1( p_langu :$parameters.p_langu ) as i    on(
         h.vbeln = i.vbeln
       )

    inner join       ARUN_I_SO_VBEP_FIELD                       as sl   on(
        i.vbeln      = sl.vbeln
        and i.posnr  = sl.posnr
      )
    left outer join arun_preview                                as prev on(

          sl.vbeln      = prev.salesdoc_num
      and sl.posnr      = prev.salesdoc_item
      and sl.mbdat      = prev.material_avail_date
      and sl.min_mbdat  = prev.requested_date
      and(
        prev.req_ind    = 'VC'
        or prev.req_ind = 'VI'
        or prev.req_ind = 'VG' //For contracts

      )
    )
  //         ship to party (Kunwe) WE

    left outer join vbpa                                        as WE1  on(
       i.vbeln       = WE1.vbeln
       and i.posnr   = WE1.posnr
       and WE1.parvw = 'WE'
     )
    left outer join vbpa                                        as WE2  on(
       h.vbeln       = WE2.vbeln
       and WE2.posnr = '000000'
       and WE2.parvw = 'WE'
     )
  // Sold to party - AG

    left outer join vbpa                                        as AG1  on(
       i.vbeln       = AG1.vbeln
       and i.posnr   = AG1.posnr
       and AG1.parvw = 'AG'
     )
    left outer join vbpa                                        as AG2  on(
       h.vbeln       = AG2.vbeln
       and AG2.posnr = '000000'
       and AG2.parvw = 'AG'
     )
  //Payer - RG

    left outer join vbpa                                        as RG1  on(
       i.vbeln       = RG1.vbeln
       and i.posnr   = RG1.posnr
       and RG1.parvw = 'RG'
     )
    left outer join vbpa                                        as RG2  on(
       h.vbeln       = RG2.vbeln
       and RG2.posnr = '000000'
       and RG2.parvw = 'RG'
     )
  //   Bill to party - RE

    left outer join vbpa                                        as RE1  on(
       i.vbeln       = RE1.vbeln
       and i.posnr   = RE1.posnr
       and RE1.parvw = 'RE'
     )
    left outer join vbpa                                        as RE2  on(
       h.vbeln       = RE2.vbeln
       and RE2.posnr = '000000'
       and RE2.parvw = 'RE'
     )

    left outer join knvv                                        as c    on(
         h.kunnr     = c.kunnr
         and h.vkorg = c.vkorg
         and h.vtweg = c.vtweg
         and h.spart = c.spart
       )
    left outer join vbkd                                        as vbkd on(
      i.vbeln      = vbkd.vbeln
      and i.posnr  = vbkd.posnr
    )
    left outer join ekko                                        as ekko on(

      prev.purchord_num = ekko.ebeln
    )
    left outer join lfa1                                        as lfa1 on(

          lfa1.lifnr     = ekko.lifnr
      and lfa1.cvp_xblck = ''
    )
    left outer join ekes                                        as ekes on(

          ekes.ebeln        = prev.purchord_num
      and ekes.ebelp        = prev.purchord_item
      and ekes.etens        = prev.confirmation_no
      and prev.stock_source = 'L'
    )
{
  key h.vbeln,
  key i.posnr,
      h.vbeln                                                                                  as aufnr,
      h.erdat,
      h.audat,
      h.vbtyp,
      h.augru,
      h.auart,
      h.vkorg,
      h.vtweg,
      h.vdatu,
      h.bstdk,
      cast('00000000' as eindt)                                                                as eindt,
      h.bstnk,
      h.fsh_rereg,
      h.cmgst,
      h.kunnr,
      h.fsh_cq_check,
      h.vkgrp,
      h.vkbur,
      h.ernam,
      h.lifsk,
      h.lfstk,
      i.grkor,

      i.matnr                                                                                  as material,
      case  when batch is not null then  batch
            else i.charg             end                                                       as batch,
      i.matkl,
      i.mvgr1,
      i.werks                                                                                  as plant,
      case  when storage_location is not null then  storage_location
            else i.lgort             end                                                       as storage_location,
      i.fsh_candate,
      h.fsh_candate                                                                            as hfsh_candate,
      i.sgt_rcat                                                                               as req_segment,
      case  when material_baseunit is not null then  material_baseunit
            else i.meins            end                                                        as material_baseunit,
      case  when prev.salesdoc_num is not null then salesdoc_num
       else  h.vbeln end                                                                       as salesdoc_num,
      case  when prev.salesdoc_item is not null then salesdoc_item
      else  i.posnr end                                                                        as salesdoc_item,
      case
       when h.vbtyp = 'I' then 'VI'
       when h.vbtyp = 'G' then 'VG'
      else   'VC'  end                                                                         as req_ind,
      i.fsh_season,
      i.fsh_season_year,
      i.fsh_collection,
      i.fsh_theme,
      i.lprio,
      i.sobkz,
      i.kowrr,
      i.sposn,
      i.bedae,
      i.pstyv,
      i.fsh_vas_rel,
      i.fsh_vas_prnt_id,
      i.fsh_transaction,
      i.fsh_item_group,
      i.fsh_item,
      i.lfsta,
      i.vstel,
      i.fsh_mg_arun_req,
      i.attyp,
      i.maktg,
      i.netpr,
      i.netwr,
      i.waerk,
      i.mtvfp,
      i.rfm_psst_group,
      i.berid,
      i.route,
      i.dismm,
      i.stlnr,
      i.stdat,
      i.fixmg,
      sl.lmeng                                                                                 as req_conf_qty,
      case
      when h.vbtyp = 'I' then 'VI'
      when h.vbtyp = 'G' then 'VG'
      else   'VC'  end                                                                         as bdart,
      sl.req_dlvdate                                                                           as req_dlvdate,
      sl.edatu                                                                                 as cnf_dlvdate,
      sl.mbdat                                                                                 as material_avail_date,
      i.satnr,
      i.xchpf,
      i.sgt_stk_prt,
      c.kdgrp,
      c.fsh_grreg,
      i.sgt_covs,
      i.spart,
      h.spart as hspart,
      i.color_atinn,
      i.size1_atinn,
      i.size2_atinn,
      i.color,
      i.size1,
      i.size2,
      i.uepos,
      c.kvgr1,
      c.kvgr2,
      c.kvgr3,
      c.kvgr4,
      c.kvgr5,
      i.fsh_grid_cond_rec,
      i.kwmeng,
      arunid,
      arun_status,
      purchdoc_num,
      reserv_num,
      purchdoc_item,
      reserv_item,
      stock_source,
      purchord_num,
      prodord_num,
      purchord_item,
      prodord_item,
      purchord_sch,
      confirmation_no,
      consignment_num,
      prev.alloc_qty,
      @Semantics.quantity.unitOfMeasure: 'material_baseunit'
      delivery_date_po,
      arun_mode,
      creation_time,
      changed_by,
      mat_avail_date_mrp,
      ilfgsa,
      iuvall,
      iuvvlk,
      iuvfak,
      h.lfgsk,
      h.abstk,
      h.spstg,
      h.uvall,
      h.uvvlk,
      h.uvfak,
      h.uvals,
      h.uvvls,
      h.uvfas,
      h.faksk,
      bukrs_vf                                                                                 as bukrs,
      vbkd.zterm,
      vbkd.inco1,
      vbkd.delco,
      case when arun_status = 'F' then prev.alloc_qty else 0 end                               as pfmenge,
      case when arun_status = 'H' then prev.alloc_qty else 0 end                               as phmenge,
      case when arun_status = 'R' then prev.alloc_qty else 0 end                               as prmenge,
      case when arun_status = 'O' then prev.alloc_qty else 0 end                               as pomenge,
      case when prev.requested_date is not null then prev.requested_date else sl.min_mbdat end as requested_date,
      prev.stk_segment,
      //      vbep_date.min_mbdat                                                                             as    new_req_dlvdate,

      ekko.lifnr                                                                               as lifnr,
      vbkd.bstkd,
      'A'                                                                                      as prreg,
      case
      when fsh_rereg != '' then fsh_rereg else '$$$$'    end                                   as rereg,
      case when WE1.kunnr is not null then WE1.kunnr else WE2.kunnr end                        as shipto,
      case when AG1.kunnr is not null then AG1.kunnr else AG2.kunnr end                        as soldto,
      case when RG1.kunnr is not null then RG1.kunnr else RG2.kunnr end                        as payer,
      case when RE1.kunnr is not null then RE1.kunnr else RE2.kunnr end                        as billto,
      case when AG1.land1 is not null then AG1.land1 else AG2.land1 end                        as land1,
      i.sgt_scope,
      i.charg                                                                                  as batch_req,
      i.lgort                                                                                  as sloc_req,
      i.arun_fix_batch,
      ekes.vbeln                                                                               as ibd_num,
      ekes.vbelp                                                                               as ibd_itm,
      prev.plnum                                                                               as plnum,
      dlvqty_bu                                                                                as dmenge,
      i.klmeng                                                                                 as klmeng
}
where
  //  (

  //       h.uvvlk = 'C'

  //  ) -- header incompletion status concerning delivery

  //  and(

  //       sl.lmeng != 0

  //  ) -- confirmed quantity

  (
          h.vbtyp         = 'C'
    or    h.vbtyp         = 'I' -- consider only so and free of charge orders
    or(
          h.vbtyp         = 'G'
      and tv.fsh_cq_check = 'X'
    )
  )
  and     h.rfgsk != 'C' -- total refernce status of all items
  and     tv.fsh_msoind != '1' -- Exclude Multi Ship-to order
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"ARUN_I_SO_1",
"ARUN_I_SO_VBEP_FIELD",
"ARUN_PREVIEW",
"EKES",
"EKKO",
"KNA1",
"KNVV",
"LFA1",
"TVAK",
"VBAK",
"VBKD",
"VBPA"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/