AR HSBC FAA Asset File (France) – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: Receivables · all Receivables reports

Overview

Extracts completed Receivables invoices and credit memos in the HSBC FACTORING FRANCE FAA file format, for invoice discounting. One 532 character record per document.

The report is implemented from the HSBC specification document “HFF - Invoice Discounting - Asset File IT Protocol.pdf” dated 09/09/2025, and its output has been validated against both the field table in that document and the sample file supplied with it. The 31 zones are, in order, each followed by a semicolon: File type FAA (3), Seller account number (5), File creation date YYYYMMDDHHMM (12), Debtor’s Seller Identifier (15), Debtor ID number (9), Debtor Activity Code (5), Debtor Country Code (2), Debtor’s corporate name (40), Debtor’s trade name (40), Debtor Address 1 to 3 (30 each), Debtor Town (30), Debtor postal code (10), Debtor contact name (20), Debtor telephone (15), Debtor fax (15), Document type (1), Document number (20), Document’s secondary number (1), Credited invoice number (20), Document issue date YYYYMMDD (8), Document due date YYYYMMDD (8), Document currency (3), Document amount including tax (15), Document amount excluding tax (15), Document VAT rate (4), Order reference (10), Other invoice reference (25), Invoice Status (30), Free field (30).

Document type is F for an invoice and A for a credit note. Amounts are always positive, are expressed in the smallest unit of the invoice currency with no decimal separator, and are zero filled to the left. The VAT rate is the highest rate on the document, expressed without a decimal point, so 20 per cent is 2000. The credited invoice number is populated on a credit memo with the invoice it is applied to, and is blank on an invoice.

Amount including tax is the document total, being the sum of all instalments. Amount excluding tax is that total less the tax lines. The due date is the earliest instalment due date. The debtor name, address, town and postal code come from the bill to site. Invoice Status is RGL, or LIT1 when the document has an amount in dispute. Semicolons, tabs and line breaks are removed from text zones so they cannot break a record.

Run with Output Format set to TSV and Exclude Column Headers set to Yes, and set the Server Output Directory runtime option to the directory the file should be written to. The file is written as FAA_.

HSBC require a .txt extension, CR LF line endings and ANSI encoding. The report carries hsbc_faa_rename.sh as its Custom Postprocess to apply all three, so the script must be installed as $XXEN_TOP/bin/custom/hsbc_faa_rename.sh and be executable. It only acts on the file in the Server Output Directory, so the concurrent request output is unaffected and View Output continues to work.

The Seller account number is allocated by HSBC and differs by currency for the same seller, so a file must cover a single currency. Currency is therefore a required parameter, defaulted to the functional currency of the selected operating unit. Where invoices are raised in more than one currency, run the report once per currency with the matching Seller account number.

Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.

Report Parameters

Operating Unit, Seller Account Number, Currency, Invoice Date From, Invoice Date To

Oracle EBS Tables Used

zx_lines, ra_customer_trx_lines_all, ra_customer_trx_all, ar_payment_schedules_all, hr_operating_units, ar_receivable_applications_all, hz_cust_accounts, hz_parties, hz_cust_site_uses_all, hz_cust_acct_sites_all, hz_party_sites, hz_locations, fnd_currencies

Report Categories

Enginatics, R12 only

GL Account Analysis, GL Account Analysis (Drilldown) (with inventory and WIP), GL Account Distribution Analysis, AR Aging

Running This SQL Without Blitz Report

The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.

Download & Import Options

Resource Link
Excel Example Output Example
Blitz Report™ XML Import AR_HSBC_FAA_Asset_File_France.xml
SQL Source AR HSBC FAA Asset File (France).sql
Full Report on Enginatics www.enginatics.com/reports/ar-hsbc-faa-asset-file-france/

Case Study & Technical Analysis: AR HSBC FAA Asset File (France)

Executive Summary

This report extracts completed Receivables invoices and credit memos in the HSBC Factoring France FAA file format used for invoice discounting, producing one 532-character record per document. French sellers that assign receivables to HSBC use it to build each submission’s asset file. The output is a delimited text file that is run through Blitz Report™ with Output Format set to TSV and written to the server output directory.

Business Challenge

The Solution

Technical Architecture (High Level)

Primary Tables

Logical Relationships

  1. Completed transactions (COMPLETE_FLAG = Y) in the selected operating unit are joined to their INV and CM payment schedules and grouped per transaction.
  2. The tax total comes from the TAX lines of RA_CUSTOMER_TRX_LINES_ALL, and the amount excluding tax is the total less that tax.
  3. The due date is the earliest installment due date, and the invoice status is LIT1 when any installment has an amount in dispute.
  4. The credited invoice is found in AR_RECEIVABLE_APPLICATIONS_ALL for applications of type CM with status APP, choosing the application with the largest amount.
  5. The bill-to customer account is joined to its party, then to the bill-to site use, account site, party site and location, supplying the debtor address.
  6. Records are built per document and ordered by debtor identifier and document number.

Parameters & Filtering

Performance & Optimization

FAQ

Q: What format does the HSBC France FAA asset file use? A: Each record is 532 characters long, contains 31 zones separated by semicolons, and is written as a text file with CR LF line endings and ANSI encoding by the custom post-process script.

Q: How are amounts written in the FAA file? A: Amounts are always positive, expressed in the smallest unit of the invoice currency with no decimal separator, and zero-filled to the left.

Q: Why must the report be run once for each currency? A: HSBC allocates a different seller account number for each currency, so a single file can only cover one currency and one seller account.

© Enginatics GmbH