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).

Process X refers to the mechanism used by your organization to populate the Deposit Control, Tender Control, Payment Tender, and Payment staging tables

The following topics in this section describe each of these tables:

Deposit Control Staging
Tender Control Staging
Payment Tender Staging
Payment Staging

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.

Top of Page

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.

Top of Page

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.

Top of Page

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.