1 PACKAGE BODY hxc_archive_restore_debug as
2 /* $Header: hxcarcresdbg.pkb 120.0 2005/09/10 03:43:53 psnellin noship $ */
3
4
5
6 --------------------------------------------------------------------------------------------------
7 --Procedure Name : print_timecard_id
8 --Description : This procedure prints all the time building block id corresponding to the chunk
9 -- Which has failed due to some reason.
10 --------------------------------------------------------------------------------------------------
11
12 procedure print_timecard_id(p_data_set_id number,
13 p_start_date date,
14 p_stop_date date) is
15
16 --Curosr which will fetch the time_building_block_id for logging purpose in case
17 --any chunk fails
18 cursor c_get_timecard_for_log(p_data_set_id number,
19 p_start_date date,
20 p_stop_date date) is
21 select tbb.time_building_block_id
22 from hxc_time_building_blocks tbb
23 where scope ='TIMECARD'
24 and data_set_id is null
25 and stop_time between p_start_date and p_stop_date
26 and not exists (
27 select 1 from hxc_tc_ap_links tap, hxc_app_period_summary apbb
28 where tap.timecard_id = tbb.time_building_block_id
29 and apbb.application_period_id = tap.application_period_id
30 and (apbb.start_time < p_start_date or
31 apbb.stop_time > p_stop_date)
32 )
33 and rownum <= nvl(fnd_profile.value('HXC_ARCHIVE_RESTORE_CHUNK_SIZE'),100);
34
35 l_dummy number;
36
37 begin
38
39
40 open c_get_timecard_for_log(p_data_set_id,
41 p_start_date ,
42 p_stop_date) ;
43
44 fnd_file.put_line(fnd_file.LOG,'--- >Printing all the time building block id for timecard scope');
45
46
47 fetch c_get_timecard_for_log into l_dummy;
48 if c_get_timecard_for_log%found then
49 close c_get_timecard_for_log;
50 --print the id of the timecard
51 For l_rec in c_get_timecard_for_log(p_data_set_id,p_start_date,p_stop_date) loop
52 fnd_file.put_line(fnd_file.log,'--- >Time Building Block Id :'||l_rec.time_building_block_id);
53 End Loop;
54 else
55 close c_get_timecard_for_log;
56
57 end if;
58
59
60
61
62 exception
63 when others then
64 fnd_file.put_line(fnd_file.LOG,'--- >Error during Printing timeacrd id: '||sqlerrm);
65 NULL;
66
67 end print_timecard_id;
68
69
70 --------------------------------------------------------------------------------------------------
71 --Procedure Name : print_attributes_id
72 --Description : This procedure prints all the time attribute id which are in consoliadted state
73 -- corresponding to the chunk which has failed due to some reason.
74 --------------------------------------------------------------------------------------------------
75
76 Procedure print_attributes_id(p_data_set_id in number) is
77
78 cursor c_get_consolidated_attributes(p_data_set_id number) is
79 select distinct ta.TIME_ATTRIBUTE_ID
80 from hxc_time_attributes ta, hxc_time_attribute_usages tau
81 where ta.time_attribute_id = tau.time_attribute_id
82 and tau.data_set_id = p_data_set_id
83 and ta.consolidated_flag = 'Y'
84 and ta.data_set_id is null;
85
86 l_dummy number;
87
88
89 begin
90
91 open c_get_consolidated_attributes(p_data_set_id) ;
92
93 fnd_file.put_line(fnd_file.LOG,'--- >Printing all the Consolidate Attribute id');
94
95
96 fetch c_get_consolidated_attributes into l_dummy;
97 if c_get_consolidated_attributes%found then
98 close c_get_consolidated_attributes;
99
100 --print the attribute id
101 For l_rec in c_get_consolidated_attributes(p_data_set_id) loop
102 fnd_file.put_line(fnd_file.log,'--- >Time Attribute Id :'||l_rec.time_attribute_id);
103 End Loop;
104 else
105 close c_get_consolidated_attributes;
106
107 end if;
108
109
110 exception
111 when others then
112 fnd_file.put_line(fnd_file.LOG,'--- >Error during Printing all the consolidated attribute id: '||sqlerrm);
113 NULL;
114
115 end print_attributes_id;
116
117
118
119 procedure print_table_record(p_table_name varchar2,
120 p_data_set_id number,
121 p_first_column varchar2,
122 p_second_column varchar2) is
123
124 type l_record is record(l_first number,l_second number);
125 type p_rec_tab is table of l_record index by binary_integer;
126 p_tab p_rec_tab;
127
128 type c_table_records is REF CURSOR;
129 c_tab_record c_table_records;
130
131 l_sql varchar2(2000);
132 l_count number:=1;
133
134 begin
135
136 l_sql:= 'select '||p_first_column||','||p_second_column||
137 ' from '||p_table_name||
138 ' where data_set_id= '||p_data_set_id||
139 ' and rownum<100 ';
140
141 fnd_file.put_line(fnd_file.LOG,'--- >Before fetching the records into PL/SQL table');
142 open c_tab_record for l_sql;
143 loop
144
145 if(c_tab_record%notfound) then
146 exit;
147 end if;
148
149 fetch c_tab_record into p_tab(l_count);
150 l_count:=l_count+1;
151
152 end loop;
153
154 close c_tab_record;
155
156
157 fnd_file.put_line(fnd_file.LOG,'--- >Printing the record for the table: '||p_table_name);
158 fnd_file.put_line(fnd_file.LOG,'--- >Total count of records in PL/SQL Table: '||l_count);
159 fnd_file.put_line(fnd_file.LOG,' '||p_table_name||' '||p_first_column||' '||p_second_column||' ');
160
161 l_count:=p_tab.first;
162
163 loop exit when not p_tab.exists(l_count);
164
165 fnd_file.put_line(fnd_file.LOG,' '||p_table_name||' '||p_tab(l_count).l_first||' '||p_tab(l_count).l_second||' ');
166
167 l_count:=p_tab.next(l_count);
168 end loop;
169
170 fnd_file.put_line(fnd_file.LOG,'-------------------------------------');
171
172 exception when others then
173 fnd_file.put_line(fnd_file.LOG,'--- >Error during coying the data in table: '||sqlerrm);
174 null;
175
176 END print_table_record;
177
178 END hxc_archive_restore_debug;
[Home] [Help]