Monday, 28 October 2019 13:45

Margem de contribuição das ordens de venda

Written by https://blogs.sap.com/2019/10/29/margem-de-contribuicao-das-ordens-de-venda/
Rate this item
(0 votes)

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.

Read 34 times

Leave a comment

Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.