Support for M3 EXPORTMI API

EXPORTMI is an M3 API that allows directly querying M3 tables. The EXPORTMI API accepts a query as input and generates delimited text containing query results as output. SCV now supports calling EXPORTMI API from SCP.

SCV BOIs for M3 have been extended to support calling the EXPORTMI API. The <QUERY_VALUE> new tag is added to the BOI structure that allows the user to provide the details to build the required query on the M3 table. The <QUERY_VALUE> tag has this structure:

<QUERY_VALUE>
 
    <TABLE></TABLE>
 
    <TABLE_FIELDS>
        <TABLE_FIELD></TABLE_FIELD>
    </TABLE_FIELDS>
 
    <FILTER_CONDITION></FILTER_CONDITION>
 
    <REF_FILTER>   
        <TABLE_FIELD TYPE="" OPERATOR=""></TABLE_FIELD>
        <QUERY></QUERY>   
    </REF_FILTER>
 
    <INCREMENTAL_FILTER>
        <TABLE_FIELD></TABLE_FIELD>
        <DATE_VALUE></DATE_VALUE>
    </INCREMENTAL_FILTER>
 
    <SOURCE_FILTER>   
        <TABLE_FIELD TYPE="" OPERATOR=""></TABLE_FIELD>
        <QUERY></QUERY>   
    </SOURCE_FILTER>
 
</QUERY_VALUE>

Where:

<TABLE>: Name of the M3 table to query (Mandatory). For example, MITMAS
<TABLE_FIELDS>: List of selected columns. Each <TABLE_FIELD> contains the full column name. At least one column must be specified. All PK columns of the M3 table must be included. For example, MMITNO.
<FILTER_CONDITION>: Free-form filter condition on the specified M3 table. It must be a valid condition using M3 query syntax (where, for example, only "and" and "or" are supported and all keywords are expected in small letters).
<REF_FILTER>: Special filter to handle references between tables.
<TABLE_FIELD> specifies a reference to another M3 table. For example, reference to the parent table in a child table. It allows reading the children of a parent table. In most cases there is one REF_FILTER, but there can be multiple references that requires to be used in the query as filter condition. In this case, all reference values are used as filter condition with ’and’.
For a <TABLE_FIELD>, TYPE attribute can be used for explicit type casting. Possible values for TYPE attribute are DATE, TIME, TEXT, NUMERIC. Default is TEXT. For any other data type required by M3, TYPE must be explicitly mentioned. Otherwise, the passed value is converted to string using SQL Server defaults.
Note:
- For dates, the format is YYYY-MM-DD and this format fails in M3. If TYPE=”DATE” is used, YYYYMMDD is passed to M3 as string. For a <TABLE_FIELD>, OPERATOR attribute can be used for filter operators. Possible values for OPERATOR attribute are EQ (equal to), NEQ (not equal to), GT (greater than), GE (greater than or equal to), LT (less than), LE (less than or equal to). Default is EQ.
- Care must be taken when using an operator other than EQ. Specifying ranges may result in large amounts of data to be selected from the M3 table and cause timeouts at run time.
<INCREMENTAL_FILTER>: Special filter to handle incremental imports. <TABLE_FIELD> specifies the timestamp column in the M3 table that holds the last modification time as EPOCH value.
<DATE_VALUE> specifies the actual value used as the filter value against <TABLE_FIELD>. It allows specifying the WINDOW_START keyword or a constant date value and converting that to EPOCH value.
<SOURCE_FILTER>: Special filter to handle processing by source.
<TABLE_FIELD> specifies the column to use when applying the source filter. This is mainly the location column in the M3 table, such as warehouse or facility. There can be only one SOURCE_FILTER. For a <TABLE_FIELD>. TYPE and OPERATOR attributes can be used. Usage of these attributes are the same as in REF_FIELD.

The M3_EXECUTE_IMPORT SCV program is extended to process the new <QUERY_VALUE> tag in the BOIs. The program builds the input query as follows using the specified <QUERY_VALUE>:

Add selected columns and the table: <TABLE_FIELD 1>, <TABLE_FIELD 2>, ... <TABLE_FIELD n> from <TABLE>
- If available, add filter conditions: where <FILTER_CONDITION>
- If available, add the reference filters: and <TABLE_FIELD> = <TABLE_FIELD value>, for each <TABLE_FIELD value> returned by the <QUERY>
- If available, add the incremental filter: and <TABLE_FIELD> = <DATE_VALUE>, converted to EPOCH
- When <SOURCE> is specified, add the source filter: and <TABLE_FIELD> = <TABLE_FIELD value>, for each <TABLE_FIELD value> returned by the <QUERY>
- If multiple values are returned by the reference filter or source filter queries, call EXPORTMI-select once for each combinations of the filter values.
When adding the reference and source filters, determine if an additional filter condition is required:
- If the #SOURCE# keyword already exists in the query, add no additional filter condition.
- If the #SOURCE# keyword does not exist in the query, add an additional filter condition. WHERE source = #SOURCE# (or, and source = #SOURCE# if the WHERE clause already exists.
- Replaces #SOURCE# keyword with <SOURCE>
When adding the reference filters and source filters, determine the column data types from the specified TYPE attribute:
- If <TABLE_FIELD TYPE="TEXT">, wraps the value with quotes: <TABLE_FIELD> = 'text value'
- If <TABLE_FIELD TYPE="NUMERIC">, do not wrap the value with quotes: <TABLE_FIELD> = numeric value
- If <TABLE_FIELD TYPE="DATE">, formats the value as YYYYMMDD and wraps it with quotes: <TABLE_FIELD> = 'YYYYMMDD'
- If <TABLE_FIELD TYPE="TIME">, format the value as HHMMSS and wrap it with quotes: <TABLE_FIELD> = 'HHMMSS'
When adding the reference filters and source filters, determine the filter operator from the specified OPERATOR attribute:
- If <TABLE_FIELD OPERATOR="EQ">, use "=" as the operator: <TABLE_FIELD> = <TABLE_FIELD value>
- If <TABLE_FIELD OPERATOR="NEQ">, use "!=" as the operator: <TABLE_FIELD> != <TABLE_FIELD value>
- If <TABLE_FIELD OPERATOR="GT">, use ">" as the operator: <TABLE_FIELD> > <TABLE_FIELD value>
- If <TABLE_FIELD OPERATOR="GE">, use ">=" as the operator: <TABLE_FIELD> >= <TABLE_FIELD value>
- If <TABLE_FIELD OPERATOR="LT">, use "<" as the operator: <TABLE_FIELD> < <TABLE_FIELD value>
- If <TABLE_FIELD OPERATOR="LE">, use "<=" as the operator: <TABLE_FIELD> <= <TABLE_FIELD value>

Note: The EXPORTMI API supports querying one M3 table at a time; joins between multiple tables are not supported. If one M3 table must be queried based on values in another M3 table, both M3 tables must be imported to SCV using separate staging tables. When the first staging table is populated, the second one can be imported from M3 using the REF_FILTER construct. In this case, one separate query is generated and EXPORTMI run for each distinct value in the first staging table.

Similarly, the IN statement available in standard SQL is not supported by EXPORTMI API. If one M3 table must be queried to retrieve all records where a specific column value is one of the required values, the M3 Import program generates one separate query and run EXPORTMI for each required column value.

When combined with queries simulating IN statements, specifying ranges using the OPERATOR attribute may result in running multiple queries with overlapping ranges, which in turn results in both large amounts of data to be retrieved from M3 and potentially duplicated records to be inserted into the SCV staging table.

The M3_EXECUTE_GENERATE_STAGING_ENTITY SCV program is extended to process the new <QUERY_VALUE> tag in the BOIs. When EXPORTMI API is used in a BOI, the program generates the entity definition XML for the corresponding staging table using the metadata related to the M3 table specified in the BOI. In addition, the PK columns of the M3 table are identified using the index 00 of the M3 table, and the corresponding SCV PK constraint definition on the PK columns is included in the XML. As a result, all SCV import staging tables corresponding to M3 EXPORTMI API tables have a PK constraint. A PK constraint on the staging table allows users to keep all imported data in the staging tables and merge newly retrieved changed records into the staging table using the PK on the table.

Note: By default this feature is enabled. A new role or privilege is not required to access this feature.