Traces a purchase order through the full order-to-cash cycle: PO creation (850) to acknowledgment (855) to shipment (856/857) to invoice (810) to receipt confirmation (861), from one unified external table with materialized BEG/BIG/BAK/BSN/BRA/N1/CTT/REF fields.
-- Unified table, every lifecycle segment materialized
CREATE EXTERNAL TABLE IF NOT EXISTS external.edi_demos.orders_unified
USING EDI
LOCATION '/path/to/edi/*.edi'
OPTIONS (
edi_config = '{
"ediFormat": "x12",
"materialized_paths": [
"beg_1", "beg_3", "beg_5",
"big_1", "big_2",
"bak_1", "bak_3",
"bsn_2", "bsn_3",
"bra_1",
"n1_1", "n1_2",
"ctt_1",
"ref_1", "ref_2"
]
}',
file_metadata = '{"columns":["df_file_name","df_row_number"]}'
);
-- Query 1, map each row to a lifecycle stage
SELECT
df_file_name,
st_1 AS txn_type,
CASE st_1
WHEN '850' THEN '1. PO created'
WHEN '855' THEN '2. PO acknowledged'
WHEN '856' THEN '3. Shipped'
WHEN '857' THEN '3. Ship & Bill'
WHEN '810' THEN '4. Invoiced'
WHEN '861' THEN '5. Received'
ELSE '?. Other'
END AS lifecycle_stage,
COALESCE(beg_3, bak_3, ref_2) AS reference_id,
n1_2 AS first_party
FROM external.edi_demos.orders_unified
ORDER BY lifecycle_stage, df_file_name;
-- Query 2: PO timeline: pick earliest/latest dates across stages per PO number
WITH by_po AS (
SELECT
COALESCE(beg_3, bak_3, ref_2) AS po_number,
st_1,
COALESCE(beg_5, bsn_3, big_1) AS stage_date
FROM external.edi_demos.orders_unified
WHERE COALESCE(beg_3, bak_3, ref_2) IS NOT NULL
)
SELECT
po_number,
MIN(stage_date) AS earliest_stage,
MAX(stage_date) AS latest_stage,
COUNT(DISTINCT st_1) AS stages_seen
FROM by_po
GROUP BY po_number
ORDER BY po_number;
-- Query 3: POs with gaps (no 855 or no 856)
WITH stages AS (
SELECT
COALESCE(beg_3, bak_3) AS po_number,
st_1
FROM external.edi_demos.orders_unified
WHERE COALESCE(beg_3, bak_3) IS NOT NULL
)
SELECT
po_number,
MAX(CASE WHEN st_1 = '850' THEN 1 ELSE 0 END) AS has_po,
MAX(CASE WHEN st_1 = '855' THEN 1 ELSE 0 END) AS has_ack,
MAX(CASE WHEN st_1 IN ('856','857') THEN 1 ELSE 0 END) AS has_ship,
MAX(CASE WHEN st_1 = '810' THEN 1 ELSE 0 END) AS has_invoice
FROM stages
GROUP BY po_number
ORDER BY po_number;
-- Query 4, trading partner activity across stages
SELECT
n1_2 AS party_name,
COUNT(DISTINCT st_1) AS distinct_stages,
COUNT(*) AS total_messages
FROM external.edi_demos.orders_unified
WHERE n1_2 IS NOT NULL
GROUP BY n1_2
ORDER BY distinct_stages DESC, total_messages DESC;## When to Use Use this demo when your X12 feed contains multiple correlated document types for the same business transaction and you need to follow a purchase order through its lifecycle. Unlike the basics demo (which treats each document independently), this one materializes the header segments of every transaction type into one table and uses COALESCE + CASE to map each row to a lifecycle stage. The result: cross-document joins and traceability without staging each document type into its own table. ## What You Will Learn 1. How to materialize header segments from every order-to-cash transaction into one wide table: BEG (850), BAK (855), BSN (856/857), BRA (receiving), BIG (810), plus common N1/CTT/REF 2. How to assign a lifecycle stage to each row with a CASE on ST_1 3. How to correlate documents across transaction types using shared reference numbers: PO number in BEG_3 (850) equals BAK_3 (855) equals REF_2 for F8 'Reference Number' qualifier 4. How to compute document timelines per PO using MIN/MAX on date elements (BEG_5, BAK_4, BSN_3, BIG_1) 5. How to spot missing stages, a PO with an 850 but no 855 or 856 has a gap in its lifecycle 6. How to use COALESCE to produce a single 'primary reference' column that pulls from the right segment per transaction type ## Prerequisites - A mix of X12 order-to-cash documents (850, 855, 856/857, 810, 861 are ideal) - Familiarity with materialized_paths (see demo_edi_x12_supply_chain_basics) - Familiarity with how REF segments carry cross-document references (qualifier in REF_1, value in REF_2)