When the service/job runs in the background, the following sequence of operations takes place:
-
Loading data into staging table
-
Transforming data from staging table to raw table
-
Storing Account Codes in Smart Materials using raw table
Loading Data into Staging Table
Before loading the data into staging table, the service reads projects from product group defined in DBA Setting ECO_PRODUCT_GROUP. Criteria for choosing a project is that the value of the project default ZI_E_ACSER is set to Y (Account Code service is enabled for integration) and project default ZI_E_PROJ is properly set for the project.
For instance, consider the following project default values:
-
ZI_E_SURL: http://{host}/ecosys/api/restxml/
-
ZI_E_UN: username
-
ZI_E_PW: *****
-
ZI_E_PROJ: TRG-01
-
ZI_E_ACAPI: ApprovedBudgetDetails/?Project={ZI_E_PROJ}
Project is the parameter name which the API expects, so append Project to the API name as shown above. The value of that parameter is a dynamic value, it is specified within placeholders as another project default ZI_E_PROJ.
To know more details, contact our support team.
The corresponding API is invoked to get the response.
The data from the response is inserted into the staging table M_ECO_ACT_STAGINGS as shown below.
Transforming Data from Staging Table to Raw Table
The service first checks for mandatory attributes (specified through DBA setting ECO_ACC_UK) in staging table for every set of records. If they are not present, those set of records will not be considered to transform into raw table M_ECO_ACT_STAGING_RAW. The table M_ECO_ACT_STAGING_RAW is created whose structure is similar to the structure of published API (that is, with the Attributes present in API).
After processing all records, the service transforms set of records (with same Record ID) from the staging table to raw table M_ECO_ACT_STAGING_RAW as a single record with all the required attributes as columns as shown below.
The record is inserted into the raw table if there was no record previously with the unique combination or is updated if there is a record with that unique combination.
If multiple rows are found for the unique combination specified in Attribute Set
of DBA setting ECO_ACC_UK, only a single row is retained in the table by deleting
the duplicate rows.
Storing Account Codes in Smart Materials
The service finally inserts the account codes into M_JOBS table by fetching the data from the raw table.
For storing the account codes, a new Account Code structure is to be created and the attributes are to be added under that structure. The name of that structure must be specified with the project default ZI_E_ACSTR. If that Account Code Structure already exists, the account codes are stored into that structure, or else new account code is created with defined name. If no value is specified in that project default, the service stops executing further.
If the attributes under the Account Code Structure don’t match with the attributes from both attribute sets defined by DBA settings ECO_ACC_UK and EC_ACC_ATTR_SET, they are deleted and then all attributes of the attribute sets defined by the DBA settings are added to the above created Account Code structure.
Since only seven attributes can be added under an Account Code Structure, even if
more than seven attributes are specified in AttributeSets, only the first seven attributes
are added to the Account Code Structure.
Then service calls the M_PCK_ECOSYS_CUSTOM CIO to get the format and the required columns for the formation of Account Code in Smart Materials. The CIP also provides the column names from where the values of BUDGET, CURRENCY, SHORT_DESCRIPTION, and DESCRIPTION can be fetched from EcoSys API. The Account Code is compared with the existing records in M_JOBS table and is inserted or updated based on the presence of that Account Code previously.
If an Account Code is transferred for the first time, the Planned Budget field in J.10.03 would be filled for that Account Code and 0 (zero) would be entered in Revised Budget. For all further transfers, the Revised Budget would be updated.
Finally, data from the fields specified in CIP for SHORT_DESC and DESCRIPTION is inserted into M_JOB_NLS table corresponding to each Account Code.
-
For a specific field in EcoSys, if user gives different column names in different EcoSys instances, the column from EcoSys which should be considered for specific project of Smart Materials should be specified in CIP by using the parameter proj_id using IF statement.
-
Since null values cannot be inserted into CURRENCY, SHORT_DESC and DESCRIPTION columns, it is mandatory to specify the data source for these columns in the CIP.
-
Since the maximum width of SHORT_DESC is 20 characters and of DESCRIPTION is 60 characters, the data retrieved from the EcoSys for these fields is trimmed to 20 and 60 characters irrespective of the number of characters in EcoSys.
The Account Codes structures with the assigned Account Codes are displayed in Smart Materials on the J.10.02 Maintain Account Codes screen.
The Account Codes with the short description and description are displayed on the J.10.03 Account Codes screen.