1 PACKAGE BODY PER_SG_ASG_LEG_HOOK AS
2 /* $Header: pesglhas.pkb 120.0.12020000.2 2012/08/29 01:28:26 mingyhua noship $ */
3
4 PROCEDURE UPDATE_SG_ASG
5 (p_datetrack_update_mode IN VARCHAR2
6 ,p_assignment_id IN NUMBER
7 ,p_effective_date IN DATE
8 ,p_segment1 IN VARCHAR2
9 ) AS
10
11 CURSOR c_tax_unit_id
12 (c_assignment_id per_all_assignments_f.assignment_id%TYPE
13 ,c_effective_date DATE
14 ) IS
15 SELECT hsc.segment1, business_group_id
16 FROM per_all_assignments_f a
17 , hr_soft_coding_keyflex hsc
18 WHERE a.assignment_id = c_assignment_id
19 AND hsc.soft_coding_keyflex_id = a.soft_coding_keyflex_id
20 AND c_effective_date BETWEEN a.effective_start_date AND a.effective_end_date;
21
22 g_debug BOOLEAN;
23 l_bg_id per_business_groups.business_group_id%TYPE;
24 l_future_actions_exist VARCHAR2(10);
25 l_actions_exist VARCHAR2(10);
26 l_tax_unit_id hr_soft_coding_keyflex.segment1%TYPE;
27
28 /* This Cursor Checks returns 'YES' when the LE of
29 the given assignment is changed and there exists
30 actions in the period or in future periods */
31
32 CURSOR c_check_future_actions
33 (c_assignment_id per_all_assignments_f.assignment_id%TYPE
34 ,c_bg_id per_business_groups.business_group_id%TYPE
35 ,c_effective_date DATE) IS
36 SELECT 'YES'
37 FROM pay_payroll_actions ppa
38 ,pay_assignment_actions paa
39 WHERE paa.assignment_id = c_assignment_id
40 and ppa.payroll_action_id = paa.payroll_action_id
41 and ppa.business_group_id = c_bg_id
42 and ppa.action_type in ('I','B','V','R','Q')
43 and ppa.action_status = 'C'
44 and last_day(ppa.effective_date) >= last_day(c_effective_date);
45
46 /* This Cursor Checks returns 'YES' when the LE of
47 the given assignment is changed and there exists
48 any actions later than the effective start date
49 of the employee assignment */
50
51 CURSOR c_check_actions
52 (c_assignment_id per_all_assignments_f.assignment_id%TYPE
53 ,c_bg_id per_business_groups.business_group_id%TYPE
54 ,c_effective_date DATE) IS
55 SELECT 'YES'
56 FROM pay_payroll_actions ppa
57 ,pay_assignment_actions paa
58 WHERE paa.assignment_id = c_assignment_id
59 AND ppa.business_group_id = c_bg_id
60 AND ppa.payroll_action_id = paa.payroll_action_id
61 AND ppa.action_type in ('I','B','V','R','Q')
62 AND ppa.action_status = 'C'
63 AND ppa.effective_date >= (SELECT effective_start_date
64 FROM per_all_assignments_f
65 WHERE assignment_id=c_assignment_id
66 AND business_group_id=c_bg_id
67 AND c_effective_date BETWEEN effective_start_date AND effective_end_date );
68 BEGIN
69 g_debug := hr_utility.debug_enabled;
70
71 IF g_debug THEN
72 hr_utility.set_location('Start of PER_SG_ASG_LEG_HOOK.UPDATE_SG_ASG',1);
73 END IF;
74
75 OPEN c_tax_unit_id(p_assignment_id, p_effective_date);
76 FETCH c_tax_unit_id into l_tax_unit_id, l_bg_id;
77 CLOSE c_tax_unit_id;
78
79 IF p_segment1 <> hr_api.g_varchar2 THEN
80 IF p_segment1 <> l_tax_unit_id THEN
81 OPEN c_check_future_actions(p_assignment_id, l_bg_id, p_effective_date);
82 FETCH c_check_future_actions into l_future_actions_exist;
83
84 IF c_check_future_actions%NOTFOUND then
85 l_future_actions_exist:= 'NO';
86 END IF;
87 CLOSE c_check_future_actions;
88
89 OPEN c_check_actions(p_assignment_id, l_bg_id, p_effective_date);
90 FETCH c_check_actions into l_actions_exist;
91 IF c_check_actions%NOTFOUND then
92 l_actions_exist:= 'NO';
93 END IF;
94 CLOSE c_check_actions;
95
96 IF l_actions_exist = 'YES' THEN
97 IF (l_future_actions_exist <> 'YES' AND p_datetrack_update_mode = 'UPDATE') THEN
98 NULL;
99 ELSE
100 IF p_datetrack_update_mode = 'CORRECTION' THEN
101 hr_utility.set_message(801, 'HR_SG_LEGAL_EMP_CHANGE2');
102 ELSE
103 hr_utility.set_message(801, 'HR_SG_LEGAL_EMP_CHANGE1');
104 END IF;
105 hr_utility.raise_error;
106
107 IF g_debug THEN
108 hr_utility.set_location('End of PER_SG_ASG_LEG_HOOK.UPDATE_SG_ASG', 2);
109 END IF;
110 END IF;
111 END IF;
112 END IF;
113 END IF;
114
115 EXCEPTION
116 WHEN OTHERS THEN
117 IF g_debug THEN
118 hr_utility.set_location('Error in PER_SG_ASG_LEG_HOOK.UPDATE_SG_ASG', 100);
119 END IF;
120 RAISE;
121
122
123 END UPDATE_SG_ASG ;
124
125 END PER_SG_ASG_LEG_HOOK;
[Home] [Help]