[Home] [Help]
PACKAGE BODY: APPS.HRSUMREP
Source
1 package body hrsumrep as
2 /* $Header: hrsumrep.pkb 115.14 2004/06/21 07:38:31 jheer noship $ */
3 --
4 procedure delete_process_data(p_process_run_id in number) is
5 --
6 begin
7 --
8 delete hr_summary_key_value
9 where item_value_id in (select item_value_id
10 from hr_summary_item_value
11 where process_run_id = p_process_run_id);
12 --
13 delete hr_summary_item_value
14 where process_run_id = p_process_run_id;
15 --
16 delete hr_summary_parameter
17 where process_run_id = p_process_run_id;
18 --
19 delete hr_summary_process_run
20 where process_run_id = p_process_run_id;
21 --
22 end delete_process_data;
23 --
24 procedure write_error (p_error in varchar2) is
25 begin
26 fnd_file.put_line(FND_FILE.LOG,substrb(p_error,1,1024));
27 exception when others then
28 null;
29 end;
30 --
31 procedure write_stmt_log (p_stmt in varchar2) is
32 l_num number:= 1;
33 begin
34 if lengthb(p_stmt) > 1024 then
35 for i in 1..ceil(lengthb(p_stmt)/1024) loop
36 write_error(substrb(p_stmt,l_num,1024));
37 l_num := (i*1024)+1;
38 end loop;
39 else
40 write_error(p_stmt);
41 end if;
42 end;
43 --
44 procedure process_run(p_business_group_id number
45 ,p_process_type varchar2
46 ,p_template_id number
47 ,p_process_name varchar2
48 ,p_parameters hr_summary_util.prmTabType
49 ,p_item_type_usage_id number default null
50 ,p_store_data boolean default FALSE
51 ,p_debug varchar2 default 'N'
52 ,p_statement out NOCOPY varchar2
53 ,p_retcode out NOCOPY number) is
54 --
55 cursor c_get_item_type_usage(p_template_id number) is
56 select it.DATATYPE
57 , it.count_clause1
58 , it.count_clause2
59 , it.where_clause
60 , it.name it_name
61 , itu.name itu_name
62 , itu.ITEM_TYPE_USAGE_ID
63 , itu.ITEM_TYPE_ID
64 from hr_summary_item_type_usage itu
65 , hr_summary_item_type it
66 where itu.template_id = p_template_id
67 and itu.ITEM_TYPE_ID = it.ITEM_TYPE_ID
68 and nvl(p_item_type_usage_id,item_type_usage_id) = item_type_usage_id
69 order by itu.sequence_number;
70 --
71 cursor c_get_key_type_usage(p_item_type_usage_id number) is
72 select kty.name
73 , kty.key_function
74 , kty.key_type_id
75 from hr_summary_key_type_usage ktu
76 , hr_summary_valid_key_type vkt
77 , hr_summary_key_type kty
78 where kty.key_type_id = vkt.key_type_id
79 and ktu.valid_key_type_id = vkt.valid_key_type_id
80 and ktu.item_type_usage_id = p_item_type_usage_id;
81 --
82 cursor c_get_restriction_usage(p_item_type_usage_id number) is
83 select srt.data_type
84 , srt.restriction_clause
85 , vru.restriction_type
86 , vru.restriction_usage_id
87 , srt.name
88 from hr_summary_valid_restriction vrt
89 , hr_summary_restriction_usage vru
90 , hr_summary_restriction_type srt
91 where vrt.valid_restriction_id = vru.valid_restriction_id
92 and vru.item_type_usage_id = p_item_type_usage_id
93 and vrt.restriction_type_id = srt.restriction_type_id
94 and srt.name <> 'USER_PERSON_TYPE';
95 --
96 cursor c_get_restriction_value(p_restriction_usage_id number) is
97 select value
98 from hr_summary_restriction_value
99 where restriction_usage_id = p_restriction_usage_id;
100 --
101 l_object_version_number number;
102 l_datatype varchar2(1);
103 l_stmt varchar2(32000);
104 l_item_type_usage_id number;
105 l_count_clause1 varchar2(32000);
106 l_count_clause2 varchar2(32000);
107 l_key_col_clause varchar2(32000);
108 l_where_clause varchar2(32000);
109 l_restriction_clause varchar2(32000);
110 l_restriction_clause2 varchar2(32000);
111 l_restriction_value varchar2(80);
112 l_error_mesg varchar2(100);
113 l_group_clause varchar2(32000);
114 l_key_clause varchar2(32000);
115 l_comma varchar2(1);
116 l_tab_num varchar2(2);
117 l_error boolean;
118 i number;
119 l_itu_error number; /* variable to check if item type usage occurred */
120 --
121 p number;
122 y number;
123 --
124 l_pos1 number;
125 l_pos2 number;
126 l_pos3 number;
127
128 l_alias varchar2(30);
129 l_parameter_alias varchar2(32000); -- require this length for the substr,instr to work
130 l_character_after_brkt varchar2(50);
131 --
132 source_cursor integer;
133 ignore integer;
134 --
135 begin
136 p_retcode :=0; /* Default retcode to zero, to indicate no error */
137
138 -- Set STORE_DATA utility package global
139 --
140 hr_utility.set_location('Entering : hrumrep.process_run ', 5);
141 hr_summary_util.store_data := p_store_data;
142 --
143 -- If the process needs to write data then initialize it by writing
144 -- and process row and parameter rows, otherwise just setup business group
145 -- for bsutil
146 --
147 hr_summary_util.initialize_run(p_store_data
148 ,p_business_group_id
149 ,p_template_id
150 ,p_process_name
151 ,p_process_type
152 ,p_parameters);
153 --
154 /* ------------------------------------------------------------------
155 For each of the item types that are required for the selected template,
156 retrieve the item type details
157 ------------------------------------------------------------------ */
158 i := 1;
159 for itu in c_get_item_type_usage(p_template_id) loop
160 --
161 --
162 ituTab(i).item_type_usage_id := itu.item_type_usage_id;
163 ituTab(i).item_type_id := itu.item_type_id;
164 ituTab(i).datatype := itu.datatype;
165 ituTab(i).count_clause1 := itu.count_clause1;
166 ituTab(i).count_clause2 := itu.count_clause2;
167 ituTab(i).where_clause := itu.where_clause;
168 ituTab(i).it_name := itu.it_name;
169 ituTab(i).itu_name := itu.itu_name;
170 --
171 i := i + 1;
172 --
173 end loop;
174 --
175 /* ------------------------------------------------------------------
176 Fo each item type usage retrieved
177 a) delete any existing item values or key values for the item type usage
178 b) build up the dynamic SQL statement and excute (if appropriate)
179 ------------------------------------------------------------------ */
180 if ituTab.count > 0 then
181 <<itu_loop>>
182 for x in 1..i-1 loop
183 --
184 -- Populate local and global variables
185 --
186 hr_summary_util.item_type_usage_id := ituTab(x).item_type_usage_id;
187 l_item_type_usage_id := ituTab(x).item_type_usage_id;
188 l_datatype := ituTab(x).datatype;
189 --
190 -- Delete existing results where appropriate
191 --
192 /* ------------------------------------------------------------------
193 Need to call Initialize procedure before evaluating the key functions because
194 initialize_procedure populates the zero_item_value_id for the item_type being
195 processed, the key functions subsequently reference it.
196 ------------------------------------------------------------------ */
197 hr_summary_util.initialize_procedure(p_business_group_id);
198 --
199 -- Initialize dynamic SQL components
200 --
201 l_stmt := null;
202 l_count_clause1 := null;
203 l_count_clause2 := null;
204 l_key_col_clause := null;
205 l_where_clause := null;
206 l_restriction_clause := null;
207 l_group_clause := null;
208 --
209 -- Begin determining the dynamic SQL components
210 --
211 hr_utility.trace('processing item ' || itutab(x).itu_name || ' ' || itutab(x).it_name);
212 l_count_clause1 := ituTab(x).count_clause1 || ' col_value1 ';
213 if ituTab(x).count_clause2 is not null then
214 l_count_clause2 := ','||ituTab(x).count_clause2 || ' col_value2 ';
215 end if;
216 --
217 -- Load the Key Types into a PLSQL table
218 --
219 i := 0;
220 ktyTab := nullktyTab;
221 for kty in c_get_key_type_usage(l_item_type_usage_id) loop
222 ktyTab(i).key_type := kty.name;
223 ktyTab(i).key_function := kty.key_function;
224 ktyTab(i).key_type_id := kty.key_type_id;
225 ktyTab(i).key_other := FALSE;
226 i := i + 1;
227 end loop;
228 --
229 /* -----------------------------------------------------------------
230 If there are key types being used
231 a) evaluate the function associated with the key type to return
232 the group by clause
233 b) build up the select clause
234 c) build up the group by clause
235 ----------------------------------------------------------------- */
236 --
237 hr_utility.trace('hrsumrep - keytab');
238 if ktyTab.count > 0 then
239 for i in ktyTab.first..ktyTab.last loop
240 --
241 --
242 l_stmt := 'declare key_type_id number := '||ktyTab(i).key_type_id||';
243 begin :l_clause := '||ktyTab(i).key_function||';
244 end;';
245 source_cursor := dbms_sql.open_cursor;
246 begin
247 l_error_mesg := fnd_message.get_string('PER','PER_74874_PARSE_ERROR');
248 dbms_sql.parse(source_cursor,l_stmt,dbms_sql.v7);
249 dbms_sql.bind_variable(source_cursor,'l_clause',l_key_clause, 32000);
250 l_error_mesg := fnd_message.get_string('PER','PER_74875_EXECUTE_ERROR');
251 ignore := dbms_sql.execute(source_cursor);
252 l_error_mesg := fnd_message.get_string('PER','PER_74876_ASSIGN_VARIABLE');
253 dbms_sql.variable_value(source_cursor,'l_clause',l_key_clause);
254 dbms_sql.close_cursor(source_cursor);
255 exception when others then
256 if p_store_data then
257 p_retcode :=1; /* error occurred in key type. set status to warning */
258 write_error(l_error_mesg);
259 write_error(sqlerrm);
260 --write_stmt_log (p_stmt => l_stmt);
261 exit itu_loop;
262 else
263 null;
264 end if;
265 end;
266 l_key_col_clause := l_key_col_clause || '
267 , '|| l_key_clause || ' '||
268 'col_'||ktyTab(i).key_type ||' ';
269 if l_group_clause is null then
270 l_group_clause := ' group by ';
271 else
272 l_group_clause := l_group_clause ||'
273 ,';
274 end if;
275 --
276 l_group_clause := l_group_clause || l_key_clause;
277 end loop;
278 end if;
279
280 --
281 --
282 hr_utility.trace('hrsumrep - restrictions');
283 /* -----------------------------------------------------------------
284 If there are restrictions being used build up the restriction clause
285 ----------------------------------------------------------------- */
286 --
287 l_where_clause := ituTab(x).where_clause;
288 --
289 for vrt in c_get_restriction_usage(l_item_type_usage_id) loop
290 --
291 l_pos1 := nvl(instr(l_where_clause,vrt.name||'['),0);
292
293 if l_pos1 <> 0 then
294
295 l_pos1 := l_pos1+length(vrt.name)+1;
296 l_character_after_brkt := substr(l_where_clause,l_pos1,16);
297 -- need to concatenate slash and asterisk otherwise check_sql will give error.
298 If l_character_after_brkt = concat('/','*'|| 'parameteralias') then
299 l_restriction_clause := ' and @'||vrt.restriction_clause || ' ' ||
300 vrt.restriction_type || ' ';
301 l_pos1 := l_pos1 + 17;
302 l_pos2 := instr(l_where_clause,(concat('*','/]')),1);
303 l_parameter_alias := substr(l_where_clause,l_pos1,(l_pos2-l_pos1));
304 else
305 l_restriction_clause := ' and @.'||vrt.restriction_clause || ' ' ||
306 vrt.restriction_type || ' ';
307 end if;
308 --
309 hr_utility.trace('hrsumrep - restrictions1');
310 l_comma := '(';
311 for rvl in c_get_restriction_value(vrt.restriction_usage_id) loop
312 if vrt.data_type = 'C' then
313 l_restriction_value := '''' || rvl.value || '''';
314 elsif vrt.data_type = 'D' then
315 l_restriction_value := 'to_date('''|| rvl.value ||
316 ''',''YYYY/MM/DD HH24:MI:SS'')';
317 elsif vrt.data_type = 'N' then
318 l_restriction_value := rvl.value;
319 end if;
320 --
321 if vrt.restriction_type in ('=','<>','>','<') then
322 l_restriction_clause := l_restriction_clause ||
323 l_restriction_value || ' ';
324 exit;
325 else
326 l_restriction_clause := l_restriction_clause || l_comma ||
327 l_restriction_value;
328 l_comma := ',';
329 end if;
330 end loop;
331 --
332 if vrt.restriction_type not in ('=','<>','>','<') then
333 l_restriction_clause := l_restriction_clause || ')';
334 end if;
335 --
336 -- Replace all occurences of RESTRICTION.<restriction_name>
337 -- with the correct alias and restriction clause.
338 loop
339
340
341 l_pos3 := nvl(instr(l_where_clause,vrt.name||'['),0);
342 l_pos3 := l_pos3+length(vrt.name)+1;
343 l_character_after_brkt := substr(l_where_clause,l_pos3,16);
344 If l_character_after_brkt = concat('/','*parameteralias') then
345 l_pos3 := l_pos3 + 17;
346 l_pos2 := instr(l_where_clause,concat('*','/]'),1);
347 l_parameter_alias := substr(l_where_clause,l_pos3,(l_pos2-l_pos3));
348 End if;
349
350 l_pos1 := nvl(instr(l_where_clause,vrt.name||'['),0);
351 exit when l_pos1 = 0;
352 l_pos1 := l_pos1+length(vrt.name)+1;
353 l_pos2 := instr(l_where_clause,']',l_pos1,1);
354 hr_utility.trace('hrsumrep - restrictions1b');
355 hr_utility.trace('l_pos1 = ' || l_pos1 || ' ' || l_pos2);
356 l_alias := substr(l_where_clause,l_pos1,l_pos2-l_pos1);
357 hr_utility.trace('l_alias = ' || l_alias);
358 if l_character_after_brkt = concat('/','*parameteralias') then
359 -- l_alias := ' ';
360 hr_utility.trace('hrsumrep - restrictions1c');
361 l_restriction_clause2 := REPLACE(l_restriction_clause,'aliasreplace',l_parameter_alias);
362 l_restriction_clause2 := REPLACE(l_restriction_clause2,'@',' ');
363 hr_utility.trace('hrsumrep - restrictions1d');
364 -- write_stmt_log (p_stmt => 'l_restric2 = ' || l_restriction_clause2);
365 else
366 hr_utility.trace('hrsumrep - restrictions1e');
367 l_restriction_clause2 := REPLACE(l_restriction_clause,'@',l_alias);
368 end if;
369
370 hr_utility.trace('hrsumrep - restrictions2');
371 If l_character_after_brkt = concat('/','*parameteralias') then
372 l_where_clause := REPLACE(l_where_clause,'RESTRICTION.'||vrt.name||'['||l_alias||']',l_restriction_clause2);
373 --write_stmt_log (p_stmt => 'l_where_cla= ' || l_where_clause);
374 else
375 l_where_clause := REPLACE(l_where_clause,'RESTRICTION.'||vrt.name||'['||l_alias||']',l_restriction_clause2);
376 end if;
377
378 l_restriction_clause2 := null;
379 end loop;
380 end if;
381 end loop;
382 -- Remove all references of RESTRICTION.<whatever> that are not valid
383 -- for this item type.
384 loop
385 l_pos1 := nvl(instr(l_where_clause,'RESTRICTION.'),0);
386 exit when l_pos1 = 0;
387 l_pos2 := instr(l_where_clause,']',l_pos1,1)+1;
388 l_where_clause := substr(l_where_clause,1,l_pos1-1)||substr(l_where_clause,l_pos2,length(l_where_clause));
389 end loop;
390 --
391 hr_utility.trace('hrsumrep - complete restrictions');
392 -- Concatenate the full dynamic statement
393 --
394 l_stmt := 'select '||
395 l_count_clause1 ||
396 l_count_clause2 ||
397 l_key_col_clause || '
398 '||
399 l_where_clause || '
400 '||
401 l_group_clause;
402 --
403 -- If the statement is going to be run any parameter values need to
404 -- be substituted
405 --
406 -- if p_store_data then
407 if p_parameters.count > 0 then
408 for i in p_parameters.first..p_parameters.last loop
409 l_stmt := replace(l_stmt
410 ,p_parameters(i).name
411 ,p_parameters(i).value);
412 end loop;
413 end if;
414 -- end if;
415 --
416 -- if we are running debug then we will need to substitute the parameters separately for
417 -- the key col clause as we pass this as a separate parameter
418
419 if p_debug = 'Y' and p_parameters.count > 0 then
420 for i in p_parameters.first..p_parameters.last loop
421 l_key_col_clause := replace(l_key_col_clause
422 ,p_parameters(i).name
423 ,p_parameters(i).value);
424 end loop;
425 end if;
426 --
427
428 p_statement := l_stmt;
429 --
430 if p_store_data then
431 hr_sum_store.store_data(p_business_group_id => p_business_group_id
432 ,p_item_name => ituTab(x).it_name
433 ,p_itu_name => ituTab(x).itu_name
434 ,p_item_type_usage_id => l_item_type_usage_id
435 ,p_count_clause1 => l_count_clause1
436 ,p_count_clause2 => l_count_clause2
437 ,p_stmt => l_stmt
438 ,p_debug => p_debug
439 ,p_key_col_clause => l_key_col_clause
440 ,p_error => l_itu_error);
441 if l_itu_error = 1 THEN
442 p_retcode :=1; /* If item type fails then set status of process to warning */
443 end if;
444
445 end if; -- p_store_data
446 end loop;
447 end if;
448 hr_utility.set_location('Leaving : hrumrep.process_run ', 10);
449 exception
450 when others then
451 p_retcode :=2; /* unknown error, so set status to Error */
452 write_error('Process Run error:');
453 write_error(sqlcode);
454 write_error(sqlerrm);
455 end process_run;
456 --
457 /* ------------------------------------------------------------------------
458 Procedure BS_PROCESS
459 Overloaded procedure designed to be used when called from the setup form
460 ------------------------------------------------------------------------ */
461 procedure process_run(p_business_group_id number
462 ,p_process_type varchar2
463 ,p_template_id number
464 ,p_process_name varchar2
465 ,p_item_type_usage_id number default null
466 ,p_store_data boolean default FALSE
467 ,p_debug varchar2 default 'N'
468 ,p_statement out NOCOPY varchar2) is
469 l_retcode number;
470
471 begin
472 process_run(p_business_group_id => p_business_group_id
473 ,p_process_type => p_process_type
474 ,p_template_id => p_template_id
475 ,p_process_name => p_process_name
476 ,p_parameters => hr_summary_util.nullprmTab
477 ,p_item_type_usage_id => p_item_type_usage_id
478 ,p_store_data => p_store_data
479 ,p_statement => p_statement
480 ,p_debug => p_debug
481 ,p_retcode => l_retcode);
482 end;
483 --
484 procedure process_run(p_business_group_id number
485 ,p_process_type varchar2
486 ,p_template_id number
487 ,p_process_name varchar2
488 ,p_parameters hr_summary_util.prmTabType
489 ,p_item_type_usage_id number default null
490 ,p_store_data boolean default FALSE
491 ,p_debug varchar2 default 'N'
492 ,p_statement out NOCOPY varchar2) is
493
494 l_retcode number;
495
496 begin
497 process_run(p_business_group_id => p_business_group_id
498 ,p_process_type => p_process_type
499 ,p_template_id => p_template_id
500 ,p_process_name => p_process_name
501 ,p_item_type_usage_id => p_item_type_usage_id
502 --bug 3008112 ,parameters => hr_summary_util.nullprmTab
503 ,p_parameters => p_parameters
504 ,p_store_data => p_store_data
505 ,p_statement => p_statement
506 ,p_debug => p_debug
507 ,p_retcode => l_retcode);
508 end;
509
510
511 procedure process_run_form(p_business_group_id number
512 ,p_process_type varchar2
513 ,p_template_id number
514 ,p_process_name varchar2
515 ,p_item_type_usage_id number default null
516 ,p_store_data boolean default FALSE
517 ,p_debug varchar2 default 'N'
518 ,p_statement out NOCOPY varchar2) is
519 --
520 cursor c_get_item_type_usage(p_template_id number) is
521 select it.DATATYPE
522 , it.count_clause1
523 , it.count_clause2
524 , it.where_clause
525 , it.name it_name
526 , itu.name itu_name
527 , itu.ITEM_TYPE_USAGE_ID
528 , itu.ITEM_TYPE_ID
529 from hr_summary_item_type_usage itu
530 , hr_summary_item_type it
531 where itu.template_id = p_template_id
532 and itu.ITEM_TYPE_ID = it.ITEM_TYPE_ID
533 and nvl(p_item_type_usage_id,item_type_usage_id) = item_type_usage_id
534 order by itu.sequence_number;
535 --
536 cursor c_get_key_type_usage(p_item_type_usage_id number) is
537 select kty.name
538 , kty.key_function
539 , kty.key_type_id
540 from hr_summary_key_type_usage ktu
541 , hr_summary_valid_key_type vkt
542 , hr_summary_key_type kty
543 where kty.key_type_id = vkt.key_type_id
544 and ktu.valid_key_type_id = vkt.valid_key_type_id
545 and ktu.item_type_usage_id = p_item_type_usage_id;
546 --
547 cursor c_get_restriction_usage(p_item_type_usage_id number) is
548 select srt.data_type
549 , srt.restriction_clause
550 , vru.restriction_type
551 , vru.restriction_usage_id
552 , srt.name
553 from hr_summary_valid_restriction vrt
554 , hr_summary_restriction_usage vru
555 , hr_summary_restriction_type srt
556 where vrt.valid_restriction_id = vru.valid_restriction_id
557 and vru.item_type_usage_id = p_item_type_usage_id
558 and vrt.restriction_type_id = srt.restriction_type_id
559 and srt.name <> 'USER_PERSON_TYPE';
560 --
561 cursor c_get_restriction_value(p_restriction_usage_id number) is
562 select value
563 from hr_summary_restriction_value
564 where restriction_usage_id = p_restriction_usage_id;
565 --
566 l_object_version_number number;
567 l_datatype varchar2(1);
568 l_stmt varchar2(32000);
569 l_item_type_usage_id number;
570 l_count_clause1 varchar2(32000);
571 l_count_clause2 varchar2(32000);
572 l_key_col_clause varchar2(32000);
573 l_where_clause varchar2(32000);
574 l_restriction_clause varchar2(32000);
575 l_restriction_clause2 varchar2(32000);
576 l_restriction_value varchar2(80);
577 l_error_mesg varchar2(100);
578 l_group_clause varchar2(32000);
579 l_key_clause varchar2(32000);
580 l_comma varchar2(1);
581 l_tab_num varchar2(2);
582 l_error boolean;
583 i number;
584 l_itu_error number; /* variable to check if item type usage occurred */
585 s1 number;
586 l_pos1 number;
587 l_pos2 number;
588 l_pos3 number;
589
590 l_alias varchar2(30);
591 l_parameter_alias varchar2(32000); -- require this length for the substr,instr to work
592 l_character_after_brkt varchar2(50);
593 --
594 source_cursor integer;
595 ignore integer;
596 --
597 begin
598 -- Set STORE_DATA utility package global
599 --
600 hr_utility.set_location('Entering : hrumrep.process_run ', 5);
601 hr_summary_util.store_data := p_store_data;
602 --
603 --
604 /* ------------------------------------------------------------------
605 For each of the item types that are required for the selected template,
606 retrieve the item type details
607 ------------------------------------------------------------------ */
608 i := 1;
609 for itu in c_get_item_type_usage(p_template_id) loop
610 --
611 --
612 ituTab(i).item_type_usage_id := itu.item_type_usage_id;
613 ituTab(i).item_type_id := itu.item_type_id;
614 ituTab(i).datatype := itu.datatype;
615 ituTab(i).count_clause1 := itu.count_clause1;
616 ituTab(i).count_clause2 := itu.count_clause2;
617 ituTab(i).where_clause := itu.where_clause;
618 ituTab(i).it_name := itu.it_name;
619 ituTab(i).itu_name := itu.itu_name;
620 --
621 i := i + 1;
622 --
623 end loop;
624 --
625 /* ------------------------------------------------------------------
626 Fo each item type usage retrieved
627 a) delete any existing item values or key values for the item type usage
628 b) build up the dynamic SQL statement and excute (if appropriate)
629 ------------------------------------------------------------------ */
630 if ituTab.count > 0 then
631 <<itu_loop>>
632 for x in 1..i-1 loop
633 --
634 -- Populate local and global variables
635 --
636 hr_summary_util.item_type_usage_id := ituTab(x).item_type_usage_id;
637 l_item_type_usage_id := ituTab(x).item_type_usage_id;
638 l_datatype := ituTab(x).datatype;
639 --
640 -- Delete existing results where appropriate
641 --
642 /* ------------------------------------------------------------------
643 Need to call Initialize procedure before evaluating the key functions because
644 initialize_procedure populates the zero_item_value_id for the item_type being
645 processed, the key functions subsequently reference it.
646 ------------------------------------------------------------------ */
647 hr_summary_util.initialize_procedure(p_business_group_id);
648 --
649 -- Initialize dynamic SQL components
650 --
651 l_stmt := null;
652 l_count_clause1 := null;
653 l_count_clause2 := null;
654 l_key_col_clause := null;
655 l_where_clause := null;
656 l_restriction_clause := null;
657 l_group_clause := null;
658 --
659 -- Begin determining the dynamic SQL components
660 --
661 hr_utility.trace('processing item ' || itutab(x).itu_name || ' ' || itutab(x).it_name);
662 l_count_clause1 := ituTab(x).count_clause1 || ' col_value1 ';
663 if ituTab(x).count_clause2 is not null then
664 l_count_clause2 := ','||ituTab(x).count_clause2 || ' col_value2 ';
665 end if;
666 --
667 -- Load the Key Types into a PLSQL table
668 --
669 i := 0;
670 ktyTab := nullktyTab;
671 for kty in c_get_key_type_usage(l_item_type_usage_id) loop
672 ktyTab(i).key_type := kty.name;
673 ktyTab(i).key_function := kty.key_function;
674 ktyTab(i).key_type_id := kty.key_type_id;
675 ktyTab(i).key_other := FALSE;
676 i := i + 1;
677 end loop;
678 --
679 /* -----------------------------------------------------------------
680 If there are key types being used
681 a) evaluate the function associated with the key type to return
682 the group by clause
683 b) build up the select clause
684 c) build up the group by clause
685 ----------------------------------------------------------------- */
686 --
687 hr_utility.trace('hrsumrep - keytab');
688 if ktyTab.count > 0 then
689 for i in ktyTab.first..ktyTab.last loop
690 --
691 l_stmt := 'declare key_type_id number := '||ktyTab(i).key_type_id||';
692 begin :l_clause := '||ktyTab(i).key_function||';
693 end;';
694 source_cursor := dbms_sql.open_cursor;
695 begin
696 l_error_mesg := fnd_message.get_string('PER','PER_74874_PARSE_ERROR');
697 dbms_sql.parse(source_cursor,l_stmt,dbms_sql.v7);
698 dbms_sql.bind_variable(source_cursor,'l_clause',l_key_clause, 32000);
699 l_error_mesg := fnd_message.get_string('PER','PER_74875_EXECUTE_ERROR');
700 ignore := dbms_sql.execute(source_cursor);
701 l_error_mesg := fnd_message.get_string('PER','PER_74876_ASSIGN_VARIABLE');
702 dbms_sql.variable_value(source_cursor,'l_clause',l_key_clause);
703 dbms_sql.close_cursor(source_cursor);
704 exception when others then
705 if p_store_data then
706 --p_retcode :=1; /* error occurred in key type. set status to warning */
707 write_error(l_error_mesg);
708 write_error(sqlerrm);
709 --write_stmt_log (p_stmt => l_stmt);
710 exit itu_loop;
711 else
712 null;
713 end if;
714 end;
715 l_key_col_clause := l_key_col_clause || '
716 , '|| l_key_clause || ' '||
717 'col_'||ktyTab(i).key_type ||' ';
718 if l_group_clause is null then
719 l_group_clause := ' group by ';
720 else
721 l_group_clause := l_group_clause ||'
722 ,';
723 end if;
724 --
725 l_group_clause := l_group_clause || l_key_clause;
726 end loop;
727 end if;
728
729 --
730 --
731 hr_utility.trace('hrsumrep - restrictions');
732 /* -----------------------------------------------------------------
733 If there are restrictions being used build up the restriction clause
734 ----------------------------------------------------------------- */
735 --
736 l_where_clause := ituTab(x).where_clause;
737 --
738 for vrt in c_get_restriction_usage(l_item_type_usage_id) loop
739 --
740 l_pos1 := nvl(instr(l_where_clause,vrt.name||'['),0);
741
742 if l_pos1 <> 0 then
743
744 l_pos1 := l_pos1+length(vrt.name)+1;
745 l_character_after_brkt := substr(l_where_clause,l_pos1,16);
746 -- need to concatenate slash and asterisk otherwise check_sql will give error.
747 If l_character_after_brkt = concat('/','*'|| 'parameteralias') then
748 l_restriction_clause := ' and @'||vrt.restriction_clause || ' ' ||
749 vrt.restriction_type || ' ';
750 l_pos1 := l_pos1 + 17;
751 l_pos2 := instr(l_where_clause,(concat('*','/]')),1);
752 l_parameter_alias := substr(l_where_clause,l_pos1,(l_pos2-l_pos1));
753 else
754 l_restriction_clause := ' and @.'||vrt.restriction_clause || ' ' ||
755 vrt.restriction_type || ' ';
756 end if;
757 --
758 hr_utility.trace('hrsumrep - restrictions1');
759 l_comma := '(';
760 for rvl in c_get_restriction_value(vrt.restriction_usage_id) loop
761 if vrt.data_type = 'C' then
762 l_restriction_value := '''' || rvl.value || '''';
763 elsif vrt.data_type = 'D' then
764 l_restriction_value := 'to_date('''|| rvl.value ||
765 ''',''YYYY/MM/DD HH24:MI:SS'')';
766 elsif vrt.data_type = 'N' then
767 l_restriction_value := rvl.value;
768 end if;
769 --
770 if vrt.restriction_type in ('=','<>','>','<') then
771 l_restriction_clause := l_restriction_clause ||
772 l_restriction_value || ' ';
773 exit;
774 else
775 l_restriction_clause := l_restriction_clause || l_comma ||
776 l_restriction_value;
777 l_comma := ',';
778 end if;
779 end loop;
780 --
781 if vrt.restriction_type not in ('=','<>','>','<') then
782 l_restriction_clause := l_restriction_clause || ')';
783 end if;
784 --
785 -- Replace all occurences of RESTRICTION.<restriction_name>
786 -- with the correct alias and restriction clause.
787 loop
788
789
790 l_pos3 := nvl(instr(l_where_clause,vrt.name||'['),0);
791 l_pos3 := l_pos3+length(vrt.name)+1;
792 l_character_after_brkt := substr(l_where_clause,l_pos3,16);
793 If l_character_after_brkt = concat('/','*parameteralias') then
794 l_pos3 := l_pos3 + 17;
795 l_pos2 := instr(l_where_clause,concat('*','/]'),1);
796 l_parameter_alias := substr(l_where_clause,l_pos3,(l_pos2-l_pos3));
797 End if;
798
799 l_pos1 := nvl(instr(l_where_clause,vrt.name||'['),0);
800 exit when l_pos1 = 0;
801 l_pos1 := l_pos1+length(vrt.name)+1;
802 l_pos2 := instr(l_where_clause,']',l_pos1,1);
803 hr_utility.trace('hrsumrep - restrictions1b');
804 hr_utility.trace('l_pos1 = ' || l_pos1 || ' ' || l_pos2);
805 l_alias := substr(l_where_clause,l_pos1,l_pos2-l_pos1);
806 hr_utility.trace('l_alias = ' || l_alias);
807 if l_character_after_brkt = concat('/','*parameteralias') then
808 -- l_alias := ' ';
809 hr_utility.trace('hrsumrep - restrictions1c');
810 l_restriction_clause2 := REPLACE(l_restriction_clause,'aliasreplace',l_parameter_alias);
811 l_restriction_clause2 := REPLACE(l_restriction_clause2,'@',' ');
812 hr_utility.trace('hrsumrep - restrictions1d');
813 -- write_stmt_log (p_stmt => 'l_restric2 = ' || l_restriction_clause2);
814 else
815 hr_utility.trace('hrsumrep - restrictions1e');
816 l_restriction_clause2 := REPLACE(l_restriction_clause,'@',l_alias);
817 end if;
818
819 hr_utility.trace('hrsumrep - restrictions2');
820 If l_character_after_brkt = concat('/','*parameteralias') then
821 l_where_clause := REPLACE(l_where_clause,'RESTRICTION.'||vrt.name||'['||l_alias||']',l_restriction_clause2); --write_stmt_log (p_stmt => 'l_where_cla= ' || l_where_clause);
822 else
823 l_where_clause := REPLACE(l_where_clause,'RESTRICTION.'||vrt.name||'['||l_alias||']',l_restriction_clause2); end if;
824
825 l_restriction_clause2 := null;
826 end loop;
827 end if;
828 end loop;
829 -- Remove all references of RESTRICTION.<whatever> that are not valid
830 -- for this item type.
831 loop
832 l_pos1 := nvl(instr(l_where_clause,'RESTRICTION.'),0);
833 exit when l_pos1 = 0;
834 l_pos2 := instr(l_where_clause,']',l_pos1,1)+1;
835 l_where_clause := substr(l_where_clause,1,l_pos1-1)||substr(l_where_clause,l_pos2,length(l_where_clause));
836 end loop;
837 --
838 hr_utility.trace('hrsumrep - complete restrictions');
839 -- Concatenate the full dynamic statement
840 --
841 l_stmt := 'select '||
842 l_count_clause1 ||
843 l_count_clause2 ||
844 l_key_col_clause || '
845 '||
846 l_where_clause || '
847 '||
848 l_group_clause;
849 --
850 p_statement := l_stmt;
851 --
852 end loop;
853 end if;
854 hr_utility.set_location('Leaving : hrumrep.process_run ', 10);
855 exception
856 when others then
857 write_error('Process Run error:');
858 write_error(sqlcode);
859 write_error(sqlerrm);
860 end process_run_form;
861 --
862
863 end hrsumrep;