Inbound Deposit Spreadsheet Upload (ARI010)
  • 01 Jul 2024
  • 8 Minutes to read
  • Dark
    Light
  • PDF

Inbound Deposit Spreadsheet Upload (ARI010)

  • Dark
    Light
  • PDF

Article summary

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 Spreadsheet Upload 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.


 

Your browser does not support PDF.click here to download

 

Business Process Model

This interface can be found in the following business processes:

Run Control Parameters

The interface data selection criteria includes the following:

Data FieldData 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 FieldRecord TypeData Field Description
Agency Deposit NumberDeposit ControlAgency 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 UnitDeposit ControlEquivalent to the Florida PALM AR Business Unit for the Agency.
Deposit TypeDeposit ControlIdentifies a categorization for the deposit as determined during the Configuration Build Phase.
Accounting DateDeposit ControlAccounting 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 CodeDeposit ControlIdentifier for the Bank. This is a mandatory element in the file. It should be a valid value configured in Florida PALM.
Bank AccountDeposit ControlIdentifier 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 AmountDeposit ControlThe 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 CountDeposit ControlCount of individual receipts included in the deposit as separate transactions.
Payment Sequence NumberPaymentPayment 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 IDPaymentThis 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 DatePaymentSame 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 AmountPaymentAmount of the payment. This value is determined by the Agency.
Payment Predictor FlagPaymentAllows 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 FlagPaymentNotes 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 MethodPaymentThe Payment Method Is typically defaulted by the deposit BU.
Asset ID ReferenceTBDProvide 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 IDTBDProvide 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 NumberCustomer Deposit DataPayment 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 NumberCustomer Deposit DataUsed to identify unique Item references within a given Payment.
Customer IDCustomer Deposit DataProvide 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 NumberItem Deposit DataPayment 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 NumberItem Deposit DataUsed to identify unique Item references within a given Payment.
Reference Qualifier CodeItem Deposit DataAR 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.
ItemItem Deposit DataThe 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 LineItem Deposit DataItem line must be valid line within the open receivable to which payment needs to be applied.
Pay AmountItem Deposit DataDollar amount to be posted to open receivable line.
Payment Sequence NumberPayment DistributionPayment 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 NumberPayment DistributionNumeric 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.
SpeedKeyPayment DistributionCode 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 DatePayment DistributionEquivalent 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 UnitPayment DistributionEquivalent to the Florida PALM GL Business Unit for the Agency. Must be a valid value within Florida PALM.
OrganizationPayment DistributionOrganization ChartField tracks information according to a breakdown of your organization.
AccountPayment DistributionIdentifies 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.
FundPayment DistributionSegregates and captures specific activities or classifies certain objectives in accordance with special regulations, restrictions, or limitations.
Budget EntityPayment DistributionBudget Entity ChartField represents organizations and/or functions to which appropriations are made and typically represents a program.
CategoryPayment DistributionCategory ChartField represents both appropriation categories and revenue source codes.
State ProgramPayment DistributionStores Chart of Accounts information and provides the basic structure to segregate and categorize transactional and budget data.
GrantPayment DistributionGrant ChartField is used for tracking grants funding.
ContractPayment DistributionContract ChartField captures expenditure and revenue transactions for two party agreements.
OA1Payment DistributionOA1 tracks optional organization reporting, cost pools, expenditures, revenues, or other specific use.
OA2Payment DistributionOA2 tracks optional organization reporting, cost pools, expenditures, revenues, or other specific use.
PC Business UnitPayment DistributionIdentifies 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.
ProjectPayment DistributionIf 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.
ActivityPayment DistributionIf 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 TypePayment DistributionSource types identify the purpose or origin of a transaction.
PC CategoryPayment DistributionProject Category provides more flexibility and granularity in tracking and analyzing financial activity associated with a Project.
PC SubcategoryPayment DistributionSubcategories provides more flexibility and granularity in tracking and analyzing financial activity associated with a Project.
Line DescriptionPayment DistributionAdditional description for the distribution line. This line description information is optional for a Direct Journal payment.
Foreign AmountPayment DistributionAmount associated with the payment distribution line.
Accounting DatePayment DistributionSame 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.

Version History

DateRevision Description
12/01/2023Original Version
3/29/2024Updated the description for Customer Deposit Data - ID Sequence Number and Item Deposit Data - Reference Qualifier.

Was this article helpful?

Changing your password will log you out immediately. Use the new password to log back in.
First name must have atleast 2 characters. Numbers and special characters are not allowed.
Last name must have atleast 1 characters. Numbers and special characters are not allowed.
Enter a valid email
Enter a valid password
Your profile has been successfully updated.