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