[Home] [Help]
PACKAGE BODY: APPS.FV_PURGED_TREASURY_SYMBOLS
Source
1 package body FV_PURGED_TREASURY_SYMBOLS as
2 /* $Header: FVXPRTSB.pls 120.7.12020000.3 2013/02/13 14:51:26 snama ship $ */
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 p_sob Gl_Ledgers_public_v.ledger_id%TYPE DEFAULT NULL,
104 x_run_mode IN VARCHAR2,
105 v_treasury_symbol IN VARCHAR2 DEFAULT NULL,
106 v_time_frame IN VARCHAR2 DEFAULT NULL ,
107 n_year_established IN NUMBER 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 established_fiscal_yr,
390 expiration_date,
391 cancellation_date,
392 department_id,
393 last_update_date,
394 last_updated_by,
395 last_update_login,
396 creation_date,
397 created_by,
398 federal_acct_symbol_id,
399 dept_transfer,
400 request_id,
401 open_flag,
402 end_year_avail,
403 fund_type,
404 financing_account,
405 facts_reportable_indicator,
406 start_date,
407 receipt_account_indicator,
408 auto_create,
409 availability_type_code,
410 sub_level_prefix_code
411 )
412 select treasury_symbol_id,
413 treasury_symbol,
414 set_of_books_id,
415 sf224_type_code,
416 fund_group_code,
417 time_frame,
418 established_fiscal_yr,
419 expiration_date,
420 cancellation_date,
421 department_id,
422 gbl_last_upd_date,
423 nvl(gbl_last_update_by,1),
424 nvl(gbl_last_update_log,1),
425 gbl_creation_date,
426 nvl(gbl_created_by,1),
427 federal_acct_symbol_id,
428 dept_transfer,
429 gbl_request_id,
430 populate_history_tab.v_flag,
431 end_year_avail,
432 fund_type,
433 financing_account,
434 facts_reportable_indicator,
435 start_date,
436 receipt_account_indicator,
437 NVL(auto_create,'N'),
438 availability_type_code,
439 sub_level_prefix_code
440 from fv_treasury_symbols
441 where treasury_symbol_id = tres_id
442 and set_of_books_id = gbl_set_of_books_id;
443 EXCEPTION
444 WHEN NO_DATA_FOUND THEN
445 gbl_error_code := SQLCODE;
446 gbl_error_buf := SQLERRM ||'-- Error No Data Found in populate_history_tab while populating symbol history table';
447 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,gbl_error_buf);
448 return;
449 WHEN OTHERS THEN
450 gbl_error_code := SQLCODE;
451 gbl_error_buf := SQLERRM ||
452 ' -- Error in populate_history_tab when Inserting';
453 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,gbl_error_buf);
454 return;
455 END;
456 BEGIN
457 insert ALL into fv_fund_parameters_history
458 (fund_parameter_id,
459 fund_value,
460 treasury_symbol,
461 set_of_books_id,
462 fund_group_code,
463 fund_category,
464 fund_time_frame,
465 sf224_type_code,
466 last_update_date,
467 last_updated_by,
468 last_update_login,
469 creation_date,
470 created_by,
471 established_fiscal_yr,
472 treasury_symbol_id,
473 budget_authority,
474 unliquid_commitments,
475 unliquid_obligations,
476 expended_amount,
477 borrowing_source_code,
478 direct_or_reimb_code,
479 def_indef_flag,
480 cust_non_cust,
481 auto_create ,
482 auto_create_fund_value
483 )
484 select fund_parameter_id,
485 fund_value,
486 treasury_symbol,
487 set_of_books_id,
488 fund_group_code,
489 fund_category,
490 fund_time_frame,
491 sf224_type_code,
492 gbl_last_upd_date,
493 nvl(gbl_last_update_by,1),
494 nvl(gbl_last_update_log,1),
495 gbl_creation_date,
496 nvl(gbl_created_by,1),
497 established_fiscal_yr,
498 treasury_symbol_id,
499 budget_authority,
500 unliquid_commitments,
501 unliquid_obligations,
502 expended_amount,
503 borrowing_source_code,
504 direct_or_reimb_code,
505 def_indef_flag,
506 cust_non_cust,
507 NVL(auto_create,'N'),
508 auto_create_fund_value
509
510 from Fv_fund_parameters
511 where treasury_symbol_id = tres_id
512 and set_of_books_id = gbl_set_of_books_id;
513 EXCEPTION
514 WHEN NO_DATA_FOUND THEN
515 --gbl_error_code := SQLCODE;
516 --- dbms_output.put_line('No fund found for treasury symbol:'||gbl_treasury_symbol_id);
517 gbl_error_buf := '--No Fund value defined for Treasury_symbol : '||gbl_treasury_symbol_id;
518 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,gbl_error_buf);
519 return;
520 WHEN OTHERS THEN
521 gbl_error_code := SQLCODE;
522 gbl_error_buf := SQLERRM ||
523 ' -- Error in populate_history_tab while Inserting';
524 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,gbl_error_buf);
525 return;
526 END;
527 end populate_history_tab;
528
529 -- *----------------------------------------------------------------------* --
530 -- * PROCEDURE INQUIRE_HISTORY_TAB * --
531 -- * ------------------------------- * --
532 -- * Called during Preliminary Mode, to find if there is any treasury * --
533 -- * symbol already there in History Table * --
534 -- *----------------------------------------------------------------------* --
535
536 -------------------------------------------------------------------------------------------------------------------
537 Function inquire_history_tab(tres_id number) return boolean is
538 n_cnt number;
539 l_module_name varchar2(200) := g_module_name || 'INQUIRE_HISTORY_TAB';
540 begin
541 select count(1) into n_cnt
542 from fv_treasury_symbols_history
543 where treasury_symbol_id = tres_id
544 and set_of_books_id = gbl_set_of_books_id
545 and date_purged is null;
546 if n_cnt > 0 then
547 return true;
548 else
549 return false;
550 end if;
551 end inquire_history_tab;
552
553 -- *----------------------------------------------------------------------* --
554 -- * PROCEDURE UPDATE_HISTORY_TAB * --
555 -- * ------------------------------- * --
556 -- * Called during Preliminary Mode or in Final Mode when error occured, * --
557 -- * update the open flag for treasury * --
558 -- * symbols already there in History Table * --
559 -- *----------------------------------------------------------------------* --
560
561 procedure update_history_tab(tres_id number,v_flag varchar2) is
562 l_module_name VARCHAR2(200) := g_module_name || 'UPDATE_HISTORY_TAB';
563 begin
564 ----dbms_output.put_line(' inside update_history_tab');
565 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_statement, l_module_name,' inside update_history_tab');
566 IF v_flag in ('Y','N') then
567 ---dbms_output.put_line(' inside update_history_tab updating for flag <>d');
568 update fv_treasury_symbols_history
569 set open_flag = v_flag ,
570 request_id = gbl_request_id,
571 last_update_date = sysdate,
572 last_updated_by = gbl_last_update_by,
573 last_update_login = gbl_last_update_log,
574 creation_date = sysdate,
575 created_by = gbl_created_by
576 where treasury_symbol_id = tres_id
577 and set_of_books_id = gbl_set_of_books_id;
578 ELSIF v_flag = 'D' and gbl_error_code = 0 then ---called while deleting treasury symbols ---------
579 ---dbms_output.put_line(' inside update_history_tab updating for flag = d');
580 update fv_treasury_symbols_history
581 set date_purged = TRUNC(sysdate),
582 request_id = gbl_request_id,
583 last_update_date = sysdate,
584 last_updated_by = gbl_last_update_by,
585 last_update_login = gbl_last_update_log,
586 creation_date = sysdate,
587 created_by = gbl_created_by
588 where treasury_symbol_id = tres_id
589 and set_of_books_id = gbl_set_of_books_id
590 and request_id = gbl_prelim_req_id
591 and date_purged is null;
592 ELSIF v_flag = 'R' then --- called when report runs unsuccessfully thus rollback all the changes-------
593
594 update fv_treasury_symbols_history
595 set date_purged = null,
596 request_id = gbl_prelim_req_id,
597 last_update_date = sysdate,
598 last_updated_by = gbl_last_update_by,
599 last_update_login = gbl_last_update_log,
600 creation_date = sysdate,
601 created_by = gbl_created_by
602 where set_of_books_id = gbl_set_of_books_id
603 and request_id = gbl_request_id
604 and nvl(do_not_purge_flag,'N') = 'N';
605 END IF;
606 EXCEPTION
607 WHEN NO_DATA_FOUND THEN
608 gbl_error_code := SQLCODE;
609 gbl_error_buf := SQLERRM;
610 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.final_exception',gbl_error_buf);
611 return;
612 WHEN OTHERS THEN
613 gbl_error_code := SQLCODE;
614 gbl_error_buf := SQLERRM;
615 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',gbl_error_buf);
616 return;
617 end update_history_tab;
618
619 -- *----------------------------------------------------------------------* --
620 -- * PROCEDURE INS_DELETE_TREASURY_SYMBOLS * --
621 -- * ------------------------------------- * --
622 -- * Called during Final Mode check Balances and update the History Tables* --
623 -- *----------------------------------------------------------------------* --
624
625 procedure ins_delete_treasury_symbols is
626 n_balance number;
627 n_cnt number;
628 v_open_flag varchar2(1);
629 l_module_name VARCHAR2(200):='INS_DELETE_TREASURY_SYMBOLS';
630 cursor c1_treas_symbol is
631 select treasury_symbol,treasury_symbol_id,set_of_books_id,do_not_purge_flag,open_flag
632 from fv_treasury_symbols_history fts
633 where set_of_books_id = gbl_set_of_books_id
634 and treasury_symbol= nvl(gbl_treasury_symbol,treasury_symbol)
635 and time_frame = nvl(gbl_time_frame,time_frame)
636 and established_fiscal_yr = NVL(gbl_year_established,established_fiscal_yr)
637 and nvl(to_char(trunc(cancellation_date)),'0') = NVL(to_char(gbl_cancellation_date),nvl(to_char(TRUNC(cancellation_date)),0))
638 and date_purged is null
639 and nvl(do_not_purge_flag,'N') = 'N'
640 and request_id = gbl_prelim_req_id;
641 ---and open_flag in('N');
642 /*cursor c2_fund_param_hist (tres_id number ) is
643 select fund_value
644 from fv_fund_parameters_history
645 where set_of_books_id = gbl_set_of_books_id
646 and treasury_symbol_id = tres_id;*/
647 BEGIN
648 --dbms_output.put_line('inside ins_delete_treasury_symbols');
649 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_statement, l_module_name,' Inside ins_delete_treasury_symbols');
650 gbl_cursor_cnt := 0;
651 for c1 in c1_treas_symbol
652 loop
653 gbl_cursor_cnt := 2;
654 gbl_treasury_symbol_id := c1.treasury_symbol_id;
655 IF c1.open_flag = 'Y' then
656 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.');
657 -- for c2 in c2_fund_param_hist(c1.treasury_symbol_id)
658 -- loop
659 --- calculate_gl_balances(c2.fund_value,n_balance,n_cnt);
660
661 -----------------------------------------------------------------------------------------------------
662 --- if gbl_error_code <> 0 then
663 --- exit;
664 --- end if;
665 --- IF (n_balance > 0 ) or (n_cnt > 0) then
666 --- v_open_flag := 'Y';
667 --ELSE
668 -- v_open_flag := 'N';
669 --- exit;
670 --- END IF;
671 --- end loop;
672 ELSE
673 if gbl_error_code <> 0 then
674 exit;
675 end if;
676 ---- if v_open_flag = 'Y' then
677 ---- update_history_tab(c1.treasury_symbol_id,v_open_flag);
678 ----- elsif v_open_flag = 'N' and gbl_error_code = 0 then
679 ---delete_treasury_symbols(c1.treasury_symbol_id);
680 update_history_tab(c1.treasury_symbol_id,'D');
681 ----- end if;
682 END IF;
683 end loop;
684 EXCEPTION
685 WHEN OTHERS THEN
686 gbl_error_code := SQLCODE;
687 gbl_error_buf := SQLERRM ||
688 ' -- Error in Delete_treasury_symbols when deleting treasury symbol' ;
689 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error1',gbl_error_buf);
690 END ins_delete_treasury_symbols;
691
692 -- *----------------------------------------------------------------------* --
693 -- * PROCEDURE CALCULATE_GL_BALANCES * --
694 -- * ------------------------------- * --
695 -- * Called during Preliminary Mode and Final Mode to claculate banaces * --
696 -- * and Transaction for particular fund value. * --
697 -- *----------------------------------------------------------------------* --
698
699 procedure calculate_gl_balances(p_fund_value in varchar2,p_balance out nocopy number,p_cnt out nocopy number)
700 IS
701 l_module_name VARCHAR2(200) := g_module_name || 'CALCULATE_GL_BALANCES';
702 v_query varchar2(3000);
703 prv_year number;
704 curr_year number;
705 s_date gl_period_statuses.start_date%type;
706 e_date gl_period_statuses.end_date%type;
707 ---v_flex_value varchar2(150);
708 BEGIN
709 p_balance := 0;
710 p_cnt := 0;
711 get_period_year(curr_year);
712
713 IF curr_year IS NOT NULL THEN
714 prv_year := curr_year -1;
715 BEGIN
716 FOR I IN parent_child_rollups_cur(gbl_flex_value_set_id)
717 LOOP
718 v_query := 'SELECT NVL(SUM((period_net_dr + begin_balance_dr) - (period_net_cr + begin_balance_cr)),0)
719 FROM gl_code_combinations gcc,gl_balances gb
720 WHERE gb.code_combination_id = gcc.code_combination_id
721 AND gcc.chart_of_accounts_id =' || gbl_account_id ||
722 'AND (GB.PERIOD_NUM,GB.PERIOD_YEAR) IN (SELECT MAX(PERIOD_NUM),PERIOD_YEAR
723 FROM gl_period_statuses
724 WHERE period_year IN(' || prv_year ||','|| curr_year ||')
725 AND application_id ='|| 101||'
726 AND closing_status <>'||'''F'''||
727 'AND closing_status <>'||'''N'''||
728 'AND adjustment_period_flag ='||'''N'''||
729 'AND ledger_id ='|| gbl_set_of_books_id ||
730 'GROUP BY PERIOD_YEAR)
731 AND GB.TEMPLATE_ID IS NULL
732 AND GB.LEDGER_ID =' || gbl_set_of_books_id ||
733 'AND gb.actual_flag ='||'''A'''||
734 'AND gcc.'||gbl_bal_segment_name||' = :P_fund_value
735 AND gcc.'||gbl_acc_segment_name||' = :P_acc_value
736 AND gcc.enabled_flag='||'''Y''';
737
738 execute immediate v_query into p_balance using p_fund_value,I.Flex_value;
739 IF p_balance <> 0 then
740 exit;
741 END IF;
742 END LOOP;
743 EXCEPTION
744 WHEN NO_DATA_FOUND THEN
745 p_balance := 0;
746 WHEN OTHERS THEN
747 gbl_error_code := SQLCODE;
748 gbl_error_buf := SQLERRM ||
749 ' -- Error in Calculate_Gl_Balances when finding balances and transactions for fund value';
750 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error1',gbl_error_buf);
751 END;
752 -------------------------------------------------------------------------------------
753 ------***** To check if there any transaction unposted against the fund value *****------------
754 BEGIN
755
756 SELECT min(start_date), max(end_date)
757 INTO s_date,e_date
758 FROM gl_period_statuses
759 WHERE period_year = curr_year
760 AND application_id = 101
761 AND closing_status <> 'F'
762 AND closing_status <> 'N'
763 AND adjustment_period_flag = 'N'
764 AND ledger_id = gbl_set_of_books_id;
765 v_query := 'SELECT count(1) FROM gl_je_lines gjl,gl_code_combinations gcc
766 WHERE gjl.ledger_id =' || gbl_set_of_books_id ||
767 'AND gjl.code_combination_id = gcc.code_combination_id
768 AND gcc.chart_of_accounts_id ='|| gbl_account_id ||
769 'AND gcc.'||gbl_bal_segment_name ||' = :p
770 AND gcc.enabled_flag = '||'''Y'''||
771 'AND gcc.account_type ='||'''A'''||
772 'AND gcc.'||gbl_acc_segment_name||' not like'||'''4350%'''||
773 'AND gcc.'||gbl_acc_segment_name||' not like'||'''4201%'''||
774 'AND effective_date between :s_date
775 AND :e_date
776 AND gjl.status = '||'''U''';
777
778 execute immediate v_query into p_cnt using p_fund_value,s_date,e_date;
779
780 EXCEPTION
781 WHEN NO_DATA_FOUND THEN
782
783 p_cnt := 0;
784 WHEN OTHERS THEN
785 gbl_error_code := SQLCODE;
786 gbl_error_buf := SQLERRM ||
787 ' -- Error in Calculate_Gl_Balances while finding transactions for fund value';
788 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error1',gbl_error_buf);
789 END;
790 END IF;
791 END calculate_gl_balances;
792
793 -- *----------------------------------------------------------------------* --
794 -- * PROCEDURE GET_PERIOD_YEAR * --
795 -- * ------------------------------- * --
796 -- * To get the running Fiscal year * --
797 -- *----------------------------------------------------------------------* --
798
799 procedure get_period_year(p_year out nocopy number)
800 IS
801 l_module_name VARCHAR2(200):= 'GET_PERIOD_YEAR';
802 BEGIN
803
804 --- sysdate := megan date;
805 SELECT period_year
806 INTO p_year
807 FROM gl_periods
808 WHERE period_set_name = gbl_period_set_name
809 AND adjustment_period_flag = 'N'
810 GROUP BY PERIOD_YEAR
811 HAVING sysdate between min(start_date) and max(end_date);
812
813 --p_year := 2005;
814
815 EXCEPTION
816 WHEN NO_DATA_FOUND THEN
817
818 gbl_error_code := SQLCODE;
819 gbl_error_buf := SQLERRM ||
820 ' -- Error in get_period_year finding current fiscal year';
821 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.error1',gbl_error_buf);
822
823 WHEN OTHERS THEN
824 gbl_error_code := SQLCODE;
825 gbl_error_buf := SQLERRM ||
826 ' -- Error in Calculate_Gl_Balances when finding balances and transactions for fund value';
827 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error2',gbl_error_buf);
828 END get_period_year;
829
830 -- *----------------------------------------------------------------------* --
831 -- * PROCEDURE DELETE_TREASURY_SYMBOLS * --
832 -- * --------------------------------- * --
833 -- * Called in Final Mode to Purge The Treasury Symbols * --
834 -- *----------------------------------------------------------------------* --
835 procedure delete_treasury_symbols IS
836
837 l_module_name VARCHAR2(200):= 'DELETE_TREASURY_SYMBOLS';
838
839 begin
840 BEGIN
841 delete from fv_fund_parameters ffp
842 where exists ( select treasury_symbol_id
843 from fv_treasury_symbols_history fts
844 where fts.set_of_books_id = gbl_set_of_books_id
845 and fts.treasury_symbol_id = ffp.treasury_symbol_id
846 and fts.request_id = gbl_request_id
847 and fts.date_purged is not null)
848
849 and ffp.set_of_books_id = gbl_set_of_books_id;
850 EXCEPTION
851 WHEN NO_DATA_FOUND THEN
852 --gbl_error_code := SQLCODE;
853 gbl_error_buf := SQLERRM ||
854 ' -- Error no-data-found of fund value in delete_treasury_symbols for treasury symbol '||gbl_treasury_symbol_id;
855 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error1',gbl_error_buf);
856 WHEN OTHERS THEN
857 gbl_error_code := SQLCODE;
858 gbl_error_buf := SQLERRM ||
859 ' -- Error in delete_treasury_symbols while deleting Treasury symbols from Fund Parameters';
860 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error1',gbl_error_buf);
861 return;
862 END;
863
864 BEGIN
865 delete from fv_treasury_symbols fts
866 where exists ( select treasury_symbol_id
867 from fv_treasury_symbols_history ftsh
868 where ftsh.set_of_books_id = gbl_set_of_books_id
869 and ftsh.treasury_symbol_id = fts.treasury_symbol_id
870 and ftsh.request_id = gbl_request_id
871 and ftsh.date_purged is not null)
872 and fts.set_of_books_id = gbl_set_of_books_id;
873 EXCEPTION
874 WHEN NO_DATA_FOUND THEN
875 gbl_error_code := SQLCODE;
876 gbl_error_buf := SQLERRM ||
877 ' -- Error no-data-found in delete_treasury_symbols ';
878 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error1',gbl_error_buf);
879 return;
880 WHEN OTHERS THEN
881 gbl_error_code := SQLCODE;
882 gbl_error_buf := SQLERRM ||
883 ' -- Error in delete_treasury_symbols while deleting Treasury symbols';
884 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error2',gbl_error_buf);
885 return;
886 END;
887 end delete_treasury_symbols;
888
889
890 -- *----------------------------------------------------------------------* --
891 -- * PROCEDURE CLEAN_UP * --
892 -- * ------------------- * --
893 -- * Called in Final Mode to Delete all the Junk Data from History Tables * --
894 -- *----------------------------------------------------------------------* --
895 procedure clean_up is
896 l_module_name VARCHAR2(200):= 'CLEAN UP';
897 begin
898 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_statement, l_module_name,' inside clean up procedure');
899
900 delete from fv_fund_parameters_history ffp
901 where ffp.set_of_books_id = gbl_set_of_books_id
902 and exists ( select treasury_symbol_id
903 from fv_treasury_symbols_history fts
904 where fts.set_of_books_id= gbl_set_of_books_id
905 and fts.treasury_symbol_id = ffp.treasury_symbol_id
906 and date_purged is null);
907
908 delete from fv_treasury_symbols_history fts
909 where fts.set_of_books_id= gbl_set_of_books_id
910 and date_purged is null;
911
912
913 EXCEPTION
914
915 WHEN NO_DATA_FOUND THEN
916 NULL;
917 WHEN OTHERS THEN
918 gbl_error_code := SQLCODE;
919 gbl_error_buf := SQLERRM ||
920 ' -- Error in clean_up while clearing history tables';
921 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error2',gbl_error_buf);
922
923 end clean_up;
924 -------------------------------------------------------------------------------------------------------------
925
926 end FV_PURGED_TREASURY_SYMBOLS;