Enterprise Resource Planning Blogs by Members
Gain new perspectives and knowledge about enterprise resource planning in blog posts from community members. Share your own comments and ERP insights today!
cancel
Showing results for 
Search instead for 
Did you mean: 
former_member638291
Participant
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.
Labels in this area