[Home] [Help]
534: l_person_id number;
535:
536: begin
537: fnd_file.put_line(fnd_file.log,'inside ' ||l_proc);
538: hr_utility.set_location('Entering '||l_proc,10);
539:
540: -- Archive Legal Entity Level (Employer) Information
541:
542: -- Retrieve legislative parameters from the archiver payroll action
556:
557: if l_cert_type_param = 1 then
558: -- This is a normal (IRP5/IT3A) archive process
559: l_itreg_batch := 'N';
560: hr_utility.set_location(l_proc,15);
561: else
562: -- This is an ITREG batch process
563: l_itreg_batch := 'Y';
564: hr_utility.set_location(l_proc,16);
560: hr_utility.set_location(l_proc,15);
561: else
562: -- This is an ITREG batch process
563: l_itreg_batch := 'Y';
564: hr_utility.set_location(l_proc,16);
565: end if;
566:
567: hr_utility.set_location(l_proc,20);
568:
563: l_itreg_batch := 'Y';
564: hr_utility.set_location(l_proc,16);
565: end if;
566:
567: hr_utility.set_location(l_proc,20);
568:
569: -- Fetch Legal Entity level information
570: open csr_tax_info(l_legal_entity_org);
571: fetch csr_tax_info into rec_tax_info;
574: open csr_tax_file_creater_inf(l_legal_entity_org);
575: fetch csr_tax_file_creater_inf into rec_tax_file_creater_inf;
576: close csr_tax_file_creater_inf;
577:
578: hr_utility.set_location(l_proc,30);
579:
580: -- remove special characters from UIF Ref Num
581: rec_tax_info.uif_ref_num := translate(rec_tax_info.uif_ref_num,
582: 'U0123456789ABCDEFGHIJKLMNOPQRSTVWXYZ- "\/?@&$!#+=;:,''().',
582: 'U0123456789ABCDEFGHIJKLMNOPQRSTVWXYZ- "\/?@&$!#+=;:,''().',
583: 'U0123456789');
584:
585: -- archive data
586: hr_utility.set_location(l_proc,40);
587: pay_action_information_api.create_action_information
588: (
589: p_action_information_id => l_action_info_id,
590: p_action_context_id => pactid,
615: p_action_information17 => rec_tax_file_creater_inf.town_city,
616: p_action_information18 => rec_tax_file_creater_inf.postal_code
617: );
618:
619: hr_utility.set_location(l_proc,50);
620:
621: sql_range :=
622: 'SELECT distinct paf.person_id
623: FROM per_assignments_f paf,
669:
670: fnd_file.put_line(fnd_file.log,'sql_range:' ||sql_range);
671:
672: sqlstr := sql_range;
673: hr_utility.set_location('Leaving '||l_proc,999);
674: end ;
675:
676:
677:
805: l_period_recon varchar2(30);
806: l_period_recon_last_date date;
807: BEGIN
808:
809: -- hr_utility.trace_on(null,'ZATRC');
810: hr_utility.set_location('Entering '||l_proc, 10);
811: fnd_file.put_line(fnd_file.log,'inside ' ||l_proc);
812:
813: -- Get the legislative parameters from the archiver payroll action
806: l_period_recon_last_date date;
807: BEGIN
808:
809: -- hr_utility.trace_on(null,'ZATRC');
810: hr_utility.set_location('Entering '||l_proc, 10);
811: fnd_file.put_line(fnd_file.log,'inside ' ||l_proc);
812:
813: -- Get the legislative parameters from the archiver payroll action
814: select legislative_parameters,payroll_id
828: select last_day(decode(l_period_recon, '02', to_date(l_tax_year||'-02-01','yyyy-mm-dd'), '08', to_date(l_tax_year-1 ||'-08-01','yyyy-mm-dd')))
829: into l_period_recon_last_date
830: from dual;
831:
832: hr_utility.set_location(l_proc,10);
833:
834: if l_cert_type = '1' then
835: l_itreg_batch := 'N';
836: else
856: v_incl_sw := 'I';
857: end;
858: end if;
859:
860: hr_utility.set_location(l_proc,20);
861:
862: for asgrec in get_asg(l_legal_entity_org,l_payroll_id,l_tax_year,l_itreg_batch,l_period_recon,l_period_recon_last_date) loop
863: hr_utility.set_location('Assignment_id : ' || to_char(asgrec.assignment_id), 20);
864: asg_include := TRUE;
859:
860: hr_utility.set_location(l_proc,20);
861:
862: for asgrec in get_asg(l_legal_entity_org,l_payroll_id,l_tax_year,l_itreg_batch,l_period_recon,l_period_recon_last_date) loop
863: hr_utility.set_location('Assignment_id : ' || to_char(asgrec.assignment_id), 20);
864: asg_include := TRUE;
865:
866: -- Remove duplicate assignments
867: if prev_asg_id <> asgrec.assignment_id then -- revisit -- check if required ?
904: But now we are creating only one assignment action per assignment
905: */
906: if asg_include = TRUE then
907: -- Create one assignment action for every assignment
908: hr_utility.set_location('Archiving for assignment_id '||asgrec.assignment_id, 50);
909: select pay_assignment_actions_s.nextval
910: into lockingactid
911: from dual;
912:
923:
924: end if;
925: end loop;
926:
927: hr_utility.set_location('Leaving '||l_proc, 999);
928: -- hr_utility.trace_off;
929: end action_creation;
930:
931: -------------------------------------------------------------------
924: end if;
925: end loop;
926:
927: hr_utility.set_location('Leaving '||l_proc, 999);
928: -- hr_utility.trace_off;
929: end action_creation;
930:
931: -------------------------------------------------------------------
932: -- return the no of main certificate income codes . this is required
939: l_count number := 0;
940: l_key varchar2(100);
941: l_proc varchar2(100) := g_package||'get_main_inc_code_cnt';
942: begin
943: hr_utility.set_location('Entering '||l_proc, 10);
944:
945: l_key := t_code.first;
946: while l_key is not null
947: loop
957: l_key := t_code.next(l_key);
958: end loop;
959:
960: l_count := t_inc_code.count();
961: hr_utility.set_location('Main Inc Code Count:'||l_count, 20);
962: hr_utility.set_location('Leaving '||l_proc, 100);
963: return l_count;
964:
965: end get_main_inc_code_cnt;
958: end loop;
959:
960: l_count := t_inc_code.count();
961: hr_utility.set_location('Main Inc Code Count:'||l_count, 20);
962: hr_utility.set_location('Leaving '||l_proc, 100);
963: return l_count;
964:
965: end get_main_inc_code_cnt;
966:
975: l_count number := 0;
976: l_key varchar2(100);
977: l_proc varchar2(100) := g_package||'get_lump_inc_code_cnt';
978: begin
979: hr_utility.set_location('Entering '||l_proc, 10);
980:
981: l_key := t_code.first;
982: while l_key is not null
983: loop
992: l_key := t_code.next(l_key);
993: end loop;
994:
995: l_count := t_inc_code.count();
996: hr_utility.set_location('Lumpsum Dir Inc Count:'||dir_num||':'||l_count, 20);
997: hr_utility.set_location('Leaving '||l_proc, 100);
998: return l_count;
999:
1000: end get_lump_inc_code_cnt;
993: end loop;
994:
995: l_count := t_inc_code.count();
996: hr_utility.set_location('Lumpsum Dir Inc Count:'||dir_num||':'||l_count, 20);
997: hr_utility.set_location('Leaving '||l_proc, 100);
998: return l_count;
999:
1000: end get_lump_inc_code_cnt;
1001:
1211: l_4005 varchar2(100);
1212:
1213: begin
1214: null;
1215: -- hr_utility.trace_on(null,'ZATRC');
1216: fnd_file.put_line(fnd_file.log,'inside ' ||l_proc);
1217: hr_utility.set_location('Entering '||l_proc,1);
1218: hr_utility.set_location('p_assactid : '||p_assactid,1);
1219: hr_utility.set_location('p_effective_date : '||p_effective_date,1);
1213: begin
1214: null;
1215: -- hr_utility.trace_on(null,'ZATRC');
1216: fnd_file.put_line(fnd_file.log,'inside ' ||l_proc);
1217: hr_utility.set_location('Entering '||l_proc,1);
1218: hr_utility.set_location('p_assactid : '||p_assactid,1);
1219: hr_utility.set_location('p_effective_date : '||p_effective_date,1);
1220: ------------------------------------------------------------------------
1221: -- 1. Set PL/SQL Tables
1214: null;
1215: -- hr_utility.trace_on(null,'ZATRC');
1216: fnd_file.put_line(fnd_file.log,'inside ' ||l_proc);
1217: hr_utility.set_location('Entering '||l_proc,1);
1218: hr_utility.set_location('p_assactid : '||p_assactid,1);
1219: hr_utility.set_location('p_effective_date : '||p_effective_date,1);
1220: ------------------------------------------------------------------------
1221: -- 1. Set PL/SQL Tables
1222: -- Fetch ZA_Tax Element Details for last payroll_run for this assignment
1215: -- hr_utility.trace_on(null,'ZATRC');
1216: fnd_file.put_line(fnd_file.log,'inside ' ||l_proc);
1217: hr_utility.set_location('Entering '||l_proc,1);
1218: hr_utility.set_location('p_assactid : '||p_assactid,1);
1219: hr_utility.set_location('p_effective_date : '||p_effective_date,1);
1220: ------------------------------------------------------------------------
1221: -- 1. Set PL/SQL Tables
1222: -- Fetch ZA_Tax Element Details for last payroll_run for this assignment
1223: ------------------------------------------------------------------------
1241: from per_all_assignments_f paaf
1242: where paaf.assignment_id = l_assignment_id;
1243:
1244: l_combine_cert := get_parameter('P_COMBINE_CERT', l_leg_param);
1245: hr_utility.set_location('P_COMBINE_CERT:'||l_combine_cert,1);
1246:
1247: l_tax_year := get_parameter('TAX_YEAR', l_leg_param);
1248: -- Bug 13367825
1249: if l_tax_year < 2013 then
1301:
1302: end if;
1303: -- 9877034 ends
1304:
1305: hr_utility.trace('Certificate type param: '|| l_cert_type_param);
1306:
1307: if l_cert_type_param = 1 then
1308: -- This is a normal (IRP5/IT3A) archive process
1309: l_itreg_batch := 'N';
1333: from pay_assignment_actions
1334: where assignment_id = l_assignment_id
1335: and action_sequence = l_run_action_seq;
1336:
1337: hr_utility.trace('Last payroll run assignment_action_id : '|| l_run_assact_id);
1338:
1339: hr_utility.set_location(l_proc,10);
1340: open csr_tax_status (l_run_assact_id, 'Tax Status');
1341: fetch csr_tax_status into l_tax_status;
1335: and action_sequence = l_run_action_seq;
1336:
1337: hr_utility.trace('Last payroll run assignment_action_id : '|| l_run_assact_id);
1338:
1339: hr_utility.set_location(l_proc,10);
1340: open csr_tax_status (l_run_assact_id, 'Tax Status');
1341: fetch csr_tax_status into l_tax_status;
1342: close csr_tax_status;
1343:
1350: close csr_tax_status;
1351:
1352: l_tax_status := nvl(l_tax_status,'A');
1353:
1354: hr_utility.set_location(l_proc,20);
1355:
1356:
1357: ------------------------------------------------------------------------
1358: -- 2. Fetch employee's basic data
1372: , l_independent_contractor
1373: , l_labour_broker
1374: , l_lumpsum_effective_date);
1375:
1376: hr_utility.set_location(l_proc,30);
1377:
1378: -- Check person is below/above the 65 Years of age
1379: select decode (sign (add_months (to_date (l_archive_tab(0).act_info11, 'YYYYMMDD'), 780)
1380: - to_date (l_archive_tab(0).act_info4 || '-03-01', 'yyyy-mm-dd')-1), 1
1380: - to_date (l_archive_tab(0).act_info4 || '-03-01', 'yyyy-mm-dd')-1), 1
1381: , 'B', 'A')
1382: into l_65Years_age
1383: from dual;
1384: hr_utility.set_location('l_65Years_age:'||l_65Years_age,30);
1385:
1386: ------------------------------------------------------------------------
1387: -- 3. Populate t_dir_num with all directive numbers for this assignment in this tax year
1388: ------------------------------------------------------------------------
1388: ------------------------------------------------------------------------
1389: if l_itreg_batch = 'N' then
1390: for dirnum in curdirnum(l_assignment_id, l_tax_year, l_period_recon_last_date, l_period_recon) -- 9877034 fix
1391: loop
1392: hr_utility.trace('t_dir_num('||dirnum.directive_number||')');
1393: t_dir_num(dirnum.directive_number).certificate_type := null;
1394: t_dir_num(dirnum.directive_number).certificate_merged_with_main := null;
1395: end loop;
1396: end if;
1394: t_dir_num(dirnum.directive_number).certificate_merged_with_main := null;
1395: end loop;
1396: end if;
1397:
1398: hr_utility.set_location(l_proc,40);
1399:
1400: ------------------------------------------------------------------------
1401: -- 4. Populate t_code with values for income/lumpsum/deduction/gross codes for this assignment
1402: ------------------------------------------------------------------------
1406: , l_period_recon
1407: , t_code);
1408: end if;
1409:
1410: hr_utility.set_location(l_proc,50);
1411:
1412: ------------------------------------------------------------------------
1413: -- 5. Identify type(IRP5/IT3A/ITREG) of main certificate and lumpsum certificates
1414: ------------------------------------------------------------------------
1418: , l_main_cert_type
1419: , t_dir_num);
1420: end if;
1421:
1422: hr_utility.set_location(l_proc,60);
1423:
1424: ------------------------------------------------------------------------
1425: -- 6. Identify which lumpsum certificates can be merged with main certificate
1426: ------------------------------------------------------------------------
1471: end loop;
1472: end if;
1473: end if;
1474:
1475: hr_utility.set_location(l_proc,70);
1476:
1477: ------------------------------------------------------------------------
1478: -- 7. Consolidate codes for
1479: -- 1) Codes which are to be incorporated into other codes as per SARS
1486: , t_code
1487: , false);
1488: end if;
1489:
1490: hr_utility.set_location(l_proc,80);
1491:
1492: ------------------------------------------------------------------------
1493: -- 8. Populate employee's Main Certificate information into archive_tab
1494: ------------------------------------------------------------------------
1515: l_archive_tab(1).act_info26 := 'MAIN'; -- employee's main certificate
1516: l_archive_tab(1).act_info30 := l_temp_cert_num; -- Temporary certificate Number
1517: l_rec_count := 1;
1518:
1519: hr_utility.set_location(l_proc,90);
1520:
1521: -- Archive Income/Deduction codes data only if this is not an itreg batch
1522: if l_itreg_batch = 'N' then
1523: -- Employee's main certificate income/deduction information - all codes
1520:
1521: -- Archive Income/Deduction codes data only if this is not an itreg batch
1522: if l_itreg_batch = 'N' then
1523: -- Employee's main certificate income/deduction information - all codes
1524: hr_utility.set_location(l_proc,100);
1525: l_code_complete := t_code.first;
1526: loop
1527: l_code := substr(l_code_complete,1,4);
1528:
1525: l_code_complete := t_code.first;
1526: loop
1527: l_code := substr(l_code_complete,1,4);
1528:
1529: hr_utility.set_location('Code : '|| l_code_complete||' Value : '||trunc(t_code(l_code_complete).group_value),110);
1530:
1531: if length(l_code_complete)>5 and l_code not in ('4102','4115','3696','3697','3698','4116')then
1532: -- For 3907, t_code(3907-dirnum) will be archived under Lumpsums
1533: -- Lumpsum code
1535: if l_dir_num = 'To Be Advised' then
1536: -- Archive only To Be Advised record for lumpsums
1537: -- separate directive_num values will be archived in same record
1538:
1539: hr_utility.set_location(l_proc,120);
1540:
1541: l_rec_count := l_rec_count + 1;
1542: l_archive_tab(l_rec_count).assignment_id := l_assignment_id;
1543: l_archive_tab(l_rec_count).person_id := l_person_id;
1572: l_code := substr(l_code_complete,1,4);
1573: if g_code_list.exists(l_code) then
1574: if g_code_list(l_code).code_type = 'INCOME' then
1575:
1576: hr_utility.set_location(l_proc,130);
1577:
1578: -- Income Code
1579: -- For 3907, t_code(3907) will be archived as Normal Income
1580: l_rec_count := l_rec_count + 1;
1588: l_archive_tab(l_rec_count).act_info30 := l_temp_cert_num;
1589: elsif g_code_list(l_code).code_type = 'DEDUCTION' then
1590: -- Deduction code
1591:
1592: hr_utility.set_location(l_proc,140);
1593:
1594: l_rec_count := l_rec_count + 1;
1595: l_archive_tab(l_rec_count).assignment_id := l_assignment_id;
1596: l_archive_tab(l_rec_count).person_id := l_person_id;
1604: l_4005 := l_archive_tab(l_rec_count).act_info5;
1605: end if;
1606: else
1607: --ignore this code
1608: hr_utility.set_location(l_proc,150);
1609:
1610: end if;
1611: else
1612: -- Can be a Gross code, ignore this code
1617: l_code_complete := t_code.next(l_code_complete);
1618: exit when l_code_complete is null;
1619: end loop;
1620:
1621: hr_utility.set_location(l_proc,160);
1622:
1623: -- Employee's main certificate income/deduction information - final record
1624: t_final_arch.delete;
1625:
1688: l_dir_num := substr(l_code_complete,6);
1689: exit when l_code is null;
1690: end loop;
1691:
1692: hr_utility.set_location(l_proc,170);
1693:
1694: l_code := t_final_arch.first;
1695: while l_code is not null
1696: loop
1756:
1757: l_code := t_final_arch.next(l_code);
1758: end loop;
1759:
1760: hr_utility.set_location(l_proc,180);
1761:
1762: -- Employee's main certificate Gross Remunerations
1763: l_rec_count := l_rec_count + 1;
1764: l_archive_tab(l_rec_count).assignment_id := l_assignment_id;
1775: end if;
1776: l_archive_tab(l_rec_count).act_info5 := trunc(t_code(9999).group_value);-- Gross PKG , only used in excpetion log calculations
1777: l_archive_tab(l_rec_count).act_info30 := l_temp_cert_num;
1778:
1779: hr_utility.set_location(l_proc,190);
1780:
1781: -- Employee's main certificate Tax and Reasons
1782: l_rec_count := l_rec_count + 1;
1783: l_archive_tab(l_rec_count).assignment_id := l_assignment_id;
1787: -- if no deduction code, then this value must be nil
1788: l_archive_tab(l_rec_count).act_info2 := trunc(t_code(4497).group_value);
1789: end if;
1790:
1791: hr_utility.set_location(l_proc,191);
1792:
1793: l_4101 := trim(to_char(t_code(4101).group_value,'99999999990D99'));
1794: if t_dir_num.exists('To Be Advised') then -- Lumpsum amounts exist
1795: l_4102 := trim(to_char(t_code(4102).group_value + t_code(4102||'-To Be Advised').group_value,'99999999990D99'));
1805: l_4102 := trim(to_char(t_code(4102).group_value,'99999999990D99'));
1806: l_4115 := null;
1807: end if;
1808:
1809: hr_utility.set_location(l_proc,192);
1810: l_4141 := trim(to_char(t_code(4141).group_value,'99999999990D99')); -- UIF
1811: l_4142 := trim(to_char(nvl(t_code(4142).group_value,0),'99999999990D99')); -- Bug 14376752
1812: l_4149 := trim(to_char((to_number(l_4101) +
1813: to_number(l_4102) +
1815: to_number(l_4141) +
1816: to_number(nvl(l_4142,0)))
1817: ,'99999999990D99'));
1818:
1819: hr_utility.set_location(l_proc,193);
1820:
1821: -- TYE 2012 Changes
1822: IF (t_code.exists(4116) and l_tax_year > 2012 ) THEN
1823: l_4116 :=trim(to_char(t_code(4116).group_value,'99999999990D99'));
1848: end if;
1849:
1850: -- 2013 TAX YEAR CHANGES.
1851:
1852: hr_utility.set_location('l_3922_rep_err' || l_3922_rep_err,193.1);
1853:
1854: l_3922_non_taxable := nvl(get_balance_value ('Comp iro Death Non Taxable','_ASG_TAX_YTD',l_run_assact_id),0);
1855: l_3922_rfi := nvl(get_balance_value ('Comp iro Death during Emp RFI','_ASG_TAX_YTD',l_run_assact_id),0);
1856: l_3922_nrfi := nvl(get_balance_value ('Comp iro Death during Emp NRFI','_ASG_TAX_YTD',l_run_assact_id),0);
1864: if l_3922 > fnd_number.canonical_to_number(l_global_value) then
1865:
1866: if l_3922_non_taxable <> 0 then
1867: l_3922_rep_err := 'Y';
1868: hr_utility.set_location('l_3922_rep_err' || l_3922_rep_err,193.3);
1869: end if;
1870: else
1871:
1872: if (l_3922_rfi + l_3922_nrfi) <> 0 then
1870: else
1871:
1872: if (l_3922_rfi + l_3922_nrfi) <> 0 then
1873: l_3922_rep_err := 'N';
1874: hr_utility.set_location('l_3922_rep_err' || l_3922_rep_err,193.4);
1875: end if;
1876:
1877: end if;
1878: end if;
1879: -- 2013 TAX YEAR CHANGES.
1880:
1881:
1882:
1883: hr_utility.set_location(l_proc,195);
1884:
1885: l_archive_tab(l_rec_count).act_info3 := l_4101;
1886: l_archive_tab(l_rec_count).act_info4 := l_4102;
1887: l_archive_tab(l_rec_count).act_info5 := l_4115;
1900: l_archive_tab(l_rec_count).act_info12:= l_terminated_bef_sep;
1901: end if;
1902: -- 9877034 ends
1903:
1904: hr_utility.set_location(l_proc,200);
1905:
1906:
1907: ------------------------------------------------------------------------
1908: -- 9. Populate employee's Lumpsum Certificate information into archive_tab
1910: l_lump_dir_set := t_lump_dir_set.first;
1911: if l_lump_dir_set is not null then
1912: loop
1913:
1914: hr_utility.set_location('Archiving for directive_number set '||l_lump_dir_set,210);
1915: l_valid_dir_flag := false;
1916:
1917: l_cert_count := l_cert_count + 1; -- increase certificate number count
1918: l_temp_cert_num := lpad(p_assactid,25,'0')||'-'||lpad(l_cert_count,4,'0'); -- Temporary certificate number
1944: copy_record(l_archive_tab(1),l_archive_tab(l_rec_count));
1945: l_archive_tab(l_rec_count).act_info26 := 'LMPSM'; -- employee's lumpsum certificate
1946: l_archive_tab(l_rec_count).act_info30 := l_temp_cert_num;
1947:
1948: hr_utility.set_location(l_proc,220);
1949:
1950: -- Employee's lumpsum information records
1951: l_code := g_code_list.first;
1952: loop
1977: l_code := g_code_list.next(l_code);
1978: exit when l_code is null;
1979: end loop;
1980:
1981: hr_utility.set_location(l_proc,230);
1982:
1983: -- Employee's lumpsum certificate income information - final record
1984: t_final_arch.delete;
1985: l_rec_count := l_rec_count + 1;
2008: if t_code(l_code||'-'||l_dir_num).included_in is null
2009: and t_code(l_code||'-'||l_dir_num).group_value <>0 then
2010: l_code_complete := l_code||'-'||l_dir_num;
2011: l_code_arch := final_code(l_tax_year, l_code_complete,l_nature,l_tax_status,l_foreign_income); -- Bug 13367825
2012: hr_utility.set_location('l_code_arch:'||l_code_arch,230);
2013: if (t_final_arch.exists(l_code_arch)) then
2014: t_final_arch(l_code_arch).value := t_final_arch(l_code_arch).value + trunc(t_code(l_code_complete).group_value);
2015: else
2016: t_final_arch(l_code_arch).value := trunc(t_code(l_code_complete).group_value);
2072:
2073: l_code := t_final_arch.next(l_code);
2074: end loop;
2075:
2076: hr_utility.set_location(l_proc,240);
2077:
2078: -- Employee's Decuction Final Record for Code 4005
2079: if (l_valid_dir_flag) then
2080: l_rec_count := l_rec_count + 1;
2122: l_archive_tab(l_rec_count).act_info4 := l_lump_3698_val;
2123: end if;
2124: l_archive_tab(l_rec_count).act_info30 := l_temp_cert_num;
2125:
2126: hr_utility.set_location(l_proc,242);
2127:
2128: -- Employee's lumpsum tax and reasons
2129: l_rec_count := l_rec_count + 1;
2130: l_archive_tab(l_rec_count).assignment_id := l_assignment_id;
2165:
2166: l_4149 := trim(to_char(to_number(l_4102) + nvl(to_number(l_4115),0) + nvl(to_number(l_4142),0),'99999999990D99'));
2167: l_4150 := null;
2168:
2169: hr_utility.set_location(l_proc,243);
2170: -- TYE 2012 Changes
2171: IF (t_code.exists(4116) and l_tax_year > 2012 ) THEN
2172: l_4116 := t_code('4116-'||t_lump_dir_set(l_lump_dir_set).dir_1).group_value;
2173: if (t_code.exists('4116-'||t_lump_dir_set(l_lump_dir_set).dir_2)) then
2189: IF (l_4116 ='0.00') then
2190: l_4116 := null;
2191: END IF;
2192:
2193: hr_utility.set_location(l_proc,244);
2194:
2195: -- if there is no value in 3915, 3920, 3921 and 3922, then code 4115 must not be specified
2196: -- if there is still a value in 4115, then it should be reported as error in exception log
2197: l_lump_3915_value := t_code('3915-'||t_lump_dir_set(l_lump_dir_set).dir_1).group_value;
2233: l_4115 = '0.00') then
2234: l_4115 := null;
2235: end if;
2236:
2237: hr_utility.set_location(l_proc,245);
2238:
2239: if t_dir_num(t_lump_dir_set(l_lump_dir_set).dir_1).certificate_type = 'IT3A' then
2240: if l_4101 = '0.00' then l_4101 := null; end if;
2241: if l_4102 = '0.00' then l_4102 := null; end if;
2270: l_archive_tab(l_rec_count).act_info30:= l_temp_cert_num;
2271:
2272: --end if;
2273:
2274: hr_utility.set_location(l_proc,250);
2275: l_lump_dir_set := t_lump_dir_set.next(l_lump_dir_set);
2276: exit when l_lump_dir_set is null;
2277: end loop;
2278: end if;
2277: end loop;
2278: end if;
2279: end if; -- end of "if l_itreg_batch = 'N'"
2280:
2281: hr_utility.set_location(l_proc,230);
2282:
2283: ------------------------------------------------------------------------
2284: -- 10. Call archive API to archive data from l_archive_tab
2285: ------------------------------------------------------------------------
2285: ------------------------------------------------------------------------
2286:
2287: insert_archive_row(p_assactid, l_archive_tab);
2288:
2289: hr_utility.set_location('Leaving '||l_proc,999);
2290:
2291: end archive_data;
2292:
2293:
2328: l_period_recon varchar2(30); -- 9877034 fix
2329: begin
2330: fnd_file.put_line(fnd_file.log,'inside ' ||l_proc);
2331:
2332: hr_utility.set_location('Entering '|| l_proc,10);
2333:
2334: select legislative_parameters
2335: into leg_param
2336: from pay_payroll_actions
2390: IF (l_req_id = 0) THEN
2391: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Unable to Create Tax Certificate Exception Log');
2392: END IF;
2393:
2394: hr_utility.set_location('Leaving '|| l_proc,999);
2395:
2396: end archdinit;
2397:
2398:
2483: l_def_bal_id number;
2484: l_proc varchar2(100) := g_package||'set_code_tables';
2485: begin
2486: fnd_file.put_line(fnd_file.log,'inside ' ||l_proc);
2487: hr_utility.set_location('Entering '|| l_proc,10);
2488:
2489: for code_rec in csr_code_details
2490: loop
2491: -- Add next distinct code to g_code_list
2528: l_count := l_count + 1;
2529: end if;
2530: end loop;
2531:
2532: hr_utility.set_location('Leaving '|| l_proc,999);
2533: end set_code_tables;
2534:
2535:
2536: ----------------------------------------------------------------------------
2555: return 'RFI_LUMPSUM';
2556: -- Non-taxable for code 3922
2557: -- 2013 TAX YEAR CHANGES.
2558: elsif p_code in (3922) and substr(user_name,18,11) = 'NON_TAXABLE' and p_balance_sequence = 3 then
2559: hr_utility.set_location('No_taxable_once',500);
2560: return 'NON_TAXABLE_LUMPSUM';
2561: -- 2013 TAX YEAR CHANGES.
2562: elsif p_balance_sequence = 3 then -- this will take 3907 - Other Lump sums
2563: return 'LUMPSUM';
2755:
2756: l_location_code hr_locations.location_code%type;
2757:
2758: begin
2759: hr_utility.set_location('Entering '||l_proc,10);
2760: select ppa.legislative_parameters
2761: , paa.assignment_id
2762: into l_leg_param
2763: , l_assignment_id
2790: into l_effective_date
2791: from per_all_assignments_f
2792: where assignment_id = l_assignment_id;
2793:
2794: hr_utility.set_location(l_proc,20);
2795:
2796: open csr_person_info(l_assignment_id,l_effective_date);
2797: fetch csr_person_info into person_rec;
2798: close csr_person_info;
2824: and paa.action_sequence = l_max_act_seq
2825: and ppa.payroll_action_id = paa.payroll_action_id
2826: and ptp.time_period_id = ppa.time_period_id ;
2827:
2828: hr_utility.trace('Payroll ID :' || l_payroll_id);
2829:
2830: -- 9877034 starts
2831: if l_period_recon = '02' then
2832: select min(start_date), max(end_date)
2872: and paaf.effective_start_date <= l_tax_year_end_date
2873: and paaf.assignment_status_type_id = past.assignment_status_type_id
2874: and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
2875:
2876: hr_utility.trace('Assignment_id : '||l_assignment_id);
2877: hr_utility.trace('Assignment Hire Date :' || l_asg_hire_date);
2878: hr_utility.trace('Assignment Termination Date :' || l_asg_term_date);
2879:
2880: if l_asg_term_date < l_tax_year_start_date then
2873: and paaf.assignment_status_type_id = past.assignment_status_type_id
2874: and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
2875:
2876: hr_utility.trace('Assignment_id : '||l_assignment_id);
2877: hr_utility.trace('Assignment Hire Date :' || l_asg_hire_date);
2878: hr_utility.trace('Assignment Termination Date :' || l_asg_term_date);
2879:
2880: if l_asg_term_date < l_tax_year_start_date then
2881: l_late_payment := 'Y';
2874: and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
2875:
2876: hr_utility.trace('Assignment_id : '||l_assignment_id);
2877: hr_utility.trace('Assignment Hire Date :' || l_asg_hire_date);
2878: hr_utility.trace('Assignment Termination Date :' || l_asg_term_date);
2879:
2880: if l_asg_term_date < l_tax_year_start_date then
2881: l_late_payment := 'Y';
2882: else
2922: from dual;
2923: end if;
2924: end if;
2925:
2926: hr_utility.set_location(l_proc,30);
2927:
2928: if asg_rec.nature in ('A','B','C','N') then
2929: l_surname_or_trade_name := person_rec.last_name;
2930: else
2956: if asg_rec.nature = 'F' then
2957: person_rec.income_tax_ref_num := null;
2958: end if;
2959:
2960: hr_utility.set_location(l_proc,40);
2961:
2962: -- Bank Account Information
2963: -- At bank detail DDF, account type contains values
2964: -- Y (Internal Account Payment)
3005: end if;
3006: end if;
3007: */
3008:
3009: hr_utility.set_location(l_proc,50);
3010:
3011: p_employee_info_rec.assignment_id := l_assignment_id;
3012: p_employee_info_rec.person_id := l_person_id;
3013: p_employee_info_rec.action_info_category := 'ZATYE_EMPLOYEE_INFO';
3041: p_employee_info_rec.act_info28 := null; -- Old/Manual Certificate
3042: p_employee_info_rec.act_info29 := null; -- Manual Certificate Number
3043: p_employee_info_rec.act_info30 := null; -- Temporary Certificate Number -- This will be populated in deinit code
3044:
3045: hr_utility.set_location(l_proc,60);
3046:
3047: -- EMPLOYEE_CONTACT_INFORMATION
3048: p_employee_contact_info_rec.assignment_id := l_assignment_id;
3049: p_employee_contact_info_rec.person_id := l_person_id;
3050: p_employee_contact_info_rec.action_info_category := 'ZATYE_EMPLOYEE_CONTACT_INFO';
3051: p_employee_contact_info_rec.act_info1 := null; -- case when p_itreg_batch='Y' then l_itreg_cert_num else null end; -- Certificate Number -- will be generated in IRP5/IT3A process -- no certificate number for ITREG bug 10049956
3052: p_employee_contact_info_rec.act_info2 := person_rec.email_address ;
3053:
3054: hr_utility.set_location(l_proc,70);
3055:
3056: -- Populate Phone numbers
3057: get_phones (l_person_id
3058: , l_effective_date
3060: , p_employee_contact_info_rec.act_info4
3061: , p_employee_contact_info_rec.act_info5
3062: , p_employee_contact_info_rec.act_info6) ;
3063:
3064: hr_utility.set_location(l_proc,80);
3065:
3066: -- Populate Address Information
3067:
3068: -- hr_utility.trace_on(null,'ZATRC');
3064: hr_utility.set_location(l_proc,80);
3065:
3066: -- Populate Address Information
3067:
3068: -- hr_utility.trace_on(null,'ZATRC');
3069: hr_utility.trace('l_effective_date = '||to_char(l_effective_date));
3070: -- Business Address
3071: -- Fetch peron's address with address_style 'South Africa(SARS)' and address_type 'ZA-Business'
3072: /*open csr_sars_address(l_person_id, l_effective_date, 'ZA_SARS', 'ZA_BUS');
3065:
3066: -- Populate Address Information
3067:
3068: -- hr_utility.trace_on(null,'ZATRC');
3069: hr_utility.trace('l_effective_date = '||to_char(l_effective_date));
3070: -- Business Address
3071: -- Fetch peron's address with address_style 'South Africa(SARS)' and address_type 'ZA-Business'
3072: /*open csr_sars_address(l_person_id, l_effective_date, 'ZA_SARS', 'ZA_BUS');
3073: fetch csr_sars_address into p_employee_contact_info_rec.act_info7
3110: , p_employee_contact_info_rec.act_info18
3111: , p_employee_contact_info_rec.act_info19
3112: , p_employee_contact_info_rec.act_info20;
3113: close csr_sars_address;
3114: hr_utility.set_location(l_proc,90);
3115:
3116: -- Postal Address
3117: -- Fetch peron's Primary address with address_style 'South Africa'
3118: open csr_postal_address(l_person_id, l_effective_date);
3122: , p_employee_contact_info_rec.act_info24
3123: , p_employee_contact_info_rec.act_info25;
3124: close csr_postal_address;
3125:
3126: hr_utility.set_location(l_proc,100);
3127:
3128: p_employee_contact_info_rec.act_info26 := null; -- Main/Lumpsum certificate -- this will be populated in archive code
3129:
3130: open csr_location_code(person_rec.location_id);
3132: close csr_location_code;
3133:
3134: p_employee_contact_info_rec.act_info27 := l_location_code; -- Location Code
3135:
3136: hr_utility.set_location(l_proc,110);
3137:
3138: -- Set Output parameters
3139: p_assignment_id := l_assignment_id;
3140: p_person_id := l_person_id;
3143: p_nature := asg_rec.nature;
3144: p_independent_contractor := asg_rec.independent_contractor;
3145: p_labour_broker := asg_rec.labour_broker;
3146:
3147: hr_utility.set_location('Leaving '||l_proc,999);
3148: end fetch_person_data;
3149:
3150:
3151:
3198: -- Bug 14376752
3199: l_dir_num_sdl varchar2(100);
3200: -- Bug 14376752
3201: begin
3202: hr_utility.set_location('Entering '||l_proc,10);
3203:
3204:
3205:
3206: l_period_recon := p_period_recon ; -- 9901425 fix
3227: , p_context_lst => l_context_lst
3228: , p_output_table => l_output_table
3229: );
3230:
3231: hr_utility.set_location(l_proc,20);
3232:
3233: -- Output table l_output_table gives balance values per defined balance id
3234: -- Loop through output_table to separate out values for each code
3235: for j in l_output_table.first .. l_output_table.last
3249: end if;
3250: */
3251: -- fix for bug 12981111 ends
3252:
3253: hr_utility.trace('CODE : '||l_code||' VALUE : '||l_value);
3254:
3255: -- Populate code and value in t_code table using index "code"
3256: if t_code.exists(l_code) then
3257: t_code(l_code).value := t_code(l_code).value + l_value;
3344: -- set value for gross deduction
3345: t_code(4497).value := l_gross_deduction;
3346: t_code(4497).group_value := l_gross_deduction;
3347:
3348: hr_utility.set_location(l_proc,30);
3349:
3350: ----------------------------------------------------------------------------------------
3351: --- Fetch Lumpsum codes for all directive numbers (including 'To Be Advised')
3352: ----------------------------------------------------------------------------------------
3349:
3350: ----------------------------------------------------------------------------------------
3351: --- Fetch Lumpsum codes for all directive numbers (including 'To Be Advised')
3352: ----------------------------------------------------------------------------------------
3353: hr_utility.trace('Going for lumpsums .. ');
3354: l_dir_num := t_dir_num.first;
3355:
3356: if l_dir_num is not null then
3357: loop
3354: l_dir_num := t_dir_num.first;
3355:
3356: if l_dir_num is not null then
3357: loop
3358: hr_utility.trace('Directive Number : '||l_dir_num);
3359:
3360: -- reset pl/sql tables, total and count variables
3361: l_gross_non_txble_lmpsm := 0;
3362: l_gross_rfi_lmpsm := 0;
3383: ,p_context_lst => l_context_lst
3384: ,p_output_table => l_output_table
3385: );
3386:
3387: hr_utility.set_location(l_proc,40);
3388:
3389: -- Output table l_output_table gives balance values per defined balance id
3390: -- Loop through output_table to separate out values for each code
3391: for j in l_output_table.first .. l_output_table.last
3399: l_value := nvl(l_output_table(j).balance_value,0);
3400: if l_code not in (4101,4102,4115,4116) then
3401: l_value := trunc(l_value);
3402: end if;
3403: hr_utility.trace('CODE : '||l_code||' VALUE : '||l_value);
3404:
3405: -- Populate code and value in t_code table using index "code-dirnum"
3406: if t_code.exists(l_code||'-'||l_dir_num) then
3407: t_code(l_code||'-'||l_dir_num).value := t_code(l_code||'-'||l_dir_num).value + l_value;
3430: end if;
3431: end loop;
3432: end loop;
3433:
3434: hr_utility.set_location(l_proc,50);
3435: -- 2013 TAX YEAR CHANGES.
3436: t_code(3696||'-'||l_dir_num).value := l_gross_non_txble_lmpsm ;
3437: t_code(3696||'-'||l_dir_num).group_value := l_gross_non_txble_lmpsm ;
3438: -- 2013 TAX YEAR CHANGES.
3446: exit when l_dir_num is null;
3447: end loop;
3448: end if;
3449:
3450: hr_utility.set_location(l_proc,60);
3451: hr_utility.set_location ('l_gross_rfi = '|| l_gross_rfi,60);
3452:
3453: -- add 'Taxable Package Components RFI' and 'Annual Taxable Package Components RFI' to gross_rfi
3454:
3447: end loop;
3448: end if;
3449:
3450: hr_utility.set_location(l_proc,60);
3451: hr_utility.set_location ('l_gross_rfi = '|| l_gross_rfi,60);
3452:
3453: -- add 'Taxable Package Components RFI' and 'Annual Taxable Package Components RFI' to gross_rfi
3454:
3455: -- Need to do the below calc only if users have not over ride the RFI
3483: end if;
3484:
3485:
3486:
3487: hr_utility.set_location ('l_taxbale_pkg_cmp = '|| l_taxbale_pkg_cmp,60);
3488: hr_utility.set_location ('l_annual_taxbale_pkg_cmp = '|| l_annual_taxbale_pkg_cmp,60);
3489: hr_utility.set_location ('l_gross_rfi = '|| l_gross_rfi,60);
3490: hr_utility.set_location ('l_gross_txble_income = '|| l_gross_txble_income,60);
3491: hr_utility.set_location ('l_gross_nrfi = '|| l_gross_nrfi,60);
3484:
3485:
3486:
3487: hr_utility.set_location ('l_taxbale_pkg_cmp = '|| l_taxbale_pkg_cmp,60);
3488: hr_utility.set_location ('l_annual_taxbale_pkg_cmp = '|| l_annual_taxbale_pkg_cmp,60);
3489: hr_utility.set_location ('l_gross_rfi = '|| l_gross_rfi,60);
3490: hr_utility.set_location ('l_gross_txble_income = '|| l_gross_txble_income,60);
3491: hr_utility.set_location ('l_gross_nrfi = '|| l_gross_nrfi,60);
3492:
3485:
3486:
3487: hr_utility.set_location ('l_taxbale_pkg_cmp = '|| l_taxbale_pkg_cmp,60);
3488: hr_utility.set_location ('l_annual_taxbale_pkg_cmp = '|| l_annual_taxbale_pkg_cmp,60);
3489: hr_utility.set_location ('l_gross_rfi = '|| l_gross_rfi,60);
3490: hr_utility.set_location ('l_gross_txble_income = '|| l_gross_txble_income,60);
3491: hr_utility.set_location ('l_gross_nrfi = '|| l_gross_nrfi,60);
3492:
3493: else
3486:
3487: hr_utility.set_location ('l_taxbale_pkg_cmp = '|| l_taxbale_pkg_cmp,60);
3488: hr_utility.set_location ('l_annual_taxbale_pkg_cmp = '|| l_annual_taxbale_pkg_cmp,60);
3489: hr_utility.set_location ('l_gross_rfi = '|| l_gross_rfi,60);
3490: hr_utility.set_location ('l_gross_txble_income = '|| l_gross_txble_income,60);
3491: hr_utility.set_location ('l_gross_nrfi = '|| l_gross_nrfi,60);
3492:
3493: else
3494: l_gross_nrfi := l_gross_txble_income - l_gross_rfi;
3487: hr_utility.set_location ('l_taxbale_pkg_cmp = '|| l_taxbale_pkg_cmp,60);
3488: hr_utility.set_location ('l_annual_taxbale_pkg_cmp = '|| l_annual_taxbale_pkg_cmp,60);
3489: hr_utility.set_location ('l_gross_rfi = '|| l_gross_rfi,60);
3490: hr_utility.set_location ('l_gross_txble_income = '|| l_gross_txble_income,60);
3491: hr_utility.set_location ('l_gross_nrfi = '|| l_gross_nrfi,60);
3492:
3493: else
3494: l_gross_nrfi := l_gross_txble_income - l_gross_rfi;
3495: end if;
3508: t_code(9999).value := l_gross_pkg; -- Archive Gross PKG - for in exception log calcualtions
3509: t_code(9999).group_value := l_gross_pkg;
3510:
3511: -- UIF Conributions
3512: hr_utility.set_location(l_proc,70);
3513: t_code(4141).value := nvl(get_balance_value ('UIF Employee Contribution', '_ASG_TAX_YTD', p_assignment_action_id),0)
3514: + nvl(get_balance_value ('UIF Employer Contribution', '_ASG_TAX_YTD', p_assignment_action_id),0);
3515: t_code(4141).group_value := t_code(4141).value;
3516:
3519:
3520: t_code(4142).value := nvl(get_balance_value ('Skills Levy NE', '_ASG_TAX_YTD', p_assignment_action_id),0);
3521: t_code(4142).group_value := t_code(4142).value;
3522:
3523: hr_utility.trace('Going for lumpsums for SDL .. ');
3524: l_dir_num_sdl := t_dir_num.first;
3525:
3526: if l_dir_num_sdl is not null then
3527: loop
3524: l_dir_num_sdl := t_dir_num.first;
3525:
3526: if l_dir_num_sdl is not null then
3527: loop
3528: hr_utility.trace('Directive Number : '||l_dir_num_sdl);
3529:
3530: -- reset pl/sql tables, total and count variables
3531: l_context_lst.delete;
3532:
3545: end if;
3546:
3547: -- Bug 14376752
3548:
3549: hr_utility.set_location('Leaving '||l_proc,999);
3550:
3551: end fetch_balances;
3552:
3553:
3577: l_deductions number := 0;
3578: l_proc varchar2(100) := g_package||'populate_irp5_indicators';
3579: l_4115_ToBeAdvised number(15, 3) := 0;
3580: begin
3581: hr_utility.set_location('Entering '||l_proc,10);
3582: -----------------------------------------
3583: -- Checking type of Lumpsum Certificates
3584: -----------------------------------------
3585: l_dir_num := t_dir_num.first;
3584: -----------------------------------------
3585: l_dir_num := t_dir_num.first;
3586: if l_dir_num is not null then
3587: loop
3588: hr_utility.set_location('Directive Num '||l_dir_num,20);
3589: if l_dir_num <> 'To Be Advised' then
3590: l_total_tax := t_code('4102-'||l_dir_num).group_value
3591: + t_code('4115-'||l_dir_num).group_value;
3592: l_lumpsum_income := t_code('3696-'||l_dir_num).group_value
3592: l_lumpsum_income := t_code('3696-'||l_dir_num).group_value
3593: + t_code('3697-'||l_dir_num).group_value
3594: + t_code('3698-'||l_dir_num).group_value;
3595:
3596: hr_utility.set_location('Total Tax '||l_total_tax,20);
3597: hr_utility.set_location('l_lumpsum_income '||l_lumpsum_income,20);
3598:
3599: if l_lumpsum_income<=0 then
3600: -- If the assignment had zero for all his balances then don't include him
3593: + t_code('3697-'||l_dir_num).group_value
3594: + t_code('3698-'||l_dir_num).group_value;
3595:
3596: hr_utility.set_location('Total Tax '||l_total_tax,20);
3597: hr_utility.set_location('l_lumpsum_income '||l_lumpsum_income,20);
3598:
3599: if l_lumpsum_income<=0 then
3600: -- If the assignment had zero for all his balances then don't include him
3601: t_dir_num(l_dir_num).certificate_type := 'A';
3608: end if;
3609:
3610: l_all_lumpsum_income := l_all_lumpsum_income + l_lumpsum_income;
3611: end if;
3612: hr_utility.set_location('Cert type for '||l_dir_num||' is ' || t_dir_num(l_dir_num).certificate_type,10);
3613: l_dir_num := t_dir_num.next(l_dir_num);
3614: exit when l_dir_num is null;
3615: end loop;
3616: end if;
3617:
3618: -------------------------------------
3619: -- Checking type of main certificate
3620: -------------------------------------
3621: hr_utility.set_location(l_proc,30);
3622: l_site := t_code('4101').group_value;
3623:
3624: if t_code.exists('4102-To Be Advised') then
3625: l_paye_plus_vol_tax := t_code('4102').group_value + t_code('4102-To Be Advised').group_value;
3630:
3631: -- Total Tax paid on main certificate
3632: l_total_tax := l_paye_plus_vol_tax + l_site + l_4115_ToBeAdvised;
3633:
3634: hr_utility.set_location('l_site '||l_site,20);
3635: hr_utility.set_location('l_paye_plus_vol_tax '||l_paye_plus_vol_tax,20);
3636: hr_utility.set_location('l_4115_ToBeAdvised '||l_4115_ToBeAdvised,20);
3637: hr_utility.set_location('l_total_tax '||l_total_tax,20);
3638: hr_utility.set_location('l_main_cert_income '||l_main_cert_income,20);
3631: -- Total Tax paid on main certificate
3632: l_total_tax := l_paye_plus_vol_tax + l_site + l_4115_ToBeAdvised;
3633:
3634: hr_utility.set_location('l_site '||l_site,20);
3635: hr_utility.set_location('l_paye_plus_vol_tax '||l_paye_plus_vol_tax,20);
3636: hr_utility.set_location('l_4115_ToBeAdvised '||l_4115_ToBeAdvised,20);
3637: hr_utility.set_location('l_total_tax '||l_total_tax,20);
3638: hr_utility.set_location('l_main_cert_income '||l_main_cert_income,20);
3639: hr_utility.set_location('l_deductions '||l_deductions,20);
3632: l_total_tax := l_paye_plus_vol_tax + l_site + l_4115_ToBeAdvised;
3633:
3634: hr_utility.set_location('l_site '||l_site,20);
3635: hr_utility.set_location('l_paye_plus_vol_tax '||l_paye_plus_vol_tax,20);
3636: hr_utility.set_location('l_4115_ToBeAdvised '||l_4115_ToBeAdvised,20);
3637: hr_utility.set_location('l_total_tax '||l_total_tax,20);
3638: hr_utility.set_location('l_main_cert_income '||l_main_cert_income,20);
3639: hr_utility.set_location('l_deductions '||l_deductions,20);
3640:
3633:
3634: hr_utility.set_location('l_site '||l_site,20);
3635: hr_utility.set_location('l_paye_plus_vol_tax '||l_paye_plus_vol_tax,20);
3636: hr_utility.set_location('l_4115_ToBeAdvised '||l_4115_ToBeAdvised,20);
3637: hr_utility.set_location('l_total_tax '||l_total_tax,20);
3638: hr_utility.set_location('l_main_cert_income '||l_main_cert_income,20);
3639: hr_utility.set_location('l_deductions '||l_deductions,20);
3640:
3641: -- Total Main certificate income
3634: hr_utility.set_location('l_site '||l_site,20);
3635: hr_utility.set_location('l_paye_plus_vol_tax '||l_paye_plus_vol_tax,20);
3636: hr_utility.set_location('l_4115_ToBeAdvised '||l_4115_ToBeAdvised,20);
3637: hr_utility.set_location('l_total_tax '||l_total_tax,20);
3638: hr_utility.set_location('l_main_cert_income '||l_main_cert_income,20);
3639: hr_utility.set_location('l_deductions '||l_deductions,20);
3640:
3641: -- Total Main certificate income
3642: if t_code.exists('3697-To Be Advised') then
3635: hr_utility.set_location('l_paye_plus_vol_tax '||l_paye_plus_vol_tax,20);
3636: hr_utility.set_location('l_4115_ToBeAdvised '||l_4115_ToBeAdvised,20);
3637: hr_utility.set_location('l_total_tax '||l_total_tax,20);
3638: hr_utility.set_location('l_main_cert_income '||l_main_cert_income,20);
3639: hr_utility.set_location('l_deductions '||l_deductions,20);
3640:
3641: -- Total Main certificate income
3642: if t_code.exists('3697-To Be Advised') then
3643: l_main_cert_income := t_code(3696).group_value
3654:
3655: -- Total Main certificate deductions
3656: l_deductions := t_code(4497).group_value;
3657:
3658: hr_utility.set_location(l_proc,40);
3659: if l_main_cert_income<=0 and l_deductions<=0 then
3660: -- If the assignment had zero for all his balances
3661: -- then don't include him
3662: p_main_cert_type := 'A';
3666: else
3667: p_main_cert_type := 'IT3A';
3668: end if;
3669: end if;
3670: hr_utility.set_location('Cert type for Main Certificate is ' || p_main_cert_type,50);
3671: hr_utility.set_location('Leaving '||l_proc,999);
3672: end populate_irp5_indicators;
3673:
3674:
3667: p_main_cert_type := 'IT3A';
3668: end if;
3669: end if;
3670: hr_utility.set_location('Cert type for Main Certificate is ' || p_main_cert_type,50);
3671: hr_utility.set_location('Leaving '||l_proc,999);
3672: end populate_irp5_indicators;
3673:
3674:
3675: -----------------------------------------------------------------------------------------
3711:
3712: t_inc_code code_table;
3713:
3714: begin
3715: hr_utility.set_location('Entering '|| l_proc,10);
3716:
3717: l_main_inc_code_count := get_main_inc_code_cnt(t_code,t_inc_code);
3718:
3719: if t_dir_num.exists('To Be Advised') then
3722: -- TODO need to chek if this is required or not
3723: l_lump_inc_code_count := get_lump_inc_code_cnt(t_code,p_main_cert_dir_num,t_inc_code);
3724: IF ((l_lump_inc_code_count) <=13) THEN
3725: if p_main_cert_dir_num is not null then
3726: hr_utility.set_location('Merging Main Dir with Main:'|| p_main_cert_dir_num,10);
3727: l_combined_cert_count:= 1;
3728: p_directive_1 := p_main_cert_dir_num;
3729: end if;
3730: END IF;
3739: if t_dir_num(l_dir_num).certificate_type = p_main_cert_type then
3740:
3741: l_lump_inc_code_count := get_lump_inc_code_cnt(t_code,l_dir_num,t_inc_code);
3742: IF ((l_lump_inc_code_count) <=13) THEN
3743: hr_utility.set_location('Merging Dir with Main:'|| l_dir_num,10);
3744: l_combined_cert_count := l_combined_cert_count + 1;
3745: t_dir_num(l_dir_num).certificate_merged_with_main := 'Y';
3746:
3747: if l_combined_cert_count = 1 then
3761: exit when l_dir_num is null;
3762: end loop;
3763: end if;
3764:
3765: hr_utility.set_location('Directive Number 1 : '|| p_directive_1,20);
3766: hr_utility.set_location('Directive Number 2 : '|| p_directive_2,20);
3767: hr_utility.set_location('Directive Number 3 : '|| p_directive_3,20);
3768:
3769: -----------------------------------------------------------------------
3762: end loop;
3763: end if;
3764:
3765: hr_utility.set_location('Directive Number 1 : '|| p_directive_1,20);
3766: hr_utility.set_location('Directive Number 2 : '|| p_directive_2,20);
3767: hr_utility.set_location('Directive Number 3 : '|| p_directive_3,20);
3768:
3769: -----------------------------------------------------------------------
3770: -- Group the remaining Lumpsum Dir as set of 3 for IRP5,IT3A Seperately
3763: end if;
3764:
3765: hr_utility.set_location('Directive Number 1 : '|| p_directive_1,20);
3766: hr_utility.set_location('Directive Number 2 : '|| p_directive_2,20);
3767: hr_utility.set_location('Directive Number 3 : '|| p_directive_3,20);
3768:
3769: -----------------------------------------------------------------------
3770: -- Group the remaining Lumpsum Dir as set of 3 for IRP5,IT3A Seperately
3771: -----------------------------------------------------------------------
3781: l_dir_num := t_dir_num.first;
3782: while l_dir_num is not null
3783: loop
3784: IF ((i = 1 AND t_dir_num(l_dir_num).certificate_type='IRP5') OR (i = 2 AND t_dir_num(l_dir_num).certificate_type='IT3A')) THEN
3785: hr_utility.set_location(l_dir_num||':'||t_dir_num(l_dir_num).certificate_type||':'||t_dir_num(l_dir_num).certificate_merged_with_main,60);
3786: IF t_dir_num(l_dir_num).certificate_merged_with_main is null THEN
3787: l_dir_count := l_dir_count + 1;
3788: l_lump_inc_code_count := get_lump_inc_code_cnt(t_code,l_dir_num,t_inc_code);
3789: IF (l_dir_count > 3 OR l_lump_inc_code_count > 13) THEN
3803: end loop; -- End While
3804: END LOOP; -- End For
3805:
3806:
3807: hr_utility.set_location('Leaving '|| l_proc,999);
3808: end combine_certificates;
3809:
3810:
3811:
3895: t_code(code_B).included_in := code_A;
3896: end merge;
3897:
3898: begin
3899: hr_utility.set_location('Entering '||l_proc,10);
3900:
3901: ------------------------------------------------------------------------------
3902: -- 1) Codes whose values have been directed by SARS to be merged with other codes
3903: ------------------------------------------------------------------------------
3931: merge(4004,4003);
3932:
3933: ELSE -- pre merge
3934:
3935: hr_utility.set_location(l_proc,20);
3936: for i in g_code_bal.first .. g_code_bal.last loop
3937:
3938: l_code := g_code_bal(i).code;
3939: if (t_code.exists(l_code) and l_prev_tcode <> l_code ) then
3946:
3947: if (g_code_bal(i).sub_type in ('RFI','NRFI','PKG') and t_code(l_code).included_in is null) then
3948: l_gross_txble_income := l_gross_txble_income + t_code(l_code).group_value;
3949: end if;
3950: hr_utility.set_location('CODE : '||l_code||' GROUP VALUE : '||t_code(l_code).group_value,12);
3951: end if;
3952: l_prev_tcode := l_code;
3953: end loop;
3954:
3951: end if;
3952: l_prev_tcode := l_code;
3953: end loop;
3954:
3955: hr_utility.set_location('l_gross_txble_income: '|| l_gross_txble_income,99);
3956:
3957: if (t_code.exists(3697)) then
3958: l_gross_rfi := t_code(3697).group_value;
3959: end if;
3962: t_code(3698).value := l_gross_txble_income - l_gross_rfi ;
3963: t_code(3698).group_value := l_gross_txble_income - l_gross_rfi ;
3964: end if;
3965:
3966: hr_utility.set_location(l_proc,21);
3967:
3968: ---------------------------------------------------------------------------------------
3969: -- 2) Codes of lumpsum certificates which have been identified to be merged with main cert
3970: ---------------------------------------------------------------------------------------
3998:
3999: end loop;
4000: end if;
4001:
4002: hr_utility.set_location(l_proc,30);
4003:
4004: ---------------------------------------------------------------------------------------
4005: -- 3) For codes 3907, 3696,3697, and 3698
4006: -- Merge t_code(code-To Be Advised) into t_code(code)
4014: end if;
4015: END IF;
4016:
4017:
4018: hr_utility.set_location('Leaving '||l_proc,999);
4019: end consolidate_codes;
4020:
4021:
4022:
4040: l_reason_code varchar2(2) := '&&';
4041: l_normal_directive_value number;
4042: l_proc varchar2(100) := g_package || 'it3a_reason_code';
4043: begin
4044: hr_utility.set_location('Entering '||l_proc,10);
4045: begin
4046: l_tax_threshold_ind := nvl(get_balance_value('Tax Threshold Ind','_ASG_TAX_YTD',p_run_assact_id),0);
4047: exception when others then
4048: l_tax_threshold_ind := 0;
4103: l_reason_code := '04';
4104: end if;
4105: end if;
4106:
4107: hr_utility.set_location('IT3A Reason Code : '||l_reason_code,20);
4108:
4109: hr_utility.set_location('Leaving '||l_proc,999);
4110: return l_reason_code;
4111: end it3a_reason_code;
4105: end if;
4106:
4107: hr_utility.set_location('IT3A Reason Code : '||l_reason_code,20);
4108:
4109: hr_utility.set_location('Leaving '||l_proc,999);
4110: return l_reason_code;
4111: end it3a_reason_code;
4112:
4113:
4523: l_proc constant varchar2(50):= g_package||'insert_archive_row';
4524: l_ovn number;
4525: l_action_id number;
4526: begin
4527: hr_utility.set_location('Entering: '||l_proc,1);
4528: if p_tab_rec_data.count > 0 then
4529: for i in p_tab_rec_data.first .. p_tab_rec_data.last loop
4530:
4531: hr_utility.trace('Defining category '|| p_tab_rec_data(i).action_info_category);
4527: hr_utility.set_location('Entering: '||l_proc,1);
4528: if p_tab_rec_data.count > 0 then
4529: for i in p_tab_rec_data.first .. p_tab_rec_data.last loop
4530:
4531: hr_utility.trace('Defining category '|| p_tab_rec_data(i).action_info_category);
4532: hr_utility.trace('action_context_id = '|| p_assactid);
4533: hr_utility.trace('p_tab_rec_data(i).action_info_category = '|| p_tab_rec_data(i).action_info_category);
4534: if p_tab_rec_data(i).action_info_category is not null then
4535: pay_action_information_api.create_action_information(
4528: if p_tab_rec_data.count > 0 then
4529: for i in p_tab_rec_data.first .. p_tab_rec_data.last loop
4530:
4531: hr_utility.trace('Defining category '|| p_tab_rec_data(i).action_info_category);
4532: hr_utility.trace('action_context_id = '|| p_assactid);
4533: hr_utility.trace('p_tab_rec_data(i).action_info_category = '|| p_tab_rec_data(i).action_info_category);
4534: if p_tab_rec_data(i).action_info_category is not null then
4535: pay_action_information_api.create_action_information(
4536: p_action_information_id => l_action_id,
4529: for i in p_tab_rec_data.first .. p_tab_rec_data.last loop
4530:
4531: hr_utility.trace('Defining category '|| p_tab_rec_data(i).action_info_category);
4532: hr_utility.trace('action_context_id = '|| p_assactid);
4533: hr_utility.trace('p_tab_rec_data(i).action_info_category = '|| p_tab_rec_data(i).action_info_category);
4534: if p_tab_rec_data(i).action_info_category is not null then
4535: pay_action_information_api.create_action_information(
4536: p_action_information_id => l_action_id,
4537: p_object_version_number => l_ovn,
4573: );
4574: end if;
4575: end loop;
4576: end if;
4577: hr_utility.set_location('Leaving: '||l_proc,999);
4578: end insert_archive_row;
4579:
4580:
4581: end PAY_ZA_TYE_ARCHIVE_PKG;