[Home] [Help]
PACKAGE BODY: APPS.PQP_GB_SWF_EXTRACT
Source
1 PACKAGE BODY PQP_GB_SWF_EXTRACT AS
2 /* $Header: pqpgbswfex.pkb 120.8 2011/09/09 10:29:52 abdash noship $ */
3
4 /*
5 Data Return Type
6 ----------------
7
8 -- Bug#12586059
9
10 -- TYPE1A Single File for LA (LEA_<LEA number>.<req_id>)
11 -- TYPE1B Multiple File for LA (EST_<EST number>.<req_id>,...)
12 TYPE1 Single File for LA (LEA_<LEA number>.<req_id>)
13
14 -- Bug#12586059
15
16 TYPE2 Single File for EST (EST_<EST number>.<req_id>)
17 TYPE3 Single File for LA (LEA_<LEA number>.<req_id>)
18 TYPE4 Single File for LA (LEA_<LEA number>.<req_id>)
19
20 Person Category
21 ---------------
22 1- Regular Teacher
23 2- Agency Teacher
24 3- Teaching Assistant
25 4- Other Support Staff
26
27 */
28
29 g_package constant varchar2(20):= 'PQP_GB_SWF_EXTRACT.';
30
31 -- Parameters
32 g_census_year number;
33 g_request_id pay_payroll_actions.request_id%type;
34 g_output_dir varchar2(400);
35 g_serial_number number;
36
37 -- Process Details
38 g_payroll_action_id pay_payroll_actions.payroll_action_id%type;
39 g_census_date date;
40 g_lea_number number;
41 g_data_return_type varchar2(10);
42 g_estab_number varchar2(10);
43 g_exclude_absence varchar2(3);
44 g_exclude_qual varchar2(3);
45 g_iana_char_set varchar2(30);
46 g_file_handle utl_file.file_type;
47
48 -- LA Educational Psychologists Count
49 g_edu_psy_ft varchar2(100);
50 g_edu_psy_pt varchar2(100);
51 g_edu_psy_fte varchar2(100);
52
53 PROCEDURE WRITE_LINE(p_tag_type varchar2,p_tag_name varchar2,p_tag_value varchar2) AS
54 l_proc constant varchar2(100):= g_package||'WRITE_LINE';
55 l_data pay_action_information.action_information1%type;
56 BEGIN
57 hr_utility.trace('Entering: '||l_proc);
58
59 IF p_tag_type = 'O' THEN -- Open Tag
60 l_data:='<'||p_tag_name||'>';
61 hr_utility.set_location('Writing : '||l_data,2);
62 ELSIF p_tag_type = 'C' THEN -- Close Tag
63 l_data:='</'||p_tag_name||'>';
64 hr_utility.set_location('Writing : '||l_data,3);
65 ELSIF p_tag_type = 'D' THEN -- Data Tag
66 -- If the value is null do not display the tag
67 IF (p_tag_value is null OR trim(p_tag_value) ='') THEN
68 return;
69 hr_utility.set_location(p_tag_name||' Value is null',4);
70 END IF;
71 -- Replace special characters with XML equivalents
72 l_data := REPLACE (p_tag_value, '&', '&');
73 l_data := REPLACE (l_data, '<', '<');
74 l_data := REPLACE (l_data, '>', '>');
75 l_data := REPLACE (l_data, '''', ''');
76 l_data := REPLACE (l_data, '"', '"');
77 l_data:='<'||p_tag_name||'>'||l_data||'</'||p_tag_name||'>';
78
79 hr_utility.set_location('Writing : '||l_data,5);
80 END IF;
81
82 utl_file.put_line(g_file_handle, l_data);
83
84 hr_utility.trace('Leaving: '||l_proc);
85 END WRITE_LINE;
86
87 PROCEDURE PREPARE_FILE(p_lea_or_est_num number) AS
88 l_file_name varchar2(50);
89 l_proc constant varchar2(100):= g_package||'PREPARE_FILE';
90 BEGIN
91 hr_utility.trace('Entering: '||l_proc);
92
93 -- Bug#12586059
94
95 -- IF g_data_return_type in ('TYPE1A','TYPE3','TYPE4') THEN
96 IF g_data_return_type in ('TYPE1','TYPE3','TYPE4') THEN
97
98 -- Bug#12586059
99
100 l_file_name := 'LEA_'||p_lea_or_est_num||'.'||g_request_id;
101
102 ELSE
103 l_file_name := 'EST_'||p_lea_or_est_num||'.'||g_request_id;
104 END IF;
105 g_file_handle := utl_file.fopen (g_output_dir ,l_file_name,'w');
106 FND_FILE.PUT_LINE(fnd_file.log,'');
107 FND_FILE.PUT_LINE(fnd_file.log,'Writing File:'||l_file_name);
108 utl_file.put_line(g_file_handle, '<?xml version="1.0" encoding="'||g_iana_char_set||'"?>');
109 WRITE_LINE('O','Message',null);
110
111 hr_utility.trace('Leaving: '||l_proc);
112 END PREPARE_FILE;
113
114 PROCEDURE CLOSE_FILE AS
115 l_proc constant varchar2(100):= g_package||'CLOSE_FILE';
116 BEGIN
117 hr_utility.trace('Entering: '||l_proc);
118 WRITE_LINE('C','Message',null);
119 utl_file.fclose (g_file_handle);
120 hr_utility.trace('Leaving: '||l_proc);
121 END CLOSE_FILE;
122
123 PROCEDURE WRITE_HEADER(p_est_number number) AS
124 l_proc constant varchar2(100):= g_package||'WRITE_HEADER';
125
126 cursor c_arc_pkg_version is
127 select afv.version
128 from ad_files af,
129 ad_file_versions afv
130 where af.file_id = afv.file_id
131 and af.app_short_name = 'PQP'
132 and af.subdir = 'patch/115/sql'
133 and af.filename = 'pqpgbswfar.pkb'
134 order by afv.file_version_id desc;
135
136 l_version varchar2(100);
137
138 BEGIN
139 hr_utility.trace('Entering: '||l_proc);
140
141 WRITE_LINE('O','Header',null);
142 WRITE_LINE('O','CollectionDetails',null);
143 WRITE_LINE('D','Collection','School Workforce Census');
144 -- WRITE_LINE('D','Term','AUT'); /* Bug#12856844 */
145 WRITE_LINE('D','Year',g_census_year);
146 WRITE_LINE('D','ReferenceDate',to_char(g_census_date,'YYYY-MM-DD'));
147 WRITE_LINE('C','CollectionDetails',null);
148 WRITE_LINE('O','Source',null);
149 WRITE_LINE('D','SourceLevel','L');
150 WRITE_LINE('D','LEA',g_lea_number);
151 WRITE_LINE('D','Estab',p_est_number);
152 WRITE_LINE('D','SoftwareCode','Oracle HRMS');
153 open c_arc_pkg_version;
154 fetch c_arc_pkg_version into l_version;
155 close c_arc_pkg_version;
156 WRITE_LINE('D','Release',l_version);
157 WRITE_LINE('D','SerialNo',g_serial_number);
158 WRITE_LINE('D','DateTime',to_char(sysdate,'YYYY-MM-DD')||'T'||to_char(sysdate,'HH:MM:SS'));
159 WRITE_LINE('C','Source',null);
160 WRITE_LINE('O','Content',null);
161 WRITE_LINE('O','CBDSLevels',null);
162 IF g_data_return_type <> 'TYPE4' THEN
163 WRITE_LINE('D','CBDSLevel','Workforce');
164 END IF;
165 IF g_data_return_type = 'TYPE4' THEN
166 WRITE_LINE('D','CBDSLevel','LA');
167 END IF;
168 WRITE_LINE('C','CBDSLevels',null);
169 WRITE_LINE('C','Content',null);
170 WRITE_LINE('C','Header',null);
171
172 hr_utility.trace('Leaving: '||l_proc);
173 END WRITE_HEADER;
174
175 PROCEDURE WRITE_STAFF_DETAILS(p_person_id pay_action_information.action_information1%type,
176 p_person_category number)
177 AS
178 cursor c_staff_details is
179 select pai.action_information3 teacher_number,
180 pai.action_information4 family_name,
181 pai.action_information5 given_name,
182 pai.action_information6 former_family_name,
183 pai.action_information7 ni_number,
184 pai.action_information8 gender,
185 pai.action_information9 date_of_birth,
186 pai.action_information10 ethnicity,
187 pai.action_information11 disability,
188 max(pai.action_information12)over() QTStatus,
189 max(pai.action_information13)over() HLTAStatus,
190 max(pai.action_information14)over() QTSRoute
191 from pay_action_information pai,
192 pay_assignment_actions paa
193 where paa.payroll_action_id = g_payroll_action_id
194 and paa.assignment_action_id = pai.action_context_id
195 and pai.action_context_type = 'AAP'
196 and pai.action_information_category = 'GB_SWF_STAFF_DETAILS'
197 and pai.action_information1 = p_person_id;
198
199 l_proc constant varchar2(100):= g_package||'WRITE_STAFF_DETAILS';
200 l_HLTA_Status varchar2(10);
201 l_QT_Status varchar2(10); -- Bug#12586059
202 BEGIN
203 hr_utility.trace('Entering: '||l_proc);
204
205 FOR l_staff_rec in c_staff_details LOOP
206 WRITE_LINE('O','StaffDetails',null);
207 WRITE_LINE('D','TeacherNumber',l_staff_rec.teacher_number);
208 WRITE_LINE('O','StaffMemberName',null);
209 WRITE_LINE('D','PersonFamilyName',l_staff_rec.family_name);
210 WRITE_LINE('O','GivenNames',null);
211 WRITE_LINE('O','GivenName',null);
212 WRITE_LINE('D','PersonGivenName',l_staff_rec.given_name);
213 WRITE_LINE('C','GivenName',null);
214 WRITE_LINE('C','GivenNames',null);
215 WRITE_LINE('C','StaffMemberName',null);
216 IF (p_person_category in (1,3)) THEN
217 WRITE_LINE('O','FormerFamilyNames',null);
218 WRITE_LINE('D','PersonFamilyName',l_staff_rec.former_family_name);
219 WRITE_LINE('C','FormerFamilyNames',null);
220 END IF;
221 WRITE_LINE('D','NINumber',l_staff_rec.ni_number);
222 WRITE_LINE('D','GenderCurrent',l_staff_rec.gender);
223 WRITE_LINE('D','PersonBirthDate',l_staff_rec.date_of_birth);
224 WRITE_LINE('D','Ethnicity',l_staff_rec.ethnicity);
225 WRITE_LINE('D','Disability',l_staff_rec.disability);
226 IF (p_person_category in (1,2,3)) THEN
227
228 -- Bug#12586059
229
230 -- QT Status value changed from Yes/No to True/False
231 -- WRITE_LINE('D','QTStatus',l_staff_rec.QTStatus);
232
233 Select decode(l_staff_rec.QTStatus,'Yes','True','No','False',null)
234 into l_QT_Status
235 from dual;
236
237 WRITE_LINE('D','QTStatus',l_QT_Status);
238
239 -- Bug#12586059
240
241 END IF;
242 IF (p_person_category in (1,3,4)) THEN
243 --Fix for bug#9773083
244 --WRITE_LINE('D','HLTAStatus',l_staff_rec.HLTAStatus);
245 --Fix for bug#9773083
246 Select decode(l_staff_rec.HLTAStatus,'Yes','True','No','False',null)
247 into l_HLTA_Status
248 from dual;
249 WRITE_LINE('D','HLTAStatus',l_HLTA_Status);
250 END IF;
251 IF (p_person_category in (1,2)) THEN
252 WRITE_LINE('D','QTSRoute',l_staff_rec.QTSRoute);
253 END IF;
254 WRITE_LINE('C','StaffDetails',null);
255 EXIT; -- Do not want to display staff multiple times
256 END LOOP;
257
258 hr_utility.trace('Entering: '||l_proc);
259 END WRITE_STAFF_DETAILS;
260
261 PROCEDURE WRITE_CONTRACT_DETAILS(p_est_number pay_action_information.action_information11%type,
262 p_person_id pay_action_information.action_information1%type,
263 p_person_category number) AS
264
265 cursor c_contract_details is
266 select pai.assignment_id assignment_id,
267 pai.action_information2 contract_type,
268 pai.action_information3 contract_st_date,
269 pai.action_information4 contract_end_date,
270 pai.action_information5 post,
271 pai.action_information6 arrival_date,
272 pai.action_information7 daily_rate,
273 pai.action_information8 destination,
274 pai.action_information9 origin,
275 pai.action_information10 la_school_level,
276 pai.action_information11 est_number
277 from pay_action_information pai,
278 pay_assignment_actions paa
279 where paa.payroll_action_id = g_payroll_action_id
280 and paa.assignment_action_id = pai.action_context_id
281 and pai.action_context_type = 'AAP'
282 and pai.action_information_category = 'GB_SWF_CONTRACT_DETAILS'
283 and pai.action_information1 = p_person_id
284 and pai.action_information11 = nvl(p_est_number,pai.action_information11);
285
286 cursor c_payment_details(cp_assignment_id pay_action_information.assignment_id%type) is
287 select pai.action_information1 pay_scale,
288 pai.action_information2 regional_pay_spine,
289 pai.action_information3 spine_point,
290 pai.action_information4 salary_rate,
291 pai.action_information5 safeguarded_salary
292 from pay_action_information pai,
293 pay_assignment_actions paa
294 where paa.payroll_action_id = g_payroll_action_id
295 and paa.assignment_action_id = pai.action_context_id
296 and pai.action_context_type = 'AAP'
297 and pai.action_information_category = 'GB_SWF_PAYMENT_DETAILS'
298 and pai.assignment_id = cp_assignment_id;
299
300 cursor c_add_payment_details(cp_assignment_id pay_action_information.assignment_id%type) is
301 select pai.action_information1 cat_of_add_payment,
302 pai.action_information2 add_payment_amt
303 from pay_action_information pai,
304 pay_assignment_actions paa
305 where paa.payroll_action_id = g_payroll_action_id
306 and paa.assignment_action_id = pai.action_context_id
307 and pai.action_context_type = 'AAP'
308 and pai.action_information_category = 'GB_SWF_ADD_PAYMENT_DETAILS'
309 and pai.assignment_id = cp_assignment_id;
310
311 cursor c_hours_details(cp_assignment_id pay_action_information.assignment_id%type) is
312 select pai.action_information1 hours_worked_per_week,
313 pai.action_information2 fte_hours_per_week,
314 pai.action_information3 weeks_per_year
315 from pay_action_information pai,
316 pay_assignment_actions paa
317 where paa.payroll_action_id = g_payroll_action_id
318 and paa.assignment_action_id = pai.action_context_id
319 and pai.action_context_type = 'AAP'
320 and pai.action_information_category = 'GB_SWF_HOURS_DETAILS'
321 and pai.assignment_id = cp_assignment_id;
322
323 cursor c_role_details(cp_assignment_id pay_action_information.assignment_id%type) is
324 select pai.action_information1 role_identifier
325 from pay_action_information pai,
326 pay_assignment_actions paa
327 where paa.payroll_action_id = g_payroll_action_id
328 and paa.assignment_action_id = pai.action_context_id
329 and pai.action_context_type = 'AAP'
330 and pai.action_information_category = 'GB_SWF_ROLE_DETAILS'
331 and pai.assignment_id = cp_assignment_id;
332
333 l_proc constant varchar2(100):= g_package||'WRITE_CONTRACT_DETAILS';
334 BEGIN
335 hr_utility.trace('Entering: '||l_proc);
336
337 WRITE_LINE('O','ContractOrServiceGroup',null);
338 FOR l_contract_rec in c_contract_details LOOP
339 WRITE_LINE('O','ContractOrService',null);
340 WRITE_LINE('D','ContractType',l_contract_rec.contract_type);
341 WRITE_LINE('D','ContractStart',l_contract_rec.contract_st_date);
342 WRITE_LINE('D','ContractEnd',l_contract_rec.contract_end_date);
343 WRITE_LINE('D','Post',l_contract_rec.post);
344 IF (p_person_category in (1,2,3)) THEN
345 WRITE_LINE('D','SchoolArrivalDate',l_contract_rec.arrival_date);
346 END IF;
347 -- Bug 12906657
348 -- IF (p_person_category in (2,3)) THEN
349 IF (p_person_category = 2) THEN
350 -- Bug 12906657
351 WRITE_LINE('D','DailyRate',l_contract_rec.daily_rate);
352 END IF;
353 IF (p_person_category in (1,2)) THEN
354 WRITE_LINE('D','DestinationCode',l_contract_rec.destination);
355 END IF;
356 IF (p_person_category in (1,3)) THEN
357 WRITE_LINE('D','Origin',l_contract_rec.origin);
358 END IF;
359 WRITE_LINE('D','LASchoolLevel',l_contract_rec.la_school_level);
360 WRITE_LINE('D','Estab',l_contract_rec.est_number);
361
362 -- Start PostLevelDetails
363 WRITE_LINE('O','PostLevelDetails',null);
364 FOR l_payment_rec in c_payment_details(l_contract_rec.assignment_id) LOOP
365 WRITE_LINE('O','Payments',null);
366 IF (p_person_category in (1,2,3)) THEN
367 WRITE_LINE('D','Scale',l_payment_rec.pay_scale);
368 END IF;
369 IF (p_person_category in (1,2)) THEN
370 WRITE_LINE('D','RegionSpine',l_payment_rec.regional_pay_spine);
371 END IF;
372 IF (p_person_category in (1,2)) THEN
373 WRITE_LINE('D','SpinePoint',l_payment_rec.spine_point);
374 END IF;
375 IF (p_person_category in (1,2,3)) THEN
376
377 -- Bug#10106993
378
379 -- WRITE_LINE('D','SalaryAmount',l_payment_rec.salary_rate);
380 WRITE_LINE('D','TotalPay',l_payment_rec.salary_rate);
381
382 -- Bug#10106993
383
384 END IF;
385 IF (p_person_category in (1)) THEN
386 WRITE_LINE('D','SafeguardedSalary',l_payment_rec.safeguarded_salary);
387 END IF;
388 WRITE_LINE('C','Payments',null);
389 END LOOP;
390
391 IF (p_person_category in (1,2,3)) THEN
392 WRITE_LINE('O','AdditionalPayments',null);
393 FOR l_add_payment_rec in c_add_payment_details(l_contract_rec.assignment_id) LOOP
394 WRITE_LINE('O','AdditionalPayment',null);
395 WRITE_LINE('D','PaymentType',l_add_payment_rec.cat_of_add_payment);
396 WRITE_LINE('D','PaymentAmount',l_add_payment_rec.add_payment_amt);
397 WRITE_LINE('C','AdditionalPayment',null);
398 END LOOP;
399 WRITE_LINE('C','AdditionalPayments',null);
400 END IF;
401
402 IF (p_person_category in (1,2,3)) THEN
403 FOR l_hours_rec in c_hours_details(l_contract_rec.assignment_id) LOOP
404 WRITE_LINE('O','Hours',null);
405 WRITE_LINE('D','HoursPerWeek',l_hours_rec.hours_worked_per_week);
406 WRITE_LINE('D','FTEHours',l_hours_rec.fte_hours_per_week);
407 WRITE_LINE('D','WeeksPerYear',l_hours_rec.weeks_per_year);
408 WRITE_LINE('C','Hours',null);
409 END LOOP;
410 END IF;
411
412 WRITE_LINE('C','PostLevelDetails',null);
413 -- End PostLevelDetails
414
415 -- Start Roles
416 WRITE_LINE('O','Roles',null);
417 FOR l_role_rec in c_role_details(l_contract_rec.assignment_id) LOOP
418 WRITE_LINE('O','Role',null);
419 WRITE_LINE('D','RoleIdentifier',l_role_rec.role_identifier);
420 WRITE_LINE('C','Role',null);
421 END LOOP;
422 WRITE_LINE('C','Roles',null);
423 -- End Roles
424
425 WRITE_LINE('C','ContractOrService',null);
426 END LOOP;
427 WRITE_LINE('C','ContractOrServiceGroup',null);
428
429 hr_utility.trace('Leaving: '||l_proc);
430 END WRITE_CONTRACT_DETAILS;
431
432 PROCEDURE WRITE_ABS_DETAILS(p_est_number pay_action_information.action_information6%type,
433 p_person_id pay_action_information.action_information1%type) AS
434
435 cursor c_abs_details is
436 select pai.action_information2 first_day,
437 pai.action_information3 last_day,
438 pai.action_information4 working_days_lost,
439 pai.action_information5 abs_catagory,
440 pai.action_information6 est_number
441 from pay_action_information pai,
442 pay_assignment_actions paa
443 where paa.payroll_action_id = g_payroll_action_id
444 and paa.assignment_action_id = pai.action_context_id
445 and pai.action_context_type = 'AAP'
446 and pai.action_information_category = 'GB_SWF_ABS_DETAILS'
447 and pai.action_information1 = p_person_id
448 and pai.action_information6 = nvl(p_est_number,pai.action_information6);
449
450 l_proc constant varchar2(100):= g_package||'WRITE_ABS_DETAILS';
451 BEGIN
452 hr_utility.trace('Entering: '||l_proc);
453
454 WRITE_LINE('O','Absences',null);
455 FOR l_abs_rec in c_abs_details LOOP
456 WRITE_LINE('O','Absence',null);
457 WRITE_LINE('D','FirstDayOfAbsence',l_abs_rec.first_day);
458 WRITE_LINE('D','LastDayOfAbsence',l_abs_rec.last_day);
459 WRITE_LINE('D','WorkingDaysLost',l_abs_rec.working_days_lost);
460 WRITE_LINE('D','AbsenceCategory',l_abs_rec.abs_catagory);
461 WRITE_LINE('D','Estab',l_abs_rec.est_number);
462 WRITE_LINE('C','Absence',null);
463 END LOOP;
464 WRITE_LINE('C','Absences',null);
465
466 hr_utility.trace('Leaving: '||l_proc);
467 END WRITE_ABS_DETAILS;
468
469 PROCEDURE WRITE_QUAL_DETAILS(p_est_number pay_action_information.action_information6%type,
470 p_person_id pay_action_information.action_information1%type) AS
471
472
473 cursor c_qual_details is
474 select pai.action_information2 qual_code,
475 -- Bug 12906657
476 -- pai.action_information3 qual_verified,
477 -- pai.action_information4 qual_subject1,
478 -- pai.action_information5 qual_subject2,
479 pai.action_information5 qual_verified,
480 pai.action_information3 qual_subject1,
481 pai.action_information4 qual_subject2,
482 -- Bug 12906657
483 pai.action_information6 est_number
484 from pay_action_information pai,
485 pay_assignment_actions paa
486 where paa.payroll_action_id = g_payroll_action_id
487 and paa.assignment_action_id = pai.action_context_id
488 and pai.action_context_type = 'AAP'
489 and pai.action_information_category = 'GB_SWF_QUAL_DETAILS'
490 and pai.action_information1 = p_person_id
491 and pai.action_information6 = nvl(p_est_number,pai.action_information6);
492
493 l_proc constant varchar2(100):= g_package||'WRITE_QUAL_DETAILS';
494 BEGIN
495 hr_utility.trace('Entering: '||l_proc);
496
497 WRITE_LINE('O','Qualifications',null);
498 FOR l_qual_rec in c_qual_details LOOP
499 WRITE_LINE('O','Qualification',null);
500 WRITE_LINE('D','QualificationCode',l_qual_rec.qual_code);
501 WRITE_LINE('O','Subjects',null);
502 WRITE_LINE('D','QualificationSubject',l_qual_rec.qual_subject1);
503 WRITE_LINE('C','Subjects',null);
504 WRITE_LINE('O','Subjects',null);
505 WRITE_LINE('D','QualificationSubject',l_qual_rec.qual_subject2);
506 WRITE_LINE('C','Subjects',null);
507 -- Commented out for Bug#10106993
508 -- WRITE_LINE('D','QualVerified',l_qual_rec.qual_verified);
509 -- Bug#10106993
510 WRITE_LINE('D','Estab',l_qual_rec.est_number);
511 WRITE_LINE('C','Qualification',null);
512 END LOOP;
513 WRITE_LINE('C','Qualifications',null);
514
515 hr_utility.trace('Leaving: '||l_proc);
516 END WRITE_QUAL_DETAILS;
517
518 PROCEDURE WRITE_LA AS
519
520 cursor c_edu_psy_ft is
521 select COUNT(*) edu_psy_ft
522 from pay_action_information pai,
523 pay_assignment_actions paa
524 where paa.payroll_action_id = g_payroll_action_id
525 and paa.assignment_action_id = pai.action_context_id
526 and pai.action_context_type = 'AAP'
527 and pai.action_information_category = 'GB_SWF_ROLE_DETAILS'
528 and pai.action_information1 = 'EPSY'
529 and pai.action_information2 = 'F';
530
531 cursor c_edu_psy_pt is
532 select COUNT(*) edu_psy_pt
533 from pay_action_information pai,
534 pay_assignment_actions paa
535 where paa.payroll_action_id = g_payroll_action_id
536 and paa.assignment_action_id = pai.action_context_id
537 and pai.action_context_type = 'AAP'
538 and pai.action_information_category = 'GB_SWF_ROLE_DETAILS'
539 and pai.action_information1 = 'EPSY'
540 and pai.action_information2 = 'P';
541
542 cursor c_edu_psy_fte is
543 select sum(round(nvl(pai2.action_information7,0),1)) edu_psy_fte
544 from pay_action_information pai,
545 pay_action_information pai2,
546 pay_assignment_actions paa
547 where paa.payroll_action_id = g_payroll_action_id
548 and paa.assignment_action_id = pai.action_context_id
549 and pai.action_context_id = pai2.action_context_id
550 and pai.action_information_category = 'GB_SWF_ROLE_DETAILS'
551 and pai2.action_information_category = 'GB_SWF_HOURS_DETAILS'
552 and pai.action_information1 = 'EPSY'
553 and pai.action_information2 = 'P';
554
555 l_proc constant varchar2(100):= g_package||'WRITE_LA';
556 BEGIN
557 hr_utility.trace('Entering: '||l_proc);
558
559 OPEN c_edu_psy_ft;
560 FETCH c_edu_psy_ft INTO g_edu_psy_ft;
561 CLOSE c_edu_psy_ft;
562
563 OPEN c_edu_psy_pt;
564 FETCH c_edu_psy_pt INTO g_edu_psy_pt;
565 CLOSE c_edu_psy_pt;
566
567 OPEN c_edu_psy_fte;
568 FETCH c_edu_psy_fte INTO g_edu_psy_fte;
569 CLOSE c_edu_psy_fte;
570
571 WRITE_LINE('O','LA',null);
572 WRITE_LINE('O','EducationalPsychologists',null);
573 WRITE_LINE('D','EdPsychsFT',nvl(g_edu_psy_ft,0));
574 WRITE_LINE('D','EdPsychsPT',nvl(g_edu_psy_pt,0));
575 WRITE_LINE('D','EdPsychsFTE',round(nvl(g_edu_psy_fte,0),1));
576 WRITE_LINE('C','EducationalPsychologists',null);
577 WRITE_LINE('C','LA',null);
578
579 hr_utility.trace('Leaving: '||l_proc);
580 END WRITE_LA;
581
582 PROCEDURE XML_EXTRACT(errbuf out nocopy varchar2
583 ,retcode out nocopy varchar2
584 ,p_census_year in number
585 ,p_request_id in number
586 ,p_output_dir in varchar2
587 ,p_serial_number in number
588 ) AS
589
590 cursor c_process_details is
591 select ppa.payroll_action_id
592 ,pay_gb_eoy_archive.get_parameter(ppa.legislative_parameters,'LEA_NUM') lea_number
593 ,pay_gb_eoy_archive.get_parameter(ppa.legislative_parameters,'CENSUS_DAY') census_date
594 ,upper(pay_gb_eoy_archive.get_parameter(ppa.legislative_parameters,'DATA_RETURN_TYPE')) data_return_type
595 ,NVL(pay_gb_eoy_archive.get_parameter(ppa.legislative_parameters,'ESTB_NUM'),'All') estab_number
596 ,pay_gb_eoy_archive.get_parameter(ppa.legislative_parameters,'EXCLUDE_ABS') exclude_abs
597 ,pay_gb_eoy_archive.get_parameter(legislative_parameters,'EXCLUDE_QUAL') exclude_qual
598 from pay_payroll_actions ppa
599 where ppa.request_id = p_request_id;
600
601 cursor c_all_staff is
602 select distinct pai.action_information1 person_id
603 from pay_action_information pai,
604 pay_assignment_actions paa
605 where paa.payroll_action_id = g_payroll_action_id
606 and paa.assignment_action_id = pai.action_context_id
607 and pai.action_context_type = 'AAP'
608 and pai.action_information_category = 'GB_SWF_STAFF_DETAILS';
609
610 cursor c_all_eatab is
611 select distinct pai.action_information2 est_number
612 from pay_action_information pai,
613 pay_assignment_actions paa
614 where paa.payroll_action_id = g_payroll_action_id
615 and paa.assignment_action_id = pai.action_context_id
616 and pai.action_context_type = 'AAP'
617 and pai.action_information_category = 'GB_SWF_STAFF_DETAILS';
618
619 cursor c_estab_staff(cp_est_number pay_action_information.action_information2%type) is
620 select distinct pai.action_information1 person_id
621 from pay_action_information pai,
622 pay_assignment_actions paa
623 where paa.payroll_action_id = g_payroll_action_id
624 and paa.assignment_action_id = pai.action_context_id
625 and pai.action_context_type = 'AAP'
626 and pai.action_information_category = 'GB_SWF_STAFF_DETAILS'
627 and (
628 (cp_est_number is null AND pai.action_information2 is null)
629 OR
630 (cp_est_number is not null AND pai.action_information2 = cp_est_number)
631 );
632
633 cursor c_person_category(cp_person_id pay_action_information.action_information1%type) is
634 select min(pai.action_information14) person_category
635 from pay_action_information pai,
636 pay_assignment_actions paa
637 where paa.payroll_action_id = g_payroll_action_id
638 and paa.assignment_action_id = pai.action_context_id
639 and pai.action_context_type = 'AAP'
640 and pai.action_information_category = 'GB_SWF_CONTRACT_DETAILS'
641 and pai.action_information1 = cp_person_id;
642
643 cursor c_estab_staff_count is
644 select pai.action_information2 est_number,
645 count(distinct pai.action_information1) staff_count
646 from pay_action_information pai,
647 pay_assignment_actions paa
648 where paa.payroll_action_id = g_payroll_action_id
649 and paa.assignment_action_id = pai.action_context_id
650 and pai.action_context_type = 'AAP'
651 and pai.action_information_category ='GB_SWF_STAFF_DETAILS'
652 group by pai.action_information2;
653
654 l_proc constant varchar2(100):= g_package||'XML_EXTRACT';
655
656 l_person_category number;
657 l_estab_count number:=0;
658
659 BEGIN
660 hr_utility.trace('Entering: '||l_proc);
661
662 FND_FILE.PUT_LINE(fnd_file.log,'');
663 FND_FILE.PUT_LINE(fnd_file.log,'Parameters:');
664 FND_FILE.PUT_LINE(fnd_file.log,' Census Year: '|| p_census_year);
665 FND_FILE.PUT_LINE(fnd_file.log,' Request Id: '|| p_request_id);
666 FND_FILE.PUT_LINE(fnd_file.log,' Output Dir: '|| p_output_dir);
667 FND_FILE.PUT_LINE(fnd_file.log,' Serial Number: '|| p_serial_number);
668
669 g_census_year := p_census_year;
670 g_output_dir := p_output_dir;
671 g_request_id := fnd_global.conc_request_id;
672 g_serial_number := p_serial_number;
673
674 -- Retrive the Process Details from pay_payroll_actions
675 open c_process_details;
676 fetch c_process_details into g_payroll_action_id,g_lea_number,g_census_date,
677 g_data_return_type,g_estab_number,g_exclude_absence,g_exclude_qual;
678 close c_process_details;
679
680 FND_FILE.PUT_LINE(fnd_file.log,'');
681 FND_FILE.PUT_LINE(fnd_file.log,'Process Details:');
682 FND_FILE.PUT_LINE(fnd_file.log,' Payroll Action ID: '|| g_payroll_action_id);
683 FND_FILE.PUT_LINE(fnd_file.log,' LEA Number: '|| g_lea_number);
684 FND_FILE.PUT_LINE(fnd_file.log,' Census Date: '|| g_census_date);
685 FND_FILE.PUT_LINE(fnd_file.log,' Data Return Type: '|| g_data_return_type);
686
687 -- Get IANA Encoding
688 g_iana_char_set := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
689 IF g_iana_char_set is null THEN
690 g_iana_char_set:='UTF-8';
691 END IF;
692 FND_FILE.PUT_LINE(fnd_file.log,' IANA Encoding: '|| g_iana_char_set);
693
694 -- Bug#12586059
695
696 -- IF g_data_return_type in ('TYPE1A','TYPE3','TYPE4') THEN
697 IF g_data_return_type in ('TYPE1','TYPE3','TYPE4') THEN
698
699 -- Bug#12586059
700
701 PREPARE_FILE(g_lea_number);
702 -- Write Header
703 WRITE_HEADER(null);
704
705 -- Bug#12586059
706
707 -- IF g_data_return_type in ('TYPE1A','TYPE3') THEN
708 IF g_data_return_type in ('TYPE1','TYPE3') THEN
709
710 -- Bug#12586059
711
712 -- Write Workforce Details
713 WRITE_LINE('O','SchoolWorkforceMembers',null);
714 FOR l_staff in c_all_staff LOOP
715 WRITE_LINE('O','SchoolWorkforceMember',null);
716 -- Find the person category
717 OPEN c_person_category(l_staff.person_id);
718 FETCH c_person_category into l_person_category;
719 CLOSE c_person_category;
720 WRITE_STAFF_DETAILS(l_staff.person_id,l_person_category);
721 WRITE_CONTRACT_DETAILS(null,l_staff.person_id,l_person_category);
722 IF (l_person_category <> 4) THEN
723 WRITE_ABS_DETAILS(null,l_staff.person_id);
724 WRITE_QUAL_DETAILS(null,l_staff.person_id);
725 END IF;
726 WRITE_LINE('C','SchoolWorkforceMember',null);
727 END LOOP;
728 WRITE_LINE('C','SchoolWorkforceMembers',null);
729 END IF;
730 IF g_data_return_type = 'TYPE4' THEN
731 WRITE_LA();
732 END IF;
733 CLOSE_FILE();
734
735 -- Bug#12586059
736
737 -- ELSIF g_data_return_type in ('TYPE1B','TYPE2') THEN
738 ELSIF g_data_return_type = 'TYPE2' THEN
739
740 -- Bug#12586059
741
742 FOR l_eatab in c_all_eatab LOOP
743 PREPARE_FILE(l_eatab.est_number);
744 -- Write Header
745 WRITE_HEADER(l_eatab.est_number);
746 -- Write Workforce Details
747 WRITE_LINE('O','SchoolWorkforceMembers',null);
748 FOR l_staff in c_estab_staff(l_eatab.est_number) LOOP
749 WRITE_LINE('O','SchoolWorkforceMember',null);
750 -- Find the person category
751 OPEN c_person_category(l_staff.person_id);
752 FETCH c_person_category into l_person_category;
753 CLOSE c_person_category;
754 WRITE_STAFF_DETAILS(l_staff.person_id,l_person_category);
755 WRITE_CONTRACT_DETAILS(l_eatab.est_number,l_staff.person_id,l_person_category);
756 IF (l_person_category <> 4) THEN
757 WRITE_ABS_DETAILS(l_eatab.est_number,l_staff.person_id);
758 WRITE_QUAL_DETAILS(l_eatab.est_number,l_staff.person_id);
759 END IF;
760 WRITE_LINE('C','SchoolWorkforceMember',null);
761 END LOOP;
762 WRITE_LINE('C','SchoolWorkforceMembers',null);
763 CLOSE_FILE();
764 END LOOP;
765 END IF;
766
767 -- Write Extract Summary
768 FND_FILE.PUT_LINE(fnd_file.output,'Process Summary');
769 FND_FILE.PUT_LINE(fnd_file.output,'---------------');
770 FND_FILE.PUT_LINE(fnd_file.output,' Request ID: '|| g_request_id);
771 FND_FILE.PUT_LINE(fnd_file.output,' Census Year: '|| g_census_year);
772 FND_FILE.PUT_LINE(fnd_file.output,' LEA Number: '|| g_lea_number);
773 FND_FILE.PUT_LINE(fnd_file.output,' Data Return Type: '|| g_data_return_type);
774 FND_FILE.PUT_LINE(fnd_file.output,' Establishment Number: '|| g_estab_number);
775 FND_FILE.PUT_LINE(fnd_file.output,' Exclude Absence Module: '|| g_exclude_absence);
776 FND_FILE.PUT_LINE(fnd_file.output,' Exclude Qualification Module: '|| g_exclude_qual);
777 FND_FILE.PUT_LINE(fnd_file.output,'');
778 FND_FILE.PUT_LINE(fnd_file.output,' Output Dir: '|| g_output_dir);
779
780 -- Bug#12586059
781
782 -- IF g_data_return_type in ('TYPE1A','TYPE3','TYPE4') then
783 IF g_data_return_type in ('TYPE1','TYPE3','TYPE4') then
784
785 -- Bug#12586059
786
787 FND_FILE.PUT_LINE(fnd_file.output,' File Name: '||'LEA_'||g_lea_number||'.'||g_request_id);
788
789 -- Bug#12586059
790
791 -- ELSIF g_data_return_type in ('TYPE1B','TYPE2') then
792 ELSIF g_data_return_type = 'TYPE2' then
793
794 -- Bug#12586059
795
796 FND_FILE.PUT_LINE(fnd_file.output,' File Name(s): '||'EST_<estab_number>.'||g_request_id);
797 END IF;
798 FND_FILE.PUT_LINE(fnd_file.output,'');
799 IF (g_data_return_type <> 'TYPE4') THEN
800 FND_FILE.PUT_LINE(fnd_file.output,' Establishement Number No of Staff Processed');
801 FND_FILE.PUT_LINE(fnd_file.output,' --------------------- ---------------------');
802
803 FOR l_eatab_staff in c_estab_staff_count LOOP
804 l_estab_count := l_estab_count + 1;
805 FND_FILE.PUT(fnd_file.output, ' '|| lpad(l_eatab_staff.est_number,4));
806 IF (l_eatab_staff.est_number is not null) then
807 FND_FILE.PUT_LINE(fnd_file.output,' '||l_eatab_staff.staff_count );
808 ELSE
809 FND_FILE.PUT_LINE(fnd_file.output,' '||l_eatab_staff.staff_count );
810 END IF;
811 END LOOP;
812 FND_FILE.PUT_LINE(fnd_file.output,'');
813 FND_FILE.PUT_LINE(fnd_file.output,'');
814 FND_FILE.PUT_LINE(fnd_file.output,' Total No of Establishments processed: '|| l_estab_count);
815 ELSE
816 FND_FILE.PUT_LINE(fnd_file.output,' Full Time Educational Psychologists Count: '|| nvl(g_edu_psy_ft,0));
817 FND_FILE.PUT_LINE(fnd_file.output,' Part Time Educational Psychologists Count: '|| nvl(g_edu_psy_pt,0));
818 FND_FILE.PUT_LINE(fnd_file.output,' Total FTE- part Time Educational Psychologists: '|| round(nvl(g_edu_psy_fte,0),1));
819 END IF;
820 hr_utility.trace('Leaving: '||l_proc);
821
822 EXCEPTION
823 WHEN utl_file.invalid_path THEN
824 errbuf:='Output Directory specified is invalid. Please Verify.';
825 retcode:=2;
826
827 WHEN utl_file.invalid_operation THEN
828 errbuf:='Unable to open the file in the directory specified';
829 retcode:=2;
830
831 WHEN utl_file.write_error THEN
832 errbuf:='Unable to write to the file';
833 retcode:=2;
834
835 WHEN OTHERS THEN
836 errbuf:=SQLERRM;
837 retcode:=2;
838
839 END XML_EXTRACT;
840
841 END PQP_GB_SWF_EXTRACT;