[Home] [Help]
PACKAGE BODY: APPS.GMS_REPORT_SF269
Source
1 PACKAGE BODY GMS_REPORT_SF269 AS
2 --$Header: gmsgrflb.pls 120.0 2005/05/29 11:46:11 appldev noship $
3 Procedure Populate_269_History(RETCODE OUT NOCOPY VARCHAR2,
4 ERRBUF OUT NOCOPY VARCHAR2,
5 x_award_id IN NUMBER,
6 x_report_start_date IN DATE,
7 x_report_end_date IN DATE
8 ) IS
9
10 l_expenditure_item_id1 pa_cost_distribution_lines_all.expenditure_item_id%type;
11 l_line_num1 pa_cost_distribution_lines_all.line_num%type;
12
13 -- Cursor to Get the the total_outlay from raw cost and burdened cost of expenditure item
14 -- The cursor is split into two for performance reasons
15 -- BUG 4005793 : FPM Perf. fixes. |
16 --
17 cursor total_outlay_raw IS
18 Select nvl(c.amount,0) raw_cost,
19 c.expenditure_item_id ,
20 c.line_num
21 from pa_expenditure_items ei,
22 pa_cost_distribution_lines_all c,
23 gms_award_distributions g
24 where g.expenditure_item_id = c.expenditure_item_id
25 and g.cdl_line_num = c.line_num
26 and c.gl_date between X_Report_Start_Date
27 and X_Report_End_Date
31 and g.adl_status = 'A' -- BUG 4005793 : FPM Perf. fixes.
28 and c.expenditure_item_id = ei.expenditure_item_id
29 and g.award_id = X_Award_Id
30 and g.document_type = 'EXP' -- BUG 4005793 : FPM Perf. fixes.
32 and c.line_type = 'R'
33 and nvl(ei.system_linkage_function,'XXX') <> 'BTC'
34 --- BUG 4005793 : FPM Perf. fixes.
35 and ei.project_id in ( select gbv.project_id
36 from gms_budget_versions gbv
37 where gbv.budget_type_code = 'AC'
38 and gbv.budget_status_code in ('S','W' )
39 and gbv.award_id = X_award_id );
40
41 -- and c.transfer_status_code in ('A','V') -- Bug Fix 2701130
42 -- and c.reversed_flag is NULL Bug Fix 2831665.
43 -- and c.line_num_reversed is NULL Bug Fix 2831665.
44
45 --Added the report_direct_flag to fix the bug 924274.
46 cursor total_outlay_burden IS
47 Select
48 sum(nvl(bv.burden_cost,0)) burden_cost,
49 gcd.report_direct_flag report_direct_flag
50 from gms_awards a,
51 GMS_CDL_BURDEN_DETAIL_V bv,
52 pa_ind_cost_codes cd,
53 gms_ind_cost_codes gcd,
54 gms_allowable_expenditures ae
55 where
56 bv.expenditure_item_id = l_expenditure_item_id1
57 and bv.line_num = l_line_num1
58 and a.award_id = x_award_id
59 and bv.ind_cost_code = cd.ind_cost_code
60 and ae.allowability_schedule_id = a.allowable_schedule_id
61 and bv.ei_expenditure_type = ae.expenditure_type
62 and nvl(ae.mtdc_exempt_flag,'N') = 'N'
63 and cd.ind_cost_code = gcd.ind_cost_code(+) -- Added outerjoin to fix bug 2651959
64 group by
65 bv.expenditure_item_id
66 ,bv.line_num,
67 gcd.report_direct_flag;
68
69
70 l_award_id gms_269_history.award_id%type;
71 l_version gms_269_history.version%type;
72 l_status_code gms_269_history.status_code%type;
73 l_report_code gms_269_history.report_code%type;
74 l_creation_date gms_269_history.creation_date%type;
75 l_created_by gms_269_history.created_by%type;
76 l_last_update_date gms_269_history.last_update_date%type;
77 l_last_updated_by gms_269_history.last_updated_by%type;
78 l_last_update_login gms_269_history.last_update_login%type;
79 l_end_date gms_269_history.end_date%type;
80 l_document_number gms_269_history.document_number%type;
81 l_accounting_basis gms_269_history.accounting_basis%type;
82 l_funding_start_date gms_269_history.funding_start_date%type;
83 l_funding_end_date gms_269_history.funding_end_date%type;
84 l_report_period_start_date gms_269_history.report_period_start_date%type;
85 l_report_period_end_date gms_269_history.report_period_end_date%type;
86 l_total_outlay gms_269_history.total_outlay%type;
87 l_cum_total_outlay gms_269_history.cum_total_outlay%type;
88 l_refund_rebate gms_269_history.refund_rebate%type;
89 l_cum_refund_rebate gms_269_history.cum_refund_rebate%type;
90 l_program_income gms_269_history.program_income%type;
91 l_cum_program_income gms_269_history.cum_program_income%type;
92 l_contribution gms_269_history.contribution%type;
93 l_cum_contribution gms_269_history.cum_contribution%type;
94 l_other_fed_award gms_269_history.other_fed_award%type;
95 l_cum_other_fed_award gms_269_history.cum_other_fed_award%type;
96 l_prog_income_match gms_269_history.prog_income_match%type;
97 l_cum_prog_income_match gms_269_history.cum_prog_income_match%type;
98 l_other_rec_outlay gms_269_history.other_rec_outlay%type;
99 l_cum_other_rec_outlay gms_269_history.cum_other_rec_outlay%type;
100 l_total_rec_outlay gms_269_history.total_rec_outlay%type;
101 l_cum_total_rec_outlay gms_269_history.cum_total_rec_outlay%type;
102 l_cum_unliquid_obligation gms_269_history.cum_unliquid_obligation%type;
103 l_cum_recipient_obligation gms_269_history.cum_recipient_obligation%type;
104 l_cum_period_federal_fund gms_269_history.cum_period_federal_fund%type;
105 l_cum_program_income_addition gms_269_history.cum_program_income_addition%type;
106 l_cum_program_income_unused gms_269_history.cum_program_income_unused%type;
107 l_rate_type gms_269_history.rate_type%type;
108 l_indirect_cost_rate gms_269_history.indirect_cost_rate%type;
109 l_allowed_cost_base_burden gms_269_history.allowed_cost_base%type;
110 l_allowed_cost_base gms_269_history.allowed_cost_base%type;
111 l_federal_idc_share gms_269_history.federal_idc_share%type;
112 l_remarks gms_269_history.remarks%type;
113
114 x_version number;
115 cursor prev_rec IS
116 select nvl(cum_total_outlay,0) cum_total_outlay,
117 nvl(cum_refund_rebate,0) cum_refund_rebate,
118 nvl(cum_program_income,0) cum_program_income,
119 nvl(cum_contribution,0) cum_contribution,
120 nvl(cum_other_fed_award,0) cum_other_fed_award,
121 nvl(cum_prog_income_match,0) cum_prog_income_match,
122 nvl(cum_other_rec_outlay,0) cum_other_rec_outlay,
123 nvl(cum_total_rec_outlay,0) cum_total_rec_outlay
124 from gms_269_history
125 where award_id = X_Award_Id
126 and version = x_version
127 and status_code = 'F';
128
129 l_prev_269 prev_rec%rowtype;
130
131 -- Added for bug 2357578
132 -- Cursor to fetch Report periods
133 CURSOR report_period_date_cur IS
134 SELECT GREATEST(x_report_start_date, start_date_active),
135 LEAST(x_report_end_date, end_date_active)
136 FROM gms_awards
137 WHERE award_id = x_award_id;
138
142 SELECT start_date_active,
139 -- Added for bug 2357578
140 -- Cursor to fetch Funding periods based on award
141 CURSOR funding_period_date_cur IS
143 end_date_active
144 FROM gms_awards
145 WHERE award_id = x_award_id;
146
147 l_set_of_books_id number;
148 l_expenditure_item_id NUMBER := NULL;
149 l_line_num NUMBER := NULL;
150 l_transfer_status_code VARCHAR2(1) := NULL;
151 l_raw_cost NUMBER(22,5) := 0;
152 l_sum_burden_cost NUMBER(22,5) := 0;
153 l_total_program_outlays NUMBER(22,5) := 0;
154 l_err_code VARCHAR2(1);
155 l_err_buff VARCHAR2(2000);
156
157 Procedure insert_269_hisrory is
158 Begin
159 insert into gms_269_history (
160 award_id ,
161 version ,
162 status_code,
163 report_status,
164 report_code ,
165 creation_date,
166 created_by ,
167 last_update_date ,
168 last_updated_by ,
169 last_update_login,
170 end_date ,
171 document_number ,
172 accounting_basis ,
173 funding_start_date ,
174 funding_end_date ,
175 report_period_start_date ,
176 report_period_end_date ,
177 total_outlay ,
178 cum_total_outlay ,
179 refund_rebate ,
180 cum_refund_rebate ,
181 program_income ,
182 cum_program_income ,
183 contribution ,
184 cum_contribution ,
185 other_fed_award ,
186 cum_other_fed_award ,
187 prog_income_match ,
188 cum_prog_income_match ,
189 other_rec_outlay ,
190 cum_other_rec_outlay ,
191 total_rec_outlay ,
192 cum_total_rec_outlay ,
193 cum_unliquid_obligation ,
194 cum_recipient_obligation ,
195 cum_period_federal_fund ,
196 cum_program_income_addition,
197 cum_program_income_unused ,
198 rate_type ,
199 indirect_cost_rate ,
200 allowed_cost_base ,
201 federal_idc_share ,
202 remarks
203 )
204 Values
205 (
206 l_award_id ,
207 l_version ,
208 l_status_code,
209 l_status_code,
210 l_report_code ,
211 l_creation_date,
212 l_created_by ,
213 l_last_update_date ,
214 l_last_updated_by ,
215 l_last_update_login,
216 l_end_date ,
217 l_document_number ,
218 l_accounting_basis ,
219 l_funding_start_date ,
220 l_funding_end_date ,
221 l_report_period_start_date ,
222 l_report_period_end_date ,
223 l_total_outlay ,
224 l_cum_total_outlay ,
225 l_refund_rebate ,
226 l_cum_refund_rebate ,
227 l_program_income ,
228 l_cum_program_income ,
229 l_contribution ,
230 l_cum_contribution ,
231 l_other_fed_award ,
232 l_cum_other_fed_award ,
233 l_prog_income_match ,
234 l_cum_prog_income_match ,
235 l_other_rec_outlay ,
236 l_cum_other_rec_outlay ,
237 l_total_rec_outlay ,
238 l_cum_total_rec_outlay ,
239 l_cum_unliquid_obligation ,
240 l_cum_recipient_obligation ,
241 l_cum_period_federal_fund ,
242 l_cum_program_income_addition,
243 l_cum_program_income_unused ,
244 l_rate_type ,
245 l_indirect_cost_rate ,
246 l_allowed_cost_base ,
247 l_federal_idc_share ,
248 l_remarks
249 );
250 Exception
251 When others then
252 raise;
253 End insert_269_hisrory;
254
255
256 Begin
257 -- Initialize amount variable
258 l_total_outlay := 0;
259 l_cum_total_outlay := 0;
260 l_refund_rebate := 0;
261 l_cum_refund_rebate := 0;
262 l_program_income := 0;
263 l_cum_program_income := 0;
264 l_contribution := 0;
265 l_cum_contribution := 0;
266 l_other_fed_award := 0;
267 l_cum_other_fed_award := 0;
268 l_prog_income_match := 0;
269 l_cum_prog_income_match := 0;
270 l_other_rec_outlay := 0;
271 l_cum_other_rec_outlay := 0;
272 l_total_rec_outlay := 0;
273 l_cum_total_rec_outlay := 0;
274 l_cum_unliquid_obligation := 0;
275 l_cum_recipient_obligation := 0;
276 l_cum_period_federal_fund := 0;
277 l_cum_program_income_addition:= 0;
278 l_cum_program_income_unused := 0;
279 l_indirect_cost_rate := 0;
280 l_allowed_cost_base := 0;
281 l_federal_idc_share := 0;
282
283
284 --1. Get the last version number
285
286 Begin
287 select nvl(max(version),0)
288 into x_version
289 from gms_269_history
290 where award_id = X_Award_Id
291 and status_code = 'O';
292 End;
293
294 -- 2. Get document number(Funding Source Award Number) from gms_awards
295
296 Begin
297 select funding_source_award_number
298 into l_document_number
299 from GMS_AWARDS
300 where award_id = X_Award_Id;
301 EXCEPTION
302 WHEN NO_DATA_FOUND THEN
303 NULL;
304 End;
305
309 open prev_rec;
306 --3. Get the cumulative totals from previous report
307
308 Begin
310 fetch prev_rec into l_prev_269;
311 If prev_rec%notfound then
312 l_prev_269.cum_total_outlay := 0;
313 l_prev_269.cum_refund_rebate := 0;
314 l_prev_269.cum_program_income := 0;
315 l_prev_269.cum_contribution := 0;
316 l_prev_269.cum_other_fed_award := 0;
317 l_prev_269.cum_prog_income_match := 0;
318 l_prev_269.cum_other_rec_outlay := 0;
319 l_prev_269.cum_total_rec_outlay := 0;
320 End If;
321 close prev_rec;
322 End;
323
324 --4. Get total outlays for this report
325
326 -- Set current_project_id to NULL to use GMS_CDL_BURDEN_DETAIL_V
327 -- for burden costs of all projects
328
329 -- gms_burden_costing.set_current_project_id (NULL);
330 -- the above line has been commented out for bug 2442827
331 -- Fixed the bug 924274.
332 Begin
333 For Exp_item_rec in total_outlay_raw LOOP
334 l_expenditure_item_id1:= Exp_item_rec.expenditure_item_id;
335 l_line_num1 := Exp_item_rec.line_num;
336 l_total_outlay := l_total_outlay + exp_item_rec.raw_cost;
337 l_allowed_cost_base := l_allowed_cost_base + exp_item_rec.raw_cost;
338 For Exp_item_rec_1 in total_outlay_burden LOOP
339 l_total_outlay := l_total_outlay + exp_item_rec_1.burden_cost;
340 If exp_item_rec_1.report_direct_flag ='Y' THEN
341 l_allowed_cost_base:= l_allowed_cost_base + exp_item_rec_1.burden_cost;
342 Else
343 l_federal_idc_share:= l_federal_idc_share + exp_item_rec_1.burden_cost; -- bug 2651959
344 End If;
345 End loop ;
346
347 End loop;
348 EXCEPTION
349 When no_data_found then
350 l_allowed_cost_base := 0;
351 End;
352
353
354 --5. Get the commitments
355 DECLARE
356 x_period_start_date DATE ; -- Bug 2660430
357 x_period_end_date DATE ; -- Bug 2660430
358
359 CURSOR c_period_dates IS -- Bug 2660430, Added
360 SELECT start_date, end_date
361 FROM gl_period_statuses
362 WHERE period_name = (SELECT pa_accum_utils.Get_current_gl_period FROM DUAL)
363 AND adjustment_period_flag = 'N'
364 AND application_id = 101
365 AND set_of_books_id = l_set_of_books_id ;
366
367 CURSOR c_burdened_cost IS
368 SELECT sum(burdened_cost)
369 FROM (SELECT sum(nvl(GB.encumb_period_to_date,0) * decode(balance_type, 'PO', 1, 'AP' , 1, 'ENC' , 1, 0)) burdened_cost
370 FROM GMS_BALANCES GB, GMS_BUDGET_VERSIONS GBV
371 WHERE gb.award_id = x_award_id
372 AND GBV.award_id = GB.award_id
373 AND GBV.budget_version_id = gb.budget_version_id
374 AND GBV.current_flag in ('Y','R')
375 AND GBV.budget_status_code = 'B'
376 GROUP BY GB.award_id
377 UNION ALL
378 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
379 FROM gms_bc_packets gbc, GMS_BUDGET_VERSIONS GBV
380 WHERE gbv.budget_version_id = gbc.budget_version_id
381 AND gbc.status_code = 'A'
382 AND GBV.budget_status_code = 'B'
383 AND GBV.current_flag in ('Y', 'R')
384 AND gbc.award_id = x_award_id
385 GROUP BY GBC.award_id) ;
386
387 Begin
388 select set_of_books_id
389 into l_set_of_books_id
390 from pa_implementations;
391
392 /* Select sum(nvl(acct_burdened_cost,0)) burdened_cost
393 --sum(nvl(tot_cmt_burdened_cost,0)) burdened_cost -- 11i changes
394 into l_cum_unliquid_obligation
395 from pa_commitment_txns_v cmt,
396 gl_period_statuses gps
397 where cmt.gl_period = gps.period_name
398 and cmt.original_txn_reference1 = to_char(X_Award_Id)
399 and gps.adjustment_period_flag = 'N'
400 and gps.application_id = 101
401 and gps.set_of_books_id = l_set_of_books_id
402 and gps.start_date >= X_Report_Start_Date
403 and gps.end_date <= X_Report_End_Date; */
404
405 -- Bug 2660430, Start of code
406 OPEN c_period_dates ;
407 FETCH c_period_dates INTO x_period_start_date, x_period_end_date ;
408 IF SQL%FOUND THEN
409 IF x_period_start_date >= X_Report_Start_Date
410 AND x_period_end_date <= X_Report_End_Date THEN
411 OPEN c_burdened_cost ;
412 FETCH c_burdened_cost INTO l_cum_unliquid_obligation ;
413 CLOSE c_burdened_cost ;
414 END IF ;
415 END IF ;
416 CLOSE c_period_dates ;
417 -- Bug 2660430, End of code
418
419 EXCEPTION
420 When no_data_found then
421 null;
422 End;
423
424
425 --6. Get the base amount MTDC
426
427 /* Begin
428 Select
429 sum(nvl(bv.burden_cost,0)) burden_cost
430 into l_allowed_cost_base_burden
431 from gms_allowable_expenditures ae,
432 gms_awards a,
433 GMS_CDL_BURDEN_DETAIL_V bv,
434 gms_award_distributions g,
435 pa_cost_distribution_lines_all c,
436 pa_expenditure_items ei
437 where g.expenditure_item_id = c.expenditure_item_id
438 and g.cdl_line_num = c.line_num
439 and c.transfer_status_code in ('A','V')
440 and c.gl_date between X_Report_Start_Date and X_Report_End_Date
441 and c.expenditure_item_id = ei.expenditure_item_id
442 and g.award_id = X_Award_Id
443 and c.reversed_flag is NULL
444 and c.line_num_reversed is NULL
445 and c.line_type = 'R'
446 and nvl(ei.system_linkage_function,'XXX') <> 'BTC'
447 and bv.expenditure_item_id = g.expenditure_item_id
448 and bv.line_num = g.cdl_line_num --change from g.adl_line_num to fix bug 2651959
449 and a.award_id = X_Award_Id
450 and ae.allowability_schedule_id = a.allowable_schedule_id
451 and ae.expenditure_type = ei.expenditure_type
452 and nvl(ae.mtdc_exempt_flag,'N') = 'N';
453
454 EXCEPTION
455 When no_data_found then
456 l_allowed_cost_base_burden := 0;
457 End; */ -- commented out to fix bug 2651959
458
459 --7. Get the period federal fund
460 select sum(nvl(direct_cost,0)) + sum(nvl(indirect_cost,0))
461 into l_cum_period_federal_fund
462 from gms_installments
463 where award_id = X_award_id
464 and (X_report_start_date between start_date_active and end_date_active
465 or X_report_End_date between start_date_active and end_date_active );
466 --8. Get the funding period from installments
467 -- Bug 2357578 : Modified the below code to fetch funding periods
468 -- from award instead from Installments.
469
470 -- Commented for bug 2357578
471 /* select min(start_date_active), max(end_date_active)
472 into l_funding_start_date,
473 l_funding_end_date
474 from gms_installments
475 where award_id = X_award_id; */
476
477 -- Added for bug 2357578
478 OPEN funding_period_date_cur;
479 FETCH funding_period_date_cur INTO
480 l_funding_start_date,l_funding_end_date;
481 CLOSE funding_period_date_cur;
482
483 --8. Calculate cumulative totals for this report
484
485 l_award_id := X_Award_id;
486 l_version := x_version + 1;
487 l_report_code := 'SF269';
488 l_creation_date := trunc(sysdate);
489 l_created_by := fnd_global.user_id;
490 l_last_update_date := trunc(sysdate);
491 l_last_updated_by := fnd_global.user_id;
492 l_last_update_login := fnd_global.login_id;
493 l_end_date := trunc(sysdate);
494 l_accounting_basis := 'A'; -- accruel
495
496 -- Bug 2357578 : Modified the code to fetch report dates based
497 -- Award start and end dates.
498 -- l_report_period_start_date := X_report_start_date;
499 -- l_report_period_end_date := X_report_end_date;
500 OPEN report_period_date_cur;
501 FETCH report_period_date_cur INTO l_report_period_start_date,
502 l_report_period_end_date;
503 CLOSE report_period_date_cur;
504
505 l_cum_total_outlay := nvl(l_total_outlay,0) +
506 nvl(l_prev_269.cum_total_outlay,0);
507
508 l_cum_refund_rebate := nvl(l_refund_rebate,0) +
509 nvl(l_prev_269.cum_refund_rebate,0);
510
511 l_cum_program_income := nvl(l_program_income,0) +
512 nvl(l_prev_269.cum_program_income,0);
513
514 l_cum_contribution := nvl(l_contribution,0) +
515 nvl(l_prev_269.cum_contribution,0);
516 l_cum_other_fed_award := nvl(l_other_fed_award ,0) +
517 nvl(l_prev_269.cum_other_fed_award,0 );
518
519
520 l_cum_prog_income_match := nvl(l_prog_income_match ,0) +
521 nvl(l_prev_269.cum_prog_income_match,0 );
522
523 l_cum_other_rec_outlay := nvl(l_other_rec_outlay ,0) +
524 nvl(l_prev_269.cum_other_rec_outlay,0 );
525
526 l_total_rec_outlay := l_contribution +
527 l_other_fed_award +
528 l_prog_income_match +
529 l_other_rec_outlay;
530
531 l_cum_total_rec_outlay := l_cum_contribution +
532 l_cum_other_fed_award +
533 l_cum_prog_income_match +
534 l_cum_other_rec_outlay;
535
536 l_rate_type := 'PROVISIONAL';
537 If ( l_allowed_cost_base <> 0 ) then
538 --l_indirect_cost_rate := l_allowed_cost_base_burden / l_allowed_cost_base; bug 2651959
539 l_indirect_cost_rate := l_federal_idc_share / l_allowed_cost_base;
540
541 End if;
542 --l_federal_idc_share := l_allowed_cost_base * l_indirect_cost_rate; bug 2651959
543
544 --9. Create 2 history records, one original and one draft.
545 l_status_code := 'O';
546
547 insert_269_hisrory;
548
549 l_status_code := 'D';
550
551 insert_269_hisrory;
552 End Populate_269_History;
553 End GMS_REPORT_SF269;
554