@AbapCatalog: { sqlViewName: 'PFASSETVALCRCYRL',
preserveKey:true}
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.private: true
@VDM.viewType: #COMPOSITE
@ObjectModel: { usageType.serviceQuality: #D,
usageType.sizeCategory: #XXL,
usageType.dataClass: #MIXED }
@ClientHandling.algorithm: #SESSION_VARIABLE
define view P_FxdAstValueWithCurrencyRole
as select from I_FxdAstLineItemWithCrcyRole
inner join P_LedgerCompanyCodeCrcyRoles on I_FxdAstLineItemWithCrcyRole.CompanyCode = P_LedgerCompanyCodeCrcyRoles.CompanyCode
and I_FxdAstLineItemWithCrcyRole.Ledger = P_LedgerCompanyCodeCrcyRoles.Ledger
{
key I_FxdAstLineItemWithCrcyRole.CompanyCode,
key MasterFixedAsset,
key FixedAsset,
key FiscalYear,
key FiscalPeriod,
key SubLedgerAcctLineItemType,
key AssetDepreciationArea,
key I_FxdAstLineItemWithCrcyRole.LedgerGroup,
key I_FxdAstLineItemWithCrcyRole.Ledger,
key AccountingDocument,
key TransactionSubitem,
key DebitCreditCode,
key P_LedgerCompanyCodeCrcyRoles.CurrencyRole,
key AssetAcctTransClassfctn,
LedgerFiscalYear,
FiscalYearVariant,
FiscalYearPeriod,
ChartOfAccounts,
GroupMasterFixedAsset,
GroupFixedAsset,
I_FxdAstLineItemWithCrcyRole.ChartOfDepreciation,
AssetValueDate,
AssetTransactionType,
AssignmentReference,
DocumentItemText,
ReferenceDocumentType,
ReferenceDocument,
LogicalSystem,
ReferenceDocumentContext,
ReversalReferenceDocumentCntxt,
ReversalReferenceDocument,
ReversalTransactionSubitem,
IsReversal,
IsReversed,
BusinessArea,
Segment,
FinancialAccountType,
AccountingDocumentCreationDate,
CreationTime,
PostingDate,
DocumentDate,
AccountingDocCreatedByUser,
IsStatisticalDocument,
ControllingArea,
FinancialManagementArea,
Fund,
GrantID,
FunctionalArea,
BudgetPeriod,
@Semantics.currencyCode:true
case when CompanyCodeCurrencyRole = P_LedgerCompanyCodeCrcyRoles.CurrencyRole then cast (CompanyCodeCurrency as vdm_v_display_currency preserving type)
when GlobalCurrencyRole = P_LedgerCompanyCodeCrcyRoles.CurrencyRole then cast (GlobalCurrency as vdm_v_display_currency preserving type)
when FreeDefinedCurrency1Role = P_LedgerCompanyCodeCrcyRoles.CurrencyRole then cast (FreeDefinedCurrency1 as vdm_v_display_currency preserving type)
when FreeDefinedCurrency2Role = P_LedgerCompanyCodeCrcyRoles.CurrencyRole then cast (FreeDefinedCurrency2 as vdm_v_display_currency preserving type)
when FreeDefinedCurrency3Role = P_LedgerCompanyCodeCrcyRoles.CurrencyRole then cast (FreeDefinedCurrency3 as vdm_v_display_currency preserving type)
when FreeDefinedCurrency4Role = P_LedgerCompanyCodeCrcyRoles.CurrencyRole then cast( FreeDefinedCurrency4 as vdm_v_display_currency preserving type)
when FreeDefinedCurrency5Role = P_LedgerCompanyCodeCrcyRoles.CurrencyRole then cast( FreeDefinedCurrency5 as vdm_v_display_currency preserving type)
when FreeDefinedCurrency6Role = P_LedgerCompanyCodeCrcyRoles.CurrencyRole then cast (FreeDefinedCurrency6 as vdm_v_display_currency preserving type)
when FreeDefinedCurrency7Role = P_LedgerCompanyCodeCrcyRoles.CurrencyRole then cast( FreeDefinedCurrency7 as vdm_v_display_currency preserving type)
when FreeDefinedCurrency8Role = P_LedgerCompanyCodeCrcyRoles.CurrencyRole then cast(FreeDefinedCurrency8 as vdm_v_display_currency preserving type)
else cast('' as vdm_v_display_currency)
end as DisplayCurrency,
@DefaultAggregation:#SUM
@Semantics.amount.currencyCode: 'DisplayCurrency'
case when CompanyCodeCurrencyRole = P_LedgerCompanyCodeCrcyRoles.CurrencyRole then cast(AmountInCompanyCodeCurrency as farp_amount_display_crcy)
when GlobalCurrencyRole = P_LedgerCompanyCodeCrcyRoles.CurrencyRole then cast(AmountInGlobalCurrency as farp_amount_display_crcy)
when FreeDefinedCurrency1Role = P_LedgerCompanyCodeCrcyRoles.CurrencyRole then cast(AmountInFreeDefinedCurrency1 as farp_amount_display_crcy)
when FreeDefinedCurrency2Role = P_LedgerCompanyCodeCrcyRoles.CurrencyRole then cast(AmountInFreeDefinedCurrency2 as farp_amount_display_crcy)
when FreeDefinedCurrency3Role = P_LedgerCompanyCodeCrcyRoles.CurrencyRole then cast(AmountInFreeDefinedCurrency3 as farp_amount_display_crcy)
when FreeDefinedCurrency4Role = P_LedgerCompanyCodeCrcyRoles.CurrencyRole then cast(AmountInFreeDefinedCurrency4 as farp_amount_display_crcy)
when FreeDefinedCurrency5Role = P_LedgerCompanyCodeCrcyRoles.CurrencyRole then cast(AmountInFreeDefinedCurrency5 as farp_amount_display_crcy)
when FreeDefinedCurrency6Role = P_LedgerCompanyCodeCrcyRoles.CurrencyRole then cast(AmountInFreeDefinedCurrency6 as farp_amount_display_crcy)
when FreeDefinedCurrency7Role = P_LedgerCompanyCodeCrcyRoles.CurrencyRole then cast(AmountInFreeDefinedCurrency7 as farp_amount_display_crcy)
when FreeDefinedCurrency8Role = P_LedgerCompanyCodeCrcyRoles.CurrencyRole then cast(AmountInFreeDefinedCurrency8 as farp_amount_display_crcy)
else cast('0' as farp_amount_display_crcy)
end as AmountInDisplayCurrency,
cast ('' as boolean) as IsPlanningDocument
}
//where
// IsStatisticalDocument = ''
// or(
// IsStatisticalDocument = 'X'
// and
// //Restrict selection to avoid duplicates
// AssetAcctTransClassfctn <> '46'
// and AssetAcctTransClassfctn <> '61'
// and AssetAcctTransClassfctn <> '65'
// and AssetAcctTransClassfctn <> '66'
// or(
// //Additionaly for the transaction list of asset accounting
// SubLedgerAcctLineItemType between '07900' and '07906'
// or SubLedgerAcctLineItemType = '07940'
// )
// )
union all select from I_FxdAstPlanValueWithCrcyRole
inner join P_LedgerCompanyCodeCrcyRoles on I_FxdAstPlanValueWithCrcyRole.CompanyCode = P_LedgerCompanyCodeCrcyRoles.CompanyCode
and I_FxdAstPlanValueWithCrcyRole.Ledger = P_LedgerCompanyCodeCrcyRoles.Ledger
{
key I_FxdAstPlanValueWithCrcyRole.CompanyCode,
key MasterFixedAsset,
key FixedAsset,
key FiscalYear,
key FiscalPeriod,
key SubLedgerAcctLineItemType,
key AssetDepreciationArea,
key I_FxdAstPlanValueWithCrcyRole.LedgerGroup,
key I_FxdAstPlanValueWithCrcyRole.Ledger,
key cast('' as belnr_d) as AccountingDocument,
key cast('' as fins_subta) as TransactionSubitem,
key cast('' as shkzg) as DebitCreditCode,
key P_LedgerCompanyCodeCrcyRoles.CurrencyRole,
key AssetAcctTransClassfctn,
cast('0000' as gjahr_pos) as LedgerFiscalYear,
cast('' as periv ) as FiscalYearVariant,
cast('0000000' as jahrper) as FiscalYearPeriod,
I_FxdAstPlanValueWithCrcyRole._CompanyCode.ChartOfAccounts,
GroupMasterFixedAsset,
GroupFixedAsset,
I_FxdAstPlanValueWithCrcyRole.ChartOfDepreciation,
cast('00000000' as bzdat) as AssetValueDate,
cast('' as bwasl) as AssetTransactionType,
cast('' as dzuonr) as AssignmentReference,
cast('' as sgtxt) as DocumentItemText,
cast('' as fis_awtyp) as ReferenceDocumentType,
cast('' as awref) as ReferenceDocument,
cast('' as awsys) as LogicalSystem,
cast('' as aworg) as ReferenceDocumentContext,
cast('' as aworg_rev) as ReversalReferenceDocumentCntxt,
cast('' as fis_stblg) as ReversalReferenceDocument,
cast('' as fins_subta_rev) as ReversalTransactionSubitem,
cast('' as fins_xreversing) as IsReversal,
cast('' as fins_xreversed) as IsReversed,
cast('' as gsber) as BusinessArea,
cast('' as fb_segment) as Segment,
FinancialAccountType,
cast('00000000' as fis_cpudt) as AccountingDocumentCreationDate,
cast('' as fis_cputm) as CreationTime,
cast('00000000' as budat) as PostingDate,
cast('00000000' as bldat) as DocumentDate,
cast('' as fis_usnam) as AccountingDocCreatedByUser,
cast('' as boolean preserving type) as IsStatisticalDocument,
I_FxdAstPlanValueWithCrcyRole._CompanyCode.ControllingArea,
cast('' as fikrs) as FinancialManagementArea,
cast('' as bp_geber) as Fund,
cast('' as gm_grant_nbr) as GrantID,
cast('' as fm_farea) as FunctionalArea,
cast('' as fm_budget_period) as BudgetPeriod,
@Semantics.currencyCode:true
case when CompanyCodeCurrencyRole = P_LedgerCompanyCodeCrcyRoles.CurrencyRole then cast (CompanyCodeCurrency as vdm_v_display_currency preserving type)
when GlobalCurrencyRole = P_LedgerCompanyCodeCrcyRoles.CurrencyRole then cast (GlobalCurrency as vdm_v_display_currency preserving type)
when FreeDefinedCurrency1Role = P_LedgerCompanyCodeCrcyRoles.CurrencyRole then cast (FreeDefinedCurrency1 as vdm_v_display_currency preserving type)
when FreeDefinedCurrency2Role = P_LedgerCompanyCodeCrcyRoles.CurrencyRole then cast (FreeDefinedCurrency2 as vdm_v_display_currency preserving type)
when FreeDefinedCurrency3Role = P_LedgerCompanyCodeCrcyRoles.CurrencyRole then cast (FreeDefinedCurrency3 as vdm_v_display_currency preserving type)
when FreeDefinedCurrency4Role = P_LedgerCompanyCodeCrcyRoles.CurrencyRole then cast( FreeDefinedCurrency4 as vdm_v_display_currency preserving type)
when FreeDefinedCurrency5Role = P_LedgerCompanyCodeCrcyRoles.CurrencyRole then cast( FreeDefinedCurrency5 as vdm_v_display_currency preserving type)
when FreeDefinedCurrency6Role = P_LedgerCompanyCodeCrcyRoles.CurrencyRole then cast (FreeDefinedCurrency6 as vdm_v_display_currency preserving type)
when FreeDefinedCurrency7Role = P_LedgerCompanyCodeCrcyRoles.CurrencyRole then cast( FreeDefinedCurrency7 as vdm_v_display_currency preserving type)
when FreeDefinedCurrency8Role = P_LedgerCompanyCodeCrcyRoles.CurrencyRole then cast(FreeDefinedCurrency8 as vdm_v_display_currency preserving type)
else cast('' as vdm_v_display_currency)
end as DisplayCurrency,
@DefaultAggregation:#SUM
@Semantics.amount.currencyCode: 'DisplayCurrency'
case when CompanyCodeCurrencyRole = P_LedgerCompanyCodeCrcyRoles.CurrencyRole then cast(AmountInCompanyCodeCurrency as farp_amount_display_crcy)
when GlobalCurrencyRole = P_LedgerCompanyCodeCrcyRoles.CurrencyRole then cast(AmountInGlobalCurrency as farp_amount_display_crcy)
when FreeDefinedCurrency1Role = P_LedgerCompanyCodeCrcyRoles.CurrencyRole then cast(AmountInFreeDefinedCurrency1 as farp_amount_display_crcy)
when FreeDefinedCurrency2Role = P_LedgerCompanyCodeCrcyRoles.CurrencyRole then cast(AmountInFreeDefinedCurrency2 as farp_amount_display_crcy)
when FreeDefinedCurrency3Role = P_LedgerCompanyCodeCrcyRoles.CurrencyRole then cast(AmountInFreeDefinedCurrency3 as farp_amount_display_crcy)
when FreeDefinedCurrency4Role = P_LedgerCompanyCodeCrcyRoles.CurrencyRole then cast(AmountInFreeDefinedCurrency4 as farp_amount_display_crcy)
when FreeDefinedCurrency5Role = P_LedgerCompanyCodeCrcyRoles.CurrencyRole then cast(AmountInFreeDefinedCurrency5 as farp_amount_display_crcy)
when FreeDefinedCurrency6Role = P_LedgerCompanyCodeCrcyRoles.CurrencyRole then cast(AmountInFreeDefinedCurrency6 as farp_amount_display_crcy)
when FreeDefinedCurrency7Role = P_LedgerCompanyCodeCrcyRoles.CurrencyRole then cast(AmountInFreeDefinedCurrency7 as farp_amount_display_crcy)
when FreeDefinedCurrency8Role = P_LedgerCompanyCodeCrcyRoles.CurrencyRole then cast(AmountInFreeDefinedCurrency8 as farp_amount_display_crcy)
else cast('0' as farp_amount_display_crcy)
end as AmountInDisplayCurrency,
cast ('X' as boolean) as IsPlanningDocument
}
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_COMPANYCODE",
"I_FXDASTLINEITEMWITHCRCYROLE",
"I_FXDASTPLANVALUEWITHCRCYROLE",
"P_LEDGERCOMPANYCODECRCYROLES"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/