APPS.AMS_UPGRADE_RECORDS_PKG dependencies on AMS_ACT_METRIC_HST

Line 42: l_table_name varchar2(30) := 'AMS_ACT_METRIC_HST';

38:
39:
40:
41: -- specify the table name and col name.
42: l_table_name varchar2(30) := 'AMS_ACT_METRIC_HST';
43: l_column_name varchar2(30) := 'ACTIVITY_METRIC_ID';
44:
45:
46: -- The following variables are required to check for the existinace of DBschema and table.

Line 119: UPDATE ams_act_metric_hst

115:
116: -- Update all first history records to have delta := value.
117: -- This update is to avoid 'N/A' in BIM DBI reports.
118:
119: UPDATE ams_act_metric_hst
120: SET FUNC_FORECASTED_DELTA = nvl(FUNC_FORECASTED_VALUE,0),
121: FUNC_ACTUAL_DELTA = nvl(FUNC_ACTUAL_VALUE,0)
122: WHERE (ACTIVITY_METRIC_ID, last_update_date) IN
123: (SELECT ACTIVITY_METRIC_ID, MIN(last_update_date)

Line 124: FROM ams_act_metric_hst

120: SET FUNC_FORECASTED_DELTA = nvl(FUNC_FORECASTED_VALUE,0),
121: FUNC_ACTUAL_DELTA = nvl(FUNC_ACTUAL_VALUE,0)
122: WHERE (ACTIVITY_METRIC_ID, last_update_date) IN
123: (SELECT ACTIVITY_METRIC_ID, MIN(last_update_date)
124: FROM ams_act_metric_hst
125: GROUP BY ACTIVITY_METRIC_ID)
126: AND(NVL(FUNC_FORECASTED_DELTA,0) <> NVL(FUNC_FORECASTED_VALUE,0)
127: OR NVL(FUNC_ACTUAL_DELTA,0) <> NVL(FUNC_ACTUAL_VALUE,0))
128: AND ACTIVITY_METRIC_ID BETWEEN l_start_id AND l_end_id;

Line 136: FROM ams_act_metric_hst hst

132: -- delete the duplicate records for a given last_update_date.
133:
134:
135: DELETE
136: FROM ams_act_metric_hst hst
137: WHERE (activity_metric_id, last_update_date) IN (SELECT activity_metric_id,last_update_date
138: FROM ams_act_metric_hst A
139: WHERE ROWID < (SELECT MAX(ROWID)
140: FROM ams_act_metric_hst B

Line 138: FROM ams_act_metric_hst A

134:
135: DELETE
136: FROM ams_act_metric_hst hst
137: WHERE (activity_metric_id, last_update_date) IN (SELECT activity_metric_id,last_update_date
138: FROM ams_act_metric_hst A
139: WHERE ROWID < (SELECT MAX(ROWID)
140: FROM ams_act_metric_hst B
141: WHERE A.activity_metric_id = B.activity_metric_id
142: AND TRUNC(a.last_update_date)= TRUNC(b.last_update_date)

Line 140: FROM ams_act_metric_hst B

136: FROM ams_act_metric_hst hst
137: WHERE (activity_metric_id, last_update_date) IN (SELECT activity_metric_id,last_update_date
138: FROM ams_act_metric_hst A
139: WHERE ROWID < (SELECT MAX(ROWID)
140: FROM ams_act_metric_hst B
141: WHERE A.activity_metric_id = B.activity_metric_id
142: AND TRUNC(a.last_update_date)= TRUNC(b.last_update_date)
143: )
144: )

Line 156: INSERT INTO ams_act_metric_hst

152:
153: -- if the record was deleted but the history does not show zero for the last
154: -- entry then insert a history record one day after the last entry.
155:
156: INSERT INTO ams_act_metric_hst
157: (ACT_MET_HST_ID,
158: ACTIVITY_METRIC_ID,
159: LAST_UPDATE_DATE,
160: LAST_UPDATED_BY,

Line 239: SELECT AMS_ACT_METRIC_HST_S.NEXTVAL,

235: BASIS_YEAR,
236: EX_START_NODE,
237: HIERARCHY_TYPE,
238: DEPEND_ACT_METRIC)
239: SELECT AMS_ACT_METRIC_HST_S.NEXTVAL,
240: a.ACTIVITY_METRIC_ID,
241: a.LAST_UPDATE_DATE + 1 AS LAST_UPDATE_DATE,
242: a.LAST_UPDATED_BY,
243: a.CREATION_DATE,

Line 321: FROM ams_act_metric_hst a

317: a.BASIS_YEAR,
318: a.EX_START_NODE,
319: a.HIERARCHY_TYPE,
320: a.DEPEND_ACT_METRIC
321: FROM ams_act_metric_hst a
322: WHERE NOT EXISTS (SELECT 'x' FROM ams_act_metrics_all b
323: WHERE a.activity_metric_id = b.activity_metric_id)
324: AND last_update_date =
325: (SELECT MAX(c.last_update_date)

Line 326: FROM ams_act_metric_hst c

322: WHERE NOT EXISTS (SELECT 'x' FROM ams_act_metrics_all b
323: WHERE a.activity_metric_id = b.activity_metric_id)
324: AND last_update_date =
325: (SELECT MAX(c.last_update_date)
326: FROM ams_act_metric_hst c
327: WHERE c.activity_metric_id = a.activity_metric_id)
328: AND (NVL(func_actual_value,0) <> 0 OR NVL(func_forecasted_value,0) <> 0)
329: AND a.activity_metric_id BETWEEN l_start_id AND l_end_id;
330: