SAP Business One Down Payments: A/R and A/P Down Payment Invoices Step by Step - tutorial featured image

Publications

SAP Business One Down Payments: Customer and Vendor Down Payment Invoices Step by Step

Hector Pincheira

Hector Pincheira

CIO | CTO
Digital Strategy & Technology Innovation

items

SAP Business One advance payments are what distinguish companies that collect payment before producing from those that finance their customers without realizing it. Imagine the scenario: You close a major sale with a 30% advance payment; the customer transfers the money, and your team records it as a simple payment on account. Three months later, the final invoice is issued for the full amount; the customer complains that they’ve already paid a portion, and the accounting department spends an entire afternoon piecing together the transaction history. Therefore, the problem isn’t collecting advance payments—it’s managing them without a formal process within the ERP system.

In this tutorial you will learn the complete advance invoice circuit: account configuration, step-by-step workflow for customers and vendors, the journal entries generated at each stage, and four SQL queries (in SQL Server and SAP HANA) to control every advanced currency unit. In addition, you will see the most costly mistakes and how to avoid them.

Why You Need to Master Advances in Your ERP

Advances are standard practice in custom manufacturing, imports, projects, and services. However, many companies manage them outside the system: a transfer recorded as a payment on account, a parallel Excel spreadsheet, and the salesperson's memory. As a result, three specific risks appear.

First, the double billing: if the final invoice does not deduct the advance payment, the customer is billed for the total amount and the business relationship is damaged. Second, tax disarray: in most locations, the advance payment generates VAT at the time of collection, and declaring it late carries penalties. Third, financial blindness: the advance money that has not yet been applied to final invoices is a liability with your client, and if you do not measure it, your projected cash flow Lies.

On the contrary, with the formal SAP Business One circuit, each advance payment is documented, posted, and applied. In this way, the clearing account reconciles itself and the status of each transaction can be queried.

SAP Business One down payments - management of down payment invoices in the work desk
An advance payment without a formal document in the ERP is an invisible debt to your client.

Prerequisites: Set Up Accounts Before Issuing

Before creating your first advance invoice, you need to have the account determination ready. First, go to Administration → Definitions → Financials → G/L Account Determination and check, in the Sales and Purchases tabs, the advance clearing account (down payment clearing account). This clearing account receives the advance payment while the final invoice does not exist; therefore, it must be a specific account and not a revenue account.

Then, open the business partner master, tab Finance: there you can assign a different clearing account per customer or vendor when the transaction requires it. Also, verify that your user has authorization for advance payment documents in Administration → System Initialization → Authorizations.

Finally, a point that causes confusion: the VAT treatment on the advance payment changes depending on the location of your database. For example, some localizations account for the tax when issuing the advance invoice and others upon receiving the payment. Consequently, validate this part with your accountant before going live.

SAP Business One Down Payments: The Complete Four-Stage Workflow

The entire process involves four documents: order, advance invoice, payment, and final invoice. Let’s walk through it using a real-world example: a sale of $10,000,000 with a 30% advance payment.

Step 1: Create the Customer Advance Invoice

Go Sales – Customers → Advance customer invoice. Select the customer and use the button Copy from → Sales orders to base the advance payment on the original order: this way you preserve complete traceability of the transaction. At the bottom of the document you will find the field for advance percentage; enter 30, and the system calculates $3,000,000 based on the total. This way, the document is linked to the order and does not affect inventory, because an advance invoice never moves stock.

Step 2: Record the Received Payment

When the money arrives, go to Bank Management → Received Payments, select the customer and mark the open down payment invoice. Record the payment method (transfer, check, or cash) and post it. As a result, the down payment is paid and the commitment to the customer is moved to the clearing account. This is the step that many companies skip: they collect payment, but leave the down payment invoice open, and the bridge account stops reflecting reality.

Step 3: Apply the Advance Payment to the Final Invoice

When shipping, create the Customer invoice since the order with Copy from. Before posting, check the footer of the document: next to the field Total advance There is a selection button that opens the list of the customer's available prepayments. Select the prepayment that was made, and the system deducts $3,000,000 from the total amount due. Therefore, the final invoice comes to $7,000,000, and the customer’s initial payment is reflected on the invoice. If the customer had multiple advance payments, you can apply more than one in the same window.

Step 4: Replicate the Process with Your Suppliers

The mirror circuit works the same in Purchases – Vendors → Vendor advance invoiceyou create the advance payment from the purchase order, you pay with Payments made and you apply the prepayment when you receive the supplier's invoice. For imports, for example, this process allows you to record the 100% paid prior to shipment and later reconcile it against the final invoice from the forwarder or manufacturer.

What Entries Each Stage Generates

Accounting logic is the heart of the process. Generally speaking, the advance invoice debits the customer account and credits the advance clearing account, along with VAT according to your location. Then, the payment received debits the bank and credits the customer account. Finally, when applying the advance to the final invoice, the system reverses the clearing account and recognizes the actual revenue. Consequently, the bridge account should trend toward zero: any remaining balance is a collected advance that nobody applied.

SAP Business One down payments - accounting control of the down payment clearing account
The advance clearing account must be reconciled every month: its balance tells a story.

SQL Queries to Control Your Advances

Customer advances live in the table ODPI and the ones from suppliers in ODPO; both share a structure with regular invoices. Furthermore, payments are linked through RCT2 (collections) and VPM2 (payments), where object type 203 identifies customer advances and 204 vendor advances. With that clear, these four queries give you total control.

Query 1: Open Customer Advances

This is the query you should review every week: it shows the advances that are still open, how much the client paid, and how many days they have been unapplied to a final invoice.

SQL Server

-- Open customer down payments with elapsed days
SELECT T0.DocNum      AS 'Down Payment No.',
       T0.CardCode    AS 'Code',
       T0.CardName    AS 'Customer',
       T0.DocDate     AS 'Posting Date',
       T0.DocTotal    AS 'Down Payment Total',
       T0.PaidToDate  AS 'Paid by Customer',
       T0.DocTotal - T0.PaidToDate AS 'Balance Due',
       DATEDIFF(DAY, T0.DocDate, GETDATE()) AS 'Days Open'
FROM ODPI T0
WHERE T0.DocStatus = 'O'
ORDER BY T0.DocDate

SAP HANA

-- Open customer down payments with elapsed days
SELECT T0."DocNum"      AS "Down Payment No.",
       T0."CardCode"    AS "Code",
       T0."CardName"    AS "Customer",
       T0."DocDate"     AS "Posting Date",
       T0."DocTotal"    AS "Down Payment Total",
       T0."PaidToDate"  AS "Paid by Customer",
       T0."DocTotal" - T0."PaidToDate" AS "Balance Due",
       DAYS_BETWEEN(T0."DocDate", CURRENT_DATE) AS "Days Open"
FROM ODPI T0
WHERE T0."DocStatus" = 'O'
ORDER BY T0."DocDate"

An advance with an open status and full payment is customer money waiting for its final invoice. Therefore, any line with many days open deserves a call to the responsible salesperson.

Query 2: Open Advances to Suppliers

The purchasing mirror answers another critical question: how much of your money is in the hands of suppliers without a final invoice? In imports, this number can be huge.

SQL Server

-- Outstanding vendor down payments to be applied
SELECT T0.DocNum      AS 'Down Payment No.',
       T0.CardCode    AS 'Code',
       T0.CardName    AS 'Vendor',
       T0.DocDate     AS 'Posting Date',
       T0.DocTotal    AS 'Down Payment Total',
       T0.PaidToDate  AS 'Paid to Vendor',
       DATEDIFF(DAY, T0.DocDate, GETDATE()) AS 'Days Open'
FROM ODPO T0
WHERE T0.DocStatus = 'O'
ORDER BY T0.DocTotal DESC

SAP HANA

-- Pending supplier advance payments to be applied
SELECT T0."DocNum"      AS "Advance Number",
       T0."CardCode"    AS "Code",
       T0."CardName"    AS "Supplier",
       T0."DocDate"     AS "Posting Date",
       T0."DocTotal"    AS "Advance Total",
       T0."PaidToDate"  AS "Paid to Supplier",
       DAYS_BETWEEN(T0."DocDate", CURRENT_DATE) AS "Days Open"
FROM ODPO T0
WHERE T0."DocStatus" = 'O'
ORDER BY T0."DocTotal" DESC

Query 3: Payments Linked to Advances

When you need to audit the complete circuit, this query joins each payment with its advance invoice through the RCT2 table, filtering by object type 203.

SQL Server

-- Payments applied to customer down payment invoices
SELECT T0.DocNum     AS 'Payment No.',
       T0.DocDate    AS 'Payment Date',
       T0.CardCode   AS 'Code',
       T0.CardName   AS 'Customer',
       T2.DocNum     AS 'Down Payment No.',
       T1.SumApplied AS 'Applied Amount'
FROM ORCT T0
INNER JOIN RCT2 T1 ON T1.DocNum = T0.DocEntry
INNER JOIN ODPI T2 ON T2.DocEntry = T1.DocEntry
WHERE T1.InvType = 203
  AND T0.Canceled = 'N'
ORDER BY T0.DocDate DESC

SAP HANA

-- Payments applied to customer down payment invoices
SELECT T0."DocNum"     AS "Payment No.",
       T0."DocDate"    AS "Payment Date",
       T0."CardCode"   AS "Code",
       T0."CardName"   AS "Customer",
       T2."DocNum"     AS "Down Payment No.",
       T1."SumApplied" AS "Applied Amount"
FROM ORCT T0
INNER JOIN RCT2 T1 ON T1."DocNum" = T0."DocEntry"
INNER JOIN ODPI T2 ON T2."DocEntry" = T1."DocEntry"
WHERE T1."InvType" = 203
  AND T0."Canceled" = 'N'
ORDER BY T0."DocDate" DESC

To audit vendor prepayments, replicate the same logic with the OVPM and VPM2 tables, changing the object type to 204. In this way, you cover both sides of the circuit with a single structure.

Query 4: Customer Advance Balance

This added version is management's favorite: a ranking of clients with open advances, ideal for the weekly finance meeting.

SQL Server

-- Ranking of customers with open down payments
SELECT T0.CardCode   AS 'Code',
       T0.CardName   AS 'Customer',
       COUNT(*)      AS 'Open Down Payments',
       SUM(T0.DocTotal)   AS 'Total Amount',
       SUM(T0.PaidToDate) AS 'Already Collected',
       MIN(T0.DocDate)    AS 'Oldest'
FROM ODPI T0
WHERE T0.DocStatus = 'O'
GROUP BY T0.CardCode, T0.CardName
ORDER BY SUM(T0.DocTotal) DESC

SAP HANA

-- Ranking of customers with open down payments
SELECT T0."CardCode"   AS "Code",
       T0."CardName"   AS "Customer",
       COUNT(*)        AS "Open Down Payments",
       SUM(T0."DocTotal")   AS "Total Amount",
       SUM(T0."PaidToDate") AS "Already Collected",
       MIN(T0."DocDate")    AS "Oldest"
FROM ODPI T0
WHERE T0."DocStatus" = 'O'
GROUP BY T0."CardCode", T0."CardName"
ORDER BY SUM(T0."DocTotal") DESC

Variations and Real-World Use Cases

The basic circuit allows for powerful variations. For example, in projects with billing milestones, you can issue multiple advance invoices for the same order (30% upon signing, 30% upon progress, 40% upon delivery) and apply them together to the final invoice. This way, each milestone is documented and auditable.

Another important distinction: the down payment it does not replace the advance invoice. The payment on account records money received without a document or VAT; instead, the advance invoice formalizes the commitment, calculates the tax, and is applied net on the final invoice. As a general rule, use payments on account only for installments without an associated transaction, and review the impact of each one in your VAT registry.

Additionally, you can automate the control: connect Query 1 to the alerts module to receive the advances open for more than 30 days every Monday. If you don't use alerts yet, in this tutorial from alert management in SAP Business One Here is the step-by-step guide.

SAP Business One down payments - down payment agreement with vendors in imports
In imports, the documented advance payment to the supplier protects your working capital.

Best Practices and Common Mistakes

First practice: reconcile the advance payment clearing account every month. Its balance must correspond exactly to the advances collected and not applied; any discrepancy reveals a manually applied advance or an improper manual entry.

Second: always base the advance payment on the order with Copy from. Loose advances work, but you lose traceability and follow-up queries become complicated. Third: define the standard advance percentage as a commercial policy along with the Payment terms, on a case-by-case basis.

Regarding errors, the classic one is posting the final invoice without applying the advance payment. The result: double billing to the client, an advance payment left open forever, and an inflated clearing account. The second error is using a revenue account as a clearing account; consequently, revenue is recognized twice and the income statement is distorted. The third is canceling advance invoices with applied payments without reviewing the accounting effect: first cancel the payment, then the document.

Official SAP Resources

To delve deeper, SAP maintains official process documentation: the guide How to Process Down Payments available in the SAP Business One Help Portal, and the forums of the SAP Community, where localization nuances are discussed. However, remember that the specific tax treatment in your country should always be validated with your accounting team.

Conclusion: Turn Advances into a Liquidity Advantage

Charging in advance is a competitive advantage only if the process is under control. With the advance invoice workflow properly set up, every upfront peso is documented, taxed, and applied; furthermore, the four queries in this tutorial give you weekly visibility without relying on parallel spreadsheets. This way, advances stop being an accounting risk and become what they were always meant to be: financing from your clients for your operation.

Do you already manage advances in your company? Implement Query 1 this week and let me know in the comments how many open advances you found: the number usually comes as a surprise. And if this tutorial saved you hours of trial and error, share the link with your finance team: the Strategic Circle exists for exactly that.

Most Read

QlikSense Hierarchy: parent-child chart of accounts expanded by levels
QlikSense Hierarchy: the Chart of Accounts That Doesn't Add Up
QlikSense Sum TOTAL: share of each family over the total
QlikSense Sum TOTAL: the Percentage That Changes When You Filter
SAP Business One Inventory Revaluation - featured image of the tutorial
SAP Business One Inventory Revaluation: Fix Wrongly Loaded Costs
Empty chamber with wooden benches: the counterweight that required being present to be used
The Veto You Had to Say in Person: Your Decisions With No Counterweight
SAP Business One customizations - featured image of the pre-upgrade audit tutorial
SAP Business One Customizations: Pre-Upgrade Audit
SAP Business One Customer Concentration - featured image of the tutorial
SAP Business One Customer Concentration: Measure Your Risk with SQL

RELATED ARTICLES