[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
update_control_rec control_rec_type;
SELECT DISTINCT NAME
FROM amw_controls_tl;
CURSOR update_enabled IS
SELECT control_rev_id, description, control_id, controlname, approval_status,
approval_status_name, object_version_number, rev_num,
curr_approved_flag, latest_revision_flag, requestor_id,
update_switcher, delete_switcher, end_date
FROM (SELECT ac.control_id, ac.control_type, ac.CATEGORY, ac.SOURCE,
ac.control_location, ac.automation_type,
ac.application_id, fav.application_name, ac.job_id,
ac.requestor_id, act.NAME as controlname, act.description,
pj.NAME as job_name, ac.object_version_number,
amw_controls_page_pkg.get_lookup_value('AMW_CONTROL_TYPE',ac.control_type) as control_type_name,
amw_controls_page_pkg.get_lookup_value('AMW_CONTROL_LOCATION',ac.control_location) as control_location_name,
amw_controls_page_pkg.get_lookup_value('AMW_AUTOMATION_TYPE',ac.automation_type) as automation_type_name,
amw_controls_page_pkg.get_control_source(ac.SOURCE,ac.control_type,ac.automation_type,ac.application_id,ac.control_rev_id) as control_source_name,
act.physical_evidence, 'N' select_flag,ac.control_rev_id,
ac.curr_approved_flag,ac.latest_revision_flag,
ac.approval_status,ac.end_date,
amw_controls_page_pkg.get_lookup_value('AMW_CONTROL_APPROVAL_STATUS',ac.approval_status) as approval_status_name,
DECODE(ac.curr_approved_flag,'Y',DECODE(ac.latest_revision_flag,'N', DECODE(ac.end_date,NULL, 'ChangeInProgress','NoChangeInProgress'),'NoChangeInProgress'),'NoChangeInProgress') as progress_switcher,
DECODE(ac.approval_status,'D','UpdateEnabled','UpdateDisabled') as update_switcher,
--(SELECT control_rev_id FROM amw_controls_b WHERE control_id =ac.control_id AND latest_revision_flag = 'Y') as latest_control_rev_id,
--(SELECT rev_num FROM amw_controls_b WHERE control_id = ac.control_id AND latest_revision_flag = 'Y') as latest_control_rev_num,
ac.rev_num,
DECODE(ac.approval_status,'D', DECODE (ac.end_date,NULL, 'DeleteEnabled','DeleteDisabled'),'DeleteDisabled') delete_switcher,
ac.latest_revision_flag|| ac.curr_approved_flag control_revision_choice,
amw_controls_page_pkg.get_lookup_value('AMW_REVISION_VIEW_CHOICE',ac.latest_revision_flag|| ac.curr_approved_flag) revision
FROM amw_controls_b ac,
amw_controls_tl act,
fnd_application_vl fav,
per_jobs pj
WHERE ac.application_id = fav.application_id(+)
AND ac.job_id = pj.job_id(+)
AND ac.control_rev_id = act.control_rev_id
AND act.LANGUAGE = USERENV ('LANG'))
WHERE controlname LIKE p_control_rec.NAME
AND approval_status = 'D';
updenb update_enabled%ROWTYPE;
SELECT act.NAME, act.description, ac.control_rev_id, ac.control_id, ac.rev_num,
ac.object_version_number, ac.approval_status,
ac.curr_approved_flag, ac.latest_revision_flag, ac.requestor_id
FROM amw_controls_b ac, amw_controls_tl act
WHERE ac.latest_revision_flag = 'Y'
AND act.control_rev_id = ac.control_rev_id
AND act.LANGUAGE = USERENV ('LANG')
AND ( ac.approval_status = 'A'
OR ac.approval_status = 'R'
OR ac.approval_status = 'P'
)
AND act.NAME LIKE p_control_rec.NAME;
l_update_row_found NUMBER := 0;
SELECT control_id
FROM amw_controls_b
WHERE control_rev_id = l_control_rev_id;
fnd_file.put_line (fnd_file.LOG,'Opening update_enabled');
OPEN update_enabled;
FETCH update_enabled
INTO updenb;
EXIT WHEN update_enabled%NOTFOUND;
l_update_row_found := l_update_row_found + 1;
CLOSE update_enabled;
----fnd_file.put_line (fnd_file.LOG,'Closed update_enabled, l_update_row_found: '||l_update_row_found);
IF (l_update_row_found = 0) THEN
---this has to be revision mode
OPEN rev_enabled;
update_control_rec.latest_revision_flag := 'N';
update_control_rec.curr_approved_flag := 'N';
update_control_rec.end_date := sysdate;
update_control_rec.control_rev_id := l_control_rec.control_rev_id;
update_control_rec.NAME := l_control_rec.NAME;
update_control_rec.description := l_description;
update_control_rec.object_version_number := l_control_rec.object_version_number + 1;
if(update_control_rec.classification is null) then
update_control_rec.classification := l_control_rec.classification;
----fnd_file.put_line (fnd_file.LOG,'Revising this one, going to Update_Control');
update_control
(p_api_version_number => l_api_version_number,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_control_rec => update_control_rec,
x_object_version_number => l_object_version_number
);
ELSIF (l_update_row_found > 1) THEN ----elsif for update status
amw_utility_pvt.error_message(p_message_name => 'AMW_UNEXPECT_ERROR',
p_token_name => 'OBJ_TYPE',
p_token_value => G_OBJ_TYPE);
ELSIF (l_update_row_found = 1) THEN ----elsif for update status
fnd_file.put_line (fnd_file.LOG,'Inside L_Update_Row_Found = 1, l_control_rec.control_id: '||l_control_rec.control_id);
select count(*) into l_rev_count from amw_controls_b
where control_id=l_control_rec.control_id
and curr_approved_flag='Y'
and latest_revision_flag='N';
/*select nvl(object_version_number,0) into l_o_v_n from amw_controls_b
where control_id=l_control_rec.control_id
and curr_approved_flag='Y'
and latest_revision_flag='N';
select control_rev_id into l_c_r_i from amw_controls_b
where control_id=l_control_rec.control_id
and curr_approved_flag='Y'
and latest_revision_flag='N';
update amw_controls_b
set object_version_number=object_version_number+1,
curr_approved_flag='N',
latest_revision_flag='N',
---12.28.2004 npanandi: setting the EndDate to sysdate for
---previously Approved version of this Control
end_date=sysdate,
last_updated_by=g_user_id,
last_update_date=sysdate,
last_update_login=g_login_id
where control_rev_id=l_c_r_i;
---now you can update this Control
l_control_rec.object_version_number := l_control_rec.object_version_number + 1;
update_control (p_api_version_number => l_api_version_number,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_control_rec => l_control_rec,
x_object_version_number => l_object_version_number
);
x_mode_affected := 'UPDATE_CONTROL';
SELECT amw_controls_s.NEXTVAL
FROM DUAL;
SELECT amw_control_rev_s.NEXTVAL
FROM DUAL;
SELECT 1
FROM amw_controls_b
WHERE control_id = l_id;
SELECT 1
FROM amw_controls_b
WHERE control_rev_id = l_rev_id;
SELECT ROWID
FROM amw_controls_b
WHERE control_rev_id = x_control_rev_id;
l_control_rec.last_update_date := SYSDATE;
l_control_rec.last_updated_by := g_user_id;
l_control_rec.last_update_login := g_login_id;
amw_controls_pkg.insert_row
(x_rowid => l_row_id,
x_control_rev_id => l_control_rec.control_rev_id,
x_object_version_number => l_control_rec.object_version_number,
x_orig_system_reference => l_control_rec.orig_system_reference,
x_latest_revision_flag => l_control_rec.latest_revision_flag,
x_requestor_id => l_control_rec.requestor_id,
x_control_id => l_control_rec.control_id,
x_approval_status => l_control_rec.approval_status,
x_automation_type => l_control_rec.automation_type,
x_application_id => l_control_rec.application_id,
x_job_id => l_control_rec.job_id,
x_created_by_module => l_control_rec.created_by_module,
x_attribute13 => l_control_rec.attribute13,
x_attribute14 => l_control_rec.attribute14,
x_attribute15 => l_control_rec.attribute15,
x_security_group_id => l_control_rec.security_group_id,
x_control_location => l_control_rec.control_location,
x_rev_num => l_control_rec.rev_num,
x_approval_date => l_control_rec.approval_date,
x_control_type => l_control_rec.control_type,
x_category => l_control_rec.CATEGORY,
x_source => l_control_rec.SOURCE,
x_attribute_category => l_control_rec.attribute_category,
x_attribute1 => l_control_rec.attribute1,
x_attribute2 => l_control_rec.attribute2,
x_attribute3 => l_control_rec.attribute3,
x_attribute4 => l_control_rec.attribute4,
x_attribute5 => l_control_rec.attribute5,
x_attribute6 => l_control_rec.attribute6,
x_attribute7 => l_control_rec.attribute7,
x_attribute8 => l_control_rec.attribute8,
x_attribute9 => l_control_rec.attribute9,
x_attribute10 => l_control_rec.attribute10,
x_attribute11 => l_control_rec.attribute11,
x_attribute12 => l_control_rec.attribute12,
x_end_date => l_control_rec.end_date,
x_curr_approved_flag => l_control_rec.curr_approved_flag,
x_name => l_control_rec.NAME,
x_description => l_control_rec.description,
x_physical_evidence => l_control_rec.physical_evidence,
x_creation_date => l_control_rec.creation_date,
x_created_by => l_control_rec.created_by,
x_last_update_date => l_control_rec.last_update_date,
x_last_updated_by => l_control_rec.last_updated_by,
x_last_update_login => l_control_rec.last_update_login,
x_preventive_control => l_control_rec.preventive_control,
x_detective_control => l_control_rec.detective_control,
x_disclosure_control => l_control_rec.disclosure_control,
x_key_mitigating => l_control_rec.key_mitigating,
x_verification_source => l_control_rec.verification_source,
x_verification_source_name => l_control_rec.verification_source_name,
x_verification_instruction => l_control_rec.verification_instruction,
--- NPANANDI 12.08,2004: ADDED THE BELOW ATTRIBUTES
--- FOR CONTROL ENHANCEMENT
X_UOM_CODE => L_CONTROL_REC.UOM_CODE
,X_CONTROL_FREQUENCY => L_CONTROL_REC.CONTROL_FREQUENCY
--- NPANANDI 12.10.2004: ADDED BELOW FOR CTRL CLASSIFICATION
,X_CLASSIFICATION => L_CONTROL_REC.CLASSIFICATION
);
PROCEDURE update_control (
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_commit IN VARCHAR2 := fnd_api.g_false,
p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
x_return_status OUT nocopy VARCHAR2,
x_msg_count OUT nocopy NUMBER,
x_msg_data OUT nocopy VARCHAR2,
p_control_rec IN control_rec_type,
x_object_version_number OUT nocopy NUMBER
) IS
CURSOR c_get_control (control_rev_id NUMBER) IS
SELECT *
FROM amw_controls_b
WHERE control_rev_id = control_rev_id;
l_api_name CONSTANT VARCHAR2 (30) := 'Update_Control';
SAVEPOINT update_control_pvt;
AMW_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET',
p_token_name => 'INFO',
p_token_value => 'Control') ;
p_token_value => 'Last_Update_Date') ;
l_control_rec.last_update_date := SYSDATE;
l_control_rec.last_updated_by := g_user_id;
l_control_rec.last_update_login := g_login_id;
validate_control (p_mode => 'UPDATE',
p_api_version_number => 1,
p_init_msg_list => fnd_api.g_false,
p_validation_level => p_validation_level,
----p_control_rec => p_control_rec,
p_control_rec => l_control_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
amw_utility_pvt.debug_message ('Calling Update handler');
amw_controls_pkg.update_row
(x_control_rev_id => l_control_rec.control_rev_id,
x_object_version_number => l_control_rec.object_version_number,
x_orig_system_reference => l_control_rec.orig_system_reference,
x_latest_revision_flag => l_control_rec.latest_revision_flag,
x_requestor_id => l_control_rec.requestor_id,
x_control_id => l_control_rec.control_id,
x_approval_status => l_control_rec.approval_status,
x_automation_type => l_control_rec.automation_type,
x_application_id => l_control_rec.application_id,
x_job_id => l_control_rec.job_id,
x_created_by_module => l_control_rec.created_by_module,
x_attribute13 => l_control_rec.attribute13,
x_attribute14 => l_control_rec.attribute14,
x_attribute15 => l_control_rec.attribute15,
x_security_group_id => l_control_rec.security_group_id,
x_control_location => l_control_rec.control_location,
x_rev_num => l_control_rec.rev_num,
x_approval_date => l_control_rec.approval_date,
x_control_type => l_control_rec.control_type,
x_category => l_control_rec.CATEGORY,
x_source => l_control_rec.SOURCE,
x_attribute_category => l_control_rec.attribute_category,
x_attribute1 => l_control_rec.attribute1,
x_attribute2 => l_control_rec.attribute2,
x_attribute3 => l_control_rec.attribute3,
x_attribute4 => l_control_rec.attribute4,
x_attribute5 => l_control_rec.attribute5,
x_attribute6 => l_control_rec.attribute6,
x_attribute7 => l_control_rec.attribute7,
x_attribute8 => l_control_rec.attribute8,
x_attribute9 => l_control_rec.attribute9,
x_attribute10 => l_control_rec.attribute10,
x_attribute11 => l_control_rec.attribute11,
x_attribute12 => l_control_rec.attribute12,
x_end_date => l_control_rec.end_date,
x_curr_approved_flag => l_control_rec.curr_approved_flag,
x_name => l_control_rec.NAME,
x_description => l_control_rec.description,
x_physical_evidence => l_control_rec.physical_evidence,
x_last_update_date => SYSDATE,
x_last_updated_by => g_user_id,
x_last_update_login => g_login_id,
x_preventive_control => l_control_rec.preventive_control,
x_detective_control => l_control_rec.detective_control,
x_disclosure_control => l_control_rec.disclosure_control,
x_key_mitigating => l_control_rec.key_mitigating,
x_verification_source => l_control_rec.verification_source,
x_verification_source_name => l_control_rec.verification_source_name,
x_verification_instruction => l_control_rec.verification_instruction,
--- NPANANDI 12.08,2004: ADDED THE BELOW ATTRIBUTES
--- FOR CONTROL ENHANCEMENT
X_UOM_CODE => L_CONTROL_REC.UOM_CODE
,X_CONTROL_FREQUENCY => L_CONTROL_REC.CONTROL_FREQUENCY
--- NPANANDI 12.10.2004: ADDED BELOW FOR CTRL CLASSIFICATION
,X_CLASSIFICATION => L_CONTROL_REC.CLASSIFICATION
);
ROLLBACK TO update_control_pvt;
ROLLBACK TO update_control_pvt;
ROLLBACK TO update_control_pvt;
END update_control;
PROCEDURE delete_control (
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_commit IN VARCHAR2 := fnd_api.g_false,
p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
x_return_status OUT nocopy VARCHAR2,
x_msg_count OUT nocopy NUMBER,
x_msg_data OUT nocopy VARCHAR2,
p_control_rev_id IN NUMBER,
p_object_version_number IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2 (30) := 'Delete_Control';
SAVEPOINT delete_control_pvt;
amw_utility_pvt.debug_message ('Private API: Calling delete table handler');
amw_controls_pkg.delete_row (x_control_rev_id => p_control_rev_id);
ROLLBACK TO delete_control_pvt;
ROLLBACK TO delete_control_pvt;
ROLLBACK TO delete_control_pvt;
END delete_control;
SELECT control_rev_id
FROM amw_controls_b
WHERE control_rev_id = p_control_rev_id
AND object_version_number = p_object_version
FOR UPDATE NOWAIT;
IF p_control_rec.last_update_date = fnd_api.g_miss_date
OR p_control_rec.last_update_date IS NULL THEN
----amw_utility_pvt.error_message(p_message_name => 'AMW_ctrl_NO_last_update_date');
p_token_value => 'Last_Update_Date');
IF p_control_rec.last_updated_by = FND_API.g_miss_num OR p_control_rec.last_updated_by IS NULL THEN
AMW_Utility_PVT.Error_Message(p_message_name => 'AMW_control_NO_last_updated_by');
ELSE ----Update mode checks
IF p_control_rec.last_update_date IS NULL THEN
AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_REQUIRE_ITEM_ERROR',
p_token_name => 'ITEM',
p_token_value => 'Last_Update_Date');
SELECT *
FROM amw_controls_b
WHERE control_rev_id = p_control_rec.control_rev_id;
SELECT description,
verification_source_name,
verification_instruction
FROM amw_controls_tl
WHERE control_rev_id = p_control_rec.control_rev_id;
IF p_control_rec.last_update_date IS NULL THEN
x_complete_rec.last_update_date := l_control_rec.last_update_date;
IF p_control_rec.last_updated_by IS NULL THEN
x_complete_rec.last_updated_by := l_control_rec.last_updated_by;
IF p_control_rec.last_update_login IS NULL THEN
x_complete_rec.last_update_login := l_control_rec.last_update_login;