1 PACKAGE BODY Fv_Ye_Close AS
2 --$Header: FVXYECPB.pls 120.47.12010000.2 2008/08/04 11:48:35 gnrajago ship $
3 -- ======================================================================
4 -- Variable Naming Conventions
5 -- ======================================================================
6 -- 1. Input/Output Parameter global variables
7 -- have the format "vp_<Variable Name>"
8 -- 2. Other Global Variables have the format "vg_<Variable_Name>"
9 -- 3. Procedure Level local variables have
10 -- the format "vl_<Variable_Name>"
11 -- 4. PL/SQL Table variables have "vt_<Variable_Name>"
12 -- 5. User Defined Exceptions have "e_<Exception_Name>"
13 -- 6. Variable Cursors have "vc_<Variable_Name>"
14 -- ======================================================================
15 -- Parameter Global Variable Declarations
16 -- ======================================================================
17
18 g_module_name VARCHAR2(100) ;
19 vp_errbuf VARCHAR2(1000) ;
20 vp_retcode NUMBER ;
21 vp_journal_import VARCHAR2(1) ;
22 vp_timeframe Fv_Treasury_Symbols.time_frame%TYPE ;
23 vp_fundgroup Fv_Treasury_Symbols.fund_group_code%TYPE;
24 vp_trsymbol Fv_Treasury_Symbols.treasury_symbol%TYPE;
25 vp_closing_fyr Gl_Periods.period_year%TYPE ;
26
27 -- ======================================================================
28 -- Other Global Variable Declarations
29 -- ======================================================================
30
31 vg_sob_id Gl_Sets_Of_Books.set_of_books_id%TYPE;
32 vg_coa_id Gl_Sets_Of_Books.chart_of_accounts_id%TYPE;
33 vg_currency Gl_Sets_Of_Books.currency_code%TYPE;
34 vg_start_date Gl_Periods.start_date%TYPE;
35 vg_end_date Gl_Periods.end_date%TYPE;
36 vg_closing_period Gl_Period_Statuses.period_name%TYPE;
37 vg_coy_fyr Gl_Periods.period_year%TYPE;
38 vg_coy_start_date Gl_Periods.start_date%TYPE;
39 vg_coy_period Gl_Period_Statuses.period_name%TYPE;
40 vg_bal_segment Fnd_Id_Flex_Segments.application_column_name%TYPE;
41 vg_acct_segment Fnd_Id_Flex_Segments.application_column_name%TYPE;
42 vg_acct_segnum NUMBER(4);
43 vg_bal_seg_val_opt_code gl_ledgers_public_v.bal_seg_value_option_code%TYPE;
44 vg_trsymbol Fv_Treasury_Symbols.treasury_symbol%TYPE;
45 vg_trsymbol_id Fv_Treasury_Symbols.treasury_symbol_id%TYPE;
46 vg_fund_value Fv_Fund_Parameters.fund_value%TYPE;
47 vg_group_id Fv_Ye_Groups.group_id%TYPE;
48 vg_seq_id Fv_Ye_Group_Sequences.sequence_id%TYPE;
49 vg_seq Fv_Ye_Group_Sequences.SEQUENCE%TYPE;
50 vg_acct_flag Fv_Ye_Sequence_Accounts.account_flag%TYPE;
51 vg_from_acct Fv_Ye_Sequence_Accounts.from_account%TYPE;
52 vg_child_acct Fv_Ye_Sequence_Accounts.from_account%TYPE;
53 vg_to_acct Fv_Ye_Sequence_Accounts.to_account%TYPE;
54 vg_balance_read_flag Fv_Ye_Seq_Bal_Temp.balance_read_flag%TYPE;
55 vg_bal_seq_amt NUMBER;
56 vg_gl_bal_amt NUMBER;
57 vg_coy_dr NUMBER;
58 vg_coy_cr NUMBER;
59 vt_segments Fnd_Flex_Ext.SegmentArray;
60 vg_jrnl_group_id NUMBER;
61 vg_interface_run_id NUMBER;
62
63 e_error EXCEPTION;
64 e_invalid EXCEPTION;
65
66 vg_fundgroup Fv_Treasury_Symbols.fund_group_code%TYPE;
67 vg_acct_val_set_id Fnd_Flex_Values.flex_value_set_id%TYPE;
68 vg_num_segs NUMBER;
69 vg_factsi_attr_exists VARCHAR2(1) ;
70 vg_factsi_bal_cnt NUMBER;
71 vg_factsi_attribute Fv_System_Parameters.factsi_journal_attribute%TYPE;
72
73 vg_public_law_attribute fv_system_parameters.factsii_pub_law_code_attribute%TYPE;
74 vg_advance_type_attribute fv_system_parameters.factsii_advance_type_attribute%TYPE;
75 vg_trf_dept_id_attribute fv_system_parameters.factsii_tr_dept_id_attribute%TYPE;
76 vg_trf_main_acct_attribute fv_system_parameters.factsii_tr_main_acct_attribute%TYPE;
77
78 vg_facts_attributes_setup BOOLEAN ;
79
80 vg_closing_period_num Gl_Period_Statuses.period_num%TYPE;
81 ---------------------------------------------------------------------------------
82 ----------------------------------------------------------------
83 PROCEDURE insert_gl_interface_record(l_amount_dr IN NUMBER,
84 l_amount_cr IN NUMBER,
85 l_reference_1 IN VARCHAR2,
86 l_period_name IN VARCHAR2,
87 l_trading_partner IN VARCHAR2,
88 l_public_law_code IN VARCHAR2 DEFAULT NULL,
89 l_advance_type IN VARCHAR2 DEFAULT NULL,
90 l_trf_dept_id IN VARCHAR2 DEFAULT NULL,
91 l_trf_main_acct IN VARCHAR2 DEFAULT NULL);
92
93 -- ------------------------------------------------------------------
94 -- Procedure Main
95 -- ------------------------------------------------------------------
96 -- Main procedure that is called from the Year End Closing Process
97 -- request set. This procedure calls all the subsequent procedures in
98 -- the Year End Closing process.
99 -- ------------------------------------------------------------------
100 PROCEDURE Main( errbuf OUT NOCOPY VARCHAR2,
101 retcode OUT NOCOPY NUMBER,
102 ledger_id NUMBER,
103 time_frame VARCHAR2,
104 fund_group VARCHAR2,
105 treasury_symbol VARCHAR2,
106 closing_fyr NUMBER,
107 journal_import VARCHAR2) IS
108 l_module_name VARCHAR2(200) ;
109 BEGIN
110 l_module_name := g_module_name || 'Main ';
111
112 vg_sob_id := LEDGER_ID;
113
114 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
115 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'START YEAR END CLOSING PROCESS.....');
116 END IF;
117
118 -- Assign initial values
119 vp_retcode := 0;
120 vp_errbuf := NULL;
121
122 -- Load the parameter global variables
123 vp_timeframe := time_frame;
124 vp_fundgroup := fund_group;
125 vp_trsymbol := treasury_symbol;
126 vp_closing_fyr := closing_fyr;
127 vp_journal_import := journal_import;
128
129 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
130 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'THE PARAMETERS PASSED TO THE YEAR END CLOSING PROCESS ARE: '||
131 'Submit Journal Import = '||vp_journal_import);
132 END IF;
133 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
134 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'TIME FRAME = '||VP_TIMEFRAME||
135
136 ', Fund Group = '||vp_fundgroup);
137 END IF;
138 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
139 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'TREASURY SYMBOL = '||VP_TRSYMBOL||
140
141 ', Closing Fiscal Year = '||TO_CHAR(vp_closing_fyr));
142 END IF;
143
144 -- Get the Sob, Coa and Currency Code
145 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
146 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'GETTING THE SET OF BOOKS,CHART OF ACCOUNTS AND CURRENCY CODE');
147 END IF;
148 Get_Required_Parameters;
149
150 IF (vp_retcode = 0) THEN
151 -- Get the Start Date, End Date and Last Period for the Closing Fyr
152 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
153 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'GETTING THE START DATE, END DATE AND LAST PERIOD '||
154
155 'of the Closing Fiscal Year');
156 END IF;
157 Get_Closing_Fyr_Details;
158 END IF;
159
160 IF (vp_retcode = 0) THEN
161 -- Check if Dynamic Insertion is on
162 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
163 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CHECKING IF DYNAMIC INSERTION IS ON.');
164 END IF;
165 Chk_Dynamic_Insertion;
166 END IF;
167
168 IF (vp_retcode = 0) THEN
169 -- Get the balancing and the natural account segments
170 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
171 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'GETTING THE BALANCING AND THE NATURAL ACCOUNT SEGMENTS.');
172 END IF;
173 Get_Balance_Account_Segments;
174 END IF;
175
176 IF (vp_retcode = 0) THEN
177 -- Check if there are any parent account values for the To Account
178 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
179 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CHECKING THE TO ACCOUNT VALUES IN THE SETUP FORM.....');
180 END IF;
181 Chk_To_Accounts;
182 END IF;
183
184 IF (vp_retcode = 0) THEN
185 -- Purging the Fv_Ye_Seq_Bal_Temp Table
186 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
187 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'PURGING THE FV_YE_SEQ_BAL_TEMP TABLE.');
188 END IF;
189 Purge_Bal_Temp_Table;
190 END IF;
191
192 IF (vp_retcode = 0) THEN
193 -- Checking for the Year End Parameters
194 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
195 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CHECKING FOR THE YEAR END PARAMETERS.');
196 END IF;
197 Check_Year_End_Parameters;
198 END IF;
199
200 IF (vp_retcode = 0) THEN
201 -- Checking for the data in GL
202 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
203 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CHECKING FOR THE DATA IN General Ledger.');
204 END IF;
205 Check_Gl_Data;
206 END IF;
207
208 IF (vp_retcode = 0) THEN
209 IF (vp_journal_import = 'Y') THEN
210 -- Populating the GL_Interface table when submit_journal_import is Y
211 -- Process D
212 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
213 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'POPULATING THE GL_INTERFACE TABLE .');
214 END IF;
215 Populate_Gl_Interface;
216 END IF;
217 END IF;
218
219 IF (vp_retcode = 0) THEN
220 -- Submitting the Execution Report
221 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
222 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'SUBMITTING THE EXECUTION REPORT.');
223 END IF;
224 Submit_Report;
225 END IF;
226
227 IF vp_retcode <> 0 THEN
228 -- Check for errors
229 errbuf := vp_errbuf;
230 retcode := vp_retcode;
231 ROLLBACK;
232 ELSE
233 -- If public law code and other attributes are not set up
234 -- on the system parameters form, end with a warning.
235 IF NOT vg_facts_attributes_setup
236 THEN
237 retcode := 1;
238 errbuf := 'Year End Closing Process completed with warning because the Public Law, Advance,
239 AND Transfer attribute COLUMNS are NOT established ON Define SYSTEM Parameters FORM.';
240 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,'Year End Closing Process completed with warning because the Public Law, Advance,
241 AND Transfer attribute COLUMNS are NOT established ON Define SYSTEM Parameters FORM.');
242 ELSE
243 retcode := 0;
244 errbuf := '** Year End Closing Process completed successfully **';
245 END IF;
246 COMMIT;
247 END IF;
248
249 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
250 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'ENDING THE YEAR END CLOSING PROCESS ......');
251 END IF;
252
253 EXCEPTION
254 WHEN OTHERS THEN
255 ROLLBACK;
256 errbuf := '** Year End Closing Process Failed ** '||SQLERRM;
257 retcode := 2;
258 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
259 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
260 'When Others Exception ' || errbuf );
261 END IF;
262
263 END Main;
264
265 -- ------------------------------------------------------------------
266 -- Procedure Get_Required_Parameters
267 -- ------------------------------------------------------------------
268 -- Get_Required_Parameters procedure is called from Main procedure.
269 -- It gets the sob, coa and the currency code. It also checks for
270 -- the FACTSI Journal Trading Partner attribute for FACTS I processing.
271 -- ------------------------------------------------------------------
272 PROCEDURE Get_Required_Parameters IS
273 l_module_name VARCHAR2(200) ;
274 BEGIN
275 l_module_name := g_module_name ||
276 ' Get_Required_Parameters';
277
278 -- Get the Sob
279 /* vg_sob_id := TO_NUMBER(Fnd_Profile.Value('GL_SET_OF_BKS_ID'));
280 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
281 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' SET OF BOOKS ID = '||TO_CHAR(VG_SOB_ID));
282 END IF;
283
284 -- Get the Coa
285 vg_coa_id := Sys_Context('FV_CONTEXT','CHART_OF_ACCOUNTS_ID');
286 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
287 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CHART OF ACCOUNTS ID = '||TO_CHAR(VG_COA_ID));
288 END IF;
289
290 -- Get the Currency code
291 BEGIN
292 SELECT currency_code
293 INTO vg_currency
294 FROM gl_sets_of_books
295 WHERE set_of_books_id = vg_sob_id;
296
297 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
298 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CURRENCY CODE = '||VG_CURRENCY);
299 END IF;
300 EXCEPTION
301 WHEN NO_DATA_FOUND THEN
302 vp_errbuf := 'Error in Get_Required_Parameters:'||
303 ' Currency Code is not defined';
304 vp_retcode := 1;
305 RETURN;
306 END;
307 */
308 -- Get if bal seg value option is enabled for the ledger
309 -- BSV's are not always assigned to ledgers. Therefore we should not
310 -- enforce BSV assignemnt if there is no BSV flex value set
311 -- is assigned to a ledger.
312 -- Get the COA AND Currency code
313 BEGIN
314 SELECT currency_code ,
315 chart_of_accounts_id ,
316 BAL_SEG_VALUE_OPTION_CODE
317 INTO vg_currency ,
318 vg_coa_id,
319 vg_bal_seg_val_opt_code
320 FROM gl_ledgers_public_v
321 WHERE ledger_id = vg_sob_id;
322
323
324 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
325 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
326 l_module_name,
327 ' CHART OF ACCOUNTS ID = '||TO_CHAR(VG_COA_ID));
328 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
329 l_module_name,
330 ' CURRENCY CODE = '||VG_CURRENCY);
331 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
332 l_module_name,
333 ' BALANCE SEGMENT OPTION CODE = '|| vg_bal_seg_val_opt_code);
334 END IF;
335 EXCEPTION
336 WHEN NO_DATA_FOUND THEN
337 vp_errbuf := 'Error in Get_Required_Parameters:'||
338 ' Currency Code and Chart of Accounts are not defined';
339 vp_retcode := 1;
340 RETURN;
341 END;
342
343
344
345 BEGIN
346 SELECT factsi_journal_attribute,
347 factsii_pub_law_code_attribute,
348 factsii_advance_type_attribute,
349 factsii_tr_dept_id_attribute,
350 factsii_tr_main_acct_attribute
351 INTO vg_factsi_attribute,
352 vg_public_law_attribute,
353 vg_advance_type_attribute,
354 vg_trf_dept_id_attribute,
355 vg_trf_main_acct_attribute
356 FROM Fv_System_Parameters;
357
358 IF (vg_factsi_attribute IS NULL) THEN
359 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
360 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' BALANCES WILL NOT BE CLOSED BY '||
361
362 'the FACTS I F/N and trading partner attributes since the '||
363 'FACTS I Journal Trading Partner field is not populated in '||
364 'the Define Federal System Parameters window.');
365 END IF;
366 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
367 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' TO CLOSE BY FACTS I F/N AND '||
368 'trading partner attributes, delete the journal entries '||
369 'created by this process, if any, enter the attribute in '||
370 'the FACTS I Journal Trading Partner field of the Define '||
371 'Federal System Parameters window, and rerun the Year End '||
372 'Close Program.');
373 END IF;
374 vg_factsi_attr_exists := 'N';
375 ELSE
376 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
377 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' FACTS I JOURNAL TRADING PARTNER ATTRIBUTE IS '||
378
379 vg_factsi_attribute);
380 END IF;
381 vg_factsi_attr_exists := 'Y';
382 END IF;
383
384 -- Set the global variable to false if public law code and other parameters
385 -- are not setup in the define system parameters form.
386 IF (vg_public_law_attribute IS NULL OR
387 vg_advance_type_attribute IS NULL OR
388 vg_trf_dept_id_attribute IS NULL OR
389 vg_trf_main_acct_attribute IS NULL)
390 THEN
391 vg_facts_attributes_setup := FALSE;
392 ELSE
393 vg_facts_attributes_setup := TRUE;
394 END IF;
395
396 EXCEPTION
397 WHEN OTHERS THEN
398 vp_errbuf := 'Error in Get_Required_Parameters:'||
399 ' While determining the FACTS I Journal Attribute.';
400 vp_retcode := 2;
401 END;
402
403 EXCEPTION
404 WHEN OTHERS THEN
405 vp_retcode := 2 ;
406 vp_errbuf := SQLERRM||' -- Error in Get_Required_Parameters procedure.' ;
407 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
408 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
409 'When Others Exception ' || vp_errbuf );
410 END IF;
411 RETURN ;
412 END Get_Required_Parameters;
413
414
415 -- ------------------------------------------------------------------
416 -- Procedure Get_Closing_Fyr_Details
417 -- ------------------------------------------------------------------
418 -- Get_Closing_Fyr_Details procedure is called from Main procedure.
419 -- It gets the start_date,end_date,last_period of the closing fyr.
420 -- It also checks to see if there are any records for the last period
421 -- of the closing year in Fv_Facts1_Period_Balances_v for
422 -- FACTS I processing.
423 -- ------------------------------------------------------------------
424 PROCEDURE Get_Closing_Fyr_Details IS
425 l_module_name VARCHAR2(200) ;
426 BEGIN
427 l_module_name := g_module_name ||
428 ' Get_Closing_Fyr_Details';
429
430 -- Get the Start Date and the End Date of the Closing Fyr
431 BEGIN
432 SELECT MIN(start_date), MAX(end_date)
433 INTO vg_start_date, vg_end_date
434 FROM gl_periods glp, gl_sets_of_books gsob
435 WHERE glp.period_year = vp_closing_fyr
436 AND glp.period_set_name = gsob.period_set_name
437 AND gsob.chart_of_accounts_id = vg_coa_id
438 AND gsob.set_of_books_id = vg_sob_id;
439
440 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
441 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' START DATE OF THE CLOSING FISCAL YEAR = '
442 ||TO_CHAR(vg_start_date));
443 END IF;
444 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
445 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' END DATE OF THE CLOSING FISCAL YEAR = '
446 ||TO_CHAR(vg_end_date));
447 END IF;
448 EXCEPTION
449 WHEN OTHERS THEN
450 vp_retcode := 2 ;
451 vp_errbuf := SQLERRM ||' -- Error in Get_Closing_Fyr_Details '||
452 'procedure,while getting the start/end date of closing fiscal year.';
453 RETURN ;
454 END;
455
456 -- Get the Last Period of the Closing Fyr
457 BEGIN
458 SELECT period_name, period_num
459 INTO vg_closing_period, vg_closing_period_num
460 FROM Gl_Period_Statuses
461 WHERE ledger_id = vg_sob_id
462 AND application_id = 101
463 AND period_year = vp_closing_fyr
464 AND period_num = (SELECT MAX(period_num)
465 FROM gl_period_statuses
466 WHERE ledger_id = vg_sob_id
467 AND application_id = 101
468 AND period_year = vp_closing_fyr);
469
470 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
471 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' LAST PERIOD OF THE CLOSING FISCAL YEAR = '
472 ||vg_closing_period);
473 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' LAST PERIOD NUM OF THE CLOSING FISCAL YEAR = '
474 ||vg_closing_period_num);
475 END IF;
476 EXCEPTION
477 WHEN NO_DATA_FOUND THEN
478 vp_retcode := 1;
479 vp_errbuf := 'Error in Get_Closing_Fyr_Details: '||
480 'Last period is not defined for the Closing Fiscal Year.';
481 RETURN;
482 WHEN OTHERS THEN
483 vp_retcode := 2 ;
484 vp_errbuf := SQLERRM ||
485 ' -- Error in Get_Closing_Fyr_Details procedure,'||
486 'while getting the last period of closing fiscal year.';
487 RETURN ;
488 END;
489
490 BEGIN
491
492 SELECT COUNT(*)
493 INTO vg_factsi_bal_cnt
494 FROM Fv_Facts1_Run
495 WHERE period_num = vg_closing_period_num
496 AND set_of_books_id = vg_sob_id
497 AND fiscal_year = vp_closing_fyr;
498
499 IF (vg_factsi_bal_cnt = 0) THEN
500 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
501 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' BALANCES WILL NOT BE CLOSED BY '||
502
503 'the FACTS I F/N and trading partner attributes since there '||
504 'are no balances in Fv_Facts1_Run table for '||
505 'the period '||vg_closing_period);
506 END IF;
507 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
508 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' TO CLOSE BY FACTS I F/N AND '||
509 'trading partner attributes, delete the journal entries '||
510 'created by this process, if any, run the FACTS I Interface '||
511 'program with all edit checks passed by period '||
512 vg_closing_period||' and rerun the Year End Close Program.');
513 END IF;
514 ELSE
515 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
516 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' BALANCES MAY EXIST FOR FACTS I '||
517 'for the period '||vg_closing_period);
518 END IF;
519 END IF;
520 EXCEPTION
521 WHEN OTHERS THEN
522 vp_errbuf := 'Error in Get_Closing_Fyr_Details:'||
523 ' While determining whether balances exist for FACTS I. ';
524 vp_retcode := 2;
525 END;
526 EXCEPTION
527 WHEN OTHERS THEN
528 vp_errbuf := SQLERRM;
529 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
530 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
531 'When Others Exception ' || vp_errbuf );
532 END IF;
533 RAISE;
534
535
536 END Get_Closing_Fyr_Details;
537
538
539 -- ------------------------------------------------------------------
540 -- Procedure Chk_Dynamic_Insertion
541 -- ------------------------------------------------------------------
542 -- Chk_Dynamic_Insertion procedure is called from Main procedure.
543 -- It checks if dynamic insertion is turned on.
544 -- ------------------------------------------------------------------
545 PROCEDURE Chk_Dynamic_Insertion IS
546 l_module_name VARCHAR2(200) ;
547 e_nodynamic_insert EXCEPTION;
548 vl_dyn_ins_flag Fnd_Id_Flexs.dynamic_inserts_feasible_flag%TYPE;
549 BEGIN
550 l_module_name := g_module_name
551 || ' Chk_Dynamic_Insertion';
552
553 SELECT dynamic_inserts_feasible_flag
554 INTO vl_dyn_ins_flag
555 FROM Fnd_Id_Flexs
556 WHERE application_id = 101
557 AND id_flex_code = 'GL#';
558
559 IF (vl_dyn_ins_flag = 'N') THEN
560 RAISE e_nodynamic_insert;
561 END IF;
562
563 EXCEPTION
564 WHEN e_nodynamic_insert THEN
565 vp_retcode := 1;
566 vp_errbuf := 'Error in Chk_Dynamic_Insertion:Dynamic Inserts '||
567 'Feasible Flag is not set to Yes.';
568 RETURN;
569 WHEN OTHERS THEN
570 vp_retcode := 2;
571 vp_errbuf := SQLERRM ||' -- Error in Chk_Dyanmic_Insertionprocedure.';
572 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
573 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
574 'When Others Exception ' || vp_errbuf );
575 END IF;
576 RETURN;
577 END Chk_Dynamic_Insertion;
578
579 -- ------------------------------------------------------------------
580 -- Procedure Get_Balance_Account_Segments
581 -- ------------------------------------------------------------------
582 -- Get_Balance_Account_Segments procedure is called from Main procedure.
583 -- It gets the balancing and natural account segments, and the
584 -- natural accounting segment number.
585 -- ------------------------------------------------------------------
586 PROCEDURE Get_Balance_Account_Segments IS
587 vl_errcode BOOLEAN;
588 l_module_name VARCHAR2(200) ;
589 CURSOR flex_fields IS
590 SELECT application_column_name
591 FROM fnd_id_flex_segments
592 WHERE id_flex_code = 'GL#'
593 AND id_flex_num = vg_coa_id
594 ORDER BY segment_num;
595
596 l_n_segments NUMBER(4) := 0;
597 vl_acct_segnum number(4);
598 l_column_name fnd_id_flex_segments.application_column_name%TYPE;
599 BEGIN
600
601 l_module_name := g_module_name ||
602 'Get_Balance_Account_Segments ';
603 fv_utility.get_segment_col_names
604 (
605 chart_of_accounts_id => vg_coa_id,
606 acct_seg_name => vg_acct_segment,
607 balance_seg_name => vg_bal_segment,
608 error_code => vl_errcode,
609 error_message => vp_errbuf
610 );
611
612 IF (vl_errcode) THEN
613 vp_retcode := 2 ;
614 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
615 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
616 'Call fv_utility.get_segment_col_names' || vp_errbuf );
617 END IF;
618 RETURN;
619 END IF;
620
621 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
622 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' BALANCING SEGMENT IS '||VG_BAL_SEGMENT);
623 END IF;
624 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
625 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' NATURAL ACCOUNTING SEGMENT IS '||VG_ACCT_SEGMENT);
626 END IF;
627
628 -- Get the Account segment number
629 FOR flex_fields_rec IN flex_fields
630 LOOP
631
632 l_n_segments := l_n_segments + 1;
633 l_column_name := flex_fields_rec.application_column_name;
634
635 --Get the natural account segment column position in array
636 IF (l_column_name = vg_acct_segment) THEN
637 vl_acct_segnum := l_n_segments;
638 END IF;
639 END LOOP;
640
641 vg_acct_segnum := vl_acct_segnum;
642
643 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
644 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' NATURAL ACCOUNTING SEGMENT NUMBER IS ' ||TO_CHAR(vg_acct_segnum));
645 END IF;
646
647 -- Get the Account Flex Value set ID
648 BEGIN
649 SELECT flex_value_set_id
650 INTO vg_acct_val_set_id
651 FROM Fnd_Id_Flex_Segments
652 WHERE application_column_name = vg_acct_segment
653 AND application_id = 101
654 AND id_flex_code = 'GL#'
655 AND id_flex_num = vg_coa_id
656 AND enabled_flag = 'Y';
657 EXCEPTION
658 WHEN OTHERS THEN
659 null;
660 -- vp_retcode := 2 ;
661 -- vp_errbuf := SQLERRM ||' -- Error in Get_Balance_Account_Segments '||
662 -- 'procedure,while getting the Account Flex Value Set Id .';
663 RETURN ;
664
665 END;
666 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
667 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' ACCOUNT FLEX VALUE SET ID IS ' ||TO_CHAR(vg_acct_val_set_id));
668 END IF;
669
670 -- Get the Number of segments in the chart of accounts
671 BEGIN
672 SELECT COUNT(*)
673 INTO vg_num_segs
674 FROM Fnd_Id_Flex_Segments
675 WHERE application_id = 101
676 AND id_flex_code = 'GL#'
677 AND id_flex_num = vg_coa_id
678 AND enabled_flag = 'Y';
679 EXCEPTION
680 WHEN OTHERS THEN
681 vp_retcode := 2 ;
682 vp_errbuf := SQLERRM ||' -- Error in Get_Balance_Account_Segments '||
683 'procedure,while getting the number of segments .';
684 RETURN ;
685 END;
686 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
687 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' NUMBER OF SEGMENTS ARE ' ||TO_CHAR(vg_num_segs));
688 END IF;
689 EXCEPTION
690 WHEN OTHERS THEN
691 vp_retcode := 2 ;
692 vp_errbuf := SQLERRM ||' -- Error in Get_Balance_Account_Segments
693 PROCEDURE.' ;
694 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
695 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
696 'When Others Exception ' || vp_errbuf );
697 END IF;
698
699 RETURN ;
700 END Get_Balance_Account_Segments;
701
702 -- ------------------------------------------------------------------
703 -- Procedure Chk_To_Accounts
704 -- ------------------------------------------------------------------
705 -- Chk_To_Accounts procedure is called from Main procedure.
706 -- It checks if any of the To accounts are parent accounts.
707 -- ------------------------------------------------------------------
708 PROCEDURE Chk_To_Accounts IS
709 CURSOR get_toaccts_cur IS
710 SELECT DISTINCT to_account
711 FROM Fv_Ye_Sequence_Accounts
712 WHERE set_of_books_id = vg_sob_id
713 ORDER BY to_account;
714
715 vl_parent_flag VARCHAR2(1) ;
716 vl_acct Fnd_Flex_Values_Vl.flex_value%TYPE;
717
718 CURSOR get_parentflag_cur IS
719 SELECT summary_flag
720 FROM Fnd_Flex_Values_Vl
721 WHERE flex_value_set_id = vg_acct_val_set_id
722 AND flex_value = vl_acct;
723
724 l_module_name VARCHAR2(200) ;
725
726
727 BEGIN
728
729 l_module_name := g_module_name || ' Chk_To_Accounts ';
730 vl_parent_flag := 'N';
731
732
733 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
734 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' IN CHK_TO_ACCOUNTS PROCEDURE');
735 END IF;
736
737 FOR vc_getaccts IN get_toaccts_cur LOOP
738 vl_acct := vc_getaccts.to_account;
739
740 FOR vc_getparent IN get_parentflag_cur LOOP
741 IF (vc_getparent.summary_flag = 'Y') THEN
742 vp_retcode := 2;
743 vp_errbuf := 'Parent accounts have been defined for the '||
744 'To Account on the Year End Closing Setup form. Please '||
745 'define only child accounts for the To Account.';
746 RETURN;
747 END IF;
748 END LOOP;
749 END LOOP;
750 EXCEPTION
751 WHEN OTHERS THEN
752 vp_retcode := 2 ;
753 vp_errbuf := SQLERRM ||' -- Error in Chk_To_Accounts procedure.' ;
754 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
755 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
756 'When Others Exception ' || vp_errbuf );
757 END IF;
758 RETURN ;
759 END Chk_To_Accounts;
760
761 -- ------------------------------------------------------------------
762 -- Procedure Purge_Bal_Temp_Table
763 -- ------------------------------------------------------------------
764 -- Purge_Bal_Temp_Table procedure is called from Main procedure.
765 -- It deletes from the Temp table.
766 -- ------------------------------------------------------------------
767 PROCEDURE Purge_Bal_Temp_Table IS
768 l_module_name VARCHAR2(200) ;
769 BEGIN
770
771 l_module_name := g_module_name ||'Purge_Bal_Temp_Table ';
772
773 DELETE FROM Fv_Ye_Seq_Bal_Temp WHERE set_of_books_id = vg_sob_id;
774
775 COMMIT;
776 EXCEPTION
777 WHEN OTHERS THEN
778 vp_retcode := 2 ;
779 vp_errbuf := SQLERRM ||' -- Error in Purge_Bal_Temp_Table procedure.' ;
780 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
781 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
782 'When Others Exception ' || vp_errbuf );
783 END IF;
784
785 RETURN ;
786 END Purge_Bal_Temp_Table;
787
788 -- ------------------------------------------------------------------
789 -- Procedure Check_Gl_Data
790 -- ------------------------------------------------------------------
791 -- Check_Gl_Data procedure is called from Main procedure.
792 -- It checks if there are any records in the Temp table.
793 -- ------------------------------------------------------------------
794 PROCEDURE Check_Gl_Data IS
795 vl_reccnt NUMBER;
796 l_module_name VARCHAR2(200) ;
797 e_no_gldata EXCEPTION;
798
799 BEGIN
800
801 l_module_name := g_module_name || 'Check_Gl_Data';
802
803
804 SELECT COUNT(*)
805 INTO vl_reccnt
806 FROM Fv_Ye_Seq_Bal_Temp
807 WHERE set_of_books_id = vg_sob_id;
808
809 IF (vl_reccnt = 0) THEN
810 RAISE e_no_gldata;
811 END IF;
812 EXCEPTION
813 WHEN e_no_gldata THEN
814 vp_retcode := 1;
815 vp_errbuf := 'Year End Closing Process has successfully completed,'||
816 ' but there was no data found in General Ledger, for the Year '||
817 'End account definitions. Journal Import has not been submitted.';
818
819 WHEN OTHERS THEN
820 vp_retcode := 2 ;
821 vp_errbuf := SQLERRM ||' -- Error in Check_Gl_Date procedure.' ;
822 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
823 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
824 'When Others Exception ' || vp_errbuf );
825 END IF;
826 RETURN ;
827 END Check_Gl_Data;
828
829 -- ------------------------------------------------------------------
830 -- Procedure Check_Year_End_Parameters
831 -- ------------------------------------------------------------------
832 -- Check_Year_End_Parameters procedure is called from Main procedure.
833 -- It checks what are the input parameters passed to the Year End
834 -- Process, specifically whether Trsymbol, timeframe and fundgroup
835 -- are provided as input parameters.Based on the parameters provided
836 -- it derives the others and then calls Get_Year_End_Record procedure.
837 -- ------------------------------------------------------------------
838 PROCEDURE Check_Year_End_Parameters
839 IS
840 TYPE t_checkpara IS REF CURSOR;
841 vc_checkpara t_checkpara;
842
843 vl_trsymbol Fv_Treasury_Symbols.treasury_symbol%TYPE;
844 vl_timeframe Fv_Treasury_Symbols.time_frame%TYPE ;
845 vl_fundgroup Fv_Treasury_Symbols.fund_group_code%TYPE;
846 vl_exp_date Fv_Treasury_Symbols.expiration_date%TYPE;
847 vl_can_date Fv_Treasury_Symbols.cancellation_date%TYPE;
848 vl_rec_found_flag VARCHAR2(1) ;
849 vl_fundgroup_temp Fv_Treasury_Symbols.fund_group_code%TYPE;
850 l_module_name VARCHAR2(200) ;
851
852 BEGIN
853
854 l_module_name := g_module_name || 'Check_Year_End_Parameters ';
855 vl_rec_found_flag := 'N';
856
857
858
859 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
860 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' IN CHECK_YEAR_END_PARAMETERS PROCEDURE '||
861 'with the following Parameters passed to the process:');
862 END IF;
863 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
864 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' TREASURY SYMBOL = '||VP_TRSYMBOL||
865 ', Fund Group Code = '||vp_fundgroup||
866 ', Time Frame = '||vp_timeframe);
867 END IF;
868
869 -- check if all fund_values are valid for the General Ledger
870
871 Check_bal_seg_value( vp_fundgroup,
872 vp_timeframe,
873 vp_trsymbol ,
874 vg_sob_id,
875 vg_end_date ) ;
876
877
878 IF (vp_trsymbol IS NOT NULL) THEN
879 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
880 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CASE1: WHEN TREASURY SYMBOL PARAMETER IS PROVIDED.');
881 END IF;
882
883 OPEN vc_checkpara FOR
884 'SELECT time_frame,fund_group_code,
885 expiration_date,cancellation_date
886 FROM Fv_Treasury_Symbols
887 WHERE treasury_symbol = :trsymbol
888 AND set_of_books_id = :sob'
889 USING vp_trsymbol,vg_sob_id;
890
891 FETCH vc_checkpara INTO vl_timeframe, vl_fundgroup,
892 vl_exp_date, vl_can_date;
893
894 IF ((vl_exp_date > vg_end_date) AND (vl_can_date > vg_end_date)
895 AND (vl_timeframe NOT IN ('MULTIPLE','NO_YEAR'))) THEN
896 vp_retcode := 1 ;
897 vp_errbuf := 'The Treasury Symbol '||vp_trsymbol
898 ||' is neither Expired nor Cancelled nor a Multi-Year'||
899 ' or a No-Year Symbol.';
900 vp_errbuf := vp_errbuf || ' Year End Processing is not '||
901 'done for this Treasury Symbol. ';
902
903 IF vc_checkpara%ISOPEN THEN
904 CLOSE vc_checkpara;
905 END IF;
906 RETURN ;
907 ELSE
908 -- Process A
909 Get_Year_End_Record(vp_trsymbol,vl_fundgroup,vl_timeframe);
910 IF (vp_retcode <> 0) THEN
911 RAISE e_error;
912 END IF;
913 END IF;
914
915 CLOSE vc_checkpara;
916
917 ELSIF (vp_fundgroup IS NOT NULL) THEN -- vp_trsymbol
918 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
919 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CASE2: WHEN FUND GROUP CODE PARAMETER IS PROVIDED.');
920 END IF;
921
922 -- IF timeframe is one year, then only the treasury symbols which are
923 -- either expired or cancelled are selected for processsing. For multi-year
924 -- and no-year timeframes, the unexpired treasury symbols also need to be
925 -- processed. Bug 2527452.
926 IF (vp_timeframe = 'SINGLE') THEN -- timeframe 1
927 OPEN vc_checkpara FOR
928 'SELECT treasury_symbol
929 FROM Fv_Treasury_symbols
930 WHERE set_of_books_id = :sob
931 AND time_frame = :timeframe
932 AND fund_group_code = :fundgroup
933 AND ((expiration_date <= :end_date)
934 OR (cancellation_date <= :end_date))
935 ORDER BY treasury_symbol'
936 USING vg_sob_id,
937 vp_timeframe,
938 vp_fundgroup,
939 vg_end_date,
940 vg_end_date;
941
942 ELSE -- timeframe 1
943 OPEN vc_checkpara FOR
944 'SELECT treasury_symbol
945 FROM Fv_Treasury_symbols
946 WHERE set_of_books_id = :sob_id
947 AND time_frame = :timeframe
948 AND fund_group_code = :fundgroup
949 ORDER BY treasury_symbol'
950 USING vg_sob_id,vp_timeframe,vp_fundgroup;
951 END IF; -- timeframe 1
952 ELSE -- vp_trsymbol
953 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
954 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CASE3: WHEN ONLY THE TIME FRAME PARAMETER IS PROVIDED.');
955 END IF;
956
957 -- IF timeframe is one year, then only the treasury symbols which are
958 -- either expired or cancelled are selected for processsing. For multi-year
959 -- and no-year timeframes, the unexpired treasury symbols also need to be
960 -- processed. Bug 2527452.
961 IF (vp_timeframe = 'SINGLE') THEN -- timeframe 2
962 OPEN vc_checkpara FOR
963 'SELECT treasury_symbol,fund_group_code
964 FROM Fv_Treasury_symbols
965 WHERE set_of_books_id = :sob_id
966 AND time_frame = :timeframe
967 AND ((expiration_date <= :end_date)
968 OR (cancellation_date <= :end_date))
969 ORDER BY treasury_symbol'
970 USING vg_sob_id,vp_timeframe,vg_end_date,vg_end_date;
971 ELSE -- timeframe 2
972 OPEN vc_checkpara FOR
973 'SELECT treasury_symbol,fund_group_code
974 FROM Fv_Treasury_symbols
975 WHERE set_of_books_id = :sob_id
976 AND time_frame = :timeframe
977 ORDER BY treasury_symbol'
978 USING vg_sob_id,vp_timeframe;
979 END IF; -- timeframe 2
980
981 END IF; -- vp_trsymbol
982
983 IF (vp_trsymbol IS NULL) THEN -- vp_trsymbol NULL
984 LOOP
985 IF (vp_fundgroup IS NOT NULL) THEN
986 FETCH vc_checkpara INTO vl_trsymbol;
987 ELSE
988 FETCH vc_checkpara INTO vl_trsymbol, vl_fundgroup;
989 END IF;
990
991
992 IF vc_checkpara%FOUND THEN
993 vl_rec_found_flag := 'Y';
994
995 IF (vp_fundgroup IS NULL) THEN
996 vl_fundgroup_temp := vl_fundgroup;
997 ELSE
998 vl_fundgroup_temp := vp_fundgroup;
999 END IF;
1000
1001 -- Process A
1002 Get_Year_End_Record(vl_trsymbol,vl_fundgroup_temp,vp_timeframe);
1003
1004 IF (vp_retcode <> 0) THEN
1005 RAISE e_error;
1006 END IF;
1007 ELSE
1008 IF (vl_rec_found_flag = 'N') THEN
1009 vp_retcode := 1;
1010 IF (vp_fundgroup IS NOT NULL) THEN
1011 vp_errbuf := 'No Treasury Symbols found for '||
1012 'the given Appropriation Group '||vp_fundgroup||
1013 ' and the given Appropriation Time Frame '||vp_timeframe;
1014 ELSE
1015 vp_errbuf := 'No Treasury Symbols found for the '||
1016 'given Appropriation Time Frame '||vp_timeframe;
1017 END IF;
1018 RETURN;
1019 ELSE
1020 EXIT;
1021 END IF; -- vl_rec_found_flag
1022 END IF; -- vc_checkpara
1023 END LOOP;
1024
1025 CLOSE vc_checkpara;
1026
1027 END IF; -- vp_trsymbol NULL
1028
1029 EXCEPTION
1030 WHEN e_error THEN
1031 IF vc_checkpara%ISOPEN THEN
1032 CLOSE vc_checkpara;
1033 END IF;
1034 RETURN;
1035
1036 WHEN OTHERS THEN
1037 vp_retcode := 2 ;
1038 vp_errbuf := SQLERRM ||' -- Error in Check_Year_End_Parameters procedure.' ;
1039 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1040 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
1041 'When Others Exception ' || vp_errbuf );
1042 END IF;
1043
1044 RETURN ;
1045 END Check_Year_End_Parameters;
1046
1047 -- ------------------------------------------------------------------
1048 -- Procedure Get_Year_End_Record
1049 -- ------------------------------------------------------------------
1050 -- Get_Year_End_Record procedure is called from
1051 -- Check_Year_End_Parameters procedure.
1052 -- It gets the treasury_symbol_id and then the group_id from
1053 -- fv_ye_groups table. And then calls the Get_Fund_Value procedure.
1054 -- ------------------------------------------------------------------
1055 PROCEDURE Get_Year_End_Record(trsymbol VARCHAR2,
1056 fundgroup VARCHAR2,
1057 timeframe VARCHAR2 ) IS
1058 CURSOR get_trsymid_cur IS
1059 SELECT treasury_symbol_id
1060 FROM Fv_Treasury_Symbols
1061 WHERE treasury_symbol = trsymbol
1062 AND set_of_books_id = vg_sob_id;
1063
1064 TYPE t_getgroupid IS REF CURSOR;
1065 vc_groupid t_getgroupid;
1066 l_module_name VARCHAR2(200) ;
1067
1068 BEGIN
1069
1070 l_module_name := g_module_name || 'Get_Year_End_Record ';
1071
1072
1073
1074
1075 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1076 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' ******************************************************');
1077 END IF;
1078 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1079 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' IN GET_YEAR_END_RECORD PROCEDURE,'||
1080
1081 'processing the following:');
1082 END IF;
1083 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1084 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' TREASURY SYMBOL: '||TRSYMBOL||' FUND GROUP: '||
1085
1086 fundgroup||' Time Frame: '||timeframe);
1087 END IF;
1088
1089 -- Assign the input parameter trsymbol to vg_trsymbol, so that this can be
1090 -- used in the Log messages.
1091 vg_trsymbol := trsymbol;
1092 vg_fundgroup := fundgroup;
1093
1094 -- Get the Treasury Symbol ID
1095 OPEN get_trsymid_cur;
1096 FETCH get_trsymid_cur INTO vg_trsymbol_id;
1097 CLOSE get_trsymid_cur;
1098 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1099 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PROCESSING THE TREASURY_SYMBOL_ID '||
1100 TO_CHAR(vg_trsymbol_id));
1101 END IF;
1102
1103 OPEN vc_groupid FOR
1104 'SELECT group_id
1105 FROM Fv_Ye_Groups
1106 WHERE treasury_symbol_id = :trsymbol_id
1107 AND fund_group_code = :fundgroup
1108 AND fund_time_frame = :timeframe
1109 AND set_of_books_id = :sob_id'
1110 USING vg_trsymbol_id,fundgroup,timeframe,vg_sob_id;
1111
1112 FETCH vc_groupid INTO vg_group_id;
1113
1114 IF vc_groupid%FOUND THEN --vc_groupid(1)
1115 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1116 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' GROUP ID FOUND FOR TIMEFRAME '||TIMEFRAME||
1117 ' and Fund Group '||fundgroup||' and Treasury Symbol '||
1118 trsymbol||' is '||TO_CHAR(vg_group_id));
1119 END IF;
1120
1121 ELSE --vc_groupid(1)
1122 CLOSE vc_groupid;
1123
1124 -- Looking for group_id for timeframe and fundgroup parameters
1125 OPEN vc_groupid FOR
1126 'SELECT group_id
1127 FROM Fv_Ye_Groups
1128 WHERE treasury_symbol_id IS NULL
1129 AND fund_group_code = :fundgroup
1130 AND fund_time_frame = :timeframe
1131 AND set_of_books_id = :sob_id'
1132 USING fundgroup,timeframe,vg_sob_id;
1133
1134 FETCH vc_groupid INTO vg_group_id;
1135
1136 IF vc_groupid%FOUND THEN --vc_groupid(2)
1137 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1138 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' GROUP ID FOUND FOR TIME FRAME '||TIMEFRAME||
1139 ' and Fund Group '||fundgroup||' is '||TO_CHAR(vg_group_id));
1140 END IF;
1141
1142 ELSE --vc_groupid(2)
1143 CLOSE vc_groupid;
1144
1145 -- Looking for group_id for just the timeframe parameter
1146 OPEN vc_groupid FOR
1147 'SELECT group_id
1148 FROM Fv_Ye_Groups
1149 WHERE treasury_symbol_id IS NULL
1150 AND fund_group_code IS NULL
1151 AND fund_time_frame = :timeframe
1152 AND set_of_books_id = :sob_id'
1153 USING timeframe,vg_sob_id;
1154
1155 FETCH vc_groupid INTO vg_group_id;
1156
1157 IF vc_groupid%FOUND THEN --vc_groupid(3)
1158 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1159 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' GROUP ID FOUND FOR THE TIME FRAME '||TIMEFRAME||
1160 ' is '||TO_CHAR(vg_group_id));
1161 END IF;
1162
1163 ELSE --vc_groupid(3)
1164 CLOSE vc_groupid;
1165
1166 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1167 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' YEAR END CLOSE RECORD IS NOT DEFINED '||
1168
1169 'for any of the combination of :');
1170 END IF;
1171 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1172 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' TIMEFRAME: '||TIMEFRAME||
1173
1174 ', Fund Group: '||fundgroup||', Treasury Symbol: '||trsymbol);
1175 END IF;
1176 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1177 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PROCESSING THE NEXT TREASURY SYMBOL....');
1178 END IF;
1179
1180 -- Process the next treasury symbol
1181 RETURN;
1182 END IF; --vc_groupid(3)
1183 END IF; --vc_groupid(2)
1184 END IF; --vc_groupid(1)
1185
1186 CLOSE vc_groupid;
1187
1188 Get_Fund_Value;
1189
1190 IF (vp_retcode <> 0) THEN
1191 RAISE e_error;
1192 END IF;
1193
1194 EXCEPTION
1195 WHEN e_error THEN
1196 IF vc_groupid%ISOPEN THEN
1197 CLOSE vc_groupid;
1198 END IF;
1199 RETURN;
1200
1201 WHEN OTHERS THEN
1202 IF vc_groupid%ISOPEN THEN
1203 CLOSE vc_groupid;
1204 END IF;
1205 vp_retcode := 2 ;
1206 vp_errbuf := SQLERRM ||' -- Error in Get_Year_End_Record procedure.' ;
1207 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1208 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
1209 'When Others Exception ' || vp_errbuf );
1210 END IF;
1211
1212 RETURN ;
1213 END Get_Year_End_Record;
1214
1215 -- ------------------------------------------------------------------
1216 -- Procedure Get_Fund_Value
1217 -- ------------------------------------------------------------------
1218 -- Get_Fund_Value procedure is called from Get_Year_End_Record procedure.
1219 -- It gets all the fund values for the Trsymbol that is processed.
1220 -- It then calls Determine_Acct_Flag procedure. If journal import is 'Y' then,
1221 -- it calls the Update_Closing_Status procedure.
1222 -- ------------------------------------------------------------------
1223 PROCEDURE Get_Fund_Value IS
1224
1225 CURSOR get_fund_cur IS
1226 SELECT fund_value
1227 FROM Fv_Fund_Parameters
1228 WHERE treasury_symbol_id = vg_trsymbol_id
1229 AND set_of_books_id = vg_sob_id;
1230
1231 vl_rec_found_flag VARCHAR2(1) ;
1232 l_module_name VARCHAR2(200);
1233
1234 BEGIN
1235 vl_rec_found_flag := 'N';
1236
1237 l_module_name := g_module_name ||'Get_Fund_Value ';
1238 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1239 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' IN GET_FUND_VALUE PROC WITH TREASURY SYMBOL ID '||
1240
1241 TO_CHAR(vg_trsymbol_id));
1242 END IF;
1243 OPEN get_fund_cur;
1244 LOOP
1245 FETCH get_fund_cur INTO vg_fund_value;
1246 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1247 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' --------------------------------------------------');
1248 END IF;
1249
1250 IF get_fund_cur%FOUND THEN -- get_fund_cur%found
1251 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1252 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PROCESSING THE FUND '||VG_FUND_VALUE||
1253
1254 ' for the Treasury Symbol '||vg_trsymbol);
1255 END IF;
1256
1257 vl_rec_found_flag := 'Y';
1258
1259 -- Process B
1260 Determine_Acct_Flag;
1261
1262 IF (vp_retcode <> 0) THEN
1263 RAISE e_error;
1264 END IF;
1265
1266 -- Process C
1267 IF (vp_journal_import = 'Y') THEN
1268 Update_Closing_Status;
1269
1270 IF (vp_retcode <> 0) THEN
1271 RAISE e_error;
1272 END IF;
1273 END IF;
1274
1275 ELSE -- get_fund_cur%found
1276 IF (vl_rec_found_flag = 'N') THEN
1277 IF (vp_trsymbol IS NOT NULL) THEN
1278 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1279 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' NO FUND VALUE FOUND FOR THE TREASURY SYMBOL '
1280
1281 ||vg_trsymbol);
1282 END IF;
1283 vp_retcode := 1;
1284 vp_errbuf := 'No Fund Value found for the Treasury Symbol '||
1285 vg_trsymbol;
1286 ELSE
1287 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1288 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' NO FUND VALUE FOUND FOR THE TREASURY SYMBOL '
1289 ||vg_trsymbol);
1290 END IF;
1291 END IF;
1292 RETURN;
1293 ELSE
1294 EXIT;
1295 END IF;
1296 END IF; -- get_fund_cur%found
1297
1298 END LOOP;
1299 CLOSE get_fund_cur;
1300
1301 EXCEPTION
1302 WHEN e_error THEN
1303 IF get_fund_cur%ISOPEN THEN
1304 CLOSE get_fund_cur;
1305 END IF;
1306 RETURN;
1307
1308 WHEN OTHERS THEN
1309 IF get_fund_cur%ISOPEN THEN
1310 CLOSE get_fund_cur;
1311 END IF;
1312 vp_retcode := 2 ;
1313 vp_errbuf := SQLERRM ||' -- Error in Get_Fund_Value procedure.' ;
1314 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1315 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
1316 'When Others Exception ' || vp_errbuf );
1317 END IF;
1318
1319 RETURN ;
1320
1321 END Get_Fund_Value;
1322
1323 -- ------------------------------------------------------------------
1324 -- Procedure Determine_Acct_Flag
1325 -- ------------------------------------------------------------------
1326 -- Determine_Acct_Flag procedure is called from Get_Fund_Value procedure.
1327 -- It determines whether the fund is expired,canceled or carryover.
1328 -- It then calls the Get_Year_End_SeqAcct_Info procedure.
1329 -- ------------------------------------------------------------------
1330 PROCEDURE Determine_Acct_Flag IS
1331 CURSOR acctflag_cur IS
1332 SELECT fts.expiration_date,
1333 fts.cancellation_date,
1334 fts.time_frame
1335 FROM Fv_Treasury_Symbols fts, Fv_Fund_Parameters ffp
1336 WHERE fts.treasury_symbol_id = vg_trsymbol_id
1337 AND fts.treasury_symbol_id = ffp.treasury_symbol_id
1338 AND ffp.fund_value = vg_fund_value
1339 AND fts.treasury_symbol_id = ffp.treasury_symbol_id
1340 AND fts.set_of_books_id = vg_sob_id
1341 AND ffp.set_of_books_id = fts.set_of_books_id;
1342
1343 vl_exp_date Fv_Treasury_Symbols.expiration_date%TYPE;
1344 vl_can_date Fv_Treasury_Symbols.cancellation_date%TYPE;
1345 vl_timeframe Fv_Treasury_Symbols.time_frame%TYPE;
1346 vl_status_flag VARCHAR2(1);
1347 l_module_name VARCHAR2(200) ;
1348 BEGIN
1349
1350 l_module_name := g_module_name || 'Determine_Acct_Flag ' ;
1351
1352 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1353 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' IN THE DETERMINE_ACCT_FLAG PROCEDURE:WITH TREASURY '||
1354
1355 'Symbol = '||vg_trsymbol||' and Fund Value = '||vg_fund_value);
1356 END IF;
1357
1358 -- Get the expiration and cancellation date for the treasury symbol
1359 OPEN acctflag_cur;
1360 FETCH acctflag_cur INTO vl_exp_date,vl_can_date,vl_timeframe;
1361 CLOSE acctflag_cur;
1362
1363 -- Determing whether the fund is expired, cancelled or unexpired
1364 IF ((vl_exp_date <= vg_end_date) AND (vl_can_date <= vg_end_date)) THEN
1365 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1366 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' FUND IS BOTH EXPIRED AND CANCELLED...');
1367 END IF;
1368 -- Set status flag to B(both expired and cancelled)
1369 vl_status_flag := 'B';
1370 ELSIF ((vl_exp_date <= vg_end_date) AND (vl_can_date > vg_end_date)) THEN
1371 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1372 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' FUND IS EXPIRED BUT NOT CANCELLED...');
1373 END IF;
1374 -- Set status flag to E(expired)
1375 vl_status_flag := 'E';
1376 ELSIF ((vl_exp_date > vg_end_date) AND (vl_can_date <= vg_end_date)) THEN
1377 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1378 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' FUND IS CANCELLED BUT NOT EXPIRED...');
1379 END IF;
1380 -- Set status flag to C(cancelled)
1381 vl_status_flag := 'C';
1382 ELSIF (vl_timeframe IN ('NO_YEAR','MULTIPLE')) THEN
1383 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1384 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' FUND IS UNEXPIRED...');
1385 END IF;
1386 -- Set status flag to U(unexpired)
1387 vl_status_flag := 'U';
1388 END IF;
1389 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1390 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' VL_STATUS_FLAG '||VL_STATUS_FLAG);
1391 END IF;
1392
1393 -- Based on status flag, set vg_acct_flag and call Get_Year_End_SeqAcct_Info proc
1394 IF (vl_status_flag = 'B') THEN
1395 -- Process all expired records first and then Cancelled records
1396 FOR i IN 1..2 LOOP
1397 SELECT DECODE(i,1,'Expired',2,'Canceled')
1398 INTO vg_acct_flag
1399 FROM DUAL;
1400 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1401 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' VG_ACCT_FLAG '||VG_ACCT_FLAG);
1402 END IF;
1403
1404 -- Get the Sequence Information
1405 Get_Year_End_SeqAcct_Info;
1406
1407 IF (vp_retcode <> 0) THEN
1408 RAISE e_error;
1409 END IF;
1410 END LOOP;
1411 ELSE
1412 SELECT DECODE(vl_status_flag,'E','Expired','C','Canceled','U','Unexpired')
1413 INTO vg_acct_flag
1414 FROM DUAL;
1415 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1416 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' VG_ACCT_FLAG '||VG_ACCT_FLAG);
1417 END IF;
1418
1419 -- Get the Sequence Information
1420 Get_Year_End_SeqAcct_Info;
1421
1422 IF (vp_retcode <> 0) THEN
1423 RAISE e_error;
1424 END IF;
1425 END IF;
1426
1427 EXCEPTION
1428 WHEN e_error THEN
1429 RETURN;
1430
1431 WHEN OTHERS THEN
1432 vp_retcode := 2 ;
1433 vp_errbuf := SQLERRM ||' -- Error in Detemine_Acct_Flag procedure.' ;
1434 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1435 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
1436 'When Others Exception ' || vp_errbuf );
1437 END IF;
1438
1439 RETURN ;
1440 END Determine_Acct_Flag;
1441
1442 -- ------------------------------------------------------------------
1443 -- Procedure Get_Year_End_SeqAcct_Info
1444 -- ------------------------------------------------------------------
1445 -- Get_Year_End_SeqAcct_Info procedure is called from Determine_Acct_Flag
1446 -- procedure. It gets the sequences, and for each sequence it gets the
1447 -- account entries and processes them.It then calls the procedure
1448 -- Determine_Child_Accounts for each account entry.
1449 -- ------------------------------------------------------------------
1450 PROCEDURE Get_Year_End_SeqAcct_Info IS
1451
1452 CURSOR get_seq_cur IS
1453 SELECT sequence_id,SEQUENCE
1454 FROM Fv_Ye_Group_Sequences
1455 WHERE group_id = vg_group_id
1456 AND set_of_books_id = vg_sob_id
1457 ORDER BY SEQUENCE;
1458
1459 CURSOR get_acct_cur IS
1460 SELECT from_account,to_account
1461 FROM Fv_Ye_Sequence_Accounts
1462 WHERE sequence_id = vg_seq_id
1463 AND account_flag = vg_acct_flag
1464 AND set_of_books_id = vg_sob_id
1465 ORDER BY order_by_ctr;
1466
1467 CURSOR get_count_cur IS
1468 SELECT COUNT(*)
1469 FROM Fv_Ye_Sequence_Accounts
1470 WHERE sequence_id = vg_seq_id
1471 AND set_of_books_id = vg_sob_id;
1472
1473 vl_seqrec_flag VARCHAR2(1) ;
1474 vl_acctrec_flag VARCHAR2(1) ;
1475 vl_cnt NUMBER ;
1476 l_module_name VARCHAR2(200) ;
1477
1478 BEGIN
1479
1480 l_module_name := g_module_name || ' Get_Year_End_SeqAcct_Info ';
1481 vl_seqrec_flag := 'N';
1482 vl_acctrec_flag := 'N';
1483 vl_cnt := 0;
1484
1485
1486
1487
1488 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1489 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' IN THE GET_YEAR_END_SEQACCT_INFO PROCEDURE...');
1490 END IF;
1491
1492 OPEN get_seq_cur;
1493 LOOP -- getseq loop
1494 FETCH get_seq_cur INTO vg_seq_id, vg_seq;
1495
1496 IF get_seq_cur%FOUND THEN -- get_seq_cur
1497 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1498 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PROCESSING SEQUENCE '||TO_CHAR(VG_SEQ)||
1499 ' and sequence_id '||TO_CHAR(vg_seq_id));
1500 END IF;
1501
1502 vl_seqrec_flag := 'Y';
1503
1504 OPEN get_acct_cur;
1505 LOOP -- getacct loop
1506 FETCH get_acct_cur INTO vg_from_acct,vg_to_acct;
1507
1508 IF get_acct_cur%FOUND THEN -- get_acct_cur
1509 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1510 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PROCESSING THE RECORD WITH ACCOUNT '||
1511 'flag:'||vg_acct_flag||', From Account:'||vg_from_acct
1512 ||', To Account:'|| vg_to_acct);
1513 END IF;
1514
1515 vl_acctrec_flag := 'Y';
1516
1517 -- Call Determine_Child_Accounts;
1518 Determine_Child_Accounts;
1519
1520 IF (vp_retcode <> 0) THEN
1521 RAISE e_error;
1522 END IF;
1523 ELSE -- get_acct_cur
1524 IF (vl_acctrec_flag = 'N') THEN
1525 OPEN get_count_cur;
1526 FETCH get_count_cur INTO vl_cnt;
1527 CLOSE get_count_cur;
1528
1529 IF (vl_cnt > 0) THEN
1530 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1531 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' NO ACCOUNT INFORMATION FOUND '||
1532 'with '||vg_acct_flag||' appropriation status for '||
1533 'the Sequence '||TO_CHAR(vg_seq));
1534 END IF;
1535 ELSE
1536 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1537 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' NO ACCOUNT INFORMATION FOUND '||
1538 'for the Sequence '||TO_CHAR(vg_seq));
1539 END IF;
1540 END IF;
1541 --RETURN;
1542 ELSE
1543 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1544 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1545 ' PROCESSING IS DONE FOR THE SEQUENCE '
1546 ||TO_CHAR(vg_seq));
1547 END IF;
1548 --EXIT;
1549 END IF;
1550 EXIT;
1551 END IF; -- get_acct_cur
1552
1553 END LOOP; -- getacct loop
1554 CLOSE get_acct_cur;
1555 ELSE -- get_seq_cur
1556 IF (vl_seqrec_flag = 'N') THEN
1557 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1558 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' NO SEQUENCES FOUND FOR THE GROUP ID '
1559 ||TO_CHAR(vg_group_id));
1560 END IF;
1561 RETURN;
1562 ELSE
1563 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1564 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PROCESSING IS DONE FOR THE GROUP ID '
1565
1566 ||TO_CHAR(vg_group_id));
1567 END IF;
1568 EXIT;
1569 END IF;
1570 END IF; -- get_seq_cur
1571 END LOOP; -- getseq loop
1572 CLOSE get_seq_cur;
1573
1574 EXCEPTION
1575 WHEN e_error THEN
1576 IF get_acct_cur%ISOPEN THEN
1577 CLOSE get_acct_cur;
1578 END IF;
1579 RETURN;
1580
1581 WHEN OTHERS THEN
1582 vp_retcode := 2 ;
1583 vp_errbuf := SQLERRM ||' -- Error in Get_Year_End_SeqAcct_Info procedure.' ;
1584 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1585 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
1586 'When Others Exception ' || vp_errbuf );
1587 END IF;
1588 RETURN ;
1589
1590 END Get_Year_End_SeqAcct_Info;
1591
1592
1593 -- ------------------------------------------------------------------
1594 -- Procedure Determine_Child_Accounts
1595 -- ------------------------------------------------------------------
1596 -- Determine_Child_Accounts procedure is called from Get_Year_End_SeqAcct_Info
1597 -- procedure. For a record with year_entry= Closing, this procedure
1598 -- gets the balance_read_flag from the Temp table, and then calls
1599 -- Get_Balances procedure. For a record with year_entry = Carryover,
1600 -- it calculates the balances from the previous entry, and then
1601 -- calls the procedure Determine_DrCr.
1602 -- ------------------------------------------------------------------
1603 PROCEDURE Determine_Child_Accounts IS
1604 vl_parent_cnt NUMBER;
1605 vl_child_low Fnd_Flex_Values_Vl.flex_value%TYPE;
1606 vl_child_high Fnd_Flex_Values_Vl.flex_value%TYPE;
1607
1608 TYPE t_getbal_cnt IS REF CURSOR;
1609 vc_getbal_cnt t_getbal_cnt;
1610 vl_select VARCHAR2(2000);
1611 vl_bal_cnt NUMBER;
1612
1613 CURSOR get_hierarchies_cur IS
1614 SELECT child_flex_value_low, child_flex_value_high
1615 FROM Fnd_Flex_Value_Hierarchies
1616 WHERE parent_flex_value = vg_from_acct
1617 AND flex_value_set_id = vg_acct_val_set_id;
1618
1619 CURSOR get_child_values_cur IS
1620 SELECT flex_value
1621 FROM Fnd_Flex_Values_Vl
1622 WHERE flex_value_set_id = vg_acct_val_set_id
1623 AND flex_value BETWEEN vl_child_low AND vl_child_high
1624 ORDER BY flex_value;
1625
1626 l_module_name VARCHAR2(200) ;
1627
1628 BEGIN
1629
1630 l_module_name := g_module_name || 'Determine_Child_Accounts ';
1631
1632
1633 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1634 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' IN DETERMINE_CHILD_ACCOUNTS PROCEDURE...');
1635 END IF;
1636
1637 BEGIN
1638 -- Check to see if the from account is a parent
1639 SELECT COUNT(*)
1640 INTO vl_parent_cnt
1641 FROM Fnd_Flex_Values_Vl
1642 WHERE flex_value_set_id = vg_acct_val_set_id
1643 AND summary_flag = 'Y'
1644 AND flex_value = vg_from_acct;
1645
1646 EXCEPTION
1647 WHEN OTHERS THEN
1648 vp_retcode := 2;
1649 vp_errbuf := SQLERRM || '-- Error in Determine_Child_Accounts procedure,'||
1650 'while deriving the balance_read_flag.';
1651 RETURN;
1652 END;
1653
1654 IF (vl_parent_cnt = 0) THEN -- parent_cnt
1655 -- the from account is not a parent
1656 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1657 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' FROM ACCOUNT IS NOT A PARENT');
1658 END IF;
1659 vg_child_acct := vg_from_acct;
1660
1661 -- Call Determine_Balance_Read_Flag procedure
1662 Determine_Balance_Read_Flag;
1663
1664 IF (vp_retcode <> 0) THEN
1665 RAISE e_error;
1666 END IF;
1667 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1668 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' BALANCE_READ_FLAG IS '||
1669 vg_balance_read_flag);
1670 END IF;
1671
1672 -- Call Get_Balances procedure to get balances for the from account
1673 Get_Balances;
1674
1675 IF (vp_retcode <> 0) THEN
1676 RAISE e_error;
1677 END IF;
1678 ELSE -- parent_cnt
1679 -- the from account is a parent
1680 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1681 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' FROM ACCOUNT IS A PARENT');
1682 END IF;
1683
1684 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1685 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CHECKING TO SEE IF THE PARENT HAS '||
1686 'any balances.');
1687 END IF;
1688
1689 vl_select := 'SELECT COUNT(*)
1690 FROM Gl_Balances glb,Gl_Code_Combinations gcc
1691 WHERE glb.code_combination_id = gcc.code_combination_id
1692 AND gcc.'||vg_bal_segment||' = :fund_value'||
1693 ' AND gcc.'||vg_acct_segment|| ' = :from_acct'||
1694 ' AND gcc.summary_flag = '||''''||'N'||''''||
1695 ' AND gcc.template_id IS NULL
1696 AND glb.actual_flag = '||''''||'A'||''''||
1697 ' AND glb.ledger_id = :sob
1698 AND gcc.chart_of_accounts_id = :coa
1699 AND glb.period_year = :closing_fyr
1700 AND glb.period_name = :closing_period
1701 AND glb.currency_code = :currency';
1702
1703 -- Open thru' native dynamic sql
1704 EXECUTE IMMEDIATE vl_select INTO vl_bal_cnt USING
1705 vg_fund_value,vg_from_acct,vg_sob_id,
1706 vg_coa_id,vp_closing_fyr,vg_closing_period,vg_currency;
1707
1708 IF (vl_bal_cnt > 0) THEN -- bal_check
1709 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1710 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PARENT ACCOUNT HAS BALANCES IN '||
1711 'General Ledger. This is the scenario, when a child account '||
1712 'has later been defined as a parent account.Getting the balances.');
1713 END IF;
1714
1715 vg_child_acct := vg_from_acct;
1716 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1717 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PROCESSING THE ACCT:'||
1718 vg_child_acct);
1719 END IF;
1720
1721 -- Call Process_Acct procedure
1722 Process_Acct;
1723
1724 IF (vp_retcode <> 0) THEN
1725 RAISE e_error;
1726 END IF;
1727
1728 ELSE -- bal_check
1729 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1730 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PARENT ACCOUNT HAS NO BALANCES IN '||
1731 'General Ledger.');
1732 END IF;
1733 END IF; -- bal_check
1734
1735 -- For each child hierarchy range, get low and high value
1736 FOR vc_hierarchies IN get_hierarchies_cur LOOP -- Hierarchies
1737 vl_child_low := vc_hierarchies.child_flex_value_low;
1738 vl_child_high := vc_hierarchies.child_flex_value_high;
1739 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1740 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PROCESSING HIERARCHY WITH LOW: '||
1741 vl_child_low||' and high: '||vl_child_high);
1742 END IF;
1743
1744 -- For each child hierarchy, find the child values
1745 FOR vc_children IN get_child_values_cur LOOP -- children
1746 -- set the child account
1747 vg_child_acct := vc_children.flex_value;
1748 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1749 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PROCESSING THE CHILD VALUE: '||
1750 vg_child_acct);
1751 END IF;
1752
1753 -- Call Process_Acct procedure
1754 Process_Acct;
1755
1756 IF (vp_retcode <> 0) THEN
1757 RAISE e_error;
1758 END IF;
1759
1760 END LOOP; -- children
1761 END LOOP; -- Hierarchies
1762 END IF; -- parent_cnt
1763
1764 EXCEPTION
1765 WHEN e_error THEN
1766 RETURN;
1767
1768 WHEN OTHERS THEN
1769 vp_retcode := 2 ;
1770 vp_errbuf := SQLERRM ||' -- Error in Determine_Child_Accounts procedure.' ;
1771 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1772 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
1773 'When Others Exception ' || vp_errbuf );
1774 END IF;
1775 RETURN ;
1776
1777 END Determine_Child_Accounts;
1778
1779 -- ------------------------------------------------------------------
1780 -- Procedure Process_Acct
1781 -- ------------------------------------------------------------------
1782 -- Process_Acct procedure is called from
1783 -- Determine_Child_Accounts procedure. This procedure calls the
1784 -- Determine_Balance_Read_Flag and Get_Balances procedures.
1785 -- ------------------------------------------------------------------
1786 PROCEDURE Process_Acct IS
1787 l_module_name VARCHAR2(200) ;
1788
1789 BEGIN
1790 l_module_name := g_module_name || 'Process_Acct ';
1791 -- Call Determine_Balance_Read_Flag procedure
1792 Determine_Balance_Read_Flag;
1793
1794 IF (vp_retcode <> 0) THEN
1795 RAISE e_error;
1796 END IF;
1797 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1798 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' BALANCE_READ_FLAG IS '||
1799 vg_balance_read_flag);
1800 END IF;
1801
1802 -- Call Get_Balances procedure to get balances for the from account
1803 Get_Balances;
1804
1805 IF (vp_retcode <> 0) THEN
1806 RAISE e_error;
1807 END IF;
1808
1809 EXCEPTION
1810 WHEN e_error THEN
1811 RETURN;
1812
1813 WHEN OTHERS THEN
1814 vp_retcode := 2 ;
1815 vp_errbuf := SQLERRM ||' -- Error in Process_Acct procedure.' ;
1816 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1817 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
1818 'When Others Exception ' || vp_errbuf );
1819 END IF;
1820 RETURN ;
1821 END Process_Acct;
1822
1823 -- ------------------------------------------------------------------
1824 -- Procedure Determine_Balance_Read_Flag
1825 -- ------------------------------------------------------------------
1826 -- Determine_Balance_Read_Flag procedure is called from
1827 -- Process_Acct procedure. This procedure determines the
1828 -- balance_read_flag from the Temp table.
1829 -- ------------------------------------------------------------------
1830 PROCEDURE Determine_Balance_Read_Flag IS
1831 l_module_name VARCHAR2(200) ;
1832 BEGIN
1833
1834
1835 l_module_name := g_module_name ||
1836 'Determine_Balance_Read_Flag ';
1837 vg_balance_read_flag := 'N';
1838
1839 SELECT DISTINCT balance_read_flag
1840 INTO vg_balance_read_flag
1841 FROM Fv_Ye_Seq_Bal_Temp
1842 WHERE account_seg = vg_child_acct
1843 AND balance_seg = vg_fund_value
1844 AND set_of_books_id = vg_sob_id
1845 AND fiscal_year = vp_closing_fyr
1846 AND balance_read_flag = 'Y'
1847 AND group_id = vg_group_id;
1848
1849 EXCEPTION
1850 WHEN NO_DATA_FOUND THEN
1851 vg_balance_read_flag := 'N';
1852 WHEN OTHERS THEN
1853 vp_retcode := 2;
1854 vp_errbuf := SQLERRM ||
1855 ' -- Error in Determine_Balance_Read_Flag procedure.' ;
1856 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1857 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
1858 'When Others Exception ' || vp_errbuf );
1859 END IF;
1860 RETURN;
1861 END Determine_Balance_Read_Flag;
1862
1863 -- ------------------------------------------------------------------
1864 -- Procedure Get_Balances
1865 -- ------------------------------------------------------------------
1866 -- Get_Balances procedure is called from Process_Acct
1867 -- procedure. It gets the balances from Gl_Balances , and handles
1868 -- the different scenarios and then calls Determine_DrCr procedure.
1869 -- ------------------------------------------------------------------
1870 PROCEDURE Get_Balances IS
1871 vl_rec_found_flag VARCHAR2(1) ;
1872
1873 TYPE t_getbal IS REF CURSOR;
1874 vc_getbal t_getbal;
1875 vl_select VARCHAR2(2000);
1876
1877 vl_ccid Gl_Code_Combinations.code_combination_id%TYPE;
1878
1879 TYPE t_ccidtable IS TABLE OF Gl_Code_Combinations.code_combination_id%TYPE
1880 INDEX BY BINARY_INTEGER;
1881 vt_ccid t_ccidtable;
1882 i BINARY_INTEGER ;
1883 vl_ccid_cnt NUMBER;
1884 vl_exists VARCHAR2(1) ;
1885
1886 CURSOR get_sumtemp_cur IS
1887 SELECT NVL(SUM(bal_seq_amt),0) amt,code_combination_id
1888 FROM Fv_Ye_Seq_Bal_Temp
1889 WHERE account_seg = vg_child_acct
1890 AND balance_seg = vg_fund_value
1891 AND set_of_books_id = vg_sob_id
1892 AND group_id = vg_group_id
1893 AND fiscal_year = vp_closing_fyr
1894 AND balance_read_flag = 'N'
1895 GROUP BY code_combination_id;
1896
1897 CURSOR get_sumtemp_seqcur IS
1898 SELECT NVL(SUM(bal_seq_amt),0) amt,code_combination_id
1899 FROM Fv_Ye_Seq_Bal_Temp
1900 WHERE account_seg = vg_child_acct
1901 AND balance_seg = vg_fund_value
1902 AND set_of_books_id = vg_sob_id
1903 AND group_id = vg_group_id
1904 AND fiscal_year = vp_closing_fyr
1905 AND balance_read_flag = 'Y'
1906 AND SEQUENCE IN (SELECT MAX(SEQUENCE)
1907 FROM Fv_Ye_Seq_Bal_Temp g
1908 WHERE g.account_seg = vg_from_acct
1909 AND g.balance_seg = vg_fund_value
1910 AND g.set_of_books_id = vg_sob_id
1911 AND g.group_id = vg_group_id
1912 AND g.balance_read_flag = 'Y')
1913 GROUP BY code_combination_id;
1914
1915 CURSOR get_addccid_cur IS
1916 SELECT NVL(SUM(bal_seq_amt),0) amt,code_combination_id
1917 FROM Fv_Ye_Seq_Bal_Temp
1918 WHERE account_seg = vg_child_acct
1919 AND balance_seg = vg_fund_value
1920 AND set_of_books_id = vg_sob_id
1921 AND group_id = vg_group_id
1922 AND fiscal_year = vp_closing_fyr
1923 AND balance_read_flag = 'N'
1924 AND SEQUENCE < vg_seq
1925 GROUP BY code_combination_id;
1926
1927 l_module_name VARCHAR2(200) ;
1928
1929 BEGIN
1930 l_module_name := g_module_name ||
1931 ' Get_Balances ';
1932 vl_rec_found_flag := 'N';
1933 i := 1;
1934 vl_exists := 'N';
1935 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1936 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' IN THE GET_BALANCES PROCEDURE, '||
1937
1938 'processing the account '||vg_child_acct||
1939 ' with balance_read_flag = '||vg_balance_read_flag);
1940 END IF;
1941 -- Purge the Pl/Sql table
1942 vt_ccid.DELETE;
1943
1944 /* If there are no records defined in the Fv_Ye_Seq_Bal_Temp table *
1945 * for the fund and the from acct within the group that is being *
1946 * processed. This is the scenario when vg_balance_read_flag = N */
1947 IF (vg_balance_read_flag = 'N') THEN -- vg_balance_read_flag
1948 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1949 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CASE: WHEN BALANCE_READ_FLAG = N');
1950 END IF;
1951
1952 vl_select := 'SELECT glb.code_combination_id,
1953 NVL(SUM(NVL(begin_balance_dr,0) + NVL(period_net_dr,0)) -
1954 SUM(NVL(begin_balance_cr,0) + NVL(period_net_cr,0)),0)
1955 FROM Gl_Balances glb,Gl_Code_Combinations gcc
1956 WHERE glb.code_combination_id = gcc.code_combination_id
1957 AND gcc.'||vg_bal_segment|| ' = :fund_value'||
1958 ' AND gcc.'||vg_acct_segment|| ' = :child_acct'||
1959 ' AND gcc.summary_flag = '||''''||'N'||''''||
1960 ' AND gcc.template_id IS NULL
1961 AND glb.actual_flag = '||''''||'A'||''''||
1962 ' AND glb.ledger_id = :sob
1963 AND gcc.chart_of_accounts_id = :coa
1964 AND glb.period_year = :closing_fyr
1965 AND glb.period_name = :closing_period
1966 AND glb.currency_code = :currency
1967 GROUP BY glb.code_combination_id ';
1968
1969 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,vl_select);
1970 -- Open thru' native dynamic sql
1971 OPEN vc_getbal FOR vl_select USING vg_fund_value,
1972 vg_child_acct,
1973 vg_sob_id,
1974 vg_coa_id,
1975 vp_closing_fyr,
1976 vg_closing_period,
1977 vg_currency;
1978
1979 -- Fetch the records
1980 LOOP -- loop for balances
1981 vg_gl_bal_amt := 0;
1982
1983 FETCH vc_getbal INTO vl_ccid,vg_gl_bal_amt;
1984
1985 IF (vc_getbal%FOUND) THEN -- vc_getbal found
1986 vl_rec_found_flag := 'Y';
1987
1988 vg_bal_seq_amt := 0;
1989 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1990 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PROCESSING THE CCID '
1991 ||TO_CHAR(vl_ccid)||
1992 ' and GL balance amt(vg_gl_bal_amt) is '
1993 ||TO_CHAR(vg_gl_bal_amt));
1994 END IF;
1995
1996 BEGIN
1997 SELECT NVL(SUM(bal_seq_amt),0)
1998 INTO vg_bal_seq_amt
1999 FROM Fv_Ye_Seq_Bal_Temp
2000 WHERE account_seg = vg_child_acct
2001 AND balance_seg = vg_fund_value
2002 AND set_of_books_id = vg_sob_id
2003 AND group_id = vg_group_id
2004 AND code_combination_id = vl_ccid
2005 AND fiscal_year = vp_closing_fyr
2006 AND balance_read_flag = 'N';
2007
2008 EXCEPTION
2009 WHEN NO_DATA_FOUND THEN
2010 vg_bal_seq_amt := 0;
2011 END;
2012 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2013 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' AMOUNT FROM TEMP TABLE, '||
2014 'for the ccid being processed is ' ||TO_CHAR(vg_bal_seq_amt));
2015 END IF;
2016
2017 -- set the balance_read_flag
2018 vg_balance_read_flag := 'Y';
2019
2020 -- Sum the amt from the gl_balances and the temp table.
2021 vg_bal_seq_amt := vg_bal_seq_amt + vg_gl_bal_amt;
2022 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2023 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' TOTAL AMOUNT IS '||
2024 TO_CHAR(vg_bal_seq_amt));
2025 END IF;
2026 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2027 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CALLING DETERMINE_DRCR PROC.'||
2028 'Case 1D');
2029 END IF;
2030 -- Call Determine_DrCr procedure
2031 -- Case 1D:
2032 Determine_DrCr(vl_ccid);
2033
2034 IF (vp_retcode <> 0) THEN
2035 RAISE e_error;
2036 END IF;
2037
2038 -- put the ccid into the pl/sql table
2039 vt_ccid(i) := vl_ccid;
2040 i := i + 1;
2041
2042 ELSE -- vc_getbal found
2043 /* Case when no balances are found */
2044 /* this else is if vc_getbal not found */
2045 /*this code in else part is necessary to handle the*
2046 *following situation:say group_id 1 has two seq's *
2047 *say 10 and 20. Sequence 10 has EXP CY 4700 4650 *
2048 *and EXP CY 4610 4650. Sequence 20 has EXP CY 4650*
2049 *4800. In this situation to get all the balances *
2050 *from the temp table,for 4650 when we are *
2051 *processing the 4650 account for sequence 20, we *
2052 *need to consider the sum for the to accounts 4650*
2053 * for sequence 10 in the temp table. */
2054
2055 IF (vl_rec_found_flag = 'Y') THEN -- vl_rec_found_flag
2056 EXIT;
2057 ELSE -- vl_rec_found_flag
2058 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2059 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' NO BALANCES FOUND IN General Ledger.'||
2060 'Looking for balances in the Temp table');
2061 END IF;
2062 vg_bal_seq_amt := 0;
2063
2064 FOR vc_getsumtemp IN get_sumtemp_cur LOOP -- getsum loop
2065 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2066 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' FOUND BALANCES '||
2067 'in Temp Table.');
2068 END IF;
2069
2070 -- put the ccid into the pl/sql table
2071 vt_ccid(i) := vc_getsumtemp.code_combination_id;
2072 i := i + 1;
2073
2074 -- Get the amt from the temp table
2075 vg_bal_seq_amt := vc_getsumtemp.amt;
2076 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2077 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' AMOUNT FROM TEMP TABLE '||
2078 'is '|| TO_CHAR(vg_bal_seq_amt));
2079 END IF;
2080
2081 -- Set the balance_read_flag
2082 vg_balance_read_flag := 'Y';
2083
2084 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2085 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CALLING DETERMINE_DRCR'||
2086 ' proc. Case 2D');
2087 END IF;
2088 -- Call Determine_DrCr procedure
2089 -- Case 2D:
2090 Determine_DrCr(vc_getsumtemp.code_combination_id);
2091
2092 IF (vp_retcode <> 0) THEN
2093 RAISE e_error;
2094 END IF;
2095 END LOOP; -- getsum loop
2096 EXIT;
2097 END IF; -- vl_rec_found_flag
2098 END IF; -- vc_getbal found
2099
2100 END LOOP; -- loop for balances
2101
2102 -- Get the count of ccids in pl/sql table
2103 vl_ccid_cnt := vt_ccid.COUNT;
2104 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2105 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' THE NUMBER OF CCIDS PROCESSED FROM '||
2106 'gl_balances for the from acct is '||TO_CHAR(vl_ccid_cnt));
2107 END IF;
2108
2109 /* The below code is needed to handle the following scenrio: *
2110 * The sequence are defined as follows: Group id 1 has seq's *
2111 * 10 and 20. Seq 10 has Exp 4610 4450, Exp 4700 4450. Seq 20*
2112 * has Exp 4450 4650. In this scenario, say acct 461002 with *
2113 * ccid 30608 has $1145 which has been moved to 4450 with *
2114 * ccid 34650 based on the first entry of the seq 10. There *
2115 * was also another ccid 28822 with amt $19M which was moved *
2116 * to 4450.Now when processing seq 20, for 4450 we find an *
2117 * amt $15M with ccid 28822.When the Ye process runs, it is *
2118 * just picking up the amount from gl,plus the amt from temp *
2119 * table only for ccid 28822. The below code is necessary to *
2120 * this scenario, where the money in the ccid 34650 for acct *
2121 * 4450 should also be moved to acct 4650. */
2122
2123 FOR vc_addccid IN get_addccid_cur LOOP -- additional ccid
2124 vl_ccid := vc_addccid.code_combination_id;
2125 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2126 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PROCESSING THE ADDITIONAL CCID '||
2127 TO_CHAR(vl_ccid));
2128 END IF;
2129
2130 FOR j IN 1..vl_ccid_cnt LOOP
2131 IF (vt_ccid(j) = vl_ccid) THEN
2132 vl_exists := 'Y';
2133 EXIT;
2134 ELSE
2135 vl_exists := 'N';
2136 END IF;
2137 END LOOP;
2138
2139 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2140 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' VL_EXISTS = '||VL_EXISTS);
2141 END IF;
2142
2143 IF (vl_exists = 'N') THEN
2144 -- Get the amt from the temp table
2145 vg_bal_seq_amt := vc_addccid.amt;
2146 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2147 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' AMOUNT FROM TEMP TABLE '||
2148 'is '|| TO_CHAR(vg_bal_seq_amt)||
2149 ' for the additional ccid '||TO_CHAR(vl_ccid));
2150 END IF;
2151
2152 -- Set the balance_read_flag
2153 vg_balance_read_flag := 'Y';
2154
2155 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2156 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CALLING DETERMINE_DRCR'||
2157 ' proc. Case 4D');
2158 END IF;
2159 -- Call Determine_DrCr procedure
2160 -- Case 4D:
2161 Determine_DrCr(vl_ccid);
2162
2163 IF (vp_retcode <> 0) THEN
2164 RAISE e_error;
2165 END IF;
2166 END IF;
2167
2168 END LOOP; -- additional ccid
2169
2170 ELSE -- vg_balance_read_flag
2171 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2172 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CASE: WHEN BALANCE_READ_FLAG = Y');
2173 END IF;
2174
2175 FOR vc_getsumseq IN get_sumtemp_seqcur LOOP -- getsumseq loop
2176 -- Get the amt from the temp table
2177 vg_bal_seq_amt := vc_getsumseq.amt;
2178
2179 -- Set the balance_read_flag
2180 vg_balance_read_flag := 'Y';
2181
2182 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2183 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CALLING DETERMINE_DRCR PROC.'||
2184 'Case 3D');
2185 END IF;
2186 -- Call Determine_DrCr procedure
2187 -- Case 3D:
2188 Determine_DrCr(vc_getsumseq.code_combination_id);
2189
2190 IF (vp_retcode <> 0) THEN
2191 RAISE e_error;
2192 END IF;
2193 END LOOP; -- getsumseq loop
2194
2195 END IF; -- vg_balance_read_flag
2196
2197 EXCEPTION
2198 WHEN e_error THEN
2199 IF vc_getbal%ISOPEN THEN
2200 CLOSE vc_getbal;
2201 END IF;
2202 RETURN;
2203
2204 WHEN OTHERS THEN
2205 vp_retcode := 2 ;
2206 vp_errbuf := SQLERRM ||' -- Error in Get_Balances procedure.' ;
2207 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2208 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
2209 'When Others Exception ' || vp_errbuf );
2210 END IF;
2211 RETURN;
2212 END Get_Balances;
2213
2214 -- ------------------------------------------------------------------
2215 -- Procedure Determine_DrCr
2216 -- ------------------------------------------------------------------
2217 -- Determine_DrCr procedure is called from Get_Balances procedure.
2218 -- This determines the debit and credit flag, and then calls the
2219 -- Get_Segment_Values procedure, and then calls Insert_Balances.
2220 -- ------------------------------------------------------------------
2221 PROCEDURE Determine_DrCr(ccid NUMBER) IS
2222 vl_dbt_flag VARCHAR2(1);
2223 vl_crt_flag VARCHAR2(1);
2224
2225 vl_acct Fv_Ye_Sequence_Accounts.from_account%TYPE;
2226 vl_remaining_bal NUMBER;
2227 vl_drcr_flag VARCHAR2(1);
2228 vl_read_flag VARCHAR2(1);
2229 vl_ccid Gl_Code_Combinations.code_combination_id%TYPE;
2230 vl_processing_type Fv_Ye_Seq_Bal_Temp.processing_type%TYPE;
2231 vl_acct_combi VARCHAR2(2000);
2232 vl_delimeter VARCHAR2(1);
2233 v_cross_val_msg VARCHAR2(2000);
2234 l_ccid_select varchar2(2048);
2235 l_cursor_id INTEGER;
2236 l_counter NUMBER;
2237 l_ignore INTEGER;
2238
2239
2240 CURSOR flex_cursor
2241 (
2242 c_coa_id NUMBER
2243 ) IS
2244 SELECT fifs.application_column_name
2245 FROM fnd_id_flex_segments fifs
2246 WHERE fifs.application_id = 101
2247 AND fifs.id_flex_code = 'GL#'
2248 AND fifs.id_flex_num = c_coa_id
2249 AND fifs.enabled_flag = 'Y'
2250 ORDER by fifs.segment_num;
2251 l_module_name VARCHAR2(200) ;
2252 BEGIN
2253
2254 l_module_name := g_module_name ||'Determine_DrCr ';
2255
2256
2257
2258
2259 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2260 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' IN DETERMINE_DRCR PROCEDURE,'||
2261
2262 ' processing ccid '||TO_CHAR(ccid));
2263 END IF;
2264 -- Determine the debit and credit flag
2265 -- debit flag is used for the from_acct
2266 -- credit flag is used for the to_acct
2267 IF (vg_bal_seq_amt > 0) THEN
2268 vl_dbt_flag := 'C';
2269 vl_crt_flag := 'D';
2270 ELSE
2271 vl_dbt_flag := 'D';
2272 vl_crt_flag := 'C';
2273 END IF;
2274
2275 -- Get the Segment Values for the ccid
2276 Get_Segment_Values(ccid);
2277
2278 IF (vp_retcode <> 0) THEN
2279 RAISE e_error;
2280 END IF;
2281
2282 FOR i IN 1..2 LOOP
2283 IF (i = 1) THEN
2284 -- When this procedure is called from Case 1D, this step is
2285 -- redundant, but when called from Case 2D , this is required.
2286 -- Overlaying the Natural Acct segment with the child Acct
2287 vt_segments(vg_acct_segnum) := vg_child_acct;
2288 vl_acct := vg_child_acct;
2289 vl_remaining_bal := 0;
2290 vl_ccid := ccid;
2291
2292 -- Determine the Processing Type(FACTSI or FACTSII or none)
2293 Determine_Processing_Type(vl_processing_type);
2294
2295 IF (vp_retcode <> 0) THEN
2296 RAISE e_error;
2297 END IF;
2298 ELSE
2299 -- This step is required for all the Cases.
2300 -- Overlaying the Natural Acct segment with the To Acct
2301 vt_segments(vg_acct_segnum) := vg_to_acct;
2302 vl_acct := vg_to_acct;
2303 vl_remaining_bal := vg_bal_seq_amt;
2304 vl_processing_type := 0;
2305
2306 IF NOT Fnd_Flex_Ext.Get_Combination_Id('SQLGL', 'GL#',
2307 vg_coa_id, SYSDATE, vg_num_segs,
2308 vt_segments, vl_ccid) THEN
2309 IF (vl_remaining_bal <> 0) THEN
2310 RAISE e_invalid;
2311 ELSE
2312 vl_ccid := NULL;
2313 l_ccid_select := 'SELECT code_combination_id
2314 FROM gl_code_combinations gcc
2315 WHERE gcc.chart_of_accounts_id = :coa_id ';
2316 FOR flex_rec IN flex_cursor (vg_coa_id) LOOP
2317 l_ccid_select := l_ccid_select ||
2318 ' and gcc.'||
2319 flex_rec.application_column_name||
2320 ' = :c_'||
2321 flex_rec.application_column_name;
2322 END LOOP;
2323
2324 l_cursor_id := dbms_sql.open_cursor;
2325 dbms_sql.parse(l_cursor_id, l_ccid_select, dbms_sql.v7);
2326 dbms_sql.define_column(l_cursor_id, 1, vl_ccid);
2327 dbms_sql.bind_variable(l_cursor_id, ':coa_id', vg_coa_id);
2328
2329 l_counter := 0;
2330 FOR flex_rec IN flex_cursor (vg_coa_id) LOOP
2331 l_counter := l_counter + 1;
2332 dbms_sql.bind_variable(l_cursor_id, 'c_'||flex_rec.application_column_name, vt_segments(l_counter));
2333 END LOOP;
2334
2335 l_ignore := dbms_sql.execute_and_fetch(l_cursor_id);
2336 dbms_sql.column_value(l_cursor_id, 1, vl_ccid);
2337 dbms_sql.close_cursor (l_cursor_id);
2338 IF (vl_ccid IS NULL) THEN
2339 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'No CCID Exists for the following Combination');
2340 l_counter := 0;
2341 FOR flex_rec IN flex_cursor (vg_coa_id) LOOP
2342 l_counter := l_counter + 1;
2343 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,flex_rec.application_column_name||'='|| vt_segments(l_counter));
2344 END LOOP;
2345 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Please Create the CCID');
2346 RAISE e_invalid;
2347 END IF;
2348 END IF;
2349 END IF;
2350
2351 END IF;
2352
2353 BEGIN
2354 SELECT DECODE(i,1,vl_dbt_flag,vl_crt_flag)
2355 INTO vl_drcr_flag
2356 FROM DUAL;
2357
2358 SELECT DECODE(i,1,vg_balance_read_flag,'N')
2359 INTO vl_read_flag
2360 FROM DUAL;
2361
2362 EXCEPTION
2363 WHEN OTHERS THEN
2364 vp_retcode := 2 ;
2365 vp_errbuf := SQLERRM ||' -- Error in Determine_DrCr procedure,'||
2366 'while deriving the vl_drcr_flag and vl_read_flag.' ;
2367 RETURN;
2368 END;
2369
2370
2371 -- Insert Null values for the Remaining Segments
2372
2373 FOR i in vt_segments.count+1..30
2374 LOOP
2375 vt_segments(i) := NULL;
2376 END LOOP;
2377
2378
2379 -- Call the Insert_Balances procedure.
2380 Insert_Balances(
2381 vl_ccid,
2382 vl_acct,
2383 vg_bal_seq_amt,
2384 vl_drcr_flag,
2385 vl_read_flag,
2386 vl_remaining_bal,
2387 vl_processing_type,
2388 vt_segments);
2389
2390 IF (vp_retcode <> 0) THEN
2391 RAISE e_error;
2392 END IF;
2393 END LOOP;
2394
2395 EXCEPTION
2396 WHEN e_error THEN
2397 RETURN;
2398
2399 WHEN e_invalid THEN
2400 vp_retcode := 2;
2401 v_cross_val_msg := fnd_flex_ext.get_message;
2402 vl_delimeter := fnd_flex_ext.get_delimiter('SQLGL', 'GL#',vg_coa_id) ;
2403 vl_acct_combi := fnd_flex_ext.concatenate_segments(vg_num_segs,
2404 vt_segments, vl_delimeter);
2405
2406 vp_errbuf := 'The '||'"'||'To Account combination :'|| vl_acct_combi||'"'
2407 ||' violates Cross-Validation/Security rule ' ||
2408 '"'|| v_cross_val_msg ||'".';
2409
2410 -- vp_errbuf := 'Unable to determine the CCID of the To Account combination,'||
2411 -- 'possibly due to a Cross-Validation/Security rule violation.';
2412 RETURN;
2413
2414 WHEN OTHERS THEN
2415 vp_retcode := 2 ;
2416 vp_errbuf := SQLERRM ||' -- Error in Determine_DrCr procedure.' ;
2417
2418 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2419 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
2420 'When Others Exception ' || vp_errbuf );
2421 END IF;
2422 RETURN;
2423
2424 END Determine_DrCr;
2425
2426 -- ------------------------------------------------------------------
2427 -- Procedure Get_Segment_Values
2428 -- ------------------------------------------------------------------
2429 -- Get_Segment_Values procedure is called from Determine_DrCr procedure.
2430 -- This proc gets all the segments for the ccid passed.
2431 -- ------------------------------------------------------------------
2432 PROCEDURE Get_Segment_Values(ccid NUMBER) IS
2433 vl_segment_value VARCHAR2(25);
2434 vl_num_segs NUMBER;
2435 l_module_name VARCHAR2(200);
2436 BEGIN
2437
2438 l_module_name := g_module_name ||
2439 'Get_Segment_Values ';
2440
2441 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2442 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' IN GET_SEGMENT_VALUES PROCEDURE,'||
2443
2444 'determing the segments for the ccid '||TO_CHAR(ccid));
2445 END IF;
2446
2447
2448 IF NOT fnd_flex_ext.get_segments('SQLGL','GL#',vg_coa_id,ccid,vl_num_segs,
2449 vt_segments) THEN
2450 raise e_invalid;
2451 END IF;
2452
2453 EXCEPTION
2454 WHEN e_invalid THEN
2455 vp_retcode := 2;
2456 vp_errbuf := 'Error in Get_Segment_Values - Unable to
2457 determine the segments for the CCID '||TO_CHAR(ccid);
2458 RETURN;
2459
2460 WHEN OTHERS THEN
2461 vp_retcode := 2 ;
2462 vp_errbuf := SQLERRM ||' -- Error in Get_Segment_Values procedure.' ;
2463 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2464 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
2465 'When Others Exception ' || vp_errbuf );
2466 END IF;
2467
2468 RETURN ;
2469
2470 END Get_Segment_Values;
2471
2472 -- ------------------------------------------------------------------
2473 -- Procedure Determine_Processing_Type
2474 -- ------------------------------------------------------------------
2475 -- Determine_Processing_Type procedure is called from Determine_DrCr procedure.
2476 -- This proc determines if the Year end process needs to close the
2477 -- balances at the FACTSI or FACTSII attribute level.
2478 -- ------------------------------------------------------------------
2479 PROCEDURE Determine_Processing_Type(p_type OUT NOCOPY NUMBER) IS
2480
2481 CURSOR get_attrcnt_csr(p_acct VARCHAR2) IS
2482 SELECT COUNT(*)
2483 FROM Fv_Facts_Attributes
2484 WHERE set_of_books_id = vg_sob_id
2485 AND facts_acct_number = p_acct;
2486
2487 CURSOR get_attributes_csr(p_acct VARCHAR2) IS
2488 SELECT public_law_code,
2489 advance_flag,
2490 transfer_flag,
2491 govt_non_govt
2492 FROM Fv_Facts_Attributes
2493 WHERE set_of_books_id = vg_sob_id
2494 AND facts_acct_number = p_acct;
2495
2496 CURSOR get_parent_csr IS
2497 SELECT parent_flex_value
2498 FROM Fnd_Flex_Value_Norm_Hierarchy
2499 WHERE flex_value_set_id = vg_acct_val_set_id
2500 AND vg_child_acct BETWEEN child_flex_value_low AND child_flex_value_high
2501 ORDER BY parent_flex_value;
2502
2503 vl_plcode Fv_Facts_Attributes.public_law_code%TYPE;
2504 vl_advflag Fv_Facts_Attributes.advance_flag%TYPE;
2505 vl_transflag Fv_Facts_Attributes.transfer_flag%TYPE;
2506 vl_govtflag Fv_Facts_Attributes.govt_non_govt%TYPE;
2507 vl_found_flag VARCHAR2(1) ;
2508 vl_cnt NUMBER;
2509 vl_process_type VARCHAR2(10);
2510 vl_parent Fnd_Flex_Value_Hierarchies.parent_flex_value%TYPE;
2511
2512 l_module_name VARCHAR2(200) ;
2513
2514 BEGIN
2515
2516 l_module_name := g_module_name ||
2517 'Determine_Processing_Type ';
2518 vl_found_flag := 'N';
2519
2520
2521
2522 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2523 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' IN DETERMINE_PROCESSING_TYPE PROCEDURE'||
2524 ' with acct '||vg_child_acct);
2525 END IF;
2526
2527 OPEN get_attrcnt_csr(vg_child_acct);
2528 FETCH get_attrcnt_csr INTO vl_cnt;
2529 CLOSE get_attrcnt_csr;
2530
2531 IF (vl_cnt = 1) THEN -- child cnt
2532 -- Case when the processing acct has attributes in Facts Attributes table
2533 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2534 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CASE: WHEN PROCESSING ACCT IS IN '||
2535 'Facts Attributes Table');
2536 END IF;
2537 OPEN get_attributes_csr(vg_child_acct);
2538 FETCH get_attributes_csr INTO
2539 vl_plcode,vl_advflag,vl_transflag,vl_govtflag;
2540 CLOSE get_attributes_csr;
2541
2542 vl_found_flag := 'Y';
2543 ELSIF (vl_cnt = 0) THEN -- child cnt
2544 -- Case when the child acct has no attributes in Facts Attributes table,
2545 -- hence looking for the parent.
2546 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2547 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CASE: WHEN PROCESSING ACCT IS NOT IN '||
2548 'Facts Attributes Table, hence looking for the parent');
2549 END IF;
2550 vl_cnt := 0;
2551 FOR vc_parent IN get_parent_csr LOOP
2552 vl_parent := vc_parent.parent_flex_value;
2553 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2554 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PROCESSING PARENT '||VL_PARENT);
2555 END IF;
2556
2557 OPEN get_attrcnt_csr(vl_parent);
2558 FETCH get_attrcnt_csr INTO vl_cnt;
2559 CLOSE get_attrcnt_csr;
2560
2561 IF (vl_cnt = 1) THEN -- parent cnt
2562 IF (vl_found_flag = 'N') THEN -- found flag
2563 -- Case when the parent has attributes, the first parent.
2564 OPEN get_attributes_csr(vl_parent);
2565 FETCH get_attributes_csr INTO
2566 vl_plcode,vl_advflag,vl_transflag,vl_govtflag;
2567 CLOSE get_attributes_csr;
2568
2569 vl_found_flag := 'Y';
2570 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2571 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CASE: WHEN PARENT '||
2572 'has attributes in FACTS Table');
2573 END IF;
2574 ELSE -- found flag
2575 -- Case when more than one parent is in the Facts Attributes table.
2576 -- In this case, the processing type should be 0,since we do not
2577 -- know which acct to consider for getting the attributes.
2578 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2579 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CASE: WHEN MORE THAN ONE '||
2580 'parent has attributes in FACTS table');
2581 END IF;
2582 vl_found_flag := 'N';
2583 EXIT;
2584 END IF; -- found flag
2585 END IF; -- parent cnt
2586 END LOOP;
2587
2588 END IF; -- child cnt
2589
2590 IF (vl_found_flag = 'Y') THEN
2591 -- Found attributes either for the processing acct or it's parent.
2592 IF ((vl_plcode = 'Y') OR (vl_advflag = 'Y') OR
2593 (vl_transflag = 'Y') ) THEN
2594 p_type := 2;
2595 ELSIF( (vl_govtflag IN ('F','Y')) AND (vg_factsi_attr_exists = 'Y')) THEN
2596 p_type := 1;
2597 ELSE
2598 p_type := 0;
2599 END IF;
2600 ELSE
2601 -- No attributes found for the processing acct or it's parent.
2602 p_type := 0;
2603 END IF;
2604
2605 IF (p_type = 2) THEN
2606 vl_process_type := 'FACTS II';
2607 ELSIF (p_type = 1) THEN
2608 vl_process_type := 'FACTS I';
2609 ELSE
2610 vl_process_type := 'Regular';
2611 END IF;
2612 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2613 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PROCESSING BY '||VL_PROCESS_TYPE);
2614 END IF;
2615
2616 EXCEPTION
2617 WHEN OTHERS THEN
2618 vp_retcode := 2 ;
2619 vp_errbuf := SQLERRM ||' -- Error in Determine_Processing_Type procedure.' ;
2620 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2621 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
2622 'When Others Exception ' || vp_errbuf );
2623 END IF;
2624
2625 RETURN ;
2626 END Determine_Processing_Type;
2627
2628 -- ------------------------------------------------------------------
2629 -- Procedure Insert_Balances
2630 -- ------------------------------------------------------------------
2631 -- Insert_Balances procedure is called from Determine_DrCr procedure.
2632 -- This proc inserts into the Fv_Ye_Seq_Bal_Temp table.
2633 -- ------------------------------------------------------------------
2634 PROCEDURE Insert_Balances(ccid NUMBER,
2635 acct VARCHAR2,
2636 bal_amt NUMBER,
2637 dr_cr VARCHAR2,
2638 read_flag VARCHAR2,
2639 remaining_bal NUMBER,
2640 processing_type NUMBER,
2641 segs Fnd_Flex_Ext.SegmentArray) IS
2642
2643 CURSOR flex_fields IS
2644 SELECT application_column_name
2645 FROM fnd_id_flex_segments
2646 WHERE id_flex_code = 'GL#'
2647 AND id_flex_num = vg_coa_id
2648 AND enabled_flag = 'Y'
2649 ORDER BY segment_num;
2650
2651 vl_report_seq NUMBER;
2652 vl_period_dr NUMBER;
2653 vl_period_cr NUMBER;
2654 l_module_name VARCHAR2(200) ;
2655 vl_segments Fnd_Flex_Ext.SegmentArray;
2656 vl_segnum NUMBER;
2657 l_column_name fnd_id_flex_segments.application_column_name%TYPE;
2658 l_n_segments NUMBER ;
2659
2660 BEGIN
2661 l_module_name := g_module_name || 'Insert_Balances ';
2662 -- Get the period net dr, period net cr
2663 BEGIN
2664 SELECT DECODE(dr_cr,'D',ABS(bal_amt),0)
2665 INTO vl_period_dr
2666 FROM DUAL;
2667
2668 SELECT DECODE(dr_cr,'D',0,ABS(bal_amt))
2669 INTO vl_period_cr
2670 FROM DUAL;
2671 EXCEPTION
2672 WHEN OTHERS THEN
2673 vp_retcode := 2 ;
2674 vp_errbuf := SQLERRM ||' -- Error in Insert_Balances procedure,'||
2675 'while deriving the period_net_dr and period_net_cr.' ;
2676 RETURN;
2677
2678 END;
2679 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2680 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' IN INSERT_BALANCES PROCEDURE,'||
2681 'inserting the following:');
2682 END IF;
2683 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2684 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CCID:'||TO_CHAR(CCID)||' ACCT:'||ACCT||
2685 ' balance read flag: '||read_flag);
2686 END IF;
2687 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2688 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PERIOD NET DR:'||TO_CHAR(VL_PERIOD_DR)||
2689 ' period net cr:'|| TO_CHAR(vl_period_cr)||
2690 ' remaining balance:'||TO_CHAR(remaining_bal) );
2691 END IF;
2692
2693 -- Get the report sequence
2694 BEGIN
2695 SELECT Fv_Ye_Seq_Bal_Temp_S.NEXTVAL
2696 INTO vl_report_seq
2697 FROM DUAL;
2698 EXCEPTION
2699 WHEN OTHERS THEN
2700 vp_retcode := 2 ;
2701 vp_errbuf := SQLERRM ||' -- Error in Determine_DrCr procedure,'||
2702 'while deriving the vl_report_seq.' ;
2703 RETURN;
2704 END;
2705
2706
2707 -- map the segment values to the right segments
2708
2709 -- Insert Null values for the Segments initially
2710
2711 FOR i in 1..30
2712 LOOP
2713 vl_segments(i) := NULL;
2714 END LOOP;
2715
2716 l_n_segments := 0;
2717
2718 FOR flex_fields_rec IN flex_fields
2719 LOOP
2720
2721 l_n_segments := l_n_segments + 1;
2722 l_column_name := flex_fields_rec.application_column_name;
2723 vl_segnum := SUBSTR(l_column_name,8) ;
2724 vl_segments(vl_segnum) := segs(l_n_segments);
2725
2726 END LOOP;
2727
2728
2729
2730 -- Insert into the Temp table.
2731 INSERT INTO Fv_Ye_Seq_Bal_Temp(
2732 code_combination_id,
2733 group_id,
2734 SEQUENCE,
2735 account_seg,
2736 balance_seg,
2737 period_net_dr,
2738 period_net_cr,
2739 bal_seq_amt,
2740 period_name,
2741 currency_code,
2742 fiscal_year,
2743 balance_read_flag,
2744 set_of_books_id,
2745 treasury_symbol_id,
2746 account_flag,
2747 report_sequence,
2748 processing_type,
2749 segment1,segment2,segment3,segment4,segment5,
2750 segment6,segment7,segment8,segment9,segment10,
2751 segment11,segment12,segment13,segment14,
2752 segment15,segment16,segment17,segment18,
2753 segment19,segment20,segment21,segment22,
2754 segment23,segment24,segment25,segment26,
2755 segment27,segment28,segment29,segment30)
2756 VALUES
2757 (ccid,
2758 vg_group_id,
2759 vg_seq,
2760 acct,
2761 vg_fund_value,
2762 vl_period_dr,
2763 vl_period_cr,
2764 remaining_bal,
2765 vg_closing_period,
2766 vg_currency,
2767 vp_closing_fyr,
2768 read_flag,
2769 vg_sob_id,
2770 vg_trsymbol_id,
2771 vg_acct_flag,
2772 vl_report_seq,
2773 processing_type,
2774 vl_segments(1),vl_segments(2),vl_segments(3),vl_segments(4),vl_segments(5),
2775 vl_segments(6),vl_segments(7),vl_segments(8),vl_segments(9),vl_segments(10),
2776 vl_segments(11),vl_segments(12),vl_segments(13),vl_segments(14),vl_segments(15),
2777 vl_segments(16),vl_segments(17),vl_segments(18),vl_segments(19),vl_segments(20),
2778 vl_segments(21),vl_segments(22),vl_segments(23),vl_segments(24),vl_segments(25),
2779 vl_segments(26),vl_segments(27),vl_segments(28),vl_segments(29),vl_segments(30));
2780 EXCEPTION
2781 WHEN OTHERS THEN
2782 vp_retcode := 2 ;
2783 vp_errbuf := SQLERRM ||' -- Error in Insert_Balances procedure.' ;
2784 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2785 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
2786 'When Others Exception ' || vp_errbuf );
2787 END IF;
2788
2789 RETURN ;
2790
2791 END Insert_Balances;
2792
2793 -- ------------------------------------------------------------------
2794 -- Procedure Update_Closing_Status
2795 -- ------------------------------------------------------------------
2796 -- Update_Closing_Status procedure is called from Get_Fund_Value procedure.
2797 -- When submit journal import is Y, this proc closes all the pending
2798 -- requisitions, if any exists,for the fund that is being processed.
2799 -- ------------------------------------------------------------------
2800 PROCEDURE Update_Closing_Status IS
2801
2802 CURSOR get_closereq_cur IS
2803 SELECT ffp.close_requisitions
2804 FROM fv_fund_parameters ffp, fv_treasury_symbols fts
2805 WHERE fts.treasury_symbol = vg_trsymbol
2806 AND ffp.fund_value = vg_fund_value
2807 AND ffp.treasury_symbol_id = fts.treasury_symbol_id
2808 AND ffp.set_of_books_id = vg_sob_id;
2809
2810 -- Variable declarations for Native Dynamic SQL
2811 TYPE t_refcur IS REF CURSOR;
2812 vl_dist_retcur t_refcur;
2813 vl_dist_select VARCHAR2(2000);
2814
2815 vl_rec_found_flag VARCHAR2(1) ;
2816 vl_close_req Fv_Fund_Parameters.close_requisitions%TYPE;
2817 vl_distr_id Po_Req_Distributions_All.distribution_id%TYPE;
2818 vl_line_id Po_Req_Distributions_All.requisition_line_id%TYPE;
2819 vl_req_num Po_Requisition_Headers_All.segment1%TYPE;
2820 vl_prev_line_id Po_Req_Distributions_All.requisition_line_id%TYPE ;
2821 vl_prt_line_id Po_Req_Distributions_All.requisition_line_id%TYPE ;
2822 vl_ctr NUMBER ;
2823 vl_index BINARY_INTEGER;
2824 vl_no_of_rows NUMBER;
2825 vl_last_row NUMBER;
2826 vl_lines_count NUMBER;
2827 vl_header_id Po_Requisition_Headers_All.requisition_header_id%TYPE;
2828 vl_prev_header_id Po_Requisition_Headers_All.requisition_header_id%TYPE ;
2829 vl_prt_hdr_id Po_Requisition_Headers_All.requisition_header_id%TYPE;
2830 vl_head_ctr NUMBER ;
2831
2832 TYPE t_lines_table IS TABLE OF NUMBER
2833 INDEX BY BINARY_INTEGER;
2834 vt_lines t_lines_table;
2835
2836 TYPE t_headers_table IS TABLE OF NUMBER
2837 INDEX BY BINARY_INTEGER;
2838 vt_headers t_headers_table;
2839
2840 CURSOR get_lcount_cur IS
2841 SELECT COUNT(*)
2842 FROM Po_Req_Distributions_All
2843 WHERE requisition_line_id = vt_lines(vl_index)
2844 AND gl_closed_date IS NULL;
2845
2846 CURSOR get_header_cur IS
2847 SELECT DISTINCT requisition_header_id
2848 FROM Po_Requisition_Lines_All
2849 WHERE requisition_line_id = vt_lines(vl_index);
2850
2851 CURSOR get_hcount_cur IS
2852 SELECT COUNT(*)
2853 FROM Po_Requisition_Lines_All
2854 WHERE requisition_header_id = vt_headers(vl_index)
2855 AND closed_code <> 'FINALLY CLOSED';
2856
2857 l_module_name VARCHAR2(200) ;
2858
2859 BEGIN
2860 l_module_name := g_module_name || 'Update_Closing_Status ';
2861 vl_rec_found_flag := 'N';
2862 vl_prev_line_id := 0;
2863 vl_ctr := 0;
2864 vl_prev_header_id := 0;
2865 vl_head_ctr := 0;
2866
2867 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2868 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' IN THE UPDATE_CLOSING_STATUS PROCEDURE.....');
2869 END IF;
2870
2871 -- Get the close requisition
2872 OPEN get_closereq_cur;
2873 FETCH get_closereq_cur INTO vl_close_req;
2874 CLOSE get_closereq_cur;
2875
2876 IF (vl_close_req = 'N') THEN -- vl_close_req
2877 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2878 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CLOSE REQUISITIONS = N');
2879 END IF;
2880 RETURN;
2881 ELSE -- vl_close_req
2882 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2883 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CLOSE REQUISITIONS = Y');
2884 END IF;
2885
2886 -- Build the select statement to get the distribution details
2887 vl_dist_select := 'SELECT po.distribution_id,
2888 po.requisition_line_id, ph.segment1
2889 FROM Po_Req_Distributions_All po, gl_code_combinations gcc,
2890 Po_Requisition_Lines_All pl, Po_Requisition_Headers_All ph
2891 WHERE po.gl_closed_date IS NULL
2892 AND gcc.code_combination_id = po.code_combination_id
2893 AND gcc.chart_of_accounts_id = :coa
2894 AND po.set_of_books_id = :sob
2895 AND gcc.'||vg_bal_segment|| ' = :fund_value'||
2896 ' AND ph.authorization_status = '||''''||'APPROVED'||''''||
2897 ' AND po.requisition_line_id = pl.requisition_line_id
2898 AND pl.requisition_header_id = ph.requisition_header_id
2899 AND pl.line_location_id IS NULL
2900 AND po.gl_encumbered_date <= :end_date' ;
2901 --Fv_Utility.Debug_Mesg(vl_dist_select);
2902
2903 -- Open the ref cursor
2904 OPEN vl_dist_retcur FOR vl_dist_select USING vg_coa_id,vg_sob_id,
2905 vg_fund_value,vg_end_date;
2906 LOOP -- distrdetails loop
2907 FETCH vl_dist_retcur INTO vl_distr_id, vl_line_id,vl_req_num;
2908
2909 IF vl_dist_retcur%FOUND THEN -- details found
2910 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2911 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PROCESSING THE REQUISITION NUMBER '||
2912 vl_req_num||' with requisition line id '
2913 ||TO_CHAR(vl_line_id)||' and the distribution id '
2914 ||TO_CHAR(vl_distr_id) );
2915 END IF;
2916 vl_rec_found_flag := 'Y';
2917
2918 -- If vl_prev_line_id = vl_line_id, then this is the scenario
2919 -- when for the same requisition line we have multiple distributions.
2920 -- In this case,just go ahead and update the Po_Req_Distributions_All table
2921 -- for the new distribution_id,without inseritng into the table.
2922 IF (vl_prev_line_id <> vl_line_id) THEN -- vl_prev_line_id
2923 -- insert into vt_lines table
2924 vt_lines(vl_ctr) := vl_line_id;
2925 vl_ctr := vl_ctr + 1;
2926
2927 -- Copy the vl_line_id to vl_prev_line_id
2928 vl_prev_line_id := vl_line_id;
2929
2930 END IF; -- vl_prev_line_id
2931
2932 -- Update the Po_Req_Distributions_All table
2933 UPDATE Po_Req_Distributions_All
2934 SET gl_closed_date = vg_end_date
2935 WHERE distribution_id = vl_distr_id;
2936 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2937 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CLOSED THE DISTRIBUTION WITH '||
2938 'distribution_id '||TO_CHAR(vl_distr_id));
2939 END IF;
2940
2941 ELSE -- details found
2942 IF (vl_rec_found_flag = 'Y') THEN
2943 EXIT;
2944 ELSE
2945 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2946 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' NO REQUISITIONS FOUND TO '||
2947 'be processed, where the balancing segment matches '||
2948 'the fund value '|| vg_fund_value);
2949 END IF;
2950 RETURN;
2951 END IF;
2952 END IF; -- details found
2953 END LOOP; -- distrdetails loop
2954 CLOSE vl_dist_retcur;
2955 END IF; -- vl_close_req
2956
2957 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2958 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' BEGININNING PROCESSING FOR THE LINES....');
2959 END IF;
2960 vl_no_of_rows := vt_lines.COUNT;
2961 vl_last_row := vt_lines.LAST;
2962 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2963 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' NUMBER OF ROWS:'|| TO_CHAR(VL_NO_OF_ROWS)||
2964 ' Last Row: '||TO_CHAR(vl_Last_row));
2965 END IF;
2966
2967 IF (vl_no_of_rows <> 0) THEN -- vl_no_of_rows lines
2968 vl_index := vt_lines.FIRST;
2969
2970 LOOP -- vl_index
2971 -- Get the Line count
2972 OPEN get_lcount_cur;
2973 FETCH get_lcount_cur INTO vl_lines_count;
2974 CLOSE get_lcount_cur;
2975 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2976 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' VL_LINES_COUNT IS '||TO_CHAR(VL_LINES_COUNT));
2977 END IF;
2978
2979 IF (vl_lines_count = 0) THEN -- vl_lines_count
2980 -- Update the Po_Requisition_Lines_All table
2981 UPDATE Po_Requisition_Lines_All
2982 SET closed_code = 'FINALLY CLOSED'
2983 WHERE requisition_line_id = vt_lines(vl_index);
2984
2985 vl_prt_line_id := vt_lines(vl_index);
2986 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2987 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CLOSED THE LINE WITH REQUISITION_LINE_ID '||
2988 TO_CHAR(vl_prt_line_id));
2989 END IF;
2990
2991 OPEN get_header_cur;
2992 FETCH get_header_cur INTO vl_header_id;
2993 CLOSE get_header_cur;
2994
2995 IF (vl_prev_header_id <> vl_header_id) THEN
2996 -- insert into the headers table
2997 vt_headers(vl_head_ctr) := vl_header_id;
2998 vl_head_ctr := vl_head_ctr + 1;
2999
3000 -- assign the vl_header_id to vl_prev_header_id
3001 vl_prev_header_id := vl_header_id;
3002 END IF;
3003 END IF; -- vl_lines_count
3004
3005 IF (vl_index = vl_last_row) THEN
3006 EXIT;
3007 END IF;
3008 vl_index := vl_index + 1;
3009 END LOOP; -- vl_index
3010
3011 ELSE -- vl_no_of_rows lines
3012 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3013 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' NO REQUISITION LINES FOUND TO BE PROCESSED....');
3014 END IF;
3015 RETURN;
3016 END IF; -- vl_no_of_rows lines
3017 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3018 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' ENDING PROCESSING FOR THE LINES....');
3019 END IF;
3020
3021 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3022 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' BEGINNING PROCESSING FOR THE HEADERS....');
3023 END IF;
3024 vl_no_of_rows := vt_headers.COUNT;
3025 vl_last_row := vt_headers.LAST;
3026 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3027 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' NUMBER OF ROWS:'|| TO_CHAR(VL_NO_OF_ROWS)||
3028 ' Last Row: '||TO_CHAR(vl_Last_row));
3029 END IF;
3030
3031 IF (vl_no_of_rows <> 0) THEN -- vl_no_of_rows headers
3032 vl_index := vt_headers.FIRST;
3033
3034 LOOP -- headers loop
3035 -- Get the Header count
3036 OPEN get_hcount_cur;
3037 FETCH get_hcount_cur INTO vl_lines_count;
3038 CLOSE get_hcount_cur;
3039 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3040 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' VL_LINES_COUNT(HDR) IS '||
3041 TO_CHAR(vl_lines_count));
3042 END IF;
3043
3044 IF (vl_lines_count = 0) THEN -- vl_lines_count header
3045 -- Update the Po_Requisition_Headers_All table
3046 UPDATE Po_Requisition_Headers_All
3047 SET closed_code = 'FINALLY CLOSED'
3048 WHERE requisition_header_id = vt_headers(vl_index);
3049
3050 vl_prt_hdr_id := vt_headers(vl_index);
3051 -- IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3052 --FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3053 --' CLOSED THE HEADER WITH '|| 'requisition_header_id '|| TO_CHAR(vl_prt_hdr_id));
3054 -- END IF;
3055 END IF; -- vl_lines_count header
3056
3057 IF (vl_index = vl_last_row) THEN
3058 EXIT;
3059 END IF;
3060 vl_index := vl_index + 1;
3061 END LOOP; -- headers loop
3062 ELSE -- vl_no_of_rows headers
3063 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3064 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' NO REQUISITION HEADERS FOUND TO BE PROCESSED....');
3065 END IF;
3066 END IF; -- vl_no_of_rows headers
3067 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3068 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' ENDING PROCESSING FOR THE HEADERS....');
3069 END IF;
3070
3071 vt_lines.DELETE;
3072 vt_headers.DELETE;
3073 EXCEPTION
3074 WHEN OTHERS THEN
3075 vp_retcode := 2 ;
3076 vp_errbuf := SQLERRM ||' -- Error in Update_Closing_Status procedure.' ;
3077 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3078 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
3079 'When Others Exception ' || vp_errbuf );
3080 END IF;
3081 RETURN ;
3082 END Update_Closing_Status;
3083
3084 -- ------------------------------------------------------------------
3085 -- Procedure Populate_Gl_Interface
3086 -- ------------------------------------------------------------------
3087 -- Populate_Gl_Interface procedure is called from Main procedure.
3088 -- When submit_journal_import =Y, this proc inserts records in gl_interface
3089 -- table, from the temp table and runs the journal import program.
3090 -- ------------------------------------------------------------------
3091 PROCEDURE Populate_Gl_Interface IS
3092 vl_req_id NUMBER;
3093 vl_call_status BOOLEAN;
3094 vl_rphase VARCHAR2(30);
3095 vl_rstatus VARCHAR2(30);
3096 vl_dphase VARCHAR2(30);
3097 vl_dstatus VARCHAR2(30);
3098 vl_message VARCHAR2(240);
3099 vl_period_name VARCHAR2(50);
3100 vl_reference_1 VARCHAR2(280);
3101 vl_running_amount NUMBER;
3102 vl_amount NUMBER;
3103 vl_amount_dr NUMBER;
3104 vl_amount_cr NUMBER;
3105 vl_ccid NUMBER(15);
3106 vl_ccid_cnt NUMBER;
3107 vl_ptype Fv_Ye_Seq_Bal_Temp.processing_type%TYPE;
3108 vl_factsi_amount NUMBER;
3109 vl_factsi_tempamt NUMBER;
3110 vl_trading_partner Fv_Facts1_Period_Balances_v.eliminations_dept%TYPE;
3111
3112 vl_stmt VARCHAR2(5000);
3113 vl_line_amount NUMBER;
3114 vl_attribute_cols VARCHAR2(2500);
3115 vl_dummy_cols VARCHAR2(100);
3116 vl_group_by_clause VARCHAR2(1024);
3117
3118 vl_public_law_code VARCHAR2(150);
3119 vl_advance_type VARCHAR2(150);
3120 vl_trf_dept_id VARCHAR2(150);
3121 vl_trf_main_acct VARCHAR2(150);
3122
3123 c_gl_line_cur INTEGER;
3124 vl_fetch_lines INTEGER;
3125 vl_exec_cur INTEGER;
3126 vl_column_num NUMBER;
3127 vl_gl_source VARCHAR2(1024) ;
3128
3129
3130 CURSOR journal_entries_cur IS
3131 SELECT
3132 code_combination_id,
3133 account_seg,
3134 balance_seg,
3135 segment1,segment2,segment3,segment4,segment5,
3136 segment6,segment7,segment8,segment9,segment10,
3137 segment11,segment12,segment13,segment14,
3138 segment15,segment16,segment17,segment18,
3139 segment19,segment20,segment21,segment22,
3140 segment23,segment24,segment25,segment26,
3141 segment27,segment28,segment29,segment30,
3142 period_net_dr,
3143 period_net_cr,
3144 period_name ,
3145 balance_read_flag,
3146 processing_type
3147 FROM Fv_Ye_Seq_Bal_Temp
3148 WHERE period_net_dr + period_net_cr > 0
3149 AND set_of_books_id = vg_sob_id
3150 ORDER BY report_sequence;
3151
3152 CURSOR ccid_cnt_csr IS
3153 SELECT COUNT(*),SUM(NVL(amount,0))
3154 FROM Fv_Facts1_Period_Balances_v
3155 WHERE set_of_books_id = vg_sob_id
3156 AND period_num <= vg_closing_period_num
3157 AND period_year = vp_closing_fyr
3158 AND ccid = vl_ccid;
3159
3160
3161 CURSOR factsi_bal_csr IS
3162 SELECT SUM(NVL(amount,0)) amount,eliminations_dept,g_ng_indicator
3163 FROM Fv_Facts1_Period_Balances_v
3164 WHERE set_of_books_id = vg_sob_id
3165 AND period_num <= vg_closing_period_num
3166 AND period_year = vp_closing_fyr
3167 AND ccid = vl_ccid
3168 GROUP BY eliminations_dept,g_ng_indicator;
3169
3170 l_module_name VARCHAR2(200) ;
3171 BEGIN
3172 l_module_name := g_module_name ||
3173 'Populate_Gl_Interface ';
3174
3175 vl_gl_source := '(''Budgetary Transaction'',''Year End Close'', ''Manual'')';
3176
3177 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3178 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' IN POPULATE_GL_INTERFACE PROCEDURE....');
3179 END IF;
3180
3181 -- Get the interface_run_id
3182 vg_interface_run_id := Gl_Interface_Control_Pkg.Get_Unique_Run_Id;
3183
3184 -- Get the journal group_id
3185 SELECT Gl_Interface_Control_S.NEXTVAL
3186 INTO vg_jrnl_group_id
3187 FROM DUAL;
3188
3189 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3190 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' INTERFACE RUN ID: '||TO_CHAR(VG_INTERFACE_RUN_ID));
3191 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' JOURNAL GROUP ID: '||TO_CHAR(VG_JRNL_GROUP_ID));
3192 END IF;
3193
3194 --Insert a control record in gl_interface_control for gl_import to work
3195 INSERT INTO Gl_Interface_Control
3196 (je_source_name,
3197 status,
3198 interface_run_id,
3199 group_id,
3200 set_of_books_id)
3201 VALUES ('Year End Close',
3202 'S',
3203 vg_interface_run_id,
3204 vg_jrnl_group_id,
3205 vg_sob_id);
3206
3207 FOR vc_journals IN journal_entries_cur
3208 LOOP -- journal_entries loop
3209 vl_ptype := vc_journals.processing_type;
3210 vl_trading_partner := NULL;
3211 vl_ccid := vc_journals.code_combination_id;
3212 vl_period_name := vc_journals.period_name;
3213 vl_reference_1 := NULL;
3214 vt_segments(1) := vc_journals.segment1;
3215 vt_segments(2) := vc_journals.segment2;
3216 vt_segments(3) := vc_journals.segment3;
3217 vt_segments(4) := vc_journals.segment4;
3218 vt_segments(5) := vc_journals.segment5;
3219 vt_segments(6) := vc_journals.segment6;
3220 vt_segments(7) := vc_journals.segment7;
3221 vt_segments(8) := vc_journals.segment8;
3222 vt_segments(9) := vc_journals.segment9;
3223 vt_segments(10) := vc_journals.segment10;
3224 vt_segments(11) := vc_journals.segment11;
3225 vt_segments(12) := vc_journals.segment12;
3226 vt_segments(13) := vc_journals.segment13;
3227 vt_segments(14) := vc_journals.segment14;
3228 vt_segments(15) := vc_journals.segment15;
3229 vt_segments(16) := vc_journals.segment16;
3230 vt_segments(17) := vc_journals.segment17;
3231 vt_segments(18) := vc_journals.segment18;
3232 vt_segments(19) := vc_journals.segment19;
3233 vt_segments(20) := vc_journals.segment20;
3234 vt_segments(21) := vc_journals.segment21;
3235 vt_segments(22) := vc_journals.segment22;
3236 vt_segments(23) := vc_journals.segment23;
3237 vt_segments(24) := vc_journals.segment24;
3238 vt_segments(25) := vc_journals.segment25;
3239 vt_segments(26) := vc_journals.segment26;
3240 vt_segments(27) := vc_journals.segment27;
3241 vt_segments(28) := vc_journals.segment28;
3242 vt_segments(29) := vc_journals.segment29;
3243 vt_segments(30) := vc_journals.segment30;
3244
3245 vl_line_amount := 0;
3246
3247
3248 IF (vc_journals.balance_read_flag = 'N') THEN -- balance_read_flag
3249 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3250 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'PROCESSING TO ACCOUNT ' || VC_JOURNALS.ACCOUNT_SEG);
3251 END IF;
3252 Insert_Gl_Interface_Record(vc_journals.period_net_dr,
3253 vc_journals.period_net_cr, vl_reference_1,
3254 vl_period_name,vl_trading_partner);
3255
3256 ELSE -- balance_read_flag
3257 -- If it is a from account, then check which processing to be done.
3258 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3259 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'PROCESSING FROM ACCOUNT: ' || VC_JOURNALS.ACCOUNT_SEG||
3260 ' CCID: '||vl_ccid);
3261 END IF;
3262
3263 IF (vl_ptype = 1) THEN -- vl_ptype
3264 -- FACTS I processing
3265 -- Check if the attribute exits and there are balances in the Facts table
3266 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3267 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'FACTS I PROCESSING');
3268 END IF;
3269
3270 vl_amount := (NVL(vc_journals.period_net_cr,0)
3271 - NVL(vc_journals.period_net_dr,0) );
3272 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3273 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'AMOUNT TO BE MATCHED ' || VL_AMOUNT);
3274 END IF;
3275
3276 IF ((vg_factsi_attr_exists = 'Y')
3277 AND (vg_factsi_bal_cnt > 0)) THEN -- facts attr
3278 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3279 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'FACTS I ATTRIBUTE EXISTS AND THERE ARE '||
3280 'balances in FACTS table.');
3281 END IF;
3282
3283 OPEN ccid_cnt_csr;
3284 FETCH ccid_cnt_csr INTO vl_ccid_cnt,vl_factsi_amount;
3285 CLOSE ccid_cnt_csr;
3286
3287 IF (vl_ccid_cnt = 0) THEN -- ccid cnt
3288 -- ccid does not exist in facts table
3289 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3290 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'BALANCES WILL NOT BE CLOSED BY '||
3291
3292 'the FACTS I F/N and trading partner attributes for the '||
3293 'code combination id '||vl_ccid||', since the code '||
3294 'combination id does not exist in Fv_Facts1_Period_Balances_v.');
3295 END IF;
3296 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3297 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'TO CLOSE BY FACTS I F/N AND '||
3298
3299 'trading partner attributes for this code combination id, '||
3300 'delete the journal entries created by this process, '||
3301 'if any, run the FACTS I Interface Program with all edit '||
3302 'checks passed by period '||vg_closing_period||
3303 'and rerun the Year End Close Program.');
3304 END IF;
3305 Insert_Gl_Interface_Record(vc_journals.period_net_dr,
3306 vc_journals.period_net_cr, vl_reference_1,
3307 vl_period_name,vl_trading_partner);
3308 ELSE -- ccid cnt
3309 -- ccid exists in facts table
3310 IF (vl_amount <> vl_factsi_amount) THEN -- amt matching
3311 -- amount from year end table does not match with amt from
3312 -- facts table.
3313 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3314 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'BALANCES WILL NOT BE CLOSED BY '||
3315 'the FACTS I F/N and trading partner attributes for the '||
3316 'code combination id '||vl_ccid||', since the balances '||
3317 'in GL does not equal to the balances in '||
3318 'Fv_Facts1_Period_Balances_v.');
3319 END IF;
3320 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3321 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'TO CLOSE BY FACTS I F/N AND '|| 'trading partner attributes for this code combination id, '||
3322 'delete the journal entries created by this process, '||
3323 'if any, run the FACTS I Interface Program with all edit '||
3324 'checks passed by period '||vg_closing_period||
3325 'and rerun the Year End Close Program.');
3326 END IF;
3327
3328 Insert_Gl_Interface_Record(vc_journals.period_net_dr,
3329 vc_journals.period_net_cr, vl_reference_1,
3330 vl_period_name,vl_trading_partner);
3331 ELSE -- amt matching
3332 -- amount from year end table matches with amt from
3333 -- facts table.
3334 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3335 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'AMOUNT MATCHED');
3336 END IF;
3337 FOR vc_factsi IN factsi_bal_csr LOOP -- bal loop
3338 vl_factsi_tempamt := vc_factsi.amount;
3339 IF (vl_factsi_tempamt > 0) THEN
3340 vl_amount_dr := 0;
3341 vl_amount_cr := vl_factsi_tempamt;
3342 ELSE
3343 vl_amount_dr := (-1) * vl_factsi_tempamt;
3344 vl_amount_cr := 0;
3345 END IF;
3346
3347 vl_trading_partner := vc_factsi.eliminations_dept;
3348
3349 Insert_Gl_Interface_Record(vl_amount_dr,vl_amount_cr,
3350 vl_reference_1,vl_period_name,vl_trading_partner);
3351 END LOOP; -- bal loop
3352
3353 END IF; -- amt matching
3354
3355 END IF; -- ccid cnt
3356
3357 ELSE -- facts attr
3358 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3359 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' EITHER FACTS I ATTRIBUTE DOES NOT EXISTS '||
3360 'or there are no balances in FACTS table.');
3361 END IF;
3362 Insert_Gl_Interface_Record(vc_journals.period_net_dr,
3363 vc_journals.period_net_cr, vl_reference_1,
3364 vl_period_name,vl_trading_partner);
3365 END IF; -- facts attr
3366
3367 ELSIF (vl_ptype = 2) THEN -- vl_ptype
3368 -- FACTS II processing
3369 fv_utility.log_mesg('FACTS II processing');
3370
3371 vl_amount := (NVL(vc_journals.period_net_dr,0)
3372 - NVL(vc_journals.period_net_cr,0) );
3373
3374 -- POPULATE REFERENCE_1 column for all budgetary_transaction ccid
3375 vl_running_amount := 0;
3376
3377 ---------------------------------------------------------------------------------------
3378 vl_attribute_cols := NULL;
3379 vl_dummy_cols := NULL;
3380
3381 -- Populate attribute columns for FACTS II records
3382 -- Build the attribute columns clause
3383
3384 IF vg_public_law_attribute IS NOT NULL
3385 THEN
3386 vl_attribute_cols := vl_attribute_cols||', NVL(b.public_law_code, l.'||vg_public_law_attribute||') ';
3387 END IF;
3388
3389 IF vg_advance_type_attribute IS NOT NULL
3390 THEN
3391 vl_attribute_cols := vl_attribute_cols||', NVL(b.advance_type, l.'||vg_advance_type_attribute||') ';
3392 END IF;
3393
3394 IF vg_trf_dept_id_attribute IS NOT NULL
3395 THEN
3396 vl_attribute_cols := vl_attribute_cols||', NVL(b.dept_id, l.'||vg_trf_dept_id_attribute||') ';
3397 END IF;
3398
3399 IF vg_trf_main_acct_attribute IS NOT NULL
3400 THEN
3401 vl_attribute_cols := vl_attribute_cols||', NVL(b.main_account, l.'|| vg_trf_main_acct_attribute ||') ';
3402 END IF;
3403
3404 IF vl_attribute_cols IS NOT NULL THEN
3405 vl_group_by_clause := ' GROUP BY ' || SUBSTR(vl_attribute_cols, 2);
3406 END IF;
3407 ---------------------------------------------------------------------------------------
3408 BEGIN
3409 c_gl_line_cur := DBMS_SQL.OPEN_CURSOR ;
3410 EXCEPTION
3411 WHEN OTHERS THEN
3412 vp_retcode := 2;
3413 vp_errbuf := SQLERRM||
3414 ' Open cursor error in Populate_Gl_Interface procedure.';
3415 RETURN;
3416 END ;
3417
3418 vl_stmt :=
3419 ' SELECT MAX(Fv_Ye_Carryforward.Convert_To_Num (l.reference_1)) reference_1 ,
3420 SUM( NVL(entered_dr, 0) - NVL(entered_cr,0) ) line_amount ' ||
3421 vl_attribute_cols ||
3422 ' FROM gl_je_lines l , fv_be_trx_dtls B, gl_je_headers h
3423 WHERE l.code_combination_id = :ccid
3424 AND l.je_header_id = h.je_header_id
3425 AND NVL(h.je_from_sla_flag, ''N'') IN (''N'',''U'')
3426 -- AND l.gl_sl_link_id is null
3427 AND EXISTS (SELECT 1
3428 FROM gl_period_statuses glp
3429 WHERE glp.application_id = 101
3430 AND glp.set_of_books_id = :sob_id
3431 AND glp.ledger_id = :sob_id
3432 AND glp.period_year = :closing_fyr
3433 AND glp.period_name = l.period_name)
3434 AND NVL(l.reference_1, ''-99'') = TO_CHAR(b.transaction_id (+))
3435 AND l.status = :je_status
3436 AND b.set_of_books_id(+) = :sob_id
3437 AND h.ledger_id = :sob_id
3438 AND b.set_of_books_id = h.ledger_id
3439 '|| vl_group_by_clause ||
3440 ' UNION
3441 SELECT MAX(xdl.SOURCE_DISTRIBUTION_ID_NUM_1) reference_1 ,
3442 SUM( NVL(xl.entered_dr, 0) - NVL(xl.entered_cr,0) ) line_amount '
3443 ||vl_attribute_cols ||
3444 ' FROM fv_be_trx_dtls B, xla_ae_lines xl , xla_distribution_links xdl,
3445 gl_je_lines l, gl_je_headers h, gl_import_references gli
3446 WHERE xl.code_combination_id = :ccid
3447 AND xl.ae_header_id = xdl.ae_header_id
3448 AND xl.ae_line_num = xdl.ae_line_num
3449 AND xl.gl_sl_link_id = gli.gl_sl_link_id
3450 and gli.je_batch_id = h.je_batch_id
3451 and gli.je_header_id = h.je_header_id
3452 and gli.je_line_num = l.je_line_num
3453 AND NVL(h.je_from_sla_flag, ''N'') = ''Y''
3454 --l.gl_sl_link_id is not null
3455 AND l.je_header_id = h.je_header_id
3456 AND EXISTS (SELECT 1
3457 FROM gl_period_statuses glp
3458 WHERE glp.application_id = 101
3459 AND glp.set_of_books_id = :sob_id
3460 AND glp.period_year = :closing_fyr
3461 AND glp.period_name = l.period_name)
3462 AND NVL(xdl.SOURCE_DISTRIBUTION_ID_NUM_1, '||''''||'-99'||''''||')
3463 = b.transaction_id (+)
3464 AND l.status = :je_status
3465 AND h.ledger_id = :sob_id
3466 AND b.set_of_books_id = h.LEDGER_id'
3467 ||vl_group_by_clause;
3468
3469
3470
3471 BEGIN
3472 dbms_sql.parse(c_gl_line_cur, vl_stmt, DBMS_SQL.V7) ;
3473 EXCEPTION
3474 WHEN OTHERS THEN
3475 vp_retcode := 2;
3476 vp_errbuf := SQLERRM||
3477 ' Parse cursor error in Populate_Gl_Interface procedure.';
3478 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name, vp_errbuf) ;
3479 RETURN;
3480 END ;
3481
3482 -- Bind the variables
3483 dbms_sql.bind_variable(c_gl_line_cur,':ccid', vl_ccid);
3484 dbms_sql.bind_variable(c_gl_line_cur,':sob_id', vg_sob_id);
3485 dbms_sql.bind_variable(c_gl_line_cur,':closing_fyr', vp_closing_fyr);
3486 dbms_sql.bind_variable(c_gl_line_cur,':je_status', 'P');
3487 dbms_sql.bind_variable(c_gl_line_cur,':sob_id', vg_sob_id);
3488
3489 dbms_sql.define_column(c_gl_line_cur, 1, vl_reference_1, 280);
3490 dbms_sql.define_column(c_gl_line_cur, 2, vl_line_amount);
3491
3492 vl_column_num := 3;
3493
3494 IF vg_public_law_attribute IS NOT NULL THEN
3495 dbms_sql.define_column(c_gl_line_cur, vl_column_num, vl_public_law_code, 150);
3496 vl_column_num := vl_column_num + 1;
3497 END IF;
3498
3499 IF vg_advance_type_attribute IS NOT NULL THEN
3500 dbms_sql.define_column(c_gl_line_cur, vl_column_num, vl_advance_type, 150);
3501 vl_column_num := vl_column_num + 1;
3502 END IF;
3503
3504 IF vg_trf_dept_id_attribute IS NOT NULL THEN
3505 dbms_sql.define_column(c_gl_line_cur, vl_column_num, vl_trf_dept_id, 150);
3506 vl_column_num := vl_column_num + 1;
3507 dbms_sql.define_column(c_gl_line_cur, vl_column_num, vl_trf_main_acct, 150);
3508 END IF;
3509
3510 BEGIN
3511 vl_exec_cur := dbms_sql.EXECUTE(c_gl_line_cur);
3512 EXCEPTION
3513 WHEN OTHERS THEN
3514 vp_retcode := 2 ;
3515 vp_errbuf := SQLERRM||
3516 ' Execute cursor error in Populate_Gl_Interface procedure.';
3517 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name, vp_errbuf) ;
3518 END ;
3519
3520 LOOP
3521
3522 vl_public_law_code := NULL;
3523 vl_advance_type := NULL;
3524 vl_trf_dept_id := NULL;
3525 vl_trf_main_acct := NULL;
3526
3527 IF dbms_sql.fetch_rows(c_gl_line_cur) = 0 THEN
3528 EXIT;
3529 ELSE
3530
3531 dbms_sql.column_value(c_gl_line_cur, 1, vl_reference_1);
3532 dbms_sql.column_value(c_gl_line_cur, 2, vl_line_amount);
3533
3534 vl_column_num := 3;
3535
3536 IF vg_public_law_attribute IS NOT NULL THEN
3537 dbms_sql.column_value(c_gl_line_cur, vl_column_num, vl_public_law_code);
3538 vl_column_num := vl_column_num + 1;
3539 END IF;
3540
3541 IF vg_advance_type_attribute IS NOT NULL THEN
3542 dbms_sql.column_value(c_gl_line_cur, vl_column_num, vl_advance_type);
3543 vl_column_num := vl_column_num + 1;
3544 END IF;
3545
3546 IF vg_trf_dept_id_attribute IS NOT NULL THEN
3547 dbms_sql.column_value(c_gl_line_cur, vl_column_num, vl_trf_dept_id);
3548 vl_column_num := vl_column_num + 1;
3549 dbms_sql.column_value(c_gl_line_cur, vl_column_num, vl_trf_main_acct);
3550 END IF;
3551
3552 -- vl_reference_1 := gl_record.reference_1;
3553 IF vl_line_amount <> 0 THEN -- consider only non zero balance lines
3554 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3555 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' LINE AMOUNT ' || VL_LINE_AMOUNT);
3556 END IF;
3557 vl_running_amount := vl_running_amount + vl_line_amount;
3558
3559 vl_amount_dr := 0;
3560 vl_amount_cr := 0;
3561
3562 IF vl_line_amount > 0 THEN
3563 vl_amount_cr := ABS(vl_line_amount);
3564 ELSE
3565 vl_amount_dr := ABS(vl_line_amount);
3566 END IF;
3567
3568 Insert_gl_interface_record(vl_amount_dr,vl_amount_cr,vl_reference_1,
3569 vl_period_name,vl_trading_partner, vl_public_law_code,
3570 vl_advance_type, vl_trf_dept_id, vl_trf_main_acct);
3571 END IF;
3572 END IF;
3573 END LOOP;
3574
3575 BEGIN
3576 dbms_sql.close_cursor(c_gl_line_cur);
3577 EXCEPTION
3578 WHEN OTHERS THEN
3579 vp_retcode := SQLCODE ;
3580 VP_ERRBUF := SQLERRM ;
3581 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name, vp_errbuf) ;
3582 RETURN ;
3583 END ;
3584
3585 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3586 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'AMOUNT MATCHED ' || VL_RUNNING_AMOUNT);
3587 END IF;
3588
3589 FOR facts2_ending_balance_rec IN (SELECT ffeb.ending_balance_cr,
3590 ffeb.ending_balance_dr,
3591 ffeb.transfer_dept_id,
3592 ffeb.public_law,
3593 ffeb.advance_flag,
3594 ffeb.transfer_main_acct
3595 FROM fv_factsii_ending_balances ffeb
3596 WHERE ffeb.set_of_books_id = vg_sob_id
3597 AND ffeb.fiscal_year = vp_closing_fyr-1
3598 AND ffeb.ccid = vl_ccid) LOOP
3599
3600 vl_running_amount := vl_running_amount + NVL(facts2_ending_balance_rec.ending_balance_dr, 0) - NVL(facts2_ending_balance_rec.ending_balance_cr, 0);
3601 Insert_gl_interface_record(facts2_ending_balance_rec.ending_balance_cr,facts2_ending_balance_rec.ending_balance_dr,
3602 NULL,
3603 vl_period_name,vl_trading_partner,
3604 facts2_ending_balance_rec.public_law,
3605 facts2_ending_balance_rec.advance_flag,
3606 facts2_ending_balance_rec.transfer_dept_id,
3607 facts2_ending_balance_rec.transfer_main_acct);
3608 END LOOP;
3609
3610
3611 vl_amount_dr := 0;
3612 vl_amount_cr := 0;
3613
3614 IF ABS(vl_amount) <> ABS(vl_running_amount) THEN
3615 -- Bug 4546827
3616 -- IF (vl_amount < 0) THEN
3617
3618 IF (vl_amount + vl_running_amount < 0) THEN
3619 vl_amount_cr := ABS(vl_amount + vl_running_amount);
3620 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3621 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'DIFFERENCE CR AMOUNT ADDED ' || VL_AMOUNT_CR);
3622 END IF;
3623 ELSE
3624 vl_amount_dr := ABS(vl_amount + vl_running_amount);
3625 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3626 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'DIFFERENCE DR AMOUNT ADDED ' || VL_AMOUNT_DR);
3627 END IF;
3628 END IF;
3629 -- Bug 7150443. Added vl_public_law_code to the call below.
3630 -- Reverted the change made for above.
3631 Insert_gl_interface_record(vl_amount_dr , vl_amount_cr, NULL,
3632 vl_period_name,vl_trading_partner);
3633 END IF;
3634 ELSE -- vl_ptype
3635 Insert_Gl_Interface_Record(vc_journals.period_net_dr,
3636 vc_journals.period_net_cr, vl_reference_1,
3637 vl_period_name,vl_trading_partner);
3638
3639 END IF; -- vl_ptype
3640 END IF; -- balance_read_flag
3641 END LOOP; -- journal_entries loop
3642
3643
3644 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3645 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' AFTER INSERTING INTO GL_INTERFACE....');
3646 END IF;
3647
3648 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3649 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CALLING THE JOURNAL IMPORT PROGRAM....');
3650 END IF;
3651 -- Submit a Concurrent request to invoke journal import
3652 vl_req_id := FND_REQUEST.SUBMIT_REQUEST('SQLGL',
3653 'GLLEZL',
3654 '',
3655 '',
3656 FALSE,
3657 TO_CHAR(vg_interface_run_id),
3658 TO_CHAR(vg_sob_id),
3659 'N', '', '', 'N', 'W');
3660
3661 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3662 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' THE REQUEST_ID IS '||VL_REQ_ID);
3663 END IF;
3664
3665 -- if concurrent request submission failed then abort process
3666 IF (vl_req_id = 0) THEN
3667 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3668 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' JOURNAL IMPORT REQUEST NOT SUBMITTED.');
3669 END IF;
3670 vp_errbuf := 'Cannot submit journal import program';
3671 vp_retcode := 1;
3672 ROLLBACK;
3673 RETURN;
3674 ELSE
3675 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3676 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' JOURNAL IMPORT REQUEST SUBMITTED SUCCESSFULLY.');
3677 END IF;
3678 COMMIT;
3679 END IF;
3680
3681 -- Check status of completed concurrent program
3682 -- and if complete exit
3683 vl_call_status := Fnd_Concurrent.Wait_For_Request(
3684 vl_req_id, 20, 0, vl_rphase, vl_rstatus,
3685 vl_dphase, vl_dstatus, vl_message);
3686
3687 IF (vl_call_status = FALSE) THEN
3688 vp_errbuf := 'Cannot wait for the status of journal import';
3689 vp_retcode := 1;
3690 END IF;
3691
3692 -- Clean up gl_interface table
3693 Cleanup_Gl_Interface;
3694
3695 IF (vp_retcode <> 0) THEN
3696 RETURN;
3697 END IF;
3698
3699 EXCEPTION
3700 WHEN OTHERS THEN
3701 vp_retcode := 2;
3702 vp_errbuf := SQLERRM || '--Error in Populate_Gl_Interface procedure.';
3703 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3704 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
3705 'When Others Exception ' || vp_errbuf );
3706 END IF;
3707 RETURN;
3708 END Populate_Gl_Interface;
3709
3710 -- ------------------------------------------------------------------
3711 -- Procedure Cleanup_Gl_Interface
3712 -- ------------------------------------------------------------------
3713 -- Cleanup_Gl_Interface procedure is called from Populate_Gl_interface
3714 -- procedure. This cleans up the gl_interface table.
3715 -- ------------------------------------------------------------------
3716 PROCEDURE Cleanup_Gl_Interface IS
3717 l_module_name VARCHAR2(200) ;
3718 BEGIN
3719
3720 l_module_name := g_module_name || 'Cleanup_Gl_Interface ';
3721
3722 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3723 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' IN THE CLEANUP_GL_INTERFACE PROCEDURE....');
3724 END IF;
3725
3726 -- Delete from Gl_Interface table
3727 DELETE FROM Gl_Interface
3728 WHERE user_je_source_name = 'Year End Close'
3729 AND ledger_id = vg_sob_id
3730 AND group_id = vg_jrnl_group_id;
3731
3732 EXCEPTION
3733 WHEN OTHERS THEN
3734 vp_retcode := 2;
3735 vp_errbuf := SQLERRM || '--Error in Cleanup_Gl_Interface procedure.';
3736 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3737 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
3738 'When Others Exception ' || vp_errbuf );
3739 END IF;
3740
3741 RETURN;
3742 END Cleanup_Gl_Interface;
3743
3744 -- ------------------------------------------------------------------
3745 -- Procedure Submit_Report
3746 -- ------------------------------------------------------------------
3747 -- Submit_Report procedure is called from Main procedure.
3748 -- This procedure submits the execution report.
3749 -- ------------------------------------------------------------------
3750 PROCEDURE Submit_Report IS
3751 vl_req_id NUMBER;
3752 vl_call_status BOOLEAN;
3753 vl_rphase VARCHAR2(30);
3754 vl_rstatus VARCHAR2(30);
3755 vl_dphase VARCHAR2(30);
3756 vl_dstatus VARCHAR2(30);
3757 vl_message VARCHAR2(240);
3758 l_module_name VARCHAR2(200) ;
3759
3760 BEGIN
3761 l_module_name := g_module_name || 'Submit_Report ';
3762
3763 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3764 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' IN THE SUBMIT_REPORT PROCEDURE....');
3765 END IF;
3766
3767 vl_req_id := Fnd_Request.Submit_Request('FV','FVXYECER','','',FALSE,
3768 vg_sob_id,vp_closing_fyr,vp_timeframe,
3769 vp_fundgroup,vp_trsymbol,vp_journal_import);
3770
3771 IF (vl_req_id = 0) THEN
3772 vp_retcode := 2;
3773 vp_errbuf := 'Error in Submit_Report procedure while submitting the '||
3774 ' Year End Execution Report';
3775 RETURN;
3776 ELSE
3777 COMMIT;
3778 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3779 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' YEAR END EXECUTION REPORT IS SUCCESSFULLY '||
3780 'submitted.....');
3781 END IF;
3782 END IF;
3783
3784 -- Check status of completed concurrent program
3785 -- and if complete exit
3786 vl_call_status := Fnd_Concurrent.Wait_For_Request(
3787 vl_req_id, 20, 0, vl_rphase, vl_rstatus,
3788 vl_dphase, vl_dstatus, vl_message);
3789
3790 IF (vl_call_status = FALSE) THEN
3791 vp_errbuf := 'Cannot wait for the status of Year End Execution Report';
3792 vp_retcode := 1;
3793 RETURN;
3794 END IF;
3795
3796 EXCEPTION
3797 WHEN OTHERS THEN
3798 vp_retcode := 2;
3799 vp_errbuf := SQLERRM || '--Error in Submit_Report procedure.';
3800 RETURN;
3801 END Submit_Report;
3802 ---------------------------------------------------------------
3803 PROCEDURE insert_gl_interface_record(l_amount_dr IN NUMBER,
3804 l_amount_cr IN NUMBER,
3805 l_reference_1 IN VARCHAR2,
3806 l_period_name IN VARCHAR2,
3807 l_trading_partner IN VARCHAR2,
3808 l_public_law_code IN VARCHAR2,
3809 l_advance_type IN VARCHAR2,
3810 l_trf_dept_id IN VARCHAR2,
3811 l_trf_main_acct IN VARCHAR2)
3812
3813 IS
3814
3815 TYPE attribtable IS TABLE OF gl_je_lines.attribute1%TYPE
3816 INDEX BY BINARY_INTEGER;
3817 vl_attribtable attribtable ;
3818
3819 vl_str VARCHAR2(3000);
3820 l_module_name VARCHAR2(200) ;
3821 BEGIN
3822 l_module_name := g_module_name || 'insert_gl_interface_record ';
3823
3824 FOR i IN 1..20
3825 LOOP
3826 vl_attribtable(i) := NULL;
3827 END LOOP;
3828
3829 IF vg_factsi_attribute IS NOT NULL THEN
3830 vl_attribtable(SUBSTR(vg_factsi_attribute, 10)) := l_trading_partner;
3831 END IF;
3832
3833 IF vg_public_law_attribute IS NOT NULL THEN
3834 vl_attribtable(SUBSTR(vg_public_law_attribute, 10)) := l_public_law_code;
3835 END IF;
3836
3837 IF vg_advance_type_attribute IS NOT NULL THEN
3838 vl_attribtable(SUBSTR(vg_advance_type_attribute, 10)) := l_advance_type;
3839 END IF;
3840
3841 IF vg_trf_dept_id_attribute IS NOT NULL THEN
3842 vl_attribtable(SUBSTR(vg_trf_dept_id_attribute, 10)) := l_trf_dept_id;
3843 vl_attribtable(SUBSTR(vg_trf_main_acct_attribute, 10)) := l_trf_main_acct;
3844 END IF;
3845
3846 vl_str := 'INSERT INTO Gl_Interface
3847 (
3848 status, ledger_id, accounting_date, currency_code,
3849 date_created, created_by, actual_flag, user_je_category_name,
3850 user_je_source_name, entered_dr, entered_cr, group_id,
3851 period_name, chart_of_accounts_id,
3852 segment1,segment2,segment3,
3853 segment4,segment5,segment6,
3854 segment7,segment8,segment9,
3855 segment10,segment11,segment12,
3856 segment13,segment14,segment15,
3857 segment16,segment17,segment18,
3858 segment19,segment20,segment21,
3859 segment22,segment23,segment24,
3860 segment25,segment26,segment27,
3861 segment28,segment29,segment30,
3862 reference21,context,
3863 attribute1, attribute2, attribute3, attribute4, attribute5,
3864 attribute6, attribute7, attribute8, attribute9, attribute10,
3865 attribute11, attribute12, attribute13, attribute14, attribute15,
3866 attribute16, attribute17, attribute18, attribute19, attribute20
3867 )
3868 VALUES
3869 (
3870 :status, :sob_id, :end_date, :currency,
3871 :current_date, :user_id, :actual_flag, :user_je_category,
3872 :user_je_source, :amount_dr, :amount_cr, :jrnl_group_id,
3873 :period_name, :coa_id,
3874 :vt_segments_1,:vt_segments_2,:vt_segments_3,
3875 :vt_segments_4,:vt_segments_5,:vt_segments_6,
3876 :vt_segments_7,:vt_segments_8,:vt_segments_9,
3877 :vt_segments_10, :vt_segments_11,:vt_segments_12,
3878 :vt_segments_13, :vt_segments_14,:vt_segments_15,
3879 :vt_segments_16, :vt_segments_17,:vt_segments_18,
3880 :vt_segments_19, :vt_segments_20, :vt_segments_21,
3881 :vt_segments_22,:vt_segments_23, :vt_segments_24,
3882 :vt_segments_25,:vt_segments_26, :vt_segments_27,
3883 :vt_segments_28,:vt_segments_29, :vt_segments_30,
3884 :reference_1,:context,
3885 :attribute1, :attribute2, :attribute3, :attribute4, :attribute5,
3886 :attribute6, :attribute7, :attribute8, :attribute9, :attribute10,
3887 :attribute11, :attribute12, :attribute13, :attribute14, :attribute15,
3888 :attribute16, :attribute17, :attribute18, :attribute19, :attribute20
3889 ) ' ;
3890
3891 EXECUTE IMMEDIATE vl_str USING
3892 'NEW', vg_sob_id, vg_end_date, vg_currency,
3893 SYSDATE, Fnd_Global.user_id, 'A', 'Year End Close',
3894 'Year End Close', l_amount_dr, l_amount_cr, vg_jrnl_group_id,
3895 l_period_name, vg_coa_id,
3896 vt_segments(1),vt_segments(2),vt_segments(3),
3897 vt_segments(4),vt_segments(5),vt_segments(6),
3898 vt_segments(7),vt_segments(8),vt_segments(9),
3899 vt_segments(10),
3900 vt_segments(11),vt_segments(12),vt_segments(13),
3901 vt_segments(14),vt_segments(15),vt_segments(16),
3902 vt_segments(17),vt_segments(18),vt_segments(19),
3903 vt_segments(20),
3904 vt_segments(21),vt_segments(22),vt_segments(23),
3905 vt_segments(24),vt_segments(25),vt_segments(26),
3906 vt_segments(27),vt_segments(28),vt_segments(29),
3907 vt_segments(30),
3908 l_reference_1,'Global Data Elements',
3909 vl_attribtable(1), vl_attribtable(2), vl_attribtable(3),
3910 vl_attribtable(4), vl_attribtable(5), vl_attribtable(6),
3911 vl_attribtable(7), vl_attribtable(8), vl_attribtable(9),
3912 vl_attribtable(10), vl_attribtable(11), vl_attribtable(12),
3913 vl_attribtable(13), vl_attribtable(14), vl_attribtable(15),
3914 vl_attribtable(16), vl_attribtable(17), vl_attribtable(18),
3915 vl_attribtable(19), vl_attribtable(20);
3916
3917 EXCEPTION
3918 WHEN OTHERS THEN
3919 vp_retcode := 2;
3920 vp_errbuf := SQLERRM || '--Error in Insert_Gl_Interface_Record procedure.';
3921 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3922 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
3923 'When Others Exception ' || vp_errbuf );
3924 END IF;
3925 END insert_gl_interface_record;
3926 -----------------------------------------------------------------------------
3927 -- ------------------------------------------------------------------
3928 -- Procedure Check_bal_seg_value
3929 -- ------------------------------------------------------------------
3930 -- BSV's are not always assigned to ledgers. Therefore we should not
3931 -- enforce BSV assignemnt if there is no BSV flex value set
3932 -- is assigned to a ledger.
3933 -- IF bal_seg_value_option_code column value in GL_LEDGER table is
3934 -- 'A' that means all BSV's are valid. If the column is 'I',
3935 -- then some BSV's are valid.
3936 -- ------------------------------------------------------------------
3937
3938 PROCEDURE Check_bal_seg_value( vp_fund_grp VARCHAR2,
3939 vp_time_frame VARCHAR,
3940 vp_tsymbol_id VARCHAR ,
3941 vp_sob_id NUMBER ,
3942 vp_end_date DATE )
3943 IS
3944 TYPE v_fund_val_blk IS TABLE OF fv_fund_parameters.fund_value%TYPE
3945 index by binary_integer;
3946
3947 V_fund_blk_tbl v_fund_val_blk;
3948 l_select_stmt varchar2(2500);
3949 l_module_name VARCHAR2(200) ;
3950 vl_valid_value VARCHAR2(1);
3951 Type v_ref_cursor is REF CURSOR;
3952 v_fund_val_cursor v_ref_cursor;
3953 vl_invalid_fund varchar2(1);
3954 BEGIN
3955 l_module_name := g_module_name || 'Check_bal_seg_value ';
3956 vl_invalid_fund := 'Y';
3957 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3958 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
3959 l_module_name,
3960 ' IN CHECK_BAL_SEG_VALUE PROCEDURE '||
3961 'with the following Parameters passed to the process:');
3962 END IF;
3963 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3964 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
3965 l_module_name,
3966 ' FUND GROUP= '||VP_FUND_GRP);
3967 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
3968 l_module_name,
3969 ' TIME FRAME= '||VP_TIME_FRAME);
3970 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
3971 l_module_name,
3972 ' TREASURY SYMBOL ID= '||VP_TSYMBOL_ID);
3973 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
3974 l_module_name,
3975 ' SET OF BOOKS ID= '||VP_SOB_ID);
3976 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
3977 l_module_name,
3978 ' END DATE= '||VP_END_DATE);
3979 END IF;
3980
3981 L_select_stmt := ' SELECT ffp.fund_value
3982 FROM fv_fund_parameters ffp,
3983 Fv_Ye_Groups fyg ,
3984 fv_treasury_symbols fts
3985 WHERE fyg.fund_group_code = fts.fund_group_code
3986 AND fts.time_frame = fyg.fund_time_frame
3987 AND fts.treasury_symbol_id = fyg.treasury_symbol_id
3988 AND fts.treasury_symbol_id = ffp.treasury_symbol_id
3989 AND fyg.fund_group_code = NVL(:fundgroup, fyg.fund_group_code)
3990 AND fyg.fund_time_frame = NVL(:timeframe, fyg.fund_time_frame)
3991 AND fyg.treasury_symbol = NVL(:TSYMBOLID, fyg.treasury_symbol_id)
3992 AND fts.set_of_books_id = :sob_id
3993 AND fts.set_of_books_id = fyg.set_of_books_id
3994 AND fts.set_of_books_id = ffp.set_of_books_id
3995 AND ((fts.expiration_date <= :end_date)
3996 OR (fts.cancellation_date <= :end_date))';
3997
3998 EXECUTE IMMEDIATE L_SELECT_STMT BULK COLLECT INTO v_fund_blk_tbl
3999 USING vp_fund_grp,
4000 vp_time_frame,
4001 vp_tsymbol_id ,
4002 vp_sob_id,
4003 vp_end_date,
4004 vp_end_date;
4005
4006 FOR I in 1 .. v_fund_blk_tbl.count
4007 LOOP
4008 BEGIN
4009 SELECT 'N'
4010 INTO vl_invalid_fund
4011 FROM gl_ledger_segment_values glsv
4012 WHERE glsv.ledger_id = vp_sob_id
4013 AND glsv.segment_type_code (+) = 'B'
4014 AND NVL(glsv.status_code (+), 'X') <> 'I'
4015 AND NVL(glsv.start_date (+),TO_DATE('1950/01/01','YYYY/MM/DD'))
4016 <= NVL(sysdate,TO_DATE('9999/12/31','YYYY/MM/DD'))
4017 AND NVL(glsv.end_date (+),TO_DATE('9999/12/31','YYYY/MM/DD'))
4018 >= NVL(sysdate, TO_DATE('1950/01/01','YYYY/MM/DD'))
4019 AND glsv.segment_value (+) = v_fund_blk_tbl(i);
4020
4021 EXCEPTION
4022 WHEN NO_DATA_FOUND THEN
4023 IF vl_invalid_fund = 'Y' THEN
4024 FV_UTILITY.LOG_MESG('The below fund values are not valid balance segment
4025 values for the Ledger:');
4026 End if;
4027 vl_invalid_fund := 'W' ;
4028 FV_UTILITY.LOG_MESG('');
4029 FV_UTILITY.LOG_MESG(v_fund_blk_tbl(i));
4030 END;
4031 END LOOP;
4032 EXCEPTION
4033 WHEN OTHERS THEN
4034 vp_retcode := 2 ;
4035 vp_errbuf := SQLERRM ||' -- Error in Check Check_bal_seg_value procedure.' ;
4036 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4037 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED,
4038 l_module_name,
4039 'When Others Exception ' || vp_errbuf );
4040 END IF;
4041 RETURN ;
4042 END Check_bal_seg_value;
4043
4044 BEGIN
4045 g_module_name := 'fv.plsql.Fv_Ye_Close.';
4046 vg_factsi_attr_exists := 'N';
4047 vp_retcode := 0 ;
4048
4049
4050 END Fv_Ye_Close;
[Home] [Help]