1 package body AK_SECURITY_PVT as
2 /* $Header: akdvsecb.pls 120.3 2005/09/15 22:18:31 tshort ship $ */
3
4 --=======================================================
5 -- Function EXCLUDED_ITEM_EXISTS
6 --
7 -- Usage Private API for checking for the existence of
8 -- an attribute with the given key values. This
9 -- API should only be called by other APIs that are
10 -- owned by the Core Modules Team (AK).
11 --
12 -- Desc This API check to see if an attribute record
13 -- exists with the given key values.
14 --
15 -- Results The API returns the standard p_return_status parameter
16 -- indicating one of the standard return statuses :
17 -- * Unexpected error
18 -- * Error
19 -- * Success
20 -- This function will return TRUE if such an attribute
21 -- exists, or FALSE otherwise.
22 -- Parameters Attribute key columns
23 --
24 -- Version Initial version number = 1.0
25 -- History Current version number = 1.0
26 --=======================================================
27 function EXCLUDED_ITEM_EXISTS (
28 p_api_version_number IN NUMBER,
29 p_return_status OUT NOCOPY VARCHAR2,
30 p_responsibility_id IN NUMBER,
31 p_resp_application_id IN NUMBER,
32 p_attribute_code IN VARCHAR2,
33 p_attribute_application_id IN NUMBER
34 ) return BOOLEAN is
35 cursor l_check_csr is
36 select 1
37 from AK_EXCLUDED_ITEMS
38 where responsibility_id = p_responsibility_id
39 and resp_application_id = p_resp_application_id
40 and attribute_application_id = p_attribute_application_id
41 and attribute_code = p_attribute_code;
42 l_api_version_number CONSTANT number := 1.0;
43 l_api_name CONSTANT varchar2(30) := 'Excluded_Item_Exists';
44 l_dummy number;
45 begin
46 IF NOT FND_API.Compatible_API_Call (
47 l_api_version_number, p_api_version_number, l_api_name,
48 G_PKG_NAME) then
49 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
50 return FALSE;
51 END IF;
52
53 open l_check_csr;
54 fetch l_check_csr into l_dummy;
55 if (l_check_csr%notfound) then
56 close l_check_csr;
57 p_return_status := FND_API.G_RET_STS_SUCCESS;
58 return FALSE;
59 else
60 close l_check_csr;
61 p_return_status := FND_API.G_RET_STS_SUCCESS;
62 return TRUE;
63 end if;
64
65 EXCEPTION
66 WHEN FND_API.G_EXC_ERROR THEN
67 p_return_status := FND_API.G_RET_STS_ERROR;
68 return FALSE;
69 WHEN OTHERS THEN
70 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
71 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
72 SUBSTR (SQLERRM, 1, 240) );
73 FND_MSG_PUB.Add;
74 return FALSE;
75
76 end EXCLUDED_ITEM_EXISTS;
77
78
79 --=======================================================
80 -- Function RESP_SECURITY_ATTR_EXISTS
81 --
82 -- Usage Private API for checking for the existence of
83 -- an attribute with the given key values. This
84 -- API should only be called by other APIs that are
85 -- owned by the Core Modules Team (AK).
86 --
87 -- Desc This API check to see if an attribute record
88 -- exists with the given key values.
89 --
90 -- Results The API returns the standard p_return_status parameter
91 -- indicating one of the standard return statuses :
92 -- * Unexpected error
93 -- * Error
94 -- * Success
95 -- This function will return TRUE if such an attribute
96 -- exists, or FALSE otherwise.
97 -- Parameters Attribute key columns
98 --
99 -- Version Initial version number = 1.0
100 -- History Current version number = 1.0
101 --=======================================================
102 function RESP_SECURITY_ATTR_EXISTS (
103 p_api_version_number IN NUMBER,
104 p_return_status OUT NOCOPY VARCHAR2,
105 p_responsibility_id IN NUMBER,
106 p_resp_application_id IN NUMBER,
107 p_attribute_code IN VARCHAR2,
108 p_attribute_application_id IN NUMBER
109 ) return BOOLEAN is
110 cursor l_check_csr is
111 select 1
112 from AK_RESP_SECURITY_ATTRIBUTES
113 where responsibility_id = p_responsibility_id
114 and resp_application_id = p_resp_application_id
115 and attribute_application_id = p_attribute_application_id
116 and attribute_code = p_attribute_code;
117 l_api_version_number CONSTANT number := 1.0;
118 l_api_name CONSTANT varchar2(30) := 'Resp_Security_Attr_Exists';
119 l_dummy number;
120 begin
121 IF NOT FND_API.Compatible_API_Call (
122 l_api_version_number, p_api_version_number, l_api_name,
123 G_PKG_NAME) then
124 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
125 return FALSE;
126 END IF;
127
128 open l_check_csr;
129 fetch l_check_csr into l_dummy;
130 if (l_check_csr%notfound) then
131 close l_check_csr;
132 p_return_status := FND_API.G_RET_STS_SUCCESS;
133 return FALSE;
134 else
135 close l_check_csr;
136 p_return_status := FND_API.G_RET_STS_SUCCESS;
137 return TRUE;
138 end if;
139
140 EXCEPTION
141 WHEN FND_API.G_EXC_ERROR THEN
142 p_return_status := FND_API.G_RET_STS_ERROR;
143 return FALSE;
144 WHEN OTHERS THEN
145 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
146 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
147 SUBSTR (SQLERRM, 1, 240) );
148 FND_MSG_PUB.Add;
149 return FALSE;
150
151 end RESP_SECURITY_ATTR_EXISTS;
152
153 --=======================================================
154 -- Procedure CREATE_EXCLUDED_ITEM
155 --
156 -- Usage Private API for creating an excluded item. This
157 -- API should only be called by other APIs that are
158 -- owned by the Core Modules Team (AK).
159 --
160 -- Desc Creates an attribute using the given info. This
161 -- API should only be called by other APIs that are
162 -- owned by the Core Modules Team (AK).
163 --
164 -- Results The API returns the standard p_return_status parameter
165 -- indicating one of the standard return statuses :
166 -- * Unexpected error
167 -- * Error
168 -- * Success
169 -- Parameters Attribute columns
170 -- p_loader_timestamp : IN optional
171 -- If a timestamp is passed, the API will create the
172 -- record using this timestamp. Only the upload API
173 -- should call with this parameter loaded.
174 --
175 -- Version Initial version number = 1.0
176 -- History Current version number = 1.0
177 --=======================================================
178 procedure CREATE_EXCLUDED_ITEM (
179 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
180 p_api_version_number IN NUMBER,
181 p_init_msg_tbl IN BOOLEAN := FALSE,
182 p_msg_count OUT NOCOPY NUMBER,
183 p_msg_data OUT NOCOPY VARCHAR2,
184 p_return_status OUT NOCOPY VARCHAR2,
185 p_responsibility_id IN NUMBER,
186 p_resp_application_id IN NUMBER,
187 p_attribute_code IN VARCHAR2,
188 p_attribute_application_id IN NUMBER,
189 p_created_by IN NUMBER := FND_API.G_MISS_NUM,
190 p_creation_date IN DATE := FND_API.G_MISS_DATE,
191 p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
192 p_last_update_date IN DATE := FND_API.G_MISS_DATE,
193 p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
194 p_loader_timestamp IN DATE := FND_API.G_MISS_DATE
195 ) is
196 l_api_version_number CONSTANT number := 1.0;
197 l_api_name CONSTANT varchar2(30) := 'Create_Excluded_Item';
198 l_created_by number;
199 l_creation_date date;
200 l_lang varchar2(30);
201 l_last_update_date date;
202 l_last_update_login number;
203 l_last_updated_by number;
204 l_return_status varchar2(1);
205 l_upper_case_flag VARCHAR2(1) := null;
206 begin
207
208 IF NOT FND_API.Compatible_API_Call (
209 l_api_version_number, p_api_version_number, l_api_name,
210 G_PKG_NAME) then
211 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
212 return;
213 END IF;
214
215 -- Initialize the message table if requested.
216
217 if p_init_msg_tbl then
218 FND_MSG_PUB.initialize;
219 end if;
220
221
222 savepoint start_create_excluded_item;
223
224 --
225 -- check to see if row already exists
226 --
227 --dbms_output.put_line('Call Excluded_item_exists');
228 if AK_SECURITY_PVT.EXCLUDED_ITEM_EXISTS (
229 p_api_version_number => 1.0,
230 p_return_status => l_return_status,
231 p_responsibility_id => p_responsibility_id,
232 p_resp_application_id => p_resp_application_id,
233 p_attribute_code => p_attribute_code,
234 p_attribute_application_id => p_attribute_application_id) then
235 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
236 FND_MESSAGE.SET_NAME('AK','AK_EXCL_ITEM_EXISTS');
237 FND_MSG_PUB.Add;
238 end if;
239 raise FND_API.G_EXC_ERROR;
240 end if;
241
242 --
243 -- validate table columns passed in
244 --
245 if (p_validation_level <> FND_API.G_VALID_LEVEL_NONE) then
246 --
247 -- Validate all columns passed in
248 --
249 --dbms_output.put_line('Call validate_security');
250 if NOT VALIDATE_SECURITY(
251 p_validation_level => p_validation_level,
252 p_api_version_number => 1.0,
253 p_return_status => l_return_status,
254 p_responsibility_id => p_responsibility_id,
255 p_responsibility_appl_id => p_resp_application_id,
256 p_attribute_application_id => p_attribute_application_id,
257 p_attribute_code => p_attribute_code,
258 p_caller => AK_ON_OBJECTS_PVT.G_CREATE
259 ) then
260 raise FND_API.G_EXC_ERROR;
261 end if;
262 end if;
263
264
265 if (p_created_by <> FND_API.G_MISS_NUM) then
266 l_created_by := p_created_by;
267 end if;
268
269 if (p_creation_date <> FND_API.G_MISS_DATE) then
270 l_creation_date := p_creation_date;
271 end if;
272
273 if (p_last_updated_by <> FND_API.G_MISS_NUM) then
274 l_last_updated_by := p_last_updated_by;
275 end if;
276
277 if (p_last_update_date <> FND_API.G_MISS_DATE) then
278 l_last_update_date := p_last_update_date;
279 end if;
280
281 if (p_last_update_login <> FND_API.G_MISS_NUM) then
282 l_last_update_login := p_last_update_login;
283 end if;
284
285 --
286 -- Create record if no validation error was found
287 --
288 -- Set WHO columns
289 --
290 -- NOTE - Calling IS_UPDATEABLE for backward compatibility
291 -- old jlt files didn't have who columns and IS_UPDATEABLE
292 -- calls SET_WHO which populates those columns, for later
293 -- jlt files IS_UPDATEABLE will always return TRUE for CREATE
294
295 if AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
296 p_loader_timestamp => p_loader_timestamp,
297 p_created_by => l_created_by,
298 p_creation_date => l_creation_date,
299 p_last_updated_by => l_last_updated_by,
300 p_db_last_updated_by => null,
301 p_last_update_date => l_last_update_date,
302 p_db_last_update_date => null,
303 p_last_update_login => l_last_update_login,
304 p_create_or_update => 'CREATE') then
305 null;
306 end if;
307
308 select userenv('LANG') into l_lang
309 from dual;
310
311 insert into AK_EXCLUDED_ITEMS (
312 RESPONSIBILITY_ID,
313 RESP_APPLICATION_ID,
314 ATTRIBUTE_CODE,
315 ATTRIBUTE_APPLICATION_ID,
316 CREATION_DATE,
317 CREATED_BY,
318 LAST_UPDATE_DATE,
319 LAST_UPDATED_BY,
320 LAST_UPDATE_LOGIN
321 ) values (
322 p_responsibility_id,
323 p_resp_application_id,
324 p_attribute_code,
325 p_attribute_application_id,
326 l_creation_date,
327 l_created_by,
328 l_last_update_date,
329 l_last_updated_by,
330 l_last_update_login
331 );
332
333 -- /** commit the insert **/
334 -- commit;
335
336 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
337 FND_MESSAGE.SET_NAME('AK','AK_EXCL_ITEM_CREATED');
338 FND_MESSAGE.SET_TOKEN('KEY',to_char(p_responsibility_id)||
339 ' '||to_char(p_resp_application_id)||
340 ' '||to_char(p_attribute_application_id) ||
341 ' ' || p_attribute_code);
342 FND_MSG_PUB.Add;
343 end if;
344
345 p_return_status := FND_API.G_RET_STS_SUCCESS;
346
347 FND_MSG_PUB.Count_And_Get (
348 p_count => p_msg_count,
349 p_data => p_msg_data);
350
351
352 EXCEPTION
353 WHEN VALUE_ERROR THEN
354 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
355 FND_MESSAGE.SET_NAME('AK','AK_EXCL_ITEM_VALUE_ERROR');
356 FND_MESSAGE.SET_TOKEN('KEY',to_char(p_attribute_application_id) ||
357 ' ' || p_attribute_code);
358 FND_MESSAGE.SET_NAME('AK','AK_EXCL_ITEM_NOT_CREATED');
359 FND_MESSAGE.SET_TOKEN('KEY',to_char(p_responsibility_id)||
360 ' '||to_char(p_resp_application_id)||
361 ' '||to_char(p_attribute_application_id) ||
362 ' ' || p_attribute_code);
363 FND_MSG_PUB.Add;
364 end if;
365 p_return_status := FND_API.G_RET_STS_ERROR;
366 rollback to start_create_excluded_item;
367 FND_MSG_PUB.Count_And_Get (
368 p_count => p_msg_count,
369 p_data => p_msg_data);
370 WHEN FND_API.G_EXC_ERROR THEN
371 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
372 FND_MESSAGE.SET_NAME('AK','AK_EXCL_ITEM_NOT_CREATED');
373 FND_MESSAGE.SET_TOKEN('KEY',to_char(p_responsibility_id)||
374 ' '||to_char(p_resp_application_id)||
375 ' '||to_char(p_attribute_application_id) ||
376 ' ' || p_attribute_code);
377 FND_MSG_PUB.Add;
378 end if;
379 p_return_status := FND_API.G_RET_STS_ERROR;
380 rollback to start_create_excluded_item;
381 FND_MSG_PUB.Count_And_Get (
382 p_count => p_msg_count,
383 p_data => p_msg_data);
384 WHEN OTHERS THEN
385 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
386 rollback to start_create_excluded_item;
387 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name,
388 SUBSTR (SQLERRM, 1, 240) );
389 FND_MSG_PUB.Count_And_Get (
390 p_count => p_msg_count,
391 p_data => p_msg_data);
392
393
394 end CREATE_EXCLUDED_ITEM;
395
396 --=======================================================
397 -- Procedure CREATE_RESP_SECURITY_ATTR
398 --
399 -- Usage Private API for creating an attribute. This
400 -- API should only be called by other APIs that are
401 -- owned by the Core Modules Team (AK).
402 --
403 -- Desc Creates an attribute using the given info. This
404 -- API should only be called by other APIs that are
405 -- owned by the Core Modules Team (AK).
406 --
407 -- Results The API returns the standard p_return_status parameter
408 -- indicating one of the standard return statuses :
409 -- * Unexpected error
410 -- * Error
411 -- * Success
412 -- Parameters Attribute columns
413 -- p_loader_timestamp : IN optional
414 -- If a timestamp is passed, the API will create the
415 -- record using this timestamp. Only the upload API
416 -- should call with this parameter loaded.
417 --
418 -- Version Initial version number = 1.0
419 -- History Current version number = 1.0
420 --=======================================================
421 procedure CREATE_RESP_SECURITY_ATTR (
422 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
423 p_api_version_number IN NUMBER,
424 p_init_msg_tbl IN BOOLEAN := FALSE,
425 p_msg_count OUT NOCOPY NUMBER,
426 p_msg_data OUT NOCOPY VARCHAR2,
427 p_return_status OUT NOCOPY VARCHAR2,
428 p_responsibility_id IN NUMBER,
429 p_resp_application_id IN NUMBER,
430 p_attribute_code IN VARCHAR2,
431 p_attribute_application_id IN NUMBER,
432 p_created_by IN NUMBER := FND_API.G_MISS_NUM,
433 p_creation_date IN DATE := FND_API.G_MISS_DATE,
434 p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
435 p_last_update_date IN DATE := FND_API.G_MISS_DATE,
436 p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
437 p_loader_timestamp IN DATE := FND_API.G_MISS_DATE
438 ) is
439 l_api_version_number CONSTANT number := 1.0;
440 l_api_name CONSTANT varchar2(30) := 'Create_Resp_Security_Attr';
441 l_created_by number;
442 l_creation_date date;
443 l_lang varchar2(30);
444 l_last_update_date date;
445 l_last_update_login number;
446 l_last_updated_by number;
447 l_return_status varchar2(1);
448 l_upper_case_flag VARCHAR2(1) := null;
449 begin
450
451 IF NOT FND_API.Compatible_API_Call (
452 l_api_version_number, p_api_version_number, l_api_name,
453 G_PKG_NAME) then
454 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
455 return;
456 END IF;
457
458 -- Initialize the message table if requested.
459
460 if p_init_msg_tbl then
461 FND_MSG_PUB.initialize;
462 end if;
463
464
465 savepoint start_create_excluded_item;
466
467 --
468 -- check to see if row already exists
469 --
470 if AK_SECURITY_PVT.RESP_SECURITY_ATTR_EXISTS (
471 p_api_version_number => 1.0,
472 p_return_status => l_return_status,
473 p_responsibility_id => p_responsibility_id,
474 p_resp_application_id => p_resp_application_id,
475 p_attribute_code => p_attribute_code,
476 p_attribute_application_id => p_attribute_application_id) then
477 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
478 FND_MESSAGE.SET_NAME('AK','AK_RESP_SEC_ATTR_EXISTS');
479 FND_MSG_PUB.Add;
480 end if;
481 raise FND_API.G_EXC_ERROR;
482 end if;
483
484 --
485 -- validate table columns passed in
486 --
487 if (p_validation_level <> FND_API.G_VALID_LEVEL_NONE) then
488 --
489 -- Validate all columns passed in
490 --
491 if NOT VALIDATE_SECURITY(
492 p_validation_level => p_validation_level,
493 p_api_version_number => 1.0,
494 p_return_status => l_return_status,
495 p_responsibility_id => p_responsibility_id,
496 p_responsibility_appl_id => p_resp_application_id,
497 p_attribute_code => p_attribute_code,
498 p_attribute_application_id => p_attribute_application_id,
499 p_caller => AK_ON_OBJECTS_PVT.G_CREATE
500 ) then
501 raise FND_API.G_EXC_ERROR;
502 end if;
503 end if;
504
505
506 if (p_created_by <> FND_API.G_MISS_NUM) then
507 l_created_by := p_created_by;
508 end if;
509
510 if (p_creation_date <> FND_API.G_MISS_DATE) then
511 l_creation_date := p_creation_date;
512 end if;
513
514 if (p_last_updated_by <> FND_API.G_MISS_NUM) then
515 l_last_updated_by := p_last_updated_by;
516 end if;
517
518 if (p_last_update_date <> FND_API.G_MISS_DATE) then
519 l_last_update_date := p_last_update_date;
520 end if;
521
522 if (p_last_update_login <> FND_API.G_MISS_NUM) then
523 l_last_update_login := p_last_update_login;
524 end if;
525
526 --
527 -- Create record if no validation error was found
528 --
529 -- NOTE - Calling IS_UPDATEABLE for backward compatibility
530 -- old jlt files didn't have who columns and IS_UPDATEABLE
531 -- calls SET_WHO which populates those columns, for later
532 -- jlt files IS_UPDATEABLE will always return TRUE for CREATE
533
534 if AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
535 p_loader_timestamp => p_loader_timestamp,
536 p_created_by => l_created_by,
537 p_creation_date => l_creation_date,
538 p_last_updated_by => l_last_updated_by,
539 p_db_last_updated_by => null,
540 p_last_update_date => l_last_update_date,
541 p_db_last_update_date => null,
542 p_last_update_login => l_last_update_login,
543 p_create_or_update => 'CREATE') then
544 null;
545 end if;
546
547 select userenv('LANG') into l_lang
548 from dual;
549
550 insert into AK_RESP_SECURITY_ATTRIBUTES (
551 RESPONSIBILITY_ID,
552 RESP_APPLICATION_ID,
553 ATTRIBUTE_CODE,
554 ATTRIBUTE_APPLICATION_ID,
555 CREATION_DATE,
556 CREATED_BY,
557 LAST_UPDATE_DATE,
558 LAST_UPDATED_BY,
559 LAST_UPDATE_LOGIN
560 ) values (
561 p_responsibility_id,
562 p_resp_application_id,
563 p_attribute_code,
564 p_attribute_application_id,
565 l_creation_date,
566 l_created_by,
567 l_last_update_date,
568 l_last_updated_by,
569 l_last_update_login
570 );
571
572 -- /** commit the insert **/
573 -- commit;
574
575 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
576 FND_MESSAGE.SET_NAME('AK','AK_RESP_SEC_ATTR_CREATED');
577 FND_MESSAGE.SET_TOKEN('KEY',to_char(p_responsibility_id)||
578 ' '||to_char(p_resp_application_id)||
579 ' '||to_char(p_attribute_application_id) ||
580 ' ' || p_attribute_code);
581 FND_MSG_PUB.Add;
582 end if;
583
584 p_return_status := FND_API.G_RET_STS_SUCCESS;
585
586 FND_MSG_PUB.Count_And_Get (
587 p_count => p_msg_count,
588 p_data => p_msg_data);
589
590
591 EXCEPTION
592 WHEN VALUE_ERROR THEN
593 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
594 FND_MESSAGE.SET_NAME('AK','AK_RESP_SEC_ATTR_VALUE_ERROR');
595 FND_MESSAGE.SET_TOKEN('KEY',to_char(p_responsibility_id)||
596 ' '||to_char(p_resp_application_id)||
597 ' '||to_char(p_attribute_application_id) ||
598 ' ' || p_attribute_code);
599 FND_MESSAGE.SET_NAME('AK','AK_RESP_SEC_ATTR_NOT_CREATED');
600 FND_MESSAGE.SET_TOKEN('KEY',to_char(p_responsibility_id)||
601 ' '||to_char(p_resp_application_id)||
602 ' '||to_char(p_attribute_application_id) ||
603 ' ' || p_attribute_code);
604 FND_MSG_PUB.Add;
605 end if;
606 p_return_status := FND_API.G_RET_STS_ERROR;
607 rollback to start_create_excluded_item;
608 FND_MSG_PUB.Count_And_Get (
609 p_count => p_msg_count,
610 p_data => p_msg_data);
611 WHEN FND_API.G_EXC_ERROR THEN
612 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
613 FND_MESSAGE.SET_NAME('AK','AK_RESP_SEC_ATTR_NOT_CREATED');
614 FND_MESSAGE.SET_TOKEN('KEY',to_char(p_responsibility_id)||
615 ' '||to_char(p_resp_application_id)||
616 ' '||to_char(p_attribute_application_id) ||
617 ' ' || p_attribute_code);
618 FND_MSG_PUB.Add;
619 end if;
620 p_return_status := FND_API.G_RET_STS_ERROR;
621 rollback to start_create_excluded_item;
622 FND_MSG_PUB.Count_And_Get (
623 p_count => p_msg_count,
624 p_data => p_msg_data);
625 WHEN OTHERS THEN
626 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
627 rollback to start_create_excluded_item;
628 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name,
629 SUBSTR (SQLERRM, 1, 240) );
630 FND_MSG_PUB.Count_And_Get (
631 p_count => p_msg_count,
632 p_data => p_msg_data);
633
634
635 end CREATE_RESP_SECURITY_ATTR;
636
637 --=======================================================
638 -- Procedure WRITE_RESP_SEC_ATTR_TO_BUFFER (local procedure)
639 --
640 -- Usage Local procedure for writing one resp sec attribute to
641 -- the output file. Not designed to be called
642 -- from outside this package.
643 --
644 -- Desc Appends the single attribute passed in through the
645 -- parameters to the specified output file. The
646 -- output will be in loader file format.
647 --
648 -- Results The API returns the standard p_return_status parameter
649 -- indicating one of the standard return statuses :
650 -- * Unexpected error
651 -- * Error
652 -- * Success
653 -- Parameters Excluded items record.
654 --=======================================================
655 procedure WRITE_RESP_SEC_ATTR_TO_BUFFER (
656 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
657 p_return_status OUT NOCOPY VARCHAR2,
658 p_resp_sec_attr_rec IN ak_resp_security_attributes%ROWTYPE
659 ) is
660 l_api_name CONSTANT varchar2(30) := 'Write_Resp_Sec_Attr_to_buffer';
661 l_databuffer_tbl AK_ON_OBJECTS_PUB.Buffer_Tbl_Type;
662 l_index NUMBER;
663 l_return_status varchar2(1);
664 begin
665 --
666 -- Attribute must be validated before it is written to the file
667 --
668
669 if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
670 if not VALIDATE_SECURITY (
671 p_validation_level => p_validation_level,
672 p_api_version_number => 1.0,
673 p_return_status => l_return_status,
674 p_responsibility_appl_id =>
675 p_resp_sec_attr_rec.resp_application_id,
676 p_responsibility_id => p_resp_sec_attr_rec.responsibility_id,
677 p_attribute_application_id =>
678 p_resp_sec_attr_rec.attribute_application_id,
679 p_attribute_code => p_resp_sec_attr_rec.attribute_code,
680 p_caller => AK_ON_OBJECTS_PVT.G_DOWNLOAD)
681 then
682 --dbms_output.put_line('Responsibility_id ' || to_char(p_resp_sec_attr_rec.responsibility_id)
683 -- || ' not downloaded due to validation error');
684 raise FND_API.G_EXC_ERROR;
685 end if;
686 end if;
687
688 --
689 -- Write excluded items record into buffer
690 --
691 l_databuffer_tbl.DELETE;
692 l_index := 1;
693
694 l_databuffer_tbl(l_index) := 'BEGIN RESP_SECURITY_ATTRIBUTES ' ||
695 nvl(to_char(p_resp_sec_attr_rec.responsibility_id),'""') ||' '||
696 nvl(to_char(p_resp_sec_attr_rec.resp_application_id),'""') ||' '||
697 nvl(to_char(p_resp_sec_attr_rec.attribute_application_id),'""')||' "'||
698 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
699 p_resp_sec_attr_rec.attribute_code)|| '"';
700 -- - Write out who columns
701 l_index := l_index + 1;
702 l_databuffer_tbl(l_index) := ' CREATED_BY = ' ||
703 nvl(to_char(p_resp_sec_attr_rec.created_by),'""');
704 l_index := l_index + 1;
705 l_databuffer_tbl(l_index) := ' CREATION_DATE = "' ||
706 to_char(p_resp_sec_attr_rec.creation_date,
707 AK_ON_OBJECTS_PUB.G_DATE_FORMAT) || '"';
708 l_index := l_index + 1;
709 -- CHANGED TO OWNER FOR R12
710 --l_databuffer_tbl(l_index) := ' LAST_UPDATED_BY = ' ||
711 --nvl(to_char(p_resp_sec_attr_rec.last_updated_by),'""');
712 l_databuffer_tbl(l_index) := ' OWNER = ' ||
713 FND_LOAD_UTIL.OWNER_NAME(p_resp_sec_attr_rec.last_updated_by) || '"';
714 l_index := l_index + 1;
715 l_databuffer_tbl(l_index) := ' LAST_UPDATE_DATE = "' ||
716 to_char(p_resp_sec_attr_rec.last_update_date,
717 AK_ON_OBJECTS_PUB.G_DATE_FORMAT) || '"';
718 l_index := l_index + 1;
719 l_databuffer_tbl(l_index) := ' LAST_UPDATE_LOGIN = ' ||
720 nvl(to_char(p_resp_sec_attr_rec.last_update_login),'""');
721
722 l_index := l_index + 1;
723 l_databuffer_tbl(l_index) := 'END RESP_SECURITY_ATTRIBUTES';
724 l_index := l_index + 1;
725 l_databuffer_tbl(l_index) := ' ';
726
727 --
728 -- - Write attribute data out to the specified file
729 --
730 AK_ON_OBJECTS_PVT.WRITE_FILE (
731 p_return_status => l_return_status,
732 p_buffer_tbl => l_databuffer_tbl,
733 p_write_mode => AK_ON_OBJECTS_PUB.G_APPEND
734 );
735
736 --
737 -- If API call returns with an error status...
738 --
739 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
740 (l_return_status = FND_API.G_RET_STS_ERROR) then
741 RAISE FND_API.G_EXC_ERROR;
742 end if;
743
744 p_return_status := FND_API.G_RET_STS_SUCCESS;
745
746 EXCEPTION
747 WHEN VALUE_ERROR THEN
748 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
749 FND_MESSAGE.SET_NAME('AK','AK_RESP_SEC_ATTR_VALUE_ERROR');
750 FND_MESSAGE.SET_TOKEN('KEY',
751 to_char(p_resp_sec_attr_rec.attribute_application_id) ||
752 ' ' || p_resp_sec_attr_rec.attribute_code);
753 FND_MSG_PUB.Add;
754 FND_MESSAGE.SET_NAME('AK','AK_RESP_SEC_ATTR_NOT_DWNLOADED');
755 FND_MESSAGE.SET_TOKEN('KEY',
756 to_char(p_resp_sec_attr_rec.attribute_application_id) ||
757 ' ' || p_resp_sec_attr_rec.attribute_code);
758 FND_MSG_PUB.Add;
759 end if;
760 p_return_status := FND_API.G_RET_STS_ERROR;
761 WHEN FND_API.G_EXC_ERROR THEN
762 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
763 FND_MESSAGE.SET_NAME('AK','AK_RESP_SEC_ATTR_NOT_DWNLOADED');
764 FND_MESSAGE.SET_TOKEN('KEY',
765 to_char(p_resp_sec_attr_rec.attribute_application_id) ||
766 ' ' || p_resp_sec_attr_rec.attribute_code);
767 FND_MSG_PUB.Add;
768 end if;
769 p_return_status := FND_API.G_RET_STS_ERROR;
770 WHEN OTHERS THEN
771 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
772 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
773 SUBSTR (SQLERRM, 1, 240) );
774 FND_MSG_PUB.Add;
775 end WRITE_RESP_SEC_ATTR_TO_BUFFER;
776
777 --=======================================================
778 -- Procedure WRITE_TO_BUFFER (local procedure)
779 --
780 -- Usage Local procedure for writing one excluded item to
781 -- the output file. Not designed to be called
782 -- from outside this package.
783 --
784 -- Desc Appends the single attribute passed in through the
785 -- parameters to the specified output file. The
786 -- output will be in loader file format.
787 --
788 -- Results The API returns the standard p_return_status parameter
789 -- indicating one of the standard return statuses :
790 -- * Unexpected error
791 -- * Error
792 -- * Success
793 -- Parameters Excluded items record.
794 --=======================================================
795 procedure WRITE_TO_BUFFER (
796 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
797 p_return_status OUT NOCOPY VARCHAR2,
798 p_excluded_rec IN ak_excluded_items%ROWTYPE
799 ) is
800 l_api_name CONSTANT varchar2(30) := 'Write_to_buffer';
801 l_databuffer_tbl AK_ON_OBJECTS_PUB.Buffer_Tbl_Type;
802 l_index NUMBER;
803 l_lov_object VARCHAR2(30);
804 l_return_status varchar2(1);
805 begin
806 --
807 -- Attribute must be validated before it is written to the file
808 --
809 if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
810 if not VALIDATE_SECURITY (
811 p_validation_level => p_validation_level,
812 p_api_version_number => 1.0,
813 p_return_status => l_return_status,
814 p_responsibility_appl_id =>
815 p_excluded_rec.resp_application_id,
816 p_responsibility_id => p_excluded_rec.responsibility_id,
817 p_attribute_application_id =>
818 p_excluded_rec.attribute_application_id,
819 p_attribute_code => p_excluded_rec.attribute_code,
820 p_caller => AK_ON_OBJECTS_PVT.G_DOWNLOAD)
821 then
822 --dbms_output.put_line('Responsibility_id ' || to_char(p_excluded_rec.responsibility_id)
823 -- || ' not downloaded due to validation error');
824 raise FND_API.G_EXC_ERROR;
825 end if;
826 end if;
827
828 --
829 -- Write excluded items record into buffer
830 --
831 l_databuffer_tbl.DELETE;
832 l_index := 1;
833
834 l_databuffer_tbl(l_index) := 'BEGIN EXCLUDED_ITEMS ' ||
835 nvl(to_char(p_excluded_rec.responsibility_id),'""') ||' '||
836 nvl(to_char(p_excluded_rec.resp_application_id),'""') ||' '||
837 nvl(to_char(p_excluded_rec.attribute_application_id),'""')||' "'||
838 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(
839 p_excluded_rec.attribute_code)|| '"';
840 -- - Write out who columns
841 l_index := l_index + 1;
842 l_databuffer_tbl(l_index) := ' CREATED_BY = ' ||
843 nvl(to_char(p_excluded_rec.created_by),'""');
844 l_index := l_index + 1;
845 l_databuffer_tbl(l_index) := ' CREATION_DATE = "' ||
846 to_char(p_excluded_rec.creation_date,
847 AK_ON_OBJECTS_PUB.G_DATE_FORMAT) || '"';
848 l_index := l_index + 1;
849 -- CHANGED TO OWNER FOR R12
850 --l_databuffer_tbl(l_index) := ' LAST_UPDATED_BY = ' ||
851 --nvl(to_char(p_excluded_rec.last_updated_by),'""');
852 l_databuffer_tbl(l_index) := ' OWNER = ' ||
853 FND_LOAD_UTIL.OWNER_NAME(p_excluded_rec.last_updated_by) || '"';
854 l_index := l_index + 1;
855 l_databuffer_tbl(l_index) := ' LAST_UPDATE_DATE = "' ||
856 to_char(p_excluded_rec.last_update_date,
857 AK_ON_OBJECTS_PUB.G_DATE_FORMAT) || '"';
858 l_index := l_index + 1;
859 l_databuffer_tbl(l_index) := ' LAST_UPDATE_LOGIN = ' ||
860 nvl(to_char(p_excluded_rec.last_update_login),'""');
861
862 l_index := l_index + 1;
863 l_databuffer_tbl(l_index) := 'END EXCLUDED_ITEMS';
864 l_index := l_index + 1;
865 l_databuffer_tbl(l_index) := ' ';
866
867 --
868 -- - Write attribute data out to the specified file
869 --
870 AK_ON_OBJECTS_PVT.WRITE_FILE (
871 p_return_status => l_return_status,
872 p_buffer_tbl => l_databuffer_tbl,
873 p_write_mode => AK_ON_OBJECTS_PUB.G_APPEND
874 );
875
876 --
877 -- If API call returns with an error status...
878 --
879 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
880 (l_return_status = FND_API.G_RET_STS_ERROR) then
881 RAISE FND_API.G_EXC_ERROR;
882 end if;
883
884 p_return_status := FND_API.G_RET_STS_SUCCESS;
885
886 EXCEPTION
887 WHEN VALUE_ERROR THEN
888 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
889 FND_MESSAGE.SET_NAME('AK','AK_EXCL_ITEM_VALUE_ERROR');
890 FND_MESSAGE.SET_TOKEN('KEY',
891 to_char(p_excluded_rec.attribute_application_id) ||
892 ' ' || p_excluded_rec.attribute_code);
893 FND_MSG_PUB.Add;
894 FND_MESSAGE.SET_NAME('AK','AK_EXCL_ITEM_NOT_DOWNLOADED');
895 FND_MESSAGE.SET_TOKEN('KEY',
896 to_char(p_excluded_rec.attribute_application_id) ||
897 ' ' || p_excluded_rec.attribute_code);
898 FND_MSG_PUB.Add;
899 end if;
900 p_return_status := FND_API.G_RET_STS_ERROR;
901 WHEN FND_API.G_EXC_ERROR THEN
902 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
903 FND_MESSAGE.SET_NAME('AK','AK_EXCL_ITEM_NOT_DOWNLOADED');
904 FND_MESSAGE.SET_TOKEN('KEY',
905 to_char(p_excluded_rec.attribute_application_id) ||
906 ' ' || p_excluded_rec.attribute_code);
907 FND_MSG_PUB.Add;
908 end if;
909 p_return_status := FND_API.G_RET_STS_ERROR;
910 WHEN OTHERS THEN
911 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
912 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
913 SUBSTR (SQLERRM, 1, 240) );
914 FND_MSG_PUB.Add;
915 end WRITE_TO_BUFFER;
916
917 --=======================================================
918 -- Procedure DOWNLOAD_EXCLUDED
919 --
920 -- Usage Private API for downloading excluded_items. This
921 -- API should only be called by other APIs that are
922 -- owned by the Core Modules Team (AK).
923 --
924 -- Desc This API will extract the attributes selected
925 -- by application ID or by key values from the
926 -- database to the output file.
927 --
928 -- Results The API returns the standard p_return_status parameter
929 -- indicating one of the standard return statuses :
930 -- * Unexpected error
931 -- * Error
932 -- * Success
933 -- Parameters
934 -- p_nls_language : IN optional
935 -- NLS language for database. If none if given,
936 -- the current NLS language will be used.
937 --
938 -- One of the following parameters must be provided:
939 --
940 -- p_application_id : IN optional
941 -- If given, all attributes for this application ID
942 -- will be written to the output file.
943 -- p_application_id will be ignored if a table is
944 -- given in p_attribute_pk_tbl.
945 -- p_resp_pk_tbl : IN optional
946 -- If given, only ICX tables whose key values are
947 -- included in this table will be written to the
948 -- output file.
949 --
950 --
951 -- Version Initial version number = 1.0
952 -- History Current version number = 1.0
953 --=======================================================
954 procedure DOWNLOAD_EXCLUDED (
955 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
956 p_api_version_number IN NUMBER,
957 p_return_status OUT NOCOPY VARCHAR2,
958 p_application_id IN NUMBER := FND_API.G_MISS_NUM,
959 p_excluded_pk_tbl IN AK_SECURITY_PUB.Resp_PK_Tbl_Type :=
960 AK_SECURITY_PUB.G_MISS_RESP_PK_TBL,
961 p_nls_language IN VARCHAR2
962 ) is
963 cursor l_get_resp_1_csr (appl_id_parm number) is
964 select *
965 from AK_EXCLUDED_ITEMS
966 where RESP_APPLICATION_ID = appl_id_parm;
967 cursor l_get_resp_2_csr (appl_id_parm number, resp_id_parm number) is
968 select *
969 from AK_EXCLUDED_ITEMS
970 where RESP_APPLICATION_ID = appl_id_parm
971 and RESPONSIBILITY_ID = resp_id_parm;
972 l_api_version_number CONSTANT number := 1.0;
973 l_api_name CONSTANT varchar2(30) := 'Download Excluded Items';
974 l_responsibility_found BOOLEAN;
975 i number;
976 l_responsibility_appl_id NUMBER;
977 l_excluded_rec ak_excluded_items%ROWTYPE;
978 l_return_status varchar2(1);
979 l_select_by_appl_id BOOLEAN;
980 begin
981
982
983 IF NOT FND_API.Compatible_API_Call (
984 l_api_version_number, p_api_version_number, l_api_name,
985 G_PKG_NAME) then
986 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
987 return;
988 END IF;
989 --
990 -- Check that one of the following selection criteria is given:
991 -- - p_application_id alone, or
992 -- - attribute_application_id and attribute_code pairs in
993 -- p_excluded_pk_tbl, or
994 -- - both p_application_id and p_excluded_pk_tbl if any
995 -- p_attribute_application_id is missing in p_excluded_pk_tbl
996 --
997 if (p_application_id = FND_API.G_MISS_NUM) then
998 if (p_excluded_pk_tbl.count = 0) then
999 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1000 FND_MESSAGE.SET_NAME('AK','AK_NO_SELECTION');
1001 FND_MSG_PUB.Add;
1002 end if;
1003 raise FND_API.G_EXC_ERROR;
1004 else
1005 --
1006 -- since no application ID is passed in thru p_application_id,
1007 -- none of the responsibility_appl_id or responsibility_id
1008 -- in table can be null
1009 --
1010
1011 for i in p_excluded_pk_tbl.FIRST .. p_excluded_pk_tbl.LAST LOOP
1012 if (p_excluded_pk_tbl.exists(i)) then
1013 if (p_excluded_pk_tbl(i).responsibility_appl_id = FND_API.G_MISS_NUM) or
1014 (p_excluded_pk_tbl(i).responsibility_id = FND_API.G_MISS_NUM)
1015 then
1016 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1017 FND_MESSAGE.SET_NAME('AK','AK_INVALID_LIST');
1018 FND_MESSAGE.SET_TOKEN('ELEMENT_NUM',to_char(i));
1019 FND_MSG_PUB.Add;
1020 end if;
1021 raise FND_API.G_EXC_ERROR;
1022 end if; /* if responsibility_appl_id is null */
1023 end if; /* if exists */
1024 end LOOP;
1025
1026 end if;
1027 end if;
1028
1029 --
1030 -- selection is by application ID if the excluded items list table is empty
1031 --
1032 if (p_excluded_pk_tbl.count = 0) then
1033 l_select_by_appl_id := TRUE;
1034 else
1035 l_select_by_appl_id := FALSE;
1036 end if;
1037
1038 --
1039 -- Retrieve excluded items from AK_EXCLUDED_ITEMS that fits the selection
1040 -- criteria, one at a time, and write it the buffer table
1041 --
1042 if (l_select_by_appl_id) then
1043 --
1044 -- download by application ID
1045 --
1046 open l_get_resp_1_csr(p_application_id);
1047
1048 loop
1049 fetch l_get_resp_1_csr into l_excluded_rec;
1050 exit when l_get_resp_1_csr%notfound;
1051
1052 WRITE_TO_BUFFER(
1053 p_validation_level => p_validation_level,
1054 p_return_status => l_return_status,
1055 p_excluded_rec => l_excluded_rec
1056 );
1057 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1058 (l_return_status = FND_API.G_RET_STS_ERROR) then
1059 close l_get_resp_1_csr;
1060 RAISE FND_API.G_EXC_ERROR;
1061 end if;
1062
1063 end loop;
1064 close l_get_resp_1_csr;
1065
1066 else
1067 --
1068 -- download by list of excluded items
1069 --
1070
1071 for i in p_excluded_pk_tbl.FIRST .. p_excluded_pk_tbl.LAST LOOP
1072 if (p_excluded_pk_tbl.exists(i)) then
1073 --
1074 -- default application ID to p_application_id if not given
1075 --
1076 if (p_excluded_pk_tbl(i).responsibility_appl_id = FND_API.G_MISS_NUM) then
1077 l_responsibility_appl_id := p_application_id;
1078 else
1079 l_responsibility_appl_id := p_excluded_pk_tbl(i).responsibility_appl_id;
1080 end if;
1081
1082 --
1083 -- Retrieve attribute and its TL entry from the database
1084 --
1085 l_responsibility_found := TRUE;
1086 open l_get_resp_2_csr(l_responsibility_appl_id,
1087 p_excluded_pk_tbl(i).responsibility_id);
1088 fetch l_get_resp_2_csr into l_excluded_rec;
1089 if (l_get_resp_2_csr%notfound) then
1090 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
1091 FND_MESSAGE.SET_NAME('AK','AK_EXCL_ITEM_DOES_NOT_EXIST');
1092 FND_MSG_PUB.Add;
1093 FND_MESSAGE.SET_NAME('AK','AK_EXCL_ITEM_NOT_DOWNLOADED');
1094 FND_MESSAGE.SET_TOKEN('KEY', to_char(l_responsibility_appl_id) ||
1095 ' ' || p_excluded_pk_tbl(i).attribute_code);
1096 FND_MSG_PUB.Add;
1097 end if;
1098 l_responsibility_found := FALSE;
1099 end if;
1100 close l_get_resp_2_csr;
1101
1102 --
1103 -- write excluded items entry to buffer
1104 --
1105 if l_responsibility_found then
1106 WRITE_TO_BUFFER(
1107 p_validation_level => p_validation_level,
1108 p_return_status => l_return_status,
1109 p_excluded_rec => l_excluded_rec
1110 );
1111 --
1112 -- Download aborts when validation in WRITE_TO_BUFFER fails
1113 --
1114 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1115 (l_return_status = FND_API.G_RET_STS_ERROR) then
1116 RAISE FND_API.G_EXC_ERROR;
1117 end if;
1118 end if; /* if l_responsibility_found */
1119 end if; /* if exists(i) */
1120 end loop;
1121 end if;
1122
1123 p_return_status := FND_API.G_RET_STS_SUCCESS;
1124
1125 EXCEPTION
1126 WHEN FND_API.G_EXC_ERROR THEN
1127 p_return_status := FND_API.G_RET_STS_ERROR;
1128 WHEN OTHERS THEN
1129 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1130 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name,
1131 SUBSTR (SQLERRM, 1, 240) );
1132 end DOWNLOAD_EXCLUDED;
1133
1134
1135 --=======================================================
1136 -- Procedure DOWNLOAD_RESP_SEC
1137 --
1138 -- Usage Private API for downloading resp_attributes. This
1139 -- API should only be called by other APIs that are
1140 -- owned by the Core Modules Team (AK).
1141 --
1142 -- Desc This API will extract the attributes selected
1143 -- by application ID or by key values from the
1144 -- database to the output file.
1145 --
1146 -- Results The API returns the standard p_return_status parameter
1147 -- indicating one of the standard return statuses :
1148 -- * Unexpected error
1149 -- * Error
1150 -- * Success
1151 -- Parameters
1152 -- p_nls_language : IN optional
1153 -- NLS language for database. If none if given,
1154 -- the current NLS language will be used.
1155 --
1156 -- One of the following parameters must be provided:
1157 --
1158 -- p_application_id : IN optional
1159 -- If given, all attributes for this application ID
1160 -- will be written to the output file.
1161 -- p_application_id will be ignored if a table is
1162 -- given in p_attribute_pk_tbl.
1163 -- p_resp_pk_tbl : IN optional
1164 -- If given, only ICX tables whose key values are
1165 -- included in this table will be written to the
1166 -- output file.
1167 --
1168 --
1169 -- Version Initial version number = 1.0
1170 -- History Current version number = 1.0
1171 --=======================================================
1172 procedure DOWNLOAD_RESP_SEC (
1173 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1174 p_api_version_number IN NUMBER,
1175 p_return_status OUT NOCOPY VARCHAR2,
1176 p_application_id IN NUMBER := FND_API.G_MISS_NUM,
1177 p_resp_pk_tbl IN AK_SECURITY_PUB.Resp_PK_Tbl_Type :=
1178 AK_SECURITY_PUB.G_MISS_RESP_PK_TBL,
1179 p_nls_language IN VARCHAR2
1180 ) is
1181 cursor l_get_resp_1_csr (appl_id_parm number) is
1182 select *
1183 from AK_RESP_SECURITY_ATTRIBUTES
1184 where RESP_APPLICATION_ID = appl_id_parm;
1185 cursor l_get_resp_2_csr (appl_id_parm number, resp_id_parm number) is
1186 select *
1187 from AK_RESP_SECURITY_ATTRIBUTES
1188 where RESP_APPLICATION_ID = appl_id_parm
1189 and RESPONSIBILITY_ID = resp_id_parm;
1190 l_api_version_number CONSTANT number := 1.0;
1191 l_api_name CONSTANT varchar2(30) := 'Download Resp Sec Attributes';
1192 l_responsibility_found BOOLEAN;
1193 i number;
1194 l_responsibility_appl_id NUMBER;
1195 l_resp_rec ak_resp_security_attributes%ROWTYPE;
1196 l_return_status varchar2(1);
1197 l_select_by_appl_id BOOLEAN;
1198 begin
1199 IF NOT FND_API.Compatible_API_Call (
1200 l_api_version_number, p_api_version_number, l_api_name,
1201 G_PKG_NAME) then
1202 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1203 return;
1204 END IF;
1205
1206 --
1207 -- Check that one of the following selection criteria is given:
1208 -- - p_application_id alone, or
1209 -- - attribute_application_id and attribute_code pairs in
1210 -- p_resp_pk_tbl, or
1211 -- - both p_application_id and p_resp_pk_tbl if any
1212 -- p_attribute_application_id is missing in p_resp_pk_tbl
1213 --
1214 if (p_application_id = FND_API.G_MISS_NUM) then
1215 if (p_resp_pk_tbl.count = 0) then
1216 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1217 FND_MESSAGE.SET_NAME('AK','AK_NO_SELECTION');
1218 FND_MSG_PUB.Add;
1219 end if;
1220 raise FND_API.G_EXC_ERROR;
1221 else
1222 --
1223 -- since no application ID is passed in thru p_application_id,
1224 -- none of the responsibility_appl_id or responsibility_id
1225 -- in table can be null
1226 --
1227
1228 for i in p_resp_pk_tbl.FIRST .. p_resp_pk_tbl.LAST LOOP
1229 if (p_resp_pk_tbl.exists(i)) then
1230 if (p_resp_pk_tbl(i).responsibility_appl_id = FND_API.G_MISS_NUM) or
1231 (p_resp_pk_tbl(i).responsibility_id = FND_API.G_MISS_NUM)
1232 then
1233 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1234 FND_MESSAGE.SET_NAME('AK','AK_INVALID_LIST');
1235 FND_MESSAGE.SET_TOKEN('ELEMENT_NUM',to_char(i));
1236 FND_MSG_PUB.Add;
1237 end if;
1238 raise FND_API.G_EXC_ERROR;
1239 end if; /* if responsibility_appl_id is null */
1240 end if; /* if exists */
1241 end LOOP;
1242
1243 end if;
1244 end if;
1245
1246 --
1247 -- selection is by application ID if the excluded items list table is empty
1248 --
1249 if (p_resp_pk_tbl.count = 0) then
1250 l_select_by_appl_id := TRUE;
1251 else
1252 l_select_by_appl_id := FALSE;
1253 end if;
1254
1255 --
1256 -- Retrieve excluded items from AK_RESP_SECURITY_ATTRIBUTES that fits the selection
1257 -- criteria, one at a time, and write it the buffer table
1258 --
1259 if (l_select_by_appl_id) then
1260 --
1261 -- download by application ID
1262 --
1263 open l_get_resp_1_csr(p_application_id);
1264
1265 loop
1266 fetch l_get_resp_1_csr into l_resp_rec;
1267 exit when l_get_resp_1_csr%notfound;
1268
1269 WRITE_RESP_SEC_ATTR_TO_BUFFER(
1270 p_validation_level => p_validation_level,
1271 p_return_status => l_return_status,
1272 p_resp_sec_attr_rec => l_resp_rec
1273 );
1274 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1275 (l_return_status = FND_API.G_RET_STS_ERROR) then
1276 close l_get_resp_1_csr;
1277 RAISE FND_API.G_EXC_ERROR;
1278 end if;
1279
1280 end loop;
1281 close l_get_resp_1_csr;
1282
1283 else
1284 --
1285 -- download by list of resp security attributes
1286 --
1287 for i in p_resp_pk_tbl.FIRST .. p_resp_pk_tbl.LAST LOOP
1288 if (p_resp_pk_tbl.exists(i)) then
1289 --
1290 -- default application ID to p_application_id if not given
1291 --
1292 if (p_resp_pk_tbl(i).responsibility_appl_id = FND_API.G_MISS_NUM) then
1293 l_responsibility_appl_id := p_application_id;
1294 else
1295 l_responsibility_appl_id := p_resp_pk_tbl(i).responsibility_appl_id;
1296 end if;
1297
1298 --
1299 -- Retrieve resp security attribute entry from the database
1300 --
1301 l_responsibility_found := TRUE;
1302 open l_get_resp_2_csr(l_responsibility_appl_id,
1303 p_resp_pk_tbl(i).responsibility_id);
1304
1305 fetch l_get_resp_2_csr into l_resp_rec;
1306 if (l_get_resp_2_csr%notfound) then
1307 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
1308 FND_MESSAGE.SET_NAME('AK','AK_RESP_SEC_ATTR_DOES_NOT_EXIS');
1309 FND_MSG_PUB.Add;
1310 FND_MESSAGE.SET_NAME('AK','AK_RESP_SEC_ATTR_NOT_DWNLOADED');
1311 FND_MESSAGE.SET_TOKEN('KEY', to_char(l_responsibility_appl_id) ||
1312 ' ' || p_resp_pk_tbl(i).attribute_code);
1313 FND_MSG_PUB.Add;
1314 end if;
1315 l_responsibility_found := FALSE;
1316 end if;
1317 close l_get_resp_2_csr;
1318
1319 --
1320 -- write resp security attribute entry to buffer
1321 --
1322 if l_responsibility_found then
1323 WRITE_RESP_SEC_ATTR_TO_BUFFER(
1324 p_validation_level => p_validation_level,
1325 p_return_status => l_return_status,
1326 p_resp_sec_attr_rec => l_resp_rec
1327 );
1328 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1329 (l_return_status = FND_API.G_RET_STS_ERROR) then
1330 RAISE FND_API.G_EXC_ERROR;
1331 end if;
1332 end if; /* if l_responsibility_found */
1333 end if; /* if exists(i) */
1334 end loop;
1335 end if;
1336
1337 p_return_status := FND_API.G_RET_STS_SUCCESS;
1338
1339 EXCEPTION
1340 WHEN FND_API.G_EXC_ERROR THEN
1341 p_return_status := FND_API.G_RET_STS_ERROR;
1342 WHEN OTHERS THEN
1343 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1344 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name,
1345 SUBSTR (SQLERRM, 1, 240) );
1346 end DOWNLOAD_RESP_SEC;
1347
1348 --=======================================================
1349 -- Procedure INSERT_ATTRIBUTE_PK_TABLE
1350 --
1351 -- Usage Private API for inserting the given attribute's
1352 -- primary key value into the given attribute
1353 -- table.
1354 -- This API should only be called by other APIs
1355 -- that are owned by the Core Modules Team (AK).
1356 --
1357 -- Desc This API inserts the given attribute primary
1358 -- key value into a given attribute table
1359 -- (of type Attribute_PK_Tbl_Type) only if the
1360 -- primary key does not already exist in the table.
1361 --
1362 -- Results The API returns the standard p_return_status parameter
1363 -- indicating one of the standard return statuses :
1364 -- * Unexpected error
1365 -- * Error
1366 -- * Success
1367 -- Parameters p_attribute_application_id : IN required
1368 -- Application ID of the attribute to be inserted to the
1369 -- table.
1370 -- p_attribute_code : IN required
1371 -- Application code of the attribute to be inserted to the
1372 -- table.
1373 -- p_attribute_pk_tbl : IN OUT
1374 -- Attribute table to be updated.
1375 --
1376 -- Version Initial version number = 1.0
1377 -- History Current version number = 1.0
1378 --=======================================================
1379 procedure INSERT_ATTRIBUTE_PK_TABLE (
1380 p_return_status OUT NOCOPY VARCHAR2,
1381 p_attribute_application_id IN NUMBER,
1382 p_attribute_code IN VARCHAR2,
1383 p_attribute_pk_tbl IN OUT NOCOPY AK_ATTRIBUTE_PUB.Attribute_PK_Tbl_Type
1384 ) is
1385 l_api_version_number CONSTANT number := 1.0;
1386 l_api_name CONSTANT varchar2(30) := 'Insert_Attribute_PK_Table';
1387 l_index NUMBER;
1388 begin
1389 --
1390 -- if table is empty, just insert the attribute primary key into it
1391 --
1392 if (p_attribute_pk_tbl.count = 0) then
1393 p_attribute_pk_tbl(1).attribute_appl_id := p_attribute_application_id;
1394 p_attribute_pk_tbl(1).attribute_code := p_attribute_code;
1395 return;
1396 end if;
1397
1398 --
1399 -- otherwise, insert the attribute to the end of the table if it is
1400 -- not already in the table. If it is already in the table, return
1401 -- without changing the table.
1402 --
1403 for l_index in p_attribute_pk_tbl.FIRST .. p_attribute_pk_tbl.LAST loop
1404 if (p_attribute_pk_tbl.exists(l_index)) then
1405 if (p_attribute_pk_tbl(l_index).attribute_appl_id = p_attribute_application_id)
1406 and
1407 (p_attribute_pk_tbl(l_index).attribute_code = p_attribute_code) then
1408 return;
1409 end if;
1410 end if;
1411 end loop;
1412
1413 l_index := p_attribute_pk_tbl.LAST + 1;
1414 p_attribute_pk_tbl(l_index).attribute_appl_id := p_attribute_application_id;
1415 p_attribute_pk_tbl(l_index).attribute_code := p_attribute_code;
1416
1417 EXCEPTION
1418 WHEN FND_API.G_EXC_ERROR THEN
1419 p_return_status := FND_API.G_RET_STS_ERROR;
1420 WHEN OTHERS THEN
1421 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1422 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
1423 SUBSTR (SQLERRM, 1, 240) );
1424 FND_MSG_PUB.Add;
1425 end INSERT_ATTRIBUTE_PK_TABLE;
1426
1427 --=======================================================
1428 -- Procedure UPLOAD_SECURITY
1429 --
1430 -- Usage Private API for loading attributes from a
1431 -- loader file to the database.
1432 -- This API should only be called by other APIs
1433 -- that are owned by the Core Modules Team (AK).
1434 --
1435 -- Desc This API reads the attribute data stored in
1436 -- the loader file currently being processed, parses
1437 -- the data, and loads them to the database. The tables
1438 -- are updated with the timestamp passed. This API
1439 -- will process the file until the EOF is reached,
1440 -- a parse error is encountered, or when data for
1441 -- a different business object is read from the file.
1442 --
1443 -- Results The API returns the standard p_return_status parameter
1444 -- indicating one of the standard return statuses :
1445 -- * Unexpected error
1446 -- * Error
1447 -- * Success
1448 -- Parameters p_index : IN OUT required
1449 -- Index of PL/SQL file to be processed.
1450 -- p_loader_timestamp : IN required
1451 -- The timestamp to be used when creating or updating
1452 -- records
1453 -- p_line_num : IN optional
1454 -- The first line number in the file to be processed.
1455 -- It is used for keeping track of the line number
1456 -- read so that this info can be included in the
1457 -- error message when a parse error occurred.
1458 -- p_buffer : IN required
1459 -- The content of the first line to be processed.
1460 -- The calling API has already read the first line
1461 -- that needs to be parsed by this API, so this
1462 -- line won't be read from the file again.
1463 -- p_line_num_out : OUT
1464 -- The number of the last line in the loader file
1465 -- that is read by this API.
1466 -- p_buffer_out : OUT
1467 -- The content of the last line read by this API.
1468 -- If an EOF has not reached, this line would
1469 -- contain the beginning of another business object
1470 -- that will need to be processed by another API.
1471 --
1472 -- Version Initial version number = 1.0
1473 -- History Current version number = 1.0
1474 --=======================================================
1475 procedure UPLOAD_SECURITY (
1476 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1477 p_api_version_number IN NUMBER,
1478 p_return_status OUT NOCOPY VARCHAR2,
1479 p_index IN OUT NOCOPY NUMBER,
1480 p_loader_timestamp IN DATE,
1481 p_line_num IN NUMBER := FND_API.G_MISS_NUM,
1482 p_buffer IN AK_ON_OBJECTS_PUB.Buffer_Type,
1483 p_line_num_out OUT NOCOPY NUMBER,
1484 p_buffer_out OUT NOCOPY AK_ON_OBJECTS_PUB.Buffer_Type,
1485 p_upl_loader_cur IN OUT NOCOPY AK_ON_OBJECTS_PUB.LoaderCurTyp
1486 ) is
1487 l_api_version_number CONSTANT number := 1.0;
1488 l_api_name CONSTANT varchar2(30) := 'Upload_Security';
1489 l_excluded_items_rec ak_excluded_items%ROWTYPE;
1490 l_resp_sec_attr_rec ak_resp_security_attributes%ROWTYPE;
1491 l_buffer AK_ON_OBJECTS_PUB.Buffer_Type;
1492 l_column varchar2(30);
1493 l_dummy NUMBER;
1494 l_empty_excluded_items_rec ak_excluded_items%ROWTYPE;
1495 l_empty_resp_sec_attr_rec ak_resp_security_attributes%ROWTYPE;
1496 l_eof_flag VARCHAR2(1);
1497 l_line_num NUMBER;
1498 l_lines_read NUMBER;
1499 l_msg_count NUMBER;
1500 l_msg_data VARCHAR2(2000);
1501 l_more_item BOOLEAN := TRUE;
1502 l_return_status varchar2(1);
1503 l_saved_token AK_ON_OBJECTS_PUB.Buffer_type;
1504 l_state NUMBER; /* parse state */
1505 l_token AK_ON_OBJECTS_PUB.Buffer_Type;
1506 l_value_count NUMBER; /* # of values read for current column */
1507 begin
1508 IF NOT FND_API.Compatible_API_Call (
1509 l_api_version_number, p_api_version_number, l_api_name,
1510 G_PKG_NAME) then
1511 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1512 return;
1513 END IF;
1514
1515 --dbms_output.put_line('Started security upload: ' ||
1516 -- to_char(sysdate, 'MON-DD HH24:MI:SS'));
1517
1518 SAVEPOINT Start_Upload;
1519
1520 --
1521 -- Retrieve the first non-blank, non-comment line
1522 --
1523 l_state := 0;
1524 l_eof_flag := 'N';
1525 --
1526 -- if calling from ak_on_objects.upload (ie, loader timestamp is given),
1527 -- the tokens 'BEGIN EXCLUDED_ITEMS' has already been parsed. Set initial
1528 -- buffer to 'BEGIN EXCLUDED_ITEMS' before reading the next line from the
1529 -- file. Otherwise, set initial buffer to null.
1530 --
1531 if (p_loader_timestamp <> FND_API.G_MISS_DATE) then
1532 l_buffer := 'BEGIN ' || p_buffer;
1533 else
1534 l_buffer := null;
1535 end if;
1536
1537 if (p_line_num = FND_API.G_MISS_NUM) then
1538 l_line_num := 0;
1539 else
1540 l_line_num := p_line_num;
1541 end if;
1542
1543 while (l_buffer is null and l_eof_flag = 'N' and p_index <= AK_ON_OBJECTS_PVT.G_UPL_TABLE_NUM) loop
1544 AK_ON_OBJECTS_PVT.READ_LINE (
1545 p_return_status => l_return_status,
1546 p_index => p_index,
1547 p_buffer => l_buffer,
1548 p_lines_read => l_lines_read,
1549 p_eof_flag => l_eof_flag,
1550 p_upl_loader_cur => p_upl_loader_cur
1551 );
1552 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1553 (l_return_status = FND_API.G_RET_STS_ERROR) then
1554 RAISE FND_API.G_EXC_ERROR;
1555 end if;
1556 l_line_num := l_line_num + l_lines_read;
1557 --
1558 -- trim leading spaces and discard comment lines
1559 --
1560 l_buffer := LTRIM(l_buffer);
1561 if (SUBSTR(l_buffer, 1, 1) = '#') then
1562 l_buffer := null;
1563 end if;
1564 end loop;
1565
1566 --
1567 -- Error if there is nothing to be read from the file
1568 --
1569 if (l_buffer is null and l_eof_flag = 'Y') then
1570 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1571 FND_MESSAGE.SET_NAME('AK','AK_EMPTY_BUFFER');
1572 FND_MSG_PUB.Add;
1573 end if;
1574 raise FND_API.G_EXC_ERROR;
1575 end if;
1576
1577 --
1578 -- Read tokens from file, one at a time
1579 --
1580 while (l_eof_flag = 'N') and (l_buffer is not null)
1581 and (l_more_item) loop
1582
1583 AK_ON_OBJECTS_PVT.GET_TOKEN(
1584 p_return_status => l_return_status,
1585 p_in_buf => l_buffer,
1586 p_token => l_token
1587 );
1588
1589 --dbms_output.put_line(' State:' || l_state || 'Token:' || l_token || ' -' ||
1590 -- to_char(sysdate, 'MON-DD HH24:MI:SS'));
1591
1592 if (l_return_status = FND_API.G_RET_STS_ERROR) or
1593 (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
1594 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1595 FND_MESSAGE.SET_NAME('AK','AK_GET_TOKEN_ERROR');
1596 FND_MSG_PUB.Add;
1597 end if;
1598 -- dbms_output.put_line('Error parsing buffer');
1599 raise FND_API.G_EXC_ERROR;
1600 end if;
1601
1602 if (l_state = 0) then
1603 if (l_token = 'BEGIN') then
1604 --== Clear out previous column data ==--
1605 l_excluded_items_rec := l_empty_excluded_items_rec;
1606 l_resp_sec_attr_rec := l_empty_resp_sec_attr_rec;
1607 l_state := 1;
1608 else
1609 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1610 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR');
1611 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
1612 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
1613 FND_MESSAGE.SET_TOKEN('EXPECTED','BEGIN');
1614 FND_MSG_PUB.Add;
1615 end if;
1616 raise FND_API.G_EXC_ERROR;
1617 end if;
1618 elsif (l_state = 1) then
1619 if (l_token = 'EXCLUDED_ITEMS') then
1620 l_state := 2;
1621 elsif (l_token = 'RESP_SECURITY_ATTRIBUTES') then
1622 l_state := 32;
1623 else
1624 -- Found the beginning of a non-attribute object,
1625 -- rebuild last line and pass it back to the caller
1626 -- (ak_on_objects_pvt.upload).
1627 p_buffer_out := 'BEGIN ' || l_token || ' ' || l_buffer;
1628 l_more_item := FALSE;
1629 end if;
1630 elsif (l_state = 2) then
1631 if (l_token is not null) then
1632 l_excluded_items_rec.responsibility_id := to_number(l_token);
1633 l_state := 3;
1634 else
1635 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1636 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR_VALUE');
1637 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
1638 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
1639 FND_MESSAGE.SET_TOKEN('EXPECTED','RESPONSIBILITY_ID');
1640 FND_MSG_PUB.Add;
1641 end if;
1642 -- dbms_output.put_line('Expecting responsibility ID');
1643 raise FND_API.G_EXC_ERROR;
1644 end if;
1645 elsif (l_state = 3) then
1646 if (l_token is not null) then
1647 l_excluded_items_rec.resp_application_id := to_number(l_token);
1648 l_state := 4;
1649 else
1650 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1651 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR_VALUE');
1652 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
1653 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
1654 FND_MESSAGE.SET_TOKEN('EXPECTED','RESP_APPLICATION_ID');
1655 FND_MSG_PUB.Add;
1656 end if;
1657 --dbms_output.put_line('Expecting resp application id');
1658 raise FND_API.G_EXC_ERROR;
1659 end if;
1660 elsif (l_state = 4) then
1661 if (l_token is not null) then
1662 l_excluded_items_rec.attribute_application_id := to_number(l_token);
1663 l_state := 5;
1664 else
1665 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1666 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR_VALUE');
1667 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
1668 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
1669 FND_MESSAGE.SET_TOKEN('EXPECTED','ATTRIBUTE_CODE');
1670 FND_MSG_PUB.Add;
1671 end if;
1672 --dbms_output.put_line('Expecting attribute code');
1673 raise FND_API.G_EXC_ERROR;
1674 end if;
1675 elsif (l_state = 5) then
1676 if (l_token is not null) then
1677 l_excluded_items_rec.attribute_code := l_token;
1678 l_value_count := null;
1679 l_state := 10;
1680 else
1681 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1682 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR_VALUE');
1683 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
1684 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
1685 FND_MESSAGE.SET_TOKEN('EXPECTED','ATTRIBUTE_APPLICATION_ID');
1686 FND_MSG_PUB.Add;
1687 end if;
1688 --dbms_output.put_line('Expecting attribute code');
1689 raise FND_API.G_EXC_ERROR;
1690 end if;
1691 elsif (l_state = 10) then
1692 if (l_token = 'END') then
1693 l_state := 19;
1694 elsif
1695 (l_token = 'CREATED_BY') or
1696 (l_token = 'CREATION_DATE') or
1697 (l_token = 'LAST_UPDATED_BY') or
1698 (l_token = 'OWNER') or
1699 (l_token = 'LAST_UPDATE_DATE') or
1700 (l_token = 'LAST_UPDATE_LOGIN') then
1701 l_column := l_token;
1702 l_state := 11;
1703 else
1704 --
1705 -- error if not expecting attribute values added by the translation team
1706 -- or if we have read in more than a certain number of values
1707 -- for the same DB column
1708 --
1709 l_value_count := l_value_count + 1;
1710 --
1711 -- save second value. It will be the token with error if
1712 -- it turns out that there is a parse error on this line.
1713 --
1714 if (l_value_count = 2) then
1715 l_saved_token := l_token;
1716 end if;
1717 if (l_value_count > AK_ON_OBJECTS_PUB.G_MAX_NUM_LOADER_VALUES) or
1718 (l_value_count is null) then
1719 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1720 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR_EFIELD');
1721 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
1722 if (l_value_count is null) then
1723 FND_MESSAGE.SET_TOKEN('TOKEN', l_token);
1724 else
1725 FND_MESSAGE.SET_TOKEN('TOKEN',l_saved_token);
1726 end if;
1727 FND_MESSAGE.SET_TOKEN('EXPECTED','ATTRIBUTE');
1728 FND_MSG_PUB.Add;
1729 end if;
1730 -- dbms_output.put_line('Expecting attribute field or END');
1731 raise FND_API.G_EXC_ERROR;
1732 end if;
1733 end if;
1734 elsif (l_state = 11) then
1735 if (l_token = '=') then
1736 l_state := 12;
1737 else
1738 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1739 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR');
1740 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
1741 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
1742 FND_MESSAGE.SET_TOKEN('EXPECTED','=');
1743 FND_MSG_PUB.Add;
1744 end if;
1745 raise FND_API.G_EXC_ERROR;
1746 end if;
1747 elsif (l_state = 12) then
1748 l_value_count := 1;
1749 if (l_column = 'CREATED_BY') then
1750 l_excluded_items_rec.created_by := to_number(l_token);
1751 l_state := 10;
1752 elsif (l_column = 'CREATION_DATE') then
1753 l_excluded_items_rec.creation_date := to_date(l_token,
1754 AK_ON_OBJECTS_PUB.G_DATE_FORMAT);
1755 l_state := 10;
1756 elsif (l_column = 'LAST_UPDATED_BY') then
1757 l_excluded_items_rec.last_updated_by := to_number(l_token);
1758 l_state := 10;
1759 elsif (l_column = 'OWNER') then
1760 l_excluded_items_rec.last_updated_by := FND_LOAD_UTIL.OWNER_ID(l_token);
1761 l_state := 10;
1762 elsif (l_column = 'LAST_UPDATE_DATE') then
1763 l_excluded_items_rec.last_update_date := to_date(l_token,
1764 AK_ON_OBJECTS_PUB.G_DATE_FORMAT);
1765 l_state := 10;
1766 elsif (l_column = 'LAST_UPDATE_LOGIN') then
1767 l_excluded_items_rec.last_update_login := to_number(l_token);
1768 l_state := 10;
1769 else
1770 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1771 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR_VALUE');
1772 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
1773 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
1774 FND_MESSAGE.SET_TOKEN('EXPECTED',l_column);
1775 FND_MSG_PUB.Add;
1776 end if;
1777 --dbms_output.put_line('Expecting ' || l_column || ' value');
1778 raise FND_API.G_EXC_ERROR;
1779 end if;
1780 elsif (l_state = 19) then
1781 if (l_token = 'EXCLUDED_ITEMS') then
1782 if not AK_SECURITY_PVT.EXCLUDED_ITEM_EXISTS (
1783 p_api_version_number => 1.0,
1784 p_return_status => l_return_status,
1785 p_responsibility_id =>
1786 l_excluded_items_rec.responsibility_id,
1787 p_resp_application_id =>
1788 l_excluded_items_rec.resp_application_id,
1789 p_attribute_code => l_excluded_items_rec.attribute_code,
1790 p_attribute_application_id=>
1791 l_excluded_items_rec.attribute_application_id) then
1792
1793 -- Insert record into ak_excluded_items if record does not exist
1794 --
1795 AK_SECURITY_PVT.CREATE_EXCLUDED_ITEM (
1796 p_validation_level => p_validation_level,
1797 p_api_version_number => 1.0,
1798 p_msg_count => l_msg_count,
1799 p_msg_data => l_msg_data,
1800 p_return_status => l_return_status,
1801 p_responsibility_id =>
1802 l_excluded_items_rec.responsibility_id,
1803 p_resp_application_id =>
1804 l_excluded_items_rec.resp_application_id,
1805 p_attribute_code => l_excluded_items_rec.attribute_code,
1806 p_attribute_application_id =>
1807 l_excluded_items_rec.attribute_application_id,
1808 p_created_by => l_excluded_items_rec.created_by,
1809 p_creation_date => l_excluded_items_rec.creation_date,
1810 p_last_updated_by => l_excluded_items_rec.last_updated_by,
1811 p_last_update_date => l_excluded_items_rec.last_update_date,
1812 p_last_update_login => l_excluded_items_rec.last_update_login,
1813 p_loader_timestamp => p_loader_timestamp
1814 );
1815 -- If API call returns with an error status, upload aborts
1816 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1817 (l_return_status = FND_API.G_RET_STS_ERROR) then
1818 RAISE FND_API.G_EXC_ERROR;
1819 end if; -- /* if l_return_status */
1820 end if; -- /* if EXCLUDED_ITEM_EXISTS */
1821 l_state := 0;
1822 else
1823 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1824 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR');
1825 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
1826 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
1827 FND_MESSAGE.SET_TOKEN('EXPECTED','EXCLUDED_ITEMS');
1828 FND_MSG_PUB.Add;
1829 end if;
1830 raise FND_API.G_EXC_ERROR;
1831 end if;
1832 elsif (l_state = 30) then
1833 if (l_token = 'END') then
1834 l_state := 39;
1835 elsif
1836 (l_token = 'CREATED_BY') or
1837 (l_token = 'CREATION_DATE') or
1838 (l_token = 'LAST_UPDATED_BY') or
1839 (l_token = 'OWNER') or
1840 (l_token = 'LAST_UPDATE_DATE') or
1841 (l_token = 'LAST_UPDATE_LOGIN') then
1842 l_column := l_token;
1843 l_state := 36;
1844 else
1845 --
1846 -- error if not expecting attribute values added by the translation team
1847 -- or if we have read in more than a certain number of values
1848 -- for the same DB column
1849 --
1850 l_value_count := l_value_count + 1;
1851 --
1852 -- save second value. It will be the token with error if
1853 -- it turns out that there is a parse error on this line.
1854 --
1855 if (l_value_count = 2) then
1856 l_saved_token := l_token;
1857 end if;
1858 if (l_value_count > AK_ON_OBJECTS_PUB.G_MAX_NUM_LOADER_VALUES) or
1859 (l_value_count is null) then
1860 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1861 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR_EFIELD');
1862 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
1863 if (l_value_count is null) then
1864 FND_MESSAGE.SET_TOKEN('TOKEN', l_token);
1865 else
1866 FND_MESSAGE.SET_TOKEN('TOKEN',l_saved_token);
1867 end if;
1868 FND_MESSAGE.SET_TOKEN('EXPECTED','ATTRIBUTE');
1869 FND_MSG_PUB.Add;
1870 end if;
1871 -- dbms_output.put_line('Expecting attribute field or END');
1872 raise FND_API.G_EXC_ERROR;
1873 end if;
1874 end if;
1875 elsif (l_state = 32) then
1876 if (l_token is not null) then
1877 l_resp_sec_attr_rec.responsibility_id := to_number(l_token);
1878 l_state := 33;
1879 else
1880 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1881 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR_VALUE');
1882 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
1883 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
1884 FND_MESSAGE.SET_TOKEN('EXPECTED','RESPONSIBILITY_ID');
1885 FND_MSG_PUB.Add;
1886 end if;
1887 -- dbms_output.put_line('Expecting responsibility ID');
1888 raise FND_API.G_EXC_ERROR;
1889 end if;
1890 elsif (l_state = 33) then
1891 if (l_token is not null) then
1892 l_resp_sec_attr_rec.resp_application_id := to_number(l_token);
1893 l_state := 34;
1894 else
1895 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1896 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR_VALUE');
1897 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
1898 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
1899 FND_MESSAGE.SET_TOKEN('EXPECTED','RESP_APPLICATION_ID');
1900 FND_MSG_PUB.Add;
1901 end if;
1902 --dbms_output.put_line('Expecting resp application id');
1903 raise FND_API.G_EXC_ERROR;
1904 end if;
1905 elsif (l_state = 34) then
1906 if (l_token is not null) then
1907 l_resp_sec_attr_rec.attribute_application_id := to_number(l_token);
1908 l_state := 35;
1909 else
1910 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1911 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR_VALUE');
1912 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
1913 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
1914 FND_MESSAGE.SET_TOKEN('EXPECTED','ATTRIBUTE_APPLICATION_ID');
1915 FND_MSG_PUB.Add;
1916 end if;
1917 --dbms_output.put_line('Expecting attribute code');
1918 raise FND_API.G_EXC_ERROR;
1919 end if;
1920 elsif (l_state = 35) then
1921 if (l_token is not null) then
1922 l_resp_sec_attr_rec.attribute_code := l_token;
1923 l_value_count := null;
1924 l_state := 30;
1925 else
1926 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1927 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR_VALUE');
1928 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
1929 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
1930 FND_MESSAGE.SET_TOKEN('EXPECTED','ATTRIBUTE_CODE');
1931 FND_MSG_PUB.Add;
1932 end if;
1933 --dbms_output.put_line('Expecting attribute code');
1934 raise FND_API.G_EXC_ERROR;
1935 end if;
1936 elsif (l_state = 36) then
1937 if (l_token = '=') then
1938 l_state := 37;
1939 else
1940 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1941 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR');
1942 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
1943 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
1944 FND_MESSAGE.SET_TOKEN('EXPECTED','=');
1945 FND_MSG_PUB.Add;
1946 end if;
1947 raise FND_API.G_EXC_ERROR;
1948 end if;
1949 elsif (l_state = 37) then
1950 l_value_count := 1;
1951 if (l_column = 'CREATED_BY') then
1952 l_resp_sec_attr_rec.created_by := to_number(l_token);
1953 l_state := 30;
1954 elsif (l_column = 'CREATION_DATE') then
1955 l_resp_sec_attr_rec.creation_date := to_date(l_token,
1956 AK_ON_OBJECTS_PUB.G_DATE_FORMAT);
1957 l_state := 30;
1958 elsif (l_column = 'LAST_UPDATED_BY') then
1959 l_resp_sec_attr_rec.last_updated_by := to_number(l_token);
1960 l_state := 30;
1961 elsif (l_column = 'OWNER') then
1962 l_resp_sec_attr_rec.last_updated_by := FND_LOAD_UTIL.OWNER_ID(l_token);
1963 l_state := 30;
1964 elsif (l_column = 'LAST_UPDATE_DATE') then
1965 l_resp_sec_attr_rec.last_update_date := to_date(l_token,
1966 AK_ON_OBJECTS_PUB.G_DATE_FORMAT);
1967 l_state := 30;
1968 elsif (l_column = 'LAST_UPDATE_LOGIN') then
1969 l_resp_sec_attr_rec.last_update_login := to_number(l_token);
1970 l_state := 30;
1971 else
1972 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1973 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR_VALUE');
1974 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
1975 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
1976 FND_MESSAGE.SET_TOKEN('EXPECTED',l_column);
1977 FND_MSG_PUB.Add;
1978 end if;
1979 --dbms_output.put_line('Expecting ' || l_column || ' value');
1980 raise FND_API.G_EXC_ERROR;
1981 end if;
1982 elsif (l_state = 39) then
1983 if (l_token = 'RESP_SECURITY_ATTRIBUTES') then
1984 if not AK_SECURITY_PVT.RESP_SECURITY_ATTR_EXISTS (
1985 p_api_version_number => 1.0,
1986 p_return_status => l_return_status,
1987 p_responsibility_id =>
1988 l_resp_sec_attr_rec.responsibility_id,
1989 p_resp_application_id =>
1990 l_resp_sec_attr_rec.resp_application_id,
1991 p_attribute_code => l_resp_sec_attr_rec.attribute_code,
1992 p_attribute_application_id=>
1993 l_resp_sec_attr_rec.attribute_application_id) then
1994
1995 -- Insert record into ak_l_resp_sec_attributes if record does not exist
1996 --
1997 AK_SECURITY_PVT.CREATE_RESP_SECURITY_ATTR (
1998 p_validation_level => p_validation_level,
1999 p_api_version_number => 1.0,
2000 p_msg_count => l_msg_count,
2001 p_msg_data => l_msg_data,
2002 p_return_status => l_return_status,
2003 p_responsibility_id =>
2004 l_resp_sec_attr_rec.responsibility_id,
2005 p_resp_application_id =>
2006 l_resp_sec_attr_rec.resp_application_id,
2007 p_attribute_code => l_resp_sec_attr_rec.attribute_code,
2008 p_attribute_application_id =>
2009 l_resp_sec_attr_rec.attribute_application_id,
2010 p_created_by => l_resp_sec_attr_rec.created_by,
2011 p_creation_date => l_resp_sec_attr_rec.creation_date,
2012 p_last_updated_by => l_resp_sec_attr_rec.last_updated_by,
2013 p_last_update_date => l_resp_sec_attr_rec.lasT_update_date,
2014 p_lasT_update_login => l_resp_sec_attr_rec.last_update_login,
2015 p_loader_timestamp => p_loader_timestamp
2016 );
2017 --
2018 -- If API call returns with an error status, upload aborts
2019 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
2020 (l_return_status = FND_API.G_RET_STS_ERROR) then
2021 RAISE FND_API.G_EXC_ERROR;
2022 end if; -- /* if l_return_status */
2023 end if; -- /* if RESP_SECURITY_ATTR_EXISTS */
2024 l_state := 0;
2025 else
2026 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2027 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR');
2028 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
2029 FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
2030 FND_MESSAGE.SET_TOKEN('EXPECTED','RESP_SECURITY_ATTRIBUTES');
2031 FND_MSG_PUB.Add;
2032 end if;
2033 raise FND_API.G_EXC_ERROR;
2034 end if;
2035 end if;
2036
2037 --
2038 -- Get rid of leading white spaces, so that buffer would become
2039 -- null if the only thing in it are white spaces
2040 --
2041 l_buffer := LTRIM(l_buffer);
2042
2043 --
2044 -- Get the next non-blank, non-comment line if current line is
2045 -- fully parsed
2046 --
2047 while (l_buffer is null and l_eof_flag = 'N' and p_index <= AK_ON_OBJECTS_PVT.G_UPL_TABLE_NUM) loop
2048 AK_ON_OBJECTS_PVT.READ_LINE (
2049 p_return_status => l_return_status,
2050 p_index => p_index,
2051 p_buffer => l_buffer,
2052 p_lines_read => l_lines_read,
2053 p_eof_flag => l_eof_flag,
2054 p_upl_loader_cur => p_upl_loader_cur
2055 );
2056 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
2057 (l_return_status = FND_API.G_RET_STS_ERROR) then
2058 RAISE FND_API.G_EXC_ERROR;
2059 end if;
2060 l_line_num := l_line_num + l_lines_read;
2061 --
2062 -- trim leading spaces and discard comment lines
2063 --
2064 l_buffer := LTRIM(l_buffer);
2065 if (SUBSTR(l_buffer, 1, 1) = '#') then
2066 l_buffer := null;
2067 end if;
2068 end loop;
2069
2070 end LOOP;
2071
2072 -- If the loops end in a state other then at the end of an attribute
2073 -- (state 0) or when the beginning of another business object was
2074 -- detected, then the file must have ended prematurely, which is an error
2075 --
2076 if (l_state <> 0) and (l_more_item) then
2077 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2078 FND_MESSAGE.SET_NAME('AK','AK_PARSE_ERROR');
2079 FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
2080 FND_MESSAGE.SET_TOKEN('TOKEN','END OF FILE');
2081 FND_MESSAGE.SET_TOKEN('EXPECTED',null);
2082 FND_MSG_PUB.Add;
2083 end if;
2084 --dbms_output.put_line('Unexpected END OF FILE: state is ' ||
2085 -- to_char(l_state));
2086 raise FND_API.G_EXC_ERROR;
2087 end if;
2088
2089 --
2090 -- Load line number of the last file line processed
2091 --
2092 p_line_num_out := l_line_num;
2093
2094 p_return_status := FND_API.G_RET_STS_SUCCESS;
2095
2096 --dbms_output.put_line('Leaving security upload: ' ||
2097 -- to_char(sysdate, 'MON-DD HH24:MI:SS'));
2098
2099 EXCEPTION
2100 WHEN FND_API.G_EXC_ERROR THEN
2101 p_return_status := FND_API.G_RET_STS_ERROR;
2102 WHEN OTHERS THEN
2103 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2104 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
2105 SUBSTR (SQLERRM, 1, 240) );
2106 FND_MSG_PUB.Add;
2107 end UPLOAD_SECURITY;
2108
2109
2110 --=======================================================
2111 -- Function VALIDATE_SECURITY
2112 --
2113 -- Usage Private API for validating an excluded items. This
2114 -- API should only be called by other APIs that are
2115 -- owned by the Core Modules Team (AK).
2116 --
2117 -- Desc Perform validation on an exluded items record or
2118 -- resp_security_attributes record.
2119 --
2120 -- Results The API returns the standard p_return_status parameter
2121 -- indicating one of the standard return statuses :
2122 -- * Unexpected error
2123 -- * Error
2124 -- * Success
2125 -- In addition, this function returns TRUE if all
2126 -- validation tests are passed, or FALSE otherwise.
2127 -- Parameters Excluded_items or Resp_Security columns
2128 -- p_caller : IN required
2129 -- Must be one of the following values defined
2130 -- in package AK_ON_OBJECTS_PVT:
2131 -- - G_CREATE (if calling from the Create API)
2132 -- - G_DOWNLOAD (if calling from the Download API)
2133 -- - G_UPDATE (if calling from the Update API)
2134 --
2135 -- Note This API is intended for performing record-level
2136 -- validation. It is not designed for item-level
2137 -- validation.
2138 --
2139 -- Version Initial version number = 1.0
2140 -- History Current version number = 1.0
2141 --=======================================================
2142 function VALIDATE_SECURITY (
2143 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2144 p_api_version_number IN NUMBER,
2145 p_return_status OUT NOCOPY VARCHAR2,
2146 p_responsibility_appl_id IN NUMBER := FND_API.G_MISS_NUM,
2147 p_responsibility_id IN NUMBER := FND_API.G_MISS_NUM,
2148 p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
2149 p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
2150 p_caller IN VARCHAR2
2151 ) return BOOLEAN is
2152 l_api_version_number CONSTANT number := 1.0;
2153 l_api_name CONSTANT varchar2(30) := 'Validate_Security';
2154 l_error BOOLEAN;
2155 l_return_status VARCHAR2(1);
2156 begin
2157 IF NOT FND_API.Compatible_API_Call (
2158 l_api_version_number, p_api_version_number, l_api_name,
2159 G_PKG_NAME) then
2160 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2161 return FALSE;
2162 END IF;
2163
2164 l_error := FALSE;
2165 --
2166 -- if validation level is none, no validation is necessary
2167 --
2168 if (p_validation_level = FND_API.G_VALID_LEVEL_NONE) then
2169 p_return_status := FND_API.G_RET_STS_SUCCESS;
2170 return TRUE;
2171 end if;
2172
2173 --
2174 -- check that key columns are not null and not missing
2175 --
2176 if ((p_responsibility_appl_id is null) or
2177 (p_responsibility_appl_id = FND_API.G_MISS_NUM)) then
2178 l_error := TRUE;
2179 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
2180 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
2181 FND_MESSAGE.SET_TOKEN('COLUMN', 'RESP_APPLICATION_ID');
2182 FND_MSG_PUB.Add;
2183 end if;
2184 end if;
2185
2186 if ((p_responsibility_id is null) or
2187 (p_responsibility_id = FND_API.G_MISS_NUM)) then
2188 l_error := TRUE;
2189 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
2190 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
2191 FND_MESSAGE.SET_TOKEN('COLUMN', 'RESPONSIBILITY_ID');
2192 FND_MSG_PUB.Add;
2193 end if;
2194 end if;
2195
2196 if ((p_attribute_application_id is null) or
2197 (p_attribute_application_id = FND_API.G_MISS_NUM)) then
2198 l_error := TRUE;
2199 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
2200 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
2201 FND_MESSAGE.SET_TOKEN('COLUMN', 'ATTRIBUTE_APPLICATION_ID');
2202 FND_MSG_PUB.Add;
2203 end if;
2204 end if;
2205
2206 if ((p_attribute_code is null) or
2207 (p_attribute_code = FND_API.G_MISS_CHAR)) then
2208 l_error := TRUE;
2209 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
2210 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
2211 FND_MESSAGE.SET_TOKEN('COLUMN', 'ATTRIBUTE_CODE');
2212 FND_MSG_PUB.Add;
2213 end if;
2214 end if;
2215
2216
2217 --*** Validate columns ***
2218
2219 -- - responsibility application ID
2220 if (p_responsibility_appl_id <> FND_API.G_MISS_NUM) then
2221 if (NOT AK_ON_OBJECTS_PVT.VALID_APPLICATION_ID (
2222 p_api_version_number => 1.0,
2223 p_return_status => l_return_status,
2224 p_application_id => p_responsibility_appl_id)
2225 ) then
2226 l_error := TRUE;
2227 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
2228 FND_MESSAGE.SET_NAME('AK','AK_INVALID_COLUMN_VALUE');
2229 FND_MESSAGE.SET_TOKEN('COLUMN','RESP_APPLICATION_ID');
2230 FND_MSG_PUB.Add;
2231 end if;
2232 end if;
2233 end if;
2234
2235 -- - attribute application ID
2236 if (p_attribute_application_id <> FND_API.G_MISS_NUM) then
2237 if (NOT AK_ON_OBJECTS_PVT.VALID_APPLICATION_ID (
2238 p_api_version_number => 1.0,
2239 p_return_status => l_return_status,
2240 p_application_id => p_attribute_application_id)
2241 ) then
2242 l_error := TRUE;
2243 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
2244 FND_MESSAGE.SET_NAME('AK','AK_INVALID_COLUMN_VALUE');
2245 FND_MESSAGE.SET_TOKEN('COLUMN','ATTRIBUTE_APPLICATION_ID');
2246 FND_MSG_PUB.Add;
2247 end if;
2248 end if;
2249 end if;
2250
2251 /* return true if no error, false otherwise */
2252 p_return_status := FND_API.G_RET_STS_SUCCESS;
2253 return (not l_error);
2254
2255 EXCEPTION
2256 WHEN FND_API.G_EXC_ERROR THEN
2257 p_return_status := FND_API.G_RET_STS_ERROR;
2258 return FALSE;
2259 WHEN OTHERS THEN
2260 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2261 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
2262 SUBSTR (SQLERRM, 1, 240) );
2263 FND_MSG_PUB.Add;
2264 return FALSE;
2265
2266 end VALIDATE_SECURITY;
2267
2268 end AK_SECURITY_pvt;
[Home] [Help]