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