[Home] [Help]
PACKAGE BODY: APPS.FV_PURGED_TREASURY_SYMBOLS
Source
1 package body FV_PURGED_TREASURY_SYMBOLS as
2 /* $Header: FVXPRTSB.pls 120.2 2006/07/04 06:12:32 ckappaga noship $ */
3 procedure populate_history_tab(tres_id number,v_flag varchar2); --- private subprogramme
4 function inquire_history_tab(tres_id number) return boolean; --- private subprogramme
5 procedure update_history_tab(tres_id number,v_flag varchar2); --- private subprogramme
6 procedure ins_delete_treasury_symbols; --- private subprogramme
7 procedure delete_treasury_symbols; --- private subprogramme
8 procedure get_period_year(p_year out nocopy number); --- private subprogramme
9 procedure clean_up; --- private subprogramme
10
11 procedure calculate_gl_balances(p_fund_value in varchar2,p_balance out nocopy number,p_cnt out nocopy number); --- private subprogramme
12
13 /*----------------------------+
14 Private Global Variables
15 +-----------------------------*/
16
17 gbl_error_code Number := 0;
18 gbl_error_buf Varchar2(200);
19 gbl_account_id gl_code_combinations.chart_of_accounts_id%type;
20 gbl_bal_segment_name varchar2(30);
21 gbl_acc_segment_name varchar2(30);
22 gbl_set_of_books_id Gl_ledgers_public_v.ledger_id%TYPE;
23 gbl_last_upd_date DATE;
24 gbl_last_update_by number(15);
25 gbl_last_update_log number(15);
26 gbl_creation_date date;
27 gbl_created_by number(15);
28 gbl_cancellation_date date;
29 gbl_treasury_symbol varchar2(100);
30 gbl_treasury_symbol_id fv_treasury_symbols.treasury_symbol_id%type;
31 gbl_time_frame varchar2(100);
32 gbl_year_established number;
33 gbl_new_established_year number;
34 gbl_request_id Number;
35 gbl_prelim_req_id Number;
36 gbl_period_year gl_periods.period_year%type;
37 gbl_period_set_name Gl_Ledgers_public_v.period_set_name%type;
38 g_module_name VARCHAR2(100) := 'fv.plsql.fv_purged_treasury_symbols.';
39 gbl_cursor_cnt NUMBER := 0;
40 gbl_acct_segment_num NUMBER;
41 gbl_flex_value_set_id NUMBER;
42 --gbl_purge_cancel_flg VARCHAR2(1);
43
44
45 /*-----------------------------------------------------------------+
46 Cursor to get Treasury symbols based on the Input Parameters Passed.
47 +------------------------------------------------------------------*/
48
49 cursor c_treasury_symbols (v_treasury_symbol varchar2,v_sob number,v_time_frame IN VARCHAR2 ,
50 n_year_established IN NUMBER,
51 d_cancellation_date IN DATE ,
52 new_established_year IN NUMBER) is
53 select treasury_symbol_id,treasury_symbol,
54 set_of_books_id,
55 fund_group_code
56 from fv_treasury_symbols
57 where set_of_books_id = gbl_set_of_books_id
58 and treasury_symbol= nvl(v_treasury_symbol,treasury_symbol)
59 and time_frame = nvl(v_time_frame,time_frame)
60 and established_fiscal_yr = NVL(n_year_established,established_fiscal_yr)
61 and nvl(to_char(trunc(cancellation_date)),'0') = NVL(to_char(gbl_cancellation_date),nvl(to_char(TRUNC(cancellation_date)),0))
62 and trunc(cancellation_date) < trunc(sysdate) - 365;
63
64 /*-----------------------------------------------------+
65 Cursor to get The Fund Values of the Treasury Symbols
66 selected from Cursor "c_treasury_symbols"
67 +-------------------------------------------------------+*/
68 cursor c_fund_parameters(tres_id number, v_sob number) is
69 select fund_parameter_id,fund_value,
70 treasury_symbol,set_of_books_id,
71 fund_group_code
72 from Fv_fund_parameters
73 where treasury_symbol_id = tres_id
74 and set_of_books_id = v_sob;
75
76 /*-----------------------------------------------------+
77 Cursor to get The Natural Account Segmant Values.
78 +-------------------------------------------------------+*/
79
80 CURSOR parent_child_rollups_cur(p_value_set_id NUMBER) IS
81 SELECT flex_value
82 FROM fnd_flex_values
83 WHERE flex_value_set_id = p_value_set_id
84 and flex_value NOT IN('4350','4201')
85 AND NOT EXISTS ( SELECT 1 from fnd_flex_value_hierarchies
86 where Flex_value_set_id = p_value_set_id
87 AND flex_value BETWEEN child_flex_value_low
88 AND child_flex_value_high
89 AND PARENT_FLEX_VALUE in ('4350','4201'))
90 AND summary_flag = 'N'
91 ORDER BY 1 ;
92
93 -- *---------------------------------------------------------------------* --
94 -- * PROCEDURE MAIN * --
95 -- * ---------------- * --
96 -- * Main procedure of the package, which is called for execution from * --
97 -- * Purge Treasury Symbols cincurrent programme. * --
98 -- * It in turn calls all the other procedures to achieve the final * --
99 -- * result. * --
100 -- *---------------------------------------------------------------------* --
101 procedure MAIN(errbuf OUT NOCOPY VARCHAR2,
102 retcode OUT NOCOPY VARCHAR2,
103 x_run_mode IN VARCHAR2,
104 v_treasury_symbol IN VARCHAR2 DEFAULT NULL,
105 v_time_frame IN VARCHAR2 DEFAULT NULL ,
106 n_year_established IN NUMBER DEFAULT NULL ,
107 p_sob Gl_Ledgers_public_v.ledger_id%TYPE DEFAULT NULL,
108 d_cancellation_date IN VARCHAR2 DEFAULT NULL ,
109 new_established_year IN NUMBER,
110 p_dummy IN NUMBER,
111 prelim_req_id IN NUMBER DEFAULT NULL) IS
112 v_accounts_id gl_ledgers_public_v.chart_of_accounts_id%type;
113 v_application_name fnd_segment_attribute_values.application_column_name%type;
114 n_balance NUMBER;
115 n_cnt NUMBER;
116 l_segment_status BOOLEAN;
117 e_invalid_bal_segment EXCEPTION;
118 v_open_flag VARCHAR2(1) :='N';
119 v_result BOOLEAN;
120 v_req_status BOOLEAN;
121 v_rphase VARCHAR2(80);
122 v_rstatus VARCHAR2(80);
123 v_wait_req_status boolean;
124 v_dphase VARCHAR2(30);
125 v_dstatus VARCHAR2(30);
126 v_message VARCHAR2(240);
127 e_no_data EXCEPTION;
128 l_module_name VARCHAR2(200) := g_module_name || 'MAIN';
129 l_request_id NUMBER;
130 BEGIN
131 gbl_set_of_books_id := p_sob;
132 gbl_last_upd_date := sysdate;
133 gbl_last_update_by := fnd_global.user_id;
134 gbl_last_update_log := fnd_global.login_id;
135 gbl_creation_date := sysdate;
136 gbl_created_by := fnd_global.user_id;
137 gbl_cancellation_date := trunc(FND_DATE.CANONICAL_TO_DATE(d_cancellation_date));
138 gbl_treasury_symbol := v_treasury_symbol ;
139 gbl_time_frame := v_time_frame ;
140 gbl_year_established := n_year_established ;
141 gbl_new_established_year := new_established_year ;
142 gbl_request_id := fnd_global.conc_request_id;
143 ---gbl_purge_cancel_flg := purge_cancel_flg;
144 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' Inside Main');
145
146 SELECT chart_of_accounts_id, period_set_name
147 INTO gbl_account_id , gbl_period_set_name
148 FROM gl_ledgers_public_v GL
149 WHERE gl.ledger_id = gbl_set_of_books_id ;
150
151 /*--------------------------------------------------+
152 To get segment Name
153 +---------------------------------------------------*/
154
155 BEGIN
156 l_segment_status := FND_FLEX_APIS.get_segment_column
157 (101, 'GL#', gbl_account_id, 'GL_BALANCING',
158 gbl_bal_segment_name) ;
159 IF l_segment_status = FALSE
160 THEN
161 raise e_invalid_bal_segment;
162 END IF;
163 exception
164 WHEN e_Invalid_Bal_segment THEN
165 retcode := 2 ;
166 errbuf := 'GET SEGMENT NAME - Error Reading Balancing Segments' ;
167 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,errbuf);
168 return;
169 END;
170 ------To get the Accounting Segment Name ------------------------
171 BEGIN
172 l_segment_status := FND_FLEX_APIS.get_segment_column
173 (101, 'GL#', gbl_account_id, 'GL_ACCOUNT',
174 gbl_acc_segment_name) ;
175 IF l_segment_status = FALSE
176 THEN
177 raise e_invalid_bal_segment;
178 END IF;
179 exception
180 WHEN e_Invalid_Bal_segment THEN
181 retcode := 2 ;
182 errbuf := 'GET SEGMENT NAME - Error Reading accounting Segments' ;
183 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,errbuf);
184 return;
185 END;
186
187 ----To get the Accounting Segment Number---------
188 BEGIN
189 l_segment_status := FND_FLEX_APIS.GET_QUALIFIER_SEGNUM
190 (101,
191 'GL#',
192 gbl_account_id,
193 'GL_ACCOUNT',
194 gbl_acct_segment_num) ;
195 IF l_segment_status = FALSE
196 THEN
197 raise e_invalid_bal_segment;
198 END IF;
199 exception
200 WHEN e_Invalid_Bal_segment THEN
201 retcode := 2 ;
202 errbuf := 'GET SEGMENT NUMBER- Error Reading accounting Segments' ;
203 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,errbuf);
204 return;
205 END;
206
207
208
209 SELECT flex_value_set_id
210 INTO gbl_flex_value_set_id
211 FROM fnd_id_flex_segments
212 WHERE application_id = 101
213 AND id_flex_code = 'GL#'
214 AND id_flex_num = gbl_account_id
215 AND segment_num = gbl_acct_segment_num ;
216 -------------------------------------------------------------------------
217 gbl_cursor_cnt := 0;
218 if x_run_mode = 'P' then
219 for c1_treasury_symb in c_treasury_symbols(gbl_treasury_symbol,
220 gbl_set_of_books_id,
221 gbl_time_frame ,
222 gbl_year_established ,
223 gbl_cancellation_date ,
224 gbl_new_established_year )---- Open first cursor-----
225 loop
226 gbl_cursor_cnt := 1;
227 gbl_treasury_symbol_id := c1_treasury_symb.treasury_symbol_id;
228 v_open_flag := 'N';
229 v_result := inquire_history_tab(c1_treasury_symb.treasury_symbol_id );
230
231 for c1_fund_param IN c_fund_parameters(c1_treasury_symb.treasury_symbol_id ,gbl_set_of_books_id )----open second cursor------
232 Loop
233 gbl_cursor_cnt := 2;
234
235 /*-----------------------------------------------------------+
236 To check balances of fund against Treasury symbols
237 +------------------------------------------------------------*/
238 if gbl_error_code = 0 then
239 calculate_gl_balances(c1_fund_param.fund_value,n_balance,n_cnt);
240 else
241 exit;
242 end if;
243 -------------------------------------------------------------------------
244 if gbl_error_code <> 0 then
245 exit;
246 end if;
247 IF (n_balance <> 0 ) or (n_cnt > 0) then
248 v_open_flag := 'Y';
249 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Balance or Unposted Transactions exists for Fund Values against Treasury Symbol: ' || c1_treasury_symb.treasury_symbol );
250
251 exit;
252 END IF;
253 end loop; ----End of second cursor------
254 if gbl_error_code <> 0 then
255 exit;
256 end if;
257 if gbl_cursor_cnt =1 then
258 errbuf := ' No Fund value is defined for Treasury Symbol:'||c1_treasury_symb.treasury_symbol;
259 retcode := 1;
260 ---- FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name ,gbl_error_buf);
261 end if;
262 if v_result = false then
263 if gbl_error_code = 0 then
264 Populate_history_tab(c1_treasury_symb.treasury_symbol_id ,v_open_flag);
265 else
266 exit;
267 end if;
268 elsif v_result = true then
269 if gbl_error_code = 0 then
270 update_history_tab(c1_treasury_symb.treasury_symbol_id ,v_open_flag);
271 else
272 exit;
273 end if;
274 end if;
275 end loop; ---- End Of first cursor-----
276 elsif x_run_mode = 'F' then
277 gbl_prelim_req_id := prelim_req_id; ---- Request id populated during 'P' Mode ------
278 Ins_delete_treasury_symbols;
279 end if;
280
281 if gbl_cursor_cnt = 0 then
282 raise e_no_data;
283 end if;
284
285 IF (gbl_error_code <> 0) THEN
286 errbuf := gbl_error_buf;
287 retcode := 2;
288 ROLLBACK;
289 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_ERROR, l_module_name || gbl_error_code,gbl_error_buf);
290 return;
291 ELSE
292 COMMIT;
293 /*---------------------------------------------------------------+
294 Calling concurrent request to show the result in Report
295 +----------------------------------------------------------------*/
296 l_request_id := FND_REQUEST.SUBMIT_REQUEST( 'FV',
297 'FVPGDTSR',
298 null,
299 null,
300 FALSE,
301 x_run_mode,
302 gbl_request_id,
303 gbl_set_of_books_id,
304 gbl_treasury_symbol,
305 gbl_time_frame,
306 gbl_year_established,
307 gbl_new_established_year,
308 gbl_cancellation_date) ;
309 IF l_request_id = 0 then
310 gbl_error_code := '2';
311 gbl_error_buf := 'Cannot submit Purge Treasury Symbol Report';
312 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,gbl_error_buf);
313 ELSE
314 COMMIT;
315 END IF;
316
317 v_wait_req_status := FND_CONCURRENT.WAIT_FOR_REQUEST(request_id => l_request_id,
318 interval => 20,
319 max_wait => 0,
320 phase => v_rphase,
321 status => v_rstatus,
322 dev_phase => v_dphase,
323 dev_status => v_dstatus,
324 message => v_message);
325 IF v_wait_req_status = FALSE THEN
326 gbl_error_buf := 'Cannot wait for the status of Purge Treasury Symbols Report';
327 retcode := -1;
328 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.error4', gbl_error_buf) ;
329 return;
330 END IF;
331
332 v_req_status := FND_CONCURRENT.GET_REQUEST_STATUS(request_id => l_request_id,
333 appl_shortname => 'FV',
334 program => null,
335 phase => v_rphase,
336 status => v_rstatus,
337 dev_phase => v_dphase,
338 dev_status => v_dstatus,
339 message => v_message);
340
341 IF (x_run_mode = 'F' and v_dphase = 'COMPLETE' and v_dstatus = 'NORMAL') then
342 delete_treasury_symbols; --- procedure to delete treasury symbols --------
343 clean_up; --- procedure to clean the junk data from history tables ------
344 IF gbl_error_code <> 0 then
345 errbuf := gbl_error_buf;
346 retcode := 2;
347 ROLLBACK;
348 update_history_tab(null,'R');
349 COMMIT;
350 ELSE
351 COMMIT;
352 END IF;
353 ----- Rollback all the changes done during updation -----------------
354 ELSIF
355 (x_run_mode = 'F' and v_dphase = 'COMPLETE' and v_dstatus <>'NORMAL') then
356 update_history_tab(null,'R');
357 COMMIT;
358 --- ELSIF (x_run_mode = 'P' and v_dstatus <>'NORMAL') then
359
360 ---Error Occured while running a request ---------
361 END IF;
362 END IF;
363
364 EXCEPTION
365 WHEN e_no_data then
366 errbuf := ' No Data Found for Treasury Symbol: '||gbl_treasury_symbol;
367 retcode := 2;
368 return;
369 END MAIN;
370
371 -- *----------------------------------------------------------------------* --
372 -- * PROCEDURE POPULATE_HISTORY_TAB * --
373 -- * ------------------------------- * --
374 -- * Called during Preliminary Mode to populate history tables * --
375 -- *----------------------------------------------------------------------* --
376 Procedure populate_history_tab(tres_id number,v_flag varchar2) is
377 l_module_name VARCHAR2(200):='POPULATE_HISTORY_TAB';
378 begin
379 BEGIN
380 ---dbms_output.put_line('inside populate_history_tab');
381 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_statement, l_module_name,' inside populate_history_tab');
382 insert ALL into fv_treasury_symbols_history
383 (treasury_symbol_id,
384 treasury_symbol,
385 set_of_books_id,
386 sf224_type_code,
387 fund_group_code,
388 time_frame,
389 years_available,
390 established_fiscal_yr,
391 expiration_date,
392 cancellation_date,
393 department_id,
394 last_update_date,
395 last_updated_by,
396 last_update_login,
397 creation_date,
398 created_by,
399 federal_acct_symbol_id,
400 dept_transfer,
401 request_id,
402 open_flag)
403 select treasury_symbol_id,
404 treasury_symbol,
405 set_of_books_id,
406 sf224_type_code,
407 fund_group_code,
408 time_frame,
409 years_available,
410 established_fiscal_yr,
411 expiration_date,
412 cancellation_date,
413 department_id,
414 gbl_last_upd_date,
415 nvl(gbl_last_update_by,1),
416 nvl(gbl_last_update_log,1),
417 gbl_creation_date,
418 nvl(gbl_created_by,1),
419 federal_acct_symbol_id,
420 dept_transfer,
421 gbl_request_id,
422 populate_history_tab.v_flag
423 from fv_treasury_symbols
424 where treasury_symbol_id = tres_id
425 and set_of_books_id = gbl_set_of_books_id;
426 EXCEPTION
427 WHEN NO_DATA_FOUND THEN
428 gbl_error_code := SQLCODE;
429 gbl_error_buf := SQLERRM ||'-- Error No Data Found in populate_history_tab while populating symbol history table';
430 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,gbl_error_buf);
431 return;
432 WHEN OTHERS THEN
433 gbl_error_code := SQLCODE;
434 gbl_error_buf := SQLERRM ||
435 ' -- Error in populate_history_tab when Inserting';
436 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,gbl_error_buf);
437 return;
438 END;
439 BEGIN
440 insert ALL into fv_fund_parameters_history
441 (fund_parameter_id,
442 fund_value,
443 treasury_symbol,
444 set_of_books_id,
445 fund_group_code,
446 fund_category,
447 fund_time_frame,
448 sf224_type_code,
449 last_update_date,
450 last_updated_by,
451 last_update_login,
452 creation_date,
453 created_by,
454 established_fiscal_yr,
455 treasury_symbol_id,
456 budget_authority,
457 unliquid_commitments,
458 unliquid_obligations,
459 expended_amount,
460 red_status,
461 prior_year_recoveries)
462 select fund_parameter_id,
463 fund_value,
464 treasury_symbol,
465 set_of_books_id,
466 fund_group_code,
467 fund_category,
468 fund_time_frame,
469 sf224_type_code,
470 gbl_last_upd_date,
471 nvl(gbl_last_update_by,1),
472 nvl(gbl_last_update_log,1),
473 gbl_creation_date,
474 nvl(gbl_created_by,1),
475 established_fiscal_yr,
476 treasury_symbol_id,
477 budget_authority,
478 unliquid_commitments,
479 unliquid_obligations,
480 expended_amount,
481 red_status,
482 prior_year_recoveries
483 from Fv_fund_parameters
484 where treasury_symbol_id = tres_id
485 and set_of_books_id = gbl_set_of_books_id;
486 EXCEPTION
487 WHEN NO_DATA_FOUND THEN
488 --gbl_error_code := SQLCODE;
489 --- dbms_output.put_line('No fund found for treasury symbol:'||gbl_treasury_symbol_id);
490 gbl_error_buf := '--No Fund value defined for Treasury_symbol : '||gbl_treasury_symbol_id;
491 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,gbl_error_buf);
492 return;
493 WHEN OTHERS THEN
494 gbl_error_code := SQLCODE;
495 gbl_error_buf := SQLERRM ||
496 ' -- Error in populate_history_tab while Inserting';
497 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,gbl_error_buf);
498 return;
499 END;
500 end populate_history_tab;
501
502 -- *----------------------------------------------------------------------* --
503 -- * PROCEDURE INQUIRE_HISTORY_TAB * --
504 -- * ------------------------------- * --
505 -- * Called during Preliminary Mode, to find if there is any treasury * --
506 -- * symbol already there in History Table * --
507 -- *----------------------------------------------------------------------* --
508
509 -------------------------------------------------------------------------------------------------------------------
510 Function inquire_history_tab(tres_id number) return boolean is
511 n_cnt number;
512 l_module_name varchar2(200) := g_module_name || 'INQUIRE_HISTORY_TAB';
513 begin
514 select count(1) into n_cnt
515 from fv_treasury_symbols_history
516 where treasury_symbol_id = tres_id
517 and set_of_books_id = gbl_set_of_books_id
518 and date_purged is null;
519 if n_cnt > 0 then
520 return true;
521 else
522 return false;
523 end if;
524 end inquire_history_tab;
525
526 -- *----------------------------------------------------------------------* --
527 -- * PROCEDURE UPDATE_HISTORY_TAB * --
528 -- * ------------------------------- * --
529 -- * Called during Preliminary Mode or in Final Mode when error occured, * --
530 -- * update the open flag for treasury * --
531 -- * symbols already there in History Table * --
532 -- *----------------------------------------------------------------------* --
533
534 procedure update_history_tab(tres_id number,v_flag varchar2) is
535 l_module_name VARCHAR2(200) := g_module_name || 'UPDATE_HISTORY_TAB';
536 begin
537 ----dbms_output.put_line(' inside update_history_tab');
538 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_statement, l_module_name,' inside update_history_tab');
539 IF v_flag in ('Y','N') then
540 ---dbms_output.put_line(' inside update_history_tab updating for flag <>d');
541 update fv_treasury_symbols_history
542 set open_flag = v_flag ,
543 request_id = gbl_request_id,
544 last_update_date = sysdate,
545 last_updated_by = gbl_last_update_by,
546 last_update_login = gbl_last_update_log,
547 creation_date = sysdate,
548 created_by = gbl_created_by
549 where treasury_symbol_id = tres_id
550 and set_of_books_id = gbl_set_of_books_id;
551 ELSIF v_flag = 'D' and gbl_error_code = 0 then ---called while deleting treasury symbols ---------
552 ---dbms_output.put_line(' inside update_history_tab updating for flag = d');
553 update fv_treasury_symbols_history
554 set date_purged = TRUNC(sysdate),
555 request_id = gbl_request_id,
556 last_update_date = sysdate,
557 last_updated_by = gbl_last_update_by,
558 last_update_login = gbl_last_update_log,
559 creation_date = sysdate,
560 created_by = gbl_created_by
561 where treasury_symbol_id = tres_id
562 and set_of_books_id = gbl_set_of_books_id
563 and request_id = gbl_prelim_req_id
564 and date_purged is null;
565 ELSIF v_flag = 'R' then --- called when report runs unsuccessfully thus rollback all the changes-------
566
567 update fv_treasury_symbols_history
568 set date_purged = null,
569 request_id = gbl_prelim_req_id,
570 last_update_date = sysdate,
571 last_updated_by = gbl_last_update_by,
572 last_update_login = gbl_last_update_log,
573 creation_date = sysdate,
574 created_by = gbl_created_by
575 where set_of_books_id = gbl_set_of_books_id
576 and request_id = gbl_request_id
577 and nvl(do_not_purge_flag,'N') = 'N';
578 END IF;
579 EXCEPTION
580 WHEN NO_DATA_FOUND THEN
581 gbl_error_code := SQLCODE;
582 gbl_error_buf := SQLERRM;
583 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.final_exception',gbl_error_buf);
584 return;
585 WHEN OTHERS THEN
586 gbl_error_code := SQLCODE;
587 gbl_error_buf := SQLERRM;
588 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',gbl_error_buf);
589 return;
590 end update_history_tab;
591
592 -- *----------------------------------------------------------------------* --
593 -- * PROCEDURE INS_DELETE_TREASURY_SYMBOLS * --
594 -- * ------------------------------------- * --
595 -- * Called during Final Mode check Balances and update the History Tables* --
596 -- *----------------------------------------------------------------------* --
597
598 procedure ins_delete_treasury_symbols is
599 n_balance number;
600 n_cnt number;
601 v_open_flag varchar2(1);
602 l_module_name VARCHAR2(200):='INS_DELETE_TREASURY_SYMBOLS';
603 cursor c1_treas_symbol is
604 select treasury_symbol,treasury_symbol_id,set_of_books_id,do_not_purge_flag,open_flag
605 from fv_treasury_symbols_history fts
606 where set_of_books_id = gbl_set_of_books_id
607 and treasury_symbol= nvl(gbl_treasury_symbol,treasury_symbol)
608 and time_frame = nvl(gbl_time_frame,time_frame)
609 and established_fiscal_yr = NVL(gbl_year_established,established_fiscal_yr)
610 and nvl(to_char(trunc(cancellation_date)),'0') = NVL(to_char(gbl_cancellation_date),nvl(to_char(TRUNC(cancellation_date)),0))
611 and date_purged is null
612 and nvl(do_not_purge_flag,'N') = 'N'
613 and request_id = gbl_prelim_req_id;
614 ---and open_flag in('N');
615 /*cursor c2_fund_param_hist (tres_id number ) is
616 select fund_value
617 from fv_fund_parameters_history
618 where set_of_books_id = gbl_set_of_books_id
619 and treasury_symbol_id = tres_id;*/
620 BEGIN
621 --dbms_output.put_line('inside ins_delete_treasury_symbols');
622 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_statement, l_module_name,' Inside ins_delete_treasury_symbols');
623 gbl_cursor_cnt := 0;
624 for c1 in c1_treas_symbol
625 loop
626 gbl_cursor_cnt := 2;
627 gbl_treasury_symbol_id := c1.treasury_symbol_id;
628 IF c1.open_flag = 'Y' then
629 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,'Unable To Purge Treasury Symbol '||c1.treasury_symbol ||' as Balances or Unposted Transactions found.');
630 -- for c2 in c2_fund_param_hist(c1.treasury_symbol_id)
631 -- loop
632 --- calculate_gl_balances(c2.fund_value,n_balance,n_cnt);
633
634 -----------------------------------------------------------------------------------------------------
635 --- if gbl_error_code <> 0 then
636 --- exit;
637 --- end if;
638 --- IF (n_balance > 0 ) or (n_cnt > 0) then
639 --- v_open_flag := 'Y';
640 --ELSE
641 -- v_open_flag := 'N';
642 --- exit;
643 --- END IF;
644 --- end loop;
645 ELSE
646 if gbl_error_code <> 0 then
647 exit;
648 end if;
649 ---- if v_open_flag = 'Y' then
650 ---- update_history_tab(c1.treasury_symbol_id,v_open_flag);
651 ----- elsif v_open_flag = 'N' and gbl_error_code = 0 then
652 ---delete_treasury_symbols(c1.treasury_symbol_id);
653 update_history_tab(c1.treasury_symbol_id,'D');
654 ----- end if;
655 END IF;
656 end loop;
657 EXCEPTION
658 WHEN OTHERS THEN
659 gbl_error_code := SQLCODE;
660 gbl_error_buf := SQLERRM ||
661 ' -- Error in Delete_treasury_symbols when deleting treasury symbol' ;
662 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error1',gbl_error_buf);
663 END ins_delete_treasury_symbols;
664
665 -- *----------------------------------------------------------------------* --
666 -- * PROCEDURE CALCULATE_GL_BALANCES * --
667 -- * ------------------------------- * --
668 -- * Called during Preliminary Mode and Final Mode to claculate banaces * --
669 -- * and Transaction for particular fund value. * --
670 -- *----------------------------------------------------------------------* --
671
672 procedure calculate_gl_balances(p_fund_value in varchar2,p_balance out nocopy number,p_cnt out nocopy number)
673 IS
674 l_module_name VARCHAR2(200) := g_module_name || 'CALCULATE_GL_BALANCES';
675 v_query varchar2(3000);
676 prv_year number;
677 curr_year number;
678 s_date gl_period_statuses.start_date%type;
679 e_date gl_period_statuses.end_date%type;
680 ---v_flex_value varchar2(150);
681 BEGIN
682 get_period_year(curr_year);
683
684
685 prv_year := curr_year -1;
686 BEGIN
687 FOR I IN parent_child_rollups_cur(gbl_flex_value_set_id)
688 LOOP
689 v_query := 'SELECT NVL(SUM((period_net_dr + begin_balance_dr) - (period_net_cr + begin_balance_cr)),0)
690 FROM gl_code_combinations gcc,gl_balances gb
691 WHERE gb.code_combination_id = gcc.code_combination_id
692 AND gcc.chart_of_accounts_id =' || gbl_account_id ||
693 'AND (GB.PERIOD_NUM,GB.PERIOD_YEAR) IN (SELECT MAX(PERIOD_NUM),PERIOD_YEAR
694 FROM gl_period_statuses
695 WHERE period_year IN(' || prv_year ||','|| curr_year ||')
696 AND application_id ='|| 101||'
697 AND closing_status <>'||'''F'''||
698 'AND closing_status <>'||'''N'''||
699 'AND adjustment_period_flag ='||'''N'''||
700 'AND ledger_id ='|| gbl_set_of_books_id ||
701 'GROUP BY PERIOD_YEAR)
702 AND GB.TEMPLATE_ID IS NULL
703 AND GB.LEDGER_ID =' || gbl_set_of_books_id ||
704 'AND gb.actual_flag ='||'''A'''||
705 'AND gcc.'||gbl_bal_segment_name||' = :P_fund_value
706 AND gcc.'||gbl_acc_segment_name||' = :P_acc_value
707 AND gcc.enabled_flag='||'''Y''';
708
709 execute immediate v_query into p_balance using p_fund_value,I.Flex_value;
710 IF p_balance <> 0 then
711 exit;
712 END IF;
713 END LOOP;
714 EXCEPTION
715 WHEN NO_DATA_FOUND THEN
716 p_balance := 0;
717 WHEN OTHERS THEN
718 gbl_error_code := SQLCODE;
719 gbl_error_buf := SQLERRM ||
720 ' -- Error in Calculate_Gl_Balances when finding balances and transactions for fund value';
721 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error1',gbl_error_buf);
722 END;
723 -------------------------------------------------------------------------------------
724 ------***** To check if there any transaction unposted against the fund value *****------------
725 BEGIN
726
727 SELECT min(start_date), max(end_date)
728 INTO s_date,e_date
729 FROM gl_period_statuses
730 WHERE period_year = curr_year
731 AND application_id = 101
732 AND closing_status <> 'F'
733 AND closing_status <> 'N'
734 AND adjustment_period_flag = 'N'
735 AND ledger_id = gbl_set_of_books_id;
736 v_query := 'SELECT count(1) FROM gl_je_lines gjl,gl_code_combinations gcc
737 WHERE gjl.ledger_id =' || gbl_set_of_books_id ||
738 'AND gjl.code_combination_id = gcc.code_combination_id
739 AND gcc.chart_of_accounts_id ='|| gbl_account_id ||
740 'AND gcc.'||gbl_bal_segment_name ||' = :p
741 AND gcc.enabled_flag = '||'''Y'''||
742 'AND gcc.account_type ='||'''A'''||
743 'AND gcc.'||gbl_acc_segment_name||' not like'||'''4350%'''||
744 'AND gcc.'||gbl_acc_segment_name||' not like'||'''4201%'''||
745 'AND effective_date between :s_date
746 AND :e_date
747 AND gjl.status = '||'''U''';
748
749 execute immediate v_query into p_cnt using p_fund_value,s_date,e_date;
750
751 EXCEPTION
752 WHEN NO_DATA_FOUND THEN
753
754 p_cnt := 0;
755 WHEN OTHERS THEN
756 gbl_error_code := SQLCODE;
757 gbl_error_buf := SQLERRM ||
758 ' -- Error in Calculate_Gl_Balances while finding transactions for fund value';
759 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error1',gbl_error_buf);
760 END;
761 END calculate_gl_balances;
762
763 -- *----------------------------------------------------------------------* --
764 -- * PROCEDURE GET_PERIOD_YEAR * --
765 -- * ------------------------------- * --
766 -- * To get the running Fiscal year * --
767 -- *----------------------------------------------------------------------* --
768
769 procedure get_period_year(p_year out nocopy number)
770 IS
771 l_module_name VARCHAR2(200):= 'GET_PERIOD_YEAR';
772 BEGIN
773
774 --- sysdate := megan date;
775 SELECT period_year
776 INTO p_year
777 FROM gl_periods
778 WHERE period_set_name = gbl_period_set_name
779 AND adjustment_period_flag = 'N'
780 GROUP BY PERIOD_YEAR
781 HAVING sysdate between min(start_date) and max(end_date);
782
783 --p_year := 2005;
784
785 EXCEPTION
786 WHEN NO_DATA_FOUND THEN
787
788 gbl_error_code := SQLCODE;
789 gbl_error_buf := SQLERRM ||
790 ' -- Error in get_period_year finding current fiscal year';
791 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.error1',gbl_error_buf);
792
793 WHEN OTHERS THEN
794 gbl_error_code := SQLCODE;
795 gbl_error_buf := SQLERRM ||
796 ' -- Error in Calculate_Gl_Balances when finding balances and transactions for fund value';
797 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error2',gbl_error_buf);
798 END get_period_year;
799
800 -- *----------------------------------------------------------------------* --
801 -- * PROCEDURE DELETE_TREASURY_SYMBOLS * --
802 -- * --------------------------------- * --
803 -- * Called in Final Mode to Purge The Treasury Symbols * --
804 -- *----------------------------------------------------------------------* --
805 procedure delete_treasury_symbols IS
806
807 l_module_name VARCHAR2(200):= 'DELETE_TREASURY_SYMBOLS';
808
809 begin
810 BEGIN
811 delete from fv_fund_parameters ffp
812 where exists ( select treasury_symbol_id
813 from fv_treasury_symbols_history fts
814 where fts.set_of_books_id = gbl_set_of_books_id
815 and fts.treasury_symbol_id = ffp.treasury_symbol_id
816 and fts.request_id = gbl_request_id
817 and fts.date_purged is not null)
818
819 and ffp.set_of_books_id = gbl_set_of_books_id;
820 EXCEPTION
821 WHEN NO_DATA_FOUND THEN
822 --gbl_error_code := SQLCODE;
823 gbl_error_buf := SQLERRM ||
824 ' -- Error no-data-found of fund value in delete_treasury_symbols for treasury symbol '||gbl_treasury_symbol_id;
825 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error1',gbl_error_buf);
826 WHEN OTHERS THEN
827 gbl_error_code := SQLCODE;
828 gbl_error_buf := SQLERRM ||
829 ' -- Error in delete_treasury_symbols while deleting Treasury symbols from Fund Parameters';
830 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error1',gbl_error_buf);
831 return;
832 END;
833
834 BEGIN
835 delete from fv_treasury_symbols fts
836 where exists ( select treasury_symbol_id
837 from fv_treasury_symbols_history ftsh
838 where ftsh.set_of_books_id = gbl_set_of_books_id
839 and ftsh.treasury_symbol_id = fts.treasury_symbol_id
840 and ftsh.request_id = gbl_request_id
841 and ftsh.date_purged is not null)
842 and fts.set_of_books_id = gbl_set_of_books_id;
843 EXCEPTION
844 WHEN NO_DATA_FOUND THEN
845 gbl_error_code := SQLCODE;
846 gbl_error_buf := SQLERRM ||
847 ' -- Error no-data-found in delete_treasury_symbols ';
848 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error1',gbl_error_buf);
849 return;
850 WHEN OTHERS THEN
851 gbl_error_code := SQLCODE;
852 gbl_error_buf := SQLERRM ||
853 ' -- Error in delete_treasury_symbols while deleting Treasury symbols';
854 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error2',gbl_error_buf);
855 return;
856 END;
857 end delete_treasury_symbols;
858
859
860 -- *----------------------------------------------------------------------* --
861 -- * PROCEDURE CLEAN_UP * --
862 -- * ------------------- * --
863 -- * Called in Final Mode to Delete all the Junk Data from History Tables * --
864 -- *----------------------------------------------------------------------* --
865 procedure clean_up is
866 l_module_name VARCHAR2(200):= 'CLEAN UP';
867 begin
868 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_statement, l_module_name,' inside clean up procedure');
869
870 delete from fv_fund_parameters_history ffp
871 where ffp.set_of_books_id = gbl_set_of_books_id
872 and exists ( select treasury_symbol_id
873 from fv_treasury_symbols_history fts
874 where fts.set_of_books_id= gbl_set_of_books_id
875 and fts.treasury_symbol_id = ffp.treasury_symbol_id
876 and date_purged is null);
877
878 delete from fv_treasury_symbols_history fts
879 where fts.set_of_books_id= gbl_set_of_books_id
880 and date_purged is null;
881
882
883 EXCEPTION
884
885 WHEN NO_DATA_FOUND THEN
886 NULL;
887 WHEN OTHERS THEN
888 gbl_error_code := SQLCODE;
889 gbl_error_buf := SQLERRM ||
890 ' -- Error in clean_up while clearing history tables';
891 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error2',gbl_error_buf);
892
893 end clean_up;
894 -------------------------------------------------------------------------------------------------------------
895
896 end FV_PURGED_TREASURY_SYMBOLS;