Path: Accounting > Universal Financial > Template Assignment
Depending on your needs, one template can include all the needed information from various functions in WebTMA, or the information can be put in several different Templates.
How to Determine UFI Template Assignment
Use these instructions to associate a Template with a functional module in WebTMA that is used with account charges. You can save to a file or insert directly in an accounting application table.
-
Select the Transaction Type of the module to be set up with a template. Note: The path is set in the UFI Web Service Configuration file, and only one path is available. Options that pertain to modules you have purchased are available. For example, if Material Management has not been purchased, Sales Order is hidden.
-
Select the Template Code from the list of available Templates.
-
Choose the applicable radio button, either Save As File or Insert in Table.
-
Save As File requires the File Type. This is the file format the Template should be saved in, i.e., Fixed Length or Tab Delimited. See File Name Format Field for details.
-
Insert In Table requires an Insert Statement.
When a post charge is run, it saves the file to the directory specified in the Config file of the UFI Service.
If you use an Encumbrance Template, some existing columns may not apply to encumbered charges. These inapplicable columns will return an empty string but will not cause errors.
File Name Format Field
The optional File Name Format field allows you to assign a custom name to the export file. The format supports the following dynamic fields:
{JournalCode}
{SystemDate} - Current UTC Time
{BatchJob}
{PostDate}
{Sequence}
The default file name format is:
{JournalCode}{SysDate(MMddyyyHHmmss0}{BatchJob].txt
Date formatting for the System and Post Dates uses standard C# style date formatting. A full list of options can be found online.
The sequence option executes a function to determine all the postings that occurred on the date of the current record, and then indicates the sequential order (starting with 1) where the current record falls. For example, if the current record has a batch date of 2022/06/15 10:34:23, UFI will pull a list of all Batch Postings with a batch date of June 15, 2022. The sequence number is the current record’s index position in that list (starting with #1).
Template Assignments in Operation
When the Post Charges function is performed from WebTMA, this interface will automatically create a fixed length or tab delimited file. The file name is in the format of mmddyytime. A file will be created for the identified Transaction Type and output to the path in the client document directory on the web server.
Alternatively, you can send the output to a table. If you use the Insert in Table option, make certain you have an identical number of columns in each of the following in your template:
-
Number of header columns
-
Number of line columns
For example, if you have two columns in your Line definition, your Header definition must also contain two columns. Note that you can omit Header Columns if they are not needed.
The table must exist in the database specified in the Database Connection. When inserting into a table, the columns for the template must be mapped one-to-one in the insert statement used to insert into the table.
The Insert Statement must be provided by the WebTMA user. The “values” clause and appropriate data will be supplied by WebTMA during a Post Charges action.
The following is an example of an Insert Statement excluding the Values clause:
insert into UFIImport(column1, column2, column3, column4, column5, column6, column7, column8, column9)
The Insert Statement defines the table and columns that are to be inserted. The “values” clause is created during the Post Charges action with the appropriate values for the records being posted. Unless the number of columns in the Template is equal to the number of columns in your Insert Statement, the statement will fail because they do not match.
See Explicit Insert Statements for information about using custom value clauses in an Insert Statement.
Explicit Insert Statements
The use of Insert Statements is described in Template Assignments in Operation.
WebTMA also gives you the option to include an explicit values clause in your Insert Statement.
When you write your Insert Statement, you can include the key word “values” in the statement. The batch processor flags this as an explicit Insert Statement.
In the following example, the batch processor replaces the values you supply for '?' with the appropriate value from the template definition.
insert into UFIImport(column1, column2, column3, column4, column5, column6, column7, column8) values (? + ?,?,?,?,?,?,?,?)
In this example, the nine question marks represent the nine columns set up in Universal Financial > UFI Template Setup. These are inserted in the eight columns in your UFI database because the first two columns are concatenated. Note that the concatenation symbol “+” is specific to SQL Server; the concatenation symbol for Oracle is “||”. You must use an appropriate concatenation symbol for your database.
NOTE: Read and insert rights are required when exporting to a database.
Want to Know More?
Visit the WebTMA Help Page