[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NUMERIC_VALUE
INTO x_param_value
FROM EGO_PUB_WS_CONFIG
WHERE PARAMETER_NAME = l_param_name
AND SESSION_ID = p_session_id;
SELECT NUMERIC_VALUE
INTO x_param_value
FROM EGO_PUB_BAT_PARAMS_B
WHERE PARAMETER_NAME = l_param_name
AND DATA_TYPE = 1
AND TYPE_ID = l_batch_id;
SELECT CHAR_VALUE
INTO x_param_value
FROM EGO_PUB_BAT_PARAMS_B
WHERE PARAMETER_NAME = l_param_name
AND DATA_TYPE = 2
AND TYPE_ID = l_batch_id;
SELECT CHAR_VALUE
INTO x_param_value
FROM EGO_PUB_WS_CONFIG
WHERE PARAMETER_NAME = l_param_name
AND SESSION_ID = p_session_id;
SELECT DATE_VALUE
INTO x_param_value
FROM EGO_PUB_WS_CONFIG
WHERE PARAMETER_NAME = l_param_name
AND SESSION_ID = p_session_id;
SELECT DATE_VALUE
INTO x_param_value
FROM EGO_PUB_BAT_PARAMS_B
WHERE PARAMETER_NAME = l_param_name
AND DATA_TYPE = 3
AND TYPE_ID = l_batch_id;
select extractValue(xmlcontent, p_search_str)
into x_value
from EGO_PUB_WS_PARAMS
where session_id = p_session_id;
SELECT existsNode(xmlcontent, p_search_str)
INTO l_exists
FROM EGO_PUB_WS_PARAMS
WHERE session_id = p_session_id;
/*SELECT Nvl(extractValue(xmlcontent,p_search_str),-1)
INTO l_batch_id
FROM EGO_PUB_WS_PARAMS
WHERE session_id = p_session_id;*/
/*SELECT CHAR_VALUE
INTO l_mode
FROM EGO_PUB_WS_CONFIG
WHERE session_id = p_session_id;*/
SELECT fnd_user_name
INTO l_fnd_user_name
FROM EGO_PUB_WS_PARAMS
WHERE session_id = p_session_id;
/*INSERT INTO tr_temp (Session_id, message)
values (p_session_id, 'FND_USER_NAME:' || l_fnd_user_name);*/
INSERT INTO EGO_PUB_WS_CONFIG ( session_id,
odi_session_id,
Parameter_Name,
Data_Type,
Char_value,
creation_date,
created_by,
web_service_name)
VALUES( p_session_id,
p_session_id,
'FND_USER_NAME',
2,
l_fnd_user_name,
sysdate,
0,
l_web_service_name);
SELECT responsibility_name
INTO l_responsibility_name
FROM EGO_PUB_WS_PARAMS
WHERE session_id = p_session_id;
/*INSERT INTO tr_temp (Session_id, message)
values (p_session_id, 'RESPONSIBILITY_NAME:' || l_responsibility_name);*/
INSERT INTO EGO_PUB_WS_CONFIG( session_id,
odi_session_id,
Parameter_Name,
Data_Type,
Char_value,
creation_date,
created_by,
web_service_name)
VALUES (p_session_id,
p_session_id,
'RESPONSIBILITY_NAME',
2,
l_responsibility_name,
sysdate,
0,
l_web_service_name);
SELECT responsibility_appl_name
INTO l_responsibility_appl_name
FROM EGO_PUB_WS_PARAMS
WHERE session_id = p_session_id;
/*INSERT INTO tr_temp (Session_id, message)
values (p_session_id, 'RESPONSIBILITY_APPL_NAME:' || l_responsibility_appl_name);*/
INSERT INTO EGO_PUB_WS_CONFIG( session_id,
odi_session_id,
Parameter_Name,
Data_Type,
Char_value,
creation_date,
created_by,
web_service_name)
VALUES (p_session_id,
p_session_id,
'RESPONSIBILITY_APPL_NAME',
2,
l_responsibility_appl_name,
sysdate,
0,
l_web_service_name);
SELECT security_group_name
INTO l_security_group_name
FROM EGO_PUB_WS_PARAMS
WHERE session_id = p_session_id;
/*INSERT INTO tr_temp (Session_id, message)
values (p_session_id, 'SECURITY_GROUP_NAME:' || l_security_group_name);*/
INSERT INTO EGO_PUB_WS_CONFIG( session_id,
odi_session_id,
Parameter_Name,
Data_Type,
Char_value,
creation_date,
created_by,
web_service_name)
VALUES (p_session_id,
p_session_id,
'SECURITY_GROUP_NAME',
2,
l_security_group_name,
sysdate,
0,
l_web_service_name);
SELECT created_by, responsibility_id
INTO l_user_id, l_responsibility_id
FROM EGO_PUB_BAT_HDR_B
WHERE batch_id = l_batch_id;
SELECT USER_NAME
INTO l_fnd_user_name
FROM fnd_user
WHERE user_id = l_user_id;
/*INSERT INTO tr_temp (Session_id, message)
values (p_session_id, 'FND_USER_NAME:' || l_fnd_user_name);*/
INSERT INTO EGO_PUB_WS_CONFIG( session_id,
odi_session_id,
Parameter_Name,
Data_Type,
Char_value,
creation_date,
created_by,
web_service_name)
VALUES (p_session_id,
p_session_id,
'FND_USER_NAME',
2,
l_fnd_user_name,
sysdate,
0,
l_web_service_name);
SELECT responsibility_key
INTO l_responsibility_name
FROM FND_RESPONSIBILITY
WHERE responsibility_id = l_responsibility_id;
/*INSERT INTO tr_temp (Session_id, message)
values (p_session_id, 'RESPONSIBILITY_NAME:' || l_responsibility_name);*/
INSERT INTO EGO_PUB_WS_CONFIG( session_id,
odi_session_id,
Parameter_Name,
Data_Type,
Char_value,
creation_date,
created_by,
web_service_name)
VALUES (p_session_id,
p_session_id,
'RESPONSIBILITY_NAME',
2,
l_responsibility_name,
sysdate,
0,
l_web_service_name);
SELECT application_id
INTO l_application_id
FROM FND_RESPONSIBILITY
WHERE responsibility_id = l_responsibility_id;
SELECT APPLICATION_SHORT_NAME
INTO l_responsibility_appl_name
FROM FND_APPLICATION
WHERE application_id = l_application_id;
/*INSERT INTO tr_temp (Session_id, message)
values (p_session_id, 'RESPONSIBILITY_APPL_NAME:' || l_responsibility_appl_name);*/
/*INSERT INTO tr_temp (Session_id, message)
values (p_session_id, 'SECURITY_GROUP_NAME:' || l_security_group_name);*/
INSERT INTO EGO_PUB_WS_CONFIG( session_id,
odi_session_id,
Parameter_Name,
Data_Type,
Char_value,
creation_date,
created_by,
web_service_name)
VALUES (p_session_id,
p_session_id,
'RESPONSIBILITY_APPL_NAME',
2,
l_responsibility_appl_name,
sysdate,
0,
l_web_service_name);
INSERT INTO EGO_PUB_WS_CONFIG( session_id,
odi_session_id,
Parameter_Name,
Data_Type,
Char_value,
creation_date,
created_by,
web_service_name)
VALUES (p_session_id,
p_session_id,
'SECURITY_GROUP_NAME',
2,
l_security_group_name,
sysdate,
0,
l_web_service_name);
SELECT RESPONSIBILITY_ID
INTO x_resp_id
FROM FND_RESPONSIBILITY
WHERE RESPONSIBILITY_KEY = p_resp_name;
SELECT Nvl(Max(INPUT_ID),0) + 1
INTO l_index
FROM EGO_PUB_WS_INPUT_IDENTIFIERS
WHERE session_id = p_session_id;
SELECT APPLICATION_ID
INTO x_resp_appl_id
FROM FND_APPLICATION
WHERE APPLICATION_SHORT_NAME = p_resp_appl_name;
SELECT Nvl(Max(INPUT_ID),0) + 1
INTO l_index
FROM EGO_PUB_WS_INPUT_IDENTIFIERS
WHERE SESSION_ID = p_session_id;
SELECT SECURITY_GROUP_ID
INTO x_sec_grp_id
FROM FND_SECURITY_GROUPS
WHERE SECURITY_GROUP_KEY = p_sec_grp_name;
SELECT Nvl(Max(INPUT_ID),0) + 1 into l_index
FROM EGO_PUB_WS_INPUT_IDENTIFIERS
WHERE session_id = p_session_id;
SELECT CHAR_VALUE
INTO l_user_name
FROM EGO_PUB_WS_CONFIG
WHERE SESSION_ID = p_session_id
AND WEB_SERVICE_NAME = l_web_service_name
AND parameter_name = 'FND_USER_NAME';
SELECT USER_ID
INTO l_user_id
FROM FND_USER
WHERE USER_NAME = l_user_name;
SELECT CHAR_VALUE
INTO l_responsibility_name
FROM EGO_PUB_WS_CONFIG
WHERE SESSION_ID = p_session_id
AND WEB_SERVICE_NAME = l_web_service_name
AND PARAMETER_NAME = 'RESPONSIBILITY_NAME';
SELECT CHAR_VALUE
INTO l_responsibility_appl_name
FROM EGO_PUB_WS_CONFIG
WHERE SESSION_ID = p_session_id
AND WEB_SERVICE_NAME = l_web_service_name
AND PARAMETER_NAME = 'RESPONSIBILITY_APPL_NAME';
SELECT CHAR_VALUE
INTO l_security_group_name
FROM EGO_PUB_WS_CONFIG
WHERE SESSION_ID = p_session_id
AND WEB_SERVICE_NAME = l_web_service_name
AND PARAMETER_NAME = 'SECURITY_GROUP_NAME';
/*INSERT INTO tr_temp (Session_id, message)
values (p_session_id, 'FND_SEC Userid: ' || l_user_id);
INSERT INTO tr_temp (Session_id, message)
values (p_session_id, 'FND_SEC respid: ' || l_responsibility_id);
INSERT INTO tr_temp (Session_id, message)
values (p_session_id, 'FND_SEC appid: ' || l_application_id);
SELECT orig_system
INTO p_system_code
FROM hz_orig_systems_b where orig_system = l_temp;
SELECT orig_system
INTO p_system_code
FROM hz_orig_systems_b where orig_system = upper(p_system_name);
SELECT Nvl(Max(INPUT_ID),0) + 1
INTO l_index
FROM EGO_PUB_WS_INPUT_IDENTIFIERS
WHERE session_id = p_session_id;
SELECT CHAR_VALUE
INTO l_mode
FROM EGO_PUB_WS_CONFIG
WHERE PARAMETER_NAME = 'MODE'
AND session_id = p_session_id;
SELECT extractValue(system_code, '/SystemCode')
BULK COLLECT INTO l_system_code_tab
FROM (SELECT Value(systemcode) system_code
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, l_systemcode_xpath) )) systemcode
WHERE session_id=p_session_id
);
SELECT extractValue(system_name, '/SystemName')
BULK COLLECT INTO l_system_name_tab
FROM (SELECT Value(systemname) system_name
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, l_systemname_xpath) )) systemname
WHERE session_id=p_session_id
);
/*INSERT INTO tr_temp (Session_id, message)
values (p_session_id, ' l_temp_code: ' || l_temp_code);
INSERT INTO tr_temp (Session_id, message)
values (p_session_id, ' l_lang_name_tab(i): ' || l_lang_name_tab(i));
INSERT INTO tr_temp (Session_id, message)
values (p_session_id, ' l_temp_name: ' || l_temp_name);
SELECT Nvl(Max(INPUT_ID),0) + 1
INTO l_index
FROM EGO_PUB_WS_INPUT_IDENTIFIERS
WHERE session_id = p_session_id;
SELECT Nvl(Max(INPUT_ID),0) + 1
INTO l_index
FROM EGO_PUB_WS_INPUT_IDENTIFIERS
WHERE session_id = p_session_id;
/*INSERT INTO tr_temp (Session_id, message)
values (p_session_id, 'SYSTEM_CODE:' || l_system_code_tab(i));*/
INSERT INTO EGO_PUB_WS_CONFIG ( session_id,
odi_session_id,
Parameter_Name,
Data_Type,
Date_Value,
Char_value,
Numeric_Value,
creation_date,
created_by,
web_service_name)
VALUES (p_session_id,
p_session_id,
'SYSTEM_CODE',
2,
NULL,
l_system_code_tab(i),
NULL,
SYSDATE,
G_CURRENT_USER_ID,
p_web_service_name);
SELECT NUMERIC_VALUE
INTO l_batch_id
FROM EGO_PUB_WS_CONFIG
WHERE PARAMETER_NAME = 'BATCH_ID'
AND session_id = p_session_id;
SELECT system_code
BULK COLLECT INTO l_system_code_tab
FROM EGO_PUB_BAT_SYSTEMS_B
WHERE batch_id = l_batch_id;
/*INSERT INTO tr_temp (Session_id, message)
values (p_session_id, 'SYSTEM_CODE:' || l_system_code_tab(i));*/
INSERT INTO EGO_PUB_WS_CONFIG ( session_id,
odi_session_id,
Parameter_Name,
Data_Type,
Date_Value,
Char_value,
Numeric_Value,
creation_date,
created_by,
web_service_name)
VALUES (p_session_id,
p_session_id,
'SYSTEM_CODE',
2,
NULL,
l_system_code_tab(i),
NULL,
SYSDATE,
G_CURRENT_USER_ID,
p_web_service_name);
/*FOR i IN (SELECT orig_system FROM HZ_ORIG_SYSTEMS_B WHERE ( ( END_DATE_ACTIVE IS NULL OR TRUNC(END_DATE_ACTIVE) >=TRUNC(SYSDATE) )
AND ( TRUNC(START_DATE_ACTIVE) <= TRUNC(SYSDATE) )
AND ( STATUS = 'A' )
AND ( CREATED_BY_MODULE LIKE 'EGO_%' ) ) )
LOOP
INSERT INTO tr_temp (Session_id, message)
values (p_session_id, 'SYSTEM_CODE:' || i.orig_system);
INSERT INTO EGO_PUB_WS_CONFIG ( session_id,
odi_session_id,
Parameter_Name,
Data_Type,
Date_Value,
Char_value,
Numeric_Value,
creation_date,
created_by,
web_service_name)
VALUES (p_session_id,
p_odi_session_id,
'SYSTEM_CODE',
2,
NULL,
i.orig_system,
NULL,
SYSDATE,
G_CURRENT_USER_ID,
p_web_service_name);
SELECT Nvl(Max(INPUT_ID),0) + 1
INTO l_index
FROM EGO_PUB_WS_INPUT_IDENTIFIERS
WHERE session_id = p_session_id;
select language_code
into p_language_code
from fnd_languages where language_code = l_temp;
select language_code
into p_language_code
from fnd_languages where nls_language = upper(p_language_name);
SELECT Nvl(Max(INPUT_ID),0) + 1 into l_index
FROM EGO_PUB_WS_INPUT_IDENTIFIERS
WHERE session_id = p_session_id;
SELECT extractValue(lang_code, '/LanguageCode')
BULK COLLECT INTO l_lang_code_tab
FROM (SELECT Value(langcode) lang_code
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, l_langcode_xpath) )) langcode
WHERE session_id=p_session_id
);
SELECT extractValue(lang_name, '/LanguageName')
BULK COLLECT INTO l_lang_name_tab
FROM (SELECT Value(langname) lang_name
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, l_langname_xpath) )) langname
WHERE session_id=p_session_id
);
/*INSERT INTO tr_temp (Session_id, message)
values (p_session_id, ' l_temp_code: ' || l_temp_code);
INSERT INTO tr_temp (Session_id, message)
values (p_session_id, ' l_lang_name_tab(i): ' || l_lang_name_tab(i));
INSERT INTO tr_temp (Session_id, message)
values (p_session_id, ' l_temp_name: ' || l_temp_name);
SELECT Nvl(Max(INPUT_ID),0) + 1
INTO l_index
FROM EGO_PUB_WS_INPUT_IDENTIFIERS
WHERE session_id = p_session_id;
SELECT Nvl(Max(INPUT_ID),0) + 1
INTO l_index
FROM EGO_PUB_WS_INPUT_IDENTIFIERS
WHERE session_id = p_session_id;
/*INSERT INTO tr_temp (Session_id, message)
values (p_session_id, 'LANGUAGE_CODE:' || l_lang_code_tab(i));*/
INSERT INTO EGO_PUB_WS_CONFIG ( session_id,
odi_session_id,
Parameter_Name,
Data_Type,
Date_Value,
Char_value,
Numeric_Value,
creation_date,
created_by,
web_service_name)
VALUES (p_session_id,
p_session_id,
'LANGUAGE_CODE',
2,
NULL,
l_lang_code_tab(i),
NULL,
SYSDATE,
G_CURRENT_USER_ID,
p_web_service_name);
FOR i IN (SELECT language_code FROM FND_LANGUAGES WHERE INSTALLED_FLAG IN ('I','B') ) LOOP
/*INSERT INTO tr_temp (Session_id, message)
values (p_session_id, 'LANGUAGE_CODE:' || i.language_code);
INSERT INTO EGO_PUB_WS_CONFIG ( session_id,
odi_session_id,
Parameter_Name,
Data_Type,
Date_Value,
Char_value,
Numeric_Value,
creation_date,
created_by,
web_service_name)
VALUES (p_session_id,
p_session_id,
'LANGUAGE_CODE',
2,
NULL,
i.language_code,
NULL,
SYSDATE,
G_CURRENT_USER_ID,
p_web_service_name);
SELECT Nvl(Max(INPUT_ID),0) + 1
INTO l_index
FROM EGO_PUB_WS_INPUT_IDENTIFIERS
WHERE session_id = p_session_id;
/*Procedure to insert records into Input Identifiers table*/
PROCEDURE Populate_Input_Identifier(p_session_id IN NUMBER,
p_input_id IN NUMBER,
p_param_name IN VARCHAR2,
p_param_value IN VARCHAR2)
IS
BEGIN
INSERT INTO EGO_PUB_WS_INPUT_IDENTIFIERS(session_id,
odi_session_id,
input_id,
param_name,
param_value,
creation_date,
created_by)
VALUES(p_session_id,
p_session_id,
p_input_id,
p_param_name,
p_param_value,
SYSDATE,
-1);
INSERT INTO EGO_PUB_WS_ERRORS(session_id,
odi_session_id,
input_id,
err_code,
err_message,
creation_date,
created_by)
VALUES(p_session_id,
p_session_id,
p_input_id,
p_err_code,
p_err_message,
SYSDATE,
-1);
SELECT CHAR_VALUE
FROM EGO_PUB_WS_CONFIG
WHERE SESSION_ID = l_session_id
AND PARAMETER_NAME = 'SYSTEM_CODE';
SELECT PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE,
USER_ENTERED
FROM EGO_PUB_WS_ENTITIES
WHERE SESSION_ID = l_session_id;
SELECT SESSION_ID,
INPUT_ID,
SYSTEM_CODE,
PARAM_NAME,
PARAM_VALUE,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE
FROM EGO_PUB_WS_INPUT_IDENTIFIERS
WHERE SESSION_ID = l_session_id
AND SYSTEM_CODE = l_system_code;
SELECT SESSION_ID,
INPUT_ID,
SYSTEM_CODE,
ERR_CODE,
ERR_MESSAGE
FROM EGO_PUB_WS_ERRORS
WHERE SESSION_ID = l_session_id
AND INPUT_ID = l_input_id
AND SYSTEM_CODE = l_system_code;
SELECT BATCH_ID,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE,
SYSTEM_CODE,
STATUS,
MESSAGE
FROM EGO_PUBLICATION_BATCH_GT
WHERE BATCH_ID = l_batch_id;
SELECT CHAR_VALUE
INTO l_trigger_import
FROM EGO_PUB_WS_CONFIG
WHERE SESSION_ID = p_session_id
AND PARAMETER_NAME = 'TRIGGER_IMPORT';
SELECT count(1)
INTO l_invld_sys_cnt
FROM EGO_PUB_WS_ERRORS
WHERE SESSION_ID = p_session_id
AND ERR_CODE = 'D'
AND SYSTEM_CODE = l_system_code;
INSERT INTO EGO_PUBLICATION_BATCH_GT
(BATCH_ID,
BATCH_ENTITY_OBJECT_ID,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE,
USER_ENTERED,
SYSTEM_CODE,
STATUS,
MESSAGE,
PROCESS_FLAG,
RETURN_STATUS,
RETURN_ERROR_MESSAGE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES (l_batch_id,
NULL,
ent.PK1_VALUE,
ent.PK2_VALUE,
ent.PK3_VALUE,
ent.PK4_VALUE,
ent.PK5_VALUE,
ent.USER_ENTERED,
l_system_code,
'I',
l_message,
NULL,
NULL,
NULL,
-1,
SYSDATE,
-1,
SYSDATE
);
INSERT INTO EGO_PUBLICATION_BATCH_GT
(BATCH_ID,
BATCH_ENTITY_OBJECT_ID,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE,
USER_ENTERED,
SYSTEM_CODE,
STATUS,
MESSAGE,
PROCESS_FLAG,
RETURN_STATUS,
RETURN_ERROR_MESSAGE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES (l_batch_id,
NULL,
ent.PK1_VALUE,
ent.PK2_VALUE,
ent.PK3_VALUE,
ent.PK4_VALUE,
ent.PK5_VALUE,
ent.USER_ENTERED,
l_system_code,
'S',
NULL,
NULL,
NULL,
NULL,
-1,
SYSDATE,
-1,
SYSDATE
);
INSERT INTO EGO_PUBLICATION_BATCH_GT
( BATCH_ID,
BATCH_ENTITY_OBJECT_ID,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE,
USER_ENTERED,
SYSTEM_CODE,
STATUS,
MESSAGE,
PROCESS_FLAG,
RETURN_STATUS,
RETURN_ERROR_MESSAGE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES (l_batch_id,
NULL,
ent.PK1_VALUE,
ent.PK2_VALUE,
ent.PK3_VALUE,
ent.PK4_VALUE,
ent.PK5_VALUE,
ent.USER_ENTERED,
l_system_code,
'F',
l_message,
NULL,
NULL,
NULL,
-1,
SYSDATE,
-1,
SYSDATE
);
SELECT count(1)
INTO l_invld_sys_cnt
FROM EGO_PUB_WS_ERRORS
WHERE SESSION_ID = p_session_id
AND ERR_CODE = 'D'
AND SYSTEM_CODE = l_system_code;
SELECT count(1)
INTO l_error_rec_count
FROM EGO_PUB_WS_ERRORS
WHERE session_id = p_session_id
AND system_code = l_system_code
AND input_id = l_input_id;
UPDATE EGO_PUBLICATION_BATCH_GT
SET STATUS = 'F', MESSAGE = l_message
WHERE BATCH_ID = l_batch_id
AND PK1_VALUE = ip.PK1_VALUE
AND NVL(PK2_VALUE,'pk2_value') = NVL(ip.PK2_VALUE,'pk2_value')
AND NVL(PK3_VALUE,'pk3_value') = NVL(ip.PK3_VALUE,'pk3_value')
AND NVL(PK4_VALUE,'pk4_value') = NVL(ip.PK4_VALUE,'pk4_value')
AND NVL(PK5_VALUE,'pk5_value') = NVL(ip.PK5_VALUE,'pk5_value')
AND SYSTEM_CODE = ip.SYSTEM_CODE;
UPDATE EGO_PUB_WS_ERRORS
SET ERR_MESSAGE = l_message
WHERE SESSION_ID = p_session_id
AND INPUT_ID = l_input_id
AND SYSTEM_CODE = l_system_code;
UPDATE EGO_PUBLICATION_BATCH_GT
SET STATUS = 'F', MESSAGE = l_message
WHERE BATCH_ID = l_batch_id
AND PK1_VALUE = ip.PK1_VALUE
AND NVL(PK2_VALUE,'pk2_value') = NVL(ip.PK2_VALUE,'pk2_value')
AND NVL(PK3_VALUE,'pk3_value') = NVL(ip.PK3_VALUE,'pk3_value')
AND NVL(PK4_VALUE,'pk4_value') = NVL(ip.PK4_VALUE,'pk4_value')
AND NVL(PK5_VALUE,'pk5_value') = NVL(ip.PK5_VALUE,'pk5_value')
AND SYSTEM_CODE = ip.SYSTEM_CODE;
UPDATE EGO_PUB_WS_ERRORS
SET ERR_MESSAGE = l_message
WHERE SESSION_ID = p_session_id
AND INPUT_ID = l_input_id
AND SYSTEM_CODE = l_system_code;
UPDATE EGO_PUBLICATION_BATCH_GT
SET STATUS = 'W', MESSAGE = l_message
WHERE BATCH_ID = l_batch_id
AND PK1_VALUE = ip.PK1_VALUE
AND NVL(PK2_VALUE,'pk2_value') = NVL(ip.PK2_VALUE,'pk2_value')
AND NVL(PK3_VALUE,'pk3_value') = NVL(ip.PK3_VALUE,'pk3_value')
AND NVL(PK4_VALUE,'pk4_value') = NVL(ip.PK4_VALUE,'pk4_value')
AND NVL(PK5_VALUE,'pk5_value') = NVL(ip.PK5_VALUE,'pk5_value')
AND SYSTEM_CODE = ip.SYSTEM_CODE;
UPDATE EGO_PUB_WS_ERRORS
SET ERR_MESSAGE = l_message
WHERE SESSION_ID = p_session_id
AND INPUT_ID = l_input_id
AND SYSTEM_CODE = l_system_code;
EGO_PUB_FWK_PK.Update_Pub_Status(l_batch_id, 2, bat_ent_stat_rec,x_bat_status_out,x_return_status,x_msg_count,x_msg_data);
SELECT application_id
INTO l_application_id
FROM fnd_application
WHERE application_short_name=p_appl_short_name;
SELECT CHAR_VALUE
FROM EGO_PUB_WS_CONFIG
WHERE SESSION_ID = l_session_id
AND PARAMETER_NAME = 'SYSTEM_CODE';
SELECT SESSION_ID,
INPUT_ID,
SYSTEM_CODE,
PARAM_NAME,
PARAM_VALUE,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE
FROM EGO_PUB_WS_INPUT_IDENTIFIERS
WHERE SESSION_ID = l_session_id
AND SYSTEM_CODE = l_system_code;
SELECT SESSION_ID,
INPUT_ID,
SYSTEM_CODE,
ERR_CODE,
ERR_MESSAGE
FROM EGO_PUB_WS_ERRORS
WHERE SESSION_ID = l_session_id
AND INPUT_ID = l_input_id
AND SYSTEM_CODE = l_system_code;
SELECT count(1)
INTO l_invld_sys_cnt
FROM EGO_PUB_WS_ERRORS
WHERE SESSION_ID = p_session_id
AND ERR_CODE = 'D'
AND SYSTEM_CODE = l_system_code;
UPDATE EGO_PUB_WS_ERRORS
SET ERR_MESSAGE = l_message
WHERE SESSION_ID = p_session_id
AND INPUT_ID = l_input_id
AND SYSTEM_CODE = l_system_code
AND ERR_CODE = err.ERR_CODE;
UPDATE EGO_PUB_WS_ERRORS
SET ERR_MESSAGE = l_message
WHERE SESSION_ID = p_session_id
AND INPUT_ID = l_input_id
AND SYSTEM_CODE = l_system_code;
SELECT CHAR_VALUE param_value
FROM ego_pub_ws_config
WHERE session_id = cp_session_id
AND upper(parameter_name) = 'RETURN_PAYLOAD';
SELECT session_id,input_id, err_code,err_message, 'US' language_code, system_code
FROM EGO_PUB_WS_ERRORS
WHERE session_id=cp_session_id
AND ERR_CODE IN('EGO_SYNC_ENTITY_ERROR', 'EGO_SYNC_MIB_STAT_1_ERROR');
SELECT session_id,input_id, err_code,err_message, 'US' language_code, system_code
FROM EGO_PUB_WS_ERRORS
WHERE session_id=cp_session_id
AND ERR_CODE = 'EGO_SYNC_ENTITY_WARNING';
SELECT session_id, param_name,param_value
FROM EGO_PUB_WS_INPUT_IDENTIFIERS
WHERE session_id=cp_session_id
AND input_id=cp_input_id
AND system_code= cp_system_code;
SELECT COUNT(1)
INTO l_no_of_entities
FROM EGO_PUB_WS_ENTITIES
WHERE ENTITY_TYPE = l_entity_type
AND SESSION_ID = l_session_id;
select x.xslcontent.getclobval()
into xslclob
from EGO_ODI_WS_XSL x
where web_service_name = l_web_service_name;
SELECT count(1)
INTO l_rec_count
FROM EGO_PUB_WS_OUTPUT
WHERE SESSION_ID = l_session_id;
UPDATE ego_pub_ws_output
SET xmlcontent = l_output_xml,
xml_odi = XmlType(l_xml),
creation_date = sysdate
WHERE session_id = l_session_id
AND web_service_name = l_web_service_name;
INSERT INTO ego_pub_ws_output (session_id,
odi_session_id,
web_service_name,
entity_type,
sequence_id,
xmlcontent,
xml_odi,
creation_date,
created_by)
VALUES (l_session_id,
l_session_id,
l_web_service_name,
l_entity_type,
0,
l_output_xml,
XmlType(l_xml),
sysdate,
0);