448: lv_meaning := sv_lkup(i).dbi_value;
449: exit;
450: end if;
451: end loop;
452: hr_utility.set_location(fp_lookup_code||' '||lv_meaning, 1111 );
453: return(lv_meaning);
454: end get_lookup_meaning;
455:
456: /* The function print_spaces returns spaces to print spaces in the file */
909:
910: while sv_neg_bal.count > 0
911: loop
912:
913: hr_utility.set_location(to_char(sv_neg_bal.count),888);
914: i := 0;
915: l := 0;
916: l_juris_cd := sv_neg_bal(1).dbi_name;
917:
918: for k in sv_neg_bal.first..sv_neg_bal.last
919: loop
920:
921: l_juris_cd1 := sv_neg_bal(k).dbi_name;
922: hr_utility.set_location('K = '||to_char(k),888);
923: hr_utility.set_location('JURI = *'||sv_neg_bal(k).dbi_name||'*',888);
924:
925: if l_juris_cd1 = l_juris_cd then
926: i := i + 1;
919: loop
920:
921: l_juris_cd1 := sv_neg_bal(k).dbi_name;
922: hr_utility.set_location('K = '||to_char(k),888);
923: hr_utility.set_location('JURI = *'||sv_neg_bal(k).dbi_name||'*',888);
924:
925: if l_juris_cd1 = l_juris_cd then
926: i := i + 1;
927:
924:
925: if l_juris_cd1 = l_juris_cd then
926: i := i + 1;
927:
928: hr_utility.set_location('I = '||to_char(i),888);
929:
930: if mod(i,5) = 1 then
931: if i = 1 then
932: lv_format := '
959: sv_neg_bal(k).dbi_value||'
999: else
1000:
1001: for i in 1..sv_nb loop
1002:
1003: hr_utility.set_location(to_char(mod(i,5)),888);
1004:
1005: if mod(i,5) = 1 then
1006: if i = 1 then
1007: lv_format := '
1051: end if;
1052:
1053: if ( ( sv_c + sv_m ) > 0 ) then
1054:
1055: hr_utility.set_location('5',888);
1056: lv_format := '
1055: hr_utility.set_location('5',888);
1056: lv_format := '
1062:
1063: if ( sv_m > 0 ) then
1064: for i in 1..sv_m
1065: loop
1066: hr_utility.set_location('7',888);
1067: lv_format := '
1069: format_data(lv_format);
1070: end loop;
1071: end if;
1072: if sv_c > 0 then
1073: hr_utility.set_location('8',888);
1074: lv_format := '
1086: if ( length(lv_req_flds) < 135 ) then
1087: lv_req_flds := lv_req_flds ||
1088: print_spaces( 135 - length(lv_req_flds) );
1089: end if;
1090: hr_utility.set_location('9',888);
1091: lv_format := '
1092: lv_format := lv_format||' '||lv_req_flds||'';
1093: format_data(lv_format);
1094: end if;
1095: end if;
1096: hr_utility.set_location('10',888);
1097: lv_format := '
1271: if sv_report_type in ('RL1','CAEOY_RL1_AMEND_PP') then
1272:
1273: for i in sv_dbi.first..sv_dbi.last
1274: loop
1275: hr_utility.set_location(to_char(i)||'. '||sv_dbi(i).dbi_name||' '||
1276: sv_dbi(i).dbi_value||' '||sv_dbi(i).dbi_short_name, 999 );
1277:
1278: if sv_dbi(i).dbi_name = 'CAEOY_PERSON_ID' then
1279: lv_person_id := to_number(sv_dbi(i).dbi_value);
1374: end if;
1375:
1376: if sv_dbi(i).dbi_name = 'CAEOY_GROSS_EARNINGS_PER_JD_YTD' then
1377: sv_total_earnings := nvl(sv_total_earnings,0) + fnd_number.canonical_to_number(sv_dbi(i).dbi_value);
1378: hr_utility.trace('sv_total_earnings ='|| sv_total_earnings);
1379: end if;
1380:
1381: if sv_dbi(i).dbi_name = 'CAEOY_QPP_EE_TAXABLE_PER_JD_YTD' then
1382: sv_pensionable_earnings := nvl(sv_pensionable_earnings,0) +fnd_number.canonical_to_number(sv_dbi(i).dbi_value);
1848: begin
1849: if sv_report_type in ('RL1','CAEOY_RL1_AMEND_PP') then
1850: for i in sv_dbi.first..sv_dbi.last
1851: loop
1852: hr_utility.set_location(sv_dbi(i).dbi_short_name, 601);
1853: if sv_trans_y_n = 'Y' then
1854: if sv_dbi(i).dbi_name = 'CAEOY_RL1_TRANSMITTER_NUMBER' then
1855: if sv_dbi(i).dbi_value is null then
1856: sv_c := sv_c + 1;
1854: if sv_dbi(i).dbi_name = 'CAEOY_RL1_TRANSMITTER_NUMBER' then
1855: if sv_dbi(i).dbi_value is null then
1856: sv_c := sv_c + 1;
1857: sv_col(sv_c) := sv_dbi(i).dbi_short_name;
1858: hr_utility.set_location('Required column '||sv_col(sv_c), 610);
1859: else
1860: if ( ( substr(sv_dbi(i).dbi_value,1,2) <> 'NP' ) or
1861: ( length(sv_dbi(i).dbi_value) <> 8 ) or
1862: ( not ( substr(sv_dbi(i).dbi_value,3) >= '000000' ) and
1863: ( substr(sv_dbi(i).dbi_value,3) <= '999999' ) ) )
1864: then
1865: sv_m := sv_m + 1;
1866: sv_msg(sv_m) := get_lookup_meaning('R_INVALID_TRANS_NO');
1867: hr_utility.set_location(sv_msg(sv_m)|| ' '||sv_dbi(i).dbi_value, 611);
1868: end if;
1869: end if;
1870: end if;
1871: if sv_dbi(i).dbi_name = 'CAEOY_RL1_TRANSMITTER_NAME' and
1871: if sv_dbi(i).dbi_name = 'CAEOY_RL1_TRANSMITTER_NAME' and
1872: sv_dbi(i).dbi_value is null then
1873: sv_c := sv_c + 1;
1874: sv_col(sv_c) := sv_dbi(i).dbi_short_name;
1875: hr_utility.set_location('Required column '||sv_col(sv_c), 612);
1876: end if;
1877: if sv_dbi(i).dbi_name = 'CAEOY_RL1_TRANSMITTER_CITY' and
1878: sv_dbi(i).dbi_value is null then
1879: sv_c := sv_c + 1;
1877: if sv_dbi(i).dbi_name = 'CAEOY_RL1_TRANSMITTER_CITY' and
1878: sv_dbi(i).dbi_value is null then
1879: sv_c := sv_c + 1;
1880: sv_col(sv_c) := sv_dbi(i).dbi_short_name;
1881: hr_utility.set_location('Required column '||sv_col(sv_c), 613);
1882: end if;
1883: if sv_dbi(i).dbi_name = 'CAEOY_RL1_TRANSMITTER_PROVINCE' and
1884: sv_dbi(i).dbi_value is null then
1885: sv_c := sv_c + 1;
1883: if sv_dbi(i).dbi_name = 'CAEOY_RL1_TRANSMITTER_PROVINCE' and
1884: sv_dbi(i).dbi_value is null then
1885: sv_c := sv_c + 1;
1886: sv_col(sv_c) := sv_dbi(i).dbi_short_name;
1887: hr_utility.set_location('Required column '||sv_col(sv_c), 614);
1888: end if;
1889: if sv_dbi(i).dbi_name = 'CAEOY_RL1_TRANSMITTER_POSTAL_CODE' and
1890: sv_dbi(i).dbi_value is null then
1891: sv_c := sv_c + 1;
1889: if sv_dbi(i).dbi_name = 'CAEOY_RL1_TRANSMITTER_POSTAL_CODE' and
1890: sv_dbi(i).dbi_value is null then
1891: sv_c := sv_c + 1;
1892: sv_col(sv_c) := sv_dbi(i).dbi_short_name;
1893: hr_utility.set_location('Required column '||sv_col(sv_c), 615);
1894: end if;
1895: if sv_dbi(i).dbi_name = 'CAEOY_RL1_TECHNICAL_CONTACT_NAME' and
1896: sv_dbi(i).dbi_value is null then
1897: sv_c := sv_c + 1;
1895: if sv_dbi(i).dbi_name = 'CAEOY_RL1_TECHNICAL_CONTACT_NAME' and
1896: sv_dbi(i).dbi_value is null then
1897: sv_c := sv_c + 1;
1898: sv_col(sv_c) := sv_dbi(i).dbi_short_name;
1899: hr_utility.set_location('Required column '||sv_col(sv_c), 616);
1900: end if;
1901: if sv_dbi(i).dbi_name = 'CAEOY_RL1_TECHNICAL_CONTACT_PHONE' and
1902: sv_dbi(i).dbi_value is null then
1903: sv_c := sv_c + 1;
1901: if sv_dbi(i).dbi_name = 'CAEOY_RL1_TECHNICAL_CONTACT_PHONE' and
1902: sv_dbi(i).dbi_value is null then
1903: sv_c := sv_c + 1;
1904: sv_col(sv_c) := sv_dbi(i).dbi_short_name;
1905: hr_utility.set_location('Required column '||sv_col(sv_c), 617);
1906: end if;
1907: if sv_dbi(i).dbi_name = 'CAEOY_RL1_TECHNICAL_CONTACT_AREA_CODE' and
1908: sv_dbi(i).dbi_value is null then
1909: sv_c := sv_c + 1;
1907: if sv_dbi(i).dbi_name = 'CAEOY_RL1_TECHNICAL_CONTACT_AREA_CODE' and
1908: sv_dbi(i).dbi_value is null then
1909: sv_c := sv_c + 1;
1910: sv_col(sv_c) := sv_dbi(i).dbi_short_name;
1911: hr_utility.set_location('Required column '||sv_col(sv_c), 618);
1912: end if;
1913: if sv_dbi(i).dbi_name = 'CAEOY_RL1_TECHNICAL_CONTACT_LANGUAGE' and
1914: sv_dbi(i).dbi_value is null then
1915: sv_c := sv_c + 1;
1913: if sv_dbi(i).dbi_name = 'CAEOY_RL1_TECHNICAL_CONTACT_LANGUAGE' and
1914: sv_dbi(i).dbi_value is null then
1915: sv_c := sv_c + 1;
1916: sv_col(sv_c) := sv_dbi(i).dbi_short_name;
1917: hr_utility.set_location('Required column '||sv_col(sv_c), 619);
1918: end if;
1919: if sv_dbi(i).dbi_name = 'CAEOY_RL1_TRANSMITTER_ADDRESS_LINE1' and
1920: sv_dbi(i).dbi_value is null then
1921: sv_c := sv_c + 1;
1919: if sv_dbi(i).dbi_name = 'CAEOY_RL1_TRANSMITTER_ADDRESS_LINE1' and
1920: sv_dbi(i).dbi_value is null then
1921: sv_c := sv_c + 1;
1922: sv_col(sv_c) := sv_dbi(i).dbi_short_name;
1923: hr_utility.set_location('Required column '||sv_col(sv_c), 620);
1924: end if;
1925: if sv_dbi(i).dbi_name = 'CAEOY_RL1_TRANSMITTER_PACKAGE_TYPE' and
1926: sv_dbi(i).dbi_value is null then
1927: sv_c := sv_c + 1;
1925: if sv_dbi(i).dbi_name = 'CAEOY_RL1_TRANSMITTER_PACKAGE_TYPE' and
1926: sv_dbi(i).dbi_value is null then
1927: sv_c := sv_c + 1;
1928: sv_col(sv_c) := sv_dbi(i).dbi_short_name;
1929: hr_utility.set_location('Required column '||sv_col(sv_c), 621);
1930: end if;
1931: if sv_dbi(i).dbi_name = 'CAEOY_RL1_SOURCE_OF_SLIPS' and
1932: sv_dbi(i).dbi_value is null then
1933: sv_c := sv_c + 1;
1931: if sv_dbi(i).dbi_name = 'CAEOY_RL1_SOURCE_OF_SLIPS' and
1932: sv_dbi(i).dbi_value is null then
1933: sv_c := sv_c + 1;
1934: sv_col(sv_c) := sv_dbi(i).dbi_short_name;
1935: hr_utility.set_location('Required column '||sv_col(sv_c), 622);
1936: end if;
1937: end if;
1938:
1939: if sv_dbi(i).dbi_name = 'CAEOY_RL1_QUEBEC_BN' then
1941: if sv_dbi(i).dbi_value is null then
1942: sv_c := sv_c + 1;
1943: --sv_col(sv_c) := sv_dbi(i).dbi_short_name;
1944: sv_col(sv_c) := get_lookup_meaning('L_QCID_NUMBER');
1945: hr_utility.set_location('Required column '||sv_col(sv_c), 650);
1946: else
1947: if length(sv_dbi(i).dbi_value) <> 16 then
1948: sv_m := sv_m + 1;
1949: sv_msg(sv_m) := get_lookup_meaning('R_QIN_LENGTH');
1946: else
1947: if length(sv_dbi(i).dbi_value) <> 16 then
1948: sv_m := sv_m + 1;
1949: sv_msg(sv_m) := get_lookup_meaning('R_QIN_LENGTH');
1950: hr_utility.set_location(sv_msg(sv_m)|| ' '||sv_dbi(i).dbi_value, 603);
1951: else
1952: for j in 1..length(sv_dbi(i).dbi_value)
1953: loop
1954: if ( j not in ( 11, 12 ) ) then
1954: if ( j not in ( 11, 12 ) ) then
1955: if instr('1234567890',substr(sv_dbi(i).dbi_value,j,1)) = 0 then
1956: sv_m := sv_m + 1;
1957: sv_msg(sv_m) := get_lookup_meaning('R_QIN_INVALID');
1958: hr_utility.set_location(sv_msg(sv_m)|| ' '||sv_dbi(i).dbi_value, 651);
1959: exit;
1960: end if;
1961: else
1962: if instr('ABCDEFGHIJKLMNOPQRSTUVWXYZ',
1962: if instr('ABCDEFGHIJKLMNOPQRSTUVWXYZ',
1963: substr(sv_dbi(i).dbi_value,j,1)) = 0 then
1964: sv_m := sv_m + 1;
1965: sv_msg(sv_m) := get_lookup_meaning('R_QIN_INVALID');
1966: hr_utility.set_location(sv_msg(sv_m)|| ' '||sv_dbi(i).dbi_value, 652);
1967: exit;
1968: end if;
1969: end if;
1970: end loop;
1976: sv_employer_name := sv_dbi(i).dbi_value;
1977: if sv_dbi(i).dbi_value is null then
1978: sv_c := sv_c + 1;
1979: sv_col(sv_c) := sv_dbi(i).dbi_short_name;
1980: hr_utility.set_location('Required column '||sv_col(sv_c), 653);
1981: end if;
1982: end if;
1983:
1984: if sv_dbi(i).dbi_name = 'CAEOY_RL1_EMPLOYER_CITY' then
1985: sv_employer_city := sv_dbi(i).dbi_value;
1986: if sv_dbi(i).dbi_value is null then
1987: sv_c := sv_c + 1;
1988: sv_col(sv_c) := sv_dbi(i).dbi_short_name;
1989: hr_utility.set_location('Required column '||sv_col(sv_c), 654);
1990: end if;
1991: end if;
1992:
1993: if sv_dbi(i).dbi_name = 'CAEOY_RL1_EMPLOYER_PROVINCE' then
1994: sv_employer_province := sv_dbi(i).dbi_value;
1995: if sv_dbi(i).dbi_value is null then
1996: sv_c := sv_c + 1;
1997: sv_col(sv_c) := sv_dbi(i).dbi_short_name;
1998: hr_utility.set_location('Required column '||sv_col(sv_c), 655);
1999: end if;
2000: end if;
2001:
2002: if sv_dbi(i).dbi_name = 'CAEOY_RL1_EMPLOYER_POSTAL_CODE' then
2003: sv_employer_postal_code := sv_dbi(i).dbi_value;
2004: if sv_dbi(i).dbi_value is null then
2005: sv_c := sv_c + 1;
2006: sv_col(sv_c) := sv_dbi(i).dbi_short_name;
2007: hr_utility.set_location('Required column '||sv_col(sv_c), 656);
2008: end if;
2009: end if;
2010:
2011: if sv_dbi(i).dbi_name = 'CAEOY_RL1_ACCOUNTING_CONTACT_NAME' and
2011: if sv_dbi(i).dbi_name = 'CAEOY_RL1_ACCOUNTING_CONTACT_NAME' and
2012: sv_dbi(i).dbi_value is null then
2013: sv_c := sv_c + 1;
2014: sv_col(sv_c) := sv_dbi(i).dbi_short_name;
2015: hr_utility.set_location('Required column '||sv_col(sv_c), 657);
2016: end if;
2017:
2018: if sv_dbi(i).dbi_name = 'CAEOY_RL1_ACCOUNTING_CONTACT_PHONE' and
2019: sv_dbi(i).dbi_value is null then
2018: if sv_dbi(i).dbi_name = 'CAEOY_RL1_ACCOUNTING_CONTACT_PHONE' and
2019: sv_dbi(i).dbi_value is null then
2020: sv_c := sv_c + 1;
2021: sv_col(sv_c) := sv_dbi(i).dbi_short_name;
2022: hr_utility.set_location('Required column '||sv_col(sv_c), 658);
2023: end if;
2024:
2025: if sv_dbi(i).dbi_name = 'CAEOY_TAXATION_YEAR' and
2026: sv_dbi(i).dbi_value is null then
2025: if sv_dbi(i).dbi_name = 'CAEOY_TAXATION_YEAR' and
2026: sv_dbi(i).dbi_value is null then
2027: sv_c := sv_c + 1;
2028: sv_col(sv_c) := sv_dbi(i).dbi_short_name;
2029: hr_utility.set_location('Required column '||sv_col(sv_c), 659);
2030: end if;
2031:
2032: if sv_dbi(i).dbi_name = 'CAEOY_RL1_EMPLOYER_ADDRESS_LINE1' then
2033: sv_employer_address_line1 := sv_dbi(i).dbi_value;
2045:
2046: end if; -- End if for sv_report_type in ('RL1','CAEOY_RL1_AMEND_PP')
2047:
2048: /* Start of RL2 Employer Validation */
2049: hr_utility.set_location('RL2 Employer validation', 699);
2050: if sv_report_type = 'RL2' then
2051:
2052: if sv_trans_y_n = 'Y' then
2053:
2053:
2054: if lr_rl2_transrec.TRANSMITTER_NUMBER is null then
2055: sv_c := sv_c + 1;
2056: sv_col(sv_c) := 'Transmitter Number';
2057: hr_utility.set_location('Required column '||sv_col(sv_c), 710);
2058: else
2059: if ( ( substr(lr_rl2_transrec.TRANSMITTER_NUMBER,1,2) <> 'NP' ) or
2060: ( length(lr_rl2_transrec.TRANSMITTER_NUMBER) <> 8 ) or
2061: ( not ( substr(lr_rl2_transrec.TRANSMITTER_NUMBER,3) >= '000000' ) and
2062: ( substr(lr_rl2_transrec.TRANSMITTER_NUMBER,3) <= '999999' ) ) )
2063: then
2064: sv_m := sv_m + 1;
2065: sv_msg(sv_m) := get_lookup_meaning('R_INVALID_TRANS_NO');
2066: hr_utility.set_location(sv_msg(sv_m)|| ' '||lr_rl2_transrec.TRANSMITTER_NUMBER, 711);
2067: end if;
2068: end if;
2069:
2070: if lr_rl2_transrec.TRANSMITTER_NAME is null then
2069:
2070: if lr_rl2_transrec.TRANSMITTER_NAME is null then
2071: sv_c := sv_c + 1;
2072: sv_col(sv_c) := 'Transmitter Name';
2073: hr_utility.set_location('Required column '||sv_col(sv_c), 712);
2074: end if;
2075:
2076: if lr_rl2_transrec.TRANSMITTER_CITY is null then
2077: sv_c := sv_c + 1;
2075:
2076: if lr_rl2_transrec.TRANSMITTER_CITY is null then
2077: sv_c := sv_c + 1;
2078: sv_col(sv_c) := 'Transmitter City';
2079: hr_utility.set_location('Required column '||sv_col(sv_c), 713);
2080: end if;
2081:
2082: if lr_rl2_transrec.TRANSMITTER_PROVINCE is null then
2083: sv_c := sv_c + 1;
2081:
2082: if lr_rl2_transrec.TRANSMITTER_PROVINCE is null then
2083: sv_c := sv_c + 1;
2084: sv_col(sv_c) := 'Transmitter Province';
2085: hr_utility.set_location('Required column '||sv_col(sv_c), 714);
2086: end if;
2087:
2088: if lr_rl2_transrec.TRANSMITTER_POSTAL_CODE is null then
2089: sv_c := sv_c + 1;
2087:
2088: if lr_rl2_transrec.TRANSMITTER_POSTAL_CODE is null then
2089: sv_c := sv_c + 1;
2090: sv_col(sv_c) := 'Transmitter Postal Code';
2091: hr_utility.set_location('Required column '||sv_col(sv_c), 715);
2092: end if;
2093:
2094: if lr_rl2_transrec.TRANSMITTER_TECH_CONTACT_NAME is null then
2095: sv_c := sv_c + 1;
2093:
2094: if lr_rl2_transrec.TRANSMITTER_TECH_CONTACT_NAME is null then
2095: sv_c := sv_c + 1;
2096: sv_col(sv_c) := 'Technical Contact Name';
2097: hr_utility.set_location('Required column '||sv_col(sv_c), 716);
2098: end if;
2099:
2100: if lr_rl2_transrec.TRANSMITTER_TECH_CONTACT_PHONE is null then
2101: sv_c := sv_c + 1;
2099:
2100: if lr_rl2_transrec.TRANSMITTER_TECH_CONTACT_PHONE is null then
2101: sv_c := sv_c + 1;
2102: sv_col(sv_c) := 'Technical Contact Phone';
2103: hr_utility.set_location('Required column '||sv_col(sv_c), 717);
2104: end if;
2105:
2106: if lr_rl2_transrec.TRANSMITTER_TECH_CONTACT_CODE is null then
2107: sv_c := sv_c + 1;
2105:
2106: if lr_rl2_transrec.TRANSMITTER_TECH_CONTACT_CODE is null then
2107: sv_c := sv_c + 1;
2108: sv_col(sv_c) := 'Technical Contact Area Code';
2109: hr_utility.set_location('Required column '||sv_col(sv_c), 718);
2110: end if;
2111:
2112: if lr_rl2_transrec.TRANSMITTER_TECH_CONTACT_LANG is null then
2113: sv_c := sv_c + 1;
2111:
2112: if lr_rl2_transrec.TRANSMITTER_TECH_CONTACT_LANG is null then
2113: sv_c := sv_c + 1;
2114: sv_col(sv_c) := 'Technical Contact Language';
2115: hr_utility.set_location('Required column '||sv_col(sv_c), 719);
2116: end if;
2117:
2118: if lr_rl2_transrec.TRANSMITTER_PACKAGE_TYPE is null then
2119: sv_c := sv_c + 1;
2117:
2118: if lr_rl2_transrec.TRANSMITTER_PACKAGE_TYPE is null then
2119: sv_c := sv_c + 1;
2120: sv_col(sv_c) := 'Transmitter Package Type';
2121: hr_utility.set_location('Required column '||sv_col(sv_c), 720);
2122: end if;
2123:
2124: if lr_rl2_transrec.SOURCE_OF_SLIPS is null then
2125: sv_c := sv_c + 1;
2123:
2124: if lr_rl2_transrec.SOURCE_OF_SLIPS is null then
2125: sv_c := sv_c + 1;
2126: sv_col(sv_c) := 'Source of Slips';
2127: hr_utility.set_location('Required column '||sv_col(sv_c), 721);
2128: end if;
2129:
2130: if lr_rl2_transrec.TRANSMITTER_ADDRESS_LINE1 is null then
2131: sv_c := sv_c + 1;
2129:
2130: if lr_rl2_transrec.TRANSMITTER_ADDRESS_LINE1 is null then
2131: sv_c := sv_c + 1;
2132: sv_col(sv_c) := 'Transmitter Address Line 1';
2133: hr_utility.set_location('Required column '||sv_col(sv_c), 722);
2134: end if;
2135:
2136: end if; -- end of sv_trans_y_n = 'Y'
2137:
2139: sv_qin := lr_rl2_transrec.QUEBEC_BUSINESS_NUMBER;
2140: if lr_rl2_transrec.QUEBEC_BUSINESS_NUMBER is null then
2141: sv_c := sv_c + 1;
2142: sv_col(sv_c) := get_lookup_meaning('L_QCID_NUMBER');
2143: hr_utility.set_location('Required column '||sv_col(sv_c), 750);
2144: else
2145: if length(lr_rl2_transrec.QUEBEC_BUSINESS_NUMBER) <> 16 then
2146: sv_m := sv_m + 1;
2147: sv_msg(sv_m) := get_lookup_meaning('R_QIN_LENGTH');
2144: else
2145: if length(lr_rl2_transrec.QUEBEC_BUSINESS_NUMBER) <> 16 then
2146: sv_m := sv_m + 1;
2147: sv_msg(sv_m) := get_lookup_meaning('R_QIN_LENGTH');
2148: hr_utility.set_location(sv_msg(sv_m)|| ' '||lr_rl2_transrec.QUEBEC_BUSINESS_NUMBER, 703);
2149: else
2150: for j in 1..length(lr_rl2_transrec.QUEBEC_BUSINESS_NUMBER)
2151: loop
2152: if ( j not in ( 11, 12 ) ) then
2152: if ( j not in ( 11, 12 ) ) then
2153: if instr('1234567890',substr(lr_rl2_transrec.QUEBEC_BUSINESS_NUMBER,j,1)) = 0 then
2154: sv_m := sv_m + 1;
2155: sv_msg(sv_m) := get_lookup_meaning('R_QIN_INVALID');
2156: hr_utility.set_location(sv_msg(sv_m)|| ' '||lr_rl2_transrec.QUEBEC_BUSINESS_NUMBER, 751);
2157: exit;
2158: end if;
2159: else
2160: if instr('ABCDEFGHIJKLMNOPQRSTUVWXYZ',
2160: if instr('ABCDEFGHIJKLMNOPQRSTUVWXYZ',
2161: substr(lr_rl2_transrec.QUEBEC_BUSINESS_NUMBER,j,1)) = 0 then
2162: sv_m := sv_m + 1;
2163: sv_msg(sv_m) := get_lookup_meaning('R_QIN_INVALID');
2164: hr_utility.set_location(sv_msg(sv_m)|| ' '||lr_rl2_transrec.QUEBEC_BUSINESS_NUMBER, 752);
2165: exit;
2166: end if;
2167: end if;
2168: end loop;
2173: sv_employer_name := lr_rl2_transrec.EMPLOYER_NAME;
2174: if lr_rl2_transrec.EMPLOYER_NAME is null then
2175: sv_c := sv_c + 1;
2176: sv_col(sv_c) := 'Employer Name';
2177: hr_utility.set_location('Required column '||sv_col(sv_c), 753);
2178: end if;
2179:
2180: sv_employer_city := lr_rl2_transrec.EMPLOYER_CITY;
2181: if lr_rl2_transrec.EMPLOYER_CITY is null then
2180: sv_employer_city := lr_rl2_transrec.EMPLOYER_CITY;
2181: if lr_rl2_transrec.EMPLOYER_CITY is null then
2182: sv_c := sv_c + 1;
2183: sv_col(sv_c) := 'Employer City';
2184: hr_utility.set_location('Required column '||sv_col(sv_c), 754);
2185: end if;
2186:
2187: sv_employer_province := lr_rl2_transrec.EMPLOYER_PROVINCE;
2188: if lr_rl2_transrec.EMPLOYER_PROVINCE is null then
2187: sv_employer_province := lr_rl2_transrec.EMPLOYER_PROVINCE;
2188: if lr_rl2_transrec.EMPLOYER_PROVINCE is null then
2189: sv_c := sv_c + 1;
2190: sv_col(sv_c) := 'Employer Province';
2191: hr_utility.set_location('Required column '||sv_col(sv_c), 755);
2192: end if;
2193:
2194: sv_employer_postal_code := lr_rl2_transrec.EMPLOYER_POSTAL_CODE;
2195: if lr_rl2_transrec.EMPLOYER_POSTAL_CODE is null then
2194: sv_employer_postal_code := lr_rl2_transrec.EMPLOYER_POSTAL_CODE;
2195: if lr_rl2_transrec.EMPLOYER_POSTAL_CODE is null then
2196: sv_c := sv_c + 1;
2197: sv_col(sv_c) := 'Employer Postal Code';
2198: hr_utility.set_location('Required column '||sv_col(sv_c), 756);
2199: end if;
2200:
2201: if lr_rl2_transrec.TRANSMITTER_ACCT_CONTACT_NAME is null then
2202: sv_c := sv_c + 1;
2200:
2201: if lr_rl2_transrec.TRANSMITTER_ACCT_CONTACT_NAME is null then
2202: sv_c := sv_c + 1;
2203: sv_col(sv_c) := 'Accounting Contact Name';
2204: hr_utility.set_location('Required column '||sv_col(sv_c), 757);
2205: end if;
2206:
2207: if lr_rl2_transrec.TRANSMITTER_ACCT_CONTACT_PHONE is null then
2208: sv_c := sv_c + 1;
2206:
2207: if lr_rl2_transrec.TRANSMITTER_ACCT_CONTACT_PHONE is null then
2208: sv_c := sv_c + 1;
2209: sv_col(sv_c) := 'Accounting Contact Phone';
2210: hr_utility.set_location('Required column '||sv_col(sv_c), 758);
2211: end if;
2212:
2213: if lr_rl2_transrec.REPORTING_YEAR is null then
2214: sv_c := sv_c + 1;
2212:
2213: if lr_rl2_transrec.REPORTING_YEAR is null then
2214: sv_c := sv_c + 1;
2215: sv_col(sv_c) := 'Taxation Year';
2216: hr_utility.set_location('Required column '||sv_col(sv_c), 759);
2217: end if;
2218:
2219: sv_employer_address_line1 := lr_rl2_transrec.EMPLOYER_ADD_LINE1;
2220: if lr_rl2_transrec.EMPLOYER_ADD_LINE1 is null then
2242: lv_asg_act_id number(10) := 0;
2243: begin
2244: for i in sv_dbi.first..sv_dbi.last
2245: loop
2246: hr_utility.set_location(to_char(i)||'. '||sv_dbi(i).dbi_name||' '||
2247: sv_dbi(i).dbi_value||' '||sv_dbi(i).dbi_short_name, 999 );
2248: if sv_dbi(i).dbi_name = 'CAEOY_PERSON_ID' then
2249: lv_person_id := to_number(sv_dbi(i).dbi_value);
2250: end if;
2322: end if;
2323: if sv_dbi(i).dbi_name = 'CAEOY_GROSS_EARNINGS_PER_JD_GRE_YTD' then
2324: sv_total_earnings :=nvl(sv_total_earnings,0) + fnd_number.canonical_to_number(sv_dbi(i).dbi_value);
2325: --lv_multi_jurisdiction := lv_multi_jurisdiction + 1;
2326: hr_utility.set_location('Multi Jurisdiction : '||
2327: to_char(lv_multi_jurisdiction), 999 );
2328: end if;
2329: if sv_dbi(i).dbi_name = 'CAEOY_CPP_EE_TAXABLE_PER_JD_GRE_YTD' then
2330: sv_pensionable_earnings :=nvl(sv_pensionable_earnings,0) + fnd_number.canonical_to_number(sv_dbi(i).dbi_value);
2356: from ff_archive_item_contexts
2357: where archive_item_id = sv_dbi(i).archive_item_id
2358: and context_id = sv_context_id;
2359:
2360: hr_utility.trace('Emp JD is '||sv_emp_jurisdiction);
2361:
2362: if sv_emp_jurisdiction = 'QC' then
2363:
2364: if sv_dbi(i).dbi_name = 'CAEOY_EI_EE_TAXABLE_PER_JD_GRE_YTD' then
2362: if sv_emp_jurisdiction = 'QC' then
2363:
2364: if sv_dbi(i).dbi_name = 'CAEOY_EI_EE_TAXABLE_PER_JD_GRE_YTD' then
2365: sv_qc_insurable_earnings := nvl(sv_qc_insurable_earnings,0) + fnd_number.canonical_to_number(sv_dbi(i).dbi_value);
2366: hr_utility.trace('QC Insurable Earning : '|| sv_qc_insurable_earnings);
2367: end if;
2368:
2369: if sv_dbi(i).dbi_name = 'CAEOY_EI_EE_WITHHELD_PER_JD_GRE_YTD' then
2370: sv_qc_ded_reported_18 := nvl(sv_qc_ded_reported_18,0) + fnd_number.canonical_to_number(sv_dbi(i).dbi_value);
2367: end if;
2368:
2369: if sv_dbi(i).dbi_name = 'CAEOY_EI_EE_WITHHELD_PER_JD_GRE_YTD' then
2370: sv_qc_ded_reported_18 := nvl(sv_qc_ded_reported_18,0) + fnd_number.canonical_to_number(sv_dbi(i).dbi_value);
2371: hr_utility.trace('QC Withheld : '|| sv_qc_ded_reported_18);
2372: end if;
2373:
2374: else
2375:
2374: else
2375:
2376: if sv_dbi(i).dbi_name = 'CAEOY_EI_EE_TAXABLE_PER_JD_GRE_YTD' then
2377: sv_insurable_earnings := nvl(sv_insurable_earnings,0) + fnd_number.canonical_to_number(sv_dbi(i).dbi_value);
2378: hr_utility.trace('FED Insurable Earning : '|| sv_insurable_earnings);
2379: end if;
2380:
2381: if sv_dbi(i).dbi_name = 'CAEOY_EI_EE_WITHHELD_PER_JD_GRE_YTD' then
2382: sv_ded_reported_18 := nvl(sv_ded_reported_18,0) + fnd_number.canonical_to_number(sv_dbi(i).dbi_value);
2379: end if;
2380:
2381: if sv_dbi(i).dbi_name = 'CAEOY_EI_EE_WITHHELD_PER_JD_GRE_YTD' then
2382: sv_ded_reported_18 := nvl(sv_ded_reported_18,0) + fnd_number.canonical_to_number(sv_dbi(i).dbi_value);
2383: hr_utility.trace('FED Withheld : '|| sv_ded_reported_18);
2384: end if;
2385:
2386: end if; /* 'QC' */
2387:
2473:
2474: sv_ei_ded_required := (( sv_insurable_earnings * sv_ei_rate )/ 100 );
2475: sv_ei_max_exempt := ( ( sv_ei_max_earn ) * sv_ei_rate / 100 );
2476:
2477: hr_utility.trace('QC EI Dedn Req. ' || sv_qc_ei_ded_required);
2478: hr_utility.trace('QC EI Max Exempt ' || sv_qc_ei_max_exempt);
2479: hr_utility.trace('QC EI Withheld ' || sv_qc_ded_reported_18);
2480:
2481: hr_utility.trace('FED EI Dedn Req. ' || sv_ei_ded_required);
2474: sv_ei_ded_required := (( sv_insurable_earnings * sv_ei_rate )/ 100 );
2475: sv_ei_max_exempt := ( ( sv_ei_max_earn ) * sv_ei_rate / 100 );
2476:
2477: hr_utility.trace('QC EI Dedn Req. ' || sv_qc_ei_ded_required);
2478: hr_utility.trace('QC EI Max Exempt ' || sv_qc_ei_max_exempt);
2479: hr_utility.trace('QC EI Withheld ' || sv_qc_ded_reported_18);
2480:
2481: hr_utility.trace('FED EI Dedn Req. ' || sv_ei_ded_required);
2482: hr_utility.trace('FED EI Max Exempt ' || sv_ei_max_exempt);
2475: sv_ei_max_exempt := ( ( sv_ei_max_earn ) * sv_ei_rate / 100 );
2476:
2477: hr_utility.trace('QC EI Dedn Req. ' || sv_qc_ei_ded_required);
2478: hr_utility.trace('QC EI Max Exempt ' || sv_qc_ei_max_exempt);
2479: hr_utility.trace('QC EI Withheld ' || sv_qc_ded_reported_18);
2480:
2481: hr_utility.trace('FED EI Dedn Req. ' || sv_ei_ded_required);
2482: hr_utility.trace('FED EI Max Exempt ' || sv_ei_max_exempt);
2483: hr_utility.trace('FED EI Withheld ' || sv_ded_reported_18);
2477: hr_utility.trace('QC EI Dedn Req. ' || sv_qc_ei_ded_required);
2478: hr_utility.trace('QC EI Max Exempt ' || sv_qc_ei_max_exempt);
2479: hr_utility.trace('QC EI Withheld ' || sv_qc_ded_reported_18);
2480:
2481: hr_utility.trace('FED EI Dedn Req. ' || sv_ei_ded_required);
2482: hr_utility.trace('FED EI Max Exempt ' || sv_ei_max_exempt);
2483: hr_utility.trace('FED EI Withheld ' || sv_ded_reported_18);
2484:
2485: /* overlimit condition */
2478: hr_utility.trace('QC EI Max Exempt ' || sv_qc_ei_max_exempt);
2479: hr_utility.trace('QC EI Withheld ' || sv_qc_ded_reported_18);
2480:
2481: hr_utility.trace('FED EI Dedn Req. ' || sv_ei_ded_required);
2482: hr_utility.trace('FED EI Max Exempt ' || sv_ei_max_exempt);
2483: hr_utility.trace('FED EI Withheld ' || sv_ded_reported_18);
2484:
2485: /* overlimit condition */
2486: if sv_ei_ded_required > sv_ei_max_exempt then
2479: hr_utility.trace('QC EI Withheld ' || sv_qc_ded_reported_18);
2480:
2481: hr_utility.trace('FED EI Dedn Req. ' || sv_ei_ded_required);
2482: hr_utility.trace('FED EI Max Exempt ' || sv_ei_max_exempt);
2483: hr_utility.trace('FED EI Withheld ' || sv_ded_reported_18);
2484:
2485: /* overlimit condition */
2486: if sv_ei_ded_required > sv_ei_max_exempt then
2487: sv_ei_ded_required := sv_ei_max_exempt;
2505: /* difference */
2506:
2507: sv_qc_insurable_earnings := sv_qc_insurable_earnings;
2508:
2509: hr_utility.trace('sv_ei_ded_required : '|| sv_ei_ded_required);
2510: hr_utility.trace('sv_ded_reported_18 : '|| sv_ded_reported_18);
2511: hr_utility.trace('sv_insurable_earnings : '|| sv_insurable_earnings);
2512:
2513: hr_utility.trace('sv_qc_ei_ded_required : '|| sv_qc_ei_ded_required);
2506:
2507: sv_qc_insurable_earnings := sv_qc_insurable_earnings;
2508:
2509: hr_utility.trace('sv_ei_ded_required : '|| sv_ei_ded_required);
2510: hr_utility.trace('sv_ded_reported_18 : '|| sv_ded_reported_18);
2511: hr_utility.trace('sv_insurable_earnings : '|| sv_insurable_earnings);
2512:
2513: hr_utility.trace('sv_qc_ei_ded_required : '|| sv_qc_ei_ded_required);
2514: hr_utility.trace('sv_qc_ded_reported_18 : '|| sv_qc_ded_reported_18);
2507: sv_qc_insurable_earnings := sv_qc_insurable_earnings;
2508:
2509: hr_utility.trace('sv_ei_ded_required : '|| sv_ei_ded_required);
2510: hr_utility.trace('sv_ded_reported_18 : '|| sv_ded_reported_18);
2511: hr_utility.trace('sv_insurable_earnings : '|| sv_insurable_earnings);
2512:
2513: hr_utility.trace('sv_qc_ei_ded_required : '|| sv_qc_ei_ded_required);
2514: hr_utility.trace('sv_qc_ded_reported_18 : '|| sv_qc_ded_reported_18);
2515: hr_utility.trace('sv_qc_insurable_earnings : '|| sv_qc_insurable_earnings);
2509: hr_utility.trace('sv_ei_ded_required : '|| sv_ei_ded_required);
2510: hr_utility.trace('sv_ded_reported_18 : '|| sv_ded_reported_18);
2511: hr_utility.trace('sv_insurable_earnings : '|| sv_insurable_earnings);
2512:
2513: hr_utility.trace('sv_qc_ei_ded_required : '|| sv_qc_ei_ded_required);
2514: hr_utility.trace('sv_qc_ded_reported_18 : '|| sv_qc_ded_reported_18);
2515: hr_utility.trace('sv_qc_insurable_earnings : '|| sv_qc_insurable_earnings);
2516:
2517: sv_ei_deficiency := sv_ei_ded_required - sv_ded_reported_18 ;
2510: hr_utility.trace('sv_ded_reported_18 : '|| sv_ded_reported_18);
2511: hr_utility.trace('sv_insurable_earnings : '|| sv_insurable_earnings);
2512:
2513: hr_utility.trace('sv_qc_ei_ded_required : '|| sv_qc_ei_ded_required);
2514: hr_utility.trace('sv_qc_ded_reported_18 : '|| sv_qc_ded_reported_18);
2515: hr_utility.trace('sv_qc_insurable_earnings : '|| sv_qc_insurable_earnings);
2516:
2517: sv_ei_deficiency := sv_ei_ded_required - sv_ded_reported_18 ;
2518: sv_qc_ei_deficiency := sv_qc_ei_ded_required - sv_qc_ded_reported_18 ;
2511: hr_utility.trace('sv_insurable_earnings : '|| sv_insurable_earnings);
2512:
2513: hr_utility.trace('sv_qc_ei_ded_required : '|| sv_qc_ei_ded_required);
2514: hr_utility.trace('sv_qc_ded_reported_18 : '|| sv_qc_ded_reported_18);
2515: hr_utility.trace('sv_qc_insurable_earnings : '|| sv_qc_insurable_earnings);
2516:
2517: sv_ei_deficiency := sv_ei_ded_required - sv_ded_reported_18 ;
2518: sv_qc_ei_deficiency := sv_qc_ei_ded_required - sv_qc_ded_reported_18 ;
2519:
2516:
2517: sv_ei_deficiency := sv_ei_ded_required - sv_ded_reported_18 ;
2518: sv_qc_ei_deficiency := sv_qc_ei_ded_required - sv_qc_ded_reported_18 ;
2519:
2520: hr_utility.trace('sv_ei_deficiency : '|| sv_ei_deficiency);
2521: hr_utility.trace('sv_qc_ei_deficiency : '|| sv_qc_ei_deficiency);
2522:
2523: if sv_ei_deficiency < 0 then
2524: sv_ei_deficiency := 0;
2517: sv_ei_deficiency := sv_ei_ded_required - sv_ded_reported_18 ;
2518: sv_qc_ei_deficiency := sv_qc_ei_ded_required - sv_qc_ded_reported_18 ;
2519:
2520: hr_utility.trace('sv_ei_deficiency : '|| sv_ei_deficiency);
2521: hr_utility.trace('sv_qc_ei_deficiency : '|| sv_qc_ei_deficiency);
2522:
2523: if sv_ei_deficiency < 0 then
2524: sv_ei_deficiency := 0;
2525: end if;
2669: PROCEDURE fed_employer_validation ( p_dbi_name in varchar2,
2670: p_dbi_value in varchar2,
2671: p_dbi_short_name in varchar2) is
2672: begin
2673: --hr_utility.set_location(p_dbi_short_name, 201);
2674: if sv_trans_y_n = 'Y' then
2675: if p_dbi_name = 'CAEOY_TRANSMITTER_NUMBER' then
2676: if p_dbi_value is null then
2677: sv_c := sv_c + 1;
2675: if p_dbi_name = 'CAEOY_TRANSMITTER_NUMBER' then
2676: if p_dbi_value is null then
2677: sv_c := sv_c + 1;
2678: sv_col(sv_c) := p_dbi_short_name;
2679: hr_utility.set_location('Required column '||sv_col(sv_c), 210);
2680: else
2681: if ( ( substr(p_dbi_value,1,2) <> 'MM' ) or
2682: ( length(p_dbi_value) <> 8 ) or
2683: ( not ( substr(p_dbi_value,3) >= '000000' ) and
2684: ( substr(p_dbi_value,3) <= '999999' ) ) )
2685: then
2686: sv_m := sv_m + 1;
2687: sv_msg(sv_m) := get_lookup_meaning('R_INVALID_TRANS_NO');
2688: hr_utility.set_location(sv_msg(sv_m)|| ' '||p_dbi_value, 211);
2689: end if;
2690: end if;
2691: end if;
2692: if p_dbi_name = 'CAEOY_TRANSMITTER_NAME' and
2692: if p_dbi_name = 'CAEOY_TRANSMITTER_NAME' and
2693: p_dbi_value is null then
2694: sv_c := sv_c + 1;
2695: sv_col(sv_c) := p_dbi_short_name;
2696: hr_utility.set_location('Required column '||sv_col(sv_c), 212);
2697: end if;
2698: if p_dbi_name = 'CAEOY_TRANSMITTER_CITY' and
2699: p_dbi_value is null then
2700: sv_c := sv_c + 1;
2698: if p_dbi_name = 'CAEOY_TRANSMITTER_CITY' and
2699: p_dbi_value is null then
2700: sv_c := sv_c + 1;
2701: sv_col(sv_c) := p_dbi_short_name;
2702: hr_utility.set_location('Required column '||sv_col(sv_c), 213);
2703: end if;
2704: if p_dbi_name = 'CAEOY_TRANSMITTER_PROVINCE' and
2705: p_dbi_value is null then
2706: sv_c := sv_c + 1;
2704: if p_dbi_name = 'CAEOY_TRANSMITTER_PROVINCE' and
2705: p_dbi_value is null then
2706: sv_c := sv_c + 1;
2707: sv_col(sv_c) := p_dbi_short_name;
2708: hr_utility.set_location('Required column '||sv_col(sv_c), 214);
2709: end if;
2710: if p_dbi_name = 'CAEOY_TRANSMITTER_POSTAL_CODE' and
2711: p_dbi_value is null then
2712: sv_c := sv_c + 1;
2710: if p_dbi_name = 'CAEOY_TRANSMITTER_POSTAL_CODE' and
2711: p_dbi_value is null then
2712: sv_c := sv_c + 1;
2713: sv_col(sv_c) := p_dbi_short_name;
2714: hr_utility.set_location('Required column '||sv_col(sv_c), 215);
2715: end if;
2716: if p_dbi_name = 'CAEOY_TECHNICAL_CONTACT_NAME' and
2717: p_dbi_value is null then
2718: sv_c := sv_c + 1;
2716: if p_dbi_name = 'CAEOY_TECHNICAL_CONTACT_NAME' and
2717: p_dbi_value is null then
2718: sv_c := sv_c + 1;
2719: sv_col(sv_c) := p_dbi_short_name;
2720: hr_utility.set_location('Required column '||sv_col(sv_c), 216);
2721: end if;
2722: if p_dbi_name = 'CAEOY_TECHNICAL_CONTACT_PHONE' and
2723: p_dbi_value is null then
2724: sv_c := sv_c + 1;
2722: if p_dbi_name = 'CAEOY_TECHNICAL_CONTACT_PHONE' and
2723: p_dbi_value is null then
2724: sv_c := sv_c + 1;
2725: sv_col(sv_c) := p_dbi_short_name;
2726: hr_utility.set_location('Required column '||sv_col(sv_c), 217);
2727: end if;
2728: if p_dbi_name = 'CAEOY_TECHNICAL_CONTACT_AREA_CODE' and
2729: p_dbi_value is null then
2730: sv_c := sv_c + 1;
2728: if p_dbi_name = 'CAEOY_TECHNICAL_CONTACT_AREA_CODE' and
2729: p_dbi_value is null then
2730: sv_c := sv_c + 1;
2731: sv_col(sv_c) := p_dbi_short_name;
2732: hr_utility.set_location('Required column '||sv_col(sv_c), 218);
2733: end if;
2734: if p_dbi_name = 'CAEOY_TECHNICAL_CONTACT_LANGUAGE' and
2735: p_dbi_value is null then
2736: sv_c := sv_c + 1;
2734: if p_dbi_name = 'CAEOY_TECHNICAL_CONTACT_LANGUAGE' and
2735: p_dbi_value is null then
2736: sv_c := sv_c + 1;
2737: sv_col(sv_c) := p_dbi_short_name;
2738: hr_utility.set_location('Required column '||sv_col(sv_c), 219);
2739: end if;
2740: if p_dbi_name = 'CAEOY_TRANSMITTER_TYPE_INDICATOR' and
2741: p_dbi_value is null then
2742: sv_c := sv_c + 1;
2740: if p_dbi_name = 'CAEOY_TRANSMITTER_TYPE_INDICATOR' and
2741: p_dbi_value is null then
2742: sv_c := sv_c + 1;
2743: sv_col(sv_c) := p_dbi_short_name;
2744: hr_utility.set_location('Required column '||sv_col(sv_c), 220);
2745: end if;
2746: end if;
2747: if p_dbi_name = 'CAEOY_EMPLOYER_IDENTIFICATION_NUMBER' then
2748: sv_busi_no := p_dbi_value;
2748: sv_busi_no := p_dbi_value;
2749: if p_dbi_value is null then
2750: sv_c := sv_c + 1;
2751: sv_col(sv_c) := p_dbi_short_name;
2752: hr_utility.set_location('Required column '||sv_col(sv_c), 250);
2753: else
2754: if length(p_dbi_value) <> 15 then
2755: sv_m := sv_m + 1;
2756: sv_msg(sv_m) := get_lookup_meaning('R_BN_LENGTH');
2753: else
2754: if length(p_dbi_value) <> 15 then
2755: sv_m := sv_m + 1;
2756: sv_msg(sv_m) := get_lookup_meaning('R_BN_LENGTH');
2757: hr_utility.set_location(sv_msg(sv_m)|| ' '||p_dbi_value, 203);
2758: else
2759: for i in 1..length(p_dbi_value)
2760: loop
2761: if ( i not in ( 10, 11 ) ) then
2761: if ( i not in ( 10, 11 ) ) then
2762: if instr('1234567890',substr(p_dbi_value,i,1)) = 0 then
2763: sv_m := sv_m + 1;
2764: sv_msg(sv_m) := get_lookup_meaning('R_BN_INVALID');
2765: hr_utility.set_location(sv_msg(sv_m)|| ' '||p_dbi_value, 251);
2766: exit;
2767: end if;
2768: else
2769: if instr('ABCDEFGHIJKLMNOPQRSTUVWXYZ',substr(p_dbi_value,i,1)) =
2769: if instr('ABCDEFGHIJKLMNOPQRSTUVWXYZ',substr(p_dbi_value,i,1)) =
2770: 0 then
2771: sv_m := sv_m + 1;
2772: sv_msg(sv_m) := get_lookup_meaning('R_BN_INVALID');
2773: hr_utility.set_location(sv_msg(sv_m)|| ' '||p_dbi_value, 252);
2774: exit;
2775: end if;
2776: end if;
2777:
2783: sv_employer_name := p_dbi_value;
2784: if p_dbi_value is null then
2785: sv_c := sv_c + 1;
2786: sv_col(sv_c) := p_dbi_short_name;
2787: hr_utility.set_location('Required column '||sv_col(sv_c), 253);
2788: end if;
2789: end if;
2790: if p_dbi_name = 'CAEOY_EMPLOYER_CITY' then
2791: sv_employer_city := p_dbi_value;
2791: sv_employer_city := p_dbi_value;
2792: if p_dbi_value is null then
2793: sv_c := sv_c + 1;
2794: sv_col(sv_c) := p_dbi_short_name;
2795: hr_utility.set_location('Required column '||sv_col(sv_c), 254);
2796: end if;
2797: end if;
2798: if p_dbi_name = 'CAEOY_EMPLOYER_PROVINCE' then
2799: sv_employer_province := p_dbi_value;
2799: sv_employer_province := p_dbi_value;
2800: if p_dbi_value is null then
2801: sv_c := sv_c + 1;
2802: sv_col(sv_c) := p_dbi_short_name;
2803: hr_utility.set_location('Required column '||sv_col(sv_c), 255);
2804: end if;
2805: end if;
2806: if p_dbi_name = 'CAEOY_EMPLOYER_POSTAL_CODE' then
2807: sv_employer_postal_code := p_dbi_value;
2807: sv_employer_postal_code := p_dbi_value;
2808: if p_dbi_value is null then
2809: sv_c := sv_c + 1;
2810: sv_col(sv_c) := p_dbi_short_name;
2811: hr_utility.set_location('Required column '||sv_col(sv_c), 256);
2812: end if;
2813: end if;
2814: if p_dbi_name = 'CAEOY_ACCOUNTING_CONTACT_NAME' and
2815: p_dbi_value is null then
2814: if p_dbi_name = 'CAEOY_ACCOUNTING_CONTACT_NAME' and
2815: p_dbi_value is null then
2816: sv_c := sv_c + 1;
2817: sv_col(sv_c) := p_dbi_short_name;
2818: hr_utility.set_location('Required column '||sv_col(sv_c), 257);
2819: end if;
2820: if p_dbi_name = 'CAEOY_ACCOUNTING_CONTACT_PHONE' and
2821: p_dbi_value is null then
2822: sv_c := sv_c + 1;
2820: if p_dbi_name = 'CAEOY_ACCOUNTING_CONTACT_PHONE' and
2821: p_dbi_value is null then
2822: sv_c := sv_c + 1;
2823: sv_col(sv_c) := p_dbi_short_name;
2824: hr_utility.set_location('Required column '||sv_col(sv_c), 258);
2825: end if;
2826: if p_dbi_name = 'CAEOY_TAXATION_YEAR' and
2827: p_dbi_value is null then
2828: sv_c := sv_c + 1;
2826: if p_dbi_name = 'CAEOY_TAXATION_YEAR' and
2827: p_dbi_value is null then
2828: sv_c := sv_c + 1;
2829: sv_col(sv_c) := p_dbi_short_name;
2830: hr_utility.set_location('Required column '||sv_col(sv_c), 259);
2831: end if;
2832: /*
2833: if p_dbi_name = 'CAEOY_FEDERAL_YOUTH_HIRE_PROGRAM_INDICATOR' and
2834: p_dbi_value is null then
2833: if p_dbi_name = 'CAEOY_FEDERAL_YOUTH_HIRE_PROGRAM_INDICATOR' and
2834: p_dbi_value is null then
2835: sv_c := sv_c + 1;
2836: sv_col(sv_c) := p_dbi_short_name;
2837: hr_utility.set_location('Required column '||sv_col(sv_c), 260);
2838:
2839: end if;
2840: */
2841: if p_dbi_name = 'CAEOY_EMPLOYER_ADDRESS_LINE1' then
3106: sv_qin;
3107:
3108: exit when cur_rl_pay_act%notfound;
3109:
3110: hr_utility.set_location('PACTID ' || to_char(l_payroll_action_id), 510);
3111: hr_utility.set_location('ORG ID ' || to_char(l_org_id), 520);
3112: hr_utility.set_location('BUSI NO. ' || l_business_number, 530);
3113: hr_utility.set_location('PRE NAME ' || l_pre_name, 540);
3114: hr_utility.set_location('QIN ' || sv_qin, 550);
3107:
3108: exit when cur_rl_pay_act%notfound;
3109:
3110: hr_utility.set_location('PACTID ' || to_char(l_payroll_action_id), 510);
3111: hr_utility.set_location('ORG ID ' || to_char(l_org_id), 520);
3112: hr_utility.set_location('BUSI NO. ' || l_business_number, 530);
3113: hr_utility.set_location('PRE NAME ' || l_pre_name, 540);
3114: hr_utility.set_location('QIN ' || sv_qin, 550);
3115:
3108: exit when cur_rl_pay_act%notfound;
3109:
3110: hr_utility.set_location('PACTID ' || to_char(l_payroll_action_id), 510);
3111: hr_utility.set_location('ORG ID ' || to_char(l_org_id), 520);
3112: hr_utility.set_location('BUSI NO. ' || l_business_number, 530);
3113: hr_utility.set_location('PRE NAME ' || l_pre_name, 540);
3114: hr_utility.set_location('QIN ' || sv_qin, 550);
3115:
3116: sv_busi_no := l_business_number;
3109:
3110: hr_utility.set_location('PACTID ' || to_char(l_payroll_action_id), 510);
3111: hr_utility.set_location('ORG ID ' || to_char(l_org_id), 520);
3112: hr_utility.set_location('BUSI NO. ' || l_business_number, 530);
3113: hr_utility.set_location('PRE NAME ' || l_pre_name, 540);
3114: hr_utility.set_location('QIN ' || sv_qin, 550);
3115:
3116: sv_busi_no := l_business_number;
3117: sv_pre_name := l_pre_name;
3110: hr_utility.set_location('PACTID ' || to_char(l_payroll_action_id), 510);
3111: hr_utility.set_location('ORG ID ' || to_char(l_org_id), 520);
3112: hr_utility.set_location('BUSI NO. ' || l_business_number, 530);
3113: hr_utility.set_location('PRE NAME ' || l_pre_name, 540);
3114: hr_utility.set_location('QIN ' || sv_qin, 550);
3115:
3116: sv_busi_no := l_business_number;
3117: sv_pre_name := l_pre_name;
3118:
3122: fetch cur_rl_trans_y_n into l_transmitter_y_n;
3123: close cur_rl_trans_y_n;
3124:
3125: sv_trans_y_n := l_transmitter_y_n;
3126: hr_utility.set_location('Transmitter ? ' || sv_trans_y_n, 550);
3127:
3128: if (sv_report_type <> 'CAEOY_RL1_AMEND_PP') then
3129:
3130: if sv_p_y = 'E' then
3144:
3145: else
3146: open cur_rl_dbi(l_payroll_action_id);
3147:
3148: hr_utility.set_location(' Cursor DBI Before Validation ', 560);
3149: i := 0;
3150: loop
3151: fetch cur_rl_dbi into l_dbi_name,
3152: l_dbi_value,
3158: sv_dbi(i).dbi_value := l_dbi_value;
3159: sv_dbi(i).dbi_short_name := l_dbi_short_name;
3160: end loop;
3161:
3162: hr_utility.set_location(' Cursor DBI After Validation ', 570);
3163: close cur_rl_dbi;
3164:
3165: end if; /* End of RL2 report type validation */
3166:
3196: sv_asg_id := l_assignment_id;
3197:
3198: initialize_static_var('E');
3199:
3200: hr_utility.set_location(' CUR_TAX_UNIT_ID', 587 );
3201:
3202: open cur_rl_tax_unit_id( l_assignment_id );
3203: fetch cur_rl_tax_unit_id into l_tax_unit_id;
3204: close cur_rl_tax_unit_id;
3204: close cur_rl_tax_unit_id;
3205:
3206: sv_gre := l_tax_unit_id;
3207:
3208: hr_utility.set_location(' CUR_DOB', 588 );
3209:
3210: open cur_rl_dob( l_person_id, l_effective_date);
3211: fetch cur_rl_dob into sv_date_of_birth, sv_hire_date;
3212: close cur_rl_dob;
3210: open cur_rl_dob( l_person_id, l_effective_date);
3211: fetch cur_rl_dob into sv_date_of_birth, sv_hire_date;
3212: close cur_rl_dob;
3213:
3214: hr_utility.set_location('CUR_TERMINATE',577);
3215:
3216: open cur_rl_terminate( l_person_id);
3217: fetch cur_rl_terminate into sv_terminate_date;
3218: close cur_rl_terminate;
3216: open cur_rl_terminate( l_person_id);
3217: fetch cur_rl_terminate into sv_terminate_date;
3218: close cur_rl_terminate;
3219:
3220: hr_utility.set_location('CUR_QPP_BLOCK',566);
3221:
3222: open cur_rl_qpp_block(fp_b_g_id, l_person_id, l_effective_date);
3223: fetch cur_rl_qpp_block into sv_cpp_block;
3224: close cur_rl_qpp_block;
3222: open cur_rl_qpp_block(fp_b_g_id, l_person_id, l_effective_date);
3223: fetch cur_rl_qpp_block into sv_cpp_block;
3224: close cur_rl_qpp_block;
3225:
3226: hr_utility.set_location('CUR_PPIP_BLOCK',566);
3227:
3228: open cur_rl_qpip_block(fp_b_g_id, l_person_id, l_effective_date);
3229: fetch cur_rl_qpip_block into sv_ppip_block;
3230: close cur_rl_qpip_block;
3228: open cur_rl_qpip_block(fp_b_g_id, l_person_id, l_effective_date);
3229: fetch cur_rl_qpip_block into sv_ppip_block;
3230: close cur_rl_qpip_block;
3231:
3232: hr_utility.set_location('CUR_DBI',555);
3233:
3234: if sv_report_type = 'RL2' then
3235:
3236: open cur_rl2_employee(fp_b_g_id,
3245:
3246: else
3247:
3248: open cur_rl_dbi(l_assignment_action_id);
3249: hr_utility.set_location(' Cursor Assignment actions '||to_char(l_assignment_action_id), 199);
3250: i := 0;
3251: loop
3252: fetch cur_rl_dbi into l_dbi_name,
3253: l_dbi_value,
3542: sv_report_type;
3543:
3544: exit when cur_pay_act%notfound;
3545:
3546: hr_utility.set_location('PACTID ' || to_char(l_payroll_action_id), 110);
3547: hr_utility.set_location('ORG ID ' || to_char(l_org_id), 120);
3548: hr_utility.set_location('BUSI NO. ' || l_business_number, 130);
3549: hr_utility.set_location('GRE NAME ' || l_gre_name, 140);
3550:
3543:
3544: exit when cur_pay_act%notfound;
3545:
3546: hr_utility.set_location('PACTID ' || to_char(l_payroll_action_id), 110);
3547: hr_utility.set_location('ORG ID ' || to_char(l_org_id), 120);
3548: hr_utility.set_location('BUSI NO. ' || l_business_number, 130);
3549: hr_utility.set_location('GRE NAME ' || l_gre_name, 140);
3550:
3551: sv_busi_no := l_business_number;
3544: exit when cur_pay_act%notfound;
3545:
3546: hr_utility.set_location('PACTID ' || to_char(l_payroll_action_id), 110);
3547: hr_utility.set_location('ORG ID ' || to_char(l_org_id), 120);
3548: hr_utility.set_location('BUSI NO. ' || l_business_number, 130);
3549: hr_utility.set_location('GRE NAME ' || l_gre_name, 140);
3550:
3551: sv_busi_no := l_business_number;
3552: sv_gre_name := l_gre_name;
3545:
3546: hr_utility.set_location('PACTID ' || to_char(l_payroll_action_id), 110);
3547: hr_utility.set_location('ORG ID ' || to_char(l_org_id), 120);
3548: hr_utility.set_location('BUSI NO. ' || l_business_number, 130);
3549: hr_utility.set_location('GRE NAME ' || l_gre_name, 140);
3550:
3551: sv_busi_no := l_business_number;
3552: sv_gre_name := l_gre_name;
3553: sv_gre := l_org_id;
3558: fetch cur_trans_y_n into l_transmitter_y_n;
3559: close cur_trans_y_n;
3560:
3561: sv_trans_y_n := l_transmitter_y_n;
3562: hr_utility.set_location('Transmitter ? ' || sv_trans_y_n, 150);
3563:
3564: if ((sv_report_type <> 'CAEOY_T4_AMEND_PP') and
3565: (sv_report_type <> 'CAEOY_T4A_AMEND_PP')) then
3566:
3568:
3569: initialize_static_var('R');
3570: open cur_dbi(l_payroll_action_id);
3571:
3572: hr_utility.set_location(' Cursor DBI Before Validation ', 160);
3573: loop
3574:
3575: fetch cur_dbi into l_dbi_name,
3576: l_dbi_value,
3581: fed_employer_validation(l_dbi_name,l_dbi_value,l_dbi_short_name);
3582:
3583: end loop;
3584:
3585: hr_utility.set_location(' Cursor DBI After Validation ', 170);
3586: close cur_dbi;
3587: employer_header;
3588: print_employer;
3589: end if;
3616: sv_asg_id := l_assignment_id;
3617:
3618: initialize_static_var('E');
3619:
3620: hr_utility.set_location(' CUR_DOB', 188 );
3621:
3622: open cur_dob( l_person_id, l_effective_date);
3623: fetch cur_dob into sv_date_of_birth, sv_hire_date;
3624: close cur_dob;
3622: open cur_dob( l_person_id, l_effective_date);
3623: fetch cur_dob into sv_date_of_birth, sv_hire_date;
3624: close cur_dob;
3625:
3626: hr_utility.set_location('CUR_TERMINATE',177);
3627:
3628: open cur_terminate( l_person_id);
3629: fetch cur_terminate into sv_terminate_date;
3630: close cur_terminate;
3628: open cur_terminate( l_person_id);
3629: fetch cur_terminate into sv_terminate_date;
3630: close cur_terminate;
3631:
3632: hr_utility.set_location('CUR_CPP_BLOCK',166);
3633:
3634: open cur_cpp_block( fp_b_g_id, l_person_id, l_effective_date);
3635: fetch cur_cpp_block into sv_cpp_block;
3636: close cur_cpp_block;
3634: open cur_cpp_block( fp_b_g_id, l_person_id, l_effective_date);
3635: fetch cur_cpp_block into sv_cpp_block;
3636: close cur_cpp_block;
3637:
3638: hr_utility.set_location('CUR_EI_BLOCK',156);
3639:
3640: open cur_ei_block( fp_b_g_id, l_person_id, l_effective_date);
3641: fetch cur_ei_block into sv_ei_block;
3642: close cur_ei_block;
3640: open cur_ei_block( fp_b_g_id, l_person_id, l_effective_date);
3641: fetch cur_ei_block into sv_ei_block;
3642: close cur_ei_block;
3643:
3644: hr_utility.set_location('JURISDICTION_CODE',157);
3645:
3646: open get_jurisdiction_code(l_assignment_action_id);
3647: fetch get_jurisdiction_code into sv_jurisdiction;
3648: close get_jurisdiction_code;
3646: open get_jurisdiction_code(l_assignment_action_id);
3647: fetch get_jurisdiction_code into sv_jurisdiction;
3648: close get_jurisdiction_code;
3649:
3650: hr_utility.set_location('CUR_DBI',159);
3651:
3652: open cur_dbi(l_assignment_action_id);
3653: hr_utility.set_location(' Cursor Assignment actions '||
3654: to_char(l_assignment_action_id), 199);
3649:
3650: hr_utility.set_location('CUR_DBI',159);
3651:
3652: open cur_dbi(l_assignment_action_id);
3653: hr_utility.set_location(' Cursor Assignment actions '||
3654: to_char(l_assignment_action_id), 199);
3655: i := 0;
3656: loop
3657: fetch cur_dbi into l_dbi_name,
3734: lv_meaning varchar2(80);
3735: lv_description varchar2(240);
3736: i number := 0;
3737: BEGIN
3738: -- hr_utility.trace_on(null,'VRP');
3739: hr_utility.set_location(gv_package_name || '.pier_yeer', 10);
3740: hr_utility.set_location('Reporting Year ' || p_reporting_year, 20);
3741: hr_utility.set_location('Report Name ' || p_pier_yeer, 30);
3742: hr_utility.set_location('Fed/Prov ' || p_fed_prov, 40);
3735: lv_description varchar2(240);
3736: i number := 0;
3737: BEGIN
3738: -- hr_utility.trace_on(null,'VRP');
3739: hr_utility.set_location(gv_package_name || '.pier_yeer', 10);
3740: hr_utility.set_location('Reporting Year ' || p_reporting_year, 20);
3741: hr_utility.set_location('Report Name ' || p_pier_yeer, 30);
3742: hr_utility.set_location('Fed/Prov ' || p_fed_prov, 40);
3743: hr_utility.set_location('GRE ' || to_char(p_gre), 50);
3736: i number := 0;
3737: BEGIN
3738: -- hr_utility.trace_on(null,'VRP');
3739: hr_utility.set_location(gv_package_name || '.pier_yeer', 10);
3740: hr_utility.set_location('Reporting Year ' || p_reporting_year, 20);
3741: hr_utility.set_location('Report Name ' || p_pier_yeer, 30);
3742: hr_utility.set_location('Fed/Prov ' || p_fed_prov, 40);
3743: hr_utility.set_location('GRE ' || to_char(p_gre), 50);
3744: hr_utility.set_location('PRE ' || to_char(p_pre), 60);
3737: BEGIN
3738: -- hr_utility.trace_on(null,'VRP');
3739: hr_utility.set_location(gv_package_name || '.pier_yeer', 10);
3740: hr_utility.set_location('Reporting Year ' || p_reporting_year, 20);
3741: hr_utility.set_location('Report Name ' || p_pier_yeer, 30);
3742: hr_utility.set_location('Fed/Prov ' || p_fed_prov, 40);
3743: hr_utility.set_location('GRE ' || to_char(p_gre), 50);
3744: hr_utility.set_location('PRE ' || to_char(p_pre), 60);
3745: hr_utility.set_location('BGID ' || to_char(p_b_g_id), 70);
3738: -- hr_utility.trace_on(null,'VRP');
3739: hr_utility.set_location(gv_package_name || '.pier_yeer', 10);
3740: hr_utility.set_location('Reporting Year ' || p_reporting_year, 20);
3741: hr_utility.set_location('Report Name ' || p_pier_yeer, 30);
3742: hr_utility.set_location('Fed/Prov ' || p_fed_prov, 40);
3743: hr_utility.set_location('GRE ' || to_char(p_gre), 50);
3744: hr_utility.set_location('PRE ' || to_char(p_pre), 60);
3745: hr_utility.set_location('BGID ' || to_char(p_b_g_id), 70);
3746:
3739: hr_utility.set_location(gv_package_name || '.pier_yeer', 10);
3740: hr_utility.set_location('Reporting Year ' || p_reporting_year, 20);
3741: hr_utility.set_location('Report Name ' || p_pier_yeer, 30);
3742: hr_utility.set_location('Fed/Prov ' || p_fed_prov, 40);
3743: hr_utility.set_location('GRE ' || to_char(p_gre), 50);
3744: hr_utility.set_location('PRE ' || to_char(p_pre), 60);
3745: hr_utility.set_location('BGID ' || to_char(p_b_g_id), 70);
3746:
3747: open cur_lkup;
3740: hr_utility.set_location('Reporting Year ' || p_reporting_year, 20);
3741: hr_utility.set_location('Report Name ' || p_pier_yeer, 30);
3742: hr_utility.set_location('Fed/Prov ' || p_fed_prov, 40);
3743: hr_utility.set_location('GRE ' || to_char(p_gre), 50);
3744: hr_utility.set_location('PRE ' || to_char(p_pre), 60);
3745: hr_utility.set_location('BGID ' || to_char(p_b_g_id), 70);
3746:
3747: open cur_lkup;
3748: loop
3741: hr_utility.set_location('Report Name ' || p_pier_yeer, 30);
3742: hr_utility.set_location('Fed/Prov ' || p_fed_prov, 40);
3743: hr_utility.set_location('GRE ' || to_char(p_gre), 50);
3744: hr_utility.set_location('PRE ' || to_char(p_pre), 60);
3745: hr_utility.set_location('BGID ' || to_char(p_b_g_id), 70);
3746:
3747: open cur_lkup;
3748: loop
3749: fetch cur_lkup into lv_lookup_code,
3757: end loop;
3758: close cur_lkup;
3759:
3760: select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') into sv_date from dual;
3761: hr_utility.set_location('DATE ' || sv_date, 80);
3762:
3763: /* Report Parameters print first using static_header procedure */
3764:
3765: sv_gre := p_gre;
3791: else
3792: sv_ei_max_exempt := ( ( sv_ei_max_earn ) * sv_ei_rate / 100 );
3793: end if;
3794: */
3795: hr_utility.set_location('Report: ' || p_pier_yeer, 85);
3796:
3797: if ( p_pier_yeer = 'P' ) then
3798: if ( p_fed_prov = 'F' ) then
3799: sv_pier_yeer := get_lookup_meaning('L_PIER');
3803: else
3804: sv_pier_yeer := get_lookup_meaning('L_YEER');
3805: end if;
3806:
3807: hr_utility.set_location('Report: ' || sv_pier_yeer, 90);
3808:
3809:
3810: if p_fed_prov = 'P' then
3811: sv_fed_prov := get_lookup_meaning('L_PROV');
3811: sv_fed_prov := get_lookup_meaning('L_PROV');
3812: else
3813: sv_fed_prov := get_lookup_meaning('L_FED');
3814: end if;
3815: hr_utility.set_location('FEd/Prov: ' || sv_fed_prov, 100);
3816:
3817: if ( p_gre is not null ) then
3818: begin
3819: /* Used to print GRE name as report parameter. */
3829: exception
3830: when others then
3831: null;
3832: end;
3833: hr_utility.set_location('GRE ' || sv_gre_name, 110);
3834: end if;
3835:
3836: if ( p_pre is not null ) then
3837: begin
3851: exception
3852: when others then
3853: null;
3854: end;
3855: hr_utility.set_location('PRE ' || sv_pre_name, 120);
3856: hr_utility.set_location('QIN ' || sv_qin, 120);
3857: end if;
3858:
3859: begin
3852: when others then
3853: null;
3854: end;
3855: hr_utility.set_location('PRE ' || sv_pre_name, 120);
3856: hr_utility.set_location('QIN ' || sv_qin, 120);
3857: end if;
3858:
3859: begin
3860: