[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_plan_chars (X_PLAN_ID NUMBER,
X_COPY_PLAN_ID NUMBER,
X_USER_ID NUMBER,
X_DISABLED_INDEXED_ELEMENTS OUT NOCOPY VARCHAR2) IS
--
-- Bug 3926150
--
l_disabled_indexed_elements VARCHAR2(3000);
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 AND
CHAR_ID NOT IN (SELECT CHAR_ID FROM QA_PLAN_CHARS
WHERE PLAN_ID = X_PLAN_ID)
ORDER BY TRIGGER_SEQUENCE,
PLAN_CHAR_ACTION_TRIGGER_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,
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
APPLICATION_ID,
ACTION_ID,
NAME,
ALERT_ID,
ACTION_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
END_DATE_ACTIVE,
ENABLED_FLAG,
DESCRIPTION,
ACTION_LEVEL_TYPE,
DATE_LAST_EXECUTED,
FILE_NAME,
ARGUMENT_STRING,
PROGRAM_APPLICATION_ID,
CONCURRENT_PROGRAM_ID,
LIST_APPLICATION_ID,
LIST_ID,
TO_RECIPIENTS,
CC_RECIPIENTS,
BCC_RECIPIENTS,
PRINT_RECIPIENTS,
PRINTER,
SUBJECT,
REPLY_TO,
RESPONSE_SET_ID,
FOLLOW_UP_AFTER_DAYS,
COLUMN_WRAP_FLAG,
MAXIMUM_SUMMARY_MESSAGE_WIDTH,
BODY,
VERSION_NUMBER
FROM ALR_ACTIONS
WHERE APPLICATION_ID = 250
AND ACTION_ID = QPCA.ALR_ACTION_ID;
SELECT
ALR_ACTIONS_S.NEXTVAL,
ALR_ACTION_SETS_S.NEXTVAL,
ALR_ACTION_SET_MEMBERS_S.NEXTVAL,
QA_ALR_ACTION_NAME_S.NEXTVAL,
QA_ALR_ACTION_SET_NAME_S.NEXTVAL
FROM DUAL;
SELECT
PLAN_CHAR_ACTION_ID,
CHAR_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
TOKEN_NAME
FROM QA_PLAN_CHAR_ACTION_OUTPUTS
WHERE PLAN_CHAR_ACTION_ID = QPCA.PLAN_CHAR_ACTION_ID
ORDER BY PLAN_CHAR_ACTION_ID;
SELECT
qpc.plan_id,
qpc.char_id,
qc.name char_name,
qc.datatype,
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,
-- 12.1 Device Integration Project.
-- Added device fields.
-- bhsankar Fri Oct 19 01:51:57 PDT 2007
qpc.device_flag,
qpc.device_id,
qpc.override_flag,
-- Device Integration Project End.
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,
nvl(qpc.decimal_precision, qc.decimal_precision) decimal_precision ,
nvl(qpc.uom_code, qc.uom_code) uom_code
FROM qa_plan_chars qpc,
qa_chars qc
WHERE qpc.plan_id = X_COPY_PLAN_ID
AND qc.char_id = qpc.char_id
AND qc.char_id not in
(SELECT char_id
FROM qa_plan_chars
WHERE plan_id = X_PLAN_ID )
ORDER BY prompt_sequence;
SELECT
PLAN_ID,
CHAR_ID,
CHAR_NAME, -- Bug 3926150 needed name.
DATATYPE,
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,
HARDCODED_COLUMN,
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,
DECIMAL_PRECISION,
UOM_CODE
FROM QA_PLAN_CHARS_V
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)
ORDER BY PROMPT_SEQUENCE;
SELECT TO_NUMBER(SUBSTR(QPC.RESULT_COLUMN_NAME,10,3)) RES_COLUMN_NAME
FROM QA_PLAN_CHARS QPC, QA_CHARS QC
WHERE PLAN_ID = X_PLAN_ID
AND qc.char_id = qpc.char_id
AND QC.HARDCODED_COLUMN IS NULL
AND QC.DATATYPE in (1,2,3,6)
ORDER BY TO_NUMBER(SUBSTR(QPC.RESULT_COLUMN_NAME,10,3));
SELECT TO_NUMBER(SUBSTR(RESULT_COLUMN_NAME,10,3)) RES_COLUMN_NAME
FROM QA_PLAN_CHARS_V
WHERE PLAN_ID = X_PLAN_ID
AND HARDCODED_COLUMN IS NULL
AND DATATYPE in (1,2,3,6)
ORDER BY TO_NUMBER(SUBSTR(RESULT_COLUMN_NAME,10,3));
SELECT TO_NUMBER(SUBSTR(RESULT_COLUMN_NAME,8,3)) RES_COLUMN_NAME
FROM QA_PLAN_CHARS QPC, QA_CHARS QC
WHERE QPC.PLAN_ID = X_PLAN_ID
and qc.char_id = qpc.char_id
AND QC.HARDCODED_COLUMN IS NULL
AND QC.DATATYPE = 4
ORDER BY TO_NUMBER(SUBSTR(QPC.RESULT_COLUMN_NAME,8,3));
SELECT TO_NUMBER(SUBSTR(RESULT_COLUMN_NAME,8,3)) RES_COLUMN_NAME
FROM QA_PLAN_CHARS_V
WHERE PLAN_ID = X_PLAN_ID
AND HARDCODED_COLUMN IS NULL
AND DATATYPE = 4
ORDER BY TO_NUMBER(SUBSTR(RESULT_COLUMN_NAME,8,3));
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,
CREATED_BY,
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);
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,
ALR_ACTION_ID,
ALR_ACTION_SET_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,
DECODE (QPCA.ACTION_ID,
10, NEW_ACTION_ID,
11, NEW_ACTION_ID,
12, NEW_ACTION_ID,
13, NEW_ACTION_ID,
NULL),
DECODE (QPCA.ACTION_ID,
10, NEW_ACTION_SET_ID,
11, NEW_ACTION_SET_ID,
12, NEW_ACTION_SET_ID,
13, NEW_ACTION_SET_ID,
NULL),
QPCA.ASSIGNED_CHAR_ID,
QPCA.ASSIGN_TYPE
);
INSERT INTO ALR_ACTIONS (
APPLICATION_ID,
ACTION_ID,
NAME,
ALERT_ID,
ACTION_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
END_DATE_ACTIVE,
ENABLED_FLAG,
DESCRIPTION,
ACTION_LEVEL_TYPE,
DATE_LAST_EXECUTED,
FILE_NAME,
ARGUMENT_STRING,
PROGRAM_APPLICATION_ID,
CONCURRENT_PROGRAM_ID,
LIST_APPLICATION_ID,
LIST_ID,
TO_RECIPIENTS,
CC_RECIPIENTS,
BCC_RECIPIENTS,
PRINT_RECIPIENTS,
PRINTER,
SUBJECT,
REPLY_TO,
RESPONSE_SET_ID,
FOLLOW_UP_AFTER_DAYS,
COLUMN_WRAP_FLAG,
MAXIMUM_SUMMARY_MESSAGE_WIDTH,
BODY,
VERSION_NUMBER)
VALUES (
ALRA.APPLICATION_ID,
NEW_ACTION_ID,
NEW_ACTION_NAME,
ALRA.ALERT_ID,
ALRA.ACTION_TYPE,
SYSDATE,
X_USER_ID,
SYSDATE,
X_USER_ID,
ALRA.END_DATE_ACTIVE,
ALRA.ENABLED_FLAG,
ALRA.DESCRIPTION,
ALRA.ACTION_LEVEL_TYPE,
ALRA.DATE_LAST_EXECUTED,
ALRA.FILE_NAME,
ALRA.ARGUMENT_STRING,
ALRA.PROGRAM_APPLICATION_ID,
ALRA.CONCURRENT_PROGRAM_ID,
ALRA.LIST_APPLICATION_ID,
ALRA.LIST_ID,
ALRA.TO_RECIPIENTS,
ALRA.CC_RECIPIENTS,
ALRA.BCC_RECIPIENTS,
ALRA.PRINT_RECIPIENTS,
ALRA.PRINTER,
ALRA.SUBJECT,
ALRA.REPLY_TO,
ALRA.RESPONSE_SET_ID,
ALRA.FOLLOW_UP_AFTER_DAYS,
ALRA.COLUMN_WRAP_FLAG,
ALRA.MAXIMUM_SUMMARY_MESSAGE_WIDTH,
ALRA.BODY,
ALRA.VERSION_NUMBER
);
SELECT NVL(MAX(SEQUENCE),0)+1
INTO ACTION_SET_SEQUENCE
FROM ALR_ACTION_SETS
WHERE APPLICATION_ID = 250
AND ALERT_ID = 10177;
INSERT INTO ALR_ACTION_SETS (
APPLICATION_ID,
ACTION_SET_ID,
NAME,
ALERT_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
END_DATE_ACTIVE,
ENABLED_FLAG,
RECIPIENTS_VIEW_ONLY_FLAG,
DESCRIPTION,
SUPPRESS_FLAG,
SUPPRESS_DAYS,
SEQUENCE)
VALUES (
250,
NEW_ACTION_SET_ID,
NEW_ACTION_SET_NAME,
10177,
SYSDATE,
X_USER_ID,
SYSDATE,
X_USER_ID,
NULL,
'Y',
'N',
NEW_ACTION_SET_NAME,
'N',
NULL,
ACTION_SET_SEQUENCE
);
SELECT NVL(MAX(SEQUENCE),0)+1
INTO ACTION_SET_MEMBERS_SEQUENCE
FROM ALR_ACTION_SET_MEMBERS
WHERE APPLICATION_ID = 250
AND ALERT_ID = 10177
AND ACTION_SET_ID = NEW_ACTION_SET_ID;
INSERT INTO ALR_ACTION_SET_MEMBERS (
APPLICATION_ID,
ACTION_SET_MEMBER_ID,
ACTION_SET_ID,
ACTION_ID,
ACTION_GROUP_ID,
ALERT_ID,
SEQUENCE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
END_DATE_ACTIVE,
ENABLED_FLAG,
SUMMARY_THRESHOLD,
ABORT_FLAG,
ERROR_ACTION_SEQUENCE)
VALUES (
250,
NEW_ACTION_SET_MEMBER_ID,
NEW_ACTION_SET_ID,
NEW_ACTION_ID,
NULL,
10177,
ACTION_SET_MEMBERS_SEQUENCE,
SYSDATE,
X_USER_ID,
SYSDATE,
X_USER_ID,
NULL,
'Y',
NULL,
'A',
NULL
);
INSERT INTO QA_PLAN_CHAR_ACTION_OUTPUTS (
PLAN_CHAR_ACTION_ID,
CHAR_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
TOKEN_NAME)
VALUES (
QPC_ACTION_ID,
QPCAO.CHAR_ID,
SYSDATE,
X_USER_ID,
SYSDATE,
X_USER_ID,
QPCAO.TOKEN_NAME
);
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,
decimal_precision,
uom_code,
-- 12.1 Device Integration Project.
-- Added device fields.
-- bhsankar Fri Oct 19 01:51:57 PDT 2007
device_flag,
device_id,
override_flag)
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,
QPCV.DECIMAL_PRECISION,
QPCV.UOM_CODE,
-- Bug 6350580
-- 12.1 Device Integration Project.
-- Added device fields.
-- bhsankar Fri Oct 19 01:51:57 PDT 2007
QPCV.DEVICE_FLAG,
QPCV.DEVICE_ID,
QPCV.OVERRIDE_FLAG
);
/* INSERT INTO QA_IN_LISTS (
LIST_ELEM_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LIST_ID,
PARENT_BLOCK_NAME,
VALUE,
VALUE_ID,
CHAR_ID)
SELECT
*** nextval
SYSDATE,
X_USER_ID,
SYSDATE,
X_USER_ID,
QPCAT.PLAN_CHAR_ACTION_TRIGGER_ID,
'QPC_ACTION_TRIGGERS',
QIL.VALUE,
QIL.VALUE_ID,
QIL.CHAR_ID
FROM
QA_PLAN_CHAR_ACTION_TRIGGERS QPCAT,
QA_IN_LISTS QIL
WHERE QPCAT.PLAN_ID = X_COPY_PLAN_ID
AND QPCAT.CHAR_ID = QIL.CHAR_ID
AND ???? */
END insert_plan_chars;