[Home] [Help]
PACKAGE BODY: APPS.GMS_REPORT_SF425
Source
4
1 PACKAGE BODY GMS_REPORT_SF425 AS
2 --$Header: gmsffrcb.pls 120.13 2011/07/18 13:40:36 lamalviy noship $
3
5
6 Procedure Populate_425_History(
7 p_award_id IN NUMBER,
8 p_report_end_date IN DATE
9 ) IS
10
11 CURSOR last_version_csr (p_award_id IN NUMBER) IS
12 select nvl(max(version),0)
13 from gms_425_history
14 where award_id = p_award_id
15 and status_code IN ('F');
16
17 CURSOR last_version_details_csr (p_version_number IN NUMBER, p_award_id IN NUMBER) IS
18 select
19 REPORT_TRN_ID REPORT_TRN_ID,
20 nvl(report_type_code,'QUARTERLY') REPORT_TYPE_CODE,
21 REPORT_END_DATE REPORT_END_DATE,
22 nvl(BASIS_OF_ACCNT_CODE,'A') BASIS_OF_ACCNT_CODE,
23 nvl(CASH_RECEIPTS_AMT,0) CASH_RECEIPTS_AMT,
24 nvl(CASH_DISBURSEMENTS_AMT,0) CASH_DISBURSEMENTS_AMT,
25 nvl(TOTAL_FED_FUNDS_AUTH_AMT,0) TOTAL_FED_FUNDS_AUTH_AMT,
26 nvl(FED_SHARE_OF_EXP_AMT,0) FED_SHARE_OF_EXP_AMT,
27 nvl(FED_SHARE_OF_UNLIQ_OBL_AMT,0) FED_SHARE_OF_UNLIQ_OBL_AMT,
28 nvl(TOTAL_RECPT_SHARE_REQ_AMT,0) TOTAL_RECPT_SHARE_REQ_AMT,
29 nvl(RECPT_SHARE_EXP_AMT,0) RECPT_SHARE_EXP_AMT,
30 nvl(TOTAL_FED_PRG_INC_EARN_AMT,0) TOTAL_FED_PRG_INC_EARN_AMT,
31 nvl(PRG_INC_EXP_DEDUCT_ALT_AMT,0) PRG_INC_EXP_DEDUCT_ALT_AMT,
32 nvl(PRG_INC_EXP_ADD_ALT_AMT,0) PRG_INC_EXP_ADD_ALT_AMT
33 from gms_425_history
34 where award_id = p_award_id
35 and status_code IN ('F')
36 and version = p_version_number;
37
38 CURSOR award_details_csr (p_award_id IN NUMBER) IS
39 select
40 end_date_active , -- Check with SHweta if this is correct
41 start_date_active
42 from GMS_AWARDS
43 where award_id = p_award_id;
44
45 CURSOR c_sum_amount (
46 p_award_id IN NUMBER,
47 p_report_start_date IN DATE,
48 p_report_end_date IN DATE) IS
49 Select SUM(nvl(c.amount,0))
50 from pa_expenditure_items_all ei,
51 pa_cost_distribution_lines_all c,
52 gms_award_distributions g
53 where --added by rkuttiya for bug 9117372
54 --c.gl_date between p_report_start_date and p_report_end_date
55 trunc(c.gl_date) < trunc(p_report_end_date)
56 and c.expenditure_item_id = ei.expenditure_item_id
57 and g.award_id = p_award_id
58 and g.document_type = 'EXP'
59 and g.adl_line_num = 1
60 and g.adl_status = 'A'
61 and g.expenditure_item_id = c.expenditure_item_id
62 and c.line_type = 'R'
63 and ei.system_linkage_function <> 'BTC' -- Put the correct code for system linkage function
64 and ei.project_id in ( select gbv.project_id
65 from gms_budget_versions gbv
66 where gbv.budget_type_code = 'AC'
67 and gbv.budget_status_code in ('S','W' )
68 and gbv.award_id = p_award_id );
69
70 CURSOR c_sum_burden (
71 p_award_id IN NUMBER,
72 p_report_start_date IN DATE,
73 p_report_end_date IN DATE) IS
74 Select sum(nvl(bv.burden_cost,0))
75 FROM gms_cdl_burden_detail_v bv,
76 gms_budget_versions gbv
77 WHERE --added by rkuttiya for bug 9117372
78 -- bv.gl_date between p_report_start_date and p_report_end_date
79 trunc(bv.gl_date) < trunc(p_report_end_date)
80 and bv.award_id = p_award_id
81 and bv.line_type = 'R'
82 and bv.system_linkage_function <> 'BTC' -- Put the correct code for system linkage function
83 and gbv.budget_type_code = 'AC'
84 and gbv.budget_status_code in ('S','W' )
85 and gbv.award_id = p_award_id
86 and bv.project_id = gbv.project_id;
87
88 CURSOR total_outlay_raw ( p_award_id IN NUMBER,
89 p_report_start_date IN DATE,
90 p_report_end_date IN DATE) IS
91 Select
92 nvl(c.amount,0) raw_cost,
93 c.expenditure_item_id,
94 c.line_num
95 from pa_expenditure_items ei,
96 pa_cost_distribution_lines_all c,
97 gms_award_distributions g
98 where g.expenditure_item_id = c.expenditure_item_id
99 and g.cdl_line_num = c.line_num
100 -- rkuttiya added for bug 9117372
101 -- and c.gl_date between p_report_start_date and p_report_end_date
102 and trunc(c.gl_date) < trunc(p_report_end_date)
103 --
104 and c.expenditure_item_id = ei.expenditure_item_id
105 and g.award_id = p_award_id
106 and g.document_type = 'EXP'
107 and g.adl_status = 'A'
108 and c.line_type = 'R'
109 and nvl(ei.system_linkage_function,'XXX') <> 'BTC'
110 and ei.project_id in ( select gbv.project_id
111 from gms_budget_versions gbv
112 where gbv.budget_type_code = 'AC'
113 and gbv.budget_status_code in ('S','W' )
114 and gbv.award_id = p_award_id );
115
116
117 CURSOR total_outlay_burden ( p_award_id IN NUMBER,
118 p_expenditure_item_id IN NUMBER,
119 p_line_num IN NUMBER) IS
120 Select
121 sum(nvl(bv.burden_cost,0)) burden_cost,
122 gcd.report_direct_flag report_direct_flag
123 from
124 gms_awards a,
125 GMS_CDL_BURDEN_DETAIL_V bv,
126 pa_ind_cost_codes cd,
127 gms_ind_cost_codes gcd,
128 gms_allowable_expenditures ae
129 where
130 bv.expenditure_item_id = p_expenditure_item_id
131 and bv.line_num = p_line_num
132 and a.award_id = p_award_id
133 and bv.ind_cost_code = cd.ind_cost_code
134 and ae.allowability_schedule_id = a.allowable_schedule_id
135 and bv.ei_expenditure_type = ae.expenditure_type
136 and nvl(ae.mtdc_exempt_flag,'N') = 'N'
137 and cd.ind_cost_code = gcd.ind_cost_code(+)
138 group by
139 bv.expenditure_item_id
140 ,bv.line_num
141 ,gcd.report_direct_flag;
142
143 -- Get the Federal Unobligated Commitments
144 CURSOR c_burdened_cost ( p_award_id IN NUMBER,
145 p_report_start_date IN DATE,
146 p_report_end_date IN DATE) IS
147 SELECT sum(burdened_cost)
148 FROM (SELECT sum(nvl(GB.encumb_period_to_date,0) * decode(balance_type, 'PO', 1, 'AP' , 1, 'ENC' , 1, 0)) burdened_cost
149 FROM GMS_BALANCES GB, GMS_BUDGET_VERSIONS GBV
150 WHERE gb.award_id = p_award_id
151 AND GBV.award_id = GB.award_id
152 AND GBV.budget_version_id = gb.budget_version_id
153 AND GBV.current_flag in ('Y','R')
154 AND GBV.budget_status_code = 'B'
155 AND trunc(gb.start_date) <= trunc(p_report_end_date) -- added to get the cumulative commitments
156 GROUP BY GB.award_id
157 UNION ALL
158 SELECT sum((nvl(gbc.entered_dr,0)- nvl(gbc.entered_cr,0)) * decode(gbc.document_type,'PO',1,'AP',1,'ENC',1,0)) burdened_cost
159 FROM gms_bc_packets gbc, GMS_BUDGET_VERSIONS GBV
160 WHERE gbv.budget_version_id = gbc.budget_version_id
161 AND gbc.status_code = 'A'
162 AND GBV.budget_status_code = 'B'
163 AND GBV.current_flag in ('Y', 'R')
164 AND gbc.award_id = p_award_id
165 AND trunc(gbc.expenditure_item_date) <= trunc(p_report_end_date) -- added to get additional commitments
166 GROUP BY GBC.award_id) ;
167
168 l_status_code gms_425_history.status_code%type;
169 l_creation_date gms_425_history.creation_date%type;
170 l_created_by gms_425_history.created_by%type;
171 l_last_update_date gms_425_history.last_update_date%type;
172 l_last_updated_by gms_425_history.last_updated_by%type;
173 l_last_update_login gms_425_history.last_update_login%type;
174 l_REPORT_TRN_ID gms_425_history.REPORT_TRN_ID%type;
175 l_BASIS_OF_ACCNT_CODE gms_425_history.BASIS_OF_ACCNT_CODE%type;
176 l_REPORT_TYPE_CODE gms_425_history.REPORT_TYPE_CODE%type;
177 l_CASH_RECEIPTS_AMT gms_425_history.CASH_RECEIPTS_AMT%type;
178 l_CASH_DISBURSEMENTS_AMT gms_425_history.CASH_DISBURSEMENTS_AMT%type;
179 l_TOTAL_FED_FUNDS_AUTH_AMT gms_425_history.TOTAL_FED_FUNDS_AUTH_AMT%type;
180 l_FED_SHARE_OF_EXP_AMT gms_425_history.FED_SHARE_OF_EXP_AMT%type;
181 l_FED_SHARE_OF_UNLIQ_OBL_AMT gms_425_history.FED_SHARE_OF_UNLIQ_OBL_AMT%type;
182 l_TOTAL_RECPT_SHARE_REQ_AMT gms_425_history.TOTAL_RECPT_SHARE_REQ_AMT%type;
183 l_RECPT_SHARE_EXP_AMT gms_425_history.RECPT_SHARE_EXP_AMT%type;
184 l_TOTAL_FED_PRG_INC_EARN_AMT gms_425_history.TOTAL_FED_PRG_INC_EARN_AMT%type;
185 l_PRG_INC_EXP_DEDUCT_ALT_AMT gms_425_history.PRG_INC_EXP_DEDUCT_ALT_AMT%type;
186 l_PRG_INC_EXP_ADD_ALT_AMT gms_425_history.PRG_INC_EXP_ADD_ALT_AMT%type;
187 l_REMARKS gms_425_history.REMARKS%type;
188 l_NAME gms_425_history.NAME%type;
189 l_TELEPHONE gms_425_history.TELEPHONE%type;
190 l_EMAIL gms_425_history.EMAIL%type;
191 l_REPORT_SUBMIT_DATE gms_425_history.REPORT_SUBMIT_DATE%type;
192
193 l_EXPENSE_TRN_ID gms_425_expense.EXPENSE_TRN_ID%type;
194 l_INDIRECT_EXP_TYPE_CODE gms_425_expense.INDIRECT_EXP_TYPE_CODE%type;
195 l_INDIRECT_EXP_RATE gms_425_expense.INDIRECT_EXP_RATE%type;
196 l_INDIRECT_EXP_PERIOD_FROM gms_425_expense.INDIRECT_EXP_PERIOD_FROM%type;
197 l_INDIRECT_EXP_PERIOD_TO gms_425_expense.INDIRECT_EXP_PERIOD_TO%type;
198 l_INDIRECT_EXP_BASE_AMT gms_425_expense.INDIRECT_EXP_BASE_AMT%type;
199 l_INDIRECT_EXP_FED_SHARE_AMT gms_425_expense.INDIRECT_EXP_FED_SHARE_AMT%type;
200
201 l_TEMP_FED_SHARE_OF_EXP_AMT gms_425_history.FED_SHARE_OF_EXP_AMT%type; /* bug 12760021 */
202
203 last_version_details_rec last_version_details_csr%ROWTYPE;
204 l_last_version NUMBER;
205 l_current_version NUMBER;
206 l_award_end_date DATE;
207 l_award_start_date DATE;
208 l_last_report_trn_id NUMBER;
209 l_period_start_date DATE;
210 l_Sum_Raw_Cost NUMBER;
211 l_Sum_Burden_Cost NUMBER;
212 l_expenditure_item_id NUMBER;
213 l_line_num NUMBER;
214 l_burden_cost NUMBER;
215 l_run_date DATE;
216
217
218 Procedure insert_425_history (p_status_code IN gms_425_history.status_code%type) is
219 Begin
220
221 -- set the transaction id
222 select gms_425_history_report_id_s.nextval
223 into l_report_trn_id
224 from dual;
225
226 insert into gms_425_history (
227 REPORT_TRN_ID,
228 award_id,
229 version,
230 status_code,
231 creation_date,
232 created_by,
233 last_update_date,
234 last_updated_by,
235 last_update_login,
236 run_date,
237 report_start_date,
238 grant_period_from_date,
239 grant_period_to_date,
240 BASIS_OF_ACCNT_CODE,
241 REPORT_TYPE_CODE,
245 TOTAL_FED_FUNDS_AUTH_AMT,
242 REPORT_END_DATE,
243 CASH_RECEIPTS_AMT,
244 CASH_DISBURSEMENTS_AMT,
246 FED_SHARE_OF_EXP_AMT,
247 FED_SHARE_OF_UNLIQ_OBL_AMT,
248 TOTAL_RECPT_SHARE_REQ_AMT,
249 RECPT_SHARE_EXP_AMT,
250 TOTAL_FED_PRG_INC_EARN_AMT,
251 PRG_INC_EXP_DEDUCT_ALT_AMT,
252 PRG_INC_EXP_ADD_ALT_AMT,
253 REMARKS,
254 NAME,
255 TELEPHONE,
256 EMAIL,
257 REPORT_SUBMIT_DATE
258 )
259 Values
260 (
261 l_REPORT_TRN_ID,
262 p_award_id,
263 l_current_version,
264 p_status_code,
265 l_creation_date,
266 l_created_by,
267 l_last_update_date,
268 l_last_updated_by,
269 l_last_update_login,
270 l_run_date,
271 l_period_start_date,
272 l_award_start_date,
273 l_award_end_date,
274 l_BASIS_OF_ACCNT_CODE,
275 l_REPORT_TYPE_CODE,
276 p_REPORT_END_DATE,
277 l_CASH_RECEIPTS_AMT,
278 l_CASH_DISBURSEMENTS_AMT,
279 l_TOTAL_FED_FUNDS_AUTH_AMT,
280 l_FED_SHARE_OF_EXP_AMT,
281 l_FED_SHARE_OF_UNLIQ_OBL_AMT,
282 l_TOTAL_RECPT_SHARE_REQ_AMT,
283 l_RECPT_SHARE_EXP_AMT,
284 l_TOTAL_FED_PRG_INC_EARN_AMT,
285 l_PRG_INC_EXP_DEDUCT_ALT_AMT,
286 l_PRG_INC_EXP_ADD_ALT_AMT,
287 l_REMARKS,
288 l_NAME,
289 l_TELEPHONE,
290 l_EMAIL,
291 l_REPORT_SUBMIT_DATE
292 );
293
294 commit; -- added to commit the transaction for bug 8965790
295 Exception
296 When others then
297 raise;
298 End insert_425_history;
299
300 -- create as many records as last versions expenses
301 Procedure insert_425_expense (p_last_report_trn_id IN NUMBER) IS
302
303 -- Get last versions details
304 CURSOR get_last_version_expenses_csr (p_last_report_trn_id IN NUMBER) IS
305 SELECT
306 INDIRECT_EXP_TYPE_CODE,
307 INDIRECT_EXP_RATE,
308 INDIRECT_EXP_PERIOD_FROM,
309 INDIRECT_EXP_PERIOD_TO,
310 INDIRECT_EXP_BASE_AMT,
311 INDIRECT_EXP_FED_SHARE_AMT
312 FROM
313 GMS_425_EXPENSE exp
314 WHERE
315 exp.report_trn_id = p_last_report_trn_id;
316 Begin
317
318 -- loop through the previous versions expenses and insert same values again
319 FOR get_last_version_expenses_rec IN get_last_version_expenses_csr (p_last_report_trn_id) LOOP
320
321 -- set the transaction id
322 select gms_425_expenses_id_s.nextval
323 into l_EXPENSE_TRN_ID
324 from dual;
325
326 insert into gms_425_expense (
327 EXPENSE_TRN_ID,
328 REPORT_TRN_ID,
329 creation_date,
330 created_by,
331 last_update_date,
332 last_updated_by,
333 last_update_login,
334 INDIRECT_EXP_TYPE_CODE,
335 INDIRECT_EXP_RATE,
336 INDIRECT_EXP_PERIOD_FROM,
337 INDIRECT_EXP_PERIOD_TO,
338 INDIRECT_EXP_BASE_AMT,
339 INDIRECT_EXP_FED_SHARE_AMT
340 )
341 Values
342 (
343 l_EXPENSE_TRN_ID,
344 l_REPORT_TRN_ID,
345 l_creation_date,
346 l_created_by ,
347 l_last_update_date ,
348 l_last_updated_by ,
349 l_last_update_login,
350 get_last_version_expenses_rec.INDIRECT_EXP_TYPE_CODE,
351 get_last_version_expenses_rec.INDIRECT_EXP_RATE,
352 get_last_version_expenses_rec.INDIRECT_EXP_PERIOD_FROM,
353 get_last_version_expenses_rec.INDIRECT_EXP_PERIOD_TO,
354 get_last_version_expenses_rec.INDIRECT_EXP_BASE_AMT,
355 get_last_version_expenses_rec.INDIRECT_EXP_FED_SHARE_AMT
356 );
357
358 commit ; -- added to commit the transaction fix for bug 8965790
359 END LOOP;
360 Exception
361 When others then
362 raise;
363 End insert_425_expense;
364
365 Begin
366
367 --1. Basic defaults
368
369 -- for gms_425_history
370 l_creation_date := trunc(sysdate);
371 l_created_by := fnd_global.user_id;
372 l_last_update_date := trunc(sysdate);
373 l_last_updated_by := fnd_global.user_id;
374 l_last_update_login := fnd_global.login_id;
375 l_run_date := trunc(sysdate);
376
377 l_REMARKS := null;
378 l_NAME := null;
379 l_TELEPHONE := null;
380 l_EMAIL := null;
381 l_REPORT_SUBMIT_DATE := null;
382
383 l_REPORT_TYPE_CODE := 'QUARTERLY';
384 l_BASIS_OF_ACCNT_CODE := 'A'; -- accrual
385 l_last_report_trn_id := -1;
386
387 l_CASH_RECEIPTS_AMT := 0;
388 l_CASH_DISBURSEMENTS_AMT := 0;
389 l_TOTAL_FED_FUNDS_AUTH_AMT := 0;
390 l_FED_SHARE_OF_EXP_AMT := 0;
391 l_FED_SHARE_OF_UNLIQ_OBL_AMT := 0;
392 l_TOTAL_RECPT_SHARE_REQ_AMT := 0;
393 l_RECPT_SHARE_EXP_AMT := 0;
394 l_TOTAL_FED_PRG_INC_EARN_AMT := 0;
395 l_PRG_INC_EXP_DEDUCT_ALT_AMT := 0;
396 l_PRG_INC_EXP_ADD_ALT_AMT := 0;
397
398 l_TEMP_FED_SHARE_OF_EXP_AMT := 0; /*bug 12760021 */
399
400 -- Get award details
401 OPEN award_details_csr (p_award_id);
402 FETCH award_details_csr INTO l_award_end_date, l_award_start_date;
403 CLOSE award_details_csr;
404
405 l_period_start_date := trunc(l_award_start_date);
406
407 --2. Get last version and details and set current version and amounts and details
408
409 OPEN last_version_csr (p_award_id);
410 FETCH last_version_csr INTO l_last_version;
411 CLOSE last_version_csr;
412
413 -- override defaults if last version exists
414 IF l_last_version > 0 THEN -- earlier version exist
415
419
416 OPEN last_version_details_csr (l_last_version, p_award_id);
417 FETCH last_version_details_csr INTO last_version_details_rec;
418 CLOSE last_version_details_csr;
420 l_REPORT_TYPE_CODE := last_version_details_rec.report_type_code;
421
422 l_BASIS_OF_ACCNT_CODE := last_version_details_rec.BASIS_OF_ACCNT_CODE;
423
424 l_last_report_trn_id := last_version_details_rec.REPORT_TRN_ID;
425
426 l_period_start_date := trunc(last_version_details_rec.REPORT_END_DATE) + 1;
427
428 -- set previous reports amounts as all of these are cumulative values
429 l_CASH_RECEIPTS_AMT := last_version_details_rec.CASH_RECEIPTS_AMT;
430 l_CASH_DISBURSEMENTS_AMT := last_version_details_rec.CASH_DISBURSEMENTS_AMT;
431 l_TOTAL_FED_FUNDS_AUTH_AMT := last_version_details_rec.TOTAL_FED_FUNDS_AUTH_AMT;
432 l_FED_SHARE_OF_EXP_AMT := last_version_details_rec.FED_SHARE_OF_EXP_AMT;
433 l_FED_SHARE_OF_UNLIQ_OBL_AMT := last_version_details_rec.FED_SHARE_OF_UNLIQ_OBL_AMT;
434 l_TOTAL_RECPT_SHARE_REQ_AMT := last_version_details_rec.TOTAL_RECPT_SHARE_REQ_AMT;
435 l_RECPT_SHARE_EXP_AMT := last_version_details_rec.RECPT_SHARE_EXP_AMT;
436 l_TOTAL_FED_PRG_INC_EARN_AMT := last_version_details_rec.TOTAL_FED_PRG_INC_EARN_AMT;
437 l_PRG_INC_EXP_DEDUCT_ALT_AMT := last_version_details_rec.PRG_INC_EXP_DEDUCT_ALT_AMT;
438 l_PRG_INC_EXP_ADD_ALT_AMT := last_version_details_rec.PRG_INC_EXP_ADD_ALT_AMT;
439
440 END IF;
441
442 /* Bug# 10378446 : Get the period federal fund */
443
444 select sum(nvl(direct_cost,0)) + sum(nvl(indirect_cost,0))
445 into l_total_fed_funds_auth_amt
446 from gms_installments
447 where award_id = p_award_id
448 and start_date_active <= p_report_end_date;
449
450
451 -- ( l_period_start_date between start_date_active and end_date_active
452 -- or p_report_end_date between start_date_active and end_date_active );
453
454 l_current_version := l_last_version + 1;
455
456
457
458 -- Override Report Type Code If Report End Date is Award End Date then use FINAL as REPORT TYPE CODE
459 IF trunc(p_report_end_date) >= trunc(l_award_end_date) THEN
460
461 l_REPORT_TYPE_CODE := 'FINAL';
462
463 END IF;
464
465 --3. Add current period value to Cash Disbursements (code from SF272)
466
467 -- get current period raw cost
468 open c_sum_amount (p_award_id, l_period_start_date, p_report_end_date);
469 fetch c_sum_amount into l_Sum_Raw_Cost ;
470 close c_sum_amount ;
471
472 -- get current period burden cost
473 open c_sum_burden (p_award_id, l_period_start_date, p_report_end_date);
474 fetch c_sum_burden into l_Sum_Burden_Cost ;
475 close c_sum_burden ;
476
477 -- Add current period raw cost and current period burden cost to cumulative cash disbursements
478 -- rkuttiya commented the following for bug 9117372
479 -- l_CASH_DISBURSEMENTS_AMT := l_CASH_DISBURSEMENTS_AMT + (NVL(l_Sum_Raw_Cost,0) + NVL(l_Sum_Burden_Cost,0)) ;
480
481 --rkuttiya added for bug 9117372
482 -- the query itself will fetch cumulative amounts
483 l_cash_disbursements_amt := (NVL(l_sum_raw_cost,0) +
484 nvl(l_sum_burden_cost,0));
485
486 --4. Add current period value to Federal Share of expenditures (code from SF269)
487
488 -- loop through raw costs for current period
489
490 For Exp_item_rec in total_outlay_raw (p_award_id, l_period_start_date, p_report_end_date) LOOP
491
492 l_expenditure_item_id := Exp_item_rec.expenditure_item_id;
493
494 l_line_num := Exp_item_rec.line_num;
495
496 -- Add current period raw cost to cumulative fed share of expenditure amount
497 /* for bug: 12760021 : Added if condition --rkuttiya commented and added for bug 9117372 */
498
499 IF l_last_version > 0 THEN -- earlier version exist
500 l_fed_share_of_exp_amt := NVL(l_TEMP_FED_SHARE_OF_EXP_AMT,0) + NVL(exp_item_rec.raw_cost,0);
501 ElSE
502 l_FED_SHARE_OF_EXP_AMT := NVL(l_FED_SHARE_OF_EXP_AMT,0) + NVL(exp_item_rec.raw_cost,0) ;
503 END IF;
504
505 -- loop through burden costs for raw cost
506 For Exp_item_rec_1 in total_outlay_burden (p_award_id, l_expenditure_item_id, l_line_num) LOOP
507
508 -- Add current period burden cost to cumulative fed share of expenditure amount
509 l_FED_SHARE_OF_EXP_AMT := NVL(l_FED_SHARE_OF_EXP_AMT,0 ) + exp_item_rec_1.burden_cost;
510
511 l_TEMP_FED_SHARE_OF_EXP_AMT := l_FED_SHARE_OF_EXP_AMT ; /*bug 12760021 */
512
513 End loop ;
514 End Loop;
515
516
520 FETCH c_burdened_cost INTO l_burden_cost ;
517 --5. Fetch total Federal Share of unliquidated obligations (code from SF269)
518
519 OPEN c_burdened_cost (p_award_id, l_period_start_date, p_report_end_date);
521 CLOSE c_burdened_cost ;
522
523 l_FED_SHARE_OF_UNLIQ_OBL_AMT := nvl(l_burden_cost,0); -- Rmunjulu Added nvl to get a zero value instead of null
524
525 --6. Create 2 records, one original and one draft in 425_history.
526 -- Carry forward last versions indirect expenses and create same records for both Original and Draft versions status in 425_expense
527
528 l_status_code := 'O';
529
530 insert_425_history (l_status_code);
531
532 -- create expenses based on last versions expenses
533 IF l_last_report_trn_id <> -1 THEN
534
535 insert_425_expense (l_last_report_trn_id);
536
537 END IF;
538
539 l_status_code := 'D';
540
541 insert_425_history(l_status_code);
542
543 -- create expenses based on last versions expenses
544 IF l_last_report_trn_id <> -1 THEN
545
546 insert_425_expense (l_last_report_trn_id);
547
548 END IF;
549
550 End Populate_425_History;
551 End GMS_REPORT_SF425;
552