Arun_So_Reqmt

@AbapCatalog.sqlViewName: 'ARUN_V_SO_REQMT'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ObjectModel.usageType.sizeCategory: #XXL
@ObjectModel.usageType.serviceQuality: #X
@ObjectModel.usageType.dataClass:#TRANSACTIONAL
@AccessControl.personalData.blocking: #REQUIRED
@EndUserText.label: 'Sales Order Requirement'
define view Arun_So_Reqmt
  as select from    Arun_So_Req1   as i
    left outer join vbak             as h   on(
        h.mandt     = i.client
        and h.vbeln = i.vbeln
      )
    left outer join tvak             as tv  on  h.mandt = tv.mandt
                                            and h.auart = tv.auart
                                            and tv.fsh_msoind != '1' -- Exclude Multi Ship-to order
    left outer join vbep             as sl  on(
       i.vbeln      = sl.vbeln
       and i.posnr  = sl.posnr
       and i.client = sl.mandt
     )
    left outer join Arun_So_Ralloc as b   on(
        sl.mandt       = b.mandt
        and sl.vbeln   = b.salesdoc_num
        and sl.posnr   = b.salesdoc_item
//        and sl.etenr   = b.schedule_line

        and(
          b.req_ind    = 'VC'
          or b.req_ind = 'VG'
        )
      )
    left outer join vbpa             as k1  on(
       i.vbeln      = k1.vbeln
       and i.posnr  = k1.posnr
       and k1.parvw = 'WE'
       and i.client = k1.mandt
     )
    left outer join vbpa             as k2  on(
       h.vbeln      = k2.vbeln
       and k2.posnr = '000000'
       and k2.parvw = 'WE'
       and h.mandt  = k2.mandt
     )
    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
        and h.mandt = c.mandt
      )
    left outer join fsh_vass_tl      as vas on(
      i.vbeln               = vas.fsh_vgbel
      and i.posnr           = vas.fsh_vgpos
      and i.fsh_transaction = vas.fsh_transaction
      and i.fsh_item_group  = vas.fsh_item_group
      and i.fsh_item        = vas.fsh_item
      and i.client          = vas.mandt
    )
{
      //  h.mandt,

  key h.vbeln,
  key i.posnr,
  key sl.etenr,
      h.vbeln                                                                        as aufnr,
      h.erdat,
      h.audat,
      h.vbtyp,
      h.augru,
      h.auart,
      h.vkorg,
      h.vtweg,
      h.spart,
      h.vdatu,
      cast('00000000' as eindt)                                                      as eindt,
      h.bstnk,
      //  h.bstdk,

      h.fsh_rereg,
      h.cmgst,
      h.kunnr,
      h.fsh_cq_check,
      h.vkgrp,
      h.vkbur,
      h.ernam,
      h.lifsk,
      h.lfstk,
      i.matnr,
      i.charg,
      i.matkl,
      i.mvgr1,
      i.werks,
      i.lgort,
      i.fsh_candate,
      h.fsh_candate                                                                  as hfsh_candate,
      i.sgt_rcat,
      i.meins,
      i.fsh_season,
      i.fsh_season_year,
      i.fsh_collection,
      i.fsh_theme,
      i.fixmg,
      //  i.grkor,

      //  i.uepos,

      i.lprio,
      i.sobkz,
      //  i.shkzg,

      i.kowrr,
      i.stdat,
      i.stlnr,
      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,
      sl.edatu,
      sl.lmeng                                                                       as menge,
      case when h.vbtyp = 'G' then 'VG' else 'VC' end                                as bdart,
      sl.mbdat,
      sl.tddat,
      sl.lddat,
      sl.wadat,
      COALESCE( sl.dlvqty_bu + b.alloc_qty, sl.dlvqty_bu )                           as ralloc_qty,
      //  sl.bwart,

      sl.ettyp,
      sl.lifsp,
      i.satnr,
      i.xchpf,
      i.sgt_stk_prt,
      //  i.dismm,

      c.kdgrp,
      c.fsh_grreg,
      //  c.fsh_grsgy,

      //  c.fsh_resgy,

      i.sgt_covs,
      i.mspart,
      i.color_atinn,
      i.size1_atinn,
      i.size2_atinn,
      i.color,
      i.size1,
      i.size2,
      i.fsh_mg_at1,
      i.fsh_mg_at2,
      i.fsh_mg_at3,
      c.kvgr1,
      c.kvgr2,
      c.kvgr3,
      c.kvgr4,
      c.kvgr5,
      c.fsh_kvgr6,
      c.fsh_kvgr7,
      c.fsh_kvgr8,
      c.fsh_kvgr9,
      c.fsh_kvgr10,
      k1.kunnr                                                                       as ikunnr,
      k2.kunnr                                                                       as hkunnr,
      k1.kunnr                                                                       as kunwe,
      vas.fsh_increment,
      vas.fsh_req_rel,
      0                                                                              as VBFA,
      0                                                                              as BDBS,
      sl.fsh_os_id,
      i.fsh_grid_cond_rec,
      i.kwmeng
}
where
  (
    h.uvvlk = 'C'
  ) -- header incompletion status concerning delivery
  and(
    h.rfgsk != 'C'
  ) -- total refernce status of all items
  and(
    sl.bmeng != 0
  ) -- confirmed quantity
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"ARUN_SO_RALLOC",
"ARUN_SO_REQ1",
"FSH_VASS_TL",
"KNVV",
"TVAK",
"VBAK",
"VBEP",
"VBPA"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/