Program logic

The M3_EXECUTE_IMPORT program determines the processing sequence for multiple BOIs in scope as follows:

Generates the dependency tree for the BOIs in scope using the references between BOIs.

Determines the processing sequence:

If TARGET_TABLE_GROUP_NAME is specified  
    If processing_sequence is defined for all tables in the specified table group, the processing_sequence from the table group is used in ascending order.   
Else the BOIs in scope are processed using the generated dependency tree: 
    BOIs with no dependency or BOIs depending only on already processed BOIs are processed first

Validates each BOI in scope:
- If the valid_flag is set to N or Null, the program validates the BOI and updates the valid_flag accordingly.
- If validation fails, an error is logged, and execution is stopped.
- If validation passes or valid_flag is set to Y, the program continues with BOI execution.
Determines which BOI definition to use. Possible conditions:
- If sc_indicator is set to S and user_boi_xml is not specified, the definition in boi_xml is considered.
- If sc_indicator is set to S and user_boi_xml is specified, the definition in user_boi_xml is considered.
- If sc_indicator is set to C, the definition in user_boi_xml is considered.

Calls the snapshot manager to start a new snapshot using these parameters:

p_execution_id = <current execution id>   
p_snapshot_mode = <IMPORT_MODE>   
p_partial_snapshot_start_time = <PARTIAL_IMPORT_START_TIME>   
p_source = <SOURCE>   
p_source_module = "M3"   
p_table_name = <TARGET_TABLE_NAME>   
p_table_group_name = <TARGET_TABLE_GROUP_NAME>   
p_table_area = <TARGET_TABLE_AREA>   
p_utc_time_flag = "Y"

Executes each <current BOI> in scope as follows:

Logs a new task record using the <program name>:<current BOI> format. For example, M3_EXECUTE_IMPORT:CRS610_LstByNumber.
Retrieves a snapshot window for <current BOI> by calling the snapshot manager using these parameters:
```
p_snapshot_id = <current snapshot id>
p_table_name = <current BOI>.<STAGING_TABLE>
```

Deletes target table based on TARGET_DELETE_MODE:

If TARGET_DELETE_MODE = "None", does not delete any records from target table  
If TARGET_DELETE_MODE = "Physical", deletes all records from target table <current BOI>.<STAGING_TABLE> for the specified <SOURCE>

Executes the M3 program-transaction using metadata in <current BOI> and the snapshot window. This logic uses all input parameters as specified in <current BOI>. For example:

If M3 program-transaction != "EXPORTMI-Select":

When <SOURCE> is specified:  
   If <REF_TABLE> is specified, adds the source filter as a where condition: WHERE source = #SOURCE#   
   If <REF_QUERY> is specified
   	If the #SOURCE# keyword already exists in the query, adds no additional filter condition
	If the #SOURCE# keyword does not exist in the query, adds an additional filter condition: WHERE source = #SOURCE# (or, and source = #SOURCE# if the WHERE clause already exists)
	Replaces #SOURCE# keyword with <SOURCE>

If M3 program-transaction = "EXPORTMI-Select":

Builds the input query as follows using the specified <QUERY_VALUE>:
    If <INDEX> is not available then
        When <TABLE_FIELD> is "*", adds all columns and the table: * from <TABLE>    
        Otherwise, adds selected columns and the table: <TABLE_FIELD 1>, <TABLE_FIELD 2>, ... <TABLE_FIELD n> from <TABLE>  
    If <INDEX> is specified then    
        Adds selected columns and the table: <TABLE_FIELD 1>, <TABLE_FIELD 2>, ... <TABLE_FIELD n> from <TABLE><INDEX>  
    If available, adds filter conditions: where <FILTER_CONDITION>  
    If available, adds the reference filters: and <TABLE_FIELD> = <TABLE_FIELD value>, for each <TABLE_FIELD value> returned by the <QUERY>    
    If available, adds the incremental filter: and <TABLE_FIELD> = <DATE_VALUE>, converted to EPOCH    
    When <SOURCE> is specified, adds 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, calls EXPORTMI-Select once for each combinations of the filter values   

When adding the reference filters and source filters, determines if an additional filter condition is needed:  
    If the #SOURCE# keyword already exists in the query, adds no additional filter condition
    If the #SOURCE# keyword does not exist in the query, adds 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, determines 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">, does 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">, formats the value as HHMMSS   and wraps it with quotes: <TABLE_FIELD> = 'HHMMSS'    

When adding the reference filters and source filters, determines the filter operator from the specified OPERATOR attribute:  
    If <TABLE_FIELD OPERATOR="EQ">, uses "="  as the operator: <TABLE_FIELD> =  <TABLE_FIELD value>   
    If <TABLE_FIELD OPERATOR="NEQ">,uses "!=" as the operator: <TABLE_FIELD> != <TABLE_FIELD value>   
    If <TABLE_FIELD OPERATOR="GT">, uses ">"  as the operator: <TABLE_FIELD> >  <TABLE_FIELD value>   
    If <TABLE_FIELD OPERATOR="GE">, uses ">=" as the operator: <TABLE_FIELD> >= <TABLE_FIELD value>   
    If <TABLE_FIELD OPERATOR="LT">, uses "<"  as the operator: <TABLE_FIELD> <  <TABLE_FIELD value>   
    If <TABLE_FIELD OPERATOR="LE">, uses "<=" as the operator: <TABLE_FIELD> <= <TABLE_FIELD value>

Retries API calls if an M3 program-transaction fails:

Calls the failed API once more with the same input parameters when   

    <RETRY> is specified, and  
    the error code returned from M3 program-transaction = <RETRY>.<RETRY_ON_ERROR> or the error description returned from M3 program-transaction contains <RETRY>.<RETRY_ON_ERROR>, and   
    the current retry count is less than or equal to <RETRY>.<RETRY_MAX_NUMBER>

If the retry itself fails, waits for <RETRY>.<RETRY_FREQUENCY> duration before making the subsequent retry call to the M3 program-transaction.

Updates <current BOI>.<STAGING_TABLE> based on TARGET_UPDATE_MODE:
- Insert: Adds all returned records to the staging table.
- Merge: Combines imported with existing records using the Primary Key (PK) definition on the staging table. If PK definition is not defined, an error is logged.
  Note:
  - When merging data, the program processes only the records imported from M3.
  - The columns in the <current BOI>.<STAGING_TABLE> table, corresponding to the output fields of the M3 program-transaction, are only populated.
  Updates the values of these columns in the <current BOI>.<STAGING_TABLE> table:
```
source = If <SOURCE> is specified then <SOURCE>, else default source value <M3 company>:<M3 division>   
created_by = <import program>:<execution id>  
creation_time = <Current timestamp in UTC>  
modified_by = <import program>:<execution id>  
modification_time = <Current timestamp in UTC>  
```

Encounters an unexpected error during import, if exist:

- If the error type is "InternalError" with "internal server error" message:
    Re-tries the same call after 1 second. If the call is successful then the flow continues. If the first re-try also fails with "InternalError", re-tries the same call for the second time after 5 seconds. If the call is successful then the flow continues.    
- If the second re-try call fails with "InternalError" or if any other error is encountered, the M3\_EXECUTE\_IMPORT program logs error message and returns with error.

Completes the snapshot for the <current BOI>.<STAGING_TABLE> table by calling the snapshot manager using these parameters:

p_snapshot_id = <current snapshot id>
p_table_name = <current BOI>.<STAGING_TABLE>
p_source = <SOURCE>   
p_end_status = <current status>. If the execution of the M3 program-transaction completed with success, 
  then "Success". If any errors occurred during the import, then "Failure".

Completes the task log record with the result of the M3 API call.
Continues with the next BOI in scope and determines the BOI definition and calls the snapshot manager.

Completes the snapshot for the current import using this parameter:
```
p_snapshot_id = <current snapshot id>  
```
Logs execution details in SZ_EXECUTION_LOG.