The following lines contain the word 'select', 'insert', 'update' or 'delete':
select min(rule_seq)
from qa_skiplot_process_plan_rules
where process_plan_id = pp_id;
select qsrr.receipt_date
from qa_skiplot_rcv_results qsrr
where insp_lot_id = (select max(qsrr2.insp_lot_id)
from qa_skiplot_rcv_results qsrr2
where qsrr2.criteria_id = p_criteria_id and
qsrr2.receipt_date < p_receipt_date);
select q1.insp_lot_id, q1.receipt_date
from qa_skiplot_rcv_results q1
where q1.insp_lot_id = (select min(q2.insp_lot_id)
from qa_skiplot_rcv_results q2
where q2.insp_lot_id > x_rule_start_lotid and
q2.criteria_id = x_criteria_id and
q2.process_id = x_process_id);
update qa_skiplot_plan_states
set rule_start_lot_id = x_next_lotid,
rule_start_date = x_next_receipt_date
where process_plan_id = p_plan_state.process_plan_id and
criteria_id = p_plan_state.criteria_id;
insert_error_log (
p_module_name =>'QA_SKIPLOT_UTILITY.check_date_span',
p_error_message => 'Next lotid or next receipt date is null');
insert_error_log (
p_module_name =>'QA_SKIPLOT_UTILITY.CHECK_SKIPLOT_AVAILABILITY',
p_error_message => 'QA_SKIPLOT_CHECK_AVALIABLITY_ERR',
p_comments => SUBSTR (SQLERRM , 1 , 240));
select qa_skipping_insp_flag
from mtl_parameters
where organization_id = x_org_id;
select count(*)
from qa_skiplot_rcv_criteria_val_v qsrc
where qsrc.organization_id = x_org_id and
trunc(sysdate) between nvl(trunc(qsrc.effective_from), trunc(sysdate))
and nvl(trunc(qsrc.effective_to), trunc(sysdate));
select frequency_num, frequency_denom
from qa_skiplot_process_plan_rules
where process_plan_id = x_pp_id and
rule_seq = x_rule_seq;
'select qspp.process_plan_id
from qa_skiplot_association qsa,
qa_skiplot_process_plans qspp
where qsa.criteria_id = :1 and
qsa.process_id = :2 and
qsa.process_id = qspp.process_id and
qspp.plan_id = :3'
using p_criteria_id, p_process_id, p_plan_id;
'select
qspp.plan_id,
qspp.process_plan_id,
qsa.process_id,
qsp.disqualification_days,
qsa.criteria_id,
qspp.alternate_plan_id,
qsps.current_rule,
qsppr.rounds,
qsppr.days_span,
qsppr.frequency_num,
qsppr.frequency_denom,
qsps.current_round,
qsps.current_lot,
qsps.lot_accepted,
qsps.rule_start_lot_id,
qsps.rule_start_date,
qsps.last_receipt_lot_id,
qsps.last_receipt_date
from qa_skiplot_association qsa,
qa_skiplot_processes qsp,
qa_skiplot_process_plans qspp,
qa_skiplot_process_plan_rules qsppr,
qa_skiplot_plan_states qsps
where qsa.criteria_id = :1 and
qsp.process_id = qsa.process_id and
qspp.process_plan_id = :2 and
qsppr.process_plan_id = qspp.process_plan_id and
qsps.process_plan_id = qspp.process_plan_id and
qsps.criteria_id = qsa.criteria_id and
qsps.current_rule = qsppr.rule_seq'
using p_criteria_id, pp_id;
insert_error_log (
p_module_name => 'QA_SKIPLOT_UTILITY.FETCH_PLAN_STATE',
p_error_message => 'fail to fetch plan state',
p_comments => SUBSTR (SQLERRM , 1 , 240));
select qspp.process_plan_id
from qa_skiplot_process_plans qspp
where process_id = x_pid;
update_insp_stage(
p_txn => p_txn,
p_stage => 'QUALIFICATION',
p_criteria_id =>p_criteria_id,
p_process_id => p_process_id);
insert_error_log (
p_module_name =>'QA_SKIPLOT_UTILITY.INIT_PLAN_STATE',
p_error_message => 'QA_SKIPLOT_INIT_STATE_FAILURE',
p_comments => 'process_plan_id or criteria_id not available');
delete qa_skiplot_plan_states where
process_plan_id = p_process_plan_id and
criteria_id = p_criteria_id
returning current_rule, last_receipt_date, last_receipt_lot_id into
old_rule, last_date, last_receipt_lot;
insert into qa_skiplot_plan_states(
PROCESS_PLAN_ID,
CRITERIA_ID,
CURRENT_RULE,
CURRENT_ROUND,
CURRENT_LOT,
LOT_ACCEPTED,
RULE_START_LOT_ID,
RULE_START_DATE,
LAST_RECEIPT_LOT_ID,
LAST_RECEIPT_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN)
values(
p_process_plan_id,
p_criteria_id,
first_rule,
1,
0,
0,
p_lot_id,
decode(p_lot_id, null, null, sysdate),
nvl(p_lot_id,last_receipt_lot),
nvl(last_date,sysdate),
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id);
update_state_history (
p_process_plan_id => p_process_plan_id,
p_criteria_id => p_criteria_id,
p_old_rule => old_rule,
p_new_rule => first_rule,
p_txn => p_txn);
insert_error_log (
p_module_name => 'QA_SKIPLOT_UTILITY.INIT_PLAN_STATE',
p_error_message => 'fail to delete and insert initial plan state',
p_comments => SUBSTR (SQLERRM , 1 , 240));
select qspp.process_plan_id
from qa_skiplot_association qsa,
qa_skiplot_process_plans qspp
where qsa.criteria_id = x_cid and
qsa.process_id = qspp.process_id;
update_plan_state (
p_process_plan_id => ps.process_plan_id,
p_criteria_id => p_criteria_id,
p_next_rule => 0,
p_next_round => 1,
p_next_lot => 0,
p_lot_accepted => 0);
insert_error_log (
p_module_name => 'QA_SKIPLOT_UTILITY.INIT_PLAN_STATES',
p_error_message => 'fail to update qa_skiplot_plan_states',
p_comments => SUBSTR (SQLERRM , 1 , 240));
UPDATE qa_skiplot_plan_states
SET last_receipt_date = SYSDATE,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE process_plan_id = p_process_plan_id AND
criteria_id = p_criteria_id;
select qspp.process_plan_id,
qsa.criteria_id
from qa_skiplot_process_plans qspp,
qa_skiplot_association qsa
where qspp.process_id = x_pid and
qspp.process_id = qsa.process_id;
update_plan_state (
p_process_plan_id => ps.process_plan_id,
p_criteria_id => ps.criteria_id,
p_next_rule => 0,
p_next_round => 1,
p_next_lot => 0,
p_lot_accepted => 0);
insert_error_log (
p_module_name => 'QA_SKIPLOT_UTILITY.RESET_PLAN_STATES',
p_error_message => 'fail to update qa_skiplot_plan_states',
p_comments => SUBSTR (SQLERRM , 1 , 240));
select min(rule_seq)
from qa_skiplot_process_plan_rules
where process_plan_id = pp_id and
rule_seq > current_rule;
PROCEDURE UPDATE_INSP_STAGE (
p_txn IN NUMBER,
p_stage IN VARCHAR2,
p_criteria_id IN NUMBER,
p_process_id IN NUMBER)IS
BEGIN
--
-- removed the if statement as it's really unnecessary.
-- Reference bug 2137211
-- jezheng
-- Wed Mar 17 15:41:02 PST 2004
--
--if p_txn = RCV then
update qa_skiplot_association
set insp_stage = p_stage
where criteria_id = p_criteria_id and
process_id = p_process_id;
END UPDATE_INSP_STAGE;
PROCEDURE UPDATE_PLAN_STATE(
p_process_plan_id IN NUMBER,
p_criteria_id IN NUMBER,
p_next_rule IN NUMBER DEFAULT NULL,
p_next_round IN NUMBER DEFAULT NULL,
p_next_lot IN NUMBER DEFAULT NULL,
p_rule_start_lotid IN NUMBER DEFAULT NULL,
p_last_receipt_lot_id IN NUMBER DEFAULT NULL,
p_lot_accepted IN NUMBER DEFAULT NULL,
p_txn IN NUMBER DEFAULT NULL) IS
old_plan_state plan_state_rec;
update qa_skiplot_plan_states
set current_rule = nvl(p_next_rule, current_rule),
current_round = nvl(p_next_round, current_round),
current_lot = nvl(p_next_lot, current_lot),
lot_accepted = nvl(p_lot_accepted, lot_accepted),
last_receipt_lot_id = nvl(p_last_receipt_lot_id, last_receipt_lot_id),
last_receipt_date = decode(p_last_receipt_lot_id, null, last_receipt_date, sysdate),
rule_start_lot_id = nvl(p_rule_start_lotid, rule_start_lot_id),
rule_start_date = x_rule_start_date,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where process_plan_id = p_process_plan_id and
criteria_id = p_criteria_id;
update_state_history (
p_old_plan_state => old_plan_state,
p_next_rule => p_next_rule,
p_txn => p_txn);
update_insp_stage(
p_txn => p_txn,
p_stage => 'SKIPPING',
p_criteria_id => p_criteria_id,
p_process_id => get_process_id (p_process_plan_id));
update_insp_stage(
p_txn => p_txn,
p_stage => 'QUALIFICATION',
p_criteria_id =>p_criteria_id,
p_process_id => get_process_id(p_process_plan_id));
insert_error_log (
p_module_name => 'QA_SKIPLOT_UTILITY.UPDATE_PLAN_STATE',
p_error_message => 'QA_SKIPLOT_UPDATE_STATE_FAILURE',
p_comments => SUBSTR (SQLERRM , 1 , 240));
END UPDATE_PLAN_STATE;
select qsp.process_id, qsp.process_code, qsp.description
from qa_skiplot_processes qsp, qa_skiplot_process_plans qspp
where qspp.process_plan_id = x_ppid and
qspp.process_id = qsp.process_id;
select vendor_name,
vendor_site_code,
item,
item_revision,
category_desc,
manufacturer_name,
project_number,
task_number,
wf_role_name
from qa_skiplot_rcv_criteria_v
where criteria_id = x_criteria_id;
select name
from qa_chars
where char_id in (10, 11, 13, 26, 121, 122, 130)
order by char_id;
select qp.name
from qa_plans qp, qa_skiplot_process_plans qspp
where qspp.process_plan_id = x_process_plan_id and
qspp.plan_id = qp.plan_id;
insert_error_log (
p_module_name => 'QA_SKIPLOT_UTILITY.LAUNCH_WORKFLOW',
p_error_message => 'QA_SKIPLOT_WORKFLOW_FAILURE',
p_comments => SUBSTR (SQLERRM , 1 , 240));
PROCEDURE UPDATE_STATE_HISTORY(
p_old_plan_state IN plan_state_rec,
p_next_rule IN NUMBER,
p_txn IN NUMBER DEFAULT NULL) IS
new_freq_num number;
update_state_history (
p_process_plan_id => p_old_plan_state.process_plan_id,
p_criteria_id => p_old_plan_state.criteria_id,
p_old_freq_num => p_old_plan_state.current_freq_num,
p_old_freq_denom => p_old_plan_state.current_freq_denom,
p_new_freq_num => new_freq_num,
p_new_freq_denom => new_freq_denom,
p_txn => p_txn);
END UPDATE_STATE_HISTORY;
PROCEDURE UPDATE_STATE_HISTORY(
p_process_plan_id IN NUMBER,
p_criteria_id IN NUMBER,
p_old_rule IN NUMBER,
p_new_rule IN NUMBER,
p_txn IN NUMBER) IS
old_freq_num number;
update_state_history (
p_process_plan_id => p_process_plan_id,
p_criteria_id => p_criteria_id,
p_old_freq_num => old_freq_num,
p_old_freq_denom => old_freq_denom,
p_new_freq_num => new_freq_num,
p_new_freq_denom => new_freq_denom,
p_txn => p_txn);
END UPDATE_STATE_HISTORY;
PROCEDURE UPDATE_STATE_HISTORY(
p_process_plan_id IN NUMBER,
p_criteria_id IN NUMBER,
p_old_freq_num IN NUMBER,
p_old_freq_denom IN NUMBER,
p_new_freq_num IN NUMBER,
p_new_freq_denom IN NUMBER,
p_txn IN NUMBER) IS
BEGIN
insert into qa_skiplot_state_history(
PROCESS_PLAN_ID,
CRITERIA_ID,
CHANGE_DATE,
OLD_FREQ_NUM,
OLD_FREQ_DENOM,
NEW_FREQ_NUM,
NEW_FREQ_DENOM,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN)
values(
p_process_plan_id,
p_criteria_id,
sysdate,
p_old_freq_num,
p_old_freq_denom,
p_new_freq_num,
p_new_freq_denom,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id);
insert_error_log (
p_module_name => 'QA_SKIPLOT_UTILITY.UPDATE_STATE_HISTORY',
p_error_message => 'QA_SKIPLOT_UPDATE_HISTORY_FAILURE',
p_comments => SUBSTR (SQLERRM , 1 , 240));
END UPDATE_STATE_HISTORY;
PROCEDURE INSERT_ERROR_LOG (
p_module_name IN VARCHAR2,
p_error_message IN VARCHAR2 DEFAULT NULL,
p_comments IN VARCHAR2 DEFAULT NULL) IS
PRAGMA AUTONOMOUS_TRANSACTION;
select qa_skiplot_log_id_s.nextval
from dual;
select 1 from qa_skiplot_log
where log_id = x_id;
insert into qa_skiplot_log(
LOG_ID,
MODULE_NAME,
ERROR_MESSAGE,
COMMENTS,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN)
values(
x_logid,
p_module_name,
p_error_message,
p_comments,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id);
update qa_skiplot_log
set MODULE_NAME = p_module_name,
ERROR_MESSAGE = p_error_message,
COMMENTS = p_comments,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = fnd_global.user_id,
CREATION_DATE = sysdate,
CREATED_BY = fnd_global.user_id,
LAST_UPDATE_LOGIN = fnd_global.login_id
where LOG_ID = x_logid;
END INSERT_ERROR_LOG;
select qa_skiplot_lot_id_s.nextval
from dual;
update_insp_stage(
p_txn => nvl(p_txn, RCV),
p_stage => 'SKIPPING',
p_criteria_id => p_plan_state.criteria_id,
p_process_id => p_plan_state.process_id);
select process_id
from qa_skiplot_process_plans
where process_plan_id = x_ppid;