[Home] [Help]
PACKAGE BODY: APPS.PER_RO_ADDRESS
Source
1 PACKAGE body per_ro_address AS
2 /* $Header: perrousrhk.pkb 120.0.12020000.4 2013/04/15 09:09:34 ssanjays noship $ */
3
4 g_package VARCHAR2(33) := 'per_ro_address.';
5
6 CURSOR c_get_style(p_address_id number) is
7 SELECT style,person_id
8 FROM per_addresses
9 WHERE address_id=p_address_id;
10 --
11 PROCEDURE check_address_unique(
12 p_address_ID IN NUMBER , p_address_type IN VARCHAR2,
13 p_date_from IN DATE,
14 p_date_to IN DATE,
15 p_person_id IN NUMBER
16 )
17 is
18
19 g_last_date DATE ;
20 l_address_id number default null;
21 l_duplicate_flag boolean default FALSE;
22
23 cursor c_duplicate_address(p_person_id NUMBER, p_address_type VARCHAR2, p_address_id NUMBER, p_date_from DATE, p_date_to DATE) is
24 SELECT address_id
25 FROM per_addresses pa
26 WHERE pa.person_id = p_person_id
27 AND pa.address_type = p_address_type
28 AND (
29 pa.address_id <> p_address_id
30 OR p_address_id IS NULL
31 )
32 AND nvl (pa.date_to, g_last_date) >= p_date_from
33 AND pa.date_from <= nvl (p_date_to, g_last_date);
34
35 BEGIN
36 g_last_date := to_date('31-12-4712','DD-MM-YYYY');
37 open c_duplicate_address(p_person_id, p_address_type, p_address_id, p_date_from, p_date_to);
38 fetch c_duplicate_address into l_address_id;
39 if c_duplicate_address%found then
40 l_duplicate_flag := true;
41 end if;
42 close c_duplicate_address;
43
44 if l_duplicate_flag then
45 hr_utility.set_message(800,'HR_520022_DUPLICATE_ADDRESS');
46 hr_utility.raise_error;
47 end if;
48
49 END check_address_unique;
50
51
52 PROCEDURE check_ro_address (
53 p_address_ID IN NUMBER ,
54 p_address_type IN VARCHAR2,
55 p_date_from IN DATE,
56 p_date_to IN DATE,
57 p_person_id IN NUMBER,
58 p_address_line1 IN VARCHAR2,
59 p_address_line2 IN VARCHAR2,
60 p_address_line3 IN VARCHAR2
61 )
62 IS
63 l_locality_exists number default 0;
64 BEGIN
65 --
66 -- Check that Address type is entered
67 --
68 IF ( p_address_type IS NULL ) THEN
69 hr_utility.set_message(800,'HR_520024_ADDRESS_TYPE_NULL');
70 hr_utility.raise_error;
71 END IF;
72 --
73 -- Check that Street Name is entered when street type is entered
74 --
75 IF ( p_address_line1 IS NOT NULL ) and ( p_address_line3 IS NULL ) THEN
76 hr_utility.set_message(800,'HR_520025_ST_NAME_NOT_SPEC');
77 hr_utility.raise_error;
78 END IF;
79 --
80 if p_address_line1 is not null and p_address_line2 is not null then
81 begin
82
83 select 1 into l_locality_exists from dual where p_address_line2 in (
84 select pucif.VALUE
85 from pay_user_column_instances_f pucif, pay_user_columns puc, pay_user_tables put, pay_user_rows_f purf
86 where puc.user_column_name = 'LOCALITY'
87 and puc.user_table_id = put.user_table_id
88 and pucif.user_column_id = puc.user_column_id
89 and put.user_table_name = 'RO_LOCALITY_DETAILS'
90 and purf.user_table_id = put.user_table_id
91 and pucif.user_row_id = purf.user_row_id
92 and purf.row_low_range_or_name in (
93 select purf1.ROW_LOW_RANGE_OR_NAME
94 from pay_user_rows_f purf1,
95 pay_user_column_instances_f pucif1,
96 pay_user_columns puc1,
97 pay_user_column_instances_f pucif2,
98 pay_user_columns puc2,
99 pay_user_rows_f purf2
100 where puc1.user_column_name = 'COUNTY CODE'
101 and puc1.user_table_id = put.user_table_id
102 and pucif1.user_column_id = puc1.user_column_id
103 and pucif1.value = (select lookup_code
104 from hr_lookups
105 where lookup_type = 'RO_PER_COUNTIES'
106 and enabled_flag='Y'
107 and meaning = p_address_line1)
108 and pucif1.user_row_id = purf1.user_row_id
109 and purf1.user_table_id = put.user_table_id
110 and purf1.row_low_range_or_name = purf2.row_low_range_or_name
111 and purf2.user_table_id = put.user_table_id
112 and puc2.user_column_name = 'LEVEL'
113 and puc2.user_table_id = put.user_table_id
114 and pucif2.user_column_id = puc2.user_column_id
115 and pucif2.value = '3'
116 and purf2.user_row_id = pucif2.user_row_id)
117 );
118 exception
119 when no_data_found then
120 l_locality_exists := 0;
121 end;
122
123 if l_locality_exists = 0 then
124 hr_utility.set_message(800,'HR_520023_LOC_NOT_IN_COUNTY');
125 hr_utility.raise_error;
126 end if;
127
128 end if;
129 --
130 check_address_unique(
131 p_address_ID => p_address_id ,
132 p_address_type => p_address_type,
133 p_date_from => p_date_from ,
134 p_date_to => p_date_to ,
135 p_person_id => p_person_id
136 );
137 --
138 --
139 END check_ro_address;
140 --
141 --
142 --
143 PROCEDURE create_ro_address (
144 p_style IN VARCHAR2,
145 p_address_type IN VARCHAR2,
146 p_date_from IN DATE,
147 p_date_to IN DATE,
148 p_person_id IN NUMBER,
149 p_address_line1 IN VARCHAR2,
150 p_address_line2 IN VARCHAR2,
151 p_address_line3 IN VARCHAR2
152 )
153 IS
154 BEGIN
155 --
156 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'RO') THEN
157 hr_utility.trace('RO not installed.Leaving create_ro_address');
158 return;
159 END IF;
160
161 IF p_style = 'RO' THEN
162 per_ro_address.check_ro_address(
163 p_address_ID => NULL ,
164 p_address_type => p_address_type,
165 p_date_from => p_date_from ,
166 p_date_to => p_date_to ,
167 p_person_id => p_person_id,
168 p_address_line1 => p_address_line1,
169 p_address_line2 => p_address_line2,
170 p_address_line3 => p_address_line3
171 );
172 END IF;
173 --
174 END create_ro_address;
175 --
176 --
177 PROCEDURE update_ro_address (
178 p_address_id IN NUMBER,
179 p_address_type IN VARCHAR2,
180 p_date_from IN DATE,
181 p_date_to IN DATE,
182 p_address_line1 IN VARCHAR2,
183 p_address_line2 IN VARCHAR2,
184 p_address_line3 IN VARCHAR2
185 )
186 IS
187 --
188 l_style per_addresses.style%TYPE;
189 l_person_id per_addresses.person_id%TYPE;
190 --
191 BEGIN
192 --
193 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'RO') THEN
194 hr_utility.trace('RO not installed.Leaving update_ro_address');
195 return;
196 END IF;
197
198 OPEN c_get_style(p_address_id);
199 FETCH c_get_style INTO l_style,l_person_id;
200 CLOSE c_get_style;
201 IF l_style='RO' THEN
202 per_ro_address.check_ro_address(
203 p_address_ID => p_address_id ,
204 p_address_type => p_address_type,
205 p_date_from => p_date_from ,
206 p_date_to => p_date_to ,
207 p_person_id => l_person_id ,
208 p_address_line1 => p_address_line1,
209 p_address_line2 => p_address_line2,
210 p_address_line3 => p_address_line3
211 );
212 END IF;
213
214 --
215 END update_ro_address;
216 --
217
218 PROCEDURE update_ro_address_style(
219 p_address_id IN NUMBER,
220 p_style IN VARCHAR2,
221 p_address_type IN VARCHAR2,
222 p_date_from IN DATE,
223 p_date_to IN DATE,
224 p_address_line1 IN VARCHAR2,
225 p_address_line2 IN VARCHAR2,
226 p_address_line3 IN VARCHAR2
227 )
228 IS
229 --
230 l_style per_addresses.style%TYPE;
231 l_person_id per_addresses.person_id%TYPE;
232 --
233 BEGIN
234 --
235 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'RO') THEN
236 hr_utility.trace('RO not installed.Leaving update_pl_address_style');
237 return;
238 END IF;
239 IF p_style='RO' THEN
240 OPEN c_get_style(p_address_id);
241 FETCH c_get_style INTO l_style,l_person_id;
242 CLOSE c_get_style;
243 per_ro_address.check_ro_address(
244 p_address_ID => p_address_id ,
245 p_address_type => p_address_type,
246 p_date_from => p_date_from ,
247 p_date_to => p_date_to ,
248 p_person_id => l_person_id ,
249 p_address_line1 => p_address_line1,
250 p_address_line2 => p_address_line2,
251 p_address_line3 => p_address_line3
252 );
253 END IF;
254 --
255 END update_ro_address_style;
256 --
257
258 END per_ro_address;