[Home] [Help]
PACKAGE BODY: APPS.PAY_NO_SSB_CODES
Source
1 PACKAGE BODY pay_no_ssb_codes AS
2 /* $Header: pynossbc.pkb 120.0.12000000.1 2007/05/20 09:29:22 rlingama noship $ */
3
4 FUNCTION populate_table
5 (p_assignment_action_id NUMBER, p_effective_date DATE) RETURN VARCHAR2 IS
6
7 CURSOR csr_get_processed_elements (p_assignment_action_id NUMBER,
8 p_effective_date DATE) IS
9 select pet.element_type_id,
10 pxi.eei_information1 input_value_id,
11 prr.run_result_id,
12 pxi.eei_information2 ssb_code,
13 pxi.eei_information3 add_detail
14 from
15 pay_assignment_actions paa,
16 pay_run_results prr,
17 pay_element_types_f pet,
18 pay_element_type_extra_info pxi
19 where
20 paa.assignment_action_id =p_assignment_action_id
21 and paa.assignment_action_id = prr.assignment_action_id
22 and prr.element_type_id = pet.element_type_id
23 and p_effective_date between pet.effective_start_date and pet.effective_end_date
24 and pet.element_type_id = pxi.element_type_id
25 and pxi.eei_information_category = 'NO_SSB_CODES'
26 and pxi.eei_information3 = 'SUMMARY'
27 UNION
28 select pet.element_type_id,
29 pxi.eei_information4 input_value_id,
30 prr.run_result_id,
31 pxi.eei_information3 ssb_code,
32 'AMOUNT' add_detail
33 from
34 pay_assignment_actions paa,
35 pay_run_results prr,
36 pay_element_types_f pet,
37 pay_element_type_extra_info pxi
38 where
39 paa.assignment_action_id =p_assignment_action_id
40 and paa.assignment_action_id = prr.assignment_action_id
41 and prr.element_type_id = pet.element_type_id
42 and p_effective_date between pet.effective_start_date and pet.effective_end_date
43 and pet.element_type_id = pxi.element_type_id
44 and to_char(p_effective_date,'YYYY') between pxi.eei_information1 and nvl(pxi.eei_information2,'4712')
45 and pxi.eei_information_category = 'NO_EOY_REPORTING_CODE_MAPPING'
46 order by ssb_code;
47 --
48 l_cache_index number;
49 BEGIN
50
51 If (g_ssb_codes_table.count > 0) then
52 l_cache_index := g_ssb_codes_table.last + 1;
53 else
54 l_cache_index := 1;
55 End If;
56
57 --hr_utility.set_location('In populate table',10);
58
59 for csr_get_processed_elements_rec in csr_get_processed_elements (p_assignment_action_id, p_effective_date)
60 LOOP
61
62 g_ssb_codes_table(l_cache_index).element_type_id := csr_get_processed_elements_rec.element_type_id;
63 g_ssb_codes_table(l_cache_index).input_value_id := csr_get_processed_elements_rec.input_value_id;
64 g_ssb_codes_table(l_cache_index).run_result_id := csr_get_processed_elements_rec.run_result_id;
65 g_ssb_codes_table(l_cache_index).ssb_code := csr_get_processed_elements_rec.ssb_code;
66 g_ssb_codes_table(l_cache_index).add_detail := csr_get_processed_elements_rec.add_detail;
67
68
69 /*hr_utility.set_location('g_ssb_codes_table(l_cache_index).element_type_id: ' || g_ssb_codes_table(l_cache_index).element_type_id, 10);
70 hr_utility.set_location('g_ssb_codes_table(l_cache_index).input_value_id: ' || g_ssb_codes_table(l_cache_index).input_value_id, 10);
71 hr_utility.set_location('g_ssb_codes_table(l_cache_index).run_result_id: ' || g_ssb_codes_table(l_cache_index).run_result_id, 10 );
72 hr_utility.set_location('g_ssb_codes_table(l_cache_index).ssb_code: ' || g_ssb_codes_table(l_cache_index).ssb_code, 10);
73 hr_utility.set_location('g_ssb_codes_table(l_cache_index).add_detail: ' || g_ssb_codes_table(l_cache_index).add_detail, 10);
74 */
75 l_cache_index := l_cache_index +1;
76 END LOOP;
77 return 'Y';
78
79 END populate_table;
80
81
82
83 FUNCTION set_next_cached_code(p_ssb_code in varchar2)
84 RETURN VARCHAR2
85 is
86 l_cache_index number;
87 l_ssb_code VARCHAR2(150);
88
89 begin
90
91 l_ssb_code:='XXXX';
92
93 l_cache_index := g_ssb_codes_table.FIRST;
94
95 --hr_utility.set_location('in set next ssb code',10);
96
97
98 WHILE l_cache_index IS NOT NULL
99 LOOP
100
101 --hr_utility.set_location('in loop l_cache_index' || l_cache_index,10);
102 IF ( g_ssb_codes_table(l_cache_index).ssb_code = p_ssb_code )
103 THEN
104 l_ssb_code:=p_ssb_code;
105
106 END IF;
107
108 l_cache_index := g_ssb_codes_table.NEXT(l_cache_index);
109
110
111 if l_cache_index is null then
112 exit;
113 end if;
114
115 IF l_ssb_code <> 'XXXX' and g_ssb_codes_table(l_cache_index).ssb_code <> p_ssb_code THEN
116 g_next_ssb_code :=g_ssb_codes_table(l_cache_index).ssb_code;
117 --hr_utility.set_location('g_ssb_codes_table(l_cache_index).ssb_code' || g_ssb_codes_table(l_cache_index).ssb_code,10);
118 return g_next_ssb_code;--g_next_ssb_code;
119 END IF;
120 END LOOP;
121
122 g_next_ssb_code := 'NOT FOUND';
123 --hr_utility.set_location('out set next ssb code',10);
124 RETURN 'NOT FOUND';
125
126 end set_next_cached_code;
127
128
129 FUNCTION clear_cached_value
130 (p_ssb_code VARCHAR2) RETURN VARCHAR2
131 IS
132 l_cache_index number;
133 BEGIN
134 l_cache_index := g_ssb_codes_table.FIRST;
135
136
137 -- filter out the desired preference
138
139 WHILE l_cache_index IS NOT NULL
140 LOOP
141
142 IF ( g_ssb_codes_table(l_cache_index).ssb_code = p_ssb_code )
143 THEN
144
145 g_ssb_codes_table.delete(l_cache_index);
146
147 RETURN 'Y';
148 END IF;
149
150 l_cache_index := g_ssb_codes_table.NEXT(l_cache_index);
151
152
153 END LOOP;
154 RETURN 'N';
155 end clear_cached_value;
156
157
158 FUNCTION get_total_result_value
159 (p_assignment_action_id NUMBER,
160 p_ssb_code VARCHAR2
161 ) RETURN NUMBER
162 IS
163
164 CURSOR CSR_SSB_CODES IS
165 select row_low_range_or_name
166 from pay_user_tables put,
167 pay_user_rows_f pur,
168 fnd_sessions fs
169 where
170 put.user_table_name ='NO_SSB_CODE_RULES'
171 and put.user_table_id = pur.user_table_id
172 and fs.session_id = userenv('sessionid')
173 and fs.effective_date between pur.effective_start_date
174 and pur.effective_end_date;
175
176 CURSOR csr_sum_results (p_run_result_id NUMBER, p_input_value_id NUMBER) IS
177 SELECT to_number (result_value)
178 FROM pay_run_result_values
179 WHERE run_result_id = p_run_result_id
180 AND input_value_id = p_input_value_id;
181
182 l_cache_index number;
183 l_run_result_id pay_run_results.run_result_id%TYPE;
184 l_input_value_id pay_input_values_f.input_value_id%type;
185 l_value NUMBER(15):=0;
186 l_value_sum NUMBER(15):=0;
187 l_ssb_code VARCHAR2(150);
188 l_add_detail VARCHAR2(150);
189
190 BEGIN
191
192 l_ssb_code := 'XXXX';
193 l_cache_index := g_ssb_codes_table.FIRST;
194
195
196 WHILE l_cache_index IS NOT NULL LOOP
197
198 IF g_ssb_codes_table(l_cache_index).ssb_code = p_ssb_code THEN
199 l_add_detail := g_ssb_codes_table(l_cache_index).add_detail;
200 l_input_value_id := g_ssb_codes_table(l_cache_index).input_value_id;
201 l_run_result_id := g_ssb_codes_table(l_cache_index).run_result_id;
202
203 --hr_utility.set_location('l_run_result_id: ' ||l_run_result_id,10);
204 --hr_utility.set_location('l_input_value_id: ' ||l_input_value_id,10);
205
206 -- IF l_add_detail = 'SUMMARY' THEN
207 OPEN csr_sum_results (l_run_result_id, l_input_value_id);
208 FETCH csr_sum_results INTO l_value;
209 CLOSE csr_sum_results;
210 --hr_utility.set_location('l_value: '|| l_value,10);
211 l_value_sum := l_value_sum + l_value;
212 --hr_utility.set_location('l_value_sum: '|| l_value_sum,10);
213
214 -- END IF;
215 --hr_utility.set_location('after end if sum',10);
216
217 l_ssb_code:=p_ssb_code;
218 END IF;
219
220 l_cache_index :=g_ssb_codes_table.NEXT(l_cache_index);
221
222 if l_cache_index is null then
223 exit;
224 end if;
225 -- SSB codes are arranged in order in the plsql table.
226 -- IF the SSB code value gets changed that means all the codes of one type have been exhausted.
227 -- If condition below checks this condition and exists the loop in case of change;
228
229 IF l_ssb_code <> 'XXXX' and g_ssb_codes_table(l_cache_index).ssb_code <> p_ssb_code THEN
230 exit;
231 END IF;
232 END LOOP;
233
234 return nvl(l_value_sum,0);
235
236 END get_total_result_value;
237
238
239 FUNCTION get_next_cached_code RETURN VARCHAR2 IS
240
241 BEGIN
242 --hr_utility.set_location('NVL(g_next_ssb_code): ' || NVL(g_next_ssb_code,'XXXX'), 10);
243 return NVL(g_next_ssb_code,'XXXX');
244 END get_next_cached_code ;
245
246 FUNCTION get_current_cached_code RETURN VARCHAR2 IS
247
248 BEGIN
249
250
251
252 If (g_ssb_codes_table.count > 0) then
253 g_cache_index := g_cache_index + 1;
254 End If;
255
256 If g_cache_index > g_ssb_codes_table.LAST then
257 return 'XXXX';
258 end if;
259
260 IF g_current_ssb_code IS NULL THEN
261 if g_cache_index <= g_ssb_codes_table.LAST then
262 g_current_ssb_code := g_ssb_codes_table(g_cache_index).ssb_code;
263 end if;
264 return NVL(g_current_ssb_code,'XXXX');
265 ELSE
266
267 WHILE g_cache_index IS NOT NULL LOOP
268
269 IF g_ssb_codes_table(g_cache_index).ssb_code = NVL(g_current_ssb_code,'XXXX') THEN
270
271 g_cache_index :=g_ssb_codes_table.NEXT(g_cache_index);
272
273 --hr_utility.set_location('g_cache_index: ' || to_char (g_cache_index), 10);
274
275 if g_cache_index is null then
276 exit;
277 end if;
278 END IF;
279 -- SSB codes are arranged in order in the plsql table.
280 -- IF the SSB code value gets changed that means all the codes of one type have been exhausted.
281 -- If condition below checks this condition and exists the loop in case of change;
282
283 IF g_ssb_codes_table(g_cache_index).ssb_code <> NVL(g_current_ssb_code,'XXXX') THEN
284 g_current_ssb_code:= g_ssb_codes_table(g_cache_index).ssb_code;
285 return NVL(g_current_ssb_code,'XXXX') ;
286 END IF;
287
288 hr_utility.set_location('in while loop',10);
289 END LOOP;
290
291 END IF;
292
293 -- g_current_ssb_code:= g_ssb_codes_table(g_cache_index).ssb_code;
294
295 --hr_utility.set_location('NVL(g_current_ssb_code): ' || NVL(g_current_ssb_code,'XXXX'), 10);
296 return NVL(g_current_ssb_code,'XXXX');
297 END get_current_cached_code ;
298
299 FUNCTION clear_cached_table RETURN VARCHAR2 IS
300
301 l_cache_index NUMBER(10);
302 BEGIN
303
304 l_cache_index := g_ssb_codes_table.FIRST;
305
306 WHILE l_cache_index IS NOT NULL LOOP
307
308 g_ssb_codes_table.delete(l_cache_index);
309 l_cache_index :=g_ssb_codes_table.NEXT(l_cache_index);
310 END LOOP;
311
312 g_current_ssb_code := null;
313 g_next_ssb_code := null;
314 g_cache_index :=0;
315 RETURN 'Y';
316 END clear_cached_table;
317
318 END pay_no_ssb_codes;