[Home] [Help]
PACKAGE BODY: APPS.PA_PROJECT_ROLES_PVT
Source
1 package body PA_PROJECT_ROLES_PVT AS
2 /* $Header: PARPRPVB.pls 120.1 2005/08/19 16:59:35 mwasowic noship $ */
3
4 procedure INSERT_ROW (
5 p_commit IN VARCHAR2:=FND_API.G_FALSE,
6 p_debug_mode in varchar2 default 'N',
7 X_ROWID IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
8 X_PROJECT_ROLE_ID IN NUMBER,
9 X_PROJECT_ROLE_TYPE IN VARCHAR2,
10 X_MEANING IN VARCHAR2,
11 X_QUERY_LABOR_COST_FLAG IN VARCHAR2,
12 X_START_DATE_ACTIVE IN DATE,
13 X_LAST_UPDATE_DATE IN DATE,
14 X_LAST_UPDATED_BY IN NUMBER,
15 X_CREATION_DATE IN DATE,
16 X_CREATED_BY IN NUMBER,
17 X_LAST_UPDATE_LOGIN IN NUMBER,
18 X_END_DATE_ACTIVE IN DATE,
19 X_DESCRIPTION IN VARCHAR2,
20 X_DEFAULT_MIN_JOB_LEVEL IN NUMBER,
21 X_DEFAULT_MAX_JOB_LEVEL IN NUMBER,
22 X_MENU_ID IN NUMBER,
23 X_DEFAULT_JOB_ID IN NUMBER,
24 X_FREEZE_RULES_FLAG IN VARCHAR2,
25 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
26 X_ATTRIBUTE1 IN VARCHAR2,
27 X_ATTRIBUTE2 IN VARCHAR2,
28 X_ATTRIBUTE3 IN VARCHAR2,
29 X_ATTRIBUTE4 IN VARCHAR2,
30 X_ATTRIBUTE5 IN VARCHAR2,
31 X_ATTRIBUTE6 IN VARCHAR2,
32 X_ATTRIBUTE7 IN VARCHAR2,
33 X_ATTRIBUTE8 IN VARCHAR2,
34 X_ATTRIBUTE9 IN VARCHAR2,
35 X_ATTRIBUTE10 IN VARCHAR2,
36 X_ATTRIBUTE11 IN VARCHAR2,
37 X_ATTRIBUTE12 IN VARCHAR2,
38 X_ATTRIBUTE13 IN VARCHAR2,
39 X_ATTRIBUTE14 IN VARCHAR2,
40 X_ATTRIBUTE15 IN VARCHAR2,
41 X_DEFAULT_ACCESS_LEVEL IN VARCHAR2,
42 X_ROLE_PARTY_CLASS IN VARCHAR2 DEFAULT 'PERSON',
43 X_STATUS_LEVEL IN VARCHAR2 DEFAULT NULL,
44 x_return_status OUT NOCOPY varchar2, --File.Sql.39 bug 4440895
45 x_msg_count out NOCOPY number, --File.Sql.39 bug 4440895
46 x_msg_data out NOCOPY varchar2 --File.Sql.39 bug 4440895
47 ) IS
48 v_sqlcode varchar2(30);
49 v_error_message_code varchar2(30);
50 begin
51 FND_MSG_PUB.initialize;
52 x_msg_count:=0;
53 ----Check if the role name is duplicate
54 pa_role_utils.check_dup_role_name(x_meaning,
55 x_return_status,
56 v_error_message_code);
57 if x_return_status =FND_API.G_RET_STS_SUCCESS then
58
59 --- call table handler to insert into the table
60 pa_project_role_types_pkg.insert_row(
61 X_ROWID ,
62 X_PROJECT_ROLE_ID ,
63 X_PROJECT_ROLE_TYPE ,
64 X_MEANING ,
65 X_QUERY_LABOR_COST_FLAG ,
66 NVL(X_START_DATE_ACTIVE, TRUNC(SYSDATE)) ,
67 X_LAST_UPDATE_DATE ,
68 X_LAST_UPDATED_BY ,
69 X_CREATION_DATE ,
70 X_CREATED_BY ,
71 X_LAST_UPDATE_LOGIN ,
72 X_END_DATE_ACTIVE ,
73 X_DESCRIPTION ,
74 X_DEFAULT_MIN_JOB_LEVEL ,
75 X_DEFAULT_MAX_JOB_LEVEL ,
76 X_MENU_ID ,
77 X_DEFAULT_JOB_ID ,
78 X_FREEZE_RULES_FLAG ,
79 X_ATTRIBUTE_CATEGORY ,
80 X_ATTRIBUTE1 ,
81 X_ATTRIBUTE2 ,
82 X_ATTRIBUTE3 ,
83 X_ATTRIBUTE4 ,
84 X_ATTRIBUTE5 ,
85 X_ATTRIBUTE6 ,
86 X_ATTRIBUTE7 ,
87 X_ATTRIBUTE8 ,
88 X_ATTRIBUTE9 ,
89 X_ATTRIBUTE10 ,
90 X_ATTRIBUTE11 ,
91 X_ATTRIBUTE12 ,
92 X_ATTRIBUTE13 ,
93 X_ATTRIBUTE14 ,
94 X_ATTRIBUTE15 ,
95 X_DEFAULT_ACCESS_LEVEL ,
96 X_ROLE_PARTY_CLASS ,
97 X_STATUS_LEVEL
98 );
99 elsif x_return_status =FND_API.G_RET_STS_ERROR then
100 fnd_message.set_name('PA',v_error_message_code);
101 fnd_msg_pub.ADD;
102 x_msg_count:=x_msg_count+1;
103
104 elsif x_return_status =FND_API.G_RET_STS_UNEXP_ERROR then
105 fnd_msg_pub.add_exc_msg
106 (p_pkg_name => 'PA_ROLE_UTILS',
107 p_procedure_name => 'check_dup_role_name',
108 P_ERROR_TEXT =>v_error_message_code);
109 x_msg_count:=x_msg_count+1;
110 end if;
111
112 exception
113 when others then
114 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
115 v_sqlcode:=SQLCODE;
116 fnd_msg_pub.add_exc_msg
117 (p_pkg_name => 'PA_PROJECT_ROLES_PVT',
118 p_procedure_name => 'INSERT_ROW',
119 P_ERROR_TEXT =>v_sqlcode);
120 x_msg_count:=x_msg_count+1;
121
122 end;
123
124 procedure LOCK_ROW (
125 p_commit IN VARCHAR2:=FND_API.G_FALSE,
126 p_debug_mode in varchar2 default 'N',
127 X_ROWID IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
128 X_RECORD_VERSION_NUMBER IN NUMBER,
129 x_return_status OUT NOCOPY varchar2, --File.Sql.39 bug 4440895
130 x_msg_count out NOCOPY number, --File.Sql.39 bug 4440895
131 x_msg_data out NOCOPY varchar2 --File.Sql.39 bug 4440895
132 ) IS
133 v_sqlcode varchar2(30);
134 begin
135 FND_MSG_PUB.initialize;
136 x_msg_count:=0;
137 -----any validation to be added here ?
138 -----call table handler to lock the row
139 pa_project_role_types_pkg.lock_row (
140 X_ROWID ,
141 X_RECORD_VERSION_NUMBER );
142 x_return_status:=FND_API.G_RET_STS_SUCCESS;
143 exception
144 when others then
145 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
146 v_sqlcode:=SQLCODE;
147 fnd_msg_pub.add_exc_msg
148 (p_pkg_name => 'PA_PROJECT_ROLES_PVT',
149 p_procedure_name => 'LOCK_ROW',
150 P_ERROR_TEXT =>v_sqlcode);
151 x_msg_count:=x_msg_count+1;
152 end;
153
154 procedure UPDATE_ROW (
155 p_commit IN VARCHAR2:=FND_API.G_FALSE,
156 p_debug_mode in varchar2 default 'N',
157 X_ROWID IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
158 X_PROJECT_ROLE_ID IN NUMBER,
159 X_PROJECT_ROLE_TYPE IN VARCHAR2,
160 X_MEANING IN VARCHAR2,
161 X_QUERY_LABOR_COST_FLAG IN VARCHAR2,
162 X_START_DATE_ACTIVE IN DATE,
163 X_LAST_UPDATE_DATE IN DATE,
164 X_LAST_UPDATED_BY IN NUMBER,
165 X_CREATION_DATE IN DATE,
166 X_CREATED_BY IN NUMBER,
167 X_LAST_UPDATE_LOGIN IN NUMBER,
168 X_END_DATE_ACTIVE IN DATE,
169 X_DESCRIPTION IN VARCHAR2,
170 X_DEFAULT_MIN_JOB_LEVEL IN NUMBER,
171 X_DEFAULT_MAX_JOB_LEVEL IN NUMBER,
172 X_MENU_ID IN NUMBER,
173 X_DEFAULT_JOB_ID IN NUMBER,
174 X_FREEZE_RULES_FLAG IN VARCHAR2,
175 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
176 X_ATTRIBUTE1 IN VARCHAR2,
177 X_ATTRIBUTE2 IN VARCHAR2,
178 X_ATTRIBUTE3 IN VARCHAR2,
179 X_ATTRIBUTE4 IN VARCHAR2,
180 X_ATTRIBUTE5 IN VARCHAR2,
181 X_ATTRIBUTE6 IN VARCHAR2,
182 X_ATTRIBUTE7 IN VARCHAR2,
183 X_ATTRIBUTE8 IN VARCHAR2,
184 X_ATTRIBUTE9 IN VARCHAR2,
185 X_ATTRIBUTE10 IN VARCHAR2,
186 X_ATTRIBUTE11 IN VARCHAR2,
187 X_ATTRIBUTE12 IN VARCHAR2,
188 X_ATTRIBUTE13 IN VARCHAR2,
189 X_ATTRIBUTE14 IN VARCHAR2,
190 X_ATTRIBUTE15 IN VARCHAR2,
191 X_DEFAULT_ACCESS_LEVEL IN VARCHAR2,
192 X_ROLE_PARTY_CLASS IN VARCHAR2 DEFAULT 'PERSON',
193 X_STATUS_LEVEL IN VARCHAR2 DEFAULT NULL,
194 x_return_status OUT NOCOPY varchar2, --File.Sql.39 bug 4440895
195 x_msg_count out NOCOPY number, --File.Sql.39 bug 4440895
196 x_msg_data out NOCOPY varchar2 --File.Sql.39 bug 4440895
197 ) is
198 v_menu_id number;
199 v_meaning varchar2(80);
200 v_created_by NUMBER;
201 v_error_message_code varchar2(30);
202 v_sqlcode varchar2(30);
203
204 begin
205 -- hr_utility.trace_on(NULL, 'RMFORM');
206 -- hr_utility.trace('start');
207 FND_MSG_PUB.initialize;
208 x_msg_count:=0;
209 ---any validation to be added here
210 select menu_id, meaning, created_by
211 into v_menu_id, v_meaning, v_created_by
212 from pa_project_role_types_vl
213 where row_id=x_rowid;
214 --dbms_output.put_line('v_menu_id: '||v_menu_id);
215
216 x_return_status:=FND_API.G_RET_STS_SUCCESS;
217
218 --Throwing an error if seeded role
219 /* Commented for bug 2661505
220 IF v_created_by = 1 THEN
221 x_return_status:=FND_API.G_RET_STS_ERROR;
222 fnd_message.set_name('PA', 'PA_COMMON_SEEDED_ROLES');
223 fnd_msg_pub.ADD;
224 x_msg_count:=x_msg_count+1;
225 RETURN;
226 END IF;
227 */
228
229 if v_meaning <>x_meaning then
230 pa_role_utils.check_dup_role_name(x_meaning,
231 x_return_status,
232 v_error_message_code);
233 end if;
234 --dbms_output.put_line('check1');
235 If x_return_status =FND_API.G_RET_STS_SUCCESS then
236 --dbms_output.put_line('check2');
237 -- hr_utility.trace('update');
238 pa_project_role_types_pkg.update_row(
239 X_ROWID ,
240 X_PROJECT_ROLE_ID ,
241 X_PROJECT_ROLE_TYPE ,
242 X_MEANING ,
243 X_QUERY_LABOR_COST_FLAG ,
244 X_START_DATE_ACTIVE ,
245 X_LAST_UPDATE_DATE ,
246 X_LAST_UPDATED_BY ,
247 X_CREATION_DATE ,
248 X_CREATED_BY ,
249 X_LAST_UPDATE_LOGIN ,
250 X_END_DATE_ACTIVE ,
251 X_DESCRIPTION ,
252 X_DEFAULT_MIN_JOB_LEVEL ,
253 X_DEFAULT_MAX_JOB_LEVEL ,
254 X_MENU_ID ,
255 X_DEFAULT_JOB_ID ,
256 X_FREEZE_RULES_FLAG ,
257 X_ATTRIBUTE_CATEGORY ,
258 X_ATTRIBUTE1 ,
259 X_ATTRIBUTE2 ,
260 X_ATTRIBUTE3 ,
261 X_ATTRIBUTE4 ,
262 X_ATTRIBUTE5 ,
263 X_ATTRIBUTE6 ,
264 X_ATTRIBUTE7 ,
265 X_ATTRIBUTE8 ,
266 X_ATTRIBUTE9 ,
267 X_ATTRIBUTE10 ,
268 X_ATTRIBUTE11 ,
269 X_ATTRIBUTE12 ,
270 X_ATTRIBUTE13 ,
271 X_ATTRIBUTE14 ,
272 X_ATTRIBUTE15 ,
273 X_DEFAULT_ACCESS_LEVEL ,
274 X_ROLE_PARTY_CLASS ,
275 X_STATUS_LEVEL
276 ) ;
277 -- hr_utility.trace('end update');
278 --dbms_output.put_line('check3');
279 ---If the menu_id changes,then update menu_id in fnd_grants
280 if (v_menu_id is not null and x_menu_id is not null and v_menu_id<> x_menu_id ) then
281 -- hr_utility.trace('menu update');
282 pa_role_utils.update_menu_in_grants(x_project_role_id,
283 x_menu_id,
284 x_return_status,
285 v_error_message_code);
286 if x_return_status=FND_API.G_RET_STS_ERROR then
287 fnd_message.set_name('PA',v_error_message_code);
288 fnd_msg_pub.ADD;
289 x_msg_count:=x_msg_count+1;
290 return;
291 elsif x_return_status =FND_API.G_RET_STS_UNEXP_ERROR then
292 fnd_msg_pub.add_exc_msg
293 (p_pkg_name => 'PA_ROLE_UTILS',
294 p_procedure_name => 'update_menu_in_grants',
295 P_ERROR_TEXT =>v_error_message_code);
296 x_msg_count:=x_msg_count+1;
297 return;
298 end if;
299 ----if role based security is disabled, remove records from fnd_grants
300 elsif v_menu_id is not null and x_menu_id is null then
301 pa_role_utils.disable_role_based_sec(x_project_role_id,
302 x_return_status,
303 v_error_message_code) ;
304 if x_return_status=FND_API.G_RET_STS_ERROR then
305 fnd_message.set_name('PA',v_error_message_code);
306 fnd_msg_pub.ADD;
307 x_msg_count:=x_msg_count+1;
308 return;
309 elsif x_return_status =FND_API.G_RET_STS_UNEXP_ERROR then
310 fnd_msg_pub.add_exc_msg
311 (p_pkg_name => 'PA_ROLE_UTILS',
312 p_procedure_name => 'disable_role_based_sec',
313 P_ERROR_TEXT =>v_error_message_code);
314 x_msg_count:=x_msg_count+1;
315 return;
316 end if;
317 -----if role based security is enabled, insert records into fnd_grants
318 elsif v_menu_id is null and x_menu_id is not null then
319 -- hr_utility.trace('enable menu update');
320 pa_role_utils.Enable_role_based_sec(x_project_role_id,
321 x_return_status,
322 v_error_message_code) ;
323 -- hr_utility.trace('x_return_status is ' || x_return_status);
324 -- hr_utility.trace('v_error_message_code is ' || v_error_message_code);
325 -- hr_utility.trace('after enable menu update');
326 if x_return_status=FND_API.G_RET_STS_ERROR then
327 fnd_message.set_name('PA',v_error_message_code);
328 fnd_msg_pub.ADD;
329 x_msg_count:=x_msg_count+1;
330 -- hr_utility.trace('The end');
331 return;
332 elsif x_return_status =FND_API.G_RET_STS_UNEXP_ERROR then
333 fnd_msg_pub.add_exc_msg
334 (p_pkg_name => 'PA_ROLE_UTILS',
335 p_procedure_name => 'Enable_role_based_sec',
336 P_ERROR_TEXT =>v_error_message_code);
337 x_msg_count:=x_msg_count+1;
338 return;
339 end if;
340 end if;
341
342 elsif x_return_status =FND_API.G_RET_STS_ERROR then
343 fnd_message.set_name('PA',v_error_message_code);
344 fnd_msg_pub.ADD;
345 x_msg_count:=x_msg_count+1;
346 elsif x_return_status =FND_API.G_RET_STS_UNEXP_ERROR then
347 fnd_msg_pub.add_exc_msg
348 (p_pkg_name => 'PA_ROLE_UTILS',
349 p_procedure_name => 'check_dup_role_name',
350 P_ERROR_TEXT =>v_error_message_code);
351 x_msg_count:=x_msg_count+1;
352 end if;
353
354 exception
355 when others then
356 --dbms_output.put_line('check4');
357 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
358 v_sqlcode:=SQLCODE;
359 fnd_msg_pub.add_exc_msg
360 (p_pkg_name => 'PA_PROJECT_ROLES_PVT',
361 p_procedure_name => 'UPDATE_ROW',
362 P_ERROR_TEXT =>v_sqlcode);
363 x_msg_count:=x_msg_count+1;
364 end;
365
366
367 procedure DELETE_ROW (
368 p_commit IN VARCHAR2:=FND_API.G_FALSE,
369 p_debug_mode in varchar2 default 'N',
370 X_ROWID in varchar2,
371 x_return_status OUT NOCOPY varchar2, --File.Sql.39 bug 4440895
372 x_msg_count out NOCOPY number, --File.Sql.39 bug 4440895
373 x_msg_data out NOCOPY varchar2 --File.Sql.39 bug 4440895
374 ) is
375 v_role_id number;
376 v_created_by NUMBER;
377 v_error_message_code varchar2(30);
378 v_sqlcode varchar2(30);
379 begin
380 FND_MSG_PUB.initialize;
381 x_msg_count:=0;
382 --validate if the role can be deleted or not
383 select project_role_id, created_by
384 into v_role_id, v_created_by
385 from pa_project_role_types_vl
386 where row_id=X_ROWID;
387
388 --Throwing an error if seeded role
389 IF v_created_by = 1 THEN
390 x_return_status:=FND_API.G_RET_STS_ERROR;
391 fnd_message.set_name('PA', 'PA_COMMON_SEEDED_ROLES');
392 fnd_msg_pub.ADD;
393 x_msg_count:=x_msg_count+1;
394 RETURN;
395 END IF;
396
397 --dbms_output.put_line('v_role_id: '||v_role_id);
398 pa_role_utils.check_delete_role_ok(v_role_id,
399 x_return_status,
400 v_error_message_code);
401 --dbms_output.put_line('x_return_status: '||x_return_status);
402
403 if x_return_status =FND_API.G_RET_STS_SUCCESS then
404 ----call the table handler to delete roles
405 pa_project_role_types_pkg.delete_row(X_ROWID);
406 --dbms_output.put_line('called delete_row');
407
408 delete from pa_role_controls
409 where project_role_id=v_role_id;
410 --dbms_output.put_line('delete controls');
411
412 delete from pa_role_list_members
413 where project_role_id=v_role_id;
414 --dbms_output.put_line('delete list mem');
415
416 OKE_K_ACCESS_RULES_PKG.delete_all(v_role_id);
417 --dbms_output.put_line('called delete_all');
418
419 delete from per_competence_elements
420 where OBJECT_ID=v_role_id
421 and OBJECT_NAME='PROJECT_ROLE';
422 --dbms_output.put_line('delete competence ele');
423
424 elsif x_return_status =FND_API.G_RET_STS_ERROR then
425 fnd_message.set_name('PA',v_error_message_code);
426 fnd_msg_pub.ADD;
427 x_msg_count:=x_msg_count+1;
428 elsif x_return_status =FND_API.G_RET_STS_UNEXP_ERROR then
429 fnd_msg_pub.add_exc_msg
430 (p_pkg_name => 'PA_ROLE_UTILS',
431 p_procedure_name => 'check_delete_role_ok',
432 P_ERROR_TEXT =>v_error_message_code);
433 x_msg_count:=x_msg_count+1;
434 end if;
435 exception
436 when others then
437 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
438 v_sqlcode:=SQLCODE;
439 fnd_msg_pub.add_exc_msg
440 (p_pkg_name => 'PA_PROJECT_ROLES_PVT',
441 p_procedure_name => 'DELETE_ROW',
442 P_ERROR_TEXT =>v_sqlcode);
443 x_msg_count:=x_msg_count+1;
444
445 end;
446 end PA_PROJECT_ROLES_PVT;