1 Package Body per_scn_bus as
2 /* $Header: pescnrhi.pkb 115.2 2003/08/07 23:57:30 vkonda noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_scn_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_component_name varchar2(120) default null;
16 g_solution_type_name varchar2(120) 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_component_name in varchar2
25 ,p_solution_type_name in varchar2
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_solution_cmpt_names 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_solution_cmpt_names scn
42 -- , EDIT_HERE table_name(s) 333
43 where scn.solution_id = p_solution_id
44 and scn.component_name = p_component_name
45 and scn.solution_type_name = p_solution_type_name;
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 => 'component_name'
67 ,p_argument_value => p_component_name
68 );
69 hr_api.mandatory_arg_error
70 (p_api_name => l_proc
71 ,p_argument => 'solution_type_name'
72 ,p_argument_value => p_solution_type_name
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,'COMPONENT_NAME')
90 ,p_associated_column3
91 => nvl(p_associated_column3,'SOLUTION_TYPE_NAME')
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_component_name in varchar2
115 ,p_solution_type_name in varchar2
116 )
117 Return Varchar2 Is
118 --
119 -- Declare cursor
120 --
121 -- EDIT_HERE In the following cursor statement add join(s) between
122 -- per_solution_cmpt_names 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_solution_cmpt_names scn
130 -- , EDIT_HERE table_name(s) 333
131 where scn.solution_id = p_solution_id
132 and scn.component_name = p_component_name
133 and scn.solution_type_name = p_solution_type_name;
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 => 'component_name'
155 ,p_argument_value => p_component_name
156 );
157 hr_api.mandatory_arg_error
158 (p_api_name => l_proc
159 ,p_argument => 'solution_type_name'
160 ,p_argument_value => p_solution_type_name
161 );
162 --
163 if (( nvl(per_scn_bus.g_solution_id, hr_api.g_number)
164 = p_solution_id)
165 and ( nvl(per_scn_bus.g_component_name, hr_api.g_varchar2)
166 = p_component_name)
167 and ( nvl(per_scn_bus.g_solution_type_name, hr_api.g_varchar2)
168 = p_solution_type_name)) 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_scn_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_scn_bus.g_solution_id := p_solution_id;
199 per_scn_bus.g_component_name := p_component_name;
200 per_scn_bus.g_solution_type_name := p_solution_type_name;
201 per_scn_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_scn_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_scn_shd.api_updating
246 (p_solution_id => p_rec.solution_id
247 ,p_component_name => p_rec.component_name
248 ,p_solution_type_name => p_rec.solution_type_name
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_scn_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 --
277 -- EDIT_HERE: As this table does not have a mandatory business_group_id
278 -- column, ensure client_info is populated by calling a suitable
279 -- ???_???_bus.set_security_group_id procedure, or add one of the following
280 -- comments:
281 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
282 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
283 --
284 -- Validate Dependent Attributes
285 --
286 --
287 hr_utility.set_location(' Leaving:'||l_proc, 10);
288 End insert_validate;
289 --
290 -- ----------------------------------------------------------------------------
291 -- |---------------------------< update_validate >----------------------------|
292 -- ----------------------------------------------------------------------------
293 Procedure update_validate
294 (p_rec in per_scn_shd.g_rec_type
295 ) is
296 --
297 l_proc varchar2(72) := g_package||'update_validate';
298 --
299 Begin
300 hr_utility.set_location('Entering:'||l_proc, 5);
301 --
302 -- Call all supporting business operations
303 --
304 --
305 -- EDIT_HERE: As this table does not have a mandatory business_group_id
309 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
306 -- column, ensure client_info is populated by calling a suitable
307 -- ???_???_bus.set_security_group_id procedure, or add one of the following
308 -- comments:
310 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
311 --
312 -- Validate Dependent Attributes
313 --
314 chk_non_updateable_args
315 (p_rec => p_rec
316 );
317 --
318 --
319 hr_utility.set_location(' Leaving:'||l_proc, 10);
320 End update_validate;
321 --
322 -- ----------------------------------------------------------------------------
323 -- |---------------------------< delete_validate >----------------------------|
324 -- ----------------------------------------------------------------------------
325 Procedure delete_validate
326 (p_rec in per_scn_shd.g_rec_type
327 ) is
328 --
329 l_proc varchar2(72) := g_package||'delete_validate';
330 --
331 Begin
332 hr_utility.set_location('Entering:'||l_proc, 5);
333 --
334 -- Call all supporting business operations
335 --
336 hr_utility.set_location(' Leaving:'||l_proc, 10);
337 End delete_validate;
338 --
339 end per_scn_bus;