[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select nvl(max(version),0)
from gms_425_history
where award_id = p_award_id
and status_code IN ('F');
select
REPORT_TRN_ID REPORT_TRN_ID,
nvl(report_type_code,'QUARTERLY') REPORT_TYPE_CODE,
REPORT_END_DATE REPORT_END_DATE,
nvl(BASIS_OF_ACCNT_CODE,'A') BASIS_OF_ACCNT_CODE,
nvl(CASH_RECEIPTS_AMT,0) CASH_RECEIPTS_AMT,
nvl(CASH_DISBURSEMENTS_AMT,0) CASH_DISBURSEMENTS_AMT,
nvl(TOTAL_FED_FUNDS_AUTH_AMT,0) TOTAL_FED_FUNDS_AUTH_AMT,
nvl(FED_SHARE_OF_EXP_AMT,0) FED_SHARE_OF_EXP_AMT,
nvl(FED_SHARE_OF_UNLIQ_OBL_AMT,0) FED_SHARE_OF_UNLIQ_OBL_AMT,
nvl(TOTAL_RECPT_SHARE_REQ_AMT,0) TOTAL_RECPT_SHARE_REQ_AMT,
nvl(RECPT_SHARE_EXP_AMT,0) RECPT_SHARE_EXP_AMT,
nvl(TOTAL_FED_PRG_INC_EARN_AMT,0) TOTAL_FED_PRG_INC_EARN_AMT,
nvl(PRG_INC_EXP_DEDUCT_ALT_AMT,0) PRG_INC_EXP_DEDUCT_ALT_AMT,
nvl(PRG_INC_EXP_ADD_ALT_AMT,0) PRG_INC_EXP_ADD_ALT_AMT
from gms_425_history
where award_id = p_award_id
and status_code IN ('F')
and version = p_version_number;
select
end_date_active , -- Check with SHweta if this is correct
start_date_active
from GMS_AWARDS
where award_id = p_award_id;
Select SUM(nvl(c.amount,0))
from pa_expenditure_items_all ei,
pa_cost_distribution_lines_all c,
gms_award_distributions g
where --added by rkuttiya for bug 9117372
--c.gl_date between p_report_start_date and p_report_end_date
trunc(c.gl_date) < trunc(p_report_end_date)
and c.expenditure_item_id = ei.expenditure_item_id
and g.award_id = p_award_id
and g.document_type = 'EXP'
and g.adl_line_num = 1
and g.adl_status = 'A'
and g.expenditure_item_id = c.expenditure_item_id
and c.line_type = 'R'
and ei.system_linkage_function <> 'BTC' -- Put the correct code for system linkage function
and ei.project_id in ( select gbv.project_id
from gms_budget_versions gbv
where gbv.budget_type_code = 'AC'
and gbv.budget_status_code in ('S','W' )
and gbv.award_id = p_award_id );
Select sum(nvl(bv.burden_cost,0))
FROM gms_cdl_burden_detail_v bv,
gms_budget_versions gbv
WHERE --added by rkuttiya for bug 9117372
-- bv.gl_date between p_report_start_date and p_report_end_date
trunc(bv.gl_date) < trunc(p_report_end_date)
and bv.award_id = p_award_id
and bv.line_type = 'R'
and bv.system_linkage_function <> 'BTC' -- Put the correct code for system linkage function
and gbv.budget_type_code = 'AC'
and gbv.budget_status_code in ('S','W' )
and gbv.award_id = p_award_id
and bv.project_id = gbv.project_id;
Select
nvl(c.amount,0) raw_cost,
c.expenditure_item_id,
c.line_num
from pa_expenditure_items ei,
pa_cost_distribution_lines_all c,
gms_award_distributions g
where g.expenditure_item_id = c.expenditure_item_id
and g.cdl_line_num = c.line_num
-- rkuttiya added for bug 9117372
-- and c.gl_date between p_report_start_date and p_report_end_date
and trunc(c.gl_date) < trunc(p_report_end_date)
--
and c.expenditure_item_id = ei.expenditure_item_id
and g.award_id = p_award_id
and g.document_type = 'EXP'
and g.adl_status = 'A'
and c.line_type = 'R'
and nvl(ei.system_linkage_function,'XXX') <> 'BTC'
and ei.project_id in ( select gbv.project_id
from gms_budget_versions gbv
where gbv.budget_type_code = 'AC'
and gbv.budget_status_code in ('S','W' )
and gbv.award_id = p_award_id );
Select
sum(nvl(bv.burden_cost,0)) burden_cost,
gcd.report_direct_flag report_direct_flag
from
gms_awards a,
GMS_CDL_BURDEN_DETAIL_V bv,
pa_ind_cost_codes cd,
gms_ind_cost_codes gcd,
gms_allowable_expenditures ae
where
bv.expenditure_item_id = p_expenditure_item_id
and bv.line_num = p_line_num
and a.award_id = p_award_id
and bv.ind_cost_code = cd.ind_cost_code
and ae.allowability_schedule_id = a.allowable_schedule_id
and bv.ei_expenditure_type = ae.expenditure_type
and nvl(ae.mtdc_exempt_flag,'N') = 'N'
and cd.ind_cost_code = gcd.ind_cost_code(+)
group by
bv.expenditure_item_id
,bv.line_num
,gcd.report_direct_flag;
SELECT sum(burdened_cost)
FROM (SELECT sum(nvl(GB.encumb_period_to_date,0) * decode(balance_type, 'PO', 1, 'AP' , 1, 'ENC' , 1, 0)) burdened_cost
FROM GMS_BALANCES GB, GMS_BUDGET_VERSIONS GBV
WHERE gb.award_id = p_award_id
AND GBV.award_id = GB.award_id
AND GBV.budget_version_id = gb.budget_version_id
AND GBV.current_flag in ('Y','R')
AND GBV.budget_status_code = 'B'
AND trunc(gb.start_date) <= trunc(p_report_end_date) -- added to get the cumulative commitments
GROUP BY GB.award_id
UNION ALL
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
FROM gms_bc_packets gbc, GMS_BUDGET_VERSIONS GBV
WHERE gbv.budget_version_id = gbc.budget_version_id
AND gbc.status_code = 'A'
AND GBV.budget_status_code = 'B'
AND GBV.current_flag in ('Y', 'R')
AND gbc.award_id = p_award_id
AND trunc(gbc.expenditure_item_date) <= trunc(p_report_end_date) -- added to get additional commitments
GROUP BY GBC.award_id) ;
l_last_update_date gms_425_history.last_update_date%type;
l_last_updated_by gms_425_history.last_updated_by%type;
l_last_update_login gms_425_history.last_update_login%type;
Procedure insert_425_history (p_status_code IN gms_425_history.status_code%type) is
Begin
-- set the transaction id
select gms_425_history_report_id_s.nextval
into l_report_trn_id
from dual;
insert into gms_425_history (
REPORT_TRN_ID,
award_id,
version,
status_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
run_date,
report_start_date,
grant_period_from_date,
grant_period_to_date,
BASIS_OF_ACCNT_CODE,
REPORT_TYPE_CODE,
REPORT_END_DATE,
CASH_RECEIPTS_AMT,
CASH_DISBURSEMENTS_AMT,
TOTAL_FED_FUNDS_AUTH_AMT,
FED_SHARE_OF_EXP_AMT,
FED_SHARE_OF_UNLIQ_OBL_AMT,
TOTAL_RECPT_SHARE_REQ_AMT,
RECPT_SHARE_EXP_AMT,
TOTAL_FED_PRG_INC_EARN_AMT,
PRG_INC_EXP_DEDUCT_ALT_AMT,
PRG_INC_EXP_ADD_ALT_AMT,
REMARKS,
NAME,
TELEPHONE,
EMAIL,
REPORT_SUBMIT_DATE
)
Values
(
l_REPORT_TRN_ID,
p_award_id,
l_current_version,
p_status_code,
l_creation_date,
l_created_by,
l_last_update_date,
l_last_updated_by,
l_last_update_login,
l_run_date,
l_period_start_date,
l_award_start_date,
l_award_end_date,
l_BASIS_OF_ACCNT_CODE,
l_REPORT_TYPE_CODE,
p_REPORT_END_DATE,
l_CASH_RECEIPTS_AMT,
l_CASH_DISBURSEMENTS_AMT,
l_TOTAL_FED_FUNDS_AUTH_AMT,
l_FED_SHARE_OF_EXP_AMT,
l_FED_SHARE_OF_UNLIQ_OBL_AMT,
l_TOTAL_RECPT_SHARE_REQ_AMT,
l_RECPT_SHARE_EXP_AMT,
l_TOTAL_FED_PRG_INC_EARN_AMT,
l_PRG_INC_EXP_DEDUCT_ALT_AMT,
l_PRG_INC_EXP_ADD_ALT_AMT,
l_REMARKS,
l_NAME,
l_TELEPHONE,
l_EMAIL,
l_REPORT_SUBMIT_DATE
);
End insert_425_history;
Procedure insert_425_expense (p_last_report_trn_id IN NUMBER) IS
-- Get last versions details
CURSOR get_last_version_expenses_csr (p_last_report_trn_id IN NUMBER) IS
SELECT
INDIRECT_EXP_TYPE_CODE,
INDIRECT_EXP_RATE,
INDIRECT_EXP_PERIOD_FROM,
INDIRECT_EXP_PERIOD_TO,
INDIRECT_EXP_BASE_AMT,
INDIRECT_EXP_FED_SHARE_AMT
FROM
GMS_425_EXPENSE exp
WHERE
exp.report_trn_id = p_last_report_trn_id;
select gms_425_expenses_id_s.nextval
into l_EXPENSE_TRN_ID
from dual;
insert into gms_425_expense (
EXPENSE_TRN_ID,
REPORT_TRN_ID,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
INDIRECT_EXP_TYPE_CODE,
INDIRECT_EXP_RATE,
INDIRECT_EXP_PERIOD_FROM,
INDIRECT_EXP_PERIOD_TO,
INDIRECT_EXP_BASE_AMT,
INDIRECT_EXP_FED_SHARE_AMT
)
Values
(
l_EXPENSE_TRN_ID,
l_REPORT_TRN_ID,
l_creation_date,
l_created_by ,
l_last_update_date ,
l_last_updated_by ,
l_last_update_login,
get_last_version_expenses_rec.INDIRECT_EXP_TYPE_CODE,
get_last_version_expenses_rec.INDIRECT_EXP_RATE,
get_last_version_expenses_rec.INDIRECT_EXP_PERIOD_FROM,
get_last_version_expenses_rec.INDIRECT_EXP_PERIOD_TO,
get_last_version_expenses_rec.INDIRECT_EXP_BASE_AMT,
get_last_version_expenses_rec.INDIRECT_EXP_FED_SHARE_AMT
);
End insert_425_expense;
l_last_update_date := trunc(sysdate);
l_last_updated_by := fnd_global.user_id;
l_last_update_login := fnd_global.login_id;
select sum(nvl(direct_cost,0)) + sum(nvl(indirect_cost,0))
into l_total_fed_funds_auth_amt
from gms_installments
where award_id = p_award_id
and start_date_active <= p_report_end_date;
insert_425_history (l_status_code);
insert_425_expense (l_last_report_trn_id);
insert_425_history(l_status_code);
insert_425_expense (l_last_report_trn_id);