[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_rows_inserted out nocopy number) --5726575
is
l_max_adl_line_num number;
select adl.award_set_id,
adl.adl_line_num,
adl.raw_cost,
adl.project_id,
adl.task_id,
adl.award_id,
adl.expenditure_item_id,
adl.cdl_line_num,
adl.ind_compiled_set_id,
adl.gl_date,
adl.line_num_reversed,
adl.adl_status,
adl.fc_status,
adl.reversed_flag,
adl.burdenable_raw_cost,
adl.cost_distributed_flag,
adl.accumulated_flag,
gei.encumbrance_item_date,
gei.enc_distributed_flag,
gei.adjusted_encumbrance_item_id,
gei.net_zero_adjustment_flag,
gei.transferred_from_enc_item_id,
gei.amount,
gei.ind_compiled_set_id enc_ind_compiled_set_id,
gei.denom_raw_amount,
gei.acct_raw_cost,
adl.capitalizable_flag,
adl.bill_hold_flag,
adl.billable_flag
from gms_encumbrance_items_all gei,
gms_encumbrances_all ge,
gms_award_distributions adl
where gei.encumbrance_id = ge.encumbrance_id
and gei.encumbrance_item_id = adl.expenditure_item_id
and adl.adl_status = 'A'
and adl.document_type = 'ENC'
and gei.enc_distributed_flag = 'N'
and nvl(reversed_flag, 'N') <> 'Y'
and line_num_reversed is null
and gei.adjustment_type in ('BURDEN_RECOMPILE', 'BURDEN_RECALC') -- Bug 6761516 added 'BURDEN_RECALC'
and gei.request_id = p_request_id
and adl.project_id = nvl(p_project_id, adl.project_id) --Bug 5956414
and ge.encumbrance_group = nvl(p_enc_group, ge.encumbrance_group) --Bug 5956414
and adl.ind_compiled_set_id is not null
and adl.fc_status = 'A';
p_rows_inserted := 0;
gms_error_pkg.gms_debug('Inserting into GMS_BC_PACKETS for the records which failed funds check previously.', 'C');
insert into gms_bc_packets (packet_id,
set_of_books_id,
je_source_name,
je_category_name,
actual_flag,
period_name,
period_year,
period_num,
project_id,
task_id,
award_id,
result_code,
funding_pattern_id,
funding_sequence,
fp_status,
status_code,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
entered_dr,
entered_cr,
expenditure_type,
expenditure_organization_id,
expenditure_item_date,
document_type,
document_header_id,
document_distribution_id,
TRANSFERED_FLAG,
account_type,
request_id,
bc_packet_id,
person_id,
job_id,
expenditure_category,
revenue_category,
adjusted_document_header_id,
transaction_source,
award_set_id,
ind_compiled_set_id)
Select p_packet_id,
p_sob_id,
decode(substr(gei.transaction_source,1,4),
'GMSE', gei.transaction_source,
decode(gei.transaction_source,
'GOLDE', 'Labor Distribution',
'Project Accounting')),
'Encumberances',
'E',
glst.period_name,
glst.period_year,
glst.period_num,
p.project_id,
adl.task_id,
adl.award_id,
NULL,
null,
null,
null,
'P',
p_sys_date,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
p_sys_date,
FND_GLOBAL.LOGIN_ID,
pa_currency.round_currency_amt(decode(sign(adl.raw_cost),1,adl.raw_cost,0)),
pa_currency.round_currency_amt(decode(sign(adl.raw_cost),-1,-1*adl.raw_cost,0)),
gei.encumbrance_type,
nvl(gei.override_to_organization_id,ge.incurred_by_organization_id),
trunc(gei.encumbrance_item_date),
'ENC',
gei.encumbrance_item_id,
adl.adl_line_num, --Changed
'N',
'E',
p_request_id,
gms_bc_packets_s.nextval,
ge.incurred_by_person_id,
gei.job_id,
pet.expenditure_category,
pet.revenue_category_code,
Decode(gei.net_zero_adjustment_flag,
'Y', decode(gei.adjusted_encumbrance_item_id,
NULL, gei.encumbrance_item_id,
gei.adjusted_encumbrance_item_id),
NULL),
gei.transaction_source,
adl.award_set_id,
adl.ind_compiled_set_id
from gl_period_statuses glst,
gl_sets_of_books sob,
pa_projects p,
gms_project_types gpt,
pa_expenditure_types pet,
gms_award_distributions adl,
gms_encumbrance_groups_all geg,
gms_encumbrances_all ge,
gms_encumbrance_items_all gei
where gei.request_id = p_request_id
and ge.encumbrance_group = geg.encumbrance_group
and ge.encumbrance_id = gei.encumbrance_id
and p.project_id = nvl(p_project_id, p.project_id)
and geg.encumbrance_group = nvl(p_enc_group, geg.encumbrance_group) --Bug 5956414
and gei.encumbrance_item_id = adl.expenditure_item_id
and nvl(adl.document_type,'ENC') = 'ENC'
and nvl(adl.adl_status,'A') = 'A'
and gei.enc_distributed_flag = 'N'
and adl.project_id = p.project_id
and p.project_type = gpt.project_type
and gpt.sponsored_flag = 'Y'
and sob.set_of_books_id = p_sob_id
and glst.set_of_books_id = p_sob_id
and gei.encumbrance_item_date between glst.start_date and glst.end_date
and glst.application_id = 101
and glst.adjustment_period_flag = 'N'
and gei.encumbrance_type = pet.expenditure_type
and gei.adjustment_type in ('BURDEN_RECOMPILE', 'BURDEN_RECALC') -- Bug 6761516 'BURDEN_RECALC'
and adl.fc_status = 'N';
p_rows_inserted := p_rows_inserted + sql%rowcount;
select max(adl_line_num)
into l_max_adl_line_num
from gms_award_distributions
where award_set_id = l_brdn_impacted_enc(i).award_set_id;
update gms_award_distributions
set reversed_flag = 'Y',
request_id = p_request_id
where award_set_id = l_brdn_impacted_enc(i).award_set_id
and adl_line_num = l_brdn_impacted_enc(i).adl_line_num
and adl_status = 'A';
gms_error_pkg.gms_debug('Inserting into gms_bc_packets.', 'C');
insert into gms_bc_packets (packet_id,
set_of_books_id,
je_source_name,
je_category_name,
actual_flag,
period_name,
period_year,
period_num,
project_id,
task_id,
award_id,
result_code,
funding_pattern_id,
funding_sequence,
fp_status,
status_code,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
entered_dr,
entered_cr,
expenditure_type,
expenditure_organization_id,
expenditure_item_date,
document_type,
document_header_id,
document_distribution_id,
TRANSFERED_FLAG,
account_type,
request_id,
bc_packet_id,
person_id,
job_id,
expenditure_category,
revenue_category,
adjusted_document_header_id,
transaction_source,
award_set_id,
ind_compiled_set_id)
Select p_packet_id,
p_sob_id,
decode(substr(gei.transaction_source,1,4),
'GMSE', gei.transaction_source,
decode(gei.transaction_source,
'GOLDE', 'Labor Distribution',
'Project Accounting')),
'Encumberances',
'E',
glst.period_name,
glst.period_year,
glst.period_num,
p.project_id,
adl.task_id,
adl.award_id,
NULL,
null,
null,
null,
'P',
p_sys_date,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
p_sys_date,
FND_GLOBAL.LOGIN_ID,
pa_currency.round_currency_amt(decode(sign(adl.raw_cost),1,adl.raw_cost,0)),
pa_currency.round_currency_amt(decode(sign(adl.raw_cost),-1,-1*adl.raw_cost,0)),
gei.encumbrance_type,
nvl(gei.override_to_organization_id,ge.incurred_by_organization_id),
trunc(gei.encumbrance_item_date),
'ENC',
gei.encumbrance_item_id,
adl.adl_line_num, --Changed
'N',
'E',
p_request_id,
gms_bc_packets_s.nextval,
ge.incurred_by_person_id,
gei.job_id,
pet.expenditure_category,
pet.revenue_category_code,
Decode(gei.net_zero_adjustment_flag,
'Y', decode(gei.adjusted_encumbrance_item_id,
NULL, gei.encumbrance_item_id,
gei.adjusted_encumbrance_item_id),
NULL),
gei.transaction_source,
adl.award_set_id,
adl.ind_compiled_set_id
from gl_period_statuses glst,
gl_sets_of_books sob,
pa_projects p,
gms_project_types gpt,
pa_expenditure_types pet,
gms_award_distributions adl,
gms_encumbrance_groups_all geg,
gms_encumbrances_all ge,
gms_encumbrance_items_all gei
where gei.request_id = p_request_id
and ge.encumbrance_group = geg.encumbrance_group
and ge.encumbrance_id = gei.encumbrance_id
and p.project_id = nvl(p_project_id, p.project_id)
and geg.encumbrance_group = nvl(p_enc_group, geg.encumbrance_group) --Bug 5956414
and gei.encumbrance_item_id = adl.expenditure_item_id
and nvl(adl.document_type,'ENC') = 'ENC'
and nvl(adl.adl_status,'A') = 'A'
and gei.enc_distributed_flag = 'N'
and adl.project_id = p.project_id
and p.project_type = gpt.project_type
and gpt.sponsored_flag = 'Y'
and sob.set_of_books_id = p_sob_id
and glst.set_of_books_id = p_sob_id
and gei.encumbrance_item_date between glst.start_date and glst.end_date
and glst.application_id = 101
and glst.adjustment_period_flag = 'N'
and gei.encumbrance_type = pet.expenditure_type
and gei.adjustment_type in ('BURDEN_RECOMPILE', 'BURDEN_RECALC') -- Bug 6761516 added 'BURDEN_RECALC'
and adl.award_set_id = l_brdn_impacted_enc(i).award_set_id
and adl.adl_line_num in (l_negative_ln_adl_tbl(i).adl_line_num, l_positive_ln_adl_tbl(i).adl_line_num);
p_rows_inserted := p_rows_inserted + sql%rowcount;
l_rows_inserted NUMBER; --Bug 5726575
/* ----------------------------- Update baselined budget_version_id ----------------------------------- */
x_budget_version_id number(15);
Cursor Cur_for_bvid_update is
Select distinct award_id,
project_id
from gms_bc_packets
where packet_id = x_packet_id;
/* ---------------- Update of Requset Id on gms_encumbrance_items_all --------------------- */ --1472753
update gms_encumbrance_items_all
set request_id = l_request_id
where encumbrance_item_id in (
select gei.encumbrance_item_id
from pa_projects p,
gms_project_types gpt,
gms_award_distributions adl,
gms_encumbrance_groups_all geg,
gms_encumbrances_all ge,
gms_encumbrance_items_all gei
where geg.encumbrance_group = nvl(p_enc_group,geg.encumbrance_group)
and geg.encumbrance_group_status_code = 'RELEASED' -- Bug Fix 1364085
and ge.encumbrance_group = geg.encumbrance_group
and ge.encumbrance_id = gei.encumbrance_id
and p.project_id = nvl(p_project_id,p.project_id)
and gei.encumbrance_item_date <= nvl(p_end_date, gei.encumbrance_item_date)
and nvl(gei.override_to_organization_id,ge.incurred_by_organization_id)
= nvl(p_org_id,nvl(gei.override_to_organization_id,ge.incurred_by_organization_id))
and gei.encumbrance_item_id = adl.expenditure_item_id
and gei.enc_distributed_flag = 'N'
and adl.project_id = p.project_id
and adl.document_type = 'ENC'
and p.project_type = gpt.project_type
and gpt.sponsored_flag = 'Y'
and nvl(adl.adl_status,'A')= 'A');
/* ---------------- Update of Requset Id on gms_encumbrance_items_all --------------------- */ --1472753
select gl_bc_packets_s.nextval into x_packet_id from dual;
insert into gms_bc_packets (
packet_id,
set_of_books_id,
je_source_name,
je_category_name,
actual_flag,
period_name,
period_year,
period_num,
project_id,
task_id,
award_id,
result_code,
funding_pattern_id,
funding_sequence,
fp_status,
status_code,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
entered_dr,
entered_cr,
expenditure_type,
expenditure_organization_id,
expenditure_item_date,
document_type,
document_header_id,
document_distribution_id,
TRANSFERED_FLAG,
account_type, request_id,
bc_packet_id,
person_id,
job_id,
expenditure_category,
revenue_category,
adjusted_document_header_id,
transaction_source,
award_set_id
)
Select
x_packet_id,
p_sob_id,
--decode(gei.transaction_source,'GOLDE','Labor Distribution','Project Accounting'), --Bug Fix - 1364133
decode(substr(gei.transaction_source,1,4),'GMSE',gei.transaction_source,decode(gei.transaction_source,'GOLDE','Labor Distribution','Project Accounting')), -- Bug 3035863
'Encumberances',
'E',
glst.period_name,
glst.period_year,
glst.period_num,
p.project_id,
adl.task_id,
adl.award_id,
NULL ,
null,
null,
null,
'P', -- Bug 2163845
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID,
pa_currency.round_currency_amt(decode(sign(gei.amount),1,gei.amount,0)),
pa_currency.round_currency_amt(decode(sign(gei.amount),-1,-1*gei.amount,0)),
gei.encumbrance_type,
nvl(gei.override_to_organization_id,ge.incurred_by_organization_id),
trunc(gei.encumbrance_item_date),
'ENC',
gei.encumbrance_item_id,
adl.adl_line_num, --Bug 5693864 1,
'N',
'E',
l_request_id,
gms_bc_packets_s.nextval,
ge.incurred_by_person_id,
gei.job_id,
pet.expenditure_category,
pet.revenue_category_code,
Decode(gei.net_zero_adjustment_flag,'Y',
Decode(gei.adjusted_encumbrance_item_id,
Null,gei.encumbrance_item_id,
gei.adjusted_encumbrance_item_id
), null ), -- fix for bug : 2927485
gei.transaction_source,
adl.award_set_id
from gl_period_STATUSES glst,
gl_sets_of_books sob,
pa_projects p,
gms_project_types gpt,
pa_expenditure_types pet,
gms_award_distributions adl,
gms_encumbrance_groups_all geg,
gms_encumbrances_all ge,
gms_encumbrance_items_all gei
where gei.request_id=l_request_id
and ge.encumbrance_group = geg.encumbrance_group
and ge.encumbrance_id = gei.encumbrance_id
and p.project_id = nvl(p_project_id, p.project_id)
and geg.encumbrance_group = nvl(p_enc_group, geg.encumbrance_group) --Bug 5956414
and gei.encumbrance_item_id = adl.expenditure_item_id
and nvl(adl.document_type,'ENC') = 'ENC'
and nvl(adl.adl_status,'A') = 'A'
and gei.enc_distributed_flag = 'N'
and adl.project_id = p.project_id
and p.project_type = gpt.project_type
and gpt.sponsored_flag = 'Y'
and sob.set_of_books_id = p_sob_id
and glst.set_of_books_id = p_sob_id
and gei.encumbrance_item_date between glst.start_date and glst.end_date
and glst.application_id = 101
and glst.adjustment_period_flag = 'N'
and gei.encumbrance_type = pet.expenditure_type -- Bug 2069132 (RLMI Change)
and nvl(gei.adjustment_type, 'X') not in ('BURDEN_RECOMPILE', 'BURDEN_RECALC'); --Bug 5726575
l_rows_inserted);
x_count := x_count + l_rows_inserted;
for records in cur_for_bvid_update
loop
Begin
select budget_version_id
into x_budget_version_id
from gms_budget_versions
where project_id = records.project_id
and award_id = records.award_id
and budget_status_code = 'B'
and current_flag= 'Y';
update gms_bc_packets
set budget_version_id = x_budget_version_id
where project_id = records.project_id
and award_id = records.award_id
and packet_id = x_packet_id;
update gms_bc_packets gms
set gms.status_code = 'R',
gms.result_code = 'F10',
gms.RES_RESULT_CODE = 'F10',
gms.RES_GRP_RESULT_CODE = 'F10',
gms.TASK_RESULT_CODE = 'F10',
gms.AWARD_RESULT_CODE = 'F10'
where gms.packet_id = x_packet_id
and gms.project_id = records.project_id
and gms.award_id = records.award_id;
select set_of_books_id
into l_sob_id
from pa_implementations;