1 PACKAGE BODY pqp_gb_omp_earnings AS
2 /* $Header: pqpgboae.pkb 115.1 2002/12/03 11:31:20 cchappid noship $ */
3
4 g_proc_name VARCHAR2(80) := 'pqp_gb_omp_earnings.';
5 l_person_id per_assignments_f.person_id%TYPE;
6
7 /*========================================================================
8 * CALCULATE_SMP_AVERAGE_EARNINGS
9 * This Function Returns the Average Earnings (SMP Earnings)Value of
10 * a person effective of given date. First checks if there is a User
11 * entered value. If it is Y then returns the user entered value
12 * if N then calls SMP function.
13 *=======================================================================*/
14 FUNCTION calculate_smp_average_earnings
15 (p_assignment_id IN NUMBER
16 ,p_effective_date IN DATE
17 ,p_average_earnings OUT NOCOPY NUMBER
18 ,p_error_message OUT NOCOPY VARCHAR2
19 )
20 RETURN NUMBER IS
21 --
22
23 CURSOR csr_avg_ern ( p_person_id IN NUMBER,
24 p_effective_date IN DATE ) IS
25 SELECT user_entered, average_earnings_amount
26 FROM ssp_earnings_calculations
27 WHERE person_id = p_person_id
28 AND effective_date = p_effective_date ;
29
30 CURSOR csr_person_id ( p_assignment_id IN NUMBER ) IS
31 SELECT person_id
32 FROM per_assignments_f
33 WHERE assignment_id = p_assignment_id ;
34
35 l_smp_average_earnings NUMBER := 0;
36 l_user_entered VARCHAR2(1) := 'N' ;
37
38 BEGIN
39
40 -- Find the person id for the given assignment.
41
42 OPEN csr_person_id ( p_assignment_id => p_assignment_id ) ;
43 FETCH csr_person_id INTO l_person_id ;
44 IF csr_person_id%NOTFOUND THEN
45 CLOSE csr_person_id ;
46 p_error_message := 'Error in pqp_gb_omp_earnings:Person Id not'||
47 ' found for Assignment:'||p_assignment_id;
48 RETURN -1;
49 END IF ;
50 CLOSE csr_person_id ;
51
52 -- Cursor to get user entered indicator and the earnings value.
53 OPEN csr_avg_ern ( p_person_id => l_person_id
54 ,p_effective_date => p_effective_date ) ;
55 FETCH csr_avg_ern INTO l_user_entered,l_smp_average_earnings ;
56 CLOSE csr_avg_ern ;
57
58 IF l_user_entered = 'N' THEN
59 -- Call the SMP function to derive the Average Earnings calculation
60 -- as of the efective date.
61 ssp_ern_bus.calculate_average_earnings (
62 p_person_id => l_person_id
63 ,p_effective_date => p_effective_date
64 ,p_average_earnings_amount => l_smp_average_earnings
65 ,p_user_entered => 'N'
66 ,p_absence_category => 'M');
67
68 IF l_smp_average_earnings >= 0 THEN
69 p_average_earnings := l_smp_average_earnings;
70 RETURN 0;
71 ELSE
72 p_error_message := 'Error in pqp_gb_omp_earnings:Average Earnings'||
73 ' calculated was less than zero';
74 RETURN -1;
75 END IF;
76
77 ELSIF l_user_entered ='Y' THEN
78
79 p_average_earnings := l_smp_average_earnings;
80 RETURN 0;
81
82 END IF;
83
84 END calculate_smp_average_earnings;
85
86 END pqp_gb_omp_earnings;
87
[Home] [Help]