Integration of Pharmaceutical Chargebacks with Quickbooks

Quickbooks Desktop accounting software is often used by small and mid-size pharmaceutical manufacturers. Yet, it does not come with the tools to process pharmaceutical chargebacks. As the result, manufacturers are using specialized tools to process pharmaceutical chargebacks. Processed chargebacks then need to be loaded into the Quickbooks for revenue management, financial analysis and tracking account receivables.

This case study should help pharmaceutical manufacturers to configure Quickbooks with regards to the chargebacks processing.

Pharmaceutical Chargeback – ERP Integration Overview

The general data flow for the chargeback processing and integration with ERP is as follows:

• Wholesalers send chargeback requests as debit memos (DM) as either EDI 844 documents, or as flat files in MS Excel or CSV formats.

• Each debit memo must have DM# and Date, and some ID of the DM issuer on the header level. Each DM also has a number of DM lines showing sales transaction details. Each chargeback line specifies pharmacy and product codes, the quantity of units, the contract and its pricing, invoice number and date.

• Chargeback submitter is usually identified by the DC DEA or HIN numbers. Products are identified by their NDC-11 codes. The end customers are identified by their DEA, HIN or 340B identifiers.

• Chargeback processing software receives and validates these requests.

• The approved chargebacks amount can be positive, zero, or negative. Credit or debit documents must be created in ERP for any of the amounts, including zeros.

• The distribution centers have to be mapped by their DEA or HIN numbers to EPP customer accounts. Products have to be mapped by their NDC to ERP product records. This mapping can be either provided to the Chargeback program, or be a part of the Chargeback-ERP integration tool.

• After validation, the chargeback program issues a payment request to ERP for the approved amount.

• The chargeback request always contains header level fields: DM# and date, submitter ID, chargeback amount approved and other fields to accommodate resubmissions and validation on the ERP level.

• The chargeback request may optionally include detail lines, showing the number of units sold and chargeback amounts by each product. Some manufacturers want to see only total amount of chargebacks paid by DM. Other want to see split by product. And other want to see dollars and units by product.

• Upon receiving the request, accounting software creates a credit memo for the specified amount, and communicates the document number and creation date back to the chargeback program as a payment response.

• The chargeback program reads the payment response and includes its details into the chargeback reconciliation document. The chargeback reconciliation is sent back to the wholesaler in EDI 849 or other formats.

Specifics of integrating Chargebacks with Quickbooks

While any integration of chargebacks with Quickbooks also follows the general steps described above, there are some important factors, which have to be considered.

The first factor is a variety of ways the customers, products and chargebacks can be setup in Quickbooks.

The second factor is that Quickbooks system ID of customer and products records are not visible to the users. The system IDs are mandatory fields to have to arrange the mapping. Therefore, a specialized software is required to export Quickbooks customer and product records with their system ID.

Configuration and Mapping

Wholesaler Setup

Wholesalers are always setup in Quickbooks as Customers.

In addition to that, some manufacturers, in consideration of chargebacks as Account Payble, may set wholesalers as Vendors as well.

Another choice a manufacturer has is if each Distribution Center (DC) is recorded as a separate customer Ship-To account, or only one customer record is created for the whole wholesaler, even though its many DCs purchase products and send chargebacks separately.

Quickbooks allows multiple ship-to accounts to be associated with one company. Such accounts are created as Jobs under a Customer record. Quickbooks treats the Ship-To customer records created as Jobs as regular customer accounts. Each Ship-To customer has a separate system customer ID.

If customer records are indeed created for each DC, it is a good practice to create a custom field on the customer level for DEA#.

The number of custom fields that can be created on customer, vendor and item level is described in detail at: https://blog.outoftheboxtechnology.com/managing-custom-fields-in-quickbooks-desktop/. The custom fields are created by Quickbooks administrators.

We recommend that the custom field name should be treated by the integration software as a setting rather than being hard-coded, because another software tool  integrating with Quickbooks can already have this field created. An example of such tool using DEA# is EDI processing software.

The flexible configuration prevents the field name conflict from happening.

With DEA field available, the mapping from the chargeback submitters to their corresponding accounts in Quickbooks can be based on matching DEA, and be fully automated. This is the best option.

Custom fields can be created on a vendor level as well. However, there are no options to associate multiple ship-to accounts with the vendor record, and storing DEA for each DC is not practical.

When DEA field is unavailable, the mapping between submitters and customer or vendor records must be done by the integration software.

At the end of the day, each DC has to be mapped to either a Customer, or to a Vendor record.

 

Product Mapping

When distribution centers are set as customers, chargebacks are recorded as either invoices or credit memos, depending if the chargeback amount was positive or negative. Chargeback product details are shown on an Item level.

Manufacturers may choose item type Service to track chargebacks for each product separately.

Each NDC-11 is set as a separate item. Some manufacturers create a custom field NDC-11 on an item level to automate the mapping between NDC specified in the chargebacks to Quickbook items. Others may use an existing field Item Name/Number. A proper integration software will support both of these options.

If wholesalers are set as Vendors, the chargebacks are recorded as Bills and Vendor Credits, and then chargeback product details are stored through GL Accounts. GL Account may relate to the chargebacks of the whole product family, or to be specific to NDC-9, or even to NDC-11 levels.

Integration software needs to know how each NDC-11 is mapped to either GL Account, or to an Item.

Chargeback Documents

Different Quickbooks documents created for chargebacks require filling in different fields. Some of these fields are just mandatory, some are visible and helpful to users, and some are there for better system validation.

For example, Bill documents have reference and memo fields, which could be filled in with DM# and other chargeback description. Bill document needs to be linked with an Account Payable, has to have a due date and payment terms, etc.

Invoices have a different set of fields to populate: account receivable, PO number, item and customer sales tax settings, etc.

While the chargeback amounts are always stored, tracking the number of units sold is optional and has its prerequisites. For example, bills and invoices relying on Service Item just do not support item quantity and cannot be used if the manufacturer wants to track chargeback units.

Manufacturers may create separate templates for different types of chargeback documents. The integration software has to pick up the right templates.

The integration software has to be adjusted to generate all required fields on each document level according to the manufacturer’s specifications.

 

Multiple Quickbooks Company Files

Often, a company needs to manage multiple Quickbooks files. Examples of such companies are 3PL companies and manufacturers, maintaining separate books for products with different labeler codes. The proper integration software allows the users to easily switch between the companies.

Incorrect addressing Quickbooks files often causes naming conflicts. These naming conflicts happen when the same file is opened by users using its local network address, like I:\MyFiles\QBFile.QBW, and software opening this file using its UNC location \\Myserver\MyFiles\QBFile.QBW.

Location of the current Quickbooks file can be found on the screen showing up as a response to pressing F2 button, under the field File Information-Location.

The easiest way to resolve naming conflict is to consistently use the same type of address.

QuickBooks Desktop Integrator from Relasoft Solutions

One of the nice software tools to use to integrate pharmaceutical chargebacks with Quickbooks is Quickbooks Integrator https://relasoft.net/quickbooks-integrator/, written by Relasoft Solutions (Relasoft).

Relasoft Quickbooks Integrator results in fully automated chargeback processing, starting with receiving chargebacks via EDI and files, doing full validation and processing, issuing payment documents in Quickbooks and sending 849 back to the wholesalers.

Relasoft Quickbooks Integrator is a part of the Pharma Suite https://relasoft.net/business-solutions/, yet designed to be used separately.

The program offers the following features:

1. Unlimited mapping for customers and products. That covers wholesaler accounts set as Customers and Vendors, with either one customer record, or with multiple ship-to.

2. Extensive validation and exception handling, including taking care of adding new DC accounts and new products.

3. Full support of all documents used by the manufacturer to records chargebacks: invoices, credit memos, bills and vendor credits.

4. Auto-creation of all axillary fields shown on Quickbooks documents, as per manufacturer specifications.

5. Comprehensive integration with Relasoft Enterprise Pharma Suite and its Chargeback processing and Government Pricing solutions.

7. Built-in ability to export from Quickbooks all stored data for communication with CRM, BI and other systems.

7. Support for multiple companies, stored in multiple Quickbooks files.

 

If you are looking for an advice of purchasing Quickbooks and would like to plan its handling of chargebacks, give us a call at 416-783-1027 for an advice. It is free.