1 package body pqp_gb_swf_archive as
2 /* $Header: pqpgbswfar.pkb 120.8 2011/09/09 10:49:19 abdash noship $ */
3
4 -- Global variables
5 g_census_year number;
6 g_census_day date;
7 g_cont_data_st_date date;
8 g_cont_data_end_date date;
9 g_lea_number number;
10 g_data_ret_type varchar2(10);
11 g_estb_number number;
12 g_exclude_absence varchar2(3);
13 g_exclude_qual varchar2(3);
14 g_business_group_id number;
15 g_debug boolean;
16
17 --
18 -- Set to 'Y' if PQP_GB_SWF_CONTRACT_TYPE = ASG_CAT, dates used from per_all_assignments_f
19 -- Set to 'N' if PQP_GB_SWF_CONTRACT_TYPE = , dates used from pqp_assignment_attributes_f
20 g_pick_from_asg varchar2(10);
21 --
22 g_teacher_sql_str varchar2(3000);
23 g_teach_dff_name varchar2(30);
24 g_qts_sql_str varchar2(3000);
25 g_qts_route_sql_str varchar2(3000);
26 g_hlta_dff_name varchar2(30);
27 g_hlta_sql_str varchar2(3000);
28 g_cont_post_sql_str varchar2(3000);
29 g_cont_post_src varchar2(30);
30 g_origin_sql_str varchar2(3000);
31 g_origin_dff varchar2(30);
32 g_destination_dff varchar2(30);
33 g_destination_sql_str varchar2(3000);
34 g_role_src varchar2(30);
35 g_role_sql_str varchar2(3000);
36 g_addl_role_src varchar2(30);
37 g_addl_role_sql_str varchar2(3000);
38
39 -- person level globals
40 -- these should be destroyed before they pass to the new thread
41
42 type abs_details_rec is record
43 (person_id number(20)
44 ,date_start date
45 ,date_start_dcsf varchar2(10)
46 ,date_end date
47 ,date_end_dcsf varchar2(10)
48 ,days_lost varchar2(6)
49 ,absence_category varchar2(50)
50 ,estab_no number
51 );
52
53 type abs_details_tab is table of
54 abs_details_rec index by binary_integer;
55
56 type qual_details_rec is record
57 (person_id number
58 ,qual_code varchar2(10)
59 ,sub1 varchar2(10)
60 ,sub2 varchar2(10)
61 ,verified varchar2(10)
62 ,estab_no number
63 );
64
65 type qual_details_tab is table of
66 qual_details_rec index by binary_integer;
67
68 type addl_payment_dtl_rec is record
69 ( addl_payment_cat varchar2(15)
70 ,addl_payment_amt number(10)
71 );
72
73 type addl_payment_dtl_tab is table of
74 addl_payment_dtl_rec index by binary_integer;
75
76 type addl_role_tab is table of
77 hr_lookups.meaning%type index by binary_integer;
78
79 type act_info_rec is record
80 ( assignment_id number(20)
81 ,person_id number(20)
82 ,effective_date date
83 ,action_info_category varchar2(50)
84 ,act_info1 varchar2(300)
85 ,act_info2 varchar2(300)
86 ,act_info3 varchar2(300)
87 ,act_info4 varchar2(300)
88 ,act_info5 varchar2(300)
89 ,act_info6 varchar2(300)
90 ,act_info7 varchar2(300)
91 ,act_info8 varchar2(300)
92 ,act_info9 varchar2(300)
93 ,act_info10 varchar2(300)
94 ,act_info11 varchar2(300)
95 ,act_info12 varchar2(300)
96 ,act_info13 varchar2(300)
97 ,act_info14 varchar2(300)
98 ,act_info15 varchar2(300)
99 ,act_info16 varchar2(300)
100 ,act_info17 varchar2(300)
101 ,act_info18 varchar2(300)
102 ,act_info19 varchar2(300)
103 ,act_info20 varchar2(300)
104 ,act_info21 varchar2(300)
105 ,act_info22 varchar2(300)
106 ,act_info23 varchar2(300)
107 ,act_info24 varchar2(300)
108 ,act_info25 varchar2(300)
109 ,act_info26 varchar2(300)
110 ,act_info27 varchar2(300)
111 ,act_info28 varchar2(300)
112 ,act_info29 varchar2(300)
113 ,act_info30 varchar2(300)
114 );
115 type action_info_table is table of
116 act_info_rec index by binary_integer;
117
118 g_package constant varchar2(20):= 'pqp_gb_swf_archive.';
119
120 -------------------------------Procedure insert_archive_row ---------------------------
121 -- Inserts rows to be archived to pay_action_information table with the context specified
122
123 procedure insert_archive_row(p_assactid in number,
124 p_effective_date in date,
125 p_tab_rec_data in action_info_table) is
126 l_proc constant varchar2(50):= g_package||'insert_archive_row';
127 l_ovn number;
128 l_action_id number;
129 begin
130 hr_utility.set_location('Entering: '||l_proc,1);
131 if p_tab_rec_data.count > 0 then
132 for i in p_tab_rec_data.first .. p_tab_rec_data.last loop
133
134 hr_utility.trace('Defining category '|| p_tab_rec_data(i).action_info_category);
135 hr_utility.trace('action_context_id = '|| p_assactid);
136 hr_utility.trace('p_tab_rec_data(i).action_info_category = '|| p_tab_rec_data(i).action_info_category);
137 if p_tab_rec_data(i).action_info_category is not null then
138 pay_action_information_api.create_action_information(
139 p_action_information_id => l_action_id,
140 p_object_version_number => l_ovn,
141 p_action_information_category => p_tab_rec_data(i).action_info_category,
142 p_action_context_id => p_assactid,
143 p_action_context_type => 'AAP',
144 p_assignment_id => p_tab_rec_data(i).assignment_id,
145 p_effective_date => p_effective_date,
146 p_action_information1 => p_tab_rec_data(i).act_info1,
147 p_action_information2 => p_tab_rec_data(i).act_info2,
148 p_action_information3 => p_tab_rec_data(i).act_info3,
149 p_action_information4 => p_tab_rec_data(i).act_info4,
150 p_action_information5 => p_tab_rec_data(i).act_info5,
151 p_action_information6 => p_tab_rec_data(i).act_info6,
152 p_action_information7 => p_tab_rec_data(i).act_info7,
153 p_action_information8 => p_tab_rec_data(i).act_info8,
154 p_action_information9 => p_tab_rec_data(i).act_info9,
155 p_action_information10 => p_tab_rec_data(i).act_info10,
156 p_action_information11 => p_tab_rec_data(i).act_info11,
157 p_action_information12 => p_tab_rec_data(i).act_info12,
158 p_action_information13 => p_tab_rec_data(i).act_info13,
159 p_action_information14 => p_tab_rec_data(i).act_info14,
160 p_action_information15 => p_tab_rec_data(i).act_info15,
161 p_action_information16 => p_tab_rec_data(i).act_info16,
162 p_action_information17 => p_tab_rec_data(i).act_info17,
163 p_action_information18 => p_tab_rec_data(i).act_info18,
164 p_action_information19 => p_tab_rec_data(i).act_info19,
165 p_action_information20 => p_tab_rec_data(i).act_info20,
166 p_action_information21 => p_tab_rec_data(i).act_info21,
167 p_action_information22 => p_tab_rec_data(i).act_info22,
168 p_action_information23 => p_tab_rec_data(i).act_info23,
169 p_action_information24 => p_tab_rec_data(i).act_info24,
170 p_action_information25 => p_tab_rec_data(i).act_info25,
171 p_action_information26 => p_tab_rec_data(i).act_info26,
172 p_action_information27 => p_tab_rec_data(i).act_info27,
173 p_action_information28 => p_tab_rec_data(i).act_info28,
174 p_action_information29 => p_tab_rec_data(i).act_info29,
175 p_action_information30 => p_tab_rec_data(i).act_info30
176 );
177 end if;
178 end loop;
179 end if;
180 hr_utility.set_location('Leaving: '||l_proc,999);
181 end insert_archive_row;
182
183
184 -------------------------------Procedure dyn_sql --------------------------------------
185 procedure dyn_sql
186 is
187 l_proc constant varchar2(50) := g_package || ' dyn_sql';
188 l_exp exception;
189
190 cursor get_context_values is
191 select pcv_information1 ,
192 pcv_information3 ,
193 pcv_information4
194 from pqp_configuration_values pcv
195 where pcv.pcv_information_category = 'PQP_GB_SWF_TEACHER_NUM'
196 and pcv.business_group_id = g_business_group_id;
197
198 cursor get_qts_source is
199 select pcv_information1
200 from pqp_configuration_values pcv
201 where pcv.pcv_information_category = 'PQP_GB_SWF_QTS_SRC'
202 and pcv.business_group_id = g_business_group_id;
203
204 cursor get_qts_route_source is
205 select pcv_information1,
206 pcv_information2
207 from pqp_configuration_values pcv
208 where pcv.pcv_information_category = 'PQP_GB_SWF_QTS_ROUTE_SRC'
209 and pcv.business_group_id = g_business_group_id;
210
211 cursor get_hlta_source is
212 select pcv_information1,
213 pcv_information4,
214 decode(pcv_information1,'JOB',pcv_information3, pcv_information5)
215 from pqp_configuration_values pcv
216 where pcv.pcv_information_category = 'PQP_GB_SWF_HLTA_STATUS_SRC'
217 and pcv.business_group_id = g_business_group_id;
218
219 cursor get_post_source is
220 select pcv_information1,
221 pcv_information3
222 from pqp_configuration_values pcv
223 where pcv.pcv_information_category = 'PQP_GB_SWF_POST_SOURCE'
224 and pcv.business_group_id = g_business_group_id;
225
226 cursor get_origin_source is
227 select pcv_information1,
228 pcv_information2,
229 pcv_information3
230 from pqp_configuration_values pcv
231 where pcv.pcv_information_category = 'PQP_GB_SWF_ORIGIN_SRC'
232 and pcv.business_group_id = g_business_group_id;
233
234 cursor get_destination_source is
235 select pcv_information1,
236 pcv_information2,
237 pcv_information3
238 from pqp_configuration_values pcv
239 where pcv.pcv_information_category = 'PQP_GB_SWF_DESTINATION_SRC' -- Check in database
240 and pcv.business_group_id = g_business_group_id;
241
242 cursor get_role_source is
243 select pcv_information1,
244 pcv_information3
245 from pqp_configuration_values pcv
246 where pcv.pcv_information_category = 'PQP_GB_SWF_ROLE_SOURCE'
247 and pcv.business_group_id = g_business_group_id;
248
249 cursor get_addl_role_source is
250 select pcv_information1,
251 pcv_information2,
252 pcv_information3
253 from pqp_configuration_values pcv
254 where pcv.pcv_information_category = 'PQP_GB_SWF_ADD_ROLE_SOURCE'
255 and pcv.business_group_id = g_business_group_id;
256
257 ---
258 l_context varchar2(150);
259 l_column varchar2(30);
260 sql_str varchar2(1000);
261 l_qts_grade_seg_name varchar2(30);
262 l_qts_route_dff_name varchar2(30);
263 l_qts_route_seg_name varchar2(30);
264 l_hlta_seg_name varchar2(30);
265 l_hlta_context_name varchar2(30);
266 l_cont_post_seg varchar2(30);
267 l_origin_context varchar2(30);
268 l_origin_segment varchar2(30);
269 l_destination_context varchar2(30);
270 l_destination_segment varchar2(30);
271 l_role_segment varchar2(30);
272 l_addl_role_context varchar2(30);
273 l_addl_role_segment varchar2(30);
274
275
276
277 begin
278 hr_utility.set_location('Entering '|| l_proc, 10);
279
280
281 hr_utility.set_location('Teachers Number : building teachers number start ', 20);
282
283 open get_context_values;
284 fetch get_context_values into g_teach_dff_name,l_context,l_column;
285 close get_context_values;
286
287 if g_teach_dff_name is null then
288 fnd_file.put_line(fnd_file.log,'Staff Details - Teachers Number ');
289 end if;
290
291 hr_utility.set_location('Teachers Number DFF name :'||g_teach_dff_name,20);
292 hr_utility.set_location('Teachers Number Context name :'||l_context,30);
293 hr_utility.set_location('Teachers Number Column name :'||l_column,40);
294
295
296
297
298 if g_teach_dff_name = 'PER_PEOPLE' then
299 g_teacher_sql_str :=
300 'select '||l_column||
301 ' from per_all_people_f where ATTRIBUTE_CATEGORY = '''||l_context||'''
302 and person_id = :person_id
303 and :effective_date between effective_start_date and effective_end_date';
304
305 elsif g_teach_dff_name = 'Extra Person Info DDF' then
306 g_teacher_sql_str := 'select max('||l_column||')'||
307 ' from per_people_extra_info where information_type = '''||l_context||'''
308 and person_id = :person_id
309 and '||l_column ||' is not null ';
310
311 end if;
312
313
314 hr_utility.set_location('Teachers Number SQL Str :'||g_teacher_sql_str,60);
315 hr_utility.set_location('Teachers Number : building teachers number End ',70);
316 hr_utility.set_location('QT status : building QT status start ',80);
317
318
319 open get_qts_source;
320 fetch get_qts_source into l_qts_grade_seg_name;
321 close get_qts_source;
322
323 if l_qts_grade_seg_name is not null then
324 g_qts_sql_str:=
325 'select max(hr_general.decode_lookup(''YES_NO'',pcv.pcv_information4))
326 from per_all_assignments_f paf,
327 per_grades pgr,
328 per_grade_definitions pgd ,
329 pqp_configuration_values pcv
330 where paf.business_group_id + 0 = :bg_id
331 and paf.business_group_id = pgr.business_group_id
332 and pcv.business_group_id = paf.business_group_id
333 and pgr.grade_definition_id = pgd.grade_definition_id
334 and paf.grade_id = pgr.grade_id
335 and :eff_date between paf.effective_start_date and paf.effective_end_date
336 and person_id = :person_id
337 and pcv.pcv_information_category = ''PQP_GB_SWF_QTS_MAPPING''
338 and ((pcv_information3 is null
339 and pgd.'||l_qts_grade_seg_name||' = pcv.pcv_information2 )
340 or (pcv_information3 is not null
341 and pgd.'||l_qts_grade_seg_name||' between pcv.pcv_information2 and pcv_information3))';
342 end if;
343
344
345 hr_utility.set_location('QTS SQL Str :'||g_teacher_sql_str,90);
346 hr_utility.set_location('QT status : building QT status end ',100);
347
348
349 open get_qts_route_source;
350 fetch get_qts_route_source into l_qts_route_dff_name,l_qts_route_seg_name;
351 close get_qts_route_source;
352
353 if l_qts_route_dff_name = 'GRD' then
354 g_qts_route_sql_str:=
355 'select max(pcv.pcv_information4)
356 from per_all_assignments_f paf,
357 per_grades pgr,
358 per_grade_definitions pgd ,
359 pqp_configuration_values pcv
360 where paf.business_group_id + 0 = :bg_id
361 and paf.business_group_id = pgr.business_group_id
362 and pcv.business_group_id = paf.business_group_id
363 and pgr.grade_definition_id = pgd.grade_definition_id
364 and paf.grade_id = pgr.grade_id
365 and :eff_date between paf.effective_start_date and paf.effective_end_date
366 and person_id = :person_id
367 and pcv.pcv_information_category = ''PQP_GB_SWF_QTS_ROUTE_MAPPING''
368 and ((pcv_information3 is null
369 and pgd.'||l_qts_route_seg_name||' = pcv.pcv_information2 )
370 or (pcv_information3 is not null
371 and pgd.'||l_qts_route_seg_name||' between pcv.pcv_information2 and pcv_information3))';
372 elsif l_qts_route_dff_name = 'JOB' then
373 g_qts_route_sql_str:=
374 'select max(pcv.pcv_information4) QT_status
375 from per_all_assignments_f paf,
376 per_jobs job,
377 per_job_definitions jobdef ,
378 pqp_configuration_values pcv
379 where paf.business_group_id + 0 = :bg_id
380 and paf.business_group_id = job.business_group_id
381 and pcv.business_group_id = paf.business_group_id
382 and job.job_definition_id = jobdef.job_definition_id
383 and paf.job_id = job.job_id
384 and :eff_date between paf.effective_start_date and paf.effective_end_date
385 and person_id = :person_id
386 and pcv.pcv_information_category = ''PQP_GB_SWF_QTS_ROUTE_MAPPING''
387 and jobdef.'||l_qts_route_seg_name||' = pcv.pcv_information2';
388 elsif l_qts_route_dff_name = 'POS' then
389 g_qts_route_sql_str:=
390 'select max(pcv.pcv_information4) QT_status
391 from per_all_assignments_f paf,
392 per_positions pos,
393 per_position_definitions posdef ,
394 pqp_configuration_values pcv
395 where paf.business_group_id + 0 = :bg_id
396 and paf.business_group_id = pos.business_group_id
397 and pcv.business_group_id = paf.business_group_id
398 and pos.position_definition_id = posdef.position_definition_id
399 and paf.position_id = pos.position_id
400 and :eff_date between paf.effective_start_date and paf.effective_end_date
401 and person_id = :person_id
402 and pcv.pcv_information_category = ''PQP_GB_SWF_QTS_ROUTE_MAPPING''
403 and posdef.'||l_qts_route_seg_name||' = pcv.PCV_INFORMATION2';
404 end if;
405
406 open get_hlta_source;
407 fetch get_hlta_source into g_hlta_dff_name,l_hlta_context_name,l_hlta_seg_name;
408 close get_hlta_source;
409
410 if g_hlta_dff_name = 'JOB' then
411 g_hlta_sql_str:=
412 'select max(hr_general.decode_lookup(''YES_NO'',pcv.pcv_information3))
413 from per_all_assignments_f paf,
414 per_jobs job,
415 per_job_definitions jobdef ,
416 pqp_configuration_values pcv
417 where paf.business_group_id + 0 = :bg_id
418 and paf.business_group_id = job.business_group_id
419 and pcv.business_group_id = paf.business_group_id
420 and job.job_definition_id = jobdef.job_definition_id
421 and paf.job_id = job.job_id
422 and :eff_date between paf.effective_start_date and paf.effective_end_date
423 and person_id = :person_id
424 and pcv.pcv_information_category = ''PQP_GB_SWF_HLTA_STATUS_MAPPING''
425 and jobdef.'||l_hlta_seg_name||' = pcv.pcv_information2';
426
427 elsif g_hlta_dff_name = 'PER_PEOPLE' then
428 g_hlta_sql_str:=
429 'select max(hr_general.decode_lookup(''YES_NO'',pcv.pcv_information3))
430 from per_all_people_f pap,
431 pqp_configuration_values pcv
432 where attribute_category = '''||l_hlta_context_name||'''
433 and pap.person_id = :person_id
434 and :effective_date between pap.effective_start_date and pap.effective_end_date
435 and pcv.business_group_id = pap.business_group_id
436 and pcv.pcv_information_category = ''PQP_GB_SWF_HLTA_STATUS_MAPPING''
437 and pap.'||l_hlta_seg_name||' = pcv.pcv_information2';
438 elsif g_hlta_dff_name = 'PER_ASSIGNMENTS' then
439 g_hlta_sql_str:=
440 'select max(hr_general.decode_lookup(''YES_NO'',pcv.pcv_information3))
441 from per_all_assignments_f paf,
442 pqp_configuration_values pcv
443 where ass_attribute_category = '''||l_hlta_context_name||'''
444 and paf.person_id = :person_id
445 and :effective_date between paf.effective_start_date and paf.effective_end_date
446 and pcv.business_group_id = paf.business_group_id
447 and pcv.pcv_information_category = ''PQP_GB_SWF_HLTA_STATUS_MAPPING''
448 and pap.'||l_hlta_seg_name||' = pcv.pcv_information2';
449 end if;
450
451 open get_post_source;
452 fetch get_post_source into g_cont_post_src,l_cont_post_seg;
453 close get_post_source;
454
455 if g_cont_post_src = 'GRD' then
456 g_cont_post_sql_str:=
457 'select pcv.pcv_information4
458 from per_all_assignments_f paf,
459 per_grades pgr,
460 per_grade_definitions pgd ,
461 pqp_configuration_values pcv
462 where paf.business_group_id + 0 = :bg_id
463 and paf.business_group_id = pgr.business_group_id
464 and pcv.business_group_id = paf.business_group_id
465 and pgr.grade_definition_id = pgd.grade_definition_id
466 and paf.grade_id = pgr.grade_id
467 and :eff_date between paf.effective_start_date and paf.effective_end_date
468 and paf.assignment_id = :assignment_id
469 and pcv.pcv_information_category = ''PQP_GB_SWF_POST_MAPPING''
470 and ((pcv_information3 is null
471 and pgd.'||l_cont_post_seg||' = pcv.pcv_information2 )
472 or (pcv_information3 is not null
473 and pgd.'||l_cont_post_seg||' between pcv.pcv_information2 and pcv_information3))';
474 elsif g_cont_post_src = 'JOB' then
475 g_cont_post_sql_str:=
476 'select pcv.pcv_information4
477 from per_all_assignments_f paf,
478 per_jobs job,
479 per_job_definitions jobdef ,
480 pqp_configuration_values pcv
481 where paf.business_group_id + 0 = :bg_id
482 and paf.business_group_id = job.business_group_id
483 and pcv.business_group_id = paf.business_group_id
484 and job.job_definition_id = jobdef.job_definition_id
485 and paf.job_id = job.job_id
486 and :eff_date between paf.effective_start_date and paf.effective_end_date
487 and paf.assignment_id = :assignment_id
488 and pcv.pcv_information_category = ''PQP_GB_SWF_POST_MAPPING''
489 and jobdef.'||l_cont_post_seg||' = pcv.pcv_information2';
490 elsif g_cont_post_src = 'POS' then
491 g_cont_post_sql_str:=
492 'select pcv.pcv_information4
493 from per_all_assignments_f paf,
494 per_positions pos,
495 per_position_definitions posdef ,
496 pqp_configuration_values pcv
497 where paf.business_group_id + 0 = :bg_id
498 and paf.business_group_id = pos.business_group_id
499 and pcv.business_group_id = paf.business_group_id
500 and pos.position_definition_id = posdef.position_definition_id
501 and paf.position_id = pos.position_id
502 and :eff_date between paf.effective_start_date and paf.effective_end_date
503 and paf.assignment_id = :assignment_id
504 and pcv.pcv_information_category = ''PQP_GB_SWF_POST_MAPPING''
505 and posdef.'||l_cont_post_seg||' = pcv.pcv_information2';
506 end if;
507
508 open get_origin_source;
509 fetch get_origin_source into g_origin_dff,l_origin_context,l_origin_segment;
510 close get_origin_source;
511
512 if g_origin_dff = 'PER_ASSIGNMENTS' then
513 g_origin_sql_str:=
514 'select '||l_origin_segment||'
515 from per_all_assignments_f where ass_attribute_category = '''||l_origin_context||'''
516 and assignment_id = :assignment_id
517 and :effective_date between effective_start_date and effective_end_date';
518 elsif g_origin_dff = 'PER_PEOPLE' then
519 g_origin_sql_str:=
520 'select '||l_origin_segment||
521 ' from per_all_people_f where ATTRIBUTE_CATEGORY = '''||l_origin_context||'''
522 and person_id = :person_id
523 and :effective_date between effective_start_date and effective_end_date';
524 end if;
525
526 open get_destination_source;
527 fetch get_destination_source into g_destination_dff,l_destination_context,l_destination_segment;
528 close get_destination_source;
529
530 if g_destination_dff = 'PER_ASSIGNMENTS' then
531 g_destination_sql_str:=
532 'select '||l_destination_segment||'
533 from per_all_assignments_f where ass_attribute_category = '''||l_destination_context||'''
534 and assignment_id = :assignment_id
535 and :effective_date between effective_start_date and effective_end_date';
536 elsif g_destination_dff = 'PER_PEOPLE' then
537 g_destination_sql_str:=
538 'select '||l_destination_segment||
539 ' from per_periods_of_service where ATTRIBUTE_CATEGORY = '''||l_destination_context||'''
540 and person_id = :person_id';
541 end if;
542
543 open get_role_source;
544 fetch get_role_source into g_role_src,l_role_segment;
545 close get_role_source;
546
547 if g_role_src = 'GRD' then
548 g_role_sql_str:=
549 'select pcv.pcv_information4
550 from per_all_assignments_f paf,
551 per_grades pgr,
552 per_grade_definitions pgd ,
553 pqp_configuration_values pcv
554 where paf.business_group_id + 0 = :bg_id
555 and paf.business_group_id = pgr.business_group_id
556 and pcv.business_group_id = paf.business_group_id
557 and pgr.grade_definition_id = pgd.grade_definition_id
558 and paf.grade_id = pgr.grade_id
559 and :eff_date between paf.effective_start_date and paf.effective_end_date
560 and paf.assignment_id = :assignment_id
561 and pcv.pcv_information_category = ''PQP_GB_SWF_ROLE_MAPPING''
562 and ((pcv_information3 is null
563 and pgd.'||l_role_segment||' = pcv.pcv_information2 )
564 or (pcv_information3 is not null
565 and pgd.'||l_role_segment||' between pcv.pcv_information2 and pcv_information3))';
566 elsif g_role_src = 'JOB' then
567 g_role_sql_str:=
568 'select pcv.pcv_information4
569 from per_all_assignments_f paf,
570 per_jobs job,
571 per_job_definitions jobdef ,
572 pqp_configuration_values pcv
573 where paf.business_group_id + 0 = :bg_id
574 and paf.business_group_id = job.business_group_id
575 and pcv.business_group_id = paf.business_group_id
576 and job.job_definition_id = jobdef.job_definition_id
577 and paf.job_id = job.job_id
578 and :eff_date between paf.effective_start_date and paf.effective_end_date
579 and paf.assignment_id = :assignment_id
580 and pcv.pcv_information_category = ''PQP_GB_SWF_ROLE_MAPPING''
581 and jobdef.'||l_role_segment||' = pcv.pcv_information2';
582 elsif g_role_src = 'POS' then
583 g_role_sql_str:=
584 'select pcv.pcv_information4
585 from per_all_assignments_f paf,
586 per_positions pos,
587 per_position_definitions posdef ,
588 pqp_configuration_values pcv
589 where paf.business_group_id + 0 = :bg_id
590 and paf.business_group_id = pos.business_group_id
591 and pcv.business_group_id = paf.business_group_id
592 and pos.position_definition_id = posdef.position_definition_id
593 and paf.position_id = pos.position_id
594 and :eff_date between paf.effective_start_date and paf.effective_end_date
595 and paf.assignment_id = :assignment_id
596 and pcv.pcv_information_category = ''PQP_GB_SWF_ROLE_MAPPING''
597 and posdef.'||l_role_segment||' = pcv.pcv_information2';
598 end if;
599
600 open get_addl_role_source;
601 fetch get_addl_role_source into g_addl_role_src,l_addl_role_context,l_addl_role_segment;
602 close get_addl_role_source;
603
604 if g_addl_role_src = 'Extra Position Info DDF' then
605 g_addl_role_sql_str:=
606 'select pcv.pcv_information4
607 from per_all_assignments_f paa,
608 per_position_extra_info pei,
609 pqp_configuration_values pcv
610 where paa.assignment_id = :p_assignment_id
611 and pei.position_id = paa.position_id
612 and pei.information_type = '''||l_addl_role_context||'''
613 and pcv.pcv_information_category = ''PQP_GB_SWF_ROLE_MAPPING''
614 and paa.business_group_id = pcv.business_group_id
615 and pei.'||l_addl_role_segment||' = pcv.pcv_information2
616 and :effective_date between paa.effective_start_date and paa.effective_end_date';
617 elsif g_addl_role_src = 'Extra Job Info DDF' then
618 g_addl_role_sql_str:=
619 'select pcv.pcv_information4
620 from per_all_assignments_f paa,
621 per_job_extra_info jei,
622 pqp_configuration_values pcv
623 where paa.assignment_id = :p_assignment_id
624 and jei.job_id = paa.job_id
625 and jei.information_type = '''||l_addl_role_context||'''
626 and pcv.pcv_information_category = ''PQP_GB_SWF_ROLE_MAPPING''
627 and paa.business_group_id = pcv.business_group_id
628 and jei.'||l_addl_role_segment||' = pcv.pcv_information2
629 and :effective_date between paa.effective_start_date and paa.effective_end_date';
630 elsif g_addl_role_src = 'Assignment Developer DF' then
631 g_addl_role_sql_str:=
632 'select pcv.pcv_information4
633 from per_assignment_extra_info aei,
634 pqp_configuration_values pcv
635 where aei.assignment_id = :p_assignment_id
636 and aei.information_type = '''||l_addl_role_context||'''
637 and pcv.pcv_information_category = ''PQP_GB_SWF_ROLE_MAPPING''
638 and pcv.business_group_id = :bg_id
639 and aei.'||l_addl_role_segment||' = pcv.pcv_information2';
640 end if;
641
642 hr_utility.set_location('Leaving '|| l_proc, 110);
643 exception
644 when others then
645 hr_utility.set_location('Leaving '|| l_proc, 999);
646 hr_utility.set_location(sqlerrm,9999);
647 hr_utility.raise_error;
648 end dyn_sql;
649 -------------------------------Procedure pay_message_lines ---------------------------
650 -- Procedure to insert error messages to pay_message_lines
651 -- pragma autonomous_transaction is used here to isolate this transaction from
652 -- the parent this is done to retain the error messages in the table even if
653 -- the program errors and the process is rolled back
654 procedure populate_run_msg(assignment_action_id in number
655 ,p_message_text in varchar2
656 ,p_message_level in varchar2 default 'F'
657 )
658 is
659 pragma autonomous_transaction;
660 l_proc constant varchar2(50):= g_package||'populate_run_msg';
661 begin
662 hr_utility.set_location(' Entering:'||l_proc,111);
663
664 insert into pay_message_lines(line_sequence,
665 payroll_id,
666 message_level,
667 source_id,
668 source_type,
669 line_text)
670 values(
671 pay_message_lines_s.nextval
672 ,null
673 ,p_message_level
674 ,assignment_action_id
675 ,'A'
676 ,substr(p_message_text,1,240)
677 );
678
679 hr_utility.set_location(' Entering:'||l_proc,999);
680 commit;
681 exception when others then
682 hr_utility.trace('Error occured in populate_run_msg');
683 hr_utility.set_location(' Leaving with error:'||l_proc,000);
684 raise;
685 end populate_run_msg;
686
687 -------------------------------Procedure range_cursor --------------------------
688 -- select all people in the BG, filter out non appropriate ones in
689 -- action_creation procedure.
690 -- sqlstr must contain one and only one entry of :payroll_action_id
691 -- it must be ordered by person_id
692 procedure range_cursor (pactid in number,
693 sqlstr out nocopy varchar2)
694
695 is
696 l_proc constant varchar2(35) := g_package
697 ||'range_cursor';
698 begin
699 hr_utility.set_location('Entering: '
700 ||l_proc,1);
701
702
703 sqlstr := 'select distinct person_id '
704 ||'from per_all_people_f ppf, '
705 ||'pay_payroll_actions ppa '
706 ||'where ppa.payroll_action_id = :payroll_action_id '
707 ||'and ppa.business_group_id = ppf.business_group_id '
708 ||'order by ppf.person_id';
709
710 hr_utility.trace(' Range Cursor Statement : '
711 ||sqlstr);
712
713 hr_utility.set_location(' Leaving: '
714 ||l_proc,100);
715 exception
716 when others then
717 hr_utility.set_location(' Leaving: '
718 ||l_proc,50);
719
720 fnd_file.put_line(fnd_file.log,substr('Error in rangecode '
721 ||sqlerrm(sqlcode),1,80));
722
723 -- Return cursor that selects no rows
724 sqlstr := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
725 end range_cursor;
726 ------------------------------function check_action-----------------------------
727 -- Function checks if assignment action already created for the payroll action
728 -- and assignment
729 function check_action(p_pactid in number,
730 p_assignment_id in number) return boolean is
731 l_proc constant varchar2(50):= g_package||'check_max_action';
732 l_action number;
733 l_ret boolean := true;
734
735 cursor check_action_exists is
736 select assignment_action_id
737 from pay_assignment_actions
738 where payroll_action_id = p_pactid
739 and assignment_id = p_assignment_id;
740
741 begin
742 open check_action_exists;
743 fetch check_action_exists into l_action;
744 if check_action_exists%found then
745 l_ret:= false;
746 end if;
747 close check_action_exists;
748
749 return l_ret;
750 end check_action;
751
752 ------------------------------function check_max_action-------------------------
753 -- Checks if an assignment action passed is the max for the particular payroll
754 -- action and person.This helps determine if the absence and qualification
755 -- records are to be archived or not Absence and wualification are archived only
756 -- if the assignment action is highest so that they get archived only once per person.
757
758 function check_max_action(p_assactid in number,
759 p_person_id in number,
760 p_pactid in number) return boolean
761 is
762 l_proc constant varchar2(50):= g_package||'check_max_action';
763 l_action number;
764 l_ret boolean;
765
766 cursor csr_check_action is
767 select min(assignment_action_id)
768 from pay_assignment_actions
769 where assignment_id in (select assignment_id
770 from per_all_assignments_f
771 where person_id = p_person_id)
772 and payroll_action_id = p_pactid;
773
774
775
776 begin
777
778 hr_utility.set_location('Entering: '||l_proc,1);
779
780 open csr_check_action;
781 fetch csr_check_action into l_action;
782 close csr_check_action;
783
784 if l_action = p_assactid then
785 l_ret := true;
786 else
787 l_ret := false;
788 end if;
789
790 hr_utility.set_location('Leaving: '||l_proc,999);
791 return l_ret;
792 end check_max_action;
793 ------------------------------procedure action_creation-------------------------
794 -- creates assignment action for the assignments selected by the cursor
795 procedure action_creation(pactid in number,
796 stperson in number,
797 endperson in number,
798 chunk in number) is
799
800
801 cursor csr_parameter_info
802 is
803 select(pay_gb_eoy_archive.get_parameter(legislative_parameters,'CENSUS_YEAR')) census_year,
804 (pay_gb_eoy_archive.get_parameter(legislative_parameters,'CENSUS_DAY')) census_day,
805 add_months(to_date((pay_gb_eoy_archive.get_parameter(legislative_parameters,'CONT_ST_DAY'))),-12) cont_st_day,
806 (pay_gb_eoy_archive.get_parameter(legislative_parameters,'CONT_END_DAY')) cont_end_day,
807 (pay_gb_eoy_archive.get_parameter(legislative_parameters,'LEA_NUM')) lea_num,
808 upper((pay_gb_eoy_archive.get_parameter(legislative_parameters,'DATA_RETURN_TYPE'))) data_return_type,
809 (pay_gb_eoy_archive.get_parameter(legislative_parameters,'ESTB_NUM')) estb_num,
810 (pay_gb_eoy_archive.get_parameter(legislative_parameters,'EXCLUDE_ABS')) exclude_abs,
811 (pay_gb_eoy_archive.get_parameter(legislative_parameters,'EXCLUDE_QUAL')) exclude_qual,
812 (pay_gb_eoy_archive.get_parameter(legislative_parameters,'ASG_SET')) asg_set,
813 effective_date,
814 business_group_id
815 from pay_payroll_actions
816 where payroll_action_id = pactid;
817
818
819 cursor csr_asg (p_asg_set_id number)
820 is
821 select distinct asg.assignment_id,
822 asg.person_id
823 from per_all_assignments_f asg,
824 hr_location_extra_info hlei,
825 per_people_extra_info pei,
826 (select distinct min(asg1.effective_start_date) over( partition by assignment_id) effective_start_date,
827 max(asg1.effective_end_date) over( partition by assignment_id) effective_end_date,
828 first_value(location_id)over( partition by assignment_id order by asg1.effective_end_date desc ) location_id,
829 asg1.assignment_id
830 from per_all_assignments_f asg1,
831 per_assignment_status_types pas
832 where asg1.assignment_status_type_id = pas.assignment_status_type_id
833 and pas.per_system_status = 'ACTIVE_ASSIGN') min_max
834 where asg.person_id between stperson and endperson
835 and asg.business_group_id + 0 = g_business_group_id
836 and min_max.location_id = hlei.location_id
837 and hlei.information_type = 'PQP_GB_EDU_ESTB_INFO'
838 and hlei.lei_information6 = g_lea_number
839 and (g_estb_number is null
840 or hlei.lei_information2 = g_estb_number)
841 and pei.person_id = asg.person_id
842
843 and pei.information_type = 'PQP_SCHOOL_WORKFORCE_CENSUS'
844 and pei.pei_information5 <> 'OTHER'
845
846 and min_max.assignment_id = asg.assignment_id
847
848 and (min_max.effective_end_date between g_cont_data_st_date and g_cont_data_end_date -- contract change or the contract is terminated
849
850 or (g_census_day between min_max.effective_start_date and min_max.effective_end_date
851 ) -- Check for snapshot data
852
853 or (g_exclude_absence = 'No' -- Check absence existance only if it is not excluded
854 and exists (select 1 -- If an absence exists in the previous calander year
855 from per_absence_attendances abs
856 where abs.person_id = asg.person_id
857 and abs.business_group_id = asg.business_group_id
858 and (abs.date_end between g_cont_data_st_date and g_cont_data_end_date
859 or abs.date_start between g_cont_data_st_date and g_cont_data_end_date))
860 )
861 or (g_exclude_qual = 'No' -- Qualification present or not?
862 and g_census_day between min_max.effective_start_date and min_max.effective_end_date -- check if contract is still
863 and exists (select 1 from per_qualifications qual -- valid only if qual data is included
864 where qual.person_id = asg.person_id )
865 )
866 )
867
868 -- 28 Days Condition
869 and decode(min_max.effective_end_date,hr_general.end_of_time,to_date(g_census_day),min_max.effective_end_date)-
870 min_max.effective_start_date >= 28
871 -- 28 Days Condition
872
873 and (g_data_ret_type <> 'TYPE3'
874 or (g_data_ret_type = 'TYPE3' and pei.pei_information5 = 'CENTRAL_STAFF'))
875
876 and (p_asg_set_id is null -- don't check for assignment set in this case
877 or exists (select 1
878 from hr_assignment_sets has1
879 where has1.assignment_set_id = p_asg_set_id
880 and has1.business_group_id = asg.business_group_id
881 and nvl(has1.payroll_id,asg.payroll_id) = asg.payroll_id
882 and (not exists (select 1 -- chk no amendments
883 from hr_assignment_set_amendments hasa1
884 where hasa1.assignment_set_id = has1.assignment_set_id)
885 or exists (select 1 -- chk include amendments
886 from hr_assignment_set_amendments hasa2
887 where hasa2.assignment_set_id = has1.assignment_set_id
888 and hasa2.assignment_id = asg.assignment_id
889 and nvl(hasa2.include_or_exclude,'I') = 'I')
890 or (not exists (select 1 --chk no exlude amendments
891 from hr_assignment_set_amendments hasa3
892 where hasa3.assignment_set_id = has1.assignment_set_id
893 and hasa3.assignment_id = asg.assignment_id
894 and nvl(hasa3.include_or_exclude,'I') = 'E')
895 and not exists (select 1 --and chk no Inc amendments
896 from hr_assignment_set_amendments hasa4
897 where hasa4.assignment_set_id = has1.assignment_set_id
898 and nvl(hasa4.include_or_exclude,'I') = 'I')
899 ) -- end checking exclude amendments
900 ) -- done checking amendments
901 ) -- done asg set check when not null
902 ); -- end of asg set check
903
904
905 cursor csr_contract (p_asg_set_id number)
906 is
907 select distinct asg.assignment_id,asg.person_id
908 from per_all_assignments_f asg,
909 pqp_assignment_attributes_f att,
910 hr_location_extra_info hlei,
911 per_people_extra_info pei,
912 (select min(att1.effective_start_date) effective_start_date,
913 max(att1.effective_end_date) effective_end_date,
914 att1.assignment_id
915 from pqp_assignment_attributes_f att1
916 group by assignment_id ) min_max,
917
918 (select first_value(location_id)over( partition by assignment_id order by asg1.effective_end_date desc ) location_id,
919 asg1.assignment_id
920 from per_all_assignments_f asg1,
921 per_assignment_status_types pas
922 where asg1.assignment_status_type_id = pas.assignment_status_type_id
923 and pas.per_system_status = 'ACTIVE_ASSIGN') loc
924 where asg.person_id between stperson and endperson
925 and asg.business_group_id + 0 = g_business_group_id
926 and att.business_group_id = asg.business_group_id
927 and asg.assignment_id = att.assignment_id
928 and loc.assignment_id = asg.assignment_id
929 and loc.location_id = hlei.location_id
930 and hlei.information_type = 'PQP_GB_EDU_ESTB_INFO'
931 and hlei.lei_information6 = g_lea_number
932 and (g_estb_number is null
933 or hlei.lei_information2 = g_estb_number)
934 and pei.person_id = asg.person_id
935
936 and pei.pei_information5 <> 'OTHER'
937 and pei.information_type = 'PQP_SCHOOL_WORKFORCE_CENSUS'
938
939 and min_max.assignment_id = asg.assignment_id
940
941 and (min_max.effective_end_date between g_cont_data_st_date and g_cont_data_end_date -- contract change or the contract is terminated
942
943 or (g_census_day between min_max.effective_start_date and min_max.effective_end_date
944 ) -- Check for snapshot data only
945
946 or (g_exclude_absence = 'No' -- Check absence existance only if it is not excluded
947 and exists (select 1 -- If an absence exists in the previous calander year
948 from per_absence_attendances abs
949 where abs.person_id = asg.person_id
950 and abs.business_group_id = asg.business_group_id
951 and (abs.date_end between g_cont_data_st_date and g_cont_data_end_date
952 or abs.date_start between g_cont_data_st_date and g_cont_data_end_date))
953 )
954
955 or (g_exclude_qual = 'No' -- Qualification present or not?
956 and g_census_day between min_max.effective_start_date and min_max.effective_end_date -- check if contract is still
957 and exists (select 1 from per_qualifications qual -- valid only if qual data is included
958 where qual.person_id = asg.person_id )
959 )
960
961 )
962
963 -- 28 Days Condition
964 and decode(min_max.effective_end_date,hr_general.end_of_time,to_date(g_census_day),min_max.effective_end_date)-
965 min_max.effective_start_date >= 28
966 -- 28 Days Condition
967
968 and (g_data_ret_type <> 'TYPE3'
969 or (g_data_ret_type = 'TYPE3' and pei.pei_information5 = 'CENTRAL_STAFF'))
970
971 and (p_asg_set_id is null -- don't check for assignment set in this case
972 or exists (select 1
973 from hr_assignment_sets has1
974 where has1.assignment_set_id = p_asg_set_id
975 and has1.business_group_id = asg.business_group_id
976 and nvl(has1.payroll_id,asg.payroll_id) = asg.payroll_id
977 and (not exists (select 1 -- chk no amendments
978 from hr_assignment_set_amendments hasa1
979 where hasa1.assignment_set_id = has1.assignment_set_id)
980 or exists (select 1 -- chk include amendments
981 from hr_assignment_set_amendments hasa2
982 where hasa2.assignment_set_id = has1.assignment_set_id
983 and hasa2.assignment_id = asg.assignment_id
984 and nvl(hasa2.include_or_exclude,'I') = 'I')
985 or (not exists (select 1 --chk no exlude amendments
986 from hr_assignment_set_amendments hasa3
987 where hasa3.assignment_set_id = has1.assignment_set_id
988 and hasa3.assignment_id = asg.assignment_id
989 and nvl(hasa3.include_or_exclude,'I') = 'E')
990 and not exists (select 1 --and chk no Inc amendments
991 from hr_assignment_set_amendments hasa4
992 where hasa4.assignment_set_id = has1.assignment_set_id
993 and nvl(hasa4.include_or_exclude,'I') = 'I')
994 ) -- end checking exclude amendments
995 ) -- done checking amendments
996 ) -- done asg set check when not null
997 ); -- end of asg set check
998
999
1000 cursor contract_type is
1001 select distinct pcv_information1
1002 from pqp_configuration_values
1003 where pcv_information_category = 'PQP_GB_SWF_CONTRACT_TYPE'
1004 and business_group_id = g_business_group_id;
1005
1006 -- Local variables
1007 l_arch boolean := true;
1008 l_proc constant varchar2(35):= g_package||'action_creation';
1009 l_ass_act_id pay_assignment_actions.assignment_action_id%type;
1010 l_asg_set hr_assignment_sets.assignment_set_id%type;
1011 l_effective_date date;
1012 e_contract_type_nt_defined exception;
1013 l_contract_type varchar2(50);
1014 begin
1015 if chunk = 1 then
1016 hr_utility.set_location('Entering: '||l_proc,1);
1017 end if;
1018 -----
1019 open csr_parameter_info;
1020 fetch csr_parameter_info into g_census_year,
1021 --g_census_term,
1022 g_census_day,
1023 g_cont_data_st_date,
1024 g_cont_data_end_date,
1025 g_lea_number,
1026 g_data_ret_type,
1027 g_estb_number,
1028 g_exclude_absence,
1029 g_exclude_qual,
1030 l_asg_set,
1031 l_effective_date,
1032 g_business_group_id;
1033 close csr_parameter_info;
1034
1035 -------
1036 open contract_type;
1037 fetch contract_type into l_contract_type;
1038 close contract_type;
1039
1040 if l_contract_type is not null then
1041 if l_contract_type = 'ASG_CAT' then
1042 g_pick_from_asg := 'Y';
1043 else
1044 g_pick_from_asg := 'N';
1045 end if;
1046 else
1047 fnd_file.put_line(fnd_file.log,'Contract Details - Contract Type Configuration is not set.Please configure and proce');
1048 raise e_contract_type_nt_defined;
1049 end if;
1050 if chunk = 1 then
1051 hr_utility.set_location('g_pick_from_asg : ' || g_pick_from_asg,30);
1052 end if;
1053
1054
1055 if chunk = 1 then
1056 hr_utility.set_location('g_census_year : ' || g_census_year,30);
1057 hr_utility.set_location('g_census_day : ' || g_census_day,30);
1058 hr_utility.set_location('g_cont_data_st_date : ' || g_cont_data_st_date,30);
1059 hr_utility.set_location('g_cont_data_end_date : ' || g_cont_data_end_date,30);
1060 hr_utility.set_location('g_lea_number : ' || g_lea_number,30);
1061 hr_utility.set_location('g_estb_number : ' || g_estb_number,30);
1062 hr_utility.set_location('g_exclude_qual: ' || g_exclude_qual,30);
1063 hr_utility.set_location('l_asg_set : ' || l_asg_set,30);
1064 hr_utility.set_location('l_effective_date : ' || l_effective_date,30);
1065 hr_utility.set_location('g_business_group_id : ' || g_business_group_id,30);
1066 end if;
1067
1068 if g_pick_from_asg = 'N' then
1069
1070 for asg_rec in csr_contract(l_asg_set) loop
1071 -- Check if assignment action already created for this assignment
1072 l_arch := check_action(pactid,asg_rec.assignment_id);
1073 if l_arch then
1074 -- hr_utility.set_location('Creating assignment action for ' || asg_rec.assignment_id,30);
1075 select pay_assignment_actions_s.nextval
1076 into l_ass_act_id
1077 from dual;
1078 --
1079 -- insert into pay_assignment_actions.
1080 hr_nonrun_asact.insact(l_ass_act_id,
1081 asg_rec.assignment_id,
1082 pactid,
1083 chunk,
1084 null);
1085 end if;
1086 end loop; -- end asg_rec
1087 elsif g_pick_from_asg = 'Y' then
1088 if chunk = 1 then
1089 hr_utility.set_location('Entering csr_asg',40);
1090 end if;
1091 for asg_rec in csr_asg(l_asg_set) loop
1092 -- Check if assignment action already created for this assignment
1093 l_arch := check_action(pactid,asg_rec.assignment_id);
1094 if l_arch then
1095 -- hr_utility.set_location('Creating assignment action for ' || asg_rec.assignment_id,30);
1096 select pay_assignment_actions_s.nextval
1097 into l_ass_act_id
1098 from dual;
1099 --
1100 -- insert into pay_assignment_actions.
1101 hr_nonrun_asact.insact(l_ass_act_id,
1102 asg_rec.assignment_id,
1103 pactid,
1104 chunk,
1105 null);
1106 end if;
1107 end loop; -- end asg_rec
1108 end if;
1109
1110 end action_creation;
1111
1112 ------------------------------function get_teachers_number--------------------------------------
1113 -- Fetches teachers number depending on the configuration DFF PQP_GB_SWF_TEACHER_NUM
1114 -- If value is from person EIT/Qualification table function fetches the first not null value
1115 -- for the column defined in the configuration.
1116 function get_teachers_number(p_person_id in number
1117 , p_effective_date in date) return varchar2 is
1118 cursor qual_mbr_num is
1119 select membership_number
1120 from per_qualifications
1121 where person_id = p_person_id
1122 and membership_number is not null;
1123 ---
1124 e_teacher_no_nt_found exception;
1125 l_teachers_num varchar2(100);
1126 l_proc constant varchar2(50) := g_package || ' get_teachers_number';
1127 begin
1128 hr_utility.set_location('Entering '|| l_proc, 10);
1129
1130 if g_debug then
1131 hr_utility.set_location('p_person_id :'||p_person_id,10);
1132 hr_utility.set_location('p_effective_date :'||p_effective_date,10);
1133 end if;
1134
1135 if g_teach_dff_name = 'PER_PEOPLE' then
1136
1137 execute immediate g_teacher_sql_str into l_teachers_num using p_person_id, p_effective_date;
1138
1139 elsif g_teach_dff_name = 'Extra Person Info DDF' then
1140
1141 execute immediate g_teacher_sql_str into l_teachers_num using p_person_id;
1142
1143 elsif g_teach_dff_name = 'QUAL_MEMBERSHIP_NUMBER'then
1144 open qual_mbr_num;
1145 fetch qual_mbr_num into l_teachers_num;
1146 close qual_mbr_num;
1147 end if;
1148
1149 -- Bug#12599238
1150 -- Added replace function to remove bank spaces and '-' in teacher's number
1151
1152 -- return l_teachers_num;
1153 return replace(replace(l_teachers_num,' ',''),'-','');
1154
1155 -- Bug#12599238
1156
1157
1158 hr_utility.set_location('Leaving '|| l_proc, 100);
1159 exception when
1160 no_data_found then
1161 hr_utility.set_location('Leaving with error '|| l_proc, 888);
1162 return null;
1163 when others then
1164 hr_utility.set_location('Leaving with error '|| l_proc, 999);
1165 hr_utility.set_location(sqlerrm,999);
1166 return null;
1167 end get_teachers_number;
1168
1169 ------------------------------function get_dcsf_values--------------------------
1170 -- This function queries PQP_CONFIGURATION_VALUES table for the given context
1171 -- and returns the dcsf equivalent for the passed value.
1172 -- This function is used for the following context.
1173 -- PQP_GB_SWF_ETHNIC_CODES Staff Details - Ethnic Origin
1174 -- PQP_GB_SWF_PAY_SCALE_MAPPING Pay - Pay Scale
1175 -- PQP_GB_SWF_QTS_ROUTE_SRC Staff Details - QTS Route source
1176 -- PQP_GB_SWF_QUAL_SUBJECT_MAP Qualifications - Subject
1177 function get_dcsf_values(p_context_name in varchar2,
1178 p_value in varchar2) return varchar2
1179 is
1180 l_return pqp_configuration_values.pcv_attribute2%type;
1181 l_proc constant varchar2(50):= g_package||'get_dcsf_values';
1182
1183 cursor get_config_values (p_context_name in varchar2 , p_value in varchar2) is
1184 select pcv_information2
1185 from pqp_configuration_values
1186 where pcv_information_category = p_context_name
1187 and pcv_information1 = p_value
1188 and business_group_id = g_business_group_id;
1189
1190 begin
1191
1192 open get_config_values(p_context_name,p_value);
1193 fetch get_config_values into l_return;
1194 close get_config_values;
1195
1196 return l_return;
1197
1198 end get_dcsf_values;
1199 ------------------------------function fetch_staff_rec--------------------------
1200 -- fetches details for staff details module.Validates the fetched values with
1201 -- staff_details_validate if no errors , details are archived.
1202 function fetch_staff_details( p_assactid in number,
1203 p_effective_date in date,
1204 p_staff_rec out nocopy act_info_rec) return boolean is
1205
1206 l_proc constant varchar2(50):= g_package||'fetch_staff_rec';
1207
1208
1209 cursor csr_person_details(csr_effective_date date) is
1210 select /*+ ORDERED */
1211 pap.person_id,
1212 pap.employee_number,
1213 paa.assignment_id,
1214 pap.first_name,
1215 pap.last_name ,
1216 paa.assignment_number,
1217 pap.national_identifier,
1218 decode(pap.sex,'M','1','F','2','9') sex,
1219 pap.previous_last_name,
1220 per_information1 ethnic_code,
1221 pap.date_of_birth date_of_birth,
1222 to_char(pap.date_of_birth,'YYYY-MM-DD') dob_dcsf
1223 from pay_assignment_actions act,
1224 per_all_assignments_f paa,
1225 per_all_people_f pap
1226 where act.assignment_action_id = p_assactid
1227 and act.assignment_id = paa.assignment_id
1228 and paa.person_id = pap.person_id
1229 and csr_effective_date between paa.effective_start_date and paa.effective_end_date
1230 and csr_effective_date between pap.effective_start_date and pap.effective_end_date;
1231
1232 cursor get_swf_dff_vaues(p_person_id in number) is
1233 select hr_general.decode_lookup('YES_NO',pei_information1 ) qt_sts,
1234 fnd_date.canonical_to_date(pei_information2) qt_status_date,
1235 pei_information3 qts_route,
1236 pei_information4 teacher_number,
1237 pei_information5 workforce_inc_typ,
1238 hr_general.decode_lookup('YES_NO',pei_information9) hlta_sts,
1239 pei_information10 origin
1240 from per_people_extra_info pei
1241 where pei.information_type = 'PQP_SCHOOL_WORKFORCE_CENSUS'
1242 and pei.person_id = p_person_id;
1243 --
1244 cursor csr_disablity(p_person_id number, csr_effective_date date) is
1245 select 'YES'
1246 from per_disabilities_f pdf
1247 where pdf.person_id = p_person_id
1248 and csr_effective_date between pdf.effective_start_date and pdf.effective_end_date;
1249 --
1250 cursor csr_cont_eff_date_asg is
1251 select max(effective_end_date)
1252 from pay_assignment_actions act,
1253 per_all_assignments_f paa,
1254 per_assignment_status_types pas
1255 where act.assignment_action_id = p_assactid
1256 and act.assignment_id = paa.assignment_id
1257 and paa.assignment_status_type_id = pas.assignment_status_type_id
1258 and pas.per_system_status = 'ACTIVE_ASSIGN';
1259
1260 cursor csr_cont_eff_date_contract is
1261 select max(effective_end_date)
1262 from pay_assignment_actions act,
1263 pqp_assignment_attributes_f att
1264 where act.assignment_action_id = p_assactid
1265 and act.assignment_id = att.assignment_id;
1266
1267 cursor get_person_id(p_effective_date date) is
1268 select ppf.person_id,ppf.employee_number
1269 from pay_assignment_actions act,
1270 per_all_assignments_f paa,
1271 per_all_people_f ppf
1272 where act.assignment_action_id = p_assactid
1273 and act.assignment_id = paa.assignment_id
1274 and ppf.person_id = paa.person_id
1275 and p_effective_date between paa.effective_start_date and paa.effective_end_date
1276 and p_effective_date between ppf.effective_start_date and ppf.effective_end_date;
1277
1278 cursor get_estab_no( p_assignment_id in number , p_effective_date in date) is
1279 select hlei.lei_information2
1280 from per_all_assignments_f asg,
1281 hr_location_extra_info hlei
1282 where asg.business_group_id + 0 = g_business_group_id
1283 and asg.location_id = hlei.location_id
1284 and asg.assignment_id = p_assignment_id
1285 and p_effective_date between asg.effective_start_date and asg.effective_end_date
1286 and asg.location_id = hlei.location_id
1287 and hlei.information_type = 'PQP_GB_EDU_ESTB_INFO'
1288 and hlei.lei_information6 = g_lea_number;
1289
1290
1291 l_staff_rec csr_person_details%rowtype;
1292 l_swf_def_values_rec get_swf_dff_vaues%rowtype;
1293 l_asg_active_on_census_day boolean := true; -- variable set to true if he is active on census day
1294 l_person_id number;
1295 l_employee_number per_all_people_f.employee_number%type;
1296 l_qt_status hr_lookups.meaning%type;
1297 l_qts_route hr_lookups.meaning%type;
1298 l_disablity varchar2(5) := 'NO';
1299 l_hlta_status hr_lookups.meaning%type;
1300 l_teachers_number varchar2(240);
1301 l_arch boolean;
1302 l_effective_date date;
1303 e_cont_eff_dt_nt_found exception;
1304 l_estab_number number;
1305 l_valid_ethnic_code varchar2(1);
1306 l_asg_end_date date;
1307
1308
1309 begin
1310 hr_utility.set_location('Entering: '||l_proc,1);
1311 l_arch := true;
1312
1313
1314 hr_utility.set_location('Session_id Fetch person :'|| sys_context('userenv','sessionid'),777777);
1315 hr_utility.set_location('g_census_year : ' || g_census_year,30);
1316 hr_utility.set_location('g_census_day : ' || g_census_day,30);
1317 hr_utility.set_location('g_cont_data_st_date : ' || g_cont_data_st_date,30);
1318 hr_utility.set_location('g_cont_data_end_date : ' || g_cont_data_end_date,30);
1319 hr_utility.set_location('g_lea_number : ' || g_lea_number,30);
1320 hr_utility.set_location('g_estb_number : ' || g_estb_number,30);
1321 hr_utility.set_location('g_exclude_qual: ' || g_exclude_qual,30);
1322 hr_utility.set_location('l_effective_date : ' || l_effective_date,30);
1323 hr_utility.set_location('g_business_group_id : ' || g_business_group_id,30);
1324 hr_utility.set_location('g_qts_route_sql_str : ' || g_qts_route_sql_str,30);
1325 hr_utility.set_location('g_qts_sql_str : ' || g_qts_sql_str,30);
1326 hr_utility.set_location('g_hlta_sql_str : ' || g_hlta_sql_str,30);
1327
1328 -- check if staff is present as of census day
1329 open csr_person_details(g_census_day);
1330 fetch csr_person_details into l_staff_rec;
1331 if csr_person_details%notfound then
1332 l_asg_active_on_census_day := false; -- continuous data to be archived
1333 hr_utility.set_location('Staff record not present on census day'||'Assignment act ID :'||p_assactid,1);
1334 end if;
1335 close csr_person_details;
1336
1337 -- if staff present as of census day then the effective date for the contract
1338 -- is census day.
1339 if l_asg_active_on_census_day then
1340 l_effective_date := g_census_day;
1341 l_person_id:= l_staff_rec.person_id;
1342 l_employee_number:= l_staff_rec.employee_number;
1343
1344
1345 -- the person is active but the contract is end-dated.
1346 -- pick the least of contract end date and census day.
1347 if g_pick_from_asg <> 'Y' then
1348 open csr_cont_eff_date_contract;
1349 fetch csr_cont_eff_date_contract into l_effective_date;
1350 close csr_cont_eff_date_contract;
1351
1352 l_effective_date := least(g_census_day,l_effective_date);
1353 end if;
1354 else
1355 -- if staff not present as of census day then the effective date is either
1356 -- assignment End date or the contract type End date depending on the
1357 -- configuration
1358 if g_pick_from_asg = 'Y' THEN
1359 open csr_cont_eff_date_asg;
1360 fetch csr_cont_eff_date_asg into l_effective_date;
1361 close csr_cont_eff_date_asg;
1362
1363 ELSE -- pick from pqp contract types
1364
1365 open csr_cont_eff_date_contract;
1366 fetch csr_cont_eff_date_contract into l_effective_date;
1367 close csr_cont_eff_date_contract;
1368
1369 -- the person is terminated but the contract is valid till EOT
1370 -- get least of contract end date and assignment end date.
1371 open csr_cont_eff_date_asg;
1372 fetch csr_cont_eff_date_asg into l_asg_end_date;
1373 close csr_cont_eff_date_asg;
1374
1375 l_effective_date := least(l_effective_date,l_asg_end_date);
1376 end if;
1377
1378 open get_person_id(l_effective_date);
1379 fetch get_person_id into l_person_id,l_employee_number;
1380 close get_person_id;
1381
1382 open csr_person_details(l_effective_date);
1383 fetch csr_person_details into l_staff_rec;
1384 if csr_person_details%notfound then
1385 hr_utility.set_location('Staff record could not be fetched as of ' || l_effective_date ||' for Assignment act ID :'||p_assactid,9999);
1386 fnd_file.put_line(fnd_file.log,'Staff record could not be fetched as of ' || l_effective_date ||' for Assignment act ID :'||p_assactid);
1387 --raise e_cont_eff_dt_nt_found;
1388 end if;
1389 close csr_person_details;
1390
1391 end if;
1392
1393 -- if extablishment number is not entered as parameter, compute it here
1394
1395 open get_estab_no(l_staff_rec.assignment_id,l_effective_date);
1396 fetch get_estab_no into l_estab_number;
1397 close get_estab_no;
1398
1399 if g_estb_number is not null and g_estb_number <> l_estab_number then
1400 l_arch := false;
1401 hr_utility.set_location('This assignment should not be processed',99);
1402 end if;
1403
1404
1405
1406 open csr_disablity(l_person_id,l_effective_date);
1407 fetch csr_disablity into l_disablity;
1408 close csr_disablity;
1409 -- Dynamic SQL execution starts.
1410 -- if any dynamic sql fails, it gets reported in the log file and the process
1411 -- continues
1412 begin
1413 l_teachers_number:= get_teachers_number(l_person_id,l_effective_date);
1414 exception when others then
1415 hr_utility.set_location('Teachers Number could not be fetched as of ' || l_effective_date ||'for Person ID :'||l_person_id,9999);
1416 end;
1417
1418 begin
1419 execute immediate g_qts_sql_str into l_qt_status using g_business_group_id,l_effective_date, l_person_id;
1420 exception when others then
1421 hr_utility.set_location('QT status could not be fetched as of ' || l_effective_date ||'for Person ID :'||l_person_id,9999);
1422 end;
1423
1424 hr_utility.set_location('l_qt_status :'||l_qt_status,10);
1425
1426 begin
1427 execute immediate g_qts_route_sql_str into l_qts_route using g_business_group_id,l_effective_date, l_person_id;
1428 exception when others then
1429 hr_utility.set_location('QTS Route could not be fetched as of' || l_effective_date ||'for Person ID :'||l_person_id,9999);
1430 end;
1431
1432 begin
1433 if g_hlta_dff_name = 'JOB' then
1434 execute immediate g_hlta_sql_str into l_hlta_status using g_business_group_id,l_effective_date, l_person_id;
1435 elsif g_hlta_dff_name in ('PER_PEOPLE','PER_ASSIGNMENTS') then
1436 execute immediate g_hlta_sql_str into l_hlta_status using l_person_id, l_effective_date;
1437 end if;
1438
1439 exception when others then
1440 hr_utility.set_location('HLTA status could not be fetched as of' || l_effective_date ||'for Person ID :'||l_person_id,9999);
1441 end;
1442
1443 -- Specific staff details can be entered in the extra person information
1444 -- fetch those values and use them if the computed value(thru configuration)
1445 -- is null
1446 open get_swf_dff_vaues(l_person_id);
1447 fetch get_swf_dff_vaues into l_swf_def_values_rec;
1448 close get_swf_dff_vaues;
1449
1450
1451
1452 l_staff_rec.ethnic_code := get_dcsf_values('PQP_GB_SWF_ETHNIC_CODES',l_staff_rec.ethnic_code);
1453 l_teachers_number := nvl(l_teachers_number,l_swf_def_values_rec.teacher_number);
1454 if l_swf_def_values_rec.qt_status_date < g_census_day then
1455 l_qt_status := nvl(l_qt_status,l_swf_def_values_rec.qt_sts);
1456 end if;
1457 l_qts_route := nvl(l_qts_route,l_swf_def_values_rec.qts_route);
1458 l_hlta_status := nvl(l_hlta_status,l_swf_def_values_rec.hlta_sts);
1459
1460 -- Validation starts
1461 -- the numbers in the comment denotes the validation numbers in SWF tech spec
1462 -- If any error occurs, insert into pay_message_lines with the procedure
1463 -- populate_run_msg.
1464
1465 -- 4100
1466 if l_qt_status = 'Yes' and l_teachers_number is null then
1467 l_arch := false;
1468 hr_utility.set_location('Teachers number error',10);
1469 populate_run_msg(p_assactid,'Qualified Teacher with Teacher Number missing');
1470 end if;
1471
1472 -- 4105
1473 if l_teachers_number is not null and length(l_teachers_number) <> 7 then
1474 l_arch := false;
1475 hr_utility.set_location('Teachers number error',20);
1476 populate_run_msg(p_assactid,'Teacher number is not 7 digits');
1477 end if;
1478
1479
1480 begin
1481 l_teachers_number := to_number(l_teachers_number);
1482 exception when others then
1483 l_arch := false;
1484 hr_utility.set_location('Teachers number has invalid characters',20);
1485 populate_run_msg(p_assactid,'Teachers number has invalid characters');
1486 end;
1487
1488
1489 -- 4110
1490 if l_staff_rec.last_name is null then
1491 l_arch := false;
1492 populate_run_msg(p_assactid,'Family Name is missing');
1493 hr_utility.set_location('last Name error',10);
1494 elsif l_staff_rec.last_name is not null and instr(l_staff_rec.last_name,' ') > 0 then
1495 l_arch := false;
1496 populate_run_msg(p_assactid,'Family Name contains too many consecutive spaces');
1497 hr_utility.set_location('last Name error',10);
1498 end if;
1499
1500 -- 4120
1501 if l_staff_rec.first_name is null then
1502 l_arch := false;
1503 hr_utility.set_location('First Name error',10);
1504 populate_run_msg(p_assactid,'Given Name is missing');
1505 elsif l_staff_rec.first_name is not null and instr(l_staff_rec.first_name,' ') > 0 then
1506 l_arch := false;
1507 hr_utility.set_location('First Name error',20);
1508 populate_run_msg(p_assactid,'Given Name contains too many consecutive spaces');
1509 end if;
1510
1511 -- 4140
1512 if instr(l_staff_rec.previous_last_name,' ') > 0 then
1513 l_arch := false;
1514 hr_utility.set_location('Previous Name error',10);
1515 populate_run_msg(p_assactid,'Former Family Name contains too many consecutive spaces');
1516 end if;
1517
1518 -- 4150,4160Q,4155
1519 if l_staff_rec.national_identifier is null then
1520 l_arch := false ;
1521 populate_run_msg(p_assactid,'Member of workforce with missing NI Number');
1522 hr_utility.set_location('NI error',20);
1523 elsif l_staff_rec.national_identifier is not null and
1524 hr_gb_utility.ni_validate(l_staff_rec.national_identifier,sysdate) <> 0 then
1525 l_arch := false;
1526 populate_run_msg(p_assactid,'NI Number has invalid Format');
1527 hr_utility.set_location('NI error',10);
1528 if substr(l_staff_rec.national_identifier,1,2) in ('GB','BG','NK','KN','TN','NT','ZZ') then
1529 l_arch := false;
1530 populate_run_msg(p_assactid,'Appears to be a temporary or non-standard NI Number. This must be resolved or removed');
1531 hr_utility.set_location('NI error',10);
1532 end if;
1533 end if;
1534
1535 --4180
1536 if l_staff_rec.sex is null then
1537 l_arch := false;
1538 populate_run_msg(p_assactid,'Gender is missing');
1539 hr_utility.set_location('Gender error',10);
1540 end if;
1541
1542 -- 4190
1543 if l_staff_rec.dob_dcsf is null then
1544 l_arch := false;
1545 populate_run_msg(p_assactid,'Date of Birth missing');
1546 hr_utility.set_location('DOB error',30);
1547 end if;
1548
1549 -- 4200 /* Corrected the code.Bug#12586059 */
1550 if not(months_between(g_census_day,l_staff_rec.date_of_birth)/12 between 15 and 100) then
1551 l_arch := false;
1552 populate_run_msg(p_assactid,'Person''s age must be between 15 and 100 years');
1553 hr_utility.set_location('Age error',30);
1554 end if;
1555
1556 -- Bug#12586059
1557 -- check removed as per new specification.
1558
1559 -- 4210Q
1560 -- if not(months_between(trunc(g_census_day),l_staff_rec.date_of_birth)/12 between 16 and 70) then
1561 -- populate_run_msg(p_assactid,'Please check: Person''s age expected to be between 16 and 70 years','W');
1562 -- hr_utility.set_location('Age warning',30);
1563 -- end if;
1564
1565 -- Bug#12586059
1566
1567 --4220
1568 if l_staff_rec.ethnic_code is not null then
1569 begin
1570 select 'Y'
1571 into l_valid_ethnic_code
1572 from dual
1573 where exists
1574 (select lookup_code
1575 from hr_lookups hl
1576 where hl.lookup_type = 'UK_ETHNIC_CODES'
1577 and hl.enabled_flag = 'Y'
1578 and hl.lookup_code = l_staff_rec.ethnic_code);
1579 exception
1580 when others
1581 then
1582 l_arch := false;
1583 populate_run_msg (p_assactid, 'Ethnicity is invalid ');
1584 hr_utility.set_location ('Ethnicity error', 10);
1585 end;
1586 end if;
1587
1588 if l_staff_rec.ethnic_code is not null and length(l_staff_rec.ethnic_code) <> 4 then
1589 l_arch := false;
1590 populate_run_msg (p_assactid, 'Ethnicity is invalid ');
1591 hr_utility.set_location ('Ethnicity length error', 10);
1592 end if;
1593
1594 if l_staff_rec.ethnic_code is null then
1595 populate_run_msg (p_assactid, 'Please Check: Ethnicity is missing ','W');
1596 hr_utility.set_location ('Ethnicity length error', 10);
1597 end if;
1598
1599 -- 4225 Disablity hardcoded, cannot be other than YES or NO.
1600 -- 4230
1601 if l_qt_status is not null and l_qt_status not in ('Yes','No') then
1602 l_arch := false;
1603 populate_run_msg (p_assactid, 'QT Status is invalid ');
1604 hr_utility.set_location (l_qt_status||' QT status error', 10);
1605 end if;
1606
1607 -- 4235Q
1608 if l_qt_status = 'Yes' and months_between(trunc(g_census_day),l_staff_rec.date_of_birth)/12 < 21 then
1609 populate_run_msg (p_assactid, 'Person cannot be shown as having QT status and be under 21 on 1 January','W');
1610 hr_utility.set_location ('QT status Age error', 10);
1611 end if;
1612
1613 -- 4240
1614 if l_hlta_status is not null and l_hlta_status not in ('Yes','No') then
1615 l_arch := false;
1616 populate_run_msg (p_assactid, 'HLTA Status is invalid ');
1617 hr_utility.set_location (l_hlta_status||' HLTA status error', 10);
1618 end if;
1619
1620 -- 4245
1621 if l_hlta_status = 'Yes' and months_between(trunc(g_census_day),l_staff_rec.date_of_birth)/12 < 18 then
1622 l_arch := false;
1623 populate_run_msg (p_assactid, 'Person cannot be shown as having HLTA status and be under 18 on 1 January');
1624 hr_utility.set_location (l_hlta_status||' HLTA status Age error', 10);
1625 end if;
1626
1627 -- 4250
1628 if l_qts_route is not null then
1629 begin
1630 select 'Y'
1631 into l_valid_ethnic_code
1632 from dual
1633 where exists(select lookup_code
1634 from hr_lookups hl
1635 where hl.lookup_type = 'PQP_GB_SWF_QTS_ROUTES'
1636 and hl.enabled_flag = 'Y'
1637 and hl.lookup_code = l_qts_route);
1638 exception
1639 when others
1640 then
1641 l_arch := false;
1642 populate_run_msg (p_assactid, 'QTS Route is invalid');
1643 hr_utility.set_location (l_qts_route||' QTS route error', 10);
1644 end;
1645 end if;
1646
1647 if l_qts_route is not null and length(l_qts_route) <> 4 then
1648 l_arch := false;
1649 populate_run_msg (p_assactid, 'QTS Route is invalid');
1650 hr_utility.set_location (l_qts_route||' QTS route length error', 10);
1651 end if;
1652
1653 -- DO NOT CHANGE the archive structure as many values from this are passed as
1654 -- input values for othe procedures in archive code
1655
1656 p_staff_rec.action_info_category := 'GB_SWF_STAFF_DETAILS';
1657 p_staff_rec.person_id := l_staff_rec.person_id;
1658 p_staff_rec.assignment_id := l_staff_rec.assignment_id;
1659 p_staff_rec.effective_date := sysdate;
1660 p_staff_rec.act_info1 := l_staff_rec.person_id;
1661 p_staff_rec.act_info2 := l_estab_number;
1662 p_staff_rec.act_info3 := l_teachers_number;
1663 p_staff_rec.act_info4 := l_staff_rec.last_name;
1664 p_staff_rec.act_info5 := l_staff_rec.first_name;
1665 p_staff_rec.act_info6 := l_staff_rec.previous_last_name;
1666 p_staff_rec.act_info7 := l_staff_rec.national_identifier;
1667 p_staff_rec.act_info8 := l_staff_rec.sex;
1668 p_staff_rec.act_info9 := l_staff_rec.dob_dcsf;
1669 p_staff_rec.act_info10 := l_staff_rec.ethnic_code;
1670 p_staff_rec.act_info11 := l_disablity;
1671 p_staff_rec.act_info12 := l_qt_status; -- passed as in parameter to fetch_payment_details
1672 p_staff_rec.act_info13 := l_hlta_status; -- passed as in parameter to fetch_contract_details
1673 p_staff_rec.act_info14 := l_qts_route;
1674 p_staff_rec.act_info15 := null; --l_abs_on_cd;
1675 p_staff_rec.act_info16 := l_effective_date; -- passed as in parameter to all functions
1676 -- called in archive code
1677
1678
1679 hr_utility.set_location('Leaving: '||l_proc,999);
1680 return l_arch;
1681 exception
1682 when others then
1683 hr_utility.trace(sqlerrm);
1684 hr_utility.set_location('leaving with error: '||l_proc,9999);
1685 raise;
1686 end fetch_staff_details;
1687
1688 /*-------------------------function run_user_formula --------------------------*/
1689 -- This function executes the user fourmula and provides the results
1690 -- for date of arrival, the format the formula should return is 'YYYY-MM-DD'
1691 -- for hours per week and fte hours, the formula should return values in
1692 -- number(s).numbers(s) format
1693 -- The formula will throw an error if the format is anything other than the
1694 -- specified format
1695 function run_user_formula(p_formula_id in ff_formulas_f.formula_name%type
1696 ,p_assignment_id in number
1697 ,p_effective_date in date
1698 ,p_business_group_id in number
1699 ,p_assignment_number in varchar2
1700 )
1701 return varchar2 is
1702
1703 cursor get_formula_id is
1704 select ff.formula_id,ff.formula_name
1705 from ff_formulas_f ff
1706 where ff.formula_id = p_formula_id
1707 and ff.business_group_id = p_business_group_id
1708 and p_effective_date between ff.effective_start_date and ff.effective_end_date;
1709 --
1710 l_inputs ff_exec.inputs_t;
1711 p_inputs ff_exec.inputs_t;
1712 l_outputs ff_exec.outputs_t;
1713 l_result varchar2(25);
1714 l_formula_id ff_formulas_f.formula_id%type;
1715 l_formula_name ff_formulas_f.formula_name%type;
1716 l_warning_msg varchar2(300);
1717
1718 l_proc constant varchar2(50):= g_package||'run_user_formula';
1719 begin
1720 hr_utility.set_location('Entering: '||l_proc,10);
1721 -- setting the contexts
1722 p_inputs(1).name := 'ASSIGNMENT_ID';
1723 p_inputs(1).value := p_assignment_id;
1724 p_inputs(2).name := 'EFFECTIVE_DATE';
1725 p_inputs(2).value := fnd_date.date_to_canonical(p_effective_date);
1726 p_inputs(3).name := 'BUSINESS_GROUP_ID';
1727 p_inputs(3).value := p_business_group_id;
1728
1729 l_formula_id := null;
1730 open get_formula_id;
1731 fetch get_formula_id into l_formula_id,l_formula_name;
1732 if get_formula_id%notfound then
1733 hr_utility.set_location('formula -'||l_formula_name||'- not present/effective in table',11);
1734 l_warning_msg := 'formula -'||l_formula_name||'- not present or effective for assignment '||p_assignment_number||' on '||p_effective_date;
1735 fnd_file.put_line (fnd_file.log, l_warning_msg);
1736 end if;
1737 close get_formula_id;
1738
1739 if l_formula_id is not null then
1740 hr_utility.trace(' Inside run_user_formula '||l_formula_name);
1741 ff_exec.init_formula(l_formula_id, p_effective_date , l_inputs, l_outputs);
1742
1743 if l_inputs.count > 0 and p_inputs.count > 0 then
1744 for i in 1..l_inputs.count
1745 loop
1746 for j in 1..p_inputs.count
1747 loop
1748 if l_inputs(i).name = p_inputs(j).name then
1749 l_inputs(i).value := p_inputs(j).value;
1750 exit;
1751 end if;
1752 end loop;
1753 end loop;
1754 end if;
1755
1756 for i in 1..l_inputs.count loop
1757 hr_utility.trace(' i= '||i||' name '||l_inputs(i).name ||' value '||l_inputs(i).value);
1758 end loop;
1759
1760 ff_exec.run_formula(l_inputs,l_outputs);
1761 hr_utility.trace(' calculated value from user formula '||l_outputs(1).value);
1762 l_result := l_outputs(1).value ;
1763 end if;
1764
1765 hr_utility.set_location('Leaving: '||l_proc,999);
1766
1767 return l_result;
1768
1769 exception
1770 when others then
1771 hr_utility.trace(sqlerrm);
1772 hr_utility.set_location('leaving with error: '||l_proc,9999);
1773 raise;
1774 end;
1775
1776
1777 /*-------------------------Procedure run_seeded_formula --------------------------*/
1778 -- This procedure executes the formula GB_CALCULATE_FTE_USING_PQP_CONTRACT_TYPES
1779 -- and returns if any error or the calculated FTE Ratio Value
1780 procedure run_seeded_formula(p_assignment_id in number
1781 ,p_effective_date in date
1782 ,p_business_group_id in number
1783 ,p_assignment_number in varchar2
1784 ,fte_ratio out NOCOPY number
1785 ,error_message out NOCOPY varchar2
1786 ) is
1787
1788
1789 cursor get_formula_id is
1790 select ff.formula_id,ff.formula_name
1791 from ff_formulas_f ff
1792 where ff.formula_name = 'GB_CALCULATE_FTE_USING_PQP_CONTRACT_TYPES'
1793 and ff.legislation_code = 'GB'
1794 and p_effective_date between ff.effective_start_date and ff.effective_end_date;
1795 --
1796 l_inputs ff_exec.inputs_t;
1797 p_inputs ff_exec.inputs_t;
1798 l_outputs ff_exec.outputs_t;
1799 l_result varchar2(25);
1800 l_formula_id ff_formulas_f.formula_id%type;
1801 l_formula_name ff_formulas_f.formula_name%type;
1802 l_warning_msg varchar2(300);
1803
1804 l_proc constant varchar2(50):= g_package||'run_seeded_formula';
1805 begin
1806 hr_utility.set_location('Entering: '||l_proc,10);
1807 -- setting the contexts
1808 p_inputs(1).name := 'ASSIGNMENT_ID';
1809 p_inputs(1).value := p_assignment_id;
1810 p_inputs(2).name := 'DATE_EARNED';
1811 p_inputs(2).value := fnd_date.date_to_canonical(p_effective_date);
1812 p_inputs(3).name := 'BUSINESS_GROUP_ID';
1813 p_inputs(3).value := p_business_group_id;
1814
1815 l_formula_id := null;
1816 open get_formula_id;
1817 fetch get_formula_id into l_formula_id,l_formula_name;
1818 if get_formula_id%notfound then
1819 hr_utility.set_location('formula -'||l_formula_name||'- not present/effective in table',11);
1820 l_warning_msg := 'formula -'||l_formula_name||'- not present or effective for assignment '||p_assignment_number||' on '||p_effective_date;
1821 fnd_file.put_line (fnd_file.log, l_warning_msg);
1822 end if;
1823 close get_formula_id;
1824
1825 if l_formula_id is not null then
1826 hr_utility.trace(' Inside run_seeded_formula '||l_formula_name);
1827 ff_exec.init_formula(l_formula_id, p_effective_date , l_inputs, l_outputs);
1828
1829 if l_inputs.count > 0 and p_inputs.count > 0 then
1830 for i in 1..l_inputs.count
1831 loop
1832 for j in 1..p_inputs.count
1833 loop
1834 if l_inputs(i).name = p_inputs(j).name then
1835 l_inputs(i).value := p_inputs(j).value;
1836 exit;
1837 end if;
1838 end loop;
1839 end loop;
1840 end if;
1841
1842 for i in 1..l_inputs.count loop
1843 hr_utility.trace(' i= '||i||' name '||l_inputs(i).name ||' value '||l_inputs(i).value);
1844 end loop;
1845
1846 ff_exec.run_formula(l_inputs,l_outputs);
1847
1848 for i in 1..l_outputs.count loop
1849 hr_utility.trace(' i= '||i||' name '||l_outputs(i).name ||' value '||l_outputs(i).value);
1850
1851 if l_outputs(i).name = 'FTE' then
1852 fte_ratio := l_outputs(i).value;
1853 end if;
1854
1855 if l_outputs(i).name = 'ERROR_MESSAGE' then
1856 error_message := l_outputs(i).value;
1857 end if;
1858 end loop;
1859
1860 end if;
1861
1862 hr_utility.set_location('Leaving: '||l_proc,999);
1863
1864 exception
1865 when others then
1866 hr_utility.trace(sqlerrm);
1867 hr_utility.set_location('leaving with error: '||l_proc,9999);
1868 raise;
1869 end run_seeded_formula;
1870
1871 ---------------------------function fetch_addl_payment_details -----------------
1872
1873 function fetch_addl_payment_details(p_assactid in number,
1874 p_assignment_id in number,
1875 p_effective_date in date,
1876 p_addl_payments out nocopy addl_payment_dtl_tab) return boolean is
1877
1878 l_proc constant varchar2(50):= g_package||'fetch_addl_payment_details';
1879
1880 begin
1881 hr_utility.set_location('Entering: '||l_proc,10);
1882 hr_utility.set_location('Parameters : ',20);
1883 hr_utility.set_location('p_assactid : '||p_assactid,20);
1884 hr_utility.set_location('p_assignment_id : '||p_assignment_id,20);
1885 hr_utility.set_location('p_effective_date : '||p_effective_date,20);
1886
1887 begin
1888 select pexi.eei_information1,to_char(nvl(sum(prrv.result_value),0),'fm999999.00')
1889 bulk collect into p_addl_payments
1890 from per_all_assignments_f paf,
1891 pay_element_entries_f pee,
1892 pay_element_type_extra_info pexi,
1893 pay_run_results prr,
1894 pay_input_values_f piv,
1895 pay_run_result_values prrv,
1896 pay_assignment_actions assact,
1897 pay_payroll_actions pact,
1898 per_time_periods pptp
1899 where paf.assignment_id = p_assignment_id
1900 and paf.assignment_id = pee.assignment_id
1901 and pee.element_type_id = pexi.element_type_id
1902 and pexi.information_type = 'PQP_SWFC_ADDITIONAL_PAYMNT_CAT'
1903 and pexi.element_type_id = prr.element_type_id
1904 and prr.assignment_action_id = assact.assignment_action_id
1905 and assact.payroll_action_id = pact.payroll_action_id
1906 and pact.time_period_id = pptp.time_period_id
1907 and pptp.regular_payment_date between add_months(trunc(g_census_day),-12) and trunc(g_census_day)-1
1908 and prr.run_result_id = prrv.run_result_id
1909 and prr.status in ('P','PA')
1910 and prrv.input_value_id = piv.input_value_id
1911 and piv.element_type_id = pexi.element_type_id
1912 and piv.name = 'Pay Value'
1913 and p_effective_date between paf.effective_start_date and paf.effective_end_date
1914 and p_effective_date between pee.effective_start_date and pee.effective_end_date
1915 and p_effective_date between piv.effective_start_date and piv.effective_end_date
1916 group by pexi.eei_information1;
1917
1918 exception
1919 when others then
1920 hr_utility.trace(sqlerrm);
1921 hr_utility.set_location('leaving with error: '||l_proc,9999);
1922 raise;
1923 end;
1924 hr_utility.set_location('Leaving: '||l_proc,99);
1925 return true;
1926 end fetch_addl_payment_details;
1927
1928 ---------------------------function fetch_role_details -------------------------
1929 -- this is called in fetch_payment_details procedure. There is no separate call
1930 -- from archive code.
1931
1932 function fetch_role_details (p_assactid in number,
1933 p_effective_date in date,
1934 p_person_id in number,
1935 p_assignment_id in number,
1936 p_addl_role out nocopy addl_role_tab) return boolean is
1937
1938 l_proc constant varchar2(50):= g_package||'fetch_role_details';
1939
1940 cursor get_assignment_details is
1941 select paa.assignment_number assignment_number,
1942 paa.employee_category employee_cat,
1943 paa.employment_category assignment_cat
1944 from per_all_assignments_f paa
1945 where paa.assignment_id = p_assignment_id
1946 and p_effective_date between paa.effective_start_date and paa.effective_end_date;
1947
1948 cursor role_dcsf(p_role in varchar2) is
1949 select pcv_information4
1950 from pqp_configuration_values
1951 where business_group_id = g_business_group_id
1952 and pcv_information_category = 'PQP_GB_SWF_ROLE_MAPPING'
1953 and pcv_information2 = p_role;
1954
1955
1956 l_main_role hr_lookups.lookup_code%type;
1957 l_assignment_number per_all_assignments_f.assignment_number%type;
1958 l_asg_cat per_all_assignments_f.employment_category%type;
1959 l_employee_cat per_all_assignments_f.employee_category%type;
1960 l_role_tab_idx pls_integer;
1961
1962
1963 begin
1964 hr_utility.set_location('Entering: '||l_proc,10);
1965 hr_utility.set_location('p_assactid : '||p_assactid,20);
1966 hr_utility.set_location('p_effective_date : '||p_effective_date,20);
1967 hr_utility.set_location('p_person_id : '||p_person_id,20);
1968 hr_utility.set_location('p_assignment_id : '||p_assignment_id,20);
1969
1970
1971 if g_role_src in ('JOB','GRD','POS') then
1972 --fetch main role
1973 begin
1974 execute immediate g_role_sql_str into l_main_role using g_business_group_id,p_effective_date, p_assignment_id;
1975 exception when others then
1976 hr_utility.set_location('main role could not be fetched as of ' || p_effective_date ||' for Assignment ID :'||p_assignment_id,9999);
1977 end;
1978 elsif g_role_src in ('EMP_CAT','EMPLOYEE_CATG') then -- asg category
1979 open get_assignment_details;
1980 fetch get_assignment_details into l_assignment_number,l_employee_cat,l_asg_cat;
1981 close get_assignment_details;
1982 end if;
1983
1984 if g_role_src = 'EMP_CAT' then
1985 open role_dcsf(l_asg_cat);
1986 fetch role_dcsf into l_main_role;
1987 close role_dcsf;
1988 elsif g_role_src= 'EMPLOYEE_CATG' then
1989 open role_dcsf(l_employee_cat);
1990 fetch role_dcsf into l_main_role;
1991 close role_dcsf;
1992 end if;
1993
1994 if l_main_role is null then
1995 hr_utility.set_location('Main role not fetched for Assignment no :'||l_assignment_number,9999);
1996 end if;
1997
1998 -- Bulk collect additional roles into p_addl_role
1999 if g_addl_role_src = 'Assignment Developer DF' then
2000 begin
2001 execute immediate g_addl_role_sql_str bulk collect into p_addl_role using p_assignment_id,g_business_group_id;
2002 exception when others then
2003 hr_utility.set_location('Error while fetching Additional Role for Assignment ID :'||p_assignment_id,7777);
2004 end;
2005 elsif g_addl_role_src in('Extra Position Info DDF','Extra Job Info DDF') then
2006 begin
2007 execute immediate g_addl_role_sql_str bulk collect into p_addl_role using p_assignment_id,p_effective_date;
2008 exception when others then
2009 hr_utility.set_location('Error while fetching Additional Role' || p_effective_date ||'for Assignment ID :'||p_assignment_id,8888);
2010 end;
2011 end if;
2012
2013 l_role_tab_idx := p_addl_role.count;
2014
2015 p_addl_role(l_role_tab_idx) := l_main_role; -- Append main role with all additional roles
2016
2017 hr_utility.set_location('Leaving: '||l_proc,999);
2018 return true;
2019 exception when others then
2020 hr_utility.set_location('Leaving with error : '||l_proc,99999);
2021 end fetch_role_details;
2022
2023 function fetch_hours_details(p_assactid in number,
2024 p_assignment_id in number ,
2025 p_effective_date in date,
2026 p_staff_cat in varchar2,
2027 p_person_id in number,
2028 p_contract_type in varchar2,
2029 p_contract_end_date in varchar2,
2030 p_hrs_rec out nocopy act_info_rec) return boolean is
2031
2032 cursor get_hrs_source is
2033 select pcv_information1 hrs_source
2034 from pqp_configuration_values pcv
2035 where pcv.pcv_information_category = 'PQP_GB_SWF_HOURS'
2036 and pcv.business_group_id = g_business_group_id;
2037
2038 cursor get_hrs_details(p_contract_type in varchar2) is
2039 select pcv_information1 hrs_source,
2040 pcv_information2 contract_type,
2041 pcv_information3 hrs_per_wk_formula,
2042 pcv_information4 wks_per_yr_source,
2043 pcv_information5 wks_per_yr_formula,
2044 pcv_information6 wks_per_yr_column
2045 from pqp_configuration_values pcv
2046 where pcv.pcv_information_category = 'PQP_GB_SWF_HOURS'
2047 and nvl(pcv.pcv_information2,p_contract_type) = p_contract_type
2048 and pcv.business_group_id = g_business_group_id;
2049
2050 cursor get_hrs_details_asg (p_staff_cat in varchar2) IS
2051 select pcv_information8 default_weeks_per_yr
2052 from pqp_configuration_values pcv
2053 where pcv.pcv_information_category = 'PQP_GB_SWF_HOURS'
2054 and pcv.pcv_information7 = decode(p_staff_cat,1,'REGULAR_TEACHER',2,'AGENCY_TEACHER',3,'TEACHING_ASSISTANT',4,'OTHER_SUPPORT_STAFF')
2055 and pcv.business_group_id = g_business_group_id;
2056
2057 cursor get_asg_contract_details is
2058 select att.contract_type,work_pattern
2059 from per_all_assignments_f paa,
2060 pqp_assignment_attributes_f att
2061 where paa.assignment_id = p_assignment_id
2062 and paa.assignment_id = att.assignment_id
2063 and p_effective_date between att.effective_start_date and att.effective_end_date
2064 and p_effective_date between paa.effective_start_date and paa.effective_end_date;
2065
2066 cursor get_asg_details is
2067 select paa.assignment_number,paa.person_id,paa.frequency,paa.normal_hours
2068 from per_all_assignments_f paa
2069 where paa.assignment_id = p_assignment_id
2070 and p_effective_date between paa.effective_start_date and paa.effective_end_date;
2071
2072 cursor get_work_pattern(p_work_pattern in varchar2) is
2073 select val.value
2074 from pay_user_tables tab,
2075 pay_user_columns col,
2076 pay_user_rows_f r,
2077 pay_user_column_instances_f val
2078 where tab.user_table_name = 'PQP_COMPANY_WORK_PATTERNS'
2079 and tab.user_table_id=col.user_table_id
2080 and tab.user_table_id=r.user_table_id
2081 and col.user_column_id=val.user_column_id
2082 and r.user_row_id= val.user_row_id
2083 and col.user_column_name = p_work_pattern
2084 and r.row_low_range_or_name = 'Number of Working Days'
2085 and g_census_day between r.effective_start_date and r.effective_end_date
2086 and g_census_day between val.effective_start_date and val.effective_end_date;
2087
2088
2089 cursor get_wk_per_yr(p_column_no in number,p_contract_type in varchar2) is
2090 select val.value
2091 from pay_user_tables tab,
2092 pay_user_columns col,
2093 pay_user_rows_f r,
2094 pay_user_column_instances_f val
2095 where tab.user_table_name = 'PQP_CONTRACT_TYPES'
2096 and tab.business_group_id = g_business_group_id
2097 and tab.user_table_id=col.user_table_id
2098 and tab.user_table_id=r.user_table_id
2099 and col.user_column_id=val.user_column_id
2100 and r.user_row_id= val.user_row_id
2101 and col.user_column_id = p_column_no
2102 and r.row_low_range_or_name = p_contract_type
2103 and g_census_day between r.effective_start_date and r.effective_end_date
2104 and g_census_day between val.effective_start_date and val.effective_end_date;
2105
2106 cursor get_fte_src is
2107 select pcv_information1,pcv_information2
2108 from pqp_configuration_values pcv
2109 where pcv.pcv_information_category = 'PQP_GB_SWF_FTE_HOURS'
2110 and pcv.business_group_id = g_business_group_id;
2111
2112 cursor get_fte_budget_hrs is
2113 select value
2114 from per_assignment_budget_values_f
2115 where assignment_id = p_assignment_id
2116 and unit = 'HOURS'
2117 and p_effective_date between effective_end_date and effective_start_date;
2118
2119 l_proc constant varchar2(50) := g_package || ' fetch_hours_details';
2120 l_hrs_details_rec get_hrs_details%rowtype;
2121 l_contract_type pqp_assignment_attributes_f.contract_type%type;
2122 l_assignment_number per_all_assignments_f.assignment_number%type;
2123 l_hrs_src pqp_configuration_values.pcv_information1%type;
2124 l_hours_per_week varchar2(20);
2125 l_weeks_per_yr varchar2(20);
2126 l_work_pattern pqp_assignment_attributes_f.work_pattern%type;
2127 l_frequency per_all_assignments_f.frequency%type;
2128 l_no_of_hrs per_all_assignments_f.normal_hours%type;
2129 l_person_id per_all_assignments_f.person_id%type;
2130 l_no_of_days_per_wk number;
2131 l_fte_hrs varchar2(1000);
2132 l_fte_src varchar2(30);
2133 l_fte_formula_id number;
2134 l_arch boolean := true;
2135 l_fte_ratio number;
2136 l_error_message varchar2(1000);
2137 l_check_if_num number;
2138 l_error_flag varchar2(1) := 'N';
2139
2140 begin
2141 hr_utility.set_location('Entering: '||l_proc,10);
2142 hr_utility.set_location('Parameters : ',20);
2143 hr_utility.set_location('p_assactid : '||p_assactid,20);
2144 hr_utility.set_location('p_assignment_id : '||p_assignment_id,20);
2145 hr_utility.set_location('p_staff_cat : '||p_staff_cat,20);
2146 hr_utility.set_location('p_person_id : '||p_person_id,20);
2147 hr_utility.set_location('p_effective_date : '||p_effective_date,20);
2148 hr_utility.set_location('g_data_ret_type : '||g_data_ret_type,20);
2149
2150
2151 open get_hrs_source;
2152 fetch get_hrs_source into l_hrs_src;
2153 close get_hrs_source;
2154
2155 open get_asg_details;
2156 fetch get_asg_details into l_assignment_number,l_person_id,l_frequency,l_no_of_hrs;
2157 close get_asg_details;
2158
2159 open get_asg_contract_details;
2160 fetch get_asg_contract_details into l_contract_type,l_work_pattern;
2161 close get_asg_contract_details;
2162
2163 if l_hrs_src = 'ASG' then
2164
2165 if l_frequency = 'D' then
2166 open get_work_pattern(l_work_pattern);
2167 fetch get_work_pattern into l_no_of_days_per_wk;
2168 close get_work_pattern;
2169
2170 l_no_of_hrs := l_no_of_hrs*l_no_of_days_per_wk;
2171 end if;
2172
2173 l_hours_per_week := l_no_of_hrs;
2174
2175 open get_hrs_details_asg(p_staff_cat);
2176 fetch get_hrs_details_asg into l_weeks_per_yr;
2177 close get_hrs_details_asg;
2178
2179 elsif l_hrs_src = 'CONTRACT_TYPE' then
2180
2181 open get_hrs_details(l_contract_type);
2182 fetch get_hrs_details into l_hrs_details_rec;
2183 close get_hrs_details;
2184
2185 if l_contract_type is null then
2186 open get_hrs_details('null');
2187 fetch get_hrs_details into l_hrs_details_rec;
2188 close get_hrs_details;
2189 end if;
2190
2191 if l_hrs_details_rec.hrs_per_wk_formula is not null then
2192 begin
2193 l_hours_per_week := run_user_formula(l_hrs_details_rec.hrs_per_wk_formula,p_assignment_id,p_effective_date,g_business_group_id,l_assignment_number);
2194 hr_utility.set_location('Formula Result Value '||l_hours_per_week,20);
2195 exception when others then
2196 populate_run_msg(p_assactid,'Error while executing formula id '|| l_hrs_details_rec.hrs_per_wk_formula||'. Please refer Log file for details' );
2197 fnd_file.put_line(fnd_file.log,'Error while executing formula id '|| l_hrs_details_rec.hrs_per_wk_formula);
2198 fnd_file.put_line(fnd_file.log,sqlerrm);
2199 end;
2200 end if;
2201
2202 if l_hrs_details_rec.wks_per_yr_source = 'FORMULA' then
2203 if l_hrs_details_rec.wks_per_yr_formula is not null then
2204 begin
2205 l_weeks_per_yr := run_user_formula(l_hrs_details_rec.wks_per_yr_formula,p_assignment_id,p_effective_date,g_business_group_id,l_assignment_number);
2206 hr_utility.set_location('Formula Result Value '||l_weeks_per_yr,20);
2207 exception when others then
2208 populate_run_msg(p_assactid,'Error while executing formula id '|| l_hrs_details_rec.wks_per_yr_formula||'. Please refer Log file for details' );
2209 fnd_file.put_line(fnd_file.log,'Error while executing formula id '|| l_hrs_details_rec.wks_per_yr_formula);
2210 fnd_file.put_line(fnd_file.log,sqlerrm);
2211 end;
2212 end if;
2213 elsif l_hrs_details_rec.wks_per_yr_source = 'COLUMN' then
2214 open get_wk_per_yr(l_hrs_details_rec.wks_per_yr_column,l_contract_type);
2215 fetch get_wk_per_yr into l_weeks_per_yr;
2216 close get_wk_per_yr;
2217 end if;
2218
2219 end if;
2220
2221 l_fte_ratio := null;
2222
2223 if p_staff_cat in (1 , 2) then
2224 l_fte_hrs := 32.5;
2225 elsif p_staff_cat = 3 then
2226 l_fte_hrs := 37;
2227 elsif p_staff_cat = 4 then
2228 open get_fte_src;
2229 fetch get_fte_src into l_fte_src,l_fte_formula_id;
2230 close get_fte_src;
2231
2232 if l_fte_src = 'CAL' and l_fte_formula_id is not null then
2233 begin
2234 l_fte_hrs := run_user_formula(l_fte_formula_id,p_assignment_id,p_effective_date,g_business_group_id,l_assignment_number);
2235 hr_utility.set_location('Formula Result Value '||l_fte_hrs,20);
2236 exception when others then
2237 populate_run_msg(p_assactid,'Error while executing formula id '|| l_fte_formula_id||'. Please refer Log file for details' );
2238 fnd_file.put_line(fnd_file.log,'Error while executing formula id '|| l_fte_formula_id);
2239 fnd_file.put_line(fnd_file.log,sqlerrm);
2240 end;
2241 elsif l_fte_src = 'ASG_BUDGET' then
2242 /*open get_fte_budget_hrs;
2243 fetch get_fte_budget_hrs into l_fte_hrs;
2244 close get_fte_budget_hrs;*/
2245
2246 run_seeded_formula(p_assignment_id
2247 ,p_effective_date
2248 ,g_business_group_id
2249 ,l_assignment_number
2250 ,l_fte_ratio
2251 ,l_error_message
2252 );
2253
2254
2255 l_fte_hrs := l_hours_per_week/l_fte_ratio;
2256
2257 hr_utility.set_location('l_fte_ratio'||l_fte_ratio,20);
2258 hr_utility.set_location('l_hours_per_week'||l_hours_per_week,20);
2259
2260 if l_error_message is not null then
2261 populate_run_msg(p_assactid,'Error while executing formula GB_CALCULATE_FTE_USING_PQP_CONTRACT_TYPES.'||l_error_message );
2262 fnd_file.put_line(fnd_file.log,'Error while executing formula id '|| l_fte_formula_id);
2263 fnd_file.put_line(fnd_file.log,sqlerrm);
2264 end if;
2265
2266 if l_fte_hrs = 0 or l_fte_hrs is null then
2267 l_arch := false;
2268 hr_utility.set_location('FTE Hrs is not defiled or FTE hrs is Zero',20);
2269 populate_run_msg(p_assactid,'FTE Hrs is not defiled or FTE hrs is Zero. This could be if Work Pattern is not defined for the assignment');
2270 end if;
2271
2272 end if;
2273
2274 end if;
2275
2276 if l_fte_ratio is null then -- to make sure it was not calculated previously
2277 l_fte_ratio := l_hours_per_week/l_fte_hrs;
2278 end if;
2279
2280 if g_data_ret_type <> 'TYPE4' then
2281 begin
2282 l_hours_per_week := to_char(to_number(l_hours_per_week),'fm99.00');
2283 l_check_if_num := l_hours_per_week;
2284 exception when others then
2285 hr_utility.set_location('Hours Per Week has invalid characters',20);
2286 populate_run_msg(p_assactid,'Hours Per Week has invalid characters');
2287 end;
2288
2289 -- 4740
2290
2291 -- Bug#12599238
2292 -- New requirement as mandated for Data required for Categories of Staff
2293
2294 -- if p_staff_cat <> 4 and
2295
2296 If l_contract_type is not null and l_hours_per_week is null then
2297
2298 -- Bug#12599238
2299 l_arch := false;
2300 hr_utility.set_location('Hours worked per week is missing',20);
2301 populate_run_msg(p_assactid,'Hours worked per week is missing');
2302 end if;
2303
2304 hr_utility.set_location('FTE Hours '|| l_fte_hrs,20);
2305 begin
2306
2307 -- 4750 /* Added the Code.Bug#12586059 */
2308 l_fte_hrs := to_char(to_number(l_fte_hrs),'fm99.00');
2309 l_check_if_num := l_fte_hrs;
2310 exception when others then
2311 l_error_flag := 'Y';
2312 l_arch := false;
2313 hr_utility.set_location('FTE Hours has invalid characters '|| l_fte_hrs,20);
2314 populate_run_msg(p_assactid,'FTE Hours has invalid characters');
2315 end;
2316
2317
2318 -- 4760
2319
2320 -- Bug#12599238
2321 -- New requirement as mandated for Data required for Categories of Staff
2322
2323 -- if p_staff_cat <> 4 and
2324 if l_fte_hrs is null then
2325
2326 -- Bug#12599238
2327
2328 l_arch := false;
2329 hr_utility.set_location('FTE Hours per week is missing',20);
2330 populate_run_msg(p_assactid,'FTE Hours per week is missing');
2331 end if;
2332
2333 -- 4765
2334 if not(l_fte_hrs between 24 and 40) then
2335 l_arch := false;
2336 hr_utility.set_location('FTE hours should be at least 24 and no greater than 40',20);
2337 populate_run_msg(p_assactid,'FTE hours should be at least 24 and no greater than 40');
2338 end if;
2339
2340 -- 4770
2341 begin
2342 l_weeks_per_yr := to_char(to_number(l_weeks_per_yr),'fm99');
2343 l_check_if_num := l_weeks_per_yr;
2344 exception when others then
2345 l_arch := false;
2346 hr_utility.set_location('Weeks per year has invalid characters',20);
2347 populate_run_msg(p_assactid,'Weeks per year has invalid characters');
2348 end;
2349
2350 -- 4780
2351
2352 -- Bug#12599238
2353 -- New requirement as mandated for Data required for Categories of Staff
2354 -- if p_staff_cat <> 4 and
2355 if l_weeks_per_yr is null then
2356
2357 -- Bug#12599238
2358 l_arch := false;
2359 hr_utility.set_location('Weeks per year is missing',20);
2360 populate_run_msg(p_assactid,'Weeks per year is missing');
2361 end if;
2362
2363 if l_error_flag <>'Y' then
2364 if l_fte_hrs <> 0 and p_contract_type IN ('PRM','FXT','TMP') and l_fte_ratio > 1.5 then
2365 l_arch := false;
2366 hr_utility.set_location('FTE Error',20);
2367 populate_run_msg(p_assactid,'The same person has a total Full Time Equivalent ratio greater than 1.5');
2368 end if;
2369
2370 if l_fte_hrs <> 0 and p_contract_type IN ('PRM','FXT','TMP') and l_fte_ratio between 1.2 and 1.5 then
2371 hr_utility.set_location('FTE Warning',20);
2372 populate_run_msg(p_assactid,'Please Check: The same person has a total Full Time Equivalent ratio greater than 1.2 and less than or equal to 1.5','W');
2373 end if;
2374 end if;
2375 end if ; -- g_data_ret_type <> 'TYPE4'
2376
2377
2378 p_hrs_rec.action_info_category := 'GB_SWF_HOURS_DETAILS';
2379 p_hrs_rec.person_id := l_person_id;
2380 p_hrs_rec.assignment_id := p_assignment_id;
2381 p_hrs_rec.effective_date := sysdate;
2382 p_hrs_rec.act_info1 := l_hours_per_week;
2383 p_hrs_rec.act_info2 := l_fte_hrs;
2384 p_hrs_rec.act_info3 := l_weeks_per_yr;
2385 p_hrs_rec.act_info4 := p_person_id;
2386 p_hrs_rec.act_info5 := p_contract_type;
2387 p_hrs_rec.act_info6 := p_contract_end_date;
2388 p_hrs_rec.act_info7 := l_fte_ratio;
2389
2390 return l_arch;
2391 exception when others then
2392 hr_utility.trace(sqlerrm);
2393 hr_utility.set_location('leaving with error: '||l_proc,7777);
2394 raise;
2395 end fetch_hours_details;
2396
2397 ---------------------------------function get_person_category-------------------
2398 --- Functuon fetches the person category.This is not with respect to the person.
2399 --- Its computed based on the assignment.This value is uesed in fetch_hours_details
2400 --- to compute hours data.
2401 --- The actual person category based on the ranking order will be determined in the
2402 --- extract process from the archived data.The final determined value will be used to
2403 --- conditionally display qualifications and absence data.
2404 -- The function will return
2405 -- 1 if Regular Teacher , 2 if Agency Teacher
2406 -- 3 if Teaching Assistant, 4 if Other Support Staff
2407
2408 function get_person_category(p_contract_type in varchar2,
2409 p_start_date in date,
2410 p_end_date in date,
2411 p_post in varchar2,
2412 p_role in addl_role_tab) return varchar2 is
2413
2414 l_proc constant varchar2(50) := g_package || ' get_person_category';
2415 l_person_category varchar2(30);
2416 begin
2417 hr_utility.set_location('Entering: '||l_proc,10);
2418
2419 -- Incorporated New logic for person category
2420 if ( (p_end_date-p_start_date) >= 28) then
2421 if (p_post = 'TAS') then
2422 l_person_category := 3; --'Teaching Assistant';
2423 elsif (p_post in ('SUP','AVT')) then
2424 l_person_category := 4; --'Other Support Staff';
2425 else
2426 if (p_contract_type in ('PRM','FXT','TMP')) then
2427 l_person_category := 1; --'Regular Teacher';
2428 else
2429 l_person_category := 2; --'Agency Teacher';
2430 end if;
2431 end if;
2432 end if;
2433
2434 hr_utility.set_location('Entering: '||l_proc,10);
2435 return l_person_category;
2436 end get_person_category;
2437 ---
2438 ----------------------------function fetch_absence_details----------------------
2439 ---
2440 function fetch_absence_details(p_assactid in number,
2441 p_person_id in number,
2442 p_estab_no in number,
2443 p_abs_tab out nocopy abs_details_tab)return boolean is
2444
2445 l_proc constant varchar2(50):= g_package||'fetch_absence_details';
2446
2447 cursor get_abs_source is
2448 select pcv_information1
2449 from pqp_configuration_values pcv
2450 where pcv.pcv_information_category = 'PQP_GB_SWF_ABSENCE_CODE'
2451 and pcv.business_group_id = g_business_group_id;
2452
2453 --
2454 l_abs_source pqp_configuration_values.pcv_information1%type;
2455 l_date_end_missing varchar2(1) := 'N';
2456 l_last_before_first varchar2(1) := 'N';
2457 l_last_after_census varchar2(1) := 'N';
2458 l_first_day_of_abs varchar2(1) := 'N';
2459 l_last_day_of_abs varchar2(1) := 'N';
2460 l_last_day_of_abs_41_days varchar2(1) := 'N';
2461 l_no_of_last_day_missing number := 0;
2462 l_wrking_days_lost_missing varchar2(1) := 'N';
2463 -- Bug#12586059
2464 l_day_of_diff_nodes_overlap varchar2(1) := 'N';
2465 -- Bug#12586059
2466 l_arch boolean := true;
2467
2468 begin
2469 hr_utility.set_location('Entering: '||l_proc,10);
2470 open get_abs_source;
2471 fetch get_abs_source into l_abs_source;
2472 close get_abs_source;
2473
2474 if l_abs_source = 'ABSENCE_CATEGORY' then
2475 begin
2476 select paat.person_id,
2477 paat.date_start,
2478 to_char(paat.date_start,'YYYY-MM-DD'),
2479 paat.date_end,
2480 to_char(paat.date_end,'YYYY-MM-DD'),
2481 to_char(paat.absence_days,'fm999.0'),
2482 pcv.pcv_information5 absence_category,
2483 p_estab_no
2484 bulk collect into p_abs_tab
2485 from per_absence_attendances paat,
2486 per_absence_attendance_types paatt,
2487 pqp_configuration_values pcv
2488 where paat.absence_attendance_type_id = paatt.absence_attendance_type_id
2489 and paat.person_id = p_person_id
2490 and pcv.pcv_information_category = 'PQP_GB_SWF_ABSENCE_CODE'
2491 and pcv_information1 = 'ABSENCE_CATEGORY'
2492 and pcv.business_group_id = g_business_group_id
2493 and pcv_information3 = paatt.absence_category
2494 and (paat.date_end between g_cont_data_st_date and g_cont_data_end_date or
2495 paat.date_start between g_cont_data_st_date and g_cont_data_end_date)
2496 order by paat.date_start asc;
2497
2498 exception
2499 when others then
2500 hr_utility.trace(sqlerrm);
2501 hr_utility.set_location('leaving with error: '||l_proc,7777);
2502 raise;
2503 end;
2504 elsif l_abs_source = 'ABSENCE_TYPE' then
2505 begin
2506 select paat.person_id,
2507 paat.date_start,
2508 to_char(paat.date_start,'YYYY-MM-DD'),
2509 paat.date_end,
2510 to_char(paat.date_end,'YYYY-MM-DD'),
2511 to_char(paat.absence_days,'fm999.0'),
2512 pcv.pcv_information5 absence_type,
2513 p_estab_no
2514 bulk collect into p_abs_tab
2515 from per_absence_attendances paat,
2516 pqp_configuration_values pcv
2517 where paat.person_id = p_person_id
2518 and pcv.pcv_information_category = 'PQP_GB_SWF_ABSENCE_CODE'
2519 and pcv_information1 = 'ABSENCE_TYPE'
2520 and pcv.business_group_id = g_business_group_id
2521 and pcv_information4 = paat.absence_attendance_type_id
2522 and (paat.date_end between g_cont_data_st_date and g_cont_data_end_date or
2523 paat.date_start between g_cont_data_st_date and g_cont_data_end_date)
2524 order by paat.date_start asc;
2525
2526 exception
2527 when others then
2528 hr_utility.trace(sqlerrm);
2529 hr_utility.set_location('leaving with error: '||l_proc,7777);
2530 raise;
2531 end;
2532 elsif l_abs_source = 'ABSENCE_REASON' then
2533 begin
2534 select paat.person_id,
2535 paat.date_start,
2536 to_char(paat.date_start,'YYYY-MM-DD'),
2537 paat.date_end,
2538 to_char(paat.date_end,'YYYY-MM-DD'),
2539 to_char(paat.absence_days,'fm999.0'),
2540 pcv.pcv_information5 absence_reason,
2541 p_estab_no
2542 bulk collect into p_abs_tab
2543 from per_absence_attendances paat,
2544 per_abs_attendance_reasons paatr,
2545 pqp_configuration_values pcv
2546 where paatr.abs_attendance_reason_id = paat.abs_attendance_reason_id
2547 and paat.person_id = p_person_id
2548 and pcv.pcv_information_category = 'PQP_GB_SWF_ABSENCE_CODE'
2549 and pcv_information1 = 'ABSENCE_REASON'
2550 and pcv.business_group_id = g_business_group_id
2551 and pcv_information3 = paatr.name
2552 and (paat.date_end between g_cont_data_st_date and g_cont_data_end_date or
2553 paat.date_start between g_cont_data_st_date and g_cont_data_end_date)
2554 order by paat.date_start asc;
2555
2556 exception
2557 when others then
2558 hr_utility.trace(sqlerrm);
2559 hr_utility.set_location('leaving with error: '||l_proc,7777);
2560 raise;
2561 end;
2562 end if;
2563
2564 if p_abs_tab.count >0 then
2565 for i in p_abs_tab.first .. p_abs_tab.last loop
2566 if p_abs_tab(i).date_end is null then
2567 l_date_end_missing := 'Y';
2568 l_no_of_last_day_missing := l_no_of_last_day_missing +1;
2569 end if;
2570
2571 if p_abs_tab(i).date_end < p_abs_tab(i).date_start then
2572 l_last_before_first := 'Y';
2573 end if;
2574
2575 if p_abs_tab(i).date_end is not null and p_abs_tab(i).days_lost is NULL and p_abs_tab(i).absence_category = 'SIC' then
2576 l_wrking_days_lost_missing:= 'Y';
2577 end if;
2578
2579 if p_abs_tab(i).date_end > g_census_day then
2580 l_last_after_census := 'Y';
2581 end if;
2582
2583 if p_abs_tab(i).date_start < g_cont_data_st_date then
2584 l_first_day_of_abs := 'Y';
2585 end if;
2586
2587 if p_abs_tab(i).date_end > g_cont_data_end_date then
2588 l_last_day_of_abs := 'Y';
2589 end if;
2590
2591 if p_abs_tab(i).date_end - g_census_day > 41 then
2592 l_last_day_of_abs_41_days := 'Y';
2593 end if;
2594
2595 -- Bug#12586059
2596 -- Bug#12710229
2597
2598 if( p_abs_tab.count <> i) THEN
2599
2600 -- Bug#12710229
2601
2602 if p_abs_tab(i).date_end >= p_abs_tab(i+1).date_start then
2603 l_day_of_diff_nodes_overlap := 'Y';
2604 end if;
2605
2606 end if;
2607 -- Bug#12586059
2608
2609
2610 end loop;
2611
2612 -- 4920
2613 if l_first_day_of_abs = 'Y' then
2614 l_arch := false;
2615 hr_utility.set_location('First Day of absence must be in the academic year before Census day',20);
2616 populate_run_msg(p_assactid,'First Day of absence must be in the academic year before Census day');
2617 end if;
2618
2619 -- 4936
2620 if l_last_day_of_abs = 'Y' then
2621 l_arch := false;
2622 hr_utility.set_location('Last Day of absence must be in the current or preceding academic year',20);
2623 populate_run_msg(p_assactid,'Last Day of absence must be in the current or preceding academic year');
2624 end if;
2625
2626 --4945Q
2627 if l_last_day_of_abs_41_days = 'Y' then
2628 hr_utility.set_location('Please check: Last Day of absence is not expected to be after Census day',20);
2629 populate_run_msg(p_assactid,'Last Day of absence must be in the current or preceding academic year Census day','W');
2630 end if;
2631
2632 if l_date_end_missing = 'Y' then
2633 hr_utility.set_location('Last Day of absence is missing - please check absence is ongoing',20);
2634 populate_run_msg(p_assactid,'Last Day of absence is missing - please check absence is ongoing','W');
2635 end if;
2636
2637 if l_last_before_first = 'Y' then
2638 hr_utility.set_location('Last Day of absence cannot be before First Day of absence',20);
2639 populate_run_msg(p_assactid,'Last Day of absence cannot be before First Day of absence','W');
2640 end if;
2641
2642 -- 4945Q
2643 if l_last_after_census = 'Y' then
2644 hr_utility.set_location('Please check: Last Day of absence should not be after the Census Day',20);
2645 populate_run_msg(p_assactid,'Please check: Last Day of absence should not be after the Census Day','W');
2646 end if;
2647
2648 -- 4950Q
2649 if l_no_of_last_day_missing > 1 then
2650 hr_utility.set_location('Please check - more than one absence record without an end date',20);
2651 populate_run_msg(p_assactid,'Please check - more than one absence record without an end date','W');
2652 end if;
2653
2654 if l_wrking_days_lost_missing = 'Y' then
2655 l_arch := false;
2656 hr_utility.set_location('Where Last Day of a sickness absence is provided then the number of Working Days Lost must also be provided',20);
2657 populate_run_msg(p_assactid,'Where Last Day of a sickness absence is provided then the number of Working Days Lost must also be provided');
2658 end if;
2659
2660 -- Bug#12586059
2661 -- 4960Q
2662 if l_day_of_diff_nodes_overlap = 'Y' then
2663 l_arch := false;
2664 hr_utility.set_location('Please check - more than one absence record with Absence Category of Sickness covering the same date(s)',20);
2665 populate_run_msg(p_assactid,'Please check - more than one absence record with Absence Category of Sickness covering the same date(s)');
2666 end if;
2667 -- Bug#12586059
2668
2669 end if;
2670
2671 hr_utility.set_location('Leaving: '||l_proc,99);
2672
2673 return l_arch;
2674 exception when others then
2675 hr_utility.set_location('Leaving with error: '||l_proc,9999);
2676 raise;
2677 end fetch_absence_details;
2678 ------------------------------function fetch_contract_details--------------------------------------
2679 --Fetches contract details
2680 --Contract/Agreement Type , Start Date, End Date ,Post,Date of Arrival in School,Daily Rate,
2681 --Destination,Origin,LA or School Level,Establishment
2682 function fetch_contract_details ( p_assactid in number,
2683 p_effective_date in date,
2684 p_person_id in number,
2685 p_hlta_status in varchar2,
2686 p_estab_no in number,
2687 p_contract_rec out nocopy act_info_rec,
2688 p_role_tab out nocopy addl_role_tab) return boolean is
2689
2690 l_proc constant varchar2(50):= g_package||'fetch_contract_details';
2691 -- if the
2692 cursor get_assignment_details is
2693 select paa.assignment_id assignment_id,
2694 paa.assignment_number assignment_number,
2695 paa.employment_category contract_agg_type,
2696 paa.employee_category employee_cat,
2697 paa.employment_category assignment_cat
2698 from pay_assignment_actions act,
2699 per_all_assignments_f paa
2700 where act.assignment_action_id = p_assactid
2701 and act.assignment_id = paa.assignment_id
2702 and p_effective_date between paa.effective_start_date and paa.effective_end_date;
2703
2704 cursor get_assignment_dates(p_assignment_id number) is
2705 select min(paa.effective_start_date) contract_st_date,
2706 max(paa.effective_end_date) contract_end_date,
2707 to_char(min(paa.effective_start_date),'YYYY-MM-DD') contract_st_date_dcsf,
2708 decode(to_char(max(paa.effective_end_date) ,'YYYY-MM-DD'),'4712-12-31',null,
2709 to_char(max(paa.effective_end_date) ,'YYYY-MM-DD'))contract_end_date_dcsf,
2710 to_char(min(paa.effective_start_date) ,'YYYY-MM-DD') date_of_arrival_dcsf
2711 from per_all_assignments_f paa,
2712 per_assignment_status_types pas
2713 where paa.assignment_status_type_id = pas.assignment_status_type_id
2714 and pas.per_system_status = 'ACTIVE_ASSIGN'
2715 and paa.assignment_id = p_assignment_id;
2716
2717 cursor get_asg_contract_details is
2718 select paa.assignment_id assignment_id,
2719 paa.assignment_number assignment_number,
2720 att.contract_type contract_agg_type,
2721 paa.employee_category employee_cat,
2722 paa.employment_category assignment_cat
2723 from per_all_assignments_f paa,
2724 pay_assignment_actions act,
2725 pqp_assignment_attributes_f att
2726 where act.assignment_action_id = p_assactid
2727 and act.assignment_id = att.assignment_id
2728 and paa.assignment_id = att.assignment_id
2729 and p_effective_date between att.effective_start_date and att.effective_end_date
2730 and p_effective_date between paa.effective_start_date and paa.effective_end_date;
2731
2732
2733 cursor get_contract_dates(p_assignment_id number) is
2734 select min(att.effective_start_date) contract_st_date,
2735 max(att.effective_end_date) contract_end_date,
2736 to_char(min(att.effective_start_date) ,'YYYY-MM-DD') contract_st_date_dcsf,
2737 decode(to_char(max(att.effective_end_date) ,'YYYY-MM-DD'),'4712-12-31',null,
2738 to_char(max(att.effective_end_date) ,'YYYY-MM-DD'))contract_end_date_dcsf,
2739 to_char(min(paa.effective_start_date) ,'YYYY-MM-DD') date_of_arrival_dcsf
2740 from pqp_assignment_attributes_f att,
2741 per_all_assignments_f paa
2742 where att.assignment_id = p_assignment_id
2743 and paa.assignment_id = att.assignment_id;
2744
2745 cursor get_date_of_arrival_src is
2746 select pcv_information1, pcv_information2
2747 from pqp_configuration_values
2748 where business_group_id = g_business_group_id
2749 and pcv_information_category = 'PQP_GB_SWF_CNTRT_ARRIVAL_DATE';
2750
2751 cursor get_post(p_emp_or_asgcat in varchar2) is
2752 select pcv_information4
2753 from pqp_configuration_values pcv
2754 where pcv.pcv_information_category = 'PQP_GB_SWF_POST_MAPPING'
2755 and pcv_information2 = p_emp_or_asgcat
2756 and pcv.business_group_id = g_business_group_id;
2757
2758 cursor get_origin(l_origin in varchar2) is
2759 select pcv_information3
2760 from pqp_configuration_values pcv
2761 where pcv.pcv_information_category = 'PQP_GB_SWF_ORIGIN_MAPPING'
2762 and pcv_information2 = l_origin
2763 and pcv.business_group_id = g_business_group_id;
2764
2765 cursor get_destination(l_destination in varchar2) is
2766 select pcv_information3
2767 from pqp_configuration_values pcv
2768 where pcv.pcv_information_category = 'PQP_GB_SWF_DESTINATION_MAPPING'
2769 and pcv_information2 = l_destination
2770 and pcv.business_group_id = g_business_group_id;
2771
2772 cursor get_daily_rate(p_assignment_id in number) is
2773 select aei_information1
2774 from per_assignment_extra_info
2775 where aei_information_category = 'PQP_SCHOOL_WORKFORCE_CENSUS'
2776 and assignment_id = p_assignment_id;
2777
2778 cursor get_work_inc_type(p_person_id in number) is --- Check what are the valid values
2779 select decode(pei_information5,'CENTRAL_STAFF','L','SCHOOL_STAFF','S',null) workforce_inc_typ --- which can be archived
2780 from per_people_extra_info pei
2781 where pei.information_type = 'PQP_SCHOOL_WORKFORCE_CENSUS'
2782 and pei.person_id = p_person_id;
2783
2784 cursor get_pqp_contract_type_dcsf(p_pqp_cont_type in pqp_assignment_attributes_f.contract_type%type ) is
2785 select pcv_information5
2786 from pqp_configuration_values pcv
2787 where pcv.pcv_information_category = 'PQP_GB_SWF_CONTRACT_TYPE'
2788 and pcv_information4 = p_pqp_cont_type
2789 and pcv.business_group_id = g_business_group_id;
2790
2791 cursor get_asg_contract_type_dcsf(p_cont_type in per_all_assignments_f.employment_category%type ) is
2792 select pcv_information5
2793 from pqp_configuration_values pcv
2794 where pcv.pcv_information_category = 'PQP_GB_SWF_CONTRACT_TYPE'
2795 and pcv_information3 = p_cont_type
2796 and pcv.business_group_id = g_business_group_id;
2797
2798
2799 l_asg_rec get_assignment_details%rowtype;
2800 l_asg_dates_rec get_assignment_dates%rowtype;
2801 l_post hr_lookups.lookup_code%type;
2802 l_post_emp_cat hr_lookups.lookup_code%type;
2803 l_post_asg_cat hr_lookups.lookup_code%type;
2804 l_date_of_arrival_src pqp_configuration_values.pcv_information1%type;
2805 l_date_of_arrival date;
2806 l_daily_rate varchar2(150);
2807 l_origin hr_lookups.lookup_code%type;
2808 l_dcsf_origin hr_lookups.lookup_code%type;
2809 l_destination hr_lookups.lookup_code%type;
2810 l_dcsf_destination hr_lookups.lookup_code%type;
2811 l_la_or_school_level varchar2(150);
2812 l_establishment number := p_estab_no;
2813 l_contract_agg_type hr_lookups.lookup_code%type;
2814 l_user_formula_name ff_formulas_f.formula_name%type;
2815 l_arch_role boolean;
2816 l_person_category varchar2(30);
2817 l_arch boolean := true;
2818 l_valid_flag varchar2(1);
2819 l_date_of_arrival_dcsf varchar2(10);
2820 l_role_count number;
2821 begin
2822 hr_utility.set_location('Entering '|| l_proc, 10);
2823 hr_utility.set_location('Parameters :', 20);
2824 hr_utility.set_location('p_effective_date :'|| p_effective_date, 20);
2825 hr_utility.set_location('p_person_id :'|| p_person_id, 20);
2826 hr_utility.set_location('p_hlta_status :'|| p_hlta_status, 20);
2827
2828 if g_pick_from_asg is null then
2829 fnd_file.put_line(fnd_file.log,'Contract Details - Contract Type Configuration is not set.Please configure and proceed');
2830 hr_utility.raise_error;
2831 end if;
2832
2833 if p_effective_date is null then
2834 fnd_file.put_line(fnd_file.log,'Contract Details - Contract effective date could not be determined.');
2835 hr_utility.raise_error;
2836 end if;
2837
2838 open get_date_of_arrival_src;
2839 fetch get_date_of_arrival_src into l_date_of_arrival_src,l_user_formula_name;
2840 close get_date_of_arrival_src;
2841
2842 if g_pick_from_asg = 'Y' then
2843 open get_assignment_details;
2844 fetch get_assignment_details into l_asg_rec;
2845 close get_assignment_details;
2846
2847 open get_assignment_dates(l_asg_rec.assignment_id);
2848 fetch get_assignment_dates into l_asg_dates_rec;
2849 close get_assignment_dates;
2850
2851 open get_asg_contract_type_dcsf(l_asg_rec.contract_agg_type);
2852 fetch get_asg_contract_type_dcsf into l_contract_agg_type;
2853 close get_asg_contract_type_dcsf;
2854
2855 if l_date_of_arrival_src = 'CAL' then
2856 begin
2857 l_date_of_arrival_dcsf := run_user_formula(l_user_formula_name,l_asg_rec.assignment_id,g_census_day,g_business_group_id,l_asg_rec.assignment_number);
2858 hr_utility.set_location('Formula Result Value '||l_date_of_arrival_dcsf,20);
2859 exception when others then
2860 populate_run_msg(p_assactid,'Error while executing formula id '|| l_user_formula_name||'. Please refer Log file for details' );
2861 fnd_file.put_line(fnd_file.log,'Error while executing formula id '|| l_user_formula_name);
2862 fnd_file.put_line(fnd_file.log,sqlerrm);
2863 end;
2864 else
2865 l_date_of_arrival_dcsf := l_asg_dates_rec.date_of_arrival_dcsf;
2866 end if;
2867
2868 else
2869 open get_asg_contract_details;
2870 fetch get_asg_contract_details into l_asg_rec;
2871 close get_asg_contract_details;
2872
2873 open get_contract_dates(l_asg_rec.assignment_id);
2874 fetch get_contract_dates into l_asg_dates_rec;
2875 close get_contract_dates;
2876
2877 open get_pqp_contract_type_dcsf(l_asg_rec.contract_agg_type);
2878 fetch get_pqp_contract_type_dcsf into l_contract_agg_type;
2879 close get_pqp_contract_type_dcsf;
2880
2881 if l_date_of_arrival_src = 'CAL' then
2882 begin
2883 l_date_of_arrival_dcsf :=run_user_formula(l_user_formula_name,l_asg_rec.assignment_id,g_census_day,g_business_group_id,l_asg_rec.assignment_number);
2884 hr_utility.set_location('Formula Result Value '||l_date_of_arrival_dcsf,20);
2885 exception when others then
2886 populate_run_msg(p_assactid,'Error while executing formula '|| l_user_formula_name||'. Please refer Log file for details' );
2887 fnd_file.put_line(fnd_file.log,'Error while executing formula '|| l_user_formula_name);
2888 fnd_file.put_line(fnd_file.log,sqlerrm);
2889 end;
2890 else
2891 l_date_of_arrival_dcsf := l_asg_dates_rec.date_of_arrival_dcsf;
2892 end if;
2893
2894 end if;
2895
2896 if g_cont_post_src in ('JOB','GRD','POS') then
2897 begin
2898 execute immediate g_cont_post_sql_str into l_post using g_business_group_id,p_effective_date, l_asg_rec.assignment_id;
2899 exception when others then
2900 hr_utility.set_location('Post could not be fetched as of' || p_effective_date ||'for Assignment number :'||l_asg_rec.assignment_number,9999);
2901 end;
2902 elsif g_cont_post_src = 'EMP_CAT' then
2903 open get_post(l_asg_rec.assignment_cat);
2904 fetch get_post into l_post;
2905 close get_post;
2906 elsif g_cont_post_src = 'EMPLOYEE_CATG' then
2907 open get_post(l_asg_rec.employee_cat);
2908 fetch get_post into l_post;
2909 close get_post;
2910 end if;
2911
2912 open get_daily_rate(l_asg_rec.assignment_id);
2913 fetch get_daily_rate into l_daily_rate;
2914 close get_daily_rate;
2915
2916 if g_origin_dff = 'PER_ASSIGNMENTS' then
2917 begin
2918 execute immediate g_origin_sql_str into l_origin using l_asg_rec.assignment_id, p_effective_date;
2919 exception when others then
2920 hr_utility.set_location('Origin could not be fetched as of ' || p_effective_date ||'for Assignment Number :'||l_asg_rec.assignment_number,8888);
2921 end;
2922 elsif g_origin_dff = 'PER_PEOPLE' then
2923 begin
2924 execute immediate g_origin_sql_str into l_origin using p_person_id, p_effective_date;
2925 exception when others then
2926 hr_utility.set_location('Origin could not be fetched as of ' || p_effective_date ||'for Assignment Number :'||l_asg_rec.assignment_number,9999);
2927 end;
2928 end if;
2929
2930 open get_origin(l_origin);
2931 fetch get_origin into l_dcsf_origin;
2932 close get_origin;
2933
2934 if g_destination_dff = 'PER_ASSIGNMENTS' then
2935 begin
2936 execute immediate g_destination_sql_str into l_destination using l_asg_rec.assignment_id, p_effective_date;
2937 exception when others then
2938 hr_utility.set_location('Destination could not be fetched as of ' || p_effective_date ||'for Assignment Number :'||l_asg_rec.assignment_number,8888);
2939 end;
2940 elsif upper(g_destination_dff) like 'TERM%' then -- get the exact value
2941 begin
2942 execute immediate g_destination_sql_str into l_destination using p_person_id;
2943 exception when others then
2944 hr_utility.set_location('Destination could not be fetched as of ' || p_effective_date ||'for Assignment Number :'||l_asg_rec.assignment_number,9999);
2945 end;
2946 end if;
2947
2948 open get_destination(l_destination);
2949 fetch get_destination into l_dcsf_destination;
2950 close get_destination;
2951
2952 open get_work_inc_type(p_person_id);
2953 fetch get_work_inc_type into l_la_or_school_level;
2954 close get_work_inc_type;
2955
2956
2957 l_arch_role := fetch_role_details(p_assactid,p_effective_date,p_person_id,l_asg_rec.assignment_id,p_role_tab);
2958
2959 l_person_category := get_person_category(l_contract_agg_type,l_asg_dates_rec.contract_st_date,l_asg_dates_rec.contract_end_date,l_post,p_role_tab);
2960
2961 if l_person_category is null then
2962 l_arch := false;
2963 hr_utility.set_location('Staff Category Could not be determined',10);
2964 populate_run_msg(p_assactid,'Staff Category Could not be determined.This could be becacuse of incorrect Post or Role or Contract Type.');
2965 end if;
2966 -- 4285
2967 if l_establishment is not null then
2968 if not(
2969 l_establishment between 1000 and 1099
2970 or l_establishment between 1800 and 1899
2971 or l_establishment between 2000 and 3999
2972 or l_establishment between 5200 and 5299
2973 or l_establishment between 5499 and 5900
2974 or l_establishment between 4000 and 4999
2975 or l_establishment between 5400 and 5499
2976 or l_establishment between 5900 and 5949
2977 or l_establishment between 6900 and 6924) then
2978 l_arch := false;
2979 hr_utility.set_location('Estab Number in Contract module is not valid',10);
2980 populate_run_msg(p_assactid,'Estab Number in Contract module is not valid');
2981 end if;
2982 end if;
2983
2984 -- 4310
2985 if l_contract_agg_type is not null then
2986 begin
2987 select 'Y'
2988 into l_valid_flag
2989 from dual
2990 where exists(select lookup_code
2991 from hr_lookups hl
2992 where hl.lookup_type = 'PQP_GB_SWF_CNTRCT_AGRMNT_TYPES'
2993 and hl.enabled_flag = 'Y'
2994 and hl.lookup_code = l_contract_agg_type);
2995 exception
2996 when OTHERS then
2997 l_arch := false;
2998 populate_run_msg (p_assactid, 'Contract / Agreement Type is invalid');
2999 hr_utility.set_location ('Contract / Agreement Type is invalid', 10);
3000 end;
3001
3002 if length(l_contract_agg_type) <> 3 then
3003 l_arch := false;
3004 hr_utility.set_location('Contract / Agreement Type is invalid',10);
3005 populate_run_msg(p_assactid,'Contract / Agreement Type is invalid');
3006 end if;
3007 end if;
3008
3009 -- 4350
3010 if l_asg_dates_rec.contract_st_date is null then
3011 l_arch := false;
3012 hr_utility.set_location('Start Date is missing',10);
3013 populate_run_msg(p_assactid,'Start Date is missing');
3014 end if;
3015
3016 -- 4355
3017 if g_census_day < l_asg_dates_rec.contract_st_date then
3018 l_arch := false;
3019 hr_utility.set_location('Contract Start Date can not be in the future',10);
3020 populate_run_msg(p_assactid,'Contract Start Date can not be in the future');
3021 end if;
3022
3023 -- 4357
3024 if months_between(trunc(g_census_day),l_asg_dates_rec.contract_st_date)/12 > 50 then
3025 l_arch := false;
3026 hr_utility.set_location('Contract / Agreement Start Date more than 50 years ago',10);
3027 populate_run_msg(p_assactid,'Contract / Agreement Start Date more than 50 years ago');
3028 end if;
3029
3030 -- 4360
3031
3032 if l_asg_dates_rec.contract_end_date <> hr_general.end_of_time then
3033 if not(l_asg_dates_rec.contract_st_date between to_date('01-09-'||(g_census_year-1),'DD-MM-YYYY') and to_date('31-08-'||(g_census_year),'DD-MM-YYYY'))
3034 and not(l_asg_dates_rec.contract_end_date between to_date('01-09-'||(g_census_year-1),'DD-MM-YYYY') and to_date('31-08-'||(g_census_year),'DD-MM-YYYY'))
3035 and l_asg_dates_rec.contract_end_date < g_census_day then
3036 l_arch := false;
3037 hr_utility.set_location('Contract end date error',20);
3038 populate_run_msg(p_assactid,'Contract has invalid End date for this Census');
3039 end if;
3040 end if;
3041
3042 -- 4361, 4362 Deleted
3043
3044 -- 4370 already handled in select
3045
3046 -- 4375
3047 /*if g_census_term <> 'SPRING' then -- Added as PRM and other contract types can exist in the system without end date
3048 if l_contract_agg_type <> 'FXT' and (l_asg_dates_rec.contract_end_date > g_census_day
3049 or months_between(l_asg_dates_rec.contract_end_date ,l_asg_dates_rec.contract_st_date) < 1) then
3050 l_arch := false;
3051 hr_utility.set_location('Contract / Agreement end date must be at least a month after the start date, and on or prior to Census Day, for this type of contract or agreement',20);
3052 populate_run_msg(p_assactid,'Contract / Agreement end date must be at least a month after the start date, and on or prior to Census Day, for this type of contract or agreement');
3053 end if;
3054 end if;*/
3055
3056 -- Bug#12586059
3057 -- 4376
3058 -- If Contract Type is not 'PRM' and the Contract End Date is not provided then the Contract
3059 -- Start Date must be at least 27 days prior to the Census Reference Date
3060
3061 if l_contract_agg_type <> 'PRM' and l_asg_dates_rec.contract_end_date is null
3062 and (g_census_day - l_asg_dates_rec.contract_st_date < 27) then
3063 l_arch := false;
3064 hr_utility.set_location('Contract Start Date must be at least 27 days prior to the Census Reference Date',20);
3065 populate_run_msg(p_assactid,'Contract Start Date must be at least 27 days prior to the Census Reference Date');
3066 end if;
3067
3068 -- Bug#12586059
3069
3070 -- 4380
3071 if l_contract_agg_type = 'FXT' and l_asg_dates_rec.contract_end_date_dcsf is null then
3072 l_arch := false;
3073 hr_utility.set_location('Contract / Agreement Type is Fixed Term therefore End Date must be specified',20);
3074 populate_run_msg(p_assactid,'Contract / Agreement Type is Fixed Term therefore End Date must be specified');
3075 end if;
3076
3077 -- 4385
3078 if (l_person_category = 1 or l_person_category = 2) and l_asg_dates_rec.contract_end_date_dcsf is not null and l_asg_dates_rec.contract_end_date < g_census_day
3079 and l_dcsf_destination is null then
3080
3081 l_arch := false;
3082 hr_utility.set_location('Destination code must be provided for completed contracts',20);
3083 populate_run_msg(p_assactid,'Destination code must be provided for completed contracts');
3084 end if;
3085
3086 -- 4390Q
3087 if l_dcsf_destination is not null and l_asg_dates_rec.contract_end_date_dcsf is null then
3088 l_arch := false; -- Bug 12906657
3089 hr_utility.set_location('Please check: Destination code has been provided therefore contract End Date must be specified ',20);
3090 populate_run_msg(p_assactid,'Please check: Destination code has been provided therefore contract End Date must be specified ','W');
3091 end if;
3092
3093 -- 4400
3094 if l_asg_dates_rec.contract_st_date > l_asg_dates_rec.contract_end_date then
3095 l_arch := false;
3096 hr_utility.set_location('Contract End Date cannot be before contract Start Date',20);
3097 populate_run_msg(p_assactid,'Contract End Date cannot be before contract Start Date');
3098 end if;
3099
3100 -- 4410
3101 if l_post is null then
3102 l_arch := false;
3103 hr_utility.set_location('Post is missing',20);
3104 populate_run_msg(p_assactid,'Post is missing');
3105 end if;
3106
3107 -- 4700/4710
3108 l_role_count := 0;
3109
3110 for i in p_role_tab.first .. p_role_tab.last loop
3111 hr_utility.set_location('Roles for this contract'||p_role_tab(i),25);
3112 if p_role_tab(i) is not null then
3113 l_role_count := l_role_count+1;
3114 end if;
3115 end loop;
3116
3117 if l_role_count = 0 then
3118 l_arch := false;
3119 hr_utility.set_location('Role Identifier is missing ',20);
3120 populate_run_msg(p_assactid,'No Role details have been supplied');
3121 end if;
3122
3123
3124 -- Bug#12586059
3125 -- 4720
3126 -- if l_post = 'SUP' then
3127 if l_post in ('SUP', 'TAS') then
3128 -- Bug#12586059
3129 l_valid_flag := 'Y';
3130 for i in p_role_tab.first .. p_role_tab.last loop
3131 if p_role_tab(i) in ('ADVT', 'ASHT', 'DPHT', 'HDTR', 'MISC', 'MUSC', 'PERI', 'SPLY', 'TCHR', 'TMIS', 'TNON', 'TPRU', 'EXHT') then /* Bug#12586059 -> added 'EXHT')*/
3132 l_valid_flag := 'N';
3133 exit;
3134 end if;
3135 end loop;
3136 if l_valid_flag = 'N' then
3137 l_arch := false;
3138 hr_utility.set_location('Post is inconsistent with Role Identifier',20);
3139 populate_run_msg(p_assactid,'Post is inconsistent with Role Identifier');
3140 end if;
3141 end if;
3142
3143 -- Bug#12586059
3144
3145 -- 4725
3146 -- Bug#12599238
3147
3148 -- if l_post = 'TAS' then
3149 if l_post = 'SUP' then
3150
3151 -- Bug#12599238
3152 l_valid_flag := 'Y';
3153 for i in p_role_tab.first .. p_role_tab.last loop
3154 if p_role_tab(i) in ('TASS','HLTA') then
3155 l_valid_flag := 'N';
3156 exit;
3157 end if;
3158 end loop;
3159 if l_valid_flag = 'N' then
3160 l_arch := false;
3161 -- Bug#12599238
3162 -- hr_utility.set_location('Role of Teaching Assistant or Higher Level Teaching Assistant should not be linked to the post of Support Staff',20);
3163 -- populate_run_msg(p_assactid,'Role of Teaching Assistant or Higher Level Teaching Assistant should not be linked to the post of Support Staff');
3164 hr_utility.set_location('Role of Teaching Assistant or Higher Level Teaching Assistant should be linked to the post of Teaching Assistant',20);
3165 populate_run_msg(p_assactid,'Role of Teaching Assistant or Higher Level Teaching Assistant should be linked to the post of Teaching Assistant');
3166 -- Bug#12599238
3167 end if;
3168 end if;
3169
3170 -- Bug#12586059
3171
3172 -- 4414
3173 if l_post = 'HDT' then
3174 l_valid_flag := 'N';
3175 for i in p_role_tab.first .. p_role_tab.last loop
3176 if p_role_tab(i) = 'HDTR' then
3177 l_valid_flag := 'Y';
3178 exit;
3179 end if;
3180 end loop;
3181 if l_valid_flag = 'N' then
3182 l_arch := false;
3183 hr_utility.set_location('Post shown as Head Teacher. One of the associated Roles must also be Head Teacher',20);
3184 populate_run_msg(p_assactid,'Post shown as Head Teacher. One of the associated Roles must also be Head Teacher');
3185 end if;
3186 end if;
3187
3188 -- 4415
3189 if l_post = 'DHT' then
3190 l_valid_flag := 'N';
3191 for i in p_role_tab.first .. p_role_tab.last loop
3192 if p_role_tab(i) = 'DPHT' then
3193 l_valid_flag := 'Y';
3194 exit;
3195 end if;
3196 end loop;
3197 if l_valid_flag = 'N' then
3198 l_arch := false;
3199 hr_utility.set_location('Post shown as Deputy Head. One of the associated Roles must also be Deputy Head',20);
3200 populate_run_msg(p_assactid,'Post shown as Deputy Head. One of the associated Roles must also be Deputy Head');
3201 end if;
3202 end if;
3203
3204 -- 4416
3205 if l_post = 'AHT' then
3206 l_valid_flag := 'N';
3207 for i in p_role_tab.first .. p_role_tab.last loop
3208 if p_role_tab(i) = 'ASHT' then
3209 l_valid_flag := 'Y';
3210 exit;
3211 end if;
3212 end loop;
3213 if l_valid_flag = 'N' then
3214 l_arch := false;
3215 hr_utility.set_location('Post shown as Deputy Head. One of the associated Roles must also be Deputy Head',20);
3216 populate_run_msg(p_assactid,'Post shown as Deputy Head. One of the associated Roles must also be Deputy Head');
3217 end if;
3218 end if;
3219
3220 --4417
3221 begin
3222 l_valid_flag := 'N';
3223 for i in p_role_tab.first .. p_role_tab.last loop
3224 if p_role_tab(i) = 'HLTA' then
3225 l_valid_flag := 'Y';
3226 exit;
3227 end if;
3228 end loop;
3229 if l_valid_flag = 'Y' and p_hlta_status = 'No' then
3230 l_arch := false;
3231 hr_utility.set_location('If role is HLTA then HLTA Status must be Yes',20);
3232 populate_run_msg(p_assactid,'If role is HLTA then HLTA Status must be Yes');
3233 end if;
3234 end;
3235
3236 -- 4420
3237
3238 if l_date_of_arrival_dcsf is not null then
3239 l_date_of_arrival := to_date(l_date_of_arrival_dcsf,'YYYY-MM-DD');
3240 end if;
3241
3242 -- 4425Q
3243 if months_between(trunc(g_census_day),l_date_of_arrival)/ 12 > 50 then
3244 hr_utility.set_location('Please check: Date of Arrival in School is more than 50 years ago',20);
3245 populate_run_msg(p_assactid,'Please check: Date of Arrival in School is more than 50 years ago','W');
3246 end if;
3247
3248
3249 -- 4430
3250 if l_person_category in (1,2,3) and l_la_or_school_level = 'S' and l_date_of_arrival_dcsf is null then
3251 l_arch := false;
3252 hr_utility.set_location('Date of Arrival in School must be supplied',20);
3253 populate_run_msg(p_assactid,'Date of Arrival in School must be supplied');
3254 end if;
3255
3256 -- 4440Q
3257 if l_person_category in (1,2,3) and l_date_of_arrival > l_asg_dates_rec.contract_st_date then
3258 hr_utility.set_location('Please check: Date of Arrival in School should not be later than the start of the contract ',20);
3259 populate_run_msg(p_assactid,'Please check: Date of Arrival in School should not be later than the start of the contract','W');
3260 end if;
3261
3262 -- 4570
3263 if l_daily_rate is not null
3264 and (length(l_daily_rate) <> 1 or l_daily_rate not in ('Y','N')) then
3265 l_arch := false;
3266 hr_utility.set_location('Daily Rate is invalid',20);
3267 populate_run_msg(p_assactid,'Daily Rate is invalid');
3268 end if;
3269
3270 -- Bug 12906657
3271 -- Daily Rate indicator of 'Y' is only valid for Agency/SA Teacher.
3272 -- For other Staff categories it is invalid and should throw an error message
3273
3274 if l_person_category in (1,3,4) and l_daily_rate = 'Y' then
3275 l_arch := false;
3276 hr_utility.set_location('Daily Rate indicator of Y is only valid for Agency/SA Teacher. Please correct the data and resubmit for person_id:'|| p_person_id ,20);
3277 populate_run_msg(p_assactid,'Daily Rate indicator of Y is only valid for Agency/SA Teacher. Please correct the data and resubmit');
3278 end if;
3279 -- Bug 12906657
3280
3281 -- 4580
3282 if l_dcsf_destination is not null then
3283 begin
3284 select 'Y'
3285 into l_valid_flag
3286 from dual
3287 where exists
3288 (select lookup_code
3289 from hr_lookups hl
3290 where hl.lookup_type = 'PQP_GB_SWF_DESTINATION_CODES'
3291 and hl.enabled_flag = 'Y'
3292 and hl.lookup_code = l_dcsf_destination);
3293 exception
3294 when others
3295 then
3296 l_arch := false;
3297 populate_run_msg (p_assactid, 'Destination code is invalid');
3298 hr_utility.set_location ('Destination code is invalid', 10);
3299 end;
3300
3301 if length(l_dcsf_destination) <> 6 then
3302 l_arch := false;
3303 populate_run_msg (p_assactid, 'Destination code is invalid');
3304 hr_utility.set_location ('Destination code is invalid', 20);
3305 end if;
3306 end if;
3307
3308 -- 4600
3309 if l_dcsf_origin is not null then
3310 begin
3311 select 'Y'
3312 into l_valid_flag
3313 from dual
3314 where exists
3315 (select lookup_code
3316 from hr_lookups hl
3317 where hl.lookup_type = 'PQP_GB_SWF_ORIGIN_CODES'
3318 and hl.enabled_flag = 'Y'
3319 and hl.lookup_code = l_dcsf_origin);
3320 exception
3321 when others
3322 then
3323 l_arch := false;
3324 populate_run_msg (p_assactid, 'Origin code is invalid');
3325 hr_utility.set_location ('Origin code is invalid', 10);
3326 end;
3327
3328 if length(l_dcsf_origin) <> 6 then
3329 l_arch := false;
3330 populate_run_msg (p_assactid, 'Origin code is invalid');
3331 hr_utility.set_location ('Origin code is invalid', 20);
3332 end if;
3333 end if;
3334
3335 -- 4610Q
3336 if l_person_category in (1,3) and l_asg_dates_rec.contract_st_date > to_date('31-08-2009','DD-MM-YYYY') and l_dcsf_origin is null then
3337 populate_run_msg (p_assactid, 'Please check: Origin should be provided','W');
3338 hr_utility.set_location ('Please check: Origin should be provided', 20);
3339 end if;
3340
3341 -- Additional Validations
3342
3343 if l_contract_agg_type is null then
3344 l_arch := false;
3345 populate_run_msg (p_assactid, 'Contract Aggreement type can not be null. This error could have occured if the DCSF mapping is incorrect or missing');
3346 hr_utility.set_location ('Contract Aggreement type can not be null. This error could have occured if the DCSF mapping is incorrect or missing', 20);
3347 end if;
3348 p_contract_rec.action_info_category := 'GB_SWF_CONTRACT_DETAILS';
3349 p_contract_rec.person_id := p_person_id;
3350 p_contract_rec.assignment_id := l_asg_rec.assignment_id;
3351 p_contract_rec.effective_date := g_census_day;
3352 p_contract_rec.act_info1 := p_person_id;
3353 p_contract_rec.act_info2 := l_contract_agg_type;
3354
3355 -- passed as parameter to fetch_payment_details
3356 p_contract_rec.act_info3 := l_asg_dates_rec.contract_st_date_dcsf;
3357 p_contract_rec.act_info4 := l_asg_dates_rec.contract_end_date_dcsf;
3358 p_contract_rec.act_info5 := l_post;
3359 --
3360 p_contract_rec.act_info6 := l_date_of_arrival_dcsf;
3361 -- passed as parameter to fetch_payment_details
3362 p_contract_rec.act_info7 := l_daily_rate;
3363 --
3364 p_contract_rec.act_info8 := l_dcsf_destination;
3365 p_contract_rec.act_info9 := l_dcsf_origin;
3366 p_contract_rec.act_info10 := l_la_or_school_level;
3367 p_contract_rec.act_info11 := l_establishment;
3368 p_contract_rec.act_info12 := l_asg_rec.assignment_number;
3369 p_contract_rec.act_info13 := p_effective_date; -- staff effective_date
3370 -- passed as parameter to fetch_payment_details
3371 p_contract_rec.act_info14 := l_person_category;
3372 --
3373 hr_utility.set_location('Leaving '|| l_proc, 99);
3374 return l_arch;
3375 exception when others then
3376 hr_utility.set_location(sqlerrm, 999);
3377 hr_utility.set_location('Leaving with error'|| l_proc, 999);
3378 end fetch_contract_details;
3379
3380 -----------------------function fetch_qualification_details---------------------
3381 function fetch_qualification_details (p_assactid in number,
3382 p_person_id in number,
3383 p_estab_no in number,
3384 p_qual_tab out nocopy qual_details_tab)
3385 return boolean is
3386
3387 cursor get_qual_details is
3388 select qual.qualification_id qual_id,
3389 qualtyp.qualification_type_id qualification_type_id,
3390 qualtyp.category qual_cat,
3391 decode(qua_information_category, 'GB', qua_information1, null) qual_code_dff,
3392 decode(qua_information_category, 'GB', qua_information2, null) subject1_dff,
3393 decode(qua_information_category, 'GB', qua_information3, null) subject2_dff,
3394 decode(qua_information_category, 'GB', decode(qua_information4,'Y','true','false'), null) verified_dff
3395 from per_qualifications qual, per_qualification_types qualtyp
3396 where qual.person_id = p_person_id
3397 and qual.qualification_type_id = qualtyp.qualification_type_id;
3398
3399 cursor subject_taken(p_qual_id in number) is
3400 select max(decode(seq, 1, subject_dcsf, null)) subject_1,
3401 max(decode(seq, 2, subject_dcsf, null)) subject_2
3402 from (select subject_dcsf, seq
3403 from (select pcv.pcv_information2 subject_dcsf, row_number() over(order by major desc) seq
3404 from per_qualifications qual, per_subjects_taken sub,
3405 pqp_configuration_values pcv
3406 where qual.qualification_id = sub.qualification_id
3407 and qual.person_id = p_person_id
3408 and qual.qualification_id = p_qual_id
3409 and pcv.pcv_information_category = 'PQP_GB_SWF_QUAL_SUBJECT_MAP'
3410 and pcv.pcv_information1 = sub.subject
3411 and pcv.business_group_id = g_business_group_id)
3412 where seq < 3);
3413
3414 cursor get_qual_code_src is
3415 select pcv_information1
3416 from pqp_configuration_values pcv
3417 where pcv.pcv_information_category = 'PQP_GB_SWF_QUAL_CODE_MAP'
3418 and pcv.business_group_id = g_business_group_id;
3419
3420 cursor get_qual_code_dcsf_cat(p_qual_cat in varchar2) is
3421 select pcv_information5
3422 from pqp_configuration_values pcv
3423 where pcv.pcv_information_category = 'PQP_GB_SWF_QUAL_CODE_MAP'
3424 and pcv.pcv_information3 = p_qual_cat
3425 and pcv.business_group_id = g_business_group_id;
3426
3427 cursor get_qual_code_dcsf_typ(p_qual_typ in varchar2) is
3428 select pcv_information5
3429 from pqp_configuration_values pcv
3430 where pcv.pcv_information_category = 'PQP_GB_SWF_QUAL_CODE_MAP'
3431 and pcv.pcv_information4 = p_qual_typ
3432 and pcv.business_group_id = g_business_group_id;
3433
3434 l_proc constant varchar2(50) := g_package || ' fetch_qualification_details';
3435 l_qual_rec get_qual_details%rowtype;
3436 l_subject1 per_subjects_taken.subject%type;
3437 l_subject2 per_subjects_taken.subject%type;
3438 l_qual_code_src pqp_configuration_values.pcv_information1%type;
3439 l_qual_code_dcsf pqp_configuration_values.pcv_information1%type;
3440 l_qual_tab_idx number := 1;
3441 l_invalid_qual_code varchar2(1) := 'N';
3442 l_missing_qual_code varchar2(1) := 'N';
3443 l_sub_code_1_invalid varchar2(1) := 'N';
3444 l_sub_code_2_invalid varchar2(1) := 'N';
3445 l_sub1_missing varchar2(1) := 'N';
3446 l_missing_verified varchar2(1) := 'N';
3447 l_same_sub_1_2 varchar2(1) := 'N';
3448 l_arch boolean := true;
3449 begin
3450 hr_utility.set_location('Entering :'||l_proc,10);
3451 hr_utility.set_location('Parameters :',20);
3452 hr_utility.set_location('p_assactid :'||p_assactid,20);
3453 hr_utility.set_location('p_person_id :'||p_person_id,20);
3454
3455 open get_qual_code_src;
3456 fetch get_qual_code_src into l_qual_code_src;
3457 close get_qual_code_src;
3458
3459 for qual_cur in get_qual_details loop
3460 open subject_taken(qual_cur.qual_id);
3461 fetch subject_taken into l_subject1, l_subject2;
3462 close subject_taken;
3463
3464 if l_qual_code_src = 'QUALIFICATION_CATEGORY' then
3465 open get_qual_code_dcsf_cat(qual_cur.qual_cat);
3466 fetch get_qual_code_dcsf_cat into l_qual_code_dcsf;
3467 close get_qual_code_dcsf_cat;
3468 elsif l_qual_code_src = 'QUALIFICATION_TYPE' then
3469 open get_qual_code_dcsf_typ(qual_cur.qualification_type_id);
3470 fetch get_qual_code_dcsf_typ into l_qual_code_dcsf;
3471 close get_qual_code_dcsf_typ;
3472 end if;
3473
3474 p_qual_tab(l_qual_tab_idx).person_id := p_person_id;
3475 p_qual_tab(l_qual_tab_idx).qual_code := nvl(qual_cur.qual_code_dff,l_qual_code_dcsf);
3476 p_qual_tab(l_qual_tab_idx).sub1 := nvl(qual_cur.subject1_dff,l_subject1);
3477 p_qual_tab(l_qual_tab_idx).sub2 := nvl(qual_cur.subject2_dff,l_subject2);
3478 p_qual_tab(l_qual_tab_idx).verified := qual_cur.verified_dff;
3479 p_qual_tab(l_qual_tab_idx).estab_no := p_estab_no;
3480
3481
3482 l_qual_tab_idx := l_qual_tab_idx +1;
3483 end loop;
3484
3485 if p_qual_tab.count > 0 then
3486 for i in p_qual_tab.first .. p_qual_tab.last loop
3487 if p_qual_tab(i).qual_code not in ('PGCE', 'MAST', 'DOCT', 'BEDO', 'FRST', 'CTED', 'NVQ4', 'NNUK') then
3488 l_invalid_qual_code := 'Y';
3489 end if;
3490
3491 if (p_qual_tab(i).sub1 is not null or p_qual_tab(i).sub2 is not null ) -- or p_qual_tab(i).verified is not null) /* Commented out for Bug# 10106993 */
3492 and p_qual_tab(i).qual_code is null then
3493 l_missing_qual_code := 'Y';
3494 end if;
3495
3496 if p_qual_tab(i).sub1 is not null and substr(p_qual_tab(i).sub1,1,1) not between 'A' and 'X'
3497 or length(substr(p_qual_tab(i).sub1,2)) <>3
3498 or substr(p_qual_tab(i).sub1,2) not between 100 and 990 then
3499 l_sub_code_1_invalid := 'Y';
3500 end if;
3501
3502 if p_qual_tab(i).sub2 is not null and substr(p_qual_tab(i).sub2,1,1)
3503 not in ('A', 'B', 'C', 'D', 'F', 'G', 'H', 'J', 'K', 'L', 'M', 'N', 'P', 'Q', 'R', 'T', 'V', 'W', 'X')
3504 or length(substr(p_qual_tab(i).sub2,2)) <>3
3505 or substr(p_qual_tab(i).sub2,2) not between 100 and 990 then
3506 l_sub_code_2_invalid := 'Y';
3507 end if;
3508
3509 if p_qual_tab(i).sub1 is null then
3510 l_sub1_missing := 'Y';
3511 end if;
3512
3513 -- Commented out for Bug# 10106993
3514 /*
3515 if (p_qual_tab(i).sub1 is not null or p_qual_tab(i).sub2 is not null or p_qual_tab(i).qual_code is not null)
3516 and p_qual_tab(i).verified is null then
3517 l_missing_verified := 'Y';
3518 end if;
3519 */
3520 -- Commented out for Bug# 10106993
3521
3522 if p_qual_tab(i).sub1 = p_qual_tab(i).sub2 then
3523 l_same_sub_1_2 :='Y';
3524 end if;
3525 end loop;
3526 end if;
3527
3528
3529 if l_invalid_qual_code = 'Y' then
3530 l_arch := false;
3531 populate_run_msg (p_assactid, 'Qualification Code is invalid');
3532 hr_utility.set_location ('Qualification Code is invalid', 20);
3533 end if;
3534
3535
3536 if l_missing_qual_code = 'Y' then
3537 l_arch := false;
3538 populate_run_msg (p_assactid, 'Qualification Code is missing');
3539 hr_utility.set_location ('Qualification Code is missing', 20);
3540 end if;
3541
3542 if l_sub_code_1_invalid = 'Y' then
3543 l_arch := false;
3544 populate_run_msg (p_assactid, 'Subject Code 1 is invalid');
3545 hr_utility.set_location ('Subject Code 1 is invalid', 20);
3546 end if;
3547
3548 if l_sub_code_2_invalid = 'Y' then
3549 l_arch := false;
3550 populate_run_msg (p_assactid, 'Second qualification Subject Code 2 is invalid');
3551 hr_utility.set_location ('Second qualification Subject Code 2 is invalid', 20);
3552 end if;
3553
3554 if l_sub1_missing = 'Y' then
3555 l_arch := false;
3556 populate_run_msg (p_assactid, 'Subject Code 1 is missing');
3557 hr_utility.set_location ('Subject Code 1 is missing', 20);
3558 end if;
3559
3560 if l_same_sub_1_2 = 'Y' then
3561 l_arch := false;
3562 populate_run_msg (p_assactid, 'Qualification Subject Code 1 and Subject Code 2 cannot be the same');
3563 hr_utility.set_location ('Qualification Subject Code 1 and Subject Code 2 cannot be the same', 20);
3564 end if;
3565
3566 -- Commented out for Bug# 10106993
3567 /*
3568 if l_missing_verified = 'Y' then
3569 l_arch := false;
3570 populate_run_msg (p_assactid, 'Qualification Verified is missing');
3571 hr_utility.set_location ('Qualification Verified is missing', 20);
3572 end if;
3573 */
3574 -- Commented out for Bug# 10106993
3575
3576 hr_utility.set_location('Leaving :'||l_proc,99);
3577 return l_arch;
3578 exception when others then
3579 hr_utility.set_location(sqlerrm,999);
3580 hr_utility.set_location('Leaving with error:'||l_proc,999);
3581 raise;
3582 end fetch_qualification_details;
3583
3584 ------------------------------function fetch_payment_details--------------------
3585 -- This is called from the archive code.
3586 function fetch_payment_details ( p_assactid in number,
3587 p_effective_date in date,
3588 p_person_id in number,
3589 p_assignment_id in number,
3590 p_post in varchar2,
3591 p_qt_status in varchar2,
3592 p_person_category in varchar2,
3593 p_cont_st_date in varchar2,
3594 p_cont_end_date in varchar2,
3595 p_daily_rate in varchar2,
3596 p_payment_rec out nocopy act_info_rec)
3597 return boolean is
3598
3599 cursor pay_scale is
3600 select ps.parent_spine_id
3601 from per_grade_spines_f grs,
3602 per_grades gdt,
3603 per_parent_spines ps,
3604 per_all_assignments_f asg
3605 where grs.grade_id = gdt.grade_id
3606 and grs.parent_spine_id = ps.parent_spine_id
3607 and asg.grade_id = grs.grade_id
3608 and asg.assignment_id = p_assignment_id
3609 and p_effective_date between asg.effective_start_date and asg.effective_end_date
3610 and p_effective_date between grs.effective_start_date and grs.effective_end_date;
3611
3612 cursor pay_scale_dcsf(p_pay_scale in per_parent_spines.parent_spine_id%type) is
3613 select pcv.pcv_information2
3614 from pqp_configuration_values pcv
3615 where pcv.pcv_information_category = 'PQP_GB_SWF_PAY_SCALE_MAPPING'
3616 and pcv.pcv_information1 = p_pay_scale
3617 and pcv.business_group_id = g_business_group_id;
3618
3619 cursor spinal_points is
3620 select spinal_point,psp.spinal_point_id
3621 from per_spinal_point_placements_f pspp,
3622 per_spinal_point_steps_f psps,
3623 per_spinal_points psp
3624 where pspp.assignment_id = p_assignment_id
3625 and pspp.step_id = psps.step_id
3626 and psps.spinal_point_id = psp.spinal_point_id
3627 and p_effective_date between pspp.effective_start_date and pspp.effective_end_date
3628 and p_effective_date between psps.effective_start_date and psps.effective_end_date;
3629
3630
3631 cursor spinal_point_dcsf(p_pay_scale in per_parent_spines.parent_spine_id%type,
3632 p_spinal_point in per_spinal_points.spinal_point_id%type) is
3633 select pcv.pcv_information3
3634 from pqp_configuration_values pcv
3635 where pcv.pcv_information_category = 'PQP_GB_SWF_SPINE_POINT_MAPPING'
3636 and pcv.pcv_information1 = p_pay_scale
3637 and pcv.pcv_information2 = p_spinal_point
3638 and pcv.business_group_id = g_business_group_id;
3639
3640 cursor salary_rate (p_spinal_point_id in number) is
3641 select to_char(value,'fm999999.00')
3642 from pay_grade_rules_f pgr
3643 where grade_or_spinal_point_id = p_spinal_point_id
3644 and p_effective_date between pgr.effective_start_date and pgr.effective_end_date;
3645
3646 cursor get_regional_spine_source is
3647 select pcv_information1, pcv_information2, pcv_information3
3648 from pqp_configuration_values pcv
3649 where pcv.pcv_information_category = 'PQP_GB_SWF_REG_SPINE_SRC'
3650 and pcv.business_group_id = g_business_group_id;
3651
3652 cursor reg_spinal_point_dcsf(p_pay_scale in per_parent_spines.name%type,
3653 p_spinal_point in per_spinal_points.spinal_point%type) is
3654 select pcv.pcv_information4
3655 from pqp_configuration_values pcv
3656 where pcv.pcv_information_category = 'PQP_GB_SWF_REG_SPINE_MAP_PYSCL'
3657 and pcv.pcv_information1 = p_pay_scale
3658 and p_spinal_point between pcv.pcv_information2 and pcv.pcv_information3
3659 and pcv.business_group_id = g_business_group_id;
3660
3661 cursor safe_grd_sal is
3662 select decode(tp_safeguarded_rate_type,'SN','True','SP','True','G','True','False')
3663 from pqp_assignment_attributes_f
3664 where assignment_id= p_assignment_id;
3665 ---
3666 l_proc constant varchar2(50) := g_package || ' fetch_payment_details';
3667 l_regional_spine_context varchar2(30);
3668 l_regional_spine_segment varchar2(30);
3669 l_regional_spine_def_val varchar2(30);
3670 l_regional_spine_sql_str varchar2(3000);
3671 l_pay_scale per_parent_spines.parent_spine_id%type;
3672 l_pay_scale_dcsf hr_lookups.lookup_code%type;
3673 l_spinal_point per_spinal_points.spinal_point%type;
3674 l_spinal_point_dcsf hr_lookups.lookup_code%type;
3675 l_reg_spinal_point_dcsf hr_lookups.lookup_code%type;
3676 l_safe_grd_sal varchar2(5);
3677 l_arch boolean := true;
3678 l_contract_st_date date := to_date(p_cont_st_date,'YYYY-MM-DD');
3679 l_contract_end_date date := to_date(p_cont_end_date,'YYYY-MM-DD');
3680 l_valid_flag varchar2(10);
3681 l_salary_rate number;
3682 l_spinal_point_id number;
3683
3684
3685
3686 begin
3687 hr_utility.set_location('Entering :'||l_proc,100);
3688
3689 hr_utility.set_location('p_assactid :' ||p_assactid,110);
3690 hr_utility.set_location('p_effective_date :' ||p_effective_date,110);
3691 hr_utility.set_location('p_person_id :' ||p_person_id,110);
3692 hr_utility.set_location('p_assignment_id :' ||p_assignment_id,110);
3693 hr_utility.set_location('p_post :' ||p_post,110);
3694 hr_utility.set_location('p_person_category :' ||p_person_category,110);
3695 hr_utility.set_location('p_cont_st_date :' ||p_cont_st_date,110);
3696 hr_utility.set_location('p_cont_end_date :' ||p_cont_end_date,110);
3697 hr_utility.set_location('p_daily_rate :' ||p_daily_rate,110);
3698
3699 open pay_scale;
3700 fetch pay_scale into l_pay_scale;
3701 close pay_scale;
3702
3703 open pay_scale_dcsf(l_pay_scale);
3704 fetch pay_scale_dcsf into l_pay_scale_dcsf;
3705 close pay_scale_dcsf;
3706
3707 open spinal_points;
3708 fetch spinal_points into l_spinal_point,l_spinal_point_id;
3709 close spinal_points;
3710
3711 open spinal_point_dcsf(l_pay_scale,l_spinal_point_id);
3712 fetch spinal_point_dcsf into l_spinal_point_dcsf;
3713 close spinal_point_dcsf;
3714
3715 open get_regional_spine_source;
3716 fetch get_regional_spine_source into l_regional_spine_context,l_regional_spine_segment,l_regional_spine_def_val;
3717 close get_regional_spine_source;
3718
3719 open salary_rate(l_spinal_point_id);
3720 fetch salary_rate into l_salary_rate;
3721 close salary_rate;
3722
3723 if l_regional_spine_context = 'GRD' then
3724 l_regional_spine_sql_str :='select pcv.pcv_information4
3725 from per_all_assignments_f paf,
3726 per_grades pgr,
3727 per_grade_definitions pgd ,
3728 pqp_configuration_values pcv
3729 where paf.business_group_id + 0 = :bg_id
3730 and paf.business_group_id = pgr.business_group_id
3731 and pcv.business_group_id = paf.business_group_id
3732 and pgr.grade_definition_id = pgd.grade_definition_id
3733 and paf.grade_id = pgr.grade_id
3734 and :eff_date between paf.effective_start_date and paf.effective_end_date
3735 and paf.assignment_id = :assignment_id
3736 and pcv.pcv_information_category = ''PQP_GB_SWF_REG_SPINE_MAP_GRD''
3737 and ((pcv_information3 is null
3738 and pgd.'||l_regional_spine_segment||' = pcv.pcv_information2 )
3739 or (pcv_information3 is not null
3740 and pgd.'||l_regional_spine_segment||' between pcv.pcv_information2 and pcv_information3))';
3741
3742 begin
3743 execute immediate l_regional_spine_sql_str into l_reg_spinal_point_dcsf using g_business_group_id,p_effective_date, p_assignment_id;
3744 exception when others then
3745 hr_utility.set_location('Spinal could not be fetched as of' || p_effective_date ||'for Assignment ID :'||p_assignment_id,9999);
3746 end;
3747
3748 elsif l_regional_spine_context = 'PAYSCALE_SPINEPOINT' then
3749 open reg_spinal_point_dcsf(l_pay_scale,l_spinal_point_id);
3750 fetch reg_spinal_point_dcsf into l_reg_spinal_point_dcsf;
3751 close reg_spinal_point_dcsf;
3752 elsif l_regional_spine_context = 'DEFAULT' then
3753 l_reg_spinal_point_dcsf := l_regional_spine_def_val;
3754 end if;
3755
3756 open safe_grd_sal;
3757 fetch safe_grd_sal into l_safe_grd_sal;
3758 close safe_grd_sal;
3759
3760 -- 4460
3761 if length(l_pay_scale_dcsf) <> 2 then
3762 l_arch := false;
3763 hr_utility.set_location('Pay Scale is invalid',10);
3764 populate_run_msg(p_assactid,'Pay Scale is invalid');
3765 end if;
3766
3767 -- 4470
3768 if l_pay_scale_dcsf in('LD', 'TE', 'TU', 'EX' ,'AS')and p_qt_status <> 'Yes' then
3769 l_arch := false;
3770 hr_utility.set_location('Pay Scale type inconsistent with Qualified Teacher Status',10);
3771 populate_run_msg(p_assactid,'Pay Scale type inconsistent with Qualified Teacher Status');
3772 end if;
3773
3774 -- 4480
3775 if p_post = 'SUP' and l_pay_scale_dcsf in('LD', 'TE', 'TU', 'EX' ,'AS') then
3776 l_arch := false;
3777 hr_utility.set_location('Pay Scale is invalid for the given Post',10);
3778 populate_run_msg(p_assactid,'Pay Scale is invalid for the given Post');
3779 end if;
3780
3781 -- 4490
3782 if p_person_category in (1,2) and (l_contract_end_date is null or l_contract_end_date > g_census_day)
3783 and (p_daily_rate = 'N' or p_daily_rate is null) and p_post in('HDT','DHT','AHT') and l_pay_scale_dcsf not in ('LD', 'OT') then /*Bug#125860598 */
3784 l_arch := false;
3785 hr_utility.set_location('Pay Scale is invalid for the given Post',20);
3786 populate_run_msg(p_assactid,'Pay Scale is invalid for the given Post');
3787 end if;
3788
3789 -- 4500
3790 if p_person_category in (1,2) and (l_contract_end_date is null or l_contract_end_date > g_census_day)
3791 and (p_daily_rate = 'N' or p_daily_rate is null )and p_post = 'AST' and l_pay_scale_dcsf not in ('AS', 'OT')then /*Bug#125860598 */
3792 l_arch := false;
3793 hr_utility.set_location('Pay Scale is invalid for the given Post',30);
3794 populate_run_msg(p_assactid,'Pay Scale is invalid for the given Post');
3795 end if;
3796
3797 -- 4505
3798 if p_person_category in (1,2) and (l_contract_end_date is null or l_contract_end_date > g_census_day)
3799 and (p_daily_rate = 'N' or p_daily_rate is null ) and p_post = 'EXL' and l_pay_scale_dcsf not in ('EX', 'OT')then /*Bug#125860598 */
3800 l_arch := false;
3801 hr_utility.set_location('Pay Scale is invalid for the given Post',40);
3802 populate_run_msg(p_assactid,'Pay Scale is invalid for the given Post');
3803 end if;
3804
3805 -- 4510
3806
3807 if l_reg_spinal_point_dcsf is not null then
3808 begin
3809 select 'Y'
3810 into l_valid_flag
3811 from dual
3812 where exists
3813 (select lookup_code
3814 from hr_lookups hl
3815 where hl.lookup_type = 'PQP_GB_REGIONAL_SPINE_CODE'
3816 and hl.enabled_flag = 'Y'
3817 and hl.lookup_code = l_reg_spinal_point_dcsf);
3818 exception
3819 when others
3820 then
3821 l_arch := false;
3822 hr_utility.set_location('Regional Pay Spine is invalid',10);
3823 populate_run_msg(p_assactid,'Regional Pay Spine is invalid');
3824 end;
3825
3826 if length(l_reg_spinal_point_dcsf) <> 2 then
3827 l_arch := false;
3828 hr_utility.set_location('Regional Pay Spine is invalid',20);
3829 populate_run_msg(p_assactid,'Regional Pay Spine is invalid');
3830 end if;
3831 end if;
3832
3833 -- 4520
3834 if l_spinal_point_dcsf is not null then
3835 begin
3836 select 'Y'
3837 into l_valid_flag
3838 from dual
3839 where exists
3840 (select lookup_code
3841 from hr_lookups hl
3842 where hl.lookup_type = 'PQP_GB_DCSF_SPINE_POINTS'
3843 and hl.enabled_flag = 'Y'
3844 and hl.lookup_code = l_spinal_point_dcsf);
3845 exception
3846 when others
3847 then
3848 l_arch := false;
3849 hr_utility.set_location('Spine Point is invalid',10);
3850 populate_run_msg(p_assactid,'Spine Point is invalid');
3851 end;
3852
3853 if length(l_spinal_point_dcsf) not between 1 and 6 then
3854 l_arch := false;
3855 hr_utility.set_location('Spine Point is invalid',20);
3856 populate_run_msg(p_assactid,'Spine Point is invalid');
3857 end if;
3858 end if;
3859
3860 -- 4530 handled in the cursor
3861 -- 4540
3862 if p_person_category in (2,3) and (l_contract_end_date is null or l_contract_end_date > g_census_day)
3863 and p_daily_rate = 'N' and l_salary_rate is null then
3864 l_arch := false;
3865 hr_utility.set_location('Salary Rate must be provided where Daily Rate is false',10);
3866 populate_run_msg(p_assactid,'Salary Rate must be provided where Daily Rate is false');
3867 end if;
3868
3869 -- 4550
3870 if p_person_category in (2,3) and (l_contract_end_date is null or l_contract_end_date > g_census_day)
3871 and p_daily_rate = 'Y' and l_salary_rate is not null then
3872 l_arch := false;
3873 hr_utility.set_location('Salary Rate must not be provided where Daily Rate is true',10);
3874 populate_run_msg(p_assactid,'Salary Rate must not be provided where Daily Rate is true');
3875 end if;
3876
3877 p_payment_rec.action_info_category := 'GB_SWF_PAYMENT_DETAILS';
3878 p_payment_rec.person_id := p_person_id;
3879 p_payment_rec.assignment_id := p_assignment_id;
3880 p_payment_rec.effective_date := sysdate;
3881 p_payment_rec.act_info1 := l_pay_scale_dcsf;
3882 p_payment_rec.act_info2 := l_reg_spinal_point_dcsf;
3883 p_payment_rec.act_info3 := l_spinal_point_dcsf;
3884 p_payment_rec.act_info4 := l_salary_rate;
3885 p_payment_rec.act_info5 := l_safe_grd_sal;
3886
3887
3888 hr_utility.set_location('Leaving :'||l_proc,999);
3889
3890 return l_arch;
3891 exception when others then
3892 hr_utility.set_location('Leaving with error:'||l_proc,9999);
3893 raise;
3894 end fetch_payment_details;
3895
3896 ----------------------procedure archinit----------------------------------------
3897 procedure archinit(p_payroll_action_id in number)
3898 is
3899 l_proc constant varchar2(50) := g_package || ' archinit';
3900 l_exp exception;
3901
3902 cursor param_details is
3903 select upper((pay_gb_eoy_archive.get_parameter(legislative_parameters,'DATA_RETURN_TYPE'))) data_return_type,
3904 (pay_gb_eoy_archive.get_parameter(legislative_parameters,'ESTB_NUM')) estb_num
3905 from pay_payroll_actions ppa
3906 where ppa.payroll_action_id = p_payroll_action_id;
3907
3908 param_details_rec param_details%rowtype;
3909
3910 begin
3911 hr_utility.set_location('Entering '|| l_proc, 10);
3912 open param_details;
3913 fetch param_details into param_details_rec;
3914 close param_details;
3915
3916 if param_details_rec.data_return_type = 'TYPE3' and param_details_rec.estb_num is not null then
3917 raise_application_error(-20002,'Establishment number should not be entered for Type 3 Extract.');
3918 fnd_file.put_line(fnd_file.log,'Establishment number should not be entered for Type 3 Extract.');
3919 fnd_file.put_line(fnd_file.output,'Establishment number should not be entered for Type 3 Extract.');
3920 fnd_file.put_line(fnd_file.output,' ');
3921 end if;
3922
3923 if param_details_rec.data_return_type = 'TYPE2' and param_details_rec.estb_num is null then
3924 raise_application_error(-20002,'Establishment number should be entered for Type 2 Extract.');
3925 fnd_file.put_line(fnd_file.log,'Establishment number should be entered for Type 2 Extract.');
3926 fnd_file.put_line(fnd_file.output,'Establishment number should be entered for Type 2 Extract.');
3927 fnd_file.put_line(fnd_file.output,' ');
3928 end if;
3929
3930
3931 hr_utility.set_location('Leaving '|| l_proc, 110);
3932 exception
3933 when others then
3934 hr_utility.set_location('Leaving '|| l_proc, 999);
3935 hr_utility.set_location(sqlerrm,9999);
3936 hr_utility.raise_error;
3937 end archinit;
3938
3939 ---------------------------procedure archive_code-------------------------------
3940 procedure archive_code(p_assactid in number,
3941 p_effective_date in date) is
3942 l_proc constant varchar2(35):= g_package||'archive_code';
3943 error_found exception;
3944 l_archive_tab action_info_table;
3945 l_role_tab addl_role_tab;
3946 l_archive_person boolean:= true;
3947 l_archive_type varchar2(20);
3948 l_archive_contract boolean:= true;
3949 l_archive_payment boolean:= true;
3950 l_archive_role boolean:= true;
3951 l_archive_abs boolean:= true;
3952 l_archive_qual boolean:= true;
3953 l_archive_addl_payment boolean:= true;
3954 l_archive_hrs boolean:= true;
3955 l_archive_tab_index pls_integer;
3956 l_abs_tab abs_details_tab;
3957 l_qual_tab qual_details_tab;
3958 p_addl_payment_tab addl_payment_dtl_tab;
3959 l_pactid number;
3960 l_contract_type varchar2(50);
3961 l_do_not_process_further boolean;
3962 -- Type 4
3963 l_assignment_id per_all_assignments_f.assignment_id%type;
3964 l_employment_category per_all_assignments_f.employment_category%type;
3965 l_assignment_number per_all_assignments_f.assignment_number%type;
3966 l_arch_role boolean :=true;
3967 l_epsy varchar2(1) := 'N';
3968 p_role_tab addl_role_tab;
3969 l_temp_or_perm varchar2(10);
3970 l_fte_src varchar2(30);
3971 l_fte_formula_id number;
3972 l_error_message varchar2(1000);
3973 l_fte_ratio number;
3974 l_fte_hrs number;
3975
3976
3977
3978
3979 cursor csr_parameter_info is
3980 select (pay_gb_eoy_archive.get_parameter(legislative_parameters,'CENSUS_YEAR')) census_year,
3981 (pay_gb_eoy_archive.get_parameter(legislative_parameters,'CENSUS_DAY')) census_day,
3982 add_months(to_date((pay_gb_eoy_archive.get_parameter(legislative_parameters,'CONT_ST_DAY'))),-12) cont_st_day,
3983 (pay_gb_eoy_archive.get_parameter(legislative_parameters,'CONT_END_DAY')) cont_end_day,
3984 (pay_gb_eoy_archive.get_parameter(legislative_parameters,'LEA_NUM')) lea_num,
3985 upper((pay_gb_eoy_archive.get_parameter(legislative_parameters,'DATA_RETURN_TYPE'))) data_return_type,
3986 (pay_gb_eoy_archive.get_parameter(legislative_parameters,'ESTB_NUM')) estb_num,
3987 (pay_gb_eoy_archive.get_parameter(legislative_parameters,'EXCLUDE_ABS')) exclude_abs,
3988 (pay_gb_eoy_archive.get_parameter(legislative_parameters,'EXCLUDE_QUAL')) exclude_qual,
3989 business_group_id,
3990 ppa.payroll_action_id
3991 from pay_assignment_actions paa, pay_payroll_actions ppa
3992 where paa.assignment_action_id = p_assactid
3993 and paa.payroll_action_id = ppa.payroll_action_id;
3994
3995 cursor csr_asg_no(p_census_day in date) is
3996 select paa.assignment_id , paa.employment_category, paa.assignment_number
3997 from pay_assignment_actions act,
3998 per_all_assignments_f paa,
3999 per_assignment_status_types pas
4000 where act.assignment_action_id = p_assactid
4001 and act.assignment_id = paa.assignment_id
4002 and p_census_day between paa.effective_start_date and paa.effective_end_date
4003 and paa.assignment_status_type_id = pas.assignment_status_type_id
4004 and pas.per_system_status = 'ACTIVE_ASSIGN';
4005
4006 cursor contract_type is
4007 select distinct pcv_information1
4008 from pqp_configuration_values
4009 where pcv_information_category = 'PQP_GB_SWF_CONTRACT_TYPE'
4010 and business_group_id = g_business_group_id;
4011
4012 cursor get_fte_src is
4013 select pcv_information1,pcv_information2
4014 from pqp_configuration_values pcv
4015 where pcv.pcv_information_category = 'PQP_GB_SWF_FTE_HOURS'
4016 and pcv.business_group_id = g_business_group_id;
4017
4018 begin
4019 hr_utility.set_location('Entering: '||l_proc,10);
4020 -----
4021 open csr_parameter_info;
4022 fetch csr_parameter_info into g_census_year,
4023 g_census_day,
4024 g_cont_data_st_date,
4025 g_cont_data_end_date,
4026 g_lea_number,
4027 g_data_ret_type,
4028 g_estb_number,
4029 g_exclude_absence,
4030 g_exclude_qual,
4031 g_business_group_id,
4032 l_pactid;
4033 close csr_parameter_info;
4034
4035 open contract_type;
4036 fetch contract_type into l_contract_type;
4037 close contract_type;
4038
4039 if l_contract_type is not null then
4040 if l_contract_type = 'ASG_CAT' then
4041 g_pick_from_asg := 'Y';
4042 else
4043 g_pick_from_asg := 'N';
4044 end if;
4045 else
4046 fnd_file.put_line(fnd_file.log,'Contract Details - Contract Type Configuration is not set.Please configure and proce');
4047 end if;
4048 ------------------------------------------------------------------------------
4049 --dyn_sql call here. dyn_sql builds all the dynamic sql strings that are used
4050 -- in the archive procedures.this has been wrapped into a procedure to keep the
4051 -- dynamic sql logic separate from the archive code.
4052 dyn_sql;
4053 ------------------------------------------------------------------------------
4054 hr_utility.set_location('Archiving Starts for assignment_action_id :'||p_assactid ,20);
4055 -- archive starts
4056 -- l_archive_tab table will be populated with all the archive records and
4057 -- finally will be passed to the archive api. the first record will be put in
4058 -- 0th index and any new records that are to be populated are to be populated
4059 -- in l_archive_tab.count location to avoid no_data_found exception
4060 if g_data_ret_type <> 'TYPE4' then
4061
4062 l_archive_person := fetch_staff_details(p_assactid,p_effective_date,l_archive_tab(0));
4063
4064 hr_utility.set_location('Archiving Staff Details Complete: '||l_proc,100);
4065
4066 l_archive_contract := fetch_contract_details(p_assactid,l_archive_tab(0).act_info16,l_archive_tab(0).person_id,l_archive_tab(0).act_info13,l_archive_tab(0).act_info2,l_archive_tab(1),l_role_tab);
4067
4068 hr_utility.set_location('Archiving Contract Details Complete: '||l_proc,200);
4069
4070 l_archive_payment := fetch_payment_details(p_assactid,l_archive_tab(0).act_info16,l_archive_tab(0).person_id,l_archive_tab(1).assignment_id,
4071 l_archive_tab(1).act_info5,l_archive_tab(0).act_info12,l_archive_tab(1).act_info14, l_archive_tab(1).act_info3,l_archive_tab(1).act_info4,l_archive_tab(1).act_info7,l_archive_tab(2));
4072
4073 hr_utility.set_location('Archiving Payment Details Complete: '||l_proc,300);
4074
4075 l_archive_tab_index := l_archive_tab.count;
4076
4077
4078 for i in l_role_tab.first .. l_role_tab.last loop
4079 l_archive_tab(l_archive_tab_index).action_info_category := 'GB_SWF_ROLE_DETAILS';
4080 l_archive_tab(l_archive_tab_index).person_id := l_archive_tab(1).person_id;
4081 l_archive_tab(l_archive_tab_index).assignment_id := l_archive_tab(1).assignment_id;
4082 l_archive_tab(l_archive_tab_index).effective_date := sysdate;
4083 l_archive_tab(l_archive_tab_index).act_info1 := l_role_tab(i);
4084
4085 l_archive_tab_index := l_archive_tab_index +1;
4086 end loop;
4087
4088 hr_utility.set_location('Archiving Role Details Complete: '||l_proc,400);
4089
4090
4091 if check_max_action(p_assactid,l_archive_tab(0).person_id,l_pactid) then
4092
4093 if g_exclude_absence = 'No' then
4094
4095 l_archive_abs := fetch_absence_details(p_assactid,l_archive_tab(0).person_id,l_archive_tab(0).act_info2,l_abs_tab);
4096 l_archive_tab_index := l_archive_tab.count;
4097 if l_abs_tab.count >0 then
4098 for i in l_abs_tab.first .. l_abs_tab.last loop
4099 l_archive_tab(l_archive_tab_index).action_info_category := 'GB_SWF_ABS_DETAILS';
4100 l_archive_tab(l_archive_tab_index).person_id := l_archive_tab(1).person_id;
4101 l_archive_tab(l_archive_tab_index).assignment_id := l_archive_tab(1).assignment_id;
4102 l_archive_tab(l_archive_tab_index).effective_date := sysdate;
4103 l_archive_tab(l_archive_tab_index).act_info1 := l_abs_tab(i).person_id;
4104 l_archive_tab(l_archive_tab_index).act_info2 := l_abs_tab(i).date_start_dcsf;
4105 l_archive_tab(l_archive_tab_index).act_info3 := l_abs_tab(i).date_end_dcsf;
4106 l_archive_tab(l_archive_tab_index).act_info4 := l_abs_tab(i).days_lost;
4107 l_archive_tab(l_archive_tab_index).act_info5 := l_abs_tab(i).absence_category;
4108 l_archive_tab(l_archive_tab_index).act_info6 := l_abs_tab(i).estab_no;
4109
4110 l_archive_tab_index := l_archive_tab_index +1;
4111 end loop;
4112 end if;
4113 end if;
4114
4115 hr_utility.set_location('Archiving Absence Complete: '||l_proc,500);
4116
4117 if g_exclude_qual = 'No' then
4118 l_archive_qual := fetch_qualification_details(p_assactid,l_archive_tab(0).person_id,l_archive_tab(0).act_info2,l_qual_tab);
4119 l_archive_tab_index := l_archive_tab.count;
4120 if l_qual_tab.count > 0 then
4121 for i in l_qual_tab.first .. l_qual_tab.last loop
4122 l_archive_tab(l_archive_tab_index).action_info_category := 'GB_SWF_QUAL_DETAILS';
4123 l_archive_tab(l_archive_tab_index).person_id := l_archive_tab(1).person_id;
4124 l_archive_tab(l_archive_tab_index).assignment_id := l_archive_tab(1).assignment_id;
4125 l_archive_tab(l_archive_tab_index).effective_date := sysdate;
4126 l_archive_tab(l_archive_tab_index).act_info1 := l_qual_tab(i).person_id;
4127 l_archive_tab(l_archive_tab_index).act_info2 := l_qual_tab(i).qual_code;
4128 l_archive_tab(l_archive_tab_index).act_info3 := l_qual_tab(i).sub1;
4129 l_archive_tab(l_archive_tab_index).act_info4 := l_qual_tab(i).sub2;
4130 l_archive_tab(l_archive_tab_index).act_info5 := l_qual_tab(i).verified;
4131 l_archive_tab(l_archive_tab_index).act_info6 := l_qual_tab(i).estab_no;
4132
4133 l_archive_tab_index := l_archive_tab_index +1;
4134 end loop;
4135 end if;
4136 end if;
4137
4138 hr_utility.set_location('Archiving Qualification Complete: '||l_proc,600);
4139 end if; -- end check_max_action
4140
4141
4142 l_archive_addl_payment := fetch_addl_payment_details(p_assactid,l_archive_tab(1).assignment_id,l_archive_tab(0).act_info16,p_addl_payment_tab);
4143 l_archive_tab_index:= l_archive_tab.count;
4144 if p_addl_payment_tab.count >0 then
4145 for i in p_addl_payment_tab.first .. p_addl_payment_tab.last loop
4146 l_archive_tab(l_archive_tab_index).action_info_category := 'GB_SWF_ADD_PAYMENT_DETAILS';
4147 l_archive_tab(l_archive_tab_index).person_id := l_archive_tab(1).person_id;
4148 l_archive_tab(l_archive_tab_index).assignment_id := l_archive_tab(1).assignment_id;
4149 l_archive_tab(l_archive_tab_index).effective_date := sysdate;
4150 l_archive_tab(l_archive_tab_index).act_info1 := p_addl_payment_tab(i).addl_payment_cat;
4151 l_archive_tab(l_archive_tab_index).act_info2 := p_addl_payment_tab(i).addl_payment_amt;
4152
4153 l_archive_tab_index := l_archive_tab_index +1;
4154 end loop;
4155 end if;
4156
4157
4158 hr_utility.set_location('Archiving Addl Payments Complete: '||l_proc,700);
4159
4160 l_archive_tab_index:= l_archive_tab.count;
4161
4162 l_archive_hrs := fetch_hours_details(p_assactid,l_archive_tab(1).assignment_id,l_archive_tab(0).act_info16,
4163 l_archive_tab(1).act_info14,l_archive_tab(0).person_id,l_archive_tab(1).act_info2,l_archive_tab(1).act_info4,l_archive_tab(l_archive_tab_index));
4164
4165 hr_utility.set_location('Archiving Hours Complete: '||l_proc,800);
4166
4167 if l_archive_person and l_archive_contract and l_archive_payment and l_archive_abs and
4168 l_archive_qual and l_archive_addl_payment and l_archive_hrs then
4169
4170 insert_archive_row(p_assactid, p_effective_date, l_archive_tab);
4171 else
4172 fnd_file.put_line(fnd_file.log,'Error(s) in assignment id: '||l_archive_tab(1).assignment_id||'. Please refer Output file for detailed error messages');
4173 raise_application_error(-20001,'Error(s) found while archiving data.');
4174 end if;
4175
4176 else
4177 open csr_asg_no(g_census_day);
4178 fetch csr_asg_no into l_assignment_id,l_employment_category,l_assignment_number;
4179 close csr_asg_no;
4180
4181 if l_assignment_id is not null then
4182
4183 l_arch_role := fetch_role_details(p_assactid,g_census_day,null,l_assignment_id,l_role_tab);
4184 hr_utility.set_location('Test 0: '||l_proc,800);
4185
4186 for i in l_role_tab.first .. l_role_tab.last loop
4187 if l_role_tab(i) = 'EPSY' then
4188 l_epsy := 'Y';
4189 end if;
4190 end loop;
4191
4192 hr_utility.set_location('Test 1: '||l_proc,800);
4193 if l_epsy = 'Y' then
4194 -- If the value for l_employment_category is seeded
4195 l_temp_or_perm := pqp_gb_t1_pension_extracts.get_translate_asg_emp_cat_code
4196 (l_employment_category
4197 ,g_census_day
4198 ,'Pension Extracts Employment Category Code'
4199 ,null
4200 ) ;
4201 -- if value for l_employment_category is not seeded
4202 l_temp_or_perm := pqp_gb_t1_pension_extracts.get_translate_asg_emp_cat_code
4203 (l_employment_category
4204 ,g_census_day
4205 ,'Pension Extracts Employment Category Code'
4206 ,g_business_group_id
4207 ) ;
4208 hr_utility.set_location('Test 2: '||l_proc,800);
4209
4210 l_archive_hrs := fetch_hours_details(p_assactid,l_assignment_id,g_census_day,4,null,l_contract_type, null,l_archive_tab(0) );
4211 l_archive_tab_index:= l_archive_tab.count;
4212 hr_utility.set_location('Test 3: '||l_proc,800);
4213 for i in l_role_tab.first .. l_role_tab.last loop
4214 l_archive_tab(l_archive_tab_index).action_info_category := 'GB_SWF_ROLE_DETAILS';
4215 l_archive_tab(l_archive_tab_index).person_id := l_archive_tab(1).person_id;
4216 l_archive_tab(l_archive_tab_index).assignment_id := l_archive_tab(1).assignment_id;
4217 l_archive_tab(l_archive_tab_index).effective_date := sysdate;
4218 l_archive_tab(l_archive_tab_index).act_info1 := l_role_tab(i);
4219 l_archive_tab(l_archive_tab_index).act_info2 := l_temp_or_perm;
4220
4221 l_archive_tab_index := l_archive_tab_index +1;
4222 end loop;
4223
4224 hr_utility.set_location('Test 4: '||l_proc,800);
4225 insert_archive_row(p_assactid, g_census_day, l_archive_tab);
4226 end if;
4227 end if;
4228 end if;
4229
4230
4231 hr_utility.set_location('leaving: '||l_proc,999);
4232
4233 exception
4234 when others then
4235 hr_utility.trace(sqlerrm);
4236 hr_utility.set_location('Error in Archive: '||l_proc,999);
4237 raise;
4238 end archive_code;
4239
4240 --------------------------- procedure deinit_code------------------------------
4241
4242 procedure deinit_code(pactid in number) is
4243 l_proc constant varchar2(50) := g_package || 'deinit_code';
4244 l_counter number;
4245 l_temp varchar2(100);
4246
4247 cursor param_details
4248 is
4249 select(pay_gb_eoy_archive.get_parameter(legislative_parameters,'CENSUS_YEAR')) census_year,
4250 (pay_gb_eoy_archive.get_parameter(legislative_parameters,'CENSUS_DAY')) census_day,
4251 add_months(to_date((pay_gb_eoy_archive.get_parameter(legislative_parameters,'CONT_ST_DAY'))),-12) cont_st_day,
4252 (pay_gb_eoy_archive.get_parameter(legislative_parameters,'CONT_END_DAY')) cont_end_day,
4253 (pay_gb_eoy_archive.get_parameter(legislative_parameters,'LEA_NUM')) lea_num,
4254 upper((pay_gb_eoy_archive.get_parameter(legislative_parameters,'DATA_RETURN_TYPE'))) data_return_type,
4255 (pay_gb_eoy_archive.get_parameter(legislative_parameters,'ESTB_NUM')) estb_num,
4256 (pay_gb_eoy_archive.get_parameter(legislative_parameters,'EXCLUDE_ABS')) exclude_abs,
4257 (pay_gb_eoy_archive.get_parameter(legislative_parameters,'EXCLUDE_QUAL')) exclude_qual,
4258 (pay_gb_eoy_archive.get_parameter(legislative_parameters,'ASG_SET')) asg_set,
4259 effective_date,
4260 business_group_id
4261 from pay_payroll_actions
4262 where payroll_action_id = pactid;
4263
4264 cursor csr_asg is
4265 select distinct
4266 peo.first_name f_name ,
4267 peo.middle_names m_name,
4268 peo.last_name l_name,
4269 peo.title title,
4270 peo.employee_number emp_no,
4271 paf.assignment_number asg_no,
4272 peo.national_identifier ni_no,
4273 paa.assignment_action_id asg_act_id
4274 from pay_payroll_actions pay,
4275 pay_assignment_actions paa,
4276 per_all_assignments_f paf,
4277 per_all_people_f peo,
4278 (select max(effective_end_date) effective_date,assignment_id
4279 from per_all_assignments_f
4280 group by assignment_id) max_eff_date
4281 where pay.payroll_action_id = pactid
4282 and paa.payroll_action_id = pay.payroll_action_id
4283 and paf.assignment_id = paa.assignment_id
4284 and peo.person_id = paf.person_id
4285 and max_eff_date.assignment_id = paf.assignment_id
4286 and exists (select 'X'
4287 from pay_message_lines pml
4288 where paa.assignment_action_id = pml.source_id)
4289 and max_eff_date.effective_date between paf.effective_start_date and paf.effective_end_date
4290 and max_eff_date.effective_date between peo.effective_start_date and peo.effective_end_date;
4291
4292
4293 cursor messages (p_asg_act_id in number) is
4294 select pml.line_text error_text
4295 from pay_message_lines pml
4296 where pml.source_id = p_asg_act_id
4297 and pml.MESSAGE_LEVEL = 'F'
4298 and pml.line_sequence < (select line_sequence
4299 from pay_message_lines pml1
4300 where pml1.source_id = p_asg_act_id
4301 and pml1.line_text like 'Error ORA-20001: Error(s) found while archiving data.')
4302 UNION ALL
4303 select pml.line_text error_text
4304 from pay_message_lines pml
4305 where pml.source_id = p_asg_act_id
4306 and pml.message_level = 'W';
4307
4308
4309 param_details_rec param_details%rowtype;
4310
4311 cursor asg_without_errors is
4312 select distinct
4313 peo.first_name f_name ,
4314 peo.middle_names m_name,
4315 peo.last_name l_name,
4316 peo.title title,
4317 peo.employee_number emp_no,
4318 paf.assignment_number asg_no,
4319 peo.national_identifier ni_no,
4320 paa.assignment_action_id asg_act_id
4321 from pay_payroll_actions pay,
4322 pay_assignment_actions paa,
4323 per_all_assignments_f paf,
4324 per_all_people_f peo,
4325 (select max(effective_end_date) effective_date,assignment_id
4326 from per_all_assignments_f
4327 group by assignment_id) max_eff_date
4328 where pay.payroll_action_id = pactid
4329 and paa.payroll_action_id = pay.payroll_action_id
4330 and paf.assignment_id = paa.assignment_id
4331 and peo.person_id = paf.person_id
4332 and max_eff_date.assignment_id = paf.assignment_id
4333 and not exists (select 'X'
4334 from pay_message_lines pml
4335 where paa.assignment_action_id = pml.source_id
4336 and message_level <> 'W')
4337 and max_eff_date.effective_date between paf.effective_start_date and paf.effective_end_date
4338 and max_eff_date.effective_date between peo.effective_start_date and peo.effective_end_date;
4339
4340 cursor fte_hrs is
4341 select action_information4 person_id,sum(action_information7)
4342 from pay_action_information pai,
4343 pay_payroll_actions ppa,
4344 pay_assignment_actions paa
4345 where ppa.payroll_action_id = pactid
4346 and ppa.payroll_action_id = paa.payroll_action_id
4347 and paa.assignment_action_id = pai.action_context_id
4348 and pai.action_information_category = 'GB_SWF_HOURS_DETAILS'
4349 and action_information5 IN ('PRM','TMP','FXT')
4350 and action_information6 is null
4351 group by action_information4
4352 having sum(action_information7) > 1.5;
4353
4354 cursor emp_details (p_person_id IN number)is
4355 select peo.first_name f_name ,
4356 peo.middle_names m_name,
4357 peo.last_name l_name,
4358 peo.title title,
4359 peo.employee_number emp_no,
4360 peo.national_identifier ni_no
4361 from per_all_people_f peo
4362 where person_id = p_person_id;
4363
4364 emp_rec emp_details%rowtype;
4365
4366 CURSOR hdtr_count IS
4367 select COUNT(*)
4368 from pay_action_information pai,
4369 pay_payroll_actions ppa,
4370 pay_assignment_actions paa
4371 where ppa.payroll_action_id = pactid
4372 and ppa.payroll_action_id = paa.payroll_action_id
4373 and paa.assignment_action_id = pai.action_context_id
4374 and pai.action_information_category = 'GB_SWF_ROLE_DETAILS'
4375 and action_information1 = 'HDTR';
4376
4377 l_hdtr_count number;
4378
4379 l_espf_full_time number;
4380 l_espf_part_time number;
4381 l_espf_fte number;
4382
4383 cursor epsy_head_count_prm is
4384 select COUNT(*)
4385 from pay_action_information pai,
4386 pay_payroll_actions ppa,
4387 pay_assignment_actions paa
4388 where ppa.payroll_action_id = pactid
4389 and ppa.payroll_action_id = paa.payroll_action_id
4390 and paa.assignment_action_id = pai.action_context_id
4391 and pai.action_information_category = 'GB_SWF_ROLE_DETAILS'
4392 and action_information1 = 'EPSY'
4393 and action_information2 = 'F';
4394
4395 cursor epsy_head_count_part_time is
4396 select COUNT(*)
4397 from pay_action_information pai,
4398 pay_payroll_actions ppa,
4399 pay_assignment_actions paa
4400 where ppa.payroll_action_id = pactid
4401 and ppa.payroll_action_id = paa.payroll_action_id
4402 and paa.assignment_action_id = pai.action_context_id
4403 and pai.action_information_category = 'GB_SWF_ROLE_DETAILS'
4404 and action_information1 = 'EPSY'
4405 and action_information2 = 'P';
4406
4407 cursor epsy_fte_sum_part_time is
4408 select sum(pai2.action_information7)
4409 from pay_action_information pai,
4410 pay_action_information pai2,
4411 pay_payroll_actions ppa,
4412 pay_assignment_actions paa
4413 where ppa.payroll_action_id = pactid
4414 and ppa.payroll_action_id = paa.payroll_action_id
4415 and paa.assignment_action_id = pai.action_context_id
4416 and pai.action_context_id = pai2.action_context_id
4417 and pai.action_information_category = 'GB_SWF_ROLE_DETAILS'
4418 and pai2.action_information_category = 'GB_SWF_HOURS_DETAILS'
4419 and pai.action_information1 = 'EPSY'
4420 and pai.action_information2 = 'P';
4421
4422
4423 begin
4424 hr_utility.set_location('Entering: '||l_proc,10);
4425 open param_details;
4426 fetch param_details into param_details_rec;
4427 close param_details;
4428
4429 fnd_file.put_line(fnd_file.output,'Parameter Details:');
4430 fnd_file.put_line(fnd_file.output,rpad('Census Year',25)||': '||param_details_rec.census_year);
4431 fnd_file.put_line(fnd_file.output,rpad('Census Day',25)||': '||param_details_rec.census_day);
4432 fnd_file.put_line(fnd_file.output,rpad('Continuous Start Day',25)||': '||param_details_rec.cont_st_day);
4433 fnd_file.put_line(fnd_file.output,rpad('Continuous End Day',25)||': '||param_details_rec.cont_end_day);
4434 fnd_file.put_line(fnd_file.output,rpad('LEA Number',25)||': '||param_details_rec.lea_num);
4435 fnd_file.put_line(fnd_file.output,rpad('Data Return Type',25)||': '||param_details_rec.data_return_type);
4436 fnd_file.put_line(fnd_file.output,rpad('Establishment Number',25)||': '||param_details_rec.estb_num);
4437 fnd_file.put_line(fnd_file.output,rpad('Exclude Absence',25)||': '||param_details_rec.exclude_abs);
4438 fnd_file.put_line(fnd_file.output,rpad('Exclude Qualification',25)||': '||param_details_rec.exclude_qual);
4439 fnd_file.put_line(fnd_file.output,rpad('Assignment Set',25)||': '||param_details_rec.asg_set);
4440 fnd_file.put_line(fnd_file.output,' ');
4441 fnd_file.put_line(fnd_file.output,' ');
4442
4443
4444 if param_details_rec.data_return_type <> 'TYPE4' then
4445 fnd_file.put_line(fnd_file.output,rpad('Assignments Processed With Errors :',50));
4446 fnd_file.put_line(fnd_file.output,' ');
4447 for asg_rec in csr_asg loop
4448 fnd_file.put_line(fnd_file.output,rpad('Employee Number',19) ||
4449 rpad('NI Number',11) ||
4450 rpad('Assignment Number',19) ||
4451 rpad('Employee Name', 50));
4452 fnd_file.put_line(fnd_file.output,rpad('-',18,'-') || ' ' ||
4453 rpad('-',10,'-') || ' ' ||
4454 rpad('-',18,'-') || ' ' ||
4455 rpad('-',50,'-'));
4456 l_temp := asg_rec.l_name || ', '|| asg_rec.title || ' ' || asg_rec.f_name || ' ' || asg_rec.m_name;
4457 fnd_file.put_line(fnd_file.output,rpad(asg_rec.emp_no, 18) || ' ' ||
4458 rpad(asg_rec.ni_no ,10) || ' ' ||
4459 rpad(asg_rec.asg_no, 18) || ' ' ||
4460 rpad(l_temp,50));
4461
4462 l_counter := 1;
4463 for msg_rec in messages(asg_rec.asg_act_id) loop
4464 if l_counter = 1 then
4465 fnd_file.put_line(fnd_file.output,rpad('Error Message(s) :',18));
4466 end if;
4467 fnd_file.put_line(fnd_file.output,substr(msg_rec.error_text,1,255));
4468 l_counter:= l_counter +1;
4469 end loop;
4470 fnd_file.put_line(fnd_file.output,' ');
4471 end loop;
4472
4473 fnd_file.put_line(fnd_file.output,' ');
4474 fnd_file.put_line(fnd_file.output,' ');
4475 fnd_file.put_line(fnd_file.output,rpad('Assignments Processed Without Errors :',50));
4476 fnd_file.put_line(fnd_file.output,rpad('Employee Number',19) ||
4477 rpad('NI Number',11) ||
4478 rpad('Assignment Number',19) ||
4479 rpad('Employee Name', 50));
4480 fnd_file.put_line(fnd_file.output,rpad('-',18,'-') || ' ' ||
4481 rpad('-',10,'-') || ' ' ||
4482 rpad('-',18,'-') || ' ' ||
4483 rpad('-',50,'-'));
4484
4485 for asg_rec in asg_without_errors loop
4486
4487 l_temp := asg_rec.l_name || ', '|| asg_rec.title || ' ' || asg_rec.f_name || ' ' || asg_rec.m_name;
4488 fnd_file.put_line(fnd_file.output,rpad(asg_rec.emp_no, 18) || ' ' ||
4489 rpad(asg_rec.ni_no ,10) || ' ' ||
4490 rpad(asg_rec.asg_no, 18) || ' ' ||
4491 rpad(l_temp,50));
4492 end loop;
4493
4494 l_counter := 0;
4495 for fte_hrs_errors in fte_hrs loop
4496 l_counter := l_counter + 1;
4497 if l_counter = 1 then
4498 fnd_file.put_line(fnd_file.output,' ');
4499 fnd_file.put_line(fnd_file.output,'The following person(s) has a total Full Time Equivalent ratio greater than 1.5');
4500 fnd_file.put_line(fnd_file.output,rpad('Employee Number',19) ||
4501 rpad('NI Number',11) ||
4502 rpad('Employee Name', 50));
4503 fnd_file.put_line(fnd_file.output,rpad('-',18,'-') || ' ' ||
4504 rpad('-',10,'-') || ' ' ||
4505 rpad('-',50,'-'));
4506 end if;
4507
4508 open emp_details(fte_hrs_errors.person_id);
4509 fetch emp_details into emp_rec;
4510 l_temp := emp_rec.l_name || ', '|| emp_rec.title || ' ' || emp_rec.f_name || ' ' || emp_rec.m_name;
4511 fnd_file.put_line(fnd_file.output,rpad(emp_rec.emp_no, 18) || ' ' ||
4512 rpad(emp_rec.ni_no ,10) || ' ' ||
4513 rpad(l_temp,50));
4514 close emp_details;
4515
4516 end loop;
4517
4518 open hdtr_count;
4519 fetch hdtr_count into l_hdtr_count;
4520 close hdtr_count;
4521
4522 if param_details_rec.data_return_type <>'TYPE3' and l_hdtr_count = 0 then
4523 fnd_file.put_line(fnd_file.output,'Atleast one staff record in this school''s return should show a role of Head Teacher');
4524 end if;
4525 end if ; --data_return_type <> 'TYPE4'
4526
4527 if param_details_rec.data_return_type = 'TYPE4' then
4528 open epsy_head_count_prm;
4529 fetch epsy_head_count_prm into l_espf_full_time;
4530 close epsy_head_count_prm ;
4531
4532 open epsy_head_count_part_time;
4533 fetch epsy_head_count_part_time into l_espf_part_time;
4534 close epsy_head_count_part_time;
4535
4536 open epsy_fte_sum_part_time;
4537 fetch epsy_fte_sum_part_time into l_espf_fte;
4538 close epsy_fte_sum_part_time;
4539
4540 fnd_file.put_line(fnd_file.output,rpad('Full Time Educational Psychologists Count',60)||': '||l_espf_full_time);
4541 fnd_file.put_line(fnd_file.output,rpad('Part Time Educational Psychologists Count',60)||': '||l_espf_part_time);
4542 fnd_file.put_line(fnd_file.output,rpad('Total FTE- part Time Educational Psychologists ',60)||': '||l_espf_fte);
4543
4544
4545 end if;
4546
4547 hr_utility.set_location('leaving: '||l_proc,999);
4548 exception
4549 when others then
4550 hr_utility.set_location('Error in deinit: '||sqlerrm||l_proc,999);
4551 end deinit_code;
4552
4553 end pqp_gb_swf_archive;
[Home] [Help]