[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select 1 into l_dummy from gms_budget_versions
where budget_version_id = p_budget_version_id
for update;
Update gms_award_exp_type_act_cost
Set exp_raw_cost = nvl(exp_raw_cost,0) + nvl(p_Tab_Award_exp_burden(x).exp_raw_cost,0),
exp_burdenable_cost = nvl(exp_burdenable_cost,0)+ nvl(p_Tab_Award_exp_burden(x).exp_burdenable_cost,0),
ap_raw_cost = nvl(ap_raw_cost,0) + nvl(p_Tab_Award_exp_burden(x).ap_raw_cost,0),
ap_burdenable_cost = nvl(ap_burdenable_cost,0) + nvl(p_Tab_Award_exp_burden(x).ap_burdenable_cost,0),
po_raw_cost = nvl(po_raw_cost,0) + nvl(p_Tab_Award_exp_burden(x).po_raw_cost,0),
po_burdenable_cost = nvl(po_burdenable_cost,0) + nvl(p_Tab_Award_exp_burden(x).po_burdenable_cost,0),
req_raw_cost = nvl(req_raw_cost,0) + nvl(p_Tab_Award_exp_burden(x).req_raw_cost,0),
req_burdenable_cost = nvl(req_burdenable_cost,0)+ nvl(p_Tab_Award_exp_burden(x).req_burdenable_cost,0),
enc_raw_cost = nvl(enc_raw_cost,0) + nvl(p_Tab_Award_exp_burden(x).enc_raw_cost,0),
enc_burdenable_cost = nvl(enc_burdenable_cost,0)+ nvl(p_Tab_Award_exp_burden(x).enc_burdenable_cost,0)
Where award_id = p_Tab_Award_exp_burden(x).award_id
And expenditure_type = p_Tab_Award_exp_burden(x).expenditure_type;
INSERT INTO GMS_award_exp_type_act_cost (award_id,
expenditure_type,
exp_raw_cost,
exp_burdenable_cost,
ap_raw_cost,
ap_burdenable_cost,
po_raw_cost,
po_burdenable_cost,
req_raw_cost,
req_burdenable_cost,
enc_raw_cost,
enc_burdenable_cost,
created_by,
created_date,
last_updated_by,
last_update_date )
Values (p_Tab_Award_exp_burden(x).award_id,
p_Tab_Award_exp_burden(x).expenditure_type,
nvl(p_Tab_Award_exp_burden(x).exp_raw_cost,0),
nvl(p_Tab_Award_exp_burden(x).exp_burdenable_cost,0),
nvl(p_Tab_Award_exp_burden(x).ap_raw_cost,0),
nvl(p_Tab_Award_exp_burden(x).ap_burdenable_cost,0),
nvl(p_Tab_Award_exp_burden(x).po_raw_cost,0),
nvl(p_Tab_Award_exp_burden(x).po_burdenable_cost,0),
nvl(p_Tab_Award_exp_burden(x).req_raw_cost,0),
nvl(p_Tab_Award_exp_burden(x).req_burdenable_cost,0),
nvl(p_Tab_Award_exp_burden(x).enc_raw_cost,0),
nvl(p_Tab_Award_exp_burden(x).enc_burdenable_cost,0),
nvl(fnd_global.user_id,0),
sysdate,
nvl(fnd_global.user_id,0),
sysdate);
Select award_project_id
into l_award_project_id
from gms_awards_all -- Bug 4732065
where award_id = p_award_id;
l_stage := 'intialize_revenue_records: Delete gms_billing_cancellations';
delete
from gms_billing_cancellations
where award_project_id = l_award_project_id
and actual_project_id = p_project_id;
l_stage := 'intialize_revenue_records: Update Burden Records';
Update gms_burden_components
set revenue_accumulated='N'
where award_project_id = l_award_project_id
and actual_project_id = p_project_id
and event_type = 'REVENUE';
l_stage := 'intialize_revenue_records: Update raw records';
Update gms_event_intersect
set revenue_accumulated='N'
where expenditure_item_id in
(Select expenditure_item_id
from gms_award_distributions
where award_id = p_award_id
and project_id = p_project_id
and adl_status = 'A' -- added for bug 4108031
and document_type = 'EXP') -- added for bug 4108031
and event_type = 'REVENUE';
Select budget_version_id
into l_bvid
from gms_budget_versions
where project_id = p_project_id
and award_id = p_award_id
and budget_type_code ='AC'
and budget_status_code = 'B'
and current_flag='Y';
Delete
from gms_balances
where project_id = p_project_id
and award_id = p_award_id
and budget_version_id = l_bvid
and balance_type='REV';
SELECT MAX(TRUNC(gb.start_date)),MIN(TRUNC(gb.end_date))
FROM gms_balances gb
WHERE gb.project_id = p_project_id
AND gb.award_id = p_award_id
AND gb.budget_version_id = p_budget_version_id
AND gb.balance_type = 'BGT'
AND ( (p_check_task = 'Y' AND gb.task_id = p_bud_task_id) OR
(p_check_task = 'N'))
AND TRUNC(p_expenditure_item_date) between gb.start_date and gb.end_date;
SELECT start_date,completion_date
INTO project_start_date,project_end_date
FROM pa_projects_all -- Bug 4732065 : modified to use _all
WHERE project_id = x_project_id;
SELECT nvl(preaward_date,start_date_active), end_date_active
INTO x_award_start_date,x_award_end_date
FROM gms_awards_all -- Bug 4732065 : modified to use _all
WHERE award_id = x_award_id;
SELECT start_date,completion_date
INTO x_task_start_date,x_task_end_date
FROM pa_tasks
WHERE task_id = x_bud_task_id;
select gps.start_date, gps.end_date
into x_start_date, x_end_date
from gl_period_statuses gps
where gps.application_id = 101
and gps.set_of_books_id = x_set_of_books_id
and trunc(x_expenditure_item_date) between gps.start_date and gps.end_date
and gps.adjustment_period_flag = 'N';
select gbc.budget_version_id
, gbc.project_id
, gbc.award_id
, gbc.task_id
, gbc.bud_task_id
, gbc.top_task_id
, gbc.document_type
, gbc.period_name
, gbc.resource_list_member_id
, gbc.parent_resource_id
, gbc.bud_resource_list_member_id
, gbc.set_of_books_id
, trunc(gbc.expenditure_item_date) expenditure_item_date
, gbc.entered_dr
, gbc.entered_cr
, gbc.actual_flag
, gbv.resource_list_id
, pbm.time_phased_type_code
, pbm.entry_level_code --2673200
, gbc.document_header_id
, gbc.document_distribution_id
, gbc.bc_packet_id
, ga.amount_type
, ga.boundary_code
, nvl(gbc.burdenable_raw_cost,0) burdenable_raw_cost -- this and next 4 added for bug 4053891
, gbc.parent_bc_packet_id
, gbc.expenditure_type
, nvl(gbc.burden_adjustment_flag,'N') burden_adjustment_flag
, gbc.rowid
from gms_budget_versions gbv
, gms_bc_packets gbc
, pa_budget_entry_methods pbm
, gms_awards_all ga
where gbc.status_code = x_status_code --Bug 2138376 : Replaced 'A' with x_status_code
and gbc.packet_id = nvl(x_packet_id, packet_id)
and gbv.budget_version_id = gbc.budget_version_id
and gbv.budget_entry_method_code = pbm.budget_entry_method_code
and ga.award_id = gbc.award_id
and gbc.project_id = nvl(x_project_id,gbc.project_id) /* Bug 3813928 */
and gbc.award_id = nvl(x_award_id,gbc.award_id) /* Bug 3813928 */
--for update of gbc.project_id; Bug4053891
|| update_revenue_balance procedure is called in a loop
|| with in this cursor
*/
Cursor Cur_records is
select distinct ga.award_id award_id, gspf.project_id project_id,imp.set_of_books_id
from gms_summary_project_fundings gspf,
gms_installments gi,
gms_awards_all ga, -- Bug 4732065 : To run the process across org
pa_implementations_all imp -- Bug 4732065 :
where gspf.installment_id = gi.installment_id
and gi.award_id = ga.award_id
and ga.revenue_distribution_rule='COST'
and ga.award_template_flag='DEFERRED'
and ga.org_id = imp.org_id
and (exists (select award_project_id
from gms_event_intersect
where award_project_id = ga.award_project_id
and event_type = 'REVENUE')
OR exists
(select award_project_id
from gms_billing_cancellations
where award_project_id = ga.award_project_id
and calling_process='Revenue')) ;
select set_of_books_id
from gms_awards_all ga,
pa_implementations_all imp
where ga.award_id = p_award_id
and imp.org_id = ga.org_id;
Procedure update_revenue_balance(p_mode IN varchar2 default 'U',
p_award_id IN number,
p_project_id IN number,
p_sob_id IN number,
error_table IN OUT NOCOPY Err_Bal_Tab,
reason_table IN OUT NOCOPY Fail_Tab) IS
l_count number := 0;
select gad.project_id project_id,
gad.award_id award_id,
gad.adl_line_num adl_line_num,
gad.cdl_line_num,
gad.task_id task_id,
gad.bud_task_id,
trunc(item.expenditure_item_date) expenditure_item_date,
gad.resource_list_member_id rlmi,
prm.parent_member_id parent,
gei.expenditure_item_id,
gei.amount amount,
'GEI' from_table,
item.expenditure_type,
to_number(NULL) org_id,
gei.rowid
from gms_event_intersect gei,
pa_expenditure_items_all item,
gms_award_distributions gad ,
pa_resource_list_members prm
where gei.expenditure_item_id = gad.expenditure_item_id
and gei.adl_line_num = gad.adl_line_num
and gei.event_type = 'REVENUE'
-- and gei.revenue_accumulated = 'N'
and item.expenditure_item_id = gad.expenditure_item_id
and gad.resource_list_member_id = prm.resource_list_member_id
and gad.document_type='EXP'
and gad.adl_status='A'
and gad.project_id = p_project_id
and gad.award_id = p_award_id
and NVL(prm.migration_code,'M') ='M' -- Bug 3626671
UNION ALL
-- In this select RLMI has not been derived, GBC.
select gad.project_id project_id,
gad.award_id award_id,
gad.adl_line_num adl_line_num,
gad.cdl_line_num,
gad.task_id task_id,
gad.bud_task_id,
trunc(item.expenditure_item_date) expenditure_item_date,
to_number(NULL) rlmi,
prm.parent_member_id parent,
gbc.expenditure_item_id,
gbc.amount amount,
'GBC' from_table,
gbc.burden_exp_type,
nvl(pea.incurred_by_organization_id ,item.override_to_organization_id) org_id,
gbc.rowid
from gms_burden_components gbc,
pa_expenditure_items_all item ,
pa_expenditures_all pea,
gms_award_distributions gad ,
pa_resource_list_members prm
where gbc.expenditure_item_id = gad.expenditure_item_id
and gbc.adl_line_num = gad.adl_line_num
and gbc.event_type = 'REVENUE'
-- and gbc.revenue_accumulated = 'N'
and item.expenditure_item_id = gad.expenditure_item_id
and item.expenditure_id = pea.expenditure_id
and gad.resource_list_member_id = prm.resource_list_member_id
and gad.document_type='EXP'
and gad.adl_status='A'
and gad.project_id = p_project_id
and gad.award_id = p_award_id
and NVL(prm.migration_code,'M') ='M'; -- Bug 3626671
select gad.project_id project_id,
gad.award_id award_id,
gad.adl_line_num adl_line_num,
gad.cdl_line_num,
gad.task_id task_id,
gad.bud_task_id,
trunc(item.expenditure_item_date) expenditure_item_date,
gad.resource_list_member_id rlmi,
prm.parent_member_id parent,
gei.expenditure_item_id,
gei.amount amount,
'GEI' from_table,
item.expenditure_type,
to_number(NULL) exp_org_id,
gei.rowid
--item.org_id -- Bug 4732065 commented for the bug 5481465
from gms_event_intersect gei,
pa_expenditure_items_all item,
gms_award_distributions gad,
pa_resource_list_members prm
where gei.expenditure_item_id = gad.expenditure_item_id
and gei.adl_line_num = gad.adl_line_num
and gei.event_type = 'REVENUE'
and gei.revenue_accumulated = 'N'
and item.expenditure_item_id = gad.expenditure_item_id
and gad.resource_list_member_id = prm.resource_list_member_id
and gad.document_type='EXP'
and gad.adl_status='A'
--and gad.project_id = p_project_id /* commented for the bug 5481465 */
--and gad.award_id = p_award_id /* Commented for the bug 5481465 */
and NVL(prm.migration_code,'M') ='M' -- Bug 3626671
UNION ALL
-- In this select RLMI has not been derived, GBC.
select gad.project_id project_id,
gad.award_id award_id,
gad.adl_line_num adl_line_num,
gad.cdl_line_num,
gad.task_id task_id,
gad.bud_task_id,
trunc(item.expenditure_item_date) expenditure_item_date,
to_number(NULL) rlmi,
prm.parent_member_id parent,
gbc.expenditure_item_id,
gbc.amount amount,
'GBC' from_table,
gbc.burden_exp_type,
nvl(pea.incurred_by_organization_id,item.override_to_organization_id) exp_org_id,
gbc.rowid
--item.org_id -- Bug 4732065 commented for the bug 5481465
from gms_burden_components gbc,
pa_expenditure_items_all item,
pa_expenditures_all pea,
gms_award_distributions gad,
pa_resource_list_members prm
where gbc.expenditure_item_id = gad.expenditure_item_id
and gbc.adl_line_num = gad.adl_line_num
and gbc.event_type = 'REVENUE'
and gbc.revenue_accumulated = 'N'
and item.expenditure_item_id = gad.expenditure_item_id
and item.expenditure_id = pea.expenditure_id
and gad.resource_list_member_id = prm.resource_list_member_id
and gad.document_type='EXP'
and gad.adl_status='A'
-- and gad.project_id = p_project_id /* Commented for the bug 5481465 */
-- and gad.award_id = p_award_id /* Commented for the bug 5481465 */
and NVL(prm.migration_code,'M') ='M' -- Bug 3626671
UNION ALL
select gad.project_id project_id,
gad.award_id award_id,
gad.adl_line_num adl_line_num,
gad.cdl_line_num,
gad.task_id task_id,
gad.bud_task_id,
trunc(item.expenditure_item_date) expenditure_item_date,
gad.resource_list_member_id rlmi,
prm.parent_member_id parent,
gbi.expenditure_item_id,
gbi.bill_amount amount,
'GBI' from_table,
nvl(gbi.burden_exp_type,item.expenditure_type),
nvl(pea.incurred_by_organization_id,item.override_to_organization_id) exp_org_id,
gbi.rowid
--item.org_id -- Bug 4732065 commented for the bug 5481465
from gms_billing_cancellations gbi,
pa_expenditure_items_all item,
pa_expenditures_all pea,
gms_award_distributions gad,
pa_resource_list_members prm
where gbi.expenditure_item_id = gad.expenditure_item_id
and item.expenditure_item_id = gad.expenditure_item_id
and gad.adl_line_num = gbi.adl_line_num
and item.expenditure_id = pea.expenditure_id
and gad.resource_list_member_id = prm.resource_list_member_id
and gad.document_type='EXP'
and gad.adl_status='A'
-- and gad.project_id = p_project_id /* Commented for the bug 5481465 */
-- and gad.award_id = p_award_id /* Commented for the bug 5481465 */
and NVL(prm.migration_code,'M') ='M' -- Bug 3626671
ORDER BY 2, -- award_id
1; -- project_id
select budget_version_id
into x_budget_version_id
from gms_budget_versions
where project_id = l_project_id_tbl(i) -- p_project_id as part of the bug 5481465
and award_id = l_award_id_tbl(i) -- p_award_id as part of the bug 5481465
and budget_type_code ='AC'
and budget_status_code = 'B'
and current_flag='Y';
Select amount_type, boundary_code
Into x_amount_type, x_boundary_code
From gms_awards_all
Where award_id = l_award_id_tbl(i); --p_award_id; for bug 5481465
Select a.resource_list_id,
b.categorization_code,
b.time_phased_type_code,
b.entry_level_code --2673200
into x_resource_list_id,
x_categorization_code,
x_time_phased_type_code ,
x_entry_level_code --2673200
from gms_budget_versions a,
pa_budget_entry_methods b
where a.budget_version_id = x_budget_version_id
and b.budget_entry_method_code = a.budget_entry_method_code;
select resource_list_member_id
into x_uncategorized_rlmi
from pa_resource_list_members
where resource_list_id = x_resource_list_id
and NVL(migration_code,'M') ='M'; -- Bug 3626671
x_stage:='Update balance';
UPDATE gms_balances gb
set revenue_period_to_date = nvl(revenue_period_to_date,0) + nvl(l_amount_tbl(i),0) --nvl(rev_gen.amount,0) as part of the bug 5481465
WHERE gb.project_id = l_project_id_tbl(i) --rev_gen.project_id as part of the bug 5481465
AND gb.award_id = l_award_id_tbl(i)--rev_gen.award_id as part of the bug 5481465
AND (gb.task_id = l_task_id_tbl(i) --rev_gen.task_id as part of the bug 5481465
or gb.task_id is null)
AND (gb.resource_list_member_id = x_resource_list_member_id
or gb.resource_list_member_id is null)
AND gb.set_of_books_id = x_set_of_books_id
AND gb.budget_version_id = x_budget_version_id
AND gb.balance_type = 'REV'
AND /*rev_gen.expenditure_item_date for bug 5481465*/l_ei_date_tbl(i) between gb.start_date and gb.end_date
AND rownum = 1;
gms_error_pkg.gms_debug('No Balance Line Updated','C');
select gps.start_date, gps.end_date
into x_start_date, x_end_date
from gl_period_statuses gps
where gps.application_id = 101
and gps.set_of_books_id = x_set_of_books_id
and /*rev_gen.expenditure_item_date for bug 5481465*/ l_ei_date_tbl(i) between gps.start_date and gps.end_date
and gps.adjustment_period_flag = 'N';
select start_date , end_date
into x_start_date , x_end_date
from pa_periods gpa /* Bug 6721990: Replaced pa_periods_all with pa_periods */
where /* rev_gen.expenditure_item_date for bug 5481465 */ l_ei_date_tbl(i) between gpa.start_date and gpa.end_date;
select gb.start_date, gb.end_date
into x_start_date, x_end_date
from gms_balances gb
where gb.budget_version_id = x_budget_version_id
and /* rev_gen.expenditure_item_date for bug 5481465 */ l_ei_date_tbl(i) between
gb.start_date and gb.end_date
and rownum = 1;
x_stage:='Insert New Balance';
insert into gms_balances (project_id
,award_id
,task_id
,resource_list_member_id
,set_of_books_id
,budget_Version_id
,balance_type
,last_update_date
,last_updated_by
,created_by
,creation_date
,last_update_login
,start_date
,end_date
,parent_member_id
,revenue_period_to_date
)
values
(l_project_id_tbl(i)--rev_gen.project_id /* For bug 5481465 */
,l_award_id_tbl(i)--rev_gen.award_id /* For bug 5481465 */
,l_task_id_tbl(i)--rev_gen.task_id /* For bug 5481465 */
,x_resource_list_member_id
,x_set_of_books_id
,x_budget_version_id
,'REV'
,sysdate
,FND_GLOBAL.USER_ID
,FND_GLOBAL.USER_ID
,sysdate
,FND_GLOBAL.LOGIN_ID
,x_start_date
,x_end_date
,l_prnt_member_id_tbl(i) --rev_gen.parent
,l_amount_tbl(i) --rev_gen.amount
);
gms_error_pkg.gms_debug('After Balance Line Insert','C');
update gms_event_intersect
set revenue_accumulated = 'Y'
where rowid = l_rowid_tbl(i);--rev_gen.rowid; for bug 5481465
update gms_burden_components
set revenue_accumulated = 'Y'
where rowid = l_rowid_tbl(i);--rev_gen.rowid; for bug 5481465
delete from gms_billing_cancellations
where rowid = l_rowid_tbl(i);--rev_gen.rowid; for bug 5481465
l_project_id_tbl.delete;
l_award_id_tbl.delete;
l_adl_ln_num_id_tbl.delete;
l_cdl_ln_num_id_tbl.delete;
l_task_id_tbl.delete;
l_bud_task_id_tbl.delete;
l_ei_date_tbl.delete;
l_rlmi_tbl.delete;
l_prnt_member_id_tbl.delete;
l_ei_id_tbl.delete;
l_amount_tbl.delete;
l_fr_tab_tbl.delete;
l_exp_type_tbl.delete;
l_org_id_tbl.delete;
l_rowid_tbl.delete;
l_rowid_tbl.delete;
X_token_val5 => 'GMS_SWEEPER.update_revenue_balance, Stage: '|| X_Stage,
X_Exec_Type => 'C',
X_Err_Code => St_Err_Code,
X_Err_Buff => St_Err_Buff);
end update_revenue_balance;
upd_error_table.delete;
upd_reason_table.delete;
x_stage := '100'; -- Delete old records from gms_bc_packets
delete from gms_bc_packets
where status_code IN ('R', 'T', 'S', 'F','C','I','P','E','X')
and (sysdate - creation_date) >= l_offset_days;
delete from gms_bc_packets gms
where status_code IN ('P')
and NOT EXISTS (SELECT 'x'
FROM v$session
WHERE audsid = gms.session_id
and Serial# = gms.serial_id);
x_stage := '200'; -- Delete from gms_bc_packet_arrival_order
DELETE gms_bc_packet_arrival_order ao
WHERE NOT EXISTS (
SELECT 1
FROM gms_bc_packets
WHERE packet_id = ao.packet_id
);
x_stage := '250'; -- delete transactions left in pending state
gms_funds_control_pkg.delete_pending_txns(retcode,errbuf);
Tab_Award_exp_burden.delete;
Tab_Award_exp_burden.delete;
x_stage := '400'; -- Update GMS_BALANCES record
/* UPDATE sql commented and rewritten into two separate queries
inside IF..ELSE blocks based on rec_gms_packets.document_type */
/*
UPDATE gms_balances gb
SET gb.actual_period_to_date = nvl(gb.actual_period_to_date,0) +
(nvl(rec_gms_packets.entered_dr,0)- nvl(rec_gms_packets.entered_cr,0)) *
decode(rec_gms_packets.document_type,'EXP',1,0),
gb.encumb_period_to_date = nvl(gb.encumb_period_to_date,0) +
(nvl(rec_gms_packets.entered_dr,0)- nvl(rec_gms_packets.entered_cr,0)) *
decode(rec_gms_packets.document_type,'REQ',1,'PO',1,'AP',1,'ENC',1,0)
WHERE gb.project_id = rec_gms_packets.project_id
AND gb.award_id = rec_gms_packets.award_id
AND ((rec_gms_packets.document_type = 'BGT'
and gb.task_id = rec_gms_packets.bud_task_id)
OR (rec_gms_packets.document_type <>'BGT' -- Bug 2138376 : changed to <> as per GSCC standards
and gb.task_id=rec_gms_packets.task_id))
AND gb.resource_list_member_id = rec_gms_packets.resource_list_member_id
AND gb.set_of_books_id = rec_gms_packets.set_of_books_id
AND gb.budget_version_id = rec_gms_packets.budget_version_id
AND gb.balance_type = rec_gms_packets.document_type
AND rec_gms_packets.expenditure_item_date between gb.start_date and gb.end_date
AND rownum = 1;
UPDATE gms_balances gb
SET gb.actual_period_to_date = nvl(gb.actual_period_to_date,0) +
(nvl(rec_gms_packets.entered_dr,0)- nvl(rec_gms_packets.entered_cr,0)) *
decode(rec_gms_packets.document_type,'EXP',1,0),
gb.encumb_period_to_date = nvl(gb.encumb_period_to_date,0) +
(nvl(rec_gms_packets.entered_dr,0)- nvl(rec_gms_packets.entered_cr,0)) *
decode(rec_gms_packets.document_type,'REQ',1,'PO',1,'AP',1,'ENC',1,0)
WHERE gb.project_id = rec_gms_packets.project_id
AND gb.award_id = rec_gms_packets.award_id
AND gb.task_id = rec_gms_packets.bud_task_id --Bug5875538 for Perf. Fix
AND gb.resource_list_member_id = rec_gms_packets.resource_list_member_id
AND gb.set_of_books_id = rec_gms_packets.set_of_books_id
AND gb.budget_version_id = rec_gms_packets.budget_version_id
AND gb.balance_type = rec_gms_packets.document_type
AND rec_gms_packets.expenditure_item_date between gb.start_date and gb.end_date
AND rownum = 1;
UPDATE gms_balances gb
SET gb.actual_period_to_date = nvl(gb.actual_period_to_date,0) +
(nvl(rec_gms_packets.entered_dr,0)- nvl(rec_gms_packets.entered_cr,0)) *
decode(rec_gms_packets.document_type,'EXP',1,0),
gb.encumb_period_to_date = nvl(gb.encumb_period_to_date,0) +
(nvl(rec_gms_packets.entered_dr,0)- nvl(rec_gms_packets.entered_cr,0)) *
decode(rec_gms_packets.document_type,'REQ',1,'PO',1,'AP',1,'ENC',1,0)
WHERE gb.project_id = rec_gms_packets.project_id
AND gb.award_id = rec_gms_packets.award_id
AND gb.task_id=rec_gms_packets.task_id --Bug5875538 for Perf. Fix
AND gb.resource_list_member_id = rec_gms_packets.resource_list_member_id
AND gb.set_of_books_id = rec_gms_packets.set_of_books_id
AND gb.budget_version_id = rec_gms_packets.budget_version_id
AND gb.balance_type = rec_gms_packets.document_type
AND rec_gms_packets.expenditure_item_date between gb.start_date and gb.end_date
AND rownum = 1;
select gps.start_date, gps.end_date
into x_st_date, x_ed_date
from gl_period_statuses gps
where gps.application_id = 101
and gps.set_of_books_id = rec_gms_packets.set_of_books_id
and rec_gms_packets.expenditure_item_date between gps.start_date and gps.end_date
and gps.adjustment_period_flag = 'N';
select start_date , end_date
into x_st_date , x_ed_date
from pa_periods gpa --Bug 4732065 /*bug 6660289*/
where rec_gms_packets.expenditure_item_date between gpa.start_date and gpa.end_date;
insert into gms_balances (project_id
,award_id
,task_id
,top_task_id
,resource_list_member_id
,set_of_books_id
,budget_Version_id
,balance_type
,last_update_date
,last_updated_by
,created_by
,creation_date
,last_update_login
,start_date
,end_date
,parent_member_id
,budget_period_to_date
,actual_period_to_date
,encumb_period_to_date
)
values
(rec_gms_packets.project_id
,rec_gms_packets.award_id
,decode(rec_gms_packets.document_type,'BGT',rec_gms_packets.bud_task_id,
rec_gms_packets.task_id)
,rec_gms_packets.top_task_id
,rec_gms_packets.resource_list_member_id
,rec_gms_packets.set_of_books_id
,rec_gms_packets.budget_Version_id
,rec_gms_packets.document_type
,sysdate
,FND_GLOBAL.USER_ID
,FND_GLOBAL.USER_ID
,sysdate
,FND_GLOBAL.LOGIN_ID
,x_st_date
,x_ed_date
,rec_gms_packets.parent_resource_id
,0
,(nvl(rec_gms_packets.entered_dr,0)- nvl(rec_gms_packets.entered_cr,0)) *
decode(rec_gms_packets.document_type,'EXP',1,0)
,(nvl(rec_gms_packets.entered_dr,0)- nvl(rec_gms_packets.entered_cr,0)) *
decode(rec_gms_packets.document_type,'REQ',1,'PO',1,'AP',1,'ENC',1,0)
);
x_stage := '900'; -- Update gms_bc_packet record status to 'X'
update gms_bc_packets set status_code = 'X'
WHERE rowid=rec_gms_packets.rowid;
update gms_award_distributions
set accumulated_flag='Y'
where expenditure_item_id = rec_gms_packets.document_header_id
and rec_gms_packets.document_distribution_id = decode(rec_gms_packets.document_type, --Bug 5726575
'EXP', cdl_line_num,
'ENC', adl_line_num)
and nvl(reversed_flag, 'N') = 'N' --Bug 5726575
and line_num_reversed is null
and document_type=rec_gms_packets.document_type
and award_id=rec_gms_packets.award_id
and project_id = rec_gms_packets.project_id
and task_id = rec_gms_packets.task_id
and adl_status='A'
and cost_distributed_flag='Y'
and fc_status='A';
update gms_award_distributions
set accumulated_flag='Y'
where po_distribution_id = rec_gms_packets.document_distribution_id
and document_type=rec_gms_packets.document_type
and award_id=rec_gms_packets.award_id
and project_id = rec_gms_packets.project_id
and task_id = rec_gms_packets.task_id
and adl_status='A'
and fc_status='A';
update gms_award_distributions
set accumulated_flag='Y'
where distribution_id = rec_gms_packets.document_distribution_id
and document_type=rec_gms_packets.document_type
and award_id=rec_gms_packets.award_id
and project_id = rec_gms_packets.project_id
and task_id = rec_gms_packets.task_id
and adl_status='A'
and fc_status='A';
update gms_award_distributions
set accumulated_flag='Y'
where invoice_id = rec_gms_packets.document_header_id
-- AP Lines uptake: changed join from with distribution num to distribution id
and invoice_distribution_id = rec_gms_packets.document_distribution_id
and document_type=rec_gms_packets.document_type
and award_id=rec_gms_packets.award_id
and project_id = rec_gms_packets.project_id
and task_id = rec_gms_packets.task_id
and adl_status='A'
and fc_status='A';
update_revenue_balance(x_mode,x_award_id,x_project_id,l_sob_id,upd_error_table,upd_reason_table);
update gms_award_distributions
set accumulated_flag='Y'
where award_id = x_award_id
and project_id = x_project_id
and adl_status='A'
and fc_status='A';
/* update_revenue_balance(x_mode,records.award_id,records.project_id,records.set_of_books_id, commented and added below line for the bug 5481465*/
update_revenue_balance(x_mode,-1,-1,l_sob_id,
upd_error_table,upd_reason_table);