1 PACKAGE BODY PER_PERRPREQ_XMLP_PKG AS
2 /* $Header: PERRPREQB.pls 120.1 2007/12/06 11:32:23 amakrish noship $ */
3
4 function BeforeReport return boolean is
5 begin
6
7 declare
8 v_business_group varchar2(240);
9 begin
10
11 --hr_standard.event('BEFORE REPORT');
12
13 insert into fnd_sessions (session_id, effective_date)
14 select userenv('sessionid'), trunc(sysdate)
15 from dual
16 where not exists
17 (select 1
18 from fnd_sessions fs
19 where fs.session_id = userenv('sessionid'));
20 select bg.name
21 INTO v_business_group
22 FROM per_business_groups bg
23 WHERE bg.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID;
24 C_BUSINESS_GROUP := v_business_group;
25 end; return (TRUE);
26 end;
27
28 FUNCTION GET_ORGANIZATION RETURN VARCHAR2 IS
29 L_ORG_NAME varchar(240);
30 BEGIN
31 IF P_ORGANIZATION_ID IS NOT NULL THEN
32 SELECT NAME
33 INTO L_ORG_NAME
34 FROM HR_ORGANIZATION_UNITS
35 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID;
36 END IF;
37 RETURN L_ORG_NAME;
38 END GET_ORGANIZATION;
39
40 FUNCTION GET_JOB RETURN VARCHAR2 IS
41 L_JOB PER_JOBS.NAME%TYPE;
42 BEGIN
43 IF P_JOB_ID IS NOT NULL THEN
44 SELECT NAME
45 INTO L_JOB
46 FROM PER_JOBS_VL
47 WHERE JOB_ID = P_JOB_ID;
48 END IF;
49 RETURN L_JOB;
50 END GET_JOB;
51
52 FUNCTION GET_POSITION RETURN VARCHAR2 IS
53 L_POSITION VARCHAR(240);
54 BEGIN
55 IF P_POSITION_ID IS NOT NULL THEN
56 SELECT NAME
57 INTO L_POSITION
58 FROM PER_POSITIONS
59 WHERE POSITION_ID = P_POSITION_ID;
60 END IF;
61 RETURN L_POSITION;
62 END GET_POSITION;
63
64 FUNCTION GET_GRADE RETURN VARCHAR2 IS
65 L_GRADE VARCHAR(240);
66 BEGIN
67 IF P_GRADE_ID IS NOT NULL THEN
68 SELECT NAME
69 INTO L_GRADE
70 FROM PER_GRADES_VL
71 WHERE GRADE_ID = P_GRADE_ID;
72 END IF;
73 RETURN L_GRADE;
74 END GET_GRADE;
75
76 FUNCTION GET_LOCATION RETURN VARCHAR2 IS
77 L_LOCATION VARCHAR(30);
78 BEGIN
79 IF P_LOCATION_ID IS NOT NULL THEN
80 SELECT LOCATION_CODE
81 INTO L_LOCATION
82 FROM HR_LOCATIONS
83 WHERE LOCATION_ID = P_LOCATION_ID;
84 END IF;
85 RETURN L_LOCATION;
86 END GET_LOCATION;
87
88 FUNCTION GET_RECRUITMENT RETURN VARCHAR2 IS
89 L_RECRUITMENT VARCHAR(30);
90 BEGIN
91 IF P_RECRUITMENT_ACTIVITY_ID IS NOT NULL THEN
92 SELECT NAME
93 INTO L_RECRUITMENT
94 FROM PER_RECRUITMENT_ACTIVITIES
95 WHERE RECRUITMENT_ACTIVITY_ID = P_RECRUITMENT_ACTIVITY_ID;
96 END IF;
97 RETURN L_RECRUITMENT;
98 END GET_RECRUITMENT;
99
100 FUNCTION GET_REQUISITION RETURN VARCHAR2 IS
101 L_REQUISITION VARCHAR(30);
102 BEGIN
103 IF P_REQUISITION_ID IS NOT NULL THEN
104 SELECT NAME
105 INTO L_REQUISITION
106 FROM PER_REQUISITIONS
107 WHERE REQUISITION_ID = P_REQUISITION_ID;
108 END IF;
109 RETURN L_REQUISITION;
110 END GET_REQUISITION;
111
112 FUNCTION GET_APPS_STATUS RETURN VARCHAR2 IS
113 L_APPS_STATUS VARCHAR(80);
114 BEGIN
115 IF P_APPLICANT_STATUS_ID IS NOT NULL THEN
116 SELECT USER_STATUS
117 INTO L_APPS_STATUS
118 FROM PER_ASSIGNMENT_STATUS_TYPES
119 WHERE ASSIGNMENT_STATUS_TYPE_ID = P_APPLICANT_STATUS_ID;
120 END IF;
121 RETURN L_APPS_STATUS;
122 END GET_APPS_STATUS;
123
124 FUNCTION GET_VAC_STATUS RETURN VARCHAR2 IS
125 L_VAC_STATUS VARCHAR(30);
126 BEGIN
127 IF P_VACANCY_STATUS IS NOT NULL THEN
128 SELECT MEANING
129 INTO L_VAC_STATUS
130 FROM hr_lookups
131 WHERE LOOKUP_TYPE = 'VACANCY_STATUS'
132 AND LOOKUP_CODE = P_VACANCY_STATUS;
133 END IF;
134 RETURN L_VAC_STATUS;
135 END GET_VAC_STATUS;
136
137 function AfterReport return boolean is
138 begin
139
140 --hr_standard.event('AFTER REPORT');
141
142 return (TRUE);
143 end;
144
145 --Functions to refer Oracle report placeholders--
146
147 Function C_BUSINESS_GROUP_p return varchar2 is
148 Begin
149 return C_BUSINESS_GROUP;
150 END;
151 END PER_PERRPREQ_XMLP_PKG ;