[Home] [Help]
PACKAGE BODY: APPS.FV_FACTS2_DERIVE_BALANCES
Source
1 PACKAGE BODY fv_facts2_derive_balances AS
2 /* $Header: FVFCT2BB.pls 120.0.12000000.4 2007/10/05 20:08:17 sasukuma noship $*/
3
4 g_module_name VARCHAR2(100);
5 g_FAILURE NUMBER;
6 g_SUCCESS NUMBER;
7 g_WARNING NUMBER;
8 g_request_id NUMBER;
9 g_user_id NUMBER;
10 g_login_id NUMBER;
11 g_enter VARCHAR2(10);
12 g_exit VARCHAR2(10);
13
14 PROCEDURE report
15 (
16 p_msg IN VARCHAR2
17 )
18 IS
19 BEGIN
20 fnd_file.put_line (fnd_file.output, p_msg);
21 END;
22
23 PROCEDURE generate_output_report
24 (
25 p_ledger_id IN gl_ledgers_public_v.ledger_id%TYPE,
26 p_fiscal_year IN gl_period_statuses.period_year%TYPE,
27 p_error_code OUT NOCOPY NUMBER,
28 p_error_desc OUT NOCOPY VARCHAR2
29 )
30 IS
31 l_module_name VARCHAR2(200);
32 l_location VARCHAR2(200);
33 BEGIN
34 l_module_name := g_module_name || 'generate_output_report';
35 p_error_code := g_SUCCESS;
36
37 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
38 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_enter);
39 END IF;
40
41 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
42 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_ledger_id = '||p_ledger_id);
43 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_fiscal_year = '||p_fiscal_year);
44 END IF;
45
46 report (' FACTS II Ending Balances');
47 report ('Ledger Id: '||p_ledger_id);
48 report ('Fiscal Year: '||p_fiscal_year);
49 report ('+-------+----------+-------+---------+--------------+-----------------+-----------------+');
50 report ('|Account|Public Law|Adv Flg|Txfr Dept|Txfr Main Acct|Ending Balance Cr|Ending Balance Cr|');
51 report ('|-------+----------+-------+---------+--------------+-----------------+-----------------|');
52 FOR factsii_rec IN (SELECT *
53 FROM fv_factsii_ending_balances
54 WHERE set_of_books_id = p_ledger_id
55 AND fiscal_year = p_fiscal_year) LOOP
56 report ('|'||
57 RPAD (factsii_rec.ussgl_account, 7, ' ')||
58 '|'||
59 RPAD (NVL(factsii_rec.public_law, ' '), 10, ' ')||
60 '|'||
61 RPAD (NVL(factsii_rec.advance_flag, ' '), 7, ' ')||
62 '|'||
63 RPAD (NVL(factsii_rec.transfer_dept_id, ' '), 9, ' ')||
64 '|'||
65 RPAD (NVL(factsii_rec.transfer_main_acct, ' '), 14, ' ')||
66 '|'||
67 LPAD (NVL(TO_CHAR(factsii_rec.ending_balance_dr), ' '), 17, ' ')||
68 '|'||
69 LPAD (NVL(TO_CHAR(factsii_rec.ending_balance_cr), ' '), 17, ' ')||
70 '|');
71
72 END LOOP;
73 report ('+-------+----------+-------+---------+--------------+-----------------+-----------------+');
74
75 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
76 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
77 END IF;
78
79 EXCEPTION
80 WHEN OTHERS THEN
81 p_error_code := g_FAILURE;
82 p_error_desc := SQLERRM;
83 l_location := l_module_name||'.final_exception';
84 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
85 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
86 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
87 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
88 END IF;
89 END;
90
91
92 --****************************************************************************************--
93 --* Name : initialize_global_variables *--
94 --* Type : Procedure *--
95 --* Purpose : To initialize all global variables *--
96 --* Parameters : None *--
97 --* Global Vars : As in procedure *--
98 --* Called from : Called when initializing the package *--
99 --* Calls : None *--
100 --* Tables Used : None *--
101 --* Logic : No Logic *--
102 --****************************************************************************************--
103 PROCEDURE initialize_global_variables
104 IS
105 BEGIN
106 g_module_name := 'fv.plsql.fv_facts2_derive_balances.';
107 g_FAILURE := -1;
108 g_SUCCESS := 0;
109 g_WARNING := -2;
110 g_request_id := fnd_global.conc_request_id;
111 g_user_id := fnd_global.user_id;
112 g_login_id := fnd_global.login_id;
113 g_enter := 'ENTER';
114 g_exit := 'EXIT';
115 END;
116
117 --****************************************************************************************--
118 --* Name : explode_accounts *--
119 --* Type : Procedure *--
120 --* Purpose : Looks at all the FACTSII accounts and puts those accounts along with *--
121 --* : their child accounts into the temporary table fv_factsii_accounts_gt *--
122 --* Parameters : p_ledger_id Set Of Books Id *--
123 --* : p_acct_value_set_id Account Value Set Id *--
124 --* : p_error_code Return Error Code *--
125 --* : p_error_desc Return Error Description *--
126 --* Global Vars : fnd_log.g_current_runtime_level *--
127 --* : fnd_log.level_procedure *--
128 --* : fnd_log.level_statement *--
129 --* : fnd_log.level_unexpected *--
130 --* : g_module_name *--
131 --* : g_SUCCESS *--
132 --* Called from : derive_balances *--
133 --* Calls : fv_utility.debug_mesg *--
134 --* : fv_utility.log_mesg *--
135 --* Tables Used : fnd_flex_value_hierarchies SELECT *--
136 --* : fnd_flex_values_vl SELECT *--
137 --* : fv_facts_attributes SELECT *--
138 --* : fv_facts_ussgl_accounts SELECT *--
139 --* : fv_factsii_accounts_gt INSERT *--
140 --* Logic : 1. Get all the FACTSII accounts from table fv_facts_ussgl_accounts *--
141 --* : and select only those accounts from table fv_facts_attributes *--
142 --* : where the following attributes are set to Y. public_law_code, *--
143 --* : advance_flag and transfer_flag *--
144 --* : 2. Insert that account into table fv_factsii_accounts_gt *--
145 --* : 3. Find all the child accounts using the tables fnd_flex_values_vl *--
146 --* : and fnd_flex_value_hierarchies and insert those too into the *--
147 --* : temporary table fv_factsii_accounts_gt *--
148 --****************************************************************************************--
149 PROCEDURE explode_accounts
150 (
151 p_ledger_id IN gl_ledgers_public_v.ledger_id%TYPE,
152 p_acct_value_set_id IN NUMBER,
153 p_error_code OUT NOCOPY NUMBER,
154 p_error_desc OUT NOCOPY VARCHAR2
155 )
156 IS
157 l_module_name VARCHAR2(200);
158 l_location VARCHAR2(200);
159
160
161 CURSOR get_hierarchies_cursor
162 (
163 c_account VARCHAR2,
164 c_flex_value_set_id NUMBER
165 )
166 IS
167 SELECT child_flex_value_low,
168 child_flex_value_high
169 FROM fnd_flex_value_hierarchies
170 WHERE parent_flex_value = c_account
171 AND flex_value_set_id = c_flex_value_set_id;
172
173
174 CURSOR get_child_values_cursor
175 (
176 c_flex_value_set_id NUMBER,
177 c_flex_value_low VARCHAR2,
178 c_flex_value_hi VARCHAR2
179 )
180 IS
181 SELECT flex_value account
182 FROM fnd_flex_values_vl
183 WHERE flex_value_set_id = c_flex_value_set_id
184 AND flex_value BETWEEN c_flex_value_low AND c_flex_value_hi;
185
186
187 BEGIN
188 l_module_name := g_module_name || 'explode_accounts';
189 p_error_code := g_SUCCESS;
190
191 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
192 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_enter);
193 END IF;
194
195 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
196 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_ledger_id = '||p_ledger_id);
197 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_acct_value_set_id = '||p_acct_value_set_id);
198 END IF;
199
200 FOR account_rec IN (SELECT fsgl.ussgl_account,
201 fa.public_law_code,
202 fa.advance_flag,
203 fa.transfer_flag
204 FROM fv_facts_attributes fa,
205 fv_facts_ussgl_accounts fsgl
206 WHERE fa.set_of_books_id = p_ledger_id
207 AND fsgl.reporting_type in (2,3)
208 AND fsgl.ussgl_account = fa.ussgl_acct_number
209 AND (fa.public_law_code = 'Y' OR
210 fa.advance_flag = 'Y' OR
211 fa.transfer_flag = 'Y')) LOOP
212 BEGIN
213 INSERT INTO fv_factsii_accounts_gt
214 (
215 ussgl_account,
216 account,
217 public_law_code,
218 advance_flag,
219 transfer_flag
220 )
221 VALUES
222 (
223 account_rec.ussgl_account,
224 account_rec.ussgl_account,
225 account_rec.public_law_code,
226 account_rec.advance_flag,
227 account_rec.transfer_flag
228 );
229 EXCEPTION
230 WHEN OTHERS THEN
231 p_error_code := g_FAILURE;
232 p_error_desc := SQLERRM;
233 l_location := l_module_name||'insert_fv_factsii_accounts_gt';
234 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
235 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
236 END;
237
238 IF (p_error_code = g_SUCCESS) THEN
239 FOR get_hierarchies_rec IN get_hierarchies_cursor(account_rec.ussgl_account,
240 p_acct_value_set_id) LOOP
241 FOR get_child_values_rec IN get_child_values_cursor(p_acct_value_set_id,
242 get_hierarchies_rec.child_flex_value_low,
243 get_hierarchies_rec.child_flex_value_high) LOOP
244 BEGIN
245 INSERT INTO fv_factsii_accounts_gt
246 (
247 ussgl_account,
248 account,
249 public_law_code,
250 advance_flag,
251 transfer_flag
252 )
253 VALUES
254 (
255 account_rec.ussgl_account,
256 get_child_values_rec.account,
257 account_rec.public_law_code,
258 account_rec.advance_flag,
259 account_rec.transfer_flag
260 );
261 EXCEPTION
262 WHEN OTHERS THEN
263 p_error_code := g_FAILURE;
264 p_error_desc := SQLERRM;
265 l_location := l_module_name||'insert_fv_factsii_accounts_gt2';
266 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
267 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
268 END;
269 IF (p_error_code <> g_SUCCESS) THEN
270 EXIT;
271 END IF;
272 END LOOP;
273 IF (p_error_code <> g_SUCCESS) THEN
274 EXIT;
275 END IF;
276 END LOOP;
277 END IF;
278
279
280 IF (p_error_code <> g_SUCCESS) THEN
281 EXIT;
282 END IF;
283 END LOOP;
284
285 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
286 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
287 END IF;
288 EXCEPTION
289 WHEN OTHERS THEN
290 p_error_code := g_FAILURE;
291 p_error_desc := SQLERRM;
292 l_location := l_module_name||'.final_exception';
293 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
294 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
295 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
296 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
297 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
298 END IF;
299 END;
300
301 --****************************************************************************************--
302 --* Name : initialize_program_variables *--
303 --* Type : Procedure *--
304 --* Purpose : One time initialization of all the variables required by this *--
305 --* : concurrent program. *--
306 --* Parameters : p_ledger_id IN Set Of Books Id *--
307 --* : p_fiscal_year IN Fiscal Year *--
308 --* : p_last_period_num OUT Returns the last non adjusting period num *--
309 --* : for the fiscal year *--
310 --* : p_chart_of_accounts_id OUT Returns Chart of Accounts Id for the SOB *--
311 --* : p_acct_segment OUT Returns the Accounting Segment for the *--
312 --* : chart of accounts *--
313 --* : p_acct_value_set_id OUT Returns the Accounting Segment Value Set *--
314 --* : Id for the chart of accounts *--
315 --* : p_bal_segment OUT Returns the Balancing Segment for the *--
316 --* : chart of accounts *--
317 --* : p_fyr_segment OUT Returns the Fiscal Year Segment *--
318 --* : p_pub_law_code_col OUT Returns the public law code attribute col *--
319 --* : p_advance_type_col OUT Returns the advance type attribute col *--
320 --* : p_tr_main_acct_col OUT Returns the Main Account attribute col *--
321 --* : p_tr_dept_id_col OUT Returns the Department Id attribute col *--
322 --* : p_error_code OUT Return Error Code *--
323 --* : p_error_desc OUT Return Error Description *--
324 --* Global Vars : fnd_log.g_current_runtime_level *--
325 --* : fnd_log.level_procedure *--
326 --* : fnd_log.level_statement *--
327 --* : fnd_log.level_unexpected *--
328 --* : g_module_name *--
329 --* : g_SUCCESS *--
330 --* Called from : derive_balances *--
331 --* Calls : fv_utility.debug_mesg *--
332 --* : fv_utility.log_mesg *--
333 --* : fnd_flex_apis.get_segment_column *--
334 --* Tables Used : gl_sets_of_books SELECT *--
335 --* : fnd_id_flex_segments SELECT *--
336 --* : gl_period_statuses SELECT *--
337 --* : fv_system_parameters SELECT *--
338 --* Logic : 1. Get the chart of accounts id from gl_sets_of_books using the *--
339 --* : set of books id. *--
340 --* : 2. Call fnd_flex_apis.get_segment_column with the chart of accounts *--
341 --* : id and get the GL_ACCOUNT segment name *--
342 --* : 3. From fnd_id_flex_segments using the chart of acconts id and the *--
343 --* : accounting segment name, get the Account value set id *--
344 --* : 4. From gl_period_statuses get the last non adjusting period for the *--
345 --* : Fiscal year and set of books id. *--
346 --* : 5. From fv_system_parameters get the following attributes. *--
347 --* : factsii_advance_type_attribute,factsii_tr_main_acct_attribute, *--
348 --* : factsii_tr_dept_id_attribute and factsii_pub_law_code_attribute *--
349 --****************************************************************************************--
350 PROCEDURE initialize_program_variables
351 (
352 p_ledger_id IN gl_ledgers_public_v.ledger_id%TYPE,
353 p_fiscal_year IN gl_period_statuses.period_year%TYPE,
354 p_last_period_num OUT NOCOPY gl_period_statuses.period_num%TYPE,
355 p_chart_of_accounts_id OUT NOCOPY gl_ledgers_public_v.chart_of_accounts_id%TYPE,
356 p_acct_segment OUT NOCOPY fnd_id_flex_segments.application_column_name%TYPE,
357 p_acct_value_set_id OUT NOCOPY fnd_id_flex_segments.flex_value_set_id%TYPE,
358 p_bal_segment OUT NOCOPY fnd_id_flex_segments.application_column_name%TYPE,
359 p_fyr_segment OUT NOCOPY fnd_id_flex_segments.application_column_name%TYPE,
360 p_pub_law_code_col OUT NOCOPY fv_system_parameters.factsii_pub_law_code_attribute%TYPE,
361 p_advance_type_col OUT NOCOPY fv_system_parameters.factsii_advance_type_attribute%TYPE,
362 p_tr_main_acct_col OUT NOCOPY fv_system_parameters.factsii_tr_main_acct_attribute%TYPE,
363 p_tr_dept_id_col OUT NOCOPY fv_system_parameters.factsii_tr_dept_id_attribute%TYPE,
364 p_error_code OUT NOCOPY NUMBER,
365 p_error_desc OUT NOCOPY VARCHAR2
366 )
367 IS
368 l_module_name VARCHAR2(200);
369 l_location VARCHAR2(200);
370 l_application_id NUMBER := 101;
371 l_id_flex_code VARCHAR2(25) := 'GL#';
372 l_acct_seg_attr_type VARCHAR2(30) := 'GL_ACCOUNT';
373 l_bal_seg_attr_type VARCHAR2(30) := 'GL_BALANCING';
374 l_retval BOOLEAN;
375
376 BEGIN
377 l_module_name := g_module_name || 'initialize_program_variables';
378 p_error_code := g_SUCCESS;
379
380 p_chart_of_accounts_id := NULL;
381 p_acct_segment := NULL;
382 p_acct_value_set_id := NULL;
383
384 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
385 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_enter);
386 END IF;
387
388 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
389 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_ledger_id = '||p_ledger_id);
390 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_fiscal_year = '||p_fiscal_year);
391 END IF;
392
393 IF (p_error_code = g_SUCCESS) THEN
394 BEGIN
395
396 SELECT glp.chart_of_accounts_id
397 INTO p_chart_of_accounts_id
398 FROM gl_ledgers_public_v glp
399 WHERE glp.ledger_id = p_ledger_id;
400
401 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
402 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_chart_of_accounts_id='||p_chart_of_accounts_id);
403 END IF;
404 EXCEPTION
405 WHEN OTHERS THEN
406 p_error_code := g_FAILURE;
407 p_error_desc := SQLERRM;
408 l_location := l_module_name||'select chart_of_accounts_id';
409 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
410 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
411 END;
412 END IF;
413
414 IF (p_error_code = g_SUCCESS) THEN
415 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
416 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling fnd_flex_apis.get_segment_column');
417 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_application_id ='||l_application_id);
418 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_id_flex_code ='||l_id_flex_code);
419 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_chart_of_accounts_id ='||p_chart_of_accounts_id);
420 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_acct_seg_attr_type ='||l_acct_seg_attr_type);
421 END IF;
422 l_retval := fnd_flex_apis.get_segment_column
423 (
424 x_application_id => l_application_id,
425 x_id_flex_code => l_id_flex_code,
426 x_id_flex_num => p_chart_of_accounts_id,
427 x_seg_attr_type => l_acct_seg_attr_type,
428 x_app_column_name => p_acct_segment
429 );
430 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
431 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_acct_segment ='||p_acct_segment);
432 END IF;
433 IF (NOT l_retval) THEN
434 p_error_code := g_FAILURE;
435 p_error_desc := fnd_message.get;
436 l_location := l_module_name||'call_fnd_flex_apis.get_segment_column';
437 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
438 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
439 END IF;
440 END IF;
441
442 IF (p_error_code = g_SUCCESS) THEN
443 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
444 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling fnd_flex_apis.get_segment_column');
445 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_application_id ='||l_application_id);
446 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_id_flex_code ='||l_id_flex_code);
447 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_chart_of_accounts_id ='||p_chart_of_accounts_id);
448 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_acct_seg_attr_type ='||l_bal_seg_attr_type);
449 END IF;
450 l_retval := fnd_flex_apis.get_segment_column
451 (
452 x_application_id => l_application_id,
453 x_id_flex_code => l_id_flex_code,
454 x_id_flex_num => p_chart_of_accounts_id,
455 x_seg_attr_type => l_bal_seg_attr_type,
456 x_app_column_name => p_bal_segment
457 );
458 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
459 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_acct_segment ='||p_acct_segment);
460 END IF;
461 IF (NOT l_retval) THEN
462 p_error_code := g_FAILURE;
463 p_error_desc := fnd_message.get;
464 l_location := l_module_name||'call_fnd_flex_apis.get_segment_column';
465 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
466 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
467 END IF;
468 END IF;
469
470 IF (p_error_code = g_SUCCESS) THEN
471 BEGIN
472 SELECT flex_value_set_id
473 INTO p_acct_value_set_id
474 FROM fnd_id_flex_segments
475 WHERE application_column_name = p_acct_segment
476 AND application_id = l_application_id
477 AND id_flex_code = l_id_flex_code
478 AND id_flex_num = p_chart_of_accounts_id
479 AND enabled_flag = 'Y';
480 EXCEPTION
481 WHEN OTHERS THEN
482 p_error_code := g_FAILURE;
483 p_error_desc := SQLERRM;
484 l_location := l_module_name||'select_fnd_id_flex_segments';
485 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
486 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
487 END;
488 END IF;
489
490 IF (p_error_code = g_SUCCESS) THEN
491 BEGIN
492 SELECT MAX(period_num)
493 INTO p_last_period_num
494 FROM gl_period_statuses gps
495 WHERE gps.ledger_id = p_ledger_id
496 AND gps.application_id = l_application_id
497 AND gps.period_year = p_fiscal_year;
498 EXCEPTION
499 WHEN OTHERS THEN
500 p_error_code := g_FAILURE;
501 p_error_desc := SQLERRM;
502 l_location := l_module_name||'select_gl_period_statuses';
503 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
504 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
505 END;
506 END IF;
507
508 IF (p_error_code = g_SUCCESS) THEN
509 BEGIN
510 SELECT factsii_pub_law_code_attribute,
511 factsii_advance_type_attribute,
512 factsii_tr_main_acct_attribute,
513 factsii_tr_dept_id_attribute
514 INTO p_pub_law_code_col,
515 p_advance_type_col,
516 p_tr_main_acct_col,
517 p_tr_dept_id_col
518 FROM fv_system_parameters;
519 EXCEPTION
520 WHEN OTHERS THEN
521 p_error_code := g_FAILURE;
522 p_error_desc := SQLERRM;
523 l_location := l_module_name||'select_gl_period_statuses';
524 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
525 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
526 END;
527 END IF;
528
529 IF (p_error_code = g_SUCCESS) THEN
530 BEGIN
531 SELECT application_column_name
532 INTO p_fyr_segment
533 FROM fv_pya_fiscalyear_segment
534 WHERE set_of_books_id = p_ledger_id;
535 EXCEPTION
536 WHEN OTHERS THEN
537 p_error_code := g_FAILURE;
538 p_error_desc := SQLERRM;
539 l_location := l_module_name||'select_fv_pya_fiscalyear_segment';
540 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
541 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
542 END;
543 END IF;
544
545 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
546 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
547 END IF;
548 EXCEPTION
549 WHEN OTHERS THEN
550 p_error_code := g_FAILURE;
551 p_error_desc := SQLERRM;
552 l_location := l_module_name||'.final_exception';
553 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
554 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
555 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
556 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
557 END IF;
558 END;
559
560 --****************************************************************************************--
561 --* Name : purge_balances *--
562 --* Type : Procedure *--
563 --* Purpose : Purges data from the table fv_factsii_ending_balances for the given *--
564 --* : set of books id and fiscal year. *--
565 --* Parameters : p_ledger_id IN Set Of Books Id *--
566 --* : p_fiscal_year IN Fiscal Year *--
567 --* : p_error_code OUT Return Error Code *--
568 --* : p_error_desc OUT Return Error Descion *--
569 --* Global Vars : fnd_log.g_current_runtime_level *--
570 --* : fnd_log.level_procedure *--
571 --* : fnd_log.level_statement *--
572 --* : fnd_log.level_unexpected *--
573 --* : g_module_name *--
574 --* : g_SUCCESS *--
575 --* Called from : derive_balances *--
576 --* Calls : fv_utility.debug_mesg *--
577 --* : fv_utility.log_mesg *--
578 --* Tables Used : fv_factsii_ending_balances DELETE *--
579 --* Logic : 1. Delete data from table fv_factsii_ending_balances for the given *--
580 --* : set of books id and fiscal year.
581 --****************************************************************************************--
582 PROCEDURE purge_balances
583 (
584 p_ledger_id IN gl_ledgers_public_v.ledger_id%TYPE,
585 p_fiscal_year IN gl_period_statuses.period_year%TYPE,
586 p_error_code OUT NOCOPY NUMBER,
587 p_error_desc OUT NOCOPY VARCHAR2
588 )
589 IS
590 l_module_name VARCHAR2(200);
591 l_location VARCHAR2(200);
592 BEGIN
593 l_module_name := g_module_name || 'purge_balances';
594 p_error_code := g_SUCCESS;
595
596 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
597 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_enter);
598 END IF;
599
600 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
601 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_ledger_id = '||p_ledger_id);
602 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_fiscal_year = '||p_fiscal_year);
603 END IF;
604
605 BEGIN
606 DELETE fv_factsii_ending_balances
607 WHERE set_of_books_id = p_ledger_id
608 AND fiscal_year = p_fiscal_year;
609 EXCEPTION
610 WHEN OTHERS THEN
611 p_error_code := g_FAILURE;
612 p_error_desc := SQLERRM;
613 l_location := l_module_name||'delete_fv_factsii_ending_balances';
614 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
615 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
616 END;
617
618 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
619 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
620 END IF;
621
622 EXCEPTION
623 WHEN OTHERS THEN
624 p_error_code := g_FAILURE;
625 p_error_desc := SQLERRM;
626 l_location := l_module_name||'.final_exception';
627 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
628 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
629 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
630 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
631 END IF;
632 END;
633
634
635 --****************************************************************************************--
636 --* Name : get_treasury_info *--
637 --* Type : Procedure *--
638 --* Purpose :
639 --* Parameters :
640 --* : p_error_code OUT Return Error Code *--
641 --* : p_error_desc OUT Return Error Descion *--
642 --* Global Vars : fnd_log.g_current_runtime_level *--
643 --* : fnd_log.level_procedure *--
644 --* : fnd_log.level_statement *--
645 --* : fnd_log.level_unexpected *--
646 --* : g_module_name *--
647 --* : g_SUCCESS *--
648 --* Called from : start_processing *--
649 --* Calls : fv_utility.debug_mesg *--
650 --* : fv_utility.log_mesg *--
651 --* Tables Used :
652 --* Logic :
653 --****************************************************************************************--
654 PROCEDURE get_treasury_info
655 (
656 p_ledger_id IN NUMBER,
657 p_fund_value IN VARCHAR2,
658 p_treasury_symbol_id OUT NOCOPY fv_treasury_symbols.treasury_symbol_id%TYPE,
659 p_cohort_segment OUT NOCOPY VARCHAR2,
660 p_error_code OUT NOCOPY NUMBER,
661 p_error_desc OUT NOCOPY VARCHAR2
662 )
663 IS
664 l_module_name VARCHAR2(200);
665 l_location VARCHAR2(200);
666 BEGIN
667 l_module_name := g_module_name || 'get_treasury_info';
668 p_error_code := g_SUCCESS;
669
670 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
671 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_enter);
672 END IF;
673
674 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
675 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_ledger_id = '||p_ledger_id);
676 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_fund_value = '||p_fund_value);
677 END IF;
678
679 BEGIN
680 SELECT fp.treasury_symbol_id,
681 ffa.cohort_segment_name
682 INTO p_treasury_symbol_id,
683 p_cohort_segment
684 FROM fv_fund_parameters fp,
685 fv_treasury_symbols fts,
686 fv_facts_federal_accounts ffa
687 WHERE fp.set_of_books_id = p_ledger_id
688 AND fp.fund_value = p_fund_value
689 AND fts.treasury_symbol_id = fp.treasury_symbol_id
690 AND fts.federal_acct_symbol_id = ffa.federal_acct_symbol_id
691 AND fts.set_of_books_id = p_ledger_id
692 AND ffa.set_of_books_id = p_ledger_id;
693 EXCEPTION
694 WHEN OTHERS THEN
695 p_error_code := g_FAILURE;
696 p_error_desc := SQLERRM;
697 l_location := l_module_name||'select_fv_fund_parameters';
698 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
699 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
700 END;
701
702 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
703 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
704 END IF;
705
706 EXCEPTION
707 WHEN OTHERS THEN
708 p_error_code := g_FAILURE;
709 p_error_desc := SQLERRM;
710 l_location := l_module_name||'.final_exception';
711 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
712 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
713 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
714 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
715 END IF;
716 END;
717
718 --****************************************************************************************--
719 --* Name : start_processing *--
720 --* Type : Procedure *--
721 --* Purpose : This procedure does tha main processing of deriving the actual *--
722 --* actual FACTSII balances *--
723 --* Parameters : p_ledger_id IN Set Of Books Id *--
724 --* : p_acct_segment IN Accounting Segment *--
725 --* : p_bal_segment IN Balancing Segment *--
726 --* : p_bal_segment IN Fiscal Year Segment *--
727 --* : p_fiscal_year IN Fiscal Year *--
728 --* : p_last_period_num IN Last Non Adjusting Period for the Fiscal Year *--
729 --* : p_pub_law_code_col IN Public Law Code Attribute column *--
730 --* : p_advance_type_col IN Advance Type Attribute column *--
731 --* : p_tr_main_acct_col IN Transfer Main Account Attribute column *--
732 --* : p_tr_dept_id_col IN Department Id Attribute column *--
733 --* : p_error_code OUT Return Error Code *--
734 --* : p_error_desc OUT Return Error Descion *--
735 --* Global Vars : fnd_log.g_current_runtime_level *--
736 --* : fnd_log.level_procedure *--
737 --* : fnd_log.level_statement *--
738 --* : fnd_log.level_unexpected *--
739 --* : g_module_name *--
740 --* : g_SUCCESS *--
741 --* Called from : derive_balances *--
742 --* Calls : fv_utility.debug_mesg *--
743 --* : fv_utility.log_mesg *--
744 --* Tables Used : fv_factsii_ending_balances INSERT *--
745 --* : gl_je_lines gll SELECT *--
746 --* : gl_je_headers gjh SELECT *--
747 --* : gl_period_statuses gps SELECT *--
748 --* : fv_be_trx_dtls SELECT *--
749 --* : gl_balances glbal SELECT *--
750 --* : gl_code_combinations gcc SELECT *--
751 --* : fv_factsii_accounts_gt SELECT *--
752 --* Logic : 1.
753 --****************************************************************************************--
754 PROCEDURE start_processing
755 (
756 p_ledger_id IN gl_ledgers_public_v.ledger_id%TYPE,
757 p_acct_segment IN fnd_id_flex_segments.application_column_name%TYPE,
758 p_bal_segment IN fnd_id_flex_segments.application_column_name%TYPE,
759 p_fyr_segment IN fnd_id_flex_segments.application_column_name%TYPE,
760 p_fiscal_year IN gl_period_statuses.period_year%TYPE,
761 p_last_period_num IN NUMBER,
762 p_pub_law_code_col IN fv_system_parameters.factsii_pub_law_code_attribute%TYPE,
763 p_advance_type_col IN fv_system_parameters.factsii_advance_type_attribute%TYPE,
764 p_tr_main_acct_col IN fv_system_parameters.factsii_tr_main_acct_attribute%TYPE,
765 p_tr_dept_id_col IN fv_system_parameters.factsii_tr_dept_id_attribute%TYPE,
766 p_error_code OUT NOCOPY NUMBER,
767 p_error_desc OUT NOCOPY VARCHAR2
768 )
769 IS
770 l_module_name VARCHAR2(200);
771 l_location VARCHAR2(200);
772 TYPE balances_cur_type IS REF CURSOR;
773 balances_cur balances_cur_type;
774 l_code_combination_id gl_balances.code_combination_id%TYPE;
775 l_ending_balance_dr NUMBER;
776 l_ending_balance_cr NUMBER;
777 l_period_num gl_balances.period_num%TYPE;
778 l_account VARCHAR2(30);
779 l_fund VARCHAR2(30);
780 l_fyr VARCHAR2(30);
781 l_ussgl_account VARCHAR2(30);
782 l_gl_cursor_str VARCHAR2(10240);
783 l_attribute_columns VARCHAR2(1024);
784 l_attr_group_columns VARCHAR2(1024);
785 l_where_columns VARCHAR2(1024);
786 l_system_date DATE := SYSDATE;
787 l_public_law_code VARCHAR2(1);
788 l_advance_flag VARCHAR2(1);
789 l_transfer_flag VARCHAR2(1);
790 l_cohort_segment fnd_id_flex_segments.application_column_name%TYPE;
791 l_cohort VARCHAR2(30);
792 TYPE l_segment_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
793 l_segment l_segment_type;
794 l_treasury_symbol_id NUMBER;
795 l_gl_cursor_id INTEGER;
796 l_gl_cursor_ret INTEGER;
797 l_count_ccid NUMBER := 0;
798 l_insert_required BOOLEAN := FALSE;
799
800
801
802 BEGIN
803 l_module_name := g_module_name || 'start_processing';
804 p_error_code := g_SUCCESS;
805
806 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
807 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_enter);
808 END IF;
809
810 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
811 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_ledger_id = '||p_ledger_id);
812 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_acct_segment = '||p_acct_segment);
813 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_bal_segment = '||p_bal_segment);
814 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_fyr_segment = '||p_fyr_segment);
815 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_fiscal_year = '||p_fiscal_year);
816 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_last_period_num = '||p_last_period_num);
817 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_pub_law_code_col = '||p_pub_law_code_col);
818 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_advance_type_col = '||p_advance_type_col);
819 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_tr_main_acct_col = '||p_tr_main_acct_col);
820 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_tr_dept_id_col = '||p_tr_dept_id_col);
821 END IF;
822
823
824 l_attribute_columns := NULL;
825 l_attr_group_columns := NULL;
826 l_where_columns := NULL;
827
828
829 IF (p_pub_law_code_col IS NOT NULL) THEN
830 l_attribute_columns := l_attribute_columns||'DECODE(:b_public_law_code, ''Y'', NVL(be.public_law_code, gll.'||p_pub_law_code_col||'), NULL), ';
831 l_attr_group_columns := l_attr_group_columns||' NVL(be.public_law_code, gll.'||p_pub_law_code_col||') ';
832 l_where_columns := l_where_columns||'AND ((DECODE(:b_public_law_code, ''Y'', NVL(be.public_law_code, gll.'||p_pub_law_code_col||'), NULL) IS NOT NULL) OR ';
833 ELSE
834 l_attribute_columns := l_attribute_columns||'DECODE(:b_public_law_code, ''Y'', be.public_law_code, NULL), ';
835 l_attr_group_columns := l_attr_group_columns||' be.public_law_code ';
836 l_where_columns := l_where_columns||'AND ((DECODE(:b_public_law_code, ''Y'', be.public_law_code, NULL) IS NOT NULL) OR ';
837 END IF;
838
839 IF (p_advance_type_col IS NOT NULL) THEN
840 l_attribute_columns := l_attribute_columns||'DECODE(:b_advance_flag, ''Y'', NVL(be.advance_type, gll.'||p_advance_type_col||'), NULL), ';
841 l_attr_group_columns := l_attr_group_columns||',NVL(be.advance_type, gll.'||p_advance_type_col||') ';
842 l_where_columns := l_where_columns||'(DECODE(:b_advance_flag, ''Y'', NVL(be.advance_type, gll.'||p_advance_type_col||'), NULL) IS NOT NULL) OR ';
843 ELSE
844 l_attribute_columns := l_attribute_columns||'DECODE(:b_advance_flag, ''Y'', be.advance_type, NULL), ';
845 l_attr_group_columns := l_attr_group_columns||',be.advance_type ';
846 l_where_columns := l_where_columns||'(DECODE(:b_advance_flag, ''Y'', be.advance_type, NULL) IS NOT NULL) OR ';
847 END IF;
848
849 IF (p_tr_main_acct_col IS NOT NULL) THEN
850 l_attribute_columns := l_attribute_columns||'DECODE(:b_transfer_flag, ''Y'', NVL(be.main_account, gll.'||p_tr_main_acct_col||'), NULL), ';
851 l_attr_group_columns := l_attr_group_columns||',NVL(be.main_account, gll.'||p_tr_main_acct_col||') ';
852 l_where_columns := l_where_columns||'(DECODE(:b_transfer_flag, ''Y'', NVL(be.main_account, gll.'||p_tr_main_acct_col||'), NULL) IS NOT NULL) OR ';
853 ELSE
854 l_attribute_columns := l_attribute_columns||'DECODE(:b_transfer_flag, ''Y'', be.main_account, NULL), ';
855 l_attr_group_columns := l_attr_group_columns||',be.main_account ';
856 l_where_columns := l_where_columns||'(DECODE(:b_transfer_flag, ''Y'', be.main_account, NULL) IS NOT NULL) OR ';
857 END IF;
858
859 IF (p_tr_dept_id_col IS NOT NULL) THEN
860 l_attribute_columns := l_attribute_columns||'DECODE(:b_transfer_flag, ''Y'', NVL(be.dept_id, gll.'||p_tr_dept_id_col||'), NULL), ';
861 l_attr_group_columns := l_attr_group_columns||',NVL(be.dept_id, gll.'||p_tr_dept_id_col||') ';
862 l_where_columns := l_where_columns||'(DECODE(:b_transfer_flag, ''Y'', NVL(be.dept_id, gll.'||p_tr_dept_id_col||'), NULL) IS NOT NULL)) ';
863 ELSE
864 l_attribute_columns := l_attribute_columns||'DECODE(:b_transfer_flag, ''Y'', be.dept_id, NULL), ';
865 l_attr_group_columns := l_attr_group_columns||',be.dept_id ';
866 l_where_columns := l_where_columns||'(DECODE(:b_transfer_flag, ''Y'', be.dept_id, NULL) IS NOT NULL)) ';
867 END IF;
868
869 l_gl_cursor_str := 'INSERT INTO fv_factsii_ending_balances
870 (
871 set_of_books_id,
872 fiscal_year,
873 ccid,
874 account,
875 ussgl_account,
876 fund,
877 fyr,
878 cohort,
879 ending_balance_cr,
880 ending_balance_dr,
881 public_law,
882 advance_flag,
883 transfer_main_acct,
884 transfer_dept_id,
885 record_category,
886 creation_date,
887 created_by,
888 last_update_date,
889 last_update_by,
890 request_id,
891 treasury_symbol_id
892 )
893 SELECT :b_set_of_books_id,
894 :b_period_year,
895 :b_code_combination_id,
896 :b_account,
897 :b_ussgl_account,
898 :b_fund,
899 :b_fyr,
900 :b_cohort,
901 SUM(NVL(gll.entered_cr, 0)),
902 SUM(NVL(gll.entered_dr, 0)),'||
903 l_attribute_columns||
904 '''E'',
905 :b_curr_date,
906 :b_user_id,
907 :b_curr_date,
908 :b_user_id,
909 :b_request_id,
910 :b_treasury_symbol_id
911 FROM gl_je_lines gll,
912 gl_je_headers gjh,
913 gl_period_statuses gps,
914 fv_be_trx_dtls be
915 WHERE gjh.ledger_id = gps.ledger_id
916 AND gll.code_combination_id = :b_code_combination_id
917 AND gjh.status=''P''
918 AND gll.ledger_id = gjh.ledger_id
919 AND gll.je_header_id = gjh.je_header_id
920 AND NVL(gjh.je_from_sla_flag, ''N'') IN (''N'',''U'')
921 AND gjh.actual_flag = ''A''
922 AND gps.application_id = 101
923 AND gps.ledger_id = :b_set_of_books_id
924 AND gjh.period_name = gps.period_name '||
925 l_where_columns ||'
926 AND gps.period_year = :b_period_year
927 AND gll.reference_1 = be.transaction_id (+)
928 AND be.set_of_books_id (+) = :b_set_of_books_id
929 GROUP BY '|| l_attr_group_columns||
930 ' HAVING SUM(NVL(gll.entered_dr, 0))-SUM(NVL(gll.entered_cr, 0)) <> 0
931 UNION
932 SELECT :b_set_of_books_id,
933 :b_period_year,
934 :b_code_combination_id,
935 :b_account,
936 :b_ussgl_account,
937 :b_fund,
938 :b_fyr,
939 :b_cohort,
940 SUM(NVL(gll.entered_cr, 0)),
941 SUM(NVL(gll.entered_dr, 0)),'||
942 l_attribute_columns||
943 '''E'',
944 :b_curr_date,
945 :b_user_id,
946 :b_curr_date,
947 :b_user_id,
948 :b_request_id,
949 :b_treasury_symbol_id
950 FROM gl_je_lines gll,
951 gl_je_headers gjh,
952 xla_ae_lines xl ,
953 xla_distribution_links xdl,
954 gl_period_statuses gps,
955 fv_be_trx_dtls be,
956 gl_import_references glir
957 WHERE xl.code_combination_id = :b_code_combination_id
958 AND xl.ae_header_id = xdl.ae_header_id
959 AND xl.ae_line_num = xdl.ae_line_num
960 AND NVL(gjh.je_from_sla_flag, ''N'') = ''Y''
961 AND gjh.ledger_id = gps.ledger_id
962 AND gjh.status=''P''
963 AND gll.ledger_id = gjh.ledger_id
964 AND gll.je_header_id = gjh.je_header_id
965 AND gjh.actual_flag = ''A''
966 AND gps.application_id = 101
967 AND gps.ledger_id = :b_set_of_books_id
968 AND glir.je_header_id = gjh.je_header_id
969 AND glir.je_line_num = gll.je_line_num
970 AND glir.je_batch_id = gjh.je_batch_id
971 AND glir.gl_sl_link_id = xl.gl_sl_link_id
972 AND glir.gl_sl_link_table = xl.gl_sl_link_table
973 AND gjh.period_name = gps.period_name '||
974 l_where_columns ||'
975 AND gps.period_year = :b_period_year
976 AND nvl(xdl.SOURCE_DISTRIBUTION_ID_NUM_1,-99) = be.transaction_id (+)
977 AND be.set_of_books_id (+) = :b_set_of_books_id
978 GROUP BY '|| l_attr_group_columns||
979 ' HAVING SUM(NVL(gll.entered_dr, 0))-SUM(NVL(gll.entered_cr, 0)) <> 0';
980
981 BEGIN
982 OPEN balances_cur
983 FOR 'SELECT glbal.code_combination_id,
984 NVL(glbal.begin_balance_dr,0)+NVL(glbal.period_net_dr,0),
985 NVL(glbal.begin_balance_cr,0)+NVL(glbal.period_net_cr,0),
986 glbal.period_num,
987 fa.public_law_code,
988 fa.advance_flag,
989 fa.transfer_flag,
990 fa.account,
991 fa.ussgl_account,
992 gcc.segment1,
993 gcc.segment2,
994 gcc.segment3,
995 gcc.segment4,
996 gcc.segment5,
997 gcc.segment6,
998 gcc.segment7,
999 gcc.segment8,
1000 gcc.segment9,
1001 gcc.segment10,
1002 gcc.segment11,
1003 gcc.segment12,
1004 gcc.segment13,
1005 gcc.segment14,
1006 gcc.segment15,
1007 gcc.segment16,
1008 gcc.segment17,
1009 gcc.segment18,
1010 gcc.segment19,
1011 gcc.segment20,
1012 gcc.segment21,
1013 gcc.segment22,
1014 gcc.segment23,
1015 gcc.segment24,
1016 gcc.segment25,
1017 gcc.segment26,
1018 gcc.segment27,
1019 gcc.segment28,
1020 gcc.segment29,
1021 gcc.segment30,'||
1022 'gcc.'||p_bal_segment||'
1023 FROM gl_balances glbal,
1024 gl_code_combinations gcc,
1025 fv_factsii_accounts_gt fa
1026 WHERE glbal.ledger_id = :b_set_of_books_id
1027 AND glbal.period_year = :b_period_year
1028 AND glbal.period_num = :b_period_num
1029 AND glbal.template_id IS NULL
1030 AND glbal.actual_flag = ''A''
1031 AND glbal.currency_code = :b_currency_code
1032 AND gcc.code_combination_id = glbal.code_combination_id
1033 AND gcc.'||p_acct_segment||' = fa.account
1034 AND ((NVL(glbal.begin_balance_dr,0)+NVL(glbal.period_net_dr,0))-
1035 (NVL(glbal.begin_balance_cr,0)+NVL(glbal.period_net_cr,0))) <> 0
1036 order by gcc.'||p_bal_segment
1037 USING p_ledger_id,
1038 p_fiscal_year,
1039 p_last_period_num,
1040 'USD';
1041
1042 EXCEPTION
1043 WHEN OTHERS THEN
1044 p_error_code := g_FAILURE;
1045 p_error_desc := SQLERRM;
1046 l_location := l_module_name||'open_balances_cur';
1047 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1048 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1049 END;
1050
1051 IF (p_error_code = g_SUCCESS) THEN
1052 LOOP
1053 BEGIN
1054 FETCH balances_cur
1055 INTO l_code_combination_id,
1056 l_ending_balance_dr,
1057 l_ending_balance_cr,
1058 l_period_num,
1059 l_public_law_code,
1060 l_advance_flag,
1061 l_transfer_flag,
1062 l_account,
1063 l_ussgl_account,
1064 l_segment(1),
1065 l_segment(2),
1066 l_segment(3),
1067 l_segment(4),
1068 l_segment(5),
1069 l_segment(6),
1070 l_segment(7),
1071 l_segment(8),
1072 l_segment(9),
1073 l_segment(10),
1074 l_segment(11),
1075 l_segment(12),
1076 l_segment(13),
1077 l_segment(14),
1078 l_segment(15),
1079 l_segment(16),
1080 l_segment(17),
1081 l_segment(18),
1082 l_segment(19),
1083 l_segment(20),
1084 l_segment(21),
1085 l_segment(22),
1086 l_segment(23),
1087 l_segment(24),
1088 l_segment(25),
1089 l_segment(26),
1090 l_segment(27),
1091 l_segment(28),
1092 l_segment(29),
1093 l_segment(30),
1094 l_fund;
1095 EXCEPTION
1096 WHEN OTHERS THEN
1097 p_error_code := g_FAILURE;
1098 p_error_desc := SQLERRM;
1099 l_location := l_module_name||'open_balances_cur';
1100 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1101 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1102 END;
1103
1104 IF (p_error_code = g_SUCCESS) THEN
1105 IF balances_cur%NOTFOUND THEN
1106 EXIT;
1107 END IF;
1108 END IF;
1109
1110 IF (p_error_code = g_SUCCESS) THEN
1111 get_treasury_info
1112 (
1113 p_ledger_id => p_ledger_id,
1114 p_fund_value => l_fund,
1115 p_treasury_symbol_id => l_treasury_symbol_id,
1116 p_cohort_segment => l_cohort_segment,
1117 p_error_code => p_error_code,
1118 p_error_desc => p_error_desc
1119 );
1120 END IF;
1121
1122 IF (p_error_code = g_SUCCESS) THEN
1123 l_fyr := l_segment(SUBSTR(p_fyr_segment, 8));
1124 IF (l_cohort_segment IS NOT NULL) THEN
1125 l_cohort := l_segment(SUBSTR(l_cohort_segment, 8));
1126 ELSE
1127 l_cohort := NULL;
1128 END IF;
1129 END IF;
1130
1131 IF (p_error_code = g_SUCCESS) THEN
1132 BEGIN
1133 l_gl_cursor_id := dbms_sql.open_cursor;
1134 EXCEPTION
1135 WHEN OTHERS THEN
1136 p_error_code := g_FAILURE;
1137 p_error_desc := SQLERRM;
1138 l_location := l_module_name||'open_gl_cur';
1139 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1140 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1141 END;
1142 END IF;
1143
1144 IF (p_error_code = g_SUCCESS) THEN
1145 BEGIN
1146 dbms_sql.parse(l_gl_cursor_id, l_gl_cursor_str, dbms_sql.v7);
1147 EXCEPTION
1148 WHEN OTHERS THEN
1149 p_error_code := g_FAILURE;
1150 p_error_desc := SQLERRM;
1151 l_location := l_module_name||'parse_gl_cur';
1152 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1153 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1154 END;
1155 END IF;
1156
1157 IF (p_error_code = g_SUCCESS) THEN
1158 BEGIN
1159 dbms_sql.bind_variable(l_gl_cursor_id,':b_period_year', p_fiscal_year);
1160 dbms_sql.bind_variable(l_gl_cursor_id,':b_code_combination_id', l_code_combination_id);
1161 dbms_sql.bind_variable(l_gl_cursor_id,':b_account', l_account);
1162 dbms_sql.bind_variable(l_gl_cursor_id,':b_ussgl_account', l_ussgl_account);
1163 dbms_sql.bind_variable(l_gl_cursor_id,':b_fund', l_fund);
1164 dbms_sql.bind_variable(l_gl_cursor_id,':b_fyr', l_fyr);
1165 dbms_sql.bind_variable(l_gl_cursor_id,':b_cohort', l_cohort);
1166 dbms_sql.bind_variable(l_gl_cursor_id,':b_public_law_code', l_public_law_code);
1167 dbms_sql.bind_variable(l_gl_cursor_id,':b_advance_flag', l_advance_flag);
1168 dbms_sql.bind_variable(l_gl_cursor_id,':b_transfer_flag', l_transfer_flag);
1169 dbms_sql.bind_variable(l_gl_cursor_id,':b_curr_date', l_system_date);
1170 dbms_sql.bind_variable(l_gl_cursor_id,':b_user_id', g_user_id);
1171 dbms_sql.bind_variable(l_gl_cursor_id,':b_request_id', g_request_id);
1172 dbms_sql.bind_variable(l_gl_cursor_id,':b_treasury_symbol_id', l_treasury_symbol_id);
1173 dbms_sql.bind_variable(l_gl_cursor_id,':b_set_of_books_id', p_ledger_id);
1174 EXCEPTION
1175 WHEN OTHERS THEN
1176 p_error_code := g_FAILURE;
1177 p_error_desc := SQLERRM;
1178 l_location := l_module_name||'bind_gl_cur';
1179 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1180 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1181 END;
1182 END IF;
1183
1184 IF (p_error_code = g_SUCCESS) THEN
1185 BEGIN
1186 l_gl_cursor_ret := dbms_sql.execute(l_gl_cursor_id);
1187 EXCEPTION
1188 WHEN OTHERS THEN
1189 p_error_code := g_FAILURE;
1190 p_error_desc := SQLERRM;
1191 l_location := l_module_name||'execute_gl_cur';
1192 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1193 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1194 END;
1195 END IF;
1196
1197 IF (p_error_code = g_SUCCESS) THEN
1198 BEGIN
1199 dbms_sql.close_cursor(l_gl_cursor_id);
1200 EXCEPTION
1201 WHEN OTHERS THEN
1202 p_error_code := g_FAILURE;
1203 p_error_desc := SQLERRM;
1204 l_location := l_module_name||'close_gl_cur';
1205 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1206 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1207 END;
1208 END IF;
1209 IF (p_error_code = g_SUCCESS) THEN
1210 FOR previous_year_rec IN (SELECT *
1211 FROM fv_factsii_ending_balances ffeb
1212 WHERE ffeb.set_of_books_id = p_ledger_id
1213 AND ffeb.fiscal_year = p_fiscal_year-1
1214 AND ffeb.ccid = l_code_combination_id) LOOP
1215 BEGIN
1216 UPDATE fv_factsii_ending_balances ffeb
1217 SET ffeb.ending_balance_cr = NVL(ffeb.ending_balance_cr, 0) + NVL(previous_year_rec.ending_balance_cr, 0),
1218 ffeb.ending_balance_dr = NVL(ffeb.ending_balance_dr, 0) + NVL(previous_year_rec.ending_balance_dr, 0)
1219 WHERE ffeb.set_of_books_id = previous_year_rec.set_of_books_id
1220 AND ffeb.fiscal_year = p_fiscal_year
1221 AND ffeb.ccid = previous_year_rec.ccid
1222 AND ffeb.public_law = previous_year_rec.public_law
1223 AND ffeb.advance_flag = previous_year_rec.advance_flag
1224 AND ffeb.transfer_dept_id = previous_year_rec.transfer_dept_id
1225 AND ffeb.transfer_main_acct = previous_year_rec.transfer_main_acct;
1226 l_insert_required := FALSE;
1227 IF (SQL%ROWCOUNT = 0) THEN
1228 l_insert_required := TRUE;
1229 END IF;
1230 EXCEPTION
1231 WHEN OTHERS THEN
1232 p_error_code := g_FAILURE;
1233 p_error_desc := SQLERRM;
1234 l_location := l_module_name||'update_fv_factsii_ending_balances';
1235 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1236 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1237 END;
1238
1239 IF (p_error_code = g_SUCCESS) THEN
1240 IF (l_insert_required) THEN
1241 BEGIN
1242 INSERT INTO fv_factsii_ending_balances
1243 (
1244 set_of_books_id,
1245 fiscal_year,
1246 ccid,
1247 account,
1248 fund,
1249 fyr,
1250 cohort,
1251 ussgl_account,
1252 ending_balance_cr,
1253 ending_balance_dr,
1254 public_law,
1255 advance_flag,
1256 transfer_dept_id,
1257 transfer_main_acct,
1258 record_category,
1259 creation_date,
1260 created_by,
1261 last_update_date,
1262 last_update_by,
1263 request_id,
1264 treasury_symbol_id
1265 )
1266 VALUES
1267 (
1268 previous_year_rec.set_of_books_id,
1269 p_fiscal_year,
1270 previous_year_rec.ccid,
1271 previous_year_rec.account,
1272 previous_year_rec.fund,
1273 previous_year_rec.fyr,
1274 previous_year_rec.cohort,
1275 previous_year_rec.ussgl_account,
1276 previous_year_rec.ending_balance_cr,
1277 previous_year_rec.ending_balance_dr,
1278 previous_year_rec.public_law,
1279 previous_year_rec.advance_flag,
1280 previous_year_rec.transfer_dept_id,
1281 previous_year_rec.transfer_main_acct,
1282 'E',
1283 l_system_date,
1284 g_user_id,
1285 l_system_date,
1286 g_user_id,
1287 g_request_id,
1288 previous_year_rec.treasury_symbol_id
1289 );
1290 EXCEPTION
1291 WHEN OTHERS THEN
1292 p_error_code := g_FAILURE;
1293 p_error_desc := SQLERRM;
1294 l_location := l_module_name||'insert_fv_factsii_ending_balances1';
1295 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1296 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1297 END;
1298 END IF;
1299 END IF;
1300
1301 END LOOP;
1302 END IF;
1303
1304 IF (p_error_code = g_SUCCESS) THEN
1305 BEGIN
1306 l_count_ccid := 0;
1307 SELECT COUNT (*)
1308 INTO l_count_ccid
1309 FROM fv_factsii_ending_balances ffeb
1310 WHERE ffeb.set_of_books_id = p_ledger_id
1311 AND ffeb.fiscal_year = p_fiscal_year
1312 AND ccid = l_code_combination_id;
1313 EXCEPTION
1314 WHEN NO_DATA_FOUND THEN
1315 l_count_ccid := 0;
1316 WHEN OTHERS THEN
1317 p_error_code := g_FAILURE;
1318 p_error_desc := SQLERRM;
1319 l_location := l_module_name||'select_fv_factsii_ending_balances';
1320 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1321 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1322 END;
1323 END IF;
1324
1325 IF (p_error_code = g_SUCCESS) THEN
1326 IF (l_count_ccid <> 0) THEN
1327 BEGIN
1328 INSERT INTO fv_factsii_ending_balances
1329 (
1330 set_of_books_id,
1331 fiscal_year,
1332 ccid,
1333 account,
1334 fund,
1335 fyr,
1336 cohort,
1337 ussgl_account,
1338 ending_balance_cr,
1339 ending_balance_dr,
1340 public_law,
1341 advance_flag,
1342 transfer_dept_id,
1343 transfer_main_acct,
1344 record_category,
1345 creation_date,
1346 created_by,
1347 last_update_date,
1348 last_update_by,
1349 request_id,
1350 treasury_symbol_id
1351 )
1352 SELECT p_ledger_id,
1353 p_fiscal_year,
1354 ccid,
1355 l_account,
1356 l_fund,
1357 l_fyr,
1358 l_cohort,
1359 l_ussgl_account,
1360 l_ending_balance_cr-SUM(ending_balance_cr),
1361 l_ending_balance_dr-SUM(ending_balance_dr),
1362 DECODE (l_public_law_code, 'Y', ' ', NULL),
1363 DECODE (l_advance_flag, 'Y', 'X', NULL),
1364 DECODE (l_transfer_flag, 'Y',' ',NULL),
1365 DECODE (l_transfer_flag, 'Y',' ',NULL),
1366 'D',
1367 l_system_date,
1368 g_user_id,
1369 l_system_date,
1370 g_user_id,
1371 g_request_id,
1372 l_treasury_symbol_id
1373 FROM fv_factsii_ending_balances ffeb
1374 WHERE ffeb.set_of_books_id = p_ledger_id
1375 AND ffeb.fiscal_year = p_fiscal_year
1376 AND ccid = l_code_combination_id
1377 GROUP BY ccid
1378 HAVING (((l_ending_balance_cr-SUM(ending_balance_cr)) <> 0) OR
1379 ((l_ending_balance_dr-SUM(ending_balance_dr)) <> 0)) AND
1380 (l_ending_balance_cr-SUM(ending_balance_cr)) <> (l_ending_balance_dr-SUM(ending_balance_dr));
1381 EXCEPTION
1382 WHEN OTHERS THEN
1383 p_error_code := g_FAILURE;
1384 p_error_desc := SQLERRM;
1385 l_location := l_module_name||'INSERT INTO fv_factsii_ending_balances';
1386 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1387 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1388 END;
1389 END IF;
1390 END IF;
1391
1392 IF (p_error_code <> g_SUCCESS) THEN
1393 EXIT;
1394 END IF;
1395
1396 END LOOP;
1397 END IF;
1398
1399 IF balances_cur%ISOPEN THEN
1400 CLOSE balances_cur;
1401 END IF;
1402
1403 IF (p_error_code = g_SUCCESS) THEN
1404 BEGIN
1405 DELETE fv_factsii_ending_balances ffeb1
1406 WHERE EXISTS (SELECT ffeb2.set_of_books_id,
1407 ffeb2.ccid,
1408 ffeb2.fiscal_year,
1409 count(*)
1410 FROM fv_factsii_ending_balances ffeb2
1411 WHERE ffeb2.set_of_books_id = ffeb1.set_of_books_id
1412 AND ffeb2.ccid = ffeb1.ccid
1413 AND ffeb2.fiscal_year = ffeb1.fiscal_year
1414 GROUP BY ffeb2.set_of_books_id,
1415 ffeb2.ccid,
1416 ffeb2.fiscal_year
1417 HAVING count(*) = 1)
1418 AND RTRIM(ffeb1.public_law) IS NULL
1419 AND RTRIM(ffeb1.advance_flag) IS NULL
1420 AND RTRIM(ffeb1.transfer_dept_id) IS NULL
1421 AND RTRIM(ffeb1.transfer_main_acct) IS NULL;
1422 EXCEPTION
1423 WHEN OTHERS THEN
1424 p_error_code := g_FAILURE;
1425 p_error_desc := SQLERRM;
1426 l_location := l_module_name||'DELETE FROM fv_factsii_ending_balances1';
1427 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1428 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1429 END;
1430 END IF;
1431
1432
1433 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1434 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
1435 END IF;
1436
1437 EXCEPTION
1438 WHEN OTHERS THEN
1439 p_error_code := g_FAILURE;
1440 p_error_desc := SQLERRM;
1441 l_location := l_module_name||'.final_exception';
1442 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1443 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1444 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1445 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
1446 END IF;
1447 END;
1448
1449
1450 --****************************************************************************************--
1451 --* Name : derive_balances *--
1452 --* Type : Procedure *--
1453 --* Purpose : Main Entry point for the concurent program FVFCT2BB *--
1454 --* : (FACTS II Ending Balance Detail) *--
1455 --* : set of books id and fiscal year. *--
1456 --* Parameters : p_ledger_id IN Set Of Books Id *--
1457 --* : p_fiscal_year IN Fiscal Year *--
1458 --* : p_error_code OUT Return Error Code *--
1459 --* : p_error_desc OUT Return Error Descion *--
1460 --* Global Vars : fnd_log.g_current_runtime_level *--
1461 --* : fnd_log.level_procedure *--
1462 --* : fnd_log.level_statement *--
1463 --* : fnd_log.level_unexpected *--
1464 --* : g_module_name *--
1465 --* : g_SUCCESS *--
1466 --* Called from : derive_balances *--
1467 --* Calls : fv_utility.debug_mesg *--
1468 --* : fv_utility.log_mesg *--
1469 --* : initialize_program_variables *--
1470 --* : purge_balances *--
1471 --* : explode_accounts *--
1472 --* : start_processing *--
1473 --* Tables Used : None *--
1474 --* Logic : 1.
1475 --****************************************************************************************--
1476 PROCEDURE derive_balances
1477 (
1478 p_errbuff OUT NOCOPY VARCHAR2,
1479 p_retcode OUT NOCOPY NUMBER,
1480 p_ledger_id IN NUMBER,
1481 p_fiscal_year IN NUMBER
1482 )
1483 IS
1484 l_module_name VARCHAR2(200);
1485 l_location VARCHAR2(200);
1486 l_acct_segment fnd_id_flex_segments.application_column_name%TYPE;
1487 l_bal_segment fnd_id_flex_segments.application_column_name%TYPE;
1488 l_fyr_segment fnd_id_flex_segments.application_column_name%TYPE;
1489 l_chart_of_accounts_id gl_ledgers_public_v.chart_of_accounts_id%TYPE;
1490 l_acct_value_set_id fnd_id_flex_segments.flex_value_set_id%TYPE;
1491 l_last_period_num gl_balances.period_num%TYPE;
1492 l_pub_law_code_col fv_system_parameters.factsii_pub_law_code_attribute%TYPE;
1493 l_advance_type_col fv_system_parameters.factsii_advance_type_attribute%TYPE;
1494 l_tr_main_acct_col fv_system_parameters.factsii_tr_main_acct_attribute%TYPE;
1495 l_tr_dept_id_col fv_system_parameters.factsii_tr_dept_id_attribute%TYPE;
1496 BEGIN
1497 l_module_name := g_module_name || 'derive_balances';
1498 p_retcode := g_SUCCESS;
1499
1500 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1501 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_enter);
1502 END IF;
1503
1504 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1505 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_ledger_id = '||p_ledger_id);
1506 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_fiscal_year = '||p_fiscal_year);
1507 END IF;
1508
1509 IF (p_retcode = g_SUCCESS) THEN
1510 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1511 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling initialize_program_variables');
1512 END IF;
1513 initialize_program_variables
1514 (
1515 p_ledger_id => p_ledger_id,
1516 p_fiscal_year => p_fiscal_year,
1517 p_last_period_num => l_last_period_num,
1518 p_chart_of_accounts_id => l_chart_of_accounts_id,
1519 p_acct_segment => l_acct_segment,
1520 p_acct_value_set_id => l_acct_value_set_id,
1521 p_bal_segment => l_bal_segment,
1522 p_fyr_segment => l_fyr_segment,
1523 p_pub_law_code_col => l_pub_law_code_col,
1524 p_advance_type_col => l_advance_type_col,
1525 p_tr_main_acct_col => l_tr_main_acct_col,
1526 p_tr_dept_id_col => l_tr_dept_id_col,
1527 p_error_code => p_retcode,
1528 p_error_desc => p_errbuff
1529 );
1530 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1531 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'initialize_program_variables returned '||p_retcode);
1532 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_chart_of_accounts_id='||l_chart_of_accounts_id);
1533 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_acct_segment='||l_acct_segment);
1534 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_acct_value_set_id='||l_acct_value_set_id);
1535 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_bal_segment='||l_bal_segment);
1536 END IF;
1537 END IF;
1538
1539 IF (p_retcode = g_SUCCESS) THEN
1540 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1541 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling purge_balances');
1542 END IF;
1543 purge_balances
1544 (
1545 p_ledger_id => p_ledger_id,
1546 p_fiscal_year => p_fiscal_year,
1547 p_error_code => p_retcode,
1548 p_error_desc => p_errbuff
1549 );
1550 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1551 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'purge_balances returned '||p_retcode);
1552 END IF;
1553 END IF;
1554
1555 IF (p_retcode = g_SUCCESS) THEN
1556 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1557 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling purge_balances');
1558 END IF;
1559 explode_accounts
1560 (
1561 p_ledger_id => p_ledger_id,
1562 p_acct_value_set_id => l_acct_value_set_id,
1563 p_error_code => p_retcode,
1564 p_error_desc => p_errbuff
1565 );
1566 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1567 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'purge_balances returned '||p_retcode);
1568 END IF;
1569 END IF;
1570
1571 IF (p_retcode = g_SUCCESS) THEN
1572 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1573 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling start_processing');
1574 END IF;
1575 start_processing
1576 (
1577 p_ledger_id => p_ledger_id,
1578 p_acct_segment => l_acct_segment,
1579 p_bal_segment => l_bal_segment,
1580 p_fyr_segment => l_fyr_segment,
1581 p_fiscal_year => p_fiscal_year,
1582 p_last_period_num => l_last_period_num,
1583 p_pub_law_code_col => l_pub_law_code_col,
1584 p_advance_type_col => l_advance_type_col,
1585 p_tr_main_acct_col => l_tr_main_acct_col,
1586 p_tr_dept_id_col => l_tr_dept_id_col,
1587 p_error_code => p_retcode,
1588 p_error_desc => p_errbuff
1589 );
1590 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1591 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'start_processing returned '||p_retcode);
1592 END IF;
1593 END IF;
1594
1595 IF (p_retcode = g_SUCCESS OR p_retcode = g_WARNING) THEN
1596 COMMIT;
1597 ELSE
1598 ROLLBACK;
1599 END IF;
1600
1601 generate_output_report
1602 (
1603 p_ledger_id => p_ledger_id,
1604 p_fiscal_year => p_fiscal_year,
1605 p_error_code => p_retcode,
1606 p_error_desc => p_errbuff
1607 );
1608
1609 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1610 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_retcode||')');
1611 END IF;
1612
1613 EXCEPTION
1614 WHEN OTHERS THEN
1615 p_retcode := g_FAILURE;
1616 p_errbuff := SQLERRM;
1617 l_location := l_module_name||'.final_exception';
1618 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1619 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_errbuff) ;
1620 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1621 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_retcode||')');
1622 END IF;
1623 ROLLBACK;
1624 END;
1625 BEGIN
1626 initialize_global_variables;
1627 END fv_facts2_derive_balances;