[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT audit_procedure_id
FROM amw_audit_procedures_vl
WHERE name = l_audit_procedure_name;
SELECT count(*)
FROM amw_audit_procedures_b
GROUP BY audit_procedure_id
HAVING audit_procedure_id=l_audit_procedure_id;
SELECT audit_procedure_rev_id,
approval_status
FROM amw_audit_procedures_b
WHERE audit_procedure_id=l_audit_procedure_id AND
latest_revision_flag='Y';
-- this record is Pending Approval, cannot do G_OP_UPDATE or G_OP_REVISE
AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_PENDING_CHANGE_ERROR',
p_token_name => 'OBJ_TYPE',
p_token_value => G_OBJ_TYPE);
p_operate_mode => G_OP_UPDATE,
p_api_version_number => p_api_version_number,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_audit_procedure_rec => l_dummy_audit_procedure_rec,
x_audit_procedure_rev_id => x_audit_procedure_rev_id,
x_audit_procedure_id => x_audit_procedure_id,
p_approval_date => l_approval_date);
SELECT audit_procedure_rev_id
FROM amw_audit_procedures_b
WHERE audit_procedure_id = l_audit_procedure_id AND approval_status='D' AND latest_revision_flag='Y';
ELSIF p_operate_mode = G_OP_UPDATE THEN
l_dummy_audit_procedure_rec := p_audit_procedure_rec;
fnd_file.put_line (fnd_file.LOG, '&&&&&&&&&&&&&&& Going to Update &&&&&&&&&&&&&&&');
Update_AP(
p_operate_mode => p_operate_mode,
p_api_version_number => p_api_version_number,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_audit_procedure_rec => l_dummy_audit_procedure_rec,
x_audit_procedure_rev_id => x_audit_procedure_rev_id,
x_audit_procedure_id => x_audit_procedure_id);
fnd_file.put_line (fnd_file.LOG, '&&&&&&&&&&&&&&& Came out of Update &&&&&&&&&&&&&&&');
Update_AP(
p_operate_mode => p_operate_mode,
p_api_version_number => p_api_version_number,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_audit_procedure_rec => l_dummy_audit_procedure_rec,
x_audit_procedure_rev_id => x_audit_procedure_rev_id,
x_audit_procedure_id => x_audit_procedure_id);
ELSIF p_operate_mode = G_OP_DELETE THEN
Delete_AP(
p_operate_mode => p_operate_mode,
p_api_version_number => p_api_version_number,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_audit_procedure_rev_id => p_audit_procedure_rec.audit_procedure_rev_id,
x_audit_procedure_id => x_audit_procedure_id);
SELECT amw_procedure_rev_s.nextval
FROM dual;
SELECT 1
FROM amw_audit_procedures_b
WHERE audit_procedure_rev_id = l_rev_id;
SELECT amw_procedures_s.nextval
FROM dual;
SELECT 1
FROM amw_audit_procedures_b
WHERE audit_procedure_id = l_id;
-- Invoke table handler(AMW_AUDIT_PROCEDURES_PKG.Insert_Row)
AMW_UTILITY_PVT.debug_message( 'Private API: Calling AMW_AUDIT_PROCEDURES_PKG.Insert_Row');
AMW_AUDIT_PROCEDURES_PKG.Insert_Row(
x_rowid => l_row_id,
x_audit_procedure_rev_id => l_dummy_audit_procedure_rec.audit_procedure_rev_id,
x_project_id => l_dummy_audit_procedure_rec.project_id,
x_classification => l_dummy_audit_procedure_rec.classification,
x_attribute10 => l_dummy_audit_procedure_rec.attribute10,
x_attribute11 => l_dummy_audit_procedure_rec.attribute11,
x_attribute12 => l_dummy_audit_procedure_rec.attribute12,
x_attribute13 => l_dummy_audit_procedure_rec.attribute13,
x_attribute14 => l_dummy_audit_procedure_rec.attribute14,
x_attribute15 => l_dummy_audit_procedure_rec.attribute15,
x_object_version_number => l_object_version_number,
x_approval_status => l_dummy_audit_procedure_rec.approval_status,
x_orig_system_reference => l_dummy_audit_procedure_rec.orig_system_reference,
x_requestor_id => l_dummy_audit_procedure_rec.requestor_id,
x_attribute6 => l_dummy_audit_procedure_rec.attribute6,
x_attribute7 => l_dummy_audit_procedure_rec.attribute7,
x_attribute8 => l_dummy_audit_procedure_rec.attribute8,
x_attribute9 => l_dummy_audit_procedure_rec.attribute9,
x_security_group_id => l_dummy_audit_procedure_rec.security_group_id,
x_audit_procedure_id => l_dummy_audit_procedure_rec.audit_procedure_id,
x_audit_procedure_rev_num => l_dummy_audit_procedure_rec.audit_procedure_rev_num,
x_end_date => l_dummy_audit_procedure_rec.end_date,
x_approval_date => l_dummy_audit_procedure_rec.approval_date,
x_curr_approved_flag => l_dummy_audit_procedure_rec.curr_approved_flag,
x_latest_revision_flag => l_dummy_audit_procedure_rec.latest_revision_flag,
x_attribute5 => l_dummy_audit_procedure_rec.attribute5,
x_attribute_category => l_dummy_audit_procedure_rec.attribute_category,
x_attribute1 => l_dummy_audit_procedure_rec.attribute1,
x_attribute2 => l_dummy_audit_procedure_rec.attribute2,
x_attribute3 => l_dummy_audit_procedure_rec.attribute3,
x_attribute4 => l_dummy_audit_procedure_rec.attribute4,
x_name => l_dummy_audit_procedure_rec.audit_procedure_name,
x_description => l_dummy_audit_procedure_rec.audit_procedure_description,
x_creation_date => SYSDATE,
x_created_by => G_USER_ID,
x_last_update_date => SYSDATE,
x_last_updated_by => G_USER_ID,
x_last_update_login => G_LOGIN_ID);
PROCEDURE Update_AP(
p_operate_mode IN VARCHAR2,
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_audit_procedure_rec IN audit_procedure_rec_type,
x_audit_procedure_rev_id OUT NOCOPY NUMBER,
x_audit_procedure_id OUT NOCOPY NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_AP';
SELECT audit_procedure_rev_id
FROM amw_audit_procedures_b
WHERE audit_procedure_id = l_audit_procedure_id AND approval_status='D' AND latest_revision_flag='Y';
SAVEPOINT UPDATE_AP_PVT;
AMW_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
AMW_UTILITY_PVT.debug_message('l_audit_procedure_rev_id in Update_AP is NULL');
SELECT classification
INTO l_classification
FROM amw_audit_procedures_b
----03.01.2005 npanandi: ERROR in below JOIN
----audit_procedure_id should be equated to l_dummy_audit_procedure_rec.audit_procedure_id;
-- Invoke table handler(AMW_AUDIT_PROCEDURES_PKG.Update_Row)
AMW_AUDIT_PROCEDURES_PKG.Update_Row(
x_audit_procedure_rev_id => l_dummy_audit_procedure_rec.audit_procedure_rev_id,
x_project_id => l_dummy_audit_procedure_rec.project_id,
x_classification => l_dummy_audit_procedure_rec.classification,
x_attribute10 => l_dummy_audit_procedure_rec.attribute10,
x_attribute11 => l_dummy_audit_procedure_rec.attribute11,
x_attribute12 => l_dummy_audit_procedure_rec.attribute12,
x_attribute13 => l_dummy_audit_procedure_rec.attribute13,
x_attribute14 => l_dummy_audit_procedure_rec.attribute14,
x_attribute15 => l_dummy_audit_procedure_rec.attribute15,
x_object_version_number => l_dummy_audit_procedure_rec.object_version_number,
x_approval_status => l_dummy_audit_procedure_rec.approval_status,
x_orig_system_reference => l_dummy_audit_procedure_rec.orig_system_reference,
x_requestor_id => l_dummy_audit_procedure_rec.requestor_id,
x_attribute6 => l_dummy_audit_procedure_rec.attribute6,
x_attribute7 => l_dummy_audit_procedure_rec.attribute7,
x_attribute8 => l_dummy_audit_procedure_rec.attribute8,
x_attribute9 => l_dummy_audit_procedure_rec.attribute9,
x_security_group_id => l_dummy_audit_procedure_rec.security_group_id,
x_audit_procedure_id => l_dummy_audit_procedure_rec.audit_procedure_id,
x_audit_procedure_rev_num => l_dummy_audit_procedure_rec.audit_procedure_rev_num,
x_end_date => l_dummy_audit_procedure_rec.end_date,
x_approval_date => l_dummy_audit_procedure_rec.approval_date,
x_curr_approved_flag => l_dummy_audit_procedure_rec.curr_approved_flag,
x_latest_revision_flag => l_dummy_audit_procedure_rec.latest_revision_flag,
x_attribute5 => l_dummy_audit_procedure_rec.attribute5,
x_attribute_category => l_dummy_audit_procedure_rec.attribute_category,
x_attribute1 => l_dummy_audit_procedure_rec.attribute1,
x_attribute2 => l_dummy_audit_procedure_rec.attribute2,
x_attribute3 => l_dummy_audit_procedure_rec.attribute3,
x_attribute4 => l_dummy_audit_procedure_rec.attribute4,
x_name => l_dummy_audit_procedure_rec.audit_procedure_name,
x_description => l_dummy_audit_procedure_rec.audit_procedure_description,
x_last_update_date => SYSDATE,
x_last_updated_by => G_USER_ID,
x_last_update_login => G_LOGIN_ID);
UPDATE amw_ap_associations
SET approval_date = l_dummy_audit_procedure_rec.approval_date
WHERE audit_procedure_id = l_dummy_audit_procedure_rec.audit_procedure_id
AND object_type = 'CTRL';
ROLLBACK TO UPDATE_AP_PVT;
ROLLBACK TO UPDATE_AP_PVT;
ROLLBACK TO UPDATE_AP_PVT;
End Update_AP;
PROCEDURE Delete_AP(
p_operate_mode IN VARCHAR2,
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_audit_procedure_rev_id IN NUMBER,
x_audit_procedure_id OUT NOCOPY NUMBER
)
IS
L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_AP';
SAVEPOINT DELETE_AP_PVT;
AMW_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
AMW_AUDIT_PROCEDURES_PKG.Delete_Row(
x_audit_procedure_rev_id => p_audit_procedure_rev_id);
ROLLBACK TO DELETE_AP_PVT;
ROLLBACK TO DELETE_AP_PVT;
ROLLBACK TO DELETE_AP_PVT;
End Delete_AP;
SELECT audit_procedure_rev_id,
audit_procedure_id,
audit_procedure_rev_num,
object_version_number
FROM amw_audit_procedures_b
WHERE audit_procedure_id = l_audit_procedure_id AND ( approval_status='A' OR approval_status='R') AND latest_revision_flag='Y';
Update_AP(
p_operate_mode => p_operate_mode,
p_api_version_number => p_api_version_number,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_audit_procedure_rec => l_dummy_audit_procedure_rec,
x_audit_procedure_rev_id => x_audit_procedure_rev_id,
x_audit_procedure_id => x_audit_procedure_id);
---03.01.2005 npanandi: inserted the below SQL into a local Begin block
---webADI upload gives a "Exact Fetch return More than one Row" error
begin
SELECT classification
INTO l_classification
FROM amw_audit_procedures_b
---WHERE audit_procedure_id = target_revision.audit_procedure_rev_id;
SELECT *
FROM amw_audit_procedures_b
WHERE audit_procedure_rev_id = p_audit_procedure_rec.audit_procedure_rev_id;
SELECT name,
description
FROM amw_audit_procedures_vl
WHERE audit_procedure_rev_id = p_audit_procedure_rec.audit_procedure_rev_id;
IF p_audit_procedure_rec.last_update_date IS NULL THEN
x_complete_rec.last_update_date := l_audit_procedure_rec.last_update_date;
IF p_audit_procedure_rec.last_update_login IS NULL THEN
x_complete_rec.last_update_login := l_audit_procedure_rec.last_update_login;
IF p_audit_procedure_rec.last_updated_by IS NULL THEN
x_complete_rec.last_updated_by := l_audit_procedure_rec.last_updated_by;
-- 07.21.2003 tsho, only update and revise need complete_AP_rec
IF p_operate_mode = G_OP_UPDATE OR p_operate_mode = G_OP_REVISE THEN
Complete_AP_Rec(
p_audit_procedure_rec => p_audit_procedure_rec,
x_complete_rec => l_audit_procedure_rec);
ELSIF p_operate_mode = G_OP_UPDATE THEN
Validate_update_AP_rec(
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_audit_procedure_rec => p_audit_procedure_rec);
ELSIF p_operate_mode = G_OP_DELETE THEN
Validate_delete_AP_rec(
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_audit_procedure_rec => p_audit_procedure_rec);
SELECT 1
FROM amw_audit_procedures_vl
WHERE name = l_audit_procedure_name;
PROCEDURE Validate_update_AP_rec(
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_audit_procedure_rec IN audit_procedure_rec_type
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Validate_Update_AP_Rec';
SELECT approval_status
FROM amw_audit_procedures_b
WHERE audit_procedure_rev_id = l_audit_procedure_rev_id;
SELECT 1
FROM amw_audit_procedures_vl
WHERE name = l_audit_procedure_name AND audit_procedure_id <> l_audit_procedure_id;
-- only approval_status='D' can be updated
OPEN c_target_audit_procedure(p_audit_procedure_rec.audit_procedure_rev_id);
END Validate_update_AP_rec;
SELECT approval_status
FROM amw_audit_procedures_b
WHERE audit_procedure_rev_id = l_audit_procedure_rev_id;
SELECT name
FROM amw_audit_procedures_vl
WHERE audit_procedure_rev_id = l_audit_procedure_rev_id;
PROCEDURE Validate_delete_AP_rec(
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_audit_procedure_rec IN audit_procedure_rec_type
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Validate_Delete_AP_Rec';
SELECT 1
FROM amw_audit_procedures_b
WHERE audit_procedure_rev_id = l_audit_procedure_rev_id;
-- can only delete an audit procedure which exists and has APPROVAL_STATUS='''
l_dummy := NULL;
END Validate_delete_AP_rec;
CURSOR steps_b IS SELECT
AMW_AP_STEPS_S.NEXTVAL STEP_ID,
SEQNUM,
SAMPLESIZE,
step.LAST_UPDATE_DATE,
step.LAST_UPDATED_BY,
step.CREATION_DATE,
step.CREATED_BY,
step.LAST_UPDATE_LOGIN,
step.ATTRIBUTE_CATEGORY,
step.ATTRIBUTE1,
step.ATTRIBUTE2,
step.ATTRIBUTE3,
step.ATTRIBUTE4,
step.ATTRIBUTE5,
step.ATTRIBUTE6,
step.ATTRIBUTE7,
step.ATTRIBUTE8,
step.ATTRIBUTE9,
step.ATTRIBUTE10,
step.ATTRIBUTE11,
step.ATTRIBUTE12,
step.ATTRIBUTE13,
step.ATTRIBUTE14,
step.ATTRIBUTE15,
step.SECURITY_GROUP_ID,
step.OBJECT_VERSION_NUMBER,
step.ORIG_SYSTEM_REFERENCE,
step.REQUESTOR_ID,
step.NAME,
step.DESCRIPTION,
step.CSEQNUM
FROM AMW_AP_STEPS_VL step, AMW_AUDIT_PROCEDURES_B ap
WHERE ap.audit_procedure_rev_id = x_from_ap_rev_id and
ap.audit_procedure_id = step.audit_procedure_id and
ap.audit_procedure_rev_num >= step.from_rev_num and
ap.audit_procedure_rev_num < NVL ( step.to_rev_num, ap.audit_procedure_rev_num + 1) ;
AMW_AP_STEPS_PKG.INSERT_ROW (
X_ROWID => l_row_id,
X_AP_STEP_ID => steprec.STEP_ID,
X_ATTRIBUTE4 => steprec.ATTRIBUTE4,
X_ATTRIBUTE5 => steprec.ATTRIBUTE5,
X_ATTRIBUTE1 => steprec.ATTRIBUTE1 ,
X_ATTRIBUTE6 => steprec.ATTRIBUTE6 ,
X_ATTRIBUTE7 => steprec.ATTRIBUTE7,
X_ATTRIBUTE8 => steprec.ATTRIBUTE8,
X_ATTRIBUTE9 => steprec.ATTRIBUTE9,
X_SAMPLESIZE => steprec.SAMPLESIZE,
X_AUDIT_PROCEDURE_ID => x_to_ap_id,
X_SEQNUM => steprec.SEQNUM,
X_ATTRIBUTE2 => steprec.ATTRIBUTE2,
X_ATTRIBUTE3 => steprec.ATTRIBUTE3,
X_ATTRIBUTE10 => steprec.ATTRIBUTE10,
X_ATTRIBUTE11 => steprec.ATTRIBUTE11,
X_ATTRIBUTE12 => steprec.ATTRIBUTE12,
X_ATTRIBUTE13 => steprec.ATTRIBUTE13,
X_ATTRIBUTE14 => steprec.ATTRIBUTE14,
X_ATTRIBUTE15 => steprec.ATTRIBUTE15,
X_SECURITY_GROUP_ID => steprec.SECURITY_GROUP_ID,
X_OBJECT_VERSION_NUMBER => l_object_version_number,
X_ORIG_SYSTEM_REFERENCE => steprec.ORIG_SYSTEM_REFERENCE,
X_REQUESTOR_ID => steprec.REQUESTOR_ID,
X_ATTRIBUTE_CATEGORY => steprec.ATTRIBUTE_CATEGORY,
X_NAME => steprec.NAME,
X_DESCRIPTION => steprec.DESCRIPTION,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => G_USER_ID,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => G_USER_ID,
X_LAST_UPDATE_LOGIN => G_LOGIN_ID,
X_FROM_REV_NUM => l_from_rev_num,
X_TO_REV_NUM => NULL,
X_CSEQNUM => steprec.CSEQNUM);
INSERT INTO AMW_AP_TASKS (AP_TASK_ID,
AUDIT_PROCEDURE_ID,
TASK_ID,
PROJECT_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
SECURITY_GROUP_ID,
OBJECT_VERSION_NUMBER,
ORIG_SYSTEM_REFERENCE,
REQUESTOR_ID)
(SELECT AMW_AP_TASKS_S.NEXTVAL,
x_to_ap_id,
taskrec.TASK_ID,
taskrec.PROJECT_ID,
SYSDATE,
G_USER_ID,
SYSDATE,
G_USER_ID,
G_LOGIN_ID,
taskrec.ATTRIBUTE_CATEGORY,
taskrec.ATTRIBUTE1,
taskrec.ATTRIBUTE2,
taskrec.ATTRIBUTE3,
taskrec.ATTRIBUTE4,
taskrec.ATTRIBUTE5,
taskrec.ATTRIBUTE6,
taskrec.ATTRIBUTE7,
taskrec.ATTRIBUTE8,
taskrec.ATTRIBUTE9,
taskrec.ATTRIBUTE10,
taskrec.ATTRIBUTE11,
taskrec.ATTRIBUTE12,
taskrec.ATTRIBUTE13,
taskrec.ATTRIBUTE14,
taskrec.ATTRIBUTE15,
taskrec.SECURITY_GROUP_ID,
1,
taskrec.ORIG_SYSTEM_REFERENCE,
taskrec.REQUESTOR_ID
FROM AMW_AP_TASKS taskrec
WHERE audit_procedure_id = x_from_ap_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 ,
PK4 ,
PK5 ,
OBJECT_TYPE ,
AUDIT_PROCEDURE_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
SECURITY_GROUP_ID ,
OBJECT_VERSION_NUMBER,
DESIGN_EFFECTIVENESS ,
OP_EFFECTIVENESS)
(SELECT AMW_AP_ASSOCIATIONS_S.NEXTVAL,
SYSDATE,
G_USER_ID,
SYSDATE,
G_USER_ID,
G_LOGIN_ID,
ctrlrec.PK1,
ctrlrec.PK2,
ctrlrec.PK3,
ctrlrec.PK4,
ctrlrec.PK5,
ctrlrec.OBJECT_TYPE,
x_to_ap_id,
ctrlrec.ATTRIBUTE_CATEGORY,
ctrlrec.ATTRIBUTE1,
ctrlrec.ATTRIBUTE2,
ctrlrec.ATTRIBUTE3,
ctrlrec.ATTRIBUTE4,
ctrlrec.ATTRIBUTE5,
ctrlrec.ATTRIBUTE6,
ctrlrec.ATTRIBUTE7,
ctrlrec.ATTRIBUTE8,
ctrlrec.ATTRIBUTE9,
ctrlrec.ATTRIBUTE10,
ctrlrec.ATTRIBUTE11,
ctrlrec.ATTRIBUTE12,
ctrlrec.ATTRIBUTE13,
ctrlrec.ATTRIBUTE14,
ctrlrec.ATTRIBUTE15,
ctrlrec.SECURITY_GROUP_ID,
1,
ctrlrec.DESIGN_EFFECTIVENESS,
ctrlrec.OP_EFFECTIVENESS
FROM AMW_AP_ASSOCIATIONS ctrlrec
WHERE audit_procedure_id = x_from_ap_id);
procedure insert_ap_step(
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
p_samplesize in number,
p_audit_procedure_id in number,
p_seqnum in varchar2,
p_requestor_id in number,
p_name in varchar2,
p_description in varchar2,
p_audit_procedure_rev_id in number,
p_user_id in number,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
is
CURSOR c_step_exists (c_step_num IN varchar2,c_ap_id IN NUMBER, c_from_ap_rev_num IN NUMBER) IS
SELECT b.ap_step_id,
b.name,
b.description,
b.samplesize,
b.from_rev_num,
b.to_rev_num,
b.object_version_number
FROM amw_ap_steps_vl b
WHERE b.cseqnum = c_step_num
AND b.audit_procedure_id = c_ap_id
AND b.from_rev_num = c_from_ap_rev_num;
SELECT b.ap_step_id,
b.name,
b.description,
b.samplesize,
b.cseqnum,
b.from_rev_num,
b.to_rev_num,
b.object_version_number
FROM amw_ap_steps_vl b
WHERE b.cseqnum = c_step_num
AND b.audit_procedure_id = c_ap_id
AND b.to_rev_num is null and b.from_rev_num <> c_from_ap_rev_num;
Select audit_procedure_rev_num
From amw_audit_procedures_b
Where audit_procedure_rev_id = c_audit_procedure_rev_id;
select amw_ap_steps_s.nextval from dual;
l_api_name CONSTANT VARCHAR2(30) := 'Insert_AP_Step';
SAVEPOINT INSERT_AP_STEP_PVT;
fnd_file.put_line(fnd_file.LOG,'Inside insert_ap_step --> x_return_status: '||x_return_status);
amw_ap_steps_pkg.update_row(
X_AP_STEP_ID => lx_step_rec.ap_step_id,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE1 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_SAMPLESIZE => p_samplesize,
X_AUDIT_PROCEDURE_ID => p_audit_procedure_id,
X_SEQNUM => null,
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => 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 => lx_step_rec.object_version_number + 1,
X_ORIG_SYSTEM_REFERENCE => null,
X_REQUESTOR_ID => p_requestor_id,
X_ATTRIBUTE_CATEGORY => null,
X_NAME => p_name,
X_DESCRIPTION => p_description,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => p_user_id,
X_LAST_UPDATE_LOGIN => p_user_id,
X_FROM_REV_NUM => l_ap_rev_num,
X_TO_REV_NUM => null,
X_CSEQNUM => p_seqnum);
amw_ap_steps_pkg.update_row(
X_AP_STEP_ID => lx_prev_step_rec.ap_step_id,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE1 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_SAMPLESIZE => lx_prev_step_rec.samplesize,
X_AUDIT_PROCEDURE_ID => p_audit_procedure_id,
X_SEQNUM => null,
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => 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 => lx_prev_step_rec.object_version_number + 1,
X_ORIG_SYSTEM_REFERENCE => null,
X_REQUESTOR_ID => p_requestor_id,
X_ATTRIBUTE_CATEGORY => null,
X_NAME => lx_prev_step_rec.name,
X_DESCRIPTION => lx_prev_step_rec.description,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => p_user_id,
X_LAST_UPDATE_LOGIN => p_user_id,
X_FROM_REV_NUM => lx_prev_step_rec.from_rev_num,
X_TO_REV_NUM => l_ap_rev_num,
X_CSEQNUM => lx_prev_step_rec.cseqnum);
amw_ap_steps_pkg.insert_row(X_ROWID => lx_rowid,
X_AP_STEP_ID => l_ap_step_id,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE1 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_SAMPLESIZE => p_samplesize,
X_AUDIT_PROCEDURE_ID => p_audit_procedure_id,
X_SEQNUM => null,
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => 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_ORIG_SYSTEM_REFERENCE => null,
X_REQUESTOR_ID => p_requestor_id,
X_ATTRIBUTE_CATEGORY => null,
X_NAME => p_name,
X_DESCRIPTION => p_description,
X_CREATION_DATE => sysdate,
X_CREATED_BY => p_user_id,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => p_user_id,
X_LAST_UPDATE_LOGIN => p_user_id,
X_FROM_REV_NUM => l_ap_rev_num,
X_TO_REV_NUM => null,
X_CSEQNUM => p_seqnum);
amw_ap_steps_pkg.insert_row(X_ROWID => lx_rowid,
X_AP_STEP_ID => l_ap_step_id,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE1 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_SAMPLESIZE => p_samplesize,
X_AUDIT_PROCEDURE_ID => p_audit_procedure_id,
X_SEQNUM => null,
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => 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_ORIG_SYSTEM_REFERENCE => null,
X_REQUESTOR_ID => p_requestor_id,
X_ATTRIBUTE_CATEGORY => null,
X_NAME => p_name,
X_DESCRIPTION => p_description,
X_CREATION_DATE => sysdate,
X_CREATED_BY => p_user_id,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => p_user_id,
X_LAST_UPDATE_LOGIN => p_user_id,
X_FROM_REV_NUM => l_ap_rev_num,
X_TO_REV_NUM => null,
X_CSEQNUM => p_seqnum);
fnd_file.put_line(fnd_file.LOG,'Done with amw_ap_steps_pkg.insert_row');
ROLLBACK TO INSERT_AP_STEP_PVT;
end insert_ap_step;
procedure insert_ap_control_assoc(
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
p_control_id in number,
p_audit_procedure_id in number,
p_des_eff in varchar2,
p_op_eff in varchar2,
p_approval_date in date,
p_user_id in number,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
is
CURSOR c_assoc_exists (c_control_id IN NUMBER,c_ap_id IN NUMBER) IS
SELECT a.ap_association_id,
a.pk1,
a.audit_procedure_id,
a.design_effectiveness,
a.op_effectiveness,
a.object_version_number
FROM amw_ap_associations a
WHERE a.audit_procedure_id = c_ap_id
AND a.object_type = 'CTRL'
AND a.pk1 = c_control_id
AND a.deletion_date is null
AND a.approval_date is null;
SELECT a.ap_association_id,
a.pk1,
a.audit_procedure_id,
a.design_effectiveness,
a.op_effectiveness,
a.object_type,
a.object_version_number
FROM amw_ap_associations a
WHERE a.audit_procedure_id = c_ap_id
AND a.object_type = 'CTRL'
AND a.pk1 = c_control_id
AND a.deletion_date is null
AND a.approval_date is not null;
l_api_name CONSTANT VARCHAR2(30) := 'Insert_AP_Control_Assoc';
SAVEPOINT INSERT_AP_CONTROL_ASSOC_PVT;
fnd_file.put_line(fnd_file.LOG,'Inside insert_ap_control_assoc --> x_return_status: '||x_return_status);
UPDATE amw_ap_associations
SET design_effectiveness = p_des_eff,
op_effectiveness = p_op_eff,
object_version_number = object_version_number + 1,
approval_date = p_approval_date
WHERE ap_association_id = lx_assoc_rec.ap_association_id;
UPDATE amw_ap_associations
SET deletion_date = l_date,
object_version_number = object_version_number + 1,
deletion_approval_date = p_approval_date
WHERE ap_association_id = lx_prev_assoc_rec.ap_association_id;
INSERT INTO amw_ap_associations
(ap_association_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,audit_procedure_id
,pk1
,object_type
,design_effectiveness
,op_effectiveness
,object_version_number
,association_creation_date
,approval_date
,deletion_date
,deletion_approval_date
)
VALUES (amw_ap_associations_s.NEXTVAL
,SYSDATE
,p_user_id
,SYSDATE
,p_user_id
,p_user_id
,p_audit_procedure_id
,p_control_id
,'CTRL'
,p_des_eff
,p_op_eff
,1
,l_date
,p_approval_date
,null
,null
);
INSERT INTO amw_ap_associations
(ap_association_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,audit_procedure_id
,pk1
,object_type
,design_effectiveness
,op_effectiveness
,object_version_number
,association_creation_date
,approval_date
,deletion_date
,deletion_approval_date
)
VALUES (amw_ap_associations_s.NEXTVAL
,SYSDATE
,p_user_id
,SYSDATE
,p_user_id
,p_user_id
,p_audit_procedure_id
,p_control_id
,'CTRL'
,p_des_eff
,p_op_eff
,1
,l_date
,p_approval_date
,null
,null
);
fnd_file.put_line(fnd_file.LOG,'Done with amw_ap_steps_pkg.insert_row');
ROLLBACK TO INSERT_AP_CONTROL_ASSOC_PVT;
end insert_ap_control_assoc;
SELECT object_id
FROM fnd_objects
WHERE obj_name = cp_object_name;
SELECT application_id
FROM fnd_application
WHERE application_short_name = 'AMW';
SELECT count(*)
FROM amw_audit_procedures_b
GROUP BY audit_procedure_id
HAVING audit_procedure_id=l_audit_procedure_id;
SELECT audit_procedure_rev_id,
approval_status
FROM amw_audit_procedures_b
WHERE audit_procedure_id=l_audit_procedure_id AND
latest_revision_flag='Y';
SELECT amw_procedure_rev_s.nextval
FROM dual;
-- this record is Pending Approval, cannot do G_OP_UPDATE or G_OP_REVISE
AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_PENDING_CHANGE_ERROR',
p_token_name => 'OBJ_TYPE',
p_token_value => G_OBJ_TYPE);
insert into AMW_AUDIT_PROCEDURES_B (
PROJECT_ID,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
OBJECT_VERSION_NUMBER,
APPROVAL_STATUS,
ORIG_SYSTEM_REFERENCE,
REQUESTOR_ID,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
SECURITY_GROUP_ID,
AUDIT_PROCEDURE_ID,
AUDIT_PROCEDURE_REV_ID,
AUDIT_PROCEDURE_REV_NUM,
END_DATE,
APPROVAL_DATE,
CURR_APPROVED_FLAG,
LATEST_REVISION_FLAG,
ATTRIBUTE5,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CLASSIFICATION
) (
SELECT PROJECT_ID,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
1,
'D',
ORIG_SYSTEM_REFERENCE,
REQUESTOR_ID,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
SECURITY_GROUP_ID,
AUDIT_PROCEDURE_ID,
l_audit_procedure_rev_id,
AUDIT_PROCEDURE_REV_NUM + 1,
NULL,
NULL,
'N',
'Y',
ATTRIBUTE5,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
SYSDATE,
G_USER_ID,
SYSDATE,
G_USER_ID,
G_LOGIN_ID,
CLASSIFICATION
FROM AMW_AUDIT_PROCEDURES_B
WHERE AUDIT_PROCEDURE_REV_ID = l_approval_status.audit_procedure_rev_id
);
insert into AMW_AUDIT_PROCEDURES_TL (
AUDIT_PROCEDURE_REV_ID,
NAME,
DESCRIPTION,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
SECURITY_GROUP_ID,
LANGUAGE,
SOURCE_LANG
) (select
l_audit_procedure_rev_id,
NAME,
DESCRIPTION,
SYSDATE,
G_USER_ID,
SYSDATE,
G_USER_ID,
G_LOGIN_ID,
SECURITY_GROUP_ID,
LANGUAGE,
SOURCE_LANG
from AMW_AUDIT_PROCEDURES_TL
where AUDIT_PROCEDURE_REV_ID = l_approval_status.audit_procedure_rev_id);
UPDATE AMW_AUDIT_PROCEDURES_B
SET LATEST_REVISION_FLAG = 'N',
END_DATE = SYSDATE,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
WHERE AUDIT_PROCEDURE_REV_ID = l_approval_status.audit_procedure_rev_id;