Related Topics

F-196 Year-End Extract for OSPI

Contents

1 Overview

2 Account Codes Structure

3 Title Page with Parameters

4 Extract Summary Report

5 Extract File Listing

6 How-to

7 Extract Fields

8 Selection Criteria

9 Tables Diagram

 

8 Selection Criteria and Crosswalk Validation

The following criteria are used by the program to select data for the F-196 report.

 

Three validations must be complete before the report can be run successfully. Each must have a value of "yes".

  1. Select the fiscal year. (FFAMFA-FIS-YTD.FFAMFA-FIS-YEAR)

  2. Select the fund(s). (Display FFAMAD-ACCT-DESC.FFAMAD-CODE and FFAMAD-ACCT-DESCS.FFAMAD-SDESC)

    1. when FFAMAD-ACCT-DESCS.FFASDC-IND = SYS-FFASAD-ACCT-DTL.FFASDC-IND,

    2. when SYS-FFASAD-ACCT-DTL.FFASAD-FUND-CODE is equal to "Yes",

    3. and FAMAD-ACCT-DESC.FFAMAD-XWALK is not equal to blank.)

  4. Generate a list of account IDs from the year-to-date account table that matches the fiscal year selected. (FFAMFA-FIS-YTD.FFAMFA-FIS-YEAR = "selected")

  5. Filter the list of account IDs by selecting the ones that match the user-selected funds, and are valid fund types for the F-196 SPI extract. (FFAMFA-FIS-YTD.FFAMAM-ACCT-ID joins with FFAMAM-ACCT-MST.FFAMAM-ACCT-ID)

    1. Identify the dimension number in the account code that represents the fund for each account type (1-5). (SYS-FFASAD-ACCT-DTL.FFASAD-DIM-NBR when SYS-FFASAD-ACCT-DTL.FFASAD-FUND-CODE = "yes")

    2. Use the dimension number representing the fund for each account type to verify the accounts in the list match the fund(s) selected, and are valid fund types for the F-196 SPI extract. (FFXWAN-XWALK-ACCT-NBR.XWALK-ACCT-DIM[SYS-FFASAD-ACCT-DTL.FFASAD-DIM-NBR] must match the selected fund(s) and FFXWAD-ACCT-DESCS.FFXWAD-CODE must equal 1, 2, 3, 4 or 9 in the first position.) This check could also occur when generating the list of funds for selection.

  7. Determine the year end values for each account ID.   

  1. Determine the account type of the account ID. Parse the fourth position of the dimension in FFAMAM-ACCT-MST.FFAMAM-EDITED-ACCT for the alpha account type, for the corresponding FFAMAM-ACCT-MST.FFAMAM-ACCT-ID.  

    1. Add the following accounts for each account ID by account type as follows:

      1. Types A and E – FFAMFA-FIS-YTD.FFAMFA-BEG-BALANCE plus FFAMFA-FIS-YTD.FFAMFA-AMT-DEBIT minus FFAMFA-FIS-YTD.FFAMFA-AMT-CREDIT.  

      2. Types L, Q and R – FFAMFA-FIS-YTD.FFAMFA-BEG-BALANCE plus FFAMFA-FIS-YTD.FFAMFA-AMT-CREDIT minus FFAMFA-FIS-YTD.FFAMFA-AMT-DEBIT.

  1. Account Code to report: Determine the dimensions of the account code, based upon fund and account type, to display in the extract file.

  1. Determine the dimension number that stores account type. (SYS-FFASAD-ACCT-DTL.FFASAD-DIM-NBR when SYS-FFASAD-ACCT-DTL.FFASAD-TYPE-FLD = "yes")

  2. Determine the dimension number that stores fund. (SYS-FFASAD-ACCT-DTL.FFASAD-DIM-NBR when SYS-FFASAD-ACCT-DTL.FFASAD-FUND-CODE = "yes")

  3. Update the list of account IDs with the OSPI account code values based upon the account’s fund and account type with information in the Washington State table:

      1. For each FFAMFA-FIS-YTD.FFAMAM-ACCT-ID display FFXWAN-XWALK-ACCT-NBR.FFXWAN-XWALK-ACCT-DIM[WA001SysStRptDim.AcctDim] when appropriate based upon records in the WA001SysStRptDim table.

        • Expense type accounts include the PPAAO – Program, Activity, Object (x5) values of the account code, in some funds.

        • Revenue type accounts include the RRPP – Revenue & Program (x4) values of the account code.

        • All others or Misc. Items accounts include the OSPI Item number (x3). The OSPI Item number resides in the WA001SPIItem table.

      3. Compare the accounts to ensure they have a related record in the crosswalk table.  If the related record is not found, the following pop-up screen appears: "One of the accounts you have selected does not have a related record in the crosswalk table. Because of this, the F-196 process is aborting." (FFXWAN-XWALK-ACCT-NBR)

  1. Negative amounts: Values for expense accounts that are for object "1", and asset accounts that represent General Ledger "241," are multiplied by a negative 1 prior to inclusion in the report and extract. This calculation is due to the fact that the 1SPI system will assign a negative value to object "1" expense account values and General Ledger "241" asset account values.

Page Updated 6/03

Top