1 PACKAGE BODY Fv_Ye_Carryforward AS
2 --$Header: FVXYECFB.pls 120.23.12020000.4 2013/02/13 14:57:33 snama ship $
3 -- ======================================================================
4 -- Variable Naming Conventions
5 -- ======================================================================
6 -- 1. Input/Output Parameter global variables
7 -- have the format "vp_<Variable Name>"
8 -- 2. Other Global Variables have the format "vg_<Variable_Name>"
9 -- 3. Procedure Level local variables have
10 -- the format "vl_<Variable_Name>"
11 -- 4. PL/SQL Table variables have "vt_<Variable_Name>"
12 -- 5. User Defined Exceptions have "e_<Exception_Name>"
13 -- 6. Variable Cursors have "vc_<Variable_Name>"
14 -- ======================================================================
15 -- Parameter Global Variable Declarations
16 -- ======================================================================
17 vp_errbuf VARCHAR2(1000) ;
18 vp_retcode NUMBER := 0 ;
19 vp_sob_id Gl_Sets_Of_Books.set_of_books_id%TYPE ;
20 vp_carryfor_fyr Gl_Periods.period_year%TYPE ;
21 -- ======================================================================
22 -- Other Global Variable Declarations
23 -- ======================================================================
24 vg_coa_id Gl_Sets_Of_Books.chart_of_accounts_id%TYPE;
25 vg_bal_seg_value varchar2(30);
26 vg_period_set_name Gl_Sets_Of_Books.period_set_name%TYPE;
27 vg_currency Gl_Sets_Of_Books.currency_code%TYPE;
28 vg_closing_period Gl_Period_Statuses.period_name%TYPE;
29 vg_carryfor_period Gl_Period_Statuses.period_name%TYPE;
30 vg_start_date Gl_Period_Statuses.start_date%TYPE;
31 vg_closing_fyr Gl_Periods.period_year%TYPE;
32 vg_jrnl_group_id NUMBER;
33 vg_interface_run_id NUMBER;
34 vg_bal_seg_val_opt_code VARCHAR2(1);
35 vg_acct_seg_name varchar2(250);
36 TYPE t_numbertable IS TABLE OF NUMBER
37 INDEX BY BINARY_INTEGER;
38 TYPE t_reference IS TABLE OF VARCHAR2(250)
39 INDEX BY BINARY_INTEGER;
40 vt_dr_balances t_numbertable;
41 vt_cr_balances t_numbertable;
42 vt_ccids t_numbertable;
43 vt_reference t_reference;
44 e_error EXCEPTION;
45
46 vg_public_law_attribute fv_system_parameters.factsii_pub_law_code_attribute%TYPE;
47 vg_advance_type_attribute fv_system_parameters.factsii_advance_type_attribute%TYPE;
48 vg_trf_dept_id_attribute fv_system_parameters.factsii_tr_dept_id_attribute%TYPE;
49 vg_trf_main_acct_attribute fv_system_parameters.factsii_tr_main_acct_attribute%TYPE;
50
51 vg_facts_attributes_setup BOOLEAN ;
52 g_module_name VARCHAR2(100);
53 c_gl_line_cur INTEGER;
54
55 -- ------------------------------------------------------------------
56 -- Procedure Main
57 -- ------------------------------------------------------------------
58 -- Main procedure that is called from the Carry Forward budgetary
59 -- account balances concurrent request. This procedure calls all the
60 -- subsequent procedures in the Carry forward process.
61 -- ------------------------------------------------------------------
62 PROCEDURE Main( errbuf OUT NOCOPY VARCHAR2,
63 retcode OUT NOCOPY NUMBER,
64 sob NUMBER,
65 carryfor_fyr NUMBER) IS
66 l_module_name VARCHAR2(200) ;
67 l_bc_count NUMBER;
68 BEGIN
69 l_module_name := g_module_name || 'main ';
70 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
71 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'STARTING THE CARRY FORWARD BUDGETARY ACCOUNT ' ||
72 'Balances Process.....');
73 END IF;
74 -- Assign initial values
75 vp_retcode := 0;
76 vp_errbuf := NULL;
77 -- Load the parameter global variables
78 vp_sob_id := sob;
79 vp_carryfor_fyr := carryfor_fyr;
80 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
81 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'THE PARAMETERS PASSED TO THE CARRY FORWARD PROCESS ARE: '||
82 'Set of books id = '||TO_CHAR(vp_sob_id)||
86 SELECT count(*)
83 ',Carry Forward Fiscal Year = '||TO_CHAR(vp_carryfor_fyr));
84 END IF;
85
87 INTO l_bc_count
88 FROM gl_period_statuses
89 WHERE application_id = 101
90 AND ledger_id = vp_sob_id
91 AND period_year = vp_carryfor_fyr
92 AND NVL(track_bc_ytd_flag, 'N') = 'Y';
93
94 IF (l_bc_count > 0) THEN
95 FV_UTILITY.LOG_MESG (FND_LOG.LEVEL_ERROR, l_module_name, 'Carry forward process is not allowed for this year.');
96 vp_retcode := -1;
97 retcode := vp_retcode;
98 RETURN;
99 END IF;
100 -- Get the Coa and Currency Code
101 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
102 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'GETTING THE CHART OF ACCOUNTS AND CURRENCY CODE');
103 END IF;
104 Get_Required_Parameters;
105 IF (vp_retcode = 0) THEN
106 -- Get the Closing Fyr,Last Period for the Closing Fyr and
107 -- First Period for the Carry Forward Fyr
108 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
109 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'GETTING THE CLOSING FYR, LAST PERIOD '||
110 'of the Closing Fiscal Year and '||
111 'First Period of the Carry Forward Fiscal Year');
112 END IF;
113 Get_Period_Details;
114 END IF;
115 IF (vp_retcode = 0) THEN
116 -- Clean up gl_interface table,if any records exist in the interface
117 -- from the previous run of the process.
118 Cleanup_Gl_Interface;
119 END IF;
120 IF (vp_retcode = 0) THEN
121 -- Check if the Carry Forward process has been run earlier
122 -- for the same period.
123 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
124 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CHECKING IF THE CARRYFORWARD PROCESS HAS BEEN '||
125 'run earlier for the same period.');
126 END IF;
127
128 Check_Carryforward_Process;
129 END IF;
130 IF vp_retcode <> 0 THEN
131 -- Check for errors
132 errbuf := vp_errbuf;
133 retcode := vp_retcode;
134 ROLLBACK;
135 ELSE
136 COMMIT;
137 retcode := 0;
138 errbuf := '** Carry Forward Budgetary Account Balances Process '||
139 'completed successfully **';
140 END IF;
141 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
142 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'ENDING THE CARRY FORWARD BUGETARY ACCOUNT BALANCES '||
143 'Process ......');
144 END IF;
145 EXCEPTION
146 WHEN OTHERS THEN
147 ROLLBACK;
148 errbuf := '** Carry Forward Budgetary Account Balances Process Failed ** '
149 ||SQLERRM;
150
151 retcode := 2;
152 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
153 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name
154 ,errbuf);
155 END IF;
156 END Main;
157 -- ------------------------------------------------------------------
158 -- Procedure Get_Required_Parameters
159 -- ------------------------------------------------------------------
160 -- Get_Required_Parameters procedure is called from Main procedure.
161 -- It gets the coa and the currency code.
162 -- ------------------------------------------------------------------
163 PROCEDURE Get_Required_Parameters IS
164 l_module_name VARCHAR2(200) ;
165 l_err_code BOOLEAN;
166 BEGIN
167 l_module_name := g_module_name || 'Get_Required_Parameters ';
168 -- Get the Coa and Currency code
169 BEGIN
170 SELECT currency_code ,
171 CHART_OF_ACCOUNTS_ID,
172 BAL_SEG_VALUE_OPTION_CODE
173 INTO vg_currency ,
174 vg_coa_id ,
175 vg_bal_seg_val_opt_code
176 FROM gl_ledgers_public_v
177 WHERE ledger_id = vp_sob_id;
178 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
179 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CHART OF ACCOUNTS ID = '||TO_CHAR(VG_COA_ID));
180 END IF;
181
182 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
183 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CURRENCY CODE = '||VG_CURRENCY);
184 END IF;
185 EXCEPTION
186 WHEN NO_DATA_FOUND THEN
187 vp_errbuf := 'Error in Get_Required_Parameters:'||
188 ' Currency Code or Chart of Account is not defined';
189 vp_retcode := 1;
190 RETURN;
191 END;
192
193 FV_UTILITY.get_segment_col_names( vg_coa_id,
194 vg_acct_seg_name, Vg_bal_seg_value ,
195 l_err_code,
196 vp_errbuf) ;
197
198
199 BEGIN
200 SELECT factsii_pub_law_code_attribute,
201 factsii_advance_type_attribute, factsii_tr_dept_id_attribute,
202 factsii_tr_main_acct_attribute
203 INTO vg_public_law_attribute,
204 vg_advance_type_attribute, vg_trf_dept_id_attribute,
205 vg_trf_main_acct_attribute
206 FROM Fv_System_Parameters;
207
208 -- Set the global variable to false if public law code and other parameters
209 -- are not setup in the define system parameters form.
213 vg_trf_main_acct_attribute IS NULL)
210 IF (vg_public_law_attribute IS NULL OR
211 vg_advance_type_attribute IS NULL OR
212 vg_trf_dept_id_attribute IS NULL OR
214 THEN
215 vg_facts_attributes_setup := FALSE;
216 ELSE
217 vg_facts_attributes_setup := TRUE;
218 END IF;
219
220 EXCEPTION
221 WHEN OTHERS THEN
222 vp_errbuf := 'Error in Get_Required_Parameters:'||
223 ' While determining the FACTS I Journal Attribute.';
224 vp_retcode := 2;
225 END;
226
227 EXCEPTION
228 WHEN OTHERS THEN
229 vp_retcode := 2 ;
230 vp_errbuf := SQLERRM ||' -- Error in Get_Required_Parameters procedure.';
231 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
232 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name
233 ,vp_errbuf );
234 END IF;
235 RETURN ;
236 END Get_Required_Parameters;
237 -- ------------------------------------------------------------------
238 -- Procedure Get_Period_Details
239 -- ------------------------------------------------------------------
240 -- Get_Period_Details procedure is called from Main procedure.
241 -- It gets the closing_fyr,last_period of the closing fyr,first period
242 -- of the carry forward fyr.
243 -- ------------------------------------------------------------------
244 PROCEDURE Get_Period_Details IS
245 l_module_name VARCHAR2(200) ;
246 vl_adj_flag Gl_Period_Statuses.adjustment_period_flag%TYPE;
247 vl_closing_status Gl_Period_Statuses.closing_status%TYPE;
248 BEGIN
249 l_module_name := g_module_name ||
250 'Get_Period_Details ';
251 -- Get the Closing Fyr
252 vg_closing_fyr := vp_carryfor_fyr - 1;
253 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
254 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CLOSING FISCAL YEAR IS = '||TO_CHAR(VG_CLOSING_FYR));
255 END IF;
256 -- Get the Last Period of the Closing Fyr
257 BEGIN
258 SELECT period_name
259 INTO vg_closing_period
260 FROM Gl_Period_Statuses
261 WHERE ledger_id = vp_sob_id
262 AND application_id = 101
263 AND period_year = vg_closing_fyr
264 AND period_num = (SELECT MAX(period_num)
265 FROM gl_period_statuses
266 WHERE ledger_id = vp_sob_id
267 AND application_id = 101
268 AND period_year = vg_closing_fyr);
269 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
270 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' LAST PERIOD OF THE CLOSING FISCAL YEAR = '
271 ||vg_closing_period);
272 END IF;
273 EXCEPTION
274 WHEN NO_DATA_FOUND THEN
275 vp_retcode := 1;
276 vp_errbuf := 'Error in Get_Period_Details: '||
277 'Last period is not defined for the Closing Fiscal Year.';
278 RETURN;
279 WHEN OTHERS THEN
280 vp_retcode := 2 ;
281 vp_errbuf := SQLERRM||' -- Error in Get_Period_Details procedure,'||
282 'while getting the last period of closing fiscal year.' ;
283 RETURN ;
284 END;
285 -- Get the First Period of the Carry Forward Fyr
286 BEGIN
287 SELECT period_name, adjustment_period_flag,
288 closing_status,start_date
289 INTO vg_carryfor_period,vl_adj_flag,
290 vl_closing_status, vg_start_date
291 FROM Gl_Period_Statuses
292 WHERE ledger_id = vp_sob_id
293 AND application_id = 101
294 AND period_year = vp_carryfor_fyr
295 AND period_num = (SELECT MIN(period_num)
296 FROM gl_period_statuses
297 WHERE ledger_id = vp_sob_id
298 AND application_id = 101
299 AND period_year = vp_carryfor_fyr);
300 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
301 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' FIRST PERIOD OF THE CARRY FORWARD FISCAL YEAR = '
302 ||vg_carryfor_period||', adjustment period flag = '
303 ||vl_adj_flag||', and closing status = '
304 ||vl_closing_status);
305 END IF;
306 EXCEPTION
307 WHEN NO_DATA_FOUND THEN
308 vp_retcode := 1;
309 vp_errbuf := 'Error in Get_Period_Details: '||
310 'First period is not defined for the Carry Forward Fiscal Year.';
311 RETURN;
312 WHEN OTHERS THEN
313 vp_retcode := 2 ;
314 vp_errbuf := SQLERRM||' -- Error in Get_Period_Details procedure,'||
315 'while getting the first period of carry forward fiscal year.' ;
316 RETURN ;
317 END;
318 IF (vl_adj_flag = 'N') THEN
319 vp_retcode := 2;
320 vp_errbuf := 'The first period of the Carry Forward Fiscal Year is '||
321 ' not specified as an adjusting period.';
322 RETURN;
323 END IF;
324 IF (vl_closing_status <> 'O') THEN
325 vp_retcode := 2;
326 vp_errbuf := 'The first period of the Carry Forward Fiscal Year is '||
327 'not an open period.';
328 RETURN;
329 END IF;
330 BEGIN
331 SELECT period_set_name
332 INTO vg_period_set_name
333 FROM Gl_Sets_Of_Books
334 WHERE set_of_books_id = vp_sob_id;
335 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
339 WHEN OTHERS THEN
336 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PERIOD SET NAME = '||VG_PERIOD_SET_NAME);
337 END IF;
338 EXCEPTION
340 vp_retcode := 2 ;
341 vp_errbuf := SQLERRM||' -- Error in Get_Period_Details procedure,'||
342 'while getting the period set name.' ;
343 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
344 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name
345 ,vp_errbuf );
346 END IF;
347 RETURN ;
348 END;
349
350
351 EXCEPTION
352 WHEN OTHERS THEN
353 vp_retcode := 2 ;
354 vp_errbuf := SQLERRM ||' -- Error in Get_Period_Details procedure.';
355 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
356 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name
357 ,vp_errbuf );
358 END IF;
359 RETURN ;
360 END Get_Period_Details;
361 -- ------------------------------------------------------------------
362 -- Procedure Check_Carryforward_Process
363 -- ------------------------------------------------------------------
364 -- Check_Carryforward_Process procedure is called from Main procedure.
365 -- It checks whether the carryforward process has been run earlier for
366 -- the same period. If it has been run, then it checks to see if the
367 -- journal entries have been reversed or not.
368 -- ------------------------------------------------------------------
369 PROCEDURE Check_Carryforward_Process IS
370 vl_cnt NUMBER;
371 vl_header_id Gl_Je_Headers.je_header_id%TYPE;
372 vl_accrrev_status Gl_Je_Headers.accrual_rev_status%TYPE;
373 vl_rev_header_id Gl_Je_Headers.accrual_rev_je_header_id%TYPE;
374 vl_rev_status Gl_Je_Headers.status%TYPE;
375 vl_status Gl_Je_Headers.status%TYPE;
376 CURSOR get_count_cur IS
377 SELECT COUNT(*)
378 FROM Gl_Je_Headers
379 WHERE ledger_id = vp_sob_id
380 AND je_source = 'Year End Close'
381 AND je_category = 'Federal Carry Forward'
382 AND period_name = vg_carryfor_period;
383 CURSOR get_hdrs_cur IS
384 SELECT je_header_id,accrual_rev_status,
385 accrual_rev_je_header_id,status
386 FROM Gl_Je_Headers
387 WHERE ledger_id = vp_sob_id
388 AND je_source = 'Year End Close'
389 AND je_category = 'Federal Carry Forward'
390 AND period_name = vg_carryfor_period;
391 CURSOR get_revstat_cur IS
392 SELECT status
393 FROM Gl_Je_Headers
394 WHERE ledger_id = vp_sob_id
395 AND je_header_id = vl_rev_header_id;
396 l_module_name VARCHAR2(200);
397 BEGIN
398 l_module_name := g_module_name ||
399 'Check_Carryforward_Process ';
400 -- Check to see if there are any records existing in the headers table
401 -- for the same period.
402 OPEN get_count_cur;
403 FETCH get_count_cur INTO vl_cnt;
404 CLOSE get_count_cur;
405 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
406 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' THE NUMBER OF RECORDS EXISTING IN GL ARE '
407 ||TO_CHAR(vl_cnt));
408 END IF;
409 IF (vl_cnt > 0) THEN
410 FOR vc_hdrs IN get_hdrs_cur LOOP
411 vl_header_id := vc_hdrs.je_header_id;
412 vl_accrrev_status := vc_hdrs.accrual_rev_status;
413 vl_rev_header_id := vc_hdrs.accrual_rev_je_header_id;
414 vl_status := vc_hdrs.status;
415 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
416 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' IN THE LOOP FOR GETTING HEADERS, '||
417 'processing the following:');
418 END IF;
419 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
420 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' JE_HEADER_ID: '||TO_CHAR(VL_HEADER_ID) ||
421 ', accrual_rev_status: '||vl_accrrev_status||
422 ', accrual_rev_je_header_id: '||TO_CHAR(vl_rev_header_id));
423 END IF;
424 IF (vc_hdrs.accrual_rev_status IS NULL) THEN
425 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
426 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' ACCRUAL REV STATUS IS NULL, I.E. <> R');
427 END IF;
428 IF (vc_hdrs.accrual_rev_je_header_id IS NULL) THEN
429 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
430 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' ACCRUAL REV JE HEADER ID IS NULL');
431 END IF;
432 vp_retcode := 1;
433 IF (vc_hdrs.status = 'P') THEN
434 vp_errbuf := 'Carry Forward journal entries exist in GL:'||
435 'Please reverse and post those entries and '||
436 'then re-run the Carry Forward process.';
437 ELSE
438 vp_errbuf := 'Carry Forward journal entries exist in GL:'||
439 'Please delete the unposted journal entries and '||
440 'then re-run the Carry Forward process.';
441 END IF;
442 RETURN;
443 END IF;
444 ELSE
445 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
446 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' ACCRUAL REV STATUS = R');
447 END IF;
448 OPEN get_revstat_cur;
449 FETCH get_revstat_cur INTO vl_rev_status;
450 CLOSE get_revstat_cur;
451 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
452 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' THE STATUS OF THE REVERSED JE IS = '||
453 vl_rev_status);
454 END IF;
455 IF (vl_rev_status <> 'P') THEN
459 vp_retcode := 1;
456 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
457 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' STATUS <> POSTED');
458 END IF;
460 vp_errbuf := 'Reverse Carry Forward journal entries exist '||
461 'in GL, which are not posted.'||
462 'Please post those entries and re-run the '||
463 'Carry Forward process.';
464 RETURN;
465 END IF;
466 END IF;
467 END LOOP;
468 END IF;
469 -- Get Balances and ccid's
470 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
471 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CALLING GET_BALANCES PROCEDURE');
472 END IF;
473 Get_Balances;
474 IF (vp_retcode <> 0) THEN
475 RAISE e_error;
476 END IF;
477 EXCEPTION
478 WHEN e_error THEN
479 RETURN;
480 WHEN OTHERS THEN
481 vp_retcode := 2 ;
482 vp_errbuf := SQLERRM||' -- Error in Get_Carryforward_process procedure.';
483 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
484 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name
485 ,vp_errbuf );
486 END IF;
487 RETURN ;
488 END Check_Carryforward_Process;
489 -- ------------------------------------------------------------------
490 -- Procedure Get_Balances
491 -- ------------------------------------------------------------------
492 -- Get_Balances procedure is called from Check_Carryforward_Process
493 -- procedure.It gets the balances and ccids for all the budgetary
494 -- accounts for the last period of the closing fyr.
495 -- ------------------------------------------------------------------
496 PROCEDURE Get_Balances IS
497 vl_rec_found_flag VARCHAR2(1) ;
498 l_module_name VARCHAR2(200);
499
500 -- Dynamic Sql variables
501 l_insert_stmt VARCHAR2(2000);
502 vl_attribute_cols VARCHAR2(1024);
503 vl_group_by_clause VARCHAR2(1024);
504 l_user_id NUMBER(15) ;
505 l_select_stmt VARCHAR2(6000);
506 vl_exec_cur INTEGER ;
507 BEGIN
508 vl_rec_found_flag := 'N';
509 l_user_id := fnd_global.user_id ;
510 l_module_name := g_module_name || 'Get_Balances ';
511 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
512 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' IN GET_BALANCES PROCEDURE');
513 END IF;
514
515 -- Setup Gl Interface
516 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
517 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CALLING SETUP_GL_INTERFACE PROCEDURE');
518 END IF;
519 Setup_Gl_Interface;
520 IF (vp_retcode <> 0) THEN
521 RAISE e_error;
522 END IF;
523
524 IF vg_public_law_attribute IS NOT NULL THEN
525 vl_attribute_cols := vl_attribute_cols||', NVL(b.public_law_code, l.'||vg_public_law_attribute||') ';
526 ELSE
527 vl_attribute_cols := vl_attribute_cols||', NULL';
528 END IF;
529
530 IF vg_advance_type_attribute IS NOT NULL THEN
531 vl_attribute_cols := vl_attribute_cols||', NVL(b.advance_type, l.'||vg_advance_type_attribute||') ';
532 ELSE
533 vl_attribute_cols := vl_attribute_cols||', NULL';
534 END IF;
535
536 IF vg_trf_dept_id_attribute IS NOT NULL THEN
537 vl_attribute_cols := vl_attribute_cols||', NVL(b.dept_id, l.'||vg_trf_dept_id_attribute||') ';
538 ELSE
539 vl_attribute_cols := vl_attribute_cols||', NULL';
540 END IF;
541
542 IF vg_trf_main_acct_attribute IS NOT NULL THEN
543 vl_attribute_cols := vl_attribute_cols||', NVL(b.main_account, l.'|| vg_trf_main_acct_attribute ||') ';
544 ELSE
545 vl_attribute_cols := vl_attribute_cols||', NULL';
546 END IF;
547
548 IF (vl_attribute_cols IS NOT NULL) THEN
549 vl_group_by_clause := ' GROUP BY gcc.code_combination_id ,' ||SUBSTR(vl_attribute_cols, 2);
550 END IF;
551
552 BEGIN
553 c_gl_line_cur := DBMS_SQL.OPEN_CURSOR ;
554 EXCEPTION
555 WHEN OTHERS THEN
556 vp_retcode := 2;
557 vp_errbuf := SQLERRM|| ' Open cursor error in Populate_Gl_Interface procedure.';
558 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,vp_errbuf);
559 RAISE e_error;
560 END ;
561
562 -- Constructing the insert into Gl interface from the select of the query
563 BEGIN
564 l_insert_stmt := ' INSERT INTO Gl_Interface(status,
565 ledger_id ,
566 accounting_date ,
567 currency_code ,
568 date_created ,
569 created_by ,
570 actual_flag ,
571 user_je_category_name,
572 user_je_source_name ,
573 entered_dr ,
574 entered_cr ,
575 group_id ,
576 period_name ,
577 chart_of_accounts_id,
578 code_combination_id ,
579 reference21 ,
580 attribute' || NVL(SUBSTR(vg_public_law_attribute , 10),17) || ' ,
581 attribute' || NVL(SUBSTR(vg_advance_type_attribute , 10),18) || ' ,
582 attribute' || NVL(SUBSTR(vg_trf_dept_id_attribute , 10),19) || ' ,
586 ' SELECT
583 attribute' || NVL(SUBSTR(vg_trf_main_acct_attribute, 10),20) || ' ) ';
584
585 l_select_stmt :=
587 ''NEW'' , '
588 || vp_sob_id || ' , '''
589 || vg_start_date || ''' , '''
590 || vg_currency
591 || ''' , SYSDATE , ' ||
592 l_user_id || ' ,
593 ''A'' ,
594 ''Federal Carry Forward'' ,
595 ''Year End Close'' , '
596 || ' SUM(ROUND(NVL(xdl.unrounded_accounted_dr,0),2)) debit,
597 SUM(ROUND(NVL(xdl.unrounded_accounted_cr,0),2)) credit , '
598 || vg_jrnl_group_id || ' , '''
599 || vg_carryfor_period || ''' , '
600 || vg_coa_id
601 || ' , gcc.code_combination_id , '
602 || ' MAX(xdl.SOURCE_DISTRIBUTION_ID_NUM_1) reference_1 '
603 || vl_attribute_cols || '
604 FROM fv_be_trx_dtls B, xla_ae_lines xl ,
605 xla_distribution_links xdl, gl_je_lines l,
606 gl_je_headers h , gl_code_combinations gcc,
607 gl_import_references gir
608 WHERE l.code_combination_id = gcc.code_combination_id
609 AND l.je_header_id = h.je_header_id
610
611
612
613 AND FV_YE_CARRYFORWARD.Check_bal_seg_value(GCC.'||VG_BAL_SEG_VALUE||',
614 :sob_id,
615 :bal_seg_val_opt_code) = '||''''||'Y'||''''||
616 ' AND xl.code_combination_id = l.code_combination_id
617 AND gir.je_header_id = h.je_header_id
618 AND gir.je_batch_id = h.je_batch_id
619 AND gir.je_line_num = l.je_line_num
620 AND xl.ae_header_id = xdl.ae_header_id
621 AND xl.ae_line_num = xdl.ae_line_num
622 AND xl.gl_sl_link_id = gir.gl_sl_link_id
623 AND xl.currency_code = h.currency_code
624 AND NVL(h.je_from_sla_flag, ''N'') = ''Y''
625 AND EXISTS (SELECT 1
626 FROM gl_periods
627 WHERE period_year = :closing_fyr
628 AND period_set_name = :period_set_name
629 AND period_name = l.period_name)
630 AND nvl(xdl.SOURCE_DISTRIBUTION_ID_NUM_1,-99)=b.transaction_id(+)
631 AND b.set_of_books_id (+) = :sob_id
632 AND l.status = ''P''
633 AND h.actual_flag = ''A''
634 AND h.ledger_id = :sob_id
635 --AND h.currency_code = :vg_currency --bug 5570564
636 AND h.currency_code <> ''STAT''
637 AND gcc.summary_flag = '||''''||'N'||''''||
638 ' AND gcc.template_id IS NULL
639 AND gcc.chart_of_accounts_id = :c_coa
640 AND gcc.account_type IN ('||''''||'C'||''''||','||
641 ''''||'D'||''''||')'|| vl_group_by_clause ||
642 ' UNION
643 SELECT
644 ''NEW'' , '
645 || vp_sob_id || ' , '''
646 || vg_start_date || ''' , '''
647 || vg_currency
648 || ''' , SYSDATE , ' ||
649 l_user_id || ' ,
650 ''A'' ,
651 ''Federal Carry Forward'' ,
652 ''Year End Close'' , '
653 || ' SUM(NVL(l.accounted_dr,0)) debit,
654 SUM(NVL(l.accounted_cr,0)) credit , '
655 || vg_jrnl_group_id || ' , '''
656 || vg_carryfor_period || ''' , '
657 || vg_coa_id
658 || ' , gcc.code_combination_id , '
659 || ' MAX(fv_ye_carryforward.convert_to_num(l.reference_1)) '
660 || vl_attribute_cols || '
661 FROM gl_je_lines l , fv_be_trx_dtls b, gl_je_headers h , gl_code_combinations gcc
662 WHERE l.code_combination_id = gcc.code_combination_id
663 AND FV_YE_CARRYFORWARD.Check_bal_seg_value(GCC.'|| VG_BAL_SEG_VALUE||', :sob_id,:bal_seg_val_opt_code ) = '||''''||'Y'||''''||
664 ' AND l.je_header_id = h.je_header_id
665 AND NVL(h.je_from_sla_flag, ''N'') IN (''N'',''U'')
666 AND EXISTS (SELECT 1
667 FROM gl_periods
668 WHERE period_year = :closing_fyr
669 AND period_set_name = :period_set_name
670 AND period_name = l.period_name)
671 AND nvl(Fv_Ye_Carryforward.Convert_To_Num(l.reference_1),-99)=b.transaction_id(+)
672 AND l.ledger_id = b.set_of_books_id(+)
673 AND l.status = ''P''
674 AND h.actual_flag = ''A''
675 AND h.ledger_id = :sob_id
676 -- AND h.currency_code = :vg_currency --bug 5570564
677 AND h.currency_code <> ''STAT''
678 AND gcc.summary_flag = '||''''||'N'||''''||
679 ' AND gcc.template_id IS NULL
680 AND gcc.chart_of_accounts_id = :c_coa
681 AND gcc.account_type IN ('||''''||'C'||''''||','||
682 ''''||'D'||''''||')'|| vl_group_by_clause ;
683
684
685
686
687 l_select_stmt := l_insert_stmt || '( ' || l_select_stmt || ')';
688
689 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
690 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_select_stmt);
691 END IF;
692 dbms_sql.parse(c_gl_line_cur, l_select_stmt, DBMS_SQL.V7) ;
693 --dbms_sql.bind_variable(c_gl_line_cur,':vg_currency', vg_currency); -- 5570564
694 dbms_sql.bind_variable(c_gl_line_cur,':c_coa', vg_coa_id);
695 dbms_sql.bind_variable(c_gl_line_cur,':sob_id', vp_sob_id);
696 dbms_sql.bind_variable(c_gl_line_cur,':closing_fyr', vg_closing_fyr);
697 dbms_sql.bind_variable(c_gl_line_cur,':period_set_name', vg_period_set_name);
698 dbms_sql.bind_variable(c_gl_line_cur,':bal_seg_val_opt_code', vg_bal_seg_val_opt_code);
699
700 vl_exec_cur := dbms_sql.EXECUTE(c_gl_line_cur);
701
702 fnd_file.put_line(FND_FILE.LOG,'No of Records inserted into GL_interface :' || to_char(vl_exec_cur));
703 EXCEPTION
704 WHEN OTHERS THEN
705 vp_retcode := 2;
706 vp_errbuf := SQLERRM|| ' Parse cursor error in Get_Balances procedure.';
707 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,vp_errbuf);
708 RAISE e_error;
709 END ;
710
711 IF dbms_sql.is_open(c_gl_line_cur) THEN
712 dbms_sql.close_cursor(c_gl_line_cur);
713 END IF;
714 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
715 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' SUBMITTING JOURNAL IMPORT.');
716 END IF;
717 Submit_Journal_Import;
718 IF (vp_retcode <> 0) THEN
719 RAISE e_error;
720 END IF;
721
722 EXCEPTION
723 WHEN e_error THEN
724 IF dbms_sql.is_open(c_gl_line_cur) THEN
725 dbms_sql.close_cursor(c_gl_line_cur);
726 END IF;
727 RETURN;
728 WHEN OTHERS THEN
729 IF dbms_sql.is_open(c_gl_line_cur) THEN
730 dbms_sql.close_cursor(c_gl_line_cur);
731 END IF;
732 vp_retcode := 2 ;
733 vp_errbuf := l_select_stmt || SQLERRM||' -- Error in Get_Balances procedure.';
734 RETURN ;
735 END Get_Balances;
736
737
738 -- ------------------------------------------------------------------
739 -- Procedure Setup_Gl_Interface
740 -- ------------------------------------------------------------------
741 -- Setup_Gl_Interface procedure is called from Get_Balances procedure.
742 -- This proc inserts records in the gl_interface table, getting the ccids
743 -- and balances from the pl/sql tables and then runs journal import program.
744 -- ------------------------------------------------------------------
745 PROCEDURE Setup_Gl_Interface IS
746 vl_req_id NUMBER;
747 vl_call_status BOOLEAN;
748 vl_rphase VARCHAR2(30);
749 vl_rstatus VARCHAR2(30);
750 vl_dphase VARCHAR2(30);
751 vl_dstatus VARCHAR2(30);
752 vl_message VARCHAR2(240);
753 vl_cnt NUMBER;
754 l_module_name VARCHAR2(200);
755 BEGIN
756 l_module_name := g_module_name ||
757 'Setup_Gl_Interface ';
758 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
759 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' IN SETUP_GL_INTERFACE PROCEDURE....');
760 END IF;
761 -- Get the interface_run_id
762 vg_interface_run_id := Gl_Interface_Control_Pkg.Get_Unique_Run_Id;
763 -- Get the journal group_id
764 SELECT Gl_Interface_Control_S.NEXTVAL
765 INTO vg_jrnl_group_id
766 FROM DUAL;
767 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
768 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' INTERFACE RUN ID: '
769 ||TO_CHAR(vg_interface_run_id));
770 END IF;
771 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
772 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' JOURNAL GROUP ID: '
773 ||TO_CHAR(vg_jrnl_group_id));
774 END IF;
775 --Insert a control record in gl_interface_control for gl_import to work
776 INSERT INTO Gl_Interface_Control
777 (je_source_name,
778 status,
779 interface_run_id,
780 group_id,
781 set_of_books_id)
782 VALUES ('Year End Close',
783 'S',
784 vg_interface_run_id,
785 vg_jrnl_group_id,
786 vp_sob_id);
787 EXCEPTION
788 WHEN OTHERS THEN
789 vp_retcode := 2;
790 vp_errbuf := SQLERRM || '--Error in Setup_Gl_Interface procedure.';
791 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
792 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name
793 ,vp_errbuf);
794 END IF;
795 RETURN;
796 END Setup_Gl_Interface;
797
798 -- --------------------------------
799 -- Procedure Submit_Journal_Import
800 -- -------------------------------
801 PROCEDURE Submit_Journal_Import IS
802 vl_req_id NUMBER;
803 vl_call_status BOOLEAN;
804 vl_rphase VARCHAR2(30);
805 vl_rstatus VARCHAR2(30);
806 vl_dphase VARCHAR2(30);
807 vl_dstatus VARCHAR2(30);
808 vl_message VARCHAR2(240);
812 l_module_name := g_module_name || 'Submit_Journal_Import ';
809 l_module_name VARCHAR2(200);
810
811 BEGIN
813 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
814 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' AFTER INSERTING INTO GL_INTERFACE....');
815 END IF;
816 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
817 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CALLING THE JOURNAL IMPORT PROGRAM....');
818 END IF;
819 -- Submit a Concurrent request to invoke journal import
820 vl_req_id := FND_REQUEST.SUBMIT_REQUEST('SQLGL',
821 'GLLEZL',
822 '',
823 '',
824 FALSE,
825 TO_CHAR(vg_interface_run_id),
826 TO_CHAR(vp_sob_id),
827 'N', '', '', 'N', 'W');
828 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
829 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' THE REQUEST_ID IS '||VL_REQ_ID);
830 END IF;
831 -- if concurrent request submission failed then abort process
832 IF (vl_req_id = 0) THEN
833 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
834 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' JOURNAL IMPORT REQUEST NOT SUBMITTED.');
835 END IF;
836 vp_errbuf := 'Cannot submit journal import program';
837 vp_retcode := 1;
838 ROLLBACK;
839 RETURN;
840 ELSE
841 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
842 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' JOURNAL IMPORT REQUEST SUBMITTED '||
843 'successfully.');
844 END IF;
845 COMMIT;
846 END IF;
847
848 -- Check status of completed concurrent program
849 -- and if complete exit
850 vl_call_status := Fnd_Concurrent.Wait_For_Request(
851 vl_req_id, 20, 0, vl_rphase, vl_rstatus,
852 vl_dphase, vl_dstatus, vl_message);
853 IF (vl_call_status = FALSE) THEN
854 vp_errbuf := 'Cannot wait for the status of journal import';
855 vp_retcode := 1;
856 END IF;
857 EXCEPTION
858 WHEN OTHERS THEN
859 vp_retcode := 2;
860 vp_errbuf := SQLERRM || '--Error in Submit_Journal_Import procedure.';
861 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
862 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name
863 ,vp_errbuf);
864 END IF;
865 RETURN;
866 END Submit_Journal_Import;
867
868 -- ------------------------------------------------------------------
869 -- Procedure Cleanup_Gl_Interface
870 -- ------------------------------------------------------------------
871 -- Cleanup_Gl_Interface procedure is called from Main
872 -- procedure. This cleans up the gl_interface table.
873 -- ------------------------------------------------------------------
874 PROCEDURE Cleanup_Gl_Interface IS
875 l_module_name VARCHAR2(200) ;
876 BEGIN
877 l_module_name := g_module_name || 'Cleanup_Gl_Interface';
878 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
879 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' IN THE CLEANUP_GL_INTERFACE PROCEDURE....');
880 END IF;
881 -- Delete from Gl_Interface table
882 DELETE FROM Gl_Interface
883 WHERE user_je_source_name = 'Year End Close'
884 AND user_je_category_name = 'Federal Carry Forward'
885 AND ledger_id = vp_sob_id;
886 EXCEPTION
887 WHEN OTHERS THEN
888 vp_retcode := 2;
889 vp_errbuf := SQLERRM || '-- Error in Cleanup_Gl_Interface procedure.';
890 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
891 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name
892 ,vp_errbuf);
893 END IF;
894 RETURN;
895 END Cleanup_Gl_Interface;
896 -- + --------------------------------------------------------------------- +
897 -- + Function to convert a cloumn value to Number +
898 -- + This function returns Number if it a number else NULL +
899 -- + --------------------------------------------------------------------- +
900 FUNCTION Convert_To_Num ( p_instr VARCHAR2) RETURN NUMBER
901 IS
902 l_outnum number;
903 BEGIN
904 IF (p_instr IS NULL) THEN
905 RETURN NULL;
906 END IF;
907 l_outnum := p_instr;
908 return l_outnum;
909 EXCEPTION
910 WHEN OTHERS THEN
911 l_outnum:=0;
912 return l_outnum;
913 END Convert_To_Num;
914
915 -- ------------------------------------------------------------------
916 -- Function Check_bal_seg_value
917 -- ------------------------------------------------------------------
918 -- BSV's are not always assigned to ledgers. Therefore we should not
919 -- enforce BSV assignemnt if there is no BSV flex value set
920 -- is assigned to a ledger.
921 -- IF bal_seg_value_option_code column value in GL_LEDGER table is
922 -- 'A' that means all BSV's are valid. If the column is 'I',
923 -- then some BSV's are valid.
924 -- ------------------------------------------------------------------
925 FUNCTION Check_bal_seg_value(Vp_fund_value VARCHAR2 ,
926 Vp_sob_id NUMBER,
927 Vp_bal_seg_val_opt_code VARCHAR)
928 RETURN VARCHAR IS
929
930 l_module_name VARCHAR2(200) ;
931 vl_valid_fund VARCHAR2(1);
932 BEGIN
933 l_module_name := g_module_name || 'Check_bal_seg_value ';
934 vl_valid_fund := 'N';
935
936 IF Vp_bal_seg_val_opt_code <> 'I' THEN
937 RETURN 'Y';
938 END IF;
939
940 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
941 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
942 l_module_name,
943 ' IN CHECK_BAL_SEG_VALUE PROCEDURE '||
944 'with the following Parameters passed to the process:');
945 END IF;
946 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
947 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
948 l_module_name,
949 ' FUND VALUE= '||VP_FUND_VALUE);
950 END IF;
951
952 BEGIN
953 SELECT 'Y'
954 INTO vl_valid_fund
955 FROM gl_ledger_segment_values glsv
956 WHERE glsv.ledger_id = vp_sob_id
957 AND glsv.segment_type_code (+) = 'B'
958 AND NVL(glsv.status_code (+), 'X') <> 'I'
959 AND NVL(glsv.start_date (+),TO_DATE('1950/01/01','YYYY/MM/DD'))
960 <= NVL(sysdate,TO_DATE('9999/12/31','YYYY/MM/DD'))
961 AND NVL(glsv.end_date (+),TO_DATE('9999/12/31','YYYY/MM/DD'))
962 >= NVL(sysdate, TO_DATE('1950/01/01','YYYY/MM/DD'))
963 AND glsv.segment_value = Vp_fund_value;
964
965 EXCEPTION
966 WHEN NO_DATA_FOUND THEN
967 vl_valid_fund := 'N' ;
968 END ;
969 RETURN vl_valid_fund ;
970
971 EXCEPTION
972 WHEN OTHERS THEN
973 vp_retcode := 2 ;
974 vp_errbuf := SQLERRM ||
975 ' -- Error in Check Check_bal_seg_value procedure.' ;
976 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
977 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED,
978 l_module_name,
979 'When Others Exception ' || vp_errbuf );
980 END IF;
981 RETURN 'N';
982 END Check_bal_seg_value;
983
984 BEGIN
985 g_module_name := 'fv.plsql.Fv_Ye_Carryforward.';
986 END Fv_Ye_Carryforward;
[Home] [Help]