ctcLink Reference CenterRemediation FinanceCompliance Reporting Solution Design Part 2

Compliance Reporting Solution Design Part 2

Purpose: Remediation WebEx Solution Design session for Compliance Reporting Part 2

Audience: All College Staff

Compliance Reporting Meeting Information

Solution Identification (Design)

Topic: Compliance Reporting Part 2

Meeting Date/Time: Tuesday, March 21st  2:30pm to 4:00pm

Meeting Facilitator: Emmett Folk

Meeting Information: Compliance Reporting Part 2 WebEx Recording

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.

Remediation Solution Decision Making  The goal of this meeting is to make a decision on the remediation solution that will be pursued to address the identified issues and their associated remediation tickets.  For example, if there are several solutions for a reporting gap, we would decide on one of those solutions to be the final resolution of the identified issue(s) and their associated remediation tickets.

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.

  • Ok to proceed with development given by Spokane and Tacoma
  • Critical to note that these are living documents and will need to be updated as processes and the system change
  • Emmett to forward identified solutions for development
  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.

  • Ok to proceed with development given by Spokane and Tacoma
  • Critical to note that these are living documents and will need to be updated as processes and the system change
  • Emmett to forward identified solutions for development
  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.

  • Ok to proceed with development given by Spokane and Tacoma
  • Critical to note that these are living documents and will need to be updated as processes and the system change
  • Emmett to forward identified solutions for development
  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

  • Tentatively looking at 4/5 at 1pm (using the Student Financials timeslot)
    • Lisa Hjaltalin (CCS) is unavailable
    • Emmett to provide scheduling feedback to planning group and will confirm date by the end of the week
  1. 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

  • Deferred until the next Compliance Reporting sessions
    • Emmett to complete solution documentation and sample queries to be evaluated next week
  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. 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
  2. Have CEMLI being developed that State Board provided input for
    1. Emmett able to clarify with State Board who will be providing this information to SAO  
      1. Colleges will be providing SAO with the accounting data portion of the submittal (the Legacy Pro-Org Trial Balance equivalent) that will be handed by a CEMLI
      2. The State Board (or another central processing group) will be providing the Vendor List and Application Security portions of the submittal
  3. Verified that the Payroll/HCM components to SAO are being covered by a separate CEMLI in HCM
  4. Training/Documentation Development
    1. SAO Process Overview Document
      1. Include all steps related to Finance SAO reporting (including information on submittals made centrally / by State Board)
      2. Include all steps related to HCM SAO reporting (including information on submittals made centrally / by State Board)
      3. Include notes on process timing and run frequency
      4. Include links to process QRGs where applicable
    2. Finance SAO CEMLI functional/technical overview
      1. Identify CEMLI data source and extract parameters
      2. Identify CEMLI output type/options
    3. Finance SAO CEMLI QRG
      1. Include steps to execute CEMLI process
      2. Note any follow up steps needed to complete SAO reporting process
      3. Identify the process run frequency and any timing considerations

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

  1. Tacoma provided sample OMWBE reporting information
    1. Diversity Participation Report Example
    2. Diversity Participation Report Datat Criteria
  1. This reporting requirement is covered by the existing CEMLI
    1. Submits all qualifying Sub-Object transactions with Supplier data
    2. CEMLI transmits all PeopleSoft data, not Business Unit specific
    3. Follow up discussion with State Board indicated that this CEMLI would be run centrally (not at the colleges) and automatically scheduled if possible
  2. Query/Report Development
    1. College need to know what information was reported to OMWBE for a given reporting period
      1. Need to ensure that data received by college is the same data sent to OMWBE  could be different due to timing considerations / adjustments if run from a different source at a later date

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
  1. Spokane to review CEMLI Output for any missing information
    1. Assigned to: Lisa Hjaltalin /Nan Spear
    2. Feedback due on or before the next Tuesday (28th) meeting
  2. Tacoma to review CEMLI Output for any missing information
    1. Assigned to: Kirby Bedonie / Julie Carrier-Wells
      1. Feedback due on or before the next Tuesday (28th) meeting

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 Emmett Folk (ctcLink) Ok to move forward with identified solution provided by Spokane and Tacoma – Emmett to provide solution information to begin development
VPA Process Emmett Folk (ctcLink) Ok to move forward with identified solution provided by Spokane and Tacoma – Emmett to provide solution information to begin development
AFRS Reporting Emmett Folk (ctcLink) Ok to move forward with identified solution provided by Spokane and Tacoma – Emmett to provide solution information to begin development
Student Financial Aid Reporting Emmett Folk (ctcLink) Emmett to provide availability feedback to cross-pillar planning group and to confirm date for cross-pillar session to address topics by the end of the week
Payroll/Benefit Supplier Reporting Emmett Folk (ctcLink) Emmett to prepare solution documentation and sample queries for review during the next Compliance Reporting session (3/28)
CI / DoC Reporting Lisa Hjaltalin and Nan Spear (CCS) Lisa and Nan to review CI sample report with staff and provide feedback to be shared before or during the next Compliance Reporting session (3/28)
CI / DoC Reporting Kirby Bedonie and Julie Carrier-Wells (TCC) Kirby and Julie to review CI sample report with staff and provide feedback to be shared before or during the next Compliance Reporting session (3/28)

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