Reconciling the PO Liability Account - External GL Systems

Contents

This topic discusses:

Search Online Doc
Current Release Notes
















Overview

This section is one of two that describes how to reconcile the PO Liability Account. The discussion in this section contains instructions for Supply Chain users who export materials and invoice transactions to an external general ledger system. If your site uses General Ledger, Accounts Payable, and Materials Management, see Reconciling the PO Liability Accounts - GL Users.

PO liability means that there are POs that have been received but not matched with invoices.

The PO LIability Report pulls all materials transactions and all AP transactions up to the run period of the report by PO Number. Materials transactions have a credit to PO Liability and AP transactions have a debit to PO Liability. If the amounts do not net to zero, the report captures the PO data.

For each month in the fiscal year, you should reconcile the PO Liability Account balance with the PO Liability Report amount. If the two values are not equal, you need to find the source of any discrepancy. The process – reconciling the PO Liability Account – involves comparing data using Supply Chain tools with information available from your accounting system.

Additionally, the PO Liability Maintenance tool is available to assist in PO liability reconciliation. This tool avoids the situation in which a user has to manually create a zero dollar invoice to run a debit against the PO vendor GL account, and a credit against the AP invoice GL account to fix the PO liability issue. With non-file and non-stock items, any receipts can then be reversed. However, this approach is particularly problematic when the item is a stock-item. The item receipt cannot be reversed because that would impact the on-hand quantity.

The sections below describe the process and the tool.

For any period and year, the balance in the PO Liability Account is the dollar amount for purchase orders not yet matched with invoices:

PO Liability Account balance = the amount owed for purchase orders minus the total amount invoiced.

Transactions for materials (MM) and accounts payable (AP) are generated by Supply Chain as part of receiving and invoice matching. When items from a purchase order are delivered to a medical center, warehouse staff create Supply Chain receipts. A receipt record contains the dollar amount of the delivery. Eventually, invoices arrive for amounts on receipts. The invoices are matched to the receipts, and approved for payment.

A receipt generates an MM transaction in Supply Chain. The organization's inventory account is debited, and the PO Liability Account is credited with the receipt amount. Matching the invoice generates an AP transaction that debits PO Liability and credits AP Liability. For example, transactions for a $100.00 receipt and matching invoice would look like this:

Receipt

Debit

Credit

Inventory $100.00  
  PO Liability $100.00
 
Invoice

Debit

Credit

PO Liability $100.00  
  AP Liability $100.00

Both MM transactions for receipts and AP transactions for invoices are extracted (written to) the GL Transaction Table in Supply Chain. (You can write reports on the table's contents using the GL Transactions MM and AP Report Object.) The PO Liability Report, available from the Reports menu, pulls data from the GL Transactions Table.

When you request a PO Liability Report, Supply Chain looks for transactions in the GL Transactions Table. Any set of transactions with a non-zero balance – that is, any purchase order amount that is not relieved by matching invoices – is a candidate for the PO Liability Report. The report shows transaction amounts and the total amount for the period and year that you specify.

Transactions for materials and invoices are exported to a site's external General Ledger system. Sites usually have one or more scheduled jobs that run the export. (See Export Materials and Accounts Payable Transactions for more information on setting up export jobs.) Once imported into the external GL system and posted to the PO Liability Account, transactions can be compared with PO Liability Report data from Supply Chain. See Figure 1.

Figure 1 - Overview: Supply Chain Transaction Processing and Export to External GL System

How can PO Liability Account data in an external GL system be out of balance with the Supply Chain PO Liability Report?

The account total and the Report total can be different if:

Summary of the PO Liability Account Reconciliation Process

Summary Steps

  1. Run the PO Liability Report for the organization, period, and year that you are reconciling. .
  2. Look at the balance of the PO Liability Account in your general ledger system for the organization, period, and year.
  3. Compare the amounts from Step 1 and 2; that is, determine if the total for the Report is the same as the account balance.

    • If the account balance and the Report balance are the same, you are finished. The account and report are reconciled.

  4. Otherwise, look for the source of the discrepancy, and verify that you have identified anything that could affect the reconciliation.

    • If a difference exists, and the account total is higher,

    - Verify that the accounts have no ongoing activity. If staff are continuing to match invoices, the totals for the period can shift.
    - Double check that the PO Liability Account and the Report were reconciled in previous months. If not, earlier out-of-balance amounts could have been carried forward.
    - Verify that no manual journal vouchers have been posted to the PO Liability Account.
    - Ensure that neither invoice nor materials transaction data were changed after export. Double check the date and period, quantities, item costs, extended costs, and totals for any receipt or invoice transactions that are different from the exported transactions.

    • If a difference exists, and the Report total is higher,

    - Verify that the accounts have no ongoing activity.
    - Double check that your GL system received all the transmissions sent by Supply Chain.
    - Look at your journal entries and ensure that everything transmitted was posted.
    - Ascertain that the PO Liability Account and the Report were reconciled in previous periods.
    - Ensure that neither invoice nor materials transaction data were changed after export.

  5. Follow your hospital's procedures in reckoning the account, if needed.

Detailed Discussion and Tools

Using the PO Liability Maintenance Tool - Available beginning with Release 7.9.0.1

This utility uses the standard ERP import process to bring in amounts for PO liability and tax liability (if relevant) for a specified organization and purchase order. The utility creates an AP transaction with the type "PO Liability and Tax Liability Write Off."

The amount in the transaction is: PO liability amount + tax liability amount.

Attachments to the AP transaction are the GL transactions for PO Liability, Tax Liability, and Write-Off GL accounts.

Inter-org GL accounts will also exist when the organization for the write-off account is different from the organization for the PO.

Security: The role object "POLiabImport" controls the user's ability to work with this new tool. The default setting is None.

To use the new PO liability maintenance tool,

Make sure that the role object "POLiabImport" in your user role has the setting All or Create.

Step 1: Create the .csv file for the import
  1. Create the upload file using an Excel spreadsheet. The first two rows are headers and upload information. The data columns begin on row 3.
1 A POLiab The name of the import type. ERP recognizes this name as a PO Liability and Tax Liability Write-Off file.
1 B Customer ID The name of your ERP site. If you do not know this this value, mouse over the Current Settings header above your current settings, or call the Help Desk.
1 C Date Use the format MM/DD/YYYY
2 A Org

This is the column header for the organization field.

2 B PONo The column header for the purchase order number.
2 C POLiabAmt The column header for the PO liability amount from the PO.
2 D TaxLiabAmt The column header for the tax liability amount.
2 E GLAcctWriteOff The column header for the write-off account from your chart of accounts.
3 and succeeding A-E data Your data for each column: organization, PO number, PO liability amount, tax liability amount, GL write-off account.
Last row Last column EOF This string must be the last element in the file.

Figure 2 is an example of a spreadsheet for the import.

Figure 2 - Sample Import Formatted as .xlxs

  1. Save the spreadsheet to a local network folder as a .csv file. The file name must be unique.
    - Also, save the file as an .xls or .xlsx file, so that you can read it later if you need to.
Step 2: Upload the .csv file for import into ERP.

This step uses the standard ERP upload process.

  1. From the Materials Management main Contents, select Imports, Exports and Financials > PO Liability Write Off Import.
    Or, from AP, select Imports / Exports > PO Liability Write Off Import.

A list of imports appears, if any have been created (Figure 3). Otherwise, No Data appears.

Figure 3 - Sample List of PO Liability Write Off Imports

  1. Click New. The import panel appears (Figure 4).

Figure 4 - PO Liability Write Off Import Panel

  1. Enter the file that you wish to import in the File to Upload field.
  1. At this step, you have a choice. You can work through the import process in two stages or in one stage. For new users of this feature, we recommend the two-stage approach.
    - To use the one-stage approach, click Submit. This approach loads and imports its data in one step.
    - To use the two-stage approach, select Load and verify, No import. Then click Submit. This approach first loads the .csv file and validates it. You can correct errors, if needed, and upload the file again. You can then process it to import its data.
Step 3: Review the import (Figure 3)

The following fields provide useful status data:

Import Status - The successful or unsuccessful status of the upload.

Complete: The .csv file uploaded and imported its data successfully.

Errors: The .csv file could not be read or validated.

Ready for Processing: If you have used a two-stage upload, the file is ready for the second (update) step.


Processed -
The number of data records processed out of the total uploaded. This number indicates how many of the import file records successfully performed their respective actions on General Ledger account records.

n of X: X is the number of data records uploaded and n is the number of records processed.

For a one-stage upload, this field indicates whether the system could make changes to existing GL account information using the imported file's records, and, if so, how many records processed successfully. 0 of X means that the system was not able to process any of the imported file's records. For a two-stage upload, 0 of X is normal.

Running the PO Liability Report

The PO Liability Report Summary has two versions: one version produces output in Adobe Acrobat format; the other version outputs data to an Excel spreadsheet. You can select either, according to your preference.

To run the PO Liability Report:

  1. From the General Ledger (or Accounts Payable) main Contents, select Reports > Standard Report Definitions.
    The list of report definitions appears.
  2. Locate the PO Liability Report Summary (which produces Adobe Acrobat output),
    or, if you prefer, locate the PO Liability Report Summary (File), which produces an Excel spreadsheet. (Figure 2)

    Hint: You can find the report definition using the Search panel or the Position To field.

Figure 2 - List of Report Definitions

  1. Click Menu > Print Report.
    The Print Report panel appears (Figure 3).

Figure 3 - Print Report Panel for the PO Liability Report

  1. Enter the year and period for the report in the field Rpt Acct YrPrd (YYYYPP).
    - Enter the year using four digits.
    - Enter the period as two digits. Use 0 in periods zero through nine (e.g., 00, 02).
    - Run the year and period together with no space or other character between them (see Figure 3).
  2. Select the Organization code from the prompt.
  3. Click Submit.
    The system sends a message that the report has run, and is available from My Archived Reports or from Completed Reports.
  4. Click Completed Reports to view the report.
    Note: Long reports may take a few minutes to run (and often the PO Liability Report is long).
    Several reports may appear in a list. The top report is the most recently run.
  5. Next to the report, click View Report.
    If you ran the report for Adobe output, Adobe Acrobat opens.
    If you ran the report for Excel output, the system displays the Open dialog panel from Windows.
    - Click Open. A spreadsheet appears containing the report data.

Figure 4 is a sample PO Liability Report Summary using Adobe output. The first page and the last page of the report are in the figure. The red arrow points to the location of the amount on the last page of the report to use in the reconciliation.

Figure 4 - Sample First Page (top) and Last Page (bottom) of a PO Liability Report

View the PO Liability Account balance on your GL system

Ascertain the beginning and ending balance for the period in the PO Liability Account. Your GL system's interface should provide information on a lookup or report basis.

Compare the results of Step 2 and Step 3.

Calculate the difference between the PO Liability Report Balance and the PO Liability Account Ending Balance for the period.

- If the difference is zero, or below a threshold set for your organization, no further reconciliation is needed.
- Otherwise, proceed to Step 4 (see the Steps above) to look for discrepancies.

Finding the Sources of Discrepancies

View AP and materials transactions exported to your GL system from Supply Chain

This information is available from two reports that you will run. The reports show what Supply Chain exported.

Run the report Invoice Total Count by Match Total by Transmission ID for the period, year, and organization.
This report lists exported matched invoice transactions by Transmission ID. Each line in the report contains the Transmission ID, Period and Year, Match Total dollars and the number of invoices in the transmission.

Note: This report is output as an Excel spreadsheet. The columns are not labeled. To run a version of the report with labeled columns, copy the report into My Report Definitions. Then, edit the report to remove the flag from the Data Only box. (See Figure 5)

Figure 5 - Turning on Column Labels for Spreadsheet Reports

To run the Invoice Total Count by Match Total by Transmission ID report:

  1. From the AP or Materials main Contents, select Reports > Standard Report Definitions.
  2. Locate the report using Position To or Search.
  3. Click Menu > Print Report.
  4. Enter the Period, Year, and Organization. (See the previous discussion about the format for the Period and Year.)
  5. Click Submit.
  6. Click Completed Reports to view the report.
    Note: Long reports may take a few minutes to run.
    Several reports may appear in a list. The top report is the most recently run.
  7. Next to the report, click View Report.
    - Click Open. A spreadsheet appears containing the report data.

Run the report GL Trans Total Amount by Transmission ID for the period, year, and organization.
This report lists exported materials transactions (i.e., receipts) by Transmission ID. Each line in the report contains the Transmission ID, Period and Year, and the Transaction Amount.

Note: This report is output as an Excel spreadsheet. The columns are not labeled. To run a version of the report with labeled columns, copy the report into My Report Definitions. Then, edit the report to remove the flag from the Data Only box. (See Figure 5)

To run the GL Trans Total Amount by Transmission ID report:

  1. From the AP or Materials main Contents, select Reports > Standard Report Definitions.
  2. Locate the report using Position To or Search.
  3. Click Menu > Print Report.
  4. Enter the Period, Year, and Organization. (See the previous discussion about the format for the Period and Year.)
  5. Click Submit.
  6. Click Completed Reports to view the report.
    Note: Long reports may take a few minutes to run.
    Several reports may appear in a list. The top report is the most recently run.
  7. Next to the report, click View Report.
    - Click Open. A spreadsheet appears containing the report data.

Use the transactions reports as follows:

- Sum the individual transaction amounts in each of the two "...by Transmission ID" reports. Calculate the totals for the invoice and receipt transactions posted to the PO Liability Account for the period and year. Compare the report and account totals for invoices and receipts. This activity will help you determine whether the source of the imbalance is the invoice or receipt transactions.

- Depending on the source of the imbalance, for invoices, perform a detailed comparison of Transmission IDs and Match Total Dollars from the report with your system's import records. Similarly, for materials transactions, compare the report's Transmission IDs and Transaction Amounts to the same information in your import records.
These two comparisons will help you verify that your general ledger system received what Supply Chain exported,

- If your GL system uses the Supply Chain Transmission ID to generate a journal voucher number, you can verify that all the transmissions generated journal vouchers which were then posted.

More Research Possibilities

Using Supply Chain,
For any questionable MM transactions without matching AP transactions, review dates on invoices to make sure that the invoices were matched in the appropriate period.

Using your General Ledger system,
Review details of the transactions that were posted to the PO Liability Account. Look for any changes that could have been made inadvertently by staff on site.

- Check for changes in dates and amounts.
- Check for deletions; for example, a deleted invoice transaction that is not paralleled by a receipt transaction.

Look for postings to the PO Liability Account that were made by a source other than Supply Chain. Supply Chain writes transactions to the MM and AP source journals.

- Check for manual postings to the PO Liability Account in other journals.