Oracle Enterprise Command Center Framework supports online full load to allow users to access the dashboard functionality even during full data load execution. This feature can be utilized to greatly reduce the downtime of ECC dashboards because of full data loads. Online full load works as described in the flowchart below:
Online Full Load Flowchart
A user submits a full load for a data set with a job ID.
If the onlinefullload
flag is disabled, then the existing full load logic is executed.
If the onlinefullload
flag is not enabled for the given data set, then the existing full load logic is executed.
If the onlinefullload
flag is enabled for the steps above, then the logic for online full load logic is executed.
The steps for online full load are:
Create a new collection with the name <data set>-<job ID>
and write data to it.
Create a backup after the writing of the data is completed, with backup name <data set>-<job ID>
.
Delete the newly-created collection with the name <data set>-<job ID>
Deleted the old collection with the name <data set>
.
Restore the backup with the name <dataset>-<job ID>
to collection name <dataset>
.
Deleted the backup with the name <dataset>-<job ID>
and invalidate the cache for the data set with name <data set>
.
Any errors until step 4 will rollback the changes to the data set and users will still be able to use the current version of the data set. Errors after step 4 will not rollback the changes as the data set is deleted in step 4.
Follow the steps below to enable online full load:
Set the below properties in configuration properties:
Set the property ecc.onlinefulload.isEnabled
to true
for enabling the online full load. By default, the property is set to false
.
Add the data sets requiring online full load to property ecc.onlinefulload.enabled.datasets = <dataset1>,<dataset2>
. Each data set key has to be separated by ','. There are no data sets mentioned by default.
Add the location for creating temporary backup in property ecc.onlinefulload. staging.location = <location of temporary backup for onlinefulload>
. No location is mentioned by default.
Restart the ECC managed server to apply the changes.
Validate changes by submitting a full load for one of the data sets enabled for online data load.
Sequential load employs a staggered data load mechanism by segregating the data load execution based on unique values of the attribute set as a sequential load controller. The Sequential Full Load feature serializes the data load by languages, generating fine-grained queries that yield better performance compared to the conventional data load process that uses coarse-grained queries.
This feature an improve the data load performance as it provides better flexibility for customers to load only a subset of essential data rather than the full data.
A conventional full load resets all the data to reflect the state of data in Oracle E-Business Suite, whereas an incremental load identifies the last run date at the data set level, and then fetches the incremental changes from that date to update the Enterprise Command Center data set.
Sequential full load is a composite of full data loads for all the identifiers that form the data set and it sequences the full loads per identifier. Sequential full load ensures that the last run date is set at an identifier level so that you can run an incremental load considering this last run date.
Sequential data load also allows you to set a composite identifier basis, in which the full data loads are sequenced.
Sequential data load is triggered from a wrapper concurrent program. The data load request sends the identifier along with the other standard parameters to the central data load concurrent program. The following steps are performed:
The last run date for each identifier is gathered. Empty identifiers are treated as a null value.
If the last run has no identifier, then the last run date is set for all the identifiers that make a composite identifier.
If a data set has been reset after a run for the identifiers, then the last run date would be considered to have a null value.
The sequential full load feature is controlled by a profile option in Oracle Application Object Library. This feature has to be enabled per each data set's wrapper concurrent program definition using the steps below:
Add two parameters: Identifier and Set/Reset Data (force flag)
The Set/Reset Data parameter uses a List of Values with two values: 'Extend Existing Data set' and 'Data set Reset'
This component would be a central component similar to how the load Type Value set is defined. The upper limit on identifier length for each job is 240 characters including the separator character in the case of a composite identifier.
The profile option FND: ECC Sequential Data Load Per Data Set Language (code ECC_DATASETS_SERIAL_LANGS) has the following valid values:
ALL
NONE
Comma-separated list of data set keys
For more information on using profile options, see: User Profiles and Profile Options in Oracle Application Object Library, Oracle E-Business Suite Setup Guide.
The wrapper concurrent program has the following characteristics:
The fields identifier and the parameter p_force_flag
(for data set reset) for the wrapper concurrent program executable.
Example: p_lastrun_identifier:=p_org_code;
The last run date identifier should be assigned the parameter p_org_code
itself
For a composite identifier, the last run identifier should use “|” as separator.
Note that this separator is applicable only for the Central ECC Data Load job.
For example:
PROCEDURE SUBMIT_ECC_DATA_LOAD ( ERRBUF OUT NOCOPY VARCHAR2 , RETCODE OUT NOCOPY VARCHAR2 , p_system_name IN VARCHAR2, p_dataset_key IN VARCHAR2, p_load_type IN VARCHAR2, p_languages IN VARCHAR2, p_log_level IN VARCHAR2, p_trace_enabled IN VARCHAR2, p_org_code IN VARCHAR2, p_force_flag IN VARCHAR2,
In running the wrapper concurrent program, note the following conditions of the parameters:
Pass the following parameters in the call to ECC Run Data Load:
Identifier as custom parameter
Identifier as p_lastrun_identifier
Parameter f_force_flag
as the reset flag
The upper limit on the length of p_lastrun_identifier
for each job is 240 characters.
These parameters are optional and useful only for sequential data load.
-- Full Load fnd_file.put_line(FND_FILE.LOG,'Submitting request: PIM ECC Data Load for load type l_load_type: 1 || p_load_type) IF (p_dataset_key IS NOT NULL ) THEN l_request_id := fnd_request.submit_request( 'FND', 'ECCRUNDL', 'ECC - Run Data Load', sysdate, true, p_system_name, lapp_short_name, NVL(p_dataset_key,null), p_load_type, p.languages, p_trace_enabled, p_log_level, p_org_code, null, null, null, null, null, null, null, null, null, null, ull, null, null, null, p_lastrun_identifier, p_force_flag ): ELSE
The ECC Data Load Package includes
Procedure that gets the identifier passed in the array p_addl_params
Procedure to update the full load and incremental load queries to update the SQL clause
PROCEDURE GET_ECC_DATA_LOAD_INFO(p_dataset_key IN VARCHAR2, p_load_type IN VARCHAR2, p_ds_last_success_run IN DATE, p_languages IN VARCHAR2, p_addl_params IN ecc_sec_field_values, x_ecc_ds_meta_rec OUT NOCOPY ecc_ds_meta_rec, x_return status OUT NOCOPY VARCHAR2) IS ... IF (p_addl_params.exists(1)) THEN l_org_code := p_addl_params(l); ... ELSIF (p_dataset_key = inv-related-item') THEN l_sql_text:='SELECT * FROM ( SELECT ECC_SPEC_ID, INVENTORY_ITEM_ID, ORGANIZATION_ID, relationship_type_id, ORG_CODE, ITEM, DESCRIPTION, RELATIONSHIP_TYPES, RELATED_ITEM, LANGUAGE DFF_SELECT_TOKEN FROM MTL_ITEM_ECC_REL_ITEMS_V mtl_v DFF_FROM_TOKEN where LANGUAGE in ('||v_lang_in [...] '|| l_org_code_clause||' DFF_WHERE_TOKEN) PIVOT (MAX(description) AS DESCRIPTION,MAX(relationship_types) as RELATIONSHIP_TYPES FOR language IN('|| v_for_lang_pivot_clause ||'))';
Add/delete query as part of the full load for an identifier. This is required when the data set is not reset but you want to reload the entire data set for an identifier using Full Load.
query_det_arr(1) := ecc_query_det_rec(ld_ap_inv_acc, 'DELETE_BY_QUERY'); x_ecc_ds_meta_rec :=ecc_ds_meta_rec(p_dataset_key,query_det_arr);
ld_ap_inv_acc varchar2(10000) := 'SELECT DISTINCT ''ECC_SPEC_ID'' as ATTRIBUTE_NAME, aia.invoice_id as ATTRIBUTE_VALUE, ''LIKE'' AS OPERATOR FROM ap_invoices_all aia, ap_suppliers ap, ap_supplier_sites_all pos, xla_events xe, XLA_TRANSACTION_ENTITIES XTE, xla_ae_headers xah, xla_ae_lines xal Where ap.vendor_id = aia.vendor_id AND aia.vendor_site_id = pos.vendor_site_id AND xe.application_id = 200 AND xe.process_status_code <> ''U'' AND XTE.APPLICATION_ID = 200 AND NVL(XTE.SOURCE ID INT 1.-991= AIA.INVOICE ID ...
If the last run date is null for an incremental load, then the query for a full load should be run. If the last run date returned is not null, then the incremental load query should be run.
IF (p_load_type = 'FULL_LOAD') OR ((p_load_type = 'INCREMENTAL_LOAD') AND p_ds_last_success_run is null) THEN ...