1 Package per_pyp_bus AUTHID CURRENT_USER as
2 /* $Header: pepyprhi.pkh 120.8 2009/06/10 12:58:59 vkodedal 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 --vkodedal 03-Jun-2009 8452388
317 ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
318 );
319
320 ---vkodedal 8587143 10-Jun-2009
321 ---over load the same to make sure old core hr forms won't fail
322 ---PERWSQHM.fmb has a reference to this procedure and assignment id will be null
323 ---when inserting
324 procedure is_salary_in_range_int
325 (p_organization_id in per_all_assignments_f.organization_id%TYPE
326 ,p_pay_basis_id in per_all_assignments_f.pay_basis_id%TYPE
327 ,p_position_id in per_all_assignments_f.position_id%TYPE
328 ,p_grade_id in per_all_assignments_f.grade_id%TYPE
329 ,p_normal_hours in per_all_assignments_f.normal_hours%TYPE
330 ,p_frequency in per_all_assignments_f.frequency%TYPE
331 ,p_business_group_id in per_pay_proposals.business_group_id%TYPE
332 ,p_change_date in per_pay_proposals.change_date%TYPE
333 ,p_proposed_salary_n in per_pay_proposals.proposed_salary_n%TYPE
334 ,p_prop_salary_link_warning out nocopy boolean
335 ,p_prop_salary_ele_warning out nocopy boolean
336 ,p_prop_salary_grade_warning out nocopy boolean
337 );
338 --
339 ------------------------------------------------------------------------
340 -- |-----------------< chk_approved >-----------------------------------
341 ------------------------------------------------------------------------
342 --
343 -- Description:
344 -- Validates that the approved can only have values of 'Y' and 'N'
345 -- Validates that it is a mandatory column
346 -- Checks the value of the approved flag is 'Y' for the first emp proposal
347 -- automatically.
348 -- Checks the value for an applicants proposal is 'N'
349 -- Validates that the approved flag can not be set to 'Y' if the proposed
350 -- salary is null.
351 -- Validates that when the approved flag is set to 'Y' if some unapproved
352 -- components then raising a warning message.
353 -- Validates that the approved falg can not be set to 'N' if the proposal
354 -- is not the latest proposals.
355 --
356 -- Pre_conditions:
357 -- A valid change_date
358 -- A valid business_group_id
359 -- A valid assignment_id
360 --
361 -- In Arguments:
362 -- p_pay_proprosal_id
363 -- p_business_group_id
364 -- p_assignment_id
365 -- p_change_date
366 -- p_proposed_salary_n
367 -- p_object_version_number
368 -- p_approved_warning
369 --
370 -- Post Success:
371 -- Process continues if :
372 -- The value of the approved is 'Y' or 'N'
373 -- The proposed salary is not null when approved is set to 'Y'.
374 --
375 --
376 --
377 -- Post Failure:
378 -- An application error is raised and processing is terminated if any of
382 -- - A warning flag is set if the approved flag is set to yes while
379 -- the following cases are found :
380 -- - The assignment_id is null.
381 -- - The change_date is null.
383 -- - there are some outstanding unapproved components.
384 --
385 -- Access Status
386 -- Internal Table Handler Use Only.
387 --
388 procedure chk_approved
389 (p_pay_proposal_id in per_pay_proposals.pay_proposal_id%TYPE
390 ,p_business_group_id in per_pay_proposals.business_group_id%TYPE
391 ,p_assignment_id in per_pay_proposals.assignment_id%TYPE
392 ,p_change_date in per_pay_proposals.change_date%TYPE
393 ,p_proposed_salary_n in per_pay_proposals.proposed_salary_n%TYPE
394 ,p_object_version_number in per_pay_proposals.object_version_number%TYPE
395 ,p_approved in per_pay_proposals.approved%TYPE
396 ,p_approved_warning out nocopy boolean
397 );
398 --
399 -- -----------------------------------------------------------------------
400 -- |---------------------< chk_forced_ranking >--------------------------|
401 -- -----------------------------------------------------------------------
402 --
403 -- Description:
404 -- Validates the forced ranking.
405 --
406 -- Pre-conditions:
407 --
408 -- In Arguments:
409 -- p_forced_ranking
410 --
411 -- Post Success:
412 -- Process continues if :
413 -- p_forced_ranking is a positive integer
414 --
415 -- Post Failure:
416 -- An application error is raised and processing is terminated if any of
417 -- the following cases are found :
418 -- - p_forced_ranking is less than 1
419 --
420 --
421 procedure chk_forced_ranking
422 (p_forced_ranking in per_pay_proposals.forced_ranking%TYPE);
423 --
424 -- ----------------------------------------------------------------------------
425 -- |----------------------< chk_performance_review_id >-----------------------|
426 -- ----------------------------------------------------------------------------
427 --
428 -- Description:
429 -- Validates that the value entered for performance_review_id is valid.
430 --
431 -- Pre-conditions:
432 -- p_assignment_id is valid
433 --
434 -- In Arguments:
435 -- p_pay_proposal_id
436 -- p_assignment_id
437 -- p_performance_review_id
438 -- p_object_version_number
439 --
440 -- Post Success:
441 -- Processing continues if :
442 -- - The performance_review_id value is valid
443 --
444 -- Post Failure:
445 -- An application error is raised and processing is terminated if any
446 -- - The performance_review_id value is invalid
447 --
448 -- Access Status:
449 -- Internal Table Handler Use Only.
450 --
451 procedure chk_performance_review_id
452 (p_pay_proposal_id in per_pay_proposals.pay_proposal_id%TYPE
453 ,p_assignment_id in per_pay_proposals.assignment_id%TYPE
454 ,p_performance_review_id in per_pay_proposals.performance_review_id%TYPE
455 ,p_object_version_number in per_pay_proposals.object_version_number%TYPE
456 );
457 --
458 -------------------------------------------------------------------------------
459 -- |---------------------------< insert_validate >----------------------------|
460 -- ----------------------------------------------------------------------------
461 -- {Start Of Comments}
462 --
463 -- Description:
464 -- This procedure controls the execution of all insert business rules
465 -- validation.
466 --
467 -- Pre Conditions:
468 -- This private procedure is called from ins procedure.
469 --
470 -- In Parameters:
471 -- A Pl/Sql record structre.
472 --
473 -- Post Success:
474 -- Processing continues.
475 --
476 -- Post Failure:
477 -- If a business rules fails the error will not be handled by this procedure
478 -- unless explicity coded.
479 --
480 -- Developer Implementation Notes:
481 -- For insert, your business rules should be executed from this procedure and
482 -- should ideally (unless really necessary) just be straight procedure or
483 -- function calls. Try and avoid using conditional branching logic.
484 --
485 -- Access Status:
486 -- Internal Table Handler Use Only.
487 --
488 -- {End Of Comments}
489 -- ----------------------------------------------------------------------------
490 Procedure insert_validate
491 (p_rec in out nocopy per_pyp_shd.g_rec_type
492 ,p_inv_next_sal_date_warning out nocopy boolean
493 ,p_proposed_salary_warning out nocopy boolean
494 ,p_approved_warning out nocopy boolean
495 ,p_payroll_warning out nocopy boolean
496 );
497 --
498 -- ----------------------------------------------------------------------------
499 -- |---------------------------< update_validate >----------------------------|
500 -- ----------------------------------------------------------------------------
501 -- {Start Of Comments}
502 --
503 -- Description:
504 -- This procedure controls the execution of all update business rules
505 -- validation.
506 --
507 -- Pre Conditions:
508 -- This private procedure is called from upd procedure.
509 --
510 -- In Parameters:
511 -- A Pl/Sql record structre.
512 --
513 -- Post Success:
517 -- If a business rules fails the error will not be handled by this procedure
514 -- Processing continues.
515 --
516 -- Post Failure:
518 -- unless explicity coded.
519 --
520 -- Developer Implementation Notes:
521 -- For update, your business rules should be executed from this procedure and
522 -- should ideally (unless really necessary) just be straight procedure or
523 -- function calls. Try and avoid using conditional branching logic.
524 --
525 -- Access Status:
526 -- Internal Table Handler Use Only.
527 --
528 -- {End Of Comments}
529 -- ----------------------------------------------------------------------------
530 Procedure update_validate
531 (p_rec in out nocopy per_pyp_shd.g_rec_type
532 ,p_inv_next_sal_date_warning out nocopy boolean
533 ,p_proposed_salary_warning out nocopy boolean
534 ,p_approved_warning out nocopy boolean
535 ,p_payroll_warning out nocopy boolean
536 );
537 --
538 -- ----------------------------------------------------------------------------
539 -- |---------------------------< delete_validate >----------------------------|
540 -- ----------------------------------------------------------------------------
541 -- {Start Of Comments}
542 --
543 -- Description:
544 -- This procedure controls the execution of all delete business rules
545 -- validation.
546 --
547 -- Pre Conditions:
548 -- This private procedure is called from del procedure.
549 --
550 -- In Parameters:
551 -- A Pl/Sql record structre.
552 --
553 -- Post Success:
554 -- Processing continues.
555 --
556 -- Post Failure:
557 -- If a business rules fails the error will not be handled by this procedure
558 -- unless explicity coded.
559 --
560 -- Developer Implementation Notes:
561 -- For delete, your business rules should be executed from this procedure and
562 -- should ideally (unless really necessary) just be straight procedure or
563 -- function calls. Try and avoid using conditional branching logic.
564 --
565 -- Access Status:
566 -- Internal Table Handler Use Only.
567 --
568 -- {End Of Comments}
569 -- ----------------------------------------------------------------------------
570 Procedure delete_validate
571 (p_rec in per_pyp_shd.g_rec_type
572 ,p_salary_warning out nocopy boolean
573 );
574 --
575 --
576 -- ---------------------------------------------------------------------------
577 -- |---------------------< return_legislation_code >-------------------------|
578 -- ---------------------------------------------------------------------------
579 --
580 function return_legislation_code
581 (p_pay_proposal_id in number
582 ) return varchar2;
583 --
584 end per_pyp_bus;
[Home] [Help]