[Home] [Help]
PACKAGE BODY: APPS.FV_BE_RPR_PKG
Source
1 PACKAGE BODY fv_be_rpr_pkg AS
2 --$Header: FVBERPRB.pls 120.19 2006/06/22 06:45:03 svaithil 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';
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
336 ELSE
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 p_trx_hdr_rec.budgeting_segments := NULL;
426 p_trx_hdr_rec.segment1 := NULL;
427 p_trx_hdr_rec.segment2 := NULL;
428 p_trx_hdr_rec.segment3 := NULL;
429 p_trx_hdr_rec.segment4 := NULL;
430 p_trx_hdr_rec.segment5 := NULL;
431 p_trx_hdr_rec.segment6 := NULL;
432 p_trx_hdr_rec.segment7 := NULL;
433 p_trx_hdr_rec.segment8 := NULL;
434 p_trx_hdr_rec.segment9 := NULL;
435 p_trx_hdr_rec.segment10 := NULL;
436 p_trx_hdr_rec.segment11 := NULL;
437 p_trx_hdr_rec.segment12 := NULL;
438 p_trx_hdr_rec.segment13 := NULL;
439 p_trx_hdr_rec.segment14 := NULL;
440 p_trx_hdr_rec.segment15 := NULL;
441 p_trx_hdr_rec.segment16 := NULL;
442 p_trx_hdr_rec.segment17 := NULL;
443 p_trx_hdr_rec.segment18 := NULL;
444 p_trx_hdr_rec.segment19 := NULL;
445 p_trx_hdr_rec.segment20 := NULL;
446 p_trx_hdr_rec.segment21 := NULL;
447 p_trx_hdr_rec.segment22 := NULL;
448 p_trx_hdr_rec.segment23 := NULL;
449 p_trx_hdr_rec.segment24 := NULL;
450 p_trx_hdr_rec.segment25 := NULL;
451 p_trx_hdr_rec.segment26 := NULL;
452 p_trx_hdr_rec.segment27 := NULL;
453 p_trx_hdr_rec.segment28 := NULL;
454 p_trx_hdr_rec.segment29 := NULL;
455 p_trx_hdr_rec.segment30 := NULL;
456
457 END IF;
458
459 p_trx_hdr_rec.approval_id := NULL;
460 p_trx_hdr_rec.distribution_amount := NULL;
461 p_trx_hdr_rec.old_doc_number := NULL;
462 p_trx_hdr_rec.set_of_books_id := g_sob_id;
463 p_trx_hdr_rec.creation_date := g_sysdate;
464 p_trx_hdr_rec.created_by := g_user_id;
465 p_trx_hdr_rec.last_update_date := g_sysdate;
466 p_trx_hdr_rec.last_updated_by := g_user_id;
467 p_trx_hdr_rec.last_update_login := g_login_id;
468 p_trx_hdr_rec.bu_group_id := p_rpr_rec.bu_group_id;
469
470 EXCEPTION WHEN OTHERS THEN
471 g_retcode := 2;
472 g_errbuf := 'Error in set_hdr_fields procedure '|| SQLERRM;
473 Fv_Utility.Log_Mesg(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
474 '.final_exception',g_errbuf);
475 END; --procedure set_hdr_fields
476
477 PROCEDURE set_dtl_fields (p_count NUMBER,
478 p_trx_dtl_rec OUT NOCOPY fv_be_trx_dtls%ROWTYPE,
479 p_rpr_rec fv_be_rpr_transactions%ROWTYPE)
480 IS
481 l_module_name VARCHAR2(200);
482
483 BEGIN
484 l_module_name := g_module_name || 'set_dtl_fields';
485 SELECT fv_be_trx_dtls_s.NEXTVAL
486 INTO p_trx_dtl_rec.transaction_id
487 FROM dual;
488
489 p_trx_dtl_rec.revision_num := 0;
490 p_trx_dtl_rec.transaction_status := 'IN';
491
492 p_trx_dtl_rec.gl_date := p_rpr_rec.gl_date;
493
494 SELECT quarter_num
495 INTO p_trx_dtl_rec.quarter_num
496 FROM gl_period_statuses
497 WHERE set_of_books_id = g_sob_id
498 AND application_id = '101'
499 AND start_date <= p_rpr_rec.gl_date
500 AND end_date >= p_rpr_rec.gl_date
501 AND adjustment_period_flag='N';
502
503 p_trx_dtl_rec.transaction_type_id := p_rpr_rec.transaction_type_id;
504
505 --Set the detail records segments regardless of the budget level
506 --As per the latest change even for first budget level in the table
507 --segment values will be stored at detail level but they will not be
508 --visible on the form. This is only for the lower level
509 --from distribution LOV purpose.
510
511 IF (p_count=1) THEN
512
513 p_trx_dtl_rec.budgeting_segments := p_rpr_rec.distribution_from;
514 p_trx_dtl_rec.segment1 := p_rpr_rec.segment1_from;
515 p_trx_dtl_rec.segment2 := p_rpr_rec.segment2_from;
516 p_trx_dtl_rec.segment3 := p_rpr_rec.segment3_from;
517 p_trx_dtl_rec.segment4 := p_rpr_rec.segment4_from;
518 p_trx_dtl_rec.segment5 := p_rpr_rec.segment5_from;
519 p_trx_dtl_rec.segment6 := p_rpr_rec.segment6_from;
520 p_trx_dtl_rec.segment7 := p_rpr_rec.segment7_from;
521 p_trx_dtl_rec.segment8 := p_rpr_rec.segment8_from;
522 p_trx_dtl_rec.segment9 := p_rpr_rec.segment9_from;
523 p_trx_dtl_rec.segment10 := p_rpr_rec.segment10_from;
524 p_trx_dtl_rec.segment11 := p_rpr_rec.segment11_from;
525 p_trx_dtl_rec.segment12 := p_rpr_rec.segment12_from;
526 p_trx_dtl_rec.segment13 := p_rpr_rec.segment13_from;
527 p_trx_dtl_rec.segment14 := p_rpr_rec.segment14_from;
528 p_trx_dtl_rec.segment15 := p_rpr_rec.segment15_from;
529 p_trx_dtl_rec.segment16 := p_rpr_rec.segment16_from;
530 p_trx_dtl_rec.segment17 := p_rpr_rec.segment17_from;
531 p_trx_dtl_rec.segment18 := p_rpr_rec.segment18_from;
532 p_trx_dtl_rec.segment19 := p_rpr_rec.segment19_from;
533 p_trx_dtl_rec.segment20 := p_rpr_rec.segment20_from;
534 p_trx_dtl_rec.segment21 := p_rpr_rec.segment21_from;
535 p_trx_dtl_rec.segment22 := p_rpr_rec.segment22_from;
536 p_trx_dtl_rec.segment23 := p_rpr_rec.segment23_from;
537 p_trx_dtl_rec.segment24 := p_rpr_rec.segment24_from;
538 p_trx_dtl_rec.segment25 := p_rpr_rec.segment25_from;
539 p_trx_dtl_rec.segment26 := p_rpr_rec.segment26_from;
540 p_trx_dtl_rec.segment27 := p_rpr_rec.segment27_from;
541 p_trx_dtl_rec.segment28 := p_rpr_rec.segment28_from;
542 p_trx_dtl_rec.segment29 := p_rpr_rec.segment29_from;
543 p_trx_dtl_rec.segment30 := p_rpr_rec.segment30_from;
544 ELSE
545 p_trx_dtl_rec.budgeting_segments := p_rpr_rec.distribution_to;
546 p_trx_dtl_rec.segment1 := p_rpr_rec.segment1;
547 p_trx_dtl_rec.segment2 := p_rpr_rec.segment2;
548 p_trx_dtl_rec.segment3 := p_rpr_rec.segment3;
549 p_trx_dtl_rec.segment4 := p_rpr_rec.segment4;
550 p_trx_dtl_rec.segment5 := p_rpr_rec.segment5;
551 p_trx_dtl_rec.segment6 := p_rpr_rec.segment6;
552 p_trx_dtl_rec.segment7 := p_rpr_rec.segment7;
553 p_trx_dtl_rec.segment8 := p_rpr_rec.segment8;
554 p_trx_dtl_rec.segment9 := p_rpr_rec.segment9;
555 p_trx_dtl_rec.segment10 := p_rpr_rec.segment10;
556 p_trx_dtl_rec.segment11 := p_rpr_rec.segment11;
557 p_trx_dtl_rec.segment12 := p_rpr_rec.segment12;
558 p_trx_dtl_rec.segment13 := p_rpr_rec.segment13;
559 p_trx_dtl_rec.segment14 := p_rpr_rec.segment14;
560 p_trx_dtl_rec.segment15 := p_rpr_rec.segment15;
561 p_trx_dtl_rec.segment16 := p_rpr_rec.segment16;
562 p_trx_dtl_rec.segment17 := p_rpr_rec.segment17;
563 p_trx_dtl_rec.segment18 := p_rpr_rec.segment18;
564 p_trx_dtl_rec.segment19 := p_rpr_rec.segment19;
565 p_trx_dtl_rec.segment20 := p_rpr_rec.segment20;
566 p_trx_dtl_rec.segment21 := p_rpr_rec.segment21;
567 p_trx_dtl_rec.segment22 := p_rpr_rec.segment22;
568 p_trx_dtl_rec.segment23 := p_rpr_rec.segment23;
569 p_trx_dtl_rec.segment24 := p_rpr_rec.segment24;
570 p_trx_dtl_rec.segment25 := p_rpr_rec.segment25;
571 p_trx_dtl_rec.segment26 := p_rpr_rec.segment26;
572 p_trx_dtl_rec.segment27 := p_rpr_rec.segment27;
573 p_trx_dtl_rec.segment28 := p_rpr_rec.segment28;
574 p_trx_dtl_rec.segment29 := p_rpr_rec.segment29;
575 p_trx_dtl_rec.segment30 := p_rpr_rec.segment30;
576
577 END IF;
578
579 IF (p_count=1) THEN
580 p_trx_dtl_rec.increase_decrease_flag := 'D';
581 ELSE
582 p_trx_dtl_rec.increase_decrease_flag := 'I';
583 END IF;
584 p_trx_dtl_rec.amount := p_rpr_rec.amount;
585 p_trx_dtl_rec.sub_type := p_rpr_rec.sub_type;
586 p_trx_dtl_rec.gl_transfer_flag := 'N';
587 p_trx_dtl_rec.approved_by_user_id := NULL;
588 p_trx_dtl_rec.approved_by_user_id := NULL;
589 p_trx_dtl_rec.posting_process_id := NULL;
590 p_trx_dtl_rec.set_of_books_id := g_sob_id;
591 p_trx_dtl_rec.creation_date := g_sysdate;
592 p_trx_dtl_rec.created_by := g_user_id;
593 p_trx_dtl_rec.last_update_date := g_sysdate;
594 p_trx_dtl_rec.last_updated_by := g_user_id;
595 p_trx_dtl_rec.last_update_login := g_login_id;
596 p_trx_dtl_rec.public_law_code := p_rpr_rec.public_law_code;
597 p_trx_dtl_rec.advance_type := p_rpr_rec.advance_type;
598 p_trx_dtl_rec.dept_id := p_rpr_rec.dept_id;
599 p_trx_dtl_rec.main_account := p_rpr_rec.main_account;
600 p_trx_dtl_rec.transfer_description := p_rpr_rec.transfer_description;
601
602
603
604 EXCEPTION WHEN OTHERS THEN
605 g_retcode := 2;
606 g_errbuf := 'Error in set_dtl_fields procedure '|| SQLERRM;
607 Fv_Utility.Log_Mesg(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
608 '.final_exception',g_errbuf);
609
610 END; --procedure set_dtl_fields
611
612 PROCEDURE insert_hdr_record(p_trx_hdr_rec fv_be_trx_hdrs%ROWTYPE) IS
613 l_module_name VARCHAR2(200);
614
615 BEGIN
616 l_module_name := g_module_name || 'insert_hdr_record';
617
618 INSERT INTO fv_be_trx_hdrs
619 (doc_id ,
620 doc_number ,
621 revision_num ,
622 internal_revision_num ,
623 treasury_symbol_id ,
624 fund_value ,
625 budget_level_id ,
626 transaction_date ,
627 doc_status ,
628 doc_total ,
629 source ,
630 budgeting_segments ,
631 segment1 ,
632 segment2 ,
633 segment3 ,
634 segment4 ,
635 segment5 ,
636 segment6 ,
637 segment7 ,
638 segment8 ,
639 segment9 ,
640 segment10 ,
641 segment11 ,
642 segment12 ,
643 segment13 ,
644 segment14 ,
645 segment15 ,
646 segment16 ,
647 segment17 ,
648 segment18 ,
649 segment19 ,
650 segment20 ,
651 segment21 ,
652 segment22 ,
653 segment23 ,
654 segment24 ,
655 segment25 ,
656 segment26 ,
657 segment27 ,
658 segment28 ,
659 segment29 ,
660 segment30 ,
661 approval_id ,
662 approved_by_user_id ,
663 distribution_amount ,
664 old_doc_number ,
665 set_of_books_id ,
666 bu_group_id ,
667 creation_date ,
668 created_by ,
669 last_update_date ,
670 last_updated_by ,
671 last_update_login)
672 VALUES
673 (p_trx_hdr_rec.doc_id ,
674 p_trx_hdr_rec.doc_number ,
675 p_trx_hdr_rec.revision_num ,
676 p_trx_hdr_rec.internal_revision_num ,
677 p_trx_hdr_rec.treasury_symbol_id ,
678 p_trx_hdr_rec.fund_value ,
679 p_trx_hdr_rec.budget_level_id ,
680 TRUNC(p_trx_hdr_rec.transaction_date) ,
681 p_trx_hdr_rec.doc_status ,
682 p_trx_hdr_rec.doc_total ,
683 p_trx_hdr_rec.source ,
684 p_trx_hdr_rec.budgeting_segments ,
685 p_trx_hdr_rec.segment1 ,
686 p_trx_hdr_rec.segment2 ,
687 p_trx_hdr_rec.segment3 ,
688 p_trx_hdr_rec.segment4 ,
689 p_trx_hdr_rec.segment5 ,
690 p_trx_hdr_rec.segment6 ,
691 p_trx_hdr_rec.segment7 ,
692 p_trx_hdr_rec.segment8 ,
693 p_trx_hdr_rec.segment9 ,
694 p_trx_hdr_rec.segment10 ,
695 p_trx_hdr_rec.segment11 ,
696 p_trx_hdr_rec.segment12 ,
697 p_trx_hdr_rec.segment13 ,
698 p_trx_hdr_rec.segment14 ,
699 p_trx_hdr_rec.segment15 ,
700 p_trx_hdr_rec.segment16 ,
701 p_trx_hdr_rec.segment17 ,
702 p_trx_hdr_rec.segment18 ,
703 p_trx_hdr_rec.segment19 ,
704 p_trx_hdr_rec.segment20 ,
705 p_trx_hdr_rec.segment21 ,
706 p_trx_hdr_rec.segment22 ,
707 p_trx_hdr_rec.segment23 ,
708 p_trx_hdr_rec.segment24 ,
709 p_trx_hdr_rec.segment25 ,
710 p_trx_hdr_rec.segment26 ,
711 p_trx_hdr_rec.segment27 ,
712 p_trx_hdr_rec.segment28 ,
713 p_trx_hdr_rec.segment29 ,
714 p_trx_hdr_rec.segment30 ,
715 p_trx_hdr_rec.approval_id ,
716 p_trx_hdr_rec.approved_by_user_id ,
717 p_trx_hdr_rec.distribution_amount ,
718 p_trx_hdr_rec.old_doc_number ,
719 p_trx_hdr_rec.set_of_books_id ,
720 p_trx_hdr_rec.bu_group_id ,
721 p_trx_hdr_rec.creation_date ,
722 p_trx_hdr_rec.created_by ,
723 p_trx_hdr_rec.last_update_date ,
724 p_trx_hdr_rec.last_updated_by ,
725 p_trx_hdr_rec.last_update_login );
726
727 EXCEPTION WHEN OTHERS THEN
728 g_retcode := 2;
729 g_errbuf := 'Error in insert_hdr_record procedure '||SQLERRM;
730 Fv_Utility.Log_Mesg(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
731 '.final_exception',g_errbuf);
732
733 END; --procedure insert_hdr_record
734
735 PROCEDURE insert_dtl_record (p_trx_dtl_rec fv_be_trx_dtls%ROWTYPE) IS
736 l_module_name VARCHAR2(200);
737
738 BEGIN
739 l_module_name := g_module_name || 'insert_dtl_record';
740 INSERT INTO fv_be_trx_dtls
741 (transaction_id ,
742 doc_id ,
743 revision_num ,
744 transaction_status ,
745 gl_date ,
746 quarter_num ,
747 transaction_type_id ,
748 budgeting_segments ,
749 segment1 ,
750 segment2 ,
751 segment3 ,
752 segment4 ,
753 segment5 ,
754 segment6 ,
755 segment7 ,
756 segment8 ,
757 segment9 ,
758 segment10 ,
759 segment11 ,
760 segment12 ,
761 segment13 ,
762 segment14 ,
763 segment15 ,
764 segment16 ,
765 segment17 ,
766 segment18 ,
767 segment19 ,
768 segment20 ,
769 segment21 ,
770 segment22 ,
771 segment23 ,
772 segment24 ,
773 segment25 ,
774 segment26 ,
775 segment27 ,
776 segment28 ,
777 segment29 ,
778 segment30 ,
779 increase_decrease_flag ,
780 amount ,
781 public_law_code ,
782 advance_type ,
783 dept_id ,
784 main_account ,
785 transfer_description ,
786 sub_type ,
787 gl_transfer_flag ,
788 approved_by_user_id ,
789 posting_process_id ,
790 set_of_books_id ,
791 creation_date ,
792 created_by ,
793 last_update_date ,
794 last_updated_by ,
795 last_update_login )
796 VALUES
797 (p_trx_dtl_rec.transaction_id ,
798 p_trx_dtl_rec.doc_id ,
799 p_trx_dtl_rec.revision_num ,
800 p_trx_dtl_rec.transaction_status ,
801 TRUNC(p_trx_dtl_rec.gl_date) ,
802 p_trx_dtl_rec.quarter_num ,
803 p_trx_dtl_rec.transaction_type_id ,
804 p_trx_dtl_rec.budgeting_segments ,
805 p_trx_dtl_rec.segment1 ,
806 p_trx_dtl_rec.segment2 ,
807 p_trx_dtl_rec.segment3 ,
808 p_trx_dtl_rec.segment4 ,
809 p_trx_dtl_rec.segment5 ,
810 p_trx_dtl_rec.segment6 ,
811 p_trx_dtl_rec.segment7 ,
812 p_trx_dtl_rec.segment8 ,
813 p_trx_dtl_rec.segment9 ,
814 p_trx_dtl_rec.segment10 ,
815 p_trx_dtl_rec.segment11 ,
816 p_trx_dtl_rec.segment12 ,
817 p_trx_dtl_rec.segment13 ,
818 p_trx_dtl_rec.segment14 ,
819 p_trx_dtl_rec.segment15 ,
820 p_trx_dtl_rec.segment16 ,
821 p_trx_dtl_rec.segment17 ,
822 p_trx_dtl_rec.segment18 ,
823 p_trx_dtl_rec.segment19 ,
824 p_trx_dtl_rec.segment20 ,
825 p_trx_dtl_rec.segment21 ,
826 p_trx_dtl_rec.segment22 ,
827 p_trx_dtl_rec.segment23 ,
828 p_trx_dtl_rec.segment24,
829 p_trx_dtl_rec.segment25 ,
830 p_trx_dtl_rec.segment26 ,
831 p_trx_dtl_rec.segment27 ,
832 p_trx_dtl_rec.segment28 ,
833 p_trx_dtl_rec.segment29 ,
834 p_trx_dtl_rec.segment30 ,
835 p_trx_dtl_rec.increase_decrease_flag ,
836 p_trx_dtl_rec.amount ,
837 p_trx_dtl_rec.public_law_code ,
838 p_trx_dtl_rec.advance_type ,
839 p_trx_dtl_rec.dept_id ,
840 p_trx_dtl_rec.main_account ,
841 p_trx_dtl_rec.transfer_description ,
842 p_trx_dtl_rec.sub_type ,
843 p_trx_dtl_rec.gl_transfer_flag ,
844 p_trx_dtl_rec.approved_by_user_id ,
845 p_trx_dtl_rec.posting_process_id ,
846 p_trx_dtl_rec.set_of_books_id ,
847 p_trx_dtl_rec.creation_date ,
848 p_trx_dtl_rec.created_by ,
849 p_trx_dtl_rec.last_update_date ,
850 p_trx_dtl_rec.last_updated_by ,
851 p_trx_dtl_rec.last_update_login );
852
853 EXCEPTION WHEN OTHERS THEN
854 g_retcode := 2;
855 g_errbuf := 'Error in insert_dtl_record procedure '||SQLERRM;
856 Fv_Utility.Log_Mesg(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
857 '.final_exception',g_errbuf);
858
859 END; --procedure insert_dtl_record
860
861 PROCEDURE reset_doc_status(p_from_doc_id NUMBER, p_to_doc_id NUMBER) IS
862 l_module_name VARCHAR2(200);
863
864 BEGIN
865 l_module_name := g_module_name || 'reset_doc_status';
866 UPDATE fv_be_trx_hdrs
867 SET doc_status = 'IN',
868 approved_by_user_id = NULL
869 WHERE doc_id IN (p_from_doc_id, p_to_doc_id);
870
871 UPDATE fv_be_trx_dtls
872 SET transaction_status = 'IN'
873 WHERE doc_id IN (p_from_doc_id, p_to_doc_id);
874
875 COMMIT;
876
877 EXCEPTION WHEN OTHERS THEN
878 g_retcode := 2;
879 g_errbuf := 'Error in reset_doc_status procedure '||SQLERRM;
880 Fv_Utility.Log_Mesg(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
881 '.final_exception',g_errbuf);
882 END;
883 BEGIN
884 g_module_name := 'fv.plsql.fv_be_rpr_pkg.';
885 END fv_be_rpr_pkg; -- Package body