1 Package Body ota_fns_bus as
2 /* $Header: otfnsrhi.pkb 120.1 2005/07/18 05:29 aabalakr noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_fns_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_forum_id number default null;
15 g_person_id number default null;
16 g_contact_id number default null;
17 --
18 -- ---------------------------------------------------------------------------
19 -- |----------------------< set_security_group_id >--------------------------|
20 -- ---------------------------------------------------------------------------
21 --
22 Procedure set_security_group_id
23 (p_forum_id in number
24 ,p_person_id in number
25 ,p_contact_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 cursor csr_sec_grp is
34 select pbg.security_group_id,
35 pbg.legislation_code
36 from per_business_groups_perf pbg
37 , ota_frm_notif_subscribers fns
38 where fns.forum_id = p_forum_id
39 and fns.person_id = p_person_id
40 and fns.contact_id = p_contact_id
41 and pbg.business_group_id = fns.business_group_id;
42 --
43 -- Declare local variables
44 --
45 l_security_group_id number;
46 l_proc varchar2(72) := g_package||'set_security_group_id';
47 l_legislation_code varchar2(150);
48 --
49 begin
50 --
51 hr_utility.set_location('Entering:'|| l_proc, 10);
52 --
53 -- Ensure that all the mandatory parameter are not null
54 --
55 hr_api.mandatory_arg_error
56 (p_api_name => l_proc
57 ,p_argument => 'forum_id'
58 ,p_argument_value => p_forum_id
59 );
60 hr_api.mandatory_arg_error
61 (p_api_name => l_proc
62 ,p_argument => 'person_id'
63 ,p_argument_value => p_person_id
64 );
65 hr_api.mandatory_arg_error
66 (p_api_name => l_proc
67 ,p_argument => 'contact_id'
68 ,p_argument_value => p_contact_id
69 );
70 --
71 open csr_sec_grp;
72 fetch csr_sec_grp into l_security_group_id
73 , l_legislation_code;
74 --
75 if csr_sec_grp%notfound then
76 --
77 close csr_sec_grp;
78 --
79 -- The primary key is invalid therefore we must error
80 --
81 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
82 hr_multi_message.add
83 (p_associated_column1
84 => nvl(p_associated_column1,'FORUM_ID')
85 ,p_associated_column2
86 => nvl(p_associated_column2,'PERSON_ID')
87 ,p_associated_column3
88 => nvl(p_associated_column3,'CONTACT_ID')
89 );
90 --
91 else
92 close csr_sec_grp;
93 --
94 -- Set the security_group_id in CLIENT_INFO
95 --
96 hr_api.set_security_group_id
97 (p_security_group_id => l_security_group_id
98 );
99 --
100 -- Set the sessions legislation context in HR_SESSION_DATA
101 --
102 hr_api.set_legislation_context(l_legislation_code);
103 end if;
104 --
105 hr_utility.set_location(' Leaving:'|| l_proc, 20);
106 --
107 end set_security_group_id;
108 --
109 -- ---------------------------------------------------------------------------
110 -- |---------------------< return_legislation_code >-------------------------|
111 -- ---------------------------------------------------------------------------
112 --
113 Function return_legislation_code
114 (p_forum_id in number
115 ,p_person_id in number
116 ,p_contact_id in number
117 )
118 Return Varchar2 Is
119 --
120 -- Declare cursor
121 --
122 cursor csr_leg_code is
123 select pbg.legislation_code
124 from per_business_groups_perf pbg
125 , ota_frm_notif_subscribers fns
126 where fns.forum_id = p_forum_id
127 and fns.person_id = p_person_id
128 and fns.contact_id = p_contact_id
129 and pbg.business_group_id = fns.business_group_id;
130 --
131 -- Declare local variables
132 --
133 l_legislation_code varchar2(150);
134 l_proc varchar2(72) := g_package||'return_legislation_code';
135 --
136 Begin
137 --
138 hr_utility.set_location('Entering:'|| l_proc, 10);
139 --
140 -- Ensure that all the mandatory parameter are not null
141 --
142 hr_api.mandatory_arg_error
143 (p_api_name => l_proc
144 ,p_argument => 'forum_id'
145 ,p_argument_value => p_forum_id
146 );
147 hr_api.mandatory_arg_error
148 (p_api_name => l_proc
149 ,p_argument => 'person_id'
150 ,p_argument_value => p_person_id
151 );
152 hr_api.mandatory_arg_error
153 (p_api_name => l_proc
154 ,p_argument => 'contact_id'
155 ,p_argument_value => p_contact_id
156 );
157 --
158 if (( nvl(ota_fns_bus.g_forum_id, hr_api.g_number)
159 = p_forum_id)
160 and ( nvl(ota_fns_bus.g_person_id, hr_api.g_number)
161 = p_person_id)
162 and ( nvl(ota_fns_bus.g_contact_id, hr_api.g_number)
163 = p_contact_id)) then
164 --
165 -- The legislation code has already been found with a previous
166 -- call to this function. Just return the value in the global
167 -- variable.
168 --
169 l_legislation_code := ota_fns_bus.g_legislation_code;
170 hr_utility.set_location(l_proc, 20);
171 else
172 --
173 -- The ID is different to the last call to this function
174 -- or this is the first call to this function.
175 --
176 open csr_leg_code;
177 fetch csr_leg_code into l_legislation_code;
178 --
179 if csr_leg_code%notfound then
180 --
181 -- The primary key is invalid therefore we must error
182 --
183 close csr_leg_code;
184 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
185 fnd_message.raise_error;
186 end if;
187 hr_utility.set_location(l_proc,30);
188 --
189 -- Set the global variables so the values are
190 -- available for the next call to this function.
191 --
192 close csr_leg_code;
193 ota_fns_bus.g_forum_id := p_forum_id;
194 ota_fns_bus.g_person_id := p_person_id;
195 ota_fns_bus.g_contact_id := p_contact_id;
196 ota_fns_bus.g_legislation_code := l_legislation_code;
197 end if;
198 hr_utility.set_location(' Leaving:'|| l_proc, 40);
199 return l_legislation_code;
200 end return_legislation_code;
201 --
202 -- ----------------------------------------------------------------------------
203 -- |-----------------------< chk_non_updateable_args >------------------------|
204 -- ----------------------------------------------------------------------------
205 -- {Start Of Comments}
206 --
207 -- Description:
208 -- This procedure is used to ensure that non updateable attributes have
209 -- not been updated. If an attribute has been updated an error is generated.
210 --
211 -- Pre Conditions:
212 -- g_old_rec has been populated with details of the values currently in
213 -- the database.
214 --
215 -- In Arguments:
216 -- p_rec has been populated with the updated values the user would like the
217 -- record set to.
218 --
219 -- Post Success:
220 -- Processing continues if all the non updateable attributes have not
221 -- changed.
222 --
223 -- Post Failure:
224 -- An application error is raised if any of the non updatable attributes
225 -- have been altered.
226 --
227 -- {End Of Comments}
228 -- ----------------------------------------------------------------------------
229 Procedure chk_non_updateable_args
230 (p_effective_date in date
231 ,p_rec in ota_fns_shd.g_rec_type
232 ) IS
233 --
234 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
235 --
236 Begin
237 --
238 -- Only proceed with the validation if a row exists for the current
239 -- record in the HR Schema.
240 --
241 IF NOT ota_fns_shd.api_updating
242 (p_forum_id => p_rec.forum_id
243 ,p_person_id => p_rec.person_id
244 ,p_contact_id => p_rec.contact_id
245 ,p_object_version_number => p_rec.object_version_number
246 ) THEN
247 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
248 fnd_message.set_token('PROCEDURE ', l_proc);
249 fnd_message.set_token('STEP ', '5');
250 fnd_message.raise_error;
251 END IF;
252 --
253 -- EDIT_HERE: Add checks to ensure non-updateable args have
254 -- not been updated.
255 --
256 End chk_non_updateable_args;
257 --
258 -- ----------------------------------------------------------------------------
259 -- |---------------------------< insert_validate >----------------------------|
260 -- ----------------------------------------------------------------------------
261 Procedure insert_validate
262 (p_effective_date in date
263 ,p_rec in ota_fns_shd.g_rec_type
264 ,p_forum_id in number
265 ,p_person_id in number
266 ,p_contact_id in number
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 hr_api.validate_bus_grp_id
277 (p_business_group_id => p_rec.business_group_id
278 ,p_associated_column1 => ota_fns_shd.g_tab_nam
279 || '.BUSINESS_GROUP_ID');
280 --
281 -- After validating the set of important attributes,
282 -- if Multiple Message detection is enabled and at least
283 -- one error has been found then abort further validation.
284 --
285 hr_multi_message.end_validation_set;
286 --
287 -- Validate Dependent Attributes
288 --
289 --
290 hr_utility.set_location(' Leaving:'||l_proc, 10);
291 End insert_validate;
292 --
293 -- ----------------------------------------------------------------------------
294 -- |---------------------------< update_validate >----------------------------|
295 -- ----------------------------------------------------------------------------
296 Procedure update_validate
297 (p_effective_date in date
298 ,p_rec in ota_fns_shd.g_rec_type
299 ) is
300 --
301 l_proc varchar2(72) := g_package||'update_validate';
302 --
303 Begin
304 hr_utility.set_location('Entering:'||l_proc, 5);
305 --
306 -- Call all supporting business operations
307 --
308 hr_api.validate_bus_grp_id
309 (p_business_group_id => p_rec.business_group_id
310 ,p_associated_column1 => ota_fns_shd.g_tab_nam
311 || '.BUSINESS_GROUP_ID');
312 --
313 -- After validating the set of important attributes,
314 -- if Multiple Message detection is enabled and at least
315 -- one error has been found then abort further validation.
316 --
317 hr_multi_message.end_validation_set;
318 --
319 -- Validate Dependent Attributes
320 --
321 chk_non_updateable_args
322 (p_effective_date => p_effective_date
323 ,p_rec => p_rec
324 );
325 --
326 --
327 hr_utility.set_location(' Leaving:'||l_proc, 10);
328 End update_validate;
329 --
330 -- ----------------------------------------------------------------------------
331 -- |---------------------------< delete_validate >----------------------------|
332 -- ----------------------------------------------------------------------------
333 Procedure delete_validate
334 (p_rec in ota_fns_shd.g_rec_type
335 ) is
336 --
337 l_proc varchar2(72) := g_package||'delete_validate';
338 --
339 Begin
340 hr_utility.set_location('Entering:'||l_proc, 5);
341 --
342 -- Call all supporting business operations
343 --
344 hr_utility.set_location(' Leaving:'||l_proc, 10);
345 End delete_validate;
346 --
347 end ota_fns_bus;