OrderToCash
Arquivo SQL de origem - Google Cloud Cortex Data Foundation
Google Cloud Cortex Foudantion - Cortex Reporting - (s4) - OrderToCash
Visão geral
Esta visão fornece um conjunto de dados abrangente para relatórios de sistemas SAP, combinando dados de Pedidos de Venda, Entregas, Faturamento e tabelas de dados mestres. Ela oferece insights sobre o desempenho de vendas, atendimento de pedidos e comportamento do cliente.
Fontes de Dados
| Tabela/Visão | Descrição |
|---|---|
| SalesOrders | Dados do cabeçalho do pedido de venda, incluindo números de pedido, datas, quantidades, preços e informações do cliente. |
| Deliveries | Dados relacionados à entrega, como números de entrega, datas, quantidades e status. |
| Billing | Dados do documento de faturamento, incluindo números de fatura, datas, valores e informações fiscais. |
| CustomersMD | Dados mestres do cliente, incluindo números de cliente, nomes, endereços e informações de contato. |
| MaterialsMD | Dados mestres do material, incluindo números de material, descrições e informações de preço. |
| SalesOrganizationsMD | Dados mestres da organização de vendas, incluindo nomes e endereços da organização de vendas. |
| DistributionChannelsMD | Dados mestres do canal de distribuição, incluindo nomes e descrições do canal de distribuição. |
| CountriesMD | Dados mestres do país, incluindo nomes e códigos do país. |
| OneTouchOrder | Dados do pedido one-touch, incluindo números do pedido e detalhes do item. |
| DivisionsMD | Dados mestres da divisão, incluindo nomes e descrições da divisão. |
| DeliveryBlockingReasonsMD | Dados mestres dos motivos de bloqueio de entrega, incluindo motivos de bloqueio e descrições. |
| BillingBlockingReasonsMD | Dados mestres dos motivos de bloqueio de faturamento, incluindo motivos de bloqueio e descrições. |
Casos de Uso
- Análise de desempenho de vendas
- Rastreamento de atendimento de pedidos
- Gerenciamento de relacionamento com o cliente
- Gestão de estoque
- Relatórios financeiros
Lógica da Query
A visão combina dados de várias tabelas usando junções com base em campos comuns, como números de pedido de venda e números de material. Ela calcula várias métricas e agrega dados para fornecer uma visão abrangente do desempenho de vendas e entrega.
Possível relação com transações SAP
| Código da Transação | Descrição da Transação |
|---|---|
| VA01 | Criar Pedido de Venda |
| VA02 | Alterar Pedido de Venda |
| VL01N | Criar Entrega |
| VL02N | Alterar Entrega |
| VF01 | Criar Fatura |
| VF02 | Alterar Fatura |
Linhagem de dados
Em desenvolvimento
Campos da Tabela/Visão
| Campo | Descrição do Campo | Descrição no Dicionário SAP (Em breve) |
|---|---|---|
| SalesOrders.Client_MANDT | Client ID | |
| Deliveries.Delivery_VBELN | Delivery Number | |
| Deliveries.DeliveryItem_POSNR | Delivery Item Number | |
| SalesOrders.SalesDocument_VBELN | Sales Document Number | |
| SalesOrders.Item_POSNR | Sales Order Item Number | |
| Deliveries.ActualQuantityDelivered_InSalesUnits_LFIMG | Actual Quantity Delivered in Sales Units | |
| Deliveries.BaseUnitOfMeasure_MEINS | Base Unit of Measure | |
| Deliveries.NetPrice_NETPR | Net Price | |
| Deliveries.NetValueInDocumentCurrency_NETWR | Net Value in Document Currency | |
| Deliveries.SdDocumentCurrency_WAERK | Sales Document Currency | |
| Deliveries.DeliveryBlock_DocumentHeader_LIFSK | Delivery Block in Document Header | |
| Deliveries.BillingBlockInSdDocument_FAKSK | Billing Block in Sales Document | |
| Deliveries.Date__proofOfDelivery___PODAT | Date of Proof of Delivery | |
| Deliveries.BillingDateForBillingIndexAndPrintout_FKDAT | Billing Date for Billing Index and Printout | |
| Deliveries.SalesOrderNumber_VGBEL | Sales Order Number | |
| Deliveries.DeliveryDate_LFDAT | Delivery Date | |
| Deliveries.ActualGoodsMovementDate_WADAT_IST | Actual Goods Movement Date | |
| SalesOrders.ExchangeRateType_KURST | Exchange Rate Type | |
| SalesOrders.Requesteddeliverydate_VDATU | Requested Delivery Date | |
| SalesOrders.CumulativeOrderQuantity_KWMENG | Cumulative Order Quantity | |
| SalesOrders.BaseUnitofMeasure_MEINS | Sales Unit Measure | |
| SalesOrders.Netprice_NETPR | Net Price | |
| SalesOrders.Currency_WAERK | Sales Order Document Currency | |
| SalesOrders.ShippingReceivingPoint_VSTEL | Shipping Receiving Point | |
| SalesOrders.SoldToParty_KUNNR | Sold-to Party | |
| SalesOrders.SoldToPartyItem_KUNNR | Sold-to Party Item | |
| SalesOrders.SoldToPartyItemName_KUNNR | Sold-to Party Item Name | |
| SalesOrders.ShipToPartyItem_KUNNR | Ship-to Party Item | |
| SalesOrders.ShipToPartyItemName_KUNNR | Ship-to Party Item Name | |
| SalesOrders.BillToPartyItem_KUNNR | Bill-to Party Item | |
| SalesOrders.BillToPartyItemName_KUNNR | Bill-to Party Item Name | |
| SalesOrders.PayerItem_KUNNR | Payer Item | |
| SalesOrders.PayerItemName_KUNNR | Payer Item Name | |
| SalesOrders.SoldToPartyHeader_KUNNR | Sold-to Party Header | |
| SalesOrders.SoldToPartyHeaderName_KUNNR | Sold-to Party Header Name | |
| SalesOrders.ShipToPartyHeader_KUNNR | Ship-to Party Header | |
| SalesOrders.ShipToPartyHeaderName_KUNNR | Ship-to Party Header Name | |
| SalesOrders.BillToPartyHeader_KUNNR | Bill-to Party Header | |
| SalesOrders.BillToPartyHeaderName_KUNNR | Bill-to Party Header Name | |
| SalesOrders.PayerHeader_KUNNR | Payer Header | |
| SalesOrders.PayerHeaderName_KUNNR | Payer Header Name | |
| SalesOrders.SalesOrganization_VKORG | Sales Organization | |
| SalesOrders.DistributionChannel_VTWEG | Distribution Channel | |
| SalesOrders.OverallDeliveryStatus_LFGSK | Overall Delivery Status | |
| SalesOrders.ListPrice | List Price | |
| SalesOrders.AdjustedPrice | Adjusted Price | |
| SalesOrders.InterCompanyPrice | Intercompany Price | |
| SalesOrders.Discount | Discount | |
| SalesOrders.ConfirmedOrderQuantity_BMENG | Confirmed Order Quantity | |
| SalesOrders.CreationDate_ERDAT | Creation Date | |
| SalesOrders.DocumentCategory_VBTYP | Document Category | |
| SalesOrders.PrecedingDocCategory_VGTYP | Preceding Document Category | |
| SalesOrders.Documentnumberofthereferencedocument_VGBEL | Document Number of the Referenced Document | |
| SalesOrders.ReferenceItem_VGPOS | Reference Item | |
| SalesOrders.RejectionReason_ABGRU | Rejection Reason | |
| CustomersMD.CustomerNumber_KUNNR | Customer Number | |
| CustomersMD.Name1_NAME1 | Customer Name 1 | |
| CustomersMD.Name2_NAME2 | Customer Name 2 | |
| CustomersMD.City_ORT01 | City | |
| CustomersMD.CountryKey_LAND1 | Country Key | |
| CustomersMD.PostalCode_PSTLZ | Postal Code | |
| CustomersMD.CustomerRegion_REGIO | Customer Region | |
| CustomersMD.Address_ADRNR | Customer Address | |
| CustomersMD.LanguageKey_SPRAS | Customer Language | |
| MaterialsMD.MaterialNumber_MATNR | Material Number | |
| MaterialsMD.MaterialType_MTART | Material Type | |
| MaterialsMD.Division_SPART | Division | |
| MaterialsMD.MaterialCategory_ATTYP | Product Category | |
| MaterialsMD.Brand_BRAND_ID | Brand | |
| MaterialsMD.MaterialText_MAKTX | Material Description | |
| MaterialsMD.Language_SPRAS | Language | |
| Billing.ActualBilledQuantity_FKIMG | Actual Billed Quantity | |
| Billing.BillingDocument_VBELN | Billing Document Number | |
| Billing.Rebate | Rebate | |
| Billing.TaxAmount_MWSBK | Tax Amount | |
| Billing.Volume_VOLUM | Volume | |
| Billing.GrossWeight_BRGEW | Gross Weight | |
| Billing.BillingDate_FKDAT | Billing Date | |
| Billing.BillingItem_POSNR | Billing Item Number | |
| Billing.NetWeight_NTGEW | Net Weight | |
| Billing.NetValue_NETWR | Billing Net Value | |
| Billing.SdDocumentCurrency_WAERK | Billing Document Currency | |
| SalesOrganizationsMD.SalesOrgName_VTEXT | Sales Organization Name | |
| DistributionChannelMD.DistributionChannelName_VTEXT | Distribution Channel Name | |
| CountriesMD.CountryName_LANDX | Country Name | |
| OneTouchOrder.OneTouchOrderCount | One Touch Order Count | |
| OneTouchOrder.VBAPSalesDocument_VBELN | One Touch Orders | |
| DivisionsMD.DivisionName_VTEXT | Division Description | |
| SalesOrders.OverallProcessingStatus_GBSTK | Overall Processing Status | |
| TVLST.DeliveryBlockReason_VTEXT | Delivery Block Reason Description | |
| TVFST.BillingBlockReason_VTEXT | Billing Block Reason Description | |
| CASE SalesOrders.OverallProcessingStatus_GBSTK | ||
| WHEN 'A' THEN 'Not yet Processed' | ||
| WHEN 'B' THEN 'Partially Processed' | ||
| WHEN 'C' THEN 'Completely Processed' | ||
| ELSE 'Not relevant' | ||
| END | Return Order Description | |
| Deliveries.ActualQuantityDelivered_InSalesUnits_LFIMG * Deliveries.NetPrice_NETPR | Delivered Value | |
| Deliveries.ActualQuantityDelivered_InSalesUnits_LFIMG * Deliveries.NetValueInDocumentCurrency_NETWR | Value | |
| SalesOrders.CumulativeOrderQuantity_KWMENG * SalesOrders.Netprice_NETPR | Sales Order Net Value | |
| SUM(Deliveries.ActualQuantityDelivered_InSalesUnits_LFIMG * Deliveries.NetPrice_NETPR) OVER(PARTITION BY Deliveries.DeliveryItem_POSNR, Deliveries.Delivery_VBELN) | Delivered Net Value | |
| IF(Deliveries.Date__proofOfDelivery___PODAT > Deliveries.DeliveryDate_LFDAT, | ||
| 'Delayed', | ||
| 'NotDelayed') | Late Deliveries | |
| IF(Deliveries.DeliveryBlock_documentHeader_LIFSK IS NULL | ||
| AND Deliveries.BillingBlockInSdDocument_FAKSK IS NULL, | ||
| 'NotBlocked', | ||
| 'Blocked' ) | Blocked Sales Order | |
| COUNT(DISTINCT SalesOrders.SalesDocument_VBELN) OVER(PARTITION BY SalesOrders.Client_MANDT) | Total Orders | |
| COUNT(SalesOrders.Item_POSNR) OVER(PARTITION BY SalesOrders.Client_MANDT) | Total Order Items | |
| COUNT(Deliveries.DeliveryItem_POSNR) OVER(PARTITION BY SalesOrders.Client_MANDT) | Total Deliveries | |
| SUM(SalesOrders.CumulativeOrderQuantity_KWMENG ) OVER(PARTITION BY SalesOrders.Client_MANDT, SalesOrders.SalesDocument_VBELN, SalesOrders.Item_POSNR) | SalesOrderQuantity | |
| SUM(SalesOrders.Netprice_NETPR * SalesOrders.CumulativeOrderQuantity_KWMENG) OVER(PARTITION BY SalesOrders.Client_MANDT, SalesOrders.SalesDocument_VBELN, SalesOrders.Item_POSNR) | SalesOrderValue | |
| IF(SalesOrders.DocumentCategory_VBTYP = 'C', | ||
| SalesOrders.SalesDocument_VBELN, | ||
| NULL) | IncomingOrderNum | |
| IF(SalesOrders.CumulativeOrderQuantity_KWMENG = Deliveries.ActualQuantityDelivered_InSalesUnits_LFIMG, | ||
| 'DeliveredInFull', | ||
| 'NotDeliverdInFull') | InFullDelivery | |
| IF(Deliveries.Date__proofOfDelivery___PODAT <= Deliveries.DeliveryDate_LFDAT | ||
| AND SalesOrders.CumulativeOrderQuantity_KWMENG = Deliveries.ActualQuantityDelivered_InSalesUnits_LFIMG, | ||
| 'OTIF', | ||
| 'NotOTIF') | OTIF | |
| SAFE_DIVIDE(SalesOrders.ConfirmedOrderQuantity_BMENG, SalesOrders.CumulativeOrderQuantity_KWMENG ) * 100 | FillRatePercent | |
| IF(SalesOrders.CumulativeOrderQuantity_KWMENG > SalesOrders.ConfirmedOrderQuantity_BMENG, | ||
| 'BackOrder', | ||
| 'NotBackOrder') | BackOrder | |
| IF(Deliveries.ActualQuantityDelivered_InSalesUnits_LFIMG = SalesOrders.CumulativeOrderQuantity_KWMENG | ||
| AND SalesOrders.CumulativeOrderQuantity_KWMENG = Billing.ActualBilledQuantity_FKIMG, | ||
| 'NotOpenOrder', | ||
| 'OpenOrder') | OpenOrder | |
| IF( SalesOrders.DocumentCategory_VBTYP = 'H', | ||
| IF( SalesOrders.PrecedingDocCategory_VGTYP = 'C' AND SalesOrders.ReferenceDocument_VGBEL = SalesOrders.Documentnumberofthereferencedocument_VGBEL | ||
| AND SalesOrders.Item_POSNR = SalesOrders.ReferenceItem_VGPOS, | ||
| 'Returned', | ||
| 'NotReturned'), | ||
| IF( SalesOrders.PrecedingDocCategory_VGTYP = 'M' AND SalesOrders.ReferenceDocument_VGBEL = Billing.DocumentNumberOfTheReferenceDocument_VGBEL | ||
| AND SalesOrders.ReferenceItem_VGPOS = Billing.ItemNumberOfTheReferenceItem_VGPOS | ||
| AND Billing.SalesDocument_AUBEL = SalesOrders.SalesDocument_VBELN | ||
| AND Billing.SalesDocumentItem_AUPOS = SalesOrders.Item_POSNR, | ||
| 'Returned', | ||
| 'NotReturned') ) | ReturnOrder | |
| IF(SalesOrders.RejectionReason_ABGRU IS NOT NULL, | ||
| 'Canceled', | ||
| 'NotCanceled') | CanceledOrder | |
| IF(Deliveries.ActualGoodsMovementDate_WADAT_IST IS NOT NULL, | ||
| TIMESTAMP_DIFF(CAST(CONCAT(Deliveries.Date__proofOfDelivery___PODAT, ' ', Deliveries.ConfirmationTime_POTIM) AS TIMESTAMP), | ||
| CAST(CONCAT(SalesOrders.CreationDate_ERDAT, ' ', SalesOrders.CreationTime_ERZET) AS TIMESTAMP), DAY), NULL) | OrderCycleTimeInDays | |
| IF(Deliveries.Date__proofOfDelivery___PODAT <= Deliveries.DeliveryDate_LFDAT, | ||
| 'DeliveredOnTime', | ||
| 'NotDeliveredOnTime') | OnTimeDelivery |