供應商支出分析

這個解決方案範例說明如何使用 SAP ERP 或 SAP BDC 來源資料，加速取得供應商支出狀況的洞察資訊。

你可以使用這項功能執行下列操作：

  • 依材質類型最佳化總支出，並追蹤國家/地區集中度。
  • 瞭解隱藏的採購模式，盡量提高採購價值。
  • 運用 AI 發掘新洞察資料。

參考架構

高階參考架構會概略說明提供此使用案例所需的特定來源資料產品。

Cortex Framework 解決方案範例參考架構

圖 1：供應商支出分析解決方案的 Cortex Framework 參考架構範例。

必要資料產品

如要以 SAP ERP 來源資料為基礎建構，必須使用下列Cortex Framework 資料產品：

  • 廠商
  • 購買文件
  • 採購機構
  • Materials
  • 素材資源群組
  • 材質類型

如要以 SAP BDC 來源資料為基礎建構，必須使用下列 BDC 資料產品：

  • Product
  • 訂購單
  • 公司代碼
  • 供應商

程式碼片段

以下程式碼片段提供範例，說明如何根據 Cortex Framework 來源的 SAP ERP 資料產品，或整合 SAP BDC 資料產品後，建構 BigQuery 資料模型來分析供應商支出。

WITH purchasing_items AS (
  SELECT
    client_mandt,
    purchasing_document_number_ebeln,
    item_number_of_purchasing_document_ebelp,
    material_number_matnr,
    plant_werks,
    po_quantity_menge,
    net_order_value_in_po_currency_netwr,
    gross_order_value_in_po_currency_brtwr,
    delivery_completed_flag_elikz
  FROM
    <YOUR_PROJECT_ID>.<YOUR_CORTEX_DATA_PRODUCTS_DATASET>.purchasing_document_items
),

purchasing_headers AS (
  SELECT
    client_mandt,
    purchasing_document_number_ebeln,
    vendor_account_number_lifnr,
    purchasing_organization_ekorg,
    purchasing_document_date_bedat as order_date_bedat,
    currency_key_waers
  FROM
    <YOUR_PROJECT_ID>.<YOUR_CORTEX_DATA_PRODUCTS_DATASET>.purchasing_document_headers
),

vendor_data AS (
  SELECT
    client_mandt,
    account_number_of_vendor_or_creditor_lifnr as vendor_account_number_lifnr,
    name1_name1 as vendor_name_name1,
    country_key_land1,
    central_deletion_flag_for_master_record_loevm
  FROM
    <YOUR_PROJECT_ID>.<YOUR_CORTEX_DATA_PRODUCTS_DATASET>.vendors_md
),

material_data AS (
  SELECT
    client_mandt,
    material_number_matnr,
    material_text_maktx,
    material_type_mtart,
    material_group_matkl
  FROM
    <YOUR_PROJECT_ID>.<YOUR_CORTEX_DATA_PRODUCTS_DATASET>.materials_md
),

purchasing_orgs AS (
  SELECT
    client_mandt,
    purchasing_organization_ekorg,
    purchasing_organization_text_ekotx
  FROM
    <YOUR_PROJECT_ID>.<YOUR_CORTEX_DATA_PRODUCTS_DATASET>.purchasing_organizations_md
)

SELECT
  i.client_mandt,
  i.purchasing_document_number_ebeln,
  i.item_number_of_purchasing_document_ebelp,
  h.vendor_account_number_lifnr,
  v.vendor_name_name1,
  v.country_key_land1,
  i.material_number_matnr,
  m.material_text_maktx,
  m.material_type_mtart,
  m.material_group_matkl,
  h.purchasing_organization_ekorg,
  o.purchasing_organization_text_ekotx,
  
  -- Baseline financial & temporal fields
  h.order_date_bedat,
  i.po_quantity_menge,
  i.net_order_value_in_po_currency_netwr,
  i.gross_order_value_in_po_currency_brtwr,
  i.net_order_value_in_po_currency_netwr as spend_usd,
  
  -- Active vendor indicator (True if not deleted)
  CASE 
    WHEN v.central_deletion_flag_for_master_record_loevm = 'X' THEN false
    ELSE true
  END as active_vendor_indicator,
  
  -- Purchase Price Variance (PPV) - Net versus Gross baseline/target difference
  (i.gross_order_value_in_po_currency_brtwr - i.net_order_value_in_po_currency_netwr) as purchase_price_variance_ppv,
  
  -- Delivery and overdues tracking
  i.delivery_completed_flag_elikz,
  CASE 
    WHEN i.delivery_completed_flag_elikz != 'X' THEN i.net_order_value_in_po_currency_netwr
    ELSE 0
  END as open_po_net_amount,
  
  CASE 
    WHEN i.delivery_completed_flag_elikz != 'X' AND h.order_date_bedat < DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) THEN true
    ELSE false
  END as is_overdue,
  
  -- Efficiency & Performance metrics
  SAFE_DIVIDE(i.net_order_value_in_po_currency_netwr, i.po_quantity_menge) as average_spend_per_unit,
  (i.gross_order_value_in_po_currency_brtwr - i.net_order_value_in_po_currency_netwr) as gross_net_variance,
  1 as line_item_count
  
FROM
  purchasing_items i
LEFT JOIN
  purchasing_headers h
  ON i.client_mandt = h.client_mandt
  AND i.purchasing_document_number_ebeln = h.purchasing_document_number_ebeln
LEFT JOIN
  vendor_data v
  ON h.client_mandt = v.client_mandt
  AND h.vendor_account_number_lifnr = v.vendor_account_number_lifnr
LEFT JOIN
  material_data m
  ON i.client_mandt = m.client_mandt
  AND i.material_number_matnr = m.material_number_matnr
LEFT JOIN
  purchasing_orgs o
  ON h.client_mandt = o.client_mandt
  AND h.purchasing_organization_ekorg = o.purchasing_organization_ekorg

啟用 AI 代理程式

在 BigQuery 中建立供應商支出分析資料模型後，即可建構自訂資料代理程式。這樣一來，您就能直接使用自然語言查詢供應商支出指標，不必使用複雜的 SQL。