1 Package per_pyp_bus as
2 /* $Header: pepyprhi.pkh 120.6.12010000.1 2008/07/28 05:30:34 appldev ship $ */
3 --
4 --
5 g_validate_ss_change_pay varchar2(10) := 'N';
6 --
7 -------------------------------------------------------------------------------
8 -------------------------------< gen_last_change_date >------------------------
9 -------------------------------------------------------------------------------
10 --
11 --
12 -- Description
13 -- - Generates the last change date for a salary proposal record.
14 -- It sets the last_change_date column to null if it is the first record.
15 -- It set it to previous change date for the subsequent records.
16 --
17 -- Pre_condition:
18 -- None
19 --
20 -- In Arguments:
21 -- p_rec
22 --
23 -- Access Status:
24 -- Internal Table Handler Use Only.
25 --
26 procedure gen_last_change_date
27 (p_rec in out nocopy per_pyp_shd.g_rec_type);
28 --
29 -- ----------------------------------------------------------------------------
30 -- |----------------------< check_non_updateable_args >-----------------------|
31 -- ----------------------------------------------------------------------------
32 -- {Start Of Comments}
33 --
34 -- Description:
35 -- This procedure is used to ensure that non updatetable attributes have
36 -- not been updated. If an attribute has been updated an error is generated.
37 --
38 -- Pre Conditions:
39 -- g_old_rec has been populated with details of the values currently in
40 -- the database.
41 --
42 -- In Arguments:
43 -- p_rec has been populated with the updated values the user would like the
44 --
45 -- Post Success:
46 -- Processing continues if all the non updateable attributes have not
47 -- changed.
48 --
49 -- Post Failure:
50 -- An application error is raised if any of the non updatable attributes
51 -- (business_group_id, pay_proposal_id, multiple_components, change_date,
52 -- or assignment_id) have been altered.
53 --
54 -- Access Status:
55 -- Internal Table Handler Use Only.
56 --
57 --
58 --
59 --
60 Procedure check_non_updateable_args(p_rec in per_pyp_shd.g_rec_type);
61 --
62 -------------------------------------------------------------------------------
63 -------------------------------< chk_assignment_id_change_date >---------------
64 -------------------------------------------------------------------------------
65 --
66 --
67 --
68 -- Description:
69 -- - Validates that assignment_id exists and is date effctive on
70 -- per_assignmnets_f.
71 -- - Validates that the business group of the assignment is the same as the
72 -- business group of the pay proposal.
73 -- - Validates that the assignments has a valid pay_basis associated with it.
74 -- - Validates that the assingment system_status is not TERM_ASSIGN as of
75 -- change_date.
76 -- - Validates that the payroll status associated to the assignment is not
77 -- closed as of change_date.
78 -- - Validates that the change_date is after the last change_date.
79 -- - Validates that the change_date is unique
80 -- Note that the check for assignment type (i.e. TERM_ASSIG) and
81 -- valid pay_basis as of change date is done in chk_assignment.
82 -- validates that there is no other unapproved proposals
83 -- validates that the change_date is not updated if the proposal was -- Note: The chk_assignment_id and chk_change_date is merged into this procedure
84 -- because of close interrelations between assignment_id and change_date.
85 --
86 -- Pre_conditions:
87 -- A valid business_group_id
88 --
89 --
90 -- In Arguments:
91 -- p_pay_proposal_id
92 -- p_assignment_id
93 -- p_business_group_id
94 -- p_change_date
95 -- p_payroll_warning
96 -- p_object_version_number
97 --
98 -- Post Success:
99 -- Process continues if :
100 -- All the in parameters are valid.
101 --
102 -- Post Failure:
103 -- An application error is raised and processing is terminated if any of
104 -- the following cases are found :
105 -- - The assignmnet_id does not exist or is not date effective
106 -- - The business group of the assignment is invalid
107 -- - The assigment has not a pay_bases associated with it.
108 -- - The assignment system status is TERM_ASSIGN
109 -- - The change_date with the same date is already exists for the assinment.
110 -- - The change_date is before another existing change_date for the assignment.
111 --
112 -- Access Status
113 -- Internal Table Handler Use Only.
114 --
115 --
116
117 procedure chk_assignment_id_change_date
118 (p_pay_proposal_id in per_pay_proposals.pay_proposal_id%TYPE
119 ,p_business_group_id in per_pay_proposals.business_group_id%TYPE
120 ,p_assignment_id in per_pay_proposals.assignment_id%TYPE
121 ,p_change_date in per_pay_proposals.change_date%TYPE
122 ,p_payroll_warning out nocopy boolean
123 ,p_object_version_number in per_pay_proposals.object_version_number%TYPE
124 );
125 --
126 -- ---------------------------------------------------------------------------
127 -- |--------------------< chk_next_sal_review_date >-------------------------|
128 -- ---------------------------------------------------------------------------
129 --
130 --
131 --
132 -- Description:
133 -- - Validates that the next_sal_review_date is after the change_date.
134 -- - Set a warning flag if the assignment type is TERM_ASSIGN as of
135 -- - the next_sal_review_date.
136 --
137 --
138 -- Pre_conditions:
139 -- A valid change_date
140 -- A valid business_group_id
141 -- A valid assignment_id
142 --
143 -- In Arguments:
144 -- p_pay_proprosal_id
145 -- p_business_group_id
146 -- p_assignment_id
147 -- p_change_date
148 -- p_next_sal_review_date
149 -- p_object_version_number
150 -- p_inv_next_sal_date_warning
151 --
152 -- Post Success:
153 -- Process continues if :
154 -- The next_sal_review_date is null or
155 -- the next_sal_review_date is a date for which the assignment type is
156 -- not TERM_ASSIGN
157 --
158 -- Post Failure:
159 -- An application error is raised and processing is terminated if any of
160 -- the following cases are found :
161 -- - The assignment_id is null.
162 -- - The change_date is null.
163 -- - A warning flag is set if the next_sal_review_date is a date for which
164 -- the assignment type is TERM_ASSIGN.
165 --
166 -- Access Status
167 -- Internal Table Handler Use Only.
168 --
169 --
170 procedure chk_next_sal_review_date
171 (p_pay_proposal_id in per_pay_proposals.pay_proposal_id%TYPE
172 ,p_business_group_id in per_pay_proposals.business_group_id%TYPE
173 ,p_assignment_id in per_pay_proposals.assignment_id%TYPE
174 ,p_change_date in per_pay_proposals.change_date%TYPE
175 ,p_next_sal_review_date in per_pay_proposals.next_sal_review_date%TYPE
176 ,p_object_version_number in per_pay_proposals.object_version_number%TYPE
177 ,p_inv_next_sal_date_warning out nocopy boolean
178 );
179
180 --
181 -- ---------------------------------------------------------------------------
182 -- |------------------------< chk_pay_basis_change_date >-----------------|
183 -- ---------------------------------------------------------------------------
184 --
185 procedure chk_pay_basis_change_date
186 (p_assignment_id in per_pay_proposals.assignment_id%TYPE
187 ,p_change_date in per_pay_proposals.change_date%TYPE
188 ) ;
189
190
191
192 --
193 -- ---------------------------------------------------------------------------
194 -- |------------------------< chk_chg_next_sal_review_date >-----------------|
195 -- ---------------------------------------------------------------------------
196 --
197 --
198 -- Description:
199 -- - Derive the next_sal_review_date if the period and frequency information
200 -- - is set for the salary at the assignment level.
201 --
202 --
203 -- Pre_conditions:
204 -- A valid change_date
205 -- A valid business_group_id
206 -- A valid assignment_id
207 --
208 -- In Arguments:
209 -- p_pay_proprosal_id
210 -- p_business_group_id
211 -- p_assignment_id
212 -- p_change_date
213 -- p_next_sal_review_date
214 -- p_object_version_number
215 -- p_inv_next_sal_date_warning
216 --
217 -- Post Success:
218 -- Process continues if :
219 -- The next_sal_review_date is null or
220 -- the next_sal_review_date is a date for which the assignment type is
221 -- not TERM_ASSIGN
222 --
223 -- Post Failure:
224 -- An application error is raised and processing is terminated if any of
225 -- the following cases are found :
226 -- - The assignment_id is null.
227 -- - The change_date is null.
228 -- - A warning flag is set if the next_sal_review_date is a date for which
229 -- the assignment type is TERM_ASSIGN.
230 --
231 -- Access Status
232 -- Internal Table Handler Use Only.
233 --
234 --
235 procedure chk_chg_next_sal_review_date
236 (p_pay_proposal_id in per_pay_proposals.pay_proposal_id%TYPE
237 ,p_business_group_id in per_pay_proposals.business_group_id%TYPE
238 ,p_assignment_id in per_pay_proposals.assignment_id%TYPE
239 ,p_change_date in per_pay_proposals.change_date%TYPE
240 ,p_next_sal_review_date in out nocopy per_pay_proposals.next_sal_review_date%TYPE
241 ,p_object_version_number in per_pay_proposals.object_version_number%TYPE
242 ,p_inv_next_sal_date_warning out nocopy boolean
243 );
244 --
245 ----------------------------------------------------------------------------
246 -- |--------------------------< chk_proposed_salary >-----------------------
247 ----------------------------------------------------------------------------
248 --
249 -- Description:
250 -- - Check that the assignment's salary basis has an associated grade rate.
251 -- - If so, check if the assignment has a grade
252 -- - If so, check if the assignment has a rate assoiated with it.
253 -- - If so, check if the propoosed salary comes within the min and max
254 -- - specified for the grade and grade rate.
255 -- - If it doesn't, raise a warning to this effect.
256 --
257 -- - Validates that the proposed salary cannot be updated if the overall
258 -- proposal is approved (i.e. approved ='Y').
259 --
260 -- Pre_conditions:
261 -- A valid change_date
262 -- A valid business_group_id
263 -- A valid assignment_id
264 --
265 -- In Arguments:
266 -- p_pay_proprosal_id
267 -- p_business_group_id
268 -- p_assignment_id
269 -- p_change_date
270 -- p_proposed_salary_n
271 -- p_object_version_number
272 -- p_proposed_salary_warning
273 -- p_multiple_components
274 -- Post Success:
275 -- Process continues if :
276 -- The the assignment's salary basis has no garde assoicated with it or
277 -- the proposed salary is within the assignment's grade_rate.
278 -- The proposed salary has a valid currency_code associated with it.
279 --
280 --
281 -- Post Failure:
282 -- An application error is raised and processing is terminated if any of
283 -- the following cases are found :
284 -- - The assignment_id is null.
285 -- - The change_date is null.
286 -- - A warning flag is set if the proposed salary is not within min
287 -- and max of salary basis' grade rate.
288 --
289 -- Access Status
290 -- Internal Table Handler Use Only.
291 --
292 procedure chk_proposed_salary
293 (p_pay_proposal_id in per_pay_proposals.pay_proposal_id%TYPE
294 ,p_business_group_id in per_pay_proposals.business_group_id%TYPE
295 ,p_assignment_id in per_pay_proposals.assignment_id%TYPE
296 ,p_change_date in per_pay_proposals.change_date%TYPE
297 ,p_proposed_salary_n in per_pay_proposals.proposed_salary_n%TYPE
298 ,p_object_version_number in per_pay_proposals.object_version_number%TYPE
299 ,p_proposed_salary_warning out nocopy boolean
300 -- vkodedal added on 19-feb-2008 to fix multiple component upload issue
301 ,p_multiple_components in per_pay_proposals.multiple_components%TYPE
302 );
303 procedure is_salary_in_range_int
304 (p_organization_id in per_all_assignments_f.organization_id%TYPE
305 ,p_pay_basis_id in per_all_assignments_f.pay_basis_id%TYPE
306 ,p_position_id in per_all_assignments_f.position_id%TYPE
307 ,p_grade_id in per_all_assignments_f.grade_id%TYPE
308 ,p_normal_hours in per_all_assignments_f.normal_hours%TYPE
309 ,p_frequency in per_all_assignments_f.frequency%TYPE
310 ,p_business_group_id in per_pay_proposals.business_group_id%TYPE
311 ,p_change_date in per_pay_proposals.change_date%TYPE
312 ,p_proposed_salary_n in per_pay_proposals.proposed_salary_n%TYPE
313 ,p_prop_salary_link_warning out nocopy boolean
314 ,p_prop_salary_ele_warning out nocopy boolean
315 ,p_prop_salary_grade_warning out nocopy boolean
316 );
317 --
318 ------------------------------------------------------------------------
319 -- |-----------------< chk_approved >-----------------------------------
320 ------------------------------------------------------------------------
321 --
322 -- Description:
323 -- Validates that the approved can only have values of 'Y' and 'N'
324 -- Validates that it is a mandatory column
325 -- Checks the value of the approved flag is 'Y' for the first emp proposal
326 -- automatically.
327 -- Checks the value for an applicants proposal is 'N'
328 -- Validates that the approved flag can not be set to 'Y' if the proposed
329 -- salary is null.
330 -- Validates that when the approved flag is set to 'Y' if some unapproved
331 -- components then raising a warning message.
332 -- Validates that the approved falg can not be set to 'N' if the proposal
333 -- is not the latest proposals.
334 --
335 -- Pre_conditions:
336 -- A valid change_date
337 -- A valid business_group_id
338 -- A valid assignment_id
339 --
340 -- In Arguments:
341 -- p_pay_proprosal_id
342 -- p_business_group_id
343 -- p_assignment_id
344 -- p_change_date
345 -- p_proposed_salary_n
346 -- p_object_version_number
347 -- p_approved_warning
348 --
349 -- Post Success:
350 -- Process continues if :
351 -- The value of the approved is 'Y' or 'N'
352 -- The proposed salary is not null when approved is set to 'Y'.
353 --
354 --
355 --
356 -- Post Failure:
357 -- An application error is raised and processing is terminated if any of
358 -- the following cases are found :
359 -- - The assignment_id is null.
360 -- - The change_date is null.
361 -- - A warning flag is set if the approved flag is set to yes while
362 -- - there are some outstanding unapproved components.
363 --
364 -- Access Status
365 -- Internal Table Handler Use Only.
366 --
367 procedure chk_approved
368 (p_pay_proposal_id in per_pay_proposals.pay_proposal_id%TYPE
369 ,p_business_group_id in per_pay_proposals.business_group_id%TYPE
370 ,p_assignment_id in per_pay_proposals.assignment_id%TYPE
371 ,p_change_date in per_pay_proposals.change_date%TYPE
372 ,p_proposed_salary_n in per_pay_proposals.proposed_salary_n%TYPE
373 ,p_object_version_number in per_pay_proposals.object_version_number%TYPE
374 ,p_approved in per_pay_proposals.approved%TYPE
375 ,p_approved_warning out nocopy boolean
376 );
377 --
378 -- -----------------------------------------------------------------------
379 -- |---------------------< chk_forced_ranking >--------------------------|
380 -- -----------------------------------------------------------------------
381 --
382 -- Description:
386 --
383 -- Validates the forced ranking.
384 --
385 -- Pre-conditions:
387 -- In Arguments:
388 -- p_forced_ranking
389 --
390 -- Post Success:
391 -- Process continues if :
392 -- p_forced_ranking is a positive integer
393 --
394 -- Post Failure:
395 -- An application error is raised and processing is terminated if any of
396 -- the following cases are found :
397 -- - p_forced_ranking is less than 1
398 --
399 --
400 procedure chk_forced_ranking
401 (p_forced_ranking in per_pay_proposals.forced_ranking%TYPE);
402 --
403 -- ----------------------------------------------------------------------------
404 -- |----------------------< chk_performance_review_id >-----------------------|
405 -- ----------------------------------------------------------------------------
406 --
407 -- Description:
408 -- Validates that the value entered for performance_review_id is valid.
409 --
410 -- Pre-conditions:
411 -- p_assignment_id is valid
412 --
413 -- In Arguments:
414 -- p_pay_proposal_id
415 -- p_assignment_id
416 -- p_performance_review_id
417 -- p_object_version_number
418 --
419 -- Post Success:
420 -- Processing continues if :
421 -- - The performance_review_id value is valid
422 --
423 -- Post Failure:
424 -- An application error is raised and processing is terminated if any
425 -- - The performance_review_id value is invalid
426 --
427 -- Access Status:
428 -- Internal Table Handler Use Only.
429 --
430 procedure chk_performance_review_id
431 (p_pay_proposal_id in per_pay_proposals.pay_proposal_id%TYPE
432 ,p_assignment_id in per_pay_proposals.assignment_id%TYPE
433 ,p_performance_review_id in per_pay_proposals.performance_review_id%TYPE
434 ,p_object_version_number in per_pay_proposals.object_version_number%TYPE
435 );
436 --
437 -------------------------------------------------------------------------------
438 -- |---------------------------< insert_validate >----------------------------|
439 -- ----------------------------------------------------------------------------
440 -- {Start Of Comments}
441 --
442 -- Description:
443 -- This procedure controls the execution of all insert business rules
444 -- validation.
445 --
446 -- Pre Conditions:
447 -- This private procedure is called from ins procedure.
448 --
449 -- In Parameters:
450 -- A Pl/Sql record structre.
451 --
452 -- Post Success:
453 -- Processing continues.
454 --
455 -- Post Failure:
456 -- If a business rules fails the error will not be handled by this procedure
457 -- unless explicity coded.
458 --
459 -- Developer Implementation Notes:
460 -- For insert, your business rules should be executed from this procedure and
461 -- should ideally (unless really necessary) just be straight procedure or
462 -- function calls. Try and avoid using conditional branching logic.
463 --
464 -- Access Status:
465 -- Internal Table Handler Use Only.
466 --
467 -- {End Of Comments}
468 -- ----------------------------------------------------------------------------
469 Procedure insert_validate
470 (p_rec in out nocopy per_pyp_shd.g_rec_type
471 ,p_inv_next_sal_date_warning out nocopy boolean
472 ,p_proposed_salary_warning out nocopy boolean
473 ,p_approved_warning out nocopy boolean
474 ,p_payroll_warning out nocopy boolean
475 );
476 --
477 -- ----------------------------------------------------------------------------
478 -- |---------------------------< update_validate >----------------------------|
479 -- ----------------------------------------------------------------------------
480 -- {Start Of Comments}
481 --
482 -- Description:
483 -- This procedure controls the execution of all update business rules
484 -- validation.
485 --
486 -- Pre Conditions:
487 -- This private procedure is called from upd procedure.
488 --
489 -- In Parameters:
490 -- A Pl/Sql record structre.
491 --
492 -- Post Success:
493 -- Processing continues.
494 --
495 -- Post Failure:
496 -- If a business rules fails the error will not be handled by this procedure
497 -- unless explicity coded.
498 --
499 -- Developer Implementation Notes:
500 -- For update, your business rules should be executed from this procedure and
501 -- should ideally (unless really necessary) just be straight procedure or
502 -- function calls. Try and avoid using conditional branching logic.
503 --
504 -- Access Status:
505 -- Internal Table Handler Use Only.
506 --
507 -- {End Of Comments}
508 -- ----------------------------------------------------------------------------
509 Procedure update_validate
510 (p_rec in out nocopy per_pyp_shd.g_rec_type
511 ,p_inv_next_sal_date_warning out nocopy boolean
512 ,p_proposed_salary_warning out nocopy boolean
513 ,p_approved_warning out nocopy boolean
514 ,p_payroll_warning out nocopy boolean
515 );
516 --
517 -- ----------------------------------------------------------------------------
518 -- |---------------------------< delete_validate >----------------------------|
519 -- ----------------------------------------------------------------------------
520 -- {Start Of Comments}
521 --
522 -- Description:
523 -- This procedure controls the execution of all delete business rules
524 -- validation.
525 --
526 -- Pre Conditions:
527 -- This private procedure is called from del procedure.
528 --
529 -- In Parameters:
530 -- A Pl/Sql record structre.
531 --
532 -- Post Success:
533 -- Processing continues.
534 --
535 -- Post Failure:
536 -- If a business rules fails the error will not be handled by this procedure
537 -- unless explicity coded.
538 --
539 -- Developer Implementation Notes:
540 -- For delete, your business rules should be executed from this procedure and
541 -- should ideally (unless really necessary) just be straight procedure or
542 -- function calls. Try and avoid using conditional branching logic.
543 --
544 -- Access Status:
545 -- Internal Table Handler Use Only.
546 --
547 -- {End Of Comments}
548 -- ----------------------------------------------------------------------------
549 Procedure delete_validate
550 (p_rec in per_pyp_shd.g_rec_type
551 ,p_salary_warning out nocopy boolean
552 );
553 --
554 --
555 -- ---------------------------------------------------------------------------
556 -- |---------------------< return_legislation_code >-------------------------|
557 -- ---------------------------------------------------------------------------
558 --
559 function return_legislation_code
560 (p_pay_proposal_id in number
561 ) return varchar2;
562 --
563 end per_pyp_bus;
[Home] [Help]