ZSF_FCST_METRICS_STAGING
This table will store the list of territories for which metrics needs to be generated by the Forecast Metrics Generation Process. Work will be allocated to worker threads being used to generate the Forecast Metrics using the rows in this table. Worker threads will identify the territories that they need to process by using the MOD(territory_id) function. Territories picked up for processing will be marked as process_status=P and once processed, will be marked as process_status=C. If there was an error processing the set of rows, those rows will be marked as E and the error will be stored in the error_message column. At the end of all processing, the prent request will truncate this table if all processing was successful. Otherwise, the table will be left behind to be analysed by the Administrator
Details
-
Schema: FUSION
-
Object owner: ZSF
-
Object type: TABLE
-
Tablespace: Default
Primary Key
| Name | Columns |
|---|---|
|
ZSF_FCST_METRICS_STAGING_PK |
FCST_METRICS_STAGING_ID |
Columns
| Name | Datatype | Length | Precision | Not-null | Comments |
|---|---|---|---|---|---|
| FCST_METRICS_STAGING_ID | NUMBER | 18 | Yes | Generated primary id for the ZSF_FCST_METRICS_STAGING table. | |
| FCST_PARTICIPANT_ID | NUMBER | 18 | Yes | Participant Id from zsf_fcst_participant for which metrics are stored in this table. | |
| FCST_HEADER_ID | NUMBER | 18 | Yes | The forecast header for which the metric details are captured. | |
| FCST_METRICS_PARTITION_KEY | NUMBER | 18 | The table will be partitioned on this column. This is an alternate key to the fcst_header_id. | ||
| FCST_TERRITORY_ID | NUMBER | 18 | Yes | Territory Id of the fcst_participant_id for which the metrics details are captured | |
| TERRITORY_LEVEL | NUMBER | Yes | Territory Level from the mot_territories. Denormalized for query purpose | ||
| LEAF_TERRITORY_FLAG | VARCHAR2 | 1 | Yes | This field indicates if the current territory is a leaf territory in the territory hierarchy. | |
| PROCESS_STATUS | VARCHAR2 | 30 | Column to indicate that the row is being worked on by the Metrics Summarization Process. The column will be initially NULL and then will be set to P before processing starts for that row. Once processing is done, these rows will be deleted. If there were errors in processing, this row will be marked as E | ||
| ERROR_MESSAGE | VARCHAR2 | 4000 | If the processing of a row results in an error, this column will hold the error observed while processing this row. The error should be truncated to 4000 characters before inserting into this column. | ||
| REQUEST_ID | NUMBER | 18 | Enterprise Service Scheduler: indicates the request ID of the job that created or last updated the row. | ||
| CHILD_REQUEST_ID | NUMBER | 18 | Enterprise Service Scheduler: indicates the request ID of the job that created or last updated the row. | ||
| CREATED_BY | VARCHAR2 | 64 | Yes | Who column: indicates the user who created the row. | |
| CREATION_DATE | TIMESTAMP | Yes | Who column: indicates the date and time of the creation of the row. | ||
| LAST_UPDATE_DATE | TIMESTAMP | Yes | Who column: indicates the date and time of the last update of the row. | ||
| LAST_UPDATED_BY | VARCHAR2 | 64 | Yes | Who column: indicates the user who last updated the row. | |
| LAST_UPDATE_LOGIN | VARCHAR2 | 32 | Who column: indicates the session login associated to the user who last updated the row. | ||
| CONFLICT_ID | NUMBER | 18 | Yes | Disconnected Mobile: this value is used to guarantee the uniqueness of the row when duplicates are created in different databases (i.e. mobile databases or the server). | |
| OBJECT_VERSION_NUMBER | NUMBER | 9 | Yes | Used to implement optimistic locking. This number is incremented every time that the row is updated. The number is compared at the start and end of a transaction to detect whether another session has updated the row since it was queried. | |
| USER_LAST_UPDATE_DATE | TIMESTAMP | Disconnected Mobile: indicates the date and time of the last update of the row. This value is different from LAST_UPDATE_DATE if the update originally happened in a different database (i.e. a different mobile database or the server). |
Indexes
| Index | Uniqueness | Tablespace | Columns |
|---|---|---|---|
| ZSF_FCST_METRICS_STAGING_N1 | Non Unique | Default | TERRITORY_LEVEL, REQUEST_ID, PROCESS_STATUS |
| ZSF_FCST_METRICS_STAGING_N2 | Non Unique | Default | CHILD_REQUEST_ID, PROCESS_STATUS |
| ZSF_FCST_METRICS_STAGING_PK | Unique | Default | FCST_METRICS_STAGING_ID |
| ZSF_FCST_METRICS_STAGING_U1 | Unique | Default | FCST_PARTICIPANT_ID |