[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'T' FROM qa_pc_results_relationship
WHERE parent_plan_id = p_plan_id
AND parent_collection_id = p_collection_id
AND parent_occurrence = p_occurrence
AND child_plan_id = c_child_plan_id
AND rownum =1;
SELECT child_plan_id, child_collection_id, child_occurrence
BULK COLLECT INTO
x_plan_ids, x_collection_ids, x_occurrences
FROM qa_pc_results_relationship r
WHERE EXISTS (
SELECT 1
FROM qa_results qr
WHERE qr.plan_id = r.child_plan_id AND
qr.collection_id = r.child_collection_id AND
qr.occurrence = r.child_occurrence AND
(qr.status IS NULL or qr.status=2) )
START WITH parent_plan_id = p_plan_id AND
parent_collection_id = p_collection_id AND
parent_occurrence = p_occurrence
CONNECT BY PRIOR child_occurrence = parent_occurrence;
CURSOR c IS SELECT qpr.plan_relationship_id,qpr.child_plan_id
FROM qa_plans qp,
qa_pc_plan_relationship qpr
WHERE qpr.parent_plan_id = p_plan_id
AND qpr.child_plan_id = qp.plan_id
AND qpr.plan_relationship_type = 1
AND qpr.data_entry_mode in (1,2,3)
AND ((qp.effective_to IS NULL AND TRUNC(SYSDATE) >= qp.effective_from)
OR (qp.effective_from IS NULL AND TRUNC(SYSDATE) <= qp.effective_to)
OR (qp.effective_from IS NOT NULL AND qp.effective_to IS NOT NULL
AND TRUNC(SYSDATE) BETWEEN qp.effective_from AND qp.effective_to)
OR (qp.effective_from IS NULL AND qp.effective_to IS NULL ));
The name is eval_updateview_lov_criteria and NOT evaluate_updateview_lov_criteria
because there's a character limit for length of function name in package.
*/
FUNCTION eval_updateview_lov_criteria( p_plan_id IN NUMBER,
p_criteria_values IN VARCHAR2,
x_child_plan_ids OUT NOCOPY VARCHAR2)
RETURN VARCHAR2 IS
---
--- This function finds all the matching child plan for the current plan.
--- First converts the values passed through p_criteria_values
--- into array. For each child plan we checking for the criteria values
--- by calling another function 'criteria_matched'.
--- We will concatenate all the child id into string with separator as ','
--- Return true if any matching child availabe with the concatenated
--- child plan_id's otherwise return false.
---
-- Bug 2448888. when all child plans have effective from and to date range is
-- outside the sysdate then, FRM-41084:- Error getting Group Cell raised when
-- child button is hit. This is similar to bug Bug 2355817.
-- Make a join to qa_plans in cursor C and fetch only effective child plans.
-- rponnusa Tue Jul 9 00:25:19 PDT 2002
CURSOR c IS SELECT qpr.plan_relationship_id,qpr.child_plan_id
FROM qa_plans qp,
qa_pc_plan_relationship qpr
WHERE qpr.parent_plan_id = p_plan_id
AND qpr.child_plan_id = qp.plan_id
AND qpr.plan_relationship_type = 1
AND qpr.data_entry_mode in (1,2,3,4)
AND ((qp.effective_to IS NULL AND TRUNC(SYSDATE) >= qp.effective_from)
OR (qp.effective_from IS NULL AND TRUNC(SYSDATE) <= qp.effective_to)
OR (qp.effective_from IS NOT NULL AND qp.effective_to IS NOT NULL
AND TRUNC(SYSDATE) BETWEEN qp.effective_from AND qp.effective_to)
OR (qp.effective_from IS NULL AND qp.effective_to IS NULL ));
END eval_updateview_lov_criteria;
CURSOR c IS SELECT qpc.char_id,qpc.operator,qpc.low_value,qpc.high_value,qc.datatype
FROM qa_pc_criteria qpc ,qa_chars qc
WHERE qpc.plan_relationship_id = p_plan_relationship_id
AND qpc.char_id = qc.char_id;
CURSOR c IS SELECT plan_relationship_id,child_plan_id
FROM qa_pc_plan_relationship
WHERE parent_plan_id = p_plan_id
AND plan_relationship_type = p_relationship_type
AND data_entry_mode = p_data_entry_mode;
SELECT plan_id
FROM qa_plans
WHERE plan_id = l_child_plan_id
AND ((effective_to IS NULL AND TRUNC(SYSDATE) >= effective_from)
OR (effective_from IS NULL AND TRUNC(SYSDATE) <= effective_to)
OR (effective_from IS NOT NULL AND effective_to IS NOT NULL
AND TRUNC(SYSDATE) BETWEEN effective_from AND effective_to)
OR (effective_from IS NULL AND effective_to IS NULL ));
PROCEDURE insert_automatic_records(p_plan_id IN NUMBER,
p_collection_id IN NUMBER,
p_occurrence IN NUMBER,
p_child_plan_ids IN VARCHAR2,
p_relationship_type IN NUMBER,
p_data_entry_mode IN NUMBER,
p_criteria_values IN VARCHAR2,
p_org_id IN NUMBER,
p_spec_id IN NUMBER,
x_status OUT NOCOPY VARCHAR2,
p_txn_header_id IN NUMBER) IS
parent_values_array qa_txn_grp.ElementsArray;
SELECT auto_row_count
FROM qa_pc_plan_relationship
WHERE parent_plan_id = p_plan_id
AND child_plan_id = c_child_plan_id;
SELECT parent_char_id, child_char_id
FROM qa_pc_result_columns_v
WHERE parent_plan_id = p_plan_id
AND child_plan_id = c_child_plan_id
AND parent_enabled_flag = 1
AND child_enabled_flag = 1;
SELECT qc.char_id
FROM qa_plan_chars qpc, qa_chars qc
WHERE qpc.plan_id = c_child_plan_id
AND qpc.char_id = qc.char_id
AND qpc.enabled_flag = 1
AND qc.datatype = 5
MINUS
SELECT child_char_id
FROM qa_pc_result_columns_v
WHERE parent_plan_id = p_plan_id
AND child_plan_id = c_child_plan_id
AND parent_enabled_flag = 1
AND child_enabled_flag = 1;
SELECT qc.char_id
FROM qa_plan_chars qpc,
qa_chars qc
WHERE qpc.plan_id = c_child_plan_id
AND qpc.char_id = qc.char_id
AND qpc.enabled_flag = 1
AND qc.datatype = 5
AND qc.char_id NOT IN
(SELECT child_char_id
FROM qa_pc_result_columns_v
WHERE parent_plan_id = p_plan_id
AND child_plan_id = c_child_plan_id
AND parent_enabled_flag = 1
AND child_enabled_flag = 1);
QA_PC_RESULTS_REL_PKG.Insert_Row(
X_Rowid => l_rowid,
X_Parent_Plan_Id => p_plan_id,
X_Parent_Collection_Id => p_collection_id,
X_Parent_Occurrence => p_occurrence,
X_Child_Plan_Id => l_child_plan_id,
X_Child_Collection_Id => p_collection_id,
X_Child_Occurrence => l_occurrence,
X_Enabled_Flag => 1,
X_Last_Update_Date => l_sysdate,
X_Last_Updated_By => fnd_global.user_id,
X_Creation_Date => l_sysdate,
X_Created_By => fnd_global.user_id,
X_Last_Update_Login => fnd_global.user_id,
X_Child_Txn_Header_Id => p_txn_header_id);
SELECT MAX(occurrence)
into l_child_occurrence
FROM qa_results
WHERE plan_id = l_child_plan_id and
collection_id = p_collection_id and
organization_id = p_org_id and
txn_header_id = p_txn_header_id;
insert_history_auto_rec_QWB(p_plan_id => l_child_plan_id,
p_collection_id => p_collection_id,
p_occurrence => l_child_occurrence,
p_organization_id => p_org_id,
p_txn_header_id => p_txn_header_id,
p_relationship_type => 1,
p_data_entry_mode => 2 ,
x_status => auto_hist_proc_stat);
insert_history_auto_rec_QWB(p_plan_id => l_child_plan_id,
p_collection_id => p_collection_id,
p_occurrence => l_child_occurrence,
p_organization_id => p_org_id,
p_txn_header_id => p_txn_header_id,
p_relationship_type => 1,
p_data_entry_mode => 4 ,
x_status => auto_hist_proc_stat);
/* Bug 3223081 : Added the following statement to reset the l_count to 1 after all the rows are inserted for one child plan
l_count :=1;
SELECT 'T'
FROM qa_pc_results_relationship
WHERE parent_occurrence = p_occurrence
AND rownum = 1;
SELECT child_plan_id, child_collection_id, child_occurrence
BULK COLLECT INTO
x_plan_ids, x_collection_ids, x_occurrences
FROM qa_pc_results_relationship r
WHERE EXISTS (
SELECT 1
FROM qa_results qr
WHERE qr.plan_id = r.child_plan_id AND
qr.collection_id = r.child_collection_id AND
qr.occurrence = r.child_occurrence AND
qr.status = 1 )
START WITH parent_plan_id = p_plan_id AND
parent_collection_id = p_collection_id AND
parent_occurrence = p_occurrence
CONNECT BY PRIOR child_occurrence = parent_occurrence;
PROCEDURE delete_child_rows(p_plan_ids IN dbms_sql.number_table,
p_collection_ids IN dbms_sql.number_table,
p_occurrences IN dbms_sql.number_table,
p_parent_plan_id NUMBER ,
p_parent_collection_id NUMBER ,
p_parent_occurrence NUMBER ,
p_enabled_flag VARCHAR2)
IS
---
--- The following procedure takes in plan_id, collection id and occurrece and
--- deletes these rows from QA_RESULTS. It also deletes entry for these rows
--- from relationships tables.
---
--- p_enabled_flag holds 'T' => delete only enabled child records
--- 'F' => delete only disabled child records
i INTEGER ;
DELETE from QA_RESULTS
WHERE plan_id = p_plan_ids(i)
AND collection_id = p_collection_ids(i)
AND occurrence = p_occurrences(i);
DELETE from QA_PC_RESULTS_RELATIONSHIP
WHERE child_occurrence = p_occurrences(i);
END delete_child_rows;
UPDATE qa_results
SET status = 2
WHERE txn_header_id = p_txn_header_id;
UPDATE qa_results
SET status = 2
WHERE txn_header_id = p_txn_header_id
and status =1
RETURNING plan_id, collection_id, occurrence
BULK COLLECT INTO plan_id_tab, collection_id_tab, occurrence_tab;
select_clause VARCHAR2(80) := NULL;
SELECT plan_relationship_id,parent_plan_id
FROM qa_pc_plan_relationship
WHERE child_plan_id = p_child_plan_id
AND rownum = 1;
select pe.parent_char_id,
qpc1.result_column_name parent_database_column,
pe.child_char_id,
qpc2.result_column_name child_database_column
from
qa_pc_plan_relationship pr,
qa_pc_element_relationship pe,
qa_plan_chars qpc1,
qa_plan_chars qpc2
where
pr.plan_relationship_id = pe.plan_relationship_id and
pr.parent_plan_id = qpc1.plan_id and
pe.parent_char_id = qpc1.char_id and
pr.child_plan_id = qpc2.plan_id and
pe.child_char_id = qpc2.char_id and
pe.plan_relationship_id = p_relationship_id and
pe.element_relationship_type = 1 and
pe.link_flag = 1;
select_clause := ' SELECT 1, plan_id, collection_id, occurrence ';
fnd_dsql.add_text(select_clause || from_clause || ' ');
select_clause := ' SELECT ' || ele_rec.child_database_column;
query_clause := select_clause || from_clause || where_clause;
SELECT txn_header_id FROM qa_results
WHERE plan_id = p_child_plan_id AND
collection_id = p_child_collection_id AND
occurrence = p_child_occurrence;
QA_PC_RESULTS_REL_PKG.Insert_Row(
X_Rowid => l_rowid,
X_Parent_Plan_Id => p_parent_plan_id,
X_Parent_Collection_Id => p_parent_collection_id,
X_Parent_Occurrence => p_parent_occurrence,
X_Child_Plan_Id => p_child_plan_id,
X_Child_Collection_Id => p_child_collection_id,
X_Child_Occurrence => p_child_occurrence,
X_Enabled_Flag => 1,
X_Last_Update_Date => l_date,
X_Last_Updated_By => l_user_id,
X_Creation_Date => l_date,
X_Created_By => l_user_id,
X_Last_Update_Login => l_login_id,
X_Child_Txn_Header_Id => l_child_txn_header_id);
l_ret_value := QA_PARENT_CHILD_PKG.update_parent(p_parent_plan_id ,
p_parent_collection_id ,
p_parent_occurrence,
p_child_plan_id,
p_child_collection_id ,
p_child_occurrence,
agg_elements,
agg_val,
p_ssqr_operation);
l_ret_value:= QA_PARENT_CHILD_PKG.update_child(p_parent_plan_id ,
p_parent_collection_id ,
p_parent_occurrence,
p_child_plan_id,
p_child_collection_id ,
p_child_occurrence );
SELECT name
FROM qa_plans
WHERE plan_id = c_plan_id;
SELECT default_parent_spec
FROM qa_pc_plan_relationship
WHERE parent_plan_id = p_parent_plan_id
AND child_plan_id = p_child_plan_id;
SELECT 'T'
FROM qa_pc_plan_relationship
WHERE parent_plan_id = p_plan_id
OR child_plan_id = p_plan_id
AND rownum = 1;
l_update_parent_sql VARCHAR2(32000);
l_select_sql VARCHAR2(32000);
SELECT parent_database_column,
child_database_column,
element_relationship_type,
parent_char_id
FROM qa_pc_result_columns_v
WHERE parent_plan_id = p_parent_plan_id
AND child_plan_id = p_child_plan_id
AND element_relationship_type in (2,3,4,5,6,7,8)
AND parent_enabled_flag = 1
AND child_enabled_flag = 1;
l_sql_string := 'SELECT SUM(to_number(qr.'||cur_rec.child_database_column||')) ' || l_sql_string;
l_sql_string := 'SELECT AVG(to_number(qr.'||cur_rec.child_database_column||')) ' || l_sql_string;
l_sql_string := 'SELECT STDDEV(to_number(qr.'|| cur_rec.child_database_column||')) ' || l_sql_string;
l_sql_string := 'SELECT MIN(to_number(qr.'|| cur_rec.child_database_column||')) ' || l_sql_string;
l_sql_string := 'SELECT MAX(to_number(qr.'|| cur_rec.child_database_column||')) ' || l_sql_string;
l_sql_string := 'SELECT VARIANCE(to_number(qr.'|| cur_rec.child_database_column||')) ' || l_sql_string;
l_sql_string := 'SELECT COUNT(qr.'|| cur_rec.child_database_column||') ' || l_sql_string;
l_select_sql := 'SELECT '
|| cur_rec.parent_database_column
|| ' FROM qa_results WHERE plan_id = :p_parent_plan_id'
|| ' AND collection_id= :p_parent_collection_id'
|| ' AND occurrence= :p_parent_occurrence FOR UPDATE NOWAIT';
EXECUTE IMMEDIATE l_select_sql INTO l_parent_db_col
USING p_parent_plan_id,p_parent_collection_id,p_parent_occurrence;
l_update_parent_sql := 'UPDATE qa_results SET '
|| cur_rec.parent_database_column || ' = :l_value'
|| ' WHERE plan_id= :p_parent_plan_id'
|| ' AND collection_id= :p_parent_collection_id'
|| ' AND occurrence= :p_parent_occurrence';
EXECUTE IMMEDIATE l_update_parent_sql
USING l_value,p_parent_plan_id,p_parent_collection_id,p_parent_occurrence;
FUNCTION update_parent(p_parent_plan_id IN NUMBER,
p_parent_collection_id IN NUMBER,
p_parent_occurrence IN NUMBER,
p_child_plan_id IN NUMBER,
p_child_collection_id IN NUMBER,
p_child_occurrence IN NUMBER)
RETURN VARCHAR2 IS
-- 12.1 QWB Usability Improvements
agg_elements VARCHAR2(4000);
return update_parent(p_parent_plan_id,
p_parent_collection_id,
p_parent_occurrence,
p_child_plan_id,
p_child_collection_id,
p_child_occurrence,
agg_elements,
agg_val);
FUNCTION update_parent(p_parent_plan_id IN NUMBER,
p_parent_collection_id IN NUMBER,
p_parent_occurrence IN NUMBER,
p_child_plan_id IN NUMBER,
p_child_collection_id IN NUMBER,
p_child_occurrence IN NUMBER,
x_agg_elements OUT NOCOPY VARCHAR2,
x_agg_val OUT NOCOPY VARCHAR2,
p_ssqr_operation IN NUMBER DEFAULT NULL)
RETURN VARCHAR2 IS
l_return_status VARCHAR2(1);
END update_parent;
PROCEDURE DATE_SELECT_UPDATE(p_parent_result_column IN VARCHAR2,
p_child_result_column IN VARCHAR2,
p_parent_plan_id IN NUMBER,
p_child_plan_id IN NUMBER,
p_var IN NUMBER,
p_select_column OUT NOCOPY VARCHAR2,
p_update_column OUT NOCOPY VARCHAR2)
IS
-- Cursor to check if the resultcolumn is
-- of the DateTime type and whether its a Hardcoded
-- element
Cursor cur (p_plan_id in NUMBER, p_res_col in VARCHAR2) is
Select 1, qc.hardcoded_column
from qa_plan_chars qpc, qa_chars qc
where qpc.plan_id = p_plan_id
and qpc.char_id = qc.char_id
and qpc.result_column_name = p_res_col
and qc.datatype = 6;
p_select_column := 'to_char('||p_parent_result_column||',''DD-MON-YYYY HH24:MI:SS'') ';
p_update_column := 'to_date(:'||to_char(p_var)||',''DD-MON-YYYY HH24:MI:SS'') ';
p_select_column := 'QLTDATE.date_to_canon_dt('||p_parent_result_column||') ';
p_update_column := ':'||to_char(p_var);
p_select_column := p_parent_result_column;
p_update_column := 'qltdate.canon_to_date(:'||to_char(p_var)||') ';
p_select_column := p_parent_result_column;
p_update_column := ':'||to_char(p_var);
END DATE_SELECT_UPDATE;
FUNCTION perform_child_update(p_parentchild_element_tab IN QA_PARENT_CHILD_PKG.g_parentchild_elementtab_type,
p_parent_plan_id IN NUMBER,
p_parent_collection_id IN NUMBER,
p_parent_occurrence IN NUMBER,
p_child_plan_id IN NUMBER,
p_child_collection_id IN NUMBER,
p_child_occurrence IN NUMBER)
RETURN VARCHAR2 IS
l_sql_string VARCHAR2(32000) := NULL;
l_update_clause VARCHAR2(32000) := NULL;
select_column varchar2(2000);
update_column varchar2(2000);
DATE_SELECT_UPDATE(p_parent_result_column => UPPER(p_parentchild_element_tab(element_cntr).parent_database_column),
p_child_result_column => UPPER(p_parentchild_element_tab(element_cntr).child_database_column),
p_parent_plan_id => p_parent_plan_id,
p_child_plan_id => p_child_plan_id,
p_var => l_var,
p_select_column => select_column,
p_update_column => update_column);
l_update_clause := l_update_clause || l_comma;
|| 'replace(' || NVL(select_column, p_parentchild_element_tab(element_cntr).parent_database_column) || ', ''@'', ''@@'')';
|| 'replace(' || NVL(select_column, p_parentchild_element_tab(element_cntr).parent_database_column) || ', ''@'', ''@@'')';
l_sql_string := 'SELECT ' || p_parentchild_element_tab(element_cntr).parent_database_column
|| ' FROM qa_results '
|| ' WHERE plan_id= :p_parent_plan_id'
|| ' AND collection_id= :p_parent_collection_id'
|| ' AND occurrence= :p_parent_occurrence';
l_sql_string := 'SELECT ' || NVL(select_column, p_parentchild_element_tab(element_cntr).parent_database_column)
|| ' FROM qa_results '
|| ' WHERE plan_id= :p_parent_plan_id'
|| ' AND collection_id= :p_parent_collection_id'
|| ' AND occurrence= :p_parent_occurrence';
l_update_clause := l_update_clause ||
p_parentchild_element_tab(element_cntr).child_database_column ||
' = :'||to_char(l_var);
l_update_clause := l_update_clause ||
p_parentchild_element_tab(element_cntr).child_database_column ||
' = '||NVL(update_column, ':'||to_char(l_var));
IF( l_update_clause IS NULL) THEN
-- this will happen only if the element_cursor does not fetch any records.
RETURN 'T';
l_sql_string := 'Select ' || l_sql_string
|| ' FROM qa_results '
|| ' WHERE plan_id= :p_parent_plan_id'
|| ' AND collection_id= :p_parent_collection_id'
|| ' AND occurrence= :p_parent_occurrence';
l_update_clause := 'UPDATE qa_results SET ' || l_update_clause
|| ' WHERE plan_id= :p_child_plan_id'
|| ' AND collection_id= :p_child_collection_id'
|| ' AND occurrence= :p_child_occurrence';
dbms_sql.parse(c1, l_update_clause, dbms_sql.native);
END perform_child_update;
FUNCTION update_child(p_parent_plan_id IN NUMBER,
p_parent_collection_id IN NUMBER,
p_parent_occurrence IN NUMBER,
p_child_plan_id IN NUMBER,
p_child_collection_id IN NUMBER,
p_child_occurrence IN NUMBER)
RETURN VARCHAR2 IS
-- the following cursor contains sql text used for the view
-- qa_pc_result_columns_v. I added link_flag in where clause.
-- anagarwa Mon Dec 16 16:55:09 PST 2002
-- Bug 2701777
-- added parent_enabled_flag and child_enabled_flag to where clause
-- to limit working on onlly those elements that are enabled.
CURSOR element_cursor IS
SELECT qprc.parent_database_column,
qprc.child_database_column
FROM
qa_pc_result_columns_v qprc
WHERE
qprc.parent_plan_id = p_parent_plan_id and
qprc.child_plan_id = p_child_plan_id and
qprc.element_relationship_type = 1 and
parent_enabled_flag = 1 and
child_enabled_flag = 1;
SELECT qpc.char_id,
qpc.result_column_name
FROM qa_chars qc,
qa_plan_chars qpc
WHERE qpc.plan_id = p_child_plan_id
AND qpc.char_id NOT IN
(SELECT child_char_id
FROM qa_pc_element_relationship qper,
qa_pc_plan_relationship qppr
WHERE qper.plan_relationship_id = qppr.plan_relationship_id
AND qppr.parent_plan_id = p_parent_plan_id
AND qppr.child_plan_id = p_child_plan_id
AND qppr.data_entry_mode = 2)
AND qpc.char_id = qc.char_id
AND qc.datatype =5;
l_ret_val := perform_child_update
(p_parentchild_element_tab => l_element_cursor_tab,
p_parent_plan_id => p_parent_plan_id ,
p_parent_collection_id => p_parent_collection_id,
p_parent_occurrence => p_parent_occurrence,
p_child_plan_id => p_child_plan_id ,
p_child_collection_id => p_child_collection_id,
p_child_occurrence => p_child_occurrence);
END update_child;
SELECT qprc.parent_database_column parent_database_column,
qprc.child_database_column child_database_column
FROM
qa_pc_result_columns_v qprc
WHERE
qprc.parent_plan_id = p_parent_plan_id and
qprc.child_plan_id = p_child_plan_id and
qprc.element_relationship_type = 1 and
parent_enabled_flag = 1 and
child_dataType <> 5 and
child_enabled_flag = 1 and
parent_database_column = p_parentdbcol ;
SELECT qprc.parent_database_column parent_database_column,
qprc.child_database_column child_database_column
BULK COLLECT INTO p_elements_tab
FROM
qa_pc_result_columns_v qprc
WHERE
qprc.parent_plan_id = p_parent_plan_id and
qprc.child_plan_id = p_child_plan_id and
qprc.element_relationship_type = 1 and
parent_enabled_flag = 1 and
child_dataType <> 5 and
child_enabled_flag = 1 and
parent_dataType = 5 ;
FUNCTION update_sequence_child(p_ParentChild_Tab IN QA_PARENT_CHILD_PKG.ParentChildTabTyp)
RETURN VARCHAR2 IS
Type Num_tab_Typ is table of NUMBER INDEX BY BINARY_INTEGER;
l_ret_val := perform_child_update
(p_parentchild_element_tab => l_elements_toprocess_tab,
p_parent_plan_id => l_ParentChild_Tab(cntr).parent_plan_id,
p_parent_collection_id => l_ParentChild_Tab(cntr).parent_collection_id,
p_parent_occurrence => l_ParentChild_Tab(cntr).parent_occurrence,
p_child_plan_id => l_ParentChild_Tab(cntr).child_plan_id,
p_child_collection_id => l_ParentChild_Tab(cntr).child_collection_id,
p_child_occurrence => l_ParentChild_Tab(cntr).child_occurrence);
l_elements_toprocess_tab.DELETE;
END update_sequence_child;
By selecting form_field instead of database_column we can fix it.
However, item, comp_item, locator and comp_locator don't exist in
QA_RESULTS_V. So we add special handling for these later.
*/
--SELECT char_id,database_column
SELECT char_id, replace(form_field, 'DISPLAY' , 'CHARACTER') database_column,
datatype
FROM qa_pc_plan_columns_v
WHERE plan_id = p_parent_plan_id;
select_clause VARCHAR2(32000);
/*select_clause := 'SELECT ' || column_name;
query_clause := select_clause || from_clause || where_clause;
select_clause := 'SELECT ' || l_string;
query_clause := select_clause || from_clause || where_clause;
PROCEDURE insert_history_auto_rec(p_parent_plan_id IN NUMBER,
p_txn_header_id IN NUMBER,
p_relationship_type IN NUMBER,
p_data_entry_mode IN NUMBER) IS
CURSOR plan_cur IS
SELECT 1
FROM qa_pc_plan_relationship
WHERE parent_plan_id = p_parent_plan_id
AND plan_relationship_type = p_relationship_type
AND data_entry_mode = p_data_entry_mode;
SELECT collection_id,occurrence,organization_id
FROM qa_results
WHERE plan_id = p_parent_plan_id
AND txn_header_id = p_txn_header_id;
FOR c1 in (SELECT mtl_material_transactions_s.nextval txn_header_id FROM DUAL) LOOP
l_child_txn_header_id := c1.txn_header_id;
insert_automatic_records(p_parent_plan_id,
import_rec.collection_id,
import_rec.occurrence,
l_child_plan_ids,
p_relationship_type,
p_data_entry_mode,
l_criteria_values,
import_rec.organization_id,
l_spec_id,
x_status,
l_child_txn_header_id);
END insert_history_auto_rec;
SELECT 1
FROM qa_results
WHERE plan_id = p_plan_id
AND collection_id = p_collection_id
AND occurrence = p_occurrence
AND status = 2;
FUNCTION update_all_children(p_parent_plan_id IN NUMBER,
p_parent_collection_id IN NUMBER,
p_parent_occurrence IN NUMBER)
RETURN VARCHAR2 IS
l_return_value VARCHAR2(1);
select qprr.child_plan_id,
qprr.child_collection_id,
qprr.child_occurrence
from qa_pc_results_relationship qprr,
qa_pc_plan_relationship qpr
where qprr.parent_occurrence = p_parent_occurrence
and qprr.parent_plan_id = p_parent_plan_id
and qprr.parent_collection_id = p_parent_collection_id
and qpr.parent_plan_id = qprr.parent_plan_id
and qpr.child_plan_id = qprr.child_plan_id
and qpr.data_entry_mode <> 4;
update_child ( p_parent_plan_id,
p_parent_collection_id,
p_parent_occurrence,
children_rec.child_plan_id,
children_rec.child_collection_id,
children_rec.child_occurrence);
update_all_children(children_rec.child_plan_id,
children_rec.child_collection_id,
children_rec.child_occurrence);
END update_all_children;
SELECT qpr.plan_relationship_id,
qpr.child_plan_id,
qpr.data_entry_mode
FROM qa_plans qp,
qa_pc_plan_relationship qpr
WHERE qpr.parent_plan_id = p_plan_id
AND qpr.child_plan_id = qp.plan_id
AND qpr.plan_relationship_type = 1
AND ((qp.effective_to IS NULL AND TRUNC(SYSDATE) >= qp.effective_from)
OR (qp.effective_from IS NULL AND TRUNC(SYSDATE) <= qp.effective_to)
OR (qp.effective_from IS NOT NULL AND qp.effective_to IS NOT NULL
AND TRUNC(SYSDATE) BETWEEN qp.effective_from AND qp.effective_to)
OR (qp.effective_from IS NULL AND qp.effective_to IS NULL));
CURSOR c IS SELECT 1
FROM qa_pc_result_columns_v
WHERE child_plan_id = p_plan_id and
child_char_id = p_char_id and
parent_plan_id = p_parent_plan_id and
ELEMENT_RELATIONSHIP_TYPE = 1;
CURSOR c IS SELECT parent_char_id
FROM qa_pc_result_columns_v
WHERE parent_plan_id = p_plan_id and
child_char_id = p_child_char_id and
element_relationship_type = 1;
SELECT layout_mode
FROM qa_pc_plan_relationship
WHERE parent_plan_id = p_parent_plan_id
AND child_plan_id = p_child_plan_id;
FUNCTION count_updated(p_plan_id IN NUMBER,
p_txn_header_id IN NUMBER) RETURN NUMBER IS
---
--- Bug 3095436: Self Service Quality project
--- Simple function to count the number of rows updated in a plan
--- with a particular txn_header_id
--- Used by the Plan Search VO
---
cnt NUMBER;
select count(plan_id)
from qa_results
where plan_id = p_plan_id
and txn_header_id = p_txn_header_id;
END count_updated;
(qa_web_txn_api.allowed_for_plan('QA_RESULTS_DELETE', p_plan_id) = 'T') or
(qa_web_txn_api.allowed_for_plan('QA_RESULTS_UPDATE', p_plan_id) = 'T') THEN
RETURN 'T';
FUNCTION update_parent(p_parent_plan_id IN NUMBER,
p_parent_collection_id IN NUMBER,
p_parent_occurrence IN NUMBER,
p_child_plan_id IN NUMBER,
p_child_collection_id IN NUMBER,
p_child_occurrence IN NUMBER,
p_child_txn_hdr_id IN NUMBER)
RETURN VARCHAR2 IS
agg_elements VARCHAR2(4000);
return update_parent(
p_parent_plan_id,
p_parent_collection_id,
p_parent_occurrence,
p_child_plan_id,
p_child_collection_id,
p_child_occurrence,
p_child_txn_hdr_id,
agg_elements,
agg_val);
END update_parent;
FUNCTION update_parent(p_parent_plan_id IN NUMBER,
p_parent_collection_id IN NUMBER,
p_parent_occurrence IN NUMBER,
p_child_plan_id IN NUMBER,
p_child_collection_id IN NUMBER,
p_child_occurrence IN NUMBER,
p_child_txn_hdr_id IN NUMBER,
x_agg_elements OUT NOCOPY VARCHAR2,
x_agg_val OUT NOCOPY VARCHAR2)
RETURN VARCHAR2 IS
l_sql_string VARCHAR2(32000);
l_update_parent_sql VARCHAR2(32000);
SELECT parent_database_column,
child_database_column,
element_relationship_type,
parent_char_id
FROM qa_pc_result_columns_v
WHERE parent_plan_id = p_parent_plan_id
AND child_plan_id = p_child_plan_id
AND element_relationship_type in (2,3,4,5,6,7,8)
AND parent_enabled_flag = 1
AND child_enabled_flag = 1;
l_sql_string := 'SELECT SUM(to_number(qr.'||cur_rec.child_database_column||')) ' || l_sql_string;
l_sql_string := 'SELECT AVG(to_number(qr.'||cur_rec.child_database_column||')) ' || l_sql_string;
l_sql_string := 'SELECT STDDEV(to_number(qr.'|| cur_rec.child_database_column||')) ' || l_sql_string;
l_sql_string := 'SELECT MIN(to_number(qr.'|| cur_rec.child_database_column||')) ' || l_sql_string;
l_sql_string := 'SELECT MAX(to_number(qr.'|| cur_rec.child_database_column||')) ' || l_sql_string;
l_sql_string := 'SELECT VARIANCE(to_number(qr.'|| cur_rec.child_database_column||')) ' || l_sql_string;
l_sql_string := 'SELECT COUNT(qr.'|| cur_rec.child_database_column||') ' || l_sql_string;
l_update_parent_sql := 'UPDATE qa_results SET '
|| cur_rec.parent_database_column || ' = :l_value'
|| ' ,txn_header_id = :p_child_txn_hdr_id'
|| ' WHERE plan_id= :p_parent_plan_id'
|| ' AND collection_id= :p_parent_collection_id'
|| ' AND occurrence= :p_parent_occurrence';
EXECUTE IMMEDIATE l_update_parent_sql
USING l_value,p_child_txn_hdr_id,p_parent_plan_id,p_parent_collection_id,p_parent_occurrence;
END update_parent;
PROCEDURE insert_history_auto_rec_QWB(p_plan_id IN NUMBER,
p_collection_id IN NUMBER,
p_occurrence IN NUMBER,
p_organization_id IN NUMBER,
p_txn_header_id IN NUMBER,
p_relationship_type IN NUMBER,
p_data_entry_mode IN NUMBER,
x_status OUT NOCOPY VARCHAR2) IS
CURSOR child_check_cur(c_plan_id NUMBER) IS
SELECT 'T'
FROM qa_pc_plan_relationship
WHERE parent_plan_id = c_plan_id
AND plan_relationship_type = p_relationship_type
AND data_entry_mode = p_data_entry_mode;
insert_automatic_records(p_plan_id => p_plan_id,
p_collection_id => p_collection_id,
p_occurrence => p_occurrence,
p_child_plan_ids => l_child_plan_ids,
p_relationship_type => p_relationship_type,
p_data_entry_mode => p_data_entry_mode,
p_criteria_values => l_criteria_values,
p_org_id => p_organization_id,
p_spec_id => null,
x_status => l_status,
p_txn_header_id => p_txn_header_id);
END insert_history_auto_rec_QWB;
PROCEDURE DELETE_RELATIONSHIP_ROW(p_child_plan_id IN NUMBER,
p_child_occurrence IN NUMBER) IS
BEGIN
DELETE FROM qa_pc_results_relationship
WHERE child_plan_id = p_child_plan_id
AND child_occurrence = p_child_occurrence;
FUNCTION delete_row(
p_plan_id IN NUMBER,
p_collection_id IN NUMBER,
p_occurrence IN NUMBER,
p_enabled IN NUMBER := NULL) RETURN VARCHAR2
IS
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_ROW';
SELECT parent_plan_id,
parent_collection_id,
parent_occurrence
FROM QA_PC_RESULTS_RELATIONSHIP
WHERE child_plan_id = p_plan_id
AND child_collection_id = p_collection_id
AND child_occurrence = p_occurrence;
DELETE QA_RESULTS
WHERE occurrence = p_occurrence
AND plan_id = p_plan_id
AND collection_id = p_collection_id;
'DELETED ROW IN QA RESULTS. GETTING PARENT'
);
delete_relationship_row
(
p_child_plan_id => p_plan_id,
p_child_occurrence => p_occurrence
);
END delete_row;
INSERT INTO QA_PC_RESULTS_RELATIONSHIP (PARENT_PLAN_ID,
PARENT_COLLECTION_ID,
PARENT_OCCURRENCE,
CHILD_PLAN_ID,
CHILD_COLLECTION_ID,
CHILD_OCCURRENCE,
ENABLED_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
CHILD_TXN_HEADER_ID)
SELECT p_parent_plan_id,
p_parent_collection_id,
p_parent_occurrence,
QR.PLAN_ID,
QR.COLLECTION_ID,
QR.OCCURRENCE,
2,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
QR.TXN_HEADER_ID
FROM QA_RESULTS QR
WHERE QR.PLAN_ID = p_child_plan_id
AND QR.COLLECTION_ID = p_child_collection_id
AND QR.ORGANIZATION_ID = p_org_id;
SELECT qprc.parent_database_column,
qprc.child_database_column
FROM qa_pc_result_columns_v qprc
WHERE qprc.parent_plan_id = p_parent_plan_id and
qprc.child_plan_id = p_child_plan_id and
qprc.element_relationship_type = 1 and
parent_enabled_flag = 1 and
child_enabled_flag = 1;
l_sql_string := ' UPDATE QA_RESULTS ' ||
' SET ( ' || l_dest_string || ' ) = ' ||
' ( SELECT ' || l_src_string || ' ' ||
' FROM QA_RESULTS QR1 ' ||
' WHERE QR1.PLAN_ID = :1 ' ||
' AND QR1.COLLECTION_ID = :2' ||
' AND QR1.OCCURRENCE = :3) ' ||
' WHERE PLAN_ID = :4 ' ||
' AND COLLECTION_ID = :5 ';
SELECT child_plan_id
FROM qa_pc_plan_relationship
WHERE parent_plan_id = x_plan_id
AND data_entry_mode = 4;
INSERT INTO QA_PC_RESULTS_RELATIONSHIP (
PARENT_PLAN_ID,
PARENT_COLLECTION_ID,
PARENT_OCCURRENCE,
CHILD_PLAN_ID ,
CHILD_COLLECTION_ID,
CHILD_OCCURRENCE,
ENABLED_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY ,
LAST_UPDATE_LOGIN,
CHILD_TXN_HEADER_ID)
SELECT QR.PLAN_ID,
QR.COLLECTION_ID,
QR.OCCURRENCE,
hst_rec.child_plan_id,
p_collection_id,
QA_OCCURRENCE_S.NEXTVAL,
2,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
p_txn_header_id
FROM QA_RESULTS QR
WHERE QR.PLAN_ID = p_plan_id
AND QR.COLLECTION_ID = p_collection_id
AND QR.ORGANIZATION_ID = p_org_id;
l_sql_string := ' INSERT INTO qa_results ( collection_id, ' ||
' occurrence, ' ||
' last_update_date, ' ||
' qa_last_update_date, '||
' last_updated_by, ' ||
' qa_last_updated_by, ' ||
' creation_date, ' ||
' qa_creation_date, ' ||
' created_by, ' ||
' last_update_login, ' ||
' qa_created_by, ' ||
' status, ' ||
' transaction_number, ' ||
' organization_id, ' ||
' plan_id, ' ||
' txn_header_id, ' ||
l_dest_string || ')' ||
' SELECT QPRR.CHILD_COLLECTION_ID, ' ||
' QPRR.CHILD_OCCURRENCE, ' ||
' sysdate, ' ||
' sysdate, ' ||
' fnd_global.user_id, ' ||
' fnd_global.user_id, ' ||
' sysdate, ' ||
' sysdate, ' ||
' fnd_global.user_id, ' ||
' fnd_global.user_id, ' ||
' fnd_global.user_id, ' ||
' 2, ' ||
' -1, ' ||
' QR.ORGANIZATION_ID, ' ||
' QPRR.CHILD_PLAN_ID, ' ||
' QPRR.CHILD_TXN_HEADER_ID, ' ||
l_src_string || ' ' ||
' FROM QA_RESULTS QR, QA_PC_RESULTS_RELATIONSHIP QPRR ' ||
' WHERE QPRR.CHILD_PLAN_ID = :1 ' ||
' AND QPRR.CHILD_COLLECTION_ID = :2 ' ||
' AND QPRR.PARENT_PLAN_ID = :3 ' ||
' AND QPRR.PARENT_COLLECTION_ID = :4 ' ||
' AND QPRR.PARENT_OCCURRENCE = QR.OCCURRENCE ';
SELECT parent_plan_id, parent_collection_id, parent_occurrence
BULK COLLECT INTO x_parent_plan_ids, x_parent_collection_ids, x_parent_occurrences
FROM qa_pc_results_relationship
START WITH child_plan_id = p_child_plan_id
AND child_occurrence = p_child_occurrence
AND child_collection_id = p_child_collection_id
CONNECT BY PRIOR parent_occurrence = child_occurrence;
PROCEDURE update_all_ancestors(p_parent_plan_id IN NUMBER,
p_parent_collection_id IN NUMBER,
p_parent_occurrence IN NUMBER) IS
l_parent_plan_id_tab DBMS_SQL.NUMBER_TABLE;
IF(QA_PARENT_CHILD_PKG.update_parent
(l_current_parent_planid,
l_current_parent_collid,
l_current_parent_occrid,
l_current_child_planid,
l_current_child_collid,
l_current_child_occrid)='T')
THEN
NULL;
END update_all_ancestors;
PROCEDURE delete_invalid_children(p_txn_header_id IN NUMBER) IS
PRAGMA AUTONOMOUS_TRANSACTION;
DELETE FROM qa_results
WHERE txn_header_id = p_txn_header_id
AND status = 1
RETURNING plan_id, collection_id, occurrence
BULK COLLECT INTO child_plan_id_tab,
child_collection_id_tab,
child_occurrence_tab;
DELETE from qa_pc_results_relationship
WHERE child_txn_header_id = p_txn_header_id
AND child_plan_id = child_plan_id_tab(cntr)
AND child_collection_id = child_collection_id_tab(cntr)
AND child_occurrence = child_occurrence_tab(cntr);
END delete_invalid_children;
SELECT char_id, result_column_name
BULK COLLECT INTO res_col_tab
FROM qa_plan_chars
WHERE plan_id = p_plan_id;
'Select '''||str||
' from qa_results where plan_id = :plan_id and
collection_id = :collection_id and
occurrence = :occurrence'
INTO result_string USING p_plan_id,
p_collection_id ,
p_occurrence;
FUNCTION child_exists_for_update(p_plan_id IN NUMBER,
p_collection_id IN NUMBER,
p_occurrence IN NUMBER)
RETURN VARCHAR2 AS
--
-- removed the Immediate plans check
-- ntungare
--
CURSOR cur is
select 'UPDATE_CHILD_Y'
from qa_pc_results_relationship qpc,
qa_results qr,
qa_pc_plan_relationship qpr
where qpc.parent_plan_id = p_plan_id and
qpc.parent_collection_id = p_collection_id and
qpc.parent_occurrence = p_occurrence and
qpc.child_plan_id = qr.plan_id and
qpc.child_collection_id = qr.collection_id and
qpc.child_occurrence = qr.occurrence and
(qr.status = 2 or qr.status is NULL) and
qpr.parent_plan_id = p_plan_id and
qpr.child_plan_id = qpc.child_plan_id and
qpr.data_entry_mode <> 4 and
qa_web_txn_api.allowed_for_plan('QA_RESULTS_UPDATE', qpc.child_plan_id) = 'T';
has_child VARCHAR2(100) :='UPDATE_CHILD_N';
END child_exists_for_update;
SELECT count(*) INTO childCount
FROM qa_pc_results_relationship qpc,
qa_results qr
WHERE qpc.parent_plan_id = p_plan_id and
qpc.parent_collection_id = p_collection_id and
qpc.parent_occurrence = p_occurrence and
qpc.child_plan_id = qr.plan_id and
qpc.child_collection_id = qr.collection_id and
qpc.child_occurrence = qr.occurrence and
(qr.status = 2 or qr.status is NULL);
SELECT count(*) INTO childCount
FROM qa_pc_plan_relationship
WHERE parent_plan_id=p_plan_id;
FUNCTION update_hist_children(p_parent_plan_id IN NUMBER,
p_parent_collection_id IN NUMBER,
p_parent_occurrence IN NUMBER)
RETURN VARCHAR2 IS
l_return_value VARCHAR2(1);
select qprr.child_plan_id,
qprr.child_collection_id,
qprr.child_occurrence
from qa_pc_results_relationship qprr,
qa_pc_plan_relationship qpr
where qprr.parent_occurrence = p_parent_occurrence
and qprr.parent_plan_id = p_parent_plan_id
and qprr.parent_collection_id = p_parent_collection_id
and qpr.parent_plan_id = qprr.parent_plan_id
and qpr.child_plan_id = qprr.child_plan_id
and qpr.data_entry_mode = 4;
update_child ( p_parent_plan_id,
p_parent_collection_id,
p_parent_occurrence,
children_rec.child_plan_id,
children_rec.child_collection_id,
children_rec.child_occurrence);
END update_hist_children;
SELECT REPLACE(DECODE(QC.HARDCODED_COLUMN, NULL ,QAPC.RESULT_COLUMN_NAME,QC.DEVELOPER_NAME),
'CHARACTER','DISPLAY') FORM_FIELD
BULK COLLECT INTO result_column_name_tab
FROM qa_pc_plan_relationship qppr,
qa_pc_criteria qpc,
qa_results qr,
qa_plan_chars qapc,
qa_chars qc
WHERE qpc.plan_relationship_id = qppr.plan_relationship_id
AND qapc.char_id = qpc.char_id
AND qapc.char_id = qc.char_id
AND qr.occurrence = p_occurrence
AND qr.collection_id = p_collection_id
AND qr.plan_id = p_plan_id
AND qr.plan_id = qapc.plan_id
AND qppr.parent_plan_id = qr.plan_id
AND EXISTS
(SELECT 1 FROM
qa_pc_results_relationship qprr
WHERE qppr.child_plan_id = qprr.child_plan_id
AND qppr.parent_plan_id = qprr.parent_plan_id
AND qppr.child_plan_id = qprr.child_plan_id
AND qprr.parent_plan_id = qr.plan_id
AND qprr.parent_collection_id = p_collection_id
AND qprr.parent_occurrence = p_occurrence
AND qprr.parent_plan_id = p_plan_id
AND ROWNUM = 1);