Step 1 — Import source files
Upload the Odoo export and the audit count. The app will pivot, vlookup, and apply the adjustment rules automatically.
Downloads a ready-to-upload Qty + Audit pair covering every scenario.
Used to prefix every downloaded file name (text before "/" is used, e.g. "SD/Stock" → SD_). Also passed to Phase 2.
Location is required before processing & download.
Qty per Location per Lot
Odoo export — sheet: Raw Data
Audit Qty
Sheet: Audit Qty (Product, Sistem, Fisik, Selisih)
Step 2 — Generate Final Result
Runs the pivot, vlookup, and per-row formulas (Status / Indicator / Variance / Result).
Tips & Logic
- The logic (confirmed against the Guide sheet):
- • Adj Minus (Audit Qty negative = shortage): deduct from lots top-to-bottom. Earlier lots get fully zeroed out ("Next") until the running total covers the shortage; the lot where it tips over gets a partial deduction ("Stop") and keeps the leftover.
- • Adj Plus (Audit Qty positive = excess): skip every lot except the last one of that SKU group, and dump the entire excess onto that last lot ("Adj").
- Workflow:
- 1. In Odoo, group by Location then Product, sort by oldest Exp Date.
- 2. Export Location with Lot and Exp Date — this is the "Qty per Location per Lot" file.
- 3. The app pivots it into one row per Product + Lot + Exp Date.
- 4. Upload your "Audit Qty" file (Product, Sistem, Fisik, Selisih).
- 5. The app VLOOKUPs Audit Qty into the pivot and classifies Adj Plus / Adj Minus.
- Tip: filter out negative-lot rows in Odoo first, or rely on the app's positive-qty filter in the pivot step.