[Home] [Help]
PACKAGE BODY: APPS.PSB_WORKFLOW_PVT
Source
1 PACKAGE BODY PSB_Workflow_Pvt AS
2 /* $Header: PSBWKFLB.pls 115.29 2003/04/21 20:08:36 srawat ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PSB_Workflow_Pvt';
5
6 g_chr10 CONSTANT VARCHAR2(1) := FND_GLOBAL.Newline;
7
8 G_DBUG VARCHAR2(32767):= '!!';
9
10 /*--------------------------- Global variables -------------------------*/
11 -- The flag determines whether to print debug information or not.
12 g_debug_flag VARCHAR2(1) := 'N' ;
13
14
15 --
16 -- WHO columns variables. Instantiation will be done by local API as
17 -- these APIs are concurrent program execution files.
18 --
19 g_current_date DATE ;
20 g_current_user_id NUMBER ;
21 g_current_login_id NUMBER ;
22 --
23 g_distribution_instructions
24 psb_ws_distributions.distribution_instructions%TYPE ;
25
26 /*-------------------- End Global variables ---------------------------*/
27
28
29
30 /*--------------------- Private Variables --------------------------*/
31 PROCEDURE Create_Worksheet
32 (
33 p_worksheet_id IN NUMBER ,
34 p_global_worksheet_id IN NUMBER ,
35 p_budget_group_id IN NUMBER ,
36 p_distribution_id IN NUMBER ,
37 p_created_worksheet_id OUT NOCOPY NUMBER,
38 p_return_status OUT NOCOPY VARCHAR2
39 );
40
41 PROCEDURE Distribute_Budget_Revision
42 (
43 p_budget_revision_id IN NUMBER ,
44 p_global_budget_revision_id IN NUMBER ,
45 p_budget_group_id IN NUMBER ,
46 p_distribution_id IN NUMBER ,
47 p_created_budget_revision_id OUT NOCOPY NUMBER,
48 p_return_status OUT NOCOPY VARCHAR2
49 );
50
51 PROCEDURE Add_Debug_Info
52 (
53 p_string IN VARCHAR2
54 );
55
56 PROCEDURE pd
57 (
58 p_message IN VARCHAR2
59 ) ;
60
61
62 /*---------------------------------------------------------------------------*/
63
64
65
66 /*===========================================================================+
67 | PROCEDURE Distribute_WS |
68 +==========================================================================*/
69 --
70 -- The API sets up context information for the workflow distribute process
71 -- for a given worksheet and starts the workflow process
72 -- 'Distribute Worksheet' (It is basically an Concurrent Program API).
73 --
74 PROCEDURE Distribute_WS
75 (
76 errbuf OUT NOCOPY VARCHAR2,
77 retcode OUT NOCOPY VARCHAR2,
78 --
79 p_distribution_id IN NUMBER,
80 p_submitter_id IN NUMBER,
81 p_export_name IN VARCHAR2
82 )
83 IS
84 --
85 l_api_name CONSTANT VARCHAR2(30) := 'Distribute_WS' ;
86 l_api_version CONSTANT NUMBER := 1.0 ;
87 --
88 l_error_api_name VARCHAR2(2000);
89 l_return_status VARCHAR2(1) ;
90 l_msg_count NUMBER ;
91 l_msg_data VARCHAR2(2000) ;
92 --
93 l_worksheet_id psb_ws_distributions.worksheet_id%TYPE ;
94 l_created_worksheet_id psb_worksheets.worksheet_id%TYPE ;
95 l_budget_calendar_id psb_worksheets.budget_calendar_id%TYPE ;
96 l_global_worksheet_id psb_worksheets.global_worksheet_id%TYPE ;
97 l_distribution_rule_id psb_ws_distributions.distribution_rule_id%TYPE ;
98 l_distribution_option_flag
99 psb_ws_distributions.distribution_option_flag%TYPE ;
100 --
101 l_export_name VARCHAR2(80);
102 --
103 BEGIN
104
105 --
106 -- Re-instatiate global variable as the concurrent manager does not do it
107 -- for the same session.
108 --
109 g_current_date := SYSDATE ;
110 g_current_user_id := NVL( Fnd_Global.User_Id , 0) ;
111 g_current_login_id := NVL( Fnd_Global.Login_Id , 0) ;
112 --
113
114 SAVEPOINT Distribute_WS_Pvt ;
115
116 --
117 -- Get distribution information.
118 --
119 SELECT worksheet_id ,
120 distribution_rule_id ,
121 distribution_instructions ,
122 distribution_option_flag
123 INTO
124 l_worksheet_id ,
125 l_distribution_rule_id ,
126 g_distribution_instructions ,
127 l_distribution_option_flag
128 FROM psb_ws_distributions
129 WHERE distribution_id = p_distribution_id ;
130
131 --
132 -- The Budget Revision functionality introduced the new flag called
133 -- distribution_option_flag which determines whether the distribution is
134 -- being done for a worksheet or a budget revision document. Currently this
135 -- API will take care of worksheet distribution only.
136 -- ( "W" means Worksheet, "R" means Budget Revision for the flag )
137 --
138 IF NVL( l_distribution_option_flag, 'W') = 'R' THEN
139 RETURN ;
140 END IF;
141
142 --
143 -- Find global_worksheet_id for the l_worksheet_id. If global_worksheet_id
144 -- is NULL, then the l_worksheet_id is the global worksheet.
145 --
146 SELECT NVL( global_worksheet_id, l_worksheet_id ) ,
147 budget_calendar_id
148 INTO
149 l_global_worksheet_id ,
150 l_budget_calendar_id
151 FROM psb_worksheets
152 WHERE worksheet_id = l_worksheet_id ;
153
154 --
155 -- Get budget calendar related info to find all the budget groups down in
156 -- the current hierarchy.
157 --
158 IF NVL(PSB_WS_Acct1.g_budget_calendar_id, -99) <> l_budget_calendar_id
159 THEN
160 --
161 PSB_WS_Acct1.Cache_Budget_Calendar
162 (
163 p_return_status => l_return_status,
164 p_budget_calendar_id => l_budget_calendar_id
165 );
166 --
167 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
168 FND_MSG_PUB.Add_Exc_Msg( 'PSB_WS_Acct1', 'Cache_Budget_Calendar') ;
169 RAISE FND_API.G_EXC_ERROR ;
170 END IF ;
171 --
172 END IF ;
173
174 FOR l_budget_groups_rec IN
175 (
176 SELECT a.budget_group_id ,b.short_name short_name,
177 NVL( distribute_all_level_flag, 'N') distribute_all_level_flag,
178 NVL( download_flag, 'N') download_flag,
179 NVL( download_all_level_flag, 'N') download_all_level_flag
180 FROM psb_ws_distribution_rule_lines a, psb_budget_groups b
181 WHERE distribution_rule_id = l_distribution_rule_id
182 AND a.budget_group_id = b.budget_group_id
183 )
184 LOOP
185
186 Add_Debug_Info('download flag is ' || l_budget_groups_rec.download_flag );
187 Add_Debug_Info('download ALL LEVEL flag is ' ||
188 l_budget_groups_rec.download_all_level_flag) ;
189 --
190 IF l_budget_groups_rec.distribute_all_level_flag = 'N' THEN
191 --
192 -- Create worksheet for the given budget group only.
193 --
194 Create_Worksheet
195 ( p_worksheet_id => l_worksheet_id,
196 p_global_worksheet_id => l_global_worksheet_id,
197 p_budget_group_id => l_budget_groups_rec.budget_group_id,
198 p_distribution_id => p_distribution_id,
199 p_created_worksheet_id => l_created_worksheet_id,
200 p_return_status => l_return_status
201 );
202 --
203 Add_Debug_Info('1- WS for BG '||
204 to_char(l_budget_groups_rec.budget_group_id) ||
205 ' Status '||l_return_status) ;
206
207 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
208 RAISE FND_API.G_EXC_ERROR ;
209 END IF ;
210 --
211
212 -- download flag
213
214 IF l_budget_groups_rec.download_flag = 'Y' AND
215 p_export_name IS NOT NULL THEN
216
217 l_export_name := p_export_name || '_' || l_budget_groups_rec.short_name;
218
219 Add_Debug_Info('TOP Export name is ' || l_export_name) ;
220
221 PSB_EXCEL_PVT.Move_To_Interface
222 (
223 p_api_version => l_api_version ,
224 p_init_msg_list => FND_API.G_FALSE ,
225 p_commit => FND_API.G_FALSE ,
226 p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
227 p_return_status => l_return_status ,
228 p_msg_count => l_msg_count ,
229 p_msg_data => l_msg_data ,
230 --
231 p_export_name => l_export_name,
232 p_worksheet_id => l_created_worksheet_id
233 );
234
235 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
236 RAISE FND_API.G_EXC_ERROR ;
237 END IF ;
238 --
239
240 Add_Debug_Info('After download ' || ' Status ' || l_return_status) ;
241
242 END IF;
243
244
245
246
247 ELSIF l_budget_groups_rec.distribute_all_level_flag = 'Y' THEN
248
249 --
250 -- Create worksheet for the given budget group and its child budget groups.
251 --
252 FOR l_child_bgs_rec IN
253 (
254 SELECT budget_group_id, short_name short_name
255 FROM psb_budget_groups
256 WHERE budget_group_type = 'R'
257 AND effective_start_date <= PSB_WS_Acct1.g_startdate_pp
258 AND ((effective_end_date IS NULL)
259 OR
260 (effective_end_date >= PSB_WS_Acct1.g_enddate_cy))
261 START WITH budget_group_id = l_budget_groups_rec.budget_group_id
262 CONNECT BY PRIOR budget_group_id = parent_budget_group_id
263 )
264 LOOP
265 --
266 Create_Worksheet
267 ( p_worksheet_id => l_worksheet_id,
268 p_global_worksheet_id => l_global_worksheet_id,
269 p_budget_group_id => l_child_bgs_rec.budget_group_id,
270 p_distribution_id => p_distribution_id,
271 p_created_worksheet_id => l_created_worksheet_id,
272 p_return_status => l_return_status
273 );
274 --
275
276 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
277 RAISE FND_API.G_EXC_ERROR ;
278 END IF ;
279 --
280
281 IF l_budget_groups_rec.download_flag = 'Y' AND
282 p_export_name IS NOT NULL AND
283 (
284 ( l_budget_groups_rec.download_all_level_flag = 'Y') OR
285 ( l_budget_groups_rec.download_all_level_flag = 'N' AND
286 l_budget_groups_rec.budget_group_id =
287 l_child_bgs_rec.budget_group_id )
288 ) THEN
289
290 l_export_name := p_export_name || '_' || l_child_bgs_rec.short_name ;
291 Add_Debug_Info('Child Export name is ' || l_export_name ) ;
292
293
294 PSB_EXCEL_PVT. Move_To_Interface
295 (
296 p_api_version => l_api_version ,
297 p_init_msg_list => FND_API.G_FALSE ,
298 p_commit => FND_API.G_FALSE ,
299 p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
300 p_return_status => l_return_status ,
301 p_msg_count => l_msg_count ,
302 p_msg_data => l_msg_data ,
303 --
304 p_export_name => l_export_name,
305 p_worksheet_id => l_created_worksheet_id
306 );
307
308 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
309 RAISE FND_API.G_EXC_ERROR ;
310 END IF ;
311
312 Add_Debug_Info('After download ' || ' Status '||l_return_status) ;
313
314 END IF;
315
316 -- end ...
317 END LOOP ; -- To process all the child bgs for the current worksheet.
318 --
319 END IF; -- To check distribute_all_level_flag
320 --
321 END LOOP ; -- To process all the bgs for the current distribution_id.
322 --
323
324 retcode := 0 ;
325 COMMIT WORK;
326 --
327 EXCEPTION
328 --
329 WHEN FND_API.G_EXC_ERROR THEN
330 --
331 /*For Bug No : 2236283 Start*/
332 --ROLLBACK to statements are commented and blind ROLLBACKs are implemented
333 --since COMMIT is performed in child procedure and
334 --SAVEPOINT will never be established
335
336 --ROLLBACK TO Distribute_WS_Pvt ;
337 ROLLBACK;
338 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
339 p_print_header => FND_API.G_TRUE ) ;
340 retcode := 2 ;
341 --
342 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
343 --
344 --ROLLBACK TO Distribute_WS_Pvt ;
345 ROLLBACK;
346 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
347 p_print_header => FND_API.G_TRUE ) ;
348 retcode := 2 ;
349 --
350 WHEN OTHERS THEN
351 --
352 --ROLLBACK TO Distribute_WS_Pvt ;
353 ROLLBACK;
354 /*For Bug No : 2236283 End*/
355 --
356 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
357 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,
358 l_api_name ) ;
359 END IF ;
360 --
361 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
362 p_print_header => FND_API.G_TRUE ) ;
363 retcode := 2 ;
364 --
365 END Distribute_WS ;
366 /*---------------------------------------------------------------------------*/
367
368
369
370 /*===========================================================================+
371 | PROCEDURE Submit_WS |
372 +===========================================================================*/
373 --
374 -- The API sets up context information for the workflow submit process for a
375 -- given worksheet and starts the workflow process 'Submit Worksheet'.
376 --
377 PROCEDURE Submit_WS
378 (
379 errbuf OUT NOCOPY VARCHAR2,
380 retcode OUT NOCOPY VARCHAR2,
381 --
382 p_worksheet_id IN psb_worksheets.worksheet_id%TYPE ,
383 p_submitter_id IN NUMBER ,
384 p_operation_type IN VARCHAR2 ,
385 p_review_group_flag IN VARCHAR2 := 'N' ,
386 p_orig_system IN VARCHAR2 ,
387 p_merge_to_worksheet_id IN psb_worksheets.worksheet_id%TYPE ,
388 p_comments IN VARCHAR2 ,
389 p_operation_id IN NUMBER ,
390 p_constraint_set_id IN NUMBER
391 )
392 IS
393 --
394 l_api_name CONSTANT VARCHAR2(30) := 'Submit_WS' ;
395 l_api_version CONSTANT NUMBER := 1.0 ;
396 --
397 l_error_api_name VARCHAR2(2000);
398 l_return_status VARCHAR2(1) ;
399 l_msg_count NUMBER ;
400 l_msg_data VARCHAR2(2000) ;
401 l_msg_index_out NUMBER;
402 --
403 l_worksheet_id psb_worksheets.worksheet_id%TYPE ;
404 l_budget_group_id psb_worksheets.budget_group_id%TYPE ;
405 l_wf_role_name psb_budget_group_resp.wf_role_name%TYPE ;
406 --
407 l_lock_worksheet_id psb_worksheets.worksheet_id%TYPE ;
408 l_tmp_number NUMBER ;
409 l_item_key VARCHAR2(240) ;
410 /*For Bug No : 2115869 Start*/
411 l_review_group_exists VARCHAR2(1);
412 /*For Bug No : 2115869 End*/
413 --
414 BEGIN
415
416 --
417 -- Call Concurrency control API.
418 --
419
420 IF p_operation_type = 'MERGE' THEN
421 l_lock_worksheet_id := p_merge_to_worksheet_id ;
422 /*For Bug No : 2115869 Start*/
423 ELSIF p_operation_type = 'SUBMIT' THEN
424 IF p_review_group_flag <> 'Y' THEN
425 PSB_Submit_Worksheet_PVT.Check_Review_Groups
426 (
427 p_api_version => 1.0 ,
428 p_init_msg_list => FND_API.G_TRUE,
429 p_commit => FND_API.G_FALSE,
430 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
431 p_return_status => l_return_status,
432 p_msg_count => l_msg_count,
433 p_msg_data => l_msg_data,
434 --
435 p_worksheet_id => p_worksheet_id,
436 p_review_group_exists => l_review_group_exists
437 );
438 --
439 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
440 RAISE FND_API.G_EXC_ERROR ;
441 END IF ;
442 --
443 IF l_review_group_exists = 'T' THEN
444 l_review_group_exists := 'Y';
445 ELSE
446 l_review_group_exists := 'N';
447 END IF;
448
449 ELSE
450 l_review_group_exists := 'N';
451 END IF;
452 l_lock_worksheet_id := p_worksheet_id ;
453 /*For Bug No : 2115869 End*/
454 ELSE
455 l_lock_worksheet_id := p_worksheet_id ;
456 END IF ;
457
458 PSB_WS_Ops_Pvt.Check_WS_Ops_Concurrency
459 (
460 p_api_version => 1.0 ,
461 p_init_msg_list => FND_API.G_FALSE ,
462 p_validation_level => FND_API.G_VALID_LEVEL_NONE ,
463 p_return_status => l_return_status ,
464 p_msg_count => l_msg_count ,
465 p_msg_data => l_msg_data ,
466 --
467 p_worksheet_id => p_worksheet_id ,
468 p_operation_type => p_operation_type
469 );
470 --
471 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
472 FND_MSG_PUB.Add_Exc_Msg( 'PSB_WS_Ops_Pvt', 'Check_WS_Ops_Concurrency') ;
473 RAISE FND_API.G_EXC_ERROR ;
474 END IF ;
475 --
476
477 --
478 -- Find budget_group_id for the worksheet.
479 --
480 SELECT budget_group_id INTO l_budget_group_id
481 FROM psb_worksheets
482 WHERE worksheet_id = p_worksheet_id ;
483
484 --
485 -- Find budget_group users information.
486 --
487 SELECT min(wf_role_name) INTO l_wf_role_name
488 FROM psb_budget_groups bg ,
489 psb_budget_group_resp resp
490 WHERE bg.budget_group_id = l_budget_group_id
491 AND resp.responsibility_type = 'N'
492 AND bg.budget_group_id = resp.budget_group_id ;
493
494 --
495 -- Create an itemtype in psb_workflow_processes, to be used by the
496 -- Workflow 'Submit Process'.
497 --
498 SELECT psb_workflow_processes_s.nextval INTO l_item_key
499 FROM dual ;
500
501 INSERT INTO psb_workflow_processes
502 ( item_key ,
503 process_type ,
504 worksheet_id ,
505 process_date,
506 document_type
507 )
508 VALUES
509 ( l_item_key ,
510 p_operation_type ,
511 p_worksheet_id ,
512 SYSDATE,
513 'BP'
514 );
515
516
517 --
518 -- Start the Workflow 'Submit Process'.
519 --
520 PSB_Submit_Worksheet_PVT.Start_Process
521 ( p_api_version => 1.0 ,
522 p_init_msg_list => FND_API.G_FALSE,
523 p_commit => FND_API.G_FALSE,
524 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
525 p_return_status => l_return_status ,
526 p_msg_count => l_msg_count ,
527 p_msg_data => l_msg_data ,
528 --
529 p_item_key => l_item_key ,
530 p_submitter_id => p_submitter_id ,
531 p_submitter_name => l_wf_role_name ,
532 p_operation_type => p_operation_type ,
533 /*For Bug No : 2115869 Start*/
534 --p_review_group_flag => p_review_group_flag ,
535 p_review_group_flag => l_review_group_exists ,
536 /*For Bug No : 2115869 End*/
537 p_orig_system => '-99' ,
538 p_merge_to_worksheet_id => p_merge_to_worksheet_id ,
539 p_comments => p_comments ,
540 p_operation_id => p_operation_id ,
541 p_constraint_set_id => p_constraint_set_id
542 );
543 --
544 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
545 RAISE FND_API.G_EXC_ERROR ;
546 END IF ;
547 --
548 retcode := 0 ;
549 COMMIT WORK;
550 --
551 EXCEPTION
552 --
553 WHEN FND_API.G_EXC_ERROR THEN
554 --
555 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
556 p_print_header => FND_API.G_TRUE ) ;
557 retcode := 2 ;
558 --
559 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
560 --
561 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
562 p_print_header => FND_API.G_TRUE ) ;
563 retcode := 2 ;
564 --
565 WHEN OTHERS THEN
566 --
567 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
568 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,
569 l_api_name ) ;
570 END IF ;
571 --
572 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
573 p_print_header => FND_API.G_TRUE ) ;
574 retcode := 2 ;
575 --
576 END Submit_WS ;
577 /*---------------------------------------------------------------------------*/
578
579
580
581 /*===========================================================================+
582 | PROCEDURE Create_Worksheet (Private) |
583 +===========================================================================*/
584 PROCEDURE Create_Worksheet
585 (
586 p_worksheet_id IN NUMBER ,
587 p_global_worksheet_id IN NUMBER ,
588 p_budget_group_id IN NUMBER ,
589 p_distribution_id IN NUMBER ,
590 p_created_worksheet_id OUT NOCOPY NUMBER ,
591 p_return_status OUT NOCOPY VARCHAR2
592 )
593 IS
594 --
595 l_child_worksheet_id psb_worksheets.worksheet_id%TYPE ;
596 l_dist_wf_role_name psb_budget_group_resp.wf_role_name%TYPE ;
597 l_budget_group_resp_id psb_budget_group_resp.budget_group_resp_id%TYPE;
598 l_return_status VARCHAR2(1) ;
599 l_msg_count NUMBER ;
600 l_msg_data VARCHAR2(2000) ;
601 --
602 l_item_key NUMBER ;
603 l_freeze_flag VARCHAR2(1) ;
604 --
605 /*For Bug No : 2239422 Start*/
606 l_bg_short_name VARCHAR2(20);
607 --To find the BG short name
608 CURSOR c_bg_name IS
609 SELECT short_name
610 FROM psb_budget_groups
611 WHERE budget_group_id = p_budget_group_id;
612 /*For Bug No : 2239422 End*/
613
614 --
615 -- New way to find if a worksheet has been created for a budget group.
616 -- ( Bug#2832148 )
617 --
618 CURSOR l_child_worksheet_csr IS
619 SELECT worksheet_id
620 FROM psb_worksheets
621 WHERE global_worksheet_id = p_global_worksheet_id
622 AND budget_group_id = p_budget_group_id
623 AND worksheet_type = 'O' ;
624
625 /*
626 CURSOR l_child_worksheet_csr IS
627 SELECT child_worksheet_id
628 FROM psb_ws_distribution_details details, psb_ws_distributions distr
629 -- Bug No 2297742 Start
630 -- WHERE distr.worksheet_id = p_worksheet_id
631 WHERE distr.worksheet_id = details.worksheet_id
632 AND distr.distribution_id = p_distribution_id
633 -- AND distr.distribution_option_flag = 'W'
634 AND nvl(distr.distribution_option_flag, 'W') = 'W'
635 -- Bug No 2297742 End
636 AND global_worksheet_id = p_global_worksheet_id
637 AND child_budget_group_id = p_budget_group_id ;
638 */
639
640 -- Find the approver role name for the budget group.
641 CURSOR l_role_csr IS
642 SELECT wf_role_name, budget_group_resp_id
643 FROM psb_budget_groups bg ,
644 psb_budget_group_resp resp
645 WHERE bg.budget_group_id = p_budget_group_id
646 AND resp.responsibility_type = 'N'
647 AND bg.budget_group_id = resp.budget_group_id;
648
649 -- To find if the worksheet is frozen.
650 CURSOR l_ws_csr IS
651 SELECT freeze_flag
652 FROM psb_worksheets
653 WHERE worksheet_id = l_child_worksheet_id ;
654 --
655 BEGIN
656
657 --
658 -- Check whether it is a re-distribution or not.
659 --
660 OPEN l_child_worksheet_csr ;
661 FETCH l_child_worksheet_csr INTO l_child_worksheet_id ;
662 CLOSE l_child_worksheet_csr ;
663
664
665 IF l_child_worksheet_id IS NULL THEN
666
667 -- It means it is a first time distribution. Create an official
668 -- worksheet for distribution.
669 --
670 PSB_WS_Ops_Pvt.Create_Worksheet
671 (
672 p_api_version => 1.0 ,
673 p_init_msg_list => FND_API.G_TRUE,
674 /*For Bug no : 2236283 Start*/
675 --p_commit => FND_API.G_FALSE,
676 p_commit => FND_API.G_TRUE,
677 /*For Bug no : 2236283 End*/
678 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
679 p_return_status => l_return_status,
680 p_msg_count => l_msg_count,
681 p_msg_data => l_msg_data ,
682 --
683 p_worksheet_id => p_worksheet_id ,
684 p_budget_group_id => p_budget_group_id,
685 p_worksheet_id_OUT => l_child_worksheet_id
686 ) ;
687 --
688 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
689 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
690 END IF ;
691 --
692 ELSIF l_child_worksheet_id IS NOT NULL THEN
693 --
694 -- It means it is a re-distribution. Update l_child_worksheet_id
695 -- with the p_worksheet_id .
696 --
697 PSB_WS_Ops_Pvt.Update_Worksheet
698 (
699 p_api_version => 1.0 ,
700 p_init_msg_list => FND_API.G_TRUE,
701 p_commit => FND_API.G_FALSE,
702 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
703 p_return_status => l_return_status,
704 p_msg_count => l_msg_count,
705 p_msg_data => l_msg_data,
706 --
707 p_source_worksheet_id => p_worksheet_id ,
708 p_target_worksheet_id => l_child_worksheet_id
709 );
710 --
711 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
712 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
713 END IF ;
714 --
715
716 OPEN l_ws_csr ;
717 FETCH l_ws_csr INTO l_freeze_flag ;
718 CLOSE l_ws_csr ;
719
720 --
721 -- If the worksheet is frozen then unfreeze it.
722 --
723 IF NVL(l_freeze_flag, 'N' ) = 'Y' THEN
724 --
725 PSB_WS_Ops_Pvt.Freeze_Worksheet
726 (
727 p_api_version => 1.0 ,
728 p_init_msg_list => FND_API.G_FALSE,
729 p_commit => FND_API.G_FALSE,
730 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
731 p_return_status => l_return_status,
732 p_msg_count => l_msg_count,
733 p_msg_data => l_msg_data,
734 --
735 p_worksheet_id => l_child_worksheet_id ,
736 p_freeze_flag => 'N'
737 );
738 --
739 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
740 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
741 END IF ;
742 --
743 END IF;
744 --
745 END IF ;
746
747 --
748 p_created_worksheet_id := l_child_worksheet_id ;
749 --
750 Add_Debug_Info( 'child ws id is ' || to_char(l_child_worksheet_id) ) ;
751 --
752
753 /*---2. DISTRIBUTION -------------------------------------------*/
754
755 --
756 -- Update psb_ws_distribution_details table.
757 --
758 OPEN l_role_csr ;
759 FETCH l_role_csr INTO l_dist_wf_role_name, l_budget_group_resp_id ;
760
761 IF l_role_csr%NOTFOUND THEN
762
763 /*For Bug No : 2239422 Start*/
764 FOR c_bg_name_rec IN c_bg_name LOOP
765 l_bg_short_name := c_bg_name_rec.short_name;
766 END LOOP;
767 /*For Bug No : 2239422 End*/
768
769 FND_MESSAGE.SET_NAME ('PSB', 'PSB_DISTRIBUTION_NO_ROLE');
770
771 /*For Bug No : 2239422 Start*/
772 --commented since bg_short_name has to be displayed
773 --FND_MESSAGE.SET_TOKEN('BGROUP', p_budget_group_id);
774 FND_MESSAGE.SET_TOKEN('BGROUP', l_bg_short_name);
775 /*For Bug No : 2239422 End*/
776
777 FND_MSG_PUB.Add ;
778 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
779 END IF;
780 CLOSE l_role_csr ;
781
782
783 INSERT INTO psb_ws_distribution_details
784 (
785 distribution_id ,
786 worksheet_id ,
787 global_worksheet_id ,
788 child_worksheet_id ,
789 child_budget_group_id ,
790 budget_group_resp_id
791 )
792 VALUES
793 ( p_distribution_id ,
794 p_worksheet_id ,
795 p_global_worksheet_id ,
796 l_child_worksheet_id ,
797 p_budget_group_id ,
798 l_budget_group_resp_id
799 ) ;
800
801 IF (SQL%NOTFOUND) THEN
802 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
803 END IF;
804
805 -- Update distributions table
806 UPDATE psb_ws_distributions
807 SET distribution_date = g_current_date,
808 distributed_flag = 'Y',
809 last_update_date = g_current_date,
810 last_updated_by = g_current_user_id,
811 last_update_login = g_current_login_id
812 WHERE distribution_id = p_distribution_id ;
813
814 IF (SQL%NOTFOUND) THEN
815 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
816 END IF;
817
818
819 /*---3. WORKFLOW-----------------------------------------------*/
820 --
821 -- Create an itemtype in psb_workflow_processes, to be used by the
822 -- Workflow 'Distribute Process'.
823 --
824
825 SELECT psb_workflow_processes_s.nextval INTO l_item_key
826 FROM dual ;
827
828 INSERT INTO psb_workflow_processes
829 ( item_key ,
830 process_type ,
831 worksheet_id ,
832 process_date,
833 document_type
834 )
835 VALUES
836 ( l_item_key ,
837 'DISTRIBUTE' ,
838 l_child_worksheet_id ,
839 SYSDATE,
840 'BP'
841 );
842
843 IF (SQL%NOTFOUND ) THEN
844 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
845 END IF;
846
847 --
848 -- Start the Workflow 'Distribution Process'.
849 --
850
851 PSB_Distribute_Worksheet_PVT.Start_Process
852 ( p_api_version => 1.0 ,
853 p_init_msg_list => FND_API.G_FALSE,
854 p_commit => FND_API.G_FALSE,
855 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
856 p_return_status => l_return_status ,
857 p_msg_count => l_msg_count ,
858 p_msg_data => l_msg_data ,
859 --
860 p_item_key => l_item_key ,
861 p_distribution_instructions => g_distribution_instructions ,
862 p_recipient_name => l_dist_wf_role_name
863 );
864 --
865 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
866 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
867 END IF ;
868 --
869 p_return_status := FND_API.G_RET_STS_SUCCESS ;
870 --
871 EXCEPTION
872 --
873 WHEN OTHERS THEN
874 --
875 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
876 'Create_Worksheet (Private)' );
877 --
878 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
879 --
880 END Create_Worksheet;
881 /*---------------------------------------------------------------------------*/
882
883
884
885 /*===========================================================================+
886 | PROCEDURE Generate_Account |
887 +===========================================================================*/
888 PROCEDURE Generate_Account
889 (
890 p_api_version IN NUMBER ,
891 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
892 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
893 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
894 p_return_status OUT NOCOPY VARCHAR2 ,
895 p_msg_count OUT NOCOPY NUMBER ,
896 p_msg_data OUT NOCOPY VARCHAR2 ,
897 --
898 p_project_id IN psb_cost_distributions_i.project_id%TYPE ,
899 p_task_id IN psb_cost_distributions_i.task_id%TYPE ,
900 p_award_id IN psb_cost_distributions_i.award_id%TYPE ,
901 p_expenditure_type IN psb_cost_distributions_i.expenditure_type%TYPE ,
902 p_expenditure_organization_id
903 IN
904 psb_cost_distributions_i.expenditure_organization_id%TYPE ,
905 p_chart_of_accounts_id IN NUMBER,
906 p_description IN VARCHAR2 := FND_API.G_MISS_CHAR ,
907 p_code_combination_id OUT NOCOPY gl_code_combinations.code_combination_id%TYPE ,
908 p_error_message OUT NOCOPY VARCHAR2
909 )
910 IS
911 --
912 l_api_name CONSTANT VARCHAR2(30) := 'Generate_Account' ;
913 l_api_version CONSTANT NUMBER := 1.0 ;
914 --
915 l_return_status VARCHAR2(1) ;
916 l_msg_count NUMBER ;
917 l_msg_data VARCHAR2(2000) ;
918 --
919 l_description VARCHAR2(2000);
920 l_project_number VARCHAR2(30);
921 l_task_number VARCHAR2(30);
922 l_award_number VARCHAR2(30);
923 --UTF8 changes for Bug No : 2615261
924 l_expenditure_org_name hr_all_organization_units.name%TYPE;
925 --
926 l_itemtype CONSTANT VARCHAR2(30) := 'PSBLDMAG';
927 l_itemkey VARCHAR2(30);
928 l_result BOOLEAN;
929 l_concat_segs VARCHAR2(200);
930 l_concat_ids VARCHAR2(200);
931 l_concat_descrs VARCHAR2(500);
932 l_error_message VARCHAR2(100);
933 l_return_ccid gl_code_combinations.code_combination_id%TYPE;
934 l_new_combination BOOLEAN;
935 --
936 BEGIN
937 --
938 SAVEPOINT Generate_Account_Pvt ;
939 --
940 IF NOT FND_API.Compatible_API_Call ( l_api_version,
941 p_api_version,
942 l_api_name,
943 G_PKG_NAME )
944 THEN
945 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
946 END IF;
947 --
948
949 IF FND_API.To_Boolean ( p_init_msg_list ) THEN
950 FND_MSG_PUB.initialize ;
951 END IF;
952 --
953 p_return_status := FND_API.G_RET_STS_SUCCESS ;
954 --
955
956 pd('Calling initialize with coa_id : ' || p_chart_of_accounts_id);
957
958 -- Used for debuggin only.
959 -- fnd_flex_workflow.debug_on ;
960
961 l_itemkey := fnd_flex_workflow.initialize
962 (
963 appl_short_name => 'SQLGL',
964 code => 'GL#',
965 num => p_chart_of_accounts_id,
966 itemtype => l_itemtype
967 );
968
969 -----------------------------------------------------------
970 -- Initialize the workflow item attributes
971 -----------------------------------------------------------
972
973 wf_engine.SetItemAttrNumber( itemtype => l_itemtype,
974 itemkey => l_itemkey,
975 aname => 'PROJECT_ID',
976 avalue => p_project_id);
977
978 wf_engine.SetItemAttrNumber( itemtype => l_itemtype,
979 itemkey => l_itemkey,
980 aname => 'TASK_ID',
981 avalue => p_task_id);
982
983
984 wf_engine.SetItemAttrNumber( itemtype => l_itemtype,
985 itemkey => l_itemkey,
986 aname => 'AWARD_ID',
987 avalue => p_award_id);
988
989 wf_engine.SetItemAttrText ( itemtype => l_itemtype,
990 itemkey => l_itemkey,
991 aname => 'EXPENDITURE_TYPE',
992 avalue => p_expenditure_type);
993
994 wf_engine.SetItemAttrNumber( itemtype => l_itemtype,
995 itemkey => l_itemkey,
996 aname => 'EXPENDITURE_ORGANIZATION_ID',
997 avalue => p_expenditure_organization_id);
998
999 -----------------------------------------------------------
1000 -- Populate item attributes based on POETA internal codes.
1001 -----------------------------------------------------------
1002
1003 -- Resolve optional parameters.
1004 IF p_description = FND_API.G_MISS_CHAR THEN
1005 l_description := NULL;
1006 ELSE
1007 l_description := p_description ;
1008 END IF;
1009
1010 -- Populate description related variables.
1011 IF l_description IS NOT NULL THEN
1012
1013 l_project_number := SUBSTR(l_description,3,instr(l_description,'#;',1,2)-3);
1014
1015 l_task_number := SUBSTR(l_description, instr(l_description,'#;',1,2)+2,
1016 (instr(l_description,'#;',1,3)-4) - instr(l_description,'#;',1,2)+2) ;
1017
1018 l_award_number := SUBSTR(l_description,instr(l_description,'#;',1,3)+2,
1019 (instr(l_description,'#;',1,4)-4)-instr(l_description,'#;',1,3)+2) ;
1020
1021 l_expenditure_org_name :=
1022 SUBSTR(l_description,instr(l_description,'#;',1,4)+2,
1023 (instr(l_description,'#;',1,5)-4)-instr(l_description,'#;',1,4)+2) ;
1024
1025 END IF ;
1026
1027 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1028 itemkey => l_itemkey,
1029 aname => 'PROJECT_NUMBER',
1030 avalue => l_project_number );
1031
1032 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1033 itemkey => l_itemkey,
1034 aname => 'TASK_NUMBER',
1035 avalue => l_task_number );
1036
1037 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1038 itemkey => l_itemkey,
1039 aname => 'AWARD_NUMBER',
1040 avalue => l_award_number );
1041
1042 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1043 itemkey => l_itemkey,
1044 aname => 'EXPENDITURE_ORG_NAME',
1045 avalue => l_expenditure_org_name );
1046
1047 -----------------------------------------------------------
1048 -- Call the workflow Generate function to trigger off the
1049 -- workflow account generation
1050 -----------------------------------------------------------
1051
1052 p_error_message := NULL ;
1053
1054 l_result := fnd_flex_workflow.generate
1055 (
1056 itemtype => l_itemtype ,
1057 itemkey => l_itemkey ,
1058 insert_if_new => TRUE ,
1059 ccid => l_return_ccid ,
1060 concat_segs => l_concat_segs ,
1061 concat_ids => l_concat_ids ,
1062 concat_descrs => l_concat_descrs ,
1063 error_message => l_error_message ,
1064 new_combination => l_new_combination
1065 );
1066
1067 ------------------------------------------------------------------
1068 -- Return the code_combination_id or the error message.
1069 ------------------------------------------------------------------
1070 IF l_return_ccid <> 0 THEN
1071 p_code_combination_id := l_return_ccid ;
1072 ELSE
1073 p_code_combination_id := NULL ;
1074 p_error_message := l_error_message ;
1075 END IF;
1076
1077 --
1078 IF FND_API.To_Boolean ( p_commit ) THEN
1079 COMMIT WORK;
1080 END IF;
1081 --
1082 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1083 p_data => p_msg_data );
1084 --
1085 EXCEPTION
1086 --
1087 WHEN FND_API.G_EXC_ERROR THEN
1088 --
1089 ROLLBACK TO Generate_Account_Pvt ;
1090 p_return_status := FND_API.G_RET_STS_ERROR;
1091 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1092 p_data => p_msg_data );
1093 --
1094 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1095 --
1096 ROLLBACK TO Generate_Account_Pvt ;
1097 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1098 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1099 p_data => p_msg_data );
1100 --
1101 WHEN OTHERS THEN
1102 --
1103 ROLLBACK TO Generate_Account_Pvt ;
1104 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1105 --
1106 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1107 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
1108 l_api_name);
1109 END if;
1110 --
1111 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1112 p_data => p_msg_data );
1113 --
1114 END Generate_Account ;
1115 /*---------------------------------------------------------------------------*/
1116
1117 /*===========================================================================+
1118 | PROCEDURE Distribute_BR |
1119 +==========================================================================*/
1120 --
1121 -- The API sets up context information for the budget revision distribute
1122 -- process for a given budget revision and starts the workflow process
1123 -- 'Distribute Budget Revision' (It is basically an Concurrent Program API).
1124 --
1125 PROCEDURE Distribute_BR
1126 (
1127 errbuf OUT NOCOPY VARCHAR2,
1128 retcode OUT NOCOPY VARCHAR2,
1129 --
1130 p_distribution_id IN NUMBER,
1131 p_submitter_id IN NUMBER
1132 )
1133 IS
1134 --
1135 l_api_name CONSTANT VARCHAR2(30) := 'Distribute_BR' ;
1136 l_api_version CONSTANT NUMBER := 1.0 ;
1137 --
1138 l_error_api_name VARCHAR2(2000);
1139 l_return_status VARCHAR2(1) ;
1140 l_msg_count NUMBER ;
1141 l_msg_data VARCHAR2(2000) ;
1142 --
1143 l_budget_revision_id NUMBER;
1144 l_created_budget_revision_id NUMBER;
1145 l_global_budget_revision_id NUMBER;
1146 l_distribution_rule_id NUMBER;
1147 --
1148 BEGIN
1149
1150 --
1151 -- Re-instatiate global variable as the concurrent manager does not do it
1152 -- for the same session.
1153 --
1154 g_current_date := SYSDATE ;
1155 g_current_user_id := NVL( Fnd_Global.User_Id , 0) ;
1156 g_current_login_id := NVL( Fnd_Global.Login_Id , 0) ;
1157 --
1158
1159 SAVEPOINT Distribute_BR_Pvt ;
1160
1161 --
1162 -- Get distribution information.
1163 -- The distribution_option_flag specifies whether
1164 -- it is a worksheet or budget revision
1165
1166 SELECT worksheet_id ,
1167 distribution_rule_id ,
1168 distribution_instructions
1169 INTO
1170 l_budget_revision_id ,
1171 l_distribution_rule_id ,
1172 g_distribution_instructions
1173 FROM psb_ws_distributions
1174 WHERE distribution_id = p_distribution_id
1175 AND distribution_option_flag = 'R'; --for budget revision
1176
1177 --
1178 -- Find global_budget_revision_id for the
1179 -- l_budget_revision_id(budget_revision_id in this scenario)
1180 -- If global_budget_revision_id is NULL, then the l_budget_revision_id
1181 -- is the global budget revision.
1182 --
1183 SELECT NVL( global_budget_revision_id, l_budget_revision_id )
1184 INTO l_global_budget_revision_id
1185 FROM psb_budget_revisions
1186 WHERE budget_revision_id = l_budget_revision_id ;
1187
1188 --
1189 FOR l_budget_groups_rec IN
1190 (
1191 SELECT a.budget_group_id ,b.short_name short_name,
1192 NVL( distribute_all_level_flag, 'N') distribute_all_level_flag,
1193 NVL( download_flag, 'N') download_flag,
1194 NVL( download_all_level_flag, 'N') download_all_level_flag
1195 FROM psb_ws_distribution_rule_lines a, psb_budget_groups b
1196 WHERE distribution_rule_id = l_distribution_rule_id
1197 AND a.budget_group_id = b.budget_group_id
1198 )
1199 LOOP
1200
1201 Add_Debug_Info('download flag is ' || l_budget_groups_rec.download_flag );
1202 Add_Debug_Info('download ALL LEVEL flag is ' ||
1203 l_budget_groups_rec.download_all_level_flag) ;
1204 --
1205 IF l_budget_groups_rec.distribute_all_level_flag = 'N' THEN
1206 --
1207 -- Create Revision for the given budget group only.
1208 --
1209 Distribute_Budget_Revision
1210 ( p_budget_revision_id => l_budget_revision_id,
1211 p_global_budget_revision_id => l_global_budget_revision_id,
1212 p_budget_group_id => l_budget_groups_rec.budget_group_id,
1213 p_distribution_id => p_distribution_id,
1214 p_created_budget_revision_id => l_created_budget_revision_id,
1215 p_return_status => l_return_status
1216 );
1217 --
1218 Add_Debug_Info('1- WS for BG '||
1219 to_char(l_budget_groups_rec.budget_group_id) ||
1220 ' Status '||l_return_status) ;
1221
1222 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1223 RAISE FND_API.G_EXC_ERROR ;
1224 END IF ;
1225 --
1226
1227 ELSIF l_budget_groups_rec.distribute_all_level_flag = 'Y' THEN
1228
1229 --
1230 -- Create Revisions for the given budget group and its child budget groups.
1231 --
1232 FOR l_child_bgs_rec IN
1233 (
1234 SELECT budget_group_id, short_name short_name
1235 FROM psb_budget_groups
1236 WHERE budget_group_type = 'R'
1237 START WITH budget_group_id = l_budget_groups_rec.budget_group_id
1238 CONNECT BY PRIOR budget_group_id = parent_budget_group_id
1239 )
1240 LOOP
1241 --
1242
1243 Distribute_Budget_Revision
1244 ( p_budget_revision_id => l_budget_revision_id,
1245 p_global_budget_revision_id => l_global_budget_revision_id,
1246 p_budget_group_id => l_child_bgs_rec.budget_group_id,
1247 p_distribution_id => p_distribution_id,
1248 p_created_budget_revision_id => l_created_budget_revision_id,
1249 p_return_status => l_return_status
1250 );
1251 --
1252
1253 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1254 RAISE FND_API.G_EXC_ERROR ;
1255 END IF ;
1256 --
1257
1258 END LOOP ; -- To process all the child bgs for the current worksheet.
1259 --
1260 END IF; -- To check distribute_all_level_flag
1261 --
1262 END LOOP ; -- To process all the bgs for the current distribution_id.
1263 --
1264
1265 retcode := 0 ;
1266 COMMIT WORK;
1267 --
1268 EXCEPTION
1269 --
1270 WHEN FND_API.G_EXC_ERROR THEN
1271 --
1272 ROLLBACK TO Distribute_BR_Pvt ;
1273 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1274 p_print_header => FND_API.G_TRUE ) ;
1275 retcode := 2 ;
1276 --
1277 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1278 --
1279 ROLLBACK TO Distribute_BR_Pvt ;
1280 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1281 p_print_header => FND_API.G_TRUE ) ;
1282 retcode := 2 ;
1283 --
1284 WHEN OTHERS THEN
1285 --
1286 ROLLBACK TO Distribute_BR_Pvt ;
1287 --
1288 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1289 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,
1290 l_api_name ) ;
1291 END IF ;
1292 --
1293 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1294 p_print_header => FND_API.G_TRUE ) ;
1295 retcode := 2 ;
1296 --
1297 END Distribute_BR ;
1298 /*===========================================================================+
1299 | PROCEDURE Distribute_Budget_Revision (Private) |
1300 +===========================================================================*/
1301 -- The Private API is called by Distribute_BR or the concurrent process API
1302 -- It creates budget revisions for parent, child budget revisions as part
1303 -- of the distribution process and creates an item type for workflow process
1304 --
1305
1306 PROCEDURE Distribute_Budget_Revision
1307 (
1308 p_budget_revision_id IN NUMBER ,
1309 p_global_budget_revision_id IN NUMBER ,
1310 p_budget_group_id IN NUMBER ,
1311 p_distribution_id IN NUMBER ,
1312 p_created_budget_revision_id OUT NOCOPY NUMBER ,
1313 p_return_status OUT NOCOPY VARCHAR2
1314 )
1315 IS
1316 --
1317 l_child_budget_revision_id psb_budget_revisions.budget_revision_id%TYPE ;
1318 l_dist_wf_role_name psb_budget_group_resp.wf_role_name%TYPE ;
1319 l_budget_group_resp_id psb_budget_group_resp.budget_group_resp_id%TYPE;
1320 l_return_status VARCHAR2(1) ;
1321 l_msg_count NUMBER ;
1322 l_msg_data VARCHAR2(2000) ;
1323 --
1324 l_item_key NUMBER ;
1325 l_freeze_flag VARCHAR2(1) ;
1326 l_revision_option_flag VARCHAR2(1) ;
1327 --
1328 /*For Bug No : 2239422 Start*/
1329 l_bg_short_name VARCHAR2(20);
1330 --To find the BG short name
1331 CURSOR c_bg_name IS
1332 SELECT short_name
1333 FROM psb_budget_groups
1334 WHERE budget_group_id = p_budget_group_id;
1335 /*For Bug No : 2239422 End*/
1336
1337 --
1338 -- New way to find if a revision has been created for a budget group.
1339 -- ( Bug#2832148 )
1340 --
1341 CURSOR l_child_budget_rev_csr IS
1342 SELECT budget_revision_id
1343 FROM psb_budget_revisions
1344 WHERE global_budget_revision_id = p_global_budget_revision_id
1345 AND budget_group_id = p_budget_group_id ;
1346
1347 /*
1348 -- To find whether a budget revision been created for the budget group.
1349 CURSOR l_child_budget_rev_csr IS
1350 SELECT child_worksheet_id
1351 FROM psb_ws_distribution_details details, psb_ws_distributions distr
1352 WHERE distr.worksheet_id = p_budget_revision_id
1353 AND distr.distribution_id = p_distribution_id
1354 AND distr.distribution_option_flag = 'R'
1355 AND global_worksheet_id = p_global_budget_revision_id
1356 AND child_budget_group_id = p_budget_group_id;
1357 */
1358
1359 CURSOR l_revision_option_csr IS
1360 SELECT revision_option_flag
1361 FROM PSB_WS_DISTRIBUTIONS
1362 WHERE distribution_id = p_distribution_id
1363 AND distribution_option_flag = 'R';
1364
1365 -- Find the approver role name for the budget group.
1366 CURSOR l_role_csr IS
1367 SELECT wf_role_name, budget_group_resp_id
1368 FROM psb_budget_groups bg ,
1369 psb_budget_group_resp resp
1370 WHERE bg.budget_group_id = p_budget_group_id
1371 AND resp.responsibility_type = 'N'
1372 AND bg.budget_group_id = resp.budget_group_id;
1373
1374 -- To find if the budget revision is frozen.
1375
1376 CURSOR l_br_csr IS
1377 SELECT freeze_flag
1378 FROM psb_budget_revisions
1379 WHERE budget_revision_id = l_child_budget_revision_id;
1380 --
1381 BEGIN
1382
1383 --
1384 -- Check whether it is a re-distribution or not.
1385 --
1386 OPEN l_revision_option_csr;
1387 FETCH l_revision_option_csr INTO l_revision_option_flag ;
1388 CLOSE l_revision_option_csr ;
1389
1390 OPEN l_child_budget_rev_csr ;
1391 FETCH l_child_budget_rev_csr INTO l_child_budget_revision_id ;
1392 CLOSE l_child_budget_rev_csr ;
1393
1394
1395 IF l_child_budget_revision_id IS NULL THEN
1396
1397 -- It means it is a first time distribution. Create an official
1398 -- budget revision for distribution.
1399 --
1400 PSB_Create_BR_Pvt.Create_Budget_Revision
1401 (
1402 p_api_version => 1.0 ,
1403 p_init_msg_list => FND_API.G_TRUE,
1404 p_commit => FND_API.G_FALSE,
1405 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
1406 p_return_status => l_return_status,
1407 p_msg_count => l_msg_count,
1408 p_msg_data => l_msg_data ,
1409 --
1410 p_budget_revision_id => p_budget_revision_id ,
1411 p_budget_group_id => p_budget_group_id,
1412 p_revision_option_flag => l_revision_option_flag,
1413 p_budget_revision_id_out => l_child_budget_revision_id
1414 ) ;
1415 --
1416 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1417 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1418 END IF ;
1419 --
1420 ELSIF l_child_budget_revision_id IS NOT NULL THEN
1421 --
1422 -- It means it is a re-distribution. Update l_child_budget_revision_id
1423 -- with the p_budget_revision_id.
1424 --
1425 PSB_Create_BR_Pvt.Update_Target_Budget_Revision
1426 (
1427 p_api_version => 1.0 ,
1428 p_init_msg_list => FND_API.G_TRUE,
1429 p_commit => FND_API.G_FALSE,
1430 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1431 p_return_status => l_return_status,
1432 p_msg_count => l_msg_count,
1433 p_msg_data => l_msg_data,
1434 --
1435 p_source_budget_revision_id => p_budget_revision_id ,
1436 p_revision_option_flag => l_revision_option_flag,
1437 p_target_budget_revision_id => l_child_budget_revision_id
1438 );
1439 --
1440 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1441 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1442 END IF ;
1443 --
1444
1445
1446 if ((l_revision_option_flag is not null) and (l_revision_option_flag <> 'N')) then
1447
1448 OPEN l_br_csr ;
1449 FETCH l_br_csr INTO l_freeze_flag ;
1450 CLOSE l_br_csr ;
1451
1452 --
1453 -- If the budget revision is frozen then unfreeze it.
1454 --
1455 IF NVL(l_freeze_flag, 'N' ) = 'Y' THEN
1456 --
1457 PSB_Create_BR_Pvt.Freeze_Budget_Revision
1458 (
1459 p_api_version => 1.0 ,
1460 p_init_msg_list => FND_API.G_FALSE,
1461 p_commit => FND_API.G_FALSE,
1462 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1463 p_return_status => l_return_status,
1464 p_msg_count => l_msg_count,
1465 p_msg_data => l_msg_data,
1466 --
1467 p_budget_revision_id => l_child_budget_revision_id,
1468 p_freeze_flag => 'N'
1469 );
1470 --
1471 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1472 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1473 END IF ;
1474 --
1475 END IF;
1476 --
1477 END IF ;
1478 END IF ;
1479
1480 --
1481 p_created_budget_revision_id := l_child_budget_revision_id ;
1482 --
1483 Add_Debug_Info( 'child bg id is ' || to_char(l_child_budget_revision_id) ) ;
1484 --
1485
1486 /*---2. DISTRIBUTION -------------------------------------------*/
1487
1488 --
1489 -- Update psb_ws_distribution_details table.
1490 --
1491 OPEN l_role_csr ;
1492 FETCH l_role_csr INTO l_dist_wf_role_name, l_budget_group_resp_id ;
1493
1494 IF l_role_csr%NOTFOUND THEN
1495
1496 /*For Bug No : 2239422 Start*/
1497 FOR c_bg_name_rec IN c_bg_name LOOP
1498 l_bg_short_name := c_bg_name_rec.short_name;
1499 END LOOP;
1500 /*For Bug No : 2239422 End*/
1501
1502 FND_MESSAGE.SET_NAME ('PSB', 'PSB_DISTRIBUTION_NO_ROLE');
1503
1504 /*For Bug No : 2239422 Start*/
1505 --commented since bg_short_name has to be displayed
1506 --FND_MESSAGE.SET_TOKEN('BGROUP', p_budget_group_id);
1507 FND_MESSAGE.SET_TOKEN('BGROUP', l_bg_short_name);
1508 /*For Bug No : 2239422 End*/
1509
1510 FND_MSG_PUB.Add ;
1511 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1512 END IF;
1513 CLOSE l_role_csr ;
1514
1515
1516 INSERT INTO psb_ws_distribution_details
1517 (
1518 distribution_id ,
1519 worksheet_id ,
1520 global_worksheet_id ,
1521 child_worksheet_id ,
1522 child_budget_group_id ,
1523 budget_group_resp_id
1524 )
1525 VALUES
1526 ( p_distribution_id ,
1527 p_budget_revision_id ,
1528 p_global_budget_revision_id ,
1529 l_child_budget_revision_id ,
1530 p_budget_group_id ,
1531 l_budget_group_resp_id
1532 ) ;
1533
1534 IF (SQL%NOTFOUND) THEN
1535 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1536 END IF;
1537
1538 -- Update distributions table
1539 UPDATE psb_ws_distributions
1540 SET distribution_date = g_current_date,
1541 distributed_flag = 'Y',
1542 last_update_date = g_current_date,
1543 last_updated_by = g_current_user_id,
1544 last_update_login = g_current_login_id
1545 WHERE distribution_id = p_distribution_id ;
1546
1547 IF (SQL%NOTFOUND) THEN
1548 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1549 END IF;
1550
1551
1552 /*---3. WORKFLOW-----------------------------------------------*/
1553 --
1554 -- Create an itemtype in psb_workflow_processes, to be used by the
1555 -- Workflow 'Distribute Process'.
1556 --
1557
1558 SELECT psb_workflow_processes_s.nextval INTO l_item_key
1559 FROM dual ;
1560
1561 INSERT INTO psb_workflow_processes
1562 ( item_key ,
1563 process_type ,
1564 worksheet_id ,
1565 process_date ,
1566 document_type
1567 )
1568 VALUES
1569 ( l_item_key ,
1570 'DISTRIBUTE_REVISION' ,
1571 l_child_budget_revision_id ,
1572 SYSDATE,
1573 'BR'
1574 );
1575
1576 IF (SQL%NOTFOUND ) THEN
1577 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1578 END IF;
1579
1580 --
1581 -- Start the Workflow 'Distribution Process'.
1582 --
1583
1584 PSB_Submit_Revision_Pvt.Start_Distribution_Process
1585 ( p_api_version => 1.0 ,
1586 p_init_msg_list => FND_API.G_FALSE,
1587 p_commit => FND_API.G_FALSE,
1588 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1589 p_return_status => l_return_status ,
1590 p_msg_count => l_msg_count ,
1591 p_msg_data => l_msg_data ,
1592 --
1593 p_item_key => l_item_key ,
1594 p_distribution_instructions => g_distribution_instructions ,
1595 p_recipient_name => l_dist_wf_role_name
1596 );
1597 --
1598 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1599 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1600 END IF ;
1601 --
1602 p_return_status := FND_API.G_RET_STS_SUCCESS ;
1603 --
1604 EXCEPTION
1605 --
1606 WHEN OTHERS THEN
1607 --
1608 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
1609 'Distribute_Budget_Revision (Private)' );
1610 --
1611 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1612 --
1613 END Distribute_Budget_Revision;
1614
1615
1616 /*===========================================================================+
1617 | PROCEDURE Submit_BR |
1618 +===========================================================================*/
1619 --
1620 -- The API sets up context information for the workflow submit
1621 -- process for a given Budget Revision and starts the workflow process
1622 --'Submit Budget Revision'.
1623
1624 PROCEDURE Submit_BR
1625 (
1626 errbuf OUT NOCOPY VARCHAR2,
1627 retcode OUT NOCOPY VARCHAR2,
1628 --
1629 p_budget_revision_id IN psb_budget_revisions.budget_revision_id%type,
1630 p_submitter_id IN NUMBER ,
1631 p_operation_type IN VARCHAR2 ,
1632 p_orig_system IN VARCHAR2 ,
1633 p_comments IN VARCHAR2 ,
1634 p_operation_id IN NUMBER ,
1635 p_constraint_set_id IN NUMBER
1636 )
1637 IS
1638 --
1639 l_api_name CONSTANT VARCHAR2(30) := 'Submit_BR' ;
1640 l_api_version CONSTANT NUMBER := 1.0 ;
1641 --
1642 l_error_api_name VARCHAR2(2000);
1643 l_return_status VARCHAR2(1) ;
1644 l_msg_count NUMBER ;
1645 l_msg_data VARCHAR2(2000) ;
1646 l_msg_index_out NUMBER;
1647 --
1648 l_budget_revision_id psb_budget_revisions.budget_revision_id%TYPE ;
1649 l_budget_group_id psb_budget_revisions.budget_group_id%TYPE ;
1650 l_wf_role_name psb_budget_group_resp.wf_role_name%TYPE ;
1651 --
1652 l_tmp_number NUMBER ;
1653 l_item_key VARCHAR2(240) ;
1654 --
1655 BEGIN
1656
1657 --
1658 -- Call Concurrency control API.
1659 --
1660
1661 /*PSB_Create_BR_Pvt.Check_BR_Ops_Concurrency
1662 (
1663 p_api_version => 1.0 ,
1664 p_init_msg_list => FND_API.G_FALSE ,
1665 p_validation_level => FND_API.G_VALID_LEVEL_NONE ,
1666 p_return_status => l_return_status ,
1667 p_msg_count => l_msg_count ,
1668 p_msg_data => l_msg_data ,
1669 --
1670 p_budget_revision_id => p_budget_revision_id ,
1671 p_operation_type => p_operation_type
1672 );
1673 --
1674 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1675 FND_MSG_PUB.Add_Exc_Msg( 'PSB_BR_Ops_Pvt', 'Check_BR_Ops_Concurrency') ;
1676 RAISE FND_API.G_EXC_ERROR ;
1677 END IF ; */
1678 --
1679
1680 --
1681 -- Find budget_group_id for the worksheet.
1682 --
1683 SELECT budget_group_id INTO l_budget_group_id
1684 FROM psb_budget_revisions
1685 WHERE budget_revision_id = p_budget_revision_id ;
1686
1687 --
1688 -- Find budget_group users information.
1689 --
1690 SELECT min(wf_role_name) INTO l_wf_role_name
1691 FROM psb_budget_groups bg ,
1692 psb_budget_group_resp resp
1693 WHERE bg.budget_group_id = l_budget_group_id
1694 AND resp.responsibility_type = 'N'
1695 AND bg.budget_group_id = resp.budget_group_id ;
1696
1697 --
1698 -- Create an itemtype in psb_workflow_processes, to be used by the
1699 -- Workflow 'Submit Process'.
1700 --
1701 SELECT psb_workflow_processes_s.nextval INTO l_item_key
1702 FROM dual ;
1703
1704
1705 INSERT INTO psb_workflow_processes
1706 ( item_key ,
1707 process_type ,
1708 worksheet_id ,
1709 process_date,
1710 document_type
1711 )
1712 VALUES
1713 ( l_item_key ,
1714 p_operation_type ,
1715 p_budget_revision_id ,
1716 SYSDATE,
1717 'BR'
1718 );
1719
1720
1721 --
1722 -- Start the Workflow 'Submit Process'.
1723 --
1724
1725 PSB_Submit_Revision_PVT.Start_Process
1726 ( p_api_version => 1.0 ,
1727 p_init_msg_list => FND_API.G_FALSE,
1728 p_commit => FND_API.G_FALSE,
1729 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1730 p_return_status => l_return_status ,
1731 p_msg_count => l_msg_count ,
1732 p_msg_data => l_msg_data ,
1733 --
1734 p_item_key => l_item_key ,
1735 p_submitter_id => p_submitter_id ,
1736 p_submitter_name => l_wf_role_name ,
1737 p_operation_type => p_operation_type ,
1738 p_orig_system => '-99' ,
1739 p_comments => p_comments ,
1740 p_operation_id => p_operation_id ,
1741 p_constraint_set_id => p_constraint_set_id
1742 );
1743 --
1744 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1745 RAISE FND_API.G_EXC_ERROR ;
1746 END IF ;
1747 --
1748 retcode := 0 ;
1749
1750 COMMIT WORK;
1751 --
1752 EXCEPTION
1753 --
1754 WHEN FND_API.G_EXC_ERROR THEN
1755 --
1756 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1757 p_print_header => FND_API.G_TRUE ) ;
1758 retcode := 2 ;
1759 --
1760 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1761 --
1762 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1763 p_print_header => FND_API.G_TRUE ) ;
1764 retcode := 2 ;
1765 --
1766 WHEN OTHERS THEN
1767 --
1768 --
1769 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1770 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,
1771 l_api_name ) ;
1772 END IF ;
1773 --
1774 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1775 p_print_header => FND_API.G_TRUE ) ;
1776 retcode := 2 ;
1777 --
1778 END Submit_BR ;
1779 /*---------------------------------------------------------------------------*/
1780
1781 /*===========================================================================+
1782 | PROCEDURE No_Process_Defined |
1783 +===========================================================================*/
1784 --
1785 -- This API is called from the PSB default account generator process. This
1786 -- API simply returns an error message specifying that the default process is
1787 -- being used without proper customization.
1788 --
1789 PROCEDURE No_Process_Defined
1790 (
1791 itemtype IN VARCHAR2,
1792 itemkey IN VARCHAR2,
1793 actid IN NUMBER,
1794 funcmode IN VARCHAR2,
1795 result OUT NOCOPY VARCHAR2
1796 )
1797 IS
1798 --
1799 l_error_msg VARCHAR2(2000);
1800 --
1801 BEGIN
1802
1803 IF funcmode <> 'RUN' THEN
1804 result := null;
1805 RETURN;
1806 END IF;
1807
1808 fnd_message.set_name('PSB', 'PSB_POETA_AG_PROCESS_UNDEFINED') ;
1809 l_error_msg := fnd_message.get_encoded ;
1810
1811 wf_engine.SetItemAttrText( itemtype => itemtype,
1812 itemkey => itemkey,
1813 aname => 'ERROR_MESSAGE',
1814 avalue => l_error_msg
1815 );
1816
1817 result := 'COMPLETE:FAILURE';
1818 RETURN;
1819
1820 EXCEPTION
1821
1822 WHEN OTHERS THEN
1823 --
1824 -- Error message routine for debugging.
1825 --
1826 wf_core.context( pkg_name => 'PSB_Workflow_Pvt ' ,
1827 proc_name => 'No_Process_Defined' ,
1828 arg1 => 'Error: No valid process defined.' ,
1829 arg2 => null ,
1830 arg3 => null ,
1831 arg4 => null ,
1832 arg5 => null
1833 );
1834 RAISE;
1835 --
1836 END No_Process_Defined ;
1837 /*---------------------------------------------------------------------------*/
1838
1839
1840
1841 /*---------------------------DEBUG INFORMATION-------------------------------*/
1842 PROCEDURE Add_Debug_Info
1843 (
1844 p_string IN VARCHAR2
1845 )
1846 IS
1847 BEGIN
1848 -- Add to g_dbug only if there is no overflow.
1849 IF length(g_dbug || g_chr10 || p_string) <= 32767 THEN
1850 g_dbug := g_dbug || g_chr10 || p_string ;
1851 END IF;
1852 END Add_Debug_Info;
1853
1854
1855 -- This Module is used to retrieve Debug Information.
1856 FUNCTION get_debug RETURN VARCHAR2
1857 IS
1858 BEGIN
1859 RETURN (g_dbug);
1860 END get_debug;
1861 /*---------------------------------------------------------------------------*/
1862
1863
1864 /*===========================================================================+
1865 | PROCEDURE pd (Private) |
1866 +===========================================================================*/
1867 --
1868 -- Private procedure to print debug info. The name is tried to keep as
1869 -- short as possible for better documentaion.
1870 --
1871 PROCEDURE pd
1872 (
1873 p_message IN VARCHAR2
1874 )
1875 IS
1876 --
1877 BEGIN
1878
1879 IF g_debug_flag = 'Y' THEN
1880 NULL;
1881 -- DBMS_OUTPUT.Put_Line('PSBWKFLB : ' || p_message) ;
1882 END IF;
1883
1884 END pd ;
1885 /*---------------------------------------------------------------------------*/
1886
1887
1888 END PSB_Workflow_Pvt ;