[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure update_exp (p_expenditure_group in VARCHAR2 ,
p_exp_grp_status_code in Varchar2 ,
p_exp_status_code in Varchar2); /* Bug 3754869 and 3754875 */
procedure insert_row (x_rowid in out NOCOPY VARCHAR2,
x_expenditure_group in VARCHAR2,
x_last_update_date in DATE,
x_last_updated_by in NUMBER,
x_creation_date in DATE,
x_created_by in NUMBER,
x_expenditure_group_status in VARCHAR2,
x_expenditure_ending_date in DATE,
x_system_linkage_function in VARCHAR2,
x_control_count in NUMBER,
x_control_total_amount in NUMBER,
x_description in VARCHAR2,
x_last_update_login in NUMBER,
x_transaction_source in VARCHAR2,
x_period_accrual_flag in VARCHAR2,
P_Org_Id in NUMBER) -- 12i MOAC changes
is
cursor c is select rowid from pa_expenditure_groups
where expenditure_group = x_expenditure_group;
insert into pa_expenditure_groups (
expenditure_group,
last_update_date,
last_updated_by ,
creation_date,
created_by,
expenditure_group_status_code,
expenditure_ending_date,
system_linkage_function,
control_count,
control_total_amount,
description,
last_update_login,
transaction_source,
period_accrual_flag,
org_id) -- 12i MOAC changes
values (x_expenditure_group,
x_last_update_date,
x_last_updated_by,
x_creation_date,
x_created_by,
'WORKING',
x_expenditure_ending_date,
x_system_linkage_function,
x_control_count,
x_control_total_amount,
x_description,
x_last_update_login,
x_transaction_source,
x_period_accrual_flag,
P_Org_Id); -- 12i MOAC changes
END insert_row;
procedure update_row (x_rowid in VARCHAR2,
x_expenditure_group in VARCHAR2,
x_last_update_date in DATE,
x_last_updated_by in NUMBER,
x_expenditure_group_status in VARCHAR2,
x_expenditure_ending_date in DATE,
x_system_linkage_function in VARCHAR2,
x_control_count in NUMBER,
x_control_total_amount in NUMBER,
x_description in VARCHAR2,
x_last_update_login in NUMBER,
x_transaction_source in VARCHAR2,
x_period_accrual_flag in VARCHAR2) is
cursor c_orig_group is select * from pa_expenditure_groups
where rowid = x_rowid;
update pa_expenditure_groups
set expenditure_group = x_expenditure_group,
last_update_date = x_last_update_date,
last_updated_by = x_last_updated_by,
expenditure_ending_date = x_expenditure_ending_date,
system_linkage_function = x_system_linkage_function,
control_count = x_control_count,
control_total_amount = x_control_total_amount,
description = x_description,
last_update_login = x_last_update_login,
transaction_source = x_transaction_source,
period_accrual_flag = x_period_accrual_flag
where rowid = x_rowid;
END update_row;
procedure delete_row (x_rowid in VARCHAR2) is
cursor get_group is select expenditure_group,
expenditure_group_status_code
from pa_expenditure_groups
where rowid = x_rowid;
cursor expnds is select expenditure_id from pa_expenditures
where expenditure_group = groups_rec.expenditure_group
for update of expenditure_id nowait;
gms_awards_dist_pkg.delete_adls(exp_rec.expenditure_id, NULL, 'EXP' ) ;
pa_expenditures_pkg.delete_row (exp_rec.expenditure_id);
delete from pa_expenditure_groups
where rowid = x_rowid;
END delete_row;
select 1 into dummy
from pa_expenditure_groups
where rowid = x_rowid
for update of expenditure_group nowait;
* existing expenditure group. They are called by the update_row
* table handler if the corresponding change to the status indicates
* one of the actions has been performed, or can be called by a user
* directly.
*************************************************************************/
-- Release an expenditure group. Modifies all expenditures for that
-- group to have status 'APPROVED'.
procedure release (x_expenditure_group in VARCHAR2,
x_err_code in out NOCOPY NUMBER,
x_return_status in out NOCOPY VARCHAR2) is
cursor c_orig_group is select * from pa_expenditure_groups
where expenditure_group = x_expenditure_group;
cursor lock_exps is select expenditure_id from pa_expenditures
where expenditure_group = x_expenditure_group
for update of expenditure_status_code nowait;
update pa_expenditure_groups
set expenditure_group_status_code = 'RELEASED'
where expenditure_group = x_expenditure_group;
update pa_expenditures
set expenditure_status_code = 'APPROVED'
where expenditure_group = x_expenditure_group;
update_exp ( p_expenditure_group => x_expenditure_group ,
p_exp_grp_status_code => 'RELEASED' ,
p_exp_status_code => 'APPROVED' );
cursor c_orig_group is select * from pa_expenditure_groups
where expenditure_group = x_expenditure_group
for update of expenditure_group_status_code nowait;
cursor lock_exps is select expenditure_id from pa_expenditures
where expenditure_group = x_expenditure_group
for update of expenditure_status_code nowait;
update pa_expenditure_groups
set expenditure_group_status_code = 'WORKING'
where expenditure_group = x_expenditure_group;
update pa_expenditures
set expenditure_status_code = 'WORKING'
where expenditure_group = x_expenditure_group;
update_exp ( p_expenditure_group => x_expenditure_group ,
p_exp_grp_status_code => 'WORKING' ,
p_exp_status_code => 'WORKING' );
cursor c_orig_group is select * from pa_expenditure_groups
where expenditure_group = x_expenditure_group;
select count(*) from pa_expenditure_items
where expenditure_id in
(select expenditure_id from pa_expenditures
where expenditure_group = x_expenditure_group);
select count(*) from pa_expenditures
where expenditure_group = x_expenditure_group;
select sum(quantity)
from pa_expenditure_items
where expenditure_id in
(select expenditure_id from pa_expenditures
where expenditure_group = x_expenditure_group);
select count(*) from pa_expenditure_items
where expenditure_id in (
select expenditure_id from pa_expenditures
where expenditure_group = x_expenditure_group )
and quantity is null ;
cursor lock_exps is select expenditure_id from pa_expenditures
where expenditure_group = x_expenditure_group
for update of expenditure_status_code nowait;
update pa_expenditure_groups
set expenditure_group_status_code = 'SUBMITTED'
where expenditure_group = x_expenditure_group;
update pa_expenditures
set expenditure_status_code = 'SUBMITTED'
where expenditure_group = x_expenditure_group;
update_exp ( p_expenditure_group => x_expenditure_group ,
p_exp_grp_status_code => 'SUBMITTED' ,
p_exp_status_code => 'SUBMITTED' );
procedure update_exp (p_expenditure_group in varchar2 ,
p_exp_grp_status_code in Varchar2 ,
p_exp_status_code in Varchar2
)
Is
Begin
update pa_expenditure_groups
set expenditure_group_status_code = p_exp_grp_status_code
where expenditure_group = p_expenditure_group ;
update pa_expenditures
set expenditure_status_code = p_exp_status_code
where expenditure_group = p_expenditure_group ;