[Home] [Help]
PACKAGE BODY: APPS.PER_DIF_STMT_REPORT
Source
1 PACKAGE BODY PER_DIF_STMT_REPORT AS
2 /* $Header: perfrdif.pkb 120.3 2006/09/19 21:30:05 aparkes noship $ */
3 --
4 g_xml_ctr NUMBER;
5 ---------------------------------------------------
6 -- Main procedure for building XML string
7 ------------------------------------------------------
8 PROCEDURE dif_main_fill_table(p_business_group_id NUMBER,
9 p_estab_id NUMBER DEFAULT NULL,
10 p_accrual_plan_id NUMBER ,
11 p_start_year VARCHAR2, -- added to match parameters with CP
12 p_start_month NUMBER, -- added to match parameters with CP
13 p_date_from VARCHAR2,
14 p_end_year VARCHAR2, -- added to match parameters with CP
15 p_end_month NUMBER, -- added to match parameters with CP
16 p_date_to VARCHAR2,
17 p_emp_id NUMBER DEFAULT NULL,
18 p_sort_order VARCHAR2,
19 p_template_name VARCHAR2, -- added to match parameters with CP
20 p_xml OUT NOCOPY CLOB)
21 IS
22 --
23 l_order_by VARCHAR2(40);
24 l_where VARCHAR2(1500);
25 l_where_emp VARCHAR2(500);
26 l_where_estab VARCHAR2(500);
27 l_select VARCHAR2(500);
28 l_estab_id NUMBER;
29 l_emp_id NUMBER;
30 l_full_name per_all_people_f.full_name%TYPE;
31 l_emp_num per_all_people_f.employee_number%TYPE;
32 l_xml clob;
33 l_date_from date;
34 l_date_to date;
35 --
36 TYPE ref_cursor_type IS REF CURSOR;
37 ref_csr_emp_list ref_cursor_type;
38 --
39 -- Cursor for fetching field names
40 cursor csr_get_lookup is
41 select lookup_code,meaning
42 from hr_lookups
43 where lookup_type='FR_DIF_STMT_LOOKUP_CODE'
44 and lookup_code <> 'TOTAL'; -- added for 5111065
45 --
46 -- Cursor to specifically fetch the code and meaning
47 -- of 'TOTAL' from name_translations
48 -- added for 5111065
49 cursor csr_get_lookup_total is
50 select lookup_code,meaning
51 from hr_lookups
52 where lookup_type='NAME_TRANSLATIONS'
53 and lookup_code = 'TOTAL';
54 --
55 BEGIN
56 hr_utility.set_location('Entering dif_main_fill_table', 10);
57 -- Initialize the xml table counter
58 g_xml_ctr :=0;
59 -- Delete values in the table
60 xml_table.delete;
61 hr_utility.set_location('Deleted rows in xml table', 20);
62 --
63 -- Set the date values
64 l_date_from := fnd_date.canonical_to_date(p_date_from);
65 l_date_to := fnd_date.canonical_to_date(p_date_to);
66 --
67 -- Get a list of employees for this establishment
68 -- and accrual plan, if any
69 -- Build the query dynamically
70 l_select := 'select distinct per.person_id emp_id, estab.organization_id estab_id, per.full_name name, per.employee_number empnum from per_all_people_f per, per_all_assignments_f ass, pay_element_entries_f ent, '||
71 'pay_accrual_plans acc, hr_all_organization_units estab';
72 l_where := ' where per.person_id = ass.person_id and per.business_group_id = '||p_business_group_id||' and '|| '''' ||l_date_to|| ''''||' between per.effective_start_date '
73 ||'and per.effective_end_date and estab.business_group_id = per.business_group_id and ass.establishment_id = estab.organization_id '
74 ||'and ass.effective_end_date >= '|| '''' ||l_date_from|| ''''||' and ass.effective_start_date <= '|| '''' ||l_date_to|| ''''||' and ass.assignment_id = ent.assignment_id '
75 ||'and ent.element_type_id = acc.accrual_plan_element_type_id and acc.accrual_plan_id = '||p_accrual_plan_id||' and ent.effective_end_date >= '|| '''' ||l_date_from|| ''''||' and ent.effective_start_date <= '|| '''' ||l_date_to|| ''''||'';
76 --
77 IF p_emp_id IS NULL THEN
78 l_where_emp := ' ';
79 ELSE
80 l_where_emp := 'and per.person_id = '||p_emp_id||'';
81 END IF;
82 --
83 hr_utility.set_location('Where emp clause is: '||l_where_emp, 30);
84 --
85 IF p_estab_id IS NULL THEN
86 l_where_estab := ' ';
87 ELSE
88 l_where_estab := 'and estab.organization_id = '||p_estab_id||'';
89 END IF;
90 --
91 hr_utility.set_location('Where estab clause: '||l_where_estab, 30);
92 --
93 IF p_sort_order = 'NAME' THEN
94 l_order_by := ' order by per.full_name';
95 ELSIF p_sort_order = 'NUMBER' THEN
96 l_order_by := ' order by fnd_number.canonical_to_number(per.employee_number)';
97 END IF;
98 --
99 hr_utility.set_location('Order by clause is: '||l_order_by, 30);
100 --
101 OPEN ref_csr_emp_list FOR l_select||l_where||l_where_emp||l_where_estab||l_order_by;
102 LOOP
103 FETCH ref_csr_emp_list INTO l_emp_id, l_estab_id, l_full_name, l_emp_num;
104 EXIT WHEN ref_csr_emp_list%NOTFOUND;
105 -- Set the start label
106 g_xml_ctr := g_xml_ctr +1;
107 xml_table( g_xml_ctr).tag_name := 'L_MAIN';
108 xml_table( g_xml_ctr).tag_value := '1';
109 --
110 hr_utility.set_location('g_xml_ctr after main start is: '||to_char(g_xml_ctr), 40);
111 -- populate field names for each employee
112 FOR get_lookup_rec IN csr_get_lookup LOOP
113 --
114 g_xml_ctr := g_xml_ctr +1;
115 xml_table(g_xml_ctr).tag_name := get_lookup_rec.lookup_code;
116 xml_table(g_xml_ctr).tag_value := get_lookup_rec.meaning;
117 --
118 END LOOP;
119 --
120 -- added for 5111065
121 FOR get_lookup_total_rec IN csr_get_lookup_total LOOP
122 --
123 g_xml_ctr := g_xml_ctr +1;
124 xml_table(g_xml_ctr).tag_name := get_lookup_total_rec.lookup_code;
125 xml_table(g_xml_ctr).tag_value := get_lookup_total_rec.meaning;
126 --
127 END LOOP;
128 --
129 hr_utility.set_location('g_xml_ctr after dif lookup is: '||to_char(g_xml_ctr), 40);
130 --
131 -- Call the procedure to fetch all report values
132 dif_emp_acc_details(p_business_group_id => p_business_group_id,
133 p_estab_id => l_estab_id,
134 p_accrual_plan_id => p_accrual_plan_id,
135 p_emp_id => l_emp_id,
136 p_date_from => l_date_from,
137 p_date_to => l_date_to);
138 --
139 hr_utility.set_location('g_xml_ctr before main end is: '||to_char(g_xml_ctr), 40);
140 -- Set the end label
141 g_xml_ctr := g_xml_ctr +1;
142 xml_table( g_xml_ctr).tag_name := 'L_MAIN';
143 xml_table( g_xml_ctr).tag_value := '0';
144 --
145 END LOOP;
146 --
147 hr_utility.set_location('g_xml_ctr at end is: '||to_char(g_xml_ctr), 40);
148 -- Call the procedure to write to clob
149 write_to_clob(l_xml);
150 p_xml:= l_xml;
151 hr_utility.set_location('Exiting dif_main_fill_table', 10);
152 --
153 END dif_main_fill_table;
154 --
155 --------------------------------------------------------------
156 -- procedure for fetching employee, estab and accrual details
157 -- for each employee
158 ---------------------------------------------------------------
159 PROCEDURE dif_emp_acc_details(p_business_group_id NUMBER,
160 p_estab_id NUMBER ,
161 p_accrual_plan_id NUMBER,
162 p_emp_id NUMBER,
163 p_date_from DATE,
164 p_date_to DATE)
165 IS
166 --
167 l_rowcount number;
168 l_total_absence_duration number;
169 l_prev_asg_catg varchar2(3);
170 l_assignment_id number;
171 l_payroll_id number;
172 l_total_carryover number;
173 l_co_start_date date;
174 l_period_entitlement number;
175 l_total_dif_accrual number;
176 l_total_accrual number;
177 l_period_adjustments number;
178 l_period_absence number;
179 l_total_adjustments number;
180 l_start_date date;
181 l_end_date date;
182 l_dummy_date date;
183 l_dif_balance number;
184 --
185 -- Declare table for storing accrual periods
186 TYPE acc_dates_rec is RECORD
187 (accrual_start_date date,
188 accrual_end_date date,
189 payroll_id number,
190 wkg_hrs number);
191
192 TYPE acc_dates_tab is TABLE of acc_dates_rec INDEX by BINARY_INTEGER;
193
194 acc_period_dates acc_dates_tab;
195 --
196 -- Cursor for accrual and adjustment field label
197 cursor csr_get_repeat_lookup(c_lookup_code varchar2) is
198 select lookup_code,meaning
199 from hr_lookups
200 where lookup_type='FR_DIF_STMT_LOOKUP_CODE'
201 and lookup_code = c_lookup_code;
202 --
203 -- Cursor for establishment details in header
204 Cursor csr_hdr_estab_details is
205 Select 'er_hdr_estab_name',
206 estab_tl.name ,
207 'er_hdr_estab_addr_compl',
208 estab_loc.address_line_2 ,
209 'er_hdr_estab_addr_nstreet',
210 estab_loc.address_line_1,
211 'er_hdr_estab_addr_town',
212 estab_loc.region_3,
213 'er_hdr_estab_addr_zip',
214 estab_loc.postal_code,
215 'er_hdr_estab_addr_towncity',
216 estab_loc.town_or_city,
217 'er_hdr_estab_country',
218 estab_ft.nls_territory,
219 'er_hdr_comp_name',
220 comp_tl.name,
221 'er_hdr_comp_addr_compl',
222 comp_loc.address_line_2,
223 'er_hdr_comp_addr_nstreet',
224 comp_loc.address_line_1,
225 'er_hdr_comp_addr_town',
226 comp_loc.region_3,
227 'er_hdr_comp_addr_zip',
228 comp_loc.postal_code,
229 'er_hdr_comp_addr_towncity',
230 comp_loc.town_or_city,
231 'er_hdr_comp_country',
232 comp_ft.nls_territory,
233 'er_hdr_estab_siret',
234 estab_info.org_information2
235 from hr_all_organization_units estab,
236 hr_all_organization_units_tl estab_tl,
237 hr_all_organization_units comp,
238 hr_all_organization_units_tl comp_tl,
239 hr_organization_information estab_info,
240 hr_locations_all estab_loc,
241 hr_locations_all comp_loc,
242 fnd_territories estab_ft,
243 fnd_territories comp_ft
244 where estab.organization_id = p_estab_id
245 and estab.business_group_id = p_business_group_id
246 and estab_tl.organization_id = estab.organization_id
247 and estab_tl.language = userenv('LANG')
248 and estab_info.organization_id(+) = estab.organization_id
249 and estab_info.org_information_context(+) = 'FR_ESTAB_INFO'
250 and comp.organization_id(+) = estab_info.org_information1
251 and comp.business_group_id(+) = p_business_group_id
252 and comp_tl.organization_id(+) = comp.organization_id
253 and comp_tl.language(+) = userenv('LANG')
254 and estab_loc.location_id(+) = estab.location_id
255 and estab_loc.style(+) ='FR'
256 and comp_loc.location_id(+) = comp.location_id
257 and comp_loc.style(+) ='FR'
258 and estab_ft.territory_code(+) = estab_loc.country
259 and comp_ft.territory_code(+) = comp_loc.country;
260 --
261 -- Cursor for employee details in header
262 Cursor csr_emp_hdr_details is
263 Select 'emp_hdr_full_name' ,
264 per.full_name ,
265 'emp_hdr_empnum',
266 per.employee_number,
267 'emp_hdr_addr_compl',
268 per_addr.address_line2,
269 'emp_hdr_addr_nstreet',
270 per_addr.address_line1,
271 'emp_hdr_addr_town',
272 per_addr.region_3,
273 'emp_hdr_addr_zip',
274 per_addr.postal_code,
275 'emp_hdr_addr_towncity',
276 per_addr.town_or_city,
277 'emp_hdr_country',
278 per_ft.nls_territory ,
279 'emp_hdr_hiredate',
280 to_char(per.original_date_of_hire, 'dd-Mon-yy'),
281 'emp_hdr_adj_svc_date',
282 null, -- adjusted service date
283 'emp_hdr_term_date',
284 decode(serv.actual_termination_date, hr_general.end_of_time, null, to_char(serv.actual_termination_date, 'dd-Mon-yy')),
285 'emp_hdr_coll_aggr',
286 col_agr.name,
287 'emp_hdr_asg_catg',
288 hr_general.decode_lookup('EMP_CAT', asg.employment_category),
289 asg.assignment_id,
290 asg.payroll_id
291 from per_all_people_f per,
292 per_addresses per_addr,
293 fnd_territories per_ft,
294 per_periods_of_service serv,
295 per_all_assignments_f asg,
296 per_collective_agreements col_agr
297 where per.person_id = p_emp_id
298 and asg.person_id = per.person_id
299 and per.effective_end_date = (select max(effective_end_date)
300 from per_all_people_f
301 where person_id = per.person_id
302 and effective_end_date >= p_date_from)
303 and asg.effective_end_date =(select max(effective_end_date)
304 from per_all_assignments_f
305 where person_id = per.person_id
306 and effective_end_date >= p_date_from)
307 and serv.person_id = per.person_id
308 and (serv.actual_termination_date is null
309 or serv.actual_termination_date= (select greatest(actual_termination_date)
310 from per_periods_of_service
311 where person_id = per.person_id
312 and actual_termination_date > per.effective_start_date))
313 and per_addr.person_id(+) = per.person_id
314 and per_addr.primary_flag(+) = 'Y'
315 and per_ft.territory_code(+) = per_addr.country
316 and col_agr.collective_agreement_id(+) = asg.collective_agreement_id;
317 --
318 -- Cursor for contract details in header
319 Cursor csr_ctr_hdr_details is
320 select to_char(ctr.effective_start_date, 'dd-Mon-yy') ctr_hdr_start,
321 decode(ctr.effective_end_date, hr_general.end_of_time, null,to_char(ctr.effective_end_date, 'dd-Mon-yy')) ctr_hdr_end,
322 hr_general.decode_lookup('CONTRACT_TYPE',ctr.type) ctr_hdr_type,
323 hr_general.decode_lookup('FR_CONTRACT_CATEGORY',ctr_information2) ctr_hdr_category,
324 decode(ctr.ctr_information12, 'HOUR', ctr.ctr_information11, to_char(asg.normal_hours)) ctr_hdr_hours
325 from per_contracts_f ctr,
326 per_all_assignments_f asg
327 where asg.person_id = p_emp_id
328 and asg.effective_start_date <= p_date_to
329 and asg.effective_end_date >= p_date_from
330 and asg.contract_id(+) = ctr.contract_id
331 and ctr.effective_end_date =(select greatest(effective_end_date)
332 from per_contracts_f
333 where contract_id= ctr.contract_id
334 and effective_end_date >= p_date_from)
335 and ctr_information_category(+) = 'FR';
336 --
337 -- Cursor for accrual details in header
338 Cursor csr_acc_hdr_details is
339 select 'acc_hdr_plan_name',
340 acc.accrual_plan_name ,
341 'acc_hdr_enrol_start',
342 to_char(ent.effective_start_date, 'dd-Mon-yy') ,
343 'acc_hdr_enrol_end',
344 decode(ent.effective_end_date, hr_general.end_of_time,null, to_char(ent.effective_end_date, 'dd-Mon-yy'))
345 from pay_accrual_plans acc,
346 pay_element_entries_f ent,
347 per_all_assignments_f asg
348 where acc.accrual_plan_id = p_accrual_plan_id
349 and asg.person_id = p_emp_id
350 and ent.assignment_id = asg.assignment_id
351 and ent.element_type_id = acc.accrual_plan_element_type_id
352 and ent.effective_start_date = (select max(effective_start_date)
353 from pay_element_entries_f
354 where assignment_id = ent.assignment_id
355 and element_type_id = acc.accrual_plan_element_type_id
356 and effective_start_date <= p_date_to
357 and effective_end_date >= p_date_from);
358 --
359 ---------------------------------------
360 -- Cursors for report body
361 ------------------------------------------
362 -- Cursor for selecting the greater of enrollment date and hiredate
363 Cursor csr_get_co_start is
364 select greatest(ent.effective_start_date, per.original_date_of_hire) co_start_date
365 from pay_element_entries_f ent,
366 per_all_assignments_f asg,
367 per_all_people_f per,
368 pay_accrual_plans acc
369 where acc.accrual_plan_id = p_accrual_plan_id
370 and per.person_id = p_emp_id
371 and asg.person_id = per.person_id
372 and per.effective_end_date = (select max(effective_end_date)
373 from per_all_people_f
374 where person_id = per.person_id
375 and effective_end_date >= p_date_from)
376 and asg.effective_end_date =(select max(effective_end_date)
377 from per_all_assignments_f
378 where person_id = per.person_id
379 and effective_end_date >= p_date_from)
380 and ent.assignment_id = asg.assignment_id
381 and ent.element_type_id = acc.accrual_plan_element_type_id
382 and ent.effective_start_date = (select max(effective_start_date)
383 from pay_element_entries_f
384 where assignment_id = ent.assignment_id
385 and element_type_id = acc.accrual_plan_element_type_id
386 and effective_start_date <= p_date_to
387 and effective_end_date >= p_date_from);
388 -- Cursor for selecting accrual periods based on
389 -- statement period
390 -- and change in employment category
391 Cursor csr_dif_acc_periods is
392 select asg.effective_start_date start_date,
393 substr(hruserdt.get_table_value(p_business_group_id, 'FR_CIPDZ', 'CIPDZ',nvl(asg.employment_category,'FR'),p_date_from),1,1) asg_catg,
394 asg.payroll_id payroll_id,
395 decode(ctr.ctr_information12, 'HOUR', fnd_number.canonical_to_number(ctr.ctr_information11), asg.normal_hours) wkg_hours
396 from per_all_assignments_f asg,
397 per_contracts_f ctr
398 where asg.person_id= p_emp_id
399 and asg.effective_end_date >= p_date_from
400 and asg.effective_start_date <= p_date_to
401 and asg.contract_id = ctr.contract_id
402 and asg.effective_start_date between ctr.effective_start_date and ctr.effective_end_date
403 order by asg.effective_start_date asc;
404 --
405 -- Cursor for fetching different absences
406 -- corresponding to the accrual plan and period
407 Cursor csr_dif_abs_details is
408 select to_char(abs.date_start, 'dd-Mon-yy') abs_start,
409 to_char(abs.date_end, 'dd-Mon-yy') abs_end,
410 abs.absence_hours abs_duration,
411 hr_general.decode_lookup('FR_TRAINING_LEAVE_CATEGORY',abs.abs_information1) abs_trg_catg,
412 abs.abs_information2 abs_course,
413 po.vendor_name abs_trg_prov,
414 hr_general.decode_lookup('FR_TRAINING_TYPE',abs.abs_information4) abs_trg_type,
415 abs.abs_information17 abs_reference,
416 hr_general.decode_lookup('FR_LEGAL_TRG_CATG',abs.abs_information19) abs_leg_catg,
417 abs.abs_information20 abs_out_wkg_hrs,
418 decode(abs.date_projected_start, null, 'N', 'Y') abs_proj_yn
419 from per_absence_attendances abs,
420 per_absence_attendance_types abt,
421 pay_accrual_plans pap,
422 po_vendors po
423 where abs.absence_attendance_type_id = abt.absence_attendance_type_id
424 and abt.input_value_id = pap.pto_input_value_id
425 and abs.person_id = p_emp_id
426 and abs.abs_information_category = 'FR_TRAINING_ABSENCE'
427 and abs.date_start between p_date_from and p_date_to
428 and pap.accrual_plan_id = p_accrual_plan_id
429 and po.vendor_id = fnd_number.canonical_to_number(abs.abs_information3);
430 --
431 -- Cursor getting adjustment element entries
432 Cursor csr_dif_adj_entries(c_assignment_id number,
433 c_start_date date,
434 c_end_date date) is
435 select ele.element_name adj_element,
436 to_char(pee.effective_start_date, 'dd-Mon-yy') adj_start,
437 to_char(pee.effective_end_date, 'dd-Mon-yy') adj_end,
438 round(fnd_number.canonical_to_number(pev.screen_entry_value)*fnd_number.canonical_to_number(ncr.add_or_subtract), 2) adj_hours,
439 hr_general.decode_lookup('ADD_SUBTRACT',ncr.add_or_subtract) add_or_subtract
440 from pay_accrual_plans pap,
441 pay_net_calculation_rules ncr,
442 pay_element_entries_f pee,
443 pay_element_entry_values_f pev,
444 pay_input_values_f iv,
445 pay_element_types_f ele
446 where pap.accrual_plan_id = p_accrual_plan_id
447 and pee.assignment_id = c_assignment_id
448 and pee.element_entry_id = pev.element_entry_id
449 and pev.input_value_id = ncr.input_value_id
450 and pap.accrual_plan_id = ncr.accrual_plan_id
451 and ncr.input_value_id not in (pap.co_input_value_id,pap.pto_input_value_id)
452 and pev.screen_entry_value is not null
453 and pev.effective_start_date = pee.effective_start_date
454 and pev.effective_end_date = pee.effective_end_date
455 and iv.input_value_id = ncr.input_value_id
456 and c_end_date between iv.effective_start_date and iv.effective_end_date
457 and ele.element_type_id = iv.element_type_id
458 and c_end_date between ele.effective_start_date and ele.effective_end_date
459 and pee.element_type_id = iv.element_type_id
460 and exists
461 (select null
462 from pay_element_entry_values_f pev1,
463 pay_input_values_f piv2
464 where pev1.element_entry_id = pev.element_entry_id
465 and pev1.input_value_id = ncr.date_input_value_id
466 and pev1.effective_start_date = pev.effective_start_date
467 and pev1.effective_end_date = pev.effective_end_date
468 and ncr.date_input_value_id = piv2.input_value_id
469 and pee.element_type_id = piv2.element_type_id
470 and c_end_date between piv2.effective_start_date
471 and piv2.effective_end_date
472 and fnd_date.canonical_to_date(decode(substr(piv2.uom, 1, 1),'D',
473 pev1.screen_entry_value, Null))
474 between c_start_date and c_end_date);
475 --
476 BEGIN
477 --
478 hr_utility.set_location('Entering dif_emp_acc_details', 40);
479 hr_utility.set_location('Table count before header is: '||to_char(xml_table.count), 40);
480 hr_utility.set_location('g_xml_ctr before header is: '||to_char(g_xml_ctr), 40);
481 --
482 ---------------------------------
483 -- HEADER SECTION
484 -----------------------------------
485 -- Get establishment data
486 OPEN csr_hdr_estab_details;
487 FETCH csr_hdr_estab_details INTO
488 -- Write into form fields
489 xml_table( g_xml_ctr +1).tag_name ,
490 xml_table( g_xml_ctr +1).tag_value ,
491 --
492 xml_table(g_xml_ctr +2).tag_name,
493 xml_table(g_xml_ctr +2).tag_value,
494 --
495 xml_table(g_xml_ctr +3).tag_name,
496 xml_table(g_xml_ctr +3).tag_value,
497 --
498 xml_table(g_xml_ctr +4).tag_name,
499 xml_table(g_xml_ctr +4).tag_value,
500 --
501 xml_table(g_xml_ctr +5).tag_name,
502 xml_table(g_xml_ctr +5).tag_value,
503 --
504 xml_table(g_xml_ctr +6).tag_name,
505 xml_table(g_xml_ctr +6).tag_value,
506 --
507 xml_table(g_xml_ctr +7).tag_name,
508 xml_table(g_xml_ctr +7).tag_value,
509 --
510 xml_table(g_xml_ctr +8).tag_name,
511 xml_table(g_xml_ctr +8).tag_value,
512 --
513 xml_table(g_xml_ctr +9).tag_name,
514 xml_table(g_xml_ctr +9).tag_value,
515 --
516 xml_table(g_xml_ctr +10).tag_name,
517 xml_table(g_xml_ctr +10).tag_value,
518 --
519 xml_table(g_xml_ctr +11).tag_name,
520 xml_table(g_xml_ctr +11).tag_value,
521 --
522 xml_table(g_xml_ctr +12).tag_name,
523 xml_table(g_xml_ctr +12).tag_value,
524 --
525 xml_table(g_xml_ctr +13).tag_name,
526 xml_table(g_xml_ctr +13).tag_value,
527 --
528 xml_table(g_xml_ctr +14).tag_name,
529 xml_table(g_xml_ctr +14).tag_value,
530 --
531 xml_table(g_xml_ctr +15).tag_name,
532 xml_table(g_xml_ctr +15).tag_value;
533 --
534 -- increment the counter to table rowcount
535 g_xml_ctr := xml_table.count;
536 CLOSE csr_hdr_estab_details;
537 --
538 hr_utility.set_location('Table count after estab hdr is: '||to_char(xml_table.count), 40);
539 hr_utility.set_location('g_xml_ctr after estab hdr is: '||to_char(g_xml_ctr), 40);
540 -- Get employee data
541 OPEN csr_emp_hdr_details;
542 -- fetch data into form fields
543 -- and corresponding variables
544 FETCH csr_emp_hdr_details INTO
545 --
546 xml_table( g_xml_ctr +1).tag_name ,
547 xml_table( g_xml_ctr +1).tag_value ,
548 --
549 xml_table(g_xml_ctr +2).tag_name,
550 xml_table(g_xml_ctr +2).tag_value,
551 --
552 xml_table(g_xml_ctr +3).tag_name,
553 xml_table(g_xml_ctr +3).tag_value,
554 --
555 xml_table(g_xml_ctr +4).tag_name,
556 xml_table(g_xml_ctr +4).tag_value,
557 --
558 xml_table(g_xml_ctr +5).tag_name,
559 xml_table(g_xml_ctr +5).tag_value,
560 --
561 xml_table(g_xml_ctr +6).tag_name,
562 xml_table(g_xml_ctr +6).tag_value,
563 --
564 xml_table(g_xml_ctr +7).tag_name,
565 xml_table(g_xml_ctr +7).tag_value,
566 --
567 xml_table(g_xml_ctr +8).tag_name,
568 xml_table(g_xml_ctr +8).tag_value,
569 --
570 xml_table(g_xml_ctr +9).tag_name,
571 xml_table(g_xml_ctr +9).tag_value,
572 --
573 xml_table(g_xml_ctr +10).tag_name,
574 xml_table(g_xml_ctr +10).tag_value,
575 --
576 xml_table(g_xml_ctr +11).tag_name,
577 xml_table(g_xml_ctr +11).tag_value,
578 --
579 xml_table(g_xml_ctr +12).tag_name,
580 xml_table(g_xml_ctr +12).tag_value,
581 --
582 xml_table(g_xml_ctr +13).tag_name,
583 xml_table(g_xml_ctr +13).tag_value,
584 --
585 l_assignment_id,
586 l_payroll_id;
587 --
588 -- increment the counter to table rowcount
589 g_xml_ctr := xml_table.count;
590 --
591 CLOSE csr_emp_hdr_details;
592 --
593 hr_utility.set_location('Table count after emp hdr is: '||to_char(xml_table.count), 40);
594 hr_utility.set_location('g_xml_ctr after emp hdr is: '||to_char(g_xml_ctr), 40);
595 -- Get contract details
596 FOR ctr_hdr_rec IN csr_ctr_hdr_details LOOP
597 -- Set the start label
598 g_xml_ctr := g_xml_ctr +1;
599 xml_table( g_xml_ctr).tag_name := 'L_CTR_HDR';
600 xml_table( g_xml_ctr).tag_value := '1';
601 --
602 -- Write into form fields
603 g_xml_ctr := g_xml_ctr +1;
604 xml_table( g_xml_ctr).tag_name := 'ctr_hdr_start';
605 xml_table( g_xml_ctr).tag_value := ctr_hdr_rec.ctr_hdr_start;
606 --
607 g_xml_ctr := g_xml_ctr +1;
608 xml_table( g_xml_ctr).tag_name := 'ctr_hdr_end';
609 xml_table( g_xml_ctr).tag_value := ctr_hdr_rec.ctr_hdr_end;
610 --
611 g_xml_ctr := g_xml_ctr +1;
612 xml_table( g_xml_ctr).tag_name := 'ctr_hdr_type';
613 xml_table( g_xml_ctr).tag_value := ctr_hdr_rec.ctr_hdr_type;
614 --
615 g_xml_ctr := g_xml_ctr +1;
616 xml_table( g_xml_ctr).tag_name := 'ctr_hdr_category';
617 xml_table( g_xml_ctr).tag_value := ctr_hdr_rec.ctr_hdr_category;
618 --
619 g_xml_ctr := g_xml_ctr +1;
620 xml_table( g_xml_ctr).tag_name := 'ctr_hdr_hours';
621 xml_table( g_xml_ctr).tag_value := ctr_hdr_rec.ctr_hdr_hours;
622 --
623 -- Set the end label
624 g_xml_ctr := g_xml_ctr +1;
625 xml_table( g_xml_ctr).tag_name := 'L_CTR_HDR';
626 xml_table( g_xml_ctr).tag_value := '0';
627 --
628 END LOOP;
629 --
630 hr_utility.set_location('Table count after ctr hdr is: '||to_char(xml_table.count), 40);
631 hr_utility.set_location('g_xml_ctr after ctr header is: '||to_char(g_xml_ctr), 40);
632 -- Get accrual details
633 OPEN csr_acc_hdr_details;
634 FETCH csr_acc_hdr_details INTO
635 -- Write into form fields
636 xml_table( g_xml_ctr +1).tag_name ,
637 xml_table( g_xml_ctr +1).tag_value ,
638 --
639 xml_table(g_xml_ctr +2).tag_name,
640 xml_table(g_xml_ctr +2).tag_value,
641 --
642 xml_table(g_xml_ctr +3).tag_name,
643 xml_table(g_xml_ctr +3).tag_value;
644 --
645 -- Increment the counter
646 g_xml_ctr := xml_table.count;
647 --
648 CLOSE csr_acc_hdr_details;
649 --
650 hr_utility.set_location('Table count after accrual hdr is: '||to_char(xml_table.count), 40);
651 hr_utility.set_location('g_xml_ctr after accrual header is: '||to_char(g_xml_ctr), 40);
652 -- Write form fields for statement dates
653 --
654 g_xml_ctr := g_xml_ctr +1;
655 xml_table( g_xml_ctr).tag_name := 'report_date';
656 xml_table( g_xml_ctr).tag_value := to_char(sysdate, 'dd-Mon-yy');
657 --
658 g_xml_ctr := g_xml_ctr +1;
659 xml_table( g_xml_ctr).tag_name := 'statement_start_date';
660 xml_table( g_xml_ctr).tag_value := to_char(p_date_from, 'dd-Mon-yy');
661 --
662 g_xml_ctr := g_xml_ctr +1;
663 xml_table( g_xml_ctr).tag_name :='statement_end_date';
664 xml_table( g_xml_ctr).tag_value := to_char(p_date_to, 'dd-Mon-yy');
665 --
666 hr_utility.set_location('Table count after header is: '||to_char(xml_table.count), 40);
667 hr_utility.set_location('g_xml_ctr after header is: '||to_char(g_xml_ctr), 40);
668 -------- END OF HEADER --------------
669 -------------------------------------
670 -- Body of the report
671 --------------------------------------
672 ------------------------------
673 -- DIF accrual section
674 ------------------------------
675 -- Get the carry over values till the start of statement
676 l_total_carryover := 0;
677 OPEN csr_get_co_start;
678 FETCH csr_get_co_start INTO l_co_start_date;
679 CLOSE csr_get_co_start;
680 -- Get accrual values till statement start date
681 per_accrual_calc_functions.get_net_accrual(
682 p_assignment_id => l_assignment_id,
683 p_plan_id => p_accrual_plan_id,
684 p_payroll_id => l_payroll_id,
685 p_business_group_id => p_business_group_id,
686 p_calculation_date => p_date_from-1,
687 p_accrual_start_date => l_co_start_date,
688 p_start_date => l_dummy_date,
689 p_End_Date => l_dummy_date,
690 p_Accrual_End_Date => l_dummy_date,
691 p_accrual => l_total_accrual,
692 p_net_entitlement => l_total_carryover
693 );
694 --
695 hr_utility.set_location('Calculation date for carry over is: '||to_char(p_date_from-1), 40);
696 hr_utility.set_location('Total carryover till date is: '||to_char(l_total_carryover), 40);
697 -- populate form field values
698 g_xml_ctr := g_xml_ctr +1;
699 xml_table( g_xml_ctr).tag_name := 'co_date_to';
700 xml_table( g_xml_ctr).tag_value := to_char(p_date_from-1, 'dd-Mon-yy');
701 --
702 g_xml_ctr := g_xml_ctr +1;
703 xml_table( g_xml_ctr).tag_name := 'co_hours';
704 xml_table( g_xml_ctr).tag_value := round(l_total_carryover, 2);
705 --
706 -- Get the different accrual periods
707 -- populate the start and end dates in the PL/SQL table
708 l_rowcount:=0;
709 FOR acc_periods_rec IN csr_dif_acc_periods LOOP
710 IF l_rowcount=0 THEN
711 -- for the first record, set the start date
712 l_rowcount := l_rowcount+1;
713 acc_period_dates(l_rowcount).accrual_start_date := p_date_from;
714 acc_period_dates(l_rowcount).payroll_id := acc_periods_rec.payroll_id;
715 acc_period_dates(l_rowcount).wkg_hrs := acc_periods_rec.wkg_hours;
716 l_prev_asg_catg := acc_periods_rec.asg_catg;
717 ELSE
718 IF acc_periods_rec.asg_catg <> l_prev_asg_catg THEN
719 -- set the previous row end date
720 acc_period_dates(l_rowcount).accrual_end_date := acc_periods_rec.start_date-1;
721 l_rowcount := l_rowcount+1;
722 acc_period_dates(l_rowcount).accrual_start_date := acc_periods_rec.start_date;
723 acc_period_dates(l_rowcount).payroll_id := acc_periods_rec.payroll_id;
724 acc_period_dates(l_rowcount).wkg_hrs := acc_periods_rec.wkg_hours;
725 l_prev_asg_catg := acc_periods_rec.asg_catg;
726 END IF;
727 END IF;
728 END LOOP;
729 -- Check if the last end date was populated
730 IF acc_period_dates(l_rowcount).accrual_end_date IS NULL THEN
731 -- set it to statement end date
732 acc_period_dates(l_rowcount).accrual_end_date := p_date_to;
733 --
734 END IF;
735 l_total_dif_accrual:= 0;
736 -- Call the accrual procedure for each period
737 FOR i in 1..l_rowcount LOOP
738 per_accrual_calc_functions.get_net_accrual(
739 p_assignment_id => l_assignment_id,
740 p_plan_id => p_accrual_plan_id,
741 p_payroll_id => acc_period_dates(i).payroll_id,
742 p_business_group_id => p_business_group_id,
743 p_calculation_date => acc_period_dates(i).accrual_end_date,
744 p_accrual_start_date => acc_period_dates(i).accrual_start_date,
745 p_start_date => l_start_date,
746 p_End_Date => l_end_date,
747 p_Accrual_End_Date => l_dummy_date,
748 p_accrual => l_total_accrual,
749 p_net_entitlement => l_period_entitlement);
750 --
751 hr_utility.set_location('Calculation date for this period is: '||to_char(acc_period_dates(i).accrual_end_date), 40);
752 hr_utility.set_location('Start date for this period is: '||to_char(l_start_date),40);
753 hr_utility.set_location('End date for this period is: '||to_char(l_end_date),40);
754 --
755 hr_utility.set_location('Period accrual before adding abs and adj is: '||to_char(l_period_entitlement), 40);
756 -- Calculate the absences for this period
757 l_period_absence := per_accrual_calc_functions.get_absence(
758 p_assignment_id => l_assignment_id,
759 p_plan_id => p_accrual_plan_id,
760 p_start_date => l_start_date,
761 p_calculation_date => l_end_date);
762 -- Calculate the adjustments for this period
763 l_period_adjustments := per_accrual_calc_functions.get_other_net_contribution(
764 p_assignment_id => l_assignment_id,
765 p_plan_id => p_accrual_plan_id,
766 p_start_date => l_start_date,
767 p_calculation_date => l_end_date);
768 -- Add the values to get the gross accrual value
769 l_period_entitlement := l_period_entitlement + l_period_absence + l_period_adjustments;
770 -- Set the start label
771 g_xml_ctr := g_xml_ctr +1;
772 xml_table( g_xml_ctr).tag_name := 'L_DIF_ACC';
773 xml_table( g_xml_ctr).tag_value := '1';
774 --
775 -- Get the field name
776 g_xml_ctr := g_xml_ctr +1;
777 OPEN csr_get_repeat_lookup('ACC_REPEAT');
778 FETCH csr_get_repeat_lookup into xml_table( g_xml_ctr).tag_name, xml_table( g_xml_ctr).tag_value;
779 CLOSE csr_get_repeat_lookup;
780 --
781 -- Write form fields
782 g_xml_ctr := g_xml_ctr +1;
783 xml_table( g_xml_ctr).tag_name := 'acc_date_from';
784 xml_table( g_xml_ctr).tag_value := to_char(acc_period_dates(i).accrual_start_date, 'dd-Mon-yy');
785 --
786 g_xml_ctr := g_xml_ctr +1;
787 xml_table( g_xml_ctr).tag_name := 'acc_date_to';
788 xml_table( g_xml_ctr).tag_value := to_char(acc_period_dates(i).accrual_end_date, 'dd-Mon-yy');
789 --
790 g_xml_ctr := g_xml_ctr +1;
791 xml_table( g_xml_ctr).tag_name := 'acc_wkg_hrs';
792 xml_table( g_xml_ctr).tag_value := acc_period_dates(i).wkg_hrs;
793 --
794 g_xml_ctr := g_xml_ctr +1;
795 xml_table( g_xml_ctr).tag_name := 'acc_hours';
796 xml_table( g_xml_ctr).tag_value := round(l_period_entitlement, 2);
797 --
798 -- Set the end label
799 g_xml_ctr := g_xml_ctr +1;
800 xml_table( g_xml_ctr).tag_name := 'L_DIF_ACC';
801 xml_table( g_xml_ctr).tag_value := '0';
802 --
803 -- Calculate the total accrual for using later
804 l_total_dif_accrual:= l_total_dif_accrual +l_period_entitlement;
805 --
806 hr_utility.set_location('Period accrual is: '||to_char(l_period_entitlement), 40);
807 hr_utility.set_location('Total dif accrual is: '||to_char(l_total_dif_accrual), 40);
808 --
809 END LOOP;
810 -- write the total DIF accural field
811 l_total_dif_accrual := l_total_dif_accrual + l_total_carryover;
812 --
813 g_xml_ctr := g_xml_ctr +1;
814 xml_table( g_xml_ctr).tag_name := 'total_dif_acc';
815 xml_table( g_xml_ctr).tag_value := round(l_total_dif_accrual,2);
816 --
817 hr_utility.set_location('Table count after DIF accruals is: '||to_char(xml_table.count), 40);
818 hr_utility.set_location('g_xml_ctr after DIF accruals is: '||to_char(g_xml_ctr), 40);
819 --
820 ----------------------
821 -- DIF taken section
822 ----------------------
823 l_total_absence_duration := 0;
824 FOR dif_abs_rec IN csr_dif_abs_details LOOP
825 -- Add the total duration
826 -- to be used for balance calculation later on
827 l_total_absence_duration := l_total_absence_duration + dif_abs_rec.abs_duration;
828 -- Set the start label
829 g_xml_ctr := g_xml_ctr +1;
830 xml_table( g_xml_ctr).tag_name := 'L_DIF_TAKEN';
831 xml_table( g_xml_ctr).tag_value := '1';
832 --
833 -- Write form fields
834 g_xml_ctr := g_xml_ctr +1;
835 xml_table( g_xml_ctr).tag_name := 'abs_start_date';
836 xml_table( g_xml_ctr).tag_value := dif_abs_rec.abs_start;
837 --
838 g_xml_ctr := g_xml_ctr +1;
839 xml_table( g_xml_ctr).tag_name := 'abs_end_date';
840 xml_table( g_xml_ctr).tag_value := dif_abs_rec.abs_end;
841 --
842 g_xml_ctr := g_xml_ctr +1;
843 xml_table( g_xml_ctr).tag_name := 'abs_duration';
844 xml_table( g_xml_ctr).tag_value := dif_abs_rec.abs_duration;
845 --
846 g_xml_ctr := g_xml_ctr +1;
847 xml_table( g_xml_ctr).tag_name := 'abs_trg_catg';
848 xml_table( g_xml_ctr).tag_value := dif_abs_rec.abs_trg_catg;
849 --
850 g_xml_ctr := g_xml_ctr +1;
851 xml_table( g_xml_ctr).tag_name := 'abs_course_name';
852 xml_table( g_xml_ctr).tag_value := dif_abs_rec.abs_course;
853 --
854 g_xml_ctr := g_xml_ctr +1;
855 xml_table( g_xml_ctr).tag_name := 'abs_trg_prov';
856 xml_table( g_xml_ctr).tag_value := dif_abs_rec.abs_trg_prov;
857 --
858 g_xml_ctr := g_xml_ctr +1;
859 xml_table( g_xml_ctr).tag_name := 'abs_trg_type';
860 xml_table( g_xml_ctr).tag_value := dif_abs_rec.abs_trg_type;
861 --
862 g_xml_ctr := g_xml_ctr +1;
863 xml_table( g_xml_ctr).tag_name := 'abs_trg_ref';
864 xml_table( g_xml_ctr).tag_value := dif_abs_rec.abs_reference;
865 --
866 g_xml_ctr := g_xml_ctr +1;
867 xml_table( g_xml_ctr).tag_name := 'abs_leg_catg';
868 xml_table( g_xml_ctr).tag_value := dif_abs_rec.abs_leg_catg;
869 --
870 g_xml_ctr := g_xml_ctr +1;
871 xml_table( g_xml_ctr).tag_name := 'abs_out_wkg_hrs';
872 xml_table( g_xml_ctr).tag_value := dif_abs_rec.abs_out_wkg_hrs;
873 --
874 g_xml_ctr := g_xml_ctr +1;
875 xml_table( g_xml_ctr).tag_name := 'abs_proj_yn';
876 xml_table( g_xml_ctr).tag_value := dif_abs_rec.abs_proj_yn;
877 --
878 -- Set the end label
879 g_xml_ctr := g_xml_ctr +1;
880 xml_table( g_xml_ctr).tag_name := 'L_DIF_TAKEN';
881 xml_table( g_xml_ctr).tag_value := '0';
882 --
883 END LOOP;
884 -- Write the total absence form field
885 g_xml_ctr := g_xml_ctr +1;
886 xml_table( g_xml_ctr).tag_name := 'total_dif_abs';
887 xml_table( g_xml_ctr).tag_value := l_total_absence_duration;
888 --
889 hr_utility.set_location('Table count after DIF taken is: '||to_char(xml_table.count), 40);
890 hr_utility.set_location('g_xml_ctr after DIF taken is: '||to_char(g_xml_ctr), 40);
891 -----------------------------
892 -- DIF adjustment section
893 -----------------------------
894 -- Initialize the total adjustments
895 l_total_adjustments := 0;
896 -- Get the adjustment element entries
897 FOR dif_adj_rec IN csr_dif_adj_entries(l_assignment_id, p_date_from, p_date_to) LOOP
898 --
899 -- Add the total values
900 -- for the total adjustments field
901 l_total_adjustments := l_total_adjustments + dif_adj_rec.adj_hours;
902 -- Set the start label
903 g_xml_ctr := g_xml_ctr +1;
904 xml_table( g_xml_ctr).tag_name := 'L_DIF_ADJ';
905 xml_table( g_xml_ctr).tag_value := '1';
906 --
907 -- Get the field name
908 g_xml_ctr := g_xml_ctr +1;
909 OPEN csr_get_repeat_lookup('ADJUST');
910 FETCH csr_get_repeat_lookup into xml_table( g_xml_ctr).tag_name, xml_table( g_xml_ctr).tag_value;
911 CLOSE csr_get_repeat_lookup;
912 --
913 -- Write the form fields
914 g_xml_ctr := g_xml_ctr +1;
915 xml_table( g_xml_ctr).tag_name := 'adj_element';
916 xml_table( g_xml_ctr).tag_value := dif_adj_rec.adj_element;
917 --
918 g_xml_ctr := g_xml_ctr +1;
919 xml_table( g_xml_ctr).tag_name := 'adj_start';
920 xml_table( g_xml_ctr).tag_value := dif_adj_rec.adj_start;
921 --
922 g_xml_ctr := g_xml_ctr +1;
923 xml_table( g_xml_ctr).tag_name := 'adj_end';
924 xml_table( g_xml_ctr).tag_value := dif_adj_rec.adj_end;
925 --
926 g_xml_ctr := g_xml_ctr +1;
927 xml_table( g_xml_ctr).tag_name := 'adj_hours';
928 xml_table( g_xml_ctr).tag_value := dif_adj_rec.adj_hours;
929 --
930 g_xml_ctr := g_xml_ctr +1;
931 xml_table( g_xml_ctr).tag_name := 'adj_add_or_subtract';
932 xml_table( g_xml_ctr).tag_value := dif_adj_rec.add_or_subtract;
933 --
934 -- Set the end label
935 g_xml_ctr := g_xml_ctr +1;
936 xml_table( g_xml_ctr).tag_name := 'L_DIF_ADJ';
937 xml_table( g_xml_ctr).tag_value := '0';
938 --
939 END LOOP;
940 -- Write the total adjustments form field
941 g_xml_ctr := g_xml_ctr +1;
942 xml_table( g_xml_ctr).tag_name := 'total_dif_adj';
943 xml_table( g_xml_ctr).tag_value := l_total_adjustments;
944 --
945 hr_utility.set_location('Table count after DIF adjustments is: '||to_char(xml_table.count), 40);
946 hr_utility.set_location('g_xml_ctr after DIF adjustments is: '||to_char(g_xml_ctr), 40);
947 -------------------------------
948 -- DIF Balance section
949 ------------------------------
950 -- get the balance value
951 l_dif_balance := l_total_dif_accrual - l_total_absence_duration - l_total_adjustments;
952 -- populate the form field
953 g_xml_ctr := g_xml_ctr +1;
954 xml_table( g_xml_ctr).tag_name := 'dif_bal_hrs';
955 xml_table( g_xml_ctr).tag_value := round(l_dif_balance, 2);
956 --
957 hr_utility.set_location('Table count after DIF balance is: '||to_char(xml_table.count), 40);
958 hr_utility.set_location('g_xml_ctr after DIF balance is: '||to_char(g_xml_ctr), 40);
959 hr_utility.set_location('Exiting dif_emp_acc_details' , 50);
960 --
961 END dif_emp_acc_details;
962 --
963 --------------------------------------------------
964 -- procedure for writing to clob
965 ------------------------------------------------------
966 PROCEDURE write_to_clob(p_xfdf_clob out nocopy clob) IS
967
968 l_xfdf_string clob;
969 l_str1 varchar2(1000);
970 l_str2 varchar2(20);
971 l_str3 varchar2(20);
972 l_str4 varchar2(20);
973 l_str5 varchar2(20);
974 l_str6 varchar2(1000);
975 l_str7 varchar2(1000);
976 l_str8 varchar2(1000);
977 --
978 BEGIN
979 --
980 hr_utility.set_location('Entering write_to_clob', 60);
981 --
982 l_str1 := '<?xml version="1.0" encoding="UTF-8"?>
983 <FIELDS> ';
984
985 l_str2 := '<';
986 l_str3 := '>';
987 l_str4 := '</' ;
988 l_str5 := '</FIELDS> ';
989 l_str6 := '<?xml version="1.0" encoding="UTF-8"?>
990 <xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">
991 </xfdf>';
992 dbms_lob.createtemporary(l_xfdf_string, FALSE, dbms_lob.call);
993 dbms_lob.open(l_xfdf_string,dbms_lob.lob_readwrite);
994 --
995 hr_utility.set_location('Table count: '||to_char(xml_table.count), 60);
996 --
997 IF xml_table.count > 0 THEN
998 dbms_lob.writeappend( l_xfdf_string, length(l_str1), l_str1 );
999 FOR ctr_table IN xml_table.FIRST .. xml_table.LAST LOOP
1000 hr_utility.set_location('Counter table: '||to_char(ctr_table), 65);
1001 l_str7 := xml_table(ctr_table).tag_name;
1002 l_str8 := nvl(xml_table(ctr_table).tag_value, ' ');
1003 IF l_str7 in('L_MAIN', 'L_CTR_HDR', 'L_DIF_ACC', 'L_DIF_TAKEN', 'L_DIF_ADJ')THEN
1004 --
1005 hr_utility.set_location('Tag name is: '||l_str7, 65);
1006 hr_utility.set_location('Tag value is: '||l_str8, 65);
1007 --
1008 IF l_str8 = '1' THEN -- start of the label
1009 dbms_lob.writeappend( l_xfdf_string, length(l_str2), l_str2 );--- <
1010 dbms_lob.writeappend( l_xfdf_string, length(l_str7),l_str7);------ name
1011 dbms_lob.writeappend( l_xfdf_string, length(l_str3), l_str3 );---->
1012 --
1013 hr_utility.set_location('xml string', 70);
1014 --
1015 ELSE -- end of the label
1016 dbms_lob.writeappend( l_xfdf_string, length(l_str4), l_str4 );---- </
1017 dbms_lob.writeappend( l_xfdf_string, length(l_str7),l_str7);----- name
1018 dbms_lob.writeappend( l_xfdf_string, length(l_str3), l_str3 );----- >
1019 --
1020 hr_utility.set_location('xml string', 80);
1021 --
1022 END IF;
1023 ELSE
1024 --
1025 hr_utility.set_location('xml string: '||l_str7, 90);
1026 --
1027 dbms_lob.writeappend( l_xfdf_string, length(l_str2), l_str2 );--- <
1028 dbms_lob.writeappend( l_xfdf_string, length(l_str7),l_str7);------ name
1029 dbms_lob.writeappend( l_xfdf_string, length(l_str3), l_str3 );---->
1030 dbms_lob.writeappend( l_xfdf_string, length(l_str8), l_str8);-----value
1031 --
1032 hr_utility.set_location('Appended Value: '||l_str8, 95);
1033 --
1034 dbms_lob.writeappend( l_xfdf_string, length(l_str4), l_str4 );---- </
1035 dbms_lob.writeappend( l_xfdf_string, length(l_str7),l_str7);----- name
1036 dbms_lob.writeappend( l_xfdf_string, length(l_str3), l_str3 );----- >
1037 END IF;
1038 END LOOP;
1039 --
1040 hr_utility.set_location('l_str5 is: '||l_str5, 100);
1041 --
1042 dbms_lob.writeappend( l_xfdf_string, length(l_str5), l_str5 );
1043 --
1044 hr_utility.set_location('xml string', 100);
1045 --
1046 ELSE
1047 dbms_lob.writeappend( l_xfdf_string, length(l_str6), l_str6 );
1048 --
1049 hr_utility.set_location('xml string', 110);
1050 --
1051 END IF;
1052 hr_utility.set_location(dbms_lob.getlength(l_xfdf_string), 120);
1053 --
1054 dbms_lob.createtemporary(p_xfdf_clob,TRUE);
1055 p_xfdf_clob := l_xfdf_string;
1056 --
1057 hr_utility.set_location('After assigning to out LOB'||dbms_lob.getlength(p_xfdf_clob), 130);
1058 --
1059 hr_utility.set_location('After writing to clob', 140);
1060 --
1061 EXCEPTION
1062 WHEN OTHERS THEN
1063 hr_utility.set_location('Exception: '||to_char(SQLCODE)||' '||SUBSTR(SQLERRM, 1, 50), 60);
1064 return;
1065 --
1066 hr_utility.set_location('Exiting write_to_clob', 60);
1067 --
1068 END write_to_clob;
1069 --
1070 END;