# KEHE DC45 — DB2/AS400 AI Knowledge File
# DSN: TOLSE | Platform: DB2 for IBM i / AS400
# Use this file to teach an AI assistant the full schema before building queries

================================================================================
[SCHEMA OVERVIEW]
================================================================================

- NTS45   → DC45 Ontario/Rancho Cucamonga (primary dev schema)
- NTS41   → DC41
- NTS19   → DC19
- NTS35   → DC35
- NTS##   → All other KEHE distribution centers follow same NTS## naming pattern
- NTS25Q  → Fiscal calendar shared across ALL DCs (KKDAILY)
- TOLENT  → Enterprise-level tables: door/pallet scans (FPALLETS), invoice log (FDS0010)

CRITICAL RULE:
ALL NTS## schemas (NTS45, NTS41, NTS19, NTS35, etc.) share 100% identical
table names and column names. To port any query to another DC, change ONLY
the schema prefix. Every join, column reference, and business rule in this
document applies to all DC schemas equally.

Example:
  NTS45.FHUL04.UNUSERID = NTS45.FOP32.EIUSERID
  NTS41.FHUL04.UNUSERID = NTS41.FOP32.EIUSERID  ← identical, just swap prefix
  NTS19.FHUL04.UNUSERID = NTS19.FOP32.EIUSERID  ← identical

Cross-DC queries are valid — join NTS45 tables to NTS41 tables using the same
column names when comparing data across distribution centers.

================================================================================
[TABLE REFERENCE]
================================================================================

NTS45.FHUL04      ULL activity HISTORY (43M+ rows) — filter by UNSDTE or UNTIMESTMP first
NTS45.FUL04       ULL activity CURRENT/OPEN — same columns as FHUL04
NTS45.FHUL01      ULL header HISTORY — item/location/order detail per ULL
NTS45.FUL01       ULL header CURRENT — same columns as FHUL01
NTS45.FHUL02      Replenishment move HISTORY — user, date, wave, route, location
NTS45.FUL02       Replenishment move CURRENT/OPEN — same columns as FHUL02
NTS45.F262H       Pick move HISTORY
NTS45.F262        Pick move OPEN/CURRENT
NTS45.F262PLM     User zone assignment mapping — PLMUSER, PLMFRLOC
NTS45.F263H       Order line HISTORY — OPSONO, OPIMNO, OPCUST, OPSORT, OPULL
NTS45.F263        Order line CURRENT/OPEN — same columns as F263H
NTS45.F260H       Pick history summary — SHIDTE, SHTCSE, SHSCLA, SHSCLB, SHSCLJ, SHSCLF
NTS45.F575H       Putaway history — RCPONO, RCDTE
NTS45.F560        Item master — IMNO, IMDESC, IMCAT1-4, IMCUBE, IMPACK
NTS45.F561        Slot/location inventory — MPIMNO, MPZONE, MPAISL, MPSECT, MPLEVL, MPITEM, MPQOH, MPCUBE, MPVQOH, MPPACK
NTS45.F562H       RF move / inventory adjustment history — IMNO, date, user, qty, adj type
NTS45.F562        RF moves CURRENT
NTS45.F101C       Customer/order master — ABNO, ABALNM (fixed-length CHAR, always TRIM)
NTS45.FOP32       Employee master — EIUSERID, EINAME, EIPNO
NTS45.FOP32L1     Employee master alternate — EIUSERID, EINAME (prefer for loader/scan lookups)
NTS45.FLAACTIVH   LMS activity HISTORY — LTUSER, LTSDATE, LTETIME, LTSTIME, LTCOUNT, LTUPH, LTSUBACT, LTTEMP, LTFLOC, LTACTVTY, LTULLCNT
NTS45.FLAACTIV    LMS activity CURRENT — same columns as FLAACTIVH
NTS45.FLARFLOAD   Load data — LTRRTE, LTRPALLET, LTRUSR, LTRDC (filter: LTRDC = '45')
NTS45.FWH001      Route/door master — RTROUT, RTR2C8
NTS25Q.KKDAILY    Fiscal calendar — YRMD (date key), FISCALWK, FISCALYR, PERIOD
TOLENT.FPALLETS   Door/pallet staging scans — DC, WAVE, ROUTE, PALLET, USERID, STGLOCNR, STGLOCNA, CRTDTTM (filter: DC = '45')
TOLENT.FDS0010    Invoice transaction log — DSSONO, DSTPID, DSLUSR, DSLDAT, DSLPGM, DSDC

================================================================================
[KEY COLUMNS — shared across tables]
================================================================================

UNULL / ULULL / UMULL   → Shared ULL number key (FHUL04, FUL04, FHUL01, FUL01, FHUL02, FUL02, F262H, F262)
UNUSERID / EIUSERID     → Employee ID (FHUL04, FUL04 → FOP32, FOP32L1)
UMUSER / UMIUSR         → Replen employee ID (FHUL02, FUL02 → FOP32, FOP32L1)
LTUSER / EIUSERID       → LMS employee ID (FLAACTIVH → FOP32, FOP32L1)
LTRUSR / EIUSERID       → Loader employee ID (FLARFLOAD → FOP32L1)
USERID / EIUSERID       → Door scan employee ID (TOLENT.FPALLETS → FOP32L1)
ULIMNO / IMNO / OPIMNO  → Item number (FHUL01, FUL01, F263, F263H, F561, F562H → F560)
ULODOC / OPSONO         → Order document number (FHUL01, FUL01 → F263H, F263)
OPCUST / ABNO           → Customer number (F263, F263H → F101C)
UMROUT / RTROUT         → Route number (FUL02, FHUL02 → FWH001)
UMWAVE / WAVE           → Wave number (FUL02 ↔ TOLENT.FPALLETS)
UMPLID / LTRPALLET      → Pallet ID (FHUL02 ↔ FLARFLOAD)
UNFLOC / ULLOCN         → Location (compound join partner for ULL joins)
UMTLOC / UMFLOC         → Target/from location on replen (FHUL02, FUL02 → FHUL01, FUL01)
YRMD                    → Fiscal calendar date key (NTS25Q.KKDAILY)

================================================================================
[VERIFIED JOINS]
================================================================================

-- EMPLOYEE
NTS45.FHUL04.UNUSERID     = NTS45.FOP32.EIUSERID
NTS45.FHUL04.UNUSERID     = NTS45.FOP32L1.EIUSERID
NTS45.FUL04.UNUSERID      = NTS45.FOP32.EIUSERID
NTS45.FUL04.UNUSERID      = NTS45.FOP32L1.EIUSERID
NTS45.FHUL02.UMUSER       = NTS45.FOP32.EIUSERID
NTS45.FHUL02.UMIUSR       = NTS45.FOP32L1.EIUSERID
NTS45.FUL02.UMIUSR        = NTS45.FOP32L1.EIUSERID
NTS45.FLAACTIVH.LTUSER    = NTS45.FOP32.EIUSERID
NTS45.FLAACTIVH.LTUSER    = NTS45.FOP32L1.EIUSERID
NTS45.FLARFLOAD.LTRUSR    = NTS45.FOP32L1.EIUSERID
TOLENT.FPALLETS.USERID    = NTS45.FOP32L1.EIUSERID
NTS45.F262PLM.PLMUSER     = NTS45.FOP32L1.EIUSERID

-- ULL SPINE (compound join — always use BOTH columns)
NTS45.FHUL04.UNULL        = NTS45.FHUL01.ULULL
NTS45.FHUL04.UNFLOC       = NTS45.FHUL01.ULLOCN
NTS45.FUL04.UNULL         = NTS45.FUL01.ULULL
NTS45.FUL04.UNFLOC        = NTS45.FUL01.ULLOCN
NTS45.FHUL02.UMULL        = NTS45.FHUL01.ULULL  (via UMTLOC = ULLOCN or UMFLOC = ULLOCN)
NTS45.FUL02.UMULL         = NTS45.FUL01.ULULL   (via UMTLOC = ULLOCN)

-- REPLEN ↔ PICK MOVE
NTS45.FHUL02.UMULL        = NTS45.F262H.UMULL
NTS45.FUL02.UMULL         = NTS45.F262.UMULL

-- REPLEN ↔ PALLET/LOAD
NTS45.FHUL02.UMROUT       = NTS45.FLARFLOAD.LTRRTE
NTS45.FHUL02.UMPLID       = NTS45.FLARFLOAD.LTRPALLET
NTS45.FUL01.ULPLID        = NTS45.FHUL02.UMPLID

-- ITEM MASTER
NTS45.FHUL01.ULIMNO       = NTS45.F560.IMNO
NTS45.FUL01.ULIMNO        = NTS45.F560.IMNO
NTS45.F561.MPIMNO         = NTS45.F560.IMNO
NTS45.F562H.IMNO          = NTS45.F560.IMNO
NTS45.F263.OPIMNO         = NTS45.F560.IMNO
NTS45.F263H.OPIMNO        = NTS45.F560.IMNO      (note: column may appear as ITEMNMBR in F560)

-- ORDER / CUSTOMER
NTS45.FHUL01.ULODOC       = NTS45.F263H.OPSONO
NTS45.FHUL01.ULIMNO       = NTS45.F263H.OPIMNO
NTS45.FUL01.ULODOC        = NTS45.F263.OPSONO
NTS45.FUL01.ULIMNO        = NTS45.F263.OPIMNO
NTS45.FUL01.ULULL         = NTS45.F263.OPULL
NTS45.F263.OPCUST         = NTS45.F101C.ABNO
NTS45.F263H.OPCUST        = NTS45.F101C.ABNO

-- ROUTE / DOOR
NTS45.FUL02.UMROUT        = NTS45.FWH001.RTROUT
NTS45.FLARFLOAD.LTRRTE    = NTS45.FWH001.RTROUT

-- STAGING / DOOR SCANS
TOLENT.FPALLETS.WAVE      = NTS45.FUL02.UMWAVE
TOLENT.FPALLETS.ROUTE     = NTS45.FUL02.UMROUT

-- FISCAL CALENDAR
NTS45.FHUL02.UMCDTE       = NTS25Q.KKDAILY.YRMD
NTS45.FUL02.UMCDTE        = NTS25Q.KKDAILY.YRMD
NTS45.F260H.SHIDTE        = NTS25Q.KKDAILY.YRMD
NTS45.F575H.RCDTE         = NTS25Q.KKDAILY.YRMD
NTS45.F562H.date_col      = NTS25Q.KKDAILY.YRMD

-- SLOT INVENTORY
NTS45.F262.RLTOZN + RLTOID = NTS45.F561.MPZONE + (MPAISL||MPSECT||MPLEVL||MPITEM)

-- MULTI-DC NOTE
-- All joins above apply to ANY NTS## schema unchanged.
-- Replace NTS45 prefix with NTS41, NTS19, NTS35, etc. — columns are identical.

================================================================================
[TABLE CONNECTION MAP]
================================================================================

NTS45.FHUL04   ↔ NTS45.FHUL01     (via UNULL + UNFLOC)
NTS45.FHUL04   ↔ NTS45.FOP32      (via UNUSERID)
NTS45.FHUL04   ↔ NTS45.FOP32L1    (via UNUSERID)
NTS45.FUL04    ↔ NTS45.FUL01      (via UNULL + UNFLOC)
NTS45.FUL04    ↔ NTS45.FOP32      (via UNUSERID)
NTS45.FUL04    ↔ NTS45.FOP32L1    (via UNUSERID)
NTS45.FHUL01   ↔ NTS45.F263H      (via ULODOC + ULIMNO)
NTS45.FHUL01   ↔ NTS45.F560       (via ULIMNO)
NTS45.FUL01    ↔ NTS45.F263       (via ULODOC + ULIMNO)
NTS45.FUL01    ↔ NTS45.F560       (via ULIMNO)
NTS45.FUL01    ↔ NTS45.FHUL02     (via ULPLID = UMPLID)
NTS45.FHUL02   ↔ NTS45.F262H      (via UMULL)
NTS45.FHUL02   ↔ NTS45.FOP32      (via UMUSER)
NTS45.FHUL02   ↔ NTS45.FOP32L1    (via UMIUSR)
NTS45.FHUL02   ↔ NTS45.FLARFLOAD  (via UMROUT + UMPLID)
NTS45.FUL02    ↔ NTS45.F262       (via UMULL)
NTS45.FUL02    ↔ NTS45.FOP32L1    (via UMIUSR)
NTS45.FUL02    ↔ NTS45.FWH001     (via UMROUT)
NTS45.F263     ↔ NTS45.F101C      (via OPCUST = ABNO)
NTS45.F263H    ↔ NTS45.F101C      (via OPCUST = ABNO)
NTS45.F561     ↔ NTS45.F560       (via MPIMNO = IMNO)
NTS45.F562H    ↔ NTS45.F560       (via IMNO)
NTS45.FLAACTIVH ↔ NTS45.FOP32     (via LTUSER)
NTS45.FLAACTIVH ↔ NTS45.FOP32L1   (via LTUSER)
NTS45.FLARFLOAD ↔ NTS45.FWH001    (via LTRRTE = RTROUT)
NTS45.FLARFLOAD ↔ NTS45.FOP32L1   (via LTRUSR)
TOLENT.FPALLETS ↔ NTS45.FUL02     (via WAVE + ROUTE)
TOLENT.FPALLETS ↔ NTS45.FOP32L1   (via USERID)
NTS45.FHUL02   ↔ NTS25Q.KKDAILY   (via UMCDTE = YRMD)
NTS45.F260H    ↔ NTS25Q.KKDAILY   (via SHIDTE = YRMD)
NTS45.F575H    ↔ NTS25Q.KKDAILY   (via RCDTE = YRMD)
NTS45.F262PLM  ↔ NTS45.FOP32L1    (via PLMUSER = EIUSERID)

================================================================================
[COMMON JOIN PATTERNS]
================================================================================

-- ULL activity → item detail
NTS45.FHUL04 → NTS45.FHUL01 → NTS45.F560
  (UNULL+UNFLOC → ULULL+ULLOCN → ULIMNO=IMNO)

-- ULL activity → order → customer
NTS45.FHUL04 → NTS45.FHUL01 → NTS45.F263H → NTS45.F101C
  (UNULL+UNFLOC → ULULL+ULLOCN → ULODOC+ULIMNO=OPSONO+OPIMNO → OPCUST=ABNO)

-- Replen history → employee performance
NTS45.FHUL02 → NTS45.FOP32L1
  (UMIUSR = EIUSERID)

-- Replen → fiscal reporting
NTS45.FHUL02 → NTS25Q.KKDAILY
  (UMCDTE = YRMD → FISCALWK, FISCALYR, PERIOD)

-- Door scan → employee + wave
TOLENT.FPALLETS → NTS45.FUL02 → NTS45.FOP32L1
  (WAVE+ROUTE → UMWAVE+UMROUT → UMIUSR=EIUSERID)

-- Load data → replen → route/door
NTS45.FLARFLOAD → NTS45.FHUL02 → NTS45.FWH001
  (LTRRTE+LTRPALLET → UMROUT+UMPLID → UMROUT=RTROUT)

-- Employee dashboard (combined history + current)
NTS45.FLAACTIVH → NTS45.FOP32L1
  (LTUSER = EIUSERID)

-- Replen board (open moves with slot detail)
NTS45.FUL02 → NTS45.FUL01 → NTS45.F561 + NTS45.F560
  (UMULL+UMTLOC → ULULL+ULLOCN → MPIMNO=IMNO)

================================================================================
[BUSINESS RULES]
================================================================================

-- DATE / SHIFT
Work week start  : CURRENT_DATE - (DAYOFWEEK(CURRENT_DATE) - 1) DAYS  (Sunday)
Work week end    : start + 5 DAYS  (Friday)
Overnight 6AM    : IF UMCTME < 600 THEN date = UMCDTE - 1 DAYS (before 6AM = prior shift)
Door scan 9PM    : LTRLDATE IN (CURRENT_DATE, CURRENT_DATE - 1 DAYS)
                   AND (LTRLTIME >= '090000' OR LTRLTIME < '040000')
EST→PST rollover : Apply when timestamps are EST and warehouse operates PST

-- ZONE CLASSIFICATION (by first char of location)
A/B/C/N/R/S = Dry
J/G/H       = Cooler / Refrigerated
K/L/M       = Freezer
Zone ranges  : S83-S90 = North | S17-S38 = Dry | J03-J23 = Cooler | K42-K57 = Freezer

-- ULL TYPE (by first char of ULL number)
LEFT(UMULL,1) = '7'  → Full case pallet
LEFT(UMULL,1) = '8'  → Tote / line item

-- ROUTE CLASSIFICATION
Warehouse route  : UMROUT BETWEEN 800 AND 999
                   OR UPPER(TRIM(F101C.ABALNM)) LIKE '%WHS%'
                   OR UPPER(TRIM(F101C.ABALNM)) LIKE '%WAREHOUSE%'
                   OR UPPER(TRIM(F101C.ABALNM)) LIKE '%KEHE%'

-- DC FILTERS
TOLENT.FPALLETS : always filter DC = '45'
TOLENT.FLARFLOAD: always filter LTRDC = '45'
TOLENT.FDS0010  : always filter DSDC = '41' (DC41 invoice log)

-- FIXED-LENGTH CHAR FIELDS (always TRIM when comparing or displaying)
F101C.ABALNM    CHAR(30) — customer name, pads with spaces
FOP32.EINAME    CHAR(30) — employee name
FOP32L1.EINAME  CHAR(30) — employee name
FOP32L1.EIUSERID CHAR(10) — user ID
FPALLETS.USERID CHAR(10) — user ID (use TRIM on join)

-- EXCLUDE SYSTEM USERS
UMUSER NOT LIKE 'CHNWH%'
EINAME NOT LIKE '%DEFAULT%'

-- INTEGER WRAPPING (required to avoid DECIMAL aggregate errors)
Use INTEGER() around CHAR-stored time values before math operations

-- COMPOUND JOINS (always use BOTH columns)
FHUL04 → FHUL01 requires UNULL + UNFLOC  (not UNULL alone)
FUL04  → FUL01  requires UNULL + UNFLOC

-- DEDUPLICATION
Use ROW_NUMBER() OVER (PARTITION BY key ORDER BY timestamp DESC) for latest-record logic
F562H self-join for RF returns uses composite key: ILIMNO||CHAR(ILDTE)||RTRIM(CHAR(ILQTY*-1))||ILUSER

-- SALES ORDER PADDING
F263 / F263H OPSONO may require left-pad to match source:
CROSS JOIN (VALUES (' '),(' 0'),(' 00'),(' 000'),(' 0000'),(' 00000')) AS p(pad)
WHERE (p.pad || DSSONO) = F263.OPSONO

-- LARGE TABLE PERFORMANCE
FHUL04 has 43M+ rows — always filter by UNSDTE or UNTIMESTMP first

================================================================================
[EXCLUDED / LOW CONFIDENCE]
================================================================================

- NTS## cross-schema joins: all schemas confirmed identical structure — swap prefix freely
- TOLENT.FDS0010 joins: invoice/sales context only, not warehouse ULL operations
- F561 ↔ F262 slot join: concatenated computed key (RLTOZN||RLTOID), fragile — use with caution
- F562 self-join: composite string key, one-off pattern, excluded
- TOLENT.FDS0010 DC filter: DSDC = '41' shown in examples — verify correct DC code per query context