1 PACKAGE BODY PSB_VALIDATE_ACCT_PVT AS
2 /* $Header: PSBVVACB.pls 120.5 2006/01/09 06:07:52 maniskum ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_VALIDATE_ACCT_PVT';
5
6 -- Table to store active segments for a given chart of accounts.
7 TYPE Active_Segments_tbl_type IS TABLE OF VARCHAR2(10)
8 INDEX BY BINARY_INTEGER;
9 -- To store total number of active segments for the current chart of
10 -- accounts..
11 g_total_active_segments NUMBER := 0;
12
13 -- To store all active segments for the chart of accounts.
14 g_active_segments_tbl Active_Segments_tbl_type;
15
16 -- WHO columns variables
17 --
18 g_current_date DATE := sysdate ;
19 g_current_user_id NUMBER := NVL(Fnd_Global.User_Id , 0) ;
20 g_current_login_id NUMBER := NVL(Fnd_Global.Login_Id, 0) ;
21
22 /* ----------------------------------------------------------------------- */
23 /* */
24 /* Private Function and Procedure Declaration */
25 /* */
26 /* ----------------------------------------------------------------------- */
27
28 -- To perform range check with only the active segments
29 FUNCTION Get_Active_Segments
30 ( p_chart_of_accounts_id IN NUMBER
31 ) RETURN BOOLEAN;
32
33 -- Check if a CCID falls within an account position set
34 FUNCTION Check_APS(
35 p_aps_id IN NUMBER,
36 p_code_combination_id IN NUMBER
37 ) RETURN BOOLEAN;
38
39
40 -- Check if a ccid falls within segx_high and segx_low in account position lines
41 FUNCTION Check_Account_Line(
42 p_aps_line_id IN NUMBER,
43 p_code_combination_id IN NUMBER
44 ) RETURN BOOLEAN;
45
46
47 -- Check if CC belongs to budget group using budget account table
48
49 PROCEDURE Find_CCID_In_Budget_Accounts
50 (
51 p_parent_budget_group_id IN NUMBER,
52 p_code_combination_id IN NUMBER,
53 p_startdate_pp IN DATE,
54 p_enddate_cy IN DATE,
55 p_return_code OUT NOCOPY NUMBER,
56 p_budget_group_id OUT NOCOPY NUMBER
57 );
58
59
60
61 -- Check if CC belongs to budget group using range of accounts
62 PROCEDURE Find_CCID_In_Budget_Group
63 (
64 p_parent_budget_group_id IN NUMBER,
65 p_set_of_books_id IN NUMBER,
66 p_flex_code IN NUMBER,
67 p_code_combination_id IN NUMBER,
68 p_startdate_pp IN DATE,
69 p_enddate_cy IN DATE,
70 p_create_budget_account IN VARCHAR2,
71 p_worksheet_id IN NUMBER := FND_API.G_MISS_NUM,
72 p_return_code OUT NOCOPY NUMBER,
73 p_budget_group_id OUT NOCOPY NUMBER
74 ) ;
75
76
77
78
79 /* ----------------------------------------------------------------------- */
80
81
82
83
84 PROCEDURE Validate_Account
85 (
86 p_api_version IN NUMBER,
87 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
88 p_commit IN VARCHAR2 := FND_API.G_FALSE,
89 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
90 p_return_status OUT NOCOPY VARCHAR2,
91 p_msg_count OUT NOCOPY NUMBER,
92 p_msg_data OUT NOCOPY VARCHAR2,
93 --
94 p_parent_budget_group_id IN NUMBER,
95 p_startdate_pp IN DATE,
96 p_enddate_cy IN DATE,
97 p_set_of_books_id IN NUMBER,
98 p_flex_code IN NUMBER,
99 p_create_budget_account IN VARCHAR2 := FND_API.G_FALSE,
100 p_concatenated_segments IN VARCHAR2 := FND_API.G_MISS_CHAR,
101 p_worksheet_id IN NUMBER := FND_API.G_MISS_NUM,
102 p_in_ccid IN NUMBER := FND_API.G_MISS_NUM,
103 p_out_ccid OUT NOCOPY NUMBER,
104 p_budget_group_id OUT NOCOPY NUMBER
105 )
106 IS
107 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Account';
108 l_api_version CONSTANT NUMBER := 1.0;
109 --
110 l_return_status VARCHAR2(1);
111 --
112 l_ccid NUMBER;
113 l_return_code NUMBER;
114 l_budget_group_id NUMBER;
115 l_concat_segments VARCHAR2(1000);
116
117
118
119 BEGIN
120 /* algorithm:
121
122 Get the ccid for the input code combination using flex api
123 if ccid is not returned raise error
124 if ccid is returned, check if ccid already exists in budget accounts table
125 if ccid exists in budget accounts table then
126 check if ccid belongs to the budget group or
127 to any budget group below that in the budget group hrchy
128 if yes -- Account is valid
129 else raise error - account does not belong to the budget group
130
131 else -- this is a valid new account
132 - check if ccid belongs to the budget group or
133 to any budget group below that in the budget group hrchy using range of accounts
134 if ccid belongs to the budget group
135 create records in budget accounts table for the budget group
136 else raise error --ccid does not belong to the budget group
137
138 */
139
140
141 --
142 SAVEPOINT Validate_Account_Pvt ;
143 -- Standard call to check for call compatibility.
144 IF not FND_API.Compatible_API_Call (l_api_version,
145 p_api_version,
146 l_api_name,
147 G_PKG_NAME)
148 THEN
149 raise FND_API.G_EXC_UNEXPECTED_ERROR;
150 END IF;
151 --
152
153 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
154 FND_MSG_PUB.initialize ;
155 END IF;
156 --
157 p_return_status := FND_API.G_RET_STS_SUCCESS ;
158
159 -- Call Flex API and get ccid
160
161 IF p_in_ccid = FND_API.G_MISS_NUM THEN
162
163 IF p_concatenated_segments = FND_API.G_MISS_CHAR THEN
164 FND_MESSAGE.Set_Name('PSB', 'PSB_INVALID_ARGUMENT');
165 FND_MESSAGE.Set_Token('ROUTINE', 'PSB_VALIDATE_ACCT_PVT.Validate_Account');
166 FND_MSG_PUB.Add;
167 RAISE FND_API.G_EXC_ERROR;
168 END IF;
169
170 l_ccid := FND_FLEX_EXT.Get_CCID
171 (application_short_name => 'SQLGL',
172 key_flex_code => 'GL#',
173 structure_number => p_flex_code,
174 validation_date => to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS'),
175 concatenated_segments => p_concatenated_segments);
176
177 IF l_ccid = 0 then
178 FND_MESSAGE.Set_Name('PSB', 'PSB_INVALID_CC');
179 FND_MSG_PUB.Add;
180 RAISE FND_API.G_EXC_ERROR ;
181 END IF;
182
183
184 ELSE
185 -- Note: no validation is done if ccid is input
186 l_ccid := p_in_ccid;
187
188 END IF;
189 -- check if this account is a summary or detail posting not allowed acct
190 /*For Bug No : 2715705 Start*/
191 --changed validation from posting_allowed flag to budgeting_allowed flag
192 --changed the message also accordingly
193 IF l_ccid > 0 THEN
194 for cc_rec in
195 (select detail_budgeting_allowed_flag, summary_flag
196 from gl_code_combinations
197 where code_combination_id = l_ccid
198 )
199 loop
200 if cc_rec.detail_budgeting_allowed_flag = 'N'
201 or cc_rec.summary_flag = 'Y' then
202
203 /* Bug 3692601 Start */
204 l_concat_segments := FND_FLEX_EXT.Get_Segs
205 (application_short_name => 'SQLGL',
206 key_flex_code => 'GL#',
207 structure_number => p_flex_code,
208 combination_id => l_ccid);
209 /* Bug 3692601 End */
210
211 FND_MESSAGE.Set_Name('PSB', 'PSB_SUMMARY_DETAIL_BUDGETING');
212 /* Bug 3692601 Start */
213 FND_MESSAGE.Set_Token('ACCOUNT', l_concat_segments);
214 /* Bug 3692601 End */
215 FND_MSG_PUB.Add;
216 RAISE FND_API.G_EXC_ERROR ;
217 end if;
218 end loop;
219 END IF;
220 /*For Bug No : 2715705 End*/
221
222 p_out_ccid := l_ccid;
223
224 l_return_code := 0;
225 Find_CCID_In_Budget_Accounts
226 (
227 p_parent_budget_group_id => p_parent_budget_group_id ,
228 p_code_combination_id => l_ccid,
229 p_startdate_pp => p_startdate_pp,
230 p_enddate_cy => p_enddate_cy,
231 p_return_code => l_return_code,
232 p_budget_group_id => l_budget_group_id
233 );
234
235
236 -- 0 - Valid CCID that exists in budget accounts and belongs to the budget group
237 -- 1 - Valid CCID that exists in budget accounts and does not belong to the budget group
238 IF l_return_code = 0 THEN
239 p_budget_group_id := l_budget_group_id;
240 RETURN;
241 ELSIF l_return_code = 1 THEN
242 FND_MESSAGE.Set_Name('PSB', 'PSB_INVALID_BG_CC');
243 FND_MSG_PUB.Add;
244 RAISE FND_API.G_EXC_ERROR ;
245 END IF;
246
247 -- Return code status is 2 - CCID does not exist in Budget Accounts Table
248
249 -- reset l_return_code
250 l_return_code := 0;
251 -- If valid account also add accounts to Budget Accounts Table
252 Find_CCID_In_Budget_Group
253 (
254 p_parent_budget_group_id => p_parent_budget_group_id,
255 p_set_of_books_id => p_set_of_books_id,
256 p_flex_code => p_flex_code,
257 p_code_combination_id => l_ccid,
258 p_startdate_pp => p_startdate_pp,
259 p_enddate_cy => p_enddate_cy,
260 p_create_budget_account => p_create_budget_account,
261 p_worksheet_id => p_worksheet_id,
262 p_return_code => l_return_code,
263 p_budget_group_id => l_budget_group_id
264 ) ;
265
266 IF l_return_code = 0 THEN
267 p_budget_group_id := l_budget_group_id;
268 RETURN;
269 ELSIF l_return_code = 1 THEN
270 FND_MESSAGE.Set_Name('PSB', 'PSB_INVALID_BG_CC');
271 FND_MSG_PUB.Add;
272 RAISE FND_API.G_EXC_ERROR ;
273 END IF;
274
275 EXCEPTION
276 --
277 WHEN FND_API.G_EXC_ERROR THEN
278 --
279 ROLLBACK TO Validate_Account_Pvt ;
280 p_return_status := FND_API.G_RET_STS_ERROR;
281 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
282 p_data => p_msg_data );
283 --
284 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
285 --
286 ROLLBACK TO Validate_Account_Pvt ;
287 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
288 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
289 p_data => p_msg_data );
290 --
291 WHEN OTHERS THEN
292 --
293 ROLLBACK TO Validate_Account_Pvt ;
294 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
295 --
296 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
297 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
298 l_api_name);
299 END if;
300 --
301 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
302 p_data => p_msg_data );
303 --
304
305 END Validate_Account;
306
307 /* ----------------------------------------------------------------------- */
308 PROCEDURE Find_CCID_In_Budget_Accounts
309 (
310 p_parent_budget_group_id IN NUMBER,
311 p_code_combination_id IN NUMBER,
312 p_startdate_pp IN DATE,
313 p_enddate_cy IN DATE,
314 p_return_code OUT NOCOPY NUMBER,
315 p_budget_group_id OUT NOCOPY NUMBER
316 ) IS
317
318 l_cc_in_ba_flag VARCHAR2(1);
319 l_cc_in_bg_flag VARCHAR2(1);
320
321 BEGIN
322 -- Returns 0 in p_return_code if ccid exists in Budget Accounts Table and belongs to the budget group
323 -- Returns 1 in p_return_code if ccid exists in Budget Accounts Table but doesn't belong to the budget group
324 -- Returns 2 in p_return_code if ccid doesn't exist in Budget Accounts Table
325
326 -- Returns the specific budget group the ccid belongs to.
327 -- This is valid only if p_return_code is 0
328
329 l_cc_in_ba_flag := FND_API.G_FALSE;
330 FOR ba_rec IN
331 (select 1 from PSB_BUDGET_ACCOUNTS
332 where code_combination_id = p_code_combination_id)
333 LOOP
334 l_cc_in_ba_flag := FND_API.G_TRUE;
335 EXIT;
336 END LOOP;
337
338 IF l_cc_in_ba_flag = FND_API.G_FALSE THEN
339 p_return_code := 2; --ccid doesn't exist in Budget Accounts Table
340 RETURN;
341 END IF;
342
343 -- ccid is in the budget accounts table
344 -- now check if it belongs to the budget group
345 l_cc_in_bg_flag := FND_API.G_FALSE;
346 p_budget_group_id := 0;
347 FOR ba_rec IN
348 (
349 select sr.budget_group_id
350 from psb_set_relations_v sr,
351 psb_budget_accounts ba
352 where sr.budget_group_id in
353 (select budget_group_id
354 from PSB_BUDGET_GROUPS
355 where budget_group_type = 'R'
356 and (p_startdate_pp is null or effective_start_date <= p_startdate_pp)
357 and (effective_end_date is null or effective_end_date >= p_enddate_cy)
358 start with budget_group_id = p_parent_budget_group_id
359 connect by prior budget_group_id = parent_budget_group_id)
360 and sr.account_position_set_id = ba.account_position_set_id
361 and ba.code_combination_id = p_code_combination_id
362 )
363 LOOP
364 p_budget_group_id := ba_rec.budget_group_id;
365 l_cc_in_bg_flag := FND_API.G_TRUE;
366 --dbms_output.put.line('found ccid in bg');
367 EXIT;
368 END LOOP;
369
370 IF l_cc_in_bg_flag = FND_API.G_TRUE THEN
371 p_return_code := 0; -- CCID exists and belongs to the budget group
372 ELSE
373 p_return_code := 1; -- CCID exists and does not belong to the budget group
374 END IF;
375
376 END Find_CCID_In_Budget_Accounts;
377
378
379
380
381
382 /* ----------------------------------------------------------------------- */
383 PROCEDURE Find_CCID_In_Budget_Group
384 (
385 p_parent_budget_group_id IN NUMBER,
386 p_set_of_books_id IN NUMBER,
387 p_flex_code IN NUMBER,
388 p_code_combination_id IN NUMBER,
389 p_startdate_pp IN DATE,
390 p_enddate_cy IN DATE,
391 p_create_budget_account IN VARCHAR2,
392 p_worksheet_id IN NUMBER := FND_API.G_MISS_NUM,
393 p_return_code OUT NOCOPY NUMBER,
394 p_budget_group_id OUT NOCOPY NUMBER
395 )
396 IS
397 l_cc_in_bg_flag VARCHAR2(1);
398 l_root_budget_group_id NUMBER;
399 /*For Bug No : 2026323 Start*/
400 l_ccid_exists BOOLEAN;
401 CURSOR c_budget_account(aps_id NUMBER, ccid NUMBER) IS
402 SELECT 1
403 FROM PSB_BUDGET_ACCOUNTS
404 WHERE account_position_set_id = aps_id
405 AND code_combination_id = ccid;
406 /*For Bug No : 2026323 End*/
407
408 BEGIN
409
410 -- Returns 0 in p_return_code if ccid belongs to the budget group
411 -- Returns 1 in p_return_code if ccid doesn't belong to the budget group
412
416 /* algorithm :
413 -- Returns the specific budget group the ccid belongs to.
414 -- This is valid only if p_return_code is 0
415
417
418 Get account position sets(aps) that belong to the budget group
419 and to the budget groups below that in the budget group hrcy
420
421 For each aps loop
422 Get aps lines that are excluded
423 For each aps line that is excluded loop
424 call function check account line(apslineid, ccid);
425 ( this will return true if the ccid falls with in the segment range)
426 if function returns true then
427 -- the ccid is excluded
428 exit
429 else continue with the next aps line
430 end loop
431
432 if the function never returned true for any excluded aps line then
433 -- Check for inclusion
434 Get aps lines that are included
435 For each aps line that is included loop
436 call function check account line(apslineid, ccid);
437 ( this will return true if the ccid falls with in the segment range)
438 if function returns true then
439 -- the ccid is included
440 exit
441 else continue with the next aps line
442 end loop
443
444 if the function never returned true for any included aps line then
445 the ccid does not belong to the account position set
446
447
448 if the function returned true then,
449 create a line in budget accounts table with the ccid and set id
450 proceed to the next aps
451
452 end loop
453
454 If the function returned true
455 Get the Position and Non Position Account Position Sets
456 from the Root Budget Group and include the ccid in one of them
457 based on segment values.
458
459 If the function call made in inclusion loop never returned true for all aps
460 then the ccid does not belong to the budget group, raise an error -- return false
461
462 Added on 28th May 1999
463 Get the Account Position Sets for the Allocation Rule Set assigned to the
464 Global worksheet and add the new account to the account position set
465
466 */
467
468
469 -- Find active segments for the chart of accounts
470 -- this is used by Check Range
471 IF NOT Get_Active_Segments( p_flex_code ) THEN
472 raise FND_API.G_EXC_UNEXPECTED_ERROR;
473 END IF;
474
475 l_cc_in_bg_flag := FND_API.G_FALSE;
476
477 -- Get account position sets for the budget group using set relations
478 FOR l_set_rec IN
479 (
480 SELECT aps.account_position_set_id, sr.budget_group_id
481 FROM psb_account_position_sets aps, psb_set_relations sr
482 WHERE sr.account_position_set_id = aps.account_position_set_id
483 and aps.account_or_position_type = 'A'
484 and sr.budget_group_id in (select budget_group_id
485 from PSB_BUDGET_GROUPS
486 where budget_group_type = 'R'
487 and effective_start_date <= p_startdate_pp
488 and (effective_end_date is null or effective_end_date >= p_enddate_cy)
489 start with budget_group_id = p_parent_budget_group_id
490 connect by prior budget_group_id = parent_budget_group_id)
491 )
492 LOOP
493 --dbms_output.put_line(' bg : ' || to_char(l_set_rec.budget_group_id)
494 -- || ' aps :'||to_char(l_set_rec.account_position_set_id));
495
496 IF Check_APS(l_set_rec.account_position_set_id,
497 p_code_combination_id
498 ) THEN
499
500 l_cc_in_bg_flag := FND_API.G_TRUE;
501 p_budget_group_id := l_set_rec.budget_group_id;
502
503 -- create record in budget accounts table only if flag is on
504 IF p_create_budget_account = FND_API.G_TRUE then
505
506 INSERT INTO psb_budget_accounts(
507 account_position_set_id,
508 set_of_books_id,
509 code_combination_id,
510 last_update_date,
511 last_updated_by,
512 last_update_login,
513 created_by,
514 creation_date) values
515 (l_set_rec.account_position_set_id,
516 p_set_of_books_id,
517 p_code_combination_id,
518 g_current_date,
519 g_current_user_id,
520 g_current_login_id,
521 g_current_user_id,
522 g_current_date);
523 END IF;
524
525 EXIT; -- no need to continue as only one aps can have this ccid in the
526 -- budget group hierarchy
527
528 END IF;
529
530
531 END LOOP;
532
533
534 -- Include CCID in Position or Non Position APS found on Root Budget Group
535 IF l_cc_in_bg_flag = FND_API.G_TRUE THEN
536
537 -- Get the Root Budget Group for the Budget Group
538 FOR l_bg_rec IN
539 (
540 SELECT nvl(root_budget_group_id, budget_group_id) root_budget_group_id
541 FROM psb_budget_groups_v
542 WHERE budget_group_id = p_parent_budget_group_id
543 )
544 LOOP
545 l_root_budget_group_id := l_bg_rec.root_budget_group_id;
546 END LOOP;
547
548 FOR l_set_rec IN
549 (
550 SELECT aps.account_position_set_id, bg.budget_group_id
551 FROM psb_account_position_sets aps, psb_budget_groups bg
552 WHERE bg.budget_group_id = l_root_budget_group_id
553 AND aps.account_position_set_id IN
557 IF Check_APS(l_set_rec.account_position_set_id,
554 ( bg.ps_account_position_set_id, bg.nps_account_position_set_id)
555 )
556 LOOP
558 p_code_combination_id
559 ) THEN
560
561 -- create record in budget accounts table only if flag is on
562 IF p_create_budget_account = FND_API.G_TRUE then
563
564 INSERT INTO psb_budget_accounts(
565 account_position_set_id,
566 set_of_books_id,
567 code_combination_id,
568 last_update_date,
569 last_updated_by,
570 last_update_login,
571 created_by,
572 creation_date) values
573 (l_set_rec.account_position_set_id,
574 p_set_of_books_id,
575 p_code_combination_id,
576 g_current_date,
577 g_current_user_id,
578 g_current_login_id,
579 g_current_user_id,
580 g_current_date);
581 END IF;
582
583 EXIT; -- no need to continue as only one aps (position or non position) can have this CCID
584 END IF;
585
586 END LOOP;
587 END IF;
588
589 -- Added on May 29, 1999
590 -- Add CCIDs for Account Position Sets that belong the Allocation set assigned
591 -- to the Worksheet
592 IF l_cc_in_bg_flag = FND_API.G_TRUE and ( p_worksheet_id <> FND_API.G_MISS_NUM ) THEN
593
594 FOR alloc_aps_rec IN
595 (
596 SELECT worksheet_id, w.allocrule_set_id , sr.account_position_set_id,
597 sr.allocation_rule_id, pe.name ,pea.entity_set_id, pea.entity_id,
598 pe.entity_subtype,
599 pe.allocation_type
600 FROM psb_worksheets w,
601 psb_entity_assignment pea,
602 psb_entity pe,
603 psb_set_relations sr
604 WHERE pe.entity_type = 'ALLOCRULE'
605 AND pea.entity_set_id = w.allocrule_set_id
606 AND pea.entity_id = pe.entity_id
607 AND sr.allocation_rule_id = pea.entity_id
608 AND worksheet_id = p_worksheet_id
609 )
610 LOOP
611 /*For Bug No 2026323 Start*/
612 l_ccid_exists := FALSE;
613 FOR c_budget_account_rec IN c_budget_account(alloc_aps_rec.account_position_set_id, p_code_combination_id) LOOP
614 l_ccid_exists := TRUE;
615 END LOOP;
616 IF NOT l_ccid_exists THEN
617 BEGIN
618 /*For Bug No 2026323 End*/
619 IF Check_APS(alloc_aps_rec.account_position_set_id,
620 p_code_combination_id
621 ) THEN
622
623 -- create record in budget accounts table only if flag is on
624 IF p_create_budget_account = FND_API.G_TRUE then
625
626 INSERT INTO psb_budget_accounts(
627 account_position_set_id,
628 set_of_books_id,
629 code_combination_id,
630 last_update_date,
631 last_updated_by,
632 last_update_login,
633 created_by,
634 creation_date) values
635 (alloc_aps_rec.account_position_set_id,
636 p_set_of_books_id,
637 p_code_combination_id,
638 g_current_date,
639 g_current_user_id,
640 g_current_login_id,
641 g_current_user_id,
642 g_current_date);
643 END IF;
644
645
646 END IF;
647 /*For Bug No 2026323 Start*/
648 END;
649 END IF;
650 /*For Bug No 2026323 End*/
651 END LOOP;
652 END IF;
653
654
655
656
657
658 IF l_cc_in_bg_flag = FND_API.G_TRUE THEN
659 p_return_code := 0;
660 ELSE
661 p_return_code := 1;
662 END IF;
663
664
665
666
667
668
669 END Find_CCID_In_Budget_Group;
670
671 /* ----------------------------------------------------------------------- */
672 FUNCTION Check_APS(
673 p_aps_id IN NUMBER,
674 p_code_combination_id IN NUMBER
675 )
676 RETURN BOOLEAN IS
677
678 l_excluded_flag VARCHAR2(1) := FND_API.G_FALSE;
679 l_included_flag VARCHAR2(1) := FND_API.G_FALSE;
680
681 BEGIN
682 -- First check exclusion
683
684 FOR l_line_e_rec IN
685 (
686 SELECT line_sequence_id, include_or_exclude_type
687 FROM psb_account_position_set_lines
688 WHERE account_position_set_id = p_aps_id
689 and include_or_exclude_type = 'E'
690 )
691 LOOP
692 --
693
694 IF Check_Account_Line(l_line_e_rec.line_sequence_id,
695 p_code_combination_id
696 ) THEN
697 l_excluded_flag := FND_API.G_TRUE;
698 EXIT; -- if it is excluded, no need to proceed
699 END IF;
700
701 END LOOP;
702
703 -- Check for Inclusion only if it is not excluded
704 IF l_excluded_flag = FND_API.G_FALSE then
705 FOR l_line_i_rec IN
706 (
707 SELECT line_sequence_id, include_or_exclude_type
708 FROM psb_account_position_set_lines
709 WHERE account_position_set_id = p_aps_id
710 and include_or_exclude_type = 'I'
711 )
712 LOOP
713 --
714 IF Check_Account_Line(l_line_i_rec.line_sequence_id,
715 p_code_combination_id
716 ) THEN
717 l_included_flag := FND_API.G_TRUE;
718
722 END LOOP;
719 EXIT; -- if it is included, no need to proceed
720 END IF;
721
723 END IF;
724
725 IF l_included_flag = FND_API.G_TRUE THEN
726 RETURN TRUE;
727 ELSE
728 RETURN FALSE;
729 END IF;
730
731 END Check_APS;
732
733
734 /* ----------------------------------------------------------------------- */
735 FUNCTION Check_Account_Line(
736 p_aps_line_id IN NUMBER,
737 p_code_combination_id IN NUMBER
738 ) RETURN BOOLEAN IS
739
740 l_sql VARCHAR2(5000);
741 l_sql_tmp VARCHAR2(4500);
742
743 l_rec_count INTEGER;
744 l_ignore INTEGER;
745 l_cursor_id INTEGER;
746 BEGIN
747
748
749 l_sql := ' SELECT count(*) FROM gl_code_combinations glcc, ' ||
750 ' psb_account_position_set_lines apsl ' ||
751 ' WHERE glcc.code_combination_id = :ccid ' ||
752 ' AND apsl.line_sequence_id = :line_sequence_id ' ||
753 ' AND glcc.enabled_flag = ''Y''' ||
754 ' AND glcc.detail_budgeting_allowed_flag = ''Y''' ||
755 ' AND glcc.template_id is null ' ;
756
757
758
759 FOR i in 1..g_total_active_segments
760 LOOP
761 --
762 l_sql_tmp := l_sql_tmp ||
763 ' AND glcc.' || g_active_segments_tbl(i) ||
764 ' BETWEEN apsl.' || g_active_segments_tbl(i) ||
765 '_low AND apsl.' || g_active_segments_tbl(i) ||
766 '_high';
767 END LOOP;
768 --
769 l_sql := l_sql ||l_sql_tmp;
770
771 l_cursor_id := dbms_sql.open_cursor;
772
773 -- Parsing the statement.
774 dbms_sql.parse(l_cursor_id, l_sql, dbms_sql.v7);
775 -- Bind input variables
776 dbms_sql.bind_variable(l_cursor_id, ':ccid',
777 p_code_combination_id);
778
779 dbms_sql.bind_variable(l_cursor_id, ':line_sequence_id',
780 p_aps_line_id);
781
782 -- define output varaible
783 dbms_sql.define_column(l_cursor_id, 1, l_rec_count);
784 -- execute
785 l_ignore := dbms_sql.execute(l_cursor_id);
786 -- fetch
787 l_ignore := dbms_sql.fetch_rows(l_cursor_id );
788 -- retrieve the value
789 dbms_sql.column_value(l_cursor_id,1,l_rec_count);
790 -- close the cursor
791 dbms_sql.close_cursor(l_cursor_id);
792
793 IF l_rec_count > 0 THEN
794 RETURN TRUE;
795 ELSE
796 RETURN FALSE;
797 END IF;
798
799 END Check_Account_Line;
800
801 --Copy of Get_Active_Segments function in PSB_Account_Position_Set_PVT
802 /*===========================================================================+
803 | FUNCTION Get_Active_Segments (Private) |
804 +===========================================================================*/
805 --
806 -- This Private function finds active segments in gl_code_combinations table
807 -- and stores those in a global table g_active_segments_tab.
808 --
809
810 FUNCTION Get_Active_Segments( p_chart_of_accounts_id IN NUMBER )
811 RETURN BOOLEAN
812 IS
813 /* Start bug #4924031 */
814 l_id_flex_code fnd_id_flex_structures.id_flex_code%TYPE;
815 l_application_id fnd_id_flex_structures.application_id%TYPE;
816 l_yes_flag VARCHAR2(1);
817 /* End bug #4924031 */
818
819 BEGIN
820 --
821 -- Initialize for each chart of accounts.
822 --
823 g_total_active_segments := 0;
824
825 /* Start bug #4924031 */
826 l_id_flex_code := 'GL#';
827 l_application_id := 101;
828 l_yes_flag := 'Y';
829 /* End bug #4924031 */
830
831
832 FOR l_flex_rec IN
833 (
834 SELECT seg.application_column_name
835 FROM fnd_id_flex_structures str, fnd_id_flex_segments seg
836 WHERE str.application_id = l_application_id -- bug #4924031
837 AND str.id_flex_code = l_id_flex_code -- bug #4924031
838 AND str.id_flex_num = p_chart_of_accounts_id
839 AND str.id_flex_code = seg.id_flex_code
840 AND str.id_flex_num = seg.id_flex_num
841 AND seg.enabled_flag = l_yes_flag -- bug #4924031
842 AND seg.application_id = str.application_id -- bug #4924031
843 )
844 LOOP
845 g_total_active_segments := g_total_active_segments + 1;
846 --
847 g_active_segments_tbl(g_total_active_segments) :=
848 l_flex_rec.application_column_name;
849 --
850 END LOOP;
851 --
852 --dbms_output.Put_Line('L'||g_active_segments_tbl(g_total_active_segments));
853
854 RETURN (TRUE);
855 --
856 EXCEPTION
857 WHEN OTHERS THEN
858 --
859 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
860 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
861 'Get_Active_Segments' );
862 END if;
863 --
864 RETURN (FALSE);
865 --
866 END Get_Active_Segments;
867 /*---------------------------------------------------------------------------*/
868
869
870 END PSB_VALIDATE_ACCT_PVT;
[Home] [Help]