1 PACKAGE BODY PER_PERRPRAS_XMLP_PKG AS
2 /* $Header: PERRPRASB.pls 120.1 2007/12/06 11:30:58 amakrish noship $ */
3 function BeforeReport return boolean is
4 begin
5 P_SESSION_DATE1:=TO_CHAR(P_SESSION_DATE,'DD-MON-YYYY');
6 declare
7
8 CURSOR csr_org_structure_version
9 (p_org_structure_version_id IN NUMBER)
10 IS
11 SELECT business_group_id
12 FROM per_org_structure_versions
13 WHERE org_structure_version_id = p_org_structure_version_id;
14
15 l_business_group_id NUMBER;
16 l_legislation_code VARCHAR2(2);
17 l_primary_flag_desc VARCHAR2(10);
18 l_organization_stucture_desc VARCHAR2(80);
19 l_org_version_desc NUMBER;
20 l_version_start_date DATE;
21 l_version_end_date DATE;
22 l_organization_desc VARCHAR2(240);
23 l_organization_type VARCHAR2(30);
24 begin
25
26
27 --hr_standard.event('BEFORE REPORT');
28
29
30 l_business_group_id := p_business_group_id;
31 IF (p_org_structure_version_id IS NOT NULL) THEN
32 OPEN csr_org_structure_version(p_org_structure_version_id);
33 FETCH csr_org_structure_version INTO l_business_group_id;
34 CLOSE csr_org_structure_version;
35 END IF;
36 IF (l_business_group_id IS NOT NULL) THEN
37 c_business_group_name := hr_reports.get_business_group(l_business_group_id);
38 ELSE
39 c_business_group_name := c_global_hierarchy;
40 END IF;
41
42
43 select legislation_code
44 into l_legislation_code
45 from per_business_groups
46 where business_group_id = p_business_group_id;
47 p_legislation_code := l_legislation_code;
48 if p_org_structure_version_id is not null then
49 hr_reports.get_organization_hierarchy
50 (null
51 ,p_org_structure_version_id
52 ,l_organization_stucture_desc
53 ,l_org_version_desc
54 ,l_version_start_date
55 ,l_version_end_date);
56
57 c_org_structure_desc := l_organization_stucture_desc;
58 c_org_structure_version_desc := l_org_version_desc;
59 c_version_from_desc := l_version_start_date;
60 c_version_to_desc := l_version_end_date;
61 end if;
62
63 if p_org_structure_version_id is null then
64 c_session_date := p_session_date ;
65 elsif
66 p_session_date between c_version_from_desc and
67 nvl(c_version_to_desc,
68 to_date('31/12/4712','DD/MM/YYYY')) then
69 c_session_date := p_session_date;
70 else
71 c_session_date := c_version_from_desc;
72 end if;
73 if p_parent_organization_id is not null then
74 hr_reports.get_organization
75 (p_parent_organization_id
76 ,l_organization_desc
77 ,l_organization_type);
78
79 c_organization_desc := l_organization_desc;
80 end if;
81
82 if p_job_id is not null then
83 c_job_desc :=
84 hr_reports.get_job(p_job_id);
85 p_job_matching :=
86 'and a.job_id = ' || to_char(p_job_id);
87 end if;
88 if p_position_id is not null then
89 c_position_desc :=
90 hr_reports.get_position(p_position_id);
91 p_position_matching :=
92 'and a.position_id = '|| to_char(p_position_id);
93 end if;
94 if p_grade_id is not null then
95 c_grade_desc :=
96 hr_reports.get_grade(p_grade_id);
97 p_grade_matching :=
98 'and a.grade_id = ' || to_char(p_grade_id);
99 end if;
100 if p_payroll_id is not null then
101 c_payroll_desc :=
102 hr_reports.get_payroll_name(p_session_date,p_payroll_id);
103 p_payroll_matching :=
104 'and a.payroll_id+0 = '|| to_char(p_payroll_id);
105 end if;
106 if p_asg_status_type_id1 is not null then
107 c_asg_status_desc1 :=
108 hr_reports.get_status(p_business_group_id
109 ,p_asg_status_type_id1
110 ,p_legislation_code);
111 p_status_matching_1 :=
112 p_status_matching || ',' || to_char(p_asg_status_type_id1) ;
113 c_status_list :=
114 c_status_list || ',' || c_asg_status_desc1;
115 end if;
116 if p_asg_status_type_id2 is not null then
117 c_asg_status_desc2 :=
118 hr_reports.get_status(p_business_group_id
119 ,p_asg_status_type_id2
120 ,p_legislation_code);
121 p_status_matching_1 :=
122 p_status_matching || ',' || to_char(p_asg_status_type_id2);
123 c_status_list :=
124 c_status_list || ',' || c_asg_status_desc2;
125 end if;
126 if p_asg_status_type_id3 is not null then
127 c_asg_status_desc3 :=
128 hr_reports.get_status(p_business_group_id
129 ,p_asg_status_type_id3
130 ,p_legislation_code);
131 p_status_matching_1 :=
132 p_status_matching || ',' || to_char(p_asg_status_type_id3);
133 c_status_list :=
134 c_status_list || ',' || c_asg_status_desc3;
135 end if;
136 if p_asg_status_type_id4 is not null then
137 c_asg_status_desc4 :=
138 hr_reports.get_status(p_business_group_id
139 ,p_asg_status_type_id4
140 ,p_legislation_code);
141 p_status_matching_1 :=
142 p_status_matching || ',' || to_char(p_asg_status_type_id4);
143 c_status_list :=
144 c_status_list || ',' || c_asg_status_desc4;
145 end if;
146 if p_status_matching is not null then
147 p_status_matching_1 :=
148 'and a.assignment_status_type_id in (' ||
149 substr(p_status_matching,2,
150 NVL(length(p_status_matching), 0) - 1) || ')';
151 end if;
152 if c_status_list is not null then
153 c_status_list :=
154 substr(c_status_list,2,NVL(length(c_status_list), 0) - 1);
155 end if;
156 if p_primary_flag is not null then
157 c_primary_flag_desc :=
158 hr_reports.get_lookup_meaning('YES_NO',p_primary_flag);
159 end if;
160
161 if p_person_type is not null then
162 c_person_type_desc :=
163 hr_reports.get_lookup_meaning('EMP_OR_APL',p_person_type);
164 end if;
165
166 if p_people_group_id is not null then
167 HR_REPORTS.gen_partial_matching_lexical
168 (p_people_group_id,
169 p_people_group_flex_id,
170 p_matching_criteria,'GRP');
171 end if;
172
173 if P_ORG_STRUCTURE_VERSION_ID is not null
174 and P_PARENT_ORGANIZATION_ID is not null
175 then
176 P_ORG_MATCHING :=
177 'and a.organization_id in '||
178 '(select organization_id_child '||
179 'from per_org_structure_elements '||
180 'connect by organization_id_parent = '||
181 'prior organization_id_child '||
182 'and org_structure_version_id = prior org_structure_version_id '||
183 'start with organization_id_parent = '||
184 to_char(P_PARENT_ORGANIZATION_ID) ||
185 ' and org_structure_version_id = '||
186 to_char(P_ORG_STRUCTURE_VERSION_ID) ||
187 ' union select ' ||
188 to_char(P_PARENT_ORGANIZATION_ID) ||
189 ' from sys.dual)';
190 elsif
191 P_PARENT_ORGANIZATION_ID is not null then
192 P_ORG_MATCHING :=
193 'and a.organization_id = ' ||
194 to_char(P_PARENT_ORGANIZATION_ID);
195
196
197 else
198 P_ORG_MATCHING :=
199 'and a.business_group_id = ' ||
200 to_char(P_BUSINESS_GROUP_ID);
201
202
203 end if;
204 P_STATUS_MATCHING_1 := 'AND 1=1 ';
205
206 end;
207 return (TRUE);
208 end;
209
210 function c_status_start_dateformula(p_assignment_id in number, p_assignment_status_type_id in number) return date is
211 begin
212
213 declare l_status_start_date DATE;
214 begin
215 begin
216 select max(effective_end_date) + 1
217 into l_status_start_date
218 from per_assignments_f
219 where assignment_id = p_assignment_id
220 and assignment_status_type_id <> p_assignment_status_type_id
221 and effective_end_date < C_SESSION_DATE;
222 exception
223 when no_data_found then null;
224 end;
225 if l_status_start_date is null then
226 begin
227 select min(effective_start_date)
228 into l_status_start_date
229 from per_assignments_f
230 where assignment_id = p_assignment_id
231 and assignment_status_type_id = p_assignment_status_type_id
232 and effective_start_date <= C_SESSION_DATE;
233 exception
234 when no_data_found then null;
235 end;
236 end if;
237 return l_status_start_date;
238 end;
239 RETURN NULL; end;
240
241 function c_status_end_dateformula(p_assignment_id in number, p_assignment_status_type_id in number) return date is
242 begin
243
244 declare l_status_end_date DATE;
245 begin
246 begin
247 select min(effective_start_date) - 1
248 into l_status_end_date
249 from per_assignments_f
250 where assignment_id = p_assignment_id
251 and assignment_status_type_id <> p_assignment_status_type_id
252 and effective_start_date > C_SESSION_DATE;
253 exception
254 when no_data_found then null;
255 end;
256 if l_status_end_date is null then
257 begin
258 select max(effective_end_date)
259 into l_status_end_date
260 from per_assignments_f
261 where assignment_id = p_assignment_id
262 and assignment_status_type_id = p_assignment_status_type_id
263 and effective_end_date >= C_SESSION_DATE;
264 exception
265 when no_data_found then null;
266 end;
267 end if;
268 if l_status_end_date = to_date('31/12/4712','DD/MM/YYYY')
269 then l_status_end_date := '';
270 end if;
271 return l_status_end_date;
272 end;
273 RETURN NULL; end;
274
275 function AfterReport return boolean is
276 begin
277
278 --hr_standard.event('AFTER REPORT');
279
280 return (TRUE);
281 end;
282
283 --Functions to refer Oracle report placeholders--
284
285 Function C_BUSINESS_GROUP_NAME_p return varchar2 is
286 Begin
287 return C_BUSINESS_GROUP_NAME;
288 END;
289 Function C_REPORT_SUBTITLE_p return varchar2 is
290 Begin
291 return C_REPORT_SUBTITLE;
292 END;
293 Function C_PERSON_TYPE_DESC_p return varchar2 is
294 Begin
295 return C_PERSON_TYPE_DESC;
296 END;
297 Function C_JOB_DESC_p return varchar2 is
298 Begin
299 return C_JOB_DESC;
300 END;
301 Function C_POSITION_DESC_p return varchar2 is
302 Begin
303 return C_POSITION_DESC;
304 END;
305 Function C_GRADE_DESC_p return varchar2 is
306 Begin
307 return C_GRADE_DESC;
308 END;
309 Function C_PAYROLL_DESC_p return varchar2 is
310 Begin
311 return C_PAYROLL_DESC;
312 END;
313 Function C_PRIMARY_FLAG_DESC_p return varchar2 is
314 Begin
315 return C_PRIMARY_FLAG_DESC;
316 END;
317 Function C_ASG_STATUS_DESC1_p return varchar2 is
318 Begin
319 return C_ASG_STATUS_DESC1;
320 END;
321 Function C_ASG_STATUS_DESC2_p return varchar2 is
322 Begin
323 return C_ASG_STATUS_DESC2;
324 END;
325 Function C_ASG_STATUS_DESC3_p return varchar2 is
326 Begin
327 return C_ASG_STATUS_DESC3;
328 END;
329 Function C_ASG_STATUS_DESC4_p return varchar2 is
330 Begin
331 return C_ASG_STATUS_DESC4;
332 END;
333 Function C_ORG_STRUCTURE_DESC_p return varchar2 is
334 Begin
335 return C_ORG_STRUCTURE_DESC;
336 END;
337 Function C_ORG_STRUCTURE_VERSION_DESC_p return number is
338 Begin
339 return C_ORG_STRUCTURE_VERSION_DESC;
340 END;
341 Function C_VERSION_FROM_DESC_p return date is
342 Begin
343 return C_VERSION_FROM_DESC;
344 END;
345 Function C_VERSION_TO_DESC_p return date is
346 Begin
347 return C_VERSION_TO_DESC;
348 END;
349 Function C_ORGANIZATION_DESC_p return varchar2 is
350 Begin
351 return C_ORGANIZATION_DESC;
352 END;
353 Function C_STATUS_LIST_p return varchar2 is
354 Begin
355 return C_STATUS_LIST;
356 END;
357 Function C_SESSION_DATE_p return date is
358 Begin
359 return C_SESSION_DATE;
360 END;
361 Function C_GLOBAL_HIERARCHY_p return varchar2 is
362 Begin
363 return C_GLOBAL_HIERARCHY;
364 END;
365 END PER_PERRPRAS_XMLP_PKG ;
[Home] [Help]