Hollander data sources

What the interchange updater does

This application rebuilds URG’s interchange list — the table that powers part-request resolution — by combining Hollander’s interchange data with URG member-yard data and mapping it onto the ACES/AAIA vehicle catalog. Hollander periodically ships an interchange update (a full SQL Server .bak); the pipeline below ingests it, parses it, enriches it, and rebuilds the hollander_ic_urgpro schema. Once validated, that schema is promoted to production (hollander_ic).

The heavy lifting lives in two PostgreSQL functions — split_hollander_app() (parses the free-text application) and get_aaia_position() (derives the part position) — orchestrated by the Laravel commands shown for each stage.

Current build output — hollander_ic_urgpro (live row estimates)

The pipeline

1. 1
   Sync base data MSSQL ENTERPRISE → Postgres

   Pulls the 10 Hollander base tables from the MSSQL ENTERPRISE database (the interchange version Hollander ships as a .bak) into Postgres: interchangelist, indexlist, indexlistapp, interchangenote, carline, manufacturer, ptype, ids2hol, remarks, version.

   php artisan hollander:sync-from-mssql
2. 2
   Carline & make carline → carline_w_make

   Builds carline_w_make and assigns each vehicle its brand through 435 manufacturer rules (ported from set_makes.sh) — GM/Chrysler year-splits, the Dodge→Ram rebrand, idxmodel disambiguation — then carries forward each carline’s hol_id.

   php artisan hollander:build --phase=2
3. 3
   Build the interchange list interchangelist → urg_interchangelist (~9M rows)

   The core step. Parses every free-text application string via the split_hollander_app() database function → model, year range, engine, VIN-digit filters and left/right; joins carline for make and indexlistapp for the L/R interchange number; sets the AAIA position via get_aaia_position(); appends synthetic “NON-INTERCHANGE” one-to-one rows (every part type × carline) so parts with no interchange still resolve to a vehicle; and explodes VIN-digit ranges into discrete rows.

   php artisan interchange:update
4. 4
   OEM cross-reference Interchange.Int_Pline → ids2hol

   Rebuilds ids2hol (OEM part number → interchange) from the Interchange database’s Int_Pline table — classifying left/right from the part description and resolving each OEM number to the real L/R interchange via the freshly-built list.

   php artisan interchange:build-ids2hol
5. 5
   Derived lookups urg_interchangelist → interchangelist_dist

   Builds the distinct interchange / left-right / part-type lookup table and the other derived tables used for fast resolution.

   php artisan hollander:build --phase=4
6. 6
   Validate hollander_ic_urgpro vs hollander_ic

   Read-only comparison of the freshly-built schema against the golden production schema — row counts, distinct interchange numbers and part types, and parsed-field coverage — so a build can be checked before going live.

   php artisan interchange:validate
7. 7
   Publish (gated) hollander_ic_urgpro → hollander_ic

   Reversible production cutover: renames the current production schema to a timestamped backup and promotes the freshly-built schema in its place. Dry-run by default; requires an explicit confirmation and passes a row-count sanity check first.

   php artisan interchange:publish --confirm=PROMOTE