[Home] [Help]
252: --
253: l_archived_flag VARCHAR2(1) := 'Y';
254: l_proc VARCHAR2(50) := g_package||'CHK_ACTION_ARCHIVED';
255: BEGIN
256: hr_utility.set_location(l_proc, 10);
257: hr_utility.trace(l_proc||': p_assignment_id='|| p_assignment_id);
258: hr_utility.trace(l_proc||': p_vehicle_allocation_id='|| p_vehicle_allocation_id);
259: hr_utility.trace(l_proc||': p_allocation_start_date='|| fnd_date.date_to_displaydate(p_allocation_start_date));
260: hr_utility.trace(l_proc||': p_action_flag='|| p_action_flag);
253: l_archived_flag VARCHAR2(1) := 'Y';
254: l_proc VARCHAR2(50) := g_package||'CHK_ACTION_ARCHIVED';
255: BEGIN
256: hr_utility.set_location(l_proc, 10);
257: hr_utility.trace(l_proc||': p_assignment_id='|| p_assignment_id);
258: hr_utility.trace(l_proc||': p_vehicle_allocation_id='|| p_vehicle_allocation_id);
259: hr_utility.trace(l_proc||': p_allocation_start_date='|| fnd_date.date_to_displaydate(p_allocation_start_date));
260: hr_utility.trace(l_proc||': p_action_flag='|| p_action_flag);
261: --
254: l_proc VARCHAR2(50) := g_package||'CHK_ACTION_ARCHIVED';
255: BEGIN
256: hr_utility.set_location(l_proc, 10);
257: hr_utility.trace(l_proc||': p_assignment_id='|| p_assignment_id);
258: hr_utility.trace(l_proc||': p_vehicle_allocation_id='|| p_vehicle_allocation_id);
259: hr_utility.trace(l_proc||': p_allocation_start_date='|| fnd_date.date_to_displaydate(p_allocation_start_date));
260: hr_utility.trace(l_proc||': p_action_flag='|| p_action_flag);
261: --
262: OPEN c_chk_archive;
255: BEGIN
256: hr_utility.set_location(l_proc, 10);
257: hr_utility.trace(l_proc||': p_assignment_id='|| p_assignment_id);
258: hr_utility.trace(l_proc||': p_vehicle_allocation_id='|| p_vehicle_allocation_id);
259: hr_utility.trace(l_proc||': p_allocation_start_date='|| fnd_date.date_to_displaydate(p_allocation_start_date));
260: hr_utility.trace(l_proc||': p_action_flag='|| p_action_flag);
261: --
262: OPEN c_chk_archive;
263: FETCH c_chk_archive INTO chk_archive_rec;
256: hr_utility.set_location(l_proc, 10);
257: hr_utility.trace(l_proc||': p_assignment_id='|| p_assignment_id);
258: hr_utility.trace(l_proc||': p_vehicle_allocation_id='|| p_vehicle_allocation_id);
259: hr_utility.trace(l_proc||': p_allocation_start_date='|| fnd_date.date_to_displaydate(p_allocation_start_date));
260: hr_utility.trace(l_proc||': p_action_flag='|| p_action_flag);
261: --
262: OPEN c_chk_archive;
263: FETCH c_chk_archive INTO chk_archive_rec;
264: IF c_chk_archive%NOTFOUND THEN
261: --
262: OPEN c_chk_archive;
263: FETCH c_chk_archive INTO chk_archive_rec;
264: IF c_chk_archive%NOTFOUND THEN
265: hr_utility.set_location(l_proc, 20);
266: l_archived_flag := 'N';
267: END IF;
268: CLOSE c_chk_archive;
269: --
266: l_archived_flag := 'N';
267: END IF;
268: CLOSE c_chk_archive;
269: --
270: hr_utility.set_location(l_proc, 30);
271: RETURN l_archived_flag;
272: END chk_action_archived;
273:
274: --For Bug 6652235
305: --
306: l_archived_flag VARCHAR2(1) := 'Y';
307: l_proc VARCHAR2(50) := g_package||'CHK_ACTION_ARCHIVED_V2';
308: BEGIN
309: hr_utility.set_location(l_proc, 10);
310: hr_utility.trace(l_proc||': p_assignment_id='|| p_assignment_id);
311: hr_utility.trace(l_proc||': p_vehicle_allocation_id='|| p_vehicle_allocation_id);
312: hr_utility.trace(l_proc||': p_allocation_start_date='|| fnd_date.date_to_displaydate(p_allocation_start_date));
313: hr_utility.trace(l_proc||': p_action_flag='|| p_action_flag);
306: l_archived_flag VARCHAR2(1) := 'Y';
307: l_proc VARCHAR2(50) := g_package||'CHK_ACTION_ARCHIVED_V2';
308: BEGIN
309: hr_utility.set_location(l_proc, 10);
310: hr_utility.trace(l_proc||': p_assignment_id='|| p_assignment_id);
311: hr_utility.trace(l_proc||': p_vehicle_allocation_id='|| p_vehicle_allocation_id);
312: hr_utility.trace(l_proc||': p_allocation_start_date='|| fnd_date.date_to_displaydate(p_allocation_start_date));
313: hr_utility.trace(l_proc||': p_action_flag='|| p_action_flag);
314: --
307: l_proc VARCHAR2(50) := g_package||'CHK_ACTION_ARCHIVED_V2';
308: BEGIN
309: hr_utility.set_location(l_proc, 10);
310: hr_utility.trace(l_proc||': p_assignment_id='|| p_assignment_id);
311: hr_utility.trace(l_proc||': p_vehicle_allocation_id='|| p_vehicle_allocation_id);
312: hr_utility.trace(l_proc||': p_allocation_start_date='|| fnd_date.date_to_displaydate(p_allocation_start_date));
313: hr_utility.trace(l_proc||': p_action_flag='|| p_action_flag);
314: --
315: OPEN c_chk_archive;
308: BEGIN
309: hr_utility.set_location(l_proc, 10);
310: hr_utility.trace(l_proc||': p_assignment_id='|| p_assignment_id);
311: hr_utility.trace(l_proc||': p_vehicle_allocation_id='|| p_vehicle_allocation_id);
312: hr_utility.trace(l_proc||': p_allocation_start_date='|| fnd_date.date_to_displaydate(p_allocation_start_date));
313: hr_utility.trace(l_proc||': p_action_flag='|| p_action_flag);
314: --
315: OPEN c_chk_archive;
316: FETCH c_chk_archive INTO chk_archive_rec;
309: hr_utility.set_location(l_proc, 10);
310: hr_utility.trace(l_proc||': p_assignment_id='|| p_assignment_id);
311: hr_utility.trace(l_proc||': p_vehicle_allocation_id='|| p_vehicle_allocation_id);
312: hr_utility.trace(l_proc||': p_allocation_start_date='|| fnd_date.date_to_displaydate(p_allocation_start_date));
313: hr_utility.trace(l_proc||': p_action_flag='|| p_action_flag);
314: --
315: OPEN c_chk_archive;
316: FETCH c_chk_archive INTO chk_archive_rec;
317: IF c_chk_archive%NOTFOUND THEN
314: --
315: OPEN c_chk_archive;
316: FETCH c_chk_archive INTO chk_archive_rec;
317: IF c_chk_archive%NOTFOUND THEN
318: hr_utility.set_location(l_proc, 20);
319: l_archived_flag := 'N';
320: END IF;
321: CLOSE c_chk_archive;
322: --
319: l_archived_flag := 'N';
320: END IF;
321: CLOSE c_chk_archive;
322: --
323: hr_utility.set_location(l_proc, 30);
324: RETURN l_archived_flag;
325: END chk_action_archived_v2;
326:
327: -----------------------------------------------------
360: l_ovn pay_action_information.object_version_number%TYPE;
361: --
362: BEGIN
363: --
364: -- hr_utility.trace_on(null, 'RMAKHIJA');
365: hr_utility.set_location('Enering: '||l_proc, 10);
366: hr_utility.trace(l_proc||': payroll_action_id='||pactid);
367: --
368: -- Get Employer information
361: --
362: BEGIN
363: --
364: -- hr_utility.trace_on(null, 'RMAKHIJA');
365: hr_utility.set_location('Enering: '||l_proc, 10);
366: hr_utility.trace(l_proc||': payroll_action_id='||pactid);
367: --
368: -- Get Employer information
369: OPEN c_employer;
362: BEGIN
363: --
364: -- hr_utility.trace_on(null, 'RMAKHIJA');
365: hr_utility.set_location('Enering: '||l_proc, 10);
366: hr_utility.trace(l_proc||': payroll_action_id='||pactid);
367: --
368: -- Get Employer information
369: OPEN c_employer;
370: FETCH c_employer INTO employer_rec;
368: -- Get Employer information
369: OPEN c_employer;
370: FETCH c_employer INTO employer_rec;
371: IF c_employer%NOTFOUND THEN
372: hr_utility.set_location(l_proc, 20);
373: raise NO_DATA_FOUND;
374: END IF;
375: CLOSE c_employer;
376: --
375: CLOSE c_employer;
376: --
377: g_effective_date := employer_rec.effective_Date;
378: --
379: hr_utility.trace(l_proc||': employer_name='||employer_rec.employer_name);
380: hr_utility.trace(l_proc||': employer_address_line='||employer_rec.employer_address_line);
381: hr_utility.trace(l_proc||': tax_district_name='||employer_rec.tax_district_name);
382: hr_utility.trace(l_proc||': organization_id='||employer_rec.organization_id);
383: hr_utility.trace(l_proc||': effective_Date='||fnd_date.date_to_displaydate(employer_rec.effective_date));
376: --
377: g_effective_date := employer_rec.effective_Date;
378: --
379: hr_utility.trace(l_proc||': employer_name='||employer_rec.employer_name);
380: hr_utility.trace(l_proc||': employer_address_line='||employer_rec.employer_address_line);
381: hr_utility.trace(l_proc||': tax_district_name='||employer_rec.tax_district_name);
382: hr_utility.trace(l_proc||': organization_id='||employer_rec.organization_id);
383: hr_utility.trace(l_proc||': effective_Date='||fnd_date.date_to_displaydate(employer_rec.effective_date));
384: --
377: g_effective_date := employer_rec.effective_Date;
378: --
379: hr_utility.trace(l_proc||': employer_name='||employer_rec.employer_name);
380: hr_utility.trace(l_proc||': employer_address_line='||employer_rec.employer_address_line);
381: hr_utility.trace(l_proc||': tax_district_name='||employer_rec.tax_district_name);
382: hr_utility.trace(l_proc||': organization_id='||employer_rec.organization_id);
383: hr_utility.trace(l_proc||': effective_Date='||fnd_date.date_to_displaydate(employer_rec.effective_date));
384: --
385: pay_action_information_api.create_action_information (
378: --
379: hr_utility.trace(l_proc||': employer_name='||employer_rec.employer_name);
380: hr_utility.trace(l_proc||': employer_address_line='||employer_rec.employer_address_line);
381: hr_utility.trace(l_proc||': tax_district_name='||employer_rec.tax_district_name);
382: hr_utility.trace(l_proc||': organization_id='||employer_rec.organization_id);
383: hr_utility.trace(l_proc||': effective_Date='||fnd_date.date_to_displaydate(employer_rec.effective_date));
384: --
385: pay_action_information_api.create_action_information (
386: p_action_information_id => l_action_info_id
379: hr_utility.trace(l_proc||': employer_name='||employer_rec.employer_name);
380: hr_utility.trace(l_proc||': employer_address_line='||employer_rec.employer_address_line);
381: hr_utility.trace(l_proc||': tax_district_name='||employer_rec.tax_district_name);
382: hr_utility.trace(l_proc||': organization_id='||employer_rec.organization_id);
383: hr_utility.trace(l_proc||': effective_Date='||fnd_date.date_to_displaydate(employer_rec.effective_date));
384: --
385: pay_action_information_api.create_action_information (
386: p_action_information_id => l_action_info_id
387: , p_action_context_id => pactid
396: , p_action_information2 => upper(employer_rec.employer_name)
397: , p_action_information3 => upper(employer_rec.tax_district_name)
398: , p_action_information4 => upper(employer_rec.employer_address_line));
399: --
400: hr_utility.set_location(l_proc, 30);
401: --
402: sqlstr := 'select distinct person_id '||
403: 'from per_people_f ppf, '||
404: 'pay_payroll_actions ppa '||
406: 'and ppa.business_group_id = ppf.business_group_id '||
407: 'order by ppf.person_id';
408:
409: --
410: hr_utility.set_location('Leaving: '||l_proc, 100);
411: --
412: -- hr_utility.trace_off;
413: EXCEPTION
414: WHEN OTHERS THEN
408:
409: --
410: hr_utility.set_location('Leaving: '||l_proc, 100);
411: --
412: -- hr_utility.trace_off;
413: EXCEPTION
414: WHEN OTHERS THEN
415: -- Return cursor that selects no rows
416: sqlstr := 'select 1 '||
416: sqlstr := 'select 1 '||
417: '/* ERROR - Employer Details Fetch failed with: '||
418: sqlerrm(sqlcode)||' */ '||
419: 'from dual where to_char(:payroll_action_id) = dummy';
420: hr_utility.set_location(' Leaving: '||l_proc,110);
421: END range_cursor;
422:
423: --For Bug 6652235
424: -----------------------------------------------------
457: l_ovn pay_action_information.object_version_number%TYPE;
458: --
459: BEGIN
460: --
461: -- hr_utility.trace_on(null, 'RMAKHIJA');
462: hr_utility.set_location('Enering: '||l_proc, 10);
463: hr_utility.trace(l_proc||': payroll_action_id='||pactid);
464: --
465: -- Get Employer information
458: --
459: BEGIN
460: --
461: -- hr_utility.trace_on(null, 'RMAKHIJA');
462: hr_utility.set_location('Enering: '||l_proc, 10);
463: hr_utility.trace(l_proc||': payroll_action_id='||pactid);
464: --
465: -- Get Employer information
466: OPEN c_employer;
459: BEGIN
460: --
461: -- hr_utility.trace_on(null, 'RMAKHIJA');
462: hr_utility.set_location('Enering: '||l_proc, 10);
463: hr_utility.trace(l_proc||': payroll_action_id='||pactid);
464: --
465: -- Get Employer information
466: OPEN c_employer;
467: FETCH c_employer INTO employer_rec;
465: -- Get Employer information
466: OPEN c_employer;
467: FETCH c_employer INTO employer_rec;
468: IF c_employer%NOTFOUND THEN
469: hr_utility.set_location(l_proc, 20);
470: raise NO_DATA_FOUND;
471: END IF;
472: CLOSE c_employer;
473: --
472: CLOSE c_employer;
473: --
474: g_effective_date := employer_rec.effective_Date;
475: --
476: hr_utility.trace(l_proc||': employer_name='||employer_rec.employer_name);
477: hr_utility.trace(l_proc||': employer_address_line='||employer_rec.employer_address_line);
478: hr_utility.trace(l_proc||': tax_district_name='||employer_rec.tax_district_name);
479: hr_utility.trace(l_proc||': organization_id='||employer_rec.organization_id);
480: hr_utility.trace(l_proc||': effective_Date='||fnd_date.date_to_displaydate(employer_rec.effective_date));
473: --
474: g_effective_date := employer_rec.effective_Date;
475: --
476: hr_utility.trace(l_proc||': employer_name='||employer_rec.employer_name);
477: hr_utility.trace(l_proc||': employer_address_line='||employer_rec.employer_address_line);
478: hr_utility.trace(l_proc||': tax_district_name='||employer_rec.tax_district_name);
479: hr_utility.trace(l_proc||': organization_id='||employer_rec.organization_id);
480: hr_utility.trace(l_proc||': effective_Date='||fnd_date.date_to_displaydate(employer_rec.effective_date));
481: --
474: g_effective_date := employer_rec.effective_Date;
475: --
476: hr_utility.trace(l_proc||': employer_name='||employer_rec.employer_name);
477: hr_utility.trace(l_proc||': employer_address_line='||employer_rec.employer_address_line);
478: hr_utility.trace(l_proc||': tax_district_name='||employer_rec.tax_district_name);
479: hr_utility.trace(l_proc||': organization_id='||employer_rec.organization_id);
480: hr_utility.trace(l_proc||': effective_Date='||fnd_date.date_to_displaydate(employer_rec.effective_date));
481: --
482: pay_action_information_api.create_action_information (
475: --
476: hr_utility.trace(l_proc||': employer_name='||employer_rec.employer_name);
477: hr_utility.trace(l_proc||': employer_address_line='||employer_rec.employer_address_line);
478: hr_utility.trace(l_proc||': tax_district_name='||employer_rec.tax_district_name);
479: hr_utility.trace(l_proc||': organization_id='||employer_rec.organization_id);
480: hr_utility.trace(l_proc||': effective_Date='||fnd_date.date_to_displaydate(employer_rec.effective_date));
481: --
482: pay_action_information_api.create_action_information (
483: p_action_information_id => l_action_info_id
476: hr_utility.trace(l_proc||': employer_name='||employer_rec.employer_name);
477: hr_utility.trace(l_proc||': employer_address_line='||employer_rec.employer_address_line);
478: hr_utility.trace(l_proc||': tax_district_name='||employer_rec.tax_district_name);
479: hr_utility.trace(l_proc||': organization_id='||employer_rec.organization_id);
480: hr_utility.trace(l_proc||': effective_Date='||fnd_date.date_to_displaydate(employer_rec.effective_date));
481: --
482: pay_action_information_api.create_action_information (
483: p_action_information_id => l_action_info_id
484: , p_action_context_id => pactid
493: , p_action_information2 => upper(employer_rec.employer_name)
494: , p_action_information3 => upper(employer_rec.tax_district_name)
495: , p_action_information4 => upper(employer_rec.employer_address_line));
496: --
497: hr_utility.set_location(l_proc, 30);
498: --
499: sqlstr := 'select distinct person_id '||
500: 'from per_people_f ppf, '||
501: 'pay_payroll_actions ppa '||
503: 'and ppa.business_group_id = ppf.business_group_id '||
504: 'order by ppf.person_id';
505:
506: --
507: hr_utility.set_location('Leaving: '||l_proc, 100);
508: --
509: -- hr_utility.trace_off;
510: EXCEPTION
511: WHEN OTHERS THEN
505:
506: --
507: hr_utility.set_location('Leaving: '||l_proc, 100);
508: --
509: -- hr_utility.trace_off;
510: EXCEPTION
511: WHEN OTHERS THEN
512: -- Return cursor that selects no rows
513: sqlstr := 'select 1 '||
513: sqlstr := 'select 1 '||
514: '/* ERROR - Employer Details Fetch failed with: '||
515: sqlerrm(sqlcode)||' */ '||
516: 'from dual where to_char(:payroll_action_id) = dummy';
517: hr_utility.set_location(' Leaving: '||l_proc,110);
518: END range_code;
519:
520: -----------------------------------------------------
521: -- PROCEDURE CREATE_ASG_ACT --
583: l_create_assact_flag VARCHAR2(1);
584: l_action_flag VARCHAR2(1);
585: l_archived_flag VARCHAR2(1);
586: BEGIN
587: --hr_utility.trace_on(null, 'KTHAMPAN');
588: hr_utility.set_location('Entering '||l_proc, 10);
589: --
590: OPEN c_param_values;
591: FETCH c_param_values INTO g_payroll_id,
584: l_action_flag VARCHAR2(1);
585: l_archived_flag VARCHAR2(1);
586: BEGIN
587: --hr_utility.trace_on(null, 'KTHAMPAN');
588: hr_utility.set_location('Entering '||l_proc, 10);
589: --
590: OPEN c_param_values;
591: FETCH c_param_values INTO g_payroll_id,
592: g_tax_ref,
595: g_end_date,
596: g_business_group_id;
597: CLOSE c_param_values;
598: --
599: hr_utility.set_location(l_proc, 15);
600: hr_utility.trace(l_proc||': g_payroll_id='||g_payroll_id);
601: hr_utility.trace(l_proc||': g_tax_ref='||g_tax_ref);
602: hr_utility.trace(l_proc||': g_start_date='||fnd_date.date_to_displaydate(g_start_date));
603: hr_utility.trace(l_proc||': g_effective_date='||fnd_date.date_to_displaydate(g_effective_date));
596: g_business_group_id;
597: CLOSE c_param_values;
598: --
599: hr_utility.set_location(l_proc, 15);
600: hr_utility.trace(l_proc||': g_payroll_id='||g_payroll_id);
601: hr_utility.trace(l_proc||': g_tax_ref='||g_tax_ref);
602: hr_utility.trace(l_proc||': g_start_date='||fnd_date.date_to_displaydate(g_start_date));
603: hr_utility.trace(l_proc||': g_effective_date='||fnd_date.date_to_displaydate(g_effective_date));
604: hr_utility.trace(l_proc||': g_end_date='||fnd_date.date_to_displaydate(g_end_date));
597: CLOSE c_param_values;
598: --
599: hr_utility.set_location(l_proc, 15);
600: hr_utility.trace(l_proc||': g_payroll_id='||g_payroll_id);
601: hr_utility.trace(l_proc||': g_tax_ref='||g_tax_ref);
602: hr_utility.trace(l_proc||': g_start_date='||fnd_date.date_to_displaydate(g_start_date));
603: hr_utility.trace(l_proc||': g_effective_date='||fnd_date.date_to_displaydate(g_effective_date));
604: hr_utility.trace(l_proc||': g_end_date='||fnd_date.date_to_displaydate(g_end_date));
605: hr_utility.trace(l_proc||': g_business_group_id='||g_business_group_id);
598: --
599: hr_utility.set_location(l_proc, 15);
600: hr_utility.trace(l_proc||': g_payroll_id='||g_payroll_id);
601: hr_utility.trace(l_proc||': g_tax_ref='||g_tax_ref);
602: hr_utility.trace(l_proc||': g_start_date='||fnd_date.date_to_displaydate(g_start_date));
603: hr_utility.trace(l_proc||': g_effective_date='||fnd_date.date_to_displaydate(g_effective_date));
604: hr_utility.trace(l_proc||': g_end_date='||fnd_date.date_to_displaydate(g_end_date));
605: hr_utility.trace(l_proc||': g_business_group_id='||g_business_group_id);
606: For asg_rec IN c_asg
599: hr_utility.set_location(l_proc, 15);
600: hr_utility.trace(l_proc||': g_payroll_id='||g_payroll_id);
601: hr_utility.trace(l_proc||': g_tax_ref='||g_tax_ref);
602: hr_utility.trace(l_proc||': g_start_date='||fnd_date.date_to_displaydate(g_start_date));
603: hr_utility.trace(l_proc||': g_effective_date='||fnd_date.date_to_displaydate(g_effective_date));
604: hr_utility.trace(l_proc||': g_end_date='||fnd_date.date_to_displaydate(g_end_date));
605: hr_utility.trace(l_proc||': g_business_group_id='||g_business_group_id);
606: For asg_rec IN c_asg
607: LOOP
600: hr_utility.trace(l_proc||': g_payroll_id='||g_payroll_id);
601: hr_utility.trace(l_proc||': g_tax_ref='||g_tax_ref);
602: hr_utility.trace(l_proc||': g_start_date='||fnd_date.date_to_displaydate(g_start_date));
603: hr_utility.trace(l_proc||': g_effective_date='||fnd_date.date_to_displaydate(g_effective_date));
604: hr_utility.trace(l_proc||': g_end_date='||fnd_date.date_to_displaydate(g_end_date));
605: hr_utility.trace(l_proc||': g_business_group_id='||g_business_group_id);
606: For asg_rec IN c_asg
607: LOOP
608: -- Loop through all assignments
601: hr_utility.trace(l_proc||': g_tax_ref='||g_tax_ref);
602: hr_utility.trace(l_proc||': g_start_date='||fnd_date.date_to_displaydate(g_start_date));
603: hr_utility.trace(l_proc||': g_effective_date='||fnd_date.date_to_displaydate(g_effective_date));
604: hr_utility.trace(l_proc||': g_end_date='||fnd_date.date_to_displaydate(g_end_date));
605: hr_utility.trace(l_proc||': g_business_group_id='||g_business_group_id);
606: For asg_rec IN c_asg
607: LOOP
608: -- Loop through all assignments
609: hr_utility.set_location(l_proc, 20);
605: hr_utility.trace(l_proc||': g_business_group_id='||g_business_group_id);
606: For asg_rec IN c_asg
607: LOOP
608: -- Loop through all assignments
609: hr_utility.set_location(l_proc, 20);
610: hr_utility.trace(l_proc||': assignment_id='||asg_rec.assignment_id);
611: hr_utility.trace(l_proc||': asg_min_start_date='||fnd_date.date_to_displaydate(asg_rec.asg_min_start_date));
612: hr_utility.trace(l_proc||': asg_max_end_date='||fnd_date.date_to_displaydate(asg_rec.asg_max_end_date));
613: --
606: For asg_rec IN c_asg
607: LOOP
608: -- Loop through all assignments
609: hr_utility.set_location(l_proc, 20);
610: hr_utility.trace(l_proc||': assignment_id='||asg_rec.assignment_id);
611: hr_utility.trace(l_proc||': asg_min_start_date='||fnd_date.date_to_displaydate(asg_rec.asg_min_start_date));
612: hr_utility.trace(l_proc||': asg_max_end_date='||fnd_date.date_to_displaydate(asg_rec.asg_max_end_date));
613: --
614: l_create_assact_flag := 'N';
607: LOOP
608: -- Loop through all assignments
609: hr_utility.set_location(l_proc, 20);
610: hr_utility.trace(l_proc||': assignment_id='||asg_rec.assignment_id);
611: hr_utility.trace(l_proc||': asg_min_start_date='||fnd_date.date_to_displaydate(asg_rec.asg_min_start_date));
612: hr_utility.trace(l_proc||': asg_max_end_date='||fnd_date.date_to_displaydate(asg_rec.asg_max_end_date));
613: --
614: l_create_assact_flag := 'N';
615: --
608: -- Loop through all assignments
609: hr_utility.set_location(l_proc, 20);
610: hr_utility.trace(l_proc||': assignment_id='||asg_rec.assignment_id);
611: hr_utility.trace(l_proc||': asg_min_start_date='||fnd_date.date_to_displaydate(asg_rec.asg_min_start_date));
612: hr_utility.trace(l_proc||': asg_max_end_date='||fnd_date.date_to_displaydate(asg_rec.asg_max_end_date));
613: --
614: l_create_assact_flag := 'N';
615: --
616: FOR alc_rec IN c_allocations(asg_rec.assignment_id) LOOP
615: --
616: FOR alc_rec IN c_allocations(asg_rec.assignment_id) LOOP
617: -- Loop through all vehicle allocations
618: -- over the date range
619: hr_utility.set_location(l_proc, 30);
620: hr_utility.trace(l_proc||': vehicle_allocation_id='||alc_rec.vehicle_allocation_id);
621: hr_utility.trace(l_proc||': usage_type='||alc_rec.usage_type);
622: --
623: L_action_flag := 'X';
616: FOR alc_rec IN c_allocations(asg_rec.assignment_id) LOOP
617: -- Loop through all vehicle allocations
618: -- over the date range
619: hr_utility.set_location(l_proc, 30);
620: hr_utility.trace(l_proc||': vehicle_allocation_id='||alc_rec.vehicle_allocation_id);
621: hr_utility.trace(l_proc||': usage_type='||alc_rec.usage_type);
622: --
623: L_action_flag := 'X';
624: l_archived_flag := 'Y';
617: -- Loop through all vehicle allocations
618: -- over the date range
619: hr_utility.set_location(l_proc, 30);
620: hr_utility.trace(l_proc||': vehicle_allocation_id='||alc_rec.vehicle_allocation_id);
621: hr_utility.trace(l_proc||': usage_type='||alc_rec.usage_type);
622: --
623: L_action_flag := 'X';
624: l_archived_flag := 'Y';
625: --
626: OPEN c_alloc_dates(alc_rec.vehicle_allocation_id);
627: FETCH c_alloc_dates INTO alc_dates_rec;
628: CLOSE c_alloc_dates;
629: --
630: hr_utility.set_location(l_proc, 40);
631: hr_utility.trace(l_proc||': min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
632: hr_utility.trace(l_proc||': max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
633: --
634: -- Check whether vehicle was allocated or
627: FETCH c_alloc_dates INTO alc_dates_rec;
628: CLOSE c_alloc_dates;
629: --
630: hr_utility.set_location(l_proc, 40);
631: hr_utility.trace(l_proc||': min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
632: hr_utility.trace(l_proc||': max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
633: --
634: -- Check whether vehicle was allocated or
635: -- Withdrawn within the date range also
628: CLOSE c_alloc_dates;
629: --
630: hr_utility.set_location(l_proc, 40);
631: hr_utility.trace(l_proc||': min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
632: hr_utility.trace(l_proc||': max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
633: --
634: -- Check whether vehicle was allocated or
635: -- Withdrawn within the date range also
636: -- check that this allocation or withdrawal
650: OPEN c_first_asg(asg_rec.assignment_id, alc_dates_rec.min_start_date);
651: FETCH c_first_asg INTO first_asg_rec;
652: CLOSE c_first_asg;
653: --
654: hr_utility.set_location(l_proc, 50);
655: hr_utility.trace(l_proc||': First Assignment='|| first_asg_rec.assignment_id);
656: -- Get primary car allocation of the
657: -- first assignment of this person
658: -- in this tax ref
651: FETCH c_first_asg INTO first_asg_rec;
652: CLOSE c_first_asg;
653: --
654: hr_utility.set_location(l_proc, 50);
655: hr_utility.trace(l_proc||': First Assignment='|| first_asg_rec.assignment_id);
656: -- Get primary car allocation of the
657: -- first assignment of this person
658: -- in this tax ref
659: OPEN c_primary_car(first_asg_rec.assignment_id, alc_dates_rec.min_start_date);
659: OPEN c_primary_car(first_asg_rec.assignment_id, alc_dates_rec.min_start_date);
660: FETCH c_primary_car INTO primary_car_rec;
661: CLOSE c_primary_car;
662: --
663: hr_utility.set_location(l_proc, 60);
664: hr_utility.trace(l_proc||': Primary Car Allocation='|| primary_car_rec.vehicle_allocation_id);
665: --
666: IF primary_car_rec.vehicle_allocation_id = alc_rec.vehicle_allocation_id THEN
667: -- This is the first car allocation
660: FETCH c_primary_car INTO primary_car_rec;
661: CLOSE c_primary_car;
662: --
663: hr_utility.set_location(l_proc, 60);
664: hr_utility.trace(l_proc||': Primary Car Allocation='|| primary_car_rec.vehicle_allocation_id);
665: --
666: IF primary_car_rec.vehicle_allocation_id = alc_rec.vehicle_allocation_id THEN
667: -- This is the first car allocation
668: -- of this employee in this tax ref
675: ELSE
676: L_action_flag := 'N';
677: END IF;
678: --
679: hr_utility.set_location(l_proc, 70);
680: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
681: --
682: CLOSE c_prior_prim_car;
683: ELSE
676: L_action_flag := 'N';
677: END IF;
678: --
679: hr_utility.set_location(l_proc, 70);
680: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
681: --
682: CLOSE c_prior_prim_car;
683: ELSE
684: -- Not the first car of the employee
685: -- report this as new car
686: L_action_flag := 'N';
687: END IF;
688: --
689: hr_utility.set_location(l_proc, 80);
690: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
691: --
692: ELSE
693: -- Not a primary car therefore mark
686: L_action_flag := 'N';
687: END IF;
688: --
689: hr_utility.set_location(l_proc, 80);
690: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
691: --
692: ELSE
693: -- Not a primary car therefore mark
694: -- it as new car allocation action
694: -- it as new car allocation action
695: l_action_flag := 'N';
696: END IF;
697: --
698: hr_utility.set_location(l_proc, 90);
699: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
700: --
701: IF L_action_flag <> 'X' THEN
702: -- Check whether this allocation action
695: l_action_flag := 'N';
696: END IF;
697: --
698: hr_utility.set_location(l_proc, 90);
699: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
700: --
701: IF L_action_flag <> 'X' THEN
702: -- Check whether this allocation action
703: -- has been already archived
706: alc_rec.vehicle_allocation_id,
707: alc_dates_rec.min_start_date,
708: l_action_flag);
709: --
710: hr_utility.set_location(l_proc, 100);
711: hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
712: --
713: IF l_archived_flag = 'N' THEN
714: -- Action has not been archived already
707: alc_dates_rec.min_start_date,
708: l_action_flag);
709: --
710: hr_utility.set_location(l_proc, 100);
711: hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
712: --
713: IF l_archived_flag = 'N' THEN
714: -- Action has not been archived already
715: -- therefore create assignment action
736: OPEN c_first_asg(asg_rec.assignment_id, alc_dates_rec.max_end_date);
737: FETCH c_first_asg INTO first_asg_rec;
738: CLOSE c_first_asg;
739: --
740: hr_utility.set_location(l_proc, 50);
741: hr_utility.trace(l_proc||': First Assignment='|| first_asg_rec.assignment_id);
742: -- Get primary car allocation of the
743: -- first assignment of this person
744: -- in this tax ref
737: FETCH c_first_asg INTO first_asg_rec;
738: CLOSE c_first_asg;
739: --
740: hr_utility.set_location(l_proc, 50);
741: hr_utility.trace(l_proc||': First Assignment='|| first_asg_rec.assignment_id);
742: -- Get primary car allocation of the
743: -- first assignment of this person
744: -- in this tax ref
745: OPEN c_primary_car(first_asg_rec.assignment_id, alc_dates_rec.max_end_date);
745: OPEN c_primary_car(first_asg_rec.assignment_id, alc_dates_rec.max_end_date);
746: FETCH c_primary_car INTO primary_car_rec;
747: CLOSE c_primary_car;
748: --
749: hr_utility.set_location(l_proc, 60);
750: hr_utility.trace(l_proc||': Primary Car Allocation='|| primary_car_rec.vehicle_allocation_id);
751: --
752: IF primary_car_rec.vehicle_allocation_id = alc_rec.vehicle_allocation_id THEN
753: --
746: FETCH c_primary_car INTO primary_car_rec;
747: CLOSE c_primary_car;
748: --
749: hr_utility.set_location(l_proc, 60);
750: hr_utility.trace(l_proc||': Primary Car Allocation='|| primary_car_rec.vehicle_allocation_id);
751: --
752: IF primary_car_rec.vehicle_allocation_id = alc_rec.vehicle_allocation_id THEN
753: --
754: OPEN c_next_prim_car(asg_rec.assignment_id, Alc_dates_rec.max_end_date);
762: -- This is a withdrawal action
763: L_action_flag := 'W';
764: END IF;
765: --
766: hr_utility.set_location(l_proc, 70);
767: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
768: --
769: CLOSE c_next_prim_car;
770: ELSE
763: L_action_flag := 'W';
764: END IF;
765: --
766: hr_utility.set_location(l_proc, 70);
767: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
768: --
769: CLOSE c_next_prim_car;
770: ELSE
771: -- Not the first car of the employee
773: L_action_flag := 'W';
774: END IF;
775: --
776: --
777: hr_utility.set_location(l_proc, 80);
778: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
779: --
780: ELSE
781: -- Not a first car therefore mark
774: END IF;
775: --
776: --
777: hr_utility.set_location(l_proc, 80);
778: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
779: --
780: ELSE
781: -- Not a first car therefore mark
782: -- it as withdrawal car action
782: -- it as withdrawal car action
783: l_action_flag := 'W';
784: END IF;
785: --
786: hr_utility.set_location(l_proc, 90);
787: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
788: --
789: IF L_action_flag <> 'X' THEN
790: -- Check whether this allocation action
783: l_action_flag := 'W';
784: END IF;
785: --
786: hr_utility.set_location(l_proc, 90);
787: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
788: --
789: IF L_action_flag <> 'X' THEN
790: -- Check whether this allocation action
791: -- has been already archived
794: alc_rec.vehicle_allocation_id,
795: alc_dates_rec.min_start_date,
796: l_action_flag);
797: --
798: hr_utility.set_location(l_proc, 100);
799: hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
800: --
801: IF l_archived_flag = 'N' THEN
802: -- Action has not been archived already
795: alc_dates_rec.min_start_date,
796: l_action_flag);
797: --
798: hr_utility.set_location(l_proc, 100);
799: hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
800: --
801: IF l_archived_flag = 'N' THEN
802: -- Action has not been archived already
803: -- therefore create assignment action
811: -- Fuel type has changed therefore make sure
812: -- this change occured after the vehicle was
813: -- allocated to this assignment
814: --
815: hr_utility.set_location(l_proc, 110);
816: hr_utility.trace(l_proc||': effective_start_date='|| vehicle_changes_rec.effective_start_date);
817: hr_utility.trace(l_proc||': fuel_type='|| vehicle_changes_rec.fuel_type);
818: --
819: IF (vehicle_changes_rec.effective_start_date
812: -- this change occured after the vehicle was
813: -- allocated to this assignment
814: --
815: hr_utility.set_location(l_proc, 110);
816: hr_utility.trace(l_proc||': effective_start_date='|| vehicle_changes_rec.effective_start_date);
817: hr_utility.trace(l_proc||': fuel_type='|| vehicle_changes_rec.fuel_type);
818: --
819: IF (vehicle_changes_rec.effective_start_date
820: BETWEEN alc_dates_rec.min_start_date+1 AND alc_dates_rec.max_end_date) AND
813: -- allocated to this assignment
814: --
815: hr_utility.set_location(l_proc, 110);
816: hr_utility.trace(l_proc||': effective_start_date='|| vehicle_changes_rec.effective_start_date);
817: hr_utility.trace(l_proc||': fuel_type='|| vehicle_changes_rec.fuel_type);
818: --
819: IF (vehicle_changes_rec.effective_start_date
820: BETWEEN alc_dates_rec.min_start_date+1 AND alc_dates_rec.max_end_date) AND
821: (vehicle_changes_rec.effective_start_date
828: alc_rec.vehicle_allocation_id,
829: vehicle_changes_rec.effective_start_date,
830: 'F');
831: --
832: hr_utility.set_location(l_proc, 120);
833: hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
834: --
835: IF l_archived_flag = 'N' THEN
836: -- Action has not been archived already
829: vehicle_changes_rec.effective_start_date,
830: 'F');
831: --
832: hr_utility.set_location(l_proc, 120);
833: hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
834: --
835: IF l_archived_flag = 'N' THEN
836: -- Action has not been archived already
837: -- therefore create assignment action
840: END IF;
841: END IF;
842: END LOOP;
843: --
844: hr_utility.set_location(l_proc, 130);
845: --
846: --
847: L_action_flag := 'X';
848: l_archived_flag := 'Y';
857: -- Assignment has started on this payroll or tax ref during the
858: -- input date range and this car allocation was active on the
859: -- start date therefore check whether it is a transfer
860: -- from another tax ref
861: hr_utility.set_location(l_proc, 160);
862: l_previous_tax_ref := NULL;
863: --
864: OPEN c_tax_ref(asg_rec.assignment_id, asg_rec.asg_min_start_date-1);
865: FETCH c_tax_ref INTO l_previous_tax_ref;
864: OPEN c_tax_ref(asg_rec.assignment_id, asg_rec.asg_min_start_date-1);
865: FETCH c_tax_ref INTO l_previous_tax_ref;
866: CLOSE c_tax_ref;
867: --
868: hr_utility.set_location(l_proc, 170);
869: hr_utility.trace(l_proc||', l_previous_tax_ref='||l_previous_tax_ref);
870: --
871: IF l_previous_tax_ref is NOT NULL AND l_previous_tax_ref <> g_tax_ref THEN
872: -- This assignment has a different prior tax ref
865: FETCH c_tax_ref INTO l_previous_tax_ref;
866: CLOSE c_tax_ref;
867: --
868: hr_utility.set_location(l_proc, 170);
869: hr_utility.trace(l_proc||', l_previous_tax_ref='||l_previous_tax_ref);
870: --
871: IF l_previous_tax_ref is NOT NULL AND l_previous_tax_ref <> g_tax_ref THEN
872: -- This assignment has a different prior tax ref
873: -- therefore it should be reported as a new car
871: IF l_previous_tax_ref is NOT NULL AND l_previous_tax_ref <> g_tax_ref THEN
872: -- This assignment has a different prior tax ref
873: -- therefore it should be reported as a new car
874: -- allocation on this EDI message.
875: hr_utility.set_location(l_proc, 180);
876: l_action_flag := 'N';
877: --
878: -- Check whether this allocation action
879: -- has been already archived
882: alc_rec.vehicle_allocation_id,
883: asg_rec.asg_min_start_date,
884: l_action_flag);
885: --
886: hr_utility.set_location(l_proc, 190);
887: hr_utility.trace(l_proc||', l_archived_flag='||l_archived_flag);
888: --
889: IF l_archived_flag = 'N' THEN
890: -- Action has not been archived already
883: asg_rec.asg_min_start_date,
884: l_action_flag);
885: --
886: hr_utility.set_location(l_proc, 190);
887: hr_utility.trace(l_proc||', l_archived_flag='||l_archived_flag);
888: --
889: IF l_archived_flag = 'N' THEN
890: -- Action has not been archived already
891: -- therefore create assignment action
903: -- Assignment has ended on this payroll or tax ref during the
904: -- input date range and this car allocation was active on the
905: -- end date therefore check whether it is a transfer
906: -- to another tax ref
907: hr_utility.set_location(l_proc, 200);
908: l_next_tax_ref := NULL;
909: --
910: OPEN c_tax_ref(asg_rec.assignment_id, asg_rec.asg_max_end_date+1);
911: FETCH c_tax_ref INTO l_next_tax_ref;
910: OPEN c_tax_ref(asg_rec.assignment_id, asg_rec.asg_max_end_date+1);
911: FETCH c_tax_ref INTO l_next_tax_ref;
912: CLOSE c_tax_ref;
913: --
914: hr_utility.set_location(l_proc, 210);
915: hr_utility.trace(l_proc||', l_next_tax_ref='||l_next_tax_ref);
916: --
917: IF l_next_tax_ref is NOT NULL AND l_next_tax_ref <> g_tax_ref THEN
918: -- This assignment has a different tax ref after end date
911: FETCH c_tax_ref INTO l_next_tax_ref;
912: CLOSE c_tax_ref;
913: --
914: hr_utility.set_location(l_proc, 210);
915: hr_utility.trace(l_proc||', l_next_tax_ref='||l_next_tax_ref);
916: --
917: IF l_next_tax_ref is NOT NULL AND l_next_tax_ref <> g_tax_ref THEN
918: -- This assignment has a different tax ref after end date
919: -- therefore it should be reported as allocation withdrawal
917: IF l_next_tax_ref is NOT NULL AND l_next_tax_ref <> g_tax_ref THEN
918: -- This assignment has a different tax ref after end date
919: -- therefore it should be reported as allocation withdrawal
920: -- on this EDI message.
921: hr_utility.set_location(l_proc, 220);
922: l_action_flag := 'W';
923: --
924: -- Check whether this allocation action
925: -- has been already archived
928: alc_rec.vehicle_allocation_id,
929: asg_rec.asg_max_end_date,
930: l_action_flag);
931: --
932: hr_utility.set_location(l_proc, 230);
933: hr_utility.trace(l_proc||', l_archived_flag='||l_archived_flag);
934: --
935: IF l_archived_flag = 'N' THEN
936: -- Action has not been archived already
929: asg_rec.asg_max_end_date,
930: l_action_flag);
931: --
932: hr_utility.set_location(l_proc, 230);
933: hr_utility.trace(l_proc||', l_archived_flag='||l_archived_flag);
934: --
935: IF l_archived_flag = 'N' THEN
936: -- Action has not been archived already
937: -- therefore create assignment action
940: --
941: END IF;
942: END IF;
943: --
944: hr_utility.set_location(l_proc, 240);
945: --
946: hr_utility.set_location(l_proc, 250);
947: END LOOP;
948: --
942: END IF;
943: --
944: hr_utility.set_location(l_proc, 240);
945: --
946: hr_utility.set_location(l_proc, 250);
947: END LOOP;
948: --
949: hr_utility.set_location(l_proc, 260);
950: --
945: --
946: hr_utility.set_location(l_proc, 250);
947: END LOOP;
948: --
949: hr_utility.set_location(l_proc, 260);
950: --
951: -- If flag is set then create action
952: IF l_create_assact_flag = 'Y' THEN
953: --
954: SELECT pay_assignment_actions_s.nextval
955: INTO l_actid
956: FROM dual;
957:
958: hr_utility.set_location(l_proc, 270);
959: hr_utility.trace(l_proc||': l_actid='||l_actid);
960: hr_utility.trace(l_proc||': asg_rec.assignment_id='||asg_rec.assignment_id);
961: hr_utility.trace(l_proc||': pactid='||pactid);
962: hr_utility.trace(l_proc||': chunk='||chunk);
955: INTO l_actid
956: FROM dual;
957:
958: hr_utility.set_location(l_proc, 270);
959: hr_utility.trace(l_proc||': l_actid='||l_actid);
960: hr_utility.trace(l_proc||': asg_rec.assignment_id='||asg_rec.assignment_id);
961: hr_utility.trace(l_proc||': pactid='||pactid);
962: hr_utility.trace(l_proc||': chunk='||chunk);
963: --
956: FROM dual;
957:
958: hr_utility.set_location(l_proc, 270);
959: hr_utility.trace(l_proc||': l_actid='||l_actid);
960: hr_utility.trace(l_proc||': asg_rec.assignment_id='||asg_rec.assignment_id);
961: hr_utility.trace(l_proc||': pactid='||pactid);
962: hr_utility.trace(l_proc||': chunk='||chunk);
963: --
964: hr_nonrun_asact.insact(l_actid,
957:
958: hr_utility.set_location(l_proc, 270);
959: hr_utility.trace(l_proc||': l_actid='||l_actid);
960: hr_utility.trace(l_proc||': asg_rec.assignment_id='||asg_rec.assignment_id);
961: hr_utility.trace(l_proc||': pactid='||pactid);
962: hr_utility.trace(l_proc||': chunk='||chunk);
963: --
964: hr_nonrun_asact.insact(l_actid,
965: asg_rec.assignment_id,
958: hr_utility.set_location(l_proc, 270);
959: hr_utility.trace(l_proc||': l_actid='||l_actid);
960: hr_utility.trace(l_proc||': asg_rec.assignment_id='||asg_rec.assignment_id);
961: hr_utility.trace(l_proc||': pactid='||pactid);
962: hr_utility.trace(l_proc||': chunk='||chunk);
963: --
964: hr_nonrun_asact.insact(l_actid,
965: asg_rec.assignment_id,
966: pactid,
965: asg_rec.assignment_id,
966: pactid,
967: chunk, NULL);
968: --
969: hr_utility.set_location(l_proc, 280);
970: --
971: END IF;
972: END LOOP;
973: --
970: --
971: END IF;
972: END LOOP;
973: --
974: hr_utility.set_location('Leaving: '||l_proc,290);
975: -- hr_utility.trace_off;
976: EXCEPTION
977: WHEN OTHERS THEN
978: hr_utility.set_location('Leaving: '||l_proc,300);
971: END IF;
972: END LOOP;
973: --
974: hr_utility.set_location('Leaving: '||l_proc,290);
975: -- hr_utility.trace_off;
976: EXCEPTION
977: WHEN OTHERS THEN
978: hr_utility.set_location('Leaving: '||l_proc,300);
979: -- hr_utility.trace_off;
974: hr_utility.set_location('Leaving: '||l_proc,290);
975: -- hr_utility.trace_off;
976: EXCEPTION
977: WHEN OTHERS THEN
978: hr_utility.set_location('Leaving: '||l_proc,300);
979: -- hr_utility.trace_off;
980: raise;
981: END Create_asg_act;
982:
975: -- hr_utility.trace_off;
976: EXCEPTION
977: WHEN OTHERS THEN
978: hr_utility.set_location('Leaving: '||l_proc,300);
979: -- hr_utility.trace_off;
980: raise;
981: END Create_asg_act;
982:
983:
1048: l_create_assact_flag VARCHAR2(1);
1049: l_action_flag VARCHAR2(1);
1050: l_archived_flag VARCHAR2(1);
1051: BEGIN
1052: --hr_utility.trace_on(null, 'KTHAMPAN');
1053: hr_utility.set_location('Entering '||l_proc, 10);
1054: --
1055: OPEN c_param_values;
1056: FETCH c_param_values INTO g_payroll_id,
1049: l_action_flag VARCHAR2(1);
1050: l_archived_flag VARCHAR2(1);
1051: BEGIN
1052: --hr_utility.trace_on(null, 'KTHAMPAN');
1053: hr_utility.set_location('Entering '||l_proc, 10);
1054: --
1055: OPEN c_param_values;
1056: FETCH c_param_values INTO g_payroll_id,
1057: g_tax_ref,
1060: g_end_date,
1061: g_business_group_id;
1062: CLOSE c_param_values;
1063: --
1064: hr_utility.set_location(l_proc, 15);
1065: hr_utility.trace(l_proc||': g_payroll_id='||g_payroll_id);
1066: hr_utility.trace(l_proc||': g_tax_ref='||g_tax_ref);
1067: hr_utility.trace(l_proc||': g_start_date='||fnd_date.date_to_displaydate(g_start_date));
1068: hr_utility.trace(l_proc||': g_effective_date='||fnd_date.date_to_displaydate(g_effective_date));
1061: g_business_group_id;
1062: CLOSE c_param_values;
1063: --
1064: hr_utility.set_location(l_proc, 15);
1065: hr_utility.trace(l_proc||': g_payroll_id='||g_payroll_id);
1066: hr_utility.trace(l_proc||': g_tax_ref='||g_tax_ref);
1067: hr_utility.trace(l_proc||': g_start_date='||fnd_date.date_to_displaydate(g_start_date));
1068: hr_utility.trace(l_proc||': g_effective_date='||fnd_date.date_to_displaydate(g_effective_date));
1069: hr_utility.trace(l_proc||': g_end_date='||fnd_date.date_to_displaydate(g_end_date));
1062: CLOSE c_param_values;
1063: --
1064: hr_utility.set_location(l_proc, 15);
1065: hr_utility.trace(l_proc||': g_payroll_id='||g_payroll_id);
1066: hr_utility.trace(l_proc||': g_tax_ref='||g_tax_ref);
1067: hr_utility.trace(l_proc||': g_start_date='||fnd_date.date_to_displaydate(g_start_date));
1068: hr_utility.trace(l_proc||': g_effective_date='||fnd_date.date_to_displaydate(g_effective_date));
1069: hr_utility.trace(l_proc||': g_end_date='||fnd_date.date_to_displaydate(g_end_date));
1070: hr_utility.trace(l_proc||': g_business_group_id='||g_business_group_id);
1063: --
1064: hr_utility.set_location(l_proc, 15);
1065: hr_utility.trace(l_proc||': g_payroll_id='||g_payroll_id);
1066: hr_utility.trace(l_proc||': g_tax_ref='||g_tax_ref);
1067: hr_utility.trace(l_proc||': g_start_date='||fnd_date.date_to_displaydate(g_start_date));
1068: hr_utility.trace(l_proc||': g_effective_date='||fnd_date.date_to_displaydate(g_effective_date));
1069: hr_utility.trace(l_proc||': g_end_date='||fnd_date.date_to_displaydate(g_end_date));
1070: hr_utility.trace(l_proc||': g_business_group_id='||g_business_group_id);
1071: For asg_rec IN c_asg
1064: hr_utility.set_location(l_proc, 15);
1065: hr_utility.trace(l_proc||': g_payroll_id='||g_payroll_id);
1066: hr_utility.trace(l_proc||': g_tax_ref='||g_tax_ref);
1067: hr_utility.trace(l_proc||': g_start_date='||fnd_date.date_to_displaydate(g_start_date));
1068: hr_utility.trace(l_proc||': g_effective_date='||fnd_date.date_to_displaydate(g_effective_date));
1069: hr_utility.trace(l_proc||': g_end_date='||fnd_date.date_to_displaydate(g_end_date));
1070: hr_utility.trace(l_proc||': g_business_group_id='||g_business_group_id);
1071: For asg_rec IN c_asg
1072: LOOP
1065: hr_utility.trace(l_proc||': g_payroll_id='||g_payroll_id);
1066: hr_utility.trace(l_proc||': g_tax_ref='||g_tax_ref);
1067: hr_utility.trace(l_proc||': g_start_date='||fnd_date.date_to_displaydate(g_start_date));
1068: hr_utility.trace(l_proc||': g_effective_date='||fnd_date.date_to_displaydate(g_effective_date));
1069: hr_utility.trace(l_proc||': g_end_date='||fnd_date.date_to_displaydate(g_end_date));
1070: hr_utility.trace(l_proc||': g_business_group_id='||g_business_group_id);
1071: For asg_rec IN c_asg
1072: LOOP
1073: -- Loop through all assignments
1066: hr_utility.trace(l_proc||': g_tax_ref='||g_tax_ref);
1067: hr_utility.trace(l_proc||': g_start_date='||fnd_date.date_to_displaydate(g_start_date));
1068: hr_utility.trace(l_proc||': g_effective_date='||fnd_date.date_to_displaydate(g_effective_date));
1069: hr_utility.trace(l_proc||': g_end_date='||fnd_date.date_to_displaydate(g_end_date));
1070: hr_utility.trace(l_proc||': g_business_group_id='||g_business_group_id);
1071: For asg_rec IN c_asg
1072: LOOP
1073: -- Loop through all assignments
1074: hr_utility.set_location(l_proc, 20);
1070: hr_utility.trace(l_proc||': g_business_group_id='||g_business_group_id);
1071: For asg_rec IN c_asg
1072: LOOP
1073: -- Loop through all assignments
1074: hr_utility.set_location(l_proc, 20);
1075: hr_utility.trace(l_proc||': assignment_id='||asg_rec.assignment_id);
1076: hr_utility.trace(l_proc||': asg_min_start_date='||fnd_date.date_to_displaydate(asg_rec.asg_min_start_date));
1077: hr_utility.trace(l_proc||': asg_max_end_date='||fnd_date.date_to_displaydate(asg_rec.asg_max_end_date));
1078: --
1071: For asg_rec IN c_asg
1072: LOOP
1073: -- Loop through all assignments
1074: hr_utility.set_location(l_proc, 20);
1075: hr_utility.trace(l_proc||': assignment_id='||asg_rec.assignment_id);
1076: hr_utility.trace(l_proc||': asg_min_start_date='||fnd_date.date_to_displaydate(asg_rec.asg_min_start_date));
1077: hr_utility.trace(l_proc||': asg_max_end_date='||fnd_date.date_to_displaydate(asg_rec.asg_max_end_date));
1078: --
1079: l_create_assact_flag := 'N';
1072: LOOP
1073: -- Loop through all assignments
1074: hr_utility.set_location(l_proc, 20);
1075: hr_utility.trace(l_proc||': assignment_id='||asg_rec.assignment_id);
1076: hr_utility.trace(l_proc||': asg_min_start_date='||fnd_date.date_to_displaydate(asg_rec.asg_min_start_date));
1077: hr_utility.trace(l_proc||': asg_max_end_date='||fnd_date.date_to_displaydate(asg_rec.asg_max_end_date));
1078: --
1079: l_create_assact_flag := 'N';
1080: --
1073: -- Loop through all assignments
1074: hr_utility.set_location(l_proc, 20);
1075: hr_utility.trace(l_proc||': assignment_id='||asg_rec.assignment_id);
1076: hr_utility.trace(l_proc||': asg_min_start_date='||fnd_date.date_to_displaydate(asg_rec.asg_min_start_date));
1077: hr_utility.trace(l_proc||': asg_max_end_date='||fnd_date.date_to_displaydate(asg_rec.asg_max_end_date));
1078: --
1079: l_create_assact_flag := 'N';
1080: --
1081: FOR alc_rec IN c_allocations(asg_rec.assignment_id) LOOP
1080: --
1081: FOR alc_rec IN c_allocations(asg_rec.assignment_id) LOOP
1082: -- Loop through all vehicle allocations
1083: -- over the date range
1084: hr_utility.set_location(l_proc, 30);
1085: hr_utility.trace(l_proc||': vehicle_allocation_id='||alc_rec.vehicle_allocation_id);
1086: hr_utility.trace(l_proc||': usage_type='||alc_rec.usage_type);
1087: --
1088: L_action_flag := 'X';
1081: FOR alc_rec IN c_allocations(asg_rec.assignment_id) LOOP
1082: -- Loop through all vehicle allocations
1083: -- over the date range
1084: hr_utility.set_location(l_proc, 30);
1085: hr_utility.trace(l_proc||': vehicle_allocation_id='||alc_rec.vehicle_allocation_id);
1086: hr_utility.trace(l_proc||': usage_type='||alc_rec.usage_type);
1087: --
1088: L_action_flag := 'X';
1089: l_archived_flag := 'Y';
1082: -- Loop through all vehicle allocations
1083: -- over the date range
1084: hr_utility.set_location(l_proc, 30);
1085: hr_utility.trace(l_proc||': vehicle_allocation_id='||alc_rec.vehicle_allocation_id);
1086: hr_utility.trace(l_proc||': usage_type='||alc_rec.usage_type);
1087: --
1088: L_action_flag := 'X';
1089: l_archived_flag := 'Y';
1090: --
1091: OPEN c_alloc_dates(alc_rec.vehicle_allocation_id);
1092: FETCH c_alloc_dates INTO alc_dates_rec;
1093: CLOSE c_alloc_dates;
1094: --
1095: hr_utility.set_location(l_proc, 40);
1096: hr_utility.trace(l_proc||': min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
1097: hr_utility.trace(l_proc||': max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
1098: --
1099: -- Check whether vehicle was allocated or
1092: FETCH c_alloc_dates INTO alc_dates_rec;
1093: CLOSE c_alloc_dates;
1094: --
1095: hr_utility.set_location(l_proc, 40);
1096: hr_utility.trace(l_proc||': min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
1097: hr_utility.trace(l_proc||': max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
1098: --
1099: -- Check whether vehicle was allocated or
1100: -- Withdrawn within the date range also
1093: CLOSE c_alloc_dates;
1094: --
1095: hr_utility.set_location(l_proc, 40);
1096: hr_utility.trace(l_proc||': min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
1097: hr_utility.trace(l_proc||': max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
1098: --
1099: -- Check whether vehicle was allocated or
1100: -- Withdrawn within the date range also
1101: -- check that this allocation or withdrawal
1115: OPEN c_first_asg(asg_rec.assignment_id, alc_dates_rec.min_start_date);
1116: FETCH c_first_asg INTO first_asg_rec;
1117: CLOSE c_first_asg;
1118: --
1119: hr_utility.set_location(l_proc, 50);
1120: hr_utility.trace(l_proc||': First Assignment='|| first_asg_rec.assignment_id);
1121: -- Get primary car allocation of the
1122: -- first assignment of this person
1123: -- in this tax ref
1116: FETCH c_first_asg INTO first_asg_rec;
1117: CLOSE c_first_asg;
1118: --
1119: hr_utility.set_location(l_proc, 50);
1120: hr_utility.trace(l_proc||': First Assignment='|| first_asg_rec.assignment_id);
1121: -- Get primary car allocation of the
1122: -- first assignment of this person
1123: -- in this tax ref
1124: OPEN c_primary_car(first_asg_rec.assignment_id, alc_dates_rec.min_start_date);
1124: OPEN c_primary_car(first_asg_rec.assignment_id, alc_dates_rec.min_start_date);
1125: FETCH c_primary_car INTO primary_car_rec;
1126: CLOSE c_primary_car;
1127: --
1128: hr_utility.set_location(l_proc, 60);
1129: hr_utility.trace(l_proc||': Primary Car Allocation='|| primary_car_rec.vehicle_allocation_id);
1130: --
1131: IF primary_car_rec.vehicle_allocation_id = alc_rec.vehicle_allocation_id THEN
1132: -- This is the first car allocation
1125: FETCH c_primary_car INTO primary_car_rec;
1126: CLOSE c_primary_car;
1127: --
1128: hr_utility.set_location(l_proc, 60);
1129: hr_utility.trace(l_proc||': Primary Car Allocation='|| primary_car_rec.vehicle_allocation_id);
1130: --
1131: IF primary_car_rec.vehicle_allocation_id = alc_rec.vehicle_allocation_id THEN
1132: -- This is the first car allocation
1133: -- of this employee in this tax ref
1140: ELSE
1141: L_action_flag := 'N';
1142: END IF;
1143: --
1144: hr_utility.set_location(l_proc, 70);
1145: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
1146: --
1147: CLOSE c_prior_prim_car;
1148: ELSE
1141: L_action_flag := 'N';
1142: END IF;
1143: --
1144: hr_utility.set_location(l_proc, 70);
1145: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
1146: --
1147: CLOSE c_prior_prim_car;
1148: ELSE
1149: -- Not the first car of the employee
1150: -- report this as new car
1151: L_action_flag := 'N';
1152: END IF;
1153: --
1154: hr_utility.set_location(l_proc, 80);
1155: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
1156: --
1157: ELSE
1158: -- Not a primary car therefore mark
1151: L_action_flag := 'N';
1152: END IF;
1153: --
1154: hr_utility.set_location(l_proc, 80);
1155: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
1156: --
1157: ELSE
1158: -- Not a primary car therefore mark
1159: -- it as new car allocation action
1159: -- it as new car allocation action
1160: l_action_flag := 'N';
1161: END IF;
1162: --
1163: hr_utility.set_location(l_proc, 90);
1164: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
1165: --
1166: IF L_action_flag <> 'X' THEN
1167: -- Check whether this allocation action
1160: l_action_flag := 'N';
1161: END IF;
1162: --
1163: hr_utility.set_location(l_proc, 90);
1164: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
1165: --
1166: IF L_action_flag <> 'X' THEN
1167: -- Check whether this allocation action
1168: -- has been already archived
1171: alc_rec.vehicle_allocation_id,
1172: alc_dates_rec.min_start_date,
1173: l_action_flag);
1174: --
1175: hr_utility.set_location(l_proc, 100);
1176: hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
1177: --
1178: IF l_archived_flag = 'N' THEN
1179: -- Action has not been archived already
1172: alc_dates_rec.min_start_date,
1173: l_action_flag);
1174: --
1175: hr_utility.set_location(l_proc, 100);
1176: hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
1177: --
1178: IF l_archived_flag = 'N' THEN
1179: -- Action has not been archived already
1180: -- therefore create assignment action
1201: OPEN c_first_asg(asg_rec.assignment_id, alc_dates_rec.max_end_date);
1202: FETCH c_first_asg INTO first_asg_rec;
1203: CLOSE c_first_asg;
1204: --
1205: hr_utility.set_location(l_proc, 50);
1206: hr_utility.trace(l_proc||': First Assignment='|| first_asg_rec.assignment_id);
1207: -- Get primary car allocation of the
1208: -- first assignment of this person
1209: -- in this tax ref
1202: FETCH c_first_asg INTO first_asg_rec;
1203: CLOSE c_first_asg;
1204: --
1205: hr_utility.set_location(l_proc, 50);
1206: hr_utility.trace(l_proc||': First Assignment='|| first_asg_rec.assignment_id);
1207: -- Get primary car allocation of the
1208: -- first assignment of this person
1209: -- in this tax ref
1210: OPEN c_primary_car(first_asg_rec.assignment_id, alc_dates_rec.max_end_date);
1210: OPEN c_primary_car(first_asg_rec.assignment_id, alc_dates_rec.max_end_date);
1211: FETCH c_primary_car INTO primary_car_rec;
1212: CLOSE c_primary_car;
1213: --
1214: hr_utility.set_location(l_proc, 60);
1215: hr_utility.trace(l_proc||': Primary Car Allocation='|| primary_car_rec.vehicle_allocation_id);
1216: --
1217: IF primary_car_rec.vehicle_allocation_id = alc_rec.vehicle_allocation_id THEN
1218: --
1211: FETCH c_primary_car INTO primary_car_rec;
1212: CLOSE c_primary_car;
1213: --
1214: hr_utility.set_location(l_proc, 60);
1215: hr_utility.trace(l_proc||': Primary Car Allocation='|| primary_car_rec.vehicle_allocation_id);
1216: --
1217: IF primary_car_rec.vehicle_allocation_id = alc_rec.vehicle_allocation_id THEN
1218: --
1219: OPEN c_next_prim_car(asg_rec.assignment_id, Alc_dates_rec.max_end_date);
1227: -- This is a withdrawal action
1228: L_action_flag := 'W';
1229: END IF;
1230: --
1231: hr_utility.set_location(l_proc, 70);
1232: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
1233: --
1234: CLOSE c_next_prim_car;
1235: ELSE
1228: L_action_flag := 'W';
1229: END IF;
1230: --
1231: hr_utility.set_location(l_proc, 70);
1232: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
1233: --
1234: CLOSE c_next_prim_car;
1235: ELSE
1236: -- Not the first car of the employee
1238: L_action_flag := 'W';
1239: END IF;
1240: --
1241: --
1242: hr_utility.set_location(l_proc, 80);
1243: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
1244: --
1245: ELSE
1246: -- Not a first car therefore mark
1239: END IF;
1240: --
1241: --
1242: hr_utility.set_location(l_proc, 80);
1243: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
1244: --
1245: ELSE
1246: -- Not a first car therefore mark
1247: -- it as withdrawal car action
1247: -- it as withdrawal car action
1248: l_action_flag := 'W';
1249: END IF;
1250: --
1251: hr_utility.set_location(l_proc, 90);
1252: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
1253: --
1254: IF L_action_flag <> 'X' THEN
1255: -- Check whether this allocation action
1248: l_action_flag := 'W';
1249: END IF;
1250: --
1251: hr_utility.set_location(l_proc, 90);
1252: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
1253: --
1254: IF L_action_flag <> 'X' THEN
1255: -- Check whether this allocation action
1256: -- has been already archived
1259: alc_rec.vehicle_allocation_id,
1260: alc_dates_rec.min_start_date,
1261: l_action_flag);
1262: --
1263: hr_utility.set_location(l_proc, 100);
1264: hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
1265: --
1266: IF l_archived_flag = 'N' THEN
1267: -- Action has not been archived already
1260: alc_dates_rec.min_start_date,
1261: l_action_flag);
1262: --
1263: hr_utility.set_location(l_proc, 100);
1264: hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
1265: --
1266: IF l_archived_flag = 'N' THEN
1267: -- Action has not been archived already
1268: -- therefore create assignment action
1276: -- Fuel type has changed therefore make sure
1277: -- this change occured after the vehicle was
1278: -- allocated to this assignment
1279: --
1280: hr_utility.set_location(l_proc, 110);
1281: hr_utility.trace(l_proc||': effective_start_date='|| vehicle_changes_rec.effective_start_date);
1282: hr_utility.trace(l_proc||': fuel_type='|| vehicle_changes_rec.fuel_type);
1283: --
1284: IF (vehicle_changes_rec.effective_start_date
1277: -- this change occured after the vehicle was
1278: -- allocated to this assignment
1279: --
1280: hr_utility.set_location(l_proc, 110);
1281: hr_utility.trace(l_proc||': effective_start_date='|| vehicle_changes_rec.effective_start_date);
1282: hr_utility.trace(l_proc||': fuel_type='|| vehicle_changes_rec.fuel_type);
1283: --
1284: IF (vehicle_changes_rec.effective_start_date
1285: BETWEEN alc_dates_rec.min_start_date+1 AND alc_dates_rec.max_end_date) AND
1278: -- allocated to this assignment
1279: --
1280: hr_utility.set_location(l_proc, 110);
1281: hr_utility.trace(l_proc||': effective_start_date='|| vehicle_changes_rec.effective_start_date);
1282: hr_utility.trace(l_proc||': fuel_type='|| vehicle_changes_rec.fuel_type);
1283: --
1284: IF (vehicle_changes_rec.effective_start_date
1285: BETWEEN alc_dates_rec.min_start_date+1 AND alc_dates_rec.max_end_date) AND
1286: (vehicle_changes_rec.effective_start_date
1293: alc_rec.vehicle_allocation_id,
1294: vehicle_changes_rec.effective_start_date,
1295: 'F');
1296: --
1297: hr_utility.set_location(l_proc, 120);
1298: hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
1299: --
1300: IF l_archived_flag = 'N' THEN
1301: -- Action has not been archived already
1294: vehicle_changes_rec.effective_start_date,
1295: 'F');
1296: --
1297: hr_utility.set_location(l_proc, 120);
1298: hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
1299: --
1300: IF l_archived_flag = 'N' THEN
1301: -- Action has not been archived already
1302: -- therefore create assignment action
1305: END IF;
1306: END IF;
1307: END LOOP;
1308: --
1309: hr_utility.set_location(l_proc, 130);
1310: --
1311: --
1312: L_action_flag := 'X';
1313: l_archived_flag := 'Y';
1322: -- Assignment has started on this payroll or tax ref during the
1323: -- input date range and this car allocation was active on the
1324: -- start date therefore check whether it is a transfer
1325: -- from another tax ref
1326: hr_utility.set_location(l_proc, 160);
1327: l_previous_tax_ref := NULL;
1328: --
1329: OPEN c_tax_ref(asg_rec.assignment_id, asg_rec.asg_min_start_date-1);
1330: FETCH c_tax_ref INTO l_previous_tax_ref;
1329: OPEN c_tax_ref(asg_rec.assignment_id, asg_rec.asg_min_start_date-1);
1330: FETCH c_tax_ref INTO l_previous_tax_ref;
1331: CLOSE c_tax_ref;
1332: --
1333: hr_utility.set_location(l_proc, 170);
1334: hr_utility.trace(l_proc||', l_previous_tax_ref='||l_previous_tax_ref);
1335: --
1336: IF l_previous_tax_ref is NOT NULL AND l_previous_tax_ref <> g_tax_ref THEN
1337: -- This assignment has a different prior tax ref
1330: FETCH c_tax_ref INTO l_previous_tax_ref;
1331: CLOSE c_tax_ref;
1332: --
1333: hr_utility.set_location(l_proc, 170);
1334: hr_utility.trace(l_proc||', l_previous_tax_ref='||l_previous_tax_ref);
1335: --
1336: IF l_previous_tax_ref is NOT NULL AND l_previous_tax_ref <> g_tax_ref THEN
1337: -- This assignment has a different prior tax ref
1338: -- therefore it should be reported as a new car
1336: IF l_previous_tax_ref is NOT NULL AND l_previous_tax_ref <> g_tax_ref THEN
1337: -- This assignment has a different prior tax ref
1338: -- therefore it should be reported as a new car
1339: -- allocation on this EDI message.
1340: hr_utility.set_location(l_proc, 180);
1341: l_action_flag := 'N';
1342: --
1343: -- Check whether this allocation action
1344: -- has been already archived
1347: alc_rec.vehicle_allocation_id,
1348: asg_rec.asg_min_start_date,
1349: l_action_flag);
1350: --
1351: hr_utility.set_location(l_proc, 190);
1352: hr_utility.trace(l_proc||', l_archived_flag='||l_archived_flag);
1353: --
1354: IF l_archived_flag = 'N' THEN
1355: -- Action has not been archived already
1348: asg_rec.asg_min_start_date,
1349: l_action_flag);
1350: --
1351: hr_utility.set_location(l_proc, 190);
1352: hr_utility.trace(l_proc||', l_archived_flag='||l_archived_flag);
1353: --
1354: IF l_archived_flag = 'N' THEN
1355: -- Action has not been archived already
1356: -- therefore create assignment action
1368: -- Assignment has ended on this payroll or tax ref during the
1369: -- input date range and this car allocation was active on the
1370: -- end date therefore check whether it is a transfer
1371: -- to another tax ref
1372: hr_utility.set_location(l_proc, 200);
1373: l_next_tax_ref := NULL;
1374: --
1375: OPEN c_tax_ref(asg_rec.assignment_id, asg_rec.asg_max_end_date+1);
1376: FETCH c_tax_ref INTO l_next_tax_ref;
1375: OPEN c_tax_ref(asg_rec.assignment_id, asg_rec.asg_max_end_date+1);
1376: FETCH c_tax_ref INTO l_next_tax_ref;
1377: CLOSE c_tax_ref;
1378: --
1379: hr_utility.set_location(l_proc, 210);
1380: hr_utility.trace(l_proc||', l_next_tax_ref='||l_next_tax_ref);
1381: --
1382: IF l_next_tax_ref is NOT NULL AND l_next_tax_ref <> g_tax_ref THEN
1383: -- This assignment has a different tax ref after end date
1376: FETCH c_tax_ref INTO l_next_tax_ref;
1377: CLOSE c_tax_ref;
1378: --
1379: hr_utility.set_location(l_proc, 210);
1380: hr_utility.trace(l_proc||', l_next_tax_ref='||l_next_tax_ref);
1381: --
1382: IF l_next_tax_ref is NOT NULL AND l_next_tax_ref <> g_tax_ref THEN
1383: -- This assignment has a different tax ref after end date
1384: -- therefore it should be reported as allocation withdrawal
1382: IF l_next_tax_ref is NOT NULL AND l_next_tax_ref <> g_tax_ref THEN
1383: -- This assignment has a different tax ref after end date
1384: -- therefore it should be reported as allocation withdrawal
1385: -- on this EDI message.
1386: hr_utility.set_location(l_proc, 220);
1387: l_action_flag := 'W';
1388: --
1389: -- Check whether this allocation action
1390: -- has been already archived
1393: alc_rec.vehicle_allocation_id,
1394: asg_rec.asg_max_end_date,
1395: l_action_flag);
1396: --
1397: hr_utility.set_location(l_proc, 230);
1398: hr_utility.trace(l_proc||', l_archived_flag='||l_archived_flag);
1399: --
1400: IF l_archived_flag = 'N' THEN
1401: -- Action has not been archived already
1394: asg_rec.asg_max_end_date,
1395: l_action_flag);
1396: --
1397: hr_utility.set_location(l_proc, 230);
1398: hr_utility.trace(l_proc||', l_archived_flag='||l_archived_flag);
1399: --
1400: IF l_archived_flag = 'N' THEN
1401: -- Action has not been archived already
1402: -- therefore create assignment action
1405: --
1406: END IF;
1407: END IF;
1408: --
1409: hr_utility.set_location(l_proc, 240);
1410: --
1411: hr_utility.set_location(l_proc, 250);
1412: END LOOP;
1413: --
1407: END IF;
1408: --
1409: hr_utility.set_location(l_proc, 240);
1410: --
1411: hr_utility.set_location(l_proc, 250);
1412: END LOOP;
1413: --
1414: hr_utility.set_location(l_proc, 260);
1415: --
1410: --
1411: hr_utility.set_location(l_proc, 250);
1412: END LOOP;
1413: --
1414: hr_utility.set_location(l_proc, 260);
1415: --
1416: -- If flag is set then create action
1417: IF l_create_assact_flag = 'Y' THEN
1418: --
1419: SELECT pay_assignment_actions_s.nextval
1420: INTO l_actid
1421: FROM dual;
1422:
1423: hr_utility.set_location(l_proc, 270);
1424: hr_utility.trace(l_proc||': l_actid='||l_actid);
1425: hr_utility.trace(l_proc||': asg_rec.assignment_id='||asg_rec.assignment_id);
1426: hr_utility.trace(l_proc||': pactid='||pactid);
1427: hr_utility.trace(l_proc||': chunk='||chunk);
1420: INTO l_actid
1421: FROM dual;
1422:
1423: hr_utility.set_location(l_proc, 270);
1424: hr_utility.trace(l_proc||': l_actid='||l_actid);
1425: hr_utility.trace(l_proc||': asg_rec.assignment_id='||asg_rec.assignment_id);
1426: hr_utility.trace(l_proc||': pactid='||pactid);
1427: hr_utility.trace(l_proc||': chunk='||chunk);
1428: --
1421: FROM dual;
1422:
1423: hr_utility.set_location(l_proc, 270);
1424: hr_utility.trace(l_proc||': l_actid='||l_actid);
1425: hr_utility.trace(l_proc||': asg_rec.assignment_id='||asg_rec.assignment_id);
1426: hr_utility.trace(l_proc||': pactid='||pactid);
1427: hr_utility.trace(l_proc||': chunk='||chunk);
1428: --
1429: hr_nonrun_asact.insact(l_actid,
1422:
1423: hr_utility.set_location(l_proc, 270);
1424: hr_utility.trace(l_proc||': l_actid='||l_actid);
1425: hr_utility.trace(l_proc||': asg_rec.assignment_id='||asg_rec.assignment_id);
1426: hr_utility.trace(l_proc||': pactid='||pactid);
1427: hr_utility.trace(l_proc||': chunk='||chunk);
1428: --
1429: hr_nonrun_asact.insact(l_actid,
1430: asg_rec.assignment_id,
1423: hr_utility.set_location(l_proc, 270);
1424: hr_utility.trace(l_proc||': l_actid='||l_actid);
1425: hr_utility.trace(l_proc||': asg_rec.assignment_id='||asg_rec.assignment_id);
1426: hr_utility.trace(l_proc||': pactid='||pactid);
1427: hr_utility.trace(l_proc||': chunk='||chunk);
1428: --
1429: hr_nonrun_asact.insact(l_actid,
1430: asg_rec.assignment_id,
1431: pactid,
1430: asg_rec.assignment_id,
1431: pactid,
1432: chunk, NULL);
1433: --
1434: hr_utility.set_location(l_proc, 280);
1435: --
1436: END IF;
1437: END LOOP;
1438: --
1435: --
1436: END IF;
1437: END LOOP;
1438: --
1439: hr_utility.set_location('Leaving: '||l_proc,290);
1440: -- hr_utility.trace_off;
1441: EXCEPTION
1442: WHEN OTHERS THEN
1443: hr_utility.set_location('Leaving: '||l_proc,300);
1436: END IF;
1437: END LOOP;
1438: --
1439: hr_utility.set_location('Leaving: '||l_proc,290);
1440: -- hr_utility.trace_off;
1441: EXCEPTION
1442: WHEN OTHERS THEN
1443: hr_utility.set_location('Leaving: '||l_proc,300);
1444: -- hr_utility.trace_off;
1439: hr_utility.set_location('Leaving: '||l_proc,290);
1440: -- hr_utility.trace_off;
1441: EXCEPTION
1442: WHEN OTHERS THEN
1443: hr_utility.set_location('Leaving: '||l_proc,300);
1444: -- hr_utility.trace_off;
1445: raise;
1446: END Create_asg_act_v2;
1447:
1440: -- hr_utility.trace_off;
1441: EXCEPTION
1442: WHEN OTHERS THEN
1443: hr_utility.set_location('Leaving: '||l_proc,300);
1444: -- hr_utility.trace_off;
1445: raise;
1446: END Create_asg_act_v2;
1447:
1448: -----------------------------------------------------
1545: l_ovn PAY_ACTION_INFORMATION.OBJECT_VERSION_NUMBER%TYPE;
1546: l_proc CONSTANT VARCHAR2(50):= g_package||'archive_allocation_action';
1547: --
1548: BEGIN
1549: hr_utility.set_location('Entering '||l_proc, 10);
1550: hr_utility.trace('p_assignment_id='||p_assignment_id);
1551: hr_utility.trace('p_asg_act_id='||p_asg_act_id);
1552: hr_utility.trace('p_vehicle_allocation_id='||p_vehicle_allocation_id);
1553: hr_utility.trace('p_vehicle_repository_id='||p_vehicle_repository_id);
1546: l_proc CONSTANT VARCHAR2(50):= g_package||'archive_allocation_action';
1547: --
1548: BEGIN
1549: hr_utility.set_location('Entering '||l_proc, 10);
1550: hr_utility.trace('p_assignment_id='||p_assignment_id);
1551: hr_utility.trace('p_asg_act_id='||p_asg_act_id);
1552: hr_utility.trace('p_vehicle_allocation_id='||p_vehicle_allocation_id);
1553: hr_utility.trace('p_vehicle_repository_id='||p_vehicle_repository_id);
1554: hr_utility.trace('p_action_flag='||p_action_flag);
1547: --
1548: BEGIN
1549: hr_utility.set_location('Entering '||l_proc, 10);
1550: hr_utility.trace('p_assignment_id='||p_assignment_id);
1551: hr_utility.trace('p_asg_act_id='||p_asg_act_id);
1552: hr_utility.trace('p_vehicle_allocation_id='||p_vehicle_allocation_id);
1553: hr_utility.trace('p_vehicle_repository_id='||p_vehicle_repository_id);
1554: hr_utility.trace('p_action_flag='||p_action_flag);
1555: hr_utility.trace('p_fuel_type_change_date='||fnd_date.date_to_displaydate(p_fuel_type_change_date));
1548: BEGIN
1549: hr_utility.set_location('Entering '||l_proc, 10);
1550: hr_utility.trace('p_assignment_id='||p_assignment_id);
1551: hr_utility.trace('p_asg_act_id='||p_asg_act_id);
1552: hr_utility.trace('p_vehicle_allocation_id='||p_vehicle_allocation_id);
1553: hr_utility.trace('p_vehicle_repository_id='||p_vehicle_repository_id);
1554: hr_utility.trace('p_action_flag='||p_action_flag);
1555: hr_utility.trace('p_fuel_type_change_date='||fnd_date.date_to_displaydate(p_fuel_type_change_date));
1556: hr_utility.trace('p_allocation_start_date='||fnd_date.date_to_displaydate(p_allocation_start_date));
1549: hr_utility.set_location('Entering '||l_proc, 10);
1550: hr_utility.trace('p_assignment_id='||p_assignment_id);
1551: hr_utility.trace('p_asg_act_id='||p_asg_act_id);
1552: hr_utility.trace('p_vehicle_allocation_id='||p_vehicle_allocation_id);
1553: hr_utility.trace('p_vehicle_repository_id='||p_vehicle_repository_id);
1554: hr_utility.trace('p_action_flag='||p_action_flag);
1555: hr_utility.trace('p_fuel_type_change_date='||fnd_date.date_to_displaydate(p_fuel_type_change_date));
1556: hr_utility.trace('p_allocation_start_date='||fnd_date.date_to_displaydate(p_allocation_start_date));
1557: hr_utility.trace('p_allocation_end_date='||fnd_date.date_to_displaydate(p_allocation_end_date));
1550: hr_utility.trace('p_assignment_id='||p_assignment_id);
1551: hr_utility.trace('p_asg_act_id='||p_asg_act_id);
1552: hr_utility.trace('p_vehicle_allocation_id='||p_vehicle_allocation_id);
1553: hr_utility.trace('p_vehicle_repository_id='||p_vehicle_repository_id);
1554: hr_utility.trace('p_action_flag='||p_action_flag);
1555: hr_utility.trace('p_fuel_type_change_date='||fnd_date.date_to_displaydate(p_fuel_type_change_date));
1556: hr_utility.trace('p_allocation_start_date='||fnd_date.date_to_displaydate(p_allocation_start_date));
1557: hr_utility.trace('p_allocation_end_date='||fnd_date.date_to_displaydate(p_allocation_end_date));
1558: -- Get data as of the action date within
1551: hr_utility.trace('p_asg_act_id='||p_asg_act_id);
1552: hr_utility.trace('p_vehicle_allocation_id='||p_vehicle_allocation_id);
1553: hr_utility.trace('p_vehicle_repository_id='||p_vehicle_repository_id);
1554: hr_utility.trace('p_action_flag='||p_action_flag);
1555: hr_utility.trace('p_fuel_type_change_date='||fnd_date.date_to_displaydate(p_fuel_type_change_date));
1556: hr_utility.trace('p_allocation_start_date='||fnd_date.date_to_displaydate(p_allocation_start_date));
1557: hr_utility.trace('p_allocation_end_date='||fnd_date.date_to_displaydate(p_allocation_end_date));
1558: -- Get data as of the action date within
1559: -- the date range, effective date is:
1552: hr_utility.trace('p_vehicle_allocation_id='||p_vehicle_allocation_id);
1553: hr_utility.trace('p_vehicle_repository_id='||p_vehicle_repository_id);
1554: hr_utility.trace('p_action_flag='||p_action_flag);
1555: hr_utility.trace('p_fuel_type_change_date='||fnd_date.date_to_displaydate(p_fuel_type_change_date));
1556: hr_utility.trace('p_allocation_start_date='||fnd_date.date_to_displaydate(p_allocation_start_date));
1557: hr_utility.trace('p_allocation_end_date='||fnd_date.date_to_displaydate(p_allocation_end_date));
1558: -- Get data as of the action date within
1559: -- the date range, effective date is:
1560: IF p_action_flag = 'F' THEN
1553: hr_utility.trace('p_vehicle_repository_id='||p_vehicle_repository_id);
1554: hr_utility.trace('p_action_flag='||p_action_flag);
1555: hr_utility.trace('p_fuel_type_change_date='||fnd_date.date_to_displaydate(p_fuel_type_change_date));
1556: hr_utility.trace('p_allocation_start_date='||fnd_date.date_to_displaydate(p_allocation_start_date));
1557: hr_utility.trace('p_allocation_end_date='||fnd_date.date_to_displaydate(p_allocation_end_date));
1558: -- Get data as of the action date within
1559: -- the date range, effective date is:
1560: IF p_action_flag = 'F' THEN
1561: -- Get data as of the fuel type change date
1564: l_eff_date := p_allocation_start_date;
1565: ELSIF p_action_flag = 'W' THEN
1566: l_eff_date := p_allocation_end_date;
1567: END IF;
1568: hr_utility.set_location(l_proc, 20);
1569: hr_utility.trace('l_eff_date='||fnd_date.date_to_displaydate(l_eff_date));
1570: --
1571: -- Get person details
1572: OPEN c_person(p_assignment_id);
1565: ELSIF p_action_flag = 'W' THEN
1566: l_eff_date := p_allocation_end_date;
1567: END IF;
1568: hr_utility.set_location(l_proc, 20);
1569: hr_utility.trace('l_eff_date='||fnd_date.date_to_displaydate(l_eff_date));
1570: --
1571: -- Get person details
1572: OPEN c_person(p_assignment_id);
1573: FETCH c_person INTO person_rec;
1572: OPEN c_person(p_assignment_id);
1573: FETCH c_person INTO person_rec;
1574: CLOSE c_person;
1575: --
1576: hr_utility.set_location(l_proc, 30);
1577: -- Get Address Details
1578: OPEN c_addr(person_rec.person_id);
1579: FETCH c_addr INTO addr_rec;
1580: CLOSE c_addr;
1578: OPEN c_addr(person_rec.person_id);
1579: FETCH c_addr INTO addr_rec;
1580: CLOSE c_addr;
1581: --
1582: hr_utility.set_location(l_proc, 40);
1583: -- Get Allocation details
1584: OPEN c_alloc;
1585: FETCH c_alloc INTO alloc_rec;
1586: CLOSE c_alloc;
1584: OPEN c_alloc;
1585: FETCH c_alloc INTO alloc_rec;
1586: CLOSE c_alloc;
1587: --
1588: hr_utility.set_location(l_proc, 50);
1589: -- Get Car details
1590: OPEN c_car;
1591: FETCH c_car INTO car_rec;
1592: CLOSE c_car;
1590: OPEN c_car;
1591: FETCH c_car INTO car_rec;
1592: CLOSE c_car;
1593: --
1594: hr_utility.set_location(l_proc, 60);
1595: ---------------------------------------------
1596: -- Archive Person and Address details --
1597: ---------------------------------------------
1598: hr_utility.trace('Person ID : ' || person_rec.person_id);
1594: hr_utility.set_location(l_proc, 60);
1595: ---------------------------------------------
1596: -- Archive Person and Address details --
1597: ---------------------------------------------
1598: hr_utility.trace('Person ID : ' || person_rec.person_id);
1599: hr_utility.trace('Last Name : ' || person_rec.last_name);
1600: hr_utility.trace('First Nme : ' || person_rec.first_name);
1601: hr_utility.trace('Title : ' || person_rec.title);
1602: hr_utility.trace('NINO : ' || person_rec.national_identifier);
1595: ---------------------------------------------
1596: -- Archive Person and Address details --
1597: ---------------------------------------------
1598: hr_utility.trace('Person ID : ' || person_rec.person_id);
1599: hr_utility.trace('Last Name : ' || person_rec.last_name);
1600: hr_utility.trace('First Nme : ' || person_rec.first_name);
1601: hr_utility.trace('Title : ' || person_rec.title);
1602: hr_utility.trace('NINO : ' || person_rec.national_identifier);
1603: hr_utility.trace('ADDR1 : ' || addr_rec.address_line1);
1596: -- Archive Person and Address details --
1597: ---------------------------------------------
1598: hr_utility.trace('Person ID : ' || person_rec.person_id);
1599: hr_utility.trace('Last Name : ' || person_rec.last_name);
1600: hr_utility.trace('First Nme : ' || person_rec.first_name);
1601: hr_utility.trace('Title : ' || person_rec.title);
1602: hr_utility.trace('NINO : ' || person_rec.national_identifier);
1603: hr_utility.trace('ADDR1 : ' || addr_rec.address_line1);
1604: hr_utility.trace('ADDR1 : ' || addr_rec.address_line2);
1597: ---------------------------------------------
1598: hr_utility.trace('Person ID : ' || person_rec.person_id);
1599: hr_utility.trace('Last Name : ' || person_rec.last_name);
1600: hr_utility.trace('First Nme : ' || person_rec.first_name);
1601: hr_utility.trace('Title : ' || person_rec.title);
1602: hr_utility.trace('NINO : ' || person_rec.national_identifier);
1603: hr_utility.trace('ADDR1 : ' || addr_rec.address_line1);
1604: hr_utility.trace('ADDR1 : ' || addr_rec.address_line2);
1605: hr_utility.trace('ADDR1 : ' || addr_rec.address_line3);
1598: hr_utility.trace('Person ID : ' || person_rec.person_id);
1599: hr_utility.trace('Last Name : ' || person_rec.last_name);
1600: hr_utility.trace('First Nme : ' || person_rec.first_name);
1601: hr_utility.trace('Title : ' || person_rec.title);
1602: hr_utility.trace('NINO : ' || person_rec.national_identifier);
1603: hr_utility.trace('ADDR1 : ' || addr_rec.address_line1);
1604: hr_utility.trace('ADDR1 : ' || addr_rec.address_line2);
1605: hr_utility.trace('ADDR1 : ' || addr_rec.address_line3);
1606: hr_utility.trace('City/Town : ' || addr_rec.town_or_city);
1599: hr_utility.trace('Last Name : ' || person_rec.last_name);
1600: hr_utility.trace('First Nme : ' || person_rec.first_name);
1601: hr_utility.trace('Title : ' || person_rec.title);
1602: hr_utility.trace('NINO : ' || person_rec.national_identifier);
1603: hr_utility.trace('ADDR1 : ' || addr_rec.address_line1);
1604: hr_utility.trace('ADDR1 : ' || addr_rec.address_line2);
1605: hr_utility.trace('ADDR1 : ' || addr_rec.address_line3);
1606: hr_utility.trace('City/Town : ' || addr_rec.town_or_city);
1607: hr_utility.trace('Regioin_1 : ' || addr_rec.region_1);
1600: hr_utility.trace('First Nme : ' || person_rec.first_name);
1601: hr_utility.trace('Title : ' || person_rec.title);
1602: hr_utility.trace('NINO : ' || person_rec.national_identifier);
1603: hr_utility.trace('ADDR1 : ' || addr_rec.address_line1);
1604: hr_utility.trace('ADDR1 : ' || addr_rec.address_line2);
1605: hr_utility.trace('ADDR1 : ' || addr_rec.address_line3);
1606: hr_utility.trace('City/Town : ' || addr_rec.town_or_city);
1607: hr_utility.trace('Regioin_1 : ' || addr_rec.region_1);
1608: hr_utility.trace('Postal : ' || addr_rec.postal_code);
1601: hr_utility.trace('Title : ' || person_rec.title);
1602: hr_utility.trace('NINO : ' || person_rec.national_identifier);
1603: hr_utility.trace('ADDR1 : ' || addr_rec.address_line1);
1604: hr_utility.trace('ADDR1 : ' || addr_rec.address_line2);
1605: hr_utility.trace('ADDR1 : ' || addr_rec.address_line3);
1606: hr_utility.trace('City/Town : ' || addr_rec.town_or_city);
1607: hr_utility.trace('Regioin_1 : ' || addr_rec.region_1);
1608: hr_utility.trace('Postal : ' || addr_rec.postal_code);
1609: hr_utility.trace('DOB : ' || fnd_date.date_to_canonical(person_rec.date_of_birth)); --For Bug 6652235
1602: hr_utility.trace('NINO : ' || person_rec.national_identifier);
1603: hr_utility.trace('ADDR1 : ' || addr_rec.address_line1);
1604: hr_utility.trace('ADDR1 : ' || addr_rec.address_line2);
1605: hr_utility.trace('ADDR1 : ' || addr_rec.address_line3);
1606: hr_utility.trace('City/Town : ' || addr_rec.town_or_city);
1607: hr_utility.trace('Regioin_1 : ' || addr_rec.region_1);
1608: hr_utility.trace('Postal : ' || addr_rec.postal_code);
1609: hr_utility.trace('DOB : ' || fnd_date.date_to_canonical(person_rec.date_of_birth)); --For Bug 6652235
1610: hr_utility.trace('Gender : ' || person_rec.sex); --For Bug 6652235
1603: hr_utility.trace('ADDR1 : ' || addr_rec.address_line1);
1604: hr_utility.trace('ADDR1 : ' || addr_rec.address_line2);
1605: hr_utility.trace('ADDR1 : ' || addr_rec.address_line3);
1606: hr_utility.trace('City/Town : ' || addr_rec.town_or_city);
1607: hr_utility.trace('Regioin_1 : ' || addr_rec.region_1);
1608: hr_utility.trace('Postal : ' || addr_rec.postal_code);
1609: hr_utility.trace('DOB : ' || fnd_date.date_to_canonical(person_rec.date_of_birth)); --For Bug 6652235
1610: hr_utility.trace('Gender : ' || person_rec.sex); --For Bug 6652235
1611:
1604: hr_utility.trace('ADDR1 : ' || addr_rec.address_line2);
1605: hr_utility.trace('ADDR1 : ' || addr_rec.address_line3);
1606: hr_utility.trace('City/Town : ' || addr_rec.town_or_city);
1607: hr_utility.trace('Regioin_1 : ' || addr_rec.region_1);
1608: hr_utility.trace('Postal : ' || addr_rec.postal_code);
1609: hr_utility.trace('DOB : ' || fnd_date.date_to_canonical(person_rec.date_of_birth)); --For Bug 6652235
1610: hr_utility.trace('Gender : ' || person_rec.sex); --For Bug 6652235
1611:
1612: pay_action_information_api.create_action_information (
1605: hr_utility.trace('ADDR1 : ' || addr_rec.address_line3);
1606: hr_utility.trace('City/Town : ' || addr_rec.town_or_city);
1607: hr_utility.trace('Regioin_1 : ' || addr_rec.region_1);
1608: hr_utility.trace('Postal : ' || addr_rec.postal_code);
1609: hr_utility.trace('DOB : ' || fnd_date.date_to_canonical(person_rec.date_of_birth)); --For Bug 6652235
1610: hr_utility.trace('Gender : ' || person_rec.sex); --For Bug 6652235
1611:
1612: pay_action_information_api.create_action_information (
1613: p_action_information_id => l_action_info_id
1606: hr_utility.trace('City/Town : ' || addr_rec.town_or_city);
1607: hr_utility.trace('Regioin_1 : ' || addr_rec.region_1);
1608: hr_utility.trace('Postal : ' || addr_rec.postal_code);
1609: hr_utility.trace('DOB : ' || fnd_date.date_to_canonical(person_rec.date_of_birth)); --For Bug 6652235
1610: hr_utility.trace('Gender : ' || person_rec.sex); --For Bug 6652235
1611:
1612: pay_action_information_api.create_action_information (
1613: p_action_information_id => l_action_info_id
1614: , p_action_context_id => p_asg_act_id
1632: , p_action_information11 => upper(addr_rec.postal_code)
1633: , p_action_information12 => fnd_date.date_to_canonical(person_rec.date_of_birth) --For Bug 6652235
1634: , p_action_information13 => upper(person_rec.sex)); --For Bug 6652235
1635: --
1636: hr_utility.set_location(l_proc, 70);
1637: ---------------------------------------------
1638: -- Archive Vehicle Allocation Details --
1639: ---------------------------------------------
1640: IF g_effective_date <
1651: l_tax_year_start := fnd_date.canonical_to_date(
1652: to_char(g_effective_date, 'YYYY')||'04/06');
1653: END IF;
1654: --
1655: hr_utility.set_location(l_proc, 80);
1656: --
1657: IF p_allocation_start_date >= l_tax_year_start THEN
1658: -- Car was first allocated in this tax year
1659: -- Archive Date Car First Available
1661: ELSE
1662: l_date_first_avail := NULL;
1663: END IF;
1664: --
1665: hr_utility.set_location(l_proc, 90);
1666: hr_utility.trace('l_date_first_avail='||l_date_first_avail);
1667: --
1668: ---------------------------------------------
1669: -- Check whether it's the primary car of --
1662: l_date_first_avail := NULL;
1663: END IF;
1664: --
1665: hr_utility.set_location(l_proc, 90);
1666: hr_utility.trace('l_date_first_avail='||l_date_first_avail);
1667: --
1668: ---------------------------------------------
1669: -- Check whether it's the primary car of --
1670: -- the employee --
1674: OPEN c_first_asg(p_assignment_id, l_eff_date);
1675: FETCH c_first_asg INTO first_asg_rec;
1676: CLOSE c_first_asg;
1677: --
1678: hr_utility.set_location(l_proc, 100);
1679: --
1680: -- Get primary car allocation of the
1681: -- first assignment of this person
1682: -- in this tax ref
1683: OPEN c_primary_car(first_asg_rec.assignment_id, l_eff_date);
1684: FETCH c_primary_car INTO primary_car_rec;
1685: CLOSE c_primary_car;
1686: --
1687: hr_utility.set_location(l_proc, 110);
1688: --
1689: IF primary_car_rec.vehicle_allocation_id
1690: = p_vehicle_allocation_id THEN
1691: -- This is the primary car allocation
1696: -- therefore mark it as a second car.
1697: l_second_car_flag := 'Y';
1698: END IF;
1699: --
1700: hr_utility.set_location(l_proc, 120);
1701: hr_utility.trace('l_second_car_flag='||l_second_car_flag);
1702: --
1703: IF p_action_flag = 'N' THEN
1704: hr_utility.set_location(l_proc, 130);
1697: l_second_car_flag := 'Y';
1698: END IF;
1699: --
1700: hr_utility.set_location(l_proc, 120);
1701: hr_utility.trace('l_second_car_flag='||l_second_car_flag);
1702: --
1703: IF p_action_flag = 'N' THEN
1704: hr_utility.set_location(l_proc, 130);
1705: --
1700: hr_utility.set_location(l_proc, 120);
1701: hr_utility.trace('l_second_car_flag='||l_second_car_flag);
1702: --
1703: IF p_action_flag = 'N' THEN
1704: hr_utility.set_location(l_proc, 130);
1705: --
1706: pay_action_information_api.create_action_information (
1707: p_action_information_id => l_action_info_id
1708: , p_action_context_id => p_asg_act_id
1730: , p_action_information15 => l_second_car_flag
1731: , p_action_information16 => upper(car_rec.make || ' ' || car_rec.model)
1732: , p_action_information17 => to_char(car_rec.engine_capacity_in_cc));
1733: --
1734: hr_utility.set_location(l_proc, 140);
1735: ELSIF p_action_flag = 'R' THEN
1736: hr_utility.set_location(l_proc, 150);
1737: --
1738: pay_action_information_api.create_action_information (
1732: , p_action_information17 => to_char(car_rec.engine_capacity_in_cc));
1733: --
1734: hr_utility.set_location(l_proc, 140);
1735: ELSIF p_action_flag = 'R' THEN
1736: hr_utility.set_location(l_proc, 150);
1737: --
1738: pay_action_information_api.create_action_information (
1739: p_action_information_id => l_action_info_id
1740: , p_action_context_id => p_asg_act_id
1767: , p_action_information20 => to_char(prior_prim_car_rec.vehicle_allocation_id)
1768: , p_action_information21 => fnd_date.date_to_canonical(prior_prim_car_rec.effective_end_date)
1769: );
1770: --
1771: hr_utility.set_location(l_proc, 160);
1772: ELSIF p_action_flag = 'W' THEN
1773: hr_utility.set_location(l_proc, 170);
1774: --
1775: pay_action_information_api.create_action_information (
1769: );
1770: --
1771: hr_utility.set_location(l_proc, 160);
1772: ELSIF p_action_flag = 'W' THEN
1773: hr_utility.set_location(l_proc, 170);
1774: --
1775: pay_action_information_api.create_action_information (
1776: p_action_information_id => l_action_info_id
1777: , p_action_context_id => p_asg_act_id
1800: , p_action_information16 => upper(car_rec.make || ' ' || car_rec.model)
1801: , p_action_information17 => to_char(car_rec.engine_capacity_in_cc)
1802: );
1803: --
1804: hr_utility.set_location(l_proc, 180);
1805: ELSIF p_action_flag = 'F' THEN
1806: hr_utility.set_location(l_proc, 190);
1807: --
1808: pay_action_information_api.create_action_information (
1802: );
1803: --
1804: hr_utility.set_location(l_proc, 180);
1805: ELSIF p_action_flag = 'F' THEN
1806: hr_utility.set_location(l_proc, 190);
1807: --
1808: pay_action_information_api.create_action_information (
1809: p_action_information_id => l_action_info_id
1810: , p_action_context_id => p_asg_act_id
1837: , p_action_information20 => to_char(p_vehicle_allocation_id)
1838: , p_action_information21 => fnd_date.date_to_canonical(p_fuel_type_change_date-1)
1839: );
1840: --
1841: hr_utility.set_location(l_proc, 200);
1842: END IF;
1843: --
1844: hr_utility.set_location('Leaving '||l_proc, 300);
1845: END archive_allocation_action;
1840: --
1841: hr_utility.set_location(l_proc, 200);
1842: END IF;
1843: --
1844: hr_utility.set_location('Leaving '||l_proc, 300);
1845: END archive_allocation_action;
1846:
1847: -----------------------------------------------------
1848: -- PROCEDURE ARCHIVE_CODE --
1876: l_action_flag VARCHAR2(1);
1877: l_archived_flag VARCHAR2(1);
1878: --
1879: BEGIN
1880: --hr_utility.trace_on(null, 'KTHAMPAN');
1881: hr_utility.set_location('Entering '||l_proc, 10);
1882: --
1883: For asg_rec IN c_asg
1884: LOOP
1877: l_archived_flag VARCHAR2(1);
1878: --
1879: BEGIN
1880: --hr_utility.trace_on(null, 'KTHAMPAN');
1881: hr_utility.set_location('Entering '||l_proc, 10);
1882: --
1883: For asg_rec IN c_asg
1884: LOOP
1885: -- Loop through all assignments
1882: --
1883: For asg_rec IN c_asg
1884: LOOP
1885: -- Loop through all assignments
1886: hr_utility.set_location(l_proc, 20);
1887: hr_utility.trace(l_proc||': assignment_id='||asg_rec.assignment_id);
1888: hr_utility.trace(l_proc||': asg_min_start_date='||fnd_date.date_to_displaydate(asg_rec.asg_min_start_date));
1889: hr_utility.trace(l_proc||': asg_max_end_date='||fnd_date.date_to_displaydate(asg_rec.asg_max_end_date));
1890: --
1883: For asg_rec IN c_asg
1884: LOOP
1885: -- Loop through all assignments
1886: hr_utility.set_location(l_proc, 20);
1887: hr_utility.trace(l_proc||': assignment_id='||asg_rec.assignment_id);
1888: hr_utility.trace(l_proc||': asg_min_start_date='||fnd_date.date_to_displaydate(asg_rec.asg_min_start_date));
1889: hr_utility.trace(l_proc||': asg_max_end_date='||fnd_date.date_to_displaydate(asg_rec.asg_max_end_date));
1890: --
1891: FOR alc_rec IN c_allocations(asg_rec.assignment_id) LOOP
1884: LOOP
1885: -- Loop through all assignments
1886: hr_utility.set_location(l_proc, 20);
1887: hr_utility.trace(l_proc||': assignment_id='||asg_rec.assignment_id);
1888: hr_utility.trace(l_proc||': asg_min_start_date='||fnd_date.date_to_displaydate(asg_rec.asg_min_start_date));
1889: hr_utility.trace(l_proc||': asg_max_end_date='||fnd_date.date_to_displaydate(asg_rec.asg_max_end_date));
1890: --
1891: FOR alc_rec IN c_allocations(asg_rec.assignment_id) LOOP
1892: -- Loop through all vehicle allocations
1885: -- Loop through all assignments
1886: hr_utility.set_location(l_proc, 20);
1887: hr_utility.trace(l_proc||': assignment_id='||asg_rec.assignment_id);
1888: hr_utility.trace(l_proc||': asg_min_start_date='||fnd_date.date_to_displaydate(asg_rec.asg_min_start_date));
1889: hr_utility.trace(l_proc||': asg_max_end_date='||fnd_date.date_to_displaydate(asg_rec.asg_max_end_date));
1890: --
1891: FOR alc_rec IN c_allocations(asg_rec.assignment_id) LOOP
1892: -- Loop through all vehicle allocations
1893: -- over the date range
1890: --
1891: FOR alc_rec IN c_allocations(asg_rec.assignment_id) LOOP
1892: -- Loop through all vehicle allocations
1893: -- over the date range
1894: hr_utility.set_location(l_proc, 30);
1895: hr_utility.trace(l_proc||': vehicle_allocation_id='||alc_rec.vehicle_allocation_id);
1896: hr_utility.trace(l_proc||': usage_type='||alc_rec.usage_type);
1897: --
1898: L_action_flag := 'X';
1891: FOR alc_rec IN c_allocations(asg_rec.assignment_id) LOOP
1892: -- Loop through all vehicle allocations
1893: -- over the date range
1894: hr_utility.set_location(l_proc, 30);
1895: hr_utility.trace(l_proc||': vehicle_allocation_id='||alc_rec.vehicle_allocation_id);
1896: hr_utility.trace(l_proc||': usage_type='||alc_rec.usage_type);
1897: --
1898: L_action_flag := 'X';
1899: l_archived_flag := 'Y';
1892: -- Loop through all vehicle allocations
1893: -- over the date range
1894: hr_utility.set_location(l_proc, 30);
1895: hr_utility.trace(l_proc||': vehicle_allocation_id='||alc_rec.vehicle_allocation_id);
1896: hr_utility.trace(l_proc||': usage_type='||alc_rec.usage_type);
1897: --
1898: L_action_flag := 'X';
1899: l_archived_flag := 'Y';
1900: --
1901: OPEN c_alloc_dates(alc_rec.vehicle_allocation_id);
1902: FETCH c_alloc_dates INTO alc_dates_rec;
1903: CLOSE c_alloc_dates;
1904: --
1905: hr_utility.set_location(l_proc, 40);
1906: hr_utility.trace(l_proc||': min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
1907: hr_utility.trace(l_proc||': max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
1908: --
1909: -- Check whether vehicle was allocated or
1902: FETCH c_alloc_dates INTO alc_dates_rec;
1903: CLOSE c_alloc_dates;
1904: --
1905: hr_utility.set_location(l_proc, 40);
1906: hr_utility.trace(l_proc||': min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
1907: hr_utility.trace(l_proc||': max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
1908: --
1909: -- Check whether vehicle was allocated or
1910: -- Withdrawn within the date range also
1903: CLOSE c_alloc_dates;
1904: --
1905: hr_utility.set_location(l_proc, 40);
1906: hr_utility.trace(l_proc||': min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
1907: hr_utility.trace(l_proc||': max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
1908: --
1909: -- Check whether vehicle was allocated or
1910: -- Withdrawn within the date range also
1911: -- check that this allocation or withdrawal
1925: OPEN c_first_asg(asg_rec.assignment_id, alc_dates_rec.min_start_date);
1926: FETCH c_first_asg INTO first_asg_rec;
1927: CLOSE c_first_asg;
1928: --
1929: hr_utility.set_location(l_proc, 50);
1930: hr_utility.trace(l_proc||': First Assignment='|| first_asg_rec.assignment_id);
1931: -- Get primary car allocation of the
1932: -- first assignment of this person
1933: -- in this tax ref
1926: FETCH c_first_asg INTO first_asg_rec;
1927: CLOSE c_first_asg;
1928: --
1929: hr_utility.set_location(l_proc, 50);
1930: hr_utility.trace(l_proc||': First Assignment='|| first_asg_rec.assignment_id);
1931: -- Get primary car allocation of the
1932: -- first assignment of this person
1933: -- in this tax ref
1934: OPEN c_primary_car(first_asg_rec.assignment_id, alc_dates_rec.min_start_date);
1934: OPEN c_primary_car(first_asg_rec.assignment_id, alc_dates_rec.min_start_date);
1935: FETCH c_primary_car INTO primary_car_rec;
1936: CLOSE c_primary_car;
1937: --
1938: hr_utility.set_location(l_proc, 60);
1939: hr_utility.trace(l_proc||': Primary Car Allocation='|| primary_car_rec.vehicle_allocation_id);
1940: --
1941: IF primary_car_rec.vehicle_allocation_id = alc_rec.vehicle_allocation_id THEN
1942: -- This is the first car allocation
1935: FETCH c_primary_car INTO primary_car_rec;
1936: CLOSE c_primary_car;
1937: --
1938: hr_utility.set_location(l_proc, 60);
1939: hr_utility.trace(l_proc||': Primary Car Allocation='|| primary_car_rec.vehicle_allocation_id);
1940: --
1941: IF primary_car_rec.vehicle_allocation_id = alc_rec.vehicle_allocation_id THEN
1942: -- This is the first car allocation
1943: -- of this employee in this tax ref
1950: ELSE
1951: L_action_flag := 'N';
1952: END IF;
1953: --
1954: hr_utility.set_location(l_proc, 70);
1955: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
1956: --
1957: CLOSE c_prior_prim_car;
1958: ELSE
1951: L_action_flag := 'N';
1952: END IF;
1953: --
1954: hr_utility.set_location(l_proc, 70);
1955: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
1956: --
1957: CLOSE c_prior_prim_car;
1958: ELSE
1959: -- Not the first car of the employee
1960: -- report this as new car
1961: L_action_flag := 'N';
1962: END IF;
1963: --
1964: hr_utility.set_location(l_proc, 80);
1965: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
1966: --
1967: ELSE
1968: -- Not a primary car therefore mark
1961: L_action_flag := 'N';
1962: END IF;
1963: --
1964: hr_utility.set_location(l_proc, 80);
1965: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
1966: --
1967: ELSE
1968: -- Not a primary car therefore mark
1969: -- it as new car allocation action
1969: -- it as new car allocation action
1970: l_action_flag := 'N';
1971: END IF;
1972: --
1973: hr_utility.set_location(l_proc, 90);
1974: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
1975: --
1976: IF L_action_flag <> 'X' THEN
1977: -- Check whether this allocation action
1970: l_action_flag := 'N';
1971: END IF;
1972: --
1973: hr_utility.set_location(l_proc, 90);
1974: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
1975: --
1976: IF L_action_flag <> 'X' THEN
1977: -- Check whether this allocation action
1978: -- has been already archived
1981: alc_rec.vehicle_allocation_id,
1982: alc_dates_rec.min_start_date,
1983: l_action_flag);
1984: --
1985: hr_utility.set_location(l_proc, 100);
1986: hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
1987: --
1988: IF l_archived_flag = 'N' THEN
1989: -- Action has not been archived already
1982: alc_dates_rec.min_start_date,
1983: l_action_flag);
1984: --
1985: hr_utility.set_location(l_proc, 100);
1986: hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
1987: --
1988: IF l_archived_flag = 'N' THEN
1989: -- Action has not been archived already
1990: -- therefore archive it.
1987: --
1988: IF l_archived_flag = 'N' THEN
1989: -- Action has not been archived already
1990: -- therefore archive it.
1991: hr_utility.set_location(l_proc, 105);
1992: hr_utility.trace(l_proc||': p_assactid='|| p_assactid);
1993: hr_utility.trace(l_proc||': vehicle_allocation_id='|| alc_rec.vehicle_allocation_id);
1994: hr_utility.trace(l_proc||': vehicle_repository_id='|| alc_rec.vehicle_repository_id);
1995: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
1988: IF l_archived_flag = 'N' THEN
1989: -- Action has not been archived already
1990: -- therefore archive it.
1991: hr_utility.set_location(l_proc, 105);
1992: hr_utility.trace(l_proc||': p_assactid='|| p_assactid);
1993: hr_utility.trace(l_proc||': vehicle_allocation_id='|| alc_rec.vehicle_allocation_id);
1994: hr_utility.trace(l_proc||': vehicle_repository_id='|| alc_rec.vehicle_repository_id);
1995: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
1996: hr_utility.trace(l_proc||': alc_dates_rec.min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
1989: -- Action has not been archived already
1990: -- therefore archive it.
1991: hr_utility.set_location(l_proc, 105);
1992: hr_utility.trace(l_proc||': p_assactid='|| p_assactid);
1993: hr_utility.trace(l_proc||': vehicle_allocation_id='|| alc_rec.vehicle_allocation_id);
1994: hr_utility.trace(l_proc||': vehicle_repository_id='|| alc_rec.vehicle_repository_id);
1995: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
1996: hr_utility.trace(l_proc||': alc_dates_rec.min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
1997: hr_utility.trace(l_proc||': alc_dates_rec.max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
1990: -- therefore archive it.
1991: hr_utility.set_location(l_proc, 105);
1992: hr_utility.trace(l_proc||': p_assactid='|| p_assactid);
1993: hr_utility.trace(l_proc||': vehicle_allocation_id='|| alc_rec.vehicle_allocation_id);
1994: hr_utility.trace(l_proc||': vehicle_repository_id='|| alc_rec.vehicle_repository_id);
1995: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
1996: hr_utility.trace(l_proc||': alc_dates_rec.min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
1997: hr_utility.trace(l_proc||': alc_dates_rec.max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
1998: --
1991: hr_utility.set_location(l_proc, 105);
1992: hr_utility.trace(l_proc||': p_assactid='|| p_assactid);
1993: hr_utility.trace(l_proc||': vehicle_allocation_id='|| alc_rec.vehicle_allocation_id);
1994: hr_utility.trace(l_proc||': vehicle_repository_id='|| alc_rec.vehicle_repository_id);
1995: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
1996: hr_utility.trace(l_proc||': alc_dates_rec.min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
1997: hr_utility.trace(l_proc||': alc_dates_rec.max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
1998: --
1999: archive_allocation_action(p_assignment_id => asg_rec.assignment_id,
1992: hr_utility.trace(l_proc||': p_assactid='|| p_assactid);
1993: hr_utility.trace(l_proc||': vehicle_allocation_id='|| alc_rec.vehicle_allocation_id);
1994: hr_utility.trace(l_proc||': vehicle_repository_id='|| alc_rec.vehicle_repository_id);
1995: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
1996: hr_utility.trace(l_proc||': alc_dates_rec.min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
1997: hr_utility.trace(l_proc||': alc_dates_rec.max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
1998: --
1999: archive_allocation_action(p_assignment_id => asg_rec.assignment_id,
2000: p_asg_act_id => p_assactid,
1993: hr_utility.trace(l_proc||': vehicle_allocation_id='|| alc_rec.vehicle_allocation_id);
1994: hr_utility.trace(l_proc||': vehicle_repository_id='|| alc_rec.vehicle_repository_id);
1995: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
1996: hr_utility.trace(l_proc||': alc_dates_rec.min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
1997: hr_utility.trace(l_proc||': alc_dates_rec.max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
1998: --
1999: archive_allocation_action(p_assignment_id => asg_rec.assignment_id,
2000: p_asg_act_id => p_assactid,
2001: p_vehicle_allocation_id => alc_rec.vehicle_allocation_id,
2024: OPEN c_first_asg(asg_rec.assignment_id, alc_dates_rec.max_end_date);
2025: FETCH c_first_asg INTO first_asg_rec;
2026: CLOSE c_first_asg;
2027: --
2028: hr_utility.set_location(l_proc, 50);
2029: hr_utility.trace(l_proc||': First Assignment='|| first_asg_rec.assignment_id);
2030: -- Get primary car allocation of the
2031: -- first assignment of this person
2032: -- in this tax ref
2025: FETCH c_first_asg INTO first_asg_rec;
2026: CLOSE c_first_asg;
2027: --
2028: hr_utility.set_location(l_proc, 50);
2029: hr_utility.trace(l_proc||': First Assignment='|| first_asg_rec.assignment_id);
2030: -- Get primary car allocation of the
2031: -- first assignment of this person
2032: -- in this tax ref
2033: OPEN c_primary_car(first_asg_rec.assignment_id, alc_dates_rec.max_end_date);
2033: OPEN c_primary_car(first_asg_rec.assignment_id, alc_dates_rec.max_end_date);
2034: FETCH c_primary_car INTO primary_car_rec;
2035: CLOSE c_primary_car;
2036: --
2037: hr_utility.set_location(l_proc, 60);
2038: hr_utility.trace(l_proc||': Primary Car Allocation='|| primary_car_rec.vehicle_allocation_id);
2039: --
2040: IF primary_car_rec.vehicle_allocation_id = alc_rec.vehicle_allocation_id THEN
2041: --
2034: FETCH c_primary_car INTO primary_car_rec;
2035: CLOSE c_primary_car;
2036: --
2037: hr_utility.set_location(l_proc, 60);
2038: hr_utility.trace(l_proc||': Primary Car Allocation='|| primary_car_rec.vehicle_allocation_id);
2039: --
2040: IF primary_car_rec.vehicle_allocation_id = alc_rec.vehicle_allocation_id THEN
2041: --
2042: OPEN c_next_prim_car(asg_rec.assignment_id, Alc_dates_rec.max_end_date);
2050: -- This is a withdrawal action
2051: L_action_flag := 'W';
2052: END IF;
2053: --
2054: hr_utility.set_location(l_proc, 70);
2055: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2056: --
2057: CLOSE c_next_prim_car;
2058: ELSE
2051: L_action_flag := 'W';
2052: END IF;
2053: --
2054: hr_utility.set_location(l_proc, 70);
2055: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2056: --
2057: CLOSE c_next_prim_car;
2058: ELSE
2059: -- Not the first car of the employee
2061: L_action_flag := 'W';
2062: END IF;
2063: --
2064: --
2065: hr_utility.set_location(l_proc, 80);
2066: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2067: --
2068: ELSE
2069: -- Not a first car therefore mark
2062: END IF;
2063: --
2064: --
2065: hr_utility.set_location(l_proc, 80);
2066: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2067: --
2068: ELSE
2069: -- Not a first car therefore mark
2070: -- it as withdrawal car action
2070: -- it as withdrawal car action
2071: l_action_flag := 'W';
2072: END IF;
2073: --
2074: hr_utility.set_location(l_proc, 90);
2075: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2076: --
2077: IF L_action_flag <> 'X' THEN
2078: -- Check whether this allocation action
2071: l_action_flag := 'W';
2072: END IF;
2073: --
2074: hr_utility.set_location(l_proc, 90);
2075: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2076: --
2077: IF L_action_flag <> 'X' THEN
2078: -- Check whether this allocation action
2079: -- has been already archived
2082: alc_rec.vehicle_allocation_id,
2083: alc_dates_rec.min_start_date,
2084: l_action_flag);
2085: --
2086: hr_utility.set_location(l_proc, 100);
2087: hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
2088: --
2089: IF l_archived_flag = 'N' THEN
2090: -- Action has not been archived already
2083: alc_dates_rec.min_start_date,
2084: l_action_flag);
2085: --
2086: hr_utility.set_location(l_proc, 100);
2087: hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
2088: --
2089: IF l_archived_flag = 'N' THEN
2090: -- Action has not been archived already
2091: -- therefore archive it.
2088: --
2089: IF l_archived_flag = 'N' THEN
2090: -- Action has not been archived already
2091: -- therefore archive it.
2092: hr_utility.set_location(l_proc, 105);
2093: hr_utility.trace(l_proc||': p_assactid='|| p_assactid);
2094: hr_utility.trace(l_proc||': vehicle_allocation_id='|| alc_rec.vehicle_allocation_id);
2095: hr_utility.trace(l_proc||': vehicle_repository_id='|| alc_rec.vehicle_repository_id);
2096: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2089: IF l_archived_flag = 'N' THEN
2090: -- Action has not been archived already
2091: -- therefore archive it.
2092: hr_utility.set_location(l_proc, 105);
2093: hr_utility.trace(l_proc||': p_assactid='|| p_assactid);
2094: hr_utility.trace(l_proc||': vehicle_allocation_id='|| alc_rec.vehicle_allocation_id);
2095: hr_utility.trace(l_proc||': vehicle_repository_id='|| alc_rec.vehicle_repository_id);
2096: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2097: hr_utility.trace(l_proc||': alc_dates_rec.min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
2090: -- Action has not been archived already
2091: -- therefore archive it.
2092: hr_utility.set_location(l_proc, 105);
2093: hr_utility.trace(l_proc||': p_assactid='|| p_assactid);
2094: hr_utility.trace(l_proc||': vehicle_allocation_id='|| alc_rec.vehicle_allocation_id);
2095: hr_utility.trace(l_proc||': vehicle_repository_id='|| alc_rec.vehicle_repository_id);
2096: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2097: hr_utility.trace(l_proc||': alc_dates_rec.min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
2098: hr_utility.trace(l_proc||': alc_dates_rec.max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
2091: -- therefore archive it.
2092: hr_utility.set_location(l_proc, 105);
2093: hr_utility.trace(l_proc||': p_assactid='|| p_assactid);
2094: hr_utility.trace(l_proc||': vehicle_allocation_id='|| alc_rec.vehicle_allocation_id);
2095: hr_utility.trace(l_proc||': vehicle_repository_id='|| alc_rec.vehicle_repository_id);
2096: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2097: hr_utility.trace(l_proc||': alc_dates_rec.min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
2098: hr_utility.trace(l_proc||': alc_dates_rec.max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
2099: --
2092: hr_utility.set_location(l_proc, 105);
2093: hr_utility.trace(l_proc||': p_assactid='|| p_assactid);
2094: hr_utility.trace(l_proc||': vehicle_allocation_id='|| alc_rec.vehicle_allocation_id);
2095: hr_utility.trace(l_proc||': vehicle_repository_id='|| alc_rec.vehicle_repository_id);
2096: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2097: hr_utility.trace(l_proc||': alc_dates_rec.min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
2098: hr_utility.trace(l_proc||': alc_dates_rec.max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
2099: --
2100: archive_allocation_action(p_assignment_id => asg_rec.assignment_id,
2093: hr_utility.trace(l_proc||': p_assactid='|| p_assactid);
2094: hr_utility.trace(l_proc||': vehicle_allocation_id='|| alc_rec.vehicle_allocation_id);
2095: hr_utility.trace(l_proc||': vehicle_repository_id='|| alc_rec.vehicle_repository_id);
2096: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2097: hr_utility.trace(l_proc||': alc_dates_rec.min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
2098: hr_utility.trace(l_proc||': alc_dates_rec.max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
2099: --
2100: archive_allocation_action(p_assignment_id => asg_rec.assignment_id,
2101: p_asg_act_id => p_assactid,
2094: hr_utility.trace(l_proc||': vehicle_allocation_id='|| alc_rec.vehicle_allocation_id);
2095: hr_utility.trace(l_proc||': vehicle_repository_id='|| alc_rec.vehicle_repository_id);
2096: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2097: hr_utility.trace(l_proc||': alc_dates_rec.min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
2098: hr_utility.trace(l_proc||': alc_dates_rec.max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
2099: --
2100: archive_allocation_action(p_assignment_id => asg_rec.assignment_id,
2101: p_asg_act_id => p_assactid,
2102: p_vehicle_allocation_id => alc_rec.vehicle_allocation_id,
2113: -- Fuel type has changed therefore make sure
2114: -- this change occured after the vehicle was
2115: -- allocated to this assignment
2116: --
2117: hr_utility.set_location(l_proc, 110);
2118: hr_utility.trace(l_proc||': effective_start_date='|| vehicle_changes_rec.effective_start_date);
2119: hr_utility.trace(l_proc||': fuel_type='|| vehicle_changes_rec.fuel_type);
2120: --
2121: IF (vehicle_changes_rec.effective_start_date
2114: -- this change occured after the vehicle was
2115: -- allocated to this assignment
2116: --
2117: hr_utility.set_location(l_proc, 110);
2118: hr_utility.trace(l_proc||': effective_start_date='|| vehicle_changes_rec.effective_start_date);
2119: hr_utility.trace(l_proc||': fuel_type='|| vehicle_changes_rec.fuel_type);
2120: --
2121: IF (vehicle_changes_rec.effective_start_date
2122: BETWEEN alc_dates_rec.min_start_date+1 AND alc_dates_rec.max_end_date) AND
2115: -- allocated to this assignment
2116: --
2117: hr_utility.set_location(l_proc, 110);
2118: hr_utility.trace(l_proc||': effective_start_date='|| vehicle_changes_rec.effective_start_date);
2119: hr_utility.trace(l_proc||': fuel_type='|| vehicle_changes_rec.fuel_type);
2120: --
2121: IF (vehicle_changes_rec.effective_start_date
2122: BETWEEN alc_dates_rec.min_start_date+1 AND alc_dates_rec.max_end_date) AND
2123: (vehicle_changes_rec.effective_start_date
2130: alc_rec.vehicle_allocation_id,
2131: vehicle_changes_rec.effective_start_date,
2132: 'F');
2133: --
2134: hr_utility.set_location(l_proc, 120);
2135: hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
2136: --
2137: IF l_archived_flag = 'N' THEN
2138: -- Action has not been archived already
2131: vehicle_changes_rec.effective_start_date,
2132: 'F');
2133: --
2134: hr_utility.set_location(l_proc, 120);
2135: hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
2136: --
2137: IF l_archived_flag = 'N' THEN
2138: -- Action has not been archived already
2139: -- therefore archive it.
2149: END IF;
2150: END IF;
2151: END LOOP;
2152: --
2153: hr_utility.set_location(l_proc, 130);
2154: --
2155: L_action_flag := 'X';
2156: l_archived_flag := 'Y';
2157: --
2165: -- Assignment has started on this payroll or tax ref during the
2166: -- input date range and this car allocation was active on the
2167: -- start date therefore check whether it is a transfer
2168: -- from another tax ref
2169: hr_utility.set_location(l_proc, 160);
2170: l_previous_tax_ref := NULL;
2171: --
2172: OPEN c_tax_ref(asg_rec.assignment_id, asg_rec.asg_min_start_date-1);
2173: FETCH c_tax_ref INTO l_previous_tax_ref;
2172: OPEN c_tax_ref(asg_rec.assignment_id, asg_rec.asg_min_start_date-1);
2173: FETCH c_tax_ref INTO l_previous_tax_ref;
2174: CLOSE c_tax_ref;
2175: --
2176: hr_utility.set_location(l_proc, 170);
2177: hr_utility.trace(l_proc||', l_previous_tax_ref='||l_previous_tax_ref);
2178: --
2179: IF l_previous_tax_ref is NOT NULL AND l_previous_tax_ref <> g_tax_ref THEN
2180: -- This assignment has a different prior tax ref
2173: FETCH c_tax_ref INTO l_previous_tax_ref;
2174: CLOSE c_tax_ref;
2175: --
2176: hr_utility.set_location(l_proc, 170);
2177: hr_utility.trace(l_proc||', l_previous_tax_ref='||l_previous_tax_ref);
2178: --
2179: IF l_previous_tax_ref is NOT NULL AND l_previous_tax_ref <> g_tax_ref THEN
2180: -- This assignment has a different prior tax ref
2181: -- therefore it should be reported as a new car
2179: IF l_previous_tax_ref is NOT NULL AND l_previous_tax_ref <> g_tax_ref THEN
2180: -- This assignment has a different prior tax ref
2181: -- therefore it should be reported as a new car
2182: -- allocation on this EDI message.
2183: hr_utility.set_location(l_proc, 180);
2184: l_action_flag := 'N';
2185: --
2186: -- Check whether this allocation action
2187: -- has been already archived
2190: alc_rec.vehicle_allocation_id,
2191: asg_rec.asg_min_start_date,
2192: l_action_flag);
2193: --
2194: hr_utility.set_location(l_proc, 190);
2195: hr_utility.trace(l_proc||', l_archived_flag='||l_archived_flag);
2196: --
2197: IF l_archived_flag = 'N' THEN
2198: -- Action has not been archived already
2191: asg_rec.asg_min_start_date,
2192: l_action_flag);
2193: --
2194: hr_utility.set_location(l_proc, 190);
2195: hr_utility.trace(l_proc||', l_archived_flag='||l_archived_flag);
2196: --
2197: IF l_archived_flag = 'N' THEN
2198: -- Action has not been archived already
2199: -- therefore archive it.
2217: -- Assignment has ended on this payroll or tax ref during the
2218: -- input date range and this car allocation was active on the
2219: -- end date therefore check whether it is a transfer
2220: -- to another tax ref
2221: hr_utility.set_location(l_proc, 200);
2222: l_next_tax_ref := NULL;
2223: --
2224: OPEN c_tax_ref(asg_rec.assignment_id, asg_rec.asg_max_end_date+1);
2225: FETCH c_tax_ref INTO l_next_tax_ref;
2224: OPEN c_tax_ref(asg_rec.assignment_id, asg_rec.asg_max_end_date+1);
2225: FETCH c_tax_ref INTO l_next_tax_ref;
2226: CLOSE c_tax_ref;
2227: --
2228: hr_utility.set_location(l_proc, 210);
2229: hr_utility.trace(l_proc||', l_next_tax_ref='||l_next_tax_ref);
2230: --
2231: IF l_next_tax_ref is NOT NULL AND l_next_tax_ref <> g_tax_ref THEN
2232: -- This assignment has a different tax ref after end date
2225: FETCH c_tax_ref INTO l_next_tax_ref;
2226: CLOSE c_tax_ref;
2227: --
2228: hr_utility.set_location(l_proc, 210);
2229: hr_utility.trace(l_proc||', l_next_tax_ref='||l_next_tax_ref);
2230: --
2231: IF l_next_tax_ref is NOT NULL AND l_next_tax_ref <> g_tax_ref THEN
2232: -- This assignment has a different tax ref after end date
2233: -- therefore it should be reported as allocation withdrawal
2231: IF l_next_tax_ref is NOT NULL AND l_next_tax_ref <> g_tax_ref THEN
2232: -- This assignment has a different tax ref after end date
2233: -- therefore it should be reported as allocation withdrawal
2234: -- on this EDI message.
2235: hr_utility.set_location(l_proc, 220);
2236: l_action_flag := 'W';
2237: --
2238: -- Check whether this allocation action
2239: -- has been already archived
2242: alc_rec.vehicle_allocation_id,
2243: asg_rec.asg_max_end_date,
2244: l_action_flag);
2245: --
2246: hr_utility.set_location(l_proc, 230);
2247: hr_utility.trace(l_proc||', l_archived_flag='||l_archived_flag);
2248: --
2249: IF l_archived_flag = 'N' THEN
2250: -- Action has not been archived already
2243: asg_rec.asg_max_end_date,
2244: l_action_flag);
2245: --
2246: hr_utility.set_location(l_proc, 230);
2247: hr_utility.trace(l_proc||', l_archived_flag='||l_archived_flag);
2248: --
2249: IF l_archived_flag = 'N' THEN
2250: -- Action has not been archived already
2251: -- therefore archive it.
2260: --
2261: END IF;
2262: END IF;
2263: --
2264: hr_utility.set_location(l_proc, 240);
2265: --
2266: hr_utility.set_location(l_proc, 250);
2267: END LOOP;
2268: --
2262: END IF;
2263: --
2264: hr_utility.set_location(l_proc, 240);
2265: --
2266: hr_utility.set_location(l_proc, 250);
2267: END LOOP;
2268: --
2269: hr_utility.set_location(l_proc, 260);
2270: --
2265: --
2266: hr_utility.set_location(l_proc, 250);
2267: END LOOP;
2268: --
2269: hr_utility.set_location(l_proc, 260);
2270: --
2271: END LOOP;
2272: --
2273: hr_utility.set_location('Leaving: '||l_proc,290);
2269: hr_utility.set_location(l_proc, 260);
2270: --
2271: END LOOP;
2272: --
2273: hr_utility.set_location('Leaving: '||l_proc,290);
2274: --hr_utility.trace_off;
2275: EXCEPTION
2276: WHEN OTHERS THEN
2277: hr_utility.set_location('Leaving: '||l_proc,300);
2270: --
2271: END LOOP;
2272: --
2273: hr_utility.set_location('Leaving: '||l_proc,290);
2274: --hr_utility.trace_off;
2275: EXCEPTION
2276: WHEN OTHERS THEN
2277: hr_utility.set_location('Leaving: '||l_proc,300);
2278: -- hr_utility.trace_off;
2273: hr_utility.set_location('Leaving: '||l_proc,290);
2274: --hr_utility.trace_off;
2275: EXCEPTION
2276: WHEN OTHERS THEN
2277: hr_utility.set_location('Leaving: '||l_proc,300);
2278: -- hr_utility.trace_off;
2279: raise;
2280: END archive_code;
2281: --
2274: --hr_utility.trace_off;
2275: EXCEPTION
2276: WHEN OTHERS THEN
2277: hr_utility.set_location('Leaving: '||l_proc,300);
2278: -- hr_utility.trace_off;
2279: raise;
2280: END archive_code;
2281: --
2282:
2313: l_action_flag VARCHAR2(1);
2314: l_archived_flag VARCHAR2(1);
2315: --
2316: BEGIN
2317: --hr_utility.trace_on(null, 'KTHAMPAN');
2318: hr_utility.set_location('Entering '||l_proc, 10);
2319: --
2320: For asg_rec IN c_asg
2321: LOOP
2314: l_archived_flag VARCHAR2(1);
2315: --
2316: BEGIN
2317: --hr_utility.trace_on(null, 'KTHAMPAN');
2318: hr_utility.set_location('Entering '||l_proc, 10);
2319: --
2320: For asg_rec IN c_asg
2321: LOOP
2322: -- Loop through all assignments
2319: --
2320: For asg_rec IN c_asg
2321: LOOP
2322: -- Loop through all assignments
2323: hr_utility.set_location(l_proc, 20);
2324: hr_utility.trace(l_proc||': assignment_id='||asg_rec.assignment_id);
2325: hr_utility.trace(l_proc||': asg_min_start_date='||fnd_date.date_to_displaydate(asg_rec.asg_min_start_date));
2326: hr_utility.trace(l_proc||': asg_max_end_date='||fnd_date.date_to_displaydate(asg_rec.asg_max_end_date));
2327: --
2320: For asg_rec IN c_asg
2321: LOOP
2322: -- Loop through all assignments
2323: hr_utility.set_location(l_proc, 20);
2324: hr_utility.trace(l_proc||': assignment_id='||asg_rec.assignment_id);
2325: hr_utility.trace(l_proc||': asg_min_start_date='||fnd_date.date_to_displaydate(asg_rec.asg_min_start_date));
2326: hr_utility.trace(l_proc||': asg_max_end_date='||fnd_date.date_to_displaydate(asg_rec.asg_max_end_date));
2327: --
2328: FOR alc_rec IN c_allocations(asg_rec.assignment_id) LOOP
2321: LOOP
2322: -- Loop through all assignments
2323: hr_utility.set_location(l_proc, 20);
2324: hr_utility.trace(l_proc||': assignment_id='||asg_rec.assignment_id);
2325: hr_utility.trace(l_proc||': asg_min_start_date='||fnd_date.date_to_displaydate(asg_rec.asg_min_start_date));
2326: hr_utility.trace(l_proc||': asg_max_end_date='||fnd_date.date_to_displaydate(asg_rec.asg_max_end_date));
2327: --
2328: FOR alc_rec IN c_allocations(asg_rec.assignment_id) LOOP
2329: -- Loop through all vehicle allocations
2322: -- Loop through all assignments
2323: hr_utility.set_location(l_proc, 20);
2324: hr_utility.trace(l_proc||': assignment_id='||asg_rec.assignment_id);
2325: hr_utility.trace(l_proc||': asg_min_start_date='||fnd_date.date_to_displaydate(asg_rec.asg_min_start_date));
2326: hr_utility.trace(l_proc||': asg_max_end_date='||fnd_date.date_to_displaydate(asg_rec.asg_max_end_date));
2327: --
2328: FOR alc_rec IN c_allocations(asg_rec.assignment_id) LOOP
2329: -- Loop through all vehicle allocations
2330: -- over the date range
2327: --
2328: FOR alc_rec IN c_allocations(asg_rec.assignment_id) LOOP
2329: -- Loop through all vehicle allocations
2330: -- over the date range
2331: hr_utility.set_location(l_proc, 30);
2332: hr_utility.trace(l_proc||': vehicle_allocation_id='||alc_rec.vehicle_allocation_id);
2333: hr_utility.trace(l_proc||': usage_type='||alc_rec.usage_type);
2334: --
2335: L_action_flag := 'X';
2328: FOR alc_rec IN c_allocations(asg_rec.assignment_id) LOOP
2329: -- Loop through all vehicle allocations
2330: -- over the date range
2331: hr_utility.set_location(l_proc, 30);
2332: hr_utility.trace(l_proc||': vehicle_allocation_id='||alc_rec.vehicle_allocation_id);
2333: hr_utility.trace(l_proc||': usage_type='||alc_rec.usage_type);
2334: --
2335: L_action_flag := 'X';
2336: l_archived_flag := 'Y';
2329: -- Loop through all vehicle allocations
2330: -- over the date range
2331: hr_utility.set_location(l_proc, 30);
2332: hr_utility.trace(l_proc||': vehicle_allocation_id='||alc_rec.vehicle_allocation_id);
2333: hr_utility.trace(l_proc||': usage_type='||alc_rec.usage_type);
2334: --
2335: L_action_flag := 'X';
2336: l_archived_flag := 'Y';
2337: --
2338: OPEN c_alloc_dates(alc_rec.vehicle_allocation_id);
2339: FETCH c_alloc_dates INTO alc_dates_rec;
2340: CLOSE c_alloc_dates;
2341: --
2342: hr_utility.set_location(l_proc, 40);
2343: hr_utility.trace(l_proc||': min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
2344: hr_utility.trace(l_proc||': max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
2345: --
2346: -- Check whether vehicle was allocated or
2339: FETCH c_alloc_dates INTO alc_dates_rec;
2340: CLOSE c_alloc_dates;
2341: --
2342: hr_utility.set_location(l_proc, 40);
2343: hr_utility.trace(l_proc||': min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
2344: hr_utility.trace(l_proc||': max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
2345: --
2346: -- Check whether vehicle was allocated or
2347: -- Withdrawn within the date range also
2340: CLOSE c_alloc_dates;
2341: --
2342: hr_utility.set_location(l_proc, 40);
2343: hr_utility.trace(l_proc||': min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
2344: hr_utility.trace(l_proc||': max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
2345: --
2346: -- Check whether vehicle was allocated or
2347: -- Withdrawn within the date range also
2348: -- check that this allocation or withdrawal
2362: OPEN c_first_asg(asg_rec.assignment_id, alc_dates_rec.min_start_date);
2363: FETCH c_first_asg INTO first_asg_rec;
2364: CLOSE c_first_asg;
2365: --
2366: hr_utility.set_location(l_proc, 50);
2367: hr_utility.trace(l_proc||': First Assignment='|| first_asg_rec.assignment_id);
2368: -- Get primary car allocation of the
2369: -- first assignment of this person
2370: -- in this tax ref
2363: FETCH c_first_asg INTO first_asg_rec;
2364: CLOSE c_first_asg;
2365: --
2366: hr_utility.set_location(l_proc, 50);
2367: hr_utility.trace(l_proc||': First Assignment='|| first_asg_rec.assignment_id);
2368: -- Get primary car allocation of the
2369: -- first assignment of this person
2370: -- in this tax ref
2371: OPEN c_primary_car(first_asg_rec.assignment_id, alc_dates_rec.min_start_date);
2371: OPEN c_primary_car(first_asg_rec.assignment_id, alc_dates_rec.min_start_date);
2372: FETCH c_primary_car INTO primary_car_rec;
2373: CLOSE c_primary_car;
2374: --
2375: hr_utility.set_location(l_proc, 60);
2376: hr_utility.trace(l_proc||': Primary Car Allocation='|| primary_car_rec.vehicle_allocation_id);
2377: --
2378: IF primary_car_rec.vehicle_allocation_id = alc_rec.vehicle_allocation_id THEN
2379: -- This is the first car allocation
2372: FETCH c_primary_car INTO primary_car_rec;
2373: CLOSE c_primary_car;
2374: --
2375: hr_utility.set_location(l_proc, 60);
2376: hr_utility.trace(l_proc||': Primary Car Allocation='|| primary_car_rec.vehicle_allocation_id);
2377: --
2378: IF primary_car_rec.vehicle_allocation_id = alc_rec.vehicle_allocation_id THEN
2379: -- This is the first car allocation
2380: -- of this employee in this tax ref
2387: ELSE
2388: L_action_flag := 'N';
2389: END IF;
2390: --
2391: hr_utility.set_location(l_proc, 70);
2392: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2393: --
2394: CLOSE c_prior_prim_car;
2395: ELSE
2388: L_action_flag := 'N';
2389: END IF;
2390: --
2391: hr_utility.set_location(l_proc, 70);
2392: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2393: --
2394: CLOSE c_prior_prim_car;
2395: ELSE
2396: -- Not the first car of the employee
2397: -- report this as new car
2398: L_action_flag := 'N';
2399: END IF;
2400: --
2401: hr_utility.set_location(l_proc, 80);
2402: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2403: --
2404: ELSE
2405: -- Not a primary car therefore mark
2398: L_action_flag := 'N';
2399: END IF;
2400: --
2401: hr_utility.set_location(l_proc, 80);
2402: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2403: --
2404: ELSE
2405: -- Not a primary car therefore mark
2406: -- it as new car allocation action
2406: -- it as new car allocation action
2407: l_action_flag := 'N';
2408: END IF;
2409: --
2410: hr_utility.set_location(l_proc, 90);
2411: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2412: --
2413: IF L_action_flag <> 'X' THEN
2414: -- Check whether this allocation action
2407: l_action_flag := 'N';
2408: END IF;
2409: --
2410: hr_utility.set_location(l_proc, 90);
2411: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2412: --
2413: IF L_action_flag <> 'X' THEN
2414: -- Check whether this allocation action
2415: -- has been already archived
2418: alc_rec.vehicle_allocation_id,
2419: alc_dates_rec.min_start_date,
2420: l_action_flag);
2421: --
2422: hr_utility.set_location(l_proc, 100);
2423: hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
2424: --
2425: IF l_archived_flag = 'N' THEN
2426: -- Action has not been archived already
2419: alc_dates_rec.min_start_date,
2420: l_action_flag);
2421: --
2422: hr_utility.set_location(l_proc, 100);
2423: hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
2424: --
2425: IF l_archived_flag = 'N' THEN
2426: -- Action has not been archived already
2427: -- therefore archive it.
2424: --
2425: IF l_archived_flag = 'N' THEN
2426: -- Action has not been archived already
2427: -- therefore archive it.
2428: hr_utility.set_location(l_proc, 105);
2429: hr_utility.trace(l_proc||': p_assactid='|| p_assactid);
2430: hr_utility.trace(l_proc||': vehicle_allocation_id='|| alc_rec.vehicle_allocation_id);
2431: hr_utility.trace(l_proc||': vehicle_repository_id='|| alc_rec.vehicle_repository_id);
2432: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2425: IF l_archived_flag = 'N' THEN
2426: -- Action has not been archived already
2427: -- therefore archive it.
2428: hr_utility.set_location(l_proc, 105);
2429: hr_utility.trace(l_proc||': p_assactid='|| p_assactid);
2430: hr_utility.trace(l_proc||': vehicle_allocation_id='|| alc_rec.vehicle_allocation_id);
2431: hr_utility.trace(l_proc||': vehicle_repository_id='|| alc_rec.vehicle_repository_id);
2432: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2433: hr_utility.trace(l_proc||': alc_dates_rec.min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
2426: -- Action has not been archived already
2427: -- therefore archive it.
2428: hr_utility.set_location(l_proc, 105);
2429: hr_utility.trace(l_proc||': p_assactid='|| p_assactid);
2430: hr_utility.trace(l_proc||': vehicle_allocation_id='|| alc_rec.vehicle_allocation_id);
2431: hr_utility.trace(l_proc||': vehicle_repository_id='|| alc_rec.vehicle_repository_id);
2432: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2433: hr_utility.trace(l_proc||': alc_dates_rec.min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
2434: hr_utility.trace(l_proc||': alc_dates_rec.max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
2427: -- therefore archive it.
2428: hr_utility.set_location(l_proc, 105);
2429: hr_utility.trace(l_proc||': p_assactid='|| p_assactid);
2430: hr_utility.trace(l_proc||': vehicle_allocation_id='|| alc_rec.vehicle_allocation_id);
2431: hr_utility.trace(l_proc||': vehicle_repository_id='|| alc_rec.vehicle_repository_id);
2432: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2433: hr_utility.trace(l_proc||': alc_dates_rec.min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
2434: hr_utility.trace(l_proc||': alc_dates_rec.max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
2435: --
2428: hr_utility.set_location(l_proc, 105);
2429: hr_utility.trace(l_proc||': p_assactid='|| p_assactid);
2430: hr_utility.trace(l_proc||': vehicle_allocation_id='|| alc_rec.vehicle_allocation_id);
2431: hr_utility.trace(l_proc||': vehicle_repository_id='|| alc_rec.vehicle_repository_id);
2432: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2433: hr_utility.trace(l_proc||': alc_dates_rec.min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
2434: hr_utility.trace(l_proc||': alc_dates_rec.max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
2435: --
2436: archive_allocation_action(p_assignment_id => asg_rec.assignment_id,
2429: hr_utility.trace(l_proc||': p_assactid='|| p_assactid);
2430: hr_utility.trace(l_proc||': vehicle_allocation_id='|| alc_rec.vehicle_allocation_id);
2431: hr_utility.trace(l_proc||': vehicle_repository_id='|| alc_rec.vehicle_repository_id);
2432: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2433: hr_utility.trace(l_proc||': alc_dates_rec.min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
2434: hr_utility.trace(l_proc||': alc_dates_rec.max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
2435: --
2436: archive_allocation_action(p_assignment_id => asg_rec.assignment_id,
2437: p_asg_act_id => p_assactid,
2430: hr_utility.trace(l_proc||': vehicle_allocation_id='|| alc_rec.vehicle_allocation_id);
2431: hr_utility.trace(l_proc||': vehicle_repository_id='|| alc_rec.vehicle_repository_id);
2432: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2433: hr_utility.trace(l_proc||': alc_dates_rec.min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
2434: hr_utility.trace(l_proc||': alc_dates_rec.max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
2435: --
2436: archive_allocation_action(p_assignment_id => asg_rec.assignment_id,
2437: p_asg_act_id => p_assactid,
2438: p_vehicle_allocation_id => alc_rec.vehicle_allocation_id,
2461: OPEN c_first_asg(asg_rec.assignment_id, alc_dates_rec.max_end_date);
2462: FETCH c_first_asg INTO first_asg_rec;
2463: CLOSE c_first_asg;
2464: --
2465: hr_utility.set_location(l_proc, 50);
2466: hr_utility.trace(l_proc||': First Assignment='|| first_asg_rec.assignment_id);
2467: -- Get primary car allocation of the
2468: -- first assignment of this person
2469: -- in this tax ref
2462: FETCH c_first_asg INTO first_asg_rec;
2463: CLOSE c_first_asg;
2464: --
2465: hr_utility.set_location(l_proc, 50);
2466: hr_utility.trace(l_proc||': First Assignment='|| first_asg_rec.assignment_id);
2467: -- Get primary car allocation of the
2468: -- first assignment of this person
2469: -- in this tax ref
2470: OPEN c_primary_car(first_asg_rec.assignment_id, alc_dates_rec.max_end_date);
2470: OPEN c_primary_car(first_asg_rec.assignment_id, alc_dates_rec.max_end_date);
2471: FETCH c_primary_car INTO primary_car_rec;
2472: CLOSE c_primary_car;
2473: --
2474: hr_utility.set_location(l_proc, 60);
2475: hr_utility.trace(l_proc||': Primary Car Allocation='|| primary_car_rec.vehicle_allocation_id);
2476: --
2477: IF primary_car_rec.vehicle_allocation_id = alc_rec.vehicle_allocation_id THEN
2478: --
2471: FETCH c_primary_car INTO primary_car_rec;
2472: CLOSE c_primary_car;
2473: --
2474: hr_utility.set_location(l_proc, 60);
2475: hr_utility.trace(l_proc||': Primary Car Allocation='|| primary_car_rec.vehicle_allocation_id);
2476: --
2477: IF primary_car_rec.vehicle_allocation_id = alc_rec.vehicle_allocation_id THEN
2478: --
2479: OPEN c_next_prim_car(asg_rec.assignment_id, Alc_dates_rec.max_end_date);
2487: -- This is a withdrawal action
2488: L_action_flag := 'W';
2489: END IF;
2490: --
2491: hr_utility.set_location(l_proc, 70);
2492: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2493: --
2494: CLOSE c_next_prim_car;
2495: ELSE
2488: L_action_flag := 'W';
2489: END IF;
2490: --
2491: hr_utility.set_location(l_proc, 70);
2492: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2493: --
2494: CLOSE c_next_prim_car;
2495: ELSE
2496: -- Not the first car of the employee
2498: L_action_flag := 'W';
2499: END IF;
2500: --
2501: --
2502: hr_utility.set_location(l_proc, 80);
2503: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2504: --
2505: ELSE
2506: -- Not a first car therefore mark
2499: END IF;
2500: --
2501: --
2502: hr_utility.set_location(l_proc, 80);
2503: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2504: --
2505: ELSE
2506: -- Not a first car therefore mark
2507: -- it as withdrawal car action
2507: -- it as withdrawal car action
2508: l_action_flag := 'W';
2509: END IF;
2510: --
2511: hr_utility.set_location(l_proc, 90);
2512: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2513: --
2514: IF L_action_flag <> 'X' THEN
2515: -- Check whether this allocation action
2508: l_action_flag := 'W';
2509: END IF;
2510: --
2511: hr_utility.set_location(l_proc, 90);
2512: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2513: --
2514: IF L_action_flag <> 'X' THEN
2515: -- Check whether this allocation action
2516: -- has been already archived
2519: alc_rec.vehicle_allocation_id,
2520: alc_dates_rec.min_start_date,
2521: l_action_flag);
2522: --
2523: hr_utility.set_location(l_proc, 100);
2524: hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
2525: --
2526: IF l_archived_flag = 'N' THEN
2527: -- Action has not been archived already
2520: alc_dates_rec.min_start_date,
2521: l_action_flag);
2522: --
2523: hr_utility.set_location(l_proc, 100);
2524: hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
2525: --
2526: IF l_archived_flag = 'N' THEN
2527: -- Action has not been archived already
2528: -- therefore archive it.
2525: --
2526: IF l_archived_flag = 'N' THEN
2527: -- Action has not been archived already
2528: -- therefore archive it.
2529: hr_utility.set_location(l_proc, 105);
2530: hr_utility.trace(l_proc||': p_assactid='|| p_assactid);
2531: hr_utility.trace(l_proc||': vehicle_allocation_id='|| alc_rec.vehicle_allocation_id);
2532: hr_utility.trace(l_proc||': vehicle_repository_id='|| alc_rec.vehicle_repository_id);
2533: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2526: IF l_archived_flag = 'N' THEN
2527: -- Action has not been archived already
2528: -- therefore archive it.
2529: hr_utility.set_location(l_proc, 105);
2530: hr_utility.trace(l_proc||': p_assactid='|| p_assactid);
2531: hr_utility.trace(l_proc||': vehicle_allocation_id='|| alc_rec.vehicle_allocation_id);
2532: hr_utility.trace(l_proc||': vehicle_repository_id='|| alc_rec.vehicle_repository_id);
2533: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2534: hr_utility.trace(l_proc||': alc_dates_rec.min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
2527: -- Action has not been archived already
2528: -- therefore archive it.
2529: hr_utility.set_location(l_proc, 105);
2530: hr_utility.trace(l_proc||': p_assactid='|| p_assactid);
2531: hr_utility.trace(l_proc||': vehicle_allocation_id='|| alc_rec.vehicle_allocation_id);
2532: hr_utility.trace(l_proc||': vehicle_repository_id='|| alc_rec.vehicle_repository_id);
2533: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2534: hr_utility.trace(l_proc||': alc_dates_rec.min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
2535: hr_utility.trace(l_proc||': alc_dates_rec.max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
2528: -- therefore archive it.
2529: hr_utility.set_location(l_proc, 105);
2530: hr_utility.trace(l_proc||': p_assactid='|| p_assactid);
2531: hr_utility.trace(l_proc||': vehicle_allocation_id='|| alc_rec.vehicle_allocation_id);
2532: hr_utility.trace(l_proc||': vehicle_repository_id='|| alc_rec.vehicle_repository_id);
2533: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2534: hr_utility.trace(l_proc||': alc_dates_rec.min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
2535: hr_utility.trace(l_proc||': alc_dates_rec.max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
2536: --
2529: hr_utility.set_location(l_proc, 105);
2530: hr_utility.trace(l_proc||': p_assactid='|| p_assactid);
2531: hr_utility.trace(l_proc||': vehicle_allocation_id='|| alc_rec.vehicle_allocation_id);
2532: hr_utility.trace(l_proc||': vehicle_repository_id='|| alc_rec.vehicle_repository_id);
2533: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2534: hr_utility.trace(l_proc||': alc_dates_rec.min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
2535: hr_utility.trace(l_proc||': alc_dates_rec.max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
2536: --
2537: archive_allocation_action(p_assignment_id => asg_rec.assignment_id,
2530: hr_utility.trace(l_proc||': p_assactid='|| p_assactid);
2531: hr_utility.trace(l_proc||': vehicle_allocation_id='|| alc_rec.vehicle_allocation_id);
2532: hr_utility.trace(l_proc||': vehicle_repository_id='|| alc_rec.vehicle_repository_id);
2533: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2534: hr_utility.trace(l_proc||': alc_dates_rec.min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
2535: hr_utility.trace(l_proc||': alc_dates_rec.max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
2536: --
2537: archive_allocation_action(p_assignment_id => asg_rec.assignment_id,
2538: p_asg_act_id => p_assactid,
2531: hr_utility.trace(l_proc||': vehicle_allocation_id='|| alc_rec.vehicle_allocation_id);
2532: hr_utility.trace(l_proc||': vehicle_repository_id='|| alc_rec.vehicle_repository_id);
2533: hr_utility.trace(l_proc||': l_action_flag='|| l_action_flag);
2534: hr_utility.trace(l_proc||': alc_dates_rec.min_start_date='|| fnd_date.date_to_displaydate(alc_dates_rec.min_start_date));
2535: hr_utility.trace(l_proc||': alc_dates_rec.max_end_date='|| fnd_date.date_to_displaydate(alc_dates_rec.max_end_date));
2536: --
2537: archive_allocation_action(p_assignment_id => asg_rec.assignment_id,
2538: p_asg_act_id => p_assactid,
2539: p_vehicle_allocation_id => alc_rec.vehicle_allocation_id,
2550: -- Fuel type has changed therefore make sure
2551: -- this change occured after the vehicle was
2552: -- allocated to this assignment
2553: --
2554: hr_utility.set_location(l_proc, 110);
2555: hr_utility.trace(l_proc||': effective_start_date='|| vehicle_changes_rec.effective_start_date);
2556: hr_utility.trace(l_proc||': fuel_type='|| vehicle_changes_rec.fuel_type);
2557: --
2558: IF (vehicle_changes_rec.effective_start_date
2551: -- this change occured after the vehicle was
2552: -- allocated to this assignment
2553: --
2554: hr_utility.set_location(l_proc, 110);
2555: hr_utility.trace(l_proc||': effective_start_date='|| vehicle_changes_rec.effective_start_date);
2556: hr_utility.trace(l_proc||': fuel_type='|| vehicle_changes_rec.fuel_type);
2557: --
2558: IF (vehicle_changes_rec.effective_start_date
2559: BETWEEN alc_dates_rec.min_start_date+1 AND alc_dates_rec.max_end_date) AND
2552: -- allocated to this assignment
2553: --
2554: hr_utility.set_location(l_proc, 110);
2555: hr_utility.trace(l_proc||': effective_start_date='|| vehicle_changes_rec.effective_start_date);
2556: hr_utility.trace(l_proc||': fuel_type='|| vehicle_changes_rec.fuel_type);
2557: --
2558: IF (vehicle_changes_rec.effective_start_date
2559: BETWEEN alc_dates_rec.min_start_date+1 AND alc_dates_rec.max_end_date) AND
2560: (vehicle_changes_rec.effective_start_date
2567: alc_rec.vehicle_allocation_id,
2568: vehicle_changes_rec.effective_start_date,
2569: 'F');
2570: --
2571: hr_utility.set_location(l_proc, 120);
2572: hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
2573: --
2574: IF l_archived_flag = 'N' THEN
2575: -- Action has not been archived already
2568: vehicle_changes_rec.effective_start_date,
2569: 'F');
2570: --
2571: hr_utility.set_location(l_proc, 120);
2572: hr_utility.trace(l_proc||': l_archived_flag='|| l_archived_flag);
2573: --
2574: IF l_archived_flag = 'N' THEN
2575: -- Action has not been archived already
2576: -- therefore archive it.
2586: END IF;
2587: END IF;
2588: END LOOP;
2589: --
2590: hr_utility.set_location(l_proc, 130);
2591: --
2592: L_action_flag := 'X';
2593: l_archived_flag := 'Y';
2594: --
2602: -- Assignment has started on this payroll or tax ref during the
2603: -- input date range and this car allocation was active on the
2604: -- start date therefore check whether it is a transfer
2605: -- from another tax ref
2606: hr_utility.set_location(l_proc, 160);
2607: l_previous_tax_ref := NULL;
2608: --
2609: OPEN c_tax_ref(asg_rec.assignment_id, asg_rec.asg_min_start_date-1);
2610: FETCH c_tax_ref INTO l_previous_tax_ref;
2609: OPEN c_tax_ref(asg_rec.assignment_id, asg_rec.asg_min_start_date-1);
2610: FETCH c_tax_ref INTO l_previous_tax_ref;
2611: CLOSE c_tax_ref;
2612: --
2613: hr_utility.set_location(l_proc, 170);
2614: hr_utility.trace(l_proc||', l_previous_tax_ref='||l_previous_tax_ref);
2615: --
2616: IF l_previous_tax_ref is NOT NULL AND l_previous_tax_ref <> g_tax_ref THEN
2617: -- This assignment has a different prior tax ref
2610: FETCH c_tax_ref INTO l_previous_tax_ref;
2611: CLOSE c_tax_ref;
2612: --
2613: hr_utility.set_location(l_proc, 170);
2614: hr_utility.trace(l_proc||', l_previous_tax_ref='||l_previous_tax_ref);
2615: --
2616: IF l_previous_tax_ref is NOT NULL AND l_previous_tax_ref <> g_tax_ref THEN
2617: -- This assignment has a different prior tax ref
2618: -- therefore it should be reported as a new car
2616: IF l_previous_tax_ref is NOT NULL AND l_previous_tax_ref <> g_tax_ref THEN
2617: -- This assignment has a different prior tax ref
2618: -- therefore it should be reported as a new car
2619: -- allocation on this EDI message.
2620: hr_utility.set_location(l_proc, 180);
2621: l_action_flag := 'N';
2622: --
2623: -- Check whether this allocation action
2624: -- has been already archived
2627: alc_rec.vehicle_allocation_id,
2628: asg_rec.asg_min_start_date,
2629: l_action_flag);
2630: --
2631: hr_utility.set_location(l_proc, 190);
2632: hr_utility.trace(l_proc||', l_archived_flag='||l_archived_flag);
2633: --
2634: IF l_archived_flag = 'N' THEN
2635: -- Action has not been archived already
2628: asg_rec.asg_min_start_date,
2629: l_action_flag);
2630: --
2631: hr_utility.set_location(l_proc, 190);
2632: hr_utility.trace(l_proc||', l_archived_flag='||l_archived_flag);
2633: --
2634: IF l_archived_flag = 'N' THEN
2635: -- Action has not been archived already
2636: -- therefore archive it.
2654: -- Assignment has ended on this payroll or tax ref during the
2655: -- input date range and this car allocation was active on the
2656: -- end date therefore check whether it is a transfer
2657: -- to another tax ref
2658: hr_utility.set_location(l_proc, 200);
2659: l_next_tax_ref := NULL;
2660: --
2661: OPEN c_tax_ref(asg_rec.assignment_id, asg_rec.asg_max_end_date+1);
2662: FETCH c_tax_ref INTO l_next_tax_ref;
2661: OPEN c_tax_ref(asg_rec.assignment_id, asg_rec.asg_max_end_date+1);
2662: FETCH c_tax_ref INTO l_next_tax_ref;
2663: CLOSE c_tax_ref;
2664: --
2665: hr_utility.set_location(l_proc, 210);
2666: hr_utility.trace(l_proc||', l_next_tax_ref='||l_next_tax_ref);
2667: --
2668: IF l_next_tax_ref is NOT NULL AND l_next_tax_ref <> g_tax_ref THEN
2669: -- This assignment has a different tax ref after end date
2662: FETCH c_tax_ref INTO l_next_tax_ref;
2663: CLOSE c_tax_ref;
2664: --
2665: hr_utility.set_location(l_proc, 210);
2666: hr_utility.trace(l_proc||', l_next_tax_ref='||l_next_tax_ref);
2667: --
2668: IF l_next_tax_ref is NOT NULL AND l_next_tax_ref <> g_tax_ref THEN
2669: -- This assignment has a different tax ref after end date
2670: -- therefore it should be reported as allocation withdrawal
2668: IF l_next_tax_ref is NOT NULL AND l_next_tax_ref <> g_tax_ref THEN
2669: -- This assignment has a different tax ref after end date
2670: -- therefore it should be reported as allocation withdrawal
2671: -- on this EDI message.
2672: hr_utility.set_location(l_proc, 220);
2673: l_action_flag := 'W';
2674: --
2675: -- Check whether this allocation action
2676: -- has been already archived
2679: alc_rec.vehicle_allocation_id,
2680: asg_rec.asg_max_end_date,
2681: l_action_flag);
2682: --
2683: hr_utility.set_location(l_proc, 230);
2684: hr_utility.trace(l_proc||', l_archived_flag='||l_archived_flag);
2685: --
2686: IF l_archived_flag = 'N' THEN
2687: -- Action has not been archived already
2680: asg_rec.asg_max_end_date,
2681: l_action_flag);
2682: --
2683: hr_utility.set_location(l_proc, 230);
2684: hr_utility.trace(l_proc||', l_archived_flag='||l_archived_flag);
2685: --
2686: IF l_archived_flag = 'N' THEN
2687: -- Action has not been archived already
2688: -- therefore archive it.
2697: --
2698: END IF;
2699: END IF;
2700: --
2701: hr_utility.set_location(l_proc, 240);
2702: --
2703: hr_utility.set_location(l_proc, 250);
2704: END LOOP;
2705: --
2699: END IF;
2700: --
2701: hr_utility.set_location(l_proc, 240);
2702: --
2703: hr_utility.set_location(l_proc, 250);
2704: END LOOP;
2705: --
2706: hr_utility.set_location(l_proc, 260);
2707: --
2702: --
2703: hr_utility.set_location(l_proc, 250);
2704: END LOOP;
2705: --
2706: hr_utility.set_location(l_proc, 260);
2707: --
2708: END LOOP;
2709: --
2710: hr_utility.set_location('Leaving: '||l_proc,290);
2706: hr_utility.set_location(l_proc, 260);
2707: --
2708: END LOOP;
2709: --
2710: hr_utility.set_location('Leaving: '||l_proc,290);
2711: --hr_utility.trace_off;
2712: EXCEPTION
2713: WHEN OTHERS THEN
2714: hr_utility.set_location('Leaving: '||l_proc,300);
2707: --
2708: END LOOP;
2709: --
2710: hr_utility.set_location('Leaving: '||l_proc,290);
2711: --hr_utility.trace_off;
2712: EXCEPTION
2713: WHEN OTHERS THEN
2714: hr_utility.set_location('Leaving: '||l_proc,300);
2715: -- hr_utility.trace_off;
2710: hr_utility.set_location('Leaving: '||l_proc,290);
2711: --hr_utility.trace_off;
2712: EXCEPTION
2713: WHEN OTHERS THEN
2714: hr_utility.set_location('Leaving: '||l_proc,300);
2715: -- hr_utility.trace_off;
2716: raise;
2717: END archive_code_v2;
2718:
2711: --hr_utility.trace_off;
2712: EXCEPTION
2713: WHEN OTHERS THEN
2714: hr_utility.set_location('Leaving: '||l_proc,300);
2715: -- hr_utility.trace_off;
2716: raise;
2717: END archive_code_v2;
2718:
2719: END pay_gb_edi_p46_car;