P_Matval_Manage_Bal

@AbapCatalog.sqlViewName: 'PMATVALMGMBAL'
@VDM.private: true
@VDM.viewType: #BASIC
@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view P_Matval_Manage_Bal
  as select from acdoca_m_extract as ex
    inner join   t001k            as tk on tk.bwkey = ex.bwkey
//    inner join   marv             as mv on mv.bukrs = tk.bukrs


{
  key       kalnr                                       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 fin_qtyval)           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
  ex.fiscyearper = '9999999'

group by
  ex.rbukrs,
  ex.rldnr,
  ex.kalnr,
  ex.bwkey,
  ex.matnr

union all

select from  acdoca_m_extract as ex
  inner join t001k            as tk on tk.bwkey = ex.bwkey
//  inner join marv             as mv on mv.bukrs = tk.bukrs

  inner join P_MatlCtrlRecdLdgrFiscalPeriod as marvld on  marvld.CompanyCode = ex.rbukrs
                                                      and marvld.Ledger = ex.rldnr  

{
  key       kalnr                                       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
  ex.fiscyearper > marvld.PreviousFiscalYearPeriod 

group by
  ex.rbukrs,
  ex.rldnr,
  ex.kalnr,
  ex.bwkey,
  ex.matnr

union all

select from  acdoca_m_extract as ex
  inner join t001k            as tk on tk.bwkey = ex.bwkey
//  inner join marv             as mv on mv.bukrs = tk.bukrs

  inner join P_MatlCtrlRecdLdgrFiscalPeriod as marvld on  marvld.CompanyCode = ex.rbukrs
                                                      and marvld.Ledger = ex.rldnr   

{
  key       kalnr                                       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
  ex.fiscyearper > marvld.PrevFsclYearLastFsclYearPeriod 

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