[Home] [Help]
PACKAGE BODY: APPS.OTFR2483
Source
1 PACKAGE BODY otfr2483 AS
2 /* $Header: otfr2483.pkb 120.9 2006/09/19 18:20:08 aparkes noship $ */
3 --
4 procedure trace_sql (p_sql IN VARCHAR2) is
5 l_start number;
6 l_len number;
7 begin
8 l_start := 1;
9 hr_utility.trace('/* The ('||lengthb(p_sql)||
10 ' byte) SQL being executed is: */');
11 loop
12 l_len := instr(p_sql||'
13 ','
14 ',l_start) - l_start;
15 hr_utility.trace(substr(p_sql,l_start,l_len));
16 l_start := l_start + l_len +1;
17 exit when l_start > length(p_sql);
18 end loop;
19 hr_utility.trace('/* end SQL */');
20 end trace_sql;
21 --
22 procedure load_xml (p_xml in out nocopy clob,
23 p_data varchar2) is
24 begin
25 dbms_lob.writeappend(p_xml, length(p_data), p_data);
26 end load_xml;
27 --
28 procedure load_xml_declaration(p_xml in out nocopy clob)
29 is
30 cursor csr_get_lookup(p_lookup_type varchar2
31 ,p_lookup_code varchar2
32 ,p_view_app_id number default 3) is
33 select meaning,tag
34 FROM fnd_lookup_values flv
35 WHERE lookup_type = p_lookup_type
36 AND lookup_code = p_lookup_code
37 AND language = userenv('LANG')
38 AND view_application_id = p_view_app_id
39 and SECURITY_GROUP_ID = decode(substr(userenv('CLIENT_INFO'),55,1),
40 ' ', 0,
41 NULL, 0,
42 '0', 0,
43 fnd_global.lookup_security_group(
44 FLV.LOOKUP_TYPE,FLV.VIEW_APPLICATION_ID));
45 rec_lookup csr_get_lookup%ROWTYPE;
46 --
47 begin
48 open csr_get_lookup('FND_ISO_CHARACTER_SET_MAP',
49 substr(USERENV('LANGUAGE'),instr(USERENV('LANGUAGE'),'.')+1),
50 0);
51 fetch csr_get_lookup into rec_lookup;
52 close csr_get_lookup;
53 --
54 load_xml(p_xml,'<?xml version="1.0" encoding="'||rec_lookup.tag||'" ?>
55 ');
56 --
57 end load_xml_declaration;
58 --
59 procedure load_xml (p_xml in out nocopy clob,
60 p_node varchar2,
61 p_open_not_close boolean,
62 p_attribs varchar2 default null) is
63 begin
64 if p_open_not_close is null then
65 load_xml (p_xml,'<'||p_node||rtrim(' '||p_attribs)||'/>
66 ');
67 elsif p_open_not_close then
68 load_xml (p_xml,'<'||p_node||rtrim(' '||p_attribs)||'>
69 ');
70 else
71 load_xml (p_xml,'</'||p_node||'>
72 ');
73 end if;
74 end load_xml;
75 --
76 procedure load_xml (p_xml in out nocopy clob,
77 p_node varchar2,
78 p_data varchar2,
79 p_attribs varchar2 default null)
80 is
81 l_data varchar2(2000);
82 begin
83 /* Handle special characters in data */
84 l_data := REPLACE (p_data, '&', '&');
85 l_data := REPLACE (l_data, '>', '>');
86 l_data := REPLACE (l_data, '<', '<');
87 l_data := REPLACE (l_data, '''', ''');
88 l_data := REPLACE (l_data, '"', '"');
89 load_xml (p_xml,'<'||p_node||rtrim(' '||p_attribs)||'>'||
90 l_data||'</'||p_node||'>
91 ');
92 end load_xml;
93 --
94 procedure load_xml (p_xml in out nocopy clob,
95 p_node varchar2,
96 p_data number,
97 p_attribs varchar2 default null) is
98 begin
99 load_xml(p_xml
100 ,'<'||p_node||rtrim(' '||p_attribs)||'>'||
101 fnd_number.number_to_canonical(p_data)
102 ||'</'||p_node||'>
103 ');
104 end load_xml;
105 --
106 procedure load_xml (p_xml in out nocopy clob,
107 p_node varchar2,
108 p_data date,
109 p_attribs varchar2 default null) is
110 begin
111 load_xml (p_xml,'<'||p_node||rtrim(' '||p_attribs)||'>'||
112 to_char(p_data,'YYYY-MM-DD')||'</'||p_node||'>
113 ');
114 end load_xml;
115 --
116 FUNCTION get_dif_balance(p_assignment_id IN NUMBER,
117 p_accrual_plan_id IN NUMBER,
118 p_payroll_id IN NUMBER,
119 p_business_group_id IN NUMBER,
120 p_end_date IN DATE) RETURN NUMBER IS
121 --
122 l_bal_accrual number;
123 l_start_date date;
124 l_End_Date date;
125 l_Accrual_End_Date date;
126 l_accrual number;
127 --
128 BEGIN
129 --
130 per_accrual_calc_functions.get_net_accrual(
131 p_assignment_id => p_assignment_id,
132 p_plan_id => p_accrual_plan_id,
133 p_payroll_id => p_payroll_id,
134 p_business_group_id => p_business_group_id,
135 p_calculation_date => p_end_date,
136 p_accrual_start_date => TRUNC(p_end_date,'YEAR'),
137 p_start_date => l_start_date,
138 p_End_Date => l_End_Date,
139 p_Accrual_End_Date => l_Accrual_End_Date,
140 p_accrual => l_accrual,
141 p_net_entitlement => l_bal_accrual);
142 --
143 RETURN l_bal_accrual;
144 --
145 END get_dif_balance;
146 --
147 procedure build_XML (P_COMPANY_ID IN NUMBER,
148 P_YEAR IN NUMBER,
149 P_DATE_TO IN VARCHAR2 DEFAULT NULL,
150 P_DETAIL_SECTION IN VARCHAR2,
151 P_TEMPLATE_NAME IN VARCHAR2 DEFAULT NULL,
152 p_xml OUT NOCOPY CLOB) is
153 --
154 TYPE t_ref_cursor IS REF CURSOR;
155 l_ref_csr t_ref_cursor;
156 --
157 /* Bulk fetches from dynamic cursors not supported in 8.1.7; use a record: */
158 TYPE t_rec is RECORD
159 (full_name per_all_people_f.full_name%TYPE,
160 order_name per_all_people_f.order_name%TYPE,
161 emp_num per_all_people_f.employee_number%TYPE,
162 trn_start date,
163 trn_end date,
164 class_name ota_events_tl.title%TYPE,
165 plan_name ota_training_plans.name%TYPE,
166 supplier po_vendors.vendor_name%TYPE,
167 leave_cat hr_lookups.meaning%TYPE,
168 legal_cat hr_lookups.meaning%TYPE,
169 act_hrs_chr varchar2(150),
170 out_hrs_chr varchar2(150),
171 chr1 varchar2(150),
172 num1 number,
173 num2 number,
174 num3 number,
175 num4 number,
176 num5 number,
177 num6 number,
178 num7 number,
179 num8 number,
180 num9 number,
181 num10 number,
182 num11 number,
183 num12 number,
184 num13 number);
185 /* Bulk fetches from dynamic cursors not supported in 8.1.7; dont use tables
186 TYPE t_char_tbl is TABLE of varchar2(2000) INDEX by BINARY_INTEGER;
187 TYPE t_date_tbl is TABLE of date INDEX by BINARY_INTEGER;
188 TYPE t_num_tbl is TABLE of number INDEX by BINARY_INTEGER;
189 tbl_full_name t_char_tbl;
190 tbl_order_name t_char_tbl;
191 tbl_emp_num t_char_tbl;
192 tbl_trn_start t_date_tbl;
193 tbl_trn_end t_date_tbl;
194 tbl_class_name t_char_tbl;
195 tbl_plan_name t_char_tbl;
196 tbl_supplier t_char_tbl;
197 tbl_legal_cat t_char_tbl;
198 tbl_leave_cat t_char_tbl;
199 tbl_act_hrs_chr t_char_tbl;
200 tbl_out_hrs_chr t_char_tbl;
201 tbl_num1 t_num_tbl;
202 tbl_num2 t_num_tbl;
203 tbl_num3 t_num_tbl;
204 tbl_num4 t_num_tbl;
205 tbl_num5 t_num_tbl;
206 tbl_num6 t_num_tbl;
207 tbl_num7 t_num_tbl;*/
208 -- Records for selecting debug data into for all sections:
209 l_curr_rec t_rec;
210 l_prev_rec t_rec;
211 l_empt_rec t_rec; -- Empty record for re-initialising previous two
212 -- "Lexical" parameters
213 L_SELECT_OUTER varchar2(3000);
214 L_SELECT_INNER1 varchar2(10000);
215 L_SELECT_INNER2 varchar2(3000);
216 L_WHERE_INNER1 varchar2(2000);
217 L_WHERE_INNER2 varchar2(2000);
218 L_WHERE_TP_ORG varchar2(400);
219 L_GROUP_INNER1 varchar2(2000);
220 L_ORDER_BY varchar2(2000);
221 --
222 l_sql varchar2(29000);
223 --
224 c_OpenGrpTag constant boolean := TRUE;
225 c_CloseGrpTag constant boolean := FALSE;
226 c_EmptyTag constant boolean := NULL;
227 --
228 l_year_start date := to_date(p_year||'0101','yyyymmdd');
229 l_year_end date := to_date(p_year||'1231','yyyymmdd');
230 -- variables for pdf layout and running debug totals:
231 l_tot_trn_sal number;
232 l_tot_admin_sal number;
233 l_tot_run_costs number;
234 l_tot_trn_tran number;
235 l_tot_trn_accom number;
236 l_tot_other number;
237 l_total number;
238 l_tot_act_hrs number;
239 l_tot_out_hrs number;
240 l_NOMBRE number; -- A
241 l_b11 number; -- B2a
242 l_b12 number; -- B2b
243 l_b13 number; -- B2c
244 l_b14 number; -- B2d
245 l_b15 number; -- B2e
246 l_b16 number; -- B2f
247 l_b17 number; -- B2g
248 l_b18 number; -- B2h
249 l_b21 number; -- B3a
250 l_b22 number; -- B3b
251 l_b23 number; -- B3c
252 l_b24 number; -- B3d
253 l_b25 number; -- B3e
254 l_b26 number; -- B3f
255 l_b27 number; -- B3g
256 l_b28 number; -- B3h
257 l_b31 number; -- B4a
258 l_b32 number; -- B4b
259 l_b33 number; -- B4c
260 l_b34 number; -- B4d
261 l_b35 number; -- B4e
262 l_b36 number; -- B4f
263 l_b37 number; -- B4g
264 l_b38 number; -- B4h
265 l_b41 number; -- B5a
266 l_b42 number; -- B5b
267 l_b43 number; -- B5c
268 l_b44 number; -- B5d
269 l_b45 number; -- B5e
270 l_b46 number; -- B5f
271 l_b47 number; -- B5g
272 l_b48 number; -- B5h
273 l_c1 number; -- B7
274 l_c2 number; -- B8
275 l_c3 number; -- B9
276 l_c4 number; -- B10
277 l_c5 number; -- B11
278 l_c6 number; -- B12
279 l_C91 number; -- Fa
280 l_x1 number; -- Fb Contracted
281 l_x2 number; -- Fb Skills Assessment
282 l_x3 number; -- Fb VAE
283 l_C111 number; -- Fc
284 l_C121 number; -- Fd
285 l_C151 number; -- Fh
286 l_er_cif_contrib_rate number;
287 l_er_alternance_contrib_rate number;
288 l_er_tp_contrib_rate number;
289 l_currency_rate_type varchar2(100);
290 --
291 cursor csr_measurement_types(p_bg_id number) is
292 select
293 max(decode(tmt.tp_measurement_code,
294 'FR_DEDUCT_EXT_TRN_PLAN',TMT.tp_measurement_type_id))
295 DEDUCTIBLE_EXT_TRN_PLAN
296 ,max(decode(tmt.tp_measurement_code,
297 'FR_DEDUCT_EXT_TRN_PLAN_SA',TMT.tp_measurement_type_id))
298 DEDUCTIBLE_EXT_TRN_PLAN_SA
299 ,max(decode(tmt.tp_measurement_code,
300 'FR_DEDUCT_EXT_TRN_PLAN_VAE',TMT.tp_measurement_type_id))
301 DEDUCTIBLE_EXT_TRN_PLAN_VAE
302 ,max(decode(tmt.tp_measurement_code,
303 'FR_OTHER_PLAN_DEDUCT_COSTS',TMT.tp_measurement_type_id))
304 OTHER_PLAN_DEDUCTIBLE_COSTS
305 ,max(decode(tmt.tp_measurement_code,
306 'FR_DEDUCT_TRAINER_SALARY',TMT.tp_measurement_type_id))
307 DEDUCTIBLE_TRAINER_SALARY
308 ,max(decode(tmt.tp_measurement_code,
309 'FR_DEDUCT_ADMIN_SALARY',TMT.tp_measurement_type_id))
310 DEDUCTIBLE_ADMIN_SALARY
311 ,max(decode(tmt.tp_measurement_code,
312 'FR_DEDUCT_RUNNING_COSTS',TMT.tp_measurement_type_id))
313 DEDUCTIBLE_RUNNING_COSTS
314 ,max(decode(tmt.tp_measurement_code,
315 'FR_DEDUCT_TRAINER_TRANSPRT',TMT.tp_measurement_type_id))
316 DEDUCTIBLE_TRAINER_TRANSPRT
317 ,max(decode(tmt.tp_measurement_code,
318 'FR_DEDUCT_TRAINER_ACCOM',TMT.tp_measurement_type_id))
319 DEDUCTIBLE_TRAINER_ACCOM
320 ,max(decode(tmt.tp_measurement_code,
321 'FR_DEDUCT_EXT_TRN_CLASS',TMT.tp_measurement_type_id))
322 DEDUCTIBLE_EXT_TRN_CLASS
323 ,max(decode(tmt.tp_measurement_code,
324 'FR_OTHER_CLASS_DEDUCT_COST',TMT.tp_measurement_type_id))
325 OTHER_CLASS_DEDUCTIBLE_COST
326 ,max(decode(tmt.tp_measurement_code,
327 'FR_ACTUAL_HOURS',TMT.tp_measurement_type_id)) ACTUAL_HOURS
328 ,max(decode(tmt.tp_measurement_code,
329 'FR_SKILLS_ASSESSMENT',TMT.tp_measurement_type_id))
330 SKILLS_ASSESSMENT
331 ,max(decode(tmt.tp_measurement_code,
332 'FR_VAE',TMT.tp_measurement_type_id)) VAE
333 ,max(decode(tmt.tp_measurement_code,
334 'FR_DEDUCT_LEARNER_SALARY',TMT.tp_measurement_type_id))
335 DEDUCTIBLE_LEARNER_SALARY
336 ,max(decode(tmt.tp_measurement_code,
337 'FR_DEDUCT_TRN_ALLOWANCE',TMT.tp_measurement_type_id))
338 DEDUCTIBLE_TRN_ALLOWANCE
339 ,max(decode(tmt.tp_measurement_code,
340 'FR_OTHER_LEARN_DEDUCT_INT',TMT.tp_measurement_type_id))
341 OTHER_LEARN_DEDUCT_COST_INT
342 ,max(decode(tmt.tp_measurement_code,
343 'FR_OTHER_LEARN_DEDUCT_EXT',TMT.tp_measurement_type_id))
344 OTHER_LEARN_DEDUCT_COST_EXT
345 from ota_tp_measurement_types tmt
346 where TMT.business_group_id = p_bg_id
347 and tmt.tp_measurement_code in ('FR_DEDUCT_EXT_TRN_PLAN',
348 'FR_DEDUCT_EXT_TRN_PLAN_SA',
349 'FR_DEDUCT_EXT_TRN_PLAN_VAE',
350 'FR_OTHER_PLAN_DEDUCT_COSTS',
351 'FR_DEDUCT_TRAINER_SALARY',
352 'FR_DEDUCT_ADMIN_SALARY',
353 'FR_DEDUCT_RUNNING_COSTS',
354 'FR_DEDUCT_TRAINER_TRANSPRT',
355 'FR_DEDUCT_TRAINER_ACCOM',
356 'FR_DEDUCT_EXT_TRN_CLASS',
357 'FR_OTHER_CLASS_DEDUCT_COST',
358 'FR_ACTUAL_HOURS',
359 'FR_SKILLS_ASSESSMENT',
360 'FR_VAE',
361 'FR_DEDUCT_LEARNER_SALARY',
362 'FR_DEDUCT_TRN_ALLOWANCE',
363 'FR_OTHER_LEARN_DEDUCT_INT',
364 'FR_OTHER_LEARN_DEDUCT_EXT')
365 and ((tmt.tp_measurement_code in ('FR_DEDUCT_EXT_TRN_PLAN',
366 'FR_DEDUCT_EXT_TRN_PLAN_SA',
367 'FR_DEDUCT_EXT_TRN_PLAN_VAE',
368 'FR_OTHER_PLAN_DEDUCT_COSTS') and
369 TMT.cost_level = 'PLAN') or
370 (tmt.tp_measurement_code in ('FR_DEDUCT_TRAINER_SALARY',
371 'FR_DEDUCT_ADMIN_SALARY',
372 'FR_DEDUCT_RUNNING_COSTS',
373 'FR_DEDUCT_TRAINER_TRANSPRT',
374 'FR_DEDUCT_TRAINER_ACCOM',
375 'FR_DEDUCT_EXT_TRN_CLASS',
376 'FR_OTHER_CLASS_DEDUCT_COST') and
377 TMT.cost_level = 'EVENT') or
378 (tmt.tp_measurement_code in ('FR_ACTUAL_HOURS',
379 'FR_SKILLS_ASSESSMENT',
380 'FR_VAE',
381 'FR_DEDUCT_LEARNER_SALARY',
382 'FR_DEDUCT_TRN_ALLOWANCE',
383 'FR_OTHER_LEARN_DEDUCT_INT',
384 'FR_OTHER_LEARN_DEDUCT_EXT') and
385 TMT.cost_level = 'DELEGATE'))
386 and ((tmt.tp_measurement_code = 'FR_ACTUAL_HOURS' and
387 tmt.unit = 'N') or
388 (tmt.tp_measurement_code <> 'FR_ACTUAL_HOURS' and
389 tmt.unit = 'M'));
390 --
391 l_meas_types_rec csr_measurement_types%ROWTYPE;
392 --
393 cursor csr_classifications is
394 select
395 max(decode(pri_class.classification_name
396 ,'Information',pri_class.classification_id)) inf_pri_cls_id
397 ,max(decode(pri_class.classification_name
398 ,'Absence',pri_class.classification_id)) abs_pri_cls_id
399 ,max(decode(pri_class.classification_name
400 ,'Information',sub_class.classification_id)) dif_inf_sub_cls_id
401 ,max(decode(pri_class.classification_name
402 ,'Absence',sub_class.classification_id)) dif_abs_sub_cls_id
403 from pay_element_classifications pri_class,
404 pay_element_classifications sub_class
405 where pri_class.classification_name in ('Information','Absence')
406 and pri_class.business_group_id is null
407 and pri_class.legislation_code = 'FR'
408 and sub_class.parent_classification_id = pri_class.classification_id
409 and sub_class.classification_name = 'DIF Absence : '||
410 pri_class.classification_name
411 and sub_class.business_group_id is null
412 and sub_class.legislation_code = 'FR';
413 --
414 l_classification_rec csr_classifications%ROWTYPE;
415 --
416 cursor csr_header is
417 select
418 greatest(comp.date_from,l_year_start) date1
419 ,least(nvl(comp.date_to,l_year_end),l_year_end) date2
420 ,substr(tax_office_loc.address_line_1,1,45) ad1
421 ,substr(tax_office_loc.address_line_2,1,45) ad2
422 ,substr(tax_office_loc.region_3,1,45) ad3
423 ,substr(tax_office_loc.postal_code||' '||
424 tax_office_loc.town_or_city,1,45) ad4
425 ,substr(comp_tl.name,1,45) ad5
426 ,substr(comp_loc.address_line_1,1,45) ad6
427 ,substr(ltrim(rtrim(comp_loc.address_line_2||', '||
428 comp_loc.region_3,', '),', '),1,45) ad7
429 ,substr(comp_loc.postal_code||' '||
430 comp_loc.town_or_city,1,45) ad8
431 ,comp_2483_info.org_information2 recette
432 ,comp_2483_info.org_information3 dossier
433 ,comp_2483_info.org_information4 cle
434 ,comp_2483_info.org_information5 regime
435 ,comp_2483_info.org_information6 impot
436 ,substr(hq_info.org_information2,1,9) siret1
437 ,substr(hq_info.org_information2,10,5) code
438 ,nvl(comp_info.org_information2,hq_info.org_information3) ape
439 ,comp_2483_info.org_information7 intermittent_and_homeworkers
440 ,comp_2483_info.org_information8 tp_level
441 ,comp.business_group_id bg_id
442 ,ceil(months_between(
443 decode(
444 least(nvl(comp.date_to,l_year_end),l_year_end),
445 last_day(least(nvl(comp.date_to,l_year_end),l_year_end)),
446 least(nvl(comp.date_to,l_year_end),l_year_end),
447 trunc(least(nvl(comp.date_to,l_year_end),l_year_end),'MM')),
448 greatest(comp.date_from,l_year_start))) comp_active_mths_in_yr
449 from
450 hr_all_organization_units comp,
451 hr_organization_information comp_2483_info,
452 hr_all_organization_units tax_office,
453 hr_locations_all tax_office_loc,
454 hr_all_organization_units_TL comp_tl,
455 hr_locations_all comp_loc,
456 hr_organization_information comp_info,
457 hr_organization_information HQ_info
458 where comp.organization_id = p_company_id
459 and comp.date_from <= l_year_end
460 and (comp.date_to is null or
461 comp.date_to >= l_year_start)
462 and comp_2483_info.org_information_context(+) = 'FR_COMP_2483_INFO'
463 and comp_2483_info.organization_id (+) = comp.organization_id
464 and tax_office.organization_id(+) = comp_2483_info.org_information1
465 and tax_office_loc.location_id(+) = tax_office.location_id
466 and comp_tl.organization_id = comp.organization_id
467 and comp_tl.language = USERENV('LANG')
468 and comp_loc.location_id(+) = comp.location_id
469 and comp_info.org_information_context(+) = 'FR_COMP_INFO'
470 and comp_info.organization_id (+) = comp.organization_id
471 and HQ_info.organization_id (+) = comp_info.org_information5
472 and HQ_info.org_information_context(+) = 'FR_ESTAB_INFO';
473 --
474 l_header_rec csr_header%ROWTYPE;
475 --
476 cursor csr_comp_training_contrib_info(p_effective_date_chr varchar2) is
477 select org_information3 reduction_chr
478 from hr_organization_information tng_cntrib
479 where tng_cntrib.organization_id = p_company_id
480 and tng_cntrib.org_information_context = 'FR_COMP_TRAINING_CONTRIB'
481 and p_effective_date_chr between tng_cntrib.org_information1
482 and nvl(tng_cntrib.org_information2
483 ,p_effective_date_chr);
484 --
485 l_training_contrib_rec csr_comp_training_contrib_info%ROWTYPE;
486 --
487 begin
488 dbms_lob.createtemporary(p_xml, TRUE, dbms_lob.session);
489 dbms_lob.open(p_xml,dbms_lob.lob_readwrite);
490 load_xml_declaration(p_xml);
491 load_xml(p_xml,'FIELDS',c_OpenGrpTag);
492 --
493 open csr_header;
494 fetch csr_header into l_header_rec;
495 close csr_header;
496 --
497 if p_detail_section = 'NA' then
498 -- write XML for pdf Header
499 -- (setting date1 and date2 to null if company active all year)
500 if l_header_rec.date1 <> l_year_start
501 or l_header_rec.date2 <> l_year_end
502 then
503 load_xml(p_xml,'date1',to_char(l_header_rec.date1,'dd/mm/yyyy'));
504 load_xml(p_xml,'date2',to_char(l_header_rec.date2,'dd/mm/yyyy'));
505 end if;
506 load_xml(p_xml,'ad1',l_header_rec.ad1);
507 load_xml(p_xml,'ad2',l_header_rec.ad2);
508 load_xml(p_xml,'ad3',l_header_rec.ad3);
509 load_xml(p_xml,'ad4',l_header_rec.ad4);
510 load_xml(p_xml,'ad5',l_header_rec.ad5);
511 load_xml(p_xml,'ad6',l_header_rec.ad6);
512 load_xml(p_xml,'ad7',l_header_rec.ad7);
513 load_xml(p_xml,'ad8',l_header_rec.ad8);
514 load_xml(p_xml,'recette',l_header_rec.recette);
515 load_xml(p_xml,'dossier',l_header_rec.dossier);
516 load_xml(p_xml,'cle',l_header_rec.cle);
517 load_xml(p_xml,'regime',l_header_rec.regime);
518 load_xml(p_xml,'impot',l_header_rec.impot);
519 load_xml(p_xml,'siret1',l_header_rec.siret1);
520 load_xml(p_xml,'code',l_header_rec.code);
521 load_xml(p_xml,'ape',l_header_rec.ape);
522 --
523 -- get the currency rate type for conversions.
524 l_currency_rate_type := hr_currency_pkg.get_rate_type(l_header_rec.bg_id
525 ,sysdate,'R');
526 --
527 else
528 -- write XML for rtf Header
529 load_xml(p_xml,'date',sysdate);
530 load_xml(p_xml,'HEADER',c_OpenGrpTag);
531 load_xml(p_xml,'COMPANY_NAME',l_header_rec.ad5);
532 load_xml(p_xml,'DATE_FROM',l_header_rec.date1);
533 load_xml(p_xml,'DATE_TO',l_header_rec.date2);
534 load_xml(p_xml,'DETAIL_SECTION',
535 hr_general.decode_lookup('FR_2483_DEBUG_SECTIONS',
536 p_detail_section));
537 load_xml(p_xml,'HEADER',c_CloseGrpTag);
538 end if;
539 --
540 if l_header_rec.tp_level = 'ESTAB' then
541 L_WHERE_TP_ORG := '
542 and tp_org_info.org_information_context = ''FR_ESTAB_INFO''
543 and tp_org_info.org_information1 = to_char(comp.organization_id)';
544 else -- default to Company level
545 L_WHERE_TP_ORG := '
546 and tp_org_info.org_information_context = ''FR_COMP_INFO''
547 and tp_org_info.organization_id = comp.organization_id';
548 end if;
549 --
550 open csr_measurement_types(l_header_rec.bg_id);
551 fetch csr_measurement_types into l_meas_types_rec;
552 close csr_measurement_types;
553 --
554 if p_detail_section in ('A','NA') then
555 hr_utility.trace('comp_active_mths_in_yr: '||
556 l_header_rec.comp_active_mths_in_yr);
557 if p_detail_section = 'NA' then
558 hr_utility.trace('Section A PDF');
559 L_SELECT_OUTER := 'select
560 round((
561 trunc(nvl(sum(decode(emp_mth.mth_num,1 ,mth_count)),0))+
562 trunc(nvl(sum(decode(emp_mth.mth_num,2 ,mth_count)),0))+
563 trunc(nvl(sum(decode(emp_mth.mth_num,3 ,mth_count)),0))+
564 trunc(nvl(sum(decode(emp_mth.mth_num,4 ,mth_count)),0))+
565 trunc(nvl(sum(decode(emp_mth.mth_num,5 ,mth_count)),0))+
566 trunc(nvl(sum(decode(emp_mth.mth_num,6 ,mth_count)),0))+
567 trunc(nvl(sum(decode(emp_mth.mth_num,7 ,mth_count)),0))+
568 trunc(nvl(sum(decode(emp_mth.mth_num,8 ,mth_count)),0))+
569 trunc(nvl(sum(decode(emp_mth.mth_num,9 ,mth_count)),0))+
570 trunc(nvl(sum(decode(emp_mth.mth_num,10,mth_count)),0))+
571 trunc(nvl(sum(decode(emp_mth.mth_num,11,mth_count)),0))+
572 trunc(nvl(sum(decode(emp_mth.mth_num,12,mth_count)),0)))/
573 :num_comp_months) NOMBRE
574 FROM (';
575 L_ORDER_BY := ') emp_mth';
576 else
577 hr_utility.trace('Section A RTF');
578 L_SELECT_OUTER := 'select
579 emp_mth.full_name,
580 emp_mth.order_name,
581 emp_mth.employee_number,
582 sum(decode(emp_mth.mth_num,1 ,mth_count)) m1,
583 sum(decode(emp_mth.mth_num,2 ,mth_count)) m2,
584 sum(decode(emp_mth.mth_num,3 ,mth_count)) m3,
585 sum(decode(emp_mth.mth_num,4 ,mth_count)) m4,
586 sum(decode(emp_mth.mth_num,5 ,mth_count)) m5,
587 sum(decode(emp_mth.mth_num,6 ,mth_count)) m6,
588 sum(decode(emp_mth.mth_num,7 ,mth_count)) m7,
589 sum(decode(emp_mth.mth_num,8 ,mth_count)) m8,
590 sum(decode(emp_mth.mth_num,9 ,mth_count)) m9,
591 sum(decode(emp_mth.mth_num,10,mth_count)) m10,
592 sum(decode(emp_mth.mth_num,11,mth_count)) m11,
593 sum(decode(emp_mth.mth_num,12,mth_count)) m12,
594 sum(mth_count) EMP_TOT
595 from (';
596 L_ORDER_BY := ') emp_mth
597 group by emp_mth.order_name,emp_mth.employee_number,emp_mth.full_name
598 order by emp_mth.order_name,emp_mth.employee_number';
599 end if; -- debug or PDF
600 if l_header_rec.intermittent_and_homeworkers = 'INCL' then
601 L_WHERE_INNER1 := null;
602 else
603 L_WHERE_INNER1 := '
604 /* Exclude intermittent and home workers as per 2483 company info */
605 and substr(hruserdt.get_table_value(
606 org_comp.business_group_id,
607 ''FR_CIPDZ'',
608 ''CIPDZ'',
609 nvl(ass.employment_category,''FR''),
610 month.end_date),1,1) not in (''I'', ''D'')';
611 end if;
612 l_sql := L_SELECT_OUTER||' /* emp_mth */
613 Select
614 per.full_name,
615 per.order_name,
616 per.employee_number,
617 month.num mth_num,
618 decode(
619 substr(hruserdt.get_table_value(org_comp.business_group_id,''FR_CIPDZ'',
620 ''CIPDZ'',
621 nvl(ass.employment_category,''FR''),
622 month.end_date),1,1)
623 ,''C'',decode(
624 sign(greatest(sign(pos.date_start- month.start_date) ,0) +
625 GREATEST(sign(nvl(month.end_date -
626 pos.actual_termination_date,0)),0))
627 ,1,/* Starter or Leaver*/
628 decode(
629 length(scl.segment5)+length(scl.segment11)
630 ,null,/*No work pattern; use Cal days*/
631 ((Least(nvl(pos.actual_termination_date,month.end_date)
632 ,month.end_date) + 1 -
633 greatest(month.START_DATE,pos.date_start))/30)*
634 pay_fr_general.CONVERT_HOURS(
635 month.end_date,
636 org_comp.business_group_id,
637 ass.assignment_id,
638 decode(ctr.ctr_information12
639 ,''HOUR'',fnd_number.canonical_to_number(
640 ctr.ctr_information11)
641 ,nvl(ass.normal_hours,0)),
642 nvl(decode(ctr.ctr_information12
643 ,''HOUR'',ctr.ctr_information13
644 ,ass.frequency),''M''),
645 ''M'') /200
646 ,pay_fr_schedule_calculation.scheduled_working_hours
647 (ass.assignment_id,
648 month.end_date,
649 greatest(month.start_date,pos.date_start),
650 Least(nvl(pos.actual_termination_date,month.end_date)
651 ,month.end_date))/200 )
652 ,1)
653 ,''P'',pay_fr_general.CONVERT_HOURS(
654 month.end_date,
655 org_comp.business_group_id,
656 ass.assignment_id,
657 decode(
658 ctr.ctr_information12
659 ,''HOUR'',fnd_number.canonical_to_number(ctr.ctr_information11)
660 ,nvl(ass.normal_hours,0)),
661 nvl(decode(ctr.ctr_information12
662 ,''HOUR'',ctr.ctr_information13
663 ,ass.frequency)
664 ,''M''),
665 ''M'') /
666 fnd_number.canonical_to_number(org_info_estab.org_information4)
667 ,1 /*I or D*/) mth_count
668 from hr_all_organization_units org_comp,
669 hr_all_organization_units org_estab,
670 hr_organization_information org_info_estab,
671 per_all_assignments_f ass,
672 (select
673 to_number(hlu.lookup_code) num,
674 to_date(''01''||hlu.lookup_code||:p_year,''DDMMYYYY'') start_date,
675 last_day(to_date(''01''||hlu.lookup_code||:p_year,''DDMMYYYY''))
676 end_date
677 from hr_lookups hlu
678 where lookup_type = ''MONTH_OF_YEAR'') month,
679 per_contracts_f ctr,
680 hr_soft_coding_keyflex scl,
681 per_periods_of_service pos,
682 per_all_people_f per
683 where org_comp.organization_id = :p_company_id
684 and org_comp.date_from <= :p_year_end
685 and (org_comp.date_to is null or
686 org_comp.date_to >= :p_year_start)
687 and org_info_estab.org_information1 = org_comp.organization_id
688 and org_info_estab.org_information_context = ''FR_ESTAB_INFO''
689 and org_info_estab.organization_id = org_estab.organization_id
690 and org_estab.organization_id = ass.establishment_id
691 and org_estab.date_from <= :p_year_end
692 and (org_estab.date_to is null or
693 org_estab.date_to >= :p_year_start)
694 and ass.primary_flag = ''Y''
695 /* rough filter on asg dates: */
696 and ass.effective_start_date <= :p_year_end
697 and ass.effective_end_date >= :p_year_start
698 /* Get 1 asg row for each month. Assumes final close would be at end of
699 month of ATD or beyond */
700 and month.end_date between ass.effective_start_date
701 and ass.effective_end_date
702 and month.end_date between org_comp.date_from
703 and nvl(org_comp.date_to,:p_year_end)
704 and month.end_date between org_estab.date_from
705 and nvl(org_estab.date_to,:p_year_end)
706 and pos.period_of_service_id = ass.period_of_service_id
707 /* filter out months where no overlapping active period of service */
708 /* Also exclude CWKs; they wont have a period of service*/
709 and pos.date_start <= month.end_date
710 and (pos.actual_termination_date is null or
711 pos.actual_termination_date >= month.start_date)
712 /* exclude specific contract types */
713 and ctr.contract_id = ass.contract_id
714 and month.end_date between ctr.effective_start_date
715 and ctr.effective_end_date
716 and ctr.type not in (''APPRENTICESHIP'', ''ADAPTATION'',
717 ''QUALIFICATION'', ''ORIENTATION'',
718 ''SOLIDARITY'',
719 ''PROFESSIONALISATION'')
720 /* exclude detaches */
721 and ass.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
722 and scl.segment12 is null /* Detache Status */'||
723 L_WHERE_INNER1||'
724 and per.person_id = ass.person_id
725 and :p_comp_end between per.effective_start_date
726 and per.effective_end_date'||L_ORDER_BY;
727 --
728 -- trace_sql(l_sql);
729 l_NOMBRE := 0;
730 if p_detail_section = 'NA' then
731 if l_header_rec.comp_active_mths_in_yr <> 0 then
732 OPEN l_ref_csr for l_sql using l_header_rec.comp_active_mths_in_yr
733 ,p_year,p_year,p_company_id,l_year_end,l_year_start,l_year_end
734 ,l_year_start,l_year_end,l_year_start,l_year_end,l_year_end
735 ,l_header_rec.date2;
736 fetch l_ref_csr into l_NOMBRE;
737 close l_ref_csr;
738 end if;
739 -- Assemble pdf XML...
740 load_xml(p_xml,'nombre',l_NOMBRE);
741 else -- debug
742 -- Assemble rtf XML...
743 load_xml(p_xml,'SECTION_A',c_OpenGrpTag);
744 -- Use l_prev_rec to maintain month totals
745 l_prev_rec.num1 := 0;
746 l_prev_rec.num2 := 0;
747 l_prev_rec.num3 := 0;
748 l_prev_rec.num4 := 0;
749 l_prev_rec.num5 := 0;
750 l_prev_rec.num6 := 0;
751 l_prev_rec.num7 := 0;
752 l_prev_rec.num8 := 0;
753 l_prev_rec.num9 := 0;
754 l_prev_rec.num10 := 0;
755 l_prev_rec.num11 := 0;
756 l_prev_rec.num12 := 0;
757 l_prev_rec.num13 := 0;
758 if l_header_rec.comp_active_mths_in_yr <> 0 then
759 OPEN l_ref_csr for l_sql using
760 p_year,p_year,p_company_id,l_year_end,l_year_start,l_year_end
761 ,l_year_start,l_year_end,l_year_start,l_year_end,l_year_end
762 ,l_header_rec.date2;
763 /* Bulk fetches from dynamic cursors not supported in 8.1.7 */
764 loop
765 fetch l_ref_csr into l_curr_rec.full_name, l_curr_rec.order_name,
766 l_curr_rec.emp_num, l_curr_rec.num1, l_curr_rec.num2,
767 l_curr_rec.num3, l_curr_rec.num4, l_curr_rec.num5, l_curr_rec.num6,
768 l_curr_rec.num7, l_curr_rec.num8, l_curr_rec.num9, l_curr_rec.num10,
769 l_curr_rec.num11, l_curr_rec.num12, l_curr_rec.num13;
770 exit when l_ref_csr%NOTFOUND;
771 -- Load emp row
772 load_xml(p_xml,'EMP',c_OpenGrpTag);
773 load_xml(p_xml,'FULL_NAME', l_curr_rec.full_name);
774 load_xml(p_xml,'EMPLOYEE_NUMBER',l_curr_rec.emp_num);
775 load_xml(p_xml,'M1', l_curr_rec.num1);
776 load_xml(p_xml,'M2', l_curr_rec.num2);
777 load_xml(p_xml,'M3', l_curr_rec.num3);
778 load_xml(p_xml,'M4', l_curr_rec.num4);
779 load_xml(p_xml,'M5', l_curr_rec.num5);
780 load_xml(p_xml,'M6', l_curr_rec.num6);
781 load_xml(p_xml,'M7', l_curr_rec.num7);
782 load_xml(p_xml,'M8', l_curr_rec.num8);
783 load_xml(p_xml,'M9', l_curr_rec.num9);
784 load_xml(p_xml,'M10', l_curr_rec.num10);
785 load_xml(p_xml,'M11', l_curr_rec.num11);
786 load_xml(p_xml,'M12', l_curr_rec.num12);
787 load_xml(p_xml,'EMP_TOT', l_curr_rec.num13);
788 load_xml(p_xml,'EMP',c_CloseGrpTag);
789 -- Use l_prev_rec to maintain month totals
790 l_prev_rec.num1 := l_prev_rec.num1 + nvl(l_curr_rec.num1,0);
791 l_prev_rec.num2 := l_prev_rec.num2 + nvl(l_curr_rec.num2,0);
792 l_prev_rec.num3 := l_prev_rec.num3 + nvl(l_curr_rec.num3,0);
793 l_prev_rec.num4 := l_prev_rec.num4 + nvl(l_curr_rec.num4,0);
794 l_prev_rec.num5 := l_prev_rec.num5 + nvl(l_curr_rec.num5,0);
795 l_prev_rec.num6 := l_prev_rec.num6 + nvl(l_curr_rec.num6,0);
796 l_prev_rec.num7 := l_prev_rec.num7 + nvl(l_curr_rec.num7,0);
797 l_prev_rec.num8 := l_prev_rec.num8 + nvl(l_curr_rec.num8,0);
798 l_prev_rec.num9 := l_prev_rec.num9 + nvl(l_curr_rec.num9,0);
799 l_prev_rec.num10 := l_prev_rec.num10 + nvl(l_curr_rec.num10,0);
800 l_prev_rec.num11 := l_prev_rec.num11 + nvl(l_curr_rec.num11,0);
801 l_prev_rec.num12 := l_prev_rec.num12 + nvl(l_curr_rec.num12,0);
802 l_prev_rec.num13 := l_prev_rec.num13 + nvl(l_curr_rec.num13,0);
803 end loop;
804 close l_ref_csr;
805 end if; -- l_header_rec.comp_active_mths_in_yr <> 0
806 load_xml(p_xml,'T1', l_prev_rec.num1);
807 load_xml(p_xml,'T2', l_prev_rec.num2);
808 load_xml(p_xml,'T3', l_prev_rec.num3);
809 load_xml(p_xml,'T4', l_prev_rec.num4);
810 load_xml(p_xml,'T5', l_prev_rec.num5);
811 load_xml(p_xml,'T6', l_prev_rec.num6);
812 load_xml(p_xml,'T7', l_prev_rec.num7);
813 load_xml(p_xml,'T8', l_prev_rec.num8);
814 load_xml(p_xml,'T9', l_prev_rec.num9);
815 load_xml(p_xml,'T10', l_prev_rec.num10);
816 load_xml(p_xml,'T11', l_prev_rec.num11);
817 load_xml(p_xml,'T12', l_prev_rec.num12);
818 load_xml(p_xml,'CMP_TOT', l_prev_rec.num13);
819 if l_header_rec.comp_active_mths_in_yr <> 0 then
820 l_NOMBRE := round((
821 trunc(l_prev_rec.num1) +
822 trunc(l_prev_rec.num2) +
823 trunc(l_prev_rec.num3) +
824 trunc(l_prev_rec.num4) +
825 trunc(l_prev_rec.num5) +
826 trunc(l_prev_rec.num6) +
827 trunc(l_prev_rec.num7) +
828 trunc(l_prev_rec.num8) +
829 trunc(l_prev_rec.num9) +
830 trunc(l_prev_rec.num10) +
831 trunc(l_prev_rec.num11) +
832 trunc(l_prev_rec.num12)) / l_header_rec.comp_active_mths_in_yr);
833 end if; -- l_header_rec.comp_active_mths_in_yr <> 0
834 load_xml(p_xml,'NOMBRE', l_NOMBRE);
835 load_xml(p_xml,'SECTION_A',c_CloseGrpTag);
836 end if; -- debug
837 end if; -- section A
838 --
839 if p_detail_section in ('B2','B3','B4','B5','NA') then
840 open csr_classifications;
841 fetch csr_classifications into l_classification_rec;
842 close csr_classifications;
843 if p_detail_section = 'NA' then
844 hr_utility.trace('Section B2-5 PDF');
845 L_SELECT_OUTER := 'select
846 nvl(sum(decode(emp_cat,2,mcnt)),0) b11
847 ,nvl(sum(decode(emp_cat,2,fcnt)),0) b12
848 ,count(distinct decode(emp_cat,2,mtrn_id)) b13
849 ,count(distinct decode(emp_cat,2,ftrn_id)) b14
850 ,round(nvl(sum(decode(emp_cat,2,trn_hrs)),0)) b15
851 ,count(distinct decode(emp_cat,2,dif_trn_id)) b16
852 ,round(nvl(sum(decode(emp_cat,2,dif_hrs)),0)) b17
853 ,round(nvl(sum(decode(emp_cat,2,dif_bal)),0)) b18
854 ,nvl(sum(decode(emp_cat,3,mcnt)),0) b21
855 ,nvl(sum(decode(emp_cat,3,fcnt)),0) b22
856 ,count(distinct decode(emp_cat,3,mtrn_id)) b23
857 ,count(distinct decode(emp_cat,3,ftrn_id)) b24
858 ,round(nvl(sum(decode(emp_cat,3,trn_hrs)),0)) b25
859 ,count(distinct decode(emp_cat,3,dif_trn_id)) b26
860 ,round(nvl(sum(decode(emp_cat,3,dif_hrs)),0)) b27
861 ,round(nvl(sum(decode(emp_cat,3,dif_bal)),0)) b28
862 ,nvl(sum(decode(emp_cat,4,mcnt)),0) b31
863 ,nvl(sum(decode(emp_cat,4,fcnt)),0) b32
864 ,count(distinct decode(emp_cat,4,mtrn_id)) b33
865 ,count(distinct decode(emp_cat,4,ftrn_id)) b34
866 ,round(nvl(sum(decode(emp_cat,4,trn_hrs)),0)) b35
867 ,count(distinct decode(emp_cat,4,dif_trn_id)) b36
868 ,round(nvl(sum(decode(emp_cat,4,dif_hrs)),0)) b37
869 ,round(nvl(sum(decode(emp_cat,4,dif_bal)),0)) b38
870 ,nvl(sum(decode(emp_cat,5,mcnt)),0) b41
871 ,nvl(sum(decode(emp_cat,5,fcnt)),0) b42
872 ,count(distinct decode(emp_cat,5,mtrn_id)) b43
873 ,count(distinct decode(emp_cat,5,ftrn_id)) b44
874 ,round(nvl(sum(decode(emp_cat,5,trn_hrs)),0)) b45
875 ,count(distinct decode(emp_cat,5,dif_trn_id)) b46
876 ,round(nvl(sum(decode(emp_cat,5,dif_hrs)),0)) b47
877 ,round(nvl(sum(decode(emp_cat,5,dif_bal)),0)) b48
878 FROM (
879 ';
880 L_ORDER_BY := ')';
881 L_SELECT_INNER1:= '
882 decode(substr(job.job_information1,1,1)
883 ,''5'',3
884 ,''4'',4
885 ,''3'',5
886 ,''6'',2) emp_cat
887 ,';
888 L_WHERE_INNER1 := '
889 AND (job.job_information1 LIKE ''3%'' OR
890 job.job_information1 LIKE ''4%'' OR
891 job.job_information1 LIKE ''5%'' OR
892 job.job_information1 LIKE ''62%'' OR
893 job.job_information1 LIKE ''63%'' OR
894 job.job_information1 LIKE ''64%'' OR
895 job.job_information1 LIKE ''65%'' OR
896 job.job_information1 LIKE ''66%'' OR
897 job.job_information1 LIKE ''67%'' OR
898 job.job_information1 LIKE ''68%'' OR
899 job.job_information1 LIKE ''69%'')';
900 else -- debug
901 hr_utility.trace('Section '||p_detail_section||' RTF');
902 L_SELECT_OUTER := 'select
903 full_name
904 ,order_name
905 ,employee_number
906 ,sum(mcnt) mcnt
907 ,sum(fcnt) fcnt
908 ,count(distinct mtrn_id) mtrn
909 ,count(distinct ftrn_id) ftrn
910 ,sum(trn_hrs) trn_hrs
911 ,count(distinct dif_trn_id) dif_trn
912 ,sum(dif_hrs) dif_hrs
913 ,sum(dif_bal) dif_bal
914 FROM (
915 ';
916 L_ORDER_BY := ')
917 GROUP BY order_name,employee_number,full_name
918 ORDER BY order_name,employee_number';
919
920 L_SELECT_INNER1:= '
921 per.full_name full_name
922 ,per.order_name order_name
923 ,per.employee_number employee_number
924 ,';
925 if p_detail_section = 'B2' then
926 L_WHERE_INNER1 := '
927 AND substr(job.job_information1,2,1) BETWEEN ''2'' AND ''9''
928 AND job.job_information1 LIKE :emp_cat||''%''';
929 else
930 L_WHERE_INNER1 := '
931 AND job.job_information1 LIKE :emp_cat||''%''';
932 end if; -- debug line
933 end if; -- debug or PDF
934 l_sql := L_SELECT_OUTER||'SELECT /* a and b */'||L_SELECT_INNER1||
935 'decode(per.sex, ''M'',1, 0) mcnt
936 ,decode(per.sex, ''F'',1, 0) fcnt
937 ,to_number(NULL) mtrn_id
938 ,to_number(NULL) ftrn_id
939 ,to_number(NULL) trn_hrs
940 ,to_number(NULL) dif_trn_id
941 ,to_number(NULL) dif_hrs
942 ,to_number(NULL) dif_bal
943 FROM hr_all_organization_units comp,
944 hr_organization_information estab_info,
945 hr_all_organization_units estab,
946 per_all_assignments_f ass,
947 per_jobs job,
948 per_periods_of_service ppos,
949 per_all_people_f per
950 WHERE comp.organization_id = :p_company_id
951 AND comp.date_from <= :p_year_end
952 AND (comp.date_to IS NULL OR
953 comp.date_to >= :p_year_start)
954 AND estab_info.org_information1 = to_char(comp.organization_id)
955 AND estab_info.org_information_context = ''FR_ESTAB_INFO''
956 AND estab.organization_id = estab_info.organization_id
957 AND estab.date_from <= :p_year_end
958 AND (estab.date_to IS NULL OR
959 estab.date_to >= :p_year_start)
960 AND estab.organization_id = ass.establishment_id
961 AND ass.primary_flag = ''Y''
962 AND :p_comp_end BETWEEN ass.effective_start_date
963 AND ass.effective_end_date
964 AND ass.job_id = job.job_id
965 AND job.job_information_category = ''FR'' '||L_WHERE_INNER1||'
966 AND ass.person_id = per.person_id
967 AND :p_comp_end BETWEEN per.effective_start_date
968 AND per.effective_end_date
969 /* exclude contingent workers by joining with periods of service
970 and also check for active employees */
971 AND ppos.period_of_service_id = ass.period_of_service_id
972 and ppos.date_start <= :p_comp_end
973 and (ppos.actual_termination_date is null or
974 ppos.actual_termination_date >= :p_comp_end)
975 UNION ALL
976 SELECT /* c, d, e, f, and g absences */'||L_SELECT_INNER1||
977 'TO_NUMBER(NULL) mcnt
978 ,TO_NUMBER(NULL) fcnt
979 ,DECODE(pabs.abs_information18 /* Within Training Plan */
980 ,''N'',DECODE(per.sex, ''M'', per.person_id)) mtrn_id
981 ,DECODE(pabs.abs_information18 /* Within Training Plan */
982 ,''N'',DECODE(per.sex, ''F'', per.person_id)) ftrn_id
983 ,DECODE(pabs.abs_information18 /* Within Training Plan */
984 ,''N'',nvl(pabs.absence_hours,0)) trn_hrs
985 ,DECODE(sub_class.classification_id
986 ,NULL,TO_NUMBER(NULL)
987 ,decode(pabs.abs_information1
988 ,''OTHER'',per.person_id)) dif_trn_id
989 ,DECODE(sub_class.classification_id
990 ,NULL,TO_NUMBER(NULL)
991 ,decode(pabs.abs_information1
992 ,''OTHER'',nvl(pabs.absence_hours,0))) dif_hrs
993 ,to_number(NULL) dif_bal
994 FROM hr_all_organization_units comp,
995 hr_organization_information estab_info,
996 hr_all_organization_units estab,
997 per_all_assignments_f ass,
998 per_jobs job,
999 per_all_people_f per,
1000 per_absence_attendances pabs,
1001 per_absence_attendance_types pabt,
1002 per_contracts_f con,
1003 pay_input_values_f piv,
1004 pay_element_types_f ele,
1005 pay_sub_classification_rules_f sub_class
1006 WHERE comp.organization_id = :p_company_id
1007 AND comp.date_from <= :p_year_end
1008 AND (comp.date_to IS NULL OR
1009 comp.date_to >= :p_year_start)
1010 AND estab_info.org_information1 = to_char(comp.organization_id)
1011 AND estab_info.org_information_context = ''FR_ESTAB_INFO''
1012 AND estab.organization_id = estab_info.organization_id
1013 AND estab.date_from <= :p_year_end
1014 AND (estab.date_to IS NULL OR
1015 estab.date_to >= :p_year_start)
1016 AND estab.organization_id = ass.establishment_id
1017 AND ass.primary_flag = ''Y''
1018 /* rough filter on asg dates: */
1019 and ass.effective_start_date <= :p_comp_end
1020 and ass.effective_end_date >= :p_comp_start
1021 AND ass.job_id = job.job_id
1022 AND job.job_information_category = ''FR'' '||L_WHERE_INNER1||'
1023 AND ass.person_id = per.person_id
1024 AND :p_comp_end BETWEEN per.effective_start_date
1025 AND per.effective_end_date
1026 AND ass.person_id = pabs.person_id
1027 AND pabs.abs_information_category = ''FR_TRAINING_ABSENCE''
1028 AND pabs.date_end BETWEEN ass.effective_start_date
1029 AND ass.effective_end_date
1030 AND pabs.date_end BETWEEN :p_comp_start
1031 AND :p_comp_end
1032 AND pabs.absence_attendance_type_id = pabt.absence_attendance_type_id
1033 AND pabt.absence_category = ''TRAINING_ABSENCE''
1034 AND con.contract_id = ass.contract_id
1035 AND pabs.date_end BETWEEN con.effective_start_date
1036 AND con.effective_end_date
1037 AND pabt.input_value_id = piv.input_value_id (+)
1038 AND pabt.date_effective BETWEEN piv.effective_start_date (+)
1039 AND piv.effective_end_date (+)
1040 AND ele.element_type_id(+) = piv.element_type_id
1041 AND piv.effective_start_date BETWEEN ele.effective_start_date (+)
1042 AND ele.effective_end_date (+)
1043 AND sub_class.element_type_id(+) = ele.element_type_id
1044 AND ele.effective_start_date BETWEEN sub_class.effective_start_date(+)
1045 AND sub_class.effective_end_date (+)
1046 AND sub_class.classification_id(+) = decode(ele.classification_id
1047 ,:inf_pri_cls,:dif_inf_sub_cls
1048 ,:abs_pri_cls,:dif_abs_sub_cls)
1049 AND con.type NOT IN (''APPRENTICESHIP'',
1050 ''ADAPTATION'',
1051 ''QUALIFICATION'',
1052 ''PROFESSIONALISATION'')
1053 AND ((/*c, d and e*/
1054 pabs.abs_information1 IN (''VAE'',
1055 ''OTHER'',
1056 ''SKILLS_ASSESSMENT'',
1057 ''PP'') AND
1058 /*Not Within Training Plan */
1059 pabs.abs_information18 = ''N''/* nullable */) OR
1060 (/*f and g*/
1061 pabs.abs_information1 = ''OTHER'' AND
1062 /* DIF absences only */
1063 sub_class.classification_id IS NOT NULL))
1064 UNION ALL
1065 SELECT /* c, d, and e OTA costs */'||L_SELECT_INNER1||
1066 'TO_NUMBER(NULL) mcnt
1067 ,TO_NUMBER(NULL) fcnt
1068 ,DECODE(per.sex, ''M'', per.person_id) mtrn_id
1069 ,DECODE(per.sex, ''F'', per.person_id) ftrn_id
1070 ,decode(tmt.tp_measurement_code
1071 ,''FR_ACTUAL_HOURS'',tpc.amount
1072 ,nvl(fnd_number.canonical_to_number(tpc.tp_cost_information3)
1073 ,0)) trn_hrs
1074 ,to_number(NULL) dif_trn_id
1075 ,to_number(NULL) dif_hrs
1076 ,to_number(NULL) dif_bal
1077 from
1078 hr_all_organization_units comp,
1079 hr_organization_information tp_org_info,
1080 hr_all_organization_units org,
1081 ota_training_plans TP,
1082 per_time_periods PTP,
1083 ota_training_plan_costs TPC,
1084 ota_tp_measurement_types TMT,
1085 ota_delegate_bookings ODB,
1086 per_all_people_f PER,
1087 ota_events EVT,
1088 per_all_assignments_f ass,
1089 per_jobs job,
1090 per_contracts_f con
1091 where comp.organization_id = :p_company_id
1092 and comp.date_from <= :p_year_end
1093 and (comp.date_to is null or
1094 comp.date_to >= :p_year_start) '
1095 ||L_WHERE_TP_ORG||'
1096 and org.organization_id = tp_org_info.organization_id
1097 and org.date_from <= :p_year_end
1098 and (org.date_to is null or
1099 org.date_to >= :p_year_start)
1100 and org.organization_id = TP.organization_id
1101 /*and TP.plan_status_type_id <> ''CANCELLED''*/
1102 and TP.time_period_id = PTP.time_period_id
1103 and PTP.period_type = ''Year''
1104 and PTP.start_date = :p_year_start
1105 and TP.training_plan_id = TPC.training_plan_id
1106 and TPC.tp_measurement_type_id = TMT.tp_measurement_type_id
1107 and TMT.business_group_id = org.business_group_id
1108 and TPC.tp_measurement_type_id IN (:ACTUAL_HOURS,
1109 :SKILLS_ASSESSMENT,
1110 :VAE)
1111 AND TPC.booking_id = ODB.booking_id
1112 and ODB.delegate_person_id = PER.person_id
1113 and :p_comp_end between PER.effective_start_date
1114 AND PER.effective_end_date
1115 AND ODB.event_id = EVT.event_id
1116 AND ass.person_id = per.person_id
1117 AND ass.primary_flag = ''Y''
1118 and evt.course_end_date between ass.effective_start_date
1119 and ass.effective_end_date
1120 AND ass.job_id = job.job_id
1121 AND job.job_information_category= ''FR'' '||L_WHERE_INNER1||'
1122 AND con.contract_id = ass.contract_id
1123 AND evt.course_end_date BETWEEN con.effective_start_date
1124 AND con.effective_end_date
1125 AND con.type NOT IN (''APPRENTICESHIP'',
1126 ''ADAPTATION'',
1127 ''QUALIFICATION'',
1128 ''PROFESSIONALISATION'')
1129 UNION ALL
1130 SELECT /* DIF balance */'||L_SELECT_INNER1||
1131 'to_number(NULL) mcnt
1132 ,to_number(NULL) fcnt
1133 ,to_number(NULL) mtrn_id
1134 ,to_number(NULL) ftrn_id
1135 ,to_number(NULL) trn_hrs
1136 ,to_number(NULL) dif_trn_id
1137 ,to_number(NULL) dif_hrs
1138 ,otfr2483.get_dif_balance(ass.assignment_id,
1139 acc.accrual_plan_id,
1140 ass.payroll_id,
1141 comp.business_group_id,
1142 :p_comp_end) dif_bal
1143 FROM hr_all_organization_units comp,
1144 hr_organization_information estab_info,
1145 hr_all_organization_units estab,
1146 per_all_assignments_f ass,
1147 per_jobs job,
1148 pay_element_entries_f ent,
1149 pay_accrual_plans acc,
1150 pay_input_values_f piv,
1151 pay_sub_classification_rules_f sub_class,
1152 per_all_people_f per
1153 WHERE comp.organization_id = :p_company_id
1154 AND comp.date_from <= :p_year_end
1155 AND (comp.date_to IS NULL OR
1156 comp.date_to >= :p_year_start)
1157 AND estab_info.org_information1 = to_char(comp.organization_id)
1158 AND estab_info.org_information_context = ''FR_ESTAB_INFO''
1159 AND estab.organization_id = estab_info.organization_id
1160 AND estab.date_from <= :p_year_end
1161 AND (estab.date_to IS NULL OR
1162 estab.date_to >= :p_year_start)
1163 AND estab.organization_id = ass.establishment_id
1164 AND ass.primary_flag = ''Y''
1165 AND :p_comp_end BETWEEN ass.effective_start_date
1166 AND ass.effective_end_date
1167 AND ass.job_id = job.job_id
1168 AND job.job_information_category = ''FR'' '||L_WHERE_INNER1||'
1169 AND ass.assignment_id = ent.assignment_id
1170 and :p_comp_end BETWEEN ent.effective_start_date
1171 AND ent.effective_end_date
1172 and ent.element_type_id = acc.accrual_plan_element_type_id
1173 AND acc.business_group_id = comp.business_group_id
1174 AND piv.input_value_id = acc.pto_input_value_id
1175 and :p_comp_end BETWEEN piv.effective_start_date
1176 AND piv.effective_end_date
1177 AND sub_class.element_type_id = piv.element_type_id
1178 AND :p_comp_end BETWEEN sub_class.effective_start_date
1179 AND sub_class.effective_end_date
1180 AND sub_class.classification_id IN (:dif_inf_sub_cls,
1181 :dif_abs_sub_cls)
1182 AND ass.person_id = per.person_id
1183 AND :p_comp_end BETWEEN per.effective_start_date
1184 AND per.effective_end_date'||L_ORDER_BY;
1185 --
1186 -- trace_sql(l_sql);
1187 if p_detail_section = 'NA' then
1188 OPEN l_ref_csr for l_sql using
1189 /* a and b */
1190 p_company_id,l_year_end,l_year_start,l_year_end,l_year_start
1191 ,l_header_rec.date2,l_header_rec.date2
1192 ,l_header_rec.date2,l_header_rec.date2
1193 /* c, d, e, f, and g absences */
1194 ,p_company_id,l_year_end,l_year_start,l_year_end,l_year_start
1195 ,l_header_rec.date2,l_header_rec.date1
1196 ,l_header_rec.date2,l_header_rec.date1,l_header_rec.date2
1197 ,l_classification_rec.inf_pri_cls_id
1198 ,l_classification_rec.dif_inf_sub_cls_id
1199 ,l_classification_rec.abs_pri_cls_id
1200 ,l_classification_rec.dif_abs_sub_cls_id
1201 /* c, d, and e OTA costs */
1202 ,p_company_id,l_year_end,l_year_start,l_year_end,l_year_start
1203 ,l_year_start,l_meas_types_rec.ACTUAL_HOURS
1204 ,l_meas_types_rec.SKILLS_ASSESSMENT,l_meas_types_rec.VAE
1205 ,l_header_rec.date2
1206 /* DIF balance */
1207 ,l_header_rec.date2
1208 ,p_company_id,l_year_end,l_year_start,l_year_end,l_year_start
1209 ,l_header_rec.date2,l_header_rec.date2
1210 ,l_header_rec.date2,l_header_rec.date2
1211 ,l_classification_rec.dif_inf_sub_cls_id
1212 ,l_classification_rec.dif_abs_sub_cls_id
1213 ,l_header_rec.date2;
1214 fetch l_ref_csr into l_b11,l_b12,l_b13,l_b14,l_b15,l_b16,l_b17,l_b18,
1215 l_b21,l_b22,l_b23,l_b24,l_b25,l_b26,l_b27,l_b28,
1216 l_b31,l_b32,l_b33,l_b34,l_b35,l_b36,l_b37,l_b38,
1217 l_b41,l_b42,l_b43,l_b44,l_b45,l_b46,l_b47,l_b48;
1218 -- Assemble pdf XML...
1219 load_xml(p_xml,'b11',l_b11);
1220 load_xml(p_xml,'b12',l_b12);
1221 load_xml(p_xml,'b13',l_b13);
1222 load_xml(p_xml,'b14',l_b14);
1223 load_xml(p_xml,'b15',l_b15);
1224 load_xml(p_xml,'b16',l_b16);
1225 load_xml(p_xml,'b17',l_b17);
1226 load_xml(p_xml,'b18',l_b18);
1227 load_xml(p_xml,'b21',l_b21);
1228 load_xml(p_xml,'b22',l_b22);
1229 load_xml(p_xml,'b23',l_b23);
1230 load_xml(p_xml,'b24',l_b24);
1231 load_xml(p_xml,'b25',l_b25);
1232 load_xml(p_xml,'b26',l_b26);
1233 load_xml(p_xml,'b27',l_b27);
1234 load_xml(p_xml,'b28',l_b28);
1235 load_xml(p_xml,'b31',l_b31);
1236 load_xml(p_xml,'b32',l_b32);
1237 load_xml(p_xml,'b33',l_b33);
1238 load_xml(p_xml,'b34',l_b34);
1239 load_xml(p_xml,'b35',l_b35);
1240 load_xml(p_xml,'b36',l_b36);
1241 load_xml(p_xml,'b37',l_b37);
1242 load_xml(p_xml,'b38',l_b38);
1243 load_xml(p_xml,'b41',l_b41);
1244 load_xml(p_xml,'b42',l_b42);
1245 load_xml(p_xml,'b43',l_b43);
1246 load_xml(p_xml,'b44',l_b44);
1247 load_xml(p_xml,'b45',l_b45);
1248 load_xml(p_xml,'b46',l_b46);
1249 load_xml(p_xml,'b47',l_b47);
1250 load_xml(p_xml,'b48',l_b48);
1251 load_xml(p_xml,'zca',l_b11+l_b21+l_b31+l_b41);
1252 load_xml(p_xml,'zcb',l_b12+l_b22+l_b32+l_b42);
1253 load_xml(p_xml,'zcc',l_b13+l_b23+l_b33+l_b43);
1254 load_xml(p_xml,'zcd',l_b14+l_b24+l_b34+l_b44);
1255 load_xml(p_xml,'zce',l_b15+l_b25+l_b35+l_b45);
1256 load_xml(p_xml,'zcf',l_b16+l_b26+l_b36+l_b46);
1257 load_xml(p_xml,'zcg',l_b17+l_b27+l_b37+l_b47);
1258 load_xml(p_xml,'zch',l_b18+l_b28+l_b38+l_b48);
1259 else -- debug
1260 l_b11 := 0;
1261 l_b12 := 0;
1262 l_b13 := 0;
1263 l_b14 := 0;
1264 l_b15 := 0;
1265 l_b16 := 0;
1266 l_b17 := 0;
1267 l_b18 := 0;
1268 if p_detail_section = 'B2' then
1269 L_WHERE_INNER1 := '6';
1270 elsif p_detail_section = 'B3' then
1271 L_WHERE_INNER1 := '5';
1272 elsif p_detail_section = 'B4' then
1273 L_WHERE_INNER1 := '4';
1274 else -- p_detail_section = 'B5'
1275 L_WHERE_INNER1 := '3';
1276 end if;
1277 OPEN l_ref_csr for l_sql using
1278 /* a and b */
1279 p_company_id,l_year_end,l_year_start,l_year_end,l_year_start
1280 ,l_header_rec.date2,L_WHERE_INNER1,l_header_rec.date2
1281 ,l_header_rec.date2,l_header_rec.date2
1282 /* c, d, e, f, and g absences */
1283 ,p_company_id,l_year_end,l_year_start,l_year_end,l_year_start
1284 ,l_header_rec.date2,l_header_rec.date1,L_WHERE_INNER1
1285 ,l_header_rec.date2,l_header_rec.date1,l_header_rec.date2
1286 ,l_classification_rec.inf_pri_cls_id
1287 ,l_classification_rec.dif_inf_sub_cls_id
1288 ,l_classification_rec.abs_pri_cls_id
1289 ,l_classification_rec.dif_abs_sub_cls_id
1290 /* c, d, and e OTA costs */
1291 ,p_company_id,l_year_end,l_year_start,l_year_end,l_year_start
1292 ,l_year_start,l_meas_types_rec.ACTUAL_HOURS
1293 ,l_meas_types_rec.SKILLS_ASSESSMENT,l_meas_types_rec.VAE
1294 ,l_header_rec.date2,L_WHERE_INNER1
1295 /* h (DIF balance) */
1296 ,l_header_rec.date2
1297 ,p_company_id,l_year_end,l_year_start,l_year_end,l_year_start
1298 ,l_header_rec.date2,L_WHERE_INNER1,l_header_rec.date2
1299 ,l_header_rec.date2,l_header_rec.date2
1300 ,l_classification_rec.dif_inf_sub_cls_id
1301 ,l_classification_rec.dif_abs_sub_cls_id
1302 ,l_header_rec.date2;
1303 -- Assemble rtf XML...
1304 load_xml(p_xml,'SECTION_B2_5',c_OpenGrpTag);
1305 /* Bulk fetches from dynamic cursors not supported in 8.1.7 */
1306 loop
1307 fetch l_ref_csr into l_curr_rec.full_name, l_curr_rec.order_name,
1308 l_curr_rec.emp_num, l_curr_rec.num1, l_curr_rec.num2,
1309 l_curr_rec.num3, l_curr_rec.num4, l_curr_rec.num5, l_curr_rec.num6,
1310 l_curr_rec.num7, l_curr_rec.num8;
1311 if l_ref_csr%NOTFOUND and l_ref_csr%ROWCOUNT > 0 then
1312 -- Close emp_list
1313 load_xml(p_xml,'EMP_LIST',c_CloseGrpTag);
1314 end if;
1315 exit when l_ref_csr%NOTFOUND;
1316 if l_ref_csr%ROWCOUNT = 1 then
1317 -- Open emp_list
1318 load_xml(p_xml,'EMP_LIST',c_OpenGrpTag);
1319 end if;
1320 -- Load emp row
1321 load_xml(p_xml,'EMP',c_OpenGrpTag);
1322 load_xml(p_xml,'FULL_NAME', l_curr_rec.full_name);
1323 load_xml(p_xml,'EMPLOYEE_NUMBER',l_curr_rec.emp_num);
1324 load_xml(p_xml,'MCNT', l_curr_rec.num1);
1325 load_xml(p_xml,'FCNT', l_curr_rec.num2);
1326 load_xml(p_xml,'MTRN', l_curr_rec.num3);
1327 load_xml(p_xml,'FTRN', l_curr_rec.num4);
1328 load_xml(p_xml,'TRN_HRS', l_curr_rec.num5);
1329 load_xml(p_xml,'DIF_TRN', l_curr_rec.num6);
1330 load_xml(p_xml,'DIF_HRS', l_curr_rec.num7);
1331 load_xml(p_xml,'DIF_BAL', l_curr_rec.num8);
1332 load_xml(p_xml,'EMP',c_CloseGrpTag);
1333 --
1334 l_b11 := l_b11 + nvl(l_curr_rec.num1,0);
1335 l_b12 := l_b12 + nvl(l_curr_rec.num2,0);
1336 l_b13 := l_b13 + l_curr_rec.num3;
1337 l_b14 := l_b14 + l_curr_rec.num4;
1338 l_b15 := l_b15 + nvl(l_curr_rec.num5,0);
1339 l_b16 := l_b16 + l_curr_rec.num6;
1340 l_b17 := l_b17 + nvl(l_curr_rec.num7,0);
1341 l_b18 := l_b18 + nvl(l_curr_rec.num8,0);
1342 end loop;
1343 load_xml(p_xml,'bn1',l_b11);
1344 load_xml(p_xml,'bn2',l_b12);
1345 load_xml(p_xml,'bn3',l_b13);
1346 load_xml(p_xml,'bn4',l_b14);
1347 load_xml(p_xml,'bn5',l_b15);
1348 load_xml(p_xml,'bn6',l_b16);
1349 load_xml(p_xml,'bn7',l_b17);
1350 load_xml(p_xml,'bn8',l_b18);
1351 load_xml(p_xml,'SECTION_B2_5',c_CloseGrpTag);
1352 end if; -- debug fetch
1353 close l_ref_csr;
1354 end if; -- Section B2-5
1355 --
1356 if p_detail_section in ('B7_8','NA') then
1357 if p_detail_section = 'NA' then
1358 hr_utility.trace('Section B7 and 8 PDF');
1359 L_SELECT_OUTER := 'select
1360 count(distinct per.person_id) c1
1361 ,round(nvl(sum(pabs.absence_hours),0)) c2
1362 ';
1363 L_ORDER_BY := null;
1364 L_SELECT_INNER1:= null;
1365 else -- debug
1366 hr_utility.trace('Section B7 and 8 RTF');
1367 L_SELECT_OUTER := null;
1368 L_SELECT_INNER1:= 'select
1369 per.order_name order_name
1370 ,per.full_name full_name
1371 ,per.employee_number employee_number
1372 ,pabs.date_start abs_start
1373 ,pabs.date_end abs_end
1374 ,nvl(pabs.absence_hours,0) abs_hrs
1375 ';
1376 L_ORDER_BY := '
1377 ORDER BY 1,3,4,5 desc';
1378 end if; -- debug or PDF
1379 l_sql := L_SELECT_OUTER||L_SELECT_INNER1||'FROM
1380 hr_all_organization_units comp,
1381 hr_organization_information estab_info,
1382 hr_all_organization_units estab,
1383 per_all_assignments_f ass,
1384 per_all_people_f per,
1385 per_absence_attendances pabs,
1386 per_absence_attendance_types pabt
1387 WHERE comp.organization_id = :p_company_id
1388 AND comp.date_from <= :p_year_end
1389 AND (comp.date_to IS NULL OR
1390 comp.date_to >= :p_year_start)
1391 AND estab_info.org_information1 = to_char(comp.organization_id)
1392 AND estab_info.org_information_context = ''FR_ESTAB_INFO''
1393 AND estab.organization_id = estab_info.organization_id
1394 AND estab.date_from <= :p_year_end
1395 AND (estab.date_to IS NULL OR
1396 estab.date_to >= :p_year_start)
1397 AND estab.organization_id = ass.establishment_id
1398 AND ass.primary_flag = ''Y''
1399 /* rough filter on asg dates: */
1400 and ass.effective_start_date <= :p_comp_end
1401 and ass.effective_end_date >= :p_comp_start
1402 AND ass.person_id = per.person_id
1403 AND :p_comp_end BETWEEN per.effective_start_date
1404 AND per.effective_end_date
1405 AND per.person_id = pabs.person_id
1406 AND pabs.abs_information_category = ''FR_TRAINING_ABSENCE''
1407 AND nvl(pabs.date_end,pabs.date_start) BETWEEN ass.effective_start_date
1408 AND ass.effective_end_date
1409 AND pabs.date_start <= :p_comp_end
1410 AND (pabs.date_end IS NULL OR
1411 pabs.date_end >= :p_comp_start)
1412 AND pabs.abs_information1 = ''PP''
1413 AND pabs.absence_attendance_type_id = pabt.absence_attendance_type_id
1414 AND pabt.absence_category = ''TRAINING_ABSENCE'' '||L_ORDER_BY;
1415 --
1416 --trace_sql(l_sql);
1417 l_c1 := 0;
1418 l_c2 := 0;
1419 OPEN l_ref_csr for l_sql using
1420 p_company_id,l_year_end,l_year_start,l_year_end,l_year_start
1421 ,l_header_rec.date2,l_header_rec.date1,l_header_rec.date2
1422 ,l_header_rec.date2,l_header_rec.date1;
1423 if p_detail_section = 'NA' then
1424 fetch l_ref_csr into l_c1, l_c2;
1425 -- Assemble pdf XML...
1426 load_xml(p_xml,'c1',l_c1);
1427 load_xml(p_xml,'c2',l_c2);
1428 else -- debug
1429 /* Bulk fetches from dynamic cursors not supported in 8.1.7 */
1430 l_prev_rec := l_empt_rec;
1431 -- Assemble rtf XML...
1432 load_xml(p_xml,'SECTION_B7_8',c_OpenGrpTag);
1433 loop
1434 FETCH l_ref_csr INTO l_curr_rec.order_name, l_curr_rec.full_name,
1435 l_curr_rec.emp_num, l_curr_rec.trn_start,
1436 l_curr_rec.trn_end, l_curr_rec.num1;
1437 if (l_ref_csr%NOTFOUND
1438 or l_prev_rec.full_name <> l_curr_rec.full_name
1439 or l_prev_rec.emp_num <> l_curr_rec.emp_num)
1440 and l_ref_csr%ROWCOUNT > 0
1441 then
1442 -- Close previous emp
1443 load_xml(p_xml,'EMP',c_CloseGrpTag);
1444 l_c1 := l_c1 + 1;
1445 end if;
1446 exit when l_ref_csr%NOTFOUND;
1447 if nvl(l_prev_rec.full_name,' ') <> l_curr_rec.full_name
1448 or nvl(l_prev_rec.emp_num,' ') <> l_curr_rec.emp_num
1449 then
1450 -- Open new EMP
1451 load_xml(p_xml,'EMP',c_OpenGrpTag);
1452 load_xml(p_xml,'FULL_NAME',l_curr_rec.full_name);
1453 load_xml(p_xml,'EMPLOYEE_NUMBER',l_curr_rec.emp_num);
1454 end if;
1455 load_xml(p_xml,'ABS',c_OpenGrpTag);
1456 load_xml(p_xml,'ABS_START',l_curr_rec.trn_start);
1457 load_xml(p_xml,'ABS_END',l_curr_rec.trn_end);
1458 load_xml(p_xml,'ABS_HRS',l_curr_rec.num1);
1459 load_xml(p_xml,'ABS',c_CloseGrpTag);
1460 l_c2 := l_c2 + l_curr_rec.num1;
1461 l_prev_rec := l_curr_rec;
1462 end loop;
1463 load_xml(p_xml,'c1',l_c1);
1464 load_xml(p_xml,'c2',l_c2);
1465 load_xml(p_xml,'SECTION_B7_8',c_CloseGrpTag);
1466 end if; -- debug or pdf
1467 close l_ref_csr;
1468 end if; -- B7_8
1469 --
1470 if p_detail_section in ('B9_10_FD','NA') then
1471 if p_detail_section = 'NA' then
1472 hr_utility.trace('Section B9 and 10 and Fd PDF');
1473 L_SELECT_OUTER := 'select
1474 count(distinct person_id) c3
1475 ,round(nvl(sum(out_hrs),0)) c4
1476 ,round(nvl(sum(trn_al),0)) C121
1477 from (
1478 ';
1479 L_ORDER_BY := ')';
1480 L_SELECT_INNER1:= 'select
1481 per.person_id
1482 ,fnd_number.canonical_to_number(tp_cost_hrs.tp_cost_information4) out_hrs
1483 ,decode(tp_cost.currency_code
1484 ,''EUR'',tp_cost.amount
1485 ,hr_currency_pkg.convert_amount_sql(
1486 tp_cost.currency_code
1487 ,''EUR''
1488 ,sysdate
1489 ,tp_cost.amount
1490 ,:CURRENCY_RATE_TYPE)) trn_al
1491 ';
1492
1493 L_SELECT_INNER2:= 'select
1494 per.person_id
1495 ,fnd_number.canonical_to_number(pabs.abs_information20) out_hrs
1496 ,decode(bg_info.org_information10
1497 ,''EUR'',fnd_number.canonical_to_number(pabs.abs_information22)
1498 ,hr_currency_pkg.convert_amount_sql(
1499 bg_info.org_information10
1500 ,''EUR''
1501 ,sysdate
1502 ,nvl(fnd_number.canonical_to_number(pabs.abs_information22),0)
1503 ,:CURRENCY_RATE_TYPE)) trn_al
1504 ';
1505 else -- debug
1506 hr_utility.trace('Section B9 and 10 and Fd RTF');
1507 L_SELECT_OUTER := null;
1508 L_SELECT_INNER1:= 'select
1509 per.order_name order_name
1510 ,per.full_name full_name
1511 ,per.employee_number employee_number
1512 ,evt.course_start_date trn_start
1513 ,evt.course_end_date trn_end
1514 ,tp.name plan
1515 ,evt_tl.title class
1516 ,hlu_legal.meaning legal_cat
1517 ,tp_cost_hrs.amount act_hrs
1518 ,nvl(tp_cost_hrs.tp_cost_information4,''0'') out_hrs
1519 ,tp_cost.amount trn_al
1520 ,tp_cost.currency_code trn_al_cc
1521 ';
1522 L_SELECT_INNER2:= 'select
1523 per.order_name order_name
1524 ,per.full_name full_name
1525 ,per.employee_number employee_number
1526 ,pabs.date_start trn_start
1527 ,pabs.date_end trn_end
1528 ,null plan
1529 ,null class
1530 ,hlu_legal.meaning legal_cat
1531 ,nvl(pabs.absence_hours,0) act_hrs
1532 ,nvl(pabs.abs_information20,''0'') out_hrs
1533 ,fnd_number.canonical_to_number(pabs.abs_information22) trn_al
1534 ,bg_info.org_information10 trn_al_cc
1535 ';
1536 L_ORDER_BY := '
1537 ORDER BY 1,3,4,5 desc';
1538 end if; -- debug or PDF
1539 l_sql := L_SELECT_OUTER||L_SELECT_INNER1||'FROM
1540 hr_all_organization_units comp,
1541 hr_organization_information tp_org_info,
1542 hr_all_organization_units org,
1543 ota_training_plans tp,
1544 per_time_periods ptp,
1545 ota_training_plan_costs tp_cost,
1546 ota_training_plan_costs tp_cost_hrs,
1547 ota_delegate_bookings delegate,
1548 per_all_people_f per,
1549 ota_events evt,
1550 ota_events_tl evt_tl,
1551 hr_lookups hlu_legal
1552 WHERE comp.organization_id = :p_company_id
1553 AND comp.date_from <= :p_end_year
1554 AND (comp.date_to IS NULL OR
1555 comp.date_to >= :p_start_year) '
1556 ||L_WHERE_TP_ORG||'
1557 AND org.organization_id = tp_org_info.organization_id
1558 AND org.date_from <= :p_end_year
1559 AND (org.date_to IS NULL OR
1560 org.date_to >= :p_start_year)
1561 AND tp.organization_id = org.organization_id
1562 AND ptp.time_period_id = tp.time_period_id
1563 AND ptp.period_type = ''Year''
1564 AND PTP.start_date = :p_start_year
1565 AND tp.training_plan_id = tp_cost.training_plan_id
1566 AND tp_cost.tp_measurement_type_id = :DEDUCTIBLE_TRN_ALLOWANCE
1567 AND tp_cost.training_plan_id = tp_cost_hrs.training_plan_id
1568 AND tp_cost_hrs.booking_id = tp_cost.booking_id
1569 AND tp_cost_hrs.tp_measurement_type_id= :ACTUAL_HOURS
1570 AND tp_cost.booking_id = delegate.booking_id
1571 AND delegate.delegate_person_id = per.person_id
1572 AND :p_end_comp BETWEEN PER.effective_start_date
1573 AND PER.effective_end_date
1574 AND delegate.event_id = evt.event_id
1575 and EVT_tl.event_id = EVT.event_id
1576 and EVT_tl.language = userenv(''LANG'')
1577 AND hlu_legal.lookup_type(+) = ''FR_LEGAL_TRG_CATG''
1578 AND hlu_legal.lookup_code(+) = tp_cost_hrs.tp_cost_information3
1579 UNION ALL
1580 '||L_SELECT_INNER2||'FROM
1581 hr_all_organization_units COMP,
1582 hr_organization_information estab_info,
1583 hr_all_organization_units estab,
1584 per_all_assignments_f ass,
1585 per_all_people_f per,
1586 per_absence_attendances pabs,
1587 per_absence_attendance_types pabt,
1588 hr_lookups hlu_legal,
1589 hr_organization_information bg_info
1590 WHERE comp.organization_id = :p_company_id
1591 AND comp.date_from <= :p_year_end
1592 AND (comp.date_to IS NULL OR
1593 comp.date_to >= :p_year_start)
1594 AND estab_info.org_information1 = to_char(comp.organization_id)
1595 AND estab_info.org_information_context = ''FR_ESTAB_INFO''
1596 AND estab.organization_id = estab_info.organization_id
1597 AND estab.date_from <= :p_year_end
1598 AND (estab.date_to IS NULL OR
1599 estab.date_to >= :p_year_start)
1600 AND estab.organization_id = ass.establishment_id
1601 AND ass.primary_flag = ''Y''
1602 /* rough filter on asg dates: */
1603 and ass.effective_start_date <= :p_comp_end
1604 and ass.effective_end_date >= :p_comp_start
1605 AND ass.person_id = per.person_id
1606 AND :p_comp_end BETWEEN per.effective_start_date
1607 AND per.effective_end_date
1608 AND per.person_id = pabs.person_id
1609 AND pabs.abs_information_category = ''FR_TRAINING_ABSENCE''
1610 AND pabs.date_end BETWEEN ass.effective_start_date
1611 AND ass.effective_end_date
1612 AND pabs.date_end BETWEEN :p_comp_start
1613 AND :p_comp_end
1614 /*Not Within Training Plan */
1615 AND pabs.abs_information18 = ''N''/* nullable */
1616 AND pabs.abs_information22 <> ''0''
1617 AND pabs.absence_attendance_type_id = pabt.absence_attendance_type_id
1618 AND pabt.absence_category = ''TRAINING_ABSENCE''
1619 AND hlu_legal.lookup_code(+) = pabs.abs_information19 /*NULLABLE*/
1620 AND hlu_legal.lookup_type(+) = ''FR_LEGAL_TRG_CATG''
1621 AND bg_info.organization_id = comp.business_group_id
1622 and bg_info.org_information_context = ''Business Group Information'' '||
1623 L_ORDER_BY;
1624 --
1625 --trace_sql(l_sql);
1626 l_c3 := 0;
1627 l_c4 := 0;
1628 l_C121 := 0;
1629 if p_detail_section = 'NA' then
1630 OPEN l_ref_csr for l_sql using l_currency_rate_type
1631 ,p_company_id,l_year_end,l_year_start,l_year_end,l_year_start
1632 ,l_year_start,l_meas_types_rec.DEDUCTIBLE_TRN_ALLOWANCE
1633 ,l_meas_types_rec.ACTUAL_HOURS,l_header_rec.date2
1634 ,l_currency_rate_type
1635 ,p_company_id,l_year_end,l_year_start,l_year_end,l_year_start
1636 ,l_header_rec.date2,l_header_rec.date1,l_header_rec.date2
1637 ,l_header_rec.date1,l_header_rec.date2;
1638 fetch l_ref_csr into l_c3, l_c4, l_C121;
1639 -- Assemble pdf XML...
1640 load_xml(p_xml,'c3',l_c3);
1641 load_xml(p_xml,'c4',l_c4);
1642 close l_ref_csr;
1643 else -- debug
1644 l_currency_rate_type := hr_currency_pkg.get_rate_type(l_header_rec.bg_id
1645 ,sysdate,'R');
1646 --
1647 OPEN l_ref_csr for l_sql using
1648 p_company_id,l_year_end,l_year_start,l_year_end,l_year_start
1649 ,l_year_start,l_meas_types_rec.DEDUCTIBLE_TRN_ALLOWANCE
1650 ,l_meas_types_rec.ACTUAL_HOURS,l_header_rec.date2
1651 ,p_company_id,l_year_end,l_year_start,l_year_end,l_year_start
1652 ,l_header_rec.date2,l_header_rec.date1,l_header_rec.date2
1653 ,l_header_rec.date1,l_header_rec.date2;
1654 /* Bulk fetches from dynamic cursors not supported in 8.1.7 */
1655 l_tot_act_hrs := 0;
1656 l_prev_rec := l_empt_rec;
1657 -- Assemble rtf XML...
1658 load_xml(p_xml,'SECTION_B9_10_Fd',c_OpenGrpTag);
1659 loop
1660 FETCH l_ref_csr INTO l_curr_rec.order_name, l_curr_rec.full_name,
1661 l_curr_rec.emp_num, l_curr_rec.trn_start,
1662 l_curr_rec.trn_end, l_curr_rec.plan_name, l_curr_rec.class_name,
1663 l_curr_rec.legal_cat,l_curr_rec.num1,l_curr_rec.out_hrs_chr,
1664 l_curr_rec.num2,l_curr_rec.chr1;
1665 if (l_ref_csr%NOTFOUND
1666 or l_prev_rec.full_name <> l_curr_rec.full_name
1667 or l_prev_rec.emp_num <> l_curr_rec.emp_num)
1668 and l_ref_csr%ROWCOUNT > 0
1669 then
1670 -- Close previous emp
1671 load_xml(p_xml,'EMP',c_CloseGrpTag);
1672 l_c3 := l_c3 + 1;
1673 end if;
1674 exit when l_ref_csr%NOTFOUND;
1675 if nvl(l_prev_rec.full_name,' ') <> l_curr_rec.full_name
1676 or nvl(l_prev_rec.emp_num,' ') <> l_curr_rec.emp_num
1677 then
1678 -- Open new EMP
1679 load_xml(p_xml,'EMP',c_OpenGrpTag);
1680 load_xml(p_xml,'FULL_NAME',l_curr_rec.full_name);
1681 load_xml(p_xml,'EMPLOYEE_NUMBER',l_curr_rec.emp_num);
1682 end if;
1683 load_xml(p_xml,'TRAINING',c_OpenGrpTag);
1684 load_xml(p_xml,'TRN_START',l_curr_rec.trn_start);
1685 load_xml(p_xml,'TRN_END',l_curr_rec.trn_end);
1686 load_xml(p_xml,'PLAN',l_curr_rec.plan_name);
1687 load_xml(p_xml,'CLASS',l_curr_rec.class_name);
1688 load_xml(p_xml,'LEGAL_CAT',l_curr_rec.legal_cat);
1689 load_xml(p_xml,'ACT_HRS',l_curr_rec.num1);
1690 load_xml(p_xml,'OUT_HRS',l_curr_rec.out_hrs_chr);
1691 if l_curr_rec.chr1 = 'EUR' then
1692 -- no need to convert currency
1693 load_xml(p_xml,'TRN_AL',l_curr_rec.num2);
1694 else
1695 load_xml(p_xml,'TRN_AL'
1696 ,hr_currency_pkg.convert_amount(
1697 l_curr_rec.chr1
1698 ,'EUR'
1699 ,sysdate
1700 ,l_curr_rec.num2
1701 ,l_currency_rate_type));
1702 end if;
1703 load_xml(p_xml,'TRAINING',c_CloseGrpTag);
1704 l_tot_act_hrs := l_tot_act_hrs + l_curr_rec.num1;
1705 l_c4 := l_c4 + fnd_number.canonical_to_number(l_curr_rec.out_hrs_chr);
1706 l_C121 := l_C121 + l_curr_rec.num2;
1707 l_prev_rec := l_curr_rec;
1708 end loop;
1709 load_xml(p_xml,'c3',l_c3);
1710 load_xml(p_xml,'TOT_ACT',l_tot_act_hrs);
1711 load_xml(p_xml,'c4',l_c4);
1712 load_xml(p_xml,'C121',l_C121);
1713 load_xml(p_xml,'SECTION_B9_10_Fd',c_CloseGrpTag);
1714 close l_ref_csr;
1715 end if; -- debug or pdf
1716 end if; -- Section B9 and 10 and Fd
1717 --
1718 if p_detail_section in ('B11','B12','NA') then
1719 if p_detail_section = 'NA' then
1720 hr_utility.trace('Section B11 and 12 PDF');
1721 L_SELECT_OUTER := 'select
1722 round(nvl(sum(decode(trn_type,''SA'', num_courses)),0)) c5,
1723 round(nvl(sum(decode(trn_type,''VAE'',num_courses)),0)) c6
1724 from (
1725 ';
1726 L_ORDER_BY := ')';
1727 L_SELECT_INNER1:= 'select
1728 decode(meas_type.tp_measurement_code,
1729 ''FR_DEDUCT_EXT_TRN_PLAN_VAE'',''VAE'',
1730 ''FR_DEDUCT_EXT_TRN_PLAN_SA'', ''SA'',
1731 ''FR_SKILLS_ASSESSMENT'', ''SA'',
1732 ''FR_VAE'', ''VAE'') trn_type
1733 ,decode(tp_cost.booking_id,
1734 NULL,fnd_number.canonical_to_number(tp_cost.tp_cost_information1),
1735 1) num_courses
1736 ';
1737 L_SELECT_INNER2:= 'select
1738 decode(pabs.abs_information1,
1739 ''SKILLS_ASSESSMENT'',''SA'',
1740 ''VAE'',''VAE'') trn_type
1741 ,1 num_courses
1742 ';
1743 else -- p_detail_section in ('B11','B12')
1744 hr_utility.trace('Section '||p_detail_section||' RTF');
1745 L_SELECT_OUTER := null;
1746 L_SELECT_INNER1:= 'select
1747 per.order_name order_name
1748 ,per.full_name full_name
1749 ,per.employee_number employee_number
1750 ,event.course_start_date trn_start
1751 ,event.course_end_date trn_end
1752 ,tp.name plan
1753 ,event_tl.title class
1754 ,decode(tp_cost.booking_id
1755 ,NULL,fnd_number.canonical_to_number(tp_cost.tp_cost_information1)
1756 ,1) num_courses
1757 ';
1758 L_SELECT_INNER2:= 'select
1759 per.order_name order_name
1760 ,per.full_name full_name
1761 ,per.employee_number employee_number
1762 ,pabs.date_start trn_start
1763 ,pabs.date_end trn_end
1764 ,null plan
1765 ,null class
1766 ,1 num_courses
1767 ';
1768 L_ORDER_BY := '
1769 ORDER BY 1, 3, 4, 5 DESC, 6, 7';
1770 --
1771 end if; -- p_detail_section = 'NA'
1772 l_sql := L_SELECT_OUTER||L_SELECT_INNER1||'FROM
1773 hr_all_organization_units comp,
1774 hr_organization_information tp_org_info,
1775 hr_all_organization_units org,
1776 ota_training_plans tp,
1777 per_time_periods ptp,
1778 ota_training_plan_costs tp_cost,
1779 ota_tp_measurement_types meas_type,
1780 ota_delegate_bookings delegate,
1781 ota_events event,
1782 ota_events_tl event_tl,
1783 per_all_people_f per
1784 WHERE comp.organization_id = :p_company_id
1785 AND comp.date_from <= :p_end_year
1786 AND (comp.date_to IS NULL OR
1787 comp.date_to >= :p_start_year) '
1788 ||L_WHERE_TP_ORG||'
1789 AND org.organization_id = tp_org_info.organization_id
1790 AND org.date_from <= :p_end_year
1791 AND (org.date_to IS NULL OR
1792 org.date_to >= :p_start_year)
1793 AND tp.organization_id = org.organization_id
1794 AND ptp.time_period_id = tp.time_period_id
1795 AND ptp.period_type = ''Year''
1796 AND ptp.start_date = :p_start_year
1797 AND tp.training_plan_id = tp_cost.training_plan_id
1798 AND ((tp_cost.tp_measurement_type_id IN (:FR_SKILLS_ASSESSMENT,:FR_VAE) AND
1799 meas_type.cost_level = ''DELEGATE'' AND
1800 tp_cost.tp_cost_information1 = ''EMPLOYER'') OR
1801 (tp_cost.tp_measurement_type_id IN (:FR_DEDUCTIBLE_EXT_TRN_PLAN_SA,
1802 :FR_DEDUCTIBLE_EXT_TRN_PLAN_VAE) AND
1803 meas_type.cost_level = ''PLAN'' AND
1804 tp_cost.tp_cost_information1 <> ''0''))
1805 AND tp_cost.tp_measurement_type_id = meas_type.tp_measurement_type_id
1806 AND meas_type.unit = ''M''
1807 AND tp_cost.information_category =''FR_''||meas_type.tp_measurement_code
1808 AND tp_cost.booking_id = delegate.booking_id(+)
1809 AND delegate.delegate_person_id = per.person_id(+)
1810 AND :p_comp_end BETWEEN per.effective_start_date(+)
1811 AND per.effective_end_date(+)
1812 AND delegate.event_id = event.event_id(+)
1813 AND event_tl.event_id(+) = event.event_id
1814 AND event_tl.language(+) = userenv (''LANG'')
1815 UNION ALL
1816 '||L_SELECT_INNER2||'FROM
1817 hr_all_organization_units COMP,
1818 hr_organization_information estab_info,
1819 hr_all_organization_units estab,
1820 per_all_assignments_f ass,
1821 per_all_people_f per,
1822 per_absence_attendances pabs,
1823 per_absence_attendance_types pabt
1824 WHERE comp.organization_id = :p_company_id
1825 AND comp.date_from <= :p_year_end
1826 AND (comp.date_to IS NULL OR
1827 comp.date_to >= :p_year_start)
1828 AND estab_info.org_information_context = ''FR_ESTAB_INFO''
1829 AND estab_info.org_information1 = to_char(comp.organization_id)
1830 AND estab.organization_id = estab_info.organization_id
1831 AND estab.date_from <= :p_year_end
1832 AND (estab.date_to IS NULL OR
1833 estab.date_to >= :p_year_start)
1834 AND estab.organization_id = ass.establishment_id
1835 AND ass.primary_flag = ''Y''
1836 /* rough filter on asg dates: */
1837 and ass.effective_start_date <= :p_comp_end
1838 and ass.effective_end_date >= :p_comp_start
1839 AND ass.person_id = per.person_id
1840 AND :p_comp_end BETWEEN per.effective_start_date
1841 AND per.effective_end_date
1842 AND per.person_id = pabs.person_id
1843 AND pabs.abs_information_category = ''FR_TRAINING_ABSENCE''
1844 AND pabs.date_end BETWEEN ass.effective_start_date
1845 AND ass.effective_end_date
1846 AND pabs.date_end BETWEEN :p_comp_start
1847 AND :p_comp_end
1848 /*Not Within Training Plan */
1849 AND pabs.abs_information18 = ''N''/* nullable */
1850 /* include some training categories */
1851 AND pabs.abs_information1 IN (:SKILLS_ASSESSMENT,:VAE)
1852 /*and pabs.abs_information3 = ota_pv.vendor_id Training provider*/
1853 AND pabs.abs_information5 = ''EMPLOYER'' /* Subsidized type */
1854 AND pabs.absence_attendance_type_id = pabt.absence_attendance_type_id
1855 AND pabt.absence_category = ''TRAINING_ABSENCE'''||L_ORDER_BY;
1856 --
1857 --trace_sql(l_sql);
1858 l_c5 := 0;
1859 l_c6 := 0;
1860 if p_detail_section = 'NA' then
1861 OPEN l_ref_csr for l_sql using
1862 p_company_id,l_year_end,l_year_start,l_year_end,l_year_start
1863 ,l_year_start,l_meas_types_rec.SKILLS_ASSESSMENT,l_meas_types_rec.VAE
1864 ,l_meas_types_rec.DEDUCTIBLE_EXT_TRN_PLAN_SA
1865 ,l_meas_types_rec.DEDUCTIBLE_EXT_TRN_PLAN_VAE,l_header_rec.date2
1866 ,p_company_id,l_year_end,l_year_start,l_year_end,l_year_start
1867 ,l_header_rec.date2,l_header_rec.date1,l_header_rec.date2
1868 ,l_header_rec.date1,l_header_rec.date2
1869 ,'SKILLS_ASSESSMENT','VAE';
1870 fetch l_ref_csr into l_c5, l_c6;
1871 -- Assemble pdf XML...
1872 load_xml(p_xml,'c5',l_c5);
1873 load_xml(p_xml,'c6',l_c6);
1874 else -- debug
1875 if p_detail_section = 'B11' then
1876 OPEN l_ref_csr for l_sql using
1877 p_company_id,l_year_end,l_year_start,l_year_end,l_year_start
1878 ,l_year_start,l_meas_types_rec.SKILLS_ASSESSMENT
1879 ,l_meas_types_rec.SKILLS_ASSESSMENT
1880 ,l_meas_types_rec.DEDUCTIBLE_EXT_TRN_PLAN_SA
1881 ,l_meas_types_rec.DEDUCTIBLE_EXT_TRN_PLAN_SA,l_header_rec.date2
1882 ,p_company_id,l_year_end,l_year_start,l_year_end,l_year_start
1883 ,l_header_rec.date2,l_header_rec.date1
1884 ,l_header_rec.date2,l_header_rec.date1,l_header_rec.date2
1885 ,'SKILLS_ASSESSMENT','SKILLS_ASSESSMENT';
1886 else -- p_detail_section = 'B12'
1887 OPEN l_ref_csr for l_sql using
1888 p_company_id,l_year_end,l_year_start,l_year_end,l_year_start
1889 ,l_year_start,l_meas_types_rec.VAE,l_meas_types_rec.VAE
1890 ,l_meas_types_rec.DEDUCTIBLE_EXT_TRN_PLAN_VAE
1891 ,l_meas_types_rec.DEDUCTIBLE_EXT_TRN_PLAN_VAE,l_header_rec.date2
1892 ,p_company_id,l_year_end,l_year_start,l_year_end,l_year_start
1893 ,l_header_rec.date2,l_header_rec.date1
1894 ,l_header_rec.date2,l_header_rec.date1,l_header_rec.date2
1895 ,'VAE','VAE';
1896 end if; -- p_detail_section = 'B11'
1897 /* Bulk fetches from dynamic cursors not supported in 8.1.7 */
1898 l_prev_rec := l_empt_rec;
1899 -- Assemble rtf XML...
1900 load_xml(p_xml,'SECTION_'||p_detail_section,c_OpenGrpTag);
1901 loop
1902 FETCH l_ref_csr INTO l_curr_rec.order_name, l_curr_rec.full_name,
1903 l_curr_rec.emp_num, l_curr_rec.trn_start,
1904 l_curr_rec.trn_end, l_curr_rec.plan_name, l_curr_rec.class_name,
1905 l_curr_rec.num1;
1906 if (l_ref_csr%NOTFOUND
1907 or (l_curr_rec.full_name is null and
1908 l_prev_rec.full_name is not null))
1909 and l_ref_csr%ROWCOUNT > 0
1910 then
1911 if l_prev_rec.full_name is null then
1912 -- close previous PLAN_LIST
1913 load_xml(p_xml,'PLAN_LIST',c_CloseGrpTag);
1914 else
1915 -- Close previous EMP_LIST
1916 load_xml(p_xml,'EMP_LIST',c_CloseGrpTag);
1917 end if;
1918 end if;
1919 exit when l_ref_csr%NOTFOUND;
1920 if l_curr_rec.full_name is not null then
1921 if l_ref_csr%ROWCOUNT = 1 then
1922 -- open EMP_LIST
1923 load_xml(p_xml,'EMP_LIST',c_OpenGrpTag);
1924 end if;
1925 -- write EMP row
1926 load_xml(p_xml,'EMP',c_OpenGrpTag);
1927 load_xml(p_xml,'FULL_NAME',l_curr_rec.full_name);
1928 load_xml(p_xml,'EMPLOYEE_NUMBER',l_curr_rec.emp_num);
1929 load_xml(p_xml,'TRN_START',l_curr_rec.trn_start);
1930 load_xml(p_xml,'TRN_END',l_curr_rec.trn_end);
1931 load_xml(p_xml,'PLAN',l_curr_rec.plan_name);
1932 load_xml(p_xml,'CLASS',l_curr_rec.class_name);
1933 load_xml(p_xml,'EMP',c_CloseGrpTag);
1934 else -- PLAN row
1935 if l_prev_rec.full_name is not null
1936 or l_ref_csr%ROWCOUNT = 1 then
1937 -- open PLAN_LIST
1938 load_xml(p_xml,'PLAN_LIST',c_OpenGrpTag);
1939 end if; -- first PLAN
1940 -- write PLAN row
1941 load_xml(p_xml,'PLAN',c_OpenGrpTag);
1942 load_xml(p_xml,'PLAN_NAME',l_curr_rec.plan_name);
1943 load_xml(p_xml,'NUM_COURSES',l_curr_rec.num1);
1944 load_xml(p_xml,'PLAN',c_CloseGrpTag);
1945 end if;
1946 l_c5 := l_c5 + l_curr_rec.num1;
1947 l_prev_rec := l_curr_rec;
1948 end loop;
1949 load_xml(p_xml,'c5',l_c5);
1950 load_xml(p_xml,'SECTION_'||p_detail_section,c_CloseGrpTag);
1951 end if; -- p_detail_section = 'NA'
1952 close l_ref_csr;
1953 end if; -- p_detail_section in ('B11','B12','NA')
1954 --
1955 if p_detail_section = 'NA' then
1956 -- write XML for report date in pdf Declaration section
1957 load_xml(p_xml,'date',to_char(sysdate,'dd/mm/yyyy'));
1958 --
1959 -- Obtain training contribution rates and reduction if any
1960 open csr_comp_training_contrib_info(
1961 fnd_date.date_to_canonical(l_header_rec.date2));
1962 fetch csr_comp_training_contrib_info into l_training_contrib_rec;
1963 begin
1964 l_er_tp_contrib_rate :=
1965 fnd_number.canonical_to_number(
1966 hruserdt.get_table_value(l_header_rec.bg_id
1967 ,'FR_CONTRIBUTION_RATES'
1968 ,'Value (EUR)'
1969 ,'ER_TRAINING_PLAN_CONTRIBUTION'
1970 ,l_header_rec.date2));
1971 l_er_cif_contrib_rate :=
1972 fnd_number.canonical_to_number(
1973 hruserdt.get_table_value(l_header_rec.bg_id
1974 ,'FR_CONTRIBUTION_RATES'
1975 ,'Value (EUR)'
1976 ,'ER_CIF_CONTRIBUTION'
1977 ,l_header_rec.date2));
1978 l_er_alternance_contrib_rate :=
1979 fnd_number.canonical_to_number(
1980 hruserdt.get_table_value(l_header_rec.bg_id
1981 ,'FR_CONTRIBUTION_RATES'
1982 ,'Value (EUR)'
1983 ,'ER_ALTERNANCE_CONTRIBUTION'
1984 ,l_header_rec.date2));
1985 exception when others then
1986 null;
1987 end;
1988 --
1989 -- write XML for section C
1990 load_xml(p_xml,'C21'
1991 ,round((l_er_tp_contrib_rate
1992 +l_er_cif_contrib_rate
1993 +l_er_alternance_contrib_rate)
1994 *(100 - nvl(fnd_number.canonical_to_number(
1995 l_training_contrib_rec.reduction_chr)
1996 ,0))
1997 /100
1998 ,2));
1999 -- write XML for section D
2000 load_xml(p_xml,'C31'
2001 ,round(l_er_cif_contrib_rate
2002 *(100 - nvl(fnd_number.canonical_to_number(
2003 l_training_contrib_rec.reduction_chr)
2004 ,0))
2005 /100
2006 ,3));
2007 -- write XML for section E
2008 load_xml(p_xml,'C61'
2009 ,round(l_er_alternance_contrib_rate
2010 *(100 - nvl(fnd_number.canonical_to_number(
2011 l_training_contrib_rec.reduction_chr)
2012 ,0))
2013 /100
2014 ,3));
2015 --
2016 close csr_comp_training_contrib_info;
2017 end if;
2018 if p_detail_section in ('FA','NA') then
2019 if p_detail_section = 'NA' then
2020 hr_utility.trace('Section Fa PDF');
2021 L_SELECT_OUTER := 'select round(nvl(sum(tot),0)) C91 from (';
2022 L_ORDER_BY := ')';
2023 L_SELECT_INNER1:= 'select sum(decode(tpc.currency_code
2024 ,''EUR'',TPC.amount
2025 ,hr_currency_pkg.convert_amount_sql(tpc.currency_code
2026 ,''EUR''
2027 ,sysdate
2028 ,tpc.amount
2029 ,:CURR_RATE_TYPE))) tot ';
2030 L_GROUP_INNER1 := '
2031 ';
2032 L_SELECT_INNER2:= 'select
2033 sum(decode(nvl(pabs.abs_information8,bg_info.org_information10)
2034 ,''EUR'',fnd_number.canonical_to_number(pabs.abs_information11)
2035 ,hr_currency_pkg.convert_amount_sql(
2036 nvl(pabs.abs_information8,bg_info.org_information10)
2037 ,''EUR''
2038 ,sysdate
2039 ,nvl(fnd_number.canonical_to_number(pabs.abs_information11),0)
2040 ,:CURR_RATE_TYPE))) tot
2041 ';
2042 else -- p_detail_section = 'FA'
2043 hr_utility.trace('Section Fa RTF');
2044 L_SELECT_OUTER := null;
2045 L_SELECT_INNER1:= 'select
2046 costs.full_name
2047 ,costs.order_name
2048 ,costs.employee_number
2049 ,decode(costs.full_name,
2050 null,to_date(null),
2051 evt.course_start_date) trn_start
2052 ,decode(costs.full_name,
2053 null,to_date(null),
2054 evt.course_end_date) trn_end
2055 ,EVT_tl.title class_name
2056 ,costs.plan_name plan_name
2057 ,costs.trn_sal
2058 ,costs.admin_sal
2059 ,costs.running_costs
2060 ,costs.trn_tran
2061 ,costs.trn_accom
2062 ,costs.other
2063 ,costs.emp_tot
2064 from
2065 (select /*+ORDERED*/
2066 PER.full_name full_name
2067 ,PER.order_name order_name
2068 ,PER.employee_number employee_number
2069 ,nvl(odb.event_id,tpc.event_id) event_id
2070 ,tp.name plan_name
2071 ,sum(decode(TMT.tp_measurement_code
2072 ,''FR_DEDUCT_TRAINER_SALARY''
2073 ,decode(tpc.currency_code
2074 ,''EUR'',TPC.amount
2075 ,hr_currency_pkg.convert_amount_sql(tpc.currency_code
2076 ,''EUR''
2077 ,sysdate
2078 ,tpc.amount
2079 ,:CURR_RATE_TYPE))
2080 ,0)) trn_sal
2081 ,sum(decode(TMT.tp_measurement_code
2082 ,''FR_DEDUCT_ADMIN_SALARY''
2083 ,decode(tpc.currency_code
2084 ,''EUR'',TPC.amount
2085 ,hr_currency_pkg.convert_amount_sql(tpc.currency_code
2086 ,''EUR''
2087 ,sysdate
2088 ,tpc.amount
2089 ,:CURR_RATE_TYPE))
2090 ,0)) admin_sal
2091 ,sum(decode(TMT.tp_measurement_code
2092 ,''FR_DEDUCT_RUNNING_COSTS''
2093 ,decode(tpc.currency_code
2094 ,''EUR'',TPC.amount
2095 ,hr_currency_pkg.convert_amount_sql(tpc.currency_code
2096 ,''EUR''
2097 ,sysdate
2098 ,tpc.amount
2099 ,:CURR_RATE_TYPE))
2100 ,0)) running_costs
2101 ,sum(decode(TMT.tp_measurement_code
2102 ,''FR_DEDUCT_TRAINER_TRANSPRT''
2103 ,decode(tpc.currency_code
2104 ,''EUR'',TPC.amount
2105 ,hr_currency_pkg.convert_amount_sql(tpc.currency_code
2106 ,''EUR''
2107 ,sysdate
2108 ,tpc.amount
2109 ,:CURR_RATE_TYPE))
2110 ,0)) trn_tran
2111 ,sum(decode(TMT.tp_measurement_code
2112 ,''FR_DEDUCT_TRAINER_ACCOM''
2113 ,decode(tpc.currency_code
2114 ,''EUR'',TPC.amount
2115 ,hr_currency_pkg.convert_amount_sql(tpc.currency_code
2116 ,''EUR''
2117 ,sysdate
2118 ,tpc.amount
2119 ,:CURR_RATE_TYPE))
2120 ,0)) trn_accom
2121 ,sum(decode(TMT.tp_measurement_code
2122 ,''FR_OTHER_CLASS_DEDUCT_COST''
2123 ,decode(tpc.currency_code
2124 ,''EUR'',TPC.amount
2125 ,hr_currency_pkg.convert_amount_sql(tpc.currency_code
2126 ,''EUR''
2127 ,sysdate
2128 ,tpc.amount
2129 ,:CURR_RATE_TYPE))
2130 ,0)) other
2131 ,sum(decode(TMT.tp_measurement_code
2132 ,''FR_OTHER_LEARN_DEDUCT_INT''
2133 ,decode(tpc.currency_code
2134 ,''EUR'',TPC.amount
2135 ,hr_currency_pkg.convert_amount_sql(tpc.currency_code
2136 ,''EUR''
2137 ,sysdate
2138 ,tpc.amount
2139 ,:CURR_RATE_TYPE))
2140 ,0)) emp_tot
2141 ';
2142 L_GROUP_INNER1 := '
2143 group by PER.order_name,PER.employee_number
2144 ,nvl(ODB.event_id,TPC.event_id),tp.name,PER.full_name) costs,
2145 ota_events EVT,
2146 ota_events_tl evt_tl
2147 where costs.event_id = EVT.event_id
2148 and EVT.event_id = EVT_tl.event_id
2149 and EVT_tl.language = userenv(''LANG'')
2150 /*and EVT.vendor_id is null Internal training */
2151 /*and EVT.event_type = ''SCHEDULED''*/
2152 /*and evt.event_status <> ''A'' A=Cancelled. Nb. event_status is
2153 not null for SCHEDULED events*/
2154 /*and evt.course_start_date between PTP.start_date COURSE_START_DATE is */
2155 /* and ptp.end_date only not null for
2156 SCHEDULED events where
2157 they are Normal or Full*/
2158 ';
2159 L_SELECT_INNER2:= 'select
2160 per.full_name
2161 ,per.order_name
2162 ,per.employee_number
2163 ,pabs.date_start trn_start
2164 ,pabs.date_end trn_end
2165 ,null class_name
2166 ,null plan_name
2167 ,null trn_sal
2168 ,null admin_sal
2169 ,null running_costs
2170 ,null trn_tran
2171 ,null trn_accom
2172 ,null other
2173 ,decode(nvl(pabs.abs_information8,bg_info.org_information10)
2174 ,''EUR'',fnd_number.canonical_to_number(pabs.abs_information11)
2175 ,hr_currency_pkg.convert_amount_sql(
2176 nvl(pabs.abs_information8,bg_info.org_information10)
2177 ,''EUR''
2178 ,sysdate
2179 ,nvl(fnd_number.canonical_to_number(pabs.abs_information11),0)
2180 ,:CURR_RATE_TYPE)) emp_tot
2181 ';
2182 L_ORDER_BY := '
2183 order by 2 NULLS FIRST,3 NULLS FIRST,4 NULLS FIRST,6,7';
2184 end if; -- p_detail_section = 'NA'
2185 l_sql := L_SELECT_OUTER||L_SELECT_INNER1||' from
2186 hr_all_organization_units comp,
2187 hr_organization_information tp_org_info,
2188 hr_all_organization_units org,
2189 ota_training_plans TP,
2190 per_time_periods PTP,
2191 ota_training_plan_costs TPC,
2192 ota_tp_measurement_types TMT,
2193 ota_delegate_bookings ODB,
2194 per_all_people_f PER
2195 where comp.organization_id = :p_company_id
2196 and comp.date_from <= :p_year_end
2197 and (comp.date_to is null or
2198 comp.date_to >= :p_year_start) '
2199 ||L_WHERE_TP_ORG||'
2200 and org.organization_id = tp_org_info.organization_id
2201 and org.date_from <= :p_year_end
2202 and (org.date_to is null or
2203 org.date_to >= :p_year_start)
2204 and org.organization_id = TP.organization_id
2205 /*and TP.plan_status_type_id <> ''CANCELLED''*/
2206 and TP.time_period_id = PTP.time_period_id
2207 and PTP.period_type = ''Year''
2208 and PTP.start_date = :p_year_start
2209 and TP.training_plan_id = TPC.training_plan_id
2210 and TPC.tp_measurement_type_id = TMT.tp_measurement_type_id
2211 and TMT.business_group_id = org.business_group_id
2212 and ((TPC.tp_measurement_type_id in (:DEDUCTIBLE_TRAINER_SALARY,
2213 :DEDUCTIBLE_ADMIN_SALARY,
2214 :DEDUCTIBLE_RUNNING_COSTS,
2215 :DEDUCTIBLE_TRAINER_TRANSPRT,
2216 :DEDUCTIBLE_TRAINER_ACCOM,
2217 :OTHER_CLASS_DEDUCTIBLE_COST) AND
2218 TMT.cost_level = ''EVENT'') or
2219 (TPC.tp_measurement_type_id= :OTHER_LEARN_DEDUCT_COST_INT AND
2220 TMT.cost_level = ''DELEGATE''))
2221 AND TMT.unit = ''M''
2222 AND TPC.booking_id = ODB.booking_id(+)
2223 and ODB.delegate_person_id = PER.person_id(+)
2224 and :p_comp_end between PER.effective_start_date(+)
2225 AND PER.effective_end_date (+) '||
2226 L_GROUP_INNER1||' UNION ALL '||L_SELECT_INNER2||' from
2227 hr_all_organization_units comp,
2228 hr_organization_information estab_info,
2229 hr_all_organization_units estab,
2230 per_all_assignments_f ass,
2231 per_all_people_f per,
2232 per_absence_attendances pabs,
2233 per_absence_attendance_types pabt,
2234 hr_organization_information bg_info
2235 where comp.organization_id = :p_company_id
2236 and comp.date_from <= :p_year_end
2237 and (comp.date_to is null or
2238 comp.date_to >= :p_year_start)
2239 and estab_info.org_information_context = ''FR_ESTAB_INFO''
2240 and estab_info.org_information1 = to_char(comp.organization_id)
2241 and estab.organization_id = estab_info.organization_id
2242 and estab.date_from <= :p_year_end
2243 and (estab.date_to is null or
2244 estab.date_to >= :p_year_start)
2245 and estab.organization_id = ass.establishment_id
2246 AND ass.primary_flag = ''Y''
2247 /* rough filter on asg dates: */
2248 and ass.effective_start_date <= :p_comp_end
2249 and ass.effective_end_date >= :p_comp_start
2250 and ass.person_id = per.person_id
2251 and :p_comp_end between per.effective_start_date
2252 and per.effective_end_date
2253 and per.person_id = pabs.person_id
2254 and pabs.abs_information_category = ''FR_TRAINING_ABSENCE''
2255 and pabs.date_end between ass.effective_start_date
2256 and ass.effective_end_date
2257 and pabs.date_end between :p_comp_start
2258 and :p_comp_end
2259 /*Not Within Training Plan*/
2260 and pabs.abs_information18 = ''N''/* nullable */
2261 /* Training leave category */
2262 and (pabs.abs_information1 is null or
2263 pabs.abs_information1 not in (''TRAINING_CREDIT'',
2264 ''TRAINING_LEAVE''))
2265 and pabs.abs_information3 is null /* Training provider */
2266 and pabs.abs_information5 = ''EMPLOYER'' /* Subsidized type */
2267 and pabs.abs_information11 <> ''0''
2268 and pabs.absence_attendance_type_id= pabt.absence_attendance_type_id
2269 and pabt.absence_category = ''TRAINING_ABSENCE''
2270 AND bg_info.organization_id = comp.business_group_id
2271 and bg_info.org_information_context = ''Business Group Information'' '||
2272 L_ORDER_BY;
2273 --
2274 --trace_sql(l_sql);
2275 l_C91 := 0;
2276 if p_detail_section = 'NA' then
2277 OPEN l_ref_csr for l_sql using l_currency_rate_type
2278 ,p_company_id,l_year_end,l_year_start
2279 ,l_year_end,l_year_start,l_year_start
2280 ,l_meas_types_rec.DEDUCTIBLE_TRAINER_SALARY
2281 ,l_meas_types_rec.DEDUCTIBLE_ADMIN_SALARY
2282 ,l_meas_types_rec.DEDUCTIBLE_RUNNING_COSTS
2283 ,l_meas_types_rec.DEDUCTIBLE_TRAINER_TRANSPRT
2284 ,l_meas_types_rec.DEDUCTIBLE_TRAINER_ACCOM
2285 ,l_meas_types_rec.OTHER_CLASS_DEDUCTIBLE_COST
2286 ,l_meas_types_rec.OTHER_LEARN_DEDUCT_COST_INT
2287 ,l_header_rec.date2,l_currency_rate_type
2288 ,p_company_id,l_year_end
2289 ,l_year_start,l_year_end,l_year_start
2290 ,l_header_rec.date2,l_header_rec.date1
2291 ,l_header_rec.date2,l_header_rec.date1
2292 ,l_header_rec.date2;
2293 fetch l_ref_csr into l_C91;
2294 -- Assemble pdf XML...
2295 load_xml(p_xml,'C91',l_C91);
2296 close l_ref_csr;
2297 else
2298 l_currency_rate_type := hr_currency_pkg.get_rate_type(l_header_rec.bg_id
2299 ,sysdate,'R');
2300 --
2301 OPEN l_ref_csr for l_sql using l_currency_rate_type,l_currency_rate_type
2302 ,l_currency_rate_type,l_currency_rate_type
2303 ,l_currency_rate_type,l_currency_rate_type
2304 ,l_currency_rate_type
2305 ,p_company_id,l_year_end,l_year_start
2306 ,l_year_end,l_year_start,l_year_start
2307 ,l_meas_types_rec.DEDUCTIBLE_TRAINER_SALARY
2308 ,l_meas_types_rec.DEDUCTIBLE_ADMIN_SALARY
2309 ,l_meas_types_rec.DEDUCTIBLE_RUNNING_COSTS
2310 ,l_meas_types_rec.DEDUCTIBLE_TRAINER_TRANSPRT
2311 ,l_meas_types_rec.DEDUCTIBLE_TRAINER_ACCOM
2312 ,l_meas_types_rec.OTHER_CLASS_DEDUCTIBLE_COST
2313 ,l_meas_types_rec.OTHER_LEARN_DEDUCT_COST_INT
2314 ,l_header_rec.date2,l_currency_rate_type
2315 ,p_company_id,l_year_end
2316 ,l_year_start,l_year_end,l_year_start
2317 ,l_header_rec.date2,l_header_rec.date1
2318 ,l_header_rec.date2,l_header_rec.date1
2319 ,l_header_rec.date2;
2320 /* Bulk fetches from dynamic cursors not supported in 8.1.7
2321 FETCH l_ref_csr BULK COLLECT INTO
2322 tbl_full_name, tbl_order_name, tbl_emp_num, tbl_trn_start, tbl_trn_end,
2323 tbl_class_name, tbl_plan_name, tbl_num1, tbl_num2, tbl_num3, tbl_num4,
2324 tbl_num5, tbl_num6, tbl_num7;*/
2325 l_prev_rec := l_empt_rec;
2326 l_tot_trn_sal := 0;
2327 l_tot_admin_sal := 0;
2328 l_tot_run_costs := 0;
2329 l_tot_trn_tran := 0;
2330 l_tot_trn_accom := 0;
2331 l_tot_other := 0;
2332 l_total := 0;
2333 -- Assemble rtf XML...
2334 load_xml(p_xml,'SECTION_Fa',c_OpenGrpTag);
2335 loop
2336 FETCH l_ref_csr INTO
2337 l_curr_rec.full_name, l_curr_rec.order_name, l_curr_rec.emp_num,
2338 l_curr_rec.trn_start, l_curr_rec.trn_end, l_curr_rec.class_name,
2339 l_curr_rec.plan_name, l_curr_rec.num1, l_curr_rec.num2,
2340 l_curr_rec.num3, l_curr_rec.num4, l_curr_rec.num5, l_curr_rec.num6,
2341 l_curr_rec.num7;
2342 if (l_ref_csr%NOTFOUND
2343 or nvl(l_prev_rec.full_name,' ') <> nvl(l_curr_rec.full_name,' ')
2344 or nvl(l_prev_rec.emp_num,' ') <> nvl(l_curr_rec.emp_num,' '))
2345 and l_ref_csr%ROWCOUNT > 0
2346 then
2347 if l_prev_rec.full_name is not null then
2348 -- Close previous EMP
2349 load_xml(p_xml,'EMP',c_CloseGrpTag);
2350 if l_ref_csr%NOTFOUND then
2351 -- close previous EMP_LIST
2352 load_xml(p_xml,'TOTAL',l_total);
2353 load_xml(p_xml,'EMP_LIST',c_CloseGrpTag);
2354 l_C91 := l_C91+l_total;
2355 end if;
2356 elsif l_prev_rec.class_name is not null then
2357 -- close previous CLASS_LIST
2358 load_xml(p_xml,'TOT_TRN_SAL',l_tot_trn_sal);
2359 load_xml(p_xml,'TOT_ADMIN_SAL',l_tot_admin_sal);
2360 load_xml(p_xml,'TOT_RUN_COSTS',l_tot_run_costs);
2361 load_xml(p_xml,'TOT_TRN_TRAN',l_tot_trn_tran);
2362 load_xml(p_xml,'TOT_TRN_ACCOM',l_tot_trn_accom);
2363 load_xml(p_xml,'TOT_OTHER',l_tot_other);
2364 load_xml(p_xml,'TOTAL',l_total);
2365 load_xml(p_xml,'CLASS_LIST',c_CloseGrpTag);
2366 l_C91 := l_total;
2367 l_total := 0;
2368 end if;
2369 end if;
2370 exit when l_ref_csr%NOTFOUND;
2371 if l_curr_rec.full_name is null then
2372 if l_ref_csr%ROWCOUNT = 1 then
2373 -- open CLASS_LIST
2374 load_xml(p_xml,'CLASS_LIST',c_OpenGrpTag);
2375 end if;
2376 load_xml(p_xml,'CLASS',c_OpenGrpTag);
2377 load_xml(p_xml,'CLASS_NAME',l_curr_rec.class_name);
2378 load_xml(p_xml,'PLAN_NAME',l_curr_rec.plan_name);
2379 load_xml(p_xml,'TRN_SAL',l_curr_rec.num1);
2380 load_xml(p_xml,'ADMIN_SAL',l_curr_rec.num2);
2381 load_xml(p_xml,'RUNNING_COSTS',l_curr_rec.num3);
2382 load_xml(p_xml,'TRN_TRAN',l_curr_rec.num4);
2383 load_xml(p_xml,'TRN_ACCOM',l_curr_rec.num5);
2384 load_xml(p_xml,'OTHER',l_curr_rec.num6);
2385 l_curr_rec.num7 := l_curr_rec.num1
2386 +l_curr_rec.num2
2387 +l_curr_rec.num3
2388 +l_curr_rec.num4
2389 +l_curr_rec.num5
2390 +l_curr_rec.num6;
2391 load_xml(p_xml,'TOT',l_curr_rec.num7);
2392 load_xml(p_xml,'CLASS',c_CloseGrpTag);
2393 l_tot_trn_sal := l_tot_trn_sal + l_curr_rec.num1;
2394 l_tot_admin_sal := l_tot_admin_sal + l_curr_rec.num2;
2395 l_tot_run_costs := l_tot_run_costs + l_curr_rec.num3;
2396 l_tot_trn_tran := l_tot_trn_tran + l_curr_rec.num4;
2397 l_tot_trn_accom := l_tot_trn_accom + l_curr_rec.num5;
2398 l_tot_other := l_tot_other + l_curr_rec.num6;
2399 l_total := l_total + l_curr_rec.num7;
2400 else -- delegate / absence
2401 if nvl(l_prev_rec.full_name,' ') <> l_curr_rec.full_name
2402 or nvl(l_prev_rec.emp_num,' ') <> l_curr_rec.emp_num
2403 then
2404 if l_prev_rec.full_name is null
2405 and l_prev_rec.emp_num is null then
2406 -- open EMP_LIST
2407 load_xml(p_xml,'EMP_LIST',c_OpenGrpTag);
2408 end if;
2409 -- open EMP
2410 load_xml(p_xml,'EMP',c_OpenGrpTag);
2411 load_xml(p_xml,'FULL_NAME',l_curr_rec.full_name);
2412 load_xml(p_xml,'EMPLOYEE_NUMBER',l_curr_rec.emp_num);
2413 end if;
2414 load_xml(p_xml,'TRAINING',c_OpenGrpTag);
2415 load_xml(p_xml,'TRN_START',l_curr_rec.trn_start);
2416 load_xml(p_xml,'TRN_END',l_curr_rec.trn_end);
2417 load_xml(p_xml,'CLASS',l_curr_rec.class_name);
2418 load_xml(p_xml,'PLAN',l_curr_rec.plan_name);
2419 load_xml(p_xml,'TOT',l_curr_rec.num7);
2420 load_xml(p_xml,'TRAINING',c_CloseGrpTag);
2421 l_total := l_total + l_curr_rec.num7;
2422 end if;
2423 l_prev_rec := l_curr_rec;
2424 end loop;
2425 load_xml(p_xml,'C91',l_C91);
2426 load_xml(p_xml,'SECTION_Fa',c_CloseGrpTag);
2427 close l_ref_csr;
2428 end if; -- p_detail_section
2429 end if; -- section Fa
2430 --
2431 if p_detail_section in ('FB_CONTRACTED','FB_SA','FB_VAE','NA') then
2432 if p_detail_section = 'NA' then
2433 hr_utility.trace('Section Fb PDF');
2434 L_SELECT_OUTER := 'select
2435 round(nvl(sum(decode(trn_type,''CONTRACTED'',trn_cost)),0)) x1,
2436 round(nvl(sum(decode(trn_type,''SA'', trn_cost)),0)) x2,
2437 round(nvl(sum(decode(trn_type,''VAE'', trn_cost)),0)) x3
2438 from (
2439 ';
2440 L_ORDER_BY := ')';
2441 L_SELECT_INNER1:= 'select
2442 decode(TMT.tp_measurement_code,
2443 ''FR_DEDUCT_EXT_TRN_PLAN_VAE'',''VAE'',
2444 ''FR_DEDUCT_EXT_TRN_PLAN_SA'', ''SA'',
2445 ''FR_SKILLS_ASSESSMENT'', ''SA'',
2446 ''FR_VAE'', ''VAE'',
2447 ''CONTRACTED'') trn_type
2448 ,decode(tpc.currency_code
2449 ,''EUR'',TPC.amount
2450 ,hr_currency_pkg.convert_amount_sql(tpc.currency_code
2451 ,''EUR''
2452 ,sysdate
2453 ,tpc.amount
2454 ,:CURR_RATE_TYPE)) trn_cost
2455 ';
2456 L_GROUP_INNER1 := '
2457 ';
2458 L_SELECT_INNER2:= 'select
2459 decode(pabs.abs_information1,
2460 ''SKILLS_ASSESSMENT'',''SA'',
2461 ''VAE'', ''VAE'',
2462 ''CONTRACTED'') trn_type
2463 ,decode(nvl(pabs.abs_information8,bg_info.org_information10)
2464 ,''EUR'',fnd_number.canonical_to_number(pabs.abs_information11)
2465 ,hr_currency_pkg.convert_amount_sql(
2466 nvl(pabs.abs_information8,bg_info.org_information10)
2467 ,''EUR''
2468 ,sysdate
2469 ,nvl(fnd_number.canonical_to_number(pabs.abs_information11),0)
2470 ,:CURR_RATE_TYPE)) trn_cost
2471 ';
2472 L_WHERE_INNER2 := null;
2473 else -- p_detail_section like 'FB%'
2474 hr_utility.trace('Section '||p_detail_section||' RTF');
2475 L_SELECT_OUTER := null;
2476 L_SELECT_INNER1:= 'select
2477 costs.full_name
2478 ,costs.order_name
2479 ,costs.employee_number
2480 ,decode(costs.full_name,
2481 null,to_date(null),
2482 evt.course_start_date) trn_start
2483 ,decode(costs.full_name,
2484 null,to_date(null),
2485 evt.course_end_date) trn_end
2486 ,EVT_tl.title class_name
2487 ,ota_pv.vendor_name supplier_name
2488 ,costs.plan_name plan_name
2489 ,costs.trn_cost trn_cost
2490 ,costs.trn_cost_cc trn_cost_cc
2491 from
2492 (select /*+ORDERED*/
2493 PER.full_name full_name
2494 ,PER.order_name order_name
2495 ,PER.employee_number employee_number
2496 ,nvl(ODB.event_id,TPC.event_id) EVENT_ID
2497 ,tp.name plan_name
2498 ,tpc.amount trn_cost
2499 ,tpc.currency_code trn_cost_cc
2500 ';
2501 L_GROUP_INNER1 := ') costs,
2502 ota_events EVT,
2503 ota_events_tl evt_tl,
2504 po_vendors ota_pv
2505 where costs.event_id = EVT.event_id (+)
2506 and EVT.event_id = EVT_tl.event_id (+)
2507 and EVT_tl.language(+) = userenv(''LANG'')
2508 and EVT.vendor_id = ota_pv.vendor_id(+)
2509 /*and EVT.vendor_id is not null External training */
2510 /*and EVT.event_type = ''SCHEDULED''*/
2511 /*and evt.event_status <> ''A'' A=Cancelled. Nb. event_status is
2512 not null for SCHEDULED events*/
2513 /*and evt.course_start_date between PTP.start_date COURSE_START_DATE is */
2514 /* and ptp.end_date only not null for
2515 SCHEDULED events where
2516 they are Normal or Full*/
2517 ';
2518 L_SELECT_INNER2:= 'select
2519 per.full_name
2520 ,per.order_name
2521 ,per.employee_number
2522 ,pabs.date_start trn_start
2523 ,pabs.date_end trn_end
2524 ,null class_name
2525 ,ota_pv.vendor_name supplier_name
2526 ,null plan_name
2527 ,nvl(fnd_number.canonical_to_number(pabs.abs_information11),0) trn_cost
2528 ,nvl(pabs.abs_information8,bg_info.org_information10) trn_cost_cc
2529 ';
2530 L_WHERE_INNER2:= '
2531 and decode(pabs.abs_information1,
2532 ''SKILLS_ASSESSMENT'',''FB_SA'',
2533 ''VAE'', ''FB_VAE'',
2534 ''FB_CONTRACTED'') = :TRN_TYPE ';
2535 L_ORDER_BY := '
2536 order by 2 NULLS FIRST,3 NULLS FIRST ,4 NULLS FIRST,6 NULLS FIRST,8';
2537 end if; -- p_detail_section = 'NA'
2538 l_sql := L_SELECT_OUTER||L_SELECT_INNER1||' from
2539 hr_all_organization_units comp,
2540 hr_organization_information tp_org_info,
2541 hr_all_organization_units org,
2542 ota_training_plans TP,
2543 per_time_periods PTP,
2544 ota_training_plan_costs TPC,
2545 ota_tp_measurement_types TMT,
2546 ota_delegate_bookings ODB,
2547 per_all_people_f PER
2548 where comp.organization_id = :p_company_id
2549 and comp.date_from <= :p_year_end
2550 and (comp.date_to is null or
2551 comp.date_to >= :p_year_start) '
2552 ||L_WHERE_TP_ORG||'
2553 and org.organization_id = tp_org_info.organization_id
2554 and org.date_from <= :p_year_end
2555 and (org.date_to is null or
2556 org.date_to >= :p_year_start)
2557 and org.organization_id = TP.organization_id
2558 /*and TP.plan_status_type_id <> ''CANCELLED''*/
2559 and TP.time_period_id = PTP.time_period_id
2560 and PTP.period_type = ''Year''
2561 and PTP.start_date = :p_year_start
2562 and TP.training_plan_id = TPC.training_plan_id
2563 and TPC.tp_measurement_type_id = TMT.tp_measurement_type_id
2564 and TMT.business_group_id = org.business_group_id
2565 and ((TPC.tp_measurement_type_id in (:DEDUCTIBLE_EXT_TRN_PLAN,
2566 :DEDUCTIBLE_EXT_TRN_PLAN_SA,
2567 :DEDUCTIBLE_EXT_TRN_PLAN_VAE) AND
2568 TMT.cost_level = ''PLAN'') or
2569 (TPC.tp_measurement_type_id= :DEDUCTIBLE_EXT_TRN_CLASS AND
2570 TMT.cost_level = ''EVENT'') or
2571 (TPC.tp_measurement_type_id in (:SKILLS_ASSESSMENT,
2572 :VAE,
2573 :OTHER_LEARN_DEDUCT_COST_EXT) AND
2574 TMT.cost_level = ''DELEGATE''))
2575 AND TMT.unit = ''M''
2576 AND TPC.booking_id = ODB.booking_id(+)
2577 and ODB.delegate_person_id = PER.person_id(+)
2578 and :p_comp_end between PER.effective_start_date(+)
2579 AND PER.effective_end_date (+) '||
2580 L_GROUP_INNER1||'UNION ALL '||L_SELECT_INNER2||' from
2581 hr_all_organization_units comp,
2582 hr_organization_information estab_info,
2583 hr_all_organization_units estab,
2584 per_all_assignments_f ass,
2585 per_all_people_f per,
2586 per_absence_attendances pabs,
2587 per_absence_attendance_types pabt,
2588 po_vendors ota_pv,
2589 hr_organization_information bg_info
2590 where comp.organization_id = :p_company_id
2591 and comp.date_from <= :p_year_end
2592 and (comp.date_to is null or
2593 comp.date_to >= :p_year_start)
2594 and estab_info.org_information_context = ''FR_ESTAB_INFO''
2595 and estab_info.org_information1 = to_char(comp.organization_id)
2596 and estab.organization_id = estab_info.organization_id
2597 and estab.date_from <= :p_year_end
2598 and (estab.date_to is null or
2599 estab.date_to >= :p_year_start)
2600 and estab.organization_id = ass.establishment_id
2601 AND ass.primary_flag = ''Y''
2602 /* rough filter on asg dates: */
2603 and ass.effective_start_date <= :p_comp_end
2604 and ass.effective_end_date >= :p_comp_start
2605 and ass.person_id = per.person_id
2606 and :p_comp_end between per.effective_start_date
2607 and per.effective_end_date
2608 and per.person_id = pabs.person_id
2609 and pabs.abs_information_category = ''FR_TRAINING_ABSENCE''
2610 and pabs.date_end between ass.effective_start_date
2611 and ass.effective_end_date
2612 and pabs.date_end between :p_comp_start
2613 and :p_comp_end
2614 /*Not Within Training Plan*/
2615 and pabs.abs_information18 = ''N''/* nullable */
2616 /* Training leave category */ '||L_WHERE_INNER2||'
2617 and (pabs.abs_information1 is null or
2618 pabs.abs_information1 not in (''TRAINING_CREDIT'',
2619 ''TRAINING_LEAVE''))
2620 and pabs.abs_information3 = ota_pv.vendor_id /* Training provider*/
2621 and pabs.abs_information5 = ''EMPLOYER'' /* Subsidized type */
2622 and pabs.abs_information11 <> ''0''
2623 and pabs.absence_attendance_type_id = pabt.absence_attendance_type_id
2624 and pabt.absence_category = ''TRAINING_ABSENCE''
2625 AND bg_info.organization_id = comp.business_group_id
2626 and bg_info.org_information_context = ''Business Group Information'' '||
2627 L_ORDER_BY;
2628 --
2629 --trace_sql(l_sql);
2630 l_x1 := 0;
2631 l_x2 := 0;
2632 l_x3 := 0;
2633 if p_detail_section = 'NA' then
2634 OPEN l_ref_csr for l_sql using l_currency_rate_type
2635 ,p_company_id,l_year_end,l_year_start,l_year_end,l_year_start
2636 ,l_year_start,l_meas_types_rec.DEDUCTIBLE_EXT_TRN_PLAN
2637 ,l_meas_types_rec.DEDUCTIBLE_EXT_TRN_PLAN_SA
2638 ,l_meas_types_rec.DEDUCTIBLE_EXT_TRN_PLAN_VAE
2639 ,l_meas_types_rec.DEDUCTIBLE_EXT_TRN_CLASS
2640 ,l_meas_types_rec.SKILLS_ASSESSMENT,l_meas_types_rec.VAE
2641 ,l_meas_types_rec.OTHER_LEARN_DEDUCT_COST_EXT
2642 ,l_header_rec.date2,l_currency_rate_type
2643 ,p_company_id,l_year_end,l_year_start,l_year_end
2644 ,l_year_start,l_header_rec.date2,l_header_rec.date1
2645 ,l_header_rec.date2,l_header_rec.date1,l_header_rec.date2;
2646 fetch l_ref_csr into l_x1,l_x2,l_x3;
2647 -- Assemble pdf XML...
2648 load_xml(p_xml,'x1',l_x1);
2649 load_xml(p_xml,'x2',l_x2);
2650 load_xml(p_xml,'x3',l_x3);
2651 load_xml(p_xml,'C101',l_x1+l_x2+l_x3);
2652 close l_ref_csr;
2653 else -- Fb debug
2654 l_currency_rate_type := hr_currency_pkg.get_rate_type(l_header_rec.bg_id
2655 ,sysdate,'R');
2656 --
2657 if p_detail_section = 'FB_CONTRACTED' then
2658 OPEN l_ref_csr for l_sql using
2659 p_company_id,l_year_end,l_year_start,l_year_end,l_year_start
2660 ,l_year_start,l_meas_types_rec.DEDUCTIBLE_EXT_TRN_PLAN
2661 ,to_number(null),to_number(null)
2662 ,l_meas_types_rec.DEDUCTIBLE_EXT_TRN_CLASS
2663 ,to_number(null),to_number(null)
2664 ,l_meas_types_rec.OTHER_LEARN_DEDUCT_COST_EXT
2665 ,l_header_rec.date2
2666 ,p_company_id,l_year_end,l_year_start,l_year_end
2667 ,l_year_start,l_header_rec.date2,l_header_rec.date1
2668 ,l_header_rec.date2,l_header_rec.date1
2669 ,l_header_rec.date2,p_detail_section;
2670 elsif p_detail_section = 'FB_SA' then
2671 OPEN l_ref_csr for l_sql using
2672 p_company_id,l_year_end,l_year_start,l_year_end,l_year_start
2673 ,l_year_start,to_number(null)
2674 ,l_meas_types_rec.DEDUCTIBLE_EXT_TRN_PLAN_SA,to_number(null)
2675 ,to_number(null)
2676 ,l_meas_types_rec.SKILLS_ASSESSMENT,to_number(null)
2677 ,to_number(null)
2678 ,l_header_rec.date2
2679 ,p_company_id,l_year_end,l_year_start,l_year_end
2680 ,l_year_start,l_header_rec.date2,l_header_rec.date1
2681 ,l_header_rec.date2,l_header_rec.date1
2682 ,l_header_rec.date2,p_detail_section;
2683 else -- VAE
2684 OPEN l_ref_csr for l_sql using
2685 p_company_id,l_year_end,l_year_start,l_year_end,l_year_start
2686 ,l_year_start,to_number(null),to_number(null)
2687 ,l_meas_types_rec.DEDUCTIBLE_EXT_TRN_PLAN_VAE
2688 ,to_number(null),to_number(null),l_meas_types_rec.VAE
2689 ,to_number(null)
2690 ,l_header_rec.date2
2691 ,p_company_id,l_year_end,l_year_start,l_year_end
2692 ,l_year_start,l_header_rec.date2,l_header_rec.date1
2693 ,l_header_rec.date2,l_header_rec.date1
2694 ,l_header_rec.date2,p_detail_section;
2695 end if;
2696 /* Bulk fetches from dynamic cursors not supported in 8.1.7
2697 FETCH l_ref_csr BULK COLLECT INTO
2698 tbl_full_name, tbl_order_name, tbl_emp_num, tbl_trn_start, tbl_trn_end,
2699 tbl_class_name, tbl_plan_name, tbl_supplier, tbl_num1;*/
2700 l_prev_rec := l_empt_rec;
2701 -- Assemble rtf XML...
2702 load_xml(p_xml,'SECTION_Fb',c_OpenGrpTag);
2703 --load_xml(p_xml,'EMP_LIST',c_OpenGrpTag);
2704 loop
2705 FETCH l_ref_csr INTO l_curr_rec.full_name,
2706 l_curr_rec.order_name, l_curr_rec.emp_num, l_curr_rec.trn_start,
2707 l_curr_rec.trn_end, l_curr_rec.class_name, l_curr_rec.supplier,
2708 l_curr_rec.plan_name, l_curr_rec.num1, l_curr_rec.chr1;
2709 if (l_ref_csr%NOTFOUND and l_ref_csr%ROWCOUNT > 0) or
2710 ((nvl(l_prev_rec.full_name,' ') <> nvl(l_curr_rec.full_name,' ') or
2711 nvl(l_prev_rec.emp_num,' ') <> nvl(l_curr_rec.emp_num,' '))
2712 and l_ref_csr%ROWCOUNT > 1 )
2713 then
2714 -- close previous EMP
2715 load_xml(p_xml,'EMP',c_CloseGrpTag);
2716 end if;
2717 exit when l_ref_csr%NOTFOUND;
2718 if l_curr_rec.chr1 <> 'EUR' then
2719 l_curr_rec.num1 :=
2720 hr_currency_pkg.convert_amount(l_curr_rec.chr1
2721 ,'EUR'
2722 ,sysdate
2723 ,l_curr_rec.num1
2724 ,l_currency_rate_type);
2725 end if;
2726 if (l_ref_csr%ROWCOUNT = 1
2727 or nvl(l_prev_rec.full_name,' ') <> nvl(l_curr_rec.full_name,' ')
2728 or nvl(l_prev_rec.emp_num,' ') <> nvl(l_curr_rec.emp_num,' '))
2729 then
2730 -- open new EMP
2731 load_xml(p_xml,'EMP',c_OpenGrpTag);
2732 load_xml(p_xml,'FULL_NAME',l_curr_rec.full_name);
2733 load_xml(p_xml,'EMPLOYEE_NUMBER',l_curr_rec.emp_num);
2734 end if;
2735 load_xml(p_xml,'TRAINING',c_OpenGrpTag);
2736 load_xml(p_xml,'TRN_START',l_curr_rec.trn_start);
2737 load_xml(p_xml,'TRN_END',l_curr_rec.trn_end);
2738 load_xml(p_xml,'CLASS',l_curr_rec.class_name);
2739 load_xml(p_xml,'SUPPLIER_NAME',l_curr_rec.supplier);
2740 load_xml(p_xml,'PLAN',l_curr_rec.plan_name);
2741 load_xml(p_xml,'TRN_COST',l_curr_rec.num1);
2742 load_xml(p_xml,'TRAINING',c_CloseGrpTag);
2743 l_x1 := l_x1 + l_curr_rec.num1;
2744 l_prev_rec := l_curr_rec;
2745 end loop;
2746 --load_xml(p_xml,'EMP_LIST',c_CloseGrpTag);
2747 load_xml(p_xml,'TOTAL',l_x1);
2748 load_xml(p_xml,'SECTION_Fb',c_CloseGrpTag);
2749 close l_ref_csr;
2750 end if; -- p_detail_section
2751 end if; -- section Fb
2752 --
2753 if p_detail_section in ('FC','NA') then
2754 if p_detail_section = 'NA' then
2755 hr_utility.trace('Section Fc PDF');
2756 L_SELECT_OUTER := 'select round(nvl(sum(sal),0)) C111
2757 from (
2758 ';
2759 L_ORDER_BY := ')';
2760 L_SELECT_INNER1:= 'select
2761 decode(tpc_sal.currency_code
2762 ,''EUR'',TPC_sal.amount
2763 ,hr_currency_pkg.convert_amount_sql(tpc_sal.currency_code
2764 ,''EUR''
2765 ,sysdate
2766 ,tpc_sal.amount
2767 ,:CURR_RATE_TYPE)) sal
2768 ';
2769 L_SELECT_INNER2:= 'select
2770 decode(bg_info.org_information10
2771 ,''EUR'',fnd_number.canonical_to_number(pabs.abs_information21)
2772 ,hr_currency_pkg.convert_amount_sql(
2773 bg_info.org_information10
2774 ,''EUR''
2775 ,sysdate
2776 ,nvl(fnd_number.canonical_to_number(pabs.abs_information21),0)
2777 ,:CURR_RATE_TYPE)) sal
2778 ';
2779 else -- p_detail_section = 'FC'
2780 hr_utility.trace('Section Fc RTF');
2781 L_SELECT_OUTER := null;
2782 L_SELECT_INNER1:= 'select /*+ORDERED*/
2783 decode(tmt.tp_measurement_code,
2784 ''FR_SKILLS_ASSESSMENT'',2,
2785 ''FR_VAE'',3,
2786 1) class_order
2787 ,decode(tmt.tp_measurement_code,
2788 ''FR_SKILLS_ASSESSMENT'',HLK_tmt.meaning,
2789 ''FR_VAE'',HLK_tmt.meaning,
2790 EVT_tl.title) class_name
2791 ,PER.full_name full_name
2792 ,PER.order_name order_name
2793 ,PER.employee_number emp_num
2794 ,null leav_cat
2795 ,to_date(null) abs_st
2796 ,to_date(null) abs_en
2797 ,tp.name plan_name
2798 ,decode(tmt.tp_measurement_code,
2799 ''FR_ACTUAL_HOURS'',fnd_number.number_to_canonical(TPC_hrs.amount),
2800 ''FR_SKILLS_ASSESSMENT'',TPC_hrs.tp_cost_information3,
2801 ''FR_VAE'',TPC_hrs.tp_cost_information3) act_hrs
2802 ,decode(tmt.tp_measurement_code,
2803 ''FR_ACTUAL_HOURS'',TPC_hrs.tp_cost_information4,
2804 ''FR_SKILLS_ASSESSMENT'',TPC_hrs.tp_cost_information4,
2805 ''FR_VAE'',TPC_hrs.tp_cost_information4) out_hrs
2806 ,decode(tmt.tp_measurement_code,
2807 ''FR_ACTUAL_HOURS'',hlk_lcat.meaning) legal_cat
2808 ,tpc_sal.amount sal
2809 ,tpc_sal.currency_code sal_cc
2810 ';
2811 L_SELECT_INNER2:= 'select
2812 4 class_order
2813 ,''ABSENCE'' class_name
2814 ,PER.full_name full_name
2815 ,PER.order_name order_name
2816 ,PER.employee_number emp_num
2817 ,leavecat.meaning leav_cat
2818 ,pabs.date_start abs_st
2819 ,pabs.date_end abs_en
2820 ,null plan_name
2821 ,fnd_number.number_to_canonical(pabs.absence_hours) act_hrs
2822 ,pabs.abs_information20 out_hrs
2823 ,legalcat.meaning legal_cat
2824 ,nvl(fnd_number.canonical_to_number(pabs.abs_information21),0) sal
2825 ,bg_info.org_information10 sal_cc
2826 ';
2827 L_ORDER_BY := '
2828 order by 1,2,4,5,6,8';
2829 end if; -- p_detail_section = 'NA'
2830 l_sql := L_SELECT_OUTER||L_SELECT_INNER1||' from
2831 hr_all_organization_units comp,
2832 hr_organization_information tp_org_info,
2833 hr_all_organization_units org,
2834 ota_training_plans TP,
2835 per_time_periods PTP,
2836 ota_training_plan_costs TPC_sal,
2837 ota_training_plan_costs TPC_hrs,
2838 ota_tp_measurement_types TMT,
2839 ota_delegate_bookings ODB,
2840 ota_events EVT,
2841 per_all_people_f PER,
2842 hr_lookups HLK_tmt,
2843 hr_lookups HLK_lcat,
2844 ota_events_tl evt_tl
2845 where comp.organization_id = :p_company_id
2846 and comp.date_from <= :p_end_year
2847 and (comp.date_to is null or
2848 comp.date_to >= :p_start_year) '
2849 ||L_WHERE_TP_ORG||'
2850 and org.organization_id = tp_org_info.organization_id
2851 and org.date_from <= :p_end_year
2852 and (org.date_to is null or
2853 org.date_to >= :p_start_year)
2854 and org.organization_id = TP.organization_id
2855 /*and TP.plan_status_type_id <> ''CANCELLED''*/
2856 and TP.time_period_id = PTP.time_period_id
2857 and PTP.period_type = ''Year''
2858 and PTP.start_date = :p_start_year
2859 and TP.training_plan_id = TPC_sal.training_plan_id
2860 and TPC_sal.tp_measurement_type_id = :DEDUCTIBLE_LEARNER_SALARY
2861 and TPC_sal.booking_id = TPC_hrs.booking_id
2862 and TPC_sal.training_plan_id = TPC_hrs.training_plan_id
2863 and TPC_hrs.tp_measurement_type_id in (:ACTUAL_HOURS,
2864 :SKILLS_ASSESSMENT,
2865 :VAE)
2866 and TMT.tp_measurement_type_id = TPC_hrs.tp_measurement_type_id
2867 and TMT.cost_level = ''DELEGATE''
2868 and TMT.unit in (''M'',''N'')
2869 AND TPC_sal.booking_id = ODB.booking_id
2870 and ODB.delegate_person_id = PER.person_id
2871 and :p_end_comp between PER.effective_start_date
2872 AND PER.effective_end_date
2873 AND ODB.event_id = EVT.event_id
2874 /*and EVT.event_type = ''SCHEDULED''*/
2875 /*and evt.event_status <> ''A'' A=Cancelled. Nb. event_status is
2876 not null for SCHEDULED events*/
2877 /*and evt.course_start_date between p_start_year
2878 and p_end_year*/
2879 /* COURSE_START_DATE is only not null for SCHEDULED events where they are
2880 Normal or Full*/
2881 and hlk_tmt.lookup_type = ''OTA_PLAN_MEASUREMENT_TYPE''
2882 and hlk_tmt.lookup_code = TMT.tp_measurement_code
2883 and hlk_lcat.lookup_type(+) = ''FR_LEGAL_TRG_CATG''
2884 and hlk_lcat.lookup_code(+) = TPC_hrs.tp_cost_information3
2885 and EVT_tl.event_id = EVT.event_id
2886 and EVT_tl.language = userenv(''LANG'')
2887 UNION ALL '||L_SELECT_INNER2||' from
2888 hr_all_organization_units comp,
2889 hr_organization_information estab_info,
2890 hr_all_organization_units estab,
2891 per_all_assignments_f ass,
2892 per_all_people_f per,
2893 per_absence_attendances pabs,
2894 per_absence_attendance_types pabt,
2895 hr_lookups leavecat,
2896 hr_lookups legalcat,
2897 hr_organization_information bg_info
2898 where comp.organization_id = :p_company_id
2899 and comp.date_from <= :p_end_year
2900 and (comp.date_to is null or
2901 comp.date_to >= :p_start_year)
2902 and pabt.absence_category = ''TRAINING_ABSENCE''
2903 and pabs.absence_attendance_type_id = pabt.absence_attendance_type_id
2904 and pabs.abs_information_category = ''FR_TRAINING_ABSENCE''
2905 /* Not Within Training Plan */
2906 and pabs.abs_information18 = ''N''/*nullable*/
2907 and pabs.date_end between ass.effective_start_date
2908 and ass.effective_end_date
2909 and pabs.date_end between :p_start_comp
2910 and :p_end_comp
2911 and pabs.abs_information21 <> ''0''
2912 and per.person_id = pabs.person_id
2913 and ass.person_id = per.person_id
2914 and :p_end_comp between per.effective_start_date
2915 and per.effective_end_date
2916 and estab_info.organization_id = ass.establishment_id
2917 AND ass.primary_flag = ''Y''
2918 and estab.date_from <= :p_end_year
2919 and (estab.date_to is null or
2920 estab.date_to >= :p_start_year)
2921 and estab.organization_id = estab_info.organization_id
2922 and estab_info.org_information_context = ''FR_ESTAB_INFO''
2923 and estab_info.org_information1 = to_char(comp.organization_id)
2924 and leavecat.lookup_code(+) = pabs.abs_information1
2925 and leavecat.lookup_type(+) = ''FR_TRAINING_LEAVE_CATEGORY''
2926 and legalcat.lookup_code(+) = pabs.abs_information19
2927 and legalcat.lookup_type(+) = ''FR_LEGAL_TRG_CATG''
2928 /* rough filter on asg dates: */
2929 and ass.effective_start_date <= :p_comp_end
2930 and ass.effective_end_date >= :p_comp_start
2931 AND bg_info.organization_id = comp.business_group_id
2932 and bg_info.org_information_context = ''Business Group Information'' '||
2933 L_ORDER_BY;
2934 --
2935 --trace_sql(l_sql);
2936 l_C111 := 0;
2937 if p_detail_section = 'NA' then
2938 OPEN l_ref_csr for l_sql using l_currency_rate_type
2939 ,p_company_id,l_year_end,l_year_start
2940 ,l_year_end,l_year_start,l_year_start
2941 ,l_meas_types_rec.DEDUCTIBLE_LEARNER_SALARY
2942 ,l_meas_types_rec.ACTUAL_HOURS
2943 ,l_meas_types_rec.SKILLS_ASSESSMENT
2944 ,l_meas_types_rec.VAE
2945 ,l_header_rec.date2,l_currency_rate_type
2946 ,p_company_id,l_year_end,l_year_start
2947 ,l_header_rec.date1,l_header_rec.date2
2948 ,l_header_rec.date2,l_year_end,l_year_start
2949 ,l_header_rec.date2,l_header_rec.date1;
2950 fetch l_ref_csr into l_C111;
2951 -- Assemble pdf XML...
2952 load_xml(p_xml,'C111',l_C111);
2953 close l_ref_csr;
2954 else
2955 l_currency_rate_type := hr_currency_pkg.get_rate_type(l_header_rec.bg_id
2956 ,sysdate,'R');
2957 --
2958 OPEN l_ref_csr for l_sql using p_company_id,l_year_end,l_year_start
2959 ,l_year_end,l_year_start,l_year_start
2960 ,l_meas_types_rec.DEDUCTIBLE_LEARNER_SALARY
2961 ,l_meas_types_rec.ACTUAL_HOURS
2962 ,l_meas_types_rec.SKILLS_ASSESSMENT
2963 ,l_meas_types_rec.VAE
2964 ,l_header_rec.date2
2965 ,p_company_id,l_year_end,l_year_start
2966 ,l_header_rec.date1,l_header_rec.date2
2967 ,l_header_rec.date2,l_year_end,l_year_start
2968 ,l_header_rec.date2,l_header_rec.date1;
2969 /* Bulk fetches from dynamic cursors not supported in 8.1.7
2970 FETCH l_ref_csr BULK COLLECT INTO tbl_num1,tbl_class_name, tbl_full_name,
2971 tbl_order_name, tbl_emp_num,
2972 tbl_leave_cat, tbl_trn_start,
2973 tbl_trn_end, tbl_plan_name,
2974 tbl_act_hrs_chr, tbl_out_hrs_chr,
2975 tbl_legal_cat, tbl_num2;*/
2976 -- Assemble rtf XML...
2977 load_xml(p_xml,'SECTION_Fc',c_OpenGrpTag);
2978 l_total := 0;
2979 l_tot_act_hrs := 0;
2980 l_tot_out_hrs := 0;
2981 l_prev_rec := l_empt_rec;
2982 loop
2983 FETCH l_ref_csr INTO
2984 l_curr_rec.num1,l_curr_rec.class_name, l_curr_rec.full_name,
2985 l_curr_rec.order_name, l_curr_rec.emp_num,
2986 l_curr_rec.leave_cat, l_curr_rec.trn_start,
2987 l_curr_rec.trn_end, l_curr_rec.plan_name,
2988 l_curr_rec.act_hrs_chr, l_curr_rec.out_hrs_chr,
2989 l_curr_rec.legal_cat, l_curr_rec.num2, l_curr_rec.chr1;
2990 if l_prev_rec.num1 = 4 then
2991 if l_ref_csr%NOTFOUND
2992 or l_prev_rec.full_name <> l_curr_rec.full_name
2993 or l_prev_rec.emp_num <> l_curr_rec.emp_num
2994 then
2995 -- close EMP
2996 load_xml(p_xml,'EMP',c_CloseGrpTag);
2997 if l_ref_csr%NOTFOUND then
2998 -- close ABS_LIST
2999 load_xml(p_xml,'TOT_ACTHRS',l_tot_act_hrs);
3000 load_xml(p_xml,'TOT_OUTHRS',l_tot_out_hrs);
3001 load_xml(p_xml,'TOT_SAL',l_total);
3002 load_xml(p_xml,'ABS_LIST',c_CloseGrpTag);
3003 l_C111 := l_C111 + l_total;
3004 end if;
3005 end if;
3006 elsif l_ref_csr%ROWCOUNT > 0 then -- l_prev_rec.num1 in (1,2,3) or null
3007 if (l_ref_csr%NOTFOUND
3008 or l_prev_rec.num1 <> l_curr_rec.num1
3009 or l_prev_rec.class_name <> l_curr_rec.class_name)
3010 then
3011 -- Close CLASS
3012 load_xml(p_xml,'TOT_ACTHRS',l_tot_act_hrs);
3013 load_xml(p_xml,'TOT_OUTHRS',l_tot_out_hrs);
3014 load_xml(p_xml,'TOT_SAL',l_total);
3015 load_xml(p_xml,'CLASS',c_CloseGrpTag);
3016 l_C111 := l_C111 + l_total;
3017 l_total:= 0;
3018 l_tot_act_hrs := 0;
3019 l_tot_out_hrs := 0;
3020 end if;
3021 end if;
3022 exit when l_ref_csr%NOTFOUND;
3023 if l_curr_rec.chr1 <> 'EUR' then
3024 l_curr_rec.num2 :=
3025 hr_currency_pkg.convert_amount(l_curr_rec.chr1
3026 ,'EUR'
3027 ,sysdate
3028 ,l_curr_rec.num2
3029 ,l_currency_rate_type);
3030 end if;
3031 if l_curr_rec.num1 = 4 then
3032 if nvl(l_prev_rec.num1,3) <> 4
3033 or l_prev_rec.full_name <> l_curr_rec.full_name
3034 or l_prev_rec.emp_num <> l_curr_rec.emp_num
3035 then
3036 if nvl(l_prev_rec.num1,3) <> 4 then
3037 -- Open ABS_LIST
3038 load_xml(p_xml,'ABS_LIST',c_OpenGrpTag);
3039 end if;
3040 -- Open EMP
3041 load_xml(p_xml,'EMP',c_OpenGrpTag);
3042 load_xml(p_xml,'FULL_NAME',l_curr_rec.full_name);
3043 load_xml(p_xml,'EMPLOYEE_NUMBER',l_curr_rec.emp_num);
3044 end if;
3045 load_xml(p_xml,'ABS',c_OpenGrpTag);
3046 load_xml(p_xml,'LEAV_CAT',l_curr_rec.leave_cat);
3047 load_xml(p_xml,'ABS_ST',l_curr_rec.trn_start);
3048 load_xml(p_xml,'ABS_EN',l_curr_rec.trn_end);
3049 load_xml(p_xml,'LEGAL_CAT',l_curr_rec.legal_cat);
3050 load_xml(p_xml,'ACT_HRS',l_curr_rec.act_hrs_chr);
3051 load_xml(p_xml,'OUT_HRS',l_curr_rec.out_hrs_chr);
3052 load_xml(p_xml,'SAL',l_curr_rec.num2);
3053 load_xml(p_xml,'ABS',c_CloseGrpTag);
3054 l_tot_act_hrs := l_tot_act_hrs +
3055 fnd_number.canonical_to_number(nvl(l_curr_rec.act_hrs_chr,'0'));
3056 l_tot_out_hrs := l_tot_out_hrs +
3057 fnd_number.canonical_to_number(nvl(l_curr_rec.out_hrs_chr,'0'));
3058 l_total := l_total + l_curr_rec.num2;
3059 else -- l_curr_rec.num1 in (1,2,3)
3060 if nvl(l_prev_rec.num1,0) <> l_curr_rec.num1
3061 or l_prev_rec.class_name <> l_curr_rec.class_name
3062 then
3063 -- Open CLASS
3064 load_xml(p_xml,'CLASS',c_OpenGrpTag);
3065 load_xml(p_xml,'CLASS_NAME',l_curr_rec.class_name);
3066 end if;
3067 load_xml(p_xml,'STUDENT',c_OpenGrpTag);
3068 load_xml(p_xml,'FULL_NAME',l_curr_rec.full_name);
3069 load_xml(p_xml,'EMPLOYEE_NUMBER',l_curr_rec.emp_num);
3070 load_xml(p_xml,'PLAN_NAME',l_curr_rec.plan_name);
3071 load_xml(p_xml,'LEGAL_CAT',l_curr_rec.legal_cat);
3072 load_xml(p_xml,'ACT_HRS',l_curr_rec.act_hrs_chr);
3073 load_xml(p_xml,'OUT_HRS',l_curr_rec.out_hrs_chr);
3074 load_xml(p_xml,'SAL',l_curr_rec.num2);
3075 load_xml(p_xml,'STUDENT',c_CloseGrpTag);
3076 l_tot_act_hrs := l_tot_act_hrs +
3077 fnd_number.canonical_to_number(nvl(l_curr_rec.act_hrs_chr,'0'));
3078 l_tot_out_hrs := l_tot_out_hrs +
3079 fnd_number.canonical_to_number(nvl(l_curr_rec.out_hrs_chr,'0'));
3080 l_total := l_total + l_curr_rec.num2;
3081 end if;
3082 l_prev_rec := l_curr_rec;
3083 end loop;
3084 load_xml(p_xml,'C111',l_C111);
3085 load_xml(p_xml,'SECTION_Fc',c_CloseGrpTag);
3086 close l_ref_csr;
3087 end if;
3088 end if; -- section Fc
3089 --
3090 if p_detail_section = 'NA' then
3091 hr_utility.trace('Section Fd PDF');
3092 -- Assemble pdf XML for section Fd using l_C121
3093 load_xml(p_xml,'C121',l_C121);
3094 end if; -- section Fd
3095 --
3096 if p_detail_section in ('FH','NA') then
3097 if p_detail_section = 'NA' then
3098 hr_utility.trace('Section Fh PDF');
3099 L_SELECT_OUTER := 'select round(nvl(sum(decode(tpc.currency_code
3100 ,''EUR'',TPC.amount
3101 ,hr_currency_pkg.convert_amount_sql(tpc.currency_code
3102 ,''EUR''
3103 ,sysdate
3104 ,tpc.amount
3105 ,:CURR_RATE_TYPE))),0)) C151
3106 ';
3107 L_ORDER_BY := null;
3108 L_SELECT_INNER1:= null;
3109 else -- p_detail_section = 'FH'
3110 hr_utility.trace('Section Fh RTF');
3111 L_SELECT_OUTER := 'select /*+ORDERED*/
3112 TP.name plan_name
3113 ,tpc.amount amount
3114 ,tpc.currency_code cc
3115 ';
3116 L_SELECT_INNER1:= null;
3117 L_ORDER_BY := '
3118 order by tp.name';
3119 end if; -- p_detail_section = 'NA'
3120 l_sql := L_SELECT_OUTER||' from
3121 hr_all_organization_units comp,
3122 hr_organization_information tp_org_info,
3123 hr_all_organization_units org,
3124 ota_training_plans TP,
3125 per_time_periods PTP,
3126 ota_training_plan_costs TPC
3127 where comp.organization_id = :p_company_id
3128 and comp.date_from <= :p_end_year
3129 and (comp.date_to is null or
3130 comp.date_to >= :p_start_year) '
3131 ||L_WHERE_TP_ORG||'
3132 and org.organization_id = tp_org_info.organization_id
3133 and org.date_from <= :p_end_year
3134 and (org.date_to is null or
3135 org.date_to >= :p_start_year)
3136 and org.organization_id = TP.organization_id
3137 and TP.time_period_id = PTP.time_period_id
3138 and PTP.period_type = ''Year''
3139 and TP.training_plan_id = TPC.training_plan_id
3140 and TPC.tp_measurement_type_id = :OTHER_PLAN_DEDUCTIBLE_COSTS
3141 and PTP.start_date = :p_start_year
3142 and tpc.event_id is null
3143 and tpc.booking_id is null'||L_ORDER_BY;
3144 --
3145 --trace_sql(l_sql);
3146 l_C151 := 0;
3147 if p_detail_section = 'NA' then
3148 OPEN l_ref_csr for l_sql using l_currency_rate_type
3149 ,p_company_id,l_year_end,l_year_start
3150 ,l_year_end,l_year_start
3151 ,l_meas_types_rec.OTHER_PLAN_DEDUCTIBLE_COSTS
3152 ,l_year_start;
3153 fetch l_ref_csr into l_C151;
3154 -- Assemble pdf XML...
3155 load_xml(p_xml,'C151',l_C151);
3156 close l_ref_csr;
3157 else
3158 l_currency_rate_type := hr_currency_pkg.get_rate_type(l_header_rec.bg_id
3159 ,sysdate,'R');
3160 --
3161 OPEN l_ref_csr for l_sql using p_company_id,l_year_end,l_year_start
3162 ,l_year_end,l_year_start
3163 ,l_meas_types_rec.OTHER_PLAN_DEDUCTIBLE_COSTS
3164 ,l_year_start;
3165 /* Bulk fetches from dynamic cursors not supported in 8.1.7
3166 FETCH l_ref_csr BULK COLLECT INTO tbl_plan_name, tbl_num1;*/
3167 -- Assemble rtf XML...
3168 load_xml(p_xml,'SECTION_Fh',c_OpenGrpTag);
3169 loop
3170 FETCH l_ref_csr INTO l_curr_rec.plan_name, l_curr_rec.num1,
3171 l_curr_rec.chr1;
3172 exit when l_ref_csr%NOTFOUND;
3173 load_xml(p_xml,'PLAN',c_OpenGrpTag);
3174 load_xml(p_xml,'PLAN_NAME',l_curr_rec.plan_name);
3175 if l_curr_rec.chr1 = 'EUR' then
3176 -- no need to convert currency
3177 load_xml(p_xml,'AMOUNT',l_curr_rec.num1);
3178 else
3179 load_xml(p_xml,'AMOUNT'
3180 ,hr_currency_pkg.convert_amount(
3181 l_curr_rec.chr1
3182 ,'EUR'
3183 ,sysdate
3184 ,l_curr_rec.num1
3185 ,l_currency_rate_type));
3186 end if;
3187 load_xml(p_xml,'PLAN',c_CloseGrpTag);
3188 l_C151 := l_C151 + l_curr_rec.num1;
3189 end loop;
3190 load_xml(p_xml,'C151',l_C151);
3191 load_xml(p_xml,'SECTION_Fh',c_CloseGrpTag);
3192 close l_ref_csr;
3193 end if;
3194 end if; -- section Fh
3195 load_xml(p_xml,'FIELDS',c_CloseGrpTag);
3196 --
3197 --dbms_lob.createtemporary(p_xml,TRUE);
3198 --p_xml := g_xml;
3199 hr_utility.trace('Leaving otfr2483.build_XML');
3200 end build_XML;
3201 --
3202 PROCEDURE run_2483 (errbuf OUT NOCOPY VARCHAR2
3203 ,retcode OUT NOCOPY NUMBER
3204 ,p_business_group_id IN NUMBER
3205 ,p_template_id IN NUMBER
3206 ,p_company_id IN NUMBER
3207 ,p_calendar IN VARCHAR2
3208 ,p_time_period_id IN NUMBER
3209 ,p_currency_code IN VARCHAR2
3210 ,p_process_name IN VARCHAR2
3211 ,p_debug IN VARCHAR2) IS
3212 --
3213 l_prmrec hr_summary_util.prmTabType;
3214 l_stmt VARCHAR2(32000);
3215 l_start_of_plan VARCHAR2(100);
3216 l_end_of_plan VARCHAR2(100);
3217 l_select2 VARCHAR2(200);
3218 l_new_tp_string VARCHAR2(300);
3219 l_new_est_string VARCHAR2(300);
3220 --
3221 BEGIN
3222 --
3223 l_select2 := '(SELECT organization_id establishment_id FROM hr_fr_establishments_v WHERE company_org_id = ''';
3224 l_select2 := l_select2 || to_char(p_company_id) || '''' || ' OR organization_id = ';
3225 l_select2 := l_select2 || to_char(p_company_id) || ') v ';
3226 --
3227 l_new_tp_string := '(SELECT training_plan_id FROM ota_training_plans WHERE time_period_id = ';
3228 l_new_tp_string := l_new_tp_string || to_char(p_time_period_id) || ' and ( ( organization_id in ( select organization_id ';
3229 l_new_tp_string := l_new_tp_string || ' from hr_fr_establishments_v where company_org_id = ''' ;
3230 l_new_tp_string := l_new_tp_string || to_char(p_company_id) || '''' || ')) or ( organization_id = ';
3231 l_new_tp_string := l_new_tp_string || to_char(p_company_id) || ' )))';
3232 --
3233 l_new_est_string := '(SELECT organization_id organization_id FROM hr_fr_establishments_v WHERE company_org_id = ''';
3234 l_new_est_string := l_new_est_string || to_char(p_company_id) || '''' || ' OR organization_id = ';
3235 l_new_est_string := l_new_est_string || to_char(p_company_id) || ' )';
3236 --
3237 begin
3238 SELECT 'to_date('''||to_char(ptp.start_date,'YYYYMMDD')||''',''YYYYMMDD'')'
3239 , 'to_date('''||to_char(ptp.end_date,'YYYYMMDD')||''',''YYYYMMDD'')'
3240 INTO l_start_of_plan,
3241 l_end_of_plan
3242 FROM per_time_periods ptp
3243 WHERE ptp.time_period_id = p_time_period_id;
3244 exception
3245 when others then null;
3246 end;
3247 --
3248 l_prmrec(1).name := 'P_BUSINESS_GROUP_ID';
3249 l_prmrec(1).value := p_business_group_id;
3250 --
3251 l_prmrec(2).name := 'P_COMPANY_ID';
3252 l_prmrec(2).value := p_company_id;
3253 --
3254 l_prmrec(3).name := 'P_TIME_PERIOD_ID';
3255 l_prmrec(3).value := p_time_period_id;
3256 --
3257 l_prmrec(4).name := 'P_TRAINING_PLAN_LIST';
3258 l_prmrec(4).value := l_new_tp_string;
3259 --
3260 l_prmrec(5).name := 'P_START_OF_PLAN';
3261 l_prmrec(5).value := l_start_of_plan;
3262 --
3263 l_prmrec(6).name := 'P_END_OF_PLAN';
3264 l_prmrec(6).value := l_end_of_plan;
3265 --
3266 l_prmrec(7).name := 'P_CURRENCY_CODE';
3267 l_prmrec(7).value := ''''||p_currency_code||'''';
3268 --
3269 l_prmrec(8).name := 'P_ESTABLISHMENT_TABLE';
3270 l_prmrec(8).value := l_select2;
3271 --
3272 l_prmrec(9).name := 'P_ESTABLISHMENT_LIST';
3273 l_prmrec(9).value := l_new_est_string;
3274 --
3275 hrsumrep.process_run(p_business_group_id => p_business_group_id
3276 ,p_process_type => '2483'
3277 ,p_template_id => p_template_id
3278 ,p_process_name => p_process_name
3279 ,p_parameters => l_prmrec
3280 ,p_store_data => TRUE
3281 ,p_statement => l_stmt
3282 ,p_retcode => retcode
3283 ,p_debug => 'N');
3284 --
3285 EXCEPTION WHEN OTHERS THEN
3286 retcode :=2; /* Critical Error */
3287 errbuf := sqlerrm;
3288 END run_2483;
3289 --
3290 END otfr2483;