[Home] [Help]
PACKAGE BODY: APPS.PSB_ENTITY_SET_PVT
Source
1 PACKAGE BODY PSB_ENTITY_SET_PVT AS
2 /* $Header: PSBVESPB.pls 120.4 2005/03/16 05:33:01 shtripat ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_ENTITY_SET_PVT';
5 -- The flag determines whether to print debug information or not.
6 g_debug_flag VARCHAR2(1) := 'N' ;
7
8 /* ---------------------- Private Procedures -----------------------*/
9
10 PROCEDURE debug
11 (
12 p_message IN VARCHAR2
13
14 ) ;
15
16 PROCEDURE Copy_Attributes
17 (
18 p_api_version IN NUMBER,
19 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
20 p_commit IN VARCHAR2 := FND_API.G_FALSE,
21 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
22 p_return_status OUT NOCOPY VARCHAR2,
23 p_msg_count OUT NOCOPY NUMBER,
24 p_msg_data OUT NOCOPY VARCHAR2,
25 p_source_entity_set_id IN NUMBER,
26 p_source_data_extract_id IN NUMBER,
27 p_target_data_extract_id IN NUMBER,
28 p_entity_type IN VARCHAR2
29 );
30
31 /* ------------------ End of Private Procedures ----------------------*/
32
33 procedure INSERT_ROW (
34 p_api_version IN NUMBER,
35 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
36 p_commit IN VARCHAR2 := FND_API.G_FALSE,
37 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
38 p_return_status OUT NOCOPY VARCHAR2,
39 p_msg_count OUT NOCOPY NUMBER,
40 p_msg_data OUT NOCOPY VARCHAR2,
41 --
42 P_ROWID in OUT NOCOPY VARCHAR2,
43 P_ENTITY_SET_ID in NUMBER,
44 P_ENTITY_TYPE in VARCHAR2,
45 P_NAME in VARCHAR2,
46 P_DESCRIPTION in VARCHAR2,
47 P_BUDGET_GROUP_ID in NUMBER,
48 P_SET_OF_BOOKS_ID in NUMBER,
49 P_DATA_EXTRACT_ID IN NUMBER,
50 P_CONSTRAINT_THRESHOLD in NUMBER,
51 /* Budget Revision Rules Enhancement Start */
52 P_ENABLE_FLAG in VARCHAR2,
53 /* Budget Revision Rules Enhancement End */
54 /* Bug 4151746 Start */
55 P_EXECUTABLE_FROM_POSITION IN VARCHAR2 DEFAULT NULL,
56 /* Bug 4151746 End */
57 P_ATTRIBUTE1 in VARCHAR2,
58 P_ATTRIBUTE2 in VARCHAR2,
59 P_ATTRIBUTE3 in VARCHAR2,
60 P_ATTRIBUTE4 in VARCHAR2,
61 P_ATTRIBUTE5 in VARCHAR2,
62 P_ATTRIBUTE6 in VARCHAR2,
63 P_ATTRIBUTE7 in VARCHAR2,
64 P_ATTRIBUTE8 in VARCHAR2,
65 P_ATTRIBUTE9 in VARCHAR2,
66 P_ATTRIBUTE10 in VARCHAR2,
67 P_CONTEXT in VARCHAR2,
68 p_Last_Update_Date DATE,
69 p_Last_Updated_By NUMBER,
70 p_Last_Update_Login NUMBER,
71 p_Created_By NUMBER,
72 p_Creation_Date DATE
73 ) is
74 --
75 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row';
76 l_api_version CONSTANT NUMBER := 1.0;
77 --
78 cursor C is select ROWID from PSB_ENTITY_SET
79 where ENTITY_SET_ID = P_ENTITY_SET_ID;
80 BEGIN
81 --
82 SAVEPOINT Insert_Row_Pvt ;
83 --
84 IF NOT FND_API.Compatible_API_Call ( l_api_version,
85 p_api_version,
86 l_api_name,
87 G_PKG_NAME )
88 THEN
89 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
90 END IF;
91 --
92
93 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
94 FND_MSG_PUB.initialize ;
95 END IF;
96 --
97 p_return_status := FND_API.G_RET_STS_SUCCESS ;
98 --
99 insert into PSB_ENTITY_SET (
100 ENTITY_SET_ID,
101 ENTITY_TYPE,
102 NAME,
103 DESCRIPTION,
104 BUDGET_GROUP_ID,
105 SET_OF_BOOKS_ID,
106 DATA_EXTRACT_ID,
107 CONSTRAINT_THRESHOLD,
108 /* Budget Revision Rules Enhancement Start */
109 ENABLE_FLAG,
110 /* Budget Revision Rules Enhancement End */
111 /* Bug 4151746 Start */
112 EXECUTABLE_FROM_POSITION,
113 /* Bug 4151746 End */
114 ATTRIBUTE1,
115 ATTRIBUTE2,
116 ATTRIBUTE3,
117 ATTRIBUTE4,
118 ATTRIBUTE5,
119 ATTRIBUTE6,
120 ATTRIBUTE7,
121 ATTRIBUTE8,
122 ATTRIBUTE9,
123 ATTRIBUTE10,
124 CONTEXT,
125 CREATION_DATE,
126 CREATED_BY,
127 LAST_UPDATE_DATE,
128 LAST_UPDATED_BY,
129 LAST_UPDATE_LOGIN
130 ) values (
131 P_ENTITY_SET_ID,
132 P_ENTITY_TYPE,
133 P_NAME,
134 P_DESCRIPTION,
135 P_BUDGET_GROUP_ID,
136 P_SET_OF_BOOKS_ID,
137 P_DATA_EXTRACT_ID,
138 P_CONSTRAINT_THRESHOLD,
139 /* Budget Revision Rules Enhancement Start */
140 P_ENABLE_FLAG,
141 /* Budget Revision Rules Enhancement End */
142 /* Bug 4151746 Start */
143 P_EXECUTABLE_FROM_POSITION,
144 /* Bug 4151746 End */
145 P_ATTRIBUTE1,
146 P_ATTRIBUTE2,
147 P_ATTRIBUTE3,
148 P_ATTRIBUTE4,
149 P_ATTRIBUTE5,
150 P_ATTRIBUTE6,
151 P_ATTRIBUTE7,
152 P_ATTRIBUTE8,
153 P_ATTRIBUTE9,
154 P_ATTRIBUTE10,
155 P_CONTEXT,
156 P_LAST_UPDATE_DATE,
157 P_LAST_UPDATED_BY,
158 P_LAST_UPDATE_DATE,
159 P_LAST_UPDATED_BY,
160 P_LAST_UPDATE_LOGIN
161 );
162 open c;
163 fetch c into P_ROWID;
164 if (c%notfound) then
165 close c;
166 raise no_data_found;
167 end if;
168 close c;
169 --
170
171 --
172 IF FND_API.To_Boolean ( p_commit ) THEN
173 COMMIT WORK;
174 END iF;
175 --
176 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
177 p_data => p_msg_data );
178 --
179 EXCEPTION
180 --
181 WHEN FND_API.G_EXC_ERROR THEN
182 --
183 ROLLBACK TO Insert_Row_Pvt ;
184 p_return_status := FND_API.G_RET_STS_ERROR;
185 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
186 p_data => p_msg_data );
187 --
188 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
189 --
190 ROLLBACK TO Insert_Row_Pvt ;
191 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
192 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
193 p_data => p_msg_data );
194 --
195 WHEN OTHERS THEN
196 --
197 ROLLBACK TO Insert_Row_Pvt ;
198 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
199 --
200 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
201 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
202 l_api_name);
203 END if;
204 --
205 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
206 p_data => p_msg_data );
207 --
208 END Insert_Row;
209
210 procedure LOCK_ROW (
211 p_api_version IN NUMBER,
212 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
213 p_commit IN VARCHAR2 := FND_API.G_FALSE,
214 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
215 p_return_status OUT NOCOPY VARCHAR2,
216 p_msg_count OUT NOCOPY NUMBER,
217 p_msg_data OUT NOCOPY VARCHAR2,
218 p_lock_row OUT NOCOPY VARCHAR2,
219 --
220 P_ENTITY_SET_ID in NUMBER,
221 P_ENTITY_TYPE in VARCHAR2,
222 P_NAME in VARCHAR2,
223 P_DESCRIPTION in VARCHAR2,
224 P_BUDGET_GROUP_ID in NUMBER,
225 P_SET_OF_BOOKS_ID in NUMBER,
226 P_DATA_EXTRACT_ID IN NUMBER,
227 P_CONSTRAINT_THRESHOLD in NUMBER,
228 /* Budget Revision Rules Enhancement Start */
229 P_ENABLE_FLAG in VARCHAR2,
230 /* Budget Revision Rules Enhancement End */
231 /* Bug 4151746 Start */
232 P_EXECUTABLE_FROM_POSITION IN VARCHAR2 DEFAULT NULL,
233 /* Bug 4151746 End */
234 P_ATTRIBUTE1 in VARCHAR2,
235 P_ATTRIBUTE2 in VARCHAR2,
236 P_ATTRIBUTE3 in VARCHAR2,
237 P_ATTRIBUTE4 in VARCHAR2,
238 P_ATTRIBUTE5 in VARCHAR2,
239 P_ATTRIBUTE6 in VARCHAR2,
240 P_ATTRIBUTE7 in VARCHAR2,
241 P_ATTRIBUTE8 in VARCHAR2,
242 P_ATTRIBUTE9 in VARCHAR2,
243 P_ATTRIBUTE10 in VARCHAR2,
244 P_CONTEXT in VARCHAR2
245 ) is
246 --
247 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Row';
248 l_api_version CONSTANT NUMBER := 1.0;
249 --
250 Counter NUMBER;
251 cursor c1 is select
252 ENTITY_TYPE,
253 NAME,
254 DESCRIPTION,
255 BUDGET_GROUP_ID,
256 SET_OF_BOOKS_ID,
257 DATA_EXTRACT_ID,
258 CONSTRAINT_THRESHOLD,
259 /* Budget Revision Rules Enhancement Start */
260 ENABLE_FLAG,
261 /* Budget Revision Rules Enhancement End */
262 /* Bug 4151746 Start */
263 EXECUTABLE_FROM_POSITION,
264 /* Bug 4151746 End */
265 ATTRIBUTE1,
266 ATTRIBUTE2,
267 ATTRIBUTE3,
268 ATTRIBUTE4,
269 ATTRIBUTE5,
270 ATTRIBUTE6,
271 ATTRIBUTE7,
272 ATTRIBUTE8,
273 ATTRIBUTE9,
274 ATTRIBUTE10,
275 CONTEXT
276 from PSB_ENTITY_SET
277 where ENTITY_SET_ID = P_ENTITY_SET_ID
278 for update of ENTITY_SET_ID nowait;
279 tlinfo c1%rowtype;
280 BEGIN
281 --
282 SAVEPOINT Lock_Row_Pvt ;
283 --
284 IF NOT FND_API.Compatible_API_Call ( l_api_version,
285 p_api_version,
286 l_api_name,
287 G_PKG_NAME )
288 THEN
289 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
290 END IF;
291 --
292
293 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
294 FND_MSG_PUB.initialize ;
295 END IF;
296 --
297 p_return_status := FND_API.G_RET_STS_SUCCESS ;
298 --
299 open c1;
300 fetch c1 into tlinfo;
301 if (c1%notfound) then
302 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
303 app_exception.raise_exception;
304 close c1;
305 return;
306 end if;
307 close c1;
308 if ( (tlinfo.ENTITY_TYPE = P_ENTITY_TYPE)
309 AND ((tlinfo.NAME = P_NAME)
310 OR ((tlinfo.NAME is null)
311 AND (P_NAME is null)))
312 AND ((tlinfo.DESCRIPTION = P_DESCRIPTION)
313 OR ((tlinfo.DESCRIPTION is null)
314 AND (P_DESCRIPTION is null)))
315 AND ((tlinfo.BUDGET_GROUP_ID = P_BUDGET_GROUP_ID)
316 OR ((tlinfo.BUDGET_GROUP_ID is null)
317 AND (P_BUDGET_GROUP_ID is null)))
318 AND ((tlinfo.SET_OF_BOOKS_ID = P_SET_OF_BOOKS_ID)
319 OR ((tlinfo.SET_OF_BOOKS_ID is null)
320 AND (P_SET_OF_BOOKS_ID is null)))
321 AND ((tlinfo.DATA_EXTRACT_ID = P_DATA_EXTRACT_ID)
322 OR ((tlinfo.DATA_EXTRACT_ID is null)
323 AND (P_DATA_EXTRACT_ID is null)))
324 AND ((tlinfo.CONSTRAINT_THRESHOLD = P_CONSTRAINT_THRESHOLD)
325 OR ((tlinfo.CONSTRAINT_THRESHOLD is null)
326 AND (P_CONSTRAINT_THRESHOLD is null)))
327 /* Budget Revision Rules Enhancement Start */
328 AND ((tlinfo.ENABLE_FLAG = P_ENABLE_FLAG)
329 OR ((tlinfo.ENABLE_FLAG is null)
330 AND (P_ENABLE_FLAG is null)))
331 /* Budget Revision Rules Enhancement End */
332 /* Bug 4151746 Start */
333 AND ((tlinfo.EXECUTABLE_FROM_POSITION = P_EXECUTABLE_FROM_POSITION)
334 OR ((tlinfo.EXECUTABLE_FROM_POSITION is null)
335 AND (P_EXECUTABLE_FROM_POSITION is null)))
336 /* Bug 4151746 End */
337 AND ((tlinfo.ATTRIBUTE1 = P_ATTRIBUTE1)
338 OR ((tlinfo.ATTRIBUTE1 is null)
339 AND (P_ATTRIBUTE1 is null)))
340 AND ((tlinfo.ATTRIBUTE2 = P_ATTRIBUTE2)
341 OR ((tlinfo.ATTRIBUTE2 is null)
342 AND (P_ATTRIBUTE2 is null)))
343 AND ((tlinfo.ATTRIBUTE3 = P_ATTRIBUTE3)
344 OR ((tlinfo.ATTRIBUTE3 is null)
345 AND (P_ATTRIBUTE3 is null)))
346 AND ((tlinfo.ATTRIBUTE4 = P_ATTRIBUTE4)
347 OR ((tlinfo.ATTRIBUTE4 is null)
348 AND (P_ATTRIBUTE4 is null)))
349 AND ((tlinfo.ATTRIBUTE5 = P_ATTRIBUTE5)
350 OR ((tlinfo.ATTRIBUTE5 is null)
351 AND (P_ATTRIBUTE5 is null)))
352 AND ((tlinfo.ATTRIBUTE6 = P_ATTRIBUTE6)
353 OR ((tlinfo.ATTRIBUTE6 is null)
354 AND (P_ATTRIBUTE6 is null)))
355 AND ((tlinfo.ATTRIBUTE7 = P_ATTRIBUTE7)
356 OR ((tlinfo.ATTRIBUTE7 is null)
357 AND (P_ATTRIBUTE7 is null)))
358 AND ((tlinfo.ATTRIBUTE8 = P_ATTRIBUTE8)
359 OR ((tlinfo.ATTRIBUTE8 is null)
360 AND (P_ATTRIBUTE8 is null)))
361 AND ((tlinfo.ATTRIBUTE9 = P_ATTRIBUTE9)
362 OR ((tlinfo.ATTRIBUTE9 is null)
363 AND (P_ATTRIBUTE9 is null)))
364 AND ((tlinfo.ATTRIBUTE10 = P_ATTRIBUTE10)
365 OR ((tlinfo.ATTRIBUTE10 is null)
366 AND (P_ATTRIBUTE10 is null)))
367 AND ((tlinfo.CONTEXT = P_CONTEXT)
368 OR ((tlinfo.CONTEXT is null)
369 AND (P_CONTEXT is null)))
370 ) then
371 p_lock_row := FND_API.G_TRUE;
372 ELSE
373 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
374 FND_MSG_PUB.Add;
375 RAISE FND_API.G_EXC_ERROR ;
376 END IF;
377
378 --
379 IF FND_API.To_Boolean ( p_commit ) THEN
380 COMMIT WORK;
381 END iF;
382 --
383 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
384 p_data => p_msg_data );
385 --
386 EXCEPTION
387 --
388 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
389 --
390 ROLLBACK TO Lock_Row_Pvt ;
391 p_lock_row := FND_API.G_FALSE;
392 p_return_status := FND_API.G_RET_STS_ERROR;
393 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
394 p_data => p_msg_data );
395 --
396 WHEN FND_API.G_EXC_ERROR THEN
397 --
398 ROLLBACK TO Lock_Row_Pvt ;
399 p_lock_row := FND_API.G_FALSE;
400 p_return_status := FND_API.G_RET_STS_ERROR;
401 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
402 p_data => p_msg_data );
403 --
404 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
405 --
406 ROLLBACK TO Lock_Row_Pvt ;
407 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
408 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
409 p_data => p_msg_data );
410 --
411 WHEN OTHERS THEN
412 --
413 ROLLBACK TO Lock_Row_Pvt ;
414 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
415 --
416 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
417 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
418 l_api_name);
419 END if;
420 --
421 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
422 p_data => p_msg_data );
423 --
424 END Lock_Row;
425
426 procedure UPDATE_ROW (
427 p_api_version IN NUMBER,
428 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
429 p_commit IN VARCHAR2 := FND_API.G_FALSE,
430 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
431 p_return_status OUT NOCOPY VARCHAR2,
432 p_msg_count OUT NOCOPY NUMBER,
433 p_msg_data OUT NOCOPY VARCHAR2,
434 --
435 P_ENTITY_SET_ID in NUMBER,
436 P_ENTITY_TYPE in VARCHAR2,
437 P_NAME in VARCHAR2,
438 P_DESCRIPTION in VARCHAR2,
439 P_BUDGET_GROUP_ID in NUMBER,
440 P_SET_OF_BOOKS_ID in NUMBER,
441 P_DATA_EXTRACT_ID IN NUMBER,
442 P_CONSTRAINT_THRESHOLD in NUMBER,
443 /* Budget Revision Rules Enhancement Start */
444 P_ENABLE_FLAG in VARCHAR2,
445 /* Budget Revision Rules Enhancement End */
446 /* Bug 4151746 Start */
447 P_EXECUTABLE_FROM_POSITION IN VARCHAR2 DEFAULT NULL,
448 /* Bug 4151746 End */
449 P_ATTRIBUTE1 in VARCHAR2,
450 P_ATTRIBUTE2 in VARCHAR2,
451 P_ATTRIBUTE3 in VARCHAR2,
452 P_ATTRIBUTE4 in VARCHAR2,
453 P_ATTRIBUTE5 in VARCHAR2,
454 P_ATTRIBUTE6 in VARCHAR2,
455 P_ATTRIBUTE7 in VARCHAR2,
456 P_ATTRIBUTE8 in VARCHAR2,
457 P_ATTRIBUTE9 in VARCHAR2,
458 P_ATTRIBUTE10 in VARCHAR2,
459 P_CONTEXT in VARCHAR2,
460 p_Last_Update_Date DATE,
461 p_Last_Updated_By NUMBER,
462 p_Last_Update_Login NUMBER
463 ) is
464 --
465 l_api_name CONSTANT VARCHAR2(30) := 'Update_Row';
466 l_api_version CONSTANT NUMBER := 1.0;
467 --
468 BEGIN
469 --
470 SAVEPOINT Update_Row_Pvt ;
471 --
472 IF NOT FND_API.Compatible_API_Call ( l_api_version,
473 p_api_version,
474 l_api_name,
475 G_PKG_NAME )
476 THEN
477 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
478 END IF;
479 --
480
481 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
482 FND_MSG_PUB.initialize ;
483 END IF;
484 --
485 p_return_status := FND_API.G_RET_STS_SUCCESS ;
486 --
487 update PSB_ENTITY_SET set
488 ENTITY_TYPE = P_ENTITY_TYPE,
489 NAME = P_NAME,
490 DESCRIPTION = P_DESCRIPTION,
491 BUDGET_GROUP_ID = P_BUDGET_GROUP_ID,
492 SET_OF_BOOKS_ID = P_SET_OF_BOOKS_ID,
493 DATA_EXTRACT_ID = P_DATA_EXTRACT_ID,
494 CONSTRAINT_THRESHOLD = P_CONSTRAINT_THRESHOLD,
495 /* Budget Revision Rules Enhancement Start */
496 ENABLE_FLAG = P_ENABLE_FLAG,
497 /* Budget Revision Rules Enhancement End */
498 /* Bug 4151746 Start */
499 EXECUTABLE_FROM_POSITION = P_EXECUTABLE_FROM_POSITION,
500 /* Bug 4151746 End */
501 ATTRIBUTE1 = P_ATTRIBUTE1,
502 ATTRIBUTE2 = P_ATTRIBUTE2,
503 ATTRIBUTE3 = P_ATTRIBUTE3,
504 ATTRIBUTE4 = P_ATTRIBUTE4,
505 ATTRIBUTE5 = P_ATTRIBUTE5,
506 ATTRIBUTE6 = P_ATTRIBUTE6,
507 ATTRIBUTE7 = P_ATTRIBUTE7,
508 ATTRIBUTE8 = P_ATTRIBUTE8,
509 ATTRIBUTE9 = P_ATTRIBUTE9,
510 ATTRIBUTE10 = P_ATTRIBUTE10,
511 CONTEXT = P_CONTEXT,
512 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
513 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
514 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN
515 where ENTITY_SET_ID = P_ENTITY_SET_ID
516 ;
517
518 IF (SQL%NOTFOUND) THEN
519 RAISE NO_DATA_FOUND ;
520 END IF;
521
522 --
523 IF FND_API.To_Boolean ( p_commit ) THEN
524 COMMIT WORK;
525 END iF;
526 --
527 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
528 p_data => p_msg_data );
529 --
530 EXCEPTION
531 --
532 WHEN FND_API.G_EXC_ERROR THEN
533 --
534 ROLLBACK TO Update_Row_Pvt ;
535 p_return_status := FND_API.G_RET_STS_ERROR;
536 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
537 p_data => p_msg_data );
538 --
539 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
540 --
541 ROLLBACK TO Update_Row_Pvt ;
542 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
543 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
544 p_data => p_msg_data );
545 --
546 WHEN OTHERS THEN
547 --
548 ROLLBACK TO Update_Row_Pvt ;
549 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
550 --
551 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
552 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
553 l_api_name);
554 END if;
555 --
556 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
557 p_data => p_msg_data );
558 --
559 END Update_Row;
560
561 procedure DELETE_ROW (
562 p_api_version IN NUMBER,
563 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
564 p_commit IN VARCHAR2 := FND_API.G_FALSE,
565 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
566 p_return_status OUT NOCOPY VARCHAR2,
567 p_msg_count OUT NOCOPY NUMBER,
568 p_msg_data OUT NOCOPY VARCHAR2,
569 --
570 P_ENTITY_SET_ID in NUMBER
571 ) is
572 --
573 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Row';
574 l_api_version CONSTANT NUMBER := 1.0;
575 --
576 BEGIN
577 --
578 SAVEPOINT Delete_Row_Pvt ;
579 --
580 IF NOT FND_API.Compatible_API_Call ( l_api_version,
581 p_api_version,
582 l_api_name,
583 G_PKG_NAME )
584 THEN
585 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
586 END IF;
587 --
588
589 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
590 FND_MSG_PUB.initialize ;
591 END IF;
592 --
593 p_return_status := FND_API.G_RET_STS_SUCCESS ;
594 --
595 delete from PSB_ENTITY_SET
596 where ENTITY_SET_ID = P_ENTITY_SET_ID;
597
598 IF (SQL%NOTFOUND) THEN
599 RAISE NO_DATA_FOUND ;
600 END IF;
601
602 --
603 IF FND_API.To_Boolean ( p_commit ) THEN
604 COMMIT WORK;
605 END iF;
606 --
607 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
608 p_data => p_msg_data );
609
610 EXCEPTION
611 --
612 WHEN FND_API.G_EXC_ERROR THEN
613 --
614 ROLLBACK TO Delete_Row_Pvt ;
615 p_return_status := FND_API.G_RET_STS_ERROR;
616 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
617 p_data => p_msg_data );
618 --
619 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
620 --
621 ROLLBACK TO Delete_Row_Pvt ;
622 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
623 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
624 p_data => p_msg_data );
625 --
626 WHEN OTHERS THEN
627 --
628 ROLLBACK TO Delete_Row_Pvt ;
629 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
630 --
631 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
632 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
633 l_api_name);
634 END if;
635 --
636 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
637 p_data => p_msg_data );
638 --
639 END Delete_Row;
640
641 PROCEDURE Copy_Entity_Set
642 ( p_api_version IN NUMBER,
643 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
644 p_commit IN VARCHAR2 := FND_API.G_FALSE,
645 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
646 p_return_status OUT NOCOPY VARCHAR2,
647 p_msg_count OUT NOCOPY NUMBER,
648 p_msg_data OUT NOCOPY VARCHAR2,
649 p_source_entity_set_id IN NUMBER,
650 p_target_entity_set_id IN NUMBER,
651 p_target_data_extract_id IN NUMBER,
652 p_entity_type IN VARCHAR2
653 )
654 AS
655
656 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Parameter_Set';
657 l_api_version CONSTANT NUMBER := 1.0;
658 --
659 l_last_update_date DATE;
660 l_last_updated_by NUMBER;
661 l_last_update_login NUMBER;
662 l_creation_date DATE;
663 l_created_by NUMBER;
664 --
665 l_set_name VARCHAR2(30);
666 l_position_set_name VARCHAR2(30);
667 l_entity_name VARCHAR2(30);
668 l_rowid VARCHAR2(100);
669 l_status VARCHAR2(1);
670 l_return_status VARCHAR2(1);
671 l_msg_count NUMBER;
672 l_msg_data VARCHAR2(1000);
673 l_count NUMBER;
674 l_entity_id NUMBER;
675 l_source_entity_id NUMBER;
676 l_parameter_id NUMBER;
677 l_constraint_id NUMBER;
678 /* Budget Revision Rules Enhancement Start */
679 l_rule_id NUMBER;
680 /* Budget Revision Rules Enhancement End */
681 l_source_parameter_id NUMBER;
682 l_set_relation_id NUMBER;
683 l_account_position_set_id NUMBER;
684 l_parameter_formula_id NUMBER;
685 l_constraint_formula_id NUMBER;
686 l_business_group_id psb_data_extracts.business_group_id%TYPE ;
687 l_source_data_extract_id psb_data_extracts.data_extract_id%TYPE ;
688 l_attribute_id psb_attribute_values.attribute_id%TYPE;
689 l_attribute_value_id psb_attribute_values.attribute_value_id%TYPE;
690 l_attribute_value psb_attribute_values.attribute_value%TYPE;
691 l_create_formula_flag VARCHAR2(1);
692 l_value_table_flag psb_attributes.value_table_flag%TYPE;
693 l_source_option_flag psb_pay_elements.option_flag%TYPE;
694 l_source_salary_flag psb_pay_elements.salary_flag%TYPE;
695 l_source_pay_element_id psb_pay_elements.pay_element_id%TYPE;
696 l_source_pay_element_option_id
697 psb_pay_element_options.pay_element_option_id%TYPE;
698 l_source_element_value psb_pay_element_rates.element_value%TYPE;
699 l_target_pay_element_id psb_pay_elements.pay_element_id%TYPE;
700 l_target_pay_element_option_id
701 psb_pay_element_options.pay_element_option_id%TYPE;
702 l_target_pay_element_rate_id psb_pay_element_rates.pay_element_rate_id%TYPE;
703
704
705 CURSOR l_entity_set_csr IS
706 SELECT *
707 FROM psb_entity_set
708 WHERE entity_set_id = p_source_entity_set_id ;
709 --
710 CURSOR l_find_position_set_id_csr IS
711 SELECT account_position_set_id
712 FROM psb_account_position_sets
713 WHERE name = l_position_set_name
714 and data_extract_id = p_target_data_extract_id ;
715 --
716 CURSOR l_find_attribute_value_id_csr IS
717 SELECT attribute_value_id
718 FROM psb_attribute_values
719 WHERE attribute_id = l_attribute_id
720 and attribute_value = l_attribute_value
721 and data_extract_id = p_target_data_extract_id ;
722 --
723 CURSOR l_find_source_element_csr IS
724 SELECT pay_element_id ,
725 option_flag ,
726 salary_flag
727 FROM psb_pay_elements
728 WHERE name = (select name
729 from psb_pay_elements
730 where pay_element_id=l_source_pay_element_id)
731 and data_extract_id = p_target_data_extract_id ;
732 --
733 CURSOR l_find_element_rate_csr IS
734 SELECT pay_element_rate_id
735 FROM psb_pay_element_rates
736 WHERE pay_element_id = l_target_pay_element_id
737 and element_value = (select element_value
738 from psb_pay_element_rates
739 where pay_element_id=l_source_pay_element_id);
740 --
741 CURSOR l_find_element_option_csr IS
742 SELECT pay_element_option_id
743 FROM psb_pay_element_options
744 WHERE pay_element_id = l_target_pay_element_id
745 and name = (select name
746 from psb_pay_element_options
747 where pay_element_option_id =
748 l_source_pay_element_option_id);
749 --
750 CURSOR l_find_element_option_rate_csr IS
751 SELECT pay_element_rate_id
752 FROM psb_pay_element_rates
753 WHERE pay_element_id = l_target_pay_element_id
754 and pay_element_option_id = l_target_pay_element_option_id
755 and element_value = (select element_value
756 from psb_pay_element_rates
757 where pay_element_id =l_source_pay_element_id
758 and pay_element_option_id = l_source_pay_element_option_id);
759 --
760 CURSOR l_source_data_extract_csr IS
761 SELECT data_extract_id
762 FROM psb_entity_set
763 WHERE entity_set_id = p_source_entity_set_id ;
764 --
765
766 CURSOR l_parameter_formula_csr IS
767 SELECT *
768 FROM psb_parameter_formulas
769 WHERE parameter_id = l_source_entity_id;
770
771 --
772 CURSOR l_constraint_formula_csr IS
773 SELECT *
774 FROM psb_constraint_formulas
775 WHERE constraint_id = l_source_entity_id;
776 --
777 l_entity_set_rec l_entity_set_csr%ROWTYPE ;
778 l_source_data_extract_rec l_source_data_extract_csr%ROWTYPE;
779
780 BEGIN
781
782 -- Standard Start of API savepoint
783
784 SAVEPOINT Copy_Parameter_Set_Pvt;
785
786 -- Standard call to check for call compatibility.
787
788 --
789 IF NOT FND_API.Compatible_API_Call ( l_api_version,
790 p_api_version,
791 l_api_name,
792 G_PKG_NAME )
793 THEN
794 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
795 END IF;
796 --
797
798 -- Initialize message list if p_init_msg_list is set to TRUE.
799
800 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
801 FND_MSG_PUB.initialize ;
802 END IF;
803
804 -- Initialize API return status to success
805
806 p_return_status := FND_API.G_RET_STS_SUCCESS;
807
808 -- API body
809 l_last_update_date := SYSDATE;
810 l_last_updated_by := FND_GLOBAL.USER_ID;
811 l_last_update_login := FND_GLOBAL.LOGIN_ID;
812 l_creation_date := SYSDATE;
813 l_created_by := FND_GLOBAL.USER_ID;
814
815 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
816 RAISE FND_API.G_EXC_ERROR;
817 END IF;
818
819 -- Validate the input parameters.
820 OPEN l_source_data_extract_csr ;
821 FETCH l_source_data_extract_csr INTO l_source_data_extract_id ;
822 CLOSE l_source_data_extract_csr ;
823
824 IF l_source_data_extract_id IS NULL THEN
825
826 Fnd_Message.Set_Name ('PSB', 'PSB_INVALID_DATA_EXTRACT') ;
827 Fnd_Message.Set_Token('DATA_EXTRACT_ID', l_source_data_extract_id ) ;
828 FND_MSG_PUB.Add;
829 RAISE FND_API.G_EXC_ERROR ;
830
831 ELSE
832
833 SELECT business_group_id INTO l_business_group_id
834 FROM psb_data_extracts
835 WHERE data_extract_id = l_source_data_extract_id ;
836
837 END IF ;
838
839 -- Code to copy attributes in advance.
840
841 OPEN l_source_data_extract_csr ;
842 FETCH l_source_data_extract_csr INTO l_source_data_extract_rec ;
843 CLOSE l_source_data_extract_csr ;
844
845 PSB_ENTITY_SET_PVT.Copy_Attributes
846 ( p_api_version => 1.0,
847 p_init_msg_list => null,
848 p_commit => null,
849 p_validation_level => null,
850 p_return_status => l_return_status,
851 p_msg_count => l_msg_count,
852 p_msg_data => l_msg_data,
853 p_source_entity_set_id => p_source_entity_set_id,
854 p_source_data_extract_id => l_source_data_extract_rec.data_extract_id,
855 p_target_data_extract_id => p_target_data_extract_id,
856 p_entity_type => p_entity_type
857 );
858
859 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
860 debug( 'Copy Attributes Process Failed');
861
862 RAISE FND_API.G_EXC_ERROR ;
863
864 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
865 debug( 'Copy Attributes Process Failed');
866
867 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
868
869 END IF;
870
871 -- Code to copy all the desired position sets onto the target data extract
872
873 -- A FOR loop which finds all the position sets associated with the
874 -- source entity set.
875
876 -- Creating entities
877 FOR l_entity_rec IN
878 (
879 SELECT pe.entity_id, pe.entity_type, pe.entity_subtype,
880 pe.name, pe.description, pe.data_extract_id,
881 pe.set_of_books_id, pe.budget_group_id, pe.allocation_type,
882 pe.budget_year_type_id, pe.balance_type,
883 pe.parameter_autoinc_rule, pe.parameter_compound_annually,
884 pe.currency_code, pe.fte_constraint,
885 pe.constraint_detailed_flag,
886 /* Budget Revision Rules Enhancement Start */
887 pe.apply_account_set_flag, pe.balance_account_set_flag,
888 /* Budget Revision Rules Enhancement End */
889 pe.attribute1, pe.attribute2,
890 pe.attribute3, pe.attribute4, pe.attribute5,
891 pe.attribute6, pe.attribute7, pe.attribute8,
892 pe.attribute9, pe.attribute10, pe.context,
893 pe.effective_start_date start_date, pe.effective_end_date end_date,
894 pea.priority,
895 pea.severity_level, pea.effective_start_date,
896 pea.effective_end_date
897 FROM psb_entity pe, psb_entity_assignment pea
898 WHERE pea.entity_set_id = p_source_entity_set_id
899 and pea.entity_id = pe.entity_id
900 )
901 LOOP
902
903 debug( 'Creating entity for the entity id : ' ||
904 l_entity_rec.entity_id ) ;
905
906 --use this assignment for formula rec
907 l_source_entity_id := l_entity_rec.entity_id;
908
909 SELECT psb_entity_s.nextval INTO l_entity_id
910 FROM dual;
911
912 -- Call Create parameter API.
913
914 -- name for the new parameter
915 l_count := 30-(length(l_entity_id)+1);
916
917 IF length(l_entity_rec.name) < l_count THEN
918 l_count := length(l_entity_rec.name);
919 END IF;
920
921 l_entity_name := substr(l_entity_rec.name,1,l_count)||'_'||
922 to_char(l_entity_id);
923
924 PSB_ENTITY_PVT.INSERT_ROW
925 (
926 p_api_version => 1.0,
927 p_init_msg_list => null,
928 p_commit => null,
929 p_validation_level => null,
930 p_return_status => l_return_status,
931 p_msg_count => l_msg_count,
932 p_msg_data => l_msg_data,
933 p_rowid => l_rowid,
934 p_entity_id => l_entity_id,
935 p_entity_type => l_entity_rec.entity_type,
936 p_entity_subtype => l_entity_rec.entity_subtype,
937 p_name => l_entity_name,
938 p_description => l_entity_rec.description,
939 p_data_extract_id => p_target_data_extract_id,
940 p_set_of_books_id => l_entity_rec.set_of_books_id,
941 p_budget_group_id => l_entity_rec.budget_group_id,
942 p_allocation_type => l_entity_rec.allocation_type,
943 p_budget_year_type_id => l_entity_rec.budget_year_type_id,
944 p_balance_type => l_entity_rec.balance_type,
945 p_parameter_autoinc_rule =>
946 l_entity_rec.parameter_autoinc_rule,
947 p_parameter_compound_annually =>
948 l_entity_rec.parameter_compound_annually,
949 p_currency_code => l_entity_rec.currency_code,
950 p_fte_constraint => l_entity_rec.fte_constraint,
951 p_constraint_detailed_flag =>
952 l_entity_rec.constraint_detailed_flag,
953 /* Budget Revision Rules Enhancement Start */
954 p_apply_account_set_flag => l_entity_rec.apply_account_set_flag,
955 p_balance_account_set_flag => l_entity_rec.balance_account_set_flag,
956 /* Budget Revision Rules Enhancement End */
957 p_attribute1 => l_entity_rec.attribute1,
958 p_attribute2 => l_entity_rec.attribute2,
959 p_attribute3 => l_entity_rec.attribute3,
960 p_attribute4 => l_entity_rec.attribute4,
961 p_attribute5 => l_entity_rec.attribute5,
962 p_attribute6 => l_entity_rec.attribute6,
963 p_attribute7 => l_entity_rec.attribute7,
964 p_attribute8 => l_entity_rec.attribute8,
965 p_attribute9 => l_entity_rec.attribute9,
966 p_attribute10 => l_entity_rec.attribute10,
967 p_context => l_entity_rec.context,
968 p_effective_start_date => l_entity_rec.start_date,
969 p_effective_end_date => l_entity_rec.end_date,
970 p_last_update_date => l_last_update_date,
971 p_last_updated_by => l_last_updated_by,
972 p_last_update_login => l_last_update_login,
973 p_created_by => l_created_by,
974 p_creation_date => l_creation_date
975 );
976
977 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
978 debug( 'Entity not Copied for Entity id : ' ||
979 l_entity_rec.entity_id);
980 RAISE FND_API.G_EXC_ERROR ;
981 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
982 debug( 'Entity not Copied for Entity id:' ||
983 l_entity_rec.entity_id);
984 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
985 END IF;
986
987
988 -- creating assignment between the copied parameter set and the
989 -- copied parameters
990
991 PSB_ENTITY_ASSIGNMENT_PVT.INSERT_ROW
992 (
993 p_api_version => 1.0,
994 p_init_msg_list => null,
995 p_commit => null,
996 p_validation_level => null,
997 p_return_status => l_return_status,
998 p_msg_count => l_msg_count,
999 p_msg_data => l_msg_data,
1000 p_rowid => l_rowid,
1001 p_entity_set_id => p_target_entity_set_id,
1002 p_entity_id => l_entity_id,
1003 p_priority => l_entity_rec.priority,
1004 p_severity_level => l_entity_rec.severity_level,
1005 p_effective_start_date => l_entity_rec.effective_start_date,
1006 p_effective_end_date => l_entity_rec.effective_end_date,
1007 p_last_update_date => l_last_update_date,
1008 p_last_updated_by => l_last_updated_by,
1009 p_last_update_login => l_last_update_login,
1010 p_created_by => l_created_by,
1011 p_creation_date => l_creation_date
1012 );
1013
1014 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1015 debug( 'Assignment not created for Source and New
1016 Entity Id:'||l_entity_rec.entity_id||','||l_entity_id);
1017
1018 RAISE FND_API.G_EXC_ERROR ;
1019
1020 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1021 debug( 'Assignment not created for Source and New
1022 Entity Id:'||l_entity_rec.entity_id||','||l_entity_id);
1023
1024 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1025
1026 END IF;
1027
1028 -- Code to copy sets associated with the parameter.
1029 FOR l_sets_rec IN
1030 (
1031 SELECT pas.name, pas.account_or_position_type, psr.*
1032 FROM psb_account_position_sets pas,
1033 psb_set_relations psr
1034 WHERE DECODE(p_entity_type,
1035 'P', psr.parameter_id,
1036 'C', psr.constraint_id,
1037 /* Budget Revision Rules Enhancement Start */
1038 'BRR', psr.rule_id ) = l_entity_rec.entity_id
1039 /* Budget Revision Rules Enhancement End */
1040 and pas.account_position_set_id = psr.account_position_set_id
1041 )
1042 LOOP
1043
1044 IF l_sets_rec.account_or_position_type = 'A' THEN
1045
1046 debug('Processing Account set ' || l_sets_rec.name);
1047
1048 l_account_position_set_id := l_sets_rec.Account_Position_Set_Id;
1049
1050 ELSIF l_sets_rec.account_or_position_type = 'P' THEN
1051
1052 debug('Processing Position set ' || l_sets_rec.name);
1053
1054 l_position_set_name := l_sets_rec.name ;
1055 l_account_position_set_id := NULL ;
1056
1057 OPEN l_find_position_set_id_csr ;
1058 FETCH l_find_position_set_id_csr INTO l_account_position_set_id ;
1059 CLOSE l_find_position_set_id_csr ;
1060
1061 debug ('Matching l_account_position_set_id before creation : ' ||
1062 l_account_position_set_id ) ;
1063
1064 IF l_account_position_set_id IS NULL THEN
1065
1066 PSB_Account_Position_Set_Pvt.Copy_Position_Set
1067 (
1068 p_api_version => 1.0,
1069 p_init_msg_list => null,
1070 p_commit => null,
1071 p_validation_level => null,
1072 p_return_status => l_return_status,
1073 p_msg_count => l_msg_count,
1074 p_msg_data => l_msg_data,
1075 p_source_position_set_id => l_sets_rec.account_position_set_id,
1076 p_source_data_extract_id => l_source_data_extract_id,
1077 p_target_data_extract_id => p_target_data_extract_id,
1078 p_target_business_group_id => l_business_group_id,
1079 p_new_position_set_id => l_account_position_set_id
1080 );
1081
1082 debug( 'New Position Set Id : ' || l_account_position_set_id);
1083
1084 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1085 debug( 'Position Set not Copied for Source'||
1086 l_sets_rec.account_position_set_id);
1087
1088 RAISE FND_API.G_EXC_ERROR ;
1089
1090 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1091 debug( 'Position Set not Copied for Source'||
1092 l_sets_rec.account_position_set_id);
1093
1094 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1095
1096 END IF;
1097
1098 END IF; -- End checking whether to create a new position set or not.
1099
1100 END IF; -- End checking account position set type.
1101
1102 IF l_sets_rec.account_or_position_type = 'A' OR
1103 l_account_position_set_id IS NOT NULL
1104 THEN
1105
1106 SELECT psb_set_relations_s.nextval INTO l_set_relation_id
1107 FROM dual;
1108
1109 IF p_entity_type = 'P' THEN
1110 l_parameter_id := l_entity_id;
1111 l_constraint_id := l_sets_rec.constraint_id;
1112 l_rule_id := l_sets_rec.rule_id;
1113 ELSIF p_entity_type = 'C' THEN
1114 l_parameter_id := l_sets_rec.parameter_id;
1115 l_constraint_id := l_entity_id;
1116 l_rule_id := l_sets_rec.rule_id;
1117 /* Budget Revision Rules Enhancement Start */
1118 ELSIF p_entity_type = 'BRR' THEN
1119 l_parameter_id := l_sets_rec.parameter_id;
1120 l_constraint_id := l_sets_rec.constraint_id;
1121 l_rule_id := l_entity_id;
1122 /* Budget Revision Rules Enhancement End */
1123 END IF;
1124
1125 PSB_SET_RELATION_PVT.INSERT_ROW
1126 (
1127 p_api_version => 1.0,
1128 p_init_msg_list => null,
1129 p_commit => null,
1130 p_validation_level => null,
1131 p_return_status => l_return_status,
1132 p_msg_count => l_msg_count,
1133 p_msg_data => l_msg_data,
1134 p_row_id => l_rowid,
1135 p_Set_Relation_Id => l_Set_Relation_Id,
1136 p_Account_Position_Set_Id => l_Account_Position_Set_Id,
1137 p_Allocation_Rule_Id => l_sets_rec.Allocation_Rule_Id,
1138 p_Budget_Group_Id => l_sets_rec.Budget_Group_Id,
1139 p_Budget_Workflow_Rule_Id => l_sets_rec.Budget_Workflow_Rule_Id,
1140 p_Constraint_Id => l_Constraint_Id,
1141 p_Default_Rule_Id => l_sets_rec.Default_Rule_Id,
1142 p_Parameter_Id => l_Parameter_Id,
1143 p_Position_Set_Group_Id => l_sets_rec.Position_Set_Group_Id,
1144 /* Budget Revision Rules Enhancement Start */
1145 p_Rule_Id => l_Rule_Id,
1146 p_Apply_Balance_Flag => l_sets_rec.Apply_Balance_Flag,
1147 /* Budget Revision Rules Enhancement End */
1148 p_Effective_Start_Date => l_sets_rec.Effective_Start_Date,
1149 p_Effective_End_Date => l_sets_rec.Effective_End_Date,
1150 p_last_update_date => l_last_update_date,
1151 p_last_updated_by => l_last_updated_by,
1152 p_last_update_login => l_last_update_login,
1153 p_created_by => l_created_by,
1154 p_creation_date => l_creation_date
1155 );
1156
1157 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1158 debug( 'Set Relation not created for:'||l_sets_rec.set_relation_id);
1159 RAISE FND_API.G_EXC_ERROR ;
1160 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1161 debug( 'Set Relation not created for:'||l_sets_rec.set_relation_id);
1162 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1163 END IF;
1164
1165 END IF;
1166
1167 END LOOP; -- Processing sets within a parameter.
1168
1169 -- Code to copy formulas associated with the parameter.
1170 -- Find the matching IDs based on the assignment type.
1171
1172 IF p_entity_type ='P' THEN
1173
1174 FOR l_para_formula_rec IN l_parameter_formula_csr
1175
1176 LOOP
1177
1178 l_create_formula_flag := 'N' ;
1179
1180 IF l_entity_rec.entity_subtype = 'ACCOUNT' THEN
1181 l_create_formula_flag := 'Y' ;
1182 END IF;
1183
1184 IF l_entity_rec.entity_subtype = 'POSITION' THEN
1185
1186 IF l_para_formula_rec.assignment_type = 'ATTRIBUTE' THEN
1187
1188 debug('Processing Entity formula - attribute value : ' ||
1189 l_para_formula_rec.attribute_value );
1190
1191 l_attribute_id := l_para_formula_rec.attribute_id;
1192 l_attribute_value := l_para_formula_rec.attribute_value;
1193 l_attribute_value_id := NULL;
1194
1195 -- Find if the value table exists for the attribute.
1196 SELECT NVL(value_table_flag, 'N') INTO l_value_table_flag
1197 FROM psb_attributes
1198 WHERE attribute_id = l_attribute_id ;
1199
1200 IF l_value_table_flag = 'Y' THEN
1201
1202 OPEN l_find_attribute_value_id_csr ;
1203 FETCH l_find_attribute_value_id_csr INTO l_attribute_value_id ;
1204 CLOSE l_find_attribute_value_id_csr ;
1205
1206 debug ('Found matching l_attribute_value_id : ' ||
1207 l_attribute_value_id ) ;
1208
1209 IF l_attribute_value_id IS NOT NULL THEN
1210 l_create_formula_flag := 'Y' ;
1211 END IF ;
1212
1213 ELSIF l_value_table_flag = 'N' THEN
1214
1215 l_create_formula_flag := 'Y' ;
1216
1217 END IF ;
1218
1219 ELSIF l_para_formula_rec.assignment_type = 'ELEMENT' THEN
1220
1221 l_source_pay_element_id := l_para_formula_rec.pay_element_id;
1222 l_source_pay_element_option_id :=
1223 l_para_formula_rec.pay_element_option_id;
1224 l_source_element_value := l_para_formula_rec.element_value;
1225 l_target_pay_element_id := NULL;
1226 l_target_pay_element_option_id := NULL;
1227 l_target_pay_element_rate_id := NULL;
1228
1229 OPEN l_find_source_element_csr ;
1230 FETCH l_find_source_element_csr INTO
1231 l_target_pay_element_id,
1232 l_source_option_flag,
1233 l_source_salary_flag ;
1234 CLOSE l_find_source_element_csr ;
1235
1236 --Call to create element used in the formula if it does not
1237 --exist in target data extract
1238
1239 IF l_target_pay_element_id IS NULL THEN
1240
1241 OPEN l_source_data_extract_csr ;
1242 FETCH l_source_data_extract_csr INTO l_source_data_extract_rec ;
1243 CLOSE l_source_data_extract_csr ;
1244
1245 debug( 'Processing Copy Entity Element:'||
1246 l_para_formula_rec.pay_element_id);
1247
1248 PSB_PAY_ELEMENTS_PVT.Copy_Pay_Elements
1249 ( p_api_version => 1.0,
1250 p_init_msg_list => null,
1251 p_commit => null,
1252 p_validation_level => null,
1253 p_return_status => l_return_status,
1254 p_msg_count => l_msg_count,
1255 p_msg_data => l_msg_data,
1256 p_source_pay_element_id => l_para_formula_rec.pay_element_id,
1257 p_source_data_extract_id => l_source_data_extract_rec.data_extract_id,
1258 p_target_data_extract_id => p_target_data_extract_id
1259 );
1260
1261 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1262 debug( 'Copy Entity Element Process Failed');
1263 RAISE FND_API.G_EXC_ERROR ;
1264 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1265 debug( 'Copy Entity Element Process Failed');
1266 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1267 END IF;
1268
1269 END IF;
1270
1271 -- Check if the new element was created or not.
1272 -- opened the cursor to get new element id, and option and salary flags
1273
1274 OPEN l_find_source_element_csr ;
1275 FETCH l_find_source_element_csr INTO
1276 l_target_pay_element_id,
1277 l_source_option_flag,
1278 l_source_salary_flag ;
1279 CLOSE l_find_source_element_csr ;
1280
1281 IF l_target_pay_element_id IS NOT NULL
1282 -- and l_source_option_flag = l_target_option_flag
1283 -- and l_source_salary_flag = l_target_salary_flag
1284 THEN
1285
1286 IF l_source_option_flag = 'N' THEN
1287
1288 IF l_source_salary_flag = 'N' THEN
1289
1290 l_create_formula_flag := 'Y' ;
1291
1292 ELSIF l_source_salary_flag = 'Y' THEN
1293
1294 -- As per the element logic, the salary_type has to be 'VALUE'
1295 OPEN l_find_element_rate_csr ;
1296 FETCH l_find_element_rate_csr INTO
1297 l_target_pay_element_rate_id ;
1298 CLOSE l_find_element_rate_csr ;
1299
1300 IF l_target_pay_element_rate_id IS NOT NULL THEN
1301
1302 l_create_formula_flag := 'Y' ;
1303
1304 END IF ;
1305
1306 END IF;
1307
1308 ELSIF l_source_option_flag = 'Y' THEN
1309
1310 --The Salary flag check is for salary type and in this case
1311 --we assume that salary type matches
1312
1313 IF l_source_salary_flag in ('N','Y') THEN
1314
1315 OPEN l_find_element_option_csr ;
1316 FETCH l_find_element_option_csr INTO
1317 l_target_pay_element_option_id ;
1318 CLOSE l_find_element_option_csr ;
1319
1320 OPEN l_find_element_option_rate_csr ;
1321 FETCH l_find_element_option_rate_csr INTO
1322 l_target_pay_element_rate_id ;
1323 CLOSE l_find_element_option_rate_csr ;
1324
1325 IF l_target_pay_element_rate_id IS NOT NULL THEN
1326
1327 l_create_formula_flag := 'Y' ;
1328
1329 END IF ; --target rate id check
1330
1331 END IF; --salary flag check
1332
1333 END IF; -- option flag check
1334
1335 ELSE
1336
1337 l_create_formula_flag := 'N' ;
1338
1339 END IF ; -- Checking element name in the target data extract.
1340
1341 END IF ; -- Checking assignment type for a formula within an entity.
1342
1343 END IF;-- checking entity subtype
1344
1345 IF l_create_formula_flag = 'Y' THEN
1346
1347 SELECT psb_parameter_formulas_s.nextval INTO l_parameter_formula_id
1348 FROM dual;
1349
1350 debug('Processing formula' || l_para_formula_rec.parameter_formula_id);
1351
1352 PSB_PARAMETER_FORMULAS_PVT.INSERT_ROW
1353 ( p_api_version => 1.0,
1354 p_init_msg_list => null,
1355 p_commit => null,
1356 p_validation_level => null,
1357 p_return_status => l_return_status,
1358 p_msg_count => l_msg_count,
1359 p_msg_data => l_msg_data,
1360 p_rowid => l_rowid,
1361 p_parameter_formula_id => l_parameter_formula_id,
1362 p_parameter_id => l_entity_id,
1363 p_step_number => l_para_formula_rec.step_number,
1364 p_budget_year_type_id => l_para_formula_rec.budget_year_type_id,
1365 p_balance_type => l_para_formula_rec.balance_type,
1366 p_template_id => l_para_formula_rec.template_id,
1367 p_concatenated_segments => l_para_formula_rec.concatenated_segments,
1368 p_segment1 => l_para_formula_rec.segment1,
1369 p_segment2 => l_para_formula_rec.segment2,
1370 p_segment3 => l_para_formula_rec.segment3,
1371 p_segment4 => l_para_formula_rec.segment4,
1372 p_segment5 => l_para_formula_rec.segment5,
1373 p_segment6 => l_para_formula_rec.segment6,
1374 p_segment7 => l_para_formula_rec.segment7,
1375 p_segment8 => l_para_formula_rec.segment8,
1376 p_segment9 => l_para_formula_rec.segment9,
1377 p_segment10 => l_para_formula_rec.segment10,
1378 p_segment11 => l_para_formula_rec.segment11,
1379 p_segment12 => l_para_formula_rec.segment12,
1380 p_segment13 => l_para_formula_rec.segment13,
1381 p_segment14 => l_para_formula_rec.segment14,
1382 p_segment15 => l_para_formula_rec.segment15,
1383 p_segment16 => l_para_formula_rec.segment16,
1384 p_segment17 => l_para_formula_rec.segment17,
1385 p_segment18 => l_para_formula_rec.segment18,
1386 p_segment19 => l_para_formula_rec.segment19,
1387 p_segment20 => l_para_formula_rec.segment20,
1388 p_segment21 => l_para_formula_rec.segment21,
1389 p_segment22 => l_para_formula_rec.segment22,
1390 p_segment23 => l_para_formula_rec.segment23,
1391 p_segment24 => l_para_formula_rec.segment24,
1392 p_segment25 => l_para_formula_rec.segment25,
1393 p_segment26 => l_para_formula_rec.segment26,
1394 p_segment27 => l_para_formula_rec.segment27,
1395 p_segment28 => l_para_formula_rec.segment28,
1396 p_segment29 => l_para_formula_rec.segment29,
1397 p_segment30 => l_para_formula_rec.segment30,
1398 p_currency_code => l_para_formula_rec.currency_code,
1399 p_amount => l_para_formula_rec.amount,
1400 p_prefix_operator => l_para_formula_rec.prefix_operator,
1401 p_postfix_operator => l_para_formula_rec.postfix_operator,
1402 p_hiredate_between_FROM =>
1403 l_para_formula_rec.hiredate_between_from,
1404 p_hiredate_between_to => l_para_formula_rec.hiredate_between_to,
1405 p_adjdate_between_FROM =>
1406 l_para_formula_rec.adjdate_between_from,
1407 p_adjdate_between_to => l_para_formula_rec.adjdate_between_to,
1408 p_increment_by => l_para_formula_rec.increment_by,
1409 p_increment_type => l_para_formula_rec.increment_type,
1410 p_assignment_type => l_para_formula_rec.assignment_type,
1411 p_attribute_id => l_para_formula_rec.attribute_id,
1412 p_attribute_value => l_para_formula_rec.attribute_value,
1413 p_pay_element_id => l_target_pay_element_id,
1414 p_pay_element_option_id => l_target_pay_element_option_id,
1415 p_grade_step => l_para_formula_rec.grade_step,
1416 p_element_value => l_para_formula_rec.element_value,
1417 p_element_value_type => l_para_formula_rec.element_value_type,
1418 p_effective_start_date =>
1419 l_para_formula_rec.effective_start_date,
1420 p_effective_end_date => l_para_formula_rec.effective_end_date,
1421 p_attribute1 => l_para_formula_rec.attribute1,
1422 p_attribute2 => l_para_formula_rec.attribute2,
1423 p_attribute3 => l_para_formula_rec.attribute3,
1424 p_attribute4 => l_para_formula_rec.attribute4,
1425 p_attribute5 => l_para_formula_rec.attribute5,
1426 p_attribute6 => l_para_formula_rec.attribute6,
1427 p_attribute7 => l_para_formula_rec.attribute7,
1428 p_attribute8 => l_para_formula_rec.attribute8,
1429 p_attribute9 => l_para_formula_rec.attribute9,
1430 p_attribute10 => l_para_formula_rec.attribute10,
1431 p_context => l_para_formula_rec.context,
1432 p_last_update_date => l_last_update_date,
1433 p_last_updated_by => l_last_updated_by,
1434 p_last_update_login => l_last_update_login,
1435 p_created_by => l_created_by,
1436 p_creation_date => l_creation_date
1437 );
1438
1439 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1440 debug( 'Formula not created for:' ||
1441 l_para_formula_rec.parameter_formula_id);
1442 RAISE FND_API.G_EXC_ERROR ;
1443 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1444 debug( 'Formula not created for:' ||
1445 l_para_formula_rec.parameter_formula_id);
1446 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1447 END IF;
1448
1449 END IF; -- Checking position sub type while processing
1450 -- a formula within a parameter.
1451
1452 END LOOP; -- Processing formulas within a parameter.
1453
1454 ELSIF
1455
1456 p_entity_type = 'C' THEN
1457
1458 FOR l_const_formula_rec IN l_constraint_formula_csr
1459
1460 LOOP
1461
1462 l_create_formula_flag := 'N' ;
1463
1464 IF l_entity_rec.entity_subtype = 'ACCOUNT' THEN
1465 l_create_formula_flag := 'Y' ;
1466 END IF;
1467
1468 IF l_entity_rec.entity_subtype = 'POSITION' THEN
1469
1470 l_source_pay_element_id := l_const_formula_rec.pay_element_id;
1471 l_source_pay_element_option_id :=
1472 l_const_formula_rec.pay_element_option_id;
1473 l_source_element_value := l_const_formula_rec.element_value;
1474 l_target_pay_element_id := NULL;
1475 l_target_pay_element_option_id := NULL;
1476 l_target_pay_element_rate_id := NULL;
1477
1478 OPEN l_find_source_element_csr ;
1479 FETCH l_find_source_element_csr INTO
1480 l_target_pay_element_id,
1481 l_source_option_flag,
1482 l_source_salary_flag ;
1483 CLOSE l_find_source_element_csr ;
1484
1485 --Call to create element used in the constraint if it does not
1486 --exist in target data extract
1487
1488 IF l_target_pay_element_id IS NULL THEN
1489
1490 OPEN l_source_data_extract_csr ;
1491 FETCH l_source_data_extract_csr INTO l_source_data_extract_rec ;
1492 CLOSE l_source_data_extract_csr ;
1493
1494 debug( 'Processing Copy Entity Element:'||
1495 l_const_formula_rec.pay_element_id);
1496
1497 PSB_PAY_ELEMENTS_PVT.Copy_Pay_Elements
1498 ( p_api_version => 1.0,
1499 p_init_msg_list => null,
1500 p_commit => null,
1501 p_validation_level => null,
1502 p_return_status => l_return_status,
1503 p_msg_count => l_msg_count,
1504 p_msg_data => l_msg_data,
1505 p_source_pay_element_id => l_const_formula_rec.pay_element_id,
1506 p_source_data_extract_id => l_source_data_extract_rec.data_extract_id,
1507 p_target_data_extract_id => p_target_data_extract_id
1508 );
1509
1510 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1511 debug( 'Copy Entity Element Process Failed');
1512 RAISE FND_API.G_EXC_ERROR ;
1513 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1514 debug( 'Copy Entity Element Process Failed');
1515 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1516 END IF;
1517
1518 END IF;
1519
1520 -- Check if the new element was created or not.
1521 -- opened the cursor to get new element id, and option and salary flags
1522
1523 OPEN l_find_source_element_csr ;
1524 FETCH l_find_source_element_csr INTO
1525 l_target_pay_element_id,
1526 l_source_option_flag,
1527 l_source_salary_flag ;
1528 CLOSE l_find_source_element_csr ;
1529
1530 IF l_target_pay_element_id IS NOT NULL
1531 -- and l_source_option_flag = l_target_option_flag
1532 -- and l_source_salary_flag = l_target_salary_flag
1533 THEN
1534
1535 IF l_source_option_flag = 'N' THEN
1536
1537 IF l_source_salary_flag = 'N' THEN
1538
1539 l_create_formula_flag := 'Y' ;
1540
1541 ELSIF l_source_salary_flag = 'Y' THEN
1542
1543 -- As per the element logic, the salary_type has to be 'VALUE'
1544 OPEN l_find_element_rate_csr ;
1545 FETCH l_find_element_rate_csr INTO
1546 l_target_pay_element_rate_id ;
1547 CLOSE l_find_element_rate_csr ;
1548
1549 IF l_target_pay_element_rate_id IS NOT NULL THEN
1550
1551 l_create_formula_flag := 'Y' ;
1552
1553 END IF ;
1554
1555 END IF;
1556
1557 ELSIF l_source_option_flag = 'Y' THEN
1558
1559 --The Salary flag check is for salary type and in this case
1560 --we assume that salary type matches
1561
1562 IF l_source_salary_flag in ('N','Y') THEN
1563
1564 OPEN l_find_element_option_csr ;
1565 FETCH l_find_element_option_csr INTO
1566 l_target_pay_element_option_id ;
1567 CLOSE l_find_element_option_csr ;
1568
1569 OPEN l_find_element_option_rate_csr ;
1570 FETCH l_find_element_option_rate_csr INTO
1571 l_target_pay_element_rate_id ;
1572 CLOSE l_find_element_option_rate_csr ;
1573
1574 IF l_target_pay_element_rate_id IS NOT NULL THEN
1575
1576 l_create_formula_flag := 'Y' ;
1577
1578 END IF ;
1579
1580 END IF;--Salary flag check
1581
1582 END IF;--option flag check
1583
1584 ELSE
1585
1586 l_create_formula_flag := 'N' ;
1587
1588 END IF ; -- Checking element name in the target data extract.
1589
1590 END IF;--checking entity_subtype
1591
1592 IF l_create_formula_flag = 'Y' THEN
1593
1594 SELECT psb_constraint_formulas_s.nextval INTO l_constraint_formula_id
1595 FROM dual;
1596
1597 debug('Processing formula for constraint_formula_id: ' ||
1598 l_const_formula_rec.constraint_formula_id);
1599
1600 PSB_CONSTRAINT_FORMULAS_PVT.INSERT_ROW
1601 ( p_api_version => 1.0,
1602 p_init_msg_list => null,
1603 p_commit => null,
1604 p_validation_level => null,
1605 p_return_status => l_return_status,
1606 p_msg_count => l_msg_count,
1607 p_msg_data => l_msg_data,
1608 p_rowid => l_rowid,
1609 p_constraint_formula_id => l_constraint_formula_id,
1610 p_constraint_id => l_entity_id,
1611 p_step_number => l_const_formula_rec.step_number,
1612 p_budget_year_type_id => l_const_formula_rec.budget_year_type_id,
1613 p_balance_type => l_const_formula_rec.balance_type,
1614 p_currency_code => l_const_formula_rec.currency_code,
1615 p_template_id => l_const_formula_rec.template_id,
1616 p_segment1 => l_const_formula_rec.segment1,
1617 p_segment2 => l_const_formula_rec.segment2,
1618 p_segment3 => l_const_formula_rec.segment3,
1619 p_segment4 => l_const_formula_rec.segment4,
1620 p_segment5 => l_const_formula_rec.segment5,
1621 p_segment6 => l_const_formula_rec.segment6,
1622 p_segment7 => l_const_formula_rec.segment7,
1623 p_segment8 => l_const_formula_rec.segment8,
1624 p_segment9 => l_const_formula_rec.segment9,
1625 p_segment10 => l_const_formula_rec.segment10,
1626 p_segment11 => l_const_formula_rec.segment11,
1627 p_segment12 => l_const_formula_rec.segment12,
1628 p_segment13 => l_const_formula_rec.segment13,
1629 p_segment14 => l_const_formula_rec.segment14,
1630 p_segment15 => l_const_formula_rec.segment15,
1631 p_segment16 => l_const_formula_rec.segment16,
1632 p_segment17 => l_const_formula_rec.segment17,
1633 p_segment18 => l_const_formula_rec.segment18,
1634 p_segment19 => l_const_formula_rec.segment19,
1635 p_segment20 => l_const_formula_rec.segment20,
1636 p_segment21 => l_const_formula_rec.segment21,
1637 p_segment22 => l_const_formula_rec.segment22,
1638 p_segment23 => l_const_formula_rec.segment23,
1639 p_segment24 => l_const_formula_rec.segment24,
1640 p_segment25 => l_const_formula_rec.segment25,
1641 p_segment26 => l_const_formula_rec.segment26,
1642 p_segment27 => l_const_formula_rec.segment27,
1643 p_segment28 => l_const_formula_rec.segment28,
1644 p_segment29 => l_const_formula_rec.segment29,
1645 p_segment30 => l_const_formula_rec.segment30,
1646 p_amount => l_const_formula_rec.amount,
1647 p_prefix_operator => l_const_formula_rec.prefix_operator,
1648 p_postfix_operator => l_const_formula_rec.postfix_operator,
1649 p_pay_element_id => l_target_pay_element_id,
1650 p_pay_element_option_id => l_target_pay_element_option_id,
1651 p_allow_modify => l_const_formula_rec.allow_modify,
1652 p_element_value => l_const_formula_rec.element_value,
1653 p_element_value_type =>
1654 l_const_formula_rec.element_value_type,
1655 p_effective_start_date =>
1656 l_const_formula_rec.effective_start_date,
1657 p_effective_end_date => l_const_formula_rec.effective_end_date,
1658 p_attribute1 => l_const_formula_rec.attribute1,
1659 p_attribute2 => l_const_formula_rec.attribute2,
1660 p_attribute3 => l_const_formula_rec.attribute3,
1661 p_attribute4 => l_const_formula_rec.attribute4,
1662 p_attribute5 => l_const_formula_rec.attribute5,
1663 p_context => l_const_formula_rec.context,
1664 p_concatenated_segments =>
1665 l_const_formula_rec.concatenated_segments,
1666 p_last_update_date => l_last_update_date,
1667 p_last_updated_by => l_last_updated_by,
1668 p_last_update_login => l_last_update_login,
1669 p_created_by => l_created_by,
1670 p_creation_date => l_creation_date
1671 );
1672
1673 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1674 debug( 'Constraint not created for:' ||
1675 l_const_formula_rec.constraint_formula_id);
1676 RAISE FND_API.G_EXC_ERROR ;
1677 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1678 debug( 'Constraint not created for:' ||
1679 l_const_formula_rec.constraint_formula_id);
1680 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1681 END IF;
1682
1683 END IF; -- Checking account or position sub type while processing
1684 -- a constraint within a parameter.
1685
1686 END LOOP; -- Processing formulas within a constraint
1687
1688 END IF; --Checking for entity type
1689
1690 END LOOP; -- Processing parameters.
1691
1692
1693 -- Standard check of p_commit.
1694 IF FND_API.to_Boolean (p_commit) THEN
1695 COMMIT WORK;
1696 END IF;
1697
1698 -- Standard call to get message count and if count is 1, get message info.
1699
1700 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1701 p_data => p_msg_data);
1702
1703 EXCEPTION
1704
1705 WHEN FND_API.G_EXC_ERROR THEN
1706
1707 ROLLBACK TO Copy_Parameter_Set_Pvt;
1708
1709 p_return_status := FND_API.G_RET_STS_ERROR;
1710
1711 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1712 p_data => p_msg_data);
1713
1714
1715 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1716
1717 ROLLBACK TO Copy_Parameter_Set_Pvt;
1718
1719 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1720
1721 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1722 p_data => p_msg_data);
1723
1724 WHEN OTHERS THEN
1725
1726 ROLLBACK TO Copy_Parameter_Set_Pvt;
1727
1728 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1729
1730 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1731
1732 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1733 l_api_name);
1734 END IF;
1735
1736 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1737 p_data => p_msg_data);
1738
1739 END Copy_Entity_Set;
1740
1741 /*==========================================================================+
1742 | PROCEDURE Copy_Attributes (Private) |
1743 +===========================================================================*/
1744 --
1745 -- Procedure to copy attributes associated with position sets
1746 -- and formulas for parameters and constraints
1747
1748 PROCEDURE Copy_Attributes
1749 ( p_api_version IN NUMBER,
1750 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1751 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1752 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1753 p_return_status OUT NOCOPY VARCHAR2,
1754 p_msg_count OUT NOCOPY NUMBER,
1755 p_msg_data OUT NOCOPY VARCHAR2,
1756 p_source_entity_set_id IN NUMBER,
1757 p_source_data_extract_id IN NUMBER,
1758 p_target_data_extract_id IN NUMBER,
1759 p_entity_type IN VARCHAR2
1760
1761 ) AS
1762
1763 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Attributes';
1764 l_api_version CONSTANT NUMBER := 1.0;
1765 l_last_update_date DATE;
1766 l_last_updated_by NUMBER;
1767 l_last_update_login NUMBER;
1768 l_creation_date DATE;
1769 l_created_by NUMBER;
1770 l_name VARCHAR2(30);
1771 l_rowid VARCHAR2(100);
1772 l_status VARCHAR2(1);
1773 l_return_status VARCHAR2(1);
1774 l_msg_count NUMBER;
1775 l_msg_data VARCHAR2(1000);
1776 l_count NUMBER := 0;
1777 l_attribute_value psb_attribute_values.attribute_value%TYPE;
1778 l_attribute_id NUMBER;
1779 l_attribute_value_id NUMBER;
1780
1781 CURSOR l_attr_value_csr IS
1782 (
1783 SELECT DISTINCT lines.attribute_id, attr_values.attribute_value
1784 FROM psb_entity_assignment assgn,
1785 psb_entity entity,
1786 psb_set_relations rels,
1787 psb_account_position_sets sets,
1788 psb_account_position_set_lines lines,
1789 psb_position_set_line_values pos_val,
1790 psb_attributes attrs,
1791 psb_attribute_values attr_values
1792 WHERE assgn.entity_set_id = p_source_entity_set_id
1793 and assgn.entity_id = entity.entity_id
1794 and entity.entity_subtype = 'POSITION'
1795 and DECODE(p_entity_type,
1796 'P', rels.parameter_id,
1797 'C', rels.constraint_id) = entity.entity_id
1798 and sets.account_position_set_id = rels.account_position_set_id
1799 and sets.account_position_set_id = lines.account_position_set_id
1800 and attrs.attribute_id = lines.attribute_id
1801 and attrs.attribute_id = attr_values.attribute_id
1802 and attr_values.data_extract_id = p_source_data_extract_id
1803 and attrs.value_table_flag = 'Y'
1804 and lines.line_sequence_id = pos_val.line_sequence_id
1805 and pos_val.attribute_value_id = attr_values.attribute_value_id
1806 )
1807 UNION
1808 (
1809 SELECT DISTINCT formulas.attribute_id, attr_values.attribute_value
1810 FROM psb_entity_assignment assgn,
1811 psb_entity entity,
1812 psb_parameter_formulas formulas,
1813 psb_attributes attrs,
1814 psb_attribute_values attr_values
1815 WHERE assgn.entity_set_id = p_source_entity_set_id
1816 and assgn.entity_id = entity.entity_id
1817 and entity.entity_subtype = 'POSITION'
1818 and formulas.parameter_id = entity.entity_id
1819 and formulas.assignment_type = 'ATTRIBUTE'
1820 and formulas.attribute_id = attrs.attribute_id
1821 and attrs.value_table_flag = 'Y'
1822 and attrs.attribute_id = attr_values.attribute_id
1823 and attr_values.data_extract_id = p_source_data_extract_id
1824 );
1825
1826 CURSOR l_attribute_value_csr IS
1827 SELECT attr_values.*
1828 FROM psb_attributes attrs,
1829 psb_attribute_values attr_values
1830 WHERE attrs.value_table_flag = 'Y'
1831 and attrs.attribute_id = l_attribute_id
1832 and attr_values.attribute_value = l_attribute_value
1833 and attr_values.data_extract_id = p_source_data_extract_id;
1834
1835 l_attribute_value_rec l_attribute_value_csr%ROWTYPE;
1836
1837 BEGIN
1838
1839 -- Standard Start of API savepoint
1840
1841 SAVEPOINT Copy_Attributes_Pvt;
1842
1843 -- Standard call to check for call compatibility.
1844
1845 IF not FND_API.Compatible_API_Call (l_api_version,
1846 p_api_version,
1847 l_api_name,
1848 G_PKG_NAME)
1849 THEN
1850 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1851 END IF;
1852
1853 -- Initialize message list if p_init_msg_list is set to TRUE.
1854
1855 IF FND_API.to_Boolean (p_init_msg_list) THEN
1856 FND_MSG_PUB.initialize;
1857 END IF;
1858
1859 -- Initialize API return status to success
1860
1861 p_return_status := FND_API.G_RET_STS_SUCCESS;
1862
1863 -- API body
1864 l_last_update_date := SYSDATE;
1865 l_last_updated_by := FND_GLOBAL.USER_ID;
1866 l_last_update_login := FND_GLOBAL.LOGIN_ID;
1867 l_creation_date := SYSDATE;
1868 l_created_by := FND_GLOBAL.USER_ID;
1869
1870 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1871 RAISE FND_API.G_EXC_ERROR;
1872 END IF;
1873
1874 FOR l_attr_value_rec IN l_attr_value_csr
1875 LOOP
1876
1877 l_attribute_id := l_attr_value_rec.attribute_id;
1878 l_attribute_value := l_attr_value_rec.attribute_value;
1879
1880 SELECT count(*) into l_count
1881 FROM psb_attributes attrs,
1882 psb_attribute_values attr_values
1883 WHERE attrs.value_table_flag = 'Y'
1884 and attrs.attribute_id = l_attribute_id
1885 and attr_values.attribute_value = l_attribute_value
1886 and attr_values.data_extract_id = p_target_data_extract_id;
1887
1888 IF l_count = 0 THEN
1889
1890 OPEN l_attribute_value_csr;
1891 FETCH l_attribute_value_csr INTO l_attribute_value_rec;
1892 CLOSE l_attribute_value_csr;
1893
1894 SELECT psb_attribute_values_s.nextval INTO l_attribute_value_id
1895 FROM dual;
1896
1897 PSB_ATTRIBUTE_VALUES_PVT.INSERT_ROW
1898 ( p_api_version => 1.0,
1899 p_init_msg_list => null,
1900 p_commit => null,
1901 p_validation_level => null,
1902 p_return_status => l_return_status,
1903 p_msg_count => l_msg_count,
1904 p_msg_data => l_msg_data,
1905 p_rowid => l_rowid,
1906 p_attribute_value_id => l_attribute_value_id,
1907 p_attribute_id => l_attribute_value_rec.attribute_id,
1908 p_attribute_value => l_attribute_value_rec.attribute_value,
1909 p_hr_value_id => l_attribute_value_rec.hr_value_id,
1910 p_description => l_attribute_value_rec.description,
1911 p_data_extract_id => p_target_data_extract_id,
1912 p_context => l_attribute_value_rec.context,
1913 p_attribute1 => l_attribute_value_rec.attribute1,
1914 p_attribute2 => l_attribute_value_rec.attribute2,
1915 p_attribute3 => l_attribute_value_rec.attribute3,
1916 p_attribute4 => l_attribute_value_rec.attribute4,
1917 p_attribute5 => l_attribute_value_rec.attribute5,
1918 p_attribute6 => l_attribute_value_rec.attribute6,
1919 p_attribute7 => l_attribute_value_rec.attribute7,
1920 p_attribute8 => l_attribute_value_rec.attribute8,
1921 p_attribute9 => l_attribute_value_rec.attribute9,
1922 p_attribute10 => l_attribute_value_rec.attribute10,
1923 p_attribute11 => l_attribute_value_rec.attribute11,
1924 p_attribute12 => l_attribute_value_rec.attribute12,
1925 p_attribute13 => l_attribute_value_rec.attribute13,
1926 p_attribute14 => l_attribute_value_rec.attribute14,
1927 p_attribute15 => l_attribute_value_rec.attribute15,
1928 p_attribute16 => l_attribute_value_rec.attribute16,
1929 p_attribute17 => l_attribute_value_rec.attribute17,
1930 p_attribute18 => l_attribute_value_rec.attribute18,
1931 p_attribute19 => l_attribute_value_rec.attribute19,
1932 p_attribute20 => l_attribute_value_rec.attribute20,
1933 p_attribute21 => l_attribute_value_rec.attribute21,
1934 p_attribute22 => l_attribute_value_rec.attribute22,
1935 p_attribute23 => l_attribute_value_rec.attribute23,
1936 p_attribute24 => l_attribute_value_rec.attribute24,
1937 p_attribute25 => l_attribute_value_rec.attribute25,
1938 p_attribute26 => l_attribute_value_rec.attribute26,
1939 p_attribute27 => l_attribute_value_rec.attribute27,
1940 p_attribute28 => l_attribute_value_rec.attribute28,
1941 p_attribute29 => l_attribute_value_rec.attribute29,
1942 p_attribute30 => l_attribute_value_rec.attribute30,
1943 p_last_update_date => l_last_update_date,
1944 p_last_updated_by => l_last_updated_by,
1945 p_last_update_login => l_last_update_login,
1946 p_created_by => l_created_by,
1947 p_creation_date => l_creation_date
1948 );
1949
1950 debug( 'Attribute created for attribute value id'||l_attribute_value_id);
1951
1952 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1953 debug( 'Attribute not created for attribute value id'||
1954 l_attribute_value_rec.attribute_value_id);
1955
1956 RAISE FND_API.G_EXC_ERROR ;
1957
1958 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1959 debug( 'Attribute not created for attribute value id'||
1960 l_attribute_value_rec.attribute_value_id);
1961
1962 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1963
1964 END IF;
1965
1966 END IF;
1967
1968 END LOOP;
1969
1970
1971 -- End of API body.
1972
1973 -- Standard check of p_commit.
1974
1975 IF FND_API.to_Boolean (p_commit) THEN
1976 COMMIT WORK;
1977 END IF;
1978
1979 -- Standard call to get message count and if count is 1, get message info.
1980
1981 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1982 p_data => p_msg_data);
1983
1984 EXCEPTION
1985
1986 WHEN FND_API.G_EXC_ERROR THEN
1987
1988 ROLLBACK TO Copy_Attributes_Pvt;
1989
1990 p_return_status := FND_API.G_RET_STS_ERROR;
1991
1992 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1993 p_data => p_msg_data);
1994
1995
1996 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1997
1998 ROLLBACK TO Copy_Attributes_Pvt;
1999
2000 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2001
2002 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
2003 p_data => p_msg_data);
2004
2005 WHEN OTHERS THEN
2006
2007 ROLLBACK TO Copy_Attributes_Pvt;
2008
2009 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2010
2011 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2012
2013 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
2014 l_api_name);
2015 END IF;
2016
2017 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
2018 p_data => p_msg_data);
2019 END Copy_Attributes;
2020
2021 /*---------------------------------------------------------------------------*/
2022
2023 /*For Bug No : 2397852 Start*/
2024
2025 PROCEDURE Check_References
2026 (
2027 p_api_version IN NUMBER,
2028 p_init_msg_list IN VARCHAR2,
2029 p_commit IN VARCHAR2,
2030 p_validation_level IN NUMBER,
2031 p_return_status OUT NOCOPY VARCHAR2,
2032 p_msg_count OUT NOCOPY NUMBER,
2033 p_msg_data OUT NOCOPY VARCHAR2,
2034 --
2035 p_entity_set_id IN NUMBER,
2036 p_return_value OUT NOCOPY VARCHAR2
2037 )
2038 IS
2039 --
2040 l_api_name CONSTANT VARCHAR2(30) := 'Check_References';
2041 l_api_version CONSTANT NUMBER := 1.0;
2042 l_return_value VARCHAR2(5) := 'FALSE';
2043 --
2044 CURSOR c_check_references_ws IS
2045 SELECT 1
2046 FROM DUAL
2047 WHERE EXISTS(
2048 SELECT 1
2049 FROM PSB_WORKSHEETS
2050 WHERE ((parameter_set_id = p_entity_set_id)
2051 OR
2052 (constraint_set_id = p_entity_set_id)
2053 OR
2054 (allocrule_set_id = p_entity_set_id))
2055 );
2056
2057 CURSOR c_check_references_br IS
2058 SELECT 1
2059 FROM DUAL
2060 WHERE EXISTS(
2061 SELECT 1
2062 FROM PSB_BUDGET_REVISIONS
2063 WHERE ((parameter_set_id = p_entity_set_id)
2064 OR
2065 (constraint_set_id = p_entity_set_id))
2066 );
2067 BEGIN
2068
2069 SAVEPOINT Check_References_Pvt ;
2070 --
2071 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2072 p_api_version,
2073 l_api_name,
2074 G_PKG_NAME )
2075 THEN
2076 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2077 END IF;
2078 --
2079
2080 IF ( FND_API.To_Boolean(p_init_msg_list)) THEN
2081 FND_MSG_PUB.initialize ;
2082 END IF;
2083
2084 FOR c_check_references_ws_rec IN c_check_references_ws LOOP
2085 l_return_value := 'TRUE' ;
2086 END LOOP;
2087
2088 IF l_return_value <> 'TRUE' THEN
2089
2090 FOR c_check_references_br_rec IN c_check_references_br LOOP
2091 l_return_value := 'TRUE' ;
2092 END LOOP;
2093
2094 END IF;
2095
2096 IF ( FND_API.To_Boolean(p_commit)) THEN
2097 COMMIT WORK;
2098 END IF;
2099 --
2100 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
2101 p_data => p_msg_data );
2102
2103 p_return_value := l_return_value;
2104 p_return_status := FND_API.G_RET_STS_SUCCESS;
2105
2106 EXCEPTION
2107 --
2108 WHEN FND_API.G_EXC_ERROR THEN
2109 --
2110 ROLLBACK TO Check_References_Pvt ;
2111 p_return_status := FND_API.G_RET_STS_ERROR;
2112 p_return_value := l_return_value;
2113 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
2114 p_data => p_msg_data );
2115 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2116 --
2117 ROLLBACK TO Check_References_Pvt ;
2118 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2119 p_return_value := l_return_value;
2120 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
2121 p_data => p_msg_data );
2122 WHEN OTHERS THEN
2123 --
2124 ROLLBACK TO Check_References_Pvt ;
2125 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2126 p_return_value := l_return_value;
2127 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2128 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
2129 l_api_name);
2130 END IF;
2131 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
2132 p_data => p_msg_data );
2133 --
2134 END Check_References;
2135
2136 /*For Bug No : 2397852 End*/
2137
2138
2139 /*===========================================================================+
2140 | PROCEDURE debug (Private) |
2141 +===========================================================================*/
2142 --
2143 -- Private procedure to print debug info
2144
2145 PROCEDURE debug
2146 (
2147 p_message IN VARCHAR2
2148 )
2149 IS
2150 --
2151 BEGIN
2152
2153 IF g_debug_flag = 'Y' THEN
2154 null;
2155 -- dbms_output.put_line(p_message) ;
2156 END IF;
2157
2158 END debug ;
2159
2160
2161 end PSB_ENTITY_SET_PVT;