[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_control_pending_msg
,'AMW_CONTROLS'
,v_interface_id);
SELECT ctrl_interface_id
,control_approval_status_code
,control_automation_type_code
,control_description
,control_job_id
,control_location_code
,control_name
,control_source
,control_type_code
,physical_evidence
,revise_ctrl_flag
,control_application_id
,preventive_control
,detective_control
,disclosure_control
,key_mitigating
,verification_source
,verification_source_name
,verification_instruction
,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
,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
--npanandi 12.08.2004: Control Enhancements upload
,UOM_CODE
,CONTROL_FREQUENCY
,CTRL_PURPOSE1
,CTRL_PURPOSE2
,CTRL_PURPOSE3
,CTRL_PURPOSE4
,CTRL_PURPOSE5
,CTRL_PURPOSE6
,CTRL_PURPOSE7
,CTRL_PURPOSE8
,CTRL_PURPOSE9
,CTRL_PURPOSE10
,CTRL_PURPOSE11
,CTRL_PURPOSE12
,CTRL_PURPOSE13
,CTRL_PURPOSE14
,CTRL_PURPOSE15
,CTRL_PURPOSE16
,CTRL_PURPOSE17
,CTRL_PURPOSE18
,CTRL_PURPOSE19
,CTRL_PURPOSE20
,CTRL_PURPOSE21
,CTRL_PURPOSE22
,CTRL_PURPOSE23
,CTRL_PURPOSE24
,CTRL_PURPOSE25
,CTRL_PURPOSE26
,CTRL_PURPOSE27
,CTRL_PURPOSE28
,CTRL_PURPOSE29
,CTRL_PURPOSE30
---NPANANDI 12.13.2004: ADDED BELOW FOR CTRL CLASSIFICATION ENH.
,CLASSIFICATION
FROM amw_ctrl_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 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.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 DECODE (ctrl_rec.control_name,NULL
,l_amw_control_name_prefix||amw_controls_tl_s1.NEXTVAL
,ctrl_rec.control_name)
INTO l_control_name
FROM dual;
---Check for Update privilege here
l_new_control := false;
fnd_file.put_line (fnd_file.LOG, '************** Checking Update Privilege for l_control_name: '||l_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 l_control_name: '||l_control_name);
v_err_msg := 'Cannot update this Ctrl';
update_interface_with_error (v_err_msg
,'AMW_CONTROLS'
,v_interface_id);
---02.28.2005 npanandi: added check for lHasAccess to update this Ctrl
---only if this user has Upd privilege
l_has_access = 'T' and
(lx_control_id is null OR
l_revise_control_flag = 'Y') THEN
l_control_rec.name := l_control_name;
update_interface_with_error (v_err_msg
,'AMW_CONTROLS'
,v_interface_id);
update_interface_with_error (v_err_msg,v_table_name,v_interface_id);
UPDATE amw_ctrl_interface
SET process_flag = l_process_flag
,last_update_date = SYSDATE
,last_updated_by = v_user_id
WHERE batch_id = p_batch_id;
,'err in update process flag: '||SUBSTR (SQLERRM, 1, 200));
DELETE FROM amw_ctrl_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_ctrl_interface
SET error_flag = 'Y'
,interface_status = v_invalid_requestor_msg
WHERE batch_id = p_batch_id;
UPDATE amw_ctrl_interface
SET error_flag = 'Y'
,interface_status = v_no_import_privilege_msg
WHERE batch_id = p_batch_id;
fnd_file.put_line (fnd_file.LOG,'updated the Intf table');
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
);
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
);
SELECT lookup_code
FROM AMW_LOOKUPS
WHERE lookup_type='AMW_CONTROL_PURPOSES'
AND enabled_flag='Y'
AND tag=p_lookup_tag;
select count(*)
into l_PURPOSE_exists
from amw_control_PURPOSES
where control_rev_id = vx_control_rev_id
AND PURPOSE_code = l_ctrl_PURPOSE_code;
delete from amw_control_PURPOSEs
where control_rev_id = vx_control_rev_id
AND PURPOSE_code = l_ctrl_PURPOSE_code;
select count(*)
into l_PURPOSE_exists
from amw_control_PURPOSEs
where control_rev_id = vx_control_rev_id
AND PURPOSE_code = l_ctrl_PURPOSE_code;
INSERT INTO amw_control_PURPOSES(
control_PURPOSE_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,control_rev_id
,PURPOSE_CODE
,effective_date_from
----,effective_date_to
,OBJECT_VERSION_NUMBER
) VALUES (
amw_control_PURPOSEs_s.NEXTVAL
,SYSDATE
,v_user_id
,SYSDATE
,v_user_id
,vx_control_rev_id
,l_ctrl_PURPOSE_code
,SYSDATE
----,DECODE (l_ctrl_PURPOSE_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 person_party_id
from fnd_user
where user_id=p_user_id;
select user_name from fnd_user where user_id=p_user_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_ctrl_interface.interface_status%TYPE;
ROLLBACK; -- rollback any inserts done during the current loop process
SELECT interface_status
INTO l_interface_status
FROM amw_ctrl_interface
WHERE ctrl_interface_id = p_interface_id;
UPDATE amw_ctrl_interface
SET interface_status =
l_interface_status
-- || 'Error Msg: '
|| p_err_msg
-- || ' Table Name: '
-- || p_table_name
|| '**'
,error_flag = 'Y'
WHERE ctrl_interface_id = p_interface_id;
END update_interface_with_error;