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