1 PACKAGE BODY PER_PERRPFP2_XMLP_PKG AS
2 /* $Header: PERRPFP2B.pls 120.2 2008/05/15 10:07:08 amakrish noship $ */
3 function BeforeReport return boolean is
4 l_data_format varchar2(20):='DD-MON-YYYY';
5 begin
6 P_SESSION_DATE1:=TO_CHAR(P_SESSION_DATE,'DD-MON-YYYY');
7 --c_end_of_time := hr_general.end_of_time;
8 c_end_of_time :=to_char(to_date(hr_general.end_of_time,l_data_format),'YYYY-MM-DD');
9 declare
10 v_name varchar2(350);
11 v_title varchar2(240);
12 v_label_expr varchar2(32000);
13 v_column_expr varchar2(32000);
14 v_legislation_code varchar2(30);
15 begin
16
17
18 /* hr_standard.event('BEFORE REPORT'); */
19
20 c_business_group_name :=
21 hr_reports.get_business_group(p_business_group_id);
22
23 select peo.first_name ||
24 decode(peo.first_name,null,null,' ') ||
25 peo.last_name
26 into v_name
27 from per_all_people_f peo
28 where peo.person_id = p_person_id
29 and p_session_date between peo.effective_start_date
30 and peo.effective_end_date;
31 c_header_name := v_name;
32
33
34
35 hr_reports.get_desc_flex_context('PER','PER_ASSIGNMENT_STATUSES',
36 'sst',v_title,v_label_expr,v_column_expr);
37 if v_column_expr is not null then
38 c_app_sec_status_details := v_column_expr;
39 c_app_sec_status_label := v_label_expr;
40 else
41 c_app_sec_status_details := 'sst.attribute1';
42 end if;
43
44
45
46 hr_reports.get_desc_flex_context('PER','PER_EVENTS',
47 'a',v_title,v_label_expr,v_column_expr);
48 if v_column_expr is not null then
49 c_inter_df_details := v_column_expr;
50 c_inter_df_label := v_label_expr;
51 else
52 c_inter_df_details := 'a.attribute1';
53 end if;
54
55
56 hr_reports.get_desc_flex_context('PER','PER_ASSIGNMENTS',
57 'asg',v_title,v_label_expr,v_column_expr);
58 if v_column_expr is not null then
59 c_app_ass_df_details := v_column_expr;
60 c_app_ass_df_label := v_label_expr;
61 else
62 c_app_ass_df_details := 'as'||'g.ass_attribute1';
63 end if;
64
65 hr_reports.get_desc_flex_context('PER','PER_APPLICATIONS',
66 'app',v_title,v_label_expr,v_column_expr);
67 if v_column_expr is not null then
68 c_app_df_details := v_column_expr;
69 c_app_df_label := v_label_expr;
70 else
71 c_app_df_details := 'app.appl_attribute1';
72 end if;
73
74
75 select PBG.LEGISLATION_CODE
76 into v_legislation_code
77 from PER_BUSINESS_GROUPS PBG
78 where PBG.BUSINESS_GROUP_ID = p_business_group_id;
79
80 declare
81 v_id_flex_num number;
82 begin
83 select PBG.COST_ALLOCATION_STRUCTURE
84 into v_id_flex_num
85 from PER_BUSINESS_GROUPS PBG
86 where PBG.BUSINESS_GROUP_ID = p_business_group_id;
87 if v_id_flex_num is not null then
88 c_cost_id_flex_num := v_id_flex_num;
89
90 null;
91 end if;
92 exception
93 when others then null;
94 end;
95 declare
96 v_id_flex_num number;
97 begin
98 select rule_mode
99 into v_id_flex_num
100 from pay_legislation_rules
101 where legislation_code = v_legislation_code
102 and rule_type = 'S'
103 and exists
104 (select null
105 from FND_SEGMENT_ATTRIBUTE_VALUES
106 where ID_FLEX_NUM = rule_mode
107 and APPLICATION_ID = 800
108 and ID_FLEX_CODE = 'SCL'
109 and SEGMENT_ATTRIBUTE_TYPE = 'ASSIGNMENT'
110 and ATTRIBUTE_VALUE = 'Y');
111
112 c_scl_id_flex_num := v_id_flex_num;
113
114 exception
115 when others then null;
116 end;
117
118 end;
119 return (TRUE);
120 end;
121
122 function AfterReport return boolean is
123 begin
124
125 --hr_standard.event('AFTER REPORT');
126
127 return (TRUE);
128 end;
129
130 --Functions to refer Oracle report placeholders--
131
132 Function C_BUSINESS_GROUP_NAME_p return varchar2 is
133 Begin
134 return C_BUSINESS_GROUP_NAME;
135 END;
136 Function C_details_p return varchar2 is
137 Begin
138 return C_details;
139 END;
140 Function C_cont_details_p return varchar2 is
141 Begin
142 return C_cont_details;
143 END;
144 Function C_requirement_desc_p return varchar2 is
145 Begin
146 return C_requirement_desc;
147 END;
148 Function C_requirement_value_p return varchar2 is
149 Begin
150 return C_requirement_value;
151 END;
152 Function C_header_name_p return varchar2 is
153 Begin
154 return C_header_name;
155 END;
156 Function C_pay_meth_count_p return number is
157 Begin
158 return C_pay_meth_count;
159 END;
160 Function C_emp_df_details_p return varchar2 is
161 Begin
162 return C_emp_df_details;
163 END;
164 Function C_emp_leg_df_details_p return varchar2 is
165 Begin
166 return C_emp_leg_df_details;
167 END;
168 Function C_add_df_details_p return varchar2 is
169 Begin
170 return C_add_df_details;
171 END;
172 Function C_cont_df_details_p return varchar2 is
173 Begin
174 return C_cont_df_details;
175 END;
176 Function C_app_df_details_p return varchar2 is
177 Begin
178 return C_app_df_details;
179 END;
180 Function c_app_ass_df_details_p return varchar2 is
181 Begin
182 return c_app_ass_df_details;
183 END;
184 Function C_sec_ass_df_details_p return varchar2 is
185 Begin
186 return C_sec_ass_df_details;
187 END;
188 Function C_per_serv_df_details_p return varchar2 is
189 Begin
190 return C_per_serv_df_details;
191 END;
192 Function C_temp_p return varchar2 is
193 Begin
194 return C_temp;
195 END;
196 Function C_add_df_label_p return varchar2 is
197 Begin
198 return C_add_df_label;
199 END;
200 Function C_emp_df_label_p return varchar2 is
201 Begin
202 return C_emp_df_label;
203 END;
204 Function C_emp_leg_df_label_p return varchar2 is
205 Begin
206 return C_emp_leg_df_label;
207 END;
208 Function C_cont_df_label_p return varchar2 is
209 Begin
210 return C_cont_df_label;
211 END;
212 Function C_app_df_label_p return varchar2 is
213 Begin
214 return C_app_df_label;
215 END;
216 Function C_app_ass_df_label_p return varchar2 is
217 Begin
218 return C_app_ass_df_label;
219 END;
220 Function C_app_sec_status_details_p return varchar2 is
221 Begin
222 return C_app_sec_status_details;
223 END;
224 Function C_app_sec_status_label_p return varchar2 is
225 Begin
226 return C_app_sec_status_label;
227 END;
228 Function C_inter_df_details_p return varchar2 is
229 Begin
230 return C_inter_df_details;
231 END;
232 Function C_inter_df_label_p return varchar2 is
233 Begin
234 return C_inter_df_label;
235 END;
236 Function C_sec_ass_df_label_p return varchar2 is
237 Begin
238 return C_sec_ass_df_label;
239 END;
240 Function C_ass_df_details_p return varchar2 is
241 Begin
242 return C_ass_df_details;
243 END;
244 Function C_ass_df_label_p return varchar2 is
245 Begin
246 return C_ass_df_label;
247 END;
248 Function C_fur_info_df_details_p return varchar2 is
249 Begin
250 return C_fur_info_df_details;
251 END;
252 Function C_fur_info_df_label_p return varchar2 is
253 Begin
254 return C_fur_info_df_label;
255 END;
256 Function C_fur_info_ddf_details_p return varchar2 is
257 Begin
258 return C_fur_info_ddf_details;
259 END;
260 Function C_fur_info_ddf_label_p return varchar2 is
261 Begin
262 return C_fur_info_ddf_label;
263 END;
264 Function C_cost_id_flex_num_p return number is
265 Begin
266 return C_cost_id_flex_num;
267 END;
268 Function C_scl_id_flex_num_p return number is
269 Begin
270 return C_scl_id_flex_num;
271 END;
272 Function C_scl_desc_p return varchar2 is
273 Begin
274 return C_scl_desc;
275 END;
276 Function C_scl_value_p return varchar2 is
277 Begin
278 return C_scl_value;
279 END;
280 Function C_cost_desc_p return varchar2 is
281 Begin
282 return C_cost_desc;
283 END;
284 Function C_cost_values_p return varchar2 is
285 Begin
286 return C_cost_values;
287 END;
288 Function C_ppm_df_details_p return varchar2 is
289 Begin
290 return C_ppm_df_details;
291 END;
292 Function C_ppm_df_label_p return varchar2 is
293 Begin
294 return C_ppm_df_label;
295 END;
296 Function C_ext_act_desc_p return varchar2 is
297 Begin
298 return C_ext_act_desc;
299 END;
300 Function C_ext_act_values_p return varchar2 is
301 Begin
302 return C_ext_act_values;
303 END;
304 Function C_ele_df_details_p return varchar2 is
305 Begin
306 return C_ele_df_details;
307 END;
308 Function C_ele_df_label_p return varchar2 is
309 Begin
310 return C_ele_df_label;
311 END;
312 Function C_ele_cost_desc_p return varchar2 is
313 Begin
314 return C_ele_cost_desc;
315 END;
316 Function C_ele_cost_values_p return varchar2 is
317 Begin
318 return C_ele_cost_values;
319 END;
320 Function C_event_df_details_p return varchar2 is
321 Begin
322 return C_event_df_details;
323 END;
324 Function C_event_df_label_p return varchar2 is
325 Begin
326 return C_event_df_label;
327 END;
328 Function C_book_df_details_p return varchar2 is
329 Begin
330 return C_book_df_details;
331 END;
332 Function C_book_df_label_p return varchar2 is
333 Begin
334 return C_book_df_label;
335 END;
336 Function C_special_df_details_p return varchar2 is
337 Begin
338 return C_special_df_details;
339 END;
340 Function C_special_df_label_p return varchar2 is
341 Begin
342 return C_special_df_label;
343 END;
344 Function C_per_serv_df_label_p return varchar2 is
345 Begin
346 return C_per_serv_df_label;
347 END;
348 Function C_absence_df_details_p return varchar2 is
349 Begin
350 return C_absence_df_details;
351 END;
352 Function C_absence_df_label_p return varchar2 is
353 Begin
354 return C_absence_df_label;
355 END;
356 Function C_subtitle_p return varchar2 is
357 Begin
358 return C_subtitle;
359 END;
360 Function C_END_OF_TIME_p return varchar2 is
361 Begin
362 return C_END_OF_TIME;
363 END;
364 function M_4FormatTrigger return number is
365 v_detailcount number(10);
366 begin
367 select count(*)
368 into v_detailcount
369 from per_secondary_ass_statuses ast,
370 per_all_assignments_f pa
371 where ast.assignment_id = pa.assignment_id
372 and pa.person_id = p_person_id
373 and pa.business_group_id = p_business_group_id;
374
375 return v_detailcount;
376 end;
377
378 function M_5FormatTrigger return number is
379 v_detailcount number(10);
380 begin
381 select count(*)
382 into v_detailcount
383 from per_events pe,
384 per_all_assignments_f pa
385 where pe.event_or_interview = 'I'
386 and pa.person_id = p_person_id
387 and pa.business_group_id = p_business_group_id
388 and pa.assignment_id = pe.assignment_id;
389
390 return v_detailcount;
391 end;
392 END PER_PERRPFP2_XMLP_PKG ;