[Home] [Help]
4804: a.HIERARCHY_TYPE,
4805: a.DEPEND_ACT_METRIC,
4806: b.FUNC_FORECASTED_DELTA,
4807: b.FUNC_ACTUAL_DELTA
4808: FROM ams_act_metrics_all a, ams_act_metric_hst b
4809: WHERE b.activity_metric_id = a.activity_metric_id
4810: AND trunc(a.last_update_date) = trunc(b.last_update_date)
4811: AND a.last_update_date > b.last_update_date
4812: AND b.last_update_date =
4810: AND trunc(a.last_update_date) = trunc(b.last_update_date)
4811: AND a.last_update_date > b.last_update_date
4812: AND b.last_update_date =
4813: (SELECT MAX(last_update_date)
4814: FROM ams_act_metric_hst c
4815: WHERE c.activity_metric_id = b.activity_metric_id)
4816: ;
4817:
4818: l_ACT_MET_HST_ID num_table_type;
4903: l_today DATE := SYSDATE;
4904:
4905: CURSOR c_get_deleted_today IS
4906: SELECT act_met_hst_id
4907: FROM ams_act_metric_hst a
4908: WHERE NOT EXISTS (SELECT 'x' FROM ams_act_metrics_all b
4909: WHERE a.activity_metric_id = b.activity_metric_id)
4910: AND last_update_date =
4911: (SELECT MAX(c.last_update_date)
4908: WHERE NOT EXISTS (SELECT 'x' FROM ams_act_metrics_all b
4909: WHERE a.activity_metric_id = b.activity_metric_id)
4910: AND last_update_date =
4911: (SELECT MAX(c.last_update_date)
4912: FROM ams_act_metric_hst c
4913: WHERE c.activity_metric_id = a.activity_metric_id)
4914: AND trunc(last_update_date) = trunc(l_today)
4915: AND (NVL(func_actual_value,0) <> 0 OR NVL(func_forecasted_value,0) <> 0);
4916:
4920: IF AMS_DEBUG_HIGH_ON THEN
4921: write_msg(L_API_NAME, 'BEGIN');
4922: END IF;
4923:
4924: INSERT INTO ams_act_metric_hst
4925: (ACT_MET_HST_ID,
4926: ACTIVITY_METRIC_ID,
4927: LAST_UPDATE_DATE,
4928: LAST_UPDATED_BY,
5002: HIERARCHY_TYPE,
5003: DEPEND_ACT_METRIC,
5004: FUNC_FORECASTED_DELTA,
5005: FUNC_ACTUAL_DELTA)
5006: SELECT AMS_ACT_METRIC_HST_S.NEXTVAL,
5007: a.ACTIVITY_METRIC_ID,
5008: a.LAST_UPDATE_DATE,
5009: a.LAST_UPDATED_BY,
5010: a.CREATION_DATE,
5083: a.HIERARCHY_TYPE,
5084: a.DEPEND_ACT_METRIC,
5085: NVL(a.FUNC_FORECASTED_VALUE,0) - NVL(b.FUNC_FORECASTED_VALUE,0),
5086: NVL(a.FUNC_ACTUAL_VALUE,0) - NVL(b.FUNC_ACTUAL_VALUE,0)
5087: FROM ams_act_metrics_all a, ams_act_metric_hst b
5088: WHERE a.activity_metric_id = b.activity_metric_id
5089: AND b.last_update_date =
5090: (SELECT MAX(last_update_date)
5091: FROM ams_act_metric_hst c
5087: FROM ams_act_metrics_all a, ams_act_metric_hst b
5088: WHERE a.activity_metric_id = b.activity_metric_id
5089: AND b.last_update_date =
5090: (SELECT MAX(last_update_date)
5091: FROM ams_act_metric_hst c
5092: WHERE c.activity_metric_id = a.activity_metric_id)
5093: AND TRUNC(a.last_update_date) > TRUNC(b.last_update_date)
5094: ;
5095: IF AMS_DEBUG_HIGH_ON THEN
5104: END IF;
5105:
5106:
5107: -- Insert new activity metrics.
5108: INSERT INTO ams_act_metric_hst
5109: (ACT_MET_HST_ID,
5110: ACTIVITY_METRIC_ID,
5111: LAST_UPDATE_DATE,
5112: LAST_UPDATED_BY,
5186: HIERARCHY_TYPE,
5187: DEPEND_ACT_METRIC,
5188: FUNC_FORECASTED_DELTA,
5189: FUNC_ACTUAL_DELTA)
5190: SELECT AMS_ACT_METRIC_HST_S.NEXTVAL,
5191: a.ACTIVITY_METRIC_ID,
5192: a.LAST_UPDATE_DATE,
5193: a.LAST_UPDATED_BY,
5194: a.CREATION_DATE,
5269: -- BUG2214496: Initialize to original value.
5270: a.FUNC_FORECASTED_VALUE,
5271: a.FUNC_ACTUAL_VALUE
5272: FROM ams_act_metrics_all a
5273: WHERE NOT EXISTS (SELECT 'x' FROM ams_act_metric_hst b
5274: WHERE a.activity_metric_id = b.activity_metric_id);
5275: IF AMS_DEBUG_HIGH_ON THEN
5276: write_msg(L_API_NAME, 'INSERTED new metrics: '|| SQL%ROWCOUNT);
5277: END IF;
5377: -- If any items returned, bulk update.
5378: IF l_ACT_MET_HST_ID.COUNT > 0 THEN
5379:
5380: FORALL l_count IN l_ACT_MET_HST_ID.FIRST .. l_ACT_MET_HST_ID.LAST
5381: UPDATE ams_act_metric_hst
5382: SET LAST_UPDATE_DATE = l_LAST_UPDATE_DATE(l_count),
5383: LAST_UPDATED_BY = l_LAST_UPDATED_BY(l_count),
5384: CREATION_DATE = l_CREATION_DATE(l_count),
5385: CREATED_BY = l_CREATED_BY(l_count),
5569: END IF;
5570:
5571: IF l_ACT_MET_HST_ID.COUNT > 0 THEN
5572: FORALL l_count IN l_ACT_MET_HST_ID.FIRST .. l_ACT_MET_HST_ID.LAST
5573: UPDATE ams_act_metric_hst
5574: SET LAST_UPDATE_DATE = SYSDATE,
5575: TRANS_FORECASTED_VALUE = 0,
5576: TRANS_COMMITTED_VALUE = 0,
5577: TRANS_ACTUAL_VALUE = 0,
5598: write_msg(L_API_NAME, 'COMMIT');
5599: END IF;
5600: END IF;
5601:
5602: INSERT INTO ams_act_metric_hst
5603: (ACT_MET_HST_ID,
5604: ACTIVITY_METRIC_ID,
5605: LAST_UPDATE_DATE,
5606: LAST_UPDATED_BY,
5680: HIERARCHY_TYPE,
5681: DEPEND_ACT_METRIC,
5682: FUNC_FORECASTED_DELTA,
5683: FUNC_ACTUAL_DELTA)
5684: SELECT AMS_ACT_METRIC_HST_S.NEXTVAL,
5685: a.ACTIVITY_METRIC_ID,
5686: l_today,
5687: a.LAST_UPDATED_BY,
5688: a.CREATION_DATE,
5761: a.HIERARCHY_TYPE,
5762: a.DEPEND_ACT_METRIC,
5763: -NVL(a.FUNC_FORECASTED_VALUE,0),
5764: -NVL(a.FUNC_ACTUAL_VALUE,0)
5765: FROM ams_act_metric_hst a
5766: WHERE NOT EXISTS (SELECT 'x' FROM ams_act_metrics_all b
5767: WHERE a.activity_metric_id = b.activity_metric_id)
5768: AND last_update_date =
5769: (SELECT MAX(c.last_update_date)
5766: WHERE NOT EXISTS (SELECT 'x' FROM ams_act_metrics_all b
5767: WHERE a.activity_metric_id = b.activity_metric_id)
5768: AND last_update_date =
5769: (SELECT MAX(c.last_update_date)
5770: FROM ams_act_metric_hst c
5771: WHERE c.activity_metric_id = a.activity_metric_id)
5772: AND last_update_date < TRUNC(l_today)
5773: AND (NVL(func_actual_value,0) <> 0 OR NVL(func_forecasted_value,0) <> 0);
5774: