[Home] [Help]
PACKAGE BODY: APPS.PER_ES_SS_REP_DYN_TRG
Source
1 PACKAGE BODY PER_ES_SS_REP_DYN_TRG AS
2 /* $Header: peesssdy.pkb 115.3 2004/03/16 23:12:42 srjanard noship $ */
3
4 -----------------------------------------------------
5 --GET_ASSIGNMENT_ID
6 -----------------------------------------------------
7 FUNCTION get_assignment_id(p_element_entry_id NUMBER) RETURN NUMBER IS
8 CURSOR csr_find_asg_id IS
9 SELECT assignment_id
10 FROM pay_element_entries_f
11 WHERE element_entry_id = p_element_entry_id;
12
13 l_assignment_id NUMBER;
14 BEGIN
15 OPEN csr_find_asg_id;
16 FETCH csr_find_asg_id INTO l_assignment_id;
17 CLOSE csr_find_asg_id;
18 RETURN l_assignment_id;
19 END get_assignment_id;
20 -----------------------------------------------------------
21 --GET_BUSINESS_GROUP_ID
22 -----------------------------------------------------------
23 FUNCTION get_business_group_id(p_element_entry_id NUMBER) RETURN NUMBER IS
24 CURSOR csr_find_business_grp_id IS
25 SELECT business_group_id
26 FROM per_all_assignments_f paf
27 ,pay_element_entries_f peef
28 WHERE peef.element_entry_id = p_element_entry_id
29 AND peef.assignment_id = paf.assignment_id;
30
31 l_business_group_id NUMBER;
32 BEGIN
33 OPEN csr_find_business_grp_id;
34 FETCH csr_find_business_grp_id INTO l_business_group_id;
35 CLOSE csr_find_business_grp_id;
36 RETURN l_business_group_id;
37 END get_business_group_id;
38 --------------------------------------------------------------
39 --asg_check_update
40 --------------------------------------------------------------
41 PROCEDURE asg_check_update(p_assignment_id NUMBER,
42 p_assignment_type VARCHAR2,
43 p_effective_start_date DATE,
44 p_effective_end_date DATE,
45 p_asg_status_type_id NUMBER,
46 p_employment_category VARCHAR2,
47 p_soft_coding_keyflex_id NUMBER,
48 p_primary_flag VARCHAR) AS
49
50 CURSOR csr_asg_status IS
51 SELECT assignment_extra_info_id
52 ,object_version_number
53 ,aei_information2 last_reported_date
54 ,aei_information3 event
55 ,nvl(aei_information4,'X') value
56 ,aei_information6 action_type
57 ,aei_information7 first_change_date
58 FROM per_assignment_extra_info
59 WHERE assignment_id = p_assignment_id
60 AND information_type = 'ES_SS_REP'
61 AND aei_information5 <> 'Y';
62
63 CURSOR csr_find_contribution_code IS
64 SELECT sck.segment5
65 FROM hr_soft_coding_keyflex sck
66 WHERE sck.soft_coding_keyflex_id = p_soft_coding_keyflex_id;
67
68 CURSOR csr_asg_status_type IS
69 SELECT per_system_status
70 FROM per_assignment_status_types
71 WHERE assignment_status_type_id = p_asg_status_type_id;
72
73 l_ovn NUMBER;
74 l_contribution_code VARCHAR2(60);
75 l_system_status per_assignment_status_types.per_system_status%TYPE;
76
77 BEGIN
78 IF p_assignment_type = 'E' AND p_primary_flag= 'Y' THEN
79
80 OPEN csr_find_contribution_code;
81 FETCH csr_find_contribution_code INTO l_contribution_code;
82 CLOSE csr_find_contribution_code;
83
84 OPEN csr_asg_status_type;
85 FETCH csr_asg_status_type INTO l_system_status;
86 CLOSE csr_asg_status_type;
87
88 FOR csr_extra_info IN csr_asg_status
89 LOOP
90 -- Checking for Assignment Status Type ID
91 IF (csr_extra_info.event = 'AS' AND csr_extra_info.value <> p_asg_status_type_id
92 AND l_system_status = 'ACTIVE_ASSIGN') THEN
93 IF p_effective_end_date >= fnd_date.canonical_to_date(csr_extra_info.last_reported_date) THEN
94 UPDATE per_assignment_extra_info
95 SET aei_information5 = 'Y'
96 ,aei_information7 = fnd_date.date_to_canonical(p_effective_start_date)
97 WHERE assignment_id = p_assignment_id
98 AND aei_information3 = 'AS'
99 AND aei_information_category = 'ES_SS_REP'
100 AND object_version_number = csr_extra_info.object_version_number;
101 END IF;
102 END IF;
103 -- Checking for Termination Status Type ID
104 IF (csr_extra_info.event = 'TS' AND csr_extra_info.value <> p_asg_status_type_id
105 AND l_system_status = 'TERM_ASSIGN') THEN
106 IF p_effective_end_date >= fnd_date.canonical_to_date(csr_extra_info.last_reported_date) THEN
107 UPDATE per_assignment_extra_info
108 SET aei_information5 = 'Y'
109 ,aei_information7 = fnd_date.date_to_canonical(p_effective_start_date-1)
110 ,aei_information4 = p_asg_status_type_id
111 WHERE assignment_id = p_assignment_id
112 AND aei_information3 = 'TS'
113 AND aei_information_category = 'ES_SS_REP'
114 AND object_version_number = csr_extra_info.object_version_number;
115 END IF;
116 END IF;
117
118 -- Checking for Employement Category
119 /* IF (csr_extra_info.event = 'EC' AND csr_extra_info.value <> nvl(p_employment_category, 'X')) THEN
120 IF p_effective_end_date >= fnd_date.canonical_to_date(csr_extra_info.last_reported_date) THEN
121 UPDATE per_assignment_extra_info
122 SET aei_information5 = 'Y'
123 ,aei_information7 = fnd_date.date_to_canonical(p_effective_start_date)
124 WHERE assignment_id = p_assignment_id
125 AND aei_information3 = 'EC'
126 AND aei_information_category = 'ES_SS_REP'
127 AND object_version_number = csr_extra_info.object_version_number;
128 END IF;
129 END IF;*/
130
131 -- Checking for contribution group
132 IF (csr_extra_info.event = 'CG' AND csr_extra_info.value <> nvl(l_contribution_code, 'X')) THEN
133 IF p_effective_end_date >= fnd_date.canonical_to_date(csr_extra_info.last_reported_date) THEN
134 UPDATE per_assignment_extra_info
135 SET aei_information5 = 'Y'
136 ,aei_information7 = fnd_date.date_to_canonical(p_effective_start_date)
137 WHERE assignment_id = p_assignment_id
138 AND aei_information3 = 'CG'
139 AND aei_information_category = 'ES_SS_REP'
140 AND object_version_number = csr_extra_info.object_version_number;
141 END IF;
142 END IF;
143 END LOOP;
144 END IF;
145 EXCEPTION
146 WHEN others THEN
147 NULL;
148 END asg_check_update;
149 -----------------------------------------------------
150 --asg_check_insert
151 ------------------------------------------------------
152 PROCEDURE asg_check_insert( p_assignment_id NUMBER,
153 p_assignment_type VARCHAR2,
154 p_effective_start_date DATE,
155 p_effective_end_date DATE,
156 p_asg_status_type_id NUMBER,
157 p_employment_category VARCHAR2,
158 p_soft_coding_keyflex_id NUMBER,
159 p_primary_flag VARCHAR) IS
160 CURSOR csr_check_asg IS
161 SELECT assignment_id
162 FROM per_assignment_extra_info
163 WHERE assignment_id = p_assignment_id;
164
165 l_ovn NUMBER;
166 l_csr_find_asg_id per_all_assignments_f.assignment_id%TYPE;
167 BEGIN
168 IF p_assignment_type = 'E' AND p_primary_flag = 'Y' THEN
169 /* Check whether there is any record in the table */
170 OPEN csr_check_asg;
171 FETCH csr_check_asg INTO l_csr_find_asg_id;
172 IF csr_check_asg%FOUND THEN
173 /* Call the update procedure to update the report type */
174 asg_check_update(p_assignment_id,
175 p_assignment_type,
176 p_effective_start_date,
177 p_effective_end_date,
178 p_asg_status_type_id,
179 p_employment_category,
180 p_soft_coding_keyflex_id,
181 p_primary_flag);
182 ELSE
183 /* Insert the records directly into the table because the api used to insert the values uses savepoint*/
184 /* savepoint cannot be used in triggers */
185 INSERT INTO per_assignment_extra_info
186 (assignment_extra_info_id,
187 assignment_id,
188 information_type,
189 aei_information_category,
190 aei_information2,
191 aei_information3,
192 aei_information4,
193 aei_information5,
194 aei_information6,
195 aei_information7,
196 object_version_number
197 )
198 VALUES
199 (per_assignment_extra_info_s.nextval,
200 p_assignment_id,
201 'ES_SS_REP',
202 'ES_SS_REP',
203 fnd_date.date_to_canonical(P_EFFECTIVE_START_DATE),
204 'AS',
205 p_asg_status_type_id,
206 'Y',
207 'I',
208 fnd_date.date_to_canonical(P_EFFECTIVE_START_DATE),
209 1
210 );
211 /* Inserting the Termination Status as One */
212 INSERT INTO per_assignment_extra_info
213 (assignment_extra_info_id,
214 assignment_id,
215 information_type,
216 aei_information_category,
217 aei_information2,
218 aei_information3,
219 aei_information4,
220 aei_information5,
221 aei_information6,
222 aei_information7,
223 object_version_number
224 )
225 VALUES
226 (per_assignment_extra_info_s.nextval,
227 p_assignment_id,
228 'ES_SS_REP',
229 'ES_SS_REP',
230 fnd_date.date_to_canonical(P_EFFECTIVE_START_DATE),
231 'TS',
232 p_asg_status_type_id,
233 'N',
234 'U',
235 fnd_date.date_to_canonical(P_EFFECTIVE_START_DATE),
236 1
237 );
238 /* INSERT INTO per_assignment_extra_info
239 (assignment_extra_info_id,
240 assignment_id,
241 information_type,
242 aei_information_category,
243 aei_information2,
244 aei_information3,
245 aei_information4,
246 aei_information5,
247 aei_information6,
248 aei_information7,
249 object_version_number
250 )
251 VALUES
252 (per_assignment_extra_info_s.nextval,
253 p_assignment_id,
254 'ES_SS_REP',
255 'ES_SS_REP',
256 fnd_date.date_to_canonical(P_EFFECTIVE_START_DATE),
257 'EC',
258 p_employment_category,
259 'Y',
260 'I',
261 fnd_date.date_to_canonical(P_EFFECTIVE_START_DATE),
262 1
263 );
264 */
265 INSERT INTO per_assignment_extra_info
266 (assignment_extra_info_id,
267 assignment_id,
268 information_type,
269 aei_information_category,
270 aei_information2,
271 aei_information3,
272 aei_information4,
273 aei_information5,
274 aei_information6,
275 aei_information7,
276 object_version_number
277 )
278 VALUES
279 (per_assignment_extra_info_s.nextval,
280 p_assignment_id,
281 'ES_SS_REP',
282 'ES_SS_REP',
283 fnd_date.date_to_canonical(P_EFFECTIVE_START_DATE),
284 'CG',
285 NULL,
286 'Y',
287 'I',
288 fnd_date.date_to_canonical(P_EFFECTIVE_START_DATE),
289 1
290 );
291 END IF;
292 CLOSE csr_check_asg;
293 END IF;
294 EXCEPTION
295 WHEN OTHERS THEN
296 NULL;
297 END asg_check_insert;
298 ----------------------------------------------------------------
299 --ELEMENT_CHECK_INSERT
300 ----------------------------------------------------------------
301 PROCEDURE element_check_insert(p_element_entry_id NUMBER,
302 p_effective_start_date DATE,
303 p_effective_end_date DATE,
304 p_epigraph_code VARCHAR2,
305 p_input_value_id NUMBER) AS
306
307 CURSOR csr_chk_element_eit(p_assignment_id NUMBER) IS
308 SELECT assignment_extra_info_id
309 FROM per_assignment_extra_info
310 WHERE assignment_id = p_assignment_id
311 AND information_type = 'ES_SS_REP'
312 AND aei_information3 IN ('EP','EC');
313
314 CURSOR csr_input_value_id(p_name VARCHAR2) IS
315 SELECT input_value_id
316 FROM pay_input_values_f piv
317 WHERE piv.name = p_name
318 AND legislation_code = 'ES';
319
320 CURSOR csr_primary_flag_value(p_assignment_id NUMBER
321 ,p_effective_start_date DATE) IS
322 SELECT paf.primary_flag, paf.assignment_type
323 FROM per_all_assignments_f paf
324 WHERE paf.assignment_id = p_assignment_id
325 AND p_effective_start_date BETWEEN paf.effective_start_date
326 AND paf.effective_end_date;
327
328 l_assg_id pay_element_entries_f.assignment_id%TYPE;
329 l_assg_info_id per_assignment_extra_info.assignment_extra_info_id%TYPE;
330 l_input_value_id pay_input_values_f.input_value_id%TYPE;
331 l_primary_flag per_all_assignments_f.primary_flag%TYPE;
332 l_assignment_type per_all_assignments_f.assignment_type%TYPE;
333
334 BEGIN
335 OPEN csr_input_value_id('SS Epigraph Code');
336 FETCH csr_input_value_id INTO l_input_value_id;
337 CLOSE csr_input_value_id;
338
339 IF p_input_value_id = l_input_value_id THEN
340 l_assg_id := PER_ES_SS_REP_DYN_TRG.get_assignment_id(p_element_entry_id);
341 OPEN csr_primary_flag_value(l_assg_id,p_effective_start_date);
342 FETCH csr_primary_flag_value INTO l_primary_flag,l_assignment_type ;
343 CLOSE csr_primary_flag_value;
344 IF l_assignment_type = 'E' AND l_primary_flag = 'Y' THEN
345 OPEN csr_chk_element_eit(l_assg_id);
346 FETCH csr_chk_element_eit INTO l_assg_info_id;
347 IF csr_chk_element_eit%FOUND THEN
348 element_check_update(p_element_entry_id,
349 p_effective_start_date,
350 p_effective_end_date,
351 p_epigraph_code,
352 p_input_value_id);
353 ELSE
354 /*inserting for EPIGRAPH CODE EP*/
355 INSERT INTO per_assignment_extra_info
356 (assignment_extra_info_id,
357 assignment_id,
358 information_type,
359 aei_information_category,
360 aei_information2,
361 aei_information3,
362 aei_information4,
363 aei_information5,
364 aei_information6,
365 aei_information7,
366 object_version_number
367 )
368 VALUES
369 (per_assignment_extra_info_s.nextval,
370 l_assg_id,
371 'ES_SS_REP',
372 'ES_SS_REP',
373 fnd_date.date_to_canonical(P_EFFECTIVE_START_DATE),
374 'EP',
375 NULL,
376 'Y',
377 'I',
378 fnd_date.date_to_canonical(P_EFFECTIVE_START_DATE),
379 1
380 );
381 /*Inserting for contract key change*/
382 INSERT INTO per_assignment_extra_info
383 (assignment_extra_info_id,
384 assignment_id,
385 information_type,
386 aei_information_category,
387 aei_information2,
388 aei_information3,
389 aei_information4,
390 aei_information5,
391 aei_information6,
392 aei_information7,
393 object_version_number
394 )
395 VALUES
396 (per_assignment_extra_info_s.nextval,
397 l_assg_id,
398 'ES_SS_REP',
399 'ES_SS_REP',
400 fnd_date.date_to_canonical(P_EFFECTIVE_START_DATE),
401 'EC',
402 NULL,
403 'Y',
404 'I',
405 fnd_date.date_to_canonical(P_EFFECTIVE_START_DATE),
406 1
407 );
408 END IF;
409 CLOSE csr_chk_element_eit;
410 END IF;
411 END IF;
412 END element_check_insert;
413 -----------------------------------------------------------------
414 --ELEMENT_CHECK_UPDATE
415 ------------------------------------------------------------------
416 PROCEDURE element_check_update(p_element_entry_id NUMBER,
417 p_effective_start_date DATE,
418 p_effective_end_date DATE,
419 p_epigraph_code VARCHAR2,
420 p_input_value_id NUMBER) AS
421
422 CURSOR csr_get_eit_value(p_assignment_id NUMBER
423 ,p_event_type VARCHAR2) IS
424 SELECT assignment_extra_info_id
425 ,object_version_number
426 ,aei_information2 last_reported_date
427 ,nvl(aei_information4,'X') value
428 ,aei_information6 action_type
429 ,aei_information7 last_changed_date
430 FROM per_assignment_extra_info
431 WHERE assignment_id = p_assignment_id
432 AND information_type = 'ES_SS_REP'
433 AND aei_information3 = p_event_type
434 AND aei_information5 <> 'Y';
435
436 CURSOR csr_input_value_id(p_name VARCHAR2) IS
437 SELECT input_value_id
438 FROM pay_input_values_f piv
439 WHERE piv.name = p_name
440 AND legislation_code = 'ES';
441
442 CURSOR csr_primary_flag_value(p_assignment_id NUMBER
443 ,p_effective_start_date DATE) IS
444 SELECT paf.primary_flag, paf.assignment_type
445 FROM per_all_assignments_f paf
446 WHERE paf.assignment_id = p_assignment_id
447 AND p_effective_start_date BETWEEN paf.effective_start_date
448 AND paf.effective_end_date;
449
450 l_assignment_id pay_element_entries_f.assignment_id%type;
451 l_input_value_id pay_input_values_f.input_value_id%type;
452 l_primary_flag per_all_assignments_f.primary_flag%TYPE;
453 l_assignment_type per_all_assignments_f.assignment_type%TYPE;
454
455 BEGIN
456 OPEN csr_input_value_id('SS Epigraph Code');
457 FETCH csr_input_value_id INTO l_input_value_id;
458 CLOSE csr_input_value_id;
459 IF p_input_value_id = l_input_value_id THEN
460 l_assignment_id := PER_ES_SS_REP_DYN_TRG.get_assignment_id(P_ELEMENT_ENTRY_ID);
461 OPEN csr_primary_flag_value(l_assignment_id,p_effective_start_date);
462 FETCH csr_primary_flag_value INTO l_primary_flag,l_assignment_type ;
463 CLOSE csr_primary_flag_value;
464 IF l_assignment_type = 'E' AND l_primary_flag = 'Y' THEN
465 FOR csr_extra_info IN csr_get_eit_value(l_assignment_id,'EP')
466 LOOP
467 IF csr_extra_info.value <> p_epigraph_code THEN
468 IF p_effective_end_date >= fnd_date.canonical_to_date(csr_extra_info.last_reported_date) THEN
469 UPDATE per_assignment_extra_info
470 SET aei_information5 = 'Y'
471 ,aei_information7 = fnd_date.date_to_canonical(p_effective_start_date)
472 WHERE assignment_id = l_assignment_id
473 AND aei_information_category = 'ES_SS_REP'
474 AND aei_information3 = 'EP'
475 AND object_version_number = csr_extra_info.object_version_number;
476 END IF;
477 END IF;
478 END LOOP;
479 END IF;
480 END IF;
481 --
482 OPEN csr_input_value_id('Contract Key');
483 FETCH csr_input_value_id INTO l_input_value_id;
484 CLOSE csr_input_value_id;
485 IF p_input_value_id = l_input_value_id THEN
486 l_assignment_id := PER_ES_SS_REP_DYN_TRG.get_assignment_id(P_ELEMENT_ENTRY_ID);
487 OPEN csr_primary_flag_value(l_assignment_id,p_effective_start_date);
488 FETCH csr_primary_flag_value INTO l_primary_flag,l_assignment_type ;
489 CLOSE csr_primary_flag_value;
490 IF l_assignment_type = 'E' AND l_primary_flag = 'Y' THEN
491 FOR csr_extra_info IN csr_get_eit_value(l_assignment_id,'EC')
492 LOOP
493 IF csr_extra_info.value <> p_epigraph_code THEN
494 IF p_effective_end_date >= fnd_date.canonical_to_date(csr_extra_info.last_reported_date) THEN
495 UPDATE per_assignment_extra_info
496 SET aei_information5 = 'Y'
497 ,aei_information7 = fnd_date.date_to_canonical(p_effective_start_date)
498 WHERE assignment_id = l_assignment_id
499 AND aei_information_category = 'ES_SS_REP'
500 AND aei_information3 = 'EC'
501 AND object_version_number = csr_extra_info.object_version_number;
502 END IF;
503 END IF;
504 END LOOP;
505 END IF;
506 END IF;
507 END element_check_update;
508 END PER_ES_SS_REP_DYN_TRG;