Payments from Spreadsheet on FTP Server (Remote Cash Files)

Owned by Robert Lenk
Last updated: Feb 04, 2024 by Chris Barrett
5 min read

About Remote Cash Files

Campus Cafe may be configured to retrieve from one or two FTP servers a spreadsheet containing payments to apply to student accounts/payment plans. Provided the spreadsheet contains the necessary data, the FTP source could be anywhere, including a third party such as TFC.

Files on the FTP Server may be retrieved from within Campus Cafe and then transferred to Cash Batch for posting to student ledgers. Alternatively, the system can be configured to automatically load any outstanding files into Cash Batch once a day. Campus Cafe Support must configure the time the file is processed.

For payments to apply toward payment plans, the process assumes each student has been previously assigned a payment plan in Campus Cafe. The payment will be applied to the oldest semester's first installment due with an outstanding balance.

The process is designed to apply payments to student accounts/payment plans. It does not apply interest or payment plan fees to student ledgers. Interest and payment plan fees are also not placed into the Campus Cafe general ledger module.

Campus Cafe Configuration 

  1. Navigate to Admin > Web App

  2. Update the following parameters  

    • FTP_CASH_BATCH_BILLING_TRANS_CODE - Off-setting billing entry Campus Cafe transaction code. All transactions will go into bill batch and as cash batch. Use only if the transactions on the incoming file should also create charges.

    • FTP_CASH_BATCH_CHECK_PAY_PLAN - If set to Y then assumes all transactions on the file relate to payment plans; if set to N then assumes all transactions on the file do not relate to payment plans

    • FTP_CASH_BATCH_CHG_AMOUNT_SIGN - If payments made by students are indicated with a positive number enter N. If payments are represented with a negative number enter Y.

    • FTP_CASH_BATCH_COLUMN_MAP - Specifies which columns in the file represent which data elements (see below)

    • FTP_CASH_BATCH_EMAIL - Email address(es) to send confirmation that the file has been downloaded and is loaded into cash batch. Separate multiple emails with a comma.

    • FTP_CASH_BATCH_GL_DISTRIBUTION - The GL account as established in Campus Cafe for cash that is recorded but does not apply to student ledgers. If filled in, no transactions on the file will apply to student ledgers.

    • FTP_CASH_BATCH_LOCAL_IDENTITY_LOCATION - The path to location of the Campus Cafe public and private keys. Campus Cafe must configure the keys.

    • FTP_CASH_BATCH_PROTOCOL - Protocol for connection to FTP server. Supports SFTP or FTPS.

    • FTP_CASH_BATCH_REMOTE_DIRECTORY_PATH - The folder directory on the FTP host. Enter / for home directory. If sub directory example /payments/ Include a trailing backslash /

    • FTP_CASH_BATCH_REMOTE_HOST - Host name for the FTP server. Supports IP address or host name.

    • FTP_CASH_BATCH_REMOTE_KNOWN_HOST_KEY - For SFTP only. The remote server public key.

    • FTP_CASH_BATCH_REMOTE_PASSWORD - The FTP server password. If using public/private key there will be no password.

    • FTP_CASH_BATCH_REMOTE_PORT - The FTP server port.

    • FTP_CASH_BATCH_REMOTE_USERNAME - The FTP server username.

    • FTP_CASH_BATCH_SOURCE - TFC if using TFC, ATR if using Atrium, otherwise value of your choice.

    • FTP_CASH_BATCH_TRANSACTION_CODE - The Campus Cafe default transaction code to associate with payments if there is no other transaction code specified.

  3. If utilizing a second FTP server, complete the second set of Web App Config items. They are named the same as those in step 2 but have BATCH2 in their names.

  4. Refresh the cache by navigating to Admin > Refresh Data Cache > reload data

Column Mapping

You must tell the system which column contains which data element. In the FTP_CASH_BATCH_COLUMN_MAP value you specify in which column each data element exists. Separate each component with two pipes ||. For example COLUMN:1~SSN||~NONE||COLUMN:3||COLUMN:5

For files without column headers

  • Element One: Social Security Number or Campus Cafe ID number
    Enter Column:X if the file contains the Campus Cafe ID number. X indicates the column number that contains the number. For example, if the ID number was in column 1, enter COLUMN:1
    Enter Column:X~SSN if the file contains Social Security numbers. Numbers with and without dashes are supported. X indicates the column number that contains the number. For example, if the SSN was in column 1, enter COLUMN:1

  • Second Element: Campus Cafe Transaction Code
    Enter ~NONE if the file does not contain Campus Cafe transaction codes. The process will use the transaction code assigned in Custom Control FTP_CASH_BATCH_TRANSACTION_CODE
    Enter COLUMN:X where X indicates the column number that contains the Campus Cafe transaction code. For example, if the transaction code was in column 5, enter COLUMN:5

  • Third Element: Amount
    Enter COLUMN:X where X indicates the column number that contains the payment amount to apply to the student ledger. For example, if the amount was in column 3, enter COLUMN:3

  • Fourth Element: Exclude Specific Codes
    Identify the column that contains whether to exclude certain codes from processing. Comma separated value list of user defined transaction codes that should be excluded. For example, if your file notes that a type of transaction is an adjustment by putting in ADJ to exclude

For files with headers

For files where the top row contains headers, the columns can be in any order and the headers named anything.

In the Web App Config FTP_CASH_BATCH_COLUMN_MAP value enter the column name headers separated by pipes. Enter them in order left to right where the first column name is the Campus Cafe ID number, the second is the Campus Cafe transaction code, the third is the amount and the fourth is the type. For example, if your spreadsheet contained the columns ID_NUMBER, DOLLAR_AMT, BILLING_ITEM, RECORD_TYPE, you would enter ID_NUMBER||BILLING_ITEM||DOLLAR_AMT||RECORD_TYPE

Import File Manually to Cash Batch

Available files to import are listed on Cash Batch.

  1. Navigate to Student Financials > Cash Batch

  2. Next to Remote Cash Files choose the FTP server from where to retrieve the file (no special permission is required, but link only shows if process is configured.)

  3. Next to the file to import, click Transfer

  4. Errors such as records with SSNs or ID numbers that are missing or cannot be matched to a Campus Cafe record will be displayed

  5. All records that can be processed will be moved to Cash Batch for posting to student ledgers (A new batch will be created in Cash Batch, use the Filter on Member drop down to select the batch)

  6. Once a file is processed, the system will palace a zz_processed before the file name and and email errors the address(es) set in Web App Config FTP_CASH_BATCH_EMAIL. The file can be processed again by clicking Transfer.

Schedule Import to Cash Batch

You must contact Campus Cafe support to enable this feature.

If the process is scheduled, the system will automatically load files into Cash Batch once per day. Once a file is processed, the system will place a zz_processed before the file name and email the address(es) set in Web App Config FTP_CASH_BATCH_EMAIL.