Question

What does the data in the ROLLOVER_AUDIT table mean?

Answer

*The possible RECORD_TYPE values are 1=Ledger (LEDGER_ID), 2=Fund (FUND_ID), 3=Purchase Order (PO_ID), 4=Line Item (LINE_ITEM_ID), 5=Copy (COPY_ID), 6=Fiscal Period (FISCAL_PERIOD_ID)
*Once the RECORD_TYPE is determined, the RECORD_ID and PARENT_ID can be found.
*For RECORD_TYPE=2 (Fund), the PARENT_ID is the LEDGER_ID,
*For RECORD_TYPE=4 (Line Item), the PARENT_ID is the PO_ID or INVOICE_ID,
*For RECORD_TYPE=5 (Copy), the PARENT_ID is the LINE_ITEM_ID.
*For RECORD_TYPE==6 (Fiscal Period) and RECORD_TYPE=3 (Purchase Order), the PARENT_ID is recorded as 0.
*The RESULT_CODEs are in ROLLOVER_RESULT_CODES look-up table.

Additional Information

Example:

RECORD_ID PARENT_ID RECORD_TYPE RESULT_CODE
------------------ ----------------- ------------------------ ------------------------
2061 2023 5 33

The above line from ROLLOVER_AUDIT indicates that the record type is copy, the COPY_ID is 2061, the LINE_ITEM_ID is 2023, and the line item did not roll due to a pending invoice.

Use the COPY_ID to look at the LINE_ITEM_COPY_STATUS table, as this the table looked at by the FPC program to determine if the line item is eligible to roll. The LINE_ITEM_STATUS is 5 (invoice pending, see the LINE_ITEM_STATUS look-up table for a full list of statuses):

SQL> select * from line_item_copy_status where copy_id=2061;

LINE_ITEM_ID LOCATION_ID COPY_ID MFHD_ID LINE_ITEM_STATUS
------------ ----------- ---------- ---------- ----------------
INVOICE_ITEM_STATUS STATUS_DA ITEM_ID
------------------- --------- ----------
2023 8 2061 57056 8
5 29-SEP-09 0

Use the LINE_ITEM_COPY_HISTORY table to see when the line item was approved as well as the INV_LINE_ITEM_ID:

SQL> select * from line_item_copy_history where copy_id=2061 order by audit_id;

COPY_ID AUDIT_ID LINE_ITEM_STATUS STATUS_DA INV_LINE_ITEM_ID
---------- ---------- ---------------- --------- ----------------
2061 5938 8 29-SEP-09 0
2061 5939 5 29-SEP-09 1056

Category: Acquisitions

• Article last edited: 10/8/2013