[Home] [Help]
PACKAGE BODY: APPS.PAY_US_CTGY_FEEDS_PKG
Source
1 package body pay_us_ctgy_feeds_pkg as
2 /* $Header: pyusctgf.pkb 115.2 1999/11/03 17:54:41 pkm ship $ */
3 --
4 /*
5 --
6 -- Copyright (c) Oracle Corporation 1991, 1992, 1993. All rights reserved.
7 --
8 /*
9 NAME
10 pyusctgf.pkb
11 --
12 DESCRIPTION
13 API to set needed US balance feeds acc. to element classn and category
14 --
15 MODIFIED (DD-MON-YYYY)
16 S Panwar 6-FEB-1995 Created
17 S Panwar 17-APR-1995 Fixed problem with not dealing with multiple
18 datetracked entries for same element type
19 S Panwar 4-MAY-1995 Removed default on p_date to avoid poss. problems
20 S Panwar 13-JUL-1995 Added support for Dependent Care
24 --
21 D Jeng 25-JUN-1995 Added sections for County, City, School
22 dscully 3-NOV-1999 Added EIC balance feed for Dep Care, Section 125, and 401k dedn's
23 */
25 -------------------------------------------------------------------------------
26 --
27 --
28 --
29 --
30 -------------------------------------------------------------------------------
31 PROCEDURE create_category_feeds (p_element_type_id NUMBER,
32 p_date DATE) IS
33 --
34 -- For each index, these three tables specify which classification + category
35 -- must be fed to which balance, which must be seeded.
36 --
37 l_classn text_table;
38 l_catgry text_table;
39 l_balance text_table;
40 l_num_entries number;
41 l_payval_id number;
42 l_bal_type_id number;
43 l_bg_id number;
44 l_feed_exists number;
45 l_ele_date date;
46 l_ele_classn varchar2(80);
47 l_ele_catgry varchar2(80);
48 --
49 BEGIN
50 --
51 -- The classn/categories to be fed are hard coded here.
52 -- Need to feed all necessary tax type variations
53 --
54 l_classn(1) := 'PRE-TAX DEDUCTIONS';
55 l_catgry(1) := 'H';
56 l_balance(1) := 'SECTION 125';
57 l_classn(2) := 'PRE-TAX DEDUCTIONS';
58 l_catgry(2) := 'H';
59 l_balance(2) := 'SECTION 125 FOR FIT';
60 l_classn(3) := 'PRE-TAX DEDUCTIONS';
61 l_catgry(3) := 'H';
62 l_balance(3) := 'SECTION 125 FOR FUTA';
63 l_classn(4) := 'PRE-TAX DEDUCTIONS';
64 l_catgry(4) := 'H';
65 l_balance(4) := 'SECTION 125 FOR MEDICARE';
66 l_classn(5) := 'PRE-TAX DEDUCTIONS';
67 l_catgry(5) := 'H';
68 l_balance(5) := 'SECTION 125 FOR SDI';
69 l_classn(6) := 'PRE-TAX DEDUCTIONS';
70 l_catgry(6) := 'H';
71 l_balance(6) := 'SECTION 125 FOR SIT';
72 l_classn(7) := 'PRE-TAX DEDUCTIONS';
73 l_catgry(7) := 'H';
74 l_balance(7) := 'SECTION 125 FOR SS';
75 l_classn(8) := 'PRE-TAX DEDUCTIONS';
76 l_catgry(8) := 'H';
77 l_balance(8) := 'SECTION 125 FOR SUI';
78 -- bug 1059450 - dscully 11/3/99
79 l_classn(43) := 'PRE-TAX DEDUCTIONS';
80 l_catgry(43) := 'H';
81 l_balance(43) := 'SECTION 125 FOR EIC';
82 --
83 l_classn(9) := 'PRE-TAX DEDUCTIONS';
84 l_catgry(9) := 'D';
85 l_balance(9) := 'DEF COMP 401K';
86 l_classn(10) := 'PRE-TAX DEDUCTIONS';
87 l_catgry(10) := 'D';
88 l_balance(10) := 'DEF COMP 401K FOR FIT';
89 l_classn(11) := 'PRE-TAX DEDUCTIONS';
90 l_catgry(11) := 'D';
91 l_balance(11) := 'DEF COMP 401K FOR FUTA';
92 l_classn(12) := 'PRE-TAX DEDUCTIONS';
93 l_catgry(12) := 'D';
94 l_balance(12) := 'DEF COMP 401K FOR MEDICARE';
95 l_classn(13) := 'PRE-TAX DEDUCTIONS';
96 l_catgry(13) := 'D';
97 l_balance(13) := 'DEF COMP 401K FOR SDI';
98 l_classn(14) := 'PRE-TAX DEDUCTIONS';
99 l_catgry(14) := 'D';
100 l_balance(14) := 'DEF COMP 401K FOR SIT';
101 l_classn(15) := 'PRE-TAX DEDUCTIONS';
102 l_catgry(15) := 'D';
103 l_balance(15) := 'DEF COMP 401K FOR SS';
104 l_classn(16) := 'PRE-TAX DEDUCTIONS';
105 l_catgry(16) := 'D';
106 l_balance(16) := 'DEF COMP 401K FOR SUI';
107 -- bug 1059450 dscully 11/3/99
108 l_classn(44) := 'PRE-TAX DEDUCTIONS';
109 l_catgry(44) := 'D';
110 l_balance(44) := 'DEF COMP 401K FOR EIC';
111 --
112 l_classn(17) := 'SUPPLEMENTAL EARNINGS';
113 l_catgry(17) := 'CM';
114 l_balance(17) := 'COMMISSIONS';
115 l_classn(18) := 'SUPPLEMENTAL EARNINGS';
116 l_catgry(18) := 'CM';
117 l_balance(18) := 'COMMISSIONS FOR SIT';
118 l_classn(19) := 'SUPPLEMENTAL EARNINGS';
119 l_catgry(19) := 'CM';
120 l_balance(19) := 'COMMISSIONS FOR NWSIT';
121 --
122 l_classn(20) := 'PRE-TAX DEDUCTIONS';
123 l_catgry(20) := 'S';
124 l_balance(20) := 'DEPENDENT CARE';
125 l_classn(21) := 'PRE-TAX DEDUCTIONS';
126 l_catgry(21) := 'S';
127 l_balance(21) := 'DEPENDENT CARE FOR FIT';
128 l_classn(22) := 'PRE-TAX DEDUCTIONS';
129 l_catgry(22) := 'S';
130 l_balance(22) := 'DEPENDENT CARE FOR FUTA';
131 l_classn(23) := 'PRE-TAX DEDUCTIONS';
132 l_catgry(23) := 'S';
133 l_balance(23) := 'DEPENDENT CARE FOR MEDICARE';
134 l_classn(24) := 'PRE-TAX DEDUCTIONS';
135 l_catgry(24) := 'S';
136 l_balance(24) := 'DEPENDENT CARE FOR SDI';
137 l_classn(25) := 'PRE-TAX DEDUCTIONS';
138 l_catgry(25) := 'S';
139 l_balance(25) := 'DEPENDENT CARE FOR SIT';
140 l_classn(26) := 'PRE-TAX DEDUCTIONS';
141 l_catgry(26) := 'S';
142 l_balance(26) := 'DEPENDENT CARE FOR SS';
143 l_classn(27) := 'PRE-TAX DEDUCTIONS';
144 l_catgry(27) := 'S';
145 l_balance(27) := 'DEPENDENT CARE FOR SUI';
146 -- bug 1059450 dscully 11/3/99
147 l_classn(45) := 'PRE-TAX DEDUCTIONS';
148 l_catgry(45) := 'S';
149 l_balance(45) := 'DEPENDENT CARE FOR EIC';
150 --==================
151 -- added by dj
152 --==================
153
154 -- CITY
155 l_classn(28) := 'SUPPLEMENTAL EARNINGS';
156 l_catgry(28) := 'CM';
157 l_balance(28) := 'COMMISSIONS FOR CITY';
158 l_classn(29) := 'SUPPLEMENTAL EARNINGS';
159 l_catgry(29) := 'CM';
160 l_balance(29) := 'COMMISSIONS FOR NWCITY';
161 l_classn(30) := 'PRE-TAX DEDUCTIONS';
162 l_catgry(30) := 'D';
163 l_balance(30) := 'DEF COMP 401K FOR CITY';
164 l_classn(31) := 'PRE-TAX DEDUCTIONS';
165 l_catgry(31) := 'H';
166 l_balance(31) := 'SECTION 125 FOR CITY';
167 l_classn(32) := 'PRE-TAX DEDUCTIONS';
168 l_catgry(32) := 'S';
169 l_balance(32) := 'DEPENDENT CARE FOR CITY';
170
171 --COUNTY
172
173 l_classn(33) := 'SUPPLEMENTAL EARNINGS';
174 l_catgry(33) := 'CM';
175 l_balance(33) := 'COMMISSIONS FOR COUNTY';
176 l_classn(34) := 'SUPPLEMENTAL EARNINGS';
177 l_catgry(34) := 'CM';
178 l_balance(34) := 'COMMISSIONS FOR NWCOUNTY';
179 l_classn(35) := 'PRE-TAX DEDUCTIONS';
180 l_catgry(35) := 'D';
181 l_balance(35) := 'DEF COMP 401K FOR COUNTY';
182 l_classn(36) := 'PRE-TAX DEDUCTIONS';
183 l_catgry(36) := 'H';
184 l_balance(36) := 'SECTION 125 FOR COUNTY';
185 l_classn(37) := 'PRE-TAX DEDUCTIONS';
186 l_catgry(37) := 'S';
187 l_balance(37) := 'DEPENDENT CARE FOR COUNTY';
188
189 --SCHOOL
190
191 l_classn(38) := 'SUPPLEMENTAL EARNINGS';
192 l_catgry(38) := 'CM';
193 l_balance(38) := 'COMMISSIONS FOR SCHOOL';
194 l_classn(39) := 'SUPPLEMENTAL EARNINGS';
195 l_catgry(39) := 'CM';
196 l_balance(39) := 'COMMISSIONS FOR NWSCHOOL';
197 l_classn(40) := 'PRE-TAX DEDUCTIONS';
198 l_catgry(40) := 'D';
199 l_balance(40) := 'DEF COMP 401K FOR SCHOOL';
200 l_classn(41) := 'PRE-TAX DEDUCTIONS';
201 l_catgry(41) := 'H';
202 l_balance(41) := 'SECTION 125 FOR SCHOOL';
203 l_classn(42) := 'PRE-TAX DEDUCTIONS';
204 l_catgry(42) := 'S';
205 l_balance(42) := 'DEPENDENT CARE FOR SCHOOL';
206
207 l_num_entries := 45;
208 --
209 --
210 -- Get business group id and element start date for the element,
211 -- as well as element classification and category
212 --
213 SELECT et.business_group_id, et.effective_start_date,
214 UPPER(ec.classification_name), et.element_information1
215 INTO l_bg_id, l_ele_date, l_ele_classn, l_ele_catgry
216 FROM pay_element_types_f et,
217 pay_element_classifications ec
218 WHERE et.element_type_id = p_element_type_id
219 AND p_date between et.effective_start_date
220 and et.effective_end_date
221 AND et.classification_id = ec.classification_id;
222 --
223 FOR i IN 1..l_num_entries LOOP
224 --
225 -- Check if match, if so then create feed.
226 --
227 hr_utility.set_location('pyusctgf.create_category_feeds',10);
228 IF (l_ele_classn = l_classn(i)) and (l_ele_catgry = l_catgry(i)) THEN
229 --
230 -- Get pay value input value for the element
231 --
232 hr_utility.set_location('pyusctgf.create_category_feeds',100);
233 SELECT inp.input_value_id
234 INTO l_payval_id
235 FROM pay_input_values_f inp,
236 hr_lookups hl
237 WHERE inp.element_type_id = p_element_type_id
238 AND inp.name = hl.meaning
239 AND hl.lookup_code = 'PAY VALUE'
240 AND hl.lookup_type = 'NAME_TRANSLATIONS';
241 --
242 --
243 -- Find balance type id of appropriate balance and feed with input value.
244 -- If balance not found, then continue.
245 --
246 BEGIN
247
248 hr_utility.set_location('pyusctgf.create_category_feeds',200);
249 SELECT BT.balance_type_id
250 INTO l_bal_type_id
251 FROM pay_balance_types BT
252 WHERE UPPER(BT.balance_name) = l_balance(i)
253 AND BT.business_group_id IS NULL
254 AND BT.legislation_code = 'US';
255 --
256 SELECT count(0)
257 INTO l_feed_exists
258 FROM pay_balance_feeds_f bf
259 WHERE l_ele_date between effective_start_date and
260 effective_end_date
261 AND balance_type_id = l_bal_type_id
262 AND input_value_id = l_payval_id;
263 --
264 IF l_feed_exists = 0 THEN -- skip if feed already exists
265 --
266 -- dbms_output.put_line('insert balance feed..'||to_char(p_element_type_id));
267 hr_utility.set_location('pyusctgf.create_category_feeds',300);
268 hr_balances.ins_balance_feed(
269 p_option => 'INS_MANUAL_FEED',
270 p_input_value_id => l_payval_id,
271 p_element_type_id => NULL,
272 p_primary_classification_id => NULL,
273 p_sub_classification_id => NULL,
274 p_sub_classification_rule_id => NULL,
275 p_balance_type_id => l_bal_type_id,
276 p_scale => '1',
277 p_session_date => l_ele_date,
278 p_business_group => l_bg_id,
279 p_legislation_code => NULL,
280 p_mode => 'USER');
281 END IF;
282 -- dbms_output.put_line('balance feed already exists ' || to_char(p_element_type_id));
283 --
284 EXCEPTION WHEN NO_DATA_FOUND THEN
285 hr_utility.trace('pyusctgf: Failed to find balance type: ' || l_balance(i) || ' --> Skipped.');
286 --
287 END;
288 --
289 END IF;
290 -- dbms_output.put_line('no match..');
291 --
292 END LOOP;
293 --
294 END create_category_feeds;
295 --
296 end pay_us_ctgy_feeds_pkg;