Additional Full Load Features

Online Full Load

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

the picture is described in the document text

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.

Enabling Online Full Load

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 Full 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.

How Sequential Full Load Works

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.

Enabling Sequential Full Load

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

...
```