Margem de contribuição das ordens de venda

[former_member638291]

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.