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