Adk0_Dli_Wip_Detail_Vbl_View

@AbapCatalog.sqlViewName: 'ADK0_DLI_VBL_WIP'
@ClientDependent: true
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'DLI WIP Detail View for SD Documents'

@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.dataClass: #MIXED

//Selects DIP based WIP detail data for sales orders,

//ad01wip_dtl is calcualted by DIP tool but update is triggered by results analysis and event based revenue recognition

//Used in report SAPKKA_ADK0_02

//Selects from ad01dli, ad01dlisf, ad01wip_dtl, vbap, t001w, t001k, tka02 and tka01


define view Adk0_Dli_Wip_Detail_Vbl_View as

select a.poper as poper,
       a.twaer as in_twaer,
       a.meinh as meinh,
       a.kstar as kstar,
       a.kostl as kostl,
       a.lstar as lstar,
       a.matnr as matnr,
       a.prznr as prznr,
       a.pernr as pernr,
       a.manual as MANUAL,
       a.prctr as prctr,
       d.werks as werks,
       d.gsber as gsber,
       f.bukrs as bukrs,
       g.kokrs as kokrs,
       d.vbeln as vbeln,
       d.posnr as posnr,
       sum(b.in_wtgbtr) as IN_WTGBTR,
       sum(b.in_wogbtr) as IN_WOGBTR,
       sum(b.in_wkgbtr) as IN_WKGBTR,
       sum(b.in_megbtr) as IN_MEGBTR,
       sum(b.no_wtgbtr) as NO_WTGBTR,
       sum(b.no_wogbtr) as NO_WOGBTR,
       sum(b.no_wkgbtr) as NO_WKGBTR,
       sum(b.no_megbtr) as NO_MEGBTR,
/*     sum(b.rv_wtgbtr1) as RV_WTGBTR1,
       sum(b.rv_wtgbtr2) as RV_WTGBTR2,
       sum(b.rv_wtgbtr3) as RV_WTGBTR3,
       sum(b.rv_wtgbtr4) as RV_WTGBTR4,  */
       b.rv_waers as RV_WAERS,
       d.waerk as in_owaer,
       h.waers as in_kwaer,
       sum(c.abc_wtg) as ABC_WTG,
       sum(c.abc_wog) as ABC_WOG,
       sum(c.abc_wkg) as ABC_WKG,
       sum(c.abc_meg) as ABC_MEG,
       sum(c.noc_wtg) as NOC_WTG,
       sum(c.noc_wog) as NOC_WOG,
       sum(c.noc_wkg) as NOC_WKG,
       sum(c.noc_meg) as NOC_MEG,
       sum(c.tbc_wtg) as TBC_WTG,
       sum(c.tbc_wog) as TBC_WOG,
       sum(c.tbc_wkg) as TBC_WKG,
       sum(c.tbc_meg) as TBC_MEG,
       sum(c.tbr_wtg) as TBR_WTG,
       sum(c.tbr_wog) as TBR_WOG,
       sum(c.tbr_wkg) as TBR_WKG,
       c.hwaer as HWAER,
       c.kwaer as KWAER,
       c.rv_waers as TBR_RV_WAERS,
       cast (' ' as kstar) as rev_kstar,
       cast (' ' as matnr) as rev_matnr,
       cast (0 as ad01rev_wtg) as rev_wtg,
       cast (0 as ad01rev_wog) as rev_wog,
       cast (0 as ad01rev_wkg) as rev_wkg
  from ad01dli as a
  left outer join ad01dlisf as b on a.dlinr = b.dlinr and b.dlius = '01'
  left outer join ad01wip_dtl as c on a.dlinr = c.dlinr and c.ra_versn = '000'
  inner join vbap as d on d.objnr = a.objnr and d.kzvbr = 'E'
  inner join t001w as e on e.werks = d.werks
  inner join t001k as f on f.bwkey = e.bwkey
  inner join tka02 as g on g.bukrs = f.bukrs
  inner join tka01 as h on h.kokrs = g.kokrs
  where d.vbelv = d.vbeln and d.posnv = d.posnr
  group by a.poper,
           a.twaer,
           a.meinh,
           a.kstar,
           a.kostl,
           a.lstar,
           a.matnr,
           a.prznr,
           a.pernr,
           a.manual,
           a.prctr,
           d.werks,
           d.gsber,
           f.bukrs,
           g.kokrs,
           d.waerk,
           h.waers,
           d.vbeln,
           d.posnr,
           b.rv_waers,
           c.hwaer,
           c.kwaer,
           c.rv_waers

union all select
       a.poper as poper,
       a.twaer as in_twaer,
       a.meinh as meinh,
       a.kstar as kstar,
       a.kostl as kostl,
       a.lstar as lstar,
       a.matnr as matnr,
       a.prznr as prznr,
       a.pernr as pernr,
       a.manual as MANUAL,
       a.prctr as prctr,
       d.werks as werks,
       d.gsber as gsber,
       f.bukrs as bukrs,
       g.kokrs as kokrs,
       d.vbeln as vbeln,
       d.posnr as posnr,
       cast (0 as eb_fkwrt) as IN_WTGBTR,
       cast (0 as eb_fkwrt) as IN_WOGBTR,
       cast (0 as eb_fkwrt) as IN_WKGBTR,
       cast (0 as eb_fkmng) as IN_MEGBTR,
       cast (0 as ad01nowrt) as NO_WTGBTR,
       cast (0 as ad01nowrt) as NO_WOGBTR,
       cast (0 as ad01nowrt) as NO_WKGBTR,
       cast (0 as ad01nomng) as NO_MEGBTR,
/*     sum(b.rv_wtgbtr1) as RV_WTGBTR1,
       sum(b.rv_wtgbtr2) as RV_WTGBTR2,
       sum(b.rv_wtgbtr3) as RV_WTGBTR3,
       sum(b.rv_wtgbtr4) as RV_WTGBTR4,  */
       b.rv_waers as RV_WAERS,
       d.waerk as in_owaer,
       h.waers as in_kwaer,
       cast (0 as ad01abc_wtg) as ABC_WTG,
       cast (0 as ad01abc_wog) as ABC_WOG,
       cast (0 as ad01abc_wkg) as ABC_WKG,
       cast (0 as ad01abc_meg) as ABC_MEG,
       cast (0 as ad01noc_wtg) as NOC_WTG,
       cast (0 as ad01noc_wog) as NOC_WOG,
       cast (0 as ad01noc_wkg) as NOC_WKG,
       cast (0 as ad01noc_meg) as NOC_MEG,
       cast (0 as ad01tbc_wtg) as TBC_WTG,
       cast (0 as ad01tbc_wog) as TBC_WOG,
       cast (0 as ad01tbc_wkg) as TBC_WKG,
       cast (0 as ad01tbc_meg) as TBC_MEG,
       cast (0 as ad01rev_wtg) as TBR_WTG,
       cast (0 as ad01rev_wog) as TBR_WOG,
       cast (0 as ad01rev_wkg) as TBR_WKG,
       c.hwaer as HWAER,
       c.kwaer as KWAER,
       c.rv_waers as TBR_RV_WAERS,
       c.rev_kstar,
       c.rev_matnr,
       sum(rev_wtg) as rev_wtg,
       sum(rev_wog) as rev_wog,
       sum(rev_wkg) as rev_wkg
  from ad01dli as a
  left outer join ad01dlisf as b on a.dlinr = b.dlinr and b.dlius = '01'
  left outer join ad01wip_rev as c on a.dlinr = c.dlinr and c.ra_versn = '000'
  inner join vbap as d on d.objnr = a.objnr and d.kzvbr = 'E'
  inner join t001w as e on e.werks = d.werks
  inner join t001k as f on f.bwkey = e.bwkey
  inner join tka02 as g on g.bukrs = f.bukrs
  inner join tka01 as h on h.kokrs = g.kokrs
  where d.vbelv = d.vbeln and d.posnv = d.posnr
  group by a.poper,
           a.twaer,
           a.meinh,
           a.kstar,
           a.kostl,
           a.lstar,
           a.matnr,
           a.prznr,
           a.pernr,
           a.manual,
           a.prctr,
           d.werks,
           d.gsber,
           f.bukrs,
           g.kokrs,
           d.waerk,
           h.waers,
           d.vbeln,
           d.posnr,
           b.rv_waers,
           c.hwaer,
           c.kwaer,
           c.rv_waers,
           c.rev_kstar,
           c.rev_matnr

;