1 PACKAGE BODY PER_GENERIC_REPORT_PKG AS
2 /* $Header: pergenrp.pkb 120.0 2005/05/31 17:56:59 appldev noship $ */
3 /*===========================================================================+
4 | Copyright (C) 1995 Oracle Corporation |
5 | All rights reserved |
6 | |
7 +===========================================================================*/
8 --
9 /*
10 Name
11 PER_GENERIC_REPORT_PKG
12 Purpose
13 Contains all the procedures to support the generation of customisable
14 candidate list reports.
15
16 Notes:
17
18 History
19 18-Sep-1995 fshojaas 70.0 Date Created.
20 gperry
21 24-Jul-1997 teyres 70.1 Changed as to is on create or replace line
22 25-Jun-97 teyres 110.1/70.2 110.1 and 70.2 are the same
23 04-aug-97 mstewart 110.2 Changed statements using
24 per_people_f_secv to
25 use per_people_f instead
26 (R11 security model
27 change)
28 19-jun-01 gperry 110.3 WWBUG 1833930. Since
29 this is sample code I
30 converted it to use
31 sysdate so the index is
32 for the DT tables are
33 being used. Also got
34 rid of the distinct
35 code.
36 17-SEP-01 gperry 115.2 Fixed WWBUG 1997980.
37 Inserts into tables
38 use by column name.
39
40 ============================================================================*/
41
42 --
43 -- Private Global Definition
44 ----------------------------------------------------------------------------
45 --
46 -- Global package name
47 --
48 g_package varchar2(50):= 'per_generic_report_pkg.';
49 --
50 --
51 -- *************************************************************************
52 --
53 -- Name : example1
54 -- Parameters : p_param_1
55 -- (This is the value of the first parameter that is
56 -- displayed on the screen, in this example it refers to a
57 -- vacancy).
58 -- Values Returned : none
59 -- Description : This procedure is used as an example to show how a
60 -- one parameter report can be used to populate a generic
61 -- table with custom formatting.
62 --
63 -- *************************************************************************
64 procedure example1(p_param_1 varchar2
65 ) is
66 --
67 -- Declare local variables, these are used for storing values that will be
68 -- inserted into the generic table.
69 --
70 l_recs_inserted number := 1;
71 l_full_name per_people_f.full_name%type;
72 l_sex per_people_f.sex%type;
73 --
74 -- The l_proc variable is used for handling errors that may occur during
75 -- program execution.
76 --
77 l_proc varchar(72) := g_package || 'generate_report';
78 --
79 -- This cursor selects the full_name and sex of people who have applied
80 -- for a particular vacancy. This vacancy is dependant on the value of
81 -- p_param_1.
82 --
83 cursor c1 is
84 select a.full_name, a.sex
85 from per_all_people_f a,
86 per_all_assignments_f b,
87 per_vacancies c
88 where a.business_group_id = b.business_group_id
89 and a.person_id = b.person_id
90 and trunc(sysdate)
91 between a.effective_start_date
92 and a.effective_end_date
93 and b.vacancy_id = c.vacancy_id
94 and trunc(sysdate)
95 between b.effective_start_date
96 and b.effective_end_date
97 and c.name = p_param_1;
98 begin
99 --
100 -- This example has one parameter only. The statements below insert
101 -- data with custom formatting into a table. This example using lpad
102 -- to demonstrate the sort of custom formatting that can be applied
103 -- to the resultant data.
104 --
105 open c1;
106 loop
107 fetch c1 into l_full_name, l_sex;
108 exit when c1%notfound;
109 --
110 -- Insert values into body of table with some example formatting.
111 -- In this case we insert the full_name and the sex which we
112 -- align using an lpad statement.
113 --
114 insert into per_generic_report_output
115 (line_type,line_number,line_content)
116 values ('B',l_recs_inserted,l_full_name||
117 lpad(l_sex,50-length(l_full_name),' '));
118 l_recs_inserted := l_recs_inserted + 1;
119 hr_utility.set_location(l_proc,20);
120 end loop;
121 close c1;
122 end example1;
123 -- *************************************************************************
124 --
125 -- Name : example2
126 -- Parameters : p_param_1
127 -- (This is the value of the first parameter that is
128 -- displayed on the screen, in this example it refers to a
129 -- vacancy).
130 -- p_param_2
131 -- (This is the value of the second parameter that is
132 -- displayed on the screen, in this example it refers to
133 -- the sex of people applying for a vacancy).
134 -- Values Returned : none
135 -- Description : This procedure is used as an example to show how a
136 -- two parameter report can be used to populate a generic
137 -- table with custom formatting.
138 --
139 -- *************************************************************************
140 procedure example2(p_param_1 varchar2,
141 p_param_2 varchar2
142 ) is
143 --
144 -- Declare local variables, these are used for storing values that will be
145 -- inserted into the generic table.
146 --
147 l_recs_inserted number := 1;
148 l_full_name per_people_f.full_name%type;
149 --
150 -- The l_proc variable is used for handling errors that may occur during
151 -- program execution.
152 --
153 l_proc varchar(72) := g_package || 'generate_report';
154 --
155 -- This cursor selects the full_name of people who have applied for
156 -- a particular vacancy. This vacancy is dependant on the value of
157 -- p_param_1 and the sex of people who will be selected is dependant
158 -- on the value of p_param_2.
159 --
160 cursor c2 is
161 select a.full_name
162 from per_all_people_f a,
163 per_all_assignments_f b,
164 per_vacancies c
165 where a.business_group_id = b.business_group_id
166 and a.person_id = b.person_id
167 and trunc(sysdate)
168 between a.effective_start_date
169 and a.effective_end_date
170 and b.vacancy_id = c.vacancy_id
171 and trunc(sysdate)
172 between b.effective_start_date
173 and b.effective_end_date
174 and c.name = p_param_1
175 and a.sex = p_param_2;
176 begin
177 --
178 -- This example has two parameters. The statements below insert the
179 -- full_name of people applying for a particular vacancy into a table
180 -- with certain custom formatting. This example uses upper to convert
181 -- the applicant's full_name to upper case in order to demonstrate
182 -- the sort of custom formatting that can be applied to the resultant
183 -- data.
184 --
185 open c2;
186 loop
187 fetch c2 into l_full_name;
188 exit when c2%notfound;
189 --
190 -- Insert values into body of table with some example formatting.
191 -- In this case we insert some spaces and full_name in upper case.
192 --
193 insert into per_generic_report_output
194 (line_type,line_number,line_content)
195 values ('B',l_recs_inserted,' '||upper(l_full_name));
196 l_recs_inserted := l_recs_inserted + 1;
197 hr_utility.set_location(l_proc,25);
198 end loop;
199 close c2;
200 end example2;
201 -- *************************************************************************
202 --
203 -- Name : example3
204 -- Parameters : p_param_1
205 -- (This is the value of the first parameter that is
206 -- displayed on the screen, in this example it refers to a
207 -- vacancy).
208 -- p_param_2
209 -- (This is the value of the second parameter that is
210 -- displayed on the screen, in this example it refers to
211 -- the marital status of people applying for a vacancy).
212 -- p_param_3
213 -- (This is the value of the second parameter that is
214 -- displayed on the screen, in this example it refers to
215 -- the sex of people applying for a vacancy).
216 -- Values Returned : none
217 -- Description : This procedure is used as an example to show how a
218 -- three parameter report can be used to populate a
219 -- generic table with custom formatting.
220 --
221 -- *************************************************************************
222 procedure example3(p_param_1 varchar2,
223 p_param_2 varchar2,
224 p_param_3 varchar2
225 ) is
226 --
227 -- Declare local variables, these are used for storing values that will be
228 -- inserted into the generic table.
229 --
230 l_recs_inserted number := 1;
231 l_full_name per_people_f.full_name%type;
232 l_sex per_people_f.sex%type;
233 --
234 -- The l_proc variable is used for handling errors that may occur during
235 -- program execution.
236 --
237 l_proc varchar(72) := g_package || 'generate_report';
238 --
239 -- This cursor selects the full_name, marital status and sex of people
240 -- who have applied for a particular vacancy. This vacancy is dependant
241 -- on the value of p_param_1, the marital status is dependant on the
242 -- value of p_param_2 and the sex of people who will be selected is
243 -- dependant on the value of p_param_3.
244 --
245 cursor c3 is
246 select a.full_name, a.sex
247 from per_all_people_f a,
248 per_all_assignments_f b,
249 per_vacancies c
250 where a.business_group_id = b.business_group_id
251 and a.person_id = b.person_id
252 and trunc(sysdate)
253 between a.effective_start_date
254 and a.effective_end_date
255 and b.vacancy_id = c.vacancy_id
256 and trunc(sysdate)
257 between b.effective_start_date
258 and b.effective_end_date
259 and b.primary_flag = 'Y'
260 and c.name = p_param_1
261 and a.marital_status = p_param_2
262 and a.sex = p_param_3;
263 begin
264 --
265 -- This example has three parameters. The statements below insert the
266 -- full_name and sex of people applying for a particular vacancy into
267 -- a table with certain custom formatting. This example uses initcap
268 -- to convert the applicant's full_name to initial caps in order to
269 -- demonstrate the sort of custom formatting that can be applied to
270 -- the resultant data.
271 --
272 open c3;
273 loop
274 fetch c3 into l_full_name, l_sex;
275 exit when c3%notfound;
276 --
277 -- Insert values into body of table with some example formatting.
278 -- In this case we insert the sex and then some spaces followed
279 -- by the full_name in initcaps.
280 --
281 insert into per_generic_report_output
282 (line_type,line_number,line_content)
283 values ('B',l_recs_inserted,l_sex||' '||initcap(l_full_name));
284 l_recs_inserted := l_recs_inserted + 1;
285 hr_utility.set_location(l_proc,30);
286 end loop;
287 close c3;
288 end example3;
289 -- *************************************************************************
290 --
291 -- Name : generate_report
292 -- Parameters : p_report_name varchar2
293 -- (This is the report name selected from the LOV)
294 -- p_param_1
295 -- (This is the value of the first parameter on the
296 -- screen).
297 -- p_param_2
298 -- (This is the value of the second parameter on the
299 -- screen).
300 -- p_param_3
301 -- (This is the value of the third parameter on the
302 -- screen).
303 -- p_param_4
304 -- (This is the value of the fourth parameter on the
305 -- screen).
306 -- p_param_5
307 -- (This is the value of the fifth parameter on the
308 -- screen).
309 -- p_param_6
310 -- (This is the value of the sixth parameter on the
311 -- screen).
312 -- p_param_7
313 -- (This is the value of the seventh parameter on the
314 -- screen).
315 -- p_param_8
316 -- (This is the value of the eighth parameter on the
317 -- screen).
318 -- p_param_9
319 -- (This is the value of the nineth parameter on the
320 -- screen).
321 -- p_param_10
322 -- (This is the value of the tenth parameter on the
323 -- screen).
324 -- p_param_11
325 -- (This is the value of the eleventh parameter on the
326 -- screen).
327 -- p_param_12
328 -- (This is the value of the twelvth parameter on the
329 -- screen).
330 -- Values Returned : none
331 -- Description : This procedure calls procedures which populate
332 -- the per_generic_report_output table with the
333 -- formatting required by the customer.
334 --
335 -- *************************************************************************
336 procedure generate_report(p_report_name varchar2,
337 p_param_1 varchar2,
338 p_param_2 varchar2,
339 p_param_3 varchar2,
340 p_param_4 varchar2,
341 p_param_5 varchar2,
342 p_param_6 varchar2,
343 p_param_7 varchar2,
344 p_param_8 varchar2,
345 p_param_9 varchar2,
346 p_param_10 varchar2,
347 p_param_11 varchar2,
351 -- The l_proc variable is used for handling errors that may occur during
348 p_param_12 varchar2
349 ) is
350 --
352 -- program execution.
353 --
354 l_proc varchar(72) := g_package || 'generate_report';
355 begin
356 hr_utility.set_location('Entering: ' ||l_proc,5);
357 --
358 -- Delete existing data from per_generic_report_output
359 --
360 delete from per_generic_report_output;
361 --
362 -- Table per_generic_report_output has the following fields
363 -- Line Type T = Title, H = Header, B = Body, F = Footer
364 -- field type = varchar2(1)
365 -- Line Number field type = number(9)
366 -- Line Text field type = long
367 --
368 -- Insert header information into table
369 -- This will be displayed as the header for the report
370 --
371 -- NOTE: The user may insert their own header into the next line instead of
372 -- the 'Example Title - Oracle Corporation UK Ltd'
373 --
374 insert into per_generic_report_output
375 (line_type,line_number,line_content)
376 values ('H',1,'Example Title - Oracle Corporation UK Ltd');
377 --
378 -- Insert footer information into table
379 -- This will be displayed as the footer for the report
380 --
381 -- NOTE: The user may insert their own footer into the next line instead
382 -- of the 'Example Footer - Oracle Corporation UK Ltd'.
383 --
384 insert into per_generic_report_output
385 (line_type,line_number,line_content)
386 values ('F',1,'Example Footer - Oracle Corporation UK Ltd');
387 --
388 -- Insert title information into table, in this case p_report_name
389 -- This will be displayed as the title for the report
390 --
391 insert into per_generic_report_output
392 (line_type,line_number,line_content)
393 values ('T',1,p_report_name);
394 --
395 -- Depending on the report name run different procedures
396 --
397 if p_report_name = 'Custom Report 1' then
398 example1(p_param_1);
399 elsif p_report_name = 'Custom Report 2' then
400 example2(p_param_1,p_param_2);
401 elsif p_report_name = 'Custom Report 3' then
402 example3(p_param_1,p_param_2,p_param_3);
403 end if;
404 end generate_report;
405 -- *************************************************************************
406 --
407 -- Name : launch_report
408 -- Parameters : p_report_name
409 -- (This is the report name selected from the LOV)
410 -- p_param_1
411 -- (This is the value of the first parameter on the
412 -- screen).
413 -- p_param_2
414 -- (This is the value of the second parameter on the
415 -- screen).
416 -- p_param_3
417 -- (This is the value of the third parameter on the
418 -- screen).
419 -- p_param_4
420 -- (This is the value of the fourth parameter on the
421 -- screen).
422 -- p_param_5
423 -- (This is the value of the fifth parameter on the
424 -- screen).
425 -- p_param_6
426 -- (This is the value of the sixth parameter on the
427 -- screen).
428 -- p_param_7
429 -- (This is the value of the seventh parameter on the
430 -- screen).
431 -- p_param_8
432 -- (This is the value of the eighth parameter on the
433 -- screen).
434 -- p_param_9
435 -- (This is the value of the nineth parameter on the
436 -- screen).
437 -- p_param_10
438 -- (This is the value of the tenth parameter on the
439 -- screen).
440 -- p_param_11
441 -- (This is the value of the eleventh parameter on the
442 -- screen).
443 -- p_param_12
444 -- (This is the value of the twelvth parameter on the
445 -- screen).
446 -- Values Returned : boolean (depending on whether validation check
447 -- succeeded, in this case we check if a vacancy exists).
448 -- Description : This procedure is used as an initial validation check
449 -- before attemptng to submit the report to the
450 -- concurrent program manager.
451 --
452 -- *************************************************************************
453 function launch_report(p_report_name varchar2,
454 p_param_1 varchar2,
455 p_param_2 varchar2,
456 p_param_3 varchar2,
457 p_param_4 varchar2,
458 p_param_5 varchar2,
459 p_param_6 varchar2,
460 p_param_7 varchar2,
461 p_param_8 varchar2,
462 p_param_9 varchar2,
463 p_param_10 varchar2,
464 p_param_11 varchar2,
465 p_param_12 varchar2)
466 return boolean is
467 --
468 -- Declare local variables
469 --
473 --
470 l_success boolean := FALSE;
471 l_vacancy_name per_vacancies.name%type;
472 l_report_number number;
474 -- The l_proc variable is used for handling errors that may occur during
475 -- program execution.
476 --
477 l_proc varchar2(72) := g_package || 'launch_report';
478 --
479 -- This cursor is used as an example to show what sort or prior
480 -- to submitting validation checks a customer could do. In this
481 -- case a cursor is used to check if a vacancy exists.
482 --
483 cursor c1 is
484 select name
485 from per_vacancies
486 where name = p_param_1;
487 begin
488 hr_utility.set_location('Entering:' || l_proc,5);
489 --
490 -- This cursor checks whether a fetch was successful, in other words
491 -- does a vacancy exist. If not then a variable is set and an
492 -- appropriate error message will be displayed.
493 --
494 open c1;
495 fetch c1 into l_vacancy_name;
496 --
497 -- Check if vacancy exists by seeing if fetch succeeded
498 --
499 if c1%found then
500 --
501 -- Vacancy found
502 --
503 l_success := true;
504 else
505 --
506 -- Vacancy not found
507 --
508 l_success := false;
509 end if;
510 --
511 -- Close cursor c1
512 --
513 hr_utility.set_location(l_proc,15);
514 close c1;
515 --
516 -- Return control back to the form if vacancy has not been found.
517 -- Display error message if vacancy not found.
518 --
519 if not l_success then
520 --
521 hr_utility.set_message(801,'HR_51001_THE_VAC_NOT_FOUND');
522 hr_utility.raise_error;
523 end if;
524 --
525 -- ***********************************************************************
526 -- DO NOT REMOVE THE NEXT LINE : l_report_number := ........
527 -- ***********************************************************************
528 --
529 -- Make a request to the concurrent program manager. This returns a
530 -- number depending on whether a successful request or unsuccessful
531 -- request was made. The number 0 indicates an unsuccessful request.
532 --
533 l_report_number := fnd_request.submit_request('PER',
534 'PERGENRP',
535 NULL,
536 NULL,
537 NULL,
538 p_report_name,
539 p_param_1,
540 p_param_2,
541 p_param_3,
542 p_param_4,
543 p_param_5,
544 p_param_6,
545 p_param_7,
546 p_param_8,
547 p_param_9,
548 p_param_10,
549 p_param_11,
550 p_param_12
551 );
552 -- ***********************************************************************
553 -- DO NOT REMOVE THE NEXT IF..END IF STATEMENT : if l_report_number ...
554 -- ***********************************************************************
555 --
556 -- Check to see if the request was successful otherwise display an error
557 -- message.
558 --
559 if l_report_number = 0 then
560 hr_utility.set_location(l_proc,35);
561 hr_utility.set_message(801,'HR_51002_REPORT_CANT_SUBMITTED');
562 hr_utility.raise_error;
563 end if;
564 --
565 -- return true if all checks have been successful
566 --
567 return true;
568 end launch_report;
569 END PER_GENERIC_REPORT_PKG;
[Home] [Help]