[Home] [Help]
PACKAGE BODY: APPS.PER_PL_ADDRESS
Source
1 PACKAGE body per_pl_address AS
2 /* $Header: pepllhpa.pkb 120.1 2006/09/13 12:34:12 mseshadr noship $ */
3 g_package VARCHAR2(33) := 'per_pl_address.';
4 --
5 PROCEDURE check_address_unique
6 ( p_address_ID NUMBER ,
7 p_address_type VARCHAR2,
8 p_date_from DATE,
9 p_date_to DATE,
10 p_person_id NUMBER,
11 p_pradd_ovlapval_override in boolean default FALSE) --Added for Bug 4210646
12 is
13 --
14 l_status VARCHAR2(1);
15 g_last_date DATE ;
16 local_warning EXCEPTION;
17 p_override VARCHAR2(8) := 'FALSE';
18 BEGIN
19 g_last_date := to_date('31-12-4712','DD-MM-YYYY');
20
21 --Added If clause for Bug 4210646
22
23 If p_pradd_ovlapval_override Then
24 p_override := 'TRUE';
25 End if;
26
27 --
28 SELECT 'Y'
29 INTO l_status
30 FROM sys.dual
31 WHERE exists(SELECT '1'
32 FROM per_addresses pp
33 WHERE (p_address_id IS NULL
34 OR p_address_id <> pp.address_id)
35 AND p_person_id = pp.person_id
36 AND p_address_type = pp.address_type
37 AND (
38 p_date_from between pp.date_from and nvl(pp.date_to,g_last_date )
39 OR
40 nvl(p_date_to,g_last_date) between pp.date_from and nvl(pp.date_to,g_last_date)
41 OR
42 pp.date_from between p_date_from and nvl(p_date_to,g_last_date )
43 )
44 AND p_override = 'FALSE'
45 );
46 --
47 hr_utility.set_message(800,'HR_PL_DUPLICATE_ADDRESS');
48 hr_utility.raise_error;
49 --
50 EXCEPTION
51 WHEN NO_DATA_FOUND THEN NULL;
52 END check_address_unique;
53
54
55
56
57 PROCEDURE check_pl_address (
58 p_address_ID NUMBER ,
59 p_address_type VARCHAR2,
60 p_date_from DATE,
61 p_date_to DATE,
62 p_person_id NUMBER,
63 p_address_line1 IN VARCHAR2,
64 p_address_line2 IN VARCHAR2,
65 p_pradd_ovlapval_override in boolean --Added for Bug 4210646
66 ) IS
67 --
68 l_return Varchar2(30);
69 --
70 BEGIN
71
72
73 --
74 -- Check that Address type is entered
75 --
76 IF ( p_address_type IS NULL ) THEN
77 hr_utility.set_message(800,'HR_PL_ADDRESS_TYPE_NULL');
78 hr_utility.raise_error;
79 END IF;
80
81 --
82 -- Check that Street Name is entered when street type is entered
83 --
84 IF ( p_address_line1 IS NOT NULL ) THEN
85 IF ( p_address_line2 IS NULL ) THEN
86 hr_utility.set_message(800,'HR_PL_ST_NAME_NOT_SPEC');
87 hr_utility.raise_error;
88 END IF;
89 END IF;
90 --
91
92 check_address_unique
93 ( p_address_ID ,
94 p_address_type ,
95 p_date_from ,
96 p_date_to ,
97 p_person_id ,
98 p_pradd_ovlapval_override ); --Added for Bug 4210646
99 --
100 --
101 END check_pl_address;
102 --
103 --
104 --
105 PROCEDURE create_pl_address (p_style IN VARCHAR2,
106 p_address_type VARCHAR2,
107 p_date_from DATE,
108 p_date_to DATE,
109 p_person_id NUMBER,
110 p_address_line1 IN VARCHAR2,
111 p_address_line2 IN VARCHAR2,
112 p_pradd_ovlapval_override in boolean --Added for Bug 4210646
113 )
114 IS
115 BEGIN
116 --
117 /* Added for GSI Bug 5472781 */
118 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'PL') THEN
119 hr_utility.trace('PL not installed.Leaving create_pl_address');
120 return;
121 END IF;
122 IF p_style = 'PL' THEN
123 per_pl_address.check_pl_address(
124 p_address_ID => NULL ,
125 p_address_type => p_address_type,
126 p_date_from => p_date_from ,
127 p_date_to => p_date_to ,
128 p_person_id => p_person_id,
129 p_address_line1 => p_address_line1,
130 p_address_line2 => p_address_line2,
131 p_pradd_ovlapval_override => p_pradd_ovlapval_override); --Added for Bug 4210646
132 END IF;
133 --
134 END create_pl_address;
135 --
136 --
137 PROCEDURE update_pl_address (p_address_id IN NUMBER,
138 p_address_type VARCHAR2,
139 p_date_from DATE,
140 p_date_to DATE,
141 p_address_line1 IN VARCHAR2,
142 p_address_line2 IN VARCHAR2)
143 IS
144 --
145 CURSOR get_style(p_address_id number) is
146 SELECT style,person_id
147 FROM per_addresses
148 WHERE address_id=p_address_id;
149 --
150 l_style per_addresses.style%TYPE;
151 l_person_id per_addresses.person_id%TYPE;
152 --
153 BEGIN
154 --
155 /* Added for GSI Bug 5472781 */
156 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'PL') THEN
157 hr_utility.trace('PL not installed.Leaving update_pl_address');
158 return;
159 END IF;
160 OPEN get_style(p_address_id);
161 FETCH get_style INTO l_style,l_person_id;
162 CLOSE get_style;
163 IF l_style='PL' THEN
164 per_pl_address.check_pl_address(
165 p_address_ID => p_address_id ,
166 p_address_type => p_address_type,
167 p_date_from => p_date_from ,
168 p_date_to => p_date_to ,
169 p_person_id => l_person_id ,
170 p_address_line1 => p_address_line1,
171 p_address_line2 => p_address_line2,
172 p_pradd_ovlapval_override => null); --Added for Bug 4210646
173 END IF;
174
175 --
176 END update_pl_address;
177 --
178
179 PROCEDURE update_pl_address_style(p_address_id IN NUMBER,
180 p_style IN VARCHAR2,
181 p_address_type VARCHAR2,
182 p_date_from DATE,
183 p_date_to DATE,
184 p_address_line1 IN VARCHAR2,
185 p_address_line2 IN VARCHAR2)
186 IS
187 --
188 l_person_id per_addresses.person_id%TYPE;
189 --
190 BEGIN
191 --
192 /* Added for GSI Bug 5472781 */
193 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'PL') THEN
194 hr_utility.trace('PL not installed.Leaving update_pl_address_style');
195 return;
196 END IF;
197 IF p_style='PL' THEN
198 per_pl_address.check_pl_address(
199 p_address_ID => p_address_id ,
200 p_address_type => p_address_type,
201 p_date_from => p_date_from ,
202 p_date_to => p_date_to ,
203 p_person_id => l_person_id ,
204 p_address_line1 => p_address_line1,
205 p_address_line2 => p_address_line2,
206 p_pradd_ovlapval_override => null); --Added for Bug 4210646
207 END IF;
208 --
209 END update_pl_address_style;
210 --
211
212 END per_pl_address;