DBA Data[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 ;