# AI Database Memory — KEHE DC45 (DB2/AS400)

This folder is intended to be a **persistent schema “memory pack”** for query building and debugging.

- **Primary human reference**: `AI_DB_MEMORY.md` (this file)
- **Full source schema notes (verbatim copy)**: `KEHE_DC45_AI_SCHEMA.source.md`
- **Raw metadata export**: `Tables.source.csv`
- **Machine-readable catalog (core tables)**: `AI_DB_TABLE_CATALOG.core.json`
- **Diagrams**: see the images in your project `assets/` folder (created when you dropped screenshots into chat)

---

## Platform & connection

- **Platform**: DB2 for IBM i / AS400
- **DSN**: `TOLSE`

### Schemas

- **NTS45**: DC45 Ontario/Rancho Cucamonga (primary dev schema)
- **NTS41 / NTS19 / NTS35 / NTS##**: other distribution centers
- **NTS25Q**: fiscal calendar (shared across all DCs) — `KKDAILY`
- **TOLENT**: enterprise-level tables (door/pallet scans, invoice log)

### Critical portability rule (do not violate)

All **NTS## schemas share identical table and column names**. Porting queries from one DC to another generally requires changing **only the schema prefix**.

Example:

```sql
NTS45.FHUL04.UNUSERID = NTS45.FOP32.EIUSERID
NTS41.FHUL04.UNUSERID = NTS41.FOP32.EIUSERID
```

Cross-DC comparisons are valid when joining the same tables across different `NTS##` prefixes.

---

## Table roles (what each table is “for”)

### ULL (Unit Load) — history vs current/open

- **`FHUL04`**: ULL activity **history** (very large; filter early)
- **`FUL04`**: ULL activity **current/open**
- **`FHUL01`**: ULL header/detail **history** (item/location/order detail per ULL)
- **`FUL01`**: ULL header/detail **current/open**
- **`FHUL02`**: unit load movement / replenishment **history**
- **`FUL02`**: unit load movement / replenishment **current/open**

### Picking / replenishment movement

- **`F262H`**: pick move history
- **`F262`**: pick move current/open
- **`F262PLM`**: user zone assignment mapping (e.g. `PLMUSER`, `PLMFRLOC`)

### Orders

- **`F263H`**: order line history (pick tote workfile)
- **`F263`**: order line current/open
- **`F260H`**: pick history summary (order header-ish metrics by date/zone)

### Receiving / putaway

- **`F575H`**: putaway history

### Item & inventory

- **`F560`**: item master
- **`F561`**: slot/location inventory (warehouse map)
- **`F562H`**: RF move / inventory adjustment history
- **`F562`**: RF move / inventory adjustment current

### People / labor

- **`FOP32`**: employee master
- **`FOP32L1`**: employee master alternate (often preferred for loader/scan lookups)
- **`FLAACTIVH`**: LMS activity history
- **`FLAACTIV`**: LMS activity current

### Customers & routing

- **`F101C`**: customer/order master
- **`FWH001`**: route/door master

### Enterprise (cross-schema)

- **`TOLENT.FPALLETS`**: door/pallet staging scans
- **`TOLENT.FDS0010`**: invoice transaction log

---

## Key columns (shared identifiers)

> These are the “spines” you join on repeatedly.

### ULL key

- **`UNULL` / `ULULL` / `UMULL`**: ULL number key
  - `FHUL04.UNULL`, `FUL04.UNULL`
  - `FHUL01.ULULL`, `FUL01.ULULL`
  - `FHUL02.UMULL`, `FUL02.UMULL`
  - `F262H.RLULL`, `F262.RLULL` (in `Tables.source.csv` this appears as `RLULL`; in the schema notes it’s referred to as the same ULL key)

### Employee/user keys

- **`UNUSERID` / `EIUSERID`**: ULL activity user → employee
- **`UMUSER` / `UMIUSR` / `EIUSERID`**: replenishment user → employee
- **`LTUSER` / `EIUSERID`**: LMS activity user → employee
- **`LTRUSR` / `EIUSERID`**: loader user → employee
- **`TOLENT.FPALLETS.USERID` / `EIUSERID`**: door scan user → employee

### Item keys

- **`ULIMNO` / `IMNO` / `OPIMNO` / `MPIMNO` / `ILIMNO`**: item number across ULL detail, item master, order lines, slot inventory, and inventory ledger

### Order & customer keys

- **`ULODOC` / `OPSONO`**: order document number
- **`OPCUST` / `ABNO`**: customer number

### Route/wave/pallet keys

- **`UMROUT` / `RTROUT`**: route number
- **`UMWAVE`**: wave number (joins to `TOLENT.FPALLETS.WAVE`)
- **`UMPLID` / `LTRPALLET`**: pallet ID (replen ↔ load data)

### Calendar key

- **`YRMD`**: fiscal calendar date key (joins from multiple operational dates)

---

## Verified joins (authoritative)

### Employee joins

```sql
-- ULL activity user → employee
FHUL04.UNUSERID   = FOP32.EIUSERID
FHUL04.UNUSERID   = FOP32L1.EIUSERID
FUL04.UNUSERID    = FOP32.EIUSERID
FUL04.UNUSERID    = FOP32L1.EIUSERID

-- Replenishment user → employee
FHUL02.UMUSER     = FOP32.EIUSERID
FHUL02.UMIUSR     = FOP32L1.EIUSERID
FUL02.UMIUSR      = FOP32L1.EIUSERID

-- LMS activity user → employee
FLAACTIVH.LTUSER  = FOP32.EIUSERID
FLAACTIVH.LTUSER  = FOP32L1.EIUSERID

-- Loader / door scan users
FLARFLOAD.LTRUSR  = FOP32L1.EIUSERID
FPALLETS.USERID   = FOP32L1.EIUSERID

-- Zone assignment
F262PLM.PLMUSER   = FOP32L1.EIUSERID
```

### ULL “spine” joins (compound join required)

**Do not join by ULL alone**.

```sql
-- HISTORY
FHUL04.UNULL      = FHUL01.ULULL
FHUL04.UNFLOC     = FHUL01.ULLOCN

-- CURRENT/OPEN
FUL04.UNULL       = FUL01.ULULL
FUL04.UNFLOC      = FUL01.ULLOCN
```

### Replenishment ↔ pick move

```sql
FHUL02.UMULL      = F262H.UMULL  -- (schema notes)
FUL02.UMULL       = F262.UMULL   -- (schema notes)
```

> Note: In `Tables.source.csv`, `F262/F262H` show the ULL column as `RLULL`. If you see `UMULL` in legacy query examples, translate it to the actual column name present in your table (`RLULL`) for those tables.

### Replenishment ↔ pallet/load

```sql
FHUL02.UMROUT     = FLARFLOAD.LTRRTE
FHUL02.UMPLID     = FLARFLOAD.LTRPALLET
FUL01.ULPLID      = FHUL02.UMPLID
```

### Item master joins

```sql
FHUL01.ULIMNO     = F560.IMNO
FUL01.ULIMNO      = F560.IMNO
F561.MPIMNO       = F560.IMNO
F562H.IMNO/ILIMNO = F560.IMNO    -- confirm exact column name for your extract
F263.OPIMNO       = F560.IMNO
F263H.OPIMNO      = F560.IMNO
```

### Order / customer joins

```sql
-- ULL detail → order lines
FHUL01.ULODOC     = F263H.OPSONO
FHUL01.ULIMNO     = F263H.OPIMNO
FUL01.ULODOC      = F263.OPSONO
FUL01.ULIMNO      = F263.OPIMNO
FUL01.ULULL       = F263.OPULL

-- Order lines → customer master
F263.OPCUST       = F101C.ABNO
F263H.OPCUST      = F101C.ABNO
```

### Route / door joins

```sql
FUL02.UMROUT      = FWH001.RTROUT
FLARFLOAD.LTRRTE  = FWH001.RTROUT
```

### Staging / door scans

```sql
FPALLETS.WAVE     = FUL02.UMWAVE
FPALLETS.ROUTE    = FUL02.UMROUT
```

### Fiscal calendar joins

```sql
FHUL02.UMCDTE     = KKDAILY.YRMD
FUL02.UMCDTE      = KKDAILY.YRMD
F260H.SHIDTE      = KKDAILY.YRMD
F575H.RCDTE       = KKDAILY.YRMD
```

### Slot inventory (use with caution)

```sql
-- computed concatenation join (fragile)
F262.RLTOZN + RLTOID  = F561.MPZONE + (MPAISL||MPSECT||MPLEVL||MPITEM)
```

---

## Common join patterns (templates)

### ULL activity → item detail

```sql
FHUL04 → FHUL01 → F560
-- (UNULL+UNFLOC → ULULL+ULLOCN → ULIMNO=IMNO)
```

### ULL activity → order → customer

```sql
FHUL04 → FHUL01 → F263H → F101C
-- (UNULL+UNFLOC → ULULL+ULLOCN → ULODOC+ULIMNO=OPSONO+OPIMNO → OPCUST=ABNO)
```

### Replen history → employee performance

```sql
FHUL02 → FOP32L1
-- (UMIUSR = EIUSERID)
```

### Replenishment → fiscal reporting

```sql
FHUL02 → KKDAILY
-- (UMCDTE = YRMD → FISCALWK, FISCALYR, PERIOD)
```

### Door scan → employee + wave

```sql
TOLENT.FPALLETS → FUL02 → FOP32L1
-- (WAVE+ROUTE → UMWAVE+UMROUT → user join)
```

### Open replen board (open moves with slot detail)

```sql
FUL02 → FUL01 → F561 + F560
```

---

## Business rules (authoritative)

### Date / shift logic

- **Work week start (Sunday)**:
  - `CURRENT_DATE - (DAYOFWEEK(CURRENT_DATE) - 1) DAYS`
- **Work week end (Friday)**:
  - `start + 5 DAYS`
- **Overnight (6AM rule)**:
  - If `UMCTME < 600`, treat operational date as `UMCDTE - 1 DAYS`
- **Door scan window (9PM example)**:
  - `LTRLDATE IN (CURRENT_DATE, CURRENT_DATE - 1 DAYS)`
  - and `(LTRLTIME >= '090000' OR LTRLTIME < '040000')`
- **EST → PST rollover**:
  - apply when timestamps are EST but warehouse operations are PST (rule is context-dependent; verify at query time)

### Zone classification (by first char of location)

- **Dry**: `A/B/C/N/R/S`
- **Cooler / Refrigerated**: `J/G/H`
- **Freezer**: `K/L/M`
- **Common ranges**:
  - `S83–S90` North
  - `S17–S38` Dry
  - `J03–J23` Cooler
  - `K42–K57` Freezer

### ULL type classification

- `LEFT(UMULL, 1) = '7'` → full case pallet
- `LEFT(UMULL, 1) = '8'` → tote / line item

### Route classification (warehouse route)

Warehouse route if any is true:

- `UMROUT BETWEEN 800 AND 999`
- or `UPPER(TRIM(F101C.ABALNM)) LIKE '%WHS%'`
- or `... LIKE '%WAREHOUSE%'`
- or `... LIKE '%KEHE%'`

### DC filters (enterprise tables)

- `TOLENT.FPALLETS`: always filter `DC = '45'`
- `FLARFLOAD`: always filter `LTRDC = '45'`
- `TOLENT.FDS0010`: examples show `DSDC = '41'` — **verify** correct DC per use case

### Fixed-length CHAR fields (always TRIM)

TRIM on compare/display:

- `F101C.ABALNM` (customer name)
- `FOP32.EINAME`, `FOP32L1.EINAME`
- `FOP32L1.EIUSERID`
- `FPALLETS.USERID`

### Exclude system users

- `UMUSER NOT LIKE 'CHNWH%'`
- `EINAME NOT LIKE '%DEFAULT%'`

### Integer wrapping for time math

When times are stored as CHAR/DECIMAL-like fields, wrap with `INTEGER()` (or equivalent) before arithmetic to avoid DECIMAL aggregate errors.

### Deduplication

Use:

```sql
ROW_NUMBER() OVER (PARTITION BY <business_key> ORDER BY <timestamp_or_date> DESC)
```

### Sales order padding (OPSONO matching)

`OPSONO` may require left-pad to match source order numbers; reference pattern:

```sql
CROSS JOIN (VALUES (' '),(' 0'),(' 00'),(' 000'),(' 0000'),(' 00000')) AS p(pad)
WHERE (p.pad || DSSONO) = F263.OPSONO
```

### Large table performance rule (critical)

`FHUL04` is very large (43M+ rows in notes). Always filter early by:

- `UNSDTE` **or**
- `UNTIMESTMP`

---

## How to use the machine-readable catalog

- **Core catalog**: `AI_DB_TABLE_CATALOG.core.json`
  - Contains **columns + indexes** for the “core” operational tables most frequently queried.
  - Built by parsing `Tables.source.csv`.

- **Full raw export**: `Tables.source.csv`
  - Use when you need a column not present in the schema notes or when validating data types/lengths/nullability.

---

## Output column naming convention (reporting queries)

These conventions are used for any query intended for Excel / report consumers
(e.g. `inventory_pending_picks.sql`). Reuse these so naming stays consistent
across reports.

### Identity / location

| Output column     | Source / meaning                                         |
| ----------------- | -------------------------------------------------------- |
| `Aisle`           | `OPLOAI`                                                 |
| `Section Area`    | `OPLOZN || OPLOAI || OPLOSC` (internal grouping key)     |
| `Location`        | `OPLOZN || OPLOAI || OPLOSC || OPLOLV || OPLOIT` (slot)  |
| `Route`           | `OPROUT`                                                 |
| `Wave`            | `OPWAVE`                                                 |
| `Stop`            | `OPSTOP`                                                 |
| `Pallet ID`       | `FUL01.ULPLID` (max per ULL)                             |
| `ULL`             | `TRIM(OPULL)` — tote/ULL key                             |
| `UMSTAT Status`   | Decoded from latest `FUL02.UMSTAT` per ULL               |

### Item / quantities

| Output column      | Source / meaning                  |
| ------------------ | --------------------------------- |
| `Item #`           | `TRIM(OPIMNO)`                    |
| `Item Description` | `TRIM(IMDESC)`                    |
| `Qty Ordered`      | `OPOQTY`                          |
| `Qty Shipped`      | `OPSQTY`                          |
| `Qty Short`        | `OPOQTY - OPSQTY`                 |
| `Inventory`        | `IMQOH` (item master on-hand qty) |

### Status flags

| Output column      | Logic                                                                          |
| ------------------ | ------------------------------------------------------------------------------ |
| `Inventory Status` | `''` unless `UMSTAT IN ('O','E')`; then `IMQOH<=3`→`COMPLETE OOS`, else `CAN CHASE` |
| `Short Status`     | `''` when `UMSTAT` is blank/`P`; else `OPEN SHORT`                                          |
| `UMSTAT Status`    | blank→`Not Picked`; `E`→`In process`; `O`→`OOS (Out of stock)`; `P`→`Picked`       |

> **Inventory Status rule (authoritative):** OOS / chase classification is
> only meaningful for *pending* lines. A fully-picked line has no inventory
> status (blank).

### Short status note (F263.OPIDSC)

`F263.OPIDSC` column text: **TOTE DIVERTED CODE** (CHAR(1)).

- `Short Status` is simplified to just `OPEN SHORT` for any non-picked, started tote.
- `OPIDSC` is still included as a raw field for reference/filtering.

Recommended reporting fields:

- `OPIDSC`: raw code (trimmed)
- `Group Code`: raw `OPGPCD` code (trimmed)
- `Short Status`: reporting label based on picked/system-short/open-short logic

### Pivot-friendly export rule

Keep report SQL at row-level detail whenever Excel pivot tables can calculate
the metric. Do not add route/section/tote sums unless they are difficult to
recreate reliably in Excel.

Current exception:

| Output column      | Grain   | Source                          |
| ------------------ | ------- | ------------------------------- |
| `Route Tote Count` | `Route` | distinct `ULL` count per route  |
| `Ready Totes`      | `Route` | distinct `ULL` with UMSTAT Status in (`Picked`,`OOS (Out of stock)`) |
| `Missing Totes`    | `Route` | distinct `ULL` with UMSTAT Status in (`Not Picked`,`In process`)     |
| `Picks Remaining`  | `Route` | sum of positive `Qty Short`, shown only when `Missing Totes > 0`     |

---

## Replenishment sheet (F262)

For replenishment moves, use `F262` (current/open pick moves / replen workfile) as the driver and join:

- `F262.RLIMNO = F560.IMNO` for item description/brand/pack
- destination slot inventory (optional): `F561` on:
  - `F262.RLTOZN = F561.MPZONE`
  - `F262.RLTOID = (MPAISL||MPSECT||MPLEVL||MPITEM)`
  - `F262.RLIMNO = F561.MPIMNO`

Reference implementation: `replenishment_sheet.sql`

## “Don’t lose anything” guarantee

To ensure nothing from your source docs gets missed:

- All narrative, joins, rules, and cautions are preserved in the verbatim file:
  - `KEHE_DC45_AI_SCHEMA.source.md`
- All available table/column/index metadata is preserved in:
  - `Tables.source.csv`
- A structured subset for core tables is preserved as JSON:
  - `AI_DB_TABLE_CATALOG.core.json`