Line Item Matching

Line Item Matching (LIM) feature allows the application users to match and verify the processed Invoices with their corresponding Purchase Orders on the Validation Screen. Earlier LIM was developed as an external application, but now it has been integrated within the Ephesoft environment. The administrator can configure the Batch Class based on the pre-configured Default.properties file and po_configuration table, or can further change these configurations and map to an external PO Data Source as per the requirement. These configurations can be different for each data table within the Batch Class. User can configure LIM from the Document Type Screen and once the batch is executed, the user can select the option for LIM from the Validation Screen or directly use the short-cut to navigate to the LIM Screen (Ctrl + Shift + Y) to verify the Invoices against their corresponding Purchase Orders. User can manually match the items at the LIM screen if the results does not match on the Validation Screen.

Two-way matching process: In the two-way matching process, quantity and price on the invoice are matched to the quantity and price on the corresponding purchase order. The line items are considered invalid, if any of the two conditions fails:

Quantity in Invoice <= Quantity in PO

Price in Invoice = Price in PO

Line Item Matching: Items in the invoices are matched to the corresponding purchase order on the basis of configuration done by the administrator in the Properties file.

Video:

 

Batch Class Configuration

Following are the Configurations done at Batch Class level which can be further changed by the administrator as per the requirement.

  1. Go to Document Type >> Invoice-Table >> Index Fields. Make sure that the following Document level index fields are present in the Batch Class.PO Number: This field is required to fetch Purchase Order results from the database. If this field is not configured in the Batch Class, the application cannot proceed further. It is a mandatory field.Comment: This field helps user to add any comment in the Batch.xml in case no PO match is found. It is a mandatory field.Line Items Matched: This field is reflected on the LIM Screen as a checkbox. It shows whether the line items are matched or not. Configure it as a Read-only field in the additional configuration and field type as Checkbox. The validation Pattern should have the value True/False.F:\Enterprise\Product documentation 4060\images\lim3.png
  2. Go to Document Type >> Invoice-Table >> Tables >> Table Columns. Make sure that the following columns are present in the data table.Quantity: This column is required for applying two-way-matching algorithm. It is a mandatory field.Unit Price: This column is required for applying two-way-matching algorithm. It is a mandatory field.PO Line: This column will be updated based on the matched PO line items. It is a mandatory field.All the other fields and columns are optional.

PO Database Configuration

A Batch Class can have more than one data table and corresponding to each data table there can be separate Properties file. The administrator can make changes in each Properties file for different set of configurations as needed.

There is a default PO data table “po_configuration” already configured in the Ephesoft database with the following columns.

  1. po_line
  2. po_number
  3. part_number
  4. vendor_id
  5. vendor_name
  6. quantity
  7. available_quantity
  8. unit_price
  9. item_description

The administrator can also configure an external PO data source. In order to do that, following configurations are required:

  • On the Tomcat server, add a resource tag in server.xml file for the external data source and corresponding resource link in the context.xml file.
  • Update the Data Source and PO_Table name in the property file.

Property File Configuration

Property file is located at:

SharedFolders/BatchClassIdentifier/lim/DocumentTypeName.DatatableName.properties

If the above Property file does not exist, then go to the Default.properties file located at –

SharedFolders/BatchClassIdentifier/lim/Default.properties

 

Below is the Property Files table with details:

Property Placeholder Default Value Description Allowed Values Comment
PO_TABLE PO_CONFIGURATION Name of the PO table in database. The name of data base table
PO_COLUMNS_TO_DISPLAY All the PO column names The names of all PO columns to display at UI The names of all PO columns to display at UI. Column names are case sensitive.
INVOICE_COLUMNS_TO_DISPLAY All the Invoice columns names. The names of all invoice columns to display at UI. The names of all invoice columns to display at UI. Column names are case insensitive.
DLF_TO_DISPLAY_TOP All Document Level Fields in a batch class. The document level fields to be displayed at top panel. If this property contains fields names that are also to be displayed at bottom, then that fields are skipped. The document level fields to be displayed at top panel. Names are case insensitive.
DLF_TO_DISPLAY_BOTTTOM Sub Total, Invoice Total, Tax The document level fields to be displayed at bottom panel. The document level fields to be displayed at bottom panel. Names are case insensitive.
DLF_COMMENT Comment The comment document level field to add any comment in batch xml in case no PO match is found. The name of comment field configured in batch class. This field is case insensitive.
PO_COLUMNS_FILTER po_number The name of PO table columns based on which PO results will be filtered. The name of PO columns on which results should be filtered. PO Column names are case sensitive.
DLF_po_number PO Number The name of PO Number Document level field in batch class needed for executing search query. The name of Document Level Field in batch class. Property value is case insensitive.
PO_NUMBER po_number The name of PO number column in PO table. The name of PO number column in PO table. Property value is case sensitive.
MATCHING_ALGO_MAPPING part_number, SEARCH_IN_DESC, item_description, unit_price The priority based on which matching algo should be executed.
INVOICE_POLINE PO Line The name of PO Line column in data table needed for matching purpose. The name of PO Line column in data table. Property value is case insensitive.
PO_POLINE po_line The name of PO Line column in PO table needed for matching purpose. The name of PO Line column in PO table. Property value is case sensitive.
PO_QUANTITY available_quantity The name of available quantity column in po table needed for matching purpose. The name of available quantity column in PO table. Property value is case sensitive.
PO_PRICE unit_price The name of unit price column in PO table needed for matching purpose. The name of unit price column in PO table. Property value is case sensitive.
INVOICE_QUANTITY Quantity The name of quantity column in data table needed for matching purpose. The name of quantity column in data table. Property value is case insensitive.
INVOICE_PRICE Unit Price The name of unit price column in data table needed for matching purpose. The name of unit price column in data table. Property value is case insensitive.
INVOICE_DESC Description The name of description column in data table needed for matching purpose. The name of description column in data table. Property value is case insensitive.
MAPPING_CONFIG part_number,unit_price, available_quantity,item_description,po_line The column in PO table which should be mapped to datatable column. Required for copy purposes. Property value is case insensitive
TOLERANCE 0 The allowable tolerance for 2 way matching. Any positive integer value.
DLF_TOTAL Invoice Total The name of total document level field in batch class. Required in second phase. The name of total document level field in batch class. Property value is case insensitive.
DLF_SUB_TOTAL Sub Total The name of sub-total document level field in batch class. Required in second phase. The name of sub-total document level field in batch class. Property value is case insensitive.
FILTERABLE_PO_COLUMNS part_number Comma separated names of PO columns on which filters can be applied. The name of PO columns. PO column names are case sensitive.
DATASOURCE_NAME jdbc/ephesoft Name of data source from which PO configuration is to be fetched. Data source name Data source name is case sensitive.

 

MATCHING_ALGO_MAPPING

It should be defined in the order of priority. Its default value is:

part_number:Part No,SEARCH_IN_DESC:part_number,item_description:Description,unit_price:Unit Price

By default, matching will be done on basis of part number. If no match is found, then matching will be done by searching part number of PO in invoice description. If still no match is found, the item descriptions will be matched and lastly the unit price will be matched.

It is defined as {name of po column:name of invoice column, …}.

SEARCH_IN_DESC:part_number: This defines that part_number of PO table should be searched in description of invoice table for matching.

 

MAPPING_CONFIG

This property defines the name of PO column and corresponding data table column. It is required for the purpose of copy. For example, if po_line column is column name in PO table and PO Line is corresponding column name in data table then it will be defined as:

po_line:PO Line

Similarly, all columns will be defined as comma separated.

MAPPING_CONFIG=part_number:Part No,unit_price:Unit Price,available_quantity:Quantity,item_description:Description,po_line:PO Line

Working

In order to use the LIM feature for a Document Type, mark the checkbox LIM from the Additional Configurations column on the Document Type Screen. If LIM is configured at the Document level, the LIM button becomes visible on the Validation Screen for the selected Document Type.

F:\Enterprise\Product documentation 4060\images\lim1.png

In AUTOMATED_REGEX_VALIDATION, Line items are matched for the document types for which LIM was enabled. Based on validation “Line Item Field” is marked as True/Empty. If this plugin is not present at the Batch Class level, the application will always consider that the Line Items are not matched (validation failed) and the LIM button will appear in Red on the Validation Screen. The user has to manually validate the line items and process further.

F:\Enterprise\Product documentation 4060\images\lim2.png

On executing the Batch, the Automatic Validation module runs the LIM algorithm and validates the line items based on the configurations.

The checkbox Line Items Matched is a read-only field and is checked if all the line items are matched.

F:\Enterprise\Product documentation 4060\images\lim4.png

The LIM View lists the Invoice and PO data tables with line items matched.

Case 1: If the line items are matched and fields are valid, it will be shown as Green.

Case 2: If the line items are matched and validation fails, it will be shown as Red.

Case 3: If the line items are not matched, it will show empty rows.

Case 4: If there are multiple data tables for a Document Type and any of the property (Batch Class/ PO level configuration) is missing, it will consider validation failed and the LIM View button will be Red.

F:\Enterprise\Product documentation 4060\images\lim5.png

In the above example, the green rows reflect the Line Items which are matched and valid. And the red row reflects that the Line Item is matched but invalid, here the value of the PO Line field is not correct.

(Invalid – If either the price or quantity or both did not match.)

User can manually update the PO Line by selecting the desired value from the dropdown list.

Note that the Line Items Matched checkbox will be marked based on whether all line items are matched and valid, or not. User can also mark it manually, if needed.

F:\Enterprise\Product documentation 4060\images\lim6.png

By clicking on Save, only the Data Table which is visible will be saved.

All the other Data Tables will not be saved.

 

 

Was this article helpful to you?

wikiadmin

Comments are closed.