ctcLink Reference CenterResourcesRemediation FinanceCompliance Reporting Solution Design Part 1

Compliance Reporting Solution Design Part 1

Purpose: Remediation WebEx Solution Design session for Compliance Reporting

Audience: All College Staff

Compliance Reporting Meeting Information

Solution Identification (Design)

Topic: Compliance Reporting

Meeting Date/Time:

March 14, 2017 2:30 - 4:00pm

March 15, 2017 2:30 - 4:00pm

Meeting Facilitator: Emmett Folk,

Meeting Information: Compliance Reporting WebEx Recordings

Tuesday, March 14, 2017 Part 1- Day 1

Wednesday, March 15, 2017 Part 1 - Day 2

Meeting Objective

Remediation Solution Identification  The goal of this meeting is to gain a mutual understanding of the viable options for remediating the identified.  For example, if there is a reporting gap, we would discuss any delivered pages or reports that provide the required information or if a query would need to be developed.

Key Finance Modules Involved

  • General Ledger
  • Accounts Payable

Meeting Notes and Decisions

From College Critical Issues

IPEDs Reporting

Items for this topic below are ready for decision making meeting.

  1. Training/Documentation Development
    1. IPEDs Report Generation QRG
      1. QRG/Video content that outline the steps needed to produce the IPEDs report
      2. Note input dependencies and required follow up processes, if any
    2. Functional/Technical IPEDs CEMLI Overview Document
      1. What is being used to generate the report?
        1. BI Publisher, nVision, etc.
      2. Where is the report data being pulled from?
        1. Include source tables, query logic, etc.
      3. Field mappings and value translations
        1. Where do values on the report come from
        2. Is PeopleSoft data changed for reporting purposes
        3. How Accounts are being summarized in the IPEDs output
  2. Report/Query Development
    1. IPEDs Validation Query/Report
      1. Query that provides the data that will be reported for IPEDs  a kind of test run output

VPA Process

Items for this topic below are ready for the decision making meeting.

  1. Training/Documentation Development
    1. VPA Process Overview Document
      1. Business Process Overview
        1. Needs to include how the steps that the state board performs fits into the overall process
        2. Needs to include all steps regardless of whether it’s performed by the colleges or State Board
      2. Accounting Process Overview
        1. Needs to include all steps regardless of whether it’s performed by the colleges or State Board
      3. Step-by-Step outline
        1. Include initiating event or timing
        2. Note who is responsible for each step
        3. Link each step to QRG where appropriate
        4. Note supporting queries or reports in each step where appropriate  include whether the colleges are being sent this information or whether they need to get them; critical for any items performed by the State Board
        5. Note any step dependencies
        6. End each step with expected results
    2. VPA Process QRGs
      1. Need QRG/Video content for any PeopleSoft task that’s part of the VPA process

AFRS Reporting

Items for this topic below are ready for decision making meeting.

  1. Training/Documentation Development
    1. AFRS Validation/Reconciliation Documentation
      1. Fundamental AFRS CEMLI Overview
      2. How to retrieve reported/to-be-reported AFRS data for comparison
      3. How to compare AFRS data to GL data
  2. Report/Query Development
    1. AFRS to GL Comparison
      1. Want to know what was reported for AFRS so adjustments made by the colleges that differ from the AFRS report can be identified and explained
      2. General Ledger Trial Balance  by PS Fund, PS Class
        1. Delivered PS Trial Balance Report  PDF or CSV output available
      3. AFRS Trial Balance  by AFRS Fund, AFRS Class
        1. PS Query
        2. Prompts
          1. Business Unit
          2. Fiscal Year
        3. Fields
          1. Account
          2. AFRS Fund
          3. AFRS Class
          4. Amount

Student Financial Aid Reporting

  1. Business Process Question Follow Up
    1. Jane Sackville-West (CCS) and Nor Boice (TCC) connected on their business processes for recording scholarship related receipts; both colleges are currently recording those using the Department Receipts function in Student Financials.
    2. Use of Departmental Receipts leads to difficulty in reconciling these scholarship related transactions; getting these transactions reflected on a Student’s account would eliminate the additional difficulty.
    3. Emmett to follow up with Financial Aid and Student Financials teams to see about organizing a cross-pillar session to discuss this process and our report/query gaps.
  2. Report/Query Development
    1. Reconciliation for Financial Aid and Scholarships
      1. Need to identify what has been disbursed
        1. Financial Aid indicated the query below that’s currently being used in Production
        2. CTC_FA_SF_DISCREPANCY_LIST  shows accepted & disbursed awards  applied and unapplied to Customer Accounts (Student Financials)
      2. Need to identify what has been applied
        1. Financial Aid indicated the queries below that are currently being used in Production
        2. CTC_SF_FINAID_REFUND_SUMMARY  shows summary what has been applied to charges in Student Financials, by FA Item Type
        3. CTC_SF_FINAID_REFUND_DETAILS  shows what has been applied to various item types in Customer Accounts (Student Financials)
      3. Need to identify what has made it to the GL
        1. Gap appears to be with this piece
      4. Need to identify information by type, e.g. Pell, Direct Loan, individual scholarships
    2. Reconciliation for Work Study
      1. Need to identify what was awarded and what appears in Payroll
        1. Financial Aid indicated the CEMLI below is currently being used in Production
        2. Financial Aid CEMLI R-075  Provides Payroll information that Financial Aid uses to reconcile/update their information
      2. Need to identify what had made it to the GL
        1. Gap appears to be with this piece
      3. Legacy report examples: BA1211 (FMS), PS1515 (payroll), SM9420 (fin aid)

Payroll/Benefit Supplier Reporting

  1. Report/Query Development
    1. Tracking Open Payroll Liabilities
      1. Spokane Suggestion  Create new GL Accounts for specific Payroll liabilities, based on different types of deductions (e.g. VEBA, DRS, HCA)
      2. New Account values would be incorporated into existing Accounts; existing Accounts could be used as roll-up level Accounts that the new ones would be organized under
        1. Current Liability Accounts assigned to Deduction Codes:
          1. 520000  “Employee Ins Ded Pybl, Shrt-Trm”
          2. 522000  “Grnshmnt Ded Payable-Short-Trm”
          3. 506000  “Accr Sal and Frng Ben Pybl S-T”
          4. 527000  “Other Liabilities - Short-Term”
          5. 502000  “AP Liability - Short Term”
          6. 521000  “Worker's Compensation”
    2. Payroll Vendor Payment Reconciliation
      1. Need to be able to identify instances where the payment to a Vendor doesn’t match what was deducted  need to know where the deducted lines went or didn’t go
      2. What has been deducted from an employees paycheck
        1. Legacy PS1330
        2. Info would come out of HCM, maybe use CTC_DED_DETAIL query as a baseline?
          1. Run by Company, by Pay Period End Date
      3. What vendors have been paid and how that reconciles back to college GL
        1. PS1425
        2. Info would come out of the VCHR_ACCTG_LN (Voucher Accounting Line) table in AP
          1. Want to make sure that query is not hard-coded around specific Vendors in case deduction providers change
      4. College specific draws/refunds as it relates to State Treasurer transactions
        1. CR7020 shows summary data
        2. Maybe use detail version of underlying query as a baseline?

1099 Reporting

  1. No gaps for Spokane or Tacoma

From Open Remediation Tickets

State Auditor’s Office (SAO) Reporting

  1. Have CEMLI being developed that State Board provided input for
    1. Emmett to clarify who will be providing this information to SAO  
      1. Legacy had items split between colleges and State Board
  2. There are also Payroll/HCM reporting components to the SAO submittals; Emmett to check to make sure that HCM items are included in this CEMLI if not covered by a different one
  3. Tacoma provided sample SAO reporting information
    1. Reports downloaded from FMS for 14/15 Fiscal Year for Finance
      1. Pro-Org Trial Balance
      2. Vendor List in Name Order
      3. Application Security Menu Listing by User-ID, Process-ID
    2. Files uploaded via secure SAO portal

Office of Minority & Women’s Business Enterprises (OMWBE) Reporting

  1. Tacoma provided sample OMWBE reporting information
    1. Diversity Participation Report
  1. Existing CEMLI
    1. Used “OMWBE Guidelines for CTC and Institution Reporting” document for CEMLI
    2. Submits all qualifying Sub-Object transactions with Supplier data
    3. Emmett to determine if this CEMLI is providing the information to OMWBE that they need to generate the Diversity Participation Report
  2. If current CEMLI does not provide a Diversity Participation Report solution
    1. Business Process for Supplier information
      1. Correctly identifying Suppliers as women-owned, minority-owned, etc.
    2. Query for report information
    3. Currently being tracked separately, complete provided template, and provide back to OMWBE

Correctional Industries (CI) / Department of Corrections (DoC) Reporting

  1. Existing CEMLI
    1. CEMLI document addresses the specific requirement mentioned here  includes PCard data
    2. Reports by Business Unit, by calendar date range
    3. Produces CSV files of Summary and Detail information

Use Tax Reporting

  1. Currently available queries
    1. From CEMLI
      1. CTC_SUT_DETAIL_REPORT
        1. Does not return data
        2. Looks at Voucher Accounting Line for Sales/Use Tax transactions
        3. By Business Unit, by Accounting Date range
    2. Developed Post Go-Live
      1. CTC_PCARD_TAX_NOT_PAID_DIST
      2. CTC_PCARD_TAX_NOT_PAID_TRANS
      3. CTC_PCARD_USE_TAX_DIST
      4. CTC_PCARD_USE_TAX_TRANS

SBCTC Consolidated Reporting

  1. No college-specific gaps for Spokane or Tacoma
  2. Forwarding to State Board for final gap analysis

NACUBO Reporting

  1. No college-specific gaps for Spokane or Tacoma
  2. Forwarding to State Board for final gap analysis

Meeting Action Items and Follow Up

Topic Assigned To Action / Follow Up Item Description
IPEDs Reporting Spokane and Tacoma Identified solutions are ready for decision making meeting on 3/21 – ensure appropriate decision makers attend that meeting
VPA Process Spokane and Tacoma Identified solutions are ready for decision making meeting on 3/21 – ensure appropriate decision makers attend that meeting
AFRS Reporting Spokane and Tacoma Identified solutions are ready for decision making meeting on 3/21 – ensure appropriate decision makers attend that meeting
Student Financial Aid Reporting Emmett Folk (ctcLink) Emmett to organize a separate cross-pillar session to discuss our identified remediation gaps for “Student Financial Aid Reporting”
State Auditor’s Office (SAO) Reporting Emmett Folk (ctcLink) Emmett to follow up with State Board on questions related to SAO reporting: who will be providing SAO with what portions of the report, and are the HCM components being covered through that pillar?
OMWBE Reporting Emmett Folk (ctcLink) Emmett to determine if this CEMLI is providing the information to OMWBE that they need to generate the Diversity Participation Report

Parking Lot Items

  • N/A

Related Concerns or Issues not in Critical Issues Lists or Remediation Tickets

  • N/A

Topics and Associated Remediation Tickets

Compliance Reporting

Ticket # Requirement ID Requirement Description Assoc. Topic
19342 GL4 – General “Support vendor payment advance process based on user-defined criteria (e.g., each college is authorized to draw an advance against the State Treasurer, with the amount equal to 17% of preceding biennium's monthly allocation).” VPA Process
19315 GL59 – Inquiry & Reporting “Support for IPEDs reporting in compliance with IPEDS requirements.” IPEDs Reporting
19327 GL72 – Inquiry & Reporting “Support for State of Washington auditing and reporting requirements, including but not limited to Office of Financial Management (OFM) Reporting, based on user-defined criteria (e.g., by appropriation).” SAO Reporting
19353 GL73 – Inquiry & Reporting “Support for Comprehensive Annual Financial Report (CAFR), including interfacing of required data to State of Washington's Annual Financial Reporting System (AFRS).” AFRS Reporting
19386 AP55 – Processing Withholdings “Enter and maintain withholding criteria for vendors, vendor groups, contracts, and other relevant entities.” 1099 Reporting
19452 AP56 – Processing Withholdings “Process withholding (e.g., taxes, retainage) based on withholding criteria including, but not limited to percentages, standard withholding methodologies, and contractual withholding terms.” 1099 Reporting
19370 AP57 – Processing Withholdings “Enable standard forms of withholding processing based on user-configurable criteria.” 1099 Reporting
19447 AP58 – Processing Withholdings “Calculate withholdings.” 1099 Reporting
19405 AP59 – Processing Withholdings “Perform simulated withholding calculations.” 1099 Reporting
19425 AP60 – Processing Withholdings “Post withholding transactions.” 1099 Reporting
19453 AP61 – Processing Withholdings “Generate withholding reports.” 1099 Reporting
19421 AP89 – Inquiry & Reporting “Generate and electronically transmit 1099 file, based on IRS requirements.” 1099 Reporting
19432 AP79 – Inquiry & Reporting “Generate all standard compliance documents, financial forms, and financial reporting requirements for vendors and payees.” Multiple
19419 AP87 – Inquiry & Reporting “Generate sales and use tax reports by user-defined parameters (e.g., by college, by department, by fund) and business rules (e.g., include auto-accrued use tax on out of state purchases), in compliance with State of Washington Department of Revenue requirements.” Use Tax Reporting
19552 PO104 – Inquiry & Reporting “Generate required Office of Minority and Women's Business Enterprises (OMWBE) reporting in compliance with OMWBE Guidelines for CTC & Institution Reporting (e.g., vendor certified by OMWBE).” OMWBE Reporting
19518 PO105 – Inquiry & Reporting “Generate required State reporting on purchases from Department of Corrections (DOC)/ Correctional Industries in compliance with RCW 43.19.534 and WAC 236-49-055 (e.g., How much was actually purchased? What could have been purchased?).” CI/DoC Reporting
19350 GL7 – Accounting Structure “Support an institution-wide consolidation set of books and unique sets of books across colleges and specified affiliates.” SBCTC Consolidated Reporting
19293 GL25 – Consolidation “Support consolidation mapping and mapping processes.” SBCTC Consolidated Reporting
19308 GL27 – Consolidation “Incorporate mapping to consolidation as a component of all account addition and modification processes.” SBCTC Consolidated Reporting
19325 GL28 – Consolidation “Initiate consolidation manually or automatically based on user-configurable business rules and frequencies.” SBCTC Consolidated Reporting
19354 GL26 – Consolidation “Enhanced management capability for consolidation mapping to ensure data integrity. For example, if an account value is added or modified and there is a potential mapping inconsistency, generate notifications to the relevant user.” SBCTC Consolidated Reporting
19355 GL70 – Inquiry & Reporting “Support for Financial Accounting and Reporting Manual for Higher Education guidelines published by National Association of College and University Business Officers (NACUBO).” NACUBO Reporting

0 Comments

Add your comment

E-Mail me when someone replies to this comment