[Home] [Help]
PACKAGE BODY: APPS.FV_BE_RPR_PKG
Source
1 PACKAGE BODY fv_be_rpr_pkg AS
2 --$Header: FVBERPRB.pls 120.19.12010000.2 2009/11/04 03:22:16 yanasing ship $
3 g_module_name VARCHAR2(100);
4
5
6 g_errbuf VARCHAR2(1000);
7 g_retcode NUMBER := 0;
8 g_user_id NUMBER(15);
9 g_resp_id NUMBER(15);
10 g_login_id NUMBER(15);
11 g_sysdate DATE;
12 g_sob_id NUMBER(15);
13
14 PROCEDURE main ( errbuf OUT NOCOPY VARCHAR2,
15 retcode OUT NOCOPY VARCHAR2,
16 p_sob_id NUMBER,
17 p_approval_id NUMBER,
18 p_submitter_id NUMBER,
19 p_approver_id NUMBER,
20 p_note VARCHAR2
21 )
22 IS
23
24 l_module_name VARCHAR2(200);
25 e_error EXCEPTION;
26
27 l_rpr_rec fv_be_rpr_transactions%ROWTYPE;
28 l_trx_hdr_rec fv_be_trx_hdrs%ROWTYPE;
29 l_trx_dtl_rec fv_be_trx_dtls%ROWTYPE;
30 l_from_doc_number Fv_Be_Trx_Hdrs.doc_number%TYPE;
31 l_to_doc_number Fv_Be_Trx_Hdrs.doc_number%TYPE;
32 l_workflow_flag fv_budget_options.workflow_flag%TYPE;
33
34 l_from_doc_id NUMBER(15);
35 l_to_doc_id NUMBER(15);
36 l_amount CHAR(19);
37 l_gl_date CHAR(11);
38 l_packet_id NUMBER(15);
39 l_error_flag VARCHAR2(1) ;
40 l_log_message VARCHAR2(2000);
41
42 -- R12 changes
43
44 l_doc_type CONSTANT VARCHAR2(30) := 'BE_RPR_TRANSACTIONS';
45 l_event_type VARCHAR2(30);
46 l_return_status VARCHAR2(10);
47 l_status_code VARCHAR2(100);
48
49 -- R12 changes
50
51 CURSOR rpr_transactions_c IS
52 SELECT * FROM fv_be_rpr_transactions
53 WHERE set_of_books_id = p_sob_id
54 AND approval_id = p_approval_id
55 AND transaction_status = 'IP'
56 ORDER BY budget_level_id;
57
58 BEGIN
59 l_module_name := g_module_name || 'main';
60 l_error_flag := 'N';
61
62 IF ( Fnd_Log.Level_Statement>= Fnd_Log.G_Current_Runtime_Level) THEN
63 Fv_Utility.Debug_Mesg(Fnd_Log.Level_Statement, l_module_name,
64 'START OF PROCESS');
65 END IF;
66 retcode := 0;
67 g_user_id := fnd_global.user_id;
68 g_resp_id := fnd_global.resp_id;
69 g_login_id := fnd_global.login_id;
70 g_sysdate := SYSDATE;
71 g_sob_id := p_sob_id;
72
73 SELECT workflow_flag
74 INTO l_workflow_flag
75 FROM fv_budget_options
76 WHERE set_of_books_id = p_sob_id;
77
78 l_log_message := ' Reprogramming Documents Creation and Approval Output Report';
79 fnd_file.put_line(FND_FILE.OUTPUT,l_log_message);
80 l_log_message := ' ';
81 fnd_file.put_line(FND_FILE.OUTPUT,l_log_message);
82 l_log_message := ' From Document To Document GL Date Amount Status ';
83 fnd_file.put_line(FND_FILE.OUTPUT,l_log_message);
84 l_log_message := ' ------------- ----------- -------- ------ -------';
85 fnd_file.put_line(FND_FILE.OUTPUT,l_log_message);
86
87 BEGIN
88
89 FOR l_rpr_rec IN rpr_transactions_c
90 LOOP
91
92 FOR i IN 1..2
93 LOOP
94
95 IF ( Fnd_Log.Level_Statement>= Fnd_Log.G_Current_Runtime_Level) THEN
96 Fv_Utility.Debug_Mesg(Fnd_Log.Level_Statement, l_module_name,
97 'INSIDE CURSOR LOOP');
98 END IF;
99
100 IF ( Fnd_Log.Level_Statement>= Fnd_Log.G_Current_Runtime_Level) THEN
101 Fv_Utility.Debug_Mesg(Fnd_Log.Level_Statement, l_module_name,
102 'SETTING HEADER VARIABLES ');
103 END IF;
104
105 set_hdr_fields(i, l_trx_hdr_rec, l_rpr_rec);
106
107 IF (g_retcode = 2) THEN
108 RAISE e_error;
109 END IF;
110
111 IF (i=1) THEN
112 l_from_doc_id := l_trx_hdr_rec.doc_id;
113 l_from_doc_number := l_trx_hdr_rec.doc_number;
114 ELSE
115 l_to_doc_id := l_trx_hdr_rec.doc_id;
116 l_to_doc_number := l_trx_hdr_rec.doc_number;
117 END IF;
118
119 IF ( Fnd_Log.Level_Statement>= Fnd_Log.G_Current_Runtime_Level) THEN
120 Fv_Utility.Debug_Mesg(Fnd_Log.Level_Statement, l_module_name,
121 'BEFORE INSERTING RECORD DOC NUMBER '||
122 l_trx_hdr_rec.doc_number);
123 END IF;
124
125 insert_hdr_record(l_trx_hdr_rec);
126
127 IF (g_retcode = 2) THEN
128 RAISE e_error;
129 END IF;
130
131 IF ( Fnd_Log.Level_Statement>= Fnd_Log.G_Current_Runtime_Level) THEN
132 Fv_Utility.Debug_Mesg(Fnd_Log.Level_Statement, l_module_name,
133 'SETTING DETAIL VARIABLES ');
134 END IF;
135
136 set_dtl_fields(i, l_trx_dtl_rec, l_rpr_rec);
137
138 IF (g_retcode = 2) THEN
139 RAISE e_error;
140 END IF;
141
142 l_trx_dtl_rec.doc_id := l_trx_hdr_rec.doc_id;
143
144 IF ( Fnd_Log.Level_Statement>= Fnd_Log.G_Current_Runtime_Level) THEN
145 Fv_Utility.Debug_Mesg(Fnd_Log.Level_Statement, l_module_name,
146 'BEFORE INSERTING DETAIL RECORD');
147 END IF;
148
149 insert_dtl_record(l_trx_dtl_rec);
150
151 IF (g_retcode = 2) THEN
152 RAISE e_error;
153 END IF;
154
155 END LOOP; --for i in 1..2
156
157 --Check if a lock is needed when record is fetched???????
158
159 UPDATE fv_be_rpr_transactions
160 SET transaction_status = 'PR'
161 WHERE transaction_id = l_rpr_rec.transaction_id;
162
163 COMMIT;
164
165 UPDATE fv_be_trx_hdrs
166 SET doc_status = 'IP'
167 WHERE doc_id IN (l_from_doc_id, l_to_doc_id);
168
169 UPDATE fv_be_trx_dtls
170 SET transaction_status = 'IP'
171 WHERE doc_id IN (l_from_doc_id, l_to_doc_id);
172
173 COMMIT;
174 -- R12 changes
175
176 If l_rpr_rec.budget_level_id = 1 Then
177 l_event_type := 'RPR_BA_RESERVE';
178 Else
179 l_event_type := 'RPR_FD_RESERVE';
180 End If;
181
182 -- R12 changes
183 IF ((l_workflow_flag = 'Y') AND (g_user_id <> l_rpr_rec.approved_by_user_id)) THEN
184 --p_approver_id)) then
185
186 IF ( Fnd_Log.Level_Statement>= Fnd_Log.G_Current_Runtime_Level) THEN
187 Fv_Utility.Debug_Mesg(Fnd_Log.Level_Statement, l_module_name,
188 'CALLING WORKFLOW');
189 END IF;
190 --Call workflow procedure
191 fv_wf_be_approval.Main(errbuf, retcode,p_sob_id,
192 p_submitter_id,l_rpr_rec.approved_by_user_id,
193 l_from_doc_id,
194 p_note, l_to_doc_id,g_user_id,g_resp_id);
195
196 IF retcode <> 0 THEN
197 l_log_message := 'Error submitting workflow '||errbuf;
198 Fv_Utility.Log_Mesg(Fnd_Log.Level_Error, l_module_name,
199 L_LOG_MESSAGE);
200 reset_doc_status(l_from_doc_id, l_to_doc_id);
201 l_error_flag := 'Y';
202 ELSE
203 l_log_message := 'Document submitted to workflow ';
204 IF ( Fnd_Log.Level_Statement>=
205 Fnd_Log.G_Current_Runtime_Level) THEN
206 Fv_Utility.Debug_Mesg(Fnd_Log.Level_Statement,
207 l_module_name,L_LOG_MESSAGE);
208 END IF;
209 END IF;
210
211 ELSE
212
213 IF ( Fnd_Log.Level_Statement>= Fnd_Log.G_Current_Runtime_Level) THEN
214 Fv_Utility.Debug_Mesg(Fnd_Log.Level_Statement, l_module_name,
215 'CALLING FUNDS RESERVATION');
216 END IF;
217
218 -- R12 changes
219 fv_be_fund_pkg.main (
220 errbuf,
221 retcode,
222 'R',
223 p_sob_id,
224 l_from_doc_id,
225 l_to_doc_id,
226 g_user_id,
227 l_doc_type,
228 l_event_type,
229 l_rpr_rec.gl_date,
230 l_return_status,
231 l_status_code,
232 g_user_id,
233 g_resp_id);
234
235 -- R12 changes
236 IF retcode = 2 THEN
237 l_log_message := 'Error in Fund Reservation process '||errbuf;
238 IF ( Fnd_Log.Level_Error >= Fnd_Log.G_Current_Runtime_Level)THEN
239 Fv_Utility.Debug_Mesg(Fnd_Log.Level_Error, l_module_name,
240 L_LOG_MESSAGE);
241 END IF;
242 reset_doc_status(l_from_doc_id, l_to_doc_id);
243 l_error_flag := 'Y';
244 ELSIF retcode = 1 THEN
245 l_log_message := 'Unable to Reserve Funds, no documents created';
246 Fv_Utility.Log_Mesg(Fnd_Log.Level_Error, l_module_name,
247 L_LOG_MESSAGE);
248 ELSE
249 l_log_message := 'Fund Reservation Successful ';
250 IF ( Fnd_Log.Level_Statement>=
251 Fnd_Log.G_Current_Runtime_Level) THEN
252 Fv_Utility.Debug_Mesg(Fnd_Log.Level_Statement, l_module_name,
253 L_LOG_MESSAGE);
254 END IF;
255 END IF;
256
257 END IF; --workflow flag
258
259 IF ( Fnd_Log.Level_Statement>= Fnd_Log.G_Current_Runtime_Level) THEN
260 Fv_Utility.Debug_Mesg(Fnd_Log.Level_Statement, l_module_name,
261 'BEFORE AMOUNT');
262 END IF;
263 l_amount := TO_CHAR(l_trx_dtl_rec.amount,'999,999,999,999.99');
264 IF ( Fnd_Log.Level_Statement>= Fnd_Log.G_Current_Runtime_Level) THEN
265 Fv_Utility.Debug_Mesg(Fnd_Log.Level_Statement, l_module_name,
266 'AFTER AMOUNT');
267 END IF;
268 l_gl_date := TO_CHAR(l_trx_dtl_rec.gl_date,'DD-MON-YYYY');
269
270 l_log_message := ' '||l_from_doc_number||' '||l_to_doc_number
271 || ' '|| l_gl_date || ' ' || l_amount ||' ' ||l_log_message;
272
273 fnd_file.put_line(FND_FILE.OUTPUT,l_log_message);
274
275 END LOOP; --l_rpr_rec cursor
276
277
278 IF (l_error_flag = 'Y') THEN
279 retcode := 2;
280 END IF;
281 IF ( Fnd_Log.Level_Statement>= Fnd_Log.G_Current_Runtime_Level) THEN
282 Fv_Utility.Debug_Mesg(Fnd_Log.Level_Statement, l_module_name,
283 'PROCESS END');
284 END IF;
285
286 EXCEPTION WHEN e_error THEN
287
288 IF ( Fnd_Log.Level_Statement>= Fnd_Log.G_Current_Runtime_Level) THEN
289 Fv_Utility.Debug_Mesg(Fnd_Log.Level_Statement, l_module_name,
290 'EXCEPTION ENCOUNTERED IN MAIN ');
291 END IF;
292
293 -- Rolling back the row created in fv_be_trx_hdrs.
294 ROLLBACK ;
295
296 --Reset transaction status to Incomplete if error encountered
297 UPDATE fv_be_rpr_transactions
298 SET transaction_status = 'IN'
299 WHERE set_of_books_id = p_sob_id
300 AND approval_id = p_approval_id
301 AND transaction_status = 'IP';
302
303 retcode := g_retcode;
304 errbuf := g_errbuf;
305 RETURN;
306
307 END;
308
309 EXCEPTION WHEN OTHERS THEN
310 retcode := 2;
311 errbuf := 'Error in main procedure '|| SQLERRM;
312 Fv_Utility.Log_Mesg(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
313 '.final_exception',errbuf);
314 RETURN;
315
316 END ; --procedure main
317
318
319 PROCEDURE set_hdr_fields (p_count NUMBER,
320 p_trx_hdr_rec OUT NOCOPY fv_be_trx_hdrs%ROWTYPE,
321 p_rpr_rec fv_be_rpr_transactions%ROWTYPE)
322 IS
323 l_module_name VARCHAR2(200);
324
325 BEGIN
326 l_module_name := g_module_name || 'set_hdr_fields';
327 SELECT fv_be_trx_hdrs_s.NEXTVAL
328 INTO p_trx_hdr_rec.doc_id
329 FROM dual;
330
331 IF (p_count=1) THEN
332 p_trx_hdr_rec.doc_number := p_rpr_rec.doc_number||'-RPF';
336 ELSE
333 p_trx_hdr_rec.fund_value := p_rpr_rec.fund_value_from;
334 p_trx_hdr_rec.doc_total := p_rpr_rec.amount * -1;
335
337 p_trx_hdr_rec.doc_number := p_rpr_rec.doc_number||'-RPT';
338 p_trx_hdr_rec.fund_value := p_rpr_rec.fund_value_to;
339 p_trx_hdr_rec.doc_total := p_rpr_rec.amount;
340 END IF;
341 p_trx_hdr_rec.revision_num := 0;
342 p_trx_hdr_rec.internal_revision_num := 0;
343
344 SELECT treasury_symbol_id
345 INTO p_trx_hdr_rec.treasury_symbol_id
346 FROM fv_fund_parameters
347 WHERE fund_value =
348 DECODE(p_count,1,p_rpr_rec.fund_value_from,2,p_rpr_rec.fund_value_to)
349 AND set_of_books_id = g_sob_id;
350
351 p_trx_hdr_rec.budget_level_id := p_rpr_rec.budget_level_id;
352 p_trx_hdr_rec.transaction_date := g_sysdate;
353 p_trx_hdr_rec.doc_status := 'IN';
354 p_trx_hdr_rec.source := 'RPR';
355 IF p_rpr_rec.budget_level_id = 1 THEN
356 IF (p_count=1) THEN
357 p_trx_hdr_rec.budgeting_segments := p_rpr_rec.distribution_from;
358 p_trx_hdr_rec.segment1 := p_rpr_rec.segment1_from;
359 p_trx_hdr_rec.segment2 := p_rpr_rec.segment2_from;
360 p_trx_hdr_rec.segment3 := p_rpr_rec.segment3_from;
361 p_trx_hdr_rec.segment4 := p_rpr_rec.segment4_from;
362 p_trx_hdr_rec.segment5 := p_rpr_rec.segment5_from;
363 p_trx_hdr_rec.segment6 := p_rpr_rec.segment6_from;
364 p_trx_hdr_rec.segment7 := p_rpr_rec.segment7_from;
365 p_trx_hdr_rec.segment8 := p_rpr_rec.segment8_from;
366 p_trx_hdr_rec.segment9 := p_rpr_rec.segment9_from;
367 p_trx_hdr_rec.segment10 := p_rpr_rec.segment10_from;
368 p_trx_hdr_rec.segment11 := p_rpr_rec.segment11_from;
369 p_trx_hdr_rec.segment12 := p_rpr_rec.segment12_from;
370 p_trx_hdr_rec.segment13 := p_rpr_rec.segment13_from;
371 p_trx_hdr_rec.segment14 := p_rpr_rec.segment14_from;
372 p_trx_hdr_rec.segment15 := p_rpr_rec.segment15_from;
373 p_trx_hdr_rec.segment16 := p_rpr_rec.segment16_from;
374 p_trx_hdr_rec.segment17 := p_rpr_rec.segment17_from;
375 p_trx_hdr_rec.segment18 := p_rpr_rec.segment18_from;
376 p_trx_hdr_rec.segment19 := p_rpr_rec.segment19_from;
377 p_trx_hdr_rec.segment20 := p_rpr_rec.segment20_from;
378 p_trx_hdr_rec.segment21 := p_rpr_rec.segment21_from;
379 p_trx_hdr_rec.segment22 := p_rpr_rec.segment22_from;
380 p_trx_hdr_rec.segment23 := p_rpr_rec.segment23_from;
381 p_trx_hdr_rec.segment24 := p_rpr_rec.segment24_from;
382 p_trx_hdr_rec.segment25 := p_rpr_rec.segment25_from;
383 p_trx_hdr_rec.segment26 := p_rpr_rec.segment26_from;
384 p_trx_hdr_rec.segment27 := p_rpr_rec.segment27_from;
385 p_trx_hdr_rec.segment28 := p_rpr_rec.segment28_from;
386 p_trx_hdr_rec.segment29 := p_rpr_rec.segment29_from;
387 p_trx_hdr_rec.segment30 := p_rpr_rec.segment30_from;
388 ELSE
389 p_trx_hdr_rec.budgeting_segments := p_rpr_rec.distribution_to;
390 p_trx_hdr_rec.segment1 := p_rpr_rec.segment1;
391 p_trx_hdr_rec.segment2 := p_rpr_rec.segment2;
392 p_trx_hdr_rec.segment3 := p_rpr_rec.segment3;
393 p_trx_hdr_rec.segment4 := p_rpr_rec.segment4;
394 p_trx_hdr_rec.segment5 := p_rpr_rec.segment5;
395 p_trx_hdr_rec.segment6 := p_rpr_rec.segment6;
396 p_trx_hdr_rec.segment7 := p_rpr_rec.segment7;
397 p_trx_hdr_rec.segment8 := p_rpr_rec.segment8;
398 p_trx_hdr_rec.segment9 := p_rpr_rec.segment9;
399 p_trx_hdr_rec.segment10 := p_rpr_rec.segment10;
400 p_trx_hdr_rec.segment11 := p_rpr_rec.segment11;
401 p_trx_hdr_rec.segment12 := p_rpr_rec.segment12;
402 p_trx_hdr_rec.segment13 := p_rpr_rec.segment13;
403 p_trx_hdr_rec.segment14 := p_rpr_rec.segment14;
404 p_trx_hdr_rec.segment15 := p_rpr_rec.segment15;
405 p_trx_hdr_rec.segment16 := p_rpr_rec.segment16;
406 p_trx_hdr_rec.segment17 := p_rpr_rec.segment17;
407 p_trx_hdr_rec.segment18 := p_rpr_rec.segment18;
408 p_trx_hdr_rec.segment19 := p_rpr_rec.segment19;
409 p_trx_hdr_rec.segment20 := p_rpr_rec.segment20;
410 p_trx_hdr_rec.segment21 := p_rpr_rec.segment21;
411 p_trx_hdr_rec.segment22 := p_rpr_rec.segment22;
412 p_trx_hdr_rec.segment23 := p_rpr_rec.segment23;
413 p_trx_hdr_rec.segment24 := p_rpr_rec.segment24;
414 p_trx_hdr_rec.segment25 := p_rpr_rec.segment25;
415 p_trx_hdr_rec.segment26 := p_rpr_rec.segment26;
416 p_trx_hdr_rec.segment27 := p_rpr_rec.segment27;
417 p_trx_hdr_rec.segment28 := p_rpr_rec.segment28;
418 p_trx_hdr_rec.segment29 := p_rpr_rec.segment29;
419 p_trx_hdr_rec.segment30 := p_rpr_rec.segment30;
420
421 END IF;
422
423 ELSE
424
425 /*
426 Commented for bug 9067331 and added below query
427 p_trx_hdr_rec.budgeting_segments := NULL;
428 p_trx_hdr_rec.segment1 := NULL;
429 p_trx_hdr_rec.segment2 := NULL;
430 p_trx_hdr_rec.segment3 := NULL;
431 p_trx_hdr_rec.segment4 := NULL;
432 p_trx_hdr_rec.segment5 := NULL;
433 p_trx_hdr_rec.segment6 := NULL;
434 p_trx_hdr_rec.segment7 := NULL;
435 p_trx_hdr_rec.segment8 := NULL;
436 p_trx_hdr_rec.segment9 := NULL;
437 p_trx_hdr_rec.segment10 := NULL;
438 p_trx_hdr_rec.segment11 := NULL;
439 p_trx_hdr_rec.segment12 := NULL;
440 p_trx_hdr_rec.segment13 := NULL;
441 p_trx_hdr_rec.segment14 := NULL;
442 p_trx_hdr_rec.segment15 := NULL;
443 p_trx_hdr_rec.segment16 := NULL;
444 p_trx_hdr_rec.segment17 := NULL;
445 p_trx_hdr_rec.segment18 := NULL;
446 p_trx_hdr_rec.segment19 := NULL;
447 p_trx_hdr_rec.segment20 := NULL;
448 p_trx_hdr_rec.segment21 := NULL;
449 p_trx_hdr_rec.segment22 := NULL;
450 p_trx_hdr_rec.segment23 := NULL;
451 p_trx_hdr_rec.segment24 := NULL;
452 p_trx_hdr_rec.segment25 := NULL;
453 p_trx_hdr_rec.segment26 := NULL;
454 p_trx_hdr_rec.segment27 := NULL;
458
455 p_trx_hdr_rec.segment28 := NULL;
456 p_trx_hdr_rec.segment29 := NULL;
457 p_trx_hdr_rec.segment30 := NULL;*/
459 select h.budgeting_segments,
460 h.segment1,h.segment2,h.segment3,h.segment4,h.segment5,h.segment6,
461 h.segment7,h.segment8,h.segment9,h.segment10,
462 h.segment11,h.segment12,h.segment13,h.segment14,h.segment15,h.segment16,
463 h.segment17,h.segment18,h.segment19,h.segment20,h.segment21,h.segment22,h.segment23,
464 h.segment24, h.segment25,h.segment26,h.segment27,
465 h.segment28,h.segment29,h.segment30
466 into
467 p_trx_hdr_rec.budgeting_segments,
468 p_trx_hdr_rec.segment1,
469 p_trx_hdr_rec.segment2,
470 p_trx_hdr_rec.segment3,
471 p_trx_hdr_rec.segment4,
472 p_trx_hdr_rec.segment5,
473 p_trx_hdr_rec.segment6,
474 p_trx_hdr_rec.segment7,
475 p_trx_hdr_rec.segment8,
476 p_trx_hdr_rec.segment9,
477 p_trx_hdr_rec.segment10,
478 p_trx_hdr_rec.segment11,
479 p_trx_hdr_rec.segment12,
480 p_trx_hdr_rec.segment13,
481 p_trx_hdr_rec.segment14,
482 p_trx_hdr_rec.segment15,
483 p_trx_hdr_rec.segment16,
484 p_trx_hdr_rec.segment17,
485 p_trx_hdr_rec.segment18,
486 p_trx_hdr_rec.segment19,
487 p_trx_hdr_rec.segment20,
488 p_trx_hdr_rec.segment21,
489 p_trx_hdr_rec.segment22,
490 p_trx_hdr_rec.segment23,
491 p_trx_hdr_rec.segment24,
492 p_trx_hdr_rec.segment25,
493 p_trx_hdr_rec.segment26,
494 p_trx_hdr_rec.segment27,
495 p_trx_hdr_rec.segment28,
496 p_trx_hdr_rec.segment29,
497 p_trx_hdr_rec.segment30
498 from fv_be_trx_dtls d , fv_be_trx_hdrs h
499 where d.budgeting_segments = p_rpr_rec.distribution_from
500 and h.doc_id = d.doc_id
501 and h.budget_level_id = p_rpr_rec.budget_level_id
502 and rownum = 1;
503
504 END IF;
505
506 p_trx_hdr_rec.approval_id := NULL;
507 p_trx_hdr_rec.distribution_amount := NULL;
508 p_trx_hdr_rec.old_doc_number := NULL;
509 p_trx_hdr_rec.set_of_books_id := g_sob_id;
510 p_trx_hdr_rec.creation_date := g_sysdate;
511 p_trx_hdr_rec.created_by := g_user_id;
512 p_trx_hdr_rec.last_update_date := g_sysdate;
513 p_trx_hdr_rec.last_updated_by := g_user_id;
514 p_trx_hdr_rec.last_update_login := g_login_id;
515 p_trx_hdr_rec.bu_group_id := p_rpr_rec.bu_group_id;
516
517 EXCEPTION WHEN OTHERS THEN
518 g_retcode := 2;
519 g_errbuf := 'Error in set_hdr_fields procedure '|| SQLERRM;
520 Fv_Utility.Log_Mesg(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
521 '.final_exception',g_errbuf);
522 END; --procedure set_hdr_fields
523
524 PROCEDURE set_dtl_fields (p_count NUMBER,
525 p_trx_dtl_rec OUT NOCOPY fv_be_trx_dtls%ROWTYPE,
526 p_rpr_rec fv_be_rpr_transactions%ROWTYPE)
527 IS
528 l_module_name VARCHAR2(200);
529
530 BEGIN
531 l_module_name := g_module_name || 'set_dtl_fields';
532 SELECT fv_be_trx_dtls_s.NEXTVAL
533 INTO p_trx_dtl_rec.transaction_id
534 FROM dual;
535
536 p_trx_dtl_rec.revision_num := 0;
537 p_trx_dtl_rec.transaction_status := 'IN';
538
539 p_trx_dtl_rec.gl_date := p_rpr_rec.gl_date;
540
541 SELECT quarter_num
542 INTO p_trx_dtl_rec.quarter_num
543 FROM gl_period_statuses
544 WHERE set_of_books_id = g_sob_id
545 AND application_id = '101'
546 AND start_date <= p_rpr_rec.gl_date
547 AND end_date >= p_rpr_rec.gl_date
548 AND adjustment_period_flag='N';
549
550 p_trx_dtl_rec.transaction_type_id := p_rpr_rec.transaction_type_id;
551
552 --Set the detail records segments regardless of the budget level
553 --As per the latest change even for first budget level in the table
554 --segment values will be stored at detail level but they will not be
555 --visible on the form. This is only for the lower level
556 --from distribution LOV purpose.
557
558 IF (p_count=1) THEN
559
560 p_trx_dtl_rec.budgeting_segments := p_rpr_rec.distribution_from;
561 p_trx_dtl_rec.segment1 := p_rpr_rec.segment1_from;
562 p_trx_dtl_rec.segment2 := p_rpr_rec.segment2_from;
563 p_trx_dtl_rec.segment3 := p_rpr_rec.segment3_from;
564 p_trx_dtl_rec.segment4 := p_rpr_rec.segment4_from;
565 p_trx_dtl_rec.segment5 := p_rpr_rec.segment5_from;
566 p_trx_dtl_rec.segment6 := p_rpr_rec.segment6_from;
567 p_trx_dtl_rec.segment7 := p_rpr_rec.segment7_from;
568 p_trx_dtl_rec.segment8 := p_rpr_rec.segment8_from;
569 p_trx_dtl_rec.segment9 := p_rpr_rec.segment9_from;
570 p_trx_dtl_rec.segment10 := p_rpr_rec.segment10_from;
571 p_trx_dtl_rec.segment11 := p_rpr_rec.segment11_from;
572 p_trx_dtl_rec.segment12 := p_rpr_rec.segment12_from;
573 p_trx_dtl_rec.segment13 := p_rpr_rec.segment13_from;
574 p_trx_dtl_rec.segment14 := p_rpr_rec.segment14_from;
575 p_trx_dtl_rec.segment15 := p_rpr_rec.segment15_from;
576 p_trx_dtl_rec.segment16 := p_rpr_rec.segment16_from;
577 p_trx_dtl_rec.segment17 := p_rpr_rec.segment17_from;
578 p_trx_dtl_rec.segment18 := p_rpr_rec.segment18_from;
579 p_trx_dtl_rec.segment19 := p_rpr_rec.segment19_from;
580 p_trx_dtl_rec.segment20 := p_rpr_rec.segment20_from;
581 p_trx_dtl_rec.segment21 := p_rpr_rec.segment21_from;
582 p_trx_dtl_rec.segment22 := p_rpr_rec.segment22_from;
583 p_trx_dtl_rec.segment23 := p_rpr_rec.segment23_from;
584 p_trx_dtl_rec.segment24 := p_rpr_rec.segment24_from;
585 p_trx_dtl_rec.segment25 := p_rpr_rec.segment25_from;
586 p_trx_dtl_rec.segment26 := p_rpr_rec.segment26_from;
587 p_trx_dtl_rec.segment27 := p_rpr_rec.segment27_from;
591 ELSE
588 p_trx_dtl_rec.segment28 := p_rpr_rec.segment28_from;
589 p_trx_dtl_rec.segment29 := p_rpr_rec.segment29_from;
590 p_trx_dtl_rec.segment30 := p_rpr_rec.segment30_from;
592 p_trx_dtl_rec.budgeting_segments := p_rpr_rec.distribution_to;
593 p_trx_dtl_rec.segment1 := p_rpr_rec.segment1;
594 p_trx_dtl_rec.segment2 := p_rpr_rec.segment2;
595 p_trx_dtl_rec.segment3 := p_rpr_rec.segment3;
596 p_trx_dtl_rec.segment4 := p_rpr_rec.segment4;
597 p_trx_dtl_rec.segment5 := p_rpr_rec.segment5;
598 p_trx_dtl_rec.segment6 := p_rpr_rec.segment6;
599 p_trx_dtl_rec.segment7 := p_rpr_rec.segment7;
600 p_trx_dtl_rec.segment8 := p_rpr_rec.segment8;
601 p_trx_dtl_rec.segment9 := p_rpr_rec.segment9;
602 p_trx_dtl_rec.segment10 := p_rpr_rec.segment10;
603 p_trx_dtl_rec.segment11 := p_rpr_rec.segment11;
604 p_trx_dtl_rec.segment12 := p_rpr_rec.segment12;
605 p_trx_dtl_rec.segment13 := p_rpr_rec.segment13;
606 p_trx_dtl_rec.segment14 := p_rpr_rec.segment14;
607 p_trx_dtl_rec.segment15 := p_rpr_rec.segment15;
608 p_trx_dtl_rec.segment16 := p_rpr_rec.segment16;
609 p_trx_dtl_rec.segment17 := p_rpr_rec.segment17;
610 p_trx_dtl_rec.segment18 := p_rpr_rec.segment18;
611 p_trx_dtl_rec.segment19 := p_rpr_rec.segment19;
612 p_trx_dtl_rec.segment20 := p_rpr_rec.segment20;
613 p_trx_dtl_rec.segment21 := p_rpr_rec.segment21;
614 p_trx_dtl_rec.segment22 := p_rpr_rec.segment22;
615 p_trx_dtl_rec.segment23 := p_rpr_rec.segment23;
616 p_trx_dtl_rec.segment24 := p_rpr_rec.segment24;
617 p_trx_dtl_rec.segment25 := p_rpr_rec.segment25;
618 p_trx_dtl_rec.segment26 := p_rpr_rec.segment26;
619 p_trx_dtl_rec.segment27 := p_rpr_rec.segment27;
620 p_trx_dtl_rec.segment28 := p_rpr_rec.segment28;
621 p_trx_dtl_rec.segment29 := p_rpr_rec.segment29;
622 p_trx_dtl_rec.segment30 := p_rpr_rec.segment30;
623
624 END IF;
625
626 IF (p_count=1) THEN
627 p_trx_dtl_rec.increase_decrease_flag := 'D';
628 ELSE
629 p_trx_dtl_rec.increase_decrease_flag := 'I';
630 END IF;
631 p_trx_dtl_rec.amount := p_rpr_rec.amount;
632 p_trx_dtl_rec.sub_type := p_rpr_rec.sub_type;
633 p_trx_dtl_rec.gl_transfer_flag := 'N';
634 p_trx_dtl_rec.approved_by_user_id := NULL;
635 p_trx_dtl_rec.approved_by_user_id := NULL;
636 p_trx_dtl_rec.posting_process_id := NULL;
637 p_trx_dtl_rec.set_of_books_id := g_sob_id;
638 p_trx_dtl_rec.creation_date := g_sysdate;
639 p_trx_dtl_rec.created_by := g_user_id;
640 p_trx_dtl_rec.last_update_date := g_sysdate;
641 p_trx_dtl_rec.last_updated_by := g_user_id;
642 p_trx_dtl_rec.last_update_login := g_login_id;
643 p_trx_dtl_rec.public_law_code := p_rpr_rec.public_law_code;
644 p_trx_dtl_rec.advance_type := p_rpr_rec.advance_type;
645 p_trx_dtl_rec.dept_id := p_rpr_rec.dept_id;
646 p_trx_dtl_rec.main_account := p_rpr_rec.main_account;
647 p_trx_dtl_rec.transfer_description := p_rpr_rec.transfer_description;
648
649
650
651 EXCEPTION WHEN OTHERS THEN
652 g_retcode := 2;
653 g_errbuf := 'Error in set_dtl_fields procedure '|| SQLERRM;
654 Fv_Utility.Log_Mesg(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
655 '.final_exception',g_errbuf);
656
657 END; --procedure set_dtl_fields
658
659 PROCEDURE insert_hdr_record(p_trx_hdr_rec fv_be_trx_hdrs%ROWTYPE) IS
660 l_module_name VARCHAR2(200);
661
662 BEGIN
663 l_module_name := g_module_name || 'insert_hdr_record';
664
665 INSERT INTO fv_be_trx_hdrs
666 (doc_id ,
667 doc_number ,
668 revision_num ,
669 internal_revision_num ,
670 treasury_symbol_id ,
671 fund_value ,
672 budget_level_id ,
673 transaction_date ,
674 doc_status ,
675 doc_total ,
676 source ,
677 budgeting_segments ,
678 segment1 ,
679 segment2 ,
680 segment3 ,
681 segment4 ,
682 segment5 ,
683 segment6 ,
684 segment7 ,
685 segment8 ,
686 segment9 ,
687 segment10 ,
688 segment11 ,
689 segment12 ,
690 segment13 ,
691 segment14 ,
692 segment15 ,
693 segment16 ,
694 segment17 ,
695 segment18 ,
696 segment19 ,
697 segment20 ,
698 segment21 ,
699 segment22 ,
700 segment23 ,
701 segment24 ,
702 segment25 ,
703 segment26 ,
704 segment27 ,
705 segment28 ,
706 segment29 ,
707 segment30 ,
708 approval_id ,
709 approved_by_user_id ,
710 distribution_amount ,
711 old_doc_number ,
712 set_of_books_id ,
713 bu_group_id ,
714 creation_date ,
715 created_by ,
716 last_update_date ,
717 last_updated_by ,
718 last_update_login)
719 VALUES
720 (p_trx_hdr_rec.doc_id ,
721 p_trx_hdr_rec.doc_number ,
722 p_trx_hdr_rec.revision_num ,
723 p_trx_hdr_rec.internal_revision_num ,
724 p_trx_hdr_rec.treasury_symbol_id ,
725 p_trx_hdr_rec.fund_value ,
726 p_trx_hdr_rec.budget_level_id ,
727 TRUNC(p_trx_hdr_rec.transaction_date) ,
728 p_trx_hdr_rec.doc_status ,
729 p_trx_hdr_rec.doc_total ,
730 p_trx_hdr_rec.source ,
731 p_trx_hdr_rec.budgeting_segments ,
732 p_trx_hdr_rec.segment1 ,
733 p_trx_hdr_rec.segment2 ,
734 p_trx_hdr_rec.segment3 ,
735 p_trx_hdr_rec.segment4 ,
736 p_trx_hdr_rec.segment5 ,
737 p_trx_hdr_rec.segment6 ,
738 p_trx_hdr_rec.segment7 ,
739 p_trx_hdr_rec.segment8 ,
740 p_trx_hdr_rec.segment9 ,
741 p_trx_hdr_rec.segment10 ,
742 p_trx_hdr_rec.segment11 ,
743 p_trx_hdr_rec.segment12 ,
747 p_trx_hdr_rec.segment16 ,
744 p_trx_hdr_rec.segment13 ,
745 p_trx_hdr_rec.segment14 ,
746 p_trx_hdr_rec.segment15 ,
748 p_trx_hdr_rec.segment17 ,
749 p_trx_hdr_rec.segment18 ,
750 p_trx_hdr_rec.segment19 ,
751 p_trx_hdr_rec.segment20 ,
752 p_trx_hdr_rec.segment21 ,
753 p_trx_hdr_rec.segment22 ,
754 p_trx_hdr_rec.segment23 ,
755 p_trx_hdr_rec.segment24 ,
756 p_trx_hdr_rec.segment25 ,
757 p_trx_hdr_rec.segment26 ,
758 p_trx_hdr_rec.segment27 ,
759 p_trx_hdr_rec.segment28 ,
760 p_trx_hdr_rec.segment29 ,
761 p_trx_hdr_rec.segment30 ,
762 p_trx_hdr_rec.approval_id ,
763 p_trx_hdr_rec.approved_by_user_id ,
764 p_trx_hdr_rec.distribution_amount ,
765 p_trx_hdr_rec.old_doc_number ,
766 p_trx_hdr_rec.set_of_books_id ,
767 p_trx_hdr_rec.bu_group_id ,
768 p_trx_hdr_rec.creation_date ,
769 p_trx_hdr_rec.created_by ,
770 p_trx_hdr_rec.last_update_date ,
771 p_trx_hdr_rec.last_updated_by ,
772 p_trx_hdr_rec.last_update_login );
773
774 EXCEPTION WHEN OTHERS THEN
775 g_retcode := 2;
776 g_errbuf := 'Error in insert_hdr_record procedure '||SQLERRM;
777 Fv_Utility.Log_Mesg(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
778 '.final_exception',g_errbuf);
779
780 END; --procedure insert_hdr_record
781
782 PROCEDURE insert_dtl_record (p_trx_dtl_rec fv_be_trx_dtls%ROWTYPE) IS
783 l_module_name VARCHAR2(200);
784
785 BEGIN
786 l_module_name := g_module_name || 'insert_dtl_record';
787 INSERT INTO fv_be_trx_dtls
788 (transaction_id ,
789 doc_id ,
790 revision_num ,
791 transaction_status ,
792 gl_date ,
793 quarter_num ,
794 transaction_type_id ,
795 budgeting_segments ,
796 segment1 ,
797 segment2 ,
798 segment3 ,
799 segment4 ,
800 segment5 ,
801 segment6 ,
802 segment7 ,
803 segment8 ,
804 segment9 ,
805 segment10 ,
806 segment11 ,
807 segment12 ,
808 segment13 ,
809 segment14 ,
810 segment15 ,
811 segment16 ,
812 segment17 ,
813 segment18 ,
814 segment19 ,
815 segment20 ,
816 segment21 ,
817 segment22 ,
818 segment23 ,
819 segment24 ,
820 segment25 ,
821 segment26 ,
822 segment27 ,
823 segment28 ,
824 segment29 ,
825 segment30 ,
826 increase_decrease_flag ,
827 amount ,
828 public_law_code ,
829 advance_type ,
830 dept_id ,
831 main_account ,
832 transfer_description ,
833 sub_type ,
834 gl_transfer_flag ,
835 approved_by_user_id ,
836 posting_process_id ,
837 set_of_books_id ,
838 creation_date ,
839 created_by ,
840 last_update_date ,
841 last_updated_by ,
842 last_update_login )
843 VALUES
844 (p_trx_dtl_rec.transaction_id ,
845 p_trx_dtl_rec.doc_id ,
846 p_trx_dtl_rec.revision_num ,
847 p_trx_dtl_rec.transaction_status ,
848 TRUNC(p_trx_dtl_rec.gl_date) ,
849 p_trx_dtl_rec.quarter_num ,
850 p_trx_dtl_rec.transaction_type_id ,
851 p_trx_dtl_rec.budgeting_segments ,
852 p_trx_dtl_rec.segment1 ,
853 p_trx_dtl_rec.segment2 ,
854 p_trx_dtl_rec.segment3 ,
855 p_trx_dtl_rec.segment4 ,
856 p_trx_dtl_rec.segment5 ,
857 p_trx_dtl_rec.segment6 ,
858 p_trx_dtl_rec.segment7 ,
859 p_trx_dtl_rec.segment8 ,
860 p_trx_dtl_rec.segment9 ,
861 p_trx_dtl_rec.segment10 ,
862 p_trx_dtl_rec.segment11 ,
863 p_trx_dtl_rec.segment12 ,
864 p_trx_dtl_rec.segment13 ,
865 p_trx_dtl_rec.segment14 ,
866 p_trx_dtl_rec.segment15 ,
867 p_trx_dtl_rec.segment16 ,
868 p_trx_dtl_rec.segment17 ,
869 p_trx_dtl_rec.segment18 ,
870 p_trx_dtl_rec.segment19 ,
871 p_trx_dtl_rec.segment20 ,
872 p_trx_dtl_rec.segment21 ,
873 p_trx_dtl_rec.segment22 ,
874 p_trx_dtl_rec.segment23 ,
875 p_trx_dtl_rec.segment24,
876 p_trx_dtl_rec.segment25 ,
877 p_trx_dtl_rec.segment26 ,
878 p_trx_dtl_rec.segment27 ,
879 p_trx_dtl_rec.segment28 ,
880 p_trx_dtl_rec.segment29 ,
881 p_trx_dtl_rec.segment30 ,
882 p_trx_dtl_rec.increase_decrease_flag ,
883 p_trx_dtl_rec.amount ,
884 p_trx_dtl_rec.public_law_code ,
885 p_trx_dtl_rec.advance_type ,
886 p_trx_dtl_rec.dept_id ,
887 p_trx_dtl_rec.main_account ,
888 p_trx_dtl_rec.transfer_description ,
889 p_trx_dtl_rec.sub_type ,
890 p_trx_dtl_rec.gl_transfer_flag ,
891 p_trx_dtl_rec.approved_by_user_id ,
892 p_trx_dtl_rec.posting_process_id ,
893 p_trx_dtl_rec.set_of_books_id ,
894 p_trx_dtl_rec.creation_date ,
895 p_trx_dtl_rec.created_by ,
896 p_trx_dtl_rec.last_update_date ,
897 p_trx_dtl_rec.last_updated_by ,
898 p_trx_dtl_rec.last_update_login );
899
900 EXCEPTION WHEN OTHERS THEN
901 g_retcode := 2;
902 g_errbuf := 'Error in insert_dtl_record procedure '||SQLERRM;
903 Fv_Utility.Log_Mesg(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
904 '.final_exception',g_errbuf);
905
906 END; --procedure insert_dtl_record
907
908 PROCEDURE reset_doc_status(p_from_doc_id NUMBER, p_to_doc_id NUMBER) IS
909 l_module_name VARCHAR2(200);
910
911 BEGIN
912 l_module_name := g_module_name || 'reset_doc_status';
913 UPDATE fv_be_trx_hdrs
914 SET doc_status = 'IN',
915 approved_by_user_id = NULL
916 WHERE doc_id IN (p_from_doc_id, p_to_doc_id);
920 WHERE doc_id IN (p_from_doc_id, p_to_doc_id);
917
918 UPDATE fv_be_trx_dtls
919 SET transaction_status = 'IN'
921
922 COMMIT;
923
924 EXCEPTION WHEN OTHERS THEN
925 g_retcode := 2;
926 g_errbuf := 'Error in reset_doc_status procedure '||SQLERRM;
927 Fv_Utility.Log_Mesg(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
928 '.final_exception',g_errbuf);
929 END;
930 BEGIN
931 g_module_name := 'fv.plsql.fv_be_rpr_pkg.';
932 END fv_be_rpr_pkg; -- Package body