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