Lockbox Table Population
- Keith Mangold
This page documents the population requirements for the DepositImport table which is used for Lockbox Processing.
DepositImport Table
This table is where all records will be placed. INSERT a record into the table using the following rules:
Column Name | Data Type | Allow Null? | Value |
|---|---|---|---|
DepositImportID | int | No | Auto-Assigned. Do not specify a value. |
DepositImportStatusID | int | No | Set value to "2" for "Ready" Refer to lkpDepositImportStatus table for possible values. |
DivisionCode | varchar(3) | No | Must match a record within the "division" table, "divcd" column. |
BankAccountCode | varchar(5) | No | Must match a record within the "BankAcct" table, "bacctcd" column. |
DepositType | varchar(10) | No | Must match a record within the "ARSDepositType" table, "ardtcd" column. |
DepositDate | datetime | No | Valid date format. |
Description | varchar(255) | Yes | Free-form text field. Placed on the Bank Deposit Description field. |
CustomerShortName | varchar(15) | No | Must match a record within the "Cust" table, "sname" column. NOTE: If the Payment if for a Super Customer or Child of a Super Customer, this must be the Super Customer's Short Name, NOT the Child Customer. |
PaymentType | varchar(10) | No | Must match a record within the "ARSPaymentType" table, "arptcd" column. |
PaymentNumber | varchar(20) | No | Check # |
PaymentDate | datetime | No | Valid date format. |
PaymentAmount | decimal(16, 2) | No | Currency; Amount of Payment. |
Comment | varchar(255) | Yes | Free-form text field. |
InvoiceNumber | varchar(25) | Yes | Optional: Will attempt to match record against the "LISInvoice" table, "InvoiceNo" column. |
OrderNumber | int | Yes | Optional: Will attempt to match record against the "OrderMaster" table, "OrdNo" column. |
PONumber | varchar(40) | Yes | Optional: Will attempt to match record against the "LISInvoice" table, "CustPONo" column. |
ApplyAmount | decimal(16, 2) | Yes | Currency; How much of the PaymentAmount to Apply to the Invoice. |
CompanyID | int | Yes | Do Not Populate |
CompanyCode | varchar(2) | Yes | Do Not Populate |
DivisionID | int | Yes | Do Not Populate |
BankAcctID | int | Yes | Do Not Populate |
BankDepositNumber | int | Yes | Do Not Populate |
BankCurrencyCode | varchar(4) | Yes | Do Not Populate |
GLAccountID | int | Yes | Do Not Populate |
GLBankUnappliedAccountID | int | Yes | Do Not Populate |
GLControlAccountID | int | Yes | Do Not Populate |
CustomerID | int | Yes | Do Not Populate |
AccountingPeriodID | int | Yes | Do Not Populate |
ARSDepositTypeID | int | Yes | Do Not Populate |
ARSPaymentTypeID | int | Yes | Do Not Populate |
ARSBankDepositID | int | Yes | Do Not Populate |
ARSDepDetailID | int | Yes | Do Not Populate |
ARSDepDtlApplID | int | Yes | Do Not Populate |
LisInvoiceID | int | Yes | Do Not Populate |
BatchImportID | int | Yes | Do Not Populate |
ImportSourcePrimaryKey | varchar(255) | Yes | Do Not Populate |
ImportDate | datetime | Yes | Do Not Populate |
Sample INSERT Statement:
INSERT INTO [DepositImport]
([DepositImportStatusID]
,[DivisionCode]
,[BankAccountCode]
,[DepositType]
,[DepositDate]
,[Description]
,[CustomerShortName]
,[PaymentType]
,[PaymentNumber]
,[PaymentDate]
,[PaymentAmount]
,[Comment]
,[InvoiceNumber]
,[OrderNumber]
,[PONumber]
,[ApplyAmount])
VALUES
(2
,'001'
,'BANK1'
,'LOCKBOX'
,'09/25/2012'
,'Sample'
,'CUST001'
,'CHECK'
,'123456'
,'09/25/2012'
,500.00
,'MEMO #1234'
,'98700001'
,NULL
,'43-235634'
,500.00)
DepositImportStatusID Comment:
When creating records, it is recommended to initially insert records with the DepositImportStatusID equal to "1" for "Pending." Then, after you have interested all records into the table, mark them all as "2" for "Ready". This will then make the entire batch ready for processing within the ERP Lockbox Processing screen.
Example:
UPDATE DepositImport SET DepositImportStatusID = 2 WHERE DepositImportStatusID = 1
lkpDepositImportStatus Table
NOTE: Information displayed here is for reference purposes only. Records in this table should NOT be modified. Records within the table are used by the Lockbox Processing screen to return the appropriate records. Modifying or adding records can result in unexpected and unsupported behavior.
Status ID | Description |
|---|---|
1 | Pending |
2 | Ready |
3 | Completed |
4 | Rejected |
5 | Error |
6 | Processing |