[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_sql := 'SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM ' || UPPER(p_table_name);
l_sql := 'SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM ' || p_lookup_table_name;
SELECT 1 FROM fnd_lookup_values lkup
WHERE lkup.LOOKUP_TYPE = p_lookup_type
AND lkup.LOOKUP_CODE = p_lookup_code
AND lkup.view_application_id = p_view_app_id
AND lkup.ENABLED_FLAG = 'Y'
AND lkup.language = USERENV('LANG')
AND lkup.security_group_id = to_number(decode(substrb(userenv('CLIENT_INFO'),55,1
), ' ', '0'
, NULL, '0'
, substrb(userenv('CLIENT_INFO'),55,10
)
)
);
l_sql := 'SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM ' || UPPER(p_table_name);
select HLevel
into Hier_Level
from AMW_HIERARCHY_LEVELS_V
where Process_Name = entity_name;
SELECT meaning
FROM amw_lookups
WHERE lookup_type = UPPER (p_lookup_type)
AND lookup_code = UPPER (p_lookup_code);
SELECT full_name
FROM amw_employees_current_v
WHERE party_id = p_party_id;
select meaning
into l_meaning
from amw_lookups
where lookup_type = p_lookup_type
and lookup_code = p_lookup_code;
select ffvt.description
into l_lob_desc
from
fnd_id_flex_structures fft,
fnd_segment_attribute_values fsav,
fnd_id_flex_segments seg,
fnd_flex_values ffv,
fnd_flex_values_tl ffvt
where fft.application_id = 450
and fft.id_flex_code = 'FII#'
and fft.id_flex_structure_code = 'DBI_DEFAULT_STRUCTURE'
and fsav.application_id = 450
and fsav.id_flex_code = 'FII#'
and fsav.id_flex_num = fft.id_flex_num
and fsav.segment_attribute_type = 'FII_LOB'
and fsav.attribute_value = 'Y'
and seg.application_id=450
and seg.id_flex_code='FII#'
and seg.id_flex_num = fft.id_flex_num
and seg.application_column_name=fsav.application_column_name
and seg.flex_value_set_id = ffv.flex_value_set_id
and ffv.summary_flag = 'N'
and ffv.flex_value_id = ffvt.flex_value_id
and ffvt.language=userenv('LANG')
and ffvt.flex_value_meaning = p_lob_name;
select watl.display_name into l_process_name
from wf_activities_tl watl, wf_activities wa, amw_process ap
where ap.process_id = p_process_id
and ap.name = wa.name
and wa.item_type = 'AUDITMGR'
and wa.end_date is null
and watl.name = wa.name
and watl.item_type = 'AUDITMGR'
and watl.version = wa.version
and watl.language = userenv('LANG');
select rt.name into l_risk_name
from amw_risks_all_vl rt
where
rt.risk_id = p_risk_id and
rt.LATEST_REVISION_FLAG = 'Y';
select ct.name into l_control_name
from amw_controls_all_vl ct
where
ct.control_id = p_control_id and
ct.LATEST_REVISION_FLAG = 'Y';
select ot.name into l_organization_name
from amw_audit_units_v ot
where
ot.organization_id = p_organization_id;
select audit_result
into l_last_audit_status
from amw_opinions_v
where pk1_value = p_process_id and pk3_value = p_org_id
and object_opinion_type_id =
(select object_opinion_type_id from AMW_OBJECT_OPINION_TYPES
where opinion_type_id = (select opinion_type_id from amw_opinion_types_b where opinion_type_code = p_mode)
and object_id = (select object_id from fnd_objects where obj_name = 'AMW_ORG_PROCESS') )
and last_update_date =
(select max(last_update_date) from amw_opinions_v
where pk1_value = p_process_id and pk3_value = p_org_id
and object_opinion_type_id =
(select object_opinion_type_id from AMW_OBJECT_OPINION_TYPES
where opinion_type_id = (select opinion_type_id from amw_opinion_types_b where opinion_type_code = p_mode)
and object_id = (select object_id from fnd_objects where obj_name = 'AMW_ORG_PROCESS') ) );
l_last_update_date date;
select max(last_update_date) into l_last_update_date from amw_opinions_v
where pk1_value = p_process_id and pk3_value = p_org_id
and object_opinion_type_id =
(select object_opinion_type_id from AMW_OBJECT_OPINION_TYPES
where opinion_type_id = (select opinion_type_id from amw_opinion_types_b where opinion_type_code = p_mode)
and object_id = (select object_id from fnd_objects where obj_name = 'AMW_ORG_PROCESS') );
return l_last_update_date;
select new_process_name into l_process_name
from amw_exceptions_tl aetl
where
aetl.exception_id = p_exception_id
and aetl.language = userenv('LANG');
select old_process_name into l_process_name
from amw_exceptions_tl aetl
where
aetl.exception_id = p_exception_id
and aetl.language = userenv('LANG');
select Process_Owner_Id
into process_owner_id
from amw_process
where process_id = p_pk;
select Process_Owner_Id
into process_owner_id
from amw_process_organization
where process_organization_id = p_pk;
select person_party_id
into f_party
from fnd_user
where user_id = p_userid;
SELECT PARAMETER_VALUE INTO l_value
FROM AMW_PARAMETERS
WHERE PARAMETER_NAME = p_param_name
AND PK1 = p_org_id;
select display_name into l_display_name
from amw_process amwp,
amw_process_names_tl amwp_tl
where amwp.process_rev_id = amwp_tl.process_rev_id and
amwp.process_id = p_process_id and
amwp.approval_date is not null and
amwp.approval_end_date is null and
amwp_tl.language=userenv('LANG');
select display_name into l_display_name
from amw_process amwp,
amw_process_names_tl amwp_tl
where amwp.process_rev_id = amwp_tl.process_rev_id and
amwp.process_id = p_process_id and
amwp.end_date is null and
amwp_tl.language=userenv('LANG');
select display_name into l_display_name
from amw_process amwp,
amw_process_names_tl amwp_tl
where amwp.process_rev_id = amwp_tl.process_rev_id and
amwp.process_id = p_process_id and
amwp.approval_date is not null and
amwp.approval_end_date is null and
amwp_tl.language=userenv('LANG');
select display_name into l_display_name
from amw_process_organization apo,
amw_process_names_tl amwp_tl
where apo.approval_date is not null
and apo.approval_end_date is null
and apo.rl_process_rev_id = amwp_tl.process_rev_id
and apo.process_id = p_process_id
and apo.organization_id = p_org_id
and amwp_tl.language=userenv('LANG');
select 1 INTO l_dummy
from amw_process_locks
where organization_id = p_org_id;
select 1
into l_dummy
from amw_process_locks
where locked_process_id = p_process_id
and organization_id = p_org_id;
SELECT COUNT(AP.AUDIT_PROJECT_ID) into l_dummy
FROM AMW_AUDIT_PROJECTS AP,
AMW_EXECUTION_SCOPE AES
WHERE
AP.AUDIT_PROJECT_ID = AES.ENTITY_ID
AND AES.ENTITY_TYPE = 'PROJECT'
AND AES.LEVEL_ID = 3
AND AP.AUDIT_PROJECT_STATUS = 'ACTI'
AND AES.ORGANIZATION_ID = p_org_id;
SELECT COUNT(APV.AUDIT_PROJECT_ID) into l_dummy
FROM AMW_AUDIT_PROJECTS_V APV,
AMW_ENTITY_HIERARCHIES AEH
WHERE
APV.AUDIT_PROJECT_ID = AEH.ENTITY_ID
AND AEH.ENTITY_TYPE = 'PROJECT'
AND AEH.OBJECT_TYPE = 'ORGANIZATION'
AND APV.PROJECT_STATUS_CODE = 'ACTI'
AND AEH.OBJECT_ID = p_org_id;
SELECT APV.name into l_dummy
FROM AMW_PROCESS_OBJECTIVES_VL APV,
AMW_OBJECTIVE_ASSOCIATIONS AOA
WHERE
APV.PROCESS_OBJECTIVE_ID =AOA.PROCESS_OBJECTIVE_ID
AND AOA.PK1 = p_org_id
AND AOA.PK2 = p_proc_id
AND AOA.PK3 = p_risk_id
AND AOA.PK4 = p_control_id
AND AOA.DELETION_DATE IS NULL
AND AOA.OBJECT_TYPE = 'CONTROL_ORG';
SELECT AOA.PROCESS_OBJECTIVE_ID into l_dummy
FROM AMW_OBJECTIVE_ASSOCIATIONS AOA
WHERE
AOA.PK1 = p_org_id
AND AOA.PK2 = p_proc_id
AND AOA.PK3 = p_risk_id
AND AOA.PK4 = p_control_id
AND AOA.DELETION_DATE IS NULL
AND AOA.OBJECT_TYPE = 'CONTROL_ORG';
SELECT AOA.approval_date into l_dummy
FROM AMW_OBJECTIVE_ASSOCIATIONS AOA
WHERE AOA.PK1 = p_org_id
AND AOA.PK2 = p_proc_id
AND AOA.PK3 = p_risk_id
AND AOA.PK4 = p_control_id
AND AOA.DELETION_DATE IS NULL
AND AOA.OBJECT_TYPE = 'CONTROL_ORG';
SELECT APV.name into l_dummy
FROM AMW_PROCESS_OBJECTIVES_VL APV,
AMW_OBJECTIVE_ASSOCIATIONS AOA
WHERE
APV.PROCESS_OBJECTIVE_ID =AOA.PROCESS_OBJECTIVE_ID
AND AOA.PK1 = p_org_id
AND AOA.PK2 = p_proc_id
AND AOA.PK3 = p_risk_id
AND AOA.PK4 = p_control_id
AND AOA.DELETION_DATE IS NULL
AND AOA.APPROVAL_DATE IS NOT NULL
AND AOA.OBJECT_TYPE = 'CONTROL_ORG';
SELECT 'Y' INTO l_dummy from dual
where exists ( select 1 from amw_latest_hierarchies
where organization_id = p_org_id
and (parent_id = p_proc_id or child_id = p_proc_id));
select max(ecs.change_id) INTO l_dummy
from eng_change_subjects ecs
,eng_engineering_changes eec
where ecs.entity_name='AMW_REVISION_ETTY'
and ecs.pk1_value=p_proc_id
and ecs.pk2_value=p_rev_num
and ecs.subject_level=1
and ecs.change_id=eec.change_id
and eec.status_type <> 5;
select max(ecs.change_id) INTO l_dummy
from eng_change_subjects ecs, eng_engineering_changes eec
where ecs.entity_name='AMW_ORG_REV_ETTY'
and ecs.pk1_value=p_org_id
and ecs.pk2_value=p_proc_id
and ecs.pk3_value=p_rev_num
and ecs.subject_level=1
and eec.change_id=ecs.change_id
and eec.status_type <> 5;
select 1 INTO l_dummy
from amw_latest_hierarchies
where parent_id = p_proc_id
and organization_id = p_org_id;
select
count(1) into dummy_count
from
amw_control_associations aca,
amw_process ap
where
aca.pk1 = ap.process_id
and aca.object_type = 'RISK'
and aca.pk1 = ap.process_id
and ap.process_id = p_process_id and ap.revision_number = p_revision_number and aca.pk2= p_risk_id
and ((ap.approval_date is null and ap.end_date is null and aca.deletion_date is null ) OR
(ap.approval_date is not null and aca.approval_date <= ap.approval_date and
(aca.deletion_approval_date is null or aca.deletion_approval_date >= ap.approval_end_date)) );
select
((select count(1)
from
amw_control_associations aca,
amw_process_vl apvl
where
aca.object_type = 'RISK'
and aca.pk1 = apvl.process_id
and aca.pk2 = p_risk_id
and ((apvl.approval_date is null and apvl.end_date is null and aca.deletion_date is null) OR
(apvl.approval_date is not null and apvl.approval_end_date is null and aca.approval_date is not null and aca.deletion_approval_date is null))
and apvl.process_id = p_process_id and apvl.revision_number = p_revision_number)
+
(select count(1)
from
amw_control_associations aca,
Amw_Proc_Hierarchy_Denorm aphd,
amw_process ap
where aphd.process_id = ap.process_id
and aphd.up_down_ind = 'D'
and ap.process_id = aphd.parent_child_id
and aca.object_type = 'RISK'
and aca.pk1 = aphd.parent_child_id
and aca.pk2 = p_risk_id
and ((ap.approval_date is null and ap.end_date is null and aphd.hierarchy_type = 'L' and aca.deletion_date is null) OR
(ap.approval_date is not null and ap.approval_end_date is null and aphd.hierarchy_type = 'A' and aca.approval_date is not null and aca.deletion_approval_date is null)
)
and ap.process_id = aphd.parent_child_id
and ap.process_id = p_process_id and ap.revision_number = p_revision_number))
into dummy_count from dual;
SELECT AOA.approval_date into l_dummy
FROM AMW_OBJECTIVE_ASSOCIATIONS AOA
WHERE AOA.PK1 = p_process_id
AND AOA.PK2 = p_risk_id
AND AOA.PK3 = p_control_id
AND AOA.DELETION_DATE IS NULL
AND AOA.OBJECT_TYPE = 'CONTROL';
SELECT EXCEPTION_ID
FROM AMW_EXCEPTIONS_B
WHERE OBJECT_TYPE = p_object
AND OLD_PK1 = p_pk1
AND OLD_PK2 = p_pk2
AND OLD_PK3 = p_pk3
AND NVL(OLD_PK4, -99) = NVL(p_pk4, -99)
AND NVL(OLD_PK5, -99) = NVL(p_pk5, -99)
AND APPROVED_FLAG = 'Y'
AND END_DATE IS NULL;
SELECT EXCEPTION_ID
FROM AMW_EXCEPTIONS_B
WHERE OBJECT_TYPE = p_object
AND NEW_PK1 = p_pk1
AND NEW_PK2 = p_pk2
AND NEW_PK3 = p_pk3
AND NVL(NEW_PK4, -99) = NVL(p_pk4, -99)
AND NVL(NEW_PK5, -99) = NVL(p_pk5, -99)
AND APPROVED_FLAG = 'Y'
AND END_DATE IS NULL;
SELECT MEANING
FROM AMW_EXCEPTIONS_REASONS,
AMW_LOOKUPS
WHERE LOOKUP_TYPE = 'AMW_EXCEPTION_REASONS'
AND LOOKUP_CODE = REASON_CODE
AND EXCEPTION_ID = p_exception_id;
SELECT EXCEPTION_ID
FROM AMW_EXCEPTIONS_B
WHERE OBJECT_TYPE = p_object
AND OLD_PK1 = p_pk1
AND OLD_PK2 = p_pk2
AND OLD_PK3 = p_pk3
AND NVL(OLD_PK4, -99) = NVL(p_pk4, -99)
AND NVL(OLD_PK5, -99) = NVL(p_pk5, -99)
AND APPROVED_FLAG = 'Y'
AND END_DATE IS NULL;
SELECT EXCEPTION_ID
FROM AMW_EXCEPTIONS_B
WHERE OBJECT_TYPE = p_object
AND NEW_PK1 = p_pk1
AND NEW_PK2 = p_pk2
AND NEW_PK3 = p_pk3
AND NVL(NEW_PK4, -99) = NVL(p_pk4, -99)
AND NVL(NEW_PK5, -99) = NVL(p_pk5, -99)
AND APPROVED_FLAG = 'Y'
AND END_DATE IS NULL;
SELECT JUSTIFICATION
FROM AMW_EXCEPTIONS_TL
WHERE EXCEPTION_ID = p_exception_id
AND LANGUAGE = USERENV('LANG');
/* This procedure inserts a record into the FND_LOG_MESSAGES table
FND uses an autonomous transaction so even when the hookinsert is
rolled back because of an error the log messages still exists
*/
PROCEDURE LOG_MSG( v_object_id IN VARCHAR2
, v_object_name IN VARCHAR2
, v_message IN VARCHAR2
-- , v_level_id IN NUMBER := -1
, v_module IN VARCHAR2)
IS
l_log_level NUMBER;
SELECT amw_debug_log_s.nextval INTO g_session_id FROM DUAL;
select 'Y' into l_dummy
from amw_control_associations
where object_type = 'RISK_ORG'
and pk1 = p_org_id
and pk2 = p_process_id
and pk3 = p_risk_id
and approval_date is not null and deletion_approval_date is null;
select 'Y' into l_dummy
from amw_control_associations
where object_type = 'RISK_ORG'
and pk1 = p_org_id
and pk2 = p_process_id
and pk3 = p_risk_id
and deletion_date is null;
select 'Y' into l_dummy
from amw_process_organization
where organization_id = p_org_id
and process_id = -2;
select data_source_code into l_data_source_code
from xdo_templates_b
where template_code = p_template_code
and application_short_name = 'AMW';
select audit_result
into l_last_audit_status
from amw_opinions_v
where pk1_value = p_org_id
and object_opinion_type_id =
(select object_opinion_type_id from AMW_OBJECT_OPINION_TYPES
where opinion_type_id = (select opinion_type_id from amw_opinion_types_b
where opinion_type_code = p_mode)
and object_id = (select object_id from fnd_objects where obj_name =
'AMW_ORGANIZATION') )
and last_update_date =
(select max(last_update_date) from amw_opinions_v
where pk1_value = p_org_id
and object_opinion_type_id =
(select object_opinion_type_id from AMW_OBJECT_OPINION_TYPES
where opinion_type_id = (select opinion_type_id from
amw_opinion_types_b where opinion_type_code = p_mode)
and object_id = (select object_id from fnd_objects where obj_name =
'AMW_ORGANIZATION') ) );
select pk1, parameter_value bulk collect into l_pk_values,l_param_values
from amw_parameters
where parameter_name = 'PROCESS_APPROVAL_OPTION';