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
8 PROCEDURE create_asg_geo_row( P_assignment_id Number,
9 P_jurisdiction varchar2,
10 P_tax_unit_id varchar2 := NULL)
11 IS
12
13 CURSOR csr_date IS
14 SELECT effective_start_date, effective_end_date
15 FROM per_assignments_f
16 WHERE assignment_id = P_assignment_id;
17
18 CURSOR csr_date_and_gre IS
19 SELECT distinct --Bug 4671218 paf.effective_start_date, paf.effective_end_date,
20 hsck.segment1 tax_unit_id
21 FROM per_assignments_f paf, hr_soft_coding_keyflex hsck
22 WHERE paf.assignment_id = P_assignment_id
23 AND paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id;
24
25 v_tax_unit_id NUMBER;
26 v_start_date DATE;
27 v_end_date DATE;
28 v_exists varchar2(10);
29 v_level varchar2(10);
30
31 BEGIN
32 --
33 hr_utility.set_location('PAY_ASG_GEO_PKG for: '||p_jurisdiction,10);
34 /* First get effective dates and tax_unit_id if it is not passed */
35 --
36 hr_utility.set_location('PAY_ASG_GEO_PKG', 20);
37 FOR cur_rec IN csr_date_and_gre LOOP
38
39 v_tax_unit_id := cur_rec.tax_unit_id;
40 BEGIN/* inner unit */
41
42 --
43 --
44 hr_utility.set_location('PAY_ASG_GEO_PKG', 0);
45 /* Check if an appropriate record already exists */
46 /* If like state see if any record exists in that state*/
47 --
48 IF P_jurisdiction like '%000-0000' THEN
49 v_level := 'State';
50 SELECT 'Y'
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 --
60 hr_utility.set_location('PAY_ASG_GEO_PKG', '1');
61
62 --
63 /* If like county see if any record for that county already exists*/
64 --
65 ELSIF P_jurisdiction like '%-0000' THEN
66 v_level := 'County';
67 SELECT 'Y'
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 --
77 --
78 hr_utility.set_location('PAY_ASG_GEO_PKG', 2);
79 /* If city make certain it is not already present */
80 --
81 ELSIF length(P_jurisdiction) = 8 THEN
82 v_level := 'School';
83 SELECT 'Y'
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
93 v_level := 'City';
94 SELECT 'Y'
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 --
104 hr_utility.set_location('PAY_ASG_GEO_PKG', 3);
105 --
106 END IF;
107 v_level := 'Federal';
108 --
109 /* Update the table if nessesary*/
110 hr_utility.set_location('PAY_ASG_GEO_PKG', 4);
111
112
113 EXCEPTION
114 when NO_DATA_FOUND then
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';
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'
131 OR
132 jurisdiction_code = substr(P_jurisdiction,1,6)||'-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,
142 P_jurisdiction, v_tax_unit_id);
143 END IF;
144 --
145 END; /*Inner loop*/
146 --
147 --
148 END LOOP;
149 --
150 --
151 hr_utility.set_location('PAY_ASG_GEO_PKG', 7);
152 --
153 END;
154 --
155 --
156 PROCEDURE Pay_US_Asg_rpt(p_assignment_id NUMBER)
157 IS
158 --
159
160 -- Bug 3756385 -- Broke the cursor c_asg_info in three different cursors for city , county and state.
161
162 CURSOR csr_city_asg_info IS -- get local geos
163 SELECT DISTINCT
164 paf.assignment_id, hsck.segment1 tax_unit_id, pect.jurisdiction_code
165 FROM per_all_assignments_f paf,
166 hr_soft_coding_keyflex hsck,
167 pay_us_emp_city_tax_rules_f pect
168 WHERE paf.assignment_id = p_assignment_id
169 AND paf.assignment_id = pect.assignment_id
170 AND paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
171 UNION ALL -- get school districts (CITY)
172 SELECT DISTINCT
173 paf.assignment_id, hsck.segment1 tax_unit_id,
174 substr(jurisdiction_code,1,2)||'-'||pect.school_district_code
175 FROM per_all_assignments_f paf,
176 hr_soft_coding_keyflex hsck,
177 pay_us_emp_city_tax_rules_f pect
178 WHERE paf.assignment_id = p_assignment_id
179 AND pect.school_district_code IS NOT NULL
180 AND paf.assignment_id = pect.assignment_id
181 AND paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id;
182
183 CURSOR csr_county_asg_info IS -- get county codes
184 SELECT DISTINCT
185 paf.assignment_id, hsck.segment1 tax_unit_id, pect.jurisdiction_code
186 FROM per_all_assignments_f paf,
187 hr_soft_coding_keyflex hsck,
188 pay_us_emp_county_tax_rules_f pect
189 WHERE paf.assignment_id = p_assignment_id
190 AND paf.assignment_id = pect.assignment_id
191 AND paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
192 UNION ALL -- get school districts (COUNTY)
193 SELECT DISTINCT
194 paf.assignment_id, hsck.segment1 tax_unit_id,
195 substr(jurisdiction_code,1,2)||'-'||pect.school_district_code
196 FROM per_all_assignments_f paf,
197 hr_soft_coding_keyflex hsck,
198 pay_us_emp_county_tax_rules_f pect
199 WHERE paf.assignment_id = p_assignment_id
200 AND pect.school_district_code IS NOT NULL
201 AND paf.assignment_id = pect.assignment_id
202 AND paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id;
203
204
205 CURSOR csr_state_asg_info IS -- get state codes
206 SELECT DISTINCT
207 paf.assignment_id, hsck.segment1 tax_unit_id, pest.jurisdiction_code
208 FROM per_all_assignments_f paf,
209 hr_soft_coding_keyflex hsck,
210 pay_us_emp_state_tax_rules_f pest
211 WHERE paf.assignment_id = p_assignment_id
212 AND paf.assignment_id = pest.assignment_id
213 AND paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id;
214
215
216 --
217 BEGIN /*Begin pay_us_rpt*/
218 --
219 hr_utility.set_location('PAY_US_ASG_RPT', 1);
220 --
221 /* Call create_asg_geo_row for each jurisdiction and taxunit */
222 FOR cur_rec IN csr_city_asg_info LOOP
223 hr_utility.set_location('PAY_US_ASG_RPT', 2);
224 --
225 PAY_ASG_GEO_PKG.create_asg_geo_row(cur_rec.assignment_id,
226 cur_rec.jurisdiction_code,
227 cur_rec.tax_unit_id);
228 --
229 END LOOP;
230
231 FOR cur_rec IN csr_county_asg_info LOOP
232 hr_utility.set_location('PAY_US_ASG_RPT', 2);
233 --
234 PAY_ASG_GEO_PKG.create_asg_geo_row(cur_rec.assignment_id,
235 cur_rec.jurisdiction_code,
236 cur_rec.tax_unit_id);
237 --
238 END LOOP;
239
240 FOR cur_rec IN csr_state_asg_info LOOP
241 hr_utility.set_location('PAY_US_ASG_RPT', 2);
242 --
243 PAY_ASG_GEO_PKG.create_asg_geo_row(cur_rec.assignment_id,
244 cur_rec.jurisdiction_code,
245 cur_rec.tax_unit_id);
246 --
247 END LOOP;
248 --
249 hr_utility.set_location('PAY_US_ASG_RPT', 3);
250 --
251 END; /* END pay_us_rpt */
252 --
253 END PAY_ASG_GEO_PKG;
254
255
[Home] [Help]