1 PACKAGE BODY PER_AU_ASG_LEG_HOOK AS
2 /* $Header: peaulhas.pkb 120.2 2006/07/07 13:43:24 ksingla noship $ */
3
4 PROCEDURE UPDATE_AU_ASG
5 (p_assignment_id IN NUMBER
6 ,p_effective_date IN DATE
7 ,p_segment1 IN VARCHAR2
8 ) AS
9
10 CURSOR c_check_actions
11 (c_assignment_id per_all_assignments_f.assignment_id%TYPE
12 ,c_effective_date DATE
13 ) IS
14 SELECT 'Y'
15 FROM per_all_assignments_f paaf
16 , pay_payroll_actions ppa
17 , pay_assignment_actions paa
18 WHERE paaf.assignment_id = c_assignment_id
19 AND paaf.assignment_id = paa.assignment_id
20 AND paaf.business_group_id = ppa.business_group_id
21 AND ppa.payroll_action_id = paa.payroll_action_id
22 AND ppa.effective_date >= (SELECT ptp.start_date
23 FROM per_time_periods ptp
24 WHERE ptp.payroll_id = ppa.payroll_id
25 AND c_effective_date BETWEEN ptp.start_date AND ptp.end_date)
26 and ppa.action_type IN ('Q','R','B','I','V');
27
28 CURSOR c_tax_unit_id
29 (c_assignment_id per_all_assignments_f.assignment_id%TYPE
30 ,c_effective_date DATE
31 ) IS
32 SELECT hsc.segment1
33 FROM per_all_assignments_f a
34 , hr_soft_coding_keyflex hsc
35 WHERE a.assignment_id = c_assignment_id
36 AND hsc.soft_coding_keyflex_id = a.soft_coding_keyflex_id
37 AND c_effective_date BETWEEN a.effective_start_date AND a.effective_end_date;
38 g_debug BOOLEAN;
39 l_org_id NUMBER;
40 l_eff_start_date DATE;
41 l_eff_end_date DATE;
42 l_business_group_id NUMBER;
43 l_actions_exist VARCHAR2(3);
44 l_tax_unit_id hr_soft_coding_keyflex.segment1%type;
45 BEGIN
46 g_debug := hr_utility.debug_enabled;
47
48 IF g_debug THEN
49 hr_utility.set_location('Start of PER_AU_ASG_LEG_HOOK.UPDATE_AU_ASG',1);
50 END if;
51
52 OPEN c_tax_unit_id( p_assignment_id,p_effective_date);
53 FETCH c_tax_unit_id into l_tax_unit_id;
54 CLOSE c_tax_unit_id;
55
56 IF p_segment1 <> hr_api.g_varchar2 THEN /* Added for bug 5375920*/
57 IF p_segment1 <> l_tax_unit_id THEN
58 OPEN c_check_actions(p_assignment_id, p_effective_date);
59 FETCH c_check_actions into l_actions_exist;
60 CLOSE c_check_actions;
61
62 IF l_actions_exist = 'Y' THEN
63 hr_utility.set_message(801, 'HR_AU_LE_CHANGE_VAL');
64 hr_utility.raise_error;
65 END IF;
66 End if;
67 END IF;
68
69 IF g_debug THEN
70 hr_utility.set_location('End of PER_AU_ASG_LEG_HOOK.UPDATE_AU_ASG',2);
71 END if;
72 EXCEPTION
73 WHEN OTHERS THEN
74 IF g_debug THEN
75 hr_utility.set_location('Error in PER_AU_ASG_LEG_HOOK.UPDATE_AU_ASG',100);
76 END IF;
77 RAISE;
78
79
80 END UPDATE_AU_ASG ;
81
82 END PER_AU_ASG_LEG_HOOK;