[Home] [Help]
968: GROUP BY irp5.code,faic2.CONTEXT ;
969:
970: begin
971: retcode := 0;
972: -- hr_utility.trace_on(null,'ZATYEVL');
973: FND_FILE.PUT_LINE(FND_FILE.LOG,'In validate_tye_data');
974: FND_FILE.PUT_LINE(FND_FILE.LOG,'p_payroll_action_id :' || p_payroll_action_id);
975: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inside validate_tye_data');
976:
1380: a:=retiremnt_fund_lmpsum.first();
1381: FOR i in 1..retiremnt_fund_lmpsum.count
1382: loop
1383:
1384: hr_utility.set_location('a:'||a,10);
1385: hr_utility.set_location('substr(a,1,4):'||substr(a,1,4),10);
1386: hr_utility.set_location('retiremnt_fund_lmpsum(a):'||retiremnt_fund_lmpsum(a),10);
1387:
1388: IF (substr(a,1,4)=4115 and retiremnt_fund_lmpsum(a) <>0 ) THEN
1381: FOR i in 1..retiremnt_fund_lmpsum.count
1382: loop
1383:
1384: hr_utility.set_location('a:'||a,10);
1385: hr_utility.set_location('substr(a,1,4):'||substr(a,1,4),10);
1386: hr_utility.set_location('retiremnt_fund_lmpsum(a):'||retiremnt_fund_lmpsum(a),10);
1387:
1388: IF (substr(a,1,4)=4115 and retiremnt_fund_lmpsum(a) <>0 ) THEN
1389: l_tax_dir_num := substr(a,5);
1382: loop
1383:
1384: hr_utility.set_location('a:'||a,10);
1385: hr_utility.set_location('substr(a,1,4):'||substr(a,1,4),10);
1386: hr_utility.set_location('retiremnt_fund_lmpsum(a):'||retiremnt_fund_lmpsum(a),10);
1387:
1388: IF (substr(a,1,4)=4115 and retiremnt_fund_lmpsum(a) <>0 ) THEN
1389: l_tax_dir_num := substr(a,5);
1390: hr_utility.set_location('l_tax_dir_num:'||l_tax_dir_num,10);
1386: hr_utility.set_location('retiremnt_fund_lmpsum(a):'||retiremnt_fund_lmpsum(a),10);
1387:
1388: IF (substr(a,1,4)=4115 and retiremnt_fund_lmpsum(a) <>0 ) THEN
1389: l_tax_dir_num := substr(a,5);
1390: hr_utility.set_location('l_tax_dir_num:'||l_tax_dir_num,10);
1391:
1392: if ((retiremnt_fund_lmpsum.exists('3920'||l_tax_dir_num) and retiremnt_fund_lmpsum('3920'||l_tax_dir_num) <> 0)
1393: OR (retiremnt_fund_lmpsum.exists('3915'||l_tax_dir_num) and retiremnt_fund_lmpsum('3915'||l_tax_dir_num) <> 0)) then
1394: null;
1412:
1413: END LOOP ; -- End of assignment Loop
1414: FND_FILE.PUT_LINE(FND_FILE.LOG,'End of log file');
1415: FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1416: -- hr_utility.trace_off;
1417: EXCEPTION
1418: WHEN OTHERS then
1419: errbuf := substr(SQLERRM,1,255);
1420: retcode := sqlcode;
1432:
1433: l_invalid_in_alphanum varchar2(100):= '~`!@#$%^&*()_+=|\[]{}<>":;?/.';
1434: l_invalid_in_alpha varchar2(100):= '~`!@#$%^&*()_+=|\[]{}<>":;?/.0123456789';
1435: begin
1436: hr_utility.trace('Validating value : '||p_input_value||' as '||p_mode);
1437: if p_mode = 'NUMERIC' then
1438: l_translated := translate (p_input_value
1439: , '~' || l_num
1440: , '~');
1459: null;
1460: -- we will not validate for freetext
1461: end if;
1462:
1463: hr_utility.trace('l_translated='||l_translated);
1464: return l_result ;
1465: end validate_character_set;
1466:
1467:
1507: l_msg_count number(5):=0;
1508: l_warn_count number(5):=0;
1509: begin
1510:
1511: hr_utility.trace('P_STYLE:'||P_STYLE);
1512: hr_utility.trace('P_TAX_YEAR:'||P_TAX_YEAR);
1513: hr_utility.trace('P_ADDRESS_TYPE:'||P_ADDRESS_TYPE);
1514: hr_utility.trace('P_PRIMARY_FLAG:'||P_PRIMARY_FLAG);
1515: hr_utility.trace('P_UNIT_NUMBER:'||P_UNIT_NUMBER);
1508: l_warn_count number(5):=0;
1509: begin
1510:
1511: hr_utility.trace('P_STYLE:'||P_STYLE);
1512: hr_utility.trace('P_TAX_YEAR:'||P_TAX_YEAR);
1513: hr_utility.trace('P_ADDRESS_TYPE:'||P_ADDRESS_TYPE);
1514: hr_utility.trace('P_PRIMARY_FLAG:'||P_PRIMARY_FLAG);
1515: hr_utility.trace('P_UNIT_NUMBER:'||P_UNIT_NUMBER);
1516: hr_utility.trace('P_COMPLEX:'||P_COMPLEX);
1509: begin
1510:
1511: hr_utility.trace('P_STYLE:'||P_STYLE);
1512: hr_utility.trace('P_TAX_YEAR:'||P_TAX_YEAR);
1513: hr_utility.trace('P_ADDRESS_TYPE:'||P_ADDRESS_TYPE);
1514: hr_utility.trace('P_PRIMARY_FLAG:'||P_PRIMARY_FLAG);
1515: hr_utility.trace('P_UNIT_NUMBER:'||P_UNIT_NUMBER);
1516: hr_utility.trace('P_COMPLEX:'||P_COMPLEX);
1517: hr_utility.trace('P_STREET_NUMBER:'||P_STREET_NUMBER);
1510:
1511: hr_utility.trace('P_STYLE:'||P_STYLE);
1512: hr_utility.trace('P_TAX_YEAR:'||P_TAX_YEAR);
1513: hr_utility.trace('P_ADDRESS_TYPE:'||P_ADDRESS_TYPE);
1514: hr_utility.trace('P_PRIMARY_FLAG:'||P_PRIMARY_FLAG);
1515: hr_utility.trace('P_UNIT_NUMBER:'||P_UNIT_NUMBER);
1516: hr_utility.trace('P_COMPLEX:'||P_COMPLEX);
1517: hr_utility.trace('P_STREET_NUMBER:'||P_STREET_NUMBER);
1518: hr_utility.trace('P_STREET_NAME:'||P_STREET_NAME);
1511: hr_utility.trace('P_STYLE:'||P_STYLE);
1512: hr_utility.trace('P_TAX_YEAR:'||P_TAX_YEAR);
1513: hr_utility.trace('P_ADDRESS_TYPE:'||P_ADDRESS_TYPE);
1514: hr_utility.trace('P_PRIMARY_FLAG:'||P_PRIMARY_FLAG);
1515: hr_utility.trace('P_UNIT_NUMBER:'||P_UNIT_NUMBER);
1516: hr_utility.trace('P_COMPLEX:'||P_COMPLEX);
1517: hr_utility.trace('P_STREET_NUMBER:'||P_STREET_NUMBER);
1518: hr_utility.trace('P_STREET_NAME:'||P_STREET_NAME);
1519: hr_utility.trace('P_SUBURB_DISTRICT:'||P_SUBURB_DISTRICT);
1512: hr_utility.trace('P_TAX_YEAR:'||P_TAX_YEAR);
1513: hr_utility.trace('P_ADDRESS_TYPE:'||P_ADDRESS_TYPE);
1514: hr_utility.trace('P_PRIMARY_FLAG:'||P_PRIMARY_FLAG);
1515: hr_utility.trace('P_UNIT_NUMBER:'||P_UNIT_NUMBER);
1516: hr_utility.trace('P_COMPLEX:'||P_COMPLEX);
1517: hr_utility.trace('P_STREET_NUMBER:'||P_STREET_NUMBER);
1518: hr_utility.trace('P_STREET_NAME:'||P_STREET_NAME);
1519: hr_utility.trace('P_SUBURB_DISTRICT:'||P_SUBURB_DISTRICT);
1520: hr_utility.trace('P_TOWN_OR_CITY:'||P_TOWN_OR_CITY);
1513: hr_utility.trace('P_ADDRESS_TYPE:'||P_ADDRESS_TYPE);
1514: hr_utility.trace('P_PRIMARY_FLAG:'||P_PRIMARY_FLAG);
1515: hr_utility.trace('P_UNIT_NUMBER:'||P_UNIT_NUMBER);
1516: hr_utility.trace('P_COMPLEX:'||P_COMPLEX);
1517: hr_utility.trace('P_STREET_NUMBER:'||P_STREET_NUMBER);
1518: hr_utility.trace('P_STREET_NAME:'||P_STREET_NAME);
1519: hr_utility.trace('P_SUBURB_DISTRICT:'||P_SUBURB_DISTRICT);
1520: hr_utility.trace('P_TOWN_OR_CITY:'||P_TOWN_OR_CITY);
1521: hr_utility.trace('P_POSTAL_CODE:'||P_POSTAL_CODE);
1514: hr_utility.trace('P_PRIMARY_FLAG:'||P_PRIMARY_FLAG);
1515: hr_utility.trace('P_UNIT_NUMBER:'||P_UNIT_NUMBER);
1516: hr_utility.trace('P_COMPLEX:'||P_COMPLEX);
1517: hr_utility.trace('P_STREET_NUMBER:'||P_STREET_NUMBER);
1518: hr_utility.trace('P_STREET_NAME:'||P_STREET_NAME);
1519: hr_utility.trace('P_SUBURB_DISTRICT:'||P_SUBURB_DISTRICT);
1520: hr_utility.trace('P_TOWN_OR_CITY:'||P_TOWN_OR_CITY);
1521: hr_utility.trace('P_POSTAL_CODE:'||P_POSTAL_CODE);
1522: hr_utility.trace('P_SAME_AS_RES_ADD:'||P_SAME_AS_RES_ADD);
1515: hr_utility.trace('P_UNIT_NUMBER:'||P_UNIT_NUMBER);
1516: hr_utility.trace('P_COMPLEX:'||P_COMPLEX);
1517: hr_utility.trace('P_STREET_NUMBER:'||P_STREET_NUMBER);
1518: hr_utility.trace('P_STREET_NAME:'||P_STREET_NAME);
1519: hr_utility.trace('P_SUBURB_DISTRICT:'||P_SUBURB_DISTRICT);
1520: hr_utility.trace('P_TOWN_OR_CITY:'||P_TOWN_OR_CITY);
1521: hr_utility.trace('P_POSTAL_CODE:'||P_POSTAL_CODE);
1522: hr_utility.trace('P_SAME_AS_RES_ADD:'||P_SAME_AS_RES_ADD);
1523: hr_utility.trace('P_ADDRESS_LINE1:'||P_ADDRESS_LINE1);
1516: hr_utility.trace('P_COMPLEX:'||P_COMPLEX);
1517: hr_utility.trace('P_STREET_NUMBER:'||P_STREET_NUMBER);
1518: hr_utility.trace('P_STREET_NAME:'||P_STREET_NAME);
1519: hr_utility.trace('P_SUBURB_DISTRICT:'||P_SUBURB_DISTRICT);
1520: hr_utility.trace('P_TOWN_OR_CITY:'||P_TOWN_OR_CITY);
1521: hr_utility.trace('P_POSTAL_CODE:'||P_POSTAL_CODE);
1522: hr_utility.trace('P_SAME_AS_RES_ADD:'||P_SAME_AS_RES_ADD);
1523: hr_utility.trace('P_ADDRESS_LINE1:'||P_ADDRESS_LINE1);
1524: hr_utility.trace('P_ADDRESS_LINE2:'||P_ADDRESS_LINE2);
1517: hr_utility.trace('P_STREET_NUMBER:'||P_STREET_NUMBER);
1518: hr_utility.trace('P_STREET_NAME:'||P_STREET_NAME);
1519: hr_utility.trace('P_SUBURB_DISTRICT:'||P_SUBURB_DISTRICT);
1520: hr_utility.trace('P_TOWN_OR_CITY:'||P_TOWN_OR_CITY);
1521: hr_utility.trace('P_POSTAL_CODE:'||P_POSTAL_CODE);
1522: hr_utility.trace('P_SAME_AS_RES_ADD:'||P_SAME_AS_RES_ADD);
1523: hr_utility.trace('P_ADDRESS_LINE1:'||P_ADDRESS_LINE1);
1524: hr_utility.trace('P_ADDRESS_LINE2:'||P_ADDRESS_LINE2);
1525: hr_utility.trace('P_ADDRESS_LINE3:'||P_ADDRESS_LINE3);
1518: hr_utility.trace('P_STREET_NAME:'||P_STREET_NAME);
1519: hr_utility.trace('P_SUBURB_DISTRICT:'||P_SUBURB_DISTRICT);
1520: hr_utility.trace('P_TOWN_OR_CITY:'||P_TOWN_OR_CITY);
1521: hr_utility.trace('P_POSTAL_CODE:'||P_POSTAL_CODE);
1522: hr_utility.trace('P_SAME_AS_RES_ADD:'||P_SAME_AS_RES_ADD);
1523: hr_utility.trace('P_ADDRESS_LINE1:'||P_ADDRESS_LINE1);
1524: hr_utility.trace('P_ADDRESS_LINE2:'||P_ADDRESS_LINE2);
1525: hr_utility.trace('P_ADDRESS_LINE3:'||P_ADDRESS_LINE3);
1526: hr_utility.trace('P_NATURE:'||P_NATURE);
1519: hr_utility.trace('P_SUBURB_DISTRICT:'||P_SUBURB_DISTRICT);
1520: hr_utility.trace('P_TOWN_OR_CITY:'||P_TOWN_OR_CITY);
1521: hr_utility.trace('P_POSTAL_CODE:'||P_POSTAL_CODE);
1522: hr_utility.trace('P_SAME_AS_RES_ADD:'||P_SAME_AS_RES_ADD);
1523: hr_utility.trace('P_ADDRESS_LINE1:'||P_ADDRESS_LINE1);
1524: hr_utility.trace('P_ADDRESS_LINE2:'||P_ADDRESS_LINE2);
1525: hr_utility.trace('P_ADDRESS_LINE3:'||P_ADDRESS_LINE3);
1526: hr_utility.trace('P_NATURE:'||P_NATURE);
1527:
1520: hr_utility.trace('P_TOWN_OR_CITY:'||P_TOWN_OR_CITY);
1521: hr_utility.trace('P_POSTAL_CODE:'||P_POSTAL_CODE);
1522: hr_utility.trace('P_SAME_AS_RES_ADD:'||P_SAME_AS_RES_ADD);
1523: hr_utility.trace('P_ADDRESS_LINE1:'||P_ADDRESS_LINE1);
1524: hr_utility.trace('P_ADDRESS_LINE2:'||P_ADDRESS_LINE2);
1525: hr_utility.trace('P_ADDRESS_LINE3:'||P_ADDRESS_LINE3);
1526: hr_utility.trace('P_NATURE:'||P_NATURE);
1527:
1528: --Check which address is passed
1521: hr_utility.trace('P_POSTAL_CODE:'||P_POSTAL_CODE);
1522: hr_utility.trace('P_SAME_AS_RES_ADD:'||P_SAME_AS_RES_ADD);
1523: hr_utility.trace('P_ADDRESS_LINE1:'||P_ADDRESS_LINE1);
1524: hr_utility.trace('P_ADDRESS_LINE2:'||P_ADDRESS_LINE2);
1525: hr_utility.trace('P_ADDRESS_LINE3:'||P_ADDRESS_LINE3);
1526: hr_utility.trace('P_NATURE:'||P_NATURE);
1527:
1528: --Check which address is passed
1529: if P_STYLE = 'ZA_GRE' then
1522: hr_utility.trace('P_SAME_AS_RES_ADD:'||P_SAME_AS_RES_ADD);
1523: hr_utility.trace('P_ADDRESS_LINE1:'||P_ADDRESS_LINE1);
1524: hr_utility.trace('P_ADDRESS_LINE2:'||P_ADDRESS_LINE2);
1525: hr_utility.trace('P_ADDRESS_LINE3:'||P_ADDRESS_LINE3);
1526: hr_utility.trace('P_NATURE:'||P_NATURE);
1527:
1528: --Check which address is passed
1529: if P_STYLE = 'ZA_GRE' then
1530: l_location := 'ZA Tax File Information';
1540: l_warn_count := P_WARN_TXT.count;
1541:
1542: if P_STYLE in ('ZA_GRE','ZA_SARS') then
1543: -- Validate Unit Number
1544: hr_utility.set_location('Validating Unit Number',10);
1545: if validate_character_set(P_UNIT_NUMBER,'ALPHANUM') = false then
1546: fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
1547: fnd_message.set_token('FIELD', 'Unit Number in '||l_location);
1548: p_msg_txt(l_msg_count):=fnd_message.get('Y');
1549: l_msg_count := l_msg_count + 1;
1550: end if;
1551:
1552: --Validate Complex
1553: hr_utility.set_location('Validating Complex',20);
1554: if validate_character_set(P_COMPLEX,'FREETEXT') = false then
1555: fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
1556: fnd_message.set_token('FIELD', 'Complex in '||l_location);
1557: p_msg_txt(l_msg_count):=fnd_message.get('Y');
1558: l_msg_count := l_msg_count + 1;
1559: end if;
1560:
1561: --Validate Street Number
1562: hr_utility.set_location('Validating Street Number',25);
1563: if validate_character_set(P_STREET_NUMBER,'ALPHANUM') = false then
1564: fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
1565: fnd_message.set_token('FIELD', 'Street Number in '||l_location);
1566: p_msg_txt(l_msg_count):=fnd_message.get('Y');
1568: end if;
1569:
1570: --For residential, it is mandatory irrespective of nature
1571: --Validate Street or Name of Farm
1572: hr_utility.set_location('Validating Street or Name of Farm',30);
1573: if P_STREET_NAME is null and nvl(P_NATURE,'X') <> 'N' then
1574: fnd_message.set_name('PER', 'HR_ZA_ENTER_STREET_NAME_FARM');
1575: fnd_message.set_token('LOCATION', l_location);
1576: if P_STYLE = 'ZA_GRE' OR (P_STYLE='ZA_SARS' and P_TAX_YEAR <>2010) then
1588: l_msg_count := l_msg_count + 1;
1589: end if;
1590:
1591: --Validate Suburb or District
1592: hr_utility.set_location('Validating Suburb/District',35);
1593: if validate_character_set(P_SUBURB_DISTRICT,'FREETEXT') = false then
1594: fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
1595: fnd_message.set_token('FIELD', 'Suburb or District in '||l_location);
1596: p_msg_txt(l_msg_count):=fnd_message.get('Y');
1597: l_msg_count := l_msg_count + 1;
1598: end if;
1599:
1600: --Validate City or Town
1601: hr_utility.set_location('Validating City/Town',40);
1602: if validate_character_set(P_TOWN_OR_CITY,'FREETEXT') = false then
1603: fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
1604: fnd_message.set_token('FIELD', 'City or Town in '||l_location);
1605: p_msg_txt(l_msg_count):=fnd_message.get('Y');
1619: end if;
1620: end if ;
1621:
1622: --Validate Postal Code
1623: hr_utility.set_location('Validating Postal Code',50);
1624: if P_POSTAL_CODE is null and nvl(P_NATURE,'X') <> 'N' then
1625: fnd_message.set_name('PER', 'HR_ZA_NEW_ENTER_POSTAL_CODE');
1626: fnd_message.set_token('LOCATION', l_location);
1627: if P_STYLE = 'ZA_GRE' OR (P_STYLE='ZA_SARS' and P_TAX_YEAR <>2010) then
1633: l_warn_count := l_warn_count + 1;
1634: end if;
1635: else
1636: if P_STYLE='ZA_GRE' and length(P_POSTAL_CODE) <> 4 then
1637: hr_utility.set_location('Invalid postal code',50);
1638: fnd_message.set_name('PER', 'HR_ZA_INVALID_LENGTH');
1639: fnd_message.set_token('FIELD', 'Postal Code in '||l_location );
1640: fnd_message.set_token('LENGTH', 4);
1641: fnd_message.set_token('UNITS', 'digits');
1763: --
1764: EXCEPTION
1765: WHEN OTHERS THEN
1766: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
1767: hr_utility.set_location(sqlerrm(sqlcode),110);
1768: --
1769: END write_to_clob;
1770:
1771: procedure get_tyev_parameters
1805: l_proc varchar2(250):='PAY_ZA_EOY_VAL.GET_TYEV_PARAMETERS';
1806: begin
1807: -- Retrieve the Report Parameter Information
1808:
1809: hr_utility.set_location('Entering '||l_proc,10);
1810:
1811: --Legal Entity Name
1812: select name
1813: into l_legal_entity
1819: into l_cert_type
1820: from hr_lookups
1821: where lookup_type='ZA_TAX_CERTIFICATES' and lookup_code=P_CERT_TYPE;
1822:
1823: hr_utility.set_location('l_legal_entity: '||l_legal_entity,10);
1824: hr_utility.set_location('l_cert_type: '||l_cert_type,10);
1825:
1826:
1827: --Payroll Name
1820: from hr_lookups
1821: where lookup_type='ZA_TAX_CERTIFICATES' and lookup_code=P_CERT_TYPE;
1822:
1823: hr_utility.set_location('l_legal_entity: '||l_legal_entity,10);
1824: hr_utility.set_location('l_cert_type: '||l_cert_type,10);
1825:
1826:
1827: --Payroll Name
1828: if P_PAYROLL_ID is not null
1837: where ppf1.payroll_id=ppf.payroll_id
1838: and P_TAX_YEAR_END between ppf1.effective_start_date and ppf1.effective_end_date);
1839: end if;
1840:
1841: hr_utility.set_location('l_payroll_name: '||l_payroll_name,10);
1842:
1843: --Assignment Set Info
1844: if P_ASG_SET_ID is not null
1845: then
1865: if g_asg_set_where is null then
1866: g_asg_set_where := ' AND 1= 1 ';
1867: end if;
1868:
1869: hr_utility.set_location('Retrieved Assignment Set Info',10);
1870:
1871: --Employee Name Info
1872: if P_PERSON_ID is not null then
1873: select substr(per.full_name,1,40)
1888: else
1889: l_test_run:='N';
1890: end if;
1891:
1892: hr_utility.set_location('l_person_name: '||l_person_name,10);
1893: hr_utility.set_location('l_test_run: '||l_test_run,10);
1894:
1895:
1896: --SORT ORDER parameters
1889: l_test_run:='N';
1890: end if;
1891:
1892: hr_utility.set_location('l_person_name: '||l_person_name,10);
1893: hr_utility.set_location('l_test_run: '||l_test_run,10);
1894:
1895:
1896: --SORT ORDER parameters
1897: if p_sort_order1 is not null or p_sort_order2 is not null or p_sort_order3 is not null then
1896: --SORT ORDER parameters
1897: if p_sort_order1 is not null or p_sort_order2 is not null or p_sort_order3 is not null then
1898: g_sort_order_clause:=' order by ';
1899:
1900: hr_utility.set_location('Order by clause present',15);
1901: -- Append first sort order
1902: if p_sort_order1 = '3' then
1903: g_sort_order_clause := g_sort_order_clause || ' substr(per.full_name,1,40), ';
1904: l_sort_order1:='Employee Name';
1937: g_sort_order_clause:=g_sort_order_clause || ' ass.assignment_id';
1938:
1939: end if;
1940:
1941: hr_utility.set_location('Retrieved Sort Order Info',20);
1942:
1943: hr_utility.set_location('Populating XML Table',90);
1944:
1945: --Build XML for report parameters
1939: end if;
1940:
1941: hr_utility.set_location('Retrieved Sort Order Info',20);
1942:
1943: hr_utility.set_location('Populating XML Table',90);
1944:
1945: --Build XML for report parameters
1946: g_xml_element_table(g_xml_element_count).tagname := 'LEGAL_ENTITY_NAME';
1947: g_xml_element_table(g_xml_element_count).tagvalue := l_legal_entity;
1993: g_xml_element_table(g_xml_element_count).tagname := 'End Parameter Information';
1994: g_xml_element_table(g_xml_element_count).tagvalue := '_COMMENT_';
1995: g_xml_element_count := g_xml_element_count + 1;
1996:
1997: hr_utility.set_location('Leaving '||l_proc,20);
1998:
1999: end get_tyev_parameters;
2000:
2001: procedure get_employer_info
2044: l_msg_count number(5):=0;
2045: l_er_trade_name varchar2(2);
2046: begin
2047:
2048: -- hr_utility.trace_on(null,'ZATYEV');
2049: hr_utility.set_location('Entered '||l_proc,10);
2050:
2051: g_xml_element_table(g_xml_element_count).tagname := 'Employer Information';
2052: g_xml_element_table(g_xml_element_count).tagvalue := '_COMMENT_';
2045: l_er_trade_name varchar2(2);
2046: begin
2047:
2048: -- hr_utility.trace_on(null,'ZATYEV');
2049: hr_utility.set_location('Entered '||l_proc,10);
2050:
2051: g_xml_element_table(g_xml_element_count).tagname := 'Employer Information';
2052: g_xml_element_table(g_xml_element_count).tagvalue := '_COMMENT_';
2053: g_xml_element_count := g_xml_element_count + 1;
2051: g_xml_element_table(g_xml_element_count).tagname := 'Employer Information';
2052: g_xml_element_table(g_xml_element_count).tagvalue := '_COMMENT_';
2053: g_xml_element_count := g_xml_element_count + 1;
2054:
2055: hr_utility.set_location('Get ZA Tax Information',15);
2056: open csr_tax_info;
2057: fetch csr_tax_info into rec_tax_info;
2058: close csr_tax_info;
2059:
2056: open csr_tax_info;
2057: fetch csr_tax_info into rec_tax_info;
2058: close csr_tax_info;
2059:
2060: hr_utility.set_location('Get ZA GRE Tax File Creator Info',15);
2061: open csr_tax_file_creator_inf;
2062: fetch csr_tax_file_creator_inf into rec_tax_file_creator_inf;
2063: if csr_tax_file_creator_inf%rowcount = 0 then
2064: rec_tax_file_creator_inf.er_contact_person:=null;
2068: close csr_tax_file_creator_inf;
2069:
2070: l_er_msg_tab.delete;
2071:
2072: hr_utility.set_location('Start the Employer level validation',15);
2073:
2074: --Validate the Company Trading Name (Code 2010):
2075: hr_utility.set_location('Validate Company Trading Name - Code 2010',15);
2076: if rec_tax_info.er_trade_name is null then
2071:
2072: hr_utility.set_location('Start the Employer level validation',15);
2073:
2074: --Validate the Company Trading Name (Code 2010):
2075: hr_utility.set_location('Validate Company Trading Name - Code 2010',15);
2076: if rec_tax_info.er_trade_name is null then
2077: hr_utility.set_location('Company Trading Name is null',15);
2078: fnd_message.set_name('PAY', 'PY_ZA_ENTER_TRADING_NAME');
2079: l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2073:
2074: --Validate the Company Trading Name (Code 2010):
2075: hr_utility.set_location('Validate Company Trading Name - Code 2010',15);
2076: if rec_tax_info.er_trade_name is null then
2077: hr_utility.set_location('Company Trading Name is null',15);
2078: fnd_message.set_name('PAY', 'PY_ZA_ENTER_TRADING_NAME');
2079: l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2080: l_msg_count := l_msg_count + 1;
2081: l_er_info :='Y';
2081: l_er_info :='Y';
2082: /* Commented as per revision 8.0.0 of SARS PAYE Reconiliation 2010
2083: elsif length(translate(rec_tax_info.er_trade_name,'~\/*?:><|','~')) <> length(rec_tax_info.er_trade_name)
2084: OR instr(rec_tax_info.er_trade_name,'""')<>0 then
2085: hr_utility.set_location('Company Trading Name is invalid',15);
2086: fnd_message.set_name('PAY', 'PY_ZA_INVALID_EMPLOYER_NAME');
2087: l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2088: l_msg_count := l_msg_count + 1;
2089: l_er_info :='Y'; */
2087: l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2088: l_msg_count := l_msg_count + 1;
2089: l_er_info :='Y'; */
2090: elsif validate_character_set(rec_tax_info.er_trade_name,'FREETEXT') = FALSE then
2091: hr_utility.set_location('Company Trading Name is invalid',15);
2092: fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
2093: fnd_message.set_token('FIELD','Company Trading or Other Name');
2094: l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2095: l_msg_count := l_msg_count + 1;
2103: (l_er_trade_name between 'a' and 'z') or
2104: (l_er_trade_name between '0' and '9') then
2105: null;
2106: else
2107: hr_utility.set_location('Company Trading Name is invalid',15);
2108: fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
2109: fnd_message.set_token('FIELD','Company Trading or Other Name');
2110: l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2111: l_msg_count := l_msg_count + 1;
2113: end if;
2114:
2115: --Validate Trade classification (Code 2035)
2116: --Updated as per revision 8.0.0 of SARS PAYE Reconiliation 2010
2117: hr_utility.set_location('Validate Company Trade Classification - Code 2035',20);
2118: if rec_tax_info.er_trade_class is null and p_tax_year <> 2010 then
2119: hr_utility.set_location('Company Trade Classification is null',20);
2120: fnd_message.set_name('PAY', 'PY_ZA_ENTER_TRADE_CLASS');
2121: l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2115: --Validate Trade classification (Code 2035)
2116: --Updated as per revision 8.0.0 of SARS PAYE Reconiliation 2010
2117: hr_utility.set_location('Validate Company Trade Classification - Code 2035',20);
2118: if rec_tax_info.er_trade_class is null and p_tax_year <> 2010 then
2119: hr_utility.set_location('Company Trade Classification is null',20);
2120: fnd_message.set_name('PAY', 'PY_ZA_ENTER_TRADE_CLASS');
2121: l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2122: l_msg_count := l_msg_count + 1;
2123: l_er_info :='Y';
2121: l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2122: l_msg_count := l_msg_count + 1;
2123: l_er_info :='Y';
2124: else
2125: hr_utility.set_location('Company Trade Classification is not null',20);
2126: if validate_character_set(rec_tax_info.er_trade_class,'NUMERIC')=FALSE then
2127: fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
2128: fnd_message.set_token('FIELD', 'Trade Classification in ZA Tax Information');
2129: l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2141: end if;
2142: end if;
2143:
2144: -- Validate the PAYE Reference Number (Code 2020)
2145: hr_utility.set_location('Validating PAYE Ref Number - Code 2020',22);
2146: if rec_tax_info.paye_ref_num is null then
2147: hr_utility.set_location('PAYE Ref Number is null',22);
2148: fnd_message.set_name('PAY', 'PY_ZA_ENTER_TAX_REF_NO');
2149: l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2143:
2144: -- Validate the PAYE Reference Number (Code 2020)
2145: hr_utility.set_location('Validating PAYE Ref Number - Code 2020',22);
2146: if rec_tax_info.paye_ref_num is null then
2147: hr_utility.set_location('PAYE Ref Number is null',22);
2148: fnd_message.set_name('PAY', 'PY_ZA_ENTER_TAX_REF_NO');
2149: l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2150: l_msg_count := l_msg_count + 1;
2151: l_er_info :='Y';
2149: l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2150: l_msg_count := l_msg_count + 1;
2151: l_er_info :='Y';
2152: elsif substr(rec_tax_info.paye_ref_num,1,1) not in ('0','1','2','3','7','9') then
2153: hr_utility.set_location('PAYE Ref Number begins with invalid character',22);
2154: fnd_message.set_name('PAY', 'PY_ZA_INVALID_TAX_REF_NO');
2155: l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2156: l_msg_count := l_msg_count + 1;
2157: l_er_info :='Y';
2155: l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2156: l_msg_count := l_msg_count + 1;
2157: l_er_info :='Y';
2158: elsif rec_tax_info.paye_ref_num_mod = 0 then
2159: hr_utility.set_location('PAYE Ref Number fails modulus 10 test',22);
2160: fnd_message.set_name('PAY', 'PY_ZA_INVALID_TAX_REF_NO');
2161: l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2162: l_msg_count := l_msg_count + 1;
2163: l_er_info :='Y';
2163: l_er_info :='Y';
2164: end if;
2165:
2166: -- Validate the SDL Number (Code 2022)
2167: hr_utility.set_location('Validating SDL Number - Code 2022',25);
2168: if rec_tax_info.sdl_ref_num is not null then
2169: hr_utility.set_location('SDL Number is not null',25);
2170: if substr(rec_tax_info.sdl_ref_num,1,1) <> 'L' OR length(rec_tax_info.sdl_ref_num) <> 10 then
2171: hr_utility.set_location('SDL Number begins with invalid character',25);
2165:
2166: -- Validate the SDL Number (Code 2022)
2167: hr_utility.set_location('Validating SDL Number - Code 2022',25);
2168: if rec_tax_info.sdl_ref_num is not null then
2169: hr_utility.set_location('SDL Number is not null',25);
2170: if substr(rec_tax_info.sdl_ref_num,1,1) <> 'L' OR length(rec_tax_info.sdl_ref_num) <> 10 then
2171: hr_utility.set_location('SDL Number begins with invalid character',25);
2172: fnd_message.set_name('PAY', 'PY_ZA_INVALID_FIRST_CHAR');
2173: fnd_message.set_token('FIELD','SDL Number');
2167: hr_utility.set_location('Validating SDL Number - Code 2022',25);
2168: if rec_tax_info.sdl_ref_num is not null then
2169: hr_utility.set_location('SDL Number is not null',25);
2170: if substr(rec_tax_info.sdl_ref_num,1,1) <> 'L' OR length(rec_tax_info.sdl_ref_num) <> 10 then
2171: hr_utility.set_location('SDL Number begins with invalid character',25);
2172: fnd_message.set_name('PAY', 'PY_ZA_INVALID_FIRST_CHAR');
2173: fnd_message.set_token('FIELD','SDL Number');
2174: fnd_message.set_token('CHAR1','L');
2175: l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2177: l_er_info :='Y';
2178: end if;
2179: if substr(rec_tax_info.sdl_ref_num,2,9) <> substr(rec_tax_info.paye_ref_num,2,9)
2180: AND substr(rec_tax_info.paye_ref_num,1,1) = '7' then
2181: hr_utility.set_location('SDL Number doesnt match with PAYE Number',25);
2182: fnd_message.set_name('PAY', 'PY_ZA_INVALID_LAST_NINE_CHAR');
2183: fnd_message.set_token('FIELD','SDL Number');
2184: l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2185: l_msg_count := l_msg_count + 1;
2185: l_msg_count := l_msg_count + 1;
2186: l_er_info :='Y';
2187: end if;
2188: if pay_za_eoy_val.modulus_10_test(rec_tax_info.sdl_ref_num,'S') =0 then
2189: hr_utility.set_location('SDL Number fails modulus 10 test',25);
2190: fnd_message.set_name('PAY', 'PY_ZA_INVALID_SDL_NO');
2191: l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2192: l_msg_count := l_msg_count + 1;
2193: l_er_info :='Y';
2196:
2197: -- Validate the UIF Number (Code 2024)
2198: -- First replacing any char other than U and digits with null as earlier UIF Number segment
2199: -- allowed value greater than 10 digits.
2200: hr_utility.set_location('Validating UIF Number - Code 2024',27);
2201: rec_tax_info.uif_ref_num := translate(rec_tax_info.uif_ref_num,
2202: 'U0123456789ABCDEFGHIJKLMNOPQRSTVWXYZ- "\/?@&$!#+=;:,''().',
2203: 'U0123456789');
2204: if rec_tax_info.uif_ref_num is not null then
2201: rec_tax_info.uif_ref_num := translate(rec_tax_info.uif_ref_num,
2202: 'U0123456789ABCDEFGHIJKLMNOPQRSTVWXYZ- "\/?@&$!#+=;:,''().',
2203: 'U0123456789');
2204: if rec_tax_info.uif_ref_num is not null then
2205: hr_utility.set_location('UIF Number not null',27);
2206: if substr(rec_tax_info.uif_ref_num,1,1) <> 'U' OR length(rec_tax_info.uif_ref_num) <> 10 then
2207: hr_utility.set_location('UIF Number begins with invalid character',27);
2208: fnd_message.set_name('PAY', 'PY_ZA_INVALID_FIRST_CHAR');
2209: fnd_message.set_token('FIELD','UIF Number');
2203: 'U0123456789');
2204: if rec_tax_info.uif_ref_num is not null then
2205: hr_utility.set_location('UIF Number not null',27);
2206: if substr(rec_tax_info.uif_ref_num,1,1) <> 'U' OR length(rec_tax_info.uif_ref_num) <> 10 then
2207: hr_utility.set_location('UIF Number begins with invalid character',27);
2208: fnd_message.set_name('PAY', 'PY_ZA_INVALID_FIRST_CHAR');
2209: fnd_message.set_token('FIELD','UIF Number');
2210: fnd_message.set_token('CHAR1','U');
2211: l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2213: l_er_info :='Y';
2214: end if;
2215: if substr(rec_tax_info.uif_ref_num,2,9) <> substr(rec_tax_info.paye_ref_num,2,9)
2216: AND substr(rec_tax_info.paye_ref_num,1,1) = '7' then
2217: hr_utility.set_location('UIF Number doesnt match with PAYE Number',27);
2218: fnd_message.set_name('PAY', 'PY_ZA_INVALID_LAST_NINE_CHAR');
2219: fnd_message.set_token('FIELD','UIF Number');
2220: l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2221: l_msg_count := l_msg_count + 1;
2221: l_msg_count := l_msg_count + 1;
2222: l_er_info :='Y';
2223: end if;
2224: if pay_za_eoy_val.modulus_10_test(rec_tax_info.uif_ref_num,'U') =0 then
2225: hr_utility.set_location('UIF Number fails modulus 10 test',25);
2226: fnd_message.set_name('PAY', 'PY_ZA_INVALID_UIF_NO');
2227: l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2228: l_msg_count := l_msg_count + 1;
2229: l_er_info :='Y';
2231: end if;
2232:
2233: -- Validate GRE Tax File Creator Info
2234: -- Validate Contact Person Name (Code 2025)
2235: hr_utility.set_location('Validating Contact Person Name - Code 2025',30);
2236: if rec_tax_file_creator_inf.er_contact_person is null then
2237: hr_utility.set_location('Contact Person Name is null',30);
2238: fnd_message.set_name('PAY', 'PY_ZA_NEW_ENTER_CONTACT_NAME');
2239: l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2233: -- Validate GRE Tax File Creator Info
2234: -- Validate Contact Person Name (Code 2025)
2235: hr_utility.set_location('Validating Contact Person Name - Code 2025',30);
2236: if rec_tax_file_creator_inf.er_contact_person is null then
2237: hr_utility.set_location('Contact Person Name is null',30);
2238: fnd_message.set_name('PAY', 'PY_ZA_NEW_ENTER_CONTACT_NAME');
2239: l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2240: l_msg_count := l_msg_count + 1;
2241: l_er_info :='Y';
2239: l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2240: l_msg_count := l_msg_count + 1;
2241: l_er_info :='Y';
2242: elsif validate_character_set(rec_tax_file_creator_inf.er_contact_person,'ALPHA') = FALSE then
2243: hr_utility.set_location('Contact Person Name contains invalid characters',30);
2244: fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
2245: fnd_message.set_token('FIELD','Contact Person in ZA Tax File Information');
2246: l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2247: l_msg_count := l_msg_count + 1;
2248: l_er_info :='Y';
2249: end if;
2250:
2251: -- Validate Contact Person Number (Code 2026)
2252: hr_utility.set_location('Validating Contact Person Number - Code 2026',32);
2253: if rec_tax_file_creator_inf.er_contact_number is null then
2254: hr_utility.set_location('Contact Person Number is null',32);
2255: hr_utility.set_location('l_msg_count:'||l_msg_count,32);
2256: fnd_message.set_name('PAY', 'PY_ZA_NEW_ENTER_CONTACT_PH_NO');
2250:
2251: -- Validate Contact Person Number (Code 2026)
2252: hr_utility.set_location('Validating Contact Person Number - Code 2026',32);
2253: if rec_tax_file_creator_inf.er_contact_number is null then
2254: hr_utility.set_location('Contact Person Number is null',32);
2255: hr_utility.set_location('l_msg_count:'||l_msg_count,32);
2256: fnd_message.set_name('PAY', 'PY_ZA_NEW_ENTER_CONTACT_PH_NO');
2257: l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2258: l_msg_count := l_msg_count + 1;
2251: -- Validate Contact Person Number (Code 2026)
2252: hr_utility.set_location('Validating Contact Person Number - Code 2026',32);
2253: if rec_tax_file_creator_inf.er_contact_number is null then
2254: hr_utility.set_location('Contact Person Number is null',32);
2255: hr_utility.set_location('l_msg_count:'||l_msg_count,32);
2256: fnd_message.set_name('PAY', 'PY_ZA_NEW_ENTER_CONTACT_PH_NO');
2257: l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2258: l_msg_count := l_msg_count + 1;
2259: l_er_info :='Y';
2257: l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2258: l_msg_count := l_msg_count + 1;
2259: l_er_info :='Y';
2260: elsif length(rec_tax_file_creator_inf.er_contact_number) not between 9 and 11 then
2261: hr_utility.set_location('Contact Person Number length not between 9 and 11',32);
2262: fnd_message.set_name('PAY', 'PY_ZA_INVALID_PH_NO');
2263: fnd_message.set_token('FIELD','Contact Number in ZA Tax File Information');
2264: l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2265: l_msg_count := l_msg_count + 1;
2267:
2268: /* Added for ZA Aug reconciliation Phase II */
2269: elsif (instr(rec_tax_file_creator_inf.er_contact_number,'+') > 0 )
2270: or (instr(rec_tax_file_creator_inf.er_contact_number,' ') > 0 ) then
2271: hr_utility.set_location('Contact Person Number contains invalid characters',32);
2272: fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
2273: fnd_message.set_token('FIELD','Contact Number in ZA Tax File Information');
2274: l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2275: l_msg_count := l_msg_count + 1;
2277:
2278: end if;
2279:
2280: -- Validate Employer Email Address (Code 2027)
2281: hr_utility.set_location('Validating Employer Email Address - Code 2027',35);
2282: if rec_tax_file_creator_inf.er_email_address is not null then
2283: hr_utility.set_location('Email Address not null',35);
2284: if validate_email_id(rec_tax_file_creator_inf.er_email_address) = FALSE then
2285: hr_utility.set_location('Email Address doesnt contain @ or .',35);
2279:
2280: -- Validate Employer Email Address (Code 2027)
2281: hr_utility.set_location('Validating Employer Email Address - Code 2027',35);
2282: if rec_tax_file_creator_inf.er_email_address is not null then
2283: hr_utility.set_location('Email Address not null',35);
2284: if validate_email_id(rec_tax_file_creator_inf.er_email_address) = FALSE then
2285: hr_utility.set_location('Email Address doesnt contain @ or .',35);
2286: fnd_message.set_name('PER', 'HR_ZA_INVALID_CONTACT_EMAIL');
2287: fnd_message.set_token('CONTACT','Email Address in ZA Tax File Information');
2281: hr_utility.set_location('Validating Employer Email Address - Code 2027',35);
2282: if rec_tax_file_creator_inf.er_email_address is not null then
2283: hr_utility.set_location('Email Address not null',35);
2284: if validate_email_id(rec_tax_file_creator_inf.er_email_address) = FALSE then
2285: hr_utility.set_location('Email Address doesnt contain @ or .',35);
2286: fnd_message.set_name('PER', 'HR_ZA_INVALID_CONTACT_EMAIL');
2287: fnd_message.set_token('CONTACT','Email Address in ZA Tax File Information');
2288: l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2289: l_msg_count := l_msg_count + 1;
2289: l_msg_count := l_msg_count + 1;
2290: l_er_info :='Y';
2291: end if;
2292: if validate_character_set(rec_tax_file_creator_inf.er_email_address,'FREETEXT') = FALSE then
2293: hr_utility.set_location('Email Address contains invalid characters.',35);
2294: fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
2295: fnd_message.set_token('FIELD','Email Address in ZA Tax File Information');
2296: l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2297: l_msg_count := l_msg_count + 1;
2299: end if;
2300: end if;
2301: -- End Validate GRE Tax File Creator Info general info
2302:
2303: hr_utility.set_location('l_msg_count:'||l_msg_count,35);
2304: --Validate employer address
2305: validate_address( P_STYLE => 'ZA_GRE'
2306: ,P_TAX_YEAR => null
2307: ,P_UNIT_NUMBER => rec_tax_file_creator_inf.er_unit_num
2314: ,P_MSG_TXT => l_er_msg_tab
2315: ,P_WARN_TXT => l_er_warn_tab
2316: );
2317:
2318: hr_utility.set_location('l_msg_count:'||l_msg_count,36);
2319:
2320: hr_utility.set_location('Populating XML Table',90);
2321:
2322: l_msg_count:=l_er_msg_tab.count;
2316: );
2317:
2318: hr_utility.set_location('l_msg_count:'||l_msg_count,36);
2319:
2320: hr_utility.set_location('Populating XML Table',90);
2321:
2322: l_msg_count:=l_er_msg_tab.count;
2323: if l_er_info = 'Y' OR l_msg_count >0 then
2324: g_xml_element_table(g_xml_element_count).tagname := 'ER_INFO';
2342: g_xml_element_count := g_xml_element_count + 1;
2343: end loop;
2344: end if;
2345:
2346: hr_utility.set_location('l_er_msg_tab.first:'||l_er_msg_tab.first,90);
2347: hr_utility.set_location('l_er_msg_tab.last:'||l_er_msg_tab.last,90);
2348:
2349: g_xml_element_table(g_xml_element_count).tagname := 'End Employer Information';
2350: g_xml_element_table(g_xml_element_count).tagvalue := '_COMMENT_';
2343: end loop;
2344: end if;
2345:
2346: hr_utility.set_location('l_er_msg_tab.first:'||l_er_msg_tab.first,90);
2347: hr_utility.set_location('l_er_msg_tab.last:'||l_er_msg_tab.last,90);
2348:
2349: g_xml_element_table(g_xml_element_count).tagname := 'End Employer Information';
2350: g_xml_element_table(g_xml_element_count).tagvalue := '_COMMENT_';
2351: g_xml_element_count := g_xml_element_count + 1;
2351: g_xml_element_count := g_xml_element_count + 1;
2352:
2353: p_er_info:=l_er_info;
2354:
2355: hr_utility.set_location('Leaving '||l_proc,200);
2356:
2357: end get_employer_info;
2358:
2359: ----------------------------------------------------------------------------
2386: l_warn_count number(5):=0;
2387: l_proc varchar2(250):='PAY_ZA_EOY_VAL.VALIDATE_PHONES';
2388: begin
2389:
2390: hr_utility.set_location('Entering '||l_proc,10);
2391: l_msg_count :=p_ee_msg_tab.count;
2392: l_warn_count := p_ee_warn_tab.count;
2393:
2394: -- Validate Home Phone Number (Code 3135)
2392: l_warn_count := p_ee_warn_tab.count;
2393:
2394: -- Validate Home Phone Number (Code 3135)
2395:
2396: hr_utility.set_location('Validating Home Telephone Number -Code 3135',15);
2397: hr_utility.set_location('Retrieve Home Primary Number',15);
2398: open csr_phones('H1');
2399: fetch csr_phones into l_home_no;
2400: close csr_phones;
2393:
2394: -- Validate Home Phone Number (Code 3135)
2395:
2396: hr_utility.set_location('Validating Home Telephone Number -Code 3135',15);
2397: hr_utility.set_location('Retrieve Home Primary Number',15);
2398: open csr_phones('H1');
2399: fetch csr_phones into l_home_no;
2400: close csr_phones;
2401:
2399: fetch csr_phones into l_home_no;
2400: close csr_phones;
2401:
2402: if l_home_no is null then
2403: hr_utility.set_location('Retrieve Home Secondary Number',15);
2404: open csr_phones('H2');
2405: fetch csr_phones into l_home_no;
2406: close csr_phones;
2407:
2405: fetch csr_phones into l_home_no;
2406: close csr_phones;
2407:
2408: if l_home_no is null then
2409: hr_utility.set_location('Retrieve Home Tertiary Number',15);
2410: open csr_phones('H3');
2411: fetch csr_phones into l_home_no;
2412: close csr_phones;
2413: end if;
2428: end if;
2429: end if;
2430:
2431: -- Validate Business Phone Number (Code 3136)
2432: hr_utility.set_location('Validating Business Telephone Number -Code 3136',20);
2433: hr_utility.set_location('Retrieve Work Primary Number',20);
2434: open csr_phones('W1');
2435: fetch csr_phones into l_work_no;
2436: close csr_phones;
2429: end if;
2430:
2431: -- Validate Business Phone Number (Code 3136)
2432: hr_utility.set_location('Validating Business Telephone Number -Code 3136',20);
2433: hr_utility.set_location('Retrieve Work Primary Number',20);
2434: open csr_phones('W1');
2435: fetch csr_phones into l_work_no;
2436: close csr_phones;
2437:
2435: fetch csr_phones into l_work_no;
2436: close csr_phones;
2437:
2438: if l_work_no is null then
2439: hr_utility.set_location('Retrieve Work Secondary Number',20);
2440: open csr_phones('W2');
2441: fetch csr_phones into l_work_no;
2442: close csr_phones;
2443:
2441: fetch csr_phones into l_work_no;
2442: close csr_phones;
2443:
2444: if l_work_no is null then
2445: hr_utility.set_location('Retrieve Work Tertiary Number',20);
2446: open csr_phones('W3');
2447: fetch csr_phones into l_work_no;
2448: close csr_phones;
2449: end if;
2473: end if;
2474: end if;
2475:
2476: -- Validate Fax Number (Code 3137)
2477: hr_utility.set_location('Validating Fax Number -Code 3137',25);
2478: hr_utility.set_location('Retrieve Work Fax Number',25);
2479: open csr_phones('WF');
2480: fetch csr_phones into l_fax;
2481: close csr_phones;
2474: end if;
2475:
2476: -- Validate Fax Number (Code 3137)
2477: hr_utility.set_location('Validating Fax Number -Code 3137',25);
2478: hr_utility.set_location('Retrieve Work Fax Number',25);
2479: open csr_phones('WF');
2480: fetch csr_phones into l_fax;
2481: close csr_phones;
2482:
2480: fetch csr_phones into l_fax;
2481: close csr_phones;
2482:
2483: if l_fax is null then
2484: hr_utility.set_location('Retrieve Home Fax Number',25);
2485: open csr_phones('HF');
2486: fetch csr_phones into l_fax;
2487: close csr_phones;
2488: end if;
2502: end if;
2503: end if;
2504:
2505: --Validate cell number (code 3138)
2506: hr_utility.set_location('Validating Cell Number -Code 3138',30);
2507: open csr_phones('M');
2508: fetch csr_phones into l_cell_no;
2509: close csr_phones;
2510:
2525: l_msg_count := l_msg_count + 1;
2526: end if;
2527: end if;
2528:
2529: hr_utility.set_location('Leaving '||l_proc,200);
2530:
2531: end validate_phones;
2532:
2533: -- 11899934
2552: -- The employee number should be free text and shouldn't be
2553: -- greater than 25 characters.
2554: -- Validate Employee Number (Code 3160)
2555:
2556: hr_utility.set_location('Entering '||l_proc,10);
2557: hr_utility.set_location('p_empno'|| p_emp_num, 15);
2558: --
2559: if ((p_emp_num is null)
2560: or (length(p_emp_num) > 25 or per_za_user_hook_pkg.validate_charcter_set(p_emp_num, 'FREETEXT')= FALSE))
2553: -- greater than 25 characters.
2554: -- Validate Employee Number (Code 3160)
2555:
2556: hr_utility.set_location('Entering '||l_proc,10);
2557: hr_utility.set_location('p_empno'|| p_emp_num, 15);
2558: --
2559: if ((p_emp_num is null)
2560: or (length(p_emp_num) > 25 or per_za_user_hook_pkg.validate_charcter_set(p_emp_num, 'FREETEXT')= FALSE))
2561: then
2566: l_msg_count := l_msg_count + 1;
2567:
2568: end if;
2569:
2570: hr_utility.set_location('Leaving '||l_proc,20);
2571:
2572: end validate_emp_num;
2573:
2574: -- 11899934
2603: l_alpha varchar2(52) := '- ,''.';
2604: l_invalid_char varchar2(1) := '~';
2605: begin
2606:
2607: hr_utility.set_location('Entering '||l_proc,10);
2608:
2609: --Validate Nature of Person (Code 3020)
2610: hr_utility.set_location('Validation Nature of Person -Code 3020',10);
2611: if p_nature is null then
2606:
2607: hr_utility.set_location('Entering '||l_proc,10);
2608:
2609: --Validate Nature of Person (Code 3020)
2610: hr_utility.set_location('Validation Nature of Person -Code 3020',10);
2611: if p_nature is null then
2612: hr_utility.set_location('Nature of Person is null',10);
2613: fnd_message.set_name('PAY', 'PY_ZA_ENTER_NATURE_PERSON');
2614: p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2608:
2609: --Validate Nature of Person (Code 3020)
2610: hr_utility.set_location('Validation Nature of Person -Code 3020',10);
2611: if p_nature is null then
2612: hr_utility.set_location('Nature of Person is null',10);
2613: fnd_message.set_name('PAY', 'PY_ZA_ENTER_NATURE_PERSON');
2614: p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2615: l_msg_count := l_msg_count + 1;
2616: elsif p_nature = 'M' then
2613: fnd_message.set_name('PAY', 'PY_ZA_ENTER_NATURE_PERSON');
2614: p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2615: l_msg_count := l_msg_count + 1;
2616: elsif p_nature = 'M' then
2617: hr_utility.set_location('Nature of Person is M',10);
2618: fnd_message.set_name('PAY', 'PY_ZA_INVALID_NATURE_PERSON');
2619: fnd_message.set_token('NATURE', 'M');
2620: p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2621: l_msg_count := l_msg_count + 1;
2619: fnd_message.set_token('NATURE', 'M');
2620: p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2621: l_msg_count := l_msg_count + 1;
2622: elsif p_nature = 'K' then
2623: hr_utility.set_location('Nature of Person is K',10);
2624: fnd_message.set_name('PAY', 'PY_ZA_INVALID_NATURE_PERSON');
2625: fnd_message.set_token('NATURE', 'K');
2626: p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2627: l_msg_count := l_msg_count + 1;
2627: l_msg_count := l_msg_count + 1;
2628: end if;
2629:
2630: -- Validate Code Employee Surname/Trading Name (Code 3030)
2631: hr_utility.set_location('Validation EE Surname/Trading Name -Code 3030',15);
2632: if p_nature in ('D','E','F','G','H') then
2633: if p_trading_name is null then
2634: hr_utility.set_location('EE Surname is null',15);
2635: fnd_message.set_name('PAY', 'PY_ZA_ENTER_NAT_DEFGH_TRADE');
2630: -- Validate Code Employee Surname/Trading Name (Code 3030)
2631: hr_utility.set_location('Validation EE Surname/Trading Name -Code 3030',15);
2632: if p_nature in ('D','E','F','G','H') then
2633: if p_trading_name is null then
2634: hr_utility.set_location('EE Surname is null',15);
2635: fnd_message.set_name('PAY', 'PY_ZA_ENTER_NAT_DEFGH_TRADE');
2636: p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2637: l_msg_count := l_msg_count + 1;
2638: elsif validate_character_set(p_trading_name,'FREETEXT')= FALSE then
2635: fnd_message.set_name('PAY', 'PY_ZA_ENTER_NAT_DEFGH_TRADE');
2636: p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2637: l_msg_count := l_msg_count + 1;
2638: elsif validate_character_set(p_trading_name,'FREETEXT')= FALSE then
2639: hr_utility.set_location('EE Surname contains invalid characters',15);
2640: fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
2641: fnd_message.set_token('FIELD', 'Employee Trading Name');
2642: p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2643: l_msg_count := l_msg_count + 1;
2642: p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2643: l_msg_count := l_msg_count + 1;
2644: /* Commented as per revision 8.0.0 of SARS PAYE Reconiliation 2010
2645: elsif translate(p_trading_name,'~/\*?:><|','~') is not null OR instr(p_trading_name,'""')<>0 then
2646: hr_utility.set_location('EE Surname contains invalid characters',15);
2647: fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
2648: fnd_message.set_token('FIELD', 'Employee Trading Name');
2649: p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2650: l_msg_count := l_msg_count + 1; */
2652: elsif p_nature in ('A','B','C','N') then
2653: --Validate code 3030
2654: --Checking IF the Surname IS Null
2655: IF p_lname IS NULL then
2656: hr_utility.set_location('EE Surname is null',16);
2657: fnd_message.set_name('PAY', 'PY_ZA_ENTER_NAT_ABC_S_F_NAME');
2658: -- fnd_message.set_token('FIELD', 'Employee''s Surname');
2659: p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2660: l_msg_count := l_msg_count + 1;
2661: END if;
2662: /* commented as the numeric are allowed in 3030 as it is free text bug 9507670*/
2663: /* uncommented for Aug 2010 reconciliation phase II as numeric are not allowed in 3030 */
2664: if nvl(length(translate(p_lname,'~0123456789','~')),0) <> length(p_lname) then
2665: hr_utility.set_location('EE last name contains invalid characters',15);
2666: fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
2667: fnd_message.set_token('FIELD', 'Employee''s Surname');
2668: p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2669: l_msg_count := l_msg_count + 1;
2670: end if;
2671: end if;
2672:
2673: --Validate First Two Names ( Code 3040 )
2674: hr_utility.set_location('Validation First Two Names -Code 3040',20);
2675: if p_nature in ('A','B','C','N') then
2676: if p_fname is null and p_mname is null then
2677: fnd_message.set_name('PAY', 'PY_ZA_ENTER_NAT_ABCN_F_NAME');
2678: p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2699: end if;
2700:
2701:
2702: --Validate Identity Number (Code 3060)
2703: hr_utility.set_location('Validating Identity Number -Code 3060',22);
2704: if p_nature in ('A','C','N') then
2705: if p_ni is null and p_passport_no is null and p_nature <> 'N' then
2706: fnd_message.set_name('PAY', 'PY_ZA_ENTER_NAT_ACN_ID_PASSNO');
2707: p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2735: end if;
2736: end if;
2737:
2738: --Validate Passport Number (Code 3070)
2739: hr_utility.set_location('Validating Passport Number -Code 3070',22);
2740: if p_nature not in ('B','D','E','F','G','H') and p_passport_no is not null then
2741: -- bug 12914879
2742: -- if length(p_passport_no)< 7 then
2743: if length(p_passport_no)< 6 then
2778: end if;
2779:
2780: --Validate Country of Issue ( Code 3075 )
2781: --If Nature of Person is B, then country of Issue is archived as ZNC, hence validation not required.
2782: hr_utility.set_location('Validating Country of Issue -Code 3075',25);
2783: if p_country_passport is null and p_passport_no is not null then -- 9877034 fix removed B and N
2784: fnd_message.set_name('PAY', 'PY_ZA_ENTER_PASS_COUNTRY_ISSUE');
2785: --Made a warning as per revision 8.0.0 of SARS PAYE Reconiliation 2010 for only Tax Year 2010
2786: if p_tax_year = 2010 then
2807: end if;
2808: end if;
2809:
2810: --Validate code Income Tax Number (Code 3100)
2811: hr_utility.set_location('Validating Income Tax Number -Code 3100',27);
2812: if p_it_no_value is null and p_cert_type <> '2' and p_nature <> 'F' then
2813: fnd_message.set_name('PAY', 'PY_ZA_ENTER_IT_TAX_NO');
2814: --Made a warning as per revision 8.0.0 of SARS PAYE Reconiliation 2010 for only Tax Year 2010
2815: /* Bug no 9939519 changes specific to Aug 2010 changes
2867: */
2868:
2869: -- bug 11899934
2870:
2871: hr_utility.set_location('Leaving '||l_proc,200);
2872:
2873: end validate_nature;
2874:
2875: --------------------------------------------------------------------------
2887: l_proc varchar2(250):='PAY_ZA_EOY_VAL.VALIDATE_CONTACT_DETAILS';
2888: begin
2889: l_msg_count := p_ee_msg_tab.count;
2890:
2891: hr_utility.set_location('Entering '||l_proc,10);
2892:
2893: -- Validate email id (Code 3125)
2894: hr_utility.set_location('Validating Email Address -Code 3125',10);
2895: if p_email is not null then
2890:
2891: hr_utility.set_location('Entering '||l_proc,10);
2892:
2893: -- Validate email id (Code 3125)
2894: hr_utility.set_location('Validating Email Address -Code 3125',10);
2895: if p_email is not null then
2896: if validate_email_id(p_email) = FALSE then
2897: fnd_message.set_name('PER', 'HR_ZA_INVALID_CONTACT_EMAIL');
2898: fnd_message.set_token('CONTACT', 'Employee');
2915: end if;
2916: end if;
2917:
2918: --validate the phones i.e. codes 3135 to 3138
2919: hr_utility.set_location('Validating Phone Numbers',15);
2920: validate_phones(P_PERSON_ID, P_NATURE, P_TAX_YEAR_END, P_TAX_YEAR, P_EE_MSG_TAB, P_EE_WARN_TAB);
2921:
2922: hr_utility.set_location('Leaving '||l_proc,200);
2923: end validate_contact_details;
2918: --validate the phones i.e. codes 3135 to 3138
2919: hr_utility.set_location('Validating Phone Numbers',15);
2920: validate_phones(P_PERSON_ID, P_NATURE, P_TAX_YEAR_END, P_TAX_YEAR, P_EE_MSG_TAB, P_EE_WARN_TAB);
2921:
2922: hr_utility.set_location('Leaving '||l_proc,200);
2923: end validate_contact_details;
2924:
2925: ---------------------------------------------------------------------------
2926: --Validate Bank Details
2986: l_external_account_id number;
2987: l_proc varchar2(250):= 'PAY_ZA_OEY_VAL.VALIDATE_BANK_DETAILS';
2988: begin
2989:
2990: hr_utility.set_location('Entering '||l_proc,10);
2991: l_msg_count := P_EE_MSG_TAB.count;
2992: l_warn_count := P_EE_WARN_TAB.count;
2993:
2994: select count(*)
3064: end if;
3065: end if;
3066:
3067:
3068: hr_utility.set_location('Leaving '||l_proc,100);
3069:
3070: end validate_bank_details;
3071: ---------------------------------------------------------------------------
3072: --Get Employee Information
3185: l_period_recon_last_date date;
3186:
3187: l_sql varchar2(4000);
3188: begin
3189: -- hr_utility.trace_on(null,'ZATYEV');
3190: hr_utility.set_location('Entering '||l_proc,10);
3191:
3192: hr_utility.set_location('P_BUSINESS_GROUP_ID :'||P_BUSINESS_GROUP_ID,10);
3193: hr_utility.set_location('P_LEGAL_ENTITY :'||P_LEGAL_ENTITY,10);
3186:
3187: l_sql varchar2(4000);
3188: begin
3189: -- hr_utility.trace_on(null,'ZATYEV');
3190: hr_utility.set_location('Entering '||l_proc,10);
3191:
3192: hr_utility.set_location('P_BUSINESS_GROUP_ID :'||P_BUSINESS_GROUP_ID,10);
3193: hr_utility.set_location('P_LEGAL_ENTITY :'||P_LEGAL_ENTITY,10);
3194: hr_utility.set_location('P_PAYROLL_ID :'||P_PAYROLL_ID,10);
3188: begin
3189: -- hr_utility.trace_on(null,'ZATYEV');
3190: hr_utility.set_location('Entering '||l_proc,10);
3191:
3192: hr_utility.set_location('P_BUSINESS_GROUP_ID :'||P_BUSINESS_GROUP_ID,10);
3193: hr_utility.set_location('P_LEGAL_ENTITY :'||P_LEGAL_ENTITY,10);
3194: hr_utility.set_location('P_PAYROLL_ID :'||P_PAYROLL_ID,10);
3195: hr_utility.set_location('P_ASG_SET_ID :'||P_ASG_SET_ID,10);
3196: hr_utility.set_location('P_PERSON_ID :'||P_PERSON_ID,10);
3189: -- hr_utility.trace_on(null,'ZATYEV');
3190: hr_utility.set_location('Entering '||l_proc,10);
3191:
3192: hr_utility.set_location('P_BUSINESS_GROUP_ID :'||P_BUSINESS_GROUP_ID,10);
3193: hr_utility.set_location('P_LEGAL_ENTITY :'||P_LEGAL_ENTITY,10);
3194: hr_utility.set_location('P_PAYROLL_ID :'||P_PAYROLL_ID,10);
3195: hr_utility.set_location('P_ASG_SET_ID :'||P_ASG_SET_ID,10);
3196: hr_utility.set_location('P_PERSON_ID :'||P_PERSON_ID,10);
3197: hr_utility.set_location('P_TAX_YEAR :'||P_TAX_YEAR ,10);
3190: hr_utility.set_location('Entering '||l_proc,10);
3191:
3192: hr_utility.set_location('P_BUSINESS_GROUP_ID :'||P_BUSINESS_GROUP_ID,10);
3193: hr_utility.set_location('P_LEGAL_ENTITY :'||P_LEGAL_ENTITY,10);
3194: hr_utility.set_location('P_PAYROLL_ID :'||P_PAYROLL_ID,10);
3195: hr_utility.set_location('P_ASG_SET_ID :'||P_ASG_SET_ID,10);
3196: hr_utility.set_location('P_PERSON_ID :'||P_PERSON_ID,10);
3197: hr_utility.set_location('P_TAX_YEAR :'||P_TAX_YEAR ,10);
3198: hr_utility.set_location('P_TAX_YEAR_START:'||to_char(P_TAX_YEAR_START,'dd-mon-yyyy hh24:mi:ss'),10);
3191:
3192: hr_utility.set_location('P_BUSINESS_GROUP_ID :'||P_BUSINESS_GROUP_ID,10);
3193: hr_utility.set_location('P_LEGAL_ENTITY :'||P_LEGAL_ENTITY,10);
3194: hr_utility.set_location('P_PAYROLL_ID :'||P_PAYROLL_ID,10);
3195: hr_utility.set_location('P_ASG_SET_ID :'||P_ASG_SET_ID,10);
3196: hr_utility.set_location('P_PERSON_ID :'||P_PERSON_ID,10);
3197: hr_utility.set_location('P_TAX_YEAR :'||P_TAX_YEAR ,10);
3198: hr_utility.set_location('P_TAX_YEAR_START:'||to_char(P_TAX_YEAR_START,'dd-mon-yyyy hh24:mi:ss'),10);
3199: hr_utility.set_location('P_TAX_YEAR_END :'||to_char(P_TAX_YEAR_END,'dd-mon-yyyy hh24:mi:ss'),10);
3192: hr_utility.set_location('P_BUSINESS_GROUP_ID :'||P_BUSINESS_GROUP_ID,10);
3193: hr_utility.set_location('P_LEGAL_ENTITY :'||P_LEGAL_ENTITY,10);
3194: hr_utility.set_location('P_PAYROLL_ID :'||P_PAYROLL_ID,10);
3195: hr_utility.set_location('P_ASG_SET_ID :'||P_ASG_SET_ID,10);
3196: hr_utility.set_location('P_PERSON_ID :'||P_PERSON_ID,10);
3197: hr_utility.set_location('P_TAX_YEAR :'||P_TAX_YEAR ,10);
3198: hr_utility.set_location('P_TAX_YEAR_START:'||to_char(P_TAX_YEAR_START,'dd-mon-yyyy hh24:mi:ss'),10);
3199: hr_utility.set_location('P_TAX_YEAR_END :'||to_char(P_TAX_YEAR_END,'dd-mon-yyyy hh24:mi:ss'),10);
3200: hr_utility.set_location('P_CERT_TYPE :'||P_CERT_TYPE,10);
3193: hr_utility.set_location('P_LEGAL_ENTITY :'||P_LEGAL_ENTITY,10);
3194: hr_utility.set_location('P_PAYROLL_ID :'||P_PAYROLL_ID,10);
3195: hr_utility.set_location('P_ASG_SET_ID :'||P_ASG_SET_ID,10);
3196: hr_utility.set_location('P_PERSON_ID :'||P_PERSON_ID,10);
3197: hr_utility.set_location('P_TAX_YEAR :'||P_TAX_YEAR ,10);
3198: hr_utility.set_location('P_TAX_YEAR_START:'||to_char(P_TAX_YEAR_START,'dd-mon-yyyy hh24:mi:ss'),10);
3199: hr_utility.set_location('P_TAX_YEAR_END :'||to_char(P_TAX_YEAR_END,'dd-mon-yyyy hh24:mi:ss'),10);
3200: hr_utility.set_location('P_CERT_TYPE :'||P_CERT_TYPE,10);
3201: hr_utility.set_location('P_TAX_YEAR :'||P_TAX_YEAR,10);
3194: hr_utility.set_location('P_PAYROLL_ID :'||P_PAYROLL_ID,10);
3195: hr_utility.set_location('P_ASG_SET_ID :'||P_ASG_SET_ID,10);
3196: hr_utility.set_location('P_PERSON_ID :'||P_PERSON_ID,10);
3197: hr_utility.set_location('P_TAX_YEAR :'||P_TAX_YEAR ,10);
3198: hr_utility.set_location('P_TAX_YEAR_START:'||to_char(P_TAX_YEAR_START,'dd-mon-yyyy hh24:mi:ss'),10);
3199: hr_utility.set_location('P_TAX_YEAR_END :'||to_char(P_TAX_YEAR_END,'dd-mon-yyyy hh24:mi:ss'),10);
3200: hr_utility.set_location('P_CERT_TYPE :'||P_CERT_TYPE,10);
3201: hr_utility.set_location('P_TAX_YEAR :'||P_TAX_YEAR,10);
3202: hr_utility.trace('g_asg_set_where:'|| g_asg_set_where);
3195: hr_utility.set_location('P_ASG_SET_ID :'||P_ASG_SET_ID,10);
3196: hr_utility.set_location('P_PERSON_ID :'||P_PERSON_ID,10);
3197: hr_utility.set_location('P_TAX_YEAR :'||P_TAX_YEAR ,10);
3198: hr_utility.set_location('P_TAX_YEAR_START:'||to_char(P_TAX_YEAR_START,'dd-mon-yyyy hh24:mi:ss'),10);
3199: hr_utility.set_location('P_TAX_YEAR_END :'||to_char(P_TAX_YEAR_END,'dd-mon-yyyy hh24:mi:ss'),10);
3200: hr_utility.set_location('P_CERT_TYPE :'||P_CERT_TYPE,10);
3201: hr_utility.set_location('P_TAX_YEAR :'||P_TAX_YEAR,10);
3202: hr_utility.trace('g_asg_set_where:'|| g_asg_set_where);
3203: hr_utility.trace('g_sort_order_clause:'|| g_sort_order_clause);
3196: hr_utility.set_location('P_PERSON_ID :'||P_PERSON_ID,10);
3197: hr_utility.set_location('P_TAX_YEAR :'||P_TAX_YEAR ,10);
3198: hr_utility.set_location('P_TAX_YEAR_START:'||to_char(P_TAX_YEAR_START,'dd-mon-yyyy hh24:mi:ss'),10);
3199: hr_utility.set_location('P_TAX_YEAR_END :'||to_char(P_TAX_YEAR_END,'dd-mon-yyyy hh24:mi:ss'),10);
3200: hr_utility.set_location('P_CERT_TYPE :'||P_CERT_TYPE,10);
3201: hr_utility.set_location('P_TAX_YEAR :'||P_TAX_YEAR,10);
3202: hr_utility.trace('g_asg_set_where:'|| g_asg_set_where);
3203: hr_utility.trace('g_sort_order_clause:'|| g_sort_order_clause);
3204: l_miss_LE_tab.delete;
3197: hr_utility.set_location('P_TAX_YEAR :'||P_TAX_YEAR ,10);
3198: hr_utility.set_location('P_TAX_YEAR_START:'||to_char(P_TAX_YEAR_START,'dd-mon-yyyy hh24:mi:ss'),10);
3199: hr_utility.set_location('P_TAX_YEAR_END :'||to_char(P_TAX_YEAR_END,'dd-mon-yyyy hh24:mi:ss'),10);
3200: hr_utility.set_location('P_CERT_TYPE :'||P_CERT_TYPE,10);
3201: hr_utility.set_location('P_TAX_YEAR :'||P_TAX_YEAR,10);
3202: hr_utility.trace('g_asg_set_where:'|| g_asg_set_where);
3203: hr_utility.trace('g_sort_order_clause:'|| g_sort_order_clause);
3204: l_miss_LE_tab.delete;
3205:
3198: hr_utility.set_location('P_TAX_YEAR_START:'||to_char(P_TAX_YEAR_START,'dd-mon-yyyy hh24:mi:ss'),10);
3199: hr_utility.set_location('P_TAX_YEAR_END :'||to_char(P_TAX_YEAR_END,'dd-mon-yyyy hh24:mi:ss'),10);
3200: hr_utility.set_location('P_CERT_TYPE :'||P_CERT_TYPE,10);
3201: hr_utility.set_location('P_TAX_YEAR :'||P_TAX_YEAR,10);
3202: hr_utility.trace('g_asg_set_where:'|| g_asg_set_where);
3203: hr_utility.trace('g_sort_order_clause:'|| g_sort_order_clause);
3204: l_miss_LE_tab.delete;
3205:
3206: g_xml_element_table(g_xml_element_count).tagname := 'Employee Information';
3199: hr_utility.set_location('P_TAX_YEAR_END :'||to_char(P_TAX_YEAR_END,'dd-mon-yyyy hh24:mi:ss'),10);
3200: hr_utility.set_location('P_CERT_TYPE :'||P_CERT_TYPE,10);
3201: hr_utility.set_location('P_TAX_YEAR :'||P_TAX_YEAR,10);
3202: hr_utility.trace('g_asg_set_where:'|| g_asg_set_where);
3203: hr_utility.trace('g_sort_order_clause:'|| g_sort_order_clause);
3204: l_miss_LE_tab.delete;
3205:
3206: g_xml_element_table(g_xml_element_count).tagname := 'Employee Information';
3207: g_xml_element_table(g_xml_element_count).tagvalue := '_COMMENT_';
3294: exit when csr_employee%notfound;
3295: /*
3296: for rec_employee in csr_employee(p_cert_type)
3297: loop */
3298: hr_utility.set_location('Employee Number:'||l_emp_no, 10);
3299: hr_utility.set_location('Employee Name:'||l_emp_name,10);
3300: hr_utility.set_location('Legal Entity:'||l_legal_entity,10 );
3301:
3302: l_ee_msg_tab.delete;
3295: /*
3296: for rec_employee in csr_employee(p_cert_type)
3297: loop */
3298: hr_utility.set_location('Employee Number:'||l_emp_no, 10);
3299: hr_utility.set_location('Employee Name:'||l_emp_name,10);
3300: hr_utility.set_location('Legal Entity:'||l_legal_entity,10 );
3301:
3302: l_ee_msg_tab.delete;
3303: l_ee_warn_tab.delete;
3296: for rec_employee in csr_employee(p_cert_type)
3297: loop */
3298: hr_utility.set_location('Employee Number:'||l_emp_no, 10);
3299: hr_utility.set_location('Employee Name:'||l_emp_name,10);
3300: hr_utility.set_location('Legal Entity:'||l_legal_entity,10 );
3301:
3302: l_ee_msg_tab.delete;
3303: l_ee_warn_tab.delete;
3304:
3308: from per_all_assignments_f paaf
3309: where paaf.effective_start_date <= p_tax_year_end
3310: and paaf.assignment_id = l_assignment_id;
3311:
3312: hr_utility.set_location('l_effective_date:'||to_char(l_effective_date,'dd-mon-yyyy'),10);
3313:
3314: --Legal entity not provided. Hence populate the warnings table
3315: if l_legal_entity is null then
3316: hr_utility.set_location('Legal Entity not provided.',20);
3312: hr_utility.set_location('l_effective_date:'||to_char(l_effective_date,'dd-mon-yyyy'),10);
3313:
3314: --Legal entity not provided. Hence populate the warnings table
3315: if l_legal_entity is null then
3316: hr_utility.set_location('Legal Entity not provided.',20);
3317:
3318: --create a warnings table for missing legal entities and populate it.
3319: -- fnd_message.set_name('PAY', 'PY_ZA_ENTER_LEGAL_ENTITY');
3320: -- l_ee_warn_tab(l_warn_count):=fnd_message.get('Y');
3325: l_miss_LE_count := l_miss_LE_count + 1;
3326: else
3327:
3328: -- 11899934
3329: hr_utility.set_location('Validating Employee Number',19);
3330: -- Validate Employee Number
3331:
3332: validate_emp_num
3333: ( p_emp_num => l_emp_no
3336: );
3337: l_msg_count := l_ee_msg_tab.count;
3338: -- 11899934
3339:
3340: hr_utility.set_location('Validating Nature',20);
3341: -- Validate nature of person
3342:
3343: validate_nature( P_NATURE => l_nature
3344: ,P_FNAME => l_fname
3360: );
3361: l_msg_count := l_ee_msg_tab.count;
3362:
3363: --Validate contact details
3364: hr_utility.set_location('Validating Contact details',20);
3365: validate_contact_details( P_PERSON_ID => l_person_id
3366: ,P_NATURE => l_nature
3367: ,P_EMAIL => l_email
3368: ,P_TAX_YEAR_END=> l_effective_date
3372: );
3373:
3374:
3375: --Retrieve employee's business address
3376: hr_utility.set_location('Retrieve Business address',20);
3377: /*open csr_sars_address(l_person_id, l_effective_date,
3378: 'ZA_SARS', 'ZA_BUS');
3379: fetch csr_sars_address into rec_sars_bus_address;
3380: if csr_sars_address%notfound then
3467: ,P_WARN_TXT => l_ee_warn_tab
3468: );
3469:
3470: --Retrieve employee's residential address
3471: hr_utility.set_location('Retrieve Residential address',20);
3472: open csr_sars_address(l_person_id, l_effective_date,
3473: 'ZA_SARS', 'ZA_RES');
3474: fetch csr_sars_address into rec_sars_res_address;
3475: if csr_sars_address%notfound then
3496: ,P_MSG_TXT => l_ee_msg_tab
3497: ,P_WARN_TXT => l_ee_warn_tab
3498: );
3499:
3500: hr_utility.set_location('Retrieve Postal address',20);
3501: --Retrieve employee's postal address
3502: open csr_postal_address(l_person_id, l_effective_date);
3503: fetch csr_postal_address into rec_postal_address;
3504: if csr_postal_address%notfound then
3525: end if;
3526: close csr_postal_address;
3527:
3528: --Validate bank details
3529: hr_utility.set_location('Validating bank details',20);
3530: validate_bank_details(l_person_id,l_assignment_id,l_nature,
3531: l_payment_type,l_personal_pay_meth_id,
3532: l_effective_date, p_tax_year,l_ee_msg_tab,l_ee_warn_tab);
3533:
3534: end if;
3535:
3536: -- Populate XML
3537: -- If any employee errors present then show the section
3538: hr_utility.set_location('Populate XML Error Table',50);
3539:
3540: if l_ee_msg_tab.count > 0 and l_ee_info = 'N' then
3541: hr_utility.set_location('Employee level errors exists',20);
3542: l_ee_info := 'Y';
3537: -- If any employee errors present then show the section
3538: hr_utility.set_location('Populate XML Error Table',50);
3539:
3540: if l_ee_msg_tab.count > 0 and l_ee_info = 'N' then
3541: hr_utility.set_location('Employee level errors exists',20);
3542: l_ee_info := 'Y';
3543: g_xml_element_table(g_xml_element_count).tagname := 'EE_INFO';
3544: g_xml_element_table(g_xml_element_count).tagvalue := l_ee_info;
3545: g_xml_element_count := g_xml_element_count + 1;
3562: g_xml_element_table(g_xml_element_count).tagvalue := l_emp_name;
3563: g_xml_element_count := g_xml_element_count + 1;
3564:
3565:
3566: hr_utility.trace('l_ee_msg_tab.first:'||l_ee_msg_tab.first);
3567: hr_utility.trace('l_ee_msg_tab.last:'||l_ee_msg_tab.last);
3568:
3569: for i in l_ee_msg_tab.first .. l_ee_msg_tab.last
3570: loop
3563: g_xml_element_count := g_xml_element_count + 1;
3564:
3565:
3566: hr_utility.trace('l_ee_msg_tab.first:'||l_ee_msg_tab.first);
3567: hr_utility.trace('l_ee_msg_tab.last:'||l_ee_msg_tab.last);
3568:
3569: for i in l_ee_msg_tab.first .. l_ee_msg_tab.last
3570: loop
3571: g_xml_element_table(g_xml_element_count).tagname := 'EE_ERROR';
3587: g_xml_element_count := g_xml_element_count + 1;
3588: end if;
3589:
3590: -- Populate warnings
3591: hr_utility.set_location('Populate XML Warnings Table',70);
3592: if l_ee_warn_tab.count > 0 and l_warn_info = 'N' then
3593: l_warn_info := 'Y';
3594: g_xml_element_table(g_xml_element_count).tagname := 'WARN_INFO';
3595: g_xml_element_table(g_xml_element_count).tagvalue := l_warn_info;
3700: elsif l_ee_info='N' then
3701: P_EE_NDF_INFO:='Y';
3702: end if;
3703:
3704: hr_utility.set_location('l_warn_info:'||l_warn_info,100);
3705: hr_utility.set_location('l_ee_info:'||l_ee_info,100);
3706: hr_utility.set_location('P_EE_NDF_INFO:'||P_EE_NDF_INFO,100);
3707: hr_utility.set_location('Leaving '||l_proc,200);
3708: end get_employee_info;
3701: P_EE_NDF_INFO:='Y';
3702: end if;
3703:
3704: hr_utility.set_location('l_warn_info:'||l_warn_info,100);
3705: hr_utility.set_location('l_ee_info:'||l_ee_info,100);
3706: hr_utility.set_location('P_EE_NDF_INFO:'||P_EE_NDF_INFO,100);
3707: hr_utility.set_location('Leaving '||l_proc,200);
3708: end get_employee_info;
3709:
3702: end if;
3703:
3704: hr_utility.set_location('l_warn_info:'||l_warn_info,100);
3705: hr_utility.set_location('l_ee_info:'||l_ee_info,100);
3706: hr_utility.set_location('P_EE_NDF_INFO:'||P_EE_NDF_INFO,100);
3707: hr_utility.set_location('Leaving '||l_proc,200);
3708: end get_employee_info;
3709:
3710: procedure get_tyev_xml(
3703:
3704: hr_utility.set_location('l_warn_info:'||l_warn_info,100);
3705: hr_utility.set_location('l_ee_info:'||l_ee_info,100);
3706: hr_utility.set_location('P_EE_NDF_INFO:'||P_EE_NDF_INFO,100);
3707: hr_utility.set_location('Leaving '||l_proc,200);
3708: end get_employee_info;
3709:
3710: procedure get_tyev_xml(
3711: P_PROCESS_NAME IN varchar2,
3770: );
3771: */
3772:
3773: begin
3774: -- hr_utility.trace_on(null,'ZATYEV');
3775: hr_utility.set_location('Entering '||l_proc,10);
3776:
3777: g_xml_element_table.DELETE;
3778: g_xml_element_count:=0;
3771: */
3772:
3773: begin
3774: -- hr_utility.trace_on(null,'ZATYEV');
3775: hr_utility.set_location('Entering '||l_proc,10);
3776:
3777: g_xml_element_table.DELETE;
3778: g_xml_element_count:=0;
3779: ---
3797: l_tax_year_start:=fnd_date.canonical_to_date(substr(P_START_DATE,12,10));
3798: l_tax_year_end :=fnd_date.canonical_to_date(substr(P_END_DATE,10,10));
3799:
3800:
3801: hr_utility.set_location('Before retrieving parameter info',10);
3802:
3803: -- Retrieve parameter details
3804: get_tyev_parameters(P_LEGAL_ENTITY => P_LEGAL_ENTITY
3805: ,P_PAYROLL_ID => P_PAYROLL_ID
3814: ,P_SORT_ORDER2 => P_SORT_ORDER2
3815: ,P_SORT_ORDER3 => P_SORT_ORDER3
3816: );
3817:
3818: hr_utility.set_location('Before retrieving Employer info',10);
3819: -- Retrieve Employer specific errors/warnings
3820: get_employer_info(P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
3821: ,P_LEGAL_ENTITY => P_LEGAL_ENTITY
3822: ,P_TAX_YEAR => l_TAX_YEAR
3822: ,P_TAX_YEAR => l_TAX_YEAR
3823: ,P_ER_INFO => l_er_ndf_info
3824: );
3825:
3826: hr_utility.set_location('Before retrieving Employee info',10);
3827: -- Retrieve Employee specific errors/warnings
3828: get_employee_info(P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
3829: ,P_LEGAL_ENTITY => P_LEGAL_ENTITY
3830: ,P_PAYROLL_ID => P_PAYROLL_ID
3856: p_xml:=p_clob;
3857: */
3858: write_to_clob(p_xml);
3859:
3860: hr_utility.set_location('l_ee_ndf_info:'||l_ee_ndf_info,40);
3861: hr_utility.set_location('l_er_ndf_info:'||l_er_ndf_info,40);
3862: hr_utility.set_location('P_MONTHLY_RUN:'||P_MONTHLY_RUN,40);
3863: hr_utility.set_location('P_TEST_RUN:'||P_TEST_RUN,40);
3864:
3857: */
3858: write_to_clob(p_xml);
3859:
3860: hr_utility.set_location('l_ee_ndf_info:'||l_ee_ndf_info,40);
3861: hr_utility.set_location('l_er_ndf_info:'||l_er_ndf_info,40);
3862: hr_utility.set_location('P_MONTHLY_RUN:'||P_MONTHLY_RUN,40);
3863: hr_utility.set_location('P_TEST_RUN:'||P_TEST_RUN,40);
3864:
3865:
3858: write_to_clob(p_xml);
3859:
3860: hr_utility.set_location('l_ee_ndf_info:'||l_ee_ndf_info,40);
3861: hr_utility.set_location('l_er_ndf_info:'||l_er_ndf_info,40);
3862: hr_utility.set_location('P_MONTHLY_RUN:'||P_MONTHLY_RUN,40);
3863: hr_utility.set_location('P_TEST_RUN:'||P_TEST_RUN,40);
3864:
3865:
3866: if (P_TEST_RUN='Y' OR (l_ee_ndf_info is not null and l_er_ndf_info<>'Y'))
3859:
3860: hr_utility.set_location('l_ee_ndf_info:'||l_ee_ndf_info,40);
3861: hr_utility.set_location('l_er_ndf_info:'||l_er_ndf_info,40);
3862: hr_utility.set_location('P_MONTHLY_RUN:'||P_MONTHLY_RUN,40);
3863: hr_utility.set_location('P_TEST_RUN:'||P_TEST_RUN,40);
3864:
3865:
3866: if (P_TEST_RUN='Y' OR (l_ee_ndf_info is not null and l_er_ndf_info<>'Y'))
3867: then
4407: AND pai3.action_information5 is null;
4408:
4409: begin
4410: retcode := 0;
4411: -- hr_utility.trace_on(null,'ZATYEVL');
4412: FND_FILE.PUT_LINE(FND_FILE.LOG,'In validate_tye_data');
4413: FND_FILE.PUT_LINE(FND_FILE.LOG,'p_payroll_action_id :' || p_payroll_action_id);
4414: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inside validate_tye_data');
4415:
4420: where payroll_action_id = p_payroll_action_id;
4421:
4422: --For ITREG certificate dont do any validations
4423: if pay_za_irp5_archive_pkg.get_parameter('CERT_TYPE', l_leg_param)=2 then
4424: hr_utility.set_location('ITREG Certificate',10);
4425: return;
4426: end if;
4427:
4428: /*Loop through all Assignments for given payroll_action_id*/
4437: LOOP
4438: l_empno := emp_num.empno ;
4439: l_assgno:= emp_num.assgno;
4440: END LOOP;
4441: hr_utility.set_location('Processing Employee:'||l_empno,15);
4442:
4443: --Fetch the temporary number generated for main certificate
4444: hr_utility.set_location('Fetching Main certificate Number',15);
4445: open csr_cert_num(l_ass_act_id);
4440: END LOOP;
4441: hr_utility.set_location('Processing Employee:'||l_empno,15);
4442:
4443: --Fetch the temporary number generated for main certificate
4444: hr_utility.set_location('Fetching Main certificate Number',15);
4445: open csr_cert_num(l_ass_act_id);
4446: fetch csr_cert_num into l_main_cert_num,l_cert_type;
4447: close csr_cert_num;
4448:
4445: open csr_cert_num(l_ass_act_id);
4446: fetch csr_cert_num into l_main_cert_num,l_cert_type;
4447: close csr_cert_num;
4448:
4449: hr_utility.set_location('l_main_cert_num:'||l_main_cert_num,15);
4450: hr_utility.set_location('l_cert_type: '||l_cert_type,15);
4451:
4452: -- Check whether the SITE PAYE split exists
4453: hr_utility.set_location('Fetching Tax Details',15);
4446: fetch csr_cert_num into l_main_cert_num,l_cert_type;
4447: close csr_cert_num;
4448:
4449: hr_utility.set_location('l_main_cert_num:'||l_main_cert_num,15);
4450: hr_utility.set_location('l_cert_type: '||l_cert_type,15);
4451:
4452: -- Check whether the SITE PAYE split exists
4453: hr_utility.set_location('Fetching Tax Details',15);
4454:
4449: hr_utility.set_location('l_main_cert_num:'||l_main_cert_num,15);
4450: hr_utility.set_location('l_cert_type: '||l_cert_type,15);
4451:
4452: -- Check whether the SITE PAYE split exists
4453: hr_utility.set_location('Fetching Tax Details',15);
4454:
4455:
4456: open tax_info(l_ass_act_id,l_main_cert_num);
4457: fetch tax_info into rec_info;
4455:
4456: open tax_info(l_ass_act_id,l_main_cert_num);
4457: fetch tax_info into rec_info;
4458: close tax_info;
4459: hr_utility.set_location('Fetched Tax Details',15);
4460:
4461: /* The check should be for TYE or if employee is terminated
4462: For normal employee in Mid TYE this check should not be performed*/
4463: if p_period_recon = '02' and rec_info.tax <>0 and (rec_info.site=0 and rec_info.paye=0) then
4476: end if;
4477:
4478:
4479: --Validate negative income in the main certificate
4480: hr_utility.set_location('Validating negative income for main certificate',20);
4481: for rec_neg_income in csr_neg_income(l_ass_act_id, l_main_cert_num)
4482: loop
4483: if l_count <>1 then
4484: l_count:=1;
4494: FND_FILE.PUT_LINE(FND_FILE.LOG, l_msgtext);
4495: end loop;
4496:
4497: --Validate negative lump sums in the main certificate
4498: hr_utility.set_location('Validating negative income for lump sum',20);
4499: for rec_neg_lmpsm in csr_neg_lmpsm(l_ass_act_id, l_main_cert_num)
4500: loop
4501: if l_count <>1 then
4502: l_count:=1;
4547: end if;
4548: end loop;
4549:
4550: --Validate negative lump sums in the lump sum certificate
4551: hr_utility.set_location('Validating negative income for lump sum',20);
4552: for rec_neg_lmpsm in csr_neg_lmpsm2(l_ass_act_id, l_main_cert_num)
4553: loop
4554: if l_count <>1 then
4555: l_count:=1;
4647:
4648: end loop;
4649:
4650: --Validate negative deductions in the main certificate
4651: hr_utility.set_location('Validating negative deductions in main certificate',20);
4652: for rec_neg_deduct in csr_neg_deduct(l_ass_act_id, l_main_cert_num)
4653: loop
4654: if l_count <>1 then
4655: l_count:=1;
4671:
4672: loop
4673:
4674: -- Validate Non-Taxable Income is not negative.
4675: hr_utility.set_location('Validating Non-Taxable Income',20);
4676:
4677: if rec_fetch_rfi_nrfi.Non_Taxable_Income < 0 then
4678:
4679: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Non-Taxable Income is negative for ');
4682: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Non-Taxable Income : ' || rec_fetch_rfi_nrfi.Non_Taxable_Income);
4683:
4684: end if;
4685:
4686: hr_utility.set_location('Validated Non-Taxable Income',20);
4687:
4688:
4689: -- Validate Gross Retirement Funding Income is not negative
4690: hr_utility.set_location('Validating Gross Retirement Funding Income',20);
4686: hr_utility.set_location('Validated Non-Taxable Income',20);
4687:
4688:
4689: -- Validate Gross Retirement Funding Income is not negative
4690: hr_utility.set_location('Validating Gross Retirement Funding Income',20);
4691:
4692:
4693: if rec_fetch_rfi_nrfi.Gross_Retire_Fund_Income < 0 then
4694:
4699:
4700: end if;
4701:
4702:
4703: hr_utility.set_location('Validated Gross Retirement Funding Income',20);
4704:
4705:
4706: -- Validate Gross Non-Retirement Funding Income is not negative
4707: hr_utility.set_location('Validating Gross Non-Retirement Funding Income ',20);
4703: hr_utility.set_location('Validated Gross Retirement Funding Income',20);
4704:
4705:
4706: -- Validate Gross Non-Retirement Funding Income is not negative
4707: hr_utility.set_location('Validating Gross Non-Retirement Funding Income ',20);
4708:
4709:
4710: if rec_fetch_rfi_nrfi.Gross_Non_Retire_Fund_In < 0 then
4711:
4715: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Gross Non-Retirement Funding Income : ' || rec_fetch_rfi_nrfi.Gross_Non_Retire_Fund_In);
4716:
4717: end if ;
4718:
4719: hr_utility.set_location('Validated Gross Non-Retirement Funding Income ',20);
4720:
4721: end loop ;
4722:
4723: -- Added for bug#10287216
4722:
4723: -- Added for bug#10287216
4724:
4725: --Employee not on package structure but has PKG balances
4726: hr_utility.set_location('Check whether Emp on PKG Structure',25);
4727: for rec_pkg_balances in fetch_pkg_balances(l_ass_act_id, l_main_cert_num)
4728: loop
4729: if l_count <>1 then
4730: l_count:=1;
4758: where assignment_id = rec_asgn.assignment_id
4759: and action_sequence = l_run_action_seq;
4760:
4761:
4762: hr_utility.set_location('Employee not on Package structure but has PKG balances',25);
4763: FOR pkg_ele IN fetch_pkg_ele(l_run_ass_act_id)
4764: LOOP
4765: fnd_message.set_name('PAY', 'PY_ZA_NEW_ELE_FEED_PKG_BAL');
4766: fnd_message.set_token('ELEMENTname',pkg_ele.element_name);
4769: END LOOP;
4770: end loop; --end loop for fetch_pkg_balances
4771:
4772: -- Code 4115 must be present only if code 3901, 3915, 3920, 3921 are present. (added 3901 for bug 11899934)
4773: hr_utility.set_location('Tax on Retirement Fund check',30);
4774: /*for rec_tax_ret_fund_ls in chk_tax_ret_fund_ls(l_ass_act_id)
4775: loop
4776: open chk_ret_fund_ls(l_ass_act_id, rec_tax_ret_fund_ls.temp_cert_num);
4777: fetch chk_ret_fund_ls into rec_ret_fund_ls;
4806: l_msgtext :='Code 4115 is mandatory if there is a value for 3901, 3915, 3920, 3921 and 3922 AND 4150 does not have a value';
4807: FND_FILE.PUT_LINE (FND_FILE.LOG,l_msgtext);
4808: end if;
4809: close csr_4115_is_mandatory;
4810: hr_utility.set_location('End Tax on Retirement Fund check',35);
4811:
4812:
4813: --Delete the cross validation table
4814: cross_val_t.delete;
4820: cross_val_t(4024):=0;
4821: cross_val_t(4474):=0;
4822: cross_val_t(4493):=0;
4823:
4824: hr_utility.set_location('Before populating cross validation table',40);
4825: --Populate the cross validation table
4826: FOR rec_cv_inc_codes IN cross_val_inc_codes(l_ass_act_id , l_main_cert_num)
4827: LOOP
4828: cross_val_t(rec_cv_inc_codes.code)
4828: cross_val_t(rec_cv_inc_codes.code)
4829: := rec_cv_inc_codes.value;
4830: END LOOP;
4831:
4832: hr_utility.set_location('Intermediate cross validation table',40);
4833:
4834: FOR rec_cv_ded_codes IN cross_val_ded_codes(l_ass_act_id, l_main_cert_num)
4835: LOOP
4836: cross_val_t(rec_cv_ded_codes.code)
4836: cross_val_t(rec_cv_ded_codes.code)
4837: := rec_cv_ded_codes.value;
4838: END LOOP;
4839:
4840: hr_utility.set_location('After cross validation table',40);
4841:
4842: -- Code 3810 must be less than code 4474
4843: /* Bug 9939519 added below condition as per the new BRS version 2.0 published on 14-Jul-2010 */
4844:
4873: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Code 3810 / 3860 must be equal to the Code 4474.');
4874: end if;
4875: end if;
4876:
4877: hr_utility.set_location('check whether 3813 greater than 4024',45);
4878: --Code 3813/3863 must be greater than or equal to the value of code 4024
4879: if ((case when cross_val_t(3813)=0 then cross_val_t(3863) else cross_val_t(3813) end) < cross_val_t(4024)) then
4880: if l_count <>1 then
4881: l_count:=1;
4887: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Codes 3813 and 3863 must not be less than Code 4024.');
4888: end if;
4889:
4890: --Code 3810/3860 and 4474 is not allowed if code 4493 is specified
4891: hr_utility.set_location('check whether 3810 OR 4474 present if 4493 specified',45);
4892: if cross_val_t(4493) <> 0 then
4893: if cross_val_t(3810) <>0 OR cross_val_t(3860) <>0 OR cross_val_t(4474) <>0 then
4894: if l_count <>1 then
4895: l_count:=1;
4903: end if;
4904:
4905: /* --Already handled in message Code 3813/3863 must be greater than or equal to the value of code 4024
4906: --Code 4024 cannot be greater than the sum of the values for codes 3813 and 3863
4907: hr_utility.set_location('Check whether 4024 greater than 3813/3863',45);
4908: if cross_val_t(4024) > cross_val_t(3813) OR cross_val_t(4024) > cross_val_t(3863) then
4909: if l_count <>1 then
4910: l_count:=1;
4911: FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
4917: end if;
4918: */
4919:
4920: --Code 4474 is mandatory if an amount is specified for code 3810/3860
4921: hr_utility.set_location('4474 mandatory for 3810',45);
4922: if (cross_val_t(3810) <> 0 OR cross_val_t(3860)<>0) AND cross_val_t(4474)=0 then
4923: if l_count <>1 then
4924: l_count:=1;
4925: FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
4930: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Code 4474 must be greater than zero if Code 3810 or Code 3860 has a value.');
4931: end if;
4932:
4933: --Code 4005 is mandatory if an amount is specified for code 3810/3860
4934: hr_utility.set_location('4005 mandatory for 3810',45);
4935: if (cross_val_t(3810) <> 0 OR cross_val_t(3860)<>0) AND cross_val_t(4005)=0 then
4936: if l_count <>1 then
4937: l_count:=1;
4938: FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
4944: end if;
4945:
4946:
4947: -- Check that number of deduction codes must not exceed 7.
4948: hr_utility.set_location('Count deduction codes',45);
4949: open csr_num_ded_codes(l_ass_act_id , l_main_cert_num);
4950: fetch csr_num_ded_codes into l_num_ded_codes;
4951: close csr_num_ded_codes;
4952:
4966: FND_FILE.PUT_LINE (FND_FILE.LOG,l_msgtext);
4967: end if;
4968:
4969: -- Check that number of income codes must not exceed 13.
4970: hr_utility.set_location('Count income codes',50);
4971: open csr_num_inc_codes(l_ass_act_id , l_main_cert_num);
4972: fetch csr_num_inc_codes into l_num_inc_temp;
4973: close csr_num_inc_codes;
4974:
4995: end if;
4996:
4997: --If the employer is not register for PAYE (Paye Reference number doesnot start with 7)
4998: --but any employee has code 4101,4102,4115 then it is invalid
4999: hr_utility.set_location('Check PAYE Ref Num',60);
5000: open csr_paye_ref_num;
5001: fetch csr_paye_ref_num into l_paye_ref_num;
5002: if csr_paye_ref_num%notfound then
5003: l_paye_ref_num:=7;
5003: l_paye_ref_num:=7;
5004: end if;
5005: close csr_paye_ref_num;
5006:
5007: hr_utility.set_location('Retrieved PAYE Ref Num',60);
5008: if substr(l_paye_ref_num,1,1) <> 7 then
5009: open tax_codes(l_ass_act_id);
5010: fetch tax_codes into l_tax_code_ind;
5011: close tax_codes;
5092: END LOOP; --end of assignment loop
5093:
5094: FND_FILE.PUT_LINE(FND_FILE.LOG,'End of log file');
5095: FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
5096: -- hr_utility.trace_off;
5097: EXCEPTION
5098: WHEN OTHERS then
5099: errbuf := substr(SQLERRM,1,255);
5100: retcode := sqlcode;