[Home] [Help]
PACKAGE BODY: APPS.OTA_FR_TRG_SUM
Source
1 PACKAGE BODY OTA_FR_TRG_SUM AS
2 /* $Header: otfrtrgsm.pkb 120.0.12010000.2 2008/11/28 05:39:04 parusia noship $ */
3
4 vCtr NUMBER;
5 g_utf8 boolean;
6 g_catg1_t number:=0;
7 g_catg2_t number:=0;
8 g_catg3_t number:=0;
9 g_catg1_o number:=0;
10 g_catg2_o number:=0;
11 g_catg3_o number:=0;
12
13
14 function get_lookup_value(p_lookup_type varchar2,
15 p_lookup_code varchar2)return varchar2 IS
16 l_lookup_meaning varchar2(80);
17 begin
18
19 select meaning into l_lookup_meaning
20 from hr_lookups
21 where lookup_type=p_lookup_type
22 and lookup_code=p_lookup_code;
23
24 return l_lookup_meaning ;
25 end get_lookup_value;
26
27
28 function xml_d(p_data varchar2)return varchar2 is
29 l_data varchar2(1000);
30 begin
31 if g_utf8 then
32 l_data:= convert(p_data,'UTF8');
33 else
34 l_data:= p_data;
35 end if;
36 return l_data;
37 end xml_d;
38
39
40 procedure xml_t (p_xml in out nocopy clob,
41 p_tag varchar2) is
42 begin
43
44 dbms_lob.writeappend(p_xml, length(p_tag), p_tag);
45
46 end xml_t;
47
48
49
50 procedure xml_utf8(p_xml in out nocopy clob)
51 is
52 cursor csr_get_lookup(p_lookup_type varchar2
53 ,p_lookup_code varchar2
54 ,p_view_app_id number default 3) is
55 select meaning,tag
56 FROM fnd_lookup_values flv
57 WHERE lookup_type = p_lookup_type
58 AND lookup_code = p_lookup_code
59 AND language = userenv('LANG')
60 AND view_application_id = p_view_app_id
61 and SECURITY_GROUP_ID = decode(substr(userenv('CLIENT_INFO'),55,1),
62 ' ', 0,
63 NULL, 0,
64 '0', 0,
65 fnd_global.lookup_security_group(
66 FLV.LOOKUP_TYPE,FLV.VIEW_APPLICATION_ID));
67 rec_lookup csr_get_lookup%ROWTYPE;
68 --
69 begin
70 open csr_get_lookup('FND_ISO_CHARACTER_SET_MAP',
71 substr(USERENV('LANGUAGE'),instr(USERENV('LANGUAGE'),'.')+1),
72 0);
73 fetch csr_get_lookup into rec_lookup;
74 close csr_get_lookup;
75 --
76 if rec_lookup.tag is null then
77 g_utf8 := TRUE;
78 else
79 g_utf8 := FALSE;
80 end if;
81 xml_t(p_xml,'<?xml version="1.0" encoding="'||
82 nvl(rec_lookup.tag,'UTF-8')||'" ?>');
83 hr_utility.trace('<?xml version="1.0" encoding="'||
84 nvl(rec_lookup.tag,'UTF-8')||'" ?>');
85 --
86 end xml_utf8;
87
88 --
89
90 PROCEDURE POPULATE_REPORT_DATA(P_ASG_NUM IN varchar2,
91 dummy IN varchar2,
92 dummy1 IN varchar2,
93 P_COMPANY_ID IN NUMBER ,
94 P_ESTABLISHMENT_ID IN NUMBER ,
95 P_BUSINESS_GROUP_ID IN NUMBER,
96 P_ASSIGNMENT_SET_ID IN NUMBER,
97 P_PERSON_ID IN NUMBER ,
98 P_DATE_FROM IN VARCHAR2 default NULL,
99 P_DATE_TO IN VARCHAR2 default NULL,
100 P_TEMPLATE_NAME IN VARCHAR2 ,
101 P_SORT_ORDER IN VARCHAR2,
102 p_xml OUT NOCOPY CLOB
103 )IS
104
105 TYPE ref_cur IS REF CURSOR;
106 c_ref_cur ref_cur;
107
108 TYPE rec IS RECORD
109 (
110 person_id per_all_people_f.person_id%TYPE,
111 employee_number per_all_people_f.employee_number%TYPE,
112 full_name per_all_people_f.full_name%TYPE
113 );
114
115
116 cursor c_asg_amendments is
117 select hsa.include_or_exclude ioe
118 from hr_assignment_set_amendments hsa
119 where hsa.assignment_set_id =p_assignment_set_id;
120
121 p_asg_emp varchar2(30);
122 l_c_asg_amendments c_asg_amendments%ROWTYPE ;
123 l_date_from date;
124 l_date_to date;
125 l_c_asg_set rec;
126
127
128 BEGIN
129
130 -- hr_utility.trace_on(NULL,'TRG');
131 dbms_lob.createtemporary(p_xml, TRUE, dbms_lob.session);
132 dbms_lob.open(p_xml,dbms_lob.lob_readwrite);
133 xml_utf8(p_xml);
134 xml_t(p_xml,'<REPORT>');
135 l_date_from:=trunc(fnd_date.canonical_to_date(p_date_from));
136 l_date_to:=trunc(fnd_date.canonical_to_date(p_date_to));
137
138 --OTA_FR_TRG_SUM.vXMLTable.DELETE;
139 --vCtr:=0;
140
141 hr_utility.TRACE('l_date_to ' || to_char(l_date_to,'YYYY-MON-DD'));
142
143
144 if (p_asg_num='A') then
145
146 open c_asg_amendments;
147 fetch c_asg_amendments into l_c_asg_amendments;
148
149
150 if (c_asg_amendments%FOUND) then
151
152 if (l_c_asg_amendments.ioe='I' /*and l_c_asg_amendments.cnt > 0*/) then
153
154 open c_ref_cur for -- ASG 1
155 'select distinct paf.person_id person_id ,pef.employee_number employee_number,pef.full_name full_name
156 from per_all_assignments_f paf,
157 hr_assignment_sets hs,
158 per_all_people_f pef,
159 per_periods_of_service ppos
160 where hs.assignment_set_id=:p_assignment_set_id
161 and nvl(hs.payroll_id,paf.payroll_id)=paf.payroll_id
162 and hs.business_group_id=paf.business_group_id
163 and pef.person_id=paf.person_id
164 and paf.business_group_id=:p_business_group_id
165 and paf.business_group_id=pef.business_group_id
166 and paf.assignment_id in ( select assignment_id
167 from hr_assignment_set_amendments hsa
168 where hsa.assignment_set_id =:p_assignment_set_id
169 and hsa.include_or_exclude=''I'')
170 and ppos.person_id=pef.person_id
171 and ppos.period_of_service_id=paf.period_of_service_id
172 and :l_date_from <=nvl(ppos.actual_termination_date,to_date(''31-12-4712'',''DD-MM-YYYY''))
173 and :l_date_to between pef.effective_start_date and pef.effective_end_date
174 and ((:l_date_to between paf.effective_start_date and paf.effective_end_date)
175 or
176 (:l_date_to >= (select max(effective_end_date)
177 from per_all_assignments_f p1 /*To include TERMINATED employee*/
178 where p1.person_id=pef.person_id))
179 )
180 ORDER BY '||p_sort_order
181 using p_assignment_set_id,p_business_group_id,p_assignment_set_id,l_date_from,l_date_to,l_date_to,l_date_to;
182
183 -- for l_c_asg_set in c_asg_set1 ----Assignment Set Loop
184 loop
185 fetch c_ref_cur into l_c_asg_set ;
186 exit when c_ref_cur%NOTFOUND;
187 make_employee(l_c_asg_set.person_id,l_date_from,l_date_to,p_xml);
188 end loop ; ----For Assignment Set
189 end if;
190
191 if (l_c_asg_amendments.ioe='E' /* and l_c_asg_amendments.cnt > 0*/) then
192 open c_ref_cur for
193 'select distinct paf.person_id person_id ,pef.employee_number employee_number,pef.full_name full_name
194 from per_all_assignments_f paf,
195 hr_assignment_sets hs,
196 per_all_people_f pef,
197 per_periods_of_service ppos
198 where hs.assignment_set_id=:p_assignment_set_id
199 and hs.business_group_id=paf.business_group_id
200 and nvl(hs.payroll_id,paf.payroll_id)=paf.payroll_id
201 and pef.person_id=paf.person_id
202 and paf.business_group_id=:p_business_group_id
203 and paf.business_group_id=pef.business_group_id
204 and paf.assignment_id not in ( select assignment_id
205 from hr_assignment_set_amendments hsa
206 where hsa.assignment_set_id =:p_assignment_set_id
207 and hsa.include_or_exclude=''E'')
208 and ppos.person_id=pef.person_id
209 and ppos.period_of_service_id=paf.period_of_service_id
210 and :l_date_from <=nvl(ppos.actual_termination_date,to_date(''31-12-4712'',''DD-MM-YYYY''))
211 and :l_date_to between pef.effective_start_date and pef.effective_end_date
212 and ((:l_date_to between paf.effective_start_date and paf.effective_end_date)
213 or
214 (:l_date_to >= (select max(effective_end_date)
215 from per_all_assignments_f p1 /*To include TERMINATED employee*/
216 where p1.person_id=pef.person_id))
217 )
218 ORDER BY '||p_sort_order
219 using p_assignment_set_id,p_business_group_id,p_assignment_set_id,l_date_from,l_date_to,l_date_to,l_date_to;
220
221 loop
222 fetch c_ref_cur into l_c_asg_set ;
223 exit when c_ref_cur%NOTFOUND;
224 make_employee(l_c_asg_set.person_id,l_date_from,l_date_to,p_xml);
225 end loop ; ----For Assignment Set
226 end if;
227 else
228 ---- No amendment is mentioned
229 open c_ref_cur for
230 'select distinct paf.person_id ,pef.employee_number employee_number,pef.full_name full_name
231 from per_all_assignments_f paf,
232 hr_assignment_sets hs,
233 per_all_people_f pef,
234 per_periods_of_service ppos
235 where hs.assignment_set_id=:p_assignment_set_id
236 and hs.business_group_id=paf.business_group_id
237 and nvl(hs.payroll_id,paf.payroll_id)=paf.payroll_id
238 and pef.person_id=paf.person_id
239 and paf.business_group_id=:p_business_group_id
240 and paf.business_group_id=pef.business_group_id
241 and paf.assignment_id not in ( select assignment_id
242 from hr_assignment_set_amendments hsa
243 where hsa.assignment_set_id =:p_assignment_set_id
244 and hsa.include_or_exclude=''E'')
245 and ppos.person_id=pef.person_id
246 and ppos.period_of_service_id=paf.period_of_service_id
247 and :l_date_from <=nvl(ppos.actual_termination_date,to_date(''31-12-4712'',''DD-MM-YYYY''))
248 and :l_date_to between pef.effective_start_date and pef.effective_end_date
249 and ((:l_date_to between paf.effective_start_date and paf.effective_end_date)
250 or
251 (:l_date_to >= (select max(effective_end_date)
252 from per_all_assignments_f p1 /*To include TERMINATED employee*/
253 where p1.person_id=pef.person_id))
254 )
255 ORDER BY '||p_sort_order
256 using p_assignment_set_id,p_business_group_id,p_assignment_set_id,l_date_from,l_date_to,l_date_to,l_date_to;
257
258 loop
259 fetch c_ref_cur into l_c_asg_set ;
260 exit when c_ref_cur%NOTFOUND;
261 make_employee(l_c_asg_set.person_id,l_date_from,l_date_to,p_xml);
262 end loop ; ----For Assignment Set
263 end if;
264
265 close c_asg_amendments;
266 else
267
268 open c_ref_cur for
269 'Select DISTINCT pap.person_id person_id,pap.employee_number employee_number,pap.full_name full_name
270 from per_all_people_f pap,
271 per_all_assignments_f paa,
272 hr_all_organization_units hao,
273 hr_organization_information hoi,
274 per_periods_of_service ppos
275 where pap.person_id =paa.person_id
276 and pap.business_group_id=:l_business_group_id
277 and pap.business_group_id=paa.business_group_id
278 and pap.person_id=nvl(:l_person_id,pap.person_id)
279 and paa.establishment_id=nvl(:l_establishment_id,paa.establishment_id)
280 and paa.establishment_id=hao.organization_id
281 and hoi.organization_id=hao.organization_id
282 and hoi.org_information_context =''FR_ESTAB_INFO''
283 and hoi.org_information1=nvl(:l_company_id,hoi.org_information1)
284 and ppos.person_id=pap.person_id
285 and ppos.period_of_service_id=paa.period_of_service_id
286 and :l_date_from <=nvl(ppos.actual_termination_date,to_date(''31-12-4712'',''DD-MM-YYYY''))
287 and :l_date_to between pap.effective_start_date and pap.effective_end_date
288 and ((:l_date_to between paa.effective_start_date and paa.effective_end_date)
289 or
290 (:l_date_to >= (select max(effective_end_date)
291 from per_all_assignments_f p1 /*To include TERMINATED employee*/
292 where p1.person_id=pap.person_id))
293 )
294 ORDER BY '||p_sort_order
295 using
296 p_business_group_id,p_person_id,p_establishment_id,p_company_id,l_date_from,l_date_to,l_date_to,l_date_to;
297
298 loop
299
300 fetch c_ref_cur into l_c_asg_set ;
301 exit when c_ref_cur%NOTFOUND;
302 make_employee(l_c_asg_set.person_id,l_date_from,l_date_to,p_xml);
303 end loop ;
304
305 end if;
306 xml_t(p_xml,'</REPORT>');
307 --WritetoCLOB(p_xml);
308
309 END POPULATE_REPORT_DATA ;
310
311
312
313 PROCEDURE make_employee ( L_P_PERSON_ID IN NUMBER,
314 L_P_DATE_FROM IN DATE ,
315 L_P_DATE_TO IN DATE,
316 p_xml in out nocopy clob
317 )
318 IS
319
320 CURSOR c_emp_h is
321 select distinct papf.full_name EMP_NAME,
322 papf.employee_number EMP_NUM,
323 pa.address_line1||' '||pa.address_line2||' '|| pa.address_line3 ||' '|| pa.region_2 ||' '||
324 pa.region_3 ||' '|| pa.postal_code ||' '|| pa.town_or_city||' '|| pa.region_1 ||' '||pa.country
325 ||' '||pa.telephone_number_1||' '|| pa.telephone_number_2||' '||pa.telephone_number_3 E_ADDRESS,
326 to_char(papf.original_date_of_hire,'YYYY-MM-DD') HIRE_DATE,
327 to_char(ppos.adjusted_svc_date,'YYYY-MM-DD') ADJUSTED_SVC_DATE,
328 to_char(ppos.actual_termination_date,'YYYY-MM-DD') TERM_DATE,
329 OTA_FR_TRG_SUM.get_lookup_value('FR_CONTRACT_CATEGORY',pcf.ctr_information2) CONTRACT_CATEGORY,
330 pcf.type CONTRACT_TYPE,
331 case
332 when pcf.ctr_information11 is null then
333 paaf.normal_hours
334 else
335 fnd_number.canonical_to_number(pcf.ctr_information11)
336 end CONT_WRK_HRS,---CONTRACTUAL_WORKING_HOURS --It is the hours in the DDF segment with unit ,display unit also
337 case
338 when pcf.ctr_information11 is null then
339 OTA_FR_TRG_SUM.get_lookup_value('FREQUENCY',paaf.frequency)
340 else
341 OTA_FR_TRG_SUM.get_lookup_value('FR_FIXED_TIME_UNITS',pcf.ctr_information12)
342 end CONT_WRK_UNT, --Contractual working hours unit
343 pca.name COLLECTIVE_AGREEMENT,
344 paaf.assignment_category ASSGN_CATEGORY,
345 to_char(L_P_DATE_FROM,'YYYY-MM-DD') PERIOD_FROM,
346 to_char(L_P_DATE_TO,'YYYY-MM-DD') PERIOD_TO,
347 hout1.name EST_NAME,
348 hla1.address_line_1||' '||hla1.address_line_2||' '||hla1.address_line_3||' '||hla1.region_2
352 hoi1.org_information2 SIRET,
349 ||' '||hla1.region_3||' '||hla1.postal_code||' '||hla1.town_or_city||' '||hla1.region_1||' '||
350 hla1.country||' '||hla1.telephone_number_1||' '||hla1.telephone_number_2||' '||
351 hla1.telephone_number_3 EST_ADDRESS,
353 hout2.name COM_NAME,
354 hla2.address_line_1||' '||hla2.address_line_2||' '||hla2.address_line_3||' '||
355 hla2.region_2||' '||hla2.region_3||' '||hla2.postal_code||' '||hla2.town_or_city||' '||
356 hla2.region_1||' '||hla2.country||' '||hla2.telephone_number_1||' '||hla2.telephone_number_2||' '||
357 hla2.telephone_number_3 COM_ADDRESS
358 from per_all_people_f papf,
359 per_addresses pa,
360 per_all_assignments_f paaf,
361 per_periods_of_service ppos,
362 per_contracts_f pcf,
363 per_collective_agreements pca,
364 hr_all_organization_units hou1,
365 hr_all_organization_units_tl hout1,
366 hr_locations_all hla1,
367 hr_organization_information hoi1,
368 hr_all_organization_units hou2,
369 hr_all_organization_units_tl hout2,
370 hr_locations_all hla2
371 where papf.person_id=l_p_person_id
372 and pa.person_id(+)=papf.person_id
373 and pa.primary_flag(+)='Y'
374 and pa.business_group_id(+)=papf.business_group_id
375 and paaf.business_group_id=paaf.business_group_id
376 and paaf.person_id=papf.person_id
377 and ppos.person_id=papf.person_id
378 and ppos.period_of_service_id=paaf.period_of_service_id
379 and paaf.contract_id=pcf.contract_id(+)
380 and paaf.person_id=pcf.person_id(+)
381 and nvl(paaf.collective_agreement_id,-1)=pca.collective_agreement_id(+)
382 and paaf.establishment_id=hou1.organization_id
383 and hout1.organization_id=hou1.organization_id
384 and hout1.language=userenv('lang')
385 and nvl(hou1.location_id,-1)=hla1.location_id
386 and hoi1.organization_id=hou1.organization_id
387 and hoi1.org_information_context='FR_ESTAB_INFO'
388 and hoi1.org_information1=hout2.organization_id
389 and hout2.language=userenv('lang')
390 and hou2.organization_id=hout2.organization_id
391 and nvl(hou2.location_id,-1)=hla2.location_id(+)
392 and l_p_date_to between papf.effective_start_date and papf.effective_end_date
393 and ((l_p_date_to between paaf.effective_start_date and paaf.effective_end_date)
394 or
395 (l_p_date_to >= (select max(effective_end_date)
396 from per_all_assignments_f p1 /*To include TERMINATED employee*/
397 where p1.person_id=papf.person_id))
398 )
399 and l_p_date_to between pcf.effective_start_date and pcf.effective_end_date;
400
401
402
403 cursor c_emp_w_trg is /* Cursor for Within Training Plan*/
404 select distinct odb.booking_id C_W_REF, -- Enrollment reference
405 obst.name ENR_STATUS ,--Enrollment Status
406 oeventl.title C_W_NAME , --Class Name
407 to_char(oevent.course_start_date,'YYYY-MM-DD') C_W_S_DAT ,--Course Start Date
408 to_char(oevent.course_end_date,'YYYY-MM-DD') C_W_E_DAT , --Course End date
409 odb.source_of_booking ENR_SOURCE , --Source
410 odb.failure_reason FAIL_REASON, --Failure Reason of Attendance
411 decode(otmt.tp_measurement_code,'FR_ACTUAL_HOURS',
412 case otpc.tp_cost_information3
413 when 'JOB_ADAPT' then
414 'I-'||OTA_FR_TRG_SUM.get_lookup_value('FR_LEGAL_TRG_CATG',otpc.tp_cost_information3)
415 when 'JOB_EVOL' then
416 'II-'||OTA_FR_TRG_SUM.get_lookup_value('FR_LEGAL_TRG_CATG',otpc.tp_cost_information3)
417 when 'COMP_DEV' then
418 'III-'||OTA_FR_TRG_SUM.get_lookup_value('FR_LEGAL_TRG_CATG',otpc.tp_cost_information3)
419 end) C_W_L_CAT,--Legal category
420 fnd_number.canonical_to_number(otpc.tp_cost_information4) C_W_O_HR , --Hours Outside working
421 decode(otmt.tp_measurement_code,'FR_ACTUAL_HOURS',nvl(otpc.amount,0),
422 nvl(fnd_number.canonical_to_number(otpc.tp_cost_information3),0))C_W_TOT_HR --Total Hours
423 from per_all_people_f papf,
424 ota_delegate_bookings odb,
425 ota_booking_status_types obst,
426 ota_events oevent,
427 ota_events_tl oeventl,
428 ota_training_plan_costs otpc,
429 ota_tp_measurement_types otmt
430 where papf.person_id=l_p_person_id
431 and papf.person_id=odb.delegate_person_id
432 and odb.booking_status_type_id=obst.booking_status_type_id
433 and oevent.event_id=odb.event_id
434 and oeventl.event_id=oevent.event_id
435 and oeventl.language=userenv('lang')
436 and odb.booking_id=otpc.booking_id
437 --and otpc.training_plan_id=otp.training_plan_id
438 and otpc.tp_measurement_type_id = otmt.tp_measurement_type_id
439 and otmt.business_group_id=odb.business_group_id
440 and otmt.tp_measurement_code in ('FR_ACTUAL_HOURS','FR_SKILLS_ASSESSMENT','FR_VAE')
441 and oevent.course_start_date between L_P_DATE_FROM and L_P_DATE_TO
442 -- or oevent.course_end_date<=L_P_DATE_TO
443 and l_p_date_to between papf.effective_start_date and papf.effective_end_date
444 order by C_W_S_DAT asc;
445
446
447 cursor c_emp_w_trg_tot is
448 select sum(decode(otmt.tp_measurement_code,'FR_ACTUAL_HOURS',otpc.amount,
449 fnd_number.canonical_to_number(nvl(otpc.tp_cost_information3,0)))) W_TOT_HOURS ,
450 sum(fnd_number.canonical_to_number(nvl(otpc.tp_cost_information4,0))) W_TOT_OUT_WK_HR,
451 decode(otmt.tp_measurement_code,'FR_ACTUAL_HOURS',
452 otpc.tp_cost_information3,
453 ' ') LEGAL_CATG --i)JOB_ADAPT ii) JOB_EVOL , iii)COMP_DEV
454 from per_all_people_f papf,
455 ota_delegate_bookings odb,
456 ota_training_plan_costs otpc,
457 ota_tp_measurement_types otmt,
458 ota_events oevent
459 where papf.person_id=l_p_person_id
460 and papf.person_id=odb.delegate_person_id
461 and odb.booking_id=otpc.booking_id
465 and oevent.course_start_date between L_P_DATE_FROM and L_P_DATE_TO
462 and otpc.tp_measurement_type_id = otmt.tp_measurement_type_id
463 and otmt.tp_measurement_code in ('FR_ACTUAL_HOURS','FR_SKILLS_ASSESSMENT','FR_VAE')
464 and oevent.event_id=odb.event_id
466 --and oevent.course_end_date<=L_P_DATE_TO
467 and l_p_date_to between papf.effective_start_date and papf.effective_end_date
468 group by decode(otmt.tp_measurement_code,'FR_ACTUAL_HOURS',
469 otpc.tp_cost_information3,
470 ' ');
471
472
473
474 cursor c_emp_o_trg is /* Cursor for Outside Training plan*/
475 select distinct paat.name ABS_TYPE,-- Absence Type
476 to_char(paa.DATE_START,'YYYY-MM-DD') C_O_S_DAT, --Start Date
477 to_char(paa.DATE_END,'YYYY-MM-DD') C_O_E_DAT, --End date
478 fnd_number.canonical_to_number(nvl(paa.ABSENCE_DAYS,0)) DURATION_DAYS, --Duration days
479 fnd_number.canonical_to_number(nvl(paa.ABSENCE_HOURS,0)) C_O_TOT_HR, --Duration hours
480 OTA_FR_TRG_SUM.get_lookup_value('FR_TRAINING_LEAVE_CATEGORY',paa.ABS_INFORMATION1) TRG_LEAV_CATG,-- Training Leave Category
481 paa.ABS_INFORMATION2 C_O_NAME, --Course
482 pv.VENDOR_NAME TRG_PROV,--Training Provider
483 OTA_FR_TRG_SUM.get_lookup_value('FR_TRAINING_TYPE',paa.ABS_INFORMATION4) TYPE_OF_TRG, --Type Of Training
484 paa.ABS_INFORMATION17 C_O_REF, --Training reference
485 paa.ABS_INFORMATION18 WITH_TRG_PLAN, --Within Training Plan
486 case paa.ABS_INFORMATION19
487 when 'JOB_ADAPT' then
488 'I-'||OTA_FR_TRG_SUM.get_lookup_value('FR_LEGAL_TRG_CATG',paa.ABS_INFORMATION19)
489 when 'JOB_EVOL' then
490 'II-'||OTA_FR_TRG_SUM.get_lookup_value('FR_LEGAL_TRG_CATG',paa.ABS_INFORMATION19)
491 when 'COMP_DEV' then
492 'III-'||OTA_FR_TRG_SUM.get_lookup_value('FR_LEGAL_TRG_CATG',paa.ABS_INFORMATION19)
493 end C_O_L_CAT, ---Legal Category
494 fnd_number.canonical_to_number(nvl(paa.ABS_INFORMATION20,0)) C_O_O_HR, --Hours Outside Working Hours
495 OTA_FR_TRG_SUM.get_lookup_value('FR_TRAINING_SUBSIDY_TYPE',paa.ABS_INFORMATION5) SUBSI_TYPE, --Subsidized Type
496 hfov.name SUBSI_ORG --Subsidizing Organization
497 from per_all_people_f papf,
498 per_absence_attendances paa,
499 per_absence_attendance_types paat,
500 po_vendors pv,
501 HR_FR_OPCA_V hfov
502 where papf.person_id=l_p_person_id
503 and papf.person_id=paa.person_id
504 and papf.business_group_id=paa.business_group_id
505 and paa.ABSENCE_ATTENDANCE_TYPE_ID=paat.ABSENCE_ATTENDANCE_TYPE_ID
506 and paat.ABSENCE_CATEGORY='TRAINING_ABSENCE'
507 and paa.ABS_INFORMATION_CATEGORY='FR_TRAINING_ABSENCE'
508 and paa.ABS_INFORMATION18='N'
509 and paa.ABS_INFORMATION19 is not null
510 and paa.ABS_INFORMATION3=pv.vendor_id(+)
511 and paa.ABS_INFORMATION6=hfov.organization_id(+)
512 and paa.date_start between L_P_DATE_FROM and L_P_DATE_TO
513 --and paa.date_end<=L_P_DATE_TO
514 and l_p_date_to between papf.effective_start_date and papf.effective_end_date
515 order by C_O_S_DAT asc;
516
517
518 cursor c_emp_o_trg_tot is
519 Select sum(fnd_number.canonical_to_number(nvl(paa.ABSENCE_HOURS,0))) O_TOT_HOURS ,
520 sum(fnd_number.canonical_to_number(nvl(paa.ABS_INFORMATION20,0))) O_TOT_OUT_WK_HR,
521 (paa.ABS_INFORMATION19) LEGAL_CATG --i)JOB_ADAPT ii) JOB_EVOL , iii)COMP_DEV
522 from per_all_people_f papf,
523 per_absence_attendances paa
524 where papf.person_id=l_p_person_id
525 and papf.person_id=paa.person_id
526 and papf.business_group_id=paa.business_group_id
527 and paa.ABS_INFORMATION_CATEGORY='FR_TRAINING_ABSENCE' -- Absence Should be Training Leave category
528 and paa.ABS_INFORMATION18='N' -- Within Training Plan should be 'NO'
529 and paa.ABS_INFORMATION19 is not null --Legal category can not be null
530 and paa.date_start between L_P_DATE_FROM and L_P_DATE_TO
531 -- and paa.date_end<=L_P_DATE_TO
532 and l_p_date_to between papf.effective_start_date and papf.effective_end_date
533 group by paa.ABS_INFORMATION19;
534
535
536
537 l_catg23_t number :=0;
538 l_catg23_o number :=0;
539 l_catg23_t_w number :=0;
540 l_catg23_o_w number :=0;
541
542
543 BEGIN
544
545
546 for l_c_emp_h in c_emp_h --Employee Header
547 loop
548
549 xml_t(p_xml,'<RECORD>'); ---Start tag of an Employee
550 xml_t(p_xml,'<EMP_NAME>'||xml_d(l_c_emp_h.EMP_NAME)||'</EMP_NAME>');
551 xml_t(p_xml,'<EMP_NUM>'||xml_d(l_c_emp_h.EMP_NUM)||'</EMP_NUM>');
552 xml_t(p_xml,'<E_ADDRESS>'||xml_d(l_c_emp_h.E_ADDRESS)||'</E_ADDRESS>');
553 xml_t(p_xml,'<HIRE_DATE>'||xml_d(l_c_emp_h.HIRE_DATE)||'</HIRE_DATE>');
554 xml_t(p_xml,'<ADJUSTED_SVC_DATE>'||xml_d(l_c_emp_h.ADJUSTED_SVC_DATE)||'</ADJUSTED_SVC_DATE>');
555 xml_t(p_xml,'<TERM_DATE>'||xml_d(l_c_emp_h.TERM_DATE)||'</TERM_DATE>');
556 xml_t(p_xml,'<CONTRACT_CATEGORY>'||xml_d(l_c_emp_h.CONTRACT_CATEGORY)||'</CONTRACT_CATEGORY>');
557 xml_t(p_xml,'<CONTRACT_TYPE>'||xml_d(l_c_emp_h.CONTRACT_TYPE)||'</CONTRACT_TYPE>');
558 xml_t(p_xml,'<COLLECTIVE_AGREEMENT>'||xml_d(l_c_emp_h.COLLECTIVE_AGREEMENT)||'</COLLECTIVE_AGREEMENT>');
559 xml_t(p_xml,'<ASSGN_CATEGORY>'||xml_d(l_c_emp_h.ASSGN_CATEGORY)||'</ASSGN_CATEGORY>');
560 xml_t(p_xml,'<PERIOD_FROM>'||xml_d(l_c_emp_h.PERIOD_FROM)||'</PERIOD_FROM>');
561 xml_t(p_xml,'<PERIOD_TO>'||xml_d(l_c_emp_h.PERIOD_TO)||'</PERIOD_TO>');
562 xml_t(p_xml,'<EST_NAME>'||xml_d(l_c_emp_h.EST_NAME)||'</EST_NAME>');
563 xml_t(p_xml,'<EST_ADDRESS>'||xml_d(l_c_emp_h.EST_ADDRESS)||'</EST_ADDRESS>');
564 xml_t(p_xml,'<SIRET>'||xml_d(l_c_emp_h.SIRET)||'</SIRET>');
565 xml_t(p_xml,'<COM_NAME>'||xml_d(l_c_emp_h.COM_NAME)||'</COM_NAME>');
566 xml_t(p_xml,'<COM_ADDRESS>'||xml_d(l_c_emp_h.COM_ADDRESS)||'</COM_ADDRESS>');
567
568 hr_utility.trace('<RECORD>'); ---Start tag of an Employee
569 hr_utility.trace('<EMP_NAME>'||xml_d(l_c_emp_h.EMP_NAME)||'</EMP_NAME>');
573 hr_utility.trace('<ADJUSTED_SVC_DATE>'||xml_d(l_c_emp_h.ADJUSTED_SVC_DATE)||'</ADJUSTED_SVC_DATE>');
570 hr_utility.trace('<EMP_NUM>'||xml_d(l_c_emp_h.EMP_NUM)||'</EMP_NUM>');
571 hr_utility.trace('<E_ADDRESS>'||xml_d(l_c_emp_h.E_ADDRESS)||'</E_ADDRESS>');
572 hr_utility.trace('<HIRE_DATE>'||xml_d(l_c_emp_h.HIRE_DATE)||'</HIRE_DATE>');
574 hr_utility.trace('<TERM_DATE>'||xml_d(l_c_emp_h.TERM_DATE)||'</TERM_DATE>');
575 hr_utility.trace('<CONTRACT_CATEGORY>'||xml_d(l_c_emp_h.CONTRACT_CATEGORY)||'</CONTRACT_CATEGORY>');
576 hr_utility.trace('<CONTRACT_TYPE>'||xml_d(l_c_emp_h.CONTRACT_TYPE)||'</CONTRACT_TYPE>');
577 hr_utility.trace('<COLLECTIVE_AGREEMENT>'||xml_d(l_c_emp_h.COLLECTIVE_AGREEMENT)||'</COLLECTIVE_AGREEMENT>');
578 hr_utility.trace('<ASSGN_CATEGORY>'||xml_d(l_c_emp_h.ASSGN_CATEGORY)||'</ASSGN_CATEGORY>');
579 hr_utility.trace('<PERIOD_FROM>'||xml_d(l_c_emp_h.PERIOD_FROM)||'</PERIOD_FROM>');
580 hr_utility.trace('<PERIOD_TO>'||xml_d(l_c_emp_h.PERIOD_TO)||'</PERIOD_TO>');
581 hr_utility.trace('<EST_NAME>'||xml_d(l_c_emp_h.EST_NAME)||'</EST_NAME>');
582 hr_utility.trace('<EST_ADDRESS>'||xml_d(l_c_emp_h.EST_ADDRESS)||'</EST_ADDRESS>');
583 hr_utility.trace('<SIRET>'||xml_d(l_c_emp_h.SIRET)||'</SIRET>');
584 hr_utility.trace('<COM_NAME>'||xml_d(l_c_emp_h.COM_NAME)||'</COM_NAME>');
585 hr_utility.trace('<COM_ADDRESS>'||xml_d(l_c_emp_h.COM_ADDRESS)||'</COM_ADDRESS>');
586
587 for l_c_emp_w_trg in c_emp_w_trg --- Courses Within Training Plan
588 loop
589
590 xml_t(p_xml,'<W_CLASS>');
591 xml_t(p_xml,'<C_W_NAME>'||xml_d(l_c_emp_w_trg.C_W_NAME)||'</C_W_NAME>');
592 xml_t(p_xml,'<ENR_STATUS>'||xml_d(l_c_emp_w_trg.ENR_STATUS)||'</ENR_STATUS>');
593 xml_t(p_xml,'<C_W_E_DAT>'||xml_d(l_c_emp_w_trg.C_W_E_DAT)||'</C_W_E_DAT>');
594 xml_t(p_xml,'<C_W_S_DAT>'||xml_d(l_c_emp_w_trg.C_W_S_DAT)||'</C_W_S_DAT>');
595 xml_t(p_xml,'<ENR_SOURCE>'||xml_d(l_c_emp_w_trg.ENR_SOURCE)||'</ENR_SOURCE>');
596 xml_t(p_xml,'<C_W_TOT_HR>'||xml_d(l_c_emp_w_trg.C_W_TOT_HR)||'</C_W_TOT_HR>');
597 xml_t(p_xml,'<C_W_O_HR>'||xml_d(l_c_emp_w_trg.C_W_O_HR)||'</C_W_O_HR>');
598 xml_t(p_xml,'<FAIL_REASON>'||xml_d(l_c_emp_w_trg.FAIL_REASON)||'</FAIL_REASON>');
599 xml_t(p_xml,'<C_W_REF>'||xml_d(l_c_emp_w_trg.C_W_REF)||'</C_W_REF>');
600 xml_t(p_xml,'<C_W_L_CAT>'||xml_d(l_c_emp_w_trg.C_W_L_CAT)||'</C_W_L_CAT>');
601 xml_t(p_xml,'</W_CLASS>');
602
603
604 hr_utility.trace('<W_CLASS>');
605 hr_utility.trace('<C_W_NAME>'||xml_d(l_c_emp_w_trg.C_W_NAME)||'</C_W_NAME>');
606 hr_utility.trace('<ENR_STATUS>'||xml_d(l_c_emp_w_trg.ENR_STATUS)||'</ENR_STATUS>');
607 hr_utility.trace('<C_W_E_DAT>'||xml_d(l_c_emp_w_trg.C_W_E_DAT)||'</C_W_E_DAT>');
608 hr_utility.trace('<C_W_S_DAT>'||xml_d(l_c_emp_w_trg.C_W_S_DAT)||'</C_W_S_DAT>');
609 hr_utility.trace('<ENR_SOURCE>'||xml_d(l_c_emp_w_trg.ENR_SOURCE)||'</ENR_SOURCE>');
610 hr_utility.trace('<C_W_TOT_HR>'||xml_d(l_c_emp_w_trg.C_W_TOT_HR)||'</C_W_TOT_HR>');
611 hr_utility.trace('<C_W_O_HR>'||xml_d(l_c_emp_w_trg.C_W_O_HR)||'</C_W_O_HR>');
612 hr_utility.trace('<FAIL_REASON>'||xml_d(l_c_emp_w_trg.FAIL_REASON)||'</FAIL_REASON>');
613 hr_utility.trace('<C_W_REF>'||xml_d(l_c_emp_w_trg.C_W_REF)||'</C_W_REF>');
614 hr_utility.trace('<C_W_L_CAT>'||xml_d(l_c_emp_w_trg.C_W_L_CAT)||'</C_W_L_CAT>');
615 hr_utility.trace('</W_CLASS>');
616 end loop;
617
618
619 for l_c_emp_w_trg_tot in c_emp_w_trg_tot --Category Total for Courses Within Training Plan
620 loop
621
622 if l_c_emp_w_trg_tot.LEGAL_CATG = 'JOB_ADAPT' then
623 g_catg1_t :=l_c_emp_w_trg_tot.W_TOT_HOURS+g_catg1_t ; -- ii) JOB_EVOL , iii)COMP_DEV
624 g_catg1_o :=l_c_emp_w_trg_tot.W_TOT_OUT_WK_HR+g_catg1_o ;
625 xml_t(p_xml,'<c_w_c1>'||xml_d(l_c_emp_w_trg_tot.W_TOT_HOURS)||'</c_w_c1>');
626 xml_t(p_xml,'<c_w_o_c1>'||xml_d(l_c_emp_w_trg_tot.W_TOT_OUT_WK_HR)||'</c_w_o_c1>');
627
628 hr_utility.trace('<c_w_c1>'||xml_d(l_c_emp_w_trg_tot.W_TOT_HOURS)||'</c_w_c1>');
629 hr_utility.trace('<c_w_o_c1>'||xml_d(l_c_emp_w_trg_tot.W_TOT_OUT_WK_HR)||'</c_w_o_c1>');
630 else
631 if l_c_emp_w_trg_tot.LEGAL_CATG = 'JOB_EVOL' then
632 g_catg2_t :=l_c_emp_w_trg_tot.W_TOT_HOURS+g_catg2_t ; -- ii) JOB_EVOL , iii)COMP_DEV
633 g_catg2_o :=l_c_emp_w_trg_tot.W_TOT_OUT_WK_HR+g_catg2_o ;
634 l_catg23_t_w :=l_c_emp_w_trg_tot.W_TOT_HOURS+l_catg23_t_w ; -- ii) JOB_EVOL , iii)COMP_DEV
635 l_catg23_o_w :=l_c_emp_w_trg_tot.W_TOT_OUT_WK_HR+l_catg23_o_w ;
636 xml_t(p_xml,'<c_w_c2>'||xml_d(l_c_emp_w_trg_tot.W_TOT_HOURS)||'</c_w_c2>');
637 xml_t(p_xml,'<c_w_o_c2>'||xml_d(l_c_emp_w_trg_tot.W_TOT_OUT_WK_HR)||'</c_w_o_c2>');
638
639 hr_utility.trace('<c_w_c2>'||xml_d(l_c_emp_w_trg_tot.W_TOT_HOURS)||'</c_w_c2>');
640 hr_utility.trace('<c_w_o_c2>'||xml_d(l_c_emp_w_trg_tot.W_TOT_OUT_WK_HR)||'</c_w_o_c2>');
641 else
642 if l_c_emp_w_trg_tot.LEGAL_CATG = 'COMP_DEV' then
643 g_catg3_t :=l_c_emp_w_trg_tot.W_TOT_HOURS+g_catg3_t ; -- ii) JOB_EVOL , iii)COMP_DEV
644 g_catg3_o :=l_c_emp_w_trg_tot.W_TOT_OUT_WK_HR+g_catg3_o ;
645 l_catg23_t_w :=l_c_emp_w_trg_tot.W_TOT_HOURS+l_catg23_t_w ; -- ii) JOB_EVOL , iii)COMP_DEV
646 l_catg23_o_w :=l_c_emp_w_trg_tot.W_TOT_OUT_WK_HR+l_catg23_o_w ;
647 xml_t(p_xml,'<c_w_c3>'||xml_d(l_c_emp_w_trg_tot.W_TOT_HOURS)||'</c_w_c3>');
648 xml_t(p_xml,'<c_w_o_c3>'||xml_d(l_c_emp_w_trg_tot.W_TOT_OUT_WK_HR)||'</c_w_o_c3>');
649
650 hr_utility.trace('<c_w_c3>'||xml_d(l_c_emp_w_trg_tot.W_TOT_HOURS)||'</c_w_c3>');
651 hr_utility.trace('<c_w_o_c3>'||xml_d(l_c_emp_w_trg_tot.W_TOT_OUT_WK_HR)||'</c_w_o_c3>');
652 end if;
653 end if;
654 end if;
655
656
657
658 /* l_catg23_t_w:=0;
659 l_catg23_o_w:=0;*/
660
661 end loop;
662 xml_t(p_xml,'<c_w_c23>'||xml_d(l_catg23_t_w)||'</c_w_c23>');
663 xml_t(p_xml,'<c_w_o_c23>'||xml_d(l_catg23_o_w)||'</c_w_o_c23>');
664 hr_utility.trace('<c_w_c23>'||xml_d(l_catg23_t_w)||'</c_w_c23>');
665 hr_utility.trace('<c_w_o_c23>'||xml_d(l_catg23_o_w)||'</c_w_o_c23>');
669 xml_t(p_xml,'<O_CLASS>');
666 for l_c_emp_o_trg in c_emp_o_trg --- Courses Outside Training Plan
667 loop
668
670 xml_t(p_xml,'<C_O_NAME>'||xml_d(l_c_emp_o_trg.C_O_NAME)||'</C_O_NAME>');
671 xml_t(p_xml,'<ABS_TYPE>'||xml_d(l_c_emp_o_trg.ABS_TYPE)||'</ABS_TYPE>');
672 xml_t(p_xml,'<C_O_E_DAT>'||xml_d(l_c_emp_o_trg.C_O_E_DAT)||'</C_O_E_DAT>');
673 xml_t(p_xml,'<C_O_S_DAT>'||xml_d(l_c_emp_o_trg.C_O_S_DAT)||'</C_O_S_DAT>');
674 xml_t(p_xml,'<DURATION_DAYS>'||xml_d(l_c_emp_o_trg.DURATION_DAYS)||'</DURATION_DAYS>');
675 xml_t(p_xml,'<C_O_TOT_HR>'||xml_d(l_c_emp_o_trg.C_O_TOT_HR)||'</C_O_TOT_HR>');
676 xml_t(p_xml,'<TRG_LEAV_CATG>'||xml_d(l_c_emp_o_trg.TRG_LEAV_CATG)||'</TRG_LEAV_CATG>');
677 xml_t(p_xml,'<TRG_PROV>'||xml_d(l_c_emp_o_trg.TRG_PROV)||'</TRG_PROV>');
678 xml_t(p_xml,'<TYPE_OF_TRG>'||xml_d(l_c_emp_o_trg.TYPE_OF_TRG)||'</TYPE_OF_TRG>');
679 xml_t(p_xml,'<C_O_REF>'||xml_d(l_c_emp_o_trg.C_O_REF)||'</C_O_REF>');
680 xml_t(p_xml,'<WITH_TRG_PLAN>'||xml_d(l_c_emp_o_trg.WITH_TRG_PLAN)||'</WITH_TRG_PLAN>');
681 xml_t(p_xml,'<C_O_L_CAT>'||xml_d(l_c_emp_o_trg.C_O_L_CAT)||'</C_O_L_CAT>');
682 xml_t(p_xml,'<C_O_O_HR>'||xml_d(l_c_emp_o_trg.C_O_O_HR)||'</C_O_O_HR>');
683 xml_t(p_xml,'<SUBSI_TYPE>'||xml_d(l_c_emp_o_trg.SUBSI_TYPE)||'</SUBSI_TYPE>');
684 xml_t(p_xml,'<SUBSI_ORG>'||xml_d(l_c_emp_o_trg.SUBSI_ORG)||'</SUBSI_ORG>');
685 xml_t(p_xml,'</O_CLASS>');
686
687
688 hr_utility.trace('<O_CLASS>');
689 hr_utility.trace('<C_O_NAME>'||xml_d(l_c_emp_o_trg.C_O_NAME)||'</C_O_NAME>');
690 hr_utility.trace('<ABS_TYPE>'||xml_d(l_c_emp_o_trg.ABS_TYPE)||'</ABS_TYPE>');
691 hr_utility.trace('<C_O_E_DAT>'||xml_d(l_c_emp_o_trg.C_O_E_DAT)||'</C_O_E_DAT>');
692 hr_utility.trace('<C_O_S_DAT>'||xml_d(l_c_emp_o_trg.C_O_S_DAT)||'</C_O_S_DAT>');
693 hr_utility.trace('<DURATION_DAYS>'||xml_d(l_c_emp_o_trg.DURATION_DAYS)||'</DURATION_DAYS>');
694 hr_utility.trace('<C_O_TOT_HR>'||xml_d(l_c_emp_o_trg.C_O_TOT_HR)||'</C_O_TOT_HR>');
695 hr_utility.trace('<TRG_LEAV_CATG>'||xml_d(l_c_emp_o_trg.TRG_LEAV_CATG)||'</TRG_LEAV_CATG>');
696 hr_utility.trace('<TRG_PROV>'||xml_d(l_c_emp_o_trg.TRG_PROV)||'</TRG_PROV>');
697 hr_utility.trace('<TYPE_OF_TRG>'||xml_d(l_c_emp_o_trg.TYPE_OF_TRG)||'</TYPE_OF_TRG>');
698 hr_utility.trace('<C_O_REF>'||xml_d(l_c_emp_o_trg.C_O_REF)||'</C_O_REF>');
699 hr_utility.trace('<WITH_TRG_PLAN>'||xml_d(l_c_emp_o_trg.WITH_TRG_PLAN)||'</WITH_TRG_PLAN>');
700 hr_utility.trace('<C_O_L_CAT>'||xml_d(l_c_emp_o_trg.C_O_L_CAT)||'</C_O_L_CAT>');
701 hr_utility.trace('<C_O_O_HR>'||xml_d(l_c_emp_o_trg.C_O_O_HR)||'</C_O_O_HR>');
702 hr_utility.trace('<SUBSI_TYPE>'||xml_d(l_c_emp_o_trg.SUBSI_TYPE)||'</SUBSI_TYPE>');
703 hr_utility.trace('<SUBSI_ORG>'||xml_d(l_c_emp_o_trg.SUBSI_ORG)||'</SUBSI_ORG>');
704 hr_utility.trace('</O_CLASS>');
705
706
707
708 end loop;
709
710 for l_c_emp_o_trg_tot in c_emp_o_trg_tot --Category Total for Courses outside Training Plan
711 loop
712
713 if l_c_emp_o_trg_tot.LEGAL_CATG = 'JOB_ADAPT' then
714 g_catg1_t :=l_c_emp_o_trg_tot.O_TOT_HOURS+g_catg1_t ; -- ii) JOB_EVOL , iii)COMP_DEV
715 g_catg1_o :=l_c_emp_o_trg_tot.O_TOT_OUT_WK_HR+g_catg1_o ;
716 xml_t(p_xml,'<c_o_c1>'||xml_d(l_c_emp_o_trg_tot.O_TOT_HOURS)||'</c_o_c1>');
717 xml_t(p_xml,'<c_o_o_c1>'||xml_d(l_c_emp_o_trg_tot.O_TOT_OUT_WK_HR)||'</c_o_o_c1>');
718
719 hr_utility.trace('<c_o_c1>'||xml_d(l_c_emp_o_trg_tot.O_TOT_HOURS)||'</c_o_c1>');
720 hr_utility.trace('<c_o_o_c1>'||xml_d(l_c_emp_o_trg_tot.O_TOT_OUT_WK_HR)||'</c_o_o_c1>');
721 else
722 if l_c_emp_o_trg_tot.LEGAL_CATG = 'JOB_EVOL' then
723 g_catg2_t :=l_c_emp_o_trg_tot.O_TOT_HOURS+g_catg2_t ; -- ii) JOB_EVOL , iii)COMP_DEV
724 g_catg2_o :=l_c_emp_o_trg_tot.O_TOT_OUT_WK_HR+g_catg2_o ;
725
726 l_catg23_t :=l_c_emp_o_trg_tot.O_TOT_HOURS+l_catg23_t ; -- ii) JOB_EVOL , iii)COMP_DEV
727 l_catg23_o :=l_c_emp_o_trg_tot.O_TOT_OUT_WK_HR+l_catg23_o ;
728
729 xml_t(p_xml,'<c_o_c2>'||xml_d(l_c_emp_o_trg_tot.O_TOT_HOURS)||'</c_o_c2>');
730 xml_t(p_xml,'<c_o_o_c2>'||xml_d(l_c_emp_o_trg_tot.O_TOT_OUT_WK_HR)||'</c_o_o_c2>');
731
732 hr_utility.trace('<c_o_c2>'||xml_d(l_c_emp_o_trg_tot.O_TOT_HOURS)||'</c_o_c2>');
733 hr_utility.trace('<c_o_o_c2>'||xml_d(l_c_emp_o_trg_tot.O_TOT_OUT_WK_HR)||'</c_o_o_c2>');
734 else
735 if l_c_emp_o_trg_tot.LEGAL_CATG = 'COMP_DEV' then
736 g_catg3_t :=l_c_emp_o_trg_tot.O_TOT_HOURS+g_catg3_t ; -- ii) JOB_EVOL , iii)COMP_DEV
737 g_catg3_o :=l_c_emp_o_trg_tot.O_TOT_OUT_WK_HR+g_catg3_o ;
738
739 l_catg23_t :=l_c_emp_o_trg_tot.O_TOT_HOURS+l_catg23_t ; -- ii) JOB_EVOL , iii)COMP_DEV
740 l_catg23_o :=l_c_emp_o_trg_tot.O_TOT_OUT_WK_HR+l_catg23_o ;
741
742 xml_t(p_xml,'<c_o_c3>'||xml_d(l_c_emp_o_trg_tot.O_TOT_HOURS)||'</c_o_c3>');
743 xml_t(p_xml,'<c_o_o_c3>'||xml_d(l_c_emp_o_trg_tot.O_TOT_OUT_WK_HR)||'</c_o_o_c3>');
744
745 hr_utility.trace('<c_o_c3>'||xml_d(l_c_emp_o_trg_tot.O_TOT_HOURS)||'</c_o_c3>');
746 hr_utility.trace('<c_o_o_c3>'||xml_d(l_c_emp_o_trg_tot.O_TOT_OUT_WK_HR)||'</c_o_o_c3>');
747 end if;
748 end if;
749 end if;
750
751 /* l_catg23_t:=0;
752 l_catg23_o:=0;*/
753
754 end loop;
755 xml_t(p_xml,'<c_o_c23>'||xml_d(l_catg23_t)||'</c_o_c23>');
756 xml_t(p_xml,'<c_o_o_c23>'||xml_d(l_catg23_o)||'</c_o_o_c23>');
757
758 hr_utility.trace('<c_o_c23>'||xml_d(l_catg23_t)||'</c_o_c23>');
759 hr_utility.trace('<c_o_o_c23>'||xml_d(l_catg23_o)||'</c_o_o_c23>');
760
761 xml_t(p_xml,'<g_catg1_t>'||xml_d(g_catg1_t)||'</g_catg1_t>');
762 xml_t(p_xml,'<g_catg2_t>'||xml_d(g_catg2_t)||'</g_catg2_t>');
763 xml_t(p_xml,'<g_catg3_t>'||xml_d(g_catg3_t)||'</g_catg3_t>');
764 xml_t(p_xml,'<g_catg1_o>'||xml_d(g_catg1_o)||'</g_catg1_o>');
765 xml_t(p_xml,'<g_catg2_o>'||xml_d(g_catg2_o)||'</g_catg2_o>');
766 xml_t(p_xml,'<g_catg3_o>'||xml_d(g_catg3_o)||'</g_catg3_o>');
767 xml_t(p_xml,'<g_catg23_t>'||xml_d(g_catg2_t+g_catg3_t)||'</g_catg23_t>');
768 xml_t(p_xml,'<g_catg23_o>'||xml_d(g_catg2_o+g_catg3_o)||'</g_catg23_o>');
769
770 hr_utility.trace('<g_catg1_t>'||xml_d(g_catg1_t)||'</g_catg1_t>');
771 hr_utility.trace('<g_catg2_t>'||xml_d(g_catg2_t)||'</g_catg2_t>');
772 hr_utility.trace('<g_catg3_t>'||xml_d(g_catg3_t)||'</g_catg3_t>');
773 hr_utility.trace('<g_catg1_o>'||xml_d(g_catg1_o)||'</g_catg1_o>');
774 hr_utility.trace('<g_catg2_o>'||xml_d(g_catg2_o)||'</g_catg2_o>');
775 hr_utility.trace('<g_catg3_o>'||xml_d(g_catg3_o)||'</g_catg3_o>');
776 hr_utility.trace('<g_catg23_t>'||xml_d(g_catg2_t+g_catg3_t)||'</g_catg23_t>');
777 hr_utility.trace('<g_catg23_o>'||xml_d(g_catg2_o+g_catg3_o)||'</g_catg23_o>');
778
779 g_catg1_t :=0;
780 g_catg2_t :=0;
781 g_catg3_t :=0;
782 g_catg1_o :=0;
783 g_catg2_o :=0;
784 g_catg3_o :=0;
785 xml_t(p_xml,'</RECORD>');
786 hr_utility.TRACE('</RECORD>');
787 end loop;
788
789 END make_employee;
790
791
792 end OTA_FR_TRG_SUM;
793
794