[Home] [Help]
14: begin
15: l_length_clob := dbms_lob.getlength(p_clob);
16: l_offset := 1;
17: while l_length_clob > 0 loop
18: hr_utility.trace('l_length_clob '|| l_length_clob);
19: if l_length_clob < l_buffer_len then
20: l_chunk_len := l_length_clob;
21: else
22: l_chunk_len := l_buffer_len;
25: l_raw_buffer := utl_raw.cast_to_raw(l_varchar_buffer);
26: dbms_lob.write(p_blob,l_chunk_len, l_offset, l_raw_buffer);
27: l_offset := l_offset + l_chunk_len;
28: l_length_clob := l_length_clob - l_chunk_len;
29: hr_utility.trace('l_length_blob '|| dbms_lob.getlength(p_blob));
30: end loop;
31: end;
32:
33: procedure delete_entries(errbuf out nocopy VARCHAR2,
139: fetch csr_get_element_name into l_element_name;
140: if csr_get_element_name%notfound
141: then
142: close csr_get_element_name;
143: hr_utility.TRACE('Incorrect element type id '|| p_element_type_id);
144: raise e_p11d_element_err;
145: end if;
146: close csr_get_element_name;
147: hr_utility.TRACE('Value of element name='|| l_element_name);
143: hr_utility.TRACE('Incorrect element type id '|| p_element_type_id);
144: raise e_p11d_element_err;
145: end if;
146: close csr_get_element_name;
147: hr_utility.TRACE('Value of element name='|| l_element_name);
148: -- get the benefit start date id
149: open csr_get_benefit_date_id(c_ben_start_date_string, p_element_type_id);
150: fetch csr_get_benefit_date_id into l_ben_start_date_id;
151: if csr_get_benefit_date_id%notfound
150: fetch csr_get_benefit_date_id into l_ben_start_date_id;
151: if csr_get_benefit_date_id%notfound
152: then
153: close csr_get_benefit_date_id;
154: hr_utility.TRACE('No Benefit Start date defined for element ');
155: raise e_p11d_ben_st_dt_err;
156: end if;
157: close csr_get_benefit_date_id;
158: hr_utility.TRACE('Value of benefit start date id = '|| to_char(l_ben_start_date_id) );
154: hr_utility.TRACE('No Benefit Start date defined for element ');
155: raise e_p11d_ben_st_dt_err;
156: end if;
157: close csr_get_benefit_date_id;
158: hr_utility.TRACE('Value of benefit start date id = '|| to_char(l_ben_start_date_id) );
159: -- get the benefit END date id;
160: open csr_get_benefit_date_id(c_ben_end_date_string, p_element_type_id);
161: fetch csr_get_benefit_date_id into l_ben_end_date_id;
162: if csr_get_benefit_date_id%notfound
161: fetch csr_get_benefit_date_id into l_ben_end_date_id;
162: if csr_get_benefit_date_id%notfound
163: then
164: close csr_get_benefit_date_id;
165: hr_utility.TRACE('No Benefit End date defined for element type id ');
166: raise e_p11d_ben_end_dt_err;
167: end if;
168: close csr_get_benefit_date_id;
169: hr_utility.TRACE('Value of benefit end date id ='|| to_char(l_ben_end_date_id) );
165: hr_utility.TRACE('No Benefit End date defined for element type id ');
166: raise e_p11d_ben_end_dt_err;
167: end if;
168: close csr_get_benefit_date_id;
169: hr_utility.TRACE('Value of benefit end date id ='|| to_char(l_ben_end_date_id) );
170: -- get the elementry id WHERE the start date is greater than the p_start_date
171: -- AND END date is less than the p_end_date. Only those element entry id's will
172: -- be returned WHERE the business group id is p_bus_grp_id
173: hr_utility.TRACE('p_start_date '|| p_start_date);
169: hr_utility.TRACE('Value of benefit end date id ='|| to_char(l_ben_end_date_id) );
170: -- get the elementry id WHERE the start date is greater than the p_start_date
171: -- AND END date is less than the p_end_date. Only those element entry id's will
172: -- be returned WHERE the business group id is p_bus_grp_id
173: hr_utility.TRACE('p_start_date '|| p_start_date);
174: hr_utility.TRACE('p_end_date '|| p_end_date);
175: hr_utility.TRACE('p_bus_grp_id '|| p_bus_grp_id);
176: hr_utility.TRACE('p_element_type_id '|| p_element_type_id);
177: if p_assignment_set_id is null then
170: -- get the elementry id WHERE the start date is greater than the p_start_date
171: -- AND END date is less than the p_end_date. Only those element entry id's will
172: -- be returned WHERE the business group id is p_bus_grp_id
173: hr_utility.TRACE('p_start_date '|| p_start_date);
174: hr_utility.TRACE('p_end_date '|| p_end_date);
175: hr_utility.TRACE('p_bus_grp_id '|| p_bus_grp_id);
176: hr_utility.TRACE('p_element_type_id '|| p_element_type_id);
177: if p_assignment_set_id is null then
178: for del_element_entry in csr_get_del_element_entry_id(
171: -- AND END date is less than the p_end_date. Only those element entry id's will
172: -- be returned WHERE the business group id is p_bus_grp_id
173: hr_utility.TRACE('p_start_date '|| p_start_date);
174: hr_utility.TRACE('p_end_date '|| p_end_date);
175: hr_utility.TRACE('p_bus_grp_id '|| p_bus_grp_id);
176: hr_utility.TRACE('p_element_type_id '|| p_element_type_id);
177: if p_assignment_set_id is null then
178: for del_element_entry in csr_get_del_element_entry_id(
179: l_ben_start_date_id,
172: -- be returned WHERE the business group id is p_bus_grp_id
173: hr_utility.TRACE('p_start_date '|| p_start_date);
174: hr_utility.TRACE('p_end_date '|| p_end_date);
175: hr_utility.TRACE('p_bus_grp_id '|| p_bus_grp_id);
176: hr_utility.TRACE('p_element_type_id '|| p_element_type_id);
177: if p_assignment_set_id is null then
178: for del_element_entry in csr_get_del_element_entry_id(
179: l_ben_start_date_id,
180: p_start_date,
183: p_bus_grp_id,
184: p_element_type_id)
185: loop
186: l_count := l_count + 1;
187: hr_utility.TRACE('Delete entries for element entry id '|| to_char(del_element_entry.element_entry_id) );
188: pay_element_entry_api.delete_element_entry(p_datetrack_delete_mode => 'ZAP',
189: p_effective_date => del_element_entry.effective_start_date,
190: p_element_entry_id => del_element_entry.element_entry_id,
191: p_object_version_number => del_element_entry.object_version_number,
194: p_delete_warning => l_del_warning_dummy);
195: if mod(l_count, c_commit_num) = 0
196: then
197: commit;
198: hr_utility.TRACE('Commiting delete, counter = '|| to_char(l_count) );
199: end if;
200: end loop;
201: else
202: for del_element_entry in csr_del_entries_assignset(
208: p_element_type_id,
209: p_assignment_set_id)
210: loop
211: l_count := l_count + 1;
212: hr_utility.TRACE('Delete entries for element entry id '|| to_char(del_element_entry.element_entry_id) );
213: pay_element_entry_api.delete_element_entry(p_datetrack_delete_mode => 'ZAP',
214: p_effective_date => del_element_entry.effective_start_date,
215: p_element_entry_id => del_element_entry.element_entry_id,
216: p_object_version_number => del_element_entry.object_version_number,
219: p_delete_warning => l_del_warning_dummy);
220: if mod(l_count, c_commit_num) = 0
221: then
222: commit;
223: hr_utility.TRACE('Commiting delete, counter = '|| to_char(l_count) );
224: end if;
225: end loop;
226: end if;
227: commit;
227: commit;
228: exception
229: when e_p11d_element_err
230: then
231: hr_utility.set_message(800, 'HR_78046_GB_P11D_ELEMENT_ERR');
232: hr_utility.set_message_token(800, 'ELEMENT', l_element_name);
233: errbuf := hr_utility.get_message;
234: hr_utility.raise_error;
235: retcode := 1;
228: exception
229: when e_p11d_element_err
230: then
231: hr_utility.set_message(800, 'HR_78046_GB_P11D_ELEMENT_ERR');
232: hr_utility.set_message_token(800, 'ELEMENT', l_element_name);
233: errbuf := hr_utility.get_message;
234: hr_utility.raise_error;
235: retcode := 1;
236: rollback;
229: when e_p11d_element_err
230: then
231: hr_utility.set_message(800, 'HR_78046_GB_P11D_ELEMENT_ERR');
232: hr_utility.set_message_token(800, 'ELEMENT', l_element_name);
233: errbuf := hr_utility.get_message;
234: hr_utility.raise_error;
235: retcode := 1;
236: rollback;
237: when e_p11d_ben_st_dt_err
230: then
231: hr_utility.set_message(800, 'HR_78046_GB_P11D_ELEMENT_ERR');
232: hr_utility.set_message_token(800, 'ELEMENT', l_element_name);
233: errbuf := hr_utility.get_message;
234: hr_utility.raise_error;
235: retcode := 1;
236: rollback;
237: when e_p11d_ben_st_dt_err
238: then
235: retcode := 1;
236: rollback;
237: when e_p11d_ben_st_dt_err
238: then
239: hr_utility.set_message(800, 'HR_78047_GB_P11D_BEN_ST_DT_ERR');
240: hr_utility.set_message_token(800, 'ELEMENT', l_element_name);
241: errbuf := hr_utility.get_message;
242: hr_utility.raise_error;
243: retcode := 1;
236: rollback;
237: when e_p11d_ben_st_dt_err
238: then
239: hr_utility.set_message(800, 'HR_78047_GB_P11D_BEN_ST_DT_ERR');
240: hr_utility.set_message_token(800, 'ELEMENT', l_element_name);
241: errbuf := hr_utility.get_message;
242: hr_utility.raise_error;
243: retcode := 1;
244: rollback;
237: when e_p11d_ben_st_dt_err
238: then
239: hr_utility.set_message(800, 'HR_78047_GB_P11D_BEN_ST_DT_ERR');
240: hr_utility.set_message_token(800, 'ELEMENT', l_element_name);
241: errbuf := hr_utility.get_message;
242: hr_utility.raise_error;
243: retcode := 1;
244: rollback;
245: when e_p11d_ben_end_dt_err
238: then
239: hr_utility.set_message(800, 'HR_78047_GB_P11D_BEN_ST_DT_ERR');
240: hr_utility.set_message_token(800, 'ELEMENT', l_element_name);
241: errbuf := hr_utility.get_message;
242: hr_utility.raise_error;
243: retcode := 1;
244: rollback;
245: when e_p11d_ben_end_dt_err
246: then
243: retcode := 1;
244: rollback;
245: when e_p11d_ben_end_dt_err
246: then
247: hr_utility.set_message(800, 'HR_78048_GB_P11D_BEN_ED_DT_ERR');
248: hr_utility.set_message_token(800, 'ELEMENT', l_element_name);
249: errbuf := hr_utility.get_message;
250: hr_utility.raise_error;
251: retcode := 1;
244: rollback;
245: when e_p11d_ben_end_dt_err
246: then
247: hr_utility.set_message(800, 'HR_78048_GB_P11D_BEN_ED_DT_ERR');
248: hr_utility.set_message_token(800, 'ELEMENT', l_element_name);
249: errbuf := hr_utility.get_message;
250: hr_utility.raise_error;
251: retcode := 1;
252: rollback;
245: when e_p11d_ben_end_dt_err
246: then
247: hr_utility.set_message(800, 'HR_78048_GB_P11D_BEN_ED_DT_ERR');
248: hr_utility.set_message_token(800, 'ELEMENT', l_element_name);
249: errbuf := hr_utility.get_message;
250: hr_utility.raise_error;
251: retcode := 1;
252: rollback;
253: when OTHERS
246: then
247: hr_utility.set_message(800, 'HR_78048_GB_P11D_BEN_ED_DT_ERR');
248: hr_utility.set_message_token(800, 'ELEMENT', l_element_name);
249: errbuf := hr_utility.get_message;
250: hr_utility.raise_error;
251: retcode := 1;
252: rollback;
253: when OTHERS
254: then
263: if csr_get_del_element_entry_id%isopen
264: then
265: close csr_get_del_element_entry_id;
266: end if;
267: hr_utility.set_message(800, 'HR_78045_GB_P11D_DEL_ERR');
268: hr_utility.set_message_token(800, 'ERRORMSG', sqlerrm);
269: errbuf := hr_utility.get_message;
270: hr_utility.raise_error;
271: retcode := 1;
264: then
265: close csr_get_del_element_entry_id;
266: end if;
267: hr_utility.set_message(800, 'HR_78045_GB_P11D_DEL_ERR');
268: hr_utility.set_message_token(800, 'ERRORMSG', sqlerrm);
269: errbuf := hr_utility.get_message;
270: hr_utility.raise_error;
271: retcode := 1;
272: rollback;
265: close csr_get_del_element_entry_id;
266: end if;
267: hr_utility.set_message(800, 'HR_78045_GB_P11D_DEL_ERR');
268: hr_utility.set_message_token(800, 'ERRORMSG', sqlerrm);
269: errbuf := hr_utility.get_message;
270: hr_utility.raise_error;
271: retcode := 1;
272: rollback;
273: end delete_entries;
266: end if;
267: hr_utility.set_message(800, 'HR_78045_GB_P11D_DEL_ERR');
268: hr_utility.set_message_token(800, 'ERRORMSG', sqlerrm);
269: errbuf := hr_utility.get_message;
270: hr_utility.raise_error;
271: retcode := 1;
272: rollback;
273: end delete_entries;
274:
277: p_token3 in VARCHAR2)return VARCHAR2
278: is
279: l_errmsg VARCHAR2(1000);
280: begin
281: hr_utility.set_message(800, 'HR_78052_GB_P11D_VALUES_NULL');
282: hr_utility.set_message_token(800, '1INPUT_VALUE', p_token1);
283: hr_utility.set_message_token(800, '2INPUT_VALUE', p_token2);
284: -- hr_utility.set_message_token (800, '3INPUT_VALUE', p_token3);
285: l_errmsg := hr_utility.get_message;
278: is
279: l_errmsg VARCHAR2(1000);
280: begin
281: hr_utility.set_message(800, 'HR_78052_GB_P11D_VALUES_NULL');
282: hr_utility.set_message_token(800, '1INPUT_VALUE', p_token1);
283: hr_utility.set_message_token(800, '2INPUT_VALUE', p_token2);
284: -- hr_utility.set_message_token (800, '3INPUT_VALUE', p_token3);
285: l_errmsg := hr_utility.get_message;
286: return l_errmsg;
279: l_errmsg VARCHAR2(1000);
280: begin
281: hr_utility.set_message(800, 'HR_78052_GB_P11D_VALUES_NULL');
282: hr_utility.set_message_token(800, '1INPUT_VALUE', p_token1);
283: hr_utility.set_message_token(800, '2INPUT_VALUE', p_token2);
284: -- hr_utility.set_message_token (800, '3INPUT_VALUE', p_token3);
285: l_errmsg := hr_utility.get_message;
286: return l_errmsg;
287: end get_null_error;
280: begin
281: hr_utility.set_message(800, 'HR_78052_GB_P11D_VALUES_NULL');
282: hr_utility.set_message_token(800, '1INPUT_VALUE', p_token1);
283: hr_utility.set_message_token(800, '2INPUT_VALUE', p_token2);
284: -- hr_utility.set_message_token (800, '3INPUT_VALUE', p_token3);
285: l_errmsg := hr_utility.get_message;
286: return l_errmsg;
287: end get_null_error;
288:
281: hr_utility.set_message(800, 'HR_78052_GB_P11D_VALUES_NULL');
282: hr_utility.set_message_token(800, '1INPUT_VALUE', p_token1);
283: hr_utility.set_message_token(800, '2INPUT_VALUE', p_token2);
284: -- hr_utility.set_message_token (800, '3INPUT_VALUE', p_token3);
285: l_errmsg := hr_utility.get_message;
286: return l_errmsg;
287: end get_null_error;
288:
289: function get_incorrect_val_error(p_token1 in VARCHAR2,
291: p_token3 in VARCHAR2) return VARCHAR2
292: is
293: l_errmsg VARCHAR2(1000);
294: begin
295: hr_utility.set_message(800, 'HR_78051_GB_P11D_INCORRECT_VAL');
296: hr_utility.set_message_token(800, '1INPUT_VALUE', p_token1);
297: hr_utility.set_message_token(800, '2INPUT_VALUE', p_token2);
298: hr_utility.set_message_token(800, '3INPUT_VALUE', p_token3);
299: l_errmsg := hr_utility.get_message;
292: is
293: l_errmsg VARCHAR2(1000);
294: begin
295: hr_utility.set_message(800, 'HR_78051_GB_P11D_INCORRECT_VAL');
296: hr_utility.set_message_token(800, '1INPUT_VALUE', p_token1);
297: hr_utility.set_message_token(800, '2INPUT_VALUE', p_token2);
298: hr_utility.set_message_token(800, '3INPUT_VALUE', p_token3);
299: l_errmsg := hr_utility.get_message;
300: return l_errmsg;
293: l_errmsg VARCHAR2(1000);
294: begin
295: hr_utility.set_message(800, 'HR_78051_GB_P11D_INCORRECT_VAL');
296: hr_utility.set_message_token(800, '1INPUT_VALUE', p_token1);
297: hr_utility.set_message_token(800, '2INPUT_VALUE', p_token2);
298: hr_utility.set_message_token(800, '3INPUT_VALUE', p_token3);
299: l_errmsg := hr_utility.get_message;
300: return l_errmsg;
301: end get_incorrect_val_error;
294: begin
295: hr_utility.set_message(800, 'HR_78051_GB_P11D_INCORRECT_VAL');
296: hr_utility.set_message_token(800, '1INPUT_VALUE', p_token1);
297: hr_utility.set_message_token(800, '2INPUT_VALUE', p_token2);
298: hr_utility.set_message_token(800, '3INPUT_VALUE', p_token3);
299: l_errmsg := hr_utility.get_message;
300: return l_errmsg;
301: end get_incorrect_val_error;
302:
295: hr_utility.set_message(800, 'HR_78051_GB_P11D_INCORRECT_VAL');
296: hr_utility.set_message_token(800, '1INPUT_VALUE', p_token1);
297: hr_utility.set_message_token(800, '2INPUT_VALUE', p_token2);
298: hr_utility.set_message_token(800, '3INPUT_VALUE', p_token3);
299: l_errmsg := hr_utility.get_message;
300: return l_errmsg;
301: end get_incorrect_val_error;
302:
303: function get_error_message(p_applid in NUMBER,
304: p_message_name in VARCHAR2) return VARCHAR2
305: is
306: l_errmsg VARCHAR2(1000);
307: begin
308: hr_utility.set_message(p_applid, p_message_name);
309: l_errmsg := hr_utility.get_message;
310: return l_errmsg;
311: end get_error_message;
312:
305: is
306: l_errmsg VARCHAR2(1000);
307: begin
308: hr_utility.set_message(p_applid, p_message_name);
309: l_errmsg := hr_utility.get_message;
310: return l_errmsg;
311: end get_error_message;
312:
313: function get_loan_amount(p_archive_payroll_action_id in VARCHAR2,
354: and GLOBAL_NAME = p_global_name
355: and fnd_date.canonical_to_date(p_benefit_end_date)
356: between effective_start_date and effective_end_date;
357: begin
358: hr_utility.TRACE('p_benefit_end_date '|| p_benefit_end_date);
359: hr_utility.TRACE('p_global_name '|| p_global_name);
360: open csr_get_value;
361: fetch csr_get_value into l_global_value;
362: close csr_get_value;
355: and fnd_date.canonical_to_date(p_benefit_end_date)
356: between effective_start_date and effective_end_date;
357: begin
358: hr_utility.TRACE('p_benefit_end_date '|| p_benefit_end_date);
359: hr_utility.TRACE('p_global_name '|| p_global_name);
360: open csr_get_value;
361: fetch csr_get_value into l_global_value;
362: close csr_get_value;
363: return l_global_value;
369: l_present_val NUMBER;
370: l_new_val NUMBER;
371: l_dummy NUMBER;
372: begin
373: hr_utility.TRACE(' p_variable_value '|| p_variable_value);
374: l_present_val := per_formula_functions.get_number(p_varable_name);
375: hr_utility.TRACE(' l_present_val '|| l_present_val);
376: if l_present_val is null
377: then
371: l_dummy NUMBER;
372: begin
373: hr_utility.TRACE(' p_variable_value '|| p_variable_value);
374: l_present_val := per_formula_functions.get_number(p_varable_name);
375: hr_utility.TRACE(' l_present_val '|| l_present_val);
376: if l_present_val is null
377: then
378: if p_variable_value is null
379: then
380: l_new_val := null;
381: else
382: l_new_val := nvl(to_number(p_variable_value), 0);
383: end if;
384: hr_utility.TRACE('l_new_val '|| l_new_val);
385: else
386: --Added the below logic for the bug fix 8864717.
387: --
388: if ((l_present_val = p_variable_value) and (PAY_GB_P11D_ARCHIVE_SS.g_updated_flag = 'Y'))
391: else
392: l_new_val := l_present_val + nvl(to_number(p_variable_value), 0);
393: end if;
394: --
395: hr_utility.TRACE(' l_new_val '|| l_new_val);
396: end if;
397: l_dummy := per_formula_functions.set_number(p_varable_name, l_new_val);
398: hr_utility.TRACE(' l_dummy '|| l_dummy);
399: return l_new_val;
394: --
395: hr_utility.TRACE(' l_new_val '|| l_new_val);
396: end if;
397: l_dummy := per_formula_functions.set_number(p_varable_name, l_new_val);
398: hr_utility.TRACE(' l_dummy '|| l_dummy);
399: return l_new_val;
400: end;
401:
402: function max_and_set_global_var(p_variable_name in Varchar2,
427: -- this is for date, we need to store the max date
428: -- this is used for date free fuel withdrawn
429: -- if p_variable_value is null or zero this won't be called
430: l_present_val := per_formula_functions.get_text(p_variable_name);
431: hr_utility.TRACE(' l_present_val '|| l_present_val);
432: if l_present_val is null
433: then
434: l_dummy := per_formula_functions.set_text(p_variable_name, p_variable_value);
435: else
479: l_present_val VARCHAR2(150);
480: l_new_val VARCHAR2(150);
481: l_dummy NUMBER;
482: begin
483: hr_utility.TRACE('.. p_varable_name ...'|| p_varable_name);
484: hr_utility.TRACE('.. p_variable_value ...'|| p_variable_value);
485: l_present_val := per_formula_functions.get_text(p_varable_name);
486: hr_utility.TRACE('..l_present_val.. '|| l_present_val);
487: if p_lookup_type is null
480: l_new_val VARCHAR2(150);
481: l_dummy NUMBER;
482: begin
483: hr_utility.TRACE('.. p_varable_name ...'|| p_varable_name);
484: hr_utility.TRACE('.. p_variable_value ...'|| p_variable_value);
485: l_present_val := per_formula_functions.get_text(p_varable_name);
486: hr_utility.TRACE('..l_present_val.. '|| l_present_val);
487: if p_lookup_type is null
488: then
482: begin
483: hr_utility.TRACE('.. p_varable_name ...'|| p_varable_name);
484: hr_utility.TRACE('.. p_variable_value ...'|| p_variable_value);
485: l_present_val := per_formula_functions.get_text(p_varable_name);
486: hr_utility.TRACE('..l_present_val.. '|| l_present_val);
487: if p_lookup_type is null
488: then
489: if l_present_val is null
490: then
490: then
491: l_new_val := p_variable_value;
492: l_dummy := per_formula_functions.set_text(p_varable_name, l_new_val);
493: else
494: hr_utility.TRACE('..Checking present Value with variable value '|| l_present_val);
495: if l_present_val <> nvl(p_variable_value, l_present_val)
496: then
497: l_new_val := 'Multiple';
498: l_dummy := per_formula_functions.set_text(p_varable_name, l_new_val);
503: p_variable_value,
504: p_effective_date || ' 00:00:00'); -- since it accepts date in canonical format
505: if l_present_val is null
506: then
507: hr_utility.trace('New vale ' || l_new_val);
508: l_dummy := per_formula_functions.set_text(p_varable_name, l_new_val);
509: else
510: hr_utility.TRACE('..Checking present Value with variable value '|| l_present_val);
511: if l_present_val <> nvl(l_new_val, l_present_val)
506: then
507: hr_utility.trace('New vale ' || l_new_val);
508: l_dummy := per_formula_functions.set_text(p_varable_name, l_new_val);
509: else
510: hr_utility.TRACE('..Checking present Value with variable value '|| l_present_val);
511: if l_present_val <> nvl(l_new_val, l_present_val)
512: then
513: l_new_val := 'Multiple';
514: l_dummy := per_formula_functions.set_text(p_varable_name, l_new_val);
692: and pai_comp.action_information_category = 'EMEA PAYROLL INFO';
693:
694: l_p11d_fields.employers_ref_no := g_emp_ref_no;
695: l_p11d_fields.employers_name := replace(g_employer_name,'&','&');
696: hr_utility.trace('l_p11d_fields.employers_name' || l_p11d_fields.employers_name);
697:
698: select decode(action_information4, 'Y', 'Y', 'N'),
699: action_information8, -- P11D changes 07/08 last_name
700: action_information6, -- P11D changes 07/08 first_name
827: where pai_emp.action_context_id = p_assignment_action_id
828: and pai_emp.action_context_type = 'AAP'
829: and pai_emp.action_information_category = 'GB P11D ASSIGNMENT RESULTB';
830:
831: hr_utility.trace('Fetching from Result C');
832: select substr(action_information10,9,2) || ' ' ||
833: substr(action_information10,6,2) || ' ' ||
834: substr(action_information10,1,4) ,
835: decode (action_information11,null,'N','Y'),to_number(nvl(ACTION_INFORMATION23,'0'))
931: where pai_emp.action_context_id = p_assignment_action_id
932: and pai_emp.action_context_type = 'AAP'
933: and pai_emp.action_information_category = 'GB P11D ASSIGNMENT RESULTB';
934:
935: hr_utility.trace('Fetching from Result C');
936: select to_number(nvl(ACTION_INFORMATION23,'0'))
937: into l_h_sum_max_amt_outstanding
938: from pay_action_information pai_emp
939: where pai_emp.action_context_id = p_assignment_action_id
1011: l_ben_end varchar2(10);
1012: l_tax_year varchar2(4);
1013: l_date_reg varchar2(10);
1014: begin
1015: hr_utility.trace('l_f_count ' || l_f_count);
1016: if l_f_count > 0
1017: then
1018: hr_utility.trace('l_payroll_action_id ' || g_payroll_action_id);
1019: hr_utility.trace('l_person_id ' || g_person_id);
1014: begin
1015: hr_utility.trace('l_f_count ' || l_f_count);
1016: if l_f_count > 0
1017: then
1018: hr_utility.trace('l_payroll_action_id ' || g_payroll_action_id);
1019: hr_utility.trace('l_person_id ' || g_person_id);
1020: l_tax_year := pay_gb_p11d_magtape.get_parameters(g_payroll_action_id,'Rep_Run');
1021: l_tax_year_end := l_tax_year || '0405';
1022: l_tax_year_start := to_char(to_number(l_tax_year) - 1) || '0406';
1015: hr_utility.trace('l_f_count ' || l_f_count);
1016: if l_f_count > 0
1017: then
1018: hr_utility.trace('l_payroll_action_id ' || g_payroll_action_id);
1019: hr_utility.trace('l_person_id ' || g_person_id);
1020: l_tax_year := pay_gb_p11d_magtape.get_parameters(g_payroll_action_id,'Rep_Run');
1021: l_tax_year_end := l_tax_year || '0405';
1022: l_tax_year_start := to_char(to_number(l_tax_year) - 1) || '0406';
1023: for car_benefits in csr_car_benefits(g_payroll_action_id,
1025: g_emp_ref_no,
1026: g_employer_name)
1027: loop
1028: l_rec_count := l_rec_count + 1;
1029: hr_utility.trace('l_rec_count ' || l_rec_count);
1030: l_ben_start := car_benefits.f_start;
1031: l_ben_end := car_benefits.f_end;
1032: l_ben_start := substr(l_ben_start,7,4) || substr(l_ben_start,4,2) || substr(l_ben_start,1,2);
1033: l_ben_end := substr(l_ben_end,7,4) || substr(l_ben_end,4,2) || substr(l_ben_end,1,2);
1033: l_ben_end := substr(l_ben_end,7,4) || substr(l_ben_end,4,2) || substr(l_ben_end,1,2);
1034: l_date_reg := substr(car_benefits.f_dreg,7,4) || substr(car_benefits.f_dreg,4,2) || substr(car_benefits.f_dreg,1,2);
1035: if l_rec_count = 1
1036: then
1037: hr_utility.trace('car_benefits.f_make ' || car_benefits.f_make);
1038: hr_utility.trace('l_rec_count in 1 ' || l_rec_count);
1039: if to_number(l_ben_start) > to_number(l_tax_year_start)
1040: then
1041: l_p11d_fields.f1_start := car_benefits.f_start;
1034: l_date_reg := substr(car_benefits.f_dreg,7,4) || substr(car_benefits.f_dreg,4,2) || substr(car_benefits.f_dreg,1,2);
1035: if l_rec_count = 1
1036: then
1037: hr_utility.trace('car_benefits.f_make ' || car_benefits.f_make);
1038: hr_utility.trace('l_rec_count in 1 ' || l_rec_count);
1039: if to_number(l_ben_start) > to_number(l_tax_year_start)
1040: then
1041: l_p11d_fields.f1_start := car_benefits.f_start;
1042: else
1076: l_p11d_fields.f1_date_free := car_benefits.f_date_free;
1077: l_p11d_fields.f1_rein_yr := car_benefits.f_rein_yr;
1078: elsif l_rec_count = 2
1079: then
1080: hr_utility.trace('car_benefits.f_make ' || car_benefits.f_make);
1081: hr_utility.trace('l_rec_count in 2' || l_rec_count);
1082: if to_number(l_ben_start) > to_number(l_tax_year_start)
1083: then
1084: l_p11d_fields.f2_start := car_benefits.f_start;
1077: l_p11d_fields.f1_rein_yr := car_benefits.f_rein_yr;
1078: elsif l_rec_count = 2
1079: then
1080: hr_utility.trace('car_benefits.f_make ' || car_benefits.f_make);
1081: hr_utility.trace('l_rec_count in 2' || l_rec_count);
1082: if to_number(l_ben_start) > to_number(l_tax_year_start)
1083: then
1084: l_p11d_fields.f2_start := car_benefits.f_start;
1085: else
1173: pai_emp.action_information1, -- ele entrty id
1174: pai_emp.action_information2; -- effec date
1175: l_rec_count INTEGER := 0;
1176: begin
1177: hr_utility.trace('l_h_count ' || l_h_count);
1178: hr_utility.trace('max outstd ' || l_h_sum_max_amt_outstanding);
1179: if l_h_count > 2 and
1180: -- Added this extra and condition for 3558538
1181: -- this will ensure that only int free lons where CE is greater than 0
1174: pai_emp.action_information2; -- effec date
1175: l_rec_count INTEGER := 0;
1176: begin
1177: hr_utility.trace('l_h_count ' || l_h_count);
1178: hr_utility.trace('max outstd ' || l_h_sum_max_amt_outstanding);
1179: if l_h_count > 2 and
1180: -- Added this extra and condition for 3558538
1181: -- this will ensure that only int free lons where CE is greater than 0
1182: -- are reported
1183: l_h_sum_max_amt_outstanding > 5000
1184: then
1185: l_p11d_fields.h2_ce := 'See Attached';
1186: l_p11d_fields.h1_ce := l_h_ce;
1187: hr_utility.trace('See att ' );
1188: elsif l_h_count > 0 and
1189: -- Added this extra and condition for 3558538
1190: -- this will ensure that only int free lons where CE is greater than 0
1191: -- are reported
1190: -- this will ensure that only int free lons where CE is greater than 0
1191: -- are reported
1192: l_h_sum_max_amt_outstanding > 5000
1193: then
1194: hr_utility.trace('l_payroll_action_id ' || g_payroll_action_id);
1195: hr_utility.trace('l_person_id ' || g_person_id);
1196: for int_benefits in csr_int_benefits(g_payroll_action_id,
1197: g_person_id,
1198: g_emp_ref_no,
1191: -- are reported
1192: l_h_sum_max_amt_outstanding > 5000
1193: then
1194: hr_utility.trace('l_payroll_action_id ' || g_payroll_action_id);
1195: hr_utility.trace('l_person_id ' || g_person_id);
1196: for int_benefits in csr_int_benefits(g_payroll_action_id,
1197: g_person_id,
1198: g_emp_ref_no,
1199: g_employer_name)
1198: g_emp_ref_no,
1199: g_employer_name)
1200: loop
1201: l_rec_count := l_rec_count + 1;
1202: hr_utility.trace('l_rec_count ' || l_rec_count);
1203: if l_rec_count = 1
1204: then
1205: hr_utility.trace('int_benefits.h_njb ' || int_benefits.h_njb);
1206: l_p11d_fields.h1_njb := int_benefits.h_njb;
1201: l_rec_count := l_rec_count + 1;
1202: hr_utility.trace('l_rec_count ' || l_rec_count);
1203: if l_rec_count = 1
1204: then
1205: hr_utility.trace('int_benefits.h_njb ' || int_benefits.h_njb);
1206: l_p11d_fields.h1_njb := int_benefits.h_njb;
1207: l_p11d_fields.h1_ayb := int_benefits.h_ayb;
1208: l_p11d_fields.h1_mao := int_benefits.h_mao;
1209: l_p11d_fields.h1_ip := int_benefits.h_ip;
1212: l_p11d_fields.h1_ce := int_benefits.h_ce;
1213: l_p11d_fields.h1_aye := int_benefits.h_aye;
1214: elsif l_rec_count = 2
1215: then
1216: hr_utility.trace('int_benefits.h_njb ' || int_benefits.h_njb);
1217: l_p11d_fields.h2_njb := int_benefits.h_njb;
1218: l_p11d_fields.h2_ayb := int_benefits.h_ayb;
1219: l_p11d_fields.h2_mao := int_benefits.h_mao;
1220: l_p11d_fields.h2_ip := int_benefits.h_ip;
1227: end loop;
1228: end if;
1229: end;
1230: begin
1231: hr_utility.trace('Inside fetch p11d rep_data p_assignment_action_id' || p_assignment_action_id);
1232: select payroll_action_id
1233: into g_payroll_action_id
1234: from pay_assignment_actions
1235: where assignment_action_id = p_assignment_action_id;
1233: into g_payroll_action_id
1234: from pay_assignment_actions
1235: where assignment_action_id = p_assignment_action_id;
1236:
1237: hr_utility.trace('g_payroll_action_id ' || g_payroll_action_id);
1238: populate_stored_fields;
1239: hr_utility.trace('Calling populate car fields ' );
1240: populate_car_fields;
1241: hr_utility.trace('Calling interest fields ' );
1235: where assignment_action_id = p_assignment_action_id;
1236:
1237: hr_utility.trace('g_payroll_action_id ' || g_payroll_action_id);
1238: populate_stored_fields;
1239: hr_utility.trace('Calling populate car fields ' );
1240: populate_car_fields;
1241: hr_utility.trace('Calling interest fields ' );
1242: populate_interest_fields;
1243: return l_p11d_fields;
1237: hr_utility.trace('g_payroll_action_id ' || g_payroll_action_id);
1238: populate_stored_fields;
1239: hr_utility.trace('Calling populate car fields ' );
1240: populate_car_fields;
1241: hr_utility.trace('Calling interest fields ' );
1242: populate_interest_fields;
1243: return l_p11d_fields;
1244: end;
1245: --
1256: l_fuel1_new VARCHAR2(1);
1257:
1258: begin
1259:
1260: hr_utility.trace('p_assignment_action_id ' || p_assignment_action_id);
1261: l_p11d_fields := fetch_p11d_rep_data(p_assignment_action_id);
1262:
1263: select payroll_action_id
1264: into l_pactid
1929: clob_to_blob(l_xfdf_clob,l_xfdf_blob);
1930: -- insert into temp values (l_xfdf_clob);
1931: dbms_lob.close(l_xfdf_clob);
1932: dbms_lob.freetemporary(l_xfdf_clob);
1933: hr_utility.trace('P11D.......................');
1934: return l_xfdf_blob;
1935: exception
1936: when OTHERS then
1937: HR_UTILITY.TRACE('sqleerm ' || sqlerrm);
1933: hr_utility.trace('P11D.......................');
1934: return l_xfdf_blob;
1935: exception
1936: when OTHERS then
1937: HR_UTILITY.TRACE('sqleerm ' || sqlerrm);
1938: HR_UTILITY.RAISE_ERROR;
1939: end;
1940: --
1941: /*------------------------------------------------------------------------
1934: return l_xfdf_blob;
1935: exception
1936: when OTHERS then
1937: HR_UTILITY.TRACE('sqleerm ' || sqlerrm);
1938: HR_UTILITY.RAISE_ERROR;
1939: end;
1940: --
1941: /*------------------------------------------------------------------------
1942: Below validate_display_output function returns 1 if ANY of the following
2078: l_return number;
2079: l_person_id number; --Added for the bug 9799823
2080:
2081: begin
2082: hr_utility.trace('Entering validate_display_output function');
2083: hr_utility.trace('p_assignment_action_id: '||p_assignment_action_id);
2084: l_asgactid:= p_assignment_action_id;
2085: open get_pactid ;
2086: fetch get_pactid into l_pactid;
2079: l_person_id number; --Added for the bug 9799823
2080:
2081: begin
2082: hr_utility.trace('Entering validate_display_output function');
2083: hr_utility.trace('p_assignment_action_id: '||p_assignment_action_id);
2084: l_asgactid:= p_assignment_action_id;
2085: open get_pactid ;
2086: fetch get_pactid into l_pactid;
2087: hr_utility.trace('l_pactid: '||l_pactid);
2083: hr_utility.trace('p_assignment_action_id: '||p_assignment_action_id);
2084: l_asgactid:= p_assignment_action_id;
2085: open get_pactid ;
2086: fetch get_pactid into l_pactid;
2087: hr_utility.trace('l_pactid: '||l_pactid);
2088: close get_pactid ;
2089:
2090: PAY_GB_P11D_ARCHIVE_SS.get_parameters(
2091: p_payroll_action_id => l_pactid,
2093: p_token_value => l_rep_run);
2094:
2095: open get_emp_ref;
2096: fetch get_emp_ref into l_emp_ref;
2097: hr_utility.trace('l_emp_ref: '||l_emp_ref);
2098: close get_emp_ref;
2099:
2100: open get_loan_amount(l_asgactid, l_emp_ref);
2101: fetch get_loan_amount into l_h_sum_max_amt_outstanding;
2098: close get_emp_ref;
2099:
2100: open get_loan_amount(l_asgactid, l_emp_ref);
2101: fetch get_loan_amount into l_h_sum_max_amt_outstanding;
2102: hr_utility.trace('l_h_sum_max_amt_outstanding: '||l_h_sum_max_amt_outstanding);
2103: close get_loan_amount;
2104:
2105: open get_threshold_val(l_rep_run);
2106: fetch get_threshold_val into l_loan_threshold;
2103: close get_loan_amount;
2104:
2105: open get_threshold_val(l_rep_run);
2106: fetch get_threshold_val into l_loan_threshold;
2107: hr_utility.trace('l_loan_threshold: '||l_loan_threshold);
2108: close get_threshold_val;
2109:
2110: open get_ben_value(l_asgactid, l_emp_ref);
2111: fetch get_ben_value into l_ben_value;
2108: close get_threshold_val;
2109:
2110: open get_ben_value(l_asgactid, l_emp_ref);
2111: fetch get_ben_value into l_ben_value;
2112: hr_utility.trace('l_ben_value: '||l_ben_value);
2113: close get_ben_value;
2114:
2115: open get_other_ben_exists (l_asgactid);
2116: fetch get_other_ben_exists into l_other_ben_exists;
2113: close get_ben_value;
2114:
2115: open get_other_ben_exists (l_asgactid);
2116: fetch get_other_ben_exists into l_other_ben_exists;
2117: hr_utility.trace('l_other_ben_exists: '||l_other_ben_exists);
2118: close get_other_ben_exists;
2119:
2120: if ((l_h_sum_max_amt_outstanding > l_loan_threshold)
2121: or (l_ben_value > 0 ) or (l_other_ben_exists = 1)) then
2118: close get_other_ben_exists;
2119:
2120: if ((l_h_sum_max_amt_outstanding > l_loan_threshold)
2121: or (l_ben_value > 0 ) or (l_other_ben_exists = 1)) then
2122: hr_utility.trace('returning 1');
2123: return 1;
2124: else
2125:
2126: l_person_id := get_person_id (l_asgactid); --Added for the bug 9799823
2129: open get_other_asg_action_exists (l_asgactid, l_pactid, l_person_id); --Modified for the bug 9799823
2130: loop
2131: fetch get_other_asg_action_exists into l_other_asg_action_id;
2132: if l_other_asg_action_id is not null then
2133: hr_utility.trace('l_other_asg_action_id: '||l_other_asg_action_id);
2134:
2135: open get_loan_amount(l_other_asg_action_id, l_emp_ref);
2136: fetch get_loan_amount into l_h_sum_max_amt_out_other;
2137: hr_utility.trace('l_h_sum_max_amt_out_other: '||l_h_sum_max_amt_out_other);
2133: hr_utility.trace('l_other_asg_action_id: '||l_other_asg_action_id);
2134:
2135: open get_loan_amount(l_other_asg_action_id, l_emp_ref);
2136: fetch get_loan_amount into l_h_sum_max_amt_out_other;
2137: hr_utility.trace('l_h_sum_max_amt_out_other: '||l_h_sum_max_amt_out_other);
2138: close get_loan_amount;
2139:
2140: open get_ben_value(l_other_asg_action_id, l_emp_ref);
2141: fetch get_ben_value into l_ben_value_other;
2138: close get_loan_amount;
2139:
2140: open get_ben_value(l_other_asg_action_id, l_emp_ref);
2141: fetch get_ben_value into l_ben_value_other;
2142: hr_utility.trace('l_ben_value_other: '||l_ben_value_other);
2143: close get_ben_value;
2144:
2145: open get_other_ben_exists (l_other_asg_action_id);
2146: fetch get_other_ben_exists into l_other_ben_exists_other;
2143: close get_ben_value;
2144:
2145: open get_other_ben_exists (l_other_asg_action_id);
2146: fetch get_other_ben_exists into l_other_ben_exists_other;
2147: hr_utility.trace('l_other_ben_exists_other: '||l_other_ben_exists_other);
2148: close get_other_ben_exists;
2149:
2150: if ((l_h_sum_max_amt_out_other > l_loan_threshold)
2151: or (l_ben_value_other > 0 ) or (l_other_ben_exists_other = 1)) then
2148: close get_other_ben_exists;
2149:
2150: if ((l_h_sum_max_amt_out_other > l_loan_threshold)
2151: or (l_ben_value_other > 0 ) or (l_other_ben_exists_other = 1)) then
2152: hr_utility.trace('Other asg returning 1');
2153: l_return := 1;
2154: end if;
2155: else
2156: hr_utility.trace('Inside else returning 0');
2152: hr_utility.trace('Other asg returning 1');
2153: l_return := 1;
2154: end if;
2155: else
2156: hr_utility.trace('Inside else returning 0');
2157: l_return := 0;
2158: end if;
2159: exit when (get_other_asg_action_exists%NOTFOUND) or (l_return = 1);
2160: end loop;
2157: l_return := 0;
2158: end if;
2159: exit when (get_other_asg_action_exists%NOTFOUND) or (l_return = 1);
2160: end loop;
2161: hr_utility.trace('Closing the cursor');
2162: close get_other_asg_action_exists;
2163: end if;
2164: if (l_return = 1) then
2165: return 1;
2167: return 0;
2168: end if;
2169: --Modification for the bug fix 9676798 ends here
2170:
2171: hr_utility.trace('Leaving validate_display_output function');
2172: end validate_display_output;
2173: --End of the fix for the EAP bug 9383416
2174:
2175: function get_p11d_year return VARCHAR2
2244: where payroll_action_id = p_payroll_action_id; --8875;
2245: exception
2246: when Old_Archive then
2247: retcode := 1;
2248: hr_utility.set_message(800, 'HR_78078_P11D_STAT_INCOR_ARCH');
2249: errbuf := hr_utility.get_message;
2250: hr_utility.raise_error;
2251: when OTHERS then
2252: retcode := 1;
2245: exception
2246: when Old_Archive then
2247: retcode := 1;
2248: hr_utility.set_message(800, 'HR_78078_P11D_STAT_INCOR_ARCH');
2249: errbuf := hr_utility.get_message;
2250: hr_utility.raise_error;
2251: when OTHERS then
2252: retcode := 1;
2253: hr_utility.set_message(800, 'HR_78077_P11D_STATUS_CHG_ERR');
2246: when Old_Archive then
2247: retcode := 1;
2248: hr_utility.set_message(800, 'HR_78078_P11D_STAT_INCOR_ARCH');
2249: errbuf := hr_utility.get_message;
2250: hr_utility.raise_error;
2251: when OTHERS then
2252: retcode := 1;
2253: hr_utility.set_message(800, 'HR_78077_P11D_STATUS_CHG_ERR');
2254: hr_utility.set_message_token(800, 'ERRORMSG', sqlerrm);
2249: errbuf := hr_utility.get_message;
2250: hr_utility.raise_error;
2251: when OTHERS then
2252: retcode := 1;
2253: hr_utility.set_message(800, 'HR_78077_P11D_STATUS_CHG_ERR');
2254: hr_utility.set_message_token(800, 'ERRORMSG', sqlerrm);
2255: errbuf := hr_utility.get_message;
2256: hr_utility.raise_error;
2257: end;
2250: hr_utility.raise_error;
2251: when OTHERS then
2252: retcode := 1;
2253: hr_utility.set_message(800, 'HR_78077_P11D_STATUS_CHG_ERR');
2254: hr_utility.set_message_token(800, 'ERRORMSG', sqlerrm);
2255: errbuf := hr_utility.get_message;
2256: hr_utility.raise_error;
2257: end;
2258:
2251: when OTHERS then
2252: retcode := 1;
2253: hr_utility.set_message(800, 'HR_78077_P11D_STATUS_CHG_ERR');
2254: hr_utility.set_message_token(800, 'ERRORMSG', sqlerrm);
2255: errbuf := hr_utility.get_message;
2256: hr_utility.raise_error;
2257: end;
2258:
2259: function get_lookup_meaning(p_lookup_type varchar2,
2252: retcode := 1;
2253: hr_utility.set_message(800, 'HR_78077_P11D_STATUS_CHG_ERR');
2254: hr_utility.set_message_token(800, 'ERRORMSG', sqlerrm);
2255: errbuf := hr_utility.get_message;
2256: hr_utility.raise_error;
2257: end;
2258:
2259: function get_lookup_meaning(p_lookup_type varchar2,
2260: p_lookup_code varchar2,
3332: else
3333: l_BEN_ST_DATE := car_rec.f_start;
3334: l_BEN_ED_DATE := car_rec.f_end;
3335: end if;
3336: hr_utility.trace('A4');
3337: select action_information30
3338: into l_car_count
3339: from pay_action_information pai_emp
3340: where pai_emp.action_context_id = p_assignment_action_id
3339: from pay_action_information pai_emp
3340: where pai_emp.action_context_id = p_assignment_action_id
3341: and pai_emp.action_information_category = 'GB P11D ASSIGNMENT RESULTA';
3342:
3343: hr_utility.trace('A5');
3344: if l_car_count = 1
3345: then
3346: l_ONLY_CAR_FLAG := 'Y';
3347: else
3347: else
3348: l_ONLY_CAR_FLAG := 'N';
3349: l_CAR_NUMBERS := l_car_count;
3350: end if ;
3351: hr_utility.trace('A6');
3352: hr_utility.trace('date_registered ' ||car_rec.date_registered);
3353: -- calculating benefit charge section
3354: if fnd_date.canonical_to_date( car_rec.date_registered)
3355: >= to_date('01-01-1998','dd-mm-yyyy') and car_rec.CO2 is not null
3348: l_ONLY_CAR_FLAG := 'N';
3349: l_CAR_NUMBERS := l_car_count;
3350: end if ;
3351: hr_utility.trace('A6');
3352: hr_utility.trace('date_registered ' ||car_rec.date_registered);
3353: -- calculating benefit charge section
3354: if fnd_date.canonical_to_date( car_rec.date_registered)
3355: >= to_date('01-01-1998','dd-mm-yyyy') and car_rec.CO2 is not null
3356: then
3353: -- calculating benefit charge section
3354: if fnd_date.canonical_to_date( car_rec.date_registered)
3355: >= to_date('01-01-1998','dd-mm-yyyy') and car_rec.CO2 is not null
3356: then
3357: hr_utility.trace('A');
3358: l_MOD_CO2 := floor(car_rec.CO2/5) * 5;
3359: hr_utility.trace('b');
3360: if (car_rec.f_fuel = 'B' or car_rec.f_fuel = 'C' or
3361: car_rec.f_fuel = 'H' or car_rec.f_fuel = 'L' or
3355: >= to_date('01-01-1998','dd-mm-yyyy') and car_rec.CO2 is not null
3356: then
3357: hr_utility.trace('A');
3358: l_MOD_CO2 := floor(car_rec.CO2/5) * 5;
3359: hr_utility.trace('b');
3360: if (car_rec.f_fuel = 'B' or car_rec.f_fuel = 'C' or
3361: car_rec.f_fuel = 'H' or car_rec.f_fuel = 'L' or
3362: car_rec.f_fuel = 'P')
3363: then
3360: if (car_rec.f_fuel = 'B' or car_rec.f_fuel = 'C' or
3361: car_rec.f_fuel = 'H' or car_rec.f_fuel = 'L' or
3362: car_rec.f_fuel = 'P')
3363: then
3364: hr_utility.trace('c');
3365: l_PERCENT_1 := car_rec.BENEFIT_PERCENT;
3366: l_BEN_1 := car_rec.benefit_charge;
3367: hr_utility.trace('d');
3368: elsif ( car_rec.f_fuel ='D' )
3363: then
3364: hr_utility.trace('c');
3365: l_PERCENT_1 := car_rec.BENEFIT_PERCENT;
3366: l_BEN_1 := car_rec.benefit_charge;
3367: hr_utility.trace('d');
3368: elsif ( car_rec.f_fuel ='D' )
3369: then
3370: hr_utility.trace('e');
3371: l_PERCENT_2 := car_rec.BENEFIT_PERCENT;
3366: l_BEN_1 := car_rec.benefit_charge;
3367: hr_utility.trace('d');
3368: elsif ( car_rec.f_fuel ='D' )
3369: then
3370: hr_utility.trace('e');
3371: l_PERCENT_2 := car_rec.BENEFIT_PERCENT;
3372: l_BEN_2 := car_rec.benefit_charge;
3373: elsif car_rec.f_fuel = 'E'
3374: then
3375: l_PERCENT_2 :=0;
3376: l_STD_DISC_2 := 0;
3377: end if;
3378:
3379: hr_utility.trace('f');
3380: if car_rec.f_fuel = 'H'
3381: then
3382: l_STD_DISC_1 := car_rec.STD_DISC;
3383: elsif ( car_rec.f_fuel = 'B' or car_rec.f_fuel = 'C')
3383: elsif ( car_rec.f_fuel = 'B' or car_rec.f_fuel = 'C')
3384: then
3385: l_STD_DISC_2 := car_rec.STD_DISC;
3386: end if;
3387: hr_utility.trace('g');
3388: if l_MOD_CO2 <= 145
3389: then
3390: l_EXTRA_CO2 := 165 -l_MOD_CO2;
3391: l_NORMAL_CO2 := l_EXTRA_CO2/20;
3390: l_EXTRA_CO2 := 165 -l_MOD_CO2;
3391: l_NORMAL_CO2 := l_EXTRA_CO2/20;
3392: l_ROUND_NORMAL_CO2 := floor(l_NORMAL_CO2);
3393: end if;
3394: hr_utility.trace('h');
3395: l_CAR_BENEFIT_1 := car_rec.ben_charge - car_rec.stand_disc - car_rec.additional_disc;
3396: elsif fnd_date.canonical_to_date( car_rec.date_registered)
3397: >= to_date('01-01-1998','dd-mm-yyyy') and car_rec.co2 is null
3398: then
3395: l_CAR_BENEFIT_1 := car_rec.ben_charge - car_rec.stand_disc - car_rec.additional_disc;
3396: elsif fnd_date.canonical_to_date( car_rec.date_registered)
3397: >= to_date('01-01-1998','dd-mm-yyyy') and car_rec.co2 is null
3398: then
3399: hr_utility.trace('i');
3400: if (car_rec.f_fuel = 'B' or car_rec.f_fuel = 'C' or
3401: car_rec.f_fuel = 'H' or car_rec.f_fuel = 'L' or
3402: car_rec.f_fuel = 'P' )
3403: then
3421: elsif car_rec.f_fuel = 'E'
3422: then
3423: l_STD_DISC_3 := car_rec.STD_DISC;
3424: end if;
3425: hr_utility.trace('j');
3426: if car_rec.f_fuel ='E'
3427: then
3428: l_CAR_BENEFIT_2 := 0;
3429: else
3432: l_full_DISC := null;
3433: elsif fnd_date.canonical_to_date( car_rec.date_registered)
3434: < to_date('01-01-1998','dd-mm-yyyy')
3435: then
3436: hr_utility.trace('K');
3437: -- hr_utility.trace('car_rec.benefit_charge '|| car_rec.benefit_charge);
3438: l_full_disc := null;
3439: if car_rec.f_esize <= 1400
3440: then
3433: elsif fnd_date.canonical_to_date( car_rec.date_registered)
3434: < to_date('01-01-1998','dd-mm-yyyy')
3435: then
3436: hr_utility.trace('K');
3437: -- hr_utility.trace('car_rec.benefit_charge '|| car_rec.benefit_charge);
3438: l_full_disc := null;
3439: if car_rec.f_esize <= 1400
3440: then
3441: l_CAR_BENEFIT_3 := car_rec.benefit_charge;
3446: then
3447: l_CAR_BENEFIT_5 := car_rec.benefit_charge;
3448: end if;
3449: end if;
3450: hr_utility.trace('k');
3451: -- fuel benefit
3452: l_esize := null;
3453: l_UNAVIALABLE_VAL_2 := null;
3454: if car_rec.FUEL_BENEFIT = 'Y'
3461: else
3462: l_FUEL_FLAG := 'D';
3463: end if;
3464: end if;
3465: hr_utility.trace('L');
3466: l_xfdf_str_tab.extend;
3467: l_xfdf_str_tab(l_loop_count) := '
3468:
3469:
3583: else
3584: l_BEN_ST_DATE := car_rec.f_start;
3585: l_BEN_ED_DATE := car_rec.f_end;
3586: end if;
3587: -- hr_utility.trace_on(null,'CAR');
3588: hr_utility.trace('A4');
3589: select action_information30
3590: into l_car_count
3591: from pay_action_information pai_emp
3584: l_BEN_ST_DATE := car_rec.f_start;
3585: l_BEN_ED_DATE := car_rec.f_end;
3586: end if;
3587: -- hr_utility.trace_on(null,'CAR');
3588: hr_utility.trace('A4');
3589: select action_information30
3590: into l_car_count
3591: from pay_action_information pai_emp
3592: where pai_emp.action_context_id = p_assignment_action_id
3590: into l_car_count
3591: from pay_action_information pai_emp
3592: where pai_emp.action_context_id = p_assignment_action_id
3593: and pai_emp.action_information_category = 'GB P11D ASSIGNMENT RESULTA';
3594: hr_utility.trace('A5');
3595: if l_car_count = 1
3596: then
3597: l_ONLY_CAR_FLAG := 'Y';
3598: else
3598: else
3599: l_ONLY_CAR_FLAG := 'N';
3600: l_CAR_NUMBERS := l_car_count;
3601: end if ;
3602: hr_utility.trace('A6');
3603: hr_utility.trace('date_registered ' ||car_rec.date_registered);
3604: -- calculating benefit charge section
3605: if fnd_date.canonical_to_date( car_rec.date_registered)
3606: >= to_date('01-01-1998','dd-mm-yyyy') and car_rec.CO2 is not null
3599: l_ONLY_CAR_FLAG := 'N';
3600: l_CAR_NUMBERS := l_car_count;
3601: end if ;
3602: hr_utility.trace('A6');
3603: hr_utility.trace('date_registered ' ||car_rec.date_registered);
3604: -- calculating benefit charge section
3605: if fnd_date.canonical_to_date( car_rec.date_registered)
3606: >= to_date('01-01-1998','dd-mm-yyyy') and car_rec.CO2 is not null
3607: then
3604: -- calculating benefit charge section
3605: if fnd_date.canonical_to_date( car_rec.date_registered)
3606: >= to_date('01-01-1998','dd-mm-yyyy') and car_rec.CO2 is not null
3607: then
3608: hr_utility.trace('a');
3609: l_MOD_CO2 := floor(car_rec.CO2/5) * 5;
3610: hr_utility.trace('b');
3611: l_PERCENT_1 := car_rec.BENEFIT_PERCENT;
3612: --l_BEN_1 := car_rec.benefit_charge;
3606: >= to_date('01-01-1998','dd-mm-yyyy') and car_rec.CO2 is not null
3607: then
3608: hr_utility.trace('a');
3609: l_MOD_CO2 := floor(car_rec.CO2/5) * 5;
3610: hr_utility.trace('b');
3611: l_PERCENT_1 := car_rec.BENEFIT_PERCENT;
3612: --l_BEN_1 := car_rec.benefit_charge;
3613: hr_utility.trace('c');
3614: l_STD_DISC_1 := car_rec.STD_DISC;
3609: l_MOD_CO2 := floor(car_rec.CO2/5) * 5;
3610: hr_utility.trace('b');
3611: l_PERCENT_1 := car_rec.BENEFIT_PERCENT;
3612: --l_BEN_1 := car_rec.benefit_charge;
3613: hr_utility.trace('c');
3614: l_STD_DISC_1 := car_rec.STD_DISC;
3615: hr_utility.trace('d');
3616: if (car_rec.f_fuel = 'B' or car_rec.f_fuel = 'H') -- Bug #4293824
3617: then
3611: l_PERCENT_1 := car_rec.BENEFIT_PERCENT;
3612: --l_BEN_1 := car_rec.benefit_charge;
3613: hr_utility.trace('c');
3614: l_STD_DISC_1 := car_rec.STD_DISC;
3615: hr_utility.trace('d');
3616: if (car_rec.f_fuel = 'B' or car_rec.f_fuel = 'H') -- Bug #4293824
3617: then
3618: if car_rec.CO2 <= 120
3619: then
3625: l_CAR_BENEFIT_1 := car_rec.TOTAL_BENIFIT ;
3626: elsif fnd_date.canonical_to_date( car_rec.date_registered)
3627: >= to_date('01-01-1998','dd-mm-yyyy') and car_rec.CO2 is null
3628: then
3629: hr_utility.trace('e');
3630: -- below is the code snippet for P11D change 6.1.4.2
3631: if car_rec.f_fuel = 'E' then
3632: l_PERCENT_2 := 0;
3633: l_STD_DISC_2 := 0;
3636: l_STD_DISC_2 := car_rec.STD_DISC;
3637: l_PERCENT_2 := car_rec.BENEFIT_PERCENT;
3638: l_CAR_BENEFIT_2 := car_rec.TOTAL_BENIFIT_2;
3639: end if;
3640: hr_utility.trace('f');
3641:
3642:
3643: elsif (fnd_date.canonical_to_date( car_rec.date_registered)< to_date('01-01-1998','dd-mm-yyyy'))
3644: then
3641:
3642:
3643: elsif (fnd_date.canonical_to_date( car_rec.date_registered)< to_date('01-01-1998','dd-mm-yyyy'))
3644: then
3645: hr_utility.trace('g');
3646: -- hr_utility.trace('car_rec.benefit_charge '|| car_rec.benefit_charge);
3647: l_full_disc := null;
3648: l_esize := car_rec.f_esize;
3649: if car_rec.f_esize <= 1400
3642:
3643: elsif (fnd_date.canonical_to_date( car_rec.date_registered)< to_date('01-01-1998','dd-mm-yyyy'))
3644: then
3645: hr_utility.trace('g');
3646: -- hr_utility.trace('car_rec.benefit_charge '|| car_rec.benefit_charge);
3647: l_full_disc := null;
3648: l_esize := car_rec.f_esize;
3649: if car_rec.f_esize <= 1400
3650: then
3658: else
3659: l_CAR_BENEFIT_3 := 32;
3660: end if;
3661: end if;
3662: hr_utility.trace('h');
3663: -- fuel benefit
3664: if car_rec.FUEL_BENEFIT = 'Y'
3665: then
3666: L_FUEL_BENEFIT_YEAR := car_rec.FUEL_BENEFIT_YEAR;
3667: l_add_days := car_rec.additional_days;
3668: l_tot_days := car_rec.total_days;
3669: l_unavailble_days := car_rec.unavailable;
3670: end if;
3671: hr_utility.trace('i');
3672: l_xfdf_str_tab.extend;
3673: l_xfdf_str_tab(l_loop_count) := '
3674:
3675:
3797: else
3798: l_BEN_ST_DATE := car_rec.f_start;
3799: l_BEN_ED_DATE := car_rec.f_end;
3800: end if;
3801: -- hr_utility.trace_on(null,'CAR');
3802: hr_utility.trace('A4');
3803: select action_information30
3804: into l_car_count
3805: from pay_action_information pai_emp
3798: l_BEN_ST_DATE := car_rec.f_start;
3799: l_BEN_ED_DATE := car_rec.f_end;
3800: end if;
3801: -- hr_utility.trace_on(null,'CAR');
3802: hr_utility.trace('A4');
3803: select action_information30
3804: into l_car_count
3805: from pay_action_information pai_emp
3806: where pai_emp.action_context_id = p_assignment_action_id
3804: into l_car_count
3805: from pay_action_information pai_emp
3806: where pai_emp.action_context_id = p_assignment_action_id
3807: and pai_emp.action_information_category = 'GB P11D ASSIGNMENT RESULTA';
3808: hr_utility.trace('A5');
3809: if l_car_count = 1
3810: then
3811: l_ONLY_CAR_FLAG := 'Y';
3812: else
3812: else
3813: l_ONLY_CAR_FLAG := 'N';
3814: l_CAR_NUMBERS := l_car_count;
3815: end if ;
3816: hr_utility.trace('A6');
3817: hr_utility.trace('date_registered ' ||car_rec.date_registered);
3818: -- calculating benefit charge section
3819: if (to_number(l_rep_run)) > 2008 then --P11D 08/09
3820: if fnd_date.canonical_to_date( car_rec.date_registered)
3813: l_ONLY_CAR_FLAG := 'N';
3814: l_CAR_NUMBERS := l_car_count;
3815: end if ;
3816: hr_utility.trace('A6');
3817: hr_utility.trace('date_registered ' ||car_rec.date_registered);
3818: -- calculating benefit charge section
3819: if (to_number(l_rep_run)) > 2008 then --P11D 08/09
3820: if fnd_date.canonical_to_date( car_rec.date_registered)
3821: >= to_date('01-01-1998','dd-mm-yyyy') and car_rec.CO2 is not null
3821: >= to_date('01-01-1998','dd-mm-yyyy') and car_rec.CO2 is not null
3822: and car_rec.CO2 > 120 -- P11D 08/09
3823: --hr_gb_process_p11d_entries_pkg.get_global_value('P11D_QUALEC_CO2_EMISSIONS',l_BEN_ED_DATE)
3824: then
3825: hr_utility.trace('a');
3826: -- Bug 13618076: Co2 value should not be rounded for values between 121 to 124
3827: if car_rec.CO2 > 125 then
3828: l_MOD_CO2 := floor(car_rec.CO2/5) * 5;
3829: else
3835: l_MOD_CO2 := 130;
3836: end if;
3837: */
3838: --P11D 08/09
3839: hr_utility.trace('b');
3840: l_PERCENT_1 := car_rec.BENEFIT_PERCENT;
3841: hr_utility.trace('c');
3842: l_STD_DISC_1 := car_rec.STD_DISC;
3843: l_ROUND_NORMAL_CO2 := car_rec.ROUND_NORMAL_CO2;
3837: */
3838: --P11D 08/09
3839: hr_utility.trace('b');
3840: l_PERCENT_1 := car_rec.BENEFIT_PERCENT;
3841: hr_utility.trace('c');
3842: l_STD_DISC_1 := car_rec.STD_DISC;
3843: l_ROUND_NORMAL_CO2 := car_rec.ROUND_NORMAL_CO2;
3844: l_full_DISC := car_rec.FULL_DISC;
3845: l_CAR_BENEFIT_1 := car_rec.TOTAL_BENIFIT ;
3893: /* bug 8277887 changed car_rec.co2 is null to nvl(car_rec.CO2,0) = 0 */
3894: elsif fnd_date.canonical_to_date( car_rec.date_registered)
3895: >= to_date('01-01-1998','dd-mm-yyyy') and nvl(car_rec.CO2,0) = 0
3896: then
3897: hr_utility.trace('e');
3898: --l_PERCENT_2 := car_rec.BENEFIT_PERCENT; --chnage this as below for 6.1.4.2 P11D change
3899: if car_rec.f_fuel = 'E' then
3900: l_PERCENT_2 := 0;
3901: l_STD_DISC_2 := 0;
3904: l_STD_DISC_2 := car_rec.STD_DISC;
3905: l_PERCENT_2 := car_rec.BENEFIT_PERCENT;
3906: l_CAR_BENEFIT_2 := car_rec.TOTAL_BENIFIT_2;
3907: end if;
3908: hr_utility.trace('f');
3909:
3910:
3911: elsif (fnd_date.canonical_to_date( car_rec.date_registered)< to_date('01-01-1998','dd-mm-yyyy'))
3912: then
3909:
3910:
3911: elsif (fnd_date.canonical_to_date( car_rec.date_registered)< to_date('01-01-1998','dd-mm-yyyy'))
3912: then
3913: hr_utility.trace('g');
3914: -- hr_utility.trace('car_rec.benefit_charge '|| car_rec.benefit_charge);
3915: l_full_disc := null;
3916: l_esize := car_rec.f_esize;
3917: open csr_engine_discount(to_number(l_esize),
3910:
3911: elsif (fnd_date.canonical_to_date( car_rec.date_registered)< to_date('01-01-1998','dd-mm-yyyy'))
3912: then
3913: hr_utility.trace('g');
3914: -- hr_utility.trace('car_rec.benefit_charge '|| car_rec.benefit_charge);
3915: l_full_disc := null;
3916: l_esize := car_rec.f_esize;
3917: open csr_engine_discount(to_number(l_esize),
3918: fnd_date.canonical_to_date(car_rec.ben_end));
3925:
3926: if fnd_date.canonical_to_date( car_rec.date_registered)
3927: >= to_date('01-01-1998','dd-mm-yyyy') and car_rec.CO2 is not null
3928: then
3929: hr_utility.trace('a');
3930: l_MOD_CO2 := floor(car_rec.CO2/5) * 5;
3931: hr_utility.trace('b');
3932: l_PERCENT_1 := car_rec.BENEFIT_PERCENT;
3933: hr_utility.trace('c');
3927: >= to_date('01-01-1998','dd-mm-yyyy') and car_rec.CO2 is not null
3928: then
3929: hr_utility.trace('a');
3930: l_MOD_CO2 := floor(car_rec.CO2/5) * 5;
3931: hr_utility.trace('b');
3932: l_PERCENT_1 := car_rec.BENEFIT_PERCENT;
3933: hr_utility.trace('c');
3934: l_STD_DISC_1 := car_rec.STD_DISC;
3935: l_ROUND_NORMAL_CO2 := car_rec.ROUND_NORMAL_CO2;
3929: hr_utility.trace('a');
3930: l_MOD_CO2 := floor(car_rec.CO2/5) * 5;
3931: hr_utility.trace('b');
3932: l_PERCENT_1 := car_rec.BENEFIT_PERCENT;
3933: hr_utility.trace('c');
3934: l_STD_DISC_1 := car_rec.STD_DISC;
3935: l_ROUND_NORMAL_CO2 := car_rec.ROUND_NORMAL_CO2;
3936: l_full_DISC := car_rec.FULL_DISC;
3937: l_CAR_BENEFIT_1 := car_rec.TOTAL_BENIFIT ;
3937: l_CAR_BENEFIT_1 := car_rec.TOTAL_BENIFIT ;
3938: elsif fnd_date.canonical_to_date( car_rec.date_registered)
3939: >= to_date('01-01-1998','dd-mm-yyyy') and car_rec.CO2 is null
3940: then
3941: hr_utility.trace('e');
3942: --l_PERCENT_2 := car_rec.BENEFIT_PERCENT; -- below is the code snippet for P11D change 6.1.4.2
3943: if car_rec.f_fuel = 'E' then
3944: l_PERCENT_2 := 0;
3945: l_STD_DISC_2 := 0;
3948: l_STD_DISC_2 := car_rec.STD_DISC;
3949: l_PERCENT_2 := car_rec.BENEFIT_PERCENT;
3950: l_CAR_BENEFIT_2 := car_rec.TOTAL_BENIFIT_2;
3951: end if;
3952: hr_utility.trace('f');
3953:
3954:
3955: elsif (fnd_date.canonical_to_date( car_rec.date_registered)< to_date('01-01-1998','dd-mm-yyyy'))
3956: then
3953:
3954:
3955: elsif (fnd_date.canonical_to_date( car_rec.date_registered)< to_date('01-01-1998','dd-mm-yyyy'))
3956: then
3957: hr_utility.trace('g');
3958: -- hr_utility.trace('car_rec.benefit_charge '|| car_rec.benefit_charge);
3959: l_full_disc := null;
3960: l_esize := car_rec.f_esize;
3961: open csr_engine_discount(to_number(l_esize),
3954:
3955: elsif (fnd_date.canonical_to_date( car_rec.date_registered)< to_date('01-01-1998','dd-mm-yyyy'))
3956: then
3957: hr_utility.trace('g');
3958: -- hr_utility.trace('car_rec.benefit_charge '|| car_rec.benefit_charge);
3959: l_full_disc := null;
3960: l_esize := car_rec.f_esize;
3961: open csr_engine_discount(to_number(l_esize),
3962: fnd_date.canonical_to_date(car_rec.ben_end));
3980: l_fuel_new := car_rec.f_fuel;
3981: END IF;
3982:
3983:
3984: hr_utility.trace('h');
3985: -- fuel benefit
3986: if car_rec.FUEL_BENEFIT = 'Y'
3987: then
3988: L_FUEL_BENEFIT_YEAR := car_rec.FUEL_BENEFIT_YEAR;
3989: l_add_days := car_rec.additional_days;
3990: l_tot_days := car_rec.total_days;
3991: l_unavailble_days := car_rec.unavailable;
3992: end if;
3993: hr_utility.trace('i');
3994: l_xfdf_str_tab.extend;
3995: l_xfdf_str_tab(l_loop_count) := '
3996:
3997:
4348: l_emp_ref_no,
4349: l_employer_name)
4350: loop
4351: l_loop_count := l_loop_count+1;
4352: hr_utility.trace('a');
4353: -- l_ws3_info_tab(l_loop_count).l_vans_entries := van_entries;
4354: l_xfdf_str_tab.extend;
4355: l_xfdf_str_tab(l_loop_count) := '
4356:
4369: '
4370: '
4371: '
4372: '
4373: hr_utility.trace('aa');
4374: if van_entries.exclusive_flag = 'Y'
4375: then -- we fill section 1
4376: if fnd_date.canonical_to_date(van_entries.dreg) > to_date('05-04-2000','dd-mm-yyyy')
4377: then
4380: else
4381: l_xfdf_str_tab(l_loop_count) := l_xfdf_str_tab(l_loop_count) ||
4382: '
4383: end if;
4384: hr_utility.trace('2');
4385: l_xfdf_str_tab(l_loop_count) := l_xfdf_str_tab(l_loop_count) ||
4386: '
4387: '
4388: '
4394: '
4395: '
4396: '
4397: '
4398: hr_utility.trace('3');
4399: else -- else of van_entries.exclusive_flag = 'Y'
4400: hr_utility.trace('4');
4401: if fnd_date.canonical_to_date(van_entries.dreg) > to_date('05-04-2000','dd-mm-yyyy')
4402: then
4396: '
4397: '
4398: hr_utility.trace('3');
4399: else -- else of van_entries.exclusive_flag = 'Y'
4400: hr_utility.trace('4');
4401: if fnd_date.canonical_to_date(van_entries.dreg) > to_date('05-04-2000','dd-mm-yyyy')
4402: then
4403: l_xfdf_str_tab(l_loop_count) := l_xfdf_str_tab(l_loop_count) ||
4404: '
4407: '
4408: end if;
4409: l_xfdf_str_tab(l_loop_count) := l_xfdf_str_tab(l_loop_count) ||
4410: '
4411: hr_utility.trace('5');
4412: if nvl(van_entries.UNAVAILABLE_VAL,0) = 0
4413: then
4414: l_xfdf_str_tab(l_loop_count) := l_xfdf_str_tab(l_loop_count) ||
4415: '
4412: if nvl(van_entries.UNAVAILABLE_VAL,0) = 0
4413: then
4414: l_xfdf_str_tab(l_loop_count) := l_xfdf_str_tab(l_loop_count) ||
4415: '
4416: hr_utility.trace('7');
4417: else
4418: hr_utility.trace('6');
4419: l_xfdf_str_tab(l_loop_count) := l_xfdf_str_tab(l_loop_count) ||
4420: '
4414: l_xfdf_str_tab(l_loop_count) := l_xfdf_str_tab(l_loop_count) ||
4415: '
4416: hr_utility.trace('7');
4417: else
4418: hr_utility.trace('6');
4419: l_xfdf_str_tab(l_loop_count) := l_xfdf_str_tab(l_loop_count) ||
4420: '
4421: '
4422: '
4425: '
4426: nvl(van_entries.UNAVAILABLE_2,0) + nvl(van_entries.UNAVAILABLE_3,0) + nvl(van_entries.UNAVAILABLE_4,0)) || '
4427: '
4428: end if;
4429: hr_utility.trace('8');
4430: l_xfdf_str_tab(l_loop_count) := l_xfdf_str_tab(l_loop_count) ||
4431: '
4432: to_char(to_number((nvl(van_entries.standard_charge,0) - nvl(van_entries.UNAVAILABLE_VAL,0))),
4433: 'FM999,999,990.00') || '
4441: '
4442: '
4443: '
4444: end if; -- end of checking for van_entries.exclusive_flag = 'Y'
4445: hr_utility.trace('9');
4446: l_xfdf_str_tab(l_loop_count) := l_xfdf_str_tab(l_loop_count) || ' ';
4447: end loop;
4448: elsif to_number(l_rep_run) >= 2006 and to_number(l_rep_run) < 2008 then -- P11D changes 07/08 -- l_rep_run > 2005, so 2006 onwards.
4449: open csr_vans_05_count(l_pactid,
4466: l_emp_ref_no,
4467: l_employer_name)
4468: loop
4469: l_loop_count := l_loop_count+1;
4470: hr_utility.trace('Vans');
4471: l_after_flag := 'N';
4472: if to_number(l_rep_run) < 2007 then
4473: if fnd_date.canonical_to_date(vans_entries.date_registered) > to_date('05-04-2002','dd-mm-yyyy')
4474: then
4585: l_fuel_charge_reduction := NULL;
4586: l_days_after_fuel_wd :=NULL;
4587: END IF;
4588: --end of bug 7146755
4589: hr_utility.trace('Vans 2007');
4590: l_after_flag := 'N';
4591: /* if to_number(l_rep_run) < 2007 then
4592: if fnd_date.canonical_to_date(vans_entries.date_registered) > to_date('05-04-2002','dd-mm-yyyy')
4593: then
4684: ' ';
4685: end loop;
4686: end if;
4687:
4688: hr_utility.trace('10');
4689: open l_ret_ref_cursor for
4690: select *
4691: from table (cast(l_xfdf_str_tab as per_gb_xfdftableType));
4692: p_record_num := l_loop_count;
4758: l_h_sum_max_amt_outstanding Number;
4759: l_rep_run varchar2(10);
4760: l_loan_threshold Number;
4761: begin
4762: hr_utility.trace('calling get_employer_details');
4763: get_employer_details(p_assignment_action_id,
4764: l_emp_ref_no,
4765: l_employer_name);
4766: hr_utility.trace('calling get_employee_details');
4762: hr_utility.trace('calling get_employer_details');
4763: get_employer_details(p_assignment_action_id,
4764: l_emp_ref_no,
4765: l_employer_name);
4766: hr_utility.trace('calling get_employee_details');
4767: get_employee_details(p_assignment_action_id,
4768: l_full_name,
4769: l_national_ins_no,
4770: l_employee_number);
4781: p_token_name => 'Rep_Run',
4782: p_token_value => l_rep_run);
4783:
4784: l_loop_count := 0;
4785: hr_utility.trace('l_pactid ' || l_pactid);
4786: hr_utility.trace('l_person_id '|| l_person_id);
4787: hr_utility.trace('l_emp_ref_no '|| l_emp_ref_no);
4788: hr_utility.trace('l_employer_name '|| l_employer_name);
4789: l_tab_index := 0;
4782: p_token_value => l_rep_run);
4783:
4784: l_loop_count := 0;
4785: hr_utility.trace('l_pactid ' || l_pactid);
4786: hr_utility.trace('l_person_id '|| l_person_id);
4787: hr_utility.trace('l_emp_ref_no '|| l_emp_ref_no);
4788: hr_utility.trace('l_employer_name '|| l_employer_name);
4789: l_tab_index := 0;
4790:
4783:
4784: l_loop_count := 0;
4785: hr_utility.trace('l_pactid ' || l_pactid);
4786: hr_utility.trace('l_person_id '|| l_person_id);
4787: hr_utility.trace('l_emp_ref_no '|| l_emp_ref_no);
4788: hr_utility.trace('l_employer_name '|| l_employer_name);
4789: l_tab_index := 0;
4790:
4791: select to_number(nvl(ACTION_INFORMATION23,'0'))
4784: l_loop_count := 0;
4785: hr_utility.trace('l_pactid ' || l_pactid);
4786: hr_utility.trace('l_person_id '|| l_person_id);
4787: hr_utility.trace('l_emp_ref_no '|| l_emp_ref_no);
4788: hr_utility.trace('l_employer_name '|| l_employer_name);
4789: l_tab_index := 0;
4790:
4791: select to_number(nvl(ACTION_INFORMATION23,'0'))
4792: into l_h_sum_max_amt_outstanding
4813: l_employer_name)
4814: loop
4815: l_employee_number := get_assignment_number(int_entries.action_context_id);
4816: l_loop_count := l_loop_count+1;
4817: hr_utility.trace('l_loop_count ' ||l_loop_count);
4818: --we need 5 records in a single pdf template so if more than 5 exists
4819: --we need extra pages ceil(l_loop_count/5) will give the index number
4820: --for the l_ws4_info_tab
4821: if l_tab_index =ceil(l_loop_count/5)
4850: '
4851: '
4852: '
4853: end if;
4854: hr_utility.trace('l_tab_index ' ||l_tab_index);
4855: l_field_num := mod(l_loop_count,5);
4856: if l_field_num = 0
4857: then
4858: l_field_num := 5;
4856: if l_field_num = 0
4857: then
4858: l_field_num := 5;
4859: end if;
4860: hr_utility.trace('l_field_num ' ||l_field_num);
4861: l_xfdf_str_tab(l_tab_index) := l_xfdf_str_tab(l_tab_index) ||
4862: '
4863: '
4864: '
4880: '
4881: end loop;
4882: -- this will ensure the xfdf sytring ends with the correct fields
4883: end if;
4884: hr_utility.trace('After cursor close');
4885: hr_utility.trace('l_tab_index' || l_tab_index);
4886: if l_tab_index <> 0
4887: then
4888: l_xfdf_str_tab(l_tab_index) := l_xfdf_str_tab(l_tab_index) || ' ';
4881: end loop;
4882: -- this will ensure the xfdf sytring ends with the correct fields
4883: end if;
4884: hr_utility.trace('After cursor close');
4885: hr_utility.trace('l_tab_index' || l_tab_index);
4886: if l_tab_index <> 0
4887: then
4888: l_xfdf_str_tab(l_tab_index) := l_xfdf_str_tab(l_tab_index) || ' ';
4889: end if;
5258: l_loop_count := l_loop_count+1;
5259: -- l_ws6_info_tab(l_loop_count).l_amap_entries := amap_entries;
5260: -- hardcoding 10000, as currently this si the fig,
5261: -- if this changes then we may archve max limit as well.
5262: HR_UTILITY.TRaCE('A');
5263: if l_BCycle = 'BCY'
5264: then
5265: l_ws6_info_tab(l_loop_count).l_c_mileage_allow_payments := amap_entries.B_MILEAGE_ALLOW_PAYMENTS;
5266: l_ws6_info_tab(l_loop_count).l_c_tax_deducted_payments := amap_entries.B_TAX_DEDUCTED_PAYMENTS;
5299: l_mile2_amount :=0;
5300: if l_ws6_info_tab(l_loop_count).l_c_business_miles > 10000
5301: then
5302: l_miles1 := 10000;
5303: HR_UTILITY.TRaCE('B');
5304: l_miles2 := l_ws6_info_tab(l_loop_count).l_c_business_miles - 10000;
5305: else
5306: HR_UTILITY.TRaCE('C');
5307: l_miles1 := l_ws6_info_tab(l_loop_count).l_c_business_miles;
5302: l_miles1 := 10000;
5303: HR_UTILITY.TRaCE('B');
5304: l_miles2 := l_ws6_info_tab(l_loop_count).l_c_business_miles - 10000;
5305: else
5306: HR_UTILITY.TRaCE('C');
5307: l_miles1 := l_ws6_info_tab(l_loop_count).l_c_business_miles;
5308: l_miles2 := 0;
5309: end if;
5310: HR_UTILITY.TRaCE('D');
5306: HR_UTILITY.TRaCE('C');
5307: l_miles1 := l_ws6_info_tab(l_loop_count).l_c_business_miles;
5308: l_miles2 := 0;
5309: end if;
5310: HR_UTILITY.TRaCE('D');
5311: l_mile1_amount := l_ws6_info_tab(l_loop_count).l_c_reimbrs1_rate1 * l_miles1;
5312: l_mile2_amount := l_ws6_info_tab(l_loop_count).l_c_reimbrs2_rate2 * l_miles2;
5313: l_ws6_info_tab(l_loop_count).l_bus_mile_1_amount:= to_char(to_number(l_mile1_amount),'FM999,999,990.00');
5314: HR_UTILITY.TRaCE('E');
5310: HR_UTILITY.TRaCE('D');
5311: l_mile1_amount := l_ws6_info_tab(l_loop_count).l_c_reimbrs1_rate1 * l_miles1;
5312: l_mile2_amount := l_ws6_info_tab(l_loop_count).l_c_reimbrs2_rate2 * l_miles2;
5313: l_ws6_info_tab(l_loop_count).l_bus_mile_1_amount:= to_char(to_number(l_mile1_amount),'FM999,999,990.00');
5314: HR_UTILITY.TRaCE('E');
5315: l_ws6_info_tab(l_loop_count).l_bus_mile_2_amount:= to_char(to_number(l_mile2_amount),'FM999,999,990.00');
5316: l_ws6_info_tab(l_loop_count).l_total_approved_maps:= to_char(to_number((l_mile1_amount+l_mile2_amount)),'FM999,999,990.00');
5317: HR_UTILITY.TRaCE('EE');
5318: l_ws6_info_tab(l_loop_count).l_total_payment :=
5313: l_ws6_info_tab(l_loop_count).l_bus_mile_1_amount:= to_char(to_number(l_mile1_amount),'FM999,999,990.00');
5314: HR_UTILITY.TRaCE('E');
5315: l_ws6_info_tab(l_loop_count).l_bus_mile_2_amount:= to_char(to_number(l_mile2_amount),'FM999,999,990.00');
5316: l_ws6_info_tab(l_loop_count).l_total_approved_maps:= to_char(to_number((l_mile1_amount+l_mile2_amount)),'FM999,999,990.00');
5317: HR_UTILITY.TRaCE('EE');
5318: l_ws6_info_tab(l_loop_count).l_total_payment :=
5319: greatest(l_ws6_info_tab(l_loop_count).l_c_net_allowance -l_mile1_amount - l_mile2_amount,0);
5320: HR_UTILITY.TRaCE('FF');
5321: l_ws6_info_tab(l_loop_count).l_business_miles_1:= l_miles1;
5316: l_ws6_info_tab(l_loop_count).l_total_approved_maps:= to_char(to_number((l_mile1_amount+l_mile2_amount)),'FM999,999,990.00');
5317: HR_UTILITY.TRaCE('EE');
5318: l_ws6_info_tab(l_loop_count).l_total_payment :=
5319: greatest(l_ws6_info_tab(l_loop_count).l_c_net_allowance -l_mile1_amount - l_mile2_amount,0);
5320: HR_UTILITY.TRaCE('FF');
5321: l_ws6_info_tab(l_loop_count).l_business_miles_1:= l_miles1;
5322: l_ws6_info_tab(l_loop_count).l_business_miles_2:= l_miles2;
5323: --hr_uitility.trace('gg');
5324: l_ws6_info_tab(l_loop_count).l_taxable_passen_payment :=
5898: dbms_lob.writeAppend( l_xfdf_str, length(l_xfdf_intermetiate_var) ,l_xfdf_intermetiate_var );
5899: end if;
5900: end;
5901: begin
5902: --hr_utility.trace_on(null,'KT');
5903: hr_utility.trace('calling get_employer_details');
5904: get_employer_details(p_assignment_action_id,
5905: l_emp_ref_no,
5906: l_employer_name);
5899: end if;
5900: end;
5901: begin
5902: --hr_utility.trace_on(null,'KT');
5903: hr_utility.trace('calling get_employer_details');
5904: get_employer_details(p_assignment_action_id,
5905: l_emp_ref_no,
5906: l_employer_name);
5907: hr_utility.trace('calling get_employee_details');
5903: hr_utility.trace('calling get_employer_details');
5904: get_employer_details(p_assignment_action_id,
5905: l_emp_ref_no,
5906: l_employer_name);
5907: hr_utility.trace('calling get_employee_details');
5908: get_employee_details(p_assignment_action_id,
5909: l_full_name,
5910: l_national_ins_no,
5911: l_employee_number);
5918: l_pactid := get_pactid(p_assignment_action_id);
5919: l_person_id := get_person_id (p_assignment_action_id );
5920: l_employee_number := get_assignment_number(p_assignment_action_id, true, l_person_id, l_emp_ref_no);
5921: l_loop_count := 0;
5922: -- hr_utility.trace_on(null,'SUMM');
5923: hr_utility.trace('l_pactid ' || l_pactid);
5924: hr_utility.trace('l_person_id '|| l_person_id);
5925: hr_utility.trace('l_emp_ref_no '|| l_emp_ref_no);
5926: hr_utility.trace('l_employer_name '|| l_employer_name);
5919: l_person_id := get_person_id (p_assignment_action_id );
5920: l_employee_number := get_assignment_number(p_assignment_action_id, true, l_person_id, l_emp_ref_no);
5921: l_loop_count := 0;
5922: -- hr_utility.trace_on(null,'SUMM');
5923: hr_utility.trace('l_pactid ' || l_pactid);
5924: hr_utility.trace('l_person_id '|| l_person_id);
5925: hr_utility.trace('l_emp_ref_no '|| l_emp_ref_no);
5926: hr_utility.trace('l_employer_name '|| l_employer_name);
5927: hr_utility.trace('opening the cursor');
5920: l_employee_number := get_assignment_number(p_assignment_action_id, true, l_person_id, l_emp_ref_no);
5921: l_loop_count := 0;
5922: -- hr_utility.trace_on(null,'SUMM');
5923: hr_utility.trace('l_pactid ' || l_pactid);
5924: hr_utility.trace('l_person_id '|| l_person_id);
5925: hr_utility.trace('l_emp_ref_no '|| l_emp_ref_no);
5926: hr_utility.trace('l_employer_name '|| l_employer_name);
5927: hr_utility.trace('opening the cursor');
5928: l_tot_col1 := null;
5921: l_loop_count := 0;
5922: -- hr_utility.trace_on(null,'SUMM');
5923: hr_utility.trace('l_pactid ' || l_pactid);
5924: hr_utility.trace('l_person_id '|| l_person_id);
5925: hr_utility.trace('l_emp_ref_no '|| l_emp_ref_no);
5926: hr_utility.trace('l_employer_name '|| l_employer_name);
5927: hr_utility.trace('opening the cursor');
5928: l_tot_col1 := null;
5929: l_tot_col2 := null;
5922: -- hr_utility.trace_on(null,'SUMM');
5923: hr_utility.trace('l_pactid ' || l_pactid);
5924: hr_utility.trace('l_person_id '|| l_person_id);
5925: hr_utility.trace('l_emp_ref_no '|| l_emp_ref_no);
5926: hr_utility.trace('l_employer_name '|| l_employer_name);
5927: hr_utility.trace('opening the cursor');
5928: l_tot_col1 := null;
5929: l_tot_col2 := null;
5930: l_tot_col3 := null;
5923: hr_utility.trace('l_pactid ' || l_pactid);
5924: hr_utility.trace('l_person_id '|| l_person_id);
5925: hr_utility.trace('l_emp_ref_no '|| l_emp_ref_no);
5926: hr_utility.trace('l_employer_name '|| l_employer_name);
5927: hr_utility.trace('opening the cursor');
5928: l_tot_col1 := null;
5929: l_tot_col2 := null;
5930: l_tot_col3 := null;
5931: l_prev_section := null;
5928: l_tot_col1 := null;
5929: l_tot_col2 := null;
5930: l_tot_col3 := null;
5931: l_prev_section := null;
5932: hr_utility.trace('l_prev_section ' || l_prev_section);
5933: dbms_lob.createtemporary(l_xfdf_str,false,DBMS_LOB.CALL);
5934: dbms_lob.open(l_xfdf_str,dbms_lob.lob_readwrite);
5935:
5936:
5942:
5943: l_odd_page := TRUE;
5944: l_line_count := 0;
5945: if l_odd_page then
5946: hr_utility.trace('Current page is odd page');
5947: else
5948: hr_utility.trace('Current page is even page');
5949: end if;
5950: if to_number(l_rep_run) < 2005
5944: l_line_count := 0;
5945: if l_odd_page then
5946: hr_utility.trace('Current page is odd page');
5947: else
5948: hr_utility.trace('Current page is even page');
5949: end if;
5950: if to_number(l_rep_run) < 2005
5951: then
5952: for summary_entries in csr_summary_entries(l_pactid,
5983: dbms_lob.writeAppend( l_xfdf_str, length(l_xfdf_intermetiate_var) ,l_xfdf_intermetiate_var );
5984: end if;
5985: -- add new line desc
5986: set_line_desc(summary_entries.SECTION_HEADING);
5987: hr_utility.trace('l_loop_count ' ||l_loop_count);
5988: l_prev_section := summary_entries.SECTION_HEADING;
5989: l_line_count := l_line_count + 2;
5990: end if;
5991: hr_utility.trace('l_prev_section ' || l_prev_section);
5987: hr_utility.trace('l_loop_count ' ||l_loop_count);
5988: l_prev_section := summary_entries.SECTION_HEADING;
5989: l_line_count := l_line_count + 2;
5990: end if;
5991: hr_utility.trace('l_prev_section ' || l_prev_section);
5992: hr_utility.trace('summary_entries.SECTION_HEADING ' || summary_entries.SECTION_HEADING);
5993: if l_prev_section = summary_entries.SECTION_HEADING
5994: then
5995: -- we just add the line details
5988: l_prev_section := summary_entries.SECTION_HEADING;
5989: l_line_count := l_line_count + 2;
5990: end if;
5991: hr_utility.trace('l_prev_section ' || l_prev_section);
5992: hr_utility.trace('summary_entries.SECTION_HEADING ' || summary_entries.SECTION_HEADING);
5993: if l_prev_section = summary_entries.SECTION_HEADING
5994: then
5995: -- we just add the line details
5996: add_detail_line(summary_entries.SECTION_HEADING,
6000: summary_entries.LINE_COL3,
6001: summary_entries.LINE_START_DATE,
6002: summary_entries.LINE_END_DATE);
6003: l_temp := get_desc_line(summary_entries.LINE_DETAIL);
6004: hr_utility.trace(summary_entries.LINE_DETAIL || ' : ' || l_temp);
6005: if (l_temp > 1) then
6006: l_line_count := l_line_count + (l_temp * g_desc_size);
6007: else
6008: l_line_count := l_line_count + 1;
6021: l_TOT_COL3 := nvl(l_TOT_COL3,0) + nvl(summary_entries.LINE_COL3,0);
6022: end if;
6023: end if;
6024: else
6025: hr_utility.trace('l_loop_count ' || l_loop_count);
6026: add_total_lines;
6027: l_line_count := l_line_count + 1;
6028: hr_utility.trace('adding new section heading and title');
6029: -- add new section heading and title
6024: else
6025: hr_utility.trace('l_loop_count ' || l_loop_count);
6026: add_total_lines;
6027: l_line_count := l_line_count + 1;
6028: hr_utility.trace('adding new section heading and title');
6029: -- add new section heading and title
6030: -- we do not add heading if the SECTION_HEADING is Other Items Non 1A
6031: -- and prev was Other Items
6032: if l_prev_section = 'Other Items' and summary_entries.SECTION_HEADING = 'Other Items Non 1A'
6051: summary_entries.LINE_COL3,
6052: summary_entries.LINE_START_DATE,
6053: summary_entries.LINE_END_DATE);
6054: l_temp := get_desc_line(summary_entries.LINE_DETAIL);
6055: hr_utility.trace(summary_entries.LINE_DETAIL || ' : ' || l_temp);
6056: if (l_temp > 1) then
6057: l_line_count := l_line_count + (l_temp * g_desc_size);
6058: else
6059: l_line_count := l_line_count + 1;
6125: end if;
6126: -- add new line desc
6127:
6128: set_line_desc(summary_entries.SECTION_HEADING);
6129: hr_utility.trace('l_loop_count ' ||l_loop_count);
6130: l_prev_section := summary_entries.SECTION_HEADING;
6131: l_line_count := l_line_count + 2;
6132: hr_utility.trace('Writing line_desc + header : ' || l_line_count);
6133: end if;
6128: set_line_desc(summary_entries.SECTION_HEADING);
6129: hr_utility.trace('l_loop_count ' ||l_loop_count);
6130: l_prev_section := summary_entries.SECTION_HEADING;
6131: l_line_count := l_line_count + 2;
6132: hr_utility.trace('Writing line_desc + header : ' || l_line_count);
6133: end if;
6134: hr_utility.trace('l_prev_section ' || l_prev_section);
6135: hr_utility.trace('summary_entries.SECTION_HEADING ' || summary_entries.SECTION_HEADING);
6136: if l_prev_section = summary_entries.SECTION_HEADING
6130: l_prev_section := summary_entries.SECTION_HEADING;
6131: l_line_count := l_line_count + 2;
6132: hr_utility.trace('Writing line_desc + header : ' || l_line_count);
6133: end if;
6134: hr_utility.trace('l_prev_section ' || l_prev_section);
6135: hr_utility.trace('summary_entries.SECTION_HEADING ' || summary_entries.SECTION_HEADING);
6136: if l_prev_section = summary_entries.SECTION_HEADING
6137: then
6138: -- we just add the line details
6131: l_line_count := l_line_count + 2;
6132: hr_utility.trace('Writing line_desc + header : ' || l_line_count);
6133: end if;
6134: hr_utility.trace('l_prev_section ' || l_prev_section);
6135: hr_utility.trace('summary_entries.SECTION_HEADING ' || summary_entries.SECTION_HEADING);
6136: if l_prev_section = summary_entries.SECTION_HEADING
6137: then
6138: -- we just add the line details
6139: add_detail_line(summary_entries.SECTION_HEADING,
6143: summary_entries.LINE_COL3,
6144: summary_entries.LINE_START_DATE,
6145: summary_entries.LINE_END_DATE);
6146: l_temp := get_desc_line(summary_entries.LINE_DETAIL);
6147: hr_utility.trace(summary_entries.LINE_DETAIL || ' : ' || l_temp);
6148: if (l_temp > 1) then
6149: l_line_count := l_line_count + (l_temp * g_desc_size);
6150: else
6151: l_line_count := l_line_count + 1;
6149: l_line_count := l_line_count + (l_temp * g_desc_size);
6150: else
6151: l_line_count := l_line_count + 1;
6152: end if;
6153: hr_utility.trace('Writing line details : ' || l_line_count);
6154: -- we need to sum the totals
6155: if summary_entries.SECTION_HEADING <> 'Shares'
6156: then
6157: if summary_entries.SECTION_HEADING ='Payments made on behalf of employee'
6166: l_TOT_COL3 := nvl(l_TOT_COL3,0) + nvl(summary_entries.LINE_COL3,0);
6167: end if;
6168: end if;
6169: else
6170: hr_utility.trace('l_loop_count ' || l_loop_count);
6171: add_total_lines;
6172: l_line_count := l_line_count + 1;
6173: hr_utility.trace('Writing total line : ' || l_line_count);
6174: hr_utility.trace('adding new section heading and title');
6169: else
6170: hr_utility.trace('l_loop_count ' || l_loop_count);
6171: add_total_lines;
6172: l_line_count := l_line_count + 1;
6173: hr_utility.trace('Writing total line : ' || l_line_count);
6174: hr_utility.trace('adding new section heading and title');
6175: -- add new section heading and title
6176: -- we do not add heading if the SECTION_HEADING is Other Items Non 1A
6177: -- and prev was Other Items
6170: hr_utility.trace('l_loop_count ' || l_loop_count);
6171: add_total_lines;
6172: l_line_count := l_line_count + 1;
6173: hr_utility.trace('Writing total line : ' || l_line_count);
6174: hr_utility.trace('adding new section heading and title');
6175: -- add new section heading and title
6176: -- we do not add heading if the SECTION_HEADING is Other Items Non 1A
6177: -- and prev was Other Items
6178: if l_prev_section = 'Other Items' and
6185: '
6186: '
6187: dbms_lob.writeAppend( l_xfdf_str, length(l_xfdf_intermetiate_var) ,l_xfdf_intermetiate_var );
6188: l_line_count := l_line_count + 1;
6189: hr_utility.trace('Writing header : ' || l_line_count);
6190: end if;
6191: -- add new line desc
6192: set_line_desc(summary_entries.SECTION_HEADING);
6193: l_line_count := l_line_count + 1;
6190: end if;
6191: -- add new line desc
6192: set_line_desc(summary_entries.SECTION_HEADING);
6193: l_line_count := l_line_count + 1;
6194: hr_utility.trace('Writing line_desc : ' || l_line_count);
6195: -- add the line details
6196: add_detail_line(summary_entries.SECTION_HEADING,
6197: summary_entries.LINE_DETAIL,
6198: summary_entries.LINE_COL1,
6200: summary_entries.LINE_COL3,
6201: summary_entries.LINE_START_DATE,
6202: summary_entries.LINE_END_DATE);
6203: l_temp := get_desc_line(summary_entries.LINE_DETAIL);
6204: hr_utility.trace(summary_entries.LINE_DETAIL || ' : ' || l_temp);
6205: if (l_temp > 1) then
6206: l_line_count := l_line_count + (l_temp * g_desc_size);
6207: else
6208: l_line_count := l_line_count + 1;
6206: l_line_count := l_line_count + (l_temp * g_desc_size);
6207: else
6208: l_line_count := l_line_count + 1;
6209: end if;
6210: hr_utility.trace('Writing line details : ' || l_line_count);
6211: -- resetting tot cols
6212: l_tot_col1 := null;
6213: l_tot_col2 := null;
6214: l_tot_col3 := null;
6229: end if;
6230: /* end if; */
6231: end if;
6232: if l_line_count > g_max_line then
6233: hr_utility.trace('Line more than max line : ' || l_line_count);
6234: if l_odd_page then
6235: hr_utility.trace('Current page is odd page, resetting it to even');
6236: else
6237: hr_utility.trace('Current page is even page, resetting it to odd');
6231: end if;
6232: if l_line_count > g_max_line then
6233: hr_utility.trace('Line more than max line : ' || l_line_count);
6234: if l_odd_page then
6235: hr_utility.trace('Current page is odd page, resetting it to even');
6236: else
6237: hr_utility.trace('Current page is even page, resetting it to odd');
6238: end if;
6239: l_odd_page := not(l_odd_page);
6233: hr_utility.trace('Line more than max line : ' || l_line_count);
6234: if l_odd_page then
6235: hr_utility.trace('Current page is odd page, resetting it to even');
6236: else
6237: hr_utility.trace('Current page is even page, resetting it to odd');
6238: end if;
6239: l_odd_page := not(l_odd_page);
6240: l_line_count := l_line_count - g_max_line;
6241: hr_utility.trace('New line count is : ' || l_line_count);
6237: hr_utility.trace('Current page is even page, resetting it to odd');
6238: end if;
6239: l_odd_page := not(l_odd_page);
6240: l_line_count := l_line_count - g_max_line;
6241: hr_utility.trace('New line count is : ' || l_line_count);
6242: end if;
6243: end loop;
6244: end if;
6245: if l_loop_count <> 0
6246: then
6247: -- add last total line
6248: add_total_lines;
6249: l_line_count := l_line_count + 1;
6250: hr_utility.trace('Writing total line : ' || l_line_count);
6251: hr_utility.trace('Final Line : ' || l_line_count);
6252: if l_odd_page then
6253: hr_utility.trace('Current page is Odd page');
6254: else
6247: -- add last total line
6248: add_total_lines;
6249: l_line_count := l_line_count + 1;
6250: hr_utility.trace('Writing total line : ' || l_line_count);
6251: hr_utility.trace('Final Line : ' || l_line_count);
6252: if l_odd_page then
6253: hr_utility.trace('Current page is Odd page');
6254: else
6255: hr_utility.trace('Current page is Even page');
6249: l_line_count := l_line_count + 1;
6250: hr_utility.trace('Writing total line : ' || l_line_count);
6251: hr_utility.trace('Final Line : ' || l_line_count);
6252: if l_odd_page then
6253: hr_utility.trace('Current page is Odd page');
6254: else
6255: hr_utility.trace('Current page is Even page');
6256: end if;
6257:
6251: hr_utility.trace('Final Line : ' || l_line_count);
6252: if l_odd_page then
6253: hr_utility.trace('Current page is Odd page');
6254: else
6255: hr_utility.trace('Current page is Even page');
6256: end if;
6257:
6258: if l_line_count > g_max_line then
6259: hr_utility.trace('Line more than max line : ' || l_line_count);
6255: hr_utility.trace('Current page is Even page');
6256: end if;
6257:
6258: if l_line_count > g_max_line then
6259: hr_utility.trace('Line more than max line : ' || l_line_count);
6260: if l_odd_page then
6261: hr_utility.trace('Current page is odd page, resetting it to even');
6262: else
6263: hr_utility.trace('Current page is even page, resetting it to odd');
6257:
6258: if l_line_count > g_max_line then
6259: hr_utility.trace('Line more than max line : ' || l_line_count);
6260: if l_odd_page then
6261: hr_utility.trace('Current page is odd page, resetting it to even');
6262: else
6263: hr_utility.trace('Current page is even page, resetting it to odd');
6264: end if;
6265: l_odd_page := not(l_odd_page);
6259: hr_utility.trace('Line more than max line : ' || l_line_count);
6260: if l_odd_page then
6261: hr_utility.trace('Current page is odd page, resetting it to even');
6262: else
6263: hr_utility.trace('Current page is even page, resetting it to odd');
6264: end if;
6265: l_odd_page := not(l_odd_page);
6266: l_line_count := l_line_count - g_max_line;
6267: hr_utility.trace('New line count is : ' || l_line_count);
6263: hr_utility.trace('Current page is even page, resetting it to odd');
6264: end if;
6265: l_odd_page := not(l_odd_page);
6266: l_line_count := l_line_count - g_max_line;
6267: hr_utility.trace('New line count is : ' || l_line_count);
6268: end if;
6269:
6270: if l_odd_page and p_print_Style = 'Double Sided Printing' then --bug 8241399
6271: -- If print option is two sided printing we are appending the dummy XML data
6268: end if;
6269:
6270: if l_odd_page and p_print_Style = 'Double Sided Printing' then --bug 8241399
6271: -- If print option is two sided printing we are appending the dummy XML data
6272: hr_utility.trace('Current page is Odd page');
6273: l_temp := ceil((g_max_line - l_line_count)/4) + 1;
6274: hr_utility.trace('Current line is : ' || l_line_count);
6275: hr_utility.trace('Writing more record (4 line / record) : ' || l_temp);
6276: for l_line_count in 0..l_temp loop
6270: if l_odd_page and p_print_Style = 'Double Sided Printing' then --bug 8241399
6271: -- If print option is two sided printing we are appending the dummy XML data
6272: hr_utility.trace('Current page is Odd page');
6273: l_temp := ceil((g_max_line - l_line_count)/4) + 1;
6274: hr_utility.trace('Current line is : ' || l_line_count);
6275: hr_utility.trace('Writing more record (4 line / record) : ' || l_temp);
6276: for l_line_count in 0..l_temp loop
6277: l_xfdf_intermetiate_var :=
6278: '
6271: -- If print option is two sided printing we are appending the dummy XML data
6272: hr_utility.trace('Current page is Odd page');
6273: l_temp := ceil((g_max_line - l_line_count)/4) + 1;
6274: hr_utility.trace('Current line is : ' || l_line_count);
6275: hr_utility.trace('Writing more record (4 line / record) : ' || l_temp);
6276: for l_line_count in 0..l_temp loop
6277: l_xfdf_intermetiate_var :=
6278: '
6279: '
6303: DBMS_LOB.CREATETEMPORARY(l_xfdf_blob_str,true);
6304: clob_to_blob(l_xfdf_str,l_xfdf_blob_str);
6305: dbms_lob.close(l_xfdf_str);
6306: dbms_lob.freetemporary(l_xfdf_str);
6307: hr_utility.trace('blob length '|| dbms_lob.getlength(l_xfdf_blob_str));
6308: -- Adding this causes error in xdo so commented this
6309: -- DBMS_LOB.FREETEMPORARY(l_xfdf_blob_str);
6310: --hr_utility.trace_off;
6311: return l_xfdf_blob_str;
6306: dbms_lob.freetemporary(l_xfdf_str);
6307: hr_utility.trace('blob length '|| dbms_lob.getlength(l_xfdf_blob_str));
6308: -- Adding this causes error in xdo so commented this
6309: -- DBMS_LOB.FREETEMPORARY(l_xfdf_blob_str);
6310: --hr_utility.trace_off;
6311: return l_xfdf_blob_str;
6312: end ;
6313: /*Added this function to generate pdf report for Address*/
6314: /*Bug No. 3201848*/