[Home] [Help]
PACKAGE BODY: APPS.PSB_GL_INTERFACE_PVT
Source
1 PACKAGE BODY PSB_GL_Interface_PVT AS
2 /* $Header: PSBVOGLB.pls 120.25 2007/10/19 08:52:36 rkotha ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_GL_Interface_PVT';
5
6 g_posting_completed BOOLEAN := FALSE;
7 g_budget_by_position VARCHAR2(1);
8 g_set_of_books_id NUMBER(15);
9 g_set_of_books_name VARCHAR2(30);
10 g_chart_of_accounts_id NUMBER(15);
11 g_flex_mapping_set_id NUMBER(15);
12 g_gl_calendar VARCHAR2(30);
13 g_currency_code VARCHAR2(30);
14 g_gl_map VARCHAR2(30);
15 g_fund_segment VARCHAR2(30);
16 g_budgetary_control VARCHAR2(1);
17 g_average_balances VARCHAR2(1);
18 g_templ_seg_val FND_FLEX_EXT.SegmentArray;
19 g_post_to_all VARCHAR2(1) := FND_API.G_FALSE; -- Bug#4310411
20 g_budget_source_type VARCHAR2(2); -- Bug#4310411
21 g_budget_year_type_id NUMBER; -- Bug#4310411
22
23
24 g_offset_revision VARCHAR2(1);
25 g_permanent_revision VARCHAR2(1);
26 g_budget_set_id NUMBER(15);
27 g_revision_type VARCHAR2(1);
28
29 g_user_id NUMBER(15);
30 g_login_id NUMBER(15);
31 g_program_id NUMBER(15);
32 g_request_id NUMBER(15);
33 /*FOR Bug No : 2760443 Start*/
34 --increased the size FROM 15 to 45 FOR NLS compliance
35 --g_org_code VARCHAR2(45); -- Bug#4310411
36 /*FOR Bug No : 2760443 END*/
37 g_source_name VARCHAR2(25);
38 g_category_name VARCHAR2(25);
39 g_batch_name VARCHAR2(100); -- Bug#4310411
40 g_batch_description VARCHAR2(100); -- Bug#4310411
41 g_je_name VARCHAR2(100); -- Bug#4310411
42 g_je_description VARCHAR2(100); -- Bug#4310411
43 g_budget_year_id NUMBER(15); -- Bug 3029168
44
45 -- SLA variables
46 g_sob_list XLA_GL_TRANSFER_PKG.T_SOB_LIST := XLA_GL_TRANSFER_PKG.T_SOB_LIST();
47 g_ae_category XLA_GL_TRANSFER_PKG.t_ae_category;
48
49 --Reporting Sets of books list
50
51 TYPE HdrArrayRec IS RECORD
52 (ae_header_id NUMBER,
53 budget_version_id NUMBER,
54 dual_posting_type VARCHAR2(1));
55
56 TYPE HdrArrayTbl IS TABLE OF HdrArrayRec
57 INDEX BY BINARY_INTEGER;
58
59 g_header_ids HdrArrayTbl;
60 g_header_count NUMBER;
61
62 TYPE LineArrayRec IS RECORD
63 ( ae_line_id NUMBER,
64 code_combination_id NUMBER,
65 service_package_id NUMBER,
66 currency_code VARCHAR2(15),
67 ae_line_type_code VARCHAR2(30),
68 delete_flag VARCHAR2(1),
69 reference2 VARCHAR2(240),
70 -- FOR bug no 3347237
71 reference3 VARCHAR2(240)
72 ) ;
73
74 TYPE LineArrayTbl IS TABLE OF LineArrayRec
75 INDEX BY BINARY_INTEGER;
76
77 g_line_ids LineArrayTbl;
78 g_line_count NUMBER;
79
80 TYPE SegNamArray IS TABLE OF VARCHAR2(9)
81 INDEX BY BINARY_INTEGER;
82
83 g_seg_name SegNamArray;
84 g_num_segs NUMBER;
85
86 -- FOR Message reporting
87 TYPE TokNameArray IS TABLE OF VARCHAR2(100)
88 INDEX BY BINARY_INTEGER;
89 --
90 TYPE TokValArray IS TABLE OF VARCHAR2(1000)
91 INDEX BY BINARY_INTEGER;
92 --
93 -- Bug#4310411 Start.
94 -- Declare three TYPES represnting required datatypes
95 TYPE Number_Tbl_Type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
96 TYPE Char_Tbl_Type IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
97 TYPE Date_Tbl_Type IS TABLE OF DATE INDEX BY PLS_INTEGER;
98 -- Bug#4310411 End.
99
100 no_msg_tokens NUMBER := 0;
101 msg_tok_names TokNameArray;
102 msg_tok_val TokValArray;
103 /*---------------------------------------------------------------------------*/
104
105
106 /*===========================================================================+
107 | PROCEDURE pd |
108 +===========================================================================*/
109 PROCEDURE pd( p_message IN VARCHAR2)
110 IS
111 BEGIN
112 NULL ;
113 --DBMS_OUTPUT.Put_Line(p_message) ;
114 END pd ;
115 /*---------------------------------------------------------------------------*/
116
117
118 /*---------------------------------------------------------------------------*/
119 PROCEDURE Validate_Funding_Account
120 (x_return_status OUT NOCOPY VARCHAR2,
121 p_event_type VARCHAR2,
122 p_source_id NUMBER,
123 p_stage_sequence NUMBER := NULL,
124 p_budget_year_id NUMBER := NULL,
125 p_gl_budget_set_id NUMBER,
126 p_start_date DATE,
127 x_validation_status IN OUT NOCOPY VARCHAR2
128 );
129
130 PROCEDURE Create_Adopted_Budget
131 (x_return_status OUT NOCOPY VARCHAR2,
132 p_worksheet_id NUMBER,
133 p_budget_stage_id NUMBER,
134 p_budget_year_id NUMBER,
135 p_year_journal VARCHAR2,
136 p_gl_transfer_mode VARCHAR2,
137 p_auto_offset VARCHAR2,
138 p_gl_budget_set_id NUMBER,
139 p_order_by1 VARCHAR2,
140 p_order_by2 VARCHAR2,
141 p_order_by3 VARCHAR2
142 );
143
144 PROCEDURE Message_Token(tokname IN VARCHAR2,
145 tokval IN VARCHAR2);
146
147 PROCEDURE Add_Message( appname IN VARCHAR2,
148 msgname IN VARCHAR2);
149
150 /* ----------------------------------------------------------------------- */
151
152 FUNCTION Find_GL_Budget_Set
153 (p_set_of_books_id IN NUMBER) RETURN NUMBER IS
154
155 l_gl_budget_set_id NUMBER := 0;
156
157 CURSOR c_gl_budget_set
158 IS
159 SELECT gl_budget_set_id
160 FROM PSB_GL_BUDGET_SETS
161 WHERE set_of_books_id = p_set_of_books_id;
162
163 BEGIN
164
165 FOR c_gl_budget_set_rec IN c_gl_budget_set LOOP
166 l_gl_budget_set_id := c_gl_budget_set_rec.gl_budget_set_id;
167 END LOOP;
168
169 RETURN l_gl_budget_set_id;
170
171 END Find_GL_Budget_Set;
172
173 /* ----------------------------------------------------------------------- */
174
175 PROCEDURE Get_Offset_Account
176 (x_return_status OUT NOCOPY VARCHAR2,
177 p_templ_acct VARCHAR2,
178 p_fund VARCHAR2,
179 p_templ_seg IN OUT NOCOPY FND_FLEX_EXT.SegmentArray,
180 p_ccid OUT NOCOPY NUMBER
181 )
182 IS
183
184 l_ccid NUMBER;
185 l_seg_val FND_FLEX_EXT.SegmentArray;
186
187 l_account_found BOOLEAN;
188
189 CURSOR c_templacct
190 IS
191 SELECT code_combination_id
192 FROM psb_fund_balance_accounts
193 WHERE set_of_books_id = g_set_of_books_id
194 AND template_account = 'Y';
195
196 CURSOR c_nontemplacct
197 IS
198 SELECT a.code_combination_id
199 FROM gl_code_combinations a,
200 psb_fund_balance_accounts b
201 WHERE a.code_combination_id = b.code_combination_id
202 AND b.set_of_books_id = g_set_of_books_id
203 AND DECODE(g_fund_segment,'SEGMENT1', SEGMENT1,
204 'SEGMENT2', SEGMENT2,
205 'SEGMENT3', SEGMENT3,
206 'SEGMENT4', SEGMENT4,
207 'SEGMENT5', SEGMENT5,
208 'SEGMENT6', SEGMENT6,
209 'SEGMENT7', SEGMENT7,
210 'SEGMENT8', SEGMENT8,
211 'SEGMENT9', SEGMENT9,
212 'SEGMENT10', SEGMENT10,
213 'SEGMENT11', SEGMENT11,
214 'SEGMENT12', SEGMENT12,
215 'SEGMENT13', SEGMENT13,
216 'SEGMENT14', SEGMENT14,
217 'SEGMENT15', SEGMENT15,
218 'SEGMENT16', SEGMENT16,
219 'SEGMENT17', SEGMENT17,
220 'SEGMENT18', SEGMENT18,
221 'SEGMENT19', SEGMENT19,
222 'SEGMENT20', SEGMENT20,
223 'SEGMENT21', SEGMENT21,
224 'SEGMENT22', SEGMENT22,
225 'SEGMENT23', SEGMENT23,
226 'SEGMENT24', SEGMENT24,
227 'SEGMENT25', SEGMENT25,
228 'SEGMENT26', SEGMENT26,
229 'SEGMENT27', SEGMENT27,
230 'SEGMENT28', SEGMENT28,
231 'SEGMENT29', SEGMENT29,
232 'SEGMENT30', SEGMENT30
233 ) = p_fund;
234
235 BEGIN
236
237 l_account_found := FALSE;
238
239 IF p_templ_acct = 'Y' THEN
240 BEGIN
241 FOR c_templacct_rec IN c_templacct LOOP
242 l_account_found := TRUE;
243 l_ccid := c_templacct_rec.code_combination_id;
244 END LOOP;
245
246 IF NOT l_account_found THEN
247 Add_Message('PSB', 'PSB_GL_BJE_NO_TEMPL_ACCT');
248 RAISE FND_API.G_EXC_ERROR;
249 ELSE
250 BEGIN
251 IF NOT FND_FLEX_EXT.Get_Segments
252 (application_short_name => 'SQLGL',
253 key_flex_code => 'GL#',
254 structure_number => g_chart_of_accounts_id,
255 combination_id => l_ccid,
256 n_segments => g_num_segs,
257 segments => p_templ_seg
258 )
259 THEN
260 FND_MSG_PUB.Add;
261 RAISE FND_API.G_EXC_ERROR;
262 END IF;
263 p_ccid := l_ccid;
264 END;
265 END IF;
266 END;
267 ELSE
268 BEGIN
269 FOR c_nontemplacct_rec IN c_nontemplacct LOOP
270 l_account_found := TRUE;
271 l_ccid := c_nontemplacct_rec.code_combination_id;
272 END LOOP;
273
274 IF NOT l_account_found THEN
275 BEGIN
276 FOR l_index IN 1..g_num_segs LOOP
277 IF g_seg_name(l_index) = g_fund_segment THEN
278 l_seg_val(l_index) := p_fund;
279 ELSE
280 l_seg_val(l_index) := p_templ_seg(l_index);
281 END IF;
282 END LOOP;
283
284 -- A possibility that this ccid AND mapped ccid
285 -- violates cross-validation rule get the new
286 -- ccid of substitued template account
287
288 IF NOT FND_FLEX_EXT.Get_Combination_ID
289 (application_short_name => 'SQLGL',
290 key_flex_code => 'GL#',
291 structure_number => g_chart_of_accounts_id,
292 validation_date => sysdate,
293 n_segments => g_num_segs,
294 segments => l_seg_val,
295 combination_id => p_ccid
296 )
297 THEN
298 FND_MSG_PUB.Add;
299 RAISE FND_API.G_EXC_ERROR;
300 END IF;
301 END;
302 ELSE
303 p_ccid := l_ccid;
304 END IF;
305 END;
306 END IF;
307
308 x_return_status := FND_API.G_RET_STS_SUCCESS;
309
310 EXCEPTION
311 WHEN FND_API.G_EXC_ERROR THEN
312 x_return_status := FND_API.G_RET_STS_ERROR;
313
314 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
315 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
316
317 WHEN OTHERS THEN
318 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
319
320 END Get_Offset_Account;
321
322 /* ----------------------------------------------------------------------- */
323 PROCEDURE Balance_Journal
324 (x_return_status OUT NOCOPY VARCHAR2,
325 p_worksheet_id IN NUMBER,
326 p_period_name IN VARCHAR2,
327 p_GL_budget_set_id IN NUMBER
328 )
329 IS
330
331 l_api_name CONSTANT VARCHAR2(30) := 'Balance_Journal';
332 l_api_version CONSTANT NUMBER := 1.0;
333
334 l_ccid NUMBER;
335 l_out_bal_amt NUMBER;
336 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
337
338 CURSOR c_balacct
339 IS
340 SELECT
341 DECODE
342 ( g_fund_segment,
343 'SEGMENT1', SEGMENT1, 'SEGMENT2', SEGMENT2,
344 'SEGMENT3', SEGMENT3, 'SEGMENT4', SEGMENT4, 'SEGMENT5',SEGMENT5,
345 'SEGMENT6', SEGMENT6, 'SEGMENT7', SEGMENT7, 'SEGMENT8',SEGMENT8,
346 'SEGMENT9', SEGMENT9, 'SEGMENT10',SEGMENT10, 'SEGMENT11',SEGMENT11,
347 'SEGMENT12',SEGMENT12, 'SEGMENT13',SEGMENT13, 'SEGMENT14',SEGMENT14,
348 'SEGMENT15',SEGMENT15, 'SEGMENT16',SEGMENT16, 'SEGMENT17',SEGMENT17,
349 'SEGMENT18',SEGMENT18, 'SEGMENT19',SEGMENT19, 'SEGMENT20',SEGMENT20,
350 'SEGMENT21',SEGMENT21, 'SEGMENT22',SEGMENT22, 'SEGMENT23',SEGMENT23,
351 'SEGMENT24',SEGMENT24, 'SEGMENT25',SEGMENT25, 'SEGMENT26',SEGMENT26,
352 'SEGMENT27',SEGMENT27, 'SEGMENT28',SEGMENT28, 'SEGMENT29',SEGMENT29,
353 'SEGMENT30',SEGMENT30)
354 segment,
355 a.group_id,
356 a.status,
357 a.set_of_books_id,
358 a.user_je_source_name,
359 a.user_je_category_name,
360 a.currency_code,
361 a.created_by,
362 a.actual_flag,
363 a.budget_version_id,
364 a.period_name,
365 a.period_year,
366 a.period_num,
367 a.quarter_num,
368 a.reference1,
369 a.reference2,
370 sum(a.entered_dr) dr_amt,
371 sum(a.entered_cr) cr_amt,
372 a.accounting_date,
373 a.budget_version_flag,
374 sum(a.amount) amount
375 FROM psb_gl_interfaces a,
376 gl_code_combinations b
377 WHERE worksheet_id = p_worksheet_id
378 AND period_name = p_period_name
379 AND budget_source_type = g_budget_source_type
380 AND a.code_combination_id = b.code_combination_id
381 GROUP BY DECODE
382 ( g_fund_segment,
383 'SEGMENT1', SEGMENT1, 'SEGMENT2', SEGMENT2,
384 'SEGMENT3', SEGMENT3, 'SEGMENT4', SEGMENT4, 'SEGMENT5',SEGMENT5,
385 'SEGMENT6', SEGMENT6, 'SEGMENT7', SEGMENT7, 'SEGMENT8',SEGMENT8,
386 'SEGMENT9', SEGMENT9, 'SEGMENT10',SEGMENT10, 'SEGMENT11',SEGMENT11,
387 'SEGMENT12',SEGMENT12, 'SEGMENT13',SEGMENT13, 'SEGMENT14',SEGMENT14,
388 'SEGMENT15',SEGMENT15, 'SEGMENT16',SEGMENT16, 'SEGMENT17',SEGMENT17,
389 'SEGMENT18',SEGMENT18, 'SEGMENT19',SEGMENT19, 'SEGMENT20',SEGMENT20,
390 'SEGMENT21',SEGMENT21, 'SEGMENT22',SEGMENT22, 'SEGMENT23',SEGMENT23,
391 'SEGMENT24',SEGMENT24, 'SEGMENT25',SEGMENT25, 'SEGMENT26',SEGMENT26,
392 'SEGMENT27',SEGMENT27, 'SEGMENT28',SEGMENT28, 'SEGMENT29',SEGMENT29,
393 'SEGMENT30',SEGMENT30) ,
394 a.group_id,
395 a.status,
396 a.set_of_books_id,
397 a.user_je_source_name,
398 a.user_je_category_name,
399 a.currency_code,
400 a.created_by,
401 a.actual_flag,
402 a.budget_version_id,
403 a.period_name,
404 a.period_year,
405 a.period_num,
406 a.quarter_num,
407 a.reference1,
408 a.reference2,
409 a.accounting_date,
410 a.budget_version_flag;
411
412
413 BEGIN
414
415 SAVEPOINT Balance_Journal;
416
417 FOR c_balacct_rec in c_balacct LOOP
418 l_out_bal_amt := NVL(c_balacct_rec.dr_amt, 0) - NVL(c_balacct_rec.cr_amt, 0);
419 IF l_out_bal_amt <> 0 THEN
420 Get_Offset_Account
421 (x_return_status => l_return_status,
422 p_templ_acct => 'N',
423 p_fund => c_balacct_rec.segment,
424 p_templ_seg => g_templ_seg_val,
425 p_ccid => l_ccid
426 );
427
428 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
429 RAISE FND_API.G_EXC_ERROR;
430 END IF;
431
432 INSERT INTO psb_gl_interfaces
433 (worksheet_id,
434 group_id,
435 status,
436 set_of_books_id,
437 user_je_source_name,
438 user_je_category_name,
439 currency_code,
440 date_created,
441 created_by,
442 actual_flag,
443 budget_version_id,
444 accounting_date,
445 period_name,
446 period_year,
447 period_num,
448 quarter_num,
449 code_combination_id,
450 entered_dr,
451 entered_cr,
452 reference1,
453 reference2,
454 reference4,
455 reference5,
456 budget_source_type,
457 budget_version_flag,
458 balancing_entry_flag,
459 amount,
460 gl_budget_set_id
461 )
462 VALUES
463 (p_worksheet_id,
464 p_worksheet_id,
465 c_balacct_rec.status,
466 c_balacct_rec.set_of_books_id,
467 c_balacct_rec.user_je_source_name,
468 c_balacct_rec.user_je_category_name,
469 c_balacct_rec.currency_code,
470 sysdate,
471 c_balacct_rec.created_by,
472 c_balacct_rec.actual_flag,
473 c_balacct_rec.budget_version_id,
474 c_balacct_rec.accounting_date,
475 c_balacct_rec.period_name,
476 c_balacct_rec.period_year,
477 c_balacct_rec.period_num,
478 c_balacct_rec.quarter_num,
479 l_ccid,
480 DECODE(sign(l_out_bal_amt), -1, -1*l_out_bal_amt, null),
481 DECODE(sign(l_out_bal_amt), 1, l_out_bal_amt, null),
482 c_balacct_rec.reference1,
483 c_balacct_rec.reference2,
484 NULL,
485 NULL,
486 g_budget_source_type,
487 c_balacct_rec.budget_version_flag,
488 'Y',
489 c_balacct_rec.amount,
490 p_GL_budget_set_id
491 );
492 END IF;
493 END LOOP;
494
495 x_return_status := FND_API.G_RET_STS_SUCCESS;
496
497 EXCEPTION
498 WHEN FND_API.G_EXC_ERROR THEN
499 ROLLBACK TO Balance_Journal;
500 x_return_status := FND_API.G_RET_STS_ERROR;
501
502 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
503 ROLLBACK TO Balance_Journal;
504 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
505
506 WHEN OTHERS THEN
507 ROLLBACK TO Balance_Journal;
508 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
509 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
510 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
511 END IF;
512
513 END Balance_Journal;
514
515 /* ----------------------------------------------------------------------- */
516
517 PROCEDURE Initialize
518 (x_return_status OUT NOCOPY VARCHAR2,
519 p_event_type VARCHAR2,
520 p_source_id NUMBER,
521 p_auto_offset VARCHAR2
522 )
523 IS
524
525 l_seg_num NUMBER;
526 l_appcol_name VARCHAR2(30);
527 l_seg_name VARCHAR2(30);
528 l_prompt VARCHAR2(100);
529 l_value_set VARCHAR2(100);
530 l_templ_ccid NUMBER;
531 l_current_sob_index NUMBER;
532 l_return_status VARCHAR2(1);
533 /*FOR Bug No : 2098359 Start*/
534 l_multi_org_flag VARCHAR2(1);
535 /*FOR Bug No : 2098359 END*/
536
537 CURSOR c_seginfo IS
538 SELECT application_column_name
539 FROM FND_ID_FLEX_SEGMENTS
540 WHERE application_id = 101
541 AND id_flex_code = 'GL#'
542 AND id_flex_num = g_chart_of_accounts_id
543 AND enabled_flag = 'Y'
544 ORDER BY segment_num;
545
546 CURSOR c_worksheet
547 IS
548 SELECT a.worksheet_id, a.budget_by_position,
549 a.flex_mapping_set_id,
550 b.set_of_books_id,
551 b.name,
552 b.chart_of_accounts_id,
553 b.currency_code,
554 b.enable_budgetary_control_flag,
555 b.enable_average_balances_flag,
556 b.period_set_name
557 FROM PSB_WORKSHEETS a,
558 GL_SETS_OF_BOOKS b,
559 PSB_BUDGET_GROUPS_V c
560 WHERE b.set_of_books_id = NVL(c.set_of_books_id, c.root_set_of_books_id)
561 AND a.budget_group_id = c.budget_group_id
562 AND a.worksheet_id = p_source_id;
563
564 CURSOR c_revision
565 IS
566 SELECT a.budget_revision_id, a.revise_by_position,
567 a.permanent_revision,
568 a.gl_budget_set_id,
569 a.budget_revision_type,
570 b.set_of_books_id,
571 b.name,
572 b.chart_of_accounts_id,
573 b.currency_code,
574 b.enable_budgetary_control_flag,
575 b.enable_average_balances_flag,
576 b.period_set_name,
577 b.latest_opened_period_name,
578 b.require_budget_journals_flag
579 FROM PSB_BUDGET_REVISIONS a,
580 GL_SETS_OF_BOOKS b,
581 PSB_BUDGET_GROUPS_V c
582 WHERE b.set_of_books_id = NVL(c.set_of_books_id,c.root_set_of_books_id)
583 AND a.budget_group_id = c.budget_group_id
584 AND a.budget_revision_id = p_source_id;
585
586 CURSOR c_je_source
587 IS
588 SELECT user_je_source_name
589 FROM GL_JE_SOURCES
590 WHERE je_source_name = 'Budget Journal';
591
592 CURSOR c_je_category
593 IS
594 SELECT user_je_category_name
595 FROM GL_JE_CATEGORIES
596 WHERE je_category_name = 'Budget';
597
598 -- Bug#4310411 Start.
599 /*CURSOR c_org_code
600 IS
601 SELECT substr(name,1,15) org_code
602 FROM hr_operating_units
603 WHERE organization_id = g_org_id;*/
604 -- Bug#4310411 End.
605
606 /*FOR Bug No : 2098359 Start*/
607 CURSOR c_multi_org
608 IS
609 SELECT multi_org_flag
610 FROM fnd_product_groups;
611 /*FOR Bug No : 2098359 END*/
612
613 BEGIN
614
615 g_user_id := FND_GLOBAL.USER_ID;
616 g_login_id := FND_GLOBAL.LOGIN_ID;
617
618 /*FOR Bug No : 2098359 Start*/
619 FOR c_multi_org_rec IN c_multi_org LOOP
620 l_multi_org_flag := c_multi_org_rec.multi_org_flag;
621 END LOOP;
622
623 --The following code has been commented since ORG_ID
624 --IS NOT applicable IN single org environments
625 --g_org_id := FND_PROFILE.VALUE('ORG_ID');
626 /*FOR Bug No : 2098359 END*/
627
628 g_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
629 g_request_id := FND_GLOBAL.CONC_REQUEST_ID;
630 g_ae_category(1) := 'Budget';
631
632 -- Bug#4310411 Start.
633 /*IF g_org_id IS NOT NULL THEN
634 BEGIN
635 FOR c_org_code_rec IN c_org_code LOOP
636 g_org_code := c_org_code_rec.org_code;
637 END LOOP;
638 END;
639 END IF;*/
640 -- Bug#4310411 End.
641
642 FOR c_je_source_rec IN c_je_source LOOP
643 g_source_name := c_je_source_rec.user_je_source_name;
644 END LOOP;
645
646 FOR c_je_category_rec IN c_je_category LOOP
647 g_category_name := c_je_category_rec.user_je_category_name;
648 END LOOP;
649
650 g_budget_source_type := p_event_type; -- -- Bug#4310411.
651
652 -- Bug 3029168 added the second clause p_event_type ='SW'
653 if p_event_type = 'BP' OR p_event_type = 'SW' then
654 BEGIN
655 FND_MESSAGE.SET_NAME('PSB', 'PSB_GL_BJE_BATCH_NAME');
656 FND_MESSAGE.SET_TOKEN('WORKSHEET_ID', p_source_id);
657 g_batch_name := FND_MESSAGE.Get;
658
659 FND_MESSAGE.SET_NAME('PSB', 'PSB_GL_BJE_BATCH_DESC');
660 FND_MESSAGE.SET_TOKEN('WORKSHEET_ID', p_source_id);
661 g_batch_description := FND_MESSAGE.Get;
662
663 FND_MESSAGE.SET_NAME('PSB', 'PSB_GL_BJE_JE_NAME');
664 FND_MESSAGE.SET_TOKEN('WORKSHEET_ID', p_source_id);
665 g_je_name := FND_MESSAGE.Get;
666
667 FND_MESSAGE.SET_NAME('PSB', 'PSB_GL_BJE_JE_DESC');
668 FND_MESSAGE.SET_TOKEN('WORKSHEET_ID', p_source_id);
669 g_je_description := FND_MESSAGE.Get;
670
671 FOR c_worksheet_rec IN c_worksheet LOOP
672 g_budget_by_position := c_worksheet_rec.budget_by_position;
673 g_flex_mapping_set_id := c_worksheet_rec.flex_mapping_set_id;
674 g_set_of_books_id := c_worksheet_rec.set_of_books_id;
675 g_set_of_books_name := c_worksheet_rec.name;
676 g_chart_of_accounts_id := c_worksheet_rec.chart_of_accounts_id;
677 g_currency_code := c_worksheet_rec.currency_code;
678 g_budgetary_control
679 := c_worksheet_rec.enable_budgetary_control_flag;
680 g_average_balances
681 := c_worksheet_rec.enable_average_balances_flag;
682 g_gl_calendar := c_worksheet_rec.period_set_name;
683 END LOOP;
684 END;
685 -- Bud 3029168 added the second clause p_event_type='SR'
686 elsif p_event_type = 'BR' OR p_event_type = 'SR' then
687 BEGIN
688 FND_MESSAGE.SET_NAME('PSB', 'PSB_GL_BJR_BATCH_NAME');
689 FND_MESSAGE.SET_TOKEN('BUDGET_REVISION_ID', p_source_id);
690 g_batch_name := FND_MESSAGE.Get;
691
692 FND_MESSAGE.SET_NAME('PSB', 'PSB_GL_BJR_BATCH_DESC');
693 FND_MESSAGE.SET_TOKEN('BUDGET_REVISION_ID', p_source_id);
694 g_batch_description := FND_MESSAGE.Get;
695
696 FND_MESSAGE.SET_NAME('PSB', 'PSB_GL_BJR_JE_NAME');
697 FND_MESSAGE.SET_TOKEN('BUDGET_REVISION_ID', p_source_id);
698 g_je_name := FND_MESSAGE.Get;
699
700 FND_MESSAGE.SET_NAME('PSB', 'PSB_GL_BJR_JE_DESC');
701 FND_MESSAGE.SET_TOKEN('BUDGET_REVISION_ID', p_source_id);
702 g_je_description := FND_MESSAGE.Get;
703
704 -- Get Auto Offset value FROM a profile option
705 FND_PROFILE.GET
706 (name => 'PSB_REVISION_AUTO_OFFSET',
707 val => g_offset_revision
708 );
709
710 FOR c_revision_rec IN c_revision LOOP
711 g_budget_by_position := c_revision_rec.revise_by_position;
712 g_set_of_books_id := c_revision_rec.set_of_books_id;
713 g_permanent_revision := c_revision_rec.permanent_revision;
714 g_budget_set_id := c_revision_rec.gl_budget_set_id;
715 g_revision_type := c_revision_rec.budget_revision_type;
716 g_set_of_books_name := c_revision_rec.name;
717 g_chart_of_accounts_id := c_revision_rec.chart_of_accounts_id;
718 g_currency_code := c_revision_rec.currency_code;
719 g_budgetary_control := c_revision_rec.enable_budgetary_control_flag;
720 g_average_balances := c_revision_rec.enable_average_balances_flag;
721 g_gl_calendar := c_revision_rec.period_set_name;
722 END LOOP;
723 END;
724 END IF;
725
726 IF NOT FND_FLEX_APIS.Get_Qualifier_Segnum
727 (appl_id => 101,
728 key_flex_code => 'GL#',
729 structure_number => g_chart_of_accounts_id,
730 flex_qual_name => 'GL_BALANCING',
731 segment_number => l_seg_num
732 )
733 THEN
734 Add_Message('PSB', 'PSB_GL_CANNOT_FIND_BAL_SEG');
735 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
736 END IF;
737
738 IF NOT FND_FLEX_APIS.Get_Segment_Info
739 (x_application_id => 101,
740 x_id_flex_code => 'GL#',
741 x_id_flex_num => g_chart_of_accounts_id,
742 x_seg_num => l_seg_num,
743 x_appcol_name => g_fund_segment,
744 x_seg_name => l_seg_name,
745 x_prompt => l_prompt,
746 x_value_set_name => l_value_set
747 )
748 THEN
749 FND_MSG_PUB.Add;
750 Add_Message('PSB', 'PSB_GL_CANNOT_GET_SEG_INFO');
751 END IF;
752
753 g_num_segs := 0;
754
755 FOR c_Seginfo_Rec IN c_seginfo LOOP
756 g_num_segs := g_num_segs + 1;
757 g_seg_name(g_num_segs) := c_Seginfo_Rec.application_column_name;
758 END LOOP;
759
760 IF p_auto_offset = 'Y' THEN
761 BEGIN
762 Get_Offset_Account
763 (x_return_status => l_return_status,
764 p_templ_acct => 'Y',
765 p_fund => NULL,
766 p_templ_seg => g_templ_seg_val,
767 p_ccid => l_templ_ccid
768 );
769
770 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
771 RAISE FND_API.G_EXC_ERROR;
772 END IF;
773 END;
774 END IF;
775
776 x_return_status := FND_API.G_RET_STS_SUCCESS;
777
778 EXCEPTION
779
780 WHEN FND_API.G_EXC_ERROR THEN
781 x_return_status := FND_API.G_RET_STS_ERROR;
782
783 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
784 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
785
786 WHEN OTHERS THEN
787 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
788
789 END Initialize;
790
791 /* ----------------------------------------------------------------------- */
792 /*FOR Bug No : 2543724 Start*/
793 --Removed the following two parameters FROM this procedure as they are unused
794 --p_next_period OUT NOCOPY VARCHAR2,
795 --p_reversal_date OUT NOCOPY DATE,
796 /*FOR Bug No : 2543724 END*/
797
798 PROCEDURE Get_GL_Period
799 (x_return_status OUT NOCOPY VARCHAR2,
800 p_start_date DATE,
801 x_effective_period_num OUT NOCOPY NUMBER,
802 x_period_name OUT NOCOPY VARCHAR2,
803 x_period_start_date OUT NOCOPY DATE,
804 x_period_end_date OUT NOCOPY DATE,
805 x_period_status OUT NOCOPY VARCHAR2,
806 x_period_year OUT NOCOPY NUMBER,
807 x_period_number OUT NOCOPY NUMBER,
808 x_quarter_number OUT NOCOPY NUMBER
809 )
810 IS
811
812 l_period_found BOOLEAN := FALSE;
813
814 CURSOR c_period
815 IS
816 SELECT period_name,
817 effective_period_num,
818 start_date,
819 end_date,
820 closing_status,
821 period_year,
822 period_num,
823 quarter_num
824 FROM GL_PERIOD_STATUSES
825 WHERE application_id = 101
826 AND set_of_books_id = g_set_of_books_id
827 AND NVL(adjustment_period_flag, 'N') = 'N'
828 AND p_start_date BETWEEN start_date AND end_date
829 ORDER BY period_num; -- Bug 3029168
830
831 /*FOR Bug No : 2543724 Start*/
832 --commented the following CURSOR as it's NOT being used
833 /*
834 CURSOR c_next_period IS
835 SELECT period_name, start_date
836 FROM gl_period_statuses
837 WHERE application_id = 101
838 AND set_of_books_id = g_set_of_books_id
839 AND NVL(adjustment_period_flag,'N') = 'N'
840 AND p_period_end_date+1 BETWEEN start_date AND end_date;
841 */
842 /*FOR Bug No : 2543724 END*/
843
844 BEGIN
845 FOR c_period_rec IN c_period LOOP
846 l_period_found := TRUE;
847
848 x_period_name := c_period_rec.period_name;
849 x_effective_period_num := c_period_rec.effective_period_num;
850 x_period_start_date := c_period_rec.start_date;
851 x_period_end_date := c_period_rec.end_date;
852 x_period_status := c_period_rec.closing_status;
853 x_period_year := c_period_rec.period_year; -- Bug#4310411
854 x_period_number := c_period_rec.period_num; -- Bug#4310411
855 x_quarter_number := c_period_rec.quarter_num; -- Bug#4310411
856 END LOOP;
857
858 IF NOT l_period_found THEN
859 Add_Message('PSB', 'PSB_GL_PRD_NOT_FOUND');
860 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
861 END IF;
862
863 /*FOR Bug No : 2543724 Start*/
864 --We need NOT to validate against GL periods as
865 --Budget Journal doesn't require Gl periods to be opened
866 --hence commenting the following code
867
868 /*
869 IF p_period_status = 'N' THEN
870 Message_Token('PERIOD_NAME', p_period_name);
871 Add_Message('PSB', 'PSB_GL_PRD_NEVER_OPENED');
872 RAISE FND_API.G_EXC_ERROR;
873 ELSIF p_period_status IN ('P', 'C') THEN
874 Message_Token('PERIOD_NAME', p_period_name);
875 Add_Message('PSB', 'PSB_GL_PRD_CLOSED');
876 RAISE FND_API.G_EXC_ERROR;
877 END IF;
878
879 --commented the following code as this IS NOT being used
880 FOR c_next_period_rec IN c_next_period LOOP
881 p_next_period := c_next_period_rec.period_name;
882 p_reversal_date := c_next_period_rec.start_date;
883 END LOOP;
884 */
885
886 /*FOR Bug No : 2543724 END*/
887
888 x_return_status := FND_API.G_RET_STS_SUCCESS;
889
890 EXCEPTION
891
892 WHEN FND_API.G_EXC_ERROR THEN
893 x_return_status := FND_API.G_RET_STS_ERROR;
894
895 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
896 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
897
898 WHEN OTHERS THEN
899 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
900
901 END Get_GL_Period;
902
903 /* ----------------------------------------------------------------------- */
904 /*FOR Bug No : 2712019 Start*/
905 --Following procedure will determine IF there are any GL Budgets, FOR which
906 --the latest OPEN year IS NOT matching with the year we are trying to post FROM PSB
907
908 PROCEDURE Validate_GL_Budget_Year
909 (x_return_status OUT NOCOPY VARCHAR2,
910 p_gl_budget_set_id IN NUMBER,
911 p_year_start_date IN DATE,
912 p_year_end_date IN DATE
913 )
914 IS
915 --
916 l_period_year NUMBER(15);
917 l_open_flag VARCHAR2(1);
918 l_error_flag BOOLEAN := FALSE;
919 l_budget_name VARCHAR2(15);
920 --
921 CURSOR c_period
922 IS
923 SELECT period_year
924 FROM GL_PERIOD_STATUSES
925 WHERE application_id = 101
926 AND set_of_books_id = g_set_of_books_id
927 AND p_year_end_date BETWEEN start_date AND end_date;
928
929 CURSOR c_budver
930 IS
931 SELECT gl_budget_version_id
932 FROM PSB_GL_BUDGETS
933 WHERE gl_budget_set_id = p_gl_budget_set_id
934 AND p_year_start_date BETWEEN start_date AND end_date;
935
936 CURSOR c_bud_name(gl_budver_id NUMBER)
937 IS
938 SELECT budget_name
939 FROM gl_budget_versions
940 WHERE budget_version_id = gl_budver_id;
941 --
942 BEGIN
943 --
944 --FETCH the GL accounting year FOR the correponding
945 --year END GL posting DATE IN the given set of books
946 FOR c_period_rec IN c_period LOOP
947 l_period_year := c_period_rec.period_year;
948 END LOOP;
949 --
950 --Validate the GL Budget year FOR all the GL Budget versions
951 --available IN PSB GL Budget Set
952 FOR c_budver_rec IN c_budver LOOP
953 --
954 --Following GL API returns the OPEN flag FOR the given accounting year
955 --AND the details are stored IN gl_budget_period_ranges table
956 GL_Budget_Period_Ranges_Pkg.Get_Open_Flag
957 (x_budget_version_id => c_budver_rec.gl_budget_version_id,
958 x_period_year => l_period_year,
959 x_open_flag => l_open_flag
960 );
961 --
962 --Throw an error IF the corresponding year IS NOT OPEN FOR the GL Budget
963 IF NVL(l_open_flag,'X') <> 'O' THEN
964 --
965 --FETCH the GL Budget name FROM the CURSOR before throwing an error
966 FOR c_bud_name_rec IN c_bud_name(c_budver_rec.gl_budget_version_id) LOOP
967 l_budget_name := c_bud_name_rec.budget_name;
968 END LOOP;
969 --
970 Message_Token('YEAR', l_period_year);
971 Message_Token('GLBUDGET', l_budget_name);
972 Add_Message('PSB', 'PSB_GL_BUDGET_YEAR_NOT_OPENED');
973 l_error_flag := TRUE;
974 l_budget_name := NULL;
975 --
976 END IF;
977 --
978 l_open_flag := NULL;
979 END LOOP;
980 --
981 IF l_error_flag THEN
982 x_return_status := FND_API.G_RET_STS_ERROR;
983 ELSE
984 x_return_status := FND_API.G_RET_STS_SUCCESS;
985 END IF;
986 --
987 EXCEPTION
988 WHEN FND_API.G_EXC_ERROR THEN
989 x_return_status := FND_API.G_RET_STS_ERROR;
990
991 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
992 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
993
994 WHEN OTHERS THEN
995 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
996
997 END Validate_GL_Budget_Year;
998 /*FOR Bug No : 2712019 END*/
999 /* ----------------------------------------------------------------------- */
1000
1001 PROCEDURE Submit_Concurrent_Request
1002 (x_return_status OUT NOCOPY VARCHAR2,
1003 p_source_id NUMBER,
1004 p_event_type VARCHAR2,
1005 p_order_by1 VARCHAR2,
1006 p_order_by2 VARCHAR2,
1007 p_order_by3 VARCHAR2,
1008 p_gl_budget_set_id NUMBER,
1009 p_budget_year_id NUMBER,
1010 p_currency_code VARCHAR2 DEFAULT 'C' -- Bug 3029168
1011 )
1012 IS
1013
1014 l_req_id NUMBER;
1015 l_return_status VARCHAR2(10);
1016
1017 BEGIN
1018 --
1019 -- Starting the concurrent program
1020 --
1021 l_req_id := FND_REQUEST.SUBMIT_REQUEST
1022 (application => 'PSB',
1023 program => 'PSBOTGLR',
1024 description => 'Budget Journal Edit Report',
1025 start_time => NULL,
1026 sub_request => FALSE,
1027 argument1 => p_source_id,
1028 argument2 => p_currency_code, -- Bug 3029168
1029 argument5 => p_order_by1,
1030 argument6 => p_order_by2,
1031 argument7 => p_order_by3,
1032 argument4 => p_gl_budget_set_id,
1033 argument3 => p_budget_year_id
1034 );
1035
1036 IF l_req_id = 0 THEN
1037 Add_Message('PSB', 'PSB_FAIL_TO_SUBMIT_REQUEST');
1038 RAISE FND_API.G_EXC_ERROR;
1039 END IF;
1040
1041 x_return_status := FND_API.G_RET_STS_SUCCESS;
1042
1043 EXCEPTION
1044
1045 WHEN FND_API.G_EXC_ERROR THEN
1046 x_return_status := FND_API.G_RET_STS_ERROR;
1047
1048 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1049 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1050
1051 WHEN OTHERS THEN
1052 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1053
1054 END Submit_Concurrent_Request;
1055
1056 /* ----------------------------------------------------------------------- */
1057 PROCEDURE Insert_Lines_Into_BCP
1058 (x_return_status OUT NOCOPY VARCHAR2,
1059 p_worksheet_id IN NUMBER,
1060 p_called_from IN VARCHAR2,
1061 p_period_name IN VARCHAR2 DEFAULT NULL,
1062 p_packetid IN NUMBER DEFAULT NULL
1063 )
1064 IS
1065
1066 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Lines_Into_BCP';
1067 l_api_version CONSTANT NUMBER := 1.0;
1068
1069 l_period_name VARCHAR2(15);
1070 l_packetid GL_bc_packets.packet_id%TYPE;
1071 l_return_status VARCHAR2(10);
1072 l_session_id number(38);
1073 l_serial_id number(38);
1074
1075 CURSOR l_packet_csr
1076 IS
1077 SELECT gl_bc_packets_s.nextval
1078 FROM dual;
1079 PRAGMA autonomous_transaction;
1080
1081 /* 5148282 made this api as autonomous transaction
1082 as funds check runs as a separate transaction */
1083 BEGIN
1084
1085 SELECT s.sid, s.serial#
1086 INTO l_session_id,
1087 l_serial_id
1088 FROM v$session s,v$process p
1089 WHERE s.paddr = p.addr
1090 AND audsid = USERENV('SESSIONID');
1091
1092 INSERT INTO gl_bc_packets
1093 (packet_id,
1094 ledger_id, -- Bug#4310411
1095 je_source_name,
1096 je_category_name,
1097 code_combination_id,
1098 actual_flag,
1099 period_name,
1100 period_year,
1101 period_num,
1102 quarter_num,
1103 currency_code,
1104 status_code,
1105 last_update_date,
1106 last_updated_by,
1107 budget_version_id,
1108 entered_dr,
1109 entered_cr,
1110 accounted_dr,
1111 accounted_cr,
1112 reference1,
1113 reference2,
1114 reference3,
1115 reference4,
1116 reference5,
1117 application_id, -- Bug 4589283 added the below columns
1118 session_id,
1119 serial_id
1120 )
1121 SELECT P_packetid,
1122 set_of_books_id,
1123 user_je_source_name,
1124 user_je_category_name,
1125 code_combination_id,
1126 actual_flag,
1127 period_name,
1128 period_year,
1129 period_num,
1130 quarter_num,
1131 currency_code,
1132 'P',
1133 date_created,
1134 created_by,
1135 budget_version_id,
1136 entered_dr,
1137 entered_cr,
1138 entered_dr,
1139 entered_cr,
1140 reference1,
1141 reference2,
1142 reference3,
1143 reference4,
1144 reference5,
1145 8401,
1146 l_session_id,
1147 l_serial_id
1148 FROM psb_gl_interfaces
1149 WHERE worksheet_id = p_worksheet_id
1150 AND period_name = p_period_name
1151 AND budget_version_flag = 'P'
1152 AND NVL(budget_source_type, 'BP') = g_budget_source_type;
1153
1154 COMMIT;
1155
1156 x_return_status := FND_API.G_RET_STS_SUCCESS;
1157
1158 EXCEPTION
1159
1160 WHEN FND_API.G_EXC_ERROR THEN
1161
1162 x_return_status := FND_API.G_RET_STS_ERROR;
1163
1164 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1165
1166 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1167
1168
1169 WHEN OTHERS THEN
1170
1171 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1172
1173 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1174 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
1175 END IF;
1176
1177 END Insert_Lines_Into_BCP;
1178
1179 /* ----------------------------------------------------------------------- */
1180
1181 PROCEDURE Insert_Lines_Into_GL_I
1182 (x_return_status OUT NOCOPY VARCHAR2,
1183 p_worksheet_id IN NUMBER
1184 )
1185 IS
1186
1187 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Lines_Into_GL_I';
1188 l_api_version CONSTANT NUMBER := 1.0;
1189
1190 BEGIN
1191
1192 SAVEPOINT Insert_Lines_Into_GL_I;
1193
1194 INSERT INTO gl_interface
1195 (group_id,
1196 status,
1197 ledger_id, -- Bug#4310411
1198 user_je_source_name,
1199 user_je_category_name,
1200 currency_code,
1201 date_created,
1202 created_by,
1203 actual_flag,
1204 budget_version_id,
1205 accounting_date,
1206 period_name,
1207 code_combination_id,
1208 entered_dr,
1209 entered_cr,
1210 reference1,
1211 reference2,
1212 reference4,
1213 reference5
1214 )
1215 SELECT group_id,
1216 status,
1217 set_of_books_id,
1218 user_je_source_name,
1219 user_je_category_name,
1220 currency_code,
1221 date_created,
1222 created_by,
1223 actual_flag,
1224 budget_version_id,
1225 accounting_date,
1226 period_name,
1227 code_combination_id,
1228 entered_dr,
1229 entered_cr,
1230 reference1,
1231 reference2,
1232 reference4,
1233 reference5
1234 FROM psb_gl_interfaces
1235 WHERE worksheet_id = p_worksheet_id
1236 AND NVL(budget_source_type,'BP') = g_budget_source_type;
1237
1238 x_return_status := FND_API.G_RET_STS_SUCCESS;
1239
1240 EXCEPTION
1241 WHEN FND_API.G_EXC_ERROR THEN
1242 ROLLBACK TO Insert_Lines_Into_GL_I;
1243 x_return_status := FND_API.G_RET_STS_ERROR;
1244
1245 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1246 ROLLBACK TO Insert_Lines_Into_GL_I;
1247 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1248
1249 WHEN OTHERS THEN
1250 ROLLBACK TO Insert_Lines_Into_GL_I;
1251 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1252 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1253 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
1254 END IF;
1255
1256 END Insert_Lines_Into_GL_I;
1257
1258 /* ----------------------------------------------------------------------- */
1259 PROCEDURE Insert_Lines_To_GL
1260 (x_return_status OUT NOCOPY VARCHAR2,
1261 p_source_id IN NUMBER,
1262 p_called_from IN VARCHAR2,
1263 p_event_type IN VARCHAR2 DEFAULT NULL
1264 )
1265 IS
1266
1267 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Lines_To_GL';
1268 l_api_version CONSTANT NUMBER := 1.0;
1269 l_return_status VARCHAR2(10);
1270
1271 l_req_id NUMBER;
1272 l_group_id NUMBER;
1273 l_interface_run_id NUMBER;
1274 /* Bug 3029168 Start */
1275 l_max_period NUMBER;
1276 l_min_period NUMBER;
1277 l_max_period_name VARCHAR2(15);
1278 l_min_period_name VARCHAR2(15);
1279 /* Bug 3029168 End */
1280 l_iso_language VARCHAR2(2);
1281 l_iso_territory VARCHAR2(2);
1282 l_template_code VARCHAR2(100);
1283 l_layout BOOLEAN;
1284
1285 l_period_name VARCHAR2(15);
1286
1287 CURSOR l_ws_period_csr
1288 IS
1289 SELECT DISTINCT period_name
1290 FROM psb_gl_interfaces
1291 WHERE worksheet_id = p_source_id
1292 AND NVL(budget_source_type,'BP') = g_budget_source_type;
1293
1294
1295 CURSOR l_packet_csr
1296 IS
1297 SELECT gl_bc_packets_s.nextval
1298 FROM dual;
1299
1300 l_packetid GL_bc_packets.packet_id%TYPE;
1301
1302 BEGIN
1303
1304 IF g_budgetary_control = 'Y' THEN
1305 -- FOR GL_BC_PACKATES route.
1306
1307 -- the following code insert the data in GL_BC_PACKETS
1308 -- and calls the funds checker in reserve mode
1309 commit; -- this has to be there before calling the autonomous commit
1310 IF p_called_from = 'C' THEN
1311
1312 -- Bug 3029168 Start. code to find period names
1313 SELECT MAX(period_num),MIN(PERIOD_NUM)
1314 INTO l_max_period,
1315 l_min_period
1316 FROM psb_gl_interfaces
1317 WHERE budget_source_type = p_event_type
1318 AND budget_year_id = g_budget_year_id
1319 AND worksheet_id = p_source_id;
1320
1321
1322 SELECT period_name
1323 INTO l_max_period_name
1324 FROM psb_gl_interfaces
1325 WHERE period_num = l_max_period
1326 AND budget_source_type = p_event_type
1327 AND budget_year_id = g_budget_year_id
1328 AND worksheet_id = p_source_id
1329 AND rownum = 1;
1330
1331 SELECT period_name
1332 INTO l_min_period_name
1333 FROM psb_gl_interfaces
1334 WHERE period_num = l_min_period
1335 AND budget_source_type = p_event_type
1336 AND budget_year_id = g_budget_year_id
1337 AND worksheet_id = p_source_id
1338 AND rownum = 1;
1339 -- Bug 3029168 End
1340
1341 /* Bug 5148282 moved the following logic from Insert_Lines_Into_BCP
1342 as inserts into gl_bc_packets is done as autonomous transaction */
1343 OPEN l_ws_period_csr;
1344 LOOP
1345
1346 FETCH l_ws_period_csr INTO l_period_name;
1347 IF l_ws_period_csr%notfound THEN
1348 EXIT;
1349 END IF;
1350
1351 OPEN l_packet_csr;
1352 FETCH l_packet_csr INTO l_packetid;
1353 CLOSE l_packet_csr;
1354
1355 Insert_Lines_Into_BCP
1356 (x_return_status => l_return_status,
1357 p_worksheet_id => p_source_id,
1358 p_called_from => p_called_from,
1359 p_period_name => l_period_name,
1360 p_packetid => l_packetid
1361 );
1362
1363 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1364 RAISE FND_API.G_EXC_ERROR;
1365 END IF;
1366
1367 IF NOT PSA_FUNDS_CHECKER_PKG.GLXFCK
1368 (p_ledgerid => g_set_of_books_id,
1369 p_packetid => l_packetid,
1370 p_mode => 'P', -- partial reserve
1371 p_conc_flag => 'N',
1372 p_return_code => l_return_status,
1373 p_calling_prog_flag => 'P' -- Bug 4589283
1374
1375 )
1376 -- Bug#4310411 End
1377 THEN
1378 -- Fundscheck Failed --
1379 Message_Token('GL_PERIOD', l_period_name);
1380 Add_Message('PSB', 'PSB_FAIL_FUNDS_CHECK');
1381 RAISE FND_API.G_EXC_ERROR;
1382 END IF;
1383 END LOOP;
1384 --
1385 -- Starting the concurrent program
1386 --
1387 /* Bug 4589283 Start */
1388 l_template_code := fnd_profile.value('PSA_BC_REPORT_TEMPLATE');
1389
1390 SELECT iso_language,iso_territory
1391 INTO l_iso_language,l_iso_territory
1392 FROM fnd_languages
1393 WHERE language_code = userenv('LANG');
1394
1395 l_layout := FND_REQUEST.ADD_LAYOUT
1396 (TEMPLATE_APPL_NAME => 'PSA',
1397 TEMPLATE_CODE => l_template_code,
1398 TEMPLATE_LANGUAGE => l_iso_language,
1399 TEMPLATE_TERRITORY => l_iso_territory,
1400 OUTPUT_FORMAT => 'PDF');
1401
1402 /* Bug 4589283 End */
1403
1404 /*Bug:6502210:Modified/Added the parameters - 'argument4,argument5 .. argument9'
1405 for the program call - PSABCRRP */
1406
1407 l_req_id := FND_REQUEST.SUBMIT_REQUEST
1408 (application => 'PSA',
1409 program => 'PSABCRRP',
1410 description => 'Budgetary Control Results Report',
1411 start_time => NULL,
1412 sub_request => FALSE,
1413 argument1 => to_char(g_set_of_books_id),
1414 argument2 => l_min_period_name, --PSA_BC_GL_PERIOD_FROM,
1415 argument3 => l_max_period_name, --PSA_BC_GL_PERIOD_TO
1416 argument4 => NULL,
1417 argument5 => NULL,
1418 argument6 => NULL,
1419 argument7 => 'PSB',
1420 argument8 => NULL,
1421 argument9 => NULL
1422 );
1423
1424 IF l_req_id = 0 THEN
1425 Add_Message('PSB', 'PSB_FAIL_TO_SUBMIT_REQUEST');
1426 RAISE FND_API.G_EXC_ERROR;
1427 END IF;
1428
1429 -- Bug 5148554 the following code handles funds checking
1430 -- in funds reservation mode for a revision
1431 ELSIF p_called_from = 'R' THEN
1432
1433 OPEN l_ws_period_csr;
1434 LOOP
1435
1436 FETCH l_ws_period_csr INTO l_period_name;
1437 IF l_ws_period_csr%notfound THEN
1438 EXIT;
1439 END IF;
1440
1441 OPEN l_packet_csr;
1442 FETCH l_packet_csr INTO l_packetid;
1443 CLOSE l_packet_csr;
1444
1445 Insert_Lines_Into_BCP
1446 (x_return_status => l_return_status,
1447 p_worksheet_id => p_source_id,
1448 p_called_from => p_called_from,
1449 p_period_name => l_period_name,
1450 p_packetid => l_packetid
1451 );
1452
1453 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1454 RAISE FND_API.G_EXC_ERROR;
1455 END IF;
1456
1457 IF NOT PSA_FUNDS_CHECKER_PKG.GLXFCK
1458 (p_ledgerid => g_set_of_books_id,
1459 p_packetid => l_packetid,
1460 p_mode => 'R',
1461 p_conc_flag => 'N',
1462 p_return_code => l_return_status,
1463 p_calling_prog_flag => 'P' -- Bug 4589283
1464
1465 )
1466 THEN
1467 -- Fundscheck Failed --
1468 Message_Token('GL_PERIOD', l_period_name);
1469 Add_Message('PSB', 'PSB_FAIL_FUNDS_CHECK');
1470 RAISE FND_API.G_EXC_ERROR;
1471 END IF;
1472 END LOOP;
1473
1474 END IF;
1475
1476 ELSE
1477 -- FOR GL_INTERFACE route.
1478 Insert_Lines_Into_GL_I
1479 (x_return_status => l_return_status,
1480 p_worksheet_id => p_source_id
1481 );
1482
1483 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1484 RAISE FND_API.G_EXC_ERROR;
1485 END IF;
1486
1487 -- Now spawn the "GLLEZL" CP to create the journel to GL.
1488 SELECT gl_interface_control_s.NEXTVAL,
1489 gl_journal_import_s.NEXTVAL
1490 INTO l_group_id,
1491 l_interface_run_id
1492 FROM dual;
1493
1494 INSERT INTO gl_interface_control
1495 (JE_SOURCE_NAME,
1496 STATUS,
1497 INTERFACE_RUN_ID,
1498 GROUP_ID,
1499 SET_OF_BOOKS_ID,
1500 PACKET_ID
1501 )
1502 VALUES
1503 (g_source_name,
1504 'S',
1505 l_interface_run_id,
1506 p_source_id,
1507 g_set_of_books_id,
1508 ''
1509 );
1510
1511 l_req_id := FND_Request.Submit_Request
1512 ('SQLGL', -- application short name
1513 'GLLEZL', -- program short name
1514 NULL, -- program name
1515 NULL, -- start DATE
1516 FALSE, -- sub-request
1517 l_interface_run_id, -- interface run id
1518 g_set_of_books_id, -- set of books id
1519 'N', -- error to suspense flag
1520 NULL, -- FROM accounting DATE
1521 NULL, -- to accounting DATE
1522 'N', -- create summary(Default value N)
1523 'N' -- import desc flex flag
1524 );
1525
1526 IF l_req_id = 0 THEN
1527 Add_Message('PSB', 'PSB_FAIL_TO_SUBMIT_REQUEST');
1528 RAISE FND_API.G_EXC_ERROR;
1529 END IF;
1530 END IF;
1531
1532 x_return_status := FND_API.G_RET_STS_SUCCESS;
1533
1534 EXCEPTION
1535 WHEN FND_API.G_EXC_ERROR THEN
1536 x_return_status := FND_API.G_RET_STS_ERROR;
1537
1538 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1539 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1540
1541 WHEN OTHERS THEN
1542 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1543 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1544 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
1545 END IF;
1546
1547 END Insert_Lines_To_GL;
1548
1549 /* ----------------------------------------------------------------------- */
1550
1551 -- commenting out the savepoints FOR XLA transfer since the XLA transfer
1552 -- program commits within the process AND this erases the savepoints established
1553
1554 PROCEDURE Transfer_GLI_To_GL
1555 (p_return_status OUT NOCOPY VARCHAR2,
1556 p_msg_count OUT NOCOPY NUMBER,
1557 p_msg_data OUT NOCOPY VARCHAR2,
1558 p_init_msg_list VARCHAR2 := FND_API.G_FALSE,
1559 p_event_type VARCHAR2,
1560 p_source_id NUMBER,
1561 p_gl_transfer_mode VARCHAR2 := NULL,
1562 p_order_by1 VARCHAR2,
1563 p_order_by2 VARCHAR2,
1564 p_order_by3 VARCHAR2
1565 )
1566 IS
1567
1568 l_return_status VARCHAR2(1);
1569 l_msg_count NUMBER;
1570 l_msg_data VARCHAR2(2000);
1571 l_budget_revision_type VARCHAR2(1);
1572
1573 l_cbc_document BOOLEAN := FALSE;
1574 l_include_cbc_commit_balance VARCHAR2(1);
1575 l_include_cbc_oblig_balance VARCHAR2(1);
1576 l_include_cbc_budget_balance VARCHAR2(1);
1577
1578 CURSOR c_rev_type
1579 IS
1580 SELECT budget_revision_type
1581 FROM psb_budget_revisions
1582 WHERE budget_revision_id = p_source_id;
1583
1584 CURSOR c_ws
1585 IS
1586 SELECT include_cbc_commit_balance,
1587 include_cbc_oblig_balance,
1588 include_cbc_budget_balance
1589 FROM psb_worksheets
1590 WHERE worksheet_id = p_source_id;
1591
1592 BEGIN
1593
1594 -- Standard Start of API savepoint
1595 SAVEPOINT Transfer_GLI_To_GL;
1596
1597 -- Initialize message list IF p_init_msg_list IS set to TRUE.
1598 IF FND_API.to_Boolean (p_init_msg_list) THEN
1599 FND_MSG_PUB.initialize;
1600 END IF;
1601
1602 Initialize
1603 (x_return_status => l_return_status,
1604 p_event_type => p_event_type, -- Bug 3029168
1605 p_source_id => p_source_id,
1606 p_auto_offset => 'N'
1607 );
1608
1609 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1610 RAISE FND_API.G_EXC_ERROR;
1611 END IF;
1612
1613 -- Bug 3029168 added the clause p_event_type = 'SW'
1614 if p_event_type = 'BP' OR p_event_type = 'SW' then -- Bug 3029168
1615 BEGIN
1616 FOR c_ws_rec IN c_ws LOOP
1617 l_include_cbc_commit_balance := c_ws_rec.include_cbc_commit_balance;
1618 l_include_cbc_oblig_balance := c_ws_rec.include_cbc_oblig_balance;
1619 l_include_cbc_budget_balance := c_ws_rec.include_cbc_budget_balance;
1620 END LOOP;
1621
1622 IF (NVL(l_include_cbc_commit_balance,'N') = 'Y'
1623 OR
1624 NVL(l_include_cbc_oblig_balance,'N') = 'Y'
1625 OR
1626 NVL(l_include_cbc_budget_balance, 'N') = 'Y')
1627 AND p_event_type <> 'SW' -- Bug 3029168 added STAT join
1628 THEN
1629 BEGIN
1630 l_cbc_document := TRUE;
1631
1632 PSB_COMMITMENTS_PVT.Post_Commitment_Worksheet
1633 (p_api_version => 1.0,
1634 p_return_status => l_return_status,
1635 p_msg_data => l_msg_data,
1636 p_msg_count => l_msg_count,
1637 p_worksheet_id => p_source_id
1638 );
1639
1640 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1641 Message_Token('WORKSHEET', p_source_id);
1642 Add_Message('PSB', 'PSB_CANNOT_POST_COMMITMENT_WS');
1643 RAISE FND_API.G_EXC_ERROR;
1644 END IF;
1645 END;
1646 ELSE
1647 BEGIN
1648 -- Bug#4310411 Start
1649 -- Replace XLA with Old call.
1650 Insert_Lines_To_GL
1651 (x_return_status => l_return_status,
1652 p_source_id => p_source_id,
1653 p_called_from => 'T',
1654 p_event_type => p_event_type -- Bug 3029168
1655 );
1656
1657 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1658 RAISE FND_API.G_EXC_ERROR;
1659 END IF;
1660 -- Bug#4310411 End
1661 END;
1662 END IF;
1663
1664 -- Initial posting of budget to revisions
1665 PSB_BUDGET_REVISIONS_PVT.Create_Base_Budget_Revision
1666 (p_api_version => 1.0,
1667 p_return_status => l_return_status,
1668 p_msg_count => p_msg_count,
1669 p_msg_data => p_msg_data,
1670 p_worksheet_id => p_source_id,
1671 p_event_type => p_event_type -- Bug 3029168
1672 );
1673
1674 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1675 RAISE FND_API.G_EXC_ERROR;
1676 END IF;
1677 END;
1678 -- Bug 3029168 added the clause p_event_type = 'SR'
1679 elsif p_event_type = 'BR' OR p_event_type = 'SR' then
1680 BEGIN
1681 FOR c_rev_type_rec IN c_rev_type LOOP
1682 l_budget_revision_type := c_rev_type_rec.budget_revision_type;
1683 END LOOP;
1684
1685 IF l_budget_revision_type = 'C'
1686 AND p_event_type <> 'SR' THEN -- Bug 3029168
1687 BEGIN
1688 l_cbc_document := TRUE;
1689
1690 PSB_COMMITMENTS_PVT.Post_Commitment_Revisions
1691 (p_api_version => 1.0,
1692 p_return_status => l_return_status,
1693 p_msg_data => l_msg_data,
1694 p_msg_count => l_msg_count,
1695 p_budget_revision_id => p_source_id
1696 );
1697
1698 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1699 Message_Token('BUDGET_REVISION', p_source_id);
1700 Add_Message('PSB', 'PSB_CANNOT_POST_COMMITMENT_REV');
1701 RAISE FND_API.G_EXC_ERROR;
1702 END IF;
1703 END;
1704 ELSE
1705 BEGIN
1706 -- Bug#4310411 Start
1707 -- Replace XLA with Old call.
1708 Insert_Lines_To_GL
1709 (x_return_status => l_return_status,
1710 p_source_id => p_source_id,
1711 p_called_from => 'T',
1712 p_event_type => p_event_type -- Bug 3029168
1713 );
1714 -- Bug#4310411 End
1715
1716 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1717 RAISE FND_API.G_EXC_ERROR;
1718 END IF;
1719 END;
1720 END IF;
1721 END;
1722 END IF;
1723
1724 -- Initialize API RETURN status to success
1725 p_return_status := FND_API.G_RET_STS_SUCCESS;
1726
1727 EXCEPTION
1728
1729 WHEN FND_API.G_EXC_ERROR THEN
1730 ROLLBACK TO Transfer_GLI_To_GL;
1731 p_return_status := FND_API.G_RET_STS_ERROR;
1732
1733 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1734 ROLLBACK TO Transfer_GLI_To_GL;
1735 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1736
1737 WHEN OTHERS THEN
1738 ROLLBACK TO Transfer_GLI_To_GL;
1739 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1740
1741 END Transfer_GLI_To_GL;
1742
1743 /*---------------------------------------------------------------------*/
1744
1745 -- Wrapper routine FOR FND_FLEX_APIS. The reason FOR this IS procedure
1746 -- IS to be called FROM PSBSTGLS.fmb (Setup GL Interfaces) form.
1747 -- Call the fnd_flex_apis directly causes a GPF AND I suspect it IS
1748 -- due to the 64 K size limit
1749
1750 PROCEDURE Get_Qualifier_Segnum
1751 (p_api_version IN NUMBER,
1752 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1753 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1754 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1755 p_return_status OUT NOCOPY VARCHAR2,
1756 p_msg_count OUT NOCOPY NUMBER,
1757 p_msg_data OUT NOCOPY VARCHAR2,
1758 --
1759 p_chart_of_accounts_id IN NUMBER,
1760 p_segment_number OUT NOCOPY NUMBER
1761 ) IS
1762
1763 l_api_name CONSTANT VARCHAR2(30) := 'Get_Qualifier_Segnum';
1764 l_api_version CONSTANT NUMBER := 1.0;
1765
1766 l_seg_num NUMBER;
1767
1768 BEGIN
1769
1770 -- Standard Start of API savepoint
1771 SAVEPOINT Get_Qualifier_Segnum;
1772
1773 -- Standard call to check FOR call compatibility.
1774 IF NOT FND_API.Compatible_API_Call
1775 (l_api_version,
1776 p_api_version,
1777 l_api_name,
1778 G_PKG_NAME
1779 )
1780 THEN
1781 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1782 END IF;
1783
1784 -- Initialize message list IF p_init_msg_list IS set to TRUE.
1785 IF FND_API.to_Boolean (p_init_msg_list) THEN
1786 FND_MSG_PUB.initialize;
1787 END IF;
1788
1789 IF NOT FND_FLEX_APIS.Get_Qualifier_Segnum
1790 (appl_id => 101,
1791 key_flex_code => 'GL#',
1792 structure_number => p_chart_of_accounts_id,
1793 flex_qual_name => 'GL_BALANCING',
1794 segment_number => l_seg_num
1795 )
1796 THEN
1797 Add_Message('PSB', 'PSB_GL_CANNOT_FIND_BAL_SEG');
1798 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1799 END IF;
1800
1801 p_segment_number := l_seg_num ;
1802
1803 -- Initialize API RETURN status to success
1804 p_return_status := FND_API.G_RET_STS_SUCCESS;
1805
1806 EXCEPTION
1807
1808 WHEN FND_API.G_EXC_ERROR THEN
1809 ROLLBACK TO Get_Qualifier_Segnum ;
1810 p_return_status := FND_API.G_RET_STS_ERROR;
1811 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1812 p_data => p_msg_data);
1813
1814 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1815 ROLLBACK TO Get_Qualifier_Segnum ;
1816 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1817 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1818 p_data => p_msg_data);
1819
1820 WHEN OTHERS THEN
1821 ROLLBACK TO Get_Qualifier_Segnum ;
1822 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1823 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1824 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
1825 END IF;
1826 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1827 p_data => p_msg_data);
1828
1829 END Get_Qualifier_Segnum;
1830
1831 /* ----------------------------------------------------------------------- */
1832
1833 PROCEDURE Position_Name
1834 (p_position_line_id IN NUMBER,
1835 x_reference2 OUT NOCOPY VARCHAR2,
1836 x_return_status OUT NOCOPY VARCHAR2,
1837 x_msg_count OUT NOCOPY NUMBER,
1838 x_msg_data OUT NOCOPY VARCHAR2
1839 )
1840 IS
1841
1842 l_api_name CONSTANT VARCHAR2(30) := 'Position_Name';
1843 l_api_version CONSTANT NUMBER := 1.0;
1844
1845
1846 CURSOR c_posname
1847 IS
1848 SELECT psb_pos.name
1849 FROM PSB_WS_POSITION_LINES pos_lines,
1850 PSB_POSITIONS psb_pos
1851 WHERE pos_lines.position_line_id = p_position_line_id
1852 AND psb_pos.position_id = pos_lines.position_id;
1853
1854 BEGIN
1855
1856 IF p_position_line_id IS NULL THEN
1857 x_reference2 := g_je_description;
1858 ELSE
1859 FOR c_posname_rec IN c_posname LOOP
1860 x_reference2 := c_posname_rec.name;
1861 END LOOP;
1862 END IF;
1863
1864 -- Initialize API RETURN status to success
1865 x_return_status := FND_API.G_RET_STS_SUCCESS;
1866
1867 EXCEPTION
1868 WHEN FND_API.G_EXC_ERROR THEN
1869 x_return_status := FND_API.G_RET_STS_ERROR;
1870 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1871 p_data => x_msg_data);
1872
1873 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1874 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1875 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
1876 p_data => x_msg_data);
1877
1878 WHEN OTHERS THEN
1879 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1880 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1881 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
1882 END IF;
1883 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1884 p_data => x_msg_data);
1885
1886 END Position_Name;
1887
1888 /*---------------------------------------------------------------------------*/
1889
1890 PROCEDURE Insert_Lines_Into_PSB_I_Fund
1891 (x_return_status OUT NOCOPY VARCHAR2,
1892 x_msg_count OUT NOCOPY NUMBER,
1893 x_msg_data OUT NOCOPY VARCHAR2,
1894 p_worksheet_id IN NUMBER,
1895 p_gl_budget_set_id IN NUMBER,
1896 p_stage_seq IN NUMBER,
1897 p_year_id IN NUMBER,
1898 p_column IN NUMBER,
1899 p_gl_period IN VARCHAR2,
1900 p_gl_period_start IN DATE,
1901 p_gl_year IN VARCHAR2,
1902 p_period_num IN NUMBER,
1903 p_quarter_num IN NUMBER,
1904 p_je_source IN VARCHAR2,
1905 p_je_category IN VARCHAR2,
1906 p_budget_stage_id IN NUMBER,
1907 p_budget_year_id IN NUMBER,
1908 p_detailed IN VARCHAR2,
1909 p_event_type IN VARCHAR2 DEFAULT 'BP'
1910 )
1911 IS
1912
1913 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Lines_Into_PSB_I_Fund';
1914 l_api_version CONSTANT NUMBER := 1.0;
1915
1916 l_batch_name VARCHAR2(100);
1917 l_batch_description VARCHAR2(100);
1918 l_je_name VARCHAR2(100);
1919 l_je_description VARCHAR2(100);
1920 l_created_by NUMBER;
1921 --
1922 l_sql_bal VARCHAR2(4000);
1923 l_cur_bal INTEGER;
1924 l_num_bal INTEGER;
1925 --
1926 l_budget_version_id NUMBER;
1927 l_code_combination_id NUMBER;
1928 l_dr_amount NUMBER;
1929 l_cr_amount NUMBER;
1930
1931 l_msg_count NUMBER;
1932 l_msg_data VARCHAR2(2000);
1933 l_count NUMBER := 0; -- delete this
1934 l_flex_mapping_set_id NUMBER;
1935 l_budget_year_type_id NUMBER;
1936 l_concat_segments VARCHAR2(2000);
1937 l_budget_version_flag VARCHAR2(1);
1938 l_ccid NUMBER;
1939 l_return_status VARCHAR2(1);
1940
1941 l_sum_count VARCHAR2(1);
1942 l_currency_code VARCHAR2(15);
1943
1944 l_reference2 VARCHAR2(100); -- Bug#4310411
1945
1946 CURSOR c_detail
1947 IS
1948 SELECT a.code_combination_id,
1949 a.position_line_id,
1950 a.account_line_id,
1951 DECODE(a.account_type,'L', NULL, 'O', NULL, 'R', NULL,
1952 DECODE(p_column, 0, NVL(a.ytd_amount, 0),
1953 1, NVL(a.period1_amount, 0),
1954 2, NVL(a.period2_amount, 0),
1955 3, NVL(a.period3_amount, 0),
1956 4, NVL(a.period4_amount, 0),
1957 5, NVL(a.period5_amount, 0),
1958 6, NVL(a.period6_amount, 0),
1959 7, NVL(a.period7_amount, 0),
1960 8, NVL(a.period8_amount, 0),
1961 9, NVL(a.period9_amount, 0),
1962 10, NVL(a.period10_amount, 0),
1963 11, NVL(a.period11_amount, 0),
1964 12, NVL(a.period12_amount, 0)
1965 )
1966 ) dr_amount,
1967 DECODE(a.account_type, 'A', NULL, 'E', NULL,
1968 DECODE(p_column, 0, NVL(a.ytd_amount, 0),
1969 1, NVL(a.period1_amount, 0),
1970 2, NVL(a.period2_amount, 0),
1971 3, NVL(a.period3_amount, 0),
1972 4, NVL(a.period4_amount, 0),
1973 5, NVL(a.period5_amount, 0),
1974 6, NVL(a.period6_amount, 0),
1975 7, NVL(a.period7_amount, 0),
1976 8, NVL(a.period8_amount, 0),
1977 9, NVL(a.period9_amount, 0),
1978 10, NVL(a.period10_amount, 0),
1979 11, NVL(a.period11_amount, 0),
1980 12, NVL(a.period12_amount, 0)
1981 )
1982 ) cr_amount,
1983 DECODE(p_column, 0, NVL(ytd_amount, 0),
1984 1, NVL(period1_amount, 0),
1985 2, NVL(period2_amount, 0),
1986 3, NVL(period3_amount, 0),
1987 4, NVL(period4_amount, 0),
1988 5, NVL(period5_amount, 0),
1989 6, NVL(period6_amount, 0),
1990 7, NVL(period7_amount, 0),
1991 8, NVL(period8_amount, 0),
1992 9, NVL(period9_amount, 0),
1993 10, NVL(period10_amount, 0),
1994 11, NVL(period11_amount, 0),
1995 12, NVL(period12_amount, 0)
1996 ) x_amount
1997 FROM psb_ws_account_lines a,
1998 psb_ws_lines b,
1999 psb_service_packages d
2000 WHERE a.budget_year_id = p_budget_year_id
2001 AND a.balance_type = 'E'
2002 AND a.template_id IS NULL
2003 AND p_stage_seq BETWEEN a.start_stage_seq AND a.current_stage_seq
2004 AND DECODE(p_column,0, NVL(a.ytd_amount,0),
2005 1, NVL(a.period1_amount,0),
2006 2, NVL(a.period2_amount, 0),
2007 3, NVL(a.period3_amount, 0),
2008 4, NVL(a.period4_amount, 0),
2009 5, NVL(a.period5_amount, 0),
2010 6, NVL(a.period6_amount, 0),
2011 7, NVL(a.period7_amount, 0),
2012 8, NVL(a.period8_amount, 0),
2013 9, NVL(a.period9_amount, 0),
2014 10,NVL(a.period10_amount,0),
2015 11,NVL(a.period11_amount, 0),
2016 12, NVL(a.period12_amount, 0)
2017 ) <> 0
2018 -- Bug 3029168 added the following join for STAT currency
2019 AND ((a.currency_code <> 'STAT' AND p_event_type = 'BP') OR
2020 (a.currency_code = 'STAT' AND p_event_type = 'SW'))
2021 AND b.worksheet_id = p_worksheet_id
2022 AND b.account_line_id = a.account_line_id
2023 AND d.service_package_id = a.service_package_id
2024 AND b.view_line_flag = 'Y';
2025
2026 CURSOR c_summary
2027 IS
2028 SELECT a.code_combination_id,
2029 SUM(DECODE(account_type, 'L', NULL, 'O', NULL, 'R', NULL,
2030 DECODE(p_column, 0, NVL(ytd_amount, 0),
2031 1, NVL(period1_amount, 0),
2032 2, NVL(period2_amount, 0),
2033 3, NVL(period3_amount, 0),
2034 4, NVL(period4_amount, 0),
2035 5, NVL(period5_amount, 0),
2036 6, NVL(period6_amount, 0),
2037 7, NVL(period7_amount, 0),
2038 8, NVL(period8_amount, 0),
2039 9, NVL(period9_amount, 0),
2040 10, NVL(period10_amount, 0),
2041 11, NVL(period11_amount, 0),
2042 12, NVL(period12_amount, 0)
2043 )
2044 )
2045 ) dr_amount ,
2046 SUM(DECODE(account_type, 'A', NULL, 'E', NULL,
2047 DECODE(p_column, 0, NVL(ytd_amount, 0),
2048 1, NVL(period1_amount, 0),
2049 2, NVL(period2_amount, 0),
2050 3, NVL(period3_amount, 0),
2051 4, NVL(period4_amount, 0),
2052 5, NVL(period5_amount, 0),
2053 6, NVL(period6_amount, 0),
2054 7, NVL(period7_amount, 0),
2055 8, NVL(period8_amount, 0),
2056 9, NVL(period9_amount, 0),
2057 10, NVL(period10_amount, 0),
2058 11, NVL(period11_amount, 0),
2059 12, NVL(period12_amount, 0)
2060 )
2061 )
2062 ) cr_amount,
2063 SUM(DECODE(p_column, 0, NVL(ytd_amount, 0),
2064 1, NVL(period1_amount, 0),
2065 2, NVL(period2_amount, 0),
2066 3, NVL(period3_amount, 0),
2067 4, NVL(period4_amount, 0),
2068 5, NVL(period5_amount, 0),
2069 6, NVL(period6_amount, 0),
2070 7, NVL(period7_amount, 0),
2071 8, NVL(period8_amount, 0),
2072 9, NVL(period9_amount, 0),
2073 10, NVL(period10_amount, 0),
2074 11, NVL(period11_amount, 0),
2075 12, NVL(period12_amount, 0)
2076 )
2077 ) x_amount
2078 FROM psb_ws_account_lines a,
2079 psb_ws_lines b,
2080 psb_service_packages d
2081 WHERE a.budget_year_id = p_year_id
2082 AND a.balance_type = 'E'
2083 AND a.template_id IS NULL
2084 AND p_stage_seq BETWEEN a.start_stage_seq AND a.current_stage_seq
2085 AND DECODE(p_column,0, NVL(a.ytd_amount,0),
2086 1, NVL(a.period1_amount,0),
2087 2, NVL(a.period2_amount, 0),
2088 3, NVL(a.period3_amount, 0),
2089 4, NVL(a.period4_amount, 0),
2090 5, NVL(a.period5_amount, 0),
2091 6, NVL(a.period6_amount, 0),
2092 7, NVL(a.period7_amount, 0),
2093 8, NVL(a.period8_amount, 0),
2094 9, NVL(a.period9_amount, 0),
2095 10,NVL(a.period10_amount,0),
2096 11,NVL(a.period11_amount, 0),
2097 12, NVL(a.period12_amount, 0)
2098 ) <> 0
2099 -- Bug 3029168 added the following join for STAT currency
2100 AND ((a.currency_code <> 'STAT' AND p_event_type = 'BP') OR
2101 (a.currency_code = 'STAT' AND p_event_type = 'SW'))
2102 AND b.worksheet_id = p_worksheet_id
2103 AND b.account_line_id = a.account_line_id
2104 AND d.service_package_id = a.service_package_id
2105 AND b.view_line_flag = 'Y'
2106 GROUP BY a.code_combination_id;
2107
2108 CURSOR c_bgversion
2109 IS
2110 SELECT gl_budget_version_id
2111 FROM psb_budget_accounts v,
2112 psb_set_relations vs,
2113 psb_gl_budgets vgb
2114 WHERE vgb.gl_budget_set_id = p_gl_budget_set_id
2115 AND vgb.gl_budget_id = vs.gl_budget_id
2116 AND v.code_combination_id = l_code_combination_id
2117 AND vs.account_position_set_id = v.account_position_set_id
2118 AND p_gl_period_start
2119 BETWEEN vgb.start_date AND NVL(vgb.end_date, p_gl_period_start);
2120
2121 CURSOR c_ws
2122 IS
2123 SELECT flex_mapping_set_id
2124 FROM psb_worksheets
2125 WHERE worksheet_id = p_worksheet_id;
2126
2127 CURSOR c_type
2128 IS
2129 SELECT budget_year_type_id
2130 FROM psb_budget_periods
2131 WHERE budget_period_id = p_year_id;
2132
2133 BEGIN
2134
2135 SAVEPOINT Insert_Lines_Into_PSB_I_Fund;
2136
2137 l_created_by := fnd_global.user_id;
2138
2139 FND_MESSAGE.SET_NAME('PSB', 'PSB_GL_BJE_BATCH_NAME');
2140 FND_MESSAGE.SET_TOKEN('WORKSHEET_ID', p_worksheet_id);
2141 l_batch_name := FND_MESSAGE.Get;
2142
2143 FND_MESSAGE.SET_NAME('PSB', 'PSB_GL_BJE_BATCH_DESC');
2144 FND_MESSAGE.SET_TOKEN('WORKSHEET_ID', p_worksheet_id);
2145 l_batch_description := FND_MESSAGE.Get;
2146
2147 FND_MESSAGE.SET_NAME('PSB', 'PSB_GL_BJE_JE_NAME');
2148 FND_MESSAGE.SET_TOKEN('WORKSHEET_ID', p_worksheet_id);
2149 l_je_name := FND_MESSAGE.Get;
2150
2151 FND_MESSAGE.SET_NAME('PSB', 'PSB_GL_BJE_JE_DESC');
2152 FND_MESSAGE.SET_TOKEN('WORKSHEET_ID', p_worksheet_id);
2153 l_je_description := FND_MESSAGE.Get;
2154
2155 /* Bug 3029168 Start */
2156 IF p_event_type = 'SW' THEN
2157 l_currency_code := 'STAT';
2158 ELSE
2159 l_currency_code := g_currency_code;
2160 END IF;
2161 /* Bug 3029168 End */
2162
2163
2164 --++ process CURSOR, THEN get mapped account AND corresponding funding budget
2165 --++
2166 FOR c_ws_rec IN c_ws LOOP
2167 l_flex_mapping_set_id := c_ws_rec.FLEX_MAPPING_SET_ID;
2168 g_flex_mapping_set_id := l_flex_mapping_set_id;
2169 END LOOP;
2170
2171 FOR c_type_rec IN c_type LOOP
2172 l_budget_year_type_id := c_type_rec.budget_year_type_id;
2173 g_budget_year_type_id := l_budget_year_type_id;
2174 END LOOP;
2175
2176 IF p_detailed = 'D' THEN
2177 -- ++ detail
2178 BEGIN
2179
2180 FOR c_detail_rec IN c_detail LOOP
2181 IF g_flex_mapping_set_id IS NOT NULL THEN
2182 --++ flex mapping
2183 l_code_combination_id
2184 := PSB_Flex_Mapping_PVT.Get_Mapped_CCID
2185 (p_api_version => '1.0',
2186 p_init_msg_list => FND_API.G_FALSE,
2187 p_commit => FND_API.G_FALSE,
2188 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2189 p_ccid => c_detail_rec.code_combination_id,
2190 p_budget_year_type_id => l_budget_year_type_id,
2191 p_flexfield_mapping_set_id => l_flex_mapping_set_id ,
2192 p_mapping_mode => 'GL_POSTING'
2193 );
2194
2195 IF l_code_combination_id = 0 THEN
2196 l_code_combination_id := c_detail_rec.code_combination_id;
2197 END IF;
2198 l_ccid := l_code_combination_id;
2199 ELSE
2200 l_ccid := c_detail_rec.code_combination_id;
2201 END IF;
2202
2203 -- Get the description of the current Pos_line_id.
2204 Position_Name
2205 (p_position_line_id => c_detail_rec.position_line_id,
2206 x_reference2 => l_reference2,
2207 x_return_status => l_return_status,
2208 x_msg_count => l_msg_count,
2209 x_msg_data => l_msg_data
2210 );
2211
2212 -- ++ FOR each detail_rec, process 2 times - one FOR Permanent AND another
2213 -- ++ FOR 'ALL' (IF all exists');
2214 FOR i IN 1 .. 2 LOOP
2215 -- ++ post to gl_interface FOR both permanent AND all
2216 IF i = 1 THEN
2217 l_budget_version_flag := 'P';
2218 ELSE
2219 l_budget_version_flag := 'A';
2220 END IF;
2221
2222 IF i = 2 AND NOT FND_API.to_Boolean(g_post_to_all) THEN
2223 EXIT;
2224 -- ++ skip posting since there's no 'ALL' posting defined
2225 END IF;
2226
2227 --++ get corresponding budget version id AND get out IF with error
2228 --++ use worksheet ccid
2229 PSB_GL_BUDGET_PVT.Find_GL_Budget
2230 (p_api_version => 1.0,
2231 p_return_status => l_return_status,
2232 p_msg_count => l_msg_count,
2233 p_msg_data => l_msg_data,
2234 p_gl_budget_set_id => p_gl_budget_set_id,
2235 p_code_combination_id => c_detail_rec.code_combination_id,
2236 p_start_date => p_gl_period_start,
2237 p_dual_posting_type => l_budget_version_flag,
2238 p_gl_budget_version_id => l_budget_version_id
2239 );
2240
2241 IF l_budget_version_id IS NULL THEN
2242 l_concat_segments
2243 := FND_FLEX_EXT.Get_Segs
2244 (application_short_name => 'SQLGL',
2245 key_flex_code => 'GL#',
2246 structure_number => g_chart_of_accounts_id,
2247 combination_id => c_detail_rec.code_combination_id
2248 );
2249
2250 Message_Token('CCID', l_concat_segments);
2251 Add_Message('PSB', 'PSB_CCID_NO_FUND_INFO');
2252 RAISE FND_API.G_EXC_ERROR;
2253 CLOSE c_bgversion;
2254 END IF;
2255
2256 --++
2257 --++ THEN INTO psb_gl_interfaces
2258 --++
2259 INSERT INTO psb_gl_interfaces
2260 (worksheet_id,
2261 group_id,
2262 status,
2263 set_of_books_id,
2264 user_je_source_name,
2265 user_je_category_name,
2266 currency_code,
2267 date_created,
2268 created_by,
2269 actual_flag,
2270 budget_version_id,
2271 accounting_date,
2272 period_name,
2273 period_year,
2274 period_num,
2275 quarter_num,
2276 code_combination_id,
2277 entered_dr,
2278 entered_cr,
2279 reference1,
2280 reference2,
2281 reference4,
2282 reference5,
2283 budget_stage_id,
2284 budget_year_id,
2285 je_type,
2286 amount,
2287 budget_source_type,
2288 budget_version_flag,
2289 balancing_entry_flag,
2290 gl_budget_set_id
2291 )
2292 VALUES
2293 (p_worksheet_id,
2294 p_worksheet_id ,
2295 'NEW',
2296 g_set_of_books_id ,
2297 p_je_source,
2298 p_je_category ,
2299 l_currency_code, -- Bug 3029168
2300 sysdate,
2301 l_created_by ,
2302 'B',
2303 l_budget_version_id,
2304 p_gl_period_start,
2305 p_gl_period,
2306 p_gl_year,
2307 p_period_num ,
2308 p_quarter_num,
2309 l_ccid ,
2310 c_detail_rec.dr_amount,
2311 c_detail_rec.cr_amount,
2312 g_je_name,
2313 l_reference2,
2314 c_detail_rec.account_line_id,
2315 NULL,
2316 p_budget_stage_id,
2317 p_budget_year_id ,
2318 p_detailed,
2319 c_detail_rec.x_amount,
2320 p_event_type , -- Bug 3029168
2321 l_budget_version_flag,
2322 'N',
2323 p_gl_budget_set_id
2324 );
2325 END LOOP; -- END of 2 loops FOR permanent AND temp FOR each record
2326 END LOOP; -- END of detail rec processing
2327 END;
2328
2329 --++ END detail
2330 ELSE
2331 --++ summary
2332 BEGIN
2333
2334 FOR c_summary_rec IN c_summary LOOP
2335 IF g_flex_mapping_set_id IS NOT NULL THEN
2336 --++ get flex mapping
2337 l_code_combination_id
2338 := PSB_Flex_Mapping_PVT.Get_Mapped_CCID
2339 (
2340 p_api_version => '1.0',
2341 p_init_msg_list => FND_API.G_FALSE,
2342 p_commit => FND_API.G_FALSE,
2343 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2344 p_ccid => c_summary_rec.code_combination_id,
2345 p_budget_year_type_id =>l_budget_year_type_id,
2346 p_flexfield_mapping_set_id => l_flex_mapping_set_id ,
2347 p_mapping_mode => 'GL_POSTING'
2348 );
2349
2350 IF l_code_combination_id = 0 THEN
2351 l_code_combination_id := c_summary_rec.code_combination_id;
2352 END IF;
2353
2354 l_ccid := l_code_combination_id;
2355
2356 ELSE
2357 l_ccid := c_summary_rec.code_combination_id;
2358 END IF;
2359
2360 FOR i IN 1 .. 2 LOOP
2361 -- ++ post to gl_interface FOR both permanent AND all FOR each record
2362 IF i = 1 THEN
2363 l_budget_version_flag := 'P';
2364 ELSE
2365 l_budget_version_flag := 'A';
2366 END IF;
2367
2368 IF i = 2 AND NOT FND_API.to_Boolean(g_post_to_all) THEN
2369 EXIT;
2370 -- ++ skip posting since there's no 'ALL' posting defined
2371 END IF;
2372
2373 --++ get corresponding budget version id AND get out IF with error
2374 --++ use worksheet ccid
2375 PSB_GL_BUDGET_PVT.Find_GL_Budget
2376 (p_api_version => 1.0,
2377 p_return_status => l_return_status,
2378 p_msg_count => l_msg_count,
2379 p_msg_data => l_msg_data,
2380 p_gl_budget_set_id => p_gl_budget_set_id,
2381 p_code_combination_id => c_summary_rec.code_combination_id,
2382 p_start_date => p_gl_period_start,
2383 p_dual_posting_type => l_budget_version_flag,
2384 p_gl_budget_version_id => l_budget_version_id
2385 );
2386
2387 IF l_budget_version_id IS NULL THEN
2388 l_concat_segments
2389 := FND_FLEX_EXT.Get_Segs
2390 (application_short_name => 'SQLGL',
2391 key_flex_code => 'GL#',
2392 structure_number => g_chart_of_accounts_id,
2393 combination_id => c_summary_rec.code_combination_id
2394 );
2395 Message_Token('CCID', l_concat_segments);
2396 Add_Message('PSB', 'PSB_CCID_NO_FUND_INFO');
2397 RAISE FND_API.G_EXC_ERROR;
2398 END IF;
2399
2400 --++
2401 --++ THEN INTO psb_gl_interfaces
2402 --++
2403 INSERT INTO psb_gl_interfaces
2404 (worksheet_id,
2405 group_id,
2406 status,
2407 set_of_books_id,
2408 user_je_source_name,
2409 user_je_category_name,
2410 currency_code,
2411 date_created,
2412 created_by,
2413 actual_flag,
2414 budget_version_id,
2415 accounting_date,
2416 period_name,
2417 period_year,
2418 period_num,
2419 quarter_num,
2420 code_combination_id,
2421 entered_dr,
2422 entered_cr,
2423 reference1,
2424 reference2,
2425 reference4,
2426 reference5,
2427 budget_stage_id,
2428 budget_year_id,
2429 je_type,
2430 amount,
2431 budget_source_type,
2432 budget_version_flag,
2433 balancing_entry_flag,
2434 gl_budget_set_id
2435 )
2436 VALUES
2437 (p_worksheet_id,
2438 p_worksheet_id,
2439 'NEW',
2440 g_set_of_books_id,
2441 p_je_source,
2442 p_je_category ,
2443 l_currency_code,
2444 sysdate,
2445 l_created_by ,
2446 'B',
2447 l_budget_version_id,
2448 p_gl_period_start,
2449 p_gl_period,
2450 p_gl_year,
2451 p_period_num ,
2452 p_quarter_num,
2453 l_ccid ,
2454 c_summary_rec.dr_amount,
2455 c_summary_rec.cr_amount,
2456 g_je_name ,
2457 g_je_description,
2458 NULL,
2459 NULL,
2460 p_budget_stage_id,
2461 p_budget_year_id ,
2462 p_detailed,
2463 c_summary_rec.x_amount,
2464 p_event_type , -- Bug 3029168
2465 l_budget_version_flag,
2466 'N',
2467 p_GL_budget_set_id
2468 );
2469 END LOOP; -- END of 2 loops FOR permanent AND temp FOR each record
2470 END LOOP; -- END of summary record processing
2471
2472 END;
2473 END IF;
2474
2475 x_return_status := FND_API.G_RET_STS_SUCCESS;
2476
2477 EXCEPTION
2478 WHEN FND_API.G_EXC_ERROR THEN
2479 ROLLBACK TO Insert_Lines_Into_PSB_I_Fund;
2480 x_return_status := FND_API.G_RET_STS_ERROR;
2481
2482 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2483 ROLLBACK TO Insert_Lines_Into_PSB_I_Fund;
2484 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2485
2486 WHEN OTHERS THEN
2487 ROLLBACK TO Insert_Lines_Into_PSB_I_Fund;
2488 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2489 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2490 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
2491 END IF;
2492 END Insert_Lines_Into_PSB_I_Fund;
2493 /* ----------------------------------------------------------------------- */
2494
2495 PROCEDURE Create_JE_Lines_Fund
2496 (x_return_status OUT NOCOPY VARCHAR2,
2497 p_worksheet_id IN NUMBER,
2498 p_budget_stage_id IN NUMBER,
2499 p_budget_year_id IN NUMBER,
2500 p_detailed IN VARCHAR2,
2501 p_auto_offset IN VARCHAR2,
2502 p_gl_budget_set_id IN NUMBER,
2503 p_start_date IN DATE,
2504 p_end_date IN DATE,
2505 p_column IN NUMBER,
2506 p_je_source IN VARCHAR2,
2507 p_je_category IN VARCHAR2,
2508 p_period_name IN VARCHAR2,
2509 p_gl_year IN NUMBER,
2510 p_gl_period_num IN NUMBER,
2511 p_gl_quarter_num IN NUMBER,
2512 p_event_type IN VARCHAR2 DEFAULT 'BP'
2513 )
2514 IS
2515
2516 l_api_name CONSTANT VARCHAR2(30) := 'Create_JE_Lines_Fund';
2517 l_api_version CONSTANT NUMBER := 1.0;
2518
2519 l_gl_period VARCHAR2(15);
2520 l_gl_year NUMBER;
2521 l_gl_period_num NUMBER;
2522 l_gl_quarter_num NUMBER;
2523 l_worksheet_id NUMBER;
2524 l_stage_seq NUMBER;
2525 l_gl_period_start DATE;
2526
2527 l_event_number NUMBER;
2528 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2529 l_msg_count NUMBER;
2530 l_msg_data VARCHAR2(2000);
2531
2532 CURSOR bgt_stage
2533 IS
2534 SELECT sequence_number
2535 FROM psb_budget_stages
2536 WHERE budget_stage_id = p_budget_stage_id;
2537
2538 BEGIN
2539
2540 FOR bgt_stage_rec IN bgt_stage LOOP
2541 l_stage_seq := bgt_stage_rec.sequence_number;
2542 END LOOP;
2543
2544 --++ INSERT INTO PSB_GL_INTERFACE Table.
2545 Insert_Lines_Into_PSB_I_Fund
2546 (x_return_status => l_return_status ,
2547 x_msg_count => l_msg_count,
2548 x_msg_data => l_msg_data,
2549 p_worksheet_id => p_worksheet_id,
2550 p_gl_budget_set_id => p_gl_budget_set_id ,
2551 p_stage_seq => l_stage_seq,
2552 p_year_id => p_budget_year_id,
2553 p_column => p_column,
2554 p_gl_period => p_period_name,
2555 p_gl_period_start => p_start_date,
2556 p_gl_year => p_gl_year,
2557 p_period_num => p_gl_period_num,
2558 p_quarter_num => p_gl_quarter_num,
2559 p_je_source => p_je_source,
2560 p_je_category => p_je_category,
2561 p_budget_stage_id => p_budget_stage_id,
2562 p_budget_year_id => p_budget_year_id,
2563 p_detailed => p_detailed,
2564 p_event_type => p_event_type); -- Bug 3029168
2565
2566 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2567 RAISE FND_API.G_EXC_ERROR;
2568 END IF;
2569
2570 IF p_auto_offset = 'Y' THEN
2571 Balance_Journal
2572 (x_return_status => l_return_status ,
2573 p_worksheet_id => p_worksheet_id,
2574 p_period_name => p_period_name,
2575 p_gl_budget_set_id => p_gl_budget_set_id
2576 );
2577
2578 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2579 RAISE FND_API.G_EXC_ERROR;
2580 END IF;
2581 END IF;
2582
2583 -- Initialize API RETURN status to success
2584 x_return_status := FND_API.G_RET_STS_SUCCESS;
2585
2586 EXCEPTION
2587 WHEN FND_API.G_EXC_ERROR THEN
2588 x_return_status := FND_API.G_RET_STS_ERROR;
2589
2590 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2591 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2592
2593 WHEN OTHERS THEN
2594 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2595 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2596 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2597 END IF;
2598
2599 END Create_JE_Lines_Fund;
2600
2601 /* ----------------------------------------------------------------------- */
2602
2603 PROCEDURE Delete_Old_Run
2604 (x_return_status OUT NOCOPY VARCHAR2,
2605 p_worksheet_id IN NUMBER,
2606 p_budget_source_type IN VARCHAR2
2607 )
2608 IS
2609 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Old_Run';
2610 l_api_version CONSTANT NUMBER := 1.0;
2611
2612 BEGIN
2613
2614 SAVEPOINT Delete_Old_Run;
2615
2616 DELETE FROM psb_gl_interfaces
2617 WHERE worksheet_id = p_worksheet_id
2618 AND NVL(budget_source_type, 'P') = p_budget_source_type;
2619
2620 x_return_status := FND_API.G_RET_STS_SUCCESS;
2621
2622 EXCEPTION
2623
2624 WHEN FND_API.G_EXC_ERROR THEN
2625 ROLLBACK TO Delete_Old_Run;
2626 x_return_status := FND_API.G_RET_STS_ERROR;
2627
2628 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2629 ROLLBACK TO Delete_Old_Run;
2630 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2631
2632 WHEN OTHERS THEN
2633 ROLLBACK TO Delete_Old_Run;
2634 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2635 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2636 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2637 END IF;
2638
2639 END Delete_Old_Run;
2640 /* ----------------------------------------------------------------------- */
2641
2642 -- commenting out the savepoints FOR XLA transfer since the XLA transfer
2643 -- program commits within the process AND this erases the savepoints established
2644
2645 PROCEDURE Create_Budget_Journal_Fund
2646 (x_return_status OUT NOCOPY VARCHAR2,
2647 p_worksheet_id NUMBER,
2648 p_budget_stage_id NUMBER,
2649 p_budget_year_id NUMBER,
2650 p_year_journal VARCHAR2,
2651 p_gl_transfer_mode VARCHAR2,
2652 p_auto_offset VARCHAR2,
2653 p_gl_budget_set_id NUMBER,
2654 p_run_mode VARCHAR2,
2655 p_order_by1 VARCHAR2,
2656 p_order_by2 VARCHAR2,
2657 p_order_by3 VARCHAR2,
2658 p_currency_code VARCHAR2 DEFAULT 'C' -- Bug 3029168
2659 )
2660 IS
2661
2662 l_year_start_date DATE;
2663 l_year_end_date DATE;
2664 l_budget_year_type_id NUMBER;
2665
2666 l_stage_sequence NUMBER;
2667
2668 l_period_name VARCHAR2(15);
2669 l_period_start_date DATE;
2670 l_period_end_date DATE;
2671
2672 l_gl_year NUMBER;
2673 l_gl_period_num NUMBER;
2674 l_gl_quarter_num NUMBER;
2675
2676 /*FOR Bug No : 2543724 Start*/
2677 --commented the following two variables as they are NOT being used
2678 --AND also removed them FROM passing INTO Get_GL_Period procedure
2679 --l_next_period VARCHAR2(15);
2680 --l_reversal_date DATE;
2681 /*FOR Bug No : 2543724 END*/
2682
2683 l_column NUMBER;
2684
2685 l_req_id NUMBER;
2686 l_funding_status VARCHAR2(1);
2687 l_validation_status VARCHAR2(1);
2688 l_acct_overlap_status VARCHAR2(1);
2689
2690 l_event_number NUMBER;
2691 l_accounting_date DATE;
2692 l_period_status VARCHAR2(1);
2693
2694 l_return_status VARCHAR2(1);
2695 l_msg_count NUMBER;
2696 l_msg_data VARCHAR2(2000);
2697
2698 l_cbc_document BOOLEAN := FALSE;
2699 l_include_cbc_commit_balance VARCHAR2(1);
2700 l_include_cbc_oblig_balance VARCHAR2(1);
2701 l_include_cbc_budget_balance VARCHAR2(1);
2702
2703 l_gl_budget_set_id NUMBER;
2704 l_event_type VARCHAR2(5);
2705
2706 CURSOR c_year
2707 IS
2708 SELECT start_date,
2709 end_date,
2710 budget_year_type_id
2711 FROM psb_budget_periods
2712 WHERE budget_period_id = p_budget_year_id;
2713
2714 CURSOR c_stage
2715 IS
2716 SELECT sequence_number
2717 FROM psb_budget_stages
2718 WHERE budget_stage_id = p_budget_stage_id;
2719
2720 CURSOR c_period
2721 IS
2722 SELECT budget_period_id,
2723 start_date,
2724 end_date
2725 FROM psb_budget_periods
2726 WHERE budget_period_type = 'P'
2727 AND parent_budget_period_id = p_budget_year_id
2728 ORDER BY start_date;
2729
2730 CURSOR c_ws
2731 IS
2732 SELECT include_cbc_commit_balance,
2733 include_cbc_oblig_balance,
2734 include_cbc_budget_balance
2735 FROM psb_worksheets
2736 WHERE worksheet_id = p_worksheet_id;
2737
2738 CURSOR c_post_to_all
2739 IS
2740 SELECT 'Y'
2741 FROM dual
2742 WHERE EXISTS
2743 (SELECT 1
2744 FROM psb_GL_BUDGETS
2745 WHERE dual_posting_type = 'A'
2746 AND gl_budget_set_id = p_gl_budget_set_id
2747 AND start_date BETWEEN l_year_start_date AND l_year_end_date
2748 );
2749
2750 BEGIN
2751
2752 FND_MSG_PUB.initialize;
2753
2754 /* Bug 3029168 Start */
2755 IF p_currency_code = 'C' THEN
2756 l_event_type := 'BP';
2757 ELSE
2758 l_event_type := 'SW';
2759 END IF;
2760 /* Bug 3029168 End */
2761 g_budget_year_id := p_budget_year_id;
2762
2763 Initialize
2764 (x_return_status => l_return_status,
2765 p_event_type => l_event_type, -- Bug 3029168
2766 p_source_id => p_worksheet_id,
2767 p_auto_offset => p_auto_offset
2768 );
2769
2770 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2771 RAISE FND_API.G_EXC_ERROR;
2772 END IF;
2773
2774 -- Dele the old run for the same worksheet
2775 -- and budget source.
2776 Delete_Old_Run
2777 (x_return_status => l_return_status,
2778 p_worksheet_id => p_worksheet_id,
2779 p_budget_source_type => g_budget_source_type
2780 );
2781
2782 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2783 RAISE FND_API.G_EXC_ERROR;
2784 END IF;
2785
2786 --
2787 FOR c_year_rec IN c_year LOOP
2788 l_year_start_date := c_year_rec.start_date;
2789 l_year_end_date := c_year_rec.end_date;
2790 l_budget_year_type_id := c_year_rec.budget_year_type_id;
2791 END LOOP;
2792
2793 FOR c_ws_rec IN c_ws LOOP
2794 l_include_cbc_commit_balance := c_ws_rec.include_cbc_commit_balance;
2795 l_include_cbc_oblig_balance := c_ws_rec.include_cbc_oblig_balance;
2796 l_include_cbc_budget_balance := c_ws_rec.include_cbc_budget_balance;
2797 END LOOP;
2798
2799 -- initialize global which determines IF there's an 'ALL' posting type
2800 FOR c_post_rec IN c_post_to_all LOOP
2801 g_post_to_all := FND_API.G_TRUE;
2802 END LOOP;
2803
2804 IF g_budget_by_position = 'Y' AND p_currency_code = 'C' THEN
2805 BEGIN
2806 PSB_POSITION_CONTROL_PVT.Validate_Position_Budget
2807 (p_return_status => l_return_status,
2808 p_event_type => l_event_type,
2809 p_source_id => p_worksheet_id
2810 );
2811
2812 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2813 RAISE FND_API.G_EXC_ERROR;
2814 END IF;
2815 END;
2816 END IF;
2817
2818 FOR c_stage_rec IN c_stage LOOP
2819 l_stage_sequence := c_stage_rec.sequence_number;
2820 END LOOP;
2821
2822 l_gl_budget_set_id := p_gl_budget_set_id;
2823
2824 PSB_GL_BUDGET_SET_PVT.Validate_Account_Overlap
2825 (p_api_version => 1.0,
2826 p_return_status => l_return_status,
2827 p_msg_data => l_msg_data,
2828 p_msg_count => l_msg_count,
2829 p_gl_budget_set_id => l_gl_budget_set_id,
2830 p_validation_status => l_acct_overlap_status
2831 );
2832
2833 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2834 RAISE FND_API.G_EXC_ERROR;
2835 END IF;
2836
2837 IF l_acct_overlap_status <> FND_API.G_RET_STS_SUCCESS THEN
2838 -- dummy line to force this error to print to outfile IF no
2839 -- error IN validate funded proc
2840 Add_Message('PSB', 'PSB_VAL_LINE');
2841 END IF;
2842
2843 /*FOR Bug No : 2712019 Start*/
2844 --Validate the GL Budget year FOR all the gl budgets IN PSB GL Budget set
2845 --AND this procedure throws an error IF there IS no correponding year
2846 --opened FOR any GL Budget IN PSB GL Budget Set
2847
2848 Validate_GL_Budget_Year
2849 (x_return_status => l_return_status,
2850 p_gl_budget_set_id => p_gl_budget_set_id,
2851 p_year_start_date => l_year_start_date,
2852 p_year_end_date => l_year_end_date
2853 );
2854
2855 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2856 RAISE FND_API.G_EXC_ERROR;
2857 END IF;
2858 /*FOR Bug No : 2712019 END*/
2859
2860 -- continue validation regardless value of validation status
2861 IF p_year_journal = 'Y' THEN
2862 BEGIN
2863 Get_GL_Period
2864 (x_return_status => l_return_status,
2865 x_effective_period_num => l_event_number,
2866 p_start_date => l_year_start_date,
2867 x_period_name => l_period_name,
2868 x_period_start_date => l_accounting_date,
2869 x_period_end_date => l_period_end_date,
2870 x_period_status => l_period_status,
2871 x_period_year => l_gl_year,
2872 x_period_number => l_gl_period_num,
2873 x_quarter_number => l_gl_quarter_num
2874 );
2875
2876 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2877 RAISE FND_API.G_EXC_ERROR;
2878 END IF;
2879
2880 Validate_Funding_Account
2881 (x_return_status => l_return_status,
2882 p_event_type => l_event_type,
2883 p_source_id => p_worksheet_id,
2884 p_stage_sequence => l_stage_sequence,
2885 p_budget_year_id => p_budget_year_id,
2886 p_gl_budget_set_id => l_gl_budget_set_id,
2887 p_start_date => l_accounting_date,
2888 x_validation_status => l_validation_status
2889 );
2890
2891 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2892 RAISE FND_API.G_EXC_ERROR;
2893 END IF;
2894
2895 l_funding_status := l_validation_status;
2896 END;
2897 ELSE
2898 BEGIN
2899 l_funding_status := FND_API.G_RET_STS_SUCCESS;
2900
2901 FOR c_period_rec IN c_period LOOP
2902 Get_GL_Period
2903 (x_return_status => l_return_status,
2904 x_effective_period_num => l_event_number,
2905 p_start_date => c_period_rec.start_date,
2906 x_period_name => l_period_name,
2907 x_period_start_date => l_accounting_date,
2908 x_period_end_date => l_period_end_date,
2909 x_period_status => l_period_status,
2910 x_period_year => l_gl_year,
2911 x_period_number => l_gl_period_num,
2912 x_quarter_number => l_gl_quarter_num
2913 );
2914
2915 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2916 RAISE FND_API.G_EXC_ERROR;
2917 END IF;
2918
2919 Validate_Funding_Account
2920 (x_return_status => l_return_status,
2921 p_event_type => l_event_type,
2922 p_source_id => p_worksheet_id,
2923 p_stage_sequence => l_stage_sequence,
2924 p_budget_year_id => p_budget_year_id,
2925 p_gl_budget_set_id => l_gl_budget_set_id,
2926 p_start_date => l_accounting_date,
2927 x_validation_status => l_validation_status
2928 );
2929
2930 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2931 RAISE FND_API.G_EXC_ERROR;
2932 END IF;
2933
2934 IF ((l_funding_status = FND_API.G_RET_STS_SUCCESS)
2935 AND
2936 (l_validation_status <> FND_API.G_RET_STS_SUCCESS)
2937 )
2938 THEN
2939 l_funding_status := l_validation_status;
2940 END IF;
2941 END LOOP;
2942 END;
2943 END IF;
2944
2945 IF (l_funding_status <> FND_API.G_RET_STS_SUCCESS
2946 OR
2947 l_acct_overlap_status <> FND_API.G_RET_STS_SUCCESS
2948 )
2949 THEN
2950 RAISE FND_API.G_EXC_ERROR;
2951 ELSE
2952 BEGIN
2953
2954 IF p_year_journal = 'Y' THEN
2955 BEGIN
2956 Get_GL_Period
2957 (x_return_status => l_return_status,
2958 x_effective_period_num => l_event_number,
2959 p_start_date => l_year_start_date,
2960 x_period_name => l_period_name,
2961 x_period_start_date => l_accounting_date,
2962 x_period_end_date => l_period_end_date,
2963 x_period_status => l_period_status,
2964 x_period_year => l_gl_year,
2965 x_period_number => l_gl_period_num,
2966 x_quarter_number => l_gl_quarter_num
2967 );
2968
2969 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2970 RAISE FND_API.G_EXC_ERROR;
2971 END IF;
2972
2973 -- Include your changes here.
2974 l_column := 0;
2975
2976 Create_JE_Lines_Fund
2977 (x_return_status => l_return_status,
2978 p_worksheet_id => p_worksheet_id,
2979 p_budget_stage_id => p_budget_stage_id,
2980 p_budget_year_id => p_budget_year_id ,
2981 p_detailed => p_gl_transfer_mode,
2982 p_auto_offset => p_auto_offset,
2983 p_gl_budget_set_id => p_gl_budget_set_id,
2984 p_start_date => l_accounting_date,
2985 p_end_date => l_year_end_date,
2986 p_column => l_column,
2987 p_je_source => g_source_name,
2988 p_je_category => g_category_name,
2989 p_period_name => l_period_name,
2990 p_gl_year => l_gl_year,
2991 p_gl_period_num => l_gl_period_num,
2992 p_gl_quarter_num => l_gl_quarter_num,
2993 p_event_type => l_event_type
2994 );
2995
2996 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2997 RAISE FND_API.G_EXC_ERROR;
2998 END IF;
2999 -- Include your changes here done.
3000 END;
3001 ELSE
3002 BEGIN
3003 l_column := 0;
3004 FOR c_period_rec IN c_period LOOP
3005 l_column := l_column + 1;
3006
3007 Get_GL_Period
3008 (x_return_status => l_return_status,
3009 x_effective_period_num => l_event_number,
3010 p_start_date => c_period_rec.start_date,
3011 x_period_name => l_period_name,
3012 x_period_start_date => l_accounting_date,
3013 x_period_end_date => l_period_end_date,
3014 x_period_status => l_period_status,
3015 x_period_year => l_gl_year,
3016 x_period_number => l_gl_period_num,
3017 x_quarter_number => l_gl_quarter_num
3018 );
3019
3020 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3021 RAISE FND_API.G_EXC_ERROR;
3022 END IF;
3023
3024 -- Include your changes here.
3025 Create_JE_Lines_Fund
3026 (x_return_status => l_return_status,
3027 p_worksheet_id => p_worksheet_id,
3028 p_budget_stage_id => p_budget_stage_id,
3029 p_budget_year_id => p_budget_year_id ,
3030 p_detailed => p_gl_transfer_mode,
3031 p_auto_offset => p_auto_offset,
3032 p_gl_budget_set_id => p_gl_budget_set_id ,
3033 p_start_date => l_accounting_date,
3034 p_end_date => l_period_end_date,
3035 p_column => l_column,
3036 p_je_source => g_source_name,
3037 p_je_category => g_category_name,
3038 p_period_name => l_period_name,
3039 p_gl_year => l_gl_year,
3040 p_gl_period_num => l_gl_period_num,
3041 p_gl_quarter_num => l_gl_quarter_num,
3042 p_event_type => l_event_type
3043 );
3044
3045 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3046 RAISE FND_API.G_EXC_ERROR;
3047 END IF;
3048 -- Include your changes here done.
3049 END LOOP; /*END of LOOP FOR getting gl periods*/
3050 END;
3051 END IF;
3052
3053 IF p_run_mode <> 'F' THEN --
3054 -- Starting the concurrent program FOR trial mode
3055 --
3056 Submit_Concurrent_Request
3057 (x_return_status => l_return_status,
3058 p_source_id => p_worksheet_id,
3059 p_event_type => l_event_type,
3060 p_order_by1 => p_order_by1,
3061 p_order_by2 => p_order_by2,
3062 p_order_by3 => p_order_by3,
3063 p_gl_budget_set_id => l_gl_budget_set_id,
3064 p_budget_year_id => p_budget_year_id,
3065 p_currency_code => p_currency_code -- Bug 3029168
3066 );
3067
3068 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3069 RAISE FND_API.G_EXC_ERROR;
3070 END IF;
3071 END IF;
3072 END;
3073 END IF; -- no validation error found
3074
3075 -- ++ Final mode update to GL
3076 IF p_run_mode = 'F' THEN
3077 -- no report FOR final since xla will process all records
3078 BEGIN
3079 IF (NVL(l_include_cbc_commit_balance,'N') = 'Y'
3080 OR
3081 NVL(l_include_cbc_oblig_balance,'N') = 'Y'
3082 OR
3083 NVL(l_include_cbc_budget_balance, 'N') = 'Y')
3084 AND l_event_type <> 'SW'
3085 THEN
3086 BEGIN
3087 l_cbc_document := TRUE;
3088
3089 PSB_COMMITMENTS_PVT.Post_Commitment_Worksheet
3090 (p_api_version => 1.0,
3091 p_return_status => l_return_status,
3092 p_msg_data => l_msg_data,
3093 p_msg_count => l_msg_count,
3094 p_worksheet_id => p_worksheet_id
3095 );
3096
3097 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3098 Message_Token('WORKSHEET', p_worksheet_id);
3099 Add_Message('PSB', 'PSB_CANNOT_POST_COMMITMENT_WS');
3100 RAISE FND_API.G_EXC_ERROR;
3101 END IF;
3102 END;
3103 ELSE
3104 BEGIN
3105 -- Include your changes here. At the place of XLA call, make ur own call FOR Journel Import.
3106 Insert_Lines_To_GL
3107 (x_return_status => l_return_status,
3108 p_source_id => p_worksheet_id,
3109 p_called_from => 'C',
3110 p_event_type => l_event_type
3111 );
3112 -- Include your changes here. END.
3113
3114 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3115 RAISE FND_API.G_EXC_ERROR;
3116 END IF;
3117 END;
3118 END IF;
3119
3120 PSB_BUDGET_REVISIONS_PVT.Create_Base_Budget_Revision
3121 (p_api_version => 1.0,
3122 p_return_status => l_return_status,
3123 p_msg_count => l_msg_count,
3124 p_msg_data => l_msg_data,
3125 p_worksheet_id => p_worksheet_id,
3126 p_event_type => l_event_type -- Bug 3029168
3127 );
3128
3129 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3130 RAISE FND_API.G_EXC_ERROR;
3131 END IF;
3132 END;
3133 END IF;
3134
3135 -- Initialize API RETURN status to success
3136 x_return_status := FND_API.G_RET_STS_SUCCESS;
3137
3138 EXCEPTION
3139
3140 WHEN FND_API.G_EXC_ERROR THEN
3141 x_return_status := FND_API.G_RET_STS_ERROR;
3142
3143 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3144 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3145
3146 WHEN OTHERS THEN
3147 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3148
3149 END Create_Budget_Journal_Fund;
3150
3151 /* ----------------------------------------------------------------------- */
3152
3153 PROCEDURE Validate_Funding_Account
3154 (x_return_status OUT NOCOPY VARCHAR2,
3155 p_event_type VARCHAR2,
3156 p_source_id NUMBER,
3157 p_stage_sequence NUMBER := NULL,
3158 p_budget_year_id NUMBER := NULL,
3159 p_gl_budget_set_id NUMBER,
3160 p_start_date DATE,
3161 x_validation_status IN OUT NOCOPY VARCHAR2
3162 )
3163 IS
3164
3165 TYPE FBCurType IS REF CURSOR;
3166 fb_cv FBCurType;
3167
3168 l_cv_ccid NUMBER;
3169 l_return_status VARCHAR2(1);
3170
3171 l_gl_budget_set_name VARCHAR2(80);
3172 l_concat_segments VARCHAR2(2000);
3173 l_curr_string VARCHAR2(100);
3174
3175 CURSOR c_budgetset
3176 IS
3177 SELECT gl_budget_set_name
3178 FROM PSB_GL_BUDGET_SETS
3179 WHERE gl_budget_set_id = p_gl_budget_set_id;
3180
3181 CURSOR c_revision
3182 IS
3183 SELECT permanent_revision
3184 FROM PSB_BUDGET_REVISIONS
3185 WHERE budget_revision_id = p_source_id;
3186
3187 BEGIN
3188 /* Bug 3029168 Start */
3189 -- setting the dynamic cursor based on event type
3190 IF p_event_type = 'BP' THEN
3191 l_curr_string := 'and a.currency_code <> ''STAT'' ';
3192 ELSIF p_event_type = 'SW' THEN
3193 l_curr_string := 'and a.currency_code = ''STAT'' ';
3194 ELSIF p_event_type = 'BR' THEN
3195 l_curr_string := 'and bra.currency_code <> ''STAT'' ';
3196 ELSIF p_event_type = 'SR' THEN
3197 l_curr_string := 'and bra.currency_code = ''STAT'' ';
3198 END IF;
3199 /* Bug 3029168 End */
3200
3201 FOR c_budgetset_rec IN c_budgetset LOOP
3202 l_gl_budget_set_name := c_budgetset_rec.gl_budget_set_name;
3203 END LOOP;
3204
3205 IF p_event_type = 'BP' OR p_event_type = 'SW' THEN -- Bug 3029168
3206 BEGIN
3207
3208 OPEN fb_cv FOR
3209 'SELECT a.code_combination_id ' ||
3210 'FROM PSB_WS_ACCOUNT_LINES a, PSB_WS_LINES b ' ||
3211 'WHERE b.worksheet_id = :source_id ' ||
3212 l_curr_string|| -- Bug 3029168
3213 'AND b.account_line_id = a.account_line_id ' ||
3214 'AND a.budget_year_id = :budget_year_id ' ||
3215 'AND a.balance_type = ''E'' ' ||
3216 'AND a.template_id IS NULL ' ||
3217 'AND :stage_sequence BETWEEN a.start_stage_seq AND a.current_stage_seq ' ||
3218 /* Bug No 1357416 Start */
3219 --- 'minus ' ||
3220 'AND NOT exists (' ||
3221 /* Bug No 1357416 END */
3222 'SELECT v.code_combination_id ' ||
3223 'FROM PSB_BUDGET_ACCOUNTS v, PSB_SET_RELATIONS vs, PSB_GL_BUDGETS vgb ' ||
3224 'WHERE :start_date BETWEEN vgb.start_date AND vgb.end_date ' ||
3225 'AND vgb.gl_budget_set_id = :gl_budget_set_id ' ||
3226 'AND vgb.gl_budget_id = vs.gl_budget_id ' ||
3227 'AND vs.account_position_set_id = v.account_position_set_id ' ||
3228 /* Bug No 1357416 Start */
3229 'AND v.code_combination_id = a.code_combination_id)'
3230 /* Bug No 1357416 END */
3231 USING p_source_id, p_budget_year_id, p_stage_sequence, p_start_date, p_gl_budget_set_id;
3232 LOOP
3233 FETCH fb_cv INTO l_cv_ccid;
3234 EXIT WHEN fb_cv%NOTFOUND;
3235
3236 l_concat_segments := FND_FLEX_EXT.Get_Segs
3237 (application_short_name => 'SQLGL',
3238 key_flex_code => 'GL#',
3239 structure_number => g_chart_of_accounts_id,
3240 combination_id => l_cv_ccid
3241 );
3242 Message_Token('CCID', l_concat_segments);
3243 Message_Token('BUDGETSET', l_gl_budget_set_name);
3244 Add_Message('PSB', 'PSB_CCID_NO_FUND_INFO');
3245 x_validation_status := FND_API.G_RET_STS_ERROR;
3246 END LOOP;
3247 CLOSE fb_cv;
3248 END;
3249 ELSIF p_event_type = 'BR' OR p_event_type = 'SR' THEN -- Bug 3029168
3250 BEGIN
3251 OPEN fb_cv FOR
3252 'SELECT bra.code_combination_id ' ||
3253 'FROM psb_budget_revision_accounts bra, psb_budget_revision_lines brl ' ||
3254 'WHERE brl.budget_revision_id = :source_id ' ||
3255 l_curr_string|| -- Bug 3029168
3256 'AND bra.budget_revision_acct_line_id = brl.budget_revision_acct_line_id ' ||
3257 /* Bug No 1357416 Start */
3258 --- 'minus ' ||
3259 'AND NOT exists (' ||
3260 /* Bug No 1357416 END */
3261 'SELECT v.code_combination_id ' ||
3262 'FROM PSB_BUDGET_ACCOUNTS v, PSB_SET_RELATIONS vs, PSB_GL_BUDGETS vgb ' ||
3263 'WHERE :start_date BETWEEN vgb.start_date AND vgb.end_date ' ||
3264 'AND vgb.gl_budget_set_id = :gl_budget_set_id ' ||
3265 'AND vgb.gl_budget_id = vs.gl_budget_id ' ||
3266 'AND vs.account_position_set_id = v.account_position_set_id ' ||
3267 'AND NVL(dual_posting_type, ''P'') = DECODE(:permanent_revision, ''Y'', NVL(dual_posting_type, ''P''), ''A'') ' ||
3268 /* Bug No 1357416 Start */
3269 'AND v.code_combination_id = bra.code_combination_id)'
3270 /* Bug No 1357416 END */
3271 USING p_source_id, p_start_date, p_gl_budget_set_id, g_permanent_revision;
3272 LOOP
3273 FETCH fb_cv INTO l_cv_ccid;
3274 EXIT WHEN fb_cv%NOTFOUND;
3275 l_concat_segments := FND_FLEX_EXT.Get_Segs
3276 (application_short_name => 'SQLGL',
3277 key_flex_code => 'GL#',
3278 structure_number => g_chart_of_accounts_id,
3279 combination_id => l_cv_ccid
3280 );
3281
3282
3283 Message_Token('CCID', l_concat_segments);
3284 Message_Token('BUDGETSET', l_gl_budget_set_name);
3285 Add_Message('PSB', 'PSB_CCID_NO_FUND_INFO');
3286 x_validation_status := FND_API.G_RET_STS_ERROR;
3287 END LOOP;
3288 CLOSE fb_cv;
3289 END;
3290 END IF;
3291
3292 EXCEPTION
3293
3294 WHEN FND_API.G_EXC_ERROR THEN
3295 x_return_status := FND_API.G_RET_STS_ERROR;
3296
3297 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3298 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3299
3300 WHEN OTHERS THEN
3301 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3302
3303 END Validate_Funding_Account;
3304
3305 /* ----------------------------------------------------------------------- */
3306 PROCEDURE Insert_BR_Lines_In_PSB_I_Fund
3307 (p_api_version IN NUMBER,
3308 p_init_msg_list IN VARCHAR2,
3309 p_commit IN VARCHAR2,
3310 p_validation_level IN NUMBER,
3311 x_return_status OUT NOCOPY VARCHAR2,
3312 x_msg_count OUT NOCOPY NUMBER,
3313 x_msg_data OUT NOCOPY VARCHAR2,
3314 p_budget_revision_id IN NUMBER,
3315 p_je_source IN VARCHAR2,
3316 p_je_category IN VARCHAR2,
3317 p_auto_offset IN VARCHAR2,
3318 p_gl_budget_set_id IN NUMBER,
3319 p_event_type IN VARCHAR2,
3320 x_validation_status OUT NOCOPY VARCHAR2
3321 )
3322 IS
3323
3324 l_api_name CONSTANT VARCHAR2(30) := 'Insert_BR_Lines_In_PSB_I_Fund';
3325 l_api_version CONSTANT NUMBER := 1.0;
3326
3327 l_batch_name VARCHAR2(100);
3328 l_batch_description VARCHAR2(100);
3329 l_je_name VARCHAR2(100);
3330 l_je_description VARCHAR2(100);
3331 l_created_by NUMBER;
3332 --
3333 l_funding_status VARCHAR2(1);
3334 l_validation_status VARCHAR2(1);
3335
3336 l_budget_version_id NUMBER := NULL;
3337 l_code_combination_id NUMBER;
3338 l_dr_amount NUMBER;
3339 l_cr_amount NUMBER;
3340 l_msg_count NUMBER;
3341 l_msg_data VARCHAR2(2000);
3342 l_concat_segments VARCHAR2(2000);
3343 l_budget_version_flag VARCHAR2(1);
3344 l_permanent_revision VARCHAR2(1);
3345
3346 l_prev_gl_period_name VARCHAR2(15) := 'INITIALLY NULL';
3347 l_gl_period_name VARCHAR2(15);
3348 l_gl_year NUMBER;
3349 l_gl_period_num NUMBER;
3350 l_gl_quarter_num NUMBER;
3351
3352 l_period_changed VARCHAR2(1);
3353 l_validation_start_date DATE;
3354
3355 l_accounting_date DATE;
3356 l_period_end_date DATE;
3357 l_event_number NUMBER;
3358 l_period_name VARCHAR2(15);
3359 l_period_status VARCHAR2(1);
3360
3361 l_return_status VARCHAR2(1);
3362
3363 l_period_indx NUMBER :=0;
3364 l_tbl_indx NUMBER :=0;
3365
3366 -- Bug#4310411 Start
3367 -- Record types those will hold the PSB_GL_INTERFACES
3368 -- table's columns.
3369 rec_budget_version_id Number_Tbl_Type;
3370 rec_accounting_date Date_Tbl_Type;
3371 rec_period_name Char_Tbl_Type;
3372 rec_period_year Char_Tbl_Type;
3373 rec_period_num Char_Tbl_Type;
3374 rec_quarter_num Char_Tbl_Type;
3375 rec_code_combination_id Number_Tbl_Type;
3376 rec_entered_dr Char_Tbl_Type;
3377 rec_entered_cr Char_Tbl_Type;
3378 rec_amount Char_Tbl_Type;
3379 rec_budget_version_flag Char_Tbl_Type;
3380 -- Bug#4310411 End
3381
3382 CURSOR c_budrev_accts
3383 IS
3384 SELECT bra.gl_period_name,
3385 gps.start_date, ---1
3386 pgb.start_date budget_set_start_date, --2
3387 gps.period_name,
3388 gps.effective_period_num,
3389 gps.end_date,
3390 gps.closing_status,
3391 gps.period_year,
3392 gps.period_num,
3393 gps.quarter_num,
3394 bra.code_combination_id,
3395 bra.gl_budget_version_id,
3396 DECODE(bra.account_type,'L', NULL, 'O', NULL, 'R', NULL,
3397 DECODE(bra.revision_type,'I',bra.revision_amount * 1.0,'D',
3398 bra.revision_amount * -1.0
3399 )
3400 ) dr_amount,
3401 DECODE(bra.account_type, 'A' , NULL, 'E' , NULL,
3402 DECODE(bra.revision_type,'I',bra.revision_amount * 1.0,'D',
3403 bra.revision_amount * -1.0
3404 )
3405 ) cr_amount,
3406 budget_balance x_amount
3407 FROM psb_budget_revisions br,
3408 psb_budget_revision_accounts bra,
3409 psb_budget_revision_lines brl,
3410 gl_period_statuses gps,
3411 psb_gl_budgets pgb
3412 WHERE br.budget_revision_id = p_budget_revision_id
3413 AND brl.budget_revision_id = p_budget_revision_id
3414 AND br.budget_revision_type = 'R'
3415 -- Bug 3029168 added the following OR condition
3416 AND ((bra.currency_code <> 'STAT' AND p_event_type = 'BR') OR
3417 (bra.currency_code = 'STAT' AND p_event_type = 'SR'))
3418 AND brl.budget_revision_acct_line_id = bra.budget_revision_acct_line_id
3419 AND gps.period_name = bra.gl_period_name
3420 AND gps.application_id = 101
3421 AND gps.adjustment_period_flag='N'
3422 AND ((gps.start_date BETWEEN pgb.start_date AND pgb.end_date)
3423 OR
3424 (gps.end_date BETWEEN pgb.start_date AND pgb.end_date)
3425 )
3426 AND gps.set_of_books_id = g_set_of_books_id
3427 AND pgb.gl_budget_set_id = p_gl_budget_set_id
3428 ORDER BY bra.gl_period_name, bra.code_combination_id, gps.period_num;
3429
3430 l_rec_period_name Char_Tbl_Type;
3431
3432 BEGIN
3433
3434 SAVEPOINT Insert_BR_Lines_In_PSB_I_Fund;
3435
3436 l_created_by := fnd_global.user_id;
3437
3438 FND_MESSAGE.SET_NAME('PSB', 'PSB_GL_BJR_BATCH_NAME');
3439 FND_MESSAGE.SET_TOKEN('BUDGET_REVISION_ID', p_budget_revision_id);
3440 l_batch_name := FND_MESSAGE.Get;
3441
3442 FND_MESSAGE.SET_NAME('PSB', 'PSB_GL_BJR_BATCH_DESC');
3443 FND_MESSAGE.SET_TOKEN('BUDGET_REVISION_ID', p_budget_revision_id);
3444 l_batch_description := FND_MESSAGE.Get;
3445
3446 FND_MESSAGE.SET_NAME('PSB', 'PSB_GL_BJR_JE_NAME');
3447 FND_MESSAGE.SET_TOKEN('BUDGET_REVISION_ID', p_budget_revision_id);
3448 l_je_name := FND_MESSAGE.Get;
3449
3450 FND_MESSAGE.SET_NAME('PSB', 'PSB_GL_BJR_JE_DESC');
3451 FND_MESSAGE.SET_TOKEN('BUDGET_REVISION_ID', p_budget_revision_id);
3452 l_je_description := FND_MESSAGE.Get;
3453
3454 -- initialize global which determines IF revision IS permanent OR temporary
3455
3456 FOR c_budrev_accts_rec IN c_budrev_accts LOOP
3457 -- Bug#4310411 Start
3458 -- Get all the different gl_period names.
3459 l_gl_period_name := c_budrev_accts_rec.gl_period_name;
3460
3461 IF l_gl_period_name <> l_prev_gl_period_name THEN
3462 l_period_indx := l_period_indx + 1;
3463 l_rec_period_name(l_period_indx) := l_gl_period_name;
3464 l_prev_gl_period_name := l_gl_period_name;
3465 END IF;
3466 -- Bug#4310411 End
3467
3468 -- FOR each budget revisions acct rec, process 2 times FOR permanent revision
3469 -- (post to the 'A' version flag budget AND 'P' version flag budget)
3470 -- AND once FOR temporary revision (post only to 'A' version flag)
3471
3472 FOR i IN 1 .. 2 LOOP
3473 -- post to gl_interface FOR both permanent AND temporary revisions
3474
3475 IF i = 1 THEN
3476 l_budget_version_flag := 'A';
3477 ELSE
3478 l_budget_version_flag := 'P';
3479 END IF;
3480
3481 l_budget_version_id := c_budrev_accts_rec.gl_budget_version_id;
3482
3483 IF i = 2 THEN
3484 -- post to gl_interface FOR only permanent revision
3485 IF g_permanent_revision = 'N' THEN
3486 EXIT;
3487 -- skip posting since this IS NOT a permanent revision
3488 END IF;
3489
3490 -- get corresponding budget version id FOR 'permanent' budget
3491 -- AND get out IF with error
3492 -- use actual ccid
3493
3494 l_validation_start_date
3495 := GREATEST(c_budrev_accts_rec.start_date, c_budrev_accts_rec.budget_set_start_date);
3496
3497 x_validation_status := FND_API.G_RET_STS_SUCCESS;
3498
3499 Validate_Funding_Account
3500 (x_return_status => l_return_status,
3501 p_event_type => p_event_type, -- Bug 3029168
3502 p_source_id => p_budget_revision_id,
3503 p_gl_budget_set_id => p_gl_budget_set_id,
3504 p_start_date => l_validation_start_date,
3505 x_validation_status => l_validation_status
3506 );
3507
3508 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3509 RAISE FND_API.G_EXC_ERROR;
3510 END IF;
3511
3512 IF l_validation_status <> FND_API.G_RET_STS_SUCCESS THEN
3513 x_validation_status := FND_API.G_RET_STS_ERROR;
3514 END IF;
3515 END IF;
3516
3517 --
3518 -- THEN INSERT INTO psb_gl_interfaces
3519 --
3520
3521 l_tbl_indx := l_tbl_indx + 1;
3522
3523 rec_budget_version_id(l_tbl_indx) := l_budget_version_id;
3524 rec_accounting_date(l_tbl_indx) := c_budrev_accts_rec.start_date;
3525 rec_period_name(l_tbl_indx) := c_budrev_accts_rec.gl_period_name;
3526 rec_period_year(l_tbl_indx) := c_budrev_accts_rec.period_year;
3527 rec_period_num(l_tbl_indx) := c_budrev_accts_rec.period_num;
3528 rec_quarter_num(l_tbl_indx) := c_budrev_accts_rec.quarter_num;
3529 rec_code_combination_id(l_tbl_indx) := c_budrev_accts_rec.code_combination_id;
3530 rec_entered_dr(l_tbl_indx) := c_budrev_accts_rec.dr_amount;
3531 rec_entered_cr(l_tbl_indx) := c_budrev_accts_rec.cr_amount;
3532 rec_amount(l_tbl_indx) := c_budrev_accts_rec.x_amount;
3533 rec_budget_version_flag(l_tbl_indx) := l_budget_version_flag;
3534 END LOOP; -- END of 2 loops FOR permanent AND temp revisions FOR each record
3535 END LOOP; -- END of budget revision accounts rec processing
3536
3537 -- Populate the last period also
3538 -- IN the PL/SQL table as no period
3539 -- change occured.
3540 l_period_indx := l_period_indx + 1;
3541 l_rec_period_name(l_period_indx) := l_gl_period_name;
3542
3543 --++ Now Bulk Insert the Data INTO PSB_GL_INTERFACES.
3544 IF rec_budget_version_id.COUNT > 0 THEN
3545 FORALL l_indx IN 1..rec_budget_version_id.COUNT
3546 INSERT INTO psb_gl_interfaces
3547 (worksheet_id,
3548 group_id,
3549 status,
3550 set_of_books_id,
3551 user_je_source_name,
3552 user_je_category_name,
3553 currency_code,
3554 date_created,
3555 created_by,
3556 actual_flag,
3557 budget_version_id,
3558 accounting_date,
3559 period_name,
3560 period_year,
3561 period_num,
3562 quarter_num,
3563 code_combination_id,
3564 entered_dr,
3565 entered_cr,
3566 reference1,
3567 reference2,
3568 reference4,
3569 reference5,
3570 budget_stage_id,
3571 budget_year_id,
3572 je_type,
3573 amount,
3574 budget_source_type,
3575 budget_version_flag,
3576 balancing_entry_flag
3577 )
3578 VALUES
3579 (
3580 p_budget_revision_id,
3581 p_budget_revision_id,
3582 /* For bug 4654145 --> Changed the status to POSTED, as there is no trial mode for budget revision */
3583 'Posted',
3584 g_set_of_books_id,
3585 p_je_source,
3586 p_je_category,
3587 g_currency_code,
3588 SYSDATE,
3589 l_created_by,
3590 'B',
3591 rec_budget_version_id(l_indx),
3592 rec_accounting_date(l_indx),
3593 rec_period_name(l_indx),
3594 rec_period_year(l_indx),
3595 rec_period_num(l_indx),
3596 rec_quarter_num(l_indx),
3597 rec_code_combination_id(l_indx),
3598 rec_entered_dr(l_indx),
3599 rec_entered_cr(l_indx),
3600 g_je_name,
3601 g_je_description,
3602 NULL,
3603 NULL,
3604 NULL,
3605 NULL,
3606 NULL,
3607 rec_amount(l_indx),
3608 p_event_type,
3609 rec_budget_version_flag(l_indx),
3610 'N'
3611 );
3612 END IF;
3613
3614 --++ Now balance the Journal.
3615 IF p_auto_offset = 'Y' THEN
3616 BEGIN
3617 FOR l_indx IN 1..l_rec_period_name.COUNT LOOP
3618 Balance_Journal
3619 (x_return_status => l_return_status ,
3620 p_worksheet_id => p_budget_revision_id,
3621 p_period_name => l_rec_period_name(l_indx),
3622 p_gl_budget_set_id => NULL
3623 );
3624
3625 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3626 RAISE FND_API.G_EXC_ERROR;
3627 END IF;
3628 END LOOP;
3629 END;
3630 END IF;--END of check FOR auto offset value
3631
3632 x_return_status := FND_API.G_RET_STS_SUCCESS;
3633 --
3634
3635 EXCEPTION
3636 WHEN FND_API.G_EXC_ERROR THEN
3637 ROLLBACK TO Insert_BR_Lines_In_PSB_I_Fund;
3638 x_return_status := FND_API.G_RET_STS_ERROR;
3639
3640 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3641 ROLLBACK TO Insert_BR_Lines_In_PSB_I_Fund;
3642 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3643
3644 WHEN OTHERS THEN
3645 ROLLBACK TO Insert_BR_Lines_In_PSB_I_Fund;
3646 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3647 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3648 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
3649 END IF;
3650
3651 END Insert_BR_Lines_In_PSB_I_Fund;
3652 /* ----------------------------------------------------------------------- */
3653
3654 -- commenting out the savepoints FOR XLA transfer since the XLA transfer
3655 -- program commits within the process AND this erases the savepoints established
3656
3657 PROCEDURE Create_Revision_Journal
3658 (p_api_version IN NUMBER,
3659 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3660 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3661 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3662 p_return_status OUT NOCOPY VARCHAR2,
3663 p_msg_count OUT NOCOPY NUMBER,
3664 p_msg_data OUT NOCOPY VARCHAR2,
3665 p_budget_revision_id IN NUMBER,
3666 p_order_by1 IN VARCHAR2,
3667 p_order_by2 IN VARCHAR2,
3668 p_order_by3 IN VARCHAR2,
3669 p_error_code OUT NOCOPY VARCHAR2 -- bug# 4341619
3670 )
3671 IS
3672
3673 l_api_name CONSTANT VARCHAR2(30) := 'Create_Revision_Journal';
3674 l_api_version CONSTANT NUMBER := 1.0;
3675
3676 TYPE RevCurType IS REF CURSOR;
3677 rev_cv RevCurType;
3678
3679 l_cv_period_name VARCHAR2(15);
3680 l_cv_start_date DATE;
3681 l_cv_effective_period_num NUMBER;
3682 l_cv_budgetset_start_date DATE;
3683
3684 l_auto_offset VARCHAR2(1);
3685
3686 l_je_source VARCHAR2(25);
3687 l_je_category VARCHAR2(25);
3688
3689 l_req_id NUMBER;
3690 l_funding_status VARCHAR2(1);
3691 l_validation_status VARCHAR2(1);
3692 l_acct_overlap_status VARCHAR2(1);
3693 l_validation_start_date DATE;
3694
3695 l_column NUMBER;
3696
3697 l_cbc_document BOOLEAN := FALSE;
3698
3699 l_set_of_books_id NUMBER;
3700 l_gl_budget_set_id NUMBER;
3701
3702 l_return_status VARCHAR2(1);
3703 l_msg_count NUMBER;
3704 l_msg_data VARCHAR2(2000);
3705 /*FOR Bug No : 2920702 Start*/
3706 l_year_start_date DATE;
3707 l_year_end_date DATE;
3708 l_event_type VARCHAR2(5);
3709 l_budget_group_id NUMBER;
3710 l_currency_code VARCHAR2(15);
3711
3712
3713
3714 CURSOR c_year
3715 IS
3716 SELECT MIN(gp.start_date) start_date, MAX(gp.end_date) end_date
3717 FROM PSB_BUDGET_REVISION_ACCOUNTS ac,
3718 GL_PERIOD_STATUSES gp
3719 WHERE ac.budget_revision_acct_line_id IN
3720 (SELECT budget_revision_acct_line_id
3721 FROM psb_budget_revision_lines
3722 WHERE budget_revision_id = p_budget_revision_id
3723 )
3724 AND ac.gl_period_name = gp.period_name
3725 AND gp.application_id = 101
3726 AND gp.set_of_books_id = g_set_of_books_id ;
3727
3728 /*FOR Bug No : 2920702 END*/
3729 BEGIN
3730
3731 -- Standard call to check FOR call compatibility.
3732 IF NOT FND_API.Compatible_API_Call
3733 (l_api_version,
3734 p_api_version,
3735 l_api_name,
3736 G_PKG_NAME
3737 )
3738 THEN
3739 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3740 END IF;
3741
3742 -- Initialize message list IF p_init_msg_list IS set to TRUE.
3743 IF FND_API.to_Boolean (p_init_msg_list) THEN
3744 FND_MSG_PUB.initialize;
3745 END IF;
3746
3747 p_error_code := 'NO_ERR'; -- bug # 4341619
3748
3749 /* Bug 3029168 Start */
3750 SELECT currency_code,budget_group_id,gl_budget_set_id
3751 INTO l_currency_code,l_budget_group_id,l_gl_budget_set_id
3752 FROM psb_budget_revisions
3753 WHERE budget_revision_id = p_budget_revision_id;
3754 /* Bug 3029168 Start */
3755
3756 IF l_currency_code = 'STAT' THEN
3757 l_event_type := 'SR';
3758 ELSE
3759 l_event_type := 'BR';
3760 END IF;
3761 /* Bug 3029168 End */
3762
3763
3764 /* FOR Bug No. 2662506 : Start */
3765 l_auto_offset := FND_PROFILE.VALUE('PSB_REVISION_AUTO_OFFSET');
3766 /* FOR Bug No. 2662506 : END */
3767
3768 -- Bug#4310411 Start
3769 --Get Journal Source value FROM a profile option
3770 l_je_source := FND_PROFILE.VALUE('PSB_GL_BUDGET_JOURNAL_SOURCE');
3771
3772 IF l_je_source IS NULL THEN
3773 l_je_source := 'Budget Journal';
3774 END IF;
3775
3776 --Get Journal Category value FROM a profile option
3777 l_je_category := FND_PROFILE.VALUE('PSB_GL_BUDGET_JOURNAL_CATEGORY');
3778
3779 IF l_je_category IS NULL THEN
3780 l_je_category := 'Budget';
3781 END IF;
3782 -- Bug#4310411 End
3783
3784 Initialize
3785 (x_return_status => l_return_status,
3786 p_event_type => l_event_type,
3787 p_source_id => p_budget_revision_id,
3788 p_auto_offset => l_auto_offset
3789 );
3790
3791 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3792 RAISE FND_API.G_EXC_ERROR;
3793 END IF;
3794
3795 -- Dele the old run for the same Budget Revision
3796 -- and budget source.
3797 Delete_Old_Run
3798 (x_return_status => l_return_status,
3799 p_worksheet_id => p_budget_revision_id,
3800 p_budget_source_type => g_budget_source_type
3801 );
3802
3803 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3804 RAISE FND_API.G_EXC_ERROR;
3805 END IF;
3806
3807 -- Bug 3029168 added the second clause
3808 IF g_budget_by_position = 'Y' AND l_currency_code <> 'STAT' THEN
3809 BEGIN
3810 PSB_POSITION_CONTROL_PVT.Validate_Position_Budget
3811 (p_return_status => l_return_status,
3812 p_event_type => l_event_type,
3813 p_source_id => p_budget_revision_id
3814 );
3815
3816 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3817 RAISE FND_API.G_EXC_ERROR;
3818 END IF;
3819 END;
3820 END IF;
3821
3822 l_set_of_books_id := l_set_of_books_id;
3823
3824 PSB_GL_BUDGET_SET_PVT.Validate_Account_Overlap
3825 (p_api_version => p_api_version,
3826 p_return_status => l_return_status,
3827 p_msg_data => l_msg_data,
3828 p_msg_count => l_msg_count,
3829 p_gl_budget_set_id => l_gl_budget_set_id,
3830 p_validation_status => l_acct_overlap_status
3831 );
3832
3833 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3834 RAISE FND_API.G_EXC_ERROR;
3835 END IF;
3836
3837 IF l_acct_overlap_status <> FND_API.G_RET_STS_SUCCESS THEN
3838 /* start bug # 4341619 */
3839 p_error_code := 'ACCOUNT_OVERLAP_ERR';
3840 p_return_status := FND_API.G_RET_STS_SUCCESS;
3841 RETURN;
3842 --Add_Message('PSB', 'PSB_VAL_LINE');
3843 /* END bug # 4341619 */
3844 END IF;
3845
3846 --l_funding_status := FND_API.G_RET_STS_SUCCESS; -- bug # 4341619
3847
3848 -- continue validation regardless value of validation status
3849 /*FOR Bug No : 2920702 Start*/
3850 FOR c_year_rec IN c_year LOOP
3851 l_year_start_date := c_year_rec.start_date;
3852 l_year_end_date := c_year_rec.end_date;
3853 END LOOP;
3854
3855 Validate_GL_Budget_Year
3856 (x_return_status => l_return_status,
3857 p_gl_budget_set_id => l_gl_budget_set_id,
3858 p_year_start_date => l_year_start_date,
3859 p_year_end_date => l_year_end_date
3860 );
3861
3862 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3863 /* start bug # 4341619 */
3864 p_error_code := 'GL_BUDGET_PERIOD_NOT_OPEN_ERR';
3865 p_return_status := FND_API.G_RET_STS_SUCCESS;
3866 RETURN;
3867 --RAISE FND_API.G_EXC_ERROR;
3868 /* END bug # 4341619 */
3869 END IF;
3870
3871 /*FOR Bug No : 2920702 END*/
3872 BEGIN
3873 -- Bug#4310411 Start
3874 Insert_BR_Lines_In_PSB_I_Fund
3875 (p_api_version => p_api_version,
3876 p_init_msg_list => p_init_msg_list,
3877 p_commit => p_commit,
3878 p_validation_level => p_validation_level,
3879 x_return_status => l_return_status,
3880 x_msg_count => l_msg_count,
3881 x_msg_data => l_msg_data,
3882 p_budget_revision_id => p_budget_revision_id,
3883 p_je_source => l_je_source,
3884 p_je_category => l_je_category,
3885 p_auto_offset => l_auto_offset,
3886 p_gl_budget_set_id => l_gl_budget_set_id,
3887 p_event_type => l_event_type, -- Bug 3029168
3888 x_validation_status => l_validation_status
3889 );
3890
3891 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3892 RAISE FND_API.G_EXC_ERROR;
3893 END IF;
3894
3895 /* start bug # 4341619 */
3896 IF(l_validation_status <> FND_API.G_RET_STS_SUCCESS) THEN
3897 p_error_code := 'NO_FUNDING_BUDGET_ERR';
3898 p_return_status := FND_API.G_RET_STS_SUCCESS;
3899 RETURN;
3900 END IF;
3901 -- Bug#4310411 End
3902 END;
3903 -- END IF; -- bug # 4341619
3904
3905 IF g_revision_type = 'C' AND l_currency_code <> 'STAT' THEN -- Bug 3029168
3906 BEGIN
3907 l_cbc_document := TRUE;
3908 PSB_COMMITMENTS_PVT.Post_Commitment_Revisions
3909 (p_api_version => 1.0,
3910 p_return_status => l_return_status,
3911 p_msg_data => l_msg_data,
3912 p_msg_count => l_msg_count,
3913 p_budget_revision_id => p_budget_revision_id
3914 );
3915
3916 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3917 Message_Token('BUDGET_REVISION', p_budget_revision_id);
3918 Add_Message('PSB', 'PSB_CANNOT_POST_COMMITMENT_REV');
3919 RAISE FND_API.G_EXC_ERROR;
3920 END IF;
3921 END;
3922 ELSE
3923 BEGIN
3924 -- Bug#4310411 Start
3925 --Final INSERT INTO GL
3926 Insert_Lines_To_GL
3927 (x_return_status => l_return_status,
3928 p_source_id => p_budget_revision_id,
3929 p_called_from => 'R',
3930 p_event_type => l_event_type -- Bug 3029168
3931 );
3932 -- Bug#4310411 End
3933
3934 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3935 RAISE FND_API.G_EXC_ERROR;
3936 END IF;
3937 END;
3938 END IF;
3939
3940 -- Standard check of p_commit.
3941 IF FND_API.to_Boolean (p_commit) THEN
3942 COMMIT WORK;
3943 END IF;
3944
3945 -- Initialize API RETURN status to success
3946
3947 p_return_status := FND_API.G_RET_STS_SUCCESS;
3948 /* Start Bug No. 2322856 */
3949 -- PSB_MESSAGE_S.Print_Success;
3950 /* END Bug No. 2322856 */
3951
3952 EXCEPTION
3953
3954 WHEN FND_API.G_EXC_ERROR THEN
3955 p_return_status := FND_API.G_RET_STS_ERROR;
3956 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
3957 p_data => p_msg_data);
3958 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
3959 p_print_header => FND_API.G_TRUE ) ;
3960
3961 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3962 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3963 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
3964 p_data => p_msg_data);
3965 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
3966 p_print_header => FND_API.G_TRUE ) ;
3967
3968 WHEN OTHERS THEN
3969 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3970 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
3971 p_data => p_msg_data);
3972 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3973 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
3974 l_api_name);
3975 END IF;
3976 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
3977 p_print_header => FND_API.G_TRUE ) ;
3978
3979 END Create_Revision_Journal;
3980
3981 /* ----------------------------------------------------------------------- */
3982
3983 -- Add Token AND Value to the Message Token array
3984
3985 PROCEDURE Message_Token(tokname IN VARCHAR2,
3986 tokval IN VARCHAR2) IS
3987
3988 BEGIN
3989
3990 IF no_msg_tokens IS NULL THEN
3991 no_msg_tokens := 1;
3992 ELSE
3993 no_msg_tokens := no_msg_tokens + 1;
3994 END IF;
3995
3996 msg_tok_names(no_msg_tokens) := tokname;
3997 msg_tok_val(no_msg_tokens) := tokval;
3998
3999 END Message_Token;
4000
4001 /* ----------------------------------------------------------------------- */
4002
4003 -- Define a Message Token with a Value AND set the Message Name
4004
4005 -- Calls FND_MESSAGE server package to set the Message Stack. This message IS
4006 -- retrieved by the calling program.
4007
4008 PROCEDURE Add_Message(appname IN VARCHAR2,
4009 msgname IN VARCHAR2) IS
4010
4011 i BINARY_INTEGER;
4012
4013 BEGIN
4014
4015 IF ((appname IS NOT NULL) AND
4016 (msgname IS NOT NULL)) THEN
4017
4018 FND_MESSAGE.SET_NAME(appname, msgname);
4019
4020 IF no_msg_tokens IS NOT NULL THEN
4021 FOR i IN 1..no_msg_tokens LOOP
4022 FND_MESSAGE.SET_TOKEN(msg_tok_names(i), msg_tok_val(i));
4023 END LOOP;
4024 END IF;
4025
4026 FND_MSG_PUB.Add;
4027
4028 END IF;
4029
4030 -- Clear Message Token stack
4031
4032 no_msg_tokens := 0;
4033
4034 END Add_Message;
4035 /*---------------------------------------------------------------------------*/
4036
4037
4038 /*===========================================================================+
4039 | PROCEDURE Transfer_GLI_TO_GL_CP |
4040 +===========================================================================*/
4041 --
4042 -- This IS the execution file FOR the concurrent program Transfer GLI to GL
4043 -- through Standard Report Submissions.
4044 --
4045 PROCEDURE Transfer_GLI_To_GL_CP
4046 (errbuf OUT NOCOPY VARCHAR2,
4047 retcode OUT NOCOPY VARCHAR2,
4048 p_source_id IN NUMBER,
4049 p_currency_code VARCHAR2 DEFAULT 'C', -- Bug 3029168
4050 p_gl_transfer_mode IN VARCHAR2 := NULL,
4051 p_order_by1 IN VARCHAR2,
4052 p_order_by2 IN VARCHAR2,
4053 p_order_by3 IN VARCHAR2
4054 )
4055 IS
4056
4057 l_api_name CONSTANT VARCHAR2(30) := 'Transfer_GLI_To_GL_CP';
4058 l_api_version CONSTANT NUMBER := 1.0 ;
4059
4060 l_return_status VARCHAR2(1);
4061 l_msg_count NUMBER;
4062 l_msg_data VARCHAR2(2000);
4063 l_event_type VARCHAR2(5);
4064
4065 BEGIN
4066 /* Bug 3029168 Start */
4067 IF p_currency_code = 'C' THEN
4068 l_event_type := 'BP';
4069 ELSE
4070 l_event_type := 'SW';
4071 END IF;
4072 /* Bug 3029168 End */
4073 Transfer_GLI_To_GL
4074 (p_return_status => l_return_status,
4075 p_msg_count => l_msg_count,
4076 p_msg_data => l_msg_data,
4077 p_init_msg_list => FND_API.G_TRUE,
4078 p_event_type => l_event_type, -- Bug 3029168
4079 p_source_id => p_source_id,
4080 p_gl_transfer_mode => p_gl_transfer_mode,
4081 p_order_by1 => p_order_by1,
4082 p_order_by2 => p_order_by2,
4083 p_order_by3 => p_order_by3
4084 );
4085
4086 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4087 RAISE FND_API.G_EXC_ERROR;
4088 END IF;
4089 --
4090 retcode := 0 ;
4091 --COMMIT WORK; Bug#4310411
4092 --
4093 EXCEPTION
4094
4095 WHEN FND_API.G_EXC_ERROR THEN
4096 PSB_MESSAGE_S.Print_Error(p_mode => FND_FILE.LOG ,
4097 p_print_header => FND_API.G_TRUE
4098 );
4099 retcode := 2 ;
4100
4101 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4102 PSB_MESSAGE_S.Print_Error(p_mode => FND_FILE.LOG ,
4103 p_print_header => FND_API.G_TRUE
4104 );
4105 retcode := 2 ;
4106
4107 WHEN OTHERS THEN
4108 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
4109 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,
4110 l_api_name
4111 );
4112 END IF ;
4113 PSB_MESSAGE_S.Print_Error(p_mode => FND_FILE.LOG ,
4114 p_print_header => FND_API.G_TRUE
4115 );
4116 retcode := 2 ;
4117
4118 END Transfer_GLI_To_GL_CP;
4119 /*---------------------------------------------------------------------------*/
4120
4121
4122 /*===========================================================================+
4123 | PROCEDURE Create_Budget_Journal_CP |
4124 +===========================================================================*/
4125 --
4126 -- This IS the execution file FOR the concurrent program Create Budget Journal
4127 -- through Standard Report Submissions.
4128 --
4129 PROCEDURE Create_Budget_Journal_CP
4130 (errbuf OUT NOCOPY VARCHAR2,
4131 retcode OUT NOCOPY VARCHAR2,
4132 p_worksheet_id IN NUMBER,
4133 p_budget_stage_id IN NUMBER,
4134 p_budget_year_id IN NUMBER,
4135 p_year_journal IN VARCHAR2,
4136 p_gl_transfer_mode IN VARCHAR2,
4137 p_currency_code IN VARCHAR2 DEFAULT 'C', -- Bug 3029168
4138 p_auto_offset IN VARCHAR2,
4139 p_gl_budget_set_id IN NUMBER,
4140 p_run_mode IN VARCHAR2,
4141 p_order_by1 IN VARCHAR2,
4142 p_order_by2 IN VARCHAR2,
4143 p_order_by3 IN VARCHAR2
4144 )
4145 IS
4146 --
4147 l_api_name CONSTANT VARCHAR2(30) := 'Create_Budget_Journal_CP';
4148 l_api_version CONSTANT NUMBER := 1.0 ;
4149 --
4150 l_error_api_name VARCHAR2(2000);
4151 l_return_status VARCHAR2(1) ;
4152 l_msg_count NUMBER ;
4153 l_msg_data VARCHAR2(2000) ;
4154 l_msg_index_out NUMBER;
4155 --
4156 BEGIN
4157 --
4158 Create_Budget_Journal_Fund
4159 (x_return_status => l_return_status,
4160 p_worksheet_id => p_worksheet_id,
4161 p_budget_stage_id => p_budget_stage_id,
4162 p_budget_year_id => p_budget_year_id,
4163 p_year_journal => p_year_journal,
4164 p_gl_transfer_mode => p_gl_transfer_mode,
4165 p_auto_offset => p_auto_offset,
4166 p_gl_budget_set_id => p_gl_budget_set_id,
4167 p_run_mode => p_run_mode,
4168 p_order_by1 => p_order_by1,
4169 p_order_by2 => p_order_by2,
4170 p_order_by3 => p_order_by3,
4171 p_currency_code => p_currency_code -- Bug 3029168
4172 );
4173 --
4174 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4175 RAISE FND_API.G_EXC_ERROR;
4176 END IF;
4177 --
4178
4179 --
4180 -- Check whether the API performed the validation successfully. Otherwise,
4181 -- we will fail the concurrent program so that the user can fix it.
4182 --
4183 IF NVL(l_msg_count, 0) > 0 THEN
4184
4185 -- Print error on the OUTPUT file.
4186 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.OUTPUT ,
4187 p_print_header => FND_API.G_TRUE ) ;
4188 --
4189 retcode := 2 ;
4190 --
4191 ELSE
4192 retcode := 0 ;
4193 END IF;
4194 --
4195 --COMMIT WORK; -- Bug#4310411
4196 --
4197 EXCEPTION
4198
4199 WHEN FND_API.G_EXC_ERROR THEN
4200 PSB_MESSAGE_S.Print_Error(p_mode => FND_FILE.LOG ,
4201 p_print_header => FND_API.G_TRUE
4202 );
4203 retcode := 2 ;
4204
4205 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4206 PSB_MESSAGE_S.Print_Error(p_mode => FND_FILE.LOG ,
4207 p_print_header => FND_API.G_TRUE
4208 );
4209 retcode := 2 ;
4210 WHEN OTHERS THEN
4211 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
4212 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
4213 END IF ;
4214 PSB_MESSAGE_S.Print_Error(p_mode => FND_FILE.LOG ,
4215 p_print_header => FND_API.G_TRUE
4216 );
4217 retcode := 2 ;
4218
4219 END Create_Budget_Journal_CP;
4220 /*---------------------------------------------------------------------------*/
4221
4222
4223 /*===========================================================================+
4224 | PROCEDURE Create Adopted Budget - post upgrade |
4225 +===========================================================================*/
4226
4227 PROCEDURE Create_Adopted_Budget
4228 (x_return_status OUT NOCOPY VARCHAR2,
4229 p_worksheet_id IN NUMBER,
4230 p_budget_stage_id IN NUMBER,
4231 p_budget_year_id IN NUMBER,
4232 p_year_journal IN VARCHAR2,
4233 p_gl_transfer_mode IN VARCHAR2,
4234 p_auto_offset IN VARCHAR2,
4235 p_gl_budget_set_id IN NUMBER,
4236 p_order_by1 IN VARCHAR2,
4237 p_order_by2 IN VARCHAR2,
4238 p_order_by3 IN VARCHAR2
4239 )
4240 IS
4241
4242 l_year_start_date DATE;
4243 l_year_end_date DATE;
4244 l_budget_year_type_id NUMBER;
4245
4246 l_gl_year NUMBER;
4247 l_gl_period_num NUMBER;
4248 l_gl_quarter_num NUMBER;
4249
4250 l_stage_sequence NUMBER;
4251
4252 l_period_name VARCHAR2(15);
4253 l_period_start_date DATE;
4254 l_period_end_date DATE;
4255 /*FOR Bug No : 2543724 Start*/
4256 --commented the following two variables as they are NOT being used
4257 --AND also removed them FROM passing INTO Get_GL_Period procedure
4258 --l_next_period VARCHAR2(15);
4259 --l_reversal_date DATE;
4260 /*FOR Bug No : 2543724 END*/
4261
4262 l_column NUMBER;
4263
4264 l_req_id NUMBER;
4265 l_funding_status VARCHAR2(1);
4266 l_validation_status VARCHAR2(1);
4267 l_acct_overlap_status VARCHAR2(1);
4268
4269 l_event_number NUMBER;
4270 l_accounting_date DATE;
4271 l_period_status VARCHAR2(1);
4272
4273 l_return_status VARCHAR2(1);
4274 l_msg_count NUMBER;
4275 l_msg_data VARCHAR2(2000);
4276
4277 l_include_cbc_commit_balance VARCHAR2(1);
4278 l_include_cbc_oblig_balance VARCHAR2(1);
4279 l_include_cbc_budget_balance VARCHAR2(1);
4280
4281 l_gl_budget_set_id NUMBER;
4282
4283 CURSOR c_year
4284 IS
4285 SELECT start_date,
4286 end_date,
4287 budget_year_type_id
4288 FROM psb_budget_periods
4289 WHERE budget_period_id = p_budget_year_id;
4290
4291 CURSOR c_stage
4292 IS
4293 SELECT sequence_number
4294 FROM psb_budget_stages
4295 WHERE budget_stage_id = p_budget_stage_id;
4296
4297 CURSOR c_period
4298 IS
4299 SELECT budget_period_id,
4300 start_date,
4301 end_date
4302 FROM psb_budget_periods
4303 WHERE budget_period_type = 'P'
4304 AND parent_budget_period_id = p_budget_year_id
4305 ORDER BY start_date;
4306
4307 CURSOR c_ws
4308 IS
4309 SELECT include_cbc_commit_balance,
4310 include_cbc_oblig_balance,
4311 include_cbc_budget_balance
4312 FROM psb_worksheets
4313 WHERE worksheet_id = p_worksheet_id;
4314
4315 BEGIN
4316
4317 FND_MSG_PUB.initialize;
4318
4319 Initialize
4320 (x_return_status => l_return_status,
4321 p_event_type => 'BP',
4322 p_source_id => p_worksheet_id,
4323 p_auto_offset => p_auto_offset
4324 );
4325
4326 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4327 RAISE FND_API.G_EXC_ERROR;
4328 END IF;
4329
4330 FOR c_year_rec IN c_year LOOP
4331 l_year_start_date := c_year_rec.start_date;
4332 l_year_end_date := c_year_rec.end_date;
4333 l_budget_year_type_id := c_year_rec.budget_year_type_id;
4334 END LOOP;
4335
4336 FOR c_ws_rec IN c_ws LOOP
4337 l_include_cbc_commit_balance := c_ws_rec.include_cbc_commit_balance;
4338 l_include_cbc_oblig_balance := c_ws_rec.include_cbc_oblig_balance;
4339 l_include_cbc_budget_balance := c_ws_rec.include_cbc_budget_balance;
4340 END LOOP;
4341
4342 FOR c_stage_rec IN c_stage LOOP
4343 l_stage_sequence := c_stage_rec.sequence_number;
4344 END LOOP;
4345
4346 l_gl_budget_set_id := p_gl_budget_set_id;
4347
4348 PSB_GL_BUDGET_SET_PVT.Validate_Account_Overlap
4349 (p_api_version => 1.0,
4350 p_return_status => l_return_status,
4351 p_msg_data => l_msg_data,
4352 p_msg_count => l_msg_count,
4353 p_gl_budget_set_id => l_gl_budget_set_id,
4354 p_validation_status => l_acct_overlap_status
4355 );
4356
4357 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4358 RAISE FND_API.G_EXC_ERROR;
4359 END IF;
4360
4361 IF l_acct_overlap_status <> FND_API.G_RET_STS_SUCCESS THEN
4362 Add_Message('PSB', 'PSB_VAL_LINE');
4363 END IF;
4364
4365 /*FOR Bug No : 2712019 Start*/
4366 --Validate the GL Budget year FOR all the gl budgets IN PSB GL Budget set
4367 --AND this procedure throws an error IF there IS no correponding year
4368 --opened FOR any GL Budget IN PSB GL Budget Set
4369
4370 Validate_GL_Budget_Year
4371 (x_return_status => l_return_status,
4372 p_gl_budget_set_id => p_gl_budget_set_id,
4373 p_year_start_date => l_year_start_date,
4374 p_year_end_date => l_year_end_date
4375 );
4376
4377 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4378 RAISE FND_API.G_EXC_ERROR;
4379 END IF;
4380 /*FOR Bug No : 2712019 END*/
4381
4382 -- continue validation regardless value of validation status
4383
4384 IF p_year_journal = 'Y' THEN
4385 BEGIN
4386 Get_GL_Period
4387 (x_return_status => l_return_status,
4388 x_effective_period_num => l_event_number,
4389 p_start_date => l_year_start_date,
4390 x_period_name => l_period_name,
4391 x_period_start_date => l_accounting_date,
4392 x_period_end_date => l_period_end_date,
4393 x_period_status => l_period_status,
4394 x_period_year => l_gl_year,
4395 x_period_number => l_gl_period_num,
4396 x_quarter_number => l_gl_quarter_num
4397 );
4398
4399 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4400 RAISE FND_API.G_EXC_ERROR;
4401 END IF;
4402
4403 Validate_Funding_Account
4404 (x_return_status => l_return_status,
4405 p_event_type => 'BP',
4406 p_source_id => p_worksheet_id,
4407 p_stage_sequence => l_stage_sequence,
4408 p_budget_year_id => p_budget_year_id,
4409 p_gl_budget_set_id => l_gl_budget_set_id,
4410 p_start_date => l_accounting_date,
4411 x_validation_status => l_validation_status
4412 );
4413
4414 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4415 RAISE FND_API.G_EXC_ERROR;
4416 END IF;
4417
4418 l_funding_status := l_validation_status;
4419 END;
4420 ELSE
4421 BEGIN
4422 l_funding_status := FND_API.G_RET_STS_SUCCESS;
4423
4424 FOR c_period_rec IN c_period LOOP
4425 Get_GL_Period
4426 (x_return_status => l_return_status,
4427 x_effective_period_num => l_event_number,
4428 p_start_date => c_period_rec.start_date,
4429 x_period_name => l_period_name,
4430 x_period_start_date => l_accounting_date,
4431 x_period_end_date => l_period_end_date,
4432 x_period_status => l_period_status,
4433 x_period_year => l_gl_year,
4434 x_period_number => l_gl_period_num,
4435 x_quarter_number => l_gl_quarter_num
4436 );
4437
4438 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4439 RAISE FND_API.G_EXC_ERROR;
4440 END IF;
4441
4442 Validate_Funding_Account
4443 (x_return_status => l_return_status,
4444 p_event_type => 'BP',
4445 p_source_id => p_worksheet_id,
4446 p_stage_sequence => l_stage_sequence,
4447 p_budget_year_id => p_budget_year_id,
4448 p_gl_budget_set_id => l_gl_budget_set_id,
4449 p_start_date => l_accounting_date,
4450 x_validation_status => l_validation_status
4451 );
4452
4453 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4454 RAISE FND_API.G_EXC_ERROR;
4455 END IF;
4456
4457 IF ((l_funding_status = FND_API.G_RET_STS_SUCCESS)
4458 AND
4459 (l_validation_status <> FND_API.G_RET_STS_SUCCESS)
4460 )
4461 THEN
4462 l_funding_status := l_validation_status;
4463 END IF;
4464 END LOOP;
4465 END;
4466 END IF;
4467
4468 IF (l_funding_status <> FND_API.G_RET_STS_SUCCESS
4469 OR
4470 l_acct_overlap_status <> FND_API.G_RET_STS_SUCCESS
4471 )
4472 THEN
4473 RAISE FND_API.G_EXC_ERROR;
4474 ELSE
4475 BEGIN
4476 IF p_year_journal = 'Y' THEN
4477 BEGIN
4478 Get_GL_Period
4479 (x_return_status => l_return_status,
4480 x_effective_period_num => l_event_number,
4481 p_start_date => l_year_start_date,
4482 x_period_name => l_period_name,
4483 x_period_start_date => l_accounting_date,
4484 x_period_end_date => l_period_end_date,
4485 x_period_status => l_period_status,
4486 x_period_year => l_gl_year,
4487 x_period_number => l_gl_period_num,
4488 x_quarter_number => l_gl_quarter_num
4489 );
4490
4491 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4492 RAISE FND_API.G_EXC_ERROR;
4493 END IF;
4494
4495 -- Include your changes here.
4496 l_column := 0;
4497
4498 Create_JE_Lines_Fund
4499 (x_return_status => l_return_status,
4500 p_worksheet_id => p_worksheet_id,
4501 p_budget_stage_id => p_budget_stage_id,
4502 p_budget_year_id => p_budget_year_id ,
4503 p_detailed => p_gl_transfer_mode,
4504 p_auto_offset => p_auto_offset,
4505 p_gl_budget_set_id => p_gl_budget_set_id,
4506 p_start_date => l_accounting_date,
4507 p_end_date => l_year_end_date,
4508 p_column => l_column,
4509 p_je_source => g_source_name,
4510 p_je_category => g_category_name,
4511 p_period_name => l_period_name,
4512 p_gl_year => l_gl_year,
4513 p_gl_period_num => l_gl_period_num,
4514 p_gl_quarter_num => l_gl_quarter_num
4515 );
4516
4517 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4518 RAISE FND_API.G_EXC_ERROR;
4519 END IF;
4520 END;
4521 ELSE
4522 BEGIN
4523 l_column := 0;
4524
4525 FOR c_period_rec IN c_period LOOP
4526 l_column := l_column + 1;
4527
4528 Get_GL_Period
4529 (x_return_status => l_return_status,
4530 x_effective_period_num => l_event_number,
4531 p_start_date => c_period_rec.start_date,
4532 x_period_name => l_period_name,
4533 x_period_start_date => l_accounting_date,
4534 x_period_end_date => l_period_end_date,
4535 x_period_status => l_period_status,
4536 x_period_year => l_gl_year,
4537 x_period_number => l_gl_period_num,
4538 x_quarter_number => l_gl_quarter_num
4539 );
4540
4541 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4542 RAISE FND_API.G_EXC_ERROR;
4543 END IF;
4544
4545 Create_JE_Lines_Fund
4546 (x_return_status => l_return_status,
4547 p_worksheet_id => p_worksheet_id,
4548 p_budget_stage_id => p_budget_stage_id,
4549 p_budget_year_id => p_budget_year_id ,
4550 p_detailed => p_gl_transfer_mode,
4551 p_auto_offset => p_auto_offset,
4552 p_gl_budget_set_id => p_gl_budget_set_id ,
4553 p_start_date => l_accounting_date,
4554 p_end_date => l_period_end_date,
4555 p_column => l_column,
4556 p_je_source => g_source_name,
4557 p_je_category => g_category_name,
4558 p_period_name => l_period_name,
4559 p_gl_year => l_gl_year,
4560 p_gl_period_num => l_gl_period_num,
4561 p_gl_quarter_num => l_gl_quarter_num
4562 );
4563
4564 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4565 RAISE FND_API.G_EXC_ERROR;
4566 END IF;
4567 END LOOP;
4568 END;
4569 END IF;
4570
4571 /*FOR Bug No : 2221409 Start*/
4572 --commented the following procedure as the same has to be
4573 --invoked after uploading all the lines INTO SLA tables
4574 /*
4575 PSB_BUDGET_REVISIONS_PVT.Create_Base_Budget_Revision
4576 (p_api_version => 1.0,
4577 p_return_status => l_return_status,
4578 p_msg_count => l_msg_count,
4579 p_msg_data => l_msg_data,
4580 p_worksheet_id => p_worksheet_id);
4581
4582 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4583 RAISE FND_API.G_EXC_ERROR;
4584 END IF;
4585 */
4586 /*FOR Bug No : 2221409 END*/
4587
4588 --
4589 -- Starting the concurrent program
4590 --
4591 Submit_Concurrent_Request
4592 (x_return_status => l_return_status,
4593 p_source_id => p_worksheet_id,
4594 p_event_type => 'BP',
4595 p_order_by1 => p_order_by1,
4596 p_order_by2 => p_order_by2,
4597 p_order_by3 => p_order_by3,
4598 p_gl_budget_set_id => l_gl_budget_set_id,
4599 p_budget_year_id => p_budget_year_id
4600 );
4601
4602 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4603 RAISE FND_API.G_EXC_ERROR;
4604 END IF;
4605 END;
4606 END IF; -- no validation error found
4607
4608 /*FOR Bug No : 2221409 Start*/
4609
4610 PSB_BUDGET_REVISIONS_PVT.Create_Base_Budget_Revision
4611 (p_api_version => 1.0,
4612 p_return_status => l_return_status,
4613 p_msg_count => l_msg_count,
4614 p_msg_data => l_msg_data,
4615 p_worksheet_id => p_worksheet_id
4616 );
4617
4618 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4619 RAISE FND_API.G_EXC_ERROR;
4620 END IF;
4621
4622 /*FOR Bug No : 2221409 END*/
4623 -- Initialize API RETURN status to success
4624
4625 x_return_status := FND_API.G_RET_STS_SUCCESS;
4626
4627
4628 EXCEPTION
4629
4630 WHEN FND_API.G_EXC_ERROR THEN
4631 /*FOR Bug No : 2712019 Start*/
4632 --ROLLBACK TO Adopted_Budget;
4633 ROLLBACK TO Create_Adopted_Budget;
4634 /*FOR Bug No : 2712019 END*/
4635 x_return_status := FND_API.G_RET_STS_ERROR;
4636
4637 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4638 /*FOR Bug No : 2712019 Start*/
4639 --ROLLBACK TO Adopted_Budget;
4640 ROLLBACK TO Create_Adopted_Budget;
4641 /*FOR Bug No : 2712019 END*/
4642 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4643
4644 WHEN OTHERS THEN
4645 /*FOR Bug No : 2712019 Start*/
4646 --ROLLBACK TO Adopted_Budget;
4647 ROLLBACK TO Create_Adopted_Budget;
4648 /*FOR Bug No : 2712019 END*/
4649 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4650
4651 END Create_Adopted_Budget;
4652
4653 /*===========================================================================+
4654 | PROCEDURE Create Adopted Budget - post upgrade |
4655 +===========================================================================*/
4656 --
4657 -- This IS the execution file FOR the concurrent program Create Adopted Budget
4658 -- through Standard Report Submissions.
4659 --
4660 PROCEDURE Create_Adopted_Budget_CP
4661 (errbuf OUT NOCOPY VARCHAR2,
4662 retcode OUT NOCOPY VARCHAR2,
4663 p_worksheet_id IN NUMBER,
4664 p_budget_stage_id IN NUMBER,
4665 p_budget_year_id IN NUMBER,
4666 p_year_journal IN VARCHAR2,
4667 p_gl_transfer_mode IN VARCHAR2,
4668 p_auto_offset IN VARCHAR2,
4669 p_gl_budget_set_id IN NUMBER
4670 )
4671 IS
4672 --
4673 l_api_name CONSTANT VARCHAR2(30) := 'Create_Budget_Journal_CP';
4674 l_api_version CONSTANT NUMBER := 1.0 ;
4675 --
4676 l_error_api_name VARCHAR2(2000);
4677 l_return_status VARCHAR2(1) ;
4678 l_msg_count NUMBER ;
4679 l_msg_data VARCHAR2(2000) ;
4680 l_msg_index_out NUMBER;
4681 --
4682 BEGIN
4683 --
4684 Create_Adopted_Budget
4685 (x_return_status => l_return_status,
4686 p_worksheet_id => p_worksheet_id,
4687 p_budget_stage_id => p_budget_stage_id,
4688 p_budget_year_id => p_budget_year_id,
4689 p_year_journal => p_year_journal,
4690 p_gl_transfer_mode => p_gl_transfer_mode,
4691 p_auto_offset => p_auto_offset,
4692 p_gl_budget_set_id => p_gl_budget_set_id,
4693 p_order_by1 => NULL,
4694 p_order_by2 => NULL,
4695 p_order_by3 => NULL
4696 );
4697 --
4698 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4699 RAISE FND_API.G_EXC_ERROR;
4700 END IF;
4701 --
4702
4703 --
4704 -- Check whether the API performed the validation successfully. Otherwise,
4705 -- we will fail the concurrent program so that the user can fix it.
4706 --
4707 IF NVL(l_msg_count, 0) > 0 THEN
4708
4709 -- Print error on the OUTPUT file.
4710 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.OUTPUT ,
4711 p_print_header => FND_API.G_TRUE ) ;
4712 --
4713 retcode := 2 ;
4714 --
4715 ELSE
4716 retcode := 0 ;
4717 END IF;
4718 --
4719 --COMMIT WORK; --Bug#4310411
4720 --
4721 EXCEPTION
4722
4723 WHEN FND_API.G_EXC_ERROR THEN
4724 --
4725 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
4726 p_print_header => FND_API.G_TRUE ) ;
4727 retcode := 2 ;
4728 --
4729 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4730 --
4731 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
4732 p_print_header => FND_API.G_TRUE ) ;
4733 retcode := 2 ;
4734 --
4735
4736 WHEN OTHERS THEN
4737 --
4738 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
4739 --
4740 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,
4741 l_api_name ) ;
4742 END IF ;
4743 --
4744 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
4745 p_print_header => FND_API.G_TRUE ) ;
4746 retcode := 2 ;
4747 --
4748 END Create_Adopted_Budget_CP;
4749
4750 /* Start bug 3659531 */
4751 -- This procedure returns the status of Budget Revision posting.
4752 PROCEDURE Find_Document_Posting_Status (
4753 x_return_Status OUT NOCOPY VARCHAR2, -- Bug#4460150
4754 x_document_posted_flag OUT NOCOPY VARCHAR2, -- Bug#4460150
4755 p_document_type IN VARCHAR2,
4756 p_document_Id IN NUMBER)
4757 IS
4758 -- local variables
4759 l_api_name CONSTANT VARCHAR2(30) := 'Find Document Posting Status';
4760
4761 /* for bug 4654145 --> Check the psb_gl_interfaces table whether the BR has been posted or not */
4762 l_no NUMBER;
4763
4764 BEGIN
4765 IF p_Document_Type = 'BR' THEN
4766 SELECT 1
4767 INTO l_no
4768 FROM psb_gl_interfaces
4769 WHERE worksheet_id = p_document_id
4770 AND budget_source_type = p_document_type
4771 AND rownum = 1;
4772
4773 x_document_posted_flag := 'Y';
4774 END IF;
4775
4776 EXCEPTION
4777 WHEN no_data_found THEN
4778 x_document_posted_flag := 'N';
4779 WHEN OTHERS THEN
4780 x_document_posted_flag := 'N';
4781
4782 -- set the status of the API
4783 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4784
4785 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
4786 fnd_msg_pub.add_exc_msg(G_PKG_NAME , l_api_name);
4787 END IF;
4788
4789 END Find_Document_Posting_Status;
4790 /* END bug 3659531 */
4791
4792
4793 END PSB_GL_Interface_PVT;
4794