1 Package Body per_sos_bus as
2 /* $Header: pesosrhi.pkb 115.2 2003/08/08 00:05:41 vkonda noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_sos_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_solution_id number default null;
15 g_solution_set_name varchar2(120) default null;
16 g_user_id number default null;
17 --
18 -- ---------------------------------------------------------------------------
19 -- |----------------------< set_security_group_id >--------------------------|
20 -- ---------------------------------------------------------------------------
21 --
22 Procedure set_security_group_id
23 (p_solution_id in number
24 ,p_solution_set_name in varchar2
25 ,p_user_id in number
26 ,p_associated_column1 in varchar2 default null
27 ,p_associated_column2 in varchar2 default null
28 ,p_associated_column3 in varchar2 default null
29 ) is
30 --
31 -- Declare cursor
32 --
33 -- EDIT_HERE In the following cursor statement add join(s) between
34 -- per_solutions_selected and PER_BUSINESS_GROUPS
35 -- so that the security_group_id for
36 -- the current business group context can be derived.
37 -- Remove this comment when the edit has been completed.
38 cursor csr_sec_grp is
39 select pbg.security_group_id
40 from per_business_groups pbg
41 , per_solutions_selected sos
42 -- , EDIT_HERE table_name(s) 333
43 where sos.solution_id = p_solution_id
44 and sos.solution_set_name = p_solution_set_name
45 and sos.user_id = p_user_id;
46 -- and pbg.business_group_id = EDIT_HERE 333.business_group_id;
47 --
48 -- Declare local variables
49 --
50 l_security_group_id number;
51 l_proc varchar2(72) := g_package||'set_security_group_id';
52 --
53 begin
54 --
55 hr_utility.set_location('Entering:'|| l_proc, 10);
56 --
57 -- Ensure that all the mandatory parameter are not null
58 --
59 hr_api.mandatory_arg_error
60 (p_api_name => l_proc
61 ,p_argument => 'solution_id'
62 ,p_argument_value => p_solution_id
63 );
64 hr_api.mandatory_arg_error
65 (p_api_name => l_proc
66 ,p_argument => 'solution_set_name'
67 ,p_argument_value => p_solution_set_name
68 );
69 hr_api.mandatory_arg_error
70 (p_api_name => l_proc
71 ,p_argument => 'user_id'
72 ,p_argument_value => p_user_id
73 );
74 --
75 open csr_sec_grp;
76 fetch csr_sec_grp into l_security_group_id;
77 --
78 if csr_sec_grp%notfound then
79 --
80 close csr_sec_grp;
81 --
82 -- The primary key is invalid therefore we must error
83 --
84 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
85 hr_multi_message.add
86 (p_associated_column1
87 => nvl(p_associated_column1,'SOLUTION_ID')
88 ,p_associated_column2
89 => nvl(p_associated_column2,'SOLUTION_SET_NAME')
90 ,p_associated_column3
91 => nvl(p_associated_column3,'USER_ID')
92 );
93 --
94 else
95 close csr_sec_grp;
96 --
97 -- Set the security_group_id in CLIENT_INFO
98 --
99 hr_api.set_security_group_id
100 (p_security_group_id => l_security_group_id
101 );
102 end if;
103 --
104 hr_utility.set_location(' Leaving:'|| l_proc, 20);
105 --
106 end set_security_group_id;
107 --
108 -- ---------------------------------------------------------------------------
109 -- |---------------------< return_legislation_code >-------------------------|
110 -- ---------------------------------------------------------------------------
111 --
112 Function return_legislation_code
113 (p_solution_id in number
114 ,p_solution_set_name in varchar2
115 ,p_user_id in number
116 )
117 Return Varchar2 Is
118 --
119 -- Declare cursor
120 --
121 -- EDIT_HERE In the following cursor statement add join(s) between
122 -- per_solutions_selected and PER_BUSINESS_GROUPS
123 -- so that the legislation_code for
124 -- the current business group context can be derived.
125 -- Remove this comment when the edit has been completed.
126 cursor csr_leg_code is
127 select pbg.legislation_code
128 from per_business_groups pbg
129 , per_solutions_selected sos
130 -- , EDIT_HERE table_name(s) 333
131 where sos.solution_id = p_solution_id
132 and sos.solution_set_name = p_solution_set_name
133 and sos.user_id = p_user_id;
134 -- and pbg.business_group_id = EDIT_HERE 333.business_group_id;
135 --
136 -- Declare local variables
137 --
138 l_legislation_code varchar2(150);
139 l_proc varchar2(72) := g_package||'return_legislation_code';
140 --
141 Begin
142 --
143 hr_utility.set_location('Entering:'|| l_proc, 10);
144 --
145 -- Ensure that all the mandatory parameter are not null
146 --
147 hr_api.mandatory_arg_error
148 (p_api_name => l_proc
149 ,p_argument => 'solution_id'
150 ,p_argument_value => p_solution_id
151 );
152 hr_api.mandatory_arg_error
153 (p_api_name => l_proc
154 ,p_argument => 'solution_set_name'
155 ,p_argument_value => p_solution_set_name
156 );
157 hr_api.mandatory_arg_error
158 (p_api_name => l_proc
159 ,p_argument => 'user_id'
160 ,p_argument_value => p_user_id
161 );
162 --
163 if (( nvl(per_sos_bus.g_solution_id, hr_api.g_number)
164 = p_solution_id)
165 and ( nvl(per_sos_bus.g_solution_set_name, hr_api.g_varchar2)
166 = p_solution_set_name)
167 and ( nvl(per_sos_bus.g_user_id, hr_api.g_number)
168 = p_user_id)) then
169 --
170 -- The legislation code has already been found with a previous
171 -- call to this function. Just return the value in the global
172 -- variable.
173 --
174 l_legislation_code := per_sos_bus.g_legislation_code;
175 hr_utility.set_location(l_proc, 20);
176 else
177 --
178 -- The ID is different to the last call to this function
179 -- or this is the first call to this function.
180 --
181 open csr_leg_code;
182 fetch csr_leg_code into l_legislation_code;
183 --
184 if csr_leg_code%notfound then
185 --
186 -- The primary key is invalid therefore we must error
187 --
188 close csr_leg_code;
189 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
190 fnd_message.raise_error;
191 end if;
192 hr_utility.set_location(l_proc,30);
193 --
194 -- Set the global variables so the values are
195 -- available for the next call to this function.
196 --
197 close csr_leg_code;
198 per_sos_bus.g_solution_id := p_solution_id;
199 per_sos_bus.g_solution_set_name := p_solution_set_name;
200 per_sos_bus.g_user_id := p_user_id;
201 per_sos_bus.g_legislation_code := l_legislation_code;
202 end if;
203 hr_utility.set_location(' Leaving:'|| l_proc, 40);
204 return l_legislation_code;
205 end return_legislation_code;
206 --
207 -- ----------------------------------------------------------------------------
208 -- |-----------------------< chk_non_updateable_args >------------------------|
209 -- ----------------------------------------------------------------------------
210 -- {Start Of Comments}
211 --
212 -- Description:
213 -- This procedure is used to ensure that non updateable attributes have
214 -- not been updated. If an attribute has been updated an error is generated.
215 --
216 -- Pre Conditions:
217 -- g_old_rec has been populated with details of the values currently in
218 -- the database.
219 --
220 -- In Arguments:
221 -- p_rec has been populated with the updated values the user would like the
222 -- record set to.
223 --
224 -- Post Success:
225 -- Processing continues if all the non updateable attributes have not
226 -- changed.
227 --
228 -- Post Failure:
229 -- An application error is raised if any of the non updatable attributes
230 -- have been altered.
231 --
232 -- {End Of Comments}
233 -- ----------------------------------------------------------------------------
234 Procedure chk_non_updateable_args
235 (p_rec in per_sos_shd.g_rec_type
236 ) IS
237 --
238 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
239 --
240 Begin
241 --
242 -- Only proceed with the validation if a row exists for the current
243 -- record in the HR Schema.
244 --
245 IF NOT per_sos_shd.api_updating
246 (p_solution_id => p_rec.solution_id
247 ,p_solution_set_name => p_rec.solution_set_name
248 ,p_user_id => p_rec.user_id
249 ,p_object_version_number => p_rec.object_version_number
250 ) THEN
251 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
252 fnd_message.set_token('PROCEDURE ', l_proc);
253 fnd_message.set_token('STEP ', '5');
254 fnd_message.raise_error;
255 END IF;
256 --
257 -- EDIT_HERE: Add checks to ensure non-updateable args have
258 -- not been updated.
259 --
260 End chk_non_updateable_args;
261 --
262 -- ----------------------------------------------------------------------------
263 -- |---------------------------< insert_validate >----------------------------|
264 -- ----------------------------------------------------------------------------
265 Procedure insert_validate
266 (p_rec in per_sos_shd.g_rec_type
267 ) is
268 --
269 l_proc varchar2(72) := g_package||'insert_validate';
270 --
271 Begin
272 hr_utility.set_location('Entering:'||l_proc, 5);
273 --
274 -- Call all supporting business operations
275 --
276 -- No business group context. HR_STANDARD_LOOKUPS used for validation.
277 --
278 -- Validate Dependent Attributes
279 --
280 --
281 hr_utility.set_location(' Leaving:'||l_proc, 10);
282 End insert_validate;
283 --
284 -- ----------------------------------------------------------------------------
285 -- |---------------------------< update_validate >----------------------------|
286 -- ----------------------------------------------------------------------------
287 Procedure update_validate
288 (p_rec in per_sos_shd.g_rec_type
289 ) is
290 --
291 l_proc varchar2(72) := g_package||'update_validate';
292 --
293 Begin
294 hr_utility.set_location('Entering:'||l_proc, 5);
295 --
296 -- Call all supporting business operations
297 --
298 -- No business group context. HR_STANDARD_LOOKUPS used for validation.
299 --
300 -- Validate Dependent Attributes
301 --
302 chk_non_updateable_args
303 (p_rec => p_rec
304 );
305 --
306 --
307 hr_utility.set_location(' Leaving:'||l_proc, 10);
308 End update_validate;
309 --
310 -- ----------------------------------------------------------------------------
311 -- |---------------------------< delete_validate >----------------------------|
312 -- ----------------------------------------------------------------------------
313 Procedure delete_validate
314 (p_rec in per_sos_shd.g_rec_type
315 ) is
316 --
317 l_proc varchar2(72) := g_package||'delete_validate';
318 --
319 Begin
320 hr_utility.set_location('Entering:'||l_proc, 5);
321 --
322 -- Call all supporting business operations
323 --
324 hr_utility.set_location(' Leaving:'||l_proc, 10);
325 End delete_validate;
326 --
327 end per_sos_bus;