[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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
and c.gl_date between X_Report_Start_Date
and X_Report_End_Date
and c.expenditure_item_id = ei.expenditure_item_id
and g.award_id = X_Award_Id
and g.document_type = 'EXP' -- BUG 4005793 : FPM Perf. fixes.
and g.adl_status = 'A' -- BUG 4005793 : FPM Perf. fixes.
and c.line_type = 'R'
and nvl(ei.system_linkage_function,'XXX') <> 'BTC'
--- BUG 4005793 : FPM Perf. fixes.
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 = X_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 = l_expenditure_item_id1
and bv.line_num = l_line_num1
and a.award_id = x_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(+) -- Added outerjoin to fix bug 2651959
group by
bv.expenditure_item_id
,bv.line_num,
gcd.report_direct_flag;
l_last_update_date gms_269_history.last_update_date%type;
l_last_updated_by gms_269_history.last_updated_by%type;
l_last_update_login gms_269_history.last_update_login%type;
select nvl(cum_total_outlay,0) cum_total_outlay,
nvl(cum_refund_rebate,0) cum_refund_rebate,
nvl(cum_program_income,0) cum_program_income,
nvl(cum_contribution,0) cum_contribution,
nvl(cum_other_fed_award,0) cum_other_fed_award,
nvl(cum_prog_income_match,0) cum_prog_income_match,
nvl(cum_other_rec_outlay,0) cum_other_rec_outlay,
nvl(cum_total_rec_outlay,0) cum_total_rec_outlay
from gms_269_history
where award_id = X_Award_Id
and version = x_version
and status_code = 'F';
SELECT GREATEST(x_report_start_date, start_date_active),
LEAST(x_report_end_date, end_date_active)
FROM gms_awards
WHERE award_id = x_award_id;
SELECT start_date_active,
end_date_active
FROM gms_awards
WHERE award_id = x_award_id;
Procedure insert_269_hisrory is
Begin
insert into gms_269_history (
award_id ,
version ,
status_code,
report_status,
report_code ,
creation_date,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login,
end_date ,
document_number ,
accounting_basis ,
funding_start_date ,
funding_end_date ,
report_period_start_date ,
report_period_end_date ,
total_outlay ,
cum_total_outlay ,
refund_rebate ,
cum_refund_rebate ,
program_income ,
cum_program_income ,
contribution ,
cum_contribution ,
other_fed_award ,
cum_other_fed_award ,
prog_income_match ,
cum_prog_income_match ,
other_rec_outlay ,
cum_other_rec_outlay ,
total_rec_outlay ,
cum_total_rec_outlay ,
cum_unliquid_obligation ,
cum_recipient_obligation ,
cum_period_federal_fund ,
cum_program_income_addition,
cum_program_income_unused ,
rate_type ,
indirect_cost_rate ,
allowed_cost_base ,
federal_idc_share ,
remarks
)
Values
(
l_award_id ,
l_version ,
l_status_code,
l_status_code,
l_report_code ,
l_creation_date,
l_created_by ,
l_last_update_date ,
l_last_updated_by ,
l_last_update_login,
l_end_date ,
l_document_number ,
l_accounting_basis ,
l_funding_start_date ,
l_funding_end_date ,
l_report_period_start_date ,
l_report_period_end_date ,
l_total_outlay ,
l_cum_total_outlay ,
l_refund_rebate ,
l_cum_refund_rebate ,
l_program_income ,
l_cum_program_income ,
l_contribution ,
l_cum_contribution ,
l_other_fed_award ,
l_cum_other_fed_award ,
l_prog_income_match ,
l_cum_prog_income_match ,
l_other_rec_outlay ,
l_cum_other_rec_outlay ,
l_total_rec_outlay ,
l_cum_total_rec_outlay ,
l_cum_unliquid_obligation ,
l_cum_recipient_obligation ,
l_cum_period_federal_fund ,
l_cum_program_income_addition,
l_cum_program_income_unused ,
l_rate_type ,
l_indirect_cost_rate ,
l_allowed_cost_base ,
l_federal_idc_share ,
l_remarks
);
End insert_269_hisrory;
select nvl(max(version),0)
into x_version
from gms_269_history
where award_id = X_Award_Id
and status_code = 'O';
select funding_source_award_number
into l_document_number
from GMS_AWARDS
where award_id = X_Award_Id;
SELECT start_date, end_date
FROM gl_period_statuses
WHERE period_name = (SELECT pa_accum_utils.Get_current_gl_period FROM DUAL)
AND adjustment_period_flag = 'N'
AND application_id = 101
AND set_of_books_id = l_set_of_books_id ;
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 = x_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'
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 = x_award_id
GROUP BY GBC.award_id) ;
select set_of_books_id
into l_set_of_books_id
from pa_implementations;
/* Select sum(nvl(acct_burdened_cost,0)) burdened_cost
--sum(nvl(tot_cmt_burdened_cost,0)) burdened_cost -- 11i changes
into l_cum_unliquid_obligation
from pa_commitment_txns_v cmt,
gl_period_statuses gps
where cmt.gl_period = gps.period_name
and cmt.original_txn_reference1 = to_char(X_Award_Id)
and gps.adjustment_period_flag = 'N'
and gps.application_id = 101
and gps.set_of_books_id = l_set_of_books_id
and gps.start_date >= X_Report_Start_Date
and gps.end_date <= X_Report_End_Date; */
Select
sum(nvl(bv.burden_cost,0)) burden_cost
into l_allowed_cost_base_burden
from gms_allowable_expenditures ae,
gms_awards a,
GMS_CDL_BURDEN_DETAIL_V bv,
gms_award_distributions g,
pa_cost_distribution_lines_all c,
pa_expenditure_items ei
where g.expenditure_item_id = c.expenditure_item_id
and g.cdl_line_num = c.line_num
and c.transfer_status_code in ('A','V')
and c.gl_date between X_Report_Start_Date and X_Report_End_Date
and c.expenditure_item_id = ei.expenditure_item_id
and g.award_id = X_Award_Id
and c.reversed_flag is NULL
and c.line_num_reversed is NULL
and c.line_type = 'R'
and nvl(ei.system_linkage_function,'XXX') <> 'BTC'
and bv.expenditure_item_id = g.expenditure_item_id
and bv.line_num = g.cdl_line_num --change from g.adl_line_num to fix bug 2651959
and a.award_id = X_Award_Id
and ae.allowability_schedule_id = a.allowable_schedule_id
and ae.expenditure_type = ei.expenditure_type
and nvl(ae.mtdc_exempt_flag,'N') = 'N';
select sum(nvl(direct_cost,0)) + sum(nvl(indirect_cost,0))
into l_cum_period_federal_fund
from gms_installments
where award_id = X_award_id
and (X_report_start_date between start_date_active and end_date_active
or X_report_End_date between start_date_active and end_date_active );
/* select min(start_date_active), max(end_date_active)
into l_funding_start_date,
l_funding_end_date
from gms_installments
where award_id = X_award_id; */
l_last_update_date := trunc(sysdate);
l_last_updated_by := fnd_global.user_id;
l_last_update_login := fnd_global.login_id;
insert_269_hisrory;
insert_269_hisrory;