Importing Patient Outstanding data into Odoo 16

In your current implementation of Odoo 10 or OpenERP 7, you may have outstanding invoices from customers, meaning there are invoices that have been posted but remain unpaid. These specific details can be transferred to Odoo 16 through the Odoo’s Excel/CSV import procedure. The provided guide offers detailed steps on incorporating the total outstanding amount as a single invoice line, labeled as 'Outstanding Payment'.

Before proceeding with the steps below, ensure you have completed the masterdata migration process as mentioned here and validated it.

Step 1: Retrieving the outstanding information from the current system

The outstanding data needs to be prepared as a CSV or Excel file in the following format.

On this template only the following fields needs to be updated.

partner_id → Patient Identifier
invoice_line_ids/price_unit → Outstanding Amount
currency_id/name → Name of your implementation currency

The following is a reference query which can be run on the openerp (or) odoo database to retrieve the patient id and their associated outstanding balance. Note: This query should be used as a reference to get the data and needs validation of the data. If your implementation uses additional addons, then the query needs to be altered.

COPY (SELECT p.ref, p.name, SUM(l.debit-l.credit) as amount  FROM account_move_line l LEFT JOIN account_account a ON (l.account_id=a.id) LEFT JOIN res_partner p on l.partner_id=p.id WHERE a.type IN ('receivable','payable') AND p.customer=true  AND l.reconcile_id IS NULL GROUP BY p.id,p.name, p.ref HAVING SUM(l.debit-l.credit) <>0) TO '/var/lib/pgsql/outstanding.csv' WITH CSV;

We recommend preparing multiple CSV files with row count 500 to avoid memory errors during the import process.

Step 2: Importing the CSV files

Once you have prepared the CSV data, follow the steps below for each CSV file to import it.

Navigate to Menu -> Invoicing -> Customers -> Invoices
Click on Favorites → Import Records
Upload the file by clicking on Upload File button
Review the mappings and click on Test
Once there are no validation errors, click on Import.
Repeat for all the CSV files.

Step 3: Setting the invoice type for imported invoices:

Once you have completed the import process for all the CSV, the type of the invoice needs to be updated. Run the following query inside the Odoo database.

update account_move set move_type= 'out_invoice' where id in (select id from account_move where state ='draft' and narration = '<p>Import Outstanding Payments</p>')

Step 4: Posting the invoices

Now navigate to Menu -> Invoicing -> Customers -> Invoices
Select all the invoices by clicking the Select All checkbox
Now Click on Action → Post Entries
In the following pop up, click on Post Entries to post all the invoices

Now you should be able to see that the outstanding information is visible for patients in Sales Quotation page or during the payment process.