1 PACKAGE BODY PSB_Flex_Mapping_PVT AS
2 /* $Header: PSBVFLXB.pls 120.2 2005/07/13 11:26:01 shtripat ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_Flex_Mapping_PVT';
5
6 g_chr10 CONSTANT VARCHAR2(1) := FND_GLOBAL.Newline;
7
8
9
10 /*=======================================================================+
11 | PROCEDURE Insert_Row |
12 +=======================================================================*/
13
14 PROCEDURE Insert_Row
15 (
16 p_api_version IN NUMBER,
17 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
18 p_commit IN VARCHAR2 := FND_API.G_FALSE,
19 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
20 p_return_status OUT NOCOPY VARCHAR2,
21 p_msg_count OUT NOCOPY NUMBER,
22 p_msg_data OUT NOCOPY VARCHAR2,
23 p_Row_Id IN OUT NOCOPY VARCHAR2,
24 --
25 p_Flex_Mapping_Set_ID IN NUMBER,
26 p_Flex_Mapping_Value_ID IN NUMBER,
27 p_Budget_Year_Type_ID IN NUMBER,
28 p_Application_Column_Name IN VARCHAR2,
29 p_Flex_Value_Set_ID IN NUMBER,
30 p_Flex_Value_ID IN NUMBER,
31 p_From_Flex_Value_ID IN NUMBER,
32
33 p_mode in varchar2
34
35 )
36 IS
37
38 CURSOR C IS
39 SELECT rowid
40 FROM psb_flex_mapping_set_values
41 WHERE flex_mapping_value_id = p_flex_mapping_value_id ;
42
43 --
44 P_LAST_UPDATE_DATE DATE;
45 P_LAST_UPDATED_BY NUMBER;
46 P_LAST_UPDATE_LOGIN NUMBER;
47
48 -- variables --
49 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row';
50 l_api_version CONSTANT NUMBER := 1.0;
51 l_return_status VARCHAR2(1);
52 --
53 BEGIN
54 --
55 SAVEPOINT Insert_Row_Pvt ;
56 --
57 IF NOT FND_API.Compatible_API_Call ( l_api_version,
58 p_api_version,
59 l_api_name,
60 G_PKG_NAME )
61 THEN
62 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
63 END IF;
64 --
65
66 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
67 FND_MSG_PUB.initialize ;
68 END IF;
69 --
70 p_return_status := FND_API.G_RET_STS_SUCCESS ;
71 --
72 P_LAST_UPDATE_DATE := SYSDATE;
73 if(P_MODE = 'I') then
74 P_LAST_UPDATED_BY := 1;
75 P_LAST_UPDATE_LOGIN := 0;
76 elsif (P_MODE = 'R') then
77 P_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
78 if P_LAST_UPDATED_BY is NULL then
79 P_LAST_UPDATED_BY := -1;
80 end if;
81 P_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
82 if P_LAST_UPDATE_LOGIN is NULL then
83 P_LAST_UPDATE_LOGIN := -1;
84 end if;
85 else
86 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
87 FND_MSG_PUB.Add ;
88 raise FND_API.G_EXC_ERROR;
89 end if;
90 --
91
92 INSERT INTO psb_flex_mapping_set_values
93 ( flex_mapping_set_id,
94 flex_mapping_value_id,
95 budget_year_type_id,
96 application_column_name,
97 flex_value_set_id,
98 flex_value_id ,
99 from_flex_value_id ,
100 creation_date,
101 created_by,
102 last_update_date,
103 last_updated_by,
104 last_update_login
105 )
106 VALUES
107 ( p_flex_mapping_set_id,
108 p_flex_mapping_value_id,
109 p_budget_year_type_id,
110 p_application_column_name,
111 p_flex_value_set_id,
112 p_flex_value_id ,
113 p_from_flex_value_id ,
114 p_last_update_date,
115 p_last_updated_by,
116 p_last_update_date,
117 p_last_updated_by,
118 p_last_update_login
119
120
121 );
122 OPEN C;
123 FETCH C INTO p_Row_Id;
124 IF (C%NOTFOUND) THEN
125 CLOSE C;
126 RAISE FND_API.G_EXC_ERROR ;
127 END IF;
128 CLOSE C;
129 --
130
131 --
132 IF FND_API.To_Boolean ( p_commit ) THEN
133 COMMIT WORK;
134 END iF;
135 --
136 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
137 p_data => p_msg_data );
138 --
139 EXCEPTION
140 --
141 WHEN FND_API.G_EXC_ERROR THEN
142 --
143 ROLLBACK TO Insert_Row_Pvt ;
144 p_return_status := FND_API.G_RET_STS_ERROR;
145 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
146 p_data => p_msg_data );
147 --
148 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
149 --
150 ROLLBACK TO Insert_Row_Pvt ;
151 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
152 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
153 p_data => p_msg_data );
154 --
155 WHEN OTHERS THEN
156 --
157 ROLLBACK TO Insert_Row_Pvt ;
158 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
159 --
160 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
161 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
162 l_api_name);
163 END if;
164 --
165 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
166 p_data => p_msg_data );
167 --
168 END Insert_Row;
169 /*-------------------------------------------------------------------------*/
170
171
172
173 /*==========================================================================+
174 | PROCEDURE Lock_Row |
175 +==========================================================================*/
176
177 PROCEDURE Lock_Row
178 (
179 p_api_version IN NUMBER,
180 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
181 p_commit IN VARCHAR2 := FND_API.G_FALSE,
182 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
183 p_return_status OUT NOCOPY VARCHAR2,
184 p_msg_count OUT NOCOPY NUMBER,
185 p_msg_data OUT NOCOPY VARCHAR2,
186 --
187 p_Flex_Mapping_Set_ID IN NUMBER,
188 p_Flex_Mapping_Value_ID IN NUMBER,
189 p_Budget_Year_Type_ID IN NUMBER,
190 p_Application_Column_Name IN VARCHAR2,
191 p_Flex_Value_Set_ID IN NUMBER,
192 p_Flex_Value_ID IN NUMBER,
193 p_From_Flex_Value_ID IN NUMBER,
194 --
195 p_row_locked OUT NOCOPY VARCHAR2
196 )
197 IS
198 --
199 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Row';
200 l_api_version CONSTANT NUMBER := 1.0;
201 l_return_status VARCHAR2(1);
202
203 --
204 Counter NUMBER;
205 CURSOR C IS
206 SELECT Flex_Mapping_Set_ID,
207 Flex_Mapping_Value_ID,
208 Budget_Year_Type_ID ,
209 Application_Column_Name,
210 Flex_Value_Set_ID,
211 Flex_Value_ID,
212 From_Flex_Value_ID
213 FROM psb_flex_mapping_set_values
214 WHERE Flex_Mapping_Value_ID = p_Flex_Mapping_Value_ID
215 FOR UPDATE of Flex_Mapping_Value_ID NOWAIT;
216 Recinfo C%ROWTYPE;
217
218 BEGIN
219 --
220 SAVEPOINT Lock_Row_Pvt ;
221 --
222 IF NOT FND_API.Compatible_API_Call ( l_api_version,
223 p_api_version,
224 l_api_name,
225 G_PKG_NAME )
226 THEN
227 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
228 END IF;
229 --
230
231 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
232 FND_MSG_PUB.initialize ;
233 END IF;
234 --
235 p_return_status := FND_API.G_RET_STS_SUCCESS ;
236 p_row_locked := FND_API.G_TRUE ;
237 --
238 OPEN C;
239 --
240 FETCH C INTO Recinfo;
241 IF (C%NOTFOUND) then
242 CLOSE C;
243 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
244 FND_MSG_PUB.Add;
245 CLOSE c;
246 RAISE FND_API.G_EXC_ERROR ;
247 END IF;
248 CLOSE C;
249 IF
250 (
251 ( Recinfo.Flex_Mapping_Set_ID = p_Flex_Mapping_Set_ID )
252 AND ( Recinfo.Flex_Mapping_Value_ID = p_Flex_Mapping_Value_ID )
253 AND ( Recinfo.budget_year_type_id = p_budget_year_type_id)
254 AND ( Recinfo.application_column_name = p_application_column_name)
255 --
256 AND ((recinfo.flex_value_set_id = P_flex_value_set_id)
257 OR ((recinfo.flex_value_set_id is null)
258 AND (P_flex_value_set_id is null)))
259 AND ((recinfo.flex_value_id = P_flex_value_id)
260 OR ((recinfo.flex_value_id is null)
261 AND (P_flex_value_id is null)))
262 AND ((recinfo.from_flex_value_id = P_from_flex_value_id)
263 OR ((recinfo.from_flex_value_id is null)
264 AND (P_from_flex_value_id is null)))
265 )
266 THEN
267 NULL ;
268 ELSE
269 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED') ;
270 FND_MSG_PUB.Add ;
271 RAISE FND_API.G_EXC_ERROR ;
272 END IF;
273
274 --
275 /*--
276 IF FND_API.To_Boolean ( p_commit ) THEN
277 COMMIT WORK;
278 END iF;
279 --
280 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
281 p_data => p_msg_data );
282 */
283 --
284 EXCEPTION
285 --
286 WHEN App_Exception.Record_Lock_Exception THEN
287 --
288 ROLLBACK TO Lock_Row_Pvt ;
289 p_row_locked := FND_API.G_FALSE ;
290 p_return_status := FND_API.G_RET_STS_ERROR ;
291 --
292 WHEN FND_API.G_EXC_ERROR THEN
293 --
294 ROLLBACK TO Lock_Row_Pvt ;
295 p_return_status := FND_API.G_RET_STS_ERROR;
296 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
297 p_data => p_msg_data );
298 --
299 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
300 --
301 ROLLBACK TO Lock_Row_Pvt ;
302 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
303 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
304 p_data => p_msg_data );
305 --
306 WHEN OTHERS THEN
307 --
308 ROLLBACK TO Lock_Row_Pvt ;
309 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
310 --
311 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
312 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
313 l_api_name);
314 END if;
315 --
316 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
317 p_data => p_msg_data );
318 --
319 END Lock_Row;
320 /* ----------------------------------------------------------------------- */
321
322
323
324
325 /*==========================================================================+
326 | PROCEDURE Update_Row |
327 +==========================================================================*/
328
329 PROCEDURE Update_Row
330 (
331 p_api_version IN NUMBER,
332 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
333 p_commit IN VARCHAR2 := FND_API.G_FALSE,
334 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
335 p_return_status OUT NOCOPY VARCHAR2,
336 p_msg_count OUT NOCOPY NUMBER,
337 p_msg_data OUT NOCOPY VARCHAR2,
338 --
339 p_Flex_Mapping_Set_ID IN NUMBER,
340 p_Flex_Mapping_Value_ID IN NUMBER,
341 p_Budget_Year_Type_ID IN NUMBER,
342 p_Application_Column_Name IN VARCHAR2,
343 p_Flex_Value_Set_ID IN NUMBER,
344 p_Flex_Value_ID IN NUMBER,
345 p_From_Flex_Value_ID IN NUMBER,
346 --
347 p_mode in varchar2
348
349 )
350 IS
351 P_LAST_UPDATE_DATE DATE;
352 P_LAST_UPDATED_BY NUMBER;
353 P_LAST_UPDATE_LOGIN NUMBER;
354 --
355 l_api_name CONSTANT VARCHAR2(30) := 'Update_Row';
356 l_api_version CONSTANT NUMBER := 1.0;
357 l_return_status VARCHAR2(1);
358 --
359 BEGIN
360 --
361 SAVEPOINT Update_Row_Pvt ;
362 --
363 IF NOT FND_API.Compatible_API_Call ( l_api_version,
364 p_api_version,
365 l_api_name,
366 G_PKG_NAME )
367 THEN
368 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
369 END IF;
370 --
371
372 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
373 FND_MSG_PUB.initialize ;
374 END IF;
375 --
376 p_return_status := FND_API.G_RET_STS_SUCCESS ;
377 --
378
379 P_LAST_UPDATE_DATE := SYSDATE;
380 if(P_MODE = 'I') then
381 P_LAST_UPDATED_BY := 1;
382 P_LAST_UPDATE_LOGIN := 0;
383 elsif (P_MODE = 'R') then
384 P_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
385 if P_LAST_UPDATED_BY is NULL then
386 P_LAST_UPDATED_BY := -1;
387 end if;
388 P_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
389 if P_LAST_UPDATE_LOGIN is NULL then
390 P_LAST_UPDATE_LOGIN := -1;
391 end if;
392 else
393 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
394 FND_MSG_PUB.Add ;
395 raise FND_API.G_EXC_ERROR ;
396 end if;
397 --
398 UPDATE psb_flex_mapping_set_values
399 SET
400 Flex_Mapping_Set_ID = p_Flex_Mapping_Set_ID,
401 Flex_Mapping_Value_ID = p_Flex_Mapping_Value_ID ,
402 Budget_Year_Type_ID = p_Budget_Year_Type_ID ,
403 Application_Column_Name = p_Application_Column_Name ,
404 Flex_Value_Set_ID = p_Flex_Value_Set_ID ,
405 Flex_Value_ID = p_Flex_Value_ID ,
406 From_Flex_Value_ID = p_From_Flex_Value_ID ,
407 last_update_date = p_last_update_date,
408 last_updated_by = p_last_updated_by,
409 last_update_login = p_last_update_login
410 WHERE Flex_Mapping_Value_ID = p_Flex_Mapping_Value_ID;
411
412 IF (SQL%NOTFOUND) THEN
413 RAISE FND_API.G_EXC_ERROR ;
414 END IF;
415
416 --
417 --
418 -- Standard check of p_commit.
419
420 IF FND_API.To_Boolean ( p_commit ) THEN
421 COMMIT WORK;
422 END iF;
423 --
424 -- Standard call to get message count and if count is 1, get message info.
425 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
426 p_data => p_msg_data );
427 --
428 EXCEPTION
429
430 WHEN FND_API.G_EXC_ERROR THEN
431 --
432 ROLLBACK TO Update_Row_Pvt ;
433 p_return_status := FND_API.G_RET_STS_ERROR;
434 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
435 p_data => p_msg_data );
436 --
437 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
438 --
439 ROLLBACK TO Update_Row_Pvt ;
440 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
441 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
442 p_data => p_msg_data );
443 --
444 WHEN OTHERS THEN
445 --
446 ROLLBACK TO Update_Row_Pvt ;
447 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
448 --
449 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
450 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
451 l_api_name);
452 END if;
453 --
454 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
455 p_data => p_msg_data );
456 --
457 END Update_Row;
461
458 /* ----------------------------------------------------------------------- */
459
460
462
463 /*==========================================================================+
464 | PROCEDURE Delete_Row |
465 +==========================================================================*/
466
467 PROCEDURE Delete_Row
468 (
469 p_api_version IN NUMBER,
470 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
471 p_commit IN VARCHAR2 := FND_API.G_FALSE,
472 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
473 p_return_status OUT NOCOPY VARCHAR2,
474 p_msg_count OUT NOCOPY NUMBER,
475 p_msg_data OUT NOCOPY VARCHAR2,
476 --
477 p_Flex_Mapping_Value_ID IN NUMBER
478 )
479 IS
480 --
481 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Row';
482 l_api_version CONSTANT NUMBER := 1.0;
483 --
484 BEGIN
485 --
486 SAVEPOINT Delete_Row_Pvt ;
487 --
488 IF NOT FND_API.Compatible_API_Call ( l_api_version,
489 p_api_version,
490 l_api_name,
491 G_PKG_NAME )
492 THEN
493 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
494 END IF;
495 --
496
497 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
498 FND_MSG_PUB.initialize ;
499 END IF;
500 --
501 p_return_status := FND_API.G_RET_STS_SUCCESS ;
502 --
503
504 --
505 -- Deleting the record in psb_flex_mapping_set_values.
506 --
507 DELETE psb_flex_mapping_set_values
508 WHERE Flex_Mapping_Value_ID = p_Flex_Mapping_Value_ID;
509
510 IF (SQL%NOTFOUND) THEN
511 RAISE NO_DATA_FOUND ;
512 END IF;
513
514 --
515 IF FND_API.To_Boolean ( p_commit ) THEN
516 COMMIT WORK;
517 END iF;
518 --
519 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
520 p_data => p_msg_data );
521
522 EXCEPTION
523 --
524 WHEN FND_API.G_EXC_ERROR THEN
525 --
526 ROLLBACK TO Delete_Row_Pvt ;
527 p_return_status := FND_API.G_RET_STS_ERROR;
528 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
529 p_data => p_msg_data );
530 --
531 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
532 --
533 ROLLBACK TO Delete_Row_Pvt ;
534 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
535 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
536 p_data => p_msg_data );
537 --
538 WHEN OTHERS THEN
539 --
540 ROLLBACK TO Delete_Row_Pvt ;
541 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
542 --
543 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
544 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
545 l_api_name);
546 END if;
547 --
548 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
549 p_data => p_msg_data );
550 --
551 END Delete_Row;
552 /* ----------------------------------------------------------------------- */
553
554
555
556
557
558 /* ----------------------------------------------------------------------- */
559
560
561 PROCEDURE Sets_Insert_Row
562 (
563 p_api_version IN NUMBER,
564 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
565 p_commit IN VARCHAR2 := FND_API.G_FALSE,
566 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
567 p_return_status OUT NOCOPY VARCHAR2,
568 p_msg_count OUT NOCOPY NUMBER,
569 p_msg_data OUT NOCOPY VARCHAR2,
570 --
571 p_Flex_Mapping_Set_ID IN NUMBER,
572 p_Name IN VARCHAR2,
573 p_Description IN VARCHAR2,
574 p_set_of_books_id IN NUMBER,
575 --
576 p_mode in varchar2
577
578 )
579 IS
580
581 CURSOR C IS
582 SELECT rowid
583 FROM psb_flex_mapping_sets
584 WHERE Flex_Mapping_Set_ID = p_Flex_Mapping_Set_ID ;
585
586 --
587 P_LAST_UPDATE_DATE DATE;
588 P_LAST_UPDATED_BY NUMBER;
589 P_LAST_UPDATE_LOGIN NUMBER;
590 -- variables --
591 l_api_name CONSTANT VARCHAR2(30) := 'Sets_Insert_Row';
592 l_api_version CONSTANT NUMBER := 1.0;
593 l_return_status VARCHAR2(1);
594 l_row_id VARCHAR(18);
595 --
596 BEGIN
597 --
598 SAVEPOINT Sets_Insert_Row_Pvt ;
599 --
600 IF NOT FND_API.Compatible_API_Call ( l_api_version,
601 p_api_version,
602 l_api_name,
603 G_PKG_NAME )
604 THEN
605 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
606 END IF;
607 --
608
609 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
610 FND_MSG_PUB.initialize ;
611 END IF;
612 --
613 p_return_status := FND_API.G_RET_STS_SUCCESS ;
614 --
615 P_LAST_UPDATE_DATE := SYSDATE;
616 if(P_MODE = 'I') then
617 P_LAST_UPDATED_BY := 1;
621 if P_LAST_UPDATED_BY is NULL then
618 P_LAST_UPDATE_LOGIN := 0;
619 elsif (P_MODE = 'R') then
620 P_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
622 P_LAST_UPDATED_BY := -1;
623 end if;
624 P_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
625 if P_LAST_UPDATE_LOGIN is NULL then
626 P_LAST_UPDATE_LOGIN := -1;
627 end if;
628 else
629 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
630 FND_MSG_PUB.Add ;
631 raise FND_API.G_EXC_ERROR;
632 end if;
633 --
634
635 INSERT INTO psb_flex_mapping_sets
636 ( Flex_Mapping_Set_ID ,
637 Name,
638 Description,
639 set_of_books_id,
640 creation_date,
641 created_by,
642 last_update_date,
643 last_updated_by,
644 last_update_login
645 )
646 VALUES
647 ( p_Flex_Mapping_Set_ID,
648 p_Name,
649 p_Description,
650 p_Set_of_Books_ID,
651 p_last_update_date,
652 p_last_updated_by,
653 p_last_update_date,
654 p_last_updated_by,
655 p_last_update_login
656
657 );
658 OPEN C;
659 FETCH C INTO l_Row_Id;
660 IF (C%NOTFOUND) THEN
661 CLOSE C;
662 RAISE FND_API.G_EXC_ERROR ;
663 END IF;
664 CLOSE C;
665 --
666
667 --
668 IF FND_API.To_Boolean ( p_commit ) THEN
669 COMMIT WORK;
670 END iF;
671 --
672 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
673 p_data => p_msg_data );
674 --
675 EXCEPTION
676 --
677 WHEN FND_API.G_EXC_ERROR THEN
678 --
679 ROLLBACK TO Sets_Insert_Row_Pvt ;
680 p_return_status := FND_API.G_RET_STS_ERROR;
681 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
682 p_data => p_msg_data );
683 --
684 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
685 --
686 ROLLBACK TO Sets_Insert_Row_Pvt ;
687 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
688 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
689 p_data => p_msg_data );
690 --
691 WHEN OTHERS THEN
692 --
693 ROLLBACK TO Sets_Insert_Row_Pvt ;
694 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
695 --
696 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
697 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
698 l_api_name);
699 END if;
700 --
701 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
702 p_data => p_msg_data );
703 --
704 END Sets_Insert_Row;
705 /*==========================================================================+
706 | PROCEDURE Delete_Row |
707 +==========================================================================*/
708
709 PROCEDURE Sets_Delete_Row
710 (
711 p_api_version IN NUMBER,
712 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
713 p_commit IN VARCHAR2 := FND_API.G_FALSE,
714 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
715 p_return_status OUT NOCOPY VARCHAR2,
716 p_msg_count OUT NOCOPY NUMBER,
717 p_msg_data OUT NOCOPY VARCHAR2,
718 --
719 p_Flex_Mapping_Set_ID IN NUMBER
720 )
721 IS
722 --
723 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Row';
724 l_api_version CONSTANT NUMBER := 1.0;
725 --
726 l_ws_count NUMBER;
727 BEGIN
728 --
729 SAVEPOINT Sets_Delete_Row_Pvt ;
730 --
731 IF NOT FND_API.Compatible_API_Call ( l_api_version,
732 p_api_version,
733 l_api_name,
734 G_PKG_NAME )
735 THEN
736 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
737 END IF;
738 --
739
740 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
741 FND_MSG_PUB.initialize ;
742 END IF;
743 --
744 p_return_status := FND_API.G_RET_STS_SUCCESS ;
745 --
746 DELETE psb_flex_mapping_set_values
747 WHERE flex_mapping_set_id = p_flex_mapping_set_id ;
748
749 DELETE psb_flex_mapping_sets
750 WHERE flex_mapping_set_id = p_flex_mapping_set_id;
751
752 IF (SQL%NOTFOUND) THEN
753 RAISE NO_DATA_FOUND ;
754 END IF;
755
756 --
757 IF FND_API.To_Boolean ( p_commit ) THEN
758 COMMIT WORK;
759 END iF;
760 --
761 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
762 p_data => p_msg_data );
763
764 EXCEPTION
765 --
766 WHEN FND_API.G_EXC_ERROR THEN
767 --
768 ROLLBACK TO Sets_Delete_Row_Pvt ;
769 p_return_status := FND_API.G_RET_STS_ERROR;
770 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
771 p_data => p_msg_data );
772 --
773 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
774 --
775 ROLLBACK TO Sets_Delete_Row_Pvt ;
776 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
777 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
778 p_data => p_msg_data );
782 ROLLBACK TO Sets_Delete_Row_Pvt ;
779 --
780 WHEN OTHERS THEN
781 --
783 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
784 --
785 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
786 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
787 l_api_name);
788 END if;
789 --
790 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
791 p_data => p_msg_data );
792 --
793 END Sets_Delete_Row;
794
795 /*==========================================================================+
796 | PROCEDURE Lock_Row |
797 +==========================================================================*/
798
799 PROCEDURE Sets_Lock_Row
800 (
801 p_api_version IN NUMBER,
802 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
803 p_commit IN VARCHAR2 := FND_API.G_FALSE,
804 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
805 p_return_status OUT NOCOPY VARCHAR2,
806 p_msg_count OUT NOCOPY NUMBER,
807 p_msg_data OUT NOCOPY VARCHAR2,
808 --
809 p_Flex_Mapping_Set_ID IN NUMBER,
810 p_Name IN VARCHAR2,
811 p_Description IN VARCHAR2,
812 p_set_of_books_id IN NUMBER,
813 --
814 p_row_locked OUT NOCOPY VARCHAR2
815 )
816 IS
817 --
818 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Row';
819 l_api_version CONSTANT NUMBER := 1.0;
820 l_return_status VARCHAR2(1);
821
822 --
823 Counter NUMBER;
824 CURSOR C IS
825 SELECT Flex_Mapping_Set_ID,
826 Name,
827 description ,
828 set_of_books_id
829 FROM psb_flex_mapping_sets
830 WHERE Flex_Mapping_Set_Id = p_Flex_Mapping_Set_Id
831 FOR UPDATE of Flex_Mapping_Set_Id NOWAIT;
832 Recinfo C%ROWTYPE;
833
834 BEGIN
835 --
836 SAVEPOINT Lock_Row_Pvt ;
837 --
838 IF NOT FND_API.Compatible_API_Call ( l_api_version,
839 p_api_version,
840 l_api_name,
841 G_PKG_NAME )
842 THEN
843 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
844 END IF;
845 --
846
847 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
848 FND_MSG_PUB.initialize ;
849 END IF;
850 --
851 p_return_status := FND_API.G_RET_STS_SUCCESS ;
852 p_row_locked := FND_API.G_TRUE ;
853 --
854 OPEN C;
855 --
856 FETCH C INTO Recinfo;
857 IF (C%NOTFOUND) then
858 CLOSE C;
859 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
860 FND_MSG_PUB.Add;
861 CLOSE c;
862 RAISE FND_API.G_EXC_ERROR ;
863 END IF;
864 CLOSE C;
865 IF
866 (
867 ( Recinfo.Flex_Mapping_Set_ID = p_Flex_Mapping_Set_ID )
868 AND ( Recinfo.Name = p_Name )
869 AND ( Recinfo.set_of_books_id = p_set_of_books_id)
870 --
871 AND ((recinfo.description = P_description)
872 OR ((recinfo.description is null)
873 AND (P_description is null)))
874 )
875 THEN
876 NULL ;
877 ELSE
878 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED') ;
879 FND_MSG_PUB.Add ;
880 RAISE FND_API.G_EXC_ERROR ;
881 END IF;
882
883 --
884
885 EXCEPTION
886 --
887 WHEN App_Exception.Record_Lock_Exception THEN
888 --
889 ROLLBACK TO Lock_Row_Pvt ;
890 p_row_locked := FND_API.G_FALSE ;
891 p_return_status := FND_API.G_RET_STS_ERROR ;
892 --
893 WHEN FND_API.G_EXC_ERROR THEN
894 --
895 ROLLBACK TO Lock_Row_Pvt ;
896 p_return_status := FND_API.G_RET_STS_ERROR;
897 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
898 p_data => p_msg_data );
899 --
900 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
901 --
902 ROLLBACK TO Lock_Row_Pvt ;
903 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
904 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
905 p_data => p_msg_data );
906 --
907 WHEN OTHERS THEN
908 --
909 ROLLBACK TO Lock_Row_Pvt ;
910 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
911 --
912 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
913 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
914 l_api_name);
915 END if;
916 --
917 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
918 p_data => p_msg_data );
919 --
920 END Sets_Lock_Row;
921 /* ----------------------------------------------------------------------- */
922
923
924
925
926 /*==========================================================================+
927 | PROCEDURE Update_Row |
928 +==========================================================================*/
929
930 PROCEDURE Sets_Update_Row
931 (
932 p_api_version IN NUMBER,
936 p_return_status OUT NOCOPY VARCHAR2,
933 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
934 p_commit IN VARCHAR2 := FND_API.G_FALSE,
935 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
937 p_msg_count OUT NOCOPY NUMBER,
938 p_msg_data OUT NOCOPY VARCHAR2,
939 --
940 p_Flex_Mapping_Set_ID IN NUMBER,
941 p_Name IN VARCHAR2,
942 p_Description IN VARCHAR2,
943 p_set_of_books_id IN NUMBER,
944
945 --
946 p_mode in varchar2
947
948 )
949 IS
950 P_LAST_UPDATE_DATE DATE;
951 P_LAST_UPDATED_BY NUMBER;
952 P_LAST_UPDATE_LOGIN NUMBER;
953 --
954 l_api_name CONSTANT VARCHAR2(30) := 'Update_Row';
955 l_api_version CONSTANT NUMBER := 1.0;
956 l_return_status VARCHAR2(1);
957 --
958 BEGIN
959 --
960 SAVEPOINT Update_Row_Pvt ;
961 --
962 IF NOT FND_API.Compatible_API_Call ( l_api_version,
963 p_api_version,
964 l_api_name,
965 G_PKG_NAME )
966 THEN
967 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
968 END IF;
969 --
970
971 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
972 FND_MSG_PUB.initialize ;
973 END IF;
974 --
975 p_return_status := FND_API.G_RET_STS_SUCCESS ;
976 --
977
978 P_LAST_UPDATE_DATE := SYSDATE;
979 if(P_MODE = 'I') then
980 P_LAST_UPDATED_BY := 1;
981 P_LAST_UPDATE_LOGIN := 0;
982 elsif (P_MODE = 'R') then
983 P_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
984 if P_LAST_UPDATED_BY is NULL then
985 P_LAST_UPDATED_BY := -1;
986 end if;
987 P_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
988 if P_LAST_UPDATE_LOGIN is NULL then
989 P_LAST_UPDATE_LOGIN := -1;
990 end if;
991 else
992 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
993 FND_MSG_PUB.Add ;
994 raise FND_API.G_EXC_ERROR ;
995 end if;
996 --
997 UPDATE psb_flex_mapping_sets
998 SET
999 Flex_Mapping_Set_ID = p_Flex_Mapping_Set_ID,
1000 Name = p_Name ,
1001 Description = p_Description ,
1002 Set_of_Books_ID = p_Set_of_Books_ID ,
1003 last_update_date = p_last_update_date,
1004 last_updated_by = p_last_updated_by,
1005 last_update_login = p_last_update_login
1006 WHERE Flex_Mapping_Set_ID = p_Flex_Mapping_Set_ID;
1007
1008 IF (SQL%NOTFOUND) THEN
1009 RAISE FND_API.G_EXC_ERROR ;
1010 END IF;
1011
1012 --
1013 --
1014 -- Standard check of p_commit.
1015
1016 IF FND_API.To_Boolean ( p_commit ) THEN
1017 COMMIT WORK;
1018 END iF;
1019 --
1020 -- Standard call to get message count and if count is 1, get message info.
1021 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1022 p_data => p_msg_data );
1023 --
1024 EXCEPTION
1025
1026 WHEN FND_API.G_EXC_ERROR THEN
1027 --
1028 ROLLBACK TO Update_Row_Pvt ;
1029 p_return_status := FND_API.G_RET_STS_ERROR;
1030 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1031 p_data => p_msg_data );
1032 --
1033 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1034 --
1035 ROLLBACK TO Update_Row_Pvt ;
1036 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1037 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1038 p_data => p_msg_data );
1039 --
1040 WHEN OTHERS THEN
1041 --
1042 ROLLBACK TO Update_Row_Pvt ;
1043 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1044 --
1045 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1046 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
1047 l_api_name);
1048 END if;
1049 --
1050 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1051 p_data => p_msg_data );
1052 --
1053 END Sets_Update_Row;
1054
1055 -- +++++++++++++++++++++++++++++++++
1056 -- This function maps segment values from psb_flex_mapping_set_values to the segment values
1057 -- of the input ccid. If no mapping record found, input ccid segment is unchanged
1058 -- p_mapping_mode of Worksheet,Report,GL_Posting
1059 -- return mapped ccid value if a valid ccid or 0 if invalid ccid
1060 -- +++++++++++++++++++++++++++++++++
1061
1062 FUNCTION Get_Mapped_CCID
1063 (
1064 p_api_version IN NUMBER,
1065 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1066 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1067 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1068 --
1069 p_CCID IN NUMBER,
1070 p_Budget_Year_Type_ID IN NUMBER,
1071 p_Flexfield_Mapping_Set_ID IN NUMBER,
1072 p_Mapping_Mode IN VARCHAR2 := 'WORKSHEET'
1073
1074
1075 ) RETURN NUMBER IS
1076 l_ccid NUMBER := 0;
1077 l_flex_code NUMBER := 0;
1078 l_seg_val FND_FLEX_EXT.SegmentArray;
1082 l_cy_id NUMBER ;
1079 l_return_status VARCHAR2(1);
1080 l_cy_budget_year_type_id NUMBER ;
1081 l_py_budget_year_type_id NUMBER ;
1083 l_index NUMBER;
1084 l_segment_num NUMBER;
1085 l_from_value VARCHAR2(150);
1086 l_to_value VARCHAR2(150);
1087 l_py_from_value VARCHAR2(150);
1088 l_py_to_value VARCHAR2(150);
1089 l_seg_value VARCHAR2(150);
1090
1091 CURSOR c_flex IS
1092 SELECT s.chart_of_accounts_id
1093 FROM psb_flex_mapping_sets f,
1094 gl_sets_of_books s
1095 WHERE flex_mapping_set_id = p_flexfield_mapping_set_id AND
1096 f.set_of_books_id = s.set_of_books_id ;
1097
1098
1099 -- cursor for gl_posting mapping_mode
1100 -- get the flex map record with from value if it exists is first record;
1101 -- otherwise, null from value will do ... pass segment num
1102 cursor c_seginfo_subs is
1103 select fval.flex_value to_val ,
1104 fromval.flex_value from_val
1105 from fnd_flex_values_vl fval,
1106 fnd_flex_values_vl fromval,
1107 psb_flex_mapping_set_values map,
1108 fnd_id_flex_segments seg
1109 where flex_mapping_set_id = p_flexfield_mapping_set_id
1110 and budget_year_type_id = p_budget_year_type_id
1111 and map.flex_value_id = fval.flex_value_id(+)
1112 and map.from_flex_value_id = fromval.flex_value_id
1113 and seg.application_id = 101
1114 and seg.id_flex_code = 'GL#'
1115 and seg.id_flex_num = l_flex_code
1116 and seg.enabled_flag = 'Y'
1117 and seg.application_column_name = map.application_column_name
1118 and map.application_column_name = g_seg_name(l_segment_num)
1119 and ( fval.flex_value is null
1120 or fromval.flex_value = l_seg_val(l_segment_num) )
1121 order by fromval.flex_value
1122
1123 ;
1124 -- need to outer join fromval so that null from values will selected also
1125 -- the record has already been selected and to further select the record with
1126 -- null from value, the map record is now the bigger table(null) than fromval
1127 -- read only values for a segment which matches the input segment or is null
1128 -- and is ordered by with null values last. Specific value matched supercedes
1129 -- null from value so it will be read first when first rec is read.
1130
1131 -- cursor for worksheet mapping_mode - uses from value
1132 cursor c_seginfo_ws is
1133 select fval.flex_value from_val , seg.application_column_name
1134 from fnd_flex_values_vl fval,
1135 psb_flex_mapping_set_values map,
1136 fnd_id_flex_segments seg
1137 where flex_mapping_set_id = p_flexfield_mapping_set_id
1138 and budget_year_type_id = l_cy_budget_year_type_id
1139 and map.from_flex_value_id = fval.flex_value_id
1140 and seg.application_id = 101
1141 and seg.id_flex_code = 'GL#'
1142 and seg.id_flex_num = l_flex_code
1143 and seg.enabled_flag = 'Y'
1144 and seg.application_column_name = map.application_column_name ;
1145
1146 cursor c_cy is
1147 select fval.flex_value curr_val
1148 from fnd_flex_values_vl fval,
1149 psb_flex_mapping_set_values map,
1150 fnd_id_flex_segments seg
1151 where flex_mapping_set_id = p_flexfield_mapping_set_id
1152 and budget_year_type_id = l_cy_budget_year_type_id
1153 and map.from_flex_value_id = fval.flex_value_id
1154 and seg.application_id = 101
1155 and seg.id_flex_code = 'GL#'
1156 and seg.id_flex_num = l_flex_code
1157 and seg.enabled_flag = 'Y'
1158 and seg.application_column_name = map.application_column_name
1159 and map.application_column_name = g_seg_name(l_segment_num)
1160 ;
1161
1162 cursor c_py is
1163 select fval.flex_value curr_val
1164 from fnd_flex_values_vl fval,
1165 psb_flex_mapping_set_values map,
1166 fnd_id_flex_segments seg
1167 where flex_mapping_set_id = p_flexfield_mapping_set_id
1168 and budget_year_type_id = l_py_budget_year_type_id
1169 and map.from_flex_value_id = fval.flex_value_id
1170 and seg.application_id = 101
1171 and seg.id_flex_code = 'GL#'
1172 and seg.id_flex_num = l_flex_code
1173 and seg.enabled_flag = 'Y'
1174 and seg.application_column_name = map.application_column_name
1175 and map.application_column_name = g_seg_name(l_segment_num)
1176 ;
1177
1178 cursor c_cy_type is
1179 select budget_year_type_id
1180 from psb_budget_year_types_vl
1181 where year_category_type = 'CY';
1182
1183 cursor c_py_type is
1184 select budget_year_type_id
1185 from psb_budget_year_types_vl y
1186 where year_category_type = 'PY'
1187 and budget_year_type_id = p_budget_year_type_id
1188 ;
1189 BEGIN
1190
1191 -- +++++++++++++++++
1192 -- Setup flex code = coa
1193 -- +++++++++++++++++
1194 OPEN c_flex;
1195 FETCH c_flex INTO l_flex_code;
1196 IF c_flex%NOTFOUND THEN
1197 CLOSE c_flex;
1198 raise NO_DATA_FOUND;
1199 END IF;
1200 CLOSE c_flex;
1201
1202 -- +++++++++++++++++
1203 -- Setup flex info (segments)
1204 -- +++++++++++++++++
1205
1206 Flex_Info (p_flex_code => l_flex_code,
1210 raise FND_API.G_EXC_ERROR;
1207 p_return_status => l_return_status);
1208
1209 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1211 end if;
1212
1213 -- +++++++++++++++++
1214 -- Explode p_ccid into individual segments to l_seg_val array
1215 -- +++++++++++++++++
1216
1217 if not FND_FLEX_EXT.Get_Segments
1218 (application_short_name => 'SQLGL',
1219 key_flex_code => 'GL#',
1220 structure_number => g_flex_code,
1221 combination_id => p_ccid,
1222 n_segments => g_num_segs,
1223 segments => l_seg_val ) then
1224
1225 FND_MSG_PUB.Add;
1226 raise FND_API.G_EXC_ERROR;
1227 end if;
1228
1229
1230 -- +++++++++++++++++
1231 -- get current year budget year type id
1232 -- +++++++++++++++++
1233
1234 for c_cy_type_rec in c_cy_type loop
1235 -- get cy id
1236 l_cy_budget_year_type_id := c_cy_type_rec.budget_year_type_id;
1237 end loop;
1238
1239 for c_py_type_rec in c_py_type loop
1240 -- get py id
1241 l_py_budget_year_type_id := c_py_type_rec.budget_year_type_id;
1242 end loop;
1243
1244
1245 -- +++++++++++++++++
1246 -- for worksheet mapping mode, always map using current year type since cy stores the
1247 -- values to be stored in worksheet
1248 -- +++++++++++++++++
1249
1250 if p_mapping_mode = 'WORKSHEET' then
1251 -- substitute from value of each flex value mapping record to the appropriate segment
1252 -- using segment_name
1253
1254
1255 if p_budget_year_type_id = l_py_budget_year_type_id then
1256 -- map py to cy; no mapping for cy or pp
1257 for l_index in 1..g_num_segs loop
1258 l_segment_num := l_index ;
1259 open c_py;
1260 fetch c_py into l_py_to_value;
1261 if (c_py%NOTFOUND) THEN
1262 close c_py;
1263 else
1264 open c_cy;
1265 fetch c_cy into l_to_value;
1266
1267 if (c_cy%NOTFOUND) THEN
1268 close c_py;
1269 close c_cy;
1270 -- no py or cy so bypass this segment
1271 else
1272 -- cy/py exists so try to map
1273 if l_py_to_value = l_seg_val(l_index) then
1274 l_seg_val(l_index) := l_to_value;
1275 end if;
1276
1277 close c_py;
1278 close c_cy;
1279 end if;
1280 --
1281 end if;
1282
1283
1284 -- process next iteration
1285
1286 end loop;
1287
1288 else
1289 null;
1290 -- p_budget_year_id is not py so for ws, should not map it -> ccid unchanged
1291 end if;
1292
1293 -- ++ PY mapped to CY value if py segment match input segment
1294 -- ++ no mapping for CY and PP; input ccid becomes ws ccid; so for pp,
1295 -- ++ so if pp ccid is not the correct ccid for pp (i.e. still cy), then
1296 -- ++ that will be a separate account in ws
1297
1298 else
1299 --- ++ for GL_POSTING; use the input budget year type id to map the values
1300 --- ++ this is specific from_value substitution in this order
1301 --- ++ 1. from value = input ccid segment
1302 --- ++ 2. from value is null, substitute to value to input ccid segment
1303 --- ++ 3. retain input ccid segment coz no flex mapping record found
1304 --- ++ if input budget year type id is current year, do not do a mapping
1305 --- ++ logically, call this routine only for PP type for GL_POSTING and
1306 --- ++ PY/CY/PP for REPORT where there is no mapping for CY
1307
1308 if l_cy_budget_year_type_id = p_budget_year_type_id then
1309 null; -- no mapping
1310 else
1311 for l_index in 1..g_num_segs loop
1312
1313 l_segment_num := l_index ;
1314 open c_seginfo_subs;
1315 -- ++ for each of the input ccid, fetch the corresponding values for the
1316 -- ++ segment name/value. Only process the first record found, which could
1317 -- ++ either have from value = to l_seg_value, or null from_value
1318
1319 fetch c_seginfo_subs into l_to_value, l_from_value;
1320
1321 if (c_seginfo_subs%NOTFOUND) THEN
1322 close c_seginfo_subs;
1323 else
1324 l_seg_val(l_index) := l_to_value ;
1325 close c_seginfo_subs;
1326 end if ;
1327
1328 end loop;
1329 end if; -- cy type = p type
1330
1331 end if;
1332
1333
1334
1335
1336
1337 --+++++++++++++++++++
1338 -- If the composed Code Combination does not already exist in GL, it is
1339 -- dynamically created
1340 --+++++++++++++++++++
1341
1342 if not FND_FLEX_EXT.Get_Combination_ID
1343 (application_short_name => 'SQLGL',
1344 key_flex_code => 'GL#',
1345 structure_number => g_flex_code,
1346 validation_date => sysdate,
1347 n_segments => g_num_segs,
1348 segments => l_seg_val,
1349 combination_id => l_ccid) then
1350
1351 FND_MSG_PUB.Add;
1352 l_ccid := 0;
1353 raise FND_API.G_EXC_ERROR;
1354 end if;
1355
1356 --+++++++++++++++++++
1357 -- return the new ccid
1358 --+++++++++++++++++++
1359
1360 return(l_ccid);
1361
1362 EXCEPTION
1363
1364 when FND_API.G_EXC_ERROR then
1365 if (c_py%ISOPEN) then
1366 close c_py;
1367 end if;
1371 l_ccid := 0;
1368 if (c_cy%ISOPEN) then
1369 close c_cy;
1370 end if;
1372
1373 when FND_API.G_EXC_UNEXPECTED_ERROR then
1374 if (c_py%ISOPEN) then
1375 close c_py;
1376 end if;
1377 if (c_cy%ISOPEN) then
1378 close c_cy;
1379 end if;
1380 l_ccid := 0;
1381
1382 when OTHERS then
1383 if (c_py%ISOPEN) then
1384 close c_py;
1385 end if;
1386 if (c_cy%ISOPEN) then
1387 close c_cy;
1388 end if;
1389 l_ccid := 0;
1390
1391 END Get_Mapped_CCID;
1392 ----++++++++++
1393
1394 --++
1395 -- function will return the concatenated segments with proper delimiter
1396 -- call this function only for reports, not for gl_posting since it will not
1397 -- dynamically insert a new ccid
1398 --
1399
1400 FUNCTION Get_Mapped_Account
1401 (
1402 p_api_version IN NUMBER,
1403 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1404 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1405 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1406 --
1407 p_CCID IN NUMBER,
1408 p_Budget_Year_Type_ID IN NUMBER,
1409 p_Flexfield_Mapping_Set_ID IN NUMBER
1410
1411 ) RETURN VARCHAR2 IS
1412
1413 l_concat_segments VARCHAR2(2000) := null;
1414 l_flex_code NUMBER := 0;
1415 l_seg_val FND_FLEX_EXT.SegmentArray;
1416 l_return_status VARCHAR2(1);
1417 l_cy_budget_year_type_id NUMBER ;
1418 l_cy_id NUMBER ;
1419 l_index NUMBER;
1420 l_segment_num NUMBER;
1421 l_from_value VARCHAR2(150);
1422 l_cy_from_value VARCHAR2(150);
1423 l_to_value VARCHAR2(150);
1424 l_seg_value VARCHAR2(150);
1425 l_segment_delimiter VARCHAR2(1);
1426 l_py_exists VARCHAR2(1) := FND_API.G_FALSE;
1427
1428 CURSOR c_flex IS
1429 SELECT s.chart_of_accounts_id,fnd.concatenated_segment_delimiter
1430 FROM psb_flex_mapping_sets f,
1431 gl_sets_of_books s,
1432 fnd_id_flex_structures_vl fnd
1433 WHERE flex_mapping_set_id = p_flexfield_mapping_set_id AND
1434 f.set_of_books_id = s.set_of_books_id AND
1435 s.chart_of_accounts_id = fnd.id_flex_num AND
1436 application_id = 101 AND
1437 id_flex_code = 'GL#'
1438
1439 ;
1440
1441
1442 -- cursor for report
1443 -- get the flex map record with to value if it exists is first record;
1444 -- otherwise, null from value will do ... pass segment num
1445
1446 cursor c_seginfo_subs is
1447 select fval.flex_value to_val ,
1448 fromval.flex_value from_val
1449 from fnd_flex_values_vl fval,
1450 fnd_flex_values_vl fromval,
1451 psb_flex_mapping_set_values map,
1452 fnd_id_flex_segments seg
1453 where flex_mapping_set_id = p_flexfield_mapping_set_id
1454 and budget_year_type_id = p_budget_year_type_id
1455 and map.flex_value_id = fval.flex_value_id(+)
1456 and map.from_flex_value_id = fromval.flex_value_id
1457 and seg.application_id = 101
1458 and seg.id_flex_code = 'GL#'
1459 and seg.id_flex_num = l_flex_code
1460 and seg.enabled_flag = 'Y'
1461 and seg.application_column_name = map.application_column_name
1462 and map.application_column_name = g_seg_name(l_segment_num)
1463 and ( fval.flex_value is null
1464 or fromval.flex_value = l_seg_val(l_segment_num) )
1465 order by fromval.flex_value
1466
1467 ;
1468 -- need to outer join fval so that null from values will selected also
1469 -- the record has already been selected and to further select the record with
1470 -- null from value, the map record is now the bigger table(null) than fromval
1471 -- read only values for a segment which matches the input segment or is null
1472 -- and is ordered by with null values last. Specific value matched supercedes
1473 -- null from value so it will be read first when first rec is read.
1474
1475 cursor c_cy is
1476 select fval.flex_value curr_val
1477 from fnd_flex_values_vl fval,
1478 psb_flex_mapping_set_values map,
1479 fnd_id_flex_segments seg
1480 where flex_mapping_set_id = p_flexfield_mapping_set_id
1481 and budget_year_type_id = l_cy_budget_year_type_id
1482 and map.from_flex_value_id = fval.flex_value_id
1483 and seg.application_id = 101
1484 and seg.id_flex_code = 'GL#'
1485 and seg.id_flex_num = l_flex_code
1486 and seg.enabled_flag = 'Y'
1487 and seg.application_column_name = map.application_column_name
1488 and map.application_column_name = g_seg_name(l_segment_num)
1489 ;
1490
1491
1492 cursor c_cy_type is
1493 select budget_year_type_id
1494 from psb_budget_year_types_vl
1495 where year_category_type = 'CY'
1496 ;
1497
1498 cursor c_py_exists is
1499 select 'Exists'
1500 from dual
1501 where exists
1502 (select 1
1503 from psb_budget_year_types_vl
1504 where budget_year_type_id = p_Budget_Year_Type_ID
1505 and year_category_type = 'PY'
1506 );
1510 BEGIN
1507 -- flag indicating if input budget year id is PY since substitution
1508 -- of CY value to PY will take place
1509
1511
1512 -- +++++++++++++++++
1513 -- Setup flex code = coa
1514 -- +++++++++++++++++
1515 OPEN c_flex;
1516 FETCH c_flex INTO l_flex_code,l_segment_delimiter;
1517 IF c_flex%NOTFOUND THEN
1518 CLOSE c_flex;
1519 raise NO_DATA_FOUND;
1520 END IF;
1521 CLOSE c_flex;
1522
1523 -- +++++++++++++++++
1524 -- Setup flex info (segments)
1525 -- +++++++++++++++++
1526
1527 Flex_Info (p_flex_code => l_flex_code,
1528 p_return_status => l_return_status);
1529
1530 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1531 raise FND_API.G_EXC_ERROR;
1532 end if;
1533
1534 -- +++++++++++++++++
1535 -- Explode p_ccid into individual segments to l_seg_val array
1536 -- +++++++++++++++++
1537
1538 if not FND_FLEX_EXT.Get_Segments
1539 (application_short_name => 'SQLGL',
1540 key_flex_code => 'GL#',
1541 structure_number => g_flex_code,
1542 combination_id => p_ccid,
1543 n_segments => g_num_segs,
1544 segments => l_seg_val ) then
1545
1546 FND_MSG_PUB.Add;
1547 raise FND_API.G_EXC_ERROR;
1548 end if;
1549
1550
1551 -- +++++++++++++++++
1552 -- get current year budget year type id
1553 -- +++++++++++++++++
1554
1555 for c_cy_type_rec in c_cy_type loop
1556 -- get cy id
1557 l_cy_budget_year_type_id := c_cy_type_rec.budget_year_type_id;
1558 end loop;
1559
1560 for c_py_exists_rec in c_py_exists loop
1561 l_py_exists := FND_API.G_TRUE;
1562 -- indicates if py budget group id is before cy where the ccid segment
1563 -- of the cy will be mapped back to py ; all other py will be unchanged
1564 end loop;
1565
1566 -- +++++++++++++++++
1567 -- for worksheet mapping mode, always map using current year type since cy stores the
1568 -- values to be stored in worksheet
1569 -- +++++++++++++++++
1570
1571 --- ++ REPORT ; use the input budget year type id to map the values
1572 --- ++ this is specific from_value substitution in this order
1573 --- ++ 1. from value = input ccid segment
1574 --- ++ 2. from value is null, substitute to value to input ccid segment
1575 --- ++ 3. retain input ccid segment coz no flex mapping record found
1576 --- ++ if input budget year type id is current year, do not do a mapping
1577 --- ++ logically, call this routine only for PP type for GL_POSTING and
1578 --- ++ PY/CY/PP for REPORT where there is no mapping for CY
1579
1580 if l_cy_budget_year_type_id = p_budget_year_type_id then
1581 null; -- no mapping
1582 else
1583 for l_index in 1..g_num_segs loop
1584
1585 l_segment_num := l_index ;
1586 open c_seginfo_subs;
1587
1588 -- ++ for each of the input ccid, fetch the corresponding values for the
1589 -- ++ segment name/value. Only process the first record found, which could
1590 -- ++ either have from value = to l_seg_value, or null to_value
1591
1592 fetch c_seginfo_subs into l_to_value, l_from_value;
1593
1594 if (c_seginfo_subs%NOTFOUND) THEN
1595 close c_seginfo_subs;
1596 else
1597
1598 if (FND_API.to_Boolean(l_py_exists)) then
1599
1600 -- ++ substitute ws ccid from value for py to value
1601 open c_cy;
1602 fetch c_cy into l_cy_from_value;
1603 if (c_cy%NOTFOUND) THEN
1604 close c_cy;
1605 end if;
1606 if l_seg_val(l_index) = l_cy_from_value then
1607
1608 l_seg_val(l_index) := l_from_value; -- py value substituted
1609
1610 close c_cy;
1611 end if;
1612
1613 else
1614 -- ++ pp substitution is flex map's from/to value
1615 -- ++ cursor should have done a match already
1616 l_seg_val(l_index) := l_to_value;
1617 end if;
1618 close c_seginfo_subs;
1619 end if ;
1620
1621 end loop;
1622 end if; -- cy type = p type
1623
1624
1625
1626 --+++++++++++++++++++
1627 -- Concatenate the segments of the account combination
1628 --+++++++++++++++++++
1629 l_concat_segments := FND_FLEX_EXT.Concatenate_Segments
1630 (n_segments => g_num_segs,
1631 segments => l_seg_val,
1632 delimiter => l_segment_delimiter);
1633
1634
1635 --+++++++++++++++++++
1636 -- return the new ccid
1637 --+++++++++++++++++++
1638
1639 return(l_concat_segments);
1640
1641 EXCEPTION
1642
1643 when FND_API.G_EXC_ERROR then
1644 if (c_cy%ISOPEN) then
1645 close c_cy;
1646 end if;
1647 l_concat_segments := p_ccid;
1648
1649 when FND_API.G_EXC_UNEXPECTED_ERROR then
1650 if (c_cy%ISOPEN) then
1651 close c_cy;
1652 end if;
1653 l_concat_segments := p_ccid;
1654
1655 when OTHERS then
1656 if (c_cy%ISOPEN) then
1657 close c_cy;
1658 end if;
1659 l_concat_segments := p_ccid;
1660
1661 END;
1662
1663
1664
1665 PROCEDURE Flex_Info
1666 ( p_return_status OUT NOCOPY VARCHAR2,
1667 p_flex_code IN NUMBER
1668 ) IS
1669
1670 cursor c_seginfo is
1671 select application_column_name,segment_num
1672 from fnd_id_flex_segments
1673 where application_id = 101
1674 and id_flex_code = 'GL#'
1675 and id_flex_num = p_flex_code
1676 and enabled_flag = 'Y'
1677 order by segment_num;
1678
1679 BEGIN
1680
1681 -- this procedure sets the number of segments used by the coa and
1682 -- stores the segments names (i.e., SEGMENT1...)
1683 for l_init_index in 1..g_seg_name.Count loop
1684 g_seg_name(l_init_index) := null;
1685 end loop;
1686
1687 g_num_segs := 0;
1688
1689 g_flex_code := p_flex_code;
1690
1691 for c_Seginfo_Rec in c_seginfo loop
1692 g_num_segs := g_num_segs + 1;
1693 g_seg_name(g_num_segs) := c_Seginfo_Rec.application_column_name;
1694 g_seg_num(g_num_segs) := c_Seginfo_Rec.segment_num;
1695 end loop;
1696
1697
1698 --+++++++++++++++++++
1699 -- If the composed Code Combination does not already exist in GL, it is
1700 -- dynamically created
1701 --+++++++++++++++++++
1702
1703 p_return_status := FND_API.G_RET_STS_SUCCESS;
1704
1705
1706 EXCEPTION
1707
1708 when FND_API.G_EXC_ERROR then
1709 p_return_status := FND_API.G_RET_STS_ERROR;
1710
1711 when FND_API.G_EXC_UNEXPECTED_ERROR then
1712 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1713
1714 when OTHERS then
1715 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1716
1717 END Flex_Info;
1718
1719 /*-------------------------------------------------------------------------*/
1720
1721 /*-------------------------------------------------------------------------*/
1722
1723
1724 PROCEDURE Pass_View_Parameters ( p_flex_set_id IN NUMBER,
1725 p_application_column_name IN VARCHAR2) IS
1726
1727 BEGIN
1728 g_Flex_Set_ID := p_Flex_Set_ID;
1729 g_Application_Column_Name := p_Application_Column_Name;
1730
1731 END Pass_View_Parameters;
1732
1733 --
1734 -- FUNCTIONS
1735 --
1736
1737 FUNCTION Get_Flex_Set_ID RETURN NUMBER IS
1738 BEGIN
1739 Return g_Flex_Set_ID;
1740 END Get_Flex_Set_ID ;
1741
1742 FUNCTION Get_Application_Column_Name RETURN varchar2 IS
1743 BEGIN
1744 Return g_Application_Column_Name;
1745 END Get_Application_Column_Name;
1746
1747
1748
1749
1750 /* ----------------------------------------------------------------------- */
1751
1752 END PSB_Flex_Mapping_PVT;
[Home] [Help]