[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(*)
into n
from amw_control_objectives
where control_rev_id = P_CONTROL_REV_ID
and objective_code = P_OBJECTIVE_CODE;
select count(*)
into n
from amw_control_objectives
where control_rev_id = P_CONTROL_REV_ID
and objective_code = P_OBJECTIVE_CODE;
select meaning
into yes
from fnd_lookups
where lookup_type='YES_NO'
and lookup_code='Y';
select meaning
into no
from fnd_lookups
where lookup_type='YES_NO'
and lookup_code='N';
select preventive_control
into n
from amw_controls_all_vl
where control_rev_id = P_CONTROL_REV_ID;
SELECT 'Y'
INTO N
FROM AMW_CONTROL_OBJECTIVES
WHERE CONTROL_REV_ID=P_CONTROL_REV_ID
AND OBJECTIVE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_OBJECTIVES'
AND TAG=P_TAG_NUM);
select count(*)
into n
from amw_control_assertions
where control_rev_id = P_CONTROL_REV_ID
and assertion_code = P_ASSERTION_CODE;
select count(*)
into n
from amw_control_assertions
where control_rev_id = P_CONTROL_REV_ID
and assertion_code = P_ASSERTION_CODE;
select meaning
into yes
from fnd_lookups
where lookup_type='YES_NO'
and lookup_code='Y';
select meaning
into no
from fnd_lookups
where lookup_type='YES_NO'
and lookup_code='N';
select count(*)
into n
from amw_assessment_components
where object_type = 'CONTROL'
and object_id = P_CONTROL_REV_ID
and component_code = P_component_CODE;
select count(*)
into n
from amw_assessment_components
where object_type = 'CONTROL'
and object_id = P_CONTROL_REV_ID
and component_code = P_component_CODE;
select meaning
into yes
from fnd_lookups
where lookup_type='YES_NO'
and lookup_code='Y';
select meaning
into no
from fnd_lookups
where lookup_type='YES_NO'
and lookup_code='N';
select count(*)
into n
from amw_objective_associations
where process_objective_id = P_process_objective_ID;
select meaning
into l_meaning
from amw_lookups
where lookup_type = p_lookup_type
and lookup_code = p_lookup_code
AND enabled_flag ='Y'
AND (end_date_active > SYSDATE or end_date_active is null);
select user_profile_option_name
into l_control_source_name
from fnd_profile_options_vl
--where to_char(profile_option_id) = p_control_source_id
where to_char(profile_option_id) = l_control_source_id
and application_id = p_application_id;
/**select user_form_name
into l_control_source_name
from fnd_form_vl
where to_char(form_id) = p_control_source_id
and application_id = p_application_id;
select user_function_name
into l_control_source_name
from fnd_form_functions_vl
--where to_char(function_id) = p_control_source_id
where to_char(function_id) = l_control_source_id
and application_id = p_application_id;
select user_function_name
into l_control_source_name
from fnd_form_functions_vl
--where to_char(function_id) = p_control_source_id
where to_char(function_id) = l_control_source_id
and application_id is null;
select distinct display_name
into l_control_source_name
from wf_activities_vl
--where name = p_control_source_id and type='PROCESS'
where name = l_control_source_id and type='PROCESS'
and end_date is null;
select fcpv.user_concurrent_program_name
into l_control_source_name
from fnd_concurrent_programs_vl fcpv
---where to_char(fcpv.concurrent_program_id) = p_control_source_id
where to_char(fcpv.concurrent_program_id) = l_control_source_id
and fcpv.application_id=p_application_id and fcpv.enabled_flag='Y';
/*** select to_char(fcpv.concurrent_program_id) control_source_id,
fcpv.user_concurrent_program_name control_source_name,
fcpv.application_id application_id,
(select application_name from fnd_application_vl where application_id=fcpv.application_id) applicationName,
'REPORT' lov_type,
'A' control_type,
'7' automation_type
from amw_controls_all_vl acav, fnd_concurrent_programs_vl fcpv
where acav.application_id=fcpv.application_id and enabled_flag='Y'
***/
elsif ((p_control_type = 'A' or p_control_type = 'C') AND (p_automation_type = '9')) then
select itl.NAME control_source_name
into l_control_source_name
from bis_application_measures am,
bis_indicators i ,bis_indicators_tl itl,
fnd_application_vl a
---where to_char(am.indicator_id) = p_control_source_id
where to_char(am.indicator_id) = l_control_source_id
and am.indicator_id = i.INDICATOR_ID
AND i.INDICATOR_ID = itl.INDICATOR_ID
AND itl.LANGUAGE = USERENV('LANG')
AND am.application_id = a.application_id
and a.application_id = p_application_id;
select distinct ac.CONSTRAINT_NAME
into l_control_source_name
from AMW_Constraints_vl ac
where to_char(ac.CONSTRAINT_ID) = l_control_source_id;
select 'Y'
into l_ita_installed
from fnd_product_installations
where application_id=438;
---EXECUTE IMMEDIATE 'SELECT PARAMETER_NAME from ITA_SETUP_PARAMETERS_VL WHERE PARAMETER_CODE=:B1' INTO l_control_source_name USING l_CONTROL_SOURCE_ID;
EXECUTE IMMEDIATE 'SELECT isgv.SETUP_GROUP_NAME||'': ''||PARAMETER_NAME '
||' from ITA_SETUP_PARAMETERS_VL ispv, ita_setup_groups_vl isgv WHERE ispv.AUDIT_ENABLED_FLAG=''Y'' and ispv.SETUP_GROUP_CODE=isgv.SETUP_GROUP_CODE and ispv.PARAMETER_CODE=:B1' INTO l_control_source_name USING l_CONTROL_SOURCE_ID;
p_select_flag IN VARCHAR2,
p_control_rev_id IN NUMBER,
p_objective_code IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_creation_date date;
l_last_update_date date;
l_last_updated_by number;
l_last_update_login number;
delete from amw_control_objectives
where control_rev_id = p_control_rev_id
and objective_code = p_objective_code;
if (p_select_flag = 'Y') then
l_creation_date := SYSDATE;
l_last_update_date := SYSDATE;
l_last_updated_by := FND_GLOBAL.USER_ID;
l_last_update_login := FND_GLOBAL.USER_ID;
select amw_control_objectives_s.nextval into l_control_objective_id from dual;
insert into amw_control_objectives (control_objective_id,
control_rev_id,
objective_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
values (l_control_objective_id,
p_control_rev_id,
p_objective_code,
l_creation_date,
l_created_by,
l_last_update_date,
l_last_updated_by,
l_last_update_login);
p_select_flag IN VARCHAR2,
p_control_rev_id IN NUMBER,
p_assertion_code IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_creation_date date;
l_last_update_date date;
l_last_updated_by number;
l_last_update_login number;
delete from amw_control_assertions
where control_rev_id = p_control_rev_id
and assertion_code = p_assertion_code;
if (p_select_flag = 'Y') then
l_creation_date := SYSDATE;
l_last_update_date := SYSDATE;
l_last_updated_by := FND_GLOBAL.USER_ID;
l_last_update_login := FND_GLOBAL.USER_ID;
select amw_control_assertions_s.nextval into l_control_assertion_id from dual;
insert into amw_control_assertions (control_assertion_id,
control_rev_id,
assertion_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
values (l_control_assertion_id,
p_control_rev_id,
p_assertion_code,
l_creation_date,
l_created_by,
l_last_update_date,
l_last_updated_by,
l_last_update_login);
p_select_flag IN VARCHAR2,
p_control_rev_id IN NUMBER,
p_component_code IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_creation_date date;
l_last_update_date date;
l_last_updated_by number;
l_last_update_login number;
delete from amw_assessment_components
where object_id = p_control_rev_id
and object_type = 'CONTROL'
and component_code = p_component_code;
if (p_select_flag = 'Y') then
l_creation_date := SYSDATE;
l_last_update_date := SYSDATE;
l_last_updated_by := FND_GLOBAL.USER_ID;
l_last_update_login := FND_GLOBAL.USER_ID;
select amw_assessment_components_s.nextval into l_assessment_component_id from dual;
insert into amw_assessment_components (assessment_component_id,
component_code,
object_type,
object_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
object_version_number)
values (l_assessment_component_id,
p_component_code,
'CONTROL',
p_control_rev_id,
l_creation_date,
l_created_by,
l_last_update_date,
l_last_updated_by,
l_last_update_login,
1);
PROCEDURE delete_control_association (p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_object_type IN VARCHAR2,
p_risk_association_id IN NUMBER,
p_orig_control_id in number,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
----String pObjectType,String pRiskId, String pProcessOrgId, String pControlId)
l_creation_date date;
l_last_update_date date;
l_last_updated_by number;
l_last_update_login number;
select control_association_id from amw_control_associations
where object_type = l_object_type
and pk1 = l_pk1
and control_id = l_control_id;
SAVEPOINT update_association_save_point;
delete from amw_control_associations
where control_association_id = l_control_association_id.control_association_id;
ROLLBACK TO update_association_save_point;
p_procedure_name => 'UPDATE_CONTROL_ASSOCIATION',
p_error_text => SUBSTRB(SQLERRM,1,240));
END delete_control_association;
PROCEDURE delete_obj_assert_comp (p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_control_rev_id in number,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
----String pObjectType,String pRiskId, String pProcessOrgId, String pControlId)
l_creation_date date;
l_last_update_date date;
l_last_updated_by number;
l_last_update_login number;
select control_association_id from amw_control_associations
where object_type = l_object_type
and pk1 = l_pk1
and control_id = l_control_id;
SAVEPOINT delete_save_point;
delete from amw_control_objectives where control_rev_id = p_control_rev_id;
delete from amw_control_assertions where control_rev_id = p_control_rev_id;
delete from amw_assessment_components
where object_id = p_control_rev_id
and object_type = 'CONTROL';
ROLLBACK TO delete_save_point;
p_procedure_name => 'UPDATE_CONTROL_ASSOCIATION',
p_error_text => SUBSTRB(SQLERRM,1,240));
END delete_obj_assert_comp;
SELECT DECODE(aov.audit_result_code,'EFFECTIVE','Y','N')
INTO CTRL_EFF
FROM AMW_OPINIONS_V aov
WHERE aov.object_name = 'AMW_ORG_CONTROL'
AND aov.opinion_type_code = 'EVALUATION'
AND aov.pk3_value = P_ORGANIZATION_ID
AND aov.pk1_value = P_CONTROL_ID
AND aov.authored_date = (select max(aov2.authored_date)
from AMW_OPINIONS aov2
where aov2.object_opinion_type_id = aov.object_opinion_type_id
and aov2.pk3_value = aov.pk3_value
and aov2.pk1_value = aov.pk1_value);
SELECT 'Y'
INTO L_IS_ITA_INSTALLED
FROM FND_PRODUCT_INSTALLATIONS
WHERE APPLICATION_ID=438;
EXECUTE IMMEDIATE 'SELECT POLICY_NAME FROM ITA_POLICY_VL WHERE CONTROL_ID=:B1' INTO L_POLICY_NAME USING P_CONTROL_ID;
SELECT CTRL2.Control_REV_ID
FROM amw_controls_b CTRL1 , amw_controls_b CTRL2
WHERE CTRL1.Control_id = CTRL2.control_id
AND CTRL1.control_rev_id = p_CTRL_rev_id
AND CTRL2.curr_approved_flag= 'Y'
and CTRL2.latest_revision_flag = 'N';
update amw_controls_b
set approval_status='A'
--,object_version_number=object_version_number+1
,curr_approved_flag='Y'
,latest_revision_flag ='Y'
,approval_date=SYSDATE
,LAST_UPDATE_DATE=SYSDATE
,LAST_UPDATED_BY=G_USER_ID
,LAST_UPDATE_LOGIN=G_LOGIN_ID
where control_rev_id=P_CONTROL_REV_ID;
UPDATE AMW_CONTROLS_B
SET END_DATE=SYSDATE
,CURR_APPROVED_FLAG='N'
,LATEST_REVISION_FLAG='N'
--,OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1
,LAST_UPDATE_DATE=SYSDATE
,LAST_UPDATED_BY=G_USER_ID
,LAST_UPDATE_LOGIN=G_LOGIN_ID
WHERE CONTROL_REV_ID=l_old_appr_CTRL_rev_id;
SELECT UNIT_OF_MEASURE_TL
INTO LX_UNIT_OF_MEASURE_TL
FROM MTL_UNITS_OF_MEASURE_VL
WHERE UOM_CLASS=L_UOM_CLASS
AND UOM_CODE=P_UOM_CODE;
SELECT meaning
INTO LX_UNIT_OF_MEASURE_TL
FROM AMW_LOOKUPS
WHERE lookup_type='AMW_CONTROL_FREQUENCY'
AND lookup_code=P_UOM_CODE;
select count(*)
into n
from amw_control_purposes
where control_rev_id = P_CONTROL_REV_ID
and PURPOSE_code = P_PURPOSE_CODE;
select count(*)
into n
from amw_control_PURPOSES
where control_rev_id = P_CONTROL_REV_ID
and PURPOSE_code = P_PURPOSE_CODE;
select meaning
into yes
from fnd_lookups
where lookup_type='YES_NO'
and lookup_code='Y';
select meaning
into no
from fnd_lookups
where lookup_type='YES_NO'
and lookup_code='N';
p_select_flag IN VARCHAR2,
p_control_rev_id IN NUMBER,
p_PURPOSE_code IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_creation_date date;
l_last_update_date date;
l_last_updated_by number;
l_last_update_login number;
delete from amw_control_PURPOSES
where control_rev_id = p_control_rev_id
and PURPOSE_code = p_PURPOSE_code;
if (p_select_flag = 'Y') then
insert into amw_control_PURPOSES(
control_PURPOSE_id
,control_rev_id
,PURPOSE_codE
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login) values (
AMW_CONTROL_PURPOSES_S.NEXTVAL
,p_control_rev_id
,p_PURPOSE_code
,SYSDATE
,FND_GLOBAL.USER_ID
,SYSDATE
,FND_GLOBAL.USER_ID
,FND_GLOBAL.LOGIN_ID);
select vl.name
into l_obj
from amw_process ap,
amw_objective_associations ao,
amw_process_objectives_vl vl
where ao.object_type = 'CONTROL'
and ao.pk1 = p_process_id
and ao.pk2 = p_risk_id
and ao.pk3 = p_control_id
and ap.process_id = p_process_id
and ap.revision_number = p_rev
and ((ap.approval_date is null and ap.end_date is null and ao.deletion_date is null) OR
(ap.approval_date is not null and ao.approval_date <= ap.approval_date and
(ao.deletion_approval_date is null or ao.deletion_approval_date >= ap.approval_end_date)))
and vl.process_objective_id = ao.process_objective_id;