[Home] [Help]
PACKAGE BODY: APPS.FV_SLA_UTL_PROCESSING_PKG
Source
1 PACKAGE BODY fv_sla_utl_processing_pkg AS
2 --$Header: FVXLAUTB.pls 120.14.12020000.8 2013/03/26 17:05:45 sasukuma ship $
3
4 ---------------------------------------------------------------------------
5 ---------------------------------------------------------------------------
6
7 c_FAILURE CONSTANT NUMBER := -1;
8 c_SUCCESS CONSTANT NUMBER := 0;
9 C_GL_APPLICATION CONSTANT NUMBER := 101;
10 C_GL_APPL_SHORT_NAME CONSTANT VARCHAR2(30) := 'SQLGL';
11 C_GL_FLEX_CODE CONSTANT VARCHAR2(10) := 'GL#';
12 CRLF CONSTANT VARCHAR2(1) := FND_GLOBAL.newline;
13 g_path_name CONSTANT VARCHAR2(200) := 'fv.plsql.fvxlautb.fv_sla_utl_processing_pkg';
14 C_STATE_LEVEL CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
15 C_PROC_LEVEL CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
16 g_log_level CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
17
18 g_ledger_tab LedgerTabType;
19 g_org_tab OrgTabType;
20 g_bank_tab BankTabType;
21
22
23
24 PROCEDURE trace
25 (
26 p_level IN NUMBER,
27 p_procedure_name IN VARCHAR2,
28 p_debug_info IN VARCHAR2
29 )
30 IS
31 BEGIN
32 IF (p_level >= g_log_level ) THEN
33 FND_LOG.STRING(p_level, p_procedure_name, p_debug_info);
34 END IF;
35
36 END trace;
37
38 PROCEDURE insert_fv_xla_acc_errors
39 (
40 p_appli_s_name IN VARCHAR2,
41 p_msg_name IN VARCHAR2,
42 p_entity_id IN NUMBER,
43 p_event_id IN NUMBER,
44 p_ledger_id IN NUMBER
45 )
46 IS
47 l_procedure_name VARCHAR2(100):='.insert_fv_xla_acc_errors';
48 BEGIN
49 trace(C_STATE_LEVEL, l_procedure_name, ' Begin insert_fv_xla_acc_errors ');
50 trace(C_STATE_LEVEL, l_procedure_name, ' Before inserting into xla_accounting_errors');
51 xla_accounting_err_pkg.build_message
52 (
53 p_appli_s_name,
54 p_msg_name,
55 p_entity_id,
56 p_event_id,
57 p_ledger_id,
58 NULL,
59 NULL,
60 xla_accounting_pkg.g_parent_request_id
61 );
62 END insert_fv_xla_acc_errors;
63
64 PROCEDURE stack_error
65 (
66 p_program_name IN VARCHAR2,
67 p_location IN VARCHAR2,
68 p_error_message IN VARCHAR2
69 )
70 IS
71 l_procedure_name VARCHAR2(100) :='.stack_error';
72 BEGIN
73 trace(C_STATE_LEVEL, l_procedure_name, 'BEGIN');
74 trace(C_STATE_LEVEL, l_procedure_name, 'g_CurrentEventId='||g_CurrentEventId);
75 fnd_file.put_line(fnd_file.log, 'ERROR: '||p_error_message);
76 IF (g_CurrentEventId IS NOT NULL) THEN
77 Fnd_message.set_name('FND','FND_GENERIC_MESSAGE');
78 Fnd_Message.Set_Token('MESSAGE',p_error_message);
79 psa_bc_xla_pvt.psa_xla_error
80 (
81 p_message_code => 'FND_GENERIC_MESSAGE',
82 p_event_id => g_CurrentEventId
83 );
84 END IF;
85 END;
86
87 PROCEDURE init
88 IS
89 l_procedure_name VARCHAR2(100) :='.init';
90 BEGIN
91 trace(C_STATE_LEVEL, l_procedure_name, 'Package Information');
92 trace(C_STATE_LEVEL, l_procedure_name, '$Header: FVXLAUTB.pls 120.14.12020000.8 2013/03/26 17:05:45 sasukuma ship $');
93 END;
94
95 PROCEDURE init_extract_record
96 (
97 p_application_id IN NUMBER,
98 p_fv_extract_detail IN OUT NOCOPY fv_extract_detail_gt%ROWTYPE
99 )
100 IS
101 BEGIN
102 p_fv_extract_detail.application_id := p_application_id;
103 p_fv_extract_detail.ent_commitment_amount := 0;
104 p_fv_extract_detail.acc_commitment_amount := 0;
105 p_fv_extract_detail.ent_unpaid_obl_amount := 0;
106 p_fv_extract_detail.acc_unpaid_obl_amount := 0;
107 p_fv_extract_detail.ent_unpaid_obl_pya_amount := 0;
108 p_fv_extract_detail.acc_unpaid_obl_pya_amount := 0;
109 p_fv_extract_detail.ent_unpaid_obl_pya_off_amount := 0;
110 p_fv_extract_detail.acc_unpaid_obl_pya_off_amount := 0;
111 p_fv_extract_detail.ent_anticipated_budget_amount := 0;
112 p_fv_extract_detail.acc_anticipated_budget_amount := 0;
113 p_fv_extract_detail.ent_unanticipated_bud_amount := 0;
114 p_fv_extract_detail.acc_unanticipated_bud_amount := 0;
115 p_fv_extract_detail.ent_unreserved_budget_amount := 0;
116 p_fv_extract_detail.acc_unreserved_budget_amount := 0;
117 p_fv_extract_detail.ent_charge_amount := 0;
118 p_fv_extract_detail.acc_charge_amount := 0;
119 p_fv_extract_detail.ent_unpaid_exp_amount := 0;
120 p_fv_extract_detail.acc_unpaid_exp_amount := 0;
121 p_fv_extract_detail.ent_paid_exp_amount := 0;
122 p_fv_extract_detail.acc_paid_exp_amount := 0;
123 p_fv_extract_detail.acc_paid_exp_pya_off_amount := 0;
124 p_fv_extract_detail.ent_paid_exp_pya_off_amount := 0;
125 p_fv_extract_detail.ent_paid_obl_amount := 0;
126 p_fv_extract_detail.acc_paid_obl_amount := 0;
127 p_fv_extract_detail.ent_unpaid_exp_pya_amount := 0;
128 p_fv_extract_detail.acc_unpaid_exp_pya_amount := 0;
129 p_fv_extract_detail.ent_unpaid_exp_pya_off_amount := 0;
130 p_fv_extract_detail.acc_unpaid_exp_pya_off_amount := 0;
131 p_fv_extract_detail.exp_appropriations_amount := 0;
132 p_fv_extract_detail.ent_paid_exp_pya_amount := 0;
133 p_fv_extract_detail.acc_paid_exp_pya_amount := 0;
134 p_fv_extract_detail.acc_expended_approp_amount := 0;
135 p_fv_extract_detail.ent_expended_approp_amount := 0;
136 p_fv_extract_detail.ent_refund_amount := 0;
137 p_fv_extract_detail.acc_refund_amount := 0;
138 END;
139
140 FUNCTION get_bank_cash_account
141 (
142 p_bank_acct_use_id IN NUMBER
143 ) RETURN VARCHAR2
144 IS
145 l_debug_info VARCHAR2(240);
146 l_procedure_name VARCHAR2(100):='.get_bank_cash_account';
147 l_bank_rec BankRecType;
148 l_error_code NUMBER;
149 l_error_desc VARCHAR2(2000);
150 BEGIN
151 l_procedure_name := g_path_name || l_procedure_name;
152 l_error_code := c_SUCCESS;
153 trace(C_STATE_LEVEL, l_procedure_name, 'BEGIN');
154 trace(C_STATE_LEVEL, l_procedure_name, 'p_bank_acct_use_id = '||p_bank_acct_use_id);
155
156 get_bank_account_info
157 (
158 p_bank_acct_use_id => p_bank_acct_use_id,
159 p_bank_rec => l_bank_rec,
160 p_error_code => l_error_code,
161 p_error_desc => l_error_desc
162 );
163
164 trace(C_STATE_LEVEL, l_procedure_name, 'END');
165 RETURN (l_bank_rec.cash_bank_natural_account);
166 EXCEPTION
167 WHEN OTHERS THEN
168 l_error_code := c_FAILURE;
169 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
170 fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
171 l_error_desc := fnd_message.get;
172 stack_error (l_procedure_name, 'FINAL', l_error_desc);
173 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:FINAL:'||l_error_desc);
174 RETURN (l_bank_rec.cash_bank_natural_account);
175 END;
176
177 PROCEDURE get_bank_account_info
178 (
179 p_bank_acct_use_id IN NUMBER,
180 p_bank_rec OUT NOCOPY BankRecType,
181 p_error_code OUT NOCOPY NUMBER,
182 p_error_desc OUT NOCOPY VARCHAR2
183 )
184 IS
185 l_debug_info VARCHAR2(240);
186 l_procedure_name VARCHAR2(100):='.get_bank_account_info';
187 l_bank_rec BankRecType;
188 l_coaid gl_code_combinations.chart_of_accounts_id%TYPE;
189 l_accounting_seg_num fnd_id_flex_segments.segment_num%TYPE;
190 l_result BOOLEAN;
191 l_no_of_segments NUMBER;
192 l_segments fnd_flex_ext.SegmentArray;
193
194 BEGIN
195 l_procedure_name := g_path_name || l_procedure_name;
196 p_error_code := c_SUCCESS;
197 trace(C_STATE_LEVEL, l_procedure_name, 'BEGIN');
198 trace(C_STATE_LEVEL, l_procedure_name, 'p_bank_acct_use_id = '||p_bank_acct_use_id);
199
200 -- See if the Org information is in Cache
201 IF g_bank_tab.EXISTS(p_bank_acct_use_id) THEN
202 l_bank_rec := g_bank_tab(p_bank_acct_use_id);
203 ELSE
204 trace(C_STATE_LEVEL, l_procedure_name, 'Getting Org Information');
205 l_bank_rec.bank_acct_use_id := p_bank_acct_use_id;
206 l_accounting_seg_num := NULL;
207 l_coaid := NULL;
208 BEGIN
209 SELECT c.ap_asset_ccid, c.cash_clearing_ccid,
210 g.chart_of_accounts_id
211 INTO l_bank_rec.cash_bank_account_ccid,
212 l_bank_rec.cash_clearing_ccid,
213 l_coaid
214 FROM ce_gl_accounts_ccid c,
215 gl_code_combinations g
216 WHERE c.bank_acct_use_id = p_bank_acct_use_id
217 AND g.code_combination_id = c.ap_asset_ccid;
218
219 IF l_bank_rec.cash_clearing_ccid IS NULL THEN
220 fv_utility.log_mesg('Confirm cash account has not been setup !');
221 fv_utility.log_mesg('Please setup confirm cash natural account in the Bank Account');
222 fv_utility.log_mesg('Clearing Cash Account field and retry.');
223 p_error_code := c_FAILURE;
224 p_error_desc := 'Confirm Cash has not been setup in the Clearing Cash Account field.';
225 stack_error (l_procedure_name, 'SELECT_ce_gl_accounts_ccid', p_error_desc);
226 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:SELECT_ce_gl_accounts_ccid:'||p_error_desc);
227 RETURN;
228 END IF;
229 EXCEPTION
230 WHEN NO_DATA_FOUND THEN
231 l_bank_rec.cash_bank_account_ccid := NULL;
232 l_coaid := NULL;
233 WHEN OTHERS THEN
234 p_error_code := c_FAILURE;
235 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
236 fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
237 p_error_desc := fnd_message.get;
238 stack_error (l_procedure_name, 'SELECT_ce_gl_accounts_ccid', p_error_desc);
239 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:SELECT_ce_gl_accounts_ccid:'||p_error_desc);
240 END;
241
242 IF (p_error_code = c_SUCCESS AND l_coaid IS NOT NULL) THEN
243 -- Get the GL ACCOUNT segment num and name
244 BEGIN
245 SELECT b.segment_num
246 INTO l_accounting_seg_num
247 FROM fnd_segment_attribute_values a,
248 fnd_id_flex_segments b
249 WHERE a.application_id = b.application_id
250 AND a.id_flex_code = b.id_flex_code
251 AND a.id_flex_num = b.id_flex_num
252 AND a.application_column_name = b.application_column_name
253 AND a.segment_attribute_type = 'GL_ACCOUNT'
254 AND a.attribute_value = 'Y'
255 AND b.application_id = C_GL_APPLICATION
256 AND b.id_flex_code = C_GL_FLEX_CODE
257 AND b.id_flex_num = l_coaid;
258 trace(C_STATE_LEVEL, l_procedure_name, 'accounting_seg_num='||l_accounting_seg_num);
259 EXCEPTION
260 WHEN OTHERS THEN
261 p_error_code := c_FAILURE;
262 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
263 fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
264 p_error_desc := fnd_message.get;
265 stack_error (l_procedure_name, 'SELECT_FND_ID_FLEX_SEGMENTS (GL_ACCOUNT)', p_error_desc);
266 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:SELECT_FND_ID_FLEX_SEGMENTS (GL_ACCOUNT):'||p_error_desc);
267 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
268 fnd_message.set_token('MESSAGE' , 'Error in Getting Accounting Segment.'||CRLF||
269 'Accounting Segment is not Defined for the COA '||l_coaid);
270 p_error_desc := fnd_message.get;
271 stack_error (l_procedure_name, 'SELECT_FND_ID_FLEX_SEGMENTS (GL_ACCOUNT)', p_error_desc);
272 END;
273 END IF;
274
275 IF (p_error_code = c_SUCCESS AND l_bank_rec.cash_bank_account_ccid IS NOT NULL) THEN
276 l_result := fnd_flex_ext.get_segments
277 (
278 C_GL_APPL_SHORT_NAME,
279 C_GL_FLEX_CODE,
280 l_coaid,
281 l_bank_rec.cash_bank_account_ccid,
282 l_no_of_segments,
283 l_segments
284 );
285
286 trace(C_STATE_LEVEL, l_procedure_name, 'l_no_of_segments='||l_no_of_segments);
287
288 l_bank_rec.cash_bank_natural_account := l_segments(l_accounting_seg_num);
289
290 trace(C_STATE_LEVEL, l_procedure_name, 'cash_bank_natural_account='||l_bank_rec.cash_bank_natural_account);
291 END IF;
292
293 --Get the cash clearing account
294 IF (p_error_code = c_SUCCESS AND l_bank_rec.cash_clearing_ccid IS NOT NULL) THEN
295 l_segments.DELETE;
296
297 l_result := fnd_flex_ext.get_segments
298 (
299 C_GL_APPL_SHORT_NAME,
300 C_GL_FLEX_CODE,
301 l_coaid,
302 l_bank_rec.cash_clearing_ccid,
303 l_no_of_segments,
304 l_segments
305 );
306
307 trace(C_STATE_LEVEL, l_procedure_name, 'l_no_of_segments='||l_no_of_segments);
308
309 l_bank_rec.cash_clearing_natural_account := l_segments(l_accounting_seg_num);
310
311 trace(C_STATE_LEVEL, l_procedure_name, 'cash_clearing_natural_account='||l_bank_rec.cash_clearing_natural_account);
312 END IF;
313
314 IF (p_error_code = c_SUCCESS) THEN
315 g_bank_tab(p_bank_acct_use_id) := l_bank_rec;
316 END IF;
317 END IF;
318
319 IF (p_error_code = c_SUCCESS) THEN
320 p_bank_rec := l_bank_rec;
321 END IF;
322
323 trace(C_STATE_LEVEL, l_procedure_name, 'END');
324 EXCEPTION
325 WHEN OTHERS THEN
326 p_error_code := c_FAILURE;
327 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
328 fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
329 p_error_desc := fnd_message.get;
330 stack_error (l_procedure_name, 'FINAL', p_error_desc);
331 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:FINAL:'||p_error_desc);
332 END;
333
334 PROCEDURE get_org_info
335 (
336 p_org_id IN NUMBER,
337 p_org_rec OUT NOCOPY OrgRecType,
338 p_error_code OUT NOCOPY NUMBER,
339 p_error_desc OUT NOCOPY VARCHAR2
340 )
341 IS
342 l_debug_info VARCHAR2(240);
343 l_procedure_name VARCHAR2(100):='.get_org_info';
344 l_org_rec OrgRecType;
345 BEGIN
346 l_procedure_name := g_path_name || l_procedure_name;
347 p_error_code := c_SUCCESS;
348 trace(C_STATE_LEVEL, l_procedure_name, 'BEGIN');
349 trace(C_STATE_LEVEL, l_procedure_name, 'p_org_id = '||p_org_id);
350
351 -- See if the Org information is in Cache
352 IF g_org_tab.EXISTS(p_org_id) THEN
353 l_org_rec := g_org_tab(p_org_id);
354 ELSE
355 trace(C_STATE_LEVEL, l_procedure_name, 'Getting Org Information');
356 l_org_rec.org_id := p_org_id;
357 BEGIN
358 SELECT f.dit_flag,
359 f.dit_confirm_cash_account
360 INTO l_org_rec.dit_flag,
361 l_org_rec.cash_account
362 FROM fv_operating_units_all f
363 WHERE org_id = p_org_id;
364 EXCEPTION
365 WHEN NO_DATA_FOUND THEN
366 l_org_rec.dit_flag := NULL;
367 l_org_rec.cash_account := NULL;
368 WHEN OTHERS THEN
369 p_error_code := c_FAILURE;
370 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
371 fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
372 p_error_desc := fnd_message.get;
373 stack_error (l_procedure_name, 'SELECT_fv_operating_units_all', p_error_desc);
374 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:SELECT_fv_operating_units_all:'||p_error_desc);
375 END;
376 IF (p_error_code = c_SUCCESS) THEN
377 g_org_tab(p_org_id) := l_org_rec;
378 END IF;
379 END IF;
380
381 IF (p_error_code = c_SUCCESS) THEN
382 p_org_rec := l_org_rec;
383 END IF;
384
385 trace(C_STATE_LEVEL, l_procedure_name, 'END');
386 EXCEPTION
387 WHEN OTHERS THEN
388 p_error_code := c_FAILURE;
389 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
390 fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
391 p_error_desc := fnd_message.get;
392 stack_error (l_procedure_name, 'FINAL', p_error_desc);
393 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:FINAL:'||p_error_desc);
394 END;
395
396 /*
397 ----------------------------------------------------------------------------
398 -- This procedure is used to get the segment values for a specific ccid --
399 -- Returns the fund (balancing), account and bfy values specfic to the --
400 -- ccid --
401 ----------------------------------------------------------------------------
402 */
403 PROCEDURE get_segment_values
404 (
405 p_ledger_id IN NUMBER,
406 p_ccid IN NUMBER,
407 p_fund_value OUT NOCOPY VARCHAR2,
408 p_account_value OUT NOCOPY VARCHAR2,
409 p_bfy_value OUT NOCOPY VARCHAR2,
410 p_error_code OUT NOCOPY NUMBER,
411 p_error_desc OUT NOCOPY VARCHAR2
412 )
413 IS
414 l_procedure_name VARCHAR2(100) :='.get_fund_and_account_value';
415
416 l_result BOOLEAN;
417 l_ledger_info LedgerRecType;
418 l_no_of_segments NUMBER;
419 l_segments fnd_flex_ext.SegmentArray;
420 BEGIN
421 l_procedure_name := g_path_name || l_procedure_name;
422 p_error_code := c_SUCCESS;
423 p_error_desc := NULL;
424
425 trace(C_STATE_LEVEL, l_procedure_name, 'BEGIN');
426 trace(C_STATE_LEVEL, l_procedure_name, 'p_ledger_id='||p_ledger_id);
427 trace(C_STATE_LEVEL, l_procedure_name, 'p_ccid='||p_ccid);
428
429 -- Call Ledger Info to get Chart of Accounts id for the Ledger
430 l_ledger_info := g_ledger_tab(p_ledger_id);
431
432 -- Call FND API to split the segments into l_segments
433 l_result := fnd_flex_ext.get_segments
434 (
435 C_GL_APPL_SHORT_NAME,
436 C_GL_FLEX_CODE,
437 l_ledger_info.coaid,
438 p_ccid,
439 l_no_of_segments,
440 l_segments
441 );
442
443 trace(C_STATE_LEVEL, l_procedure_name, 'l_no_of_segments='||l_no_of_segments);
444
445 p_fund_value := l_segments(l_ledger_info.balancing_seg_num);
446 p_account_value := l_segments(l_ledger_info.accounting_seg_num);
447 p_bfy_value := l_segments(l_ledger_info.bfy_segment_num);
448
449 trace(C_STATE_LEVEL, l_procedure_name, 'p_fund_value='||p_fund_value);
450 trace(C_STATE_LEVEL, l_procedure_name, 'p_account_value='||p_account_value);
451 trace(C_STATE_LEVEL, l_procedure_name, 'p_bfy_value='||p_bfy_value);
452 trace(C_PROC_LEVEL, l_procedure_name, 'END');
453
454 EXCEPTION
455 WHEN OTHERS THEN
456 p_error_code := c_FAILURE;
457 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
458 fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
459 p_error_desc := fnd_message.get;
460 stack_error (l_procedure_name, 'FINAL', p_error_desc);
461 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:FINAL:'||p_error_desc);
462 END get_segment_values;
463
464 /*
465 ----------------------------------------------------------------------------
466 -- This procedure is used to get the Ledger Information given a ledger_id --
467 -- If the ledger_id is not in cache, the ledger information is obtained --
468 -- form the tables and cached for future calls. --
469 ----------------------------------------------------------------------------
470 */
471 PROCEDURE get_ledger_info
472 (
473 p_ledger_id IN NUMBER,
474 p_ledger_rec OUT NOCOPY LedgerRecType,
475 p_error_code OUT NOCOPY NUMBER,
476 p_error_desc OUT NOCOPY VARCHAR2
477 )
478 IS
479 l_procedure_name VARCHAR2(100) :='.get_ledger_info';
480 l_ledger_rec LedgerRecType;
481 BEGIN
482 l_procedure_name := g_path_name || l_procedure_name;
483 p_error_code := c_SUCCESS;
484 p_error_desc := NULL;
485
486 trace(C_STATE_LEVEL, l_procedure_name, 'BEGIN');
487 trace(C_STATE_LEVEL, l_procedure_name, 'p_ledger_id = '||p_ledger_id);
488
489 -- See if the ledger information is in Cache
490 IF g_ledger_tab.EXISTS(p_ledger_id) THEN
491 l_ledger_rec := g_ledger_tab(p_ledger_id);
492 ELSE
493 trace(C_STATE_LEVEL, l_procedure_name, 'Getting Ledger Information');
494 BEGIN
495 SELECT l.ledger_id,
496 l.chart_of_accounts_id,
497 l.name,
498 l.currency_code
499 INTO l_ledger_rec.ledger_id,
500 l_ledger_rec.coaid,
501 l_ledger_rec.ledger_name,
502 l_ledger_rec.currency_code
503 FROM gl_ledgers l
504 WHERE ledger_id = p_ledger_id;
505 trace(C_STATE_LEVEL, l_procedure_name, 'chart_of_accounts_id='||l_ledger_rec.coaid);
506 EXCEPTION
507 WHEN OTHERS THEN
508 p_error_code := c_FAILURE;
509 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
510 fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
511 p_error_desc := fnd_message.get;
512 stack_error (l_procedure_name, 'SELECT_GL_LEDGERS', p_error_desc);
513 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:SELECT_GL_LEDGERS:'||p_error_desc);
514 END;
515
516 IF (p_error_code = c_SUCCESS) THEN
517 -- Get the GL ACCOUNT segment num and name
518 BEGIN
519 SELECT b.segment_num,
520 b.application_column_name
521 INTO l_ledger_rec.accounting_seg_num,
522 l_ledger_rec.accounting_seg_name
523 FROM fnd_segment_attribute_values a,
524 fnd_id_flex_segments b
525 WHERE a.application_id = b.application_id
526 AND a.id_flex_code = b.id_flex_code
527 AND a.id_flex_num = b.id_flex_num
528 AND a.application_column_name = b.application_column_name
529 AND a.segment_attribute_type = 'GL_ACCOUNT'
530 AND a.attribute_value = 'Y'
531 AND b.application_id = C_GL_APPLICATION
532 AND b.id_flex_code = C_GL_FLEX_CODE
533 AND b.id_flex_num = l_ledger_rec.coaid;
534 trace(C_STATE_LEVEL, l_procedure_name, 'accounting_seg_num='||l_ledger_rec.accounting_seg_num);
535 trace(C_STATE_LEVEL, l_procedure_name, 'accounting_seg_name='||l_ledger_rec.accounting_seg_name);
536 EXCEPTION
537 WHEN OTHERS THEN
538 p_error_code := c_FAILURE;
539 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
540 fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
541 p_error_desc := fnd_message.get;
542 stack_error (l_procedure_name, 'SELECT_FND_ID_FLEX_SEGMENTS (GL_ACCOUNT)', p_error_desc);
543 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:SELECT_FND_ID_FLEX_SEGMENTS (GL_ACCOUNT):'||p_error_desc);
544 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
545 fnd_message.set_token('MESSAGE' , 'Error in Getting Accounting Segment.'||CRLF||
546 'Accounting Segment is not Defined for the Ledger '||l_ledger_rec.ledger_name);
547 p_error_desc := fnd_message.get;
548 stack_error (l_procedure_name, 'SELECT_FND_ID_FLEX_SEGMENTS (GL_ACCOUNT)', p_error_desc);
549 l_ledger_rec.balancing_seg_num := NULL;
550 l_ledger_rec.balancing_seg_name := NULL;
551 END;
552 END IF;
553
554 IF (p_error_code = c_SUCCESS) THEN
555 -- Get the GL BALANCING segment num and name
556 BEGIN
557 SELECT b.segment_num,
558 b.application_column_name
559 INTO l_ledger_rec.balancing_seg_num,
560 l_ledger_rec.balancing_seg_name
561 FROM fnd_segment_attribute_values a,
562 fnd_id_flex_segments b
563 WHERE a.application_id = b.application_id
564 AND a.id_flex_code = b.id_flex_code
565 AND a.id_flex_num = b.id_flex_num
566 AND a.application_column_name = b.application_column_name
567 AND a.segment_attribute_type = 'GL_BALANCING'
568 AND a.attribute_value = 'Y'
569 AND b.application_id = C_GL_APPLICATION
570 AND b.id_flex_code = C_GL_FLEX_CODE
571 AND b.id_flex_num = l_ledger_rec.coaid;
572 trace(C_STATE_LEVEL, l_procedure_name, 'balancing_segment_num='||l_ledger_rec.balancing_seg_num);
573 trace(C_STATE_LEVEL, l_procedure_name, 'balancing_segment_name='||l_ledger_rec.balancing_seg_name);
574 EXCEPTION
575 WHEN OTHERS THEN
576 p_error_code := c_FAILURE;
577 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
578 fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
579 p_error_desc := fnd_message.get;
580 stack_error (l_procedure_name, 'SELECT_FND_ID_FLEX_SEGMENTS (GL_BALANCING)', p_error_desc);
581 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:SELECT_FND_ID_FLEX_SEGMENTS (GL_BALANCING):'||p_error_desc);
582 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
583 fnd_message.set_token('MESSAGE' , 'Error in Getting Balancing Segment.'||CRLF||
584 'Balancing Segment is not Defined for the Ledger '||l_ledger_rec.ledger_name);
585 p_error_desc := fnd_message.get;
586 stack_error (l_procedure_name, 'SELECT_FND_ID_FLEX_SEGMENTS (GL_BALANCING)', p_error_desc);
587 l_ledger_rec.balancing_seg_num := NULL;
588 l_ledger_rec.balancing_seg_name := NULL;
589 END;
590 END IF;
591
592 IF (p_error_code = c_SUCCESS) THEN
593 -- Get the BFY segment num and bfy id
594 BEGIN
595 SELECT b.segment_num,
596 a.fyr_segment_id
597 INTO l_ledger_rec.bfy_segment_num,
598 l_ledger_rec.fyr_segment_id
599 FROM fv_pya_fiscalyear_segment a,
600 fnd_id_flex_segments b
601 WHERE set_of_books_id = p_ledger_id
602 AND a.application_column_name = b.application_column_name
603 AND b.application_id = C_GL_APPLICATION
604 AND b.id_flex_code = C_GL_FLEX_CODE
605 AND b.id_flex_num = l_ledger_rec.coaid;
606 trace(C_STATE_LEVEL, l_procedure_name, 'bfy_segment_num='||l_ledger_rec.bfy_segment_num);
607 trace(C_STATE_LEVEL, l_procedure_name, 'fyr_segment_id='||l_ledger_rec.fyr_segment_id);
608 EXCEPTION
609 WHEN NO_DATA_FOUND THEN
610 p_error_code := c_FAILURE;
611 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
612 fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
613 p_error_desc := fnd_message.get;
614 stack_error (l_procedure_name, 'SELECT_FV_PYA_FISCALYEAR_SEGMENT', p_error_desc);
615 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:SELECT_FV_PYA_FISCALYEAR_SEGMENT:'||p_error_desc);
616 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
617 fnd_message.set_token('MESSAGE' , 'Error in setup of Define Federal Options Form.'||CRLF||
618 'BFY Segment is not Defined for the Ledger '||l_ledger_rec.ledger_name);
619 p_error_desc := fnd_message.get;
620 stack_error (l_procedure_name, 'SELECT_FV_PYA_FISCALYEAR_SEGMENT', p_error_desc);
621 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:SELECT_FV_PYA_FISCALYEAR_SEGMENT:'||p_error_desc);
622 l_ledger_rec.bfy_segment_num := NULL;
623 l_ledger_rec.fyr_segment_id := NULL;
624 END;
625 END IF;
626
627 IF (p_error_code = c_SUCCESS) THEN
628 g_ledger_tab(p_ledger_id) := l_ledger_rec;
629 END IF;
630 END IF;
631
632 IF (p_error_code = c_SUCCESS) THEN
633 p_ledger_rec := l_ledger_rec;
634 END IF;
635
636 trace(C_STATE_LEVEL, l_procedure_name, 'END');
637 EXCEPTION
638 WHEN OTHERS THEN
639 p_error_code := c_FAILURE;
640 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
641 fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
642 p_error_desc := fnd_message.get;
643 stack_error (l_procedure_name, 'FINAL', p_error_desc);
644 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:FINAL:'||p_error_desc);
645 END;
646
647 /*
648 Generic Wrapper API for all get_fund_details
649 */
650 PROCEDURE get_fund_details
651 (
652 p_ledger_id IN NUMBER,
653 p_fund_value IN VARCHAR2,
654 p_fund_parameter_rec OUT NOCOPY fv_fund_parameters%ROWTYPE,
655 p_treasury_symbols_rec OUT NOCOPY fv_treasury_symbols%ROWTYPE,
656 p_error_code OUT NOCOPY NUMBER,
657 p_error_desc OUT NOCOPY VARCHAR2
658 )
659 IS
660 l_procedure_name VARCHAR2(100) :='.get_fund_details';
661 BEGIN
662 l_procedure_name := g_path_name || l_procedure_name;
663 p_error_code := c_SUCCESS;
664 p_error_desc := NULL;
665
666 trace(C_STATE_LEVEL, l_procedure_name, 'BEGIN');
667 trace(C_STATE_LEVEL, l_procedure_name, 'p_ledger_id='||p_ledger_id);
668 trace(C_STATE_LEVEL, l_procedure_name, 'p_fund_value='||p_fund_value);
669
670 BEGIN
671 SELECT *
672 INTO p_fund_parameter_rec
673 FROM fv_fund_parameters a
674 WHERE a.set_of_books_id = p_ledger_id
675 AND a.fund_value = p_fund_value;
676 EXCEPTION
677 WHEN NO_DATA_FOUND THEN
678 p_error_code := c_FAILURE;
679 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
680 fnd_message.set_token('MESSAGE' , 'Details for Fund '||p_fund_value||' could not be found in Treasury/Fund Setup');
681 p_error_desc := fnd_message.get;
682 stack_error (l_procedure_name, 'FINAL', p_error_desc);
683 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:FINAL:'||p_error_desc);
684 END;
685
686 IF (p_error_code = c_SUCCESS) THEN
687 trace(C_STATE_LEVEL, l_procedure_name, 'treasury_symbol_id='||p_fund_parameter_rec.treasury_symbol_id);
688 SELECT *
689 INTO p_treasury_symbols_rec
690 FROM fv_treasury_symbols
691 WHERE treasury_symbol_id = p_fund_parameter_rec.treasury_symbol_id;
692 NULL;
693 END IF;
694 trace(C_PROC_LEVEL, l_procedure_name, 'END');
695 EXCEPTION
696 WHEN OTHERS THEN
697 p_error_code := c_FAILURE;
698 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
699 fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
700 p_error_desc := fnd_message.get;
701 stack_error (l_procedure_name, 'FINAL', p_error_desc);
702 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:FINAL:'||p_error_desc);
703 END;
704
705 /*
706 CLM Wrapper API
707 */
708 PROCEDURE get_fund_information
709 (
710 p_ledger_id IN NUMBER,
711 p_ccid IN NUMBER,
712 p_fiscal_year OUT NOCOPY NUMBER,
713 p_fund_cancellation_date OUT NOCOPY DATE,
714 p_fund_start_date OUT NOCOPY DATE,
715 p_fund_end_date OUT NOCOPY DATE,
716 p_non_annual_fund_flag OUT NOCOPY VARCHAR2,
717 p_error_code OUT NOCOPY NUMBER,
718 p_error_desc OUT NOCOPY VARCHAR2
719 )
720 IS
721 l_procedure_name VARCHAR2(100) :='.get_fund_information';
722 l_ledger_info LedgerRecType;
723 l_fund_value fv_fund_parameters.fund_value%TYPE;
724 l_account_value VARCHAR2(30);
725 l_bfy_value VARCHAR2(30);
726 l_fund_parameter_rec fv_fund_parameters%ROWTYPE;
727 l_treasury_symbols_rec fv_treasury_symbols%ROWTYPE;
728 BEGIN
729 p_error_code := c_SUCCESS;
730 p_error_desc := NULL;
731
732 trace(C_STATE_LEVEL, l_procedure_name, 'BEGIN');
733 trace(C_STATE_LEVEL, l_procedure_name, 'p_ledger_id='||p_ledger_id);
734 trace(C_STATE_LEVEL, l_procedure_name, 'p_ccid='||p_ccid);
735
736 IF (p_error_code = c_SUCCESS) THEN
737 trace(C_STATE_LEVEL, l_procedure_name, 'Calling get_ledger_info');
738 get_ledger_info
739 (
740 p_ledger_id => p_ledger_id,
741 p_ledger_rec => l_ledger_info,
742 p_error_code => p_error_code,
743 p_error_desc => p_error_desc
744 );
745 trace(C_STATE_LEVEL, l_procedure_name, 'get_ledger_info returned p_error_code='||p_error_code);
746 trace(C_STATE_LEVEL, l_procedure_name, 'get_ledger_info returned p_error_desc='||p_error_desc);
747 END IF;
748
749 IF (p_error_code = c_SUCCESS) THEN
750 trace(C_STATE_LEVEL, l_procedure_name, 'Calling get_segment_values');
751 get_segment_values
752 (
753 p_ledger_id => l_ledger_info.ledger_id,
754 p_ccid => p_ccid,
755 p_fund_value => l_fund_value,
756 p_account_value => l_account_value,
757 p_bfy_value => l_bfy_value,
758 p_error_code => p_error_code,
759 p_error_desc => p_error_desc
760 );
761 trace(C_STATE_LEVEL, l_procedure_name, 'get_segment_values returned l_fund_value='||l_fund_value);
762 trace(C_STATE_LEVEL, l_procedure_name, 'get_segment_values returned p_error_code='||p_error_code);
763 trace(C_STATE_LEVEL, l_procedure_name, 'get_segment_values returned p_error_desc='||p_error_desc);
764 END IF;
765
766 IF (p_error_code = c_SUCCESS) THEN
767 trace(C_STATE_LEVEL, l_procedure_name, 'Calling get_fund_details');
768 get_fund_details
769 (
770 p_ledger_id => p_ledger_id,
771 p_fund_value => l_fund_value,
772 p_fund_parameter_rec => l_fund_parameter_rec,
773 p_treasury_symbols_rec => l_treasury_symbols_rec,
774 p_error_code => p_error_code,
775 p_error_desc => p_error_desc
776 );
777 trace(C_STATE_LEVEL, l_procedure_name, 'get_fund_details returned p_error_code='||p_error_code);
778 trace(C_STATE_LEVEL, l_procedure_name, 'get_fund_details returned p_error_desc='||p_error_desc);
779 END IF;
780
781 IF (p_error_code = c_SUCCESS) THEN
782 p_fiscal_year := l_treasury_symbols_rec.established_fiscal_yr;
783 p_fund_cancellation_date := l_treasury_symbols_rec.cancellation_date;
784 p_fund_start_date := l_treasury_symbols_rec.start_date;
785 p_fund_end_date := l_treasury_symbols_rec.expiration_date;
786 IF (l_treasury_symbols_rec.time_frame IN ('M', 'X')) THEN
787 p_non_annual_fund_flag := 'Y';
788 ELSIF (l_treasury_symbols_rec.time_frame IN ('A')) THEN
789 p_non_annual_fund_flag := 'N';
790 ELSE
791 p_non_annual_fund_flag := NULL;
792 END IF;
793 END IF;
794
795 trace(C_PROC_LEVEL, l_procedure_name, 'END');
796 EXCEPTION
797 WHEN OTHERS THEN
798 p_error_code := c_FAILURE;
799 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
800 fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
801 p_error_desc := fnd_message.get;
802 stack_error (l_procedure_name, 'FINAL', p_error_desc);
803 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:FINAL:'||p_error_desc);
804 END;
805
806 PROCEDURE get_fund_details
807 (
808 p_application_id IN NUMBER,
809 p_ledger_id IN NUMBER,
810 p_fund_value IN VARCHAR2,
811 p_gl_date IN DATE,
812 p_appor_category OUT NOCOPY fv_fund_parameters.fund_category%TYPE,
813 p_direct_or_reimb OUT NOCOPY fv_fund_parameters.fund_category%TYPE,
814 p_fund_status OUT NOCOPY VARCHAR2,
815 p_fund_time_frame OUT NOCOPY fv_treasury_symbols.time_frame%TYPE,
816 p_treasury_symbol_id OUT NOCOPY fv_fund_parameters.treasury_symbol_id%TYPE,
817 p_treasury_symbol OUT NOCOPY fv_treasury_symbols.treasury_symbol%TYPE,
818 p_no_pya_acct_flag OUT NOCOPY fv_treasury_symbols.no_pya_acct_flag%TYPE,
819 p_fund_type OUT NOCOPY fv_treasury_symbols.fund_type%TYPE,
820 p_error_code OUT NOCOPY NUMBER,
821 p_error_desc OUT NOCOPY VARCHAR2
822 )
823 IS
824 l_procedure_name VARCHAR2(100) :='.get_fund_details';
825 l_fund_parameter_rec fv_fund_parameters%ROWTYPE;
826 l_treasury_symbols_rec fv_treasury_symbols%ROWTYPE;
827
828 BEGIN
829 l_procedure_name := g_path_name || l_procedure_name;
830 p_error_code := c_SUCCESS;
831 p_error_desc := NULL;
832
833 trace(C_STATE_LEVEL, l_procedure_name, 'BEGIN');
834 trace(C_STATE_LEVEL, l_procedure_name, 'p_application_id='||p_application_id);
835 trace(C_STATE_LEVEL, l_procedure_name, 'p_ledger_id='||p_ledger_id);
836 trace(C_STATE_LEVEL, l_procedure_name, 'p_fund_value='||p_fund_value);
837 trace(C_STATE_LEVEL, l_procedure_name, 'p_gl_date='||p_gl_date);
838
839 p_fund_status := NULL;
840 p_appor_category := NULL;
841 p_fund_time_frame := NULL;
842 p_direct_or_reimb := NULL;
843 p_fund_type := NULL;
844
845 get_fund_details
846 (
847 p_ledger_id => p_ledger_id,
848 p_fund_value => p_fund_value,
849 p_fund_parameter_rec => l_fund_parameter_rec,
850 p_treasury_symbols_rec => l_treasury_symbols_rec,
851 p_error_code => p_error_code,
852 p_error_desc => p_error_desc
853 );
854
855 IF (p_error_code = c_SUCCESS) THEN
856 p_treasury_symbol_id := l_fund_parameter_rec.treasury_symbol_id;
857 p_treasury_symbol := l_treasury_symbols_rec.treasury_symbol;
858 p_fund_time_frame := l_treasury_symbols_rec.time_frame;
859 p_no_pya_acct_flag := NVL(l_treasury_symbols_rec.no_pya_acct_flag, 'N');
860 p_fund_type := l_treasury_symbols_rec.fund_type;
861 -- fund category
862 IF (l_fund_parameter_rec.direct_or_reimb_code IS NULL) THEN
863 IF l_fund_parameter_rec.fund_category IN ('A','S') THEN
864 p_appor_category := 'A';
865 ELSIF l_fund_parameter_rec.fund_category IN ('B','T') THEN
866 p_appor_category := 'B';
867 ELSE
868 p_appor_category := 'C';
869 END IF;
870
871 IF (l_fund_parameter_rec.fund_category IN ('A', 'B', 'C')) THEN
872 p_direct_or_reimb := 'D';
873 ELSIF (l_fund_parameter_rec.fund_category IN ('R', 'S', 'T')) THEN
874 p_direct_or_reimb := 'R';
875 END IF;
876 ELSE
877 p_appor_category := l_fund_parameter_rec.fund_category;
878 p_direct_or_reimb := l_fund_parameter_rec.direct_or_reimb_code;
879 END IF;
880
881 -- fund expired
882 IF l_fund_parameter_rec.fund_expire_date < p_gl_date THEN
883 p_fund_status := 'Expired';
884 ELSE
885 p_fund_status := 'Unexpired';
886 END IF;
887 END IF;
888
889 trace(C_PROC_LEVEL, l_procedure_name, 'END');
890 EXCEPTION
891 WHEN OTHERS THEN
892 p_error_code := c_FAILURE;
893 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
894 fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
895 p_error_desc := fnd_message.get;
896 stack_error (l_procedure_name, 'FINAL', p_error_desc);
897 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:FINAL:'||p_error_desc);
898 END;
899
900 /*
901 ----------------------------------------------------------------------------
902 -- This procedure gets the fund information from Federal tables for a --
903 -- specific fund --
904 ----------------------------------------------------------------------------
905 */
906 PROCEDURE get_fund_details
907 (
908 p_application_id IN NUMBER,
909 p_ledger_id IN NUMBER,
910 p_fund_value IN VARCHAR2,
911 p_gl_date IN DATE,
912 p_fund_category OUT NOCOPY fv_fund_parameters.fund_category%TYPE,
913 p_fund_status OUT NOCOPY VARCHAR2,
914 p_fund_time_frame OUT NOCOPY fv_treasury_symbols.time_frame%TYPE,
915 p_treasury_symbol_id OUT NOCOPY fv_fund_parameters.treasury_symbol_id%TYPE,
916 p_treasury_symbol OUT NOCOPY fv_treasury_symbols.treasury_symbol%TYPE,
917 p_no_pya_acct_flag OUT NOCOPY fv_treasury_symbols.no_pya_acct_flag%TYPE,
918 p_error_code OUT NOCOPY NUMBER,
919 p_error_desc OUT NOCOPY VARCHAR2
920 )
921 IS
922 l_procedure_name VARCHAR2(100) :='.get_fund_details';
923 l_direct_or_reimb VARCHAR2(1);
924 l_fund_type fv_treasury_symbols.fund_type%TYPE;
925 BEGIN
926 l_procedure_name := g_path_name || l_procedure_name;
927 p_error_code := c_SUCCESS;
928 p_error_desc := NULL;
929
930 trace(C_STATE_LEVEL, l_procedure_name, 'BEGIN');
931 trace(C_STATE_LEVEL, l_procedure_name, 'p_application_id='||p_application_id);
932 trace(C_STATE_LEVEL, l_procedure_name, 'p_ledger_id='||p_ledger_id);
933 trace(C_STATE_LEVEL, l_procedure_name, 'p_fund_value='||p_fund_value);
934 trace(C_STATE_LEVEL, l_procedure_name, 'p_gl_date='||p_gl_date);
935
936 get_fund_details
937 (
938 p_application_id => p_application_id,
939 p_ledger_id => p_ledger_id,
940 p_fund_value => p_fund_value,
941 p_gl_date => p_gl_date,
942 p_appor_category => p_fund_category,
943 p_direct_or_reimb => l_direct_or_reimb,
944 p_fund_status => p_fund_status,
945 p_fund_time_frame => p_fund_time_frame,
946 p_treasury_symbol_id => p_treasury_symbol_id,
947 p_treasury_symbol => p_treasury_symbol,
948 p_no_pya_acct_flag => p_no_pya_acct_flag,
949 p_fund_type => l_fund_type,
950 p_error_code => p_error_code,
951 p_error_desc => p_error_desc
952 );
953
954 trace(C_PROC_LEVEL, l_procedure_name, 'END');
955 EXCEPTION
956 WHEN OTHERS THEN
957 p_error_code := c_FAILURE;
958 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
959 fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
960 p_error_desc := fnd_message.get;
961 stack_error (l_procedure_name, 'FINAL', p_error_desc);
962 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:FINAL:'||p_error_desc);
963 END get_fund_details;
964
965 PROCEDURE get_prior_year_status
966 (
967 p_application_id IN NUMBER,
968 p_ledger_id IN NUMBER,
969 p_bfy_value IN VARCHAR2,
970 p_gl_date IN DATE,
971 p_pya OUT NOCOPY VARCHAR2,
972 p_pya_type OUT NOCOPY VARCHAR2,
973 p_error_code OUT NOCOPY NUMBER,
974 p_error_desc OUT NOCOPY VARCHAR2
975 )
976 IS
977 l_procedure_name VARCHAR2(100) := '.get_prior_year_status';
978 l_transaction_year gl_period_statuses.period_year%TYPE;
979 l_bfy_map_year fv_pya_fiscalyear_map.period_year%TYPE;
980 l_ledger_info LedgerRecType;
981
982 BEGIN
983
984 l_procedure_name := g_path_name || l_procedure_name;
985 p_error_code := c_SUCCESS;
986 p_error_desc := NULL;
987
988 -------------------------------------------------------------------------
989 trace(C_STATE_LEVEL, l_procedure_name, 'BEGIN');
990 trace(C_STATE_LEVEL, l_procedure_name, 'p_application_id='||p_application_id);
991 trace(C_STATE_LEVEL, l_procedure_name, 'p_ledger_id='||p_ledger_id);
992 trace(C_STATE_LEVEL, l_procedure_name, 'p_bfy_value='||p_bfy_value);
993 trace(C_STATE_LEVEL, l_procedure_name, 'p_gl_date='||p_gl_date);
994 -------------------------------------------------------------------------
995
996 get_ledger_info
997 (
998 p_ledger_id => p_ledger_id,
999 p_ledger_rec => l_ledger_info,
1000 p_error_code => p_error_code,
1001 p_error_desc => p_error_desc
1002 );
1003
1004 IF (p_error_code = c_SUCCESS) THEN
1005 BEGIN
1006 SELECT period_year
1007 INTO l_bfy_map_year
1008 FROM fv_pya_fiscalyear_map
1009 WHERE set_of_books_id = p_ledger_id
1010 AND fyr_segment_id = l_ledger_info.fyr_segment_id
1011 AND fyr_segment_value = p_bfy_value;
1012 trace(C_STATE_LEVEL, l_procedure_name, 'l_bfy_map_year='||l_bfy_map_year);
1013 EXCEPTION
1014 WHEN NO_DATA_FOUND THEN
1015 p_error_code := c_FAILURE;
1016 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1017 fnd_message.set_token('MESSAGE' , 'Error in setup of Define Federal Options Form.'||CRLF||
1018 'Segment Mapping is missing for the Ledger '||l_ledger_info.ledger_name||'. '||CRLF||
1019 'Mapping of BFY Year '||p_bfy_value||' is missing.');
1020 p_error_desc := fnd_message.get;
1021 stack_error (l_procedure_name, 'SELECT_FV_PYA_FISCALYEAR_MAP', p_error_desc);
1022 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:SELECT_FV_PYA_FISCALYEAR_MAP:'||p_error_desc);
1023 WHEN OTHERS THEN
1024 p_error_code := c_FAILURE;
1025 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1026 fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
1027 p_error_desc := fnd_message.get;
1028 stack_error (l_procedure_name, 'SELECT_FV_PYA_FISCALYEAR_MAP', p_error_desc);
1029 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:SELECT_FV_PYA_FISCALYEAR_MAP:'||p_error_desc);
1030 END;
1031 END IF;
1032
1033 IF (p_error_code = c_SUCCESS) THEN
1034 BEGIN
1035 SELECT period_year
1036 INTO l_transaction_year
1037 FROM gl_period_statuses
1038 WHERE ledger_id = p_ledger_id
1039 AND application_id = p_application_id
1040 AND trunc(p_gl_date) BETWEEN start_date AND end_date
1041 AND adjustment_period_flag='N';
1042 trace(C_STATE_LEVEL, l_procedure_name, 'l_transaction_year='||l_transaction_year);
1043 EXCEPTION
1044 WHEN OTHERS THEN
1045 p_error_code := c_FAILURE;
1046 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1047 fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
1048 p_error_desc := fnd_message.get;
1049 stack_error (l_procedure_name, 'SELECT_GL_PERIOD_STATUSES', p_error_desc);
1050 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:SELECT_GL_PERIOD_STATUSES:'||p_error_desc);
1051 END;
1052 END IF;
1053
1054 IF (p_error_code = c_SUCCESS) THEN
1055 p_pya_type := NULL;
1056 IF l_transaction_year <> l_bfy_map_year THEN
1057 IF l_transaction_year > l_bfy_map_year THEN
1058 p_pya_type := 'Upward';
1059 ELSIF l_transaction_year < l_bfy_map_year THEN
1060 p_pya_type := 'Downward';
1061 END IF;
1062 p_pya := 'Y';
1063 ELSE
1064 p_pya := 'N';
1065 END IF;
1066 END IF;
1067
1068 trace(C_PROC_LEVEL, l_procedure_name, 'p_pya='||p_pya);
1069 trace(C_PROC_LEVEL, l_procedure_name, 'p_pya_type='||p_pya_type);
1070 trace(C_PROC_LEVEL, l_procedure_name, 'END');
1071 EXCEPTION
1072 WHEN OTHERS THEN
1073 p_error_code := c_FAILURE;
1074 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1075 fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
1076 p_error_desc := fnd_message.get;
1077 stack_error (l_procedure_name, 'FINAL', p_error_desc);
1078 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:FINAL:'||p_error_desc);
1079 END get_prior_year_status;
1080
1081 PROCEDURE dump_gt_table
1082 (
1083 p_error_code OUT NOCOPY NUMBER,
1084 p_error_desc OUT NOCOPY VARCHAR2
1085 )
1086 IS
1087 l_procedure_name VARCHAR2(100) :='.dump_gt_table';
1088 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('FV_DEBUG_FLAG'),'N');
1089 l_fv_extract_detail fv_ref_detail;
1090 BEGIN
1091 l_procedure_name := g_path_name || l_procedure_name;
1092 p_error_code := c_SUCCESS;
1093 p_error_desc := NULL;
1094
1095 IF (l_debug = 'Y') THEN
1096 SELECT *
1097 BULK COLLECT INTO l_fv_extract_detail
1098 FROM fv_extract_detail_gt;
1099
1100 dump_gt_table
1101 (
1102 p_fv_extract_detail => l_fv_extract_detail,
1103 p_error_code => p_error_code,
1104 p_error_desc => p_error_desc
1105 );
1106 END IF;
1107
1108 EXCEPTION
1109 WHEN OTHERS THEN
1110 p_error_code := c_FAILURE;
1111 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1112 fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
1113 p_error_desc := fnd_message.get;
1114 stack_error (l_procedure_name, 'FINAL', p_error_desc);
1115 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:FINAL:'||p_error_desc);
1116 END dump_gt_table;
1117
1118
1119 PROCEDURE dump_gt_table
1120 (
1121 p_fv_extract_detail IN fv_ref_detail,
1122 p_error_code OUT NOCOPY NUMBER,
1123 p_error_desc OUT NOCOPY VARCHAR2
1124 )
1125 IS
1126 PRAGMA AUTONOMOUS_TRANSACTION;
1127 l_procedure_name VARCHAR2(100) :='.dump_gt_table';
1128 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('FV_DEBUG_FLAG'),'N');
1129 BEGIN
1130 l_procedure_name := g_path_name || l_procedure_name;
1131 p_error_code := c_SUCCESS;
1132 p_error_desc := NULL;
1133
1134 IF (p_fv_extract_detail.count = 0) THEN
1135 RETURN;
1136 END IF;
1137
1138 IF (l_debug = 'Y') THEN
1139 FOR i IN p_fv_extract_detail.first..p_fv_extract_detail.last LOOP
1140 INSERT INTO fv_extract_detail_gt_logs
1141 (
1142 event_id,
1143 line_number,
1144 application_id,
1145 fund_value,
1146 fund_category,
1147 fund_expired_status,
1148 prior_year_flag,
1149 adjustment_type,
1150 net_pya_adj_amt,
1151 entered_pya_amt,
1152 entered_pya_diff_amt,
1153 anticipation,
1154 anticipated_amt,
1155 unanticipated_amt,
1156 tcf_amt,
1157 unexpended_obligation,
1158 paid_unexpended_obligation,
1159 paid_received_amt,
1160 unpaid_unexpended_obligation,
1161 unpaid_received_amt,
1162 unpaid_open_amt,
1163 fund_time_frame,
1164 rcv_parent_sub_ledger_id,
1165 account_valid_flag,
1166 account_rule,
1167 old_ccid,
1168 receivable_with_advance,
1169 ent_commitment_amount,
1170 ent_unpaid_obl_amount,
1171 acc_commitment_amount,
1172 acc_unpaid_obl_amount,
1173 ent_unpaid_obl_pya_amount,
1174 acc_unpaid_obl_pya_amount,
1175 ent_unpaid_obl_pya_off_amount,
1176 acc_unpaid_obl_pya_off_amount,
1177 ent_anticipated_budget_amount,
1178 acc_anticipated_budget_amount,
1179 ent_unanticipated_bud_amount,
1180 acc_unanticipated_bud_amount,
1181 ent_unreserved_budget_amount,
1182 acc_unreserved_budget_amount,
1183 ent_charge_amount,
1184 acc_charge_amount,
1185 ent_unpaid_exp_amount,
1186 acc_unpaid_exp_amount,
1187 ent_paid_exp_amount,
1188 acc_paid_exp_amount,
1189 ar_transaction_category,
1190 acc_paid_exp_pya_off_amount,
1191 ent_paid_exp_pya_off_amount,
1192 ent_paid_obl_amount,
1193 acc_paid_obl_amount,
1194 ent_unpaid_exp_pya_amount,
1195 acc_unpaid_exp_pya_amount,
1196 ent_unpaid_exp_pya_off_amount,
1197 acc_unpaid_exp_pya_off_amount,
1198 ent_paid_exp_pya_amount,
1199 acc_paid_exp_pya_amount,
1200 acc_expended_approp_amount,
1201 ent_expended_approp_amount,
1202 treasury_symbol_id,
1203 direct_or_reimb,
1204 po_distribution_id,
1205 fund_type,
1206 fed_non_fed_ind,
1207 acc_refund_amount,
1208 ent_refund_amount,
1209 advance_required
1210 )
1211 VALUES
1212 (
1213 p_fv_extract_detail(i).event_id,
1214 p_fv_extract_detail(i).line_number,
1215 p_fv_extract_detail(i).application_id,
1216 p_fv_extract_detail(i).fund_value,
1217 p_fv_extract_detail(i).fund_category,
1218 p_fv_extract_detail(i).fund_expired_status,
1219 p_fv_extract_detail(i).prior_year_flag,
1220 p_fv_extract_detail(i).adjustment_type,
1221 p_fv_extract_detail(i).net_pya_adj_amt,
1222 p_fv_extract_detail(i).entered_pya_amt,
1223 p_fv_extract_detail(i).entered_pya_diff_amt,
1224 p_fv_extract_detail(i).anticipation,
1225 p_fv_extract_detail(i).anticipated_amt,
1226 p_fv_extract_detail(i).unanticipated_amt,
1227 p_fv_extract_detail(i).tcf_amt,
1228 p_fv_extract_detail(i).unexpended_obligation,
1229 p_fv_extract_detail(i).paid_unexpended_obligation,
1230 p_fv_extract_detail(i).paid_received_amt,
1231 p_fv_extract_detail(i).unpaid_unexpended_obligation,
1232 p_fv_extract_detail(i).unpaid_received_amt,
1233 p_fv_extract_detail(i).unpaid_open_amt,
1234 p_fv_extract_detail(i).fund_time_frame,
1235 p_fv_extract_detail(i).rcv_parent_sub_ledger_id,
1236 p_fv_extract_detail(i).account_valid_flag,
1237 p_fv_extract_detail(i).account_rule,
1238 p_fv_extract_detail(i).old_ccid,
1239 p_fv_extract_detail(i).receivable_with_advance,
1240 p_fv_extract_detail(i).ent_commitment_amount,
1241 p_fv_extract_detail(i).ent_unpaid_obl_amount,
1242 p_fv_extract_detail(i).acc_commitment_amount,
1243 p_fv_extract_detail(i).acc_unpaid_obl_amount,
1244 p_fv_extract_detail(i).ent_unpaid_obl_pya_amount,
1245 p_fv_extract_detail(i).acc_unpaid_obl_pya_amount,
1246 p_fv_extract_detail(i).ent_unpaid_obl_pya_off_amount,
1247 p_fv_extract_detail(i).acc_unpaid_obl_pya_off_amount,
1248 p_fv_extract_detail(i).ent_anticipated_budget_amount,
1249 p_fv_extract_detail(i).acc_anticipated_budget_amount,
1250 p_fv_extract_detail(i).ent_unanticipated_bud_amount,
1251 p_fv_extract_detail(i).acc_unanticipated_bud_amount,
1252 p_fv_extract_detail(i).ent_unreserved_budget_amount,
1253 p_fv_extract_detail(i).acc_unreserved_budget_amount,
1254 p_fv_extract_detail(i).ent_charge_amount,
1255 p_fv_extract_detail(i).acc_charge_amount,
1256 p_fv_extract_detail(i).ent_unpaid_exp_amount,
1257 p_fv_extract_detail(i).acc_unpaid_exp_amount,
1258 p_fv_extract_detail(i).ent_paid_exp_amount,
1259 p_fv_extract_detail(i).acc_paid_exp_amount,
1260 p_fv_extract_detail(i).ar_transaction_category,
1261 p_fv_extract_detail(i).acc_paid_exp_pya_off_amount,
1262 p_fv_extract_detail(i).ent_paid_exp_pya_off_amount,
1263 p_fv_extract_detail(i).ent_paid_obl_amount,
1264 p_fv_extract_detail(i).acc_paid_obl_amount,
1265 p_fv_extract_detail(i).ent_unpaid_exp_pya_amount,
1266 p_fv_extract_detail(i).acc_unpaid_exp_pya_amount,
1267 p_fv_extract_detail(i).ent_unpaid_exp_pya_off_amount,
1268 p_fv_extract_detail(i).acc_unpaid_exp_pya_off_amount,
1269 p_fv_extract_detail(i).ent_paid_exp_pya_amount,
1270 p_fv_extract_detail(i).acc_paid_exp_pya_amount,
1271 p_fv_extract_detail(i).acc_expended_approp_amount,
1272 p_fv_extract_detail(i).ent_expended_approp_amount,
1273 p_fv_extract_detail(i).treasury_symbol_id,
1274 p_fv_extract_detail(i).direct_or_reimb,
1275 p_fv_extract_detail(i).po_distribution_id,
1276 p_fv_extract_detail(i).fund_type,
1277 p_fv_extract_detail(i).fed_non_fed_ind,
1278 p_fv_extract_detail(i).acc_refund_amount,
1279 p_fv_extract_detail(i).ent_refund_amount,
1280 p_fv_extract_detail(i).advance_required
1281 );
1282 END LOOP;
1283 COMMIT;
1284 END IF;
1285 EXCEPTION
1286 WHEN OTHERS THEN
1287 p_error_code := c_FAILURE;
1288 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1289 fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
1290 p_error_desc := fnd_message.get;
1291 stack_error (l_procedure_name, 'FINAL', p_error_desc);
1292 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:FINAL:'||p_error_desc);
1293 END;
1294
1295
1296 PROCEDURE get_sla_doc_balances
1297 (
1298 p_called_from IN VARCHAR2,
1299 p_trx_amount IN NUMBER,
1300 p_ordered_amount IN NUMBER,
1301 p_delivered_amount IN NUMBER,
1302 p_billed_amount IN NUMBER,
1303 p_4801_bal OUT NOCOPY NUMBER,
1304 p_4802_bal OUT NOCOPY NUMBER,
1305 p_4901_bal OUT NOCOPY NUMBER,
1306 p_4902_bal OUT NOCOPY NUMBER,
1307 p_error_code OUT NOCOPY NUMBER,
1308 p_error_desc OUT NOCOPY VARCHAR2
1309 )
1310 IS
1311 l_procedure_name VARCHAR2(100) :='.get_sla_doc_balances';
1312 l_delivered_amt NUMBER;
1313 BEGIN
1314 l_procedure_name := g_path_name || l_procedure_name;
1315
1316 p_error_code := c_SUCCESS;
1317 p_error_desc := NULL;
1318 -------------------------------------------------------------------------
1319 trace(C_STATE_LEVEL, l_procedure_name, 'BEGIN');
1320 trace(C_STATE_LEVEL, l_procedure_name, 'p_called_from='||p_called_from);
1321 trace(C_STATE_LEVEL, l_procedure_name, 'p_trx_amount='||p_trx_amount);
1322 trace(C_STATE_LEVEL, l_procedure_name, 'p_ordered_amount='||p_ordered_amount);
1323 trace(C_STATE_LEVEL, l_procedure_name, 'p_delivered_amount='||p_delivered_amount);
1324 trace(C_STATE_LEVEL, l_procedure_name, 'p_billed_amount='||p_billed_amount);
1325 -------------------------------------------------------------------------
1326
1327 p_4801_bal := 0;
1328 p_4802_bal := 0;
1329 p_4901_bal := 0;
1330 p_4902_bal := 0;
1331
1332 IF (p_called_from = 'CST') THEN
1333 l_delivered_amt := p_delivered_amount - p_trx_amount;
1334 trace(C_STATE_LEVEL, l_procedure_name, 'l_delivered_amt='||l_delivered_amt);
1335 IF (NVL(l_delivered_amt, 0) >= NVL(p_billed_amount, 0)) THEN
1336 p_4902_bal := NVL(p_billed_amount, 0);
1337 p_4901_bal := NVL(l_delivered_amt, 0) - NVL(p_billed_amount, 0);
1338 ELSE
1339 p_4902_bal := NVL(l_delivered_amt, 0);
1340 p_4802_bal := NVL(p_billed_amount, 0) - NVL(l_delivered_amt, 0);
1341 END IF;
1342 p_4801_bal := p_ordered_amount - (p_4802_bal + p_4901_bal + p_4902_bal);
1343 IF (p_4801_bal <= 0) THEN
1344 p_4801_bal := 0;
1345 END IF;
1346 END IF;
1347 -------------------------------------------------------------------------
1348 trace(C_STATE_LEVEL, l_procedure_name, 'p_4801_bal='||p_4801_bal);
1349 trace(C_STATE_LEVEL, l_procedure_name, 'p_4802_bal='||p_4802_bal);
1350 trace(C_STATE_LEVEL, l_procedure_name, 'p_4901_bal='||p_4901_bal);
1351 trace(C_STATE_LEVEL, l_procedure_name, 'p_4902_bal='||p_4902_bal);
1352 trace(C_PROC_LEVEL, l_procedure_name, 'END');
1353 -------------------------------------------------------------------------
1354 EXCEPTION
1355 WHEN OTHERS THEN
1356 p_error_code := c_FAILURE;
1357 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1358 fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
1359 p_error_desc := fnd_message.get;
1360 stack_error (l_procedure_name, 'FINAL', p_error_desc);
1361 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:FINAL:'||p_error_desc);
1362 END;
1363
1364 PROCEDURE get_anticipated_ts_amt
1365 (
1366 p_ledger_id IN NUMBER,
1367 p_gl_date IN DATE,
1368 p_treasury_symbol_id IN VARCHAR2,
1369 p_anticipated_amt OUT NOCOPY NUMBER,
1370 p_error_code OUT NOCOPY NUMBER,
1371 p_error_desc OUT NOCOPY VARCHAR2
1372 )
1373 IS
1374 l_procedure_name VARCHAR2(100) :='.get_anticipated_ts_amt';
1375
1376 l_ledger_info LedgerRecType;
1377
1378 --l_anticipated_acct VARCHAR2(30);
1379 l_template_id NUMBER;
1380 l_ccid Gl_Code_Combinations.code_combination_id%TYPE;
1381 l_amount NUMBER;
1382 l_tot_amount NUMBER := 0;
1383
1384 -- Variable declartions for Dynamic SQL
1385 l_fund_cur_id INTEGER;
1386 l_fund_select VARCHAR2(2000);
1387 l_fund_ret INTEGER;
1388 l_period_year gl_period_statuses.period_year%TYPE;
1389 l_period_num gl_period_statuses.period_num%TYPE;
1390 l_period_name gl_period_statuses.period_name%TYPE;
1391
1392 BEGIN
1393 l_procedure_name := g_path_name || l_procedure_name;
1394 p_error_code := c_SUCCESS;
1395 p_error_desc := NULL;
1396
1397 -------------------------------------------------------------------------
1398 trace(C_STATE_LEVEL, l_procedure_name, 'BEGIN');
1399 trace(C_STATE_LEVEL, l_procedure_name, 'p_treasury_symbol_id='||p_treasury_symbol_id);
1400 trace(C_STATE_LEVEL, l_procedure_name, 'p_gl_date='||p_gl_date);
1401 trace(C_STATE_LEVEL, l_procedure_name, 'p_ledger_id='||p_ledger_id);
1402 -------------------------------------------------------------------------
1403 get_ledger_info
1404 (
1405 p_ledger_id => p_ledger_id,
1406 p_ledger_rec => l_ledger_info,
1407 p_error_code => p_error_code,
1408 p_error_desc => p_error_desc
1409 );
1410
1411 IF (p_error_code = c_SUCCESS) THEN
1412 BEGIN
1413 SELECT template_id
1414 INTO l_template_id
1415 FROM fv_pya_fiscalyear_segment
1416 WHERE set_of_books_id = p_ledger_id;
1417 trace(C_STATE_LEVEL, l_procedure_name, 'l_template_id='||l_template_id);
1418 EXCEPTION
1419 WHEN NO_DATA_FOUND THEN
1420 l_template_id := NULL;
1421 WHEN OTHERS THEN
1422 p_error_code := c_FAILURE;
1423 fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
1424 p_error_desc := fnd_message.get;
1425 stack_error (l_procedure_name, 'SELECT_fv_pya_fiscalyear_segment', p_error_desc);
1426 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:SELECT_fv_pya_fiscalyear_segment:'||p_error_desc);
1427 END;
1428 END IF;
1429 trace(C_STATE_LEVEL, l_procedure_name, 'l_template_id='||l_template_id);
1430
1431 IF (p_error_code = c_SUCCESS AND l_template_id IS NOT NULL) THEN
1432 BEGIN
1433 SELECT period_year,
1434 period_num,
1435 period_name
1436 INTO l_period_year,
1437 l_period_num,
1438 l_period_name
1439 FROM gl_period_statuses
1440 WHERE ledger_id = p_ledger_id
1441 AND application_id = C_GL_APPLICATION
1442 AND adjustment_period_flag = 'N'
1443 AND p_gl_date BETWEEN start_date AND end_date;
1444 trace(C_STATE_LEVEL, l_procedure_name, 'l_period_year='||l_period_year);
1445 trace(C_STATE_LEVEL, l_procedure_name, 'l_period_num='||l_period_num);
1446 trace(C_STATE_LEVEL, l_procedure_name, 'l_period_name='||l_period_name);
1447 EXCEPTION
1448 WHEN OTHERS THEN
1449 p_error_code := c_FAILURE;
1450 fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
1451 p_error_desc := fnd_message.get;
1452 stack_error (l_procedure_name, 'SELECT_gl_period_statuses', p_error_desc);
1453 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:SELECT_gl_period_statuses:'||p_error_desc);
1454 END;
1455 END IF;
1456
1457 IF (p_error_code = c_SUCCESS AND l_template_id IS NOT NULL) THEN
1458 -- get the ccid that contains this fund in its balancing segment
1459 -- and this anticipated account in Natural account segment
1460 -- assumption is federal would set up summary template for the anticpated account
1461
1462 l_fund_cur_id := DBMS_SQL.OPEN_CURSOR;
1463
1464 --Build the Select statement for getting the fund values and ccids
1465 l_fund_select := 'SELECT code_combination_id ' ||
1466 ' FROM gl_code_Combinations g, ' ||
1467 ' fv_fund_parameters f'||
1468 ' WHERE g.chart_of_accounts_id = :p_coaid '||
1469 ' AND g.'||l_ledger_info.balancing_seg_name || ' = f.fund_value '||
1470 ' AND f.treasury_symbol_id = :p_treasury_symbol_id '||
1471 ' AND f.set_of_books_id = :p_ledger_id '||
1472 ' AND g.template_id = :p_template_id '||
1473 ' AND g.summary_flag = ''Y''' ;
1474
1475 -------------------------------------------------------------------------
1476 trace(C_STATE_LEVEL, l_procedure_name, 'l_fund_select='||l_fund_select);
1477 -------------------------------------------------------------------------
1478
1479 -------------------------------------------------------------------------
1480 trace(C_STATE_LEVEL, l_procedure_name, 'parse');
1481 -------------------------------------------------------------------------
1482 DBMS_SQL.PARSE(l_fund_cur_id, l_fund_select, DBMS_SQL.Native);
1483 DBMS_SQL.BIND_VARIABLE(l_fund_cur_id, ':p_coaid', l_ledger_info.coaid);
1484 DBMS_SQL.BIND_VARIABLE(l_fund_cur_id, ':p_treasury_symbol_id', p_treasury_symbol_id);
1485 DBMS_SQL.BIND_VARIABLE(l_fund_cur_id, ':p_ledger_id', p_ledger_id);
1486 DBMS_SQL.BIND_VARIABLE(l_fund_cur_id, ':p_template_id', l_template_id);
1487
1488 -------------------------------------------------------------------------
1489 trace(C_STATE_LEVEL, l_procedure_name, 'DEFINE_COLUMN');
1490 -------------------------------------------------------------------------
1491 DBMS_SQL.DEFINE_COLUMN(l_fund_cur_id,1,l_ccid);
1492
1493 l_fund_ret := DBMS_SQL.EXECUTE(l_fund_cur_id);
1494
1495 LOOP
1496 -- Fetch the ccid's from Gl_Code_Combinations
1497 trace(C_STATE_LEVEL, l_procedure_name, 'FETCH_ROWS');
1498 IF DBMS_SQL.FETCH_ROWS(l_fund_cur_id) = 0 THEN
1499 trace(C_STATE_LEVEL, l_procedure_name, 'EXIT');
1500 EXIT;
1501 ELSE
1502 trace(C_STATE_LEVEL, l_procedure_name, 'COLUMN_VALUE');
1503 DBMS_SQL.COLUMN_VALUE(l_fund_cur_id, 1,l_ccid);
1504 END IF;
1505
1506 trace(C_PROC_LEVEL, l_procedure_name, 'Before calling calc_funds');
1507 trace(C_STATE_LEVEL, l_procedure_name, 'l_ccid='||l_ccid);
1508 trace(C_STATE_LEVEL, l_procedure_name, 'l_template_id='||l_template_id);
1509 trace(C_STATE_LEVEL, l_procedure_name, 'p_ledger_id='||p_ledger_id);
1510 trace(C_STATE_LEVEL, l_procedure_name, 'l_period_name='||l_period_name);
1511
1512 SELECT SUM((begin_balance_dr - begin_balance_cr) +
1513 (period_net_dr - period_net_cr))
1514 INTO l_amount
1515 FROM gl_balances
1516 WHERE ledger_id = p_Ledger_id
1517 AND currency_code = l_ledger_info.currency_code
1518 AND code_combination_id = l_ccid
1519 AND period_name = l_period_name;
1520
1521 trace(C_STATE_LEVEL, l_procedure_name, ' gl_balances l_amount='||l_amount);
1522
1523 l_tot_amount := l_tot_amount + NVL(l_amount, 0);
1524
1525 trace(C_STATE_LEVEL, l_procedure_name, ' gl_balances l_tot_amount='||l_tot_amount);
1526
1527 SELECT SUM(NVL(accounted_dr,0) - NVL(accounted_cr,0))
1528 INTO l_amount
1529 FROM gl_bc_packets gbc,
1530 gl_account_hierarchies gah
1531 WHERE gbc.ledger_id = p_Ledger_id
1532 AND gah.ledger_id = p_Ledger_id
1533 AND gah.template_id = l_template_id
1534 AND gah.summary_code_combination_id = l_ccid
1535 AND gbc.currency_code = l_ledger_info.currency_code
1536 AND gbc.code_combination_id = gah.detail_code_combination_id
1537 AND gbc.period_year = l_period_year
1538 AND gbc.period_num <= l_period_num
1539 AND gbc.status_code = 'A';
1540
1541 trace(C_STATE_LEVEL, l_procedure_name, ' gl_bc_packets l_amount='||l_amount);
1542
1543 l_tot_amount := l_tot_amount + NVL(l_amount, 0);
1544
1545 trace(C_STATE_LEVEL, l_procedure_name, ' gl_bc_packets l_tot_amount='||l_tot_amount);
1546 END LOOP;
1547 dbms_sql.close_cursor (l_fund_cur_id);
1548 END IF;
1549
1550 p_anticipated_amt := l_tot_amount;
1551 trace(C_PROC_LEVEL, l_procedure_name, 'p_anticipated_amt='||p_anticipated_amt);
1552 trace(C_PROC_LEVEL, l_procedure_name, 'END');
1553
1554 EXCEPTION
1555 WHEN OTHERS THEN
1556 p_error_code := c_FAILURE;
1557 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1558 fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
1559 p_error_desc := fnd_message.get;
1560 stack_error (l_procedure_name, 'FINAL', p_error_desc);
1561 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:FINAL:'||p_error_desc);
1562 END get_anticipated_ts_amt;
1563
1564 PROCEDURE po_pya_processor
1565 (
1566 p_application_id IN NUMBER,
1567 p_event_rec IN xla_events_gt%ROWTYPE,
1568 p_extract_rec IN OUT NOCOPY fv_extract_detail_gt%ROWTYPE,
1569 p_error_code OUT NOCOPY NUMBER,
1570 p_error_desc OUT NOCOPY VARCHAR2
1571 )
1572 IS
1573 l_procedure_name VARCHAR2(100) :='.po_pya_processor';
1574 l_po_bc_rec po_bc_distributions%ROWTYPE;
1575 BEGIN
1576 l_procedure_name := g_path_name || l_procedure_name;
1577 p_error_code := c_SUCCESS;
1578 p_error_desc := NULL;
1579 trace(C_STATE_LEVEL, l_procedure_name, 'BEGIN');
1580
1581 SELECT *
1582 INTO l_po_bc_rec
1583 FROM po_bc_distributions pbd
1584 WHERE pbd.ae_event_id = p_extract_rec.event_id
1585 AND pbd.line_number = p_extract_rec.line_number;
1586
1587 trace(C_STATE_LEVEL, l_procedure_name, 'event_type_code='||p_event_rec.event_type_code);
1588 IF (p_event_rec.event_type_code IN ('PO_PA_UNRESERVED', 'RELEASE_UNRESERVED'))THEN
1589 NULL;
1590 ELSIF (p_event_rec.event_type_code IN ('PO_PA_CANCELLED',
1591 'PO_PA_CR_MEMO_CANCELLED',
1592 'PO_PA_FINAL_CLOSED',
1593 'RELEASE_CANCELLED',
1594 'RELEASE_CR_MEMO_CANCELLED',
1595 'RELEASE_FINAL_CLOSED'))THEN
1596 trace(C_STATE_LEVEL, l_procedure_name, 'Before Setting Sources');
1597 trace(C_STATE_LEVEL, l_procedure_name, 'ent_unpaid_obl_amount='||p_extract_rec.ent_unpaid_obl_amount);
1598 trace(C_STATE_LEVEL, l_procedure_name, 'acc_unpaid_obl_amount='||p_extract_rec.acc_unpaid_obl_amount);
1599 trace(C_STATE_LEVEL, l_procedure_name, 'adjustment_type='||p_extract_rec.adjustment_type);
1600 p_extract_rec.ent_unpaid_obl_pya_amount := p_extract_rec.ent_unpaid_obl_amount;
1601 p_extract_rec.acc_unpaid_obl_pya_amount := p_extract_rec.acc_unpaid_obl_amount;
1602 p_extract_rec.ent_unpaid_obl_amount := 0;
1603 p_extract_rec.acc_unpaid_obl_amount := 0;
1604 p_extract_rec.adjustment_type := 'Downward';
1605 trace(C_STATE_LEVEL, l_procedure_name, 'After Setting Sources');
1606 trace(C_STATE_LEVEL, l_procedure_name, 'ent_unpaid_obl_pya_amount='||p_extract_rec.ent_unpaid_obl_pya_amount);
1607 trace(C_STATE_LEVEL, l_procedure_name, 'acc_unpaid_obl_pya_amount='||p_extract_rec.acc_unpaid_obl_pya_amount);
1608 trace(C_STATE_LEVEL, l_procedure_name, 'ent_unpaid_obl_amount='||p_extract_rec.ent_unpaid_obl_amount);
1609 trace(C_STATE_LEVEL, l_procedure_name, 'acc_unpaid_obl_amount='||p_extract_rec.acc_unpaid_obl_amount);
1610 trace(C_STATE_LEVEL, l_procedure_name, 'adjustment_type='||p_extract_rec.adjustment_type);
1611 ELSE
1612 trace(C_STATE_LEVEL, l_procedure_name, 'Before Setting Sources');
1613 trace(C_STATE_LEVEL, l_procedure_name, 'ent_unpaid_obl_amount='||p_extract_rec.ent_unpaid_obl_amount);
1614 trace(C_STATE_LEVEL, l_procedure_name, 'acc_unpaid_obl_amount='||p_extract_rec.acc_unpaid_obl_amount);
1615 trace(C_STATE_LEVEL, l_procedure_name, 'ent_unreserved_budget_amount='||p_extract_rec.ent_unreserved_budget_amount);
1616 trace(C_STATE_LEVEL, l_procedure_name, 'acc_unreserved_budget_amount='||p_extract_rec.acc_unreserved_budget_amount);
1617 p_extract_rec.ent_unpaid_obl_amount := p_extract_rec.ent_unpaid_obl_amount - p_extract_rec.ent_unreserved_budget_amount;
1618 p_extract_rec.acc_unpaid_obl_amount := p_extract_rec.acc_unpaid_obl_amount - p_extract_rec.acc_unreserved_budget_amount;
1619 p_extract_rec.ent_unpaid_obl_pya_amount := p_extract_rec.ent_unpaid_obl_amount;
1620 p_extract_rec.acc_unpaid_obl_pya_amount := p_extract_rec.acc_unpaid_obl_amount;
1621 p_extract_rec.ent_unpaid_obl_amount := 0;
1622 p_extract_rec.acc_unpaid_obl_amount := 0;
1623 trace(C_STATE_LEVEL, l_procedure_name, 'After Setting Sources');
1624 trace(C_STATE_LEVEL, l_procedure_name, 'ent_unpaid_obl_pya_amount='||p_extract_rec.ent_unpaid_obl_pya_amount);
1625 trace(C_STATE_LEVEL, l_procedure_name, 'acc_unpaid_obl_pya_amount='||p_extract_rec.acc_unpaid_obl_pya_amount);
1626 trace(C_STATE_LEVEL, l_procedure_name, 'ent_unpaid_obl_amount='||p_extract_rec.ent_unpaid_obl_amount);
1627 trace(C_STATE_LEVEL, l_procedure_name, 'acc_unpaid_obl_amount='||p_extract_rec.acc_unpaid_obl_amount);
1628 END IF;
1629
1630 trace(C_PROC_LEVEL, l_procedure_name, 'END');
1631 EXCEPTION
1632 WHEN OTHERS THEN
1633 p_error_code := c_FAILURE;
1634 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1635 fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
1636 p_error_desc := fnd_message.get;
1637 stack_error (l_procedure_name, 'FINAL', p_error_desc);
1638 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:FINAL:'||p_error_desc);
1639 END;
1640
1641 PROCEDURE ap_pya_processor
1642 (
1643 p_application_id IN NUMBER,
1644 p_event_rec IN xla_events_gt%ROWTYPE,
1645 p_extract_rec IN OUT NOCOPY fv_extract_detail_gt%ROWTYPE,
1646 p_error_code OUT NOCOPY NUMBER,
1647 p_error_desc OUT NOCOPY VARCHAR2
1648 )
1649 IS
1650 l_procedure_name VARCHAR2(100) :='.ap_pya_processor';
1651 l_po_bc_rec po_bc_distributions%ROWTYPE;
1652 BEGIN
1653 l_procedure_name := g_path_name || l_procedure_name;
1654 p_error_code := c_SUCCESS;
1655 p_error_desc := NULL;
1656 trace(C_STATE_LEVEL, l_procedure_name, 'BEGIN');
1657
1658 IF (p_event_rec.entity_code = 'AP_INVOICES') THEN
1659
1660 IF (p_extract_rec.po_distribution_id IS NULL) THEN
1661 -- modified by ks for 12430639 changed ent_
1662 p_extract_rec.acc_unpaid_exp_pya_amount := -1*NVL(p_extract_rec.acc_unanticipated_bud_amount,0);
1663 p_extract_rec.ent_unpaid_exp_pya_amount := -1*NVL(p_extract_rec.ent_unanticipated_bud_amount,0);
1664 p_extract_rec.ent_unpaid_exp_amount := p_extract_rec.ent_unpaid_exp_amount - p_extract_rec.ent_unpaid_exp_pya_amount;
1665 p_extract_rec.acc_unpaid_exp_amount := p_extract_rec.acc_unpaid_exp_amount - p_extract_rec.acc_unpaid_exp_pya_amount;
1666 ELSE
1667 p_extract_rec.acc_unpaid_obl_pya_amount := -1*NVL(p_extract_rec.acc_unanticipated_bud_amount,0);
1668 p_extract_rec.ent_unpaid_obl_pya_amount := -1*NVL(p_extract_rec.ent_unanticipated_bud_amount,0);
1669 p_extract_rec.ent_unpaid_obl_amount := p_extract_rec.ent_unpaid_obl_amount + NVL(p_extract_rec.ent_unanticipated_bud_amount,0);
1670 p_extract_rec.acc_unpaid_obl_amount := p_extract_rec.acc_unpaid_obl_amount + NVL(p_extract_rec.acc_unanticipated_bud_amount,0);
1671 END IF;
1672 ELSIF (p_event_rec.entity_code = 'AP_PAYMENTS') THEN
1673 p_extract_rec.acc_unpaid_exp_pya_amount := -1*NVL(p_extract_rec.acc_unanticipated_bud_amount,0);
1674 p_extract_rec.ent_unpaid_exp_pya_amount := -1*NVL(p_extract_rec.ent_unanticipated_bud_amount,0);
1675 p_extract_rec.ent_unpaid_exp_amount := NVL(p_extract_rec.ent_unpaid_exp_amount, 0) - p_extract_rec.ent_unpaid_exp_pya_amount;
1676 p_extract_rec.acc_unpaid_exp_amount := NVL(p_extract_rec.acc_unpaid_exp_amount, 0) - p_extract_rec.acc_unpaid_exp_pya_amount;
1677 END IF;
1678
1679 trace(C_STATE_LEVEL, l_procedure_name, 'event_type_code='||p_event_rec.event_type_code);
1680
1681 trace(C_PROC_LEVEL, l_procedure_name, 'END');
1682 EXCEPTION
1683 WHEN OTHERS THEN
1684 p_error_code := c_FAILURE;
1685 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1686 fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
1687 p_error_desc := fnd_message.get;
1688 stack_error (l_procedure_name, 'FINAL', p_error_desc);
1689 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:FINAL:'||p_error_desc);
1690 END;
1691
1692 PROCEDURE pya_processor
1693 (
1694 p_application_id IN NUMBER,
1695 p_error_code OUT NOCOPY NUMBER,
1696 p_error_desc OUT NOCOPY VARCHAR2
1697 )
1698 IS
1699 l_procedure_name VARCHAR2(100) :='.pya_processor';
1700 l_tot_acc_unant_bud_amount NUMBER;
1701 l_tot_acc_charge_amount NUMBER;
1702 l_tot_acc_unreserve_bud_amount NUMBER;
1703 l_adjustment_type fv_extract_detail_gt.adjustment_type%TYPE;
1704 l_exchange_rate NUMBER;
1705 l_tot_acc_anticipated_amt NUMBER;
1706 l_tot_acc_commitment_amt NUMBER;
1707 l_curr_ts_id NUMBER := -1;
1708 l_total_pya_amount NUMBER;
1709 BEGIN
1710 l_procedure_name := g_path_name || l_procedure_name;
1711 p_error_code := c_SUCCESS;
1712 p_error_desc := NULL;
1713 trace(C_STATE_LEVEL, l_procedure_name, 'BEGIN');
1714
1715 ----------------------------
1716 -- First process by event --
1717 ----------------------------
1718 FOR event_rec IN (SELECT *
1719 FROM xla_events_gt
1720 WHERE application_id = p_application_id) LOOP
1721 g_CurrentEventId := event_rec.event_id; --For Error Handling
1722 trace(C_STATE_LEVEL, l_procedure_name, 'Processing Event:'||event_rec.event_id);
1723 trace(C_STATE_LEVEL, l_procedure_name, 'Line Number:'||event_rec.line_number);
1724 trace(C_STATE_LEVEL, l_procedure_name, 'Event Type:'||event_rec.entity_code);
1725 ------------------------------
1726 -- Group by Treasury Symbol --
1727 ------------------------------
1728 --Bug#16478406 Added logic to process invoices that are po matched and non matched seperately
1729 FOR ts_rec IN (SELECT distinct treasury_symbol_id treasury_symbol_id,
1730 DECODE(f.application_id, 200, DECODE (f.po_distribution_id, NULL, -1, -2), -2) po_match_type
1731 FROM fv_extract_detail_gt f
1732 WHERE f.event_id = event_rec.event_id
1733 AND f.prior_year_flag = 'Y') LOOP
1734 trace(C_STATE_LEVEL, l_procedure_name, 'Processing TS:'||ts_rec.treasury_symbol_id);
1735
1736 IF (event_rec.application_id = 201 AND event_rec.event_type_code IN ('PO_PA_UNRESERVED', 'RELEASE_UNRESERVED'))THEN
1737 /* No Upward Downward for Unreeserve*/
1738 NULL;
1739 ELSE
1740 IF (l_curr_ts_id <> ts_rec.treasury_symbol_id) THEN
1741 --
1742 -- For each TS get the anticipated amount
1743 --
1744 trace(C_STATE_LEVEL, l_procedure_name, 'Calling get_anticipated_ts_amt');
1745 trace(C_STATE_LEVEL, l_procedure_name, 'p_ledger_id='||event_rec.ledger_id);
1746 trace(C_STATE_LEVEL, l_procedure_name, 'p_gl_date='||event_rec.event_date);
1747 trace(C_STATE_LEVEL, l_procedure_name, 'p_treasury_symbol_id='||ts_rec.treasury_symbol_id);
1748 get_anticipated_ts_amt
1749 (
1750 p_ledger_id => event_rec.ledger_id,
1751 p_gl_date => event_rec.event_date,
1752 p_treasury_symbol_id => ts_rec.treasury_symbol_id,
1753 p_anticipated_amt => l_tot_acc_anticipated_amt,
1754 p_error_code => p_error_code,
1755 p_error_desc => p_error_desc
1756 );
1757 trace(C_STATE_LEVEL, l_procedure_name, 'Finished Calling get_anticipated_ts_amt');
1758 trace(C_STATE_LEVEL, l_procedure_name, 'p_error_code='||p_error_code);
1759 trace(C_STATE_LEVEL, l_procedure_name, 'p_error_desc='||p_error_desc);
1760 trace(C_STATE_LEVEL, l_procedure_name, 'l_tot_acc_anticipated_amt='||l_tot_acc_anticipated_amt);
1761 END IF;
1762 END IF;
1763 l_curr_ts_id := ts_rec.treasury_symbol_id;
1764
1765 IF (p_error_code = c_SUCCESS) THEN
1766 l_tot_acc_unant_bud_amount := 0;
1767 l_tot_acc_charge_amount := 0;
1768 l_tot_acc_unreserve_bud_amount := 0;
1769 l_tot_acc_commitment_amt := 0;
1770 ------------------------------------------------
1771 -- Get total of PYA Amount by Treasury Symbol --
1772 ------------------------------------------------
1773 BEGIN
1774 SELECT SUM(NVL(f.acc_unanticipated_bud_amount, 0)),
1775 SUM(NVL(f.acc_charge_amount, 0)),
1776 SUM(NVL(f.acc_unreserved_budget_amount, 0)),
1777 SUM(NVL(f.acc_commitment_amount, 0))
1778 INTO l_tot_acc_unant_bud_amount,
1779 l_tot_acc_charge_amount,
1780 l_tot_acc_unreserve_bud_amount,
1781 l_tot_acc_commitment_amt
1782 FROM fv_extract_detail_gt f
1783 WHERE f.event_id = event_rec.event_id
1784 AND f.treasury_symbol_id = ts_rec.treasury_symbol_id
1785 AND DECODE(f.application_id, 200, DECODE (f.po_distribution_id, NULL, -1, -2), -2) = ts_rec.po_match_type
1786 AND f.prior_year_flag = 'Y';
1787 trace(C_STATE_LEVEL, l_procedure_name, 'l_tot_acc_unant_bud_amount:'||l_tot_acc_unant_bud_amount);
1788 trace(C_STATE_LEVEL, l_procedure_name, 'l_tot_acc_charge_amount:'||l_tot_acc_charge_amount);
1789 trace(C_STATE_LEVEL, l_procedure_name, 'l_tot_acc_unreserve_bud_amount:'||l_tot_acc_unreserve_bud_amount);
1790 trace(C_STATE_LEVEL, l_procedure_name, 'l_tot_acc_commitment_amt:'||l_tot_acc_unreserve_bud_amount);
1791 EXCEPTION
1792 WHEN OTHERS THEN
1793 p_error_code := c_FAILURE;
1794 fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
1795 p_error_desc := fnd_message.get;
1796 stack_error (l_procedure_name, 'SELECT_fv_extract_detail_gt', p_error_desc);
1797 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:SELECT_fv_extract_detail_gt:'||p_error_desc);
1798 END;
1799 END IF;
1800
1801 IF (p_error_code = c_SUCCESS) THEN
1802 IF (event_rec.application_id = 201 AND l_tot_acc_unreserve_bud_amount <> 0) THEN
1803 IF ((l_tot_acc_charge_amount + l_tot_acc_unreserve_bud_amount) < 0) THEN
1804 l_adjustment_type := 'Upward';
1805 ELSIF ((l_tot_acc_charge_amount + l_tot_acc_unreserve_bud_amount) > 0) THEN
1806 l_adjustment_type := 'Downward';
1807 ELSE
1808 l_adjustment_type := '';
1809 END IF;
1810 ELSE
1811 IF (l_tot_acc_unant_bud_amount+l_tot_acc_commitment_amt < 0) THEN
1812 l_adjustment_type := 'Upward';
1813 ELSIF (l_tot_acc_unant_bud_amount+l_tot_acc_commitment_amt > 0) THEN
1814 l_adjustment_type := 'Downward';
1815 ELSE
1816 l_adjustment_type := '';
1817 END IF;
1818 END IF;
1819 trace(C_STATE_LEVEL, l_procedure_name, 'l_adjustment_type:'||l_adjustment_type);
1820 END IF;
1821 /* Bug 13832228
1822 IF (p_error_code = c_SUCCESS) THEN
1823 -- Move all Commitments to Unanticipated Budget
1824 BEGIN
1825 trace(C_STATE_LEVEL, l_procedure_name, 'Moving Commitments to Unanticipated Budget');
1826 UPDATE fv_extract_detail_gt f
1827 SET f.acc_unanticipated_bud_amount = NVL(f.acc_unanticipated_bud_amount, 0) + NVL(f.acc_commitment_amount, 0),
1828 f.ent_unanticipated_bud_amount = NVL(f.ent_unanticipated_bud_amount, 0) + NVL(f.ent_commitment_amount, 0),
1829 f.acc_commitment_amount = 0,
1830 f.ent_commitment_amount = 0
1831 WHERE f.event_id = event_rec.event_id
1832 AND f.treasury_symbol_id = ts_rec.treasury_symbol_id
1833 AND f.prior_year_flag = 'Y';
1834 trace(C_STATE_LEVEL, l_procedure_name, 'Updated'||SQL%ROWCOUNT||' rows.');
1835 EXCEPTION
1836 WHEN OTHERS THEN
1837 p_error_code := c_FAILURE;
1838 fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
1839 p_error_desc := fnd_message.get;
1840 stack_error (l_procedure_name, 'UPDATE_fv_extract_detail_gt', p_error_desc);
1841 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:UPDATE_fv_extract_detail_gt:'||p_error_desc);
1842 END;
1843 END IF;
1844 */
1845
1846 IF (p_error_code = c_SUCCESS) THEN
1847 FOR pya_rec IN (SELECT *
1848 FROM fv_extract_detail_gt f
1849 WHERE f.event_id = event_rec.event_id
1850 AND f.treasury_symbol_id = ts_rec.treasury_symbol_id
1851 AND DECODE(f.application_id, 200, DECODE (f.po_distribution_id, NULL, -1, -2), -2) = ts_rec.po_match_type
1852 AND f.prior_year_flag = 'Y') LOOP
1853 trace(C_STATE_LEVEL, l_procedure_name, 'Processing PYA');
1854 pya_rec.adjustment_type := l_adjustment_type;
1855 IF (event_rec.application_id = 200) THEN
1856 trace(C_STATE_LEVEL, l_procedure_name, 'Payables Processing');
1857 ap_pya_processor
1858 (
1859 p_application_id => p_application_id,
1860 p_event_rec => event_rec,
1861 p_extract_rec => pya_rec,
1862 p_error_code => p_error_code,
1863 p_error_desc => p_error_desc
1864 );
1865 ELSIF (event_rec.application_id = 8901) THEN
1866 trace(C_STATE_LEVEL, l_procedure_name, 'Federal Processing');
1867 IF (event_rec.entity_code = 'TREASURY_CONFIRMATION') THEN
1868 pya_rec.ent_unpaid_exp_pya_amount := -1*NVL(pya_rec.acc_unanticipated_bud_amount,0);
1869 pya_rec.acc_unpaid_exp_pya_amount := -1*NVL(pya_rec.ent_unanticipated_bud_amount,0);
1870 END IF;
1871 ELSIF (event_rec.application_id = 201) THEN
1872 trace(C_STATE_LEVEL, l_procedure_name, 'Purchasing Processing');
1873 po_pya_processor
1874 (
1875 p_application_id => p_application_id,
1876 p_event_rec => event_rec,
1877 p_extract_rec => pya_rec,
1878 p_error_code => p_error_code,
1879 p_error_desc => p_error_desc
1880 );
1881 END IF;
1882
1883 IF (l_tot_acc_anticipated_amt > 0 AND
1884 pya_rec.adjustment_type = 'Downward' AND
1885 pya_rec.fund_expired_status = 'Unexpired') THEN
1886 trace(C_STATE_LEVEL, l_procedure_name, 'There is Anticipated Balance. So we have to use that.');
1887 l_exchange_rate := 1;
1888 l_total_pya_amount := -1*(pya_rec.acc_unpaid_obl_pya_amount + pya_rec.acc_unpaid_exp_pya_amount);
1889 IF (pya_rec.ent_unanticipated_bud_amount <> 0) THEN
1890 l_exchange_rate := pya_rec.acc_unanticipated_bud_amount/pya_rec.ent_unanticipated_bud_amount;
1891 END IF;
1892 IF (l_tot_acc_anticipated_amt >= l_total_pya_amount) THEN
1893 trace(C_STATE_LEVEL, l_procedure_name, 'Anticipated Greather than Unanticipated.');
1894 l_tot_acc_anticipated_amt := l_tot_acc_anticipated_amt - l_total_pya_amount;
1895 pya_rec.acc_anticipated_budget_amount := -1*l_total_pya_amount;
1896 pya_rec.ent_anticipated_budget_amount := -1*l_total_pya_amount/l_exchange_rate;
1897 ELSE
1898 trace(C_STATE_LEVEL, l_procedure_name, 'Anticipated Less than Unanticipated.');
1899 pya_rec.acc_anticipated_budget_amount := -1*l_tot_acc_anticipated_amt;
1900 pya_rec.ent_anticipated_budget_amount := -1*l_tot_acc_anticipated_amt/l_exchange_rate;
1901 l_tot_acc_anticipated_amt := 0;
1902 END IF;
1903 pya_rec.acc_unanticipated_bud_amount := pya_rec.acc_unanticipated_bud_amount + pya_rec.acc_anticipated_budget_amount;
1904 pya_rec.ent_unanticipated_bud_amount := pya_rec.ent_unanticipated_bud_amount + pya_rec.ent_anticipated_budget_amount;
1905 END IF;
1906
1907 trace(C_STATE_LEVEL, l_procedure_name, 'acc_unreserved_budget_amount='||pya_rec.acc_unreserved_budget_amount);
1908 trace(C_STATE_LEVEL, l_procedure_name, 'ent_unreserved_budget_amount='||pya_rec.ent_unreserved_budget_amount);
1909 trace(C_STATE_LEVEL, l_procedure_name, 'acc_anticipated_budget_amount='||pya_rec.acc_anticipated_budget_amount);
1910 trace(C_STATE_LEVEL, l_procedure_name, 'ent_anticipated_budget_amount='||pya_rec.ent_anticipated_budget_amount);
1911 trace(C_STATE_LEVEL, l_procedure_name, 'acc_unanticipated_bud_amount='||pya_rec.acc_unanticipated_bud_amount);
1912 trace(C_STATE_LEVEL, l_procedure_name, 'ent_unanticipated_bud_amount='||pya_rec.ent_unanticipated_bud_amount);
1913 trace(C_STATE_LEVEL, l_procedure_name, 'acc_unpaid_obl_pya_amount='||pya_rec.acc_unpaid_obl_pya_amount);
1914 trace(C_STATE_LEVEL, l_procedure_name, 'ent_unpaid_obl_pya_amount='||pya_rec.ent_unpaid_obl_pya_amount);
1915 trace(C_STATE_LEVEL, l_procedure_name, 'acc_unpaid_obl_amount='||pya_rec.acc_unpaid_obl_amount);
1916 trace(C_STATE_LEVEL, l_procedure_name, 'ent_unpaid_obl_amount='||pya_rec.ent_unpaid_obl_amount);
1917 trace(C_STATE_LEVEL, l_procedure_name, 'ent_unpaid_exp_pya_amount='||pya_rec.ent_unpaid_exp_pya_amount);
1918 trace(C_STATE_LEVEL, l_procedure_name, 'acc_unpaid_exp_pya_amount='||pya_rec.acc_unpaid_exp_pya_amount);
1919 trace(C_STATE_LEVEL, l_procedure_name, 'acc_unpaid_exp_amount='||pya_rec.acc_unpaid_exp_amount);
1920 trace(C_STATE_LEVEL, l_procedure_name, 'ent_unpaid_exp_amount='||pya_rec.ent_unpaid_exp_amount);
1921 trace(C_STATE_LEVEL, l_procedure_name, 'acc_paid_exp_amount='||pya_rec.acc_paid_exp_amount);
1922 trace(C_STATE_LEVEL, l_procedure_name, 'ent_paid_exp_amount='||pya_rec.ent_paid_exp_amount);
1923 trace(C_STATE_LEVEL, l_procedure_name, 'adjustment_type='||pya_rec.adjustment_type);
1924
1925 BEGIN
1926 UPDATE fv_extract_detail_gt f
1927 SET adjustment_type = pya_rec.adjustment_type,
1928 acc_anticipated_budget_amount = pya_rec.acc_anticipated_budget_amount,
1929 ent_anticipated_budget_amount = pya_rec.ent_anticipated_budget_amount,
1930 acc_unanticipated_bud_amount = pya_rec.acc_unanticipated_bud_amount,
1931 ent_unanticipated_bud_amount = pya_rec.ent_unanticipated_bud_amount,
1932 acc_unpaid_obl_pya_amount = pya_rec.acc_unpaid_obl_pya_amount,
1933 ent_unpaid_obl_pya_amount = pya_rec.ent_unpaid_obl_pya_amount,
1934 acc_unpaid_obl_amount = pya_rec.acc_unpaid_obl_amount,
1935 ent_unpaid_obl_amount = pya_rec.ent_unpaid_obl_amount,
1936 acc_unpaid_exp_pya_amount = pya_rec.acc_unpaid_exp_pya_amount,
1937 ent_unpaid_exp_pya_amount = pya_rec.ent_unpaid_exp_pya_amount,
1938 acc_paid_exp_amount = pya_rec.acc_paid_exp_amount,
1939 ent_paid_exp_amount = pya_rec.ent_paid_exp_amount,
1940 ent_unpaid_exp_amount = pya_rec.ent_unpaid_exp_amount,
1941 acc_unpaid_exp_amount = pya_rec.acc_unpaid_exp_amount,
1942 acc_commitment_amount = pya_rec.acc_commitment_amount,
1943 ent_commitment_amount = pya_rec.ent_commitment_amount
1944 WHERE f.event_id = pya_rec.event_id
1945 AND f.line_number = pya_rec.line_number
1946 AND f.treasury_symbol_id = ts_rec.treasury_symbol_id;
1947 EXCEPTION
1948 WHEN OTHERS THEN
1949 p_error_code := c_FAILURE;
1950 fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
1951 p_error_desc := fnd_message.get;
1952 stack_error (l_procedure_name, 'UPDATE_fv_extract_detail_gt', p_error_desc);
1953 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:UPDATE_fv_extract_detail_gt:'||p_error_desc);
1954 END;
1955
1956 trace(C_STATE_LEVEL, l_procedure_name, 'Updated'||SQL%ROWCOUNT||' rows.');
1957 IF (p_error_code <> c_SUCCESS) THEN
1958 EXIT;
1959 END IF;
1960 END LOOP;
1961 END IF;
1962 IF (p_error_code <> c_SUCCESS) THEN
1963 EXIT;
1964 END IF;
1965 END LOOP;
1966 IF (p_error_code <> c_SUCCESS) THEN
1967 EXIT;
1968 END IF;
1969 END LOOP;
1970
1971 trace(C_PROC_LEVEL, l_procedure_name, 'END');
1972 EXCEPTION
1973 WHEN OTHERS THEN
1974 p_error_code := c_FAILURE;
1975 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1976 fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
1977 p_error_desc := fnd_message.get;
1978 stack_error (l_procedure_name, 'FINAL', p_error_desc);
1979 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:FINAL:'||p_error_desc);
1980 END;
1981
1982 PROCEDURE check_errors
1983 (
1984 p_application_id IN NUMBER,
1985 p_error_code OUT NOCOPY NUMBER,
1986 p_error_desc OUT NOCOPY VARCHAR2
1987 )
1988 IS
1989 l_procedure_name VARCHAR2(100) :='.check_errors';
1990 l_tot_acc_unant_bud_amount NUMBER;
1991 l_tot_acc_charge_amount NUMBER;
1992 l_tot_acc_unreserve_bud_amount NUMBER;
1993 l_adjustment_type fv_extract_detail_gt.adjustment_type%TYPE;
1994 l_exchange_rate NUMBER;
1995 l_tot_acc_anticipated_amt NUMBER;
1996 BEGIN
1997 l_procedure_name := g_path_name || l_procedure_name;
1998 p_error_code := c_SUCCESS;
1999 p_error_desc := NULL;
2000 trace(C_STATE_LEVEL, l_procedure_name, 'BEGIN');
2001 /* Check for Expired Funds used in current Year*/
2002 FOR exp_funds_rec IN (SELECT *
2003 FROM fv_extract_detail_gt g
2004 WHERE g.fund_expired_status = 'Expired'
2005 AND g.prior_year_flag = 'N'
2006 AND NOT EXISTS (SELECT 1 --Bug14593920
2007 FROM fv_treasury_symbols t
2008 WHERE t.treasury_symbol_id = g.treasury_symbol_id
2009 AND NVL(t.no_pya_acct_flag, 'N') = 'Y')) LOOP
2010 g_CurrentEventId := exp_funds_rec.event_id;
2011 p_error_code := c_FAILURE;
2012 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
2013 fnd_message.set_token('MESSAGE' , 'Expired Fund '||exp_funds_rec.fund_value||' is used in current year.');
2014 p_error_desc := fnd_message.get;
2015 stack_error (l_procedure_name, 'FINAL', p_error_desc);
2016 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:exp_funds_rec:'||p_error_desc);
2017 END LOOP;
2018
2019
2020 trace(C_PROC_LEVEL, l_procedure_name, 'END');
2021 EXCEPTION
2022 WHEN OTHERS THEN
2023 p_error_code := c_FAILURE;
2024 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
2025 fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
2026 p_error_desc := fnd_message.get;
2027 stack_error (l_procedure_name, 'FINAL', p_error_desc);
2028 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:FINAL:'||p_error_desc);
2029 END;
2030
2031
2032
2033 PROCEDURE get_fed_non_fed_ind
2034 (
2035 p_cust_vend_id IN NUMBER,
2036 p_cust_or_vend IN VARCHAR2,
2037 p_fed_non_fed_ind OUT NOCOPY VARCHAR2,
2038 p_error_code OUT NOCOPY NUMBER,
2039 p_error_desc OUT NOCOPY VARCHAR2
2040 )
2041 IS
2042 l_procedure_name VARCHAR2(100) :='.get_fed_non_fed_ind';
2043 l_vendor_type VARCHAR2(100);
2044 BEGIN
2045 l_procedure_name := g_path_name || l_procedure_name;
2046 trace(C_STATE_LEVEL, l_procedure_name, 'BEGIN');
2047 trace(C_STATE_LEVEL, l_procedure_name, 'p_cust_vend_id='||p_cust_vend_id);
2048 trace(C_STATE_LEVEL, l_procedure_name, 'p_cust_or_vend='||p_cust_or_vend);
2049
2050 p_fed_non_fed_ind := 'N';
2051
2052 BEGIN
2053 IF (p_cust_or_vend = 'C') THEN
2054 SELECT hzca.customer_class_code
2055 INTO l_vendor_type
2056 FROM hz_cust_accounts hzca
2057 WHERE hzca.cust_account_id = p_cust_vend_id;
2058 ELSIF (p_cust_or_vend = 'V') THEN
2059 SELECT vendor_type_lookup_code
2060 INTO l_vendor_type
2061 FROM po_vendors
2062 WHERE vendor_id = p_cust_vend_id;
2063 END IF;
2064
2065 IF (l_vendor_type = 'FEDERAL') THEN
2066 p_fed_non_fed_ind := 'F';
2067 ELSE
2068 p_fed_non_fed_ind := 'N';
2069 END IF;
2070 EXCEPTION
2071 WHEN NO_DATA_FOUND THEN
2072 p_fed_non_fed_ind := 'N';
2073 END;
2074
2075 p_error_code := c_SUCCESS;
2076 p_error_desc := NULL;
2077 trace(C_PROC_LEVEL, l_procedure_name, 'END');
2078 EXCEPTION
2079 WHEN OTHERS THEN
2080 p_error_code := c_FAILURE;
2081 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
2082 fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
2083 p_error_desc := fnd_message.get;
2084 stack_error (l_procedure_name, 'FINAL', p_error_desc);
2085 trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:FINAL:'||p_error_desc);
2086 END;
2087
2088 BEGIN
2089 init;
2090 END fv_sla_utl_processing_pkg;