[Home] [Help]
PACKAGE BODY: APPS.PSB_CREATE_BR_PVT
Source
1 PACKAGE BODY PSB_Create_BR_Pvt AS
2 /* $Header: PSBVCBRB.pls 115.26 2003/04/21 20:10:28 srawat ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_Create_BR_Pvt';
5
6
7 /*--------------------------- Global variables -----------------------------*/
8
9 -- The flag determines whether to print debug information or not.
10 g_debug_flag VARCHAR2(1) := 'N' ;
11
12 --
13 -- WHO columns variables
14 --
15
16 g_current_date DATE := sysdate;
17 g_current_user_id NUMBER := FND_GLOBAL.USER_ID;
18 g_current_login_id NUMBER := FND_GLOBAL.LOGIN_ID;
19 g_budget_by_position NUMBER;
20
21 /*----------------------- End Global variables -----------------------------*/
22
23
24 /* ---------------------- Private Routine prototypes -----------------------*/
25
26
27 PROCEDURE Insert_BR_Lines_Pvt
28 (
29 p_budget_revision_id IN NUMBER,
30 p_budget_revision_acct_line_id IN NUMBER,
31 p_freeze_flag IN VARCHAR2,
32 p_view_line_flag IN VARCHAR2,
33 p_last_update_date IN DATE,
34 p_last_updated_by IN NUMBER,
35 p_last_update_login IN NUMBER,
36 p_created_by IN NUMBER,
37 p_creation_date IN DATE,
38 p_return_status OUT NOCOPY VARCHAR2
39 ) ;
40
41 PROCEDURE Insert_BR_Pos_Lines_Pvt
42 (
43 p_budget_revision_id IN NUMBER,
44 p_budget_revision_pos_line_id IN NUMBER,
45 p_freeze_flag IN VARCHAR2,
46 p_view_line_flag IN VARCHAR2,
47 p_last_update_date IN DATE,
48 p_last_updated_by IN NUMBER,
49 p_last_update_login IN NUMBER,
50 p_created_by IN NUMBER,
51 p_creation_date IN DATE,
52 p_return_status OUT NOCOPY VARCHAR2
53 ) ;
54
55 PROCEDURE debug
56 (
57 p_message IN VARCHAR2
58 ) ;
59
60 /* ------------------ End Private Routines prototypes ----------------------*/
61
62 /*===========================================================================+
63 | PROCEDURE Enforce_BR_Concurrency |
64 +===========================================================================*/
65 --
66 -- The budget revision operations may affect one or more budget revisions
67 -- depending on the type of the operation. This API locks all the relevent
68 -- budget revisions required for a budget revision operation.
69 --
70 PROCEDURE Enforce_BR_Concurrency
71 (
72 p_api_version IN NUMBER ,
73 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
74 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
75 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
76 p_return_status OUT NOCOPY VARCHAR2 ,
77 p_msg_count OUT NOCOPY NUMBER ,
78 p_msg_data OUT NOCOPY VARCHAR2 ,
79 --
80 p_budget_revision_id IN NUMBER,
81 p_parent_or_child_mode IN VARCHAR2 ,
82 p_maintenance_mode IN VARCHAR2 := 'MAINTENANCE'
83 )
84 IS
85 --
86 l_api_name CONSTANT VARCHAR2(30) := 'Enforce_BR_Concurrency' ;
87 l_api_version CONSTANT NUMBER := 1.0 ;
88 --
89 l_return_status VARCHAR2(1) ;
90 l_msg_count NUMBER ;
91 l_msg_data VARCHAR2(2000) ;
92 --
93 l_budget_revision_tab PSB_Create_BR_Pvt.Budget_Revision_Tbl_Type ;
94 --
95 BEGIN
96 --
97 SAVEPOINT Enforce_BR_Concurrency_Pvt ;
98 --
99 IF NOT FND_API.Compatible_API_Call ( l_api_version,
100 p_api_version,
101 l_api_name,
102 G_PKG_NAME )
103 THEN
104 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
105 END IF;
106 --
107
108 IF FND_API.To_Boolean ( p_init_msg_list ) THEN
109 FND_MSG_PUB.initialize ;
110 END IF;
111 --
112 p_return_status := FND_API.G_RET_STS_SUCCESS ;
113 --
114
115 --
116 -- First lock the current budget revision p_budget_revision_id
117 --
118 PSB_Concurrency_Control_Pub.Enforce_Concurrency_Control
119 (
120 p_api_version => 1.0 ,
121 p_init_msg_list => FND_API.G_FALSE ,
122 p_validation_level => FND_API.G_VALID_LEVEL_NONE ,
123 p_return_status => l_return_status ,
124 p_msg_count => l_msg_count ,
125 p_msg_data => l_msg_data ,
126 --
127 p_concurrency_class => nvl(p_maintenance_mode,'MAINTENANCE'),
128 p_concurrency_entity_name => 'BUDGET_REVISION',
129 p_concurrency_entity_id => p_budget_revision_id
130 );
131 --
132 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
133 RAISE FND_API.G_EXC_ERROR ;
134 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
135 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
136 END IF;
137
138 --
139 -- Find parent or child budget revisions depending on p_parent_or_child_mode
140 -- parameter.
141 --
142 IF p_parent_or_child_mode = 'PARENT' THEN
143 --
144 PSB_Create_BR_Pvt.Find_Parent_Budget_Revisions
145 (
146 p_api_version => 1.0 ,
147 p_init_msg_list => FND_API.G_FALSE,
148 p_commit => FND_API.G_FALSE,
149 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
150 p_return_status => l_return_status,
151 p_msg_count => l_msg_count,
152 p_msg_data => l_msg_data,
153 --
154 p_budget_revision_id => p_budget_revision_id,
155 p_budget_revision_tbl => l_budget_revision_tab
156 );
157 --
158 ELSIF p_parent_or_child_mode = 'CHILD' THEN
159 --
160 PSB_Create_BR_Pvt.Find_Child_Budget_Revisions
161 (
162 p_api_version => 1.0 ,
163 p_init_msg_list => FND_API.G_FALSE,
164 p_commit => FND_API.G_FALSE,
165 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
166 p_return_status => l_return_status,
167 p_msg_count => l_msg_count,
168 p_msg_data => l_msg_data,
169 --
170 p_budget_revision_id => p_budget_revision_id,
171 p_budget_revision_tbl => l_budget_revision_tab
172 );
173 --
174 END IF ;
175
176 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
177 RAISE FND_API.G_EXC_ERROR ;
178 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
179 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
180 END IF;
181
182 FOR i IN 1..l_budget_revision_tab.COUNT
183 LOOP
184 --
185 -- Lock parent or child budget revisions retrieved in the previous step.
186 --
187 PSB_Concurrency_Control_Pub.Enforce_Concurrency_Control
188 (
189 p_api_version => 1.0 ,
190 p_init_msg_list => FND_API.G_FALSE ,
191 p_validation_level => FND_API.G_VALID_LEVEL_NONE ,
192 p_return_status => l_return_status ,
193 p_msg_count => l_msg_count ,
194 p_msg_data => l_msg_data ,
195 --
196 p_concurrency_class => nvl(p_maintenance_mode,'MAINTENANCE'),
197 p_concurrency_entity_name => 'BUDGET_REVISION',
198 p_concurrency_entity_id => l_budget_revision_tab(i)
199 );
200 --
201 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
202 RAISE FND_API.G_EXC_ERROR ;
203 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
204 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
205 END IF;
206
207 --
208 END LOOP ;
209
210 --
211 IF FND_API.To_Boolean ( p_commit ) THEN
212 COMMIT WORK;
213 END IF;
214 --
215 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
216 p_data => p_msg_data );
217 --
218 EXCEPTION
219 --
220 WHEN FND_API.G_EXC_ERROR THEN
221 --
222 ROLLBACK TO Enforce_BR_Concurrency_Pvt ;
223 p_return_status := FND_API.G_RET_STS_ERROR;
224 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
225 p_data => p_msg_data );
226 --
227 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
228 --
229 ROLLBACK TO Enforce_BR_Concurrency_Pvt ;
230 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
231 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
232 p_data => p_msg_data );
233 --
234 WHEN OTHERS THEN
235 --
236 ROLLBACK TO Enforce_BR_Concurrency_Pvt ;
237 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
238 --
239 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
240 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
241 l_api_name);
242 END if;
243 --
244 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
245 p_data => p_msg_data );
246 --
247 END Enforce_BR_Concurrency ;
248 /*---------------------------------------------------------------------------*/
249
250 /*===========================================================================+
251 | PROCEDURE Check_BR_Ops_Concurrency |
252 +===========================================================================*/
253 --
254 -- The API checks for the operation type to invoke appropriate concurrency
255 -- control routines.
256 --
257 PROCEDURE Check_BR_Ops_Concurrency
258 (
259 p_api_version IN NUMBER ,
260 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
261 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
262 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
263 p_return_status OUT NOCOPY VARCHAR2 ,
264 p_msg_count OUT NOCOPY NUMBER ,
265 p_msg_data OUT NOCOPY VARCHAR2 ,
266 --
267 p_budget_revision_id IN NUMBER,
268 p_operation_type IN VARCHAR2
269 )
270 IS
271 --
272 l_api_name CONSTANT VARCHAR2(30) := 'Check_BR_Ops_Concurrency';
273 l_api_version CONSTANT NUMBER := 1.0 ;
274 --
275 l_return_status VARCHAR2(1) ;
276 l_msg_count NUMBER ;
277 l_msg_data VARCHAR2(2000) ;
278 --
279 BEGIN
280 --
281 SAVEPOINT Check_BR_Ops_Concurrency_Pvt ;
282 --
283 IF NOT FND_API.Compatible_API_Call ( l_api_version,
284 p_api_version,
285 l_api_name,
286 G_PKG_NAME )
287 THEN
288 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
289 END IF;
290 --
291
292 IF FND_API.To_Boolean ( p_init_msg_list ) THEN
293 FND_MSG_PUB.initialize ;
294 END IF;
295 --
296 p_return_status := FND_API.G_RET_STS_SUCCESS ;
297 --
298
299 IF p_operation_type IN ('FREEZE_REVISION', 'SUBMIT_REVISION' ) THEN
300 --
301 -- Lock in 'CHILD' mode as the child Revisions also need to be frozen.
302 --
303 PSB_Create_BR_PVT.Enforce_BR_Concurrency
304 (
305 p_api_version => 1.0,
306 p_init_msg_list => FND_API.G_FALSE ,
307 p_commit => FND_API.G_FALSE ,
308 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
309 p_return_status => l_return_status,
310 p_msg_count => l_msg_count,
311 p_msg_data => l_msg_data,
312 --
313 p_budget_revision_id => p_budget_revision_id ,
314 p_parent_or_child_mode => 'CHILD' ,
315 p_maintenance_mode => 'MAINTENANCE'
316 );
317 --
318 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
319 RAISE FND_API.G_EXC_ERROR ;
320 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
321 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
322 END IF;
323 --
324 ELSIF p_operation_type IN ('UNFREEZE_REVISION' ) THEN
325 --
326 -- Lock only the current Revision.
327 --
328 PSB_Concurrency_Control_Pub.Enforce_Concurrency_Control
329 (
330 p_api_version => 1.0,
331 p_init_msg_list => FND_API.G_FALSE,
332 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
333 p_return_status => l_return_status,
334 p_msg_count => l_msg_count,
335 p_msg_data => l_msg_data,
336 --
337 p_concurrency_class => 'MAINTENANCE' ,
338 p_concurrency_entity_name => 'BUDGET_REVISION' ,
339 p_concurrency_entity_id => p_budget_revision_id
340 );
341 --
342 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
343 RAISE FND_API.G_EXC_ERROR ;
344 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
345 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
346 END IF;
347 --
348 ELSE
349 --
350 Fnd_Message.Set_Name ('PSB', 'PSB_INVALID_ARGUMENT') ;
351 Fnd_Message.Set_Token('ROUTINE', l_api_name ) ;
352 FND_MSG_PUB.Add;
353 RAISE FND_API.G_EXC_ERROR ;
354 --
355 END IF ;
356
357 --
358 IF FND_API.To_Boolean ( p_commit ) THEN
359 COMMIT WORK;
360 END IF;
361 --
362 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
363 p_data => p_msg_data );
364 --
365 EXCEPTION
366 --
367 WHEN FND_API.G_EXC_ERROR THEN
368 --
369 ROLLBACK TO Check_BR_Ops_Concurrency_Pvt ;
370 p_return_status := FND_API.G_RET_STS_ERROR;
371 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
372 p_data => p_msg_data );
373 --
374 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
375 --
376 ROLLBACK TO Check_BR_Ops_Concurrency_Pvt ;
377 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
378 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
379 p_data => p_msg_data );
380 --
381 WHEN OTHERS THEN
382 --
383 ROLLBACK TO Check_BR_Ops_Concurrency_Pvt ;
384 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
385 --
386 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
387 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
388 l_api_name );
389 END if;
390 --
391 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
392 p_data => p_msg_data );
393 --
394 END Check_BR_Ops_Concurrency ;
395
396 /*===========================================================================+
397 | PROCEDURE Create_Budget_Revision |
398 +===========================================================================*/
399 --
400 -- This overloaded API creates a new budget revision for a given budget group.
401 --
402 PROCEDURE Create_Budget_Revision
403 (
404 p_api_version IN NUMBER ,
405 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
406 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
407 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
408 p_return_status OUT NOCOPY VARCHAR2 ,
409 p_msg_count OUT NOCOPY NUMBER ,
410 p_msg_data OUT NOCOPY VARCHAR2 ,
411 --
412 p_budget_revision_id IN NUMBER,
413 p_revision_option_flag IN VARCHAR2,
414 p_budget_group_id IN NUMBER,
415 p_budget_revision_id_out OUT NOCOPY NUMBER
416 )
417
418 IS
419 --
420 l_api_name CONSTANT VARCHAR2(30) := 'Create_Budget_Revision';
421 l_api_version CONSTANT NUMBER := 1.0 ;
422 --
423 l_return_status VARCHAR2(1) ;
424 l_msg_count NUMBER ;
425 l_msg_data VARCHAR2(2000) ;
426 --
427 l_revision_justification psb_budget_revisions.justification%TYPE ;
428 l_main_budget_group_id psb_budget_revisions.budget_group_id%TYPE ;
429 l_main_budget_group_name psb_budget_groups.name%TYPE ;
430 l_new_budget_revision_id psb_budget_revisions.budget_revision_id%TYPE ;
431 l_global_budget_revision_id psb_budget_revisions.budget_revision_id%TYPE ;
432 --
433 l_tmp_char VARCHAR2(1) ;
434 l_freeze_flag VARCHAR2(1) ;
435 --
436 CURSOR l_budget_revisions_csr IS
437 SELECT *
438 FROM psb_budget_revisions
439 WHERE budget_revision_id = p_budget_revision_id;
440
441 CURSOR l_budget_by_position_csr IS
442 SELECT count(*)
443 FROM psb_budget_revision_pos_lines lines,
444 psb_budget_revisions rev
445 WHERE rev.budget_revision_id = p_budget_revision_id
446 AND rev.budget_revision_id = lines.budget_revision_id;
447
448 --CURSOR l_seq IS
449 -- SELECT psb_budget_revisions_s.nextval budget_revision_id
450 -- FROM DUAL;
451 --
452 l_br_row_type l_budget_revisions_csr%ROWTYPE ;
453 --
454 BEGIN
455 --
456 SAVEPOINT Create_Budget_Revision_Pvt ;
457 --
458 IF NOT FND_API.Compatible_API_Call ( l_api_version,
459 p_api_version,
460 l_api_name,
461 G_PKG_NAME )
462 THEN
463 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
464 END IF;
465 --
466
467 IF FND_API.To_Boolean ( p_init_msg_list ) THEN
468 FND_MSG_PUB.initialize ;
469 END IF;
470 --
471 p_return_status := FND_API.G_RET_STS_SUCCESS ;
472 --
473 --
474 -- Validating p_budget_group_id.
475 --
476 SELECT '1' INTO l_tmp_char
477 FROM psb_budget_groups
478 WHERE budget_group_id = p_budget_group_id ;
479
480 --
481 -- Finding the worksheet information.
482 --
483 OPEN l_budget_revisions_csr;
484
485 FETCH l_budget_revisions_csr INTO l_br_row_type;
486
487 IF ( l_budget_revisions_csr%NOTFOUND ) THEN
488 --
489 Fnd_Message.Set_Name ('PSB','PSB_INVALID_BUDGET_REVISION_ID') ;
490 Fnd_Message.Set_Token('ROUTINE', l_api_name ) ;
491 FND_MSG_PUB.Add;
492 RAISE FND_API.G_EXC_ERROR ;
493 END IF ;
494
495 -- l_budget_by_position defines whether budget revision contains positions
496 -- or not.
497
498 l_main_budget_group_id := l_br_row_type.budget_group_id ;
499 --
500 -- Finding the main budget group name.
501 --
502 SELECT name INTO l_main_budget_group_name
503 FROM psb_budget_groups
504 WHERE budget_group_id = l_main_budget_group_id;
505
506 --
507 -- Get translated messages for the new budget revision.
508 --
509 Fnd_Message.Set_Name ( 'PSB', 'PSB_BUDGET_REVISION_INFO') ;
510 Fnd_Message.Set_Token( 'BUDGET_REVISION_ID', p_budget_revision_id ) ;
511 Fnd_Message.Set_Token( 'BUDGET_GROUP_NAME', l_main_budget_group_name ) ;
512 l_revision_justification := Fnd_Message.Get ;
513
514 --
515 -- Find global budget revision related information,
516 -- use by Create_Budget_Revision API.
517 --
518 IF NVL(l_br_row_type.global_budget_revision, 'N') = 'Y' THEN
519 l_global_budget_revision_id := p_budget_revision_id;
520 ELSE
521 l_global_budget_revision_id := l_br_row_type.global_budget_revision_id;
522 END IF ;
523
524 --
525 -- Create the new budget revision in psb_budget_revisions table.
526 --
527 if ((p_revision_option_flag is not null ) and (p_revision_option_flag = 'N'))
528 then
529 l_freeze_flag := 'Y';
530 else
531 l_freeze_flag := l_br_row_type.freeze_flag;
532 end if;
533
534
535 PSB_Budget_Revisions_Pvt.Create_Budget_Revision
536 (
537 p_api_version => 1.0 ,
538 p_init_msg_list => FND_API.G_FALSE,
539 p_commit => FND_API.G_FALSE,
540 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
541 p_return_status => l_return_status,
542 p_msg_count => l_msg_count,
543 p_msg_data => l_msg_data ,
544 --
545 p_justification => l_br_row_type.justification,
546 p_budget_group_id => p_budget_group_id,
547 p_gl_budget_set_id => l_br_row_type.gl_budget_set_id,
548 p_hr_budget_id => l_br_row_type.hr_budget_id,
549 p_from_gl_period_name => l_br_row_type.from_gl_period_name,
550 p_to_gl_period_name => l_br_row_type.to_gl_period_name,
551 p_currency_code => l_br_row_type.currency_code,
552 p_effective_start_date => l_br_row_type.effective_start_date,
553 p_effective_end_date => l_br_row_type.effective_end_date,
554 p_budget_revision_type => l_br_row_type.budget_revision_type,
555 p_transaction_type => l_br_row_type.transaction_type,
556 p_permanent_revision => l_br_row_type.permanent_revision,
557 p_revise_by_position => l_br_row_type.revise_by_position,
558 p_balance_type => l_br_row_type.balance_type,
559 p_requestor => l_br_row_type.requestor,
560 p_parameter_set_id => l_br_row_type.parameter_set_id,
561 p_constraint_set_id => l_br_row_type.constraint_set_id,
562 p_submission_date => l_br_row_type.submission_date,
563 p_submission_status => l_br_row_type.submission_status,
564 p_approval_override_by => l_br_row_type.approval_override_by,
565 p_freeze_flag => l_freeze_flag,
566 p_base_line_revision => l_br_row_type.base_line_revision,
567 p_global_budget_revision => 'N',
568 p_global_budget_revision_id => l_global_budget_revision_id,
569 p_attribute1 => l_br_row_type.attribute1,
570 p_attribute2 => l_br_row_type.attribute2,
571 p_attribute3 => l_br_row_type.attribute3,
572 p_attribute4 => l_br_row_type.attribute4,
573 p_attribute5 => l_br_row_type.attribute5,
574 p_attribute6 => l_br_row_type.attribute6,
575 p_attribute7 => l_br_row_type.attribute7,
576 p_attribute8 => l_br_row_type.attribute8,
577 p_attribute9 => l_br_row_type.attribute9,
578 p_attribute10 => l_br_row_type.attribute10,
579 p_attribute11 => l_br_row_type.attribute11,
580 p_attribute12 => l_br_row_type.attribute12,
581 p_attribute13 => l_br_row_type.attribute13,
582 p_attribute14 => l_br_row_type.attribute14,
583 p_attribute15 => l_br_row_type.attribute15,
584 p_attribute16 => l_br_row_type.attribute16,
585 p_attribute17 => l_br_row_type.attribute17,
586 p_attribute18 => l_br_row_type.attribute18,
587 p_attribute19 => l_br_row_type.attribute19,
588 p_attribute20 => l_br_row_type.attribute20,
589 p_attribute21 => l_br_row_type.attribute21,
590 p_attribute22 => l_br_row_type.attribute22,
591 p_attribute23 => l_br_row_type.attribute23,
592 p_attribute24 => l_br_row_type.attribute24,
593 p_attribute25 => l_br_row_type.attribute25,
594 p_attribute26 => l_br_row_type.attribute26,
595 p_attribute27 => l_br_row_type.attribute27,
596 p_attribute28 => l_br_row_type.attribute28,
597 p_attribute29 => l_br_row_type.attribute29,
598 p_attribute30 => l_br_row_type.attribute30,
599 p_context => l_br_row_type.context,
600 p_budget_revision_id => l_new_budget_revision_id
601 );
602
603 --
604 CLOSE l_budget_revisions_csr ;
605 --
606 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
607 RAISE FND_API.G_EXC_ERROR ;
608 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
609 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
610 END IF;
611 --
612 --
613 -- This LOOP gets all the account_line_id for the new budget revision
614 -- which will be used to maintain psb_budget_revision_lines table.
615 --
616 FOR l_lines_rec IN
617 (
618 SELECT lines.*
619 FROM psb_budget_revision_lines lines,
620 psb_budget_revision_accounts acct
621 WHERE lines.budget_revision_id = p_budget_revision_id
622 AND lines.budget_revision_acct_line_id =
623 acct.budget_revision_acct_line_id
624 AND acct.budget_group_id in
625 ( SELECT budget_group_id
626 FROM psb_budget_groups
627 WHERE budget_group_type = 'R'
628 AND effective_start_date <= sysdate
629 AND ((effective_end_date IS NULL)
630 OR
631 (effective_end_date >= sysdate))
632 START WITH budget_group_id = p_budget_group_id
633 CONNECT BY PRIOR budget_group_id = parent_budget_group_id
634 )
635 )
636 LOOP
637
638 debug('Budget Revision Account line id '||
639 l_lines_rec.budget_revision_acct_line_id);
640 --
641 -- Put the account line ids in the psb_budget_revision_lines table
642 -- for the new budget revision.
643 --
644 Insert_BR_Lines_Pvt
645 ( p_budget_revision_id => l_new_budget_revision_id,
646 p_budget_revision_acct_line_id =>
647 l_lines_rec.budget_revision_acct_line_id,
648 p_freeze_flag => l_lines_rec.freeze_flag,
649 p_view_line_flag => l_lines_rec.view_line_flag,
650 p_last_update_date => g_current_date,
651 p_last_updated_by => g_current_user_id,
652 p_last_update_login => g_current_login_id,
653 p_created_by => g_current_user_id,
654 p_creation_date => g_current_date,
655 p_return_status => l_return_status
656 ) ;
657 --
658 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
659 RAISE FND_API.G_EXC_ERROR ;
660 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
661 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
662 END IF;
663 --
664 END LOOP;
665 --
666 -- Maintain psb_budget_revision_pos_lines if budget revision
667 -- contains positions.(This also means the budget revision includes
668 -- position budgeting.)
669 --
670 OPEN l_budget_by_position_csr;
671 FETCH l_budget_by_position_csr INTO g_budget_by_position;
672 CLOSE l_budget_by_position_csr;
673
674 IF g_budget_by_position IS NOT NULL THEN
675
676 debug('l_budget_by_position loop');
677
678 --
679 -- This loop gets all the position_line_id for the new worksheet which will
680 -- be used to maintain psb_ws_lines_positions table.
681 --
682 FOR l_lines_rec IN
683 (
684 SELECT lines.*
685 FROM psb_budget_revision_pos_lines lines ,
686 psb_budget_revision_positions pos
687 WHERE lines.budget_revision_id = p_budget_revision_id
688 AND lines.budget_revision_pos_line_id
689 = pos.budget_revision_pos_line_id
690 AND pos.budget_group_id in
691 (
692 SELECT bg.budget_group_id
693 FROM psb_budget_groups bg
694 WHERE budget_group_type = 'R'
695 AND effective_start_date <= sysdate
696 AND ((effective_end_date IS NULL)
697 OR
698 (effective_end_date >= sysdate))
699 START WITH bg.budget_group_id = p_budget_group_id
700 CONNECT BY PRIOR bg.budget_group_id =
701 bg.parent_budget_group_id
702 )
703 )
704 LOOP
705 --
706 debug('Budget Revision Position line id '||
707 l_lines_rec.budget_revision_pos_line_id);
708
709 -- Put the budget_revision_pos_line_id in the
710 -- psb_budget_revision_pos_lines
711 -- table for the new budget revision.
712
713 Insert_BR_Pos_Lines_Pvt
714 ( p_budget_revision_id => l_new_budget_revision_id,
715 p_budget_revision_pos_line_id =>
716 l_lines_rec.budget_revision_pos_line_id,
717 p_freeze_flag => l_lines_rec.freeze_flag,
718 p_view_line_flag => l_lines_rec.view_line_flag,
719 p_last_update_date => g_current_date,
720 p_last_updated_by => g_current_user_id,
721 p_last_update_login => g_current_login_id,
722 p_created_by => g_current_user_id,
723 p_creation_date => g_current_date,
724 p_return_status => l_return_status
725 );
726 --
727 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
728 RAISE FND_API.G_EXC_ERROR ;
729 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
730 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
731 END IF;
732 --
733 --
734 --
735 END LOOP;
736
737 END IF; -- end of check for g_budget_by_position
738
739 p_budget_revision_id_out := l_new_budget_revision_id;
740
741 --
742 IF FND_API.To_Boolean ( p_commit ) THEN
743 COMMIT WORK;
744 END IF;
745 --
746 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
747 p_data => p_msg_data );
748 --
749 EXCEPTION
750 --
751 WHEN FND_API.G_EXC_ERROR THEN
752 --
753 ROLLBACK TO Create_Budget_Revision_Pvt ;
754 p_return_status := FND_API.G_RET_STS_ERROR;
755 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
756 p_data => p_msg_data );
757 --
758 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
759 --
760 ROLLBACK TO Create_Budget_Revision_Pvt ;
761 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
762 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
763 p_data => p_msg_data );
764 --
765 WHEN OTHERS THEN
766 --
767 IF ( l_budget_revisions_csr%ISOPEN ) THEN
768 CLOSE l_budget_revisions_csr ;
769 END IF ;
770 --
771 ROLLBACK TO Create_Budget_Revision_Pvt ;
772 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
773 --
774 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
775 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
776 l_api_name);
777 END if;
778 --
779 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
780 p_data => p_msg_data );
781 --
782
783 END Create_Budget_Revision;
784 /*---------------------------------------------------------------------------*/
785
786 /*===========================================================================+
787 | PROCEDURE Freeze_Budget_Revision |
788 +===========================================================================*/
789 --
790 -- This API freezes a given budget revision.
791 --
792 PROCEDURE Freeze_Budget_Revision
793 (
794 p_api_version IN NUMBER ,
795 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
796 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
797 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
798 p_return_status OUT NOCOPY VARCHAR2 ,
799 p_msg_count OUT NOCOPY NUMBER ,
800 p_msg_data OUT NOCOPY VARCHAR2 ,
801 --
802 p_budget_revision_id IN NUMBER,
803 p_freeze_flag IN VARCHAR2
804 )
805 IS
806 --
807 l_api_name CONSTANT VARCHAR2(30) := 'Freeze_Budget_Revision' ;
808 l_api_version CONSTANT NUMBER := 1.0 ;
809 --
810 l_return_status VARCHAR2(1) ;
811 l_msg_count NUMBER ;
812 l_msg_data VARCHAR2(2000) ;
813 --
814 l_parent_budget_revision_id psb_budget_revisions.budget_revision_id%TYPE ;
815 l_budget_revision_id psb_budget_revisions.budget_revision_id%TYPE ;
816 l_parent_freeze_flag psb_budget_revisions.freeze_flag%TYPE ;
817 --
818 BEGIN
819 --
820 SAVEPOINT Freeze_Budget_Revision_Pvt ;
821 --
822 IF NOT FND_API.Compatible_API_Call ( l_api_version,
823 p_api_version,
824 l_api_name,
825 G_PKG_NAME )
826 THEN
827 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
828 END IF;
829 --
830
831 IF FND_API.To_Boolean ( p_init_msg_list ) THEN
832 FND_MSG_PUB.initialize ;
833 END IF;
834 --
835 p_return_status := FND_API.G_RET_STS_SUCCESS ;
836 --
837
838
839 --
840 -- A budget revision can only be unfrozen if the parent budget revision is not
841 -- frozen ( p_freeze_flag = 'N' identifies an unfreeze operation).
842 --
843 IF p_freeze_flag = 'N' THEN
844
845 -- Find parent budget revision, if exists.
846
847 Find_Parent_Budget_Revision
848 (
849 p_api_version => 1.0 ,
850 p_init_msg_list => FND_API.G_FALSE,
851 p_commit => FND_API.G_FALSE,
852 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
853 p_return_status => l_return_status,
854 p_msg_count => l_msg_count,
855 p_msg_data => l_msg_data ,
856 --
857 p_budget_revision_id => p_budget_revision_id ,
858 p_budget_revision_id_OUT => l_parent_budget_revision_id
859 ) ;
860 --
861 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
862 RAISE FND_API.G_EXC_ERROR ;
863 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
864 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
865 END IF;
866
867 -- Check the freeze_flag for the parent worksheet.
868 IF l_parent_budget_revision_id <> 0 THEN
869
870 SELECT NVL(freeze_flag, 'N') INTO l_parent_freeze_flag
871 FROM psb_budget_revisions
872 WHERE budget_revision_id = l_parent_budget_revision_id;
873
874 IF l_parent_freeze_flag = 'Y' THEN
875
876 Fnd_Message.Set_Name('PSB','PSB_CANNOT_UNFREEZE_REVISION') ;
877 FND_MSG_PUB.Add;
878 RAISE FND_API.G_EXC_ERROR ;
879
880 END IF ;
881
882 END IF ;
883
884 END IF ; -- For p_freeze_flag = 'N' condition.
885
886 --
887 -- Update freeze_flag in psb_budget_revisions.
888 --
889 l_budget_revision_id := p_budget_revision_id;
890
891
892 /* Code split into 2 conditions if and elsif to make request_id
893 null in case of unfreeze. Changed by Siva on 07/17/00 to resolve bug 1303434 */
894 IF p_freeze_flag = 'Y' THEN
895 PSB_Budget_Revisions_Pvt.Create_Budget_Revision
896 (
897 p_api_version => 1.0 ,
898 p_init_msg_list => FND_API.G_FALSE,
899 p_commit => FND_API.G_FALSE,
900 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
901 p_return_status => l_return_status,
902 p_msg_count => l_msg_count,
903 p_msg_data => l_msg_data ,
904 --
905 p_budget_revision_id => l_budget_revision_id,
906 p_freeze_flag => p_freeze_flag
907 );
908 ELSIF p_freeze_flag = 'N' THEN
909
910 PSB_Budget_Revisions_Pvt.Create_Budget_Revision
911 (
912 p_api_version => 1.0 ,
913 p_init_msg_list => FND_API.G_FALSE,
914 p_commit => FND_API.G_FALSE,
915 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
916 p_return_status => l_return_status,
917 p_msg_count => l_msg_count,
918 p_msg_data => l_msg_data ,
919 --
920 p_budget_revision_id => l_budget_revision_id,
921 p_freeze_flag => p_freeze_flag,
922 p_request_id => NULL
923 );
924
925 END IF;
926 --
927 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
928 RAISE FND_API.G_EXC_ERROR ;
929 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
930 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
931 END IF;
932 --
933 -- Update freeze_flag in psb_budget_revision_lines.
934
935 UPDATE psb_budget_revision_lines
936 SET freeze_flag = p_freeze_flag
937 WHERE budget_revision_id = p_budget_revision_id;
938
939 IF g_budget_by_position IS NOT NULL THEN
940 --
941 -- Update freeze_flag in psb_budget_revision_pos_lines.
942 --
943 UPDATE psb_budget_revision_pos_lines
944 SET freeze_flag = p_freeze_flag
945 WHERE budget_revision_id = p_budget_revision_id;
946 --
947 END IF;
948
949 IF FND_API.To_Boolean ( p_commit ) THEN
950 COMMIT WORK;
951 END IF;
952 --
953 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
954 p_data => p_msg_data );
955 --
956 EXCEPTION
957 --
958 WHEN FND_API.G_EXC_ERROR THEN
959 --
960 ROLLBACK TO Freeze_Budget_Revision_Pvt ;
961 p_return_status := FND_API.G_RET_STS_ERROR;
962 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
963 p_data => p_msg_data );
964 --
965 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
966 --
967 ROLLBACK TO Freeze_Budget_Revision_Pvt ;
968 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
969 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
970 p_data => p_msg_data );
971 --
972 WHEN OTHERS THEN
973 --
974 ROLLBACK TO Freeze_Budget_Revision_Pvt ;
975 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
976 --
977 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
978 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
979 l_api_name);
980 END if;
981 --
982 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
983 p_data => p_msg_data );
984 --
985 END Freeze_Budget_Revision;
986 /*---------------------------------------------------------------------------*/
987
988 /*===========================================================================+
989 | PROCEDURE Find_Parent_Budget_Revision |
990 +===========================================================================*/
991 --
992 -- The API finds parent budget revision of a given budget revision.
993 --
994 PROCEDURE Find_Parent_Budget_Revision
995 (
996 p_api_version IN NUMBER,
997 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
998 p_commit IN VARCHAR2 := FND_API.G_FALSE,
999 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1000 p_return_status OUT NOCOPY VARCHAR2,
1001 p_msg_count OUT NOCOPY NUMBER,
1002 p_msg_data OUT NOCOPY VARCHAR2,
1003 --
1004 p_budget_revision_id IN NUMBER,
1005 p_budget_revision_id_OUT OUT NOCOPY NUMBER
1006 )
1007 IS
1008 --
1009 l_api_name CONSTANT VARCHAR2(30)
1010 := 'Find_Parent_Budget_Revision';
1011 l_api_version CONSTANT NUMBER := 1.0;
1012 l_return_status VARCHAR2(1);
1013 l_msg_count NUMBER;
1014 l_msg_data VARCHAR2(2000);
1015 --
1016 l_global_budget_revision_id NUMBER;
1017 l_global_budget_revision VARCHAR2(1);
1018 l_budget_group_id NUMBER;
1019 l_global_budget_group_id NUMBER;
1020 --
1021 l_parent_budget_group_id NUMBER;
1022 --
1023 BEGIN
1024 --
1025 SAVEPOINT Find_Parent_Revision_Pvt ;
1026 --
1027 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1028 p_api_version,
1029 l_api_name,
1030 G_PKG_NAME )
1031 THEN
1032 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1033 END IF;
1034 --
1035
1036 IF FND_API.To_Boolean ( p_init_msg_list ) THEN
1037 FND_MSG_PUB.initialize ;
1038 END IF;
1039 --
1040 p_return_status := FND_API.G_RET_STS_SUCCESS ;
1041 p_budget_revision_id_OUT := -99 ;
1042 --
1043 --
1044 -- Finding the budget revision information.
1045 --
1046 SELECT br.budget_group_id,
1047 br.global_budget_revision_id,
1048 br.global_budget_revision,
1049 bg.parent_budget_group_id
1050 INTO
1051 l_budget_group_id,
1052 l_global_budget_revision_id,
1053 l_global_budget_revision,
1054 l_parent_budget_group_id
1055 FROM psb_budget_revisions br,
1056 psb_budget_groups bg
1057 WHERE br.budget_revision_id = p_budget_revision_id
1058 AND br.budget_group_id = bg.budget_group_id ;
1059
1060 IF (l_global_budget_revision = 'Y') OR
1061 (l_parent_budget_group_id IS NULL) THEN
1062 --
1063 p_budget_revision_id_OUT := 0 ;
1064 RETURN ;
1065 END IF ;
1066
1067 --
1068 -- Find global budget_group_id for the global budget revision.
1069 --
1070 SELECT budget_group_id INTO l_global_budget_group_id
1071 FROM psb_budget_revisions
1072 WHERE budget_revision_id = l_global_budget_revision_id;
1073
1074 --
1075 -- If parent budget group for the current budget revision is same as the budget
1076 -- group for the global budget revision, then the global budget revision
1077 -- is the parent budget revision for the given budget revision.
1078 --
1079 IF l_global_budget_group_id = l_parent_budget_group_id THEN
1080 p_budget_revision_id_OUT := l_global_budget_revision_id ;
1081 RETURN ;
1082 END IF ;
1083 --
1084 -- Get the desired parent budget revision at the
1085 -- l_parent_budget_group_id level.
1086 --
1087 BEGIN
1088
1089 --
1090 -- New way to find if a revision has been created for a budget group.
1091 -- ( Bug#2832148 )
1092 --
1093 SELECT budget_revision_id INTO p_budget_revision_id_OUT
1094 FROM psb_budget_revisions
1095 WHERE global_budget_revision_id = l_global_budget_revision_id
1096 AND budget_group_id = l_parent_budget_group_id ;
1097
1098 /*
1099 SELECT DISTINCT child_worksheet_id INTO p_budget_revision_id_OUT
1100 FROM psb_ws_distribution_details details, psb_ws_distributions distr
1101 WHERE distr.worksheet_id = p_budget_revision_id
1102 AND distr.distribution_option_flag = 'R'
1103 AND details.global_worksheet_id = l_global_budget_revision_id
1104 AND details.child_budget_group_id = l_parent_budget_group_id;
1105 */
1106
1107 --
1108 --
1109 EXCEPTION
1110 WHEN no_data_found THEN
1111 --
1112 -- Cannot use FND_API.G_MISS_NUM as budget_revision_id is NUMBER(20) only.
1113 --
1114 p_budget_revision_id_OUT := 0 ;
1115 --
1116 END ;
1117
1118 --
1119 IF FND_API.To_Boolean ( p_commit ) THEN
1120 COMMIT WORK;
1121 END IF;
1122 --
1123 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1124 p_data => p_msg_data );
1125 --
1126 EXCEPTION
1127 --
1128 WHEN FND_API.G_EXC_ERROR THEN
1129 --
1130 ROLLBACK TO Find_Parent_Revision_Pvt ;
1131 p_return_status := FND_API.G_RET_STS_ERROR;
1132 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1133 p_data => p_msg_data );
1134 --
1135 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1136 --
1137 ROLLBACK TO Find_Parent_Revision_Pvt ;
1138 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1139 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1140 p_data => p_msg_data );
1141 --
1142 WHEN OTHERS THEN
1143 --
1144 ROLLBACK TO Find_Parent_Revision_Pvt ;
1145 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1146 --
1147 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1148 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
1149 l_api_name);
1150 END if;
1151 --
1152 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1153 p_data => p_msg_data );
1154 --
1155 END Find_Parent_Budget_Revision;
1156 /*---------------------------------------------------------------------------*/
1157 /*===========================================================================+
1158 | PROCEDURE Find_Parent_Budget_Revisions |
1159 +===========================================================================*/
1160 --
1161 -- The API finds parent budget revisions of a given budget revision
1162 -- in a PL/SQL table.
1163 --
1164 PROCEDURE Find_Parent_Budget_Revisions
1165 (
1166 p_api_version IN NUMBER,
1167 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1168 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1169 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1170 p_return_status OUT NOCOPY VARCHAR2,
1171 p_msg_count OUT NOCOPY NUMBER,
1172 p_msg_data OUT NOCOPY VARCHAR2,
1173 --
1174 p_budget_revision_id IN NUMBER,
1175 p_budget_revision_tbl IN OUT NOCOPY Budget_Revision_Tbl_Type
1176 )
1177 IS
1178 --
1179 l_api_name CONSTANT VARCHAR2(30) :=
1180 'Find_Parent_Budget_Revisions';
1181 l_api_version CONSTANT NUMBER := 1.0;
1182 l_return_status VARCHAR2(1);
1183 l_msg_count NUMBER;
1184 l_msg_data VARCHAR2(2000);
1185 --
1186 l_current_budget_revision_id NUMBER;
1187 l_parent_budget_revision_id NUMBER;
1188 --
1189 l_count NUMBER;
1190 --
1191 BEGIN
1192 --
1193 SAVEPOINT Find_Parent_Revisions_Pvt;
1194 --
1195 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1196 p_api_version,
1197 l_api_name,
1198 G_PKG_NAME )
1199 THEN
1200 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1201 END IF;
1202 --
1203
1204 IF FND_API.To_Boolean ( p_init_msg_list ) THEN
1205 FND_MSG_PUB.initialize ;
1206 END IF;
1207 --
1208 p_return_status := FND_API.G_RET_STS_SUCCESS ;
1209 --
1210
1211 --
1212 -- Perform initialization
1213 --
1214 l_count := 0 ;
1215 p_budget_revision_tbl.DELETE;
1216 l_current_budget_revision_id := p_budget_revision_id ;
1217
1218 LOOP
1219 --
1220 -- Find the parent budget revision for the current budget revision.
1221 --
1222 PSB_Create_BR_Pvt.Find_Parent_Budget_Revision
1223 (
1224 p_api_version => 1.0,
1225 p_init_msg_list => FND_API.G_FALSE,
1226 p_commit => FND_API.G_FALSE,
1227 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1228 p_return_status => l_return_status,
1229 p_msg_count => l_msg_count,
1230 p_msg_data => l_msg_data,
1231 --
1232 p_budget_revision_id => l_current_budget_revision_id,
1233 p_budget_revision_id_OUT => l_parent_budget_revision_id
1234 );
1235 --
1236 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1237 RAISE FND_API.G_EXC_ERROR;
1238 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1239 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1240 END IF;
1241 --
1242 IF l_parent_budget_revision_id = 0 THEN
1243
1244 -- It means all the parent budget revisions has been retrieved.
1245 EXIT ;
1246 --
1247 ELSE
1248 --
1249 -- Insert the budget revision in the table.
1250 --
1251 l_count := l_count + 1;
1252 p_budget_revision_tbl(l_count) := l_parent_budget_revision_id;
1253 l_current_budget_revision_id := l_parent_budget_revision_id;
1254 END IF ;
1255 --
1256 END LOOP ;
1257
1258 --
1259 IF FND_API.To_Boolean ( p_commit ) THEN
1260 COMMIT WORK;
1261 END IF;
1262 --
1263 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1264 p_data => p_msg_data );
1265 --
1266 EXCEPTION
1267 --
1268 WHEN FND_API.G_EXC_ERROR THEN
1269 --
1270 ROLLBACK TO Find_Parent_Revisions_Pvt;
1271 p_return_status := FND_API.G_RET_STS_ERROR;
1272 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1273 p_data => p_msg_data );
1274 --
1275 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1276 --
1277 ROLLBACK TO Find_Parent_Revisions_Pvt;
1278 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1279 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1280 p_data => p_msg_data );
1281 --
1282 WHEN OTHERS THEN
1283 --
1284 ROLLBACK TO Find_Parent_Revisions_Pvt;
1285 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1286 --
1287 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1288 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
1289 l_api_name);
1290 END if;
1291 --
1292 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1293 p_data => p_msg_data );
1294 --
1295 END Find_Parent_Budget_Revisions;
1296 /*---------------------------------------------------------------------------*/
1297
1298 /*===========================================================================+
1299 | PROCEDURE Find_Child_Budget_Revisions |
1300 +===========================================================================*/
1301 --
1302 -- The API finds all the child budget revisions of a
1303 -- budget revision in a PL/SQL table.
1304 --
1305 PROCEDURE Find_Child_Budget_Revisions
1306 (
1307 p_api_version IN NUMBER,
1308 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1309 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1310 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1311 p_return_status OUT NOCOPY VARCHAR2,
1312 p_msg_count OUT NOCOPY NUMBER,
1313 p_msg_data OUT NOCOPY VARCHAR2,
1314 --
1315 p_budget_revision_id IN NUMBER,
1316 p_budget_revision_tbl IN OUT NOCOPY Budget_Revision_Tbl_Type
1317 )
1318 IS
1319 --
1320 l_api_name CONSTANT VARCHAR2(30)
1321 := 'Find_Child_Budget_Revisions';
1322 l_api_version CONSTANT NUMBER := 1.0 ;
1323 l_return_status VARCHAR2(1);
1324 l_msg_count NUMBER;
1325 l_msg_data VARCHAR2(2000);
1326 --
1327 l_child_budget_revision_id NUMBER;
1328 l_global_budget_revision_id NUMBER;
1329 l_global_budget_revision VARCHAR2(1);
1330 l_budget_group_id NUMBER;
1331 l_budget_calendar_id NUMBER;
1332 --
1333 l_count NUMBER ;
1334 --
1335 BEGIN
1336 --
1337 SAVEPOINT Find_Child_Revisions_Pvt;
1338 --
1339 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1340 p_api_version,
1341 l_api_name,
1342 G_PKG_NAME )
1343 THEN
1344 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1345 END IF;
1346 --
1347
1348 IF FND_API.To_Boolean ( p_init_msg_list ) THEN
1349 FND_MSG_PUB.initialize ;
1350 END IF;
1351 --
1352 p_return_status := FND_API.G_RET_STS_SUCCESS ;
1353 --
1354
1355 --
1356 -- Perform initialization
1357 --
1358 l_count := 0 ;
1359 p_budget_revision_tbl.DELETE ;
1360 --
1361
1362 --
1363 -- Get budget revision information for the p_budget_revision_id .
1364 --
1365 SELECT budget_group_id ,
1366 global_budget_revision_id ,
1367 NVL( global_budget_revision , 'N' )
1368 INTO
1369 l_budget_group_id ,
1370 l_global_budget_revision_id ,
1371 l_global_budget_revision
1372 FROM psb_budget_revisions
1373 WHERE budget_revision_id = p_budget_revision_id ;
1374
1375 IF l_global_budget_revision = 'Y' THEN
1376 l_global_budget_revision_id := p_budget_revision_id ;
1377 END IF ;
1378 --
1379 l_count := 0 ;
1380
1381 --
1382 -- Process all the lower level budget groups to fine budget revisions.
1383 --
1384 FOR l_budget_group_rec IN
1385 (
1386 SELECT budget_group_id
1387 FROM psb_budget_groups
1388 WHERE budget_group_type = 'R'
1389 AND effective_start_date <= sysdate
1390 AND ((effective_end_date IS NULL)
1391 OR
1392 (effective_end_date >= sysdate))
1393 START WITH budget_group_id = l_budget_group_id
1394 CONNECT BY PRIOR budget_group_id = parent_budget_group_id
1395 )
1396 LOOP
1397
1398 --
1399 -- The hierarchial query will also return the l_budget_group_id.
1400 -- Do not consider it.
1401 --
1402 IF l_budget_group_rec.budget_group_id <> l_budget_group_id THEN
1403
1404 l_child_budget_revision_id := NULL ;
1405
1406 --
1407 -- Get the budget_revision_id at the current budget_group_level.
1408 --
1409 BEGIN
1410
1411 --
1412 -- New way to find if a revision has been created for a budget group.
1413 -- ( Bug#2832148 )
1414 --
1415 SELECT budget_revision_id INTO l_child_budget_revision_id
1416 FROM psb_budget_revisions
1417 WHERE global_budget_revision_id = l_global_budget_revision_id
1418 AND budget_group_id = l_budget_group_rec.budget_group_id ;
1419
1420 /*
1421 SELECT child_worksheet_id INTO l_child_budget_revision_id
1422 FROM psb_ws_distribution_details details, psb_ws_distributions distr
1423 WHERE distr.worksheet_id = p_budget_revision_id
1424 AND distr.distribution_option_flag = 'R'
1425 AND details.global_worksheet_id = l_global_budget_revision_id
1426 AND details.child_budget_group_id =
1427 l_budget_group_rec.budget_group_id
1428 AND ROWNUM < 2 ;
1429 */
1430
1431 EXCEPTION
1432 WHEN no_data_found THEN
1433 --
1434 -- Means the budget revision has not been distributed to this level.
1435 -- Simply ignore it.
1436 --
1437 NULL ;
1438 END ;
1439
1440 debug( 'BG id ' || l_budget_group_rec.budget_group_id ||
1441 ' BR id ' || l_child_budget_revision_id ) ;
1442
1443 --
1444 -- Insert the budget revision in the p_budget_revision_tbl table
1445 --
1446 IF l_child_budget_revision_id IS NOT NULL THEN
1447 l_count := l_count + 1 ;
1448 p_budget_revision_tbl( l_count ) := l_child_budget_revision_id ;
1449 END IF ;
1450
1451 END IF ;
1452
1453 END LOOP ;
1454
1455 --
1456 IF FND_API.To_Boolean ( p_commit ) THEN
1457 COMMIT WORK;
1458 END IF;
1459 --
1460 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1461 p_data => p_msg_data );
1462 --
1463 EXCEPTION
1464 --
1465 WHEN FND_API.G_EXC_ERROR THEN
1466 --
1467 ROLLBACK TO Find_Child_Revisions_Pvt ;
1468 p_return_status := FND_API.G_RET_STS_ERROR;
1469 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1470 p_data => p_msg_data );
1471 --
1472 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1473 --
1474 ROLLBACK TO Find_Child_Revisions_Pvt ;
1475 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1476 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1477 p_data => p_msg_data );
1478 --
1479 WHEN OTHERS THEN
1480 --
1481 ROLLBACK TO Find_Child_Revisions_Pvt ;
1482 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1483 --
1484 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1485 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
1486 l_api_name);
1487 END if;
1488 --
1489 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1490 p_data => p_msg_data );
1491 --
1492 END Find_Child_Budget_Revisions ;
1493 /*---------------------------------------------------------------------------*/
1494 /*===========================================================================+
1495 | PROCEDURE Update_Target_Budget_Revision |
1496 +===========================================================================*/
1497 --
1498 -- The API takes 2 budget revisions, source and target. It updates target
1499 -- budget revision by adding new account or position lines if they are their
1500 -- in the source budget revision and not in the target budget revision.
1501 -- It also updates the budget revision submission related columns in
1502 -- the source budget revision.
1503 --
1504 PROCEDURE Update_Target_Budget_Revision
1505 (
1506 p_api_version IN NUMBER,
1507 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1508 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1509 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1510 p_return_status OUT NOCOPY VARCHAR2,
1511 p_msg_count OUT NOCOPY NUMBER,
1512 p_msg_data OUT NOCOPY VARCHAR2,
1513 --
1514 p_source_budget_revision_id IN NUMBER,
1515 p_revision_option_flag IN VARCHAR2,
1516 p_target_budget_revision_id IN NUMBER
1517 )
1518 IS
1519 --
1520 l_api_name CONSTANT VARCHAR2(30)
1521 := 'Update_Target_Budget Revision' ;
1522 l_api_version CONSTANT NUMBER := 1.0 ;
1523 --
1524 l_return_status VARCHAR2(1) ;
1525 l_msg_count NUMBER ;
1526 l_msg_data VARCHAR2(2000) ;
1527 --
1528 l_source_budget_group_id NUMBER;
1529 l_source_local_copy_flag VARCHAR2(1);
1530 l_source_global_budget_rev_id NUMBER;
1531 l_source_global_budget_rev VARCHAR(1);
1532 l_freeze_flag VARCHAR(1);
1533 l_source_budget_by_position NUMBER;
1534 --
1535 l_target_budget_group_id NUMBER;
1536 l_target_budget_revision_id NUMBER;
1537 l_target_global_budget_rev_id NUMBER;
1538 --
1539 l_budget_calendar_id NUMBER;
1540 l_br_lines_rec psb_budget_revision_lines%ROWTYPE;
1541 l_br_lines_pos_rec psb_budget_revision_pos_lines%ROWTYPE;
1542
1543 CURSOR l_budget_by_position_csr IS
1544 SELECT count(*)
1545 FROM psb_budget_revision_pos_lines lines,
1546 psb_budget_revisions rev
1547 WHERE rev.budget_revision_id = p_source_budget_revision_id
1548 AND rev.budget_revision_id = lines.budget_revision_id;
1549
1550 BEGIN
1551 --
1552 SAVEPOINT Update_Target_Revision_Pvt ;
1553 --
1554 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1555 p_api_version,
1556 l_api_name,
1557 G_PKG_NAME )
1558 THEN
1559 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1560 END IF;
1561 --
1562
1563 IF FND_API.To_Boolean ( p_init_msg_list ) THEN
1564 FND_MSG_PUB.initialize ;
1565 END IF;
1566 --
1567 p_return_status := FND_API.G_RET_STS_SUCCESS ;
1568 --
1569 --
1570 -- Update budget revision submission related columns in the target
1571 -- budget revision.
1572 --
1573 l_target_budget_revision_id := p_target_budget_revision_id;
1574
1575 if ((p_revision_option_flag is not null ) and (p_revision_option_flag = 'N')) then
1576 l_freeze_flag := 'Y';
1577 end if;
1578
1579 PSB_Budget_Revisions_Pvt.Create_Budget_Revision
1580 (
1581 p_api_version => 1.0 ,
1582 p_init_msg_list => FND_API.G_FALSE,
1583 p_commit => FND_API.G_FALSE,
1584 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
1585 p_return_status => l_return_status,
1586 p_msg_count => l_msg_count,
1587 p_msg_data => l_msg_data ,
1588 --
1589 p_freeze_flag => l_freeze_flag,
1590 p_budget_revision_id => l_target_budget_revision_id
1591 );
1592 --
1593 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1594 RAISE FND_API.G_EXC_ERROR ;
1595 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1596 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1597 END IF;
1598
1599 --
1600 -- Find the source budget revision information.
1601 --
1602 SELECT budget_group_id,
1603 global_budget_revision_id,
1604 NVL( global_budget_revision, 'N' )
1605 INTO
1606 l_source_budget_group_id,
1607 l_source_global_budget_rev_id,
1608 l_source_global_budget_rev
1609 FROM psb_budget_revisions
1610 WHERE budget_revision_id = p_source_budget_revision_id ;
1611
1612 IF l_source_global_budget_rev = 'Y' THEN
1613 l_source_global_budget_rev_id := p_source_budget_revision_id ;
1614 END IF ;
1615
1616 --
1617 -- Find the target budget revision information. The target budget revision
1618 -- will never be the top budget revision i.e. global_budget_revision
1619 -- is always 'N'.
1620 --
1621 SELECT budget_group_id,
1622 global_budget_revision_id
1623 INTO
1624 l_target_budget_group_id,
1625 l_target_global_budget_rev_id
1626 FROM psb_budget_revisions
1627 WHERE budget_revision_id = p_target_budget_revision_id ;
1628 --
1629 --
1630 --
1631 -- Find account_line_id to be inserted into target budget revision.
1632 -- ( The hierarchial query will select lines falling in the subtreee, the
1633 -- target budget revision belongs. We will not consider other lines. )
1634 --
1635 FOR l_account_line_id_rec IN
1636 (
1637 SELECT lines.budget_revision_acct_line_id
1638 FROM psb_budget_revision_lines lines,
1639 psb_budget_revision_accounts acct
1640 WHERE lines.budget_revision_id = p_source_budget_revision_id
1641 AND lines.budget_revision_acct_line_id
1642 =
1643 acct.budget_revision_acct_line_id
1644 AND acct.budget_group_id in
1645 ( SELECT budget_group_id
1646 FROM psb_budget_groups
1647 WHERE budget_group_type = 'R'
1648 AND effective_start_date <= sysdate
1649 AND ((effective_end_date IS NULL)
1650 OR
1651 (effective_end_date >= sysdate))
1652 START WITH budget_group_id = l_target_budget_group_id
1653 CONNECT BY PRIOR budget_group_id = parent_budget_group_id
1654 )
1655 MINUS
1656 SELECT lines.budget_revision_acct_line_id
1657 FROM psb_budget_revision_lines lines
1658 WHERE budget_revision_id = p_target_budget_revision_id
1659 )
1660 LOOP
1661
1662 SELECT * INTO l_br_lines_rec
1663 FROM psb_budget_revision_lines
1664 WHERE budget_revision_id = p_source_budget_revision_id
1665 AND budget_revision_acct_line_id =
1666 l_account_line_id_rec.budget_revision_acct_line_id ;
1667
1668 --
1669 -- Each account_line_id found is the account_line_id missing in the
1670 -- target budget revision. Add the account_line_id to the
1671 -- target budget revision.
1672 --
1673 Insert_BR_Lines_Pvt
1674 (
1675 p_budget_revision_id => p_target_budget_revision_id,
1676 p_budget_revision_acct_line_id =>
1677 l_br_lines_rec.budget_revision_acct_line_id,
1678 p_freeze_flag => l_br_lines_rec.freeze_flag,
1679 p_view_line_flag => l_br_lines_rec.view_line_flag,
1680 p_last_update_date => g_current_date,
1681 p_last_updated_by => g_current_user_id,
1682 p_last_update_login => g_current_login_id,
1683 p_created_by => g_current_user_id,
1684 p_creation_date => g_current_date,
1685 p_return_status => l_return_status
1686 ) ;
1687 --
1688 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1689 RAISE FND_API.G_EXC_ERROR ;
1690 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1691 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1692 END IF;
1693 --
1694 END LOOP ;
1695
1696 --
1697 -- Find budget_revision_pos_line_id to be inserted into
1698 -- target budget revision.
1699 -- ( The hierarchial query will select lines falling in the sub-tree,
1700 -- the target budget revision belongs. We will not consider other lines. )
1701 --
1702 OPEN l_budget_by_position_csr;
1703 FETCH l_budget_by_position_csr INTO l_source_budget_by_position;
1704 CLOSE l_budget_by_position_csr;
1705
1706 IF l_source_budget_by_position IS NOT NULL THEN
1707 --
1708 FOR l_lines_pos_rec IN
1709 (
1710 SELECT lines.budget_revision_pos_line_id
1711 FROM psb_budget_revision_pos_lines lines ,
1712 psb_budget_revision_positions pos
1713 WHERE lines.budget_revision_id = p_source_budget_revision_id
1714 AND lines.budget_revision_pos_line_id
1715 = pos.budget_revision_pos_line_id
1716 AND pos.budget_group_id in
1717 (
1718 SELECT bg.budget_group_id
1719 FROM psb_budget_groups bg
1720 WHERE bg.budget_group_type = 'R'
1721 AND effective_start_date <= sysdate
1722 AND ((effective_end_date IS NULL)
1723 OR
1724 (effective_end_date >= sysdate))
1725 START WITH bg.budget_group_id = l_target_budget_group_id
1726 CONNECT BY PRIOR bg.budget_group_id =
1727 bg.parent_budget_group_id
1728 )
1729 MINUS
1730 SELECT budget_revision_pos_line_id
1731 FROM psb_budget_revision_pos_lines
1732 WHERE budget_revision_id = p_target_budget_revision_id
1733 )
1734 LOOP
1735
1736 SELECT * INTO l_br_lines_pos_rec
1737 FROM psb_budget_revision_pos_lines
1738 WHERE budget_revision_id = p_source_budget_revision_id
1739 AND budget_revision_pos_line_id
1740 = l_lines_pos_rec.budget_revision_pos_line_id ;
1741
1742 --
1743 -- Each budget_revision_pos_line_id found is the one missing in the target
1744 -- budget revision. Add it to the target budget revision.
1745 --
1746 Insert_BR_Pos_Lines_Pvt
1747 ( p_budget_revision_id => p_target_budget_revision_id,
1748 p_budget_revision_pos_line_id =>
1749 l_br_lines_pos_rec.budget_revision_pos_line_id,
1750 p_freeze_flag => l_br_lines_pos_rec.freeze_flag,
1751 p_view_line_flag => l_br_lines_pos_rec.view_line_flag,
1752 p_last_update_date => g_current_date,
1753 p_last_updated_by => g_current_user_id,
1754 p_last_update_login => g_current_login_id,
1755 p_created_by => g_current_user_id,
1756 p_creation_date => g_current_date,
1757 p_return_status => l_return_status
1758 );
1759 --
1760 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1761 RAISE FND_API.G_EXC_ERROR ;
1762 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1763 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1764 END IF;
1765 --
1766 END LOOP ;
1767
1768 END IF ;
1769
1770 --
1771 IF FND_API.To_Boolean ( p_commit ) THEN
1772 COMMIT WORK;
1773 END IF;
1774 --
1775 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1776 p_data => p_msg_data );
1777 --
1778 EXCEPTION
1779 --
1780 WHEN FND_API.G_EXC_ERROR THEN
1781 --
1782 ROLLBACK TO Update_Target_Revision_Pvt ;
1783 p_return_status := FND_API.G_RET_STS_ERROR;
1784 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1785 p_data => p_msg_data );
1786 --
1787 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1788 --
1789 ROLLBACK TO Update_Target_Revision_Pvt ;
1790 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1791 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1792 p_data => p_msg_data );
1793 --
1794 WHEN OTHERS THEN
1795 --
1796 ROLLBACK TO Update_Target_Revision_Pvt ;
1797 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1798 --
1799
1800 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1801 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
1802 l_api_name);
1803 END if;
1804 --
1805 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1806 p_data => p_msg_data );
1807 --
1808 END Update_Target_Budget_Revision ;
1809 /*---------------------------------------------------------------------------*/
1810
1811 /*===========================================================================+
1812 | PROCEDURE Insert_BR_Lines_Pvt ( Private ) |
1813 +===========================================================================*/
1814 --
1815 -- The private procedure inserts a new record in psb_ws_lines table.
1816 --
1817 PROCEDURE Insert_BR_Lines_Pvt
1818 (
1819 p_budget_revision_id IN NUMBER,
1820 p_budget_revision_acct_line_id IN NUMBER,
1821 p_freeze_flag IN VARCHAR2,
1822 p_view_line_flag IN VARCHAR2,
1823 p_last_update_date IN DATE,
1824 p_last_updated_by IN NUMBER,
1825 p_last_update_login IN NUMBER,
1826 p_created_by IN NUMBER,
1827 p_creation_date IN DATE,
1828 p_return_status OUT NOCOPY VARCHAR2
1829 )
1830 IS
1831 --
1832 l_api_name CONSTANT VARCHAR2(30) := 'Insert_BR_Lines_Pvt' ;
1833 --
1834 BEGIN
1835 --
1836 p_return_status := FND_API.G_RET_STS_SUCCESS ;
1837 --
1838 update psb_budget_revision_lines
1839 set freeze_flag = p_freeze_flag,
1840 view_line_flag = p_view_line_flag,
1841 last_update_date = g_current_date,
1842 last_updated_by = g_current_user_id,
1843 last_update_login = g_current_login_id
1844 where budget_revision_acct_line_id = p_budget_revision_acct_line_id
1845 and budget_revision_id = p_budget_revision_id;
1846
1847 IF SQL%NOTFOUND THEN
1848
1849 INSERT INTO psb_budget_revision_lines
1850 (
1851 budget_revision_id,
1852 budget_revision_acct_line_id,
1853 freeze_flag,
1854 view_line_flag,
1855 last_update_date,
1856 last_updated_by,
1857 last_update_login,
1858 created_by,
1859 creation_date
1860 )
1861 VALUES
1862 (
1863 p_budget_revision_id,
1864 p_budget_revision_acct_line_id,
1865 p_freeze_flag,
1866 p_view_line_flag,
1867 g_current_date,
1868 g_current_user_id,
1869 g_current_login_id,
1870 g_current_user_id,
1871 g_current_date
1872 );
1873
1874 END IF;
1875 --
1876
1877 EXCEPTION
1878 --
1879 WHEN OTHERS THEN
1880 --
1881 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1882 --
1883 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1884 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
1885 l_api_name );
1886 END IF;
1887 --
1888 END Insert_BR_Lines_Pvt ;
1889 /*---------------------------------------------------------------------------*/
1890
1891 /*===========================================================================+
1892 | PROCEDURE Insert_BR_Pos_Lines_Pvt ( Private ) |
1893 +===========================================================================*/
1894 --
1895 -- The private procedure inserts a new record in
1896 -- psb_budget_revision_pos_lines table.
1897 --
1898 PROCEDURE Insert_BR_Pos_Lines_Pvt
1899 (
1900 p_budget_revision_id IN NUMBER,
1901 p_budget_revision_pos_line_id IN NUMBER,
1902 p_freeze_flag IN VARCHAR2,
1903 p_view_line_flag IN VARCHAR2,
1904 p_last_update_date IN DATE,
1905 p_last_updated_by IN NUMBER,
1906 p_last_update_login IN NUMBER,
1907 p_created_by IN NUMBER,
1908 p_creation_date IN DATE,
1909 p_return_status OUT NOCOPY VARCHAR2
1910 )
1911 IS
1912 --
1913 l_api_name CONSTANT VARCHAR2(30) := 'Insert_BR_Pos_Lines_Pvt' ;
1914 --
1915 BEGIN
1916 --
1917 p_return_status := FND_API.G_RET_STS_SUCCESS ;
1918 --
1919 update psb_budget_revision_pos_lines
1920 set freeze_flag = p_freeze_flag,
1921 view_line_flag = p_view_line_flag,
1922 last_update_date = g_current_date,
1923 last_updated_by = g_current_user_id,
1924 last_update_login = g_current_login_id
1925 where budget_revision_pos_line_id = p_budget_revision_pos_line_id
1926 and budget_revision_id = p_budget_revision_id;
1927
1928 IF SQL%NOTFOUND THEN
1929
1930 INSERT INTO psb_budget_revision_pos_lines
1931 (
1932 budget_revision_id,
1933 budget_revision_pos_line_id,
1934 freeze_flag,
1935 view_line_flag,
1936 last_update_date,
1937 last_updated_by,
1938 last_update_login,
1939 created_by,
1940 creation_date
1941 )
1942 VALUES
1943 (
1944 p_budget_revision_id,
1945 p_budget_revision_pos_line_id,
1946 p_freeze_flag,
1947 p_view_line_flag,
1948 g_current_date,
1949 g_current_user_id,
1950 g_current_login_id,
1951 g_current_user_id,
1952 g_current_date
1953 );
1954
1955 END IF;
1956 --
1957
1958 EXCEPTION
1959 --
1960 WHEN OTHERS THEN
1961 --
1962 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1963 --
1964 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1965 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
1966 l_api_name );
1967 END IF;
1968 --
1969 END Insert_BR_Pos_Lines_Pvt ;
1970 /*---------------------------------------------------------------------------*/
1971 /*===========================================================================+
1972 | PROCEDURE pd (Private) |
1973 +===========================================================================*/
1974 --
1975 -- Private procedure to print debug info. The name is tried to keep as
1976 -- short as possible for better documentaion.
1977 --
1978 PROCEDURE debug
1979 (
1980 p_message IN VARCHAR2
1981 )
1982 IS
1983 --
1984 BEGIN
1985
1986 IF g_debug_flag = 'Y' THEN
1987 null;
1988 -- DBMS_OUTPUT.Put_Line(p_message) ;
1989 END IF;
1990
1991 END debug ;
1992 /*---------------------------------------------------------------------------*/
1993
1994
1995 END PSB_Create_BR_Pvt ;