eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.HXC_ARCHIVE_RESTORE_UTILS

Source


1 PACKAGE BODY hxc_archive_restore_utils AS
2 /* $Header: hxcarcresutl.pkb 120.6 2005/11/22 16:18:11 jdupont noship $ */
3 ----------------------------------------------------------------------------
4 -- Function Name : check_max_errors
5 -- Description : This function is called during Validate Data Set process to
6 --               check if the error count has exceeded the maximum specified
7 --               value
8 -- Returns true if maximum errors exceeded; else false
9 ----------------------------------------------------------------------------
10 FUNCTION check_max_errors(p_error_count IN NUMBER)
11 RETURN BOOLEAN
12 IS
13 BEGIN
14 
15   IF g_error_count is null THEN
16     g_error_count := nvl(fnd_profile.value('HXC_VALIDATE_DATA_SET_MAX_ERRORS'),25);
17   END IF;
18 
19   IF p_error_count >= g_error_count THEN
20   --fnd_file.put_line(fnd_file.LOG,'Error count exceeded '||g_error_count);
21     RETURN TRUE;
22   ELSE
23     RETURN FALSE;
24   END IF;
25 
26 END check_max_errors;
27 
28 
29 ----------------------------------------------------------------------------
30 -- Function Name : check_data_corruption
31 -- Description : This function checks for any data corruptions.
32 --               It is before called at the start of Archive process.
33 -- Returns true in case of data corruption; else false
34 ----------------------------------------------------------------------------
35 FUNCTION check_data_corruption(p_data_set_id IN NUMBER)
36 RETURN BOOLEAN
37 IS
38 
39 CURSOR c_timecard_template(p_data_set_id NUMBER) IS
40 SELECT DISTINCT temp.time_building_block_id
41 FROM hxc_time_building_blocks temp, hxc_data_sets d
42 WHERE temp.scope = 'TIMECARD_TEMPLATE'
43 AND EXISTS (SELECT 1 FROM hxc_time_building_blocks tc
44 	    WHERE tc.scope = 'TIMECARD'
45 	      AND tc.time_building_block_id = temp.time_building_block_id
46 	      )
47 AND temp.stop_time BETWEEN d.start_date AND d.end_date
48 AND d.data_set_id = p_data_set_id;
49 
50 l_data_corruption BOOLEAN;
51 l_dummy 	  NUMBER;
52 
53 begin
54 
55   l_data_corruption := FALSE;
56 
57   --1. check if any timecard and template share the same time building block id
58   OPEN c_timecard_template(p_data_set_id);
59   FETCH c_timecard_template INTO l_dummy;
60   IF c_timecard_template%found
61   THEN
62 
63   fnd_file.put_line(fnd_file.LOG, 'The following timecards and templates share the same time building block id');
64     l_data_corruption := TRUE;
65     CLOSE c_timecard_template;
66 
67     FOR l_rec IN c_timecard_template(p_data_set_id) LOOP
68     fnd_file.put_line(fnd_file.LOG,'Common id is:' ||l_rec.time_building_block_id);
69     END LOOP;
70 
71   ELSE
72     CLOSE c_timecard_template;
73   END IF;
74 
75 fnd_file.put_line(fnd_file.LOG, 'Before returning for check_data_corruption function');
76 
77   RETURN l_data_corruption;
78 
79 END check_data_corruption;
80 
81 
82 ----------------------------------------------------------------------------
83 -- Procedure Name : core_table_count_snapshott
84 -- Description : This procedure will give a snapshot of the core tables
85 ----------------------------------------------------------------------------
86 PROCEDURE core_table_count_snapshot
87 				(p_tbb_count	OUT NOCOPY NUMBER,
88 				 p_tau_count	OUT NOCOPY NUMBER,
89 				 p_ta_count	OUT NOCOPY NUMBER,
90 				 p_td_count	OUT NOCOPY NUMBER,
91 				 p_trans_count	OUT NOCOPY NUMBER,
92 				 p_tal_count	OUT NOCOPY NUMBER,
93 				 p_aps_count	OUT NOCOPY NUMBER,
94 				 p_adl_count	OUT NOCOPY NUMBER,
95 				 p_ld_count	OUT NOCOPY NUMBER,
96 				 p_ts_count	OUT NOCOPY NUMBER)
97 
98 IS
99 
100 BEGIN
101 
102   -- Building Blocks table
103   select /*+ index_ffs (t HXC_TIME_BUILDING_BLOCKS_PK ) parallel (t,4)*/ count(time_building_block_id)
104   into p_tbb_count from hxc_time_building_blocks t;
105 
106   -- Attributes Usages
107   select/*+ index_ffs (t HXC_TIME_ATTRIBUTE_USAGES_PK ) parallel (t,4)*/ count(time_attribute_usage_id)
108   into p_tau_count from hxc_time_attribute_usages t;
109 
110   -- Attributes
111   select /*+ index_ffs (t HXC_TIME_ATTRIBUTES_PK ) parallel (t,4)*/ count(time_attribute_id)
112   into p_ta_count from hxc_time_attributes t;
113 
114   -- Transaction Details
115   select /*+ index_ffs (t HXC_TRANSACTION_DETAILS_PK ) parallel (t,4)*/ count(transaction_detail_id)
116   into p_td_count from hxc_transaction_details t;
117 
118   -- Transactions
119   select /*+ index_ffs (t HXC_TRANSACTIONS_PK ) parallel (t,4)*/ count(transaction_id)
120   into p_trans_count from hxc_transactions t;
121 
122   -- Timecard Approver Links
123   select /*+ index_ffs (t HXC_TC_AP_LINKS_PK ) parallel (t,4)*/ count(timecard_id)
124   into p_tal_count from hxc_tc_ap_links t;
125 
126   -- Application Period Summary
127   select /*+ index_ffs (t HXC_APP_PERIOD_SUMMARY_PK ) parallel (t,4)*/ count(application_period_id)
128   into p_aps_count from hxc_app_period_summary t;
129 
130   -- Approval Detail Links
131   select /*+ index_ffs (t HXC_AP_DETAIL_LINKS_PK ) parallel (t,4)*/ count(application_period_id)
132   into p_adl_count from hxc_ap_detail_links t;
133 
134   -- Latest Details
135   select /*+ index_ffs (t HXC_LATEST_DETAILS_FK ) parallel (t,4)*/ count(time_building_block_id)
136   into p_ld_count from hxc_latest_details t;
137 
138   -- Timecard Summary
139   select /*+ index_ffs (t HXC_TIMECARD_SUMMARY_PK ) parallel (t,4)*/ count(timecard_id)
140   into p_ts_count from hxc_timecard_summary t;
141 
142   -- Create the report
143 fnd_file.put_line(fnd_file.LOG,'--------------------------------------');
144 fnd_file.put_line(fnd_file.LOG,' Core Table Count Snapshot');
145 fnd_file.put_line(fnd_file.LOG,'--------------------------------------');
146 fnd_file.put_line(fnd_file.LOG,'-> hxc_time_building_blocks '||p_tbb_count);
147 fnd_file.put_line(fnd_file.LOG,'-> hxc_time_attribute_usages '||p_tau_count);
148 fnd_file.put_line(fnd_file.LOG,'-> hxc_time_attributes '||p_ta_count);
149 fnd_file.put_line(fnd_file.LOG,'-> hxc_tansaction_details '||p_td_count);
150 fnd_file.put_line(fnd_file.LOG,'-> hxc_transactions '||p_trans_count);
151 fnd_file.put_line(fnd_file.LOG,'-> hxc_tc_ap_links '||p_tal_count);
152 fnd_file.put_line(fnd_file.LOG,'-> hxc_app_period_summary '||p_aps_count);
153 fnd_file.put_line(fnd_file.LOG,'-> hxc_ap_detail_links '||p_adl_count);
154 fnd_file.put_line(fnd_file.LOG,'-> hxc_latest_details '||p_ld_count);
155 fnd_file.put_line(fnd_file.LOG,'-> hxc_timecard_summary '||p_ts_count);
156 
157 
158 END core_table_count_snapshot;
159 
160 ----------------------------------------------------------------------------
161 -- Procedure Name : bkup_table_count_snapshot
162 -- Description : This procedure will give a snapshot of the bkup tables
163 ----------------------------------------------------------------------------
164 PROCEDURE bkup_table_count_snapshot
165 				(p_tbb_ar_count	OUT NOCOPY NUMBER,
166 				 p_tau_ar_count	OUT NOCOPY NUMBER,
167 				 p_ta_ar_count	OUT NOCOPY NUMBER,
168 				 p_td_ar_count	OUT NOCOPY NUMBER,
169 				 p_trans_ar_count	OUT NOCOPY NUMBER,
170 				 p_tal_ar_count	OUT NOCOPY NUMBER,
171 				 p_adl_ar_count	OUT NOCOPY NUMBER,
172 				 p_aps_ar_count	OUT NOCOPY NUMBER)
173 
174 IS
175 
176 BEGIN
177 
178   -- Building Blocks table
179   select /*+ index_ffs (t HXC_TIME_BUILDING_BLOCKS_AR_PK ) parallel (t,4)*/ count(time_building_block_id)
180   into p_tbb_ar_count from hxc_time_building_blocks_ar t;
181 
182   -- Attributes Usages
183   select/*+ index_ffs (t HXC_TIME_ATTR_USAGES_AR_PK ) parallel (t,4)*/ count(time_attribute_usage_id)
184   into p_tau_ar_count from hxc_time_attribute_usages_ar t;
185 
186   -- Attributes
187   select /*+ index_ffs (t HXC_TIME_ATTRIBUTES_AR_PK ) parallel (t,4)*/ count(time_attribute_id)
188   into p_ta_ar_count from hxc_time_attributes_ar t;
189 
190   -- Transaction Details
191   select /*+ index_ffs (t HXC_TRANS_DETAILS_AR_PK ) parallel (t,4)*/ count(transaction_detail_id)
192   into p_td_ar_count from hxc_transaction_details_ar t;
193 
194   -- Transactions
195   select /*+ index_ffs (t HXC_TRANSACTIONS_AR_PK ) parallel (t,4)*/ count(transaction_id)
196   into p_trans_ar_count from hxc_transactions_ar t;
197 
198   -- Timecard Approver Links
199   select /*+ index_ffs (t HXC_TC_AP_LINKS_AR_PK ) parallel (t,4)*/ count(timecard_id)
200   into p_tal_ar_count from hxc_tc_ap_links_ar t;
201 
202   -- Application Period Detail links
203   select /*+ index_ffs (t HXC_AP_DETAIL_LINKS_AR_PK ) parallel (t,4)*/ count(application_period_id)
204   into p_adl_ar_count from hxc_ap_detail_links_ar t;
205 
206   -- Application Period Summary
207   select /*+ index_ffs (t HXC_APP_PERIOD_SUMMARY_AR_PK ) parallel (t,4)*/ count(application_period_id)
208   into p_aps_ar_count from hxc_app_period_summary_ar t;
209 
210 
211   -- Create the report
212 fnd_file.put_line(fnd_file.LOG,'--------------------------------------');
213 fnd_file.put_line(fnd_file.LOG,' Archive Table Count Snapshot');
214 fnd_file.put_line(fnd_file.LOG,'--------------------------------------');
215 fnd_file.put_line(fnd_file.LOG,'-> hxc_time_building_blocks_ar '||p_tbb_ar_count);
216 fnd_file.put_line(fnd_file.LOG,'-> hxc_time_attribute_usages_ar '||p_tau_ar_count);
217 fnd_file.put_line(fnd_file.LOG,'-> hxc_time_attributes_ar '||p_ta_ar_count);
218 fnd_file.put_line(fnd_file.LOG,'-> hxc_tansaction_details_ar '||p_td_ar_count);
219 fnd_file.put_line(fnd_file.LOG,'-> hxc_transactions_ar '||p_trans_ar_count);
220 fnd_file.put_line(fnd_file.LOG,'-> hxc_tc_ap_links_ar '||p_tal_ar_count);
221 fnd_file.put_line(fnd_file.LOG,'-> hxc_ap_detail_links_ar '||p_adl_ar_count);
222 fnd_file.put_line(fnd_file.LOG,'-> hxc_app_period_summary_ar '||p_aps_ar_count);
223 
224 
225 END bkup_table_count_snapshot;
226 
227 
228 ----------------------------------------------------------------------------
229 -- Procedure Name : count_chunk_check
230 -- Description : This procedure will give a snapshot of the bkup tables
231 ----------------------------------------------------------------------------
232 PROCEDURE count_chunk_check
233                           (p_tc_ar_count		IN NUMBER,
234 			   p_day_ar_count		IN NUMBER,
235 			   p_detail_ar_count		IN NUMBER,
236 			   p_app_period_ar_count	IN NUMBER,
237 			   p_tau_ar_count		IN NUMBER,
238 			   p_td_ar_count		IN NUMBER,
239 			   p_trans_ar_count		IN NUMBER,
240 			   p_tal_ar_count		IN NUMBER,
241 			   p_adl_ar_count		IN NUMBER,
242 			   p_app_period_sum_ar_count	IN NUMBER,
243 			   p_tbb_count			IN NUMBER,
244 			   p_app_period_count		IN NUMBER,
245 			   p_tau_count			IN NUMBER,
246 			   p_td_count			IN NUMBER,
247 			   p_trans_count		IN NUMBER,
248 			   p_tal_count			IN NUMBER,
249 			   p_adl_count			IN NUMBER,
250 			   p_app_period_sum_count	IN NUMBER)
251 			   IS
252 
253 
254 
255 BEGIN
256 
257 fnd_file.put_line(fnd_file.LOG,' ================================= ');
258 fnd_file.put_line(fnd_file.LOG,' ====== Chunk count ============== ');
259 fnd_file.put_line(fnd_file.LOG,' ================================= ');
260 fnd_file.put_line(fnd_file.LOG,' --- > p_tc_ar_count :'||p_tc_ar_count);
261 fnd_file.put_line(fnd_file.LOG,' --- > p_day_ar_count :'||p_day_ar_count );
262 fnd_file.put_line(fnd_file.LOG,' --- > p_detail_ar_count :'||p_detail_ar_count);
263 fnd_file.put_line(fnd_file.LOG,' --- > p_app_period_ar_count :'||p_app_period_ar_count);
264 fnd_file.put_line(fnd_file.LOG,' --- > p_tau_ar_count :'||p_tau_ar_count);
265 fnd_file.put_line(fnd_file.LOG,' --- > p_td_ar_count :'||p_td_ar_count);
266 --fnd_file.put_line(fnd_file.LOG,' --- > p_trans_ar_count :'||p_trans_ar_count);
267 fnd_file.put_line(fnd_file.LOG,' --- > p_tal_ar_count :'||p_tal_ar_count);
268 fnd_file.put_line(fnd_file.LOG,' --- > p_adl_ar_count :'||p_adl_ar_count);
269 fnd_file.put_line(fnd_file.LOG,' --- > p_app_period_sum_ar_count :'||p_app_period_sum_ar_count);
270 fnd_file.put_line(fnd_file.LOG,' --- > p_tbb_count :'||p_tbb_count);
271 fnd_file.put_line(fnd_file.LOG,' --- > p_app_period_count :'||p_app_period_count);
272 fnd_file.put_line(fnd_file.LOG,' --- > p_tau_count :'||p_tau_count);
273 fnd_file.put_line(fnd_file.LOG,' --- > p_td_count :'||p_td_count);
274 fnd_file.put_line(fnd_file.LOG,' --- > p_trans_count :'||p_trans_count);
275 fnd_file.put_line(fnd_file.LOG,' --- > p_tal_count :'||p_tal_count);
276 fnd_file.put_line(fnd_file.LOG,' --- > p_adl_count :'||p_adl_count);
277 fnd_file.put_line(fnd_file.LOG,' --- > p_app_period_sum_count :'||p_app_period_sum_count);
278 
279 
280   IF (p_tc_ar_count + p_day_ar_count + p_detail_ar_count) <> p_tbb_count THEN
281 fnd_file.put_line(fnd_file.LOG,'==> THE TIMECARD, DAY , DETAIL COUNT FAILED ');
282     RAISE hxc_archive_restore_process.e_chunk_count;
283   END IF;
284 
285   IF p_app_period_ar_count <> p_app_period_count THEN
286 fnd_file.put_line(fnd_file.LOG,'==> THE APPLICATION PERIOD COUNT FAILED');
287     RAISE hxc_archive_restore_process.e_chunk_count;
288   END IF;
289 
290   IF p_tau_ar_count <> p_tau_count THEN
291 fnd_file.put_line(fnd_file.LOG,'==> THE TIME ATTRIBUTE USAGES COUNT FAILED');
292     RAISE hxc_archive_restore_process.e_chunk_count;
293   END IF;
294 
295   IF p_td_ar_count <> p_td_count THEN
296 fnd_file.put_line(fnd_file.LOG,'==> THE TRANSACTION DETAILS COUNT FAILED');
297     RAISE hxc_archive_restore_process.e_chunk_count;
298   END IF;
299 
300 --  IF p_trans_ar_count <> p_trans_count THEN
301 --fnd_file.put_line(fnd_file.LOG,' The Transaction count failed ');
302 --    RAISE hxc_archive_restore_process.e_chunk_count;
303 --  END IF;
304 
305   IF p_tal_ar_count <> p_tal_count THEN
306 fnd_file.put_line(fnd_file.LOG,'==> THE TIMECARD APPLICATION PERIODS COUNT FAILED');
307     RAISE hxc_archive_restore_process.e_chunk_count;
308   END IF;
309 
310   IF p_adl_ar_count <> p_adl_count THEN
311 fnd_file.put_line(fnd_file.LOG,'==> THE APPLICATION PERIODS - DETAILS BB COUNT FAILED');
312     RAISE hxc_archive_restore_process.e_chunk_count;
313   END IF;
314 
315   IF p_app_period_sum_ar_count <> p_app_period_sum_count THEN
316 fnd_file.put_line(fnd_file.LOG,'==> THE APPLICATION PERIOD SUMMARY COUNT FAILED');
317     RAISE hxc_archive_restore_process.e_chunk_count;
318   END IF;
319 
320 END count_chunk_check;
321 
322 
323 ----------------------------------------------------------------------------
324 -- Procedure Name : count_snapshot_check
325 -- Description : This procedure will give a snapshot of the bkup tables
326 ----------------------------------------------------------------------------
327 
328 PROCEDURE count_snapshot_check	(p_tbb_count_1		IN  NUMBER,
329 				 p_tau_count_1		IN NUMBER,
330 				 p_ta_count_1		IN NUMBER,
331 				 p_td_count_1		IN NUMBER,
332 				 p_trans_count_1	IN NUMBER,
333 				 p_tal_count_1		IN NUMBER,
334 				 p_aps_count_1		IN NUMBER,
335 				 p_adl_count_1		IN NUMBER,
336 				 p_ld_count_1		IN NUMBER,
337 				 p_ts_count_1		IN NUMBER,
338 				 p_tbb_ar_count_1	IN NUMBER,
339 				 p_tau_ar_count_1	IN NUMBER,
340 				 p_ta_ar_count_1	IN NUMBER,
341 				 p_td_ar_count_1	IN NUMBER,
342 				 p_trans_ar_count_1	IN NUMBER,
343 				 p_tal_ar_count_1	IN NUMBER,
344 				 p_adl_ar_count_1	IN NUMBER,
345 				 p_aps_ar_count_1	IN NUMBER,
346 				 p_tbb_count_2		IN NUMBER,
347 				 p_tau_count_2		IN NUMBER,
348 				 p_ta_count_2		IN NUMBER,
349 				 p_td_count_2		IN NUMBER,
350 				 p_trans_count_2	IN NUMBER,
351 				 p_tal_count_2		IN NUMBER,
352 				 p_aps_count_2		IN NUMBER,
353 				 p_adl_count_2		IN NUMBER,
354 				 p_ld_count_2		IN NUMBER,
355 				 p_ts_count_2		IN NUMBER,
356 				 p_tbb_ar_count_2	IN NUMBER,
357 				 p_tau_ar_count_2	IN NUMBER,
358 				 p_ta_ar_count_2	IN NUMBER,
359 				 p_td_ar_count_2	IN NUMBER,
360 				 p_trans_ar_count_2	IN NUMBER,
361 				 p_tal_ar_count_2	IN NUMBER,
362 				 p_adl_ar_count_2	IN NUMBER,
363 				 p_aps_ar_count_2	IN NUMBER,
364 				 retcode		OUT NOCOPY NUMBER)
365 IS
366 
367 l_dummy			VARCHAR2(1);
368 l_att_diff_check	NUMBER;
369 
370 BEGIN
371 
372 fnd_file.put_line(fnd_file.LOG,'----------------------------------------');
373 fnd_file.put_line(fnd_file.LOG,'------ COUNT CHECK ON SNAPSHOT  --------');
374 fnd_file.put_line(fnd_file.LOG,'----------------------------------------');
375 fnd_file.put_line(fnd_file.LOG,'---   Building Blocks Table Count ------');
376 fnd_file.put_line(fnd_file.LOG,'--- > p_tbb_count_1  '||p_tbb_count_1);
377 fnd_file.put_line(fnd_file.LOG,'--- > p_tbb_count_2 '|| p_tbb_count_2);
378 fnd_file.put_line(fnd_file.LOG,'--- > p_tbb_ar_count_1 '||p_tbb_ar_count_1);
379 fnd_file.put_line(fnd_file.LOG,'--- > p_tbb_ar_count_2 '||p_tbb_ar_count_2);
380 fnd_file.put_line(fnd_file.LOG,'--- > Difference core '||(p_tbb_count_1 - p_tbb_count_2));
381 fnd_file.put_line(fnd_file.LOG,'--- > Difference backup '||(p_tbb_ar_count_2 - p_tbb_ar_count_1));
382 
383   -- we can check that all the time building block have been transfered
384   IF (p_tbb_count_1 - p_tbb_count_2) <> (p_tbb_ar_count_2 - p_tbb_ar_count_1)
385   THEN
386     --error
387     fnd_file.put_line(fnd_file.LOG,'=== > COPY FAILED ON THE TIME BUILDING BLOCK');
388     retcode := 2;
389     --RETURN;
390   END IF;
391 
392 fnd_file.put_line(fnd_file.LOG,'-----------------------------------');
393 fnd_file.put_line(fnd_file.LOG,'---   Attribute Usages Count ------');
394 fnd_file.put_line(fnd_file.LOG,'--- > p_tau_count_1 '||p_tau_count_1);
395 fnd_file.put_line(fnd_file.LOG,'--- > p_tau_count_2 '||p_tau_count_2);
396 fnd_file.put_line(fnd_file.LOG,'--- > p_tau_ar_count_1  '||p_tau_ar_count_1);
397 fnd_file.put_line(fnd_file.LOG,'--- > p_tau_ar_count_2  '||p_tau_ar_count_2);
398 fnd_file.put_line(fnd_file.LOG,'--- > Difference core '||(p_tau_count_1 - p_tau_count_2));
399 fnd_file.put_line(fnd_file.LOG,'--- > Difference backup '||(p_tau_ar_count_2 - p_tau_ar_count_1));
400 
401   -- we can check that all the attributes usages have been transfered
402   IF (p_tau_count_1 - p_tau_count_2) <> (p_tau_ar_count_2 - p_tau_ar_count_1)
403   THEN
404     --error
405     fnd_file.put_line(fnd_file.LOG,'=== > COPY FAILED ON THE ATTRIBUTE USAGES BLOCK');
406     retcode := 2;
407     --RETURN;
408   END IF;
409 
410 fnd_file.put_line(fnd_file.LOG,'-----------------------------');
411 fnd_file.put_line(fnd_file.LOG,'---   Attribute  Count ------');
412 fnd_file.put_line(fnd_file.LOG,'--- > p_ta_count_1 '||p_ta_count_1);
413 fnd_file.put_line(fnd_file.LOG,'--- > p_ta_count_2 '||p_ta_count_2);
414 fnd_file.put_line(fnd_file.LOG,'--- > p_ta_ar_count_1  '||p_ta_ar_count_1);
415 fnd_file.put_line(fnd_file.LOG,'--- > p_ta_ar_count_2  '||p_ta_ar_count_2);
416 fnd_file.put_line(fnd_file.LOG,'--- > Difference core '||(p_ta_count_1 - p_ta_count_2));
417 fnd_file.put_line(fnd_file.LOG,'--- > Difference backup '||(p_ta_ar_count_2 - p_ta_ar_count_1));
418 
419   -- for the attributes we need to check more if the attributes count is different
420   -- since we can copy consolidated attributes without deleting them from the core
421   -- table
422   IF (p_ta_count_1 - p_ta_count_2) <> (p_ta_ar_count_2 - p_ta_ar_count_1)
423   THEN
424     -- warning
425     fnd_file.put_line(fnd_file.LOG,'=== > SOME ATTRIBUTES WERE SHARED ');
426     retcode := 1;
427 
428     SELECT /*+ index_ffs (t HXC_TIME_ATTRIBUTES_PK ) parallel (4)*/ count(ta.time_attribute_id)
429     INTO l_att_diff_check
430     FROM hxc_time_attributes ta, hxc_time_attributes_ar tabkup
431     WHERE ta.time_attribute_id = tabkup.time_attribute_id
432     AND EXISTS
433     (SELECT 1 FROM hxc_time_attribute_usages tau
434      WHERE tau.time_attribute_id = ta.time_attribute_id);
435 
436     --l_att_diff_check := sql%rowcount;
437 
438 fnd_file.put_line(fnd_file.LOG,'--- > Difference core - backup '||((p_ta_count_1 - p_ta_count_2) - (p_ta_ar_count_2 - p_ta_ar_count_1)));
439 fnd_file.put_line(fnd_file.LOG,'--- > l_att_diff_check '||l_att_diff_check);
440 
441     IF (abs((p_ta_ar_count_2 - p_ta_ar_count_1)-(p_ta_count_1 - p_ta_count_2)) <> abs(l_att_diff_check))
442     THEN
443       fnd_file.put_line(fnd_file.LOG,'===> > IF THE PROCESS IS ARCHIVE THEN BAD TRANSFER OF ATTRIBUTES');
444 
445     -- we need to add another check to see if the difference is legitimate.
446     /****************** SET TO 2 *****************************/
447       retcode := 1;
448     END IF;
449 
450 
451   END IF;
452 
453 
454 fnd_file.put_line(fnd_file.LOG,'---------------------------------------');
455 fnd_file.put_line(fnd_file.LOG,'---   Transaction Details  Count ------');
456 fnd_file.put_line(fnd_file.LOG,'--- > p_td_count_1 '||p_td_count_1);
457 fnd_file.put_line(fnd_file.LOG,'--- > p_td_count_2 '||p_td_count_2);
458 fnd_file.put_line(fnd_file.LOG,'--- > p_td_ar_count_1  '||p_td_ar_count_1);
459 fnd_file.put_line(fnd_file.LOG,'--- > p_td_ar_count_2  '||p_td_ar_count_2);
460 fnd_file.put_line(fnd_file.LOG,'--- > Difference core '||(p_td_count_1 - p_td_count_2));
461 fnd_file.put_line(fnd_file.LOG,'--- > Difference backup '||(p_td_ar_count_2 - p_td_ar_count_1));
462 
463   -- we can check that all the transaction details have been transfered
464   IF (p_td_count_1 - p_td_count_2) <> (p_td_ar_count_2 - p_td_ar_count_1)
465   THEN
466     --error
467     fnd_file.put_line(fnd_file.LOG,'=== > COPY FAILED ON THE TRANSACTION DETAILS');
468     retcode := 2;
469     --RETURN;
470   END IF;
471 
472 fnd_file.put_line(fnd_file.LOG,'------------------------------');
473 fnd_file.put_line(fnd_file.LOG,'---   Transaction Count ------');
474 fnd_file.put_line(fnd_file.LOG,'--- > p_trans_count_1 '||p_trans_count_1);
475 fnd_file.put_line(fnd_file.LOG,'--- > p_trans_count_2 '||p_trans_count_2);
476 fnd_file.put_line(fnd_file.LOG,'--- > p_trans_ar_count_1  '||p_trans_ar_count_1);
477 fnd_file.put_line(fnd_file.LOG,'--- > p_trans_ar_count_2  '||p_trans_ar_count_2);
478 fnd_file.put_line(fnd_file.LOG,'--- > Difference core '||(p_trans_count_1 - p_trans_count_2));
479 fnd_file.put_line(fnd_file.LOG,'--- > Difference backup '||(p_trans_ar_count_2 - p_trans_ar_count_1));
480 
481   -- we can check that all the transactions have been transfered
482   IF (p_trans_count_1 - p_trans_count_2) <> (p_trans_ar_count_2 - p_trans_ar_count_1)
483   THEN
484     --error
485     fnd_file.put_line(fnd_file.LOG,'=== > COPY TRANSACTIONS WERE SHARED');
486     retcode := 1;
487     --RETURN;
488   END IF;
489 
490 fnd_file.put_line(fnd_file.LOG,'----------------------------------------------------');
491 fnd_file.put_line(fnd_file.LOG,'---   Timecard Application Period Links Count ------');
492 fnd_file.put_line(fnd_file.LOG,'--- > p_tal_count_1 '||p_tal_count_1);
493 fnd_file.put_line(fnd_file.LOG,'--- > p_tal_count_2 '||p_tal_count_2);
494 fnd_file.put_line(fnd_file.LOG,'--- > p_tal_ar_count_1  '||p_tal_ar_count_1);
495 fnd_file.put_line(fnd_file.LOG,'--- > p_tal_ar_count_2  '||p_tal_ar_count_2);
496 fnd_file.put_line(fnd_file.LOG,'--- > Difference core '||(p_tal_count_1 - p_tal_count_2));
497 fnd_file.put_line(fnd_file.LOG,'--- > Difference backup '||(p_tal_ar_count_2 - p_tal_ar_count_1));
498 
499   -- we can check that all the timecard application links have been transfered
500   IF (p_tal_count_1 - p_tal_count_2) <> (p_tal_ar_count_2 - p_tal_ar_count_1)
501   THEN
502     --error
503     fnd_file.put_line(fnd_file.LOG,'=== > COPY FAILED ON THE TIMECARD APPLICATION PERIOD LINKS');
504     retcode := 2;
505     --RETURN;
506   END IF;
507 
508 
509 fnd_file.put_line(fnd_file.LOG,'----------------------------------------------------');
510 fnd_file.put_line(fnd_file.LOG,'---   Application Period Detail BB Links Count ------');
511 fnd_file.put_line(fnd_file.LOG,'--- > p_adl_count_1 '||p_adl_count_1);
512 fnd_file.put_line(fnd_file.LOG,'--- > p_adl_count_2 '||p_adl_count_2);
513 fnd_file.put_line(fnd_file.LOG,'--- > p_adl_ar_count_1  '||p_adl_ar_count_1);
514 fnd_file.put_line(fnd_file.LOG,'--- > p_adl_ar_count_2  '||p_adl_ar_count_2);
515 fnd_file.put_line(fnd_file.LOG,'--- > Difference core '||(p_adl_count_1 - p_adl_count_2));
516 fnd_file.put_line(fnd_file.LOG,'--- > Difference backup '||(p_adl_ar_count_2 - p_adl_ar_count_1));
517 
518   -- we can check that all the timecard application links have been transfered
519   IF (p_adl_count_1 - p_adl_count_2) <> (p_adl_ar_count_2 - p_adl_ar_count_1)
520   THEN
521     --error
522     fnd_file.put_line(fnd_file.LOG,'=== > COPY FAILED ON THE APPLICATION PERIOD DETAIL BB LINKS');
523     retcode := 2;
524     --RETURN;
525   END IF;
526 
527 
528 fnd_file.put_line(fnd_file.LOG,'---------------------------------------------');
529 fnd_file.put_line(fnd_file.LOG,'---   Application Period Summary Count ------');
530 fnd_file.put_line(fnd_file.LOG,'--- > p_aps_count_1 '||p_aps_count_1);
531 fnd_file.put_line(fnd_file.LOG,'--- > p_aps_count_2 '||p_aps_count_2);
532 fnd_file.put_line(fnd_file.LOG,'--- > p_aps_ar_count_1  '||p_aps_ar_count_1);
533 fnd_file.put_line(fnd_file.LOG,'--- > p_aps_ar_count_2  '||p_aps_ar_count_2);
534 fnd_file.put_line(fnd_file.LOG,'--- > Difference core '||(p_aps_count_1 - p_aps_count_2));
535 fnd_file.put_line(fnd_file.LOG,'--- > Difference backup '||(p_aps_ar_count_2 - p_aps_ar_count_1));
536 
537   -- we can check that all the application period summary have been transfered
538   IF (p_aps_count_1 - p_aps_count_2) <> (p_aps_ar_count_2 - p_aps_ar_count_1)
539   THEN
540     --error
541     fnd_file.put_line(fnd_file.LOG,'=== > COPY FAILED ON THE APPLICATION PERIOD SUMMARY');
542     retcode := 2;
543     --RETURN;
544   END IF;
545 
546 
547 END count_snapshot_check;
548 
549 ----------------------------------------------------------------------------
550 -- Procedure Name : incompatibility_pg_running
551 -- Description :
552 ----------------------------------------------------------------------------
553 
554 FUNCTION incompatibility_pg_running
555 RETURN BOOLEAN
556 IS
557 
558 l_running_id NUMBER;
559 
560 BEGIN
561 
562   BEGIN
563 
564     SELECT  cr.request_id into l_running_id
565     FROM
566          fnd_concurrent_programs cp,
567          fnd_concurrent_requests cr
568     WHERE cp.concurrent_program_name in ('HXCCATTRB','HXCRESDS','HXCUNDEFDS','HXCDEFDS','HXCARCHDS')
569     AND cp.application_id = 809
570     AND
571       cr.concurrent_program_id = cp.concurrent_program_id AND
572       cr.request_id <> fnd_global.conc_request_id and
573       cr.status_code = 'R';
574 
575   EXCEPTION
576     WHEN NO_DATA_FOUND then
577      RETURN FALSE;
578   END;
579 
580 RETURN TRUE;
581 
582 END incompatibility_pg_running;
583 
584 --updating the wf_notification_attributes with Archival=Yes URL param
585 --while cancelling the notification via archival.
586 --Gets called from hxc_find_notify_aprs_pkg.cancel_notifications procedure.
587 
588 PROCEDURE upd_wf_notif_attributes(p_item_type in varchar2,
589                                   p_item_key  in varchar2) is
590 
591 l_notification_id NUMBER;
592 l_timecard_url wf_notification_attributes.TEXT_VALUE%TYPE;
593 l_temp_timecard_url wf_notification_attributes.TEXT_VALUE%TYPE;
594 l_pos NUMBER;
595 
596 
597 CURSOR c_notif_id(p_item_key in varchar2,p_item_type in varchar2 ) is
598 SELECT DISTINCT notification_id
599 FROM WF_ITEM_ACTIVITY_STATUSES
600 WHERE ITEM_KEY = p_item_key
601 AND  ITEM_TYPE = p_item_type;
602 
603 CURSOR c_timecard_url_value(p_notification_id in varchar2) is
604 SELECT text_value
605 FROM wf_notification_attributes
606 WHERE notification_id = p_notification_id
607 and name = 'TIMECARD';
608 
609 BEGIN
610 
611 OPEN c_notif_id(p_item_key,p_item_type);
612 FETCH c_notif_id into l_notification_id; --Picking up the Notification Id.
613 CLOSE c_notif_id;
614 
615 IF(l_notification_id is not null) THEN
616   open c_timecard_url_value(l_notification_id);
617   fetch c_timecard_url_value into l_timecard_url;
618   close c_timecard_url_value;
619 
620   IF(l_timecard_url is not null) THEN
621     --Setting the New Parameter.
622     -- Just a hack here, because adding the Archived parameter at the last
623     -- doesn't seem to work.
624     -- So spiliting the timecard url and add the Archived parameter just before the
625     -- StartTime param.
626     l_pos := Instr(l_timecard_url,'&StartTime',1);
627 
628     IF(l_pos > 0) THEN --Only if the param exists(A safe check,though it should always exist)
629        l_temp_timecard_url := substr(l_timecard_url,0,(l_pos-1));
630        l_timecard_url :=
631                l_temp_timecard_url||'&Archived=Yes'||substr(l_timecard_url,(l_pos-1));
632 
633        --Updating the Workflow Notification Attributes.
634        update wf_notification_attributes set
635        text_value = l_timecard_url
636        where notification_id = l_notification_id
637         and name = 'TIMECARD';
638 
639        --Updating the Workflow Item Attributes.
640         wf_engine.SetItemAttrText(
641                    itemtype => p_item_type,
642                    itemkey  => p_item_key,
643                    aname    => 'TIMECARD',
644                    avalue   => l_timecard_url);
645      END IF;
646 
647   END IF;
648 END IF;
649 
650 EXCEPTION
651    WHEN OTHERS THEN
652         NULL;
653 
654 END upd_wf_notif_attributes;
655 
656 
657 END hxc_archive_restore_utils;