1 Package Body psp_pso_bus as
2 /* $Header: PSPSORHB.pls 120.2 2005/11/28 23:27 dpaudel noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' psp_pso_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_salary_cap_override_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_salary_cap_override_id in number
22 ,p_associated_column1 in varchar2 default null
23 ) is
24 --
25 -- Declare cursor
26 --
27 -- EDIT_HERE In the following cursor statement add join(s) between
28 -- psp_salary_cap_overrides and PER_BUSINESS_GROUPS_PERF
29 -- so that the security_group_id for
30 -- the current business group context can be derived.
31 -- Remove this comment when the edit has been completed.
32 cursor csr_sec_grp is
33 select pbg.security_group_id,
34 pbg.legislation_code
35 from per_business_groups_perf pbg
36 , psp_salary_cap_overrides pso
37 -- , EDIT_HERE table_name(s) 333
38 where pso.salary_cap_override_id = p_salary_cap_override_id;
39 -- and pbg.business_group_id = EDIT_HERE 333.business_group_id;
40 --
41 -- Declare local variables
42 --
43 l_security_group_id number;
44 l_proc varchar2(72) := g_package||'set_security_group_id';
45 l_legislation_code varchar2(150);
46 --
47 begin
48 --
49 hr_utility.set_location('Entering:'|| l_proc, 10);
50 --
51 -- Ensure that all the mandatory parameter are not null
52 --
53 hr_api.mandatory_arg_error
54 (p_api_name => l_proc
55 ,p_argument => 'salary_cap_override_id'
56 ,p_argument_value => p_salary_cap_override_id
57 );
58 --
59 open csr_sec_grp;
60 fetch csr_sec_grp into l_security_group_id
61 , l_legislation_code;
62 --
63 if csr_sec_grp%notfound then
64 --
65 close csr_sec_grp;
66 --
67 -- The primary key is invalid therefore we must error
68 --
69 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
70 hr_multi_message.add
71 (p_associated_column1
72 => nvl(p_associated_column1,'SALARY_CAP_OVERRIDE_ID')
73 );
74 --
75 else
76 close csr_sec_grp;
77 --
78 -- Set the security_group_id in CLIENT_INFO
79 --
80 hr_api.set_security_group_id
81 (p_security_group_id => l_security_group_id
82 );
83 --
84 -- Set the sessions legislation context in HR_SESSION_DATA
85 --
86 hr_api.set_legislation_context(l_legislation_code);
87 end if;
88 --
89 hr_utility.set_location(' Leaving:'|| l_proc, 20);
90 --
91 end set_security_group_id;
92 --
93 -- ---------------------------------------------------------------------------
94 -- |---------------------< return_legislation_code >-------------------------|
95 -- ---------------------------------------------------------------------------
96 --
97 Function return_legislation_code
98 (p_salary_cap_override_id in number
99 )
100 Return Varchar2 Is
101 --
102 -- Declare cursor
103 --
104 -- EDIT_HERE In the following cursor statement add join(s) between
105 -- psp_salary_cap_overrides and PER_BUSINESS_GROUPS_PERF
106 -- so that the legislation_code for
107 -- the current business group context can be derived.
108 -- Remove this comment when the edit has been completed.
109 cursor csr_leg_code is
110 select pbg.legislation_code
111 from per_business_groups_perf pbg
112 , psp_salary_cap_overrides pso
113 -- , EDIT_HERE table_name(s) 333
114 where pso.salary_cap_override_id = p_salary_cap_override_id;
115 -- and pbg.business_group_id = EDIT_HERE 333.business_group_id;
116 --
117 -- Declare local variables
118 --
119 l_legislation_code varchar2(150);
120 l_proc varchar2(72) := g_package||'return_legislation_code';
121 --
122 Begin
123 --
124 hr_utility.set_location('Entering:'|| l_proc, 10);
125 --
126 -- Ensure that all the mandatory parameter are not null
127 --
128 hr_api.mandatory_arg_error
129 (p_api_name => l_proc
130 ,p_argument => 'salary_cap_override_id'
131 ,p_argument_value => p_salary_cap_override_id
132 );
133 --
134 if ( nvl(psp_pso_bus.g_salary_cap_override_id, hr_api.g_number)
135 = p_salary_cap_override_id) then
136 --
137 -- The legislation code has already been found with a previous
138 -- call to this function. Just return the value in the global
139 -- variable.
140 --
141 l_legislation_code := psp_pso_bus.g_legislation_code;
142 hr_utility.set_location(l_proc, 20);
143 else
144 --
145 -- The ID is different to the last call to this function
146 -- or this is the first call to this function.
147 --
148 open csr_leg_code;
149 fetch csr_leg_code into l_legislation_code;
150 --
151 if csr_leg_code%notfound then
152 --
153 -- The primary key is invalid therefore we must error
154 --
155 close csr_leg_code;
156 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
157 fnd_message.raise_error;
158 end if;
159 hr_utility.set_location(l_proc,30);
160 --
161 -- Set the global variables so the values are
162 -- available for the next call to this function.
163 --
164 close csr_leg_code;
165 psp_pso_bus.g_salary_cap_override_id := p_salary_cap_override_id;
166 psp_pso_bus.g_legislation_code := l_legislation_code;
167 end if;
168 hr_utility.set_location(' Leaving:'|| l_proc, 40);
169 return l_legislation_code;
170 end return_legislation_code;
171 --
172 -- ----------------------------------------------------------------------------
173 -- |-----------------------< chk_non_updateable_args >------------------------|
174 -- ----------------------------------------------------------------------------
175 -- {Start Of Comments}
176 --
177 -- Description:
178 -- This procedure is used to ensure that non updateable attributes have
179 -- not been updated. If an attribute has been updated an error is generated.
180 --
181 -- Pre Conditions:
182 -- g_old_rec has been populated with details of the values currently in
183 -- the database.
184 --
185 -- In Arguments:
186 -- p_rec has been populated with the updated values the user would like the
187 -- record set to.
188 --
189 -- Post Success:
190 -- Processing continues if all the non updateable attributes have not
191 -- changed.
192 --
193 -- Post Failure:
194 -- An application error is raised if any of the non updatable attributes
195 -- have been altered.
196 --
197 -- {End Of Comments}
198 -- ----------------------------------------------------------------------------
199 Procedure chk_non_updateable_args
200 (p_rec in psp_pso_shd.g_rec_type
201 ) IS
202 --
203 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
204 --
205 Begin
206 --
207 -- Only proceed with the validation if a row exists for the current
208 -- record in the HR Schema.
209 --
210 IF NOT psp_pso_shd.api_updating
211 (p_salary_cap_override_id => p_rec.salary_cap_override_id
212 ,p_object_version_number => p_rec.object_version_number
213 ) THEN
214 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
215 fnd_message.set_token('PROCEDURE ', l_proc);
216 fnd_message.set_token('STEP ', '5');
217 fnd_message.raise_error;
218 END IF;
219 --
220 -- EDIT_HERE: Add checks to ensure non-updateable args have
221 -- not been updated.
222 --
223 End chk_non_updateable_args;
224
225
226 --
227 -- ----------------------------------------------------------------------------
228 -- |---------------------------< validate_dates >----------------------------|
229 -- ----------------------------------------------------------------------------
230 --
231
232 PROCEDURE validate_dates( p_salary_cap_override_id IN NUMBER
233 , p_funding_source_code IN VARCHAR2
234 , p_project_id IN NUMBER
235 , p_start_date IN DATE
236 , p_end_date IN DATE
237 , p_currency_code IN VARCHAR2 ) IS
238 --When a record is being inserted or modified, check for the dates. The dates shouldn't overlap with the dates of
239 --previously entered accounts of this organization. If the start date is less than the start date of a previously
240 --entered account of this organization, end date must be entered else the period will overlap. Also, end date must be
241 --greater than the start date.
242
243 --Cursor date_selection_csr: Select the dates for the accounts(other than the current one) of this organization.
244 CURSOR salary_cap_date_selection_csr IS
245 SELECT start_date, end_date
246 FROM psp_salary_caps
247 WHERE funding_source_code = p_funding_source_code
248 AND currency_code = p_currency_code;
249
250 CURSOR date_selection_csr IS
251 SELECT start_date, end_date
252 FROM psp_salary_cap_overrides
253 WHERE funding_source_code = p_funding_source_code
254 AND project_id = p_project_id
255 AND currency_code = p_currency_code
256 AND salary_cap_override_id <> NVL(p_salary_cap_override_id,-999);
257
258 CURSOR project_number_csr IS
259 SELECT project_number
260 FROM pa_projects_expend_v
261 WHERE project_id = p_project_id;
262
263 l_start_date DATE;
264 l_end_date DATE;
265 l_project_number VARCHAR2(25);
266 l_start_flag BOOLEAN := false;
267 l_end_flag BOOLEAN := false;
268
269 BEGIN
270 IF p_end_date < p_start_date THEN
271 fnd_message.set_name ('PSP', 'PSP_ED_GREATERTHAN_BD');
272 fnd_message.raise_error;
273 END IF;
274
275 OPEN salary_cap_date_selection_csr;
276 LOOP
277 FETCH salary_cap_date_selection_csr INTO l_start_date, l_end_date;
278 EXIT WHEN salary_cap_date_selection_csr%NOTFOUND;
279 IF p_start_date >= l_start_date THEN
280 l_start_flag := true;
281 END IF;
282 IF p_end_date <= l_end_date THEN
283 l_end_flag := true;
284 END IF;
285 END LOOP;
286
287 IF (l_start_flag = false) OR (l_end_flag = FALSE) THEN
288 fnd_message.set_name ('PSP', 'PSP_SC_NOT_DEFINED');
289 fnd_message.raise_error;
290 END IF;
291
292 OPEN date_selection_csr;
293 LOOP
294 fetch date_selection_csr INTO l_start_date, l_end_date;
295 EXIT WHEN date_selection_csr%NOTFOUND;
296 IF p_start_date BETWEEN l_start_date AND l_end_date THEN
297 OPEN project_number_csr;
298 FETCH project_number_csr INTO l_project_number;
299 CLOSE project_number_csr;
300
301 fnd_message.set_name ('PSP', 'PSP_SC_BEGINDATE_OVERLAP_PRJ');
302 fnd_message.set_token('BEGIN_DATE', TO_CHAR(l_start_date));
303 fnd_message.set_token('END_DATE', l_end_date);
304 fnd_message.set_token('PROJECT_NUMBER', l_project_number);
305 fnd_message.raise_error;
306 END IF;
307 END LOOP;
308 CLOSE date_selection_csr;
309
310
311 OPEN date_selection_csr;
312 LOOP
313 fetch date_selection_csr INTO l_start_date , l_end_date ;
314 EXIT WHEN date_selection_csr%NOTFOUND ;
315 IF p_end_date BETWEEN l_start_date AND l_end_date THEN
316 OPEN project_number_csr;
317 FETCH project_number_csr INTO l_project_number;
318 CLOSE project_number_csr;
319
320 fnd_message.set_name('PSP', 'PSP_SC_ENDDATE_OVERLAP_PRJ');
321 fnd_message.set_token('BEGIN_DATE', TO_CHAR(l_start_date ));
322 fnd_message.set_token('END_DATE', l_end_date);
323 fnd_message.set_token('PROJECT_NUMBER', l_project_number);
324 fnd_message.raise_error;
325 ELSIF p_start_date < l_start_date AND p_end_date > l_end_date THEN
326 OPEN project_number_csr;
327 FETCH project_number_csr INTO l_project_number;
328 CLOSE project_number_csr;
329
330 fnd_message.set_name('PSP', 'PSP_SC_ENDDATE_OVERLAP_PRJ');
331 fnd_message.set_token('BEGIN_DATE', TO_CHAR(l_start_date));
332 fnd_message.set_token('END_DATE', l_end_date);
333 fnd_message.set_token('PROJECT_NUMBER', l_project_number);
334 fnd_message.raise_error;
335 END IF;
336 END LOOP;
337 CLOSE date_selection_csr;
338 END validate_dates;
339
340
341
342
343 --
344 -- ----------------------------------------------------------------------------
345 -- |---------------------------< insert_validate >----------------------------|
346 -- ----------------------------------------------------------------------------
347 Procedure insert_validate
348 (p_rec in psp_pso_shd.g_rec_type
349 ) is
350 --
351 l_proc varchar2(72) := g_package||'insert_validate';
352 --
353 Begin
354 hr_utility.set_location('Entering:'||l_proc, 5);
355 --
356 -- Call all supporting business operations
357 --
358 --
359 validate_dates( p_salary_cap_override_id => p_rec.salary_cap_override_id
360 , p_funding_source_code => p_rec.funding_source_code
361 , p_project_id => p_rec.project_id
362 , p_start_date => p_rec.start_date
363 , p_end_date => p_rec.end_date
364 , p_currency_code => p_rec.currency_code) ;
365
366 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
367 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
368 --
369 -- Validate Dependent Attributes
370 --
371 --
372 hr_utility.set_location(' Leaving:'||l_proc, 10);
373 End insert_validate;
374 --
375 -- ----------------------------------------------------------------------------
376 -- |---------------------------< update_validate >----------------------------|
377 -- ----------------------------------------------------------------------------
378 Procedure update_validate
379 (p_rec in psp_pso_shd.g_rec_type
380 ) is
381 --
382 l_proc varchar2(72) := g_package||'update_validate';
383 --
384 Begin
385 hr_utility.set_location('Entering:'||l_proc, 5);
386 --
387 -- Call all supporting business operations
388 --
389 --
390 validate_dates( p_salary_cap_override_id => p_rec.salary_cap_override_id
391 , p_funding_source_code => p_rec.funding_source_code
392 , p_project_id => p_rec.project_id
393 , p_start_date => p_rec.start_date
394 , p_end_date => p_rec.end_date
395 , p_currency_code => p_rec.currency_code) ;
396
397 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
398 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
399 --
400 -- Validate Dependent Attributes
401 --
402 chk_non_updateable_args
403 (p_rec => p_rec
404 );
405 --
406 --
407 hr_utility.set_location(' Leaving:'||l_proc, 10);
408 End update_validate;
409 --
410 -- ----------------------------------------------------------------------------
411 -- |---------------------------< delete_validate >----------------------------|
412 -- ----------------------------------------------------------------------------
413 Procedure delete_validate
414 (p_rec in psp_pso_shd.g_rec_type
415 ) is
416 --
417 l_proc varchar2(72) := g_package||'delete_validate';
418 --
419 Begin
420 hr_utility.set_location('Entering:'||l_proc, 5);
421 --
422 -- Call all supporting business operations
423 --
424 hr_utility.set_location(' Leaving:'||l_proc, 10);
425 End delete_validate;
426 --
427 end psp_pso_bus;
[Home] [Help]