[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT CONFIG_VALUE
FROM wms_carousel_configuration
WHERE CONFIG_NAME = p_name
AND NVL (NVL (device_type_id, p_device_type_id), 0) = NVL (p_device_type_id, 0)
AND NVL (NVL (business_event_id, p_business_event_id), 0) = NVL (p_business_event_id, 0)
AND NVL (NVL (sequence_id, p_sequence_id), 0) = NVL (p_sequence_id, 0)
AND active_ind = 'Y'
ORDER BY device_type_id, business_event_id, sequence_id;
SELECT CONFIG_VALUE
FROM WMS_CAROUSEL_CONFIGURATION
WHERE CONFIG_NAME = P_NAME
AND DEVICE_TYPE_ID = P_DEVICE_TYPE_ID
AND BUSINESS_EVENT_ID = P_BUSINESS_EVENT_ID
AND SEQUENCE_ID = P_SEQUENCE_ID
AND ACTIVE_IND = 'Y'
ORDER BY DEVICE_TYPE_ID, BUSINESS_EVENT_ID, SEQUENCE_ID;
SELECT *
FROM wms_carousel_directive_queue
WHERE status = 'C'
AND NVL (NVL (device_id, p_device_id), -1) = NVL (p_device_id, -1)
AND NVL (p_response, 'null') LIKE NVL (response, 'null')
FOR UPDATE;
SELECT *
FROM wms_carousel_directive_queue
WHERE status = 'C'
AND NVL (NVL (device_id, p_device_id), -1) = NVL (p_device_id, -1)
AND NVL (p_response, 'null') LIKE NVL (response, 'null')
FOR UPDATE;
UPDATE wms_carousel_directive_queue
SET status = 'F'
WHERE CURRENT OF c_error_directive;
UPDATE WMS_CAROUSEL_DIRECTIVE_QUEUE Q
SET STATUS = 'X', LAST_UPDATE_DATE = SYSDATE
WHERE REQUEST_ID = NVL(v_error_directive.REQUEST_ID,0)
AND SEQUENCE_ID > NVL(v_error_directive.SEQUENCE_ID,SEQUENCE_ID);
UPDATE WMS_CAROUSEL_DIRECTIVE_QUEUE Q
SET STATUS = 'P', LAST_UPDATE_DATE = SYSDATE
WHERE REQUEST_ID = NVL(v_error_directive.REQUEST_ID,0)
AND SEQUENCE_ID > NVL(v_error_directive.SEQUENCE_ID,SEQUENCE_ID);
SELECT message_template_id
INTO l_msg_template_id
FROM wms_devices_b
WHERE device_id = p_device_id;
UPDATE wms_carousel_directive_queue
SET status = 'S'
WHERE CURRENT OF c_corresponding_directive;
SELECT sequence_id, config_value
FROM wms_carousel_configuration
WHERE CONFIG_NAME = 'DIRECTIVE'
AND device_type_id = p_device_type_id
AND BUSINESS_EVENT_ID = P_BUSINESS_EVENT_ID
AND active_ind = 'Y'
ORDER BY sequence_id;
INSERT INTO wms_carousel_directive_queue
(CAROUSEL_DIRECTIVE_QUEUE_ID , request_id, task_id,
sequence_id, SUBINVENTORY,
directive, prev_id,
request, response,
response_timeout, max_attempts,
status, send_pipe, pipe_timeout,
receive_pipe, device_id,
device_type_id, addr,
segment1, segment2,
segment3, segment4,
segment5, segment6,
segment7, segment8,
segment9, segment10
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,business_event_id
)
VALUES (WMS_CAROUSEL_DIRECTIVE_QUEUE_S.NEXTVAL, p_task.request_id, p_task.task_id,
p_directive.sequence_id, p_directive.subinventory,
p_directive.directive, p_directive.prev_id,
p_directive.request, p_directive.response,
p_directive.response_timeout, p_directive.max_attempts,
l_directive_queue_status, p_directive.send_pipe, p_directive.pipe_timeout,
p_directive.receive_pipe, p_directive.device_id,
p_directive.device_type_id, p_directive.addr,
p_directive.segment1, p_directive.segment2,
p_directive.segment3, p_directive.segment4,
p_directive.segment5, p_directive.segment6,
p_directive.segment7, p_directive.segment8,
p_directive.segment9, p_directive.segment10
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,fnd_global.login_id
,p_directive.business_event_id
);
IF (UPPER(SUBSTR(LTRIM(P_QUERY),1,6)) = 'SELECT') THEN
IF (l_debug > 0) THEN
LOG (p_task.device_id, 'Executing dynamic query: ' || p_query || ',using: ' || p_task.request_id ||','|| p_task.task_id);
'select max(CAROUSEL_DIRECTIVE_QUEUE_ID) '
|| ' from wms_carousel_directive_queue'
|| ' where sequence_id='
|| ':dir_dep_seq_id'
|| ' and '
|| ':seg_formula'
|| '='
|| ':seg';
SELECT *
FROM wms_carousel_directive_queue a
WHERE ( -- this set of clauses is for timed-out directives
status = 'C' -- has to be current
AND (SYSDATE - last_attempt) * 24 * 60 * 60 >= response_timeout
)
OR ( -- this set of clauses is for independent directives
NVL(status,'P') = 'P' -- has to be a new directive
AND CAROUSEL_DIRECTIVE_QUEUE_ID = -- has to be the first of such by sequence
(SELECT MIN (CAROUSEL_DIRECTIVE_QUEUE_ID)
FROM wms_carousel_directive_queue b
WHERE b.request_id = a.request_id
-- same request
AND b.task_id = a.task_id -- same task
AND NVL (b.status, 'P') in ('P','C')
-- include current ones
)
AND ( -- has to be independent
prev_id IS NULL -- either independent by itself
OR NOT EXISTS -- or the predecessor is finished
(
SELECT *
FROM wms_carousel_directive_queue c
WHERE c.CAROUSEL_DIRECTIVE_QUEUE_ID = a.prev_id -- dependency
AND NVL (c.status, 'P') in ('P','C')
-- include current ones
)
)
)
ORDER BY CAROUSEL_DIRECTIVE_QUEUE_ID
FOR UPDATE;
UPDATE wms_carousel_directive_queue
SET status = 'F', last_attempt = SYSDATE
WHERE request_id = v_directive.request_id
AND task_id = v_directive.task_id
AND sequence_id >= v_directive.sequence_id;
UPDATE wms_carousel_directive_queue
SET status = 'F', last_attempt = SYSDATE
WHERE request_id = v_directive.request_id
AND task_id = v_directive.task_id
AND directive = v_directive.directive;
UPDATE wms_carousel_directive_queue
SET status = v_status,
attempts = NVL (v_directive.attempts, 0) + 1,
last_attempt = SYSDATE
WHERE CURRENT OF c_directives_to_process;
INSERT INTO wms_carousel_log
(CAROUSEL_LOG_ID
,text
,device_id
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
)
VALUES (wms_carousel_log_s.NEXTVAL
,p_data
,p_device_id
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,fnd_global.login_id
);
PROCEDURE update_queue (p_request_id IN NUMBER, p_device_id IN NUMBER, P_STATUS IN VARCHAR2, p_config_name IN VARCHAR2, p_task_id IN NUMBER DEFAULT NULL)
IS
CURSOR c_current_msg (p_request_id IN NUMBER,
p_config_name IN VARCHAR2,
p_task_id IN NUMBER)
IS
SELECT *
FROM WMS_CAROUSEL_DIRECTIVE_QUEUE q
WHERE q.REQUEST_ID = P_REQUEST_ID
AND NVL(q.TASK_ID,0) = NVL(P_TASK_ID,NVL(q.TASK_ID,0))
AND NVL (q.STATUS, 'P') IN ('C', 'P')
AND q.DIRECTIVE IN
(SELECT CONFIG_VALUE
FROM WMS_CAROUSEL_CONFIGURATION
WHERE CONFIG_NAME = NVL(p_config_name,'DIRECTIVE_QUEUE_UPDATE')
AND DEVICE_TYPE_ID = q.DEVICE_TYPE_ID
AND BUSINESS_EVENT_ID = q.BUSINESS_EVENT_ID
AND SEQUENCE_ID = q.SEQUENCE_ID
AND ACTIVE_IND = 'Y')
ORDER BY q.REQUEST_ID, q.DEVICE_TYPE_ID, q.BUSINESS_EVENT_ID, q.SEQUENCE_ID
FOR UPDATE;
UPDATE WMS_CAROUSEL_DIRECTIVE_QUEUE Q
SET STATUS = P_STATUS,
LAST_UPDATE_DATE = SYSDATE
WHERE CURRENT OF c_current_msg;
update_queue (p_request_id, p_device_id, 'X', 'DIRECTIVE_CANCEL_TASK', p_task_id);
update_queue (p_request_id, p_device_id, 'X', 'DIRECTIVE_SKIP_TASK', p_task_id);
update_queue (p_request_id, p_device_id, 'S', 'DIRECTIVE_COMPLETE_TASK', p_task_id);
DELETE FROM wms_carousel_directive_queue
WHERE status IN ('S', 'F', 'X') -- Success, failure, or cancelled
AND (SYSDATE - nvl(last_attempt,LAST_UPDATE_DATE)) * 24 * 60 * 60 >= v_purge_interval;
DELETE FROM wms_carousel_log
WHERE SYSDATE - LAST_UPDATE_DATE > 1;
SELECT device_type_id
INTO v_device_type_id
FROM wms_devices_b
WHERE device_id = p_device_id;
SELECT SUBSTR (l_string, 1, 1), SUBSTR (l_string, -1, 1)
INTO l_begin, l_end
FROM DUAL;
SELECT INSTR (l_string, ',', 1, l_char_count)
INTO l_instr_output
FROM DUAL;
SELECT SUBSTR (l_string,
l_start_position,
( DECODE (l_instr_output,
0, LENGTH (l_string)+1,
l_instr_output
)
- l_start_position
)
)
INTO l_ascii_value
FROM DUAL;
SELECT template_id, sequence_id, component, component_length,
left_or_right_padded, padding_character, start_component_delimiter,
end_component_delimiter
FROM (SELECT wmc.*, ROWNUM rnum
FROM (SELECT *
FROM wms_msg_components
WHERE template_id = p_templ_id
ORDER BY sequence_id) wmc
WHERE ROWNUM <= p_comp_no)
WHERE rnum >= p_comp_no;
SELECT lookup_code, meaning
INTO x_component_code, x_component_meaning
FROM mfg_lookups
WHERE lookup_type = 'WMS_DEVICE_MSG_COMPONENTS'
AND lookup_code = l_msg_component.component;
SELECT lookup_code, meaning
INTO x_datatype_code, x_datatype_meaning
FROM mfg_lookups
WHERE lookup_type = 'WMS_DATA_TYPE'
AND lookup_code = l_msg_component.datatype;
SELECT *
FROM wms_msg_templates
WHERE template_id = p_templ_id;
SELECT *
FROM wms_msg_components
WHERE template_id = p_templ_id
ORDER BY sequence_id;
SELECT INSTR (l_msg, l_param_delimiter, 1, l_no_of_msg_comps)
INTO l_instr_output
FROM DUAL;
SELECT SUBSTR (l_msg,
l_start_position,
( DECODE (l_instr_output,
0, LENGTH (l_msg) + 1,
l_instr_output
)
- l_start_position
)
)
INTO l_msg_component
FROM DUAL;
SELECT SUBSTR (l_msg,
l_start_position,
l_comp_record.component_length
)
INTO l_substr_output
FROM DUAL;
SELECT INSTR (l_msg, l_delimiter, 1, l_occurrence)
INTO l_instr_output
FROM DUAL;
SELECT SUBSTR (l_msg,
l_start_position,
( DECODE (l_instr_output,
0, LENGTH (l_msg),
l_instr_output
)
- l_start_position
)
)
INTO l_msg_component
FROM DUAL;