Spotlight PQGC

PepsiCo North America · Databricks pipeline · Order fulfilment

Spotlight PQGC, in plain English

This code keeps the Spotlight app current. On each run it copies the latest orders, deliveries, trucks, production plans and stock levels out of SAP. It works out which customer orders are at risk of shipping short and loads the answers into the database behind the app. When planners act on a risk in Spotlight, their changes go back to SAP.

SAP S/4→ Databricks lakehouse→ Azure SQL database→ Spotlight app→ back to SAP
27notebooks 12,462lines of code 26SAP sources 59Databricks tables 21app tables, procedures & files

The one-minute version

What problem does it solve?

Picture a beverage and food business with dozens of plants. Customers place orders, trucks move product between plants, and factories make more. Every day someone has to answer one question: will each order ship complete and on time, and if not, which ones need attention first?

SAP holds all the raw facts, spread across dozens of tables. This project gathers them, joins them up, and turns them into simple answers for every order: a fill rate, a dollar amount at risk, a confidence score and a traffic light.

It runs as a chain of Databricks notebooks. Each notebook does one job and hands a table to the next. The final tables are copied into an Azure SQL database, which is what the Spotlight app reads. Planners act in the app, and a writeback step sends their changes to SAP, so the next run starts from the updated picture.

Nothing here has a screen of its own. The Spotlight app's front end, the job schedule and the SQL stored procedures are not in this folder. This guide covers the data engine that feeds them.

End to end · 8 stages

The loop, stage by stage

Each run moves left to right through these stages. Select a stage to see what it does and which notebooks do the work.

    Stage 08 feeds SAP, and SAP feeds stage 02 on the next run

    Walk the loop in 3DA three.js model of the whole process, with a guided tour.

    A worked example

    Follow one order through the system

    Illustrative example The order, plant and figures below are invented to show the mechanics. The rules applied at each step are the real ones from the code.

    1. SAP

      A customer orders 600 cases

      A retailer's distribution centre orders 600 cases of a sports drink, shipping from plant 8123 on Thursday. SAP records sales order 0012345678 and confirms it can promise 540 of those cases.

      Order type ZOR · ordered 600 cs · ATP 540 cs

    2. 02

      The next run picks up the change

      The order-line extract sees the order changed after its last watermark and copies the line: 600 cases ordered (10 pallets of 60), 540 promised, 60 at risk. Once the warehouse creates a delivery and a truck is booked, the delivery and shipment extracts copy those too.

      pgt_whd_ordr_item_extrct_tbl → pgt_whd_dlvry_extrct_tbl → pgt_whd_shpmt_extrct_tbl

    3. 03

      It becomes one row with a status

      The order-delivery detail joins the line to its delivery, truck, carrier and customer hierarchy. A delivery exists and there is no block, so the status is RLSD (released). Until the truck actually leaves, the ship date is the planned goods-issue date.

      ord_dely_detl_pgt · sap_stts = RLSD

    4. 04

      The plant's coverage is scored

      Plant 8123 has 300 cases free and 100 on quality hold, which count as 75. Another 120 cases are on a truck from a sister plant (trusted at 80%) and 200 are planned for production (trusted at 50%). Measured against everything due out on Thursday, the plant scores 85% for that day.

      plant_matnr_fcs_dly · FCS_PCT for Thursday = 85

    5. 05

      The order gets its numbers and a light

      Fill rate is 90% (540 ÷ 600). Revenue at risk is $1,080 (60 cases × $18). Confidence is 85, taken from the plant's Thursday score. The order is short, so it can't be green or yellow. The plant's "max cut & ship" setting decides between orange and red.

      fact_order_pqgc → PQGC_SPOTLIGHT.FACT_ORDER_PQGC_DLT → FO_DLT_MRGE()

    6. 07

      It shows up in Spotlight

      Stored procedures merge the changed orders into the app's tables, and the "Last Spotlight Refresh" clock moves forward. A planner filtering for orange and red orders now sees this one.

      PQGC_SPOTLIGHT.Timestamp

    7. 08

      A planner acts and SAP receives the change

      The planner's change goes into the app's writeback queue. The writeback notebook turns the queue into a CSV file for SAP S/4. Once SAP applies it, the fast-refresh notebook pushes the updated order back to the app, and the next full run starts the loop again.

      PQGC_SPOTLIGHT.Writeback → SL_S4_Writeback_<time>.csv → SAP S/4

    Sequence

    How a run unfolds

    The job definition (Databricks Workflow) isn't in this folder. This order is worked out from what each notebook reads and writes, so a notebook always runs after the ones that feed it. Steps 04–06 can run in parallel.

    Main refresh · process "PQGC_INCREMENTAL"

    1. Note SAP data freshnessTimer-stamp notebook in start mode records the SAP data-pull time. update timer stamp ASQL
    2. Check sources, alert if stalePQGC_BASE_TABLE_VALIDATION_CEN, _DF, PQGC_BASE_TABLE_VALIDATION
    3. Tidy the big tables (pre)At most every 6 hours. Optimize drvd tables stage=pre
    4. Extract order lines, deliveries, purchase & transfer ordersPGT_WHD_ORDR_ITEM…, PGT_WHD_DLVRY…, PGT_WHD_PO…
    5. Extract truck shipments, merge the change listsNeeds step 04. PGT_WHD_SHPMT…
    6. Refresh production and stock on handPGT_WHD_PROD_ORDR…, PGT_WHD_PROD_PROCESS_ORDR…, MATDOC_INCREMENTAL
    7. Stitch order-delivery detailPasses its timestamp to later tasks. Ord_dely_detl_pgt_cur
    8. Score plant coverage for 14 daysSends results to the app database. plant_matnr_fcs_dly
    9. Rate orders and order linesSends results to the app database and runs its merge procedures. PQGC_FO_CUR, PQGC_FOL_CUR
    10. Build the supply listplnt_mtrl_scs_rptng
    11. Score transfers with MLPQGC_ML_SIT_Predictions, PQGC_ML_SOO_SPL_Predictions
    12. Run the app's stored proceduresEXEC_SP_ASQL with the job's sp_list
    13. Mark "Last Spotlight Refresh"update timer stamp ASQL process=Full
    14. Tidy the big tables (post)Optimize drvd tables stage=post

    Writeback · process "Writeback"

    1. Export planners' changes for SAPSAPWritebackStatus type=ATP or BEAM
    2. Mark "Last SAP Writeback"update timer stamp ASQL process=Writeback

    Between full runs

    1. Fast order refreshPushes SAP's latest order status straight to the app. UPDATE_FO

    When something fails

    Most notebooks send a failure alert through an email web service, naming the notebook, environment and error. Alerts go to a single contractor address. The shipment extract, ML notebooks and fast refresh send no alert.

    Inventory

    Every notebook, explained

    All 27 files in the project. Open one to see what it does step by step and which tables it reads and writes. Search finds table names too.

    Data map

    Where the data lives

    Data starts in SAP (left), is shaped in Databricks (middle), and ends up in the app's database (right). Pick a notebook to highlight what it reads and what it writes.

    readswrites or runs

    SAP sources

    SAP foundation views in uc_<env>_snt_fdn_01. Read-only.

    Databricks tables

    Delta tables in schema pbus_spotlight, plus ML model files.

    Spotlight app side

    Azure SQL schema PQGC_SPOTLIGHT, plus the writeback file for SAP.

    Glossary

    Words you'll see

    Codes and abbreviations from the notebooks. Entries marked "inferred from the code" are not spelled out anywhere in the code; they are a best reading from how each term is used.

    Review notes

    Things to fix

    Problems found while reading the code. Each one was checked against the line shown.

    Urgent

    The app database password is written in plain text

    The Azure SQL server, username and password are typed directly into the code. The lines that would read them from the secret store are commented out. One notebook also prints the full connection options, password included, into the job log on every run. Anyone with read access to the repo or the job logs has the login. Rotate the password and move the login into a Databricks secret scope.

    Common/ENV_VARIABLES.py:29–32 · writeback/PBNA_COMMON_PROCESS.py:205–208 · plant_matnr_fcs_dly.py:2421 · Common/Email.py (alert URL carries an access signature)

    Urgent

    Failed runs are never recorded in the app's clock

    The SQL that marks a run as failed has a stray comma before WHERE, so it errors every time. When a refresh or writeback fails, the "last run" status planners see never shows the failure.

    writeback/update timer stamp ASQL.py:74

    Fix soon

    Order-level revenue at risk uses the wrong formula

    It computes ORD_QTY - ATP_QTY * NET_PRICE, but should compute (ORD_QTY - ATP_QTY) * NET_PRICE. The line-level figure is correct; the order-level figure is not.

    PQGC_FO_CUR.py:556–558

    Fix soon

    Coverage maths counts Day 8 supply twice

    The running supply total for Day 9 adds Day 8 twice, which makes the plant look better supplied from Day 9 onward.

    plant_matnr_fcs_dly.py:598

    Fix soon

    Production-order supply comes from a table nothing fills

    The coverage score reads production-order (PPO) quantities from plnt_mtrl_sply_typ_scs_cc, which no notebook in this folder writes. PPO supply is probably empty or stale.

    plant_matnr_fcs_dly.py:607

    Fix soon

    The purchase/transfer extract never moves its watermark forward

    Each run carries the old watermark forward but never sets a new one. Every run therefore reprocesses everything changed since 1 Jan 2025, and the runs get slower over time.

    PGT_WHD_PO_EXTRCT_TBL_SPLTGHT.py:88–111, 343–355

    Fix soon

    A run-control update has no WHERE clause

    The order-delivery notebook overwrites the "last processed" time on every row of its run table, not just its own.

    Ord_dely_detl_pgt_cur.py:1018–1023

    Fix soon

    Plant tolerance lookup ignores the plant

    The join that picks a plant's "max cut & ship" threshold compares a column with itself (cf.featid=cf.featid), which is always true. Every location gets the same threshold, which can change orange and red lights.

    PQGC_FO_CUR.py:822

    Tidy up

    Gaps in failure alerts

    The shipment extract has no error handling at all. The order-header alert is commented out. The writeback notebook's alert says "MATDOC_INCREMENTAL FAILED", and the process-order alert names the wrong notebook. All alerts go to one contractor's address.

    PGT_WHD_SHPMT_EXTRCT_TBL_SPLTGHT.py · PQGC_FO_CUR.py:1072 · writeback/SAPWritebackStatus.py:145 · Common/Email.py

    Tidy up

    ML predictions are produced but never used

    The two ML notebooks write mlpqgcsit and mlpqgcsoospl, but nothing in this folder reads them. Confidence scores still use the fixed 80 / 60 / 50 / 40 weights.

    ML/PQGC_ML_SIT_Predictions.py:328 · ML/PQGC_ML_SOO_SPL_Predictions.py:227

    Tidy up

    Notebooks depend on one person's workspace folder

    Shared files are loaded with %run from a contractor's personal workspace path. If that account is removed, every notebook breaks. Use repo-relative paths.

    Most notebooks, first cells