APPS.XNP_MESSAGE SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 48

		SELECT  msg_id,
			msg_code,
			reference_id,
			opp_reference_id,
			msg_creation_date,
			sender_name,
			recipient_name,
			msg_version,
			order_id,
			wi_instance_id,
			fa_instance_id
		FROM  xnp_msgs
		WHERE msg_id = message_id ;

Line: 319

	SELECT  ( XNP_MSGS_S.nextval )  INTO x_msg_id  FROM DUAL ;

Line: 342

		SELECT  msg_id,
			msg_code,
			reference_id,
			opp_reference_id,
			msg_creation_date,
			sender_name,
			recipient_name,
			msg_version,
			order_id,
			wi_instance_id,
			fa_instance_id,
			body_text
		FROM  xnp_msgs
		WHERE msg_id = message_id ;

Line: 407

		SELECT body_text from xnp_msgs
		WHERE msg_id = message_id ;

Line: 434

*****  Purpose:      Inserts a message into XNP_MSGS table
*****                and enqueues the message on a specified Queue .
****************************************************************************/

PROCEDURE push(
	p_msg_header IN msg_header_rec_type
	,p_body_text IN VARCHAR2
	,p_queue_name IN VARCHAR2
	,p_recipient_list IN VARCHAR2 DEFAULT NULL
	,p_correlation_id IN VARCHAR2 DEFAULT NULL
	,p_priority IN INTEGER DEFAULT 1
	,p_commit_mode IN NUMBER DEFAULT c_on_commit
	,p_delay IN NUMBER DEFAULT DBMS_AQ.NO_DELAY
	,p_fe_name IN VARCHAR2 DEFAULT NULL
	,p_adapter_name IN VARCHAR2 DEFAULT NULL
)
IS

	l_message            SYSTEM.XNP_MESSAGE_TYPE ;

Line: 550

	INSERT into xnp_msgs (
		msg_id,
		msg_code,
		direction_indicator,
		reference_id,
		opp_reference_id,
		fe_name,
		msg_creation_date,
		sender_name,
		recipient_name,
		msg_status,
		msg_version,
		order_id,
		wi_instance_id,
		fa_instance_id,
		adapter_name,
		body_text,
		created_by,
		creation_date,
		last_updated_by,
		last_update_date )
  	VALUES (
		l_msg_header.message_id,
		l_msg_header.message_code,
		l_msg_header.direction_indr,
		l_msg_header.reference_id,
		l_msg_header.opp_reference_id,
		l_fe_name,
		sysdate,
		l_msg_header.sender_name,
		l_msg_header.recipient_name,
		'READY',
		l_msg_header.version,
		l_msg_header.order_id,
		l_msg_header.wi_instance_id,
		l_msg_header.fa_instance_id,
		l_adapter_name,
		empty_clob(),
		fnd_global.user_id,
		SYSDATE,
		fnd_global.user_id,
		SYSDATE )
	RETURNING body_text INTO l_lob_loc ;

Line: 731

*****  Purpose:      Inserts a message into XNP_MSGS table
*****                and enqueues the message on a specified Queue .
****************************************************************************/

PROCEDURE push(
	p_message_id IN NUMBER
	,p_message_code IN VARCHAR2
	,p_reference_id IN VARCHAR2
	,p_opp_reference_id IN VARCHAR2
	,p_direction_indr IN VARCHAR2
	,p_creation_date IN DATE
	,p_sender_name IN VARCHAR2
	,p_recipient_name IN VARCHAR2
	,p_version OUT NOCOPY VARCHAR2
	,p_order_id IN NUMBER
	,p_wi_instance_id IN NUMBER
	,p_fa_instance_id IN NUMBER
	,p_body_text IN VARCHAR2
	,p_queue_name IN VARCHAR2
	,p_recipient_list IN VARCHAR2 DEFAULT NULL
	,p_correlation_id IN VARCHAR2 DEFAULT NULL
	,p_priority IN INTEGER DEFAULT 1
	,p_commit_mode IN NUMBER DEFAULT c_on_commit
)
IS
	l_msg_header  XNP_MESSAGE.MSG_HEADER_REC_TYPE ;

Line: 885

       	       		 SELECT count(*)
               		   INTO l_count
               		 FROM xnp_timer_registry
               		 WHERE timer_id = l_message.message_id;

Line: 958

		SELECT xnp_utils.get_adapter_using_fe(
		FET.fulfillment_element_name) adapter_name
		FROM xdp_fes FET,
		xnp_event_subscribers ESS
		WHERE FET.FE_ID = ESS.FE_ID
		AND ESS.msg_code = message_code ;

Line: 1111

*****  Procedure:    UPDATE_STATUS()
*****  Purpose:      Updates message status.
****************************************************************************/

PROCEDURE update_status(
	p_msg_id IN NUMBER
	,p_status IN VARCHAR2
	,p_error_desc IN VARCHAR2 DEFAULT NULL
	,p_order_id IN NUMBER DEFAULT NULL
	,p_wi_instance_id IN NUMBER DEFAULT NULL
	,p_fa_instance_id IN NUMBER DEFAULT NULL
)
IS

BEGIN

-- mviswana 11/2001
-- bug fix # 1882340 to populate send_rcv_date

	IF (p_order_id IS NULL) THEN
		UPDATE XNP_MSGS SET msg_status = p_status,
                send_rcv_date = SYSDATE,
                last_update_date = SYSDATE,
		description = description || p_error_desc
		WHERE msg_id = p_msg_id ;

Line: 1137

		UPDATE XNP_MSGS SET msg_status = p_status,
                        send_rcv_date = SYSDATE,
                        last_update_date = SYSDATE,
			description = description || p_error_desc,
			order_id = p_order_id,
			wi_instance_id = p_wi_instance_id,
			fa_instance_id = p_fa_instance_id
		WHERE msg_id = p_msg_id ;

Line: 1153

END update_status ;

Line: 1167

		SELECT msg_status FROM XNP_MSGS
		WHERE msg_id = p_msg_id ;

Line: 1182

PROCEDURE xnp_mte_insert_element (
	p_msg_code IN VARCHAR2
	,p_msg_type IN VARCHAR2
) IS

	L_PARENT_ID	   NUMBER;

Line: 1202

	INSERT INTO XNP_MSG_ELEMENTS(
		MSG_ELEMENT_ID
		,MSG_CODE
		,NAME
		,ELEMENT_DATATYPE
		,MANDATORY_FLAG
		,PARAMETER_FLAG
		,CREATED_BY
		,CREATION_DATE
		,LAST_UPDATED_BY
		,LAST_UPDATE_DATE
        ,LAST_UPDATE_LOGIN)
	VALUES(
		 XNP_MSG_ELEMENTS_S.NEXTVAL
		,P_MSG_CODE
		,'MESSAGE' -- Always insert type to be 'Message' - even though it can be an event or a timer
		,'VARCHAR2'
		,'Y'
		,'N'
		,FND_GLOBAL.USER_ID
		,SYSDATE
		,FND_GLOBAL.USER_ID
		,SYSDATE
                ,FND_GLOBAL.LOGIN_ID)
	RETURNING MSG_ELEMENT_ID INTO L_PARENT_ID;

Line: 1234

	INSERT INTO XNP_MSG_ELEMENTS(
		MSG_ELEMENT_ID
		,MSG_CODE
		,NAME
		,ELEMENT_DATATYPE
		,MANDATORY_FLAG
		,PARAMETER_FLAG
		,CREATED_BY
		,CREATION_DATE
		,LAST_UPDATED_BY
		,LAST_UPDATE_DATE
                ,LAST_UPDATE_LOGIN
		)
	VALUES(
		XNP_MSG_ELEMENTS_S.NEXTVAL
		,P_MSG_CODE
		,P_MSG_CODE
		,'VARCHAR2'
		,'Y'
		,'N'
		,FND_GLOBAL.USER_ID
		,SYSDATE
		,FND_GLOBAL.USER_ID
		,SYSDATE
                ,FND_GLOBAL.LOGIN_ID
		)
	RETURNING MSG_ELEMENT_ID INTO L_CHILD_ID;

Line: 1268

	INSERT INTO XNP_MSG_STRUCTURES(
                 STRUCTURE_ID
		,PARENT_ELEMENT_ID
		,CHILD_ELEMENT_ID
		,MSG_CODE
		,CREATED_BY
		,CREATION_DATE
		,LAST_UPDATED_BY
		,LAST_UPDATE_DATE
                ,LAST_UPDATE_LOGIN
		)
	VALUES(
         XNP_MSG_STRUCTURES_S.NEXTVAL
		,L_PARENT_ID
		,L_CHILD_ID
		,P_MSG_CODE
		,FND_GLOBAL.USER_ID
		,SYSDATE
		,FND_GLOBAL.USER_ID
		,SYSDATE
        ,FND_GLOBAL.LOGIN_ID
		)
	RETURNING STRUCTURE_ID INTO L_STRUCTURE_ID;

Line: 1300

		INSERT INTO XNP_MSG_ELEMENTS(
			MSG_ELEMENT_ID
			,MSG_CODE
			,NAME
			,ELEMENT_DATATYPE
			,ELEMENT_DEFAULT_VALUE
			,MANDATORY_FLAG
			,PARAMETER_FLAG
			,CREATED_BY
			,CREATION_DATE
			,LAST_UPDATED_BY
			,LAST_UPDATE_DATE
                        ,LAST_UPDATE_LOGIN
			)
		VALUES(
			XNP_MSG_ELEMENTS_S.NEXTVAL
			,P_MSG_CODE
			,'DELAY'
			,'NUMBER'
			,'0'
			,'Y'
			,'N'
			,FND_GLOBAL.USER_ID
			,SYSDATE
			,FND_GLOBAL.USER_ID
			,SYSDATE
                        ,FND_GLOBAL.LOGIN_ID
			)
		RETURNING MSG_ELEMENT_ID INTO L_GRANDCHILD_ID;

Line: 1335

		INSERT INTO XNP_MSG_STRUCTURES(
                         STRUCTURE_ID
			,PARENT_ELEMENT_ID
			,CHILD_ELEMENT_ID
			,MSG_CODE
			,CREATED_BY
			,CREATION_DATE
			,LAST_UPDATED_BY
			,LAST_UPDATE_DATE
                        ,LAST_UPDATE_LOGIN
			)
		VALUES(
			XNP_MSG_STRUCTURES_S.NEXTVAL
			,L_CHILD_ID
			,L_GRANDCHILD_ID
			,P_MSG_CODE
			,FND_GLOBAL.USER_ID
			,SYSDATE
			,FND_GLOBAL.USER_ID
			,SYSDATE
                        ,FND_GLOBAL.LOGIN_ID
			)
		RETURNING STRUCTURE_ID INTO L_STRUCTURE_ID;

Line: 1365

		INSERT INTO XNP_MSG_ELEMENTS(
			MSG_ELEMENT_ID
			,MSG_CODE
			,NAME
			,ELEMENT_DATATYPE
			,ELEMENT_DEFAULT_VALUE
			,MANDATORY_FLAG
			,PARAMETER_FLAG
			,CREATED_BY
			,CREATION_DATE
			,LAST_UPDATED_BY
			,LAST_UPDATE_DATE
                        ,LAST_UPDATE_LOGIN
			)
		VALUES(
			XNP_MSG_ELEMENTS_S.NEXTVAL
			,P_MSG_CODE
			,'INTERVAL'
			,'NUMBER'
			,'0'
			,'Y'
			,'N'
			,FND_GLOBAL.USER_ID
			,SYSDATE
			,FND_GLOBAL.USER_ID
			,SYSDATE
            ,FND_GLOBAL.LOGIN_ID
			)
		RETURNING MSG_ELEMENT_ID INTO L_GRANDCHILD_ID;

Line: 1400

		INSERT INTO XNP_MSG_STRUCTURES(
            STRUCTURE_ID
			,PARENT_ELEMENT_ID
			,CHILD_ELEMENT_ID
			,MSG_CODE
			,CREATED_BY
			,CREATION_DATE
			,LAST_UPDATED_BY
			,LAST_UPDATE_DATE
                        ,LAST_UPDATE_LOGIN
			)
		VALUES(
			XNP_MSG_STRUCTURES_S.NEXTVAL
			,L_CHILD_ID
			,L_GRANDCHILD_ID
			,P_MSG_CODE
			,FND_GLOBAL.USER_ID
			,SYSDATE
			,FND_GLOBAL.USER_ID
			,SYSDATE
            ,FND_GLOBAL.LOGIN_ID
			)
		RETURNING STRUCTURE_ID INTO L_STRUCTURE_ID;

Line: 1426

END xnp_mte_insert_element ;

Line: 1432

            It also updates the status to ready and clears any error message.
**************************************************************************/

PROCEDURE fix (
	P_MSG_ID IN NUMBER
)
IS
l_msg_header XNP_MESSAGE.MSG_HEADER_REC_TYPE ;

Line: 1453

  select msg_code into l_msg_header.message_code from xnp_msgs where msg_id = p_msg_id;

Line: 1455

	UPDATE xnp_msgs SET msg_status = 'READY',
        last_update_date = SYSDATE,
	description = NULL
	WHERE msg_id = p_msg_id ;

Line: 1562

		SELECT NVL(xms.role_name,'FND_RESP535:21704')
		FROM xnp_msg_types_b xms, xnp_msgs xmg
		WHERE xmg.msg_id = p_msg_id
		AND xms.msg_code = xmg.msg_code;

Line: 1774

PROCEDURE delete (
	p_msg_code IN VARCHAR2
)
IS
 l_error_code NUMBER := 0;

Line: 1793

		DELETE FROM xnp_msg_structures
		WHERE msg_code = p_msg_code;

Line: 1796

		DELETE FROM xnp_msg_elements
		WHERE MSG_CODE = p_msg_code;

Line: 1799

		DELETE FROM xnp_msg_acks
		WHERE source_msg_code = p_msg_code
		OR ack_msg_code = p_msg_code;

Line: 1803

		DELETE FROM xnp_timer_publishers
		WHERE timer_message_code = p_msg_code
		OR source_message_code = p_msg_code;

Line: 1807

                DELETE FROM xnp_event_subscribers
		WHERE msg_code = p_msg_code;

Line: 1811

		        xnp_msg_types_pkg.delete_row(p_msg_code) ;

Line: 1823

END delete;

Line: 1851

	SELECT  1
	INTO	l_count
	FROM	DUAL
	WHERE	EXISTS
		(SELECT	1
		FROM	XNP_CALLBACK_EVENTS
		WHERE	msg_code = p_msg_code);

Line: 1871

	SELECT  1
	INTO	l_count
	FROM	DUAL
	WHERE	EXISTS
		(SELECT	1
		FROM XNP_MSGS
		WHERE msg_code=p_msg_code);

Line: 1890

	SELECT  1
	INTO	l_count
	FROM	DUAL
	WHERE	EXISTS
		(SELECT	1
		FROM XNP_TIMER_REGISTRY
		WHERE p_msg_code=timer_message_code);

Line: 1910

	SELECT count(*) INTO l_count
	FROM XNP_CALLBACK_EVENTS
	WHERE msg_code=p_msg_code;

Line: 1921

	SELECT count(*) INTO l_count
	FROM XNP_MSGS
	WHERE msg_code=p_msg_code;

Line: 1932

	SELECT count(*) INTO l_count
	FROM XNP_TIMER_REGISTRY
	WHERE p_msg_code=timer_message_code;

Line: 2122

        SELECT nvl(substr(value,1,instr(value,',')-1),value)
          INTO g_logdir
        FROM v$parameter
        WHERE name = 'utl_file_dir';

Line: 2127

        select sysdate into g_logdate from dual;