[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* INSERTS OR UPDATES ARE DONE AGAINIST THE FOLLOWING TABLES */
/* Insert into AMW_RISKS_B and AMW_RISKS_TL */
/* Insert into AMW_CONTROLS_B and AMW_CONTROLS_TL */
/* Insert into AMW_CONTROL_ASSOCIATIONS */
/* Insert into AMW_RISK_ASSOCIATIONS */
/* Insert into AMW_CONTROL_OBJECTIVES */
/* Insert into AMW_CONTROL_ASSERTIONS */
/* Updates amw_risk-ctrl_interface, with error messages */
/* Deleting successful production inserts, based on profile */
/* */
/*****************************************************************************/
--
-- Used for exception processing
--
G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
SELECT 'Y'
FROM fnd_responsibility r, fnd_compiled_menu_functions m, fnd_form_functions f
WHERE r.responsibility_id = fnd_global.resp_id
AND r.application_id=fnd_global.resp_appl_id
AND r.menu_id = m.menu_id
AND m.function_id = f.function_id
AND f.function_name = v_import_func;
SELECT 'Y'
FROM fnd_resp_functions rf, fnd_form_functions f
WHERE rf.application_id = fnd_global.resp_appl_id
AND rf.responsibility_id = fnd_global.resp_id
AND rf.rule_type = 'F'
AND rf.action_id = f.function_id
AND f.function_name = v_import_func;
update_interface_with_error (v_risk_pending_msg
,'AMW_RISKS'
,v_interface_id);
update_interface_with_error (v_control_pending_msg
,'AMW_CONTROLS'
,v_interface_id);
SELECT batch_id
,rcm_org_interface_id
,control_approval_status_code
,control_automation_type_code
,control_description
,control_job_id
,control_location_code
,control_name
,control_source
,control_source_type
,control_type_code
,created_by
,creation_date
,error_flag
,interface_status
,last_update_date
,last_updated_by
,physical_evidence
,process_flag
,process_name
,requestor_id
,risk_approval_date
,risk_approval_status_code
,risk_description
,risk_impact_code
,risk_name
,risk_likelihood_code
,process_id
,process_code
,revise_risk_flag
,revise_ctrl_flag
,control_application_id
,upper(material) as material
,decode(nvl(upper(material),'N'),'N',null,material_value) as material_value
,ap_name
,preventive_control
,detective_control
,disclosure_control
,key_mitigating
,verification_source_name
,verification_instruction
,verification_source
,upper(nvl(design_effectiveness,'N')) as design_effectiveness
,upper(nvl(op_effectiveness,'N')) as op_effectiveness
,risk_type1
,risk_type2
,risk_type3
,risk_type4
,risk_type5
,risk_type6
,risk_type7
,risk_type8
,risk_type9
,risk_type10
,risk_type11
,risk_type12
,risk_type13
,risk_type14
,risk_type15
,risk_type16
,risk_type17
,risk_type18
,risk_type19
,risk_type20
,risk_type21
,risk_type22
,risk_type23
,risk_type24
,risk_type25
,risk_type26
,risk_type27
,risk_type28
,risk_type29
,risk_type30
,control_comp1
,control_comp2
,control_comp3
,control_comp4
,control_comp5
,control_comp6
,control_comp7
,control_comp8
,control_comp9
,control_comp10
,control_comp11
,control_comp12
,control_comp13
,control_comp14
,control_comp15
,control_comp16
,control_comp17
,control_comp18
,control_comp19
,control_comp20
,control_comp21
,control_comp22
,control_comp23
,control_comp24
,control_comp25
,control_comp26
,control_comp27
,control_comp28
,control_comp29
,control_comp30
,control_obj1
,control_obj2
,control_obj3
,control_obj4
,control_obj5
,control_obj6
,control_obj7
,control_obj8
,control_obj9
,control_obj10
,control_obj11
,control_obj12
,control_obj13
,control_obj14
,control_obj15
,control_obj16
,control_obj17
,control_obj18
,control_obj19
,control_obj20
,control_obj21
,control_obj22
,control_obj23
,control_obj24
,control_obj25
,control_obj26
,control_obj27
,control_obj28
,control_obj29
,control_obj30
,control_assert1
,control_assert2
,control_assert3
,control_assert4
,control_assert5
,control_assert6
,control_assert7
,control_assert8
,control_assert9
,control_assert10
,control_assert11
,control_assert12
,control_assert13
,control_assert14
,control_assert15
,control_assert16
,control_assert17
,control_assert18
,control_assert19
,control_assert20
,control_assert21
,control_assert22
,control_assert23
,control_assert24
,control_assert25
,control_assert26
,control_assert27
,control_assert28
,control_assert29
,control_assert30
,organization_id
,process_objective_name
,process_obj_description
--npanandi 12.12.2004: added below for classification
,risk_classification
,ctrl_classification
,uom_code
FROM amw_rcm_org_interface
WHERE created_by = DECODE (p_user_id, NULL, created_by, p_user_id)
AND batch_id = DECODE (p_batch_id, NULL, batch_id, p_batch_id)
AND process_flag IS NULL
AND error_flag IS NULL;
SELECT ORGANIZATION_ID
,PROCESS_ID
,PROCESS_NAME
,PROCESS_CODE
,rcm_org_interface_id
FROM AMW_RCM_ORG_INTERFACE
WHERE CREATED_BY=DECODE(P_USER_ID,NULL,CREATED_BY,P_USER_ID)
AND BATCH_ID=DECODE(p_batch_id, NULL, batch_id, p_batch_id)
AND process_flag IS NULL
AND error_flag IS NULL
ORDER BY BATCH_ID DESc;
SELECT party_id
FROM amw_employees_current_v
WHERE employee_id = (select employee_id
from fnd_user
where user_id = p_user_id)
AND rownum = 1;
SELECT b.risk_id, b.approval_status
FROM amw_risks_b b, amw_risks_tl tl
WHERE tl.name = c_risk_name
AND tl.language = USERENV('LANG')
AND tl.risk_rev_id = b.risk_rev_id
AND b.latest_revision_flag='Y';
SELECT b.control_id, b.approval_status
FROM amw_controls_b b, amw_controls_tl tl
WHERE tl.name = c_control_name
AND tl.language = USERENV('LANG')
AND tl.control_rev_id = b.control_rev_id
AND b.latest_revision_flag='Y';
SELECT PROCESS_CODE,PROCESS_ID INTO L_PROCESS_CODE,L_PROCESS_ID
FROM AMW_LATEST_REV_ORG_V
WHERE ORGANIZATION_ID=VALID_REC.ORGANIZATION_ID
AND DISPLAY_NAME=VALID_REC.PROCESS_NAME;
SELECT PROCESS_CODE,PROCESS_ID INTO L_PROCESS_CODE,L_PROCESS_ID
FROM AMW_LATEST_REV_ORG_V
WHERE ORGANIZATION_ID=VALID_REC.ORGANIZATION_ID
AND DISPLAY_NAME=VALID_REC.PROCESS_NAME
AND PROCESS_CODE=VALID_REC.PROCESS_CODE;
V_ERR_MSG := 'Multiple processes exist with same Process Name, please select Unique Process Code';
update_interface_with_error (v_err_msg
,'AMW_RISKS'
,VALID_REC.rcm_org_interface_id);
V_ERR_MSG := 'Please select valid combination of Process Name and Process Code';
update_interface_with_error (v_err_msg
,'AMW_RISKS'
,VALID_REC.rcm_org_interface_id);
V_ERR_MSG := 'Please select valid combination of Process Name and Process Code';
update_interface_with_error (v_err_msg
,'AMW_RISKS'
,VALID_REC.rcm_org_interface_id);
---Check for Update privilege here
l_new_risk := false;
fnd_file.put_line (fnd_file.LOG, '************** Checking Update Privilege for rc_rec.risk_name: '||rc_rec.risk_name);
p_function => 'AMW_RISK_UPDATE_PRVLG'
,p_object_name => 'AMW_RISK'
,p_instance_pk1_value => lx_risk_id
,p_user_id => fnd_global.user_id);
fnd_file.put_line (fnd_file.LOG, '************** Checked Update Privilege for rc_rec.risk_name: '||rc_rec.risk_name);
v_err_msg := 'Cannot update this Risk';
update_interface_with_error (v_err_msg
,'AMW_RISKS'
,v_interface_id);
select process_id into l_process_id
from amw_latest_rev_org_v
where display_name=rc_rec.process_name
and process_code=rc_rec.process_code
and organization_id=rc_rec.organization_id;
V_ERR_MSG := 'Multiple processes exist with same Process Name, please select Unique Process Code';
update_interface_with_error (v_err_msg
,'AMW_RISKS'
,rc_REC.rcm_org_interface_id);
V_ERR_MSG := 'Please select valid combination of Process Name and Process Code';
update_interface_with_error (v_err_msg
,'AMW_RISKS'
,rc_REC.rcm_org_interface_id);
V_ERR_MSG := 'Please select valid combination of Process Name and Process Code';
update_interface_with_error (v_err_msg
,'AMW_RISKS'
,rc_REC.rcm_org_interface_id);
V_ERR_MSG := 'Please select valid combination of Process Name and Process Code';
update_interface_with_error (v_err_msg
,'AMW_RISKS'
,rc_REC.rcm_org_interface_id);
update_interface_with_error (v_err_msg
,'AMW_RISKS'
,v_interface_id);
fnd_file.put_line (fnd_file.LOG, '************** Checking Update Privilege for rc_rec.control_name: '||rc_rec.control_name);
p_function => 'AMW_CTRL_UPDATE_PRVLG'
,p_object_name => 'AMW_CONTROL'
,p_instance_pk1_value => lx_control_id
,p_user_id => fnd_global.user_id);
fnd_file.put_line (fnd_file.LOG, '************** Checked Update Privilege for rc_rec.control_name: '||rc_rec.control_name);
v_err_msg := 'Cannot update this Ctrl';
update_interface_with_error (v_err_msg
,'AMW_CONTROLS'
,v_interface_id);
---03.03.2005 npanandi: added check for lHasAccess to update this Ctrl
---only if this user has Upd privilege
l_has_ctrl_access = 'T' and
(lx_control_id is null OR
l_revise_control_flag = 'Y') THEN
SELECT DECODE (rc_rec.control_name,
NULL,
l_amw_control_name_prefix||amw_controls_tl_s1.NEXTVAL,
rc_rec.control_name)
INTO l_control_name
FROM dual;
update_interface_with_error (v_err_msg
,'AMW_CONTROLS'
,v_interface_id);
update_interface_with_error (v_err_msg
,'AMW_PROCESS_ORGANIZATION'
,v_interface_id);
SELECT CONTROL_ASSOCIATION_ID,APPROVAL_DATE
INTO L_CONTROL_ASSOCIATION_ID,L_APPROVAL_DATE
FROM AMW_CONTROL_ASSOCIATIONS
WHERE CONTROL_ID=LX_CONTROL_ID
AND PK1=RC_REC.ORGANIZATION_ID
AND PK2=L_PROCESS_ID
AND PK3=LX_RISK_ID
AND OBJECT_TYPE='RISK_ORG'
AND DELETION_DATE IS NULL;
--IS LATEST_REVISION, SO SIMPLY UPDATE ASSOC ATTRIBUTES
--but of course, for Ctrl - Risk Associations, there ARE NO
--association attributes ....
IF(L_APPROVAL_DATE IS NOT NULL) THEN
--THIS MEANS THAT THIS IS LATEST REVISION FOR THIS ASSOCIATION
--AND IS APPROVED, SO
BEGIN
UPDATE AMW_CONTROL_ASSOCIATIONS
SET DELETION_DATE=SYSDATE
,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_ASSOCIATION_ID=L_CONTROL_ASSOCIATION_ID;
UPDATE_INTERFACE_WITH_ERROR(V_ERR_MSG,V_TABLE_NAME,v_interface_id);
select control_association_id,approval_date
into l_control_association_id,l_approval_date
from amw_control_associations
where control_id=lx_control_id
and pk1=rc_rec.organization_id
and pk2=lx_risk_id
and object_type='ENTITY_CONTROL'
and deletion_date is null;
--is latest_revision, so simply update assoc attributes
--but of course, for ctrl - risk associations, there are no
--association attributes ....
if(l_approval_date is not null) then
--this means that this is latest revision for this association
--and is approved, so
begin
update amw_control_associations
set deletion_date=sysdate
,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_association_id=l_control_association_id;
update_interface_with_error(v_err_msg,v_table_name,v_interface_id);
update_interface_with_error (v_err_msg
,v_table_name
,v_interface_id);
--NPANANDI 11/12/2004: CHANGED THE ABOVE SELECT CHECK
--FOR EXISTING AP 2 CTRL ASSOCIATION
fnd_file.put_line (fnd_file.LOG,'l_object_type_count --> '||l_object_type_count);
update_interface_with_error (v_err_msg
,'AMW_AP_ASSOCIATIONS'
,v_interface_id);
update_interface_with_error (v_err_msg
,'AMW_PROCESS_OBJECTIVES'
,v_interface_id);
--npanandi 11/12/2004: changed the way insert logic is handled
--post AMW.D+
BEGIN
SELECT OBJECTIVE_ASSOCIATION_ID,APPROVAL_DATE
INTO L_PROC_OBJ_ASSOCIATION_ID,L_PROC_OBJ_APPROVAL_DATE
FROM AMW_OBJECTIVE_ASSOCIATIONS
WHERE PROCESS_OBJECTIVE_ID=l_process_objective_id
AND PK1=l_organization_id
---01.13.2005 npanandi: added the below
and pk2=l_process_id
AND OBJECT_TYPE='PROCESS_ORG'
AND DELETION_DATE IS NULL;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Need to change the way data is inserted in amw_objective_associations');
--IS LATEST_REVISION, SO SIMPLY UPDATE ASSOC ATTRIBUTES
IF(L_PROC_OBJ_APPROVAL_DATE IS NOT NULL) THEN
--THIS MEANS THAT THIS IS LATEST REVISION FOR THIS ASSOCIATION
--AND IS APPROVED, SO
BEGIN
UPDATE AMW_OBJECTIVE_ASSOCIATIONS
SET DELETION_DATE=SYSDATE
,OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1
,LAST_UPDATE_DATE=SYSDATE
,LAST_UPDATED_BY=G_USER_ID
,LAST_UPDATE_LOGIN=G_LOGIN_ID
WHERE OBJECTIVE_ASSOCIATION_ID=L_PROC_OBJ_ASSOCIATION_ID;
UPDATE_INTERFACE_WITH_ERROR(V_ERR_MSG,V_TABLE_NAME,v_interface_id);
SELECT OBJECTIVE_ASSOCIATION_ID,APPROVAL_DATE
INTO L_RISK_OBJ_ASSOCIATION_ID,L_RISK_OBJ_APPROVAL_DATE
FROM AMW_OBJECTIVE_ASSOCIATIONS
WHERE PROCESS_OBJECTIVE_ID=l_process_objective_id
AND PK1=lx_risk_id
AND OBJECT_TYPE='RISK'
AND DELETION_DATE IS NULL;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Need to change the way data is inserted in amw_risk_associations');
fnd_file.put_line (fnd_file.LOG, 'inserting lx_risk_id: '||lx_risk_id||' l_process_objective_id '||l_process_objective_id);
--IS LATEST_REVISION, SO SIMPLY UPDATE ASSOC ATTRIBUTES
IF(L_RISK_OBJ_APPROVAL_DATE IS NOT NULL) THEN
--THIS MEANS THAT THIS IS LATEST REVISION FOR THIS ASSOCIATION
--AND IS APPROVED, SO
BEGIN
UPDATE AMW_OBJECTIVE_ASSOCIATIONS
SET DELETION_DATE=SYSDATE
,OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1
,LAST_UPDATE_DATE=SYSDATE
,LAST_UPDATED_BY=G_USER_ID
,LAST_UPDATE_LOGIN=G_LOGIN_ID
WHERE OBJECTIVE_ASSOCIATION_ID=L_RISK_OBJ_ASSOCIATION_ID;
UPDATE_INTERFACE_WITH_ERROR(V_ERR_MSG,V_TABLE_NAME,v_interface_id);
SELECT OBJECTIVE_ASSOCIATION_ID,APPROVAL_DATE,process_objective_id
INTO L_ctrl_OBJ_ASSOCIATION_ID,L_ctrl_OBJ_APPROVAL_DATE,l_ctrl_objective_id
FROM AMW_OBJECTIVE_ASSOCIATIONS
WHERE
-- PROCESS_OBJECTIVE_ID=l_process_objective_id AND
PK1=l_organization_id
--01.13.2005 npanandi: added below 2 columns in where clause
--per changed datamodel
and pk2=l_process_id
and pk3=lx_risk_id
and pk4=lx_control_id
AND OBJECT_TYPE='CONTROL_ORG'
AND DELETION_DATE IS NULL;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Need to change the way data is inserted in amw_risk_associations');
fnd_file.put_line (fnd_file.LOG, 'inserting lx_control_id: '||lx_control_id||' l_process_objective_id '||l_process_objective_id);
--IS LATEST_REVISION, SO SIMPLY UPDATE ASSOC ATTRIBUTES
IF(L_ctrl_OBJ_APPROVAL_DATE IS NOT NULL) THEN
--THIS MEANS THAT THIS IS LATEST REVISION FOR THIS ASSOCIATION
--AND IS APPROVED, SO
if (l_ctrl_objective_id <> l_process_objective_id) then
BEGIN
UPDATE AMW_OBJECTIVE_ASSOCIATIONS
SET DELETION_DATE=SYSDATE
,OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1
,LAST_UPDATE_DATE=SYSDATE
,LAST_UPDATED_BY=G_USER_ID
,LAST_UPDATE_LOGIN=G_LOGIN_ID
WHERE OBJECTIVE_ASSOCIATION_ID=L_CTRL_OBJ_ASSOCIATION_ID;
UPDATE_INTERFACE_WITH_ERROR(V_ERR_MSG,V_TABLE_NAME,v_interface_id);
delete from AMW_OBJECTIVE_ASSOCIATIONS
WHERE OBJECTIVE_ASSOCIATION_ID=L_CTRL_OBJ_ASSOCIATION_ID;
UPDATE_INTERFACE_WITH_ERROR(V_ERR_MSG,V_TABLE_NAME,v_interface_id);
---Org update pages checks only for RiskAssoc privilege, while making
---Process - Risk Association, other associations are done on the basis
---of access privilege for AmwOrgUpdate privilege
l_has_proc_risk_access := 'T'; --setting this to 'T' to avoid conflict with value from previous loop
update_interface_with_error (v_err_msg
,'AMW_RISK_ASSOCIATIONS'
,v_interface_id);
SELECT RISK_ASSOCIATION_ID,APPROVAL_DATE
INTO L_RISK_ASSOCIATION_ID,L_RISK_APPROVAL_DATE
FROM AMW_RISK_ASSOCIATIONS
WHERE RISK_ID=lx_risk_id
AND PK1=L_ORGANIZATION_ID
AND PK2=L_PROCESS_ID
AND OBJECT_TYPE='PROCESS_ORG'
AND DELETION_DATE IS NULL;
--IS LATEST_REVISION, SO SIMPLY UPDATE ASSOC ATTRIBUTES
IF(L_RISK_APPROVAL_DATE IS NOT NULL) THEN
--THIS MEANS THAT THIS IS LATEST REVISION FOR THIS ASSOCIATION
--AND IS APPROVED, SO
FND_FILE.PUT_LINE(FND_FILE.LOG,'UPDATE THIS ASSOCIATION, THEN INSERT');
UPDATE AMW_RISK_ASSOCIATIONS
SET DELETION_DATE=SYSDATE
,OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1
,LAST_UPDATE_DATE=SYSDATE
,LAST_UPDATED_BY=G_USER_ID
,LAST_UPDATE_LOGIN=G_LOGIN_ID
WHERE RISK_ASSOCIATION_ID=L_RISK_ASSOCIATION_ID;
UPDATE_INTERFACE_WITH_ERROR(V_ERR_MSG,V_TABLE_NAME,v_interface_id);
--AND IS NOT APPROVED, SO SIMPLY UPDATE THE ATTRIBUTES
UPDATE AMW_RISK_ASSOCIATIONS
SET RISK_LIKELIHOOD_CODE=rc_rec.risk_likelihood_code
,RISK_IMPACT_CODE=rc_rec.risk_IMPACT_code
,MATERIAL=RC_REC.MATERIAL
,MATERIAL_VALUE=RC_REC.MATERIAL_VALUE
,OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1
,LAST_UPDATE_DATE=SYSDATE
,LAST_UPDATED_BY=G_USER_ID
,LAST_UPDATE_LOGIN=G_LOGIN_ID
WHERE RISK_ASSOCIATION_ID=L_RISK_ASSOCIATION_ID;
select risk_association_id,approval_date
into l_risk_association_id,l_risk_approval_date
from amw_risk_associations
where risk_id=lx_risk_id
and pk1=l_organization_id
and object_type='ENTITY_RISK'
and deletion_date is null;
--is latest_revision, so simply update assoc attributes
if(l_risk_approval_date is not null) then
--this means that this is latest revision for this association
--and is approved, so
fnd_file.put_line(fnd_file.log,'update this association, then insert');
update amw_risk_associations
set deletion_date=sysdate
,object_version_number=object_version_number+1
,last_update_date=sysdate
,last_updated_by=g_user_id
,last_update_login=g_login_id
where risk_association_id=l_risk_association_id;
update_interface_with_error(v_err_msg,v_table_name,v_interface_id);
--and is not approved, so simply update the attributes
update amw_risk_associations
set risk_likelihood_code=rc_rec.risk_likelihood_code
,risk_impact_code=rc_rec.risk_impact_code
,material=rc_rec.material
,material_value=rc_rec.material_value
,object_version_number=object_version_number+1
,last_update_date=sysdate
,last_updated_by=g_user_id
,last_update_login=g_login_id
where risk_association_id=l_risk_association_id;
update_interface_with_error (v_err_msg
,v_table_name
,v_interface_id);
UPDATE amw_rcm_org_interface
SET process_flag = l_process_flag
,last_update_date = SYSDATE
,last_updated_by = v_user_id
WHERE batch_id = p_batch_id;
fnd_file.put_line (fnd_file.LOG,'err in update process flag: '||SUBSTR (SQLERRM, 1, 200));
DELETE FROM amw_rcm_org_interface WHERE batch_id = p_batch_id;
fnd_file.put_line (fnd_file.LOG,'err in delete interface records: '||SUBSTR (SQLERRM, 1, 200));
UPDATE amw_rcm_org_interface
SET error_flag = 'Y'
,interface_status = v_invalid_risk_type
WHERE batch_id = p_batch_id;
UPDATE amw_rcm_org_interface
SET error_flag = 'Y'
,interface_status = v_invalid_requestor_msg
WHERE batch_id = p_batch_id;
UPDATE amw_rcm_org_interface
SET error_flag = 'Y'
,interface_status = v_no_import_privilege_msg
WHERE batch_id = p_batch_id;
SELECT name,process_objective_id
FROM AMW_process_objectives_vl
WHERE name = p_process_objective_name;
SELECT AMW_process_objectives_S.NEXTVAL
FROM dual;
select count(*) into l_risk_type_exists
from amw_risk_type
where risk_rev_id = lx_risk_rev_id
and risk_type_code = l_risk_type_code;
amw_process_objectives_b_pkg.insert_row(
X_ROWID => l_rowid,
x_last_updated_by => g_user_id,
x_last_update_date => sysdate,
x_created_by => g_user_id,
x_creation_date => sysdate,
x_last_update_login => g_login_id,
x_objective_type => 'C',
x_start_date => sysdate,
x_end_date => null,
x_attribute_category => null,
x_attribute1 => null,
x_attribute2 => null,
x_attribute3 => null,
x_attribute4 => null,
x_attribute5 => null,
x_attribute6 => null,
x_attribute7 => null,
x_attribute8 => null,
x_attribute9 => null,
x_attribute10 => null,
x_attribute11 => null,
x_attribute12 => null,
x_attribute13 => null,
x_attribute14 => null,
x_attribute15 => null,
x_security_group_id => null,
x_object_version_number => 1,
x_process_objective_id => l_process_objective_id,
x_requestor_id => p_requestor_id,
X_NAME => p_process_objective_name,
X_DESCRIPTION => p_process_obj_description
);
update AMW_PROCESS_OBJECTIVEs_TL set
NAME = p_process_objective_name,
DESCRIPTION = p_process_obj_description,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = g_user_id,
LAST_UPDATE_LOGIN = g_login_id,
SOURCE_LANG = userenv('LANG')
where PROCESS_OBJECTIVE_ID = l_get_objective.process_objective_id
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
update_interface_with_error (v_err_msg
,v_table_name
,v_interface_id
);
update_interface_with_error (v_err_msg
,v_table_name
,v_interface_id
);
SELECT risk_type_code
FROM AMW_SETUP_RISK_TYPES_VL
WHERE
start_date <= sysdate AND (end_date IS NULL OR end_date >= sysdate)
AND tag=p_lookup_tag;
SELECT AMW_RISK_TYPE_S.NEXTVAL
FROM dual;
select count(*) into l_risk_type_exists
from amw_risk_type
where risk_rev_id = lx_risk_rev_id
and risk_type_code = l_risk_type_code;
delete from amw_risk_type
where risk_rev_id=lx_risk_rev_id
and risk_type_code=l_risk_type_code;
---insert into test_test (text,creation_date) values ('risk_type_flag: '||p_risk_type_flag||' lookup_tag: '||p_lookup_tag,sysdate);
--this risk_type has been selected for this risk ....
--need to check if this row already exists in amw_risk_type
--for this risk_rev_id
select count(*) into l_risk_type_exists
from amw_risk_type
where risk_rev_id = lx_risk_rev_id
and risk_type_code = l_risk_type_code;
--for the current risk ... hence insert a new row
OPEN c_risk_type_id;
INSERT INTO amw_risk_type
(risk_type_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
risk_rev_id,
risk_type_code,
OBJECT_VERSION_NUMBER) VALUES
(l_risk_type_id,
SYSDATE,
g_user_id,
SYSDATE,
g_user_id,
g_login_id,
lx_risk_rev_id,
l_risk_type_code,
1);
update_interface_with_error (v_err_msg
,v_table_name
,v_interface_id
);
update_interface_with_error (v_err_msg
,v_table_name
,v_interface_id
);
update_interface_with_error (v_err_msg
,v_table_name
,v_interface_id
);
update_interface_with_error (v_err_msg
,v_table_name
,v_interface_id
);
SELECT lookup_code
FROM AMW_LOOKUPS
WHERE lookup_type='AMW_CONTROL_OBJECTIVES'
AND enabled_flag='Y'
AND tag=p_lookup_tag;
select count(*)
into l_obj_exists
from amw_control_objectives
where control_rev_id = vx_control_rev_id
AND objective_code = l_ctrl_obj_code;
delete from amw_control_objectives
where control_rev_id = vx_control_rev_id
AND objective_code = l_ctrl_obj_code;
--- this control objective has been selected
--- need to check if it is already present or not
select count(*)
into l_obj_exists
from amw_control_objectives
where control_rev_id = vx_control_rev_id
AND objective_code = l_ctrl_obj_code;
INSERT INTO amw_control_objectives
(control_objective_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,control_rev_id
,objective_code
---effective_date from and to are not used anywa
---,effective_date_from
---,effective_date_to
,OBJECT_VERSION_NUMBER
) VALUES (
amw_control_objectives_s.NEXTVAL
,SYSDATE
,v_user_id
,SYSDATE
,v_user_id
,vx_control_rev_id
,l_ctrl_obj_code
---effective_date from and to are not used anywa
---,SYSDATE
---,DECODE (l_ctrl_obj_flag, 'N', SYSDATE, NULL)
,1
);
update_interface_with_error (v_err_msg
,v_table_name
,v_interface_id
);
update_interface_with_error (v_err_msg
,v_table_name
,v_interface_id
);
update_interface_with_error (v_err_msg
,v_table_name
,v_interface_id
);
SELECT lookup_code
FROM AMW_LOOKUPS
WHERE lookup_type='AMW_CONTROL_ASSERTIONS'
AND enabled_flag='Y'
AND tag=p_lookup_tag;
UPDATE amw_control_assertions
SET effective_date_to = DECODE (l_ctrl_assert_flag, 'N', SYSDATE, NULL)
,last_update_date = SYSDATE
,last_updated_by = v_user_id
,OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
WHERE control_rev_id = vx_control_rev_id
AND ASSERTION_CODE = l_ctrl_assert_code;
select count(*)
into l_assert_exists
from amw_control_assertions
where control_rev_id = vx_control_rev_id
AND assertion_code = l_ctrl_assert_code;
delete from amw_control_assertions
where control_rev_id = vx_control_rev_id
AND assertion_code = l_ctrl_assert_code;
select count(*)
into l_assert_exists
from amw_control_assertions
where control_rev_id = vx_control_rev_id
AND assertion_code = l_ctrl_assert_code;
INSERT INTO amw_control_assertions
(control_assertion_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,control_rev_id
,ASSERTION_CODE
----not using the date columns anyway
----,effective_date_from
----,effective_date_to
,OBJECT_VERSION_NUMBER
) VALUES (
amw_control_assertions_s.NEXTVAL
,SYSDATE
,v_user_id
,SYSDATE
,v_user_id
,vx_control_rev_id
,l_ctrl_assert_code
----not using the date columns anyway
----,SYSDATE
----,DECODE (l_ctrl_assert_flag, 'N', SYSDATE, NULL)
,1
);
update_interface_with_error (v_err_msg
,v_table_name
,v_interface_id
);
update_interface_with_error (v_err_msg
,v_table_name
,v_interface_id
);
update_interface_with_error (v_err_msg
,v_table_name
,v_interface_id
);
PROCEDURE update_interface_with_error (
p_err_msg IN VARCHAR2
,p_table_name IN VARCHAR2
,p_interface_id IN NUMBER)
IS
l_interface_status amw_rcm_org_interface.interface_status%TYPE;
ROLLBACK; -- rollback any inserts done during the current loop process
fnd_file.put_line (fnd_file.LOG, '********************** UPDATE_INTERFACE_WITH_ERROR **********************');
SELECT interface_status
INTO l_interface_status
FROM amw_rcm_org_interface
WHERE rcm_org_interface_id = p_interface_id;
UPDATE amw_rcm_org_interface
SET interface_status =l_interface_status|| p_err_msg|| '**'
,error_flag = 'Y'
WHERE rcm_org_interface_id = p_interface_id;
END update_interface_with_error;
SELECT lookup_code
FROM AMW_LOOKUPS
WHERE lookup_type='AMW_ASSESSMENT_COMPONENTS'
AND enabled_flag='Y'
AND tag=p_lookup_tag;
select count(*)
into l_comp_exists
from amw_assessment_components
where object_type='CONTROL'
AND object_id = vx_control_rev_id
AND component_code = l_ctrl_comp_code;
delete from amw_assessment_components
where object_type='CONTROL'
AND object_id = vx_control_rev_id
AND component_code = l_ctrl_comp_code;
--- this control objective has been selected
--- need to check if it is already present or not
select count(*)
into l_comp_exists
from amw_assessment_components
where object_type='CONTROL'
AND object_id = vx_control_rev_id
AND component_code = l_ctrl_comp_code;
INSERT INTO amw_assessment_components
(assessment_component_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,component_code
,object_type
,object_id
,OBJECT_VERSION_NUMBER
) VALUES (
amw_assessment_components_s.NEXTVAL
,SYSDATE
,v_user_id
,SYSDATE
,v_user_id
,v_user_id
,l_ctrl_comp_code
,'CONTROL'
,vx_control_rev_id
,1
);
update_interface_with_error (v_err_msg
,v_table_name
,v_interface_id
);
update_interface_with_error (v_err_msg
,v_table_name
,v_interface_id
);
update_interface_with_error (v_err_msg
,v_table_name
,v_interface_id
);
INSERT INTO amw_control_associations(
control_association_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,control_id
,pk1
,PK2
,PK3
,object_type
,effective_date_from
,effective_date_to
,ASSOCIATION_CREATION_DATE
,OBJECT_VERSION_NUMBER)
VALUES (
amw_control_associations_s.NEXTVAL
,SYSDATE
,v_user_id
,SYSDATE
,v_user_id
,P_control_id
,P_ORGANIZATION_ID
,P_PROCESS_ID
,P_RISK_ID
,'RISK_ORG'
,SYSDATE
,NULL
,SYSDATE
,1
);
p_function => 'AMW_UPDATE_AP_DETAILS'
,p_object_name => 'AMW_AUDIT_PROCEDURE'
,p_instance_pk1_value => P_AUDIT_PROCEDURE_ID
,p_user_id => fnd_global.user_id);
SELECT AP_ASSOCIATION_ID,APPROVAL_DATE
INTO L_AP_ASSOCIATION_ID,L_AP_APPROVAL_DATE
FROM AMW_AP_ASSOCIATIONS
WHERE AUDIT_PROCEDURE_ID=P_AUDIT_PROCEDURE_ID
AND PK1=P_CONTROL_ID
AND OBJECT_TYPE='CTRL'
AND DELETION_DATE IS NULL;
UPDATE AMW_AP_ASSOCIATIONS
SET DELETION_DATE=SYSDATE
,OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1
,LAST_UPDATE_DATE=SYSDATE
,LAST_UPDATED_BY=G_USER_ID
,LAST_UPDATE_LOGIN=G_LOGIN_ID
WHERE AP_ASSOCIATION_ID=L_AP_ASSOCIATION_ID;
UPDATE_INTERFACE_WITH_ERROR(V_ERR_MSG,V_TABLE_NAME,v_interface_id);
--SIMPLY UPDATE THE ASSOCIATION ATTRIBUTES HERE
BEGIN
UPDATE AMW_AP_ASSOCIATIONS
SET DESIGN_EFFECTIVENESS=P_DESIGN_EFFECTIVENESS
,OP_EFFECTIVENESS=P_OP_EFFECTIVENESS
,OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1
,LAST_UPDATE_DATE=SYSDATE
,LAST_UPDATED_BY=G_USER_ID
,LAST_UPDATE_LOGIN=G_LOGIN_ID
WHERE AP_ASSOCIATION_ID=L_AP_ASSOCIATION_ID;
UPDATE_INTERFACE_WITH_ERROR(V_ERR_MSG,V_TABLE_NAME,v_interface_id);
SELECT AP_ASSOCIATION_ID,APPROVAL_DATE
INTO L_AP_ASSOCIATION_ID,L_AP_APPROVAL_DATE
FROM AMW_AP_ASSOCIATIONS
WHERE AUDIT_PROCEDURE_ID=P_AUDIT_PROCEDURE_ID
AND PK1=P_ORGANIZATION_ID
AND PK2=P_PROCESS_ID
AND PK3=P_CONTROL_ID
AND OBJECT_TYPE='CTRL_ORG'
AND DELETION_DATE IS NULL;
--IS LATEST_REVISION, SO SIMPLY UPDATE ASSOC ATTRIBUTES
IF(L_AP_APPROVAL_DATE IS NOT NULL) THEN
--THIS MEANS THAT THIS IS LATEST REVISION FOR THIS ASSOCIATION
--AND IS APPROVED, SO
BEGIN
UPDATE AMW_AP_ASSOCIATIONS
SET DELETION_DATE=SYSDATE
,OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1
,LAST_UPDATE_DATE=SYSDATE
,LAST_UPDATED_BY=G_USER_ID
,LAST_UPDATE_LOGIN=G_LOGIN_ID
WHERE AP_ASSOCIATION_ID=L_AP_ASSOCIATION_ID;
UPDATE_INTERFACE_WITH_ERROR(V_ERR_MSG,V_TABLE_NAME,v_interface_id);
--SIMPLY UPDATE THE ASSOCIATION ATTRIBUTES HERE
BEGIN
UPDATE AMW_AP_ASSOCIATIONS
SET DESIGN_EFFECTIVENESS=P_DESIGN_EFFECTIVENESS
,OP_EFFECTIVENESS=P_OP_EFFECTIVENESS
,OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1
,LAST_UPDATE_DATE=SYSDATE
,LAST_UPDATED_BY=G_USER_ID
,LAST_UPDATE_LOGIN=G_LOGIN_ID
WHERE AP_ASSOCIATION_ID=L_AP_ASSOCIATION_ID;
UPDATE_INTERFACE_WITH_ERROR(V_ERR_MSG,V_TABLE_NAME,v_interface_id);
INSERT INTO amw_ap_associations(
ap_association_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,pk1
,PK2
,PK3
,object_type
,audit_procedure_id
,design_effectiveness
,op_effectiveness
,object_version_number
,ASSOCIATION_CREATION_DATE)
VALUES (
AMW_AP_ASSOCIATIONS_S.NEXTVAL
,SYSDATE
,v_user_id
,SYSDATE
,v_user_id
,v_user_id
,P_PK1 --CTRL_ID OR ORG_ID
,P_PK2 --NULL OR PRC_ID
,P_PK3 --NULL OF CTRL_ID
,P_OBJECT_TYPE --CTRL OR CTRL_ORG
,P_AUDIT_PROCEDURE_ID
,NVL(P_DESIGN_EFFECTIVENESS,'N')
,NVL(P_OP_EFFECTIVENESS,'N')
,1
,SYSDATE
);
INSERT INTO amw_objective_associations(
objective_association_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,process_objective_id
,pk1
---01.13.2005 npanandi: added pk2,pk3,pk4,pk5 for Ctrl to Obj association
,PK2
,PK3
,PK4
,PK5
,object_type
,effective_date_from
,ASSOCIATION_CREATION_DATE
,OBJECT_VERSION_NUMBER)
VALUES (
amw_objective_associations_s.NEXTVAL
,SYSDATE
,v_user_id
,SYSDATE
,v_user_id
,P_PROCESS_OBJECTIVE_ID
,P_PK1 --organizationId or riskId or organizationId
,P_PK2 --processId or null or processId
,P_PK3 --null or null or riskId
,P_PK4 --null or null or controlId
,P_PK5 --null
,P_OBJECT_TYPE --'PROCESS_ORG' OR 'RISK' OR 'CONTROL_ORG'
,SYSDATE
,SYSDATE
,1
);
INSERT INTO amw_risk_associations(
risk_association_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,risk_id
,pk1 --org_id
,pk2 --process_id
,object_type
,effective_date_from
,ASSOCIATION_CREATION_DATE
,OBJECT_VERSION_NUMBER
,RISK_LIKELIHOOD_CODE
,RISK_IMPACT_CODE
,MATERIAL
,MATERIAL_VALUE)
VALUES (
amw_risk_associations_s.NEXTVAL
,SYSDATE
,v_user_id
,SYSDATE
,v_user_id
,P_RISK_ID
,P_ORGANIZATION_ID
,P_process_id
,'PROCESS_ORG'
,SYSDATE
,SYSDATE
,1
,P_risk_likelihood_code
,P_risk_impact_code
,P_MATERIAL
,P_MATERIAL_VALUE
);
select person_party_id
from fnd_user
where user_id=p_user_id;
select user_name from fnd_user where user_id=p_user_id;
insert into amw_risk_associations(
risk_association_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,risk_id
,pk1 --org_id
,object_type --entity_risk
,effective_date_from
,association_creation_date
,object_version_number
,risk_likelihood_code
,risk_impact_code
,material
,material_value)
values (
amw_risk_associations_s.nextval
,sysdate
,v_user_id
,sysdate
,v_user_id
,p_risk_id
,p_organization_id
,'ENTITY_RISK'
,sysdate
,sysdate
,1
,p_risk_likelihood_code
,p_risk_impact_code
,p_material
,p_material_value
);
insert into amw_control_associations(
control_association_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,control_id
,pk1
,pk2
,object_type
,effective_date_from
,effective_date_to
,association_creation_date
,object_version_number)
values (
amw_control_associations_s.nextval
,sysdate
,v_user_id
,sysdate
,v_user_id
,p_control_id
,p_organization_id
,p_risk_id
,'ENTITY_CONTROL'
,sysdate
,null
,sysdate
,1
);