The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_select_clause VARCHAR2(10) := 'SELECT 1';
l_query_clause := l_select_clause || l_from_clause || l_where_clause;
PROCEDURE insert_plan_rel_chk(
p_parent_plan_id IN NUMBER,
p_parent_plan_name IN VARCHAR2,
p_child_plan_id IN NUMBER,
p_child_plan_name IN VARCHAR2,
p_data_entry_mode IN NUMBER,
p_layout_mode IN NUMBER,
p_num_auto_rows IN NUMBER,
x_parent_plan_id OUT NOCOPY NUMBER,
x_child_plan_id OUT NOCOPY NUMBER,
x_status OUT NOCOPY VARCHAR2)
IS
ret_status VARCHAR2(1) := 'T';
SELECT 1
FROM qa_pc_plan_relationship
WHERE parent_plan_id = c_parent_plan_id
AND child_plan_id = c_child_plan_id;
l_error_array.delete;
select qa_plans_api.plan_id(p_parent_plan_name) into result_num from dual;
select qa_plans_api.plan_id(p_child_plan_name) into result_num from dual;
END insert_plan_rel_chk;
PROCEDURE update_plan_rel_chk(
p_parent_plan_id IN NUMBER,
p_parent_plan_name IN VARCHAR2,
p_child_plan_id IN NUMBER,
p_child_plan_name IN VARCHAR2,
p_data_entry_mode IN NUMBER,
p_layout_mode IN NUMBER,
p_num_auto_rows IN NUMBER,
p_new_plan IN VARCHAR2,
x_parent_plan_id OUT NOCOPY NUMBER,
x_child_plan_id OUT NOCOPY NUMBER,
x_status OUT NOCOPY VARCHAR2)
IS
ret_status VARCHAR2(1) := 'T';
SELECT 1
FROM qa_pc_plan_relationship
WHERE parent_plan_id = c_parent_plan_id
AND child_plan_id = c_child_plan_id;
l_error_array.delete;
select qa_plans_api.plan_id(p_parent_plan_name) into result_num from dual;
select qa_plans_api.plan_id(p_child_plan_name) into result_num from dual;
END update_plan_rel_chk;
PROCEDURE insert_plan_rel(p_parent_plan_id NUMBER,
p_child_plan_id NUMBER,
p_plan_relationship_type NUMBER,
p_data_entry_mode NUMBER,
p_layout_mode NUMBER,
p_auto_row_count NUMBER,
p_default_parent_spec VARCHAR2,
p_last_updated_by NUMBER := fnd_global.user_id,
p_created_by NUMBER := fnd_global.user_id,
p_last_update_login NUMBER := fnd_global.user_id,
x_plan_relationship_id IN OUT NOCOPY NUMBER)
IS
l_sysdate DATE;
SELECT sysdate INTO l_sysdate
FROM DUAL;
QA_PC_PLAN_REL_PKG.Insert_Row(
X_Rowid => x_row_id,
X_Plan_Relationship_Id =>x_plan_relationship_id,
X_Parent_Plan_Id => p_parent_plan_id,
X_Child_Plan_id => p_child_plan_id,
X_Plan_Relationship_Type => p_plan_relationship_type,
X_Data_Entry_Mode => p_data_entry_mode,
X_Layout_Mode => p_layout_mode,
X_Auto_Row_Count => p_auto_row_count,
X_Default_Parent_Spec => l_default_parent_spec,
X_Last_Update_Date =>l_sysdate,
X_Last_Updated_By => p_last_updated_by,
X_Creation_Date =>l_sysdate,
X_Created_By => p_created_by,
X_Last_Update_Login => p_last_update_login);
END insert_plan_rel;
PROCEDURE insert_element_rel_chk(p_parent_char_id NUMBER,
p_child_char_id NUMBER,
p_relationship_type NUMBER,
x_status OUT NOCOPY VARCHAR2) IS
l_error_array ErrorTable;
SELECT datatype
FROM qa_chars
WHERE char_id = c_char_id;
l_error_array.delete;
END insert_element_rel_chk;
PROCEDURE insert_element_rel(
p_plan_relationship_id NUMBER,
p_parent_char_id NUMBER,
p_child_char_id NUMBER,
p_element_relationship_type NUMBER,
p_link_flag VARCHAR2,
p_last_updated_by NUMBER := fnd_global.user_id,
p_created_by NUMBER := fnd_global.user_id,
p_last_update_login NUMBER := fnd_global.user_id,
x_element_relationship_id OUT NOCOPY NUMBER) IS
l_sysdate DATE;
SELECT sysdate INTO l_sysdate
FROM DUAL;
QA_PC_ELEMENT_REL_PKG.Insert_Row(
X_Rowid => l_row_id,
X_Element_Relationship_Id => x_element_relationship_id,
X_Plan_Relationship_Id =>p_plan_relationship_id,
X_Parent_Char_id => p_parent_char_id,
X_Child_Char_id => p_child_char_id,
X_Element_Relationship_Type=>p_element_relationship_type,
X_Link_Flag=>l_link_flag,
X_Last_Update_Date=>l_sysdate,
X_Last_Updated_By => p_last_updated_by,
X_Creation_Date =>l_sysdate,
X_Created_By => p_created_by,
X_Last_Update_Login => p_last_update_login);
END insert_element_rel;
PROCEDURE insert_criteria_rel(p_plan_relationship_id NUMBER,
p_char_id NUMBER,
p_operator NUMBER,
p_low_value VARCHAR2,
-- p_low_value_id NUMBER,
p_high_value VARCHAR2,
-- p_high_value_id NUMBER,
p_last_updated_by NUMBER := fnd_global.user_id,
p_created_by NUMBER := fnd_global.user_id,
p_last_update_login NUMBER := fnd_global.user_id,
x_criteria_id OUT NOCOPY NUMBER) IS
l_sysdate DATE;
SELECT sysdate INTO l_sysdate
FROM DUAL;
QA_PC_CRITERIA_PKG.Insert_Row(
X_Rowid => l_row_id,
X_Criteria_Id => x_criteria_id,
X_Plan_Relationship_Id =>p_plan_relationship_id,
X_Char_id => p_char_id,
X_Operator => p_operator,
X_Low_Value => p_low_value,
X_Low_Value_Id => null,
X_High_Value => p_high_value,
X_High_Value_Id => null,
X_Last_Update_Date=>l_sysdate,
X_Last_Updated_By => p_last_updated_by,
X_Creation_Date =>l_sysdate,
X_Created_By => p_created_by,
X_Last_Update_Login => p_last_update_login);
END insert_criteria_rel;
PROCEDURE update_plan_rel(
-- p_rowid VARCHAR2,
p_plan_relationship_id NUMBER,
p_parent_plan_id NUMBER,
p_child_plan_id NUMBER,
p_plan_relationship_type NUMBER,
p_data_entry_mode NUMBER,
p_layout_mode NUMBER,
p_auto_row_count NUMBER,
p_default_parent_spec VARCHAR2,
p_last_updated_by NUMBER:=fnd_global.user_id,
p_created_by NUMBER:=fnd_global.user_id,
p_last_update_login NUMBER:=fnd_global.user_id
) IS
l_sysdate DATE;
select rowid
from qa_pc_plan_relationship
where plan_relationship_id = p_plan_relationship_id;
SELECT sysdate INTO l_sysdate
FROM DUAL;
QA_PC_PLAN_REL_PKG.Update_Row(
X_Rowid => l_rowid,
X_Plan_Relationship_Id => p_plan_relationship_id,
X_Parent_Plan_Id => p_parent_plan_id,
X_Child_Plan_id => p_child_plan_id,
X_Plan_Relationship_Type => p_plan_relationship_type,
X_Data_Entry_Mode => p_data_entry_mode,
X_Layout_Mode => p_layout_mode,
X_Auto_Row_Count => p_auto_row_count,
X_Default_Parent_Spec => l_default_parent_spec,
X_Last_Update_Date => l_sysdate,
X_Last_Updated_By => p_last_updated_by,
X_Creation_Date => l_sysdate,
X_Created_By => p_created_by,
X_Last_Update_Login => p_last_update_login );
END update_plan_rel;
PROCEDURE update_element_rel(
p_element_relationship_id NUMBER,
p_plan_relationship_id NUMBER,
p_parent_char_id NUMBER,
p_child_char_id NUMBER,
p_element_relationship_type NUMBER,
p_link_flag VARCHAR2,
p_last_updated_by NUMBER := fnd_global.user_id,
p_created_by NUMBER := fnd_global.user_id,
p_last_update_login NUMBER := fnd_global.user_id,
p_row_id VARCHAR2) IS
l_sysdate DATE;
SELECT sysdate INTO l_sysdate
FROM DUAL;
QA_PC_ELEMENT_REL_PKG.Update_Row(
X_Rowid => p_row_id,
X_Element_Relationship_Id => p_element_relationship_id,
X_Plan_Relationship_Id =>p_plan_relationship_id,
X_Parent_Char_id => p_parent_char_id,
X_Child_Char_id => p_child_char_id,
X_Element_Relationship_Type=>p_element_relationship_type,
X_Link_Flag=>l_link_flag,
X_Last_Update_Date=>l_sysdate,
X_Last_Updated_By => p_last_updated_by,
X_Creation_Date =>l_sysdate,
X_Created_By => p_created_by,
X_Last_Update_Login => p_last_update_login);
END update_element_rel;
PROCEDURE update_criteria_rel(
p_rowid VARCHAR2,
p_plan_relationship_id NUMBER,
p_char_id NUMBER,
p_operator NUMBER,
p_low_value VARCHAR2,
p_high_value VARCHAR2,
p_last_updated_by NUMBER := fnd_global.user_id,
p_created_by NUMBER := fnd_global.user_id,
p_last_update_login NUMBER := fnd_global.user_id,
p_criteria_id NUMBER) IS
l_sysdate DATE;
SELECT sysdate INTO l_sysdate
FROM DUAL;
QA_PC_CRITERIA_PKG.Update_Row(
X_Rowid => p_rowid,
X_Criteria_Id => p_criteria_id,
X_Plan_Relationship_Id =>p_plan_relationship_id,
X_Char_id => p_char_id,
X_Operator => p_operator,
X_Low_Value => p_low_value,
X_Low_Value_Id => null,
X_High_Value => p_high_value,
X_High_Value_Id => null,
X_Last_Update_Date=>l_sysdate,
X_Last_Updated_By => p_last_updated_by,
X_Creation_Date =>l_sysdate,
X_Created_By => p_created_by,
X_Last_Update_Login => p_last_update_login);
END update_criteria_rel;
PROCEDURE delete_element_rel(p_element_relationship_id NUMBER) IS
BEGIN
--QA_PC_CRITERIA_PKG.Delete_Row(X_Rowid => p_rowid);
DELETE FROM QA_PC_ELEMENT_RELATIONSHIP
WHERE element_relationship_id = p_element_relationship_id;
END delete_element_rel;
PROCEDURE delete_criteria(p_criteria_id NUMBER) IS
BEGIN
--QA_PC_CRITERIA_PKG.Delete_Row(X_Rowid => p_rowid);
DELETE FROM QA_PC_CRITERIA
WHERE criteria_id = p_criteria_id;
END delete_criteria;
SELECT 1
FROM qa_pc_plan_relationship
WHERE parent_plan_id = c_child_plan_id;
SelectFromClause VARCHAR2(20000);
SelectFromClause :=
qa_results_interface_pkg.get_plan_vqr_sql (p_plan_id);
RETURN SelectFromClause || WhereClause;
SelectFromClause VARCHAR2(20000);
SelectFromClause :=
qa_results_interface_pkg.get_plan_vqr_sql (p_child_plan_id);
|| ' AND (COLLECTION_ID, OCCURRENCE) IN (SELECT CHILD_COLLECTION_ID, CHILD_OCCURRENCE FROM QA_PC_RESULTS_RELATIONSHIP WHERE PARENT_OCCURRENCE = ' || p_parent_occurrence || ' AND CHILD_PLAN_ID = ' || p_child_plan_id || ' ) ';
RETURN SelectFromClause || WhereClause;
SelectFromClause VARCHAR2(20000);
SelectFromClause :=
qa_results_interface_pkg.get_plan_vqr_sql (p_parent_plan_id);
RETURN SelectFromClause || WhereClause;
PROCEDURE delete_plan_rel(p_plan_relationship_id NUMBER) IS
BEGIN
DELETE FROM QA_PC_PLAN_RELATIONSHIP
WHERE plan_relationship_id = p_plan_relationship_id;
DELETE FROM QA_PC_ELEMENT_RELATIONSHIP
WHERE plan_relationship_id = p_plan_relationship_id;
DELETE FROM QA_PC_CRITERIA
WHERE plan_relationship_id = p_plan_relationship_id;
END delete_plan_rel;
select distinct qpr.parent_plan_id
from qa_pc_plan_relationship qpr, qa_plans qp
where qpr.parent_plan_id = qp.plan_id
and qp.organization_id = p_org_id;