[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT msg_type,
priority,
in_process_logic,
out_process_logic,
default_process_logic,
validate_logic,
queue_name,
dtd_url
FROM XNP_MSG_TYPES_B
WHERE msg_code = g_message_code ;
SELECT text FROM user_errors
WHERE name = UPPER( l_proc_name ) ;
SELECT name,
element_datatype,
mandatory_flag,
NVL ( element_default_value, 'NP_NULL' ) element_default_value ,
parameter_sequence
FROM xnp_msg_elements
WHERE msg_code = g_message_code
AND parameter_flag = 'Y'
ORDER BY parameter_sequence ;
SELECT msg_element_id
INTO lv_element_id
FROM xnp_msg_elements
WHERE name = p_child_element_name
AND msg_code = p_new_msg_code;
SELECT msg_element_id
INTO lv_element_id
FROM xnp_msg_elements
WHERE name = p_element_name
AND msg_code = p_new_msg_code;
SELECT MSG_TYPE,
STATUS,
PRIORITY,
QUEUE_NAME,
PROTECTED_FLAG,
ROLE_NAME,
LAST_COMPILED_DATE,
VALIDATE_LOGIC,
IN_PROCESS_LOGIC,
OUT_PROCESS_LOGIC,
DEFAULT_PROCESS_LOGIC,
DTD_URL,
DISPLAY_NAME,
DESCRIPTION
FROM XNP_MSG_TYPES_VL
WHERE MSG_CODE = p_old_msg_code;
XNP_MSG_TYPES_PKG.INSERT_ROW
(
X_ROWID => l_rowid,
X_MSG_CODE => p_new_msg_code,
X_MSG_TYPE => c_mesg_rec.msg_type,
X_STATUS => 'UNCOMPILED',
X_PRIORITY => c_mesg_rec.PRIORITY,
X_QUEUE_NAME => c_mesg_rec.QUEUE_NAME,
X_PROTECTED_FLAG => c_mesg_rec.PROTECTED_FLAG,
X_ROLE_NAME => c_mesg_rec.ROLE_NAME,
X_LAST_COMPILED_DATE => sysdate,
X_VALIDATE_LOGIC => c_mesg_rec.VALIDATE_LOGIC ,
X_IN_PROCESS_LOGIC => c_mesg_rec.IN_PROCESS_LOGIC ,
X_OUT_PROCESS_LOGIC => c_mesg_rec.OUT_PROCESS_LOGIC,
X_DEFAULT_PROCESS_LOGIC => c_mesg_rec.DEFAULT_PROCESS_LOGIC,
X_DTD_URL => c_mesg_rec.DTD_URL,
X_DISPLAY_NAME => p_new_disp_name ,--c_mesg_rec.DISPLAY_NAME,
X_DESCRIPTION => c_mesg_rec.DESCRIPTION,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => FND_GLOBAL.USER_ID,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
X_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID);
SELECT NAME,
MANDATORY_FLAG,
PARAMETER_FLAG,
ELEMENT_DATATYPE,
ELEMENT_DEFAULT_VALUE,
DATA_LENGTH,
PARAMETER_SEQUENCE,
MSG_ELEMENT_ID --- Added for msg_structure
FROM XNP_MSG_ELEMENTS
WHERE MSG_CODE= p_old_msg_code;
select str.SEQUENCE_IN_PARENT,
str.CARDINALITY,
str.DATA_SOURCE,
str.DATA_SOURCE_TYPE,
str.DATA_SOURCE_REFERENCE,
str.ELEMENT_POSITION,
str.ELEMENT_ALIGNMENT,
str.PADDING
from xnp_msg_structures str,
xnp_msg_elements elm
where str.parent_element_id = elm.msg_element_id
AND elm.msg_code=p_old_msg_code
AND elm.NAME='MESSAGE' ;
INSERT INTO XNP_MSG_ELEMENTS
(
MSG_ELEMENT_ID ,
MSG_CODE ,
NAME ,
MANDATORY_FLAG ,
PARAMETER_FLAG ,
ELEMENT_DATATYPE ,
ELEMENT_DEFAULT_VALUE ,
DATA_LENGTH ,
PARAMETER_SEQUENCE ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN )
VALUES(
XNP_MSG_ELEMENTS_S.NEXTVAL,
p_new_msg_code,
lv_element_name,
c_msg_element_rec.MANDATORY_FLAG,
c_msg_element_rec.PARAMETER_FLAG,
c_msg_element_rec.ELEMENT_DATATYPE,
c_msg_element_rec.ELEMENT_DEFAULT_VALUE,
c_msg_element_rec.DATA_LENGTH,
c_msg_element_rec.PARAMETER_SEQUENCE,
FND_GLOBAL.USER_ID,
SYSDATE,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID
);
SELECT str.child_element_id,
str.SEQUENCE_IN_PARENT,
str.CARDINALITY,
str.DATA_SOURCE,
str.DATA_SOURCE_TYPE,
str.DATA_SOURCE_REFERENCE,
str.ELEMENT_POSITION,
str.ELEMENT_ALIGNMENT,
str.PADDING,
elm.NAME
FROM xnp_msg_structures str,
xnp_msg_elements elm
WHERE str.msg_code = p_old_msg_code
AND str.parent_element_id = p_element_id
AND str.child_element_id = elm.msg_element_id;
INSERT INTO XNP_MSG_STRUCTURES
(STRUCTURE_ID ,
PARENT_ELEMENT_ID ,
CHILD_ELEMENT_ID ,
MSG_CODE ,
SEQUENCE_IN_PARENT ,
CARDINALITY ,
DATA_SOURCE ,
DATA_SOURCE_TYPE ,
DATA_SOURCE_REFERENCE ,
ELEMENT_POSITION ,
ELEMENT_ALIGNMENT ,
PADDING ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN )
VALUES( XNP_MSG_STRUCTURES_S.NEXTVAL,
P_PARENT_ELEMENT_ID ,
P_CHILD_ELEMENT_ID ,
P_MSG_CODE ,
P_SEQUENCE_IN_PARENT ,
P_CARDINALITY ,
P_DATA_SOURCE ,
P_DATA_SOURCE_TYPE ,
P_DATA_SOURCE_REFERENCE ,
P_ELEMENT_POSITION ,
P_ELEMENT_ALIGNMENT ,
P_PADDING ,
FND_GLOBAL.USER_ID ,
SYSDATE ,
FND_GLOBAL.USER_ID ,
SYSDATE ,
FND_GLOBAL.LOGIN_ID );
CURSOR update_msg_type IS
SELECT 'X' FROM xnp_msg_types_b
WHERE msg_code = p_msg_code
FOR UPDATE OF status , last_compiled_date;
SELECT protected_flag
INTO l_flag
FROM xnp_msg_types_b
WHERE msg_code = p_msg_code;
FOR cur_rec IN update_msg_type LOOP
UPDATE xnp_msg_types_b
SET status='COMPILED',
last_compiled_date = l_sysdate
WHERE CURRENT OF update_msg_type ;
IF ( update_msg_type%ISOPEN ) THEN
CLOSE update_msg_type ;
SELECT MET.name,
MET.element_datatype,
MET.mandatory_flag,
MSE.data_source_type,
MSE.data_source,
MSE.data_source_reference,
MSE.cardinality,
MET.parameter_flag
FROM xnp_msg_elements MET, xnp_msg_structures MSE
WHERE MSE.msg_code = g_message_code
AND MSE.child_element_id = MET.msg_element_id
AND MSE.parent_element_id = (
SELECT msg_element_id FROM xnp_msg_elements MET1
WHERE MET1.name = parent_name
AND MET1.msg_code = g_message_code )
ORDER BY MSE.sequence_in_parent ;
|| ' XNP_MESSAGE.UPDATE_STATUS(x_message_id, '
|| '''' || 'TIMEOUT' || ''');' || g_new_line
SELECT msg_type
FROM xnp_msg_types_b
WHERE msg_code = g_message_code ;
SELECT MET.name,
MET.element_datatype,
MET.mandatory_flag,
MSE.data_source_type,
MSE.data_source,
MSE.data_source_reference,
MSE.cardinality,
MET.parameter_flag
FROM xnp_msg_elements MET,
xnp_msg_structures MSE
WHERE MSE.msg_code = g_message_code
AND MSE.child_element_id = MET.msg_element_id
AND MSE.parent_element_id = ( SELECT msg_element_id
FROM xnp_msg_elements MET1
WHERE MET1.name = parent_name
AND MET1.msg_code = g_message_code )
ORDER BY MSE.sequence_in_parent ;
g_temp_tab.DELETE;