Validating Tables
The Table Validation process is a powerful tool that enables you to report on and correct referential integrity errors within Student Financials tables. These errors can occur as a result of setup errors, changes to setup after data is partially processed, or your own modified programs that insert or update Student Financials tables.
Page Name |
Definition Name |
Navigation |
Usage |
---|---|---|---|
Table Validation |
RUN_CNTL_SFTBLVAL |
|
Run the Table Validation process. |
Access the Table Validation page (
).Image: Table Validation page
This example illustrates the fields and controls on the Table Validation page. You can find definitions for the fields and controls later on this page.
![Table Validation page](img/i6604892an-6d72.png)
Select Option
Field or Control |
Definition |
---|---|
Select Option |
Individual Only: Select to process only individual accounts. If you select this option, all options are available in the ID Options group box, Individual and Organization: Select to process all accounts in the system. If you select this option, the ID Option is limited to All IDs. Organization Only: Select to process only external organization accounts. If you select this option, all options are available in the ID Options group box. |
Run Option
Field or Control |
Definition |
---|---|
Update |
Select to correct data errors when they are detected. Clear this check box to produce reports only. If you select this check box, the process still produces reports. Note: When you run the Table Validation process, you have the option of selecting as many reports and updates as you want. In update mode, data dependencies and processing must occur in the order that the options appear on the page. |
ID Option
Field or Control |
Definition |
---|---|
All IDs |
Select to run the process against all accounts in the system. If Individual and Organization is selected as the Select Option, All IDs is the required ID Option. |
One ID |
Select to run the process against a single individual or corporate account. When you select this option, the ID field becomes available. |
ID Range |
Select to process a range of either individual or corporate accounts. When you select this option, the From ID and to ID fields become available. |
Detail Options
In the Detail Options group box, select the reports or updates that you want to process. If the Update run option is not selected, the process produces a report that identifies referential data integrity discrepancies, but it does not change any data. If you select the Update run option, discrepancies are corrected as indicated in each description.
Field or Control |
Definition |
---|---|
Student Account Balance |
Select to compare the account balance from the ACCOUNT_SF table with the sum of the item amounts from the ITEM_SF table. If the amounts are different, the system uses the sum of the item amounts to update the account balance. |
Corporation Account Balance |
Select to compare the account balance from the ACCOUNT_ORG_SF table with the sum of the item amounts from the ITEM_SF table. If the amounts are different, the system uses the sum of the item amounts to update the account balance. |
Item Amount |
Select to compare the item amount from the ITEM_SF table with the sum of the line amounts from the ITEM_LINE_SF table. If the amounts are different, the system uses the sum of the line amounts to update the item amount. |
Payment Amount |
Select to compare the payment amounts from the PAYMENT_TBL table with the sum of the item amounts for each payment from the ITEM_SF table. If the amounts are different, the system uses the sum of the item amounts to update the payment amount. |
Refund Amount |
Select to compare the refund amount from the REFUND_HDR table with the item amount from the ITEM_SF table. If the amounts are different, the system uses the item amount to update the refund amount. |
Applied Amount |
Select to compare the sum of the cross reference amounts from the ITEM_XREF table with the applied amount from the ITEM_SF table. If the amounts are different, the system uses the cross-reference amount to update the applied amount. |
Balance Amount |
Select to compare the sum of the cross-reference amount from the ITEM_XREF table with the net value of the item amount, minus the item balance from the ITEM_SF table. If the amounts are different, the system subtracts the sum of the cross-reference amount from the item amount and uses the result to update the item balance. |
Cross Reference CommonID Table |
Select the Cross Reference CommonID Table check box and run the process. The report performs the following cross checks:
The report lists any discrepancies found. Note: Due to its intensive table analysis of the core SF transaction tables and possible updates to SF_COMMON_ID, you should run the Cross Reference CommonID Table report in update mode only on a limited basis and only while it has exclusive access to the system. Do not run other Student Financials processes while this report is running. |
Cross Reference CommonID Field |
Select the Cross Reference CommonID Field check box and run the process. The report cross checks COMMON_ID and SA_ID_TYPE against the fields EMPLID and EXT_ORG_ID in the following tables:
For each table, the system reports errors when the following conditions occur:
Note: Even if you run the report in update mode, the system does not update the EMPLID, EXT_ORG_ID, COMMON_ID, and SA_ID_TYPE fields to prevent an inadvertent change to these ID fields. You must identify the correct values and make a manual correction in all relevant tables. |
Cross Reference Item Amount |
Select to compare the sum of the cross-reference amounts from the ITEM_XREF table with the item amount from the ITEM_SF table. If the item amount is less than this sum, the values in the ITEM_XREF table are set to zero and the errors are reported. To correct the errors, the payments must be backed out and then reapplied. Each payment can be backed out manually or by running the Applied Amount and Balance Amount reports after running this report. Exception: If the item is linked to a contract, the system does not set the values in the ITEM_XREF table to zero. To correct the errors, the contracts must be cancelled, the update process must be run again to zero the ITEM_XREF values, and the contracts must be reapplied. |
Cross Reference Effective Date |
Select to find any missing effective dates in the ITEM_XREF table to the item effective date in the ITEM_SF table. If dates are missing, the system uses the item effective date from ITEM_SF to update the effective date. |
Cross Reference Payment ID |
Select to find any missing payment ID numbers from the ITEM_XREF table with the payment ID number from the ITEM_SF table. If the numbers are different, the system uses the payment ID number from ITEM_SF to update the payment ID number in ITEM_XREF. |
Cross Reference Contract Nbr (cross reference contract number) |
Select to validate the contract number in the ITEM_XREF table. If the item number contract is populated but the contract number is blank, the system uses the contract number from the TP_STUDENT table to update the ITEM_XREF table. |
Item Due Info
Field or Control |
Definition |
---|---|
Item Due Date |
Select to find due date discrepancies in the ITEM_DUE_SF table. The program compares this due date with the due date from the ITEM_LINE_SF table. If the dates are different, the system uses the due date from ITEM_LINE_SF to calculate the appropriate value and update the ITEM_DUE_SF due date. To correct any discrepancies, this option should be run in update mode. |
Initial Run |
Select in conjunction with the Item Due Date option to delete all due date values from the ITEM_DUE_SF table before recalculating them. This option only has an effect if the report is run in update mode. |
Commit Option
Field or Control |
Definition |
---|---|
Commit At the End |
Select to commit changes only at the end of processing. This option applies only to the Item Due Date option. |
Commit Level |
Select to specify a commit level. When you select this option, you are required to specify a value in the box that appears to the right. The program commits changes to the table after each 100 IDs have been processed. Note: This option applies only to the Item Due Date option. |
You can use the Operator ID and Date Time Stamp columns to find out who last performed an update or insert operation on the following tables and when the operation was last performed
ITEM_SF
ITEM_LINE_SF
PAYMENT_TBL
ITEM_DUE_SF
ACCOUNT_SF
ACCOUNT_ORG_SF
ITEM_XREF
BI_BILL_HEADER
BI_BILLING_LINE
SF_ACCTG_LN
You will need to use a query tool to find out the Operator ID and Date Time Stamp column values.