[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select Award_Number
into p_award_number
from gms_ssa_awards_v
where award_id = p_award_id;
select AWARD_ID, AWARD_SHORT_NAME
into p_award_id, p_award_name
from gms_ssa_awards_v
where AWARD_NUMBER = p_award_number;
select award_id
into p_award_id
from gms_ssa_awards_v
where award_number = p_award_number;
select nvl(pt.sponsored_flag, 'N')
into p_sponsored_flag
from pa_projects_all b,
gms_project_types pt
where b.segment1 = p_project_num
and b.project_type = pt.project_type
and pt.sponsored_flag = 'Y';
select 'Y'
into l_award_valid
from dual
where exists (
select '1'
from gms_awards_all
where award_number = p_award_number
and award_template_flag = 'DEFERRED'
and status in ('ACTIVE', 'AT_RISK'));
select 'Y'
into l_award_funds
from gms_ssa_awards_v
where award_id = p_award_id
and project_id = p_project_id
and task_id = p_task_id;
FUNCTION DeleteACGenADL(p_award_set_id IN NUMBER)
RETURN BOOLEAN IS
BEGIN
delete from gms_award_distributions
where award_set_id = p_award_set_id;
END DeleteACGenADL;
select aid.invoice_distribution_id,
aid.distribution_line_number,
aerl.project_id,
aerl.task_id,
aerl.award_id,
aid.amount,
aid.request_id,
aid.creation_date,
aid.created_by,
to_number(aerl.reference_1), -- Expenditure_item_id
to_number(aerl.reference_2), -- CDL Line number
null, -- ind_compiled_set_id
null, -- burdenable_raw_cost
null, -- rlmi_id
null -- bud_task_id
from ap_invoice_distributions_all aid,
ap_expense_report_headers_all aerh,
ap_expense_report_lines_all aerl,
gms_project_types gpt,
pa_projects_all pp
where aerh.vouchno = aid.invoice_id
and aerh.report_header_id = aerl.report_header_id
and aid.invoice_id = v_invoice_id
and aid.distribution_line_number = aerl.distribution_line_number
and aid.project_id = pp.project_id
and pp.project_type = gpt.project_type
and gpt.sponsored_flag = 'Y'
and aerl.award_id is not null
order by aid.distribution_line_number;
select source
from ap_expense_report_headers_all
where vouchno = v_invoice_id;
select aeh.report_header_id, aerl.project_id bulk collect
into cur_report_header_id, cur_project_id
from ap_expense_report_headers_all aeh,
ap_expense_report_lines_all aerl,
pa_projects_all pp, gms_project_types gpt
where aeh.report_header_id = aerl.report_header_id
and aeh.vouchno = p_invoice_id(i)
and aerl.project_id = pp.project_id
and pp.project_type = gpt.project_type
and gpt.sponsored_flag = 'Y';
update ap_expense_report_lines_all aerl
set (award_id, award_number) = (select aw.award_id, aw.award_number
from gms_awards_all aw, gms_award_distributions adl
where aw.award_id = adl.award_id
and adl.expenditure_item_id = aerl.reference_1
and adl.document_type = 'EXP'
and adl.adl_status = 'A'
and adl.adl_line_num = 1
and rownum = 1
and adl.project_id = aerl.project_id
and adl.task_id = aerl.task_id)
where aerl.report_header_id = cur_report_header_id(i)
and aerl.project_id = cur_project_id(i);
t_award_set_id.delete;
t_distribution_line_number.delete;
t_invoice_distribution_id.delete;
t_project_id.delete;
t_task_id.delete;
t_award_id.delete;
t_amount.delete;
t_request_id.delete;
t_created_by.delete;
t_date.delete;
t_reference_1.delete;
t_reference_2.delete;
t_ind_compiled_set_id.delete;
t_rlmi_id.delete;
t_bud_task_id.delete;
t_burdenable_cost.delete;
cur_project_id.delete;
cur_report_header_id.delete;
select ind_compiled_set_id, burdenable_raw_cost,
resource_list_member_id, bud_task_id
into t_ind_compiled_set_id(i), t_burdenable_cost(i),
t_rlmi_id(i), t_bud_task_id(i)
from gms_award_distributions
where expenditure_item_id = t_reference_1(i)
and cdl_line_num = t_reference_2(i)
and adl_status = 'A'
and document_type = 'EXP'
and fc_status = 'A';
insert into gms_award_distributions (
AWARD_SET_ID,
ADL_LINE_NUM,
FUNDING_PATTERN_ID,
DISTRIBUTION_VALUE,
RAW_COST,
DOCUMENT_TYPE,
PROJECT_ID,
TASK_ID,
AWARD_ID,
EXPENDITURE_ITEM_ID,
CDL_LINE_NUM,
IND_COMPILED_SET_ID,
GL_DATE,
REQUEST_ID,
LINE_NUM_REVERSED,
RESOURCE_LIST_MEMBER_ID,
OUTPUT_VAT_TAX_ID,
OUTPUT_TAX_EXEMPT_FLAG,
OUTPUT_TAX_EXEMPT_REASON_CODE,
OUTPUT_TAX_EXEMPT_NUMBER,
ADL_STATUS,
FC_STATUS,
LINE_TYPE,
CAPITALIZED_FLAG,
CAPITALIZABLE_FLAG,
REVERSED_FLAG,
REVENUE_DISTRIBUTED_FLAG,
BILLED_FLAG,
BILL_HOLD_FLAG,
DISTRIBUTION_ID,
PO_DISTRIBUTION_ID,
INVOICE_DISTRIBUTION_ID,
PARENT_AWARD_SET_ID,
INVOICE_ID,
PARENT_ADL_LINE_NUM,
DISTRIBUTION_LINE_NUMBER,
BURDENABLE_RAW_COST,
COST_DISTRIBUTED_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
BUD_TASK_ID,
BILLABLE_FLAG,
ACCUMULATED_FLAG)
values (
gms_adls_award_set_id_s.nextval, -- award_set_id
1, -- adl_line_num
null, -- funding_pattern_id
100, -- distribution_rule
null, -- raw_cost
'AP', -- document_type
t_project_id(i), -- project_id
t_task_id(i), -- task_id
t_award_id(i), -- award_id
null, -- expenditure_item_id
null, -- cdl_line_num
t_ind_compiled_set_id(i), -- ind_compiled_set_id
null, -- gl_date
t_request_id(i), -- request_id
null, -- line_num_reversed
t_rlmi_id(i), -- resource_list_member_id
null, -- output_vat_tax_id
null, -- output_tax_exempt_flag
null, -- output_tax_exempt_reason_code
null, -- output_tax_exempt_number
'A', -- adl_status
decode(v_source,
'Oracle Project Accounting', 'A',
'N'), -- fc_status
'R', -- line_type
null, -- capitalized_flag
null, -- capitalizable_flag
null, -- reversed_flag
'N', -- revenue_distributed_flag
'N', -- billed_flag
null, -- bill_hold_flag
null, -- distribution_id
null, -- po_distribution_id
t_invoice_distribution_id(i), -- invoice_distribution_id
null, -- parent_award_set_id
p_invoice_id(inv_index), -- invoice_id
null, -- parent_adl_line_num
t_distribution_line_number(i), -- distribution_line_number
t_burdenable_cost(i), -- burdenable_raw_cost
null, -- cost_distributed_flag
t_date(i), -- last_update_date
t_created_by(i), -- last_updated_by
t_created_by(i), -- created_by
t_date(i), -- creation_date
t_created_by(i), -- last_update_login
t_bud_task_id(i), -- bud_task_id
'N', -- billable_flag
'N') -- accumulated_flag
returning award_set_id bulk collect
into t_award_set_id;
update ap_invoice_distributions_all
set award_id = t_award_set_id(asi)
where invoice_id = p_invoice_id(inv_index)
and distribution_line_number = t_distribution_line_number(asi);