Inbound_Deposit_Spreadsheet_Upload

Interface Description

This interface was implemented in CMS Wave and will be updated in Financials Wave. The information in this article is the planned design for Financials Wave.

Florida PALM has identified a business need for agencies to upload deposit information through into the Accounts Receivable (AR) module. For example, if the Agency Deposit Processor receives a request to create a large volume of deposits, the deposit upload spreadsheet template is used to upload the deposit information as an alternative to manual online entry.

The deposit spreadsheet upload process is a delivered process which creates regular deposits but does not accommodate additional data elements (Asset ID Reference & Supplier ID) which will be added to the payment page and direct journal payment accounting entries. As a part of this interface, the delivered deposit upload template is customized to accommodate the additional data elements needed to create direct journal payments and AR item payments. This includes additional payment information like accounting details when the deposit is marked as direct journal payment. Also includes Asset ID Reference and Supplier ID for both direct journal and AR items payments and deposit information including Agency Deposit Number (ARE003).

Direct journals are used to record payments that are not associated with an outstanding receivable and correct deposit accounting entries or reclassify revenues within an agency.

After the deposits are loaded into PeopleSoft Accounts Receivable (AR), the accounting entries need to be validated and, in certain instances, approved by A&A when expense values are used in the deposit. The ARW002 – Direct Journal Workflow criteria will be defined with A&A. Direct journal payments created through the deposit spreadsheet upload do not go through an agency level approval process, unless ARW002 triggers workflow. If workflow is triggered, the agency will need to review the deposits by navigating online before routing the deposit to A&A through workflow. Once the deposit is completed in Florida PALM, the deposit is reconciled to the bank transactions received by Treasury. The agencies will be able to attach documents via Attachment Interface (DDID 0046).

Click below to view the interface file layout and sample data file.

Data Field	Data Field Details
Attached File (Required)	Attach the excel file from which data needs to be loaded.

Data Field

Data Field Details

Attached File (Required)

Attach the excel file from which data needs to be loaded.

File Layout

The layout of the interface is listed below.

Data Field	Record Type	Data Field Description
Agency Deposit Number	Deposit Control	Agency deposit number is used in the bank reconciliation process. This is the Florida PALM created number or bank reference number for electronic deposits and the deposit slip number for paper deposits.
Deposit Business Unit	Deposit Control	Equivalent to the Florida PALM AR Business Unit for the Agency.
Deposit Type	Deposit Control	Identifies a categorization for the deposit as determined during the Configuration Build Phase.
Accounting Date	Deposit Control	Accounting Date for the deposit. This should be the bank deposit date for receipts deposited at the bank. If this is not related to a bank deposit, then this should represent a valid date in the accounting period for when the deposit is to be posted.
Bank Code	Deposit Control	Identifier for the Bank. This is a mandatory element in the file. It should be a valid value configured in Florida PALM.
Bank Account	Deposit Control	Identifier for the Bank Account that the deposit is being made into. Must be a valid value for the agency based on the GL Business Unit associated with the External Bank Account within Florida PALM.
Control Total Amount	Deposit Control	The control amount is the total payment amount of the deposit. Users can have a zero-total deposit if the payment amounts offset each other.
Control Count	Deposit Control	Count of individual receipts included in the deposit as separate transactions.
Payment Sequence Number	Payment	Payment number in relation to the deposit. This must be a unique value within the current deposit (e.g. 1, 2, 3 etc.). Multiple payments may be associated with a deposit.
Payment ID	Payment	This will be used to store the payment identifier (e.g. check number, legacy system identifier) that can be used to identify the specific payment within the deposit.
Accounting Date	Payment	Same value as the Deposit Control Accounting Date. This value is being included here in case there is a need in the future for having accounting dates that vary by payment.
Payment Amount	Payment	Amount of the payment. This value is determined by the Agency.
Payment Predictor Flag	Payment	Allows the payment to be applied to an invoice through the Payment Predictor process. Provide ‘Y’ if the payment is for an AR item in Florida PALM, the Item ID has been provided as a reference and the payment should be applied through the Payment Predictor process. Provide ‘N’ if the payment will be applied online. Default value is ‘N’.
Direct Journal Flag	Payment	Notes that the payment is a Customer receipt or miscellaneous payment (i.e. not associated with an AR item). Provide ‘Y’ for yes or ‘N’ for no. Both the Payment Predictor Flag and the Miscellaneous Payment Flag cannot be selected for the same payment.
Payment Method	Payment	The Payment Method Is typically defaulted by the deposit BU.
Asset ID Reference	Payment	Provide an Asset ID to identify an Asset associated with the receipt, if applicable. This value should represent a value from the Asset list set up in Florida PALM.
Supplier ID	Payment	Provide a Supplier ID to identify a Supplier associated with the receipt, if applicable. This value should represent a value from the Supplier list set up in Florida PALM.
Payment Sequence Number	Customer Deposit Data	Payment number in relation to the deposit. This must be a unique value within the current deposit (e.g. 1, 2, 3 etc.). Multiple payments may be associated with a deposit.
ID Sequence Number	Customer Deposit Data	Provides the agency flexibility to break down a Payment into one or more Customer IDs and one or more Items and Item Lines. For example, the agency could receive one payment from a collection agency where multiple Customers and Items are being accounted for in the payment.
Customer ID	Customer Deposit Data	Provide a Customer ID to identify a customer associated with the receipt, if applicable. This value should represent a value from the Customer list set up in Florida PALM.
Payment Sequence Number	Item Deposit Data	Payment number in relation to the deposit. This must be a unique value within the current deposit (e.g. 1, 2, 3 etc.). Multiple payments may be associated with a deposit.
ID Sequence Number	Item Deposit Data	Used to identify unique Item references within a given Payment
Reference Qualifier Code	Item Deposit Data	AR Item Payments not related to a Grant Letter of Credit is always ‘I’. AR Item payments related to a Grant Letter of Credit can be ‘LC’, if needed for reporting purposes.
Item	Item Deposit Data	The Item ID for the payment. This is a required field if the Reference Qualifier Code is populated. Enter a reference number to identify an open receivable (Item ID) associated with the receipt. This Item ID value should represent a valid open receivable in Florida PALM. A receipt can contain one or more AR Item references, so if the receipt is for multiple Items, provide the Items as separate lines.
Item Line	Item Deposit Data	Item line must be valid line within the open receivable to which payment needs to be applied.
Pay Amount	Item Deposit Data	Dollar amount to be posted to open receivable line.
Payment Sequence Number	Payment Distribution	Payment number in relation to the deposit. This must be a unique value within the current deposit (e.g. 1, 2, 3 etc.). Multiple payments may be associated with a deposit, and multiple distribution lines may be associated with a payment.
Distribution Sequence Number	Payment Distribution	Numeric sequence number for the accounting distribution line for the payment. Start with 1 and increase for each row of accounting distribution in the payment. If there is only one accounting distribution for the payment, then provide the number 1.
SpeedKey	Payment Distribution	Code that defines ChartField distribution line information for Direct journal Payments. The full ChartField string as defined in the SpeedKey will be populated onto the Direct journal Payment during the file load process.
Budget Date	Payment Distribution	Equivalent to the Florida PALM GL Business Unit for the Agency. Must be a valid value within Florida PALM. Represents the budget date/appropriation date including the fiscal year for the budget associated with the payment. Must be a valid value within Florida PALM if populated.
GL Business Unit	Payment Distribution	Equivalent to the Florida PALM GL Business Unit for the Agency. Must be a valid value within Florida PALM.
Organization	Payment Distribution	Organization ChartField tracks information according to a breakdown of your organization.
Account	Payment Distribution	Identifies the General Ledger account value that will be used to record the transactions (i.e. ChartField value for revenue, expenditure, etc.)This value is determined by the Agency and is only for Direct Journal payments.
Fund	Payment Distribution	Segregates and captures specific activities or classifies certain objectives in accordance with special regulations, restrictions, or limitations.
Budget Entity	Payment Distribution	Budget Entity ChartField represents organizations and/or functions to which appropriations are made and typically represents a program.
Category	Payment Distribution	Category ChartField represents both appropriation categories and revenue source codes.
State Program	Payment Distribution	Stores Chart of Accounts information and provides the basic structure to segregate and categorize transactional and budget data.
Grant	Payment Distribution	Grant ChartField is used for tracking grants funding.
Contract	Payment Distribution	Contract ChartField captures expenditure and revenue transactions for two party agreements.
OA1	Payment Distribution	OA1 tracks optional organization reporting, cost pools, expenditures, revenues, or other specific use.
OA2	Payment Distribution	OA2 tracks optional organization reporting, cost pools, expenditures, revenues, or other specific use.
PC Business Unit	Payment Distribution	Identifies the Projects Business Unit if the payment is linked to a project that has been set up in Florida PALM. Must be a valid value within Florida PALM, if populated.
Project	Payment Distribution	If the payment is linked to a project which has been set up in Florida PALM, provide the corresponding value. If the Project Costing Business Unit data element is populated for a distribution line, then the spreadsheet must include a Project value within that distribution line. Must be a valid value within Florida PALM if populated. Project value is required when an FCO category is recorded.
Activity	Payment Distribution	If the payment is linked to a project which has been set up in Florida PALM, provide the corresponding Activity ID associated with the project. Only required if the PC Business Unit field has been populated. Must be a valid value within Florida PALM if populated.
PC Source Type	Payment Distribution	Source types identify the purpose or origin of a transaction.
PC Category	Payment Distribution	Project Category provides more flexibility and granularity in tracking and analyzing financial activity associated with a Project.
PC Subcategory	Payment Distribution	Subcategories provides more flexibility and granularity in tracking and analyzing financial activity associated with a Project.
Line Description	Payment Distribution	Additional description for the distribution line. This line description information is optional for a Direct Journal payment.
Foreign Amount	Payment Distribution	Amount associated with the payment distribution line.
Accounting Date	Payment Distribution	Same value as the Deposit Control Accounting Date. This value is being included here in case there is a need in the future for having accounting dates that vary by payment.

Data Field

Record Type

Data Field Description

Agency Deposit Number

Deposit Control

Agency deposit number is used in the bank reconciliation process. This is the Florida PALM created number or bank reference number for electronic deposits and the deposit slip number for paper deposits.

Deposit Business Unit

Deposit Control

Equivalent to the Florida PALM AR Business Unit for the Agency.

Deposit Type

Deposit Control

Identifies a categorization for the deposit as determined during the Configuration Build Phase.

Accounting Date

Deposit Control

Accounting Date for the deposit. This should be the bank deposit date for receipts deposited at the bank. If this is not related to a bank deposit, then this should represent a valid date in the accounting period for when the deposit is to be posted.

Bank Code

Deposit Control

Identifier for the Bank. This is a mandatory element in the file. It should be a valid value configured in Florida PALM.

Bank Account

Deposit Control

Identifier for the Bank Account that the deposit is being made into. Must be a valid value for the agency based on the GL Business Unit associated with the External Bank Account within Florida PALM.

Control Total Amount

Deposit Control

The control amount is the total payment amount of the deposit. Users can have a zero-total deposit if the payment amounts offset each other.

Control Count

Deposit Control

Count of individual receipts included in the deposit as separate transactions.

Payment Sequence Number

Payment

Payment number in relation to the deposit. This must be a unique value within the current deposit (e.g. 1, 2, 3 etc.). Multiple payments may be associated with a deposit.

Payment ID

Payment

This will be used to store the payment identifier (e.g. check number, legacy system identifier) that can be used to identify the specific payment within the deposit.

Accounting Date

Payment

Same value as the Deposit Control Accounting Date. This value is being included here in case there is a need in the future for having accounting dates that vary by payment.

Payment Amount

Payment

Amount of the payment. This value is determined by the Agency.

Payment Predictor Flag

Payment

Allows the payment to be applied to an invoice through the Payment Predictor process. Provide ‘Y’ if the payment is for an AR item in Florida PALM, the Item ID has been provided as a reference and the payment should be applied through the Payment Predictor process. Provide ‘N’ if the payment will be applied online. Default value is ‘N’.

Direct Journal Flag

Payment

Notes that the payment is a Customer receipt or miscellaneous payment (i.e. not associated with an AR item). Provide ‘Y’ for yes or ‘N’ for no. Both the Payment Predictor Flag and the Miscellaneous Payment Flag cannot be selected for the same payment.

Payment Method

Payment

The Payment Method Is typically defaulted by the deposit BU.

Asset ID Reference

Payment

Provide an Asset ID to identify an Asset associated with the receipt, if applicable. This value should represent a value from the Asset list set up in Florida PALM.

Supplier ID

Payment

Provide a Supplier ID to identify a Supplier associated with the receipt, if applicable. This value should represent a value from the Supplier list set up in Florida PALM.

Payment Sequence Number

Customer Deposit Data

Payment number in relation to the deposit. This must be a unique value within the current deposit (e.g. 1, 2, 3 etc.). Multiple payments may be associated with a deposit.

ID Sequence Number

Customer Deposit Data

Provides the agency flexibility to break down a Payment into one or more Customer IDs and one or more Items and Item Lines. For example, the agency could receive one payment from a collection agency where multiple Customers and Items are being accounted for in the payment.

Customer ID

Customer Deposit Data

Provide a Customer ID to identify a customer associated with the receipt, if applicable. This value should represent a value from the Customer list set up in Florida PALM.

Payment Sequence Number

Item Deposit Data

Payment number in relation to the deposit. This must be a unique value within the current deposit (e.g. 1, 2, 3 etc.). Multiple payments may be associated with a deposit.

ID Sequence Number

Item Deposit Data

Used to identify unique Item references within a given Payment

Reference Qualifier Code

Item Deposit Data

AR Item Payments not related to a Grant Letter of Credit is always ‘I’. AR Item payments related to a Grant Letter of Credit can be ‘LC’, if needed for reporting purposes.

Item

Item Deposit Data

The Item ID for the payment. This is a required field if the Reference Qualifier Code is populated. Enter a reference number to identify an open receivable (Item ID) associated with the receipt. This Item ID value should represent a valid open receivable in Florida PALM. A receipt can contain one or more AR Item references, so if the receipt is for multiple Items, provide the Items as separate lines.

Item Line

Item Deposit Data

Item line must be valid line within the open receivable to which payment needs to be applied.

Pay Amount

Item Deposit Data

Dollar amount to be posted to open receivable line.

Payment Sequence Number

Payment Distribution

Payment number in relation to the deposit. This must be a unique value within the current deposit (e.g. 1, 2, 3 etc.). Multiple payments may be associated with a deposit, and multiple distribution lines may be associated with a payment.

Distribution Sequence Number

Payment Distribution

Numeric sequence number for the accounting distribution line for the payment. Start with 1 and increase for each row of accounting distribution in the payment. If there is only one accounting distribution for the payment, then provide the number 1.

SpeedKey

Payment Distribution

Code that defines ChartField distribution line information for Direct journal Payments. The full ChartField string as defined in the SpeedKey will be populated onto the Direct journal Payment during the file load process.

Budget Date

Payment Distribution

Equivalent to the Florida PALM GL Business Unit for the Agency. Must be a valid value within Florida PALM.
Represents the budget date/appropriation date including the fiscal year for the budget associated with the payment. Must be a valid value within Florida PALM if populated.

GL Business Unit

Payment Distribution

Equivalent to the Florida PALM GL Business Unit for the Agency. Must be a valid value within Florida PALM.

Organization

Payment Distribution

Organization ChartField tracks information according to a breakdown of your organization.

Account

Payment Distribution

Identifies the General Ledger account value that will be used to record the transactions (i.e. ChartField value for revenue, expenditure, etc.)This value is determined by the Agency and is only for Direct Journal payments.

Fund

Payment Distribution

Segregates and captures specific activities or classifies certain objectives in accordance with special regulations, restrictions, or limitations.

Budget Entity

Payment Distribution

Budget Entity ChartField represents organizations and/or functions to which appropriations are made and typically represents a program.

Date	Revision Description
12/01/2023	Original Version
03/29/2024	Updated the description for Customer Deposit Data - ID Sequence Number and Item Deposit Data - Reference Qualifier.
06/20/2024	Administrative updates - processing rules for SpeedKey value, and associated validations for required/non required ChartFields in relation to the SpeedKey.

Inbound Deposit Spreadsheet Upload (ARI010)

Interface Description

Business Process Model

Run Control Parameters

File Layout

Version History

What's Next