[Home] [Help]
PACKAGE BODY: APPS.PSB_POSITION_ATTRIBUTES_PVT
Source
1 PACKAGE BODY PSB_POSITION_ATTRIBUTES_PVT AS
2 /* $Header: PSBVPATB.pls 120.9 2006/06/28 12:18:56 mvenugop ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_POSITION_ATTRIBUTES_PVT';
5
6 /* ----------------------------------------------------------------------- */
7
8 PROCEDURE INSERT_ROW
9 ( p_api_version IN NUMBER,
10 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
11 p_commit IN VARCHAR2 := FND_API.G_FALSE,
12 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
13 p_return_status OUT NOCOPY VARCHAR2,
14 p_msg_count OUT NOCOPY NUMBER,
15 p_msg_data OUT NOCOPY VARCHAR2,
16 --
17 p_ROW_ID IN OUT NOCOPY VARCHAR2,
18 p_ATTRIBUTE_ID IN NUMBER,
19 p_BUSINESS_GROUP_ID IN NUMBER,
20 p_NAME IN VARCHAR2,
21 p_DISPLAY_IN_WORKSHEET IN VARCHAR2,
22 p_DISPLAY_SEQUENCE IN NUMBER,
23 p_DISPLAY_PROMPT IN VARCHAR2,
24 p_REQUIRED_FOR_IMPORT_FLAG IN VARCHAR2,
25 p_REQUIRED_FOR_POSITIONS_FLAG IN VARCHAR2,
26 p_ALLOW_IN_POSITION_SET_FLAG IN VARCHAR2,
27 p_VALUE_TABLE_FLAG IN VARCHAR2,
28 p_PROTECTED_FLAG IN VARCHAR2,
29 p_DEFINITION_TYPE IN VARCHAR2,
30 p_DEFINITION_STRUCTURE IN VARCHAR2,
31 p_DEFINITION_TABLE IN VARCHAR2,
32 p_DEFINITION_COLUMN IN VARCHAR2,
33 p_ATTRIBUTE_TYPE_ID IN NUMBER,
34 p_DATA_TYPE IN VARCHAR2,
35 p_APPLICATION_ID IN NUMBER,
36 p_SYSTEM_ATTRIBUTE_TYPE IN VARCHAR2,
37 p_LAST_UPDATE_DATE IN DATE,
38 p_LAST_UPDATED_BY IN NUMBER,
39 p_LAST_UPDATE_LOGIN IN NUMBER,
40 p_CREATED_BY IN NUMBER,
41 p_CREATION_DATE IN DATE
42 ) AS
43
44 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_ROW';
45 l_api_version CONSTANT NUMBER := 1.0;
46 l_attribute_id number;
47 --
48 cursor c1 is
49 select row_id from psb_attributes_VL
50 where attribute_id = p_attribute_id;
51
52 BEGIN
53
54 -- Standard Start of API savepoint
55
56 SAVEPOINT INSERT_ROW_PVT;
57
58 -- Standard call to check for call compatibility.
59
60 if not FND_API.Compatible_API_Call (l_api_version,
61 p_api_version,
62 l_api_name,
63 G_PKG_NAME)
64 then
65 raise FND_API.G_EXC_UNEXPECTED_ERROR;
66 end if;
67
68 -- Initialize message list if p_init_msg_list is set to TRUE.
69
70 if FND_API.to_Boolean (p_init_msg_list) then
71 FND_MSG_PUB.initialize;
72 end if;
73
74 -- Initialize API return status to success
75
76 p_return_status := FND_API.G_RET_STS_SUCCESS;
77
78
79 -- API body
80 INSERT INTO psb_attributes
81 (ATTRIBUTE_ID ,
82 BUSINESS_GROUP_ID ,
83 NAME ,
84 DISPLAY_IN_WORKSHEET ,
85 DISPLAY_SEQUENCE ,
86 DISPLAY_PROMPT ,
87 REQUIRED_FOR_IMPORT_FLAG ,
88 REQUIRED_FOR_POSITIONS_FLAG ,
89 ALLOW_IN_POSITION_SET_FLAG ,
90 VALUE_TABLE_FLAG ,
91 PROTECTED_FLAG ,
92 DEFINITION_TYPE ,
93 DEFINITION_STRUCTURE ,
94 DEFINITION_TABLE ,
95 DEFINITION_COLUMN ,
96 ATTRIBUTE_TYPE_ID ,
97 DATA_TYPE ,
98 APPLICATION_ID ,
99 SYSTEM_ATTRIBUTE_TYPE ,
100 LAST_UPDATE_DATE ,
101 LAST_UPDATED_BY ,
102 LAST_UPDATE_LOGIN ,
103 CREATED_BY ,
104 CREATION_DATE
105 )
106 VALUES
107 (
108 p_attribute_id ,
109 p_BUSINESS_GROUP_ID ,
110 p_NAME ,
111 p_DISPLAY_IN_WORKSHEET ,
112 p_DISPLAY_SEQUENCE ,
113 p_DISPLAY_PROMPT ,
114 p_REQUIRED_FOR_IMPORT_FLAG ,
115 p_REQUIRED_FOR_POSITIONS_FLAG ,
116 p_ALLOW_IN_POSITION_SET_FLAG ,
117 p_VALUE_TABLE_FLAG ,
118 p_PROTECTED_FLAG ,
119 p_DEFINITION_TYPE ,
120 p_DEFINITION_STRUCTURE ,
121 p_DEFINITION_TABLE ,
122 p_DEFINITION_COLUMN ,
123 p_ATTRIBUTE_TYPE_ID ,
124 p_DATA_TYPE ,
125 p_APPLICATION_ID ,
126 P_SYSTEM_ATTRIBUTE_TYPE ,
127 p_LAST_UPDATE_DATE ,
128 p_LAST_UPDATED_BY ,
129 p_LAST_UPDATE_LOGIN ,
130 p_CREATED_BY ,
131 p_CREATION_DATE
132 );
133
134 insert into PSB_ATTRIBUTES_TL (
135 ATTRIBUTE_ID,
136 NAME,
137 DISPLAY_PROMPT,
138 LAST_UPDATE_DATE,
139 LAST_UPDATED_BY,
140 LAST_UPDATE_LOGIN,
141 CREATED_BY,
142 CREATION_DATE,
143 LANGUAGE,
144 SOURCE_LANG
145 ) select
146 P_ATTRIBUTE_ID,
147 P_NAME,
148 P_DISPLAY_PROMPT,
149 P_LAST_UPDATE_DATE,
150 P_LAST_UPDATED_BY,
151 P_LAST_UPDATE_LOGIN,
152 P_CREATED_BY,
153 P_CREATION_DATE,
154 L.LANGUAGE_CODE,
155 userenv('LANG')
156 from FND_LANGUAGES L
157 where L.INSTALLED_FLAG in ('I', 'B')
158 and not exists
159 (select NULL
160 from PSB_ATTRIBUTES_TL T
161 where T.ATTRIBUTE_ID = P_ATTRIBUTE_ID
162 and T.LANGUAGE = L.LANGUAGE_CODE);
163
164 open c1;
165 fetch c1 into P_ROW_ID;
166 if (c1%notfound) then
167 close c1;
168 raise no_data_found;
169 end if;
170
171 -- End of API body.
172
173 -- Standard check of p_commit.
174
175 if FND_API.to_Boolean (p_commit) then
176 commit work;
177 end if;
178
179 -- Standard call to get message count and if count is 1, get message info.
180
181 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
182 p_data => p_msg_data);
183
184 EXCEPTION
185
186 when FND_API.G_EXC_ERROR then
187
188 rollback to INSERT_ROW_PVT;
189
190 p_return_status := FND_API.G_RET_STS_ERROR;
191
192 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
193 p_data => p_msg_data);
194
195
196 when FND_API.G_EXC_UNEXPECTED_ERROR then
197
198 rollback to INSERT_ROW_PVT;
199
200 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
201
202 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
203 p_data => p_msg_data);
204
205
206 when OTHERS then
207
208 rollback to INSERT_ROW_PVT;
209
210 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
211
212 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
213
214 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
215 l_api_name);
216 end if;
217
218 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
219 p_data => p_msg_data);
220
221 END INSERT_ROW;
222
223 PROCEDURE UPDATE_ROW
224 ( p_api_version IN NUMBER,
225 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
226 p_commit IN VARCHAR2 := FND_API.G_FALSE,
227 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
228 p_return_status OUT NOCOPY VARCHAR2,
229 p_msg_count OUT NOCOPY NUMBER,
230 p_msg_data OUT NOCOPY VARCHAR2,
231 --
232 p_ATTRIBUTE_ID IN NUMBER,
233 p_BUSINESS_GROUP_ID IN NUMBER,
234 p_NAME IN VARCHAR2,
235 p_DISPLAY_IN_WORKSHEET IN VARCHAR2,
236 p_DISPLAY_SEQUENCE IN NUMBER,
237 p_DISPLAY_PROMPT IN VARCHAR2,
238 p_REQUIRED_FOR_IMPORT_FLAG IN VARCHAR2,
239 p_REQUIRED_FOR_POSITIONS_FLAG IN VARCHAR2,
240 p_ALLOW_IN_POSITION_SET_FLAG IN VARCHAR2,
241 p_VALUE_TABLE_FLAG IN VARCHAR2,
242 p_PROTECTED_FLAG IN VARCHAR2,
243 p_DEFINITION_TYPE IN VARCHAR2,
244 p_DEFINITION_STRUCTURE IN VARCHAR2,
245 p_DEFINITION_TABLE IN VARCHAR2,
246 p_DEFINITION_COLUMN IN VARCHAR2,
247 p_ATTRIBUTE_TYPE_ID IN NUMBER,
248 p_DATA_TYPE IN VARCHAR2,
249 p_APPLICATION_ID IN NUMBER,
250 p_SYSTEM_ATTRIBUTE_TYPE IN VARCHAR2,
251 p_LAST_UPDATE_DATE IN DATE,
252 p_LAST_UPDATED_BY IN NUMBER,
253 p_LAST_UPDATE_LOGIN IN NUMBER
254 ) AS
255
256 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_ROW';
257 l_api_version CONSTANT NUMBER := 1.0;
258
259 BEGIN
260
261 -- Standard Start of API savepoint
262
263 SAVEPOINT UPDATE_ROW_PVT;
264
265 -- Standard call to check for call compatibility.
266
267 if not FND_API.Compatible_API_Call (l_api_version,
268 p_api_version,
269 l_api_name,
270 G_PKG_NAME)
271 then
272 raise FND_API.G_EXC_UNEXPECTED_ERROR;
273 end if;
274
275 -- Initialize message list if p_init_msg_list is set to TRUE.
276
277 if FND_API.to_Boolean (p_init_msg_list) then
278 FND_MSG_PUB.initialize;
279 end if;
280
281 -- Initialize API return status to success
282
283 p_return_status := FND_API.G_RET_STS_SUCCESS;
284
285 -- API body
286 UPDATE psb_attributes SET
287 ATTRIBUTE_ID = p_ATTRIBUTE_ID,
288 BUSINESS_GROUP_ID = p_BUSINESS_GROUP_ID ,
289 NAME = p_NAME ,
290 DISPLAY_IN_WORKSHEET = p_DISPLAY_IN_WORKSHEET,
291 DISPLAY_SEQUENCE = p_DISPLAY_SEQUENCE,
292 DISPLAY_PROMPT = p_DISPLAY_PROMPT,
293 REQUIRED_FOR_IMPORT_FLAG = p_REQUIRED_FOR_IMPORT_FLAG,
294 REQUIRED_FOR_POSITIONS_FLAG = p_REQUIRED_FOR_POSITIONS_FLAG,
295 ALLOW_IN_POSITION_SET_FLAG = p_ALLOW_IN_POSITION_SET_FLAG,
296 VALUE_TABLE_FLAG = p_VALUE_TABLE_FLAG,
297 PROTECTED_FLAG = p_PROTECTED_FLAG,
298 DEFINITION_TYPE = p_DEFINITION_TYPE,
299 DEFINITION_STRUCTURE = p_DEFINITION_STRUCTURE,
300 DEFINITION_TABLE = p_DEFINITION_TABLE,
301 DEFINITION_COLUMN = p_DEFINITION_COLUMN,
302 ATTRIBUTE_TYPE_ID = p_ATTRIBUTE_TYPE_ID,
303 DATA_TYPE = p_DATA_TYPE,
304 APPLICATION_ID = P_APPLICATION_ID,
305 SYSTEM_ATTRIBUTE_TYPE = P_SYSTEM_ATTRIBUTE_TYPE
306 WHERE attribute_id = p_attribute_id;
307
308 if (SQL%NOTFOUND) then
309 RAISE NO_DATA_FOUND;
310 end if;
311
312 update PSB_ATTRIBUTES_TL set
313 NAME = P_NAME,
314 DISPLAY_PROMPT = P_DISPLAY_PROMPT,
315 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
316 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
317 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN,
318 SOURCE_LANG = userenv('LANG')
319 where ATTRIBUTE_ID = P_ATTRIBUTE_ID
320 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
321
322 if (sql%notfound) then
323 raise no_data_found;
324 end if;
325
326 -- End of API body.
327
328 -- Standard check of p_commit.
329
330 if FND_API.to_Boolean (p_commit) then
331 commit work;
332 end if;
333
334 -- Standard call to get message count and if count is 1, get message info.
335
336 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
337 p_data => p_msg_data);
338
339 EXCEPTION
340
341 when FND_API.G_EXC_ERROR then
342
343 rollback to UPDATE_ROW_PVT;
344
345 p_return_status := FND_API.G_RET_STS_ERROR;
346
347 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
348 p_data => p_msg_data);
349
350
351 when FND_API.G_EXC_UNEXPECTED_ERROR then
352
353 rollback to UPDATE_ROW_PVT;
354
355 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
356
357 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
358 p_data => p_msg_data);
359
360
361 when OTHERS then
362
363 rollback to UPDATE_ROW_PVT;
364
365 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
366
367 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
368
369 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
370 l_api_name);
371 end if;
372
373 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
374 p_data => p_msg_data);
375
376 END UPDATE_ROW;
377
378 PROCEDURE DELETE_ROW
379 ( p_api_version IN NUMBER,
380 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
381 p_commit IN VARCHAR2 := FND_API.G_FALSE,
382 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
383 p_return_status OUT NOCOPY VARCHAR2,
384 p_msg_count OUT NOCOPY NUMBER,
385 p_msg_data OUT NOCOPY VARCHAR2,
386 --
387 p_ATTRIBUTE_ID IN NUMBER
388 ) AS
389
390 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_ROW';
391 l_api_version CONSTANT NUMBER := 1.0;
392
393 BEGIN
394
395 -- Standard Start of API savepoint
396
397 SAVEPOINT DELETE_ROW_PVT;
398
399 -- Standard call to check for call compatibility.
400
401 if not FND_API.Compatible_API_Call (l_api_version,
402 p_api_version,
403 l_api_name,
404 G_PKG_NAME)
408
405 then
406 raise FND_API.G_EXC_UNEXPECTED_ERROR;
407 end if;
409 -- Initialize message list if p_init_msg_list is set to TRUE.
410
411 if FND_API.to_Boolean (p_init_msg_list) then
412 FND_MSG_PUB.initialize;
413 end if;
414
415 -- Perform the delete
416
417 delete from PSB_ATTRIBUTES_TL
418 where ATTRIBUTE_ID = P_ATTRIBUTE_ID;
419
420 if (sql%notfound) then
421 raise no_data_found;
422 end if;
423
424 DELETE FROM psb_attributes WHERE attribute_id = p_attribute_id;
425
426 if (SQL%NOTFOUND) then
427 RAISE NO_DATA_FOUND;
428 end if;
429
430
431 -- Standard call to get message count and if count is 1, get message info.
432
433 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
434 p_data => p_msg_data);
435
436 -- Standard check of p_commit.
437
438 if FND_API.to_Boolean (p_commit) then
439 commit work;
440 end if;
441
442
443 EXCEPTION
444
445 when FND_API.G_EXC_ERROR then
446
447 rollback to DELETE_ROW_PVT;
448
449 p_return_status := FND_API.G_RET_STS_ERROR;
450
451 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
452 p_data => p_msg_data);
453
454
455 when FND_API.G_EXC_UNEXPECTED_ERROR then
456
457 rollback to DELETE_ROW_PVT;
458
459 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
460
461 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
462 p_data => p_msg_data);
463
464
465 when OTHERS then
466
467 rollback to DELETE_ROW_PVT;
468
469 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
470
471 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
472
473 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
474 l_api_name);
475 end if;
476
477 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
478 p_data => p_msg_data);
479
480 END DELETE_ROW;
481
482 PROCEDURE LOCK_ROW(
483 p_api_version IN NUMBER,
484 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
485 p_commit IN VARCHAR2 := FND_API.G_FALSE,
486 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
487 p_return_status OUT NOCOPY VARCHAR2,
488 p_msg_count OUT NOCOPY NUMBER,
489 p_msg_data OUT NOCOPY VARCHAR2,
490 --
491 p_lock_row OUT NOCOPY VARCHAR2,
492 --
493 p_ROW_ID IN VARCHAR2,
494 p_ATTRIBUTE_ID IN NUMBER,
495 p_BUSINESS_GROUP_ID IN NUMBER,
496 p_NAME IN VARCHAR2,
497 p_DISPLAY_IN_WORKSHEET IN VARCHAR2,
498 p_DISPLAY_SEQUENCE IN NUMBER,
499 p_DISPLAY_PROMPT IN VARCHAR2,
500 p_REQUIRED_FOR_IMPORT_FLAG IN VARCHAR2,
501 p_REQUIRED_FOR_POSITIONS_FLAG IN VARCHAR2,
502 p_ALLOW_IN_POSITION_SET_FLAG IN VARCHAR2,
503 p_VALUE_TABLE_FLAG IN VARCHAR2,
504 p_PROTECTED_FLAG IN VARCHAR2,
505 p_DEFINITION_TYPE IN VARCHAR2,
506 p_DEFINITION_STRUCTURE IN VARCHAR2,
507 p_DEFINITION_TABLE IN VARCHAR2,
508 p_DEFINITION_COLUMN IN VARCHAR2,
509 p_ATTRIBUTE_TYPE_ID IN NUMBER,
510 p_DATA_TYPE IN VARCHAR2,
511 p_APPLICATION_ID IN NUMBER,
512 p_SYSTEM_ATTRIBUTE_TYPE IN VARCHAR2
513 ) AS
514
515 l_api_name CONSTANT VARCHAR2(30) := 'LOCK_ROW';
516 l_api_version CONSTANT NUMBER := 1.0;
517 --
518 counter number;
519
520 cursor c is select
521 ALLOW_IN_POSITION_SET_FLAG,
522 VALUE_TABLE_FLAG,
523 APPLICATION_ID,
524 DEFINITION_TYPE,
525 ATTRIBUTE_TYPE_ID,
526 DATA_TYPE,
527 SYSTEM_ATTRIBUTE_TYPE,
528 BUSINESS_GROUP_ID,
529 REQUIRED_FOR_POSITIONS_FLAG,
530 REQUIRED_FOR_IMPORT_FLAG,
531 PROTECTED_FLAG,
532 DEFINITION_STRUCTURE,
533 DEFINITION_TABLE,
534 DEFINITION_COLUMN,
535 DISPLAY_SEQUENCE,
536 DISPLAY_IN_WORKSHEET
537 from PSB_ATTRIBUTES
538 where ATTRIBUTE_ID = p_ATTRIBUTE_ID
539 for update of ATTRIBUTE_ID nowait;
540 recinfo c%rowtype;
541
542 cursor c1 is select
543 NAME,
544 DISPLAY_PROMPT,
545 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
546 from PSB_ATTRIBUTES_TL
547 where ATTRIBUTE_ID = p_ATTRIBUTE_ID
548 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
549 for update of ATTRIBUTE_ID nowait;
550
551 BEGIN
552 --
553 SAVEPOINT Lock_Row_Pvt ;
554 --
555 IF NOT FND_API.Compatible_API_Call ( l_api_version,
556 p_api_version,
560 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
557 l_api_name,
558 G_PKG_NAME )
559 THEN
561 END IF;
562 --
563
564 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
565 FND_MSG_PUB.initialize ;
566 END IF;
567 --
568 p_return_status := FND_API.G_RET_STS_SUCCESS ;
569 p_lock_row := FND_API.G_TRUE ;
570 --
571 OPEN C;
572 --
573 FETCH C INTO Recinfo;
574 IF (C%NOTFOUND) then
575 CLOSE C;
576 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
577 FND_MSG_PUB.Add;
578 RAISE FND_API.G_EXC_ERROR ;
579 END IF;
580 CLOSE C;
581
582 if ( ((recinfo.ALLOW_IN_POSITION_SET_FLAG = p_ALLOW_IN_POSITION_SET_FLAG)
583 OR ((recinfo.ALLOW_IN_POSITION_SET_FLAG is null) AND (p_ALLOW_IN_POSITION_SET_FLAG is null)))
584 AND ((recinfo.VALUE_TABLE_FLAG = p_VALUE_TABLE_FLAG)
585 OR ((recinfo.VALUE_TABLE_FLAG is null) AND (p_VALUE_TABLE_FLAG is null)))
586 AND ((recinfo.APPLICATION_ID = p_APPLICATION_ID)
587 OR ((recinfo.APPLICATION_ID is null) AND (p_APPLICATION_ID is null)))
588 AND ((recinfo.DEFINITION_TYPE = p_DEFINITION_TYPE)
589 OR ((recinfo.DEFINITION_TYPE is null) AND (p_DEFINITION_TYPE is null)))
590 AND ((recinfo.ATTRIBUTE_TYPE_ID = p_ATTRIBUTE_TYPE_ID)
591 OR ((recinfo.ATTRIBUTE_TYPE_ID is null) AND (p_ATTRIBUTE_TYPE_ID is null)))
592 AND ((recinfo.DATA_TYPE = p_DATA_TYPE)
593 OR ((recinfo.DATA_TYPE is null) AND (p_DATA_TYPE is null)))
594 AND ((recinfo.SYSTEM_ATTRIBUTE_TYPE = p_SYSTEM_ATTRIBUTE_TYPE)
595 OR ((recinfo.SYSTEM_ATTRIBUTE_TYPE is null) AND (p_SYSTEM_ATTRIBUTE_TYPE is null)))
596 AND (recinfo.BUSINESS_GROUP_ID = p_BUSINESS_GROUP_ID)
597 AND ((recinfo.REQUIRED_FOR_POSITIONS_FLAG = p_REQUIRED_FOR_POSITIONS_FLAG)
598 OR ((recinfo.REQUIRED_FOR_POSITIONS_FLAG is null) AND (p_REQUIRED_FOR_POSITIONS_FLAG is null)))
599 AND ((recinfo.REQUIRED_FOR_IMPORT_FLAG = p_REQUIRED_FOR_IMPORT_FLAG)
600 OR ((recinfo.REQUIRED_FOR_IMPORT_FLAG is null) AND (p_REQUIRED_FOR_IMPORT_FLAG is null)))
601 AND ((recinfo.PROTECTED_FLAG = p_PROTECTED_FLAG)
602 OR ((recinfo.PROTECTED_FLAG is null) AND (p_PROTECTED_FLAG is null)))
603 AND ((recinfo.DEFINITION_STRUCTURE = p_DEFINITION_STRUCTURE)
604 OR ((recinfo.DEFINITION_STRUCTURE is null) AND (p_DEFINITION_STRUCTURE is null)))
605 AND ((recinfo.DEFINITION_TABLE = p_DEFINITION_TABLE)
606 OR ((recinfo.DEFINITION_TABLE is null) AND (p_DEFINITION_TABLE is null)))
607 AND ((recinfo.DEFINITION_COLUMN = p_DEFINITION_COLUMN)
608 OR ((recinfo.DEFINITION_COLUMN is null) AND (p_DEFINITION_COLUMN is null)))
609 AND ((recinfo.DISPLAY_SEQUENCE = p_DISPLAY_SEQUENCE)
610 OR ((recinfo.DISPLAY_SEQUENCE is null) AND (p_DISPLAY_SEQUENCE is null)))
611 AND ((recinfo.DISPLAY_IN_WORKSHEET = p_DISPLAY_IN_WORKSHEET)
612 OR ((recinfo.DISPLAY_IN_WORKSHEET is null) AND (p_DISPLAY_IN_WORKSHEET is null)))
613 )
614 THEN
615 Null;
616 ELSE
617 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
618 FND_MSG_PUB.Add;
619 RAISE FND_API.G_EXC_ERROR ;
620 END IF;
621
622 for tlinfo in c1 loop
623 if (tlinfo.BASELANG = 'Y') then
624 if ( (tlinfo.NAME = p_NAME)
625 AND ((tlinfo.DISPLAY_PROMPT = p_DISPLAY_PROMPT)
626 OR ((tlinfo.DISPLAY_PROMPT is null) AND (p_DISPLAY_PROMPT is null)))
627 ) then
628 null;
629 else
630 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
631 app_exception.raise_exception;
632 end if;
633 end if;
634 end loop;
635
636 --
637 IF FND_API.To_Boolean ( p_commit ) THEN
638 COMMIT WORK;
639 END iF;
640 --
641 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
642 p_data => p_msg_data );
643 --
644 EXCEPTION
645 --
646 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
647 --
648 ROLLBACK TO Lock_Row_Pvt ;
649 p_lock_row := FND_API.G_FALSE;
650 p_return_status := FND_API.G_RET_STS_ERROR;
651 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
652 p_data => p_msg_data );
653 --
654 WHEN FND_API.G_EXC_ERROR THEN
655 --
656 ROLLBACK TO Lock_Row_Pvt ;
657 p_return_status := FND_API.G_RET_STS_ERROR;
658 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
659 p_data => p_msg_data );
660 --
661 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
662 --
663 ROLLBACK TO Lock_Row_Pvt ;
664 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
665 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
666 p_data => p_msg_data );
667 --
668 WHEN OTHERS THEN
669 --
670 ROLLBACK TO Lock_Row_Pvt ;
671 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
672 --
673 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
674 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
675 l_api_name);
676 END if;
677 --
678 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
679 p_data => p_msg_data );
680 --
681 END Lock_Row;
682
683
684 PROCEDURE Check_Unique
685 (
686 p_api_version IN NUMBER,
687 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
691 p_msg_count OUT NOCOPY NUMBER,
688 p_commit IN VARCHAR2 := FND_API.G_FALSE,
689 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
690 p_return_status OUT NOCOPY VARCHAR2,
692 p_msg_data OUT NOCOPY VARCHAR2,
693 --
694 p_Row_Id IN VARCHAR2,
695 p_Name IN VARCHAR2,
696 p_business_group_id IN NUMBER,
697 p_Return_Value IN OUT NOCOPY VARCHAR2
698 )
699 AS
700 --
701 l_api_name CONSTANT VARCHAR2(30) := 'Check_Unique';
702 l_api_version CONSTANT NUMBER := 1.0;
703 --
704 l_tmp VARCHAR2(1);
705
706 CURSOR c IS
707 SELECT '1'
708 FROM psb_attributes_VL
709 WHERE name = p_name
710 AND business_group_id = p_business_group_id
711 AND ( (p_Row_Id IS NULL)
712 OR (row_id <> p_Row_Id) );
713 BEGIN
714 --
715 SAVEPOINT Check_Unique_Pvt ;
716 --
717 IF NOT FND_API.Compatible_API_Call ( l_api_version,
718 p_api_version,
719 l_api_name,
720 G_PKG_NAME )
721 THEN
722 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
723 END IF;
724 --
725
726 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
727 FND_MSG_PUB.initialize ;
728 END IF;
729 --
730 p_return_status := FND_API.G_RET_STS_SUCCESS ;
731 --
732
733 -- Checking the Psb_set_relations table for references.
734 OPEN c;
735 FETCH c INTO l_tmp;
736 --
737 -- p_Return_Value tells whether references exist or not.
738 IF l_tmp IS NULL THEN
739 p_Return_Value := 'FALSE';
740 ELSE
741 p_Return_Value := 'TRUE';
742 END IF;
743
744 CLOSE c;
745 --
746 IF FND_API.To_Boolean ( p_commit ) THEN
747 COMMIT WORK;
748 END iF;
749 --
750 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
751 p_data => p_msg_data );
752 --
753 EXCEPTION
754 --
755 WHEN FND_API.G_EXC_ERROR THEN
756 --
757 ROLLBACK TO Check_Unique_Pvt ;
758 p_return_status := FND_API.G_RET_STS_ERROR;
759 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
760 p_data => p_msg_data );
761 --
762 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
763 --
764 ROLLBACK TO Check_Unique_Pvt ;
765 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
766 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
767 p_data => p_msg_data );
768 --
769 WHEN OTHERS THEN
770 --
771 ROLLBACK TO Check_Unique_Pvt ;
772 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
773 --
774 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
775 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
776 l_api_name);
777 END if;
778 --
779 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
780 p_data => p_msg_data );
781 --
782 END Check_Unique;
783
784 PROCEDURE Check_References1
785 (
786 p_api_version IN NUMBER,
787 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
788 p_commit IN VARCHAR2 := FND_API.G_FALSE,
789 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
790 p_return_status OUT NOCOPY VARCHAR2,
791 p_msg_count OUT NOCOPY NUMBER,
792 p_msg_data OUT NOCOPY VARCHAR2,
793 --
794 p_ATTRIBUTE_ID IN NUMBER,
795 p_Return_Value IN OUT NOCOPY VARCHAR2
796 )
797 AS
798 --
799 l_api_name CONSTANT VARCHAR2(30) := 'Check_References';
800 l_api_version CONSTANT NUMBER := 1.0;
801 --
802 l_tmp VARCHAR2(1);
803
804 CURSOR c IS
805 SELECT '1'
806 FROM psb_position_assignments
807 WHERE attribute_id = p_attribute_Id;
808
809 Cursor c1 IS
810 SELECT '1'
811 FROM psb_account_position_set_lines
812 WHERE attribute_id = p_attribute_Id;
813
814 BEGIN
815 --
816 SAVEPOINT Check_References_Pvt ;
817 --
818 IF NOT FND_API.Compatible_API_Call ( l_api_version,
819 p_api_version,
820 l_api_name,
821 G_PKG_NAME )
822 THEN
823 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
824 END IF;
825 --
826
827 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
828 FND_MSG_PUB.initialize ;
829 END IF;
830 --
831 p_return_status := FND_API.G_RET_STS_SUCCESS ;
832 --
833
834 -- Checking the Psb_set_relations table for references.
835 OPEN c;
836 FETCH c INTO l_tmp;
837 --
838 -- p_Return_Value tells whether references exist or not.
839 IF l_tmp IS NULL THEN
840 p_Return_Value := 'FALSE';
841 ELSE
842 p_Return_Value := 'TRUE';
843 END IF;
844
845 CLOSE c;
846 --
847 -- Checking the Psb_Account_Position_Set_lines table for references.
851 --
848 l_tmp := null;
849 OPEN c1;
850 FETCH c1 INTO l_tmp;
852 -- p_Return_Value tells whether references exist or not.
853 IF l_tmp IS NULL THEN
854 p_Return_Value := 'FALSE';
855 ELSE
856 p_Return_Value := 'TRUE';
857 END IF;
858
859 CLOSE c1;
860 IF FND_API.To_Boolean ( p_commit ) THEN
861 COMMIT WORK;
862 END iF;
863 --
864 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
865 p_data => p_msg_data );
866
867 EXCEPTION
868 --
869 WHEN FND_API.G_EXC_ERROR THEN
870 --
871 ROLLBACK TO Check_References_Pvt ;
872 p_return_status := FND_API.G_RET_STS_ERROR;
873 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
874 p_data => p_msg_data );
875 --
876 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
877 --
878 ROLLBACK TO Check_References_Pvt ;
879 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
880 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
881 p_data => p_msg_data );
882 --
883 WHEN OTHERS THEN
884 --
885 ROLLBACK TO Check_References_Pvt ;
886 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
887 --
888 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
889 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
890 l_api_name);
891 END if;
892 --
893 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
894 p_data => p_msg_data );
895 --
896 END Check_References1;
897
898
899
900 PROCEDURE Check_References2
901 (
902 p_api_version IN NUMBER,
903 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
904 p_commit IN VARCHAR2 := FND_API.G_FALSE,
905 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
906 p_return_status OUT NOCOPY VARCHAR2,
907 p_msg_count OUT NOCOPY NUMBER,
908 p_msg_data OUT NOCOPY VARCHAR2,
909 --
910 p_attribute_id IN NUMBER,
911 p_Return_Value IN OUT NOCOPY VARCHAR2
912 )
913 AS
914 --
915 l_api_name CONSTANT VARCHAR2(30) := 'Check_References';
916 l_api_version CONSTANT NUMBER := 1.0;
917 --
918 l_tmp VARCHAR2(1);
919
920 CURSOR c IS
921 SELECT '1'
922 FROM psb_attribute_values
923 WHERE attribute_id = p_attribute_Id;
924
925 BEGIN
926 --
927 SAVEPOINT Check_References_Pvt ;
928 --
929 IF NOT FND_API.Compatible_API_Call ( l_api_version,
930 p_api_version,
931 l_api_name,
932 G_PKG_NAME )
933 THEN
934 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
935 END IF;
936 --
937
938 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
939 FND_MSG_PUB.initialize ;
940 END IF;
941 --
942 p_return_status := FND_API.G_RET_STS_SUCCESS ;
943 --
944
945 -- Checking the Psb_set_relations table for references.
946 OPEN c;
947 FETCH c INTO l_tmp;
948 --
949 -- p_Return_Value tells whether references exist or not.
950 IF l_tmp IS NULL THEN
951 p_Return_Value := 'FALSE';
952 ELSE
953 p_Return_Value := 'TRUE';
954 END IF;
955
956 CLOSE c;
957 --
958 IF FND_API.To_Boolean ( p_commit ) THEN
959 COMMIT WORK;
960 END iF;
961 --
962 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
963 p_data => p_msg_data );
964
965 EXCEPTION
966 --
967 WHEN FND_API.G_EXC_ERROR THEN
968 --
969 ROLLBACK TO Check_References_Pvt ;
970 p_return_status := FND_API.G_RET_STS_ERROR;
971 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
972 p_data => p_msg_data );
973 --
974 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
975 --
976 ROLLBACK TO Check_References_Pvt ;
977 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
978 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
979 p_data => p_msg_data );
980 --
981 WHEN OTHERS THEN
982 --
983 ROLLBACK TO Check_References_Pvt ;
984 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
985 --
986 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
987 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
988 l_api_name);
989 END if;
990 --
991 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
992 p_data => p_msg_data );
993 --
994 END Check_References2;
995
996
997 PROCEDURE Insert_System_Attributes
998 (
999 p_api_version IN NUMBER,
1000 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1001 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1002 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1003 p_return_status OUT NOCOPY VARCHAR2,
1004 p_msg_count OUT NOCOPY NUMBER,
1005 p_msg_data OUT NOCOPY VARCHAR2,
1006 --
1007 p_business_group_id IN NUMBER
1008 )
1009 AS
1010
1014 l_msg_count NUMBER;
1011 l_api_name CONSTANT VARCHAR2(30) := 'Insert_System_Attributes';
1012 l_api_version CONSTANT NUMBER := 1.0;
1013
1015 l_msg_data VARCHAR2(2000);
1016
1017 l_attribute_name VARCHAR2(30);
1018
1019 Temp_ID NUMBER(20);
1020 Temp_Type VARCHAR2(30);
1021
1022 /* bug start 3953023 */
1023 Type l_sys_attributes_rec_type IS RECORD (
1024 l_sys_attribute_type psb_attributes.system_attribute_type%TYPE,
1025 l_attribute_name psb_attributes.name%TYPE,
1026 l_display_worksheet psb_attributes.DISPLAY_IN_WORKSHEET%TYPE,
1027 l_display_seq psb_attributes.DISPLAY_SEQUENCE%TYPE,
1028 l_req_import_flg psb_attributes.REQUIRED_FOR_IMPORT_FLAG%TYPE,
1029 l_req_position_flg psb_attributes.REQUIRED_FOR_POSITIONS_FLAG%TYPE,
1030 l_value_table_flg psb_attributes.VALUE_TABLE_FLAG%TYPE,
1031 l_application_id psb_attributes.APPLICATION_ID%TYPE,
1032 l_data_type psb_attributes.DATA_TYPE%TYPE,
1033 l_allow_pos_set_flg psb_attributes.ALLOW_IN_POSITION_SET_FLAG%TYPE);
1034
1035 -- table defenition and declaration
1036 Type l_sys_attributes_tbl_type IS TABLE OF
1037 l_sys_attributes_rec_type INDEX BY BINARY_INTEGER;
1038
1039 l_sys_attributes_tbl l_sys_attributes_tbl_type;
1040
1041 -- local variables defined
1042 l_exists_attribute BOOLEAN;
1043 l_rowid VARCHAR2(100);
1044 l_attribute_id NUMBER;
1045 /* bug end 3953023 */
1046
1047 BEGIN
1048
1049 SAVEPOINT Insert_System_Attributes ;
1050
1051 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1052 p_api_version,
1053 l_api_name,
1054 G_PKG_NAME )
1055 THEN
1056 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1057 END IF;
1058 --
1059
1060 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
1061 FND_MSG_PUB.initialize ;
1062 END IF;
1063 --
1064 p_return_status := FND_API.G_RET_STS_SUCCESS ;
1065 --
1066
1067 /* bug no 3953023 */
1068 -- load all system attributes into the record type
1069 -- there are 6 system defined attributes that has to be loaded
1070
1071 l_sys_attributes_tbl(1).l_sys_attribute_type := 'JOB_CLASS';
1072 FND_MESSAGE.Set_Name('PSB', 'PSB_PAT_JOB_CLASS_NAME');
1073 l_sys_attributes_tbl(1).l_attribute_name := FND_MESSAGE.get;
1074 l_sys_attributes_tbl(1).l_display_worksheet := 'Y';
1075 l_sys_attributes_tbl(1).l_display_seq := '1';
1076 l_sys_attributes_tbl(1).l_req_import_flg := 'Y';
1077 l_sys_attributes_tbl(1).l_req_position_flg := 'Y';
1078 l_sys_attributes_tbl(1).l_value_table_flg := 'Y';
1079 l_sys_attributes_tbl(1).l_application_id := '';
1080 l_sys_attributes_tbl(1).l_data_type := 'C';
1081 l_sys_attributes_tbl(1).l_allow_pos_set_flg := 'Y';
1082
1083 -- for fte
1084 l_sys_attributes_tbl(2).l_sys_attribute_type := 'FTE';
1085 FND_MESSAGE.Set_Name('PSB', 'PSB_PAT_FTE_NAME');
1086 l_sys_attributes_tbl(2).l_attribute_name := FND_MESSAGE.get;
1087 l_sys_attributes_tbl(2).l_display_worksheet := 'Y';
1088 l_sys_attributes_tbl(2).l_display_seq := '2';
1089 l_sys_attributes_tbl(2).l_req_import_flg := 'Y';
1090 l_sys_attributes_tbl(2).l_req_position_flg := 'Y';
1091 l_sys_attributes_tbl(2).l_value_table_flg := 'N';
1092 l_sys_attributes_tbl(2).l_application_id := '';
1093 l_sys_attributes_tbl(2).l_data_type := 'N';
1094 l_sys_attributes_tbl(2).l_allow_pos_set_flg := '';
1095
1096 -- for organization
1097 l_sys_attributes_tbl(3).l_sys_attribute_type := 'ORG';
1098 FND_MESSAGE.Set_Name('PSB', 'PSB_PAT_ORGANIZATION_NAME');
1099 l_sys_attributes_tbl(3).l_attribute_name := FND_MESSAGE.get;
1100 l_sys_attributes_tbl(3).l_display_worksheet := 'Y';
1101 l_sys_attributes_tbl(3).l_display_seq := '6';
1102 l_sys_attributes_tbl(3).l_req_import_flg := '';
1103 l_sys_attributes_tbl(3).l_req_position_flg := 'Y';
1104 l_sys_attributes_tbl(3).l_value_table_flg := 'Y';
1105 l_sys_attributes_tbl(3).l_application_id := '';
1106 l_sys_attributes_tbl(3).l_data_type := 'C';
1107 l_sys_attributes_tbl(3).l_allow_pos_set_flg := 'Y';
1108
1109 -- for hire Date
1110 l_sys_attributes_tbl(4).l_sys_attribute_type := 'HIREDATE';
1111 FND_MESSAGE.Set_Name('PSB', 'PSB_PAT_HIRE_DATE_NAME');
1112 l_sys_attributes_tbl(4).l_attribute_name := FND_MESSAGE.get;
1113 l_sys_attributes_tbl(4).l_display_worksheet := '';
1114 l_sys_attributes_tbl(4).l_display_seq := '';
1115 l_sys_attributes_tbl(4).l_req_import_flg := '';
1116 l_sys_attributes_tbl(4).l_req_position_flg := '';
1117 l_sys_attributes_tbl(4).l_value_table_flg := '';
1118 l_sys_attributes_tbl(4).l_application_id := '';
1119 l_sys_attributes_tbl(4).l_data_type := 'D';
1120 l_sys_attributes_tbl(4).l_allow_pos_set_flg := '';
1121
1122 -- for adjustment date
1123 l_sys_attributes_tbl(5).l_sys_attribute_type := 'ADJUSTMENT_DATE';
1124 FND_MESSAGE.Set_Name('PSB', 'PSB_PAT_ADJUSTMENT_DATE_NAME');
1125 l_sys_attributes_tbl(5).l_attribute_name := FND_MESSAGE.get;
1126 l_sys_attributes_tbl(5).l_display_worksheet := '';
1127 l_sys_attributes_tbl(5).l_display_seq := '';
1131 l_sys_attributes_tbl(5).l_application_id := '';
1128 l_sys_attributes_tbl(5).l_req_import_flg := '';
1129 l_sys_attributes_tbl(5).l_req_position_flg := '';
1130 l_sys_attributes_tbl(5).l_value_table_flg := '';
1132 l_sys_attributes_tbl(5).l_data_type := 'D';
1133 l_sys_attributes_tbl(5).l_allow_pos_set_flg := '';
1134
1135 -- for default weekly hours
1136 l_sys_attributes_tbl(6).l_sys_attribute_type := 'DEFAULT_WEEKLY_HOURS';
1137 FND_MESSAGE.Set_Name('PSB', 'PSB_PAT_DFLT_WEEKLY_HOURS_NAME');
1138 l_sys_attributes_tbl(6).l_attribute_name := FND_MESSAGE.get;
1139 l_sys_attributes_tbl(6).l_display_worksheet := '';
1140 l_sys_attributes_tbl(6).l_display_seq := '';
1141 l_sys_attributes_tbl(6).l_req_import_flg := 'Y';
1142 l_sys_attributes_tbl(6).l_req_position_flg := '';
1143 l_sys_attributes_tbl(6).l_value_table_flg := '';
1144 l_sys_attributes_tbl(6).l_application_id := '';
1145 l_sys_attributes_tbl(6).l_data_type := 'N';
1146 l_sys_attributes_tbl(6).l_allow_pos_set_flg := '';
1147
1148 FOR l_rec IN 1..l_sys_attributes_tbl.COUNT
1149 LOOP
1150 l_exists_attribute := FALSE;
1151 FOR l_sys_attribute_exist IN
1152 (SELECT system_attribute_type,
1153 attribute_id
1154 FROM psb_attributes
1155 WHERE system_attribute_type =
1156 l_sys_attributes_tbl(l_rec).l_sys_attribute_type
1157 AND business_group_id = p_business_group_id)
1158 LOOP
1159 --update statement as the system attribute statement already exists
1160 l_exists_attribute := TRUE;
1161
1162 UPDATE psb_attributes
1163 SET definition_type = null,
1164 last_update_date = sysdate,
1165 last_updated_by = 1,
1166 last_update_login = null
1167 WHERE attribute_id = l_sys_attribute_exist.attribute_id;
1168
1169 END LOOP;
1170
1171
1172 IF NOT l_exists_attribute THEN
1173 IF l_sys_attributes_tbl(l_rec).l_sys_attribute_type = 'ORG' THEN
1174 FOR l_org_rec IN
1175 (SELECT attribute_id
1176 FROM psb_attributes
1177 WHERE name = l_sys_attributes_tbl(l_rec).l_attribute_name
1178 AND business_group_id = p_business_group_id
1179 )
1180 LOOP
1181 -- update the psb_attributes_tl table
1182 UPDATE psb_attributes_tl
1183 SET name = name || '_X',
1184 last_update_date = sysdate,
1185 last_updated_by = 1,
1186 last_update_login = null
1187 WHERE attribute_id = l_org_rec.attribute_id;
1188 --
1189 -- Bug#5022777 Start.
1190 -- update psb_attributes table also.
1191 UPDATE psb_attributes
1192 SET
1193 name = name || '_X'
1194 , last_update_date = SYSDATE
1195 , last_updated_by = 1
1196 , last_update_login = NULL
1197 WHERE
1198 attribute_id = l_org_rec.attribute_id ;
1199 --
1200 -- Bug#5022777 End.
1201 END LOOP;
1202 END IF;
1203
1204 FOR l_attribute_id_rec IN
1205 (SELECT psb_attributes_s.nextval attribute_id
1206 FROM dual)
1207 LOOP
1208 l_attribute_id := l_attribute_id_rec.attribute_id;
1209 END LOOP;
1210
1211 PSB_POSITION_ATTRIBUTES_PVT.INSERT_ROW
1212 ( p_api_version => 1.0,
1213 p_return_status => p_return_status,
1214 p_msg_count => l_msg_count,
1215 p_msg_data => l_msg_data,
1216 p_row_id => l_rowid,
1217 p_attribute_id => l_attribute_id,
1218 p_business_group_id => p_business_group_id,
1219 p_name
1220 => l_sys_attributes_tbl(l_rec).l_attribute_name,
1221 p_display_in_worksheet
1222 => l_sys_attributes_tbl(l_rec).l_display_worksheet,
1223 p_display_sequence
1224 => l_sys_attributes_tbl(l_rec).l_display_seq,
1225 p_display_prompt
1226 => l_sys_attributes_tbl(l_rec).l_attribute_name,
1227 p_required_for_import_flag
1228 => l_sys_attributes_tbl(l_rec).L_req_import_flg,
1229 p_required_for_positions_flag
1230 => l_sys_attributes_tbl(l_rec).l_req_position_flg,
1231 p_allow_in_position_set_flag
1232 => l_sys_attributes_tbl(l_rec).l_allow_pos_set_flg,
1233 p_value_table_flag
1234 => l_sys_attributes_tbl(l_rec).l_value_table_flg,
1235 p_protected_flag => null,
1236 p_definition_type => null,
1237 p_definition_structure
1238 => null,
1239 p_definition_table => null,
1240 p_definition_column => null,
1241 p_attribute_type_id => null,
1242 p_data_type
1243 => l_sys_attributes_tbl(l_rec).l_data_type,
1244 p_application_id
1245 => l_sys_attributes_tbl(l_rec).l_application_id,
1246 p_system_attribute_type
1247 => l_sys_attributes_tbl(l_rec).l_sys_attribute_type,
1248 p_last_update_date => sysdate,
1249 p_last_updated_by => 1,
1250 p_last_update_login => null,
1251 p_created_by => 1,
1252 p_creation_date => sysdate
1253 );
1254
1255 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1259 END IF;
1256 RAISE FND_API.G_EXC_ERROR;
1257 END IF;
1258
1260 END LOOP;
1261
1262 /* bug end 3953023 */
1263
1264 /* bug start 3953023 */
1265 -- Commenting this piece of code as the code above
1266 -- takes care of inserting system attributes by calling
1267 -- INSERT_ROW api.
1268
1269 /*Begin
1270
1271 Temp_ID := Null;
1272 Temp_Type := '';
1273
1274 FND_MESSAGE.Set_Name('PSB', 'PSB_PAT_JOB_CLASS_NAME');
1275 l_attribute_name := FND_MESSAGE.Get ;
1276
1277 Begin
1278 select SYSTEM_ATTRIBUTE_TYPE,
1279 ATTRIBUTE_ID
1280 into Temp_Type
1281 , Temp_ID
1282 from PSB_ATTRIBUTES
1283 where SYSTEM_ATTRIBUTE_TYPE = 'JOB_CLASS'
1284 and BUSINESS_GROUP_ID = p_business_group_id;
1285 Exception
1286 When NO_DATA_FOUND then
1287 Temp_Type := '';
1288 Temp_ID := NULL;
1289 End;
1290
1291 if (nvl(Temp_Type,'NULL') <> 'JOB_CLASS') then
1292
1293 select psb_attributes_s.nextval
1294 into Temp_ID
1295 from dual;
1296
1297 INSERT INTO PSB_ATTRIBUTES (
1298 ATTRIBUTE_ID,
1299 BUSINESS_GROUP_ID,
1300 NAME,
1301 DISPLAY_IN_WORKSHEET,
1302 DISPLAY_SEQUENCE,
1303 DISPLAY_PROMPT,
1304 REQUIRED_FOR_IMPORT_FLAG,
1305 REQUIRED_FOR_POSITIONS_FLAG,
1306 VALUE_TABLE_FLAG,
1307 APPLICATION_ID,
1308 DATA_TYPE,
1309 SYSTEM_ATTRIBUTE_TYPE,
1310 LAST_UPDATE_DATE,
1311 LAST_UPDATED_BY,
1312 LAST_UPDATE_LOGIN,
1313 CREATED_BY,
1314 CREATION_DATE,
1315 ALLOW_IN_POSITION_SET_FLAG)
1316 VALUES (
1317 Temp_ID,
1318 p_business_group_id,
1319 l_attribute_name,
1320 'Y',
1321 1,
1322 l_attribute_name,
1323 'Y',
1324 'Y',
1325 'Y',
1326 NULL,
1327 'C',
1328 'JOB_CLASS',
1329 sysdate,
1330 1,
1331 NULL,
1332 1,
1333 sysdate,
1334 'Y'
1335 );
1336
1337 INSERT INTO PSB_ATTRIBUTES_TL(
1338 ATTRIBUTE_ID,
1339 NAME,
1340 DISPLAY_PROMPT,
1341 LANGUAGE,
1342 SOURCE_LANG,
1343 LAST_UPDATE_DATE,
1344 LAST_UPDATED_BY,
1345 LAST_UPDATE_LOGIN,
1346 CREATED_BY,
1347 CREATION_DATE)
1348 VALUES (
1349 Temp_ID,
1350 l_attribute_name,
1351 l_attribute_name,
1352 --Bug No 2740368 Start
1353 -- 'US',
1354 -- userenv('LANG'),
1355 -- Bug No 2740368 End
1356 'US',
1357 sysdate,
1358 1,
1359 NULL,
1360 1,
1361 sysdate
1362 );
1363 else
1364 Update psb_attributes
1365 set definition_type = null
1366 , last_update_date = sysdate
1367 , last_updated_by = 1
1368 , last_update_login = null
1369 where attribute_id = Temp_ID;
1370
1371 end if;
1372 End;
1373
1374
1375 Begin
1376
1377 Temp_ID := Null;
1378 Temp_Type := '';
1379
1380 FND_MESSAGE.Set_Name('PSB', 'PSB_PAT_FTE_NAME');
1381 l_attribute_name := FND_MESSAGE.Get ;
1382
1383 Begin
1384 select SYSTEM_ATTRIBUTE_TYPE
1385 , ATTRIBUTE_ID
1386 into Temp_Type
1387 , Temp_ID
1388 from PSB_ATTRIBUTES
1389 where SYSTEM_ATTRIBUTE_TYPE = 'FTE'
1390 and BUSINESS_GROUP_ID = p_business_group_id;
1391 Exception
1392 When NO_DATA_FOUND then
1393 Temp_Type := '';
1394 Temp_ID := NULL;
1395 End;
1396
1397 if (nvl(Temp_Type, 'NULL') <> 'FTE') then
1398 select psb_attributes_s.nextval
1399 into Temp_ID
1400 from dual;
1401
1402 INSERT INTO PSB_ATTRIBUTES (
1403 ATTRIBUTE_ID,
1404 BUSINESS_GROUP_ID,
1405 NAME,
1406 DISPLAY_IN_WORKSHEET,
1407 DISPLAY_SEQUENCE,
1408 DISPLAY_PROMPT,
1409 REQUIRED_FOR_IMPORT_FLAG,
1410 REQUIRED_FOR_POSITIONS_FLAG,
1411 VALUE_TABLE_FLAG,
1412 APPLICATION_ID,
1413 DATA_TYPE,
1414 SYSTEM_ATTRIBUTE_TYPE,
1415 LAST_UPDATE_DATE,
1416 LAST_UPDATED_BY,
1417 LAST_UPDATE_LOGIN,
1418 CREATED_BY,
1419 CREATION_DATE,
1420 ALLOW_IN_POSITION_SET_FLAG)
1421 VALUES (
1422 Temp_ID,
1423 p_business_group_id,
1424 l_attribute_name,
1425 'Y',
1426 2,
1427 l_attribute_name,
1428 -- Bug No 2549894 Start
1429 -- NULL,
1430 -- 'Y',
1431 -- Bug No 2549894 End
1432 'Y',
1433 'N',
1434 NULL,
1435 'N',
1436 'FTE',
1437 sysdate,
1438 1,
1439 NULL,
1440 1,
1441 sysdate,
1442 NULL
1443 );
1444
1445 INSERT INTO PSB_ATTRIBUTES_TL(
1446 ATTRIBUTE_ID,
1447 NAME,
1448 DISPLAY_PROMPT,
1449 LANGUAGE,
1450 SOURCE_LANG,
1451 LAST_UPDATE_DATE,
1452 LAST_UPDATED_BY,
1453 LAST_UPDATE_LOGIN,
1454 CREATED_BY,
1455 CREATION_DATE)
1456 VALUES (
1457 Temp_ID,
1458 l_attribute_name,
1459 l_attribute_name,
1460 -- Bug No 2740368 Start
1461 -- 'US',
1462 -- userenv('LANG'),
1463 -- Bug No 2740368 End
1464 'US',
1465 sysdate,
1466 1,
1467 NULL,
1468 1,
1472 Update psb_attributes
1469 sysdate
1470 );
1471 else
1473 set definition_type = null
1474 , last_update_date = sysdate
1475 , last_updated_by = 1
1476 , last_update_login = null
1477 where attribute_id = Temp_ID;
1478 end if;
1479 End;
1480
1481 Begin
1482
1483 Temp_ID := Null;
1484 Temp_Type := '';
1485
1486 FND_MESSAGE.Set_Name('PSB', 'PSB_PAT_ORGANIZATION_NAME');
1487 l_attribute_name := FND_MESSAGE.Get ;
1488
1489 Begin
1490 select SYSTEM_ATTRIBUTE_TYPE
1491 , ATTRIBUTE_ID
1492 into Temp_Type
1493 , Temp_ID
1494 from PSB_ATTRIBUTES
1495 where SYSTEM_ATTRIBUTE_TYPE = 'ORG'
1496 and BUSINESS_GROUP_ID = p_business_group_id;
1497 Exception
1498 When NO_DATA_FOUND then
1499 Temp_Type := '';
1500 Temp_ID := NULL;
1501 End;
1502
1503 if (nvl(Temp_Type, 'NULL') <> 'ORG') then
1504
1505 For C_org_rec in
1506 ( Select attribute_id
1507 From psb_attributes
1508 where upper(name) = upper(l_attribute_name)
1509 and business_group_id = p_business_group_id
1510 )
1511 Loop
1512 update psb_attributes
1513 set name = name || '_X'
1514 , last_update_date = sysdate
1515 , last_updated_by = 1
1516 , last_update_login = null
1517 where attribute_id = c_org_rec.attribute_id;
1518
1519 update psb_attributes_tl
1520 set name = name || '_X'
1521 , last_update_date = sysdate
1522 , last_updated_by = 1
1523 , last_update_login = null
1524 where attribute_id = c_org_rec.attribute_id;
1525 End Loop;
1526
1527 select psb_attributes_s.nextval
1528 into Temp_ID
1529 from dual;
1530
1531 INSERT INTO PSB_ATTRIBUTES (
1532 ATTRIBUTE_ID,
1533 BUSINESS_GROUP_ID,
1534 NAME,
1535 DISPLAY_IN_WORKSHEET,
1536 DISPLAY_SEQUENCE,
1537 DISPLAY_PROMPT,
1538 REQUIRED_FOR_IMPORT_FLAG,
1539 REQUIRED_FOR_POSITIONS_FLAG,
1540 VALUE_TABLE_FLAG,
1541 APPLICATION_ID,
1542 DATA_TYPE,
1543 SYSTEM_ATTRIBUTE_TYPE,
1544 LAST_UPDATE_DATE,
1545 LAST_UPDATED_BY,
1546 LAST_UPDATE_LOGIN,
1547 CREATED_BY,
1548 CREATION_DATE,
1549 ALLOW_IN_POSITION_SET_FLAG)
1550 VALUES (
1551 Temp_ID,
1552 p_business_group_id,
1553 l_attribute_name,
1554 'Y',
1555 6,
1556 l_attribute_name,
1557 NULL,
1558 'Y',
1559 'Y',
1560 NULL,
1561 'C',
1562 'ORG',
1563 sysdate,
1564 1,
1565 NULL,
1566 1,
1567 sysdate,
1568 -- Bug No 2549894 Start
1569 -- NULL,
1570 -- 'Y'
1571 -- Bug No 2549894 End
1572 );
1573
1574 INSERT INTO PSB_ATTRIBUTES_TL(
1575 ATTRIBUTE_ID,
1576 NAME,
1577 DISPLAY_PROMPT,
1578 LANGUAGE,
1579 SOURCE_LANG,
1580 LAST_UPDATE_DATE,
1581 LAST_UPDATED_BY,
1582 LAST_UPDATE_LOGIN,
1583 CREATED_BY,
1584 CREATION_DATE)
1585 VALUES (
1586 Temp_ID,
1587 l_attribute_name,
1588 l_attribute_name,
1589 -- Bug No 2740368 Start
1590 -- 'US',
1591 -- userenv('LANG'),
1592 -- Bug No 2740368 End
1593 'US',
1594 sysdate,
1595 1,
1596 NULL,
1597 1,
1598 sysdate
1599 );
1600 else
1601 Update psb_attributes
1602 set definition_type = null
1603 , last_update_date = sysdate
1604 , last_updated_by = 1
1605 , last_update_login = null
1606 where attribute_id = Temp_ID;
1607 end if;
1608 End;
1609
1610 Begin
1611
1612 Temp_ID := Null;
1613 Temp_Type := '';
1614
1615 FND_MESSAGE.Set_Name('PSB', 'PSB_PAT_HIRE_DATE_NAME');
1616 l_attribute_name := FND_MESSAGE.Get ;
1617
1618 Begin
1619 select SYSTEM_ATTRIBUTE_TYPE
1620 , ATTRIBUTE_ID
1621 into Temp_Type
1622 , Temp_ID
1623 from PSB_ATTRIBUTES
1624 where SYSTEM_ATTRIBUTE_TYPE = 'HIREDATE'
1625 and BUSINESS_GROUP_ID = p_business_group_id;
1626 Exception
1627 When NO_DATA_FOUND then
1628 Temp_Type := '';
1629 Temp_ID := NULL;
1630 End;
1631
1632 if (nvl(Temp_Type, 'NULL') <> 'HIREDATE') then
1633 select psb_attributes_s.nextval
1634 into Temp_ID
1635 from dual;
1636
1637
1638 INSERT INTO PSB_ATTRIBUTES (
1639 ATTRIBUTE_ID,
1640 BUSINESS_GROUP_ID,
1641 NAME,
1642 DISPLAY_IN_WORKSHEET,
1643 DISPLAY_SEQUENCE,
1644 DISPLAY_PROMPT,
1645 REQUIRED_FOR_IMPORT_FLAG,
1646 REQUIRED_FOR_POSITIONS_FLAG,
1647 VALUE_TABLE_FLAG,
1648 APPLICATION_ID,
1649 DATA_TYPE,
1650 SYSTEM_ATTRIBUTE_TYPE,
1651 LAST_UPDATE_DATE,
1652 LAST_UPDATED_BY,
1653 LAST_UPDATE_LOGIN,
1654 CREATED_BY,
1655 CREATION_DATE,
1656 ALLOW_IN_POSITION_SET_FLAG)
1657 VALUES (
1658 Temp_ID,
1659 p_business_group_id,
1660 l_attribute_name,
1661 NULL,
1662 NULL,
1663 l_attribute_name,
1664 NULL,
1665 NULL,
1666 NULL,
1667 NULL,
1668 'D',
1669 'HIREDATE',
1670 sysdate,
1671 1,
1672 NULL,
1673 1,
1674 sysdate,
1675 NULL
1676 );
1677
1678 INSERT INTO PSB_ATTRIBUTES_TL(
1679 ATTRIBUTE_ID,
1680 NAME,
1681 DISPLAY_PROMPT,
1682 LANGUAGE,
1683 SOURCE_LANG,
1684 LAST_UPDATE_DATE,
1685 LAST_UPDATED_BY,
1686 LAST_UPDATE_LOGIN,
1690 Temp_ID,
1687 CREATED_BY,
1688 CREATION_DATE)
1689 VALUES (
1691 l_attribute_name,
1692 l_attribute_name,
1693 -- Bug No 2740368 Start
1694 -- 'US',
1695 -- userenv('LANG'),
1696 -- Bug No 2740368 End
1697 'US',
1698 sysdate,
1699 1,
1700 NULL,
1701 1,
1702 sysdate
1703 );
1704 else
1705 Update psb_attributes
1706 set definition_type = null
1707 , last_update_date = sysdate
1708 , last_updated_by = 1
1709 , last_update_login = null
1710 where attribute_id = Temp_ID;
1711 end if;
1712 End;
1713
1714
1715 Begin
1716
1717 Temp_ID := Null;
1718 Temp_Type := '';
1719
1720 FND_MESSAGE.Set_Name('PSB', 'PSB_PAT_ADJUSTMENT_DATE_NAME');
1721 l_attribute_name := FND_MESSAGE.Get ;
1722
1723 Begin
1724 select SYSTEM_ATTRIBUTE_TYPE
1725 into Temp_Type
1726 from PSB_ATTRIBUTES
1727 where SYSTEM_ATTRIBUTE_TYPE = 'ADJUSTMENT_DATE'
1728 and BUSINESS_GROUP_ID = p_business_group_id;
1729 Exception
1730 When NO_DATA_FOUND then
1731 Temp_Type := '';
1732 End;
1733
1734 if (nvl(Temp_Type, 'NULL') <> 'ADJUSTMENT_DATE') then
1735 select psb_attributes_s.nextval
1736 into Temp_ID
1737 from dual;
1738
1739
1740 INSERT INTO PSB_ATTRIBUTES (
1741 ATTRIBUTE_ID,
1742 BUSINESS_GROUP_ID,
1743 NAME,
1744 DISPLAY_IN_WORKSHEET,
1745 DISPLAY_SEQUENCE,
1746 DISPLAY_PROMPT,
1747 REQUIRED_FOR_IMPORT_FLAG,
1748 REQUIRED_FOR_POSITIONS_FLAG,
1749 VALUE_TABLE_FLAG,
1750 APPLICATION_ID,
1751 DATA_TYPE,
1752 SYSTEM_ATTRIBUTE_TYPE,
1753 LAST_UPDATE_DATE,
1754 LAST_UPDATED_BY,
1755 LAST_UPDATE_LOGIN,
1756 CREATED_BY,
1757 CREATION_DATE,
1758 ALLOW_IN_POSITION_SET_FLAG)
1759 VALUES (
1760 Temp_ID,
1761 p_business_group_id,
1762 l_attribute_name,
1763 NULL,
1764 NULL,
1765 l_attribute_name,
1766 NULL,
1767 NULL,
1768 NULL,
1769 NULL,
1770 'D',
1771 'ADJUSTMENT_DATE',
1772 sysdate,
1773 1,
1774 NULL,
1775 1,
1776 sysdate,
1777 NULL
1778 );
1779
1780 INSERT INTO PSB_ATTRIBUTES_TL(
1781 ATTRIBUTE_ID,
1782 NAME,
1783 DISPLAY_PROMPT,
1784 LANGUAGE,
1785 SOURCE_LANG,
1786 LAST_UPDATE_DATE,
1787 LAST_UPDATED_BY,
1788 LAST_UPDATE_LOGIN,
1789 CREATED_BY,
1790 CREATION_DATE)
1791 VALUES (
1792 Temp_ID,
1793 l_attribute_name,
1794 l_attribute_name,
1795 -- Bug No 2740368 Start
1796 -- 'US',
1797 -- userenv('LANG'),
1798 -- Bug No 2740368 End
1799 'US',
1800 sysdate,
1801 1,
1802 NULL,
1803 1,
1804 sysdate
1805 );
1806
1807 end if;
1808 End;
1809
1810
1811 Begin
1812
1813 Temp_ID := Null;
1814 Temp_Type := '';
1815
1816 FND_MESSAGE.Set_Name('PSB', 'PSB_PAT_DFLT_WEEKLY_HOURS_NAME');
1817 l_attribute_name := FND_MESSAGE.Get ;
1818
1819 Begin
1820 select SYSTEM_ATTRIBUTE_TYPE
1821 , ATTRIBUTE_ID
1822 into Temp_Type
1823 , Temp_ID
1824 from PSB_ATTRIBUTES
1825 where SYSTEM_ATTRIBUTE_TYPE = 'DEFAULT_WEEKLY_HOURS'
1826 and BUSINESS_GROUP_ID = p_business_group_id;
1827 Exception
1828 When NO_DATA_FOUND then
1829 Temp_Type := '';
1830 Temp_ID := NULL;
1831 End;
1832
1833 if (nvl(Temp_Type, 'NULL') <> 'DEFAULT_WEEKLY_HOURS') then
1834 select psb_attributes_s.nextval
1835 into Temp_ID
1836 from dual;
1837
1838
1839 INSERT INTO PSB_ATTRIBUTES (
1840 ATTRIBUTE_ID,
1841 BUSINESS_GROUP_ID,
1842 NAME,
1843 DISPLAY_IN_WORKSHEET,
1844 DISPLAY_SEQUENCE,
1845 DISPLAY_PROMPT,
1846 REQUIRED_FOR_IMPORT_FLAG,
1847 REQUIRED_FOR_POSITIONS_FLAG,
1848 VALUE_TABLE_FLAG,
1849 APPLICATION_ID,
1850 DATA_TYPE,
1851 SYSTEM_ATTRIBUTE_TYPE,
1852 LAST_UPDATE_DATE,
1853 LAST_UPDATED_BY,
1854 LAST_UPDATE_LOGIN,
1855 CREATED_BY,
1856 CREATION_DATE,
1857 ALLOW_IN_POSITION_SET_FLAG)
1858 VALUES (
1859 Temp_ID,
1860 p_business_group_id,
1861 l_attribute_name,
1862 NULL,
1863 NULL,
1864 l_attribute_name,
1865 -- Bug No 2549894 Start
1866 -- NULL,
1867 -- 'Y',
1868 -- Bug No 2549894 End
1869 NULL,
1870 NULL,
1871 NULL,
1872 'N',
1873 'DEFAULT_WEEKLY_HOURS',
1874 sysdate,
1875 1,
1876 NULL,
1877 1,
1878 sysdate,
1879 NULL
1880 );
1881
1882 INSERT INTO PSB_ATTRIBUTES_TL(
1883 ATTRIBUTE_ID,
1884 NAME,
1885 DISPLAY_PROMPT,
1886 LANGUAGE,
1887 SOURCE_LANG,
1888 LAST_UPDATE_DATE,
1889 LAST_UPDATED_BY,
1890 LAST_UPDATE_LOGIN,
1891 CREATED_BY,
1892 CREATION_DATE)
1893 VALUES (
1894 Temp_ID,
1895 l_attribute_name,
1896 l_attribute_name,
1897 -- Bug No 2740368 Start
1898 -- 'US',
1899 -- userenv('LANG'),
1900 -- Bug No 2740368 End
1901 'US',
1902 sysdate,
1903 1,
1904 NULL,
1905 1,
1906 sysdate
1907 );
1908 else
1909 Update psb_attributes
1910 set definition_type = null
1911 , last_update_date = sysdate
1912 , last_updated_by = 1
1913 , last_update_login = null
1914 where attribute_id = Temp_ID;
1915 end if;
1916 End;*/
1917 -- The code comment ends here
1918
1919 /* bug end 3953023 */
1920
1921 IF FND_API.To_Boolean ( p_commit ) THEN
1922 COMMIT WORK;
1923 END iF;
1924 --
1925 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1926 p_data => p_msg_data );
1927
1928 EXCEPTION
1929 --
1930 WHEN FND_API.G_EXC_ERROR THEN
1931 --
1932 ROLLBACK TO Insert_System_Attributes ;
1933 p_return_status := FND_API.G_RET_STS_ERROR;
1934 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1935 p_data => p_msg_data );
1936 --
1937 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1938 --
1939 ROLLBACK TO Insert_System_Attributes ;
1940 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1941 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1942 p_data => p_msg_data );
1943 --
1944 WHEN OTHERS THEN
1945 --
1946 ROLLBACK TO Insert_System_Attributes ;
1947 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1948 --
1949 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1950 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
1951 l_api_name);
1952 END if;
1953 --
1954 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1955 p_data => p_msg_data );
1956 --
1957 END Insert_System_Attributes ;
1958
1959 FUNCTION GET_TRANSLATED_NAME(p_sys_attribute_type IN varchar2)
1960 RETURN varchar2 IS
1961 l_attribute_name varchar2(2000);
1962 BEGIN
1963 IF p_sys_attribute_type = 'JOB_CLASS' THEN
1964 FND_MESSAGE.Set_Name('PSB', 'PSB_PAT_JOB_CLASS_NAME');
1965 l_attribute_name := FND_MESSAGE.get;
1966 ELSIF
1967 -- for fte
1968 p_sys_attribute_type = 'FTE' THEN
1969 FND_MESSAGE.Set_Name('PSB', 'PSB_PAT_FTE_NAME');
1970 l_attribute_name := FND_MESSAGE.get;
1971
1972 ELSIF
1973 -- for organization
1974 p_sys_attribute_type = 'ORG' THEN
1975 FND_MESSAGE.Set_Name('PSB', 'PSB_PAT_ORGANIZATION_NAME');
1976 l_attribute_name := FND_MESSAGE.get;
1977
1978 ELSIF
1979 -- for hire Date
1980 p_sys_attribute_type = 'HIREDATE' THEN
1981 FND_MESSAGE.Set_Name('PSB', 'PSB_PAT_HIRE_DATE_NAME');
1982 l_attribute_name := FND_MESSAGE.get;
1983
1984 ELSIF
1985 -- for adjustment date
1986 p_sys_attribute_type = 'ADJUSTMENT_DATE' THEN
1987 FND_MESSAGE.Set_Name('PSB', 'PSB_PAT_ADJUSTMENT_DATE_NAME');
1988 l_attribute_name := FND_MESSAGE.get;
1989
1990 ELSIF
1991 -- for default weekly hours
1992 p_sys_attribute_type = 'DEFAULT_WEEKLY_HOURS' THEN
1993 FND_MESSAGE.Set_Name('PSB', 'PSB_PAT_DFLT_WEEKLY_HOURS_NAME');
1994 l_attribute_name := FND_MESSAGE.get;
1995 END IF;
1996
1997 RETURN l_attribute_name;
1998 END GET_TRANSLATED_NAME;
1999
2000 procedure ADD_LANGUAGE
2001 is
2002 begin
2003 delete from PSB_ATTRIBUTES_TL T
2004 where not exists
2005 (select NULL
2006 from PSB_ATTRIBUTES B
2007 where B.ATTRIBUTE_ID = T.ATTRIBUTE_ID
2008 );
2009
2010 update PSB_ATTRIBUTES_TL T set (
2011 NAME
2012 ) = (select
2013 B.NAME
2014 from PSB_ATTRIBUTES_TL B
2015 where B.ATTRIBUTE_ID = T.ATTRIBUTE_ID
2016 and B.LANGUAGE = T.SOURCE_LANG)
2017 where (
2018 T.ATTRIBUTE_ID,
2019 T.LANGUAGE
2020 ) in (select
2021 SUBT.ATTRIBUTE_ID,
2022 SUBT.LANGUAGE
2023 from PSB_ATTRIBUTES_TL SUBB, PSB_ATTRIBUTES_TL SUBT
2024 where SUBB.ATTRIBUTE_ID = SUBT.ATTRIBUTE_ID
2025 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
2026 and (SUBB.NAME <> SUBT.NAME
2027 ));
2028
2029 /*Bug#5237452. Added a new method to translate the name
2030 as per the session language. Also modified the sql to
2031 insert data for only the current session language */
2032
2033 insert into PSB_ATTRIBUTES_TL (
2034 ATTRIBUTE_ID,
2035 NAME,
2036 /* Bug No 2777757 Start */
2037 DISPLAY_PROMPT,
2038 /* Bug No 2777757 End */
2039 LAST_UPDATE_DATE,
2040 LAST_UPDATED_BY,
2041 LAST_UPDATE_LOGIN,
2042 CREATED_BY,
2043 CREATION_DATE,
2044 LANGUAGE,
2045 SOURCE_LANG
2046 ) select
2047 B.ATTRIBUTE_ID,
2048 NVL(PSB_POSITION_ATTRIBUTES_PVT.get_translated_name(
2049 S.SYSTEM_ATTRIBUTE_TYPE),B.NAME),
2050 NVL(PSB_POSITION_ATTRIBUTES_PVT.get_translated_name(
2051 S.SYSTEM_ATTRIBUTE_TYPE),B.DISPLAY_PROMPT),
2052 B.LAST_UPDATE_DATE,
2053 B.LAST_UPDATED_BY,
2054 B.LAST_UPDATE_LOGIN,
2055 B.CREATED_BY,
2056 B.CREATION_DATE,
2057 USERENV('LANG'),
2058 USERENV('LANG')
2059 from PSB_ATTRIBUTES_TL B, FND_LANGUAGES L,PSB_ATTRIBUTES S
2060 where L.INSTALLED_FLAG = 'B'
2061 and B.LANGUAGE = L.LANGUAGE_CODE
2062 and S.attribute_id=B.attribute_id
2063 and not exists
2064 (select NULL
2065 from PSB_ATTRIBUTES_TL T
2066 where T.ATTRIBUTE_ID = B.ATTRIBUTE_ID
2067 and T.LANGUAGE = USERENV('LANG'));
2068 end ADD_LANGUAGE;
2069
2070 END PSB_POSITION_ATTRIBUTES_PVT;