P_CN_CADEAssetRetirement

@AbapCatalog.preserveKey: true
@AbapCatalog.sqlViewName: 'PCADEASSETRETIRE'
@AbapCatalog.compiler.compareFilter: true
@ClientHandling.algorithm: #SESSION_VARIABLE
@VDM.viewType: #CONSUMPTION
@VDM.private: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view P_CN_CADEAssetRetirement with parameters
    @EndUserText.label: 'Fiscal Year'
    P_FiscalYear   : fis_gjahr,
    @EndUserText.label: 'To Fiscal Period'
    P_FromFiscalPeriod   : fins_fiscalperiod,
    @EndUserText.label: 'To Fiscal Period'
    P_ToFiscalPeriod   : fins_fiscalperiod
as select from I_FixedAssetTransaction ( P_KeyDate: $session.system_date, P_AssetAccountingKeyFigureSet: 'TRANS_ALL' ) as JournalEntry
inner join I_CN_CADEGeneralInformation as DepreciationArea on DepreciationArea.AssetDepreciationArea = JournalEntry.AssetDepreciationArea
                                                           and DepreciationArea.CompanyCode = JournalEntry.CompanyCode
                                                           and DepreciationArea.Ledger = JournalEntry.Ledger
left outer to one join I_LedgerCompanyCodeCrcyRoles as _LedgerCompanyCodeCrcyRole on _LedgerCompanyCodeCrcyRole.CompanyCode = JournalEntry.CompanyCode
                                                                                 and _LedgerCompanyCodeCrcyRole.Ledger = JournalEntry.Ledger
association [0..1] to I_GLAccountLineItem as _Quantity on _Quantity.CompanyCode = JournalEntry.CompanyCode
                                                      and _Quantity.Ledger = JournalEntry.Ledger
                                                      and _Quantity.AccountingDocument = JournalEntry.AccountingDocument
                                                      and _Quantity.ReferenceDocument = JournalEntry.ReferenceDocument
                                                      and ( _Quantity.SubLedgerAcctLineItemType = '07040' or _Quantity.SubLedgerAcctLineItemType = '07000')
                                                      and _Quantity.MasterFixedAsset = JournalEntry.MasterFixedAsset
                                                      and _Quantity.FixedAsset = JournalEntry.FixedAsset
                                                      and _Quantity.AssetDepreciationArea = JournalEntry.AssetDepreciationArea
association [0..1] to I_AssetValuationForLedger as _ScrapAmount on _ScrapAmount.CompanyCode = JournalEntry.CompanyCode
                                                          and _ScrapAmount.MasterFixedAsset = JournalEntry.MasterFixedAsset
                                                          and _ScrapAmount.FixedAsset = JournalEntry.FixedAsset
                                                          and _ScrapAmount.AssetRealDepreciationArea = JournalEntry.AssetDepreciationArea
                                                          and _ScrapAmount.ValidityStartDate <= JournalEntry.PostingDate
                                                          and _ScrapAmount.ValidityEndDate >= JournalEntry.PostingDate
{
    key JournalEntry.CompanyCode,
    key DepreciationArea.CN_CADENatlStdRelVersion,
    key MasterFixedAsset,
    key FixedAsset,
    key FiscalYear,
    key FiscalPeriod,
    key JournalEntry.AssetDepreciationArea,
    key LedgerGroup,
    key JournalEntry.Ledger,
    key JournalEntry.ReferenceDocument,
        FixedAssetExternalID,
        case when _FixedAsset.AssetSerialNumber = ''
          then _FixedAsset.Inventory
          else _FixedAsset.AssetSerialNumber
        end as AssetSerialNumber,
        _FixedAsset.Inventory,
        _FixedAsset.FixedAssetDescription,
        _FixedAsset.AssetClass,
        ChartOfDepreciation,
        PostingDate,
        AssetTransactionVariant,
        _ScrapAmount.AcqnProdnCostScrapPercent,
      //  @ObjectModel.foreignKey.association: '_BaseUnit'

        @Semantics.unitOfMeasure:true
        _Quantity.BaseUnit,
        
        max( AssetValueDate ) as AssetValueDate,
        @Semantics: { quantity : {unitOfMeasure: 'BaseUnit'} }
        max( _Quantity.Quantity ) as Quantity,
        
        @Semantics.currencyCode:true
        DisplayCurrency,
        
        @DefaultAggregation:#SUM
        @Semantics.amount.currencyCode: 'DisplayCurrency'
        sum( AcqnProdnAmtInDspCrcy ) as AcqnProdnAmtInDspCrcy,
        
        @DefaultAggregation:#SUM
        @Semantics.amount.currencyCode: 'DisplayCurrency'
        sum( case when JournalEntry.SubLedgerAcctLineItemType = '07003' or JournalEntry.SubLedgerAcctLineItemType = '07043' or JournalEntry.SubLedgerAcctLineItemType = '07203' //revaluation amount

               then AcqnProdnAmtInDspCrcy
               else cast(0 as fis_valueadjamt)
             end ) as ValueAdjustmentAmtInDspCrcy,
        @DefaultAggregation:#SUM
        @Semantics.amount.currencyCode: 'DisplayCurrency'
        sum(RetirementRevenueAmtInDspCrcy) as RetirementRevenueAmtInDspCrcy,
        @DefaultAggregation:#SUM
        @Semantics.amount.currencyCode: 'DisplayCurrency'
        sum(RetirementCostsAmtInDspCrcy) as RetirementCostsAmtInDspCrcy,
        @DefaultAggregation:#SUM
        @Semantics.amount.currencyCode: 'DisplayCurrency'
        sum(RetirementDeprAmtInDspCrcy) as RetirementDeprAmtInDspCrcy,
        @DefaultAggregation:#SUM
        @Semantics.amount.currencyCode: 'DisplayCurrency'
        sum(RetirementAmountInDspCrcy) as RetirementAmountInDspCrcy,
        @DefaultAggregation:#SUM
        @Semantics.amount.currencyCode: 'DisplayCurrency'
        sum(OrdinaryDeprAmtInDspCrcy) as OrdinaryDeprAmtInDspCrcy,
        @DefaultAggregation:#SUM
        @Semantics.amount.currencyCode: 'DisplayCurrency'
        sum(SpecialDeprAmtInDspCrcy) as SpecialDeprAmtInDspCrcy,
        @DefaultAggregation:#SUM
        @Semantics.amount.currencyCode: 'DisplayCurrency'
        sum(UnplannedDeprAmtInDspCrcy) as UnplannedDeprAmtInDspCrcy,
        max(JournalEntry.AssetTransactionType) as AssetTransactionType,
        max(JournalEntry.DocumentItemText) as DocumentItemText
}
where
CurrencyRole = _LedgerCompanyCodeCrcyRole.CompanyCodeCurrencyRole
and AssetTransactionVariant = '2'
and FiscalYear = :P_FiscalYear
and FiscalPeriod >= :P_FromFiscalPeriod
and FiscalPeriod <= :P_ToFiscalPeriod
group by 
JournalEntry.CompanyCode,
DepreciationArea.CN_CADENatlStdRelVersion,
MasterFixedAsset,
FixedAsset,
FiscalYear,
FiscalPeriod,
JournalEntry.AssetDepreciationArea,
LedgerGroup,
JournalEntry.Ledger,
JournalEntry.ReferenceDocument,
FixedAssetExternalID,
_FixedAsset.AssetSerialNumber,
_FixedAsset.Inventory,
_FixedAsset.FixedAssetDescription,
_FixedAsset.AssetClass,
_FixedAsset.OriglAcqnAmtInCoCodeCrcy,
_FixedAsset.Quantity,
ChartOfDepreciation,
PostingDate,
AssetTransactionVariant,
DisplayCurrency,
_ScrapAmount.AcqnProdnCostScrapPercent,
_Quantity.BaseUnit

/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_ASSETVALUATIONFORLEDGER",
"I_CN_CADEGENERALINFORMATION",
"I_FIXEDASSET",
"I_FIXEDASSETTRANSACTION",
"I_GLACCOUNTLINEITEM",
"I_LEDGERCOMPANYCODECRCYROLES"
],
"ASSOCIATED":
[
"I_ASSETVALUATIONFORLEDGER",
"I_GLACCOUNTLINEITEM"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/