[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Procedure Inserts the UDA values for the publishing items,
* for a given business entity level in to the table EGO_PUB_WS_FLAT_RECS.
*/
/* Below are the different values stored in EGO_PUB_WS_FLAT_RECS for AG, UDA and transalateble UDAs.
Column in AG UDA UDA-For Translatable value
Flat Rec table
============== ============= ============== =============================
REF1_VALUE : attr_grp_id attr_grp_id attr_grp_id
REF2_VALUE : exension id exension id exension id
REF3_VALUE : data_level_id data_level_id data_level_id
REF4_VALUE : org id org id org id
REF5_VALUE : ATTRIBUTE_ID ATTRIBUTE_ID
REF6_VALUE : "TRANSLATED_CHAR_VALUE"
*/
PROCEDURE POPULATE_AGS(sessionId IN NUMBER,
odisessionId IN NUMBER,
dataLevelId IN NUMBER
)
AS
v_count NUMBER;
select char_value INTO v_publish_udas
from EGO_PUB_WS_CONFIG
where session_id = sessionId
and PARAMETER_NAME = 'PUBLISH_UDA_GROUPS';
SELECT Count(*) INTO v_count
FROM EGO_PUB_WS_CONFIG
WHERE SESSION_ID = sessionId
AND PARAMETER_NAME = 'PUBLISH_AG_NAME';
insert into EGO_PUB_WS_FLAT_RECS
(
SEQUENCE_ID,
SESSION_ID,
ODI_SESSION_ID,
ENTITY_TYPE,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
REF1_VALUE,
REF3_VALUE,
REF4_VALUE,
REF6_VALUE,
PARENT_SEQUENCE_ID,
VALUE,
CREATION_DATE
)
select
EGO_PUB_WS_FLAT_RECS_S.nextval,
sessionId,
odiSessionId,
'ATTRIBUTE_GROUP',
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
PK3_VALUE,
ATTRIBUTEGROUP_ID,
43101,
ORGANIZATION_ID,
'VARIANT',
SEQUENCE_ID,
XMLForest(
ATTRIBUTEGROUP_ID AS "ATTRIBUTEGROUP_ID",
ATTRIBUTE_GROUP_NAME AS "ATTRIBUTE_GROUP_NAME",
NULL AS "EXTENSION_ID",
DATA_LEVEL_NAME AS "DATA_LEVEL_NAME"
).getclobval() ,
SYSDATE
FROM
(
SELECT DISTINCT
agv.ATTR_GROUP_ID AS "ATTRIBUTEGROUP_ID",
'Item' AS "DATA_LEVEL_NAME",
attr.ATTR_GROUP_NAME AS "ATTRIBUTE_GROUP_NAME",
ent.PK1_VALUE AS "INVENTORY_ITEM_ID",
ent.PK2_VALUE AS "ORGANIZATION_ID",
ent.PK3_VALUE, FLAT.SEQUENCE_ID
FROM
ego_attrs_v attr, EGO_STYLE_VARIANT_ATTR_VS var,
ego_attr_groups_v agv, mtl_system_items_b msib,
EGO_ODI_WS_ENTITIES ent, EGO_PUB_WS_FLAT_RECS flat
WHERE
var.ATTRIBUTE_ID = attr.ATTR_ID
AND agv.ATTR_GROUP_NAME = attr.ATTR_GROUP_NAME
AND attr.APPLICATION_ID = agv.APPLICATION_ID
AND agv.variant = 'Y'
AND var.INVENTORY_ITEM_ID = ent.PK1_VALUE
AND ent.SESSION_ID = sessionId
AND ent.SESSION_ID = FLAT.SESSION_ID
AND ent.PK1_VALUE = FLAT.PK1_VALUE
AND ent.PK2_VALUE = FLAT.PK2_VALUE
AND ent.PK3_VALUE = FLAT.PK3_VALUE
AND FLAT.ENTITY_TYPE = 'ITEM'
AND msib.INVENTORY_ITEM_ID = To_Number(ent.PK1_VALUE)
AND msib.ORGANIZATION_ID = To_Number(ent.PK2_VALUE)
AND msib.style_item_flag = 'Y'
);
/* If any varinat AG details are inserted in above query, then execute the below query to insert their
attributes and value set names.
*/
IF (SQL%ROWCOUNT > 0 ) THEN
insert INTO EGO_PUB_WS_FLAT_RECS
(
SEQUENCE_ID,
SESSION_ID,
ODI_SESSION_ID,
ENTITY_TYPE,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
REF1_VALUE,
REF3_VALUE ,
REF4_VALUE ,
REF5_VALUE ,
PARENT_SEQUENCE_ID,
VALUE,
CREATION_DATE
)
SELECT
EGO_PUB_WS_FLAT_RECS_S.nextval,
sessionId,
odisessionId,
'UDA',
INVENTORY_ITEM_ID ,
ORGANIZATION_ID ,
PK3_VALUE ,
ATTRIBUTEGROUP_ID ,
43101 ,
ORGANIZATION_ID,
ATTRIBUTE_ID ,
SEQUENCE_ID,
XMLForest(
ATTR_NAME AS "ATTRIBUTE_NAME",
ATTRIBUTE_CHAR_VALUE AS "ATTRIBUTE_CHAR_VALUE",
NULL AS "ATTRIBUTE_NUMBER_VALUE",
NULL AS "ATTRIBUTE_UOM_VALUE",
NULL AS "ATTRIBUTE_DATE_VALUE",
NULL AS "ATTRIBUTE_DATETIME_VALUE" ,
NULL AS "DISPLAY_VALUE"
).getclobval() ,
SYSDATE
FROM
(
SELECT
agv.ATTR_GROUP_ID AS "ATTRIBUTEGROUP_ID",
attr.ATTR_GROUP_NAME AS "ATTRIBUTE_GROUP_NAME",
attr.VALUE_SET_NAME AS "ATTRIBUTE_CHAR_VALUE",
ent.PK1_VALUE AS "INVENTORY_ITEM_ID",
ent.PK2_VALUE AS "ORGANIZATION_ID",
var.ATTRIBUTE_ID , attr.ATTR_NAME ,
var.VALUE_SET_ID , ent.PK3_VALUE ,
FLAT.SEQUENCE_ID
FROM
ego_attrs_v attr, EGO_STYLE_VARIANT_ATTR_VS var, ego_attr_groups_v agv,
EGO_ODI_WS_ENTITIES ent, EGO_PUB_WS_FLAT_RECS flat
WHERE
var.ATTRIBUTE_ID = attr.ATTR_ID
AND agv.ATTR_GROUP_NAME = attr.ATTR_GROUP_NAME
AND attr.APPLICATION_ID = agv.APPLICATION_ID
AND agv.variant = 'Y'
AND var.INVENTORY_ITEM_ID = To_Number(ent.PK1_VALUE)
AND ent.SESSION_ID = sessionId
AND ent.SESSION_ID = FLAT.SESSION_ID
AND ent.PK1_VALUE = FLAT.PK1_VALUE
AND ent.PK2_VALUE = FLAT.PK2_VALUE
AND ent.PK3_VALUE = FLAT.PK3_VALUE
AND FLAT.ENTITY_TYPE = 'ATTRIBUTE_GROUP'
AND FLAT.REF6_VALUE = 'VARIANT'
AND FLAT.REF3_VALUE = 43101
AND agv.ATTR_GROUP_ID = To_Number(FLAT.REF1_VALUE)
);
v_query_string_b := 'SELECT egob.ATTR_GROUP_ID AS "ATTRIBUTEGROUP_ID" , ' ||
' AG.ATTR_GROUP_NAME AS "ATTRIBUTE_GROUP_NAME", '||
' egob.ORGANIZATION_ID , '||
' egob.INVENTORY_ITEM_ID , '||
' egob.REVISION_ID , '||
' AG.APPLICATION_ID , '||
' egob.EXTENSION_ID , '||
' egob.DATA_LEVEL_ID , '||
' egob.PK1_VALUE , '||
' egob.PK2_VALUE , '||
' edlv.USER_DATA_LEVEL_NAME AS "DATA_LEVEL_NAME" , '||
' flat.SEQUENCE_ID , '||
' ent.PK3_VALUE AS "PK3_VALUE" , '||
' flat.PK2_VALUE AS "ORG_ID" '||
' FROM EGO_MTL_SY_ITEMS_EXT_B egob, EGO_ATTR_GROUPS_V AG, '||
' EGO_DATA_LEVEL_VL edlv , EGO_ODI_WS_ENTITIES ent, EGO_PUB_WS_FLAT_RECS flat '||
' WHERE egob.DATA_LEVEL_ID = edlv.DATA_LEVEL_ID '||
' AND AG.APPLICATION_ID = edlv.APPLICATION_ID '||
' AND egob.ATTR_GROUP_ID = AG.ATTR_GROUP_ID '||
' AND ent.SESSION_ID = :session_id '||
' AND ent.SESSION_ID=FLAT.SESSION_ID AND ent.PK1_VALUE=FLAT.PK1_VALUE '||
' AND ent.PK2_VALUE=FLAT.PK2_VALUE AND ent.PK3_VALUE=FLAT.PK3_VALUE ';
v_query_string_Inherit_b := 'SELECT egob.ATTR_GROUP_ID AS "ATTRIBUTEGROUP_ID" , ' ||
' AG.ATTR_GROUP_NAME AS "ATTRIBUTE_GROUP_NAME", '||
' egob.ORGANIZATION_ID , '||
' egob.INVENTORY_ITEM_ID , '||
' egob.REVISION_ID , '||
' AG.APPLICATION_ID , '||
' egob.EXTENSION_ID , '||
' egob.DATA_LEVEL_ID , '||
' egob.PK1_VALUE , '||
' egob.PK2_VALUE , '||
' edlv.USER_DATA_LEVEL_NAME AS "DATA_LEVEL_NAME" , '||
' flat.SEQUENCE_ID , '||
' ent.PK3_VALUE AS "PK3_VALUE" , '||
' flat.PK2_VALUE AS "ORG_ID" '||
' FROM EGO_MTL_SY_ITEMS_EXT_B egob, EGO_ATTR_GROUPS_V AG, ego_attr_group_dl AGDL, mtl_system_items_b msib, '||
' EGO_DATA_LEVEL_VL edlv , EGO_ODI_WS_ENTITIES ent, EGO_PUB_WS_FLAT_RECS flat '||
' WHERE egob.DATA_LEVEL_ID = edlv.DATA_LEVEL_ID '||
' AND egob.DATA_LEVEL_ID = AGDL.DATA_LEVEL_ID '||
' AND AG.APPLICATION_ID = edlv.APPLICATION_ID '||
' AND egob.ATTR_GROUP_ID = AG.ATTR_GROUP_ID '||
' AND ent.SESSION_ID = :session_id '||
' AND ent.SESSION_ID=FLAT.SESSION_ID AND ent.PK1_VALUE=FLAT.PK1_VALUE '||
' AND ent.PK2_VALUE=FLAT.PK2_VALUE AND ent.PK3_VALUE=FLAT.PK3_VALUE '||
' AND msib.INVENTORY_ITEM_ID = To_Number(ent.PK1_VALUE) '||
' AND msib.ORGANIZATION_ID = To_Number(ent.PK2_VALUE) '||
' AND msib.style_item_flag = ''N'' '||
' AND AG.ATTR_GROUP_ID = AGDL.ATTR_GROUP_ID '||
' AND AGDL.DEFAULTING = ''I'' ';
' AG.ATTR_GROUP_NAME IN (SELECT char_value FROM EGO_PUB_WS_CONFIG '||
' WHERE session_id = '||sessionId||
' and PARAMETER_NAME = ''PUBLISH_AG_NAME'' ) ';
' AG.ATTR_GROUP_NAME IN (SELECT char_value FROM EGO_PUB_WS_CONFIG '||
' WHERE session_id = '||sessionId||
' and PARAMETER_NAME = ''PUBLISH_AG_NAME'' ) ';
v_query_string := 'insert into EGO_PUB_WS_FLAT_RECS
(
SEQUENCE_ID,
SESSION_ID,
ODI_SESSION_ID,
ENTITY_TYPE,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
REF1_VALUE,
REF2_VALUE,
REF3_VALUE,
REF4_VALUE,
PARENT_SEQUENCE_ID,
VALUE,
CREATION_DATE
)
select
EGO_PUB_WS_FLAT_RECS_S.nextval,'
||sessionId||','
||odiSessionId||',
''ATTRIBUTE_GROUP'',
INVENTORY_ITEM_ID,
ORG_ID,
PK3_VALUE,
ATTRIBUTEGROUP_ID,
EXTENSION_ID ,
DATA_LEVEL_ID,
ORGANIZATION_ID,
SEQUENCE_ID,
XMLForest(
ATTRIBUTEGROUP_ID AS "ATTRIBUTEGROUP_ID",
ATTRIBUTE_GROUP_NAME AS "ATTRIBUTE_GROUP_NAME",
EXTENSION_ID AS "EXTENSION_ID",
DATA_LEVEL_NAME AS "DATA_LEVEL_NAME"
).getclobval() ,
SYSDATE '||'
FROM
( '||v_query_string||')';
insert into EGO_PUB_WS_FLAT_RECS
(
SEQUENCE_ID,
SESSION_ID,
ODI_SESSION_ID,
ENTITY_TYPE,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
REF1_VALUE,
REF2_VALUE,
REF3_VALUE,
REF4_VALUE,
PARENT_SEQUENCE_ID,
VALUE,
CREATION_DATE
)
select
EGO_PUB_WS_FLAT_RECS_S.nextval,
sessionId,
odiSessionId,
'ATTRIBUTE_GROUP',
v_inventory_item_id,
v_org_id,
v_pk3_value,
v_attribute_group_id,
v_extension_id ,
v_data_level_id,
v_organization_id,
v_sequence_id,
XMLForest(
v_attribute_group_id AS "ATTRIBUTEGROUP_ID",
v_attribute_group_name AS "ATTRIBUTE_GROUP_NAME",
v_extension_id AS "EXTENSION_ID",
v_data_level_name AS "DATA_LEVEL_NAME"
).getclobval() ,
SYSDATE
FROM dual;
/* Insert The UDA values other than translated char value, for all the AGs that were inserted into the flat table for the corresponding level */
/*
insert into EGO_PUB_WS_FLAT_RECS
(
SEQUENCE_ID,
SESSION_ID,
ODI_SESSION_ID,
ENTITY_TYPE,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
REF1_VALUE,
REF2_VALUE,
REF3_VALUE ,
REF4_VALUE ,
REF5_VALUE ,
PARENT_SEQUENCE_ID,
VALUE,
CREATION_DATE
)
select
EGO_PUB_WS_FLAT_RECS_S.nextval,
sessionId,
odisessionId,
'UDA',
flat.PK1_VALUE ,
flat.PK2_VALUE ,
flat.PK3_VALUE ,
list.ATTRIBUTEGROUP_ID ,
list.EXTENSION_ID ,
dataLevelId ,
flat.REF4_VALUE ,
list.ATTRIBUTE_ID ,
flat.SEQUENCE_ID,
XMLForest(
list.ATTRIBUTE_NAME AS "ATTRIBUTE_NAME",
list.ATTRIBUTE_CHAR_VALUE AS "ATTRIBUTE_CHAR_VALUE",
list.ATTRIBUTE_NUMBER_VALUE AS "ATTRIBUTE_NUMBER_VALUE",
list.ATTRIBUTE_UOM_VALUE AS "ATTRIBUTE_UOM_VALUE",
list.ATTRIBUTE_DATE_VALUE AS "ATTRIBUTE_DATE_VALUE",
list.ATTRIBUTE_DATETIME_VALUE AS "ATTRIBUTE_DATETIME_VALUE" ,
evsv.DISPLAY_NAME AS "DISPLAY_VALUE"
).getclobval() ,
SYSDATE
FROM (
(SELECT
ATTRIBUTEGROUP_ID,
EXTENSION_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
APPLICATION_ID,
DATA_LEVEL_ID,
ATTRIBUTE_ID,
ATTRIBUTE_NAME,
ATTRIBUTE_CHAR_VALUE,
ATTRIBUTE_NUMBER_VALUE,
ATTRIBUTE_UOM_VALUE,
ATTRIBUTE_DATE_VALUE,
ATTRIBUTE_DATETIME_VALUE,
null as TRANSLATED_CHAR_VALUE,
REVISION_ID
FROM ego_all_attr_base_v)
) list , EGO_PUB_WS_FLAT_RECS flat ,
ego_attrs_v eav, EGO_VALUE_SET_VALUES_V evsv
WHERE
list.ATTRIBUTEGROUP_ID = FLAT.REF1_VALUE
AND list.EXTENSION_ID = FLAT.REF2_VALUE
AND list.INVENTORY_ITEM_ID = FLAT.PK1_VALUE
AND list.ORGANIZATION_ID = Decode(dataLevelId , 43101, flat.REF4_VALUE,
43103 , flat.REF4_VALUE,
43104 , flat.REF4_VALUE,
FLAT.PK2_VALUE )
AND nvl(list.REVISION_ID, -1) = Decode(nvl(list.REVISION_ID, -1), -1,-1,flat.PK3_VALUE)
AND list.DATA_LEVEL_ID = dataLevelId
AND FLAT.session_id = sessionId
AND FLAT.ENTITY_TYPE = 'ATTRIBUTE_GROUP'
AND eav.VALUE_SET_ID = evsv.VALUE_SET_ID (+)
AND eav.attr_id = list.ATTRIBUTE_ID
AND Nvl(eav.enabled_flag, 'Y') = 'Y' -- Bug 9542020
AND
(
( list.ATTRIBUTE_CHAR_VALUE IS NOT NULL
AND Nvl(evsv.FORMAT_TYPE, 'C') = 'C' -- Bug 9539538
AND Nvl(evsv.INTERNAL_NAME,list.ATTRIBUTE_CHAR_VALUE ) = list.ATTRIBUTE_CHAR_VALUE
)
OR
( list.ATTRIBUTE_NUMBER_VALUE IS NOT NULL
AND Nvl(evsv.FORMAT_TYPE, 'N') = 'N' -- Bug 9539538
AND Nvl(evsv.INTERNAL_NAME,list.ATTRIBUTE_NUMBER_VALUE) = list.ATTRIBUTE_NUMBER_VALUE
)
OR
( list.ATTRIBUTE_DATE_VALUE IS NOT NULL
AND Nvl(evsv.FORMAT_TYPE, 'X') = 'X' -- Bug 9539538
-- Bug 9615220 Should change parameters to DATEor DATETIME, then compare
AND Nvl(To_Date(evsv.INTERNAL_NAME, 'YYYY-MM-DD HH24:MI:SS'),To_Date(list.ATTRIBUTE_DATE_VALUE, 'MM/DD/YYYY')) = To_Date(list.ATTRIBUTE_DATE_VALUE, 'MM/DD/YYYY')
)
OR
( list.ATTRIBUTE_DATETIME_VALUE IS NOT NULL
AND Nvl(evsv.FORMAT_TYPE, 'Y') = 'Y' -- Bug 9539538
-- Bug 9615220 Should change parameters to DATEor DATETIME, then compare
AND Nvl(To_Date(evsv.INTERNAL_NAME, 'YYYY-MM-DD HH24:MI:SS'),To_Date(list.ATTRIBUTE_DATETIME_VALUE, 'MM/DD/YYYY HH24:MI:SS')) = To_Date(list.ATTRIBUTE_DATETIME_VALUE, 'MM/DD/YYYY HH24:MI:SS')
)
);
/* Insert The UDA values other than translated char value, for all the AGs that were inserted into the flat table for the corresponding level */
insert into EGO_PUB_WS_FLAT_RECS
(
SEQUENCE_ID,
SESSION_ID,
ODI_SESSION_ID,
ENTITY_TYPE,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
REF1_VALUE,
REF2_VALUE,
REF3_VALUE ,
REF4_VALUE ,
REF5_VALUE ,
PARENT_SEQUENCE_ID,
VALUE,
CREATION_DATE
)
SELECT
EGO_PUB_WS_FLAT_RECS_S.nextval,
sessionId,
odisessionId,
'UDA',
list.PK1_VALUE ,
list.PK2_VALUE ,
list.PK3_VALUE ,
list.ATTRIBUTEGROUP_ID ,
list.EXTENSION_ID ,
dataLevelId ,
list.REF4_VALUE ,
list.ATTRIBUTE_ID ,
list.SEQUENCE_ID,
XMLForest(
list.ATTR_NAME AS "ATTRIBUTE_NAME",
list.ATTRIBUTE_CHAR_VALUE AS "ATTRIBUTE_CHAR_VALUE",
list.ATTRIBUTE_NUMBER_VALUE AS "ATTRIBUTE_NUMBER_VALUE",
list.ATTRIBUTE_UOM_VALUE AS "ATTRIBUTE_UOM_VALUE",
list.ATTRIBUTE_DATE_VALUE AS "ATTRIBUTE_DATE_VALUE",
list.ATTRIBUTE_DATETIME_VALUE AS "ATTRIBUTE_DATETIME_VALUE" ,
evsv.DISPLAY_NAME AS "DISPLAY_VALUE"
).getclobval() ,
SYSDATE
FROM
( select
flat.PK1_VALUE ,
flat.PK2_VALUE ,
flat.PK3_VALUE ,
UDA.ATTR_GROUP_ID AS "ATTRIBUTEGROUP_ID" ,
UDA.EXTENSION_ID ,
flat.REF4_VALUE ,
AGC.attr_id AS "ATTRIBUTE_ID",
flat.SEQUENCE_ID,
AGC.VALUE_SET_ID,
AGC.ATTR_NAME,
DECODE (AGC.DATA_TYPE_CODE, 'C', DECODE (AGC.DATABASE_COLUMN, 'C_EXT_ATTR1', UDA.C_EXT_ATTR1, 'C_EXT_ATTR2', UDA.C_EXT_ATTR2, 'C_EXT_ATTR3', UDA.C_EXT_ATTR3, 'C_EXT_ATTR4', UDA.C_EXT_ATTR4, 'C_EXT_ATTR5', UDA.C_EXT_ATTR5,
'C_EXT_ATTR6', UDA.C_EXT_ATTR6, 'C_EXT_ATTR7', UDA.C_EXT_ATTR7, 'C_EXT_ATTR8', UDA.C_EXT_ATTR8, 'C_EXT_ATTR9', UDA.C_EXT_ATTR9, 'C_EXT_ATTR10', UDA.C_EXT_ATTR10,
'C_EXT_ATTR11', UDA.C_EXT_ATTR11, 'C_EXT_ATTR12', UDA.C_EXT_ATTR12, 'C_EXT_ATTR13', UDA.C_EXT_ATTR13, 'C_EXT_ATTR14', UDA.C_EXT_ATTR14, 'C_EXT_ATTR15', UDA.C_EXT_ATTR15,
'C_EXT_ATTR16', UDA.C_EXT_ATTR16, 'C_EXT_ATTR17', UDA.C_EXT_ATTR17, 'C_EXT_ATTR18', UDA.C_EXT_ATTR18, 'C_EXT_ATTR19', UDA.C_EXT_ATTR19, 'C_EXT_ATTR20', UDA.C_EXT_ATTR20,
'C_EXT_ATTR21', UDA.C_EXT_ATTR21, 'C_EXT_ATTR22', UDA.C_EXT_ATTR22, 'C_EXT_ATTR23', UDA.C_EXT_ATTR23, 'C_EXT_ATTR24', UDA.C_EXT_ATTR24, 'C_EXT_ATTR25', UDA.C_EXT_ATTR25,
'C_EXT_ATTR26', UDA.C_EXT_ATTR26, 'C_EXT_ATTR27', UDA.C_EXT_ATTR27, 'C_EXT_ATTR28', UDA.C_EXT_ATTR28, 'C_EXT_ATTR29', UDA.C_EXT_ATTR29, 'C_EXT_ATTR30', UDA.C_EXT_ATTR30,
'C_EXT_ATTR31', UDA.C_EXT_ATTR31, 'C_EXT_ATTR32', UDA.C_EXT_ATTR32, 'C_EXT_ATTR33', UDA.C_EXT_ATTR33, 'C_EXT_ATTR34', UDA.C_EXT_ATTR34, 'C_EXT_ATTR35', UDA.C_EXT_ATTR35,
'C_EXT_ATTR36', UDA.C_EXT_ATTR36, 'C_EXT_ATTR37', UDA.C_EXT_ATTR37, 'C_EXT_ATTR38', UDA.C_EXT_ATTR38, 'C_EXT_ATTR39', UDA.C_EXT_ATTR39, 'C_EXT_ATTR40', UDA.C_EXT_ATTR40 )
) AS ATTRIBUTE_CHAR_VALUE,
(DECODE (AGC.DATA_TYPE_CODE, 'N', DECODE (AGC.DATABASE_COLUMN, 'N_EXT_ATTR1', UDA.N_EXT_ATTR1, 'N_EXT_ATTR2', UDA.N_EXT_ATTR2, 'N_EXT_ATTR3', UDA.N_EXT_ATTR3, 'N_EXT_ATTR4', UDA.N_EXT_ATTR4, 'N_EXT_ATTR5', UDA.N_EXT_ATTR5,
'N_EXT_ATTR6', UDA.N_EXT_ATTR6, 'N_EXT_ATTR7', UDA.N_EXT_ATTR7, 'N_EXT_ATTR8', UDA.N_EXT_ATTR8, 'N_EXT_ATTR9', UDA.N_EXT_ATTR9, 'N_EXT_ATTR10', UDA.N_EXT_ATTR10,
'N_EXT_ATTR11', UDA.N_EXT_ATTR11, 'N_EXT_ATTR12', UDA.N_EXT_ATTR12, 'N_EXT_ATTR13', UDA.N_EXT_ATTR13, 'N_EXT_ATTR14', UDA.N_EXT_ATTR14, 'N_EXT_ATTR15', UDA.N_EXT_ATTR15,
'N_EXT_ATTR16', UDA.N_EXT_ATTR16, 'N_EXT_ATTR17', UDA.N_EXT_ATTR17, 'N_EXT_ATTR18', UDA.N_EXT_ATTR18, 'N_EXT_ATTR19', UDA.N_EXT_ATTR19, 'N_EXT_ATTR20', UDA.N_EXT_ATTR20 )
)/NVL(
(SELECT CONVERSION_RATE
FROM MTL_UOM_CONVERSIONS UOMLIST
WHERE UOMLIST.UOM_CODE =
DECODE (AGC.DATA_TYPE_CODE, 'N',
DECODE (AGC.DATABASE_COLUMN,'N_EXT_ATTR1', UDA.UOM_EXT_ATTR1, 'N_EXT_ATTR2', UDA.UOM_EXT_ATTR2, 'N_EXT_ATTR3', UDA.UOM_EXT_ATTR3, 'N_EXT_ATTR4', UDA.UOM_EXT_ATTR4, 'N_EXT_ATTR5', UDA.UOM_EXT_ATTR5,
'N_EXT_ATTR6', UDA.UOM_EXT_ATTR6, 'N_EXT_ATTR7', UDA.UOM_EXT_ATTR7, 'N_EXT_ATTR8', UDA.UOM_EXT_ATTR8, 'N_EXT_ATTR9', UDA.UOM_EXT_ATTR9, 'N_EXT_ATTR10', UDA.UOM_EXT_ATTR10,
'N_EXT_ATTR11', UDA.UOM_EXT_ATTR11, 'N_EXT_ATTR12', UDA.UOM_EXT_ATTR12, 'N_EXT_ATTR13', UDA.UOM_EXT_ATTR13, 'N_EXT_ATTR14', UDA.UOM_EXT_ATTR14, 'N_EXT_ATTR15', UDA.UOM_EXT_ATTR15,
'N_EXT_ATTR16', UDA.UOM_EXT_ATTR16, 'N_EXT_ATTR17', UDA.UOM_EXT_ATTR17, 'N_EXT_ATTR18', UDA.UOM_EXT_ATTR18, 'N_EXT_ATTR19', UDA.UOM_EXT_ATTR19, 'N_EXT_ATTR20', UDA.UOM_EXT_ATTR20)
)
AND INVENTORY_ITEM_ID = 0
), 1)
) AS ATTRIBUTE_NUMBER_VALUE,
DECODE (AGC.DATA_TYPE_CODE, 'N', DECODE (AGC.DATABASE_COLUMN, 'N_EXT_ATTR1', UDA.UOM_EXT_ATTR1, 'N_EXT_ATTR2', UDA.UOM_EXT_ATTR2, 'N_EXT_ATTR3', UDA.UOM_EXT_ATTR3, 'N_EXT_ATTR4', UDA.UOM_EXT_ATTR4, 'N_EXT_ATTR5', UDA.UOM_EXT_ATTR5,
'N_EXT_ATTR6', UDA.UOM_EXT_ATTR6, 'N_EXT_ATTR7', UDA.UOM_EXT_ATTR7, 'N_EXT_ATTR8', UDA.UOM_EXT_ATTR8, 'N_EXT_ATTR9', UDA.UOM_EXT_ATTR9, 'N_EXT_ATTR10', UDA.UOM_EXT_ATTR10,
'N_EXT_ATTR11', UDA.UOM_EXT_ATTR11, 'N_EXT_ATTR12', UDA.UOM_EXT_ATTR12, 'N_EXT_ATTR13', UDA.UOM_EXT_ATTR13, 'N_EXT_ATTR14', UDA.UOM_EXT_ATTR14,
'N_EXT_ATTR15', UDA.UOM_EXT_ATTR15, 'N_EXT_ATTR16', UDA.UOM_EXT_ATTR16, 'N_EXT_ATTR17', UDA.UOM_EXT_ATTR17, 'N_EXT_ATTR18', UDA.UOM_EXT_ATTR18,
'N_EXT_ATTR19', UDA.UOM_EXT_ATTR19, 'N_EXT_ATTR20', UDA.UOM_EXT_ATTR20 )
) AS ATTRIBUTE_UOM_VALUE,
TO_CHAR(DECODE(AGC.DATA_TYPE_CODE, 'X', DECODE(AGC.DATABASE_COLUMN, 'D_EXT_ATTR1', UDA.D_EXT_ATTR1, 'D_EXT_ATTR2', UDA.D_EXT_ATTR2, 'D_EXT_ATTR3', UDA.D_EXT_ATTR3, 'D_EXT_ATTR4', UDA.D_EXT_ATTR4, 'D_EXT_ATTR5', UDA.D_EXT_ATTR5,
'D_EXT_ATTR6', UDA.D_EXT_ATTR6, 'D_EXT_ATTR7', UDA.D_EXT_ATTR7, 'D_EXT_ATTR8', UDA.D_EXT_ATTR8, 'D_EXT_ATTR9', UDA.D_EXT_ATTR9, 'D_EXT_ATTR10', UDA.D_EXT_ATTR10)
), 'MM/DD/YYYY'
) AS ATTRIBUTE_DATE_VALUE,
TO_CHAR(DECODE(AGC.DATA_TYPE_CODE, 'Y', DECODE(AGC.DATABASE_COLUMN, 'D_EXT_ATTR1', UDA.D_EXT_ATTR1, 'D_EXT_ATTR2', UDA.D_EXT_ATTR2, 'D_EXT_ATTR3', UDA.D_EXT_ATTR3, 'D_EXT_ATTR4', UDA.D_EXT_ATTR4, 'D_EXT_ATTR5', UDA.D_EXT_ATTR5,
'D_EXT_ATTR6', UDA.D_EXT_ATTR6, 'D_EXT_ATTR7', UDA.D_EXT_ATTR7, 'D_EXT_ATTR8', UDA.D_EXT_ATTR8, 'D_EXT_ATTR9', UDA.D_EXT_ATTR9, 'D_EXT_ATTR10', UDA.D_EXT_ATTR10)
), 'MM/DD/YYYY HH24:MI:SS'
) AS ATTRIBUTE_DATETIME_VALUE
FROM EGO_MTL_SY_ITEMS_EXT_B UDA , EGO_PUB_WS_FLAT_RECS flat ,
EGO_ATTRS_V AGC, EGO_ATTR_GROUPS_V AG
WHERE UDA.ATTR_GROUP_ID = AG.ATTR_GROUP_ID
AND AGC.APPLICATION_ID = AG.APPLICATION_ID
AND AGC.ATTR_GROUP_TYPE = AG.ATTR_GROUP_TYPE
AND AGC.ATTR_GROUP_NAME = AG.ATTR_GROUP_NAME
AND AGC.DATA_TYPE_CODE <> 'A'
AND AG.ATTR_GROUP_ID = To_Number(flat.REF1_VALUE)
AND UDA.EXTENSION_ID = To_Number(FLAT.REF2_VALUE)
AND FLAT.REF3_VALUE = To_Char(dataLevelId)
AND FLAT.session_id = sessionId
AND FLAT.ENTITY_TYPE = 'ATTRIBUTE_GROUP'
AND Nvl(AGC.enabled_flag, 'Y') = 'Y' -- Bug 9542020
) list, EGO_VALUE_SET_VALUES_V evsv
WHERE list.VALUE_SET_ID = evsv.VALUE_SET_ID (+)
AND
(
( list.ATTRIBUTE_CHAR_VALUE IS NOT NULL
AND Nvl(evsv.FORMAT_TYPE, 'C') = 'C' -- Bug 9539538
AND Nvl(evsv.INTERNAL_NAME,list.ATTRIBUTE_CHAR_VALUE ) = list.ATTRIBUTE_CHAR_VALUE
)
OR
( list.ATTRIBUTE_NUMBER_VALUE IS NOT NULL
AND Nvl(evsv.FORMAT_TYPE, 'N') = 'N' -- Bug 9539538
AND Nvl(evsv.INTERNAL_NAME,list.ATTRIBUTE_NUMBER_VALUE) = list.ATTRIBUTE_NUMBER_VALUE
)
OR
( list.ATTRIBUTE_DATE_VALUE IS NOT NULL
AND Nvl(evsv.FORMAT_TYPE, 'X') = 'X' -- Bug 9539538
-- Bug 9615220 Should change parameters to DATEor DATETIME, then compare
AND Nvl(To_Date(evsv.INTERNAL_NAME, 'YYYY-MM-DD HH24:MI:SS'),To_Date(list.ATTRIBUTE_DATE_VALUE, 'MM/DD/YYYY')) = To_Date(list.ATTRIBUTE_DATE_VALUE, 'MM/DD/YYYY')
)
OR
( list.ATTRIBUTE_DATETIME_VALUE IS NOT NULL
AND Nvl(evsv.FORMAT_TYPE, 'Y') = 'Y' -- Bug 9539538
-- Bug 9615220 Should change parameters to DATEor DATETIME, then compare
AND Nvl(To_Date(evsv.INTERNAL_NAME, 'YYYY-MM-DD HH24:MI:SS'),To_Date(list.ATTRIBUTE_DATETIME_VALUE, 'MM/DD/YYYY HH24:MI:SS')) = To_Date(list.ATTRIBUTE_DATETIME_VALUE, 'MM/DD/YYYY HH24:MI:SS')
)
);
insert into EGO_PUB_WS_FLAT_RECS
(
SEQUENCE_ID,
SESSION_ID,
ODI_SESSION_ID,
ENTITY_TYPE,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
REF1_VALUE,
REF2_VALUE,
REF3_VALUE ,
REF4_VALUE ,
REF5_VALUE ,
PARENT_SEQUENCE_ID,
VALUE,
CREATION_DATE
)
select
EGO_PUB_WS_FLAT_RECS_S.nextval,
sessionId,
odisessionId,
'UDA',
flat.PK1_VALUE ,
flat.PK2_VALUE ,
flat.PK3_VALUE ,
list.ATTRIBUTEGROUP_ID ,
list.EXTENSION_ID ,
dataLevelId ,
flat.REF4_VALUE ,
list.ATTRIBUTE_ID ,
flat.SEQUENCE_ID,
XMLForest(
list.ATTRIBUTE_NAME AS "ATTRIBUTE_NAME",
list.ATTRIBUTE_CHAR_VALUE AS "ATTRIBUTE_CHAR_VALUE",
list.ATTRIBUTE_NUMBER_VALUE AS "ATTRIBUTE_NUMBER_VALUE",
list.ATTRIBUTE_UOM_VALUE AS "ATTRIBUTE_UOM_VALUE",
list.ATTRIBUTE_DATE_VALUE AS "ATTRIBUTE_DATE_VALUE",
list.ATTRIBUTE_DATETIME_VALUE AS "ATTRIBUTE_DATETIME_VALUE" ,
NULL AS "DISPLAY_VALUE"
).getclobval() ,
SYSDATE
FROM (
(SELECT
ATTRIBUTEGROUP_ID,
EXTENSION_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
APPLICATION_ID,
DATA_LEVEL_ID,
ATTRIBUTE_ID,
ATTRIBUTE_NAME,
ATTRIBUTE_CHAR_VALUE,
ATTRIBUTE_NUMBER_VALUE,
ATTRIBUTE_UOM_VALUE,
ATTRIBUTE_DATE_VALUE,
ATTRIBUTE_DATETIME_VALUE,
null as TRANSLATED_CHAR_VALUE,
REVISION_ID
FROM ego_all_attr_base_v)
) list , EGO_PUB_WS_FLAT_RECS flat ,
ego_attrs_v eav
WHERE
list.ATTRIBUTEGROUP_ID = FLAT.REF1_VALUE
AND list.EXTENSION_ID = FLAT.REF2_VALUE
AND list.INVENTORY_ITEM_ID = FLAT.PK1_VALUE
AND list.ORGANIZATION_ID = Decode(dataLevelId , 43101, flat.REF4_VALUE,
43103 , flat.REF4_VALUE,
43104 , flat.REF4_VALUE,
FLAT.PK2_VALUE )
AND nvl(list.REVISION_ID, -1) = Decode(nvl(list.REVISION_ID, -1), -1,-1,flat.PK3_VALUE)
AND list.DATA_LEVEL_ID = dataLevelId
AND FLAT.session_id = sessionId
AND FLAT.ENTITY_TYPE = 'ATTRIBUTE_GROUP'
AND eav.attr_id = list.ATTRIBUTE_ID
AND Nvl(eav.enabled_flag, 'Y') = 'Y' -- Bug 9542020
AND list.ATTRIBUTE_CHAR_VALUE IS NULL
AND list.ATTRIBUTE_NUMBER_VALUE IS NULL
AND list.ATTRIBUTE_DATE_VALUE IS NULL
AND list.ATTRIBUTE_DATETIME_VALUE IS NULL;
insert into EGO_PUB_WS_FLAT_RECS
(
SEQUENCE_ID,
SESSION_ID,
ODI_SESSION_ID,
ENTITY_TYPE,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
REF1_VALUE,
REF2_VALUE,
REF3_VALUE ,
REF4_VALUE ,
REF5_VALUE ,
PARENT_SEQUENCE_ID,
VALUE,
CREATION_DATE
)
SELECT
EGO_PUB_WS_FLAT_RECS_S.nextval,
sessionId,
odisessionId,
'UDA',
list.PK1_VALUE ,
list.PK2_VALUE ,
list.PK3_VALUE ,
list.ATTRIBUTEGROUP_ID ,
list.EXTENSION_ID ,
dataLevelId ,
list.REF4_VALUE ,
list.ATTRIBUTE_ID ,
list.SEQUENCE_ID,
XMLForest(
list.ATTR_NAME AS "ATTRIBUTE_NAME",
list.ATTRIBUTE_CHAR_VALUE AS "ATTRIBUTE_CHAR_VALUE",
list.ATTRIBUTE_NUMBER_VALUE AS "ATTRIBUTE_NUMBER_VALUE",
list.ATTRIBUTE_UOM_VALUE AS "ATTRIBUTE_UOM_VALUE",
list.ATTRIBUTE_DATE_VALUE AS "ATTRIBUTE_DATE_VALUE",
list.ATTRIBUTE_DATETIME_VALUE AS "ATTRIBUTE_DATETIME_VALUE" ,
NULL AS "DISPLAY_VALUE"
).getclobval() ,
SYSDATE
FROM
( select
flat.PK1_VALUE ,
flat.PK2_VALUE ,
flat.PK3_VALUE ,
UDA.ATTR_GROUP_ID AS "ATTRIBUTEGROUP_ID" ,
UDA.EXTENSION_ID ,
flat.REF4_VALUE ,
AGC.attr_id AS "ATTRIBUTE_ID",
flat.SEQUENCE_ID,
AGC.VALUE_SET_ID,
AGC.ATTR_NAME,
DECODE (AGC.DATA_TYPE_CODE, 'C', DECODE (AGC.DATABASE_COLUMN, 'C_EXT_ATTR1', UDA.C_EXT_ATTR1, 'C_EXT_ATTR2', UDA.C_EXT_ATTR2, 'C_EXT_ATTR3', UDA.C_EXT_ATTR3, 'C_EXT_ATTR4', UDA.C_EXT_ATTR4, 'C_EXT_ATTR5', UDA.C_EXT_ATTR5,
'C_EXT_ATTR6', UDA.C_EXT_ATTR6, 'C_EXT_ATTR7', UDA.C_EXT_ATTR7, 'C_EXT_ATTR8', UDA.C_EXT_ATTR8, 'C_EXT_ATTR9', UDA.C_EXT_ATTR9, 'C_EXT_ATTR10', UDA.C_EXT_ATTR10,
'C_EXT_ATTR11', UDA.C_EXT_ATTR11, 'C_EXT_ATTR12', UDA.C_EXT_ATTR12, 'C_EXT_ATTR13', UDA.C_EXT_ATTR13, 'C_EXT_ATTR14', UDA.C_EXT_ATTR14, 'C_EXT_ATTR15', UDA.C_EXT_ATTR15,
'C_EXT_ATTR16', UDA.C_EXT_ATTR16, 'C_EXT_ATTR17', UDA.C_EXT_ATTR17, 'C_EXT_ATTR18', UDA.C_EXT_ATTR18, 'C_EXT_ATTR19', UDA.C_EXT_ATTR19, 'C_EXT_ATTR20', UDA.C_EXT_ATTR20,
'C_EXT_ATTR21', UDA.C_EXT_ATTR21, 'C_EXT_ATTR22', UDA.C_EXT_ATTR22, 'C_EXT_ATTR23', UDA.C_EXT_ATTR23, 'C_EXT_ATTR24', UDA.C_EXT_ATTR24, 'C_EXT_ATTR25', UDA.C_EXT_ATTR25,
'C_EXT_ATTR26', UDA.C_EXT_ATTR26, 'C_EXT_ATTR27', UDA.C_EXT_ATTR27, 'C_EXT_ATTR28', UDA.C_EXT_ATTR28, 'C_EXT_ATTR29', UDA.C_EXT_ATTR29, 'C_EXT_ATTR30', UDA.C_EXT_ATTR30,
'C_EXT_ATTR31', UDA.C_EXT_ATTR31, 'C_EXT_ATTR32', UDA.C_EXT_ATTR32, 'C_EXT_ATTR33', UDA.C_EXT_ATTR33, 'C_EXT_ATTR34', UDA.C_EXT_ATTR34, 'C_EXT_ATTR35', UDA.C_EXT_ATTR35,
'C_EXT_ATTR36', UDA.C_EXT_ATTR36, 'C_EXT_ATTR37', UDA.C_EXT_ATTR37, 'C_EXT_ATTR38', UDA.C_EXT_ATTR38, 'C_EXT_ATTR39', UDA.C_EXT_ATTR39, 'C_EXT_ATTR40', UDA.C_EXT_ATTR40 )
) AS ATTRIBUTE_CHAR_VALUE,
(DECODE (AGC.DATA_TYPE_CODE, 'N', DECODE (AGC.DATABASE_COLUMN, 'N_EXT_ATTR1', UDA.N_EXT_ATTR1, 'N_EXT_ATTR2', UDA.N_EXT_ATTR2, 'N_EXT_ATTR3', UDA.N_EXT_ATTR3, 'N_EXT_ATTR4', UDA.N_EXT_ATTR4, 'N_EXT_ATTR5', UDA.N_EXT_ATTR5,
'N_EXT_ATTR6', UDA.N_EXT_ATTR6, 'N_EXT_ATTR7', UDA.N_EXT_ATTR7, 'N_EXT_ATTR8', UDA.N_EXT_ATTR8, 'N_EXT_ATTR9', UDA.N_EXT_ATTR9, 'N_EXT_ATTR10', UDA.N_EXT_ATTR10,
'N_EXT_ATTR11', UDA.N_EXT_ATTR11, 'N_EXT_ATTR12', UDA.N_EXT_ATTR12, 'N_EXT_ATTR13', UDA.N_EXT_ATTR13, 'N_EXT_ATTR14', UDA.N_EXT_ATTR14, 'N_EXT_ATTR15', UDA.N_EXT_ATTR15,
'N_EXT_ATTR16', UDA.N_EXT_ATTR16, 'N_EXT_ATTR17', UDA.N_EXT_ATTR17, 'N_EXT_ATTR18', UDA.N_EXT_ATTR18, 'N_EXT_ATTR19', UDA.N_EXT_ATTR19, 'N_EXT_ATTR20', UDA.N_EXT_ATTR20 )
)/NVL(
(SELECT CONVERSION_RATE
FROM MTL_UOM_CONVERSIONS UOMLIST
WHERE UOMLIST.UOM_CODE =
DECODE (AGC.DATA_TYPE_CODE, 'N',
DECODE (AGC.DATABASE_COLUMN,'N_EXT_ATTR1', UDA.UOM_EXT_ATTR1, 'N_EXT_ATTR2', UDA.UOM_EXT_ATTR2, 'N_EXT_ATTR3', UDA.UOM_EXT_ATTR3, 'N_EXT_ATTR4', UDA.UOM_EXT_ATTR4, 'N_EXT_ATTR5', UDA.UOM_EXT_ATTR5,
'N_EXT_ATTR6', UDA.UOM_EXT_ATTR6, 'N_EXT_ATTR7', UDA.UOM_EXT_ATTR7, 'N_EXT_ATTR8', UDA.UOM_EXT_ATTR8, 'N_EXT_ATTR9', UDA.UOM_EXT_ATTR9, 'N_EXT_ATTR10', UDA.UOM_EXT_ATTR10,
'N_EXT_ATTR11', UDA.UOM_EXT_ATTR11, 'N_EXT_ATTR12', UDA.UOM_EXT_ATTR12, 'N_EXT_ATTR13', UDA.UOM_EXT_ATTR13, 'N_EXT_ATTR14', UDA.UOM_EXT_ATTR14, 'N_EXT_ATTR15', UDA.UOM_EXT_ATTR15,
'N_EXT_ATTR16', UDA.UOM_EXT_ATTR16, 'N_EXT_ATTR17', UDA.UOM_EXT_ATTR17, 'N_EXT_ATTR18', UDA.UOM_EXT_ATTR18, 'N_EXT_ATTR19', UDA.UOM_EXT_ATTR19, 'N_EXT_ATTR20', UDA.UOM_EXT_ATTR20)
)
AND INVENTORY_ITEM_ID = 0
), 1)
) AS ATTRIBUTE_NUMBER_VALUE,
DECODE (AGC.DATA_TYPE_CODE, 'N', DECODE (AGC.DATABASE_COLUMN, 'N_EXT_ATTR1', UDA.UOM_EXT_ATTR1, 'N_EXT_ATTR2', UDA.UOM_EXT_ATTR2, 'N_EXT_ATTR3', UDA.UOM_EXT_ATTR3, 'N_EXT_ATTR4', UDA.UOM_EXT_ATTR4, 'N_EXT_ATTR5', UDA.UOM_EXT_ATTR5,
'N_EXT_ATTR6', UDA.UOM_EXT_ATTR6, 'N_EXT_ATTR7', UDA.UOM_EXT_ATTR7, 'N_EXT_ATTR8', UDA.UOM_EXT_ATTR8, 'N_EXT_ATTR9', UDA.UOM_EXT_ATTR9, 'N_EXT_ATTR10', UDA.UOM_EXT_ATTR10,
'N_EXT_ATTR11', UDA.UOM_EXT_ATTR11, 'N_EXT_ATTR12', UDA.UOM_EXT_ATTR12, 'N_EXT_ATTR13', UDA.UOM_EXT_ATTR13, 'N_EXT_ATTR14', UDA.UOM_EXT_ATTR14,
'N_EXT_ATTR15', UDA.UOM_EXT_ATTR15, 'N_EXT_ATTR16', UDA.UOM_EXT_ATTR16, 'N_EXT_ATTR17', UDA.UOM_EXT_ATTR17, 'N_EXT_ATTR18', UDA.UOM_EXT_ATTR18,
'N_EXT_ATTR19', UDA.UOM_EXT_ATTR19, 'N_EXT_ATTR20', UDA.UOM_EXT_ATTR20 )
) AS ATTRIBUTE_UOM_VALUE,
TO_CHAR(DECODE(AGC.DATA_TYPE_CODE, 'X', DECODE(AGC.DATABASE_COLUMN, 'D_EXT_ATTR1', UDA.D_EXT_ATTR1, 'D_EXT_ATTR2', UDA.D_EXT_ATTR2, 'D_EXT_ATTR3', UDA.D_EXT_ATTR3, 'D_EXT_ATTR4', UDA.D_EXT_ATTR4, 'D_EXT_ATTR5', UDA.D_EXT_ATTR5,
'D_EXT_ATTR6', UDA.D_EXT_ATTR6, 'D_EXT_ATTR7', UDA.D_EXT_ATTR7, 'D_EXT_ATTR8', UDA.D_EXT_ATTR8, 'D_EXT_ATTR9', UDA.D_EXT_ATTR9, 'D_EXT_ATTR10', UDA.D_EXT_ATTR10)
), 'MM/DD/YYYY'
) AS ATTRIBUTE_DATE_VALUE,
TO_CHAR(DECODE(AGC.DATA_TYPE_CODE, 'Y', DECODE(AGC.DATABASE_COLUMN, 'D_EXT_ATTR1', UDA.D_EXT_ATTR1, 'D_EXT_ATTR2', UDA.D_EXT_ATTR2, 'D_EXT_ATTR3', UDA.D_EXT_ATTR3, 'D_EXT_ATTR4', UDA.D_EXT_ATTR4, 'D_EXT_ATTR5', UDA.D_EXT_ATTR5,
'D_EXT_ATTR6', UDA.D_EXT_ATTR6, 'D_EXT_ATTR7', UDA.D_EXT_ATTR7, 'D_EXT_ATTR8', UDA.D_EXT_ATTR8, 'D_EXT_ATTR9', UDA.D_EXT_ATTR9, 'D_EXT_ATTR10', UDA.D_EXT_ATTR10)
), 'MM/DD/YYYY HH24:MI:SS'
) AS ATTRIBUTE_DATETIME_VALUE
FROM EGO_MTL_SY_ITEMS_EXT_B UDA , EGO_PUB_WS_FLAT_RECS flat ,
EGO_ATTRS_V AGC, EGO_ATTR_GROUPS_V AG
WHERE UDA.ATTR_GROUP_ID = AG.ATTR_GROUP_ID
AND AGC.APPLICATION_ID = AG.APPLICATION_ID
AND AGC.ATTR_GROUP_TYPE = AG.ATTR_GROUP_TYPE
AND AGC.ATTR_GROUP_NAME = AG.ATTR_GROUP_NAME
AND AGC.DATA_TYPE_CODE <> 'A'
AND AG.ATTR_GROUP_ID = To_Number(flat.REF1_VALUE)
AND UDA.EXTENSION_ID = To_Number(FLAT.REF2_VALUE)
AND FLAT.REF3_VALUE = To_Char(dataLevelId)
AND FLAT.session_id = sessionId
AND FLAT.ENTITY_TYPE = 'ATTRIBUTE_GROUP'
AND Nvl(AGC.enabled_flag, 'Y') = 'Y' -- Bug 9542020
) list
WHERE list.ATTRIBUTE_CHAR_VALUE IS NULL
AND list.ATTRIBUTE_NUMBER_VALUE IS NULL
AND list.ATTRIBUTE_DATE_VALUE IS NULL
AND list.ATTRIBUTE_DATETIME_VALUE IS NULL;
/* Insert the translated char value if exists, for all the AGs that were inserted into the flat table for the corresponding level */
/*
insert into EGO_PUB_WS_FLAT_RECS
(
SEQUENCE_ID,
SESSION_ID,
ODI_SESSION_ID,
ENTITY_TYPE,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
REF1_VALUE,
REF2_VALUE,
REF3_VALUE,
REF4_VALUE,
REF5_VALUE,
REF6_VALUE,
PARENT_SEQUENCE_ID,
VALUE,
CREATION_DATE
)
select
EGO_PUB_WS_FLAT_RECS_S.nextval,
sessionId,
odisessionId,
'UDA',
flat.PK1_VALUE ,
flat.PK2_VALUE ,
flat.PK3_VALUE ,
list.ATTRIBUTEGROUP_ID ,
list.EXTENSION_ID ,
dataLevelId ,
flat.REF4_VALUE ,
list.ATTRIBUTE_ID ,
'TRANSLATED_CHAR_VALUE' ,
flat.SEQUENCE_ID,
XMLForest(
list.ATTRIBUTE_NAME AS "ATTRIBUTE_NAME",
list.TRANSLATED_CHAR_VALUE AS TRANSLATED_CHAR_VALUE
).getclobval() ,
SYSDATE
FROM (
(SELECT
ATTRIBUTEGROUP_ID,
EXTENSION_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
APPLICATION_ID,
DATA_LEVEL_ID,
ATTRIBUTE_ID,
ATTRIBUTE_NAME,
ATTRIBUTE_TRANSLATABLE_VALUE as TRANSLATED_CHAR_VALUE,
REVISION_ID
FROM ego_all_attr_lang_v
where language = userenv('LANG'))
) list , EGO_PUB_WS_FLAT_RECS flat
, ego_attrs_v eav -- Bug 9542020
WHERE
list.ATTRIBUTEGROUP_ID = FLAT.REF1_VALUE
AND list.EXTENSION_ID = FLAT.REF2_VALUE
AND list.INVENTORY_ITEM_ID = FLAT.PK1_VALUE
AND list.ORGANIZATION_ID = Decode(dataLevelId , 43101, flat.REF4_VALUE,
43103 , flat.REF4_VALUE,
43104 , flat.REF4_VALUE,
FLAT.PK2_VALUE )
AND nvl(list.REVISION_ID, -1) = Decode(nvl(list.REVISION_ID, -1), -1,-1,flat.PK3_VALUE)
AND list.DATA_LEVEL_ID = dataLevelId
-- Bug 9542020 - Start
AND eav.attr_id = list.ATTRIBUTE_ID
AND Nvl(eav.enabled_flag, 'Y') = 'Y'
-- Bug 9542020 - End
AND FLAT.session_id = sessionId
AND FLAT.ENTITY_TYPE = 'ATTRIBUTE_GROUP';
/* Insert the translated char value if exists, for all the AGs that were inserted into the flat table for the corresponding level */
insert into EGO_PUB_WS_FLAT_RECS
(
SEQUENCE_ID,
SESSION_ID,
ODI_SESSION_ID,
ENTITY_TYPE,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
REF1_VALUE,
REF2_VALUE,
REF3_VALUE,
REF4_VALUE,
REF5_VALUE,
REF6_VALUE,
PARENT_SEQUENCE_ID,
VALUE,
CREATION_DATE
)
select
EGO_PUB_WS_FLAT_RECS_S.nextval,
sessionId,
odisessionId,
'UDA',
list.PK1_VALUE ,
list.PK2_VALUE ,
list.PK3_VALUE ,
list.ATTRIBUTEGROUP_ID ,
list.EXTENSION_ID ,
dataLevelId ,
list.REF4_VALUE ,
list.ATTRIBUTE_ID ,
'TRANSLATED_CHAR_VALUE' ,
list.SEQUENCE_ID,
XMLForest(
list.ATTR_NAME AS "ATTRIBUTE_NAME",
list.ATTRIBUTE_TRANSLATABLE_VALUE AS TRANSLATED_CHAR_VALUE
).getclobval() ,
SYSDATE
FROM
(
select
flat.PK1_VALUE ,
flat.PK2_VALUE ,
flat.PK3_VALUE ,
UDA.ATTR_GROUP_ID AS "ATTRIBUTEGROUP_ID" ,
UDA.EXTENSION_ID ,
flat.REF4_VALUE ,
AGC.attr_id AS "ATTRIBUTE_ID",
flat.SEQUENCE_ID,
AGC.VALUE_SET_ID,
AGC.ATTR_NAME,
DECODE (AGC.DATA_TYPE_CODE, 'A', DECODE (AGC.DATABASE_COLUMN, 'TL_EXT_ATTR1', UDA.TL_EXT_ATTR1, 'TL_EXT_ATTR2', UDA.TL_EXT_ATTR2, 'TL_EXT_ATTR3', UDA.TL_EXT_ATTR3, 'TL_EXT_ATTR4', UDA.TL_EXT_ATTR4, 'TL_EXT_ATTR5', UDA.TL_EXT_ATTR5,
'TL_EXT_ATTR6', UDA.TL_EXT_ATTR6, 'TL_EXT_ATTR7', UDA.TL_EXT_ATTR7, 'TL_EXT_ATTR8', UDA.TL_EXT_ATTR8, 'TL_EXT_ATTR9', UDA.TL_EXT_ATTR9, 'TL_EXT_ATTR10', UDA.TL_EXT_ATTR10,
'TL_EXT_ATTR11', UDA.TL_EXT_ATTR11, 'TL_EXT_ATTR12', UDA.TL_EXT_ATTR12, 'TL_EXT_ATTR13', UDA.TL_EXT_ATTR13, 'TL_EXT_ATTR14', UDA.TL_EXT_ATTR14,
'TL_EXT_ATTR15', UDA.TL_EXT_ATTR15, 'TL_EXT_ATTR16', UDA.TL_EXT_ATTR16, 'TL_EXT_ATTR17', UDA.TL_EXT_ATTR17, 'TL_EXT_ATTR18', UDA.TL_EXT_ATTR18,
'TL_EXT_ATTR19', UDA.TL_EXT_ATTR19, 'TL_EXT_ATTR20', UDA.TL_EXT_ATTR20, 'TL_EXT_ATTR21', UDA.TL_EXT_ATTR21, 'TL_EXT_ATTR22', UDA.TL_EXT_ATTR22,
'TL_EXT_ATTR23', UDA.TL_EXT_ATTR23, 'TL_EXT_ATTR24', UDA.TL_EXT_ATTR24, 'TL_EXT_ATTR25', UDA.TL_EXT_ATTR25, 'TL_EXT_ATTR26', UDA.TL_EXT_ATTR26,
'TL_EXT_ATTR27', UDA.TL_EXT_ATTR27, 'TL_EXT_ATTR28', UDA.TL_EXT_ATTR28, 'TL_EXT_ATTR29', UDA.TL_EXT_ATTR29, 'TL_EXT_ATTR30', UDA.TL_EXT_ATTR30,
'TL_EXT_ATTR31', UDA.TL_EXT_ATTR31, 'TL_EXT_ATTR32', UDA.TL_EXT_ATTR32, 'TL_EXT_ATTR33', UDA.TL_EXT_ATTR33, 'TL_EXT_ATTR34', UDA.TL_EXT_ATTR34,
'TL_EXT_ATTR35', UDA.TL_EXT_ATTR35, 'TL_EXT_ATTR36', UDA.TL_EXT_ATTR36, 'TL_EXT_ATTR37', UDA.TL_EXT_ATTR37, 'TL_EXT_ATTR38', UDA.TL_EXT_ATTR38,
'TL_EXT_ATTR39', UDA.TL_EXT_ATTR39, 'TL_EXT_ATTR40', UDA.TL_EXT_ATTR40 )
) AS ATTRIBUTE_TRANSLATABLE_VALUE
FROM EGO_MTL_SY_ITEMS_EXT_TL UDA , EGO_PUB_WS_FLAT_RECS flat ,
EGO_ATTRS_V AGC, EGO_ATTR_GROUPS_V AG
WHERE UDA.ATTR_GROUP_ID = AG.ATTR_GROUP_ID
AND AGC.APPLICATION_ID = AG.APPLICATION_ID
AND AGC.ATTR_GROUP_TYPE = AG.ATTR_GROUP_TYPE
AND AGC.ATTR_GROUP_NAME = AG.ATTR_GROUP_NAME
AND AGC.DATA_TYPE_CODE = 'A'
AND UDA.language = userenv('LANG')
AND AG.ATTR_GROUP_ID = To_Number(flat.REF1_VALUE)
AND UDA.EXTENSION_ID = To_Number(FLAT.REF2_VALUE)
AND FLAT.REF3_VALUE = To_Char(dataLevelId)
AND FLAT.session_id = sessionId
AND FLAT.ENTITY_TYPE = 'ATTRIBUTE_GROUP'
AND Nvl(AGC.enabled_flag, 'Y') = 'Y' -- Bug 9542020
) list;
/* Insert Translatable values ( for Trabslated char value )*/
/*
insert into EGO_PUB_WS_FLAT_RECS
(
SEQUENCE_ID,
SESSION_ID,
ODI_SESSION_ID,
ENTITY_TYPE,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
REF1_VALUE,
PARENT_SEQUENCE_ID,
VALUE,
CREATION_DATE
)
select
EGO_PUB_WS_FLAT_RECS_S.nextval,
sessionId,
odisessionId,
'UDA_TRANSLATIONS',
flat.PK1_VALUE ,
flat.PK2_VALUE ,
flat.PK3_VALUE ,
dataLevelId ,
flat.SEQUENCE_ID,
XMLForest(
CONFIG.CHAR_VALUE AS "Language" ,
list.TRANSLATED_CHAR_VALUE AS "Value"
).getclobval() ,
SYSDATE
FROM (SELECT
ATTRIBUTEGROUP_ID,
EXTENSION_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
APPLICATION_ID,
DATA_LEVEL_ID,
ATTRIBUTE_ID,
ATTRIBUTE_NAME,
ATTRIBUTE_TRANSLATABLE_VALUE as TRANSLATED_CHAR_VALUE,
REVISION_ID,
LANGUAGE
FROM ego_all_attr_lang_v ealv
) list , EGO_PUB_WS_FLAT_RECS flat , EGO_PUB_WS_CONFIG CONFIG
WHERE list.ATTRIBUTEGROUP_ID = FLAT.REF1_VALUE
AND list.EXTENSION_ID = FLAT.REF2_VALUE
AND list.INVENTORY_ITEM_ID = FLAT.PK1_VALUE
AND list.ORGANIZATION_ID = Decode(dataLevelId , 43101, flat.REF4_VALUE,
43103 , flat.REF4_VALUE,
43104 , flat.REF4_VALUE,
FLAT.PK2_VALUE )
AND nvl(list.REVISION_ID, -1) = Decode(nvl(list.REVISION_ID, -1), -1,-1,flat.PK3_VALUE)
AND To_Char(list.ATTRIBUTE_ID) = flat.REF5_VALUE
AND To_Char(list.DATA_LEVEL_ID) = flat.REF3_VALUE
AND list.DATA_LEVEL_ID = dataLevelId
AND To_Char(list.INVENTORY_ITEM_ID) = flat.PK1_VALUE
AND FLAT.session_id = sessionId
AND CONFIG.SESSION_ID = FLAT.SESSION_ID
AND CONFIG.CHAR_VALUE = list.LANGUAGE
AND FLAT.ENTITY_TYPE = 'UDA'
AND CONFIG.PARAMETER_NAME = 'LANGUAGE_CODE'
AND FLAT.REF6_VALUE = 'TRANSLATED_CHAR_VALUE';
/* Insert Translatable values ( for Trabslated char value )*/
insert into EGO_PUB_WS_FLAT_RECS
(
SEQUENCE_ID,
SESSION_ID,
ODI_SESSION_ID,
ENTITY_TYPE,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
REF1_VALUE,
PARENT_SEQUENCE_ID,
VALUE,
CREATION_DATE
)
select
EGO_PUB_WS_FLAT_RECS_S.nextval,
sessionId,
odisessionId,
'UDA_TRANSLATIONS',
list.PK1_VALUE ,
list.PK2_VALUE ,
list.PK3_VALUE ,
dataLevelId ,
list.SEQUENCE_ID,
XMLForest(
list.CHAR_VALUE AS "Language" ,
list.ATTRIBUTE_TRANSLATABLE_VALUE AS "Value"
).getclobval() ,
SYSDATE
FROM
(
select
flat.PK1_VALUE ,
flat.PK2_VALUE ,
flat.PK3_VALUE ,
flat.SEQUENCE_ID,
CONFIG.CHAR_VALUE,
DECODE (AGC.DATA_TYPE_CODE, 'A', DECODE (AGC.DATABASE_COLUMN, 'TL_EXT_ATTR1', UDA.TL_EXT_ATTR1, 'TL_EXT_ATTR2', UDA.TL_EXT_ATTR2, 'TL_EXT_ATTR3', UDA.TL_EXT_ATTR3, 'TL_EXT_ATTR4', UDA.TL_EXT_ATTR4, 'TL_EXT_ATTR5', UDA.TL_EXT_ATTR5,
'TL_EXT_ATTR6', UDA.TL_EXT_ATTR6, 'TL_EXT_ATTR7', UDA.TL_EXT_ATTR7, 'TL_EXT_ATTR8', UDA.TL_EXT_ATTR8, 'TL_EXT_ATTR9', UDA.TL_EXT_ATTR9, 'TL_EXT_ATTR10', UDA.TL_EXT_ATTR10,
'TL_EXT_ATTR11', UDA.TL_EXT_ATTR11, 'TL_EXT_ATTR12', UDA.TL_EXT_ATTR12, 'TL_EXT_ATTR13', UDA.TL_EXT_ATTR13, 'TL_EXT_ATTR14', UDA.TL_EXT_ATTR14,
'TL_EXT_ATTR15', UDA.TL_EXT_ATTR15, 'TL_EXT_ATTR16', UDA.TL_EXT_ATTR16, 'TL_EXT_ATTR17', UDA.TL_EXT_ATTR17, 'TL_EXT_ATTR18', UDA.TL_EXT_ATTR18,
'TL_EXT_ATTR19', UDA.TL_EXT_ATTR19, 'TL_EXT_ATTR20', UDA.TL_EXT_ATTR20, 'TL_EXT_ATTR21', UDA.TL_EXT_ATTR21, 'TL_EXT_ATTR22', UDA.TL_EXT_ATTR22,
'TL_EXT_ATTR23', UDA.TL_EXT_ATTR23, 'TL_EXT_ATTR24', UDA.TL_EXT_ATTR24, 'TL_EXT_ATTR25', UDA.TL_EXT_ATTR25, 'TL_EXT_ATTR26', UDA.TL_EXT_ATTR26,
'TL_EXT_ATTR27', UDA.TL_EXT_ATTR27, 'TL_EXT_ATTR28', UDA.TL_EXT_ATTR28, 'TL_EXT_ATTR29', UDA.TL_EXT_ATTR29, 'TL_EXT_ATTR30', UDA.TL_EXT_ATTR30,
'TL_EXT_ATTR31', UDA.TL_EXT_ATTR31, 'TL_EXT_ATTR32', UDA.TL_EXT_ATTR32, 'TL_EXT_ATTR33', UDA.TL_EXT_ATTR33, 'TL_EXT_ATTR34', UDA.TL_EXT_ATTR34,
'TL_EXT_ATTR35', UDA.TL_EXT_ATTR35, 'TL_EXT_ATTR36', UDA.TL_EXT_ATTR36, 'TL_EXT_ATTR37', UDA.TL_EXT_ATTR37, 'TL_EXT_ATTR38', UDA.TL_EXT_ATTR38,
'TL_EXT_ATTR39', UDA.TL_EXT_ATTR39, 'TL_EXT_ATTR40', UDA.TL_EXT_ATTR40 )
) AS ATTRIBUTE_TRANSLATABLE_VALUE
FROM EGO_MTL_SY_ITEMS_EXT_TL UDA , EGO_PUB_WS_FLAT_RECS flat ,
EGO_ATTRS_V AGC, EGO_ATTR_GROUPS_V AG , EGO_PUB_WS_CONFIG CONFIG
WHERE UDA.ATTR_GROUP_ID = AG.ATTR_GROUP_ID
AND AGC.APPLICATION_ID = AG.APPLICATION_ID
AND AGC.ATTR_GROUP_TYPE = AG.ATTR_GROUP_TYPE
AND AGC.ATTR_GROUP_NAME = AG.ATTR_GROUP_NAME
AND AGC.DATA_TYPE_CODE = 'A'
AND AG.ATTR_GROUP_ID = To_Number(flat.REF1_VALUE)
AND UDA.EXTENSION_ID = To_Number(FLAT.REF2_VALUE)
AND To_Char(AGC.ATTR_ID) = flat.REF5_VALUE
AND To_Char(dataLevelId) = flat.REF3_VALUE
AND FLAT.session_id = sessionId
AND FLAT.ENTITY_TYPE = 'UDA'
AND FLAT.REF6_VALUE = 'TRANSLATED_CHAR_VALUE'
AND CONFIG.SESSION_ID = FLAT.SESSION_ID
AND CONFIG.CHAR_VALUE = UDA.LANGUAGE
AND CONFIG.PARAMETER_NAME = 'LANGUAGE_CODE'
) list;
insert into EGO_PUB_WS_FLAT_RECS
(
SEQUENCE_ID,
SESSION_ID,
ODI_SESSION_ID,
ENTITY_TYPE,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
REF1_VALUE,
PARENT_SEQUENCE_ID,
VALUE,
CREATION_DATE
)
SELECT
EGO_PUB_WS_FLAT_RECS_S.nextval,
sessionId,
odiSessionId,
'GTIN_CROSS_REFERENCE',
flat.PK1_VALUE,
flat.PK2_VALUE,
flat.PK3_VALUE,
CROSS_REFERENCE_ID,
flat.SEQUENCE_ID,
XMLForest(
EgoGtinEO.CROSS_REFERENCE AS "CrossReference" ,
A.ITEM_NUMBER AS "PackItemNumber" ,
EgoGtinEO.DESCRIPTION AS "GTINDescription" ,
EgoGtinEO.UOM_CODE AS "UnitOfMeasure" ,
(SELECT MIR.REVISION
FROM MTL_ITEM_REVISIONS_B MIR
WHERE EgoGtinEO.REVISION_ID = MIR.REVISION_ID
) AS "Revision" ,
EgoGtinEO.CROSS_REFERENCE_ID AS "CrossReferenceId" ,
EgoGtinEO.EPC_GTIN_SERIAL AS "EpcGtinSerial"
).getclobval() ,
SYSDATE
FROM
MTL_CROSS_REFERENCES EgoGtinEO ,
MTL_SYSTEM_ITEMS_B MSIB ,
MTL_PARAMETERS MP ,
( SELECT DISTINCT MCR1.CROSS_REFERENCE ,
MSIK.INVENTORY_ITEM_ID ,
MSIK.CONCATENATED_SEGMENTS ITEM_NUMBER ,
MSIK.ORGANIZATION_ID MASTER_ORGANIZATION_ID,
MSIK.PRIMARY_UOM_CODE
FROM MTL_CROSS_REFERENCES_B MCR1,
MTL_SYSTEM_ITEMS_KFV MSIK ,
MTL_PARAMETERS MP1
WHERE MCR1.CROSS_REFERENCE_TYPE = 'GTIN'
AND MCR1.INVENTORY_ITEM_ID = MSIK.INVENTORY_ITEM_ID
AND MSIK.ORGANIZATION_ID = MP1.MASTER_ORGANIZATION_ID
AND MSIK.PRIMARY_UOM_CODE = trim(MCR1.UOM_CODE)
) A,
EGO_PUB_WS_FLAT_RECS flat
WHERE EgoGtinEO.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND MP.ORGANIZATION_ID = flat.PK2_VALUE
AND MSIB.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID
AND EgoGtinEO.CROSS_REFERENCE_TYPE = 'GTIN'
AND MP.MASTER_ORGANIZATION_ID = flat.PK2_VALUE
AND EgoGtinEO.CROSS_REFERENCE = A.CROSS_REFERENCE(+)
AND flat.PK1_VALUE = EgoGtinEO.INVENTORY_ITEM_ID
AND flat.ENTITY_TYPE = 'ITEM'
AND flat.SESSION_ID = sessionId ;
/* Insert Translations for GTIN */
insert into EGO_PUB_WS_FLAT_RECS
(
SEQUENCE_ID,
SESSION_ID,
ODI_SESSION_ID,
ENTITY_TYPE,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PARENT_SEQUENCE_ID,
VALUE,
CREATION_DATE
)
SELECT
EGO_PUB_WS_FLAT_RECS_S.nextval,
sessionId,
odiSessionId,
'GTIN_CROSS_REFERENCE_TRANSLATIONS',
flat.PK1_VALUE,
flat.PK2_VALUE,
flat.PK3_VALUE,
flat.SEQUENCE_ID,
XMLForest(
config.char_value AS "Language" ,
mcrt.DESCRIPTION AS "GTINDescription"
).getclobval() ,
SYSDATE
FROM MTL_CROSS_REFERENCES_TL mcrt, EGO_PUB_WS_CONFIG config, EGO_PUB_WS_FLAT_RECS flat
WHERE config.SESSION_ID = flat.SESSION_ID
AND config.PARAMETER_NAME = 'LANGUAGE_CODE'
AND config.CHAR_VALUE = mcrt.LANGUAGE
AND flat.ENTITY_TYPE = 'GTIN_CROSS_REFERENCE'
AND flat.REF1_VALUE = mcrt.CROSS_REFERENCE_ID
AND flat.SESSION_ID = sessionId
AND Nvl(mcrt.DESCRIPTION, 1) = Nvl(mcrt.DESCRIPTION, 2);
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_date
GROUP BY item_catalog_group_id
HAVING MAX(start_active_date)<=cp_start_date
)
AND version_seq_id > 0
)
)
);
SELECT sequence_id,parent_sequence_id,pk1_value item_id ,pk2_value org_id, pk3_value rev_id
FROM EGO_PUB_WS_FLAT_RECS
WHERE session_id= sessionId
AND odi_session_id = odisessionId
AND entity_type ='ITEM_REVISION';
SELECT ITEM_CATALOG_GROUP_ID INTO l_item_catalog_group_id FROM MTL_SYSTEM_ITEMS_VL
WHERE INVENTORY_ITEM_ID = j.item_id AND ORGANIZATION_ID = j.org_id ;
SELECT EFFECTIVITY_DATE ,CREATION_DATE
INTO l_item_start_date, l_item_create_date
FROM MTL_ITEM_REVISIONS_VL
WHERE INVENTORY_ITEM_ID = j.item_id
AND ORGANIZATION_ID = j.org_id
AND REVISION_ID = j.rev_id;
SELECT VERSION_SEQ_ID INTO l_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 <= l_item_create_date AND
ITEM_CATALOG_GROUP_ID = l_item_catalog_group_id AND
START_ACTIVE_DATE <= l_item_start_date AND VERSION_SEQ_ID >0
GROUP BY ITEM_CATALOG_GROUP_ID
HAVING MAX(START_ACTIVE_DATE) <= l_item_start_date
);
SELECT Count(*)
INTO l_ta_entered_count
FROM EGO_PUB_WS_CONFIG
WHERE PARAMETER_NAME = 'PUBLISH_TA_ID'
AND SESSION_ID = sessionId;
SELECT Count(*)
INTO l_ta_count
FROM EGO_PUB_WS_CONFIG
WHERE PARAMETER_NAME = 'PUBLISH_TA_ID'
AND SESSION_ID = sessionId
AND NUMERIC_VALUE = k.attr_id;
INSERT INTO EGO_PUB_WS_FLAT_RECS
(
SEQUENCE_ID,
SESSION_ID,
ODI_SESSION_ID,
ENTITY_TYPE,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
REF1_VALUE,
PARENT_SEQUENCE_ID,
VALUE,
CREATION_DATE
)
SELECT
EGO_PUB_WS_FLAT_RECS_S.nextval,
sessionId ,
odisessionId,
'TRANSACTION_ATTRIBUTE',
j.item_id,
j.org_id,
j.rev_id,
k.attr_id,
j.sequence_id,
xmlforest(l_item_ta_metadata_tbl(i).attrid as "AttributeId",
k.attr_name as "AttrName",
l_item_ta_metadata_tbl(i).AttrDisplayName as "AttrDisplayName",
l_item_ta_metadata_tbl(i).SEQUENCE as "AttrSequence",
l_item_ta_metadata_tbl(i).ValueSetName AS "ValueSetName", -- Bug 8643860
l_item_ta_metadata_tbl(i).UomClass AS "UomClass", -- Bug 8643860
l_item_ta_metadata_tbl(i).defaultvalue as "DeafultValue",
l_item_ta_metadata_tbl(i).rejectedvalue as "RejectedValue",
l_item_ta_metadata_tbl(i).requiredflag as "RequiredFlag",
l_item_ta_metadata_tbl(i).readonlyflag as "ReadOnlyFlag",
l_item_ta_metadata_tbl(i).hiddenflag as "HiddenFlag",
l_item_ta_metadata_tbl(i).searchableflag as "SearchableFlag",
l_item_ta_metadata_tbl(i).checkeligibility as "CheckEligibility" ,
l_is_inherited AS "Inherited",
l_is_modified AS "Modified"
).getclobval()
,SYSDATE
FROM dual ;
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 existsNode(xmlcontent, '/itemQueryParameters/InventoryItemId') ,
existsNode(xmlcontent, '/itemQueryParameters/InventoryItemName')
INTO l_exists_inv_id , l_exists_inv_name
FROM EGO_PUB_WS_PARAMS
WHERE session_id = p_session_id;
SELECT existsNode(xmlcontent, '/itemQueryParameters/OrganizationId') ,
existsNode(xmlcontent, '/itemQueryParameters/OrganizationCode')
INTO l_exists_org_id , l_exists_org_code
FROM EGO_PUB_WS_PARAMS
WHERE session_id = p_session_id;
SELECT existsNode(xmlcontent, '/itemQueryParameters/RevisionId') ,
existsNode(xmlcontent, '/itemQueryParameters/Revision') ,
existsNode(xmlcontent, '/itemQueryParameters/RevisionDate')
INTO l_exists_rev_id , l_exists_revision, l_exists_rev_date
FROM EGO_PUB_WS_PARAMS
WHERE session_id = p_session_id;
DELETE ego_odi_ws_entities
WHERE session_id = p_session_id
AND pk1_value = pk1_value
AND pk2_value = pk2_value
AND pk3_value = pk3_value;
SELECT
ego.inventory_item_id AS inventory_item_id
,ego.organization_id AS org_id
,bom_exploder_pub.get_component_revision_id(nvl(be.component_sequence_id, 0)) AS rev_id
, bom_exploder_pub.get_component_revision_label(nvl(be.component_sequence_id, 0)) AS rev_label
, be.plan_level AS plan_level
FROM bom_explosions_all be , mtl_system_items_b_kfv ego
WHERE be.group_id = grp_id
AND ego.inventory_item_id = be.component_item_id
AND ego.organization_id = be.organization_id
AND be.plan_level <= levels
AND be.plan_level > 0
AND /* This whereclause for filter criteria: Start */
( bom_exploder_pub.get_explode_option = 1 OR
be.plan_level = 0 OR
/* Date Effectivity */
( nvl(be.effectivity_control,1) = 1 AND
( ( be.implementation_date IS NULL AND
be.acd_type = 3 AND
decode(be.comp_fixed_revision_id,
NULL,bom_exploder_pub.get_explosion_date,
bom_exploder_pub.get_revision_highdate(be.comp_fixed_revision_id)) >= be.trimmed_effectivity_date
) OR
( bom_exploder_pub.get_explode_option = 2 AND
decode(be.comp_fixed_revision_id,
NULL,bom_exploder_pub.get_explosion_date,
bom_exploder_pub.get_revision_highdate(be.comp_fixed_revision_id)) >= be.trimmed_effectivity_date AND
decode(be.comp_fixed_revision_id,
NULL,bom_exploder_pub.get_explosion_date,
bom_exploder_pub.get_revision_highdate(be.comp_fixed_revision_id)) < nvl(be.trimmed_disable_date,to_date('9999/12/31 00:00:00','yyyy/mm/dd hh24:mi:ss'))
) OR
( bom_exploder_pub.get_explode_option = 3 AND
decode(be.comp_fixed_revision_id,
NULL,bom_exploder_pub.get_explosion_date,
bom_exploder_pub.get_revision_highdate(be.comp_fixed_revision_id)) < nvl(be.trimmed_disable_date,to_date('9999/12/31 00:00:00','yyyy/mm/dd hh24:mi:ss'))
)
)
) OR
/* Rev Effectivity */
( nvl(be.effectivity_control,1) = 4 AND
( ( bom_exploder_pub.get_explode_option = 2 AND
( ( bom_exploder_pub.get_expl_end_item_rev_code >= (SELECT revision FROM mtl_item_revisions_b
WHERE inventory_item_id = be.end_item_id
AND organization_id = be.end_item_org_id
AND revision_id = be.from_end_item_rev_id) AND
( be.to_end_item_rev_id IS NULL OR
bom_exploder_pub.get_expl_end_item_rev_code <= (SELECT revision FROM mtl_item_revisions_b
WHERE inventory_item_id = be.end_item_id
AND organization_id = be.end_item_org_id
AND revision_id = be.to_end_item_rev_id)
)
) OR
( be.plan_level > 1 AND
bom_exploder_pub.get_component_revision(nvl(be.parent_comp_seq_id,0)) >= (SELECT revision FROM mtl_item_revisions_b
WHERE inventory_item_id = be.assembly_item_id
AND organization_id = be.organization_id
AND revision_id = be.from_end_item_rev_id) AND
( be.to_end_item_rev_id IS NULL OR
bom_exploder_pub.get_component_revision(nvl(be.parent_comp_seq_id,0)) <= (SELECT revision FROM mtl_item_revisions_b
WHERE inventory_item_id = be.assembly_item_id
AND organization_id = be.organization_id
AND revision_id = be.to_end_item_rev_id)
)
)
)
) OR
( bom_exploder_pub.get_explode_option = 3 AND
( ( be.to_end_item_rev_id IS NULL
) OR
( bom_exploder_pub.get_expl_end_item_rev_code <= (SELECT revision FROM mtl_item_revisions_b
WHERE inventory_item_id = be.end_item_id
AND organization_id = be.end_item_org_id
AND revision_id = be.to_end_item_rev_id)
) OR
( be.plan_level > 1 AND
bom_exploder_pub.get_component_revision(nvl(be.parent_comp_seq_id,0)) <= (SELECT revision FROM mtl_item_revisions_b
WHERE inventory_item_id = be.assembly_item_id
AND organization_id = be.organization_id
AND revision_id = be.to_end_item_rev_id)
)
)
)
)
) OR
/* Unit/Serial Effectivity */
( nvl(be.effectivity_control,1) = 2 AND
( ( bom_exploder_pub.get_explode_option = 2 AND
bom_exploder_pub.get_expl_unit_number BETWEEN be.trimmed_from_unit_number AND nvl(be.trimmed_to_unit_number,bom_exploder_pub.get_expl_unit_number)
) OR
( bom_exploder_pub.get_explode_option = 3 AND
bom_exploder_pub.get_expl_unit_number <= nvl(be.trimmed_to_unit_number,bom_exploder_pub.get_expl_unit_number)
)
)
)
);
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,'INVOCATION_MODE',2,NULL,l_mode,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,'BATCH_ID',2,NULL,NULL,l_batch_id,SYSDATE,G_CURRENT_USER_ID);
SELECT pk1_value , pk2_value ,pk3_value
BULK COLLECT INTO l_item_id_tab, l_org_id_tab, l_rev_id_tab
FROM Ego_Pub_Bat_Ent_Objs_v --Find OUT NOCOPY if any other PK's
WHERE batch_id = l_batch_id
AND USER_ENTERED = 'Y';
SELECT CHAR_VALUE INTO l_alt_desg FROM EGO_PUB_BAT_PARAMS_B
WHERE type_id=l_batch_id AND Upper(parameter_name) ='STRUCTURE_NAME';
SELECT DATE_VALUE INTO l_rev_date FROM EGO_PUB_BAT_PARAMS_B
WHERE type_id=l_batch_id AND Upper(parameter_name) ='EXPLOSION_DATE';
SELECT NUMERIC_VALUE INTO l_levels_to_explode FROM EGO_PUB_BAT_PARAMS_B
WHERE type_id = l_batch_id AND Upper(parameter_name) ='LEVELS_TO_EXPLODE';
SELECT NUMERIC_VALUE INTO l_explode_option FROM EGO_PUB_BAT_PARAMS_B
WHERE type_id = l_batch_id AND Upper(parameter_name) ='EXPLODE_OPTION';
SELECT CHAR_VALUE INTO l_expl_std_bom FROM EGO_PUB_BAT_PARAMS_B
WHERE type_id=l_batch_id AND Upper(parameter_name) ='EXPLODE_STD_BOM';
INSERT INTO ego_odi_ws_entities ( session_id,odi_session_id,entity_type,pk1_value,pk2_value,pk3_value,pk4_value, SEQUENCE_NUMBER)
VALUES (p_session_id,p_odi_session_id,'ITEM',l_item_id_tab(i),l_org_id_tab(i),l_rev_id_tab(i), i, i);
SELECT existsNode(xmlcontent, '/itemQueryParameters/StructureName')
INTO l_exists_struct_name
FROM EGO_PUB_WS_PARAMS
WHERE session_id = p_session_id;
SELECT extractValue(xmlcontent, '/itemQueryParameters/StructureName')
INTO l_alt_desg
FROM EGO_PUB_WS_PARAMS
WHERE session_id = p_session_id;
SELECT existsNode(xmlcontent, '/itemQueryParameters/BomExploderParameters/LevelsToExplode'),
existsNode(xmlcontent, '/itemQueryParameters/BomExploderParameters/ExplodeOption'),
existsNode(xmlcontent, '/itemQueryParameters/BomExploderParameters/ExplodeStandard') -- Bug 8752314 : CMR Change
INTO l_exists_levels_to_explode, l_exists_explode_option, l_exists_explode_std
FROM EGO_PUB_WS_PARAMS
WHERE session_id = p_session_id;
SELECT nvl(extractValue(xmlcontent, '/itemQueryParameters/BomExploderParameters/LevelsToExplode'),60)
INTO l_levels_to_explode
FROM EGO_PUB_WS_PARAMS
WHERE session_id = p_session_id;
SELECT nvl(extractValue(xmlcontent, '/itemQueryParameters/BomExploderParameters/ExplodeOption'),2)
INTO l_explode_option
FROM EGO_PUB_WS_PARAMS
WHERE session_id = p_session_id;
SELECT nvl(extractValue(xmlcontent, '/itemQueryParameters/BomExploderParameters/ExplodeStandard'),'Y')
INTO l_expl_std_bom
FROM EGO_PUB_WS_PARAMS
WHERE session_id = p_session_id;
SELECT pk1_value , pk2_value ,pk3_value, SEQUENCE_NUMBER, To_Date(pk5_value,'YYYY.MM.DD HH24:MI:SS') -- Bug 8659192
BULK COLLECT INTO l_item_id_tab, l_org_id_tab, l_rev_id_tab, l_seq_num_tab, l_rev_date_tab
FROM ego_odi_ws_entities
WHERE session_id = p_session_id
AND nvl(REF1_VALUE, 'Y') = 'Y';
SELECT pk1_value , pk2_value ,pk3_value, SEQUENCE_NUMBER
BULK COLLECT INTO l_item_id_tab, l_org_id_tab, l_rev_id_tab , l_seq_num_tab
FROM ego_odi_ws_entities
WHERE session_id = p_session_id
AND nvl(REF1_VALUE, 'Y') = 'Y';
INSERT INTO ego_odi_ws_entities ( session_id, odi_session_id, entity_type, pk1_value, pk2_value, pk3_value, pk4_value)
VALUES (p_session_id,p_odi_session_id,'ITEM',j.inventory_item_id,j.org_id,j.rev_id, l_seq_num_tab(i) );
for k in (SELECT DISTINCT pk1_value , pk2_value ,pk3_value -- Bug 9530282
FROM ego_odi_ws_entities
WHERE session_id = p_session_id
AND nvl(REF1_VALUE, 'Y') = 'Y')
loop
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 pk2_value = k.pk2_value
AND pk3_value = k.pk3_value;
FOR i IN (SELECT pk1_value, pk2_value, pk3_value FROM ego_odi_ws_entities
WHERE session_id = p_session_id AND entity_type = 'ITEM')
LOOP
select Count(*) INTO l_duplicates_count FROM ego_odi_ws_entities
WHERE session_id = p_session_id and pk1_value = i.pk1_value and pk2_value = i.pk2_value AND pk3_value = i.pk3_value
AND nvl(REF1_VALUE, 'Y') = 'Y'; -- Bug 8658259
DELETE ego_odi_ws_entities
WHERE session_id = p_session_id
AND pk1_value = i.pk1_value
AND pk2_value = i.pk2_value
AND pk3_value = i.pk3_value
AND ROWNUM < l_duplicates_count;
select count(*) into v_count
FROM ego_odi_ws_entities
WHERE session_id = p_session_id
AND nvl(REF1_VALUE, 'Y') = 'Y';
/* Insert all the configurations into the table in below procedure */
process_configurations(p_session_id, p_odi_session_id);
SELECT MTL_BUSINESS_EVENTS_S.NEXTVAL into l_event_num FROM dual;
l_parameter_list.DELETE;
SELECT Nvl(Max(INPUT_ID),0) + 1 into p_input_id
FROM EGO_PUB_WS_INPUT_IDENTIFIERS
WHERE session_id = p_session_id;
SELECT Nvl(Max(INPUT_ID),0) + 1 into p_input_id
FROM EGO_PUB_WS_INPUT_IDENTIFIERS
WHERE session_id = p_session_id;
SELECT Nvl(Max(INPUT_ID),0) + 1 into p_input_id
FROM EGO_PUB_WS_INPUT_IDENTIFIERS
WHERE session_id = p_session_id;
SELECT Nvl(Max(INPUT_ID),0) + 1 into p_input_id
FROM EGO_PUB_WS_INPUT_IDENTIFIERS
WHERE session_id = p_session_id;
SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/ReturnPayload')
INTO l_node_exists
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,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
VALUES (p_session_id,p_odi_session_id,'return_payload',2,NULL,'TRUE',NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT Upper(Nvl(extractValue(ret_pay, '/ReturnPayload'),'Y'))
INTO l_retpayload
FROM (SELECT Value(retpay) ret_pay
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/ReturnPayload') )) 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,'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_odi_session_id,'return_payload',2,NULL,'FALSE',NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT extractValue(lang, '/ListOfLanguages/LanguageCode'), extractValue(lang, '/ListOfLanguages/LanguageName')
BULK COLLECT INTO l_lang_code_tab, l_lang_name_tab
FROM (SELECT Value(lang) lang
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/ListOfLanguages') )) lang
WHERE session_id= p_session_id
);
--Insert record into config table for parameter language
-- Bug 8670897 : Below code is modified to handle Language Name along with Language Code
IF ((l_lang_name_tab.Count = l_lang_code_tab.Count) AND l_lang_code_tab.Count > 0 AND l_lang_name_tab.Count > 0 ) THEN
l_lang_count := 0;
SELECT language_code INTO l_lang_code_tab(i)
FROM FND_LANGUAGES WHERE NLS_LANGUAGE = l_lang_name_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)
VALUES (p_session_id,p_odi_session_id,'LANGUAGE_CODE',2,NULL,l_lang_code_tab(i),NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT Nvl(Max(INPUT_ID),0) + 1 into p_index
FROM EGO_PUB_WS_INPUT_IDENTIFIERS
WHERE session_id = p_session_id;
SELECT language_code INTO l_lang_code_tab(i)
FROM FND_LANGUAGES WHERE 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)
VALUES (p_session_id,p_odi_session_id,'LANGUAGE_CODE',2,NULL,l_lang_code_tab(i),NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT Nvl(Max(INPUT_ID),0) + 1 into p_index
FROM EGO_PUB_WS_INPUT_IDENTIFIERS
WHERE session_id = p_session_id;
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)
VALUES (p_session_id,p_odi_session_id,'LANGUAGE_CODE',2,NULL,i.language_code,NULL,SYSDATE,G_CURRENT_USER_ID);
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)
VALUES (p_session_id,p_odi_session_id,'LANGUAGE_CODE',2,NULL,i.language_code,NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/OperationalAttributeGroups')
INTO l_node_exists
FROM EGO_PUB_WS_PARAMS
WHERE session_id = p_session_id;
SELECT Nvl(extractValue(uda_ag, '/OperationalAttributeGroups'),'Y')
INTO L_PUBLISH_OP_ATTR_GROUPS
FROM (SELECT Value(udaag) uda_ag
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/OperationalAttributeGroups') )) 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,'PUBLISH_OP_ATTR_GROUPS',2,NULL,Upper(L_PUBLISH_OP_ATTR_GROUPS),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,'PUBLISH_OP_ATTR_GROUPS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups')
INTO l_node_exists
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,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
VALUES (p_session_id,p_odi_session_id,'PUBLISH_ITEM_CATALOG',2,NULL,'Y',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,'PUBLISH_INV_CHARS',2,NULL,'Y',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,'PUBLISH_PHY_CHARS',2,NULL,'Y',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,'PUBLISH_BOM_CHARS',2,NULL,'Y',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,'PUBLISH_WIP_CHARS',2,NULL,'Y',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,'PUBLISH_COST_CHARS',2,NULL,'Y',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,'PUBLISH_PLT_CHARS',2,NULL,'Y',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,'PUBLISH_PLAN_CHARS',2,NULL,'Y',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,'PUBLISH_PURCHASE_CHARS',2,NULL,'Y',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,'PUBLISH_OM_CHARS',2,NULL,'Y',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,'PUBLISH_RECEIVE_CHARS',2,NULL,'Y',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,'PUBLISH_INVOICE_CHARS',2,NULL,'Y',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,'PUBLISH_WEBOPT_CHARS',2,NULL,'Y',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,'PUBLISH_SERVICE_CHARS',2,NULL,'Y',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,'PUBLISH_ASSET_CHARS',2,NULL,'Y',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,'PUBLISH_PMFG_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/ItemCatalog')
INTO l_node_exists
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,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
VALUES (p_session_id,p_odi_session_id,'PUBLISH_ITEM_CATALOG',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT Nvl(extractValue(uda_ag, '/ItemCatalog'),'Y')
INTO L_PUBLISH_ITEM_CATALOG
FROM (SELECT Value(udaag) uda_ag
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/ItemCatalog') )) 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,'PUBLISH_ITEM_CATALOG',2,NULL,Upper(L_PUBLISH_ITEM_CATALOG),NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/InventoryCharacteristics')
INTO l_node_exists
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,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
VALUES (p_session_id,p_odi_session_id,'PUBLISH_INV_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT Nvl(extractValue(uda_ag, '/InventoryCharacteristics'),'Y')
INTO L_PUBLISH_INV_CHARS
FROM (SELECT Value(udaag) uda_ag
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/InventoryCharacteristics') )) 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,'PUBLISH_INV_CHARS',2,NULL,Upper(L_PUBLISH_INV_CHARS),NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/PhysicalCharacteristics')
INTO l_node_exists
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,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
VALUES (p_session_id,p_odi_session_id,'PUBLISH_PHY_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT Nvl(extractValue(uda_ag, '/PhysicalCharacteristics'),'Y')
INTO L_PUBLISH_PHY_CHARS
FROM (SELECT Value(udaag) uda_ag
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/PhysicalCharacteristics') )) 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,'PUBLISH_PHY_CHARS',2,NULL,Upper(L_PUBLISH_PHY_CHARS),NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/BillsOfMaterialCharacteristics')
INTO l_node_exists
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,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
VALUES (p_session_id,p_odi_session_id,'PUBLISH_BOM_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT Nvl(extractValue(uda_ag, '/BillsOfMaterialCharacteristics'),'Y')
INTO L_PUBLISH_BOM_CHARS
FROM (SELECT Value(udaag) uda_ag
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/BillsOfMaterialCharacteristics') )) 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,'PUBLISH_BOM_CHARS',2,NULL,Upper(L_PUBLISH_BOM_CHARS),NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/WorkInProcessCharacteristics')
INTO l_node_exists
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,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
VALUES (p_session_id,p_odi_session_id,'PUBLISH_WIP_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT Nvl(extractValue(uda_ag, '/WorkInProcessCharacteristics'),'Y')
INTO L_PUBLISH_WIP_CHARS
FROM (SELECT Value(udaag) uda_ag
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/WorkInProcessCharacteristics') )) 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,'PUBLISH_WIP_CHARS',2,NULL,Upper(L_PUBLISH_WIP_CHARS),NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/CostingCharacteristics')
INTO l_node_exists
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,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
VALUES (p_session_id,p_odi_session_id,'PUBLISH_COST_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT Nvl(extractValue(uda_ag, '/CostingCharacteristics'),'Y')
INTO L_PUBLISH_COST_CHARS
FROM (SELECT Value(udaag) uda_ag
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/CostingCharacteristics') )) 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,'PUBLISH_COST_CHARS',2,NULL,Upper(L_PUBLISH_COST_CHARS),NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/ProcessingLeadTimeCharacteristics')
INTO l_node_exists
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,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
VALUES (p_session_id,p_odi_session_id,'PUBLISH_PLT_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT Nvl(extractValue(uda_ag, '/ProcessingLeadTimeCharacteristics'),'Y')
INTO L_PUBLISH_PLT_CHARS
FROM (SELECT Value(udaag) uda_ag
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/ProcessingLeadTimeCharacteristics') )) 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,'PUBLISH_PLT_CHARS',2,NULL,Upper(L_PUBLISH_PLT_CHARS),NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/PlanningCharacteristics')
INTO l_node_exists
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,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
VALUES (p_session_id,p_odi_session_id,'PUBLISH_PLAN_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT Nvl(extractValue(uda_ag, '/PlanningCharacteristics'),'Y')
INTO L_PUBLISH_PLAN_CHARS
FROM (SELECT Value(udaag) uda_ag
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/PlanningCharacteristics') )) 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,'PUBLISH_PLAN_CHARS',2,NULL,Upper(L_PUBLISH_PLAN_CHARS),NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/PurchasingCharacteristics')
INTO l_node_exists
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,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
VALUES (p_session_id,p_odi_session_id,'PUBLISH_PURCHASE_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT Nvl(extractValue(uda_ag, '/PurchasingCharacteristics'),'Y')
INTO L_PUBLISH_PURCHASE_CHARS
FROM (SELECT Value(udaag) uda_ag
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/PurchasingCharacteristics') )) 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,'PUBLISH_PURCHASE_CHARS',2,NULL,Upper(L_PUBLISH_PURCHASE_CHARS),NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/OrderManagementCharacteristics')
INTO l_node_exists
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,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
VALUES (p_session_id,p_odi_session_id,'PUBLISH_OM_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT Nvl(extractValue(uda_ag, '/OrderManagementCharacteristics'),'Y')
INTO L_PUBLISH_OM_CHARS
FROM (SELECT Value(udaag) uda_ag
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/OrderManagementCharacteristics') )) 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,'PUBLISH_OM_CHARS',2,NULL,Upper(L_PUBLISH_OM_CHARS),NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/ReceivingCharacteristics')
INTO l_node_exists
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,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
VALUES (p_session_id,p_odi_session_id,'PUBLISH_RECEIVE_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT Nvl(extractValue(uda_ag, '/ReceivingCharacteristics'),'Y')
INTO L_PUBLISH_RECEIVE_CHARS
FROM (SELECT Value(udaag) uda_ag
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/ReceivingCharacteristics') )) 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,'PUBLISH_RECEIVE_CHARS',2,NULL,Upper(L_PUBLISH_RECEIVE_CHARS),NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/InvoicingCharacteristics')
INTO l_node_exists
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,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
VALUES (p_session_id,p_odi_session_id,'PUBLISH_INVOICE_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT Nvl(extractValue(uda_ag, '/InvoicingCharacteristics'),'Y')
INTO L_PUBLISH_INVOICE_CHARS
FROM (SELECT Value(udaag) uda_ag
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/InvoicingCharacteristics') )) 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,'PUBLISH_INVOICE_CHARS',2,NULL,Upper(L_PUBLISH_INVOICE_CHARS),NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/WebOptionsCharacteristics')
INTO l_node_exists
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,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
VALUES (p_session_id,p_odi_session_id,'PUBLISH_WEBOPT_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT Nvl(extractValue(uda_ag, '/WebOptionsCharacteristics'),'Y')
INTO L_PUBLISH_WEBOPT_CHARS
FROM (SELECT Value(udaag) uda_ag
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/WebOptionsCharacteristics') )) 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,'PUBLISH_WEBOPT_CHARS',2,NULL,Upper(L_PUBLISH_WEBOPT_CHARS),NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/ServiceCharacteristics')
INTO l_node_exists
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,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
VALUES (p_session_id,p_odi_session_id,'PUBLISH_SERVICE_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT Nvl(extractValue(uda_ag, '/ServiceCharacteristics'),'Y')
INTO L_PUBLISH_SERVICE_CHARS
FROM (SELECT Value(udaag) uda_ag
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/ServiceCharacteristics') )) 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,'PUBLISH_SERVICE_CHARS',2,NULL,Upper(L_PUBLISH_SERVICE_CHARS),NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/AssetCharacteristics')
INTO l_node_exists
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,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
VALUES (p_session_id,p_odi_session_id,'PUBLISH_ASSET_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT Nvl(extractValue(uda_ag, '/AssetCharacteristics'),'Y')
INTO L_PUBLISH_ASSET_CHARS
FROM (SELECT Value(udaag) uda_ag
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/AssetCharacteristics') )) 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,'PUBLISH_ASSET_CHARS',2,NULL,Upper(L_PUBLISH_ASSET_CHARS),NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/ProcessMfgCharacteristics')
INTO l_node_exists
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,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
VALUES (p_session_id,p_odi_session_id,'PUBLISH_PMFG_CHARS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT Nvl(extractValue(uda_ag, '/ProcessMfgCharacteristics'),'Y')
INTO L_PUBLISH_PMFG_CHARS
FROM (SELECT Value(udaag) uda_ag
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/PublishOperationalAttributeGroups/ProcessMfgCharacteristics') )) 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,'PUBLISH_PMFG_CHARS',2,NULL,Upper(L_PUBLISH_PMFG_CHARS),NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/UserDefinedAttributeGroups')
INTO l_node_exists
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,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
VALUES (p_session_id,p_odi_session_id,'PUBLISH_UDA_GROUPS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT Nvl(extractValue(uda_ag, '/UserDefinedAttributeGroups'),'Y')
INTO L_PUBLISH_UDA_GROUPS
FROM (SELECT Value(udaag) uda_ag
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/UserDefinedAttributeGroups') )) 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,'PUBLISH_UDA_GROUPS',2,NULL,Upper(L_PUBLISH_UDA_GROUPS),NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/ItemRevision')
INTO l_node_exists
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,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
VALUES (p_session_id,p_odi_session_id,'PUBLISH_ITEM_REVISION',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT Nvl(extractValue(uda_ag, '/ItemRevision'),'Y')
INTO L_PUBLISH_ITEM_REVISION
FROM (SELECT Value(udaag) uda_ag
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/ItemRevision') )) 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,'PUBLISH_ITEM_REVISION',2,NULL,Upper(L_PUBLISH_ITEM_REVISION),NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/TransactionAttributes')
INTO l_node_exists
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,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
VALUES (p_session_id,p_odi_session_id,'PUBLISH_TRANSACTION_ATTRS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT Nvl(extractValue(uda_ag, '/TransactionAttributes'),'Y')
INTO L_PUBLISH_TRANSACTION_ATTRS
FROM (SELECT Value(udaag) uda_ag
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/TransactionAttributes') )) 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,'PUBLISH_TRANSACTION_ATTRS',2,NULL,Upper(L_PUBLISH_TRANSACTION_ATTRS),NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/RelatedItems')
INTO l_node_exists
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,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
VALUES (p_session_id,p_odi_session_id,'PUBLISH_RELATED_ITEMS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT Nvl(extractValue(uda_ag, '/RelatedItems'),'Y')
INTO L_PUBLISH_RELATED_ITEMS
FROM (SELECT Value(udaag) uda_ag
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/RelatedItems') )) 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,'PUBLISH_RELATED_ITEMS',2,NULL,Upper(L_PUBLISH_RELATED_ITEMS),NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/CustomerItems')
INTO l_node_exists
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,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
VALUES (p_session_id,p_odi_session_id,'PUBLISH_CUSTOMER_ITEMS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT Nvl(extractValue(uda_ag, '/CustomerItems'),'Y')
INTO L_PUBLISH_CUSTOMER_ITEMS
FROM (SELECT Value(udaag) uda_ag
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/CustomerItems') )) 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,'PUBLISH_CUSTOMER_ITEMS',2,NULL,Upper(L_PUBLISH_CUSTOMER_ITEMS),NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/ManufacturerPartNumbers')
INTO l_node_exists
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,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
VALUES (p_session_id,p_odi_session_id,'PUBLISH_MFGPART_NUMBERS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT Nvl(extractValue(uda_ag, '/ManufacturerPartNumbers'),'Y')
INTO L_PUBLISH_MFGPART_NUMBERS
FROM (SELECT Value(udaag) uda_ag
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/ManufacturerPartNumbers') )) 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,'PUBLISH_MFGPART_NUMBERS',2,NULL,Upper(L_PUBLISH_MFGPART_NUMBERS),NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/GTINCrossReferences')
INTO l_node_exists
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,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
VALUES (p_session_id,p_odi_session_id,'PUBLISH_GTIN_XREFS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT Nvl(extractValue(uda_ag, '/GTINCrossReferences'),'Y')
INTO L_PUBLISH_GTIN_XREFS
FROM (SELECT Value(udaag) uda_ag
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/GTINCrossReferences') )) 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,'PUBLISH_GTIN_XREFS',2,NULL,Upper(L_PUBLISH_GTIN_XREFS),NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/AlternateCategoryAssignments')
INTO l_node_exists
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,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
VALUES (p_session_id,p_odi_session_id,'PUBLISH_ALTCAT_ASSIGNMENTS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT Nvl(extractValue(uda_ag, '/AlternateCategoryAssignments'),'Y')
INTO L_PUBLISH_ALTCAT_ASSIGNMENTS
FROM (SELECT Value(udaag) uda_ag
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/AlternateCategoryAssignments') )) 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,'PUBLISH_ALTCAT_ASSIGNMENTS',2,NULL,Upper(L_PUBLISH_ALTCAT_ASSIGNMENTS),NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/SupplierAssignments')
INTO l_node_exists
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,Date_Value,Char_value,Numeric_Value,creation_date,created_by)
VALUES (p_session_id,p_odi_session_id,'PUBLISH_SUPPLIER_ASSIGNMNETS',2,NULL,'Y',NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT Nvl(extractValue(uda_ag, '/SupplierAssignments'),'Y')
INTO L_PUBLISH_SUPPLIER_ASSIGNMNETS
FROM (SELECT Value(udaag) uda_ag
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/SupplierAssignments') )) 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,'PUBLISH_SUPPLIER_ASSIGNMNETS',2,NULL,Upper(L_PUBLISH_SUPPLIER_ASSIGNMNETS),NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT CHAR_VALUE INTO v_publish_udas
FROM EGO_PUB_WS_CONFIG
WHERE Parameter_Name = 'PUBLISH_UDA_GROUPS'
AND session_id = p_session_id;
SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/ListOfPublishUserDefinedAttributeGroups')
INTO l_node_exists
FROM EGO_PUB_WS_PARAMS
WHERE session_id = p_session_id;
SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/ListOfPublishUserDefinedAttributeGroups/AttributeGroupId') ,
existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/ListOfPublishUserDefinedAttributeGroups/AttributeGroupName')
INTO l_node_exists_ag_id, l_node_exists_ag_name
FROM EGO_PUB_WS_PARAMS
WHERE session_id = p_session_id;
SELECT extractValue(uda_ag, '/AttributeGroupId')
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, '/itemQueryParameters/PublishEntities/ListOfPublishUserDefinedAttributeGroups/AttributeGroupId') )) udaag
WHERE session_id=p_session_id
);
SELECT extractValue(uda_ag, '/AttributeGroupName')
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, '/itemQueryParameters/PublishEntities/ListOfPublishUserDefinedAttributeGroups/AttributeGroupName') )) udaag
WHERE session_id=p_session_id
);
SELECT ATTR_GROUP_NAME
INTO l_uda_attr_name_tab(i) -- v_attr_group_name
FROM ego_attr_groups_v
WHERE ATTR_GROUP_NAME = l_uda_attr_name_tab(i)
AND ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP';
SELECT Nvl(Max(INPUT_ID),0) + 1 into p_index
FROM EGO_PUB_WS_INPUT_IDENTIFIERS
WHERE session_id = p_session_id;
SELECT ATTR_GROUP_NAME
INTO l_uda_attr_name_tab(i) -- v_attr_group_name
FROM ego_attr_groups_v
WHERE ATTR_GROUP_ID = l_uda_attr_id_tab(i)
AND ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP';
SELECT Nvl(Max(INPUT_ID),0) + 1 into p_index
FROM EGO_PUB_WS_INPUT_IDENTIFIERS
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,'PUBLISH_AG_NAME',2,NULL, l_uda_attr_name_tab(i),NULL,SYSDATE,G_CURRENT_USER_ID);
SELECT Count(*) INTO v_ags_count
FROM EGO_PUB_WS_CONFIG
WHERE Parameter_Name = 'PUBLISH_AG_NAME'
AND session_id = p_session_id;
UPDATE EGO_PUB_WS_CONFIG
SET Char_value = 'N'
WHERE session_id = p_session_id
AND Parameter_Name = 'PUBLISH_UDA_GROUPS';
SELECT CHAR_VALUE INTO v_publish_tas
FROM EGO_PUB_WS_CONFIG
WHERE Parameter_Name = 'PUBLISH_TRANSACTION_ATTRS'
AND session_id = p_session_id;
SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/ListOfPublishTransactionAttributes')
INTO l_node_exists
FROM EGO_PUB_WS_PARAMS
WHERE session_id = p_session_id;
SELECT existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/ListOfPublishTransactionAttributes/AttributeId') ,
existsNode(xmlcontent, '/itemQueryParameters/PublishEntities/ListOfPublishTransactionAttributes/AttributeName')
INTO l_node_exists_ta_id, l_node_exists_ta_name
FROM EGO_PUB_WS_PARAMS
WHERE session_id = p_session_id;
SELECT extractValue(ta_attr, '/AttributeId')
BULK COLLECT INTO l_ta_attr_id_tab
FROM (SELECT Value(ta) ta_attr
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/ListOfPublishTransactionAttributes/AttributeId') )) ta
WHERE session_id=p_session_id
);
SELECT extractValue(ta_attr, '/AttributeName')
BULK COLLECT INTO l_ta_attr_name_tab
FROM (SELECT Value(ta) ta_attr
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/itemQueryParameters/PublishEntities/ListOfPublishTransactionAttributes/AttributeName') )) ta
WHERE session_id=p_session_id
);
SELECT ATTR_ID
INTO l_ta_attr_id_tab(i)
FROM ego_attrs_v
WHERE ATTR_NAME = l_ta_attr_name_tab(i)
AND ATTR_GROUP_TYPE = 'EGO_ITEM_TRANS_ATTR_GROUP';
SELECT Nvl(Max(INPUT_ID),0) + 1 into p_index
FROM EGO_PUB_WS_INPUT_IDENTIFIERS
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,'PUBLISH_TA_ID',2,NULL, l_ta_attr_name_tab(i), NULL , SYSDATE, G_CURRENT_USER_ID);
FOR j IN ( SELECT ATTR_ID
FROM ego_attrs_v
WHERE ATTR_NAME = l_ta_attr_name_tab(i)
AND ATTR_GROUP_TYPE = 'EGO_ITEM_TRANS_ATTR_GROUP')
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)
VALUES (p_session_id,p_odi_session_id,'PUBLISH_TA_ID',1,NULL, NULL,j.ATTR_ID, SYSDATE, G_CURRENT_USER_ID);
SELECT ATTR_ID
INTO l_ta_attr_id_tab(i)
FROM ego_attrs_v
WHERE ATTR_ID = l_ta_attr_id_tab(i)
AND ATTR_GROUP_TYPE = 'EGO_ITEM_TRANS_ATTR_GROUP';
SELECT Nvl(Max(INPUT_ID),0) + 1 into p_index
FROM EGO_PUB_WS_INPUT_IDENTIFIERS
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,'PUBLISH_TA_ID',2,NULL, l_ta_attr_id_tab(i), 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,'PUBLISH_TA_ID',1,NULL, NULL, l_ta_attr_id_tab(i), SYSDATE, G_CURRENT_USER_ID);
SELECT Nvl(extractValue(xmlcontent, '/itemQueryParameters/InventoryItemId'), -1)
INTO l_inv_id
FROM EGO_PUB_WS_PARAMS
WHERE session_id = p_session_id;
SELECT extractValue(segments, '/InventoryItemName/Segment1'), extractValue(segments, '/InventoryItemName/Segment2') ,
extractValue(segments, '/InventoryItemName/Segment3'), extractValue(segments, '/InventoryItemName/Segment4') ,
extractValue(segments, '/InventoryItemName/Segment5'), extractValue(segments, '/InventoryItemName/Segment6') ,
extractValue(segments, '/InventoryItemName/Segment7'), extractValue(segments, '/InventoryItemName/Segment8') ,
extractValue(segments, '/InventoryItemName/Segment9'), extractValue(segments, '/InventoryItemName/Segment10') ,
extractValue(segments, '/InventoryItemName/Segment11'), extractValue(segments, '/InventoryItemName/Segment12') ,
extractValue(segments, '/InventoryItemName/Segment13'), extractValue(segments, '/InventoryItemName/Segment14') ,
extractValue(segments, '/InventoryItemName/Segment15'), extractValue(segments, '/InventoryItemName/Segment16') ,
extractValue(segments, '/InventoryItemName/Segment17'), extractValue(segments, '/InventoryItemName/Segment18') ,
extractValue(segments, '/InventoryItemName/Segment19'), extractValue(segments, '/InventoryItemName/Segment20')
INTO l_segment_1, l_segment_2 ,
l_segment_3, l_segment_4 ,
l_segment_5, l_segment_6 ,
l_segment_7, l_segment_8 ,
l_segment_9, l_segment_10 ,
l_segment_11, l_segment_12 ,
l_segment_13, l_segment_14 ,
l_segment_15, l_segment_16 ,
l_segment_17, l_segment_18 ,
l_segment_19, l_segment_20
FROM (SELECT Value(itemName) segments
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(extract(i.xmlcontent, '/itemQueryParameters/InventoryItemName'))) itemName
WHERE session_id = p_session_id
);
SELECT existsNode(xmlcontent, '/itemQueryParameters/ItemsList')
INTO l_exists_items_list
FROM EGO_PUB_WS_PARAMS
WHERE session_id = p_session_id;
SELECT Nvl(extractValue(xmlcontent, '/itemQueryParameters/OrganizationId'), -1)
INTO l_org_id
FROM EGO_PUB_WS_PARAMS
WHERE session_id = p_session_id;
SELECT extractValue(xmlcontent, '/itemQueryParameters/OrganizationCode')
INTO l_org_code
FROM EGO_PUB_WS_PARAMS
WHERE session_id = p_session_id;
SELECT Nvl(extractValue(xmlcontent, '/itemQueryParameters/RevisionId'), -1)
INTO l_rev_id
FROM EGO_PUB_WS_PARAMS
WHERE session_id = p_session_id;
SELECT extractValue(xmlcontent, '/itemQueryParameters/Revision')
INTO l_revision
FROM EGO_PUB_WS_PARAMS
WHERE session_id = p_session_id;
SELECT To_Date(extractValue(xmlcontent, '/itemQueryParameters/RevisionDate'), 'YYYY.MM.DD HH24:MI:SS')
INTO l_rev_date
FROM EGO_PUB_WS_PARAMS
WHERE session_id = p_session_id;
INSERT INTO ego_odi_ws_entities ( session_id,odi_session_id,entity_type,pk1_value,pk2_value,pk3_value, pk4_value, pk5_value, SEQUENCE_NUMBER)
VALUES (p_session_id,p_odi_session_id,'ITEM',l_inv_item_id,l_organization_id,l_revision_id, 1, To_Char(l_revision_date,'YYYY.MM.DD HH24:MI:SS'), 1);
SELECT existsNode(xmlcontent, '/itemQueryParameters/ItemsList')
INTO l_exists_items_list
FROM EGO_PUB_WS_PARAMS
WHERE session_id = p_session_id;
SELECT Nvl(extractValue(item_id, '/InventoryItemId'),-1)
BULK COLLECT INTO l_item_id_tab
FROM (SELECT Value(itemId) item_id
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/itemQueryParameters/ItemsList/InventoryItemId') )) itemId
WHERE session_id=p_session_id
);
SELECT extractValue(segments, '/InventoryItemName/Segment1'), extractValue(segments, '/InventoryItemName/Segment2') ,
extractValue(segments, '/InventoryItemName/Segment3'), extractValue(segments, '/InventoryItemName/Segment4') ,
extractValue(segments, '/InventoryItemName/Segment5'), extractValue(segments, '/InventoryItemName/Segment6') ,
extractValue(segments, '/InventoryItemName/Segment7'), extractValue(segments, '/InventoryItemName/Segment8') ,
extractValue(segments, '/InventoryItemName/Segment9'), extractValue(segments, '/InventoryItemName/Segment10') ,
extractValue(segments, '/InventoryItemName/Segment11'), extractValue(segments, '/InventoryItemName/Segment12') ,
extractValue(segments, '/InventoryItemName/Segment13'), extractValue(segments, '/InventoryItemName/Segment14') ,
extractValue(segments, '/InventoryItemName/Segment15'), extractValue(segments, '/InventoryItemName/Segment16') ,
extractValue(segments, '/InventoryItemName/Segment17'), extractValue(segments, '/InventoryItemName/Segment18') ,
extractValue(segments, '/InventoryItemName/Segment19'), extractValue(segments, '/InventoryItemName/Segment20')
BULK COLLECT INTO
l_segment_1_tab, l_segment_2_tab,
l_segment_3_tab, l_segment_4_tab,
l_segment_5_tab, l_segment_6_tab,
l_segment_7_tab, l_segment_8_tab,
l_segment_9_tab, l_segment_10_tab,
l_segment_11_tab, l_segment_12_tab,
l_segment_13_tab, l_segment_14_tab,
l_segment_15_tab, l_segment_16_tab,
l_segment_17_tab, l_segment_18_tab,
l_segment_19_tab, l_segment_20_tab
FROM (SELECT Value(itemName) segments
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(extract(i.xmlcontent, '/itemQueryParameters/ItemsList/InventoryItemName'))) itemName
WHERE session_id = p_session_id
);
SELECT Nvl(extractValue(org_id, '/OrganizationId'), -1)
BULK COLLECT INTO l_org_id_tab
FROM (SELECT Value(orgId) org_id
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/itemQueryParameters/ItemsList/OrganizationId') )) orgId
WHERE session_id=p_session_id
);
SELECT extractValue(org_code, '/OrganizationCode')
BULK COLLECT INTO l_org_code_tab
FROM (SELECT Value(orgCode) org_code
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/itemQueryParameters/ItemsList/OrganizationCode') )) orgCode
WHERE session_id=p_session_id
);
SELECT Nvl(extractValue(rev_id, '/RevisionId'), -1)
BULK COLLECT INTO l_rev_id_tab
FROM (SELECT Value(revId) rev_id
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/itemQueryParameters/ItemsList/RevisionId') )) revId
WHERE session_id=p_session_id
);
SELECT extractValue(rev_lable, '/Revision')
BULK COLLECT INTO l_rev_tab
FROM (SELECT Value(rev) rev_lable
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(
extract(i.xmlcontent, '/itemQueryParameters/ItemsList/Revision') )) rev
WHERE session_id=p_session_id
);
SELECT
Decode(existsNode(items_list, '/ItemsList/InventoryItemId'), 1, Nvl(extractValue(items_list, '/ItemsList/InventoryItemId'), -1), 0, -1),
Decode(existsNode(items_list, '/ItemsList/InventoryItemName/Segment1'), 1, extractValue(items_list, '/ItemsList/InventoryItemName/Segment1'), 0, NULL),
Decode(existsNode(items_list, '/ItemsList/InventoryItemName/Segment2'), 1, extractValue(items_list, '/ItemsList/InventoryItemName/Segment2'), 0, NULL),
Decode(existsNode(items_list, '/ItemsList/InventoryItemName/Segment3'), 1, extractValue(items_list, '/ItemsList/InventoryItemName/Segment3'), 0, NULL),
Decode(existsNode(items_list, '/ItemsList/InventoryItemName/Segment4'), 1, extractValue(items_list, '/ItemsList/InventoryItemName/Segment4'), 0, NULL),
Decode(existsNode(items_list, '/ItemsList/InventoryItemName/Segment5'), 1, extractValue(items_list, '/ItemsList/InventoryItemName/Segment5'), 0, NULL),
Decode(existsNode(items_list, '/ItemsList/InventoryItemName/Segment6'), 1, extractValue(items_list, '/ItemsList/InventoryItemName/Segment6'), 0, NULL),
Decode(existsNode(items_list, '/ItemsList/InventoryItemName/Segment7'), 1, extractValue(items_list, '/ItemsList/InventoryItemName/Segment7'), 0, NULL),
Decode(existsNode(items_list, '/ItemsList/InventoryItemName/Segment8'), 1, extractValue(items_list, '/ItemsList/InventoryItemName/Segment8'), 0, NULL),
Decode(existsNode(items_list, '/ItemsList/InventoryItemName/Segment9'), 1, extractValue(items_list, '/ItemsList/InventoryItemName/Segment9'), 0, NULL),
Decode(existsNode(items_list, '/ItemsList/InventoryItemName/Segment10'), 1, extractValue(items_list, '/ItemsList/InventoryItemName/Segment10'), 0, NULL),
Decode(existsNode(items_list, '/ItemsList/InventoryItemName/Segment11'), 1, extractValue(items_list, '/ItemsList/InventoryItemName/Segment11'), 0, NULL),
Decode(existsNode(items_list, '/ItemsList/InventoryItemName/Segment12'), 1, extractValue(items_list, '/ItemsList/InventoryItemName/Segment12'), 0, NULL),
Decode(existsNode(items_list, '/ItemsList/InventoryItemName/Segment13'), 1, extractValue(items_list, '/ItemsList/InventoryItemName/Segment13'), 0, NULL),
Decode(existsNode(items_list, '/ItemsList/InventoryItemName/Segment14'), 1, extractValue(items_list, '/ItemsList/InventoryItemName/Segment14'), 0, NULL),
Decode(existsNode(items_list, '/ItemsList/InventoryItemName/Segment15'), 1, extractValue(items_list, '/ItemsList/InventoryItemName/Segment15'), 0, NULL),
Decode(existsNode(items_list, '/ItemsList/InventoryItemName/Segment16'), 1, extractValue(items_list, '/ItemsList/InventoryItemName/Segment16'), 0, NULL),
Decode(existsNode(items_list, '/ItemsList/InventoryItemName/Segment17'), 1, extractValue(items_list, '/ItemsList/InventoryItemName/Segment17'), 0, NULL),
Decode(existsNode(items_list, '/ItemsList/InventoryItemName/Segment18'), 1, extractValue(items_list, '/ItemsList/InventoryItemName/Segment18'), 0, NULL),
Decode(existsNode(items_list, '/ItemsList/InventoryItemName/Segment19'), 1, extractValue(items_list, '/ItemsList/InventoryItemName/Segment19'), 0, NULL),
Decode(existsNode(items_list, '/ItemsList/InventoryItemName/Segment20'), 1, extractValue(items_list, '/ItemsList/InventoryItemName/Segment20'), 0, NULL),
Decode(existsNode(items_list, '/ItemsList/OrganizationId'), 1, Nvl(extractValue(items_list, '/ItemsList/OrganizationId'), -1), 0, -1),
Decode(existsNode(items_list, '/ItemsList/OrganizationCode'), 1, extractValue(items_list, '/ItemsList/OrganizationCode'), 0, NULL),
Decode(existsNode(items_list, '/ItemsList/RevisionId'), 1, Nvl(extractValue(items_list, '/ItemsList/RevisionId'), -1), 0, -1),
Decode(existsNode(items_list, '/ItemsList/Revision'), 1, extractValue(items_list, '/ItemsList/Revision'), 0, NULL)
BULK COLLECT INTO l_item_id_tab,
l_segment_1_tab, l_segment_2_tab,
l_segment_3_tab, l_segment_4_tab,
l_segment_5_tab, l_segment_6_tab,
l_segment_7_tab, l_segment_8_tab,
l_segment_9_tab, l_segment_10_tab,
l_segment_11_tab, l_segment_12_tab,
l_segment_13_tab, l_segment_14_tab,
l_segment_15_tab, l_segment_16_tab,
l_segment_17_tab, l_segment_18_tab,
l_segment_19_tab, l_segment_20_tab,
l_org_id_tab,
l_org_code_tab,
l_rev_id_tab,
l_rev_tab
FROM ( SELECT Value(itemsList) items_list
FROM EGO_PUB_WS_PARAMS i,
TABLE(XMLSequence(extract(i.xmlcontent, '/itemQueryParameters/ItemsList') )) itemsList
WHERE session_id = p_session_id
);
SELECT To_Date(extractValue(xmlcontent, '/itemQueryParameters/RevisionDate'),'YYYY.MM.DD HH24:MI:SS')
INTO l_rev_date
FROM EGO_PUB_WS_PARAMS
WHERE session_id = p_session_id;
INSERT INTO ego_odi_ws_entities ( session_id,odi_session_id,entity_type,pk1_value,pk2_value,pk3_value, pk4_value, pk5_value, SEQUENCE_NUMBER)
VALUES (p_session_id,p_odi_session_id,'ITEM',l_inv_item_id,l_organization_id,l_revision_id, i, To_Char(l_revision_date,'YYYY.MM.DD HH24:MI:SS') , i);
SELECT To_Date(extractValue(xmlcontent, '/itemQueryParameters/RevisionDate'),'YYYY.MM.DD HH24:MI:SS')
INTO l_rev_date
FROM EGO_PUB_WS_PARAMS
WHERE session_id = p_session_id;
SELECT To_Date(extractValue(xmlcontent, '/itemQueryParameters/RevisionDate'),'YYYY.MM.DD HH24:MI:SS')
INTO l_rev_date
FROM EGO_PUB_WS_PARAMS
WHERE session_id = p_session_id;
select inventory_item_id
INTO p_inv_item_id
from mtl_system_items_kfv
WHERE organization_id = p_org_id
AND inventory_item_id = p_inv_id;
select inventory_item_id
INTO p_inv_item_id
from mtl_system_items_kfv
WHERE organization_id = p_org_id
AND Nvl(segment1, 0) = Nvl(p_segment1, 0)
AND Nvl(segment2, 0) = Nvl(p_segment2, 0)
AND Nvl(segment3, 0) = Nvl(p_segment3, 0)
AND Nvl(segment4, 0) = Nvl(p_segment4, 0)
AND Nvl(segment5, 0) = Nvl(p_segment5, 0)
AND Nvl(segment6, 0) = Nvl(p_segment6, 0)
AND Nvl(segment7, 0) = Nvl(p_segment7, 0)
AND Nvl(segment8, 0) = Nvl(p_segment8, 0)
AND Nvl(segment9, 0) = Nvl(p_segment9, 0)
AND Nvl(segment10, 0) = Nvl(p_segment10, 0)
AND Nvl(segment11, 0) = Nvl(p_segment11, 0)
AND Nvl(segment12, 0) = Nvl(p_segment12, 0)
AND Nvl(segment13, 0) = Nvl(p_segment13, 0)
AND Nvl(segment14, 0) = Nvl(p_segment14, 0)
AND Nvl(segment15, 0) = Nvl(p_segment15, 0)
AND Nvl(segment16, 0) = Nvl(p_segment16, 0)
AND Nvl(segment17, 0) = Nvl(p_segment17, 0)
AND Nvl(segment18, 0) = Nvl(p_segment18, 0)
AND Nvl(segment19, 0) = Nvl(p_segment19, 0)
AND Nvl(segment20, 0) = Nvl(p_segment20, 0) ;
select organization_id
INTO p_organization_id
from mtl_parameters
WHERE organization_id = p_org_id;
select organization_id
INTO p_organization_id
from mtl_parameters
WHERE organization_code = p_org_code;
SELECT *
INTO p_revision_id, v_effectivity_date, v_end_date, v_impl_date
FROM
(SELECT revision_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 organization_id = p_org_id
AND inventory_item_id = p_inv_id
AND effectivity_date < p_rev_date
AND implementation_date IS NOT NULL
ORDER BY effectivity_date DESC) list
WHERE ROWNUM = 1;
SELECT revision_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
INTO p_revision_id, v_effectivity_date, v_end_date, v_impl_date
from mtl_item_revisions_b a
WHERE organization_id = p_org_id
AND inventory_item_id = p_inv_id
AND revision_id = p_rev_id;
SELECT revision_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
INTO p_revision_id, v_effectivity_date, v_end_date, v_impl_date
from mtl_item_revisions_b a
WHERE organization_id = p_org_id
AND inventory_item_id = p_inv_id
AND revision = p_revision;
SELECT *
INTO p_revision_id, v_effectivity_date, v_end_date, v_impl_date
FROM
(SELECT revision_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 organization_id = p_org_id
AND inventory_item_id = p_inv_id
AND effectivity_date < SYSDATE
AND implementation_date IS NOT NULL
ORDER BY effectivity_date DESC) list
WHERE ROWNUM = 1;
SELECT SYSDATE INTO v_current_date from dual;
select 1 INTO v_count
from
bom_alternate_designators
WHERE organization_id = p_org_id
AND ALTERNATE_DESIGNATOR_CODE = p_structure_name;
SELECT Nvl(Max(INPUT_ID),0) + 1 into p_index
FROM EGO_PUB_WS_INPUT_IDENTIFIERS
WHERE session_id = p_session_id;
SELECT PARAM_VALUE into v_org_code
FROM EGO_PUB_WS_INPUT_IDENTIFIERS
WHERE SESSION_ID = p_session_id
AND INPUT_ID = p_input_id
AND PARAM_NAME = 'OrganizationCode';
l_dynamic_update_sql VARCHAR2(32767);
select char_value
into l_mode
from EGO_PUB_WS_CONFIG
where parameter_name = 'INVOCATION_MODE'
and session_id = p_session_id;
SELECT party_name INTO l_user_name
FROM EGO_USER_V WHERE USER_ID = FND_GLOBAL.USER_ID;
select Numeric_Value
into l_batch_id
from EGO_PUB_WS_CONFIG
where parameter_name = 'BATCH_ID'
and session_id = p_session_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';
l_dynamic_sql := ' select distinct PK4_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 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, PK2_VALUE, PK3_VALUE) in (select pk1_value, pk2_value, pk3_value
from EGO_ODI_WS_ENTITIES
where session_id = p_session_id
and SEQUENCE_NUMBER = l_seq_number);
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 := ' update EGO_ODI_WS_ENTITIES i ' ||
' set REF1_VALUE = ''N'' ' ||
' where i.session_id = :1 ' ||
' AND nvl(i.REF1_VALUE, ''Y'') = ''Y'' ' ||
' AND NOT ' || l_sec_predicate;
EXECUTE IMMEDIATE l_dynamic_update_sql
USING IN p_session_id;
l_dynamic_sql := ' select pk1_value, pk2_value, pk3_value, PK4_VALUE ' ||
' from EGO_ODI_WS_ENTITIES i ' ||
' where i.session_id = :1 ' ||
' AND nvl(i.REF1_VALUE, ''Y'') = ''N'' ' ||
' AND SEQUENCE_NUMBER IS NULL ' ||
' AND ' || l_sec_predicate;
select CONCATENATED_SEGMENTS
into l_component_name
from mtl_system_items_b_kfv
WHERE inventory_item_id = l_item_id
AND ORGANIZATION_ID = l_org_id;
l_dynamic_sql := ' select distinct PK4_VALUE ' ||
' from EGO_ODI_WS_ENTITIES i ' ||
' where i.session_id = :1 ' ||
' AND nvl(i.REF1_VALUE, ''Y'') = ''N'' ' ||
' AND SEQUENCE_NUMBER IS NULL ' ||
' AND NOT ' || l_sec_predicate;
for i in (SELECT PK1_VALUE, PK2_VALUE, PK3_VALUE, PK4_VALUE
FROM EGO_ODI_WS_ENTITIES
WHERE SESSION_ID = P_SESSION_ID
AND NVL(REF1_VALUE, 'Y') = 'N'
AND SEQUENCE_NUMBER IS NULL)
loop
select CONCATENATED_SEGMENTS
into l_component_name
from mtl_system_items_b_kfv
WHERE inventory_item_id = i.PK1_VALUE
AND ORGANIZATION_ID = i.PK2_VALUE;
UPDATE EGO_ODI_WS_ENTITIES ent1
SET REF1_VALUE = 'N'
WHERE Nvl(REF1_VALUE, 'Y') <> 'N'
AND session_id = p_session_id
AND PK4_VALUE IN (SELECT PK4_VALUE FROM EGO_ODI_WS_ENTITIES ent2
WHERE Nvl(REF1_VALUE, 'Y') = 'N'
AND SEQUENCE_NUMBER IS NULL
AND ent1.session_id = ent2.session_id
);
select char_value
into l_mode
from EGO_PUB_WS_CONFIG
where parameter_name = 'INVOCATION_MODE'
and session_id = p_session_id;
select to_number(extractValue(xmlcontent, '/itemQueryParameters/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 fnd_user_name, responsibility_name, responsibility_appl_name, security_group_name
into l_fnd_user_name, l_responsibility_name, l_responsibility_appl_name, 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)
VALUES (p_session_id,
p_odi_session_id,
'FND_USER_NAME',
2,
l_fnd_user_name,
sysdate,
0);
INSERT INTO EGO_PUB_WS_CONFIG (session_id,
odi_session_id,
Parameter_Name,
Data_Type,
Char_value,
creation_date,
created_by)
VALUES (p_session_id,
p_odi_session_id,
'RESPONSIBILITY_NAME',
2,
l_responsibility_name,
sysdate,
0);
INSERT INTO EGO_PUB_WS_CONFIG (session_id,
odi_session_id,
Parameter_Name,
Data_Type,
Char_value,
creation_date,
created_by)
VALUES (p_session_id,
p_odi_session_id,
'RESPONSIBILITY_APP_NAME',
2,
l_responsibility_appl_name,
sysdate,
0);
INSERT INTO EGO_PUB_WS_CONFIG (session_id,
odi_session_id,
Parameter_Name,
Data_Type,
Char_value,
creation_date,
created_by)
VALUES (p_session_id,
p_odi_session_id,
'SECURITY_GROUP_NAME',
2,
l_security_group_name,
sysdate,
0);
select user_id
into l_user_id
from fnd_user
where user_name = l_fnd_user_name;
Select responsibility_id
into l_responsibility_id
from FND_RESPONSIBILITY
where responsibility_key = l_responsibility_name;
select application_id
into l_application_id
from FND_APPLICATION
where application_short_name = l_responsibility_appl_name;
DELETE EGO_PUB_WS_INPUT_IDENTIFIERS
WHERE session_id = p_session_id;
DELETE EGO_PUB_WS_ERRORS
WHERE session_id = p_session_id;
DELETE ego_odi_ws_entities
WHERE session_id = p_session_id;