FSH_DDL_TPO

@EndUserText.label: 'TPO Extract'
@AbapCatalog.sqlViewName: 'V_FSH_TPO'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ClientHandling.algorithm: #AUTOMATED
@ObjectModel.usageType: {serviceQuality: #X, sizeCategory: #XXL, dataClass: #TRANSACTIONAL}
define view FSH_DDL_TPO
  as select from    fsh_ddl_tpo_4 as m

    inner join      pph_e_Ekkn    as e    on  m.delnr =  e.ebeln
                                          and m.delps =  e.ebelp
                                          and e.vbeln <> ''
    left outer join vbap          as vbap on  e.vbeln = vbap.vbeln
                                          and e.vbelp = vbap.posnr
{
  m.matnr,
  m.werks,
  m.berid,
  mdbs_bstme                    as meins, //*** ??

  // round(division(psamg * umrez , umren, 4) ,3)

  case when ( umren != 1 or umrez != 1 ) and umren != 0 and umrez != 0
            then
            case when plumi = '+' then round(division(unconvmng * umrez , umren, 4) ,3)
                                       else - round(division(unconvmng * umrez , umren, 4) ,3)
                 end
            else case when plumi = '+' then unconvmng
                                       else - unconvmng
           end
  end                           as mng01,

  dat01                         as dat00, --Delivery date
  --fixkz as fixkz, --Fixing Ind. (EKET)
  delkz, -- Requirement Delkz
  plumi,
  m.lgort,
  m.charg,
  delnr, -- PO Number
  delps, -- PO Item
  delet,
  m.knttp, -- Account Assignment Category
  fixkz,
  m.kzvbr, -- Consumption
  m.sobkz,
  e.vbeln                       as kdauf, -- Third Party Order Number
  coalesce(e.vbelp,'000000')    as kdpos, -- Third Party Order Item
  case when m.sobkz <> '' then e.ps_psp_pnr
       else '00000000'
  end                           as pspel,

  lifnr,
  bedat, -- PO Date
  etmen, -- Scheduled Qty
  glmng, -- Qty Delivered
  case when ( umren != 1 or umrez != 1 ) and umren != 0 and umrez != 0 then
    round(division(auftm * umrez , umren, 4) ,3)
  else
    auftm
  end                           as auftm,
  m.kzbws, -- Valuation
  case when ( umren != 1 or umrez != 1 ) and umren != 0 and umrez != 0 then
    round(division(mdmng_unconv * umrez , umren, 4) ,3)
  else
    mdmng_unconv
  end                           as mdmng,

  case when ( umren != 1 or umrez != 1 ) and umren != 0 and umrez != 0 then
    round(division(unconvmng * umrez , umren, 4) ,3) +
    round(division(mdbs_wemng_db * umrez , umren, 4) ,3)
  else
    unconvmng + mdbs_wemng_db
  end                           as tpmng,
  sgt_scat,
  vbap.sgt_rcat,
  mdbs_statu, -- Should Only  be 'V' for Created via Sales document in EKPO
  mdbs_bstme,
  mdbs_no_disp,
  mdbs_wepos, -- Goods receipt EKPO-WEPOS
  mdbs_emlif, -- Supplier
  mdbs_lblkz, -- SC Supplier
  mdbs_menge_db, -- PO Quantity
  mdbs_wemng_db, -- Delivered
  mdbs_wamng_db, -- Issued
  mdbs_dabmg_db, -- Qty Reduced
  mdbs_ameng,    -- Previous Qty
  mdbs_fixmg,    -- Fixed Date (CheckBox)

  case when ( umren != 1 or umrez != 1 ) and umren != 0 and umrez != 0
            then case when plumi = '+' then round(division(unconv_ameng * umrez , umren, 4) ,3)
                                       else - round(division(unconv_ameng * umrez , umren, 4) ,3)
                 end
            else
                 case when plumi = '+' then unconv_ameng
                                       else - unconv_ameng
            end
  end                           as mdbs_amng01,

  vbap.fsh_season_year          as fsh_season_year,
  vbap.fsh_season               as fsh_season,
  vbap.fsh_collection           as fsh_collection,
  vbap.fsh_theme                as fsh_theme

}

union

-- Extract of TPOs with only PRs and no POs created yet
select from       eban
  left outer join pph_e_Ebkn as ebkn on  eban.banfn = ebkn.banfn
                                     and eban.bnfpo = ebkn.bnfpo
  left outer join vbap       as vbap on  ebkn.vbeln = vbap.vbeln
                                     and ebkn.vbelp = vbap.posnr

{
  eban.matnr                                                 as matnr,
  eban.werks                                                 as werks,
  eban.berid,
  eban.meins,
  case
  when statu = 'B' or statu = 'L' then eban.menge - bsmng
  else eban.menge
  end                                                        as mng01,
  eban.lfdat                                                 as dat00, -- Item delivery date
  'BA'                                                       as delkz,
  '+'                                                        as plumi,
  eban.lgort                                                 as lgort,
  case
      when eban.bwtar = '          ' or
           eban.bwtar = '' or
           eban.bwtar is null then eban.charg
      else eban.bwtar
  end                                                        as charg,
  eban.banfn                                                 as delnr,
  cast(rpad(eban.bnfpo,6,' ') as pph_delps preserving type)  as delps,
  cast('' as abap.numc( 4 ))                                 as DELET,
  eban.knttp                                                 as knttp,
  fixkz,
  eban.kzvbr                                                 as kzvbr,
  eban.sobkz                                                 as sobkz,
  coalesce(ebkn.vbeln,'')                                    as kdauf,
  coalesce(ebkn.vbelp,'000000')                              as kdpos,
  coalesce(ebkn.ps_psp_pnr,'00000000')                       as pspel,
  eban.flief                                                 as lifnr,
  bedat,
  cast (0 as abap.dec(13,3))                                 as ETMEN,
  cast (0 as abap.dec(13,3))                                 as GLMNG,
  cast (0 as abap.dec(13,3))                                 as auftm,
  case when eban.sobkz = '' then '' else eban.kzbws end      as kzbws,
  cast (0 as abap.dec(13,3))                                 as MDMNG,
  cast (0 as abap.dec(13,3))                                 as TPMNG,
  eban.sgt_scat                                              as sgt_scat,
  vbap.sgt_rcat                                              as sgt_rcat,
  ''                                                         as MDBS_STATU,
  cast ('' as abap.unit( 3 ))                                as MDBS_BSTME,
  ''                                                         as MDBS_NO_DISP,
  ''                                                         as MDBS_WEPOS,
  ''                                                         as MDBS_EMLIF,
  ''                                                         as MDBS_LBLKZ,
  cast (0 as abap.dec(13,3))                                 as MDBS_MENGE_DB,
  cast (0 as abap.dec(13,3))                                 as MDBS_WEMNG_DB,
  cast (0 as abap.dec(13,3))                                 as MDBS_WAMNG_DB,
  cast (0 as abap.dec(13,3))                                 as MDBS_DABMG_DB,
  cast (0 as abap.dec(13,3))                                 as MDBS_AMENG,
  ''                                                         as MDBS_FIXMG,
  cast (0 as abap.dec(13,3))                                 as MDBS_AMNG01,
  vbap.fsh_season_year                                       as fsh_season_year,
  vbap.fsh_season                                            as fsh_season,
  vbap.fsh_collection                                        as fsh_collection,
  vbap.fsh_theme                                             as fsh_theme

}

where
            eban.loekz =  ''  -- Filter: Deletion indicator must not be set
  and       eban.ebakz =  ''  -- Filter: Purchase requisition must not be closed
  and       eban.pstyp =  '5' -- Third Party Items
  and       eban.bsakz =  ''  -- Filter: Regular purchase requisitions only
  and(
            memory     =  ''
    or      memorytype <> 'H' -- Filter: No held incomplete PRs
    or      estkz      =  'B' -- Always consider EBANs created by MRP
    or      estkz      =  'D'
  )                           -- Always consider direct procurement EBANs
  -- Filter: Order quantity must be positive
  and(
    (
      (
            statu      =  'B'
        or  statu      =  'L'
      )
      and   eban.menge >  bsmng
    )
    or(
      (
            statu      <> 'B'
        and statu      <> 'L'
      )
      and   eban.menge >  0
    )
  )