[Home] [Help]
96:
97:
98: begin
99:
100: l_payroll_installed := hr_utility.chk_product_install(p_product =>'Oracle Payroll',
101: p_legislation => 'US');
102: if l_payroll_installed then
103:
104: hr_utility.set_location('pay_us_emp_dt_tax_rules.maintain_element_entry'
100: l_payroll_installed := hr_utility.chk_product_install(p_product =>'Oracle Payroll',
101: p_legislation => 'US');
102: if l_payroll_installed then
103:
104: hr_utility.set_location('pay_us_emp_dt_tax_rules.maintain_element_entry'
105: ,1);
106:
107: open csr_tax_element;
108:
127: end loop;
128:
129: close csr_tax_element;
130:
131: hr_utility.set_location('pay_us_emp_dt_tax_rules.maintain_element_entry'
132: ,2);
133:
134: /* Check that all of the input value id for vertex, exists */
135:
144: end if;
145:
146: end loop;
147:
148: hr_utility.set_location('pay_us_emp_dt_tax_rules.maintain_element_entry'
149: ,3);
150:
151: /* Get element link */
152: l_element_link_id := hr_entry_api.get_link(
160: fnd_message.set_token('1','VERTEX');
161: fnd_message.raise_error;
162: end if;
163:
164: hr_utility.set_location('pay_us_emp_dt_tax_rules.maintain_element_entry'
165: ,4);
166:
167: /* Store screen entry value in the table */
168:
180: if p_mode = 'INSERT' then
181:
182: /* Create the vertex element entry */
183:
184: hr_utility.set_location('pay_us_emp_dt_tax_rules.maintain_element_entry' ,5);
185:
186: hr_entry_api.insert_element_entry( P_effective_start_date => l_effective_start_date,
187: P_effective_end_date => l_effective_end_date,
188: P_element_entry_id => l_element_entry_id,
193: P_num_entry_values => 3,
194: P_input_value_id_tbl => l_inp_value_id_table,
195: P_entry_value_tbl => l_scr_value_table);
196:
197: hr_utility.set_location('pay_us_emp_dt_tax_rules.maintain_element_entry' ,6);
198:
199: elsif p_mode in ('CORRECTION','UPDATE', 'UPDATE_CHANGE_INSERT','UPDATE_OVERRIDE','ZAP','DELETE_NEXT_CHANGE','FUTURE_CHANGE','INSERT_OLD') then
200:
201: /* Get the element entry of the vertex element entry that is to be updated
200:
201: /* Get the element entry of the vertex element entry that is to be updated
202: or deleted */
203:
204: hr_utility.set_location('pay_us_emp_dt_tax_rules.maintain_element_entry' ,7);
205:
206:
207: open csr_ele_entry(l_element_link_id, l_inp_value_id_table(2));
208:
366: and pee.assignment_id = p_assignment_id;
367:
368: begin
369:
370: l_payroll_installed := hr_utility.chk_product_install(p_product =>'Oracle Payroll',
371: p_legislation => 'US');
372: if l_payroll_installed then
373:
374: hr_utility.set_location('pay_us_emp_dt_tax_rules.maintain_wc_ele_entry'
370: l_payroll_installed := hr_utility.chk_product_install(p_product =>'Oracle Payroll',
371: p_legislation => 'US');
372: if l_payroll_installed then
373:
374: hr_utility.set_location('pay_us_emp_dt_tax_rules.maintain_wc_ele_entry'
375: ,1);
376:
377: open csr_wc_tax_element;
378:
397: end loop;
398:
399: close csr_wc_tax_element;
400:
401: hr_utility.set_location('pay_us_emp_dt_tax_rules.maintain_wc_ele_entry'
402: ,2);
403:
404: /* Check that all of the input value id for vertex, exists */
405:
414: end if;
415:
416: end loop;
417:
418: hr_utility.set_location('pay_us_emp_dt_tax_rules.maintain_wc_ele_entry'
419: ,3);
420:
421: /* Get element link */
422: l_element_link_id := hr_entry_api.get_link(
431: fnd_message.raise_error;
432:
433: end if;
434:
435: hr_utility.set_location('pay_us_emp_dt_tax_rules.maintain_wc_ele_entry'
436: ,4);
437:
438: /* Store screen entry value in the table */
439:
451: then
452:
453: /* Insert the worker's compensation element entry */
454:
455: hr_utility.set_location(
456: 'pay_us_emp_dt_tax_rules.maintain_wc_ele_entry' ,5);
457: hr_entry_api.insert_element_entry(
458: P_effective_start_date => l_effective_start_date,
459: P_effective_end_date => l_effective_end_date,
465: P_num_entry_values => 2,
466: P_input_value_id_tbl => l_inp_value_id_table,
467: P_entry_value_tbl => l_scr_value_table);
468:
469: hr_utility.set_location(
470: 'pay_us_emp_dt_tax_rules.maintain_wc_ele_entry' ,8);
471:
472: elsif p_mode in ('CORRECTION', 'UPDATE', 'UPDATE_CHANGE_INSERT','UPDATE_OVERRIDE','ZAP')then
473:
887:
888: begin
889:
890:
891: hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_fed_tax_row'||
892: ' - Opening cursor', 1);
893:
894: if p_mode = 'INSERT' then
895:
894: if p_mode = 'INSERT' then
895:
896: open csr_fed_tax_rule_id;
897:
898: hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_fed_tax_row'||
899: ' - Fetching cursor', 2);
900:
901: fetch csr_fed_tax_rule_id into p_emp_fed_tax_rule_id;
902:
899: ' - Fetching cursor', 2);
900:
901: fetch csr_fed_tax_rule_id into p_emp_fed_tax_rule_id;
902:
903: hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_fed_tax_row'||
904: ' - Closing cursor', 3);
905:
906: close csr_fed_tax_rule_id;
907:
905:
906: close csr_fed_tax_rule_id;
907:
908:
909: hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_fed_tax_row'||
910: ' - inserting row', 4);
911:
912: end if;
913:
1112:
1113: /* create workers compensation element entry for the sui state in
1114: the federal record */
1115:
1116: hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_fed_tax_row'||
1117: ' - inserting row', 5);
1118: l_step := 4;
1119:
1120: if hr_utility.chk_product_install(p_product =>'Oracle Payroll',
1116: hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_fed_tax_row'||
1117: ' - inserting row', 5);
1118: l_step := 4;
1119:
1120: if hr_utility.chk_product_install(p_product =>'Oracle Payroll',
1121: p_legislation => 'US') then
1122:
1123: maintain_wc_ele_entry (p_assignment_id => p_assignment_id,
1124: p_effective_start_date => p_effective_start_date,
1256:
1257: begin
1258:
1259:
1260: hr_utility.set_location('pay_us_emp_dt_tax_rules.ins_st_tax_row'||
1261: ' - Opening cursor', 1);
1262:
1263: open csr_state_tax_rule_id;
1264:
1261: ' - Opening cursor', 1);
1262:
1263: open csr_state_tax_rule_id;
1264:
1265: hr_utility.set_location('pay_us_emp_dt_tax_rules.ins_st_tax_row'||
1266: ' - Fetching cursor', 2);
1267:
1268: fetch csr_state_tax_rule_id into p_emp_state_tax_rule_id;
1269:
1266: ' - Fetching cursor', 2);
1267:
1268: fetch csr_state_tax_rule_id into p_emp_state_tax_rule_id;
1269:
1270: hr_utility.set_location('pay_us_emp_dt_tax_rules.ins_st_tax_row'||
1271: ' - Closing cursor', 3);
1272:
1273: close csr_state_tax_rule_id;
1274:
1272:
1273: close csr_state_tax_rule_id;
1274:
1275:
1276: hr_utility.set_location('pay_us_emp_dt_tax_rules.ins_st_tax_row'||
1277: ' - inserting row', 4);
1278:
1279: insert into PAY_US_EMP_STATE_TAX_RULES_F
1280: (emp_state_tax_rule_id,
1473: end if;
1474:
1475: close csr_get_row_id;
1476:
1477: hr_utility.set_location('pay_us_emp_dt_tax_rules.ins_st_tax_row'||
1478: ' - creating %age record ', 5);
1479:
1480: /* Get the changes in location of an assignment.
1481: For each change in location, create vertex element entry with
1480: /* Get the changes in location of an assignment.
1481: For each change in location, create vertex element entry with
1482: time in state as 0% - if Payroll is installed */
1483:
1484: if hr_utility.chk_product_install(p_product =>'Oracle Payroll',
1485: p_legislation => 'US') then
1486:
1487: create_tax_percentage (p_assignment_id => p_assignment_id,
1488: p_state_code => p_state_code,
1617: p_mode => 'I',
1618: p_rowid => null);
1619: end if;
1620:
1621: hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_county_tax_row'||
1622: ' - Opening cursor', 1);
1623:
1624: open csr_county_tax_rule_id;
1625:
1622: ' - Opening cursor', 1);
1623:
1624: open csr_county_tax_rule_id;
1625:
1626: hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_county_tax_row'||
1627: ' - Fetching cursor', 2);
1628:
1629: fetch csr_county_tax_rule_id into p_emp_county_tax_rule_id;
1630:
1627: ' - Fetching cursor', 2);
1628:
1629: fetch csr_county_tax_rule_id into p_emp_county_tax_rule_id;
1630:
1631: hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_county_tax_row'||
1632: ' - Closing cursor', 3);
1633:
1634: close csr_county_tax_rule_id;
1635:
1633:
1634: close csr_county_tax_rule_id;
1635:
1636:
1637: hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_county_tax_row'||
1638: ' - inserting row', 4);
1639:
1640:
1641: insert into pay_us_emp_county_tax_rules_f
1823: end if;
1824:
1825: close csr_get_row_id;
1826:
1827: hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_county_tax_row'||
1828: ' - creating %age record ', 5);
1829:
1830: /* Get the changes in location of an assignment.
1831: For each change in location, create vertex element entry with
1830: /* Get the changes in location of an assignment.
1831: For each change in location, create vertex element entry with
1832: time in county as 0% */
1833:
1834: if hr_utility.chk_product_install(p_product =>'Oracle Payroll',
1835: p_legislation => 'US') then
1836:
1837: create_tax_percentage (p_assignment_id => p_assignment_id,
1838: p_state_code => p_state_code,
1976: p_mode => 'I',
1977: p_rowid => null);
1978: end if;
1979:
1980: hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_city_tax_row'||
1981: ' - Opening cursor', 1);
1982:
1983: open csr_city_tax_rule_id;
1984:
1981: ' - Opening cursor', 1);
1982:
1983: open csr_city_tax_rule_id;
1984:
1985: hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_city_tax_row'||
1986: ' - Fetching cursor', 2);
1987:
1988: fetch csr_city_tax_rule_id into p_emp_city_tax_rule_id;
1989:
1986: ' - Fetching cursor', 2);
1987:
1988: fetch csr_city_tax_rule_id into p_emp_city_tax_rule_id;
1989:
1990: hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_city_tax_row'||
1991: ' - Closing cursor', 3);
1992:
1993: close csr_city_tax_rule_id;
1994:
1992:
1993: close csr_city_tax_rule_id;
1994:
1995:
1996: hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_city_tax_row'||
1997: ' - inserting row', 4);
1998:
1999: insert into PAY_US_EMP_CITY_TAX_RULES_F
2000: (emp_city_tax_rule_id,
2186:
2187: close csr_get_row_id;
2188:
2189:
2190: hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_city_tax_row'||
2191: ' - creating %age record ', 5);
2192:
2193: /* Get the changes in location of an assignment.
2194: For each change in location, create vertex element entry with
2198: thus resulting in only one city tax record with 100% - makes sense??
2199: In most of the cases the defaulting routine will create tax records
2200: from begin of time till end of time */
2201:
2202: if hr_utility.chk_product_install(p_product =>'Oracle Payroll',
2203: p_legislation => 'US') then
2204:
2205: create_tax_percentage (p_assignment_id => p_assignment_id,
2206: p_state_code => p_state_code,
2327:
2328: begin
2329:
2330:
2331: hr_utility.set_location('pay_us_emp_dt_tax_rules.update_fed_tax_row'||
2332: ' - updating row', 1);
2333: ----added by vaprakas Bug 5607135
2334: check_nra_status(p_assignment_id
2335: , p_withholding_allowances
2542: p_sta_information29 in varchar2,
2543: p_sta_information30 in varchar2) is
2544: begin
2545:
2546: hr_utility.set_location('pay_us_emp_dt_tax_rules.ins_st_tax_row'||
2547: ' - updating row', 1);
2548:
2549: update PAY_US_EMP_STATE_TAX_RULES_F
2550: set emp_state_tax_rule_id = p_emp_state_tax_rule_id,
2643: fnd_message.set_token('STEP','1');
2644: fnd_message.raise_error;
2645: end if;
2646:
2647: hr_utility.set_location('pay_us_emp_dt_tax_rules.ins_st_tax_row'||
2648: ' - updated row', 2);
2649:
2650: end update_state_tax_row;
2651:
2740:
2741: if p_school_district_code is not null
2742: then
2743:
2744: hr_utility.set_location('pay_us_emp_dt_tax_rules.update_county_tax_row'||
2745: ' - checking sd', 1);
2746: /* Check that the school district is assigned to only one county/city
2747: at a given point in time */
2748:
2753: p_mode => 'U',
2754: p_rowid => p_row_id);
2755: end if;
2756:
2757: hr_utility.set_location('pay_us_emp_dt_tax_rules.update_county_tax_row'||
2758: ' - updating row', 2);
2759:
2760: update PAY_US_EMP_COUNTY_TAX_RULES_F
2761: set emp_county_tax_rule_id = p_emp_county_tax_rule_id,
2852: if p_school_district_code is not null then
2853:
2854: /* Insert row into the pay_us_asg_reporting table */
2855:
2856: hr_utility.set_location('pay_us_emp_dt_tax_rules.update_county_tax_row'||
2857: ' - asg_geo row', 3);
2858:
2859: pay_asg_geo_pkg.create_asg_geo_row(P_assignment_id => p_assignment_id,
2860: P_jurisdiction => p_state_code || '-'||
2956:
2957: if p_school_district_code is not null
2958: then
2959:
2960: hr_utility.set_location('pay_us_emp_dt_tax_rules.update_city_tax_row'||
2961: ' - checking sd', 1);
2962:
2963: /* Check that the school district is assigned to only one county/city
2964: at a given point in time */
2970: p_mode => 'U',
2971: p_rowid => p_row_id);
2972: end if;
2973:
2974: hr_utility.set_location('pay_us_emp_dt_tax_rules.update_city_tax_row'||
2975: ' - updating row', 2);
2976:
2977: update PAY_US_EMP_CITY_TAX_RULES_F
2978: set emp_city_tax_rule_id = p_emp_city_tax_rule_id,
3072: if p_school_district_code is not null then
3073:
3074: /* Insert row into the pay_us_asg_reporting table */
3075:
3076: hr_utility.set_location('pay_us_emp_dt_tax_rules.update_city_tax_row'||
3077: ' - asg_geo row', 3);
3078:
3079: pay_asg_geo_pkg.create_asg_geo_row(P_assignment_id => p_assignment_id,
3080: P_jurisdiction => p_state_code || '-' ||
3146: begin
3147:
3148: /* Check if payroll has been installed or not */
3149:
3150: l_payroll_installed := hr_utility.chk_product_install(p_product =>'Oracle Payroll',
3151: p_legislation => 'US');
3152:
3153: /* Now all validations done. Go ahead and delete the element entries.
3154: Once the element entries are deleted, delete the tax rules records */
5295:
5296: begin
5297:
5298: /* Get Filing Status */
5299: hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_def_fed_rec',1);
5300: --dbms_output.put_line('asg** '||to_char(p_assignment_id));
5301: --dbms_output.put_line('sd** '||to_char(p_effective_start_date));
5302: --dbms_output.put_line('ed** '||to_char(p_effective_end_date));
5303: --dbms_output.put_line('sui** '||p_sui_state_code);
5301: --dbms_output.put_line('sd** '||to_char(p_effective_start_date));
5302: --dbms_output.put_line('ed** '||to_char(p_effective_end_date));
5303: --dbms_output.put_line('sui** '||p_sui_state_code);
5304: --dbms_output.put_line('bg** ' || to_char(p_business_group_id));
5305: hr_utility.trace('insert_def_fed_rec** ' || to_char(p_assignment_id));
5306: hr_utility.trace('insert_def_fed_rec** ' || to_char(p_effective_start_date));
5307: hr_utility.trace('insert_def_fed_rec** ' || to_char(p_effective_end_date));
5308: hr_utility.trace('insert_def_fed_rec** ' || p_sui_state_code);
5309: hr_utility.trace('insert_def_fed_rec** ' || to_char(p_business_group_id));
5302: --dbms_output.put_line('ed** '||to_char(p_effective_end_date));
5303: --dbms_output.put_line('sui** '||p_sui_state_code);
5304: --dbms_output.put_line('bg** ' || to_char(p_business_group_id));
5305: hr_utility.trace('insert_def_fed_rec** ' || to_char(p_assignment_id));
5306: hr_utility.trace('insert_def_fed_rec** ' || to_char(p_effective_start_date));
5307: hr_utility.trace('insert_def_fed_rec** ' || to_char(p_effective_end_date));
5308: hr_utility.trace('insert_def_fed_rec** ' || p_sui_state_code);
5309: hr_utility.trace('insert_def_fed_rec** ' || to_char(p_business_group_id));
5310:
5303: --dbms_output.put_line('sui** '||p_sui_state_code);
5304: --dbms_output.put_line('bg** ' || to_char(p_business_group_id));
5305: hr_utility.trace('insert_def_fed_rec** ' || to_char(p_assignment_id));
5306: hr_utility.trace('insert_def_fed_rec** ' || to_char(p_effective_start_date));
5307: hr_utility.trace('insert_def_fed_rec** ' || to_char(p_effective_end_date));
5308: hr_utility.trace('insert_def_fed_rec** ' || p_sui_state_code);
5309: hr_utility.trace('insert_def_fed_rec** ' || to_char(p_business_group_id));
5310:
5311: open csr_filing_status;
5304: --dbms_output.put_line('bg** ' || to_char(p_business_group_id));
5305: hr_utility.trace('insert_def_fed_rec** ' || to_char(p_assignment_id));
5306: hr_utility.trace('insert_def_fed_rec** ' || to_char(p_effective_start_date));
5307: hr_utility.trace('insert_def_fed_rec** ' || to_char(p_effective_end_date));
5308: hr_utility.trace('insert_def_fed_rec** ' || p_sui_state_code);
5309: hr_utility.trace('insert_def_fed_rec** ' || to_char(p_business_group_id));
5310:
5311: open csr_filing_status;
5312:
5305: hr_utility.trace('insert_def_fed_rec** ' || to_char(p_assignment_id));
5306: hr_utility.trace('insert_def_fed_rec** ' || to_char(p_effective_start_date));
5307: hr_utility.trace('insert_def_fed_rec** ' || to_char(p_effective_end_date));
5308: hr_utility.trace('insert_def_fed_rec** ' || p_sui_state_code);
5309: hr_utility.trace('insert_def_fed_rec** ' || to_char(p_business_group_id));
5310:
5311: open csr_filing_status;
5312:
5313: fetch csr_filing_status into l_filing_status_code;
5320:
5321: close csr_filing_status;
5322:
5323: /* Get EIC Filing Status */
5324: hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_def_fed_rec',2);
5325:
5326: open csr_eic_fstatus;
5327:
5328: fetch csr_eic_fstatus into l_eic_fstatus_code;
5338: close csr_eic_fstatus;
5339:
5340: /* Insert Federal Tax Record */
5341:
5342: hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_def_fed_rec',3);
5343:
5344: l_mode := 'INSERT';
5345:
5346: insert_fed_tax_row(p_emp_fed_tax_rule_id => l_emp_fed_tax_rule_id,
5430: p_fed_information29 => null,
5431: p_fed_information30 => null,
5432: p_mode => 'INSERT');
5433:
5434: hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_def_fed_rec',5);
5435:
5436: return l_emp_fed_tax_rule_id;
5437:
5438: end insert_def_fed_rec;
5517: l_flag varchar2(1) := 'N';
5518:
5519: begin
5520:
5521: hr_utility.set_location('pay_us_emp_dt_tax_rules.ins_def_state',1);
5522:
5523: open chk_state_exists;
5524: fetch chk_state_exists into l_flag;
5525: if chk_state_exists%NOTFOUND then
5526: l_flag := 'N';
5527: end if;
5528: close chk_state_exists;
5529:
5530: hr_utility.set_location('pay_us_emp_dt_tax_rules.ins_def_state',91);
5531: if l_flag = 'N'then
5532: open csr_get_asg_gre(p_assignment_id);
5533: fetch csr_get_asg_gre into ln_asg_tax_unit_id;
5534: close csr_get_asg_gre;
5535:
5536: open csr_fed_or_def(ln_asg_tax_unit_id, p_state_code);
5537: fetch csr_fed_or_def into l_def_pref;
5538:
5539: hr_utility.set_location('pay_us_emp_dt_tax_rules.ins_def_state',2);
5540:
5541: if csr_fed_or_def%NOTFOUND then
5542: l_filing_status_code := '01';
5543: l_allowances := 0;
5543: l_allowances := 0;
5544: end if;
5545: close csr_fed_or_def;
5546:
5547: hr_utility.set_location('pay_us_emp_dt_tax_rules.ins_def_state',3);
5548:
5549:
5550: /* Bug 864068 - Added check for Connecticut (p_state_code = 07) to default
5551: the filing status for new Connecticut State Tax records to '07' instead
5556: the highest rate.
5557: */
5558:
5559: if p_state_code = '07' then
5560: hr_utility.set_location('pay_us_emp_dt_tax_rules.ins_def_state',4);
5561: l_filing_status_code := '07';
5562: l_allowances := 0;
5563: elsif p_state_code = '22' then -- Bug No 4325326
5564: hr_utility.set_location('pay_us_emp_dt_tax_rules.ins_def_state',4.4);
5560: hr_utility.set_location('pay_us_emp_dt_tax_rules.ins_def_state',4);
5561: l_filing_status_code := '07';
5562: l_allowances := 0;
5563: elsif p_state_code = '22' then -- Bug No 4325326
5564: hr_utility.set_location('pay_us_emp_dt_tax_rules.ins_def_state',4.4);
5565: l_filing_status_code := '04';
5566: l_allowances := 0;
5567: elsif l_def_pref = 'SINGLE_ZERO' or l_def_pref is null then
5568: hr_utility.set_location('pay_us_emp_dt_tax_rules.ins_def_state',5);
5564: hr_utility.set_location('pay_us_emp_dt_tax_rules.ins_def_state',4.4);
5565: l_filing_status_code := '04';
5566: l_allowances := 0;
5567: elsif l_def_pref = 'SINGLE_ZERO' or l_def_pref is null then
5568: hr_utility.set_location('pay_us_emp_dt_tax_rules.ins_def_state',5);
5569: l_filing_status_code := '01';
5570: l_allowances := 0;
5571: elsif l_def_pref = 'FED_DEF' then
5572: hr_utility.set_location('pay_us_emp_dt_tax_rules.ins_def_state',6);
5568: hr_utility.set_location('pay_us_emp_dt_tax_rules.ins_def_state',5);
5569: l_filing_status_code := '01';
5570: l_allowances := 0;
5571: elsif l_def_pref = 'FED_DEF' then
5572: hr_utility.set_location('pay_us_emp_dt_tax_rules.ins_def_state',6);
5573: open csr_filing_status(p_assignment_id, p_state_code);
5574: fetch csr_filing_status into l_filing_status_code, l_allowances;
5575: if csr_filing_status%NOTFOUND then
5576: hr_utility.set_location('pay_us_emp_dt_tax_rules.ins_def_state',7);
5572: hr_utility.set_location('pay_us_emp_dt_tax_rules.ins_def_state',6);
5573: open csr_filing_status(p_assignment_id, p_state_code);
5574: fetch csr_filing_status into l_filing_status_code, l_allowances;
5575: if csr_filing_status%NOTFOUND then
5576: hr_utility.set_location('pay_us_emp_dt_tax_rules.ins_def_state',7);
5577: l_filing_status_code := '01';
5578: l_allowances := 0;
5579: end if;
5580: close csr_filing_status;
5582: end if;
5583:
5584: /* Insert State Tax record */
5585:
5586: hr_utility.set_location('pay_us_emp_dt_tax_rules.ins_def_state',8);
5587:
5588: insert_state_tax_row ( p_row_id => l_row_id,
5589: p_emp_state_tax_rule_id => l_emp_state_tax_rule_id,
5590: p_effective_start_date => p_effective_start_date,
5674: p_sta_information28 => null,
5675: p_sta_information29 => null,
5676: p_sta_information30 => null );
5677:
5678: hr_utility.set_location('pay_us_emp_dt_tax_rules.ins_def_state',9);
5679:
5680: end if;
5681:
5682: hr_utility.set_location('pay_us_emp_dt_tax_rules.ins_def_state',10);
5678: hr_utility.set_location('pay_us_emp_dt_tax_rules.ins_def_state',9);
5679:
5680: end if;
5681:
5682: hr_utility.set_location('pay_us_emp_dt_tax_rules.ins_def_state',10);
5683:
5684: return l_emp_state_tax_rule_id;
5685:
5686: end insert_def_state_rec;
5727: l_flag varchar2(1) := 'N';
5728:
5729: begin
5730:
5731: hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_def_county_rec',1);
5732:
5733: open chk_county_exists;
5734: fetch chk_county_exists into l_flag;
5735: if chk_county_exists%NOTFOUND then
5736: l_flag := 'N';
5737: end if;
5738: close chk_county_exists;
5739:
5740: hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_def_county_rec',91);
5741:
5742: if l_flag = 'N' then
5743:
5744: open csr_filing_status;
5744: open csr_filing_status;
5745:
5746: fetch csr_filing_status into l_filing_status_code;
5747:
5748: hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_def_county_rec',2);
5749:
5750: if csr_filing_status%NOTFOUND then
5751:
5752: fnd_message.set_name('PAY','HR_6091_DEF_MISSING_LOOKUPS');
5758: close csr_filing_status;
5759:
5760: /* Insert County Tax record */
5761:
5762: hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_def_county_rec',3);
5763:
5764:
5765: insert_county_tax_row(p_row_id => l_row_id,
5766: p_emp_county_tax_rule_id => l_emp_county_tax_rule_id,
5846: p_cnt_information28 => null,
5847: p_cnt_information29 => null,
5848: p_cnt_information30 => null );
5849:
5850: hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_def_county_rec',4);
5851: end if;
5852: hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_def_county_rec',5);
5853:
5854: return l_emp_county_tax_rule_id;
5848: p_cnt_information30 => null );
5849:
5850: hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_def_county_rec',4);
5851: end if;
5852: hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_def_county_rec',5);
5853:
5854: return l_emp_county_tax_rule_id;
5855:
5856: end insert_def_county_rec;
5900:
5901:
5902: begin
5903:
5904: hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_def_city_rec',1);
5905:
5906: open chk_city_exists;
5907: fetch chk_city_exists into l_flag;
5908: if chk_city_exists%NOTFOUND then
5909: l_flag := 'N';
5910: end if;
5911: close chk_city_exists;
5912:
5913: hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_def_city_rec',91);
5914:
5915: if l_flag = 'N' then
5916:
5917: open csr_filing_status;
5917: open csr_filing_status;
5918:
5919: fetch csr_filing_status into l_filing_status_code;
5920:
5921: hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_def_city_rec',2);
5922:
5923: if csr_filing_status%NOTFOUND then
5924:
5925: fnd_message.set_name('PAY','HR_6091_DEF_MISSING_LOOKUPS');
5931: close csr_filing_status;
5932:
5933: /* Insert City Tax record */
5934:
5935: hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_def_city_rec',3);
5936:
5937:
5938: insert_city_tax_row(p_row_id => l_row_id,
5939: p_emp_city_tax_rule_id => l_emp_city_tax_rule_id,
6020: p_cty_information28 => null,
6021: p_cty_information29 => null,
6022: p_cty_information30 => null );
6023:
6024: hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_def_city_rec',4);
6025: end if;
6026:
6027: hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_def_city_rec',5);
6028:
6023:
6024: hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_def_city_rec',4);
6025: end if;
6026:
6027: hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_def_city_rec',5);
6028:
6029: return l_emp_city_tax_rule_id;
6030:
6031: end insert_def_city_rec;
6069: -- PAY_ELEMENT_ENTRIES_F
6070:
6071: begin
6072:
6073: hr_utility.set_location('pay_us_emp_dt_tax_rules.zero_out_time',1);
6074:
6075: open csr_get_jurisdiction;
6076:
6077: /* Now loop through all VERTEX element entries
6099: end loop;
6100:
6101: close csr_get_jurisdiction;
6102:
6103: hr_utility.set_location('pay_us_emp_dt_tax_rules.zero_out_time',2);
6104:
6105: end zero_out_time;
6106:
6107: /* Name : set_sui_wage_base_override
6251: state_code_tab state_code_typ ;
6252: type balance_typ is table of number index by BINARY_INTEGER ;
6253: balance_tab balance_typ ;
6254: begin
6255: --hr_utility.trace_on(null,'pyusdtw4') ;
6256:
6257: hr_utility.trace('Entering pay_us_emp_dt_tax_rules.set_sui_wage_base_override.') ;
6258: hr_utility.trace('p_assignment_id := ' || to_char(p_assignment_id)) ;
6259: hr_utility.trace('p_state_code := ' || p_state_code) ;
6253: balance_tab balance_typ ;
6254: begin
6255: --hr_utility.trace_on(null,'pyusdtw4') ;
6256:
6257: hr_utility.trace('Entering pay_us_emp_dt_tax_rules.set_sui_wage_base_override.') ;
6258: hr_utility.trace('p_assignment_id := ' || to_char(p_assignment_id)) ;
6259: hr_utility.trace('p_state_code := ' || p_state_code) ;
6260: hr_utility.trace('p_session_date := ' || to_char(p_session_date)) ;
6261:
6254: begin
6255: --hr_utility.trace_on(null,'pyusdtw4') ;
6256:
6257: hr_utility.trace('Entering pay_us_emp_dt_tax_rules.set_sui_wage_base_override.') ;
6258: hr_utility.trace('p_assignment_id := ' || to_char(p_assignment_id)) ;
6259: hr_utility.trace('p_state_code := ' || p_state_code) ;
6260: hr_utility.trace('p_session_date := ' || to_char(p_session_date)) ;
6261:
6262: l_balance_value := 0 ;
6255: --hr_utility.trace_on(null,'pyusdtw4') ;
6256:
6257: hr_utility.trace('Entering pay_us_emp_dt_tax_rules.set_sui_wage_base_override.') ;
6258: hr_utility.trace('p_assignment_id := ' || to_char(p_assignment_id)) ;
6259: hr_utility.trace('p_state_code := ' || p_state_code) ;
6260: hr_utility.trace('p_session_date := ' || to_char(p_session_date)) ;
6261:
6262: l_balance_value := 0 ;
6263: l_combined_balance_value := 0 ;
6256:
6257: hr_utility.trace('Entering pay_us_emp_dt_tax_rules.set_sui_wage_base_override.') ;
6258: hr_utility.trace('p_assignment_id := ' || to_char(p_assignment_id)) ;
6259: hr_utility.trace('p_state_code := ' || p_state_code) ;
6260: hr_utility.trace('p_session_date := ' || to_char(p_session_date)) ;
6261:
6262: l_balance_value := 0 ;
6263: l_combined_balance_value := 0 ;
6264: l_sui_wg_base := 0 ;
6264: l_sui_wg_base := 0 ;
6265: l_actual_balance_value := 0 ;
6266: l_oth_combined_balance := 0 ;
6267:
6268: hr_utility.trace('Getting Effective Start Date of Latest Active Assignment.') ;
6269: open c_max_asg_eff_st_date(p_assignment_id) ;
6270: fetch c_max_asg_eff_st_date into l_max_asg_eff_st_dt ;
6271: if c_max_asg_eff_st_date%notfound then
6272: close c_max_asg_eff_st_date ;
6269: open c_max_asg_eff_st_date(p_assignment_id) ;
6270: fetch c_max_asg_eff_st_date into l_max_asg_eff_st_dt ;
6271: if c_max_asg_eff_st_date%notfound then
6272: close c_max_asg_eff_st_date ;
6273: raise hr_utility.hr_error ;
6274: end if ;
6275: hr_utility.trace('Effective Start Date of Latest Active Assignment: '|| to_char(l_max_asg_eff_st_dt)) ;
6276:
6277: hr_utility.trace('Getting Effective Date of Latest Payroll Action ID.') ;
6271: if c_max_asg_eff_st_date%notfound then
6272: close c_max_asg_eff_st_date ;
6273: raise hr_utility.hr_error ;
6274: end if ;
6275: hr_utility.trace('Effective Start Date of Latest Active Assignment: '|| to_char(l_max_asg_eff_st_dt)) ;
6276:
6277: hr_utility.trace('Getting Effective Date of Latest Payroll Action ID.') ;
6278: open c_max_pact_dt(p_assignment_id, p_session_date) ;
6279: fetch c_max_pact_dt into l_max_pact_dt ;
6273: raise hr_utility.hr_error ;
6274: end if ;
6275: hr_utility.trace('Effective Start Date of Latest Active Assignment: '|| to_char(l_max_asg_eff_st_dt)) ;
6276:
6277: hr_utility.trace('Getting Effective Date of Latest Payroll Action ID.') ;
6278: open c_max_pact_dt(p_assignment_id, p_session_date) ;
6279: fetch c_max_pact_dt into l_max_pact_dt ;
6280: if c_max_pact_dt%notfound then
6281: close c_max_pact_dt ;
6278: open c_max_pact_dt(p_assignment_id, p_session_date) ;
6279: fetch c_max_pact_dt into l_max_pact_dt ;
6280: if c_max_pact_dt%notfound then
6281: close c_max_pact_dt ;
6282: raise hr_utility.hr_error ;
6283: end if ;
6284: hr_utility.trace('Effective Date of Latest Payroll Action ID: '|| to_char(l_max_pact_dt)) ;
6285:
6286: hr_utility.trace('Getting Defined Balance ID for SUI_ER_TAXABLE_PER_JD_GRE_YTD.') ;
6280: if c_max_pact_dt%notfound then
6281: close c_max_pact_dt ;
6282: raise hr_utility.hr_error ;
6283: end if ;
6284: hr_utility.trace('Effective Date of Latest Payroll Action ID: '|| to_char(l_max_pact_dt)) ;
6285:
6286: hr_utility.trace('Getting Defined Balance ID for SUI_ER_TAXABLE_PER_JD_GRE_YTD.') ;
6287: open c_defined_balance_id('SUI_ER_TAXABLE_PER_JD_GRE_YTD') ;
6288: fetch c_defined_balance_id into l_defined_balance_id ;
6282: raise hr_utility.hr_error ;
6283: end if ;
6284: hr_utility.trace('Effective Date of Latest Payroll Action ID: '|| to_char(l_max_pact_dt)) ;
6285:
6286: hr_utility.trace('Getting Defined Balance ID for SUI_ER_TAXABLE_PER_JD_GRE_YTD.') ;
6287: open c_defined_balance_id('SUI_ER_TAXABLE_PER_JD_GRE_YTD') ;
6288: fetch c_defined_balance_id into l_defined_balance_id ;
6289: if c_defined_balance_id%notfound then
6290: close c_defined_balance_id ;
6287: open c_defined_balance_id('SUI_ER_TAXABLE_PER_JD_GRE_YTD') ;
6288: fetch c_defined_balance_id into l_defined_balance_id ;
6289: if c_defined_balance_id%notfound then
6290: close c_defined_balance_id ;
6291: raise hr_utility.hr_error ;
6292: end if ;
6293: hr_utility.trace('Defined Balance ID for SUI_ER_TAXABLE_PER_JD_GRE_YTD: '|| to_char(l_defined_balance_id)) ;
6294:
6295: hr_utility.trace('Getting Tax Unit ID.') ;
6289: if c_defined_balance_id%notfound then
6290: close c_defined_balance_id ;
6291: raise hr_utility.hr_error ;
6292: end if ;
6293: hr_utility.trace('Defined Balance ID for SUI_ER_TAXABLE_PER_JD_GRE_YTD: '|| to_char(l_defined_balance_id)) ;
6294:
6295: hr_utility.trace('Getting Tax Unit ID.') ;
6296: open c_tax_unit_id(p_assignment_id, p_session_date ) ;
6297: fetch c_tax_unit_id into l_tax_unit_id ;
6291: raise hr_utility.hr_error ;
6292: end if ;
6293: hr_utility.trace('Defined Balance ID for SUI_ER_TAXABLE_PER_JD_GRE_YTD: '|| to_char(l_defined_balance_id)) ;
6294:
6295: hr_utility.trace('Getting Tax Unit ID.') ;
6296: open c_tax_unit_id(p_assignment_id, p_session_date ) ;
6297: fetch c_tax_unit_id into l_tax_unit_id ;
6298: if c_tax_unit_id%notfound then
6299: close c_tax_unit_id ;
6296: open c_tax_unit_id(p_assignment_id, p_session_date ) ;
6297: fetch c_tax_unit_id into l_tax_unit_id ;
6298: if c_tax_unit_id%notfound then
6299: close c_tax_unit_id ;
6300: raise hr_utility.hr_error ;
6301: end if ;
6302: hr_utility.trace('Tax Unit ID: '|| to_char(l_tax_unit_id)) ;
6303:
6304: hr_utility.trace('Getting Person ID.') ;
6298: if c_tax_unit_id%notfound then
6299: close c_tax_unit_id ;
6300: raise hr_utility.hr_error ;
6301: end if ;
6302: hr_utility.trace('Tax Unit ID: '|| to_char(l_tax_unit_id)) ;
6303:
6304: hr_utility.trace('Getting Person ID.') ;
6305: open c_get_person_id(p_assignment_id) ;
6306: fetch c_get_person_id into l_person_id ;
6300: raise hr_utility.hr_error ;
6301: end if ;
6302: hr_utility.trace('Tax Unit ID: '|| to_char(l_tax_unit_id)) ;
6303:
6304: hr_utility.trace('Getting Person ID.') ;
6305: open c_get_person_id(p_assignment_id) ;
6306: fetch c_get_person_id into l_person_id ;
6307: if c_get_person_id%notfound then
6308: close c_get_person_id ;
6305: open c_get_person_id(p_assignment_id) ;
6306: fetch c_get_person_id into l_person_id ;
6307: if c_get_person_id%notfound then
6308: close c_get_person_id ;
6309: raise hr_utility.hr_error ;
6310: end if ;
6311: hr_utility.trace('Person ID: '|| to_char(l_person_id)) ;
6312:
6313: hr_utility.trace('p_state_code := '||p_state_code) ;
6307: if c_get_person_id%notfound then
6308: close c_get_person_id ;
6309: raise hr_utility.hr_error ;
6310: end if ;
6311: hr_utility.trace('Person ID: '|| to_char(l_person_id)) ;
6312:
6313: hr_utility.trace('p_state_code := '||p_state_code) ;
6314: hr_utility.trace('p_assignment_id..Original := '||to_char(p_assignment_id)) ;
6315:
6309: raise hr_utility.hr_error ;
6310: end if ;
6311: hr_utility.trace('Person ID: '|| to_char(l_person_id)) ;
6312:
6313: hr_utility.trace('p_state_code := '||p_state_code) ;
6314: hr_utility.trace('p_assignment_id..Original := '||to_char(p_assignment_id)) ;
6315:
6316: hr_utility.trace('Getting Rehire Y or N Flag.') ;
6317: open c_ckeck_rehire(p_assignment_id, p_session_date) ;
6310: end if ;
6311: hr_utility.trace('Person ID: '|| to_char(l_person_id)) ;
6312:
6313: hr_utility.trace('p_state_code := '||p_state_code) ;
6314: hr_utility.trace('p_assignment_id..Original := '||to_char(p_assignment_id)) ;
6315:
6316: hr_utility.trace('Getting Rehire Y or N Flag.') ;
6317: open c_ckeck_rehire(p_assignment_id, p_session_date) ;
6318: fetch c_ckeck_rehire into l_rehired ;
6312:
6313: hr_utility.trace('p_state_code := '||p_state_code) ;
6314: hr_utility.trace('p_assignment_id..Original := '||to_char(p_assignment_id)) ;
6315:
6316: hr_utility.trace('Getting Rehire Y or N Flag.') ;
6317: open c_ckeck_rehire(p_assignment_id, p_session_date) ;
6318: fetch c_ckeck_rehire into l_rehired ;
6319: if c_ckeck_rehire%notfound then
6320: l_rehired := 'N' ;
6319: if c_ckeck_rehire%notfound then
6320: l_rehired := 'N' ;
6321: close c_ckeck_rehire ;
6322: end if ;
6323: hr_utility.trace('Rehire Flag Value: '|| l_rehired) ;
6324:
6325: IF nvl(l_rehired,'N') = 'Y' THEN
6326: l_count := 1 ;
6327: /* For Rehired Condition, iterating through all the assignments for the concerned person
6327: /* For Rehired Condition, iterating through all the assignments for the concerned person
6328: either active for the current year or was effective from earlier and terminated within the current year */
6329: for i_get_all_assignments in c_get_all_assignments(l_person_id,p_session_date)
6330: loop
6331: hr_utility.trace('l_person_id := '||to_char(l_person_id)) ;
6332: /* For each of the above-mentioned assignment iterating through all the states the employee worked on
6333: and populating PL/SQL table with the SUI ER Taxable Balance value and corresponding State Code */
6334: for i_all_states in c_all_states(i_get_all_assignments.assignment_id,p_session_date)
6335: loop
6337:
6338: state_code_tab(l_count) := i_all_states.state_code ;
6339: l_jurisdiction_code := i_all_states.state_code || '-000-0000' ;
6340:
6341: hr_utility.trace('l_count := '||to_char(l_count)) ;
6342: hr_utility.trace('i_get_all_assignments.assignment_id:= '||to_char(i_get_all_assignments.assignment_id)) ;
6343: hr_utility.trace('l_tax_unit_id := '||to_char(l_tax_unit_id)) ;
6344: hr_utility.trace('l_jurisdiction_code := '||l_jurisdiction_code) ;
6345:
6338: state_code_tab(l_count) := i_all_states.state_code ;
6339: l_jurisdiction_code := i_all_states.state_code || '-000-0000' ;
6340:
6341: hr_utility.trace('l_count := '||to_char(l_count)) ;
6342: hr_utility.trace('i_get_all_assignments.assignment_id:= '||to_char(i_get_all_assignments.assignment_id)) ;
6343: hr_utility.trace('l_tax_unit_id := '||to_char(l_tax_unit_id)) ;
6344: hr_utility.trace('l_jurisdiction_code := '||l_jurisdiction_code) ;
6345:
6346: pay_balance_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id) ;
6339: l_jurisdiction_code := i_all_states.state_code || '-000-0000' ;
6340:
6341: hr_utility.trace('l_count := '||to_char(l_count)) ;
6342: hr_utility.trace('i_get_all_assignments.assignment_id:= '||to_char(i_get_all_assignments.assignment_id)) ;
6343: hr_utility.trace('l_tax_unit_id := '||to_char(l_tax_unit_id)) ;
6344: hr_utility.trace('l_jurisdiction_code := '||l_jurisdiction_code) ;
6345:
6346: pay_balance_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id) ;
6347: pay_balance_pkg.set_context('JURISDICTION_CODE',l_jurisdiction_code) ;
6340:
6341: hr_utility.trace('l_count := '||to_char(l_count)) ;
6342: hr_utility.trace('i_get_all_assignments.assignment_id:= '||to_char(i_get_all_assignments.assignment_id)) ;
6343: hr_utility.trace('l_tax_unit_id := '||to_char(l_tax_unit_id)) ;
6344: hr_utility.trace('l_jurisdiction_code := '||l_jurisdiction_code) ;
6345:
6346: pay_balance_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id) ;
6347: pay_balance_pkg.set_context('JURISDICTION_CODE',l_jurisdiction_code) ;
6348:
6352: nvl(l_max_pact_dt,GREATEST(l_max_asg_eff_st_dt,trunc(p_session_date,'Y')))) ;
6353:
6354: balance_tab(l_count) := l_balance_value ;
6355:
6356: hr_utility.trace('l_balance_value := '||to_char(l_balance_value)) ;
6357: /* Summing up the balance value */
6358: l_combined_balance_value := l_combined_balance_value + l_balance_value ;
6359:
6360: hr_utility.trace('l_combined_balance_value := '||to_char(l_combined_balance_value)) ;
6356: hr_utility.trace('l_balance_value := '||to_char(l_balance_value)) ;
6357: /* Summing up the balance value */
6358: l_combined_balance_value := l_combined_balance_value + l_balance_value ;
6359:
6360: hr_utility.trace('l_combined_balance_value := '||to_char(l_combined_balance_value)) ;
6361:
6362: l_count := l_count + 1 ;
6363: END IF ;
6364: end loop ;
6376: end if ;
6377: end loop ;
6378: end loop ;
6379: END IF ;
6380: hr_utility.trace('l_combined_balance_value B4 Subtracting Actual := '||to_char(l_combined_balance_value)) ;
6381: /* Looping through the current States under the Current Active Assignment
6382: and doing a balance call wrt to current jurisdiction and calculating
6383: eligible SUI Wage Base Override for the State */
6384: for i_all_curr_states in c_all_states(p_assignment_id,p_session_date)
6397: nvl(l_max_pact_dt,GREATEST(l_max_asg_eff_st_dt,trunc(p_session_date,'Y')))) ;
6398:
6399: l_oth_combined_balance := l_combined_balance_value - l_actual_balance_value ;
6400:
6401: hr_utility.trace('Actual States := '||i_all_curr_states.state_code) ;
6402: hr_utility.trace('l_actual_balance_value := '||to_char(l_actual_balance_value)) ;
6403: hr_utility.trace('l_oth_combined_balance A4 Subtracting Actual := '||to_char(l_oth_combined_balance)) ;
6404:
6405: open c_sui_wage_limit(i_all_curr_states.state_code, p_session_date) ;
6398:
6399: l_oth_combined_balance := l_combined_balance_value - l_actual_balance_value ;
6400:
6401: hr_utility.trace('Actual States := '||i_all_curr_states.state_code) ;
6402: hr_utility.trace('l_actual_balance_value := '||to_char(l_actual_balance_value)) ;
6403: hr_utility.trace('l_oth_combined_balance A4 Subtracting Actual := '||to_char(l_oth_combined_balance)) ;
6404:
6405: open c_sui_wage_limit(i_all_curr_states.state_code, p_session_date) ;
6406: fetch c_sui_wage_limit into l_sui_er_wg_lt_curr_state ;
6399: l_oth_combined_balance := l_combined_balance_value - l_actual_balance_value ;
6400:
6401: hr_utility.trace('Actual States := '||i_all_curr_states.state_code) ;
6402: hr_utility.trace('l_actual_balance_value := '||to_char(l_actual_balance_value)) ;
6403: hr_utility.trace('l_oth_combined_balance A4 Subtracting Actual := '||to_char(l_oth_combined_balance)) ;
6404:
6405: open c_sui_wage_limit(i_all_curr_states.state_code, p_session_date) ;
6406: fetch c_sui_wage_limit into l_sui_er_wg_lt_curr_state ;
6407: close c_sui_wage_limit ;
6404:
6405: open c_sui_wage_limit(i_all_curr_states.state_code, p_session_date) ;
6406: fetch c_sui_wage_limit into l_sui_er_wg_lt_curr_state ;
6407: close c_sui_wage_limit ;
6408: hr_utility.trace('SUI Wage Limit for the current State: '|| to_char(l_sui_er_wg_lt_curr_state)) ;
6409:
6410: IF l_oth_combined_balance < l_sui_er_wg_lt_curr_state THEN
6411: l_sui_wg_base := l_sui_er_wg_lt_curr_state - l_oth_combined_balance ;
6412: ELSIF l_oth_combined_balance >= l_sui_er_wg_lt_curr_state THEN
6411: l_sui_wg_base := l_sui_er_wg_lt_curr_state - l_oth_combined_balance ;
6412: ELSIF l_oth_combined_balance >= l_sui_er_wg_lt_curr_state THEN
6413: l_sui_wg_base := 0 ;
6414: END IF ;
6415: hr_utility.trace('l_sui_wg_base := '||to_char(l_sui_wg_base)) ;
6416:
6417: IF l_oth_combined_balance > 0 OR l_actual_balance_value > 0 THEN
6418: update pay_us_emp_state_tax_rules_f
6419: set sui_wage_base_override_amount = l_sui_wg_base
6424: set sui_wage_base_override_amount = null
6425: where assignment_id = p_assignment_id
6426: and state_code = i_all_curr_states.state_code ;
6427: END IF ;
6428: hr_utility.trace('SUI Wage Base Updated...') ;
6429: END IF ;
6430: end loop ;
6431: ELSE -- Not Rehired Condition
6432: hr_utility.trace('Not Rehired Condition... ') ;
6428: hr_utility.trace('SUI Wage Base Updated...') ;
6429: END IF ;
6430: end loop ;
6431: ELSE -- Not Rehired Condition
6432: hr_utility.trace('Not Rehired Condition... ') ;
6433: l_count := 1 ;
6434: /* Iterating through all the states where the employee worked for the Current Assignment
6435: and populating PL/SQL table with the State Code and SUI ER Taxable Balance value */
6436: for i_all_states in c_all_states(p_assignment_id,p_session_date)
6435: and populating PL/SQL table with the State Code and SUI ER Taxable Balance value */
6436: for i_all_states in c_all_states(p_assignment_id,p_session_date)
6437: loop
6438: IF i_all_states.state_code <> '24' THEN
6439: hr_utility.trace('l_count := '||to_char(l_count)) ;
6440: state_code_tab(l_count) := i_all_states.state_code ;
6441: l_jurisdiction_code := i_all_states.state_code || '-000-0000' ;
6442:
6443: hr_utility.trace('l_tax_unit_id := '||to_char(l_tax_unit_id)) ;
6439: hr_utility.trace('l_count := '||to_char(l_count)) ;
6440: state_code_tab(l_count) := i_all_states.state_code ;
6441: l_jurisdiction_code := i_all_states.state_code || '-000-0000' ;
6442:
6443: hr_utility.trace('l_tax_unit_id := '||to_char(l_tax_unit_id)) ;
6444: hr_utility.trace('l_jurisdiction_code := '||l_jurisdiction_code) ;
6445:
6446: pay_balance_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id) ;
6447: pay_balance_pkg.set_context('JURISDICTION_CODE',l_jurisdiction_code) ;
6440: state_code_tab(l_count) := i_all_states.state_code ;
6441: l_jurisdiction_code := i_all_states.state_code || '-000-0000' ;
6442:
6443: hr_utility.trace('l_tax_unit_id := '||to_char(l_tax_unit_id)) ;
6444: hr_utility.trace('l_jurisdiction_code := '||l_jurisdiction_code) ;
6445:
6446: pay_balance_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id) ;
6447: pay_balance_pkg.set_context('JURISDICTION_CODE',l_jurisdiction_code) ;
6448:
6451: p_assignment_id,
6452: nvl(l_max_pact_dt,GREATEST(l_max_asg_eff_st_dt,trunc(p_session_date,'Y')))) ;
6453:
6454: balance_tab(l_count) := l_balance_value ;
6455: hr_utility.trace('l_balance_value := '||to_char(l_balance_value)) ;
6456: /* Summing up the Total Balance Value */
6457: l_combined_balance_value := l_combined_balance_value + l_balance_value ;
6458: hr_utility.trace('l_combined_balance_value := '||to_char(l_combined_balance_value)) ;
6459: l_count := l_count + 1 ;
6454: balance_tab(l_count) := l_balance_value ;
6455: hr_utility.trace('l_balance_value := '||to_char(l_balance_value)) ;
6456: /* Summing up the Total Balance Value */
6457: l_combined_balance_value := l_combined_balance_value + l_balance_value ;
6458: hr_utility.trace('l_combined_balance_value := '||to_char(l_combined_balance_value)) ;
6459: l_count := l_count + 1 ;
6460:
6461: END IF ;
6462: end loop ;
6469:
6470: open c_sui_wage_limit(state_code_tab(i), p_session_date) ;
6471: fetch c_sui_wage_limit into l_sui_er_wg_lt_curr_state ;
6472: close c_sui_wage_limit ;
6473: hr_utility.trace('SUI Wage Limit for the current State: '|| to_char(l_sui_er_wg_lt_curr_state)) ;
6474:
6475: IF l_oth_combined_balance < l_sui_er_wg_lt_curr_state THEN
6476: l_sui_wg_base := l_sui_er_wg_lt_curr_state - l_oth_combined_balance ;
6477: ELSIF l_oth_combined_balance >= l_sui_er_wg_lt_curr_state THEN
6476: l_sui_wg_base := l_sui_er_wg_lt_curr_state - l_oth_combined_balance ;
6477: ELSIF l_oth_combined_balance >= l_sui_er_wg_lt_curr_state THEN
6478: l_sui_wg_base := 0 ;
6479: END IF ;
6480: hr_utility.trace('State_code := '||state_code_tab(i)) ;
6481: hr_utility.trace('l_oth_combined_balance := '||to_char(l_oth_combined_balance)) ;
6482: hr_utility.trace('l_sui_wg_base := '||to_char(l_sui_wg_base)) ;
6483:
6484: IF l_oth_combined_balance > 0 OR l_combined_balance_value > 0 THEN
6477: ELSIF l_oth_combined_balance >= l_sui_er_wg_lt_curr_state THEN
6478: l_sui_wg_base := 0 ;
6479: END IF ;
6480: hr_utility.trace('State_code := '||state_code_tab(i)) ;
6481: hr_utility.trace('l_oth_combined_balance := '||to_char(l_oth_combined_balance)) ;
6482: hr_utility.trace('l_sui_wg_base := '||to_char(l_sui_wg_base)) ;
6483:
6484: IF l_oth_combined_balance > 0 OR l_combined_balance_value > 0 THEN
6485: update pay_us_emp_state_tax_rules_f
6478: l_sui_wg_base := 0 ;
6479: END IF ;
6480: hr_utility.trace('State_code := '||state_code_tab(i)) ;
6481: hr_utility.trace('l_oth_combined_balance := '||to_char(l_oth_combined_balance)) ;
6482: hr_utility.trace('l_sui_wg_base := '||to_char(l_sui_wg_base)) ;
6483:
6484: IF l_oth_combined_balance > 0 OR l_combined_balance_value > 0 THEN
6485: update pay_us_emp_state_tax_rules_f
6486: set sui_wage_base_override_amount = l_sui_wg_base
6491: set sui_wage_base_override_amount = null
6492: where assignment_id = p_assignment_id
6493: and state_code = state_code_tab(i) ;
6494: END IF ;
6495: hr_utility.trace('SUI Wage Base Updated...') ;
6496: end loop ;
6497: END IF ;
6498: END IF ;
6499: /* Exception for the State of Minnesota - No SUI Wage Transfer allowed */
6607:
6608: begin
6609:
6610:
6611: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',1);
6612: if (p_new_location_id is null) and (p_res_state_code is not null
6613: and p_res_county_code is not null
6614: and p_res_city_code is not null )then
6615:
6622:
6623: /* Get the state code, county code and the city code for the
6624: new location */
6625:
6626: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',2);
6627: open csr_get_codes(p_new_location_id);
6628: fetch csr_get_codes into l_state_code, l_county_code,l_city_code,
6629: l_ovrd_state_code, l_ovrd_county_code,
6630: l_ovrd_city_code;
6638: close csr_get_codes;
6639:
6640: end if;
6641:
6642: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',3);
6643: /* Get the default date from the federal tax rules record */
6644:
6645: open csr_get_eff_date;
6646:
6658:
6659: /* Create the new location records */
6660:
6661:
6662: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',4);
6663: l_ret_code := 0;
6664: l_ret_text := null;
6665: l_jurisdiction_code := l_state_code ||'-000-0000';
6666:
6663: l_ret_code := 0;
6664: l_ret_text := null;
6665: l_jurisdiction_code := l_state_code ||'-000-0000';
6666:
6667: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',5);
6668: pay_us_emp_dt_tax_val.check_jurisdiction_exists(p_assignment_id => p_assignment_id,
6669: p_jurisdiction_code => l_jurisdiction_code,
6670: p_ret_code => l_ret_code,
6671: p_ret_text => l_ret_text);
6668: pay_us_emp_dt_tax_val.check_jurisdiction_exists(p_assignment_id => p_assignment_id,
6669: p_jurisdiction_code => l_jurisdiction_code,
6670: p_ret_code => l_ret_code,
6671: p_ret_text => l_ret_text);
6672: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',6);
6673:
6674: /* Bug 2535501 */
6675:
6676: open csr_get_max_assign_end_dt;
6698: and also the state percentage record for every change in
6699: location */
6700:
6701: if l_ret_code = 1 then
6702: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',7);
6703: l_emp_state_tax_rule_id :=
6704: insert_def_state_rec(p_assignment_id => p_assignment_id,
6705: p_effective_start_date => l_default_date,
6706: p_effective_end_date => l_max_assign_end_dt, -- Bug 2535501
6713: for state. The ins_def_county_rec routine will create the
6714: county tax rule record from begin of time till end of time
6715: and also the county percentage record for every change in
6716: location */
6717: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',8);
6718:
6719: l_emp_county_tax_rule_id :=
6720: insert_def_county_rec(p_assignment_id => p_assignment_id,
6721: p_effective_start_date => l_default_date,
6730: for state. The ins_def_city_rec routine will create the
6731: city tax rule record from begin of time till end of time
6732: and also the city percentage record for every change in
6733: location */
6734: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',9);
6735:
6736: l_emp_city_tax_rule_id :=
6737: insert_def_city_rec(p_assignment_id => p_assignment_id,
6738: p_effective_start_date => l_default_date,
6743: p_city_code => l_city_code,
6744: p_business_group_id => p_business_group,
6745: p_percent_time => 0);
6746:
6747: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',10);
6748:
6749: elsif l_ret_code = 0 then
6750: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',101);
6751: /* State record exists. Now check if county record exists */
6746:
6747: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',10);
6748:
6749: elsif l_ret_code = 0 then
6750: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',101);
6751: /* State record exists. Now check if county record exists */
6752: -- Update SUI Wage Base Override Amount
6753: -- Update SUI WAGE BASE Overide amount if payroll is installed otherwise don't
6754: -- call the procedure which does the update
6753: -- Update SUI WAGE BASE Overide amount if payroll is installed otherwise don't
6754: -- call the procedure which does the update
6755: -- Turning Off SUI Wage Base Override Functionality due to Bug# 5486281
6756: /*
6757: IF hr_utility.chk_product_install(p_product =>'Oracle Payroll',
6758: p_legislation => 'US')
6759: then
6760: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',102);
6761: if p_assignment_id is not null and p_session_date is not null
6756: /*
6757: IF hr_utility.chk_product_install(p_product =>'Oracle Payroll',
6758: p_legislation => 'US')
6759: then
6760: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',102);
6761: if p_assignment_id is not null and p_session_date is not null
6762: then
6763: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',103);
6764: set_sui_wage_base_override(p_assignment_id,l_state_code,p_session_date) ;
6759: then
6760: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',102);
6761: if p_assignment_id is not null and p_session_date is not null
6762: then
6763: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',103);
6764: set_sui_wage_base_override(p_assignment_id,l_state_code,p_session_date) ;
6765: end if ;
6766: end if;
6767: -- End Change
6770: l_ret_code := 0;
6771: l_ret_text := null;
6772: l_jurisdiction_code := l_state_code ||'-' || l_county_code ||'-0000';
6773:
6774: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',11);
6775: pay_us_emp_dt_tax_val.check_jurisdiction_exists(p_assignment_id => p_assignment_id,
6776: p_jurisdiction_code => l_jurisdiction_code,
6777: p_ret_code => l_ret_code,
6778: p_ret_text => l_ret_text);
6775: pay_us_emp_dt_tax_val.check_jurisdiction_exists(p_assignment_id => p_assignment_id,
6776: p_jurisdiction_code => l_jurisdiction_code,
6777: p_ret_code => l_ret_code,
6778: p_ret_text => l_ret_text);
6779: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',12);
6780: /* If county record does not exist then city will also not exist */
6781: if l_ret_code = 1 then
6782:
6783: /* Create the county tax rule record and then create %age record
6785: county tax rule record from begin of time till end of time
6786: and also the county percentage record for every change in
6787: location */
6788:
6789: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',13);
6790: l_emp_county_tax_rule_id :=
6791: insert_def_county_rec(p_assignment_id => p_assignment_id,
6792: p_effective_start_date => l_default_date,
6793: p_effective_end_date => l_max_assign_end_dt, -- Bug 2535501
6802: city tax rule record from begin of time till end of time
6803: and also the city percentage record for every change in
6804: location */
6805:
6806: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',14);
6807: l_emp_city_tax_rule_id :=
6808: insert_def_city_rec(p_assignment_id => p_assignment_id,
6809: p_effective_start_date => l_default_date,
6810: p_effective_end_date => l_max_assign_end_dt, -- Bug 2535501
6813: p_county_code => l_county_code,
6814: p_city_code => l_city_code,
6815: p_business_group_id => p_business_group,
6816: p_percent_time => 0);
6817: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',15);
6818:
6819: elsif l_ret_code = 0 then
6820: /* State and county records exist. Check if the city record exists */
6821:
6823: l_ret_text := null;
6824: l_jurisdiction_code := l_state_code ||'-' || l_county_code ||'-'||
6825: l_city_code;
6826:
6827: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',16);
6828: pay_us_emp_dt_tax_val.check_jurisdiction_exists(p_assignment_id => p_assignment_id,
6829: p_jurisdiction_code => l_jurisdiction_code,
6830: p_ret_code => l_ret_code,
6831: p_ret_text => l_ret_text);
6828: pay_us_emp_dt_tax_val.check_jurisdiction_exists(p_assignment_id => p_assignment_id,
6829: p_jurisdiction_code => l_jurisdiction_code,
6830: p_ret_code => l_ret_code,
6831: p_ret_text => l_ret_text);
6832: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',17);
6833:
6834: /* If city record does not exist then create one */
6835:
6836: if l_ret_code = 1 then
6839: for state. The ins_def_city_rec routine will create the
6840: city tax rule record from begin of time till end of time
6841: and also the city percentage record for every change in
6842: location */
6843: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',18);
6844:
6845: l_emp_city_tax_rule_id :=
6846: insert_def_city_rec(p_assignment_id => p_assignment_id,
6847: p_effective_start_date => l_default_date,
6851: p_county_code => l_county_code,
6852: p_city_code => l_city_code,
6853: p_business_group_id => p_business_group,
6854: p_percent_time => 0);
6855: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',19);
6856:
6857: end if;
6858:
6859: end if;
6861: end if;
6862:
6863: if l_ovrd_state_code <> l_state_code then
6864:
6865: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',20);
6866: l_emp_state_tax_rule_id :=
6867: insert_def_state_rec(p_assignment_id => p_assignment_id,
6868: p_effective_start_date => l_default_date,
6869: p_effective_end_date => l_end_of_time,
6869: p_effective_end_date => l_end_of_time,
6870: p_state_code => l_ovrd_state_code,
6871: p_business_group_id => p_business_group,
6872: p_percent_time => 0);
6873: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',21);
6874:
6875: end if;
6876:
6877: if (l_ovrd_state_code <> l_state_code
6875: end if;
6876:
6877: if (l_ovrd_state_code <> l_state_code
6878: or l_ovrd_county_code <> l_county_code) then
6879: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',22);
6880: l_emp_county_tax_rule_id :=
6881: insert_def_county_rec(p_assignment_id => p_assignment_id,
6882: p_effective_start_date => l_default_date,
6883: p_effective_end_date => l_end_of_time,
6884: p_state_code => l_ovrd_state_code,
6885: p_county_code => l_ovrd_county_code,
6886: p_business_group_id => p_business_group,
6887: p_percent_time => 0);
6888: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',23);
6889: end if;
6890:
6891: if (l_ovrd_state_code <> l_state_code
6892: or l_ovrd_county_code <> l_county_code
6890:
6891: if (l_ovrd_state_code <> l_state_code
6892: or l_ovrd_county_code <> l_county_code
6893: or l_ovrd_city_code <> l_city_code) then
6894: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',24);
6895: l_emp_city_tax_rule_id :=
6896: insert_def_city_rec(p_assignment_id => p_assignment_id,
6897: p_effective_start_date => l_default_date,
6898: p_effective_end_date => l_end_of_time,
6900: p_county_code => l_ovrd_county_code,
6901: p_city_code => l_ovrd_city_code,
6902: p_business_group_id => p_business_group,
6903: p_percent_time => 0);
6904: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',25);
6905: end if;
6906:
6907: /* if called for change in location then set the city to 100% */
6908:
6910: p_percent = 100 and
6911: (p_res_state_code is null
6912: and p_res_county_code is null and p_res_city_code is null) then
6913:
6914: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',26);
6915: /* Now update the city record and set it to 100% */
6916:
6917: if l_state_code = l_ovrd_state_code and l_county_code = l_ovrd_county_code
6918: and l_city_code = l_ovrd_city_code then
6916:
6917: if l_state_code = l_ovrd_state_code and l_county_code = l_ovrd_county_code
6918: and l_city_code = l_ovrd_city_code then
6919: l_jurisdiction_code := l_state_code ||'-' || l_county_code ||'-'|| l_city_code;
6920: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',27);
6921: else
6922: l_jurisdiction_code := l_ovrd_state_code ||'-' || l_ovrd_county_code ||'-'||l_ovrd_city_code;
6923: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',28);
6924: end if;
6919: l_jurisdiction_code := l_state_code ||'-' || l_county_code ||'-'|| l_city_code;
6920: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',27);
6921: else
6922: l_jurisdiction_code := l_ovrd_state_code ||'-' || l_ovrd_county_code ||'-'||l_ovrd_city_code;
6923: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',28);
6924: end if;
6925:
6926: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',29);
6927: maintain_element_entry (p_assignment_id => p_assignment_id,
6922: l_jurisdiction_code := l_ovrd_state_code ||'-' || l_ovrd_county_code ||'-'||l_ovrd_city_code;
6923: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',28);
6924: end if;
6925:
6926: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',29);
6927: maintain_element_entry (p_assignment_id => p_assignment_id,
6928: p_effective_start_date => p_validation_start_date,
6929: p_effective_end_date => p_validation_end_date,
6930: p_session_date => p_session_date,
6930: p_session_date => p_session_date,
6931: p_jurisdiction_code => l_jurisdiction_code,
6932: p_percentage_time => 100,
6933: p_mode => 'CORRECTION');
6934: hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',30);
6935: end if;
6936:
6937: end create_new_location_rec;
6938:
7007: and pee.assignment_id = p_assignment_id;
7008:
7009: begin
7010:
7011: hr_utility.set_location('pay_emp_dt_tax_rules.del_updt_entries_for_dates' ,1);
7012:
7013: l_step := 1;
7014: open csr_tax_element;
7015:
7034: end loop;
7035:
7036: close csr_tax_element;
7037:
7038: hr_utility.set_location('pay_us_emp_dt_tax_rules.del_updt_entries_for_dates'
7039: ,2);
7040:
7041: /* Check that all of the input value id for vertex, exists */
7042:
7051: end if;
7052:
7053: end loop;
7054:
7055: hr_utility.set_location('pay_us_emp_dt_tax_rules.del_updt_entries_for_dates'
7056: ,3);
7057:
7058: /* Get element link */
7059: l_step := 2;
7068: fnd_message.set_token('1','ELEMENT LINK');
7069: fnd_message.raise_error;
7070: end if;
7071:
7072: hr_utility.set_location('pay_us_emp_dt_tax_rules.del_updt_entries_for_dates'
7073: ,4);
7074:
7075: /* Get the Element Entry Id */
7076: l_step := 3;
7378: and pee.element_link_id = p_element_link;
7379:
7380: begin
7381:
7382: hr_utility.set_location('pay_emp_tax_dt_tax_rules.del_updt_wc_entry_for_dates' ,1);
7383:
7384: l_step := 1;
7385: open csr_tax_element;
7386:
7394: end if;
7395:
7396: close csr_tax_element;
7397:
7398: hr_utility.set_location('pay_us_emp_dt_tax_rules.del_updt_wc_entry_for_dates' ,2);
7399:
7400: /* Get element link */
7401: l_step := 2;
7402: l_element_link_id := hr_entry_api.get_link(
7410: fnd_message.set_token('1','ELEMENT LINK');
7411: fnd_message.raise_error;
7412: end if;
7413:
7414: hr_utility.set_location('pay_us_emp_dt_tax_rules.del_updt_wc_entry_for_dates' ,3);
7415:
7416: /* Get the Element Entry Id */
7417: l_step := 3;
7418: open csr_wc_ele_entry(l_element_link_id);
8105: end backward_processing;
8106:
8107: begin
8108:
8109: hr_utility.set_location('pay_us_emp_dt_tax_rules.correct_percentage',1);
8110: l_payroll_installed := hr_utility.chk_product_install(p_product => 'Oracle Payroll',
8111: p_legislation => 'US');
8112:
8113: /* Get effective Start date of the Federal Tax Rules record */
8106:
8107: begin
8108:
8109: hr_utility.set_location('pay_us_emp_dt_tax_rules.correct_percentage',1);
8110: l_payroll_installed := hr_utility.chk_product_install(p_product => 'Oracle Payroll',
8111: p_legislation => 'US');
8112:
8113: /* Get effective Start date of the Federal Tax Rules record */
8114:
8111: p_legislation => 'US');
8112:
8113: /* Get effective Start date of the Federal Tax Rules record */
8114:
8115: hr_utility.set_location('pay_us_emp_dt_tax_rules.correct_percentage',2);
8116: open csr_get_eff_date(p_assignment_id);
8117:
8118: fetch csr_get_eff_date into l_default_date;
8119:
8127: end if;
8128:
8129: close csr_get_eff_date;
8130:
8131: hr_utility.set_location('pay_us_emp_dt_tax_rules.correct_percentage',3);
8132: open csr_tax_element;
8133: fetch csr_tax_element into l_element_type_id;
8134: if csr_tax_element%NOTFOUND then
8135: close csr_tax_element;
8140: fnd_message.raise_error;
8141: end if;
8142: close csr_tax_element;
8143:
8144: hr_utility.set_location('pay_us_emp_dt_tax_rules.correct_percentage',4);
8145: /* Get element link */
8146: l_element_link_id := hr_entry_api.get_link(
8147: P_assignment_id => p_assignment_id,
8148: P_element_type_id => l_element_type_id,
8183: as of date T2 */
8184:
8185: if (p_effective_start_date <= l_default_date and
8186: p_effective_end_date = l_end_of_time) then
8187: hr_utility.set_location('pay_us_emp_dt_tax_rules.correct_percentage',5);
8188:
8189: if l_payroll_installed then
8190: /* Get all of the percentage records and set the percentage to zero */
8191:
8188:
8189: if l_payroll_installed then
8190: /* Get all of the percentage records and set the percentage to zero */
8191:
8192: hr_utility.set_location('pay_us_emp_dt_tax_rules.correct_percentage',6);
8193: zero_out_time(p_assignment_id => p_assignment_id,
8194: p_effective_start_date => p_effective_start_date,
8195: p_effective_end_date => p_effective_end_date);
8196: end if;
8197:
8198: /* Create the tax rules records and the %age records for the
8199: new location - if required and set the city %age to 100 */
8200:
8201: hr_utility.set_location('pay_us_emp_dt_tax_rules.correct_percentage',7);
8202:
8203: l_ovrd_percent := 0;
8204: l_percent := 100;
8205: open csr_get_ovrd_loc(p_assignment_id, p_session_date);
8211: end if;
8212: end if;
8213: close csr_get_ovrd_loc;
8214:
8215: hr_utility.set_location('pay_us_emp_dt_tax_rules.correct_percentage',8);
8216: create_new_location_rec(p_assignment_id => p_assignment_id,
8217: p_validation_start_date => p_effective_start_date,
8218: p_validation_end_date => p_effective_end_date,
8219: p_session_date => p_session_date,
8222: p_res_county_code => null,
8223: p_res_city_code => null,
8224: p_business_group => p_business_group_id,
8225: p_percent => l_percent);
8226: hr_utility.set_location('pay_us_emp_dt_tax_rules.correct_percentage',9);
8227: if l_ovrd_percent = 100 then
8228: hr_utility.set_location('pay_us_emp_dt_tax_rules.correct_percentage',10);
8229: create_new_location_rec(p_assignment_id => p_assignment_id,
8230: p_validation_start_date => p_effective_start_date,
8224: p_business_group => p_business_group_id,
8225: p_percent => l_percent);
8226: hr_utility.set_location('pay_us_emp_dt_tax_rules.correct_percentage',9);
8227: if l_ovrd_percent = 100 then
8228: hr_utility.set_location('pay_us_emp_dt_tax_rules.correct_percentage',10);
8229: create_new_location_rec(p_assignment_id => p_assignment_id,
8230: p_validation_start_date => p_effective_start_date,
8231: p_validation_end_date => p_effective_end_date,
8232: p_session_date => p_session_date,
8235: p_res_county_code => null,
8236: p_res_city_code => null,
8237: p_business_group => p_business_group_id,
8238: p_percent => l_ovrd_percent);
8239: hr_utility.set_location('pay_us_emp_dt_tax_rules.correct_percentage',11);
8240: end if;
8241:
8242:
8243: else /* multiple locations */
10444: l_validation_start_date := p_session_date;
10445: l_validation_end_date := l_end_of_time;
10446: end if;
10447:
10448: hr_utility.set_location('pay_us_emp_dt_tax_rules.change_wc_entry',1);
10449: correct_wc_entry (p_assignment_id => p_assignment_id,
10450: p_effective_start_date => l_validation_start_date,
10451: p_effective_end_date => l_validation_end_date,
10452: p_session_date => p_session_date,
10452: p_session_date => p_session_date,
10453: p_new_location_id => p_new_location_id,
10454: p_ret_code => l_ret_code,
10455: p_ret_text => l_ret_text);
10456: hr_utility.set_location('pay_us_emp_dt_tax_rules.change_wc_entry',2);
10457:
10458: end change_wc_entry;
10459:
10460: procedure pull_tax_records( p_assignment_id in number,
10463: l_ef_date DATE;
10464: l_proc VARCHAR2(50) := 'pay_us_emp_dt_tax_rules.pull_tax_records';
10465:
10466: begin
10467: hr_utility.set_location('Entering: ' || l_proc, 5);
10468: /* dscully - modified to handle case where tax record changes
10469: have occured between old hire date and new hire date */
10470:
10471: if p_new_start_date < p_default_date then
10527: end if;
10528:
10529: /* Next delete any orphaned rows */
10530: if p_new_start_date > p_default_date then
10531: hr_utility.set_location(l_proc, 10);
10532: delete PAY_US_EMP_FED_TAX_RULES_F
10533: where assignment_id = p_assignment_id
10534: and p_new_start_date > effective_start_date;
10535:
10546: and p_new_start_date > effective_start_date;
10547:
10548: end if;
10549:
10550: hr_utility.set_location('Leaving: ' || l_proc, 20);
10551:
10552: end pull_tax_records;
10553:
10554: procedure check_hiring_date( p_assignment_id in number,
10572: /* the HR only customers do not run into an error when this code is */
10573:
10574:
10575: l_payroll_installed :=
10576: hr_utility.chk_product_install(p_product =>'Oracle Payroll',
10577: p_legislation => 'US');
10578:
10579:
10580:
10703: begin
10704:
10705: /* Get the resident and the work state, county and city codes */
10706:
10707: hr_utility.set_location('pay_us_emp_dt_tax_rules.default_tax',1);
10708: pay_us_emp_dt_tax_val.get_orig_res_codes (p_assignment_id => p_assignment_id,
10709:
10710: p_session_date => p_effective_start_date,
10711: p_res_state_code => l_res_state_code,
10714: p_res_state_name => l_res_state_name,
10715: p_res_county_name => l_res_county_name,
10716: p_res_city_name => l_res_city_name);
10717:
10718: hr_utility.set_location('pay_us_emp_dt_tax_rules.default_tax',2);
10719: if l_res_state_code is null then
10720: fnd_message.set_name('PER', 'PER_52985_ADD_NO_STATE_SET');
10721: fnd_message.raise_error;
10722: end if;
10735: an override for the address then get the non override address for
10736: the assignment and assign it to the additional state, county
10737: and city codes */
10738:
10739: hr_utility.set_location('pay_us_emp_dt_tax_rules.default_tax',3);
10740: open csr_chk_addr_ovrd;
10741: fetch csr_chk_addr_ovrd into l_add_state_code,
10742: l_add_county_code,
10743: l_add_city_code;
10747: l_add_city_code := null;
10748: end if;
10749: close csr_chk_addr_ovrd;
10750:
10751: hr_utility.set_location('pay_us_emp_dt_tax_rules.default_tax',4);
10752: pay_us_emp_dt_tax_val.get_all_work_codes (p_assignment_id => p_assignment_id,
10753: p_session_date => p_effective_start_date,
10754: p_work_state_code => l_work_state_code,
10755: p_work_county_code => l_work_county_code,
11403: l_loc_id hr_locations.location_id%TYPE;
11404:
11405: begin
11406:
11407: -- hr_utility.trace_on(null,'AMITA');
11408: hr_utility.set_location('pay_us_emp_dt_tax_rules.validate_default',1);
11409:
11410: hr_utility.trace('validate_default-asg ** ' || to_char(p_assignment_id));
11411: hr_utility.trace('validate_default-person id ** ' || to_char(p_person_id));
11404:
11405: begin
11406:
11407: -- hr_utility.trace_on(null,'AMITA');
11408: hr_utility.set_location('pay_us_emp_dt_tax_rules.validate_default',1);
11409:
11410: hr_utility.trace('validate_default-asg ** ' || to_char(p_assignment_id));
11411: hr_utility.trace('validate_default-person id ** ' || to_char(p_person_id));
11412: hr_utility.trace('validate_default-eff start dt ** ' || to_char(p_effective_start_date,'dd-mon-yyyy'));
11406:
11407: -- hr_utility.trace_on(null,'AMITA');
11408: hr_utility.set_location('pay_us_emp_dt_tax_rules.validate_default',1);
11409:
11410: hr_utility.trace('validate_default-asg ** ' || to_char(p_assignment_id));
11411: hr_utility.trace('validate_default-person id ** ' || to_char(p_person_id));
11412: hr_utility.trace('validate_default-eff start dt ** ' || to_char(p_effective_start_date,'dd-mon-yyyy'));
11413: hr_utility.trace('validate_defaulteff end dt ** ' || to_char(p_effective_end_date,'dd-mon-yyyy'));
11414: hr_utility.trace('validate_default - session dt ** ' || to_char(p_session_date,'dd-mon-yyyy'));
11407: -- hr_utility.trace_on(null,'AMITA');
11408: hr_utility.set_location('pay_us_emp_dt_tax_rules.validate_default',1);
11409:
11410: hr_utility.trace('validate_default-asg ** ' || to_char(p_assignment_id));
11411: hr_utility.trace('validate_default-person id ** ' || to_char(p_person_id));
11412: hr_utility.trace('validate_default-eff start dt ** ' || to_char(p_effective_start_date,'dd-mon-yyyy'));
11413: hr_utility.trace('validate_defaulteff end dt ** ' || to_char(p_effective_end_date,'dd-mon-yyyy'));
11414: hr_utility.trace('validate_default - session dt ** ' || to_char(p_session_date,'dd-mon-yyyy'));
11415: hr_utility.trace('validate_default- bg ** ' || to_char(p_business_group_id));
11408: hr_utility.set_location('pay_us_emp_dt_tax_rules.validate_default',1);
11409:
11410: hr_utility.trace('validate_default-asg ** ' || to_char(p_assignment_id));
11411: hr_utility.trace('validate_default-person id ** ' || to_char(p_person_id));
11412: hr_utility.trace('validate_default-eff start dt ** ' || to_char(p_effective_start_date,'dd-mon-yyyy'));
11413: hr_utility.trace('validate_defaulteff end dt ** ' || to_char(p_effective_end_date,'dd-mon-yyyy'));
11414: hr_utility.trace('validate_default - session dt ** ' || to_char(p_session_date,'dd-mon-yyyy'));
11415: hr_utility.trace('validate_default- bg ** ' || to_char(p_business_group_id));
11416: hr_utility.trace('validate_default - form name ** ' || p_from_form);
11409:
11410: hr_utility.trace('validate_default-asg ** ' || to_char(p_assignment_id));
11411: hr_utility.trace('validate_default-person id ** ' || to_char(p_person_id));
11412: hr_utility.trace('validate_default-eff start dt ** ' || to_char(p_effective_start_date,'dd-mon-yyyy'));
11413: hr_utility.trace('validate_defaulteff end dt ** ' || to_char(p_effective_end_date,'dd-mon-yyyy'));
11414: hr_utility.trace('validate_default - session dt ** ' || to_char(p_session_date,'dd-mon-yyyy'));
11415: hr_utility.trace('validate_default- bg ** ' || to_char(p_business_group_id));
11416: hr_utility.trace('validate_default - form name ** ' || p_from_form);
11417: hr_utility.trace('validate_default - mode ** ' || p_mode);
11410: hr_utility.trace('validate_default-asg ** ' || to_char(p_assignment_id));
11411: hr_utility.trace('validate_default-person id ** ' || to_char(p_person_id));
11412: hr_utility.trace('validate_default-eff start dt ** ' || to_char(p_effective_start_date,'dd-mon-yyyy'));
11413: hr_utility.trace('validate_defaulteff end dt ** ' || to_char(p_effective_end_date,'dd-mon-yyyy'));
11414: hr_utility.trace('validate_default - session dt ** ' || to_char(p_session_date,'dd-mon-yyyy'));
11415: hr_utility.trace('validate_default- bg ** ' || to_char(p_business_group_id));
11416: hr_utility.trace('validate_default - form name ** ' || p_from_form);
11417: hr_utility.trace('validate_default - mode ** ' || p_mode);
11418: hr_utility.trace('validate_default - location id ** ' || to_char(p_location_id));
11411: hr_utility.trace('validate_default-person id ** ' || to_char(p_person_id));
11412: hr_utility.trace('validate_default-eff start dt ** ' || to_char(p_effective_start_date,'dd-mon-yyyy'));
11413: hr_utility.trace('validate_defaulteff end dt ** ' || to_char(p_effective_end_date,'dd-mon-yyyy'));
11414: hr_utility.trace('validate_default - session dt ** ' || to_char(p_session_date,'dd-mon-yyyy'));
11415: hr_utility.trace('validate_default- bg ** ' || to_char(p_business_group_id));
11416: hr_utility.trace('validate_default - form name ** ' || p_from_form);
11417: hr_utility.trace('validate_default - mode ** ' || p_mode);
11418: hr_utility.trace('validate_default - location id ** ' || to_char(p_location_id));
11419: --dbms_output.put_line('asg** '||to_char(p_assignment_id));
11412: hr_utility.trace('validate_default-eff start dt ** ' || to_char(p_effective_start_date,'dd-mon-yyyy'));
11413: hr_utility.trace('validate_defaulteff end dt ** ' || to_char(p_effective_end_date,'dd-mon-yyyy'));
11414: hr_utility.trace('validate_default - session dt ** ' || to_char(p_session_date,'dd-mon-yyyy'));
11415: hr_utility.trace('validate_default- bg ** ' || to_char(p_business_group_id));
11416: hr_utility.trace('validate_default - form name ** ' || p_from_form);
11417: hr_utility.trace('validate_default - mode ** ' || p_mode);
11418: hr_utility.trace('validate_default - location id ** ' || to_char(p_location_id));
11419: --dbms_output.put_line('asg** '||to_char(p_assignment_id));
11420: --dbms_output.put_line('asg** '||to_char(p_person_id));
11413: hr_utility.trace('validate_defaulteff end dt ** ' || to_char(p_effective_end_date,'dd-mon-yyyy'));
11414: hr_utility.trace('validate_default - session dt ** ' || to_char(p_session_date,'dd-mon-yyyy'));
11415: hr_utility.trace('validate_default- bg ** ' || to_char(p_business_group_id));
11416: hr_utility.trace('validate_default - form name ** ' || p_from_form);
11417: hr_utility.trace('validate_default - mode ** ' || p_mode);
11418: hr_utility.trace('validate_default - location id ** ' || to_char(p_location_id));
11419: --dbms_output.put_line('asg** '||to_char(p_assignment_id));
11420: --dbms_output.put_line('asg** '||to_char(p_person_id));
11421: --dbms_output.put_line('sd** '||to_char(p_effective_start_date));
11414: hr_utility.trace('validate_default - session dt ** ' || to_char(p_session_date,'dd-mon-yyyy'));
11415: hr_utility.trace('validate_default- bg ** ' || to_char(p_business_group_id));
11416: hr_utility.trace('validate_default - form name ** ' || p_from_form);
11417: hr_utility.trace('validate_default - mode ** ' || p_mode);
11418: hr_utility.trace('validate_default - location id ** ' || to_char(p_location_id));
11419: --dbms_output.put_line('asg** '||to_char(p_assignment_id));
11420: --dbms_output.put_line('asg** '||to_char(p_person_id));
11421: --dbms_output.put_line('sd** '||to_char(p_effective_start_date));
11422: --dbms_output.put_line('ed** '||to_char(p_effective_end_date));
11435: end if;
11436:
11437: /* Check if payroll has been installed or not */
11438:
11439: l_payroll_installed := hr_utility.chk_product_install(p_product =>'Oracle Payroll', p_legislation => 'US');
11440:
11441: /* Set up the validation start date */
11442:
11443: if p_from_form = 'Assignment' then
11451: end if;
11452:
11453: if p_from_form = 'Address' then
11454:
11455: hr_utility.set_location('pay_us_emp_dt_tax_rules.validate_default',2);
11456:
11457: /* Get all of the assignments */
11458:
11459: open csr_addr_get_assignment(p_person_id);
11466:
11467:
11468: exit when csr_addr_get_assignment%NOTFOUND;
11469:
11470: hr_utility.set_location('pay_us_emp_dt_tax_rules.validate_default',3);
11471:
11472: /* Check whether the defaulting of tax rules has been done or not.
11473: If not then do the defaulting of tax rules else return 'Y' in
11474: l_fed_exists to indicate that the defaulting of tax rules has
11573: fetch csr_get_assignment into l_assignment_id, l_validation_start_date;
11574:
11575: exit when csr_get_assignment%NOTFOUND;
11576:
11577: hr_utility.set_location('pay_us_emp_dt_tax_rules.validate_default',3);
11578:
11579: /* Check whether the defaulting of tax rules has been done or not.
11580: If not then do the defaulting of tax rules else return 'Y' in
11581: l_fed_exists to indicate that the defaulting of tax rules has
11629: end if; /* End of Person form processing */
11630:
11631: if p_from_form = 'Assignment' or p_from_form = 'Tax Rules' then
11632:
11633: hr_utility.set_location('pay_us_emp_dt_tax_rules.validate_default',5);
11634:
11635: if p_from_form = 'Tax Rules' then
11636:
11637: /* Get the min effective start date of the assignment for which the
11642: fetch csr_get_min_eff_date into l_validation_start_date;
11643:
11644: if l_validation_start_date is null then
11645:
11646: hr_utility.set_location('pay_us_emp_dt_tax_rules.validate_default',6);
11647:
11648: p_return_code := 1;
11649: p_return_text := 'Default rules not satisfied';
11650: close csr_get_min_eff_date;
11661: fetch csr_chk_assignment into l_code;
11662:
11663: if csr_chk_assignment%NOTFOUND then
11664:
11665: hr_utility.set_location('pay_us_emp_dt_tax_rules.validate_default',7);
11666:
11667: p_return_code := 1;
11668: p_return_text := 'Default rules not satisfied';
11669: close csr_chk_assignment;
11733: end if;
11734:
11735: end if; /* This marks the end of processing when called by the 'Tax Rules */
11736:
11737: hr_utility.set_location('pay_us_emp_dt_tax_rules.validate_default',8);
11738:
11739:
11740: /* begin modifications - dscully 21-JUN-2000 */
11741: /* added default creation of tax records for taxation location */
11781: -- Update SUI WAGE BASE Overide amount you have payroll installed otherwise don't
11782: -- call the procedure which does the update
11783:
11784:
11785: IF hr_utility.chk_product_install(p_product =>'Oracle Payroll',
11786: p_legislation => 'US')
11787: then
11788: if p_assignment_id is not null and p_session_date is not null
11789: then
12043: /* the assignment record itself(which might be a better way of doing it!). */
12044:
12045: /* first lets make sure the record exists */
12046:
12047: hr_utility.set_location('pay_us_emp_dt_tax_rules.validate_default',100);
12048:
12049: create_new_location_rec(p_assignment_id => p_assignment_id,
12050: p_validation_start_date => null,
12051: p_validation_end_date => null,
12060:
12061: /* next we get the begin and end dates for the new location being effective
12062: along with the state code of the jurisdiction */
12063:
12064: hr_utility.set_location('pay_us_emp_dt_tax_rules.validate_default',105);
12065:
12066: open csr_max_loc_date(p_assignment_id,p_location_id,l_validation_start_date);
12067: fetch csr_max_loc_date into l_loc_max_date;
12068: close csr_max_loc_date;
12072: end if;
12073:
12074: --
12075:
12076: hr_utility.set_location('pay_us_emp_dt_tax_rules.validate_default',110);
12077:
12078: open csr_min_loc_date(p_assignment_id,p_location_id,l_validation_start_date);
12079: fetch csr_min_loc_date into l_loc_min_date;
12080: close csr_min_loc_date;
12083: l_loc_min_date := hr_api.g_sot;
12084: end if;
12085:
12086: --
12087: hr_utility.set_location('pay_us_emp_dt_tax_rules.validate_default',115);
12088:
12089: open csr_loc_state_code(p_location_id);
12090: fetch csr_loc_state_code into l_loc_state_code;
12091:
12114: to:
12115: ASG --------------|-------L1-----------------
12116: TAX ----------|---|--------------------------
12117: */
12118: hr_utility.set_location('pay_us_emp_dt_tax_rules.validate_default',120);
12119:
12120: /* insert the middle record */
12121: insert_fed_tax_row(
12122: tax_rec.emp_fed_tax_rule_id,
12209: else
12210:
12211: /* here we simply update the sui codes */
12212:
12213: hr_utility.set_location('pay_us_emp_dt_tax_rules.validate_default',125);
12214:
12215: update pay_us_emp_fed_tax_rules_f
12216: set sui_state_code = l_loc_state_code,
12217: sui_jurisdiction_code = l_loc_state_code || '-000-0000'
12228: ASG -------L1-----------------|--------------
12229: TAX --------------------------|-----|--------
12230: */
12231:
12232: hr_utility.set_location('pay_us_emp_dt_tax_rules.validate_default',130);
12233:
12234: insert_fed_tax_row(
12235: tax_rec.emp_fed_tax_rule_id,
12236: l_loc_max_date + 1,
12322: end if;
12323: end loop;
12324:
12325: /* end modifications - dscully 20-jul-2000 */
12326: hr_utility.set_location('pay_us_emp_dt_tax_rules.validate_default',140);
12327: end if; /* for payroll installed/not installed */
12328: end if; /* for location id is not null */
12329:
12330: end if; /* call from assignment form */