4 | Copyright (C) 1995 Oracle Corporation |
1 PACKAGE BODY PER_GENERIC_REPORT_PKG AS
2 /* $Header: pergenrp.pkb 120.0 2005/05/31 17:56:59 appldev noship $ */
3 /*===========================================================================+
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.
39
36 17-SEP-01 gperry 115.2 Fixed WWBUG 1997980.
37 Inserts into tables
38 use by column name.
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,
166 and a.person_id = b.person_id
163 per_all_assignments_f b,
164 per_vacancies c
165 where a.business_group_id = b.business_group_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
244 --
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.
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,
348 p_param_12 varchar2
349 ) is
350 --
351 -- The l_proc variable is used for handling errors that may occur during
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;
364 -- field type = varchar2(1)
361 --
362 -- Table per_generic_report_output has the following fields
363 -- Line Type T = Title, H = Header, B = Body, F = Footer
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 --
470 l_success boolean := FALSE;
471 l_vacancy_name per_vacancies.name%type;
472 l_report_number number;
473 --
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]