[Home] [Help]
182: l_sunday_txt varchar2(100) := rtrim(to_char(to_date('07/01/2001','DD/MM/YYYY'),'DAY'));
183: --
184: begin
185: --
186: hr_utility.set_location('Entering:'||l_proc,1);
187: --
188: hr_utility.trace(' p_date: '||to_char (p_date));
189: --
190: if p_date is not null and p_date <> hr_general.end_of_time
184: begin
185: --
186: hr_utility.set_location('Entering:'||l_proc,1);
187: --
188: hr_utility.trace(' p_date: '||to_char (p_date));
189: --
190: if p_date is not null and p_date <> hr_general.end_of_time
191: then
192: if rtrim (to_char (p_date, 'DAY')) <> l_sunday_txt then
193: l_Sunday := next_day (p_date, l_sunday_txt) -7;
194: end if;
195: end if;
196: --
197: hr_utility.set_location('Leaving :'||l_proc,100);
198: --
199: return l_Sunday;
200: --
201: end start_of_week;
210: l_saturday_txt varchar2(100) := rtrim(to_char(to_date('06/01/2001','DD/MM/YYYY'),'DAY'));
211: --
212: begin
213: --
214: hr_utility.set_location('Entering:'||l_proc,1);
215: --
216: hr_utility.trace(' p_date: '||to_char (p_date));
217: --
218: if p_date is not null and p_date <> hr_general.end_of_time
212: begin
213: --
214: hr_utility.set_location('Entering:'||l_proc,1);
215: --
216: hr_utility.trace(' p_date: '||to_char (p_date));
217: --
218: if p_date is not null and p_date <> hr_general.end_of_time
219: then
220: if rtrim (to_char (p_date, 'DAY')) <> l_saturday_txt then
221: l_Saturday := next_day (p_date, l_saturday_txt);
222: end if;
223: end if;
224: --
225: hr_utility.set_location('Leaving :'||l_proc,100);
226: --
227: return l_Saturday;
228: --
229: end end_of_week;
251: and p_effective_date = trunc (p_effective_date));
252: --
253: begin
254: --
255: hr_utility.trace (l_proc||' p_effective_date = '
256: ||to_char (p_effective_date));
257: --
258: hr_general.assert_condition (all_parameters_valid);
259: --
260: end check_parameters;
261: --
262: begin
263: --
264: hr_utility.set_location('Entering:'||l_proc,1);
265: --
266: check_parameters;
267: --
268: open csr_LEL;
275: -- tax years before 95/96 so if the customer has need for the LEL value
276: -- before this date (eg for historic absences) then we must set the historic
277: -- value before returning.
278: --
279: hr_utility.trace (' No LEL defined on user tables; hard-coding value');
280: --
281: if p_effective_date between to_date ('06-04-1991', 'DD-MM-YYYY')
282: and to_date ('05-04-1992', 'DD-MM-YYYY')
283: then
299: l_LEL := 57.00; -- Tax Year 94/95
300: end if;
301: end if;
302: --
303: hr_utility.set_location('Leaving :'||l_proc, 100);
304: --
305: return l_LEL;
306: --
307: end NI_Lower_Earnings_Limit;
531: -- which is being modified by the code which caused the trigger to fire.
532: --
533: BEGIN
534: --
535: hr_utility.set_location('Entering:'||l_proc,1);
536: --
537: if ssp_ssp_pkg.ssp_is_installed
538: then
539: if csr_affected_PIWs%IsOpen then
549: -- Make sure all the linked PIWs are correctly defined.
550: ssp_ssp_pkg.update_linked_absence_IDs;
551: --
552: for each_PIW in csr_affected_PIWs LOOP
553: hr_utility.trace (' Recalculate SSP for PIW #'||
554: to_char(each_PIW.PIW_ID));
555: --
556: if each_PIW.locked <> 'Y'
557: then
565: --
566: -- Recalculate SMP for all maternities affected by inserts/updates/deletes
567: --
568: for each_maternity in csr_affected_maternities LOOP
569: hr_utility.trace (' Recalculate SMP for maternity_id # '
570: ||to_char (each_maternity.maternity_id));
571: --
572: update ssp_temp_affected_rows
573: set locked = 'Y'
600: close csr_count_absences;
601:
602: if l_deleting and l_count < 1
603: then
604: hr_utility.set_location('ssp_del_orphaned_rows:'||l_proc,50);
605: --
606: delete ssp_stoppages
607: where maternity_id = each_maternity.maternity_id;
608: end if;
613: or locked is null
614: or locked not in (select to_char(AUDSID) from v$session);
615: end if;
616: --
617: hr_utility.set_location('Leaving :'||l_proc,100);
618: --
619: exception
620: --
621: when mutating_table or row_deleted then
627: -- second and subsequent calls to do anything so just exit silently.
628: --
629: null;
630: --
631: hr_utility.set_location (l_proc,999);
632: --
633: end recalculate_SSP_and_SMP;
634: ------------------------------------------------------------------------------
635: procedure get_entry_details(p_date_earned in date,
728: p_date_earned is not null and
729: p_date_earned = trunc (p_date_earned) and
730: p_last_process_date = trunc (p_last_process_date));
731: begin
732: hr_utility.trace (' p_date_earned = '||to_char (p_date_earned, 'DD-MON-YYYY'));
733: hr_utility.trace (' p_last_process_date = '||to_char (p_last_process_date));
734: hr_utility.trace (' p_person_id = ' ||to_char (p_person_id));
735: hr_utility.trace (' p_element_type_id = '||to_char (p_element_type_id));
736: hr_utility.trace (' p_element_link_id = '||to_char (p_element_link_id));
729: p_date_earned = trunc (p_date_earned) and
730: p_last_process_date = trunc (p_last_process_date));
731: begin
732: hr_utility.trace (' p_date_earned = '||to_char (p_date_earned, 'DD-MON-YYYY'));
733: hr_utility.trace (' p_last_process_date = '||to_char (p_last_process_date));
734: hr_utility.trace (' p_person_id = ' ||to_char (p_person_id));
735: hr_utility.trace (' p_element_type_id = '||to_char (p_element_type_id));
736: hr_utility.trace (' p_element_link_id = '||to_char (p_element_link_id));
737: hr_utility.trace (' p_assignment_id = '||to_char (p_assignment_id));
730: p_last_process_date = trunc (p_last_process_date));
731: begin
732: hr_utility.trace (' p_date_earned = '||to_char (p_date_earned, 'DD-MON-YYYY'));
733: hr_utility.trace (' p_last_process_date = '||to_char (p_last_process_date));
734: hr_utility.trace (' p_person_id = ' ||to_char (p_person_id));
735: hr_utility.trace (' p_element_type_id = '||to_char (p_element_type_id));
736: hr_utility.trace (' p_element_link_id = '||to_char (p_element_link_id));
737: hr_utility.trace (' p_assignment_id = '||to_char (p_assignment_id));
738: hr_utility.trace (' p_pay_as_lump_sum = '||p_pay_as_lump_sum);
731: begin
732: hr_utility.trace (' p_date_earned = '||to_char (p_date_earned, 'DD-MON-YYYY'));
733: hr_utility.trace (' p_last_process_date = '||to_char (p_last_process_date));
734: hr_utility.trace (' p_person_id = ' ||to_char (p_person_id));
735: hr_utility.trace (' p_element_type_id = '||to_char (p_element_type_id));
736: hr_utility.trace (' p_element_link_id = '||to_char (p_element_link_id));
737: hr_utility.trace (' p_assignment_id = '||to_char (p_assignment_id));
738: hr_utility.trace (' p_pay_as_lump_sum = '||p_pay_as_lump_sum);
739: --
732: hr_utility.trace (' p_date_earned = '||to_char (p_date_earned, 'DD-MON-YYYY'));
733: hr_utility.trace (' p_last_process_date = '||to_char (p_last_process_date));
734: hr_utility.trace (' p_person_id = ' ||to_char (p_person_id));
735: hr_utility.trace (' p_element_type_id = '||to_char (p_element_type_id));
736: hr_utility.trace (' p_element_link_id = '||to_char (p_element_link_id));
737: hr_utility.trace (' p_assignment_id = '||to_char (p_assignment_id));
738: hr_utility.trace (' p_pay_as_lump_sum = '||p_pay_as_lump_sum);
739: --
740: hr_general.assert_condition (all_parameters_valid);
733: hr_utility.trace (' p_last_process_date = '||to_char (p_last_process_date));
734: hr_utility.trace (' p_person_id = ' ||to_char (p_person_id));
735: hr_utility.trace (' p_element_type_id = '||to_char (p_element_type_id));
736: hr_utility.trace (' p_element_link_id = '||to_char (p_element_link_id));
737: hr_utility.trace (' p_assignment_id = '||to_char (p_assignment_id));
738: hr_utility.trace (' p_pay_as_lump_sum = '||p_pay_as_lump_sum);
739: --
740: hr_general.assert_condition (all_parameters_valid);
741: --
734: hr_utility.trace (' p_person_id = ' ||to_char (p_person_id));
735: hr_utility.trace (' p_element_type_id = '||to_char (p_element_type_id));
736: hr_utility.trace (' p_element_link_id = '||to_char (p_element_link_id));
737: hr_utility.trace (' p_assignment_id = '||to_char (p_assignment_id));
738: hr_utility.trace (' p_pay_as_lump_sum = '||p_pay_as_lump_sum);
739: --
740: hr_general.assert_condition (all_parameters_valid);
741: --
742: end check_parameters;
742: end check_parameters;
743: --
744: begin
745: --
746: hr_utility.set_location ('Entering:'||l_proc,1);
747: --
748: check_parameters;
749: --
750: -- Get the effective date
755: if l_effective_date is null then
756: l_effective_date := trunc (sysdate);
757: end if;
758: --
759: hr_utility.trace(l_proc||' effective date = '||to_char(l_effective_date, 'DD-MON-YYYY'));
760: --
761: -- 1. Find out which date to use when searching for payroll period
762: if l_pay_as_lump_sum = 'Y' then
763: l_temp_date := l_effective_date;
814: else
815: l_found := true;
816: p_effective_start_date := l_effective_start_date;
817: p_effective_end_date := l_effective_end_date;
818: hr_utility.trace(l_proc||' l_effective_start_date = ' ||to_char(l_effective_start_date, 'DD-MON-YYYY'));
819: hr_utility.trace(l_proc||' l_effective_end_date = ' ||to_char(l_effective_end_date, 'DD-MON-YYYY'));
820: end if;
821: end if;
822: end loop;
815: l_found := true;
816: p_effective_start_date := l_effective_start_date;
817: p_effective_end_date := l_effective_end_date;
818: hr_utility.trace(l_proc||' l_effective_start_date = ' ||to_char(l_effective_start_date, 'DD-MON-YYYY'));
819: hr_utility.trace(l_proc||' l_effective_end_date = ' ||to_char(l_effective_end_date, 'DD-MON-YYYY'));
820: end if;
821: end if;
822: end loop;
823: --
825: p_element_link_id := hr_entry_api.get_link (p_assignment_id => p_assignment_id,
826: p_element_type_id => p_element_type_id,
827: p_session_date => p_effective_start_date);
828: --
829: hr_utility.set_location ('Leaving :'||l_proc,1);
830: --
831: exception
832: when cannot_derive_payroll_period then
833: fnd_message.set_name ('SSP', 'SSP_35029_NO_PAYROLL_PERIOD');
1109:
1110:
1111: begin
1112:
1113: hr_utility.trace('Entering ssp_entries_already_updated function');
1114:
1115: /* Find SSP rate for new tax year */
1116: open csr_new_ssp_rate;
1117: fetch csr_new_ssp_rate into l_new_SSP_rate;
1125: end if;
1126:
1127: close csr_new_ssp_rate;
1128:
1129: hr_utility.trace('New SSP rate: '||l_new_SSP_rate);
1130:
1131: /* Find first element entry value holding SSP rate for new tax year */
1132: open csr_first_new_ssp_entry;
1133: fetch csr_first_new_ssp_entry into l_first_SSP_entry_rate;
1141: end if;
1142:
1143: close csr_first_new_ssp_entry;
1144:
1145: hr_utility.trace('First SSP entry rate: '||l_first_SSP_entry_rate);
1146:
1147: if l_new_SSP_rate = l_first_SSP_entry_rate
1148: then
1149:
1252: l_old_std_smp_rate number;
1253:
1254: begin
1255:
1256: hr_utility.trace('Entering smp_rate_changed function');
1257: --
1258: /* Find SMP rate for new tax year */
1259: open csr_new_smp_rate;
1260: fetch csr_new_smp_rate into l_new_SMP_rate, l_new_high_smp_rate, l_new_std_smp_rate;
1268: end if;
1269: --
1270: close csr_new_smp_rate;
1271: --
1272: hr_utility.trace('New Lower SMP rate: '||l_new_SMP_rate);
1273: hr_utility.trace('New Higher SMP rate: '||l_new_high_SMP_rate);
1274: hr_utility.trace('New Standard SMP rate: '||l_new_std_SMP_rate);
1275: --
1276: /* Find SMP rate for current tax year */
1269: --
1270: close csr_new_smp_rate;
1271: --
1272: hr_utility.trace('New Lower SMP rate: '||l_new_SMP_rate);
1273: hr_utility.trace('New Higher SMP rate: '||l_new_high_SMP_rate);
1274: hr_utility.trace('New Standard SMP rate: '||l_new_std_SMP_rate);
1275: --
1276: /* Find SMP rate for current tax year */
1277: open csr_old_smp_rate;
1270: close csr_new_smp_rate;
1271: --
1272: hr_utility.trace('New Lower SMP rate: '||l_new_SMP_rate);
1273: hr_utility.trace('New Higher SMP rate: '||l_new_high_SMP_rate);
1274: hr_utility.trace('New Standard SMP rate: '||l_new_std_SMP_rate);
1275: --
1276: /* Find SMP rate for current tax year */
1277: open csr_old_smp_rate;
1278: fetch csr_old_smp_rate into l_old_SMP_rate, l_old_high_smp_rate, l_old_std_smp_rate;
1277: open csr_old_smp_rate;
1278: fetch csr_old_smp_rate into l_old_SMP_rate, l_old_high_smp_rate, l_old_std_smp_rate;
1279: close csr_old_smp_rate;
1280: --
1281: hr_utility.trace('Old SMP rate: '||l_old_SMP_rate);
1282: hr_utility.trace('Old Higher SMP rate: '||l_old_high_SMP_rate);
1283: hr_utility.trace('Old Standard SMP rate: '||l_old_std_SMP_rate);
1284: --
1285: if (l_new_SMP_rate = l_old_smp_rate) and (l_new_high_smp_rate = l_old_high_smp_rate) and (l_new_std_smp_rate = l_old_std_smp_rate)
1278: fetch csr_old_smp_rate into l_old_SMP_rate, l_old_high_smp_rate, l_old_std_smp_rate;
1279: close csr_old_smp_rate;
1280: --
1281: hr_utility.trace('Old SMP rate: '||l_old_SMP_rate);
1282: hr_utility.trace('Old Higher SMP rate: '||l_old_high_SMP_rate);
1283: hr_utility.trace('Old Standard SMP rate: '||l_old_std_SMP_rate);
1284: --
1285: if (l_new_SMP_rate = l_old_smp_rate) and (l_new_high_smp_rate = l_old_high_smp_rate) and (l_new_std_smp_rate = l_old_std_smp_rate)
1286: then
1279: close csr_old_smp_rate;
1280: --
1281: hr_utility.trace('Old SMP rate: '||l_old_SMP_rate);
1282: hr_utility.trace('Old Higher SMP rate: '||l_old_high_SMP_rate);
1283: hr_utility.trace('Old Standard SMP rate: '||l_old_std_SMP_rate);
1284: --
1285: if (l_new_SMP_rate = l_old_smp_rate) and (l_new_high_smp_rate = l_old_high_smp_rate) and (l_new_std_smp_rate = l_old_std_smp_rate)
1286: then
1287: return false;
1374: l_old_std_sap_rate number;
1375: --
1376: begin
1377: --
1378: hr_utility.trace('Entering sap_rate_changed function');
1379: --
1380: /* Find sap rate for new tax year */
1381: open csr_new_sap_rate;
1382: fetch csr_new_sap_rate into l_new_sap_rate, l_new_std_sap_rate;
1389: end if;
1390: --
1391: close csr_new_sap_rate;
1392: --
1393: hr_utility.trace('New sap rate: '||l_new_sap_rate);
1394: hr_utility.trace('New std sap rate: '||l_new_std_sap_rate);
1395: --
1396: /* Find sap rate for current tax year */
1397: open csr_old_sap_rate;
1390: --
1391: close csr_new_sap_rate;
1392: --
1393: hr_utility.trace('New sap rate: '||l_new_sap_rate);
1394: hr_utility.trace('New std sap rate: '||l_new_std_sap_rate);
1395: --
1396: /* Find sap rate for current tax year */
1397: open csr_old_sap_rate;
1398: fetch csr_old_sap_rate into l_old_sap_rate, l_old_std_sap_rate;
1397: open csr_old_sap_rate;
1398: fetch csr_old_sap_rate into l_old_sap_rate, l_old_std_sap_rate;
1399: close csr_old_sap_rate;
1400: --
1401: hr_utility.trace('Old sap rate: '||l_old_sap_rate);
1402: hr_utility.trace('Old std sap rate: '||l_old_std_sap_rate);
1403: --
1404: if (l_new_sap_rate = l_old_sap_rate) and (l_new_std_sap_rate = l_old_std_sap_rate) then
1405: return false;
1398: fetch csr_old_sap_rate into l_old_sap_rate, l_old_std_sap_rate;
1399: close csr_old_sap_rate;
1400: --
1401: hr_utility.trace('Old sap rate: '||l_old_sap_rate);
1402: hr_utility.trace('Old std sap rate: '||l_old_std_sap_rate);
1403: --
1404: if (l_new_sap_rate = l_old_sap_rate) and (l_new_std_sap_rate = l_old_std_sap_rate) then
1405: return false;
1406: else
1490: l_old_std_sppa_rate number;
1491: --
1492: begin
1493: --
1494: hr_utility.trace('Entering sppa_rate_changed function');
1495: --
1496: /* Find sppa rate for new tax year */
1497: open csr_new_sppa_rate;
1498: fetch csr_new_sppa_rate into l_new_sppa_rate, l_new_std_sppa_rate;
1505: end if;
1506: --
1507: close csr_new_sppa_rate;
1508: --
1509: hr_utility.trace('New sppa rate: '||l_new_sppa_rate);
1510: hr_utility.trace('New std sppa rate: '||l_new_std_sppa_rate);
1511: --
1512: open csr_old_sppa_rate;
1513: fetch csr_old_sppa_rate into l_old_sppa_rate, l_old_std_sppa_rate;
1506: --
1507: close csr_new_sppa_rate;
1508: --
1509: hr_utility.trace('New sppa rate: '||l_new_sppa_rate);
1510: hr_utility.trace('New std sppa rate: '||l_new_std_sppa_rate);
1511: --
1512: open csr_old_sppa_rate;
1513: fetch csr_old_sppa_rate into l_old_sppa_rate, l_old_std_sppa_rate;
1514: close csr_old_sppa_rate;
1512: open csr_old_sppa_rate;
1513: fetch csr_old_sppa_rate into l_old_sppa_rate, l_old_std_sppa_rate;
1514: close csr_old_sppa_rate;
1515: --
1516: hr_utility.trace('Old SPPA rate: '||l_old_sppa_rate);
1517: hr_utility.trace('Old Std SPPA rate: '||l_old_std_sppa_rate);
1518: --
1519: if (l_new_sppa_rate = l_old_sppa_rate) and (l_new_std_sppa_rate = l_old_std_sppa_rate) then
1520: return false;
1513: fetch csr_old_sppa_rate into l_old_sppa_rate, l_old_std_sppa_rate;
1514: close csr_old_sppa_rate;
1515: --
1516: hr_utility.trace('Old SPPA rate: '||l_old_sppa_rate);
1517: hr_utility.trace('Old Std SPPA rate: '||l_old_std_sppa_rate);
1518: --
1519: if (l_new_sppa_rate = l_old_sppa_rate) and (l_new_std_sppa_rate = l_old_std_sppa_rate) then
1520: return false;
1521: else
1604: l_old_std_sppb_rate number;
1605: --
1606: begin
1607: --
1608: hr_utility.trace('Entering sppb_rate_changed function');
1609: --
1610: /* Find SPPB rate for new tax year */
1611: open csr_new_sppb_rate;
1612: fetch csr_new_sppb_rate into l_new_sppb_rate, l_new_std_sppb_rate;
1619: end if;
1620: --
1621: close csr_new_sppb_rate;
1622: --
1623: hr_utility.trace('New SPPB rate: '||l_new_sppb_rate);
1624: hr_utility.trace('New Std SPPB rate: '||l_new_Std_sppb_rate);
1625: --
1626: open csr_old_sppb_rate;
1627: fetch csr_old_sppb_rate into l_old_sppb_rate, l_old_std_sppb_rate;
1620: --
1621: close csr_new_sppb_rate;
1622: --
1623: hr_utility.trace('New SPPB rate: '||l_new_sppb_rate);
1624: hr_utility.trace('New Std SPPB rate: '||l_new_Std_sppb_rate);
1625: --
1626: open csr_old_sppb_rate;
1627: fetch csr_old_sppb_rate into l_old_sppb_rate, l_old_std_sppb_rate;
1628: close csr_old_sppb_rate;
1626: open csr_old_sppb_rate;
1627: fetch csr_old_sppb_rate into l_old_sppb_rate, l_old_std_sppb_rate;
1628: close csr_old_sppb_rate;
1629: --
1630: hr_utility.trace('old SPPB rate: '||l_old_sppb_rate);
1631: hr_utility.trace('old Std SPPB rate: '||l_old_Std_sppb_rate);
1632: --
1633: if (l_new_sppb_rate = l_old_sppb_rate) and (l_new_std_sppb_rate = l_old_std_sppb_rate) then
1634: return false;
1627: fetch csr_old_sppb_rate into l_old_sppb_rate, l_old_std_sppb_rate;
1628: close csr_old_sppb_rate;
1629: --
1630: hr_utility.trace('old SPPB rate: '||l_old_sppb_rate);
1631: hr_utility.trace('old Std SPPB rate: '||l_old_Std_sppb_rate);
1632: --
1633: if (l_new_sppb_rate = l_old_sppb_rate) and (l_new_std_sppb_rate = l_old_std_sppb_rate) then
1634: return false;
1635: else
1642:
1643: /* Main program body */
1644: begin
1645:
1646: hr_utility.trace('Entering: '||g_package||'.update_ssp_smp_entries');
1647:
1648: savepoint pre_update_status;
1649: /* Check first whether SSP element entries already updated for new tax year */
1650: /* SSP update block */
1654:
1655: if not ssp_entries_already_updated
1656: then
1657:
1658: hr_utility.trace('SSP element entries not updated, updating ....');
1659:
1660: for r_affected_absences in csr_affected_absences loop
1661:
1662: /* SSP control call block */
1661:
1662: /* SSP control call block */
1663: begin
1664:
1665: hr_utility.trace('Processing SSP absence: '||r_affected_absences.absence_id);
1666:
1667: ssp_ssp_pkg.ssp_control(r_affected_absences.absence_id);
1668:
1669: l_count := l_count + 1;
1670:
1671: exception
1672:
1673: when others then
1674: hr_utility.trace('Error occurred while processing SSP absence: '||r_affected_absences.absence_id);
1675: hr_utility.trace('SQL error code: '||SQLCODE);
1676: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1677: hr_utility.trace('Person id: '||r_affected_absences.person_id);
1678: hr_utility.trace('Absence start date: '||r_affected_absences.absence_start_date);
1671: exception
1672:
1673: when others then
1674: hr_utility.trace('Error occurred while processing SSP absence: '||r_affected_absences.absence_id);
1675: hr_utility.trace('SQL error code: '||SQLCODE);
1676: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1677: hr_utility.trace('Person id: '||r_affected_absences.person_id);
1678: hr_utility.trace('Absence start date: '||r_affected_absences.absence_start_date);
1679: hr_utility.trace('Absence end date: '||r_affected_absences.absence_end_date);
1672:
1673: when others then
1674: hr_utility.trace('Error occurred while processing SSP absence: '||r_affected_absences.absence_id);
1675: hr_utility.trace('SQL error code: '||SQLCODE);
1676: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1677: hr_utility.trace('Person id: '||r_affected_absences.person_id);
1678: hr_utility.trace('Absence start date: '||r_affected_absences.absence_start_date);
1679: hr_utility.trace('Absence end date: '||r_affected_absences.absence_end_date);
1680: l_update_error := true;
1673: when others then
1674: hr_utility.trace('Error occurred while processing SSP absence: '||r_affected_absences.absence_id);
1675: hr_utility.trace('SQL error code: '||SQLCODE);
1676: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1677: hr_utility.trace('Person id: '||r_affected_absences.person_id);
1678: hr_utility.trace('Absence start date: '||r_affected_absences.absence_start_date);
1679: hr_utility.trace('Absence end date: '||r_affected_absences.absence_end_date);
1680: l_update_error := true;
1681:
1674: hr_utility.trace('Error occurred while processing SSP absence: '||r_affected_absences.absence_id);
1675: hr_utility.trace('SQL error code: '||SQLCODE);
1676: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1677: hr_utility.trace('Person id: '||r_affected_absences.person_id);
1678: hr_utility.trace('Absence start date: '||r_affected_absences.absence_start_date);
1679: hr_utility.trace('Absence end date: '||r_affected_absences.absence_end_date);
1680: l_update_error := true;
1681:
1682: end;
1675: hr_utility.trace('SQL error code: '||SQLCODE);
1676: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1677: hr_utility.trace('Person id: '||r_affected_absences.person_id);
1678: hr_utility.trace('Absence start date: '||r_affected_absences.absence_start_date);
1679: hr_utility.trace('Absence end date: '||r_affected_absences.absence_end_date);
1680: l_update_error := true;
1681:
1682: end;
1683:
1684:
1685:
1686: end loop;
1687:
1688: hr_utility.trace('Updated entries for '||l_count||' absences');
1689:
1690: else
1691:
1692: hr_utility.trace('SSP element entries already updated for new tax year');
1688: hr_utility.trace('Updated entries for '||l_count||' absences');
1689:
1690: else
1691:
1692: hr_utility.trace('SSP element entries already updated for new tax year');
1693:
1694: end if;
1695:
1696: commit;
1698: exception
1699:
1700: when e_ssp_rate_not_set
1701: then
1702: hr_utility.trace('Warning: SSP rate for new tax year not set');
1703: hr_utility.trace('Unable to proceed with updating SSP entries');
1704:
1705:
1706: when e_no_new_ssp_entry
1699:
1700: when e_ssp_rate_not_set
1701: then
1702: hr_utility.trace('Warning: SSP rate for new tax year not set');
1703: hr_utility.trace('Unable to proceed with updating SSP entries');
1704:
1705:
1706: when e_no_new_ssp_entry
1707: then
1704:
1705:
1706: when e_no_new_ssp_entry
1707: then
1708: hr_utility.trace('Warning: Unable to locate SSP entry in new tax year');
1709: hr_utility.trace('Entries for SSP absences in new tax year may be non-existent');
1710: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end dates of absences');
1711:
1712: when others
1705:
1706: when e_no_new_ssp_entry
1707: then
1708: hr_utility.trace('Warning: Unable to locate SSP entry in new tax year');
1709: hr_utility.trace('Entries for SSP absences in new tax year may be non-existent');
1710: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end dates of absences');
1711:
1712: when others
1713: then
1706: when e_no_new_ssp_entry
1707: then
1708: hr_utility.trace('Warning: Unable to locate SSP entry in new tax year');
1709: hr_utility.trace('Entries for SSP absences in new tax year may be non-existent');
1710: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end dates of absences');
1711:
1712: when others
1713: then
1714: hr_utility.trace('Unexpected error occurred inside SSP update block');
1710: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end dates of absences');
1711:
1712: when others
1713: then
1714: hr_utility.trace('Unexpected error occurred inside SSP update block');
1715: hr_utility.trace('SQL error number: '||SQLCODE);
1716: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1717: rollback to pre_ssp_update_status;
1718: l_update_error := true;
1711:
1712: when others
1713: then
1714: hr_utility.trace('Unexpected error occurred inside SSP update block');
1715: hr_utility.trace('SQL error number: '||SQLCODE);
1716: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1717: rollback to pre_ssp_update_status;
1718: l_update_error := true;
1719:
1712: when others
1713: then
1714: hr_utility.trace('Unexpected error occurred inside SSP update block');
1715: hr_utility.trace('SQL error number: '||SQLCODE);
1716: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1717: rollback to pre_ssp_update_status;
1718: l_update_error := true;
1719:
1720: end; /* SSP update block */
1727: savepoint pre_smp_update_status;
1728: --
1729: if smp_rate_changed then
1730: --
1731: hr_utility.trace('SMP element entries updating ....');
1732: for r_affected_maternities in csr_affected_leave('MA') loop
1733: /* SMP Control call block */
1734: begin
1735: hr_utility.trace('Processing SMP absence: '||r_affected_maternities.maternity_id);
1731: hr_utility.trace('SMP element entries updating ....');
1732: for r_affected_maternities in csr_affected_leave('MA') loop
1733: /* SMP Control call block */
1734: begin
1735: hr_utility.trace('Processing SMP absence: '||r_affected_maternities.maternity_id);
1736: ssp_smp_pkg.smp_control(p_maternity_id => r_affected_maternities.maternity_id,
1737: p_deleting => FALSE);
1738: l_mat_count := l_mat_count + 1;
1739: exception
1737: p_deleting => FALSE);
1738: l_mat_count := l_mat_count + 1;
1739: exception
1740: when others then
1741: hr_utility.trace('Error occurred while processing SMP absence: '||r_affected_maternities.maternity_id);
1742: hr_utility.trace('SQL error code: '||SQLCODE);
1743: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1744: hr_utility.trace('Person id: '||r_affected_maternities.person_id);
1745: hr_utility.trace('Maternity start date: '||r_affected_maternities.date_start);
1738: l_mat_count := l_mat_count + 1;
1739: exception
1740: when others then
1741: hr_utility.trace('Error occurred while processing SMP absence: '||r_affected_maternities.maternity_id);
1742: hr_utility.trace('SQL error code: '||SQLCODE);
1743: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1744: hr_utility.trace('Person id: '||r_affected_maternities.person_id);
1745: hr_utility.trace('Maternity start date: '||r_affected_maternities.date_start);
1746: hr_utility.trace('Maternity end date: '||r_affected_maternities.date_end);
1739: exception
1740: when others then
1741: hr_utility.trace('Error occurred while processing SMP absence: '||r_affected_maternities.maternity_id);
1742: hr_utility.trace('SQL error code: '||SQLCODE);
1743: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1744: hr_utility.trace('Person id: '||r_affected_maternities.person_id);
1745: hr_utility.trace('Maternity start date: '||r_affected_maternities.date_start);
1746: hr_utility.trace('Maternity end date: '||r_affected_maternities.date_end);
1747: hr_utility.trace('Maternity projected start date: '||r_affected_maternities.date_projected_start);
1740: when others then
1741: hr_utility.trace('Error occurred while processing SMP absence: '||r_affected_maternities.maternity_id);
1742: hr_utility.trace('SQL error code: '||SQLCODE);
1743: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1744: hr_utility.trace('Person id: '||r_affected_maternities.person_id);
1745: hr_utility.trace('Maternity start date: '||r_affected_maternities.date_start);
1746: hr_utility.trace('Maternity end date: '||r_affected_maternities.date_end);
1747: hr_utility.trace('Maternity projected start date: '||r_affected_maternities.date_projected_start);
1748: hr_utility.trace('Maternity projected end date: '||r_affected_maternities.date_projected_end);
1741: hr_utility.trace('Error occurred while processing SMP absence: '||r_affected_maternities.maternity_id);
1742: hr_utility.trace('SQL error code: '||SQLCODE);
1743: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1744: hr_utility.trace('Person id: '||r_affected_maternities.person_id);
1745: hr_utility.trace('Maternity start date: '||r_affected_maternities.date_start);
1746: hr_utility.trace('Maternity end date: '||r_affected_maternities.date_end);
1747: hr_utility.trace('Maternity projected start date: '||r_affected_maternities.date_projected_start);
1748: hr_utility.trace('Maternity projected end date: '||r_affected_maternities.date_projected_end);
1749: l_update_error := true;
1742: hr_utility.trace('SQL error code: '||SQLCODE);
1743: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1744: hr_utility.trace('Person id: '||r_affected_maternities.person_id);
1745: hr_utility.trace('Maternity start date: '||r_affected_maternities.date_start);
1746: hr_utility.trace('Maternity end date: '||r_affected_maternities.date_end);
1747: hr_utility.trace('Maternity projected start date: '||r_affected_maternities.date_projected_start);
1748: hr_utility.trace('Maternity projected end date: '||r_affected_maternities.date_projected_end);
1749: l_update_error := true;
1750: end;
1743: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1744: hr_utility.trace('Person id: '||r_affected_maternities.person_id);
1745: hr_utility.trace('Maternity start date: '||r_affected_maternities.date_start);
1746: hr_utility.trace('Maternity end date: '||r_affected_maternities.date_end);
1747: hr_utility.trace('Maternity projected start date: '||r_affected_maternities.date_projected_start);
1748: hr_utility.trace('Maternity projected end date: '||r_affected_maternities.date_projected_end);
1749: l_update_error := true;
1750: end;
1751: end loop;
1744: hr_utility.trace('Person id: '||r_affected_maternities.person_id);
1745: hr_utility.trace('Maternity start date: '||r_affected_maternities.date_start);
1746: hr_utility.trace('Maternity end date: '||r_affected_maternities.date_end);
1747: hr_utility.trace('Maternity projected start date: '||r_affected_maternities.date_projected_start);
1748: hr_utility.trace('Maternity projected end date: '||r_affected_maternities.date_projected_end);
1749: l_update_error := true;
1750: end;
1751: end loop;
1752: hr_utility.trace('Updated entries for '||l_mat_count||' maternities');
1748: hr_utility.trace('Maternity projected end date: '||r_affected_maternities.date_projected_end);
1749: l_update_error := true;
1750: end;
1751: end loop;
1752: hr_utility.trace('Updated entries for '||l_mat_count||' maternities');
1753: else
1754: hr_utility.trace('SMP element entries already updated for new tax year');
1755: end if;
1756: --
1750: end;
1751: end loop;
1752: hr_utility.trace('Updated entries for '||l_mat_count||' maternities');
1753: else
1754: hr_utility.trace('SMP element entries already updated for new tax year');
1755: end if;
1756: --
1757: commit;
1758: --
1757: commit;
1758: --
1759: exception
1760: when e_smp_rate_not_set then
1761: hr_utility.trace('Warning: SMP rate for new tax year not set');
1762: hr_utility.trace('Unable to proceed with updating SMP entries');
1763: when e_no_new_smp_entry then
1764: hr_utility.trace('Warning: Unable to locate SMP entry in new tax year');
1765: hr_utility.trace('Entries for SMP absences in new tax year may be non-existent');
1758: --
1759: exception
1760: when e_smp_rate_not_set then
1761: hr_utility.trace('Warning: SMP rate for new tax year not set');
1762: hr_utility.trace('Unable to proceed with updating SMP entries');
1763: when e_no_new_smp_entry then
1764: hr_utility.trace('Warning: Unable to locate SMP entry in new tax year');
1765: hr_utility.trace('Entries for SMP absences in new tax year may be non-existent');
1766: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
1760: when e_smp_rate_not_set then
1761: hr_utility.trace('Warning: SMP rate for new tax year not set');
1762: hr_utility.trace('Unable to proceed with updating SMP entries');
1763: when e_no_new_smp_entry then
1764: hr_utility.trace('Warning: Unable to locate SMP entry in new tax year');
1765: hr_utility.trace('Entries for SMP absences in new tax year may be non-existent');
1766: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
1767: when others then
1768: hr_utility.trace('Unexpected error occurred inside SMP update block');
1761: hr_utility.trace('Warning: SMP rate for new tax year not set');
1762: hr_utility.trace('Unable to proceed with updating SMP entries');
1763: when e_no_new_smp_entry then
1764: hr_utility.trace('Warning: Unable to locate SMP entry in new tax year');
1765: hr_utility.trace('Entries for SMP absences in new tax year may be non-existent');
1766: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
1767: when others then
1768: hr_utility.trace('Unexpected error occurred inside SMP update block');
1769: hr_utility.trace('SQL error number: '||SQLCODE);
1762: hr_utility.trace('Unable to proceed with updating SMP entries');
1763: when e_no_new_smp_entry then
1764: hr_utility.trace('Warning: Unable to locate SMP entry in new tax year');
1765: hr_utility.trace('Entries for SMP absences in new tax year may be non-existent');
1766: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
1767: when others then
1768: hr_utility.trace('Unexpected error occurred inside SMP update block');
1769: hr_utility.trace('SQL error number: '||SQLCODE);
1770: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1764: hr_utility.trace('Warning: Unable to locate SMP entry in new tax year');
1765: hr_utility.trace('Entries for SMP absences in new tax year may be non-existent');
1766: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
1767: when others then
1768: hr_utility.trace('Unexpected error occurred inside SMP update block');
1769: hr_utility.trace('SQL error number: '||SQLCODE);
1770: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1771: rollback to pre_smp_update_status;
1772: l_update_error := true;
1765: hr_utility.trace('Entries for SMP absences in new tax year may be non-existent');
1766: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
1767: when others then
1768: hr_utility.trace('Unexpected error occurred inside SMP update block');
1769: hr_utility.trace('SQL error number: '||SQLCODE);
1770: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1771: rollback to pre_smp_update_status;
1772: l_update_error := true;
1773: end; /* SMP update block */
1766: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
1767: when others then
1768: hr_utility.trace('Unexpected error occurred inside SMP update block');
1769: hr_utility.trace('SQL error number: '||SQLCODE);
1770: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1771: rollback to pre_smp_update_status;
1772: l_update_error := true;
1773: end; /* SMP update block */
1774: -- 05/12/2003 rmakhija: Uncommented section ends here
1779: savepoint pre_sap_update_status;
1780: --
1781: if sap_rate_changed then
1782: --
1783: hr_utility.trace('SAP element entries updating ....');
1784: for r_affected_adoption in csr_affected_leave('AD') loop
1785: /* SAP Control call block */
1786: begin
1787: hr_utility.trace('Processing SAP absence: '||r_affected_adoption.maternity_id);
1783: hr_utility.trace('SAP element entries updating ....');
1784: for r_affected_adoption in csr_affected_leave('AD') loop
1785: /* SAP Control call block */
1786: begin
1787: hr_utility.trace('Processing SAP absence: '||r_affected_adoption.maternity_id);
1788: ssp_sap_pkg.sap_control(p_maternity_id => r_affected_adoption.maternity_id,
1789: p_deleting => FALSE);
1790: l_adop_count := l_adop_count + 1;
1791: exception
1789: p_deleting => FALSE);
1790: l_adop_count := l_adop_count + 1;
1791: exception
1792: when others then
1793: hr_utility.trace('Error occurred while processing SAP absence: '||r_affected_adoption.maternity_id);
1794: hr_utility.trace('SQL error code: '||SQLCODE);
1795: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1796: hr_utility.trace('Person id: '||r_affected_adoption.person_id);
1797: hr_utility.trace('Adoption start date: '||r_affected_adoption.date_start);
1790: l_adop_count := l_adop_count + 1;
1791: exception
1792: when others then
1793: hr_utility.trace('Error occurred while processing SAP absence: '||r_affected_adoption.maternity_id);
1794: hr_utility.trace('SQL error code: '||SQLCODE);
1795: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1796: hr_utility.trace('Person id: '||r_affected_adoption.person_id);
1797: hr_utility.trace('Adoption start date: '||r_affected_adoption.date_start);
1798: hr_utility.trace('Adoption end date: '||r_affected_adoption.date_end);
1791: exception
1792: when others then
1793: hr_utility.trace('Error occurred while processing SAP absence: '||r_affected_adoption.maternity_id);
1794: hr_utility.trace('SQL error code: '||SQLCODE);
1795: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1796: hr_utility.trace('Person id: '||r_affected_adoption.person_id);
1797: hr_utility.trace('Adoption start date: '||r_affected_adoption.date_start);
1798: hr_utility.trace('Adoption end date: '||r_affected_adoption.date_end);
1799: hr_utility.trace('Adoption projected start date: '||r_affected_adoption.date_projected_start);
1792: when others then
1793: hr_utility.trace('Error occurred while processing SAP absence: '||r_affected_adoption.maternity_id);
1794: hr_utility.trace('SQL error code: '||SQLCODE);
1795: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1796: hr_utility.trace('Person id: '||r_affected_adoption.person_id);
1797: hr_utility.trace('Adoption start date: '||r_affected_adoption.date_start);
1798: hr_utility.trace('Adoption end date: '||r_affected_adoption.date_end);
1799: hr_utility.trace('Adoption projected start date: '||r_affected_adoption.date_projected_start);
1800: hr_utility.trace('Adoption projected end date: '||r_affected_adoption.date_projected_end);
1793: hr_utility.trace('Error occurred while processing SAP absence: '||r_affected_adoption.maternity_id);
1794: hr_utility.trace('SQL error code: '||SQLCODE);
1795: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1796: hr_utility.trace('Person id: '||r_affected_adoption.person_id);
1797: hr_utility.trace('Adoption start date: '||r_affected_adoption.date_start);
1798: hr_utility.trace('Adoption end date: '||r_affected_adoption.date_end);
1799: hr_utility.trace('Adoption projected start date: '||r_affected_adoption.date_projected_start);
1800: hr_utility.trace('Adoption projected end date: '||r_affected_adoption.date_projected_end);
1801: l_update_error := true;
1794: hr_utility.trace('SQL error code: '||SQLCODE);
1795: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1796: hr_utility.trace('Person id: '||r_affected_adoption.person_id);
1797: hr_utility.trace('Adoption start date: '||r_affected_adoption.date_start);
1798: hr_utility.trace('Adoption end date: '||r_affected_adoption.date_end);
1799: hr_utility.trace('Adoption projected start date: '||r_affected_adoption.date_projected_start);
1800: hr_utility.trace('Adoption projected end date: '||r_affected_adoption.date_projected_end);
1801: l_update_error := true;
1802: end;
1795: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1796: hr_utility.trace('Person id: '||r_affected_adoption.person_id);
1797: hr_utility.trace('Adoption start date: '||r_affected_adoption.date_start);
1798: hr_utility.trace('Adoption end date: '||r_affected_adoption.date_end);
1799: hr_utility.trace('Adoption projected start date: '||r_affected_adoption.date_projected_start);
1800: hr_utility.trace('Adoption projected end date: '||r_affected_adoption.date_projected_end);
1801: l_update_error := true;
1802: end;
1803: end loop;
1796: hr_utility.trace('Person id: '||r_affected_adoption.person_id);
1797: hr_utility.trace('Adoption start date: '||r_affected_adoption.date_start);
1798: hr_utility.trace('Adoption end date: '||r_affected_adoption.date_end);
1799: hr_utility.trace('Adoption projected start date: '||r_affected_adoption.date_projected_start);
1800: hr_utility.trace('Adoption projected end date: '||r_affected_adoption.date_projected_end);
1801: l_update_error := true;
1802: end;
1803: end loop;
1804: hr_utility.trace('Updated entries for '||l_adop_count||' adoptions');
1800: hr_utility.trace('Adoption projected end date: '||r_affected_adoption.date_projected_end);
1801: l_update_error := true;
1802: end;
1803: end loop;
1804: hr_utility.trace('Updated entries for '||l_adop_count||' adoptions');
1805: else
1806: hr_utility.trace('SAP element entries already updated for new tax year');
1807: end if;
1808: --
1802: end;
1803: end loop;
1804: hr_utility.trace('Updated entries for '||l_adop_count||' adoptions');
1805: else
1806: hr_utility.trace('SAP element entries already updated for new tax year');
1807: end if;
1808: --
1809: commit;
1810: --
1809: commit;
1810: --
1811: exception
1812: when e_sap_rate_not_set then
1813: hr_utility.trace('Warning: SAP rate for new tax year not set');
1814: hr_utility.trace('Unable to proceed with updating SAP entries');
1815: when e_no_new_sap_entry then
1816: hr_utility.trace('Warning: Unable to locate SAP entry in new tax year');
1817: hr_utility.trace('Entries for SAP absences in new tax year may be non-existent');
1810: --
1811: exception
1812: when e_sap_rate_not_set then
1813: hr_utility.trace('Warning: SAP rate for new tax year not set');
1814: hr_utility.trace('Unable to proceed with updating SAP entries');
1815: when e_no_new_sap_entry then
1816: hr_utility.trace('Warning: Unable to locate SAP entry in new tax year');
1817: hr_utility.trace('Entries for SAP absences in new tax year may be non-existent');
1818: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
1812: when e_sap_rate_not_set then
1813: hr_utility.trace('Warning: SAP rate for new tax year not set');
1814: hr_utility.trace('Unable to proceed with updating SAP entries');
1815: when e_no_new_sap_entry then
1816: hr_utility.trace('Warning: Unable to locate SAP entry in new tax year');
1817: hr_utility.trace('Entries for SAP absences in new tax year may be non-existent');
1818: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
1819: when others then
1820: hr_utility.trace('Unexpected error occurred inside SAP update block');
1813: hr_utility.trace('Warning: SAP rate for new tax year not set');
1814: hr_utility.trace('Unable to proceed with updating SAP entries');
1815: when e_no_new_sap_entry then
1816: hr_utility.trace('Warning: Unable to locate SAP entry in new tax year');
1817: hr_utility.trace('Entries for SAP absences in new tax year may be non-existent');
1818: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
1819: when others then
1820: hr_utility.trace('Unexpected error occurred inside SAP update block');
1821: hr_utility.trace('SQL error number: '||SQLCODE);
1814: hr_utility.trace('Unable to proceed with updating SAP entries');
1815: when e_no_new_sap_entry then
1816: hr_utility.trace('Warning: Unable to locate SAP entry in new tax year');
1817: hr_utility.trace('Entries for SAP absences in new tax year may be non-existent');
1818: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
1819: when others then
1820: hr_utility.trace('Unexpected error occurred inside SAP update block');
1821: hr_utility.trace('SQL error number: '||SQLCODE);
1822: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1816: hr_utility.trace('Warning: Unable to locate SAP entry in new tax year');
1817: hr_utility.trace('Entries for SAP absences in new tax year may be non-existent');
1818: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
1819: when others then
1820: hr_utility.trace('Unexpected error occurred inside SAP update block');
1821: hr_utility.trace('SQL error number: '||SQLCODE);
1822: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1823: rollback to pre_sap_update_status;
1824: l_update_error := true;
1817: hr_utility.trace('Entries for SAP absences in new tax year may be non-existent');
1818: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
1819: when others then
1820: hr_utility.trace('Unexpected error occurred inside SAP update block');
1821: hr_utility.trace('SQL error number: '||SQLCODE);
1822: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1823: rollback to pre_sap_update_status;
1824: l_update_error := true;
1825: end; /* SAP update block */
1818: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
1819: when others then
1820: hr_utility.trace('Unexpected error occurred inside SAP update block');
1821: hr_utility.trace('SQL error number: '||SQLCODE);
1822: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1823: rollback to pre_sap_update_status;
1824: l_update_error := true;
1825: end; /* SAP update block */
1826: -- Begin SPP Adoption Update Block
1829: savepoint pre_sppa_update_status;
1830: --
1831: if sppa_rate_changed then
1832: --
1833: hr_utility.trace('SPP Adoption element entries updating ....');
1834: for r_affected_pat_adop in csr_affected_leave('PA') loop
1835: /* SPP Adoption Control call block */
1836: begin
1837: hr_utility.trace('Processing SPP Adoption absence: '||r_affected_pat_adop.maternity_id);
1833: hr_utility.trace('SPP Adoption element entries updating ....');
1834: for r_affected_pat_adop in csr_affected_leave('PA') loop
1835: /* SPP Adoption Control call block */
1836: begin
1837: hr_utility.trace('Processing SPP Adoption absence: '||r_affected_pat_adop.maternity_id);
1838: ssp_pad_pkg.pad_control(p_maternity_id => r_affected_pat_adop.maternity_id,
1839: p_deleting => FALSE);
1840: l_pat_adop_count := l_pat_adop_count + 1;
1841: exception
1839: p_deleting => FALSE);
1840: l_pat_adop_count := l_pat_adop_count + 1;
1841: exception
1842: when others then
1843: hr_utility.trace('Error occurred while processing SPP Adoption absence: '||r_affected_pat_adop.maternity_id);
1844: hr_utility.trace('SQL error code: '||SQLCODE);
1845: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1846: hr_utility.trace('Person id: '||r_affected_pat_adop.person_id);
1847: hr_utility.trace('Paternity Adoption start date: '||r_affected_pat_adop.date_start);
1840: l_pat_adop_count := l_pat_adop_count + 1;
1841: exception
1842: when others then
1843: hr_utility.trace('Error occurred while processing SPP Adoption absence: '||r_affected_pat_adop.maternity_id);
1844: hr_utility.trace('SQL error code: '||SQLCODE);
1845: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1846: hr_utility.trace('Person id: '||r_affected_pat_adop.person_id);
1847: hr_utility.trace('Paternity Adoption start date: '||r_affected_pat_adop.date_start);
1848: hr_utility.trace('Paternity Adoption end date: '||r_affected_pat_adop.date_end);
1841: exception
1842: when others then
1843: hr_utility.trace('Error occurred while processing SPP Adoption absence: '||r_affected_pat_adop.maternity_id);
1844: hr_utility.trace('SQL error code: '||SQLCODE);
1845: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1846: hr_utility.trace('Person id: '||r_affected_pat_adop.person_id);
1847: hr_utility.trace('Paternity Adoption start date: '||r_affected_pat_adop.date_start);
1848: hr_utility.trace('Paternity Adoption end date: '||r_affected_pat_adop.date_end);
1849: hr_utility.trace('Paternity Adoption projected start date: '||r_affected_pat_adop.date_projected_start);
1842: when others then
1843: hr_utility.trace('Error occurred while processing SPP Adoption absence: '||r_affected_pat_adop.maternity_id);
1844: hr_utility.trace('SQL error code: '||SQLCODE);
1845: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1846: hr_utility.trace('Person id: '||r_affected_pat_adop.person_id);
1847: hr_utility.trace('Paternity Adoption start date: '||r_affected_pat_adop.date_start);
1848: hr_utility.trace('Paternity Adoption end date: '||r_affected_pat_adop.date_end);
1849: hr_utility.trace('Paternity Adoption projected start date: '||r_affected_pat_adop.date_projected_start);
1850: hr_utility.trace('Paternity Adoption projected end date: '||r_affected_pat_adop.date_projected_end);
1843: hr_utility.trace('Error occurred while processing SPP Adoption absence: '||r_affected_pat_adop.maternity_id);
1844: hr_utility.trace('SQL error code: '||SQLCODE);
1845: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1846: hr_utility.trace('Person id: '||r_affected_pat_adop.person_id);
1847: hr_utility.trace('Paternity Adoption start date: '||r_affected_pat_adop.date_start);
1848: hr_utility.trace('Paternity Adoption end date: '||r_affected_pat_adop.date_end);
1849: hr_utility.trace('Paternity Adoption projected start date: '||r_affected_pat_adop.date_projected_start);
1850: hr_utility.trace('Paternity Adoption projected end date: '||r_affected_pat_adop.date_projected_end);
1851: l_update_error := true;
1844: hr_utility.trace('SQL error code: '||SQLCODE);
1845: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1846: hr_utility.trace('Person id: '||r_affected_pat_adop.person_id);
1847: hr_utility.trace('Paternity Adoption start date: '||r_affected_pat_adop.date_start);
1848: hr_utility.trace('Paternity Adoption end date: '||r_affected_pat_adop.date_end);
1849: hr_utility.trace('Paternity Adoption projected start date: '||r_affected_pat_adop.date_projected_start);
1850: hr_utility.trace('Paternity Adoption projected end date: '||r_affected_pat_adop.date_projected_end);
1851: l_update_error := true;
1852: end;
1845: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1846: hr_utility.trace('Person id: '||r_affected_pat_adop.person_id);
1847: hr_utility.trace('Paternity Adoption start date: '||r_affected_pat_adop.date_start);
1848: hr_utility.trace('Paternity Adoption end date: '||r_affected_pat_adop.date_end);
1849: hr_utility.trace('Paternity Adoption projected start date: '||r_affected_pat_adop.date_projected_start);
1850: hr_utility.trace('Paternity Adoption projected end date: '||r_affected_pat_adop.date_projected_end);
1851: l_update_error := true;
1852: end;
1853: end loop;
1846: hr_utility.trace('Person id: '||r_affected_pat_adop.person_id);
1847: hr_utility.trace('Paternity Adoption start date: '||r_affected_pat_adop.date_start);
1848: hr_utility.trace('Paternity Adoption end date: '||r_affected_pat_adop.date_end);
1849: hr_utility.trace('Paternity Adoption projected start date: '||r_affected_pat_adop.date_projected_start);
1850: hr_utility.trace('Paternity Adoption projected end date: '||r_affected_pat_adop.date_projected_end);
1851: l_update_error := true;
1852: end;
1853: end loop;
1854: hr_utility.trace('Updated entries for '||l_pat_adop_count||' paternity adoptions');
1850: hr_utility.trace('Paternity Adoption projected end date: '||r_affected_pat_adop.date_projected_end);
1851: l_update_error := true;
1852: end;
1853: end loop;
1854: hr_utility.trace('Updated entries for '||l_pat_adop_count||' paternity adoptions');
1855: else
1856: hr_utility.trace('SPP Adoption element entries already updated for new tax year');
1857: end if;
1858: --
1852: end;
1853: end loop;
1854: hr_utility.trace('Updated entries for '||l_pat_adop_count||' paternity adoptions');
1855: else
1856: hr_utility.trace('SPP Adoption element entries already updated for new tax year');
1857: end if;
1858: --
1859: commit;
1860: --
1859: commit;
1860: --
1861: exception
1862: when e_sppa_rate_not_set then
1863: hr_utility.trace('Warning: SPP ADoption rate for new tax year not set');
1864: hr_utility.trace('Unable to proceed with updating SPP ADoption entries');
1865: when e_no_new_sppa_entry then
1866: hr_utility.trace('Warning: Unable to locate SPP ADoption entry in new tax year');
1867: hr_utility.trace('Entries for SPP ADoption absences in new tax year may be non-existent');
1860: --
1861: exception
1862: when e_sppa_rate_not_set then
1863: hr_utility.trace('Warning: SPP ADoption rate for new tax year not set');
1864: hr_utility.trace('Unable to proceed with updating SPP ADoption entries');
1865: when e_no_new_sppa_entry then
1866: hr_utility.trace('Warning: Unable to locate SPP ADoption entry in new tax year');
1867: hr_utility.trace('Entries for SPP ADoption absences in new tax year may be non-existent');
1868: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
1862: when e_sppa_rate_not_set then
1863: hr_utility.trace('Warning: SPP ADoption rate for new tax year not set');
1864: hr_utility.trace('Unable to proceed with updating SPP ADoption entries');
1865: when e_no_new_sppa_entry then
1866: hr_utility.trace('Warning: Unable to locate SPP ADoption entry in new tax year');
1867: hr_utility.trace('Entries for SPP ADoption absences in new tax year may be non-existent');
1868: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
1869: when others then
1870: hr_utility.trace('Unexpected error occurred inside SPP ADoption update block');
1863: hr_utility.trace('Warning: SPP ADoption rate for new tax year not set');
1864: hr_utility.trace('Unable to proceed with updating SPP ADoption entries');
1865: when e_no_new_sppa_entry then
1866: hr_utility.trace('Warning: Unable to locate SPP ADoption entry in new tax year');
1867: hr_utility.trace('Entries for SPP ADoption absences in new tax year may be non-existent');
1868: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
1869: when others then
1870: hr_utility.trace('Unexpected error occurred inside SPP ADoption update block');
1871: hr_utility.trace('SQL error number: '||SQLCODE);
1864: hr_utility.trace('Unable to proceed with updating SPP ADoption entries');
1865: when e_no_new_sppa_entry then
1866: hr_utility.trace('Warning: Unable to locate SPP ADoption entry in new tax year');
1867: hr_utility.trace('Entries for SPP ADoption absences in new tax year may be non-existent');
1868: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
1869: when others then
1870: hr_utility.trace('Unexpected error occurred inside SPP ADoption update block');
1871: hr_utility.trace('SQL error number: '||SQLCODE);
1872: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1866: hr_utility.trace('Warning: Unable to locate SPP ADoption entry in new tax year');
1867: hr_utility.trace('Entries for SPP ADoption absences in new tax year may be non-existent');
1868: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
1869: when others then
1870: hr_utility.trace('Unexpected error occurred inside SPP ADoption update block');
1871: hr_utility.trace('SQL error number: '||SQLCODE);
1872: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1873: rollback to pre_sppa_update_status;
1874: l_update_error := true;
1867: hr_utility.trace('Entries for SPP ADoption absences in new tax year may be non-existent');
1868: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
1869: when others then
1870: hr_utility.trace('Unexpected error occurred inside SPP ADoption update block');
1871: hr_utility.trace('SQL error number: '||SQLCODE);
1872: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1873: rollback to pre_sppa_update_status;
1874: l_update_error := true;
1875: end; /* SPP Adoption update block */
1868: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
1869: when others then
1870: hr_utility.trace('Unexpected error occurred inside SPP ADoption update block');
1871: hr_utility.trace('SQL error number: '||SQLCODE);
1872: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1873: rollback to pre_sppa_update_status;
1874: l_update_error := true;
1875: end; /* SPP Adoption update block */
1876: -- Begin SPP Birth Update Block
1879: savepoint pre_sppb_update_status;
1880: --
1881: if sppb_rate_changed then
1882: --
1883: hr_utility.trace('SPP Birth element entries updating ....');
1884: for r_affected_pat_Birth in csr_affected_leave('PB') loop
1885: /* SPP Birth Control call block */
1886: begin
1887: hr_utility.trace('Processing SPP Birth absence: '||r_affected_pat_Birth.maternity_id);
1883: hr_utility.trace('SPP Birth element entries updating ....');
1884: for r_affected_pat_Birth in csr_affected_leave('PB') loop
1885: /* SPP Birth Control call block */
1886: begin
1887: hr_utility.trace('Processing SPP Birth absence: '||r_affected_pat_Birth.maternity_id);
1888: ssp_pab_pkg.pab_control(p_maternity_id => r_affected_pat_Birth.maternity_id,
1889: p_deleting => FALSE);
1890: l_pat_Birth_count := l_pat_birth_count + 1;
1891: exception
1889: p_deleting => FALSE);
1890: l_pat_Birth_count := l_pat_birth_count + 1;
1891: exception
1892: when others then
1893: hr_utility.trace('Error occurred while processing SPP Birth absence: '||r_affected_pat_Birth.maternity_id);
1894: hr_utility.trace('SQL error code: '||SQLCODE);
1895: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1896: hr_utility.trace('Person id: '||r_affected_pat_Birth.person_id);
1897: hr_utility.trace('Paternity Birth start date: '||r_affected_pat_Birth.date_start);
1890: l_pat_Birth_count := l_pat_birth_count + 1;
1891: exception
1892: when others then
1893: hr_utility.trace('Error occurred while processing SPP Birth absence: '||r_affected_pat_Birth.maternity_id);
1894: hr_utility.trace('SQL error code: '||SQLCODE);
1895: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1896: hr_utility.trace('Person id: '||r_affected_pat_Birth.person_id);
1897: hr_utility.trace('Paternity Birth start date: '||r_affected_pat_Birth.date_start);
1898: hr_utility.trace('Paternity Birth end date: '||r_affected_pat_Birth.date_end);
1891: exception
1892: when others then
1893: hr_utility.trace('Error occurred while processing SPP Birth absence: '||r_affected_pat_Birth.maternity_id);
1894: hr_utility.trace('SQL error code: '||SQLCODE);
1895: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1896: hr_utility.trace('Person id: '||r_affected_pat_Birth.person_id);
1897: hr_utility.trace('Paternity Birth start date: '||r_affected_pat_Birth.date_start);
1898: hr_utility.trace('Paternity Birth end date: '||r_affected_pat_Birth.date_end);
1899: hr_utility.trace('Paternity Birth projected start date: '||r_affected_pat_Birth.date_projected_start);
1892: when others then
1893: hr_utility.trace('Error occurred while processing SPP Birth absence: '||r_affected_pat_Birth.maternity_id);
1894: hr_utility.trace('SQL error code: '||SQLCODE);
1895: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1896: hr_utility.trace('Person id: '||r_affected_pat_Birth.person_id);
1897: hr_utility.trace('Paternity Birth start date: '||r_affected_pat_Birth.date_start);
1898: hr_utility.trace('Paternity Birth end date: '||r_affected_pat_Birth.date_end);
1899: hr_utility.trace('Paternity Birth projected start date: '||r_affected_pat_Birth.date_projected_start);
1900: hr_utility.trace('Paternity Birth projected end date: '||r_affected_pat_Birth.date_projected_end);
1893: hr_utility.trace('Error occurred while processing SPP Birth absence: '||r_affected_pat_Birth.maternity_id);
1894: hr_utility.trace('SQL error code: '||SQLCODE);
1895: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1896: hr_utility.trace('Person id: '||r_affected_pat_Birth.person_id);
1897: hr_utility.trace('Paternity Birth start date: '||r_affected_pat_Birth.date_start);
1898: hr_utility.trace('Paternity Birth end date: '||r_affected_pat_Birth.date_end);
1899: hr_utility.trace('Paternity Birth projected start date: '||r_affected_pat_Birth.date_projected_start);
1900: hr_utility.trace('Paternity Birth projected end date: '||r_affected_pat_Birth.date_projected_end);
1901: l_update_error := true;
1894: hr_utility.trace('SQL error code: '||SQLCODE);
1895: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1896: hr_utility.trace('Person id: '||r_affected_pat_Birth.person_id);
1897: hr_utility.trace('Paternity Birth start date: '||r_affected_pat_Birth.date_start);
1898: hr_utility.trace('Paternity Birth end date: '||r_affected_pat_Birth.date_end);
1899: hr_utility.trace('Paternity Birth projected start date: '||r_affected_pat_Birth.date_projected_start);
1900: hr_utility.trace('Paternity Birth projected end date: '||r_affected_pat_Birth.date_projected_end);
1901: l_update_error := true;
1902: end;
1895: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1896: hr_utility.trace('Person id: '||r_affected_pat_Birth.person_id);
1897: hr_utility.trace('Paternity Birth start date: '||r_affected_pat_Birth.date_start);
1898: hr_utility.trace('Paternity Birth end date: '||r_affected_pat_Birth.date_end);
1899: hr_utility.trace('Paternity Birth projected start date: '||r_affected_pat_Birth.date_projected_start);
1900: hr_utility.trace('Paternity Birth projected end date: '||r_affected_pat_Birth.date_projected_end);
1901: l_update_error := true;
1902: end;
1903: end loop;
1896: hr_utility.trace('Person id: '||r_affected_pat_Birth.person_id);
1897: hr_utility.trace('Paternity Birth start date: '||r_affected_pat_Birth.date_start);
1898: hr_utility.trace('Paternity Birth end date: '||r_affected_pat_Birth.date_end);
1899: hr_utility.trace('Paternity Birth projected start date: '||r_affected_pat_Birth.date_projected_start);
1900: hr_utility.trace('Paternity Birth projected end date: '||r_affected_pat_Birth.date_projected_end);
1901: l_update_error := true;
1902: end;
1903: end loop;
1904: hr_utility.trace('Updated entries for '||l_pat_birth_count||' paternity births');
1900: hr_utility.trace('Paternity Birth projected end date: '||r_affected_pat_Birth.date_projected_end);
1901: l_update_error := true;
1902: end;
1903: end loop;
1904: hr_utility.trace('Updated entries for '||l_pat_birth_count||' paternity births');
1905: else
1906: hr_utility.trace('SPP Birth element entries already updated for new tax year');
1907: end if;
1908: --
1902: end;
1903: end loop;
1904: hr_utility.trace('Updated entries for '||l_pat_birth_count||' paternity births');
1905: else
1906: hr_utility.trace('SPP Birth element entries already updated for new tax year');
1907: end if;
1908: --
1909: commit;
1910: --
1909: commit;
1910: --
1911: exception
1912: when e_sppb_rate_not_set then
1913: hr_utility.trace('Warning: SPP Birth rate for new tax year not set');
1914: hr_utility.trace('Unable to proceed with updating SPP Birth entries');
1915: when e_no_new_sppb_entry then
1916: hr_utility.trace('Warning: Unable to locate SPP Birth entry in new tax year');
1917: hr_utility.trace('Entries for SPP Birth absences in new tax year may be non-existent');
1910: --
1911: exception
1912: when e_sppb_rate_not_set then
1913: hr_utility.trace('Warning: SPP Birth rate for new tax year not set');
1914: hr_utility.trace('Unable to proceed with updating SPP Birth entries');
1915: when e_no_new_sppb_entry then
1916: hr_utility.trace('Warning: Unable to locate SPP Birth entry in new tax year');
1917: hr_utility.trace('Entries for SPP Birth absences in new tax year may be non-existent');
1918: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
1912: when e_sppb_rate_not_set then
1913: hr_utility.trace('Warning: SPP Birth rate for new tax year not set');
1914: hr_utility.trace('Unable to proceed with updating SPP Birth entries');
1915: when e_no_new_sppb_entry then
1916: hr_utility.trace('Warning: Unable to locate SPP Birth entry in new tax year');
1917: hr_utility.trace('Entries for SPP Birth absences in new tax year may be non-existent');
1918: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
1919: when others then
1920: hr_utility.trace('Unexpected error occurred inside SPP Birth update block');
1913: hr_utility.trace('Warning: SPP Birth rate for new tax year not set');
1914: hr_utility.trace('Unable to proceed with updating SPP Birth entries');
1915: when e_no_new_sppb_entry then
1916: hr_utility.trace('Warning: Unable to locate SPP Birth entry in new tax year');
1917: hr_utility.trace('Entries for SPP Birth absences in new tax year may be non-existent');
1918: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
1919: when others then
1920: hr_utility.trace('Unexpected error occurred inside SPP Birth update block');
1921: hr_utility.trace('SQL error number: '||SQLCODE);
1914: hr_utility.trace('Unable to proceed with updating SPP Birth entries');
1915: when e_no_new_sppb_entry then
1916: hr_utility.trace('Warning: Unable to locate SPP Birth entry in new tax year');
1917: hr_utility.trace('Entries for SPP Birth absences in new tax year may be non-existent');
1918: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
1919: when others then
1920: hr_utility.trace('Unexpected error occurred inside SPP Birth update block');
1921: hr_utility.trace('SQL error number: '||SQLCODE);
1922: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1916: hr_utility.trace('Warning: Unable to locate SPP Birth entry in new tax year');
1917: hr_utility.trace('Entries for SPP Birth absences in new tax year may be non-existent');
1918: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
1919: when others then
1920: hr_utility.trace('Unexpected error occurred inside SPP Birth update block');
1921: hr_utility.trace('SQL error number: '||SQLCODE);
1922: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1923: rollback to pre_sppb_update_status;
1924: l_update_error := true;
1917: hr_utility.trace('Entries for SPP Birth absences in new tax year may be non-existent');
1918: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
1919: when others then
1920: hr_utility.trace('Unexpected error occurred inside SPP Birth update block');
1921: hr_utility.trace('SQL error number: '||SQLCODE);
1922: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1923: rollback to pre_sppb_update_status;
1924: l_update_error := true;
1925: end; /* SPP Birth update block */
1918: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
1919: when others then
1920: hr_utility.trace('Unexpected error occurred inside SPP Birth update block');
1921: hr_utility.trace('SQL error number: '||SQLCODE);
1922: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1923: rollback to pre_sppb_update_status;
1924: l_update_error := true;
1925: end; /* SPP Birth update block */
1926:
1923: rollback to pre_sppb_update_status;
1924: l_update_error := true;
1925: end; /* SPP Birth update block */
1926:
1927: hr_utility.trace('Update of SSP, SMP, SAP, SPP Adoption and SPP Birth entries complete');
1928:
1929: p_update_error := l_update_error;
1930:
1931: hr_utility.trace('Leaving: '||g_package||'.update_ssp_smp_entries');
1927: hr_utility.trace('Update of SSP, SMP, SAP, SPP Adoption and SPP Birth entries complete');
1928:
1929: p_update_error := l_update_error;
1930:
1931: hr_utility.trace('Leaving: '||g_package||'.update_ssp_smp_entries');
1932:
1933:
1934: exception
1935:
1935:
1936:
1937: when others
1938: then
1939: hr_utility.trace('Unexpected error occurred inside SSP/SMP element entries update procedure');
1940: hr_utility.trace('SQL error number: '||SQLCODE);
1941: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1942: rollback to pre_update_status;
1943: p_update_error := true;
1936:
1937: when others
1938: then
1939: hr_utility.trace('Unexpected error occurred inside SSP/SMP element entries update procedure');
1940: hr_utility.trace('SQL error number: '||SQLCODE);
1941: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1942: rollback to pre_update_status;
1943: p_update_error := true;
1944:
1937: when others
1938: then
1939: hr_utility.trace('Unexpected error occurred inside SSP/SMP element entries update procedure');
1940: hr_utility.trace('SQL error number: '||SQLCODE);
1941: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1942: rollback to pre_update_status;
1943: p_update_error := true;
1944:
1945:
2159:
2160:
2161: begin
2162:
2163: hr_utility.trace('Entering ssp_entries_already_updated function');
2164:
2165: /* Find SSP rate for new tax year */
2166: open csr_new_ssp_rate;
2167: fetch csr_new_ssp_rate into l_new_SSP_rate;
2175: end if;
2176:
2177: close csr_new_ssp_rate;
2178:
2179: hr_utility.trace('New SSP rate: '||l_new_SSP_rate);
2180:
2181: /* Find first element entry value holding SSP rate for new tax year */
2182: open csr_first_new_ssp_entry;
2183: fetch csr_first_new_ssp_entry into l_first_SSP_entry_rate;
2191: end if;
2192:
2193: close csr_first_new_ssp_entry;
2194:
2195: hr_utility.trace('First SSP entry rate: '||l_first_SSP_entry_rate);
2196:
2197: if l_new_SSP_rate = l_first_SSP_entry_rate
2198: then
2199:
2302: l_old_std_smp_rate number;
2303:
2304: begin
2305:
2306: hr_utility.trace('Entering smp_rate_changed function');
2307: --
2308: /* Find SMP rate for new tax year */
2309: open csr_new_smp_rate;
2310: fetch csr_new_smp_rate into l_new_SMP_rate, l_new_high_smp_rate, l_new_std_smp_rate;
2318: end if;
2319: --
2320: close csr_new_smp_rate;
2321: --
2322: hr_utility.trace('New Lower SMP rate: '||l_new_SMP_rate);
2323: hr_utility.trace('New Higher SMP rate: '||l_new_high_SMP_rate);
2324: hr_utility.trace('New Standard SMP rate: '||l_new_std_SMP_rate);
2325: --
2326: /* Find SMP rate for current tax year */
2319: --
2320: close csr_new_smp_rate;
2321: --
2322: hr_utility.trace('New Lower SMP rate: '||l_new_SMP_rate);
2323: hr_utility.trace('New Higher SMP rate: '||l_new_high_SMP_rate);
2324: hr_utility.trace('New Standard SMP rate: '||l_new_std_SMP_rate);
2325: --
2326: /* Find SMP rate for current tax year */
2327: open csr_old_smp_rate;
2320: close csr_new_smp_rate;
2321: --
2322: hr_utility.trace('New Lower SMP rate: '||l_new_SMP_rate);
2323: hr_utility.trace('New Higher SMP rate: '||l_new_high_SMP_rate);
2324: hr_utility.trace('New Standard SMP rate: '||l_new_std_SMP_rate);
2325: --
2326: /* Find SMP rate for current tax year */
2327: open csr_old_smp_rate;
2328: fetch csr_old_smp_rate into l_old_SMP_rate, l_old_high_smp_rate, l_old_std_smp_rate;
2327: open csr_old_smp_rate;
2328: fetch csr_old_smp_rate into l_old_SMP_rate, l_old_high_smp_rate, l_old_std_smp_rate;
2329: close csr_old_smp_rate;
2330: --
2331: hr_utility.trace('Old SMP rate: '||l_old_SMP_rate);
2332: hr_utility.trace('Old Higher SMP rate: '||l_old_high_SMP_rate);
2333: hr_utility.trace('Old Standard SMP rate: '||l_old_std_SMP_rate);
2334: --
2335: if (l_new_SMP_rate = l_old_smp_rate) and (l_new_high_smp_rate = l_old_high_smp_rate) and (l_new_std_smp_rate = l_old_std_smp_rate)
2328: fetch csr_old_smp_rate into l_old_SMP_rate, l_old_high_smp_rate, l_old_std_smp_rate;
2329: close csr_old_smp_rate;
2330: --
2331: hr_utility.trace('Old SMP rate: '||l_old_SMP_rate);
2332: hr_utility.trace('Old Higher SMP rate: '||l_old_high_SMP_rate);
2333: hr_utility.trace('Old Standard SMP rate: '||l_old_std_SMP_rate);
2334: --
2335: if (l_new_SMP_rate = l_old_smp_rate) and (l_new_high_smp_rate = l_old_high_smp_rate) and (l_new_std_smp_rate = l_old_std_smp_rate)
2336: then
2329: close csr_old_smp_rate;
2330: --
2331: hr_utility.trace('Old SMP rate: '||l_old_SMP_rate);
2332: hr_utility.trace('Old Higher SMP rate: '||l_old_high_SMP_rate);
2333: hr_utility.trace('Old Standard SMP rate: '||l_old_std_SMP_rate);
2334: --
2335: if (l_new_SMP_rate = l_old_smp_rate) and (l_new_high_smp_rate = l_old_high_smp_rate) and (l_new_std_smp_rate = l_old_std_smp_rate)
2336: then
2337: return false;
2424: l_old_std_sap_rate number;
2425: --
2426: begin
2427: --
2428: hr_utility.trace('Entering sap_rate_changed function');
2429: --
2430: /* Find sap rate for new tax year */
2431: open csr_new_sap_rate;
2432: fetch csr_new_sap_rate into l_new_sap_rate, l_new_std_sap_rate;
2439: end if;
2440: --
2441: close csr_new_sap_rate;
2442: --
2443: hr_utility.trace('New sap rate: '||l_new_sap_rate);
2444: hr_utility.trace('New std sap rate: '||l_new_std_sap_rate);
2445: --
2446: /* Find sap rate for current tax year */
2447: open csr_old_sap_rate;
2440: --
2441: close csr_new_sap_rate;
2442: --
2443: hr_utility.trace('New sap rate: '||l_new_sap_rate);
2444: hr_utility.trace('New std sap rate: '||l_new_std_sap_rate);
2445: --
2446: /* Find sap rate for current tax year */
2447: open csr_old_sap_rate;
2448: fetch csr_old_sap_rate into l_old_sap_rate, l_old_std_sap_rate;
2447: open csr_old_sap_rate;
2448: fetch csr_old_sap_rate into l_old_sap_rate, l_old_std_sap_rate;
2449: close csr_old_sap_rate;
2450: --
2451: hr_utility.trace('Old sap rate: '||l_old_sap_rate);
2452: hr_utility.trace('Old std sap rate: '||l_old_std_sap_rate);
2453: --
2454: if (l_new_sap_rate = l_old_sap_rate) and (l_new_std_sap_rate = l_old_std_sap_rate) then
2455: return false;
2448: fetch csr_old_sap_rate into l_old_sap_rate, l_old_std_sap_rate;
2449: close csr_old_sap_rate;
2450: --
2451: hr_utility.trace('Old sap rate: '||l_old_sap_rate);
2452: hr_utility.trace('Old std sap rate: '||l_old_std_sap_rate);
2453: --
2454: if (l_new_sap_rate = l_old_sap_rate) and (l_new_std_sap_rate = l_old_std_sap_rate) then
2455: return false;
2456: else
2540: l_old_std_sppa_rate number;
2541: --
2542: begin
2543: --
2544: hr_utility.trace('Entering sppa_rate_changed function');
2545: --
2546: /* Find sppa rate for new tax year */
2547: open csr_new_sppa_rate;
2548: fetch csr_new_sppa_rate into l_new_sppa_rate, l_new_std_sppa_rate;
2555: end if;
2556: --
2557: close csr_new_sppa_rate;
2558: --
2559: hr_utility.trace('New sppa rate: '||l_new_sppa_rate);
2560: hr_utility.trace('New std sppa rate: '||l_new_std_sppa_rate);
2561: --
2562: open csr_old_sppa_rate;
2563: fetch csr_old_sppa_rate into l_old_sppa_rate, l_old_std_sppa_rate;
2556: --
2557: close csr_new_sppa_rate;
2558: --
2559: hr_utility.trace('New sppa rate: '||l_new_sppa_rate);
2560: hr_utility.trace('New std sppa rate: '||l_new_std_sppa_rate);
2561: --
2562: open csr_old_sppa_rate;
2563: fetch csr_old_sppa_rate into l_old_sppa_rate, l_old_std_sppa_rate;
2564: close csr_old_sppa_rate;
2562: open csr_old_sppa_rate;
2563: fetch csr_old_sppa_rate into l_old_sppa_rate, l_old_std_sppa_rate;
2564: close csr_old_sppa_rate;
2565: --
2566: hr_utility.trace('Old SPPA rate: '||l_old_sppa_rate);
2567: hr_utility.trace('Old Std SPPA rate: '||l_old_std_sppa_rate);
2568: --
2569: if (l_new_sppa_rate = l_old_sppa_rate) and (l_new_std_sppa_rate = l_old_std_sppa_rate) then
2570: return false;
2563: fetch csr_old_sppa_rate into l_old_sppa_rate, l_old_std_sppa_rate;
2564: close csr_old_sppa_rate;
2565: --
2566: hr_utility.trace('Old SPPA rate: '||l_old_sppa_rate);
2567: hr_utility.trace('Old Std SPPA rate: '||l_old_std_sppa_rate);
2568: --
2569: if (l_new_sppa_rate = l_old_sppa_rate) and (l_new_std_sppa_rate = l_old_std_sppa_rate) then
2570: return false;
2571: else
2654: l_old_std_sppb_rate number;
2655: --
2656: begin
2657: --
2658: hr_utility.trace('Entering sppb_rate_changed function');
2659: --
2660: /* Find SPPB rate for new tax year */
2661: open csr_new_sppb_rate;
2662: fetch csr_new_sppb_rate into l_new_sppb_rate, l_new_std_sppb_rate;
2669: end if;
2670: --
2671: close csr_new_sppb_rate;
2672: --
2673: hr_utility.trace('New SPPB rate: '||l_new_sppb_rate);
2674: hr_utility.trace('New Std SPPB rate: '||l_new_Std_sppb_rate);
2675: --
2676: open csr_old_sppb_rate;
2677: fetch csr_old_sppb_rate into l_old_sppb_rate, l_old_std_sppb_rate;
2670: --
2671: close csr_new_sppb_rate;
2672: --
2673: hr_utility.trace('New SPPB rate: '||l_new_sppb_rate);
2674: hr_utility.trace('New Std SPPB rate: '||l_new_Std_sppb_rate);
2675: --
2676: open csr_old_sppb_rate;
2677: fetch csr_old_sppb_rate into l_old_sppb_rate, l_old_std_sppb_rate;
2678: close csr_old_sppb_rate;
2676: open csr_old_sppb_rate;
2677: fetch csr_old_sppb_rate into l_old_sppb_rate, l_old_std_sppb_rate;
2678: close csr_old_sppb_rate;
2679: --
2680: hr_utility.trace('old SPPB rate: '||l_old_sppb_rate);
2681: hr_utility.trace('old Std SPPB rate: '||l_old_Std_sppb_rate);
2682: --
2683: if (l_new_sppb_rate = l_old_sppb_rate) and (l_new_std_sppb_rate = l_old_std_sppb_rate) then
2684: return false;
2677: fetch csr_old_sppb_rate into l_old_sppb_rate, l_old_std_sppb_rate;
2678: close csr_old_sppb_rate;
2679: --
2680: hr_utility.trace('old SPPB rate: '||l_old_sppb_rate);
2681: hr_utility.trace('old Std SPPB rate: '||l_old_Std_sppb_rate);
2682: --
2683: if (l_new_sppb_rate = l_old_sppb_rate) and (l_new_std_sppb_rate = l_old_std_sppb_rate) then
2684: return false;
2685: else
2692:
2693: /* Main program body */
2694: begin
2695:
2696: hr_utility.trace('Entering: '||g_package||'.update_ssp_smp_entries');
2697:
2698: savepoint pre_update_status;
2699: /* Check first whether SSP element entries already updated for new tax year */
2700: /* SSP update block */
2704:
2705: if not ssp_entries_already_updated
2706: then
2707:
2708: hr_utility.trace('SSP element entries not updated, updating ....');
2709:
2710: for r_affected_absences in csr_affected_absences loop
2711:
2712: /* SSP control call block */
2711:
2712: /* SSP control call block */
2713: begin
2714:
2715: hr_utility.trace('Processing SSP absence: '||r_affected_absences.absence_id);
2716: --6800788 begin
2717: --ssp_ssp_pkg.ssp_control(r_affected_absences.absence_id);
2718: DBMS_JOB.SUBMIT(l_job_no,'ssp_ssp_pkg.ssp_control('||r_affected_absences.absence_id||');');
2719: l_count := l_count + 1;
2726: --6800788 end
2727: exception
2728:
2729: when others then
2730: hr_utility.trace('Error occurred while processing SSP absence: '||r_affected_absences.absence_id);
2731: hr_utility.trace('SQL error code: '||SQLCODE);
2732: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2733: hr_utility.trace('Person id: '||r_affected_absences.person_id);
2734: hr_utility.trace('Absence start date: '||r_affected_absences.absence_start_date);
2727: exception
2728:
2729: when others then
2730: hr_utility.trace('Error occurred while processing SSP absence: '||r_affected_absences.absence_id);
2731: hr_utility.trace('SQL error code: '||SQLCODE);
2732: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2733: hr_utility.trace('Person id: '||r_affected_absences.person_id);
2734: hr_utility.trace('Absence start date: '||r_affected_absences.absence_start_date);
2735: hr_utility.trace('Absence end date: '||r_affected_absences.absence_end_date);
2728:
2729: when others then
2730: hr_utility.trace('Error occurred while processing SSP absence: '||r_affected_absences.absence_id);
2731: hr_utility.trace('SQL error code: '||SQLCODE);
2732: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2733: hr_utility.trace('Person id: '||r_affected_absences.person_id);
2734: hr_utility.trace('Absence start date: '||r_affected_absences.absence_start_date);
2735: hr_utility.trace('Absence end date: '||r_affected_absences.absence_end_date);
2736: l_update_error := true;
2729: when others then
2730: hr_utility.trace('Error occurred while processing SSP absence: '||r_affected_absences.absence_id);
2731: hr_utility.trace('SQL error code: '||SQLCODE);
2732: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2733: hr_utility.trace('Person id: '||r_affected_absences.person_id);
2734: hr_utility.trace('Absence start date: '||r_affected_absences.absence_start_date);
2735: hr_utility.trace('Absence end date: '||r_affected_absences.absence_end_date);
2736: l_update_error := true;
2737:
2730: hr_utility.trace('Error occurred while processing SSP absence: '||r_affected_absences.absence_id);
2731: hr_utility.trace('SQL error code: '||SQLCODE);
2732: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2733: hr_utility.trace('Person id: '||r_affected_absences.person_id);
2734: hr_utility.trace('Absence start date: '||r_affected_absences.absence_start_date);
2735: hr_utility.trace('Absence end date: '||r_affected_absences.absence_end_date);
2736: l_update_error := true;
2737:
2738: end;
2731: hr_utility.trace('SQL error code: '||SQLCODE);
2732: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2733: hr_utility.trace('Person id: '||r_affected_absences.person_id);
2734: hr_utility.trace('Absence start date: '||r_affected_absences.absence_start_date);
2735: hr_utility.trace('Absence end date: '||r_affected_absences.absence_end_date);
2736: l_update_error := true;
2737:
2738: end;
2739:
2740:
2741:
2742: end loop;
2743:
2744: hr_utility.trace('Updated entries for '||l_count||' absences');
2745: -- dbms_output.put_line(' Total SSP absences ='|| l_count);
2746: else
2747:
2748: hr_utility.trace('SSP element entries already updated for new tax year');
2744: hr_utility.trace('Updated entries for '||l_count||' absences');
2745: -- dbms_output.put_line(' Total SSP absences ='|| l_count);
2746: else
2747:
2748: hr_utility.trace('SSP element entries already updated for new tax year');
2749:
2750: end if;
2751:
2752: commit;
2754: exception
2755:
2756: when e_ssp_rate_not_set
2757: then
2758: hr_utility.trace('Warning: SSP rate for new tax year not set');
2759: hr_utility.trace('Unable to proceed with updating SSP entries');
2760:
2761:
2762: when e_no_new_ssp_entry
2755:
2756: when e_ssp_rate_not_set
2757: then
2758: hr_utility.trace('Warning: SSP rate for new tax year not set');
2759: hr_utility.trace('Unable to proceed with updating SSP entries');
2760:
2761:
2762: when e_no_new_ssp_entry
2763: then
2760:
2761:
2762: when e_no_new_ssp_entry
2763: then
2764: hr_utility.trace('Warning: Unable to locate SSP entry in new tax year');
2765: hr_utility.trace('Entries for SSP absences in new tax year may be non-existent');
2766: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end dates of absences');
2767:
2768: when others
2761:
2762: when e_no_new_ssp_entry
2763: then
2764: hr_utility.trace('Warning: Unable to locate SSP entry in new tax year');
2765: hr_utility.trace('Entries for SSP absences in new tax year may be non-existent');
2766: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end dates of absences');
2767:
2768: when others
2769: then
2762: when e_no_new_ssp_entry
2763: then
2764: hr_utility.trace('Warning: Unable to locate SSP entry in new tax year');
2765: hr_utility.trace('Entries for SSP absences in new tax year may be non-existent');
2766: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end dates of absences');
2767:
2768: when others
2769: then
2770: hr_utility.trace('Unexpected error occurred inside SSP update block');
2766: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end dates of absences');
2767:
2768: when others
2769: then
2770: hr_utility.trace('Unexpected error occurred inside SSP update block');
2771: hr_utility.trace('SQL error number: '||SQLCODE);
2772: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2773: rollback to pre_ssp_update_status;
2774: l_update_error := true;
2767:
2768: when others
2769: then
2770: hr_utility.trace('Unexpected error occurred inside SSP update block');
2771: hr_utility.trace('SQL error number: '||SQLCODE);
2772: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2773: rollback to pre_ssp_update_status;
2774: l_update_error := true;
2775:
2768: when others
2769: then
2770: hr_utility.trace('Unexpected error occurred inside SSP update block');
2771: hr_utility.trace('SQL error number: '||SQLCODE);
2772: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2773: rollback to pre_ssp_update_status;
2774: l_update_error := true;
2775:
2776: end; /* SSP update block */
2783: savepoint pre_smp_update_status;
2784: --
2785: if smp_rate_changed then
2786: --
2787: hr_utility.trace('SMP element entries updating ....');
2788: for r_affected_maternities in csr_affected_leave('MA') loop
2789: /* SMP Control call block */
2790: begin
2791: hr_utility.trace('Processing SMP absence: '||r_affected_maternities.maternity_id);
2787: hr_utility.trace('SMP element entries updating ....');
2788: for r_affected_maternities in csr_affected_leave('MA') loop
2789: /* SMP Control call block */
2790: begin
2791: hr_utility.trace('Processing SMP absence: '||r_affected_maternities.maternity_id);
2792: --6800788 begin
2793: -- ssp_smp_pkg.smp_control(p_maternity_id => r_affected_maternities.maternity_id,
2794: -- p_deleting => FALSE);
2795:
2805: --6800788 end
2806:
2807: exception
2808: when others then
2809: hr_utility.trace('Error occurred while processing SMP absence: '||r_affected_maternities.maternity_id);
2810: hr_utility.trace('SQL error code: '||SQLCODE);
2811: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2812: hr_utility.trace('Person id: '||r_affected_maternities.person_id);
2813: hr_utility.trace('Maternity start date: '||r_affected_maternities.date_start);
2806:
2807: exception
2808: when others then
2809: hr_utility.trace('Error occurred while processing SMP absence: '||r_affected_maternities.maternity_id);
2810: hr_utility.trace('SQL error code: '||SQLCODE);
2811: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2812: hr_utility.trace('Person id: '||r_affected_maternities.person_id);
2813: hr_utility.trace('Maternity start date: '||r_affected_maternities.date_start);
2814: hr_utility.trace('Maternity end date: '||r_affected_maternities.date_end);
2807: exception
2808: when others then
2809: hr_utility.trace('Error occurred while processing SMP absence: '||r_affected_maternities.maternity_id);
2810: hr_utility.trace('SQL error code: '||SQLCODE);
2811: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2812: hr_utility.trace('Person id: '||r_affected_maternities.person_id);
2813: hr_utility.trace('Maternity start date: '||r_affected_maternities.date_start);
2814: hr_utility.trace('Maternity end date: '||r_affected_maternities.date_end);
2815: hr_utility.trace('Maternity projected start date: '||r_affected_maternities.date_projected_start);
2808: when others then
2809: hr_utility.trace('Error occurred while processing SMP absence: '||r_affected_maternities.maternity_id);
2810: hr_utility.trace('SQL error code: '||SQLCODE);
2811: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2812: hr_utility.trace('Person id: '||r_affected_maternities.person_id);
2813: hr_utility.trace('Maternity start date: '||r_affected_maternities.date_start);
2814: hr_utility.trace('Maternity end date: '||r_affected_maternities.date_end);
2815: hr_utility.trace('Maternity projected start date: '||r_affected_maternities.date_projected_start);
2816: hr_utility.trace('Maternity projected end date: '||r_affected_maternities.date_projected_end);
2809: hr_utility.trace('Error occurred while processing SMP absence: '||r_affected_maternities.maternity_id);
2810: hr_utility.trace('SQL error code: '||SQLCODE);
2811: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2812: hr_utility.trace('Person id: '||r_affected_maternities.person_id);
2813: hr_utility.trace('Maternity start date: '||r_affected_maternities.date_start);
2814: hr_utility.trace('Maternity end date: '||r_affected_maternities.date_end);
2815: hr_utility.trace('Maternity projected start date: '||r_affected_maternities.date_projected_start);
2816: hr_utility.trace('Maternity projected end date: '||r_affected_maternities.date_projected_end);
2817: l_update_error := true;
2810: hr_utility.trace('SQL error code: '||SQLCODE);
2811: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2812: hr_utility.trace('Person id: '||r_affected_maternities.person_id);
2813: hr_utility.trace('Maternity start date: '||r_affected_maternities.date_start);
2814: hr_utility.trace('Maternity end date: '||r_affected_maternities.date_end);
2815: hr_utility.trace('Maternity projected start date: '||r_affected_maternities.date_projected_start);
2816: hr_utility.trace('Maternity projected end date: '||r_affected_maternities.date_projected_end);
2817: l_update_error := true;
2818: end;
2811: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2812: hr_utility.trace('Person id: '||r_affected_maternities.person_id);
2813: hr_utility.trace('Maternity start date: '||r_affected_maternities.date_start);
2814: hr_utility.trace('Maternity end date: '||r_affected_maternities.date_end);
2815: hr_utility.trace('Maternity projected start date: '||r_affected_maternities.date_projected_start);
2816: hr_utility.trace('Maternity projected end date: '||r_affected_maternities.date_projected_end);
2817: l_update_error := true;
2818: end;
2819: end loop;
2812: hr_utility.trace('Person id: '||r_affected_maternities.person_id);
2813: hr_utility.trace('Maternity start date: '||r_affected_maternities.date_start);
2814: hr_utility.trace('Maternity end date: '||r_affected_maternities.date_end);
2815: hr_utility.trace('Maternity projected start date: '||r_affected_maternities.date_projected_start);
2816: hr_utility.trace('Maternity projected end date: '||r_affected_maternities.date_projected_end);
2817: l_update_error := true;
2818: end;
2819: end loop;
2820: hr_utility.trace('Updated entries for '||l_mat_count||' maternities');
2816: hr_utility.trace('Maternity projected end date: '||r_affected_maternities.date_projected_end);
2817: l_update_error := true;
2818: end;
2819: end loop;
2820: hr_utility.trace('Updated entries for '||l_mat_count||' maternities');
2821: -- dbms_output.put_line(' Total SMP absences ='|| l_mat_count);
2822: else
2823: hr_utility.trace('SMP element entries already updated for new tax year');
2824: end if;
2819: end loop;
2820: hr_utility.trace('Updated entries for '||l_mat_count||' maternities');
2821: -- dbms_output.put_line(' Total SMP absences ='|| l_mat_count);
2822: else
2823: hr_utility.trace('SMP element entries already updated for new tax year');
2824: end if;
2825: --
2826: commit;
2827: --
2826: commit;
2827: --
2828: exception
2829: when e_smp_rate_not_set then
2830: hr_utility.trace('Warning: SMP rate for new tax year not set');
2831: hr_utility.trace('Unable to proceed with updating SMP entries');
2832: when e_no_new_smp_entry then
2833: hr_utility.trace('Warning: Unable to locate SMP entry in new tax year');
2834: hr_utility.trace('Entries for SMP absences in new tax year may be non-existent');
2827: --
2828: exception
2829: when e_smp_rate_not_set then
2830: hr_utility.trace('Warning: SMP rate for new tax year not set');
2831: hr_utility.trace('Unable to proceed with updating SMP entries');
2832: when e_no_new_smp_entry then
2833: hr_utility.trace('Warning: Unable to locate SMP entry in new tax year');
2834: hr_utility.trace('Entries for SMP absences in new tax year may be non-existent');
2835: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
2829: when e_smp_rate_not_set then
2830: hr_utility.trace('Warning: SMP rate for new tax year not set');
2831: hr_utility.trace('Unable to proceed with updating SMP entries');
2832: when e_no_new_smp_entry then
2833: hr_utility.trace('Warning: Unable to locate SMP entry in new tax year');
2834: hr_utility.trace('Entries for SMP absences in new tax year may be non-existent');
2835: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
2836: when others then
2837: hr_utility.trace('Unexpected error occurred inside SMP update block');
2830: hr_utility.trace('Warning: SMP rate for new tax year not set');
2831: hr_utility.trace('Unable to proceed with updating SMP entries');
2832: when e_no_new_smp_entry then
2833: hr_utility.trace('Warning: Unable to locate SMP entry in new tax year');
2834: hr_utility.trace('Entries for SMP absences in new tax year may be non-existent');
2835: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
2836: when others then
2837: hr_utility.trace('Unexpected error occurred inside SMP update block');
2838: hr_utility.trace('SQL error number: '||SQLCODE);
2831: hr_utility.trace('Unable to proceed with updating SMP entries');
2832: when e_no_new_smp_entry then
2833: hr_utility.trace('Warning: Unable to locate SMP entry in new tax year');
2834: hr_utility.trace('Entries for SMP absences in new tax year may be non-existent');
2835: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
2836: when others then
2837: hr_utility.trace('Unexpected error occurred inside SMP update block');
2838: hr_utility.trace('SQL error number: '||SQLCODE);
2839: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2833: hr_utility.trace('Warning: Unable to locate SMP entry in new tax year');
2834: hr_utility.trace('Entries for SMP absences in new tax year may be non-existent');
2835: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
2836: when others then
2837: hr_utility.trace('Unexpected error occurred inside SMP update block');
2838: hr_utility.trace('SQL error number: '||SQLCODE);
2839: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2840: rollback to pre_smp_update_status;
2841: l_update_error := true;
2834: hr_utility.trace('Entries for SMP absences in new tax year may be non-existent');
2835: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
2836: when others then
2837: hr_utility.trace('Unexpected error occurred inside SMP update block');
2838: hr_utility.trace('SQL error number: '||SQLCODE);
2839: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2840: rollback to pre_smp_update_status;
2841: l_update_error := true;
2842: end; /* SMP update block */
2835: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
2836: when others then
2837: hr_utility.trace('Unexpected error occurred inside SMP update block');
2838: hr_utility.trace('SQL error number: '||SQLCODE);
2839: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2840: rollback to pre_smp_update_status;
2841: l_update_error := true;
2842: end; /* SMP update block */
2843: -- 05/12/2003 rmakhija: Uncommented section ends here
2848: savepoint pre_sap_update_status;
2849: --
2850: if sap_rate_changed then
2851: --
2852: hr_utility.trace('SAP element entries updating ....');
2853: for r_affected_adoption in csr_affected_leave('AD') loop
2854: /* SAP Control call block */
2855: begin
2856: hr_utility.trace('Processing SAP absence: '||r_affected_adoption.maternity_id);
2852: hr_utility.trace('SAP element entries updating ....');
2853: for r_affected_adoption in csr_affected_leave('AD') loop
2854: /* SAP Control call block */
2855: begin
2856: hr_utility.trace('Processing SAP absence: '||r_affected_adoption.maternity_id);
2857: --6800788 begin
2858: -- ssp_sap_pkg.sap_control(p_maternity_id => r_affected_adoption.maternity_id,
2859: -- p_deleting => FALSE);
2860:
2870: --6800788 end
2871:
2872: exception
2873: when others then
2874: hr_utility.trace('Error occurred while processing SAP absence: '||r_affected_adoption.maternity_id);
2875: hr_utility.trace('SQL error code: '||SQLCODE);
2876: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2877: hr_utility.trace('Person id: '||r_affected_adoption.person_id);
2878: hr_utility.trace('Adoption start date: '||r_affected_adoption.date_start);
2871:
2872: exception
2873: when others then
2874: hr_utility.trace('Error occurred while processing SAP absence: '||r_affected_adoption.maternity_id);
2875: hr_utility.trace('SQL error code: '||SQLCODE);
2876: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2877: hr_utility.trace('Person id: '||r_affected_adoption.person_id);
2878: hr_utility.trace('Adoption start date: '||r_affected_adoption.date_start);
2879: hr_utility.trace('Adoption end date: '||r_affected_adoption.date_end);
2872: exception
2873: when others then
2874: hr_utility.trace('Error occurred while processing SAP absence: '||r_affected_adoption.maternity_id);
2875: hr_utility.trace('SQL error code: '||SQLCODE);
2876: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2877: hr_utility.trace('Person id: '||r_affected_adoption.person_id);
2878: hr_utility.trace('Adoption start date: '||r_affected_adoption.date_start);
2879: hr_utility.trace('Adoption end date: '||r_affected_adoption.date_end);
2880: hr_utility.trace('Adoption projected start date: '||r_affected_adoption.date_projected_start);
2873: when others then
2874: hr_utility.trace('Error occurred while processing SAP absence: '||r_affected_adoption.maternity_id);
2875: hr_utility.trace('SQL error code: '||SQLCODE);
2876: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2877: hr_utility.trace('Person id: '||r_affected_adoption.person_id);
2878: hr_utility.trace('Adoption start date: '||r_affected_adoption.date_start);
2879: hr_utility.trace('Adoption end date: '||r_affected_adoption.date_end);
2880: hr_utility.trace('Adoption projected start date: '||r_affected_adoption.date_projected_start);
2881: hr_utility.trace('Adoption projected end date: '||r_affected_adoption.date_projected_end);
2874: hr_utility.trace('Error occurred while processing SAP absence: '||r_affected_adoption.maternity_id);
2875: hr_utility.trace('SQL error code: '||SQLCODE);
2876: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2877: hr_utility.trace('Person id: '||r_affected_adoption.person_id);
2878: hr_utility.trace('Adoption start date: '||r_affected_adoption.date_start);
2879: hr_utility.trace('Adoption end date: '||r_affected_adoption.date_end);
2880: hr_utility.trace('Adoption projected start date: '||r_affected_adoption.date_projected_start);
2881: hr_utility.trace('Adoption projected end date: '||r_affected_adoption.date_projected_end);
2882: l_update_error := true;
2875: hr_utility.trace('SQL error code: '||SQLCODE);
2876: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2877: hr_utility.trace('Person id: '||r_affected_adoption.person_id);
2878: hr_utility.trace('Adoption start date: '||r_affected_adoption.date_start);
2879: hr_utility.trace('Adoption end date: '||r_affected_adoption.date_end);
2880: hr_utility.trace('Adoption projected start date: '||r_affected_adoption.date_projected_start);
2881: hr_utility.trace('Adoption projected end date: '||r_affected_adoption.date_projected_end);
2882: l_update_error := true;
2883: end;
2876: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2877: hr_utility.trace('Person id: '||r_affected_adoption.person_id);
2878: hr_utility.trace('Adoption start date: '||r_affected_adoption.date_start);
2879: hr_utility.trace('Adoption end date: '||r_affected_adoption.date_end);
2880: hr_utility.trace('Adoption projected start date: '||r_affected_adoption.date_projected_start);
2881: hr_utility.trace('Adoption projected end date: '||r_affected_adoption.date_projected_end);
2882: l_update_error := true;
2883: end;
2884: end loop;
2877: hr_utility.trace('Person id: '||r_affected_adoption.person_id);
2878: hr_utility.trace('Adoption start date: '||r_affected_adoption.date_start);
2879: hr_utility.trace('Adoption end date: '||r_affected_adoption.date_end);
2880: hr_utility.trace('Adoption projected start date: '||r_affected_adoption.date_projected_start);
2881: hr_utility.trace('Adoption projected end date: '||r_affected_adoption.date_projected_end);
2882: l_update_error := true;
2883: end;
2884: end loop;
2885: hr_utility.trace('Updated entries for '||l_adop_count||' adoptions');
2881: hr_utility.trace('Adoption projected end date: '||r_affected_adoption.date_projected_end);
2882: l_update_error := true;
2883: end;
2884: end loop;
2885: hr_utility.trace('Updated entries for '||l_adop_count||' adoptions');
2886: -- dbms_output.put_line(' Total SAP absences ='|| l_adop_count);
2887: else
2888: hr_utility.trace('SAP element entries already updated for new tax year');
2889: end if;
2884: end loop;
2885: hr_utility.trace('Updated entries for '||l_adop_count||' adoptions');
2886: -- dbms_output.put_line(' Total SAP absences ='|| l_adop_count);
2887: else
2888: hr_utility.trace('SAP element entries already updated for new tax year');
2889: end if;
2890: --
2891: commit;
2892: --
2891: commit;
2892: --
2893: exception
2894: when e_sap_rate_not_set then
2895: hr_utility.trace('Warning: SAP rate for new tax year not set');
2896: hr_utility.trace('Unable to proceed with updating SAP entries');
2897: when e_no_new_sap_entry then
2898: hr_utility.trace('Warning: Unable to locate SAP entry in new tax year');
2899: hr_utility.trace('Entries for SAP absences in new tax year may be non-existent');
2892: --
2893: exception
2894: when e_sap_rate_not_set then
2895: hr_utility.trace('Warning: SAP rate for new tax year not set');
2896: hr_utility.trace('Unable to proceed with updating SAP entries');
2897: when e_no_new_sap_entry then
2898: hr_utility.trace('Warning: Unable to locate SAP entry in new tax year');
2899: hr_utility.trace('Entries for SAP absences in new tax year may be non-existent');
2900: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
2894: when e_sap_rate_not_set then
2895: hr_utility.trace('Warning: SAP rate for new tax year not set');
2896: hr_utility.trace('Unable to proceed with updating SAP entries');
2897: when e_no_new_sap_entry then
2898: hr_utility.trace('Warning: Unable to locate SAP entry in new tax year');
2899: hr_utility.trace('Entries for SAP absences in new tax year may be non-existent');
2900: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
2901: when others then
2902: hr_utility.trace('Unexpected error occurred inside SAP update block');
2895: hr_utility.trace('Warning: SAP rate for new tax year not set');
2896: hr_utility.trace('Unable to proceed with updating SAP entries');
2897: when e_no_new_sap_entry then
2898: hr_utility.trace('Warning: Unable to locate SAP entry in new tax year');
2899: hr_utility.trace('Entries for SAP absences in new tax year may be non-existent');
2900: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
2901: when others then
2902: hr_utility.trace('Unexpected error occurred inside SAP update block');
2903: hr_utility.trace('SQL error number: '||SQLCODE);
2896: hr_utility.trace('Unable to proceed with updating SAP entries');
2897: when e_no_new_sap_entry then
2898: hr_utility.trace('Warning: Unable to locate SAP entry in new tax year');
2899: hr_utility.trace('Entries for SAP absences in new tax year may be non-existent');
2900: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
2901: when others then
2902: hr_utility.trace('Unexpected error occurred inside SAP update block');
2903: hr_utility.trace('SQL error number: '||SQLCODE);
2904: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2898: hr_utility.trace('Warning: Unable to locate SAP entry in new tax year');
2899: hr_utility.trace('Entries for SAP absences in new tax year may be non-existent');
2900: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
2901: when others then
2902: hr_utility.trace('Unexpected error occurred inside SAP update block');
2903: hr_utility.trace('SQL error number: '||SQLCODE);
2904: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2905: rollback to pre_sap_update_status;
2906: l_update_error := true;
2899: hr_utility.trace('Entries for SAP absences in new tax year may be non-existent');
2900: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
2901: when others then
2902: hr_utility.trace('Unexpected error occurred inside SAP update block');
2903: hr_utility.trace('SQL error number: '||SQLCODE);
2904: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2905: rollback to pre_sap_update_status;
2906: l_update_error := true;
2907: end; /* SAP update block */
2900: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
2901: when others then
2902: hr_utility.trace('Unexpected error occurred inside SAP update block');
2903: hr_utility.trace('SQL error number: '||SQLCODE);
2904: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2905: rollback to pre_sap_update_status;
2906: l_update_error := true;
2907: end; /* SAP update block */
2908: -- Begin SPP Adoption Update Block
2911: savepoint pre_sppa_update_status;
2912: --
2913: if sppa_rate_changed then
2914: --
2915: hr_utility.trace('SPP Adoption element entries updating ....');
2916: for r_affected_pat_adop in csr_affected_leave('PA') loop
2917: /* SPP Adoption Control call block */
2918: begin
2919: hr_utility.trace('Processing SPP Adoption absence: '||r_affected_pat_adop.maternity_id);
2915: hr_utility.trace('SPP Adoption element entries updating ....');
2916: for r_affected_pat_adop in csr_affected_leave('PA') loop
2917: /* SPP Adoption Control call block */
2918: begin
2919: hr_utility.trace('Processing SPP Adoption absence: '||r_affected_pat_adop.maternity_id);
2920: --6800788 begin
2921: -- ssp_pad_pkg.pad_control(p_maternity_id => r_affected_pat_adop.maternity_id,
2922: -- p_deleting => FALSE);
2923:
2933: --6800788 end
2934:
2935: exception
2936: when others then
2937: hr_utility.trace('Error occurred while processing SPP Adoption absence: '||r_affected_pat_adop.maternity_id);
2938: hr_utility.trace('SQL error code: '||SQLCODE);
2939: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2940: hr_utility.trace('Person id: '||r_affected_pat_adop.person_id);
2941: hr_utility.trace('Paternity Adoption start date: '||r_affected_pat_adop.date_start);
2934:
2935: exception
2936: when others then
2937: hr_utility.trace('Error occurred while processing SPP Adoption absence: '||r_affected_pat_adop.maternity_id);
2938: hr_utility.trace('SQL error code: '||SQLCODE);
2939: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2940: hr_utility.trace('Person id: '||r_affected_pat_adop.person_id);
2941: hr_utility.trace('Paternity Adoption start date: '||r_affected_pat_adop.date_start);
2942: hr_utility.trace('Paternity Adoption end date: '||r_affected_pat_adop.date_end);
2935: exception
2936: when others then
2937: hr_utility.trace('Error occurred while processing SPP Adoption absence: '||r_affected_pat_adop.maternity_id);
2938: hr_utility.trace('SQL error code: '||SQLCODE);
2939: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2940: hr_utility.trace('Person id: '||r_affected_pat_adop.person_id);
2941: hr_utility.trace('Paternity Adoption start date: '||r_affected_pat_adop.date_start);
2942: hr_utility.trace('Paternity Adoption end date: '||r_affected_pat_adop.date_end);
2943: hr_utility.trace('Paternity Adoption projected start date: '||r_affected_pat_adop.date_projected_start);
2936: when others then
2937: hr_utility.trace('Error occurred while processing SPP Adoption absence: '||r_affected_pat_adop.maternity_id);
2938: hr_utility.trace('SQL error code: '||SQLCODE);
2939: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2940: hr_utility.trace('Person id: '||r_affected_pat_adop.person_id);
2941: hr_utility.trace('Paternity Adoption start date: '||r_affected_pat_adop.date_start);
2942: hr_utility.trace('Paternity Adoption end date: '||r_affected_pat_adop.date_end);
2943: hr_utility.trace('Paternity Adoption projected start date: '||r_affected_pat_adop.date_projected_start);
2944: hr_utility.trace('Paternity Adoption projected end date: '||r_affected_pat_adop.date_projected_end);
2937: hr_utility.trace('Error occurred while processing SPP Adoption absence: '||r_affected_pat_adop.maternity_id);
2938: hr_utility.trace('SQL error code: '||SQLCODE);
2939: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2940: hr_utility.trace('Person id: '||r_affected_pat_adop.person_id);
2941: hr_utility.trace('Paternity Adoption start date: '||r_affected_pat_adop.date_start);
2942: hr_utility.trace('Paternity Adoption end date: '||r_affected_pat_adop.date_end);
2943: hr_utility.trace('Paternity Adoption projected start date: '||r_affected_pat_adop.date_projected_start);
2944: hr_utility.trace('Paternity Adoption projected end date: '||r_affected_pat_adop.date_projected_end);
2945: l_update_error := true;
2938: hr_utility.trace('SQL error code: '||SQLCODE);
2939: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2940: hr_utility.trace('Person id: '||r_affected_pat_adop.person_id);
2941: hr_utility.trace('Paternity Adoption start date: '||r_affected_pat_adop.date_start);
2942: hr_utility.trace('Paternity Adoption end date: '||r_affected_pat_adop.date_end);
2943: hr_utility.trace('Paternity Adoption projected start date: '||r_affected_pat_adop.date_projected_start);
2944: hr_utility.trace('Paternity Adoption projected end date: '||r_affected_pat_adop.date_projected_end);
2945: l_update_error := true;
2946: end;
2939: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2940: hr_utility.trace('Person id: '||r_affected_pat_adop.person_id);
2941: hr_utility.trace('Paternity Adoption start date: '||r_affected_pat_adop.date_start);
2942: hr_utility.trace('Paternity Adoption end date: '||r_affected_pat_adop.date_end);
2943: hr_utility.trace('Paternity Adoption projected start date: '||r_affected_pat_adop.date_projected_start);
2944: hr_utility.trace('Paternity Adoption projected end date: '||r_affected_pat_adop.date_projected_end);
2945: l_update_error := true;
2946: end;
2947: end loop;
2940: hr_utility.trace('Person id: '||r_affected_pat_adop.person_id);
2941: hr_utility.trace('Paternity Adoption start date: '||r_affected_pat_adop.date_start);
2942: hr_utility.trace('Paternity Adoption end date: '||r_affected_pat_adop.date_end);
2943: hr_utility.trace('Paternity Adoption projected start date: '||r_affected_pat_adop.date_projected_start);
2944: hr_utility.trace('Paternity Adoption projected end date: '||r_affected_pat_adop.date_projected_end);
2945: l_update_error := true;
2946: end;
2947: end loop;
2948: hr_utility.trace('Updated entries for '||l_pat_adop_count||' paternity adoptions');
2944: hr_utility.trace('Paternity Adoption projected end date: '||r_affected_pat_adop.date_projected_end);
2945: l_update_error := true;
2946: end;
2947: end loop;
2948: hr_utility.trace('Updated entries for '||l_pat_adop_count||' paternity adoptions');
2949: -- dbms_output.put_line(' Total SPPA absences ='|| l_pat_adop_count);
2950: else
2951: hr_utility.trace('SPP Adoption element entries already updated for new tax year');
2952: end if;
2947: end loop;
2948: hr_utility.trace('Updated entries for '||l_pat_adop_count||' paternity adoptions');
2949: -- dbms_output.put_line(' Total SPPA absences ='|| l_pat_adop_count);
2950: else
2951: hr_utility.trace('SPP Adoption element entries already updated for new tax year');
2952: end if;
2953: --
2954: commit;
2955: --
2954: commit;
2955: --
2956: exception
2957: when e_sppa_rate_not_set then
2958: hr_utility.trace('Warning: SPP ADoption rate for new tax year not set');
2959: hr_utility.trace('Unable to proceed with updating SPP ADoption entries');
2960: when e_no_new_sppa_entry then
2961: hr_utility.trace('Warning: Unable to locate SPP ADoption entry in new tax year');
2962: hr_utility.trace('Entries for SPP ADoption absences in new tax year may be non-existent');
2955: --
2956: exception
2957: when e_sppa_rate_not_set then
2958: hr_utility.trace('Warning: SPP ADoption rate for new tax year not set');
2959: hr_utility.trace('Unable to proceed with updating SPP ADoption entries');
2960: when e_no_new_sppa_entry then
2961: hr_utility.trace('Warning: Unable to locate SPP ADoption entry in new tax year');
2962: hr_utility.trace('Entries for SPP ADoption absences in new tax year may be non-existent');
2963: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
2957: when e_sppa_rate_not_set then
2958: hr_utility.trace('Warning: SPP ADoption rate for new tax year not set');
2959: hr_utility.trace('Unable to proceed with updating SPP ADoption entries');
2960: when e_no_new_sppa_entry then
2961: hr_utility.trace('Warning: Unable to locate SPP ADoption entry in new tax year');
2962: hr_utility.trace('Entries for SPP ADoption absences in new tax year may be non-existent');
2963: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
2964: when others then
2965: hr_utility.trace('Unexpected error occurred inside SPP ADoption update block');
2958: hr_utility.trace('Warning: SPP ADoption rate for new tax year not set');
2959: hr_utility.trace('Unable to proceed with updating SPP ADoption entries');
2960: when e_no_new_sppa_entry then
2961: hr_utility.trace('Warning: Unable to locate SPP ADoption entry in new tax year');
2962: hr_utility.trace('Entries for SPP ADoption absences in new tax year may be non-existent');
2963: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
2964: when others then
2965: hr_utility.trace('Unexpected error occurred inside SPP ADoption update block');
2966: hr_utility.trace('SQL error number: '||SQLCODE);
2959: hr_utility.trace('Unable to proceed with updating SPP ADoption entries');
2960: when e_no_new_sppa_entry then
2961: hr_utility.trace('Warning: Unable to locate SPP ADoption entry in new tax year');
2962: hr_utility.trace('Entries for SPP ADoption absences in new tax year may be non-existent');
2963: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
2964: when others then
2965: hr_utility.trace('Unexpected error occurred inside SPP ADoption update block');
2966: hr_utility.trace('SQL error number: '||SQLCODE);
2967: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2961: hr_utility.trace('Warning: Unable to locate SPP ADoption entry in new tax year');
2962: hr_utility.trace('Entries for SPP ADoption absences in new tax year may be non-existent');
2963: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
2964: when others then
2965: hr_utility.trace('Unexpected error occurred inside SPP ADoption update block');
2966: hr_utility.trace('SQL error number: '||SQLCODE);
2967: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2968: rollback to pre_sppa_update_status;
2969: l_update_error := true;
2962: hr_utility.trace('Entries for SPP ADoption absences in new tax year may be non-existent');
2963: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
2964: when others then
2965: hr_utility.trace('Unexpected error occurred inside SPP ADoption update block');
2966: hr_utility.trace('SQL error number: '||SQLCODE);
2967: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2968: rollback to pre_sppa_update_status;
2969: l_update_error := true;
2970: end; /* SPP Adoption update block */
2963: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
2964: when others then
2965: hr_utility.trace('Unexpected error occurred inside SPP ADoption update block');
2966: hr_utility.trace('SQL error number: '||SQLCODE);
2967: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2968: rollback to pre_sppa_update_status;
2969: l_update_error := true;
2970: end; /* SPP Adoption update block */
2971: -- Begin SPP Birth Update Block
2974: savepoint pre_sppb_update_status;
2975: --
2976: if sppb_rate_changed then
2977: --
2978: hr_utility.trace('SPP Birth element entries updating ....');
2979: --6800788 begin
2980: -- for r_affected_pat_Birth in csr_affected_leave('PA') loop
2981: for r_affected_pat_Birth in csr_affected_leave('PB') loop
2982: --6800788 end
2981: for r_affected_pat_Birth in csr_affected_leave('PB') loop
2982: --6800788 end
2983: /* SPP Birth Control call block */
2984: begin
2985: hr_utility.trace('Processing SPP Birth absence: '||r_affected_pat_Birth.maternity_id);
2986: --6800788 begin
2987: -- ssp_pab_pkg.pab_control(p_maternity_id => r_affected_pat_Birth.maternity_id,
2988: -- p_deleting => FALSE);
2989:
3000:
3001:
3002: exception
3003: when others then
3004: hr_utility.trace('Error occurred while processing SPP Birth absence: '||r_affected_pat_Birth.maternity_id);
3005: hr_utility.trace('SQL error code: '||SQLCODE);
3006: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
3007: hr_utility.trace('Person id: '||r_affected_pat_Birth.person_id);
3008: hr_utility.trace('Paternity Birth start date: '||r_affected_pat_Birth.date_start);
3001:
3002: exception
3003: when others then
3004: hr_utility.trace('Error occurred while processing SPP Birth absence: '||r_affected_pat_Birth.maternity_id);
3005: hr_utility.trace('SQL error code: '||SQLCODE);
3006: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
3007: hr_utility.trace('Person id: '||r_affected_pat_Birth.person_id);
3008: hr_utility.trace('Paternity Birth start date: '||r_affected_pat_Birth.date_start);
3009: hr_utility.trace('Paternity Birth end date: '||r_affected_pat_Birth.date_end);
3002: exception
3003: when others then
3004: hr_utility.trace('Error occurred while processing SPP Birth absence: '||r_affected_pat_Birth.maternity_id);
3005: hr_utility.trace('SQL error code: '||SQLCODE);
3006: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
3007: hr_utility.trace('Person id: '||r_affected_pat_Birth.person_id);
3008: hr_utility.trace('Paternity Birth start date: '||r_affected_pat_Birth.date_start);
3009: hr_utility.trace('Paternity Birth end date: '||r_affected_pat_Birth.date_end);
3010: hr_utility.trace('Paternity Birth projected start date: '||r_affected_pat_Birth.date_projected_start);
3003: when others then
3004: hr_utility.trace('Error occurred while processing SPP Birth absence: '||r_affected_pat_Birth.maternity_id);
3005: hr_utility.trace('SQL error code: '||SQLCODE);
3006: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
3007: hr_utility.trace('Person id: '||r_affected_pat_Birth.person_id);
3008: hr_utility.trace('Paternity Birth start date: '||r_affected_pat_Birth.date_start);
3009: hr_utility.trace('Paternity Birth end date: '||r_affected_pat_Birth.date_end);
3010: hr_utility.trace('Paternity Birth projected start date: '||r_affected_pat_Birth.date_projected_start);
3011: hr_utility.trace('Paternity Birth projected end date: '||r_affected_pat_Birth.date_projected_end);
3004: hr_utility.trace('Error occurred while processing SPP Birth absence: '||r_affected_pat_Birth.maternity_id);
3005: hr_utility.trace('SQL error code: '||SQLCODE);
3006: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
3007: hr_utility.trace('Person id: '||r_affected_pat_Birth.person_id);
3008: hr_utility.trace('Paternity Birth start date: '||r_affected_pat_Birth.date_start);
3009: hr_utility.trace('Paternity Birth end date: '||r_affected_pat_Birth.date_end);
3010: hr_utility.trace('Paternity Birth projected start date: '||r_affected_pat_Birth.date_projected_start);
3011: hr_utility.trace('Paternity Birth projected end date: '||r_affected_pat_Birth.date_projected_end);
3012: l_update_error := true;
3005: hr_utility.trace('SQL error code: '||SQLCODE);
3006: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
3007: hr_utility.trace('Person id: '||r_affected_pat_Birth.person_id);
3008: hr_utility.trace('Paternity Birth start date: '||r_affected_pat_Birth.date_start);
3009: hr_utility.trace('Paternity Birth end date: '||r_affected_pat_Birth.date_end);
3010: hr_utility.trace('Paternity Birth projected start date: '||r_affected_pat_Birth.date_projected_start);
3011: hr_utility.trace('Paternity Birth projected end date: '||r_affected_pat_Birth.date_projected_end);
3012: l_update_error := true;
3013: end;
3006: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
3007: hr_utility.trace('Person id: '||r_affected_pat_Birth.person_id);
3008: hr_utility.trace('Paternity Birth start date: '||r_affected_pat_Birth.date_start);
3009: hr_utility.trace('Paternity Birth end date: '||r_affected_pat_Birth.date_end);
3010: hr_utility.trace('Paternity Birth projected start date: '||r_affected_pat_Birth.date_projected_start);
3011: hr_utility.trace('Paternity Birth projected end date: '||r_affected_pat_Birth.date_projected_end);
3012: l_update_error := true;
3013: end;
3014: end loop;
3007: hr_utility.trace('Person id: '||r_affected_pat_Birth.person_id);
3008: hr_utility.trace('Paternity Birth start date: '||r_affected_pat_Birth.date_start);
3009: hr_utility.trace('Paternity Birth end date: '||r_affected_pat_Birth.date_end);
3010: hr_utility.trace('Paternity Birth projected start date: '||r_affected_pat_Birth.date_projected_start);
3011: hr_utility.trace('Paternity Birth projected end date: '||r_affected_pat_Birth.date_projected_end);
3012: l_update_error := true;
3013: end;
3014: end loop;
3015: hr_utility.trace('Updated entries for '||l_pat_birth_count||' paternity births');
3011: hr_utility.trace('Paternity Birth projected end date: '||r_affected_pat_Birth.date_projected_end);
3012: l_update_error := true;
3013: end;
3014: end loop;
3015: hr_utility.trace('Updated entries for '||l_pat_birth_count||' paternity births');
3016: -- dbms_output.put_line(' Total SPPB absences ='|| l_pat_birth_count);
3017: else
3018: hr_utility.trace('SPP Birth element entries already updated for new tax year');
3019: end if;
3014: end loop;
3015: hr_utility.trace('Updated entries for '||l_pat_birth_count||' paternity births');
3016: -- dbms_output.put_line(' Total SPPB absences ='|| l_pat_birth_count);
3017: else
3018: hr_utility.trace('SPP Birth element entries already updated for new tax year');
3019: end if;
3020: --
3021: commit;
3022: --
3021: commit;
3022: --
3023: exception
3024: when e_sppb_rate_not_set then
3025: hr_utility.trace('Warning: SPP Birth rate for new tax year not set');
3026: hr_utility.trace('Unable to proceed with updating SPP Birth entries');
3027: when e_no_new_sppb_entry then
3028: hr_utility.trace('Warning: Unable to locate SPP Birth entry in new tax year');
3029: hr_utility.trace('Entries for SPP Birth absences in new tax year may be non-existent');
3022: --
3023: exception
3024: when e_sppb_rate_not_set then
3025: hr_utility.trace('Warning: SPP Birth rate for new tax year not set');
3026: hr_utility.trace('Unable to proceed with updating SPP Birth entries');
3027: when e_no_new_sppb_entry then
3028: hr_utility.trace('Warning: Unable to locate SPP Birth entry in new tax year');
3029: hr_utility.trace('Entries for SPP Birth absences in new tax year may be non-existent');
3030: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
3024: when e_sppb_rate_not_set then
3025: hr_utility.trace('Warning: SPP Birth rate for new tax year not set');
3026: hr_utility.trace('Unable to proceed with updating SPP Birth entries');
3027: when e_no_new_sppb_entry then
3028: hr_utility.trace('Warning: Unable to locate SPP Birth entry in new tax year');
3029: hr_utility.trace('Entries for SPP Birth absences in new tax year may be non-existent');
3030: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
3031: when others then
3032: hr_utility.trace('Unexpected error occurred inside SPP Birth update block');
3025: hr_utility.trace('Warning: SPP Birth rate for new tax year not set');
3026: hr_utility.trace('Unable to proceed with updating SPP Birth entries');
3027: when e_no_new_sppb_entry then
3028: hr_utility.trace('Warning: Unable to locate SPP Birth entry in new tax year');
3029: hr_utility.trace('Entries for SPP Birth absences in new tax year may be non-existent');
3030: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
3031: when others then
3032: hr_utility.trace('Unexpected error occurred inside SPP Birth update block');
3033: hr_utility.trace('SQL error number: '||SQLCODE);
3026: hr_utility.trace('Unable to proceed with updating SPP Birth entries');
3027: when e_no_new_sppb_entry then
3028: hr_utility.trace('Warning: Unable to locate SPP Birth entry in new tax year');
3029: hr_utility.trace('Entries for SPP Birth absences in new tax year may be non-existent');
3030: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
3031: when others then
3032: hr_utility.trace('Unexpected error occurred inside SPP Birth update block');
3033: hr_utility.trace('SQL error number: '||SQLCODE);
3034: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
3028: hr_utility.trace('Warning: Unable to locate SPP Birth entry in new tax year');
3029: hr_utility.trace('Entries for SPP Birth absences in new tax year may be non-existent');
3030: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
3031: when others then
3032: hr_utility.trace('Unexpected error occurred inside SPP Birth update block');
3033: hr_utility.trace('SQL error number: '||SQLCODE);
3034: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
3035: rollback to pre_sppb_update_status;
3036: l_update_error := true;
3029: hr_utility.trace('Entries for SPP Birth absences in new tax year may be non-existent');
3030: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
3031: when others then
3032: hr_utility.trace('Unexpected error occurred inside SPP Birth update block');
3033: hr_utility.trace('SQL error number: '||SQLCODE);
3034: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
3035: rollback to pre_sppb_update_status;
3036: l_update_error := true;
3037: end; /* SPP Birth update block */
3030: hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
3031: when others then
3032: hr_utility.trace('Unexpected error occurred inside SPP Birth update block');
3033: hr_utility.trace('SQL error number: '||SQLCODE);
3034: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
3035: rollback to pre_sppb_update_status;
3036: l_update_error := true;
3037: end; /* SPP Birth update block */
3038:
3040:
3041: p_update_error := l_update_error;
3042:
3043: --6800788 begin
3044: hr_utility.trace(' Total Absences to be processed '|| l_count);
3045: --DBMS_OUTPUT.PUT_LINE(' Total Absences to be processed '|| l_count);
3046: for I in 1..l_count
3047: loop
3048: loop
3056: l_fail_count := l_fail_count + 1;
3057: DBMS_JOB.REMOVE(l_tbl_job(I).job_no);
3058: commit;
3059: p_job_err(l_fail_count) := ' Process Failed for person id '||l_tbl_job(I).person_id||' with absence id '||l_tbl_job(I).absence_id;
3060: hr_utility.trace(' Job '||l_tbl_job(I).job_no||' Failed for person id '||l_tbl_job(I).person_id||' with absence id '||l_tbl_job(I).absence_id);
3061: -- DBMS_OUTPUT.PUT_LINE(' Job '||l_tbl_job(I).job_no||' Failed for person id '||l_tbl_job(I).person_id||' with absence id '||l_tbl_job(I).absence_id);
3062: p_update_error := true;
3063: exit;
3064: ELSE
3069: exit;
3070: end;
3071: end loop;
3072: end loop;
3073: hr_utility.trace('Total Absences failed '||l_fail_count);
3074: --DBMS_OUTPUT.PUT_LINE('Total Absences failed '||l_fail_count);
3075: --6800788 end
3076: hr_utility.trace('Update of SSP, SMP, SAP, SPP Adoption and SPP Birth entries complete');
3077: hr_utility.trace('Leaving: '||g_package||'.update_ssp_smp_entries');
3072: end loop;
3073: hr_utility.trace('Total Absences failed '||l_fail_count);
3074: --DBMS_OUTPUT.PUT_LINE('Total Absences failed '||l_fail_count);
3075: --6800788 end
3076: hr_utility.trace('Update of SSP, SMP, SAP, SPP Adoption and SPP Birth entries complete');
3077: hr_utility.trace('Leaving: '||g_package||'.update_ssp_smp_entries');
3078:
3079:
3080: exception
3073: hr_utility.trace('Total Absences failed '||l_fail_count);
3074: --DBMS_OUTPUT.PUT_LINE('Total Absences failed '||l_fail_count);
3075: --6800788 end
3076: hr_utility.trace('Update of SSP, SMP, SAP, SPP Adoption and SPP Birth entries complete');
3077: hr_utility.trace('Leaving: '||g_package||'.update_ssp_smp_entries');
3078:
3079:
3080: exception
3081:
3081:
3082:
3083: when others
3084: then
3085: hr_utility.trace('Unexpected error occurred inside SSP/SMP element entries update procedure');
3086: hr_utility.trace('SQL error number: '||SQLCODE);
3087: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
3088: rollback to pre_update_status;
3089: p_update_error := true;
3082:
3083: when others
3084: then
3085: hr_utility.trace('Unexpected error occurred inside SSP/SMP element entries update procedure');
3086: hr_utility.trace('SQL error number: '||SQLCODE);
3087: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
3088: rollback to pre_update_status;
3089: p_update_error := true;
3090:
3083: when others
3084: then
3085: hr_utility.trace('Unexpected error occurred inside SSP/SMP element entries update procedure');
3086: hr_utility.trace('SQL error number: '||SQLCODE);
3087: hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
3088: rollback to pre_update_status;
3089: p_update_error := true;
3090:
3091: