Configure Import Templates - APOHUTCT
The Accounts Payable (AP) Configure Import Templates (APOHUTCT) page tells the interface which columns to import and where to place the data. The page also allows the option to default values for repetitive properties such as the transaction type or a bank ID. The template determines the type of AP set to be created: Open Hold (OH), Immediate Pay (IP), or Hand-Write (TR).
Property names are derived from the column name used to store the record in the database and cannot be changed by the user. It is critical that the exact spelling and case be used when specifying a property name in the import file.
The template created in APOHUTCT is used by utilities such as the AP Import CSV File (APOHUTIN) utility, to perform a file upload. APOHUTIN requires the following fields: CheckID, DistAmt, Div, PEID, Ref, and RefDt. Be sure to test new templates to ensure the data loads as expected.
Page Header
The top portion of the page contains four fields that apply to the template in general. The Template Code and Description are required.
Template Code: The Template Code is used to uniquely identify the import template. Reference this code within the BusinessPlus menu when executing the AP Import CSV File (APOHUTIN) process. The code should be between one and four characters, containing only letters and numbers. Use of punctuation and symbols is not recommended. If a Template Code does not appear within a utility menu, be sure the "Active" checkbox is checked in the APOHUTCT template; or clear the cache, log out and then log in to be sure the system recognizes the changes.
Description: This field contains a description of the template using up to 30 characters. The description will appear as the BusinessPlus menu description when the AP Import CSV File process is executed.
Output Type: Select the code from the drop-down list to determine the type of AP Set to be created from the input file. There are three types of AP sets: OH = Open Hold (APOHBTUB), IP = Immediate Pay (APIPBTUB), HW = Hand-Written (APTRBTUB).
Active: This checkbox indicates whether the import template can be used by the AP Import CSV File (APOHUTIN) process. Only active templates will appear on an upload utility's menu when attempting to execute the AP Import CSV File process. The "Active" flag disables a template without having to delete it; the template is active when checked.
Page Grid
The bottom portion of the page contains fields that determine how to build the upload file. Column Number, Property Name, and Default Value tell the template how to read the upload file and where exactly to interface file data, so it will properly merge into the correct field within the given AP set.
Column Number: This numeric field tells BusinessPlus how to read the file data. Column number 0 (zero) is a special column value used to define a hard-coded value for a property. Column "0" needs a valid data element (defined within BusinessPlus) to be placed in the Description field for the given Property Name tagged; this data element will be used for all the invoices in the set to be loaded.
In the example above, the Warehouse property will always be set to "MAIN" on every AP record because the column number is zero. Use this method to define a value for properties that are always the same for this template. If a Template column number refers to a field in the file that does not exist, that property will be left empty on the AP record.
Property Name: The Property Name field contains a drop-down list that contains static codes representing column names within the AP database. The list is predefined in the software and cannot be changed by users. Note that the "DistAmt" is a required field and should equal the Unit Price x Units. The data loaded into the DistAmt field is literally transferred from the file into AP. The data import is considered a "data dump" because the file content will be placed into the AP database "as is", without warnings from the system with the exception of the PR and PO number fields. The file will not load when an invalid PR and/or PO number is within the upload file. Therefore, ensuring the correct spelling, case, content, property value and so on, are essential for the file upload process. The APOHUTIN utility requires the "DistAmt" to be a column within the .csv file. The Unit Price (amount) and Units (qty) are not required. Therefore, if the template holds only "DistAmt", the units will default to "1" while the Unit price will default to the "DistAmt" (dist amount). Also note that if DistAmt, UnitPrice and Units are all included in the template, be sure the DistAmt is accurate since the DistAmt value is not validated against the price x qty. Be aware of this because the DistAmt feeds AP's Invoice Summary totals and User Invoice Total fields. Summary totals do not post, but when a posting strategy calculation includes summary totals then the "DistAmt" field must agree with the System Invoice Total and sum of invoice(s) accumulated "Extended Amount"(s).
The following are descriptions of the various Property Names:
Property Name | Page Name / Description |
|---|---|
AltAddrCd | Alt Address Code |
AltPeId | Vendor of Rec |
AltPedbCd | Alt PEDB |
Code AssetId | Asset Id |
AuthDt | Authorized Date |
BatchId | Set Id (also a question on APOHUTIN) |
Bid | Bid |
CalcFlag | Calc Flag |
Catalog | Catalog |
ChargeAmt | Charge Amount |
ChargeCd | Charge Code |
CheckId | Bank ID (Required) |
CheckNo | Check No |
CheckType | Check Type |
CkDt | Check Date |
CkJob | Check Job |
ClaimNo | Claim No |
Contract | Contract |
DataKey | Data Key |
DeleteFlag | Delete Flag |
Desc | Description (30 character or less) |
DiscAmt | Discount |
DiscTerms | Disc. Terms |
DistAmt | Distribution Amount (Required) and should equal Unit Price x Units |
Div | Division Code (Required) |
DueDt | Due Date |
DutyAmt | Duty Amount |
DutyCd | Duty Code |
EncbPart | P/F (reference to partial or full pay) |
EncbPo | PO# |
Explode | Explode |
FormName | Form Name |
FormerId | Former Bank ID |
FormerNo | Former Check No |
GlGr | GL ledger code |
GlKey | GL key (put FQA string in Key format) |
GlObj | GL object |
ImageRec | Image Rec |
JlGr | JL ledger code |
JlKey | JL key |
JlObj | JL object |
JobNo | Job Number |
Misc | Misc |
Msg | Msg |
Msur | Units of Measure |
PayType | Pay Type |
PeAddrCd | Addr Cd |
PeId | Vendor (Required) |
PeName | PE Name |
PedbCd | PEDB Code ('P' for PE database (default), 'E' for HR database) |
PoType | PO Type |
PoiQty | Quantity |
PostCd | Posting Code |
PostDt | Post Date |
PrItem | PR Item # |
PrNo | PR # |
PrepId | Prep ID |
ProdId | Product ID |
RcvdDt | Received Date |
RecNo | Item No |
Ref | Invoice (Required) |
RefAmt | Invoice Amount |
RefDt | Invoice Date (Required) |
RefundType | Refund Type |
RelOne | Relate 1 |
RelTwo | Relate 2 |
ResaleFlag | Resale |
RetailAmt | Retail Amount |
SecCd | Security Code |
SecRef | Second Ref |
SepCheck | Sep Check |
SeqNo | Sequence No |
Sort | Sort |
SortField | Sort Field |
Status | Status |
TaxAmt | Tax Amount |
TaxAmt2 | Tax Amount 2 |
TaxCd | Tax Code |
TaxCd2 | Tax Code 2 |
TrnsFormat | Transaction Format |
UndoJobNo | Undo Job |
UnitPrice | Unit Price |
Units | Quantity |
VendAcct | Vendor Account |
Warehouse | Warehouse |
Wo | Work Order |
Default Value: Required when using Column "0". This field allows a default value to be specified that will be used on the AP record whenever the related property does not receive a value from the input file. In the case of column zero, the default is always used as "column zero" fields and, as such, do not appear in the input file.
Example
The following is an example of a defined template that uses an Excel spreadsheet to load data into the system. Using APOHUTIN to load invoice data into APOHBTUB (Output Type "OH"), the columns listed on the template need to be defined in the .csv file. In this example, the GLKey will insert as "100000" for all line items defined within the file. Number columns 1 through X to indicate the order that BusinessPlus ought to read file content. For example, if Column 1 has the Property Name "PeId", BusinessPlus will see the 1st column in the file as PEID (Column 1 and the Excel column A are synonymous).
The .csv file based on the template should look like this:
For the .csv file in this example, be sure to delete the header row, select the area to save/load, then save the flat file as ".csv" to local (or preferred) drive.
Using APOHUTIN, select the prepared .csv file from the local drive, choose the corresponding template, type in a name for the new Set ID, and click Submit. The template's Output Type determines where the set will be made. In this example, the Output Type is "OH", so the set is in APOHBTUB. Proof the set, modify, and distribute as usual.