The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT msc_questions_s.nextval
INTO x_question_id
FROM dual;
INSERT INTO MSC_QUESTIONS_B(
QUESTION_ID
,ANSWER_ID
,QUESTION_CODE
,QUESTION_TYPE
,PACKAGE_NAME
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN )
SELECT
x_question_id
,null
,x_question_code
,x_question_type
,x_package_name
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.conc_login_id
FROM dual
WHERE not exists (select 'already exists'
from msc_questions_b
where question_code = x_question_code);
msc_util.msc_debug('Inserted into msc_questions_b:'|| sql%rowcount);
INSERT INTO MSC_QUESTIONS_TL(
QUESTION_ID
,LANGUAGE
,USER_QUESTION_NAME
,DESCRIPTION
,SOURCE_LANG
,TRANSLATED
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN )
SELECT
x_question_id
,x_lang_code
,x_question
,NULL
,x_lang_code
,NULL
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.conc_login_id
FROM dual
WHERE not exists
(select 'already exists'
from msc_questions_tl
where question_id = (select question_id
from msc_questions_b
where question_code = x_question_code)
and language = x_lang_code);
msc_util.msc_debug('Inserted msc_questions_tl:' || to_char(sql%rowcount));
SELECT msc_questions_s.nextval
INTO x_question_id
FROM dual;
INSERT INTO MSC_QUESTIONS_B(
QUESTION_ID
,ANSWER_ID
,QUESTION_CODE
,QUESTION_TYPE
,PACKAGE_NAME
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN )
SELECT
x_question_id
,null
,x_question_code
,q.question_type
,q.package_name
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.conc_login_id
FROM msc_questions_b q
WHERE q.question_code = x_copy_question
AND not exists (select 'already exists'
from msc_questions_b
where question_code = x_question_code);
msc_util.msc_debug('Inserted into msc_questions_b:'|| sql%rowcount);
INSERT INTO MSC_QUESTIONS_TL(
QUESTION_ID
,LANGUAGE
,USER_QUESTION_NAME
,DESCRIPTION
,SOURCE_LANG
,TRANSLATED
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN )
SELECT
x_question_id
,q.language
,x_question
,NULL
,x_lang_code
,NULL
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.conc_login_id
FROM msc_questions_tl q
WHERE q.language = x_lang_code
AND q.question_id = (select question_id
from msc_questions_b
x where question_code = x_copy_question)
AND not exists
(select 'already exists'
from msc_questions_tl
where question_id = (select question_id
from msc_questions_b
where question_code = x_question_code)
and language = x_lang_code);
msc_util.msc_debug('Inserted msc_questions_tl:' || to_char(sql%rowcount));
ELSIF (x_mode = 3) THEN -- update mode
update msc_questions_b
set package_name = x_package_name
where question_code = x_question_code;
msc_util.msc_debug('Updated msc_questions_b:' || to_char(sql%rowcount) ||
':'|| x_question_code ||'with package name value:' ||
x_package_name);
update msc_questions_tl
set user_question_name = x_question
where question_id = (select question_id
from msc_questions_b
where question_code = x_question_code)
and language = x_lang_code;
msc_util.msc_debug('Updated msc_questions_tl:' || to_char(sql%rowcount) ||
':' || x_question_code || 'with user question:' ||
x_question);
ELSIF (x_mode = 4) THEN -- delete mode
delete msc_questions_tl
where question_id = (select question_id
from msc_questions_b
where question_code = x_question_code);
msc_util.msc_debug('Deleted msc_questions_tl:'|| to_char(sql%rowcount) ||
':' || x_question_code);
delete msc_questions_b
where question_code = x_question_code;
msc_util.msc_debug('Deleted msc_questions_b:'|| to_char(sql%rowcount) ||
':' || x_question_code);
SELECT NVL(package_name,'MSC_ASK_ORACLE'), question_code
INTO x_pkg_name, x_question_code
FROM msc_questions_b
WHERE question_id = x_question_id;
SELECT msc_answers_s.nextval
INTO answer_id
FROM sys.dual;
PROCEDURE insert_answer(
a_id IN NUMBER,
q_id IN NUMBER,
seq IN NUMBER,
ans IN VARCHAR2)
IS
BEGIN
-- dbms_output.put_line('Inserting answer:'|| ans);
INSERT INTO MSC_ANSWERS(
ANSWER_ID,
QUESTION_ID,
SEQ_NUM,
SESSION_ID,
ANSWER_TEXT,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
SELECT
a_id,
q_id,
seq,
USERENV('SESSIONID'),
ans,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID
FROM sys.dual;
END insert_answer;
SELECT min(peg2.demand_date)
INTO l_due_date
FROM msc_full_pegging peg2, msc_full_pegging peg1
WHERE peg1.plan_id = l_plan
AND peg1.transaction_id (+) = l_key1
AND peg2.pegging_id = peg1.end_pegging_id (+);
SELECT p2.project_number||'-'||p1.task_number || '-' ||p2.task_name,
early_start_date,
early_finish_date,late_start_date, late_finish_date,
scheduled_start_date, scheduled_finish_date
INTO l_prj, l_es, l_ef, l_ls, l_lf, l_ss, l_sf
FROM pjm_tasks_v p2, pa_tasks_v p1, msc_supplies m
WHERE p2.project_id = p1.project_id
AND p2.task_id = p1.task_id
AND p1.project_id = m.project_id
AND p1.task_id = m.task_id
AND m.transaction_id = l_key1;
l_seq NUMBER := 0; -- remember to increment while inserting the answers
SELECT dmd.order_number,NULL, --dmd.demand_schedule_name,
dmd.using_assembly_demand_date
FROM msc_demands dmd, msc_full_pegging peg
WHERE peg.transaction_id = lkey
AND peg.plan_id = lplan
AND peg.demand_id = dmd.demand_id
AND peg.plan_id = dmd.plan_id
AND dmd.using_assembly_demand_date < peg.supply_date;
SELECT res.operation_seq_num, res.std_op_code, res.resource_seq_num,
res.start_date, res.end_date
FROM msc_resource_requirements res, msc_supplies sup
WHERE sup.plan_id = lplan
AND sup.transaction_id = lkey
AND res.plan_id = sup.plan_id
AND res.supply_id = sup.transaction_id
AND NVL(res.end_date,res.start_date) < sup.new_schedule_date
ORDER BY res.operation_seq_num, res.resource_seq_num;
insert_answer(l_ansid,l_qid,l_seq,l_ans);
l_seq NUMBER := 0; -- remember to increment while inserting the answers
SELECT peg.transaction_id
FROM msc_full_pegging peg
WHERE peg.demand_date > peg.supply_date
START WITH peg.demand_id = lkey
AND peg.plan_id = lplan
CONNECT BY PRIOR peg.pegging_id = peg.prev_pegging_id
ORDER BY peg.demand_date;
insert_answer(l_ansid,l_qid,l_seq,l_ans);