Process X - Populate Payment Upload Staging
Process X refers to the mechanism used by your organization to populate the various staging tables (shown in the orange section of the following ERD).
Deposit Control Staging
You must create a deposit control staging record for each batch of payments to be uploaded into the system. The name of this table is CI_DEP_CTL_ST. The following table describes each column on this table.
Column Name |
Length |
Req'd |
Data Type |
Comments |
EXT_SOURCE_ID |
30 |
Y |
A/N |
This must correspond with an external source ID on one of the defined tender sources. Refer to Setting Up Tender Sources for more information. |
EXT_TRANSMIT_ID |
50 |
Y |
A/N |
This is the unique identifier of the transmission from the external source. This must be a unique value for each transmission from the source. |
DEP_CTL_STG_ST_FLG |
2 |
Y |
A/N |
This must be set to 20 (20 is the lookup value that corresponds with the Pending state) |
DEP_CTL_ID |
10 |
N |
N |
Leave this column blank. It will be assigned by the system when it creates a deposit control record. |
TRANSMIT_DTTM |
15 |
Y |
DateTime |
Date and time that the file was transmitted. |
CURRENCY_CD |
3 |
Y |
A/N |
This must be a valid currency code (this would be USD for United States dollars). |
TOT_TNDR_CTL_AMT |
13.2 |
Y |
N |
This column must equal the sum of the payment amounts on the tender control staging records associated with this deposit control staging. |
TOT_TNDR_CTL_CNT |
10 |
Y |
N |
This column must equal the number of tender control staging records associated with this deposit control staging. |
LAST_UPDATE_INST |
10 |
N |
N |
This field is populated during the upload. It is the process scheduler instance ID of the process performing the upload. |
You must create one or more Tender Control Staging for this deposit control staging record.
Tender Control Staging
You must create at least one tender control staging record for each batch of payments to be uploaded into the system. The name of this table is CI_TNDR_CTL_ST. The following table describes each column on this table.
Column Name |
Length |
Req'd |
Data Type |
Comments |
EXT_SOURCE_ID |
30 |
Y |
A/N |
This must correspond with the external source ID on the parent deposit control staging record. |
EXT_TRANSMIT_ID |
50 |
Y |
A/N |
This must correspond with the external transmission ID on the parent deposit control staging record. |
EXT_BATCH_ID |
30 |
Y |
A/N |
This is the unique identifier of the batch of payments in respect of the external transmission ID. |
TNDR_CTL_STG_ST_FLG |
2 |
Y |
A/N |
This must be set to 20 (20 is the translate value that corresponds with the Pending state) |
TNDR_CTL_ID |
10 |
N |
N |
Leave this column blank. It will be assigned by the system when it creates a tender control record. |
TOT_TNDR_AMT |
13.2 |
Y |
N |
This column must equal the sum of the payment amounts on the payment tender staging records associated with this tender control staging. |
TOT_TNDR_CNT |
10 |
Y |
N |
This column must equal the number of payment tender staging records associated with this tender control staging. |
You must create one or more Payment Tender Staging records for this tender control staging record.
Payment Tender Staging
You must create at least one payment tender staging record for each payment associated with the tender control staging record. The name of this table is CI_PAY_TNDR_ST. The following table describes each column on this table.
Column Name |
Length |
Req'd |
Data Type |
Comments |
EXT_SOURCE_ID |
30 |
Y |
A/N |
This must correspond with the external source ID on the parent deposit control staging record. |
EXT_TRANSMIT_ID |
50 |
Y |
A/N |
This must correspond with the external transmission ID on the parent tender control staging record. |
EXT_BATCH_ID |
30 |
Y |
A/N |
This must correspond with the external batch ID on the parent tender control staging record. |
EXT_REFERENCE_ID |
30 |
Y |
A/N |
This is the unique identifier of the payment in respect of the external batch ID. |
PAY_TND_STG_ST_FLG |
2 |
Y |
A/N |
This must be set to 10 (10 is the translate value that corresponds with the Pending state) |
PAY_TENDER_ID |
12 |
N |
N |
Leave this column blank. It will be assigned by the system when it creates a tender record. |
TENDER_AMT |
13.2 |
Y |
N |
The amount tendered (i.e., the payment amount). |
ACCOUNTING_DT |
10 |
Y |
Date |
This is the date that should be used for accounting purposes. This should correspond with an open accounting period. |
TENDER_TYPE_CD |
4 |
Y |
A/N |
This must correspond with the prime key of one of your tender types. Refer to Setting Up Tender Types for more information. |
CUST_ID |
15 |
Y |
A/N |
This is the account ID or old account number of the customer tendering the payment. If the system cannot find an account ID or old account number that matches this value, the account ID of the tender source's suspense SA will be used on the corresponding tender and payment. |
MICR_ID |
30 |
N |
A/N |
This is the MICR ID associated with the payment. |
NAME1 |
40 |
N |
A/N |
This is the customer name on the payment. |
CHECK_NBR |
10 |
N |
A/N |
This is the check number on the payment. |
Payment Staging
You need only populate rows on this table if any of the following conditions apply:
- If you need to distribute a payment tender to an account other than that defined with the CUST_ID on the payment tender staging record, you must create a payment staging record. You may distribute a tender to multiple accounts by creating multiple payment staging records. Note, if you want to distribute the payment tender to the same account, you do NOT need a payment staging record.
- If you want to restrict a payment to a specific service agreement, you must insert a row on this table to indicate the specific the service agreement in question. You do this by populating MATCH_TYPE_CD with a value that indicates that you are paying for a specific service agreement and MATCH_VALUE with the unique ID of the service agreement.
- If you practice open-item accounting, you must insert a row on this table for each to indicate the open-item to which the payment should be matched. Note, because open-item customer typically match payments to bills, you would populate MATCH_TYPE_CD with a value to indicate that you are matching by bill ID and MATCH_VALUE with the unique ID of the bill.
The name of this table is CI_PAY_ST. The following table describes each column on this table.
Column Name |
Length |
Req'd |
Data Type |
Comments |
EXT_SOURCE_ID |
30 |
Y |
A/N |
This must correspond with the external source ID on the parent payment tender staging record. |
EXT_TRANSMIT_ID |
50 |
Y |
A/N |
This must correspond with the external transmission ID on the parent payment tender staging record. |
EXT_BATCH_ID |
30 |
Y |
A/N |
This must correspond with the external batch ID on the parent payment tender staging record. |
EXT_REFERENCE_ID |
30 |
Y |
A/N |
This must correspond with the external reference ID on the parent payment tender staging record. |
CUST_ID |
15 |
Y |
A/N |
This is the account ID or old account number of the customer to which the payment should be distributed. If the system cannot find an account ID or old account number that matches this value, the account ID of the payor is used on the corresponding payment. If the payor's account ID is invalid, the tender source's suspense SA is used. |
PAY_AMT |
13.2 |
Y |
N |
The amount tendered (i.e., the payment amount). |
MATCH_TYPE_CD |
8 |
N |
A/N |
See the description of the MATCH_VALUE field below. Refer to Payments And Match Events for more information about the significance of this field. |
MATCH_VALUE |
30 |
N |
A/N |
MATCH_VALUE and MATCH_TYPE_CD are used in conjunction to indicate that the distribution of the payment should be restricted in some way (i.e., the standard payment distribution algorithm should not be used). MATCH_TYPE_CD indicates how the payment should be distributed (e.g., only distribute to a specific service agreement), MATCH_VALUE contains the ID of the restriction (e.g., the SA ID). If MATCH_TYPE_CD is specified, it must reference a valid Match Type. |