ZSF_FCST_METRICS_TXN
Users perform transactions on the Sales Forecasting and Opportunity Management user interface applications in the form of adjustments and revenue edits. Such transactions alter the unadjusted forecast and item / summary forecast numbers of a territory and all its ancestors. Each transaction will insert rows containing the differences between the old and new value of the metrics modified, rolled up all the way to the root product and root territory. This table will also have new rows inserted when the background jobs Refresh Current Forecast and Refresh Territory Forecast are executed. Other background jobs like Copy Previous Forecast Adjustments and Allocate Adjustments By Time will also insert metric differentials into this table in the same manner discussed above. This table will have the same structure as the table ZSF_FCST_METRICS. The column request_id will be set to -1 in this table always.
Details
-
Schema: FUSION
-
Object owner: ZSF
-
Object type: TABLE
-
Tablespace: Default
Primary Key
| Name | Columns |
|---|---|
|
ZSF_FCST_METRICS_TXN_PK |
FCST_METRICS_TXN_ID |
Columns
| Name | Datatype | Length | Precision | Not-null | Comments |
|---|---|---|---|---|---|
| FCST_METRICS_TXN_ID | NUMBER | 18 | Yes | Generated primary ID for the ZSF_FCST_METRICS_TXN table. | |
| BI_METRIC_TYPE | VARCHAR2 | 30 | Yes | Type of metric. Can be BI_TERR_PROD, BI_TERR_PROD_ADJPER. | |
| FCST_HEADER_ID | NUMBER | 18 | Yes | The forecast header for which the metric details are captured. | |
| FCST_METRICS_PARTITION_KEY | NUMBER | 18 | This is a new partition Key for zsf_Fcst_metrics table. This is an alternate key to the fcst_header_id | ||
| ADJUST_PERIOD_ID | NUMBER | 18 | Yes | Adjustment Period. For adjustments by Territory and Product, this will be the last adjust_period_id of the corresponding forecast header. | |
| FCST_TERRITORY_ID | NUMBER | 18 | Yes | Territory Id of the fcst_participant_id for which the metrics details are captured | |
| FCST_ADJUSTED_TERR_ID | NUMBER | 18 | FCST_ADJUSTED_TERR_ID | ||
| FCST_PARTICIPANT_ID | NUMBER | 18 | Yes | Participant Id from zsf_fcst_participant for which metrics are stored in this table. | |
| FCST_PRODUCT_ID | NUMBER | 18 | Yes | Product id for which metrics is stored. If there are no products in the application, a dummy product will be created and that Id will be used in this column instead. | |
| ROOT_PRODUCT_FLAG | VARCHAR2 | 1 | Used only in case the metrics should be computed for TERR, TERR_AP views. This will be either 'Y' indicating that fcst_product_id is the root product for that header or N if it is not the Root product | ||
| UNALLOCATED_ADJUSTMENT_FLAG | VARCHAR2 | 1 | This column will have the value 'N' if the adjustment of category BI_TERR_PROD_ADJPER is created from an actual VIEW2 adjustment. It will have a value 'Y' if the adjustment is a copy of the VIEW1 adjustment created for bi_metric_type BI_TERR_PROD_ADJPER. | ||
| CORP_REVN_AMT | NUMBER | unadjusted revenue amount for the non pseudo territory is stored here. | |||
| CORP_REVN_EXPECT_AMT | NUMBER | expected revenue amount for the non pseudo territory is stored here. | |||
| CORP_UPSIDE_AMT | NUMBER | Unadjusted Metrics for Non-Pseudo Territory, upside revenue amount is stored here | |||
| CORP_DOWNSIDE_AMT | NUMBER | Unadjusted Metrics for Non-Pseudo Territory, downside revenue amount is stored here. | |||
| CORP_PRED_REVN_AMT | NUMBER | Unadjusted Metrics for Non-Pseudo Territory, predicted revenue amount is stored here. | |||
| CORP_EXTN_UNADJ_MET_1 | NUMBER | Runtime Extensibility: place holder column 1 for custom unadjusted metrics | |||
| CORP_EXTN_UNADJ_MET_2 | NUMBER | Runtime Extensibility: place holder column 2 for custom unadjusted metrics | |||
| CORP_EXTN_UNADJ_MET_3 | NUMBER | Runtime Extensibility: place holder column 3 for custom unadjusted metrics | |||
| CORP_EXTN_UNADJ_MET_4 | NUMBER | Runtime Extensibility: place holder column 4 for custom unadjusted metrics | |||
| CORP_EXTN_UNADJ_MET_5 | NUMBER | Runtime Extensibility: place holder column 5 for custom unadjusted metrics | |||
| CORP_ITEM_ADJ_AMT | NUMBER | CORP_ITEM_ADJ_AMT | |||
| CORP_BEST_ITEM_ADJ_AMT | NUMBER | best case item adjustment (delta) amount for non-pseudo terrtiory is stored here. | |||
| CORP_WRST_ITEM_ADJ_AMT | NUMBER | worst case item adjustment (delta) amount for non-pseudo territory is stored here. | |||
| CORP_EXTN_ITADJ_MET_1 | NUMBER | Runtime Extensibility: place holder column 1 for custom item adjustment metrics | |||
| CORP_EXTN_ITADJ_MET_2 | NUMBER | Runtime Extensibility: place holder column 2 for custom item adjustment metrics | |||
| CORP_EXTN_ITADJ_MET_3 | NUMBER | Runtime Extensibility: place holder column 3 for custom item adjustment metrics | |||
| CORP_EXTN_ITADJ_MET_4 | NUMBER | Runtime Extensibility: place holder column 4 for custom item adjustment metrics | |||
| CORP_EXTN_ITADJ_MET_5 | NUMBER | Runtime Extensibility: place holder column 5 for custom item adjustment metrics | |||
| CORP_SUM_ADJ_AMT | NUMBER | summary adjustment (delta) amount for non-pseudo territory is stored here. | |||
| CORP_BEST_SUM_ADJ_AMT | NUMBER | best case summary adjustment (delta) amount for non-pseudo territory is stored here. | |||
| CORP_WRST_SUM_ADJ_AMT | NUMBER | worst case summary adjustment (delta) amount for non-pseudo territory is stored here. | |||
| CORP_EXTN_SUMADJ_MET_1 | NUMBER | Runtime Extensibility: place holder column 1 for custom summary adustment metrics | |||
| CORP_EXTN_SUMADJ_MET_2 | NUMBER | Runtime Extensibility: place holder column 2 for custom summary adustment metrics | |||
| CORP_EXTN_SUMADJ_MET_3 | NUMBER | Runtime Extensibility: place holder column 3 for custom summary adustment metrics | |||
| CORP_EXTN_SUMADJ_MET_4 | NUMBER | Runtime Extensibility: place holder column 4 for custom summary adustment metrics | |||
| CORP_EXTN_SUMADJ_MET_5 | NUMBER | Runtime Extensibility: place holder column 5 for custom summary adustment metrics | |||
| CHILD_REQUEST_ID | NUMBER | 18 | Enterprise Service Scheduler: indicates the request ID of the job that created or last updated the row. | ||
| TRANSACTION_TYPE | VARCHAR2 | 30 | Yes | Could be UNADJ (corresponding to Forecast Item), ITEMADJ (corresponding to Item Adjustment), SUMADJ (corresponding to Summary Adjustment). Used for debugging errors in metrics summarization job. | |
| TRANSACTION_SOURCE | VARCHAR2 | 30 | Yes | Used to identify the source transaction of this transaction entry. Used for debugging errors in metrics summarization job. | |
| PROCESS_STATUS | VARCHAR2 | 1 | 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. | ||
| CORP_REVN_WON_AMT | NUMBER | This field has the Won amount stored in corporate currency. | |||
| 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). | |||
| UNADJ_QUANTITY | NUMBER | unadjusted Quantity for the non pseudo territory is stored here. | |||
| ITADJ_QUANTITY | NUMBER | Item Adjustment Delta for Quantity for the non pseudo territory is stored here. | |||
| SUMADJ_QUANTITY | NUMBER | Summary Adjustment Delta for Quantity for the non pseudo territory is stored here. | |||
| RNWL_REVN_WON_AMT | NUMBER | Renewal won amount in corporate currency. | |||
| RNWL_REVN_EXPECT_AMT | NUMBER | Expected renewal revenue amount for the non pseudo territory is stored here. | |||
| RNWL_PRED_REVN_AMT | NUMBER | Unadjusted metrics for non-pseudo territory, predicted renewal revenue amount is stored here. | |||
| RNWL_UPSIDE_AMT | NUMBER | Unadjusted metrics for non-pseudo territory, renewal upside revenue amount is stored here. | |||
| RNWL_REVN_AMT | NUMBER | Unadjusted renewal revenue amount for the non pseudo territory is stored here. | |||
| RNWL_DOWNSIDE_AMT | NUMBER | Unadjusted metrics for non-pseudo territory, renewal downside revenue amount is stored here. | |||
| RNWL_UNADJ_QUANTITY | NUMBER | Renewal unadjusted quantity for the non pseudo territory is stored here. | |||
| RNWL_WRST_ITEM_ADJ_AMT | NUMBER | Renewal worst case item adjustment (delta) amount for non-pseudo territory is stored here. | |||
| RNWL_ITEM_ADJ_AMT | NUMBER | Renewal item adjustment (delta) amount for non-pseudo territory is stored here. | |||
| RNWL_BEST_ITEM_ADJ_AMT | NUMBER | Renewal best case item adjustment (delta) amount for non-pseudo territory is stored here. | |||
| RNWL_ITADJ_QUANTITY | NUMBER | Renewal item adjustment delta for quantity for the non pseudo territory is stored here. | |||
| RNWL_WRST_SUM_ADJ_AMT | NUMBER | Renewal worst case summary adjustment (delta) amount for non-pseudo territory is stored here. | |||
| RNWL_BEST_SUM_ADJ_AMT | NUMBER | Renewal best case summary adjustment (delta) amount for non-pseudo territory is stored here. | |||
| RNWL_SUM_ADJ_AMT | NUMBER | Renewal summary adjustment (delta) amount for non-pseudo territory is stored here. | |||
| RNWL_SUMADJ_QUANTITY | NUMBER | Renewal summary adjustment delta for quantity for the non pseudo territory is stored here. | |||
| NEW_REVN_WON_AMT | NUMBER | New won amount in corporate currency. | |||
| NEW_REVN_EXPECT_AMT | NUMBER | Expected new revenue amount for the non pseudo territory is stored here. | |||
| NEW_PRED_REVN_AMT | NUMBER | Unadjusted metrics for non-pseudo territory, predicted new revenue amount is stored here. | |||
| NEW_UPSIDE_AMT | NUMBER | Unadjusted metrics for non-pseudo territory, new upside revenue amount is stored here. | |||
| NEW_REVN_AMT | NUMBER | Unadjusted new revenue amount for the non pseudo territory is stored here. | |||
| NEW_DOWNSIDE_AMT | NUMBER | Unadjusted metrics for non-pseudo territory, new downside revenue amount is stored here. | |||
| NEW_UNADJ_QUANTITY | NUMBER | New unadjusted quantity for the non pseudo territory is stored here. | |||
| NEW_WRST_ITEM_ADJ_AMT | NUMBER | New worst case item adjustment (delta) amount for non-pseudo territory is stored here. | |||
| NEW_ITEM_ADJ_AMT | NUMBER | New item adjustment (delta) amount for non-pseudo territory is stored here. | |||
| NEW_BEST_ITEM_ADJ_AMT | NUMBER | New best case item adjustment (delta) amount for non-pseudo territory is stored here. | |||
| NEW_ITADJ_QUANTITY | NUMBER | New item adjustment delta for quantity for the non pseudo territory is stored here. | |||
| NEW_WRST_SUM_ADJ_AMT | NUMBER | New worst case summary adjustment (delta) amount for non-pseudo territory is stored here. | |||
| NEW_BEST_SUM_ADJ_AMT | NUMBER | New best case summary adjustment (delta) amount for non-pseudo territory is stored here. | |||
| NEW_SUM_ADJ_AMT | NUMBER | New summary adjustment (delta) amount for non-pseudo territory is stored here. | |||
| NEW_SUMADJ_QUANTITY | NUMBER | New summary adjustment delta for quantity for the non pseudo territory is stored here. |
Indexes
| Index | Uniqueness | Tablespace | Columns |
|---|---|---|---|
| ZSF_FCST_METRICS_TXN_N1 | Non Unique | Default | FCST_HEADER_ID, BI_METRIC_TYPE, FCST_METRICS_PARTITION_KEY, FCST_TERRITORY_ID, FCST_PRODUCT_ID, ADJUST_PERIOD_ID, ROOT_PRODUCT_FLAG |
| ZSF_FCST_METRICS_TXN_N2 | Non Unique | Default | REQUEST_ID, PROCESS_STATUS, BI_METRIC_TYPE |
| ZSF_FCST_METRICS_TXN_PK | Unique | Default | FCST_METRICS_TXN_ID |