40: --
41: --
42: procedure chk_assignment_id
43: (p_emp_city_tax_rule_id in
44: pay_us_emp_city_tax_rules_f.emp_city_tax_rule_id%TYPE
45: ,p_assignment_id in
46: pay_us_emp_city_tax_rules_f.assignment_id%TYPE
47: ,p_business_group_id in
48: pay_us_emp_city_tax_rules_f.business_group_id%TYPE
42: procedure chk_assignment_id
43: (p_emp_city_tax_rule_id in
44: pay_us_emp_city_tax_rules_f.emp_city_tax_rule_id%TYPE
45: ,p_assignment_id in
46: pay_us_emp_city_tax_rules_f.assignment_id%TYPE
47: ,p_business_group_id in
48: pay_us_emp_city_tax_rules_f.business_group_id%TYPE
49: ,p_effective_date in date
50: ,p_object_version_number in number
44: pay_us_emp_city_tax_rules_f.emp_city_tax_rule_id%TYPE
45: ,p_assignment_id in
46: pay_us_emp_city_tax_rules_f.assignment_id%TYPE
47: ,p_business_group_id in
48: pay_us_emp_city_tax_rules_f.business_group_id%TYPE
49: ,p_effective_date in date
50: ,p_object_version_number in number
51: )
52: is
153: -- Internal Row Handler Use Only.
154: --
155: --
156: procedure chk_state_code
157: (p_state_code in pay_us_emp_city_tax_rules_f.state_code%TYPE
158: )
159: is
160: --
161: l_proc varchar2(72) := g_package||'chk_state_code';
158: )
159: is
160: --
161: l_proc varchar2(72) := g_package||'chk_state_code';
162: l_state_code pay_us_emp_city_tax_rules_f.state_code%TYPE;
163: --
164: --
165: cursor csr_valid_state_code is
166: select state_code
182: fetch csr_valid_state_code into l_state_code;
183: if csr_valid_state_code%notfound then
184: close csr_valid_state_code;
185: pay_cty_shd.constraint_error
186: ('PAY_US_EMP_CITY_TAX_RULES_FK1');
187: end if;
188: close csr_valid_state_code;
189: else
190: hr_utility.set_message(801,'PAY_72752_CTY_STA_NOT_NULL');
224: -- Internal Row Handler Use Only.
225: --
226: --
227: procedure chk_county_code
228: (p_county_code in pay_us_emp_city_tax_rules_f.county_code%TYPE
229: ,p_state_code in pay_us_emp_city_tax_rules_f.state_code%TYPE
230: )
231: is
232: --
225: --
226: --
227: procedure chk_county_code
228: (p_county_code in pay_us_emp_city_tax_rules_f.county_code%TYPE
229: ,p_state_code in pay_us_emp_city_tax_rules_f.state_code%TYPE
230: )
231: is
232: --
233: l_proc varchar2(72) := g_package||'chk_county_code';
230: )
231: is
232: --
233: l_proc varchar2(72) := g_package||'chk_county_code';
234: l_county_code pay_us_emp_city_tax_rules_f.county_code%TYPE;
235: l_state_code pay_us_emp_city_tax_rules_f.state_code%TYPE;
236: --
237: --
238: cursor csr_valid_county_code is
231: is
232: --
233: l_proc varchar2(72) := g_package||'chk_county_code';
234: l_county_code pay_us_emp_city_tax_rules_f.county_code%TYPE;
235: l_state_code pay_us_emp_city_tax_rules_f.state_code%TYPE;
236: --
237: --
238: cursor csr_valid_county_code is
239: select county_code
300: -- Internal Row Handler Use Only.
301: --
302: --
303: procedure chk_city_code
304: (p_state_code in pay_us_emp_city_tax_rules_f.state_code%TYPE
305: ,p_county_code in pay_us_emp_city_tax_rules_f.county_code%TYPE
306: ,p_city_code in pay_us_emp_city_tax_rules_f.city_code%TYPE
307: )
308: is
301: --
302: --
303: procedure chk_city_code
304: (p_state_code in pay_us_emp_city_tax_rules_f.state_code%TYPE
305: ,p_county_code in pay_us_emp_city_tax_rules_f.county_code%TYPE
306: ,p_city_code in pay_us_emp_city_tax_rules_f.city_code%TYPE
307: )
308: is
309: --
302: --
303: procedure chk_city_code
304: (p_state_code in pay_us_emp_city_tax_rules_f.state_code%TYPE
305: ,p_county_code in pay_us_emp_city_tax_rules_f.county_code%TYPE
306: ,p_city_code in pay_us_emp_city_tax_rules_f.city_code%TYPE
307: )
308: is
309: --
310: l_proc varchar2(72) := g_package||'chk_city_code';
307: )
308: is
309: --
310: l_proc varchar2(72) := g_package||'chk_city_code';
311: l_city_code pay_us_emp_city_tax_rules_f.city_code%TYPE;
312: --
313: --
314: cursor csr_valid_city_code is
315: select city_code
332: fetch csr_valid_city_code into l_city_code;
333: if csr_valid_city_code%notfound then
334: close csr_valid_city_code;
335: pay_cty_shd.constraint_error
336: (p_constraint_name => 'PAY_US_EMP_CITY_TAX_RULES_FK3');
337: end if;
338: close csr_valid_city_code;
339: else
340: hr_utility.set_message(801,'PAY_72739_CTY_CTY_NOT_NULL');
377: -- Internal Row Handler Use Only.
378: --
379: --
380: procedure chk_jurisdiction_code
381: (p_jurisdiction_code in pay_us_emp_city_tax_rules_f.jurisdiction_code%TYPE
382: ,p_city_code in pay_us_emp_city_tax_rules_f.city_code%TYPE
383: ,p_county_code in pay_us_emp_city_tax_rules_f.county_code%TYPE
384: ,p_state_code in pay_us_emp_city_tax_rules_f.state_code%TYPE)
385: is
378: --
379: --
380: procedure chk_jurisdiction_code
381: (p_jurisdiction_code in pay_us_emp_city_tax_rules_f.jurisdiction_code%TYPE
382: ,p_city_code in pay_us_emp_city_tax_rules_f.city_code%TYPE
383: ,p_county_code in pay_us_emp_city_tax_rules_f.county_code%TYPE
384: ,p_state_code in pay_us_emp_city_tax_rules_f.state_code%TYPE)
385: is
386: --
379: --
380: procedure chk_jurisdiction_code
381: (p_jurisdiction_code in pay_us_emp_city_tax_rules_f.jurisdiction_code%TYPE
382: ,p_city_code in pay_us_emp_city_tax_rules_f.city_code%TYPE
383: ,p_county_code in pay_us_emp_city_tax_rules_f.county_code%TYPE
384: ,p_state_code in pay_us_emp_city_tax_rules_f.state_code%TYPE)
385: is
386: --
387: l_proc varchar2(72) := g_package||'chk_jurisdiction_code';
380: procedure chk_jurisdiction_code
381: (p_jurisdiction_code in pay_us_emp_city_tax_rules_f.jurisdiction_code%TYPE
382: ,p_city_code in pay_us_emp_city_tax_rules_f.city_code%TYPE
383: ,p_county_code in pay_us_emp_city_tax_rules_f.county_code%TYPE
384: ,p_state_code in pay_us_emp_city_tax_rules_f.state_code%TYPE)
385: is
386: --
387: l_proc varchar2(72) := g_package||'chk_jurisdiction_code';
388: l_jurisdiction number;
465: -- Internal Row Handler Use Only.
466: --
467: procedure chk_additional_wa_rate
468: (p_emp_city_tax_rule_id in
469: pay_us_emp_city_tax_rules_f.emp_city_tax_rule_id%TYPE
470: ,p_additional_wa_rate in
471: pay_us_emp_city_tax_rules_f.additional_wa_rate%TYPE
472: )
473: is
467: procedure chk_additional_wa_rate
468: (p_emp_city_tax_rule_id in
469: pay_us_emp_city_tax_rules_f.emp_city_tax_rule_id%TYPE
470: ,p_additional_wa_rate in
471: pay_us_emp_city_tax_rules_f.additional_wa_rate%TYPE
472: )
473: is
474: --
475: l_proc varchar2(72) := g_package||'chk_additional_wa_rate';
472: )
473: is
474: --
475: l_proc varchar2(72) := g_package||'chk_additional_wa_rate';
476: l_additional_wa_rate pay_us_emp_city_tax_rules_f.additional_wa_rate%TYPE;
477: --
478: begin
479: hr_utility.set_location('Entering: '|| l_proc, 5);
480: --
536: -- Internal Row Handler Use Only.
537: --
538: procedure chk_filing_status_code
539: (p_emp_city_tax_rule_id in
540: pay_us_emp_city_tax_rules_f.emp_city_tax_rule_id%TYPE,
541: p_filing_status_code in
542: pay_us_emp_city_tax_rules_f.filing_status_code%TYPE,
543: p_state_code in pay_us_emp_city_tax_rules_f.state_code%TYPE,
544: p_effective_date in date,
538: procedure chk_filing_status_code
539: (p_emp_city_tax_rule_id in
540: pay_us_emp_city_tax_rules_f.emp_city_tax_rule_id%TYPE,
541: p_filing_status_code in
542: pay_us_emp_city_tax_rules_f.filing_status_code%TYPE,
543: p_state_code in pay_us_emp_city_tax_rules_f.state_code%TYPE,
544: p_effective_date in date,
545: p_validation_start_date in date,
546: p_validation_end_date in date
539: (p_emp_city_tax_rule_id in
540: pay_us_emp_city_tax_rules_f.emp_city_tax_rule_id%TYPE,
541: p_filing_status_code in
542: pay_us_emp_city_tax_rules_f.filing_status_code%TYPE,
543: p_state_code in pay_us_emp_city_tax_rules_f.state_code%TYPE,
544: p_effective_date in date,
545: p_validation_start_date in date,
546: p_validation_end_date in date
547: )
639: --
640: --
641: procedure chk_lit_additional_tax
642: (p_emp_city_tax_rule_id in
643: pay_us_emp_city_tax_rules_f.emp_city_tax_rule_id%TYPE
644: ,p_lit_additional_tax in
645: pay_us_emp_city_tax_rules_f.lit_additional_tax%TYPE
646: )
647: is
641: procedure chk_lit_additional_tax
642: (p_emp_city_tax_rule_id in
643: pay_us_emp_city_tax_rules_f.emp_city_tax_rule_id%TYPE
644: ,p_lit_additional_tax in
645: pay_us_emp_city_tax_rules_f.lit_additional_tax%TYPE
646: )
647: is
648: --
649: l_proc varchar2(72) := g_package||'chk_lit_additional_tax';
703: -- Internal Row Handler Use Only.
704: --
705: procedure chk_lit_override_amount
706: (p_emp_city_tax_rule_id in
707: pay_us_emp_city_tax_rules_f.emp_city_tax_rule_id%TYPE
708: ,p_lit_override_amount in
709: pay_us_emp_city_tax_rules_f.lit_override_amount%TYPE
710: )
711: is
705: procedure chk_lit_override_amount
706: (p_emp_city_tax_rule_id in
707: pay_us_emp_city_tax_rules_f.emp_city_tax_rule_id%TYPE
708: ,p_lit_override_amount in
709: pay_us_emp_city_tax_rules_f.lit_override_amount%TYPE
710: )
711: is
712: --
713: l_proc varchar2(72) := g_package||
768: -- Internal Row Handler Use Only.
769: --
770: procedure chk_lit_override_rate
771: (p_emp_city_tax_rule_id in
772: pay_us_emp_city_tax_rules_f.emp_city_tax_rule_id%TYPE
773: ,p_lit_override_rate in
774: pay_us_emp_city_tax_rules_f.lit_override_rate%TYPE
775: )
776: is
770: procedure chk_lit_override_rate
771: (p_emp_city_tax_rule_id in
772: pay_us_emp_city_tax_rules_f.emp_city_tax_rule_id%TYPE
773: ,p_lit_override_rate in
774: pay_us_emp_city_tax_rules_f.lit_override_rate%TYPE
775: )
776: is
777: --
778: l_proc varchar2(72) := g_package||
832: -- Internal Row Handler Use Only.
833: --
834: procedure chk_withholding_allowances
835: (p_emp_city_tax_rule_id in
836: pay_us_emp_city_tax_rules_f.emp_city_tax_rule_id%TYPE
837: ,p_withholding_allowances in
838: pay_us_emp_city_tax_rules_f.withholding_allowances%TYPE
839: )
840: is
834: procedure chk_withholding_allowances
835: (p_emp_city_tax_rule_id in
836: pay_us_emp_city_tax_rules_f.emp_city_tax_rule_id%TYPE
837: ,p_withholding_allowances in
838: pay_us_emp_city_tax_rules_f.withholding_allowances%TYPE
839: )
840: is
841: --
842: l_proc varchar2(72) := g_package||
910: -- Internal Row Handler Use Only.
911: --
912: procedure chk_school_district_code
913: (p_emp_city_tax_rule_id in
914: pay_us_emp_city_tax_rules_f.emp_city_tax_rule_id%TYPE,
915: p_assignment_id in
916: pay_us_emp_city_tax_rules_f.assignment_id%TYPE,
917: p_school_district_code in
918: pay_us_emp_city_tax_rules_f.school_district_code%TYPE,
912: procedure chk_school_district_code
913: (p_emp_city_tax_rule_id in
914: pay_us_emp_city_tax_rules_f.emp_city_tax_rule_id%TYPE,
915: p_assignment_id in
916: pay_us_emp_city_tax_rules_f.assignment_id%TYPE,
917: p_school_district_code in
918: pay_us_emp_city_tax_rules_f.school_district_code%TYPE,
919: p_state_code in
920: pay_us_emp_city_tax_rules_f.state_code%TYPE,
914: pay_us_emp_city_tax_rules_f.emp_city_tax_rule_id%TYPE,
915: p_assignment_id in
916: pay_us_emp_city_tax_rules_f.assignment_id%TYPE,
917: p_school_district_code in
918: pay_us_emp_city_tax_rules_f.school_district_code%TYPE,
919: p_state_code in
920: pay_us_emp_city_tax_rules_f.state_code%TYPE,
921: p_county_code in
922: pay_us_emp_city_tax_rules_f.county_code%TYPE,
916: pay_us_emp_city_tax_rules_f.assignment_id%TYPE,
917: p_school_district_code in
918: pay_us_emp_city_tax_rules_f.school_district_code%TYPE,
919: p_state_code in
920: pay_us_emp_city_tax_rules_f.state_code%TYPE,
921: p_county_code in
922: pay_us_emp_city_tax_rules_f.county_code%TYPE,
923: p_city_code in
924: pay_us_emp_city_tax_rules_f.city_code%TYPE,
918: pay_us_emp_city_tax_rules_f.school_district_code%TYPE,
919: p_state_code in
920: pay_us_emp_city_tax_rules_f.state_code%TYPE,
921: p_county_code in
922: pay_us_emp_city_tax_rules_f.county_code%TYPE,
923: p_city_code in
924: pay_us_emp_city_tax_rules_f.city_code%TYPE,
925: p_effective_date in date,
926: p_validation_start_date in date,
920: pay_us_emp_city_tax_rules_f.state_code%TYPE,
921: p_county_code in
922: pay_us_emp_city_tax_rules_f.county_code%TYPE,
923: p_city_code in
924: pay_us_emp_city_tax_rules_f.city_code%TYPE,
925: p_effective_date in date,
926: p_validation_start_date in date,
927: p_validation_end_date in date,
928: p_object_version_number in
925: p_effective_date in date,
926: p_validation_start_date in date,
927: p_validation_end_date in date,
928: p_object_version_number in
929: pay_us_emp_city_tax_rules_f.object_version_number%TYPE
930: )
931: is
932: --
933: l_proc varchar2(72) := g_package||'chk_school_district_code';
931: is
932: --
933: l_proc varchar2(72) := g_package||'chk_school_district_code';
934: l_school_district_code
935: pay_us_emp_city_tax_rules_f.school_district_code%TYPE;
936: l_cty_schl_dist_code
937: pay_us_emp_city_tax_rules_f.school_district_code%TYPE;
938: l_cnt_schl_dist_code
939: pay_us_emp_county_tax_rules_f.school_district_code%TYPE;
933: l_proc varchar2(72) := g_package||'chk_school_district_code';
934: l_school_district_code
935: pay_us_emp_city_tax_rules_f.school_district_code%TYPE;
936: l_cty_schl_dist_code
937: pay_us_emp_city_tax_rules_f.school_district_code%TYPE;
938: l_cnt_schl_dist_code
939: pay_us_emp_county_tax_rules_f.school_district_code%TYPE;
940: l_api_updating boolean;
941: --
956: p_cnt_code varchar2,
957: p_cty_code varchar2,
958: p_tax_rule_id number) is
959: select school_district_code
960: from pay_us_emp_city_tax_rules_f ctr
961: where ctr.assignment_id = p_asg_id
962: and ctr.effective_end_date >= p_validation_start_date
963: and ctr.effective_start_date <= p_validation_end_date
964: and ctr.school_district_code is not null
1070: -- ---------------------------------------------------------------------------
1071: --
1072: --
1073: -- Description:
1074: -- Tax rules may be deleted from pay_us_emp_city_tax_rules_f when the
1075: -- following conditions are met:
1076: -- - no payroll has been run for this city
1077: -- - the city is not assigned to a work location
1078: -- - the city is not assigned to a primary resident address
1100: --
1101: procedure chk_delete
1102: (p_effective_date in date,
1103: p_assignment_id in per_assignments_f.assignment_id%TYPE,
1104: p_city_code in pay_us_emp_city_tax_rules_f.city_code%TYPE,
1105: p_county_code in pay_us_emp_city_tax_rules_f.county_code%TYPE,
1106: p_state_code in pay_us_emp_city_tax_rules_f.state_code%TYPE,
1107: p_delete_mode in varchar2,
1108: p_delete_routine in varchar2
1101: procedure chk_delete
1102: (p_effective_date in date,
1103: p_assignment_id in per_assignments_f.assignment_id%TYPE,
1104: p_city_code in pay_us_emp_city_tax_rules_f.city_code%TYPE,
1105: p_county_code in pay_us_emp_city_tax_rules_f.county_code%TYPE,
1106: p_state_code in pay_us_emp_city_tax_rules_f.state_code%TYPE,
1107: p_delete_mode in varchar2,
1108: p_delete_routine in varchar2
1109: )
1102: (p_effective_date in date,
1103: p_assignment_id in per_assignments_f.assignment_id%TYPE,
1104: p_city_code in pay_us_emp_city_tax_rules_f.city_code%TYPE,
1105: p_county_code in pay_us_emp_city_tax_rules_f.county_code%TYPE,
1106: p_state_code in pay_us_emp_city_tax_rules_f.state_code%TYPE,
1107: p_delete_mode in varchar2,
1108: p_delete_routine in varchar2
1109: )
1110: is
1774: --
1775: cursor csr_leg_code is
1776: select a.legislation_code
1777: from per_business_groups a,
1778: pay_us_emp_city_tax_rules_f b
1779: where b.emp_city_tax_rule_id = p_emp_city_tax_rule_id
1780: and a.business_group_id = b.business_group_id;
1781: --
1782: -- Declare local variables