Skip to main content
Reference

EDI Document Queries

Overview​

These queries read uc_edi_documents, which holds one row for every EDI document UltraCart has sent to or received from a channel partner on your behalf: the inbound 850 purchase order, and the outbound 855 acknowledgement, 856 ship notice, 810 invoice, 812 credit/debit adjustment, and 180 return authorization. Each row carries the order it belongs to, the direction, the document type, the group control number, and when the 997 functional acknowledgement for it was exchanged.

The EDI Document Archive on an order shows the same rows for one order at a time. That answers "was this order's credit acknowledged?" but not "how many credits has this partner never acknowledged?". The second question is the one a trading partner's EDI team needs answered, and these queries answer it.

Four things about this data matter before you run anything.

A missing 997 is a null functional_acknowledgement_dts. For an outbound document it is when the partner's 997 arrived. For an inbound document it is when UltraCart sent its 997 back. The functional_acknowledgement_pending flag usually agrees, but key on the timestamp: it is the field the queries below use, and the one the Document Archive displays.

Allow time before calling a 997 missing. Partners batch their acknowledgements, and a document sent an hour ago with no 997 is usually just waiting. The queries that list missing acknowledgements skip anything sent in the last 24 hours.

Times are UTC. The Document Archive shows the same timestamps in Eastern time. The list query below converts them so that what you send a partner matches what they see in a copy of the archive. Order and Revenue Queries covers the conversion in more detail.

partition_date is a weekly bucket. Each document sits in the partition for the week it was created, up to seven days before doc_dts. The table is small enough that the queries here filter on doc_dts alone. If you add a partition filter to cut cost, make it seven days wider than the doc_dts range you want.

The partner for each document comes from the order: join to uc_orders on order_id and read channel_partner.channel_partner_code for the partner and channel_partner.channel_partner_order_id for the partner's own PO number. Filter test_mode = false to drop documents exchanged while a partner connection was being certified.

997 Acknowledgement Rate by Partner and Document Type​

Returns one row per partner, direction, document type, and month for the last 12 months, with the count of documents and how many have no 997. Run this first. A healthy connection has a no_997 column of zero almost everywhere, so any document type where it climbs stands out immediately. When one outbound type goes unacknowledged while the others on the same connection are fine, the partner is receiving your documents but not processing that type, and the conversation to have is with their EDI team about that document type specifically.

SELECT
  o.channel_partner.channel_partner_code AS partner,
  FORMAT_DATETIME('%Y-%m', d.doc_dts) AS month,
  d.direction,
  d.document_type_number,
  d.document_type_description,
  COUNT(*) AS documents,
  COUNTIF(d.functional_acknowledgement_dts IS NULL) AS no_997,
  ROUND(100 * SAFE_DIVIDE(COUNTIF(d.functional_acknowledgement_dts IS NULL), COUNT(*)), 1) AS no_997_pct
FROM `my-data-warehouse.my_dataset.uc_edi_documents` AS d
JOIN `my-data-warehouse.my_dataset.uc_orders` AS o USING (order_id)
WHERE d.doc_dts >= DATETIME_SUB(CURRENT_DATETIME(), INTERVAL 365 DAY)
  AND NOT d.test_mode
GROUP BY partner, month, d.direction, d.document_type_number, d.document_type_description
ORDER BY partner, month, d.direction, d.document_type_number

When Each Partner Last Acknowledged Each Document Type​

Returns one row per partner and outbound document type, with the last document sent, the last one the partner acknowledged, and how many have gone unacknowledged since then. This dates the start of a problem in one line instead of making you read it out of a monthly table, and the count is the size of the backlog to raise with the partner. A last_acknowledged far behind last_sent with a large unacknowledged_since is a connection that has stopped processing that type.

WITH docs AS (
  SELECT
    o.channel_partner.channel_partner_code AS partner,
    d.document_type_number,
    d.document_type_description,
    d.doc_dts,
    d.functional_acknowledgement_dts
  FROM `my-data-warehouse.my_dataset.uc_edi_documents` AS d
  JOIN `my-data-warehouse.my_dataset.uc_orders` AS o USING (order_id)
  WHERE d.direction = 'outbound'
    AND NOT d.test_mode
),
last_ack AS (
  SELECT
    partner,
    document_type_number,
    MAX(IF(functional_acknowledgement_dts IS NOT NULL, doc_dts, NULL)) AS last_acknowledged
  FROM docs
  GROUP BY partner, document_type_number
)
SELECT
  docs.partner,
  docs.document_type_number,
  ANY_VALUE(docs.document_type_description) AS document_type_description,
  COUNT(*) AS documents_all_time,
  MAX(docs.doc_dts) AS last_sent,
  ANY_VALUE(last_ack.last_acknowledged) AS last_acknowledged,
  COUNTIF(docs.functional_acknowledgement_dts IS NULL
          AND docs.doc_dts > IFNULL(last_ack.last_acknowledged, DATETIME '1970-01-01')
          AND docs.doc_dts < DATETIME_SUB(CURRENT_DATETIME(), INTERVAL 1 DAY)) AS unacknowledged_since
FROM docs
JOIN last_ack USING (partner, document_type_number)
GROUP BY docs.partner, docs.document_type_number
ORDER BY unacknowledged_since DESC, docs.partner, docs.document_type_number

Outbound Documents Missing a 997​

Lists every outbound document to one partner that has gone more than 24 hours without a 997, oldest first, with the partner's PO number and the group control number. This is the list to send the partner's EDI team: the group control number is how they find the transmission in their own system, and the PO number is how they find the order. Export it to a spreadsheet from the BigQuery console with Save Results.

Set the partner code, and narrow document_type_number if you only care about one type, such as 812 for credits.

SELECT
  d.order_id,
  o.channel_partner.channel_partner_order_id AS partner_po,
  d.document_type_number,
  d.document_type_description,
  d.group_control_number,
  FORMAT_DATETIME('%m/%d/%Y %H:%M:%S',
    DATETIME(TIMESTAMP(d.doc_dts), 'America/New_York')) AS sent_eastern
FROM `my-data-warehouse.my_dataset.uc_edi_documents` AS d
JOIN `my-data-warehouse.my_dataset.uc_orders` AS o USING (order_id)
WHERE o.channel_partner.channel_partner_code = 'PARTNER'   -- <- your partner code
  AND d.direction = 'outbound'
  -- AND d.document_type_number = 812                       -- <- optionally one type
  AND d.functional_acknowledgement_dts IS NULL
  AND d.doc_dts < DATETIME_SUB(CURRENT_DATETIME(), INTERVAL 1 DAY)
  AND d.doc_dts >= DATETIME_SUB(CURRENT_DATETIME(), INTERVAL 365 DAY)
  AND NOT d.test_mode
ORDER BY d.doc_dts

Pair this with the query above. If a partner has never reliably acknowledged a document type, a list going back years mostly repeats an old, known problem. Starting the list at the last_acknowledged date shows only what changed.

EDI History for One Order​

Returns every EDI document for a single order in the order it was exchanged, with acknowledgement times in Eastern. This is the SQL equivalent of the Document Archive screen, useful when you are working a case in a notebook or pasting the history into a ticket.

SELECT
  d.direction,
  d.document_type_number,
  d.document_type_description,
  d.group_control_number,
  FORMAT_DATETIME('%m/%d/%Y %H:%M:%S',
    DATETIME(TIMESTAMP(d.doc_dts), 'America/New_York')) AS sent_eastern,
  FORMAT_DATETIME('%m/%d/%Y %H:%M:%S',
    DATETIME(TIMESTAMP(d.functional_acknowledgement_dts), 'America/New_York')) AS acknowledged_eastern,
  d.test_mode
FROM `my-data-warehouse.my_dataset.uc_edi_documents` AS d
WHERE d.order_id = 'DEMO-1234'   -- <- the order you are investigating
ORDER BY d.doc_dts, d.direction
Was this page helpful?