[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT to_number(substr(result_column_name,10)) res_column_name
FROM qa_plan_chars
WHERE plan_id = p_plan_id AND
result_column_name like 'CHARACTER%'
ORDER BY to_number(substr(result_column_name,10));
SELECT
qpc.plan_id,
qpc.char_id,
qc.name char_name, -- bug 3926150 needs char_name
qpc.last_update_date,
qpc.last_updated_by,
qpc.creation_date,
qpc.created_by,
qpc.prompt_sequence,
qpc.prompt,
qpc.enabled_flag,
qpc.mandatory_flag,
qpc.read_only_flag,
qpc.ss_poplist_flag,
qpc.information_flag,
qpc.default_value,
qc.hardcoded_column,
qpc.result_column_name,
qpc.values_exist_flag,
qpc.displayed_flag,
qpc.attribute_category,
qpc.attribute1,
qpc.attribute2,
qpc.attribute3,
qpc.attribute4,
qpc.attribute5,
qpc.attribute6,
qpc.attribute7,
qpc.attribute8,
qpc.attribute9,
qpc.attribute10,
qpc.attribute11,
qpc.attribute12,
qpc.attribute13,
qpc.attribute14,
qpc.attribute15,
qpc.default_value_id
FROM
qa_plan_chars qpc,
qa_chars qc
WHERE
qpc.plan_id = p_from_plan_id AND
qpc.char_id IN (
SELECT id
FROM qa_performance_temp
WHERE key = p_char_id_key) AND
qpc.char_id NOT IN (
SELECT char_id
FROM qa_plan_chars
WHERE plan_id = p_to_plan_id) AND
qpc.char_id = qc.char_id
ORDER BY
qpc.prompt_sequence;
SELECT
PLAN_CHAR_ACTION_TRIGGER_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
TRIGGER_SEQUENCE,
PLAN_ID,
CHAR_ID,
OPERATOR,
LOW_VALUE_LOOKUP,
HIGH_VALUE_LOOKUP,
LOW_VALUE_OTHER,
HIGH_VALUE_OTHER,
LOW_VALUE_OTHER_ID,
HIGH_VALUE_OTHER_ID
FROM QA_PLAN_CHAR_ACTION_TRIGGERS
WHERE PLAN_ID = X_COPY_PLAN_ID;*/
SELECT a.plan_char_action_trigger_id,
a.last_update_date,
a.last_updated_by,
a.creation_date,
a.created_by,
a.trigger_sequence,
a.plan_id,
a.char_id,
a.operator,
a.low_value_lookup,
a.high_value_lookup,
a.low_value_other,
a.high_value_other,
a.low_value_other_id,
a.high_value_other_id
FROM qa_plan_char_action_triggers a
WHERE NOT EXISTS
(SELECT 1
FROM qa_plan_char_action_triggers b
WHERE b.plan_id = X_PLAN_ID
AND a.char_id = b.char_id
--AND nvl(a.trigger_sequence, 0) = nvl(b.trigger_sequence, 0)
AND nvl(a.operator, 0) = nvl(b.operator, 0)
AND nvl(a.low_value_lookup, 0) = nvl(b.low_value_lookup, 0)
AND nvl(b.low_value_other, 0) = decode(nvl(a.low_value_other, 0),
'ACCEPT',
(SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'ERT RESULTS ACTION'
AND LOOKUP_CODE = a.low_value_other),
'REJECT',
(SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'ERT RESULTS ACTION'
AND LOOKUP_CODE = a.low_value_other),
nvl(a.low_value_other, 0))
AND nvl(a.high_value_other, 0) = nvl(b.high_value_other, 0)
AND nvl(a.high_value_lookup, 0) = nvl(b.high_value_lookup, 0)
AND nvl(a.low_value_other_id, 0) = nvl(b.low_value_other_id, 0)
AND nvl(a.high_value_other_id, 0) = nvl(b.high_value_other_id, 0))
AND a.plan_id = X_COPY_PLAN_ID;
SELECT QA_PLAN_CHAR_ACTION_TRIGGERS_S.NEXTVAL FROM DUAL;
SELECT
PLAN_CHAR_ACTION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
PLAN_CHAR_ACTION_TRIGGER_ID,
ACTION_ID,
CAR_NAME_PREFIX,
CAR_TYPE_ID,
CAR_OWNER,
MESSAGE,
STATUS_CODE,
STATUS_ID,
ALR_ACTION_ID,
ALR_ACTION_SET_ID,
ASSIGNED_CHAR_ID,
ASSIGN_TYPE
FROM QA_PLAN_CHAR_ACTIONS
WHERE PLAN_CHAR_ACTION_TRIGGER_ID = QPCAT.PLAN_CHAR_ACTION_TRIGGER_ID
ORDER BY PLAN_CHAR_ACTION_ID;
SELECT QA_PLAN_CHAR_ACTIONS_S.NEXTVAL FROM DUAL;
SELECT a.plan_char_action_trigger_id temp_action_trigger_id,
b.plan_char_action_trigger_id action_trigger_id
FROM qa_plan_char_action_triggers a, qa_plan_char_action_triggers b
WHERE a.char_id = b.char_id
AND nvl(a.operator, 0) = nvl(b.operator, 0)
AND nvl(a.low_value_lookup, 0) = nvl(b.low_value_lookup, 0)
AND nvl(b.low_value_other, 0) = decode(nvl(a.low_value_other, 0),
'ACCEPT',
(SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'ERT RESULTS ACTION'
AND LOOKUP_CODE = a.low_value_other),
'REJECT',
(SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'ERT RESULTS ACTION'
AND LOOKUP_CODE = a.low_value_other),
nvl(a.low_value_other, 0))
AND nvl(a.high_value_other, 0) = nvl(b.high_value_other, 0)
AND nvl(a.high_value_lookup, 0) = nvl(b.high_value_lookup, 0)
AND nvl(a.low_value_other_id, 0) = nvl(b.low_value_other_id, 0)
AND nvl(a.high_value_other_id, 0) = nvl(b.high_value_other_id, 0)
AND b.plan_id = X_PLAN_ID
AND a.plan_id = X_COPY_PLAN_ID;
SELECT ACTION_ID,
CAR_NAME_PREFIX,
CAR_TYPE_ID,
CAR_OWNER,
MESSAGE,
STATUS_CODE,
STATUS_ID,
ALR_ACTION_ID,
ALR_ACTION_SET_ID,
ASSIGNED_CHAR_ID,
ASSIGN_TYPE
FROM qa_plan_char_actions a,
qa_plan_char_action_triggers c
WHERE NOT EXISTS
(SELECT 1
FROM qa_plan_char_actions b,
qa_plan_char_action_triggers d
WHERE nvl(a.action_id, 0) = nvl(b.action_id, 0)
AND nvl(a.car_name_prefix, 0) = nvl(b.car_name_prefix, 0)
AND nvl(a.car_type_id, 0) = nvl(b.car_type_id, 0)
AND nvl(a.car_owner, 0) = nvl(b.car_owner, 0)
AND nvl(a.message, 0) = nvl(b.message, 0)
AND nvl(a.status_code, 0) = nvl(b.status_code, 0)
AND nvl(a.alr_action_id, 0) = nvl(b.alr_action_id, 0)
AND nvl(a.alr_action_set_id, 0) = nvl(b.alr_action_set_id, 0)
AND nvl(a.assigned_char_id, 0) = nvl(b.assigned_char_id, 0)
AND nvl(a.assign_type, 0) = nvl(b.assign_type, 0)
AND d.plan_id = x_plan_id
AND b.plan_char_action_trigger_id = d.plan_char_action_trigger_id
AND b.plan_char_action_trigger_id = qpcat_temp.action_trigger_id)
AND a.plan_char_action_trigger_id = c.plan_char_action_trigger_id
AND c.plan_char_action_trigger_id = qpcat_temp.temp_action_trigger_id
AND c.plan_id = x_copy_plan_id;
SELECT MAX(PROMPT_SEQUENCE) FROM QA_PLAN_CHARS
WHERE PLAN_ID = X_PLAN_ID;
INSERT INTO QA_PLAN_CHAR_VALUE_LOOKUPS (
PLAN_ID,
CHAR_ID,
SHORT_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
DESCRIPTION,
SHORT_CODE_ID)
SELECT
X_PLAN_ID,
CHAR_ID,
SHORT_CODE,
SYSDATE,
X_USER_ID,
SYSDATE,
X_USER_ID,
DESCRIPTION,
SHORT_CODE_ID
FROM QA_PLAN_CHAR_VALUE_LOOKUPS
WHERE PLAN_ID = X_COPY_PLAN_ID
AND CHAR_ID NOT IN (SELECT CHAR_ID FROM QA_PLAN_CHARS
WHERE PLAN_ID = X_PLAN_ID);
/*DELETE FROM qa_plan_char_actions
WHERE action_id in (25, 26)
AND plan_char_action_trigger_id IN
(SELECT plan_char_action_trigger_id
FROM qa_plan_char_action_triggers
WHERE plan_id = X_Plan_id
AND char_id = 8
AND low_value_other IN
(SELECT displayed_field
FROM po_lookup_codes
WHERE lookup_type='ERT RESULTS ACTION'));
DELETE FROM qa_plan_char_action_triggers
WHERE plan_id = X_Plan_id
AND char_id = 8
AND low_value_other IN
(SELECT displayed_field
FROM po_lookup_codes
WHERE lookup_type='ERT RESULTS ACTION');*/
INSERT INTO QA_PLAN_CHAR_ACTIONS
(PLAN_CHAR_ACTION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
PLAN_CHAR_ACTION_TRIGGER_ID,
ACTION_ID,
CAR_NAME_PREFIX,
CAR_TYPE_ID,
CAR_OWNER,
MESSAGE,
STATUS_CODE,
STATUS_ID,
ASSIGNED_CHAR_ID,
ASSIGN_TYPE)
VALUES
(QPC_ACTION_ID,
SYSDATE,
X_USER_ID,
SYSDATE,
X_USER_ID,
QPCAT_TEMP.ACTION_TRIGGER_ID,
QPCA_TEMP.ACTION_ID,
QPCA_TEMP.CAR_NAME_PREFIX,
QPCA_TEMP.CAR_TYPE_ID,
QPCA_TEMP.CAR_OWNER,
QPCA_TEMP.MESSAGE,
QPCA_TEMP.STATUS_CODE,
QPCA_TEMP.STATUS_ID,
QPCA_TEMP.ASSIGNED_CHAR_ID,
QPCA_TEMP.ASSIGN_TYPE);
INSERT INTO QA_PLAN_CHAR_ACTION_TRIGGERS (
PLAN_CHAR_ACTION_TRIGGER_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
TRIGGER_SEQUENCE,
PLAN_ID,
CHAR_ID,
OPERATOR,
LOW_VALUE_LOOKUP,
HIGH_VALUE_LOOKUP,
LOW_VALUE_OTHER,
HIGH_VALUE_OTHER,
LOW_VALUE_OTHER_ID,
HIGH_VALUE_OTHER_ID)
VALUES (
ACTION_TRIGGER_ID,
SYSDATE,
X_USER_ID,
SYSDATE,
X_USER_ID,
QPCAT.TRIGGER_SEQUENCE,
X_PLAN_ID,
QPCAT.CHAR_ID,
QPCAT.OPERATOR,
QPCAT.LOW_VALUE_LOOKUP,
QPCAT.HIGH_VALUE_LOOKUP,
QPCAT.LOW_VALUE_OTHER,
QPCAT.HIGH_VALUE_OTHER,
QPCAT.LOW_VALUE_OTHER_ID,
QPCAT.HIGH_VALUE_OTHER_ID);
UPDATE QA_PLAN_CHAR_ACTION_TRIGGERS
SET LOW_VALUE_OTHER = (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'ERT RESULTS ACTION'
AND LOOKUP_CODE = QPCAT.LOW_VALUE_OTHER)
WHERE PLAN_CHAR_ACTION_TRIGGER_ID = ACTION_TRIGGER_ID;
INSERT INTO QA_PLAN_CHAR_ACTIONS (
PLAN_CHAR_ACTION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
PLAN_CHAR_ACTION_TRIGGER_ID,
ACTION_ID,
CAR_NAME_PREFIX,
CAR_TYPE_ID,
CAR_OWNER,
MESSAGE,
STATUS_CODE,
STATUS_ID,
ASSIGNED_CHAR_ID,
ASSIGN_TYPE)
VALUES (
QPC_ACTION_ID,
SYSDATE,
X_USER_ID,
SYSDATE,
X_USER_ID,
ACTION_TRIGGER_ID,
QPCA.ACTION_ID,
QPCA.CAR_NAME_PREFIX,
QPCA.CAR_TYPE_ID,
QPCA.CAR_OWNER,
QPCA.MESSAGE,
QPCA.STATUS_CODE,
QPCA.STATUS_ID,
QPCA.ASSIGNED_CHAR_ID,
QPCA.ASSIGN_TYPE
);
INSERT INTO QA_PLAN_CHARS (
plan_id,
char_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
prompt_sequence,
prompt,
enabled_flag,
mandatory_flag,
read_only_flag,
ss_poplist_flag,
information_flag,
default_value,
result_column_name,
values_exist_flag,
displayed_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
default_value_id)
VALUES (
X_PLAN_ID,
MIE.CHAR_ID,
SYSDATE,
X_USER_ID,
SYSDATE,
X_USER_ID,
NEW_PROMPT_SEQUENCE,
MIE.PROMPT,
MIE.ENABLED_FLAG,
MIE.MANDATORY_FLAG,
MIE.READ_ONLY_FLAG,
MIE.SS_POPLIST_FLAG,
MIE.INFORMATION_FLAG,
MIE.DEFAULT_VALUE,
NEW_RESULT_COLUMN_NAME,
MIE.VALUES_EXIST_FLAG,
MIE.DISPLAYED_FLAG,
MIE.ATTRIBUTE_CATEGORY,
MIE.ATTRIBUTE1,
MIE.ATTRIBUTE2,
MIE.ATTRIBUTE3,
MIE.ATTRIBUTE4,
MIE.ATTRIBUTE5,
MIE.ATTRIBUTE6,
MIE.ATTRIBUTE7,
MIE.ATTRIBUTE8,
MIE.ATTRIBUTE9,
MIE.ATTRIBUTE10,
MIE.ATTRIBUTE11,
MIE.ATTRIBUTE12,
MIE.ATTRIBUTE13,
MIE.ATTRIBUTE14,
MIE.ATTRIBUTE15,
MIE.DEFAULT_VALUE_ID);
INSERT INTO QA_PLAN_CHARS (
plan_id,
char_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
prompt_sequence,
prompt,
enabled_flag,
mandatory_flag,
read_only_flag,
ss_poplist_flag,
information_flag,
default_value,
result_column_name,
values_exist_flag,
displayed_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
default_value_id)
VALUES (
X_PLAN_ID,
QPCV.CHAR_ID,
SYSDATE,
X_USER_ID,
SYSDATE,
X_USER_ID,
NEW_PROMPT_SEQUENCE,
QPCV.PROMPT,
QPCV.ENABLED_FLAG,
QPCV.MANDATORY_FLAG,
QPCV.READ_ONLY_FLAG,
QPCV.SS_POPLIST_FLAG,
QPCV.INFORMATION_FLAG,
QPCV.DEFAULT_VALUE,
NEW_RESULT_COLUMN_NAME,
QPCV.VALUES_EXIST_FLAG,
QPCV.DISPLAYED_FLAG,
QPCV.ATTRIBUTE_CATEGORY,
QPCV.ATTRIBUTE1,
QPCV.ATTRIBUTE2,
QPCV.ATTRIBUTE3,
QPCV.ATTRIBUTE4,
QPCV.ATTRIBUTE5,
QPCV.ATTRIBUTE6,
QPCV.ATTRIBUTE7,
QPCV.ATTRIBUTE8,
QPCV.ATTRIBUTE9,
QPCV.ATTRIBUTE10,
QPCV.ATTRIBUTE11,
QPCV.ATTRIBUTE12,
QPCV.ATTRIBUTE13,
QPCV.ATTRIBUTE14,
QPCV.ATTRIBUTE15,
QPCV.DEFAULT_VALUE_ID);
SELECT MAX(prompt_sequence) FROM qa_plan_chars
WHERE plan_id = p_plan_id;
INSERT INTO qa_plan_chars(
plan_id,
char_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
prompt_sequence,
prompt,
enabled_flag,
mandatory_flag,
read_only_flag,
ss_poplist_flag,
information_flag,
default_value,
result_column_name,
values_exist_flag,
displayed_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
default_value_id)
VALUES (
p_plan_id,
qpcv.char_id,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
new_prompt_sequence,
qpcv.prompt,
qpcv.enabled_flag,
qpcv.mandatory_flag,
qpcv.read_only_flag,
qpcv.ss_poplist_flag,
qpcv.information_flag,
qpcv.default_value,
new_result_column_name,
qpcv.values_exist_flag,
qpcv.displayed_flag,
qpcv.attribute_category,
qpcv.attribute1,
qpcv.attribute2,
qpcv.attribute3,
qpcv.attribute4,
qpcv.attribute5,
qpcv.attribute6,
qpcv.attribute7,
qpcv.attribute8,
qpcv.attribute9,
qpcv.attribute10,
qpcv.attribute11,
qpcv.attribute12,
qpcv.attribute13,
qpcv.attribute14,
qpcv.attribute15,
qpcv.default_value_id);
SELECT MAX(prompt_sequence) FROM qa_plan_chars
WHERE plan_id = p_plan_id;
SELECT char_id,prompt,hardcoded_column
FROM qa_chars
WHERE char_id IN (l_asset_group_char_id,l_asset_number_char_id);
DELETE FROM qa_plan_chars
WHERE plan_id = p_plan_id
AND char_id IN (l_asset_group_char_id,l_asset_number_char_id);
INSERT INTO qa_plan_chars(
plan_id,
char_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
prompt_sequence,
prompt,
enabled_flag,
mandatory_flag,
result_column_name,
values_exist_flag,
displayed_flag,
read_only_flag,
ss_poplist_flag,
information_flag)
VALUES (
p_plan_id,
i.char_id,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
l_new_prompt_sequence,
i.prompt,
1,
1,
i.hardcoded_column,
2,
1,
2,
2,
2);
SELECT MAX(prompt_sequence) FROM qa_plan_chars
WHERE plan_id = x_plan_id;
SELECT char_id cid,prompt pro,hardcoded_column hc
FROM qa_chars
WHERE char_id = 150;
INSERT INTO qa_plan_chars(
plan_id,
char_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
prompt_sequence,
prompt,
enabled_flag,
mandatory_flag,
result_column_name,
values_exist_flag,
displayed_flag,
read_only_flag,
ss_poplist_flag,
information_flag)
VALUES (
x_plan_id,
LPN_REC.cid,
SYSDATE,
x_user_id,
SYSDATE,
x_user_id,
PROMPT_SEQ +10,
LPN_REC.pro,
1,
2,
LPN_REC.hc,
2,
1,
2,
2,
2);
SELECT MAX(prompt_sequence) FROM qa_plan_chars
WHERE plan_id = X_PLAN_ID;
SELECT char_id cid,prompt pro,hardcoded_column hc,enabled_flag,
mandatory_flag
FROM qa_chars
WHERE char_id in (SELECT char_id
FROM
qa_plan_chars qpc1
WHERE plan_id=X_COPY_PLAN_ID
AND enabled_flag =1
and not exists
( select 1
from
qa_plan_chars qpc2
where qpc2.plan_id=X_PLAN_ID
and qpc2.char_id = qpc1.char_id)
)
AND enabled_flag =1;
INSERT INTO qa_plan_chars(
plan_id,
char_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
prompt_sequence,
prompt,
enabled_flag,
mandatory_flag,
result_column_name,
values_exist_flag,
displayed_flag,
read_only_flag,
ss_poplist_flag,
information_flag)
VALUES (
x_plan_id,
i.cid,
SYSDATE,
x_user_id,
SYSDATE,
x_user_id,
PROMPT_SEQ,
i.pro,
i.enabled_flag,
i.mandatory_flag,
i.hc,
2,
1,
2,
2,
2);