[Home] [Help]
PACKAGE BODY: APPS.PER_CN_ADD_LEG_HOOK
Source
1 PACKAGE BODY per_cn_add_leg_hook AS
2 /* $Header: pecnlhpa.pkb 115.8 2004/01/14 04:32:35 sshankar noship $ */
3 --
4 --
5 g_trace boolean;
6
7 --------------------------------------------------------------------------
8 -- Name : CHECK_ADDRESS --
9 -- Type : Procedure --
10 -- Access : Public --
11 -- Description : Procedure is the driver procedure for the validation--
12 -- of the address. --
13 -- This procedure is the hook procedure for the --
14 -- address. --
15 -- Parameters : --
16 -- IN : p_address_line1 IN VARCHAR2 --
17 -- p_town_or_city IN VARCHAR2 --
18 -- p_country IN VARCHAR2 --
19 -- p_postal_code IN VARCHAR2 --
20 -- --
21 -- Change History : --
22 --------------------------------------------------------------------------
23 -- Rev# Date Userid Description --
24 --------------------------------------------------------------------------
25 -- 1.0 29/11/02 saikrish Created this procedure --
26 -- 1.1 10/04/03 statkar Nulled out this procedure - 2893334 --
27 --------------------------------------------------------------------------
28 PROCEDURE check_address(p_address_line1 IN VARCHAR2
29 ,p_town_or_city IN VARCHAR2
30 ,p_country IN VARCHAR2
31 ,p_postal_code IN VARCHAR2)
32 AS
33 BEGIN
34 --
35 -- bug 2893334: Need to change the script to handle address style
36 -- However, the address leg hook seeding script (pecnpadd.sql) calls
37 -- CHECK_ADDRESS. This procedure is kept here for allowing compatibility
38 -- with that procedure. This procedure does nothing and is replaced with
39 -- CHECK_ADDRESS_UPD and CHECK_ADDRESS_INS for calls through the resp. APIS
40
41 NULL;
42
43 END check_address;
44
45 --------------------------------------------------------------------------
46 -- Name : CHECK_ADDRESS --
47 -- Type : Procedure --
48 -- Access : Private --
49 -- Description : Procedure is the driver procedure for the validation--
50 -- of the address. --
51 -- This procedure is the hook procedure for the --
52 -- address. --
53 -- Parameters : --
54 -- IN : p_style IN VARCHAR2 --
55 -- p_address_line1 IN VARCHAR2 --
56 -- p_town_or_city IN VARCHAR2 --
57 -- p_country IN VARCHAR2 --
58 -- p_postal_code IN VARCHAR2 --
59 -- --
60 -- Change History : --
61 --------------------------------------------------------------------------
62 -- Rev# Date Userid Description --
63 --------------------------------------------------------------------------
64 -- 1.0 09/04/03 bramajey Created this procedure --
65 -- 1.1 19/09/03 statkar Added the hr_utility.chk_product_install --
66 -- check for installed CN leg (3145322) --
67 -- 1.2 31/10/03 bramajey Changed value of token LOW in message --
68 -- HR_374603_INVALID_RANGE from 100000 to --
69 -- 000001 for bug 3226285 --
70 --------------------------------------------------------------------------
71 PROCEDURE check_address(p_style IN VARCHAR2
72 ,p_address_line1 IN VARCHAR2
73 ,p_town_or_city IN VARCHAR2
74 ,p_country IN VARCHAR2
75 ,p_postal_code IN VARCHAR2) AS
76
77 l_proc VARCHAR2(72) := g_package||'check_address';
78 l_territory_code fnd_territories_vl.territory_code%TYPE;
79
80 CURSOR csr_country (p_country VARCHAR2) IS
81 SELECT territory_code
82 FROM fnd_territories_vl
83 WHERE territory_code = p_country;
84
85 BEGIN
86
87 g_trace := hr_cn_person_address_api.g_trace;
88
89 hr_cn_api.set_location(g_trace,'Entering: '||l_proc,10);
90
91 --
92 -- Bug 3145322 Check the leg-specific validations only if the legislation
93 -- is installed
94 --
95 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'CN') THEN
96 hr_utility.trace ('CN Legislation not installed. Not performing the validations');
97 hr_cn_api.set_location(g_trace,'Leaving: '||l_proc, 15);
98 RETURN;
99 END IF;
100
101 if p_style = 'CN_GLB' then
102 --
103 -- Check for the mandatory values
104 --
105 hr_api.mandatory_arg_error
106 (p_api_name => l_proc,
107 p_argument => 'P_ADDRESS_LINE1',
108 p_argument_value => p_address_line1
109 );
110
111 hr_cn_api.set_location (g_trace, l_proc, 20);
112 hr_api.mandatory_arg_error
113 (p_api_name => l_proc,
114 p_argument => 'P_COUNTRY',
115 p_argument_value => p_country
116 );
117
118 hr_cn_api.set_location (g_trace, l_proc, 30);
119 hr_api.mandatory_arg_error
120 (p_api_name => l_proc,
121 p_argument => 'P_PROVINCE_CITY_SAR',
122 p_argument_value => p_town_or_city
123 );
124
125 hr_cn_api.set_location (g_trace, l_proc, 30);
126 hr_api.mandatory_arg_error
127 (p_api_name => l_proc,
128 p_argument => 'P_POSTAL_CODE',
129 p_argument_value => p_postal_code
130 );
131 --
132 -- Check the valid values for country
133 --
134 hr_cn_api.set_location (g_trace, l_proc, 40);
135 IF p_country <> hr_api.g_varchar2 THEN
136 OPEN csr_country(p_country);
137 FETCH csr_country INTO l_territory_code;
138 IF csr_country%NOTFOUND THEN
139 hr_cn_api.set_location (g_trace, l_proc, 50);
140 CLOSE csr_country;
141 hr_utility.set_message(800,'HR_374602_INVALID_VALUE');
142 hr_utility.set_message_token('VALUE',p_country);
143 hr_utility.set_message_token('FIELD','P_COUNTRY');
144 hr_utility.raise_error;
145 END IF;
146 END IF;
147 --
148 -- Check the valid lookup values
149 --
150 hr_cn_api.set_location (g_trace, l_proc, 60);
151 IF p_town_or_city <> hr_api.g_varchar2 THEN
152 hr_cn_api.check_lookup (
153 p_lookup_type => 'CN_PROVINCE',
154 p_argument => 'P_PROVINCE_CITY_SAR',
155 p_argument_value => p_town_or_city
156 );
157 END IF;
158
159 hr_cn_api.set_location (g_trace, l_proc, 90);
160 IF p_postal_code <> hr_api.g_varchar2 THEN
161 IF NOT hr_cn_api.is_valid_postal_code(p_postal_code) THEN
162 hr_cn_api.set_location (g_trace, l_proc, 100);
163 hr_utility.set_message(800,'HR_374603_INVALID_RANGE');
164 hr_utility.set_message_token('NUMBER','P_POSTAL_CODE');
165 -- Bug 3226285
166 hr_utility.set_message_token('LOW','000001');
167 hr_utility.set_message_token('HIGH','999999');
168 hr_utility.raise_error;
169 END IF;
170 END IF;
171
172 END IF;
173
174 hr_cn_api.set_location (g_trace, 'Leaving: '||l_proc, 100);
175
176 EXCEPTION
177 WHEN OTHERS THEN
178 IF csr_country%ISOPEN THEN
179 CLOSE csr_country;
180 END IF;
181 RAISE;
182
183 END check_address;
184
185 --------------------------------------------------------------------------
186 -- --
187 -- Name : CHECK_ADDRESS_INS --
188 -- Type : Procedure --
189 -- Access : Public --
190 -- Description : Procedure is the driver procedure for the validation--
191 -- of the applicant. --
192 -- This procedure is the hook procedure for the --
193 -- address when address is inserted. --
194 -- Parameters : --
195 -- IN : p_style IN VARCHAR2 --
196 -- p_address_line1 IN VARCHAR2 --
197 -- p_town_or_city IN VARCHAR2 --
198 -- p_country IN VARCHAR2 --
199 -- p_postal_code IN VARCHAR2 --
200 -- --
201 -- Change History : --
202 --------------------------------------------------------------------------
203 -- Rev# Date Userid Description --
204 --------------------------------------------------------------------------
205 -- 1.0 09/04/03 bramajey Created this procedure --
206 --------------------------------------------------------------------------
207 PROCEDURE check_address_ins(p_style IN VARCHAR2
208 ,p_address_line1 IN VARCHAR2
209 ,p_town_or_city IN VARCHAR2
210 ,p_country IN VARCHAR2
211 ,p_postal_code IN VARCHAR2)
212 IS
213 BEGIN
214
215 check_address (p_style => p_style
216 ,p_address_line1 => p_address_line1
217 ,p_town_or_city => p_town_or_city
218 ,p_country => p_country
219 ,p_postal_code => p_postal_code);
220
221 END;
222
223 --------------------------------------------------------------------------
224 -- --
225 -- Name : CHECK_ADDRESS_UPD --
226 -- Type : Procedure --
227 -- Access : Public --
228 -- Description : Procedure is the driver procedure for the validation--
229 -- of the applicant. --
230 -- This procedure is the hook procedure for the --
231 -- address when address is updated. --
232 -- Parameters : --
233 -- IN : p_address_id IN NUMBER --
234 -- p_address_line1 IN VARCHAR2 --
235 -- p_town_or_city IN VARCHAR2 --
236 -- p_country IN VARCHAR2 --
237 -- p_postal_code IN VARCHAR2 --
238 -- --
239 -- Change History : --
240 --------------------------------------------------------------------------
241 -- Rev# Date Userid Description --
242 --------------------------------------------------------------------------
243 -- 1.0 09/04/03 bramajey Created this procedure --
244 -- 1.1 14/01/04 sshankar Added parameter 'address_id' to procedure --
245 -- and created cursor csr_addr_style to --
246 -- fetch address style.(Bug 3371417) --
247 --------------------------------------------------------------------------
248 PROCEDURE check_address_upd
249 (p_address_id IN NUMBER
250 ,p_address_line1 IN VARCHAR2
251 ,p_town_or_city IN VARCHAR2
252 ,p_country IN VARCHAR2
253 ,p_postal_code IN VARCHAR2)
254 IS
255
256 l_style per_addresses.style%TYPE := 'CN_GLB';
257
258 --
259 -- Bug 3371417
260 -- Added Cursor to fetch addres style from per_addresses
261 --
262 l_proc VARCHAR2(72) := g_package||'check_address_upd';
263
264 CURSOR csr_addr_style(p_address_id NUMBER)IS
265 SELECT addr.style
266 FROM per_addresses addr
267 WHERE addr.address_id = p_address_id ;
268
269
270 BEGIN
271
272 --
273 -- Bug 3371417
274 -- Changes Starts
275 -- Added code to fetch address style.
276 --
277 g_trace := hr_cn_person_address_api.g_trace;
278
279 hr_cn_api.set_location(g_trace,'Entering: '||l_proc,10);
280
281 OPEN csr_addr_style(p_address_id);
282 FETCH csr_addr_style INTO l_style;
283
284 IF csr_addr_style%NOTFOUND THEN
285 l_style := 'CN_GLB';
286 END IF;
287
288 CLOSE csr_addr_style;
289
290 --
291 -- Bug 3371417
292 -- Changes End
293 --
294
295 check_address (p_style => l_style
296 ,p_address_line1 => p_address_line1
297 ,p_town_or_city => p_town_or_city
298 ,p_country => p_country
299 ,p_postal_code => p_postal_code);
300
301 hr_cn_api.set_location(g_trace,'Leaving: '||l_proc,20);
302
303 END check_address_upd;
304
305
306 END per_cn_add_leg_hook;