Chapter 11.3 — BSP / ARC Import

1. Purpose

The BSP (Billing and Settlement Plan, operated by IATA outside the US) and ARC (Airlines Reporting Corporation, US) are the clearing houses that settle airline-issued tickets between agents and airlines. This chapter defines how travoBooks ingests BSP/ARC files, matches them to internal ticket and memo records, identifies and resolves variances, computes the agent's settlement obligation, and produces the reconciliation report that the partner pays against.

2. Why it matters

The BSP/ARC file is the authoritative settlement record. If travoBooks' books disagree with BSP, the partner pays what BSP says — disputes are filed retrospectively. The wire to BSP is non-negotiable in timing; missed wires trigger penalties and possibly loss of accreditation. A robust BSP import is therefore a financial-survival capability, not just a data-engineering task.

3. Industry relevance

• BSP is structured per country (BSP-BD, BSP-IN, BSP-AE, BSP-UK, etc.) with semi-monthly billing periods (H1: 1–15, H2: 16–end-of-month) and a country-specific remittance deadline.
• ARC is for US-based agents; weekly Wednesday cycle.
• Both produce files in IATA-standardised formats: HOT (Header / Detail / Trailer), AGTDATA, plus newer DISH record types.
• Memos (ADM/ACM, Chapter 4.6) come as part of or separate from the main billing analysis.

4. Compliance considerations

• IATA Resolution 818g (2026 revision) defines the billing-and-settlement process; travoBooks implementation aligned.
• Dispute window typically 30–60 days from receipt depending on local BSP rules.
• Cash retention — some BSPs require collateral / bank guarantee; tracking but not travoBooks' posting concern.
• Audit — BSP files form part of the audit pack each period.

5. Business logic

5.1 File structure (HOT format, simplified)

A BSP billing-analysis file has multiple record types identified by leading characters:

Each record has fixed positions per IATA spec; the parser maps them to a normalised internal model.

5.2 Internal model

bsp_import_files (one row per file)
└── bsp_import_lines (one row per record, normalised)
    ├── type = TICKET → bsp_tickets
    ├── type = EMD → bsp_emds
    ├── type = REFUND → bsp_refunds
    ├── type = MEMO → memos
    └── type = ADJUSTMENT → bsp_adjustments

5.3 Import flow

1. File acquisition — daily SFTP poll from BSPlink, or manual upload by BSP-ops.
2. Checksum + duplicate check — idempotency table.
3. Persist file metadata + S3 archive of raw.
4. Parse — produce bsp_import_lines and typed child tables (staging).
5. Schema validation — header/trailer totals reconcile; record counts match.
6. Matching — each ticket/EMD/refund/memo matched to internal record by:
7. Primary: ticket_number exact match.
8. Secondary: (airline_code, ticket_number_partial) if partial number.
9. Tertiary: (passenger_name, segments, issue_date_range) for likely match — manual confirmation needed.
10. Variance computation — per record: amount, commission, taxes, refund_amount — internal vs file.
11. Commit:
12. Linked records: post settlement JEs.
13. Memo records: insert into memos (Chapter 4.6 lifecycle).
14. Variance records: open dispute case or post variance JE.
15. Orphan records: queue for manual investigation.
16. BSP settlement total computed from file trailer: this is what the partner wires.
17. Reconciliation report generated; circulated to controller.
18. Import run marked complete.

5.4 The settlement JE

For a typical BSP-BD H1 file with totals: - Gross ticket sales (issued): BDT 12,800,000 - Refunds: BDT 1,200,000 - Commission (base + override): BDT 700,000 - Taxes (pass-through): BDT 1,500,000 (already inside gross) - ADMs: BDT 30,000 - ACMs: BDT 8,000 - Net to BSP: BDT 11,322,000

The BSP wire JE (when paid):

Debit  2011 BSP Payable BD            11,322,000
Credit 1013 Bank — BSP Settlement     11,322,000

The 2011 balance prior to this wire was built up over the period through individual ticket-issuance JEs. The file matches what we accrued (within tolerance). Variances are addressed per §5.6.

5.5 Per-ticket matching details

For each bsp_tickets row, the matching engine computes:

Mismatches go into variance buckets: - MATCH_OK — exact alignment, settled. - COMMISSION_VARIANCE — different commission rate/amount. - TAX_VARIANCE — XT breakdown differs. - FARE_VARIANCE — base fare differs (rare; usually data error). - MISSING_TICKET — in our books, not in BSP file (re-check next file; if still missing, escalate). - PHANTOM_TICKET — in BSP file, not in our books (operational gap; investigate root cause).

5.6 Variance handling

All variance JEs use a current-period date even if the original ticket is older. Variance accounts: - 5045 — BSP Variance Expense - 7045 — BSP Variance Income

5.7 Memo handling

ADM/ACM records in the file are routed to the memos module (Chapter 4.6). They are not posted as part of the BSP settlement JE directly; the memo lifecycle (dispute window etc.) governs their final posting.

However, the net wire amount to BSP already includes accepted/uncontested memos — the partner cannot delay wire because of a disputed memo. The disputed-memo provisioning is a parallel accounting concern.

5.8 Wire execution

Once the file is reconciled: 1. Treasury initiates wire to BSP clearing bank for the file-stated net. 2. Wire confirmation recorded against 2011 BSP Payable BD. 3. Bank reconciliation matches the outgoing wire to the BSP file reference. 4. Period reconciliation closed for that BSP cycle.

5.9 ARC variant

ARC files follow IAR (Interactive Agent Reporting) format with weekly cycles. The internal model is the same; parser differs; settlement JEs identical conceptually with US-jurisdiction account codes.

6. Inputs → processing → outputs

Already covered in §5.3.

7. Module dependencies

8. Security & permissions

9. Validation rules

10. Error handling

A failed parse leaves the file in bsp_import_files with state = FAILED_PARSE; BSP-ops re-investigates. Re-importing the same file after fixing the parser uses the same idempotency key — safe.

A successful import with orphans does not block settlement; orphans are tracked separately.

11. Real-world examples

A — Clean cycle

BSP-BD H1 (May 1–15) file arrives May 18: - 4,200 ticket records, 180 refund records, 22 ADM/ACM. - 99.8% match rate; 8 orphans (1 phantom, 7 missing). - Commission variance: BDT 0 (rules in sync). - Net to BSP: BDT 11.32M. - Wire executed May 22 (BSP deadline May 25).

B — Commission variance

H2 cycle: aggregate commission variance BDT 78,000 short of expected. - Drill-down: EK route DAC-DXB returns 4% rather than expected 6% on 30 tickets. - Root cause: EK changed override structure effective May 12, partner config not updated until May 20. - Treatment: - File dispute for BDT 78,000. - Provision write-down BDT 78,000 to 5045 BSP Variance Expense. - Update commission rules with corrected valid_from. - Dispute resolution next cycle.

C — Phantom ticket

File contains a ticket BDT 25,000 against our agent code; not in our books. - Investigation: PNR was created on a sub-agency code that mistakenly used our agent code (BSP data quality). - Action: dispute filed; ticket excluded from our books; net wire still includes it (file is authoritative). - Provision: pending dispute outcome.

12. Step-by-step workflow

13. Database tables touched

14. Future scalability

• BSPlink API direct integration — replace SFTP polling with API push.
• Real-time per-ticket pre-validation — check fare/commission against expected at issue-time, not at settlement.
• ML-based variance root-cause — propose likely causes (rule lag, supplier behaviour change).
• Predictive net-wire — give Treasury 24h forecast of next BSP wire amount.

15. Common pitfalls

• ⚠️ Treating internal books as authoritative for settlement. They are for accounting; BSP file is for paying. Use file's net.
• ⚠️ Posting memos within the settlement JE. Memos have their own lifecycle.
• ⚠️ Missing BSP wire deadline. Penalty + accreditation risk.
• ⚠️ Stale agent code. If a partner changes IATA accreditation, the agent code in BSP file changes — config must follow.
• ⚠️ Ignoring small variances. Small consistently in one direction = systemic config error.
• 🔒 BSP credentials in supplier_credentials are highly sensitive — encrypted, accessed only by the import job's service account.