1 PACKAGE BODY FV_FUNDS_AVAIL_PKG AS
2 /* $Header: FVFUNAVB.pls 120.12.12000000.4 2007/08/01 21:19:54 sasukuma ship $ */
3
4 -- ======================================================================
5 -- Variable Naming Conventions
6 -- ======================================================================
7 -- 1. Input/Output Parameter global variables
8 -- have the format "vp_<Variable Name>"
9 -- 2. Other Global Variables have the format "vg_<Variable_Name>"
10 -- 3. Procedure Level local variables have
11 -- the format "vl_<Variable_Name>"
12 -- 4. PL/SQL Table variables have "vt_<Variable_Name>"
13 -- 5. User Defined Excpetions have "e_<Exception_Name>"
14 -- ======================================================================
15 -- Parameter Global Variable Declarations
16 -- ======================================================================
17 g_module_name VARCHAR2(100);
18 vp_errbuf VARCHAR2(5000) ;
19 vp_retcode NUMBER := 0 ;
20 vp_sob_id Gl_Sets_Of_Books.set_of_books_id%TYPE ;
21 vp_coa_id Gl_Sets_Of_Books.chart_of_accounts_id%TYPE ;
22 vp_currency_code Fnd_Currencies.currency_code%TYPE ;
23 vp_flex_low VARCHAR2(2000);
24 vp_flex_high VARCHAR2(2000);
25 vp_treasury_symbol_id fv_treasury_symbols.treasury_symbol_id%type ;
26 vp_summary_type VARCHAR2(1);
27 vp_period_name VARCHAR2(30);
28 vp_report_id NUMBER;
29 vp_units VARCHAR2(30);
30 -- ======================================================================
31 -- Other Global Variable Declarations
32 -- ======================================================================
33 vg_bal_segment Fnd_Id_Flex_Segments.application_column_name%TYPE;
34 vg_acct_segment Fnd_Id_Flex_Segments.application_column_name%TYPE;
35 vg_apps_id Fnd_Id_Flex_Structures.application_id%TYPE;
36 vg_id_flex_code Fnd_Id_Flex_Structures.id_flex_code%TYPE;
37 vg_delimiter Fnd_Id_Flex_Structures.concatenated_segment_delimiter%TYPE ;
38 vg_flex_value_id fnd_flex_values.flex_value_id%TYPE;
39 i NUMBER ;
40
41 -- Variable used for dynamic sql in the Populate_CCIDs procedure
42 vg_where VARCHAR2(2000);
43 vg_select VARCHAR2(2000);
44 vg_amount VARCHAR2(1000);
45
46 -- PL/SQL Tables to hold the low and high values,
47 -- used in Get_Application_Col_Names proc
48 vt_seg_low Fnd_Flex_Ext.segmentarray;
49 vt_seg_high Fnd_Flex_Ext.segmentarray;
50 -- PL/SQL Table to hold the flexfield column names
51 TYPE seg_name IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
52 vt_seg_name seg_name;
53 -- Variables needed for the Breakup_Segments API,
54 -- used in Get_Application_Col_Names proc
55 vg_nsegs_low NUMBER ;
56 vg_nsegs_high NUMBER ;
57
58
59 -- Variables used in the procedure Get_Bfy_Segment
60 vg_bfy_segment Fnd_Id_Flex_Segments.application_column_name%TYPE;
61
62 -- ------------------------------------------------------------------
63 -- Procedure Main
64 -- ------------------------------------------------------------------
65 --Main procedure is called from concurrent program.
66 --This procedure calls all the subsequent procedures
67 --in the funds available process
68 -- ------------------------------------------------------------------
69 PROCEDURE Main(
70 errbuf OUT NOCOPY VARCHAR2,
71 retcode OUT NOCOPY NUMBER,
72 sob_id NUMBER,
73 coa_id NUMBER,
74 summary_type VARCHAR2 ,
75 report_id NUMBER,
76 Treasury_symbol_id NUMBER ,
77 flex_low VARCHAR2,
78 flex_high VARCHAR2,
79 period_name VARCHAR2 ,
80 currency_code VARCHAR2 ,
81 units VARCHAR2)
82 IS
83 l_module_name VARCHAR2(200);
84 BEGIN
85 l_module_name := g_module_name || 'Main';
86
87 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
88 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'START OF FUNDS AVAILBILITY MAIN PROCESS ......');
89 END IF;
90
91 -- Load the parameter global variables
92 vp_sob_id := sob_id ;
93 vp_coa_id := coa_id ;
94 vp_currency_code := currency_code ;
95 vp_summary_type := summary_type ;
96 vp_report_id := report_id;
97 vp_treasury_symbol_id := treasury_symbol_id ;
98 vp_flex_low := flex_low;
99 vp_flex_high := flex_high;
100 vp_period_name := period_name ;
101 vp_units :=units ;
102
103 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
104 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' SET OF BOOKS ID IS '||TO_CHAR(SOB_ID));
105 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CHART OF ACCOUNTS ID IS ' || TO_CHAR(VP_COA_ID));
106 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' RANGE FLEX FIELD LOW VALUE IS '||VP_FLEX_LOW);
107 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' RANGE FLEX FIELD HIGH VALUE IS '||VP_FLEX_HIGH);
108 END IF;
109
110 -- Get Qualifier Segments
111 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
112 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'DERIVING THE QUALIFIER SEGMENTS.....') ;
113 END IF;
114 Get_Qualifier_Segments;
115 IF vp_retcode = 0 THEN
116 IF (vp_summary_type = 'A' ) THEN
117 -- User selected Summary Types as Accounting Flexfield Range
118 Get_Application_Col_Names;
119 ELSIF (vp_summary_type = 'T' ) THEN
120 -- User selected Summary Types as Treasury symbol
121 Treasury_Symbol_attributes ;
122 END IF;
123 END IF;
124
125 IF vp_retcode = 0 THEN
126 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
127 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'SUBMITTING FUNDS AVAILABILITY REPORTS .....');
128 END IF;
129 Submit_Reports ;
130 END IF;
131 -- Checking for any errors
132 IF vp_retcode <> 0 THEN
133 errbuf := vp_errbuf;
134 retcode := vp_retcode;
135 ROLLBACK;
136 ELSE
137 COMMIT;
138 END IF;
139 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
140 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'END THE FUNDS AVAILABILITY MAIN PROCESS ......');
141 END IF;
142 EXCEPTION
143 WHEN OTHERS THEN
144 vp_retcode := SQLCODE ;
145 vp_errbuf := SQLERRM ||' -- Error in Main procedure' ;
146 errbuf := vp_errbuf;
147 retcode := vp_retcode;
148
149 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',VP_ERRBUF) ;
150 RETURN ;
151 END Main;
152 -- ------------------------------------------------------------------
153 -- Procedure Get_Qualifier_Segments
154 -- ------------------------------------------------------------------
155 -- Get_Qualifier_Segments procedure is called from the Main procedure.
156 -- This procedure gets the accounting and the balancing segments.
157 -- ------------------------------------------------------------------
158
159 PROCEDURE Get_Qualifier_Segments
160 IS
161 l_module_name VARCHAR2(200);
162 CURSOR flex_value_id_cur IS
163 SELECT flex_value_set_id
164 FROM fnd_id_flex_segments
165 WHERE application_id = 101
166 AND application_column_name =vg_acct_segment
167 AND id_flex_code = vg_id_flex_code
168 AND id_flex_num = vp_coa_id
169 AND enabled_flag = 'Y' ;
170
171 l_ret_val BOOLEAN;
172 BEGIN
173 l_module_name := g_module_name || 'Get_Qualifier_Segments';
174
175 vp_retcode := 0;
176
177 --Get the Account Segment
178 l_ret_val := FND_FLEX_APIS.GET_SEGMENT_COLUMN
179 (
180 x_application_id => vg_apps_id,
181 x_id_flex_code => vg_id_flex_code,
182 x_id_flex_num => vp_coa_id,
183 x_seg_attr_type => 'GL_ACCOUNT',
184 x_app_column_name => vg_acct_segment
185 );
186 IF (NOT l_ret_val) THEN
187 vp_retcode := 2 ;
188 vp_errbuf := 'Cannot read Account Segment Information' ;
189 fv_utility.log_mesg(fnd_log.level_error, l_module_name,vp_errbuf) ;
190 END IF;
191
192 IF (vp_retcode = 0) THEN
193 l_ret_val := FND_FLEX_APIS.GET_SEGMENT_COLUMN
194 (
195 x_application_id => vg_apps_id,
196 x_id_flex_code => vg_id_flex_code,
197 x_id_flex_num => vp_coa_id,
198 x_seg_attr_type => 'GL_BALANCING',
199 x_app_column_name => vg_bal_segment
200 );
201
202 IF (NOT l_ret_val) THEN
203 vp_retcode := 2 ;
204 vp_errbuf := 'Cannot read Balancing Segment Information' ;
205 fv_utility.log_mesg(fnd_log.level_error, l_module_name,vp_errbuf) ;
206 END IF;
207 END IF;
208
209
210 IF (vp_retcode = 0) THEN
211 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level ) THEN
212 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,' balancing segment is '||vg_bal_segment);
213 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,' natural accounting segment is '||vg_acct_segment);
214 END IF;
215 END IF;
216
217 IF (vp_retcode = 0) THEN
218 -- Get the Delimiter
219 vg_delimiter := Fnd_Flex_Ext.get_delimiter
220 (
221 'SQLGL',
222 vg_id_flex_code,
223 vp_coa_id
224 );
225
226 OPEN flex_value_id_cur ;
227 FETCH flex_value_id_cur
228 INTO vg_flex_value_id ;
229 CLOSE flex_value_id_cur ;
230
231 IF (vg_delimiter is NULL) THEN
232 vp_retcode := 2 ;
233 vp_errbuf := 'The Flexfield Structure is not found' ;
234 fv_utility.log_mesg(fnd_log.level_error, l_module_name,vp_errbuf) ;
235 END IF;
236 END IF;
237
238 IF (vp_retcode = 0) THEN
239 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level ) then
240 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,' delimiter is ' ||vg_delimiter) ;
241 END IF;
242 END IF;
243
244 EXCEPTION
245 WHEN OTHERS THEN
246 vp_retcode := SQLCODE;
247 vp_errbuf := SQLERRM;
248 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',vp_errbuf) ;
249 RAISE;
250 END Get_Qualifier_Segments ;
251
252 -- ------------------------------------------------------------------
253 -- Procedure Get_Application_Col_Names
254 -- ------------------------------------------------------------------
255 -- Get_Application_Col_Names procedure is called from the Main procedure.
256 -- This procedure gets the application column names of the accounting
257 -- flexfield for the passed chart of account id.
258 -- ------------------------------------------------------------------
259 PROCEDURE Get_Application_Col_Names IS
260 l_module_name VARCHAR2(200);
261 vl_ctr NUMBER;
262
263 CURSOR seg_names_cur IS
264 SELECT application_column_name
265 FROM Fnd_Id_Flex_Segments
266 WHERE application_id = vg_apps_id
267 AND id_flex_code = vg_id_flex_code
268 AND id_flex_num = vp_coa_id
269 ORDER BY segment_num;
270 BEGIN
271 l_module_name := g_module_name || 'Get_Application_Col_Names';
272
273 i := 1;
274
275 -- Get the Flexfield Column Names(Application Column Names)
276 -- for the Chart Of Accounts Id passed.
277 FOR vc_seg_names IN seg_names_cur LOOP
278 vt_seg_name(i) := vc_seg_names.application_column_name;
279 i := i + 1;
280 END LOOP;
281
282 -- Get the Maximum number of segments
283 vl_ctr := vt_seg_name.COUNT;
284 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
285 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' NUMBER OF SEGMENTS ARE '||TO_CHAR(VL_CTR));
286 END IF;
287 -- Calling Get_Segment_Values procedure
288 Get_Segment_Values(vl_ctr);
289
290 EXCEPTION
291 WHEN OTHERS THEN
292 vp_retcode := SQLCODE;
293 vp_errbuf := SQLERRM;
294 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',vp_errbuf) ;
295 END Get_Application_Col_Names ;
296 -- ------------------------------------------------------------------
297 -- Procedure Get_Segment_Values
298 -- ------------------------------------------------------------------
299 -- Get_Segment_Values procedure is called from Get_Application_Col_Names
300 -- procedure.
301 -- This procedure builds the where clause based on the segment low
302 -- and high values entered in SRS window ,to be passed to the
303 -- Populate_CCIDs procedure to get the CCIDS.
304 -- ------------------------------------------------------------------
305
306 PROCEDURE Get_Segment_Values(
307 seg_cnt NUMBER)
308
309 IS
310 l_module_name VARCHAR2(200);
311 CURSOR seg_num_cur (p_application_column_name VARCHAR2) IS
312 SELECT segment_num
313 FROM Fnd_Id_Flex_Segments
314 WHERE application_id = vg_apps_id
315 AND id_flex_code = vg_id_flex_code
316 AND id_flex_num = vp_coa_id
317 AND application_column_name=p_application_column_name ;
318 vl_acc_seg_num NUMBER;
319 vl_bal_seg_num NUMBER ;
320 -- To count the first entered range in the Felxfield window .
321 vl_first_seg NUMBER:=0 ;
322 BEGIN
323 l_module_name := g_module_name || 'Get_Segment_Values';
324 vg_where := '' ;
325
326 -- Get low segment values
327 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
328 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CALLING POPULATE LOW SEGMETNS ' ) ;
329 END IF;
330 vg_nsegs_low := Fnd_Flex_Ext.breakup_segments
331 (vp_flex_low,
332 vg_delimiter,
333 vt_seg_low );
334 -- Get high segment values
335 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
336 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CALLING POPULATE HIGH SEGMETNS ' ) ;
337 END IF;
338 vg_nsegs_high := Fnd_Flex_Ext.breakup_segments
339 (vp_flex_high,
340 vg_delimiter,
341 vt_seg_high );
342
343 -- Get the Balancing segment segment number for Flexfield
344 OPEN seg_num_cur(vg_bal_segment ) ;
345 FETCH seg_num_cur INTO vl_bal_seg_num ;
346 CLOSE seg_num_cur ;
347 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
348 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' BALANCING SEGMENT NUMBER IS ' ||VL_BAL_SEG_NUM ) ;
349 END IF;
350 -- Get the Accounting segment number for Flexfield
351 OPEN seg_num_cur (vg_acct_segment);
352 FETCH seg_num_cur INTO vl_acc_seg_num ;
353 CLOSE seg_num_cur;
354 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
355 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' ACCOUNTING SEGMENT NUMBER IS ' ||VL_ACC_SEG_NUM ) ;
356 END IF;
357
358 -- Verify whether user has entered Fund range "Balancing segment"
359 -- when process run by Accounting range
360 IF vt_seg_low(vl_bal_seg_num) IS NULL THEN
361 vp_retcode := 2 ;
362 vp_errbuf :='Balancing segment - Fund range is mandatory when the process is run with Summary Type as Accounting Flexfield';
363 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,vp_errbuf) ;
364 RETURN ;
365 END IF;
366 IF vt_seg_low(vl_acc_seg_num) IS NOT NULL THEN
367 vt_seg_low(i):=NULL ;
368 vt_seg_high(i):=NULL ;
369 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
370 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' USER HAS GIVEN ACCOUNT RANGE AS PARAMETERS FOR THIS PROCESS.THIS ACCOUNT RANGE WILL BE OVERRIDEN ');
371 END IF;
372 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
373 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' WITH ACCOUNT RANGES GIVEN IN THE REPORT DEFINITIONS SET UP FORM ') ;
374 END IF;
375 END IF ;
376 vg_select:= '' ;
377 -- Construct the concatenate segments based on the Accounting range entered
378 FOR i IN 1..seg_cnt LOOP
379 IF (vt_seg_low(i) IS NOT NULL) AND
380 ( i <> vl_acc_seg_num ) THEN
381 IF vl_first_seg >=1 THEN
382 vg_select:= vg_select||'||'||''''||vg_delimiter
383 ||''''||'||'||'glcc.'||vt_seg_name(i);
384 ELSE
385 vg_select:= vg_select||'glcc.'||vt_seg_name(i);
386 vl_first_seg:=vl_first_seg+1 ;
387 END IF ;
388 END IF ;
389 END LOOP ;
390
391 vg_select:= vg_select||' , glcc.'||vg_acct_segment||
392 ' , glcc.' ||vg_bal_segment ;
393
394 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
395 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' SELECT STATEMENT IS ' ||VG_SELECT);
396 END IF;
397
398 -- Construct a where condition to be used to select the
399 -- transactions from gl_balances and gl_bc_packets
400 vl_first_seg:=0 ;
401 FOR i IN 1..seg_cnt LOOP
402 IF (vt_seg_low(i) IS NOT NULL) AND
403 ( i <> vl_acc_seg_num ) THEN
404 IF vl_first_seg >=1 THEN
405 vg_where := vg_where ||' AND '||'glcc.'||
406 vt_seg_name(i) ||' BETWEEN '
407 ||''''||vt_seg_low(i)||''''||' AND '
408 ||''''||vt_seg_high(i)||'''' ;
409 ELSE
410 vg_where := 'glcc.'||vt_seg_name(i) ||' BETWEEN '
411 ||''''||vt_seg_low(i)||''''||' AND '
412 ||''''||vt_seg_high(i)||'''' ;
413 vl_first_seg:=vl_first_seg+1 ;
414 END IF ;
415 END IF ;
416 END LOOP;
417
418 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
419 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' WHERE CLAUSE IS ' ||VG_WHERE );
420 END IF;
421
422 Populate_CCIDs(vg_select,vg_where);
423 EXCEPTION
424 WHEN OTHERS THEN
425 vp_retcode := SQLCODE;
426 vp_errbuf := SQLERRM;
427 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',vp_errbuf) ;
428 RAISE;
429 END Get_Segment_Values ;
430
431 -- ------------------------------------------------------------------
432 -- Procedure Treasury_Symbol_attributes
433 -- ------------------------------------------------------------------
434 -- This procedure finds the fund values attached for the TAS
435 -- This procedure calls the get BFY segment value procedure
436 -- Calls the populate_CCID'S procedure to ge the CCID's
437 -- ------------------------------------------------------------------
438
439 PROCEDURE Treasury_Symbol_attributes IS
440 l_module_name VARCHAR2(200);
441
442 vl_fund VARCHAR2(30);
443
444 BEGIN
445 l_module_name := g_module_name || 'Treasury_Symbol_attributes';
446 -- Verify IF user entered TAS paramter .
447 IF vp_treasury_symbol_id IS NULL THEN
448 vp_retcode := -1 ;
449 vp_errbuf := 'Treasury symbol parameter should be entered when the process is run with Summary Type as Treasury Symbol ';
450 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,vp_errbuf) ;
451 RETURN ;
452 END IF;
453
454 -- Verify If Fund values are exists for the Treasury Symbol user entered .
455 SELECT COUNT(1) INTO vl_fund FROM FV_FUND_PARAMETERS
456 WHERE TREASURY_SYMBOL_ID = vp_treasury_symbol_id
457 AND set_of_books_id = vp_sob_id ;
458 IF vl_fund=0 THEN
459 vp_retcode := -1 ;
460 vp_errbuf :='There are no Funds defined for the Treasury Symbol Passed.
461 Please define Funds for this Treasury Symbol in the Define Parameters Form' ;
462
463 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,vp_errbuf) ;
464 RETURN ;
465 END IF;
466
467 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
468 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CALLING PROCEDURE GET_BFY_SEGMENT ' ||VG_WHERE);
469 END IF;
470 Get_Bfy_Segment ;
471 IF vp_retcode = 0 THEN
472
473 vg_select:= 'glcc.'||vg_bal_segment||'||'||''''||vg_delimiter||''''||'||'||
474 'glcc.'||vg_bfy_segment||' , glcc.'||vg_acct_segment ;
475
476 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
477 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' SELECT STATMENT WHEN PROCESS RUNS FOR TAS ' || VG_SELECT );
478 END IF;
479
480 vg_where := 'glcc.'|| vg_bal_segment || ' IN ' ||
481 '(SELECT FUND_VALUE FROM FV_FUND_PARAMETERS
482 WHERE TREASURY_SYMBOL_ID = '||vp_treasury_symbol_id||
483 ' AND FV_FUND_PARAMETERS.set_of_books_id= '||vp_sob_id||')' ;
484
485 Populate_CCIDs(vg_select,vg_where);
486 END IF ;
487 EXCEPTION
488 WHEN OTHERS THEN
489 vp_retcode := SQLCODE;
490 vp_errbuf := SQLERRM;
491 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',vp_errbuf) ;
492 RAISE;
493 END Treasury_Symbol_attributes ;
494
495 -- ------------------------------------------------------------------
496 -- Procedure Get_Bfy_Segment
497 -- ------------------------------------------------------------------
498 -- Get_Bfy_Segment procedure is called from the Main procedure If
499 -- the process is runds for Treasury Symbol
500 -- This procedure gets the fiscal year segment from the PYA Mapping table .
501 -- ------------------------------------------------------------------
502 PROCEDURE Get_Bfy_Segment IS
503 l_module_name VARCHAR2(200);
504 vl_segment_id NUMBER;
505
506 CURSOR Bfa_segment_cur IS
507 SELECT application_column_name
508 FROM Fv_Pya_Fiscalyear_Segment
509 WHERE set_of_books_id = vp_sob_id ;
510
511 BEGIN
512 l_module_name := g_module_name || 'Get_Bfy_Segment';
513 OPEN Bfa_segment_cur ;
514 FETCH Bfa_segment_cur INTO vg_bfy_segment ;
515 IF Bfa_segment_cur%NOTFOUND THEN
516 vp_retcode := 2;
517 vp_errbuf := 'Budget Fiscal Year Segment not defined in PYA set up for the ledger '||to_char(vp_sob_id);
518 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,vp_errbuf) ;
519 CLOSE Bfa_segment_cur ;
520 RETURN ;
521 END IF ;
522 CLOSE Bfa_segment_cur ;
523
524 EXCEPTION
525 WHEN OTHERS THEN
526 vp_retcode := SQLCODE ;
527 vp_errbuf := SQLERRM ||
528 ' -- Error in Get_Bfy_Segment_Details procedure '
529 ||' while getting the BFY Segment Name.' ;
530 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',vp_errbuf) ;
531 RETURN ;
532 END Get_Bfy_Segment;
533
534 -- ------------------------------------------------------------------
535 -- Procedure Populate_CCIDs
536 -- ------------------------------------------------------------------
537 -- Populate_CCIDs procedure is called from Get_Segment_Values
538 -- procedure.This procedure populates the temp table with
539 -- the concatenate segments based on the where clause passed .
540 -- ------------------------------------------------------------------
541
542 PROCEDURE Populate_CCIDs( select_cl VARCHAR2,
543 where_cl VARCHAR2)
544 IS
545 l_module_name VARCHAR2(200);
546 -- Variable declartions for Dynamic SQL
547 TYPE t_refcur IS REF CURSOR;
548 vl_bal_retcur t_refcur;
549 vl_cursor_id INTEGER;
550 vl_select_stmnt VARCHAR2(4000);
551 vl_ret INTEGER;
552 vl_conc_seg VARCHAR2(2000);
553 vl_acc_seg VARCHAR2(30);
554 vl_bal_seg VARCHAR2(30);
555 vl_amount NUMBER ;
556 vl_report_id NUMBER ;
557 vl_column_ID NUMBER ;
558 i NUMBER := 1;
559 vl_seed_count NUMBER ;
560 vl_Acct_count NUMBER ;
561 l_period_year NUMBER;
562 l_period_num NUMBER;
563
564
565 BEGIN
566 l_module_name := g_module_name || 'Populate_CCIDs';
567
568 -- Check whether seed process was run
569
570 SELECT COUNT(1) INTO vl_seed_count
571 FROM Fv_Funds_Avail_Rep_Def
572 WHERE set_of_books_id = vp_sob_id
573 AND Report_Id = vp_report_id ;
574
575 IF vl_seed_count=0 THEN
576 vp_retcode := -1 ;
577 vp_errbuf := ' Please run the Populate Funds Availabiity Report Definitions Process .' ;
578 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,vp_errbuf) ;
579 RETURN ;
580 END IF;
581 -- Check whether Accounts are Defined in the form ,
582 -- Funds Availability Report Definitions .
583
584 SELECT COUNT(1) INTO vl_Acct_count
585 FROM Fv_Funds_Avail_Rep_Def fvd ,
586 Fv_Funds_Avail_Acct_Ranges fvr
587 WHERE fvr.column_id=fvd.column_id
588 AND fvr.set_of_books_id = vp_sob_id
589 AND Report_Id = vp_report_id ;
590
591 IF vl_Acct_count=0 THEN
592 vp_retcode := -1 ;
593 vp_errbuf := ' Accounts not defined . Please define Accounts in the Funds Availiability Report
594 Definitions Form. ' ;
595 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,vp_errbuf) ;
596 RETURN ;
597 END IF;
598
599 SELECT period_year,
600 period_num
601 INTO l_period_year,
602 l_period_num
603 FROM gl_period_statuses
604 WHERE application_id = 101
605 AND set_of_books_id = vp_sob_id
606 AND period_name = vp_period_name;
607
608 IF vp_report_id = 1 THEN
609
610 -- If the process is run for the Funds Available-Total Resources
611 -- If it is a Debit balance Positive ,If credit it is negative .
612
613 vg_amount:=' SUM((nvl(glb.begin_balance_dr,0) - nvl(glb.begin_balance_cr,0))+
614 (nvl(glb.period_net_dr,0) - nvl(glb.period_net_cr,0))) amount ' ;
615 ELSE
616
617 -- If the Process is run for other than Funds Available-Total Resources
618 -- Accounts with Account Type, "Budgetary Debit", that have
619 -- Credit Balances will be considered as negative Otherwise,
620 -- the balances are positive
621 -- Accounts with Account Type, "Budgetary Credits", that have
622 -- Debit Balances will be considered as negative amounts.
623 -- Otherwise, the balances are positive .
624
625 vg_amount :='SUM( DECODE(glcc.account_type , ' ||''''||'C'||''''|| ',
626 ((nvl(glb.begin_balance_cr,0) - nvl(glb.begin_balance_dr,0)) +
627 (nvl(glb.period_net_cr,0) - nvl(glB.period_net_dr,0))) ,
628 ((nvl(glb.begin_balance_dr,0) - nvl(glb.begin_balance_cr,0)) +
629 (nvl(glb.period_net_dr,0) - nvl(glb.period_net_cr,0))))) amount ';
630 END IF ;
631 vl_select_stmnt :=
632 'SELECT '||select_cl||
633 ' , fvd.report_id, '||
634 ' fvr.column_id , '||
635 vg_amount ||
636 ' FROM Fv_Funds_Avail_Rep_Def fvd ,
637 Fv_Funds_Avail_Acct_Ranges fvr,
638 Gl_Code_Combinations glcc , Gl_Balances glb
639 WHERE glcc.code_combination_id =
640 glb.code_combination_id '||
641 ' AND fvr.column_id=fvd.column_id '||
642 ' AND fvr.set_of_books_id = :b_vp_sob_id ' ||
643 ' AND fvd.set_of_books_id = :b_vp_sob_id ' ||
644 ' AND glcc.template_id IS NULL ' ||
645 ' AND ( glcc.'||vg_acct_segment ||
646 ' BETWEEN '|| ' fvr.account_from ' ||
647 ' AND '|| ' fvr.account_to OR EXISTS '||
648 ' (SELECT 1 FROM fnd_flex_value_hierarchies h '||
649 ' WHERE glcc.'||vg_acct_segment ||' BETWEEN' ||
650 ' child_flex_value_low AND child_flex_value_high '||
651 ' AND h.flex_value_set_id = :b_vg_flex_value_id' ||
652 ' AND h.PARENT_FLEX_VALUE BETWEEN fvr.account_from '||
653 ' AND fvr.account_to )) '||
654 ' AND glcc.chart_of_accounts_id = :b_vp_coa_id'||
655 ' AND glb.ledger_id = :b_vp_sob_id ' ||
656 ' AND glb.currency_code = :b_vp_currency_code '||
657 ' AND glb.period_name = :b_vp_period_name'||
658 ' AND glb.actual_flag = '||''''||'A'||'''' ||
659 ' AND glcc.enabled_flag ='||''''||'Y'||'''' ||
660 ' AND fvd.report_id = :b_vp_report_id' ||
661 ' AND '||
662 where_cl || ' GROUP BY '||
663 select_cl ||
664 ' , fvd.report_id, '||
665 ' fvr.column_id ' ;
666
667
668 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
669 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'SELECT STATEMENT TO GET DATA FROM GL BALANCES TABLE ' ||VL_SELECT_STMNT);
670 END IF;
671
672 -- Fetch the Transactions from GL balances
673
674 OPEN vl_bal_retcur FOR vl_select_stmnt USING vp_sob_id,vp_sob_id,vg_flex_value_id,
675 vp_coa_id,vp_sob_id,vp_currency_code,vp_period_name,vp_report_id;
676
677 IF vp_summary_type = 'A' THEN
678 LOOP
679 -- Fetch the rows
680 FETCH vl_bal_retcur INTO vl_conc_seg , vl_acc_seg ,vl_bal_seg ,
681 vl_report_id , vl_column_id , vl_amount ;
682 EXIT WHEN vl_bal_retcur%NOTFOUND ;
683
684 Create_Transactions(vl_conc_seg,
685 vl_bal_seg ,
686 vl_report_id ,
687 vl_column_id ,
688 vl_amount ,
689 vp_sob_id );
690 i:=i+1 ;
691 END LOOP ;
692
693 ELSIF vp_summary_type = 'T' THEN
694 LOOP
695
696 FETCH vl_bal_retcur INTO vl_conc_seg , vl_acc_seg , vl_report_id ,
697 vl_column_id , vl_amount ;
698 EXIT WHEN vl_bal_retcur%NOTFOUND ;
699 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
700 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' INSERTING INTO TEMP TABLE FROM GL GL_BALANCES '|| I);
701 END IF;
702
703 Create_Transactions(vl_conc_seg,
704 NULL ,
705 vl_report_id ,
706 vl_column_id ,
707 vl_amount ,
708 vp_sob_id );
709 i:=i+1 ;
710 END LOOP ;
711 END IF ;
712 CLOSE vl_bal_retcur;
713 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
714 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CREATED ALL THE TRANSACTIONS FROM GL BALANCES TABLE ');
715 END IF;
716
717 -- Fetch Transactions from GL BC packets table .
718 i:=1 ;
719 vl_select_stmnt := ' ' ;
720 vg_amount := ' ' ;
721 IF vp_report_id = 1 THEN
722
723 -- If the process is run for the Funds Available-Total Resources
724 -- If it is a Debit balance Positive ,If credit it is negative .
725
726 vg_amount := ' Sum (Nvl(accounted_dr,0) - nvl(accounted_cr,0) ) amount ' ;
727 ELSE
728
729 -- If the Process is run for other than Funds Available-Total Resources
730 -- Accounts with Account Type, "Budgetary Debit", that have
731 -- Credit Balances will be considered as negative Otherwise,
732 -- the balances are positive
733 -- Accounts with Account Type, "Budgetary Credits", that have
734 -- Debit Balances will be considered as negative amounts.
735 -- Otherwise, the balances are positive .
736
737 vg_amount := ' Sum(Decode(glcc.account_type ,' ||''''||'C'||''''|| ',
738 (Nvl(accounted_cr,0) - nvl(accounted_dr,0) ) ,
739 (Nvl(accounted_dr,0) - nvl(accounted_cr,0)))) amount ';
740 END IF ;
741 vl_select_stmnt :=
742 'SELECT '||select_cl||
743 ' , fvd.report_id, '||
744 ' fvr.column_id , '||
745 vg_amount ||
746 ' FROM Fv_Funds_Avail_Rep_Def fvd ,
747 Fv_Funds_Avail_Acct_Ranges fvr,
748 Gl_Code_Combinations glcc , Gl_Bc_Packets glbc
749 WHERE glcc.code_combination_id =
750 glbc.code_combination_id '||
751 ' AND fvr.column_id=fvd.column_id '||
752 ' AND fvr.set_of_books_id = :b_vp_sob_id ' ||
753 ' AND fvd.set_of_books_id = :b_vp_sob_id ' ||
754 ' AND ( glcc.'||vg_acct_segment ||
755 ' BETWEEN '|| ' fvr.account_from ' ||
756 ' AND '|| ' fvr.account_to OR EXISTS '||
757 ' (SELECT 1 FROM fnd_flex_value_hierarchies h '||
758 ' WHERE glcc.'||vg_acct_segment ||' BETWEEN' ||
759 ' child_flex_value_low AND child_flex_value_high '||
760 ' AND h.flex_value_set_id = :b_vg_flex_value_id' ||
761 ' AND h.PARENT_FLEX_VALUE BETWEEN fvr.account_from '||
762 ' AND fvr.account_to )) '||
763 ' AND glcc.chart_of_accounts_id = :b_vp_coa_id'||
764 ' AND glbc.ledger_id = :b_vp_sob_id ' ||
765 ' AND glbc.currency_code = :b_vp_currency_code '||
766 ' AND glbc.period_year <= :b_vp_period_year'||
767 ' AND glbc.period_num <= :b_vp_period_num'||
768 ' AND glbc.actual_flag = '||''''||'A'||'''' ||
769 ' AND glcc.enabled_flag ='||''''||'Y'||'''' ||
770 ' AND glbc.status_code = '||''''||'A'||'''' ||
771 ' AND glbc.template_id IS NULL '||
772 ' AND fvd.report_id = :b_vp_report_id' ||
773 ' AND '||
774 where_cl || ' GROUP BY '||
775 select_cl ||
776 ' , fvd.report_id, '||
777 ' fvr.column_id ' ;
778
779
780 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
781 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'SELECT STATEMENT TO GET DATA FROM GL BC PACKETS TABLE ' ||VL_SELECT_STMNT);
782 END IF;
783
784 OPEN vl_bal_retcur FOR vl_select_stmnt USING vp_sob_id,vp_sob_id,vg_flex_value_id,
785 vp_coa_id,vp_sob_id,vp_currency_code,l_period_year, l_period_num, vp_report_id;
786
787 IF vp_summary_type = 'A' THEN
788 LOOP
789 -- Fetch the rows
790 FETCH vl_bal_retcur INTO vl_conc_seg , vl_acc_seg ,vl_bal_seg ,
791 vl_report_id ,vl_column_id , vl_amount ;
792 EXIT WHEN vl_bal_retcur%NOTFOUND ;
793
794 -- Popualte Fund_value in the temp table Fv_Funds_Avail_Temp
795 Create_Transactions(vl_conc_seg,
796 vl_bal_seg ,
797 vl_report_id ,
798 vl_column_id ,
799 vl_amount ,
800 vp_sob_id );
801 i:=i+1 ;
802 END LOOP ;
803
804 ELSIF vp_summary_type = 'T' THEN
805 LOOP
806
807 FETCH vl_bal_retcur INTO vl_conc_seg , vl_acc_seg , vl_report_id ,
808 vl_column_id , vl_amount ;
809 EXIT WHEN vl_bal_retcur%NOTFOUND ;
810 -- Popualte Fund_value as NULL in the temp table Fv_Funds_Avail_Temp
811 Create_Transactions(vl_conc_seg,
812 NULL ,
813 vl_report_id ,
814 vl_column_id ,
815 vl_amount ,
816 vp_sob_id );
817 i:=i+1 ;
818 END LOOP ;
819 END IF ;
820 CLOSE vl_bal_retcur;
821 EXCEPTION
822 WHEN OTHERS THEN
823 vp_retcode := SQLCODE;
824 vp_errbuf := SQLERRM;
825 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',vp_errbuf) ;
826 RAISE;
827
828 END Populate_CCIDs ;
829
830 -- ------------------------------------------------------------------
831 -- Procedure Submit_Reports
832 -- ------------------------------------------------------------------
833 -- Submit_Reports procedure is called from the Main Procedure.
834 -- This procedure submits the Funds Availability Reports
835 -- ------------------------------------------------------------------
836 PROCEDURE Submit_Reports IS
837 l_module_name VARCHAR2(200);
838
839 CURSOR treasury_symbol_c IS
840 SELECT treasury_symbol
841 FROM fv_treasury_symbols
842 WHERE treasury_symbol_id = vp_treasury_symbol_id ;
843
844 vl_req_id NUMBER;
845 vl_count NUMBER ;
846 treasury_symbol VARCHAR2(35);
847
848
849 BEGIN
850 l_module_name := g_module_name || 'Submit_Reports';
851 SELECT COUNT(*) INTO vl_count FROM
852 Fv_Funds_Avail_Temp WHERE
853 report_id =vp_report_id ;
854 IF vl_count = 0 THEN
855 vp_retcode := 1 ;
856 IF vp_summary_type='T' THEN
857 OPEN treasury_symbol_c;
858 FETCH treasury_symbol_c into treasury_symbol;
859 CLOSE treasury_symbol_c;
860 vp_errbuf := 'No Data Found for treasury symbol ' || treasury_symbol ||
861 ' in the period ' || vp_period_name || '.' ;
862 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,vp_errbuf) ;
863 ELSE
864 vp_errbuf := 'No Data Found for accounts between ' || vp_flex_low ||
865 ' and '|| vp_flex_high || ' in the period ' || vp_period_name || '.' ;
866 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,vp_errbuf) ;
867 END IF;
868
869 RETURN ;
870 END IF;
871
872 -- Check if available balances for all funds equal zero
873 -- If so, dont kick off reports.
874 SELECT COUNT(*) INTO vl_count FROM
875 Fv_Funds_Avail_Temp WHERE
876 report_id = vp_report_id AND
877 amount <> 0;
878
879 IF vl_count = 0 THEN
880 vp_retcode := 1 ;
881 IF vp_summary_type='T' THEN
882 OPEN treasury_symbol_c;
883 FETCH treasury_symbol_c into treasury_symbol;
884 CLOSE treasury_symbol_c;
885 vp_errbuf:= 'Zero balances available for treasury symbol ' || treasury_symbol ||
886 ' in the period ' || vp_period_name || '.' ;
887 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,vp_errbuf) ;
888 ELSE
889 vp_errbuf := 'Zero balances available for accounts between ' || vp_flex_low ||
890 ' and ' || vp_flex_high || ' in the period ' || vp_period_name || '.' ;
891 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,vp_errbuf) ;
892 END IF;
893 RETURN ;
894 END IF;
895
896
897 vl_req_id:= Fnd_Request.Submit_Request ('FV','FVFUNAVR','','',FALSE,
898 vp_sob_id,vp_coa_id,VP_SUMMARY_TYPE,VP_REPORT_ID,
899 VP_TREASURY_SYMBOL_ID,VP_FLEX_LOW,VP_FLEX_HIGH,
900 VP_PERIOD_NAME,VP_CURRENCY_CODE,vp_units ) ;
901 IF (vl_req_id = 0) THEN
902 vp_retcode := 2 ;
903 vp_errbuf := 'Error in Submit_Reports procedure, while submitting Funds Available Report .' ;
904 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,vp_errbuf) ;
905
906 RETURN ;
907 END IF;
908
909 EXCEPTION
910 WHEN OTHERS THEN
911 vp_retcode := SQLCODE ;
912 vp_errbuf := SQLERRM ||' -- Error in Submit_Reports procedure.' ;
913 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',vp_errbuf) ;
914 RETURN ;
915 END Submit_Reports;
916
917 -- ------------------------------------------------------------------
918 -- Procedure Create_Transactions
919 -- ------------------------------------------------------------------
920 -- Create_Transactions procedure is called from the populate_ccid procedure.
921 -- This procedure creates the data in the Fv_Funds_Avail_temp Table.
922 --
923 -- ------------------------------------------------------------------
924 PROCEDURE Create_Transactions
925 ( CONCAT_SEGMENTS VARCHAR2,
926 FUND_VALUE VARCHAR2 ,
927 REPORT_ID NUMBER ,
928 COLUMN_ID NUMBER ,
929 AMOUNT NUMBER ,
930 SET_OF_BOOKS_ID NUMBER
931
932 ) IS
933 l_module_name VARCHAR2(200);
934 BEGIN
935 l_module_name := g_module_name || 'Create_Transactions';
936
937 INSERT INTO Fv_Funds_Avail_temp (CONCAT_SEGMENTS,
938 FUND_VALUE ,
939 REPORT_ID ,
940 COLUMN_ID,
941 AMOUNT ,
942 SET_OF_BOOKS_ID)
943 VALUES (
944 CONCAT_SEGMENTS ,
945 FUND_VALUE ,
946 REPORT_ID ,
947 COLUMN_ID ,
948 AMOUNT ,
949 SET_OF_BOOKS_ID );
950 EXCEPTION
951 WHEN OTHERS THEN
952 vp_retcode := SQLCODE ;
953 vp_errbuf := SQLERRM ||' -- Error in Submit_Reports procedure.' ;
954
955 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',vp_errbuf) ;
956 RETURN ;
957
958 END ;
959 BEGIN
960 g_module_name := 'fv.plsql.FV_FUNDS_AVAIL_PKG.';
961 vg_apps_id := 101;
962 vg_id_flex_code := 'GL#';
963
964 ----------------------------------------------------------------------
965 -- END OF PACKAGE BODY
966 ----------------------------------------------------------------------
967 END FV_FUNDS_AVAIL_PKG;
[Home] [Help]