INTL
Freelancer
보통
외주
원격 가능
Unreceived JIT PO's Tracking Mechanism Creation
예산
$30~$250 USD
예상 기간
1~2주
난이도
보통
기술 스택
SQL
Business Intelligence
Data Analysis
Power Automate
SharePoint Online
Excel Online
AI 분석 요약
이 프로젝트는 JIT PO의 미수신 건을 자동으로 추적하여 기한을 초과한 PO 목록을 생성하고, GPCS NIR 팀에 블라인드 수신을 위한 알림을 보내는 자동화된 메커니즘을 구축하는 것입니다. SQL 기반의 데이터 추출, BI 도구/스프레드시트를 활용한 중앙 트래커 구현, Power Automate를 이용한 자동 알림 및 에스컬레이션 로직 개발 능력이 필요합니다.
프로젝트 원문 설명
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
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
Freelancer에서 원본 확인
원본 보기