This chapter provides an overview of electronic payment processing and discusses how to:
Receive lockbox deposits.
Use electronic banking to process payments.
Use electronic data interchange (EDI) and split stream processing.
Receive cash drawer payments.
Receive and edit spreadsheet payments.
(JPN) Receive electronic funds transfer (EFT) payments.
Check electronic payment errors.
Correct errors.
See Also
Developing Interfaces for Electronic Payments
The Payment Loader Application Engine process (AR_PAYLOAD) processes all payments that are received through an electronic payment process. Payment Loader processes the following types of electronic payments:
Lockbox
EDI
Bank statements
EFT files
Excel Spreadsheet
Payment Loader also processes payments entered using the Cash Drawer Receipts feature.
This diagram illustrates how the process works:
Entering payments electronically using Payment Loader
The Payment Loader process performs the following tasks:
Edits data for internal integrity.
Assigns defaults and deposit IDs.
Note. The Load Cash Drawer Receipts Application Engine process (CDR_LOADPMT) assigns the deposit ID to payments that you enter using the Cash Drawer Receipt feature.
Assigns document sequence numbers if you enabled document sequencing at the installation level and the general ledger (GL) business unit level.
Creates cash control accounting entries based on settings at the bank account and business unit options.
Matches split stream remittance advices and their cash, depending on the request parameters that you specify for split stream processing.
Assigns the user ID of the individual that created the run control for the Payment Loader process to the Deposit Control record (DEPOSIT_CONTROL) in the User ID (ORPRID) and Assigned User ID (ASSN_OPRID) fields for each electronic deposit.
Note. If another user accesses and modifies the deposit in the Regular Deposit component (PAYMENT_ENTRY), the system automatically
changes the assigned user ID to the individual who modified the deposit. When you apply the payment, the system updates the
Applied User ID (APPLIED_OPRID) field on the Payment Record (PAYMENT) with the user ID of the person who applied the payment.
The values in the User ID and Assigned User ID fields on the Deposit Control record remain the same. You must run a query
to see the values in the APPLIED_OPRID field.
The process processes all payments that are received through an electronic data interface. It moves the payment data from the staging tables to these payment application tables:
PS_DEPOSIT_CONTROL
PS_PAYMENT
PS_PAYMENT_ID_ITEM
PS_PAYMENT_ID_CUST
PS_PAY_MISC_DST
You can load EDI transactions or run the Lockbox SQR process (AR25001) many times a day to load multiple transmissions into the staging tables. Then at the end of the day, run the Payment Loader process once to load the data into the application tables and process all the received payments.
Before you perform electronic payment processing, you must:
Assign a default entry type, entry reason, and system function to adjustment reasons for each setID on the Receivables Definition - Bank/Payment Options page.
If you use cash control accounting, define your cash control method on the Receivables Definition - Accounting Options 2 page.
Define the bank accounts where you will deposit the payments in the External Accounts component (BANK_EXTERNAL).
Assign a business unit to the bank accounts from which you receive deposits on the External Accounts - Account Information page.
Define group types for payments on the Group Type page.
Define deposit types on the Deposit Type page.
Set up qualifiers for payment reference information on the Reference Qualifier page.
Set up adjustment reason codes.
See Also
Defining Business Unit Defaults for Individual Business Units
Setting Up Group Types and Group Origins
Defining Additional Processing Options
Defining External Account Information
This section provides an overview of lockbox processing and the lockbox process flow and discusses how to:
Run the Lockbox SQR process (AR25001).
Run the Payment Loader process.
Review lockbox deposit control information.
Lockbox deposits automatically update deposit and payment information from a bank or payment collection system. Each lockbox file can have multiple lockboxes. Each lockbox can have multiple deposits with multiple payments. Each deposit is identified by the bank and bank account it came from. Each payment can be in a different currency.
The Lockbox process accepts deposits with these payment methods: CHK (check), EFT (electronic file transfer), and GE (giro - EFT). If an invalid value is found in the lockbox file, the process assigns a default payment method as follows:
Uses the payment method assigned to the deposit type that shares the same setID as the deposit business unit.
Uses the payment method specified on the Receivables Options - Payment Options page for the deposit business unit.
Uses check as the payment method for the deposit.
We provide you with a record layout for lockbox transmissions that mirrors the online deposit entry pages and is ANSI X12-compatible. We designed the interface guidelines for recording deposit and payment information to provide you with all of the information that you currently receive from your lockbox.
See Also
Receiving Information from a Lockbox
Perform these steps to receive electronic payments:
Run the Lockbox process to load data from the flat file into the staging tables.
Run the Payment Loader process to move the data from the staging tables into the payment application tables.
Note. You can combine the Lockbox and Payment Loader processes in a PeopleSoft Enterprise Process Scheduler job and run them together.
View the Application Engine error messages in the Process Monitor.
The Payment Loader process compares the control totals and counts with the calculated totals at all levels (deposit, lockbox, and file). If they do not match, the process issues an error message. Batch level errors have an Out of Balance status.
Review the received control totals on the Lockbox Run Information page.
Check for errors at the deposit level on the All Deposits or Incomplete Deposits pages.
Correct errors on the deposit and payment entry pages and on the Payment Interface Duplicates page.
If you use cash control accounting and there is a duplicate or out of balance deposit, run the Cash Control Application Engine process (AR_CASHCNTL) to create the cash control accounting entries after correcting the errors.
See Also
Creating Cash Control Accounting Entries
Page Name |
Object Name |
Navigation |
Usage |
LOCKBOX_REQUEST |
Accounts Receivable, Payments, Electronic Payments, Retrieve Lockbox Files. Lockbox |
Run the Lockbox SQR process that imports lockbox files to the staging tables. |
|
Payment Interface |
PAYLOAD_REQUEST |
Accounts Receivable, Payments, Electronic Payments, Process Payment Interface, Payment Interface |
Run the Payment Loader process for lockbox deposits. |
LB_CONTROL_AMTS |
Accounts Receivable, Payments, Electronic Payments, Review Deposit Information, Lockbox Run Information |
View the control totals for the lockbox deposit. This page compares the payment count and amount with the lockbox totals. |
Access the Lockbox page.
Name of Lockbox File |
Enter the name of the file that contains deposits from one or more lockboxes. This value can be up to 80 characters long. Do not specify the file path. |
Note. If the file is on the client, you must run the process on the client. If the file is on the server, you must run it on the server.
Access the Payment Interface page.
Select the Lockbox check box.
Access the Lockbox Run Information page.
Lockbox ID |
Displays the ID that was processed. |
Transmission Date/Time |
Indicates the time that the file was transmitted. |
Processed Date/Time |
Indicates the time that the Payment Loader process processed the lockbox. |
Control Information
Displays the control information from the lockbox, the calculated results and the difference for the deposit count, payment count, and payment amount. The difference should be zero. If not, errors exist in the lockbox.
This section provides an overview of electronic banking and discusses how to:
Load unreconciled payments for individual statements.
Load unreconciled payments for all statements.
Receivable provides support for electronic banking. The following diagram illustrates how the process works:
Electronic banking process flow
Process Flow for Electronic Banking
To process bank statement payments:
Run the Import Bank Statement Application Engine process (BSP_IMPORT) to import the electronic statements into the staging tables and to load the bank statement data from the staging tables into the Bank Statement tables.
Run the Bank Reconciliation Application Engine process (FSPRECON) on the AutoRecon Manager page.
You must reconcile the bank statement at the payment level and not at the deposit level by assigning the BNK_RCN_PAYMENT reconciliation rule to the bank account.
You can also manually reconcile bank statements.
The process verifies whether the receipts on the bank statement with a TRANSACTION_CODE of C exist as payments in the PS_PAYMENT table. The process marks receipts that are found on the PS_PAYMENT table as reconciled. The process marks those not found as unreconciled and gives the receipts an NTF (not found in system) status.
Note. Documentation about receiving bank statements, the Transaction Loader process, and the Automatic Reconciliation process is in the PeopleSoft Enterprise Bank Setup and Processing 9.0 PeopleBook.
Load any unreconciled payments that have the NTF status into the payment tables and create cash control accounting entries for the payments if you selected the appropriate settings at the bank account and business unit options.
Use one of these options to load the data:
Run the Bank Statement Processing Application Engine process (AR_BNKSTMT) to load specific bank statement data.
Run the Payment Loader process to load data for all bank statements.
The Bank Statement Processing process handles only the payments that are processed by the Bank Reconciliation process. It does not evaluate unprocessed payments in the bank statement tables.
If you use the Bank Reconciliation cash control accounting method that records the debit to cash when the payment or trade receipt is reconciled on the bank statement, you must reconcile your bank statements. With the Bank Reconciliation cash control method, the system debits cash only when a deposit is reconciled to payments that were previously recorded in PeopleSoft Receivables. The debit to cash is the reconciled deposit amount.
You must run the Bank Statement Processing process twice to generate the cash control entries if you use the Bank Reconciliation cash control accounting method. In the first run, you load the unreconciled deposits into PeopleSoft Receivables as new payments. The second time that you run the Bank Statement Processing process for a bank you generate the cash control entries. Another option is to use the Cash Control process after loading the bank statement into PeopleSoft Receivables to generate the accounting entries.
Note. You can combine the Reconciliation process and the Bank Statement Processing process in one Process Scheduler job. However, the combined Process Scheduler job can be run only from the Reconciliation page.
If you use cash control accounting and you encounter errors, fix the problem and then run the Cash Control process to create the cash control accounting entries.
View the Application Engine error messages in the Process Monitor for the Payment Loader process.
See Also
Creating Cash Control Accounting Entries
Receiving and Updating Bank Statements
Before you use electronic banking to process payments, you must set up the statement codes to determine the payment method for each bank transaction line. If a statement code is not matched to a payment method, the system assigns CHK (check) as the payment method.
See Also
Setting Up the Bank Statement Import Process
Page Name |
Object Name |
Navigation |
Usage |
BNKSTMT_REQUEST |
Accounts Receivable, Payments, Electronic Payments, Load Bank Statements, Bank Statement Interface |
Load unreconciled payments for specific statements into PeopleSoft Receivables by running the Bank Statement Processing process. |
|
Payment Interface |
PAYLOAD_REQUEST |
Accounts Receivable, Payments, Electronic Payments, Process Payment Interface, Payment Interface |
Load unreconciled payments for all statements into PeopleSoft Receivables by running the Payment Loader process. |
Access the Bank Statement Interface page.
Bank ID and Bank Account Number |
Enter the bank ID and account number into which the deposits were made. |
Statement ID |
Specify the bank's statement identification number to identify which statement to process. |
Access the Payment Interface page.
Select the Bank Statement check box.
This section provides overviews of EDI processing, split stream processing, and the EDI payment process flow, lists prerequisites, and discusses how to:
Load business documents into staging tables.
Confirm that data loaded successfully.
Run the Payment Loader process.
Delete unmatched customer or item remittances.
PeopleSoft Receivables provides the ability to receive a payment (cash and remittance advice combined) or just the remittance (remittance information without cash) in an EDI transmission.
When you receive the remittance advice and the cash information at different times and through different channels, you can use a variety of methods to match them. This helps you identify and apply the payments.
PeopleSoft Receivables supports both European and U.S. EDI formats. The European EDI standard is EDIFACT, and the supported format is CREEXT. The U.S. standard is ANSI-X12; its supported format is 820. In both formats:
One transmission can contain a payment (cash and remittance information combined) or just cash.
One transmission can contain only a remittance (the corresponding cash information to be transmitted separately).
This scenario introduces the need for split stream processing.
Split stream processing matches and unites the two parts of a payment, the cash information and the remittance advice, when they are received at different times and possibly from different data sources. EDI, lockbox, and bank statement processing support the transmission of cash by itself. Only EDI processing and the Excel Payment Upload process support the transmission of a remittance advice by itself or cash and remittance.
Note. The Payment Loader process matches remittance information in the staging tables with cash information that is in either the staging tables or the application tables. You can use the Remittance Delete pages to find and delete unmatched remittances in the staging tables.
Split Stream Examples
Typically both the cash information and the remittance advice are received through EDI, as shown in the following example:
Example of a complete EDI payment
In this example, the customer sends you two EDI transmissions. The remittance advice is sent directly to you. The cash information is sent to the customer's bank, then to your bank, and then to you. As a result, you receive two separate EDI transmissions, one with cash and one with remittance information. You use split stream processing to match them and form the complete payment (linked cash and remittance information).
The common denominator for all methods of receiving split stream data is that the remittance information is received by EDI; only the source of the cash information varies. The method that customers use to send the cash to their bank or to your bank is irrelevant to split stream processing. Your only concern is how your bank transmits the cash information to you: as an EDI transmission, in a lockbox file, or, as in the following example, as a receipt on a bank statement:
The bank sends a bank statement that details cash information
The following example shows transmission of cash information by way of a lockbox:
The bank sends a lockbox file that provides the cash information
U.S. and European business practices differ. In the United States, you usually receive payment in a lockbox file, but you are unlikely to receive payments from bank statements. In Europe, you probably do not use a lockbox, but you probably receive payments from bank statements.
To receive payments and remittance information in an EDI file:
By using a third-party translator, translate the file into a PeopleSoft business document format.
Publish the data in the business document in the Application Messaging queue.
The subscriber program loads the data into data tables.
Confirm that the data loaded.
Run the Payment Loader process to move the data from the staging tables into the payment application tables.
View PeopleSoft Enterprise Application Engine messages.
Check for errors at the deposit level on the All Deposits or Incomplete Deposits page.
Correct errors in the deposit and payment entry pages and the Payment Interface Duplicates component (ERROR_CORRECTION).
If you use cash control accounting and you have a duplicate out-of-balance deposit, run the Cash Control process to create the cash control accounting entries.
If you receive a business unit for a deposit in the EDI file, the system assigns that business unit to the deposit. Otherwise, it uses the business unit that you assigned to the bank account on the External Accounts - Account Information page. If both business units are missing, the system uses the business unit that is assigned to the operator that runs the Payment Loader process.
Use the PeopleSoft Enterprise Integration Broker to set up the EDI interface.
See Also
Enterprise PeopleTools PeopleBook: PeopleSoft Integration Broker
Enterprise PeopleTools PeopleBook: PeopleSoft Integration Tools and Utilities
Page Name |
Object Name |
Navigation |
Usage |
EO_FILE_INBOUND |
Enterprise Components, Integration Definitions, Inbound File Rule, File Inbound |
Define the path, name of the flat file, and file layout for the inbound bank file. |
|
EO_FILETOMSG |
Enterprise Components, Integration Definitions, Initiate Processes, Inbound File Publish, Inbound File |
Load banking data from a flat file into the staging tables using the EOP_PUBLISHF Application Engine process. You can also use an external system to publish data directly in the Application Messaging queue. |
|
EC_BUSDOC_02 |
PeopleTools, EDI Manager, View EDI Audit Trail, Business Document Summary, EC Business Doc Links |
Verify that data was loaded into the EC tables at the subscribing bank. |
|
EC_BUSDOC_01 |
PeopleTools, EDI Manager, View EDI Audit Trail, Business Document Summary, Summary |
Confirm that the banking data loaded successfully. |
|
PAYLOAD_REQUEST |
Accounts Receivable, Payments, Electronic Payments, Process Payment Interface, Payment Interface |
Define the run parameters for the Payment Loader process and run the process. |
|
MATCH_ERROR_CU |
Accounts Receivable, Payments, Electronic Payments, Delete Remittance, Customer |
Delete unmatched customer remittances in the staging tables. |
|
MATCH_ERROR |
Accounts Receivable, Payments, Electronic Payments, Delete Remittance, Item |
Delete unmatched item remittances in the staging tables. |
To set up and use the EDI process:
Use the File Inbound page to set up the file identifier to point to a file.
File Identifier |
Displays an identifier pointing to the file. |
Inbound File |
Enter the path and file name that you want for the inbound flat file when you import into the PeopleSoft Receivables system. |
Index Flag |
Select only if the inbound file is an index file. |
File Layout ID |
Enter an ID that identifies the file layout. |
LUW Size, Program Name, and Section |
Leave blank. |
Create Message Header and Create Message Trailer |
Clear these check boxes. |
Definition Name |
Change the value to PAYMENT_LOAD. |
Message Name |
Change the value to PAYMENT_LOAD. |
Enter run parameters on the Inbound File page.
File Identifier |
Enter the file identifier that you defined on the File Inbound page. |
Index Flag |
Select only if the inbound file is an index file. |
Click Run to run the EOP_PUBLISHF process, which loads data from the file and publishes the data as application messages.
Select the following process name on the Process Scheduler Request page: EOP_PUBLISHF.
Use the Process Monitor to verify that the process completed successfully.
Verify that the subscriber processed the message and loaded the data into EC tables on the EC Business Doc Links page.
The value for the EC Completion Status field should be Done.
Status |
Verify that the status is Loaded. |
See Also
Enterprise PeopleTools PeopleBook: Supported Integration Technologies
Access the Payment Interface page.
Access the Remittance Delete - Customer page or the Remittance Delete - Item page.
Remittance Status |
Select the remittance status to search for and display in the list. Values are: Unmatched: Displays unmatched remittances in the file. This value is available only if unmatched remittances exist in the file. Not Processed: Displays unprocessed remittances. This value is available only if remittances in the file were not processed. |
Delete |
To remove the unmatched remittances from the staging tables, select the Delete check box next to each remittance that you want to delete and click the Delete button. |
If the cash information precedes the remittance information and you have already run the Payment Loader process for the cash information, the cash information is already loaded into the payment application table. Thus, the applicable remittance information does not match the cash information. Instead, the applicable remittance information remains in the staging tables as unmatched unless you run the option that matches the payment that you already loaded into the payment application table. You can manually correct this by deleting the unmatched remittance information on the Regular Deposits - Totals page. Then enter the remittance information with its associated payment in the reference information fields on the Regular Deposits - Payments page.
See Also
Entering Regular Deposit Totals
Entering Regular Deposit Payment Information
This section provides an overview of cash drawer receipt processing and discusses how to update payment application tables.
PeopleSoft enables you to enter an order and the payment for the order at the same time for counter sales using the Cash Drawer Receipts feature. To load the cash drawer payments into PeopleSoft Receivables, run the Load Cash Drawer Receipts process to update the payment staging tables, and run the Payment Loader process to load the data in the payment staging tables into the payment application tables.
Customers can pay for an order with multiple payment methods, for example cash, check, and credit card—each as a separate payment. Each payment that the Load Cash Drawer Receipts process interfaces to PeopleSoft Receivables should have the order number and possibly other reference information to enable you to apply the payment to the item associated with the order.
The cash drawer accepts these payment methods: gift vouchers, cash, checks, and credit, debit, or procurement card authorizations. However, the Load Cash Drawer Receipts process does not interface gift voucher information to PeopleSoft Receivables. When the Load Cash Drawer Receipts process sends information for payments made by credit, debit, or procurement card authorizations, the payment method is EFT. Also, the payment method for the items paid for with these authorizations is EFT. The reason that these items have an EFT payment method is to differentiate these items from items with a credit card payment where you run the Receivables Credit Card process (AR_CRCARD) to create the payments and a payment worksheet in PeopleSoft Receivables. The payments can be either full payments or deposits for an order.
The Load Cash Drawer Receipts process populates the Payment ID field with different values depending on the payment method as shown in this table:
Payment Method |
Payment ID |
Cash |
Cash drawer receipt number |
EFT (debit, credit, or procurement card) |
Authorization code |
Check |
Check number |
The cash drawer setup defines which bank and bank account to use for payments that you enter in the cash drawer. The setup also defines which receivables business unit is associated with each cash drawer. The system assigns the next available deposit ID to the cash drawer transaction for the deposit business unit. This table shows how the Load Cash Drawer Receipts process maps the fields on the cash drawer transaction to the fields in the payment staging tables:
Cash Drawer Receipt Fields |
Payment Staging Table Fields |
Deposit Business Unit |
LOCKBOX_ID |
Deposit ID |
LOCKBOX_BATCH_ID |
Payment Sequence Number |
PAYMENT_SEQ_NUM |
The system creates a separate deposit for all payments with the same payment method. For example, it creates three deposits if applicable: one for cash payments, one for EFT payments, and one for checks.
Note. The Payment Loader process only processes payments with a cash payment method if they originate in the cash drawer receipts interface.
After you load the cash drawer receipts into the payment application tables, use Payment Predictor to apply the payments to the items associated with the orders. We recommend that you interface the items from PeopleSoft Billing into PeopleSoft Receivables and post them before running the Payment Predictor process.
See Also
Item-Level Adjustments and Reference Values
Setting Up and Maintaining a Cash Drawer
Page Name |
Object Name |
Navigation |
Usage |
Payment Interface |
PAYLOAD_REQUEST |
Accounts Receivable, Payments, Electronic Payments, Process Payment Interface, Payment Interface |
Define run parameters for the Payment Loader process to load cash drawer payments into the payment application tables. |
Access the Payment Interface page.
Select the Cash Drawer Receipts check box.
This section provides an overview of spreadsheet payments, lists prerequisites, and discusses how to:
Set up spreadsheet payments.
Enter spreadsheet payments.
Edit and process spreadsheet payments.
Run the Payment Loader process
Review errors in spreadsheet payments.
Correct errors in spreadsheet payments.
Correct errors in spreadsheet payment references
The Spreadsheet Payment workbook enables you to enter payments offline using Microsoft Excel and then import the payments into your PeopleSoft Receivables system. Once you import the spreadsheet payments, and they go through the edit process, the Payment Loader Application Engine process loads the payment data into the payment application tables.
Note. The spreadsheet payment upload process supports MicroSoft Excel input formats. If you use a non-Excel spreadsheet, you must convert the data to an Excel format before importing.
The ExcelUploadforDeposits.xls workbook is the Excel data input tool . You use it to prepare and enter payments and import them into your PeopleSoft database.
Two worksheets make up the ExcelUploadforDeposits.xls workbook, the Template worksheet and the Data Sheet worksheet. The Template worksheet contains all of the available fields that you can configure for data entry. You enter the transaction data on the Data Sheet worksheet. You can create multiple versions of the Excel workbook, each with different templates if required.
The Spreadsheet Payment workbook supports check, EFT or EFT Giro payment methods.
Spreadsheet Payment Upload Process Flow
To create spreadsheet payments:
Enter the payment data into the spreadsheet.
Enter data into all of the required fields for a PeopleSoft Receivables payment.
Generate XML and post the file from the spreadsheet payment options.
A Visual Basic (VB) macro in the Spreadsheet Payment workbook converts the spreadsheet data into an XML format that the PeopleSoft Integration Broker reads and then posts to a URL available to PeopleSoft systems.
Integration Broker processes the incoming XML data and transfers it to the Excel Payment Upload tables on the application server.
The Excel Payment Upload process (AR_EDIT_UPLOADED_PAY_INFO) validates the data in the Excel Payment Upload tables. Data with errors causes the rejection of the entire payment group. You must correct any errors before the data can be processed successfully. We recommend that you correct the errors online through the Excel Upload Error Correction page. However, you can correct the errors in the Spreadsheet Payment workbook and then regenerate the XML file and post the data again.
The Excel Upload Payment process copies the validated payment data into the payment staging tables.
Run the Payment Loader process to select the payments based on the process run control parameters and build the PeopleSoft Receivables deposit and payment groups.
All payments in the Excel Payment Upload tables are assigned the value XEL in the DATA_SOURCE field. The Payment Loader process uses the XEL data source value to recognize payments created through the spreadsheet payment upload process.
The following graphic illustrates the flow for processing spreadsheet payments.
Excel Payment Upload process flow
Spreadsheet Payment Upload Data Fields
You can configure the ExcelUploadforDeposits.xls workbook for the appropriate data entry required. However, certain fields must contain data in order to properly convert the data into an XML file.
The ExcelUploadforDeposits.xls workbook enables you to enter data into fields that are available in the regular deposit entry process. The fields available in the ExcelUploadforDeposits.xls workbook are:
Record Type |
Field |
Required? |
Notes |
Record Type 000 Deposit Information |
Process Flag |
N |
This field exists on the Data Sheet worksheet. Valid values are Yand N. This field is only used by the Visual Basic macros in the Spreadsheet Payment workbook. This row of data is only processed if the Process Flag field value is Y. After processing, the field value is set to N. To reprocess this row, you must change the value to Y |
|
Remit Only |
N |
Valid values are Yand N. Y indicates that only the remit data will be processed and added to a payment already in the system. The payment is identified in record type 001 of the spreadsheet. |
|
Deposit Business Unit |
Y |
Populating this field on the Data Sheet worksheet indicates a new deposit for system processing. Note. This field must be populated in the first row of the Data Sheet worksheet. All rows are skipped until the VB macros find a row with this field populated. |
|
Deposit ID |
Y |
The system assigns the deposit ID when you clear the Add to Data Sheet check box for this field type on the Template worksheet. The system assigns the deposit ID when this field on the Data Sheet worksheet is blank. |
|
OprID |
Y |
Default values are not available for this field |
|
Control Count |
Y |
|
|
Control Amount |
Y |
|
|
Accounting Date |
Y |
|
|
Bank Code |
N |
If you leave this field blank, the system uses the bank specified on the Receivables Options - General 1 page. |
|
Bank Account |
N |
If you leave this field blank, the system uses the bank account specified on the Receivables Options - General 1 page. |
|
Received Date |
N |
If you leave this field blank, the system uses the current date. |
Record Type 001 Payment Information |
Deposit BU Deposit ID |
Y |
Default values for these fields are copied from the deposit record, record type 000. |
|
Payment Sequence |
Y |
This field is automatically populated in the background. The number in this field increments by one for each new payment. |
|
Payment ID |
Y |
Data in this field on the Data Sheet worksheet indicates that there is a new payment within a deposit for system processing. If this field is left blank the payment will not be uploaded to the database. |
|
Payment Amount |
Y |
The Excel Payment Upload process does not process payments with negative values. |
|
Payment Currency |
N |
If you leave this field blank, the system uses the format currency specified on the Receivables Options - Payment Options page. |
|
Payment Method |
N |
The supported payment methods are:
If you leave this field blank, the system uses the payment method specified on the Receivables Options - Payment Options page. |
Record Type 002 Customer Remit Information |
Deposit BU Deposit ID Payment Sequence |
Y |
Default values for these fields are copied from the payment information, record type 001. |
|
Identification Sequence |
Y |
This field is automatically populated in the background. The number in this field increments by one for each new customer. |
|
Customer ID |
Y |
Do not enter data in this field if data is entered in the MICR ID field |
Business Unit |
Y |
This field is required when the Customer ID field is used. Do not enter data in this field if data is entered in the MICR ID field |
|
|
MICR ID |
Y |
This field is required if the Customer ID field is not used. Do not enter data in this field if data is entered in the Customer ID field |
Record Type 003 Summary and Detail Reference Remit Information |
Deposit BU Deposit ID Payment Sequence |
Y |
Default values for these fields are copied from the customer payment information, record type 001. |
|
Identification Sequence |
Y |
This field is automatically populated in the background. The number in this field increments by one for each new payment. |
|
Reference Qualifier Code |
Y |
This field requires data in the Reference Value field. If you enter data in this field, do not enter data in the Item, Item Line, Business Unit, Customer ID, Payment Amount, or Discount Taken fields. |
|
Reference Value |
Y |
This field requires data in the Reference Qualifier Code field. |
|
Entry Event |
N |
|
|
Item |
Y |
If you enter data in this field, do not enter data in the Reference Qualifier Code or Reference Value, fields. Requires data in the following fields
|
|
Item Line |
Y |
If you enter data in this field, do not enter data in the Reference Qualifier Code or Reference Value, fields. Requires data in the following fields
|
|
Business Unit |
Y |
If you enter data in this field, do not enter data in the Reference Qualifier Code or Reference Value, fields. Requires data in the following fields
|
|
Customer ID |
Y |
If you enter data in this field, do not enter data in the Reference Qualifier Code or Reference Value, fields. Requires data in the following fields
|
|
Payment Amount |
Y |
If you enter data in this field, do not enter data in the Reference Qualifier Code or Reference Value, fields. This amount must be greater than zero. Requires data in the following fields
|
|
Discount Taken |
N |
If you enter data in this field, do not enter data in the Reference Qualifier Code or Reference Value, fields. This amount must be greater than or equal to zero. Requires data in the following fields
|
See Also
Before you can enter and import spreadsheet payments, you must:
Copy the ExcelUploadforDeposits.xls file to a folder on your workstation.
The file is located in the core\build\excel folder of the shipped PeopleSoft software.
Install MicroSoft delivered XML library file MSXML.DLL on your workstation.
Set up your Microsoft Excel to accept macros.
Configure Integration Broker for the Receivables EIP (enterprise integration point).
The web server and the application server should be configured for setting up the Integration Gateway. Also, the application server should be configured to have the Pub/Sub servers established before bringing up the application server.
Determine your default local node for the Integration Broker to receive the data and run the application message. PeopleSoft defines the default local node as PSFT_EP, but you can change it to your default local node.
Configure your browser to automatically detect LAN settings. From your browser, navigate to Tools > Internet Options. Select the Connections tab and click the LAN Settings button. Select Automatically detect settings in the Automatic configuration group box.
Create an Excel Payment Upload process run control.
See Also
PeopleSoft PeopleTools PeopleBook: Integration Broker
Page Name |
Object Name |
Navigation |
Usage |
Excel Payment Upload Process |
EXCEL_EDIT_REQUEST |
Accounts Receivable, Payments, Electronic Payments, Excel Edit Request |
Use to retrieve and validate payment data that is created from an uploaded XML file. |
Payment Interface |
PAYLOAD_REQUEST |
Accounts Receivable, Payments, Electronic Payments, Process Payment Interface, Payment Interface |
Run the Payment Loader process for spreadsheet payments. |
Excel Upload Deposit Errors |
EXCEL_ERROR_LIST |
Accounts Receivable, Payments, Electronic Payments, Excel Error Correction |
Select deposit records created from the Excel Payment Upload process. |
Excel Upload Error Correction |
EXCEL_ERR_CORR |
Click the link for the deposit ID on the Excel Upload Deposit Errors page. |
Edit payments created from the Excel Payment Upload process that have been marked as containing errors |
References |
EXCEL_ERR_CORR2 |
Click the References link on the Excel Upload Error Correction page |
Edit the reference information for a payment received from the Excel Payment Upload process. |
This section discusses how to set up spreadsheet payments.
Access the Spreadsheet Payment - Template worksheet by clicking the ExcelUploadforDeposits.xls file delivered with your PeopleSoft products. You set the defaults and set up your payment spreadsheets for importing into PeopleSoft Receivables on the Template worksheet.
The Template worksheet in the Spreadsheet Payment workbook contains all of the available fields that you can use on the Data Sheet worksheet to enter payment data.
Machine Name, Integration Gateway, From Node, and To Node |
Enter machine name, integration gateway, from node, and to node. The data entered in these fields creates a URL string. Note. PeopleSoft delivers the From Node as PSFT_AR_DEP_EXCEL_XMLThe Integration Broker subscribes only to a single default local node. The To Node is the URL for the database that you want to receive the payments. See PeopleSoft PeopleTools PeopleBook: Integration Broker |
Generated URL |
Displays the URL generated from the host name, integration gateway, and node entered. The XML file created is posted to the defined URL. |
Record |
Displays the Excel Payment Upload table name associated with the field. |
Record Type |
Displays the record type associated with the field:
The deposit, payment, customer remit, summary and detail reference remit information fields are color coded to differentiate the various sets of data. |
Field |
Displays the name of the field. |
Label |
Displays the label of the field. |
Field Type |
Displays the type of the field. Types include Character, Date, and Number. |
Field Length |
Displays the length of the field. |
Required |
Displays a value of Yes or No depending on if the field is required during data entry. |
Default Value |
Enter the default value for the field. The value entered in the Template worksheet will be the field value displayed on the Data Sheet worksheet. You can override the default value on the Data Sheet worksheet. Note. You can enter default values for fields not included in the Data Sheet worksheet. The default values will become part of the
transaction data, but will not be visible on the Data Sheet worksheet. |
Add to Data Sheet |
Select the check box to include the field on the Data page. |
Update Data Sheet |
Click to update the Data Sheet worksheet with the selected fields. |
Set Password |
Click to enter or change a password associated with this spreadsheet file. |
Hide Template |
Click to hide the Template worksheet for this spreadsheet file. You will be prompted to enter a password if one has not been defined. You can navigate back to the Template worksheet by selecting from your Excel Tools menu, Macro, Macros. A window pops up listing all the macros. Select the ShowAdminSheet macro and click Run. The system prompts you for a password before displaying the Template worksheet. |
This section discusses how to enter spreadsheet payments.
Access the Spreadsheet Payment - Data Sheet worksheet by clicking the ExcelUploadforDeposits.xls file delivered with your PeopleSoft products. You use the Data Sheet worksheet to create and import payment data.
Enter payment data and import the payment data into PeopleSoft Receivables using the Data Sheet worksheet. The Data Sheet worksheet is designed to display only the fields selected in the Template worksheet.
Note. You must enter data in record type 000, the Deposit Information fields in the first row of the Data Sheet worksheet. The VB macro skips all rows until the macro locates data in the Deposit Unit field in record type 000.. You are not required to enter the deposit information for multiple payments if they are part of a single deposit. Also, you are not required to enter the same payment information for multiple lines of customer and item remit information.
Generate XML and Post |
Click to create an XML file and post the file to the URL defined in the Template worksheet. The Excel Save As window appears when you click the Generate XML and Post button. Save the XML file to the same folder where the actual spreadsheet resides to instantly post the XML file to the URL. Note. The URL string must be valid to post the XML data. The Excel Upload Payment Process (AR_EDIT_UPLOADED_PAY_INFO) retrieves the XML data from the URL site once it is posted. |
Generate XML |
Click to create an XML file. The Excel Save As window appears when you click the Generate XML button. Indicate the file name and where you want to save it. For example, you can write the data to an FTP site and create another process to post the XML file to the URL. When Integration Broker is active, it posts the XML data to the system making the data available for processing by the online and batch Receivables Excel Payment Upload Process. |
Record Type (Line 2) |
Displays the record type associated with the field.
|
Description (line 3) |
Displays the description of the field. |
This section discusses how to edit and process spreadsheet payments.
Access the Excel Payment Upload Process page.
Deposit Unit |
Enter the business unit for the excel deposits you want to edit. Leave this field blank to accept deposits from any business unit. |
Deposit ID |
Enter the deposit ID for the excel deposits you want to edit. Leave this field blank to accept all deposits. |
User ID |
Enter the user ID for the excel deposits you want to edit. Leave this field blank to accept all user IDs. |
Access the Payment Interface page.
Select the Upload from Excel check box.
Note. You can use the Excel Payment Upload process to enter remit only information.
Use the Excel Upload Deposit Errors page to review and select deposit records created by the Excel Payment Upload process. You can narrow the selection criteria by specifying a Deposit Business Unit, Deposit ID, Payment ID, or User ID. You can review all of the Excel deposit records by leaving all four of these fields blank.
Access the Excel Upload Deposit Errors page.
Error Deposits Only |
Select to display only spreadsheet deposits that contain errors. |
All Deposits |
Select to display all spreadsheet deposits with or without errors. |
Deposits without Errors |
Select to display only deposits that do not contain errors. |
Search |
Click the Search button, after you determine the search criteria, to display the relevant deposits in the Deposits region. The page displays the deposit data and up to five error messages for each deposit. |
Clear |
Click to clear the search criteria. |
Load Deposits to Staging Tables |
Click to run the Excel Payment Upload Process. The process uses the values in the Deposit Unit, Deposit ID, and User ID fields for the run control criteria. You can view the deposits that the system will edit and load to the staging tables by selecting the Deposits Without Errors checkbox and then clicking the Search button . |
Process Monitor |
Click to access the Process List page and view the process you initiated by clicking the Load to Staging Tables link. There is a link on the Process List page that enables you to return to the Excel Upload Deposit Errors page. |
Delete |
Select to mark the deposit for deletion. |
Deposit ID |
Displays the identifier for a payment within a deposit. Click the link for the payment to open the Excel Upload Error Correction page, where you can edit payments created from the Excel Payment Upload process that have been marked as containing errors. |
Deposit Errors |
Indicates if the deposit has errors. Valid values are Y, to indicate errors in a deposit, and N, to indicate that there are no errors in a deposit. |
Message 1, Message 2, Message 3, Message 4, and Message 5 |
Displays up to five error messages associated with each deposit. |
Delete Selected Deposits |
Click to delete any deposits on this page that have the delete check box selected. |
Use the Excel Upload Error Correction page to display the deposit data for deposits with errors. This page also displays up to five error messages for a deposit. You can change the deposit information or information for individual payments on this page to correct the errors. The system enables you to add or delete payments for the current deposit. If you add or delete payments, you must also change the values in the Control Total Amount and Control Countfields until the values in the Difference Amount and Difference Count fields equal zero.
After completing the changes, select the Apply button to save your changes, or select the OK button to save the changes and return to the Excel Upload Error Correction page. Click the Cancel button to remove any changes you have made since clicking the Apply button or the OK button.
Note. Clicking the Cancel button after accessing the References page can also remove some changed data if you do not first click the Apply button or the OK button.
Access the Excel Upload Error Correction page.
Message 1, Message 2, Message 3, Message 4, Message 5 |
The system displays up to five error messages associated with the deposit. The Payments region displays all error messages associated with the payments in the deposit. |
Get Payments with Errors |
Click to populate the Payments region with information about any payments in this deposit that contain errors. |
Get All Payments |
Click to populate the Payments region with information about any payments in this deposit. |
Error Correction Complete |
Displays a checkbox that indicates the error correction for this deposit is complete. |
References |
Click to access the References page. You can modify values and add or delete reference records on this page. |
Use the References page to add, delete, or modify any customer, summary, and detail references associated with a payment.
Click the Apply button to save your changes or click the OK button to save your changes and return to the Excel Upload Error Correction page. Clicking the Cancel button loses any changes you have made.
Access the References page
This section provides an overview of the EFT payment process flow and discusses how to:
Load EFT payment files.
Run the Payment Loader process.
See Also
Run the AR_DRAFT_EFT Application Engine process to load the EFT files into the Payment staging table (AR_PAYMENT_EC).
The process extracts regular payments from the file if the creation date equals the accounting date (due date). If the customer name can be resolved to a customer ID, the system also creates a customer staging record (AR_IDCUST_EC). The process uses the Kijitsu file layout.
Run the Payment Loader process to move the data from the staging tables into the payment application tables.
View Application Engine messages.
Check for errors at the deposit level on the All Deposits or Incomplete Deposits page.
Correct errors in the deposit and payment entry pages and the Payment Interface Duplicates component.
If you use cash control accounting and you have a duplicate out-of-balance deposit, run the Cash Control process to create the cash control accounting entries.
You must associate the customer IDs with the customer names in the EFT files on the Customer EFT Name page.
See Also
Associating EFT Payment File Names With Customer IDs
Page Name |
Object Name |
Navigation |
Usage |
DR_EFT_REQUEST |
Accounts Receivable, Drafts, Create Drafts, Receive Draft Payments, Load EFT Payments |
Use to load regular payments from an EFT file. |
|
Payment Interface |
PAYLOAD_REQUEST |
Accounts Receivable, Payments, Electronic Payments, Process Payment Interface, Payment Interface |
Define run parameters for the Payment Loader process to load EFT payments into the payment application tables. |
Access the Load EFT Payments page.
Process |
Select Payments or Both to process the regular payments in the EFT file. Note. If you select Both the process loads draft and regular payments. |
Review |
This field is used only for draft processing. |
File Name |
Enter a name for the EFT file. The name of the EFT file must be unique. The file must be loaded into the directory on the application server defined by %PS_SERVDIR%\FILES, where %PS_SERVDIR% is the directory where the application server domain is defined. If you process the EFT file using Process Scheduler, then you need to load the file into %PS_HOME%\appserv\prcs\ <database name>\files. After the system processes the file, it updates a table (DR_FILE_NAME) on the database with the name of the flat file that has been loaded. Using a unique file name prevents drafts or payments from being entered into the system twice. Only the file name is stored, not the path. |
Access the Payment Interface page.
Select the EDI 820 check box.
This section provides an overview of electronic payment errors and list pages used to check electronic payment errors.
The Payment Loader process checks for formatting errors and duplicates. These error are corrected as follows:
Nonnumeric characters in numeric fields are replaced with zeros.
Invalid dates in date fields are replaced with the current date.
Duplicate deposits in lockbox and EDI deposits are placed on hold, with an indicator on the Payment Interface Duplicate - Totals page.
The system uses different criteria to identify duplicates, depending on the type of deposit:
If two lockbox deposits with the same deposit unit and lockbox ID are received, the system uses four criteria to determine whether they are duplicates: bank, bank account, deposit date, and control totals.
For EDI deposits, if two payments with the same payment ID are received, the system uses the following criteria to determine whether they are duplicates: bank, bank account, deposit date, amount, and deposit unit.
The system groups duplicate payments into deposits and marks them as duplicates.
Online error correction is available for any payments that do not transfer smoothly. If a deposit is out of balance and contains errors, you can access it on the All Deposits or Incomplete Deposits pages.
Page Name |
Object Name |
Navigation |
Usage |
DEPOSIT_ALL |
Accounts Receivable, Payments, Review Payments, All Deposits, All Deposits |
Check a specific deposit ID number or scan any or all of your deposits at one time. Displays control totals and status information for a single deposit at a time. |
|
DEPOSIT_INCOMPLETE |
Accounts Receivable, Payments, Review Payments, Incomplete Deposits, Incomplete Deposits |
Track down deposits that are not completely processed. View the payment amount in the deposit that is still in process, summarize out-of-balance deposits or view the list of incomplete deposits by operator. The deposits contain a mix of posted and unposted payments. |
This section provides an overview of error correction and discusses how to correct duplicate deposits.
Error correction involves comparing and matching your recorded business transactions with the bank transactions. If the totals or control amounts are out of balance on the Regular Deposit - Totals page, then the deposit is unbalanced. You must correct the errors.
Use these indications to determine whether the deposit is balanced:
If the difference on the Regular Deposit - Totals page is zero, then the deposit is balanced.
If the difference is not zero, then there are errors.
If the control total amount and entered total amount are the same on the Regular Deposit - Totals page, then the deposit is balanced.
You can delete a deposit that has unmatched remittance information or you can balance unmatched deposits using the Regular Deposit Balancing component (BALANCING).
Note. The Regular Balance Balancing component is available only if the deposit is out of balance.
See Also
Page Name |
Object Name |
Navigation |
Usage |
Totals |
PAYMENT_DATA1 |
|
Review deposit totals and delete duplicate deposits created by the payment interface. |
Payments |
PAYMENT_DATA2 |
|
Provide detailed information for each payment in a regular deposit. |
Action |
PAYMENT_DATA3 |
|
Select an action to save the deposit or delete the deposit. |
Access the Correct Duplicate Payments - Totals page.
Hold Duplicate |
If this is not a duplicate deposit, clear the Hold Duplicate check box and make the deposit available for payment processing. If this is a duplicate deposit, click the Delete Deposit button to delete the deposit. This field is visible only if the Payment Loader process marked a lockbox deposit as a duplicate or if an EDI deposit contains duplicate payments. |
Note. If no lockbox or EDI deposit errors exits, the Correct Duplicate Payments component is unavailable.