xTuple Connect predefines several EDI Profiles. These are referred to as System EDI Profiles and are used for handling special uses of xTuple Connect:
Sending email for Quotes
Sending email for Incidents
Running ad-hoc database queries with the Batch Manager
The xTupleQuoteEmail EDI Profile is a good example of a reusable EDI Profile—it can be used to send a Quote to almost any Prospect or Customer. In fact, that is what it is for.
The xtConnect extension package handles Quotes a little differently than Sales Orders, Invoices, and Purchase Orders. If xTuple Connect is enabled, every time you print a Quote and the Quote's bill-to email address is not empty, EDI will be sent. If the Customer or Prospect has an EDI Profile attached, that EDI Profile will be used. Otherwise the xTupleQuoteEmail profile will be used. For the other Document Types you must explicitly attach an EDI Profile to the Customer or Vendor to enable sending EDI.
The reasoning behind this feature is that Quotes are often generated when first establishing a business relationship. You may not have had the opportunity to enter full details about how to work with this new Prospect or Customer. Therefore, xTuple Connect makes it easy to send Quotes electronically, starting with the very first time you interact with this Prospect or Customer.
xTuple Connect can be set up to send email whenever Incidents are changed within xTuple ERP. The xTupleIncidentEmail System EDI Profile is another reusable EDI Profile. Using xTuple Connect for Incident management, including the xTupleIncidentEmail EDI Profile, is discussed in detail in Chapter 6, Connecting with Incidents.
MetaSQL is an extension of the SQL database query language. You can find documentation for it on the xTuple web site at http://www.xtuple.org/RptAdvancedTopics.
The MetaSQL editor embedded in xTuple ERP allows creating, editing, and running ad-hoc database queries within the xTuple ERP application. xTuple Connect extends this capability to allow sending MetaSQL statements to the xTuple Connect Batch Manager to be run. Once submitted, these jobs can be scheduled to recur (see Section 3, “Scheduling Recurring Jobs” in Chapter 3, Connect Inside Your Company).
Individual MetaSQL statements can be grouped together into Query Sets. There is an introduction to Query Sets in Section 3, “Sending CSV Files”. They are discussed in more detail in Section 7.3.2, “Query Sets in xTuple Connect”. Query Sets can also be submitted to the xTuple Connect Batch Manager and scheduled to recur.
Both Query Sets and individual MetaSQL statements get submitted to the xTuple Connect Batch Manager using the EDI infrastructure. There are two System EDI Profiles for this purpose, one for individual MetaSQL statements and another for Query Sets.
When the xTuple Connect package is enabled, a SUBMIT button appears on the MetaSQL Statements screen. This allows you to submit any of the shared MetaSQL statements to the xTuple Connect Batch Manager. Follow these steps to do so:
Follow this path:
Select a MetaSQL statement from the list
Click the SUBMIT button
The screen should look similar to this:
Submitting a MetaSQL Statement to the Batch Manager
Set the type and value of each of the parameters in the list in turn (see Section 7.3.1.1, “Active vs. Inactive MetaSQL Parameters”):
Select a parameter from the list at the top of the Review EDI Before Sending screen
Click the EDIT button
The screen should now look like this:
Sample Parameter Editor
Choose an appropriate data type from the "Type" list. This will help ensure that the value you enter is valid
Click the "Active" box if you want to send it to the MetaSQL statement
Note
You usually want the parameter to be active. See Section 7.3.1.1, “Active vs. Inactive MetaSQL Parameters” for details.
Enter the desired value for the parameter
Click the ACCEPT button
Many MetaSQL statements are written to behave in special ways if specific parameters are active or not. For example, many screens let you choose to see Items with all Class Codes, just some Class Codes, or a particular Class Code. These work by having queries that check whether certain MetaSQL parameters are active or not:
SELECT ...
FROM item, ...
WHERE ...
<? if exists("classcode_id") ?>
AND (item_classcode_id=<? value("classcode_id") ?>)
<? elseif exists("classcode_pattern") ?>
AND (item_classcode_id IN (SELECT classcode_id
FROM classcode
WHERE (classcode_code ~ <? value("classcode_pattern") ?>)))
<? endif ?>
...In this example, when the user selects a particular Class Code, the screen makes the classcode_id parameter active. The query then looks only at items with the selected Class Code. If the user enters a Pattern, then the screen makes the classcode_pattern parameter active and all Items with Class Codes matching that pattern are displayed. If neither classcode_id nor classcode_pattern is active, there is no comparison with any Class Code and so all Class Codes are displayed.
If you are not going to make a MetaSQL Parameter active, you do not need to set the type or value for it before submitting the MetaSQL Statement to the xTuple Connect Batch Manager.
The EDI Profile used by the SUBMIT button on the MetaSQL Statements screen is fixed—it always uses the Profile xTupleMetaSQLEmail. This profile has the following properties by default:
It always sends email
The email gets sent to the email address of the user who clicked the SUBMIT button
The user is given a chance to review the EDI before it is submitted, which is required so the user can set values for the MetaSQL Parameters
The output is in CSV format
Submitting MetaSQL statements to the xTuple Connect Batch Manager always uses the xTupleMetaSQLEmail profile. You can edit this profile, but be aware that it may change in future releases and your edits will be lost. By default this profile sends email to the xTuple ERP user who submits the job.
Query Sets, which are collections of MetaSQL statements, can be created and edited in the Export Data screen () and the EDI Form screen as described in Section 3, “Sending CSV Files”. See the xTuple ERP Reference Guide for full documentation of the Export Data screen.
xTuple Connect adds a SUBMIT button to the Export Data screen. This button lets you send the Query Set to the xTuple Connect Batch Manager to be run. All Query Sets are run using the xTupleQuerySetEmail System EDI Profile.
The xTupleQuerySetEmail is similar to the xTupleMetaSQLEmail profile. The primary difference is that the default output format for Query Sets is XML. An XML file containing the results of multiple queries is easier to understand than a CSV file with the same data. This is because the CSV file may not contain header lines and, even if it did, these are easily lost because they are mixed in with the data. XML files, on the other hand, have individual tags for each data field and for each row, so the file describes itself—as long as the names of the query set, queries, and result columns are chosen well.
Here is an example of submitting a Query Set to the xTuple Connect Batch Manager:
Select a Destination Format
Create a Query Set if one does not already exist for your needs
Select the Query Set
Set values for the MetaSQL parameters if necessary
Click SUBMIT
The Review EDI Before Sending screen should appear
Click ACCEPT
Once submitted, it can be rescheduled to run on a regular basis:
Select the appropriate line from the list of jobs. Look for a line with "EmailXML" in the Action column and "Document: QSET name-of-your-queryset" in the Detail column
Click RESCHEDULE
Set the Recurring properties
Click SAVE
When crafting the queries for recurring execution, keep in mind that the values of the MetaSQL parameters are fixed. They do not change from one run of the query to another. Therefore, write your recurring queries to use as few MetaSQL parameters as possible and to use relative information instead of fixed. Here is an example of a problem and 3 solutions:
- The problem
Automatically explode Work Orders on a regular basis
- Bad solution: Explode a particular Work Order number passed in by the user
SELECT wo_number, explodeWo(wo_id, true) AS explodedStatus FROM wo WHERE wo_number = <? value('wo_number') ?>;This is a bad solution because the user has to pass a particular Work Order. If this job is set to recur, the same Work Order will be exploded repeatedly. This will work the first time but fail every other.
- Bad solution: Explode everything due by July 25, 2010
SELECT wo_number, explodeWo(wo_id, true) AS explodedStatus FROM wo WHERE wo_duedate <= '2010-07-25';
This is a little better, because at least this will explode newly created orders for the first few times it runs. However, it will return one row for every Work Order that is due before July 25, whether it needs to be processed or not, and it will stop doing anything useful at all after that date.
- Good solution: Explode everything due in the next 7 days
SELECT wo_number, explodeWo(wo_id, true) AS explodedStatus FROM wo WHERE wo_status='O' AND wo_duedate <= CURRENT_DATE + CAST('7 days' AS INTERVAL);This is a much better solution: it restricts the Work Orders to be checked to just those ready to be exploded and it has a moving seven day window.