[Home] [Help]
PACKAGE BODY: APPS.BIS_BUSINESS_PLAN_PVT
Source
1 PACKAGE BODY BIS_BUSINESS_PLAN_PVT AS
2 /* $Header: BISVBPB.pls 115.9 2004/02/13 08:22:43 ankgoel noship $ */
3 /*
4 REM +=======================================================================+
5 REM | Copyright (c) 1998 Oracle Corporation, Redwood Shores, CA, USA |
6 REM | All rights reserved. |
7 REM +=======================================================================+
8 REM | FILENAME |
9 REM | BISVBPB.pls |
10 REM | |
11 REM | DESCRIPTION |
12 REM | Private API for managing business plans for the
13 REM | Key Performance Framework.
14 REM |
15 REM | NOTES |
16 REM | |
17 REM | HISTORY |
18 REM | 14-JUL-1999 irchen Creation |
19 REM | 13-FEB-2004 ankgoel bug #3436033. Used the base tables for |
20 REM | "Value_ID_Conversion" & "Retrieve_Business_Plan"|
21 REM +=======================================================================+
22 */
23 --
24 G_PKG_NAME CONSTANT VARCHAR2(30):='BIS_BUSINESS_PLAN_PVT';
25 --
26 --
27 -- returns the record with the G_MISS_CHAR/G_MISS_NUM replaced
28 -- by null
29 --
30 PROCEDURE SetNULL
31 ( p_Business_Plan_Rec IN BIS_BUSINESS_PLAN_PUB.Business_Plan_Rec_Type
32 , x_Business_Plan_Rec OUT NOCOPY BIS_BUSINESS_PLAN_PUB.Business_Plan_Rec_Type
33 );
34 --
35 -- queries database to retrieve the business plan from the database
36 -- updates the record with the changes sent in
37 --
38 PROCEDURE UpdateRecord
39 ( p_Business_Plan_Rec BIS_Business_Plan_PUB.Business_Plan_Rec_Type
40 , x_Business_Plan_Rec OUT NOCOPY BIS_Business_Plan_PUB.Business_Plan_Rec_Type
41 , x_return_status OUT NOCOPY VARCHAR2
42 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
43 );
44 --
45 --
46 PROCEDURE SetNULL
47 ( p_Business_Plan_Rec IN BIS_BUSINESS_PLAN_PUB.Business_Plan_Rec_Type
48 , x_Business_Plan_Rec OUT NOCOPY BIS_BUSINESS_PLAN_PUB.Business_Plan_Rec_Type
49 )
50 IS
51 BEGIN
52
53 x_business_plan_rec.Business_Plan_ID
54 := BIS_UTILITIES_PVT.CheckMissNum(p_business_plan_rec.Business_Plan_ID);
55 x_business_plan_rec.Business_Plan_Short_Name
56 := BIS_UTILITIES_PVT.CheckMissChar
57 (p_business_plan_rec.Business_Plan_Short_Name);
58 x_business_plan_rec.Business_Plan_Name
59 := BIS_UTILITIES_PVT.CheckMissChar(p_business_plan_rec.Business_Plan_Name);
60 x_business_plan_rec.Description
61 := BIS_UTILITIES_PVT.CheckMissChar(p_business_plan_rec.Description);
62 x_business_plan_rec.Version_Number
63 := BIS_UTILITIES_PVT.CheckMissNum(p_business_plan_rec.Version_Number);
64 x_business_plan_rec.Current_Plan_Flag
65 := BIS_UTILITIES_PVT.CheckMissChar(p_business_plan_rec.Current_Plan_Flag);
66
67 EXCEPTION
68 WHEN FND_API.G_EXC_ERROR THEN
69 RAISE;
70 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
71 RAISE;
72 WHEN OTHERS THEN
73 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
74
75 END SetNULL;
76 --
77 PROCEDURE UpdateRecord
78 ( p_Business_Plan_Rec BIS_Business_Plan_PUB.Business_Plan_Rec_Type
79 , x_Business_Plan_Rec OUT NOCOPY BIS_Business_Plan_PUB.Business_Plan_Rec_Type
80 , x_return_status OUT NOCOPY VARCHAR2
81 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
82 )
83 IS
84 --
85 l_Business_Plan_Rec BIS_Business_Plan_PUB.Business_Plan_Rec_Type;
86 l_return_status VARCHAR2(10);
87 --
88 BEGIN
89 x_return_status:= FND_API.G_RET_STS_SUCCESS;
90
91 -- retrieve record from db
92 BIS_Business_Plan_PVT.Retrieve_Business_Plan
93 ( p_api_version => 1.0
94 , p_Business_Plan_Rec => p_Business_Plan_Rec
95 , x_Business_Plan_Rec => l_Business_Plan_Rec
96 , x_return_status => x_return_status
97 , x_error_Tbl => x_error_Tbl
98 );
99
100 -- apply changes
101 IF( BIS_UTILITIES_PUB.Value_Not_Missing(p_Business_Plan_Rec.Business_Plan_ID)
102 = FND_API.G_TRUE
103 ) THEN
104 l_Business_Plan_Rec.Business_Plan_ID
105 := p_Business_Plan_Rec.Business_Plan_ID;
106 END IF;
107 --
108 IF( BIS_UTILITIES_PUB.Value_Not_Missing(
109 p_Business_Plan_Rec.Business_Plan_Short_Name)
110 = FND_API.G_TRUE
111 ) THEN
112 l_Business_Plan_Rec.Business_Plan_Short_Name
113 := p_Business_Plan_Rec.Business_Plan_Short_Name ;
114 END IF;
115 --
116 IF( BIS_UTILITIES_PUB.Value_Not_Missing(
117 p_Business_Plan_Rec.Business_Plan_Name)
118 = FND_API.G_TRUE
119 ) THEN
120 l_Business_Plan_Rec.Business_Plan_Name
121 := p_Business_Plan_Rec.Business_Plan_Name;
122 END IF;
123 --
124 IF( BIS_UTILITIES_PUB.Value_Not_Missing(p_Business_Plan_Rec.Description)
125 = FND_API.G_TRUE
126 ) THEN
127 l_Business_Plan_Rec.Description
128 := p_Business_Plan_Rec.Description;
129 END IF;
130 --
131 IF( BIS_UTILITIES_PUB.Value_Not_Missing(p_Business_Plan_Rec.Version_number)
132 = FND_API.G_TRUE
133 ) THEN
134 l_Business_Plan_Rec.Version_number
135 := p_Business_Plan_Rec.Version_number;
136 END IF;
137 --
138 IF( BIS_UTILITIES_PUB.Value_Not_Missing(
139 p_Business_Plan_Rec.Current_Plan_Flag)
140 = FND_API.G_TRUE
141 ) THEN
142 l_Business_Plan_Rec.Current_Plan_Flag
143 := p_Business_Plan_Rec.Current_Plan_Flag;
144 END IF;
145
146 x_Business_Plan_Rec := l_Business_Plan_Rec;
147 --
148 EXCEPTION
149 WHEN FND_API.G_EXC_ERROR THEN
150 x_return_status:= FND_API.G_RET_STS_ERROR;
151 RAISE;
152 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
153 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
154 RAISE;
155 WHEN OTHERS THEN
156 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
157 BIS_UTILITIES_PVT.Add_Error_Message
158 ( p_error_table => x_error_Tbl
159 , p_error_msg_id => SQLCODE
160 , p_error_description => SQLERRM
161 , x_error_table => x_error_Tbl
162 );
163 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
164
165 END UpdateRecord;
166 --
167 --
168 Procedure Retrieve_Business_Plans
169 ( p_api_version IN NUMBER
170 , x_Business_Plan_Tbl OUT NOCOPY BIS_BUSINESS_PLAN_PUB.Business_Plan_Tbl_Type
171 , x_return_status OUT NOCOPY VARCHAR2
172 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
173 )
174 IS
175
176 l_business_plan_rec BIS_BUSINESS_PLAN_PUB.Business_Plan_Rec_Type;
177 cursor cr_all_business_plans is
178 select plan_id
179 , short_name
180 , name
181 , description
182 , version_no
183 , current_plan_flag
184 from bisfv_business_plans;
185
186 BEGIN
187
188 x_return_status := FND_API.G_RET_STS_SUCCESS;
189
190 for cr in cr_all_business_plans loop
191 l_business_plan_rec.business_plan_id := cr.plan_id;
192 l_business_plan_rec.business_plan_short_name := cr.short_name;
193 l_business_plan_rec.business_plan_name := cr.name;
194 l_business_plan_rec.description := cr.description;
195 l_business_plan_rec.version_number := cr.version_no;
196 l_business_plan_rec.current_plan_flag := cr.current_plan_flag;
197
198 x_business_plan_tbl(x_business_plan_tbl.count + 1) := l_business_plan_rec;
199
200 end loop;
201
202 EXCEPTION
203 WHEN NO_DATA_FOUND THEN
204 x_return_status := FND_API.G_RET_STS_ERROR ;
205 RAISE FND_API.G_EXC_ERROR;
206 when FND_API.G_EXC_ERROR then
207 x_return_status := FND_API.G_RET_STS_ERROR ;
208 RAISE FND_API.G_EXC_ERROR;
209 when FND_API.G_EXC_UNEXPECTED_ERROR then
210 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
211 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
212 when others then
213 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
214 BIS_UTILITIES_PVT.Add_Error_Message
215 ( p_error_msg_id => SQLCODE
216 , p_error_description => SQLERRM
217 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Business_Plans'
218 );
219 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
220
221 END Retrieve_Business_Plans;
222 --
223 Procedure Retrieve_Business_Plan
224 ( p_api_version IN NUMBER
225 , p_Business_Plan_Rec IN BIS_BUSINESS_PLAN_PUB.Business_Plan_Rec_Type
226 , x_Business_Plan_Rec OUT NOCOPY BIS_BUSINESS_PLAN_PUB.Business_Plan_Rec_Type
227 , x_return_status OUT NOCOPY VARCHAR2
228 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
229 )
230 is
231 begin
232
233 x_return_status := FND_API.G_RET_STS_SUCCESS;
234
235 x_Business_Plan_Rec := p_Business_Plan_Rec;
236 SELECT bp.short_name
237 , bptl.name
238 , bptl.description
239 , bp.version_no
240 , bp.current_plan_flag
241 INTO x_Business_Plan_Rec.business_plan_short_name
242 , x_Business_Plan_Rec.business_plan_name
243 , x_Business_Plan_Rec.description
244 , x_Business_Plan_Rec.version_number
245 , x_Business_Plan_Rec.current_plan_flag
246 FROM bis_business_plans bp, bis_business_plans_tl bptl
247 WHERE bp.plan_id = p_Business_Plan_Rec.business_plan_id
248 AND bp.plan_id = bptl.plan_id
249 AND bptl.language = userenv('LANG');
250
251 EXCEPTION
252 WHEN NO_DATA_FOUND THEN
253 x_return_status := FND_API.G_RET_STS_ERROR ;
254 RAISE FND_API.G_EXC_ERROR;
255 when FND_API.G_EXC_ERROR then
256 x_return_status := FND_API.G_RET_STS_ERROR ;
257 RAISE FND_API.G_EXC_ERROR;
258 when FND_API.G_EXC_UNEXPECTED_ERROR then
259 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
260 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
261 when others then
262 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
263 BIS_UTILITIES_PVT.Add_Error_Message
264 ( p_error_msg_id => SQLCODE
265 , p_error_description => SQLERRM
266 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Business_Plan'
267 );
268 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
269 end Retrieve_Business_Plan;
270 --
271 Procedure Create_Business_Plan
272 ( p_api_version IN NUMBER
273 , p_commit IN VARCHAR2 := FND_API.G_FALSE
274 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
275 , p_Business_Plan_Rec IN BIS_BUSINESS_PLAN_PUB.Business_Plan_Rec_Type
276 , x_return_status OUT NOCOPY VARCHAR2
277 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
278 )
279 IS
280 BEGIN
281
282 Create_Business_Plan
283 ( p_api_version => p_api_version
284 , p_commit => p_commit
285 , p_validation_level => p_validation_level
286 , p_Business_Plan_Rec => p_Business_Plan_Rec
287 , p_owner => BIS_UTILITIES_PUB.G_CUSTOM_OWNER
288 , x_return_status => x_return_status
289 , x_error_Tbl => x_error_Tbl
290 );
291
292 EXCEPTION
293 when others then
294 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
295 BIS_UTILITIES_PVT.Add_Error_Message
296 ( p_error_msg_id => SQLCODE
297 , p_error_description => SQLERRM
298 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Business_Plan'
299 );
300 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
301
302
303 END Create_Business_Plan;
304 --
305 Procedure Create_Business_Plan
306 ( p_api_version IN NUMBER
307 , p_commit IN VARCHAR2 := FND_API.G_FALSE
308 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
309 , p_Business_Plan_Rec IN BIS_BUSINESS_PLAN_PUB.Business_Plan_Rec_Type
310 , p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
311 , x_return_status OUT NOCOPY VARCHAR2
312 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
313 )
314 IS
315 l_user_id NUMBER;
316 l_login_id NUMBER;
317 l_id NUMBER;
318 l_business_plan_rec BIS_BUSINESS_PLAN_PUB.Business_Plan_Rec_Type;
319 BEGIN
320
321 x_return_status := FND_API.G_RET_STS_SUCCESS;
322 l_business_plan_Rec := p_business_plan_Rec;
323
324 SetNULL
325 ( p_business_plan_Rec => p_business_plan_Rec
326 , x_business_plan_Rec => l_business_plan_Rec
327 );
328
329 Validate_Business_Plan( p_api_version
330 , p_validation_level
331 , l_Business_Plan_Rec
332 , x_return_status
333 , x_error_Tbl
334 );
335
336 IF( x_return_status <> FND_API.G_RET_STS_SUCCESS) then
337 RAISE FND_API.G_EXC_ERROR;
338 END IF;
339 --
340 IF p_owner = BIS_UTILITIES_PUB.G_SEED_OWNER THEN
341 l_user_id := BIS_UTILITIES_PUB.G_SEED_USER_ID;
342 ELSE
343 l_user_id := fnd_global.USER_ID;
344 END IF;
345
346 l_login_id := fnd_global.LOGIN_ID;
347 --
348
349 select bis_business_plans_s.NextVal into l_id from dual;
350
351 insert into bis_business_plans(
352 PLAN_ID
353 , SHORT_NAME
354 , VERSION_NO
355 , CURRENT_PLAN_FLAG
356 , CREATION_DATE
357 , CREATED_BY
358 , LAST_UPDATE_DATE
359 , LAST_UPDATED_BY
360 , LAST_UPDATE_LOGIN
361 )
362 values
363 ( l_id
364 , l_Business_Plan_Rec.Business_Plan_Short_Name
365 , l_Business_Plan_Rec.Version_Number
366 , l_Business_Plan_Rec.Current_Plan_Flag
367 , SYSDATE
368 , l_user_id
369 , SYSDATE
370 , l_user_id
371 , l_login_id
372 );
373
374 insert into bis_BUSINESS_PLANS_TL (
375 PLAN_ID,
376 LANGUAGE,
377 NAME,
378 DESCRIPTION,
379 CREATION_DATE,
380 CREATED_BY,
381 LAST_UPDATE_DATE,
382 LAST_UPDATED_BY,
383 LAST_UPDATE_LOGIN,
384 TRANSLATED,
385 SOURCE_LANG
386 ) select
387 P.Plan_id
388 , L.LANGUAGE_CODE
389 , l_Business_Plan_Rec.Business_Plan_Name
390 , l_Business_Plan_Rec.Description
391 , SYSDATE
392 , l_user_id
393 , SYSDATE
394 , l_user_id
395 , l_login_id
396 , 'Y'
397 , userenv('LANG')
398 FROM FND_LANGUAGES L
399 , BIS_BUSINESS_PLANS P
400 WHERE L.INSTALLED_FLAG IN ('I', 'B')
401 AND P.SHORT_NAME = l_Business_Plan_Rec.Business_Plan_Short_Name
402 AND NOT EXISTS
403 (SELECT 'EXISTS'
404 FROM BIS_BUSINESS_PLANS_TL TL
405 , BIS_BUSINESS_PLANS P
406 WHERE TL.PLAN_ID = P.PLAN_ID
407 AND P.SHORT_NAME = l_Business_Plan_Rec.Business_Plan_Short_Name
408 AND TL.LANGUAGE = L.LANGUAGE_CODE) ;
409
410 if (p_commit = FND_API.G_TRUE) then
411 COMMIT;
412 end if;
413
414 EXCEPTION
415 WHEN NO_DATA_FOUND THEN
416 x_return_status := FND_API.G_RET_STS_ERROR ;
417 RAISE FND_API.G_EXC_ERROR;
418 when FND_API.G_EXC_ERROR then
419 x_return_status := FND_API.G_RET_STS_ERROR ;
420 RAISE FND_API.G_EXC_ERROR;
421 when FND_API.G_EXC_UNEXPECTED_ERROR then
422 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
423 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
424 when others then
425 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
426 BIS_UTILITIES_PVT.Add_Error_Message
427 ( p_error_msg_id => SQLCODE
428 , p_error_description => SQLERRM
429 , p_error_proc_name => G_PKG_NAME||'.Create_Business_Plan'
430 );
431 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
432 END Create_Business_Plan;
433 --
434 Procedure Update_Business_Plan
435 ( p_api_version IN NUMBER
436 , p_commit IN VARCHAR2 := FND_API.G_FALSE
437 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
438 , p_Business_Plan_Rec IN BIS_BUSINESS_PLAN_PUB.Business_Plan_Rec_Type
439 , x_return_status OUT NOCOPY VARCHAR2
440 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
441 )
442 IS
443 BEGIN
444
445 Update_Business_Plan
446 ( p_api_version => p_api_version
447 , p_commit => p_commit
448 , p_validation_level => p_validation_level
449 , p_Business_Plan_Rec => p_Business_Plan_Rec
450 , p_owner => BIS_UTILITIES_PUB.G_CUSTOM_OWNER
451 , x_return_status => x_return_status
452 , x_error_Tbl => x_error_Tbl
453 );
454
455 EXCEPTION
456 when others then
457 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
458 BIS_UTILITIES_PVT.Add_Error_Message
459 ( p_error_msg_id => SQLCODE
460 , p_error_description => SQLERRM
461 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Business_Plan'
462 );
463 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
464
465 END Update_Business_Plan;
466 --
467 Procedure Update_Business_Plan
468 ( p_api_version IN NUMBER
469 , p_commit IN VARCHAR2 := FND_API.G_FALSE
470 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
471 , p_Business_Plan_Rec IN BIS_BUSINESS_PLAN_PUB.Business_Plan_Rec_Type
472 , p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
473 , x_return_status OUT NOCOPY VARCHAR2
474 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
475 )
476 IS
477 l_user_id number;
478 l_login_id number;
479 l_count NUMBER := 0;
480 l_business_plan_rec BIS_BUSINESS_PLAN_PUB.Business_Plan_Rec_Type;
481
482 BEGIN
483
484 -- retrieve record from database and apply changes
485 UpdateRecord
486 ( p_Business_Plan_Rec => p_Business_Plan_Rec
487 , x_Business_Plan_Rec => l_Business_Plan_Rec
488 , x_return_status => x_return_status
489 , x_error_Tbl => x_error_Tbl
490 );
491
492 Validate_Business_Plan
493 ( p_api_version
494 , p_validation_level
495 , l_Business_Plan_Rec
496 , x_return_status
497 , x_error_Tbl
498 );
499
500 IF( x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
501 RAISE FND_API.G_EXC_ERROR;
502 END IF;
503 --
504 IF p_owner = BIS_UTILITIES_PUB.G_SEED_OWNER THEN
505 l_user_id := BIS_UTILITIES_PUB.G_SEED_USER_ID;
506 ELSE
507 l_user_id := fnd_global.USER_ID;
508 END IF;
509
510 l_login_id := fnd_global.LOGIN_ID;
511 --
512
513 Update bis_business_plans
514 set
515 SHORT_NAME
516 = l_Business_Plan_Rec.Business_Plan_Short_Name
517 , VERSION_NO
518 = l_Business_Plan_Rec.Version_Number
519 , CURRENT_PLAN_FLAG
520 = l_Business_Plan_Rec.Current_Plan_Flag
521 , LAST_UPDATE_DATE = SYSDATE
522 , LAST_UPDATED_BY = l_user_id
523 , LAST_UPDATE_LOGIN = l_login_id
524 where plan_ID = l_Business_Plan_Rec.Business_Plan_Id;
525
526 if (p_commit = FND_API.G_TRUE) then
527 COMMIT;
528 end if;
529
530 Translate_business_plan
531 ( p_api_version => p_api_version
532 , p_commit => p_commit
533 , p_validation_level => p_validation_level
534 , p_Business_Plan_Rec => l_Business_Plan_Rec
535 , p_owner => p_owner
536 , x_return_status => x_return_status
537 , x_error_Tbl => x_error_Tbl
538 );
539
540 EXCEPTION
541 WHEN NO_DATA_FOUND THEN
542 x_return_status := FND_API.G_RET_STS_ERROR ;
543 RAISE FND_API.G_EXC_ERROR;
544 when FND_API.G_EXC_ERROR then
545 x_return_status := FND_API.G_RET_STS_ERROR ;
546 RAISE FND_API.G_EXC_ERROR;
547 when FND_API.G_EXC_UNEXPECTED_ERROR then
548 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
549 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
550 when others then
551 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
552 BIS_UTILITIES_PVT.Add_Error_Message
553 ( p_error_msg_id => SQLCODE
554 , p_error_description => SQLERRM
555 , p_error_proc_name => G_PKG_NAME||'.Update_Business_Plan'
556 );
557 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
558
559 END Update_Business_Plan;
560 --
561 Procedure Translate_Business_Plan
562 ( p_api_version IN NUMBER
563 , p_commit IN VARCHAR2 := FND_API.G_FALSE
564 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
565 , p_Business_Plan_Rec IN BIS_BUSINESS_PLAN_PUB.Business_Plan_Rec_Type
566 , x_return_status OUT NOCOPY VARCHAR2
567 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
568 )
569 IS
570 BEGIN
571
572 Translate_Business_Plan
573 ( p_api_version => p_api_version
574 , p_commit => p_commit
575 , p_validation_level => p_validation_level
576 , p_Business_Plan_Rec => p_Business_Plan_Rec
577 , p_owner => BIS_UTILITIES_PUB.G_CUSTOM_OWNER
578 , x_return_status => x_return_status
579 , x_error_Tbl => x_error_Tbl
580 );
581
582 EXCEPTION
583 when others then
584 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
585 BIS_UTILITIES_PVT.Add_Error_Message
586 ( p_error_msg_id => SQLCODE
587 , p_error_description => SQLERRM
588 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Business_Plan'
589 );
590 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
591
592 END Translate_Business_Plan;
593 --
594 Procedure Translate_Business_Plan
595 ( p_api_version IN NUMBER
596 , p_commit IN VARCHAR2 := FND_API.G_FALSE
597 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
598 , p_Business_Plan_Rec IN BIS_BUSINESS_PLAN_PUB.Business_Plan_Rec_Type
599 , p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
600 , x_return_status OUT NOCOPY VARCHAR2
601 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
602 )
603 IS
604 l_user_id NUMBER;
605 l_login_id NUMBER;
606 l_count NUMBER := 0;
607 l_business_plan_rec BIS_BUSINESS_PLAN_PUB.Business_Plan_Rec_Type;
608
609 BEGIN
610
611 -- retrieve record from database and apply changes
612 UpdateRecord
613 ( p_Business_Plan_Rec => p_Business_Plan_Rec
614 , x_Business_Plan_Rec => l_Business_Plan_Rec
615 , x_return_status => x_return_status
616 , x_error_Tbl => x_error_Tbl
617 );
618
619 Validate_Business_Plan
620 ( p_api_version
621 , p_validation_level
622 , l_Business_Plan_Rec
623 , x_return_status
624 , x_error_Tbl
625 );
626
627 IF( x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
628 RAISE FND_API.G_EXC_ERROR;
629 END IF;
630 --
631 IF p_owner = BIS_UTILITIES_PUB.G_SEED_OWNER THEN
632 l_user_id := BIS_UTILITIES_PUB.G_SEED_USER_ID;
633 ELSE
634 l_user_id := fnd_global.USER_ID;
635 END IF;
636
637 l_login_id := fnd_global.LOGIN_ID;
638 --
639
640 Update bis_business_plans_TL
641 set
642 NAME = l_Business_Plan_Rec.Business_Plan_Name
643 , DESCRIPTION = l_Business_Plan_Rec.description
644 , LAST_UPDATE_DATE = SYSDATE
645 , LAST_UPDATED_BY = l_user_id
646 , LAST_UPDATE_LOGIN = l_login_id
647 , SOURCE_LANG = userenv('LANG')
648 where PLAN_ID = l_Business_Plan_Rec.Business_Plan_Id
649 and userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
650
651 if (p_commit = FND_API.G_TRUE) then
652 COMMIT;
653 end if;
654
655 EXCEPTION
656 WHEN NO_DATA_FOUND THEN
657 x_return_status := FND_API.G_RET_STS_ERROR ;
658 RAISE FND_API.G_EXC_ERROR;
659 when FND_API.G_EXC_ERROR then
660 x_return_status := FND_API.G_RET_STS_ERROR ;
661 RAISE FND_API.G_EXC_ERROR;
662 when FND_API.G_EXC_UNEXPECTED_ERROR then
663 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
664 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
665 when others then
666 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
667 BIS_UTILITIES_PVT.Add_Error_Message
668 ( p_error_msg_id => SQLCODE
669 , p_error_description => SQLERRM
670 , p_error_proc_name => G_PKG_NAME||'.Translate_Business_Plan'
671 );
672 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
673
674 END Translate_Business_Plan;
675 --
676 --
677 PROCEDURE Validate_Business_Plan
678 ( p_api_version IN NUMBER
679 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
680 , p_Business_Plan_Rec IN BIS_BUSINESS_PLAN_PUB.Business_Plan_Rec_Type
681 , x_return_status OUT NOCOPY VARCHAR2
682 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
683 )
684 IS
685 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
686 BEGIN
687 BEGIN
688
689 BIS_BUSINESS_PLAN_VALIDATE_PVT.Validate_Record
690 ( p_api_version => p_api_version
691 , p_validation_level => p_validation_level
692 , p_Business_Plan_Rec => p_Business_Plan_Rec
693 , x_return_status => x_return_status
694 , x_error_Tbl => l_error_Tbl
695 );
696
697 EXCEPTION
698 when FND_API.G_EXC_ERROR then
699 BIS_UTILITIES_PVT.concatenateErrorTables( x_error_Tbl
700 , l_error_Tbl
701 , x_error_tbl
702 );
703 x_return_status := FND_API.G_RET_STS_ERROR;
704 END;
705
706 if (x_error_tbl.count > 0) then
707 RAISE FND_API.G_EXC_ERROR;
708 end if;
709
710 EXCEPTION
711 WHEN NO_DATA_FOUND THEN
712 x_return_status := FND_API.G_RET_STS_ERROR ;
713 RAISE FND_API.G_EXC_ERROR;
714 when FND_API.G_EXC_ERROR then
715 x_return_status := FND_API.G_RET_STS_ERROR ;
716 RAISE FND_API.G_EXC_ERROR;
717 when FND_API.G_EXC_UNEXPECTED_ERROR then
718 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
719 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
720 when others then
721 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
722 BIS_UTILITIES_PVT.Add_Error_Message
723 ( p_error_msg_id => SQLCODE
724 , p_error_description => SQLERRM
725 , p_error_proc_name => G_PKG_NAME||'.Validate_Business_Plan'
726 );
727 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
728
729 END Validate_Business_Plan;
730 --
731 -- Value - ID conversion
732 PROCEDURE Value_ID_Conversion
733 ( p_api_version IN NUMBER
734 , p_Business_Plan_Rec IN BIS_BUSINESS_PLAN_PUB.Business_Plan_Rec_Type
735 , x_Business_Plan_Rec OUT NOCOPY BIS_BUSINESS_PLAN_PUB.Business_Plan_Rec_Type
736 , x_return_status OUT NOCOPY VARCHAR2
737 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
738 )
739 is
740 begin
741
742 x_return_status := FND_API.G_RET_STS_SUCCESS;
743 x_Business_Plan_Rec := p_Business_Plan_Rec;
744
745 if (BIS_UTILITIES_PUB.Value_Missing(x_Business_Plan_Rec.Business_Plan_id)
746 = FND_API.G_TRUE) then
747
748 BIS_BUSINESS_PLAN_PVT.Value_ID_Conversion
749 ( p_api_version
750 , x_Business_Plan_Rec.Business_Plan_Short_Name
751 , x_Business_Plan_Rec.Business_Plan_Name
752 , x_Business_Plan_Rec.Business_Plan_ID
753 , x_return_status
754 , x_error_Tbl
755 );
756 end if;
757
758 EXCEPTION
759 WHEN NO_DATA_FOUND THEN
760 x_return_status := FND_API.G_RET_STS_ERROR ;
761 RAISE FND_API.G_EXC_ERROR;
762 when FND_API.G_EXC_ERROR then
763 x_return_status := FND_API.G_RET_STS_ERROR ;
764 RAISE FND_API.G_EXC_ERROR;
765 when FND_API.G_EXC_UNEXPECTED_ERROR then
766 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
767 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
768 when others then
769 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
770 BIS_UTILITIES_PVT.Add_Error_Message
771 ( p_error_msg_id => SQLCODE
772 , p_error_description => SQLERRM
773 , p_error_proc_name => G_PKG_NAME||'.Value_ID_Conversion'
774 );
775 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
776
777 end Value_ID_Conversion;
778 --
779 PROCEDURE Value_ID_Conversion
780 ( p_api_version IN NUMBER
781 , p_Business_Plan_Short_Name IN VARCHAR2
782 , p_Business_Plan_Name IN VARCHAR2
783 , x_Business_Plan_ID OUT NOCOPY NUMBER
784 , x_return_status OUT NOCOPY VARCHAR2
785 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
786 )
787 is
788 begin
789
790 x_return_status := FND_API.G_RET_STS_SUCCESS;
791
792 if (BIS_UTILITIES_PUB.Value_Not_Missing(p_Business_Plan_Short_Name)
793 = FND_API.G_TRUE) then
794
795 SELECT plan_id into x_Business_Plan_ID
796 FROM bis_business_plans
797 WHERE short_name = p_Business_Plan_Short_Name;
798
799 elsif (BIS_UTILITIES_PUB.Value_Not_Missing(p_Business_Plan_Name)
800 = FND_API.G_TRUE) then
801
802 SELECT plan_id into x_Business_Plan_ID
803 FROM bis_business_plans_tl
804 WHERE name = p_Business_Plan_Name
805 AND language = userenv('LANG');
806 else
807
808 BIS_UTILITIES_PVT.Add_Error_Message
809 ( p_error_msg_name => 'BIS_NAME_SHORT_NAME_MISSING'
810 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
811 , p_error_proc_name => G_PKG_NAME||'.Value_ID_Conversion'
812 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
813 );
814
815 RAISE FND_API.G_EXC_ERROR;
816 end if;
817
818 EXCEPTION
819 WHEN NO_DATA_FOUND THEN
820 x_return_status := FND_API.G_RET_STS_ERROR ;
821 RAISE FND_API.G_EXC_ERROR;
822 when FND_API.G_EXC_ERROR then
823 x_return_status := FND_API.G_RET_STS_ERROR ;
824 RAISE FND_API.G_EXC_ERROR;
825 when FND_API.G_EXC_UNEXPECTED_ERROR then
826 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
827 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
828 when others then
829 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
830 BIS_UTILITIES_PVT.Add_Error_Message
831 ( p_error_msg_id => SQLCODE
832 , p_error_description => SQLERRM
833 , p_error_proc_name => G_PKG_NAME||'.Value_ID_Conversion'
834 );
835 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
836
837 end Value_ID_Conversion;
838 --
839 END BIS_BUSINESS_PLAN_PVT;