[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
** that updates the request control table with appropriate
** response header and process flag values
**
** p_request_control_id - REQUEST CONTROL ID
** p_response_header_id - RESPONSE HEADER ID
** p_process_flag - PROCESS FLAG
**
*/
PROCEDURE UPDATE_REQCTRL_REC( p_request_control_id IN NUMBER,
p_response_header_id IN NUMBER,
p_process_flag IN NUMBER) IS
--
l_debug_on BOOLEAN;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_REQCTRL_REC';
UPDATE WSH_ITM_REQUEST_CONTROL SET
RESPONSE_HEADER_ID = p_response_header_id,
PROCESS_FLAG = p_process_flag
WHERE REQUEST_CONTROL_ID = p_request_control_id;
END UPDATE_REQCTRL_REC;
** records updated with the appropriate values.
**
** p_message_id - MESSAGE ID (part of incoming response message)
** p_error_code - ERROR CODE
** p_error_type - ERROR TYPE
** p_error_text - ERROR MESSAGE
**
*/
PROCEDURE INSERT_ERROR( p_message_id IN VARCHAR2,
p_error_code IN VARCHAR2,
p_error_type IN VARCHAR2,
p_error_text IN VARCHAR2) IS
l_reqclrt_ids VARCHAR2(2000); --Bug 9226895
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_ERROR';
SELECT WSH_ITM_RESPONSE_HEADERS_S.NEXTVAL INTO l_response_header_id FROM DUAL;
INSERT INTO WSH_ITM_RESPONSE_HEADERS
(
RESPONSE_HEADER_ID,
REQUEST_CONTROL_ID,
VENDOR_ID,
SERVICE_TYPE_CODE,
MESSAGE_ID,
RESPONSE_DATE,
ERROR_TYPE,
ERROR_CODE,
ERROR_TEXT,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
l_response_header_id,
l_reqclrt_ids,
l_vendor_id,
l_service_type_code,
p_message_id,
SYSDATE,
p_error_type,
p_error_code,
p_error_text,
SYSDATE,
1,
SYSDATE,
1,
1
);
UPDATE WSH_ITM_REQUEST_CONTROL SET
PROCESS_FLAG = 2,
RESPONSE_HEADER_ID = l_response_header_id
WHERE REQUEST_CONTROL_ID=l_reqclrt_ids;
SELECT WSH_ITM_RESPONSE_HEADERS_S.NEXTVAL INTO l_response_header_id FROM DUAL;
INSERT INTO WSH_ITM_RESPONSE_HEADERS
(
RESPONSE_HEADER_ID,
REQUEST_CONTROL_ID,
VENDOR_ID,
SERVICE_TYPE_CODE,
MESSAGE_ID,
RESPONSE_DATE,
ERROR_TYPE,
ERROR_CODE,
ERROR_TEXT,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
l_response_header_id,
l_reqclrt_id,
l_vendor_id,
l_service_type_code,
p_message_id,
SYSDATE,
p_error_type,
p_error_code,
p_error_text,
SYSDATE,
1,
SYSDATE,
1,
1
);
UPDATE WSH_ITM_REQUEST_CONTROL SET
PROCESS_FLAG = 2,
RESPONSE_HEADER_ID = l_response_header_id
WHERE REQUEST_CONTROL_ID = l_reqclrt_id;
END INSERT_ERROR;
** the Message Map has calls this procedure to update the process flag to
** '-4'(Marking the request record as ACKNOLEDGMENT received.
**
** p_message_id - MESSAGE ID for which acknowledgement is received.
**
*/
PROCEDURE PROCESS_ACK_RECEIPT(p_message_id IN VARCHAR2) IS
l_reqclrt_ids VARCHAR2(2000); --Bug 9226895
UPDATE WSH_ITM_REQUEST_CONTROL
SET PROCESS_FLAG = -4
WHERE REQUEST_CONTROL_ID = l_reqclrt_ids
AND PROCESS_FLAG IN (-2, -3);
UPDATE WSH_ITM_REQUEST_CONTROL
SET PROCESS_FLAG = -4
WHERE REQUEST_CONTROL_ID = l_reqclrt_id
AND PROCESS_FLAG IN (-2, -3);
** This procedure is used to update DENIED_PARTY_MATCH flag in WSH_ITM_RESPONSE_LINES
** Table if a DENIED_PARTY_MATCH tag is found in the response.
**
** p_response_line_id - RESPONSE LINE ID FOR WHICH DENIED PARTY HAS BEEN
** FOUND IN THE RECEIVED RESPONSE.
**
*/
PROCEDURE UPDATE_DENIED_PARTY_MATCH(p_response_line_id IN NUMBER) IS
--
l_debug_on BOOLEAN;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_DENIED_PARTY_MATCH';
UPDATE WSH_ITM_RESPONSE_LINES
SET DENIED_PARTY_FLAG = 'Y'
WHERE RESPONSE_LINE_ID = p_response_line_id;
END UPDATE_DENIED_PARTY_MATCH;
** NULL - NEW HEADER (INSERT DATA TO HDR TABLE)
**
*/
PROCEDURE GET_RESPONSE_HDR(p_request_control_id IN NUMBER,
x_response_header_id OUT NOCOPY NUMBER,
x_create_resp_hdr OUT NOCOPY NUMBER) IS
--
l_debug_on BOOLEAN;
SELECT RESPONSE_HEADER_ID INTO x_response_header_id
FROM WSH_ITM_REQUEST_CONTROL
WHERE REQUEST_CONTROL_ID = p_request_control_id
AND ROWNUM < 2;
SELECT WSH_ITM_RESPONSE_HEADERS_S.NEXTVAL INTO
x_response_header_id FROM DUAL;
SELECT REQUEST_SET_ID,
APPLICATION_ID
INTO l_request_set_id,
l_application_id
FROM WSH_ITM_REQUEST_CONTROL
WHERE REQUEST_CONTROL_ID = l_reqclrt_id;
SELECT REQUEST_SET_ID,
APPLICATION_ID
INTO l_request_set_id,
l_application_id
FROM WSH_ITM_REQUEST_CONTROL
WHERE REQUEST_CONTROL_ID = l_reqclrt_id;
SELECT NVL(REQUEST_SET_ID, 0),
APPLICATION_ID
INTO l_request_set_id,
l_application_id
FROM WSH_ITM_REQUEST_CONTROL
WHERE REQUEST_CONTROL_ID = p_reqCtrlID;
select value
into G_ITM_LOG_LEVEL
from WSH_ITM_PARAMETER_SETUPS_B
where parameter_name = 'WSH_ITM_LOG_SEVERITY';
WSH_DEBUG_SV.g_CallStack.DELETE;
SELECT Decode(InStr(p_reference_id, '-' ||p_request_ctrl_id),0, REPLACE(p_reference_id, p_request_ctrl_id||'-', ''),REPLACE(p_reference_id, '-'||p_request_ctrl_id, '') )
INTO
l_reference_id
FROM dual;