[Home] [Help]
PACKAGE BODY: APPS.FV_BE_FUND_PKG
Source
1 PACKAGE BODY Fv_Be_Fund_Pkg AS
2 --$Header: FVBEFDCB.pls 120.20 2006/07/13 09:50:10 ckappaga ship $
3
4 g_module_name VARCHAR2(100);
5 g_sob_id gl_ledgers.ledger_id%TYPE;
6 g_mode gl_bc_packets.status_code%TYPE;
7 g_errbuf VARCHAR2(1000);
8 g_retcode NUMBER := 0;
9 g_user_id NUMBER(15);
10 g_resp_id NUMBER(15);
11 g_log_msg VARCHAR2(1);
12 g_approver_id NUMBER(15);
13
14
15 PROCEDURE log_message(p_module VARCHAR2, p_message VARCHAR2);
16 PROCEDURE reset_doc_status (p_doc_id NUMBER);
17
18
19 /******************************************************************************
20 Procedure conc_main
21 This procedure is called from the Funds Reservation concurrent program which
22 in turn is called from the Enter Appropriation, Enter Fund Distribution and
23 BE Transaction Summary forms when workflow is turned off.
24 If the workflow is turned off then all documents except reprogramming
25 transactions will be submitted for Funds reservation through the concurrent
26 program
27 ******************************************************************************/
28
29 PROCEDURE conc_main (errbuf OUT NOCOPY VARCHAR2,
30 retcode OUT NOCOPY VARCHAR2,
31 p_mode VARCHAR2,
32 p_sob_id NUMBER,
33 p_approval_id NUMBER)
34 IS
35
36 l_module_name VARCHAR2(200) ;
37 l_doc_id NUMBER(15);
38 l_approver_id NUMBER(15);
39 l_return_status VARCHAR2(1);
40 l_status_code VARCHAR2(30);
41 l_doc_type VARCHAR2(30);
42 l_event_type VARCHAR2(30);
43
44 CURSOR fetch_doc_id IS
45 SELECT doc_id, transaction_date, budget_level_id, source
46 FROM fv_be_trx_hdrs
47 WHERE approval_id = p_approval_id
48 AND doc_status = DECODE(p_mode, 'C', doc_status, 'IP')
49 AND NVL(approved_by_user_id,g_user_id) = g_user_id;
50
51 BEGIN
52 l_module_name := g_module_name || 'conc_main';
53 IF (p_mode NOT IN ('C', 'R')) THEN
54 retcode := 2;
55 errbuf := 'Invalid Mode for Funds Checking or Reservation ';
56 Fv_Utility.Log_mesg(Fnd_Log.LEVEL_ERROR, l_module_name||
57 '.message1',errbuf);
58 RETURN;
59 END IF;
60 l_approver_id := Fnd_Global.user_id;
61 g_user_id := Fnd_Global.user_id;
62 g_resp_id := Fnd_Global.resp_id;
63 g_log_msg := 'Y';
64
65 FOR doc_rec IN fetch_doc_id LOOP
66
67 IF doc_rec.source = 'RPR' then
68
69 l_doc_type := 'BE_RPR_TRANSACTIONS';
70
71 IF doc_rec.budget_level_id = 1 THEN
72
73 l_event_type := 'RPR_BA_RESERVE';
74
75 ELSE
76
77 l_event_type := 'RPR_FD_RESERVE';
78
79 END IF;
80
81 ELSE
82
83 l_doc_type := 'BE_TRANSACTIONS';
84
85 IF doc_rec.budget_level_id = 1 THEN
86
87 l_event_type := 'BA_RESERVE';
88
89 ELSE
90
91 l_event_type := 'FD_RESERVE';
92
93 END IF;
94
95 END IF;
96
97 Main ( errbuf,
98 retcode,
99 p_mode,
100 p_sob_id ,
101 doc_rec.doc_id,
102 NULL,
103 l_approver_id,
104 l_doc_type,
105 l_event_type,
106 doc_rec.transaction_date,
107 l_return_status,
108 l_status_code,
109 g_user_id,
110 g_resp_id);
111
112 END LOOP;
113
114 EXCEPTION
115 WHEN OTHERS THEN
116 retcode := 2;
117 errbuf := SQLERRM;
118 Fv_Utility.Log_mesg(Fnd_Log.LEVEL_UNEXPECTED, l_module_name||
119 '.final_exception',errbuf);
120 RAISE;
121 END conc_main;
122
123 /******************************************************************************
124 Procedure main
125 This procedure is called from the workflow process for reserving funds for
126 each document. It is also called from the Reprogramming Document Creation
127 and Approval process for reserving the RPR from and to documents in pair.
128 It is also called for checking funds from the Enter Appropriations and
129 Enter Fund distributions form when Funds Check button is pressed.
130 ******************************************************************************/
131
132 PROCEDURE main (errbuf OUT NOCOPY VARCHAR2,
133 retcode OUT NOCOPY VARCHAR2,
134 p_mode VARCHAR2,
135 p_sob_id NUMBER,
136 p_doc_id NUMBER,
137 p_rpr_to_doc_id NUMBER,
138 p_approver_id NUMBER,
139 p_doc_type VARCHAR2,
140 p_event_type VARCHAR2,
141 p_accounting_date DATE,
142 p_return_status OUT NOCOPY VARCHAR2,
143 p_status_code OUT NOCOPY VARCHAR2,
144 p_user_id NUMBER,
145 p_resp_id NUMBER)
146 IS
147
148 l_module_name VARCHAR2(200);
149 BEGIN
150 l_module_name := g_module_name || 'main';
151 retcode := 0;
152 g_retcode := 0;
153 g_sob_id := p_sob_id;
154
155 --Check if procedure is called from concurrent request
156 IF (g_log_msg <> 'Y') THEN
157 g_log_msg := 'N';
158 END IF;
159
160 log_message(l_module_name, 'Start of Funds Reservation Main Process ');
161 log_message(l_module_name, 'p_mode is ' ||p_mode);
162 log_message(l_module_name, 'p_doc_id is ' ||TO_CHAR(p_doc_id));
163 log_message(l_module_name, 'p_sob_id is ' ||TO_CHAR(p_sob_id));
164 log_message(l_module_name, 'p_rpr_to_doc_id is '|| p_rpr_to_doc_id);
165 log_message(l_module_name, 'p_approver_id is ' || p_approver_id);
166 log_message(l_module_name, 'p_doc_type is ' || p_doc_type);
167 log_message(l_module_name, 'p_event_type is ' || p_event_type);
168 log_message(l_module_name, 'p_accounting_date is ' || p_accounting_date);
169
170 IF (p_mode NOT IN ('C', 'R')) THEN
171 retcode := 2;
172 errbuf := 'Invalid Mode for Funds Checking or Reservation ';
173 Fv_Utility.Log_mesg(Fnd_Log.LEVEL_ERROR, l_module_name||
174 '.message1',errbuf);
175 RETURN;
176 END IF;
177
178 g_mode := p_mode;
179 g_user_id := p_user_id;
180 g_resp_id := p_resp_id;
181 g_approver_id := p_approver_id;
182
183
184 process_document(p_doc_id,
185 p_doc_type,
186 p_event_type,
187 p_accounting_date,
188 p_return_status,
189 p_status_code,
190 'FV_BE_FUND_PKG.Main');
191
192 IF g_retcode = 2 THEN
193 reset_doc_status(p_doc_id);
194 END IF;
195
196 --Process To RPR document if provided
197
198 IF (p_rpr_to_doc_id IS NOT NULL) THEN
199
200 log_message(l_module_name, 'Inside if rpr_to_doc_id is Not Null');
201
202 IF g_retcode <> 0 THEN
203
204 delete_rpr_docs(p_doc_id,p_rpr_to_doc_id);
205 ELSE
206 IF (g_mode = 'R') THEN
207 update_doc_status(p_rpr_to_doc_id, g_retcode);
208 END IF;
209 END IF;
210
211 END IF;
212
213 errbuf := g_errbuf;
214 retcode := g_retcode;
215
216 log_message(l_module_name, 'End of Funds Reservation Main Process ');
217
218 COMMIT;
219
220 EXCEPTION WHEN OTHERS THEN
221 retcode := 2;
222 errbuf:= 'Error in main procedure. SQL Error is '||SQLERRM;
223 Fv_Utility.Log_mesg(Fnd_Log.LEVEL_UNEXPECTED, l_module_name||
224 '.final_exception',errbuf);
225
226
227 END; -- main
228
229
230 PROCEDURE process_document(p_doc_id NUMBER,
231 p_doc_type VARCHAR2,
232 p_event_type VARCHAR2,
233 p_accounting_date DATE,
234 x_return_status OUT NOCOPY VARCHAR2,
235 x_status_code OUT NOCOPY VARCHAR2,
236 p_calling_sequence VARCHAR2)
237
238 IS
239 l_module_name VARCHAR2(200);
240 l_doc_id NUMBER;
241
242 cursor c_doc_id is
243 select fvr.transaction_id
244 into l_doc_id
245 from fv_be_rpr_transactions fvr,
246 fv_be_trx_hdrs fvh
247 where substr(fvh.doc_number, 1, length(fvh.doc_number) -4) = fvr.doc_number
248 and fvh.doc_id = p_doc_id;
249
250 BEGIN
251 l_module_name := g_module_name || 'process_document';
252
253 IF (g_retcode <> 2) THEN
254
255 --if rpr transaction then pass transaction id instead of doc id else pass doc id
256 IF P_doc_type = 'BE_RPR_TRANSACTIONS' THEN
257
258 OPEN c_doc_id;
259 Fetch c_doc_id into l_doc_id;
260 IF C_Doc_Id%NOTFOUND THEN
261 l_doc_id := p_doc_id;
262 END IF;
263
264 CLOSE c_doc_id;
265
266 IF l_doc_id is null then
267
268 l_doc_id := p_doc_id;
269
270 END IF;
271 ELSE
272 l_doc_id := p_doc_id;
273
274 END IF;
275
276 -- call FV Budgetary Control API to do funds check/reservation
277
278 fv_be_xla_pkg.Budgetary_Control(
279 p_ledger_id => g_sob_id,
280 p_doc_id => l_doc_id,
281 p_doc_type => p_doc_type,
282 p_event_type => p_event_type,
283 p_accounting_date => p_accounting_date,
284 p_bc_mode => g_mode,
285 p_calling_sequence=> 'FV_BE_FUND_PKG.Main',
286 x_return_status => x_return_status,
287 x_status_code => x_status_code);
288
289
290 IF x_return_status = 'S' THEN
291
292 log_message(l_module_name,
293 'Funds Checker Program completed successfully '||
294 'with a status code of '||x_status_code);
295
296 SELECT
297 DECODE(x_status_code,'SUCCESS', 0,'ADVISORY',0,'FAIL', 1, 'RFAIL',1, 'PARTIAL', 2, 'FATAL',2,'XLA_ERROR',2)
298 INTO g_retcode
299 FROM dual;
300 ELSE
301 log_message(l_module_name, 'Funds Check Errored Out');
302 g_retcode := 2;
303 END IF;
304
305 log_message(l_module_name, 'g_errbuf is '||g_errbuf);
306
307 ELSE -- g_retcode = 2
308
309 Fv_Utility.Log_mesg(Fnd_Log.LEVEL_UNEXPECTED, l_module_name,
310 'Error mesg of Else ' ||
311 g_retcode || ' - ' || g_errbuf );
312 RETURN;
313
314 END IF; --g_retcode is 2
315
316 IF (g_mode = 'R') THEN
317 update_doc_status(p_doc_id, g_retcode);
318 END IF;
319
320 EXCEPTION WHEN OTHERS THEN
321 g_retcode := 2;
322 g_errbuf:= 'Error in process_document procedure. SQL Error is '||SQLERRM;
323 Fv_Utility.Log_mesg(Fnd_Log.LEVEL_UNEXPECTED, l_module_name||
324 '.final_exception',g_errbuf);
325 END; --procedure process_document
326
327
328 PROCEDURE update_doc_status(p_doc_id NUMBER,
329 p_retcode NUMBER)
330 IS
331 l_module_name VARCHAR2(200);
332 l_status fv_be_trx_hdrs.doc_status%TYPE;
333
334 BEGIN
335 l_module_name:= g_module_name || 'update_doc_status';
336 SELECT DECODE(p_retcode, 0, 'AR', 1, 'NR', 'IN')
337 INTO l_status
338 FROM dual;
339
340 UPDATE fv_be_trx_hdrs
341 SET doc_status = l_status,
342 internal_revision_num = DECODE(l_status, 'AR', revision_num,
343 internal_revision_num),
344 distribution_amount = NULL
345 WHERE doc_id = p_doc_id
346 AND doc_status = 'IP';
347
348 UPDATE fv_be_trx_dtls
349 SET transaction_status = l_status,
350 approved_by_user_id = DECODE(l_status, 'AR',g_approver_id, NULL),
351 approval_date = DECODE(l_status,'AR',SYSDATE,NULL)
352 WHERE doc_id = p_doc_id
353 AND transaction_status = 'IP'
354 AND revision_num IN (SELECT revision_num
355 FROM fv_be_trx_hdrs
356 WHERE doc_id = p_doc_id);
357
358 EXCEPTION WHEN OTHERS THEN
359 g_retcode := 2;
360 g_errbuf := 'Error in update_doc_status procedure. SQL Error is ' ||SQLERRM;
361 Fv_Utility.Log_mesg(Fnd_Log.LEVEL_UNEXPECTED, l_module_name||'.final_exception',g_errbuf);
362
363 END; --update_doc_status
364
365
366 PROCEDURE delete_rpr_docs (p_doc_id NUMBER,
367 p_rpr_to_doc_id NUMBER)
368 IS
369 l_module_name VARCHAR2(200) ;
370 l_doc_id fv_be_trx_hdrs.doc_id%TYPE;
371 l_doc_num fv_be_rpr_transactions.doc_number%TYPE;
372
373 CURSOR get_docnum_c IS
374 SELECT SUBSTR(doc_number,1,INSTR(doc_number,'-RPF') - 1) doc_num,
375 set_of_books_id sob,budget_level_id id
376 FROM fv_be_trx_hdrs
377 WHERE doc_id = p_doc_id;
378 BEGIN
379 l_module_name := g_module_name || 'delete_rpr_docs';
380 log_message(l_module_name, 'Updating the RPR transaction status to ' ||
381 ' Not Reserved');
382
383 FOR l_getdoc IN get_docnum_c LOOP
384 UPDATE fv_be_rpr_transactions
385 SET transaction_status = 'NR'
386 WHERE doc_number = l_getdoc.doc_num
387 AND set_of_books_id = l_getdoc.sob
388 AND budget_level_id = l_getdoc.id;
389 END LOOP;
390
391 log_message(l_module_name, 'Deleting the RPR from and to documents created ');
392 FOR i IN 1..2
393 LOOP
394 IF (i = 1) THEN
395 l_doc_id := p_doc_id;
396 ELSE
397 l_doc_id := p_rpr_to_doc_id;
398 END IF;
399
400 DELETE FROM fv_be_trx_dtls
401 WHERE doc_id = l_doc_id;
402
403 DELETE FROM fv_be_trx_hdrs
404 WHERE doc_id = l_doc_id;
405 END LOOP;
406
407 COMMIT;
408 EXCEPTION WHEN OTHERS THEN
409 g_retcode := 2;
410 g_errbuf := 'Error in delete_rpr_docs procedure. SQL Error is ' ||SQLERRM;
411 Fv_Utility.Log_mesg(Fnd_Log.LEVEL_UNEXPECTED, l_module_name||
412 '.final_exception',g_errbuf);
413 END delete_rpr_docs;
414
415 PROCEDURE log_message(p_message VARCHAR2)
416 IS
417 BEGIN
418 log_message(NULL, p_message);
419 END;
420
421 PROCEDURE log_message(p_module VARCHAR2, p_message VARCHAR2)
422 IS
423 l_module_name VARCHAR2(200);
424 BEGIN
425 l_module_name := g_module_name || 'log_message';
426
427 IF ( Fnd_Log.LEVEL_STATEMENT >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
428 Fv_Utility.DEBUG_MESG(Fnd_Log.LEVEL_STATEMENT, p_module,p_message);
429 END IF;
430
431 EXCEPTION WHEN OTHERS THEN
432 g_retcode := 2;
433 g_errbuf := 'Error in delete_rpr_docs procedure. SQL Error is ' ||SQLERRM;
434 Fv_Utility.Log_mesg(Fnd_Log.LEVEL_UNEXPECTED, l_module_name||
435 '.final_exception',g_errbuf);
436 RAISE;
437 END; --log_message
438
439 -- +===========================================================================+
440 -- This procedure will reset the status of the doc to 'InComplete' if the
441 -- Revision Num is is 0 else 'Require Re approval'.
442 -- +===========================================================================+
443 PROCEDURE reset_doc_status(p_doc_id NUMBER) IS
444 l_module_name VARCHAR2(200);
445 BEGIN
446 l_module_name := g_module_name || 'reset_doc_status';
447 UPDATE Fv_Be_Trx_hdrs
448 SET doc_status = DECODE(revision_num,0,'IN','RA')
449 WHERE doc_id = p_doc_id
450 AND doc_status = 'IP';
451
452 UPDATE Fv_Be_Trx_Dtls
453 SET transaction_status = 'IN'
454 WHERE doc_id = p_doc_id
455 AND transaction_status = 'IP'
456 AND revision_num IN(SELECT revision_num
457 FROM fv_be_trx_hdrs
458 WHERE doc_id = p_doc_id);
459 EXCEPTION WHEN OTHERS THEN
460 g_retcode := 2;
461 g_errbuf := 'Error reset_doc_status SQL Error is ' ||SQLERRM;
462 Fv_Utility.Log_mesg(Fnd_Log.LEVEL_UNEXPECTED, l_module_name||
463 '.final_exception',g_errbuf);
464 RAISE;
465 END reset_doc_status;
466 -- +===========================================================================+
467 -- Global Variable Initilization
468 -- +===========================================================================+
469 BEGIN
470 g_module_name := 'fv.plsql.FV_BE_FUND_PKG.';
471 g_log_msg := 'N';
472 END Fv_Be_Fund_Pkg; -- Package body