I_MaterialLedger

@AbapCatalog.sqlViewName: 'IMATERIALLEDGER'
@EndUserText.label: 'Material Ledger Interface'
@VDM.viewType: #COMPOSITE
//@Analytics: {dataCategory: #DIMENSION, dataExtraction.enabled: true}

@AccessControl.authorizationCheck: #CHECK
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #XXL
@ObjectModel.usageType.dataClass: #MIXED
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.representativeKey: 'LedgerGLLineItem'
@Metadata.allowExtensions:true

define view I_MaterialLedger
  as select from           P_Materialledger_CurTP as it 
    
  inner join R_MatlPriceDataMigrationStatus as tf on ( tf.MatlPriceDataMigrationStatus = 'X' ) 
                                                  or ( tf.MatlPriceDataMigrationStatus = 'R' ) 
  left outer to one join ckmlhd                     as hd         on hd.kalnr             = it.kalnr
  //------------------------------------------------------------

  //  Part 1: Entry for currency column type "H" 

  //------------------------------------------------------------

  left outer to one join ckmlcr                 as _CkmlcrH on  it.kalnr        = _CkmlcrH.kalnr
                                                              and it.gjahr        = _CkmlcrH.bdatj
                                                              and it.poper        = _CkmlcrH.poper
                                                              and _CkmlcrH.untper = '000'
                                                              and _CkmlcrH.curtp  = it.ext_CurtpH 
  //------------------------------------------------------------

  //  Part 2: Entry for currency column type "K"

  //------------------------------------------------------------

    left outer to one join ckmlcr                 as _CkmlcrK on  it.kalnr        = _CkmlcrK.kalnr
                                                              and it.gjahr        = _CkmlcrK.bdatj
                                                              and it.poper        = _CkmlcrK.poper
                                                              and _CkmlcrK.untper = '000'
                                                              and _CkmlcrK.curtp  = it.ext_CurtpK

  //------------------------------------------------------------

  //  Part 3: Entry for currency column type "O"

  //------------------------------------------------------------

    left outer to one join ckmlcr                 as _CkmlcrO on  it.kalnr        = _CkmlcrO.kalnr
                                                              and it.gjahr        = _CkmlcrO.bdatj
                                                              and it.poper        = _CkmlcrO.poper
                                                              and _CkmlcrO.untper = '000'
                                                              and _CkmlcrO.curtp  = it.ext_CurtpO

  //------------------------------------------------------------

  //  Part 4: Entry for currency column type "V"

  //------------------------------------------------------------

    left outer to one join ckmlcr                 as _CkmlcrV on  it.kalnr        = _CkmlcrV.kalnr
                                                              and it.gjahr        = _CkmlcrV.bdatj
                                                              and it.poper        = _CkmlcrV.poper
                                                              and _CkmlcrV.untper = '000'
                                                              and _CkmlcrV.curtp  = it.ext_CurtpV                     
                                                              
    association [0..1] to I_MatlLedgerValnCrcyRoleName as _MLCurrencyInfo on  _MLCurrencyInfo.CompanyCode                    = it.rbukrs
                                                                          and _MLCurrencyInfo.Ledger                         = it.rldnr
                                                                          and _MLCurrencyInfo.LedgerValnCrcyRoleIsUsedInLogs = 'X'

{
      @ObjectModel.foreignKey.association: '_CompanyCode'
  key it.rbukrs                    as CompanyCode,
      @ObjectModel.foreignKey.association: '_Ledger'
  key it.rldnr                     as Ledger,
      @ObjectModel.foreignKey.association: '_GLAccountInChartOfAccounts'
  key it.racct                     as GLAccount,
      @ObjectModel.foreignKey.association: '_FiscalYear'
  key it.gjahr                     as FiscalYear,
      @ObjectModel.foreignKey.association: '_JournalEntry'
  key it.belnr                     as AccountingDocument,
  key it.docln                     as LedgerGLLineItem,
      @ObjectModel.foreignKey.association: '_Material'
  key it.matnr                     as Material,
      @ObjectModel.foreignKey.association: '_ChartOfAccounts'
      it.ktopl                     as ChartOfAccounts,
      it.blart                     as AccountingDocumentType,
      it.bwkey                     as ValuationArea,
      it.bwtar                     as InventoryValuationType,
      it.poper                     as FiscalPeriod,
      it.ryear                     as LedgerFiscalYear,
      it.budat                     as PostingDate,
      it.budat                     as MatlDocLatestPostgDate,
      it.awref                     as ReferenceDocument,
      it.awtyp                     as ReferenceDocumentType,
      it.bldat                     as DocumentDate,
      it.bttype                    as BusinessTransactionType,
      it.prctr                     as ProfitCenter,
      it.rcntr                     as CostCenter,
      it.segment                   as Segment,
      it.usnam                     as AccountingDocCreatedByUser,
      it.timestamp                 as LastChangeDateTime,
      it.fiscyearper               as FiscalYearPeriod,
      it.ebeln                     as PurchasingDocument,
      it.aufnr                     as ProductionOrder,
      it.ktosl                     as TransactionTypeDetermination,
      it.werks                     as Plant,
      @ObjectModel.foreignKey.association: '_ControllingArea'
      it.kokrs                     as ControllingArea,
      it.kalnr                     as CostEstimate,
      it.sobkz                     as InvtryValnSpecialStockType,
      it.mlptyp                    as MaterialLedgerProcessType,
      it.mlcateg                   as MaterialLedgerCategory,
      @ObjectModel.foreignKey.association: '_FiscalYearVariant'
      periv                        as FiscalYearVariant,
      //----------------------------------------

      //KEY FIGURES

      //----------------------------------------

      @Semantics.currencyCode:true
      it.rhcur                     as CompanyCodeCurrency,
      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
      it.hsl                       as AmountInCompanyCodeCurrency,
      @Semantics.currencyCode:true
      it.rkcur                     as GroupCurrency,
      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'GroupCurrency'} }
      it.ksl                       as AmountInGroupCurrency,
      @Semantics.currencyCode:true
      it.rocur                     as AdditionalCurrency1,
      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'AdditionalCurrency1'} }
      it.osl                       as AmountInAdditionalCurrency1,
      @Semantics.currencyCode:true
      it.rvcur                     as AdditionalCurrency2,
      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'AdditionalCurrency2'} }
      it.vsl                       as AmountInAdditionalCurrency2,
      @Semantics.unitOfMeasure:true
      it.rvunit                    as BaseUnit,
      @DefaultAggregation: #SUM
      @Semantics: { quantity : {unitOfMeasure: 'BaseUnit'} }
      cast(it.vmsl as fcml_lbkum)  as InventoryQty,
      //----------------------------------------

      //For Rounding Difference (Just S-price, V-price makes no sense for rounding differences)

      //----------------------------------------

      @DefaultAggregation: #MAX
      @Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'}}
      case _CkmlcrH.vprsv
        when 'S' then COALESCE(_CkmlcrH.stprs, 0)
        when 'V' then COALESCE(_CkmlcrH.pvprs, 0)
        else COALESCE(_CkmlcrH.stprs, 0)
      end                          as MaterialPriceInCoCodeCurrency,
      @DefaultAggregation: #MAX
      COALESCE(_CkmlcrH.peinh, 0)  as MatPriceUnitInCoCodeCurrency,

      @DefaultAggregation: #MAX
      @Semantics: { amount : {currencyCode: 'GroupCurrency'} }
      case _CkmlcrK.vprsv
        when 'S' then COALESCE(_CkmlcrK.stprs, 0)
        when 'V' then COALESCE(_CkmlcrK.pvprs, 0)
        else COALESCE(_CkmlcrK.stprs, 0)
      end                          as MaterialPriceInGroupCurrency,

      @DefaultAggregation: #MAX
//      @Semantics: { quantity : {unitOfMeasure: 'BaseUnit'} }

      COALESCE(_CkmlcrK.peinh, 0)  as MatPriceUnitInGroupCurrency,

      @DefaultAggregation: #MAX
      @Semantics: { amount : {currencyCode: 'AdditionalCurrency1'} }
      case _CkmlcrO.vprsv
        when 'S' then COALESCE(_CkmlcrO.stprs, 0)
        when 'V' then COALESCE(_CkmlcrO.pvprs, 0)
        else COALESCE(_CkmlcrO.stprs, 0)
      end                          as MaterialPriceInAddlCurrency1,

      @DefaultAggregation: #MAX
//      @Semantics: { quantity : {unitOfMeasure: 'BaseUnit'} }

      COALESCE(_CkmlcrO.peinh, 0)  as MatPriceUnitInAddlCurrency1,

      @DefaultAggregation: #MAX
      @Semantics: { amount : {currencyCode: 'AdditionalCurrency2'} }
      case _CkmlcrV.vprsv
        when 'S' then COALESCE(_CkmlcrV.stprs, 0)
        when 'V' then COALESCE(_CkmlcrV.pvprs, 0)
        else COALESCE(_CkmlcrV.stprs, 0)
      end                          as MaterialPriceInAddlCurrency2,

      @DefaultAggregation: #MAX
//      @Semantics: { quantity : {unitOfMeasure: 'BaseUnit'} }

      COALESCE(_CkmlcrV.peinh, 0)  as MatPriceUnitInAddlCurrency2,
      
      COALESCE(_CkmlcrH.vprsv, '') as MaterialPriceControl,
      it.CurtpK                    as CurrencyRole,
      it.CurtpO                    as AdditionalCurrency1Role,
      it.CurtpV                    as AdditionalCurrency2Role,
      //----------------------------------------

      //Additional fields for V_MaterialStockValueByKeyDate

      //----------------------------------------

      it.xobew                     as IsSupplierStockValuation,
      it.kzbws                     as InventorySpecialStockValnType,
      it.mat_kdauf                 as InventorySpclStkSalesDocument,
      it.mat_kdpos                 as InventorySpclStkSalesDocItm,
      it.mat_pspnr                 as WBSElementInternalID,
      it.mat_lifnr                 as InventorySpecialStockSupplier,
      _MLCurrencyInfo.LedgerValnCrcyRoleIsUsedInLogs as LedgerValnCrcyRoleIsUsedInLogs,
      //----------------------------------------

      //Associations

      //----------------------------------------

      _GLAccountInChartOfAccounts,
      _ChartOfAccounts,
      _Segment,
      _Material,
      _CompanyCode,
      _ProfitCenter,
      _CostCenter,
      _CompanyCodeCurrency,
      _GlobalCurrency,
      _FreeDefinedCurrency1,
      _FreeDefinedCurrency2,
      _CostSourceUnit,
      _ControllingArea,
      _Ledger,
      _FiscalYear,
      _FiscalYearVariant,
      _JournalEntry,
      _MaterialValuation,
      _BusinessTransactionType
}
where ( ( hd.price_sdm_yearper = '0000000' or hd.price_sdm_yearper > concat( _CkmlcrH.bdatj, _CkmlcrH.poper ) ) or ( tf.MatlPriceDataMigrationStatus = 'X' ) )
    and ( _CkmlcrH.sdm_version is initial )

union all
  
select from           P_Materialledger_CurTP as it 
    inner join R_MatlPriceDataMigrationStatus as tf on ( tf.MatlPriceDataMigrationStatus = 'F' ) 
                                                    or ( tf.MatlPriceDataMigrationStatus = 'R' )
    left outer to one join      ckmlhd                as hd         on  hd.kalnr             = it.kalnr   
                                                                    and hd.matnr             = it.matnr
                                                                    and hd.bwkey             = it.bwkey
                                               
  //------------------------------------------------------------

  //  Part 1: Entry for currency column type "H"

  //------------------------------------------------------------

left outer to one join I_MaterialLedgerPrice        as _FmltpH_inv on _FmltpH_inv.CostEstimate                   = it.kalnr 
                                                              and _FmltpH_inv.Material                           = it.matnr
                                                              and _FmltpH_inv.ValuationArea                      = it.bwkey
                                                              and _FmltpH_inv.MatlPrcValdtyStrtFsclYearPerd      <= it.fiscyearper
                                                              and _FmltpH_inv.MatlPrcValdtyEndFsclYearPeriod     >= it.fiscyearper
                                                              and _FmltpH_inv.Ledger                             = it.rldnrh
                                                              and _FmltpH_inv.CurrencyRole                       = it.ext_CurtpH 
                                                              and _FmltpH_inv.MaterialPriceType                  = 'INVPR'
                                                              and _FmltpH_inv.MaterialPriceSubtype               = ''                          
  //------------------------------------------------------------

  //  Part 2: Entry for currency column type "K"

  //------------------------------------------------------------

left outer to one join I_MaterialLedgerPrice        as _FmltpK_inv on _FmltpK_inv.CostEstimate                   = it.kalnr 
                                                              and _FmltpK_inv.Material                           = it.matnr
                                                              and _FmltpK_inv.ValuationArea                      = it.bwkey
                                                              and _FmltpK_inv.MatlPrcValdtyStrtFsclYearPerd      <= it.fiscyearper
                                                              and _FmltpK_inv.MatlPrcValdtyEndFsclYearPeriod     >= it.fiscyearper
                                                              and _FmltpK_inv.Ledger                             = it.rldnrk
                                                              and _FmltpK_inv.CurrencyRole                       = it.ext_CurtpK
                                                              and _FmltpK_inv.MaterialPriceType                  = 'INVPR'
                                                              and _FmltpK_inv.MaterialPriceSubtype               = ''      
  //------------------------------------------------------------

  //  Part 3: Entry for currency column type "O"

  //------------------------------------------------------------

left outer to one join I_MaterialLedgerPrice        as _FmltpO_inv on _FmltpO_inv.CostEstimate                   = it.kalnr 
                                                              and _FmltpO_inv.Material                           = it.matnr
                                                              and _FmltpO_inv.ValuationArea                      = it.bwkey
                                                              and _FmltpO_inv.MatlPrcValdtyStrtFsclYearPerd      <= it.fiscyearper
                                                              and _FmltpO_inv.MatlPrcValdtyEndFsclYearPeriod     >= it.fiscyearper
                                                              and _FmltpO_inv.Ledger                             = it.rldnro
                                                              and _FmltpO_inv.CurrencyRole                       = it.ext_CurtpO
                                                              and _FmltpO_inv.MaterialPriceType                  = 'INVPR'
                                                              and _FmltpO_inv.MaterialPriceSubtype               = ''      
  //------------------------------------------------------------

  //  Part 4: Entry for currency column type "V"

  //------------------------------------------------------------

left outer to one join I_MaterialLedgerPrice        as _FmltpV_inv on _FmltpV_inv.CostEstimate                   = it.kalnr 
                                                              and _FmltpV_inv.Material                           = it.matnr
                                                              and _FmltpV_inv.ValuationArea                      = it.bwkey
                                                              and _FmltpV_inv.MatlPrcValdtyStrtFsclYearPerd      <= it.fiscyearper
                                                              and _FmltpV_inv.MatlPrcValdtyEndFsclYearPeriod     >= it.fiscyearper
                                                              and _FmltpV_inv.Ledger                             = it.rldnrv
                                                              and _FmltpV_inv.CurrencyRole                       = it.ext_CurtpV
                                                              and _FmltpV_inv.MaterialPriceType                  = 'INVPR'
                                                              and _FmltpV_inv.MaterialPriceSubtype               = ''         
                                                              
association [0..1] to I_MatlLedgerValnCrcyRoleName as _MLCurrencyInfo on  _MLCurrencyInfo.CompanyCode                    = it.rbukrs
                                                                      and _MLCurrencyInfo.Ledger                         = it.rldnr
                                                                      and _MLCurrencyInfo.LedgerValnCrcyRoleIsUsedInLogs = 'X'
                                                                                                                                                                                                                                                             
{

      @ObjectModel.foreignKey.association: '_CompanyCode'
  key it.rbukrs                    as CompanyCode,
      @ObjectModel.foreignKey.association: '_Ledger'
  key it.rldnr                     as Ledger,
      @ObjectModel.foreignKey.association: '_GLAccountInChartOfAccounts'
  key it.racct                     as GLAccount,
      @ObjectModel.foreignKey.association: '_FiscalYear'
  key it.gjahr                     as FiscalYear,
      @ObjectModel.foreignKey.association: '_JournalEntry'
  key it.belnr                     as AccountingDocument,
  key it.docln                     as LedgerGLLineItem,
      @ObjectModel.foreignKey.association: '_Material'
  key it.matnr                     as Material,
      @ObjectModel.foreignKey.association: '_ChartOfAccounts'
      it.ktopl                     as ChartOfAccounts,
      it.blart                     as AccountingDocumentType,
      it.bwkey                     as ValuationArea,
      it.bwtar                     as InventoryValuationType,
      it.poper                     as FiscalPeriod,
      it.ryear                     as LedgerFiscalYear,
      it.budat                     as PostingDate,
      it.budat                     as MatlDocLatestPostgDate,
      it.awref                     as ReferenceDocument,
      it.awtyp                     as ReferenceDocumentType,
      it.bldat                     as DocumentDate,
      it.bttype                    as BusinessTransactionType,
      it.prctr                     as ProfitCenter,
      it.rcntr                     as CostCenter,
      it.segment                   as Segment,
      it.usnam                     as AccountingDocCreatedByUser,
      it.timestamp                 as LastChangeDateTime,
      it.fiscyearper               as FiscalYearPeriod,
      it.ebeln                     as PurchasingDocument,
      it.aufnr                     as ProductionOrder,
      it.ktosl                     as TransactionTypeDetermination,
      it.werks                     as Plant,
      @ObjectModel.foreignKey.association: '_ControllingArea'
      it.kokrs                     as ControllingArea,
      it.kalnr                     as CostEstimate,
      it.sobkz                     as InvtryValnSpecialStockType,
      it.mlptyp                    as MaterialLedgerProcessType,
      it.mlcateg                   as MaterialLedgerCategory,
      @ObjectModel.foreignKey.association: '_FiscalYearVariant'
      periv                        as FiscalYearVariant,
      //----------------------------------------

      //KEY FIGURES

      //----------------------------------------

      @Semantics.currencyCode:true
      it.rhcur                     as CompanyCodeCurrency,
      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
      it.hsl                       as AmountInCompanyCodeCurrency,
      @Semantics.currencyCode:true
      it.rkcur                     as GroupCurrency,
      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'GroupCurrency'} }
      it.ksl                       as AmountInGroupCurrency,
      @Semantics.currencyCode:true
      it.rocur                     as AdditionalCurrency1,
      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'AdditionalCurrency1'} }
      it.osl                       as AmountInAdditionalCurrency1,
      @Semantics.currencyCode:true
      it.rvcur                     as AdditionalCurrency2,
      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'AdditionalCurrency2'} }
      it.vsl                       as AmountInAdditionalCurrency2,
      @Semantics.unitOfMeasure:true
      it.rvunit                    as BaseUnit,
      @DefaultAggregation: #SUM
      @Semantics: { quantity : {unitOfMeasure: 'BaseUnit'} }
      cast(it.vmsl as fcml_lbkum)  as InventoryQty,
      //----------------------------------------

      //For Rounding Difference (Just S-price, V-price makes no sense for rounding differences)

      //----------------------------------------

      @DefaultAggregation: #MAX
      @Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'}}
      COALESCE(_FmltpH_inv.MaterialPrice, 0)         as MaterialPriceInCoCodeCurrency,
      @DefaultAggregation: #MAX
      COALESCE(_FmltpH_inv.MaterialPriceUnitQty, 0)  as MatPriceUnitInCoCodeCurrency,
      
      @Semantics: { amount : {currencyCode: 'GroupCurrency'} }
      COALESCE(_FmltpK_inv.MaterialPrice, 0)         as MaterialPriceInGroupCurrency,
      @DefaultAggregation: #MAX
      COALESCE(_FmltpK_inv.MaterialPriceUnitQty, 0)  as MatPriceUnitInGroupCurrency,
      @Semantics: { amount : {currencyCode: 'AdditionalCurrency1'} }
      COALESCE(_FmltpO_inv.MaterialPrice, 0)         as MaterialPriceInAddlCurrency1,
      @DefaultAggregation: #MAX
      COALESCE(_FmltpO_inv.MaterialPriceUnitQty, 0)  as MatPriceUnitInAddlCurrency1,
      @Semantics: { amount : {currencyCode: 'AdditionalCurrency2'} }
      COALESCE(_FmltpV_inv.MaterialPrice, 0)         as MaterialPriceInAddlCurrency2,
      @DefaultAggregation: #MAX
      COALESCE(_FmltpV_inv.MaterialPriceUnitQty, 0)  as MatPriceUnitInAddlCurrency2,
      
      COALESCE(_FmltpH_inv.MaterialPriceControl, '') as MaterialPriceControl,
      it.CurtpK                    as CurrencyRole,
      it.CurtpO                    as AdditionalCurrency1Role,
      it.CurtpV                    as AdditionalCurrency2Role,
      //----------------------------------------

      //Additional fields for V_MaterialStockValueByKeyDate

      //----------------------------------------

      it.xobew                     as IsSupplierStockValuation,
      it.kzbws                     as InventorySpecialStockValnType,
      it.mat_kdauf                 as InventorySpclStkSalesDocument,
      it.mat_kdpos                 as InventorySpclStkSalesDocItm,
      it.mat_pspnr                 as WBSElementInternalID,
      it.mat_lifnr                 as InventorySpecialStockSupplier,
      _MLCurrencyInfo.LedgerValnCrcyRoleIsUsedInLogs as LedgerValnCrcyRoleIsUsedInLogs,
      //----------------------------------------

      //Associations

      //----------------------------------------

      _GLAccountInChartOfAccounts,
      _ChartOfAccounts,
      _Segment,
      _Material,
      _CompanyCode,
      _ProfitCenter,
      _CostCenter,
      _CompanyCodeCurrency,
      _GlobalCurrency,
      _FreeDefinedCurrency1,
      _FreeDefinedCurrency2,
      _CostSourceUnit,
      _ControllingArea,
      _Ledger,
      _FiscalYear,
      _FiscalYearVariant,
      _JournalEntry,
      _MaterialValuation,
      _BusinessTransactionType
}
where ( ( hd.price_sdm_yearper <> '0000000' and  hd.price_sdm_yearper <= _FmltpH_inv.MatlPrcValdtyStrtFsclYearPerd ) or ( tf.MatlPriceDataMigrationStatus = 'F' ) )


/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_MATERIALLEDGERPRICE",
"I_MATLLEDGERVALNCRCYROLENAME",
"P_MATERIALLEDGER_CURTP",
"R_MATLPRICEDATAMIGRATIONSTATUS",
"CKMLCR",
"CKMLHD"
],
"ASSOCIATED":
[
"I_BUSINESSTRANSACTIONTYPE",
"I_CHARTOFACCOUNTS",
"I_COMPANYCODE",
"I_CONTROLLINGAREA",
"I_COSTCENTER",
"I_CURRENCY",
"I_FISCALYEARFORCOMPANYCODE",
"I_FISCALYEARVARIANT",
"I_GLACCOUNTINCHARTOFACCOUNTS",
"I_JOURNALENTRY",
"I_LEDGER",
"I_MATERIAL",
"I_MATERIALVALUATION",
"I_MATLLEDGERVALNCRCYROLENAME",
"I_PROFITCENTER",
"I_SEGMENT",
"I_UNITOFMEASURE"
],
"BASE":
[
"P_MATERIALLEDGER_CURTP"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/