[Home] [Help]
PACKAGE BODY: APPS.HRI_PARAMETERS
Source
1 PACKAGE BODY HRI_Parameters AS
2 /* $Header: hripmgen.pkb 120.1 2008/01/07 12:32:36 aejjavu noship $ */
3
4 -- This is the main procedure for this package.
5 -- It is referenced in the AOL function definition for the report.
6 -- It creates all the parameters and passes them to a
7 -- core BIS procedure to create all the formatting of the page
8
9 --=========================================================================================
10
11
12 PROCEDURE Parameter_FormView
13 (p_report_name IN varchar2,
14 p_param_request IN varchar2
15 )
16 IS
17
18 -- Create PL/SQL table
19 params BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type;
20 paramnum integer;
21 p_option integer;
22
23 -- bug fix: 1269713, check required parameter[s]
24 l_req_check number;
25 e_missing_required_parameter exception;
26 l_param_missing_req_val_name varchar2(100);
27 l_param_missing_req_label varchar2(2000);
28
29 l_job_lov_count number;
30
31 c_job_lov_count_high number := 20;
32
33 cursor c_job_lov_check is
34 select count(*)+1
35 from per_jobs -- bug fix 3680782
36 where business_group_id = Hr_General.Get_Business_Group_Id;
37
38
39 BEGIN
40
41 -- bugs 1355513, 1355528, cbridge 28/07/2000
42 -- cause an explicit check to validate the session, this ensures the
43 -- session is valid and calls to fnd_global.user_id etc. will return correct values
44
45 if (icx_sec.validatesession) then
46 null;
47 end if;
48
49 -- end of fix, cbridge 28/07/2000
50
51
52 -- Run Before-Parameter-Form Logic
53
54 Before_Parameter_Form(
55 p_function_code => 'BIS_' || p_report_name
56 ,p_rdf_filename => p_report_name
57 );
58
59 -- Assign report name to global value
60 g_report_name := p_report_name;
61
62 -- Generate initial HTML
63
64 htp.htmlOpen;
65 htp.headOpen;
66
67 -- Generate JavaScript functions for cross-validation between
68 -- Geography dimension level LOV and Geography values LOV
69
70 js.scriptopen;
71 icx_util.lovscript;
72 js.scriptclose;
73
74 -- Format Javascript Functions (if needed)
75
76 IF instr(p_param_request,'GEOGID,') > 0 then
77 Geography_Cross_Validation;
78 END IF;
79
80 -- Finish heading, start body of HTML
81
82 htp.headClose;
83 htp.bodyOpen;
84
85 -- Open the HTML form
86
87 htp.formOpen ('HRI_Parameters.Param_ActionView', 'GET',NULL,NULL, 'name="params" ');
88
89 -- Create each parameter displayed in the HTML form
90
91 paramnum := 1;
92
93 IF instr(p_param_request,'EMPAPL,') > 0 then
94 Build_Employee_Applicant(params,paramnum);
95 paramnum := paramnum + 1;
96 ELSE
97 htp.formHidden('EMPAPL', '');
98 END IF;
99
100 IF instr(p_param_request,'ORG_ID,') > 0 then
101
102 Build_LOV(params,paramnum, 0, 'P_ORGANIZATION_ID', 'ORGANIZATION');
103 paramnum := paramnum + 1;
104
105 ELSIF instr(p_param_request,'ORG_ID+,') > 0 then
106
107 Build_LOV(params,paramnum, 1, 'P_ORGANIZATION_ID', 'ORGANIZATION');
108 paramnum := paramnum + 1;
109
110 ELSE
111 htp.formHidden('P_ORGANIZATION_ID', '');
112 htp.formHidden('P_ORGANIZATION_NAME', '');
113 END IF;
114
115 IF instr(p_param_request,'ORGPRC1,') > 0 then
116 Build_Incl_Subord(params,paramnum,1);
117 paramnum := paramnum + 1;
118 ELSIF instr(p_param_request,'ORGPRC2,') > 0 then
119 Build_Incl_Subord(params,paramnum,2);
120 paramnum := paramnum + 1;
121 ELSIF instr(p_param_request,'ORGPRC3,') > 0 then
122 Build_Incl_Subord(params,paramnum,3);
123 paramnum := paramnum + 1;
124 ELSE
125 htp.formHidden('ORGPRC', '');
126 END IF;
127
128 IF instr(p_param_request,'BPL_ID,') > 0 then
129 Build_Business_Plan(params,paramnum);
130 paramnum := paramnum + 1;
131 ELSE
132 htp.formHidden('BPL_ID', '-1');
133 END IF;
134
135 IF instr(p_param_request,'GEOGID,') > 0 then
136 Build_Geog_Level(params,paramnum);
137 Build_Geog_Value(params,paramnum);
138 paramnum := paramnum + 1;
139 ELSE
140 htp.formHidden('GEOLVL', '');
141 htp.formHidden('GEOVAL', '');
142 END IF;
143
144 IF instr(p_param_request,'LOC_ID,') > 0 then
145
146 Build_LOV(params,paramnum, 1, 'P_LOCATION_ID', 'LOCATION');
147 paramnum := paramnum + 1;
148
149 ELSIF instr(p_param_request,'LOC_ID+,') > 0 then
150
151 Build_LOV(params,paramnum, 1, 'P_LOCATION_ID', 'LOCATION');
152 paramnum := paramnum + 1;
153
154 ELSE
155 htp.formHidden('P_LOCATION_ID', '-1');
156 htp.formHidden('P_LOCATION_NAME', '');
157 END IF;
158
159 IF instr(p_param_request,'PRODID,') > 0 then
160 Build_Product(params,paramnum);
161 paramnum := paramnum + 1;
162 ELSE
163 htp.formHidden('PRODID', '');
164 END IF;
165
166 IF instr(p_param_request,'JOBCAT,') > 0 then
167 Build_Job_Category(params,paramnum,0);
168 paramnum := paramnum + 1;
169 htp.formHidden('JOBCAT1', '');
170 htp.formHidden('JOBCAT2', '');
171 htp.formHidden('JOBCAT3', '');
172 ELSIF instr(p_param_request,'JOBCAT+,') > 0 then
173 Build_Job_Category(params,paramnum,1);
174 paramnum := paramnum + 1;
175 htp.formHidden('JOBCAT', '');
176 ELSE
177 htp.formHidden('JOBCAT', '');
178 htp.formHidden('JOBCAT1', '');
179 htp.formHidden('JOBCAT2', '');
180 htp.formHidden('JOBCAT3', '');
181 END IF;
182
183 IF instr(p_param_request,'JOB_ID,') > 0 then
184
185 -- enhancement, 1110938
186 open c_job_lov_check;
187 fetch c_job_lov_check into l_job_lov_count;
188 close c_job_lov_check;
189
190 IF l_job_lov_count < c_job_lov_count_high THEN
191 Build_Job(params, paramnum, 0, l_req_check);
192
193 If l_req_check = 0 THEN
194 -- no jobs are set up
195
196 htp.formHidden('JOB_ID', '*');
197
198 l_param_missing_req_val_name := 'JOB_ID';
199 Raise e_missing_required_parameter;
200 ELSE
201 htp.formHidden('P_JOB_NAME', '');
202 paramnum := paramnum + 1;
203 END IF;
204
205 ELSE
206 Build_LOV(params,paramnum, 0, 'P_JOB_ID', 'JOB');
207 paramnum := paramnum + 1;
208 END IF;
209
210 ELSIF instr(p_param_request,'JOB_ID+,') > 0 then
211
212 -- enhancement, 1110938
213 open c_job_lov_check;
214 fetch c_job_lov_check into l_job_lov_count;
215 close c_job_lov_check;
216
217 IF l_job_lov_count < c_job_lov_count_high THEN
218 Build_Job(params, paramnum, 1, l_req_check);
219
220 If l_req_check = 0 THEN
221 -- no jobs are set up
222
223 htp.formHidden('JOB_ID', '*');
224
225 l_param_missing_req_val_name := 'JOB_ID';
226 Raise e_missing_required_parameter;
227 ELSE
228 htp.formHidden('P_JOB_NAME', '');
229 paramnum := paramnum + 1;
230 END IF;
231
232 ELSE
233 Build_LOV(params,paramnum, 1, 'P_JOB_ID', 'JOB');
234 paramnum := paramnum + 1;
235 END IF;
236 ELSE
237 htp.formHidden('P_JOB_ID', '');
238 htp.formHidden('P_JOB_NAME', '');
239 END IF;
240
241 IF instr(p_param_request,'GRD_ID,') > 0 then
242 --Build_Grade_ID(params,paramnum);
243 Build_LOV(params,paramnum,1,'P_GRADE_ID','GRADE');
244 paramnum := paramnum + 1;
245 ELSE
246 htp.formHidden('P_GRADE_ID', '');
247 htp.formHidden('P_GRADE_NAME','');
248 END IF;
249
250 IF instr(p_param_request,'PERFRT,') > 0 then
251 Build_lookup_param(params,paramnum, 'HR_BIS_PERFORMANCE_RATING','PERFRT','PERFORMANCE_RATING','-1');
252 paramnum := paramnum + 1;
253 ELSE
254 htp.formHidden('PERFRT', '');
255 END IF;
256
257 IF instr(p_param_request,'COMPID,') > 0 then
258
259 Build_Competence_ID(params,paramnum,l_req_check);
260
261 -- bug fix: 1269713
262 IF l_req_check = 0 THEN
263 -- a required parameter had no values found on db to populate the drop down list
264 htp.formHidden('COMPID', '*');
265
266 l_param_missing_req_val_name := 'COMPID';
267 Raise e_missing_required_parameter;
268 END IF;
269
270 paramnum := paramnum + 1;
271
272 ELSE
273 htp.formHidden('COMPID', '');
274 END IF;
275
276 IF instr(p_param_request,'PFMEAS,') > 0 then
277 Build_Proficiency_Measure(params,paramnum);
278 paramnum := paramnum + 1;
279 ELSE
280 htp.formHidden('PFMEAS', '');
281 END IF;
282
283 IF instr(p_param_request,'PAYRLL,') > 0 then
284
285 Build_Payroll_ID(params,paramnum, 0, l_req_check);
286
287 -- bug fix: 1269713
288 IF l_req_check = 0 THEN
289 -- a required parameter had no values found on db to populate the drop down list
290 htp.formHidden('PAYRLL', '*');
291
292 l_param_missing_req_val_name := 'PAYRLL';
293 Raise e_missing_required_parameter;
294 END IF;
295
296 paramnum := paramnum + 1;
297
298 ELSIF instr(p_param_request,'PAYRLL+,') > 0 then
299 Build_Payroll_ID(params,paramnum, 1, l_req_check);
300
301 -- bug fix: 1269713
302 IF l_req_check = 0 THEN
303 -- a required parameter had no values found on db to populate the drop down list
304 htp.formHidden('PAYRLL', '*');
305
306 l_param_missing_req_val_name := 'PAYRLL';
307 Raise e_missing_required_parameter;
308 END IF;
309
310 paramnum := paramnum + 1;
311
312 ELSE
313 htp.formHidden('PAYRLL', '');
314 END IF;
315
316 if instr(p_param_request, 'BGT_ID,') > 0 then
317 htp.comment('called Build_Budget');
318 Build_Budget(params,paramnum,l_req_check);
319
320 -- bug fix: 1269713
321 IF l_req_check = 0 THEN
322 -- a required parameter had no values found on db to populate the drop down list
323 htp.formHidden('BGT_ID', '*');
324
325 l_param_missing_req_val_name := 'BGT_ID';
326 Raise e_missing_required_parameter;
327 END IF;
328
329 paramnum := paramnum+1;
330
331 elsif instr(p_param_request, 'BGT_PQH_ID,') > 0 then
332 Build_pqh_Budget(params,paramnum,l_req_check);
333 htp.comment('called Build_pqh_Budget');
334
335 -- bug fix: 1269713
336 IF l_req_check = 0 THEN
337 -- a required parameter had no values found on db to populate the drop down list
338 htp.formHidden('BGT_ID', '*');
339
340 l_param_missing_req_val_name := 'BGT_ID';
341 Raise e_missing_required_parameter;
342 END IF;
343
344 paramnum := paramnum+1;
345 ELSE
346 htp.formHidden('BGT_ID', '');
347 END IF;
348
349 IF instr(p_param_request, 'BGTTYP,') > 0 then
350 Build_lookup_param(params,paramnum, 'HR_BIS_BGTTYP','BGTTYP','BUDGET_MEASUREMENT_TYPE','');
351 paramnum := paramnum + 1;
352 ELSE
353 htp.formHidden('BGTTYP', '');
354 END IF;
355
356 IF instr(p_param_request,'PROPRN,') > 0 then
357 Build_lookup_param(params,paramnum, 'HR_BIS_PROPOSAL_REASON', 'PROPRN', 'PROPOSAL_REASON', '');
358 paramnum := paramnum + 1;
359 ELSE
360 htp.formHidden('PROPRN', '');
361 END IF;
362
363 IF instr(p_param_request,'SEPRSN,') > 0 then
364 Build_lookup_param(params,paramnum, 'HR_BIS_SEPARATION_REASON', 'SEPRSN', 'LEAV_REAS', 'BIS_ALL');
365 paramnum := paramnum + 1;
366 ELSE
367 htp.formHidden('SEPRSN', '');
368 END IF;
369
370 IF instr(p_param_request,'VIEWBY1,') > 0 then
371 Build_View_By(params,paramnum,1);
372 paramnum := paramnum + 1;
373 ELSIF instr(p_param_request,'VIEWBY2,') > 0 then
374 Build_View_By(params,paramnum,2);
375 paramnum := paramnum + 1;
376 ELSIF instr(p_param_request,'VIEWBY3,') > 0 then
377 Build_View_By(params,paramnum,3);
378 paramnum := paramnum + 1;
379 ELSE
380 htp.formHidden('VIEWBY', '');
381 END IF;
382
383 IF instr(p_param_request,'DISPLY,') > 0 then
384 Build_Display_By(params,paramnum);
385 paramnum := paramnum + 1;
386 ELSE
387 htp.formHidden('DISPLY', '');
388 htp.formHidden('DCOUNT', '');
389 END IF;
390
391 IF instr(p_param_request,'ORDERB1,') > 0 then
392 Build_Order_By(params,paramnum,1);
393 paramnum := paramnum + 1;
394 ELSIF instr(p_param_request,'ORDERB2,') > 0 then
395 Build_Order_By(params,paramnum,2);
396 paramnum := paramnum + 1;
397 ELSE
398 htp.formHidden('ORDERB', '');
399 END IF;
400
401 IF instr(p_param_request,'CURRCD,') > 0 then
402 Build_Report_Currency(params,paramnum);
403 paramnum := paramnum + 1;
404 ELSE
405 htp.formHidden('CURRCD', '');
406 END IF;
407
408 IF instr(p_param_request,'EXCCUR,') > 0 then
409 Build_Exclude_Currency(params,paramnum);
410 paramnum := paramnum + 1;
411 ELSE
412 htp.formHidden('EXCCUR', '');
413 END IF;
414
415 IF instr(p_param_request,'FRQNCY,') > 0 then
416 Build_Frequency(params,paramnum);
417 paramnum := paramnum + 1;
418 ELSE
419 htp.formHidden('FRQNCY', 'CM');
420 END IF;
421
422 IF instr(p_param_request,'DATE1,') > 0 then
423 Build_Rep_Dates(params,paramnum,1);
424 paramnum := paramnum + 1;
425 ELSE
426 htp.formHidden('P_REPORT_DATE_V', '');
427 END IF;
428
429 IF instr(p_param_request,'DATE2,') > 0 then
430 Build_Rep_Dates(params,paramnum,2);
431 paramnum := paramnum + 1;
432 ELSE
433 htp.formHidden('P_START_DATE_V', '*');
434 htp.formHidden('P_END_DATE_V', '*');
435 END IF;
436
437 -- Create hidden fields for Business Group ID and Organization Version
438
439 htp.formHidden('BUS_ID', g_bus_id);
440 htp.formHidden('ORGVER', g_orgver);
441 htp.formHidden('RPNAME', p_report_name);
442
443 -- Build Parameter Form
444
445 BIS_UTILITIES_PUB.Build_Parameter_Form('NAME="'
446 || p_report_name || '"
447 ACTION="HRI_Parameters.Param_ActionView" METHOD="GET"', params);
448
449 htp.formClose;
450 htp.bodyClose;
451 htp.htmlClose;
452
453 EXCEPTION
454 WHEN e_missing_required_parameter THEN
455 BEGIN
456
457 IF l_param_missing_req_val_name = 'PAYRLL' THEN
458 l_param_missing_req_label := Display_Label('HR_BIS_PAYROLL');
459 END IF;
460
461 IF l_param_missing_req_val_name = 'ORG_ID' THEN
462 l_param_missing_req_label := Display_Label('HR_BIS_ORGANIZATION');
463 END IF;
464
465 IF l_param_missing_req_val_name = 'COMPID' THEN
466 l_param_missing_req_label := Display_Label('HR_BIS_COMPETENCE');
467 END IF;
468
469 IF l_param_missing_req_val_name = 'JOB_ID' THEN
470 l_param_missing_req_label := Display_Label('HR_BIS_JOB');
471 END IF;
472
473 IF l_param_missing_req_val_name = 'BGT_ID' THEN
474 l_param_missing_req_label := Display_Label('HR_BIS_BUDGET');
475 END IF;
476
477 fnd_message.set_name('HRI','HR_BIS_PLSQL_CART_REQ_PARAM');
478 fnd_message.set_token('PARAMETER',l_param_missing_req_label);
479
480 htp.br;
481 htp.br;
482 htp.br;
483 htp.bold(fnd_message.get);
484 htp.formClose;
485 htp.bodyClose;
486 htp.htmlClose;
487
488
489 END;
490
491 END Parameter_FormView;
492
493 --=========================================================================================
494
495 -- Public procedure which executes when the 'OK' button is clicked
496 -- on the report parameter page
497
498 PROCEDURE Param_ActionView(
499 P_ORGANIZATION_ID varchar2 default '-1',
503 BUS_ID number,
500 P_ORGANIZATION_NAME varchar2,
501 ORGPRC varchar2 default 'SIRO',
502 ORGVER number,
504 BPL_ID number,
505 GEOLVL varchar2 default '1',
506 GEOVAL varchar2 default '-1',
507 PRODID varchar2 default '-1',
508 P_JOB_ID varchar2 default '-1',
509 P_JOB_NAME varchar2,
510 JOBCAT varchar2 default '__ALL__',
511 JOBCAT1 varchar2 default '__ALL__',
512 JOBCAT2 varchar2 default '__ALL__',
513 JOBCAT3 varchar2 default '__ALL__',
514 BGTTYP varchar2,
515 VIEWBY varchar2 default 'HR_BIS_TIME',
516 FRQNCY varchar2 default 'CM',
517 P_START_DATE_V varchar2,
518 P_END_DATE_V varchar2,
519 P_LOCATION_ID varchar2 default '-1',
520 P_LOCATION_NAME varchar2,
521 BGT_ID varchar2,
522 COMPID varchar2,
523 CURRCD varchar2,
524 P_REPORT_DATE_V varchar2,
525 EMPAPL varchar2,
526 EXCCUR varchar2,
527 P_GRADE_ID varchar2 default '-1',
528 P_GRADE_NAME varchar2,
529 ORDERB varchar2,
530 PAYRLL varchar2,
531 PERFRT varchar2,
532 PFMEAS varchar2,
533 PROPRN varchar2,
534 SEPRSN varchar2,
535 DISPLY varchar2,
536 DCOUNT varchar2,
537 RPNAME varchar2
538 ) IS
539
540 BEGIN
541
542
543 -- Run After Parameter Form Logic
544 HRI_Parameters.After_Parameter_Form;
545 /* OracleOASIS.RunReport(
546 report => RPNAME,
547 parameters => 'ORG_ID=' ||nvl(p_organization_id,-1)|| '*' ||
548 'ORGPRC=' ||ORGPRC || '*' ||
549 'ORGVER=' ||ORGVER || '*' ||
550 'BUS_ID=' ||BUS_ID || '*' ||
551 'BPL_ID=' ||BPL_ID || '*' ||
552 'GEOLVL=' ||GEOLVL || '*' ||
553 'GEOVAL=' ||GEOVAL || '*' ||
554 'PRODID=' ||PRODID || '*' ||
555 'JOB_ID=' ||nvl(p_job_id,'-1')|| '*' ||
556 'JOBCAT=' ||JOBCAT || '*' ||
557 'JOBCAT1=' ||JOBCAT1 || '*' ||
558 'JOBCAT2=' ||JOBCAT2 || '*' ||
559 'JOBCAT3=' ||JOBCAT3 || '*' ||
560 'BGTTYP=' ||BGTTYP || '*' ||
561 'VIEWBY=' ||VIEWBY || '*' ||
562 'FRQNCY=' ||FRQNCY || '*' ||
563 'LOC_ID=' ||nvl(p_location_id, '-1')|| '*'||
564 'BGT_ID=' ||BGT_ID || '*' ||
565 'COMPID=' ||COMPID || '*' ||
566 'CURRCD=' ||CURRCD || '*' ||
567 'P_REPORT_DATE_V=' ||P_REPORT_DATE_V || '*' ||
568 'EMPAPL=' ||EMPAPL || '*' ||
569 'EXCCUR=' ||EXCCUR || '*' ||
570 'GRD_ID=' ||NVL(P_GRADE_ID,'-1')|| '*' ||
571 'ORDERB=' ||ORDERB || '*' ||
572 'PAYRLL=' ||PAYRLL || '*' ||
573 'PERFRT=' ||PERFRT || '*' ||
574 'PFMEAS=' ||PFMEAS || '*' ||
575 'PROPRN=' ||PROPRN || '*' ||
576 'SEPRSN=' ||SEPRSN || '*' ||
577 'DISPLY=' ||DISPLY || '*' ||
578 'DCOUNT=' ||DCOUNT || '*' ||
579 'P_START_DATE_V='||P_START_DATE_V|| '*' ||
580 'P_END_DATE_V=' ||P_END_DATE_V || '*',
581 paramform => 'NO'
582 );
583 */
584 END Param_ActionView;
585
586 --=========================================================================================
587
588 PROCEDURE Before_Parameter_Form(
589 p_function_code IN VARCHAR2
590 ,p_rdf_filename IN VARCHAR2
591 ) IS
592
593 BEGIN
594
595 -- This procedure replaces the reports BeforePForm Trigger and
596 -- creates the banner and sets up business group id and org ver
597 -- Create Banner for top of Parameter Form
598 -- This uses Core BIS package called BIS_UTILITIES_PUB
599 -- Need to pass it Function Code and RDF Filename (without extension)
600
601 BIS_UTILITIES_PUB.Build_Report_Title(
602 p_Function_Code => p_function_code
603 ,p_Rdf_Filename => p_rdf_filename
604 ,p_Body_Attribs => NULL
605 );
606
607 -- Initialize the report parameters
608 -- Pass in globals for user id, responsibility id, application id,
609 -- and security group. The correct business group and organization
610 -- version (used to determine Org LOV) are determined and passed back
611 -- into global variables in this package
612
613 HrFastAnswers.Initialize(
614 p_user_id => FND_GLOBAL.user_id,
615 p_resp_id => FND_GLOBAL.resp_id,
616 p_resp_appl_id => FND_GLOBAL.resp_appl_id,
617 p_sec_group_id => FND_GLOBAL.security_group_id,
618 p_business_group_id => g_bus_id,
619 p_org_structure_version_id => g_orgver);
620
621 END Before_Parameter_Form;
622
623 --=========================================================================================
624
625 PROCEDURE After_Parameter_Form IS
626 BEGIN
627
628 -- This procedure replaces the reports AfterPForm Trigger and initializes
629 -- globals (just in case they are not already set)
630
631 -- Initialize the report parameters
632
633 HrFastAnswers.Initialize(
634 p_user_id => FND_GLOBAL.user_id,
635 p_resp_id => FND_GLOBAL.resp_id,
636 p_resp_appl_id => FND_GLOBAL.resp_appl_id,
637 p_sec_group_id => FND_GLOBAL.security_group_id,
638 p_business_group_id => g_bus_id,
639 p_org_structure_version_id => g_orgver);
640
641 END After_Parameter_Form;
642
643 --=========================================================================================
644
645 FUNCTION Display_Label(
646 p_label_name IN VARCHAR2 )
647 RETURN VARCHAR2 IS
648
649 BEGIN
650
651 -- This private function gets the translation for each of the report parameter
652 -- labels. Pass in the message_name (e.g. 'HR_BIS_PRODUCT_CATEGORY') and
653 -- it passes back the correct translation
654
655 fnd_message.set_name( 'HRI', p_label_name );
656 return fnd_message.get;
657
658 END Display_Label;
659
660 --=========================================================================================
661
662 PROCEDURE Build_Business_Plan(
663 params IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type
664 ,i IN NUMBER
665 ) IS
666
667 cursor c_bpl is
668 select -1 plan_id
669 ,' ' plan_name
670 from sys.dual
671 union
672 select plan_id
673 ,description plan_name
674 from bisbv_business_plans
675 where current_plan_flag = 'Y'
676 order by 2;
677
678 BEGIN
679
680 -- Creates Business Plan Parameter
681
682 params(i).Label := Display_Label('HR_BIS_BUSINESS_PLAN');
683 params(i).Value := htf.formSelectOpen( 'BPL_ID' );
684
685 for ci_bpl in c_bpl loop
686
687 params(i).Value := params(i).Value ||
688 htf.formSelectOption(ci_bpl.plan_name,NULL,
689 'VALUE="'||ci_bpl.plan_id||'"');
690 end loop;
691
692 params(i).Value := params(i).Value || htf.formSelectClose;
693
694 END Build_Business_Plan;
695
696 --=========================================================================================
697
698 PROCEDURE Build_Geog_Level(
699 params IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type
700 ,i IN NUMBER
701 ) IS
702
703 cursor c_geog_level is
704 select '1' lev_code,
705 message_text lev
706 from fnd_new_messages
707 where message_name = 'HR_BIS_AREA'
708 and application_id = 453
709 and language_code = userenv('LANG')
710 union
711 select '2' lev_code,
712 message_text lev
713 from fnd_new_messages
714 where message_name = 'HR_BIS_COUNTRY'
715 and application_id = 453
716 and language_code = userenv('LANG')
717 union
718 select '3' lev_code,
719 message_text lev
720 from fnd_new_messages
721 where message_name = 'HR_BIS_REGION'
722 and application_id = 453
723 and language_code = userenv('LANG');
724
725 BEGIN
726
727 -- Creates Geography Dimension Level Parameter
728
729 params(i).Label := htf.formSelectOpen( 'GEOLVL',NULL, NULL,
730 'OnChange="getGeog(document.params.GEOVAL, '||
731 'document.params.GEOLVL.selectedIndex)" ' );
732
733 for ci_geog_level in c_geog_level loop
734
735 params(i).Label := params(i).Label ||
736 htf.formSelectOption(ci_geog_level.lev,NULL,
737 'VALUE="'||ci_geog_level.lev_code||'"');
738 end loop;
739
740 params(i).Label := params(i).Label || htf.formSelectClose;
741
742 END Build_Geog_Level;
743
744 --=========================================================================================
745
746 PROCEDURE Build_Geog_Value(
747 params IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type
748 ,i IN NUMBER
749 ) IS
750
751 BEGIN
752
753 -- Creates Geography Dimension Value Parameter
754
755 params(i).Value := htf.formSelectOpen( 'GEOVAL' );
756
757 for ci_area in c_area loop
758
759 params(i).Value := params(i).Value ||
760 htf.formSelectOption(ci_area.area,NULL,
761 'VALUE="'||ci_area.area_code||'"');
762 end loop;
763
764 params(i).Value := params(i).Value || htf.formSelectClose;
765
766 END Build_Geog_Value;
767
768 --=========================================================================================
769
770 PROCEDURE Build_Product (
771 params IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type
772 ,i IN NUMBER
773 ) IS
774
775 cursor c_product_category is
776 select '-1' id
777 , ' ' value
778 from dual
779 UNION
780 select id
781 , value
782 from bis_product_categories_v
783 order by 2;
784
785 BEGIN
786
787 -- Create Product Category parameter
788 params(i).Label := Display_Label('HR_BIS_PRODUCT_CATEGORY');
789 params(i).Value := htf.formSelectOpen( 'PRODID' );
790
791 for ci_product_category in c_product_category loop
792
793 params(i).Value := params(i).Value ||
794 htf.formSelectOption(ci_product_category.value,NULL,
795 'VALUE="'||ci_product_category.id||'"');
796 end loop;
797
798 params(i).Value := params(i).Value || htf.formSelectClose;
799
800 END Build_Product;
801
802 --=========================================================================================
803
804 PROCEDURE Build_Job_Category (
805 params IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type
806 ,i IN OUT NOCOPY NUMBER
807 ,p_option IN NUMBER
808 ) IS
809
810 j integer;
811
812 cursor c_job_category is
813 select lookup_code
814 ,meaning
815 from fnd_common_lookups
816 where lookup_type = 'JOB_CATEGORIES'
817 order by 2;
818
819 BEGIN
820
821 -- This procedure creates the Job Category parameter
822
823 IF p_option = 0 THEN
824 params(i).Label := Display_Label('HR_BIS_JOB_CATEGORY');
825 params(i).Value := htf.formSelectOpen( 'JOBCAT' );
826 params(i).Value := params(i).Value ||
827 htf.formSelectOption(' ',NULL, 'VALUE="__ALL__"');
828
829 for ci_job_category in c_job_category loop
830 params(i).Value := params(i).Value ||
831 htf.formSelectOption(ci_job_category.meaning,NULL,
832 'VALUE="'||ci_job_category.lookup_code||'"');
833 end loop;
834
835 params(i).Value := params(i).Value || htf.formSelectClose;
836 ELSE
837 params(i).Label := Display_Label('HR_BIS_JOB_CATEGORY1');
838 params(i).Value := htf.formSelectOpen( 'JOBCAT1' );
839 for ci_job_category in c_job_category loop
840 params(i).Value := params(i).Value ||
841 htf.formSelectOption(ci_job_category.meaning,NULL,
842 'VALUE="'||ci_job_category.lookup_code||'"');
843 end loop;
844 params(i).Value := params(i).Value || htf.formSelectClose;
845 i := i + 1;
846
847 params(i).Label := Display_Label('HR_BIS_JOB_CATEGORY2');
848 params(i).Value := htf.formSelectOpen( 'JOBCAT2' );
849 params(i).Value := params(i).Value ||
850 htf.formSelectOption(' ',NULL, 'VALUE="__ALL__"');
851 for ci_job_category in c_job_category loop
852 params(i).Value := params(i).Value ||
853 htf.formSelectOption(ci_job_category.meaning,NULL,
854 'VALUE="'||ci_job_category.lookup_code||'"');
855 end loop;
856 params(i).Value := params(i).Value || htf.formSelectClose;
857 i := i + 1;
858
859 params(i).Label := Display_Label('HR_BIS_JOB_CATEGORY3');
860 params(i).Value := htf.formSelectOpen( 'JOBCAT3' );
861 params(i).Value := params(i).Value ||
862 htf.formSelectOption(' ',NULL, 'VALUE="__ALL__"');
863 for ci_job_category in c_job_category loop
864 params(i).Value := params(i).Value ||
865 htf.formSelectOption(ci_job_category.meaning,NULL,
866 'VALUE="'||ci_job_category.lookup_code||'"');
867 end loop;
868 params(i).Value := params(i).Value || htf.formSelectClose;
869
870 END IF;
871
872 END Build_Job_Category;
873
874 --=========================================================================================
875
876
877 -- cbridge , 28/06/2001, added build_pqh_budget for pqh budget changes
878
879 PROCEDURE build_pqh_budget (
880 params IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type
881 ,i IN NUMBER
882 , p_return_status IN OUT NOCOPY NUMBER
883 ) IS
884
885 cursor c_bgt_id is
886 select b.budget_id, b.budget_name
887 from pqh_budgets b ,pqh_budget_versions bv
888 where b.business_group_id = g_bus_id
889 and b.budget_id = bv.budget_id
890 and trunc(sysdate) between bv.date_from and nvl( bv.date_to, sysdate+1 )
891 order by b.budget_name ;
892
893 l_budget_id_count number :=0;
894
895 BEGIN
896
897 -- This procedure creates the Budget parameter
898 params(i).Label := Display_Label('HR_BIS_BUDGET');
899 params(i).Value := htf.formSelectOpen( 'BGT_ID' );
900
901 for ci_bgt_id in c_bgt_id loop
902
903 params(i).Value := params(i).Value ||
904 htf.formSelectOption(ci_bgt_id.budget_name, NULL,
905 'VALUE="'||ci_bgt_id.budget_id||'"');
906
907 l_budget_id_count := l_budget_id_count+1;
908
909 end loop;
910
911 params(i).Value := params(i).Value || htf.formSelectClose;
912
913 IF l_budget_id_count = 0 THEN
914 p_return_status := 0; -- error, no budget set up
915 ELSE
916 p_return_status := 1;
917 END IF;
918
919 END build_pqh_budget;
920
921
922 PROCEDURE Build_Budget (
923 params IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type
924 ,i IN NUMBER
925 , p_return_status IN OUT NOCOPY NUMBER
926 ) IS
927
928 cursor c_bgt_id is
929 select b.budget_id, b.name
930 from per_budgets b ,per_budget_versions bv
931 where b.business_group_id = g_bus_id
932 and b.budget_id = bv.budget_id
933 and trunc(sysdate) between bv.date_from and nvl( bv.date_to, sysdate+1 )
934 order by b.name ;
935
936 l_budget_id_count number :=0;
937
938 BEGIN
939
940 -- This procedure creates the Budget Measurement Type parameter
941
942 params(i).Label := Display_Label('HR_BIS_BUDGET');
943 params(i).Value := htf.formSelectOpen( 'BGT_ID' );
944
945 for ci_bgt_id in c_bgt_id loop
946
947 params(i).Value := params(i).Value ||
948 htf.formSelectOption(ci_bgt_id.name, NULL,
949 'VALUE="'||ci_bgt_id.budget_id||'"');
950
951 l_budget_id_count := l_budget_id_count+1;
952
953 end loop;
954
955 params(i).Value := params(i).Value || htf.formSelectClose;
956
957 IF l_budget_id_count = 0 THEN
958 p_return_status := 0; -- error, no budget set up
959 ELSE
960 p_return_status := 1;
961 END IF;
962
963 END Build_Budget;
964
965 --=========================================================================================
966
967 PROCEDURE Build_Frequency (
968 params IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type
969 ,i IN NUMBER
970 ) IS
971
972 cursor c_frequency is
973 select lookup_code
974 ,meaning
975 from hr_lookups
976 where lookup_type = 'PROC_PERIOD_TYPE'
977 and lookup_code in ('CM','BM','Q','SY','Y')
978 order by decode(lookup_code, 'CM',1, 'BM',2, 'Q',3, 'SY',6, 'Y',12, 99);
979
980 BEGIN
981
982 -- This procedure creates the Frequency Parameter
983
984 params(i).Label := Display_Label('HR_BIS_FREQUENCY');
985 params(i).Value := htf.formSelectOpen( 'FRQNCY' );
986
987 for ci_frequency in c_frequency loop
988
989 params(i).Value := params(i).Value ||
990 htf.formSelectOption(ci_frequency.meaning, NULL,
991 'VALUE="'||ci_frequency.lookup_code||'"');
992 end loop;
993
994 params(i).Value := params(i).Value || htf.formSelectClose;
995
996 END Build_Frequency;
997
998 --=========================================================================================
999
1000 PROCEDURE Geography_Cross_Validation IS
1001
1002 -- This procedure builds the JavaScript code for cross-validation of the
1003 -- Geography parameter.
1004 -- Whenever the Geography dimension level (Area/Country/Region) changes,
1005 -- the values displayed in the Geography LOV must change.
1006
1007 -- Cursor for Countries
1008
1009 cursor c_country is
1010 select '-1' territory_code
1011 , ' ' country
1012 from dual
1013 union
1014 select territory_code
1015 , territory_short_name country
1016 from fnd_territories_vl
1017 order by 2;
1018
1019 -- Cursor for Regions
1020
1021 cursor c_region is
1022 select '-1' region_code, ' ' region
1023 from dual
1024 union
1025 select lookup_code region_code, meaning region
1026 from fnd_lookups
1027 where lookup_type = 'REGION'
1028 order by 2;
1029
1030 -- bug 1301690 start
1031
1032 java_string varchar2(6000);
1033 java_string2 varchar2(32767);
1034 java_string3 varchar2(6000);
1035
1036 BEGIN
1037
1038 -- js.scriptopen;
1039 -- icx_util.lovscript;
1040 -- js.scriptclose;
1041
1042 -- Java Script to create functionality for cross validation of Geography parameters
1043
1044 java_string := java_string|| 'function getGeog(field, index)' ;
1045 java_string := java_string|| '{' ;
1046 java_string := java_string|| ' while( field.length > 0 )' ;
1047 java_string := java_string|| ' field.options[field.length -1 ] = null;' ;
1048 java_string := java_string|| ' if (index == 0 )' ;
1049 java_string := java_string|| '{' ;
1050
1051 for ci_area in c_area loop
1052 java_string := java_string|| ' field.options[field.length] = ';
1053 java_string := java_string|| ' new Option(''' ||replace(ci_area.area, '''');
1054 java_string := java_string|| ''',''' ||ci_area.area_code||''');';
1055 end loop;
1056
1057 java_string := java_string|| '}' ;
1058 java_string := java_string|| ' else if (index == 1 )' ;
1059 java_string := java_string|| '{' ;
1060
1061 for ci_country in c_country loop
1062
1063 java_string2 := java_string2|| ' field.options[field.length] = ';
1064 java_string2 := java_string2|| ' new Option('''||replace(ci_country.country, '''');
1065 java_string2 := java_string2|| ''',''' ||ci_country.territory_code||''');' ;
1066 end loop;
1067
1068 java_string2 := java_string2|| '}' ;
1069 java_string2 := java_string2|| ' else if (index == 2 )' ;
1070 java_string2 := java_string2|| '{' ;
1071
1072 for ci_region in c_region loop
1073 java_string3 := java_string3|| ' field.options[field.length] = ';
1074 java_string3 := java_string3|| ' new Option('''||replace(ci_region.region, '''');
1075 java_string3 := java_string3|| ''','''||ci_region.region_code||''');' ;
1076 end loop;
1077
1078 java_string3 := java_string3|| '}' ;
1079 java_string3 := java_string3|| '}' ;
1080 java_string3 := java_string3|| ' ' ;
1081
1082 -- Build up Java Script
1083 BIS_UTILITIES_PUB.Build_Report_Header(java_string||java_string2||java_string3);
1084
1085 -- bug 1301690 end
1086
1087 END Geography_Cross_Validation;
1088
1089 --=========================================================================================
1090
1091 PROCEDURE Build_Budget_ID(
1092 params IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type
1093 ,i IN NUMBER
1094 ) IS
1095
1096
1097 -- This function sets up a drop down list containing the manpower budgets which
1098 -- have been set up for the current responsibilities business group.
1099 -- The value returned by this drop down list will be called 'BGT_ID'
1100 -- Parameter Request String : BGT_ID
1101
1102 cursor c_bgt_id is
1103 select b.budget_id, b.name
1104 from per_budgets b ,per_budget_versions bv
1105 where b.business_group_id = g_bus_id
1106 and b.budget_id = bv.budget_id
1107 and trunc(sysdate) between bv.date_from and nvl( bv.date_to, sysdate+1 );
1108
1109 BEGIN
1110
1111 params(i).Label := Display_Label('HR_BIS_BUDGET');
1112 params(i).Value := htf.formSelectOpen('BGT_ID');
1113
1114 for ci_bgt_id in c_bgt_id loop
1115 params(i).Value := params(i).Value ||
1116 htf.formSelectOption(ci_bgt_id.name, NULL,
1117 'VALUE="'||ci_bgt_id.budget_id||'"');
1118
1119
1120 end loop;
1121
1122 params(i).Value := params(i).Value || htf.formSelectClose;
1123
1124
1125 END Build_Budget_ID;
1126
1127 --=========================================================================================
1128
1129 PROCEDURE Build_Competence_ID(
1130 params IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type
1131 ,i IN NUMBER
1132 ,p_return_status IN OUT NOCOPY NUMBER
1133 ) IS
1134
1135 -- This function sets up a drop down list containing the competancies
1136 -- which have been set up for the current responsibilities business group.
1137 -- The value returned by this drop down list will be called 'COMPID'.
1138 -- Parameter Request String : COMPID
1139
1140 cursor c_compid is
1141 select competence_id, name
1142 from per_competences_vl
1143 where business_group_id = g_bus_id
1144 or business_group_id is null -- bug 2518364 dsheth Sep 3, 2002
1145 order by 2;
1146
1147 l_comp_id_count number :=0;
1148
1149 BEGIN
1150
1151 params(i).Label := Display_Label('HR_BIS_COMPETENCE');
1152
1153 params(i).Value := htf.formSelectOpen('COMPID');
1154
1155 for ci_compid in c_compid loop
1156 params(i).Value := params(i).Value ||
1157 htf.formSelectOption(ci_compid.name, NULL,
1158 'VALUE="'||ci_compid.competence_id||'"');
1159
1160 l_comp_id_count := l_comp_id_count+1;
1161
1162 end loop;
1163
1164 params(i).Value := params(i).Value || htf.formSelectClose;
1165
1166 IF l_comp_id_count = 0 THEN
1167 p_return_status := 0; -- error, no competencies set up
1168 ELSE
1169 p_return_status := 1;
1170 END IF;
1171
1172
1173 END Build_Competence_ID;
1174
1175 --=========================================================================================
1176
1177 PROCEDURE Build_Report_Currency(
1178 params IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type
1179 ,i IN NUMBER
1180 ) IS
1181 -- This function gives a drop down list showing the currencies in
1182 -- which the figures can appear on the report
1183 -- Parameter Request String : CURRCD
1184
1185 cursor c_currcd is
1186 select c.currency_code , c.name
1187 from per_business_groups b , fnd_currencies_vl c
1188 where b.business_group_id = hr_bis.get_sec_profile_bg_id -- bug 2968520
1189 and c.enabled_flag = 'Y'
1190 and sysdate between nvl(c.start_date_active, hr_general.start_of_time)
1191 and nvl(c.end_date_active, hr_general.end_of_time)
1192 order by decode(b.currency_code, c.currency_code, 1,2) , c.currency_code;
1193
1194 BEGIN
1195
1196 params(i).Label := Display_Label('HR_BIS_REPORT_CURRENCY');
1197
1198 params(i).Value := htf.formSelectOpen('CURRCD');
1199
1200 for ci_currcd in c_currcd loop
1201 params(i).Value := params(i).Value ||
1202 htf.formSelectOption(ci_currcd.currency_code||' - '||ci_currcd.name, NULL,
1203 'VALUE="'||ci_currcd.currency_code||'"');
1204 end loop;
1205
1206 params(i).Value := params(i).Value || htf.formSelectClose;
1207
1208 END Build_Report_Currency;
1209
1210 --=========================================================================================
1211
1212 PROCEDURE Build_Display_By(
1213 params IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type
1214 ,i IN NUMBER
1215 ) IS
1216
1217 inc_amt integer;
1218
1219 -- This function sets up a drop down list containing two display by options.
1220 -- The first part of the function builds a list containing options to see
1221 -- the Top or Bottom values; the variable returned by this drop down
1222 -- list will be called 'DISPLY'.
1223 -- The second part of the function builds a numeric list,
1224 -- to elect how many rows the user wishes to see on the report;
1225 -- the variable returned by this drop down list will be called 'DCOUNT.
1226
1227 BEGIN
1228
1229 -- Parameter Request String : DISPLY
1230
1231 params(i).Label := Display_Label('HR_BIS_DISPLAY');
1232 params(i).Value := htf.formSelectOpen('DISPLY');
1233 params(i).Value := params(i).Value || htf.formSelectOption(fnd_message.get_string( 'HRI' ,'HR_BIS_TOP'), NULL, 'VALUE="'||'HR_BIS_TOP'||'"');
1234 params(i).Value := params(i).Value || htf.formSelectOption(fnd_message.get_string( 'HRI' ,'HR_BIS_BOTTOM'), NULL, 'VALUE="'||'HR_BIS_BOTTOM'||'"');
1235
1236 params(i).Value := params(i).Value || htf.formSelectClose;
1237
1238 params(i).Value := params(i).Value || htf.formSelectOpen('DCOUNT');
1239
1240 inc_amt := 1;
1241 for inc_val IN 1 .. 9
1242 LOOP
1243 params(i).Value := params(i).Value || htf.formSelectOption(inc_val, NULL, 'VALUE="'||inc_val||'"');
1244 END LOOP;
1245
1246 params(i).Value := params(i).Value || htf.formSelectOption(10, 'Yes', 'VALUE="10"');
1247
1248 for inc_val IN 11.. 30
1249 LOOP
1250 params(i).Value := params(i).Value || htf.formSelectOption(inc_val, NULL, 'VALUE="'||inc_val||'"');
1251 END LOOP;
1252 inc_amt := 2;
1253
1254 for inc_val IN 16 .. 24
1255 LOOP
1256 params(i).Value := params(i).Value || htf.formSelectOption(inc_amt*inc_val, NULL, 'VALUE="'||inc_amt*inc_val||'"');
1257 END LOOP;
1258
1259 inc_amt := 5;
1260
1261 for inc_val IN 10 .. 19
1262 LOOP
1263 params(i).Value := params(i).Value || htf.formSelectOption(inc_amt*inc_val, NULL, 'VALUE="'||inc_amt*inc_val||'"');
1264 END LOOP;
1265
1266 params(i).Value := params(i).Value || htf.formSelectOption('99', NULL, 'VALUE="99"');
1267
1268 params(i).Value := params(i).Value || htf.formSelectClose;
1269
1270 END Build_Display_by;
1271
1272 --=========================================================================================
1273
1274 PROCEDURE Build_Exclude_Currency(
1275 params IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type
1276 ,i IN NUMBER
1277 ) IS
1278
1279 -- This list gives the option to include (or exclude)
1280 -- currencies other than the reporting currency.
1281 -- Parameter Request String : EXCCUR
1282
1283 cursor c_exccur is
1284 select message_name , message_text
1285 from fnd_new_messages
1286 where message_name in ('HR_BIS_INCLUDE','HR_BIS_EXCLUDE')
1287 and language_code = userenv('LANG')
1288 and application_id = 453
1289 order by decode(message_name,'HR_BIS_EXCLUDE','1,2');
1290
1291 BEGIN
1292
1293 params(i).Label := Display_Label('HR_BIS_OTHER_CURRENCIES');
1294 params(i).Value := htf.formSelectOpen('EXCCUR');
1295
1296 for ci_exccur in c_exccur loop
1297 params(i).Value := params(i).Value ||
1298 htf.formSelectOption(ci_exccur.message_text, NULL,
1299 'VALUE="'||ci_exccur.message_name||'"');
1300 end loop;
1301
1302 params(i).Value := params(i).Value || htf.formSelectClose;
1303
1304 END Build_Exclude_Currency;
1305
1306 --=========================================================================================
1307
1308 /*
1309 This procedure was commented out to fix the bug 1863276.
1310 D.sheth 08-AUG-2001
1311
1312 PROCEDURE Build_Grade_ID(
1313 params IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type
1314 ,i IN NUMBER
1315 ) IS
1316
1317 -- This function gives a list of grades as defined in HR,
1318 -- upon which the report willl be based.
1319 -- Parameter Request String : GRD_ID
1320
1321 cursor c_grd_id is
1322 select grade_id,name
1323 from per_grades
1324 where business_group_id = g_bus_id
1325 order by name;
1326
1327 BEGIN
1328
1329 params(i).Label := Display_Label('HR_BIS_GRADE');
1330 params(i).Value := htf.formSelectOpen('GRD_ID');
1331 params(i).Value := params(i).Value || htf.formSelectOption(' ', NULL, 'VALUE="-1"');
1332
1333 for ci_grd_id in c_grd_id loop
1334 params(i).Value := params(i).Value ||
1335 htf.formSelectOption(ci_grd_id.name, NULL,
1336 'VALUE="'||ci_grd_id.grade_id||'"');
1337 end loop;
1338
1339 params(i).Value := params(i).Value || htf.formSelectClose;
1340
1341 END Build_Grade_ID;
1342 */
1343 --=========================================================================================
1344
1345 PROCEDURE Build_Employee_Applicant(
1346 params IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type
1347 ,i IN NUMBER
1348 ) IS
1349
1350 -- Facility Provided : gives an option whether to show employees or applicants on the report
1351 -- Parameter Request String : EMPAPL
1352
1353 BEGIN
1354
1355 params(i).Label := Display_Label('HR_BIS_EMP_APP');
1356 params(i).Value := htf.formSelectOpen('EMPAPL');
1357 params(i).Value := params(i).Value
1358 || htf.formSelectOption(fnd_message.get_string( 'HRI' ,'HR_BIS_CURRENT_EMPL_IN_JOB'),
1359 NULL,
1360 'VALUE="E"');
1361 params(i).Value := params(i).Value
1362 || htf.formSelectOption(fnd_message.get_string( 'HRI' ,'HR_BIS_CURRENT_APPL_FOR_JOB'),
1363 NULL,
1364 'VALUE="A"');
1365
1366 params(i).Value := params(i).Value || htf.formSelectClose;
1367
1368 END Build_Employee_Applicant;
1369
1370 --=========================================================================================
1371
1372 PROCEDURE Build_Order_By(
1373 params IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type
1374 ,i IN NUMBER
1375 ,p_option IN NUMBER
1376 ) IS
1377
1378 -- Parameter Request String : ORDERB1, ORDERB2
1379 -- Depending on p_option, this function sets up a
1380 -- list to elect to order the report by "total" / "percent"
1381 -- separated OR by "total" / "percent" increase in manpower.
1382 -- Parameters received : p_option - value 1 returns a list for
1383 -- 'Separated'; value 2 returns a list for 'Increase'
1384
1385 BEGIN
1386
1387 params(i).Label := Display_Label('HR_BIS_ORDER_BY');
1388 params(i).Value := htf.formSelectOpen('ORDERB');
1389
1390 IF p_option = 1
1391 THEN
1392 params(i).Value := params(i).Value ||
1393 htf.formSelectOption(fnd_message.get_string('HRI' ,'HR_BIS_TOTAL')
1394 || ' ' || fnd_message.get_string('HRI', 'HR_BIS_SEPARATED'),
1395 NULL, 'VALUE="HR_BIS_TOTAL"');
1396 params(i).Value := params(i).Value ||
1397 htf.formSelectOption(fnd_message.get_string('HRI', 'HR_BIS_PERCENT')
1398 || ' ' || fnd_message.get_string('HRI', 'HR_BIS_SEPARATED'),
1399 NULL, 'VALUE="HR_BIS_PERCENT"');
1400 ELSIF p_option = 2
1401 THEN
1402 params(i).Value := params(i).Value ||
1403 htf.formSelectOption(fnd_message.get_string('HRI', 'HR_BIS_PERCENT')
1404 || ' ' || fnd_message.get_string('HRI', 'HR_BIS_INCREASE'),
1405 NULL, 'VALUE="HR_BIS_PERCENT"');
1406 params(i).Value := params(i).Value ||
1407 htf.formSelectOption(fnd_message.get_string('HRI' ,'HR_BIS_TOTAL')
1408 || ' ' || fnd_message.get_string('HRI', 'HR_BIS_INCREASE'),
1409 NULL, 'VALUE="HR_BIS_TOTAL"');
1410 END IF;
1411
1412 params(i).Value := params(i).Value || htf.formSelectClose;
1413
1414 END Build_Order_By;
1415
1416 --=========================================================================================
1417
1418 PROCEDURE Build_Incl_Subord(
1419 params IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type
1420 ,i IN NUMBER
1421 ,p_option IN NUMBER
1422 ) IS
1423
1424 -- Parameter Request String : ORGPRC1
1425 -- gives the option of rolling up the values
1426 -- this parameter is for reports which Include Subordinates
1427 --
1428 -- Parameter Request String : ORGPRC2
1429 -- gives the option of showing a single instance,
1430 -- or of showing the selected organisation with all subordinates.
1431 -- This parameter is for reports which do not Roll Up values.
1432 --
1433 -- Parameter Request String : ORGPRC3
1434 -- gives the option of rolling up the values
1435 -- this parameter is for reports which show only the selected organisation
1436 --
1437
1438 cursor c_lookup is
1439 select meaning, lookup_code
1440 from hr_lookups
1441 where lookup_type = 'YES_NO'
1442 and lookup_code IN ('Y', 'N');
1443
1444 lookup_yes varchar2(80);
1445 lookup_no varchar2(80);
1446
1447 BEGIN
1448
1449 -- If p_option = 1 then the rollup label needs to be a rollup each org
1450 -- label. Bug #1349001 - mjandrew - 01-Aug-2000
1451 IF p_option = 1 THEN
1452 params(i).Label := Display_Label('HR_BIS_ROLLUP_EACH_ORG');
1453 ELSE
1454 params(i).Label := Display_Label('HR_BIS_ROLLUP_ORGANIZATIONS');
1455 END IF;
1456 params(i).Value := htf.formSelectOpen('ORGPRC');
1457
1458 for ci_lookup in c_lookup loop
1459 IF ci_lookup.lookup_code = 'Y'
1460 THEN
1461 lookup_yes := ci_lookup.meaning;
1462 ELSE
1463 lookup_no := ci_lookup.meaning;
1464 END IF;
1465 end loop;
1466
1467 if p_option = 1
1468 THEN
1469 -- 'ISRO'-'Y' 'ISNR'-'N'
1470 params(i).Value := params(i).Value ||
1471 htf.formSelectOption(lookup_yes, NULL, 'VALUE="ISRO"');
1472
1473 -- this one is the default selection, so has 'Yes' instead of NULL for the second parameter.
1474 params(i).Value := params(i).Value ||
1475 htf.formSelectOption(lookup_no, 'Yes', 'VALUE="ISNR"');
1476
1477 ELSIF p_option = 2
1478 THEN
1479 -- 'ISNR'-'Y' 'SINR'-'N'
1480 params(i).Value := params(i).Value ||
1481 htf.formSelectOption(lookup_yes, NULL, 'VALUE="ISNR"');
1482 params(i).Value := params(i).Value ||
1483 htf.formSelectOption(lookup_no, NULL, 'VALUE="SINR"');
1484 ELSIF p_option = 3
1485 THEN
1486 -- 'SIRO'-'Y' 'SINR'-'N'
1487 params(i).Value := params(i).Value ||
1488 htf.formSelectOption(lookup_yes, NULL, 'VALUE="SIRO"');
1489 params(i).Value := params(i).Value ||
1490 htf.formSelectOption(lookup_no, NULL, 'VALUE="SINR"');
1491 END IF;
1492
1493 params(i).Value := params(i).Value || htf.formSelectClose;
1494
1495 END Build_Incl_Subord;
1496
1497 --=========================================================================================
1498
1499 PROCEDURE Build_Payroll_ID(
1500 params IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type
1501 ,i IN NUMBER
1502 ,p_option IN NUMBER
1503 ,p_return_status IN OUT NOCOPY NUMBER
1504 ) IS
1505
1506 -- This function gives a list of payrolls which can be viewed on the report.
1507 -- Parameter Request String : PAYRLL
1508
1509 cursor c_payrll is
1510 select DISTINCT payroll_id , payroll_name
1511 from pay_payrolls_f
1512 where business_group_id = g_bus_id
1513 order by payroll_name;
1514 -- bug 3264873, added DISTINCT in cursor to exlcude multiple payroll names
1515 -- that appear to customers to be duplicates.
1516
1517 l_payroll_id_counter number :=0;
1518
1519 BEGIN
1520
1521 params(i).Label := Display_Label('HR_BIS_PAYROLL');
1522 params(i).Value := htf.formSelectOpen('PAYRLL');
1523
1524 IF p_option = 1 THEN
1525 params(i).Value := params(i).Value || htf.formSelectOption(' ', NULL, 'VALUE="-1"');
1526 END IF;
1527
1528 for ci_payrll in c_payrll loop
1529 params(i).Value := params(i).Value ||
1530 htf.formSelectOption(ci_payrll.payroll_name, NULL,
1531 'VALUE="'||ci_payrll.payroll_id||'"');
1532
1533 l_payroll_id_counter := l_payroll_id_counter +1;
1534
1535 end loop;
1536
1537 params(i).Value := params(i).Value || htf.formSelectClose;
1538
1539 IF l_payroll_id_counter = 0 THEN
1540 p_return_status := 0; -- error, no payrolls set up
1541 ELSE
1542 p_return_status := 1;
1543 END IF;
1544
1545
1546 END Build_Payroll_ID;
1547
1548 --=========================================================================================
1549
1550 PROCEDURE Build_Rep_Dates(
1551 params IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type
1552 ,i IN NUMBER
1553 ,P_OPTION IN NUMBER
1554 ) IS
1555
1556 -- DATE1 - gives a single date on which to report (snapshot)
1557 -- DATE2 - gives two date fields representing the start and end of a time period (range)
1558
1559 BEGIN
1560
1561 If p_option = 1 THEN
1562 params(i).Label := Display_Label('HR_BIS_REPORTING_DATE');
1563 params(i).Value := htf.formtext('P_REPORT_DATE_V', 11, NULL, to_char(sysdate,'DD-MON-YYYY'));
1564 else
1565 params(i).Label := Display_Label('HR_BIS_REPORTING_DATES');
1566
1567 params(i).Value := htf.formText('P_START_DATE_V',11,NULL, to_char(add_months(sysdate,-12)+1,'DD-MON-YYYY'))
1568 ||' - '||
1569 htf.formText('P_END_DATE_V',11,NULL, to_char(sysdate,'DD-MON-YYYY'));
1570 end if;
1571
1572 END Build_Rep_Dates;
1573
1574 --=========================================================================================
1575
1576 PROCEDURE Build_lookup_param(
1577 params IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type
1578 ,i IN NUMBER
1579 ,P_LABEL IN VARCHAR2
1580 ,P_FORM_NAME IN VARCHAR2
1581 ,P_LOOKUP_TYPE IN VARCHAR2
1582 ,P_ALL_OPTION IN VARCHAR2
1583 ) IS
1584
1585 -- Lookup Parameters
1586 -- The following parameters are passed to this function :
1587 -- P_LABEL - contains the label to be displayed next to the list
1588 -- P_FORM_NAME - contains the name of the variable the form field returns
1589 -- P_LOOKUP_TYPE - contains the look up type to be looked up on fnd_common_lookups
1590 -- P_ALL_OPTION - if the field is to have an 'all values' option,
1591 -- it is passed in here, otherwise this must be empty - i.e. ''
1592
1593 cursor c_lookup(p_lookup_type in varchar2) is
1594 select lookup_code,meaning
1595 from hr_lookups /* bug fix 3323544*/
1596 where lookup_type = p_lookup_type
1597 and enabled_flag = 'Y' /* bug fix 1608726 */
1598 order by 2;
1599
1600 BEGIN
1601
1602 params(i).Label := Display_Label(P_LABEL);
1603 params(i).Value := htf.formSelectOpen(P_FORM_NAME);
1604
1605 IF P_ALL_OPTION IS NOT NULL THEN
1606 params(i).Value := params(i).Value || htf.formSelectOption(' ', NULL, 'VALUE="' || P_ALL_OPTION || '"');
1607 END IF;
1608
1609 for ci_lookup in c_lookup(P_LOOKUP_TYPE) loop
1610 params(i).Value := params(i).Value ||
1611 htf.formSelectOption(ci_lookup.meaning, NULL,
1612 'VALUE="'||ci_lookup.lookup_code||'"');
1613 end loop;
1614
1615 params(i).Value := params(i).Value || htf.formSelectClose;
1616
1617 END Build_lookup_param;
1618
1619 --=========================================================================================
1620
1621 PROCEDURE Build_Proficiency_Measure(
1622 params IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type
1623 ,i IN NUMBER
1624 ) IS
1625
1626 -- Proficiency Measure to Compare Against.
1627 -- This gives the option to base the report on a Minimum
1628 -- Proficiency level required, or on the range of proficiencies found.
1629 -- Parameter Request String : PFMEAS
1630
1631 BEGIN
1632
1633 params(i).Label := Display_Label('HR_BIS_PROFICIENCY_LEVEL');
1634 params(i).Value := htf.formSelectOpen('PFMEAS');
1635 params(i).Value := params(i).Value ||
1636 htf.formSelectOption(fnd_message.get_string('HRI','HR_BIS_MINIMUM_PROFICIENCY'),
1637 NULL,
1638 'VALUE="M"');
1639 params(i).Value := params(i).Value ||
1640 htf.formSelectOption(fnd_message.get_string('HRI','HR_BIS_PROFICIENCY_RANGE'),
1641 NULL,
1642 'VALUE="R"');
1643
1644 params(i).Value := params(i).Value || htf.formSelectClose;
1645
1646 END Build_Proficiency_Measure;
1647
1648 --=========================================================================================
1649
1650 PROCEDURE Build_View_By(
1651 params IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type
1652 ,i IN NUMBER
1653 ,p_option IN NUMBER
1654 ) IS
1655
1656 -- View By
1657 -- Gives a list of categories which the data in the report can be broken down into.
1658 -- Parameter Request String : VIEWBY1, VIEWBY2, VIEWBY3
1659 -- p_option - determines which set of view by options appears
1660
1661 cursor c_viewby1 is
1662 select message_name , message_text
1663 from fnd_new_messages
1664 where language_code = userenv('LANG')
1665 and application_id = 453
1666 and message_name in ('HR_BIS_GRADE', 'HR_BIS_PERFORMANCE_RATING', 'HR_BIS_SERVICE_BAND',
1667 'HR_BIS_JOB', 'HR_BIS_LOCATION', 'HR_BIS_GENDER')
1668 UNION
1669 select message_name , message_text
1670 from fnd_new_messages , per_business_groups
1671 where business_group_id = hr_bis.get_sec_profile_bg_id -- bug 2968520
1672 and legislation_code = 'US'
1673 and language_code = userenv('LANG')
1674 and application_id = 453
1675 and message_name = 'HR_BIS_EEO_CATEGORY'
1676 UNION
1677 select message_name , message_text
1678 from fnd_new_messages fnm , per_business_groups bus
1679 where bus.business_group_id = hr_bis.get_sec_profile_bg_id -- bug 2968520
1680 and language_code = userenv('LANG')
1681 and application_id = 453
1682 and legislation_code <> 'US'
1683 and message_name = 'HR_BIS_ETHNIC_ORIGIN' order by 2;
1684
1685 cursor c_viewby2 is
1686 select message_name , message_text
1687 from fnd_new_messages
1688 where language_code = userenv('LANG')
1689 and application_id = 453
1690 and message_name in ('HR_BIS_AGE_YEARS', 'HR_BIS_LOS_YEARS', 'HR_BIS_GRADE', 'HR_BIS_PERFORMANCE_RATING')
1691 order by message_name;
1692
1693 cursor c_viewby3 is
1694 select message_name,
1695 message_text
1696 from fnd_new_messages
1697 where message_name IN ('HR_BIS_TIME', 'HR_BIS_GEOGRAPHY', 'HR_BIS_PRODUCT')
1698 and application_id = 453
1699 and language_code = userenv('LANG')
1700 order by 1 desc;
1701
1702 BEGIN
1703
1704 params(i).Label := Display_Label('HR_BIS_VIEW_BY');
1705 params(i).Value := htf.formSelectOpen('VIEWBY');
1706
1707 IF p_option = 1 THEN
1708 for ci_viewby in c_viewby1 loop
1709 params(i).Value := params(i).Value ||
1710 htf.formSelectOption(ci_viewby.message_text, NULL, 'VALUE="'||ci_viewby.message_name||'"');
1711 end loop;
1712 ELSIF p_option = 2 THEN
1713 for ci_viewby in c_viewby2 loop
1714 params(i).Value := params(i).Value ||
1715 htf.formSelectOption(ci_viewby.message_text, NULL, 'VALUE="'||ci_viewby.message_name||'"');
1716 end loop;
1717 ELSIF p_option = 3 THEN
1718 for ci_viewby in c_viewby3 loop
1719 params(i).Value := params(i).Value ||
1720 htf.formSelectOption(ci_viewby.message_text, NULL, 'VALUE="'||ci_viewby.message_name||'"');
1721 end loop;
1722 END IF;
1723
1724 params(i).Value := params(i).Value || htf.formSelectClose;
1725
1726 END Build_View_By;
1727
1728 --=========================================================================================
1729
1730 PROCEDURE Build_LOV(
1731 params IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type
1732 ,i IN Number
1733 ,p_option IN Number
1734 ,param_name IN varchar2
1735 ,LOV_name IN varchar2
1736 ) IS
1737
1738 l_where_clause varchar2(10000);
1739 l_return_status varchar2(1000);
1740 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1741
1742 BEGIN
1743
1744 params(i).Label := Display_Label('HR_BIS_'||LOV_Name);
1745
1746 params(i).Value :=
1747 htf.formHidden(param_name)|| -- AK Attribute 1
1748 htf.formText('P_'||LOV_name||'_NAME',30,200,''); -- AK Attribute 2
1749
1750 params(i).Action := '<A HREF="javascript:LOV
1751 (453
1752 ,''P_'||LOV_name||'_NAME''
1753 ,453
1754 ,''HRI_'||LOV_name||'''
1755 ,''params''
1756 ,''''
1757 ,'''||l_where_clause||'''
1758 ,'''')" onMouseOver="window.status=''List of Values'';return true"><IMG SRC="/OA_MEDIA/FNDILOV.gif" ALT="List of Values" ALIGN=absmiddle BORDER=0></A>';
1759
1760 END Build_LOV;
1761
1762 Procedure Build_Organization(
1763 params IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type
1764 ,i IN Number
1765 ,p_option IN Number
1766 ) IS
1767
1768 cursor c_organization_list is
1769 select organization_id, organization_name
1770 from hri_org_lov_v
1771 order by organization_name;
1772
1773
1774 BEGIN
1775
1776 params(i).Label := Display_Label('HR_BIS_ORGANIZATION');
1777 params(i).Value := htf.formSelectOpen('P_ORGANIZATION_ID');
1778
1779 IF p_option = 1 THEN
1780 params(i).Value := params(i).Value || htf.formSelectOption(' ', NULL, 'VALUE="-1"');
1781 END IF;
1782
1783 for ci_org_list in c_organization_list loop
1784 params(i).Value := params(i).Value ||
1785 htf.formSelectOption(ci_org_list.organization_name, NULL,
1786 'VALUE="'||ci_org_list.organization_id||'"');
1787
1788 end loop;
1789
1790 params(i).Value := params(i).Value || htf.formSelectClose;
1791
1792 END Build_Organization;
1793
1794
1795
1796 PROCEDURE Build_Job(
1797 params IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type
1798 ,i IN Number
1799 ,p_option IN Number
1800 ,p_return_status IN OUT NOCOPY NUMBER
1801 ) IS
1802
1803 cursor c_job_list is
1804 select job_id, name job_name
1805 from per_jobs -- bug fix 3680782
1806 where business_group_id = Hr_General.Get_Business_Group_Id
1807 order by name; -- use of p_option = 1
1808
1809 l_job_id_count number :=0;
1810
1811 BEGIN
1812
1813 params(i).Label := Display_Label('HR_BIS_JOB');
1814 params(i).Value := htf.formSelectOpen('P_JOB_ID');
1815
1816 IF p_option = 1 THEN
1817 params(i).Value := params(i).Value || htf.formSelectOption(' ', NULL, 'VALUE="-1"');
1818 END IF;
1819
1820 for ci_job_list in c_job_list loop
1821 params(i).Value := params(i).Value ||
1822 htf.formSelectOption(ci_job_list.job_name, NULL,
1823 'VALUE="'||ci_job_list.job_id||'"');
1824
1825 l_job_id_count := l_job_id_count+1;
1826
1827 end loop;
1828
1829 params(i).Value := params(i).Value || htf.formSelectClose;
1830
1831 IF l_job_id_count = 0 THEN
1832 p_return_status := 0; -- error, no jobs set up
1833 ELSE
1834 p_return_status := 1;
1835 END IF;
1836
1837 END Build_Job;
1838
1839 PROCEDURE Build_Location(
1840 params IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type
1841 , i IN NUMBER
1842 , p_option IN NUMBER
1843 ) IS
1844
1845 cursor c_loc_list is
1846 select location_id, location_name
1847 from hri_loc_lov_v;
1848
1849 BEGIN
1850
1851 params(i).Label := Display_Label('HR_BIS_LOCATION');
1852 params(i).Value := htf.formSelectOpen('P_LOCATION_ID');
1853
1854 IF p_option = 1 THEN
1855 params(i).Value := params(i).Value || htf.formSelectOption(' ', NULL, 'VALUE="-1"');
1856 END IF;
1857
1858 for ci_loc_list in c_loc_list loop
1859 params(i).Value := params(i).Value ||
1860 htf.formSelectOption(ci_loc_list.location_name, NULL,
1861 'VALUE="'||ci_loc_list.location_id||'"');
1862
1863 end loop;
1864
1865 params(i).Value := params(i).Value || htf.formSelectClose;
1866
1867 END Build_Location;
1868
1869 -- LINK_ procedures modified to make them call the LINK_PARAMPAGE
1870 -- procedure, so that they will use the parameters in the database.
1871 -- mjandrew - 31-Jul-2000 - Bug #1349114
1872
1873 PROCEDURE LINK_HRCOMGAP IS
1874 BEGIN
1875 LINK_PARAMPAGE('HRCOMGAP');
1876 END LINK_HRCOMGAP;
1877
1878 PROCEDURE LINK_HRCOMPEO IS
1879 BEGIN
1880 LINK_PARAMPAGE('HRCOMPEO');
1881 END LINK_HRCOMPEO;
1882
1883 PROCEDURE LINK_HRCOMREC IS
1884 BEGIN
1885 LINK_PARAMPAGE('HRCOMREC');
1886 END LINK_HRCOMREC;
1887
1888 PROCEDURE LINK_HRMNPBGT IS
1889 BEGIN
1890 LINK_PARAMPAGE('HRMNPBGT');
1891 END LINK_HRMNPBGT;
1892
1893 PROCEDURE LINK_HRMNPCMP IS
1894 BEGIN
1895 LINK_PARAMPAGE('HRMNPCMP');
1896 END LINK_HRMNPCMP;
1897
1898 PROCEDURE LINK_HRMNPRAT IS
1899 BEGIN
1900 LINK_PARAMPAGE('HRMNPRAT');
1901 END LINK_HRMNPRAT;
1902
1903 PROCEDURE LINK_HRMNPSUM IS
1904 BEGIN
1905 LINK_PARAMPAGE('HRMNPSUM');
1906 END LINK_HRMNPSUM;
1907
1908 PROCEDURE LINK_HRORGBGT IS
1909 BEGIN
1910 LINK_PARAMPAGE('HRORGBGT');
1911 END LINK_HRORGBGT;
1912
1913 PROCEDURE LINK_HRORGCHG IS
1914 BEGIN
1915 LINK_PARAMPAGE('HRORGCHG');
1916 END LINK_HRORGCHG;
1917
1918 PROCEDURE LINK_HRORGSEP IS
1919 BEGIN
1920 LINK_PARAMPAGE('HRORGSEP');
1921 END LINK_HRORGSEP;
1922
1923 PROCEDURE LINK_HRSALCOM IS
1924 BEGIN
1925 LINK_PARAMPAGE('HRSALCOM');
1926 END LINK_HRSALCOM;
1927
1928 PROCEDURE LINK_HRSALGRG IS
1929 BEGIN
1930 LINK_PARAMPAGE('HRSALGRG');
1931 END LINK_HRSALGRG;
1932
1933 PROCEDURE LINK_HRSALGRP IS
1934 BEGIN
1935 LINK_PARAMPAGE('HRSALGRP');
1936 END LINK_HRSALGRP;
1937
1938 PROCEDURE LINK_HRSALSPD IS
1939 BEGIN
1940 LINK_PARAMPAGE('HRSALSPD');
1941 END LINK_HRSALSPD;
1942
1943 PROCEDURE LINK_HRSALTND IS
1944 BEGIN
1945 LINK_PARAMPAGE('HRSALTND');
1946 END LINK_HRSALTND;
1947
1948 PROCEDURE LINK_HRTRNSUC IS
1949 BEGIN
1950 LINK_PARAMPAGE('HRTRNSUC');
1951 END LINK_HRTRNSUC;
1952
1953 PROCEDURE LINK_HRUTLABH IS
1954 BEGIN
1955 LINK_PARAMPAGE('HRUTLABH');
1956 END LINK_HRUTLABH;
1957
1958 PROCEDURE LINK_HRUTLHRS IS
1959 BEGIN
1960 LINK_PARAMPAGE('HRUTLHRS');
1961 END LINK_HRUTLHRS;
1962
1963 --cbridge, 28/06/2001, pqh budgets report link
1964 PROCEDURE LINK_HRORGPSB IS
1965 BEGIN
1966 LINK_PARAMPAGE('HRORGPSB');
1967 END LINK_HRORGPSB;
1968
1969 --cbridge, 28/06/2001, pqh budgets report link
1970 PROCEDURE LINK_HRMNPPSB IS
1971 BEGIN
1972 LINK_PARAMPAGE('HRMNPPSB');
1973 END LINK_HRMNPPSB;
1974
1975
1976
1977 -- Added to make the parameter page calls (above) look at the database
1978 -- to find their list of values, rather than having them hard coded.
1979 -- This reduces the chances of one place being updated, while the other
1980 -- isn't. - Bug #1349114 - M.J.Andrews - 31-July-2000
1981
1982 PROCEDURE LINK_PARAMPAGE
1983 ( p_report_name IN VarChar2 ) IS
1984
1985 -- Cursor to get the parameter list from the database for the report
1986 cursor c_report_parameters( cp_report VarChar2 )
1987 is
1988 select substrb( web_html_call, 48, length(web_html_call)-49)
1989 from fnd_form_functions
1990 where function_name = 'BIS_' || cp_report;
1991
1992 l_function_params fnd_form_functions.web_html_call%TYPE;
1993
1994 BEGIN
1995
1996 -- IMPORTANT : The call in the database MUST follow the following format
1997 -- to allow it to be read properly by this procedure:
1998 -- HRI_Parameters.Parameter_FormView('HRXXXXXX', '<params>')
1999 -- - HRXXXXXX is the 8 letter report filename,
2000 -- - <params> is the list of parameters seperated by comma's (with a comma at the end)
2001
2002 open c_report_parameters( p_report_name );
2003 fetch c_report_parameters into l_function_params;
2004 if ( c_report_parameters%notfound ) then
2005 raise no_data_found;
2006 end if;
2007 close c_report_parameters;
2008 IF ( l_function_params is not null ) THEN
2009 Parameter_FormView( p_report_name, l_function_params );
2010 END IF;
2011
2012 END LINK_PARAMPAGE;
2013
2014 END HRI_Parameters;