Wiki·domain·domain/the-aircraft-story.md

The Aircraft Story

If one entity is the hero key of this ERP, it's the aircraft. Every transactional record in this data — 10.5M rows across 152 tables — ultimately ties back to a specific aircraft registration number. So let's start there.

What an aircraft looks like in the data

The canonical record is in AC_ACI_AIRCRAFT_INFO. An aircraft has:

  • A registration number (ACI_AIRCRAFT_REG_NO). In commercial operation this is stencilled on the tail (e.g. G-XWBA, N12345). In this dataset we see both real-world reg formats and some internal codes like 1132, 101, 6YJMB.
  • A model (ACI_MDL_MODEL) — A310, A320-200, B767-200, E190-100AR, etc. The fleet has 125 distinct models.
  • A serial number (ACI_SERIAL_NO) from the manufacturer — globally unique to that airframe.
  • A configuration ID — which equipment package is fitted.
  • A planning base and maintenance base (the primary stations).
  • A regulatory authority (FAA, EASA, DGCA, …) and a country of registry.
  • An ownership (owned / customer / leased).

There are 1,025 aircraft in this dataset. The top models are A320-200 (132 airframes), A310 (112), A320-211 (89), E190-100AR (46).

The hero aircraft: 1132

We've picked aircraft reg 1132 as the primary demo subject because it has the richest history:

  • B767-200, serial 1132
  • 1,040 AME packages (tech logs over the dataset window)
  • 1,919 discrepancies raised against it
  • 1,825 snag rows, 125,857 sub-task sign-offs
  • 2,389 part consumption events
  • 224 deferrals, 4,040 resolution-history entries
  • 301 shop work orders for components that have been removed from this aircraft

For any question you ask the Brain about 1132, there's real data underneath. See Aircraft for entity-level details, or query FLOG_TLGMN_TECH_LOG_HDR directly with TLGMN_AIRCRAFT_REG_NO = '1132'.

Where the records show up

An aircraft's life in the ERP radiates outward through four main paths:

1. Line-maintenance work against it

Every arrival produces (or extends) an AMEPackage (also called a "tech log") in FLOG_TLGMN_TECH_LOG_HDR, keyed by TLGMN_AIRCRAFT_REG_NO. That package captures:

Walk this with AME Package Flow.

2. Discrepancies raised against it

Defects come in through DP_DISCP_DISCREPANCY_DTL (keyed by DISCP_AIRCRAFT_NO). Each discrepancy is either resolved (fix applied) or deferred (flown with a time/usage limit). The full state machine is Discrepancy Lifecycle.

3. Components installed on / removed from it

Serialised components live in AC_CMPID_COMPONENT_ID_INFO. A component is installed on the aircraft, then eventually removed (scheduled time-limit, or because it failed). When it's removed, it gets a ShopWorkOrder at swo_swoh_shop_wo_hdr, keyed by swoh_rem_aircraft_reg_no — which points back to the aircraft it came off.

For 1132, there are 301 shop work orders opened for components removed from it. That's the aircraft's repair trail.

4. Base visits

If the aircraft enters the hangar for a C-check or D-check, a VisitPackage opens. A visit is effectively an AME package on steroids — hundreds of tasks, thousands of sign-offs, multiple shop WOs in parallel.

Following an aircraft end-to-end

A typical live-demo query:

"Walk me through aircraft 1132. I want to see an open discrepancy, the AME package that resolved it, the parts consumed, the sign-off chain, and any shop WO that came out of it."

The Brain answers this by following the knowledge graph from Aircraft:

  1. Look up the fleet row in AC_ACI_AIRCRAFT_INFO.
  2. Reverse-traverse FOR_AIRCRAFT into DP_DISCP_DISCREPANCY_DTL and FLOG_TLGMN_TECH_LOG_HDR.
  3. For each AME package, cascade into FLOG_TLGPC_PART_CONSUMP_DTL, FLOG_TLGTSO_TSK_SNOFF, FLOG_TLGRC_RES_CONSUMP_DTL.
  4. For each discrepancy, cascade into DP_DPDEF_DISC_DEFERRAL_DTL, DP_RESLHST_DISC_RESOL_HIST.
  5. For each component removed, follow into swo_swoh_shop_wo_hdr and its SWO tasks, parts, sign-offs.

All of that is real data. Nothing synthetic.

See also