Project-A - Import / Export - Reference File Processing (2)

**** This new feature is created per IT email request to veer away from umbrella features and accommodate new user stories added by team ITPOs. --------------------------------------------------------- As a SBSS user I want to automatically apply updates to references files based on regular downloads from customs so that I can make sure any newly created declarations are compliant. Based on the Interface agreement, BSIS will provide the contents of the reference data files. SBSS AU will need to process these, and update the corresponding data in SBSS. Due to their complexity, mapping of tariff related files is a separate feature: Feature 4791422: AU Tariff files processing Activities: identify relevant reference files, and for each file identified: done in feature Feature 5321134: Project-A - Import / Export - Registry and Reference Files Remaining Activities: We need to verify that SBSS receives the expected files (as identified in the ICD) from the TPS (web)site from BSIS, so they are available for processing on the SBSS’ side. Not included in this feature: Verifying that all the reference files that are made available are correctly translated into SBSS (references). This must be part of the features that use the specific reference files.

Input documents:

title: SBSS Platform Knowledge Document
content: ## Carrier Co Australia Customs Brokerage System

---

## 1. Platform Overview

### What is SBSS?
SBSS (Brokerage Operations Support System) is Carrier Co Australia's customs brokerage platform used to manage import and export declarations for shipments entering or leaving Australia. It serves as the primary interface between Carrier Co customs brokers and the Customs Border Force (CBF) for regulatory compliance and customs clearance.

### Who Uses SBSS?
- **Customs Brokers (BRK)** – Process and submit declarations to customs authorities
- **Administrators (ADM)** – Configure system parameters and manage operational settings
- **Compilers (COM)** – Prepare and validate declaration data before submission
- **Operations Supervisors** – Oversee workflow and exception handling
- **Front Office Staff** – Handle day-to-day shipment processing and customer liaison

### Business Purpose
SBSS supports the end-to-end customs brokerage workflow for international shipments, including:
- Declaration preparation and validation
- Submission to customs authorities via integrated government systems
- Response handling (approvals, rejections, exceptions)
- Document management and record retention
- Charge calculation and billing
- Regulatory compliance with CBF requirements

---

## 2. System Architecture

### Core Systems in the SBSS Ecosystem

#### SBSS (Brokerage Operations Support System)
The central platform where all declaration work is performed. SBSS maintains shipment records, declarations, commodities, parties, and documents. It orchestrates communication between internal Carrier Co systems and external government platforms.

#### TPS (Trade Processing System)
The Customs Border Force's primary customs processing system. SBSS submits declarations to TPS and receives responses (permits, rejections, holds) via TradeNet messaging.

#### TradeNet
Singapore's national trade facilitation platform. For Singapore operations, SBSS communicates with TradeNet for customs approvals using EDIFACT-based messaging protocols.

#### BDIS (Brokerage Document Imaging System)
Document management system that stores scanned copies of invoices, packing lists, certificates of origin, and other supporting documents. BDIS integrates with SBSS to attach documents to declarations for customs review.

#### TCS (HAWB Management System)
Manages House Air Waybill (HAWB) records and shipment tracking references. TCS provides shipment master data to SBSS including consignee details, weights, values, and routing information.

#### MSG-ESM (Enterprise Shipment Management)
Carrier Co's enterprise-level shipment tracking system. MSG-ESM provides real-time visibility into shipment status across the Carrier Co network and feeds operational data to SBSS.

#### MSG-CSA (Cargo Release System - Trucking)
Manages cargo release for trucking operations and inland movement. MSG-CSA coordinates with SBSS to ensure shipments are physically released only after customs clearance.

#### OLAF (Operations Logistics and Forwarding)
Freight forwarding system that manages booking, routing, and carrier coordination. OLAF sends shipment forecast data to SBSS before physical arrival.

#### BCR (Broker Client Reporting)
Reporting and analytics platform that generates operational and compliance reports for brokers and customers. BCR extracts data from SBSS for business intelligence.

#### BSIS (Brokerage Statistical Information System)
Archives historical declaration data for regulatory reporting, audits, and compliance verification. BSIS ensures long-term retention of customs records as required by law.

---

## 3. Integration Patterns

### Message Exchange with Customs Authorities

#### EDIFACT Messaging
SBSS uses UN/EDIFACT (Electronic Data Interchange for Administration, Commerce and Transport) standard messages to communicate with TradeNet (Singapore) and TPS (Australia). Common message types include:

- **Declaration Messages**: Submit formal customs declarations
- **Permit Messages**: Receive customs approvals and permit numbers
- **Error Messages (ERRORM)**: Receive validation failures and rejection reasons
- **Status Messages (STATUS-R)**: Query and receive status updates on submitted declarations
- **Update Messages**: Submit amendments to previously lodged declarations

#### Message Patterns

**Synchronous Communication**:
- Real-time validation responses during declaration submission
- Immediate error feedback for malformed messages

**Asynchronous Communication**:
- Declaration submission → customs processing → permit issuance (can take minutes to hours)
- Status polling to track declaration progress through customs workflow
- Event-driven updates when customs officers complete manual review

#### Message Naming Conventions
- **INPDEC**: Import Payment Declaration (for formal clearances with duty/GST payable)
- **IPTDEC**: Import Payment Declaration (specific duty treatment types)
- **TNPDEC**: Transhipment Declaration (Batam consolidation scenario)
- **INPUPD**: Import Payment Update (amend existing declarations)
- **IPTUPD**: Import Payment Update (specific treatment amendments)
- **OUTDEC**: Outbound/Export Declaration
- **COODEC**: Country of Origin Declaration
- **INPPMT**: Import Permit (approval response)
- **IPTPMT**: Import Permit (specific treatment approval)
- **TNPPMT**: Transhipment Permit
- **OUTPMT**: Export Permit
- **COODCI**: Country of Origin Declaration Information response

### APIs and File Transfers
- **Shipment Download**: TCS pushes shipment manifests to SBSS via batch file transfer
- **Document Upload**: BDIS receives document images via API calls triggered by SBSS
- **Charge File Generation**: SBSS exports charge files to billing systems via scheduled jobs
- **Reference Data Sync**: Tariff codes, exchange rates, and regulatory tables updated via periodic feeds

---

## 4. Data Model Overview

### Key Entities and Relationships

#### Shipment
The central object representing a physical cargo movement from origin to destination.
- **Identifiers**: Shipment ID, MAWB (Master Air Waybill), HAWB (House Air Waybill), 1Z Tracking Number
- **Routing**: Origin country/port, destination country/port, transit country (for transhipments)
- **Parties**: Shipper, consignee, importer, broker
- **Weights/Values**: Gross weight, net weight, declared value, freight charges
- **Status**: Shipment status codes (e.g., 03 = in-process, 04 DA = cleared)

#### Declaration
The formal customs submission for one or more shipments.
- **Declaration Number**: Customs Reference Number (CRN) assigned by SBSS, Customs Control Number (CCN) assigned by CBF
- **Clearance Type (CT)**: FML (Formal), DEM (De minimis), LND (Low-value No-Duty), FTZ (Free Trade Zone)
- **Declaration Type**: Import/Export, Treatment codes (APS/GTR/SHO/DES/REX/GST/DUT/DNG/BKT)
- **Totals**: Declaration total value, statistical value, freight amount, insurance, duties
- **Brokerage Status**: Tracks workflow stages (RAT, EBC, CUS statuses)

#### Commodity Line
Individual line items within a declaration representing distinct products.
- **Classification**: HS Code (Harmonized System tariff code), commodity description
- **Values**: Commodity value, duty amount, supplementary quantities
- **Origin/Treatment**: Country of origin, preference codes, treatment applications
- **Linkages**: Maps back to shipment ID and invoice number via Form Type references (SHP, INV, TRK)

#### Invoice
Commercial invoice associated with shipment.
- **Invoice Number**: Unique identifier from shipper
- **Currency**: Original currency and converted value (to AUD/SGD)
- **Line Items**: Commodity details, quantities, unit prices
- **Charges**: Freight, insurance, discount allocations

#### Consignment
Grouping of related shipments for consolidated processing.
- **CONSREF#**: Consolidation Reference Number generated based on matching criteria
- **Virtual Shipment**: Temporary shipment record (prefixed with #) representing consolidated group
- **Lead Shipment**: Primary shipment within consolidation used for customs submission
- **Child Shipments**: Related shipments rolled up into virtual declaration

#### Party
Entities involved in the shipment transaction.
- **Types**: Shipper, Consignee, Importer of Record (IOR), Broker, Carrier
- **Identifiers**: TIN (Australian Business Number), OEM Client ID (Client Code ID), Tax ID
- **Attributes**: Name, address, contact details, postal code

---

## 5. User Roles and Permissions

### Front Office Roles

**Broker (BRK)**
- Primary role for declaration processing
- Can create, edit, and submit declarations to customs
- Handles document attachment and commodity classification
- Resolves exceptions and holds
- Consolidation of shipments assigned to BRK can proceed

**Administrator (ADM)**
- Configures system parameters including consolidation criteria
- Manages reference data (tariff codes, ports, countries)
- Sets up user roles and permissions
- Monitors system health and integration status
- Consolidation of shipments assigned to ADM can proceed

**Compiler (COM)**
- Prepares declarations for review before broker submission
- Validates data completeness and accuracy
- Attaches supporting documents via BDIS
- Consolidation of shipments assigned to COM can proceed

### Back Office Roles
- **Operations Supervisor**: Assigns shipments to front office staff, monitors workflow bottlenecks
- **Compliance Officer**: Audits declarations for regulatory adherence
- **Customer Service**: Responds to client inquiries about shipment status

### Assignment Logic (AST)
- Shipments must be assigned (AST) to a front office role (BRK/ADM/COM) before they can be consolidated
- Consolidation criteria can include AST status as a parameter to ensure only properly assigned shipments are grouped
- Unassigned shipments (back office) are excluded from consolidation until assignment occurs

---

## 6. Consolidation Reference System

### CONSREF# (Consolidation Reference Number)
A dynamically generated identifier that groups shipments with matching attributes for consolidated declaration submission.

#### Purpose
- Combine multiple low-value or related shipments into a single declaration
- Reduce processing overhead and submission costs
- Ensure accurate duty/tax calculation on aggregated values
- Prevent tax evasion on de minimis thresholds

#### Generation Logic
CONSREF# is constructed by parsing and concatenating values from configured matching parameters:

| Parameter | Max Chars | Parsing Rule | Example |
|-----------|-----------|--------------|---------|
| Importer Account # | 6 | Last 6 chars (exclude leading zeros) | 123456 |
| Movement Number | 8 | Full value | 12345678 |
| MAWB | 8 | Last 8 digits | 81542000 |
| Import Date | 8 | DDMMYYYY | 18032024 |
| Transit Country | 2 | Full country code | SG |
| Clearance Type | 3 | Full code | DEM |
| Shipment Indicator | Variable | Full value | TTR |
| Forecast Status | 6 | Literal value | FORCMP |

**Maximum Length**: 35 characters (right-trimmed if exceeded)

#### Matching Criteria Sets
Admins configure multiple criteria sets, each with a primary parameter:

- **Set A (Trusted Traders)**: Shipment Indicator (TTR) + Importer Account + Import Date
- **Set B (Batam Transhipments)**: Transit Country (SG/MY) + Destination Country + Import Date
- **Set C (De minimis)**: Clearance Type (DEM) + Import Date + Consignee Telephone + Movement Number
- **Set D (Forecast Complete)**: Forecast Status (FOR=CMP) + Importer Account + MAWB

#### Priority Order
Systems check criteria sets in configured sequence:
- **Australia**: Set A → Set C (Trusted Traders → De minimis)
- **Singapore**: Set B → Set C (Batam → De minimis)

### Virtual Shipments
When consolidation occurs:
1. System creates a **Virtual Shipment** with ID starting with # (e.g., #000000035)
2. Virtual shipment aggregates declaration data from all child shipments
3. **Lead Shipment** (highest value or package count) represents the group in customs submission
4. Child shipments retain individual records but reference virtual/lead via comments and events

---

## 7. Environment and Infrastructure

### Deployment Architecture
- **Production Environment**: Live system processing real customs declarations
- **Testing Environment**: Mirrors production for user acceptance testing and regression testing
- **Development Environment**: Sandbox for feature development and integration testing

### Connectivity
- **CBF TPS Connection**: Secure gateway for Australian customs messaging (encrypted, VPN-tunneled)
- **TradeNet Connection**: Singapore customs network access via certified messaging protocol
- **SBSS Internal Network**: Connects to TCS, MSG-ESM, OLAF, BDIS, MSG-CSA within Carrier Co infrastructure

### Data Residency
- Australian operations: Data stored in Australia to comply with CBF data sovereignty requirements
- Singapore operations: Data stored in Singapore for TradeNet compliance

---

## 8. Glossary of Domain Terms

### Shipment and Routing
- **MAWB**: Master Air Waybill – carrier's shipment identifier
- **HAWB**: House Air Waybill – freight forwarder's sub-shipment identifier
- **1Z Number**: Carrier Co tracking number format
- **PTN**: Package Tracking Number
- **OCC**: Origin Country Code
- **OPC**: Origin Port Code
- **DCC**: Destination Country Code
- **DPC**: Destination Port Code
- **TCC**: Transit Country Code

### Declaration Types and Clearance
- **FML**: Formal Entry – full customs declaration with duty/tax calculation
- **DEM**: De minimis – low-value exemption clearance
- **LND**: Low-value No-Duty – clearance for goods below duty threshold
- **FTZ**: Free Trade Zone – special customs area treatment
- **INF**: Informal Entry (deprecated, replaced by DEM)

### Brokerage Status Codes
- **RAT**: Rating Status (duty calculation stage)
- **EBC**: E-Broker Complete (internal validation complete)
- **CUS**: Customs Status (submission and response tracking)
- **FOR**: Forecast Status (document scanning/preparation stage)
- **AST**: Assignment Status (which user role owns the shipment)
- **MAN**: Manual Review Status (customs hold for examination)

### CUS Status Values
- **SNT**: Sent to Customs
- **SCR**: Successfully Received (acknowledgment from customs)
- **SCA**: Scanning Required (customs document request)
- **APP**: Approved
- **ERR**: Error (validation failure)
- **REJ**: Rejected (customs refusal)
- **INS**: Inspection Required
- **CLR**: Cleared for Release
- **CMP**: Complete

### RAT Status Values
- **CMP**: Complete (rating calculation finished)
- **RFC**: Ready for Customs (validation passed, ready to submit)
- **HLD1**: Hold Level 1 (pending additional information)

### Exception and Hold Codes
- **RTS**: Return to Sender
- **RTO**: Return to Origin
- **BYP**: Bypass (skip validation)
- **HLD1**: Hold Level 1 (broker action required)
- **1U**: Specific exception reason code (customs query)
- **NST**: Not Sent (message content validation failed)
- **NRQ**: No Request (declaration not yet submitted)

### Message Types
- **INPDEC**: Import Payment Declaration
- **IPTDEC**: Import Payment (Treatment-specific) Declaration
- **TNPDEC**: Transhipment Declaration
- **INPUPD**: Import Payment Update
- **IPTUPD**: Import Payment (Treatment) Update
- **OUTDEC**: Outbound/Export Declaration
- **COODEC**: Country of Origin Declaration
- **INPPMT**: Import Permit (approval)
- **IPTPMT**: Import Payment Permit
- **TNPPMT**: Transhipment Permit
- **OUTPMT**: Export Permit
- **ERRORM**: Error Message (rejection with reasons)
- **STATUS-R**: Status Request/Response

### Treatment and Preference Codes
- **APS**: ASEAN Preferential Scheme
- **GTR**: General Tariff Rate
- **SHO**: Special Handling Order
- **DES**: Destruction
- **REX**: Re-export
- **GST**: Goods and Services Tax
- **DUT**: Duty Treatment
- **DNG**: Dangerous Goods
- **BKT**: Blanket Treatment

### Parties and Identifiers
- **IOR**: Importer of Record (entity legally responsible for duties)
- **COD**: Consignee on Demand
- **TIN**: Australian Business Number
- **OEM Client ID**: Client Code ID
- **Shipper**: Party sending goods
- **Consignee**: Party receiving goods
- **Broker**: Customs agent filing declarations

### Consolidation Terms
- **CONSREF#**: Consolidation Reference Number
- **VRT/VID**: Virtual Shipment ID (prefixed with #)
- **Lead Shipment**: Primary shipment in consolidated group
- **Child Shipment**: Individual shipment rolled into consolidation
- **TTR**: Trusted Trader (shipment indicator for preferential processing)
- **Batam**: Singapore-Indonesia transhipment consolidation scenario

### Document Types
- **INV**: Commercial Invoice
- **SHP**: Shipment Identifier reference
- **TRK**: Tracking Number reference
- **CA**: Customs Acknowledgment
- **SC**: Special Conditions
- **AFD**: Additional Fee Detail
- **ERD**: Error Detail

### Financial and Charges
- **COD**: Cash on Delivery
- **FRT**: Freight Charges
- **INS**: Insurance
- **DIS**: Discount
- **UNS**: Upload Not Sent (charge file suppression flag)
- **Sigma**: Summation/total across multiple shipments

### Reference Numbers
- **CRN**: Customs Reference Number (SBSS-assigned)
- **CCN**: Customs Control Number (CBF-assigned)
- **PMT#**: Permit Number
- **PMTSD**: Permit Start Date
- **PMTED**: Permit End Date
- **CAADT**: Customs Approval Actual Date/Time
- **PMADT**: Permit Actual Date/Time
- **DESDT**: Declaration Submission Date/Time

### Shipment Status Codes
- **03**: In-Process/Submitted
- **04 DA**: Cleared/Delivered Available

---

## 9. Regulatory Context

### Customs Border Force (CBF) Requirements
- All import/export declarations must be lodged electronically via TPS
- Declarations must include accurate HS codes for tariff classification
- Supporting documents (invoices, certificates) must be retained for 5 years
- Duty and GST calculations must follow CBF guidelines
- Permit numbers must be obtained before cargo release

### Singapore Customs (TradeNet) Requirements
- Transhipment declarations required for cargo moving through Singapore en route to final destination
- De minimis thresholds apply for low-value goods
- Preferential tariff treatment requires certificates of origin
- TradeNet messaging must conform to UN/EDIFACT standards

### Compliance Obligations
- Brokers must hold valid customs broker licenses
- Declarations must be signed electronically by authorized brokers
- Tariff classification errors can result in penalties and audits
- False declarations are criminal offenses
- Record retention: 5-7 years depending on jurisdiction

---

title: Customs Brokerage Process Flow
content: ## Import and Export Clearance for Carrier Co Australia

---

## 1. Import Declaration Process (End-to-End)

### Phase 1: Shipment Arrival and Manifest Download

**Trigger**: Cargo arrives at destination airport/port or manifest is transmitted by carrier

**Activities**:
1. **Manifest Receipt**: TCS receives cargo manifest from airline/carrier with shipment details (MAWB, HAWB, consignee, origin, declared value, weight)
2. **Shipment Download to SBSS**: Manifest data flows from TCS to SBSS, creating shipment records
3. **Consolidation Matching** (if configured):
   - SBSS checks shipment attributes against configured consolidation criteria (importer account, import date, transit country, clearance type, etc.)
   - If matching criteria are met, SBSS generates CONSREF# and groups shipments
   - Admin reviews consolidation report showing CONSREF# and matching shipment counts
4. **Assignment**: Shipment assigned (AST) to broker (BRK), compiler (COM), or admin (ADM) role in front office

**Key Data Captured**:
- Shipment ID, MAWB, HAWB, tracking number
- Parties (shipper, consignee, importer)
- Declared value, weight, package count
- Routing (origin, destination, transit points)
- Movement number, flight/vessel details

**System Interactions**: TCS → SBSS → (Optional) Consolidation Engine

---

### Phase 2: Document Receipt and Validation

**Trigger**: Shipment assigned to broker for processing

**Activities**:
1. **Document Collection**:
   - Broker receives commercial invoice, packing list, certificate of origin, permits (if applicable) from shipper/consignee
   - Documents scanned and uploaded to BDIS
2. **Document Attachment**:
   - Broker attaches BDIS images to shipment declaration
   - For consolidated shipments, documents from all child shipments are merged and grouped by shipment ID in virtual declaration
3. **Forecast Status Update**:
   - Forecast status (FOR) updated to CMP (Complete) once all documents received and scanned
   - If consolidation criteria include FOR=CMP, shipments only receive CONSREF# after forecast completion

**Key Validations**:
- Invoice value matches declared shipment value
- HS codes are present and valid
- Required certificates/permits are attached
- Consignee/importer details are accurate

**System Interactions**: Broker → BDIS → SBSS

---

### Phase 3: Declaration Preparation

**Trigger**: FOR=CMP and broker begins declaration entry

**Activities**:
1. **Clearance Type Determination**:
   - **FML (Formal)**: Shipment value exceeds de minimis threshold or specific commodity requires formal entry
   - **DEM (De minimis)**: Low-value consignments below duty threshold
   - **LND (Low-value No-Duty)**: Goods below duty threshold but requiring GST
   - **FTZ**: Free trade zone clearance for bonded warehouses
2. **Declaration Data Entry**:
   - Broker enters/validates summary data (declaration type, clearance type, gross weight, declared value)
   - Customer details (shipper, consignee, importer with TIN/tax ID)
   - Movement details (arrival date, import date, movement number, port codes)
   - Charges (freight, insurance, other charges)
3. **Commodity Classification**:
   - Broker assigns HS code to each commodity line
   - Enters commodity description, quantity, unit value
   - Specifies country of origin
   - Applies treatment codes (APS/GTR/SHO/DES/REX) if preferential tariff treatment claimed
   - Attaches licenses, permits, or quota references if required
4. **Invoice Processing**:
   - For single-currency invoices: Use original declared value
   - For multi-currency invoices: SBSS converts all values to local currency (AUD/SGD) using declaration exchange rate
   - For consolidated shipments with mixed currencies: Use declaration total value (already in AUD/SGD) as baseline
5. **Consolidation (if applicable)**:
   - Broker selects matching shipments via CONSREF# from consolidation report
   - SBSS creates **Virtual Shipment** (ID starting with #) combining:
     - All commodity lines from child shipments
     - Sigma (sum) of declared values, gross weights, freight, insurance, duties
     - Common clearance type (FML forced for consolidated declarations)
     - Form type references (SHP, INV, TRK) mapping commodities back to original shipment IDs
   - **Lead Shipment** selected (highest value or package count) as primary reference for customs submission
   - Virtual shipment's declaration number (CRN) assigned

**Key Calculations**:
- **Declaration Total Value**: Sum of all invoice values (converted to local currency if needed)
- **Statistical Value**: Includes freight and insurance added to invoice value
- **Duty Amount**: Calculated per commodity line based on HS code tariff rate
- **GST/Tax**: Applied to sum of invoice value + freight + insurance + duty

**System Interactions**: SBSS (Declaration Module) → Rating Engine → Tariff Database

---

### Phase 4: Rating and Validation

**Trigger**: Broker completes data entry and initiates rating

**Activities**:
1. **Rating Calculation**:
   - SBSS rating engine retrieves tariff rates for each HS code
   - Calculates duty per commodity line (value × tariff rate)
   - Applies treatment codes (preferential rates if applicable)
   - Calculates GST on dutiable value
   - Computes total declaration charges
2. **Internal Validation**:
   - **RAT (Rating) Status** checks:
     - All commodity HS codes valid
     - Quantities and values within acceptable ranges
     - Treatment codes match HS code eligibility
     - Licenses/permits present for restricted goods
   - **EBC (E-Broker Complete) Status** checks:
     - All required fields populated
     - Party details complete (TIN, addresses)
     - Documents attached for all invoice line items
     - No conflicting data between shipment and declaration
3. **Manual Review (if needed)**:
   - High-value shipments flagged for supervisor approval
   - Unusual commodity classifications escalated to compliance officer
   - Missing document exceptions logged

**Status Progression**:
- RAT = CMP → EBC = CMP → CUS = RFC (Ready for Customs)

**System Interactions**: SBSS → Rating Engine → Validation Rules Engine

---

### Phase 5: Submission to Customs

**Trigger**: CUS = RFC and broker approves submission

**Activities**:
1. **Message Generation**:
   - SBSS constructs EDIFACT message based on declaration type and treatment:
     - **INPDEC**: Standard formal import declaration (APS/GTR/SHO/DES/REX)
     - **IPTDEC**: Import declaration with specific duty treatment (GST/DUT/DNG/BKT)
     - **TNPDEC**: Transhipment declaration (Batam consolidation scenario with inbound/outbound MAWB/HAWB)
   - For consolidated shipments:
     - Message includes all commodity lines (up to 50 per message per TradeNet limit)
     - Each commodity tagged with Form Type references (SHP/INV/TRK) to original shipment
     - Lead shipment HAWB used as declaration reference (virtual # ID not sent to customs)
2. **Message Transmission**:
   - SBSS sends message to TPS (Australia) or TradeNet (Singapore)
   - **CUS Status** updated to **SNT (Sent)**
   - Shipment status updated to **03** (In-Process)
   - Exception reason code **1U** logged (message sent, awaiting response)
   - **MAN Status** updated to **NST (Not Sent)** if validation fails, or **NRQ (No Request)** if awaiting customs action
3. **Acknowledgment**:
   - Customs system sends synchronous acknowledgment (message received and queued)
   - **CUS Status** updated to **SCR (Successfully Received)**
   - Broker sees confirmation in SBSS that declaration is in customs processing queue

**System Interactions**: SBSS → TradeNet/TPS Gateway → Customs Backend

---

### Phase 6: Customs Processing and Response

**Customs Processing (External)**:
1. Customs system validates message format and content
2. Runs risk assessment algorithms
3. Routes to officer review if flagged, otherwise auto-processes
4. Generates permit or rejection

**SBSS Receives Response**:

#### Scenario A: Approval (Permit Issued)

**Inbound Message**: INPPMT, IPTPMT, or TNPPMT

**Activities**:
1. SBSS receives permit message with:
   - **CCN (Customs Control Number)**: Official customs identifier
   - **Permit Number (PMT#)**: Authorization to release cargo
   - **PMTSD/PMTED**: Permit validity period
   - **CAADT/PMADT**: Customs approval date/time
2. **CUS Status** updated to **APP (Approved)**
3. **Shipment Status** updated to **04 DA (Cleared/Delivered Available)**
4. **For Consolidated Shipments**:
   - Virtual shipment receives response first
   - SBSS automatically syncs permit details to **Lead Shipment** declaration
   - Lead shipment's permit details copied to all **Child Shipments** via:
     - Declaration > Customs > Reference Number fields (CCN, PMT#, PMTSD, PMTED, CAADT, PMADT)
     - Shipment comments (Customer Supplied References synced)
     - Brokerage events logged for each child ("Consolidated under Shipment ID XXXXX", "Permit received: PMT#")
5. **Charge File Generation**:
   - For consolidated shipments, single charge file generated from **Lead Shipment** only
   - Charge file includes:
     - Government charges (duties, GST) from declaration
     - Accessorial charges (brokerage fees, document fees) from shipment
   - Child shipments flagged **UNS (Upload Not Sent)** to suppress duplicate charge files
6. **Document Update**:
   - Approved declaration form (PDF) generated and attached to BDIS
   - For consolidated shipments, declaration form available on virtual, lead, and all child shipments
7. **Release Notification**:
   - SBSS notifies MSG-CSA that shipment is cleared for release
   - Warehouse receives pickup authorization

**System Interactions**: TPS/TradeNet → SBSS → Lead/Child Shipments → MSG-CSA → Billing System

---

#### Scenario B: Rejection (Declaration Refused)

**Inbound Message**: ERRORM or STATUS-R with rejection codes

**Activities**:
1. SBSS receives rejection message with:
   - Error codes and descriptions (e.g., "HS code invalid", "Missing license", "Value discrepancy")
   - Line-level or declaration-level errors
2. **CUS Status** updated to **ERR (Error)** or **REJ (Rejected)**
3. **Shipment Status** remains **03** (not cleared)
4. **MAN Status** may update to specific hold code indicating reason
5. Broker reviews error details in SBSS
6. **For Consolidated Shipments**:
   - If entire declaration rejected, all child shipments receive rejection status sync
   - If specific commodity lines rejected, broker identifies faulty child shipments via Form Type SHP references
7. **Exception Handling**:
   - Broker corrects errors (amends HS code, uploads missing document, adjusts value)
   - Broker submits **INPUPD/IPTUPD** (update message) with corrections
   - Or broker withdraws declaration and resubmits fresh INPDEC

**System Interactions**: TPS/TradeNet → SBSS → Broker Worklist

---

#### Scenario C: Hold for Inspection

**Inbound Message**: Customs inspection request or SCA (Scanning Required) status

**Activities**:
1. SBSS receives hold notification
2. **CUS Status** updated to **INS (Inspection Required)** or **SCA (Scanning Required)**
3. **MAN Status** updated to **HLD1** or specific hold code
4. Broker coordinates with customs officer:
   - Provides additional documents
   - Arranges physical inspection at cargo facility
   - Responds to queries via SBSS messaging or email
5. Once inspection cleared:
   - Customs issues permit (follows Scenario A path)
   - Or requests amendment (follows Scenario B path)

**System Interactions**: SBSS ↔ Customs Officer (manual intervention) → TPS/TradeNet

---

### Phase 7: Cargo Release and Finalization

**Trigger**: CUS = APP and shipment status = 04 DA

**Activities**:
1. **Cargo Release**:
   - MSG-CSA receives clearance notification from SBSS
   - Warehouse releases cargo to consignee or delivery carrier
   - Physical handover confirmed in MSG-ESM
2. **Billing**:
   - Charge file sent to billing system
   - Customer invoiced for duties, GST, brokerage fees, accessorial charges
   - For consolidated shipments, single invoice issued to lead shipment's importer
3. **Record Archival**:
   - Declaration, commodity, document, and message data archived in BSIS
   - Retention period: 5 years minimum for compliance
4. **Reporting**:
   - BCR generates reports for:
     - Daily clearance volumes
     - Duty/GST collected
     - Exception rates (rejections, holds)
     - Consolidation efficiency metrics

**System Interactions**: SBSS → MSG-CSA → MSG-ESM → Billing System → BSIS/BCR

---

## 2. Export Declaration Process (End-to-End)

### Phase 1: Booking and Shipment Creation

**Trigger**: Customer books export shipment via OLAF or Carrier Co sales channel

**Activities**:
1. **Booking Entry**:
   - Customer provides shipper, consignee, commodity details, destination, requested pickup date
   - OLAF creates shipment booking record
2. **Manifest Generation**:
   - OLAF transmits shipment manifest to SBSS
   - SBSS creates export shipment record
3. **Assignment**:
   - Shipment assigned to broker for export declaration preparation

**Key Data Captured**:
- Shipper (exporter), consignee (foreign buyer)
- Commodity descriptions, HS codes, values
- Export country, port of exit, destination country
- Export date, outbound movement number

**System Interactions**: OLAF → SBSS

---

### Phase 2: Export Declaration Preparation

**Trigger**: Broker receives assigned export shipment

**Activities**:
1. **Document Collection**:
   - Commercial invoice, packing list, certificate of origin (if required)
   - Export licenses/permits for controlled goods
2. **Declaration Entry**:
   - Export declaration type selected (OUTDEC or COODEC)
   - Commodity classification (HS code, description, quantity, value)
   - Party details (exporter with TIN, foreign consignee)
   - Movement details (export date, port of exit, vessel/flight details)
3. **Validation**:
   - Restricted/prohibited goods checks
   - Export license verification for dual-use or strategic goods
   - Country-of-origin certification for FTA eligibility

**System Interactions**: SBSS → Tariff/License Database

---

### Phase 3: Submission to Customs

**Trigger**: Broker completes and approves export declaration

**Activities**:
1. **Message Generation**:
   - **OUTDEC**: Standard export declaration
   - **COODEC**: Certificate of origin declaration (for FTA preferential treatment in destination country)
2. **Transmission**:
   - SBSS sends message to TPS/TradeNet
   - **CUS Status** = **SNT**
   - **Shipment Status** = **03**
   - **Exception Reason** = **1U**
3. **Acknowledgment**:
   - Customs acknowledges receipt
   - **CUS Status** = **SCR**

**System Interactions**: SBSS → TPS/TradeNet

---

### Phase 4: Customs Response and Clearance

#### Scenario A: Approval

**Inbound Message**: OUTPMT or COODCI

**Activities**:
1. SBSS receives export permit
2. **CUS Status** = **CLR (Cleared)**
3. **Shipment Status** = **04 DA**
4. Cargo released for loading onto vessel/aircraft
5. Permit attached to shipment for carrier handover

**System Interactions**: TPS/TradeNet → SBSS → MSG-CSA

---

#### Scenario B: Hold/Rejection

**Inbound Message**: ERRORM or hold notification

**Activities**:
1. **CUS Status** = **ERR** or **HLD (Hold)**
2. Broker coordinates with customs officer to resolve issue
3. Submits correction via **OUTUPD** (export update message)
4. Re-approval follows Scenario A path

---

### Phase 5: Departure and Finalization

**Trigger**: Cargo loaded and vessel/aircraft departs

**Activities**:
1. Carrier confirms departure to SBSS
2. Final shipment status updated in MSG-ESM
3. Export declaration archived in BSIS
4. Customer invoiced for brokerage fees

**System Interactions**: Carrier → MSG-ESM → SBSS → BSIS

---

## 3. Message Lifecycle (Import/Export)

### Creation
- Declaration data entered in SBSS UI
- SBSS constructs EDIFACT message per customs schema (INPDEC/IPTDEC/TNPDEC/OUTDEC/COODEC)

### Validation
- **Pre-Submission**: SBSS validates message structure, mandatory fields, data types
- **Post-Submission**: Customs performs content validation (HS codes, party IDs, value ranges)

### Submission
- SBSS transmits message via secure gateway to TPS/TradeNet
- CUS = SNT, Shipment Status = 03, Exception = 1U

### Response Handling
- **Acknowledgment**: SCR (message received, queued for processing)
- **Approval**: APP → Permit issued (INPPMT/IPTPMT/TNPPMT/OUTPMT/COODCI)
- **Rejection**: ERR/REJ → Error message returned (ERRORM)
- **Hold**: INS/SCA/HLD1 → Manual review required

### Status Update
- **Synchronous**: Immediate feedback (acknowledgment, validation errors)
- **Asynchronous**: Delayed response (permit issuance after officer review, can take minutes to hours)

### Retry/Amendment
- If rejected, broker submits update message (INPUPD/IPTUPD/OUTUPD)
- Or withdraws and resubmits fresh declaration

---

## 4. Clearance Types

### FML (Formal Entry)
- **Trigger**: Shipment value exceeds de minimis threshold, or commodity requires formal declaration (e.g., restricted goods)
- **Process**: Full declaration with commodity classification, duty calculation, GST
- **Outcome**: Permit issued after customs approval, duties/taxes payable

### DEM (De minimis)
- **Trigger**: Low-value consignments (typically below AUD 1,000 or SGD 400)
- **Process**: Simplified declaration, often auto-cleared
- **Outcome**: No duty, minimal GST or exempt, faster clearance

### LND (Low-value No-Duty)
- **Trigger**: Goods below duty threshold but subject to GST
- **Process**: Declaration focuses on GST calculation, no tariff classification needed
- **Outcome**: GST payable, no customs duty

### FTZ (Free Trade Zone)
- **Trigger**: Cargo entering bonded warehouse or FTZ for storage/re-export
- **Process**: Suspended duty/tax until cargo leaves FTZ for domestic consumption
- **Outcome**: No immediate payment, duty deferred or waived on re-export

### Alternate Broker / Transhipment
- **Trigger**: Cargo transiting through country en route to final destination (e.g., Batam scenario: air into Singapore, sea out to Indonesia)
- **Process**: Transhipment declaration (TNPDEC) with inbound and outbound MAWB/HAWB
- **Outcome**: Permit issued for cargo to continue journey, no import duty (cargo not entering commerce)

---

## 5. Exception Handling

### Rejection Scenarios
- **HS Code Invalid**: Tariff code not found or incorrect format → Broker corrects and resubmits
- **Missing License**: Restricted goods require permit → Broker uploads permit document and updates declaration
- **Value Discrepancy**: Invoice value doesn't match declared value → Broker amends value or provides explanation
- **Party Details Incorrect**: TIN invalid, address incomplete → Broker updates party data

### Hold Scenarios
- **Document Request (SCA)**: Customs officer requests additional supporting documents → Broker uploads to BDIS and notifies customs
- **Physical Inspection (INS)**: Cargo examination required → Broker coordinates inspection appointment with warehouse and customs
- **Valuation Query (HLD1)**: Declared value under question → Broker provides additional invoices or valuation evidence

### Refund/Withdrawal
- **Overpayment**: Duty calculated incorrectly → Broker submits refund request with corrected calculation
- **Withdrawal (BYP)**: Declaration no longer needed (shipment cancelled, returned to sender) → Broker withdraws declaration, cargo re-exported or destroyed

---

## 6. Status Lifecycle

### Shipment Status Progression
- **Downloaded** → Shipment created in SBSS from manifest
- **Assigned (AST)** → Allocated to broker/compiler/admin
- **03** → Declaration submitted to customs, awaiting response
- **04 DA** → Cleared by customs, available for delivery
- **RTS/RTO** → Return to sender/origin (clearance failed or shipment refused)

### Declaration Brokerage Status Progression
- **RAT**: Rating calculation in progress
  - **RAT = CMP**: Rating complete, duty/tax calculated
- **EBC**: Internal validation in progress
  - **EBC = CMP**: Validation complete, ready for submission
- **CUS**: Customs submission and response tracking
  - **CUS = RFC**: Ready for Customs (internal approval to submit)
  - **CUS = SNT**: Sent to Customs
  - **CUS = SCR**: Successfully Received (acknowledged by customs)
  - **CUS = APP**: Approved (permit issued)
  - **CUS = ERR**: Error (validation failed)
  - **CUS = REJ**: Rejected (declaration refused)
  - **CUS = INS**: Inspection Required
  - **CUS = CLR**: Cleared (export approved)
  - **CUS = CMP**: Complete (all processing finished)

### Forecast Status (FOR)
- **Initiated** → Document scan process started
- **In Progress** → Some documents scanned, others pending
- **CMP (Complete)** → All documents scanned and attached
- **Note**: FOR = CMP may be consolidation criterion trigger

### Manual Review Status (MAN)
- **NST (Not Sent)**: Message content validation failed before submission
- **NRQ (No Request)**: Awaiting broker action to submit
- **HLD1**: Hold for manual review
- **Specific Exception Codes**: 1U (customs query), etc.

---

## 7. Regulatory Context

### CBF Import Requirements
- All cargo arriving in Australia must be declared electronically via TPS before release
- Importers must provide accurate HS codes for tariff classification
- Duty and GST calculated based on customs value (transaction value + freight + insurance)
- Permits required for restricted goods (food, pharmaceuticals, weapons, etc.)
- Supporting documents must be retained for 5 years for audit

### CBF Export Requirements
- Export declarations required for controlled goods (strategic exports, dual-use items)
- Certificate of origin declarations for FTA preferential treatment
- Exporters must provide accurate commodity descriptions and HS codes
- Departure confirmation required before cargo leaves Australian jurisdiction

### Singapore TradeNet Requirements
- All imports/exports/transhipments must be declared via TradeNet
- EDIFACT messaging mandatory for electronic submissions
- Transhipment permits required for cargo moving through Singapore (Batam scenario)
- De minimis threshold: SGD 400 for GST exemption
- Preferential tariff schemes (ASEAN, FTA) require certificates of origin

### Compliance Obligations
- **Broker Licensing**: Customs brokers must hold valid licenses issued by CBF or Singapore Customs
- **Electronic Signatures**: Declarations must be electronically signed by authorized brokers
- **Tariff Classification Accuracy**: Errors in HS codes can result in penalties, back-duty assessments, or criminal charges for deliberate misclassification
- **False Declarations**: Submitting false information (undervaluation, misdescription) is a criminal offense
- **Record Retention**: 5-7 years depending on jurisdiction and commodity type
- **Audit Cooperation**: Brokers must provide records upon request during customs audits

---

## 8. Consolidation-Specific Process Flow

### Identification Phase
1. Shipment downloads from manifest
2. SBSS checks shipment attributes against configured consolidation criteria sets (in priority order)
3. If match found on primary parameter + secondary parameters, CONSREF# generated and assigned
4. Shipment flagged for consolidation (viewable in consolidation matching report)

### Selection Phase
1. Broker reviews consolidation matching report (sorted by CONSREF# with highest shipment count first)
2. Broker selects shipments under same CONSREF# for consolidation
3. SBSS creates Virtual Shipment (# ID) combining:
   - All declarations and commodity lines from selected shipments
   - Sigma values (declared value, gross weight, freight, insurance, duties)
   - Common clearance type (forced to FML for consolidated submissions)
   - Form Type references mapping commodities to original shipment IDs

### Lead Shipment Assignment
1. SBSS selects lead shipment (highest value, or highest package count if values equal, or alphabetically if tied)
2. Lead shipment HAWB becomes declaration reference (virtual # ID not sent to customs)

### Submission Phase
1. Virtual declaration submitted to customs (INPDEC/IPTDEC/TNPDEC)
2. Message includes all commodity lines with Form Type tags (SHP/INV/TRK)
3. CUS = SNT on virtual, lead, and child shipments

### Response Sync Phase
1. Customs response received on virtual shipment
2. SBSS syncs permit details to lead shipment
3. Lead shipment syncs to all child shipments:
   - Declaration references (CCN, PMT#, dates)
   - Customer supplied references
   - Brokerage events
   - CUS and shipment status
   - Exception codes

### Charge File Phase
1. Single charge file generated from lead shipment
2. Child shipments flagged UNS to suppress duplicate billing

### Document Availability Phase
1. Approved declaration form available on virtual, lead, and all child shipments
2. BDIS documents merged and tagged by originating shipment ID

---

This process flow serves as the operational blueprint for all customs brokerage activities in SBSS. Brokers, compilers, and administrators follow these workflows to ensure compliant, efficient, and accurate customs clearance for Carrier Co customers.

title: Project-A - Import / Export - Reference File Processing (2) — Feature Brief
content: ## 1. Feature Title and Business Need

**Feature**: Reference File Processing for Australian Customs Integration

**Business Problem**: Carrier Co Australia customs brokers require up-to-date reference data from the Customs Border Force (CBF) Trade Processing System (TPS) to ensure all declarations submitted through SBSS are compliant with current regulatory requirements. Reference data includes tariff codes, exchange rates, exemption codes, duty calculation methods, quantity conversions, and various regulatory parameters that change frequently. Manual updates are error-prone, labor-intensive, and create compliance risk when declarations are prepared using outdated reference data.

**Current State Pain Points**:
- Brokers manually check TPS website for reference file updates
- Reference data in SBSS becomes stale between manual updates
- Declarations may be rejected due to use of expired codes or incorrect tariff classifications
- No systematic process to filter expired reference entries from broker workflows
- Tariff calculation errors due to outdated rate tables or missing conversion factors
- Export declaration exemption logic not automated, leading to submission errors
- Underbond movement creation requires manual lookup of establishment codes with risk of data entry errors
- Cargo reporting manifest types not automatically derived from transport mode

## 2. Business Objectives

**Primary Objectives**:
1. Automate daily ingestion of all TPS reference files to ensure SBSS operates with current regulatory data
2. Eliminate manual reference data maintenance for customs compliance parameters
3. Reduce declaration rejection rates caused by use of expired or invalid reference codes
4. Enable brokers to make compliant clearance type and exemption code selections based on current regulations
5. Support accurate tariff calculation through timely updates to duty rates, conversions, and instrument mappings
6. Automate export declaration exemption code assignment based on shipment value thresholds
7. Provide establishment code lookups for underbond movement creation (import and export scenarios)
8. Automate cargo report manifest type derivation based on mode of transport

**Success Metrics**:
- 100% of TPS reference files automatically downloaded and processed daily by 8 AM Sydney time
- Zero broker-initiated manual reference file updates required
- Expired reference entries automatically filtered from all SBSS UI dropdown lists and search results
- Declaration rejection rate due to invalid reference codes reduced by 80% within 3 months
- Tariff calculation discrepancies due to stale rate data reduced to zero
- Export declarations with shipment value < AUD $2,000 automatically assigned exemption code EXLV with 100% accuracy
- Underbond movement creation time reduced by 50% through establishment code dropdown population
- Cargo report manifest type auto-population accuracy at 100% for air (MOT=6) and sea (MOT=11) shipments

## 3. Scope

### In Scope

**Reference File Categories**:
- **Tariff Structure Files**: HS code hierarchies, tariff descriptions, commodity classifications, statistical codes
- **Duty Calculation Files**: Tariff rate periods, ad valorem rates, specific rates (quantity-based, second quantity, other duty factors), duty calculation method mappings, charge specifications
- **Preference and Treatment Files**: Preference scheme snapshots (ASEAN, FTA rules), preference rule snapshots, preference country mappings, preference tariff group assignments, preference scheme rules, instrument categories, instrument definitions, instrument-tariff linkages, instrument category-tariff linkages, community protection risk codes
- **Regulatory and Compliance Files**: Lodgement question requirements, GST exemption codes, export declaration exemption codes (embedded in SBSS configuration, not TPS file), AQS commodity restrictions, community protection profiles, establishment ID registry (ports, depots, warehouses)
- **Operational Reference Files**: Exchange rates (daily), quantity unit of measurement conversions, cargo report manifest type mappings (embedded in SBSS configuration, not TPS file)

**Processing Requirements**:
- Daily automated download from TPS website via BSIS intermediary
- XML message-based file transfer from BSIS to SBSS with encoded file content
- Parsing and validation of pipe-delimited and fixed-width reference file formats
- Update or insert operations into SBSS Admin reference tables and custom code value tables
- Automatic filtering of expired reference entries (where end date < current system date) from all UI search results and dropdown lists
- Special handling for tariff file dependencies: if any of 8 core tariff files updated, all 8 must be reprocessed together
- Separate processing jobs for import-related and export-related file groups
- Retry logic with email notification to SBSS Support after 3 failed processing attempts
- Manual trigger mechanism for ad-hoc file re-processing after issue resolution
- Manual seeding capability for initial reference data population before automated processing begins

**User Interface Changes**:
- Reference data search screens in SBSS Admin automatically exclude expired entries
- Shipment and declaration UI dropdown lists filter out expired codes in real-time
- Export declaration exemption code (EEC) auto-populated based on shipment value thresholds with mandatory manual selection for high-value shipments
- Cargo report manifest type (CRTYP) auto-populated based on mode of transport (air/sea)
- Origin/Destination Establishment ID lookups for underbond movements with formatted display (2,456 establishment codes from TPS Establishment Registry)
- Validation popups and save restrictions for export exemption code compliance (EXCC requires CCN, high-value shipments require manual EEC selection)

**Automatic Reference Derivation**:
- EDEC reference automatically derived from EEC selection (non-editable, auto-updates with EEC changes)
- ORESTB reference automatically derived from OED selection (frozen, changes only when OED re-selected)
- DNESTB reference automatically derived from DED selection (frozen, changes only when DED re-selected)
- CRTYP reference automatically derived from MOT (updates automatically when MOT changed manually)

### Out of Scope

- Verification that reference files are correctly published to TPS website by CBF (upstream dependency)
- Functional testing of how specific reference codes are used in declaration workflows (covered in separate features for declaration processing, rating, and submission)
- Historical reference data migration or backfill for periods before automated processing starts
- Real-time reference data updates (system operates on daily batch cycle)
- Reference data conflict resolution when multiple files contain overlapping information (system follows last-update-wins processing order)
- MSG-ESM message creation and GIS segment mapping (separate integration feature)
- TCS to TPS entry packet message structure for underbond requests (separate integration feature)
- Full tariff calculation engine implementation (covered in separate AU Tariff Files Processing feature)

## 4. Stakeholders and Actors

**Primary Stakeholders**:
- **Customs Brokers (BRK)**: Rely on current reference data for compliant declaration preparation, commodity classification, treatment code selection, and underbond movement creation
- **Compilers (COM)**: Use reference data during declaration validation and document attachment workflows
- **Administrators (ADM)**: Manage SBSS Admin reference tables, monitor file processing success, trigger manual re-processing when needed, perform initial seeding of reference data
- **SBSS Support Team**: Receives failure notifications, investigates processing errors, coordinates manual file re-ingestion, executes manual seeding operations

**Secondary Stakeholders**:
- **Operations Supervisors**: Monitor downstream impact of reference data updates on declaration approval rates and processing times, oversee underbond processing efficiency
- **Compliance Officers**: Audit that current regulatory reference data is used in all declarations, verify exemption code compliance for export shipments
- **Carrier Co Customers (Importers/Exporters)**: Indirectly benefit from reduced declaration errors, faster clearance times, and accurate exemption code assignments
- **Warehouse/Depot Operators**: Benefit from accurate underbond movement instructions with correct origin/destination establishment codes

**External Parties**:
- **Customs Border Force (CBF)**: Publishes reference files to TPS website, maintains establishment code registry (2,456 ports/depots/warehouses)
- **BSIS (Integration Middleware)**: Crawls TPS website, fetches files, transmits to SBSS via XML messages, provides manual seeding trigger
- **TPS (Trade Processing System)**: Hosts reference files in Main and Change directories

## 5. Dependencies

**Technical Dependencies**:
- BSIS must be configured to crawl TPS reference file webpage daily at 7 AM Sydney time
- HTTPS connectivity between BSIS and TPS website must be stable and authorized
- XML message schema for reference file transfer (xmlCustomsMessage with encoded file content) must be implemented in BSIS and SBSS
- SBSS must have processing handlers for all reference file types specified in Interface Control Document (ICD)
- SBSS Admin reference tables and custom code value tables must have correct schema to store all file formats
- SBSS UI must have field mappings to reference tables for dropdown population
- Manual seeding switch/trigger must be implemented in BSIS for initial data population

**Data Dependencies**:
- CBF must publish reference files to TPS website on schedule (varies by file type: daily, weekly, ad-hoc)
- Reference file naming conventions must remain stable (e.g., "XCHGRATE-P1-EDCHNG-[datetime].txt")
- File format specifications (delimiter, column order, data types) must match ICD definitions
- Tariff file interdependencies must be understood: 8 core files must be reprocessed together whenever any one updates
- Establishment code registry must contain all 2,456 active port/depot/warehouse codes before underbond functionality goes live

**Operational Dependencies**:
- Manual seeding of reference tables required before go-live for initial system setup
- SBSS Support team must have access to manual trigger mechanism for ad-hoc file re-processing and initial seeding
- Email notification system must be configured to alert SBSS Support on processing failures
- Code migration from brokerage_boss_country_AU to brokerage_boss_lib_Messages must be completed to support centralized reference file handling
- Export exemption code (EEC) configuration must be loaded into SBSS Custom Code Value tables before export declaration processing goes live
- Cargo report manifest type (CRTYP) configuration must be loaded into SBSS Shipment Reference lookups before cargo reporting goes live

**Upstream Feature Dependencies**:
- Feature "Project-A Import/Export Registry and Reference Files" (identification of all required files) must be completed
- Feature "AU Tariff Files Processing" (complex tariff calculation logic) is separate but relies on reference files processed here
- ICD document must be updated to include all implemented reference files (including QNTUTCNV, Establishment Registry, EEC, CRTYP)

## 6. Success Criteria

### Business Success Criteria

1. **Automation Completeness**: All 30+ reference files identified in ICD are automatically downloaded and processed daily without manual intervention
2. **Data Freshness**: Reference data in SBSS is never more than 24 hours behind TPS publication schedule
3. **Operational Efficiency**: Broker time spent on reference data management reduced by 90%
4. **Compliance Assurance**: 100% of declarations use reference data that was current as of submission date
5. **Error Reduction**: Declaration rejection rate due to invalid reference codes below 2% (down from 10% baseline)
6. **Export Exemption Automation**: All export shipments < AUD $2,000 automatically assigned EXLV exemption code; all shipments ≥ AUD $2,000 require manual EEC selection with save restriction enforced
7. **Underbond Processing Efficiency**: Brokers can select origin/destination establishment codes from 2,456-entry dropdown within 5 seconds; underbond movement creation time reduced by 50%
8. **Cargo Reporting Accuracy**: Manifest type auto-population achieves 100% accuracy for air and sea shipments

### Technical Success Criteria

1. **File Processing Success Rate**: >99% of daily file download attempts succeed on first try; retry logic handles transient failures
2. **Data Integrity**: All reference file records successfully parsed and inserted/updated in SBSS tables with zero data loss
3. **Performance**: Reference file processing completes within 30 minutes of file availability from BSIS
4. **Expiration Filtering**: Expired entries automatically excluded from all UI components (dropdowns, search results, auto-population logic) without requiring user action
5. **Dependency Handling**: When any of 8 core tariff files update, all 8 are reprocessed together in single job
6. **Monitoring and Alerts**: SBSS Support receives email notification within 15 minutes of any processing failure; notification includes file name, error details, and retry count
7. **Manual Seeding**: BSIS manual seeding switch successfully triggers reference file transmission for initial data population before automated daily processing begins
8. **Establishment Code Display**: Origin/Destination Establishment ID dropdowns display all 2,456 codes with formatted description: `[ID] | [Name] | [Port Code] | [Type]`

### User Acceptance Criteria

1. Brokers can search for tariff codes, exemption codes, preference schemes, and other references in SBSS Admin and see only currently valid entries
2. When a broker opens a commodity line for editing, the HS code dropdown excludes any tariff codes with end dates prior to today
3. Export declarations automatically populate exemption code "EXLV" when shipment value < AUD $2,000; broker must manually select exemption code for higher values
4. When broker attempts to save export shipment ≥ AUD $2,000 without manually selecting EEC, system displays popup: "Please choose an exemption code under EEC" and blocks save (interactive mode only)
5. When broker selects exemption code "EXCC", system validates that CCN reference exists; if missing, displays popup: "For EXCC, CCN reference input missing and is mandatory. Please choose another exemption code." and blocks save
6. EDEC reference automatically populates with selected EEC code value and is non-editable; updates automatically when EEC changes
7. Cargo report manifest type (CRTYP) auto-populates as "C" for air shipments (MOT=6) and "S" for sea shipments (MOT=11); remains blank for other MOT values
8. CRTYP reference updates automatically when MOT is changed manually (triggers on MOT change event, not just shipment download)
9. Origin and Destination Establishment ID dropdowns show all 2,456 port/depot/warehouse codes with formatted descriptions (ID | Name | Port Code | Type)
10. ORESTB reference auto-populates with selected Origin Establishment ID, is frozen/non-editable, and changes only when OED dropdown re-selected
11. DNESTB reference auto-populates with selected Destination Establishment ID, is frozen/non-editable, and changes only when DED dropdown re-selected
12. When exchange rate file updates, all new declarations use updated rates effective immediately
13. Administrators can view reference file processing logs in SBSS Admin showing last successful processing timestamp for each file type
14. When SBSS Support triggers manual seeding via BSIS switch, reference files are transmitted to SBSS and processed successfully for initial data population
15. AHECC file processing validates: Tariff Number ≤ 8 digits, Start Date < current date, End Date > current date, Net Quantity UOM present, Assay Required Indicator = Y/N, Description ≤ 253 chars
16. When AHECC Assay Required Indicator = Y, system creates Tariff Warning entry with code "ARI - Assay Required"
17. QNTUTCNV file processing creates reciprocal conversions: if CF → CU = 0.02831600, then CU → CF = 35.31466672 (1/0.02831600)
18. UOM Conversions table displays Source UOM, Target UOM, and Conversion Factor for all loaded conversions
19. Expired reference entries are filtered from all UI dropdowns, search results, and auto-population logic by default
20. Admin reference search screens include "Show Expired Entries" toggle to view historical data with visual indicator (red text/strikethrough)

## 7. Assumptions and Risks

### Assumptions

1. **CBF Stability**: CBF will maintain TPS reference file publication schedule and naming conventions without breaking changes
2. **Network Reliability**: HTTPS connectivity between BSIS and TPS will be available during daily 7 AM processing window
3. **File Format Consistency**: Reference file formats (pipe-delimited, fixed-width) will remain consistent with ICD specifications
4. **BSIS Capacity**: BSIS infrastructure can handle daily crawling, encoding, and transmission of 30+ reference files without performance degradation
5. **Processing Window**: All reference file processing can complete within 1-hour window before brokers begin daily work at 8 AM
6. **Code Migration**: Migration of reference file processing code from country-specific to shared library will not introduce regressions
7. **Establishment Code Completeness**: TPS Establishment Registry contains all 2,456 required port/depot/warehouse codes before underbond functionality goes live
8. **Exemption Code Applicability**: AUD $2,000 threshold for automatic EXLV assignment remains stable regulatory requirement
9. **Transport Mode Mapping**: MOT=6 (Air) and MOT=11 (Sea) values remain stable for cargo report manifest type derivation
10. **Manual Seeding One-Time**: Manual seeding via BSIS switch is required only once for initial data population; thereafter automated daily processing maintains data currency

### Risks

| Risk | Impact | Probability | Mitigation |
|------|--------|-------------|------------|
| CBF changes file format without notice | High (processing failures, data corruption) | Medium | Implement schema validation before processing; alert on format mismatches; maintain ICD versioning |
| Tariff file dependencies misunderstood | High (incorrect duty calculations) | Medium | Comprehensive analysis of file interdependencies; separate processing job for tariff group; detailed testing with sample files |
| BSIS-TPS connectivity failure | Medium (stale reference data for 1+ days) | Low | Retry logic with 3 attempts; email alerts; manual trigger for catch-up processing; maintain last-known-good reference data |
| Expired entry filtering breaks existing workflows | Medium (user confusion, workflow disruption) | Medium | Phased rollout of filtering logic; user training; grace period where expired entries show with warning indicator |
| Large file processing causes timeout | Medium (incomplete reference data updates) | Low | Optimize parsing and database insert logic; batch commits; monitor processing duration; alert if >30 min |
| Manual trigger mechanism abused | Low (unintended reference data reverts) | Medium | Restrict manual trigger to SBSS Support role; log all manual processing events; require justification in ticket |
| Multi-currency invoice handling for tariff lookups | Medium (calculation errors) | Low | Ensure exchange rate file processed before tariff files; validate rate availability before duty calculation |
| Establishment code count mismatch | Medium (underbond processing failures) | Low | Validate expected count of 2,456 codes on initial seeding; alert if count < 2,400; periodic reconciliation with TPS |
| Export exemption save restrictions bypass | High (non-compliant export declarations submitted) | Low | Enforce save validation at multiple layers (UI, business logic, database constraint); log all EEC validation failures |
| CRTYP auto-population failure | Medium (cargo reporting errors) | Low | Test MOT change event triggers thoroughly; validate CRTYP derivation in all shipment creation/edit scenarios |
| Manual seeding switch misconfiguration | High (reference data not seeded, automated processing fails) | Medium | Document seeding procedures; test seeding in non-production environments; verify data counts post-seeding |
| QNTUTCNV file format parsing errors | Medium (incorrect UOM conversions, duty calculation errors) | Medium | Validate fixed-width position parsing with multiple sample files; test reciprocal conversion accuracy; alert on conversion factor = 0 |

### Regulatory and Compliance Risks

- **Audit Exposure**: Use of expired reference codes in historical declarations may be flagged in CBF audits → Mitigation: Timestamp all reference data changes; log which reference version was active when declaration was created
- **Duty Underpayment**: Incorrect tariff rates or conversion factors cause incorrect duty calculations → Mitigation: Separate tariff processing into dedicated feature with additional validation; reconciliation reports for rate changes
- **Data Sovereignty**: Reference files may contain sensitive regulatory information → Mitigation: Ensure BSIS and SBSS infrastructure hosted in Australia; encrypt file transfers; restrict access to reference data logs
- **Export Compliance**: Incorrect exemption code assignment may result in non-compliant export declarations → Mitigation: Enforce value threshold validation; require manual EEC selection for high-value shipments; log all EEC assignments with justification
- **Underbond Movement Errors**: Incorrect establishment codes may result in cargo delivery to wrong location → Mitigation: Validate establishment codes against TPS registry; provide formatted display to reduce selection errors; log all establishment code selections

---

## 8. Implementation Approach (High-Level)

**Phase 1: BSIS Integration Setup**
- Configure BSIS daily crawl schedule for TPS website (7 AM Sydney time)
- Implement file detection, download, encoding, and XML message transmission logic
- Set up retry mechanism and email notification to SBSS Support
- Implement manual trigger interface for ad-hoc file requests
- Implement manual seeding switch for initial reference data population

**Phase 2: SBSS Receiving and Processing Infrastructure**
- Build XML message handler to receive encoded reference files from BSIS
- Implement parsers for each reference file format (pipe-delimited, fixed-width)
- Create database insert/update logic for Admin reference tables and custom code values
- Implement expiration date filtering in data access layer to exclude expired entries from all queries

**Phase 3: File-Specific Processing Logic**
- Develop processing handlers for each of 30+ reference file types
- Implement tariff file dependency logic (8-file group processing)
- Build special logic for exchange rate, exemption code, establishment ID, and manifest type files
- Implement QNTUTCNV fixed-width parsing with reciprocal conversion generation
- Implement AHECC validation rules (8-digit limit, date validation, assay warning creation)
- Migrate processing code from country-specific to shared library module

**Phase 4: UI Integration and Auto-Population**
- Update all SBSS UI dropdowns to query filtered reference data
- Implement auto-population logic for export exemption codes (EEC) based on shipment value with save restrictions for high-value shipments
- Implement validation popups: "Please choose an exemption code under EEC" and "For EXCC, CCN reference input missing..."
- Implement auto-population logic for cargo report manifest type (CRTYP) based on mode of transport with auto-update on MOT change
- Add Origin/Destination Establishment ID lookups with formatted descriptions (2,456 codes)
- Implement automatic reference derivation: EDEC from EEC (non-editable), ORESTB from OED (frozen), DNESTB from DED (frozen), CRTYP from MOT (auto-updates)
- Create SBSS Admin screens for monitoring reference file processing status

**Phase 5: Testing and Validation**
- End-to-end testing with sample reference files from TPS
- Verify expiration filtering across all UI components
- Test tariff file dependency processing (8-file group)
- Test retry logic and failure notification
- Test manual trigger mechanism and manual seeding switch
- Test QNTUTCNV reciprocal conversion accuracy
- Test AHECC validation rules and assay warning creation
- Test export exemption auto-population, save restrictions, and validation popups for all scenarios (< $2,000, ≥ $2,000, EXCC with/without CCN)
- Test cargo report manifest type auto-population and auto-update on MOT change
- Test establishment code dropdown display format and reference freezing behavior
- Performance testing with large files (e.g., 2,456 establishment codes)

**Phase 6: Deployment and Monitoring**
- Execute manual seeding via BSIS switch for initial reference data population
- Verify seeded data counts match expected totals (especially 2,456 establishment codes)
- Deploy to test environment for user acceptance testing
- Deploy to production after successful seeding verification
- Monitor daily processing success for first 30 days
- Tune performance and adjust processing window if needed
- Train SBSS Support on failure investigation, manual trigger procedures, and manual seeding procedures
- Train brokers on export exemption code selection requirements and underbond establishment code selection

---

title: Project-A - Import / Export - Reference File Processing (2) — Technical Specification
content: ## 1. Integration Architecture

### 1.1 System Components

**BSIS (Brokerage System Integration Service)** — Middleware responsible for:
- Crawling TPS reference file webpage via HTTPS
- Detecting new or updated reference files based on naming patterns and datetime stamps
- Downloading file content from TPS Main Directory and Change Directory
- Encoding file content (Base64) for transmission
- Constructing XML message (xmlCustomsMessage) with source identifier and encoded payload
- Transmitting message to SBSS via message queue or API endpoint
- Implementing retry logic (3 attempts with 5-minute intervals) on transmission failure
- Sending email notification to SBSS Support on final failure
- Providing manual trigger interface for ad-hoc file requests by authorized party
- **Providing manual seeding switch for initial reference data population before automated daily processing begins**

**TPS (Trade Processing System)** — CBF platform hosting reference files:
- Main Directory (EDMAIN/EEMAIN): Contains most reference files without datetime filtering requirement
- Change Directory (EDCHNG): Contains frequently updated files (e.g., exchange rates) with datetime stamp validation
- File Naming Convention: `[FILENAME]-P1-[DIRECTORY]-[YYMMDDHHMM].txt` (example: `XCHGRATE-P1-EDCHNG-2308250138.txt`)
- Publication Schedule: Varies by file type (daily, weekly, monthly, ad-hoc)
- **Hosts Establishment Registry with approximately 2,456 port/depot/warehouse codes**

**SBSS (Brokerage Operations Support System)** — End system that:
- Receives XML messages from BSIS containing reference file content
- Decodes file payload and parses according to format specification (pipe-delimited or fixed-width)
- Validates data types, mandatory fields, and referential integrity
- Inserts or updates records in Admin reference tables or Custom Code Value tables
- Implements expiration date filtering in data access layer
- Logs all processing events (success, failure, record counts, validation errors)
- Exposes processing status and logs in SBSS Admin UI for monitoring
- **Enforces export exemption code (EEC) save restrictions for shipments ≥ AUD $2,000 in interactive mode**
- **Auto-populates cargo report manifest type (CRTYP) based on mode of transport (MOT)**
- **Auto-populates and freezes underbond establishment references (ORESTB, DNESTB) based on establishment code selections (OED, DED)**
- **Auto-derives EDEC reference from EEC selection (non-editable, auto-updates with EEC changes)**

### 1.2 Data Flow

```
TPS Website (HTTPS)
  ↓ (Daily 7 AM Sydney Time OR Manual Seeding Trigger)
BSIS Crawler
  ↓ (File Detection & Download)
BSIS Encoder
  ↓ (XML Message: xmlCustomsMessage)
SBSS Message Receiver
  ↓ (Decode & Parse)
SBSS Processing Handler
  ↓ (Validate & Transform)
SBSS Database (Admin Tables / Custom Code Values)
  ↓ (Query with Expiration Filter)
SBSS UI (Dropdowns, Search, Auto-Population)
  ↓ (Broker Interaction)
Export Exemption Validation (EEC Save Restriction for Shipments ≥ $2,000)
Cargo Report Manifest Type Derivation (CRTYP from MOT)
Underbond Establishment Reference Freezing (ORESTB/DNESTB)
EDEC Reference Auto-Derivation (from EEC)
```

### 1.3 Message Format Specification

**XML Message Structure** (xmlCustomsMessage):
```xml
<xmlCustomsMessage>
  <Source>[SOURCE_IDENTIFIER]</Source>
  <Content>[BASE64_ENCODED_FILE_CONTENT]</Content>
  <FileName>[ORIGINAL_FILENAME]</FileName>
  <FileDateTime>[YYMMDDHHMM]</FileDateTime>
  <Timestamp>[MESSAGE_CREATION_TIMESTAMP]</Timestamp>
  <ManualTrigger>[true/false]</ManualTrigger>
</xmlCustomsMessage>
```

**Source Identifier Mapping** (used to route to correct processing handler):

| File Name Pattern | Directory | DateTime Check | Source Identifier |
|-------------------|-----------|----------------|-------------------|
| XCHGRATE-P1-EDCHNG | Change | Yes (today only) | ICSXCHGRATE |
| STCPCHAR-P1-EDMAIN | Main | No | ICSTARIFF-STCPCHAR |
| STCPMSAD-P1-EDMAIN | Main | No | ICSTARIFF-STCPMSAD |
| TRFCCHAR-P1-EDMAIN | Main | No | ICSTARIFF-TRFCCHAR |
| TRFCCONC-P1-EDMAIN | Main | No | ICSTARIFF-TRFCCONC |
| TRFCMSAD-P1-EDMAIN | Main | No | ICSTARIFF-TRFCMSAD |
| TRFCSNAP-P1-EDMAIN | Main | No | ICSTARIFF-TRFCSNAP |
| TRRPADDC-P1-EDMAIN | Main | No | ICSTARIFF-TRRPADDC |
| TFRPCHAR-P1-EDMAIN | Main | No | ICSTARIFF-TFRPCHAR |
| TFRPMSAD-P1-EDMAIN | Main | No | ICSTARIFF-TFRPMSAD |
| TRRPCHAR-P1-EDMAIN | Main | No | ICSTARIFF-TRRPCHAR |
| TRRPMSAD-P1-EDMAIN | Main | No | ICSTARIFF-TRRPMSAD |
| TRETSCTY-P1-EDMAIN | Main | No | ICSTARIFF-TRETSCTY |
| PRRPSNAP-P1-EDMAIN | Main | No | ICSTARIFF-PRRPSNAP |
| RRPCHAR-P1-EDMAIN | Main | No | ICSTARIFF-RRPCHAR |
| PRRPCTRY-P1-EDMAIN | Main | No | ICSTARIFF-PRRPCTRY |
| PRRPTRFG-P1-EDMAIN | Main | No | ICSTARIFF-PRRPTRFG |
| PRSPSNAP-P1-EDMAIN | Main | No | ICSTARIFF-PRSPSNAP |
| PRSRMSAD-P1-EDMAIN | Main | No | ICSTARIFF-PRSRMSAD |
| PRSCHRUL-P1-EDMAIN | Main | No | ICSTARIFF-PRSCHRUL |
| AHECCSS-P1-EEMAIN | Main | No | ICSTARIFF-AHECCSS |
| CDLST11-P1-EDMAIN | Main | Yes | TPS-CDLST11 |
| INSTRTRG-P1-EDMAIN | Main | No | TPS-INSTRTRG |
| INSTRMNT-P1-EDMAIN | Main | No | TPS-INSTRMNT |
| AQSCMDTY-P1-EDMAIN | Main | Yes | TPS-AQSCMDTY |
| LGMNTQST-P1-EDMAIN | Main | Yes | TPS-LGMNTQST |
| CMNPTRSK-P1-EDMAIN | Main | Yes | TPS-CMNPTRSK |
| CMNPRPRF-P1-EDMAIN | Main | Yes | TPS-CMNPRPRF |
| STCPSNAP-P1-EDMAIN | Main | **Special** | ICSTARIFF-STCPSNAP |
| AQSCMSTC-P1-EDMAIN | Main | **Special** | ICSTARIFF-AQSCMSTC |
| PRSPCTRY-P1-EDMAIN | Main | **Special** | ICSTARIFF-PRSPCTRY |
| TFRPSNAP-P1-EDMAIN | Main | **Special** | ICSTARIFF-TFRPSNAP |
| TRETSNAP-P1-EDMAIN | Main | **Special** | ICSTARIFF-TRETSNAP |
| TRETSTGP-P1-EDMAIN | Main | **Special** | ICSTARIFF-TRETSTGP |
| TFRPADDC-P1-EDMAIN | Main | **Special** | ICSTARIFF-TFRPADDC |
| TRRPSNAP-P1-EDMAIN | Main | **Special** | ICSTARIFF-TRRPSNAP |
| QNTUTCNV-P1-EDMAIN | Main | No | TPS-QNTUTCNV |
| ESTREG-P1-EDMAIN | Main | No | TPS-ESTREG |

**Special Tariff File Group** (8 files marked **Special**):
- If **any one** of these 8 files has been updated on TPS website on current day, BSIS must send **all 8 files** to SBSS
- SBSS processes all 8 files in a single job to maintain tariff structure consistency
- Processing order within group: TBD based on dependency analysis (see §5.3)

**DateTime Stamp Validation** (applies to files marked with "Yes" in DateTime Check column):
- BSIS extracts datetime from filename (format: YYMMDDHHMM, example: 2308250138 = August 25, 2023, 01:38 AM)
- Compares datetime to current Sydney time (UTC+11 during standard time, UTC+12 during daylight saving time)
- If datetime does not match current day, BSIS does NOT send file to SBSS
- Main Directory files without datetime check are sent regardless of datetime stamp

**Manual Trigger Flag**:
- When ManualTrigger = true, BSIS ignores datetime check and sends file to SBSS regardless of file date
- Used for ad-hoc re-processing after failure resolution
- Used for initial manual seeding of reference data before automated daily processing begins

### 1.4 Retry and Error Handling

**BSIS Retry Logic**:
- On file download failure (TPS unavailable, network timeout, file not found): Retry 3 times with 5-minute intervals between attempts
- On file transmission failure (SBSS endpoint unreachable, message queue full): Retry 3 times with 5-minute intervals between attempts
- After 3rd failed attempt: Send email to SBSS Support (SBSS-support@Carrier Co.com) with subject "Reference File Processing Failure - [FILENAME]" containing error details, timestamp, retry log
- Do NOT retry on file format errors (SBSS responsibility to log and alert)

**SBSS Retry Logic**:
- On message decode failure (corrupted payload): Log error, send email to SBSS Support, discard message
- On parsing failure (invalid delimiter, missing mandatory field): Log error with line number, send email to SBSS Support, discard message
- On database insert failure (constraint violation, duplicate key): Log error with record details, continue processing remaining records, send summary email to SBSS Support at end of job
- On processing timeout (job exceeds 30 minutes): Log error, rollback transaction, send email to SBSS Support

**Manual Trigger Mechanism**:
- SBSS Admin UI provides "Request Reference File Re-Processing" function restricted to ADM role and SBSS Support team
- User selects file type from dropdown (mapped to Source Identifier)
- System constructs manual request message (xmlCustomsMessage with ManualTrigger = true)
- BSIS receives manual request, crawls TPS for specified file (ignoring datetime check), transmits to SBSS
- All manual requests logged in audit table with user ID, timestamp, justification field (mandatory)

**Manual Seeding Mechanism**:
- BSIS provides manual seeding switch/trigger for initial reference data population before automated daily processing begins
- Authorized party triggers manual seeding for specified reference files (or all files)
- BSIS crawls TPS for specified files (ignoring datetime check), transmits to SBSS with ManualTrigger = true
- SBSS processes files and populates reference tables
- System verifies seeded data counts match expected totals (especially 2,456 establishment codes)
- Manual seeding logged in audit table with user ID, timestamp, file list, record counts

---

## 2. File Format Specifications

### 2.1 General Format Characteristics

**Pipe-Delimited Format** (used by most files):
- Field Separator: `|` (pipe character)
- Record Terminator: `\n` (newline) or `\r\n` (carriage return + newline)
- Text Qualifier: None (fields not quoted unless specified)
- Encoding: UTF-8
- Header Row: Yes (first row contains column names)
- Empty Fields: Represented as consecutive pipes `||` (no space characters)

**Fixed-Width Format** (used by QNTUTCNV file):
- Field positions and lengths defined per file specification
- Padding: Numeric fields right-justified and zero-padded; text fields left-justified and space-padded
- No delimiter between fields
- No header row
- Record length: Fixed number of characters per record (specified in ICD)

**Date Formats**:
- `CCYYMMDD` (e.g., `20240318` = March 18, 2024) — 8 characters, century included
- `CCYYMMDDHHMISS` (with zero-padding for hours/minutes/seconds) — 14 characters for timestamps
- `YYMMDD` (legacy files only) — 6 characters, century omitted

**Numeric Formats**:
- Integer: No decimal separator
- Decimal: Period `.` as decimal separator (e.g., `123.45`)
- Leading Zeros: Preserved for fixed-width fields; optional for pipe-delimited fields
- Percentage: Represented as decimal (e.g., `0.05` = 5%)

### 2.2 File-Specific Format Details

**AHECC (AHECCSS-P1-EEMAIN)** — Export Harmonized Tariff Codes
- **Format**: Pipe-delimited
- **Fields**:
  - AHECC Code (8 digits max, Chapter-Subchapter-Heading-Subheading, no statistical suffix)
  - Start Date (CCYYMMDD)
  - End Date (CCYYMMDD)
  - Net Quantity Unit Code (2 chars, e.g., `NO`, `KG`)
  - Assay Required Indicator (`Y` or `N`)
  - Description (max 253 chars)
  - Action Indicator (1 char: `A`=Add, `C`=Change, `D`=Delete)
- **Processing Rules**:
  - Reject record if AHECC Code > 8 digits
  - Reject record if Start Date > current system date (code not yet valid)
  - Mark record as expired if End Date < current system date
  - Store Net Quantity Unit Code as reference for UOM validation
  - If Assay Required Indicator = `Y`, create warning entry in Tariff Warnings table (code `ARI - Assay Required`)
  - Store Description in Tariff table, truncate if > 253 chars (log warning)
- **Example Record**:
  ```
  01011020|20020101|20111231|NO|Y|LIVE PUREBRED BREEDING HORSES (EXCL. HORSES EXPORTED FOR RACING & BREEDING PURPOSES & SUBSEQUENT RE-IMPORTATION, HORSES FOR RE-EXPORTATION HAVING BEEN ORIGINALLY IMPORTED ON A TEMPORARY BASIS)|A
  ```

**XCHGRATE (XCHGRATE-P1-EDCHNG)** — Daily Exchange Rates
- **Format**: Pipe-delimited
- **Fields**:
  - Currency Code (3 chars, ISO 4217, e.g., `USD`, `EUR`)
  - Exchange Rate (decimal, 6 decimal places, e.g., `1.345678`)
  - Effective Date (CCYYMMDD)
  - End Date (CCYYMMDD)
- **Processing Rules**:
  - Insert new record if Currency Code + Effective Date combination does not exist
  - Update exchange rate if Currency Code + Effective Date exists (replace rate value)
  - Do NOT apply expiration filtering to exchange rates (historical rates needed for declaration amendments)
  - Store in Core Reference table: Exchange Rate

**INSTRMNT (INSTRMNT-P1-EDMAIN)** — Instrument Definitions
- **Format**: Pipe-delimited
- **Fields**:
  - Instrument Code (max 10 chars)
  - Instrument Description (max 255 chars)
  - Start Date (CCYYMMDD)
  - End Date (CCYYMMDD)
  - Tariff Group Code (max 10 chars, links to tariff classification)
- **Processing Rules**:
  - Store in Local Reference table: Instrument
  - Mark as expired if End Date < current system date
  - Validate that Tariff Group Code exists in tariff reference data (if not, log warning but insert record)

**PRRPSNAP (PRRPSNAP-P1-EDMAIN)** — Preference Rule Snapshots
- **Format**: Pipe-delimited
- **Fields**:
  - Preference Rule Code (max 10 chars)
  - Preference Rule Description (max 255 chars)
  - Start Date (CCYYMMDD)
  - End Date (CCYYMMDD)
  - Associated Preference Scheme Code (max 10 chars, e.g., `APS` for ASEAN)
- **Processing Rules**:
  - Store in **two locations**:
    - Core Reference table: Custom Code Value (Type = `PRR`)
    - Local Reference table: PreferenceRules
  - Mark as expired if End Date < current system date
  - Link to Preference Scheme via scheme code (PRSPSNAP file)

**INSCTGRY (INSCTGRY-P1-EDMAIN)** — Instrument Categories
- **Format**: Pipe-delimited
- **Fields**:
  - Instrument Category Code (max 10 chars)
  - Instrument Category Description (max 255 chars)
  - Start Date (CCYYMMDD)
  - End Date (CCYYMMDD)
- **Processing Rules**:
  - Store in **two locations**:
    - Core Reference table: Custom Code Value (Type = `ICC`)
    - Local Reference table: InstrumentCategories
  - Mark as expired if End Date < current system date

**INSCTTRG (INSTRTRG-P1-EDMAIN)** — Instrument Category to Tariff Group Linkages
- **Format**: Pipe-delimited
- **Fields**:
  - Instrument Category Code (max 10 chars, FK to INSCTGRY)
  - Tariff Group Code (max 10 chars, FK to tariff reference)
  - Start Date (CCYYMMDD)
  - End Date (CCYYMMDD)
- **Processing Rules**:
  - Store in Local Reference table: InstrumentCategoryTariff
  - Validate FK to InstrumentCategories (if missing, log error and skip record)
  - Mark as expired if End Date < current system date

**PRSPSNAP (PRSPSNAP-P1-EDMAIN)** — Preference Scheme Snapshots
- **Format**: Pipe-delimited
- **Fields**:
  - Preference Scheme Code (max 10 chars, e.g., `APS`, `GTR`)
  - Preference Scheme Description (max 255 chars)
  - Start Date (CCYYMMDD)
  - End Date (CCYYMMDD)
- **Processing Rules**:
  - Store in Core Reference table: Custom Code Value (Type = `PRS`)
  - Mark as expired if End Date < current system date
  - Used in Commodity Customs Code dropdown (Type = `PRS`)

**CMNPTRSK (CMNPTRSK-P1-EDMAIN)** — Community Protection Risk Codes
- **Format**: Pipe-delimited
- **Fields**:
  - Risk Code (max 10 chars)
  - Risk Description (max 255 chars)
  - Start Date (CCYYMMDD)
  - End Date (CCYYMMDD)
  - Severity Level (1 char: `H`=High, `M`=Medium, `L`=Low)
- **Processing Rules**:
  - Store in Local Reference table: CommunityProtectionRisk
  - Mark as expired if End Date < current system date
  - Link to tariff codes via separate mapping file (if applicable)

**LGMNTQST (LGMNTQST-P1-EDMAIN)** — Lodgement Questions
- **Format**: Pipe-delimited
- **Fields**:
  - Question Code (max 10 chars)
  - Question Text (max 500 chars)
  - Question Type (`Y/N`, `TEXT`, `NUMERIC`, `DATE`)
  - Mandatory Indicator (`Y` or `N`)
  - Start Date (CCYYMMDD)
  - End Date (CCYYMMDD)
  - Applicable Declaration Type (e.g., `FML`, `DEM`)
- **Processing Rules**:
  - Store in Local Reference table: LodgementQuestions
  - Mark as expired if End Date < current system date
  - Used during declaration validation to prompt broker for additional information

**CDLST11 (CDLST11-P1-EDMAIN-[datetime])** — GST Exemption Codes
- **Format**: Pipe-delimited
- **Fields**:
  - GST Code (max 10 chars)
  - GST Description (max 255 chars)
  - Start Date (CCYYMMDD)
  - End Date (CCYYMMDD)
- **Processing Rules**:
  - Store in Core Reference table: Custom Code Value (Type = `GST`)
  - Mark as expired if End Date < current system date
  - Filename includes datetime stamp; BSIS checks that datetime = today before sending

**QNTUTCNV (QNTUTCNV-P1-EDMAIN)** — Quantity Unit of Measurement Conversions
- **Format**: Fixed-width
- **Field Layout** (positions are 1-indexed):
  - **Positions 1-2**: Original Quantity Unit Code (e.g., `CF` = Cubic Foot)
  - **Position 3**: Space
  - **Positions 4-5**: Target Quantity Unit Code (e.g., `CU` = Cubic Metre); if code is 1 char, position 5 is space
  - **Position 6**: Space
  - **Positions 7-26**: Creation of Conversion Rate Date (CCYYMMDD format in positions 7-14, followed by 12 zeros in positions 15-26)
  - **Position 27**: Space
  - **Positions 28-45**: Conversion Factor (numeric with 8 decimal places, e.g., `0.02831600` or `100000.00000000`)
- **Example Records**:
  ```
  CF CU 20000101000000000000 0.02831600
  CK G  20000101000000000000 100000.00000000
  ```
  - **Record 1**: CF (Cubic Foot) to CU (Cubic Metre) conversion: 1 Cubic Foot = 0.02831600 Cubic Metres
  - **Record 2**: CK (Quintal) to G (Gram) conversion: 1 Quintal = 100000.00000000 Grams (1 CK = 100 KG = 100 × 1000 G)
- **Processing Rules**:
  - Parse fixed-width positions to extract Original UOM (positions 1-2, right-trim spaces), Target UOM (positions 4-5, right-trim spaces), Conversion Factor (positions 28-45, parse as decimal)
  - Store in UOM Conversions table with columns: SourceUOM, TargetUOM, ConversionFactor
  - **Generate reciprocal conversion automatically**: If CF → CU = 0.02831600, then CU → CF = 35.31466672 (1 ÷ 0.02831600)
  - **Validate that both Original and Target UOM codes exist in UOM reference table** (log warning if missing)
  - **If conversion factor = 0, log error and skip record**
  - Store Creation of Conversion Rate Date (positions 7-14 parsed as CCYYMMDD) as EffectiveDate in UOM Conversions table
- **Example UOM Conversions Table Entries** (after processing):

  | Source UOM | Target UOM | Conversion Factor | Effective Date | Reciprocal Generated |
  |------------|------------|-------------------|----------------|----------------------|
  | CF | CU | 0.02831600 | 2000-01-01 | Yes (CU → CF) |
  | CU | CF | 35.31466672 | 2000-01-01 | Reciprocal of CF → CU |
  | CK | G | 100000.00000000 | 2000-01-01 | Yes (G → CK) |
  | G | CK | 0.00001000 | 2000-01-01 | Reciprocal of CK → G |

**ESTREG (Establishment Registry, ESTREG-P1-EDMAIN or embedded in other files)** — Origin/Destination Establishment IDs
- **Format**: Pipe-delimited
- **Fields**:
  - Establishment ID (max 10 chars, e.g., `FF22B`)
  - Establishment Name (max 100 chars, e.g., `COAL LOADING JETTY - BULKCOAL`)
  - Port Code (3 chars, e.g., `AUABL`)
  - Establishment Type (max 5 chars, e.g., `S15` = Seaport)
  - Start Date (CCYYMMDD)
  - End Date (CCYYMMDD)
- **Processing Rules**:
  - Store in Local Reference table: EstablishmentRegistry
  - Mark as expired if End Date < current system date
  - Used to populate Origin Establishment ID (OED) and Destination Establishment ID (DED) dropdowns
  - Display format in UI: `[EstablishmentID] | [EstablishmentName] | [PortCode] | [EstablishmentType]` (pipe-separated concatenation)
  - **Total expected record count: approximately 2,456 establishments**
  - **Validate on initial seeding that record count ≥ 2,400; alert if count < 2,400**
- **Example Record**:
  ```
  FF22B|COAL LOADING JETTY - BULKCOAL|AUABL|S15|20100101|20991231
  ```

**Tariff File Group (8 files processed together)**:
- STCPSNAP-P1-EDMAIN
- AQSCMSTC-P1-EDMAIN
- PRSPCTRY-P1-EDMAIN
- TFRPSNAP-P1-EDMAIN (Tariff Rate Period Snapshot)
- TRETSNAP-P1-EDMAIN
- TRETSTGP-P1-EDMAIN
- TFRPADDC-P1-EDMAIN
- TRRPSNAP-P1-EDMAIN

**Special Processing Rule**:
- If **any one** of these 8 files has been updated on TPS today, BSIS sends **all 8 files** to SBSS
- SBSS processes all 8 in a single job (combined tariff structure regeneration)
- Processing order determined by file interdependencies (see §5.3)

---

## 3. Data Mapping Rules

### 3.1 SBSS Admin Reference Tables

**Core Reference Tables** (shared across all countries):
- **Custom Code Value**: Stores code lists with Type indicator (e.g., Type=`PRR` for Preference Rules, Type=`GST` for GST Exemption Codes, Type=`EEC` for Export Exemption Codes)
- **Exchange Rate**: Stores daily currency conversion rates
- **UOM Conversions**: Stores unit of measurement conversion factors and reciprocals

**Local Reference Tables** (Australia-specific):
- **Instrument**: Instrument definitions for restricted goods
- **PreferenceRules**: FTA and preference scheme rules
- **InstrumentCategories**: Categories for grouping instruments
- **InstrumentCategoryTariff**: Linkage table between instrument categories and tariff codes
- **CommunityProtectionRisk**: Biosecurity and quarantine risk codes
- **LodgementQuestions**: Dynamic questions for declaration validation
- **EstablishmentRegistry**: Port, depot, warehouse identifiers for underbond movements (2,456 expected records)
- **Tariff**: HS code structure and descriptions (populated from AHECC and other tariff files)
- **TariffWarnings**: Warnings associated with tariff codes (e.g., ARI - Assay Required)

**Shipment Reference Lookups** (Australia-specific):
- **CRTYP (Cargo Report Manifest Type)**: Local lookup LPM with values `C` (Consolidated sub-manifest for air) and `S` (Slot Submanifest for sea)
- **OED (Origin Establishment ID)**: Customs Code Type for selecting origin establishment from EstablishmentRegistry
- **DED (Destination Establishment ID)**: Customs Code Type for selecting destination establishment from EstablishmentRegistry

**Shipment Reference Storage**:
- **ORESTB (Origin Establishment ID)**: Shipment reference storing selected OED code value (frozen, non-editable)
- **DNESTB (Destination Establishment ID)**: Shipment reference storing selected DED code value (frozen, non-editable)

**Declaration Reference Storage**:
- **EDEC (Export Declaration Exemption Code)**: Declaration reference storing selected EEC code value (non-editable, auto-derived from EEC)

### 3.2 Field Mapping by File Type

**AHECC → Tariff Table**:
- AHECC Code → TariffNumber (first 8 digits only, no suffix)
- Start Date → ValidFrom
- End Date → ValidTo
- Net Quantity Unit Code → UOMCode
- Assay Required Indicator → AssayRequired (boolean)
- Description → TariffDescription

**AHECC → TariffWarnings Table** (conditional):
- If Assay Required Indicator = `Y`:
  - TariffNumber = AHECC Code
  - WarningCode = `ARI`
  - WarningDescription = `Assay Required`
  - ValidFrom = AHECC Start Date
  - ValidTo = AHECC End Date

**XCHGRATE → Exchange Rate Table**:
- Currency Code → CurrencyCode
- Exchange Rate → Rate
- Effective Date → EffectiveDate
- End Date → EndDate

**INSTRMNT → Instrument Table**:
- Instrument Code → InstrumentCode
- Instrument Description → InstrumentDescription
- Start Date → ValidFrom
- End Date → ValidTo
- Tariff Group Code → TariffGroupCode

**PRRPSNAP → Custom Code Value (Type=PRR) + PreferenceRules Table**:
- Preference Rule Code → CustomCodeValue.Code (where Type=`PRR`)
- Preference Rule Code → PreferenceRules.RuleCode
- Preference Rule Description → CustomCodeValue.Description, PreferenceRules.RuleDescription
- Start Date → ValidFrom (both tables)
- End Date → ValidTo (both tables)
- Associated Preference Scheme Code → PreferenceRules.SchemeCode

**INSCTGRY → Custom Code Value (Type=ICC) + InstrumentCategories Table**:
- Instrument Category Code → CustomCodeValue.Code (where Type=`ICC`), InstrumentCategories.CategoryCode
- Instrument Category Description → CustomCodeValue.Description, InstrumentCategories.CategoryDescription
- Start Date → ValidFrom (both tables)
- End Date → ValidTo (both tables)

**INSCTTRG → InstrumentCategoryTariff Table**:
- Instrument Category Code → CategoryCode (FK to InstrumentCategories)
- Tariff Group Code → TariffGroupCode (FK to Tariff reference)
- Start Date → ValidFrom
- End Date → ValidTo

**PRSPSNAP → Custom Code Value (Type=PRS)**:
- Preference Scheme Code → CustomCodeValue.Code (where Type=`PRS`)
- Preference Scheme Description → CustomCodeValue.Description
- Start Date → ValidFrom
- End Date → ValidTo

**CMNPTRSK → CommunityProtectionRisk Table**:
- Risk Code → RiskCode
- Risk Description → RiskDescription
- Start Date → ValidFrom
- End Date → ValidTo
- Severity Level → SeverityLevel

**LGMNTQST → LodgementQuestions Table**:
- Question Code → QuestionCode
- Question Text → QuestionText
- Question Type → QuestionType
- Mandatory Indicator → IsMandatory (boolean)
- Start Date → ValidFrom
- End Date → ValidTo
- Applicable Declaration Type → DeclarationType

**CDLST11 → Custom Code Value (Type=GST)**:
- GST Code → CustomCodeValue.Code (where Type=`GST`)
- GST Description → CustomCodeValue.Description
- Start Date → ValidFrom
- End Date → ValidTo

**QNTUTCNV → UOM Conversions Table**:
- Original Quantity Unit Code (positions 1-2, right-trimmed) → SourceUOM
- Target Quantity Unit Code (positions 4-5, right-trimmed) → TargetUOM
- Conversion Factor (positions 28-45, parsed as decimal) → ConversionFactor
- Creation of Conversion Rate Date (positions 7-14, parsed as CCYYMMDD) → EffectiveDate
- **Auto-generate reciprocal**: TargetUOM → SourceUOM with ConversionFactor = 1 / OriginalConversionFactor

**Establishment Registry → EstablishmentRegistry Table**:
- Establishment ID → EstablishmentID
- Establishment Name → EstablishmentName
- Port Code → PortCode
- Establishment Type → EstablishmentType
- Start Date → ValidFrom
- End Date → ValidTo

**EEC Configuration → Custom Code Value (Type=EEC)** (not from TPS file; seeded manually via script in DT5, DT6, and production environments):
- Code Type → `EEC`
- Codes and Descriptions:
  - `EXCC` → `CONTINGENCY CUSTOMS AUTHORITY NUMBER`
  - `EXDC` → `AUSTRALIAN DOMESTIC CARGO`
  - `EXDD` → `MILITARY GOODS. OWNED BY AUS. GOVERNMENT`
  - `EXLV` → `GOODS UNDER THE VALUE OF $2000 (AUD)`
  - `EXML` → `AUSTRALIA POST OR DIPLOMATIC MAIL BAGS`
  - `EXPE` → `UNACCOMPANIED PERSONAL EFFECTS`
  - `EXSP` → `AUSTRALIAN AIRCRAFT SPARES`
  - `EXTI` → `CARNET OR TRYPTIQUE. TEMPORARY IMPORTS`

**CRTYP Configuration → Local Lookup LPM** (not from TPS file; seeded manually via script in DT5, DT6, and production environments):
- Code → `CRTYP`
- Parent Code → `SH` (Shipment)
- Description → `Cargo Report Manifest Type`
- Lookup Values:
  - `C` → `Consolidated sub-manifest` (for MOT = 6, Air)
  - `S` → `Slot Submanifest` (for MOT = 11, Sea)

**OED/DED Configuration → Custom Code Type** (not from TPS file; seeded manually via script in DT5, DT6, and production environments):
- Code Type → `OED`
- Description → `Origin Establishment ID`
- Code Type → `DED`
- Description → `Destination Establishment ID`
- Lookup Values: Populated from EstablishmentRegistry table (2,456 codes)

### 3.3 Derived and Calculated Fields

**Assay Required Warning** (AHECC processing):
- If AssayRequired = `Y`, insert record into TariffWarnings table:
  - TariffNumber = AHECC Code
  - WarningCode = `ARI`
  - WarningDescription = `Assay Required`
  - ValidFrom = AHECC Start Date
  - ValidTo = AHECC End Date

**UOM Reciprocal Conversion** (QNTUTCNV processing):
- For each record with Original UOM `A` → Target UOM `B` with ConversionFactor `F`:
  - Insert reciprocal record: Original UOM `B` → Target UOM `A` with ConversionFactor `1/F`
  - Example: If CF → CU = 0.02831600, then CU → CF = 35.31466672 (1 ÷ 0.02831600)

**Establishment Display String** (EstablishmentRegistry processing):
- Concatenate fields with pipe separator for UI display:
  - `[EstablishmentID] | [EstablishmentName] | [PortCode] | [EstablishmentType]`
  - Example: `FF22B | COAL LOADING JETTY - BULKCOAL | AUABL | S15`
  - Store concatenated string in DisplayString column for efficient dropdown population

**EEC Auto-Population** (Shipment processing):
- **Trigger**: On shipment download OR when shipment total value is edited
- **Logic**:
  - If Declaration > Movement > Direction = `EXPORT`:
    - If Shipment Total Value < AUD $2,000:
      - Set Declaration > Customs > Customs Code EEC = `EXLV`
      - Set Declaration > References > Ref Type = `EDEC`, Ref Number = `EXLV`
    - If Shipment Total Value ≥ AUD $2,000:
      - Set Declaration > Customs > Customs Code EEC = blank
      - Set Declaration > References > Ref Type = `EDEC`, Ref Number = blank
      - **Interactive mode only**: Display validation popup: "Please choose an exemption code under EEC"
      - **Interactive mode only**: Block shipment save until EEC is manually selected by user

**EDEC Reference Derivation** (Declaration processing):
- **Trigger**: When EEC is set or changed
- **Logic**:
  - Set Declaration > References > Ref Type = `EDEC`
  - Set Declaration > References > Ref Number = EEC Code Value (e.g., `EXLV`, `EXCC`, `EXPE`)
  - Mark EDEC reference as non-editable (auto-derived)
  - If EEC is changed, automatically update EDEC reference to match new value

**CRTYP Auto-Population** (Shipment processing):
- **Trigger**: On shipment download OR when Mode of Transport (MOT) is changed manually
- **Logic**:
  - If Declaration > Movement > Mode of Transport = `6` (Air):
    - Set Shipment > References > Ref Type = `CRTYP`, Ref Number = `C` (Consolidated sub-manifest)
  - If Declaration > Movement > Mode of Transport = `11` (Sea):
    - Set Shipment > References > Ref Type = `CRTYP`, Ref Number = `S` (Slot Submanifest)
  - If MOT is any other value:
    - Set Shipment > References > Ref Type = `CRTYP`, Ref Number = blank
  - Mark CRTYP reference as non-editable (auto-derived)
  - If MOT is changed manually, automatically update CRTYP reference to match new value

**ORESTB Reference Storage** (Shipment processing):
- **Trigger**: When user selects Origin Establishment ID (OED) from dropdown
- **Logic**:
  - Set Shipment > References > Ref Type = `ORESTB` (Origin Establishment ID)
  - Set Shipment > References > Ref Number = selected Establishment ID (e.g., `FF22B`)
  - Mark ORESTB reference as frozen/non-editable
  - Reference can change only when OED dropdown is re-selected by user

**DNESTB Reference Storage** (Shipment processing):
- **Trigger**: When user selects Destination Establishment ID (DED) from dropdown
- **Logic**:
  - Set Shipment > References > Ref Type = `DNESTB` (Destination Establishment ID)
  - Set Shipment > References > Ref Number = selected Establishment ID (e.g., `FF22B`)
  - Mark DNESTB reference as frozen/non-editable
  - Reference can change only when DED dropdown is re-selected by user

---

## 4. Expiration Filtering and Status Model

### 4.1 Expiration Logic

**Filtering Rule**:
- All reference data queries in SBSS must include expiration check: `WHERE ValidTo >= CURRENT_DATE` (or `EndDate >= CURRENT_DATE` depending on column name)
- Applies to:
  - SBSS Admin reference data search screens
  - SBSS UI dropdown lists (Tariff Number, Customs Code PRR, Customs Code PRS, Customs Code ICC, Customs Code GST, Customs Code EEC, Customs Code OED, Customs Code DED, etc.)
  - Auto-population logic (e.g., selecting tariff code for commodity line, selecting establishment code for underbond)
  - Validation logic (e.g., checking if selected code is valid)

**Implementation Approach**:
- **Data Access Layer**: Modify all SQL queries and ORM queries to include expiration filter by default
- **Admin Screens**: Add toggle "Show Expired Entries" (off by default) to allow admins to view historical data for audit purposes
- **UI Dropdowns**: Pre-filter reference data before populating dropdown options
- **Validation**: Reject user input if selected code is expired (error message: "Code [CODE] is no longer valid as of [END_DATE]. Please select a current code.")

**Files Affected by Expiration Filtering**:
- AHECC (export tariff codes)
- INSTRMNT (instruments)
- PRRPSNAP (preference rules)
- INSCTGRY (instrument categories)
- INSCTTRG (instrument category-tariff linkages)
- PRSPSNAP (preference schemes)
- CMNPTRSK (community protection risk)
- LGMNTQST (lodgement questions)
- CDLST11 (GST exemption codes)
- EstablishmentRegistry (origin/destination establishment IDs)
- EEC (export exemption codes, if end-dated)

**Files NOT Affected by Expiration Filtering**:
- XCHGRATE (exchange rates): Historical rates needed for declaration amendments; filter by Effective Date instead of End Date when selecting rate for specific transaction date

### 4.2 Status Transitions

**Reference Data Record Lifecycle**:
1. **Pending** (Start Date > Current Date): Code exists in table but not yet valid; excluded from all queries
2. **Active** (Start Date ≤ Current Date AND End Date ≥ Current Date): Code valid for use; included in all queries
3. **Expired** (End Date < Current Date): Code no longer valid; excluded from queries unless "Show Expired" toggle enabled

**Processing Status** (for reference file processing job):
1. **Queued**: XML message received, awaiting processing
2. **In Progress**: File decoding and parsing started
3. **Validating**: Record-level validation in progress
4. **Updating Database**: Insert/update operations in progress
5. **Complete**: All records processed successfully
6. **Failed**: Processing encountered fatal error (entire file rejected)
7. **Partial Success**: Some records processed, some skipped due to validation errors (summary logged)

### 4.3 Error and Exception States

**Record-Level Errors** (log and continue processing):
- Invalid data type (e.g., non-numeric value in numeric field)
- Missing mandatory field
- FK constraint violation (referenced record does not exist)
- Duplicate key (record with same primary key already exists) → Update existing record if Action Indicator = `C` (Change); skip if Action Indicator = `A` (Add)
- Data length exceeds column size → Truncate and log warning

**File-Level Errors** (reject entire file):
- File decode failure (corrupted Base64 encoding)
- Invalid file format (missing header row, wrong delimiter)
- Schema mismatch (column count or names do not match expected format)
- Processing timeout (job exceeds 30 minutes)

---

## 5. Business Rules and Validation

### 5.1 Tariff Code Validation (AHECC)

- **Rule T1**: AHECC Code must not exceed 8 digits (reject if > 8)
- **Rule T2**: Start Date must be ≤ current system date (reject if code not yet valid)
- **Rule T3**: End Date must be ≥ Start Date (reject if invalid date range)
- **Rule T4**: Net Quantity Unit Code must exist in UOM reference table (log warning if missing, but insert record)
- **Rule T5**: Assay Required Indicator must be `Y` or `N` (reject if other value)
- **Rule T6**: Description must not exceed 253 characters (truncate and log warning if exceeded)
- **Rule T7**: If Assay Required Indicator = `Y`, insert corresponding warning in TariffWarnings table (code `ARI - Assay Required`)

### 5.2 Exchange Rate Validation (XCHGRATE)

- **Rule X1**: Currency Code must be 3 characters (ISO 4217 format)
- **Rule X2**: Exchange Rate must be > 0 (reject if zero or negative)
- **Rule X3**: Effective Date must be ≤ current system date (reject future-dated rates)
- **Rule X4**: If Currency Code + Effective Date already exists, UPDATE rate value; otherwise INSERT new record
- **Rule X5**: Do NOT apply expiration filtering to exchange rates in queries (filter by Effective Date ≤ Transaction Date instead)

### 5.3 Tariff File Dependency and Processing Order

**Issue**: TFRPSNAP (Tariff Rate Period Snapshot) contains three types of specific rates that must be mapped to Brokerage Charge Calc Method Rate Type table. Other countries only have one specific rate per charge specification, but AU has three. This requires special handling.

**Tariff Rate Types**:
- **Ad Valorem (AV)**: Percentage-based duty (e.g., 5% of customs value)
- **Specific Rate 1 (SP1)**: Quantity-based duty (e.g., $10 per kilogram) → Maps to `Quantity Rate` field in TFRPSNAP
- **Specific Rate 2 (SP2)**: Second quantity-based duty (e.g., $5 per litre) → Maps to `Second Quantity Rate` field in TFRPSNAP
- **Specific Rate 3 (SP3)**: Other duty factor (e.g., $2 per unit based on alcohol content) → Maps to `Other Duty Factor Rate` field in TFRPSNAP

**Calc Method Mapping**:
- Calc Method Type `03`: Contains AV + SP1 + SP2 + SP3
- Calc Method Type `05`: Contains AV + SP1 + SP2 + SP3
- Calc Method Type `06`: Contains AV + SP1 + SP2 + SP3
- Calc Method Type `07`: Contains AV + SP1 + SP2 + SP3

**Processing Rules**:
- **Rule TF1**: When TFRPSNAP is processed, check Quantity Rate, Second Quantity Rate, and Other Duty Factor Rate fields
- **Rule TF2**: If Quantity Rate ≠ 0 and ≠ null, add SP1 to charge specification
- **Rule TF3**: If Second Quantity Rate ≠ 0 and ≠ null, add SP2 to charge specification
- **Rule TF4**: If Other Duty Factor Rate ≠ 0 and ≠ null, add SP3 to charge specification
- **Rule TF5**: If all three specific rates = 0 or null, only add AV to charge specification
- **Rule TF6**: Pre-populate Brokerage Charge Calc Method Rate Type table with 4 rows for each Calc Method Type (03/05/06/07): one row for AV, one for SP1, one for SP2, one for SP3

**Open Issue**: TPS documentation does not clearly explain where Calc Method Type data originates. Clarify with CBF whether Calc Method Type is embedded in one of the 8 tariff files or must be seeded manually.

**Tariff File Processing Order** (8-file group):
1. STCPSNAP (Tariff structure base)
2. TRETSNAP (Treatment snapshots)
3. TRETSTGP (Treatment stage groupings)
4. AQSCMSTC (AQS commodity-tariff linkages)
5. PRSPCTRY (Preference scheme-country linkages)
6. TFRPSNAP (Tariff rate periods) ← Must process AFTER structure files
7. TFRPADDC (Tariff rate additional components)
8. TRRPSNAP (Tariff rate rule snapshots)

**Rationale**: Rate files (TFRPSNAP, TFRPADDC, TRRPSNAP) reference tariff codes from structure files (STCPSNAP, TRETSNAP). Processing rates before structure would result in FK constraint violations.

### 5.4 UOM Conversion Validation (QNTUTCNV)

- **Rule U1**: Original UOM and Target UOM must exist in UOM reference table (log warning if missing, insert conversion anyway)
- **Rule U2**: Conversion Factor must be > 0 (reject if zero or negative)
- **Rule U3**: Auto-generate reciprocal conversion: if A → B = F, then B → A = 1/F
- **Rule U4**: If conversion already exists for same UOM pair, UPDATE conversion factor; do not create duplicate
- **Rule U5**: Fixed-width parsing (positions are 1-indexed): Extract positions 1-2 (Original UOM, right-trim spaces), 4-5 (Target UOM, right-trim spaces if 1 char), 28-45 (Conversion Factor, parse as decimal), 7-14 (Creation Date, parse as CCYYMMDD)
- **Rule U6**: Example validation: `CF CU 20000101000000000000 0.02831600` → 1 CF = 0.02831600 CU, 1 CU = 35.31466672 CF
- **Rule U7**: Example validation: `CK G  20000101000000000000 100000.00000000` → 1 CK = 100000.00000000 G, 1 G = 0.00001000 CK
- **Rule U8**: Note that CF → CC conversion mentioned in requirements is NOT directly present in file; system must chain conversions (CF → CU → CC) or compute via dimensional analysis if CC conversion factors exist in separate records

### 5.5 Preference Rule and Scheme Validation (PRRPSNAP, PRSPSNAP)

- **Rule P1**: Preference Rule Code must be unique within validity period (Start Date to End Date)
- **Rule P2**: Associated Preference Scheme Code must exist in PRSPSNAP file (validate FK relationship)
- **Rule P3**: Store Preference Rule in **two tables**: Custom Code Value (Type=`PRR`) for dropdown population, and PreferenceRules for detailed rule logic
- **Rule P4**: Same dual-storage pattern for Preference Schemes (Type=`PRS` in Custom Code Value, full details in PreferenceSchemes table if exists)
- **Rule P5**: Expired preference rules must be filtered from Commodity Customs Code dropdown (Type=`PRR`)

### 5.6 Instrument and Category Validation (INSTRMNT, INSCTGRY, INSCTTRG)

- **Rule I1**: Instrument Code must be unique within validity period
- **Rule I2**: Tariff Group Code referenced in INSTRMNT must exist in tariff structure (validate FK)
- **Rule I3**: Instrument Category Code must be unique within validity period
- **Rule I4**: INSCTTRG linkage records must reference valid Instrument Category Code (FK to INSCTGRY)
- **Rule I5**: INSCTTRG linkage records must reference valid Tariff Group Code (FK to tariff reference)
- **Rule I6**: Expired instruments and categories must be filtered from Commodity Customs Code dropdown (Type=`ICC`)

### 5.7 Community Protection and Lodgement Question Validation (CMNPTRSK, LGMNTQST)

- **Rule C1**: Risk Code must be unique within validity period
- **Rule C2**: Severity Level must be `H` (High), `M` (Medium), or `L` (Low) (reject if other value)
- **Rule C3**: Lodgement Question Code must be unique within validity period
- **Rule C4**: Question Type must be one of: `Y/N`, `TEXT`, `NUMERIC`, `DATE` (reject if other value)
- **Rule C5**: Mandatory Indicator must be `Y` or `N` (reject if other value)
- **Rule C6**: Applicable Declaration Type must match one of the clearance types in SBSS (FML, DEM, LND, FTZ, etc.)
- **Rule C7**: Expired lodgement questions must NOT be displayed in declaration validation workflow

### 5.8 GST Exemption Code Validation (CDLST11)

- **Rule G1**: GST Code must be unique within validity period
- **Rule G2**: CDLST11 filename includes datetime stamp (format: CDLST11-P1-EDMAIN-[YYMMDDHHMM].txt); BSIS must validate that datetime = today before sending
- **Rule G3**: Expired GST codes must be filtered from Commodity Customs Code dropdown (Type=`GST`)

### 5.9 Establishment Registry Validation

- **Rule E1**: Establishment ID must be unique within validity period
- **Rule E2**: Port Code must exist in SBSS port reference table (log warning if missing, but insert record)
- **Rule E3**: Establishment Type must match one of the defined types (e.g., S15, A01, etc.) (log warning if unrecognized, but insert record)
- **Rule E4**: Display String must be constructed as: `[EstablishmentID] | [EstablishmentName] | [PortCode] | [EstablishmentType]`
- **Rule E5**: Expired establishments must be filtered from Origin Establishment ID (OED) and Destination Establishment ID (DED) dropdowns
- **Rule E6**: Expected total establishment count: approximately 2,456 records
- **Rule E7**: On initial seeding, validate that record count ≥ 2,400; alert SBSS Support if count < 2,400

### 5.10 Export Exemption Code (EEC) Validation

- **Rule EEC1**: EEC must be configured as Custom Code Type `EEC` in SBSS Admin before export declaration processing goes live
- **Rule EEC2**: EEC dropdown must include all 8 exemption codes: EXCC, EXDC, EXDD, EXLV, EXML, EXPE, EXSP, EXTI
- **Rule EEC3**: On shipment download, if Direction = EXPORT and Shipment Total Value < AUD $2,000, auto-populate EEC = `EXLV`
- **Rule EEC4**: On shipment download, if Direction = EXPORT and Shipment Total Value ≥ AUD $2,000, set EEC = blank
- **Rule EEC5**: **Interactive mode only**: If EEC = blank for shipment ≥ AUD $2,000, display popup: "Please choose an exemption code under EEC" when user attempts to save shipment
- **Rule EEC6**: **Interactive mode only**: Block shipment save if EEC = blank for shipment ≥ AUD $2,000 until user manually selects EEC from dropdown
- **Rule EEC7**: If user selects EEC = `EXCC`, validate that Customs Control Number (CCN) exists in Declaration > Customs > Reference Numbers
- **Rule EEC8**: If EEC = `EXCC` and CCN is blank, display popup: "For EXCC, CCN reference input missing and is mandatory. Please choose another exemption code."
- **Rule EEC9**: Block shipment save if EEC = `EXCC` and CCN is blank
- **Rule EEC10**: When EEC is set or changed, automatically derive EDEC reference: Ref Type = `EDEC`, Ref Number = EEC code value
- **Rule EEC11**: EDEC reference is non-editable by user; updates automatically when EEC changes
- **Rule EEC12**: When shipment value is edited and crosses $2,000 threshold (in either direction), re-evaluate EEC auto-population and validation rules

### 5.11 Cargo Report Manifest Type (CRTYP) Validation

- **Rule CRTYP1**: CRTYP must be configured as Shipment Reference in Local Lookup LPM with values `C` and `S` before cargo reporting goes live
- **Rule CRTYP2**: On shipment download, if MOT = `6` (Air), auto-populate CRTYP = `C` (Consolidated sub-manifest)
- **Rule CRTYP3**: On shipment download, if MOT = `11` (Sea), auto-populate CRTYP = `S` (Slot Submanifest)
- **Rule CRTYP4**: On shipment download, if MOT is any value other than 6 or 11, set CRTYP = blank
- **Rule CRTYP5**: CRTYP reference is non-editable by user
- **Rule CRTYP6**: When MOT is changed manually, automatically update CRTYP reference to match new MOT value (trigger on MOT change event)
- **Rule CRTYP7**: CRTYP reference stored as: Ref Type = `CRTYP`, Ref Number = `C` or `S` or blank

### 5.12 Underbond Establishment Code Validation (OED/DED)

- **Rule OED1**: OED and DED must be configured as Customs Code Types before underbond processing goes live
- **Rule OED2**: OED and DED dropdowns must be populated from EstablishmentRegistry table (2,456 expected codes)
- **Rule OED3**: OED and DED dropdown display format: `[EstablishmentID] | [EstablishmentName] | [PortCode] | [EstablishmentType]`
- **Rule OED4**: When user selects OED from dropdown, auto-populate ORESTB reference: Ref Type = `ORESTB`, Ref Number = selected Establishment ID
- **Rule OED5**: ORESTB reference is frozen/non-editable by user (cannot manually edit Ref Number field)
- **Rule OED6**: ORESTB reference can change only when user re-selects OED from dropdown (new selection overwrites previous ORESTB value)
- **Rule OED7**: When user selects DED from dropdown, auto-populate DNESTB reference: Ref Type = `DNESTB`, Ref Number = selected Establishment ID
- **Rule OED8**: DNESTB reference is frozen/non-editable by user (cannot manually edit Ref Number field)
- **Rule OED9**: DNESTB reference can change only when user re-selects DED from dropdown (new selection overwrites previous DNESTB value)
- **Rule OED10**: Expired establishment codes must be filtered from OED and DED dropdowns (WHERE ValidTo >= CURRENT_DATE)

---

## 6. UI Integration and Auto-Population Rules

### 6.1 SBSS Admin Reference Screens

**Search and Display**:
- All reference data search screens must exclude expired entries by default
- Add "Show Expired Entries" toggle (checkbox) in search filter panel; when checked, include expired records in results with visual indicator (e.g., red text, strikethrough)
- Display columns must include: Code, Description, Start Date, End Date, Status (Active/Expired)
- Sort order: Active entries first (alphabetically by code), then expired entries (by End Date descending)

**Manual Entry**:
- When admin manually adds reference data, validate that Start Date ≤ current date (warn if future-dated)
- When admin manually updates reference data, validate that new End Date ≥ current date (warn if setting to expired)
- Provide "Expire Code" action button to set End Date = current date (soft delete)

**Monitoring Dashboard**:
- Add "Reference File Processing Status" screen in SBSS Admin
- Display columns: File Name, Source Identifier, Last Processed Date, Last Processed Time, Status (Success/Failed/Partial), Record Count (Inserted/Updated/Skipped/Failed), Error Summary
- Provide "View Processing Log" drill-down to see detailed error messages per record
- Provide "Request Re-Processing" button for manual trigger (restricted to ADM role and SBSS Support)

### 6.2 Shipment and Declaration UI Dropdowns

**Tariff Number (Commodity Line)**:
- Filter: `WHERE ValidTo >= CURRENT_DATE`
- Display format: `[TariffNumber] - [TariffDescription]`
- Sort order: Alphabetically by Tariff Number
- Search: Allow user to type partial Tariff Number or Description to filter list
- Example: User types "01011020" → Dropdown shows "01011020 - LIVE PUREBRED BREEDING HORSES (EXCL...)"

**Customs Code PRR (Preference Rules)**:
- Filter: `WHERE Type = 'PRR' AND ValidTo >= CURRENT_DATE`
- Display format: `[Code] - [Description]`
- Source: Custom Code Value table
- Used in: Commodity > Customs Code field when declaring preferential tariff treatment

**Customs Code PRS (Preference Schemes)**:
- Filter: `WHERE Type = 'PRS' AND ValidTo >= CURRENT_DATE`
- Display format: `[Code] - [Description]`
- Source: Custom Code Value table
- Used in: Commodity > Customs Code field when declaring FTA preference

**Customs Code ICC (Instrument Categories)**:
- Filter: `WHERE Type = 'ICC' AND ValidTo >= CURRENT_DATE`
- Display format: `[Code] - [Description]`
- Source: Custom Code Value table
- Used in: Commodity > Customs Code field when declaring restricted goods category

**Customs Code GST (GST Exemption)**:
- Filter: `WHERE Type = 'GST' AND ValidTo >= CURRENT_DATE`
- Display format: `[Code] - [Description]`
- Source: Custom Code Value table
- Used in: Commodity > Customs Code field when claiming GST exemption

**Customs Code EEC (Export Exemption)**:
- Filter: `WHERE Type = 'EEC' AND ValidTo >= CURRENT_DATE`
- Display format: `[Code] - [Description]`
- Source: Custom Code Value table
- Used in: Declaration > Customs > Customs Code EEC field for export shipments
- Dropdown values:
  - `EXCC` - `CONTINGENCY CUSTOMS AUTHORITY NUMBER`
  - `EXDC` - `AUSTRALIAN DOMESTIC CARGO`
  - `EXDD` - `MILITARY GOODS. OWNED BY AUS. GOVERNMENT`
  - `EXLV` - `GOODS UNDER THE VALUE OF $2000 (AUD)`
  - `EXML` - `AUSTRALIA POST OR DIPLOMATIC MAIL BAGS`
  - `EXPE` - `UNACCOMPANIED PERSONAL EFFECTS`
  - `EXSP` - `AUSTRALIAN AIRCRAFT SPARES`
  - `EXTI` - `CARNET OR TRYPTIQUE. TEMPORARY IMPORTS`

**Origin Establishment ID (OED) and Destination Establishment ID (DED)**:
- Filter: `WHERE ValidTo >= CURRENT_DATE`
- Display format: Use pre-constructed DisplayString column: `[EstablishmentID] | [EstablishmentName] | [PortCode] | [EstablishmentType]`
- Sort order: Alphabetically by Establishment ID
- Search: Allow user to type partial Establishment ID, Name, or Port Code to filter list
- Example: User types "FF22B" → Dropdown shows "FF22B | COAL LOADING JETTY - BULKCOAL | AUABL | S15"
- Total dropdown size: ~2,456 entries

### 6.3 Auto-Population Logic

**Export Declaration Exemption Code (EEC)**:
- **Trigger**: On shipment download OR when shipment total value is edited
- **Logic**:
  - If Declaration > Movement > Direction = `EXPORT`:
    - If Shipment Total Value < AUD $2,000:
      - Set Declaration > Customs > Customs Code EEC = `EXLV` (Goods Under Value)
      - Set Declaration > References > Ref Type = `EDEC`, Ref Number = `EXLV`
    - If Shipment Total Value ≥ AUD $2,000:
      - Set Declaration > Customs > Customs Code EEC = blank
      - Set Declaration > References > Ref Type = `EDEC`, Ref Number = blank
      - **Interactive mode only**: Display validation popup: "Please choose an exemption code under EEC"
      - **Interactive mode only**: Block shipment save (prevent save action) until EEC is manually selected from dropdown
- **Special Validation for EXCC**:
  - If user selects `EXCC`, validate that Customs Control Number (CCN) exists in Declaration > Customs > Reference Numbers
  - If CCN is blank, display popup: "For EXCC, CCN reference input missing and is mandatory. Please choose another exemption code."
  - Block shipment save until CCN is provided OR EEC is changed to different code

**EDEC Reference Derivation**:
- **Trigger**: When EEC is set or changed
- **Logic**:
  - Set Declaration > References > Ref Type = `EDEC`
  - Set Declaration > References > Ref Number = EEC Code Value (e.g., `EXLV`, `EXCC`, `EXPE`)
  - Mark EDEC reference as non-editable (user cannot manually edit Ref Number field)
  - If EEC is changed, automatically update EDEC reference to match new value (auto-sync)

**Cargo Report Manifest Type (CRTYP)**:
- **Trigger**: On shipment download OR when Mode of Transport (MOT) is changed manually
- **Logic**:
  - If Declaration > Movement > Mode of Transport = `6` (Air):
    - Set Shipment > References > Ref Type = `CRTYP`, Ref Number = `C` (Consolidated sub-manifest)
  - If Declaration > Movement > Mode of Transport = `11` (Sea):
    - Set Shipment > References > Ref Type = `CRTYP`, Ref Number = `S` (Slot Submanifest)
  - If MOT is any other value:
    - Set Shipment > References > Ref Type = `CRTYP`, Ref Number = blank
  - Mark CRTYP reference as non-editable (user cannot manually edit Ref Number field)
  - If MOT is changed manually by user, automatically update CRTYP reference to match new MOT value (trigger on MOT change event, not just on shipment download)

**Origin/Destination Establishment Reference Storage**:
- **Trigger**: When user selects Origin Establishment ID (OED) or Destination Establishment ID (DED) from dropdown
- **Logic**:
  - When OED is selected:
    - Set Shipment > References > Ref Type = `ORESTB` (Origin Establishment ID)
    - Set Shipment > References > Ref Number = selected Establishment ID (e.g., `FF22B`)
    - Mark ORESTB reference as frozen/non-editable (user cannot manually edit Ref Number field; field appears disabled/grayed out in UI)
    - ORESTB reference can change only when user re-selects OED dropdown (new selection overwrites previous ORESTB value)
  - When DED is selected:
    - Set Shipment > References > Ref Type = `DNESTB` (Destination Establishment ID)
    - Set Shipment > References > Ref Number = selected Establishment ID (e.g., `FF22B`)
    - Mark DNESTB reference as frozen/non-editable (user cannot manually edit Ref Number field; field appears disabled/grayed out in UI)
    - DNESTB reference can change only when user re-selects DED dropdown (new selection overwrites previous DNESTB value)

### 6.4 Validation Popups and Save Restrictions

**Export Exemption Code (EEC) Validation Popup 1**:
- **Trigger**: User attempts to save export shipment with Shipment Total Value ≥ AUD $2,000 AND EEC = blank
- **Popup Message**: "Please choose an exemption code under EEC"
- **Popup Type**: Modal dialog with OK button
- **User Action Required**: Click OK to dismiss popup, then manually select EEC from dropdown before attempting save again
- **Save Restriction**: Shipment save action is blocked (save button disabled or save operation returns error) until EEC is populated
- **Mode**: Interactive mode only (batch mode or automated processing may skip this validation)

**Export Exemption Code (EEC) Validation Popup 2**:
- **Trigger**: User selects EEC = `EXCC` AND Customs Control Number (CCN) is blank in Declaration > Customs > Reference Numbers
- **Popup Message**: "For EXCC, CCN reference input missing and is mandatory. Please choose another exemption code."
- **Popup Type**: Modal dialog with OK button
- **User Action Required**: Click OK to dismiss popup, then either:
  - Enter CCN in Declaration > Customs > Reference Numbers, OR
  - Change EEC to different code (not EXCC)
- **Save Restriction**: Shipment save action is blocked until CCN is provided OR EEC is changed
- **Mode**: Interactive mode only

**Cargo Report Manifest Type (CRTYP) Auto-Update Notification**:
- **Trigger**: User manually changes MOT from one value to another (e.g., from 6 to 11, or from 11 to 6)
- **Behavior**: CRTYP reference automatically updates without popup (silent auto-update)
- **No Popup Required**: CRTYP change is transparent to user; no confirmation dialog needed

**Underbond Establishment Reference Freezing**:
- **Trigger**: User selects OED or DED from dropdown
- **Behavior**: ORESTB or DNESTB reference auto-populates and becomes frozen/non-editable
- **Visual Indicator**: Ref Number field appears disabled/grayed out in UI (user cannot type or edit)
- **No Popup Required**: Freezing behavior is transparent; user can only change reference by re-selecting OED/DED dropdown

---

## 7. Code Migration and Module Organization

### 7.1 Current State

**Reference File Processing Code Location**:
- All response handlers, parsers, and validation logic currently located in: `brokerage_boss_country_AU` module
- Country-specific implementation, not reusable for other countries

**Common Components**:
- Shared utility methods (file parsing, date conversion, string trimming) scattered across country module
- Common constants (delimiter characters, date formats, error codes) duplicated in multiple files
- Unit test cases co-located with implementation code

### 7.2 Target State (Migration Requirement)

**New Module**: `brokerage_boss_lib_Messages`
- Purpose: Centralized library for all message processing (inbound, outbound, reference files) shared across all SBSS countries
- Structure:
  - `Messages.ReferenceFiles.Handlers` → All reference file response handlers (one handler per file type)
  - `Messages.ReferenceFiles.Parsers` → File format parsers (PipeDelimitedParser, FixedWidthParser)
  - `Messages.ReferenceFiles.Validators` → Validation rules (DateRangeValidator, FKValidator, DataTypeValidator)
  - `Messages.ReferenceFiles.Common` → Shared constants, utility methods, base classes
  - `Messages.ReferenceFiles.Tests` → Unit test cases for all handlers, parsers, validators

**Migration Checklist**:
- [ ] Move all AU reference file response handlers from `brokerage_boss_country_AU` to `brokerage_boss_lib_Messages.ReferenceFiles.Handlers`
- [ ] Refactor handlers to use abstract base class `ReferenceFileHandler` with template method pattern (common flow: Decode → Parse → Validate → Update DB → Log)
- [ ] Move common methods (e.g., `TrimPipeDelimitedField`, `ParseCCYYMMDD`, `CalculateReciprocal`) to `Messages.ReferenceFiles.Common.Utilities`
- [ ] Move common constants (e.g., `PIPE_DELIMITER`, `DATE_FORMAT_CCYYMMDD`, `ERROR_CODE_MISSING_FIELD`) to `Messages.ReferenceFiles.Common.Constants`
- [ ] Move all unit test cases to `Messages.ReferenceFiles.Tests` with naming convention: `[HandlerName]Tests`, `[ParserName]Tests`
- [ ] Update configuration files (dependency injection, message routing) to reference new module paths
- [ ] Ensure no breaking changes to country-specific customizations (AU may override base handler behavior if needed)

**Configuration Files Affected**:
- `MessageHandlerRegistry.config` → Update handler registration to point to new module
- `DependencyInjection.config` → Register new library module services
- `Logging.config` → Update log categories for reference file processing
- `ErrorCodes.config` → Centralize error codes for reference file validation

### 7.3 Backward Compatibility and Rollback Plan

**Compatibility Strategy**:
- During migration, maintain **both** old and new code paths for 1 sprint
- Use feature flag `UseNewReferenceFileLibrary` (default = `false` in production until validated)
- If flag = `true`, route messages to new library handlers; if `false`, use old country-specific handlers
- After 1 sprint of parallel operation with no regressions, remove old handlers and set flag = `true` permanently

**Rollback Plan**:
- If critical bug discovered in new library, set feature flag = `false` to revert to old handlers
- Fix bug in new library while old handlers handle production traffic
- Re-enable new library after fix validated in test environment

---

## 8. Performance and Scalability

### 8.1 Processing Time Targets

- Single reference file processing (average size 1,000 records): < 2 minutes
- Large reference file processing (e.g., 2,456 establishment codes): < 5 minutes
- Tariff file group (8 files, combined ~10,000 records): < 15 minutes
- Total daily reference file processing (all 30+ files): < 30 minutes
- Processing must complete before 8 AM Sydney time to ensure brokers have current data when work begins

### 8.2 Database Optimization

**Indexing Strategy**:
- Create composite index on `(Code, ValidFrom, ValidTo)` for all reference tables to optimize expiration filtering
- Create index on `Type` column in Custom Code Value table to optimize dropdown queries (e.g., `WHERE Type = 'PRR' AND ValidTo >= CURRENT_DATE`)
- Create index on `EstablishmentID` and `PortCode` in EstablishmentRegistry table to optimize search performance for 2,456-record dropdown
- Create index on `DisplayString` in EstablishmentRegistry table to optimize dropdown population

**Batch Operations**:
- Use batch inserts (500 records per transaction) instead of single-row inserts to reduce database round trips
- Use `MERGE` (or `INSERT ... ON DUPLICATE KEY UPDATE`) instead of separate INSERT/UPDATE logic to reduce query count
- Commit transactions every 1,000 records to avoid long-running transactions and lock contention

**Connection Pooling**:
- Configure database connection pool size = 10 connections for reference file processing
- Use separate connection pool for reference file processing to avoid contention with user transactions

### 8.3 Error Recovery and Resilience

**Transaction Boundaries**:
- Each reference file processed in its own transaction (commit or rollback per file, not per record)
- Exception: Tariff file group (8 files) processed in single transaction to maintain consistency; if any file fails, rollback entire transaction and retry all 8 files together

**Partial Success Handling**:
- If record-level validation error occurs, log error details (record number, field name, error code, error message) and continue processing next record
- At end of file processing, if any records failed validation, mark job status = `Partial Success` and send summary email to SBSS Support
- Email format:
  ```
  Subject: Reference File Processing - Partial Success - [FILENAME]
  Body:
  File: [FILENAME]
  Total Records: [TOTAL_COUNT]
  Successfully Processed: [SUCCESS_COUNT]
  Skipped (Validation Errors): [SKIP_COUNT]
  
  Error Summary:
  - Record 123: Field 'StartDate' - Invalid date format '20241332'
  - Record 456: Field 'TariffGroupCode' - FK constraint violation (code 'ABC123' not found)
  ...
  
  Full processing log available in SBSS Admin > Reference File Processing Status > [FILENAME]
  ```

**Retry Logic** (BSIS side):
- BSIS retries file download and transmission 3 times with 5-minute intervals between attempts
- After 3rd failure, BSIS sends email to SBSS Support and stops retry attempts
- SBSS Support uses manual trigger to re-request file after investigating issue

**Deadlock Handling**:
- If database deadlock detected during insert/update, rollback transaction and retry entire file processing once
- If deadlock occurs on retry, mark job status = `Failed` and send email to SBSS Support

---

## 9. Monitoring, Logging, and Alerting

### 9.1 Processing Logs

**Log Levels**:
- **INFO**: File received, processing started, processing completed, record counts (inserted/updated/skipped)
- **WARN**: Record-level validation warning (e.g., missing optional field, data truncation)
- **ERROR**: Record-level validation error (e.g., FK constraint violation, missing mandatory field)
- **FATAL**: File-level error (e.g., decode failure, processing timeout, database connection failure)

**Log Storage**:
- Write logs to database table: `ReferenceFileProcessingLog` with columns: LogID, FileName, SourceIdentifier, ProcessingDateTime, LogLevel, RecordNumber (nullable), ErrorCode, ErrorMessage, StackTrace (nullable)
- Retain logs for 90 days
- Provide search and export functionality in SBSS Admin > Reference File Processing Status screen

**Log Examples**:
```
[2024-03-18 07:05:23] INFO: File received - XCHGRATE-P1-EDCHNG-2403180700.txt, Source: ICSXCHGRATE, Size: 1,234 bytes
[2024-03-18 07:05:24] INFO: Processing started - XCHGRATE-P1-EDCHNG-2403180700.txt
[2024-03-18 07:05:26] INFO: Processing completed - XCHGRATE-P1-EDCHNG-2403180700.txt, Total: 45 records, Inserted: 3, Updated: 42, Skipped: 0, Failed: 0
[2024-03-18 07:05:26] INFO: File processed successfully - XCHGRATE-P1-EDCHNG-2403180700.txt

[2024-03-18 07:10:15] INFO: File received - INSTRMNT-P1-EDMAIN-2403180705.txt, Source: TPS-INSTRMNT, Size: 12,345 bytes
[2024-03-18 07:10:16] INFO: Processing started - INSTRMNT-P1-EDMAIN-2403180705.txt
[2024-03-18 07:10:18] WARN: Record 123 - Field 'TariffGroupCode' - Referenced code 'XYZ999' not found in tariff reference table (FK validation warning)
[2024-03-18 07:10:19] ERROR: Record 456 - Field 'StartDate' - Invalid date format '20241332' (expected CCYYMMDD)
[2024-03-18 07:10:21] INFO: Processing completed - INSTRMNT-P1-EDMAIN-2403180705.txt, Total: 500 records, Inserted: 450, Updated: 48, Skipped: 2, Failed: 0
[2024-03-18 07:10:21] INFO: File processed with warnings - INSTRMNT-P1-EDMAIN-2403180705.txt

[2024-03-18 07:15:30] FATAL: File decode failure - PRRPSNAP-P1-EDMAIN-2403180710.txt, Error: Invalid Base64 encoding, corrupted payload
[2024-03-18 07:15:30] ERROR: Processing failed - PRRPSNAP-P1-EDMAIN-2403180710.txt
```

### 9.2 Email Notifications

**Trigger Conditions**:
- File processing fails after 3 retry attempts (BSIS sends notification)
- File processing encounters fatal error (decode failure, timeout, database error) (SBSS sends notification)
- File processing completes with partial success (some records skipped) (SBSS sends notification)

**Email Recipients**:
- To: SBSS Support team email alias (e.g., SBSS-support@Carrier Co.com)
- Cc: SBSS Admin lead

**Email Templates**:

**Fatal Error Notification**:
```
Subject: Reference File Processing FAILED - [FILENAME]

File: [FILENAME]
Source: [SOURCE_IDENTIFIER]
Processing Time: [TIMESTAMP]
Status: FAILED

Error:
[ERROR_MESSAGE]

Stack Trace:
[STACK_TRACE]

Action Required:
1. Investigate error cause in SBSS Admin > Reference File Processing Status
2. If file issue (corrupted, wrong format), contact BSIS team to re-transmit file
3. If SBSS issue (database error, code bug), escalate to development team
4. Use "Request Re-Processing" button in SBSS Admin to manually trigger re-processing after issue resolved

Processing log available at: [SBSS_ADMIN_URL]/ReferenceFiles/ProcessingStatus?file=[FILENAME]
```

**Partial Success Notification**:
```
Subject: Reference File Processing - Partial Success - [FILENAME]

File: [FILENAME]
Source: [SOURCE_IDENTIFIER]
Processing Time: [TIMESTAMP]
Status: PARTIAL SUCCESS

Summary:
Total Records: [TOTAL_COUNT]
Successfully Processed: [SUCCESS_COUNT]
Skipped (Validation Errors): [SKIP_COUNT]

Error Summary (first 10 errors):
- Record 123: Field 'StartDate' - Invalid date format '20241332'
- Record 456: Field 'TariffGroupCode' - FK constraint violation (code 'ABC123' not found)
...

Action Required:
1. Review error details in SBSS Admin > Reference File Processing Status
2. Determine if skipped records are critical (if yes, request file correction from CBF and re-process)
3. If skipped records are minor/edge cases, no action needed (successfully processed records are sufficient)

Full processing log available at: [SBSS_ADMIN_URL]/ReferenceFiles/ProcessingStatus?file=[FILENAME]
```

### 9.3 Monitoring Dashboard

**SBSS Admin Screen**: Reference File Processing Status

**Display Columns**:
- File Name
- Source Identifier
- Last Processed Date
- Last Processed Time
- Status (Success / Failed / Partial Success)
- Total Records
- Inserted
- Updated
- Skipped
- Failed
- Error Summary (first 100 chars)

**Actions**:
- **View Processing Log**: Drill-down to detailed log entries for selected file
- **Request Re-Processing**: Manually trigger re-processing of selected file (restricted to ADM role and SBSS Support)
- **Export Log**: Download processing log as CSV for offline analysis

**Filters**:
- Date Range (default: Last 7 days)
- Status (All / Success / Failed / Partial Success)
- Source Identifier (dropdown)

**Refresh**:
- Auto-refresh every 5 minutes during processing window (7 AM - 8 AM Sydney time)
- Manual refresh button

---

## 10. Security and Access Control

### 10.1 Reference Data Access

**Read Access**:
- All authenticated SBSS users can query reference data (expired filtering applied automatically)
- SBSS Admin screens (reference data search) restricted to BRK, ADM, COM roles

**Write Access**:
- Manual reference data entry restricted to ADM role only
- Manual reference data update restricted to ADM role only
- "Expire Code" action restricted to ADM role only

**Manual Trigger Access**:
- "Request Re-Processing" button restricted to ADM role and SBSS Support team (separate permission flag)
- All manual trigger requests logged in audit table with user ID, timestamp, justification

### 10.2 File Transfer Security

**BSIS to TPS**:
- HTTPS only (no HTTP fallback)
- Server certificate validation enabled
- Timeout: 30 seconds for connection, 5 minutes for download

**BSIS to SBSS**:
- XML message transmitted via secure message queue (encrypted in transit)
- Or XML message transmitted via HTTPS API endpoint (TLS 1.2 or higher)
- File payload encoded (Base64) to prevent injection attacks

**File Content Validation**:
- Validate file size before decoding (reject if > 50 MB)
- Validate decoded content length (reject if > 100 MB)
- Scan for SQL injection patterns in text fields (log warning if detected)

### 10.3 Data Residency and Compliance

**Australian Data Sovereignty**:
- All reference data stored in Australia-based database
- No cross-border data transfer to non-Australian servers

**Audit Requirements**:
- All reference data changes logged with timestamp, user ID, old value, new value
- Audit log retention: 7 years
- Audit log immutable (no delete or update allowed)

---

## 11. Testing and Validation Strategy

### 11.1 Unit Testing

**Test Coverage**:
- All response handlers: Test decode, parse, validate, update logic with sample files
- All parsers: Test pipe-delimited and fixed-width parsing with edge cases (empty fields, special characters, max length)
- All validators: Test date range validation, FK validation, data type validation with valid and invalid inputs
- All utility methods: Test date conversion, string trimming, reciprocal calculation with boundary cases

**Test Data**:
- Sample reference files (10-20 records each) provided by CBF or manually created based on ICD specifications
- Invalid files: Missing header, wrong delimiter, corrupted encoding, invalid date formats
- Edge cases: Empty fields, max length strings, special characters in descriptions, future-dated start dates, expired codes

**Test Execution**:
- Run unit tests on every code commit (CI/CD pipeline)
- Achieve >90% code coverage for reference file processing module

### 11.2 Integration Testing

**End-to-End Test Scenarios**:
1. BSIS crawls TPS test website, downloads XCHGRATE file, transmits to SBSS → SBSS processes file, updates Exchange Rate table, sends success log
2. BSIS sends AHECC file with expired codes → SBSS processes file, marks expired codes, filters from Tariff Number dropdown
3. BSIS sends INSTRMNT file with invalid FK → SBSS logs error, skips invalid records, processes valid records, sends partial success email
4. BSIS sends corrupted PRRPSNAP file → SBSS logs fatal error, sends failure email
5. BSIS sends tariff file group (8 files) → SBSS processes all 8 in single job, regenerates tariff structure
6. Admin manually triggers re-processing of LGMNTQST file → BSIS re-downloads file from TPS, transmits to SBSS, SBSS processes successfully
7. BSIS triggers manual seeding of establishment registry file → SBSS processes file, populates 2,456 codes, validates count
8. Broker creates export shipment < $2,000 → EEC auto-populated as EXLV, EDEC reference auto-populated
9. Broker creates export shipment ≥ $2,000 → EEC blank, save blocked until manually selected, popup displayed
10. Broker selects EXCC without CCN → Save blocked, popup displayed
11. Broker changes MOT from Air to Sea → CRTYP auto-updates from C to S
12. Broker selects OED → ORESTB reference auto-populated, frozen/non-editable
13. QNTUTCNV file processed → Reciprocal conversions generated (CF → CU and CU → CF)

**Test Environment Setup**:
- Mock TPS website with static reference files (controlled test data)
- BSIS test instance configured to crawl mock TPS
- SBSS test instance with clean reference data (seed tables before test)
- Test data reset between test runs

**Validation**:
- Verify record counts in database tables match expected counts from test files
- Verify expired entries excluded from dropdowns
- Verify auto-population logic triggered correctly (EEC, CRTYP, ORESTB/DNESTB, EDEC)
- Verify email notifications sent with correct content
- Verify processing logs contain expected INFO/WARN/ERROR entries
- Verify validation popups displayed with exact wording
- Verify save restrictions enforced for export exemption code scenarios

### 11.3 User Acceptance Testing

**Test Scenarios for Brokers**:
1. Broker searches for tariff code in SBSS Admin → Sees only active codes (expired codes hidden)
2. Broker opens commodity line, selects Tariff Number dropdown → Sees only active tariff codes
3. Broker creates export declaration with shipment value < $2,000 → EEC auto-populated as `EXLV`, EDEC reference auto-populated
4. Broker creates export declaration with shipment value ≥ $2,000 → EEC blank, cannot save until manually selected, popup displayed: "Please choose an exemption code under EEC"
5. Broker selects `EXCC` as EEC without CCN → Validation popup displayed: "For EXCC, CCN reference input missing and is mandatory. Please choose another exemption code.", save blocked
6. Broker creates import declaration with air shipment (MOT=6) → CRTYP auto-populated as `C`
7. Broker creates import declaration with sea shipment (MOT=11) → CRTYP auto-populated as `S`
8. Broker manually changes MOT from Air to Sea → CRTYP automatically updates from `C` to `S`
9. Broker selects Origin Establishment ID → ORESTB reference auto-populated, non-editable (field appears disabled/grayed out)
10. Broker selects Destination Establishment ID → DNESTB reference auto-populated, non-editable (field appears disabled/grayed out)
11. Broker searches for establishment ID by port code → Dropdown filters correctly, shows 2,456 codes
12. Broker manually changes EEC from EXLV to EXPE → EDEC reference automatically updates to EXPE
13. Broker searches for AHECC code with Assay Required = Y → Sees "ARI - Assay Required" warning in Tariff Warnings table
14. Broker opens UOM Conversions screen → Sees CF → CU = 0.02831600 and reciprocal CU → CF = 35.31466672

**Test Scenarios for Admins**:
1. Admin views Reference File Processing Status screen → Sees list of files processed today with success/fail status
2. Admin drills down into failed file → Sees detailed error log
3. Admin clicks "Request Re-Processing" for failed file → File re-processed successfully
4. Admin searches for expired tariff code with "Show Expired Entries" toggled on → Sees expired code with visual indicator (red text/strikethrough)
5. Admin manually adds new preference rule → Validates start/end dates
6. Admin manually expires instrument category → End date set to today, code filtered from dropdown immediately
7. Admin verifies establishment code count after manual seeding → Sees 2,456 codes, count ≥ 2,400 validation passed

---

This technical specification provides the foundation for implementing the reference file processing feature. Business analysts can use this document to derive acceptance criteria for individual work items by:
- Identifying which file types are processed in a given work item
- Cross-referencing §2 (File Format Specifications) for field-level details
- Cross-referencing §3 (Data Mapping Rules) for target database tables
- Cross-referencing §5 (Business Rules and Validation) for acceptance criteria logic
- Cross-referencing §6 (UI Integration) for user-facing behavior and auto-population rules
- Cross-referencing §6.4 (Validation Popups and Save Restrictions) for exact popup message wording and save restriction behavior

User stories:

title: Filter out Expired References from Project-A Reference Tables
description: As a SBSS user, I want expired references to be filtered out from all reference search result lists.
acceptance_criteria: Improvement requirement When any reference file listed below is opened or searched in SBSS Admin, or added on SBSS UI, system will check if End Date of any data is expired (earlier than system current date). If yes, the data will be filtered out from corresponding SBSS reference table or UI mapping field. AHECC User story: User Story 5138900: Load AHECC file for export tariff codes and descriptions - Boards SBSS Admin storage location: Tariff SBSS UI reflection: Commodity Tariff Number INSTRMNT User story: User Story 5212882: Load INSTRMNT file to link Instruments to tariff groups - Boards SBSS Admin storage location: Local Reference table - Instrument PRRPSNAP User story: User Story 5323982: Load PRRPSNAP into custom code and local reference table - Boards SBSS Admin storage location: Core Reference table: Custom Code Value (with Type PRR) Local Reference table - PreferenceRules SBSS UI reflection: Commodity Customs Code PRR INSCTGRY User story: User Story 5323987: Load INSCTGRY into custom code ICC and local reference table - Boards SBSS Admin storage location: Core Reference table: Custom Code Value (with Type ICC) Local Reference table - InstrumentCategories SBSS UI reflection: Commodity Customs Code ICC INSCTTRG User story: User Story 5323989: Load INSCTTRG into local reference table - Boards SBSS Admin storage location: Local Reference table - InstrumentCategoryTariff PRSPSNAP User story: User Story 5322900: Load Preference scheme descriptions from PRSPSNAP - Boards SBSS Admin storage location: Core Reference table: Custom Code Value (with Type PRS) SBSS UI reflection: Commodity Customs Code PRS CMNPTRSK User story: User Story 5169282: Process Community Protection Risk reference file (CMNPTRSK) - Boards SBSS Admin storage location: Local Reference table - CommunityProtectionRisk LGMNTQST User story: User Story 5151486: Process Lodgement Question reference file (LGMNTQST) - Boards SBSS Admin storage location: Local Reference table - LodgementQuestions CDLST11-??-?D User story: User Story 5013911: Process GST Exemption Code reference file - Boards SBSS Admin storage location: Core Reference table - Custom Code Value (with Type GST) XCHGRATE User story: User Story 4838159: Create mapping for exchange rate file from Project-A customs to SBSS - Boards SBSS Admin storage location: Core Reference table - Exchange Rate

title: Combine 8 Project-A Tariff Files into a Single Job - Analysis
description: As a SBSS developer, I want the connectivity between SBSS - BSIS - TPS to be setup sufficiently for the purpose of downloading and processing multiple AU tariff files in a single job.
acceptance_criteria: Refer to the following user story for detailed tariff files need to be covered: User Story 5525220: Combine tariff engine related reference files into one single processing job - Boards Action to be taken by BSIS: Daily: if either 1 of the 8 required tariff files are updated on TPS website, send all 8 files to SBSS. (Clarified in User Story 5528230) Action to be taken by Team-B: Process 8 tariff files above to regenerate tariff structure everyday, and add new reference on SBSS UI to store the latest tariff version date.

title: BSIS Connectivity for Multiple Project-A Reference Files - Part 2 (Discussion)
description: Below I state the task that BSIS needs to do. BSIS will need to send all reference files as per AU ICD v2.1(condition: send if file got updated or not; is stated in ICD) Special case request for 8 tariff files(if any of the 8 files got updated today, send all 8 files to SBSS) create a manual switch to send reference files to SBSS (for seeding purposes) 8 tariff files send any other reference files by name(exclude date stamp and extension) for example, a file name is AHECCSS-P1-EEMAIN-2308250138.txt, if required from IT/Business, we can simply request a specific file from AU Customs by sending BSIS the filename . e.g. "STCPSNAP-P1-EEMAIN", "TRETSTGP-P1-EEMAIN", or "TRETSNAP-P1-EEMAIN" from Main directory
acceptance_criteria: Integration Points BSIS will crawl and read the TPS Reference file webpage to look for specific reference file daily via HTTPS protocol. Daily execution – 7 AM Sydney time (UTC+12 during daylight saving time or UTC+11 during standard time) Crawl Au TPS Reference File change/main web directory searching for specific file name. If the file does not exist, execution ends. Optional - Main web directory only -> if the reference file is found, check the datetime stamp in the file name, if the datetime(yyMMddhhmm) is not equal today (need to compare time zone), do not send the content to SBSS. If the reference file exists, read the text file. BSIS reads the reference file content, encodes it, and sends it to SBSS. After 3 failed attempts, BSIS automatically sends an email to SBSS Support to manually investigate the issue. BSIS needs to standby a method for authorizing party to manually trigger fetching specific reference files after issue resolved. E.g., send xmlCustomsMessage to retrigger fetch specific reference files. Change Directory Reference File Reference file name Xml Source “XCHGRATE-P1-EDCHNG” <Source>ICSXCHGRATE</Source> Main Directory Reference Files Reference file name Check date stamp in file name before send to SBSS Xml Source “STCPCHAR-P1-EDMAIN” No <Source>ICSTARIFF-STCPCHAR</Source> “STCPMSAD-P1-EDMAIN” No <Source>ICSTARIFF-STCPMSAD</Source> “TRFCCHAR-P1-EDMAIN” No <Source>ICSTARIFF-TRFCCHAR</Source> “TRFCCONC-P1-EDMAIN” No <Source>ICSTARIFF-TRFCCONC</Source> “TRFCMSAD-P1-EDMAIN” No <Source>ICSTARIFF-TRFCMSAD</Source> “TRFCSNAP-P1-EDMAIN” No <Source>ICSTARIFF-TRFCSNAP</Source> “TRRPADDC-P1-EDMAIN” No <Source>ICSTARIFF-TRRPADDC</Source> “TFRPCHAR-P1-EDMAIN” No <Source>ICSTARIFF-TFRPCHAR</Source> “TFRPMSAD-P1-EDMAIN” No <Source>ICSTARIFF-TFRPMSAD</Source> “TRRPADDC-P1-EDMAIN” No <Source>ICSTARIFF-TRRPADDC</Source> “TRRPCHAR-P1-EDMAIN” No <Source>ICSTARIFF-TRRPCHAR</Source> “TRRPMSAD-P1-EDMAIN” No <Source>ICSTARIFF-TRRPMSAD</Source> “TRETSCTY-P1-EDMAIN” No <Source>ICSTARIFF-TRETSCTY</Source> “PRRPSNAP-P1-EDMAIN” No <Source>ICSTARIFF-PRRPSNAP</Source> "RRPCHAR-P1-EDMAIN” No <Source>ICSTARIFF-RRPCHAR</Source> “PRRPCTRY-P1-EDMAIN” No <Source>ICSTARIFF-PRRPCTRY</Source> “PRRPTRFG-P1-EDMAIN” No <Source>ICSTARIFF-PRRPTRFG</Source> “PRSPSNAP-P1-EDMAIN” No <Source>ICSTARIFF-PRSPSNAP</Source> “PRSRMSAD-P1-EDMAIN” No <Source>ICSTARIFF-PRSRMSAD</Source> “PRSCHRUL-P1-EDMAIN” No <Source>ICSTARIFF-PRSCHRUL</Source> “STCPCHAR-P1-EDMAIN” No <Source>ICSTARIFF-STCPCHAR</Source> “STCPMSAD-P1-EDMAIN” No <Source>ICSTARIFF-STCPMSAD</Source> “TRFCCHAR-P1-EDMAIN” No <Source>ICSTARIFF-TRFCCHAR</Source> “TRFCCONC-P1-EDMAIN” No <Source>ICSTARIFF-TRFCCONC</Source> “TRFCMSAD-P1-EDMAIN” No <Source>ICSTARIFF-TRFCMSAD</Source> “TRFCSNAP-P1-EDMAIN” No <Source>ICSTARIFF-TRFCSNAP</Source> “AHECCSS-P1-EEMAIN” No <Source>ICSTARIFF-AHECCSS</Source> “CDLST11-P1-EDMAIN” Yes <Source>TPS-CDLST11</Source> “INSTRTRG-P1-EDMAIN” No <Source>TPS-INSTRTRG</Source> “INSTRMNT-P1-EDMAIN” No <Source>TPS-INSTRMNT</Source> “AQSCMDTY-P1-EDMAIN” Yes <Source>TPS-AQSCMDTY</Source> “LGMNTQST-P1-EDMAIN” Yes <Source>TPS-LGMNTQST</Source> “CMNPTRSK-P1-EDMAIN” Yes <Source>TPS-CMNPTRSK</Source> “CMNPRPRF-P1-EDMAIN” Yes <Source>TPS-CMNPRPRF</Source> “STCPSNAP-P1-EDMAIN” Yes, if any of these 8 files got updated today, send all 8 files to SBSS individually <Source>ICSTARIFF-STCPSNAP</Source> “AQSCMSTC-P1-EDMAIN” <Source>ICSTARIFF-AQSCMSTC</Source> “PRSPCTRY-P1-EDMAIN” <Source>ICSTARIFF-PRSPCTRY</Source> “TFRPSNAP-P1-EDMAIN” <Source>ICSTARIFF-TFRPSNAP</Source> “TRETSNAP-P1-EDMAIN” <Source>ICSTARIFF-TRETSNAP</Source> “TRETSTGP-P1-EDMAIN” <Source>ICSTARIFF-TRETSTGP</Source> “TFRPADDC-P1-EDMAIN” <Source>ICSTARIFF-TFRPADDC</Source> “TRRPSNAP-P1-EDMAIN” <Source>ICSTARIFF-TRRPSNAP</Source> Please refer to the latest version of ICD for detailed integration requirement for AU reference files.

title: Migration of Project-A Reference Files
description: Need to migrate all codes related to AU reference files from brokerage_boss_country_AU to brokerage_boss_lib_Messages according to the latest version of design.
acceptance_criteria: Need to migrate all codes related to AU reference files from brokerage_boss_country_AU to brokerage_boss_lib_Messages according to the latest version of design. The parts which need to be migrared as below: All Response Handler of Reference Files Some common part related to Reference Files, Such as Common methods, Common Constants, etc. All Unit Test Cases of Reference Files Configuration Files related to Reference Files

title: Analysis on specific rate processing logic for AU tariff files
description: As a user, I want to define the applicable logic to process Ad Valorem Rate and Specific Rate for multiple AU tariff files.
acceptance_criteria: Issue clarification Team-B plan to combine 9 AU tariff files including Tarrif Rate Period Snapshot (TFRPSNAP) into one job, but based on the requirement of user story 5315121, three specific rates (SP) could be added to one charge specification, but other countries only have one. The gap needs to investigated. Analysis conclusion Before the TFRPSNAP processing, we were required to add the following data in SBSS Admin table Brokerage Charge Calc Method Rate Type. Calc Method Type 03/05/06/07 contains ad valorem and three specific rates respectively. The data source is unclear and cannot be found on TPS. When charge specification is added to an application and Calc Method 03/05/06/07 is selected, charge rate type AV and three SP will be added automatically based on data above. The value of three SP should be added based on the following items in TFRPSNAP file: SP (1): corresponding to Quantity Rate (added when Quantity Rate ≠ 0) SP (2): corresponding to Second Quantity Rate (added when Second Quantity Rate ≠ 0) SP (3): corresponding to Other Duty Factor Rate (added when Other Duty Factor Rate ≠ 0) If it's confirmed we can keep only one SP, the two extra SP for each Calc Method Type in Brokerage Charge Calc Method Rate table need to be deleted but as a result, Second Quantity Rate and Other Duty Factor Rate cannot be processed. Need to confirm if it's acceptable. TPS information for reference Customs Duty Calculation Routines

title: Differentiate import and export for AU tariff files combination - Analysis
description: As a SBSS developer, I want AU tariff files with dependencies with each other to be combined correctly, and import and export files are processed into different jobs respectively.
acceptance_criteria: The analysis from technical perspective will be started from the beginning of sprint PI7.3. And the conclusion will be added to the story once it's done.

title: Verification of AHECC Codes for Export tariffs and descriptions - Testing
description: This story is written to enable re verification of the exact specifications of AHECC codes for AU exports when loaded into SBSS either manually or automatically for updates from TPS.
acceptance_criteria: As a SBSS user, whenever I need to load AHECC codes, I would like to verify the following post the loading of the codes into SBSS : Verify that the AHECC Code is not exceeding 8 digits : Tariff Number, first 8 digits (Chapter, Subchapter, Heading, Subheading). Statistical and suffix are not used for AHECC. Verify that the AHECC Snapshot Start Date is < current date. Verify that the AHECC Snapshot End Date is > current date. Verify that the AHECC Snapshot Net Quantity Unit of Measurement is present and = tariff UOM Verify that the AHECC Snapshot Assay Required Indicator is present as Y/N. indicating that the tariff requires special treatment. If "Y" then verify if Tariff warning ARI - Assay Required is also present under Warnings. Verify that the AHECC Snapshot Description / Tariff description is max of 253 char. For example, when the following line is processed: AHECC Snapshot AHECC Code AHECC Snapshot Start Date AHECC Snapshot End Date AHECC Snapshot Net Quantity Unit Code AHECC Snapshot Assay Required Indicator AHECC Snapshot Description Action Indicator 01011020 01-01-02 31-12-11 NO Y LIVE PUREBRED BREEDING HORSES (EXCL. HORSES EXPORTED FOR RACING & BREEDING PURPOSES & SUBSEQUENT RE-IMPORTATION, HORSES FOR RE-EXPORTATION HAVING BEEN ORIGINALLY IMPORTED ON A TEMPORARY BASIS)

title: Verification of QNTUTCNV reference file - Qty & UOM Conversions - Testing
description: This story is written to test the source data and conversion factors provided by Customs whenever there arises a scenario when the Original Quantity UOM of the Tariff is different from the Target Quantity UOMs for the purpose of calculations of duties and taxes in AU Tariff. The file that is associated with this is called QNTUTCNV reference file on Quantity and Unit of Measurement conversions for the HSN commodities in AU. This contains primarily 3 variables : Original Quantity Unit of Measurement Target Quantity Unit of Measurement Conversion Factor. Where the formula is going to be : Target Quantity UOM = Original Quantity UOM * Conversion Factor.
acceptance_criteria: As a SBSS user, If the QNTUTCNV file contains the below information : CF CU 20000101000000000000 0.02831600 Then the following conversions must be added in UOM conversions table: I need to verify the following : in the Admin-->References-->UOM Conversions Screens : Source Measurements: Original Quantity Unit of Measurement is CF (Cubic Foot - Description) Target Measurements: Target Quantity Unit of Measurement is CU (Cubic Metre - Description) as above. If Target Measurements: Target Quantity Unit of Measurement is CC (Cubic Centimeter - Description), then I can see the below in the table : Verify that : 1 CF when converted to CU = 1* 0.28316 (Conversion Factor) and Verify that 1 CF when converted to CC = 1* 28317 centimeters CK G 20000101000000000000 100000.00000000 Verify that 1 CK (Quintal) when converted to Grams is : 1 CK = 1G * 100000 (Conversion factor) because 1 KG = 1000 G and 100KG = 1 CK (Quintal) Therefore 1 CK = 100*1000 G = 100000 G. Verify that the format of the QNTUTCNV table is as follows : CK G 20000101000000000000 100000.00000000 A. where CK - first 2 char is Original Qty UOM space B. G - 3rd position code for Target Qty UOM and 4th is empty because Target Qty UOM is only 1 char here. followed by fillers (all zeroes) space C. Creation of conversion rate date follows : CCYYMMDD which in this case is 2000.01.01. followed by fillers (all zeroes) and space D. Conversion rate is Numeric with total of 18 char including decimals. 100000.00000000

title: Update ICD for AU reference files processing
description: As a SBSS user, I want the ICD for AU reference file processing to be updated for covering all files implemented.
acceptance_criteria: Issue clarification Reference file QNTUTCNV is missing in ICD Related user story: User Story 5326559: Load Quantity Conversions into UOM Conversions The requirement on ICD was provided in BA Analysis task. Update requirement Refer to attached word document for the latest version of ICD file Refer to attached excel sheet for AU reference file list Ensure all the files listed in the excel, except for code list, are included in ICD.

title: Configuring Export Declaration Exemption codes - AU Exports
description: This story is being written to load or configure the various export declaration exemption codes that operations users can select for type of commodities that do not need to be submitted for customs approval but still need to be declared as part of cargo reporting in AU.
acceptance_criteria: As a SBSS user, A. I create a new Declaration Customs code type for Export Declaration Exemption codes under Declaration-->Customs-->Customs Code Code Name : EEC Code Type and Level : DE (Declaration Level) Code Description : Export Declaration Exemption Code with the following values in Lookup : Code type Code Description EEC EXCC CONTINGENCY CUSTOMS AUTHORITY NUMBER EEC EXDC AUSTRALIAN DOMESTIC CARGO EEC EXDD MILITARY GOODS. OWNED BY AUS. GOVERNMENT EEC EXLV GOODS UNDER THE VALUE OF $2000 (AUD) EEC EXML AUSTRALIA POST OR DIPLOMATIC MAIL BAGS EEC EXPE UNACCOMPANIED PERSONAL EFFECTS EEC EXSP AUSTRALIAN AIRCRAFT SPARES EEC EXTI CARNET OR TRYPTIQUE. TEMPORARY IMPORTS B. I can see that on an export shipment download into SBSS : a. If the shipment value is <2000 AUD, then this EEC is defaulted to EXLV automatically. b. If the shipment value is >= 2000 AUD, then this EEC is defaulted to blank and the user must manually choose the EEC from the customs codes dropdown list. c. I allow the shipment save (interactive mode only) only if the user manually chooses a specific EEC, else show popup "Please choose an exemption code under EEC" C. I can see that if the user chooses EXCC, I can only save the shipment if a Customs Control number CCN issued by TPS is available and mandatory for that shipment. If there is no CCN available, user sees an error popup that says " For EXCC, CCN reference input missing and is mandatory, else please choose any other exemption code" . D. I can see that whatever exemption code is reflecting against EEC is also shown against Declaration Reference Numbers as : Ref Type : EDEC Ref Number : Code value (EXLV/EXCC/EXPE etc.) so that the same is reflected on the Turbo screen under Declaration references. E. I can see that the derived EDEC reference from EEC is non editable and will automatically get updated whenever there is a change in EEC in interactive mode by user.

title: Configuration of Cargo Report/Manifest type for Cargo Reporting in AU
description: This story is written to enable configuration of the Manifest type selection for Cargo reporting. The type of manifest depends upon primarily the Movement type for the shipments under the manifest depending upon whether the movement is by AIR / SEA Not in scope of this story: For reference purposes only : C. These values are mapped under the GIS Segment for the MSG-ESM message sent to TPS for export cargo reporting. Example:
acceptance_criteria: As a SBSS user, A. I create a new Shipment reference to configure the values under this for cargo reporting message. Code : CRTYP Parent code : SH Description : Cargo Report Manifest Type B. I need to add this reference under Local lookup LPM with the conditions as : If Declaration-->Movement-->Mode of Transport MOT = 6 ( Air), then CRTYP = C ( Consolidated sub-manifest) (Shipment-->Movement-->Mode of Transport) If Declaration-->Movement-->Mode of Transport MOT= 11 (Sea), then CRTYP= S ( Slot Submanifest) (Shipment-->Movement-->Mode of Transport) C. The references CRTYP should be added via script into DT6 and DT5 and prod environments . D. If the MOT is any other type other than 6 (Air) or 11 (Sea), then CRTYP can be added with blank value. E. The trigger point for default CRTYP becoming C or S is on shipment download depending on MOT value. F. When the MOT is changed manually, then accordingly CRTYP Value also changes automatically.

title: Configuring all AU Establishment Codes into SBSS for ShipmentLevel Underbond creation
description: This story is written to be able to configure all of the AU Establishment codes for Origin, Destination and Discharge ports for the purpose of Underbond creation , both for Imports as well as exports. This list to be loaded into SBSS AU as a lookup so user can select the Origin Establishment ID and the Destination Establishment ID for Underbond message creation by TCS to TPS. for the purpose of Underbond creation at shipment level, the SBSS user must select the Origin and Destination Establishment IDs as part of parameters to be passed to TCS via BSIS entry packet message and this story is meant to upload the codes to SBSS UI under customs codes and capture the codes selected for underbond as shipment references.
acceptance_criteria: As a SBSS user, I create a new Customs code type OED for Origin Establishment ID as follows: Code Type : OED Description : Origin Establishment ID I create a new Customs Code type DED for Destination Establishment ID as follows: Code Type : DED Description : Destination Establishment ID. When I double click on the Customs Code OED or DED, I use the attached list of codes and establishment locations to create a lookup that has the following displayed on the screen for selecting any one Code: Code: Establishment ID Description: Estbmt ID Estbmt Name Port Code Est.Type FF22B S15 COAL LOADING JETTY - BULKCOAL AUABL S15 I can see that the Description populates the Establishment Name , Port Code and Establishment Type concatenated from the Table with Pipe Separators and I can see this on the SBSS UI as below : I can see the same list of 2456 codes is populated on screen as a lookup whenever I select either OED or DED for selecting and saving the Origin Establishment Code and Destination Establishment Codes. I am able to create 2 new shipment references as follows : Code: ORESTB Description : Origin Establishment ID Code: DNESTB Description : Destination Establishment ID I can see that the I am able to store the selected Establishment IDs under these references. Type : ORESTB Number : Establishment ID value / Code value selected under OED Type : DNESTB Number: Establishment ID value / Code value selected under DED 7. I can see that the values under these shipment references are frozen and non editable and can change automatically only whenever there is a change to OED or DED selection.