Validating Billing File Format

Generate a compliant billing file for a vendor (e.g., Vonage/APIV):

Filename: APIV_20231230_000001.CSV.gz   (scheduled)
Filename: APIV_E20231230_000001.CSV.gz  (ad-hoc/error correction)

H,000003,20230630133914,VONAGEAPIV           N
D,00000001,APIM,,APIM-879878-1,O,50.00,N,,20230601,20230630,API Monetisation Monthly Charge,,,,,,5,,,AUD,
T,000003,20230630133914,APIV,00000001,50.000

Each record LF-terminated (Hex 0A). Gzip the final CSV. No empty files — header + trailer always present even with zero detail records.

Progress:

• Step 1: Determine file type (scheduled vs ad-hoc) and construct filename
• Step 2: Build header record (H)
• Step 3: Build zero or more detail records (D)
• Step 4: Build trailer record (T) with counts/sums matching details
• Step 5: Validate every field against format/length/nullability rules
• Step 6: LF-terminate all records, gzip, and confirm non-empty file

Step 1: Filename

Format: XXXX_YYYYMMDD_NNNNNN.CSV.gz

• XXXX: 4-char uppercase source code (e.g., APIV)
• YYYYMMDD: file creation date
• NNNNNN: 6-digit sequence, zero-padded, 000001→999999 then wraps
• Ad-hoc/error files insert literal E before the date: APIV_E20231230_000001.CSV.gz
• Scheduled and ad-hoc sequences are independent counters, each starting at 000001

Step 2: Header record (Record Type "H")

Step 3: Detail records (Record Type "D")

Key fields and rules:

• Record Type: always "D"
• Record Sequence Number: 8-digit, zero-padded, starts at 1, increments per file
• Product Type: 4 chars, e.g. APIM
• Account Id: optional, 30 chars
• Service Id: non-null, 40 chars, format PREFIX-AgreementID-N (e.g. APIM-879878-1); forbidden chars: \ { } ^
• Charge Type: U (usage), R (recurring), O (other) — use O for aggregated usage
• Charge: non-null, up to 10 chars, 2 decimals, may be negative (e.g. -12.12, 11.00)
• GST Exempt Flag: Y/N
• Start Date / End Date: YYYYMMDD; End Date required for Recurring charges and must be ≥ Start Date
• Details: non-null, 200 chars, forbidden chars: \ { } ^
• Quantity: optional for Recurring, positive, no decimals required
• Rate: optional for Recurring, 2 decimals, non-negative
• Currency: AUD
• Unused/N-A fields (Charge Description, Charge Time, Charge Units, Charge Unit Type, Usage Type, Sub type code, General 3) left blank unless specified otherwise

Example:

D,00000001,APIM,,APIM-879878-1,R,50.00,N,,20230601,20230630,API Monetisation Monthly Charge,,,,,,5,,,AUD,

Step 4: Trailer record (Record Type "T")

Example:

T,000003,20230630133914,APIV,00000007,211.97

Step 5: Validation checklist

• Header sequence number and trailer sequence number match
• Record count in trailer == actual number of D records
• Total Charge Amount == sum of all Charge fields (to 3 decimals)
• All non-null fields populated; nullable fields either valid value or empty
• No forbidden characters (\ { } ^) in Service Id or Details
• Dates parse as valid YYYYMMDD; End Date ≥ Start Date when present
• File not empty — header + trailer present even with 0 detail records
• Every record LF-terminated

Example 1: Zero-detail file (still valid) Input: Vendor has no charges for the day. Output:

H,000004,20230701020000,VONAGEAPIV           N
T,000004,20230701020015,APIV,00000000,0.000

Example 2: Multi-record file with mixed charge types Input: One recurring charge ($50.00) and one usage charge ($161.97). Output:

H,000003,20230630133914,VONAGEAPIV           N
D,00000001,APIM,,APIM-879878-1,R,50.00,N,,20230601,20230630,API Monetisation Monthly Charge,,,,,,1,50.00,,AUD,
D,00000002,APIM,,APIM-879878-1,O,161.97,N,,20230601,20230630,API Monetisation Usage Charge,,,,,,,,,AUD,
T,000003,20230630133914,APIV,00000002,211.970