[Home] [Help]
PACKAGE BODY: APPS.EAM_DEPT_APPROVERS_PUB
Source
1 PACKAGE BODY EAM_DEPT_APPROVERS_PUB AS
2 /* $Header: EAMPDAPB.pls 120.1 2005/11/25 10:18:21 sshahid noship $ */
3 -- Start of comments
4 -- API name : EAM_DEPT_APPROVERS_PUB
5 -- Type : Public
6 -- Function : insert_dept_appr, update_dept_appr
7 -- Pre-reqs : None.
8 -- Parameters :
9 -- IN : p_api_version IN NUMBER Required
10 -- p_init_msg_list IN VARCHAR2 Optional
11 -- Default = FND_API.G_FALSE
12 -- p_commit IN VARCHAR2 Optional
13 -- Default = FND_API.G_FALSE
14 -- p_validation_level IN NUMBER Optional
15 -- Default = FND_API.G_VALID_LEVEL_FULL
16 -- parameter1
17 -- parameter2
18 -- .
19 -- .
20 -- OUT : x_return_status OUT VARCHAR2(1)
21 -- x_msg_count OUT NUMBER
22 -- x_msg_data OUT VARCHAR2(2000)
23 -- parameter1
24 -- parameter2
25 -- .
26 -- .
27 -- Version : Current version x.x
28 -- Changed....
29 -- previous version y.y
30 -- Changed....
31 -- .
32 -- .
33 -- previous version 2.0
34 -- Changed....
35 -- Initial version 1.0
36 --
37 -- Notes : Note text
38 --
39 -- End of comments
40
41 G_PKG_NAME CONSTANT VARCHAR2(30):='EAM_DEPT_APPROVERS_PUB';
42
43 /*
44 functions for validation
45 */
46 PROCEDURE validate_application_id( P_APPLICATION_ID IN NUMBER)
47 is
48 l_count number;
49 BEGIN
50
51 SELECT count(*) INTO l_count
52 FROM FND_APPLICATION
53 WHERE APPLICATION_ID = p_application_id;
54
55 if l_count = 0
56 then
57 fnd_message.set_name('EAM', 'EAM_INVALID_APPLICATION_ID');
58 fnd_msg_pub.add;
59 RAISE fnd_api.g_exc_error;
60 end if;
61
62 END;
63
64
65 PROCEDURE validate_responsibility_id (p_resp_id in number , p_resp_app_id in number )
66 is
67 l_count number;
68 BEGIN
69 IF p_resp_app_id IS NULL
70 then
71 fnd_message.set_name('EAM', 'EAM_DA_INVALID_RESP');
72 fnd_msg_pub.add;
73 RAISE fnd_api.g_exc_error;
74 end if;
75 SELECT COUNT(*) INTO l_count
76 FROM fnd_responsibility
77 WHERE responsibility_id = p_resp_id
78 And application_id = p_resp_app_id;
79
80 if l_count = 0
81 then
82 fnd_message.set_name('EAM', 'EAM_DA_INVALID_RESP');
83 fnd_msg_pub.add;
84 RAISE fnd_api.g_exc_error;
85 end if;
86
87 END ;
88
89 procedure validate_primary_approver_id (p_primary_approver_id IN NUMBER, p_responsibility_id IN NUMBER)
90 is
91 l_count number;
92 begin
93 IF P_PRIMARY_APPROVER_ID IS NULL -- primary_approver_id IS NOT MANDATORY FIELD
94 THEN
95 RETURN;
96 END IF;
97
98 IF p_responsibility_id IS NULL
99 THEN
100 fnd_message.set_name('EAM', 'EAM_DA_INVALID_RESP');
101 fnd_msg_pub.add;
102 RAISE fnd_api.g_exc_error;
103 end if;
104
105 -- primary_approver_id HAS TO BE VALID USER WITH VALID RESPONSIBILITY ID
106 select count(*) into l_count
107 FROM FND_USER_RESP_GROUPS GRP WHERE USER_ID = P_PRIMARY_APPROVER_ID
108 AND RESPONSIBILITY_ID = P_RESPONSIBILITY_ID;
109
110 if l_count = 0
111 then
112 fnd_message.set_name('EAM', 'EAM_DEPT_INV_PRIMARY_APPROVER');
113 fnd_msg_pub.add;
114 RAISE fnd_api.g_exc_error;
115 end if;
116
117 END validate_primary_approver_id;
118
119 function validate_department (p_dept_id NUMBER, p_org_id NUMBER)
120 return boolean
121 is
122 l_count_rec NUMBER := 0;
123 begin
124 select count (*) into l_count_rec
125 from bom_departments
126 where department_id = p_dept_id
127 and organization_id = p_org_id;
128
129 if l_count_rec > 0 then --actually this should be 1
130 return true;
131 end if;
132 return false;
133 end validate_department;
134
135 function validate_dept_assign (p_dept_id NUMBER, p_responsibility_id NUMBER)
136 return boolean
137 is
138 l_count_rec NUMBER := 0;
139 begin
140 --A DEPARTMENT CAN HAVE ONLY ONE PRIMARY APPROVER
141 select count (*) into l_count_rec
142 from BOM_EAM_DEPT_APPROVERS
143 where dept_id = p_dept_id
144 and responsibility_id = p_responsibility_id;
145
146 if l_count_rec > 0 then --actually this should be 1
147 return true;
148 end if;
149 return false;
150 end validate_dept_assign;
151
152 procedure VALIDATE_ROW_EXISTS(P_DEPT_ID IN NUMBER,
153 P_ORGANIZATION_ID IN NUMBER,
154 P_RESP_APP_ID IN NUMBER,
155 P_RESPONSIBILITY_ID IN NUMBER,
156 p_create_flag in boolean)
157 is
158 l_count number;
159 BEGIN
160 -- Bug # 3518888
161 IF NOT p_create_flag
162 then
163 SELECT COUNT(*) INTO l_count
164 FROM BOM_EAM_DEPT_APPROVERS
165 WHERE DEPT_ID = P_DEPT_ID and
166 ORGANIZATION_ID = P_ORGANIZATION_ID and
167 RESPONSIBILITY_APPLICATION_ID = P_RESP_APP_ID and
168 RESPONSIBILITY_ID = P_RESPONSIBILITY_ID;
169 if l_count = 0 then
170 fnd_message.set_name('EAM', 'EAM_DEPT_REC_NOT_FOUND');
171 fnd_msg_pub.add;
172 RAISE fnd_api.g_exc_error;
173 end if;
174
175 ELSIF p_create_flag THEN
176 SELECT COUNT(*) INTO l_count
177 FROM BOM_EAM_DEPT_APPROVERS
178 WHERE DEPT_ID = P_DEPT_ID and
179 ORGANIZATION_ID = P_ORGANIZATION_ID;
180 IF l_count > 0 THEN
181 fnd_message.set_name('EAM', 'EAM_DEPT_REC_EXISTS');
182 fnd_msg_pub.add;
183 RAISE fnd_api.g_exc_error;
184 END IF;
185 END IF;
186 END;
187
188 PROCEDURE insert_dept_appr
189 ( p_api_version IN NUMBER ,
190 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
191 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
192 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
193
194 x_return_status OUT NOCOPY VARCHAR2 ,
195 x_msg_count OUT NOCOPY NUMBER ,
196 x_msg_data OUT NOCOPY VARCHAR2 ,
197
198 p_dept_id IN NUMBER,
199 p_organization_id IN NUMBER,
200 p_resp_app_id IN NUMBER,
201 p_responsibility_id IN NUMBER,
202 p_primary_approver_id IN NUMBER
203 )
204 IS
205 l_api_name CONSTANT VARCHAR2(30) := 'insert_dept_appr';
206 l_api_version CONSTANT NUMBER := 1.0;
207 l_boolean number;
208 l_return_status VARCHAR2(1);
209 l_msg_count NUMBER;
210 l_msg_data VARCHAR2(30);
211 l_bool boolean;
212 BEGIN
213 -- Standard Start of API savepoint
214 SAVEPOINT INSERT_DEPT_APPR;
215 -- Standard call to check for call compatibility.
216 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
217 p_api_version ,
218 l_api_name ,
219 G_PKG_NAME )
220 THEN
221 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
222 END IF;
223 -- Initialize message list if p_init_msg_list is set to TRUE.
224 IF FND_API.to_Boolean( p_init_msg_list ) THEN
225 FND_MSG_PUB.initialize;
226 END IF;
227 -- Initialize API return status to success
228 x_return_status := FND_API.G_RET_STS_SUCCESS;
229 -- API body
230
231 EAM_COMMON_UTILITIES_PVT.verify_org(
232 p_resp_id => NULL,
233 p_resp_app_id => 401,
234 p_org_id => P_ORGANIZATION_ID,
235 x_boolean => l_boolean,
236 x_return_status => l_return_status,
237 x_msg_count => l_msg_count ,
238 x_msg_data => l_msg_data);
239 if l_boolean = 0
240 then
241 fnd_message.set_name('EAM', 'EAM_ABO_INVALID_ORG_ID');
242 fnd_msg_pub.add;
243 RAISE fnd_api.g_exc_error;
244 end if;
245
246 /*
247 if not EAM_COMMON_UTILITIES_PVT.validate_department_id(p_dept_id, p_organization_id)
248 then
249 fnd_message.set_name('EAM', 'EAM_DA_INVALID_DEPT');
250 fnd_msg_pub.add;
251 RAISE fnd_api.g_exc_error;
252 end if; */
253
254 validate_application_id(p_resp_app_id);
255
256 validate_responsibility_id( p_responsibility_id, p_resp_app_id);
257
258 -- one department per organization
259 l_bool := validate_department (p_dept_id , p_organization_id);
260 if not l_bool then
261 fnd_message.set_name('EAM', 'EAM_DA_INVALID_DEPT');
262 fnd_msg_pub.add;
263 RAISE fnd_api.g_exc_error;
264 end if;
265
266 -- Bug # 3518888 : Commmenting as it is not required.
267 --a dept can be assigned to one responsibility only
268 /*l_bool := validate_dept_assign (p_dept_id , p_responsibility_id);
269 if l_bool then
270 fnd_message.set_name('EAM', 'EAM_INVALID_DEPT_RESP');
271 fnd_msg_pub.add;
272 RAISE fnd_api.g_exc_error;
273 end if;*/
274
275 validate_primary_approver_id (p_primary_approver_id, p_responsibility_id);
276
277 VALIDATE_ROW_EXISTS(P_DEPT_ID ,
278 P_ORGANIZATION_ID ,
279 p_resp_app_id ,
280 P_RESPONSIBILITY_ID, true );
281
282
283 l_msg_count := FND_MSG_PUB.count_msg;
284 IF l_msg_count > 0 THEN
285 X_msg_count := l_msg_count;
286 RAISE FND_API.G_EXC_ERROR;
287 END IF;
288
289
290 INSERT INTO BOM_EAM_DEPT_APPROVERS
291 (
292 DEPT_ID ,
293 ORGANIZATION_ID ,
294 RESPONSIBILITY_APPLICATION_ID ,
295 RESPONSIBILITY_ID ,
296 PRIMARY_APPROVER_ID ,
297
298 CREATED_BY ,
299 CREATION_DATE ,
300 LAST_UPDATE_LOGIN ,
301 LAST_UPDATE_DATE ,
302 LAST_UPDATED_BY
303 )
304 VALUES
305 (
306 P_DEPT_ID ,
307 P_ORGANIZATION_ID ,
308 p_resp_app_id ,
309 P_RESPONSIBILITY_ID ,
310 P_PRIMARY_APPROVER_ID ,
311
312 fnd_global.user_id,
313 sysdate,
314 fnd_global.login_id,
315 sysdate ,
316 fnd_global.user_id
317 );
318
319 -- End of API body.
320 -- Standard check of p_commit.
321 IF FND_API.To_Boolean( p_commit ) THEN
322 COMMIT WORK;
323 END IF;
324 -- Standard call to get message count and if count is 1, get message info.
325 FND_MSG_PUB.get
326 ( p_msg_index_out => x_msg_count ,
327 p_data => x_msg_data
328 );
329 EXCEPTION
330 WHEN FND_API.G_EXC_ERROR THEN
331 ROLLBACK TO INSERT_DEPT_APPR;
332 x_return_status := FND_API.G_RET_STS_ERROR ;
333 FND_MSG_PUB.get
334 ( p_msg_index_out => x_msg_count ,
335 p_data => x_msg_data
336 );
337 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
338 ROLLBACK TO INSERT_DEPT_APPR;
339 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
340 FND_MSG_PUB.get
341 ( p_msg_index_out => x_msg_count ,
342 p_data => x_msg_data
343 );
344 WHEN OTHERS THEN
345 ROLLBACK TO INSERT_DEPT_APPR;
346 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
347 IF FND_MSG_PUB.Check_Msg_Level
348 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
349 THEN
350 FND_MSG_PUB.Add_Exc_Msg
351 ( G_PKG_NAME ,
352 l_api_name
353 );
354 END IF;
355 FND_MSG_PUB.get
356 ( p_msg_index_out => x_msg_count ,
357 p_data => x_msg_data
358 );
359 END INSERT_DEPT_APPR;
360
361
362 PROCEDURE update_dept_appr
363 ( p_api_version IN NUMBER ,
364 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
365 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
366 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
367
368 x_return_status OUT NOCOPY VARCHAR2 ,
369 x_msg_count OUT NOCOPY NUMBER ,
370 x_msg_data OUT NOCOPY VARCHAR2 ,
371
372 p_dept_id IN NUMBER,
373 p_organization_id IN NUMBER,
374 p_resp_app_id IN NUMBER,
375 p_responsibility_id IN NUMBER,
376 p_primary_approver_id IN NUMBER
377
378
379
380 )
381 IS
382 l_api_name CONSTANT VARCHAR2(30) := 'update_dept_appr';
383 l_api_version CONSTANT NUMBER := 1.0;
384 l_boolean number;
385 l_return_status VARCHAR2(1);
386 l_msg_count NUMBER;
387 l_msg_data VARCHAR2(30);
388 l_bool boolean;
389
390 BEGIN
391 -- Standard Start of API savepoint
392 SAVEPOINT UPDATE_DEPT_APPR;
393 -- Standard call to check for call compatibility.
394 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
395 p_api_version ,
396 l_api_name ,
397 G_PKG_NAME )
398 THEN
399 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
400 END IF;
401 -- Initialize message list if p_init_msg_list is set to TRUE.
402 IF FND_API.to_Boolean( p_init_msg_list ) THEN
403 FND_MSG_PUB.initialize;
404 END IF;
405 -- Initialize API return status to success
406 x_return_status := FND_API.G_RET_STS_SUCCESS;
407 -- API body
408
409 EAM_COMMON_UTILITIES_PVT.verify_org(
410 p_resp_id => NULL,
411 p_resp_app_id => 401,
412 p_org_id => P_ORGANIZATION_ID,
413 x_boolean => l_boolean,
414 x_return_status => l_return_status,
415 x_msg_count => l_msg_count ,
416 x_msg_data => l_msg_data);
417 if l_boolean = 0
418 then
419 fnd_message.set_name('EAM', 'EAM_ABO_INVALID_ORG_ID');
420 fnd_msg_pub.add;
421 RAISE fnd_api.g_exc_error;
422 end if;
423
424
425 /*if not EAM_COMMON_UTILITIES_PVT.validate_department_id(p_dept_id, p_organization_id)
426 then
427 fnd_message.set_name('EAM', 'EAM_DA_INVALID_DEPT');
428 fnd_msg_pub.add;
429 RAISE fnd_api.g_exc_error;
430 end if;*/
431
432 validate_application_id(p_resp_app_id);
433
434 validate_responsibility_id( p_responsibility_id, p_resp_app_id);
435
436 -- one department per organization
437 l_bool := validate_department (p_dept_id , p_organization_id);
438 if not l_bool then
439 fnd_message.set_name('EAM', 'EAM_DA_INVALID_DEPT');
440 fnd_msg_pub.add;
441 RAISE fnd_api.g_exc_error;
442 end if;
443
444 validate_primary_approver_id (p_primary_approver_id, p_responsibility_id);
445
446 VALIDATE_ROW_EXISTS(P_DEPT_ID ,
447 P_ORGANIZATION_ID ,
448 p_resp_app_id ,
449 P_RESPONSIBILITY_ID, false );
450
451 -- Only Approver id can be updated.
452 UPDATE BOM_EAM_DEPT_APPROVERS
453 SET
454 --DEPT_ID = P_DEPT_ID ,
455 --ORGANIZATION_ID = P_ORGANIZATION_ID ,
456 --RESPONSIBILITY_APPLICATION_ID = P_RESP_APP_ID ,
457 --RESPONSIBILITY_ID = P_RESPONSIBILITY_ID ,
458 PRIMARY_APPROVER_ID = P_PRIMARY_APPROVER_ID ,
459
460 LAST_UPDATE_LOGIN = fnd_global.login_id ,
461 LAST_UPDATE_DATE = sysdate,
462 LAST_UPDATED_BY = fnd_global.user_id
463 where
464 DEPT_ID = P_DEPT_ID and
465 ORGANIZATION_ID = P_ORGANIZATION_ID and
466 RESPONSIBILITY_APPLICATION_ID = P_RESP_APP_ID and
467 RESPONSIBILITY_ID = P_RESPONSIBILITY_ID;
468
469 -- End of API body.
470 -- Standard check of p_commit.
471 IF FND_API.To_Boolean( p_commit ) THEN
472 COMMIT WORK;
473 END IF;
474 -- Standard call to get message count and if count is 1, get message info.
475 FND_MSG_PUB.get
476 ( p_msg_index_out => x_msg_count ,
477 p_data => x_msg_data
478 );
479 EXCEPTION
480 WHEN FND_API.G_EXC_ERROR THEN
481 ROLLBACK TO UPDATE_DEPT_APPR;
482 x_return_status := FND_API.G_RET_STS_ERROR ;
483 FND_MSG_PUB.get
484 ( p_msg_index_out => x_msg_count ,
485 p_data => x_msg_data
486 );
487 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
488 ROLLBACK TO UPDATE_DEPT_APPR;
489 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
490 FND_MSG_PUB.get
491 ( p_msg_index_out => x_msg_count ,
492 p_data => x_msg_data
493 );
494 WHEN OTHERS THEN
495 ROLLBACK TO UPDATE_DEPT_APPR;
496 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
497 IF FND_MSG_PUB.Check_Msg_Level
498 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
499 THEN
500 FND_MSG_PUB.Add_Exc_Msg
501 ( G_PKG_NAME ,
502 l_api_name
503 );
504 END IF;
505 FND_MSG_PUB.get
506 ( p_msg_index_out => x_msg_count ,
507 p_data => x_msg_data
508 );
509 END update_dept_appr;
510
511
512 END EAM_DEPT_APPROVERS_PUB;