1 PACKAGE BODY PA_SEARCH_GLOB AS
2 --$Header: PARIGLBB.pls 120.2 2006/06/21 04:30:14 avaithia noship $
3 --
4
5
6 PROCEDURE Check_Competence_Match(p_search_mode IN VARCHAR2,
7 p_person_id IN per_all_people_f.person_id%TYPE,
8 p_requirement_id IN pa_project_assignments.assignment_id%TYPE,
9 x_mandatory_match OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
10 x_mandatory_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
11 x_optional_match OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
12 x_optional_count OUT NOCOPY NUMBER) IS --File.Sql.39 bug 4440895
13
14
15 BEGIN
16
17 --get mandatory and optional competence count for the requirement id
18 SELECT nvl(sum(decode(mandatory,'Y',1,0)),0), nvl(sum(decode(mandatory,'N',1,0)),0) INTO x_mandatory_count, x_optional_count
19 FROM per_competence_elements pce
20 WHERE pce.object_name = 'OPEN_ASSIGNMENT'
21 AND pce.object_id = p_requirement_id;
22
23 --if the requirement has any competencies then get the mandatory/optional competence match
24 --for the requirement / resource
25 IF (x_mandatory_count > 0 OR x_optional_count > 0) THEN
26
27 --do mandatory and optional match
28 SELECT nvl(sum(decode(pce.mandatory,'Y',1,0)),0), nvl(sum(decode(pce.mandatory,'N',1,0)),0) INTO x_mandatory_match, x_optional_match
29 FROM per_competence_elements pce,
30 per_competence_elements pce2,
31 per_rating_levels prl,
32 per_rating_levels prl2
33 WHERE pce.object_id = p_requirement_id
34 AND pce.object_name = 'OPEN_ASSIGNMENT'
35 AND pce.proficiency_level_id = prl.rating_level_id(+)
36 AND pce.competence_id = pce2.competence_id
37 AND pce2.person_id = p_person_id
38 AND pce2.proficiency_level_id = prl2.rating_level_id(+)
39 AND decode(prl2.step_value, NULL, decode(prl.step_value, NULL, -999, PA_SEARCH_GLOB.get_min_prof_level(pce2.competence_id)), prl2.step_value) >= nvl(prl.step_value, nvl(prl2.step_value , -999));
40
41 ELSE
42 x_mandatory_match :=0;
43 x_optional_match := 0;
44
45 END IF;
46
47 EXCEPTION WHEN OTHERS THEN
48 RAISE;
49
50 END;
51
52 FUNCTION Check_Availability ( p_resource_id IN NUMBER,
53 p_assignment_id IN NUMBER,
54 p_project_id IN NUMBER
55 ) RETURN NUMBER IS
56
57
58 --declare local variables.
59 l_availability NUMBER;
60 l_assignment_days NUMBER;
61 l_person_id NUMBER;
62
63 --The availability is calculated on a daily basis.
64 --The resource avail hours is divided by the assignment hours
65 --for each day, summed up and then divided by the total number of assignment days
66 --with non-zero hours. We need to divide by l_assignment_days because days
67 --for which a resource has 0 hours will not be in the forecast items table,
68 --but we need to take those days into account for the availability calculation
69 --as the assignment does have hours on that day - basically we need to average
70 --in 0 for those days - this is taken care of by dividing by l_assignment_days
71 --as opposed to dividing by the number of days which make the join below.
72
73 -- Bug 3182120: DIFF CAND SCORE OBTAINED FOR SAME EMP IN AUTO/MANUAL SEARCH
74 -- This API should return the Definite Availability.
75 CURSOR check_availability(p_assignment_days NUMBER) IS
76 SELECT res.person_id, TRUNC(SUM(DECODE(SIGN(
77 (nvl(res.capacity_quantity, 0) - nvl(res.confirmed_qty, 0))/
78 asgmt.item_quantity-1),1, 1,
79 greatest((nvl(res.capacity_quantity, 0) -
80 nvl(res.confirmed_qty, 0)), 0)/ asgmt.item_quantity))/
81 l_assignment_days * 100)
82 FROM PA_FORECAST_ITEMS res,
83 PA_FORECAST_ITEMS asgmt
84 WHERE res.resource_id = p_resource_id
85 AND res.forecast_item_type = 'U'
86 AND res.delete_flag = 'N'
87 AND res.item_date = asgmt.item_date
88 AND asgmt.assignment_id = p_assignment_id
89 AND asgmt.delete_flag = 'N'
90 AND asgmt.error_flag IN ('Y','N')
91 AND asgmt.item_date >= trunc(SYSDATE)
92 AND asgmt.item_quantity > 0
93 GROUP BY res.person_id;
94
95 BEGIN
96
97 --need to know the number of assignment days because the pa_forecast_items
98 --table does not store unassigned time resource records with 0 hours.
99 --This will be used in the following sql statement...
100 SELECT count(*) INTO l_assignment_days
101 FROM pa_forecast_items
102 WHERE assignment_id = p_assignment_id
103 AND delete_flag = 'N'
104 AND error_flag IN ('Y','N')
105 AND item_date >= trunc(SYSDATE)
106 AND item_quantity > 0;
107
108 IF l_assignment_days > 0 THEN
109
110 OPEN check_availability(l_assignment_days);
111
112 FETCH check_availability INTO l_person_id, l_availability;
113
114 IF check_availability%NOTFOUND THEN
115 CLOSE check_availability; -- 5347525
116 RETURN 0;
117 END IF;
118
119 CLOSE check_availability;
120
121 RETURN l_availability;
122
123 ELSE
124
125 RETURN 0;
126
127 END IF;
128
129 EXCEPTION
130
131 WHEN OTHERS THEN
132 -- Start : 5347525
133
134 IF check_availability%ISOPEN THEN
135 CLOSE check_availability;
136 END IF;
137
138 -- ENd : 5347525
139 RAISE;
140
141 END;
142
143
144 FUNCTION get_min_prof_level(l_competence_id IN NUMBER)
145 RETURN NUMBER IS
146
147 l_min_rating_level NUMBER;
148
149 BEGIN
150
151 --get the minimum proficiency level for a given competence.
152 SELECT MIN(step_value) into l_min_rating_level
153 FROM per_competence_levels_v
154 WHERE competence_id = l_competence_id;
155
156 RETURN l_min_rating_level;
157
158 EXCEPTION
159
160 WHEN OTHERS THEN
161 RAISE;
162
163 END;
164
165
166 END PA_SEARCH_GLOB;
[Home] [Help]