[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_ap_interface, with error messages */
/* Deleting successful production inserts, based on profile */
/* */
/*****************************************************************************/
--
-- Used for exception processing
--
-- npanandi 11/08/2004 Fixed bug# 3824295 on the mainline
type t_AP_name IS table of amw_AP_INTERFACE.AP_name%type INDEX BY BINARY_INTEGER;
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_ap_pending_msg
,'AMW_CONTROLS'
,v_interface_id);
SELECT AP_NAME
,NVL(AP_APPROVAL_STATUS_CODE,'D') AS AP_APPROVAL_STATUS_CODE
,AP_INTERFACE_ID
FROM AMW_AP_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 AP_INTERFACE_ID ASC;
SELECT ap_name,
ap_description,
ap_approval_status_code,
ap_end_date,
revise_ap_flag,
control_name,
ap_step_name,
ap_step_description,
ap_step_samplesize,
---01.14.2005 npanandi: ApStepSeqNum column now supports
---alphanumeric post AMW.D
---so, created ApStepNum2 (Varchar2) column in AmwApInterface tbl
---and quering the new column while retaining the previous alias
/** ap_step_seqnum, **/
ap_step_seqnum2 as ap_step_seqnum,
ap_interface_id,
upper(nvl(design_effectiveness,'N')) as design_effectiveness,
upper(nvl(op_effectiveness,'N')) as op_effectiveness
--npanandi 12.13.2004: added following for AP classification
,CLASSIFICATION
FROM amw_ap_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 AP_INTERFACE_ID ASC;
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.audit_procedure_id, b.approval_status
FROM amw_audit_procedures_b b, amw_audit_procedures_tl tl
WHERE tl.name = c_ap_name
AND tl.language = USERENV('LANG')
AND tl.audit_procedure_rev_id = b.audit_procedure_rev_id
AND b.latest_revision_flag='Y';
SELECT b.ap_step_id,
b.name,
b.description,
b.samplesize,
b.from_rev_num,
b.to_rev_num
FROM amw_ap_steps_vl b
WHERE b.seqnum = c_step_num
AND b.audit_procedure_id = c_ap_id;
Select audit_procedure_rev_num
From amw_audit_procedures_b
Where audit_procedure_rev_id = c_audit_procedure_rev_id;
SELECT count(*)
INTO v_count
FROM AMW_AP_INTERFACE
WHERE BATCH_ID=p_batch_id
AND AP_NAME=AP_INV_UPL.AP_NAME
AND NVL(AP_APPROVAL_STATUS_CODE,'D') <> AP_INV_UPL.AP_APPROVAL_STATUS_CODE;
SELECT COUNT(1) INTO L_COUNT
FROM AMW_AP_INTERFACE
WHERE BATCH_ID=P_BATCH_ID
AND AP_NAME=ap_rec.ap_name
AND AP_INTERFACE_ID < V_INTERFACE_ID;
SELECT b.audit_procedure_id
into lx_audit_procedure_id
FROM amw_audit_procedures_b b, amw_audit_procedures_tl tl
WHERE tl.name = ap_rec.ap_name
AND tl.language = USERENV('LANG')
AND tl.audit_procedure_rev_id = b.audit_procedure_rev_id
AND b.latest_revision_flag='Y';
---Check for Update privilege here
l_new_ap := false;
p_function => 'AMW_UPDATE_AP_DETAILS'
,p_object_name => 'AMW_AUDIT_PROCEDURE'
,p_instance_pk1_value => lx_audit_procedure_id
,p_user_id => fnd_global.user_id);
v_err_msg := 'Cannot update this Audit Procedure';
update_interface_with_error (v_err_msg
,'AMW_AUDIT_PROCEDURE'
,v_interface_id);
update_interface_with_error (v_err_msg
,'AMW_AUDIT_PROCEDURES'
,v_interface_id);
update_interface_with_error (v_err_msg
,'AMW_AUDIT_PROCEDURES'
,v_interface_id);
SELECT AUDIT_PROCEDURE_ID,AUDIT_PROCEDURE_REV_ID
INTO L_AUDIT_PROCEDURE_ID,L_AUDIT_PROCEDURE_REV_ID
FROM AMW_AUDIT_PROCEDURES_VL
WHERE NAME=AP_REC.AP_NAME
AND LATEST_REVISION_FLAG='Y';
AMW_AUDIT_PROCEDURES_PVT.INSERT_AP_STEP(
P_API_VERSION_NUMBER => L_API_VERSION_NUMBER,
P_INIT_MSG_LIST => FND_API.G_TRUE,
P_COMMIT => FND_API.G_FALSE,
P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
P_SAMPLESIZE => AP_REC.ap_step_samplesize,
P_AUDIT_PROCEDURE_ID => L_AUDIT_PROCEDURE_ID,
P_SEQNUM => AP_REC.AP_STEP_SEQNUM,
P_REQUESTOR_ID => L_REQUESTOR_ID,
P_NAME => AP_REC.AP_STEP_NAME,
P_DESCRIPTION => AP_REC.AP_STEP_DESCRIPTION,
P_AUDIT_PROCEDURE_REV_ID => L_AUDIT_PROCEDURE_REV_ID,
P_USER_ID => G_USER_ID,
X_RETURN_STATUS => LX_RETURN_STATUS,
X_MSG_COUNT => LX_MSG_COUNT,
X_MSG_DATA => LX_MSG_DATA);
update_interface_with_error (v_err_msg
,'AMW_AUDIT_PROCEDURES'
,v_interface_id);
end if; ---03.01.2005 npanandi: end of l_has_access = 'T' check for ApStep insertions
update_interface_with_error (v_err_msg
,'AMW_AUDIT_PROCEDURES'
,v_interface_id);
update_interface_with_error (v_err_msg
,'AMW_AUDIT_PROCEDURES'
,v_interface_id);
AMW_AUDIT_PROCEDURES_PVT.INSERT_AP_CONTROL_ASSOC(
P_API_VERSION_NUMBER => L_API_VERSION_NUMBER,
P_INIT_MSG_LIST => FND_API.G_TRUE,
P_COMMIT => FND_API.G_FALSE,
P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
P_CONTROL_ID => AP_REC.CONTROL_NAME,
P_AUDIT_PROCEDURE_ID => L_AUDIT_PROCEDURE_ID,
P_DES_EFF => AP_REC.DESIGN_EFFECTIVENESS,
P_OP_EFF => AP_REC.OP_EFFECTIVENESS,
P_APPROVAL_DATE => L_APPROVAL_DATE,
P_USER_ID => G_USER_ID,
X_RETURN_STATUS => LX_RETURN_STATUS,
X_MSG_COUNT => LX_MSG_COUNT,
X_MSG_DATA => LX_MSG_DATA);
UPDATE amw_ap_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_ap_interface
WHERE batch_id = p_batch_id;
fnd_file.put_line (fnd_file.LOG,'err in delete interface records: '||SUBSTR (SQLERRM, 1, 200));
update_interface_with_error(
p_ERR_MSG => L_ERR_MSG
,p_table_name => 'AMW_AUDIT_PROCEDURES_B'
,P_INTERFACE_ID => V_INTERFACE_ID);
UPDATE amw_ap_interface
SET error_flag = 'Y'
,interface_status = v_invalid_requestor_msg
WHERE batch_id = p_batch_id;
UPDATE amw_ap_interface
SET error_flag = 'Y'
,interface_status = v_no_import_privilege_msg
WHERE batch_id = p_batch_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_ap_interface.interface_status%TYPE;
ROLLBACK; -- rollback any inserts done during the current loop process
SELECT interface_status
INTO l_interface_status
FROM amw_ap_interface
WHERE ap_interface_id = p_interface_id;
UPDATE amw_ap_interface
SET interface_status =
l_interface_status
-- || 'Error Msg: '
|| p_err_msg
-- || ' Table Name: '
-- || p_table_name
|| '**'
,error_flag = 'Y'
WHERE ap_interface_id = p_interface_id;
END update_interface_with_error;