DBA Data[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