[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure insert_row (x_rowid in out NOCOPY VARCHAR2 ,
x_encumbrance_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_encumbrance_group_status in VARCHAR2,
x_encumbrance_ending_date in DATE,
x_system_linkage_function in VARCHAR2,
x_control_count in NUMBER DEFAULT NULL,
x_control_total_amount in NUMBER DEFAULT NULL,
x_description in VARCHAR2 DEFAULT NULL,
x_last_update_login in NUMBER DEFAULT NULL,
x_transaction_source in VARCHAR2 DEFAULT NULL,
x_org_id in NUMBER,
x_request_id IN NUMBER DEFAULT NULL /* Bug 5689213 */
) is
cursor c is select rowid from gms_encumbrance_groups
where encumbrance_group = x_encumbrance_group;
insert into gms_encumbrance_groups (encumbrance_group,
last_update_date,
last_updated_by ,
creation_date,
created_by,
encumbrance_group_status_code,
encumbrance_ending_date,
system_linkage_function,
control_count,
control_total_amount,
description,
last_update_login,
transaction_source,
org_id,
request_id) /* Bug 5689213 */
values (x_encumbrance_group,
x_last_update_date,
x_last_updated_by,
x_creation_date,
x_created_by,
--'WORKING', -- Fix for bug : 2111317
x_status ,
x_encumbrance_ending_date, -- Fix for bug : 2111317
x_system_linkage_function,
x_control_count,
x_control_total_amount,
x_description,
x_last_update_login,
x_transaction_source ,
x_org_id,
x_request_id); /* Bug 5689213 */
END insert_row;
procedure update_row (x_rowid in VARCHAR2,
x_encumbrance_group in VARCHAR2,
x_last_update_date in DATE,
x_last_updated_by in NUMBER,
x_encumbrance_group_status in VARCHAR2,
x_encumbrance_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) is
cursor c_orig_group is select * from gms_encumbrance_groups
where rowid = x_rowid;
update gms_encumbrance_groups
set encumbrance_group = x_encumbrance_group,
last_update_date = x_last_update_date,
last_updated_by = x_last_updated_by,
encumbrance_ending_date = x_encumbrance_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
where rowid = x_rowid;
END update_row;
procedure delete_row (x_rowid in VARCHAR2) is
cursor get_group is select encumbrance_group,
encumbrance_group_status_code
from gms_encumbrance_groups
where rowid = x_rowid;
cursor expnds is select encumbrance_id from gms_encumbrances
where encumbrance_group = groups_rec.encumbrance_group
for update of encumbrance_id nowait;
gms_awards_dist_pkg.delete_adls(exp_rec.encumbrance_id, NULL, 'ENC' ) ;
gms_encumbrances_pkg.delete_row (exp_rec.encumbrance_id);
delete from gms_encumbrance_groups
where rowid = x_rowid;
END delete_row;
select 1 into dummy
from gms_encumbrance_groups
where rowid = x_rowid
for update of encumbrance_group nowait;
* existing encumbrance 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 encumbrance group. Modifies all encumbrances for that
-- group to have status 'APPROVED'.
procedure release (x_encumbrance_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 gms_encumbrance_groups
where encumbrance_group = x_encumbrance_group;
cursor lock_exps is select encumbrance_id from gms_encumbrances
where encumbrance_group = x_encumbrance_group
for update of encumbrance_status_code nowait;
update gms_encumbrance_groups
set encumbrance_group_status_code = 'RELEASED'
where encumbrance_group = x_encumbrance_group;
update gms_encumbrances
set encumbrance_status_code = 'APPROVED'
where encumbrance_group = x_encumbrance_group;
cursor c_orig_group is select * from gms_encumbrance_groups
where encumbrance_group = x_encumbrance_group
for update of encumbrance_group_status_code nowait;
cursor lock_exps is select encumbrance_id from gms_encumbrances
where encumbrance_group = x_encumbrance_group
for update of encumbrance_status_code nowait;
update gms_encumbrance_groups
set encumbrance_group_status_code = 'WORKING'
where encumbrance_group = x_encumbrance_group;
update gms_encumbrances
set encumbrance_status_code = 'WORKING'
where encumbrance_group = x_encumbrance_group;
cursor c_orig_group is select * from gms_encumbrance_groups
where encumbrance_group = x_encumbrance_group;
select count(*) from gms_encumbrance_items
where encumbrance_id in
(select encumbrance_id from gms_encumbrances
where encumbrance_group = x_encumbrance_group);
select count(*) from gms_encumbrances
where encumbrance_group = x_encumbrance_group;
select sum(amount)
from gms_encumbrance_items
where encumbrance_id in
(select encumbrance_id from gms_encumbrances
where encumbrance_group = x_encumbrance_group);
select count(*) from gms_encumbrance_items
where encumbrance_id in (
select encumbrance_id from gms_encumbrances
where encumbrance_group = x_encumbrance_group )
and amount is null ;
cursor lock_exps is select encumbrance_id from gms_encumbrances
where encumbrance_group = x_encumbrance_group
for update of encumbrance_status_code nowait;
update gms_encumbrance_groups
set encumbrance_group_status_code = 'SUBMITTED'
where encumbrance_group = x_encumbrance_group;
update gms_encumbrances
set encumbrance_status_code = 'SUBMITTED'
where encumbrance_group = x_encumbrance_group;