1 PACKAGE BODY PA_WORK_TYPE_UTILS AS
2 /* $Header: PARWUTLB.pls 120.2 2005/11/03 04:00:51 sunkalya noship $ */
3
4 -- ----------------------------------------------------------------------------
5 -- PROCEDURE
6 -- Check_Work_Type_Name_or_ID
7 -- PURPOSE
8 -- This procedure does the following
9 -- If work type name is passed converts it to the id
10 -- If id is passed, based on the check_id_flag validates it
11 -- HISTORY
12 -- 19-Jul-2000 nchouhan Created
13 -- ----------------------------------------------------------------------------
14 procedure Check_Work_Type_Name_or_ID
15 ( p_work_type_id IN pa_work_types_v.work_type_id%TYPE
16 ,p_name IN pa_work_types_v.name%TYPE
17 ,p_check_id_flag IN VARCHAR2
18 ,x_work_type_id OUT NOCOPY pa_work_types_v.work_type_id%TYPE
19 ,x_return_status OUT NOCOPY VARCHAR2
20 ,x_error_message_code OUT NOCOPY VARCHAR2)
21 IS
22 BEGIN
23 -- Set the error stack
24 pa_debug.set_err_stack('PA_WORK_TYPE_UTILS.Check_Work_Type_Name_or_ID');
25
26 IF p_work_type_id IS NOT NULL THEN
27 -- Validate ID based on the check id flag
28 IF p_check_id_flag = 'Y' THEN
29 SELECT work_type_id
30 INTO x_work_type_id
31 FROM pa_work_types_b --pa_work_types_v changed pa_work_types_v to pa_work_types_b for performance issues. Refer Bug:4668829
32 WHERE work_type_id = p_work_type_id
33 AND TRUNC(SYSDATE) BETWEEN start_date_active
34 AND NVL(end_date_active,TRUNC(SYSDATE));
35 ELSE
36 x_work_type_id := p_work_type_id;
37 END IF;
38 ELSE
39 -- Validate Name
40 SELECT work_type_id
41 INTO x_work_type_id
42 FROM pa_work_types_v
43 WHERE name = p_name
44 AND TRUNC(SYSDATE) BETWEEN start_date_active
45 AND NVL(end_date_active,TRUNC(SYSDATE));
46 END IF;
47
48 x_return_status := FND_API.G_RET_STS_SUCCESS;
49 pa_debug.reset_err_stack; -- Reset error stack
50
51 EXCEPTION
52 WHEN NO_DATA_FOUND THEN
53 x_return_status := FND_API.G_RET_STS_ERROR;
54 x_error_message_code := 'PA_WORK_TYPE_INVALID_AMBIGOUS';
55 x_work_type_id := Null;
56 pa_debug.reset_err_stack; -- Reset error stack
57
58 WHEN TOO_MANY_ROWS THEN
59 x_return_status := FND_API.G_RET_STS_ERROR;
60 x_error_message_code := 'PA_WORK_TYPE_INVALID_AMBIGOUS';
61 x_work_type_id := Null;
62 pa_debug.reset_err_stack; -- Reset error stack
63
64 WHEN OTHERS THEN
65 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
66 x_error_message_code := Null;
67 x_work_type_id := Null;
68 Raise;
69
70 END Check_Work_Type_Name_or_ID;
71
72 -- ----------------------------------------------------------------------------
73 -- PROCEDURE
74 -- Check_Work_Type
75 -- PURPOSE
76 -- This procedure does the following
77 -- It checks the work_type :
78 -- If Project is Indirect project then
79 -- only non-billable work-types can be assigned to it.
80 -- If Project is not Indirect project then
81 -- all work types are O.K.
82 --
83 -- HISTORY
84 -- 28-Nov-2000 nmishra Created
85 --
86 -- ----------------------------------------------------------------------------
87 --
88 procedure Check_Work_Type
89 ( p_work_type_id IN pa_work_types_v.work_type_id%TYPE
90 ,p_project_id IN pa_projects.project_id%TYPE
91 ,p_task_id IN pa_tasks.task_id%TYPE
92 ,x_return_status OUT NOCOPY VARCHAR2
93 ,x_error_message_code OUT NOCOPY VARCHAR2)
94 IS
95 BEGIN
96 DECLARE
97 l_proj_typ_class_code pa_project_types.project_type_class_code%TYPE;
98 l_project_id pa_projects.project_id%TYPE;
99 l_project_type pa_project_types.project_type%TYPE;
100 l_flag varchar2(1);
101 CURSOR Get_Project_type_class_cur IS
102 SELECT p.project_type_class_code
103 FROM pa_project_types p
104 WHERE p.project_type = l_project_type;
105 CURSOR Get_Project_type IS
106 SELECT a.project_type
107 FROM pa_projects a
108 WHERE a.project_id = l_project_id;
109 CURSOR Get_Project_id IS
110 SELECT t.project_id
111 FROM pa_tasks t
112 WHERE t.task_id = p_task_id;
113 CURSOR work_type_cur IS
114 SELECT w.BILLABLE_CAPITALIZABLE_FLAG
115 FROM pa_work_types_b w -- changed pa_work_types_v to pa_work_types_b for performance issue. Refer Bug: 4668829
116 WHERE w.work_type_id = p_work_type_id;
117
118 l_proj_type_rec Get_Project_type_class_cur%ROWTYPE;
119 l_proj_rec Get_Project_type%ROWTYPE;
120 l_proj_task_rec Get_Project_id%ROWTYPE;
121 l_work_type_rec work_type_cur%ROWTYPE;
122 BEGIN
123 -- Set the error stack
124 -- pa_debug.set_err_stack('PA_WORK_TYPE_UTILS.Check_Work_Type_Name_or_ID');
125 IF p_work_type_id IS NOT NULL THEN
126 IF (p_project_id IS NOT NULL AND p_task_id IS NULL)
127 OR (p_project_id IS NOT NULL AND p_task_id IS NOT NULL) THEN
128 l_project_id := p_project_id;
129 OPEN Get_Project_type;
130 LOOP
131 FETCH Get_Project_type INTO l_proj_rec ;
132 IF Get_Project_type%NOTFOUND THEN
133 EXIT;
134 ELSE
135 l_project_type := l_proj_rec.project_type;
136 OPEN Get_Project_type_class_cur;
137 LOOP
138 FETCH Get_Project_type_class_cur INTO l_proj_type_rec;
139 IF Get_Project_type_class_cur%NOTFOUND THEN
140 EXIT;
141 ELSE
142 l_proj_typ_class_code:= l_proj_type_rec.project_type_class_code;
143 END IF;
144 END LOOP;
145 CLOSE Get_Project_type_class_cur;
146 END IF;
147 END LOOP;
148 CLOSE Get_Project_type;
149 ELSIF (p_project_id IS NULL AND p_task_id IS NOT NULL) THEN
150 OPEN Get_Project_id;
151 LOOP
152 FETCH Get_Project_id INTO l_proj_task_rec;
153 IF Get_Project_id%NOTFOUND THEN
154 EXIT;
155 ELSE
156 l_project_id := l_proj_task_rec.project_id;
157 OPEN Get_Project_type;
158 LOOP
159 FETCH Get_Project_type INTO l_proj_rec;
160 IF Get_Project_type%NOTFOUND THEN
161 EXIT;
162 ELSE
163 l_project_type:= l_proj_rec.project_type;
164 OPEN Get_Project_type_class_cur;
165 LOOP
166 FETCH Get_Project_type_class_cur INTO l_proj_type_rec;
167 IF Get_Project_type_class_cur%NOTFOUND THEN
168 EXIT;
169 ELSE
170 l_proj_typ_class_code:= l_proj_type_rec.project_type_class_code;
171 END IF;
172 END LOOP;
173 CLOSE Get_Project_type_class_cur;
174 END IF;
175 END LOOP;
176 CLOSE Get_Project_type;
177 END IF;
178 END LOOP;
179 CLOSE Get_Project_id;
180 END IF;
181 OPEN work_type_cur;
182 LOOP
183 FETCH work_type_cur INTO l_work_type_rec;
184 IF work_type_cur%NOTFOUND THEN
185 EXIT;
186 ELSE
187 l_flag := l_work_type_rec.BILLABLE_CAPITALIZABLE_FLAG;
188 END IF;
189 END LOOP;
190 CLOSE work_type_cur;
191
192 IF l_proj_typ_class_code = 'INDIRECT'
193 THEN
194 IF l_flag = 'Y' THEN
195 x_return_status := FND_API.G_RET_STS_ERROR;
196 x_error_message_code := 'PA_WORK_TYPE_INVALID';
197 END IF;
198 END IF;
199 ELSE
200 x_return_status := FND_API.G_RET_STS_ERROR;
201 x_error_message_code := 'PA_WORK_TYPE_INVALID';
202 END IF;
203
204 x_return_status := FND_API.G_RET_STS_SUCCESS;
205 pa_debug.reset_err_stack; -- Reset error stack
206
207
208 EXCEPTION
209 WHEN NO_DATA_FOUND THEN
210 x_return_status := FND_API.G_RET_STS_ERROR;
211 x_error_message_code := 'PA_WORK_TYPE_INVALID';
212 pa_debug.reset_err_stack; -- Reset error stack
213
214 WHEN TOO_MANY_ROWS THEN
215 x_return_status := FND_API.G_RET_STS_ERROR;
216 x_error_message_code := 'PA_WORK_TYPE_INVALID';
217 pa_debug.reset_err_stack; -- Reset error stack
218
219 WHEN OTHERS THEN
220 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
221 x_error_message_code := Null;
222 Raise;
223 END;
224 END Check_Work_Type;
225 END PA_WORK_TYPE_UTILS ;
[Home] [Help]