P_SALESANALYTICS
P_SALESANALYTICS is a CDS View in S/4HANA. 2 CDS views read from this table.
CDS Views using this table (2)
| View | Type | Join | VDM | Description |
|---|---|---|---|---|
| I_SalesAnalyticsCube | view | from | COMPOSITE | Analytics - Sales Volume and Open Sales |
| I_SalesAnalyticsCube_1 | view | from | COMPOSITE | Sales Volume and Open Sales - Cube |
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #XL
@VDM.viewType: #COMPOSITE
@AccessControl.authorizationCheck:#NOT_REQUIRED
@AbapCatalog.sqlViewName: 'PSDSLSANALYT'
@VDM.private: true
define view P_SalesAnalytics
with parameters
P_ExchangeRateType : kurst,
P_DisplayCurrency : vdm_v_display_currency
as
select from I_SalesDocumentItemAnalytics //Core Layer Element: Sales Document Item
{
//key
key SalesDocument,
key SalesDocumentItem,
key cast('' as etenr ) as ScheduleLine,
key cast('' as fplnr) as BillingPlan,
key cast('' as fpltr) as BillingPlanItem,
key cast('' as vbeln_vl) as DeliveryDocument,
key cast('' as posnr_vl) as DeliveryDocumentItem,
key cast('' as vbeln_vf) as BillingDocument,
key cast('' as posnr_vf) as BillingDocumentItem,
//Dimensions
//Customer
@ObjectModel.foreignKey.association: '_SoldToParty'
SoldToParty,
cast(_SalesDocument._SoldToParty.CustomerName as vdm_sold_to_name) as SoldToPartyName,
--SoldToPartyName,
CustomerGroup,
--AT 29.04.2019: AdditionalCustomerGroup1..5 added
@ObjectModel.foreignKey.association: '_AdditionalCustomerGroup1'
AdditionalCustomerGroup1,
_AdditionalCustomerGroup1,
@ObjectModel.foreignKey.association: '_AdditionalCustomerGroup2'
AdditionalCustomerGroup2,
_AdditionalCustomerGroup2,
@ObjectModel.foreignKey.association: '_AdditionalCustomerGroup3'
AdditionalCustomerGroup3,
_AdditionalCustomerGroup3,
@ObjectModel.foreignKey.association: '_AdditionalCustomerGroup4'
AdditionalCustomerGroup4,
_AdditionalCustomerGroup4,
@ObjectModel.foreignKey.association: '_AdditionalCustomerGroup5'
AdditionalCustomerGroup5,
_AdditionalCustomerGroup5,
// ----- Category -----
@ObjectModel.foreignKey.association: '_SDDocumentCategory'
SDDocumentCategory,
cast('A' as sd_doc_object) as SDDocumentObject, -- AT23.02.2018: for extensibility
//Organization
@ObjectModel.foreignKey.association: '_SalesOrganization'
SalesOrganization,
@ObjectModel.foreignKey.association: '_DistributionChannel'
DistributionChannel,
@ObjectModel.foreignKey.association: '_Division'
OrganizationDivision as Division,
//Team View
@ObjectModel.foreignKey.association: '_SalesOffice'
SalesOffice,
@ObjectModel.foreignKey.association: '_SalesGroup'
SalesGroup,
//Geographics
@ObjectModel.foreignKey.association: '_SalesDistrict'
SalesDistrict,
//Product
--AT*2 17.2.2020 add Product
@ObjectModel.foreignKey.association: '_Material'
@VDM.lifecycle: { status: #DEPRECATED, successor: 'Product' }
Material,
@Analytics.internalName: #LOCAL
@ObjectModel.foreignKey.association: '_Product'
Product,
@ObjectModel.foreignKey.association: '_OriginallyRequestedMaterial'
OriginallyRequestedMaterial,
InternationalArticleNumber,
ProductHierarchyNode,
@ObjectModel.foreignKey.association: '_MaterialGroup'
@VDM.lifecycle: { status: #DEPRECATED, successor: 'ProductGroup' }
MaterialGroup,
@Analytics.internalName: #LOCAL
@ObjectModel.foreignKey.association: '_ProductGroup'
ProductGroup,
--AT 29.04.2019: AdditionalMaterialGroup1..5 added
@ObjectModel.foreignKey.association: '_AdditionalMaterialGroup1'
AdditionalMaterialGroup1,
_AdditionalMaterialGroup1,
@ObjectModel.foreignKey.association: '_AdditionalMaterialGroup2'
AdditionalMaterialGroup2,
_AdditionalMaterialGroup2,
@ObjectModel.foreignKey.association: '_AdditionalMaterialGroup3'
AdditionalMaterialGroup3,
_AdditionalMaterialGroup3,
@ObjectModel.foreignKey.association: '_AdditionalMaterialGroup4'
AdditionalMaterialGroup4,
_AdditionalMaterialGroup4,
@ObjectModel.foreignKey.association: '_AdditionalMaterialGroup5'
AdditionalMaterialGroup5,
_AdditionalMaterialGroup5,
ProfitCenter,
//Time Period
-- see I_SalesAnalyticsCube
//Measures
//Measures Sales Document Item
OpnSOForOrdReltdInvcsNetAmount,
cast ( currency_conversion(
amount => OpnSOForOrdReltdInvcsNetAmount,
source_currency => TransactionCurrency,
target_currency => :P_DisplayCurrency,
exchange_rate_date => BillingDocumentDate, -- <---
exchange_rate_type => :P_ExchangeRateType,
error_handling => 'FAIL_ON_ERROR',
round => #CDSBoolean.true,
decimal_shift => #CDSBoolean.true,
decimal_shift_back => #CDSBoolean.true
) as opn_ords_for_ordrelinv_amt_idc) as OpnSOForOrdReltdInvcsNetAmtDC,
OpnSlsOrdsForOrdReltdInvcsQty,
OpnRetsForOrdReltdInvcsNetAmt,
cast ( currency_conversion(
amount => OpnRetsForOrdReltdInvcsNetAmt,
source_currency => TransactionCurrency,
target_currency => :P_DisplayCurrency,
exchange_rate_date => BillingDocumentDate, -- <---
exchange_rate_type => :P_ExchangeRateType,
error_handling => 'FAIL_ON_ERROR',
round => #CDSBoolean.true,
decimal_shift => #CDSBoolean.true,
decimal_shift_back => #CDSBoolean.true
) as opn_rets_for_invc_net_amt_idc) as OpnRetsOrdReltdInvcsNetAmtInDC,
OpnRetsForOrdReltdInvcsQty,
//Measures Schedule Line
cast (0 as opn_ord_for_del_amt_itc) as OpenSlsOrdersForDelivNetAmount,
cast (0 as opn_ord_for_del_amt_idc) as OpnSlsOrdsForDelivAmtInDspCrcy,
cast (0 as opn_ord_for_del_qty) as OpnSlsOrdrsForDelivQuantity,
//Measures for Billing Plan Due Date
cast (0 as opn_ord_for_iplan_amt_itc) as OpnSlsOrdsForInvcPlansNetAmt,
cast (0 as opn_ord_for_iplan_amt_idc) as OpnSlsOrdsForInvcPlansNetAmtDC,
//Measures Delivery Document Item
cast (0 as opn_dlv_for_inv_net_amt) as OpnOutbDelivsForInvcNetAmt,
cast (0 as opn_shppd_dlv_for_inv_net_shor) as ShpdNotInvcdDelivsNetAmount, --AT 25.4.2018: new for CE1808
cast (0 as opn_shppd_dlv_for_inv_net_amt) as ShpdNotInvcdDelivsNetAmount_2,
cast (0 as opn_dlv_for_inv_amt_idc) as OpnOutbDelivsForInvcNetAmtInDC,
cast (0 as opn_shppd_dlv_for_inv_amt_idc) as ShpdNotInvcdDelivsNetAmtInDC, --AT 25.4.2018: new for CE1808
cast (0 as opn_dlv_for_inv_qty) as OpnOutbDelivsForInvcQty,
cast (0 as opn_shppd_dlv_for_inv_qty) as ShpdNotInvcdDelivsQuantity, --AT 25.4.2018: new for CE1808
//Measures Billing Document Item
cast (0 as sls_vlm_net_amt_short) as SalesVolumeNetAmount,
cast (0 as sls_vlm_net_amt) as SalesVolumeNetAmount_2,
cast (0 as mc_umnetwr) as SlsVolumeNetAmtInDspCrcy,
// cast ( 0 as prelim_sls_vlm_net_amt) as PrelimBillgSlsVolNetAmt, --AT 12.11.2018 added for CE1902 --AT9.4.2019 back again
// cast ( 0 as prelim_sls_vlm_net_amt_idc) as PrelimBillgSlsVolNetAmtInDC, --AT 12.11.2018 added for CE1902 --AT9.4.2019 back again
cast (0 as mc_ummenge) as SalesVolumeQuantity,
cast (0 as prf_marg_net_amt_short) as SalesProfitMarginNetAmount,
cast (0 as prf_marg_net_amt) as SalesProfitMarginNetAmount_2,
cast (0 as prf_marg_net_amt_idc) as SlsProfitMargNetAmtInDspCrcy,
--SalesProfitMargin, Calculated on AE level
cast (0 as credit_memo_net_amt_short ) as CustomerCreditMemoNetAmount,
cast (0 as credit_memo_net_amt ) as CustomerCreditMemoNetAmount_2,
cast (0 as mc_gunetwr) as CustCrdtMemoNetAmtInDspCrcy,
cast (0 as mc_gumenge) as CustCreditMemoQuantity,
cast ( 0 as cm_prf_marg_net_amt) as CustCrdtMemoPrftMargNetAmt, -- AT 13.04.2018: added for CE1808
cast ( 0 as cm_prf_marg_net_amt_idc) as CustCrdtMemoPrftMargNetAmtInDC, -- AT 13.04.2018: added for CE1808
--AT 29.04.2019: SubTotal1..6 & SubTotal1..6InDC added
cast (0 as subtotalamount1) as Subtotal1Amount,
cast (0 as subtotalamount1_idc) as Subtotal1AmountInDC,
cast (0 as subtotalamount2) as Subtotal2Amount,
cast (0 as subtotalamount2_idc) as Subtotal2AmountInDC,
cast (0 as subtotalamount3) as Subtotal3Amount,
cast (0 as subtotalamount3_idc) as Subtotal3AmountInDC,
cast (0 as subtotalamount4) as Subtotal4Amount,
cast (0 as subtotalamount4_idc) as Subtotal4AmountInDC,
cast (0 as subtotalamount5) as Subtotal5Amount,
cast (0 as subtotalamount5_idc) as Subtotal5AmountInDC,
cast (0 as subtotalamount6) as Subtotal6Amount,
cast (0 as subtotalamount6_idc) as Subtotal6AmountInDC,
TransactionCurrency,
StatisticsCurrency,
BaseUnit,
// Misc (for DCL, I_CalenderDa --Area SalesDocument
SalesDocumentType, -- DCL Check for SalesDocument
cast('' as vstel) as ShippingPoint, -- DCL Check for DeliveryDocument --AT8.7.2019 dataelement VSTEL instead of VSART
cast('' as fkart) as BillingDocumentType, -- DCL Check for BillingDocument
--GoodsIssueDate as CalendarDate, -- for joining I_calendarDate & CurrencyConversion
--case when GoodsIssueDate = '00000000' then
-- DeliveryDate else GoodsIssueDate end as CalendarDate , -- for joining I_CalendarDate later (same date as for CurrencyConversion)
BillingDocumentDate as CalendarDate,
CreationDate,
cast ( '' as edatu) as DeliveryDate, --AT14.10.2021 CE2202 expose DeliveryDate from I_SLAnalytics (CFD Jira SDANALYTICS01-873)
//Associations
_TransactionCurrency,
_StatisticsCurrency,
_BaseUnit,
_DistributionChannel,
_OrganizationDivision as _Division,
@VDM.lifecycle: { status: #DEPRECATED, successor: '_Product' }
_Material,
_Product,
@VDM.lifecycle: { status: #DEPRECATED, successor: '_ProductGroup' }
_MaterialGroup,
_ProductGroup,
_OriginallyRequestedMaterial,
_SalesDistrict,
_SalesOffice,
_SalesGroup,
_SalesOrganization,
_SoldToParty,
_CustomerGroup,
_SDDocumentCategory
}
where
--IsReturnsItem = '' and --AT29.07.2021 To get values > 0 for OpnRetsOrdReltdInvcsNetAmt/Qty ! Hotfix CE2108HF3, OP2021,(of course) Infinity
--( ItemIsBillingRelevant = 'B' or ItemIsBillingRelevant = 'C' or ItemIsBillingRelevant = 'F' or ItemIsBillingRelevant = 'G' ) and AT07.11.2017 Check on OrderRelatedBillingStatus is sufficient
( OrderRelatedBillingStatus = 'A' or OrderRelatedBillingStatus = 'B' )
or
( ItemIsBillingRelevant = 'F' and OrderRelatedBillingStatus <> 'C' and SDDocumentRejectionStatus <> 'C' ) --AT15.11.2021 for third party scenario (fkrel = F)
union all
select from I_SlsDocSchedLineAnalytics //Core Layer Element: Schedule Line
{
//key
key SalesDocument,
key SalesDocumentItem,
key ScheduleLine,
key cast('' as fplnr) as BillingPlan,
key cast('' as fpltr) as BillingPlanItem,
key cast('' as vbeln_vl) as DeliveryDocument,
key cast('' as posnr_vl) as DeliveryDocumentItem,
key cast('' as vbeln_vf) as BillingDocument,
key cast('' as posnr_vf) as BillingDocumentItem,
//Dimensions
//Customer
@ObjectModel.foreignKey.association: '_SoldToParty'
SoldToParty,
SoldToPartyName,
CustomerGroup,
--AT 29.04.2019: AdditionalCustomerGroup1..5 added
@ObjectModel.foreignKey.association: '_AdditionalCustomerGroup1'
AdditionalCustomerGroup1,
_AdditionalCustomerGroup1,
@ObjectModel.foreignKey.association: '_AdditionalCustomerGroup2'
AdditionalCustomerGroup2,
_AdditionalCustomerGroup2,
@ObjectModel.foreignKey.association: '_AdditionalCustomerGroup3'
AdditionalCustomerGroup3,
_AdditionalCustomerGroup3,
@ObjectModel.foreignKey.association: '_AdditionalCustomerGroup4'
AdditionalCustomerGroup4,
_AdditionalCustomerGroup4,
@ObjectModel.foreignKey.association: '_AdditionalCustomerGroup5'
AdditionalCustomerGroup5,
_AdditionalCustomerGroup5,
// ----- Category -----
@ObjectModel.foreignKey.association: '_SDDocumentCategory'
SDDocumentCategory,
cast('A' as sd_doc_object) as SDDocumentObject, -- AT23.02.2018: for extensibility
//Organization
@ObjectModel.foreignKey.association: '_SalesOrganization'
SalesOrganization,
@ObjectModel.foreignKey.association: '_DistributionChannel'
DistributionChannel,
@ObjectModel.foreignKey.association: '_Division'
OrganizationDivision as Division,
//Team View
@ObjectModel.foreignKey.association: '_SalesOffice'
SalesOffice,
@ObjectModel.foreignKey.association: '_SalesGroup'
SalesGroup,
//Geographics
@ObjectModel.foreignKey.association: '_SalesDistrict'
SalesDistrict,
//Product
--AT*2 17.2.2020 add Product
@ObjectModel.foreignKey.association: '_Material'
@VDM.lifecycle: { status: #DEPRECATED, successor: 'Product' }
Material,
@Analytics.internalName: #LOCAL
@ObjectModel.foreignKey.association: '_Product'
Product,
@ObjectModel.foreignKey.association: '_OriginallyRequestedMaterial'
OriginallyRequestedMaterial,
InternationalArticleNumber,
ProductHierarchyNode,
@ObjectModel.foreignKey.association: '_MaterialGroup'
@VDM.lifecycle: { status: #DEPRECATED, successor: 'ProductGroup' }
MaterialGroup,
@Analytics.internalName: #LOCAL
@ObjectModel.foreignKey.association: '_ProductGroup'
ProductGroup,
--AT 29.04.2019: AdditionalMaterialGroup1..5 added
@ObjectModel.foreignKey.association: '_AdditionalMaterialGroup1'
AdditionalMaterialGroup1,
_AdditionalMaterialGroup1,
@ObjectModel.foreignKey.association: '_AdditionalMaterialGroup2'
AdditionalMaterialGroup2,
_AdditionalMaterialGroup2,
@ObjectModel.foreignKey.association: '_AdditionalMaterialGroup3'
AdditionalMaterialGroup3,
_AdditionalMaterialGroup3,
@ObjectModel.foreignKey.association: '_AdditionalMaterialGroup4'
AdditionalMaterialGroup4,
_AdditionalMaterialGroup4,
@ObjectModel.foreignKey.association: '_AdditionalMaterialGroup5'
AdditionalMaterialGroup5,
_AdditionalMaterialGroup5,
ProfitCenter,
//Time Period
-- see I_SalesAnalyticsCube
//Measures
//Measures Sales Document Item
cast ( 0 as opn_ords_for_ordrelinv_netamt) as OpnSOForOrdReltdInvcsNetAmount,
cast ( 0 as opn_ords_for_ordrelinv_amt_idc) as OpnSOForOrdReltdInvcsNetAmtDC,
cast ( 0 as opn_ords_for_ordrel_invcs_qty) as OpnSlsOrdsForOrdReltdInvcsQty,
cast ( 0 as opn_rets_for_invc_net_amt ) as OpnRetsForOrdReltdInvcsNetAmt,
cast ( 0 as opn_rets_for_invc_net_amt_idc ) as OpnRetsOrdReltdInvcsNetAmtInDC,
cast ( 0 as opn_rets_for_invc_net_qty ) as OpnRetsForOrdReltdInvcsQty,
//Measures Schedule Line
OpenSlsOrdersForDelivNetAmount,
case when GoodsIssueDate = '00000000'
then
cast ( currency_conversion(
amount => OpenSlsOrdersForDelivNetAmount,
source_currency => TransactionCurrency,
target_currency => :P_DisplayCurrency,
exchange_rate_date => DeliveryDate, -- <---
exchange_rate_type => :P_ExchangeRateType,
error_handling => 'FAIL_ON_ERROR',
round => #CDSBoolean.true,
decimal_shift => #CDSBoolean.true,
decimal_shift_back => #CDSBoolean.true
) as opn_ord_for_del_amt_idc)
else
cast ( currency_conversion(
amount => OpenSlsOrdersForDelivNetAmount,
source_currency => TransactionCurrency,
target_currency => :P_DisplayCurrency,
exchange_rate_date => GoodsIssueDate, -- <---
exchange_rate_type => :P_ExchangeRateType,
error_handling => 'SET_TO_NULL', --'SET_TO_NULL',
round => #CDSBoolean.true,
decimal_shift => #CDSBoolean.true,
decimal_shift_back => #CDSBoolean.true
) as opn_ord_for_del_amt_idc)
end as OpnSlsOrdsForDelivAmtInDspCrcy,
OpnSlsOrdrsForDelivQuantity,
//Measures for Billing Plan Due Date
cast ( 0 as opn_ord_for_iplan_amt_itc ) as OpnSlsOrdsForInvcPlansNetAmt,
cast ( 0 as opn_ord_for_iplan_amt_idc ) as OpnSlsOrdsForInvcPlansNetAmtDC,
//Measures Delivery Document Item
cast (0 as opn_dlv_for_inv_net_amt) as OpnOutbDelivsForInvcNetAmt,
cast (0 as opn_shppd_dlv_for_inv_net_shor) as ShpdNotInvcdDelivsNetAmount, --AT 25.4.2018: new for CE1808
cast (0 as opn_shppd_dlv_for_inv_net_amt) as ShpdNotInvcdDelivsNetAmount_2,
cast (0 as opn_dlv_for_inv_amt_idc) as OpnOutbDelivsForInvcNetAmtInDC,
cast (0 as opn_shppd_dlv_for_inv_amt_idc) as ShpdNotInvcdDelivsNetAmtInDC, --AT 25.4.2018: new for CE1808
cast (0 as opn_dlv_for_inv_qty) as OpnOutbDelivsForInvcQty,
cast (0 as opn_shppd_dlv_for_inv_qty) as ShpdNotInvcdDelivsQuantity, --AT 25.4.2018: new for CE1808
//Measures Billing Document Item
cast (0 as sls_vlm_net_amt_short) as SalesVolumeNetAmount,
cast (0 as sls_vlm_net_amt) as SalesVolumeNetAmount_2,
cast ( 0 as mc_umnetwr) as SlsVolumeNetAmtInDspCrcy,
// cast ( 0 as prelim_sls_vlm_net_amt) as PrelimBillgSlsVolNetAmt, --AT 12.11.2018 added for CE1902 --AT9.4.2019 back again
// cast ( 0 as prelim_sls_vlm_net_amt_idc) as PrelimBillgSlsVolNetAmtInDC, --AT 12.11.2018 added for CE1902 --AT9.4.2019 back again
cast (0 as mc_ummenge) as SalesVolumeQuantity,
cast (0 as prf_marg_net_amt_short) as SalesProfitMarginNetAmount,
cast (0 as prf_marg_net_amt) as SalesProfitMarginNetAmount_2,
cast ( 0 as prf_marg_net_amt_idc) as SlsProfitMargNetAmtInDspCrcy,
--SalesProfitMargin, Calculated on AE level
cast (0 as credit_memo_net_amt_short ) as CustomerCreditMemoNetAmount,
cast (0 as credit_memo_net_amt ) as CustomerCreditMemoNetAmount_2,
cast ( 0 as mc_gunetwr) as CustCrdtMemoNetAmtInDspCrcy,
cast (0 as mc_gumenge) as CustCreditMemoQuantity,
cast ( 0 as cm_prf_marg_net_amt) as CustCrdtMemoPrftMargNetAmt, -- AT 13.04.2018: added for CE1808
cast ( 0 as cm_prf_marg_net_amt_idc) as CustCrdtMemoPrftMargNetAmtInDC, -- AT 13.04.2018: added for CE1808
--AT 29.04.2019: SubTotal1..6 & SubTotal1..6InDC added
cast (0 as subtotalamount1) as Subtotal1Amount,
cast (0 as subtotalamount1_idc) as Subtotal1AmountInDC,
cast (0 as subtotalamount2) as Subtotal2Amount,
cast (0 as subtotalamount2_idc) as Subtotal2AmountInDC,
cast (0 as subtotalamount3) as Subtotal3Amount,
cast (0 as subtotalamount3_idc) as Subtotal3AmountInDC,
cast (0 as subtotalamount4) as Subtotal4Amount,
cast (0 as subtotalamount4_idc) as Subtotal4AmountInDC,
cast (0 as subtotalamount5) as Subtotal5Amount,
cast (0 as subtotalamount5_idc) as Subtotal5AmountInDC,
cast (0 as subtotalamount6) as Subtotal6Amount,
cast (0 as subtotalamount6_idc) as Subtotal6AmountInDC,
TransactionCurrency,
StatisticsCurrency,
BaseUnit,
// Misc (for DCL, I_CalenderDa --Area SalesDocument
SalesDocumentType, -- DCL Check for SalesDocument
cast('' as vstel) as ShippingPoint, -- DCL Check for DeliveryDocument --AT8.7.2019 dataelement VSTEL instead of VSART
cast('' as fkart) as BillingDocumentType, -- DCL Check for BillingDocument
--GoodsIssueDate as CalendarDate, -- for joining I_calendarDate & CurrencyConversion
case when GoodsIssueDate = '00000000' then
DeliveryDate else GoodsIssueDate end as CalendarDate , -- for joining I_CalendarDate later (same date as for CurrencyConversion)
CreationDate,
DeliveryDate, --AT14.10.2021 CE2202 expose DeliveryDate from I_SLAnalytics (CFD Jira SDANALYTICS01-873)
//Associations (further exposed above)
_TransactionCurrency,
_StatisticsCurrency,
_BaseUnit,
_DistributionChannel,
_OrganizationDivision as _Division,
@VDM.lifecycle: { status: #DEPRECATED, successor: '_Product' }
_Material,
_Product,
@VDM.lifecycle: { status: #DEPRECATED, successor: '_ProductGroup' }
_MaterialGroup,
_ProductGroup,
_OriginallyRequestedMaterial,
_SalesDistrict,
_SalesOffice,
_SalesGroup,
_SalesOrganization,
_SoldToParty,
_CustomerGroup,
_SDDocumentCategory
} where TransactionCurrency <> ''
union all
select from I_BillingPlanDueDateAnalytics //Core Layer Element: Billing Plan Due Date
{
//key
key SalesDocument,
key SalesDocumentItem,
key cast('' as etenr ) as ScheduleLine,
key BillingPlan,
key BillingPlanItem,
key cast('' as vbeln_vl) as DeliveryDocument,
key cast('' as posnr_vl) as DeliveryDocumentItem,
key cast('' as vbeln_vf) as BillingDocument,
key cast('' as posnr_vf) as BillingDocumentItem,
//Dimensions
//Customer
@ObjectModel.foreignKey.association: '_SoldToParty'
SoldToParty,
SoldToPartyName,
CustomerGroup,
--AT 29.04.2019: AdditionalCustomerGroup1..5 added
@ObjectModel.foreignKey.association: '_AdditionalCustomerGroup1'
AdditionalCustomerGroup1,
_AdditionalCustomerGroup1,
@ObjectModel.foreignKey.association: '_AdditionalCustomerGroup2'
AdditionalCustomerGroup2,
_AdditionalCustomerGroup2,
@ObjectModel.foreignKey.association: '_AdditionalCustomerGroup3'
AdditionalCustomerGroup3,
_AdditionalCustomerGroup3,
@ObjectModel.foreignKey.association: '_AdditionalCustomerGroup4'
AdditionalCustomerGroup4,
_AdditionalCustomerGroup4,
@ObjectModel.foreignKey.association: '_AdditionalCustomerGroup5'
AdditionalCustomerGroup5,
_AdditionalCustomerGroup5,
// ----- Category -----
@ObjectModel.foreignKey.association: '_SDDocumentCategory'
SDDocumentCategory,
cast('A' as sd_doc_object_name) as SDDocumentObject, -- AT23.02.2018: for extensibility
//Organization
@ObjectModel.foreignKey.association: '_SalesOrganization'
SalesOrganization,
@ObjectModel.foreignKey.association: '_DistributionChannel'
DistributionChannel,
@ObjectModel.foreignKey.association: '_Division'
OrganizationDivision as Division,
//Team View
@ObjectModel.foreignKey.association: '_SalesOffice'
SalesOffice,
@ObjectModel.foreignKey.association: '_SalesGroup'
SalesGroup,
//Geographics
@ObjectModel.foreignKey.association: '_SalesDistrict'
SalesDistrict,
//Product
--AT*2 17.2.2020 add Product
@ObjectModel.foreignKey.association: '_Material'
@VDM.lifecycle: { status: #DEPRECATED, successor: 'Product' }
Material,
@Analytics.internalName: #LOCAL
@ObjectModel.foreignKey.association: '_Product'
Product,
@ObjectModel.foreignKey.association: '_OriginallyRequestedMaterial'
OriginallyRequestedMaterial,
InternationalArticleNumber,
ProductHierarchyNode,
@ObjectModel.foreignKey.association: '_MaterialGroup'
@VDM.lifecycle: { status: #DEPRECATED, successor: 'ProductGroup' }
MaterialGroup,
@Analytics.internalName: #LOCAL
@ObjectModel.foreignKey.association: '_ProductGroup'
ProductGroup,
--AT 29.04.2019: AdditionalMaterialGroup1..5 added
@ObjectModel.foreignKey.association: '_AdditionalMaterialGroup1'
AdditionalMaterialGroup1,
_AdditionalMaterialGroup1,
@ObjectModel.foreignKey.association: '_AdditionalMaterialGroup2'
AdditionalMaterialGroup2,
_AdditionalMaterialGroup2,
@ObjectModel.foreignKey.association: '_AdditionalMaterialGroup3'
AdditionalMaterialGroup3,
_AdditionalMaterialGroup3,
@ObjectModel.foreignKey.association: '_AdditionalMaterialGroup4'
AdditionalMaterialGroup4,
_AdditionalMaterialGroup4,
@ObjectModel.foreignKey.association: '_AdditionalMaterialGroup5'
AdditionalMaterialGroup5,
_AdditionalMaterialGroup5,
ProfitCenter,
//Time Period
-- see I_SalesAnalyticsCube
//Measures
//Measures Sales Document Item
cast ( 0 as opn_ords_for_ordrelinv_netamt) as OpnSOForOrdReltdInvcsNetAmount,
cast ( 0 as opn_ords_for_ordrelinv_amt_idc) as OpnSOForOrdReltdInvcsNetAmtDC,
cast ( 0 as opn_ords_for_ordrel_invcs_qty) as OpnSlsOrdsForOrdReltdInvcsQty,
cast ( 0 as opn_rets_for_invc_net_amt ) as OpnRetsForOrdReltdInvcsNetAmt,
cast ( 0 as opn_rets_for_invc_net_amt_idc ) as OpnRetsOrdReltdInvcsNetAmtInDC,
cast ( 0 as opn_rets_for_invc_net_qty ) as OpnRetsForOrdReltdInvcsQty,
//Measures Schedule Line
cast(0 as opn_ord_for_del_amt_itc) as OpenSlsOrdersForDelivNetAmount,
cast (0 as opn_ord_for_del_amt_idc) as OpnSlsOrdsForDelivAmtInDspCrcy,
cast(0 as opn_ord_for_del_qty) as OpnSlsOrdrsForDelivQuantity,
//Measures for Billing Plan Due Date
OpnSlsOrdsForInvcPlansNetAmt,
cast ( currency_conversion(
amount => OpnSlsOrdsForInvcPlansNetAmt,
source_currency => TransactionCurrency,
target_currency => :P_DisplayCurrency,
exchange_rate_date => BillingPlanBillingDate,
exchange_rate_type => :P_ExchangeRateType,
error_handling => 'FAIL_ON_ERROR',
round => #CDSBoolean.true,
decimal_shift => #CDSBoolean.true,
decimal_shift_back => #CDSBoolean.true
) as opn_ord_for_iplan_amt_idc) as OpnSlsOrdsForInvcPlansNetAmtDC,
//Measures Delivery Document Item
cast (0 as opn_dlv_for_inv_net_amt) as OpnOutbDelivsForInvcNetAmt,
cast (0 as opn_shppd_dlv_for_inv_net_shor) as ShpdNotInvcdDelivsNetAmount, --AT 25.4.2018: new for CE1808
cast (0 as opn_shppd_dlv_for_inv_net_amt) as ShpdNotInvcdDelivsNetAmount_2,
cast (0 as opn_dlv_for_inv_amt_idc) as OpnOutbDelivsForInvcNetAmtInDC,
cast (0 as opn_shppd_dlv_for_inv_amt_idc) as ShpdNotInvcdDelivsNetAmtInDC, --AT 25.4.2018: new for CE1808
cast (0 as opn_dlv_for_inv_qty) as OpnOutbDelivsForInvcQty,
cast (0 as opn_shppd_dlv_for_inv_qty) as ShpdNotInvcdDelivsQuantity, --AT 25.4.2018: new for CE1808
//Measures Billing Document Item
cast (0 as sls_vlm_net_amt_short) as SalesVolumeNetAmount,
cast (0 as sls_vlm_net_amt) as SalesVolumeNetAmount_2,
cast ( 0 as mc_umnetwr) as SlsVolumeNetAmtInDspCrcy,
// cast ( 0 as prelim_sls_vlm_net_amt) as PrelimBillgSlsVolNetAmt, --AT 12.11.2018 added for CE1902 --AT9.4.2019 back again
// cast ( 0 as prelim_sls_vlm_net_amt_idc) as PrelimBillgSlsVolNetAmtInDC, --AT 12.11.2018 added for CE1902 --AT9.4.2019 back again
cast (0 as mc_ummenge) as SalesVolumeQuantity,
cast (0 as prf_marg_net_amt_short) as SalesProfitMarginNetAmount,
cast (0 as prf_marg_net_amt) as SalesProfitMarginNetAmount_2,
cast ( 0 as prf_marg_net_amt_idc) as SlsProfitMargNetAmtInDspCrcy,
--SalesProfitMargin, Calculated on AE level
cast (0 as credit_memo_net_amt_short ) as CustomerCreditMemoNetAmount,
cast (0 as credit_memo_net_amt ) as CustomerCreditMemoNetAmount_2,
cast ( 0 as mc_gunetwr) as CustCrdtMemoNetAmtInDspCrcy,
cast (0 as mc_gumenge) as CustCreditMemoQuantity,
cast ( 0 as cm_prf_marg_net_amt) as CustCrdtMemoPrftMargNetAmt, -- AT 13.04.2018: added for CE1808
cast ( 0 as cm_prf_marg_net_amt_idc) as CustCrdtMemoPrftMargNetAmtInDC, -- AT 13.04.2018: added for CE1808
--AT 29.04.2019: SubTotal1..6 & SubTotal1..6InDC added
cast (0 as subtotalamount1) as Subtotal1Amount,
cast (0 as subtotalamount1_idc) as Subtotal1AmountInDC,
cast (0 as subtotalamount2) as Subtotal2Amount,
cast (0 as subtotalamount2_idc) as Subtotal2AmountInDC,
cast (0 as subtotalamount3) as Subtotal3Amount,
cast (0 as subtotalamount3_idc) as Subtotal3AmountInDC,
cast (0 as subtotalamount4) as Subtotal4Amount,
cast (0 as subtotalamount4_idc) as Subtotal4AmountInDC,
cast (0 as subtotalamount5) as Subtotal5Amount,
cast (0 as subtotalamount5_idc) as Subtotal5AmountInDC,
cast (0 as subtotalamount6) as Subtotal6Amount,
cast (0 as subtotalamount6_idc) as Subtotal6AmountInDC,
TransactionCurrency,
StatisticsCurrency,
BaseUnit,
// Misc (for DCL, I_CalenderDate-join)
cast('' as auart) as SalesDocumentType, -- DCL Check for SalesDocument
cast('' as vstel) as ShippingPoint, -- DCL Check for DeliveryDocument --AT8.7.2019 dataelement VSTEL instead of VSART
cast('' as fkart) as BillingDocumentType, -- DCL Check for BillingDocument
--BillingDocumentDate as CalendarDate, -- for joining I_calendarDate & CurrencyConversion
--case when BillingPlanBillingDate = '00000000' then
--CreationDate else BillingPlanBillingDate end as CalendarDate ,
BillingPlanBillingDate as CalendarDate ,
CreationDate,
cast ( '' as edatu) as DeliveryDate, --AT14.10.2021 CE2202 expose DeliveryDate from I_SLAnalytics (CFD Jira SDANALYTICS01-873)
//Associations (further exposed above)
_TransactionCurrency,
_StatisticsCurrency,
_BaseUnit,
_DistributionChannel,
_OrganizationDivision as _Division,
@VDM.lifecycle: { status: #DEPRECATED, successor: '_Product' }
_Material,
_Product,
@VDM.lifecycle: { status: #DEPRECATED, successor: '_ProductGroup' }
_MaterialGroup,
_ProductGroup,
_OriginallyRequestedMaterial,
_SalesDistrict,
_SalesOffice,
_SalesGroup,
_SalesOrganization,
_SoldToParty,
_CustomerGroup,
_SDDocumentCategory
}
where ( OrderRelatedBillingStatus = 'A' or OrderRelatedBillingStatus = 'B' ) and
BillingPlanRelatedBillgStatus = 'A'
union all
select from I_DeliveryDocItemAnalytics //Core Layer Element: Delivery Document Item
{
//key
key cast('' as vbeln_va) as SalesDocument,
key cast('' as posnr_va) as SalesDocumentItem,
key cast('' as etenr ) as ScheduleLine,
key cast('' as fplnr) as BillingPlan,
key cast('' as fpltr) as BillingPlanItem,
key DeliveryDocument,
key DeliveryDocumentItem,
key cast('' as vbeln_vf) as BillingDocument,
key cast('' as posnr_vf) as BillingDocumentItem,
//Dimensions
//Customer
@ObjectModel.foreignKey.association: '_SoldToParty'
SoldToParty,
SoldToPartyName,
CustomerGroup,
--AT 29.04.2019: AdditionalCustomerGroup1..5 added
@ObjectModel.foreignKey.association: '_AdditionalCustomerGroup1'
AdditionalCustomerGroup1,
_AdditionalCustomerGroup1,
@ObjectModel.foreignKey.association: '_AdditionalCustomerGroup2'
AdditionalCustomerGroup2,
_AdditionalCustomerGroup2,
@ObjectModel.foreignKey.association: '_AdditionalCustomerGroup3'
AdditionalCustomerGroup3,
_AdditionalCustomerGroup3,
@ObjectModel.foreignKey.association: '_AdditionalCustomerGroup4'
AdditionalCustomerGroup4,
_AdditionalCustomerGroup4,
@ObjectModel.foreignKey.association: '_AdditionalCustomerGroup5'
AdditionalCustomerGroup5,
_AdditionalCustomerGroup5,
// ----- Category -----
@ObjectModel.foreignKey.association: '_SDDocumentCategory'
SDDocumentCategory,
cast('L' as sd_doc_object) as SDDocumentObject, -- AT23.02.2018: for extensibility
//Organization
@ObjectModel.foreignKey.association: '_SalesOrganization'
SalesOrganization,
@ObjectModel.foreignKey.association: '_DistributionChannel'
DistributionChannel,
@ObjectModel.foreignKey.association: '_Division'
Division,
//Team View
@ObjectModel.foreignKey.association: '_SalesOffice'
SalesOffice,
@ObjectModel.foreignKey.association: '_SalesGroup'
SalesGroup,
//Geographics
@ObjectModel.foreignKey.association: '_SalesDistrict'
SalesDistrict,
//Product
--AT*2 17.2.2020 add Product
@ObjectModel.foreignKey.association: '_Material'
@VDM.lifecycle: { status: #DEPRECATED, successor: 'Product' }
Material,
@Analytics.internalName: #LOCAL
@ObjectModel.foreignKey.association: '_Product'
Product,
@ObjectModel.foreignKey.association: '_OriginallyRequestedMaterial'
OriginallyRequestedMaterial,
InternationalArticleNumber,
ProductHierarchyNode,
@ObjectModel.foreignKey.association: '_MaterialGroup'
@VDM.lifecycle: { status: #DEPRECATED, successor: 'ProductGroup' }
MaterialGroup,
@Analytics.internalName: #LOCAL
@ObjectModel.foreignKey.association: '_ProductGroup'
ProductGroup,
--AT 29.04.2019: AdditionalMaterialGroup1..5 added
@ObjectModel.foreignKey.association: '_AdditionalMaterialGroup1'
AdditionalMaterialGroup1,
_AdditionalMaterialGroup1,
@ObjectModel.foreignKey.association: '_AdditionalMaterialGroup2'
AdditionalMaterialGroup2,
_AdditionalMaterialGroup2,
@ObjectModel.foreignKey.association: '_AdditionalMaterialGroup3'
AdditionalMaterialGroup3,
_AdditionalMaterialGroup3,
@ObjectModel.foreignKey.association: '_AdditionalMaterialGroup4'
AdditionalMaterialGroup4,
_AdditionalMaterialGroup4,
@ObjectModel.foreignKey.association: '_AdditionalMaterialGroup5'
AdditionalMaterialGroup5,
_AdditionalMaterialGroup5,
ProfitCenter,
//Time Period
-- see I_SalesAnalyticsCube
//Measures
//Measures Sales Document Item
cast ( 0 as opn_ords_for_ordrelinv_netamt) as OpnSOForOrdReltdInvcsNetAmount,
cast ( 0 as opn_ords_for_ordrelinv_amt_idc) as OpnSOForOrdReltdInvcsNetAmtDC,
cast ( 0 as opn_ords_for_ordrel_invcs_qty) as OpnSlsOrdsForOrdReltdInvcsQty,
cast ( 0 as opn_rets_for_invc_net_amt ) as OpnRetsForOrdReltdInvcsNetAmt,
cast ( 0 as opn_rets_for_invc_net_amt_idc ) as OpnRetsOrdReltdInvcsNetAmtInDC,
cast ( 0 as opn_rets_for_invc_net_qty ) as OpnRetsForOrdReltdInvcsQty,
//Measures Schedule Line
cast(0 as opn_ord_for_del_amt_itc) as OpenSlsOrdersForDelivNetAmount,
cast (0 as opn_ord_for_del_amt_idc) as OpnSlsOrdsForDelivAmtInDspCrcy,
cast(0 as opn_ord_for_del_qty) as OpnSlsOrdrsForDelivQuantity,
//Measures for Billing Plan Due Date
cast ( 0 as opn_ord_for_iplan_amt_itc ) as OpnSlsOrdsForInvcPlansNetAmt,
cast ( 0 as opn_ord_for_iplan_amt_idc ) as OpnSlsOrdsForInvcPlansNetAmtDC,
//Measures DeliveryDocumentItem
OpnOutbDelivsForInvcNetAmt,
ShpdNotInvcdDelivsNetAmount, --AT 25.4.2018: new for CE1808
ShpdNotInvcdDelivsNetAmount_2,
// cast ( currency_conversion(
// amount => OpnOutbDelivsForInvcNetAmt,
// source_currency => TransactionCurrency,
// target_currency => :P_DisplayCurrency,
// exchange_rate_date => BillingDocumentDate,
// exchange_rate_type => :P_ExchangeRateType,
// error_handling => 'FAIL_ON_ERROR',
// round => #CDSBoolean.true,
// decimal_shift => #CDSBoolean.true,
// decimal_shift_back => #CDSBoolean.true
// ) as opn_dlv_for_inv_amt_idc) as OpnOutbDelivsForInvcNetAmtInDC,
case when BillingDocumentDate = '00000000'
then
cast ( currency_conversion(
amount => OpnOutbDelivsForInvcNetAmt,
source_currency => TransactionCurrency,
target_currency => :P_DisplayCurrency,
exchange_rate_date => cast($session.system_date as dats), -- <---
exchange_rate_type => :P_ExchangeRateType,
error_handling => 'FAIL_ON_ERROR',
round => #CDSBoolean.true,
decimal_shift => #CDSBoolean.true,
decimal_shift_back => #CDSBoolean.true
) as opn_dlv_for_inv_amt_idc ) --opn_ord_for_del_amt_idc)
else
cast ( currency_conversion(
amount => OpnOutbDelivsForInvcNetAmt,
source_currency => TransactionCurrency,
target_currency => :P_DisplayCurrency,
exchange_rate_date => BillingDocumentDate, -- <---
exchange_rate_type => :P_ExchangeRateType,
error_handling => 'SET_TO_NULL', --'SET_TO_NULL',
round => #CDSBoolean.true,
decimal_shift => #CDSBoolean.true,
decimal_shift_back => #CDSBoolean.true
) as opn_dlv_for_inv_amt_idc ) --opn_ord_for_del_amt_idc)
end as OpnOutbDelivsForInvcNetAmtInDC,
case when BillingDocumentDate = '00000000'
then
cast ( currency_conversion(
amount => ShpdNotInvcdDelivsNetAmount,
source_currency => TransactionCurrency,
target_currency => :P_DisplayCurrency,
exchange_rate_date => cast($session.system_date as dats), -- <---
exchange_rate_type => :P_ExchangeRateType,
error_handling => 'FAIL_ON_ERROR',
round => #CDSBoolean.true,
decimal_shift => #CDSBoolean.true,
decimal_shift_back => #CDSBoolean.true
) as opn_shppd_dlv_for_inv_amt_idc)
else
cast ( currency_conversion(
amount => ShpdNotInvcdDelivsNetAmount,
source_currency => TransactionCurrency,
target_currency => :P_DisplayCurrency,
exchange_rate_date => BillingDocumentDate, -- <---
exchange_rate_type => :P_ExchangeRateType,
error_handling => 'SET_TO_NULL', --'SET_TO_NULL',
round => #CDSBoolean.true,
decimal_shift => #CDSBoolean.true,
decimal_shift_back => #CDSBoolean.true
) as opn_shppd_dlv_for_inv_amt_idc)
end as ShpdNotInvcdDelivsNetAmtInDC, --AT 25.4.2018: new for CE1808
OpnOutbDelivsForInvcQty,
ShpdNotInvcdDelivsQuantity, --AT 25.4.2018: new for CE1808
//Measures Billing Document Item
cast (0 as sls_vlm_net_amt_short) as SalesVolumeNetAmount,
cast (0 as sls_vlm_net_amt) as SalesVolumeNetAmount_2,
cast ( 0 as mc_umnetwr) as SlsVolumeNetAmtInDspCrcy,
// cast ( 0 as prelim_sls_vlm_net_amt) as PrelimBillgSlsVolNetAmt, --AT 12.11.2018 added for CE1902 --AT9.4.2019 back again
// cast ( 0 as prelim_sls_vlm_net_amt_idc) as PrelimBillgSlsVolNetAmtInDC, --AT 12.11.2018 added for CE1902 --AT9.4.2019 back again
cast (0 as mc_ummenge) as SalesVolumeQuantity,
cast (0 as prf_marg_net_amt_short) as SalesProfitMarginNetAmount,
cast (0 as prf_marg_net_amt) as SalesProfitMarginNetAmount_2,
cast ( 0 as prf_marg_net_amt_idc) as SlsProfitMargNetAmtInDspCrcy,
--SalesProfitMargin, Calculated on AE level
cast (0 as credit_memo_net_amt_short ) as CustomerCreditMemoNetAmount,
cast (0 as credit_memo_net_amt ) as CustomerCreditMemoNetAmount_2,
cast ( 0 as mc_gunetwr) as CustCrdtMemoNetAmtInDspCrcy,
cast (0 as mc_gumenge) as CustCreditMemoQuantity,
cast ( 0 as cm_prf_marg_net_amt) as CustCrdtMemoPrftMargNetAmt, -- AT 13.04.2018: added for CE1808
cast ( 0 as cm_prf_marg_net_amt_idc) as CustCrdtMemoPrftMargNetAmtInDC, -- AT 13.04.2018: added for CE1808
--AT 29.04.2019: SubTotal1..6 & SubTotal1..6InDC added
cast (0 as subtotalamount1) as Subtotal1Amount,
cast (0 as subtotalamount1_idc) as Subtotal1AmountInDC,
cast (0 as subtotalamount2) as Subtotal2Amount,
cast (0 as subtotalamount2_idc) as Subtotal2AmountInDC,
cast (0 as subtotalamount3) as Subtotal3Amount,
cast (0 as subtotalamount3_idc) as Subtotal3AmountInDC,
cast (0 as subtotalamount4) as Subtotal4Amount,
cast (0 as subtotalamount4_idc) as Subtotal4AmountInDC,
cast (0 as subtotalamount5) as Subtotal5Amount,
cast (0 as subtotalamount5_idc) as Subtotal5AmountInDC,
cast (0 as subtotalamount6) as Subtotal6Amount,
cast (0 as subtotalamount6_idc) as Subtotal6AmountInDC,
TransactionCurrency,
StatisticsCurrency,
BaseUnit,
// Misc (for DCL, I_CalenderDate-join)
cast('' as auart) as SalesDocumentType, -- DCL Check for SalesDocument
ShippingPoint, -- DCL Check for DeliveryDocument
cast('' as fkart) as BillingDocumentType, -- DCL Check for BillingDocument
--BillingDocumentDate as CalendarDate, -- for joining I_calendarDate & CurrencyConversion
--2AT 24.1.2022: refactoring COS before APL Adoption
-- now three step fallback (BillingDocumentDate, CreationDate, SystemDate)
// case when BillingDocumentDate = '00000000' then
// CreationDate else BillingDocumentDate end as CalendarDate ,
case when BillingDocumentDate = '00000000' or BillingDocumentDate is null --AT15.02.2022 is null introduced
then case when CreationDate = '00000000' or CreationDate is null
then cast($session.system_date as dats)
else CreationDate
end
else BillingDocumentDate
end as CalendarDate,
CreationDate,
cast ( '' as edatu) as DeliveryDate, --AT14.10.2021 CE2202 expose DeliveryDate from I_SLAnalytics (CFD Jira SDANALYTICS01-873)
//Associations (further exposed above)
_TransactionCurrency,
_StatisticsCurrency,
_BaseUnit,
_DistributionChannel,
_Division,
@VDM.lifecycle: { status: #DEPRECATED, successor: '_Product' }
_Material,
_Product,
@VDM.lifecycle: { status: #DEPRECATED, successor: '_ProductGroup' }
_MaterialGroup,
_ProductGroup,
_OriginallyRequestedMaterial,
_SalesDistrict,
_SalesOffice,
_SalesGroup,
_SalesOrganization,
_SoldToParty,
_CustomerGroup,
_SDDocumentCategory
} where TransactionCurrency <> '' and
( DeliveryRelatedBillingStatus = 'A' or //limit to only needed subset
DeliveryRelatedBillingStatus = 'B'
)
union all
select from I_BillingDocItemAnalytics --I_BillgDocItmBscAnlyts --I_BillingDocItemAnalytics //Core Layer Element: Billing Document Item //AT16.11.2018 base on "new BillingDocumentItemAnalytics" //9.4.2018 back to I_billingDocItemAnalytics
{
//key
key cast('' as vbeln_va) as SalesDocument,
key cast('' as posnr_va) as SalesDocumentItem,
key cast('' as etenr ) as ScheduleLine,
key cast('' as fplnr) as BillingPlan,
key cast('' as fpltr) as BillingPlanItem,
key cast('' as vbeln_vl) as DeliveryDocument,
key cast('' as posnr_vl) as DeliveryDocumentItem,
key BillingDocument,
key BillingDocumentItem,
//Dimensions
//Customer
@ObjectModel.foreignKey.association: '_SoldToParty'
SoldToParty,
SoldToPartyName,
CustomerGroup,
--AT 29.04.2019: AdditionalCustomerGroup1..5 added
@ObjectModel.foreignKey.association: '_AdditionalCustomerGroup1'
AdditionalCustomerGroup1,
_AdditionalCustomerGroup1,
@ObjectModel.foreignKey.association: '_AdditionalCustomerGroup2'
AdditionalCustomerGroup2,
_AdditionalCustomerGroup2,
@ObjectModel.foreignKey.association: '_AdditionalCustomerGroup3'
AdditionalCustomerGroup3,
_AdditionalCustomerGroup3,
@ObjectModel.foreignKey.association: '_AdditionalCustomerGroup4'
AdditionalCustomerGroup4,
_AdditionalCustomerGroup4,
@ObjectModel.foreignKey.association: '_AdditionalCustomerGroup5'
AdditionalCustomerGroup5,
_AdditionalCustomerGroup5,
// ----- Category -----
@ObjectModel.foreignKey.association: '_SDDocumentCategory'
SDDocumentCategory,
cast('F' as sd_doc_object) as SDDocumentObject, -- AT23.02.2018: for extensibility
//Organization
@ObjectModel.foreignKey.association: '_SalesOrganization'
SalesOrganization,
@ObjectModel.foreignKey.association: '_DistributionChannel'
DistributionChannel,
@ObjectModel.foreignKey.association: '_Division'
Division,
//Team View
@ObjectModel.foreignKey.association: '_SalesOffice'
SalesOffice,
@ObjectModel.foreignKey.association: '_SalesGroup'
SalesGroup,
//Geographics
@ObjectModel.foreignKey.association: '_SalesDistrict'
SalesDistrict,
//Product
--AT*2 17.2.2020 add Product
@ObjectModel.foreignKey.association: '_Material'
@VDM.lifecycle: { status: #DEPRECATED, successor: 'Product' }
Material,
@Analytics.internalName: #LOCAL
@ObjectModel.foreignKey.association: '_Product'
Product,
@ObjectModel.foreignKey.association: '_OriginallyRequestedMaterial'
OriginallyRequestedMaterial,
InternationalArticleNumber,
ProductHierarchyNode,
@ObjectModel.foreignKey.association: '_MaterialGroup'
@VDM.lifecycle: { status: #DEPRECATED, successor: 'ProductGroup' }
MaterialGroup,
@Analytics.internalName: #LOCAL
@ObjectModel.foreignKey.association: '_ProductGroup'
ProductGroup,
--AT 29.04.2019: AdditionalMaterialGroup1..5 added
@ObjectModel.foreignKey.association: '_AdditionalMaterialGroup1'
AdditionalMaterialGroup1,
_AdditionalMaterialGroup1,
@ObjectModel.foreignKey.association: '_AdditionalMaterialGroup2'
AdditionalMaterialGroup2,
_AdditionalMaterialGroup2,
@ObjectModel.foreignKey.association: '_AdditionalMaterialGroup3'
AdditionalMaterialGroup3,
_AdditionalMaterialGroup3,
@ObjectModel.foreignKey.association: '_AdditionalMaterialGroup4'
AdditionalMaterialGroup4,
_AdditionalMaterialGroup4,
@ObjectModel.foreignKey.association: '_AdditionalMaterialGroup5'
AdditionalMaterialGroup5,
_AdditionalMaterialGroup5,
ProfitCenter,
//Time Period
-- see I_SalesAnalyticsCube
//Measures
//Measures Sales Document Item
cast (0 as opn_ords_for_ordrelinv_netamt) as OpnSOForOrdReltdInvcsNetAmount,
cast (0 as opn_ords_for_ordrelinv_amt_idc) as OpnSOForOrdReltdInvcsNetAmtDC,
cast (0 as opn_ords_for_ordrel_invcs_qty) as OpnSlsOrdsForOrdReltdInvcsQty,
cast ( 0 as opn_rets_for_invc_net_amt ) as OpnRetsForOrdReltdInvcsNetAmt,
cast ( 0 as opn_rets_for_invc_net_amt_idc ) as OpnRetsOrdReltdInvcsNetAmtInDC,
cast ( 0 as opn_rets_for_invc_net_qty ) as OpnRetsForOrdReltdInvcsQty,
//Measures ScheduleLine
cast (0 as opn_ord_for_del_amt_itc) as OpenSlsOrdersForDelivNetAmount,
cast (0 as opn_ord_for_del_amt_idc) as OpnSlsOrdsForDelivAmtInDspCrcy,
cast (0 as opn_ord_for_del_qty) as OpnSlsOrdrsForDelivQuantity,
//Measures for Billing Plan Due Date
cast (0 as opn_ord_for_iplan_amt_itc) as OpnSlsOrdsForInvcPlansNetAmt,
cast (0 as opn_ord_for_iplan_amt_idc) as OpnSlsOrdsForInvcPlansNetAmtDC,
//Measures DeliveryDocumentItem
cast (0 as opn_dlv_for_inv_net_amt) as OpnOutbDelivsForInvcNetAmt,
cast (0 as opn_shppd_dlv_for_inv_net_shor) as ShpdNotInvcdDelivsNetAmount, --AT 25.4.2018: new for CE1808
cast (0 as opn_shppd_dlv_for_inv_net_amt) as ShpdNotInvcdDelivsNetAmount_2,
cast (0 as opn_dlv_for_inv_amt_idc) as OpnOutbDelivsForInvcNetAmtInDC,
cast (0 as opn_shppd_dlv_for_inv_amt_idc) as ShpdNotInvcdDelivsNetAmtInDC, --AT 25.4.2018: new for CE1808
cast (0 as opn_dlv_for_inv_qty) as OpnOutbDelivsForInvcQty,
cast (0 as opn_shppd_dlv_for_inv_qty) as ShpdNotInvcdDelivsQuantity, --AT 25.4.2018: new for CE1808
//Measures BillingDocumentItem
SalesVolumeNetAmount,
SalesVolumeNetAmount_2,
cast ( currency_conversion(
amount => SalesVolumeNetAmount,
source_currency => TransactionCurrency,
target_currency => :P_DisplayCurrency,
exchange_rate_date => BillingDocumentDate,
exchange_rate_type => :P_ExchangeRateType,
error_handling => 'FAIL_ON_ERROR',
round => #CDSBoolean.true,
decimal_shift => #CDSBoolean.true,
decimal_shift_back => #CDSBoolean.true
) as mc_umnetwr) as SlsVolumeNetAmtInDspCrcy,
// PrelimBillgSlsVolNetAmt, --AT 12.11.2018 added for CE1902 --AT9.4.2019 back again
// cast ( currency_conversion(
// amount => PrelimBillgSlsVolNetAmt,
// source_currency => TransactionCurrency,
// target_currency => :P_DisplayCurrency,
// exchange_rate_date => BillingDocumentDate,
// exchange_rate_type => :P_ExchangeRateType,
// error_handling => 'FAIL_ON_ERROR',
// round => #CDSBoolean.true,
// decimal_shift => #CDSBoolean.true,
// decimal_shift_back => #CDSBoolean.true
// ) as prelim_sls_vlm_net_amt_idc ) as PrelimBillgSlsVolNetAmtInDC, --AT 12.11.2018 added for CE1902
SalesVolumeQuantity,
cast (SalesProfitMarginNetAmount as prf_marg_net_amt_short) as SalesProfitMarginNetAmount,
cast (SalesProfitMarginNetAmount as prf_marg_net_amt) as SalesProfitMarginNetAmount_2,
cast ( currency_conversion(
amount => SalesProfitMarginNetAmount,
source_currency => TransactionCurrency,
target_currency => :P_DisplayCurrency,
exchange_rate_date => BillingDocumentDate,
exchange_rate_type => :P_ExchangeRateType,
error_handling => 'FAIL_ON_ERROR',
round => #CDSBoolean.true,
decimal_shift => #CDSBoolean.true,
decimal_shift_back => #CDSBoolean.true
) as prf_marg_net_amt_idc) as SlsProfitMargNetAmtInDspCrcy,
--SalesProfitMargin, Calculated on AE level
cast (CustomerCreditMemoNetAmount as credit_memo_net_amt_short ) as CustomerCreditMemoNetAmount,
cast (CustomerCreditMemoNetAmount as credit_memo_net_amt ) as CustomerCreditMemoNetAmount_2,
cast ( currency_conversion(
amount => CustomerCreditMemoNetAmount,
source_currency => TransactionCurrency,
target_currency => :P_DisplayCurrency,
exchange_rate_date => BillingDocumentDate,
exchange_rate_type => :P_ExchangeRateType,
error_handling => 'FAIL_ON_ERROR',
round => #CDSBoolean.true,
decimal_shift => #CDSBoolean.true,
decimal_shift_back => #CDSBoolean.true
) as mc_gunetwr) as CustCrdtMemoNetAmtInDspCrcy,
CustCreditMemoQuantity,
CustCrdtMemoPrftMargNetAmt, -- AT 13.04.2018: added for CE1808
cast ( currency_conversion(
amount => CustCrdtMemoPrftMargNetAmt,
source_currency => TransactionCurrency,
target_currency => :P_DisplayCurrency,
exchange_rate_date => BillingDocumentDate,
exchange_rate_type => :P_ExchangeRateType,
error_handling => 'FAIL_ON_ERROR',
round => #CDSBoolean.true,
decimal_shift => #CDSBoolean.true,
decimal_shift_back => #CDSBoolean.true
) as cm_prf_marg_net_amt_idc) as CustCrdtMemoPrftMargNetAmtInDC, -- AT 13.04.2018: added for CE1808
--AT 29.04.2019: SubTotal1..6 & SubTotal1..6InDC added
cast( Subtotal1Amount as subtotalamount1) as Subtotal1Amount ,
cast ( currency_conversion(
amount => Subtotal1Amount,
source_currency => TransactionCurrency,
target_currency => :P_DisplayCurrency,
exchange_rate_date => BillingDocumentDate, -- <---
exchange_rate_type => :P_ExchangeRateType,
error_handling => 'FAIL_ON_ERROR',
round => #CDSBoolean.true,
decimal_shift => #CDSBoolean.true,
decimal_shift_back => #CDSBoolean.true
) as subtotalamount1_idc) as Subtotal1AmountInDC,
cast( Subtotal2Amount as subtotalamount2) as Subtotal2Amount ,
cast ( currency_conversion(
amount => Subtotal2Amount,
source_currency => TransactionCurrency,
target_currency => :P_DisplayCurrency,
exchange_rate_date => BillingDocumentDate, -- <---
exchange_rate_type => :P_ExchangeRateType,
error_handling => 'FAIL_ON_ERROR',
round => #CDSBoolean.true,
decimal_shift => #CDSBoolean.true,
decimal_shift_back => #CDSBoolean.true
) as subtotalamount2_idc) as Subtotal2AmountInDC,
cast( Subtotal3Amount as subtotalamount3) as Subtotal3Amount ,
cast ( currency_conversion(
amount => Subtotal3Amount,
source_currency => TransactionCurrency,
target_currency => :P_DisplayCurrency,
exchange_rate_date => BillingDocumentDate, -- <---
exchange_rate_type => :P_ExchangeRateType,
error_handling => 'FAIL_ON_ERROR',
round => #CDSBoolean.true,
decimal_shift => #CDSBoolean.true,
decimal_shift_back => #CDSBoolean.true
) as subtotalamount3_idc) as Subtotal3AmountInDC,
cast( Subtotal4Amount as subtotalamount4) as Subtotal4Amount ,
cast ( currency_conversion(
amount => Subtotal4Amount,
source_currency => TransactionCurrency,
target_currency => :P_DisplayCurrency,
exchange_rate_date => BillingDocumentDate, -- <---
exchange_rate_type => :P_ExchangeRateType,
error_handling => 'FAIL_ON_ERROR',
round => #CDSBoolean.true,
decimal_shift => #CDSBoolean.true,
decimal_shift_back => #CDSBoolean.true
) as subtotalamount4_idc) as Subtotal4AmountInDC,
cast( Subtotal5Amount as subtotalamount5) as Subtotal5Amount ,
cast ( currency_conversion(
amount => Subtotal5Amount,
source_currency => TransactionCurrency,
target_currency => :P_DisplayCurrency,
exchange_rate_date => BillingDocumentDate, -- <---
exchange_rate_type => :P_ExchangeRateType,
error_handling => 'FAIL_ON_ERROR',
round => #CDSBoolean.true,
decimal_shift => #CDSBoolean.true,
decimal_shift_back => #CDSBoolean.true
) as subtotalamount5_idc) as Subtotal5AmountInDC,
cast( Subtotal6Amount as subtotalamount6) as Subtotal6Amount ,
cast ( currency_conversion(
amount => Subtotal6Amount,
source_currency => TransactionCurrency,
target_currency => :P_DisplayCurrency,
exchange_rate_date => BillingDocumentDate, -- <---
exchange_rate_type => :P_ExchangeRateType,
error_handling => 'FAIL_ON_ERROR',
round => #CDSBoolean.true,
decimal_shift => #CDSBoolean.true,
decimal_shift_back => #CDSBoolean.true
) as subtotalamount6_idc) as Subtotal6AmountInDC,
TransactionCurrency,
StatisticsCurrency,
BaseUnit,
// Misc (for DCL, I_CalenderDate-join)
--Area SalesDocument
cast('' as auart) as SalesDocumentType, -- DCL Check for SalesDocument
cast('' as vstel) as ShippingPoint, -- DCL Check for DeliveryDocument --AT8.7.2019 dataelement VSTEL instead of VSART
BillingDocumentType, -- DCL Check for BillingDocument
--BillingDocumentDate as CalendarDate, -- for joining I_calendarDate & CurrencyConversion
// case when BillingDocumentDate = '00000000' then
// CreationDate else BillingDocumentDate end as CalendarDate ,
-- AT9.3.2018: BillingDocumentDate is definitly be populated (GP) ! Calculation as before prevents aggregation pushdown for Currency Conversion Preaggregation !
BillingDocumentDate as CalendarDate,
CreationDate,
cast ( '' as edatu) as DeliveryDate, --AT14.10.2021 CE2202 expose DeliveryDate from I_SLAnalytics (CFD Jira SDANALYTICS01-873)
//Associations (further exposed above)
_TransactionCurrency,
_StatisticsCurrency,
_BaseUnit,
_DistributionChannel,
_Division,
@VDM.lifecycle: { status: #DEPRECATED, successor: '_Product' }
_Material,
_Product,
@VDM.lifecycle: { status: #DEPRECATED, successor: '_ProductGroup' }
_MaterialGroup,
_ProductGroup,
_OriginallyRequestedMaterial,
_SalesDistrict,
_SalesOffice,
_SalesGroup,
_SalesOrganization,
_SoldToParty,
_CustomerGroup,
_SDDocumentCategory
} where TransactionCurrency <> ''