[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT /*+ ordered use_nl(qp) */ qpt.plan_transaction_id
FROM qa_plan_transactions qpt,
qa_plans qp
WHERE qpt.transaction_number = p_txn_number AND
qpt.plan_id = qp.plan_id AND
qpt.enabled_flag = 1 AND
qp.organization_id = p_org_id AND
trunc(sysdate) BETWEEN
nvl(trunc(qp.effective_from), trunc(sysdate)) AND
nvl(trunc(qp.effective_to), trunc(sysdate));
select_stmt VARCHAR2(10000);
select_stmt :=
'SELECT DISTINCT qpt.plan_id' ||
' FROM qa_plan_transactions qpt' ||
' WHERE qpt.plan_transaction_id IN ' ||
'( SELECT id FROM qa_performance_temp ' ||
' WHERE key=''QAGTXNB.COMMIT_ALLOWED'' ) '||
' AND qpt.enabled_flag = 1' ||
' AND qpt.mandatory_collection_flag = 1' ||
' AND qpt.background_collection_flag = 2' ||
' AND NOT EXISTS' ||
' (SELECT 1' ||
' FROM qa_results qr ' ||
' WHERE qr.plan_id = qpt.plan_id ' ||
' AND qr.collection_id = :c)';
OPEN c_plans_results FOR select_stmt USING p_collection_id;
SELECT qa_collection_id_s.nextval INTO l_coll_id FROM dual;
SELECT qpct.operator,
qpct.Low_Value,
qpct.High_Value ,
qc.datatype,
qc.char_id
FROM qa_plan_collection_triggers qpct,
qa_chars qc
WHERE qpct.Collection_Trigger_ID = qc.char_id and
qpct.plan_transaction_id = p_plan_txn_id) LOOP
IF NOT elements.EXISTS(plan_record.char_id) THEN
RETURN 'F';
SELECT qpt.plan_transaction_id
FROM qa_plan_transactions qpt, qa_plans qp
WHERE qpt.transaction_number = p_txn_number
AND qpt.plan_id = qp.plan_id
AND qp.organization_id = p_org_id
AND trunc(sysdate) between
nvl(trunc(qp.effective_from), trunc(sysdate)) and
nvl(trunc(qp.effective_to), trunc(sysdate))
AND qpt.enabled_flag = 1) LOOP
IF triggers_matched(pt.plan_transaction_id, elements) = 'T' THEN
plan_txn_list := plan_txn_list || ',' || pt.plan_transaction_id;
PROCEDURE evaltriggers_InsertRes_eamtxn(
p_txn_number IN NUMBER,
p_org_id IN NUMBER,
p_context_values IN VARCHAR2,
p_plans_tab IN QA_PARENT_CHILD_PKG.ChildPlanArray,
p_collection_id IN NUMBER) IS
elements ElementsArray;
insert_results(p_plans_tab(plan_txn_id), p_org_id, p_collection_id, elements);
END evaltriggers_InsertRes_eamtxn;
PROCEDURE insert_results(
p_plan_id IN NUMBER,
p_org_id IN NUMBER,
p_collection_id IN NUMBER,
elements IN ElementsArray) IS
uid NUMBER := fnd_global.user_id;
l_insert_columns VARCHAR2(10000);
l_insert_values VARCHAR2(10000);
l_insert_columns :=
'INSERT INTO qa_results ' ||
' (status, plan_id, organization_id, collection_id, occurrence,' ||
' last_update_date, qa_last_update_date, ' ||
' creation_date, qa_creation_date, ' ||
' last_updated_by, qa_last_updated_by, ' ||
' created_by, qa_created_by ';
l_insert_values :=
' VALUES(1, :c1, :c2, :c3, qa_occurrence_s.nextval,' ||
' sysdate, sysdate,' ||
' sysdate, sysdate,' ||
' :c4, :c5,' ||
' :c6, :c7';
SELECT qpc.char_id, qpc.result_column_name, qc.datatype,
NVL(qpc.default_value, qc.default_value) default_value
FROM qa_plan_chars qpc, qa_chars qc
WHERE plan_id = p_plan_id AND qpc.char_id = qc.char_id) LOOP
IF elements.EXISTS(c.char_id) THEN
l_insert_columns := l_insert_columns || ',' || c.result_column_name;
l_insert_values := l_insert_values || ',' ||
fmt(elements(c.char_id).value, c.datatype, c.result_column_name);
l_insert_columns := l_insert_columns || ',' || c.result_column_name;
l_insert_values := l_insert_values || ', '''
|| QA_SEQUENCE_API.get_sequence_default_value
|| '''';
l_insert_columns := l_insert_columns || ',' || c.result_column_name;
l_insert_values := l_insert_values || ', '''
|| c.default_value
|| '''';
l_insert_columns := l_insert_columns || ')';
l_insert_values := l_insert_values || ')';
EXECUTE IMMEDIATE l_insert_columns || l_insert_values
USING p_plan_id, p_org_id, p_collection_id, uid, uid, uid, uid;
'QA_TXN_GRP.INSERT_RESULTS.err', l_insert_columns || l_insert_values );
END insert_results;
select_stmt VARCHAR2(10000);
select_stmt :=
'SELECT DISTINCT qpt.plan_id' ||
' FROM qa_plan_transactions qpt' ||
' WHERE qpt.plan_transaction_id IN ' ||
'( SELECT id FROM qa_performance_temp ' ||
' WHERE key=''QAGTXNB.POST_BACKGROUND_RESULTS'' ) '||
' AND qpt.enabled_flag = 1' ||
' AND qpt.background_collection_flag = 1' ||
' AND NOT EXISTS ' ||
' (SELECT 1 ' ||
' FROM qa_results qr ' ||
' WHERE qr.plan_id = qpt.plan_id ' ||
' AND qr.collection_id = :c)';
OPEN c FOR select_stmt USING p_collection_id;
insert_results(l_plan_id, p_org_id, p_collection_id, elements);
SELECT DISTINCT qpt.plan_id plan_id,
qpt.plan_transaction_id plan_txn_id
FROM qa_plan_transactions qpt, qa_plans qp
WHERE qpt.transaction_number = txn_no
AND qpt.plan_id = qp.plan_id
AND qp.organization_id = org_id
AND trunc(sysdate) between
nvl(trunc(qp.effective_from), trunc(sysdate)) and
nvl(trunc(qp.effective_to), trunc(sysdate))
AND qpt.enabled_flag = 1
AND qpt.background_collection_flag = 1
AND NOT EXISTS
(SELECT 1
FROM qa_results qr
WHERE qr.plan_id = qpt.plan_id
AND qr.collection_id = col_id);
evaltriggers_InsertRes_eamtxn(p_txn_number => p_txn_number ,
p_org_id => p_org_id,
p_context_values => p_context_values,
p_plans_tab => plan_id_tab,
p_collection_id => p_collection_id);
s := 'SELECT name FROM qa_plans WHERE plan_id IN ' ||
'( SELECT id FROM qa_performance_temp ' ||
' WHERE key=''QAGTXNB.GET_PLAN_NAMES'' ) ';
select plan_id, occurrence
from qa_results
where collection_id = col_id;
SELECT char_id
from qa_plan_chars
where plan_id = p_plan_id
and enabled_flag = 1;
SELECT qpc.char_id,
NVL(qpc.default_value, qc.default_value) default_value,
qc.datatype
from qa_plan_chars qpc, qa_chars qc
where qpc.plan_id = p_plan_id
and qpc.char_id = qc.char_id
and qpc.enabled_flag = 1;
SELECT qa_occurrence_s.nextval
FROM DUAL;
SELECT DISTINCT qpt.plan_id
FROM qa_plan_transactions qpt
WHERE qpt.plan_transaction_id IN
( SELECT id FROM qa_performance_temp
WHERE key='QAGTXNB.SSQR_POST_BACKGROUND_RESULTS' )
AND qpt.enabled_flag = 1
AND qpt.background_collection_flag = 1
AND NOT EXISTS (SELECT 1
FROM qa_results qr
WHERE qr.plan_id = qpt.plan_id
AND qr.collection_id = c_collection_id);
PROCEDURE insert_child_results
(
p_plan_id IN NUMBER,
p_org_id IN NUMBER,
p_collection_id IN NUMBER,
p_occurrence IN NUMBER,
p_relationship_type IN NUMBER,
p_data_entry_mode IN NUMBER,
p_txn_header_id IN NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_CHILD_RESULTS';
QA_PARENT_CHILD_PKG.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_org_id,
p_spec_id => null,
x_status => l_return_status,
p_txn_header_id => p_txn_header_id
);
END insert_child_results;
SELECT QR.plan_id,
QP.name,
QR.occurrence
BULK COLLECT INTO
l_plans,
l_plan_names,
l_occurrences
FROM QA_RESULTS QR,
QA_PLANS QP
WHERE QP.plan_id = QR.plan_id
AND QR.collection_id = p_collection_id;
SELECT plan_id,
occurrence
BULK COLLECT INTO
l_plan_ids,
l_occurrences
FROM QA_RESULTS
WHERE collection_id = p_collection_id;
'BEFORE INSERTING AUTOMATIC RECORDS FOR PLAN_ID: ' || l_plan_ids(i) || ' OCCURRENCE: ' || l_occurrences(i)
);
insert_child_results
(
p_plan_id => l_plan_ids(i),
p_org_id => p_org_id,
p_collection_id => p_collection_id,
p_occurrence => l_occurrences(i),
p_relationship_type => 1,
p_data_entry_mode => 2, -- Automatic
p_txn_header_id => p_txn_header_id
);
'BEFORE INSERTING HISTORY RECORDS FOR PLAN_ID: ' || l_plan_ids(i) || ' OCCURRENCE: ' || l_occurrences(i)
);
insert_child_results
(
p_plan_id => l_plan_ids(i),
p_org_id => p_org_id,
p_collection_id => p_collection_id,
p_occurrence => l_occurrences(i),
p_relationship_type => 1,
p_data_entry_mode => 4, -- History
p_txn_header_id => p_txn_header_id
);
UPDATE qa_results
SET status = 2
WHERE collection_id = p_collection_id;
DELETE qa_results
WHERE collection_id = p_collection_id;
'DELETED ' || l_result_count || ' ROWS FROM QA_RESULTS'
);
DELETE qa_pc_results_relationship
WHERE parent_collection_id = p_collection_id
OR child_collection_id = p_collection_id;
'DELETED ' || SQL%ROWCOUNT || ' ROWS FROM QA_PC_RESULTS_RELATIONSHIP'
);
SELECT plan_id,
occurrence
BULK COLLECT INTO
l_plan_ids,
l_occurrences
FROM QA_RESULTS
WHERE collection_id = p_collection_id;
'BEFORE INSERTING AUTOMATIC RECORDS FOR PLAN_ID: ' || l_plan_ids(i) || ' OCCURRENCE: ' || l_occurrences(i)
);
insert_child_results
(
p_plan_id => l_plan_ids(i),
p_org_id => p_org_id,
p_collection_id => p_collection_id,
p_occurrence => l_occurrences(i),
p_relationship_type => 1,
p_data_entry_mode => 2, -- Automatic
p_txn_header_id => p_txn_header_id
);
'BEFORE INSERTING HISTORY RECORDS FOR PLAN_ID: ' || l_plan_ids(i) || ' OCCURRENCE: ' || l_occurrences(i)
);
insert_child_results
(
p_plan_id => l_plan_ids(i),
p_org_id => p_org_id,
p_collection_id => p_collection_id,
p_occurrence => l_occurrences(i),
p_relationship_type => 1,
p_data_entry_mode => 4, -- History
p_txn_header_id => p_txn_header_id
);
UPDATE qa_results
SET status = 2
WHERE collection_id = p_collection_id;
UPDATE qa_results
SET status=2
WHERE collection_id = p_collection_id;