Unreceived JIT PO's Tracking Mechanism Creation

Automated JIT POs Tracking System Development
Our goal is for all last mile sites to achieve 95% receiving compliance, and to reach that target as quickly as possible. As an L4 on the GPCS-AMER team you are being assigned a task to create a tracking process for all unreceived JIT PO's 4 days past the need by date so they can be blind received in alignment with the AMZL_RACI_Final 3.0 task22. Goal: Create a mechanism/tool to alert our team on Monday-Wednesday of all overdue unreceived PO’s. This mechanism/tool should gather the unreceived PO’s, creating a list for the GPCS NIR team to receive. Objective

Build a repeatable, automated mechanism (Mon–Wed) that:

Identifies JIT POs ≥4 days past Need-By Date
Filters unreceived / partially received POs
Produces a central action queue
Alerts GPCS NIR team for blind receiving
Tracks completion and drives accountability + compliance
Mechanism Design (Simple + Scalable)
1. Data Pull (Single Source of Truth)

Run daily (Mon–Wed) via SQL / BI tool.

SELECT
PO_Number,
Site_Code,
Vendor_Name,
Need_By_Date,
DATEDIFF(day, Need_By_Date, CURRENT_DATE) AS Days_Past_Due,
Receipt_Status,
PO_Type,
Region
FROM PO_Database
WHERE
PO_Type = 'JIT'
AND Receipt_Status IN ('Open', 'Partially Received')
AND Need_By_Date <= CURRENT_DATE - 4
AND Region = 'AMER'
ORDER BY Days_Past_Due DESC;

Key points:

Includes Partially Received (often missed but still non-compliant)
Focused only on actionable backlog
2. Central Tracker (Core of the Mechanism)

Host in Excel Online / SharePoint / Google Sheets

Required Columns:

| PO # | Site | Vendor | NBD | Days Overdue | Receipt Status | Owner | Action Status | Last Updated | Notes |

Action Status Dropdown:
Pending
Assigned
Blind Received
Investigating
Invalid / Cancelled

Owner column is critical — assigns each PO to a NIR team member.

3. Automated Alert (Mon–Wed Only)

Set up via:

Power Automate / Outlook rule / BI subscription
Email Trigger Time:
9 AM local (Mon, Tue, Wed)
Email Content:

Subject:
AMER JIT POs ≥4 Days Past NBD – Action Required

Body:

Total overdue POs
Change vs previous day
Top 5 sites with highest backlog
Tracker link
4. Daily Execution Loop (Drives Behavior)

Each day, track:

Total overdue POs
POs completed (blind received)
Remaining backlog
Example:
Day Overdue POs
Monday 300
Tuesday 190
Wednesday 85

This creates visible progress + pressure

5. Escalation Logic (Built-In Accountability)

Run on Wednesday after final pull

Escalate if:
Site has >10 overdue POs, OR
Any PO is ≥7 days overdue
Escalation Targets:
Site Ops Manager
Regional Ops
GPCS Leadership
6. Compliance Tracking (Tie to Goal)
KPI:
Receiving Compliance % =
(On-Time Received POs / Total JIT POs) * 100
Supporting Metrics:
Total overdue (4+ days)
Aging buckets (4–6, 7–10, 10+)
Avg days to blind receive
Repeat offender sites