[Home] [Help]
PACKAGE BODY: APPS.HR_RO_LOCATION_VAL
Source
1 PACKAGE BODY HR_RO_LOCATION_VAL AS
2 /* $Header: perolocn.pkb 120.0.12020000.4 2013/04/15 09:09:11 ssanjays noship $ */
3 g_package VARCHAR2(30) := 'HR_RO_LOCATION_VAL.';
4
5 PROCEDURE CHECK_RO_LOCATION(p_address_line_1 IN VARCHAR2
6 ,p_address_line_2 IN VARCHAR2
7 ,p_country IN VARCHAR2
8 ,p_postal_code IN VARCHAR2
9 ,p_style IN VARCHAR2
10 ) IS
11
12 cursor csr_county_valid is
13 select 'x'
14 from hr_lookups
15 where lookup_type = 'RO_PER_COUNTIES'
16 and meaning = p_address_line_1
17 and enabled_flag ='Y';
18
19
20 cursor csr_locality_valid is
21 select 'x'
22 from pay_user_column_instances_f pucif,
23 pay_user_columns puc,
24 pay_user_tables put,
25 pay_user_rows_f purf
26 where pucif.VALUE = p_address_line_2
27 and puc.user_column_name = 'LOCALITY'
28 and puc.user_table_id = put.user_table_id
29 and pucif.user_column_id = puc.user_column_id
30 and put.user_table_name = 'RO_LOCALITY_DETAILS'
31 and purf.user_table_id = put.user_table_id
32 and pucif.user_row_id = purf.user_row_id
33 and purf.row_low_range_or_name in (
34 select purf1.ROW_LOW_RANGE_OR_NAME
35 from pay_user_rows_f purf1,
36 pay_user_column_instances_f pucif1,
37 pay_user_columns puc1,
38 pay_user_column_instances_f pucif2,
39 pay_user_columns puc2,
40 pay_user_rows_f purf2
41 where puc1.user_column_name = 'COUNTY CODE'
42 and puc1.user_table_id = put.user_table_id
43 and pucif1.user_column_id = puc1.user_column_id
44 and pucif1.value = (select lookup_code
45 from hr_lookups
46 where lookup_type = 'RO_PER_COUNTIES'
47 and enabled_flag='Y'
48 and meaning = p_address_line_1)
49 and pucif1.user_row_id = purf1.user_row_id
50 and purf1.user_table_id = put.user_table_id
51 and purf1.row_low_range_or_name = purf2.row_low_range_or_name
52 and purf2.user_table_id = put.user_table_id
53 and puc2.user_column_name = 'LEVEL'
54 and puc2.user_table_id = put.user_table_id
55 and pucif2.user_column_id = puc2.user_column_id
56 and pucif2.value = '3'
57 and purf2.user_row_id = pucif2.user_row_id);
58
59 l_proc varchar2(60);
60 l_exists varchar2(1);
61
62 BEGIN
63 l_proc:= g_package||'CHECK_RO_LOCATION';
64 hr_utility.set_location('Entering : '||l_proc,5);
65
66 if p_style <> 'RO' and p_style <> hr_api.g_varchar2 then
67 hr_utility.set_location(l_proc,10);
68 hr_utility.set_message(800,'HR_520030_LOC_STYLE_WRNG');
69 hr_utility.raise_error;
70 end if;
71
72 if p_country <> 'RO' and p_country <> hr_api.g_varchar2 then
73 hr_utility.set_location(l_proc,20);
74 hr_utility.set_message(800,'HR_520031_LOC_CNTR_WRNG');
75 hr_utility.raise_error;
76 end if;
77
78 hr_utility.set_location(l_proc,30);
79
80 if p_address_line_1 <> hr_api.g_varchar2 then
81 open csr_county_valid ;
82 fetch csr_county_valid into l_exists;
83 if csr_county_valid%NOTFOUND then
84 hr_utility.set_location(l_proc,35);
85 hr_utility.set_message(800,'HR_520032_LOC_CNTY_INV');
86 hr_utility.raise_error;
87 end if;
88 close csr_county_valid ;
89 end if;
90
91 hr_utility.set_location(l_proc,40);
92
93 if p_address_line_2 <> hr_api.g_varchar2 then
94 if p_address_line_1 = hr_api.g_varchar2 then
95 hr_utility.set_location(l_proc,42);
96 hr_utility.set_message(800,'HR_520034_LOC_CNTY_NOT_ENT');
97 hr_utility.raise_error;
98 else
99 open csr_locality_valid ;
100 fetch csr_locality_valid into l_exists;
101 if csr_locality_valid%NOTFOUND then
102 hr_utility.set_location(l_proc,45);
103 hr_utility.set_message(800,'HR_520033_LOC_LOCL_INV');
104 hr_utility.raise_error;
105 end if;
106 close csr_locality_valid ;
107 end if;
108 end if;
109
110 hr_utility.set_location(l_proc,50);
111
112 if p_postal_code <> hr_api.g_varchar2 then
113 if length(p_postal_code) <> 6 then
114 hr_utility.set_location(l_proc,55);
115 hr_utility.set_message(800,'HR_520035_LOC_PC_INV');
116 hr_utility.raise_error;
117 end if;
118 end if;
119
120 hr_utility.set_location('Leaving : '||l_proc,50);
121
122 END CHECK_RO_LOCATION;
123
124
125
126 PROCEDURE CREATE_RO_LOCATION (p_location_code IN VARCHAR2
127 ,p_address_line_1 IN VARCHAR2 DEFAULT NULL
128 ,p_address_line_2 IN VARCHAR2 DEFAULT NULL
129 ,p_country IN VARCHAR2 DEFAULT NULL
130 ,p_postal_code IN VARCHAR2 DEFAULT NULL
131 ,p_style IN VARCHAR2 DEFAULT NULL
132 )
133 IS
134
135 cursor csr_location_exists is
136 select 'x'
137 from hr_locations_all
138 where location_code = p_location_code;
139
140 l_exists varchar2(1);
141
142 l_proc varchar2(60);
143
144 BEGIN
145 l_proc:= g_package||'CREATE_RO_LOCATION';
146 hr_utility.set_location('Entering : '||l_proc,5);
147 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'RO') THEN
148 hr_utility.set_location('Leaving : '||l_proc,10);
149 return;
150 END IF;
151
152 -- Another location with same location code shouldn't exist
153
154 open csr_location_exists;
155 fetch csr_location_exists into l_exists;
156 if csr_location_exists%FOUND then
157 hr_utility.set_message(800,'HR_520029_LOC_EXISTS');
158 hr_utility.raise_error;
159 end if;
160 close csr_location_exists;
161
162 hr_utility.set_location(l_proc,15);
163
164 CHECK_RO_LOCATION(p_address_line_1 => p_address_line_1
165 ,p_address_line_2 => p_address_line_2
166 ,p_country => p_country
167 ,p_postal_code => p_postal_code
168 ,p_style => p_style
169 );
170
171 hr_utility.set_location('Leaving : '||l_proc,20);
172
173 END CREATE_RO_LOCATION;
174
175 PROCEDURE UPDATE_RO_LOCATION( p_address_line_1 IN VARCHAR2 DEFAULT NULL
176 ,p_address_line_2 IN VARCHAR2 DEFAULT NULL
177 ,p_country IN VARCHAR2 DEFAULT NULL
178 ,p_postal_code IN VARCHAR2 DEFAULT NULL
179 ,p_style IN VARCHAR2 DEFAULT NULL
180 ) IS
181
182 l_proc varchar2(60);
183
184 BEGIN
185 l_proc:= g_package||'UPDATE_RO_LOCATION';
186 hr_utility.set_location('Entering : '||l_proc,5);
187 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'RO') THEN
188 hr_utility.set_location('Leaving : '||l_proc,10);
189 return;
190 END IF;
191 hr_utility.set_location(l_proc,15);
192
193 CHECK_RO_LOCATION(p_address_line_1 => p_address_line_1
194 ,p_address_line_2 => p_address_line_2
195 ,p_country => p_country
196 ,p_postal_code => p_postal_code
197 ,p_style => p_style
198 );
199
200 hr_utility.set_location('Leaving : '||l_proc,20);
201
202 END UPDATE_RO_LOCATION;
203
204 END HR_RO_LOCATION_VAL;