[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT Nvl(Max(INPUT_ID),0) + 1
INTO l_index
FROM EGO_PUB_WS_INPUT_IDENTIFIERS
WHERE session_id = p_session_id;
SELECT efvvb.version_seq_id
INTO l_version
FROM EGO_FLEX_VALUESET_VERSION_B efvvb
WHERE NVL(efvvb.end_active_date, sysdate) >= SYSDATE
AND efvvb.start_active_date <= SYSDATE
AND efvvb.FLEX_VALUE_SET_ID = p_vs_id
AND efvvb.version_seq_id > 0;
SELECT evsv.value_set_name
INTO x_vs_name
FROM EGO_VALUE_SETS_V evsv
WHERE evsv.value_set_id = to_number(l_vs_id);
SELECT evsv.value_set_id
INTO l_vs_id
FROM EGO_VALUE_SETS_V evsv
WHERE evsv.value_set_name = l_vs_name;
SELECT efvvb.version_seq_id
INTO x_vs_version
FROM EGO_flex_VALUESET_VERSION_B efvvb
where efvvb.FLEX_VALUE_SET_ID = x_vs_id
AND efvvb.version_seq_id = l_vs_version;
SELECT PK1_VALUE,
PK2_VALUE
FROM EGO_PUB_WS_ENTITIES
WHERE SESSION_ID = l_session_id
AND ENTITY_TYPE = 'VS'
AND NVL(USER_ENTERED, 'N') = 'N'
AND PARENT_SEQUENCE_ID IS NOT NULL
AND REF6_VALUE IS NOT NULL ;
SELECT entity_id
INTO l_entity_id
FROM EGO_PUB_BAT_ENTS_B
WHERE ENTITY_NAME='Value Set';
SELECT COUNT(*) INTO v_count
FROM EGO_PUB_BAT_ENT_OBJS_V
WHERE BATCH_ID = l_batch_id
AND PK1_VALUE = k.pk1_value
AND NVL(PK2_VALUE, 'PK2_VALUEXYZ') = NVL(k.pk2_value, 'PK2_VALUEXYZ');
SELECT EPWE.pk1_value vs_id
,EPWE.ref1_value vs_name
,EPWE.sequence_id sequence_id
,VS_REL.value_set_id child_vs_id
,FFVS.flex_value_set_name child_vs_name
FROM EGO_PUB_WS_ENTITIES EPWE
,EGO_VALUE_SET_EXT VS_REL
,FND_FLEX_VALUE_SETS FFVS
WHERE EPWE.session_id = p_session_id
AND EPWE.entity_type = G_ENTITY_TYPE_VS
AND EPWE.parent_sequence_id IS NULL
AND EPWE.pk1_value = VS_REL.parent_value_set_id
AND VS_REL.value_set_id = FFVS.flex_value_set_id;
SELECT 1
FROM EGO_PUB_WS_ENTITIES EPWE
WHERE EPWE.session_id = p_session_id
AND EPWE.entity_type = G_ENTITY_TYPE_VS
AND EPWE.parent_sequence_id IS NULL
AND EPWE.pk1_value = p_vs_id
;
SELECT WEB_SERVICE_NAME
INTO l_web_service_name
FROM EGO_PUB_WS_PARAMS
WHERE SESSION_ID = p_session_id;
SELECT numeric_value
INTO l_batch_id
FROM EGO_PUB_WS_CONFIG
WHERE PARAMETER_NAME = 'BATCH_ID'
AND session_id = p_session_id;
Insert_VS_Entity ( p_session_id => p_session_id
,p_vs_id => rec_get_vs_to_explode.child_vs_id
,p_vs_name => rec_get_vs_to_explode.child_vs_name
,p_vs_version => NULL
,p_user_entered_flag => 'N'
);
INSERT INTO EGO_PUB_WS_ENTITIES(SESSION_ID,
ODI_SESSION_ID,
SEQUENCE_ID,
PARENT_SEQUENCE_ID,
ENTITY_TYPE,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE,
REF1_VALUE,
REF2_VALUE,
REF3_VALUE,
REF4_VALUE,
REF5_VALUE,
REF6_VALUE,
REF7_VALUE,
REF8_VALUE,
REF9_VALUE,
REF10_VALUE,
CREATION_DATE,
CREATED_BY,
USER_ENTERED)
VALUES( p_session_id,
p_session_id,
EGO_PUB_WS_ENTITIES_S.nextval,
rec_get_vs_to_explode.sequence_id,
G_ENTITY_TYPE_VS,
rec_get_vs_to_explode.child_vs_id,
NULL,
NULL,
NULL,
NULL,
rec_get_vs_to_explode.child_vs_name,
NULL,
NULL,
NULL,
NULL,
G_ENTITY_TYPE_CHILD_VS,
NULL,
NULL,
NULL,
NULL,
SYSDATE,
0,
'N'
);
debug(2,l_proc_name||'Inserted child vs entity');
SELECT CHAR_VALUE
INTO l_mode
FROM EGO_PUB_WS_CONFIG
WHERE PARAMETER_NAME = 'MODE'
AND session_id = p_session_id;
SELECT WEB_SERVICE_NAME
INTO l_web_service_name
FROM EGO_PUB_WS_PARAMS
WHERE SESSION_ID = p_session_id;
SELECT numeric_value
INTO l_batch_id
FROM EGO_PUB_WS_CONFIG
WHERE PARAMETER_NAME = 'BATCH_ID'
AND session_id = p_session_id;
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,
'CHILD_VALUESETS',
2,
l_explode_child_vs,
sysdate,
fnd_global.user_id,
l_web_service_name);
debug(3, l_proc_name||'Inserted CHILD_VALUESETS=>'||l_explode_child_vs);
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,
'TRIGGER_IMPORT',
2,
NVL(l_trigger_import,'N'),
sysdate,
fnd_global.user_id,
l_web_service_name);
debug(3, l_proc_name||'Inserted TRIGGER_IMPORT=>'||NVL(l_trigger_import,'N'));
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,
EGO_PUB_WS_UTIL.G_SYNC_PARAM,
2,
l_publish_sync,
sysdate,
fnd_global.user_id,
l_web_service_name);
debug(3, l_proc_name||'Inserted SYNC=>'||l_publish_sync);
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,
'RETURN_PAYLOAD',
2,
l_return_payload,
sysdate,
fnd_global.user_id,
l_web_service_name);
debug(3, l_proc_name||'Inserted RETURN_PAYLOAD=>'||l_publish_sync);
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,
l_param_names(position),
2,
l_config_option,
sysdate,
fnd_global.user_id,
l_web_service_name);
SELECT 1
FROM DUAL WHERE EXISTS (
SELECT NULL
FROM ego_value_set_ext evse
WHERE evse.value_set_id = p_valueset_id
);
PROCEDURE Insert_Vs_Entity ( p_session_id IN NUMBER
,p_vs_id IN NUMBER
,p_vs_name IN VARCHAR2
,p_vs_version IN NUMBER
,p_user_entered_flag IN VARCHAR2
)
IS
l_proc_name VARCHAR2(50) := 'Insert_Vs_Entity=>';
SELECT epwe.pk1_value
INTO l_derived_vs_id
FROM EGO_PUB_WS_ENTITIES epwe
WHERE epwe.pk1_value = p_vs_id
AND NVL(epwe.pk2_value, '-999') = NVL(p_vs_version, -999)
AND epwe.session_id = p_session_id
AND epwe.entity_type = G_ENTITY_TYPE_VS;
INSERT INTO EGO_PUB_WS_ENTITIES(SESSION_ID,
ODI_SESSION_ID,
SEQUENCE_ID,
PARENT_SEQUENCE_ID,
ENTITY_TYPE,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE,
REF1_VALUE,
REF2_VALUE,
REF3_VALUE,
REF4_VALUE,
REF5_VALUE,
REF6_VALUE,
REF7_VALUE,
REF8_VALUE,
REF9_VALUE,
REF10_VALUE,
CREATION_DATE,
CREATED_BY,
USER_ENTERED)
VALUES( p_session_id,
p_session_id,
EGO_PUB_WS_ENTITIES_S.nextval,
NULL,
G_ENTITY_TYPE_VS,
p_vs_id,
p_vs_version,
NULL,
NULL,
NULL,
p_vs_name,
NULL,
NULL,
NULL,
NULL,
l_ref6_value,
NULL,
NULL,
NULL,
NULL,
SYSDATE,
0,
p_user_entered_flag
);
END Insert_Vs_Entity;
SELECT pk1_value,
pk2_value,
pk3_value
FROM EGO_PUB_BAT_ENT_OBJS_V
WHERE batch_id = p_batch_id
AND user_entered = 'Y'
;
SELECT numeric_value
INTO l_batch_id
FROM EGO_PUB_WS_CONFIG
WHERE PARAMETER_NAME = 'BATCH_ID'
AND session_id = p_session_id;
Insert_Vs_Entity ( p_session_id => p_session_id
,p_vs_id => l_vs_id
,p_vs_name => l_vs_name
,p_vs_version => l_vs_version
,p_user_entered_flag => 'Y'
);
SELECT Decode(existsNode(vs_id, 'ValuesetIdentifier/ValueSetId'),
1, Nvl(extractValue(vs_id, 'ValuesetIdentifier/ValueSetId'), -1),
0, -1)
BULK COLLECT INTO l_vs_id_tab
FROM( SELECT Value(vsid) vs_id
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, 'ValuesetQueryParam/ValuesetIdentifiersList/ValuesetIdentifier') )) vsid
WHERE session_id=p_session_id
);
SELECT Decode(existsNode(vs_name, 'ValuesetIdentifier/ValueSetName'), 1, extractValue(vs_name, 'ValuesetIdentifier/ValueSetName'), 0, NULL)
BULK COLLECT INTO l_vs_name_tab
FROM( SELECT Value(vsname) vs_name
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, 'ValuesetQueryParam/ValuesetIdentifiersList/ValuesetIdentifier') )) vsname
WHERE session_id=p_session_id
);
SELECT Decode(existsNode(vs_vers, 'ValuesetIdentifier/VersionSeqId'),
1, extractValue(vs_vers, 'ValuesetIdentifier/VersionSeqId'),
0, NULL)
BULK COLLECT INTO l_vs_vers_tab
FROM( SELECT Value(vsvers) vs_vers
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, 'ValuesetQueryParam/ValuesetIdentifiersList/ValuesetIdentifier') )) vsvers
WHERE session_id=p_session_id
);
Insert_VS_Entity ( p_session_id => p_session_id
,p_vs_id => l_vs_id
,p_vs_name => l_vs_name
,p_vs_version => l_vs_version
,p_user_entered_flag => 'Y'
);
SELECT COUNT(1)
INTO l_param_count
FROM EGO_PUB_WS_CONFIG
WHERE SESSION_ID = p_session_id
AND PARAMETER_NAME NOT IN('ODI_SESSION_ID', 'SYSTEM_CODE', 'MODE', 'BATCH_ID');
SELECT count(1)
INTO l_entity_count
from EGO_PUB_WS_ENTITIES
WHERE session_id = p_session_id
AND ENTITY_TYPE = G_ENTITY_TYPE_VS;