COPY TRANSFER DATA TO ASSET AUDIT LOG

Use this optional toolkit to copy transfer data that was manually added in earlier Costpoint versions to the Asset Audit Log, so that all the desired "change" data can be retrieved from a single source, via inquiry or report.

In Costpoint versions before 4.0, you could record change data only by using the Maintain Transfer Info and/or Create/Edit/Process Transfer Transactions screens in the Transfer Records menu.

In Costpoint Version 4.0, functionality was added to automatically trap and store change data in the Asset Audit Log table for all Asset Master data fields changed using the Asset Master Info Global Change and the Asset Master UDF Info Global Changes screens.  We also added the Asset Change History Inquiry screen, in which you can view aspects of the changed data stored in the Audit Log table for Asset Master records.

In Costpoint version 5.0, we added the following data change features:

• A change settings screen in which you can select the Asset Master fields for which change details should be written to the Asset Audit Log.

• The non-global capability to capture change data wherever changes are made in asset records: directly in the Maintain Asset Master screen or its subtasks or in the individual Asset Master maintenance screens (as well as in the Asset Master Info Global Changes and the Asset Master UDF Info Global Changes screens).

• A Print Change History report that prints aspects of the changed data stored in the Asset Audit Log for Asset Master records.

(We added similar audit trail functionality for Templates in version 5.0.)

The three copy options offered in this selection screen represent the most anticipated setup and usage scenarios. You can copy transfer data based on:

• Transfer Columns — Select this option if your "old" data is in one column and your "new" data is in a different column on the Manage Asset Transfer Information screen for a single data element.

• Transfer Dates — Select this option if both your "old" data and "new" data are in a single column on the Manage Asset Transfer Information screen for a single data element.

• Data Elements — Select this option if the field name is in one column, your "old" data is in another column, and your "new" data is in yet another column on the Manage Asset Transfer Information screen for multiple data elements.

You can copy data only from one column at a time from the FA_TRACKING table into columns in the ASSET_AUDIT_LOG table.

The required date field from the FA_TRACKING table automatically copies into the ASSET_AUDIT_LOG table during the process, regardless of the Copy Option you have selected.

Because records in FA_TRACKING will NOT be marked as having been copied, there is no safeguard against the user error of copying data more than once from the same column in FA_TRACKING. (Should this occur, however, you can run the Purge Change History process to delete the unwanted records from the ASSET_AUDIT_LOG table.)

The system will not delete the copied data from the FA_TRACKING table once the process has been run.

You must first select the appropriate fields on the Configure Asset/Template Change Settings screen.

For each database column name in the Asset Audit Log into which you plan to copy transfer tracking data, make sure that you select the corresponding check box in the Asset Audit Log column (in the Asset/Template Field/Column Names subtask of the Configure Asset/Template Change Settings screen) for each equivalent field.

Other rules from the Configure Asset/Template Change Settings screen also prevail.

The documentation for this toolkit is different from other toolkit documentation in that field descriptions are not located strictly in screen order and screen examples are illustrated for each of the three copy options.

Location

Selection Ranges

Asset/Item Numbers

Use the following fields to select the asset/item number(s) for which to copy transfer data to the Asset Audit Log.

If you enter an asset number, you must also enter a corresponding item number.

Option

Select a range option from the drop-down list. The available options are:

• All

• One

• Range

• From Beginning

• To End

Start—Asset No/Item No

Enter, or click to select, the beginning asset/item number for the range option. If you select All or From Beginning in the Option field, these fields will be inactive.

End—Asset No/Item No

Enter, or click to select the ending asset/item number for the range option. If you select All, One, or To End in the Option field, these fields will be inactive.

Options

Because you have so much flexibility when you set up and use the Transfer Records functionality, there is no way to determine all the ways in which you may have used this functionality in the past. The three copy options offered on this screen represent the most anticipated setup and usage scenarios.

Copy Transfer Data Based On

Transfer Columns

Select this option if your "old" data is in one column and your "new" data is in a different column on the Manage Asset Transfer Information screen for a single data element. In other words, you should select this option if you have designated specific columns on the Manage Asset Transfer Information screen for specific types of data AND you have designated the columns to isolate and specify the "FROM" and "TO" data separately.

Example:

Make sure you select the appropriate check box(es) in the Asset/Template Field/Column Names subtask of the Configure Asset/Template Change Settings screen BEFORE you run the toolkit. In this example, the only change data that had been recorded manually on the Manage Asset Transfer Information screen was for organization changes.

If you have not selected all the matching check boxes, the system displays a message when you try to execute the process. It will say that you have not activated all the matching Asset Audit Log check box(es) in the Field/Column Names subtask of the Configure Asset/Template Change Settings screen for the Asset Audit Log Column Name(s) you selected.

You must also make entries in the following fields before you can run this toolkit:

• Both fields in the Select Transfer Data Columns to copy FROM group box. In the example, the FROM ORG transfer data column was selected from which to copy data to the Asset Audit Log as "Old Data." The TO ORG transfer data column was selected from which to copy data to the Asset Audit Log as "New Data."

• The single field in the Select Asset Audit Log Data Column Name to Copy TO group box. In the example, the Asset Organization Audit Log Data Column Name was selected as the Field Name in the Asset Audit Log with which to link the change data.

Click when you are ready to begin the copy process.

The system will delete all data from this screen when the copy process is complete.

Once the data has been copied to the Asset Audit Log using this utility, you can perform inquiries on the data when you use the View Asset Change History screen and/or you can print the data through the Print Asset/Template Change History Report.

The ASSET_AUDIT_LOG table holds the data with a particular format.

The FA_TRACKING table, where the data from the Manage Asset Transfer Information screen is stored, holds the data corresponding to the previous example.

The system does not delete the copied data from the FA_TRACKING table when you run this toolkit.

Transfer Dates

Select this option if both your "old" data and "new" data are in a single column on the Manage Asset Transfer Information screen for a single data element. This option addresses the situation in which you have entered only NEW values on the Manage Asset Transfer Information screen each time there is a change, and, as a result, the SAME column on the Manage Transfer Information screen contains both the old and new data.

In other words, select this option if you have designated specific columns on the Manage Transfer Information screen for specific types of data AND you have entered only NEW data, as appropriate, in each column. In this circumstance, there are no FROM or TO columns and the "new" data entered in the previous row automatically acts as the "old" data in the current row.

This option reads data from two rows at a time in FA_TRACKING, reading from the earliest date/subkey row AND the next date/subkey to create a single row in the ASSET_AUDIT_LOG table. The latest FA_TRACKING.TRN_DT date from the two rows is written as ASSET_AUDIT_LOG.ENTR_DT. Data from the earliest date/subkey is written to ASSET_AUDIT_LOG.ORIG_COL_VALUE and data from the next date/subkey is written to ASSET_AUDIT_LOG.NEW_COL_VALUE.

The process then writes the next row to ASSET_AUDIT_LOG by reading the last previously read row in FA_TRACKING as the earliest date/subkey and writing the data to ASSET_AUDIT_LOG.ORIG_COL_VALUE. The next previously unread row in FA_TRACKING is read as the next date/subkey and data is written to the ASSET_AUDIT_LOG.NEW_COL_VALUE, and so on, throughout the FA_TRACKING table.

Example:

Make sure you select the appropriate check box(es) in the Asset/Template Field/Column Names subtask of the Configure Asset/Template Change Settings screen BEFORE you run the toolkit. In this example, the only change data that had been recorded manually on the Manage Asset Transfer Information screen was for organization changes.

If you have not selected all the matching check boxes, the system displays a message when you try to execute the process. It will tell you that you have not activated all the matching Asset Audit Log check box(es) in the Field/Column Names subtask of the Configure Asset/Template Change Settings screen for the Asset Audit Log Column Name(s) you selected.

You also must make entries in the following fields before you can run the process:

• The second field in the Select Transfer Data Columns to copy FROM group box. In the example, the BLDG Transfer Data Column was selected from which to copy data to the Asset Audit Log as "New Data."

• The single field in the Select Asset Audit Log Data Column Name to Copy TO group box. In the example, the Location 1 Code Audit Log Column was selected as the Field Name in the Asset Audit Log with which to link the change data.

Click when you are ready to begin the copy process.

The system will delete all data from this screen when the copy process is complete.

Once the data has been copied to the Asset Audit Log using this utility, you can perform inquiries on the data using the View Asset Change History screen and/or you can print the data using the Print Asset/Template Change History Report.

The ASSET_AUDIT_LOG table holds the data with a particular format.

The FA_TRACKING table, where the data from the Manage Asset Transfer Information screen is stored, holds the data corresponding to the previous example.

The system does not delete copied data from the FA_TRACKING table when running this toolkit.

Data Elements

You should select this option if the field name is in one column, your "old" data is in another column, and your "new" data is in yet another column on the Manage Asset Transfer Information screen. This option addresses the situation in which you have tracked changes for multiple data elements, each of which is specifically identified.

Example:

Make sure you select the appropriate check box(es) in the Asset/Template Field/Column Names subtask of the Configure Asset/Template Change Settings screen BEFORE you run the toolkit. For this example, the Employee ID, Location Group Code, Location 1 Code, and Location 2 Code check boxes were all selected in the Asset/Template Field/Column Names subtask of the Configure Asset/Template Change Settings screen.

If you have not selected all the matching check boxes, the system displays a message when you try to execute the process. It will tell you that you have not activated all the matching Asset Audit Log check box(es) in the Field/Column Names subtask of the Configure Asset/Template Change Settings screen for the Asset Audit Log Column Name(s) you selected.

You must also make entries in the following fields before running the process:

• Both fields in the Select Transfer Data Columns to copy FROM group box. In the example, the OLD DATA Transfer Data Column was selected from which to copy data to the Asset Audit Log as "Old Data." The NEW DATA Transfer Data Column was selected from which to copy data to the Asset Audit Log as "New Data."

• The single field in the Select Transfer Data to Copy Based on Mapping of Data Elements group box. In this example, the FIELD NAME Transfer Data Column was selected to indicate the Transfer Data Column containing the Transfer Data Column Name(s) as the Field Name in the Asset Audit Log with which to link the change data.

When you tab past this field, the system reads the data from the column you selected and automatically populates the first column in the table window with unique data found in the first column of the table window.

Displays rows in the table window are sorted by unique data values in alphanumeric order.

In the Asset Audit Log Column Name column in the table window, you can enter, or click to select the desired match with the appropriate English "Field Name" in the Asset Audit Log for each displayed transfer field name in the Original Transfer Data Column Name column.

Although you are not required to enter data in the Selected Data Column Name in Asset Audit Log column to run the process, you should enter the appropriate mapping data to ensure a successful data copy from this toolkit.

Click when you are ready to begin the copy process.

The system will delete all data from this screen when the copy process is complete.

Once you have copied the data to the Asset Audit Log using this utility, you can perform inquiries on the data using the View Asset Change History screen and/or you can print the data using the Print Asset/Template Change History Report.

The ASSET_AUDIT_LOG table holds the data with a particular format.

The FA_TRACKING table, where the data from the Manage Asset Transfer Information screen is stored, holds the data corresponding to the previous example.

The data copied from the FA_TRACKING table is NOT deleted when you run this toolkit.

Select Transfer Data Columns to Copy FROM

Data column to be copied to Asset Audit Log as "Old Data"

Enter, or click to select, the transfer data column to copy to Asset Audit Log as "Old Data."

Data column to be copied to Asset Audit Log as "New Data"

Enter, or click to select, the transfer data column to copy to Asset Audit Log as "New Data."

Select Asset Audit Log Data Column Name to Copy TO

Asset Audit Log Data Column Name to which Transfer Data will be copied

Enter, or click to select, the Asset Audit Log data column name to copy to.

Select Transfer Data to Copy Based on Mapping of Data Elements

Select Transfer Data Column containing Transfer Data Column Name

Enter, or click to select, the transfer data column to copy the Asset Audit Log as "New Data."

Data Elements Mapping

This table window contains the data elements mapping.

How do I populate this table window?

Original Transfer Data Column Name

This column displays the original transfer data column name based on the entered value in the Select Transfer Data Column containing Transfer Data Column Name field.

Asset Audit Log Column Name

Enter, or click to select, the Asset Audit Log column name that matches the Original Transfer Data Column Name.

Copy Transfer Data to Asset Audit Log

Click in the toolbar to initiate the copy process.

• Table Information

  Specific table results have already been detailed that are contingent upon the copy option you selected. The following table results are consistent across all copy options:

  • FA_TRACKING.TRN_DT is written to ASSET_AUDIT_LOG.ENTR_DT.

  • FA_TRACKING.ENTR_USER_ID is written to ASSET_AUDIT_LOG.ENTR_USER_ID.

  • The user ID of person running the process is written to ASSET_AUDIT_LOG.MODIFIED_BY.

  • The date/time of the process is written to ASSET_AUDIT_LOG.TIME_STAMP.

  • FA_FY_INFO and FA_PD_INFO table data is used to translate FA_TRACKING.TRN_DT into appropriate information to write to ASSET_AUDIT_LOG.FY_CD and ASSET_AUDIT_LOG.PD_NO.

  • If there is no matching information for the FA_TRACKING.TRN_DT in the FA_FY_INFO and/or FA_PD_INFO table, the following defaults are written to the ASSET_AUDIT_LOG:

  • ASSET_AUDIT_LOG.FY_CD = NULL

  • ASSET_AUDIT_LOG.PD_NO = 0

  • ASSET_AUDIT_LOG.SUB_PD_NO = 0

  • ASSET_AUDIT_LOG.JE_REQD_FL should be set to "N."