1: PACKAGE BODY PAY_ASG_GEO_PKG AS
2: -- $Header: pyasgrpt.pkb 120.1 2005/12/07 04:11:19 sackumar noship $
3: -- This packages maintains the table: pay_us_asg_reporting.
4: -- It is called from pyustaxr.pkb, peasgo1t.pkb.
5: --
6: --
7:
51: INTO v_exists
52: FROM dual
53: WHERE EXISTS (
54: SELECT 'Y'
55: FROM pay_us_asg_reporting
56: WHERE assignment_id = P_assignment_id
57: AND P_jurisdiction = substr(jurisdiction_code,1,2)||'-000-0000'
58: AND v_tax_unit_id = tax_unit_id);
59: --
68: INTO v_exists
69: FROM dual
70: WHERE EXISTS (
71: SELECT 'Y'
72: FROM pay_us_asg_reporting
73: WHERE assignment_id = P_assignment_id
74: AND P_jurisdiction = substr(jurisdiction_code,1,6)||'-0000'
75: AND v_tax_unit_id = tax_unit_id);
76: --
84: INTO v_exists
85: FROM dual
86: WHERE EXISTS(
87: SELECT 'Y'
88: FROM pay_us_asg_reporting
89: WHERE assignment_id = P_assignment_id
90: AND P_jurisdiction = jurisdiction_code
91: AND v_tax_unit_id = tax_unit_id);
92: ELSE
95: INTO v_exists
96: FROM dual
97: WHERE EXISTS(
98: SELECT 'Y'
99: FROM pay_us_asg_reporting
100: WHERE assignment_id = P_assignment_id
101: AND P_jurisdiction = jurisdiction_code
102: AND v_tax_unit_id = tax_unit_id);
103: --
115: /* Update the table if nessesary*/
116: hr_utility.set_location('PAY_ASG_GEO_PKG', 4);
117:
118: IF v_level = 'County' THEN /* look for state to update */
119: UPDATE pay_us_asg_reporting
120: SET jurisdiction_code = P_jurisdiction
121: WHERE assignment_id = P_assignment_id
122: AND v_tax_unit_id = tax_unit_id
123: AND jurisdiction_code = substr(P_jurisdiction,1,2)||'-000-0000';
122: AND v_tax_unit_id = tax_unit_id
123: AND jurisdiction_code = substr(P_jurisdiction,1,2)||'-000-0000';
124: hr_utility.set_location('PAY_ASG_GEO_PKG', 5);
125: ELSIF v_level = 'City' THEN /* look for state or county to update */
126: UPDATE pay_us_asg_reporting
127: SET jurisdiction_code = P_jurisdiction
128: WHERE assignment_id = P_assignment_id
129: AND v_tax_unit_id = tax_unit_id
130: AND (jurisdiction_code = substr(P_jurisdiction,1,2)||'-000-0000'
133: hr_utility.set_location('PAY_ASG_GEO_PKG', 6);
134: END IF;
135: IF (SQL%ROWCOUNT = 0 OR v_level = 'State' OR v_level = 'School')
136: AND (P_jurisdiction IS NOT NULL) AND (length(P_jurisdiction) <> 3) THEN
137: INSERT INTO pay_us_asg_reporting
138: (assignment_id, effective_start_date, effective_end_date,
139: jurisdiction_code, tax_unit_id)
140: VALUES
141: (P_assignment_id, v_start_date, v_end_date,