[Home] [Help]
PACKAGE BODY: APPS.PA_RESOURCE_PUB
Source
1 PACKAGE BODY PA_RESOURCE_PUB AS
2 /*$Header: PAPMREPB.pls 120.2 2005/08/19 16:43:11 mwasowic noship $*/
3 -- -------------------------------------------------------------------------------------------------------
4 -- Globals
5 -- -------------------------------------------------------------------------------------------------------
6
7 G_LAST_UPDATED_BY NUMBER(15) := FND_GLOBAL.USER_ID;
8 G_LAST_UPDATE_DATE DATE := SYSDATE;
9 G_CREATION_DATE DATE := SYSDATE;
10 G_CREATED_BY NUMBER(15) := FND_GLOBAL.USER_ID;
11 G_LAST_UPDATE_LOGIN NUMBER(15) := FND_GLOBAL.LOGIN_ID;
12
13 -- -------------------------------------------------------------------------------------------------------
14 -- Procedures and Functions
15 -- -------------------------------------------------------------------------------------------------------
16
17 -- FORWARD DECLARATION ---------------------------------------------------------------
18
19 PROCEDURE Get_Resource_Name
20 (
21 p_resource_type_code IN VARCHAR2,
22 p_resource_attr_value IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
23 p_resource_name OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
24 p_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
25 );
26
27 PROCEDURE Insert_Members (
28 p_resource_list_id IN NUMBER,
29 p_group_resource_type IN VARCHAR2,
30 p_resource_type_code IN VARCHAR2,
31 p_resource_group_alias IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
32 p_resource_group_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
33 p_resource_alias IN VARCHAR2,
34 p_sort_order IN NUMBER,
35 p_enabled_flag IN VARCHAR2,
36 p_resource_attr_value IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
37 p_job_group_id IN NUMBER, --Added for bug 2486405.
38 p_parent_member_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
39 p_resource_list_member_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
40 p_track_as_labor_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
41 p_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
42 p_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
43 p_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
44 p_return_status OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
45
46 FUNCTION Fetch_Resource_list_Member_id
47 ( p_resource_list_id IN NUMBER
48 , p_alias IN VARCHAR2
49 ) RETURN NUMBER;
50
51
52 -- ===============================================================
53
54 --
55 -- Name: Create_Resource_list
56 -- Type: PL/SQL Procedure
57 -- Decscription: This procedure creates a resource list and resource list members.
58 --
59 -- Called Subprograms:
60 -- PA_CREATE_RESOURCE.Create_Resource_List
61 --
62 -- History:
63 -- xx-AUG-96 Created rkrishna
64 -- 04-DEC-96 Update jwhite Applied latest standards.
65 --
66
67 PROCEDURE Create_Resource_List
68 (p_commit IN VARCHAR2 := FND_API.G_FALSE,
69 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
70 p_api_version_number IN NUMBER,
71 p_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
72 p_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
73 p_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
74 p_resource_list_rec IN resource_list_rec,
75 p_resource_list_out_rec OUT NOCOPY resource_list_out_rec, --File.Sql.39 bug 4440895
76 p_member_tbl IN member_tbl,
77 p_member_out_tbl OUT NOCOPY member_out_tbl --File.Sql.39 bug 4440895
78 )
79 IS
80
81 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
82 l_api_name CONSTANT VARCHAR2(30) := 'Create_Resource_List';
83 l_value_conversion_error BOOLEAN := FALSE;
84 l_resource_list_rec resource_list_rec;
85 l_member_tbl member_tbl;
86 l_return_status VARCHAR2(1);
87 l_err_code NUMBER := 0;
88 l_err_stage VARCHAR2(2000);
89 l_err_stack VARCHAR2(2000);
90 l_resource_list_id NUMBER;
91 l_end_date DATE;
92 l_msg_count NUMBER ;
93 l_msg_data VARCHAR2(2000);
94 l_function_allowed VARCHAR2(1);
95 l_resp_id NUMBER := 0;
96
97
98 BEGIN
99
100 SAVEPOINT Create_Resource_List_Pub;
101
102 IF NOT FND_API.Compatible_API_Call ( l_api_version_number ,
103 p_api_version_number ,
104 l_api_name ,
105 G_PKG_NAME )
106 THEN
107 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
108 END IF;
109
110 p_return_status := FND_API.g_ret_sts_success;
111
112 l_resp_id := FND_GLOBAL.Resp_id;
113
114 -- Actions performed using the APIs would be subject to
115 -- function security. If the responsibility does not allow
116 -- such functions to be executed, the API should not proceed further
117 -- since the user does not have access to such functions
118
119
120 PA_PM_FUNCTION_SECURITY_PUB.check_function_security
121 (p_api_version_number => p_api_version_number,
122 p_responsibility_id => l_resp_id,
123 p_function_name => 'PA_PM_CREATE_RESOURCE_LIST',
124 p_msg_count => l_msg_count,
125 p_msg_data => l_msg_data,
126 p_return_status => l_return_status,
127 p_function_allowed => l_function_allowed );
128
129 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
130 THEN
131 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
132
133 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
134 THEN
135 RAISE FND_API.G_EXC_ERROR;
136 END IF;
137 IF l_function_allowed = 'N' THEN
138 FND_MESSAGE.SET_NAME('PA','PA_FUNCTION_SECURITY_ENFORCED');
139 FND_MSG_PUB.ADD;
140 p_resource_list_out_rec.return_status:= FND_API.G_RET_STS_ERROR;
141 p_return_status := FND_API.g_ret_sts_error;
142 RAISE FND_API.G_EXC_ERROR;
143 END IF;
144
145 -- l_resource_list_rec.return_status := FND_API.g_ret_sts_success;
146 IF p_resource_list_rec.end_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE THEN
147 l_end_date := NULL;
148 ELSE
149 l_end_date := p_resource_list_rec.end_date;
150 END IF;
151
152 PA_CREATE_RESOURCE.Create_Resource_List
153 (
154 p_resource_list_name => p_resource_list_rec.resource_list_name,
155 p_description => p_resource_list_rec.description,
156 p_group_resource_type => p_resource_list_rec.group_resource_type,
157 p_start_date => p_resource_list_rec.start_date,
158 p_end_date => l_end_date,
159 p_job_group_id => p_resource_list_rec.job_group_id, --Added for bug 2486405.
160 p_resource_list_id => l_resource_list_id,
161 p_err_code => l_err_code,
162 p_err_stage => l_err_stage,
163 p_err_stack => l_err_stack
164 );
165
166 IF l_err_code > 0 THEN
167 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
168 THEN
169 IF NOT pa_project_pvt.check_valid_message (l_err_stage) THEN
170 FND_MESSAGE.SET_NAME ('PA','PA_ERR_IN_RES_LIST_CREATION');
171 ELSE
172 FND_MESSAGE.SET_NAME('PA',l_err_stage);
173 END IF;
174 FND_MSG_PUB.ADD;
175 END IF;
176 RAISE FND_API.G_EXC_ERROR;
177 END IF;
178 IF l_err_code < 0 THEN
179 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
180 THEN
181 FND_MSG_PUB.Add_Exc_Msg
182 ( p_pkg_name => 'G_PKG_NAME'
183 , p_procedure_name => 'CREATE_RESOURCE_LIST'
184 , p_error_text => 'ORA-'||LPAD(SUBSTR(l_err_code, 2), 5, '0')
185 );
186
187 END IF;
188 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
189 END IF;
190
191 Process_Members
192 (p_return_status => l_return_status,
193 p_resource_list_id => l_resource_list_id,
194 p_member_tbl => p_member_tbl,
195 p_group_resource_type => p_resource_list_rec.group_resource_type,
196 p_job_group_id => p_resource_list_rec.job_group_id, --Added for bug 2486405.
197 p_msg_count => p_msg_count,
198 p_msg_data => p_msg_data,
199 p_member_out_tbl => p_member_out_tbl
200 );
201
202 IF l_return_status = FND_API.g_ret_sts_success THEN
203 p_resource_list_out_rec.return_status := FND_API.g_ret_sts_success;
204 p_resource_list_out_rec.resource_list_id := l_resource_list_id;
205 ELSE
206 p_resource_list_out_rec.return_status := l_return_status;
207 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
208 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
209 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
210 RAISE FND_API.G_EXC_ERROR;
211 END IF;
212 END IF;
213
214 IF FND_API.to_boolean( p_commit )
215 THEN
216 COMMIT;
217 END IF;
218
219 EXCEPTION
220 WHEN FND_API.G_EXC_ERROR THEN
221 p_return_status := FND_API.G_RET_STS_ERROR ;
222 ROLLBACK TO Create_Resource_List_Pub;
223
224 FND_MSG_PUB.Count_And_Get
225 ( p_count => p_msg_count ,
226 p_data => p_msg_data );
227
228 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
229 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
230 ROLLBACK TO Create_Resource_List_Pub;
231
232 FND_MSG_PUB.Count_And_Get
233 ( p_count => p_msg_count ,
234 p_data => p_msg_data );
235
236 WHEN OTHERS THEN
237 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
238 ROLLBACK TO Create_Resource_List_Pub;
239 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
240
241 FND_MSG_PUB.Add_Exc_Msg
242 ( p_pkg_name => G_PKG_NAME ,
243 p_procedure_name => l_api_name
244 );
245
246 END IF;
247
248 FND_MSG_PUB.Count_And_Get
249 ( p_count => p_msg_count ,
250 p_data => p_msg_data );
251
252 END Create_Resource_List;
253 -- ================================================================
254
255 --
256 -- Name: Process_Members
257 -- Type: PL/SQL Procedure
258 -- Decscription: This procedure creates resource list members for a given resource list.
259 --
260 -- Called Subprograms:
261 -- Insert_Members
262 --
263 -- History:
264 -- xx-AUG-96 Created rkrishna
265 -- 04-DEC-96 Update jwhite Applied latest standards.
266 --
267
268 PROCEDURE Process_Members
269 (p_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
270 p_resource_list_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
271 p_member_tbl IN member_tbl,
272 p_group_resource_type IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
273 p_job_group_id IN NUMBER, --Added for bug 2486405.
274 p_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
275 p_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
276 p_member_out_tbl OUT NOCOPY member_out_tbl --File.Sql.39 bug 4440895
277 )
278
279 IS
280
281 l_member_tbl member_tbl;
282 l_return_status VARCHAR2(1);
283 l_err_code NUMBER := 0;
284 l_err_stage VARCHAR2(2000);
285 l_err_stack VARCHAR2(2000);
286 l_person_id NUMBER;
287 l_job_id NUMBER;
288 l_proj_organization_id NUMBER;
289 l_vendor_id NUMBER;
290 l_expenditure_type VARCHAR2(30);
291 l_event_type VARCHAR2(30);
292 l_expenditure_category VARCHAR2(30);
293 l_revenue_category_code VARCHAR2(30);
294 l_resource_list_member_id NUMBER;
295 l_parent_member_id NUMBER;
296 l_track_as_labor_flag VARCHAR2(1);
297 l_group_resource_type VARCHAR2(30);
298 l_api_name CONSTANT VARCHAR2(30) := 'Process_Members ';
299
300 BEGIN
301
302 SAVEPOINT Process_Members_Pub;
303
304 p_return_status := FND_API.g_ret_sts_success;
305
306 --IF p_group_resource_type = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR or
307 -- p_group_resource_type IS NULL THEN
308 -- NULL;
309 -- Get_Group_resource_type(p_resource_list_id,
310 -- l_group_resource_type);
311 --ELSE
312 l_group_resource_type := p_group_resource_type;
313 --END IF;
314
315
316 FOR i IN 1..p_member_tbl.COUNT LOOP
317 p_member_out_tbl(i).return_status := FND_API.g_ret_sts_success;
318 l_parent_member_id := NULL;
319 l_resource_list_member_id := NULL;
320 l_track_as_labor_flag := NULL;
321 l_err_code := NULL;
322 l_err_stack := NULL;
323 l_err_stage := NULL;
324
325 Insert_Members (
326 p_resource_list_id => p_resource_list_id,
327 p_group_resource_type => p_group_resource_type,
328 p_resource_type_code =>
329 p_member_tbl(i).resource_type_code,
330 p_resource_group_alias =>
331 p_member_tbl(i).resource_group_alias,
332 p_resource_group_name =>
333 p_member_tbl(i).resource_group_name,
334 p_resource_alias => p_member_tbl(i).resource_alias,
335 p_sort_order => p_member_tbl(i).sort_order,
336 p_enabled_flag => p_member_tbl(i).enabled_flag,
337 p_resource_attr_value =>
338 p_member_tbl(i).resource_attr_value,
339 p_job_group_id => p_job_group_id, --Added for bug 2486405.
340 p_parent_member_id => l_parent_member_id,
341 p_resource_list_member_id => l_resource_list_member_id,
342 p_track_as_labor_flag => l_track_as_labor_flag,
343 p_err_code => l_err_code,
344 p_err_stage => l_err_stage,
345 p_err_stack => l_err_stack,
346 p_return_status => l_return_status );
347
348 IF l_err_code > 0 THEN
349 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
350 THEN
351 IF NOT pa_project_pvt.check_valid_message (l_err_stage) THEN
352 FND_MESSAGE.SET_NAME ('PA','PA_ERR_IN_RL_MEMB_CREATION');
353 ELSE
354 FND_MESSAGE.SET_NAME('PA',l_err_stage);
355 END IF;
356 FND_MSG_PUB.ADD;
357 END IF;
358 RAISE FND_API.G_EXC_ERROR;
359
360 ELSIF
361 l_err_code < 0 THEN
362 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
363 THEN
364 FND_MSG_PUB.Add_Exc_Msg
365 ( p_pkg_name => 'G_PKG_NAME'
366 , p_procedure_name => 'PROCESS_MEMBERS'
367 , p_error_text => 'ORA-'||LPAD(SUBSTR(l_err_code, 2), 5, '0')
368 );
369 END IF;
370 p_member_out_tbl(i).return_status
371 := FND_API.g_ret_sts_unexp_error;
372 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
373 ELSIF
374 l_err_code = 0 THEN
375 p_member_out_tbl(i).return_status :=
376 FND_API.g_ret_sts_success;
377 p_member_out_tbl(i).resource_list_member_id :=
378 l_resource_list_member_id;
379 END IF;
380 END LOOP;
381
382 EXCEPTION
383 WHEN FND_API.G_EXC_ERROR THEN
384
385 p_return_status := FND_API.G_RET_STS_ERROR ;
386 ROLLBACK TO Process_Members_Pub;
387
388 FND_MSG_PUB.Count_And_Get
389 ( p_count => p_msg_count ,
390 p_data => p_msg_data );
391
392 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
393
394 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
395 ROLLBACK TO Process_Members_Pub;
396
397 FND_MSG_PUB.Count_And_Get
398 ( p_count => p_msg_count ,
399 p_data => p_msg_data );
400
401 WHEN OTHERS THEN
402
403 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
404 ROLLBACK TO Process_Members_Pub;
405
406 IF FND_MSG_PUB.Check_Msg_Level
407 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
408
409 FND_MSG_PUB.Add_Exc_Msg
410 ( p_pkg_name => G_PKG_NAME ,
411 p_procedure_name => l_api_name
412 );
413
414 END IF;
415
416 FND_MSG_PUB.Count_And_Get
417 ( p_count => p_msg_count ,
418 p_data => p_msg_data );
419
420 END Process_Members;
421 -- ================================================================
422 --
423 -- Name: Insert_Members
424 -- Type: PL/SQL Procedure
425 -- Decscription: This procedure inserts members in the PA_RESOURCE_LIST_MEMBERS
426 -- table.
427 --
428 -- Called Subprograms: PA_CREATE_RESOURCE.Create_Resource_list_member
429 --
430 -- History:
431 -- xx-AUG-96 Created rkrishna
432 -- 04-DEC-96 Update jwhite Applied latest standards.
433 -- 26-APR-99 Update risingh added call to get_resource_name
434 -- 12-FEB-03 Update sacgupta Added job_group_id parameter to the
435 -- procedure.
436
437 PROCEDURE Insert_Members (
438 p_resource_list_id IN NUMBER,
439 p_group_resource_type IN VARCHAR2,
440 p_resource_type_code IN VARCHAR2,
441 p_resource_group_alias IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
442 p_resource_group_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
443 p_resource_alias IN VARCHAR2,
444 p_sort_order IN NUMBER,
445 p_enabled_flag IN VARCHAR2,
446 p_resource_attr_value IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
447 p_job_group_id IN NUMBER, --Added for bug 2486405.
448 p_parent_member_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
449 p_resource_list_member_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
450 p_track_as_labor_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
451 p_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
452 p_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
453 p_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
454 p_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
455 )
456
457 IS
458 l_err_code NUMBER := 0;
459 l_err_stage VARCHAR2(2000);
460 l_err_stack VARCHAR2(2000);
461 l_person_id NUMBER;
462 l_job_id NUMBER;
463 l_proj_organization_id NUMBER;
464 l_vendor_id NUMBER;
465 l_expenditure_type VARCHAR2(30);
466 l_event_type VARCHAR2(30);
467 l_expenditure_category VARCHAR2(30);
468 l_revenue_category_code VARCHAR2(30);
469 l_resource_list_member_id NUMBER;
470 l_parent_member_id NUMBER;
471 l_track_as_labor_flag VARCHAR2(1);
472 l_api_name CONSTANT VARCHAR2(30):= 'Insert_Members';
473 l_resource_group_name VARCHAR2(80);
474 l_resource_name VARCHAR2(240); -- Bug 2487415
475
476 CURSOR l_org_csr IS
477 SELECT organization_id
478 FROM pa_organizations_res_v
479 WHERE organization_name = l_resource_group_name;
480
481
482 BEGIN
483 p_return_status := FND_API.g_ret_sts_success;
484
485 IF ((p_resource_alias IS NULL ) OR
486 (p_resource_alias = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)) THEN
487 FND_MESSAGE.SET_NAME('PA','PA_NEW_ALIAS_IS_INVALID ');
488 FND_MSG_PUB.ADD;
489 RAISE FND_API.G_EXC_ERROR;
490 END IF;
491
492 l_person_id := NULL;
493 l_job_id := NULL;
494 l_proj_organization_id := NULL;
495 l_vendor_id := NULL;
496 l_expenditure_type := NULL;
497 l_event_type := NULL;
498 l_expenditure_category := NULL;
499 l_revenue_category_code := NULL;
500 IF p_resource_type_code = 'EMPLOYEE' THEN
501 l_person_id := TO_NUMBER(p_resource_attr_value);
502 ELSIF p_resource_type_code = 'JOB' THEN
503 l_job_id := TO_NUMBER(p_resource_attr_value);
504 ELSIF p_resource_type_code = 'ORGANIZATION' THEN
505 l_proj_organization_id :=
506 TO_NUMBER(p_resource_attr_value);
507 ELSIF p_resource_type_code = 'VENDOR' THEN
508 l_vendor_id := TO_NUMBER(p_resource_attr_value);
509 ELSIF p_resource_type_code = 'EXPENDITURE_TYPE'
510 THEN l_expenditure_type :=
511 p_resource_attr_value;
512 ELSIF p_resource_type_code = 'EVENT_TYPE' THEN
513 l_event_type := p_resource_attr_value;
514 ELSIF p_resource_type_code =
515 'EXPENDITURE_CATEGORY' THEN
516 l_expenditure_category := p_resource_attr_value;
517 ELSIF p_resource_type_code =
518 'REVENUE_CATEGORY' THEN
519 l_revenue_category_code := p_resource_attr_value;
520 END IF;
521 l_parent_member_id := NULL;
522 l_resource_list_member_id := NULL;
523 l_track_as_labor_flag := NULL;
524 l_err_code := NULL;
525 l_err_stack := NULL;
526 l_err_stage := NULL;
527
528 IF p_group_resource_type = 'EXPENDITURE_CATEGORY'
529 THEN
530 l_expenditure_category := p_resource_group_alias;
531 ELSIF
532 p_group_resource_type = 'REVENUE_CATEGORY'
533 THEN
534 l_revenue_category_code := p_resource_group_alias;
535 ELSIF
536 p_group_resource_type = 'ORGANIZATION'
537 THEN
538 IF (p_resource_group_alias IS NULL OR
539 p_resource_group_alias = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) THEN
540 l_resource_group_name := p_resource_group_name;
541 ELSE
542 l_resource_group_name := p_resource_group_name;
543 END IF;
544 OPEN l_org_csr;
545 FETCH l_org_csr INTO l_proj_organization_id;
546 IF l_org_csr%NOTFOUND THEN
547 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
548 THEN
549 FND_MESSAGE.SET_NAME('PA','PA_INVALID_ORGANIZATION');
550 FND_MSG_PUB.ADD;
551 END IF;
552 CLOSE l_org_csr;
553 RAISE FND_API.G_EXC_ERROR;
554 ELSE
555 CLOSE l_org_csr;
556 END IF;
557 END IF;
558
559 -- Begin fix 864942 Risingh 04/24/99
560
561 Get_Resource_Name(
562 p_resource_type_code => p_resource_type_code,
563 p_resource_attr_value => p_resource_attr_value,
564 p_resource_name => l_resource_name,
565 p_return_status => p_return_status);
566
567 -- End fix 864942
568
569 PA_CREATE_RESOURCE.Create_Resource_list_member (
570 p_resource_list_id => p_resource_list_id,
571 p_resource_name => l_resource_name, -- fix 864942
572 p_resource_type_Code => p_resource_type_code,
573 p_alias => p_resource_alias,
574 p_sort_order => p_sort_order,
575 p_display_flag => 'Y',
576 p_enabled_flag => p_enabled_flag,
577 p_person_id => l_person_id,
578 p_job_id => l_job_id,
579 p_proj_organization_id => l_proj_organization_id,
580 p_vendor_id => l_vendor_id,
581 p_expenditure_type => l_expenditure_type,
582 p_event_type => l_event_type,
583 p_expenditure_category => l_expenditure_category,
584 p_revenue_category_code => l_revenue_category_code,
585 p_non_labor_resource => NULL,
586 p_system_linkage => NULL,
587 p_job_group_id => p_job_group_id, -- Added for bug 2486405.
588 p_parent_member_id => l_parent_member_id,
589 p_resource_list_member_id => l_resource_list_member_id,
590 p_track_as_labor_flag => l_track_as_labor_flag,
591 p_err_code => l_err_code,
592 p_err_stage => l_err_stage,
593 p_err_stack => l_err_stack );
594
595 p_parent_member_id := l_parent_member_id;
596 p_resource_list_member_id := l_resource_list_member_id;
597 p_track_as_labor_flag := l_track_as_labor_flag;
598 p_err_code := l_err_code;
599 p_err_stage := l_err_stage;
600 p_err_stack := l_err_stack;
601
602
603 EXCEPTION
604 WHEN FND_API.G_EXC_ERROR THEN
605 p_return_status := FND_API.G_RET_STS_ERROR ;
606
607 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
608 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
609
610
611 WHEN OTHERS THEN
612 /* Added the if condition block for bug 2259703 */
613 IF NVL(p_err_code,0) = 0 THEN
614 p_err_code := SQLCODE;
615 END IF;
616
617 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
618
619 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
620 FND_MSG_PUB.Add_Exc_Msg
621 ( p_pkg_name => G_PKG_NAME ,
622 p_procedure_name => l_api_name
623 );
624 END IF;
625
626 END Insert_Members;
627 -- ================================================================
628
629 --
630 -- Name: Init_Create_Resource_List
631 -- Type: PL/SQL Procedure
632 -- Decscription: This procedure initializes the global tables for resource lists and
633 -- resource list members.
634 --
635 -- Called Subprograms: None.
636 --
637 --
638 -- History:
639 -- xx-AUG-96 Created rkrishna
640 -- 08-DEC-96 Update jwhite Applied latest standards and merged
641 -- a 'init_create_members' API with this
642 -- API as per Ashwani's direction.
643 --
644
645 PROCEDURE Init_Create_Resource_List IS
646
647 l_api_name CONSTANT VARCHAR2(30) := 'Init_Create_Resource_List';
648
649 BEGIN
650 FND_MSG_PUB.initialize;
651
652 -- Initialize Resource List Globals
653 g_resource_list_rec := g_miss_resource_list_rec;
654 g_resource_list_out_rec := g_miss_resource_list_out_rec;
655 g_member_tbl.DELETE;
656 g_member_out_tbl.DELETE;
657 g_member_tbl_count := 0;
658
659 -- Initialize Resource List Members Globals
660
661 g_load_member_tbl.DELETE;
662 g_load_member_out_tbl.DELETE;
663 g_load_member_tbl_count := 0;
664 g_load_resource_list_id := 0;
665
666 EXCEPTION
667
668 WHEN OTHERS THEN
669
670 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
671 FND_MSG_PUB.Add_Exc_Msg
672 ( p_pkg_name => G_PKG_NAME ,
673 p_procedure_name => l_api_name
674 );
675 END IF;
676
677
678 END Init_Create_Resource_List;
679 -- ==============================================================
680
681 --
682 -- Name: Load_Resource_List
683 -- Type: PL/SQL Procedure
684 -- Decscription: This procedure loads the resource list globals.
685 --
686 -- Called Subprograms: None.
687 --
688 --
689 -- History:
690 -- xx-AUG-96 Created rkrishna
691 -- 08-DEC-96 Update jwhite Applied latest standards
692 --
693
694
695 PROCEDURE Load_Resource_List
696 ( p_api_version_number IN NUMBER,
697 p_resource_list_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
698 p_description IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
699 p_group_resource_type IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
700 p_start_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
701 p_end_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
702 p_resource_list_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
703 p_new_list_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
704 p_job_group_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM, --Added for bug 2486405.
705 p_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
706 )
707 IS
708
709 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
710 l_api_name CONSTANT VARCHAR2(30) := 'Load_Resource_List';
711
712 BEGIN
713 p_return_status := FND_API.g_ret_sts_success;
714
715 -- Standard Api compatibility call
716 IF NOT FND_API.Compatible_API_Call ( l_api_version_number ,
717 p_api_version_number ,
718 l_api_name ,
719 G_PKG_NAME )
720 THEN
721 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
722 END IF;
723
724 g_resource_list_rec.resource_list_name := p_resource_list_name;
725 g_resource_list_rec.description := p_description;
726 g_resource_list_rec.group_resource_type := p_group_resource_type;
727 g_resource_list_rec.start_date := p_start_date;
728 g_resource_list_rec.end_date := p_end_date;
729 g_resource_list_rec.resource_list_id := p_resource_list_id;
730 g_resource_list_rec.new_list_name := p_new_list_name;
731 g_resource_list_rec.job_group_id := p_job_group_id; --Added for bug 2486405.
732
733 EXCEPTION
734
735 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
736 THEN
737
738 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
739
740
741 WHEN OTHERS THEN
742
743 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
744
745 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
746 FND_MSG_PUB.Add_Exc_Msg
747 ( p_pkg_name => G_PKG_NAME ,
748 p_procedure_name => l_api_name
749 );
750 END IF;
751
752
753 END Load_Resource_List;
754 -- ==============================================================
755
756 --
757 -- Name: Load_Members
758 -- Type: PL/SQL Procedure
759 -- Decscription: This procedure loads the resource list members globals.
760 --
761 -- Called Subprograms: None.
762 --
763 --
764 -- History:
765 -- xx-AUG-96 Created rkrishna
766 -- 08-DEC-96 Update jwhite Applied latest standards
767 --
768
769
770 PROCEDURE Load_Members
771 ( p_api_version_number IN NUMBER,
772 p_resource_group_alias IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
773 p_resource_group_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
774 p_resource_type_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
775 p_resource_attr_value IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
776 p_resource_alias IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
777 p_resource_list_member_id IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
778 p_new_alias IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
779 p_sort_order IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
780 p_enabled_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
781 p_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
782 )
783 IS
784
785 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
786 l_api_name CONSTANT VARCHAR2(30) := 'Load_members';
787 l_enabled_flag pa_resource_list_members.enabled_flag%TYPE;
788 l_sort_order pa_resource_list_members.sort_order%TYPE;
789
790 BEGIN
791 p_return_status := FND_API.g_ret_sts_success;
792
793 -- Standard Api compatibility call
794
795 IF NOT FND_API.Compatible_API_Call ( l_api_version_number ,
796 p_api_version_number ,
797 l_api_name ,
798 G_PKG_NAME )
799 THEN
800 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
801 END IF;
802
803 -- VALUE LAYER ---------------------------------------------------------------
804
805 -- Default Sort Order if Parameter NOT Passed
806
807 IF (p_sort_order = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
808 l_sort_order := NULL;
809 ELSE
810 l_sort_order := p_sort_order;
811 END IF;
812
813
814 -- Default Enabled Flag if Parameter NOT Passed
815
816 IF (p_enabled_flag = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) THEN
817 l_enabled_flag := 'Y';
818 ELSE
819 l_enabled_flag := p_enabled_flag;
820 END IF;
821
822 -- Assign Globals
823 g_member_tbl_count := g_member_tbl_count + 1;
824 g_member_tbl(g_member_tbl_count).resource_group_alias
825 := p_resource_group_alias;
826 g_member_tbl(g_member_tbl_count).resource_group_name
827 := p_resource_group_name ;
828 g_member_tbl(g_member_tbl_count).resource_type_code
829 := p_resource_type_code;
830 g_member_tbl(g_member_tbl_count).resource_attr_value
831 := p_resource_attr_value;
832 g_member_tbl(g_member_tbl_count).resource_alias
833 := p_resource_alias;
834 g_member_tbl(g_member_tbl_count).sort_order
835 := l_sort_order ;
836 g_member_tbl(g_member_tbl_count).enabled_flag
837 := l_enabled_flag;
838 g_member_tbl(g_member_tbl_count).new_alias
839 := p_new_alias;
840 g_member_tbl(g_member_tbl_count).resource_list_member_id
841 := p_resource_list_member_id;
842
843 EXCEPTION
844
845 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
846 THEN
847
848 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
849
850
851 WHEN OTHERS THEN
852
853 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
854
855 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
856 FND_MSG_PUB.Add_Exc_Msg
857 ( p_pkg_name => G_PKG_NAME ,
858 p_procedure_name => l_api_name
859 );
860 END IF;
861
862
863 END Load_Members;
864 -- ==============================================================
865
866 --
867 -- Name: Exec_Create_Resource_List
868 -- Type: PL/SQL Procedure
869 -- Decscription: This procedure passes the PL/SQL globals to the Create_Resource_List
870 -- API. The API is typically used with the load-execute-fetch model.
871 --
872 -- Called Subprograms: Create_Resource_List
873 --
874 --
875 -- History:
876 -- xx-AUG-96 Created rkrishna
877 -- 08-DEC-96 Update jwhite Applied latest standards
878 --
879
880
881 PROCEDURE Exec_Create_Resource_List
882 (p_commit IN VARCHAR2 := FND_API.G_FALSE,
883 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
884 p_api_version_number IN NUMBER,
885 p_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
886 p_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
887 p_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
888 )
889 IS
890
891 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
892 l_api_name CONSTANT VARCHAR2(30) := 'Exec_Create_Resource_List';
893 l_message_count NUMBER;
894
895 BEGIN
896 p_return_status := FND_API.g_ret_sts_success;
897
898 -- Standard Api compatibility call
899
900 IF NOT FND_API.Compatible_API_Call ( l_api_version_number ,
901 p_api_version_number ,
902 l_api_name ,
903 G_PKG_NAME )
904 THEN
905 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
906 END IF;
907
908 Create_Resource_List
909 (p_commit => p_commit,
910 p_init_msg_list => p_init_msg_list,
911 p_api_version_number => p_api_version_number,
912 p_return_status => p_return_status,
913 p_msg_count => p_msg_count,
914 p_msg_data => p_msg_data,
915 p_resource_list_rec => g_resource_list_rec,
916 p_resource_list_out_rec => g_resource_list_out_rec,
917 p_member_tbl => g_member_tbl,
918 p_member_out_tbl => g_member_out_tbl);
919
920 EXCEPTION
921
922 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
923 THEN
924
925 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
926
927 WHEN OTHERS THEN
928
929 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
930
931 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
932 FND_MSG_PUB.Add_Exc_Msg
933 ( p_pkg_name => G_PKG_NAME ,
934 p_procedure_name => l_api_name
935 );
936 END IF;
937
938 FND_MSG_PUB.Count_And_Get
939 ( p_count => p_msg_count ,
940 p_data => p_msg_data );
941
942
943 END Exec_Create_Resource_List;
944 -- ==============================================================
945
946 --
947 -- Name: Fetch_Resource_List
948 -- Type: PL/SQL Procedure
949 -- Decscription: This procedure passes returns the return status and new created
950 -- resource_list_id, if any, from a load-execute-fetch cycle.
951 --
952 -- Called Subprograms: None.
953 --
954 --
955 -- History:
956 -- xx-AUG-96 Created rkrishna
957 -- 08-DEC-96 Update jwhite Applied latest standards
958 --
959
960
961 PROCEDURE Fetch_Resource_List
962 (
963 p_api_version_number IN NUMBER,
964 p_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
965 p_resource_list_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
966 p_list_return_status OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
967
968 IS
969
970 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
971 l_api_name CONSTANT VARCHAR2(30):= 'Fetch_Resource_List';
972 l_msg_count INTEGER :=0;
973
974 BEGIN
975
976 p_return_status := FND_API.G_RET_STS_SUCCESS;
977
978 IF NOT FND_API.Compatible_API_Call ( l_api_version_number ,
979 p_api_version_number ,
980 l_api_name ,
981 G_PKG_NAME )
982 THEN
983 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
984 END IF;
985
986 -- Get Resource List Out Values
987
988 p_resource_list_id := g_resource_list_out_rec.resource_list_id;
989 p_list_return_status := g_resource_list_out_rec.return_status;
990
991
992 EXCEPTION
993
994 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
995
996 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
997
998 WHEN OTHERS THEN
999
1000 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1001
1002 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1003 FND_MSG_PUB.Add_Exc_Msg
1004 ( p_pkg_name => G_PKG_NAME ,
1005 p_procedure_name => l_api_name
1006 );
1007 END IF;
1008
1009
1010 END Fetch_Resource_List;
1011 -- ==============================================================
1012
1013 --
1014 -- Name: Fetch_Members
1015 -- Type: PL/SQL Procedure
1016 -- Decscription: This procedure passes returns the return status and new created
1017 -- resource_list_member_id, if any, from a load-execute-fetch cycle
1018 -- for a given index number.
1019 --
1020 -- Called Subprograms: None.
1021 --
1022 --
1023 -- History:
1024 -- xx-AUG-96 Created rkrishna
1025 -- 08-DEC-96 Update jwhite Applied latest standards
1026 --
1027
1028
1029 PROCEDURE Fetch_Members
1030 ( p_api_version_number IN NUMBER,
1031 p_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1032 p_member_index IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
1033 p_resource_list_member_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1034 p_member_return_status OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
1035 IS
1036
1037 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
1038 l_api_name CONSTANT VARCHAR2(30):= 'Fetch_Members';
1039 l_index NUMBER;
1040
1041 BEGIN
1042
1043 p_return_status := FND_API.G_RET_STS_SUCCESS;
1044
1045 IF NOT FND_API.Compatible_API_Call ( l_api_version_number ,
1046 p_api_version_number ,
1047 l_api_name ,
1048 G_PKG_NAME )
1049 THEN
1050 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1051 END IF;
1052 -- Check Line index value
1053
1054 IF p_member_index = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
1055 l_index := 1;
1056 ELSE
1057 l_index := p_member_index ;
1058 END IF;
1059
1060 -- Check whether an entry exists in the G_member_tbl or not.
1061 -- If there is no entry with that index , then do nothing
1062
1063 IF NOT g_member_tbl.EXISTS(l_index) THEN
1064 p_resource_list_member_id := NULL;
1065 p_member_return_status := NULL;
1066 ELSE
1067 p_resource_list_member_id :=
1068 g_member_out_tbl(l_index).resource_list_member_id;
1069 p_member_return_status :=
1070 g_member_out_tbl(l_index).return_status;
1071 END IF;
1072
1073
1074 EXCEPTION
1075
1076 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1077
1078 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1079
1080 WHEN OTHERS THEN
1081
1082 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1083
1084 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1085 FND_MSG_PUB.Add_Exc_Msg
1086 ( p_pkg_name => G_PKG_NAME ,
1087 p_procedure_name => l_api_name
1088 );
1089 END IF;
1090
1091
1092 END Fetch_Members;
1093 -- ==============================================================
1094
1095 --
1096 -- Name: Clear_Create_Resource_List
1097 -- Type: PL/SQL Procedure
1098 -- Decscription: This procedure initializes the global tables for resource lists and
1099 -- resource list members.
1100 --
1101 -- Called Subprograms: None.
1102 --
1103 --
1104 -- History:
1105 -- xx-AUG-96 Created rkrishna
1106 -- 08-DEC-96 Update jwhite Applied latest standards and merged
1107 -- a 'clear_create_members' API with this
1108 -- API as per Ashwani's direction.
1109 --
1110
1111
1112 PROCEDURE Clear_Create_Resource_List IS
1113 BEGIN
1114 init_create_resource_list;
1115 END Clear_Create_Resource_List;
1116 -- ==============================================================
1117
1118 --
1119 -- Name: Update_Resource_List
1120 -- Type: PL/SQL Procedure
1121 -- Decscription: This procedure updates an existing reource list.
1122 --
1123 -- Called Subprograms: None.
1124 --
1125 --
1126 -- History:
1127 -- xx-AUG-96 Created rkrishna
1128 -- 08-DEC-96 Update jwhite Applied latest standards
1129 --
1130
1131 PROCEDURE Update_Resource_List
1132 (p_commit IN VARCHAR2 := FND_API.G_FALSE,
1133 p_api_version_number IN NUMBER,
1134 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1135 p_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1136 p_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1137 p_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1138 p_resource_list_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
1139 p_resource_list_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
1140 p_new_list_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
1141 p_grouped_by_type IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
1142 p_description IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
1143 p_start_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
1144 p_end_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
1145 p_member_tbl IN member_tbl,
1146 p_member_out_tbl OUT NOCOPY member_out_tbl --File.Sql.39 bug 4440895
1147 )
1148 IS
1149
1150 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
1151 l_api_name CONSTANT VARCHAR2(30) := 'Update_Resource_List';
1152 l_message_count NUMBER;
1153
1154 --version of add_resource_list_member with which this API is compatible
1155 l_version_add_member NUMBER := G_API_VERSION_NUMBER;
1156
1157 --version of update_resource_list_member with which this API is compatible
1158 l_version_update_member NUMBER := G_API_VERSION_NUMBER;
1159
1160 l_resource_list_id NUMBER := 0;
1161 l_return_status VARCHAR2(1);
1162 l_dummy VARCHAR2(1);
1163 l_cursor NUMBER;
1164 l_rows NUMBER;
1165 l_statement VARCHAR2(2000);
1166 l_update_header_flag VARCHAR2(1);
1167 l_update_member_flag VARCHAR2(1);
1168 l_resource_type_id NUMBER;
1169 l_resource_list_member_id NUMBER;
1170
1171 l_rowid_old VARCHAR2(20) := NULL;
1172 l_start_date_active_old pa_resource_lists.start_date_active%TYPE;
1173 l_end_date_active_old pa_resource_lists.end_date_active%TYPE;
1174 l_name_old pa_resource_lists.name%TYPE;
1175 l_group_resource_type_id_old pa_resource_lists.group_resource_type_id%TYPE;
1176 l_description_old pa_resource_lists.description%TYPE;
1177 l_msg_count NUMBER ;
1178 l_msg_data VARCHAR2(2000);
1179 l_function_allowed VARCHAR2(1);
1180 l_resp_id NUMBER := 0;
1181
1182
1183
1184 -- Check Uniqueness of New Resource List Name
1185 CURSOR l_new_list_name_csr (p_new_list_name VARCHAR2)
1186 IS
1187 SELECT 'x'
1188 FROM pa_resource_lists rl
1189 WHERE rl.name = p_new_list_name;
1190
1191
1192 -- FIX, 12-FEB-97, jwhite:
1193 -- Modified cursor to ignore Unclassified members
1194 -- ------------------------------------------------------------------------------------
1195 -- Validate Grouped By Type (Cannot Change List Group By if
1196 -- Classified Members Exist)
1197 CURSOR l_grouped_by_type_csr (l_resource_list_id NUMBER)
1198 IS
1199 SELECT 'x'
1200 FROM sys.dual
1201 WHERE EXISTS
1202 (SELECT 'x'
1203 FROM pa_resource_list_members rlm
1204 , pa_resources r
1205 , pa_resource_types rt
1206 WHERE
1207 rlm.resource_list_id = l_resource_list_id
1208 AND rlm.resource_id = r.resource_id
1209 AND r.resource_type_id = rt.resource_type_id
1210 AND rt.resource_type_code <> 'UNCLASSIFIED');
1211 -- ------------------------------------------------------------------------------------
1212
1213 -- Validate Resource Type Id
1214 CURSOR l_resource_type_csr (p_grouped_by_type VARCHAR2)
1215 IS
1216 SELECT rta.resource_type_id
1217 FROM pa_resource_types_active_v rta
1218 WHERE rta.resource_type_code = p_grouped_by_type;
1219
1220 -- Get Original Updatable Columns on PA_RESOURCE_LISTS
1221
1222 CURSOR l_orignal_columns_csr (l_resource_list_id NUMBER)
1223 IS
1224 SELECT rl.name, rl.group_resource_type_id, rl.description, rl.start_date_active, rl.end_date_active, ROWID
1225 FROM pa_resource_lists rl
1226 WHERE rl.resource_list_id = l_resource_list_id;
1227
1228 -- Lock Row of Existing Resource List Before Update
1229
1230 CURSOR l_lock_row_list_csr (l_rowid_old VARCHAR2)
1231 IS
1232 SELECT 'x'
1233 FROM pa_resource_lists
1234 WHERE ROWID = l_rowid_old
1235 FOR UPDATE NOWAIT;
1236
1237
1238
1239 BEGIN
1240
1241 -- Standard Api compatibility call
1242 SAVEPOINT Update_Resource_List_Pub;
1243
1244 IF NOT FND_API.Compatible_API_Call ( l_api_version_number ,
1245 p_api_version_number ,
1246 l_api_name ,
1247 G_PKG_NAME )
1248 THEN
1249 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1250 END IF;
1251 l_resp_id := FND_GLOBAL.Resp_id;
1252
1253 -- Actions performed using the APIs would be subject to
1254 -- function security. If the responsibility does not allow
1255 -- such functions to be executed, the API should not proceed further
1256 -- since the user does not have access to such functions
1257
1258
1259 PA_PM_FUNCTION_SECURITY_PUB.check_function_security
1260 (p_api_version_number => p_api_version_number,
1261 p_responsibility_id => l_resp_id,
1262 p_function_name => 'PA_PM_UPDATE_RESOURCE_LIST',
1263 p_msg_count => l_msg_count,
1264 p_msg_data => l_msg_data,
1265 p_return_status => l_return_status,
1266 p_function_allowed => l_function_allowed );
1267
1268 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1269 THEN
1270 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1271
1272 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
1273 THEN
1274 RAISE FND_API.G_EXC_ERROR;
1275 END IF;
1276 IF l_function_allowed = 'N' THEN
1277 FND_MESSAGE.SET_NAME('PA','PA_FUNCTION_SECURITY_ENFORCED');
1278 FND_MSG_PUB.ADD;
1279 p_return_status := FND_API.g_ret_sts_error;
1280 RAISE FND_API.G_EXC_ERROR;
1281 END IF;
1282
1283 IF FND_API.to_boolean( p_init_msg_list )
1284 THEN
1285 FND_MSG_PUB.initialize;
1286 END IF;
1287
1288 -- -----------------------------------------------------------------------
1289 -- Resource List HEADER
1290 -- -----------------------------------------------------------------------
1291
1292 p_return_status := FND_API.G_RET_STS_SUCCESS;
1293
1294
1295 -- VALUE LAYER ----------------------------------------------------------------
1296
1297 Convert_List_name_to_id
1298 ( p_resource_list_name => p_resource_list_name,
1299 p_resource_list_id => p_resource_list_id,
1300 p_out_resource_list_id => l_resource_list_id,
1301 p_return_status => l_return_status
1302 );
1303
1304 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1305 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1306 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1307 RAISE FND_API.G_EXC_ERROR;
1308 END IF;
1309
1310
1311 -- Validate Date IN Parameters
1312 -- If a start date is passed, it must not be null. If a start is not passed, then the list start date won't be
1313 -- updated at all.
1314 -- The end date can be null.
1315 -- If a start date and end date are passed, then the start date must be not be later than the end date.
1316
1317 IF ((p_start_date <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)) THEN
1318 IF (p_start_date IS NULL) THEN
1319 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1320 THEN
1321 FND_MESSAGE.SET_NAME('PA','PA_INVALID_START_DATE');
1322 FND_MSG_PUB.ADD;
1323 END IF;
1324 RAISE FND_API.G_EXC_ERROR;
1325 ELSIF ((p_end_date <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
1326 AND (p_end_date IS NOT NULL)) THEN
1327 IF (p_start_date > p_end_date)
1328 THEN
1329 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1330 THEN
1331 FND_MESSAGE.SET_NAME('PA','PA_INVALID_START_DATE');
1332 FND_MSG_PUB.ADD;
1333 END IF;
1334 RAISE FND_API.G_EXC_ERROR;
1335 END IF;
1336 END IF;
1337 END IF;
1338
1339 -- Validate IN Parameters New Resource List Name and Grouped By Type
1340
1341
1342 -- New List Name Must NOT Be Null
1343
1344 IF (p_new_list_name IS NULL) THEN
1345 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1346 THEN
1347 FND_MESSAGE.SET_NAME('PA', 'PA_NEW_RES_LIST_NO_NULL');
1348 FND_MSG_PUB.ADD;
1349 END IF;
1350 RAISE FND_API.G_EXC_ERROR;
1351 END IF;
1352
1353 -- New List Name Must NOT Already Exist
1354
1355 IF (p_new_list_name <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) THEN
1356 OPEN l_new_list_name_csr (p_new_list_name);
1357 FETCH l_new_list_name_csr INTO l_dummy;
1358 IF (l_new_list_name_csr %FOUND) THEN
1359 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1360 THEN
1361 FND_MESSAGE.SET_NAME('PA', 'PA_RE_RL_UNIQUE');
1362 FND_MSG_PUB.ADD;
1363 END IF;
1364 CLOSE l_new_list_name_csr;
1365 RAISE FND_API.G_EXC_ERROR;
1366 ELSE
1367 CLOSE l_new_list_name_csr;
1368 END IF;
1369 END IF;
1370
1371 -- Cannot Change Grouped-By-Type if List Already has Members
1372
1373 IF (p_grouped_by_type <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) THEN
1374 OPEN l_grouped_by_type_csr ( l_resource_list_id);
1375 FETCH l_grouped_by_type_csr INTO l_dummy;
1376 IF (l_grouped_by_type_csr%FOUND) THEN
1377 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1378 THEN
1379 FND_MESSAGE.SET_NAME('PA', 'PA_NO_CHANGE_GROUP_BY_TYPE');
1380 FND_MSG_PUB.ADD;
1381 END IF;
1382 CLOSE l_grouped_by_type_csr;
1383 RAISE FND_API.G_EXC_ERROR;
1384 ELSE
1385 CLOSE l_grouped_by_type_csr;
1386 END IF;
1387 END IF;
1388
1389
1390 -- Get Grouped_By_Type Resource_Type_Id
1391
1392 IF (p_grouped_by_type <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) THEN
1393 IF (p_grouped_by_type = 'NONE') THEN
1394 l_resource_type_id := 0;
1395 ELSE
1396 OPEN l_resource_type_csr (p_grouped_by_type);
1397 FETCH l_resource_type_csr INTO l_resource_type_id;
1398 IF (l_resource_type_csr%NOTFOUND) THEN
1399 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1400 THEN
1401 FND_MESSAGE.SET_NAME('PA', 'PA_GROUPED_RT_INVALID');
1402 FND_MSG_PUB.ADD;
1403 END IF;
1404 CLOSE l_resource_type_csr;
1405 RAISE FND_API.G_EXC_ERROR;
1406 ELSE
1407 CLOSE l_resource_type_csr;
1408 END IF;
1409 END IF;
1410 END IF;
1411
1412 -- Get Original Updatable Columns for Validation and SQL Update Statement Generation
1413
1414 OPEN l_orignal_columns_csr (l_resource_list_id);
1415 FETCH l_orignal_columns_csr INTO l_name_old, l_group_resource_type_id_old, l_description_old, l_start_date_active_old, l_end_date_active_old,
1416 l_rowid_old;
1417 CLOSE l_orignal_columns_csr;
1418
1419
1420
1421 -- Validate Date IN Parameters Against Existing Resource List Row
1422
1423 IF ((p_start_date <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
1424 OR (p_end_date <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE))
1425 THEN
1426 IF ((p_start_date <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE) AND
1427 (l_end_date_active_old IS NOT NULL))
1428 THEN
1429 IF (p_start_date > l_end_date_active_old)
1430 THEN
1431 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1432 THEN
1433 FND_MESSAGE.SET_NAME('PA','PA_INVALID_START_DATE');
1434 FND_MSG_PUB.ADD;
1435 END IF;
1436 RAISE FND_API.G_EXC_ERROR;
1437 END IF;
1438 END IF;
1439
1440 IF (p_start_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
1441 THEN
1442 IF ((p_end_date <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
1443 AND (p_end_date IS NOT NULL))
1444 THEN
1445 IF (l_start_date_active_old > p_end_date)
1446 THEN
1447 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1448 THEN
1449 FND_MESSAGE.SET_NAME('PA','PA_INVALID_END_DATE');
1450 FND_MSG_PUB.ADD;
1451 END IF;
1452 RAISE FND_API.G_EXC_ERROR;
1453 END IF;
1454 END IF;
1455 END IF;
1456 END IF;
1457
1458
1459 -- BUILD UPDATE SQL Statement for Resourec List Header ---------------------------------------------
1460
1461 l_update_header_flag := 'N';
1462 l_statement := 'UPDATE PA_RESOURCE_LISTS SET ';
1463
1464 --Changes done for SQL BIND VARIABLE by xin liu. 13-May-2003
1465
1466 IF (p_new_list_name <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1467 AND (nvl(p_new_list_name, PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) <>
1468 nvl(l_name_old,PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR))
1469 THEN
1470 l_statement := l_statement || 'NAME = :xName'||',';
1471 l_update_header_flag := 'Y';
1472 END IF;
1473
1474 IF (p_grouped_by_type <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1475 AND ( nvl(l_resource_type_id, PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) <>
1476 nvl(l_group_resource_type_id_old,PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM))
1477 THEN
1478 l_statement := l_statement || 'GROUP_RESOURCE_TYPE_ID = :xGRTID'||',';
1479 l_update_header_flag := 'Y';
1480 END IF;
1481
1482 IF (p_description <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1483 AND (nvl(p_description, PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) <>
1484 nvl(l_description_old,PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR))
1485 THEN
1486 l_statement := l_statement || 'DESCRIPTION =:xDescription' ||',';
1487 l_update_header_flag := 'Y';
1488 END IF;
1489
1490 IF (p_start_date <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
1491 AND (nvl(p_start_date, PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE) <> nvl(l_start_date_active_old,
1492 PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE))
1493 THEN
1494 l_statement := l_statement || 'START_DATE_ACTIVE = '||''''||TO_CHAR(p_start_date)||''''||',';
1495 l_update_header_flag := 'Y';
1496 END IF;
1497
1498 IF (p_end_date <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
1499 AND (nvl(p_end_date, PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE) <> nvl(l_end_date_active_old,
1500 PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE))
1501 THEN
1502 l_statement := l_statement || 'END_DATE_ACTIVE = '||''''||TO_CHAR(p_end_date)||''''||',';
1503 l_update_header_flag := 'Y';
1504 END IF;
1505
1506
1507 IF (l_update_header_flag = 'Y') THEN
1508
1509 l_statement := l_statement ||'LAST_UPDATE_DATE ='||''''||TO_CHAR(g_last_update_date)||''''||',';
1510 l_statement := l_statement ||'LAST_UPDATED_BY ='||''''||TO_CHAR(g_last_updated_by)||''''||',';
1511 l_statement := l_statement ||'LAST_UPDATE_LOGIN = '||''''||TO_CHAR(g_last_update_login)||'''';
1512
1513 l_statement := l_statement || ' WHERE RESOURCE_LIST_ID = '|| TO_CHAR(l_resource_list_id);
1514
1515 -- UPDATE Resource List Header ---------------------------------------------------------------------------
1516
1517 -- Lock Row
1518
1519 OPEN l_lock_row_list_csr (l_rowid_old);
1520 CLOSE l_lock_row_list_csr;
1521
1522 -- Execute UPDATE
1523
1524 l_cursor := dbms_sql.open_cursor;
1525 dbms_sql.parse(l_cursor, l_statement, dbms_sql.native);
1526
1527 IF (p_new_list_name <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1528 AND (nvl(p_new_list_name, PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) <>
1529 nvl(l_name_old,PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR))
1530 THEN
1531 DBMS_SQL.BIND_VARIABLE(l_cursor, ':xName',p_new_list_name );
1532 END IF;
1533
1534 IF (p_grouped_by_type <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1535 AND ( nvl(l_resource_type_id, PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) <>
1536 nvl(l_group_resource_type_id_old,PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM))
1537 THEN
1538 DBMS_SQL.BIND_VARIABLE(l_cursor, ':xGRTID',l_resource_type_id );
1539 END IF;
1540
1541 IF (p_description <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1542 AND (nvl(p_description, PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) <>
1543 nvl(l_description_old,PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR))
1544 THEN
1545 DBMS_SQL.BIND_VARIABLE(l_cursor, ':xDescription', p_description);
1546
1547 END IF;
1548
1549 l_rows := dbms_sql.execute(l_cursor);
1550 IF (dbms_sql.is_open(l_cursor) ) THEN
1551 dbms_sql.close_cursor(l_cursor);
1552 END IF;
1553 END IF;
1554 -- -----------------------------------------------------------------------
1555 -- Resource List MEMBERS
1556 -- -----------------------------------------------------------------------
1557
1558
1559 FOR I IN 1..p_member_tbl.COUNT LOOP
1560
1561 p_member_out_tbl(i).return_status := FND_API.G_RET_STS_SUCCESS;
1562
1563 IF (p_member_tbl(i).resource_list_member_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
1564 AND (p_member_tbl(i).resource_list_member_id IS NOT NULL) THEN
1565
1566 -- Validate Passed Resource_List_Member_id
1567
1568 Convert_alias_to_id
1569 (p_resource_list_id => l_resource_list_id
1570 ,p_alias => p_member_tbl(i).resource_alias
1571 ,p_resource_list_member_id => p_member_tbl(i).resource_list_member_id
1572 ,p_out_resource_list_member_id => l_resource_list_member_id
1573 ,p_return_status => l_return_status
1574 );
1575
1576 ELSE
1577 -- Find Resource_List_Member_Id with Passed Alias and List Id
1578
1579 l_resource_list_member_id := Fetch_Resource_list_member_id (p_resource_list_id => l_resource_list_id,
1580 p_alias => p_member_tbl(i).resource_alias );
1581
1582 END IF;
1583
1584
1585 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1586 p_member_out_tbl(i).return_status := l_return_status;
1587 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1588 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1589 p_member_out_tbl(i).return_status := l_return_status;
1590 RAISE FND_API.G_EXC_ERROR;
1591 END IF;
1592
1593
1594 IF (l_resource_list_member_id IS NULL) THEN
1595
1596 -- ADD NEW Resource List Member
1597
1598 Add_Resource_List_Member
1599 ( p_commit => FND_API.G_FALSE
1600 ,p_init_msg_list => FND_API.G_FALSE
1601 , p_api_version_number => l_version_add_member
1602 , p_resource_list_id => l_resource_list_id
1603 , p_resource_group_alias => p_member_tbl(i).resource_group_alias
1604 , p_resource_group_name => p_member_tbl(i).resource_group_name
1605 , p_resource_type_code => p_member_tbl(i).resource_type_code
1606 , p_resource_attr_value => p_member_tbl(i).resource_attr_value
1607 , p_resource_alias => p_member_tbl(i).resource_alias
1608 , p_sort_order => p_member_tbl(i).sort_order
1609 , p_enabled_flag => p_member_tbl(i).enabled_flag
1610 , p_resource_list_member_id =>
1611 p_member_out_tbl(i). resource_list_member_id
1612 , p_msg_count => p_msg_count
1613 , p_msg_data => p_msg_data
1614 , p_return_status => p_member_out_tbl(i).return_status
1615 );
1616
1617 IF (p_member_out_tbl(i).return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1618 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1619 ELSIF (p_member_out_tbl(i).return_status = FND_API.G_RET_STS_ERROR) THEN
1620
1621 RAISE FND_API.G_EXC_ERROR;
1622 END IF;
1623
1624
1625 ELSE
1626 -- UPDATE EXISTING Resource List Member
1627
1628 p_member_out_tbl(i). resource_list_member_id := l_resource_list_member_id;
1629
1630 Update_Resource_List_Member
1631 ( p_commit => FND_API.G_FALSE
1632 , p_init_msg_list => FND_API.G_FALSE
1633 , p_api_version_number => l_version_update_member
1634 , p_resource_list_id => l_resource_list_id
1635 , p_resource_list_member_id => l_resource_list_member_id
1636 , p_new_alias => p_member_tbl(i).new_alias
1637 , p_sort_order => p_member_tbl(i).sort_order
1638 , p_enabled_flag => p_member_tbl(i).enabled_flag
1639 , p_return_status => p_member_out_tbl(i).return_status
1640 , p_msg_count => p_msg_count
1641 , p_msg_data => p_msg_data
1642 );
1643
1644
1645 IF (p_member_out_tbl(i).return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1646 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1647 ELSIF (p_member_out_tbl(i).return_status = FND_API.G_RET_STS_ERROR) THEN
1648
1649 RAISE FND_API.G_EXC_ERROR;
1650 END IF;
1651
1652
1653 END IF;
1654 END LOOP;
1655
1656
1657
1658 IF FND_API.to_boolean( p_commit )
1659 THEN
1660 COMMIT;
1661 END IF;
1662
1663 EXCEPTION
1664
1665 WHEN FND_API.G_EXC_ERROR THEN
1666
1667 p_return_status := FND_API.G_RET_STS_ERROR ;
1668 ROLLBACK TO Update_Resource_List_Pub;
1669
1670 FND_MSG_PUB.Count_And_Get
1671 ( p_count => p_msg_count ,
1672 p_data => p_msg_data );
1673
1674 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1675
1676 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1677 ROLLBACK TO Update_Resource_List_Pub;
1678
1679 FND_MSG_PUB.Count_And_Get
1680 ( p_count => p_msg_count ,
1681 p_data => p_msg_data );
1682
1683 WHEN ROW_ALREADY_LOCKED THEN
1684
1685 p_return_status := FND_API.G_RET_STS_ERROR ;
1686 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1687 THEN
1688 FND_MESSAGE.SET_NAME('PA','PA_ROW_ALREADY_LOCKED');
1689 FND_MESSAGE.SET_TOKEN('ENTITY', 'RESOURCE_LIST');
1690 FND_MSG_PUB.ADD;
1691 END IF;
1692
1693 ROLLBACK TO Update_Resource_List_Mbr_Pub;
1694
1695 FND_MSG_PUB.Count_And_Get
1696 ( p_count => p_msg_count ,
1697 p_data => p_msg_data );
1698
1699
1700 WHEN OTHERS THEN
1701
1702 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1703 ROLLBACK TO Update_Resource_List_Pub;
1704
1705 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1706
1707 FND_MSG_PUB.Add_Exc_Msg
1708 ( p_pkg_name => G_PKG_NAME ,
1709 p_procedure_name => l_api_name
1710 );
1711
1712 END IF;
1713
1714 FND_MSG_PUB.Count_And_Get
1715 ( p_count => p_msg_count ,
1716 p_data => p_msg_data );
1717
1718 END Update_Resource_List;
1719 -- ===============================================================
1720 --
1721 -- Name: Init_Update_Members
1722 -- Type: PL/SQL Procedure
1723 -- Decscription: This procedure initlializes the resource list members globals for the
1724 -- Update_Resource_List load-execute-fetch cycle.
1725 --
1726 -- Called Subprograms:
1727 --
1728 -- History:
1729 -- xx-AUG-96 Created rkrishna
1730 -- 04-DEC-96 Update jwhite Applied latest standards.
1731 --
1732
1733
1734 PROCEDURE Init_Update_Members IS
1735
1736 l_api_name CONSTANT VARCHAR2(30) := 'Init_Update_Members';
1737
1738 BEGIN
1739
1740 g_member_tbl.DELETE;
1741 g_member_out_tbl.DELETE;
1742 g_update_member_tbl_count := 0;
1743 g_update_resource_list_id := 0;
1744
1745 EXCEPTION
1746
1747 WHEN OTHERS THEN
1748
1749 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1750 FND_MSG_PUB.Add_Exc_Msg
1751 ( p_pkg_name => G_PKG_NAME ,
1752 p_procedure_name => l_api_name
1753 );
1754 END IF;
1755
1756 END Init_Update_Members;
1757 -- ===============================================================
1758 --
1759 -- Name: Exec_Update_Resource_List
1760 -- Type: PL/SQL Procedure
1761 -- Decscription: This procedure executes the Update_Resource_List API.
1762 --
1763 -- Called Subprograms: Update_Resource_List
1764 --
1765 -- History:
1766 -- xx-AUG-96 Created rkrishna
1767 -- 04-DEC-96 Update jwhite Applied latest standards.
1768 --
1769
1770
1771 PROCEDURE Exec_Update_Resource_List
1772 (p_commit IN VARCHAR2 := FND_API.G_FALSE,
1773 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1774 p_api_version_number IN NUMBER,
1775 p_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1776 p_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1777 p_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1778 )
1779 IS
1780
1781 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
1782 l_api_name CONSTANT VARCHAR2(30) := 'Exec_Update_Resource_List';
1783 l_message_count NUMBER;
1784
1785 BEGIN
1786
1787 p_return_status := FND_API.g_ret_sts_success;
1788
1789 -- Standard Api compatibility call
1790
1791 IF NOT FND_API.Compatible_API_Call ( l_api_version_number ,
1792 p_api_version_number ,
1793 l_api_name ,
1794 G_PKG_NAME )
1795 THEN
1796 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1797 END IF;
1798
1799
1800
1801 Update_Resource_List
1802 (p_commit => p_commit,
1803 p_init_msg_list => p_init_msg_list,
1804 p_api_version_number => p_api_version_number,
1805 p_return_status => p_return_status,
1806 p_msg_count => p_msg_count,
1807 p_msg_data => p_msg_data,
1808 p_resource_list_id => g_resource_list_rec.resource_list_id,
1809 p_resource_list_name => g_resource_list_rec.resource_list_name,
1810 p_new_list_name => g_resource_list_rec.new_list_name,
1811 p_grouped_by_type => g_resource_list_rec.group_resource_type,
1812 p_description => g_resource_list_rec.description,
1813 p_start_date => g_resource_list_rec.start_date,
1814 p_end_date => g_resource_list_rec.end_date,
1815 p_member_tbl => g_member_tbl,
1816 p_member_out_tbl => g_member_out_tbl);
1817
1818 EXCEPTION
1819
1820 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1821 THEN
1822
1823 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1824
1825 WHEN OTHERS THEN
1826
1827 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1828
1829 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1830 FND_MSG_PUB.Add_Exc_Msg
1831 ( p_pkg_name => G_PKG_NAME ,
1832 p_procedure_name => l_api_name
1833 );
1834 END IF;
1835
1836 FND_MSG_PUB.Count_And_Get
1837 ( p_count => p_msg_count ,
1838 p_data => p_msg_data );
1839
1840 END Exec_Update_Resource_List;
1841 -- ===============================================================
1842
1843 --
1844 -- Name: Clear_Update_Members
1845 -- Type: PL/SQL Procedure
1846 -- Decscription: This procedure clears the resource list members globals for the
1847 -- Update_Resource_List load-execute-fetch cycle.
1848 --
1849 -- Called Subprograms: Init_Update_Members
1850 --
1851 -- History:
1852 -- xx-AUG-96 Created rkrishna
1853 -- 04-DEC-96 Update jwhite Applied latest standards.
1854 --
1855
1856 PROCEDURE Clear_Update_Members IS
1857 BEGIN
1858 Init_Update_Members;
1859 END Clear_Update_Members;
1860 -- ===============================================================
1861
1862 --
1863 -- Name: Delete_Resource_List
1864 -- Type: PL/SQL Procedure
1865 -- Decscription: This procedure deletes a resource list and its unclassified members.
1866 --
1867 -- Called Subprograms:
1868 --
1869 -- History:
1870 -- xx-AUG-96 Created rkrishna
1871 -- 04-DEC-96 Update jwhite Applied latest standards.
1872 -- 02-OCT-98 Updated jxnaraya validations for deletion modified
1873
1874
1875 PROCEDURE Delete_Resource_list
1876 ( p_commit IN VARCHAR2 := FND_API.G_FALSE,
1877 p_api_version_number IN NUMBER,
1878 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1879 p_resource_list_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
1880 p_resource_list_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
1881 p_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1882 p_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1883 p_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1884 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1885 x_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1886 )
1887 IS
1888 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
1889 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Resource_List';
1890 l_message_count NUMBER;
1891
1892 l_resource_list_id NUMBER := 0;
1893 l_return_status VARCHAR2(1);
1894 l_dummy VARCHAR2(1);
1895 l_resource_list_member_id NUMBER := 0;
1896 l_msg_count NUMBER ;
1897 l_msg_data VARCHAR2(2000);
1898 l_function_allowed VARCHAR2(1);
1899 l_resp_id NUMBER := 0;
1900
1901
1902 -- LOCK Row of Existing Resource List Before DELETE
1903
1904 CURSOR l_lock_row_list_csr (l_resource_list_id NUMBER)
1905 IS
1906 SELECT 'x'
1907 FROM pa_resource_lists rl
1908 WHERE rl.resource_list_id = l_resource_list_id
1909 FOR UPDATE NOWAIT;
1910
1911 BEGIN
1912
1913 SAVEPOINT Delete_Resource_List_Pub;
1914
1915 -- Standard Api compatibility call
1916
1917 IF NOT FND_API.Compatible_API_Call ( l_api_version_number ,
1918 p_api_version_number ,
1919 l_api_name ,
1920 G_PKG_NAME )
1921 THEN
1922 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1923 END IF;
1924 l_resp_id := FND_GLOBAL.Resp_id;
1925
1926 -- Actions performed using the APIs would be subject to
1927 -- function security. If the responsibility does not allow
1928 -- such functions to be executed, the API should not proceed further
1929 -- since the user does not have access to such functions
1930
1931
1932 PA_PM_FUNCTION_SECURITY_PUB.check_function_security
1933 (p_api_version_number => p_api_version_number,
1934 p_responsibility_id => l_resp_id,
1935 p_function_name => 'PA_PM_DELETE_RESOURCE_LIST',
1936 p_msg_count => l_msg_count,
1937 p_msg_data => l_msg_data,
1938 p_return_status => l_return_status,
1939 p_function_allowed => l_function_allowed );
1940
1941 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1942 THEN
1943 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1944
1945 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
1946 THEN
1947 RAISE FND_API.G_EXC_ERROR;
1948 END IF;
1949 IF l_function_allowed = 'N' THEN
1950 FND_MESSAGE.SET_NAME('PA','PA_FUNCTION_SECURITY_ENFORCED');
1951 FND_MSG_PUB.ADD;
1952 p_return_status := FND_API.g_ret_sts_error;
1953 RAISE FND_API.G_EXC_ERROR;
1954 END IF;
1955
1956 IF FND_API.to_boolean( p_init_msg_list )
1957 THEN
1958 FND_MSG_PUB.initialize;
1959 END IF;
1960
1961 p_return_status := FND_API.G_RET_STS_SUCCESS;
1962
1963
1964 -- VALUE LAYER ---------------------------------------------------------------
1965
1966 Convert_List_name_to_id
1967 ( p_resource_list_name => p_resource_list_name,
1968 p_resource_list_id => p_resource_list_id,
1969 p_out_resource_list_id => l_resource_list_id,
1970 p_return_status => l_return_status
1971 );
1972
1973 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1974 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1975 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1976 RAISE FND_API.G_EXC_ERROR;
1977 END IF;
1978
1979 -- VALIDATION LAYER ---------------------------------------------------------
1980
1981 x_err_code := 0;
1982
1983 PA_GET_RESOURCE.delete_resource_list_ok(
1984 l_resource_list_id => l_resource_list_id,
1985 x_err_code => x_err_code,
1986 x_err_stage => x_err_stage);
1987 IF x_err_code <> 0 THEN
1988 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1989 FND_MESSAGE.SET_NAME('PA', x_err_stage);
1990 FND_MSG_PUB.ADD;
1991 END IF;
1992 RAISE FND_API.G_EXC_ERROR;
1993 END IF;
1994
1995
1996 -- D E L E T E Resource List MEMBERS --------------------------------------------------------------
1997
1998 -- Actually, this ONLY deletes UN-classified members.
1999
2000 -- Row-Locking NOT needed as per Ramesh, 12-DEC-96, because the Unclassified resources cannot -- be locked by users.
2001
2002 BEGIN
2003
2004 DELETE
2005 pa_resource_list_members
2006 WHERE
2007 resource_list_id = l_resource_list_id;
2008
2009 EXCEPTION
2010
2011 WHEN OTHERS THEN
2012
2013 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2014 ROLLBACK TO Delete_Resource_List_Pub;
2015
2016 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2017 THEN
2018 FND_MESSAGE.SET_NAME('PA','PA_RL_MEMBER_DELETE_ERROR');
2019 FND_MSG_PUB.ADD;
2020 END IF;
2021
2022 FND_MSG_PUB.Count_And_Get
2023 ( p_count => p_msg_count ,
2024 p_data => p_msg_data );
2025
2026 END;
2027
2028
2029 -- D E L E T E RESOURCE LIST -------------------------------------------------------------------------
2030
2031 BEGIN
2032
2033 OPEN l_lock_row_list_csr (l_resource_list_id);
2034 CLOSE l_lock_row_list_csr;
2035
2036 DELETE
2037 pa_resource_lists
2038 WHERE
2039 resource_list_id = l_resource_list_id;
2040
2041 EXCEPTION
2042
2043 WHEN ROW_ALREADY_LOCKED THEN
2044
2045 p_return_status := FND_API.G_RET_STS_ERROR ;
2046 ROLLBACK TO Delete_Resource_List_Pub;
2047
2048 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2049 THEN
2050 FND_MESSAGE.SET_NAME('PA','PA_ROW_ALREADY_LOCKED');
2051 FND_MESSAGE.SET_TOKEN('ENTITY', 'RESOURCE_LIST');
2052 FND_MSG_PUB.ADD;
2053 END IF;
2054
2055 FND_MSG_PUB.Count_And_Get
2056 ( p_count => p_msg_count ,
2057 p_data => p_msg_data );
2058
2059 WHEN OTHERS THEN
2060
2061 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2062 ROLLBACK TO Delete_Resource_List_Pub;
2063
2064 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2065 THEN
2066 FND_MESSAGE.SET_NAME('PA','PA_RL_DELETE_ERROR');
2067 FND_MSG_PUB.ADD;
2068 END IF;
2069
2070 FND_MSG_PUB.Count_And_Get
2071 ( p_count => p_msg_count ,
2072 p_data => p_msg_data );
2073
2074 END;
2075
2076
2077 IF FND_API.to_boolean( p_commit )
2078 THEN
2079 COMMIT;
2080 END IF;
2081
2082 FND_MSG_PUB.Count_And_Get
2083 ( p_count => p_msg_count ,
2084 p_data => p_msg_data );
2085
2086
2087 EXCEPTION
2088
2089 WHEN FND_API.G_EXC_ERROR THEN
2090
2091 p_return_status := FND_API.G_RET_STS_ERROR ;
2092 ROLLBACK TO Delete_Resource_List_Pub;
2093
2094 FND_MSG_PUB.Count_And_Get
2095 ( p_count => p_msg_count ,
2096 p_data => p_msg_data );
2097
2098 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2099
2100 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2101 ROLLBACK TO Delete_Resource_List_Pub;
2102
2103 FND_MSG_PUB.Count_And_Get
2104 ( p_count => p_msg_count ,
2105 p_data => p_msg_data );
2106
2107 WHEN OTHERS THEN
2108
2109 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2110 ROLLBACK TO Delete_Resource_List_Pub;
2111
2112 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2113 FND_MSG_PUB.Add_Exc_Msg
2114 ( p_pkg_name => G_PKG_NAME ,
2115 p_procedure_name => l_api_name
2116 );
2117 END IF;
2118
2119 FND_MSG_PUB.Count_And_Get
2120 ( p_count => p_msg_count ,
2121 p_data => p_msg_data );
2122
2123
2124 END Delete_Resource_list;
2125 -- ===============================================================
2126
2127 --
2128 -- Name: Add_Resource_List_Member
2129 -- Type: PL/SQL Procedure
2130 -- Decscription: This procedure inserts a new member in the PA_RESOURCE_LIST_MEMBERS
2131 -- table.
2132 --
2133 -- Called Subprograms: Convert_List_name_to_id
2134 -- , Insert_Members
2135 --
2136 -- History:
2137 -- xx-AUG-96 Created rkrishna
2138 -- 04-DEC-96 Update jwhite Applied latest standards.
2139 --
2140
2141 PROCEDURE Add_Resource_List_Member
2142 (p_commit IN VARCHAR2 := FND_API.G_FALSE,
2143 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2144 p_api_version_number IN NUMBER,
2145 p_resource_list_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
2146 p_resource_list_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2147 p_resource_group_alias IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
2148 p_resource_group_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
2149 p_resource_type_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
2150 p_resource_attr_value IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
2151 p_resource_alias IN VARCHAR2 ,
2152 p_sort_order IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2153 p_enabled_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
2154 p_resource_list_member_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2155 p_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2156 p_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2157 p_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2158 )
2159 IS
2160
2161 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
2162 l_api_name CONSTANT VARCHAR2(30):=
2163 'Add_resource_list_member';
2164 l_value_conversion_error BOOLEAN := FALSE;
2165 l_return_status VARCHAR2(1);
2166 l_resource_list_id NUMBER;
2167 l_resource_list_member_id NUMBER;
2168 l_parent_member_id NUMBER;
2169 l_index NUMBER;
2170 l_err_code NUMBER := 0;
2171 l_err_stage VARCHAR2(2000);
2172 l_err_stack VARCHAR2(2000);
2173 l_person_id NUMBER;
2174 l_job_id NUMBER;
2175 l_proj_organization_id NUMBER;
2176 l_vendor_id NUMBER;
2177 l_expenditure_type VARCHAR2(30);
2178 l_event_type VARCHAR2(30);
2179 l_expenditure_category VARCHAR2(30);
2180 l_revenue_category_code VARCHAR2(30);
2181 l_track_as_labor_flag VARCHAR2(1);
2182 l_group_resource_type VARCHAR2(30);
2183 l_resource_group_name VARCHAR2(80);
2184 l_resource_group_alias VARCHAR2(30);
2185 l_group_resource_type_id NUMBER;
2186 l_sort_order NUMBER;
2187 l_enabled_flag pa_resource_list_members.enabled_flag%TYPE;
2188 l_msg_count NUMBER ;
2189 l_msg_data VARCHAR2(2000);
2190 l_function_allowed VARCHAR2(1);
2191 l_resp_id NUMBER := 0;
2192 l_job_group_id NUMBER; -- Added for the bug 2486405.
2193
2194
2195
2196 CURSOR l_resource_list_csr IS
2197 SELECT rl.group_resource_type_id,
2198 rg.resource_group,
2199 rl.job_group_id -- Added for the bug 2486405.
2200 FROM
2201 pa_resource_lists rl,pa_resource_groups_valid_v rg
2202 WHERE rl.resource_list_id = l_resource_list_id
2203 AND rl.group_resource_type_id = rg.group_resource_type_id;
2204
2205 BEGIN
2206
2207
2208 SAVEPOINT Add_Resource_List_Member_Pub;
2209
2210 p_return_status := FND_API.G_RET_STS_SUCCESS;
2211
2212 IF FND_API.to_boolean( p_init_msg_list)
2213 THEN
2214 FND_MSG_PUB.initialize;
2215 END IF;
2216
2217 l_resp_id := FND_GLOBAL.Resp_id;
2218
2219 -- Actions performed using the APIs would be subject to
2220 -- function security. If the responsibility does not allow
2221 -- such functions to be executed, the API should not proceed further
2222 -- since the user does not have access to such functions
2223
2224
2225 PA_PM_FUNCTION_SECURITY_PUB.check_function_security
2226 (p_api_version_number => p_api_version_number,
2227 p_responsibility_id => l_resp_id,
2228 p_function_name => 'PA_PM_ADD_RESOURCE_LIST_MEMBER',
2229 p_msg_count => l_msg_count,
2230 p_msg_data => l_msg_data,
2231 p_return_status => l_return_status,
2232 p_function_allowed => l_function_allowed );
2233
2234 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2235 THEN
2236 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2237
2238 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
2239 THEN
2240 RAISE FND_API.G_EXC_ERROR;
2241 END IF;
2242 IF l_function_allowed = 'N' THEN
2243 FND_MESSAGE.SET_NAME('PA','PA_FUNCTION_SECURITY_ENFORCED');
2244 FND_MSG_PUB.ADD;
2245 p_return_status := FND_API.g_ret_sts_error;
2246 RAISE FND_API.G_EXC_ERROR;
2247 END IF;
2248
2249 -- Standard call to check for call compatibility.
2250 IF NOT FND_API.Compatible_API_Call ( l_api_version_number ,
2251 p_api_version_number ,
2252 l_api_name ,
2253 G_PKG_NAME )
2254 THEN
2255 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2256 END IF;
2257
2258
2259 IF p_resource_type_code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
2260 p_resource_list_member_id := NULL;
2261 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2262 THEN
2263 FND_MESSAGE.SET_NAME('PA','PA_RL_RES_TYPE_CODE_REQD');
2264 FND_MSG_PUB.ADD;
2265 END IF;
2266 RAISE FND_API.G_EXC_ERROR;
2267 END IF;
2268
2269 IF p_resource_attr_value = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
2270 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2271 THEN
2272 IF p_resource_type_code = 'EMPLOYEE' THEN
2273 FND_MESSAGE.SET_NAME('PA', 'PA_NO_PERSON_ID');
2274 ELSIF p_resource_type_code = 'JOB' THEN
2275 FND_MESSAGE.SET_NAME('PA', 'PA_NO_JOB_ID');
2276 ELSIF p_resource_type_code = 'ORGANIZATION' THEN
2277 FND_MESSAGE.SET_NAME('PA', 'PA_NO_PROJ_ORG_ID');
2278 ELSIF p_resource_type_code = 'VENDOR' THEN
2279 FND_MESSAGE.SET_NAME('PA', 'PA_NO_VENDOR_ID');
2280 ELSIF p_resource_type_code = 'EXPENDITURE_TYPE' THEN
2281 FND_MESSAGE.SET_NAME('PA', 'PA_NO_EXPENDITURE_TYPE');
2282 ELSIF p_resource_type_code = 'EVENT_TYPE' THEN
2283 FND_MESSAGE.SET_NAME('PA', 'PA_NO_EVENT_TYPE');
2284 ELSIF p_resource_type_code = 'EXPENDITURE_CATEGORY' THEN
2285 FND_MESSAGE.SET_NAME('PA', 'PA_NO_EXPENDITURE_CATEGORY');
2286 ELSIF p_resource_type_code = 'REVENUE_CATEGORY' THEN
2287 FND_MESSAGE.SET_NAME('PA', 'REVENUE_CATEGORY');
2288 END IF;
2289 p_resource_list_member_id := NULL;
2290 FND_MSG_PUB.ADD;
2291 END IF;
2292 RAISE FND_API.G_EXC_ERROR;
2293 END IF;
2294
2295 -- Convert the resource list name to resource list id
2296
2297 Convert_List_name_to_id
2298 (
2299 p_resource_list_name => p_resource_list_name,
2300 p_resource_list_id => p_resource_list_id,
2301 p_out_resource_list_id => l_resource_list_id,
2302 p_return_status => l_return_status
2303 );
2304
2305 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2306 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2307 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2308 RAISE FND_API.G_EXC_ERROR;
2309 END IF;
2310
2311
2312 -- Get the grouped by resource type of the Resource list
2313 OPEN l_resource_list_csr;
2314 FETCH l_resource_list_csr INTO
2315 l_group_resource_type_id,
2316 l_group_resource_type,
2317 l_job_group_id; -- Added for the bug 2486405.
2318 IF l_resource_list_csr%NOTFOUND THEN
2319 CLOSE l_resource_list_csr;
2320 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2321 THEN
2322 FND_MESSAGE.SET_NAME('PA', 'PA_RL_INVALID');
2323 FND_MSG_PUB.ADD;
2324 END IF;
2325 RAISE FND_API.G_EXC_ERROR;
2326 ELSE
2327 CLOSE l_resource_list_csr;
2328 END IF;
2329
2330 IF l_group_resource_type_id <> 0 AND
2331 (p_resource_group_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR AND
2332 p_resource_group_alias = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR ) THEN
2333 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2334 THEN
2335 FND_MESSAGE.SET_NAME('PA', 'PA_RL_GROUPED');
2336 FND_MSG_PUB.ADD;
2337 END IF;
2338 RAISE FND_API.G_EXC_ERROR;
2339 END IF;
2340
2341 IF (p_sort_order = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
2342 l_sort_order := NULL;
2343 ELSE
2344 l_sort_order := p_sort_order;
2345 END IF;
2346
2347 IF (p_enabled_flag = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) THEN
2348 l_enabled_flag := 'Y';
2349 ELSE
2350 l_enabled_flag := p_enabled_flag;
2351 END IF;
2352
2353
2354 l_resource_group_name := p_resource_group_name;
2355 l_resource_group_alias := p_resource_group_alias;
2356
2357 IF l_group_resource_type_id <> 0 THEN
2358 IF p_resource_group_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
2359 l_resource_group_name := p_resource_group_alias;
2360 ELSIF
2361 p_resource_group_alias = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
2362 l_resource_group_alias := SUBSTR(p_resource_group_name,1,30);
2363 END IF;
2364 END IF;
2365
2366 l_parent_member_id := NULL;
2367 l_resource_list_member_id := NULL;
2368 l_track_as_labor_flag := NULL;
2369 l_err_code := NULL;
2370 l_err_stack := NULL;
2371 l_err_stage := NULL;
2372
2373 /* Bug 2259703 Changed the values passed to following two parameters
2374 p_resource_group_alias => p_resource_group_alias,
2375 p_resource_group_name => p_resource_group_alias,
2376 */
2377 Insert_Members (
2378 p_resource_list_id => l_resource_list_id,
2379 p_group_resource_type => l_group_resource_type,
2380 p_resource_type_code => p_resource_type_code,
2381 p_resource_group_alias => l_resource_group_alias,
2382 p_resource_group_name => l_resource_group_name,
2383 p_resource_alias => p_resource_alias,
2384 p_sort_order => l_sort_order,
2385 p_enabled_flag => l_enabled_flag,
2386 p_resource_attr_value => p_resource_attr_value,
2387 p_job_group_id => l_job_group_id, --Added for bug 2486405.
2388 p_parent_member_id => l_parent_member_id,
2389 p_resource_list_member_id => l_resource_list_member_id,
2390 p_track_as_labor_flag => l_track_as_labor_flag,
2391 p_err_code => l_err_code,
2392 p_err_stage => l_err_stage,
2393 p_err_stack => l_err_stack,
2394 p_return_status => l_return_status );
2395
2396 IF l_err_code > 0 THEN
2397 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2398 THEN
2399 IF NOT pa_project_pvt.check_valid_message (l_err_stage) THEN
2400 FND_MESSAGE.SET_NAME ('PA','PA_ERR_IN_RL_MEMB_CREATION');
2401 ELSE
2402 FND_MESSAGE.SET_NAME ('PA',l_err_stage);
2403 END IF;
2404 FND_MSG_PUB.ADD;
2405 END IF;
2406 RAISE FND_API.G_EXC_ERROR;
2407 END IF;
2408
2409 IF l_err_code < 0 THEN
2410 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2411 THEN
2412 FND_MSG_PUB.Add_Exc_Msg
2413 ( p_pkg_name => 'G_PKG_NAME'
2414 , p_procedure_name => 'ADD_RESOURCE_LIST_MEMBER'
2415 , p_error_text => 'ORA-'||LPAD(SUBSTR(l_err_code, 2), 5, '0')
2416 );
2417
2418 END IF;
2419 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2420 END IF;
2421
2422 IF l_err_code = 0 THEN
2423 p_return_status := FND_API.g_ret_sts_success;
2424 p_resource_list_member_id := l_resource_list_member_id;
2425 END IF;
2426
2427 IF FND_API.to_boolean( p_commit )
2428 THEN
2429 COMMIT;
2430 END IF;
2431
2432 EXCEPTION
2433
2434 WHEN FND_API.G_EXC_ERROR THEN
2435
2436 p_return_status := FND_API.G_RET_STS_ERROR ;
2437 ROLLBACK TO Add_Resource_List_Member_Pub;
2438
2439 FND_MSG_PUB.Count_And_Get
2440 ( p_count => p_msg_count ,
2441 p_data => p_msg_data );
2442
2443 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2444
2445 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2446 ROLLBACK TO Add_Resource_List_Member_Pub;
2447
2448 FND_MSG_PUB.Count_And_Get
2449 ( p_count => p_msg_count ,
2450 p_data => p_msg_data );
2451
2452 WHEN OTHERS THEN
2453
2454 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2455 ROLLBACK TO Add_Resource_List_Member_Pub;
2456
2457 IF FND_MSG_PUB.Check_Msg_Level
2458 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2459
2460 FND_MSG_PUB.Add_Exc_Msg
2461 ( p_pkg_name => G_PKG_NAME ,
2462 p_procedure_name => l_api_name
2463 );
2464
2465 END IF;
2466
2467 FND_MSG_PUB.Count_And_Get
2468 ( p_count => p_msg_count ,
2469 p_data => p_msg_data );
2470
2471 END Add_Resource_List_Member;
2472
2473 -- ================================================================
2474
2475 --
2476 -- Name: Update_Resource_List_Member
2477 -- Type: PL/SQL Procedure
2478 -- Decscription: This procedure updates an existing member on the PA_RESOURCE_LIST_MEMBERS
2479 -- table.
2480 --
2481 -- Called Subprograms: Convert_List_name_to_id
2482 -- , Convert_alias_to_id
2483 --
2484 -- History:
2485 -- xx-AUG-96 Created rkrishna
2486 -- 04-DEC-96 Update jwhite Applied latest standards.
2487 --
2488
2489 PROCEDURE Update_Resource_List_Member
2490 ( p_commit IN VARCHAR2 := FND_API.G_FALSE,
2491 p_api_version_number IN NUMBER,
2492 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2493 p_resource_list_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
2494 p_resource_list_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2495 p_resource_alias IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
2496 p_resource_list_member_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2497 p_new_alias IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
2498 p_sort_order IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2499 p_enabled_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
2500 p_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2501 p_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2502 p_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2503 )
2504 IS
2505
2506 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
2507 l_api_name CONSTANT VARCHAR2(30) := 'Update_Resource_List_Member';
2508 l_message_count NUMBER;
2509
2510 l_resource_list_id NUMBER := 0;
2511 l_return_status VARCHAR2(1);
2512 l_dummy VARCHAR2(1);
2513 l_cursor INTEGER;
2514 l_rows NUMBER := 0;
2515 l_statement VARCHAR2(2000);
2516 l_update_member_flag VARCHAR2(1);
2517 l_resource_list_member_id NUMBER := 0;
2518 l_group_resource_type_id NUMBER := -1;
2519 l_parent_member_id NUMBER := 0;
2520
2521 l_rowid_old VARCHAR2(20) := NULL;
2522 l_alias_old pa_resource_list_members.alias%TYPE;
2523 l_sort_order_old NUMBER := 0;
2524 l_enabled_flag_new pa_resource_list_members.enabled_flag%TYPE;
2525 l_enabled_flag_old pa_resource_list_members.enabled_flag%TYPE;
2526 l_msg_count NUMBER ;
2527 l_msg_data VARCHAR2(2000);
2528 l_function_allowed VARCHAR2(1);
2529 l_resp_id NUMBER := 0;
2530
2531
2532 -- Get the Group_Resource_Type_Id for the Resource List
2533
2534 CURSOR l_group_resource_type_csr (l_resource_list_id NUMBER)
2535 IS
2536 SELECT rl.group_resource_type_id
2537 FROM pa_resource_lists rl
2538 WHERE rl.resource_list_id = l_resource_list_id;
2539
2540 -- Find New Alias for a Non-Grouped Resource List
2541
2542 CURSOR l_new_alias_none_csr (p_new_alias VARCHAR2
2543 , l_resource_list_id NUMBER)
2544 IS
2545 SELECT 'x'
2546 FROM pa_resource_list_members rlm
2547 WHERE rlm.resource_list_id = l_resource_list_id
2548 AND rlm.alias = p_new_alias;
2549
2550 -- Find Parent Member Id of Resource List Member Id
2551
2552 CURSOR l_parent_member_csr (l_resource_list_member_id NUMBER)
2553 IS
2554 SELECT rlm.parent_member_id
2555 FROM pa_resource_list_members rlm
2556 WHERE rlm.resource_list_member_id = l_resource_list_member_id;
2557
2558 -- Find New Alias within the Group of the Resource List Member Id
2559
2560 CURSOR l_new_alias_grouped_csr (p_new_alias VARCHAR2
2561 , l_parent_member_id NUMBER)
2562 IS
2563 SELECT 'x'
2564 FROM pa_resource_list_members rlm
2565 WHERE rlm.parent_member_id = l_parent_member_id
2566 AND rlm.alias = p_new_alias;
2567
2568 -- Find Original Updatable Columns on PA_RESOURCE_LIST_MEMBERS
2569
2570 CURSOR l_orignal_columns_csr (l_resource_list_member_id NUMBER)
2571 IS
2572 SELECT rlm.alias, rlm.sort_order, rlm.enabled_flag, ROWID
2573 FROM pa_resource_list_members rlm
2574 WHERE rlm.resource_list_member_id = l_resource_list_member_id;
2575
2576 -- Lock Row of Existing Resource List Member Before Update
2577
2578 CURSOR l_lock_row_member_csr (l_rowid_old VARCHAR2)
2579 IS
2580 SELECT 'x'
2581 FROM pa_resource_list_members
2582 WHERE ROWID = l_rowid_old
2583 FOR UPDATE NOWAIT;
2584
2585
2586
2587 BEGIN
2588
2589 -- Standard Api compatibility call
2590
2591 SAVEPOINT Update_Resource_List_Mbr_Pub;
2592
2593 p_return_status := FND_API.G_RET_STS_SUCCESS;
2594
2595 IF NOT FND_API.Compatible_API_Call ( l_api_version_number ,
2596 p_api_version_number ,
2597 l_api_name ,
2598 G_PKG_NAME )
2599 THEN
2600 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2601 END IF;
2602
2603 l_resp_id := FND_GLOBAL.Resp_id;
2604
2605 -- Actions performed using the APIs would be subject to
2606 -- function security. If the responsibility does not allow
2607 -- such functions to be executed, the API should not proceed further
2608 -- since the user does not have access to such functions
2609
2610
2611 PA_PM_FUNCTION_SECURITY_PUB.check_function_security
2612 (p_api_version_number => p_api_version_number,
2613 p_responsibility_id => l_resp_id,
2614 p_function_name => 'PA_PM_UPD_RESOURCE_LIST_MEMBER',
2615 p_msg_count => l_msg_count,
2616 p_msg_data => l_msg_data,
2617 p_return_status => l_return_status,
2618 p_function_allowed => l_function_allowed );
2619
2620 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2621 THEN
2622 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2623
2624 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
2625 THEN
2626 RAISE FND_API.G_EXC_ERROR;
2627 END IF;
2628 IF l_function_allowed = 'N' THEN
2629 FND_MESSAGE.SET_NAME('PA','PA_FUNCTION_SECURITY_ENFORCED');
2630 FND_MSG_PUB.ADD;
2631 p_return_status := FND_API.g_ret_sts_error;
2632 RAISE FND_API.G_EXC_ERROR;
2633 END IF;
2634
2635 IF FND_API.to_boolean( p_init_msg_list )
2636 THEN
2637 FND_MSG_PUB.initialize;
2638 END IF;
2639
2640 -- VALUE LAYER ----------------------------------------------------------------
2641
2642 Convert_List_name_to_id
2643 ( p_resource_list_name => p_resource_list_name,
2644 p_resource_list_id => p_resource_list_id,
2645 p_out_resource_list_id => l_resource_list_id,
2646 p_return_status => l_return_status
2647 );
2648
2649 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2650 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2651 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2652 RAISE FND_API.G_EXC_ERROR;
2653 END IF;
2654
2655 Convert_alias_to_id
2656 ( p_resource_list_id => l_resource_list_id
2657 , p_alias => p_resource_alias
2658 , p_resource_list_member_id => p_resource_list_member_id
2659 , p_out_resource_list_member_id => l_resource_list_member_id
2660 , p_return_status => l_return_status
2661 );
2662
2663 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2664 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2665 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2666 RAISE FND_API.G_EXC_ERROR;
2667 END IF;
2668
2669 -- VALIDATE LAYER ------------------------------------------------------------------------
2670
2671 -- Passed Column-Related Parameters Cannot Be Null
2672
2673 IF (p_new_alias IS NULL) THEN
2674 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2675 THEN
2676 FND_MESSAGE.SET_NAME('PA','PA_P_NEW_ALIAS_NO_NULL');
2677 FND_MSG_PUB.ADD;
2678 END IF;
2679 RAISE FND_API.G_EXC_ERROR;
2680 END IF;
2681
2682 IF (p_sort_order IS NULL) THEN
2683 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2684 THEN
2685 FND_MESSAGE.SET_NAME('PA','PA_P_SORT_ORDER_NO_NULL');
2686 FND_MSG_PUB.ADD;
2687 END IF;
2688 RAISE FND_API.G_EXC_ERROR;
2689 END IF;
2690
2691 IF (p_enabled_flag IS NULL) THEN
2692 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2693 THEN
2694 FND_MESSAGE.SET_NAME('PA','PA_P_ENABLED_FLAG_NO_NULL');
2695 FND_MSG_PUB.ADD;
2696 END IF;
2697 RAISE FND_API.G_EXC_ERROR;
2698 END IF;
2699
2700 -- Check Proper Values Passed for P_ENABLED_FLAG.
2701 -- Default Value to 'Y' if NOT Passed at All.
2702
2703 IF (p_enabled_flag = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) THEN
2704 l_enabled_flag_new := 'Y';
2705 ELSE
2706 IF (p_enabled_flag IN ('Y', 'N')) THEN
2707 l_enabled_flag_new := p_enabled_flag;
2708 ELSE
2709 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2710 THEN
2711 FND_MESSAGE.SET_NAME('PA','PA_ENABLED_FLAG_YES_NO');
2712 FND_MSG_PUB.ADD;
2713 END IF;
2714 RAISE FND_API.G_EXC_ERROR;
2715 END IF;
2716 END IF;
2717
2718 -- Check Uniqueness of New Alias
2719 --
2720 -- If a resource list is not grouped, then the new alias must be unique accross the list.
2721 -- Otherwise, a new alias must be unique within the group of the original alias.
2722
2723 IF (p_new_alias <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) THEN
2724
2725 OPEN l_group_resource_type_csr (l_resource_list_id);
2726 FETCH l_group_resource_type_csr INTO l_group_resource_type_id;
2727 CLOSE l_group_resource_type_csr;
2728
2729 IF (l_group_resource_type_id = 0) THEN
2730 -- Resource List is NOT Grouped. So, new alias must be unique within the entire list.
2731
2732 OPEN l_new_alias_none_csr (p_new_alias , l_resource_list_id );
2733 FETCH l_new_alias_none_csr INTO l_dummy;
2734 IF (l_new_alias_none_csr%FOUND) THEN
2735 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2736 THEN
2737 FND_MESSAGE.SET_NAME('PA','PA_P_NEW_ALIAS_NOT_UNIQUE ');
2738 FND_MSG_PUB.ADD;
2739 END IF;
2740 CLOSE l_new_alias_none_csr;
2741 RAISE FND_API.G_EXC_ERROR;
2742 ELSE
2743 CLOSE l_new_alias_none_csr;
2744 END IF;
2745
2746 ELSE
2747 -- Resource List IS Grouped. So, new alias must be unique within the group of the resource_list_member_id
2748
2749 -- Get Parent_Member_Id (group rlm id) of Resource_List_Member_Id
2750
2751 OPEN l_parent_member_csr (l_resource_list_member_id );
2752 FETCH l_parent_member_csr INTO l_parent_member_id;
2753 CLOSE l_parent_member_csr;
2754
2755 -- Check for Uniqueness within Parent Group of Resource List Member Id.
2756
2757 OPEN l_new_alias_grouped_csr (p_new_alias , l_parent_member_id );
2758
2759 FETCH l_new_alias_grouped_csr INTO l_dummy;
2760 IF (l_new_alias_grouped_csr%FOUND) THEN
2761 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2762 THEN
2763 FND_MESSAGE.SET_NAME('PA','PA_P_NEW_ALIAS_NOT_UNIQUE ');
2764 FND_MSG_PUB.ADD;
2765 END IF;
2766 CLOSE l_new_alias_grouped_csr;
2767 RAISE FND_API.G_EXC_ERROR;
2768 ELSE
2769 CLOSE l_new_alias_grouped_csr;
2770 END IF;
2771 END IF;
2772 END IF;
2773
2774 -- BUILD UPDATE SQL STATEMENT for Resource List Member ---------------------------
2775
2776 -- Get Original Updatable Columns for Resource List Member Id
2777
2778 OPEN l_orignal_columns_csr (l_resource_list_member_id);
2779 FETCH l_orignal_columns_csr INTO l_alias_old, l_sort_order_old, l_enabled_flag_old, l_rowid_old;
2780 CLOSE l_orignal_columns_csr;
2781
2782
2783 l_update_member_flag := 'N';
2784 l_statement := 'UPDATE PA_RESOURCE_LIST_MEMBERS SET ';
2785
2786 IF (p_new_alias <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
2787 AND (NVL(p_new_alias, PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) <> NVL(l_alias_old, PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR))
2788 THEN
2789 l_statement := l_statement || 'ALIAS = '||''''||p_new_alias||''''||',';
2790 l_update_member_flag := 'Y';
2791 END IF;
2792
2793 IF (p_sort_order <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
2794 AND (NVL(p_sort_order, PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) <> NVL(l_sort_order_old, PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM))
2795 THEN
2796 l_statement := l_statement || 'SORT_ORDER = '||''''||p_sort_order||''''||',';
2797 l_update_member_flag := 'Y';
2798 END IF;
2799
2800 IF (l_enabled_flag_new <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
2801 AND (NVL(l_enabled_flag_new, PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) <> NVL(l_enabled_flag_old, PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR))
2802 THEN
2803 l_statement := l_statement || 'ENABLED_FLAG = '||''''|| l_enabled_flag_new ||''''||',';
2804 l_update_member_flag := 'Y';
2805 END IF;
2806
2807 IF (l_update_member_flag = 'Y') THEN
2808
2809 l_statement := l_statement ||'LAST_UPDATE_DATE = '||''''||TO_CHAR(g_last_update_date)||''''||',';
2810 l_statement := l_statement ||'LAST_UPDATED_BY = '||''''||TO_CHAR(g_last_updated_by)||''''||',';
2811 l_statement := l_statement ||'LAST_UPDATE_LOGIN =
2812 '||''''||TO_CHAR(g_last_update_login)||'''';
2813
2814 l_statement := l_statement || ' WHERE RESOURCE_LIST_MEMBER_ID = '|| TO_CHAR(l_resource_list_member_id);
2815
2816 -- UPDATE Resource List Member ---------------------------------------------
2817
2818 -- Lock Row
2819 OPEN l_lock_row_member_csr (l_rowid_old);
2820 CLOSE l_lock_row_member_csr;
2821
2822 -- Execute Update
2823
2824 l_cursor := dbms_sql.open_cursor;
2825 dbms_sql.parse(l_cursor, l_statement, dbms_sql.native);
2826 l_rows := dbms_sql.EXECUTE(l_cursor);
2827 IF (dbms_sql.is_open(l_cursor) ) THEN
2828 dbms_sql.close_cursor(l_cursor);
2829 END IF;
2830 END IF;
2831
2832 IF FND_API.to_boolean( p_commit )
2833 THEN
2834 COMMIT;
2835 END IF;
2836
2837 EXCEPTION
2838
2839 WHEN FND_API.G_EXC_ERROR THEN
2840
2841 p_return_status := FND_API.G_RET_STS_ERROR ;
2842 ROLLBACK TO Update_Resource_List_Mbr_Pub;
2843
2844 FND_MSG_PUB.Count_And_Get
2845 ( p_count => p_msg_count ,
2846 p_data => p_msg_data );
2847
2848 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2849
2850 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2851 ROLLBACK TO Update_Resource_List_Mbr_Pub;
2852
2853 FND_MSG_PUB.Count_And_Get
2854 ( p_count => p_msg_count ,
2855 p_data => p_msg_data );
2856
2857 WHEN ROW_ALREADY_LOCKED THEN
2858
2859 p_return_status := FND_API.G_RET_STS_ERROR ;
2860 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2861 THEN
2862 FND_MESSAGE.SET_NAME('PA','PA_ROW_ALREADY_LOCKED');
2863 FND_MESSAGE.SET_TOKEN('ENTITY', 'RESOURCE_LIST_MEMBER');
2864 FND_MSG_PUB.ADD;
2865 END IF;
2866
2867 ROLLBACK TO Update_Resource_List_Mbr_Pub;
2868
2869 FND_MSG_PUB.Count_And_Get
2870 ( p_count => p_msg_count ,
2871 p_data => p_msg_data );
2872
2873
2874 WHEN OTHERS THEN
2875
2876 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2877 ROLLBACK TO Update_Resource_List_Mbr_Pub;
2878
2879 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2880 FND_MSG_PUB.Add_Exc_Msg
2881 ( p_pkg_name => G_PKG_NAME ,
2882 p_procedure_name => l_api_name
2883 );
2884 END IF;
2885
2886 FND_MSG_PUB.Count_And_Get
2887 ( p_count => p_msg_count ,
2888 p_data => p_msg_data );
2889
2890 END Update_Resource_List_Member;
2891 -- ==============================================================
2892
2893 --
2894 -- Name: Delete_Resource_list_Member
2895 -- Type: PL/SQL Procedure
2896 -- Decscription: This procedures deletes a given resource list member id.
2897 --
2898 -- Called Subprograms:
2899 --
2900 -- History:
2901 -- xx-AUG-96 Created rkrishna
2902 -- 04-DEC-96 Update jwhite Applied latest standards.
2903 -- 02-OCT-98 Update jxnaraya validations for deletion modified
2904
2905 PROCEDURE Delete_Resource_list_Member
2906 ( p_commit IN VARCHAR2 := FND_API.G_FALSE,
2907 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2908 p_api_version_number IN NUMBER,
2909 p_resource_list_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
2910 p_resource_list_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2911 p_resource_alias IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
2912 p_resource_list_member_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2913 p_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2914 p_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2915 p_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2916 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2917 x_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2918 )
2919
2920 IS
2921
2922 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
2923 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Resource_List_Member';
2924 l_message_count NUMBER;
2925 l_resource_list_id NUMBER := 0;
2926 l_return_status VARCHAR2(1);
2927 l_dummy VARCHAR2(1);
2928 l_resource_list_member_id NUMBER := 0;
2929 l_msg_count NUMBER ;
2930 l_msg_data VARCHAR2(2000);
2931 l_function_allowed VARCHAR2(1);
2932 l_resp_id NUMBER := 0;
2933 l_parent_member_id NUMBER ;
2934 l_unclassified_list_member_id NUMBER :=0;
2935
2936
2937 -- LOCK Row of Existing Resource List Member Before DELETE
2938
2939 CURSOR l_lock_row_member_csr (l_resource_list_member_id NUMBER)
2940 IS
2941 SELECT 'x'
2942 FROM pa_resource_list_members rlm
2943 WHERE rlm.resource_list_member_id = l_resource_list_member_id
2944 FOR UPDATE NOWAIT;
2945
2946 /* Changes done for bug 1889671 Resource Mapping Enhancements */
2947
2948 CURSOR Cur_Unclassified_Parent_ID(X_resource_list_member_id pa_resource_list_members.resource_list_member_id%TYPE) IS
2949 SELECT parent_member_id
2950 FROM pa_resource_list_members
2951 WHERE resource_list_member_id = x_resource_list_member_id;
2952
2953 CURSOR Cur_Unclassified_member(x_parent_member_id pa_resource_list_members.parent_member_id%TYPE) IS
2954 SELECT resource_list_member_id
2955 FROM pa_resource_list_members
2956 WHERE parent_member_id =x_parent_member_id
2957 AND resource_type_code ='UNCLASSIFIED';
2958
2959 BEGIN
2960 SAVEPOINT Delete_Resource_List_Mbr_Pub;
2961
2962 -- Standard Api compatibility call
2963
2964 IF NOT FND_API.Compatible_API_Call ( l_api_version_number ,
2965 p_api_version_number ,
2966 l_api_name ,
2967 G_PKG_NAME )
2968 THEN
2969 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2970 END IF;
2971
2972 l_resp_id := FND_GLOBAL.Resp_id;
2973
2974 -- Actions performed using the APIs would be subject to
2975 -- function security. If the responsibility does not allow
2976 -- such functions to be executed, the API should not proceed further
2977 -- since the user does not have access to such functions
2978
2979
2980 PA_PM_FUNCTION_SECURITY_PUB.check_function_security
2981 (p_api_version_number => p_api_version_number,
2982 p_responsibility_id => l_resp_id,
2983 p_function_name => 'PA_PM_DEL_RESOURCE_LIST_MEMBER',
2984 p_msg_count => l_msg_count,
2985 p_msg_data => l_msg_data,
2986 p_return_status => l_return_status,
2987 p_function_allowed => l_function_allowed );
2988
2989 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2990 THEN
2991 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2992
2993 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
2994 THEN
2995 RAISE FND_API.G_EXC_ERROR;
2996 END IF;
2997 IF l_function_allowed = 'N' THEN
2998 FND_MESSAGE.SET_NAME('PA','PA_FUNCTION_SECURITY_ENFORCED');
2999 FND_MSG_PUB.ADD;
3000 p_return_status := FND_API.g_ret_sts_error;
3001 RAISE FND_API.G_EXC_ERROR;
3002 END IF;
3003 IF FND_API.to_boolean( p_init_msg_list )
3004 THEN
3005 FND_MSG_PUB.initialize;
3006 END IF;
3007
3008 p_return_status := FND_API.G_RET_STS_SUCCESS;
3009
3010
3011 -- VALUE LAYER:
3012
3013 Convert_List_name_to_id
3014 ( p_resource_list_name => p_resource_list_name,
3015 p_resource_list_id => p_resource_list_id,
3016 p_out_resource_list_id => l_resource_list_id,
3017 p_return_status => l_return_status
3018 );
3019
3020 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3021 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3022 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3023 RAISE FND_API.G_EXC_ERROR;
3024 END IF;
3025
3026 Convert_alias_to_id
3027 ( p_resource_list_id => l_resource_list_id
3028 ,p_alias => p_resource_alias
3029 ,p_resource_list_member_id => p_resource_list_member_id
3030 ,p_out_resource_list_member_id => l_resource_list_member_id
3031 ,p_return_status => l_return_status
3032 );
3033
3034 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3035 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3036 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3037 RAISE FND_API.G_EXC_ERROR;
3038 END IF;
3039
3040
3041 -- VALIDATION LAYER ----------------------------------------------------------------
3042 x_err_code := 0;
3043
3044 PA_GET_RESOURCE.delete_resource_list_member_ok(l_resource_list_id,l_resource_list_member_id,x_err_code,x_err_stage);
3045
3046 IF x_err_code <> 0 THEN
3047 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3048 FND_MESSAGE.SET_NAME('PA',x_err_stage);
3049 FND_MSG_PUB.ADD;
3050 END IF;
3051 RAISE FND_API.G_EXC_ERROR;
3052 END IF;
3053
3054
3055 /*Changes starts for Resource Mapping Enhancements-- Bug 1889671 */
3056
3057 OPEN Cur_Unclassified_Parent_ID(l_resource_list_member_id);
3058 FETCH Cur_Unclassified_Parent_ID INTO l_parent_member_id;
3059 CLOSE Cur_Unclassified_Parent_ID;
3060
3061 /*Changes ends for Resource Mapping Enhancements-- Bug 1889671 */
3062
3063
3064 -- D E L E T E Resource List MEMBER ------------------------------------------------------
3065
3066 OPEN l_lock_row_member_csr (l_resource_list_member_id);
3067 CLOSE l_lock_row_member_csr;
3068
3069 DELETE
3070 pa_resource_list_members
3071 WHERE
3072 resource_list_member_id = l_resource_list_member_id;
3073
3074 /* Chnages added for Resource Mapping Enhancement -- Bug 1889671 */
3075
3076 /* The deletion of unclassified resource list will tale place if de;etion is taking place for listmember and not for group .
3077 */
3078
3079 IF l_parent_member_id IS NOT NULL THEN
3080 OPEN Cur_Unclassified_member(l_parent_member_id);
3081 FETCH Cur_Unclassified_member INTO l_unclassified_list_member_id;
3082 CLOSE Cur_Unclassified_member;
3083
3084 /*Before Deleting the unclassified Resource , check whether it can be deleted. The same check is done for unclassified resource as for normal resource list member. */
3085
3086 IF l_unclassified_list_member_id IS NOT NULL THEN
3087
3088 pa_get_resource.delete_resource_list_member_ok(
3089 L_RESOURCE_LIST_ID =>l_resource_list_id,
3090 L_RESOURCE_LIST_MEMBER_ID =>l_unclassified_list_member_id,
3091 X_ERR_CODE =>x_err_code,
3092 X_ERR_STAGE =>x_err_stage);
3093
3094 /* if the unclassified resource list can be dleted then call the procedure */
3095
3096 IF x_err_code = 0 THEN
3097
3098 pa_resource_list_pkg.Delete_Unclassified_Child(
3099 X_RESOURCE_LIST_ID =>l_resource_list_id,
3100 X_PARENT_MEMBER_ID =>l_parent_member_id,
3101 X_MSG_COUNT =>x_err_code,
3102 X_MSG_DATA =>x_err_stage,
3103 X_RETURN_STATUS =>l_return_status);
3104
3105 IF x_err_code <> 0 THEN
3106 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3107 FND_MESSAGE.SET_NAME('PA',x_err_stage);
3108 FND_MSG_PUB.ADD;
3109 END IF;
3110 RAISE FND_API.G_EXC_ERROR;
3111 END IF;
3112 END IF;
3113 END IF;
3114 END IF;
3115
3116 IF FND_API.to_boolean( p_commit )
3117 THEN
3118 COMMIT;
3119 END IF;
3120
3121 EXCEPTION
3122
3123 WHEN FND_API.G_EXC_ERROR THEN
3124
3125 p_return_status := FND_API.G_RET_STS_ERROR ;
3126 ROLLBACK TO Delete_Resource_List_Mbr_Pub;
3127
3128 FND_MSG_PUB.Count_And_Get
3129 ( p_count => p_msg_count ,
3130 p_data => p_msg_data );
3131
3132 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3133
3134 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3135 ROLLBACK TO Delete_Resource_List_Mbr_Pub;
3136
3137 FND_MSG_PUB.Count_And_Get
3138 ( p_count => p_msg_count ,
3139 p_data => p_msg_data );
3140
3141 WHEN ROW_ALREADY_LOCKED THEN
3142
3143 p_return_status := FND_API.G_RET_STS_ERROR ;
3144 ROLLBACK TO Delete_Resource_List_Mbr_Pub;
3145
3146 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3147 THEN
3148 FND_MESSAGE.SET_NAME('PA','PA_ROW_ALREADY_LOCKED');
3149 FND_MESSAGE.SET_TOKEN('ENTITY', 'RESOURCE_LIST');
3150 FND_MSG_PUB.ADD;
3151 END IF;
3152
3153 FND_MSG_PUB.Count_And_Get
3154 ( p_count => p_msg_count ,
3155 p_data => p_msg_data );
3156
3157 WHEN OTHERS THEN
3158
3159 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3160 ROLLBACK TO Delete_Resource_List_Mbr_Pub;
3161
3162 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3163 THEN
3164 FND_MSG_PUB.Add_Exc_Msg
3165 ( p_pkg_name => G_PKG_NAME ,
3166 p_procedure_name => l_api_name
3167 );
3168 END IF;
3169
3170 FND_MSG_PUB.Count_And_Get
3171 ( p_count => p_msg_count ,
3172 p_data => p_msg_data );
3173
3174 END Delete_Resource_list_Member;
3175 -- ================================================================
3176 --
3177 -- Name: Sort_Resource_List_Members
3178 -- Type: PL/SQL Procedure
3179 -- Decscription: This sorts a resource list or a group within a resource list
3180 -- by alias or resource name. The sort_order column is
3181 -- resequenced by increments of ten.
3182 --
3183 -- Called Subprograms: None
3184 --
3185 -- NOTES:
3186 -- This API DOES sort a given resource list by either ALIAS
3187 -- or RESOURCE_NAME. However, it isn't obvious as the
3188 -- 'p_sort_by' parameter is included as 'decode' in
3189 -- the cursor 'order by' clauses.
3190 --
3191 -- History:
3192 -- xx-AUG-96 Created rkrishna
3193 -- 04-DEC-96 Update jwhite Applied latest standards.
3194 --
3195
3196 PROCEDURE Sort_Resource_List_Members
3197 ( p_commit IN VARCHAR2 := FND_API.G_FALSE,
3198 p_api_version_number IN NUMBER,
3199 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3200 p_resource_list_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3201 p_resource_list_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
3202 p_resource_group_alias IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3203 p_sort_by IN VARCHAR2,
3204 p_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
3205 p_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3206 p_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3207 )
3208 IS
3209 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
3210 l_api_name CONSTANT VARCHAR2(30) := 'Sort_Resource_List_Members';
3211 l_message_count NUMBER;
3212
3213 l_resource_list_id NUMBER := 0;
3214 l_return_status VARCHAR2(1);
3215 l_dummy VARCHAR2(1);
3216 l_resource_group_alias pa_resource_list_members.alias%TYPE;
3217
3218 l_rg_rowid VARCHAR2(20) ;
3219 l_rg_rlm_id NUMBER := 0;
3220 l_rg_sort_order NUMBER := 0;
3221 l_mbr_rowid VARCHAR2(20) ;
3222 l_mbr_sort_order NUMBER := 0;
3223 l_resource_list_member_id pa_resource_list_members.resource_list_member_id%TYPE;
3224 l_msg_count NUMBER ;
3225 l_msg_data VARCHAR2(2000);
3226 l_function_allowed VARCHAR2(1);
3227 l_resp_id NUMBER := 0;
3228
3229 -- Cursor for Resource Group Validation
3230
3231 CURSOR l_rsrc_grp_csr (l_resource_list_id NUMBER, l_resource_group_alias VARCHAR2)
3232 IS
3233 SELECT rlm.resource_list_member_id
3234 FROM pa_resource_list_members rlm
3235 WHERE rlm.resource_list_id = l_resource_list_id
3236 AND rlm.parent_member_id IS NULL
3237 AND rlm.alias = l_resource_group_alias;
3238
3239 -- Cursor for UPDATE LOOP: OUTER GROUP
3240
3241 CURSOR l_rsrc_list_csr (l_resource_list_id NUMBER
3242 , l_resource_group_alias VARCHAR2
3243 , p_sort_by VARCHAR2)
3244 IS
3245 SELECT rlm.ROWID, rlm.resource_list_member_id
3246 FROM pa_resource_list_members rlm,
3247 pa_resources r,
3248 pa_resource_types rt
3249 WHERE rlm.resource_id = r.resource_id
3250 AND r.resource_type_id = rt.resource_type_id
3251 AND rt.resource_type_code <> 'UNCLASSIFIED'
3252 AND rlm.resource_list_id = l_resource_list_id
3253 AND rlm.parent_member_id IS NULL
3254 AND rlm.alias = NVL(l_resource_group_alias,rlm.alias)
3255 ORDER BY DECODE(p_sort_by,'ALIAS',rlm.alias,'RESOURCE_NAME', r.name, rlm.alias);
3256
3257 -- Cursor for UPDATE LOOP: INNER MEMBERS
3258
3259 CURSOR l_rsrc_list_mbr_csr (l_rg_rlm_id NUMBER, p_sort_by VARCHAR2)
3260 IS
3261 SELECT rlm.ROWID
3262 FROM pa_resource_list_members rlm,
3263 pa_resources r,
3264 pa_resource_types rt
3265 WHERE rlm.resource_id = r.resource_id
3266 AND rlm.parent_member_id = l_rg_rlm_id
3267 AND r.resource_type_id = rt.resource_type_id
3268 AND rt.resource_type_code <> 'UNCLASSIFIED'
3269 ORDER BY DECODE(p_sort_by,'ALIAS',rlm.alias,'RESOURCE_NAME', r.name, rlm.alias);
3270
3271 -- ROW LOCKING: A L L Members of Resource List
3272
3273 CURSOR l_lock_row_all_csr (l_resource_list_id NUMBER)
3274 IS
3275 SELECT 'x'
3276 FROM pa_resource_list_members rlm
3277 WHERE rlm.resource_list_id = l_resource_list_id
3278 FOR UPDATE NOWAIT;
3279
3280 -- ROW LOCKING: A single group and its members
3281
3282 CURSOR l_lock_row_group_csr (l_resource_list_member_id NUMBER)
3283 IS
3284 SELECT 'x'
3285 FROM pa_resource_list_members rlm
3286 WHERE (rlm.resource_list_member_id = l_resource_list_member_id
3287 OR rlm.parent_member_id = l_resource_list_member_id)
3288 FOR UPDATE NOWAIT;
3289
3290
3291
3292 BEGIN
3293
3294 SAVEPOINT Sort_Resource_List_Mbr_Pub;
3295
3296 -- Standard Api compatibility call
3297
3298 IF NOT FND_API.Compatible_API_Call ( l_api_version_number ,
3299 p_api_version_number ,
3300 l_api_name ,
3301 G_PKG_NAME )
3302 THEN
3303 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3304 END IF;
3305 l_resp_id := FND_GLOBAL.Resp_id;
3306
3307 -- Actions performed using the APIs would be subject to
3308 -- function security. If the responsibility does not allow
3309 -- such functions to be executed, the API should not proceed further
3310 -- since the user does not have access to such functions
3311
3312
3313 PA_PM_FUNCTION_SECURITY_PUB.check_function_security
3314 (p_api_version_number => p_api_version_number,
3315 p_responsibility_id => l_resp_id,
3316 p_function_name => 'PA_PM_UPD_RESOURCE_LIST_MEMBER',
3317 p_msg_count => l_msg_count,
3318 p_msg_data => l_msg_data,
3319 p_return_status => l_return_status,
3320 p_function_allowed => l_function_allowed );
3321
3322 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
3323 THEN
3324 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3325
3326 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
3327 THEN
3328 RAISE FND_API.G_EXC_ERROR;
3329 END IF;
3330 IF l_function_allowed = 'N' THEN
3331 FND_MESSAGE.SET_NAME('PA','PA_FUNCTION_SECURITY_ENFORCED');
3332 FND_MSG_PUB.ADD;
3333 p_return_status := FND_API.g_ret_sts_error;
3334 RAISE FND_API.G_EXC_ERROR;
3335 END IF;
3336
3337 IF FND_API.to_boolean( p_init_msg_list )
3338 THEN
3339 FND_MSG_PUB.initialize;
3340 END IF;
3341
3342 p_return_status := FND_API.G_RET_STS_SUCCESS;
3343
3344
3345 -- VALUE LAYER --------------------------------------------------------------
3346
3347 Convert_List_name_to_id
3348 ( p_resource_list_name => p_resource_list_name,
3349 p_resource_list_id => p_resource_list_id,
3350 p_out_resource_list_id => l_resource_list_id,
3351 p_return_status => l_return_status
3352 );
3353
3354 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3355 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3356 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3357 RAISE FND_API.G_EXC_ERROR;
3358 END IF;
3359
3360 -- Default Group Alias to Local Variable
3361 IF (p_resource_group_alias = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) THEN
3362 l_resource_group_alias := NULL;
3363 ELSE
3364 l_resource_group_alias := p_resource_group_alias;
3365 END IF;
3366
3367
3368 -- VALIDATION LAYER ----------------------------------------------------------
3369
3370 -- FIX, 12-FEB-97, jwhite:
3371 -- Added validation for p_sort_by Allowable Values
3372 -- -----------------------------------------------------------------------------------------
3373
3374 IF ((p_sort_by <> 'ALIAS') AND (p_sort_by <> 'RESOURCE_NAME')) THEN
3375 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3376 THEN
3377 FND_MESSAGE.SET_NAME('PA','PA_INVALID_SORT_BY');
3378 FND_MSG_PUB.ADD;
3379 END IF;
3380 RAISE FND_API.G_EXC_ERROR;
3381 END IF;
3382
3383 -- -----------------------------------------------------------------------------------------
3384
3385 -- Validate Resource Group Alias if NOT NULL
3386
3387 IF (l_resource_group_alias IS NOT NULL) THEN
3388 OPEN l_rsrc_grp_csr (l_resource_list_id, l_resource_group_alias);
3389 FETCH l_rsrc_grp_csr INTO l_resource_list_member_id;
3390 IF (l_rsrc_grp_csr %NOTFOUND) THEN
3391 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3392 THEN
3393 FND_MESSAGE.SET_NAME('PA','PA_INVALID_RSRC_GROUP_ALIAS');
3394 FND_MSG_PUB.ADD;
3395 END IF;
3396 RAISE FND_API.G_EXC_ERROR;
3397 END IF;
3398 CLOSE l_rsrc_grp_csr ;
3399 END IF;
3400
3401 -- UPDATE RESOURCE LIST GROUPS AND MEMBERS ---------------
3402
3403
3404 -- LOCK ROWS Depending on whether to Sort All Members or a Single Group of Members
3405
3406 IF (l_resource_group_alias IS NULL) THEN
3407 -- Lock Entire List
3408
3409 OPEN l_lock_row_all_csr (l_resource_list_id);
3410 CLOSE l_lock_row_all_csr;
3411
3412 ELSE
3413 -- Lock Group and its members
3414
3415 OPEN l_lock_row_group_csr (l_resource_list_member_id);
3416 CLOSE l_lock_row_group_csr;
3417
3418 END IF;
3419
3420
3421
3422 -- Outer Update Loop: Group
3423
3424 OPEN l_rsrc_list_csr (l_resource_list_id, l_resource_group_alias
3425 , p_sort_by);
3426
3427 LOOP
3428
3429 FETCH l_rsrc_list_csr INTO l_rg_rowid, l_rg_rlm_id;
3430 EXIT WHEN l_rsrc_list_csr%NOTFOUND;
3431
3432 -- Inner Update Loop: 2nd-Level Members
3433
3434 l_mbr_sort_order := 0;
3435 OPEN l_rsrc_list_mbr_csr (l_rg_rlm_id, p_sort_by);
3436
3437 LOOP
3438
3439 FETCH l_rsrc_list_mbr_csr INTO l_mbr_rowid;
3440 EXIT WHEN l_rsrc_list_mbr_csr%NOTFOUND;
3441
3442 -- Inner Loop Update
3443 l_mbr_sort_order := l_mbr_sort_order + 10;
3444
3445 UPDATE pa_resource_list_members
3446 SET sort_order = l_mbr_sort_order
3447 WHERE ROWID = l_mbr_rowid;
3448
3449 END LOOP;
3450 CLOSE l_rsrc_list_mbr_csr;
3451
3452 l_rg_sort_order := l_rg_sort_order + 10;
3453
3454 UPDATE pa_resource_list_members
3455 SET sort_order = l_rg_sort_order
3456 WHERE ROWID = l_rg_rowid;
3457
3458 END LOOP;
3459 CLOSE l_rsrc_list_csr;
3460
3461
3462
3463 IF FND_API.to_boolean( p_commit )
3464 THEN
3465 COMMIT;
3466 END IF;
3467
3468 EXCEPTION
3469
3470 WHEN FND_API.G_EXC_ERROR THEN
3471
3472 p_return_status := FND_API.G_RET_STS_ERROR ;
3473 ROLLBACK TO Sort_Resource_List_Mbr_Pub;
3474
3475 FND_MSG_PUB.Count_And_Get
3476 ( p_count => p_msg_count ,
3477 p_data => p_msg_data );
3478
3479 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3480
3481 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3482 ROLLBACK TO Sort_Resource_List_Mbr_Pub;
3483
3484 FND_MSG_PUB.Count_And_Get
3485 ( p_count => p_msg_count ,
3486 p_data => p_msg_data );
3487
3488 WHEN ROW_ALREADY_LOCKED THEN
3489
3490 p_return_status := FND_API.G_RET_STS_ERROR ;
3491 ROLLBACK TO Sort_Resource_List_Mbr_Pub;
3492
3493 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3494 THEN
3495 FND_MESSAGE.SET_NAME('PA','PA_ROW_ALREADY_LOCKED');
3496 FND_MESSAGE.SET_TOKEN('ENTITY', 'RESOURCE_LIST_MEMBER');
3497 FND_MSG_PUB.ADD;
3498 END IF;
3499
3500 FND_MSG_PUB.Count_And_Get
3501 ( p_count => p_msg_count ,
3502 p_data => p_msg_data );
3503
3504 WHEN OTHERS THEN
3505
3506 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3507 ROLLBACK TO Sort_Resource_List_Mbr_Pub;
3508
3509 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3510 FND_MSG_PUB.Add_Exc_Msg
3511 ( p_pkg_name => G_PKG_NAME ,
3512 p_procedure_name => l_api_name
3513 );
3514 END IF;
3515
3516 FND_MSG_PUB.Count_And_Get
3517 ( p_count => p_msg_count ,
3518 p_data => p_msg_data );
3519
3520
3521 END Sort_Resource_List_Members;
3522 -- ==============================================================
3523
3524 --
3525 -- Name: Fetch_Resource_list_id
3526 -- Type: PL/SQL Procedure
3527 -- Decscription: This function fetches a resource_list_id.
3528 --
3529 -- Called Subprograms:
3530 --
3531 -- History:
3532 -- xx-AUG-96 Created rkrishna
3533 -- 04-DEC-96 Update jwhite Applied latest standards.
3534 --
3535
3536
3537 FUNCTION Fetch_Resource_list_id
3538 (p_resource_list_name IN VARCHAR2) RETURN
3539 NUMBER
3540 IS
3541
3542 CURSOR l_resource_list_csr IS
3543 SELECT resource_list_id
3544 FROM
3545 pa_resource_lists
3546 WHERE
3547 name = p_resource_list_name;
3548
3549 l_resource_list_rec l_resource_list_csr%ROWTYPE;
3550 l_api_name CONSTANT VARCHAR2(30) := ' Fetch_Resource_list_id';
3551
3552
3553 BEGIN
3554
3555 OPEN l_resource_list_csr;
3556 FETCH l_resource_list_csr INTO l_resource_list_rec.resource_list_id;
3557 IF l_resource_list_csr%NOTFOUND THEN
3558 CLOSE l_resource_list_csr;
3559 RETURN NULL;
3560 ELSE
3561 CLOSE l_resource_list_csr;
3562 RETURN l_resource_list_rec.resource_list_id;
3563 END IF;
3564
3565 EXCEPTION
3566
3567 WHEN OTHERS THEN
3568
3569 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3570
3571 FND_MSG_PUB.Add_Exc_Msg
3572 ( p_pkg_name => G_PKG_NAME ,
3573 p_procedure_name => l_api_name
3574 );
3575
3576 END IF;
3577
3578
3579 END Fetch_Resource_list_id;
3580 -- ================================================================
3581
3582 --
3583 -- Name: Convert_List_name_to_id
3584 -- Type: PL/SQL Procedure
3585 -- Decscription: This procedure converts resource list name to idenfier.
3586 --
3587 -- Called Subprograms: Fetch_Resource_list_id
3588 --
3589 -- History:
3590 -- xx-AUG-96 Created rkrishna
3591 -- 04-DEC-96 Update jwhite Applied latest standards.
3592 --
3593
3594
3595 PROCEDURE Convert_List_Name_To_Id
3596 (
3597 p_resource_list_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3598 p_resource_list_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
3599 p_out_resource_list_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
3600 p_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3601 )
3602
3603 IS
3604
3605 l_api_name CONSTANT VARCHAR2(30) := 'Convert_List_name_to_id';
3606 l_resource_list_id NUMBER ;
3607 l_dummy VARCHAR2(1);
3608
3609 CURSOR l_resource_list_csr (p_resource_list_id NUMBER)
3610 IS
3611 SELECT 'x'
3612 FROM pa_resource_lists
3613 WHERE resource_list_id = p_resource_list_id;
3614
3615
3616 BEGIN
3617 p_return_status := FND_API.G_RET_STS_SUCCESS;
3618
3619 IF p_resource_list_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
3620 -- Validate Passed Resource_List_Id
3621
3622 OPEN l_resource_list_csr (p_resource_list_id);
3623 FETCH l_resource_list_csr INTO l_dummy;
3624 IF (l_resource_list_csr %NOTFOUND) THEN
3625 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3626 THEN
3627 FND_MESSAGE.SET_NAME('PA','PA_API_CONV_ERROR_AMG'); -- Bug 2257612
3628 FND_MESSAGE.SET_TOKEN('ATTR_NAME','Resource List');
3629 FND_MESSAGE.SET_TOKEN('ATTR_VALUE', p_resource_list_id);
3630 FND_MSG_PUB.ADD;
3631 END IF;
3632 CLOSE l_resource_list_csr;
3633 RAISE FND_API.G_EXC_ERROR;
3634 ELSE
3635 CLOSE l_resource_list_csr;
3636 p_out_resource_list_id := p_resource_list_id;
3637 END IF;
3638
3639
3640 ELSIF --(i.e p_resource_list_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
3641 p_resource_list_name <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
3642
3643 l_resource_list_id :=
3644 Fetch_Resource_list_id
3645 (p_resource_list_name => p_resource_list_name );
3646
3647 IF l_resource_list_id IS NULL THEN
3648 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
3649 FND_MESSAGE.SET_NAME('PA','PA_API_CONV_ERROR_AMG'); -- Bug 2257612
3650 FND_MESSAGE.SET_TOKEN('ATTR_NAME','Resource List Name');
3651 FND_MESSAGE.SET_TOKEN('ATTR_VALUE',p_resource_list_name);
3652 FND_MSG_PUB.ADD;
3653 RAISE FND_API.G_EXC_ERROR;
3654 END IF;
3655 ELSE
3656 p_out_resource_list_id := l_resource_list_id;
3657 END IF;
3658
3659 END IF; -- If p_resource_list_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
3660
3661 EXCEPTION
3662
3663 WHEN FND_API.G_EXC_ERROR THEN
3664
3665 p_return_status := FND_API.G_RET_STS_ERROR ;
3666
3667 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3668
3669 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3670
3671
3672 WHEN OTHERS THEN
3673
3674 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3675 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3676
3677 FND_MSG_PUB.Add_Exc_Msg
3678 ( p_pkg_name => G_PKG_NAME ,
3679 p_procedure_name => l_api_name );
3680
3681 END IF;
3682
3683 END Convert_List_Name_To_Id ;
3684 -- ================================================================
3685
3686 --
3687 -- Name: Fetch_Resource_list_Member_id
3688 -- Type: PL/SQL Procedure
3689 -- Decscription: This fuctions fetches the resource_list_member_id.
3690 --
3691 -- Called Subprograms:
3692 --
3693 -- History:
3694 -- xx-AUG-96 Created rkrishna
3695 -- 04-DEC-96 Update jwhite Applied latest standards.
3696 --
3697
3698
3699 FUNCTION Fetch_Resource_list_Member_id
3700 ( p_resource_list_id IN NUMBER,
3701 p_alias IN VARCHAR2
3702 ) RETURN
3703 NUMBER
3704 IS
3705
3706 CURSOR l_resource_list_members_csr IS
3707 SELECT resource_list_member_id
3708 FROM
3709 pa_resource_list_members
3710 WHERE resource_list_id = p_resource_list_id
3711 AND alias = p_alias;
3712
3713 l_resource_list_member_rec l_resource_list_members_csr%ROWTYPE;
3714 l_api_name CONSTANT VARCHAR2(30) := ' Fetch_Resource_list_Member_id';
3715
3716 BEGIN
3717
3718 OPEN l_resource_list_members_csr;
3719 FETCH l_resource_list_members_csr INTO
3720 l_resource_list_member_rec.resource_list_member_id;
3721 IF l_resource_list_members_csr%NOTFOUND THEN
3722 CLOSE l_resource_list_members_csr;
3723 RETURN NULL;
3724 ELSE
3725 CLOSE l_resource_list_members_csr;
3726 RETURN l_resource_list_member_rec.resource_list_member_id;
3727 END IF;
3728
3729 EXCEPTION
3730
3731 WHEN OTHERS THEN
3732
3733 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3734
3735 FND_MSG_PUB.Add_Exc_Msg
3736 ( p_pkg_name => G_PKG_NAME ,
3737 p_procedure_name => l_api_name
3738 );
3739
3740 END IF;
3741
3742
3743 END Fetch_Resource_list_Member_id;
3744 -- ================================================================
3745
3746 --
3747 -- Name: Convert_Alias_To_Id
3748 -- Type: PL/SQL Procedure
3749 -- Decscription: This procedure converts an alias and resource list name to a
3750 -- resource_list_member_id.
3751 --
3752 -- Called Subprograms: Fetch_Resource_list_Member_id
3753 --
3754 -- History:
3755 -- xx-AUG-96 Created rkrishna
3756 -- 04-DEC-96 Update jwhite Applied latest standards.
3757 --
3758
3759 PROCEDURE Convert_Alias_To_Id
3760 (
3761 p_resource_list_id IN NUMBER,
3762 p_alias IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3763 p_resource_list_member_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
3764 p_project_id IN NUMBER DEFAULT NULL,
3765 p_out_resource_list_member_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
3766 p_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3767 )
3768 IS
3769
3770 l_api_name CONSTANT VARCHAR2(30) := 'Convert_alias_to_id';
3771 l_resource_list_member_id NUMBER;
3772 l_migration_code VARCHAR2(1);
3773 l_control_flag VARCHAR2(1);
3774 l_object_type VARCHAR2(20);
3775 l_object_id NUMBER;
3776 l_dummy VARCHAR2(1);
3777
3778 CURSOR l_resource_list_members_csr (p_resource_list_member_id NUMBER,
3779 p_resource_list_id NUMBER,
3780 p_object_id NUMBER,
3781 p_object_type VARCHAR2)
3782 IS
3783 SELECT 'x'
3784 FROM pa_resource_list_members
3785 WHERE resource_list_member_id = p_resource_list_member_id
3786 AND resource_list_id = p_resource_list_id
3787 AND nvl(object_id, -99) = nvl(p_object_id, -99)
3788 AND nvl(object_type, 'D') = nvl(p_object_type, 'D');
3789
3790 BEGIN
3791 p_return_status := FND_API.G_RET_STS_SUCCESS;
3792
3793 SELECT migration_code, control_flag
3794 INTO l_migration_code, l_control_flag
3795 FROM pa_resource_lists_all_bg
3796 WHERE resource_list_id = p_resource_list_id;
3797
3798 IF l_migration_code is not null THEN
3799 IF l_control_flag = 'Y' THEN
3800 l_object_type := 'RESOURCE_LIST';
3801 l_object_id := p_resource_list_id;
3802 ELSE
3803 IF p_project_id IS NULL THEN
3804 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3805 END IF;
3806 l_object_type := 'PROJECT';
3807 l_object_id := p_project_id;
3808 END IF;
3809 END IF;
3810
3811 IF (p_resource_list_member_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
3812 -- Validate Passed Resource_List_Member_Id
3813
3814 OPEN l_resource_list_members_csr(
3815 p_resource_list_member_id => p_resource_list_member_id,
3816 p_resource_list_id => p_resource_list_id,
3817 p_object_id => l_object_id,
3818 p_object_type => l_object_type);
3819
3820 FETCH l_resource_list_members_csr INTO l_dummy;
3821 IF (l_resource_list_members_csr %NOTFOUND) THEN
3822 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3823 FND_MESSAGE.SET_NAME('PA','PA_API_CONV_ERROR_AMG'); -- Bug 2257612
3824 FND_MESSAGE.SET_TOKEN('ATTR_NAME','Resource List Member');
3825 FND_MESSAGE.SET_TOKEN('ATTR_VALUE', p_resource_list_member_id);
3826 FND_MSG_PUB.ADD;
3827 END IF;
3828 CLOSE l_resource_list_members_csr;
3829 RAISE FND_API.G_EXC_ERROR;
3830 ELSE
3831 CLOSE l_resource_list_members_csr;
3832 p_out_resource_list_member_id := p_resource_list_member_id;
3833 END IF;
3834
3835 ELSIF (p_alias <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) THEN
3836
3837 -- Fetch Resource List Member Id
3838 -- old model only
3839 IF l_migration_code is NULL then
3840 l_resource_list_member_id := Fetch_Resource_list_member_id
3841 (p_resource_list_id => p_resource_list_id,
3842 p_alias => p_alias );
3843 ELSE
3844 SELECT resource_list_member_id
3845 INTO l_resource_list_member_id
3846 FROM pa_resource_list_members
3847 WHERE resource_list_id = p_resource_list_id
3848 AND object_type = l_object_type
3849 AND object_id = l_object_id
3850 AND alias = p_alias;
3851 END IF;
3852
3853 -- In certain cases,it is ok to get a null resource_list_member_id
3854 -- This implies that the alias is yet to be created. However, since this API is unware of
3855 -- those cases, a null id is always treated as an error.
3856
3857 IF (l_resource_list_member_id IS NULL ) THEN
3858
3859 p_out_resource_list_member_id := NULL;
3860 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
3861 FND_MESSAGE.SET_NAME('PA','PA_API_CONV_ERROR_AMG'); -- Bug 2257612
3862 FND_MESSAGE.SET_TOKEN('ATTR_NAME','Alias');
3863 FND_MESSAGE.SET_TOKEN('ATTR_VALUE',p_alias);
3864 FND_MSG_PUB.ADD;
3865 END IF;
3866 RAISE FND_API.G_EXC_ERROR;
3867
3868 ELSE
3869 p_out_resource_list_member_id := l_resource_list_member_id;
3870 END IF;
3871
3872 END IF; -- IF p_resource_list_member_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
3873
3874 EXCEPTION
3875
3876 WHEN FND_API.G_EXC_ERROR THEN
3877
3878 p_return_status := FND_API.G_RET_STS_ERROR ;
3879
3880 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3881
3882 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3883
3884 WHEN OTHERS THEN
3885
3886 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3887
3888 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3889
3890 FND_MSG_PUB.Add_Exc_Msg
3891 ( p_pkg_name => G_PKG_NAME ,
3892 p_procedure_name => l_api_name
3893 );
3894
3895 END IF;
3896
3897
3898 END Convert_Alias_To_Id;
3899
3900
3901 PROCEDURE Get_Resource_Name
3902 (
3903 p_resource_type_code IN VARCHAR2,
3904 p_resource_attr_value IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
3905 p_resource_name OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3906 p_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3907 )
3908 IS
3909
3910 l_api_name CONSTANT VARCHAR2(30) := 'Get_Resource_Name';
3911
3912 CURSOR l_emp_csr (l_resource_attr_value VARCHAR2)
3913 IS
3914 SELECT employee_name
3915 FROM pa_employees_res_v
3916 WHERE person_id = TO_NUMBER(l_resource_attr_value);
3917
3918 CURSOR l_job_csr (l_resource_attr_value VARCHAR2)
3919 IS
3920 SELECT job_name
3921 FROM pa_jobs_res_v
3922 WHERE job_id = TO_NUMBER(l_resource_attr_value);
3923
3924 CURSOR l_org_csr (l_resource_attr_value VARCHAR2)
3925 IS
3926 SELECT organization_name
3927 FROM pa_organizations_res_v
3928 WHERE organization_id = TO_NUMBER(l_resource_attr_value);
3929
3930 CURSOR l_vendor_csr (l_resource_attr_value VARCHAR2)
3931 IS
3932 SELECT vendor_name
3933 FROM pa_vendors_res_v
3934 WHERE vendor_id = TO_NUMBER(l_resource_attr_value);
3935
3936 CURSOR l_exp_type_csr (l_resource_attr_value VARCHAR2)
3937 IS
3938 SELECT expenditure_type
3939 FROM pa_expenditure_types_res_v
3940 WHERE expenditure_type = l_resource_attr_value;
3941
3942 CURSOR l_exp_category_csr (l_resource_attr_value VARCHAR2)
3943 IS
3944 SELECT expenditure_category
3945 FROM pa_expend_categories_res_v
3946 WHERE expenditure_category = l_resource_attr_value;
3947
3948 CURSOR l_event_type_csr (l_resource_attr_value VARCHAR2)
3949 IS
3950 SELECT event_type
3951 FROM pa_event_types_res_v
3952 WHERE event_type = l_resource_attr_value;
3953
3954 CURSOR l_rev_category_csr (l_resource_attr_value VARCHAR2)
3955 IS
3956 SELECT revenue_category_code
3957 FROM pa_revenue_categories_res_v
3958 WHERE revenue_category_code = l_resource_attr_value;
3959
3960
3961 BEGIN
3962
3963 p_return_status := FND_API.G_RET_STS_SUCCESS;
3964
3965 IF (p_resource_type_code = 'EMPLOYEE') THEN
3966 -- Validate Passed person_id
3967
3968 OPEN l_emp_csr(p_resource_attr_value);
3969 FETCH l_emp_csr INTO p_resource_name;
3970 IF (l_emp_csr%NOTFOUND) THEN
3971 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3972 THEN
3973 FND_MESSAGE.SET_NAME('PA','PA_INVALID_EMPLOYEE');
3974 FND_MSG_PUB.ADD;
3975 END IF;
3976 CLOSE l_emp_csr;
3977 RAISE FND_API.G_EXC_ERROR;
3978 ELSE
3979 CLOSE l_emp_csr;
3980 END IF;
3981
3982 ELSIF (p_resource_type_code = 'JOB') THEN
3983 -- Validate Passed job_id
3984
3985 OPEN l_job_csr(p_resource_attr_value);
3986 FETCH l_job_csr INTO p_resource_name;
3987 IF (l_job_csr%NOTFOUND) THEN
3988 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3989 THEN
3990 FND_MESSAGE.SET_NAME('PA','PA_INVALID_JOB');
3991 FND_MSG_PUB.ADD;
3992 END IF;
3993 CLOSE l_job_csr;
3994 RAISE FND_API.G_EXC_ERROR;
3995 ELSE
3996 CLOSE l_job_csr;
3997 END IF;
3998
3999 ELSIF (p_resource_type_code = 'ORGANIZATION') THEN
4000 -- Validate Passed org_id
4001
4002 OPEN l_org_csr(p_resource_attr_value);
4003 FETCH l_org_csr INTO p_resource_name;
4004 IF (l_org_csr%NOTFOUND) THEN
4005 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
4006 THEN
4007 FND_MESSAGE.SET_NAME('PA','PA_INVALID_ORGANIZATION');
4008 FND_MSG_PUB.ADD;
4009 END IF;
4010 CLOSE l_org_csr;
4011 RAISE FND_API.G_EXC_ERROR;
4012 ELSE
4013 CLOSE l_org_csr;
4014 END IF;
4015
4016 ELSIF (p_resource_type_code = 'VENDOR') THEN
4017 -- Validate Passed vendor_id
4018
4019 OPEN l_vendor_csr(p_resource_attr_value);
4020 FETCH l_vendor_csr INTO p_resource_name;
4021 IF (l_vendor_csr%NOTFOUND) THEN
4022 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
4023 THEN
4024 FND_MESSAGE.SET_NAME('PA','PA_INVALID_VENDOR');
4025 FND_MSG_PUB.ADD;
4026 END IF;
4027 CLOSE l_vendor_csr;
4028 RAISE FND_API.G_EXC_ERROR;
4029 ELSE
4030 CLOSE l_vendor_csr;
4031 END IF;
4032
4033 ELSIF (p_resource_type_code = 'EXPENDITURE_TYPE') THEN
4034 -- Validate Passed expenditure_type
4035
4036 OPEN l_exp_type_csr(p_resource_attr_value);
4037 FETCH l_exp_type_csr INTO p_resource_name;
4038 IF (l_exp_type_csr%NOTFOUND) THEN
4039 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
4040 THEN
4041 FND_MESSAGE.SET_NAME('PA','PA_INVALID_EXPENDITURE_TYPE');
4042 FND_MSG_PUB.ADD;
4043 END IF;
4044 CLOSE l_exp_type_csr;
4045 RAISE FND_API.G_EXC_ERROR;
4046 ELSE
4047 CLOSE l_exp_type_csr;
4048 END IF;
4049
4050 ELSIF (p_resource_type_code = 'EXPENDITURE_CATEGORY') THEN
4051 -- Validate Passed expenditure_category
4052
4053 OPEN l_exp_category_csr(p_resource_attr_value);
4054 FETCH l_exp_category_csr INTO p_resource_name;
4055 IF (l_exp_category_csr%NOTFOUND) THEN
4056 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
4057 THEN
4058 FND_MESSAGE.SET_NAME('PA','PA_INVALID_EXP_CATEGORY');
4059 FND_MSG_PUB.ADD;
4060 END IF;
4061 CLOSE l_exp_category_csr;
4062 RAISE FND_API.G_EXC_ERROR;
4063 ELSE
4064 CLOSE l_exp_category_csr;
4065 END IF;
4066
4067 ELSIF (p_resource_type_code = 'EVENT_TYPE') THEN
4068 -- Validate Passed event_type
4069
4070 OPEN l_event_type_csr(p_resource_attr_value);
4071 FETCH l_event_type_csr INTO p_resource_name;
4072 IF (l_event_type_csr%NOTFOUND) THEN
4073 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
4074 THEN
4075 FND_MESSAGE.SET_NAME('PA','PA_INVALID_EVENT_TYPE');
4076 FND_MSG_PUB.ADD;
4077 END IF;
4078 CLOSE l_exp_type_csr;
4079 RAISE FND_API.G_EXC_ERROR;
4080 ELSE
4081 CLOSE l_event_type_csr;
4082 END IF;
4083
4084 ELSIF (p_resource_type_code = 'REVENUE_CATEGORY') THEN
4085 -- Validate Passed revenue_category
4086
4087 OPEN l_rev_category_csr(p_resource_attr_value);
4088 FETCH l_rev_category_csr INTO p_resource_name;
4089 IF (l_rev_category_csr%NOTFOUND) THEN
4090 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
4091 THEN
4092 FND_MESSAGE.SET_NAME('PA','PA_INVALID_REV_CATEG');
4093 FND_MSG_PUB.ADD;
4094 END IF;
4095 CLOSE l_rev_category_csr;
4096 RAISE FND_API.G_EXC_ERROR;
4097 ELSE
4098 CLOSE l_rev_category_csr;
4099 END IF;
4100 END IF;
4101
4102 EXCEPTION
4103
4104 WHEN FND_API.G_EXC_ERROR THEN
4105
4106 p_return_status := FND_API.G_RET_STS_ERROR ;
4107
4108 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4109
4110 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4111
4112 WHEN OTHERS THEN
4113
4114 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4115
4116 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4117
4118 FND_MSG_PUB.Add_Exc_Msg
4119 ( p_pkg_name => G_PKG_NAME ,
4120 p_procedure_name => l_api_name
4121 );
4122
4123 END IF;
4124
4125 END GET_RESOURCE_NAME ;
4126
4127 FUNCTION is_planning_resource (p_resource_list_member_id IN NUMBER)
4128 RETURN VARCHAR2 IS
4129
4130 l_return VARCHAR2(1) := NULL;
4131 l_migration_code VARCHAR2(1) := NULL;
4132
4133 BEGIN
4134
4135 IF p_resource_list_member_id IS NOT NULL THEN
4136 BEGIN
4137 SELECT migration_code
4138 INTO l_migration_code
4139 FROM pa_resource_list_members
4140 WHERE resource_list_member_id = p_resource_list_member_id;
4141
4142 IF l_migration_code is NULL THEN
4143 l_return := 'N';
4144 ELSE
4145 l_return := 'Y';
4146 END IF;
4147
4148 EXCEPTION WHEN NO_DATA_FOUND THEN
4149 l_migration_code := NULL;
4150 l_return := NULL;
4151 END;
4152 END IF;
4153
4154 RETURN l_return;
4155
4156 END is_planning_resource;
4157
4158 FUNCTION is_planning_resource_list (p_resource_list_id IN NUMBER)
4159 RETURN VARCHAR2 IS
4160
4161 l_return VARCHAR2(1) := NULL;
4162 l_migration_code VARCHAR2(1) := NULL;
4163
4164 BEGIN
4165
4166 IF p_resource_list_id IS NOT NULL THEN
4167 BEGIN
4168 SELECT migration_code
4169 INTO l_migration_code
4170 FROM pa_resource_lists_all_bg
4171 WHERE resource_list_id = p_resource_list_id;
4172
4173 IF l_migration_code is NULL THEN
4174 l_return := 'N';
4175 ELSE
4176 l_return := 'Y';
4177 END IF;
4178
4179 EXCEPTION WHEN NO_DATA_FOUND THEN
4180 l_migration_code := NULL;
4181 l_return := NULL;
4182 END;
4183 END IF;
4184
4185 RETURN l_return;
4186
4187 END is_planning_resource_list;
4188
4189
4190 END PA_RESOURCE_PUB ;