ctcLink Accounting Manual | 50.70 Student Financials Account Analysis

Information similar to the student account pages is available using ctcLink query ‘QCS_SF_ITEM_LINE_LOJ_ITEM_SF’.

Student charges, payments and refunds are recorded in Item Line SF.

The query from this table includes the following information:

When a Student ID is entered in the ID field (sometimes called Common ID) student account information is displayed.

For a spreadsheet with this information see Item Line SF – Student-TPC.

Charges for this student (shaded items) were paid by a Third-Party Contract (Item Type 620000000000).  See the charges to the TPC account below.

When an External Org ID is entered in the ID field (sometimes called Common ID) and a Student ID is entered in the ‘3rd Party Contract StudentID’ field the Third-Party account transactions for that specific student are returned.

For a spreadsheet with this information see Item Line SF - Student-TPC. 

When a Student ID is entered in the ID (sometimes called Common ID) the charges and payoff by the Third-Party Contractor are clearly identifiable. On the Corporate (TPC) account, a single Item Number is used for this student for this term but the Line Sequence Number increments from 1 to 10 showing a separation similar to the charges on the Customer (student) account. The ‘Item Nbr St Chg’ field identifies the charges on the student account paid by the TPC.

When the TPC pays the charges a payment line will be included and the ‘Item Nbr St Chg’ field will be blank but the ‘3rd Party Contract Student ID’ will be populated allowing identification of the related student.

However, note the 2 ‘Item Nbr St Chg’ 000000000000048 which together total the single Item Number 000000000000048 on the student account. This was caused by a cash balance from a previous quarter applied to the balance and later removed or unapplied.

An additional example shows a Third-Party only paying for certain student charges (Item_Line_SF - Stdt-TPC Partial). 

Similar information is available using ctcLink query QCS_SF_ITEM_SF_SUMMARIZED.

A slightly summarized version of student charges, payments and refunds are recorded in a table Item SF.  The query from this table includes the following information:

When a Student ID is entered in the ID field (sometimes called Common ID) student account information is displayed. The information is now summarized without the Item Sequence Number field.  Whereas the Item Line SF query returned 21lines for this student, the Item SF query returns only 13 lines.

Charges for this student (shaded items) were paid by a Third-Party Contract (Item Type 620000000000 Lines 53 and 54).  See the charges to the TPC account below.

For a spreadsheet with this information see Item SF – Student-TPC. 

The query for the TPC account returns a single line due to the elimination of the Item Sequence Number.

Charges for this student (shaded items) were paid by a Third-Party Contract (Item Type 620000000000 Lines 53 and 54).  See the charges to the TPC account below.

For a spreadsheet with this information see Item SF – Student-TPC. 

The SF Accounting Line data provides a link between Item SF and Finance. The accounting chartstrings from the Item Types are added as well as Journal IDs from Finance. It retains much information from Item SF to allow easy identification and reconciliation between FIN and SF.

The increase in lines per student from the QCS_SF_ITEM_SF_SUMMARIZED query is caused by the addition of the accounting lines for each item type and the fund balancing lines created by E-214.

The query from this table includes the following information (the ID, Item Type and Journal ID fields all have optional criteria fields:

When a Student ID is entered in the ID field (sometimes called Common ID) student SF accounting information is displayed. The information no longer includes the Item Sequence Number field but still retains the Item Number information.  In the student account used in this example the records increase from 13 to 82 lines for the same period.

Lines 4-44

Lines 48-88

Charges for this student (shaded items) were paid by a Third-Party Contract (Item Type 620000000000 Lines 55 and 88).  See the charges to the TPC account below.

For a spreadsheet with this information see SF Accounting Line Student TPC. 

The SF Accounting Line summary by journal ID and account should reflect the account totals in the general ledger. Several journals will likely be required to identify all student account activity. In the example above, the student account activity was journalized with three (3) journal IDs (SF00171423, SF00175406, SF00189782). It is important to remember multiple student and/or external org IDs activity will be included in these journals.

To perform a simple comparison to the General Ledger by journal:

1. Run this query in CS: QCS_SF_ACCTG_LN_SUM_JRNL_ACCT

2.  Run this query in FIN: QFS_GL_ACCTG_LN_SUM_JRNL_ACCT

This query allows the selection of up to six (6) Journal IDs.

For a spreadsheet with this information see SF AcctgLine-JrnlID_Acct and GL AcctgLine-JrnlID_Acct

The SF Accounting Line summary by journal ID and account should reflect the account totals in the general ledger. Several journals will likely be required to identify all student account activity. In the example above, the student account activity was journalized with three (3) journal IDs (SF00171423, SF00175406, SF00189782).

It is important to remember multiple student and/or external org IDs activity will be included in these journals.

To perform a simple comparison to the General Ledger by journal:

1. Run this query in CS: QCS_SF_ACCTG_LN_SUM_JRNL_ACCT

2.  Run this query in FIN: QFS_GL_ACCTG_LN_SUM_JRNL_ACCT

This query allows the selection of up to six (6) Journal IDs.

For a spreadsheet with this information see SF AcctgLine-JrnlID_Acct and GL AcctgLine-JrnlID_Acct.

For a spreadsheet with the results of these queries see SFAcctgLine-JrnlID_Chrtstrng and GL AcctgLine-JrnlID_Chrtstrng.

For refund processes and a simplified accounting model see 50.30.60.3 Student Refunds (please link to section).

Payments that remain ‘unapplied’ on a student account normally would be refunded to the student unless the payment type is configured to pay prior or future charges. As of this writing, our system has 38 different Item Types. However, these can all be grouped by three (3) types:

1. Check Refunds (processed through Accounts Payable)
2. Credit Card Refunds (processed through credit card system in cashiering)
3. EFT Refunds (Bank Mobile, etc. processed through bank ACH, funds transfer accompanied by a transmittal list).

50.70.120.1 Refund Queries

To determine if a refund has been issued, type of refund, amount and date, college staff with appropriate permissions should run the QCS_SA_REFUNDS (Stdnt Refunds-Term, Date Range) query in CS. Required parameters are ‘From Date’ and ‘To Date’ with ‘Item Term’ as optional.

For an example of the results of this query, see SF Refund Queries – SF Refunds.

50.70.120.2 Check Refunds

In the previous queries staff should be able to trace activity from the student account in SF to the general ledger in FIN. Issuing a check refund requires an entry in the Accounts payable module to generate a voucher, an invoice and finally a payment via check.

To trace a student check refund from SF to FIN-AP the query QFS_GL_AP_CS_REFUND_VCHR_INV merges data from the SF Accounting Line, AP Voucher, AP Voucher Accounting Line, and AP Payment tables.

For a spreadsheet with the results of these queries see SF Refund Queries Example in CLAM 50.70.130. For a spreadsheet with the results of these queries see SF Refund Queries Example.

50.70.120.3 Credit Card Refunds

Credit card refund item types must use 1000070 – Cash in Bank as the credit.  Thus, when this item type is used, the college must process the refund through the credit card system.

For instructions on processing individual refunds (Credit Card, Bank Mobile or check) in ctcLink see ctcLink Reference Center Individual Student Refunds.

50.70.120.4 Bank Mobile/Electronic Refunds

Bank Mobile/Electronic Refunds item types also use 1000070 – Cash in Bank as the credit.  Thus, when this item type is used, the college must process the refund using the file transmission to Bank Mobile or other financial services company.  The college then must wire funds to the company.

For detailed instructions on processing Bank Mobile refunds in ctcLink see ctcLink Reference Center Bank Mobile Refunds.

50.70.120.5 Student Refunds Other Queries

Other useful queries for researching student refunds

Auditors will frequently request an accounts receivable ledger showing the receivable as of a specific date (normally fiscal year-end).

The query QCS_SF_ACCTG_ID_TOTAL_AS_OF_DT provides an output of the accounts receivable data netted by Student ID or External Org ID, Account, Account Number, and Amount from CS – Student Financials.

See example: CS_SF_ACCTG_ID_TOTAL_AS_OF_DT_Example

50.70.130.1 Student Accounts Receivable Ledger Query

50.70.130.2 Student Accounts Receivable Ledger BI Publisher Report

The BI Publisher report BCS SF RECV provides the same information as the query, but in a report format with additional subtotals by ID Type and account. See BCS SF RECV Report Example. 

To run the report for a district or college:

1. Navigate to Reporting Tools > BI Publisher > Query Report Viewer
2. Enter the name of the report, BCS_SF_RECV, and click the Search button
3. Click on the “View Report” hyperlink found to the left of the report name in the Report Definition grid.
4. Enter the desired prompt values and then click the OK button.

Summary Page

Header

• Student Receivables Report
• Summary by Business Unit
• Report Run Date/Time: Month/Day/Year/Time
• Receivables As of Date: Month/Day/Year*

*This is the date entered in the parameter field in the query.  The results of the query should include ALL accounts receivable transaction amounts up to and including this date.

Columns

Detail Pages

Header

• Student Receivables Report
• Detail by Business Unit
• Report Run Date/Time: Month/Day/Year/Time
• WAnnn – College Name
• Receivables As off Date: Month/Day/Year*

*This is the date entered in the parameter field in the query.  The results of the query should include ALL accounts receivable transaction amounts up to and including this date.)

Columns