Margem de contribuição das ordens de venda

Criei esse relatório com objetivo de antecipar a tomada de decisão dos gestores em receber ou não uma ordem de venda que tem margem ou não para empresa.

Facilitando a visualização com uma sinaleira, onde os pedidos que aparecem com o status vermelho, é preciso ficar atento pois está entregando uma margem baixa e pode não ser interessante para empresa.

Precisei criar um cubo buscando as informações necessárias para fazer o calculo.

@AbapCatalog.sqlViewName: 'ZCMARGEM'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@DataAging.noAgingRestriction: true
@Search.searchable: false
@Analytics.dataCategory: #CUBE
@EndUserText.label: 'CDS Margem de Contribuição'

define view ZC_Margem as select from I_SalesOrderItem 
    inner join I_SalesOrderItemPricingElement 
        on  I_SalesOrderItem.SalesOrder = I_SalesOrderItemPricingElement.SalesOrder
        and I_SalesOrderItem.SalesOrderItem = I_SalesOrderItemPricingElement.SalesOrderItem
    inner join keko 
        on I_SalesOrderItem.Material = keko.matnr
        and  I_SalesOrderItem.Plant = keko.werks
    inner join keph 
        on keko.kalnr = keph.kalnr 
        and keko.kadky = keph.kadky
{
    //I_SalesOrderItem
    key I_SalesOrderItem.SalesOrder as SalesOrder,
    key I_SalesOrderItem.SalesOrderItem as SalesOrderItem,
    key I_SalesOrderItem.Material as Material,
    @Aggregation.default: #SUM
    I_SalesOrderItem.OrderQuantity as OrderQuantity,
    I_SalesOrderItem.CreationDate,
    I_SalesOrderItem._SalesOrder.SoldToParty as Customer,
    I_SalesOrderItem._SalesOrder._SoldToParty.CustomerName as CustomerName,
    
    //I_SalesOrderItemPricingElement
    I_SalesOrderItemPricingElement.ConditionType as ConditionType,
    @Aggregation.default: #SUM
    I_SalesOrderItemPricingElement.ConditionAmount as ConditionAmount,
    
    //keko
    keko.feh_sta as Status,
    keko.kalnr as NumCalcCust,
    keko.kadky as DateCalcCust,
    keko.beskz as TipoSuprimento,
    @Aggregation.default: #SUM
    keko.losgr as TamLote,
    
    //keph
    keph.kkzst as Nivel,
    @Aggregation.default: #SUM
    keph.kst001 as MateriaPrima,
    @Aggregation.default: #SUM
    keph.kst003 as ConsumosDiversos,
    @Aggregation.default: #SUM
    keph.kst009 as MaodeObraDireta,
    @Aggregation.default: #SUM
    keph.kst013 as EnergiaEletrica
}
where (I_SalesOrderItemPricingElement.ConditionType = 'IBRX')
or (I_SalesOrderItemPricingElement.ConditionType = 'BX13')
or (I_SalesOrderItemPricingElement.ConditionType = 'BX82')
or (I_SalesOrderItemPricingElement.ConditionType = 'BX72')
or (I_SalesOrderItemPricingElement.ConditionType = 'ZCOM')
or (I_SalesOrderItemPricingElement.ConditionType = 'ZKF0')

Logo montei uma query.

@AbapCatalog.sqlViewName : 'ZQMARGEM'
@Analytics.query: true
@OData.publish: true
@EndUserText.label: 'Query Margem de Contribuição'
define view ZQ_MARGEM as select from ZC_Margem
{

// Rows
@EndUserText.label: 'Ordem de venda'
 @AnalyticsDetails.query.axis:#ROWS
 SalesOrder,
 
// Columns
 @DefaultAggregation: #SUM
 @EndUserText.label: 'Receita Bruta'
 @AnalyticsDetails.query.decimals: 2
 case 
when 
 ( 
    ConditionType   =   'IBRX'
 )  then ConditionAmount
end as ReceitaBruta,
 @DefaultAggregation: #FORMULA
 @AnalyticsDetails.query.formula: '$projection.NegICMS * ( - 1 )'
 @EndUserText.label: 'ICMS'
 @AnalyticsDetails.query.decimals: 2
 0 as ICMS,
 @DefaultAggregation: #FORMULA
 @AnalyticsDetails.query.formula: '$projection.NegPIS * ( - 1 )'
 @EndUserText.label: 'PIS'
 @AnalyticsDetails.query.decimals: 2
 0 as PIS,
 @DefaultAggregation: #FORMULA
 @AnalyticsDetails.query.formula: '$projection.NegCOFINS * ( - 1 )'
 @EndUserText.label: 'COFINS'
 @AnalyticsDetails.query.decimals: 2
 0 as COFINS,
 @DefaultAggregation: #FORMULA
 @AnalyticsDetails.query.formula: '$projection.NegComissao * ( - 1 )'
 @EndUserText.label: 'Comissão'
 @AnalyticsDetails.query.decimals: 2
 0 as Comissao,
@DefaultAggregation: #FORMULA
 @AnalyticsDetails.query.formula: '( $projection.Zeros + $projection.NegFrete ) * ( - 1 )'
 @EndUserText.label: 'Frete'
 @AnalyticsDetails.query.decimals: 2
 0 as Frete,
@DefaultAggregation: #FORMULA
@AnalyticsDetails.query.formula: 'CASE WHEN $projection.Frete < 0 THEN ( ( $projection.EQuantidadePedido * ( $projection.EConsumosDiversos + $projection.EEnergiaEletrica + $projection.EMaodeObradir + $projection.EMateriaPrima ) / $projection.ETamanhoLote * ( - 1 ) ) / 6 ) ELSE ( ( $projection.EQuantidadePedido * ( $projection.EConsumosDiversos + $projection.EEnergiaEletrica + $projection.EMaodeObradir + $projection.EMateriaPrima ) / $projection.ETamanhoLote * ( - 1 ) ) / 5 ) END'
@AnalyticsDetails: {   exceptionAggregationSteps: [{  exceptionAggregationBehavior : #SUM, exceptionAggregationElements: ['Material','SalesOrder']   }]  }
@EndUserText.label: 'Custo Variável'
@AnalyticsDetails.query.decimals: 2
0 as CustoVariavel,
@DefaultAggregation: #FORMULA
@AnalyticsDetails.query.formula: 'CASE WHEN $projection.Frete < 0 THEN ( ( $projection.FQuantidadePedido * ( $projection.FConsumosDiversos ) / $projection.FTamanhoLote * ( - 1 ) ) / 6 ) ELSE ( ( $projection.FQuantidadePedido * ( $projection.FConsumosDiversos) / $projection.FTamanhoLote * ( - 1 ) ) / 5 ) END'
@AnalyticsDetails: {   exceptionAggregationSteps: [{  exceptionAggregationBehavior : #SUM, exceptionAggregationElements: ['Material','SalesOrder']   }]  }
@EndUserText.label: 'CMV'
@AnalyticsDetails.query.decimals: 2
0 as CMV,
@DefaultAggregation: #FORMULA
@AnalyticsDetails.query.formula: '$projection.ReceitaBruta + $projection.ICMS + $projection.PIS + $projection.COFINS + $projection.Frete + $projection.Comissao + $projection.CustoVariavel + $projection.CMV'
@EndUserText.label: 'Margem de Contribuição'
@AnalyticsDetails.query.decimals: 2
0 as MargemdeContribuicao,
@DefaultAggregation: #FORMULA
@AnalyticsDetails.query.formula: '100 * $projection.MargemdeContribuicao / $projection.ReceitaBruta'
@EndUserText.label: 'Margem de Contribuição %'
@AnalyticsDetails.query.decimals: 2
0 as MargemdeContribuicaoPerc,

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
@DefaultAggregation: #FORMULA
@AnalyticsDetails.query.formula: 'CASE WHEN $projection.MargemdeContribuicaoPerc < 30 THEN 1 ELSE 0  END'
@AnalyticsDetails: {   exceptionAggregationSteps: [{  exceptionAggregationBehavior : #SUM, exceptionAggregationElements: ['SalesOrder']   }]  }
@EndUserText.label: 'Ordens com Margem < 30'
0 as MargemRuim,


////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

 // Hidden
 @DefaultAggregation: #SUM
 @EndUserText.label: 'Neg ICMS'
 @AnalyticsDetails.query.decimals: 2
 @Consumption.hidden : true
 case 
when 
 ( 
    ConditionType   =   'BX13'
 )  then ConditionAmount
end as NegICMS,
 @DefaultAggregation: #SUM
 @EndUserText.label: 'Neg PIS'
 @AnalyticsDetails.query.decimals: 2
 @Consumption.hidden : true
 case 
when 
 ( 
    ConditionType   =   'BX82'
 )  then ConditionAmount
end as NegPIS,
 @DefaultAggregation: #SUM
 @EndUserText.label: 'Neg COFINS'
 @AnalyticsDetails.query.decimals: 2
 @Consumption.hidden : true
 case 
when 
 ( 
    ConditionType   =   'BX72'
 )  then ConditionAmount
end as NegCOFINS,
 @DefaultAggregation: #SUM
 @EndUserText.label: 'Neg Comissao'
 @AnalyticsDetails.query.decimals: 2
 @Consumption.hidden : true
 case 
when 
 ( 
    ConditionType   =   'ZCOM'
 )  then ConditionAmount
end as NegComissao,
 @DefaultAggregation: #SUM
 @EndUserText.label: 'Neg Frete'
 @AnalyticsDetails.query.decimals: 2
 @Consumption.hidden : true
 case 
when 
 ( 
    ConditionType   =   'ZKF0'
 )  then ConditionAmount
end as NegFrete,
 @DefaultAggregation: #FORMULA
 @AnalyticsDetails.query.formula: '0'
 @EndUserText.label: 'Zeros'
 @Consumption.hidden : true
 @AnalyticsDetails.query.decimals: 2
 0 as Zeros,

@DefaultAggregation: #SUM
@EndUserText.label: 'EMateriaPrima'
@Consumption.hidden : true
case 
when 
 ( 
    TipoSuprimento  =   'E'
 )  then MateriaPrima
end as EMateriaPrima,
@DefaultAggregation: #SUM
@EndUserText.label: 'EConsumosDiversos'
@Consumption.hidden : true
case 
when 
 ( 
    TipoSuprimento  =   'E'
 )  then ConsumosDiversos
end as EConsumosDiversos,
@DefaultAggregation: #SUM
@EndUserText.label: 'EMaodeObradir'
@Consumption.hidden : true
case 
when 
 ( 
    TipoSuprimento  =   'E'
 )  then MaodeObraDireta
end as EMaodeObradir,
@DefaultAggregation: #SUM
@EndUserText.label: 'EEnergiaEletrica'
@Consumption.hidden : true
case 
when 
 ( 
    TipoSuprimento  =   'E'
 )  then EnergiaEletrica
end as EEnergiaEletrica,

@DefaultAggregation: #SUM
@EndUserText.label: 'FConsumosDiversos'
@Consumption.hidden : true
case 
when 
 ( 
    TipoSuprimento  =   'F'
 )  then ConsumosDiversos
end as FConsumosDiversos,

@DefaultAggregation: #SUM
@EndUserText.label: 'ELote'
@Consumption.hidden : true
case 
when 
 ( 
    TipoSuprimento  =   'E'
 )  then TamLote
end as ELote,
@DefaultAggregation: #SUM
@EndUserText.label: 'FLote'
@Consumption.hidden : true

case 
when 
 ( 
    TipoSuprimento  =   'F'
 )  then TamLote
end as FLote,
@DefaultAggregation: #FORMULA
@AnalyticsDetails.query.formula: 'CASE WHEN $projection.Frete < 0 THEN ( $projection.ELote / 6 ) ELSE ( $projection.ELote / 5 ) END'
@EndUserText.label: 'ETamanho Lote'
@Consumption.hidden : true
0 as ETamanhoLote,
@DefaultAggregation: #FORMULA
@AnalyticsDetails.query.formula: 'CASE WHEN $projection.Frete < 0 THEN ( $projection.FLote / 6 ) ELSE ( $projection.FLote / 5 ) END'
@EndUserText.label: 'FTamanho Lote'
@Consumption.hidden : true
0 as FTamanhoLote,

@DefaultAggregation: #SUM
@EndUserText.label: 'Eqtd'
@Consumption.hidden : true
case 
when 
 ( 
    TipoSuprimento  =   'E'
 )  then OrderQuantity
end as Eqtd,

@DefaultAggregation: #SUM
@EndUserText.label: 'Fqtd'
@Consumption.hidden : true
case 
when 
 ( 
    TipoSuprimento  =   'F'
 )  then OrderQuantity
end as Fqtd,

@DefaultAggregation: #FORMULA
@AnalyticsDetails.query.formula: 'CASE WHEN $projection.Frete < 0 THEN ( $projection.Eqtd / 6 ) ELSE ( $projection.Eqtd / 5 ) END'
@EndUserText.label: 'EQuantidade Pedido'
@Consumption.hidden : true
@AnalyticsDetails.query.decimals: 2
0 as EQuantidadePedido,
@DefaultAggregation: #FORMULA
@AnalyticsDetails.query.formula: 'CASE WHEN $projection.Frete < 0 THEN ( $projection.Fqtd / 6 ) ELSE ( $projection.Fqtd / 5 ) END'
@EndUserText.label: 'FQuantidade Pedido'
@Consumption.hidden : true
@AnalyticsDetails.query.decimals: 2
0 as FQuantidadePedido,


// Free
Customer,
CustomerName,
Material,
@EndUserText.label: 'Data'
CreationDate,
@EndUserText.label: 'Tipo de Suprimento'
TipoSuprimento,
Nivel,
Status,
@EndUserText.label: 'Data do Calculo de custos'
DateCalcCust
}
 where 
 ( 
    Nivel   =   '#'
 ) 
 and 
 ( 
    Status  =   'FR'
 ) 

Veja abaixo o relatório ordem a ordem com o cliente a receita, seus impostos e custos.

No final o percentual de margem e a sinaleira para uma tomada de decisão veloz.

E com esses dados foi possível montar uma análise para fazer gestão dos pedidos na chegada a empresa e não precisar aguardar o fechamento contábil para tomar uma ação tardia.