Correction Guide for Legacy UDO Issues

January 22, 2007

![](media/image1.jpeg){width="2.75in" height="2.75in"}

Office of Finance and Accounting

**Table of Contents:**

-   **Purpose**

-   **Background -- Legacy Data in STARS**

-   **Reconciling PO, CDR, and GL**

-   **Tools to Identify Legacy Issues**

-   **Correcting Legacy Data**

    -   **Contract Data Report**

    -   **Cost Overruns Converted from DISCAS SUMM-BNR**

    -   **Refund Receivables**

    -   **Inter-Entity Revenue and Collections**

    -   **Program 82 Historical Data**

    -   **Credit T code Adjustments**

-   **Appendices**

> **A. STARS GL to PO Undelivered Orders Reconciliation Process**
>
> **B. Verification of Cumulative Obligations - Procurement to STARS
> Obligation Reconciliation**
>
> **C. Helpful Tips**

# Purpose:

This guide is an aid for offices to identify and correct legacy CID
obligation/cost/pay data issues that remain in STARS. While this
document contains information on tools to use and specific directions to
correct some known issues, other issues will need extensive research to
determine the proper adjustments required. With this in mind, where
possible, this document also provides background information to help
users in identifying and resolving issues not specifically addressed in
this document.

## Background - Legacy Data in STARS

All ongoing award historical data was converted from DISCAS to STARS.
This includes cumulative obligations, cost, and payments for all award
activity. This allows users to confirm and obtain contract history
without relying on the legacy system. Several prerequisites were
required for the historical contract data to be converted correctly.
First, DISCAS data sets should have been in agreement. Specifically
SUMM-BNR and GL balances should have been in agreement. Additionally,
any negative SUMM-BNR lines should have had no uncosted or unpaid
balances (negative obligations must have been offset with negative cost
and payments). Finally, cost or payments could not exceed obligations at
the detail level.

If any current differences in STARS data between the CDR, GL, or PO are
the result of DISCAS data sets being out of balance, the user must
research DISCAS data to determine the proper approach and entries
needed. This document includes tools that will help offices identify if
current issues are related to DISCAS data not being in agreement. Also,
this document addresses negative amounts converted from DISCAS. In
addition, for any cost overruns at conversion amounts were recorded in a
specific SGL account (61009900).

In addition to DISCAS data related issues, conversion issues occurred in
some cases due to process changes in STARS. One known process change
conversion issue is related to any outstanding Refund Receivables at
March 31, 2005. A few unexplained conversion differences exist as well.

### Reconciling PO, CDR, and GL

The STARS team recently issued guidance for GL to PO Undelivered Orders
Reconciliation (see appendix A). This process involves a three way
reconciliation to GL, PO, and CDR balances by CID. With the recent
changes made to the CDR report (see Contract Data Report below), no
differences should exist between the GL UDO balances and the uncosted
amount per the CDR report. Any reconciliation that includes PO balances
can only be performed in the current period. However, the agreement of
these three items is critical to ensure auditable UDO balances. Given
this, GL to PO UDO reconciliation should be the first comparison used to
identify differences and used as verification that all corrections have
been properly made.

In addition, this process is a good starting point to identify CIDs that
need further review to determine if any legacy issues exist. If total
obligations per the current CDR do not agree with total obligations per
the source document, a manual comparison can be made of the source
document with the CDR as of March 2005. A March 2005 STARS CDR to DISCAS
CDR comparison report is being created to identify possible legacy
issues that are not identified during UDO Review Phase I. Any
differences noted by this report must be researched in DISCAS. To obtain
access to DISCAS data, each office should contact its former DISCAS
service center (Albuquerque, Headquarters, or Oak Ridge).

### Tools to Identify Legacy Issues:

-   GL to PO Undelivered Orders Reconciliation

    -   Report does a three way comparison between PO, CDR, and GL UDO
        balances. This report also has drill down capabilities to
        identify specific AFF differences for a specified CID. See
        Appendix A for detail STARS process document.

-   61009900 Analysis

    -   Report identifies any amounts recorded to 61009900 during
        conversion due to legacy data issues. See section titled \"Cost
        Overruns Converted from Summ-BNR\".

-   Credit T code Analysis

    -   Report identifies credit t codes used in conversion where
        uncosted/unpaid amounts exist. Any uncosted/unpaid balances from
        credit t codes result in PO and GL differences. See section
        titled \"Credit T code Adjustments\".

-   March 2005 STARS CDR to DISCAS CDR Comparison

    -   Report compares DISCAS SUMM-BNR data (obligations, cost,
        payments) to STARS CDR report at March 31, 2005, to allow
        verification of converted data. (Instructions will be provided
        when report is promoted to production)

-   Procurement to CDR total obligations comparison

    -   Report compares procurement system contract obligations to total
        obligations reported on the STARS CDR. For the CIDs without
        differences between the two systems, offices should rely on the
        procurement system amount instead of having to pull paper
        documents during the obligation verification review. See
        Appendix B.

**Correcting Data Including Legacy Issues**

This table provides a summary of the various adjustments for different
scenarios. Following the summary are detailed procedures and background
information for correcting known issues.

+-------------+--------+--------------+-------------------------------+
| PO UDO      | GL UDO | CDR UDO      | Adjustments                   |
| Balance     |        | Balance      |                               |
|             | B      |              |                               |
|             | alance |              |                               |
+-------------+--------+--------------+-------------------------------+
| Wrong       | Agrees | Agrees       | Correct PO with obligation or |
|             |        |              | receipt entries - Reverse     |
|             |        |              | these entries in GL           |
+-------------+--------+--------------+-------------------------------+
| Agrees      | Wrong  | Wrong        | Correct GL UDO and CDR UDO    |
|             |        |              | with direct GL entries        |
|             |        |              | recorded in the current       |
|             |        |              | period.                       |
+-------------+--------+--------------+-------------------------------+
| Wrong       | Wrong  | Wrong        | If all three are off by the   |
|             |        |              | same amount, make needed      |
|             |        |              | entries via the PO module.    |
|             |        |              |                               |
|             |        |              | If PO is off by different     |
|             |        |              | amount, correct PO with       |
|             |        |              | obligation or receipt         |
|             |        |              | entries. Determine current    |
|             |        |              | difference and correct GL UDO |
|             |        |              | and CDR UDO with direct GL    |
|             |        |              | entries recorded in the       |
|             |        |              | current period.               |
+-------------+--------+--------------+-------------------------------+

**Agrees --** Balance agrees with source documents

**Wrong** -- Balance does not match source documents

**Note: If the GL UDO and CDR UDO balance are different amounts, the
help desk should be contacted.**

**Contract Data Report**

The Contract Data Report (CDR) contains cumulative contract information
for obligations, cost, and payments. The following describes at a high
level how the information for the report is calculated in STARS. For a
more detail description, see STARS document PO0043 (Contract Data
Report).

The CDR report contains historical information from the legacy system as
well as all ongoing activity in STARS. The legacy system information was
captured by taking the DISCAS SUMM-BNR data as of March 31, 2005, and
subtracting the current year information from the cumulative amounts.
This provided contract history as of September 30, 2004. This data was
converted into STARS GL in period SEP-04. The DISCAS GL data (contract
balances) were converted into period SEPADJ1-04. To not misstate GL
contract balances, period SEP-04 information was reversed in SEPADJ1-04.
FY 2005 DISCAS GL data was converted into STARS each month from October
through March. As long as offices had no DISCAS GL and SUMM-BNR
differences, the March 2005 STARS CDR should agree with the DISCAS CDR
at conversion.

The Contract Data Report has recently been modified to pull cumulative
data from the current year. Originally the CDR report pulled data from
September 2004 and from each GL period October through September Adjust
1 period starting with 2005. Now the CDR pulls data for prior activity
from the October Adjust 1 period of the current year. This is the period
that creates beginning balances for each fiscal year. This has been
accomplished by establishing a new SGL sub-account to capture (4902
activity -- obligations paid) from the previous year. The SEP-04
(cumulative DISCAS data) 4902 amounts were adjusted from 4201 to 420101
in period OCTADJ1-05. The same adjustment was made in FY 2006 and 2007
based on 4902 activity for the previous year. All other SGL accounts
used to calculate obligations, cost, and payments do not close at year
end. Therefore, only the closing of 4902 into 4201 required adjustments
at the beginning of each fiscal year.

The contract data report pulls data from the GL from the periods:

OCTADJ1-XX through Date requested of year XX. Therefore, the March 2005
CDR will be derived by including the periods OCTADJ1-05 through MAR-05.
A January 2007 CDR will include periods OCTADJ1-07 through JAN-07.

To calculate obligations, cost, and payments the CDR uses SGLs 4801
through 4982 and 420101 (prior year cumulative amounts). For regular
operating funds the following SGLs are used to calculate cumulative
amounts:

Obligations = 4801 + 4802 + 4901 + 4902 + 420101

Cost = 4901 + 4902 + 420101

Payments = 4802 + 4902 + 420101

Corrections to the CDR:

If obligations, cost, and pay are **understated** on the CDR, the
following adjusting entry should be made: Use the detailed AFF string in
question and debit 42010000 without CID and credit 42010100 with CID. If
the amounts are **overstated**, credit the 4201 and debit 420101. These
entries can be processed in the current month.

If the uncosted amount on the CDR needs to be corrected, the GL UDO
should be misstated as well. Given the balance in the PO module is
correct, direct GL entries into the current period will be required.

Entries for regular operating funds would be:

Obligations only debit credit

10102000 10101100

31011100 31012000

46101300 48010000

Obligations and cost debit credit

10102000 10101100

31011100 31012000

46101300 49010000

31070900 57000000

61000000 21900000

Cost only debit credit

48010000 49010000

31070900 57000000

61000000 21900000

**If the PO is incorrect as well as the balances in the GL, obligation
and/or cost entries should be made through the PO module.**

**Cost Overruns Converted from SUMM-BNR**

At the conversion of DISCAS, records in SUMM-BNR where costs exceeded
obligations were put into STARS GL but couldn't be loaded to the STARS
PO module. This is due to the fact that the STARS PO Module would not
allow costs to exceed obligations. So at the point of conversion, the
STARS GL cost account (i.e. 61000000) was reversed and moved to the cost
overrun account. This was accomplished by making a credit to 61000000
and a debit to 61009900 in the STARS GL. These entries were loaded into
STARS in the MAR-05 or APR-05 timeframe. These excess costs are not
reflected in the PO module unless an office has already taken action to
correct the overruns. This creates a reconciling item between STARS GL
and STARS PO modules. Corrections to converted overruns may require
coordination with Procurement and Budget for additional obligation of
funds.

To verify these overruns are still outstanding, perform the following
tasks:

1.  Go into the STARS GL Inquiry responsibility and run the "DOE GL
    Journal Detail Report (CSV output)." The parameters should be:
    Period From = MAR-05, Period To = SEPADJ1-06, GL Posted Status =
    Posted, Allottee From and Allottee To = your office's value, SGL
    From and SGL To = 61009900. Pull the data into Excel. Build a pivot
    table as follows: Place the SGL and CID fields in the Row area,
    place the Period Name in the Column area and place the Net Amount
    field into the Data area.

2.  If a CID has a balance and it is not an integrated contractor (IC),
    your office has an issue with cost recorded in GL that isn't in the
    PO module. A balance on an integrated contractor CID would reflect a
    similar problem. Any IC balances in 61009900 should be corrected by
    the IC submitting correcting entries as current month transactions
    reversing the 61009900 entry and recording the cost (61000000) to
    the appropriate AFF. IC cost overruns are discussed in the document
    "Handling Cost Overruns" located on the I-MANAGE web page.

3.  Execute the "GL to PO Undelivered Orders Reconciliation" report from
    STARS Discoverer viewer (See Appendix A) to verify whether or not
    you have a difference between PO and GL for the CID's identified in
    step 2. If you have no difference between STARS PO and STARS GL,
    generate a direct GL batch in an ADI spreadsheet for the particular
    AFF(s) as follows to complete the correction:

> 61000000 dr
>
> 61009900 cr

4.  If you have a difference between PO and GL on the "GL to PO
    Undelivered Orders Reconciliation" report, complete the following:

    a.  Process a receipt through the PO module for the amount of the
        61009900 transaction on the affected AFF.

    b.  Pull the batch that was created by the receipt in step a. above.
        Build an ADI spreadsheet reversing all entries generated by the
        receipt in step a.

    c.  Include in the ADI spreadsheet in step b, the following
        transactions for the amount of the 61009900 on the necessary
        AFF:

> 61000000 dr
>
> 61009900 cr

**Refund Receivables**

At the time of establishing a refund receivable, STARS does not and
should not create any budgetary account offsets. However, in DISCAS, the
process was different. At the time of the establishment of the refund
receivable (BSC 143x) an offset credit cost (BSC 8132) was established.
The BSC 8132 credit cost impacted the budgetary authority reported in
the SUMM-BNR data set by creating a reduction in costs and increasing
obligation authority. Therefore, it is necessary to perform the
following steps listed below to bring the PO module and the General
Ledger into agreement with one another for converted data, provided
correcting entries have not already been made:

> 1\. Verify that the Refund Receivable (143x) amounts in DISCAS equal
> what you show as the Refund Receivable (1310Ex) amounts in STARS as of
> March 2005. They should agree.
>
> 2\. Record a PO Receipt for the Refund Receivable balance at the time
> of conversion (March 05 ending balance). This step correctly restates
> the uncosted balance in accordance with SGL accounting functionality
> that was brought forward into the PO module from the SUMM-BNR data
> set.
>
> 3\. Reverse the PO Receipt entries out of GL that were recorded in
> Step 2 above. This step is necessary because the BSC to SGL crosswalk
> programs had already corrected the out of balance condition for
> uncosted obligations that existed between the DISCAS/MARS trial
> balance SGL accounts and the uncosted obligation balances in the
> SUMM-BNR data set.

**Inter-Entity Revenue and Collections**

Inter-Entity activity within the STARS accounting system are handled two
ways. First, if the performing office is an integrated contractor, the
integrated contractor records inter-entity costs for authorizing
integrated contractor or authorizing DOE field office against the true
program and fund where the work is actually being performed and bills
and records an offset entry in a refunds receivable account. When the
performing integrated contractor receives a check from the authorizing
office it records the collection by debiting cash and crediting the cost
account where the original costs were recorded. Therefore, an IC should
not have any uncosted/unpaid balances in Fund 00911.

Second, if the performing office is a DOE field office, the performing
DOE field office records all inter-entity activity in Fund "00911" in
the following programs:

Program 1721310 -- Cost of Work Performed

Program 1721311 -- Earned Revenue or Collection

Program 1721312 -- Costs of Worked Performed for Natl. Labs

Program 1721313 -- Earned Revenue or Collection from DOE Natl. Labs

The authorizing DOE field office will record the costs in the true fund
and program that is incurring the costs.

Program "1721311 and Program "1721313" in Fund "00911" are associated
with revenue earned and/or collected. Therefore, these programs should
not be included when comparing the GL UDO Report or the Contract Data
Report to the PO UDO Report. The GL to PO Comparison Report will be
enhanced to include a separate worksheet that identifies this activity.
To make the report available as soon as possible, the current version
without this additional worksheet has been put into production. If the
identified difference with the PO UDO balance is fund 00911 and program
value 1721311 or 1721313, no adjustments are needed.

**Program 82 Historical Data**

At conversion, program 82 should not have had any uncosted/unpaid
balances. Any balances should have been transferred to YN19 in DISCAS
before the STARS conversion. Even if no balances existed, the credit
side of program 82 (82XX02000) could affect the total obligations
recorded on a CDR for non integrated contractors. Some offices
established miscellaneous documents to accept cost transfers from
others, and would obligate funds as program dollars and deobligate
program 82. The total obligations per the CDR report for these
miscellaneous documents would be zero. To match total obligations to
source documents for these items, the cumulative amounts of program
82XX02 should be omitted. If the difference in total obligations on
miscellaneous documents is the 82XX02, this should be noted on UDO
spreadsheets but no adjustments should be made.

**Credit T code Adjustments:**

The STARS modules will not allow entries that create balances less than
zero. In DISCAS, several business processes used negative entries, such
as cash work and contract transfers. These processes created negative
obligations, cost, and payments in DISCAS SUMM-BNR. Since STARS will not
allow these entries into the modules, credit "T" codes were established
for conversion. This allowed absolute values to be entered in the STARS
modules and the credit "T" code created negative balances in GL. As a
result, all cumulative contract information was converted to STARS. The
STARS CDR data comes from GL. Therefore, the CDR data in STARS should
agree with DISCAS CDR data at conversion.

At conversion, all negative items in DISCAS SUMM-BNR should have had
obligations, cost, and pay equal to each other (no uncosted/unpaid
balances). For any converted records that did not have this condition,
adjustments were made in April 2005. However, since go live, several
adjustments have been made to these credits. As a result, PO UDO
balances will not agree with GL UDOs due to the negative "T" codes.

The recommended corrective action is to have the STARS PO team run an
SQL STARS PO report that identifies all PO credit "T" code shipments
that do not have obligations, cost, and pay amounts equal to each other.
A team (to be identified) will make correcting entries in the PO and AP
modules to realign these amounts. After all records are properly
aligned, a report will be distributed to each office showing its PO
detail items that have credit "T" codes. After the office reviews, the
team will permanently close the detail items in the PO module so that no
one can adjust these items in the future. This will preclude anyone from
accidentally adjusting these credit items and recreating PO and GL
differences.

**Appendices:**

**A.** **STARS GL to PO Undelivered Orders Reconciliation Process**

GL to PO Undelivered Orders Reconciliation

*(Post Go-Live Data Review & Reconcile)*

for

*Department of Energy IMANAGE Program - STARS Project*

+------------------+---------------------------------------------------+
| Deliverable ID:  | GL to PO Undelivered Orders Reconciliation (Post  |
|                  | Go-Live Data Review and Reconcile)                |
+------------------+---------------------------------------------------+
| Version number:  | 1.01                                              |
+------------------+---------------------------------------------------+
| Draft/Final as   | *6 December 2006*                                 |
| of:              |                                                   |
+------------------+---------------------------------------------------+
| Printed on:      | *12 November 2023*                                |
+------------------+---------------------------------------------------+
| Author:          | Sonali Chaturvedi                                 |
|                  |                                                   |
|                  | Sonali.Chaturvedi@hq.doe.gov                      |
|                  |                                                   |
|                  | \(301\) 903-4362                                  |
+------------------+---------------------------------------------------+
| Owner:           | Sonali Chaturvedi                                 |
+------------------+---------------------------------------------------+

Document information

Document source

This document is maintained as an online document. Contact the author
for the latest version.

Revision history

  --------- --------- ------------------------------------- -----------------
  Version   Date      Summary of changes                    Revised By
  number                                                    

  1.01      12/6/06   Initial version                       Sonali Chaturvedi

                                                            

                                                            

                                                            

                                                            

                                                            
  --------- --------- ------------------------------------- -----------------

Approvals

The following people have approved this document. (Sign below name)

  -------------------------------------- --------------------------------
  Name                                   Function

  Warren Huffer                          DOE I-MANAGE Program Manager

  **Signature**                          **Date:**
  -------------------------------------- --------------------------------

  -------------------------------------- --------------------------------
  Laura Kramer                           DOE I-MANAGE Stars Project
                                         Manager

  **Signature**                          **Date:**
  -------------------------------------- --------------------------------

Distribution

This document has been distributed to:

  -------------------------------------- --------------------------------
  Name                                   Function

  Laura Kramer                           DOE I-MANAGE Stars Project
                                         Manager

  Wendy H Miller                         DOE STARS GL Team Lead

  Jeff Payne                             DOE STARS AP Team Lead/UDO Task
                                         Force

                                         

                                         
  -------------------------------------- --------------------------------

STARS General Ledger to Purchasing Module Undelivered Orders
Reconciliation

1.  Purpose and Objectives

    1.  The purpose of this document is to provide an overview of the
        reconciliation of the Production STARS General Ledger (GL)
        Module to Purchasing Module (PO) Undelivered Orders Balances.
        This reconciliation will include two comparisons. The first will
        be a GL to GL comparison of the Undelivered Order Balances. This
        will involve comparing the Undelivered Orders balances found on
        column 9 of the 2108, or the DOE Undelivered Orders Report (48x1
        SGLS) to the Uncosted balances found on the Contract Data Report
        (48x1 and 48x2 SGLS), less the prepayments (48x2 SGLS). The
        second will be a GL to PO comparison of the Undelivered Orders
        Balances. This will involve comparing the Undelivered Orders
        balances found on column 9 of the 2108, or the DOE Undelivered
        Orders balances (48x1 SGLS), plus GL Prepayments (48x2), to the
        Undelivered Orders balance in the PO Module at the distribution
        level (Quantity Ordered less the Quantity Cancelled less the
        Quantity Delivered). The GL to PO Undelivered Orders
        Reconciliation Report will provide the drilldown capability to
        the Treasury Symbol, CID, and the entire AFF. It is important
        that the reconciliation of the GL Undelivered Orders to the GL
        CDR Uncosted Balance and the GL Undelivered Orders to the PO
        Undelivered Orders Balance occur simultaneously. In addition, an
        overview of how to determine Stale Undelivered Order balances
        from the PO Module will also be provided.

    2.  The objectives of this document are:

        1.  To identify the steps executed as part of the process to
            reconcile the Undelivered Orders balances from the General
            Ledger to the Purchasing Module.

        2.  To provide a reporting tool that can be used both to
            reconcile the General Ledger to the Purchasing Module and to
            simultaneously help determine any differences between the
            Contract Data Uncosted Balances and the Undelivered Orders
            Balances in the GL. These differences can then be identified
            and corrected, and thus can help overcome the material
            weakness from last year's audit.

        3.  To provide a reporting tool that can be used to determine
            Stale Undelivered Order balances based on different award
            types (from the Purchasing Module).

        4.  To summarize the findings and propose the team's
            recommendation.

Executive Summary

3.  **Reconciliation Steps Executed for the GL to PO Undelivered Orders
    Reconciliation**

    1.  GL to Purchasing (PO) Reconciliation Steps

+-----------------------------------------------------------------------+
| **Logging into Discoverer Viewer**                                    |
+-----------------------------------------------------------------------+
| 1.  Go to the STARS HOMEPAGE                                          |
|     (http://crinfo.doe.gov/officedocs/cf40/stars/).                   |
+-----------------------------------------------------------------------+
| 2.  Click on the Reporting Tools link on the left hand side.          |
+-----------------------------------------------------------------------+
| 3.  Click on the Discoverer Viewer link in the Reporting & Query      |
|     Tools Box.                                                        |
+-----------------------------------------------------------------------+
| > **Establishing a Discoverer Connection**                            |
+-----------------------------------------------------------------------+
| 4.  If you already have a connection created (should be a link with   |
|     your last name and first initial), skip to Step13. If you do not  |
|     have a connection, click on the Create a Connection button.       |
+-----------------------------------------------------------------------+
| 5.  In the Connect to Box, select Oracle Applications.                |
+-----------------------------------------------------------------------+
| 6.  In the Name box, enter your last name, followed by your first     |
|     initial (this is not case sensitive).                             |
+-----------------------------------------------------------------------+
| 7.  In the Username box, enter your STARS production username (this   |
|     is not case sensitive).                                           |
+-----------------------------------------------------------------------+
| 8.  In the Password box, enter your STARS production password (this   |
|     is not case sensitive).                                           |
+-----------------------------------------------------------------------+
| 9.  Enter 'strs' in the Database box (this is not case sensitive).    |
+-----------------------------------------------------------------------+
| 10. Click on the Apply button.                                        |
+-----------------------------------------------------------------------+
| 11. In the Responsibility dropdown box, select the DOE (or your       |
|     office) GL Inquiry responsibility.                                |
+-----------------------------------------------------------------------+
| 12. Click on the Continue button.                                     |
+-----------------------------------------------------------------------+
| > **Connecting to Discoverer Viewer**                                 |
+-----------------------------------------------------------------------+
| > 13\. Click on your connection name.                                 |
+-----------------------------------------------------------------------+
| > 14\. Enter your password (STARS password) and click on the Go       |
| > Button.                                                             |
+-----------------------------------------------------------------------+
| > **Running the GL to PO Undelivered Orders Reconciliation Report**   |
+-----------------------------------------------------------------------+
| > 15\. Click on the Preferences Link at the very top right hand       |
| > corner of the Discoverer Page.                                      |
+-----------------------------------------------------------------------+
| > 16\. Make sure that the first check box is checked ('Warn me if     |
| > predicted time exceeds'). Uncheck the other two boxes below this    |
| > box.                                                                |
+-----------------------------------------------------------------------+
| > 17\. Click on the Apply button.                                     |
| >                                                                     |
| > **NOTE: IT IS VERY IMPORTANT TO COMPLETE THIS STEP BEFORE           |
| > CONTINUING TO STEP 18. THE COMPLETION OF STEP 16 WILL ENABLE YOU TO |
| > VIEW ALL ROWS OF DATA ON THE UNCOSTED VALIDATOR REPORT (AND WILL    |
| > NOT LIMIT YOUR VIEW TO 10,000 OR LESS ROWS OF DATA).**              |
+-----------------------------------------------------------------------+
| > 18\. In the box next to the Search All Workbooks box, type in 'GL   |
| > to PO', and click on the GO button. Scroll down until you find the  |
| > report name: 'GL to PO Undelivered Orders Reconciliation and Stale  |
| > PO UDO Balances Report'.                                            |
+-----------------------------------------------------------------------+
| > 19\. Click on the blue triangle with a plus sign next to the report |
| > name. Note: You should see two links (GL to PO UDO Recon Report and |
| > Stale PO UDO Balances) Click on the GL to PO UDO Recon Report link. |
+-----------------------------------------------------------------------+
| 20. You will be prompted to enter the following parameters: (Note:    |
|     Parameters are case sensitive. Also, you do not need to add       |
|     single quotes when typing in the parameters, because Discoverer   |
|     will automatically do this for you.)                              |
|                                                                       |
| > Select Most Current Open GL period (ex. JAN-07)                     |
| >                                                                     |
| > Select Allottee (ex. 30)                                            |
| >                                                                     |
| > Select CID (Leave this Parameter blank right now so that you can    |
| > view all CIDs for your particular allottee)                         |
| >                                                                     |
| > Click on the GO button                                              |
| >                                                                     |
| > **Note: It is important that the most current open GL period is     |
| > selected (not any closed prior GL periods) in order to obtain the   |
| > most up to date GL balances that are needed to tie to the PO        |
| > balances on the report (since the PO Module does not have a period  |
| > date cutoff).**                                                     |
+-----------------------------------------------------------------------+
| > **Exporting the Report into Excel**                                 |
+-----------------------------------------------------------------------+
| > 21\. If you receive a warning message "Continue to Run Query"?,     |
| > click on the "yes" button.                                          |
+-----------------------------------------------------------------------+
| > 22\. You will see a Query Progress screen pop up. You do not need   |
| > to click on anything (wait for the report output to display).       |
+-----------------------------------------------------------------------+
| > 23\. If the report is displaying less than 12 columns (the last     |
| > column should be PO Costs), then click on the "Rows and columns"    |
| > link.                                                               |
+-----------------------------------------------------------------------+
| > 24\. Type in 12 in the "columns" box and click on the GO button.    |
+-----------------------------------------------------------------------+
| > 25\. Click on the Export link under the Action Box in the top left  |
| > hand corner.                                                        |
+-----------------------------------------------------------------------+
| > 26\. Choose the Microsoft Excel Workbook (\*.xls) from the drop     |
| > down box.                                                           |
+-----------------------------------------------------------------------+
| > 27\. Click on the Export Button (top right hand corner)             |
+-----------------------------------------------------------------------+
| > 28\. You should now see an Export Ready Page. Click on the 'Click   |
| > to View or Save' button.                                            |
+-----------------------------------------------------------------------+
| > 29\. You will see a File Download Box. Click on the Save button.    |
+-----------------------------------------------------------------------+
| > 30\. Save the report output in the location of your choice on your  |
| > hard drive.                                                         |
+-----------------------------------------------------------------------+
| > 31\. Locate the exported GL to PO Reconciliation Report on your     |
| > hard drive and open it, or just click Open when the dialog box      |
| > appears after saving.                                               |
+-----------------------------------------------------------------------+
| > 32\. You will see a Microsoft Excel Box pop up. Click on Enable     |
| > Macros.                                                             |
+-----------------------------------------------------------------------+
| > **Review the Excel Output file in a summarized form by CID and      |
| > Fund**                                                              |
+-----------------------------------------------------------------------+
| 33. For each PO number and Fund type, determine if there is a         |
|     difference between:                                               |
|                                                                       |
|     1.  GL UDO balance and the GL CDR Uncosted balance columns (Note: |
|         There should be no differences in this column. If you see a   |
|         difference in this column, contact the help desk).            |
|                                                                       |
| > And/or                                                              |
|                                                                       |
| 2.  GL UDO balance and the PO UDO balance columns                     |
|                                                                       |
| **Note: It is very important that both these steps are complete. You  |
| cannot just reconcile the GL UDO balance and the GL CDR Uncosted      |
| balance because they are both from the same source system. There must |
| be a tiepoint to the PO Module as well since that is our method of    |
| funds control and should contain all transactions backed up by source |
| documentation, with exception to Working Capital, Payroll, Cost       |
| Accruals, and Cost Overruns ( at the AFF level).**                    |
+-----------------------------------------------------------------------+
| 34. Use Source documentation to verify whether the GL UDO/CDR         |
|     Uncosted or the PO UDO balance is correct.                        |
|                                                                       |
| > **Note: The GL UDO balance should be the driver of the              |
| > reconciliation process since it is reflected on the 2108 and        |
| > financial statements. Also, the differences described in the        |
| > Reconciliation Scenerios below are based off the Allottee, CID, and |
| > Fund levels.**                                                      |
+-----------------------------------------------------------------------+
| > **The following table is a summary of the 3 Reconciliation          |
| > scenerios:**                                                        |
+-----------------------------------------------------------------------+
| +---------------+---------------+---------------+---------------+     |
| | PO UDO        | GL UDO        | CDR UDO       | CORRECTIONS   |     |
| | BALANCE       | BALANCE       | BALANCE       |               |     |
| +---------------+---------------+---------------+---------------+     |
| | Incorrect     | Correct       | Correct       | Correct PO    |     |
| | (does not     | (matches      | (matches      | module with   |     |
| | match source  | source        | source        | obligation    |     |
| | d             | documentation | documentation | and/or        |     |
| | ocumentation) |               |               | receipt       |     |
| |               |               |               | entries       |     |
| |               |               |               | --reverse     |     |
| |               |               |               | entries in    |     |
| |               |               |               | the GL        |     |
| +---------------+---------------+---------------+---------------+     |
| | Correct       | Incorrect     | Incorrect     | Correct GL    |     |
| | (matches      | (does not     | (does not     | UDO           |     |
| | source        | match source  | match source  |               |     |
| | documentation | d             | d             | Correct CDR   |     |
| |               | ocumentation) | ocumentation) | balances      |     |
| +---------------+---------------+---------------+---------------+     |
| | Incorrect     | Incorrect     | Incorrect     | First,        |     |
| | (does not     | (does not     | (does not     | correct GL    |     |
| | match source  | match source  | match source  | UDO/CDR UDO   |     |
| | d             | d             | d             | balance       |     |
| | ocumentation) | ocumentation) | ocumentation) |               |     |
| |               |               |               | Then, correct |     |
| |               |               |               | PO module     |     |
| |               |               |               | with          |     |
| |               |               |               | obligation    |     |
| |               |               |               | and/or        |     |
| |               |               |               | receipt       |     |
| |               |               |               | entries       |     |
| |               |               |               | --reverse     |     |
| |               |               |               | entries in    |     |
| |               |               |               | the GL        |     |
| +---------------+---------------+---------------+---------------+     |
| | **\*Note: The |               |               |               |     |
| | GL UDO        |               |               |               |     |
| | Balances and  |               |               |               |     |
| | the CDR       |               |               |               |     |
| | Uncosted      |               |               |               |     |
| | Balances      |               |               |               |     |
| | should always |               |               |               |     |
| | match. If it  |               |               |               |     |
| | does not,     |               |               |               |     |
| | contact the   |               |               |               |     |
| | help desk.    |               |               |               |     |
| | Also note,    |               |               |               |     |
| | that not all  |               |               |               |     |
| | transactions  |               |               |               |     |
| | are entered   |               |               |               |     |
| | through the   |               |               |               |     |
| | PO Module.    |               |               |               |     |
| | The Working   |               |               |               |     |
| | Capital fund, |               |               |               |     |
| | Payroll, Cost |               |               |               |     |
| | Accruals, and |               |               |               |     |
| | Cost Overruns |               |               |               |     |
| | at the        |               |               |               |     |
| | detailed AFF  |               |               |               |     |
| | are not       |               |               |               |     |
| | entered in    |               |               |               |     |
| | the PO        |               |               |               |     |
| | Module.**     |               |               |               |     |
| +---------------+---------------+---------------+---------------+     |
+-----------------------------------------------------------------------+
| **Drilldown to the entire AFF for a specific CID using the GL to PO   |
| UDO Recon Report in Discoverer**                                      |
+-----------------------------------------------------------------------+
| 41\. Choose a CID from your exported excel worksheet that contains a  |
| GL UDO to CDR Uncosted balance difference and/or a GL UDO to PO UDO   |
| balance difference                                                    |
+-----------------------------------------------------------------------+
| 42\. Type in the CID in the Select CID parameter box at the top of    |
| the report and then click on the GO button.                           |
+-----------------------------------------------------------------------+
| 43\. Click on the blue triangle next to the Fund value                |
+-----------------------------------------------------------------------+
| 44\. You should see "Drill to Related" and a list of items that you   |
| can drilldown to (Treasury Symbol, Award Type, Appropriation Year,    |
| Reporting Entity, SGL, Object Class, Program, Project, WFO, Local     |
| Use, and Future). Select Appropriation Year.                          |
+-----------------------------------------------------------------------+
| 45\. If you receive a warning message "Continue to Run Query"?, click |
| on the "yes" button.                                                  |
+-----------------------------------------------------------------------+
| 46\. You will see a Query Progress screen pop up. You do not need to  |
| click on anything (wait for the report output to display).            |
+-----------------------------------------------------------------------+
| 47\. Repeat Steps 43 through 46 to drilldown to other segments of the |
| AFF.                                                                  |
+-----------------------------------------------------------------------+
| **Note: After you have completed your drilldown, save the report by   |
| clicking on the Save As link (in the Actions box which is located in  |
| the top left hand corner). You will have to save the report with      |
| another name. This will allow you to relog into Discoverer and use    |
| the same drilldown report the next time you need to research a CID    |
| (just change the CID parameter).**                                    |
|                                                                       |
| **If you need to run the report without the drilldown, just run the   |
| original GL to PO UDO Reconciliation Report.**                        |
+-----------------------------------------------------------------------+
| **Researching Null CIDs using the Drilldown function**                |
+-----------------------------------------------------------------------+
| 48. Open the Saved Drilldown Report created in steps 41-47 (see the   |
|     Note below step 47 as well).                                      |
+-----------------------------------------------------------------------+
| 49. Follow steps 41 through 47 above to drilldown to the lowest level |
|     of the AFF.                                                       |
|                                                                       |
| **Note: Enter the word Null for the CID parameter.**                  |
+-----------------------------------------------------------------------+
| 50. Log into STARS production using the DOE GL Inquiry Responsibility |
+-----------------------------------------------------------------------+
| 51. Select the DOE GL Null CID Research (CSV output) report           |
+-----------------------------------------------------------------------+
| 52. Enter SEP-04 in the Period From Parameter                         |
+-----------------------------------------------------------------------+
| 53. Enter the latest open GL period in the Period To Parameter        |
+-----------------------------------------------------------------------+
| 54. Enter the AFF information you have saved from you Discoverer      |
|     Drilldown as parameters for this report                           |
+-----------------------------------------------------------------------+
| 55. Export the CSV output into an Excel Worksheet. This report will   |
|     provide you further details of what makes up the Null CID balance |
|     (ex. Batch name, Journal Name, reference fields, etc), similar to |
|     the DOE GL Journal Details report                                 |
+-----------------------------------------------------------------------+
| 56. Back out Manual GL entries that were created without a CID and    |
|     reenter them with the CID DFF populated.                          |
+-----------------------------------------------------------------------+
| **NOTE: Funds 00911 and 00912 should have a null CID for the Credit   |
| Ob entries created in the GL. You do *NOT* want to add a CID to these |
| Credit Ob entries for both the 00911 and 00912 funds!**               |
+-----------------------------------------------------------------------+
| **Researching Stale PO Undelivered Order Balances**                   |
+-----------------------------------------------------------------------+
| 57\. Click on the Stale PO UDO Balances link in the Worksheets box    |
| (top left hand corner, under the Actions box)                         |
+-----------------------------------------------------------------------+
| 58\. Enter your Allottee in the Allottee parameter. This is a         |
| required parameter. You can enter a particular Fund, Reporting        |
| Entity, and/or Award Type in if you want to narrow your selection     |
| (these three parameters are not required to run the report). Click on |
| the GO button.                                                        |
+-----------------------------------------------------------------------+
| 59\. Follow Steps 25-32 to Export the Report                          |
+-----------------------------------------------------------------------+
| 60\. Research any CIDs that have a 'Y' (these indicate that the       |
| Purchasing Module has a stale UDO balance for this particular CID)    |
+-----------------------------------------------------------------------+

2.  Summary and Recommendation

    1.  It is recommended the plan outlined in the above sections be
        used by the Field Offices for future reconciliations of the
        General Ledger to the PO Module at each month end closing. In
        addition, the field offices should analyze the reasons why the
        GL and PO modules have variances and make the appropriate
        corrections for each reconciling item.

    2.  It is recommended almost all corrections be made through the PO
        Module with either an Obligation/Deobligation transaction and/or
        Receipt/Return Receipt transaction. Direct GL entries should
        *only* be made if during an analysis, the source documentation
        indicates that the PO Module has the correct Undelivered Order
        Balance and the GL is incorrect.

    3.  It is recommended the user focus their time and effort
        correcting GL UDO balances and PO UDO balances first (since
        these balances have a direct impact on the financial
        statements).

    4.  In Summary, here are the most common reasons for discrepancies
        between the PO and GL modules during the PO to GL Uncosted
        Reconciliation; and these may also be seen in the GL to PO UDO
        Reconciliations:

```{=html}
<!-- -->
```
1.  General Ledger entries are not posted (reports using the CID
    Balances Custom Table only picks up those entries that are in a
    posted status);

2.  Cost overages that are not recorded in the PO module (because the PO
    Module has been setup so that the quantity delivered cannot exceed
    quantity ordered) and are recorded in the GL module in the 61009900
    account. This account should be cleared out in the GL every month
    and during the year-end process;

3.  Transfers (fund 00911) and Undistributed (fund 00912) entries were
    made in the GL to create budget authority with a CID;

4.  Occasional System glitches (STARS Team will assist in determining
    the appropriate correcting entries);

5.  Conversion issue where the PO module used only the FY 2005 data base
    (prior year amounts used 2005 cumulative less current year) and the
    GL used the FY 2004 ending balances and created opening balances for
    FY 2005);

6.  DISCAS Validator Routine (data issues between the Summ-BNR data set
    and the DISCAS GL data set existing prior to conversion);

7.  Timing issues between the PO Module real time activity (in the
    Uncosted Validator Report) and the GL CID Balances which are one
    night behind (in the Uncosted Validator Report);

8.  Incorrect manual GL entries that went in to fix a PO related issue,
    that should have gone through the PO Module;

9.  Manual journal entries that have gone in the GL without a CID;

10. Corrections made to the PO Module (where the GL module was already
    correct) and those PO entries that went into the GL were not
    reversed out (so that the net impact in the GL is zero); and

11. PO in a status other than Approved (the approved status is the only
    status that will produce entries that go into the GL).

**B. Verification of Cumulative Obligations: (Procurement Records Versus
Accounting Records)**

1.  Background and Issue: Field Offices are currently in the process of
    validating cumulative obligations on all Purchase Orders that have
    UDO balances exceeding \$500K. This exercise is partly intended to
    ensure that cumulative obligations shown on the Contract Data Report
    are supported by the appropriate obligation documents (typically a
    contract modification, financial assistance award, or a memo
    obligation request.) A check for UDO "staleness" is also required.
    Guidance and deadlines (1/31/2007) pertaining to this review have
    already been issued by the Director of the Office of Finance and
    Oversight. To support ongoing validation of obligations after this
    initial exercise has been completed, the UDO Task Force discussed
    the possibility of using reports from the DOE procurement systems to
    identify those awards where cumulative obligations agree in
    Procurement systems and STARS. This proposal is based on the premise
    that separate entries into discreet systems lower the risk of
    misstatement and lack of supporting documentation.

2.  Methodology: HQ Procurement Office provided the UDO Subtask Team two
    reports: 1) active Financial Assistance Awards as of Sept 30, 2006;
    and 2) active Contract Awards as of Sept 30, 2006. These reports
    were compared to the STARS CDR Summary Report for the period
    SepADJ1-06. The STARS CDR Report was constrained to purchase orders
    with positive UDO balances. The result of this comparison was
    captured in a list indicating over 3,000 instances where STARS
    cumulative obligation amounts perfectly match the Procurement
    cumulative obligation amounts.

3.  Proposed Actions:

    a.  In the short term, we propose this list of Purchase Orders where
        no differences exist between the Procurement systems and STARS
        be used to prioritize the effort to validate STARS cumulative
        obligations. Because the items on this list should be considered
        low risk, they could either be considered to be supported or
        validated at a later date.

    b.  As a longer term project, we propose that a full reconciliation
        of cumulative obligations between Procurement records and STARS
        CDR reports be performed. This effort should be done prior to
        the advent of the new STRIPES procurement system to ease future
        STRIPES to STARS reconciliations.

    c.  It was noted that the Procurement reports and STARS records only
        share the common data element "Purchase Order Number". The
        Procurement data does not contain AFF information. Therefore,
        the comparison is the total award amount only. Procurement
        records do contain award date and completion date which could be
        used to derive "period of performance" and could help determine
        "staleness".

### C. Helpful Tips

-   The source in GL will identify the module that originated the entry.
    The different sources are:

    -   Payables -- Accounts Payable module

    -   Purchasing -- Purchase Order module

    -   Receivables -- Accounts Receivable module

    -   Manual -- Direct GL entries

    -   ADI JE Adjustments -- Direct GL entries

    -   Assets -- Fixed Assets module

    -   Budget Journal -- Direct GL entries

    -   Budgetary Transaction -- Federal Administrator Module

    -   Conversion -- Direct GL entries

    -   Cost Accrual -- Direct GL entries

    -   Cum Balances Payables -- September 2004 converted amounts

    -   Cum Balances Purchasing -- September 2004 converted amounts

    -   FDS -- Direct GL entries

    -   Field Budget -- Direct GL entries

    -   INTEGRATED CONTRACTOR -- Direct GL entries

    -   INTER-ENTITY ADJUSTMENT -- Direct GL entries

    -   Investments via ADI -- Direct GL entries

    -   MassAllocation -- Direct GL entries

    -   NON INTEGRATED CONTRACTOR -- Direct GL entries

    -   PMAs via ADI -- Direct GL entries

    -   Payroll -- Direct GL entries

    -   Recurring -- Direct GL entries

    -   Spreadsheet -- Direct GL entries

    -   WORKING CAPITAL FUND -- Direct GL entries

    -   Year End Close -- Direct GL entries

    -   Year End Close Adjustment -- Direct GL entries

    -   Year End Pre Close -- Direct GL entries

-   CID is located in different areas in GL depending on the source of
    the data:

    -   Manual (Direct GL) -- attribute 1

    -   Purchasing -- Reference 4

    -   Payables -- Reference 5

    -   Cum Balances Payables -- Reference 5

    -   Cum Balances Purchasing -- Reference 4

    -   Payables (category - Treasury payments) -- linked by invoice
        number in the TRX_NUMBER

-   STARS reports that come from the custom balancing table are updated
    each night. Examples are:

    -   Contract Data Report

    -   GL Balances with CID parameter -- detail

    -   GL to PO UDO Reconciliation Report

-   STARS reports that come directly from GL are updated when GL entries
    are posted. Examples are:

    -   DOE GL Journal Detail Report by CID

    -   DOE GL Journal Detail Report (includes a parameter to also
        select unposted records)