Micronet Adaptor (2.0-2.9) Adaptor Routine: Order Product Deliveries Text File Export
The Order Product Deliveries Text File Export routine generates text files that contain a list of product lines that appear in Micronet Distribution(Micronet) invoices, based on the original orders saved in the connector, that are marked as having undelivered quantities. This routine has the effect of being able to send out order product delivery notifications to external systems, as invoices are printed out with goods in Micronet.
In Micronet when an invoice is generated from an order/picking slip, it indicates that the order's products are ready to be delivered. Using this routine you can have a report that allows you or other systems to know what products where delivered against the original orders. When an order-product line is matched to invoice, the details of the invoice will be recorded in the connector against the order product under the Deliveries tab in the Order Details window.
The text files contain order product data on each line and information about the original order. These text files are directly readable by humans in a Windows application such as Notepad or Excel. Typically these files contain a file extension like .CSV (Comma Separated Value) or .TXT (Text File), but it can be configured for your own tailored file format.
Topics |
Prerequisites
Please make sure you have read through the Get Started/Overview and the Connector Adaptor Micronet (version 2.0-2.9) documents before reading further on.
How It Works
The Order Product Deliveries Text File Export first looks at all the orders within the connector that are marked as imported, as well as marked as having not all of its products delivered. For each matching order it then tries to find invoices within Micronet with the same order code.
If one or more invoices are found, the routine then looks at each ordered product line in the order and sees if there are matching lines in the invoices. If the quantity of the product line matches the quantity across all of the matching Micronet invoices, then the product line is added to the routine's outputted delivery text file. Additionally delivery records will be created against each of the order product lines in the connector, to ensure that the lines are not looked at again for the next time the routine runs. If all product lines for a given order in the connector have had their quantities marked as delivered, then the order's product delivered status will be set to yes. This status ensures that when the routine runs again that it doesn't look at these processed orders.
After the routine has gone through all the relevant orders, all the delivered product lines will be added to the one file and saved to the filesystem. Optionally an email notification can be sent out that reports on what happened in the routine.
Routine Process Diagram
Steps:
- Locate all orders in the connector that assigned to the adaptor and are marked as imported, and have Products Delivered set to No.
- For each order find invoices in Micronet that contain the same order code.
- For all found invoices create delivery records against each product in the order that have their total ordered quantity matched against one or more found invoices. If all product lines have been marked as delivered then update the order's "products delivered" status to Yes.
- Generate a product deliveries text file for all of the product delivery records created in the routine, and save the file to the auditing directory.
- Copy the delivery file from the audit directory to the deliveries directory.
- Optionally email out a report of the routine if the "Send Delivery Email Notification" setting is set to true and an email address has been set.
Example Product Deliveries Text File
Routine Setup: Creating a new Order Product Deliveries Text File Export routine
- Open up the Accounting Connector application.
- In the Adaptors and Messages tab, Under the Adaptors section, click on the Settings button against a listed Micronet version 2.0-2.9 adaptors.
- Click on the Accounting Routines tab.
- In the Routine Type drop down, select the Order Product Deliveries Text File Export option, then click the Create Routine button.
- In the Routine Code text box enter a name for the routine, then press the Save Routine button.
Routine Setup: Configure Data Fields
Once the routine has been created, each of the data fields in the text file need to be defined against the routine, based on the the column names from the first line of the text file. You will be mapping the Accounting Connector's fields as "source fields" to the text file "destination fields" for product delivery lines.
- In the Data Fields tab, click on the Add Data Field button. A new data field row will get created.
- For the new row, set the Destination Field to have your chosen label for the header field in the text file.
- In the Destination Field Label set a meaning value that describes the source field.
- In the Source Field, choose a field from the connector's order to map to the destination field.
- Continue steps 1 to 4 until all relevant columns from the text file have been listed.
Notes:
- To set a unique ID for data in the delivery file, map the deliverFileID source field to your chosen destination field. The delivery File ID data is set to the date-time of when the routine runs, in the format YYYYMMDDhhmmss eg. 20140123163215
Routine Setup: Configure Settings
- Click on the Settings tab of the routines.
- For each row in the settings table, click on the Setting Value field.
- Enter a setting value, then click to the next row. The setting will automatically save once the cursor exits the field.
Notes:
- In the Routine Settings section below describes what each of the settings does. Use this to configure the routine.
- If a red icon appears in the Setting Value cell, then the cell does not accept the given input. You will need to change the value in the cell to be valid.
Routine Settings
Below are the settings that can be configured for the routine.
| Setting label | Allowed Values | Description |
|---|---|---|
| Deliveries Text File Folder Location | Allowed Filename Characters | Directory in the filesystem where product delivery text files are saved to. |
| Deliveries Audit Text File Folder Location | Allowed Filename Characters | Directory in the filesystem where product delivery text files are saved to. This secondary location can be useful if delivery files will be moved from the Deliveries Text File Folder Location. |
| Send Delivery Email Notification | Values: true or false | Sends out an email notification containing results of the routine running. If true, ensure that the "Notification Email Address" setting contains an email address. Also ensure that the connector's SMTP property has been configured. |
| Notification Email Address | All Valid Email Characters | Email address to send notifications to once the routine has been completed. Emails will only be sent to the address if the "Send Order Import Notification" is set to true. |
| Prefix text in the receipt filename | Allowed Filename Characters | Text to place at the front of the filename of the generated delivery file. |
| File extension for the delivery file | Allowed Filename Characters | File extension to append to the delivery file name. Typically this would be set as CSV, or TXT. |
| Data field enclosing character | All Characters |
Set the character(s) used the enclose around the field data for the delivery text file. |
| Data field escaping character | All Characters | Character(s) used the escape enclosing characters within the field data for the delivery text file. Set t for tabs, n for line-feeds, r for carriage returns. |
| Data Field Delimiter | All Characters |
Set the character(s) used to delimit field data in the delivery text file. These character(s) denote one data field value from another. |
| Data Row Delimiter | All Characters | Set the character(s) used to delimit row data in the text file. These character(s) denote one line from another. Set t for tabs, n for line-feeds, r for carriage returns. |
| Prefix text in the delivery file ID | Allowed Filename Characters | Set text that is placed infront of the delivery field ID. The delivery file ID contains a unique identifier for the delivery file and is used in the delivery file name, and optionally in the file data. |
Diagram showing the settings that dictate how text file data is parsed.
Routine Data Fields
Listed below are each of the source data fields that you can map order product delivery data to the destination fields of the text file.
| Destination Field Label | Destination Field | Description |
|---|---|---|
| Invoice Date | invoiceDate | Date of the Invoice that the ordered product line quantity was set against. |
| Invoice Accounting Account ID | invoiceAccountingAccountID | ID of the debtor number stored against the invoice matching the ordered product line. |
| Order Code | orderCode | Code of the order. Typically contains the order ID and any prefix or suffix text. |
| ID of the product in the invoice | invoiceProductItemID | Inventory Item number against the Micronet invoice which matches the order product line. |
| Invoice unit quantity ordered for the product | invoiceProductQuantityOrdered | Quantity ordered against the Micronet invoice line matching the order product line. |
| Invoice unit quantity delivered for the product | invoiceProductQuantityDelivered | Quantity marked as delivered against the Micronet invoice line matching the order product line. |
| Invoice unit quantity backordered for the product | invoiceProductQuantityBackordered | Quantity marked as back-ordered against the Micronet invoice line matching the order product line. |
| Order ID | orderID | ID to assign against the order. |
| Purchase Order Number | orderPONumber | Number against the purchase order that the order relates to. |
| Purchase Order Line Number | orderProductPurchaseOrderLineID | Number against a product line of the purchase order that the order relates to. |
| ID of the purchasers product | orderProductPurchaseOrderItemID | ID against a product item in the purchase order that the order relates to. |
| Invoice ID | Invoice ID | ID of the Micronet invoice that the order product line has been found in. |
| Date of the delivery record against the ordered product | orderProductDeliveryDate | Date that the delivery record was generated against the ordrred product. |
| Quantity set for the delivery record against the ordered product | orderProductDeliveryQuantity | Quantity in the delivery record was generated against the ordered product, based on the quantity found against the Micronet invoice. |
| Delivery code set for the delivery record against the ordered product | orderProductDeliveryCode | Delivery code in the delivery record was generated against the ordered product. Currently not set since Micronet has no such code. |
| Delivery line code set for the delivery record against the ordered product | orderProductDeliveryLineCode | Delivery line code in the delivery record was generated against the ordered product. Currently not set since Micronet has no such line code. |
| Invoice code set for the delivery record against the ordered product | orderProductDeliveryInvoiceCode | Invoice code in the delivery record was generated against the ordered product. Based on the invoice ID found against the order product line. |
| Invoice line code set for the delivery record against the ordered product | orderProductDeliveryInvoiceLineCode | Quantity in the delivery record was generated against the ordered product, based on the quantity found against the Micronet invoice. |
| Date-time when the delivery file is being generated | deliveryFileDateTime | Date time that the delivery file was generated. The format of the date can be configured by set a format string in the Data Length column. Refer to MSDN Custom Date and Time Format Strings for setting the date string format. |
| Unique ID used in the delivery file and filename | deliveryFileID | Unique ID generated for the delivery file. Based on the date-time that the file was generated in the format, YYYYMMDDhhmmss eg. 20140224182455. The ID includes the prefix configured in the routine settings. |
Routine Message Meanings
Listed below are a majority of the messages that are generated by the routine, either through the connector logs, or in the sent email notification.
|
Type |
Message | Meaning |
|---|---|---|
| Error | The adaptor routine was not found or could not be run | A error has occurred that has caused the routine not to run as it should. This error occurs when no other errors have been handled. Advised us if this occurs. |
| Error | Unable to run "routine code" due to settings that cannot be found. Please notify us about this issue. | The error occurs when the settings for the routine could not be found. This is most likely to do with a update issue when the connector was last changed. Advise us if you see this message. |
| Error | Unable to run "routine code" routine due to the order delivery text file directory not existing or not having permissions to be viewed. | When the routine tried to find the directory where the delivery files are saved to, either it did not have permission to view the directory, or the directory does not exist. Look at the routine settings and/or the permissions that windows has for users on the directory. |
| Error | Unable to run "routine code" routine due to the order delivery auditing text file directory not existing or not having permissions to be viewed. | When the routine tried to find the audit directory where the delivery files are saved to, either it did not have permission to view the directory, or the directory does not exist. Look at the routine settings and/or the permissions that windows has for users on the directory. |
| Error | An error occurred while trying to run "routine code" routine, the routine has been aborted. No fields have been defined for the delivery text file. | The error occurs when no data fields have been created for the routine. Go into the routine and create data fields that map the delivery text file fields to connector order/product/delivery fields. |
| Error | An error occurred while trying to run "routine code" routine, the routine has been aborted. The details of an undelivered order could be obtained from the connector. | The error occurs when the routine could not obtain orders from the accounting connector. This most likely occurs if the connector's data file is locked, missing or unable to be accessed. |
| Error | No details of an undelivered order could be obtained from the connector | The error occurs when the routine could not obtain order data from the accounting connector. This error most likely occurs if the connector's data file is locked, missing or unable to be accessed. |
| Error | An error occurred while trying to run "routine code" routine, the routine has been aborted. No undelivered orders could be obtained from the connector | The error occurs when the routine could not obtain orders from the accounting connector. This error most likely occurs if the connector's data file is locked, missing or unable to be accessed. |
| Error | No undelivered orders could be obtained from the connector | The error occurs when the routine could not obtain orders from the accounting connector. This error most likely occurs if the connector's data file is locked, missing or unable to be accessed. |
| Error | No invoices exist in Micronet for the order | When the routine looked for invoices in Micronet that contained the order code of a given order, if no invoices existed then this error message would occur. Check that picking slips or back orders exist for the specified order. |
| Error | Unable to include order products to the order delivery file since no Micronet invoices exist for an order | When the routine looked for invoices in Micronet that contained the order code of a given order, if no invoices existed then this error message would occur. Check that picking slips or back orders exist for the specified order. |
| Error | Unable to obtain invoices from Micronet that correspond with the order | When the routine looked for invoices in Micronet that contained the order code of a given order, the ODBC query to Micronet produced an error. Check that Micronet is running and that ODBC access contain be obtained through the adaptor's configured settings. |
| Error | Unable to generate a delivery file since the invoiced quantities for an order are more than the ordered amount for a product | The error occurs when the routine found invoice(s) against a given order in Micronet, but the invoice(s) contain line(s) with a total quantity that is greater than the ordered quantity for a product line in the connector. This error can occur if the order quantities have been increased in Micronet and then invoiced. You will need to change the order quantity for the product line in the connector to stop this message from occurring. |
| Error | Unable to copy the delivery file from the audit directory to the delivery directory | The error occurs when the generated delivery file could not be copied from the audit directory to the delivery directory. This could occur if the delivery directory does not exist, or Windows user permissions lock the directory from being modified. Check the routine settings and folder permissions. |
| Error | Unable to save the delivery file to the audit directory | The error occurs when the generated delivery file could not be saved to the audit directory. This could occur if the audit directory does not exist, or Windows user permissions lock the directory from having files created in it. Check the routine settings and folder permissions. |
| Error | Unable to generate invoice line data for the delivery file | The error occurs when the delivery file was attempting to be generated but could not be. This can occur if the routine's data fields do not have correct Data Type and Data Length fields set. |
| Error | Unable to generate data for the delivery file | The error occurs when the delivery file was attempting to be generated but could not be. This can occur if the routine's data fields do not have correct Data Type and Data Length fields set. |
| Info | No new ordered quantites could be found for the order in Micronet invoices | If no delivery data was to be saved to the delivery file, then this message will be displayed. This can occur if all orders in the connector are marked as having being delivered, or if no Micronet invoices could be found having product lines that relate to the original orders. |
| Info | No new delivery quantites exist, so no delivery file will be generated | If no delivery data was to be saved to the delivery file, then this message will be displayed. This can occur if all orders in the connector are marked as having being delivered, or if no Micronet invoices could be found having product lines that relate to the original orders. |
| Error | Delivery file generated but not tracked in connector | The error can occur if the delivery file was successfully generated however the generated product delivery records could not be saved to the connector. This most likely occurs if the connector's data file is locked, missing or unable to be accessed. |
| Error | Unable to record delivery quantities against the ordered products in the connector | The error can occur if the delivery file was successfully generated however the generated product delivery records could not be saved to the connector. This most likely occurs if the connector's data file is locked, missing or unable to be accessed. |
| Error | Unable to add product delivery records to the order | The error can occur if the delivery file was successfully generated however the generated product delivery records could not be saved to the connector. This most likely occurs if the connector's data file is locked, missing or unable to be accessed. |
| Error | Unable marked an order as having all its products delivered, please manually do this in the connector | The error occurs after the delivery file was successfully generated, and all the products of an order have been marked as delivered, however the routine could not update the Products Delivered status against the order in the connector. This most likely occurs if the connector's data file is locked, missing or unable to be accessed. Find the order in the connector and changed the Products Delivered status. |
| Error | Unable marked an order as having all its products delivered, please manually do this in the connector | The error occurs after the delivery file was successfully generated, and all the products of an order have been marked as delivered, however the routine could not update the Products Delivered status against the order in the connector. This most likely occurs if the connector's data file is locked, missing or unable to be accessed. Find the order in the connector and changed the Products Delivered status. |
| Info | Routine succesfully generated out a delivery file | A delivery file was successfully generated and saved to the filesystem, as well as having the order updated in the connector. |
| Info | Delivery file successfully generated | A delivery file was successfully generated and saved to the filesystem, as well as having the order updated in the connector. |
| Info | An order-products delivery text file has been successfully generated from the adaptor routine: "routine code" | Indicates that the delivery file was successfully generated and saved to the file system. |