[Home] [Help]
PACKAGE BODY: APPS.AMS_UPGRADE_RECORDS_PKG
Source
1 PACKAGE BODY AMS_UPGRADE_RECORDS_PKG AS
2 -- $Header: amsvupdb.pls 120.0 2006/06/29 06:21:22 batoleti noship $
3
4
5 PROCEDURE AMS_UPG_METRIC_HST_RECS_MGR (
6 X_errbuf out NOCOPY varchar2,
7 X_retcode out NOCOPY varchar2,
8 X_batch_size in number,
9 X_Num_Workers in number
10 ) IS
11
12 BEGIN
13 --
14 -- Manager processing.
15 -- using dynamic sql, manager(submit_subrequests) calls the worker process(AMSUPGHISTORYRECSWKR).
16 --
17 AD_CONC_UTILS_PKG.submit_subrequests(
18 X_errbuf=>X_errbuf,
19 X_retcode=>X_retcode,
20 X_WorkerConc_app_shortname=>'AMS',
21 X_WorkerConc_progname=>'AMSUPGHISTORYRECSWKR',
22 X_batch_size=>X_batch_size,
23 X_Num_Workers=>X_Num_Workers);
24
25 END AMS_UPG_METRIC_HST_RECS_MGR;
26
27
28
29 PROCEDURE AMS_UPG_METRIC_HST_RECS_WKR (
30 X_errbuf out NOCOPY varchar2,
31 X_retcode out NOCOPY varchar2,
32 l_batch_size in number,
33 l_Worker_Id in number,
34 l_Num_Workers in number
35 ) IS
36
37
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.
47
48 l_product varchar2(30) := 'AMS';
49 l_status varchar2(30);
50 l_industry varchar2(30);
51 l_retstatus boolean;
52 l_table_owner varchar2(30);
53
54 --
55 -- the APIs use a combination of TABLE_NAME and UPDATE_NAME to track an
56 -- update. The update should be a no-op on a rerun, provided the TABLE_NAME
57 -- and UPDATE_NAME do not change.
58 --
59 -- If you have modified the script and you want the
60 -- script to reprocess the data, you must modify UPDATE_NAME to reflect
61 -- the change.
62 -- Now the ver# 120.9 so keeping the update_name as 'sqlscriptname_8.sql'.
63 --
64
65 l_update_name varchar2(500) := 'amsupamh_9.sql';
66
67 l_start_id number;
68 l_end_id number;
69 l_rows_processed number := 0;
70 l_any_rows_to_process boolean;
71 l_row_counts number := 0;
72
73
74
75 BEGIN
76
77
78 --
79 -- get schema name of the table for ID range processing
80 --
81 l_retstatus := fnd_installation.get_app_info(
82 l_product, l_status, l_industry, l_table_owner);
83
84 IF ((l_retstatus = FALSE)
85 OR
86 (l_table_owner is null))
87 THEN
88 raise_application_error(-20001,
89 'Cannot get schema name for product : '||l_product);
90 END IF;
91
92 --
93 -- Worker processing
94 --
95
96 ad_parallel_updates_pkg.initialize_id_range(
97 ad_parallel_updates_pkg.ID_RANGE,
98 l_table_owner,
99 l_table_name,
100 l_update_name,
101 l_column_name,
102 l_worker_id,
103 l_num_workers,
104 l_batch_size, 0);
105
106 ad_parallel_updates_pkg.get_id_range(
107 l_start_id,
108 l_end_id,
109 l_any_rows_to_process,
110 l_batch_size,
111 TRUE);
112
113 WHILE (l_any_rows_to_process = TRUE)
114 LOOP
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)
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;
129
130
131 -- delete where duplicate last_update_dates and not in activity table.
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
141 WHERE A.activity_metric_id = B.activity_metric_id
142 AND TRUNC(a.last_update_date)= TRUNC(b.last_update_date)
143 )
144 )
145 AND NOT EXISTS (SELECT 1
146 FROM ams_act_metrics_all c
147 WHERE hst.activity_metric_id = c.activity_metric_id)
148 AND hst.activity_metric_id BETWEEN l_start_id AND l_end_id;
149
150
151
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,
161 CREATION_DATE,
162 CREATED_BY,
163 LAST_UPDATE_LOGIN,
164 OBJECT_VERSION_NUMBER,
165 ACT_METRIC_USED_BY_ID,
166 ARC_ACT_METRIC_USED_BY,
167 APPLICATION_ID,
168 METRIC_ID,
169 TRANSACTION_CURRENCY_CODE,
170 TRANS_FORECASTED_VALUE,
171 TRANS_COMMITTED_VALUE,
172 TRANS_ACTUAL_VALUE,
173 FUNCTIONAL_CURRENCY_CODE,
174 FUNC_FORECASTED_VALUE,
175 FUNC_COMMITTED_VALUE,
176 DIRTY_FLAG,
177 FUNC_ACTUAL_VALUE,
178 LAST_CALCULATED_DATE,
179 VARIABLE_VALUE,
180 COMPUTED_USING_FUNCTION_VALUE,
181 METRIC_UOM_CODE,
182 ORG_ID,
183 DIFFERENCE_SINCE_LAST_CALC,
184 ACTIVITY_METRIC_ORIGIN_ID,
185 ARC_ACTIVITY_METRIC_ORIGIN,
186 DAYS_SINCE_LAST_REFRESH,
187 SUMMARIZE_TO_METRIC,
188 ROLLUP_TO_METRIC,
189 SCENARIO_ID,
190 ATTRIBUTE_CATEGORY,
191 ATTRIBUTE1,
192 ATTRIBUTE2,
193 ATTRIBUTE3,
194 ATTRIBUTE4,
195 ATTRIBUTE5,
196 ATTRIBUTE6,
197 ATTRIBUTE7,
198 ATTRIBUTE8,
199 ATTRIBUTE9,
200 ATTRIBUTE10,
201 ATTRIBUTE11,
202 ATTRIBUTE12,
203 ATTRIBUTE13,
204 ATTRIBUTE14,
205 ATTRIBUTE15,
206 SECURITY_GROUP_ID,
207 FUNC_FORECASTED_DELTA,
208 FUNC_ACTUAL_DELTA,
209 DESCRIPTION,
210 ACT_METRIC_DATE,
211 ARC_FUNCTION_USED_BY,
212 FUNCTION_USED_BY_ID,
213 PURCHASE_REQ_RAISED_FLAG,
214 SENSITIVE_DATA_FLAG,
215 BUDGET_ID,
216 FORECASTED_VARIABLE_VALUE,
217 HIERARCHY_ID,
218 PUBLISHED_FLAG,
219 PRE_FUNCTION_NAME,
220 POST_FUNCTION_NAME,
221 START_NODE,
222 FROM_LEVEL,
223 TO_LEVEL,
224 FROM_DATE,
225 TO_DATE,
226 AMOUNT1,
227 AMOUNT2,
228 AMOUNT3,
229 PERCENT1,
230 PERCENT2,
231 PERCENT3,
232 STATUS_CODE,
233 ACTION_CODE,
234 METHOD_CODE,
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,
244 a.CREATED_BY,
245 a.LAST_UPDATE_LOGIN,
246 a.OBJECT_VERSION_NUMBER,
247 a.ACT_METRIC_USED_BY_ID,
248 a.ARC_ACT_METRIC_USED_BY,
249 a.APPLICATION_ID,
250 a.METRIC_ID,
251 a.TRANSACTION_CURRENCY_CODE,
252 0 AS TRANS_FORECASTED_VALUE,
253 0 AS TRANS_COMMITTED_VALUE,
254 0 AS TRANS_ACTUAL_VALUE,
255 a.FUNCTIONAL_CURRENCY_CODE,
256 0 AS FUNC_FORECASTED_VALUE,
257 0 AS FUNC_COMMITTED_VALUE,
258 a.DIRTY_FLAG,
259 0 AS FUNC_ACTUAL_VALUE,
260 a.LAST_CALCULATED_DATE,
261 NULL AS VARIABLE_VALUE,
262 a.COMPUTED_USING_FUNCTION_VALUE,
263 a.METRIC_UOM_CODE,
264 a.ORG_ID,
265 a.DIFFERENCE_SINCE_LAST_CALC,
266 a.ACTIVITY_METRIC_ORIGIN_ID,
267 a.ARC_ACTIVITY_METRIC_ORIGIN,
268 a.DAYS_SINCE_LAST_REFRESH,
269 a.SUMMARIZE_TO_METRIC,
270 a.ROLLUP_TO_METRIC,
271 a.SCENARIO_ID,
272 a.ATTRIBUTE_CATEGORY,
273 a.ATTRIBUTE1,
274 a.ATTRIBUTE2,
275 a.ATTRIBUTE3,
276 a.ATTRIBUTE4,
277 a.ATTRIBUTE5,
278 a.ATTRIBUTE6,
279 a.ATTRIBUTE7,
280 a.ATTRIBUTE8,
281 a.ATTRIBUTE9,
282 a.ATTRIBUTE10,
283 a.ATTRIBUTE11,
284 a.ATTRIBUTE12,
285 a.ATTRIBUTE13,
286 a.ATTRIBUTE14,
287 a.ATTRIBUTE15,
288 a.SECURITY_GROUP_ID,
289 -NVL(a.FUNC_FORECASTED_VALUE,0) AS FUNC_FORECASTED_DELTA,
290 -NVL(a.FUNC_ACTUAL_VALUE,0) AS FUNC_ACTUAL_DELTA,
291 a.DESCRIPTION,
292 a.ACT_METRIC_DATE,
293 a.ARC_FUNCTION_USED_BY,
294 a.FUNCTION_USED_BY_ID,
295 a.PURCHASE_REQ_RAISED_FLAG,
296 a.SENSITIVE_DATA_FLAG,
297 a.BUDGET_ID,
298 NULL AS FORECASTED_VARIABLE_VALUE,
299 a.HIERARCHY_ID,
300 a.PUBLISHED_FLAG,
301 a.PRE_FUNCTION_NAME,
302 a.POST_FUNCTION_NAME,
303 a.START_NODE,
304 a.FROM_LEVEL,
305 a.TO_LEVEL,
306 a.FROM_DATE,
307 a.TO_DATE,
308 a.AMOUNT1,
309 a.AMOUNT2,
310 a.AMOUNT3,
311 a.PERCENT1,
312 a.PERCENT2,
313 a.PERCENT3,
314 a.STATUS_CODE,
315 a.ACTION_CODE,
316 a.METHOD_CODE,
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)
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
331
332 l_rows_processed := SQL%ROWCOUNT;
333
334 ad_parallel_updates_pkg.processed_id_range(
335 l_rows_processed,
336 l_end_id);
337
338
339 --
340 -- commit transaction here
341 --
342
343 commit;
344
345 --
346 -- Get the next id range
347 --
348
349 ad_parallel_updates_pkg.get_id_range(
350 l_start_id,
351 l_end_id,
352 l_any_rows_to_process,
353 l_batch_size,
354 FALSE);
355
356 END LOOP;/*For WHILE loop */
357
358
359 --
360 -- commit transaction here
361 --
362
363 COMMIT;
364
365 X_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
366
367 EXCEPTION
368 WHEN OTHERS THEN
369 X_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
370 raise;
371
372 END AMS_UPG_METRIC_HST_RECS_WKR;
373
374 END;