[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select inventory_item_id
INTO x_inv_item_id
from mtl_system_items_kfv
WHERE organization_id = p_org_id
AND inventory_item_id = to_number(l_inv_item_id);
select inventory_item_id
INTO x_inv_item_id
from mtl_system_items_kfv
WHERE organization_id = p_org_id
AND Nvl(segment1, 0) = Nvl(l_segment1, 0)
AND Nvl(segment2, 0) = Nvl(l_segment2, 0)
AND Nvl(segment3, 0) = Nvl(l_segment3, 0)
AND Nvl(segment4, 0) = Nvl(l_segment4, 0)
AND Nvl(segment5, 0) = Nvl(l_segment5, 0)
AND Nvl(segment6, 0) = Nvl(l_segment6, 0)
AND Nvl(segment7, 0) = Nvl(l_segment7, 0)
AND Nvl(segment8, 0) = Nvl(l_segment8, 0)
AND Nvl(segment9, 0) = Nvl(l_segment9, 0)
AND Nvl(segment10, 0) = Nvl(l_segment10, 0)
AND Nvl(segment11, 0) = Nvl(l_segment11, 0)
AND Nvl(segment12, 0) = Nvl(l_segment12, 0)
AND Nvl(segment13, 0) = Nvl(l_segment13, 0)
AND Nvl(segment14, 0) = Nvl(l_segment14, 0)
AND Nvl(segment15, 0) = Nvl(l_segment15, 0)
AND Nvl(segment16, 0) = Nvl(l_segment16, 0)
AND Nvl(segment17, 0) = Nvl(l_segment17, 0)
AND Nvl(segment18, 0) = Nvl(l_segment18, 0)
AND Nvl(segment19, 0) = Nvl(l_segment19, 0)
AND Nvl(segment20, 0) = Nvl(l_segment20, 0);
select organization_id
INTO x_organization_id
from mtl_parameters
WHERE organization_id = to_number(l_org_id);
select organization_id
INTO x_organization_id
from mtl_parameters
WHERE organization_code = p_org_code;
SELECT ATTR_GROUP_NAME
into x_ag_name
FROM ego_attr_groups_v
WHERE ATTR_GROUP_ID = to_number(l_ag_id);
SELECT ATTR_GROUP_NAME
INTO x_ag_name
FROM ego_attr_groups_v
WHERE ATTR_GROUP_NAME = l_ag_name;
SELECT Nvl(Max(INPUT_ID),0) + 1 into l_index
FROM EGO_PUB_WS_INPUT_IDENTIFIERS
WHERE session_id = p_session_id;
/*INSERT INTO emt_temp (Session_id, message)
values (p_session_id, 'Handling exception at Config_UDA: ' );
/*INSERT INTO emt_temp (Session_id, message)
values (4293, 'new inside l_str_name:' || p_str_name);
select ALTERNATE_DESIGNATOR_CODE
into p_str_name
from bom_alternate_designators
WHERE organization_id = p_org_id
AND ALTERNATE_DESIGNATOR_CODE = l_tmp_str_name;
SELECT Nvl(Max(INPUT_ID),0) + 1 into p_index
FROM EGO_PUB_WS_INPUT_IDENTIFIERS
WHERE session_id = p_session_id;
/*INSERT INTO emt_temp (Session_id, message)
values (4293, 'new inside l_str_name:' || p_str_name);
select alternate_bom_designator
into l_tmp_str_name
from bom_structures_b
where assembly_item_id = p_item_id
and organization_id = p_org_id
and alternate_bom_designator = l_tmp_str_name;
select alternate_bom_designator
into l_tmp_str_name
from bom_structures_b
where assembly_item_id = p_item_id
and organization_id = p_org_id
and alternate_bom_designator IS NULL;
/*SELECT Nvl(Max(INPUT_ID),0) + 1 into p_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 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 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 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;
PROCEDURE Insert_ODI_Parameter_List(p_session_id IN NUMBER,
p_xml_node_xpath VARCHAR2,
p_parameter_name VARCHAR2,
p_web_service_name VARCHAR2 DEFAULT NULL)
IS
l_node_name VARCHAR2(100);
select extractValue(val, xml_node_name) value
from
(select value(tags) val
from EGO_PUB_WS_PARAMS i,
table(XMLSequence(
extract(i.xmlcontent, xml_node_path))) tags
where 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,
p_parameter_name,
2,
r.value,
sysdate,
0,
p_web_service_name);
END Insert_ODI_Parameter_List;
select extractValue(xmlcontent, p_search_str)
into x_value
from EGO_PUB_WS_PARAMS
where session_id = p_session_id;
select extractValue(val, xml_node_name) value
from
(select value(tags) val
from EGO_PUB_WS_PARAMS i,
table(XMLSequence(
extract(i.xmlcontent, xml_node_path))) tags
where session_id = p_session_id);
select char_value
into l_mode
from EGO_PUB_WS_CONFIG
where parameter_name = 'MODE'
and web_service_name = 'GET_ITEM_STRUCTURE'
and session_id = p_session_id;
select to_number(extractValue(xmlcontent, '/structureQueryParameters/BatchStructureQueryParameters/BatchId'))
into l_batch_id
from EGO_PUB_WS_PARAMS
where session_id = p_session_id;
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 application_id
into l_application_id
from FND_RESPONSIBILITY
where responsibility_id = l_responsibility_id;
select char_value
into l_user_name
from EGO_PUB_WS_CONFIG
where session_id = p_session_id
and web_service_name = 'GET_ITEM_STRUCTURE'
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 = 'GET_ITEM_STRUCTURE'
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 = 'GET_ITEM_STRUCTURE'
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 = 'GET_ITEM_STRUCTURE'
and parameter_name = 'SECURITY_GROUP_NAME';
/*INSERT INTO emt_temp (Session_id, message)
values (p_session_id, 'FND_SEC Userid: ' || l_user_id);
INSERT INTO emt_temp (Session_id, message)
values (p_session_id, 'FND_SEC respid: ' || l_responsibility_id);
INSERT INTO emt_temp (Session_id, message)
values (p_session_id, 'FND_SEC appid: ' || l_application_id);
l_dynamic_update_sql VARCHAR2(32767);
/*INSERT INTO emt_temp (Session_id, message)
values (p_session_id, ' Starting of Check_security ');*/
/*INSERT INTO emt_temp (Session_id, message)
values (p_session_id, ' x_return_status: ' || x_return_status);
INSERT INTO emt_temp (Session_id, message)
values (p_session_id, ' l_sec_predicate: ' || l_sec_predicate);
l_dynamic_sql := ' select ITEM_ID, ITEM_ORG_ID, ITEM_REV, SEQUENCE_NUMBER ' ||
' from BOM_ODI_WS_ENTITIES i ' ||
' where i.session_id = :1 ' ||
' AND NOT ' || l_sec_predicate;
/*INSERT INTO emt_temp (Session_id, message)
values (p_session_id, ' l_dynamic_sql: ' || l_dynamic_sql);
/*INSERT INTO emt_temp (Session_id, message)
values (p_session_id, ' No publish privilege for item: ' || l_item_id);*/
/*l_dynamic_update_sql := ' delete from BOM_ODI_WS_ENTITIES i ' ||
' where i.session_id = :1 ' ||
' AND NOT ' || l_sec_predicate;*/
l_dynamic_update_sql := ' update BOM_ODI_WS_ENTITIES i ' ||
' set PUBLISH_FLAG = ''N'' ' ||
' where i.session_id = :1 ' ||
' AND nvl(i.PUBLISH_FLAG, ''Y'') = ''Y'' ' ||
' AND NOT ' || l_sec_predicate;
EXECUTE IMMEDIATE l_dynamic_update_sql
USING IN p_session_id;
FOR i IN (SELECT item_id, item_org_id, item_rev FROM BOM_ODI_WS_ENTITIES
where group_id = l_group_id and session_id = p_session_id)
--(SELECT pk1_value, pk2_value, pk3_value FROM EGO_ODI_WS_ENTITIES ent1
-- WHERE session_id = p_session_id and
-- SEQUENCE_NUMBER IN (
-- SELECT PK4_VALUE
-- FROM EGO_ODI_WS_ENTITIES ent2
-- WHERE PK1_VALUE = l_item_id
-- AND PK2_VALUE = l_org_id
-- AND ent1.session_id = ent2.session_id
-- ))
LOOP
--dbms_output.put_line('User did not have the privilege '||p_priv_check ||', on the the item '|| l_item_id);
select char_value
into l_mode
from EGO_PUB_WS_CONFIG
where parameter_name = 'MODE'
and session_id = p_session_id;
select to_number(extractValue(xmlcontent, '/structureQueryParameters/BatchStructureQueryParameters/BatchId'))
into l_batch_id
from EGO_PUB_WS_PARAMS
where session_id = p_session_id;
SELECT BATCH_ENTITY_OBJECT_ID
INTO l_batch_ent_obj_id
FROM Ego_Pub_Bat_Ent_Objs_v
WHERE batch_id = l_batch_id
AND PK1_VALUE = i.item_id
AND PK2_VALUE = i.item_org_id
AND PK3_VALUE = i.item_rev;
SELECT party_name INTO l_user_name
FROM EGO_USER_V WHERE USER_ID = FND_GLOBAL.USER_ID;
SELECT CHAR_VALUE INTO l_structure_name FROM EGO_PUB_BAT_PARAMS_B
WHERE type_id=l_batch_id AND Upper(parameter_name) ='STRUCTURE_NAME';
UPDATE EGO_PUB_BAT_STATUS_B
SET STATUS_CODE = 'F' , MESSAGE = 'User ' || l_user_name ||' does not have the publilsh privilege on few components of the structure ' ||
l_structure_name || ' for this Item.'
WHERE batch_id = l_batch_id AND BATCH_ENTITY_OBJECT_ID = l_batch_ent_obj_id;
l_dynamic_update_sql VARCHAR2(32767);
INSERT INTO emt_temp (Session_id, message)
values (p_session_id, ' Starting of Check_security ');
INSERT INTO emt_temp (Session_id, message)
values (p_session_id, ' x_return_status: ' || x_return_status);
INSERT INTO emt_temp (Session_id, message)
values (p_session_id, ' l_sec_predicate: ' || l_sec_predicate);
l_dynamic_sql := ' select i.row_id, e.COMPONENT_ITEM_ID, e.ORGANIZATION_ID, i.sequence_number, e.group_id ' ||
' from bom_explosions_all e, bom_odi_ws_revisions i ' ||
' where i.session_id = :1 ' ||
'and e.rowid = i.row_id' ||
' AND NOT ' || l_sec_predicate;
INSERT INTO emt_temp (Session_id, message)
values (p_session_id, ' Inside check_component_privilege: ');
INSERT INTO emt_temp (Session_id, message)
values (p_session_id, ' l_dynamic_sql: ' || l_dynamic_sql);
INSERT INTO emt_temp (Session_id, message)
values (p_session_id, ' Looping to generate error info: ');
INSERT INTO emt_temp (Session_id, message)
values (p_session_id, ' l_rowid: ' || l_rowid);
INSERT INTO emt_temp (Session_id, message)
values (p_session_id, ' l_item_id: ' || l_item_id);
INSERT INTO emt_temp (Session_id, message)
values (p_session_id, ' l_org_id: ' || l_org_id);
l_dynamic_update_sql := ' update BOM_ODI_WS_REVISIONS s ' ||
' set PUBLISH_FLAG = ''N'' ' ||
' where s.session_id = :1 ' ||
' AND nvl(s.PUBLISH_FLAG, ''Y'') = ''Y'' ' ||
' AND s.row_id =:2';
INSERT INTO emt_temp (Session_id, message)
values (p_session_id, ' l_dynamic_update_sql: ' || l_dynamic_update_sql);
EXECUTE IMMEDIATE l_dynamic_update_sql
USING IN p_session_id, l_rowid;
l_dynamic_update_sql VARCHAR2(32767);
/*INSERT INTO emt_temp (Session_id, message)
values (p_session_id, ' Starting of Check_Component_security ');
/*INSERT INTO emt_temp (Session_id, message)
values (p_session_id, ' x_return_status: ' || x_return_status);
INSERT INTO emt_temp (Session_id, message)
values (p_session_id, ' l_sec_predicate: ' || l_sec_predicate);
/*l_dynamic_sql := ' select e.COMPONENT_ITEM_ID, e.ORGANIZATION_ID, e.group_id ' ||
' from bom_explosions_all e ' ||
' where e.group_id = :1 ' ||
' AND NOT ' || l_sec_predicate;*/
l_dynamic_sql := ' select count(*) ' ||
' from bom_explosions_all e ' ||
' where e.group_id = :1 ' ||
' AND NOT ' || l_sec_predicate;
/*INSERT INTO emt_temp (Session_id, message)
values (p_session_id, 'components without publish privilege: ' || l_count);
l_dynamic_update_sql := ' update BOM_ODI_WS_ENTITIES i ' ||
' set PUBLISH_FLAG = ''N'' ' ||
' where i.session_id = :1 ' ||
' and i.odi_session_id = :2 ' ||
' and i.item_id = :3 ' ||
' and i.item_org_id = :4 ' ||
' and i.item_rev = :5 ' ||
' AND nvl(i.PUBLISH_FLAG, ''Y'') = ''Y'' ';
EXECUTE IMMEDIATE l_dynamic_update_sql
USING IN p_session_id, p_odi_session_id, p_inv_item_id, p_org_id, p_rev_id;
/*INSERT INTO emt_temp (Session_id, message)
values (p_session_id, ' Inside check_component_privilege: ');
INSERT INTO emt_temp (Session_id, message)
values (p_session_id, ' l_dynamic_sql: ' || l_dynamic_sql);
select count(*)
into l_exists
from EGO_PUB_WS_CONFIG
where session_id = p_session_id
and web_service_name = 'GET_ITEM_STRUCTURE'
and parameter_name = 'MODE'
and char_value = 'SUBROUTINE';
select existsNode(xmlcontent, '/structureQueryParameters/BatchStructureQueryParameters/BatchId')
into l_exists
from EGO_PUB_WS_PARAMS
where session_id = p_session_id;
select extractValue(xmlcontent, '/structureQueryParameters/BatchStructureQueryParameters/BatchId')
into l_tmp_val
from EGO_PUB_WS_PARAMS
where session_id = p_session_id;
select existsNode(xmlcontent, '/structureQueryParameters/OrganizationId')
into l_exists
from EGO_PUB_WS_PARAMS
where session_id = p_session_id;
select existsNode(xmlcontent, '/structureQueryParameters/OrganizationCode')
into l_exists1
from EGO_PUB_WS_PARAMS
where session_id = p_session_id;
select extractValue(xmlcontent, '/structureQueryParameters/OrganizationId')
into l_tmp_val
from EGO_PUB_WS_PARAMS
where session_id = p_session_id;
select extractValue(xmlcontent, '/structureQueryParameters/OrganizationCode')
into l_tmp_val
from EGO_PUB_WS_PARAMS
where session_id = p_session_id;
select count(*)
into l_exists
from EGO_PUB_WS_PARAMS
where session_id = p_session_id
and existsNode(xmlcontent, '/structureQueryParameters/ParametersForListOfItems/ListOfItemStructureQueryParams/OrganizationId')=1;
select count(*)
into l_exists1
from EGO_PUB_WS_PARAMS
where session_id = p_session_id
and existsNode(xmlcontent, '/structureQueryParameters/ParametersForListOfItems/ListOfItemStructureQueryParams/OrganizationCode')=1;
/*select extractValue(xmlcontent, '/structureQueryParameters/ParametersForListOfItems/ListOfItemStructureQueryParams/OrganizationId')
into l_tmp_val
from EGO_PUB_WS_PARAMS
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_code, '/LanguageName')
BULK COLLECT INTO l_lang_name_tab
FROM (SELECT Value(langcode) lang_code
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, l_langname_xpath) )) langcode
WHERE session_id=p_session_id
);
/*INSERT INTO emt_temp (Session_id, message)
values (p_session_id, ' l_temp_code: ' || l_temp_code);
INSERT INTO emt_temp (Session_id, message)
values (p_session_id, ' l_lang_name_tab(i): ' || l_lang_name_tab(i));
INSERT INTO emt_temp (Session_id, message)
values (p_session_id, ' l_temp_name: ' || l_temp_name);
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,
'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 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,
'LANGUAGE_CODE',
2,
NULL,
i.language_code,
NULL,
SYSDATE,
G_CURRENT_USER_ID,
p_web_service_name);
/*INSERT INTO emt_temp (Session_id, message)
values (p_session_id, 'Entering Config_UDAs ' || p_parameter_name);
SELECT extractValue(uda_ag, p_ag_id_node_tag)
BULK COLLECT INTO l_uda_attr_id_tab
FROM (SELECT Value(udaag) uda_ag
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, l_uda_id_xpath) )) udaag
WHERE session_id=p_session_id
);
SELECT extractValue(uda_ag, p_ag_name_node_tag)
BULK COLLECT INTO l_uda_attr_name_tab
FROM (SELECT Value(udaag) uda_ag
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, l_uda_name_xpath) )) udaag
WHERE session_id=p_session_id
);
/*INSERT INTO emt_temp (Session_id, message)
values (p_session_id, 'Inside attr_id loop l_uda_attr_id_tab(i) : ' || l_uda_attr_id_tab(i));
/*INSERT INTO emt_temp (Session_id, message)
values (p_session_id, 'x_ag_name: ' || x_ag_name);
INSERT INTO emt_temp (Session_id, message)
values (p_session_id, 'validation TRUE');
INSERT INTO emt_temp (Session_id, message)
values (p_session_id, 'validation FALSE');
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,
p_parameter_name,
2,
NULL,
x_ag_name,
NULL,SYSDATE,
G_CURRENT_USER_ID,
p_web_service_name);
/*INSERT INTO emt_temp (Session_id, message)
values (p_session_id, 'Inside attr_name loop l_uda_attr_name_tab(i) : ' || l_uda_attr_name_tab(i));
INSERT INTO emt_temp (Session_id, message)
values (p_session_id, 'validation TRUE');
INSERT INTO emt_temp (Session_id, message)
values (p_session_id, 'validation FALSE');
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,
p_parameter_name,
2,
NULL,
l_uda_attr_name_tab(i),
NULL,SYSDATE,
G_CURRENT_USER_ID,
p_web_service_name);
/* INSERT INTO emt_temp (Session_id, message)
values (p_session_id, 'Handling exception at Config_UDA: ' || SQLERRM);
/*INSERT INTO emt_temp (Session_id, message)
values (p_session_id, 'invokation mode:' || l_mode);
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_odi_session_id,
'MODE',
2,
l_mode,
sysdate,
0,
'GET_ITEM_STRUCTURE');
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)
VALUES (p_session_id,p_session_id,'return_payload',2,NULL,'TRUE',NULL,SYSDATE,G_CURRENT_USER_ID);
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)
VALUES (p_session_id,p_session_id,'return_payload',2,NULL,'FALSE',NULL,SYSDATE,G_CURRENT_USER_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_odi_session_id,
l_parameter_name_array(position),
2,
l_config_option,
sysdate,
0,
'GET_ITEM_STRUCTURE');
select fnd_user_name
into l_fnd_user_name
from EGO_PUB_WS_PARAMS
where 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_odi_session_id,
'FND_USER_NAME',
2,
l_fnd_user_name,
sysdate,
0,
'GET_ITEM_STRUCTURE');
select responsibility_name
into l_responsibility_name
from EGO_PUB_WS_PARAMS
where 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_odi_session_id,
'RESPONSIBILITY_NAME',
2,
l_responsibility_name,
sysdate,
0,
'GET_ITEM_STRUCTURE');
select responsibility_appl_name
into l_responsibility_appl_name
from EGO_PUB_WS_PARAMS
where 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_odi_session_id,
'RESPONSIBILITY_APPL_NAME',
2,
l_responsibility_appl_name,
sysdate,
0,
'GET_ITEM_STRUCTURE');
select security_group_name
into l_security_group_name
from EGO_PUB_WS_PARAMS
where 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_odi_session_id,
'SECURITY_GROUP_NAME',
2,
l_security_group_name,
sysdate,
0,
'GET_ITEM_STRUCTURE');
select to_number(extractValue(xmlcontent, '/structureQueryParameters/BatchStructureQueryParameters/BatchId'))
into l_batch_id
from EGO_PUB_WS_PARAMS
where session_id = p_session_id;
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 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_odi_session_id,
'FND_USER_NAME',
2,
l_fnd_user_name,
sysdate,
0,
'GET_ITEM_STRUCTURE');
select responsibility_key
into l_responsibility_name
from FND_RESPONSIBILITY
where responsibility_id = l_responsibility_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_odi_session_id,
'RESPONSIBILITY_NAME',
2,
l_responsibility_name,
sysdate,
0,
'GET_ITEM_STRUCTURE');
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 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_odi_session_id,
'RESPONSIBILITY_APPL_NAME',
2,
l_responsibility_appl_name,
sysdate,
0,
'GET_ITEM_STRUCTURE');
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_odi_session_id,
'SECURITY_GROUP_NAME',
2,
l_security_group_name,
sysdate,
0,
'GET_ITEM_STRUCTURE');
select to_number(extractValue(xmlcontent, '/structureQueryParameters/BatchStructureQueryParameters/BatchId'))
into l_batch_id
from EGO_PUB_WS_PARAMS
where session_id = p_session_id;
select to_char(numeric_value)
into l_config_option
from Ego_Pub_Bat_Params_B
where type_id = l_batch_id
and parameter_name = 'LEVELS_TO_EXPLODE';
UPDATE EGO_PUB_WS_CONFIG
SET Char_value = l_config_option
where Parameter_Name = 'LEVELS_TO_EXPLODE'
and web_service_name = 'GET_ITEM_STRUCTURE'
and session_id = p_session_id
and odi_session_id = p_odi_session_id;
select char_value
into l_config_option
from Ego_Pub_Bat_Params_B
where type_id = l_batch_id
and parameter_name = 'EXPLODE_STD_BOM';
UPDATE EGO_PUB_WS_CONFIG
SET Char_value = l_config_option
where Parameter_Name = 'EXPLODE_STD_BOM'
and web_service_name = 'GET_ITEM_STRUCTURE'
and session_id = p_session_id
and odi_session_id = p_odi_session_id;
select to_char(count(*))
into l_config_option
from Ego_Pub_Bat_Params_B
where type_id = l_batch_id
and parameter_name = 'EXPLODE_OPTION';
UPDATE EGO_PUB_WS_CONFIG
SET Char_value = l_config_option
where Parameter_Name = 'EXPLODE_OPTION'
and web_service_name = 'GET_ITEM_STRUCTURE'
and session_id = p_session_id
and odi_session_id = p_odi_session_id;
select to_char(NUMERIC_VALUE) --Bug 12984722(FP of bug 12984605) : Read from NUMERIC_VALUE instead of CHAR_VALUE
into l_config_option
from Ego_Pub_Bat_Params_B
where type_id = l_batch_id
and parameter_name = 'EXPLODE_OPTION';
UPDATE EGO_PUB_WS_CONFIG
SET Char_value = l_config_option
where Parameter_Name = 'EXPLODE_OPTION'
and web_service_name = 'GET_ITEM_STRUCTURE'
and session_id = p_session_id
and odi_session_id = p_odi_session_id;
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 CHAR_VALUE
from EGO_PUB_WS_CONFIG
where session_id = p_session_id
and web_service_name = 'GET_ITEM_STRUCTURE'
and parameter_name = 'ITEM_INFORMATION';
select char_value
into l_mode
from EGO_PUB_WS_CONFIG
where session_id = p_session_id
and web_service_name = 'GET_ITEM_STRUCTURE'
and parameter_name = 'MODE';
SELECT to_number(char_value)
BULK COLLECT INTO l_item_id_tab
from EGO_PUB_WS_CONFIG
where session_id = p_session_id
and web_service_name = 'GET_ITEM_STRUCTURE'
and parameter_name like 'INVENTORY_ITEM_ID_%'
order by parameter_name;
SELECT to_number(char_value)
BULK COLLECT INTO l_org_id_tab
from EGO_PUB_WS_CONFIG
where session_id = p_session_id
and web_service_name = 'GET_ITEM_STRUCTURE'
and parameter_name like 'ORGANIZATION_ID_%'
order by parameter_name;
SELECT to_number(char_value)
BULK COLLECT INTO l_rev_id_tab
from EGO_PUB_WS_CONFIG
where session_id = p_session_id
and web_service_name = 'GET_ITEM_STRUCTURE'
and parameter_name like 'REVISION_ID_%'
order by parameter_name;
SELECT char_value
BULK COLLECT INTO l_structure_name_tab
from EGO_PUB_WS_CONFIG
where session_id = p_session_id
and web_service_name = 'GET_ITEM_STRUCTURE'
and parameter_name like 'STRUCTURE_NAME_%'
order by parameter_name;
SELECT to_number(char_value)
BULK COLLECT INTO l_root_node_id_tab
from EGO_PUB_WS_CONFIG
where session_id = p_session_id
and web_service_name = 'GET_ITEM_STRUCTURE'
and parameter_name like 'ROOT_NODE_ID_%'
order by parameter_name;
INSERT
INTO BOM_ODI_WS_ENTITIES(
session_id,
odi_session_id,
ITEM_ID,
ITEM_ORG_ID,
ITEM_REV,
structure_name,
EXPLOSION_DATE,
PUBLISH_FLAG,
SEQUENCE_NUMBER,
CREATION_DATE,
CREATED_BY,
ROOT_NODE_ID)
VALUES(
p_session_id,
p_odi_session_id,
l_item_id,
l_org_id,
l_rev_id,
l_structure_name,
l_explosion_date,
'Y',
i,
sysdate,
0,
l_root_node_id_tab(i));
select to_number(extractValue(xmlcontent, '/structureQueryParameters/BatchStructureQueryParameters/BatchId'))
into l_batch_id
from EGO_PUB_WS_PARAMS
where session_id = p_session_id;
select char_value
into l_structure_name
from EGO_PUB_PARAMETERS_V
where type_id = l_batch_id and parameter_name = 'STRUCTURE_NAME';
select date_value
into l_explosion_date
from EGO_PUB_PARAMETERS_V
where type_id = l_batch_id and parameter_name = 'EXPLOSION_DATE';
/*INSERT INTO emt_temp (Session_id, message)
values (p_session_id, 'new in l_str_name:' || l_structure_name);*/
/*INSERT INTO emt_temp (Session_id, message)
values (p_session_id, 'new out l_str_name:' || l_structure_name);
INSERT
INTO BOM_ODI_WS_ENTITIES(
session_id,
odi_session_id,
ITEM_ID,
ITEM_ORG_ID,
ITEM_REV,
structure_name,
EXPLOSION_DATE,
PUBLISH_FLAG,
SEQUENCE_NUMBER,
CREATION_DATE,
CREATED_BY,
ROOT_NODE_ID)
VALUES(
p_session_id,
p_odi_session_id,
l_item_id,
l_org_id,
l_rev_id,
l_structure_name,
l_explosion_date,
'Y',
l_item_index,
sysdate,
0,
-1);
INSERT
INTO BOM_ODI_WS_ENTITIES(
session_id,
odi_session_id,
ITEM_ID,
ITEM_ORG_ID,
ITEM_REV,
structure_name,
EXPLOSION_DATE,
PUBLISH_FLAG,
SEQUENCE_NUMBER,
CREATION_DATE,
CREATED_BY,
ROOT_NODE_ID)
VALUES(
p_session_id,
p_odi_session_id,
l_item_id,
l_org_id,
l_rev_id,
l_structure_name,
l_explosion_date,
'Y',
1,
sysdate,
0,
-1);
SELECT extractValue(lang_code, '/InventoryItemId')
BULK COLLECT INTO l_item_id_tab
FROM (SELECT Value(langcode) lang_code
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, 'structureQueryParameters/ParametersForListOfItems/ListOfItemStructureQueryParams/InventoryItemId') )) langcode
WHERE session_id=p_session_id
);
SELECT extractValue(lang_code, '/Segment1')
BULK COLLECT INTO l_item_segment1_tab
FROM (SELECT Value(langcode) lang_code
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, 'structureQueryParameters/ParametersForListOfItems/ListOfItemStructureQueryParams/InventoryItemName/Segment1') )) langcode
WHERE session_id=p_session_id
);
SELECT extractValue(lang_code, '/Segment2')
BULK COLLECT INTO l_item_segment2_tab
FROM (SELECT Value(langcode) lang_code
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, 'structureQueryParameters/ParametersForListOfItems/ListOfItemStructureQueryParams/InventoryItemName/Segment2') )) langcode
WHERE session_id=p_session_id
);
SELECT extractValue(lang_code, '/Segment3')
BULK COLLECT INTO l_item_segment3_tab
FROM (SELECT Value(langcode) lang_code
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, 'structureQueryParameters/ParametersForListOfItems/ListOfItemStructureQueryParams/InventoryItemName/Segment3') )) langcode
WHERE session_id=p_session_id
);
SELECT extractValue(lang_code, '/Segment4')
BULK COLLECT INTO l_item_segment4_tab
FROM (SELECT Value(langcode) lang_code
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, 'structureQueryParameters/ParametersForListOfItems/ListOfItemStructureQueryParams/InventoryItemName/Segment4') )) langcode
WHERE session_id=p_session_id
);
SELECT extractValue(lang_code, '/Segment5')
BULK COLLECT INTO l_item_segment5_tab
FROM (SELECT Value(langcode) lang_code
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, 'structureQueryParameters/ParametersForListOfItems/ListOfItemStructureQueryParams/InventoryItemName/Segment5') )) langcode
WHERE session_id=p_session_id
);
SELECT extractValue(lang_code, '/Segment6')
BULK COLLECT INTO l_item_segment6_tab
FROM (SELECT Value(langcode) lang_code
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, 'structureQueryParameters/ParametersForListOfItems/ListOfItemStructureQueryParams/InventoryItemName/Segment6') )) langcode
WHERE session_id=p_session_id
);
SELECT extractValue(lang_code, '/Segment7')
BULK COLLECT INTO l_item_segment7_tab
FROM (SELECT Value(langcode) lang_code
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, 'structureQueryParameters/ParametersForListOfItems/ListOfItemStructureQueryParams/InventoryItemName/Segment7') )) langcode
WHERE session_id=p_session_id
);
SELECT extractValue(lang_code, '/Segment8')
BULK COLLECT INTO l_item_segment8_tab
FROM (SELECT Value(langcode) lang_code
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, 'structureQueryParameters/ParametersForListOfItems/ListOfItemStructureQueryParams/InventoryItemName/Segment8') )) langcode
WHERE session_id=p_session_id
);
SELECT extractValue(lang_code, '/Segment9')
BULK COLLECT INTO l_item_segment9_tab
FROM (SELECT Value(langcode) lang_code
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, 'structureQueryParameters/ParametersForListOfItems/ListOfItemStructureQueryParams/InventoryItemName/Segment9') )) langcode
WHERE session_id=p_session_id
);
SELECT extractValue(lang_code, '/Segment10')
BULK COLLECT INTO l_item_segment10_tab
FROM (SELECT Value(langcode) lang_code
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, 'structureQueryParameters/ParametersForListOfItems/ListOfItemStructureQueryParams/InventoryItemName/Segment10') )) langcode
WHERE session_id=p_session_id
);
SELECT extractValue(lang_code, '/Segment11')
BULK COLLECT INTO l_item_segment11_tab
FROM (SELECT Value(langcode) lang_code
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, 'structureQueryParameters/ParametersForListOfItems/ListOfItemStructureQueryParams/InventoryItemName/Segment11') )) langcode
WHERE session_id=p_session_id
);
SELECT extractValue(lang_code, '/Segment12')
BULK COLLECT INTO l_item_segment12_tab
FROM (SELECT Value(langcode) lang_code
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, 'structureQueryParameters/ParametersForListOfItems/ListOfItemStructureQueryParams/InventoryItemName/Segment12') )) langcode
WHERE session_id=p_session_id
);
SELECT extractValue(lang_code, '/Segment13')
BULK COLLECT INTO l_item_segment13_tab
FROM (SELECT Value(langcode) lang_code
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, 'structureQueryParameters/ParametersForListOfItems/ListOfItemStructureQueryParams/InventoryItemName/Segment13') )) langcode
WHERE session_id=p_session_id
);
SELECT extractValue(lang_code, '/Segment14')
BULK COLLECT INTO l_item_segment14_tab
FROM (SELECT Value(langcode) lang_code
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, 'structureQueryParameters/ParametersForListOfItems/ListOfItemStructureQueryParams/InventoryItemName/Segment14') )) langcode
WHERE session_id=p_session_id
);
SELECT extractValue(lang_code, '/Segment15')
BULK COLLECT INTO l_item_segment15_tab
FROM (SELECT Value(langcode) lang_code
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, 'structureQueryParameters/ParametersForListOfItems/ListOfItemStructureQueryParams/InventoryItemName/Segment15') )) langcode
WHERE session_id=p_session_id
);
SELECT extractValue(lang_code, '/Segment16')
BULK COLLECT INTO l_item_segment16_tab
FROM (SELECT Value(langcode) lang_code
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, 'structureQueryParameters/ParametersForListOfItems/ListOfItemStructureQueryParams/InventoryItemName/Segment16') )) langcode
WHERE session_id=p_session_id
);
SELECT extractValue(lang_code, '/Segment17')
BULK COLLECT INTO l_item_segment17_tab
FROM (SELECT Value(langcode) lang_code
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, 'structureQueryParameters/ParametersForListOfItems/ListOfItemStructureQueryParams/InventoryItemName/Segment17') )) langcode
WHERE session_id=p_session_id
);
SELECT extractValue(lang_code, '/Segment18')
BULK COLLECT INTO l_item_segment18_tab
FROM (SELECT Value(langcode) lang_code
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, 'structureQueryParameters/ParametersForListOfItems/ListOfItemStructureQueryParams/InventoryItemName/Segment18') )) langcode
WHERE session_id=p_session_id
);
SELECT extractValue(lang_code, '/Segment19')
BULK COLLECT INTO l_item_segment19_tab
FROM (SELECT Value(langcode) lang_code
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, 'structureQueryParameters/ParametersForListOfItems/ListOfItemStructureQueryParams/InventoryItemName/Segment19') )) langcode
WHERE session_id=p_session_id
);
SELECT extractValue(lang_code, '/Segment20')
BULK COLLECT INTO l_item_segment20_tab
FROM (SELECT Value(langcode) lang_code
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, 'structureQueryParameters/ParametersForListOfItems/ListOfItemStructureQueryParams/InventoryItemName/Segment20') )) langcode
WHERE session_id=p_session_id
);
SELECT extractValue(lang_code, '/OrganizationId')
BULK COLLECT INTO l_org_id_tab
FROM (SELECT Value(langcode) lang_code
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, 'structureQueryParameters/ParametersForListOfItems/ListOfItemStructureQueryParams/OrganizationId') )) langcode
WHERE session_id=p_session_id
);
SELECT extractValue(lang_code, '/OrganizationCode')
BULK COLLECT INTO l_org_name_tab
FROM (SELECT Value(langcode) lang_code
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, 'structureQueryParameters/ParametersForListOfItems/ListOfItemStructureQueryParams/OrganizationCode') )) langcode
WHERE session_id=p_session_id
);
SELECT extractValue(lang_code, '/RevisionId')
BULK COLLECT INTO l_rev_id_tab
FROM (SELECT Value(langcode) lang_code
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, 'structureQueryParameters/ParametersForListOfItems/ListOfItemStructureQueryParams/RevisionId') )) langcode
WHERE session_id=p_session_id
);
SELECT extractValue(lang_code, '/Revision')
BULK COLLECT INTO l_rev_name_tab
FROM (SELECT Value(langcode) lang_code
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, 'structureQueryParameters/ParametersForListOfItems/ListOfItemStructureQueryParams/Revision') )) langcode
WHERE session_id=p_session_id
);
SELECT extractValue(lang_code, '/RevisionDate')
BULK COLLECT INTO l_explosion_date_tab
FROM (SELECT Value(langcode) lang_code
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, 'structureQueryParameters/ParametersForListOfItems/ListOfItemStructureQueryParams/RevisionDate') )) langcode
WHERE session_id=p_session_id
);
SELECT extractValue(lang_code, '/StructureName')
BULK COLLECT INTO l_structure_name_tab
FROM (SELECT Value(langcode) lang_code
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, 'structureQueryParameters/ParametersForListOfItems/ListOfItemStructureQueryParams/StructureName') )) langcode
WHERE session_id=p_session_id
);
INSERT
INTO BOM_ODI_WS_ENTITIES(
session_id,
odi_session_id,
ITEM_ID,
ITEM_ORG_ID,
ITEM_REV,
structure_name,
EXPLOSION_DATE,
PUBLISH_FLAG,
SEQUENCE_NUMBER,
CREATION_DATE,
CREATED_BY,
ROOT_NODE_ID)
VALUES(
p_session_id,
p_odi_session_id,
l_item_id,
l_org_id,
l_rev_id,
l_structure_name,
l_explosion_date,
'Y',
i,
sysdate,
0,
-1);
select msi.concatenated_segments,
msi.bom_item_type,
icc.concatenated_segments
into item_name, l_bom_item_type, l_icc_name
from MTL_SYSTEM_ITEMS_kfv msi,
MTL_ITEM_CATALOG_GROUPS_kfv icc
where
inventory_item_id = item_id
and msi.item_catalog_group_id =icc.item_catalog_group_id(+)
and rownum = 1; -- Bug 12932318 (FP of bug 12908261)
calculate the explosion date based on the selected Item Revision. The explosion date would be
End Effective Date (for Past Revision), Sys Date (for Current Revision) and Start Effective Date
(for Future Revision). If the user provies both Revision and Explosion Date then, system would
check if the selected Item Revision is effective on the given Explosion Date. If it is not
effective on the given explosion date, system would select the Item Revision effective on the
given explosion date. In other words, Explosion Date would take precedence over Item Revision.
Also, as discussed please ensure that the Item Web Service will honour the Fixed Revision
Floating Revision functionality for the components.
*/
/*FUNCTION Compute_Revision_Date(p_rev_date IN DATE,
p_revision_id NUMBER,
p_revision_label VARCHAR2,
p_inventory_item_id IN NUMBER,
p_organization_id IN NUMBER)
RETURN DATE
IS
l_revision_id NUMBER;
select revision_id,
revision,
inventory_item_id,
organization_id,
effectivity_date,
(select nvl( min(b.effectivity_date)-(1/86400),to_date('9999/12/31 00:00:00','yyyy/mm/dd hh24:mi:ss')) end_date from mtl_item_revisions_b b
where b.inventory_item_id = a.inventory_item_id and
b.organization_id = a.organization_id and
b.effectivity_date > a.effectivity_date) end_date,
implementation_date
from mtl_item_revisions_b a
where a.organization_id = p_org_id
and inventory_item_id = p_inventory_item_id
and revision_id = p_rev_id;
select revision_id,
revision,
inventory_item_id,
organization_id,
effectivity_date,
(select nvl( min(b.effectivity_date)-(1/86400),to_date('9999/12/31 00:00:00','yyyy/mm/dd hh24:mi:ss')) end_date from mtl_item_revisions_b b
where b.inventory_item_id = a.inventory_item_id and
b.organization_id = a.organization_id and
b.effectivity_date > a.effectivity_date) end_date,
implementation_date
from mtl_item_revisions_b a
where a.organization_id = p_org_id
and inventory_item_id = p_inventory_item_id
and revision = p_rev_label;
l_dynamic_update_sql VARCHAR2(32767);
l_dynamic_sql := ' select pk1_value, pk2_value, pk3_value ' ||
' from EGO_ODI_WS_ENTITIES i ' ||
' where i.session_id = :1 ' ||
' AND nvl(i.REF1_VALUE, ''Y'') = ''Y'' ' ||
' AND NOT ' || l_sec_predicate;
select parent_sequence_id,
pk1_value,
ref1_value, /*BOM_ITEM_TYPE*/
ref2_value, /*INVENTORY_ITEM_ID*/
ref3_value, /*ORGANIZATION_ID*/
ref4_value, /*REVISION_ID*/
ref5_value, /*STRUCTURE_TYPE*/
ref6_value /*STRUCTURE_NAME*/
from EGO_PUB_WS_FLAT_RECS
where session_id = p_session_id
and odi_session_id = p_odi_session_id
and entity_type = 'ICCSH_COMP';
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,
-1,
'INVENTORY_ITEM_ID_' || l_batch_size,
2,
comp_rec.ref2_value,
sysdate,
0,
'GET_ITEM_STRUCTURE');
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,
-1,
'ORGANIZATION_ID_' || l_batch_size,
2,
comp_rec.ref3_value,
sysdate,
0,
'GET_ITEM_STRUCTURE');
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,
-1,
'REVISION_ID_' || l_batch_size,
2,
comp_rec.ref4_value,
sysdate,
0,
'GET_ITEM_STRUCTURE');
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,
-1,
'STRUCTURE_NAME_' || l_batch_size,
2,
comp_rec.ref6_value,
sysdate,
0,
'GET_ITEM_STRUCTURE');
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,
-1,
'ROOT_NODE_ID_' || l_batch_size,
2,
comp_rec.parent_sequence_id,
sysdate,
0,
'GET_ITEM_STRUCTURE');
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,
-1,
l_parameter_name_array(position),
2,
l_parameter_value_array(position),
sysdate,
0,
'GET_ITEM_STRUCTURE');
select fnd_user_name
into l_fnd_user_name
from EGO_PUB_WS_PARAMS
where 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,
-1,
'FND_USER_NAME',
2,
l_fnd_user_name,
sysdate,
0,
'GET_ITEM_STRUCTURE');
select responsibility_name
into l_responsibility_name
from EGO_PUB_WS_PARAMS
where 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,
-1,
'RESPONSIBILITY_NAME',
2,
l_responsibility_name,
sysdate,
0,
'GET_ITEM_STRUCTURE');
select responsibility_appl_name
into l_responsibility_appl_name
from EGO_PUB_WS_PARAMS
where 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,
-1,
'RESPONSIBILITY_APPL_NAME',
2,
l_responsibility_appl_name,
sysdate,
0,
'GET_ITEM_STRUCTURE');
select security_group_name
into l_security_group_name
from EGO_PUB_WS_PARAMS
where 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,
-1,
'SECURITY_GROUP_NAME',
2,
l_security_group_name,
sysdate,
0,
'GET_ITEM_STRUCTURE');
/*Other parameters: Insert based in the values for ICC web service
LANGUAGE_CODE
LANGUAGE_CODE
HEADER_AG_NAME
(pass value of corresponding ICC node here)
*/
--Inserts language options in Config table
Config_Languages(p_session_id,
-1,
'',
'GET_ITEM_STRUCTURE');
SELECT session_id,
odi_session_id,
ITEM_ID,
ITEM_ORG_ID,
ITEM_REV,
ITEM_REV_CODE,
structure_name,
explosion_date,
sequence_number
FROM BOM_ODI_WS_ENTITIES
WHERE session_id = p_session_id
and odi_session_id = p_odi_session_id
and PUBLISH_FLAG = 'Y';
select rowid row_id,
component_sequence_id,
comp_fixed_revision_id,
parent_comp_seq_id
from bom_explosions_all be
where group_id = p_group_id;
select to_number(char_value)
into l_levels_to_explode
from EGO_PUB_WS_CONFIG
where session_id = p_session_id
and web_service_name = 'GET_ITEM_STRUCTURE'
and parameter_name = 'LEVELS_TO_EXPLODE';
select to_number(char_value)
into l_explode_option
from EGO_PUB_WS_CONFIG
where session_id = p_session_id
and web_service_name = 'GET_ITEM_STRUCTURE'
and parameter_name = 'EXPLODE_OPTION';
select char_value
into l_explode_standard
from EGO_PUB_WS_CONFIG
where session_id = p_session_id
and web_service_name = 'GET_ITEM_STRUCTURE'
and parameter_name = 'EXPLODE_STD_BOM';
/*INSERT INTO emt_temp (Session_id, message)
values (p_session_id, 'Inside bom explosion code');
INSERT INTO emt_temp (Session_id, message)
values (p_session_id, 'provided revision code' || r.ITEM_REV_CODE);
INSERT INTO emt_temp (Session_id, message)
values (p_session_id, 'provided revision id:' || r.ITEM_REV);
INSERT INTO emt_temp (Session_id, message)
values (p_session_id, 'provided revision date:' || r.explosion_date);
INSERT INTO emt_temp (Session_id, message)
values (p_session_id, 'computed revision date:' || x_rev_date);
INSERT INTO emt_temp (Session_id, message)
values (p_session_id, 'computed revision_id :' || x_rev_id);
INSERT INTO emt_temp (Session_id, message)
values (p_session_id, ' l_levels_to_explode: ' || l_levels_to_explode);
INSERT INTO emt_temp (Session_id, message)
values (p_session_id, ' l_explode_option: ' || l_explode_option);
INSERT INTO emt_temp (Session_id, message)
values (p_session_id, ' l_explode_standard: ' || l_explode_standard);
/*INSERT INTO emt_temp (Session_id, message)
values (p_session_id, 'Error code is : ' || x_error_code);
INSERT INTO emt_temp (Session_id, message)
values (p_session_id, 'Error mesg is : ' || x_err_msg);
UPDATE BOM_ODI_WS_ENTITIES
SET group_id = x_group_id,
EXPLOSION_DATE = bom_exploder_pub.get_explosion_date,
EXPLOSION_OPTION = bom_exploder_pub.get_explode_option,
ITEM_REV_CODE = bom_exploder_pub.get_expl_end_item_rev_code,
ITEM_UNIT_NUMBER = bom_exploder_pub.get_expl_unit_number
WHERE session_id = r.session_id
AND odi_session_id = r.odi_session_id
AND ITEM_ID = r.ITEM_ID
AND ITEM_ORG_ID = r.ITEM_ORG_ID
AND ITEM_REV = r.ITEM_REV;
/*INSERT INTO emt_temp (Session_id, message)
values (p_session_id, 'comp_rec.component_sequence_id: ' || comp_rec.component_sequence_id);
INSERT INTO emt_temp (Session_id, message)
values (p_session_id, 'bom_exploder_pub.get_explosion_date: ' || bom_exploder_pub.get_explosion_date);
INSERT INTO emt_temp (Session_id, message)
values (p_session_id, 'bom_exploder_pub.get_expl_end_item_rev_code: ' || bom_exploder_pub.get_expl_end_item_rev_code);
INSERT INTO emt_temp (Session_id, message)
values (p_session_id, 'comp_rec.comp_fixed_revision_id: ' || comp_rec.comp_fixed_revision_id);
INSERT INTO emt_temp (Session_id, message)
values (p_session_id, 'comp_rec.parent_comp_seq_id: ' || comp_rec.parent_comp_seq_id);
INSERT
INTO bom_odi_ws_revisions(
session_id,
--group_id,
--component_sequence_id,
row_id,
revision,
revision_id,
revision_label,
revision_high_date,
parent_revision,
PUBLISH_FLAG,
CREATION_DATE,
CREATED_BY)
VALUES(
l_session_id,
--x_group_id,
--comp_rec.component_sequence_id,
comp_rec.row_id,
l_comp_rev,
l_comp_rev_id,
l_comp_rev_label,
l_comp_rev_high_date,
l_comp_parent_rev,
'Y',
sysdate,
0);
/*INSERT INTO emt_temp (Session_id, message)
values (p_session_id, 'Entering: Create_Params_Structure');
/*INSERT INTO emt_temp (Session_id, message)
values (p_session_id, 'Entering: Create_Entities_Structure' );
/*INSERT INTO emt_temp (Session_id, message)
values (p_session_id, 'Entering: Explode_BOM_Structure ' );
select x.xslcontent.getclobval()
into xslclob
from EGO_ODI_WS_XSL x
where web_service_name = p_web_service_name;
SELECT SEQUENCE_ID ,
PARENT_SEQUENCE_ID,
LEVEL LEVEL_NUMBER,
ENTITY_TYPE,
Value
FROM EGO_PUB_WS_FLAT_RECS
WHERE session_id = cp_session_id
START WITH session_id = cp_session_id AND Nvl(PARENT_SEQUENCE_ID,-1)=-1
CONNECT BY PRIOR SEQUENCE_ID = PARENT_SEQUENCE_ID AND session_id = cp_session_id;
SELECT Upper(Nvl(CHAR_VALUE,'TRUE')) param_value
FROM ego_pub_ws_config
WHERE session_id = cp_session_id
AND odi_session_id = cp_odi_session_id
AND upper(parameter_name) = 'RETURN_PAYLOAD';
SELECT sequence_id, parent_sequence_id, entity_type,
LEVEL level_number
FROM ego_pub_ws_flat_recs
WHERE session_id = cp_session_id
START WITH session_id = cp_session_id
AND NVL (parent_sequence_id, -1) = -1
AND sequence_id IN (SELECT sequence_id
FROM ego_pub_ws_flat_recs
WHERE session_id = cp_session_id AND NVL (parent_sequence_id, -1) = -1)
CONNECT BY PRIOR sequence_id = parent_sequence_id AND session_id = cp_session_id
UNION ALL
SELECT -99999, NULL, 'ITEM',1
FROM DUAL;
SELECT Value
FROM ego_pub_ws_flat_recs
WHERE session_id = cp_session_id
AND sequence_id = cp_sequence_id
AND NVL (parent_sequence_id, -1) = NVL(cp_parent_sequence_id, -1);
SELECT SEQUENCE_ID ,
PARENT_SEQUENCE_ID,
LEVEL LEVEL_NUMBER,
ENTITY_TYPE,
Value
FROM EGO_PUB_WS_FLAT_RECS
WHERE session_id = cp_session_id
START WITH session_id = cp_session_id AND Nvl(PARENT_SEQUENCE_ID,-1)=-1 AND SEQUENCE_ID=cp_sequence_id
CONNECT BY PRIOR SEQUENCE_ID = PARENT_SEQUENCE_ID AND session_id = cp_session_id;
SELECT session_id,input_id, err_code,err_message
FROM EGO_PUB_WS_ERRORS
WHERE session_id=cp_session_id
AND odi_session_id= cp_odi_session_id;
SELECT session_id, param_name,param_value
FROM EGO_PUB_WS_INPUT_IDENTIFIERS
WHERE session_id=cp_session_id
AND odi_session_id= cp_odi_session_id
AND input_id=cp_input_id;
SELECT SEQUENCE_ID,
decode(organization_id,master_organization_id,0,1) as master_or_child
FROM ego_pub_ws_flat_recs, mtl_parameters
WHERE session_id = cp_session_id
AND entity_type = 'ITEM'
AND NVL (parent_sequence_id, -1) = -1
AND pk2_value = organization_id
order by master_or_child;
SELECT SEQUENCE_ID
FROM ego_pub_ws_flat_recs
WHERE session_id = cp_session_id
AND entity_type <> 'ITEM'
AND NVL (parent_sequence_id, -1) = -1;
select x.xslcontent.getclobval()
into xslclob
from EGO_ODI_WS_XSL x
where web_service_name = p_web_service_name;
l_level_stack.DELETE(l_level_stack.COUNT);
l_tags_stack.DELETE(l_tags_stack.COUNT);
l_level_stack.DELETE(l_level_stack.COUNT);
l_tags_stack.DELETE(l_tags_stack.COUNT);
INSERT INTO ego_pub_ws_output (session_id,
odi_session_id,
web_service_name,
sequence_id,
xmlcontent,
xml_odi,
creation_date,
created_by)
VALUES (l_session_id,
p_odi_session_id,
p_web_service_name,
0,
l_output_xml,
XmlType(l_xml),
sysdate,
0);
INSERT INTO ego_pub_ws_output (session_id, odi_session_id, web_service_name, sequence_id, xmlcontent, xml_odi, creation_date, created_by)
VALUES (l_session_id, p_odi_session_id, p_web_service_name, l_sequence, l_output_xml, xmltype (l_xml), SYSDATE, 0);
l_level_stack.delete (l_level_stack.COUNT);
l_tags_stack.delete (l_tags_stack.COUNT);
l_level_stack.delete (l_level_stack.COUNT);
l_tags_stack.delete (l_tags_stack.COUNT);
l_level_stack.delete (l_level_stack.COUNT);
l_tags_stack.delete (l_tags_stack.COUNT);
--selected in cursor cs_ws_top_entity as its size is huge and will cause performance issues
FOR k IN c_ws_entity_val (l_session_id,j.sequence_id,j.parent_sequence_id)
LOOP
DBMS_LOB.append (l_xml, k.VALUE);
INSERT INTO EGO_PUB_WS_OUTPUT (session_id,
odi_session_id,
web_service_name,
sequence_id,
xmlcontent,
xml_odi,
creation_date,
created_by)
VALUES (l_session_id,
l_odi_session_id,
l_web_service_name,
0,
xmltype(l_chunk_detail),
XmlType(l_chunk_detail),
sysdate,
0);
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;
/* Procedure to insert record for configurable parameter*/
PROCEDURE Create_Config_Param ( p_session_id IN NUMBER,
p_odi_session_id IN NUMBER,
p_webservice_name IN VARCHAR2,
p_lang_search_str IN VARCHAR2,
p_parent_hier IN VARCHAR2,
p_child_hier IN VARCHAR2)
IS
l_lang_code_tab dbms_sql.varchar2_table;
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
);
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,'LANGUAGE_CODE',2,NULL,l_lang_code_tab(i),NULL,SYSDATE,G_CURRENT_USER_ID, p_webservice_name);
FOR i IN (SELECT language_code FROM FND_LANGUAGES WHERE INSTALLED_FLAG IN ('I','B') ) LOOP
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,'LANGUAGE_CODE',2,NULL,i.language_code,NULL,SYSDATE,G_CURRENT_USER_ID, p_webservice_name);
SELECT Upper(Nvl(extractValue(ret_pay, '/ReturnPayload'),'TRUE'))
INTO l_retpayload
FROM (SELECT Value(retpay) ret_pay
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, l_retpay_xpath) )) retpay
WHERE session_id=p_session_id
);
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)
VALUES (p_session_id,p_odi_session_id,'return_payload',2,NULL,Upper(l_retpayload),NULL,SYSDATE,G_CURRENT_USER_ID);
/*extract configurable parameter Attr Group and insert record into config table */
SELECT Nvl(extractValue(uda_ag, '/UserDefAttrGrps'),'TRUE')
INTO l_UserDefAttrGrps
FROM (SELECT Value(udaag) uda_ag
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/ICCQueryParam/ICCPubEntityObject/UserDefAttrGrps') )) udaag
WHERE session_id=p_session_id
);
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)
VALUES (p_session_id,p_odi_session_id,'PublishUDA',2,NULL,Upper(l_UserDefAttrGrps),NULL,SYSDATE,G_CURRENT_USER_ID);
/*extract configurable parameter ICCVersions and insert record into config table */
SELECT Nvl(extractValue(uda_ag, '/ICCVersions'),'TRUE')
INTO l_iccvers_config
FROM (SELECT Value(udaag) uda_ag
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/ICCQueryParam/ICCPubEntityObject/ICCVersions') )) udaag
WHERE session_id=p_session_id
);
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)
VALUES (p_session_id,p_odi_session_id,'PublishICCVersions',2,NULL,Upper(l_iccvers_config),NULL,SYSDATE,G_CURRENT_USER_ID);
/*extract configurable parameter TransAttrs and insert record into config table */
SELECT Nvl(extractValue(uda_ag, '/TransAttrs'),'TRUE')
INTO l_transattrs_config
FROM (SELECT Value(udaag) uda_ag
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/ICCQueryParam/ICCPubEntityObject/TransAttrs') )) udaag
WHERE session_id=p_session_id
);
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)
VALUES (p_session_id,p_odi_session_id,'PublishTransAttrs',2,NULL,Upper(l_transattrs_config),NULL,SYSDATE,G_CURRENT_USER_ID);
/*extract configurable parameter ICCStructure and insert record into config table */
SELECT Nvl(extractValue(uda_ag, '/ICCStructure'),'TRUE')
INTO l_structure_config
FROM (SELECT Value(udaag) uda_ag
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/ICCQueryParam/ICCPubEntityObject/ICCStructure') )) udaag
WHERE session_id=p_session_id
);
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)
VALUES (p_session_id,p_odi_session_id,'PublishICCStructure',2,NULL,Upper(l_structure_config),NULL,SYSDATE,G_CURRENT_USER_ID);
/*Insert record into config table for parameter parent and child hierarchy*/
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)
VALUES (p_session_id,p_odi_session_id,'PublishParentICCs',2,NULL,Upper(p_parent_hier),NULL,SYSDATE,G_CURRENT_USER_ID);
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)
VALUES (p_session_id,p_odi_session_id,'PublishChildICCs',2,NULL,Upper(p_child_hier),NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT cp_icc_id ITEM_CATALOG_GROUP_ID,cp_icc_ver VERSION_SEQ_ID,1 lev FROM dual
UNION
SELECT ITEM_CATALOG_GROUP_ID,VERSION_SEQ_ID,lev FROM
( SELECT iccb.ITEM_CATALOG_GROUP_ID,VERSION_SEQ_ID ,lev
FROM EGO_MTL_CATALOG_GRP_VERS_B iccb,
( SELECT ITEM_CATALOG_GROUP_ID,LEVEL lev
FROM MTL_ITEM_CATALOG_GROUPS_B
START WITH ITEM_CATALOG_GROUP_ID =cp_icc_id
CONNECT BY PRIOR ITEM_CATALOG_GROUP_ID=PARENT_CATALOG_GROUP_ID
) hier
WHERE iccb.item_catalog_group_id=hier.item_catalog_group_id
)
WHERE
(
cp_publish_child ='TRUE'
AND
(
LEV > 1
AND (item_catalog_group_id, VERSION_SEQ_ID)
IN
( SELECT item_catalog_group_id,VERSION_SEQ_ID
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE (item_catalog_group_id,start_active_date )
IN
( SELECT item_catalog_group_id, MAX(start_active_date) start_active_date
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE version_seq_id > 0
AND start_active_date <= (
SELECT nvl(end_active_date,SYSDATE)
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE ITEM_CATALOG_GROUP_ID =cp_icc_id
AND VERSION_SEQ_ID =cp_icc_ver
)
GROUP BY item_catalog_group_id
HAVING MAX(start_active_date)<=(
SELECT nvl(end_active_date,SYSDATE)
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE ITEM_CATALOG_GROUP_ID =cp_icc_id
AND VERSION_SEQ_ID = cp_icc_ver
)
)
)
)
)
UNION
SELECT ITEM_CATALOG_GROUP_ID,VERSION_SEQ_ID,lev FROM
( SELECT iccb.ITEM_CATALOG_GROUP_ID,VERSION_SEQ_ID ,lev
FROM EGO_MTL_CATALOG_GRP_VERS_B iccb,
( SELECT ITEM_CATALOG_GROUP_ID,LEVEL lev
FROM MTL_ITEM_CATALOG_GROUPS_B
START WITH ITEM_CATALOG_GROUP_ID =cp_icc_id
CONNECT BY PRIOR PARENT_CATALOG_GROUP_ID=ITEM_CATALOG_GROUP_ID
) hier
WHERE iccb.item_catalog_group_id=hier.item_catalog_group_id
)
WHERE
(
cp_publish_parent ='TRUE'
AND
(
LEV > 1
AND (item_catalog_group_id, VERSION_SEQ_ID)
IN
( SELECT item_catalog_group_id,VERSION_SEQ_ID
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE (item_catalog_group_id,start_active_date )
IN
( SELECT item_catalog_group_id, MAX(start_active_date) start_active_date
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE creation_date <= ( SELECT CREATION_DATE
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE ITEM_CATALOG_GROUP_ID = cp_icc_id
AND VERSION_SEQ_ID = cp_icc_ver
)
AND version_seq_id > 0
AND start_active_date <= (
SELECT CREATION_DATE
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE ITEM_CATALOG_GROUP_ID =cp_icc_id
AND VERSION_SEQ_ID = cp_icc_ver
)
GROUP BY item_catalog_group_id
HAVING MAX(start_active_date)<=(
SELECT CREATION_DATE
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE ITEM_CATALOG_GROUP_ID =cp_icc_id
AND VERSION_SEQ_ID = cp_icc_ver
)
)
)
)
)
-- Non versioned ICC
UNION
SELECT ITEM_CATALOG_GROUP_ID,NULL AS version_seq_id, lev FROM
( SELECT ITEM_CATALOG_GROUP_ID,LEVEL lev
FROM MTL_ITEM_CATALOG_GROUPS_B
START WITH ITEM_CATALOG_GROUP_ID =cp_icc_id
CONNECT BY PRIOR ITEM_CATALOG_GROUP_ID=PARENT_CATALOG_GROUP_ID
)
WHERE
(
cp_publish_child ='TRUE'
AND cp_icc_ver IS NULL
AND LEV > 1
)
-- Non versioned ICC
UNION
SELECT ITEM_CATALOG_GROUP_ID,NULL AS version_seq_id, lev FROM
( SELECT ITEM_CATALOG_GROUP_ID,LEVEL lev
FROM MTL_ITEM_CATALOG_GROUPS_B
START WITH ITEM_CATALOG_GROUP_ID =cp_icc_id
CONNECT BY PRIOR PARENT_CATALOG_GROUP_ID=ITEM_CATALOG_GROUP_ID
)
WHERE
(
cp_publish_parent ='TRUE'
AND cp_icc_ver IS NULL
AND LEV > 1
);
SELECT pk1_value , nvl(pk2_value,-1)
BULK COLLECT INTO l_dup_icc_id_tab, l_dup_icc_ver_tab
FROM Ego_Pub_Bat_Ent_Objs_v --Find out if any other PK's
WHERE batch_id = l_batch_id AND user_entered = 'Y';
SELECT Upper(CHAR_VALUE) INTO l_parent_hier FROM EGO_PUB_BAT_PARAMS_b WHERE type_id=l_batch_id AND Upper(parameter_name) ='PUBLISHPARENT';
SELECT Upper(CHAR_VALUE) INTO l_child_hier FROM EGO_PUB_BAT_PARAMS_b WHERE type_id=l_batch_id AND Upper(parameter_name) ='PUBLISHCHILD';
SELECT extractValue(ICC_Id, '/ICCId')
BULK COLLECT INTO l_dup_icc_id_tab
FROM (SELECT Value(iccid) ICC_Id
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/ICCQueryParam/ICCIdentifiersList/ICCIdentifier/ICCId') )) iccid
WHERE session_id=p_session_id
);
SELECT Nvl(extractValue(ICC_Ver, '/VersionSequence'),-1)
BULK COLLECT INTO l_dup_icc_ver_tab
FROM (SELECT Value(iccver) ICC_Ver
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/ICCQueryParam/ICCIdentifiersList/ICCIdentifier/VersionSequence') )) iccver
WHERE session_id=p_session_id
);
SELECT upper(Nvl(extractValue(ICC_Id, '/ParentICCs'),'FALSE'))
INTO l_parent_hier
FROM (SELECT Value(iccid) ICC_Id
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/ICCQueryParam/ICCPubEntityObject/ParentICCs') )) iccid
WHERE session_id=p_session_id
);
SELECT upper(Nvl(extractValue(ICC_Id, '/ChildICCs'),'FALSE'))
INTO l_child_hier
FROM (SELECT Value(iccid) ICC_Id
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/ICCQueryParam/ICCPubEntityObject/ChildICCs') )) iccid
WHERE session_id=p_session_id
);
SELECT version_seq_id INTO l_dup_icc_ver_tab(i)
FROM
( SELECT item_catalog_group_id,version_seq_id,MAX(start_active_date) start_active_date
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE NVL(end_active_date, sysdate) >= SYSDATE
AND start_active_date <= SYSDATE
AND ITEM_CATALOG_GROUP_ID = l_dup_icc_id_tab(i)
AND version_seq_id > 0
GROUP BY item_catalog_group_id,version_seq_id
HAVING MAX(start_active_date)<=SYSDATE
);
INSERT INTO ego_odi_ws_entities ( session_id,odi_session_id,entity_type,pk1_value,pk2_value,pk3_value,pk4_value,pk5_value,ref1_value)
VALUES (p_session_id,p_odi_session_id,'ItemCatalogCategory',j.item_catalog_group_id,j.version_seq_id,NULL,NULL,NULL,l_ref1_value);
IF( j.item_catalog_group_id <> l_dup_icc_id_tab(i)) THEN --Bug Fix 8708269.Dont insert record for main Entity again
--Bug 8757388
l_batch_entity_count :=l_batch_entity_count+1;
/* Calling Add_Derived_Entitites API once per batch to have status update.*/
--Bug 8757388
EGO_PUB_FWK_PK.add_derived_entities(batch_entity_rec,x_return_status,x_msg_count,x_msg_data);
SELECT version_seq_id INTO l_dup_icc_ver_tab(i)
FROM
( SELECT item_catalog_group_id,version_seq_id,MAX(start_active_date) start_active_date
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE NVL(end_active_date, sysdate) >= SYSDATE
AND start_active_date <= SYSDATE
AND ITEM_CATALOG_GROUP_ID = l_dup_icc_id_tab(i)
AND version_seq_id > 0
GROUP BY item_catalog_group_id,version_seq_id
HAVING MAX(start_active_date)<=SYSDATE
);
INSERT INTO ego_odi_ws_entities ( session_id,odi_session_id,entity_type,pk1_value,pk2_value,pk3_value,pk4_value,pk5_value,ref1_value)
VALUES (p_session_id,p_odi_session_id,'ItemCatalogCategory',j.item_catalog_group_id,j.version_seq_id,NULL,NULL,NULL,l_ref1_value);
SELECT value_set_id, version_seq_id
FROM
(
( SELECT value_set_id,NULL AS version_seq_id
FROM ego_value_sets_v
WHERE cp_version_seq_id IS NULL
START WITH value_set_id = cp_value_set_id
CONNECT BY PRIOR value_set_id = parent_value_set_id
)
UNION ALL
/*( SELECT flex_value_set_id value_set_id ,version_seq_id
FROM EGO_FLEX_VALUESET_VERSION_B
WHERE flex_value_set_id= cp_value_set_id
AND cp_version_seq_id IS NOT NULL
AND NVL(end_active_date, sysdate) >= SYSDATE
AND start_active_date <= SYSDATE
AND version_seq_id>0
) */
(SELECT cp_value_set_id AS value_set_id, cp_version_seq_id AS version_seq_id
FROM dual
WHERE cp_version_seq_id IS NOT NULL
)
);
SELECT pk1_value , Nvl(pk2_value,-1) --Bug 8722729
BULK COLLECT INTO l_dup_vs_id_tab, l_dup_vs_ver_tab
FROM Ego_Pub_Bat_Ent_Objs_v --Find out if any other PK's
WHERE batch_id = l_batch_id AND user_entered = 'Y';
SELECT extractValue(ValueSet_Id, '/ValueSetId')
BULK COLLECT INTO l_dup_vs_id_tab
FROM (SELECT Value(VSId) ValueSet_Id
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/ValuesetQueryParam/ValuesetIdentifiersList/ValuesetIdentifier/ValueSetId') )) VSId
WHERE session_id=p_session_id
);
SELECT Nvl(extractValue(ValueSet_Ver, '/VersionSeqId'),-1)
BULK COLLECT INTO l_dup_vs_ver_tab
FROM (SELECT Value(VSVer) ValueSet_Ver
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/ValuesetQueryParam/ValuesetIdentifiersList/ValuesetIdentifier/VersionSeqId') )) VSVer
WHERE session_id=p_session_id
);
SELECT version_seq_id INTO l_dup_vs_ver_tab(i)
FROM EGO_FLEX_VALUESET_VERSION_B
WHERE NVL(end_active_date, sysdate) >= SYSDATE
AND start_active_date <= SYSDATE
AND FLEX_VALUE_SET_ID = l_dup_vs_id_tab(i)
AND version_seq_id > 0;
INSERT INTO ego_odi_ws_entities ( session_id,odi_session_id,entity_type,pk1_value,pk2_value,pk3_value,pk4_value,pk5_value,ref1_value)
VALUES (p_session_id,p_odi_session_id,'ValueSet',j.value_set_id,j.version_seq_id,NULL,NULL,NULL,l_ref1_value);
IF( j.value_set_id <> l_dup_vs_ver_tab(i)) THEN --Bug Fix 8708269.Dont insert record for main Entity again
--Bug 8757388
l_batch_entity_count :=l_batch_entity_count+1;
INSERT INTO ego_odi_ws_entities ( session_id,odi_session_id,entity_type,pk1_value,pk2_value,pk3_value,pk4_value,pk5_value,ref1_value)
VALUES (p_session_id,p_odi_session_id,'ValueSet',l_dup_vs_id_tab(i),l_dup_vs_ver_tab(i),NULL,NULL,NULL,l_ref1_value);
SELECT version_seq_id INTO l_dup_vs_ver_tab(i)
FROM EGO_FLEX_VALUESET_VERSION_B
WHERE NVL(end_active_date, sysdate) >= SYSDATE
AND start_active_date <= SYSDATE
AND FLEX_VALUE_SET_ID = l_dup_vs_id_tab(i)
AND version_seq_id > 0;
INSERT INTO ego_odi_ws_entities ( session_id,odi_session_id,entity_type,pk1_value,pk2_value,pk3_value,pk4_value,pk5_value,ref1_value)
VALUES (p_session_id,p_odi_session_id,'ValueSet',j.value_set_id,j.version_seq_id,NULL,NULL,NULL,l_ref1_value);
INSERT INTO ego_odi_ws_entities ( session_id,odi_session_id,entity_type,pk1_value,pk2_value,pk3_value,pk4_value,pk5_value,ref1_value)
VALUES (p_session_id,p_odi_session_id,'ValueSet',l_dup_vs_id_tab(i),l_dup_vs_ver_tab(i),NULL,NULL,NULL,l_ref1_value);
SELECT fnd_user_name,responsibility_appl_name,responsibility_name
FROM ego_pub_ws_params
WHERE session_id=cp_session_id;
SELECT Count(furgd.responsibility_id) val
FROM fnd_user_resp_groups_direct furgd, fnd_responsibility fr
WHERE furgd.responsibility_id=fr.responsibility_id
AND fr.menu_id IN
(SELECT menu_id
FROM fnd_menu_entries
START WITH function_id IN
(SELECT function_id
FROM fnd_form_functions
WHERE function_name='EGO_ITEM_ADMINISTRATION'
)
CONNECT BY PRIOR menu_id=sub_menu_id
)
AND furgd.user_id= cp_user_id
AND furgd.responsibility_id= cp_resp_id
AND furgd.start_date <=SYSDATE
AND Nvl(furgd.end_date,sysdate+1) > SYSDATE;
SELECT created_by, responsibility_id
INTO l_user_id,l_resp_id
FROM EGO_PUB_BAT_HDR_B
WHERE batch_id = l_batch_id;
SELECT application_id
INTO l_application_id
FROM FND_RESPONSIBILITY
WHERE responsibility_id = l_resp_id;
SELECT USER_NAME INTO l_user_name
FROM FND_USER
WHERE USER_ID =l_user_id;
SELECT responsibility_name INTO l_resp_name
FROM fnd_responsibility_vl
WHERE application_id = l_application_id
AND responsibility_id =l_resp_id ;
SELECT USER_ID INTO l_user_id
FROM FND_USER
WHERE USER_NAME =l_user_name;
SELECT application_id INTO l_application_id
FROM fnd_application
WHERE application_short_name =l_appl_name;
SELECT responsibility_id,responsibility_name INTO l_resp_id,l_resp_name
FROM fnd_responsibility_vl
WHERE application_id = l_application_id
AND responsibility_key=l_resp_key;
/*Procedure to insert records into Input Identifiers table*/
PROCEDURE Populate_Input_Identifier(p_session_id IN NUMBER,
p_odi_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_odi_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_odi_session_id,
p_input_id,
p_err_code,
p_err_message,
SYSDATE,
-1);
SELECT Count(batch_id) batch_exist
FROM ego_pub_bat_hdr_b
WHERE BATCH_ID= p_batch_id;
SELECT Count(base.item_catalog_group_id) ver_icc_count
FROM mtl_item_catalog_groups_b base ,EGO_MTL_CATALOG_GRP_VERS_B vers
WHERE base.item_catalog_group_id= vers.item_catalog_group_id
AND base.item_catalog_group_id=cp_item_catalog_group_id
AND vers.version_seq_id=Nvl(cp_version_seq_id,vers.version_seq_id)
AND vers.version_seq_id>0; --Yjain
SELECT Count(item_catalog_group_id) icc_count
FROM mtl_item_catalog_groups_b
WHERE item_catalog_group_id=cp_item_catalog_group_id;
SELECT Count(base.flex_value_set_id) ver_vs_count
FROM fnd_flex_value_sets base ,EGO_FLEX_VALUESET_VERSION_B vers
WHERE base.flex_value_set_id= vers.flex_value_set_id
AND base.flex_value_set_id=cp_flex_value_set_id
AND vers.version_seq_id =Nvl(cp_version_seq_id,vers.version_seq_id)
AND vers.version_seq_id >0; --Yjain
SELECT Count(flex_value_set_id) vs_count
FROM fnd_flex_value_sets
WHERE flex_value_set_id=cp_flex_value_set_id;
SELECT (Nvl(Max(input_id),0)+1) AS input_id
FROM EGO_PUB_WS_INPUT_IDENTIFIERS
WHERE session_id=cp_session_id
AND odi_session_id= cp_odi_session_id;
/*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_odi_session_id,NULL,'EGO_NO_INPUT','No input has been provided to webservices');*/
/*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_odi_session_id,l_input_id,'EGO_INVALID_BATCH','Input batch is not a valid batch. Please publish valid batch id.',SYSDATE,G_CURRENT_USER_ID);*/
/*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_odi_session_id,NULL,'EGO_INVALID_BATCH','Input batch is not a valid batch. Please publish valid batch id.',SYSDATE,G_CURRENT_USER_ID);*/
/*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_odi_session_id,NULL,'EGO_INVALID_BATCH','Input batch is not a valid batch. Please publish valid batch id.',SYSDATE,G_CURRENT_USER_ID);*/
/*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_odi_session_id,NULL,'EGO_INVALID_BATCH','Input batch is not a valid batch. Please publish valid batch id.',SYSDATE,G_CURRENT_USER_ID);*/
SELECT * FROM
(
SELECT *
FROM
(
SELECT versions.item_catalog_group_id,
versions.icc_version_NUMBER ,
attrs.attr_id ,
attrs.attr_name ,
hier.lev lev
FROM ego_obj_AG_assocs_b assocs ,
ego_attrs_v attrs ,
ego_attr_groups_v ag ,
EGO_TRANS_ATTR_VERS_B versions,
mtl_item_catalog_groups_kfv icv ,
(
SELECT item_catalog_group_id,
LEVEL lev
FROM mtl_item_catalog_groups_b
START WITH item_catalog_group_id = cp_item_catalog_category_id
CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
)
hier
WHERE ag.attr_group_type = 'EGO_ITEM_TRANS_ATTR_GROUP'
AND assocs.attr_group_id = ag.attr_group_id
AND assocs.classification_code = TO_CHAR(hier.item_catalog_group_id)
AND attrs.attr_group_name = ag.attr_group_name
AND TO_CHAR(icv.item_catalog_group_id) = assocs.classification_code
AND TO_CHAR(versions.association_id) = assocs.association_id
AND TO_CHAR(versions.item_catalog_group_id) = assocs.classification_code
AND attrs.attr_id = versions.attr_id
)
)
WHERE
(
( LEV = 1 AND ICC_VERSION_number =cp_icc_version_number )
OR
( LEV > 1 AND ( item_catalog_group_id, ICC_VERSION_NUMBER )
IN ( SELECT item_catalog_group_id, VERSION_SEQ_ID
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE (item_catalog_group_id,start_active_date )
IN
(SELECT item_catalog_group_id, MAX(start_active_date) start_active_date
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE creation_date <= cp_creation_date
AND version_seq_id > 0
AND start_active_date <= cp_start_active_date
GROUP BY item_catalog_group_id
HAVING MAX(start_active_date)<=cp_start_active_date
)
AND version_seq_id > 0
)
)
);
SELECT sequence_id,parent_sequence_id,pk1_value icc_id ,pk2_value icc_ver
FROM EGO_PUB_WS_FLAT_RECS
WHERE session_id= p_session_id
AND odi_session_id=p_odi_session_id
AND entity_type ='ICCVersion';
SELECT Nvl(START_ACTIVE_DATE,SYSDATE) ,CREATION_DATE INTO l_icc_start_active_date, l_icc_create_date
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE ITEM_CATALOG_GROUP_ID = j.icc_id AND VERSION_SEQ_ID = j.icc_ver;
INSERT
INTO EGO_PUB_WS_FLAT_RECS
(
SESSION_ID ,
ODI_SESSION_ID ,
ENTITY_TYPE ,
SEQUENCE_ID ,
PARENT_SEQUENCE_ID,
PAYLOAD_SEQUENCE ,
PK1_VALUE ,
REF1_VALUE ,
REF2_VALUE ,
REF3_VALUE ,
REF4_VALUE ,
VALUE ,
CREATION_DATE
)
(SELECT p_session_id ,
p_odi_session_id ,
'TransactionAttribute' ,
EGO_PUB_WS_FLAT_RECS_S.NEXTVAL ,
j.sequence_id ,
1 ,
l_icc_ta_metadata_tbl(i).attrid ,
j.icc_id ,
j.icc_ver ,
l_icc_ta_metadata_tbl(i).valuesetid,
l_vs_version_number ,
xmlforest(k.attr_name AS AttrName,
l_icc_ta_metadata_tbl(i).attrid AS AttributeId,
l_icc_ta_metadata_tbl(i).AttrDisplayName AS AttrDisplayName,
l_icc_ta_metadata_tbl(i).SEQUENCE AS AttrSequence,
l_icc_ta_metadata_tbl(i).datatype AS DataTypeCode, -- trudave fix for FP bug 15940029, (FP for bug 14851479)
l_icc_ta_metadata_tbl(i).uomclass AS UOMCLASS,
l_icc_ta_metadata_tbl(i).defaultvalue AS DefaultValue,
l_icc_ta_metadata_tbl(i).rejectedvalue AS RejectedValue,
l_icc_ta_metadata_tbl(i).requiredflag AS RequiredFlag,
l_icc_ta_metadata_tbl(i).readonlyflag AS ReadOnlyFlag,
l_icc_ta_metadata_tbl(i).hiddenflag AS HiddenFlag,
l_icc_ta_metadata_tbl(i).searchableflag AS SearchableFlag,
l_icc_ta_metadata_tbl(i).checkeligibility AS CheckEligibility,
l_is_inherited AS INHERITED,
l_is_modified AS MODIFIED ).getclobval(),
SYSDATE
FROM dual
);
SELECT ego_pub_ws_flat_recs.sequence_id,
ego_validation_table_info_v.flex_value_set_id,
ego_validation_table_info_v.additional_where_clause
FROM ego_pub_ws_flat_recs,
ego_validation_table_info_v
WHERE ego_pub_ws_flat_recs.pk1_value = ego_validation_table_info_v.flex_value_set_id
AND ego_pub_ws_flat_recs.session_id = p_Session_Id
AND ego_pub_ws_flat_recs.odi_session_id = p_ODISession_Id
AND ego_pub_ws_flat_recs.entity_type = 'TableInfo';
/* Insert Table Information into Flat except Where Clause because
WhereClause Column is of Type 'LONG' and it is working with XML DB Functions*/
/* Entity Type of the record will be 'TableInfo' which will be child of 'ValueSet' element*/
INSERT
INTO ego_pub_ws_flat_recs
(
session_id ,
odi_session_id ,
entity_type ,
sequence_id ,
parent_sequence_id,
pk1_value ,
value ,
creation_date
)
SELECT p_Session_Id,
p_ODISession_Id,
'TableInfo',
ego_pub_ws_flat_recs_s.nextval,
ego_pub_ws_flat_recs.sequence_id,
ego_validation_table_info_v.flex_value_set_id,
XMLCONCAT( XMLELEMENT("AppName",ego_validation_table_info_v.table_application_name),
XMLELEMENT("AppId",ego_validation_table_info_v.table_application_id),
XMLELEMENT("TableName",ego_validation_table_info_v.application_table_name),
XMLELEMENT("ValueColName",ego_validation_table_info_v.value_column_name),
XMLELEMENT("ValueColType",ego_validation_table_info_v.value_column_type),
XMLELEMENT("ValueColSize",ego_validation_table_info_v.value_column_size),
XMLELEMENT("IDColName",ego_validation_table_info_v.id_column_name),
XMLELEMENT("IDColType",ego_validation_table_info_v.id_column_type),
XMLELEMENT("IDColSize",ego_validation_table_info_v.id_column_size),
XMLELEMENT("MeaningColName",ego_validation_table_info_v.meaning_column_name),
XMLELEMENT("MeaningColType",ego_validation_table_info_v.meaning_column_type),
XMLELEMENT("MeaningColSize",ego_validation_table_info_v.meaning_column_size)
).getClobVal(),
SYSDATE
FROM ego_pub_ws_flat_recs,
ego_validation_table_info_v
WHERE ego_pub_ws_flat_recs.pk1_value=ego_validation_table_info_v.flex_value_set_id
AND (
ego_pub_ws_flat_recs.session_id = p_Session_Id
AND ego_pub_ws_flat_recs.odi_session_id = p_ODISession_Id
AND ego_pub_ws_flat_recs.entity_type = 'ValueSet'
AND ego_pub_ws_flat_recs.pk2_value IS NULL
AND ego_pub_ws_flat_recs.ref1_value = 'F'
);
/*Insert WhereClause in to Flat table.This record will be Child of 'TableInfo' element*/
/*INSERT
INTO ego_pub_ws_flat_recs
(
session_id ,
odi_session_id ,
entity_type ,
sequence_id ,
parent_sequence_id,
pk1_value ,
value ,
creation_date
)
SELECT p_Session_Id,
p_ODISession_Id,
'WhereClause',
ego_pub_ws_flat_recs_s.nextval,
ego_pub_ws_flat_recs.sequence_id,
ego_validation_table_info_v.flex_value_set_id,
To_Lob(ego_validation_table_info_v.additional_where_clause),
SYSDATE
FROM ego_pub_ws_flat_recs,
ego_validation_table_info_v
WHERE ego_pub_ws_flat_recs.pk1_value=ego_validation_table_info_v.flex_value_set_id
AND (
ego_pub_ws_flat_recs.session_id = p_Session_Id
AND ego_pub_ws_flat_recs.odi_session_id = p_ODISession_Id
AND ego_pub_ws_flat_recs.entity_type = 'TableInfo'
);*/
INSERT
INTO ego_pub_ws_flat_recs
(
session_id ,
odi_session_id ,
entity_type ,
sequence_id ,
parent_sequence_id,
pk1_value ,
value ,
creation_date
)
VALUES
(
p_Session_Id ,
p_ODISession_Id ,
'WhereClause' ,
ego_pub_ws_flat_recs_s.nextval, -- Sequence Id for record
rec.sequence_id , -- Parent record Sequence Id
rec.flex_value_set_id , -- VlaueSet Id
TempLong , -- enclosed WhereClause value
SYSDATE
);
SELECT ego_pub_ws_flat_recs.sequence_id,
ego_validation_table_info_v.flex_value_set_id,
ego_validation_table_info_v.additional_where_clause
FROM ego_pub_ws_flat_recs,
ego_validation_table_info_v
WHERE ego_pub_ws_flat_recs.pk1_value = ego_validation_table_info_v.flex_value_set_id
AND ego_pub_ws_flat_recs.session_id = p_Session_Id
AND ego_pub_ws_flat_recs.odi_session_id = p_ODISession_Id
AND ego_pub_ws_flat_recs.entity_type = 'TableInfo';
/* Insert Table Information into Flat except Where Clause because
WhereClause Column is of Type 'LONG' and it is working with XML DB Functions*/
/* Entity Type of the record will be 'TableInfo' which will be child of 'ValueSet' element*/
INSERT
INTO ego_pub_ws_flat_recs
(
session_id ,
odi_session_id ,
entity_type ,
sequence_id ,
parent_sequence_id,
pk1_value ,
value ,
creation_date
)
SELECT p_Session_Id,
p_ODISession_Id,
'TableInfo',
ego_pub_ws_flat_recs_s.nextval,
ego_pub_ws_flat_recs.sequence_id,
ego_validation_table_info_v.flex_value_set_id,
XMLCONCAT( XMLELEMENT("AppName",ego_validation_table_info_v.table_application_name),
XMLELEMENT("AppId",ego_validation_table_info_v.table_application_id),
XMLELEMENT("TableName",ego_validation_table_info_v.application_table_name),
XMLELEMENT("ValueColName",ego_validation_table_info_v.value_column_name),
XMLELEMENT("ValueColType",ego_validation_table_info_v.value_column_type),
XMLELEMENT("ValueColSize",ego_validation_table_info_v.value_column_size),
XMLELEMENT("IDColName",ego_validation_table_info_v.id_column_name),
XMLELEMENT("IDColType",ego_validation_table_info_v.id_column_type),
XMLELEMENT("IDColSize",ego_validation_table_info_v.id_column_size),
XMLELEMENT("MeaningColName",ego_validation_table_info_v.meaning_column_name),
XMLELEMENT("MeaningColType",ego_validation_table_info_v.meaning_column_type),
XMLELEMENT("MeaningColSize",ego_validation_table_info_v.meaning_column_size)
).getClobVal(),
SYSDATE
FROM ego_pub_ws_flat_recs,
ego_validation_table_info_v
WHERE ego_pub_ws_flat_recs.pk1_value=ego_validation_table_info_v.flex_value_set_id
AND (
ego_pub_ws_flat_recs.session_id = p_Session_Id
AND ego_pub_ws_flat_recs.odi_session_id = p_ODISession_Id
AND ego_pub_ws_flat_recs.entity_type = 'Valueset'
AND ego_pub_ws_flat_recs.ref1_value = 'UDA'
AND ego_pub_ws_flat_recs.ref2_value = 'F'
);
/*Insert WhereClause in to Flat table.This record will be Child of 'TableInfo' element*/
/*INSERT
INTO ego_pub_ws_flat_recs
(
session_id ,
odi_session_id ,
entity_type ,
sequence_id ,
parent_sequence_id,
pk1_value ,
value ,
creation_date
)
SELECT p_Session_Id,
p_ODISession_Id,
'WhereClause',
ego_pub_ws_flat_recs_s.nextval,
ego_pub_ws_flat_recs.sequence_id,
ego_validation_table_info_v.flex_value_set_id,
To_Lob(ego_validation_table_info_v.additional_where_clause),
SYSDATE
FROM ego_pub_ws_flat_recs,
ego_validation_table_info_v
WHERE ego_pub_ws_flat_recs.pk1_value=ego_validation_table_info_v.flex_value_set_id
AND (
ego_pub_ws_flat_recs.session_id = p_Session_Id
AND ego_pub_ws_flat_recs.odi_session_id = p_ODISession_Id
AND ego_pub_ws_flat_recs.entity_type = 'TableInfo'
);*/
INSERT
INTO ego_pub_ws_flat_recs
(
session_id ,
odi_session_id ,
entity_type ,
sequence_id ,
parent_sequence_id,
pk1_value ,
value ,
creation_date
)
VALUES
(
p_Session_Id ,
p_ODISession_Id ,
'WhereClause' ,
ego_pub_ws_flat_recs_s.nextval, -- Sequence Id for record
rec.sequence_id , -- Parent record Sequence Id
rec.flex_value_set_id , -- VlaueSet Id
TempLong , -- enclosed WhereClause value
SYSDATE
);