1 PACKAGE BODY PER_US_ADD_LEG_HOOK AS
2 /* $Header: peusaddlh.pkb 120.4 2011/03/25 11:20:36 nkjaladi noship $ */
3
4 PROCEDURE CREATE_US_ADDRESS
5 (P_STYLE in varchar2
6 ,P_PRIMARY_FLAG in varchar2
7 ,p_person_id in number
8 ,P_EFFECTIVE_DATE in DATE
9 )
10 AS
11 l_assignment_id per_assignments_f.assignment_id%TYPE;
12
13 cursor csr_emp_assign(p_person_id number) is
14 select asg.assignment_id
15 from per_assignments_f asg
16 where asg.person_id = p_person_id
17 and asg.assignment_type='E'
18 and asg.payroll_id is not null /*bug 10008870 */
19 and p_effective_date between asg.effective_start_date
20 and asg.effective_end_date;
21 BEGIN
22 --commented for 9345079
23 -- 9881571: Added Payroll product check
24 --
25
26 /* bug 10008870: Address creation validation to check if business group is of 'US'
27 legislation, payroll application is installed and payroll is assigned to
28 assignment before raising the error of 'Address_Cannot_be_Primary'. If
29 payroll is assigned to any of the assignment then we need to raise this error
30 otherwise allow the creation of the address. */
31
32 /* Bug 11906210: Need to check the legislation installation instead of
33 product installation*/
34 open csr_emp_assign(p_person_id);
35 fetch csr_emp_assign into l_assignment_id;
36 IF (p_style <> 'US'
37 AND P_PRIMARY_FLAG = 'Y'
38 AND csr_emp_assign%FOUND
39 --AND hr_general.chk_product_installed('801') = 'TRUE'
40 AND hr_general2.is_legislation_install('PAY','US')
41 ) THEN
42 close csr_emp_assign;
43 hr_utility.set_message (800, 'Address_Cannot_be_Primary');
44 hr_utility.raise_error;
45 END IF;
46 close csr_emp_assign;
47 END CREATE_US_ADDRESS;
48
49 PROCEDURE UPDATE_US_ADDRESS
50 (P_STYLE in varchar2
51 ,P_PRIMARY_FLAG in varchar2
52 ,p_address_id in number
53 ,P_EFFECTIVE_DATE in DATE
54 )
55 AS
56
57 l_assignment_id per_assignments_f.assignment_id%TYPE;
58
59 cursor csr_emp_assign(p_address_id number) is
60 select asg.assignment_id
61 from per_assignments_f asg,
62 per_addresses padd
63 where asg.person_id = padd.person_id
64 and padd.address_id = p_address_id
65 and asg.assignment_type='E'
66 and asg.payroll_id is not null /*bug 10008870 */
67 and p_effective_date between padd.date_from
68 and nvl(padd.date_to,hr_api.g_eot)
69 and p_effective_date between asg.effective_start_date
70 and asg.effective_end_date;
71 BEGIN
72 /* bug: 10008870 */
73 /* Bug 11906210: Need to check the legislation installation instead of
74 product installation*/
75 open csr_emp_assign(p_address_id);
76 fetch csr_emp_assign into l_assignment_id;
77 IF (p_style <> 'US'
78 AND P_PRIMARY_FLAG = 'Y'
79 AND csr_emp_assign%FOUND
80 --AND hr_general.chk_product_installed('801') = 'TRUE'
81 AND hr_general2.is_legislation_install('PAY','US')
82 ) THEN
83 close csr_emp_assign;
84 hr_utility.set_message (800, 'Address_Cannot_be_Primary');
85 hr_utility.raise_error;
86 END IF;
87 close csr_emp_assign;
88
89 null;
90 END UPDATE_US_ADDRESS;
91
92 END PER_US_ADD_LEG_HOOK;
93