[Home] [Help]
PACKAGE BODY: APPS.HR_SUM_STORE
Source
1 package body hr_sum_store as
2 /* $Header: hrsumsto.pkb 115.8 2003/05/22 10:36:03 jheer noship $ */
3 --
4 procedure store_data (p_business_group_id in number,
5 p_item_name in varchar2,
6 p_itu_name in varchar2,
7 p_item_type_usage_id in number,
8 p_count_clause1 in varchar2,
9 p_count_clause2 in varchar2,
10 p_stmt in varchar2,
11 p_debug in varchar2,
12 p_key_col_clause in varchar2,
13 p_error out nocopy number ) is
14
15 l_key_value hr_summary_key_value.name%type;
16 l_item_value number;
17 l_item_value_char varchar2(240);
18 l_item_value2 number;
19 z number;
20 j number;
21 l_key_value_id number;
22 l_error_mesg varchar2(100);
23 l_error boolean;
24 l_item_value_id number;
25 l_object_version_number number;
26 source_cursor integer;
27 ignore integer;
28 l_new_stmt long;
29 l_count_clause_columns varchar2(4000);
30 l_columns_to_append long := ' ';
31 l_columns_required number :=0;
32 l_full_name_avail varchar2(1);
33 l_start_index number;
34
35 l_concat_debug_string varchar2(4000);
36 l_pos1 number;
37 l_pos2 number;
38 TYPE ColNameRecType IS RECORD
39 (col_name long);
40 TYPE ColNameTabType IS TABLE of ColNameRecType INDEX BY BINARY_INTEGER;
41
42 ColNameTab ColNameTabType;
43
44 --
45
46 begin
47 --
48 hr_utility.set_location('Entering: hr_sum_store.store_data', 10);
49 --
50 p_error := 0; /* Default error parameter to zero to indicate success */
51 source_cursor := dbms_sql.open_cursor;
52 --
53 -- Define column 1 as the count column
54 --
55
56 --
57 -- If p_debug = 'Y' change the statement to select just the name
58 --
59 hr_utility.trace ('convert statement');
60 -- the following have multiple group bys' which are currently not handled by debug.
61 if p_debug = 'Y' and p_item_name not in ('10_HIGHEST_REMUNERATION',
62 '10_PC_HIGHEST_REMUNERATION',
63 '10_PC_LOWEST_REMUNERATION',
64 -- 'NEW_HIRE',
65 'REMUNERATION_BREAKDOWN')
66 then
67 if p_item_name = 'NEW_HIRE' then
68 l_full_name_avail := 'N';
69 else
70 l_full_name_avail := 'Y';
71 end if;
72 l_new_stmt := p_stmt;
73 -- find the columns to append from the comment
74 l_columns_to_append := ' ';
75 l_columns_required := 0;
76 if instr(l_new_stmt,'DBGCOLS',1) <> 0 then
77 l_columns_to_append := ',' || substr(l_new_stmt,(instr(l_new_stmt,concat('/','*DBG'),1)+6),(
78 instr(l_new_stmt,'DBGCOLS',1) - (instr(l_new_stmt,concat('/','*DBG'),1)+6)));
79 l_columns_required := to_number(nvl(substr(l_new_stmt,(instr(l_new_stmt,'DBGCOLS',1)+7),1),'0'));
80 hr_utility.trace('cols required = ' || l_columns_required);
81 end if;
82
83 -- Determine the names of the appended debug columns so that they can be output with the value
84 -- the column names follow the DBGCOLS text and are should appear in the corect order
85 l_pos1 := instr(l_new_stmt,'DBGCOLS',1);
86 l_pos2 := instr(l_new_stmt,' ',l_pos1); -- find the position of the space preceeding the 1st col
87 hr_utility.trace('_pos1 = ' || l_pos1 || ' ' || l_pos2);
88 for i in 0..(l_columns_required-1) loop
89 hr_utility.trace('colname asssign');
90 ColNameTab(i).col_name := substr(l_new_stmt,(l_pos2+1),instr(l_new_stmt,' ',(l_pos2+1))-(l_pos2));
91 l_pos2 := instr(l_new_stmt,' ',l_pos2+1);
92 end loop;
93
94 l_new_stmt := substr(l_new_stmt,instr(l_new_stmt,'from',1),length(l_new_stmt));
95 if instr(l_new_stmt,' group by',1) <> 0 then
96 l_new_stmt := substr(l_new_stmt,1,instr(l_new_stmt,' group by',1));
97 if l_full_name_avail = 'Y' then
98 l_new_stmt := 'Select distinct p.full_name' || l_columns_to_append || nvl(p_key_col_clause,' ') ||
99 l_new_stmt;
100 else
101 l_new_stmt := 'Select distinct ''full_name unavail'' ' || l_columns_to_append || nvl(p_key_col_clause,' ') ||
102 l_new_stmt;
103 end if;
104 else
105 if l_full_name_avail = 'N' then
106 l_new_stmt := 'Select distinct ''full_name unavail'' ' || l_columns_to_append || l_new_stmt;
107 else
108 l_new_stmt := 'Select distinct p.full_name' || l_columns_to_append || l_new_stmt;
109 end if;
110 end if;
111
112 else
113 l_new_stmt := p_stmt;
114 end if;
115
116 if p_debug = 'Y' then
117 hrsumrep.write_stmt_log(p_stmt => 'new stmt is' || l_new_stmt);
118 hrsumrep.write_stmt_log('ITEM NAME = ' || p_itu_name || ' ' || p_item_name);
119 hr_utility.trace('ITEM NAME = ' || p_itu_name || ' ' || p_item_name);
120 end if;
121
122 hr_utility.trace ('complete convert statement');
123
124
125 <<dynamic_block>>
126 begin
127 l_error := false;
128 l_error_mesg := fnd_message.get_string('PER','PER_74874_PARSE_ERROR')||' '||p_item_name;
129 -- dbms_sql.parse(source_cursor,p_stmt,dbms_sql.v7);
130 hr_utility.trace('parse stmt');
131 dbms_sql.parse(source_cursor,l_new_stmt,dbms_sql.v7);
132 hr_utility.trace('complete parse stmt');
133 l_error_mesg := null;
134 fnd_message.set_name('PER','PER_74877_DEFINE_COLUMN');
135 fnd_message.set_token('NUM','1');
136 l_error_mesg := fnd_message.get;
137 If p_debug = 'Y' Then
138 dbms_sql.define_column(source_cursor,1,l_item_value_char,240);
139 Else
140 dbms_sql.define_column(source_cursor,1,l_item_value);
141 End If;
142 l_error_mesg := null;
143 --
144 if p_debug <> 'Y' then
145 if p_count_clause2 is not null then
146 fnd_message.set_token('NUM','2');
147 l_error_mesg := fnd_message.get;
148 dbms_sql.define_column(source_cursor,2,l_item_value2);
149 l_error_mesg := null;
150 --
151 -- Define subsequent columns based on the group by columns
152 --
153 j := 3;
154 else
155 j := 2;
156 end if;
157 --
158 if hrsumrep.ktyTab.count > 0 then
159 for i in hrsumrep.ktyTab.first..hrsumrep.ktyTab.last loop
160 fnd_message.set_token('NUM',to_char(j));
161 l_error_mesg := fnd_message.get;
162 dbms_sql.define_column(source_cursor,j,l_key_value,80);
163 l_error_mesg := null;
164 j := j + 1;
165 end loop;
166 end if;
167
168 else -- if debug
169 j := 2;
170 -- if hrsumrep.ktyTab.count > 0 then
171 if hrsumrep.ktyTab.count > 0 or l_columns_required > 0 then
172 l_columns_required := l_columns_required + nvl(hrsumrep.ktyTab.last,0);
173 hr_utility.trace('l_columns_required after adding is ' || l_columns_required);
174 --for i in hrsumrep.ktyTab.first..hrsumrep.ktyTab.last loop
175 for i in nvl(hrsumrep.ktyTab.first,1)..l_columns_required loop
176 fnd_message.set_token('NUM',to_char(j));
177 l_error_mesg := fnd_message.get;
178 hr_utility.trace('define col');
179 dbms_sql.define_column(source_cursor,j,l_key_value,80);
180 hr_utility.trace('define col2');
181 l_error_mesg := null;
182 j := j + 1;
183 end loop;
184 end if;
185 end if;
186 --
187 l_error_mesg := fnd_message.get_string('PER','PER_74875_EXECUTE_ERROR')||' '||p_item_name;
188 ignore := dbms_sql.execute(source_cursor);
189 l_error_mesg := null;
190 --
191 z := 0;
192 loop
193 if dbms_sql.fetch_rows(source_cursor) > 0 then
194 fnd_message.set_name('PER','PER_74878_COLUMN_VALUE');
195 fnd_message.set_token('NUM','1');
196 l_error_mesg := fnd_message.get;
197
198 if p_debug = 'Y' then
199 hr_utility.trace(' col value');
200 dbms_sql.column_value(source_cursor,1,l_item_value_char);
201 hrsumrep.write_stmt_log(p_stmt => null);
202 hrsumrep.write_stmt_log(p_stmt => '***** PERSON NAME is ' || l_item_value_char);
203 else
204 dbms_sql.column_value(source_cursor,1,l_item_value);
205 end if;
206
207 l_error_mesg := null;
208
209 if p_debug <> 'Y' then
210
211 if p_count_clause2 is not null then
212 fnd_message.set_token('NUM','2');
213 l_error_mesg := fnd_message.get;
214 dbms_sql.column_value(source_cursor,2,l_item_value2);
215 l_error_mesg := null;
216 end if;
217 --
218 -- Populate the Item Value Row
219 --
220 hr_summary_api.create_item_value(p_item_value_id => l_item_value_id
221 ,p_business_group_id => p_business_group_id
222 ,p_object_version_number => l_object_version_number
223 ,p_process_run_id => hr_summary_util.process_run_id
224 ,p_item_type_usage_id => p_item_type_usage_id
225 ,p_textvalue => null
226 ,p_numvalue1 => l_item_value
227 ,p_numvalue2 => l_item_value2
228 ,p_datevalue => null);
229 z := z + 1;
230 if p_count_clause2 is null then
231 j := 2;
232 else
233 j := 3;
234 end if;
235 if hrsumrep.ktyTab.count > 0 then
236 for i in hrsumrep.ktyTab.first..hrsumrep.ktyTab.last loop
237 fnd_message.set_token('NUM',to_char(j));
238 l_error_mesg := fnd_message.get;
239 dbms_sql.column_value(source_cursor,j,l_key_value);
240 l_error_mesg := null;
241 --
242 -- Populate the key value
243 --
244 hr_summary_api.create_key_value(p_key_value_id => l_key_value_id
245 ,p_business_group_id => p_business_group_id
246 ,p_object_version_number => l_object_version_number
247 ,p_key_type_id => hrsumrep.ktyTab(i).key_type_id
248 ,p_item_value_id => l_item_value_id
249 ,p_name => l_key_value);
250 --
251 --
252 -- If the value is OTHER then there may not be a
253 -- correpsonding zero item row (this is required in order
254 -- that each element in the multi-dimensional matrix has a
255 -- value
256 --
257 if l_key_value = hr_summary_util.OTHER
258 and not hrsumrep.ktyTab(i).key_other then
259 hr_summary_api.create_key_value(p_key_value_id => l_key_value_id
260 ,p_business_group_id => p_business_group_id
261 ,p_object_version_number => l_object_version_number
262 ,p_key_type_id => hrsumrep.ktyTab(i).key_type_id
263 ,p_item_value_id => hr_summary_util.zero_item_value_id
264 ,p_name => l_key_value);
265 hrsumrep.ktyTab(i).key_other := TRUE;
266 end if;
267 j := j + 1;
268 end loop;
269 end if;
270
271 end if; -- p_debug end if
272
273 If p_debug = 'Y' then
274 j :=2;
275 -- if hrsumrep.ktyTab.count > 0 then
276 hr_utility.trace('col value start');
277 if hrsumrep.ktyTab.count > 0 or l_columns_required > 0 then
278 --l_columns_required := l_columns_required + hrsumrep.ktyTab.count;
279 -- for i in hrsumrep.ktyTab.first..hrsumrep.ktyTab.last loop
280 hr_utility.trace('col value start loop');
281 -- cater for the situation where an item has no keys
282 if hrsumrep.ktyTab.count = 0 then
283 l_start_index :=0;
284 else
285 l_start_index := hrsumrep.ktyTab.first;
286 end if;
287 -- for i in hrsumrep.ktyTab.first..l_columns_required loop
288 l_concat_debug_string := ' ';
289 for i in l_start_index..(l_columns_required-1) loop
290 fnd_message.set_token('NUM',to_char(j));
291 l_error_mesg := fnd_message.get;
292 dbms_sql.column_value(source_cursor,j,l_key_value);
293 l_error_mesg := null;
294 if i < ColNameTab.count then
295 --hrsumrep.write_stmt_log(ColNameTab(i).Col_Name || ' = ' || l_key_value);
296 l_concat_debug_string := l_concat_debug_string || ' ' || ColNameTab(i).Col_Name || ' ' || l_key_value
297 || ',';
298 else
299 --hrsumrep.write_stmt_log(hrsumrep.ktytab(i-ColNameTab.count).key_type || ' = ' || l_key_value);
300 l_concat_debug_string := l_concat_debug_string || ' ' || hrsumrep.ktytab(i-ColNameTab.count).key_type
301 || ' ' || l_key_value || ',';
302 end if;
303 j := j+1;
304 end loop;
305 if l_concat_debug_string is not null and l_concat_debug_string <> ' ' then
306 hrsumrep.write_stmt_log(l_concat_debug_string);
307 end if;
308 end if;
309 end if;
310
311 else -- if fetch_rows > 0
312 exit;
313 end if;
314 end loop;
315 --
316 -- hrsumrep.write_error(p_itu_name||': '||z);
317 --
318 hr_utility.set_location('Leaving: hr_sum_store.store_data', 20);
319 --
320 exception when others then
321 p_error := 1; /* If error occurs set p_error to 1 to indicate error */
322 if l_error_mesg is null then
323 hr_utility.trace('exception in HR_SUM_STORE');
324 hr_utility.trace(SQLCODE);
325 hr_utility.trace(SQLERRM);
326 hrsumrep.write_stmt_log(p_stmt => l_new_stmt);
327 null;
328 else
329 hr_utility.trace('error in HR_SUM_STORE');
330 hrsumrep.write_error(null);
331 hrsumrep.write_error(l_error_mesg);
332 hrsumrep.write_error(sqlerrm);
333 -- hrsumrep.write_stmt_log(p_stmt => p_stmt);
334 hrsumrep.write_stmt_log(p_stmt => l_new_stmt);
335 end if;
336 end dynamic_block;
337 dbms_sql.close_cursor(source_cursor);
338 end store_data;
339
340 end hr_sum_store;