P_Matval_Manage_Bal_Split

@AbapCatalog.sqlViewName: 'PMATVALMGMBALSPL'
@VDM.private: true
@VDM.viewType: #BASIC
@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view P_Matval_Manage_Bal_Split
  as select from            mbew             as hd
    left outer to many join mbew             as ch on  ch.matnr =  hd.matnr
                                                   and ch.bwkey =  hd.bwkey
    inner join              acdoca_m_extract as ex on  ex.kalnr = ch.kaln1
                                                   and ex.matnr = ch.matnr
                                                   and ex.bwkey = ch.bwkey
{
  key       hd.kaln1                                    as CostEstimate,
  key       cast(ex.rldnr as fis_rldnr preserving type) as Ledger,
  key       ex.rbukrs                                   as CompanyCode,
  key       ex.bwkey                                    as ValuationArea,
  key       ex.matnr                                    as Material,
  key       'LF'                                        as period_type,

            cast (sum(ex.vmsl) as vquan1_12)            as TotalVltdStockQuantity,
            cast (sum(hsl)  as fins_vhcur12)            as AmountInCompanyCodeCurrency,
            cast (sum(ksl) as fins_vkcur12)             as AmountInGroupCurrency,
            cast (sum(osl) as fins_vocur12)             as AmountInFreeDefinedCurrency1,
            cast (sum(vsl) as fins_vvcur12)             as AmountInFreeDefinedCurrency2,
            cast (sum(bsl) as fins_vbcur12)             as AmountInFreeDefinedCurrency3,
            cast (sum(csl) as fins_vccur12)             as AmountInFreeDefinedCurrency4,
            cast (sum(dsl) as fins_vdcur12)             as AmountInFreeDefinedCurrency5,
            cast (sum(esl) as fins_vecur12)             as AmountInFreeDefinedCurrency6,
            cast (sum(fsl) as fins_vfcur12)             as AmountInFreeDefinedCurrency7,
            cast (sum(gsl) as fins_vgcur12)             as AmountInFreeDefinedCurrency8
}
where
      hd.bwtar       =  ''
  and hd.bwtty       <> ''
  and ex.fiscyearper =  '9999999'

group by
  ex.rbukrs,
  ex.bwkey,
  ex.rldnr,
  hd.kaln1,
  ex.matnr

union all select from mbew             as hd
  inner join          t001k            as tk on tk.bwkey = hd.bwkey
//  inner join          marv             as mv on mv.bukrs = tk.bukrs

  left outer join     mbew             as ch on  ch.matnr =  hd.matnr
                                             and ch.bwkey =  hd.bwkey
  inner join          acdoca_m_extract as ex on  ex.kalnr = ch.kaln1
                                             and ex.matnr = ch.matnr
                                             and ex.bwkey = ch.bwkey
  inner join          P_MatlCtrlRecdLdgrFiscalPeriod as marvld on  marvld.CompanyCode = ex.rbukrs
                                                               and marvld.Ledger = ex.rldnr
                                                 
{
  key       hd.kaln1                                    as CostEstimate,
  key       cast(ex.rldnr as fis_rldnr preserving type) as Ledger,
  key       ex.rbukrs                                   as CompanyCode,
  key       ex.bwkey                                    as ValuationArea,
  key       ex.matnr                                    as Material,
  key       'VM'                                        as period_type,

            cast (sum(ex.vmsl) as vquan1_12)            as TotalVltdStockQuantity,
            cast (sum(hsl)  as fins_vhcur12)            as AmountInCompanyCodeCurrency,
            cast (sum(ksl) as fins_vkcur12)             as AmountInGroupCurrency,
            cast (sum(osl) as fins_vocur12)             as AmountInFreeDefinedCurrency1,
            cast (sum(vsl) as fins_vvcur12)             as AmountInFreeDefinedCurrency2,
            cast (sum(bsl) as fins_vbcur12)             as AmountInFreeDefinedCurrency3,
            cast (sum(csl) as fins_vccur12)             as AmountInFreeDefinedCurrency4,
            cast (sum(dsl) as fins_vdcur12)             as AmountInFreeDefinedCurrency5,
            cast (sum(esl) as fins_vecur12)             as AmountInFreeDefinedCurrency6,
            cast (sum(fsl) as fins_vfcur12)             as AmountInFreeDefinedCurrency7,
            cast (sum(gsl) as fins_vgcur12)             as AmountInFreeDefinedCurrency8
}
where
      hd.bwtar       =  ''
  and hd.bwtty       <> ''
  and ex.fiscyearper > marvld.PreviousFiscalYearPeriod 

group by
  ex.rbukrs,
  ex.bwkey,
  ex.rldnr,
  hd.kaln1,
  ex.matnr

union all select from mbew             as hd
  inner join          t001k            as tk on tk.bwkey = hd.bwkey
//  inner join          marv             as mv on mv.bukrs = tk.bukrs

  left outer join     mbew             as ch on  ch.matnr =  hd.matnr
                                             and ch.bwkey =  hd.bwkey
  inner join          acdoca_m_extract as ex on  ex.kalnr = ch.kaln1
                                             and ex.matnr = ch.matnr
                                             and ex.bwkey = ch.bwkey
  inner join          P_MatlCtrlRecdLdgrFiscalPeriod as marvld on  marvld.CompanyCode = ex.rbukrs
                                                               and marvld.Ledger = ex.rldnr                                             

{
  key       hd.kaln1                                    as CostEstimate,
  key       cast(ex.rldnr as fis_rldnr preserving type) as Ledger,
  key       ex.rbukrs                                   as CompanyCode,
  key       ex.bwkey                                    as ValuationArea,
  key       ex.matnr                                    as Material,
  key       'VJ'                                        as period_type,

            cast (sum(ex.vmsl) as vquan1_12)            as TotalVltdStockQuantity,
            cast (sum(hsl)  as fins_vhcur12)            as AmountInCompanyCodeCurrency,
            cast (sum(ksl) as fins_vkcur12)             as AmountInGroupCurrency,
            cast (sum(osl) as fins_vocur12)             as AmountInFreeDefinedCurrency1,
            cast (sum(vsl) as fins_vvcur12)             as AmountInFreeDefinedCurrency2,
            cast (sum(bsl) as fins_vbcur12)             as AmountInFreeDefinedCurrency3,
            cast (sum(csl) as fins_vccur12)             as AmountInFreeDefinedCurrency4,
            cast (sum(dsl) as fins_vdcur12)             as AmountInFreeDefinedCurrency5,
            cast (sum(esl) as fins_vecur12)             as AmountInFreeDefinedCurrency6,
            cast (sum(fsl) as fins_vfcur12)             as AmountInFreeDefinedCurrency7,
            cast (sum(gsl) as fins_vgcur12)             as AmountInFreeDefinedCurrency8
}
where
      hd.bwtar       =  ''
  and hd.bwtty       <> ''
  and ex.fiscyearper > marvld.PrevFsclYearLastFsclYearPeriod

group by
  ex.rbukrs,
  ex.bwkey,
  ex.rldnr,
  hd.kaln1,
  ex.matnr
  
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"P_MATLCTRLRECDLDGRFISCALPERIOD",
"ACDOCA_M_EXTRACT",
"MBEW",
"T001K"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/