[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT version_seq_id INTO l_cur_icc_ver_id
FROM
( SELECT item_catalog_group_id,version_seq_id,MAX(start_active_date) start_active_date
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE NVL(end_active_date, sysdate) >= SYSDATE
AND start_active_date <= SYSDATE
AND ITEM_CATALOG_GROUP_ID = p_icc_id
AND version_seq_id > 0
GROUP BY item_catalog_group_id,version_seq_id
HAVING MAX(start_active_date)<=SYSDATE
);
/* SOAPUI inserts '?' for null values and
in create_entities_icc procedure we read null or non-existant tag as '-1' */
IF ((p_icc_id = '?') or (p_icc_id = '-1')) THEN
p_icc_id := NULL;
SELECT catalog_concat_segs
INTO p_icc_name
FROM mtl_item_catalog_groups_v
WHERE item_catalog_group_id = To_Number(p_icc_id);
SELECT To_Char(item_catalog_group_id)
INTO p_icc_id
FROM mtl_item_catalog_groups_v
WHERE catalog_concat_segs = p_icc_name;
SELECT version_seq_id, end_active_date
INTO l_valid_icc_ver_id, l_end_date
FROM ego_mtl_catalog_grp_vers_b
WHERE item_catalog_group_id = To_Number(p_icc_id)
AND version_seq_id = To_Number(p_icc_ver_id)
/*AND start_active_date <= SYSDATE commented this filter since we should be able to sync future eff versions*/
AND version_seq_id > 0;
PROCEDURE Check_Duplicate_And_Insert(
p_session_id NUMBER,
p_icc_id NUMBER,
p_icc_ver_id NUMBER,
p_icc_name VARCHAR2,
p_icc_id_tab IN OUT NOCOPY DBMS_SQL.number_table,
p_icc_ver_id_tab IN OUT NOCOPY DBMS_SQL.number_table,
p_icc_name_tab IN OUT NOCOPY DBMS_SQL.varchar2_table,
p_show_error_flag IN VARCHAR2,
p_user_entered_flag IN VARCHAR2
)
IS
l_is_duplicate BOOLEAN := FALSE;
L_PROC_NAME := ' Check_Duplicate_And_Insert=> ';
UPDATE ego_pub_ws_entities
SET ref10_value = G_SHOW_ERROR_FLAG_YES
WHERE (session_id = p_session_id
AND pk1_value = p_icc_id
AND (p_icc_ver_id IS NULL
OR (p_icc_ver_id IS NOT NULL AND pk2_value = p_icc_ver_id))
);
/*If not duplicate then, insert the record into entities table.
Note that parent_sequence_id column is inserted as NULL.
This is because we do not maintain info about parent-child ICC hierarchy info
in this ego_pub_ws_entities table. Every ICC (though it was derived as a parent/child ICC
in create_entities_icc) is treated as an independent ICC.*/
IF (NOT l_is_duplicate)
THEN
p_icc_id_tab (num_of_records + 1) := p_icc_id;
INSERT INTO EGO_PUB_WS_ENTITIES(SESSION_ID,
ODI_SESSION_ID,
SEQUENCE_ID,
PARENT_SEQUENCE_ID,
ENTITY_TYPE,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE,
REF1_VALUE,
REF2_VALUE,
REF3_VALUE,
REF4_VALUE,
REF5_VALUE,
REF6_VALUE,
REF7_VALUE,
REF8_VALUE,
REF9_VALUE,
REF10_VALUE,
CREATION_DATE,
CREATED_BY,
USER_ENTERED)
VALUES( p_session_id,
p_session_id,
EGO_PUB_WS_ENTITIES_S.nextval,
NULL,
G_ENTITY_TYPE_ICC,
p_icc_id,
p_icc_ver_id,
NULL,
NULL,
NULL,
p_icc_name,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
p_show_error_flag,
SYSDATE,
G_CURRENT_USER_ID,
p_user_entered_flag
);
END check_duplicate_and_insert;
SELECT WEB_SERVICE_NAME
INTO l_web_service_name
FROM EGO_PUB_WS_PARAMS
WHERE SESSION_ID = p_session_id;
The Batch UI does not insert this param. The ServiceUtil inserts it into ego_pub_ws_config.
Hence for fetching the BATCH_ID, we are passing the mode as NULL.*/
l_batch_id := EGO_PUB_WS_UTIL.Get_Numeric_Param_Value(p_session_id, 'BATCH_ID', NULL, NULL);
/* The below insert takes care of all params that have to be defaulted to TRUE */
IF l_param_names(position) NOT IN ('PARENTICCS','CHILDICCS','SYNC', 'TRIGGER_IMPORT') THEN
INSERT INTO EGO_PUB_WS_CONFIG ( session_id,
odi_session_id,
Parameter_Name,
Data_Type,
Char_value,
creation_date,
created_by,
web_service_name)
VALUES (p_session_id,
p_session_id,
l_param_names(position),
2,
G_CHAR_TRUE,
sysdate,
G_CURRENT_USER_ID,
l_web_service_name);
/* The below insert statements take care of the params that have to be taken from EGO_PUB_BAT_PARAMS_B table*/
/* Batch Mode param PublishParent will be inserted into ego_pub_ws_config as PARENTICCS */
l_param_parent_hier := EGO_PUB_WS_UTIL.Get_Char_Param_Value(p_session_id => p_session_id,
p_param_name => 'PublishParent',
p_batch_id => l_batch_id,
p_mode => 'BATCH'
);
INSERT INTO EGO_PUB_WS_CONFIG ( session_id,
odi_session_id,
Parameter_Name,
Data_Type,
Char_value,
creation_date,
created_by,
web_service_name)
VALUES (p_session_id,
p_session_id,
'PARENTICCS',
2,
upper(l_param_parent_hier), -- bug 12377269
sysdate,
G_CURRENT_USER_ID,
l_web_service_name);
/* Batch Mode param PublishChild will be inserted into ego_pub_ws_config as CHILDICCS */
l_param_child_hier := EGO_PUB_WS_UTIL.Get_Char_Param_Value ( p_session_id => p_session_id,
p_param_name => 'PublishChild',
p_batch_id => l_batch_id,
p_mode => 'BATCH'
);
INSERT INTO EGO_PUB_WS_CONFIG ( session_id,
odi_session_id,
Parameter_Name,
Data_Type,
Char_value,
creation_date,
created_by,
web_service_name)
VALUES (p_session_id,
p_session_id,
'CHILDICCS',
2,
upper(l_param_child_hier),-- bug 12377269
sysdate,
G_CURRENT_USER_ID,
l_web_service_name);
/* Batch Mode param Launch Sync Program will be inserted into ego_pub_ws_config as TRIGGER_IMPORT */
l_trigger_import := EGO_PUB_WS_UTIL.Get_Char_Param_Value(p_session_id => p_session_id,
p_param_name => EGO_PUB_WS_UTIL.G_TRIGGER_IMPORT_PARAM,
p_batch_id => l_batch_id,
p_mode => 'BATCH'
);
INSERT INTO EGO_PUB_WS_CONFIG ( session_id,
odi_session_id,
Parameter_Name,
Data_Type,
Char_value,
creation_date,
created_by,
web_service_name)
VALUES (p_session_id,
p_session_id,
'TRIGGER_IMPORT',
2,
NVL(l_trigger_import,'N'),
sysdate,
G_CURRENT_USER_ID,
l_web_service_name);
/* Batch Mode param BATCH_PROCESS_TYPE=PUBLISH/SYNC will be inserted into ego_pub_ws_config as SYNC=Y/N */
l_publish_sync := EGO_PUB_WS_UTIL.Get_Char_Param_Value ( p_session_id => p_session_id,
p_param_name => EGO_PUB_WS_UTIL.G_SYNC_PARAM,
p_batch_id => l_batch_id,
p_mode => 'BATCH'
);
INSERT INTO EGO_PUB_WS_CONFIG ( session_id,
odi_session_id,
Parameter_Name,
Data_Type,
Char_value,
creation_date,
created_by,
web_service_name)
VALUES (p_session_id,
p_session_id,
'SYNC',
2,
l_publish_sync,
sysdate,
G_CURRENT_USER_ID,
l_web_service_name);
/* In the below insert stmt, we are hardcoding data_type as 2 and providing the char value,
it implies that we are assuming all config params are of string type.
All config params except TRIGGER_IMPORT, SYNC take TRUE/FALSE (as strings not boolean).
TRIGGER_IMPORT alone takes it as Y/N. SYNC takes SYNC/PUBLISH */
INSERT INTO EGO_PUB_WS_CONFIG ( session_id,
odi_session_id,
Parameter_Name,
Data_Type,
Char_value,
creation_date,
created_by,
web_service_name)
VALUES (p_session_id,
p_session_id,
l_param_names(position),
2,
l_config_option,
sysdate,
G_CURRENT_USER_ID,
l_web_service_name);
INSERT INTO EGO_PUB_WS_CONFIG ( session_id,
odi_session_id,
Parameter_Name,
Data_Type,
Char_value,
creation_date,
created_by,
web_service_name)
VALUES (p_session_id,
p_session_id,
'CHILD_VALUESETS',
2,
G_CHAR_YES,
sysdate,
G_CURRENT_USER_ID,
l_web_service_name);
--Inserts language options in Config table
--debug(L_PROC_NAME||'calling EGO_PUB_WS_UTIL.Config_Languages procedure');
SELECT NVL(vers.end_active_date,SYSDATE)
FROM ego_mtl_catalog_grp_vers_b vers
WHERE item_catalog_group_id = p_item_catalog_group_id
AND version_seq_id = NVL(p_version_seq_id,0);
SELECT hier.item_catalog_group_id,
iccb.version_seq_id,
cat.catalog_concat_segs
FROM mtl_item_catalog_groups_v cat,
(SELECT item_catalog_group_id,
LEVEL lev
FROM mtl_item_catalog_groups_b
START WITH item_catalog_group_id = p_item_catalog_group_id
CONNECT BY PRIOR item_catalog_group_id = parent_catalog_group_id
) hier,
ego_mtl_catalog_grp_vers_b iccb
WHERE cat.item_catalog_group_id = hier.item_catalog_group_id
AND hier.lev > 1
AND iccb.item_catalog_group_id = hier.item_catalog_group_id
AND iccb.version_seq_id >= p_versions_from
AND iccb.start_active_date <= p_active_date
ORDER BY hier.item_catalog_group_id,
iccb.start_active_date DESC;
SELECT hier.item_catalog_group_id,
null version_seq_id,
cat.catalog_concat_segs
FROM mtl_item_catalog_groups_v cat,
(SELECT item_catalog_group_id,
LEVEL lev
FROM mtl_item_catalog_groups_b
START WITH item_catalog_group_id = p_item_catalog_group_id
CONNECT BY PRIOR item_catalog_group_id = parent_catalog_group_id
) hier
WHERE cat.item_catalog_group_id = hier.item_catalog_group_id
AND hier.lev > 1
ORDER BY hier.item_catalog_group_id;
SELECT hier.item_catalog_group_id,
iccb.version_seq_id,
cat.catalog_concat_segs
FROM mtl_item_catalog_groups_v cat,
(SELECT item_catalog_group_id,
LEVEL lev
FROM mtl_item_catalog_groups_b
START WITH item_catalog_group_id = p_item_catalog_group_id
CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
) hier,
ego_mtl_catalog_grp_vers_b iccb
WHERE cat.item_catalog_group_id = hier.item_catalog_group_id
AND hier.lev > 1
AND iccb.item_catalog_group_id = hier.item_catalog_group_id
AND iccb.version_seq_id >= p_versions_from
AND iccb.start_active_date <= p_active_date
ORDER BY hier.item_catalog_group_id,
iccb.start_active_date DESC;
SELECT hier.item_catalog_group_id,
null version_seq_id,
cat.catalog_concat_segs
FROM mtl_item_catalog_groups_v cat,
(SELECT item_catalog_group_id,
LEVEL lev
FROM mtl_item_catalog_groups_b
START WITH item_catalog_group_id = p_item_catalog_group_id
CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
) hier
WHERE cat.item_catalog_group_id = hier.item_catalog_group_id
AND hier.lev > 1
ORDER BY hier.item_catalog_group_id;
SELECT To_Char(pk1_value) , NULL, To_Char(pk2_value)
BULK COLLECT INTO l_dup_icc_id_tab, l_dup_icc_name_tab, l_dup_icc_ver_id_tab
FROM Ego_Pub_Bat_Ent_Objs_v
WHERE batch_id = l_batch_id
AND user_entered = G_USER_ENTERED_FLAG_YES;
If the ICCId node does not exist or has a null value, insert -1 as value*/
SELECT Decode(existsNode(ICC_Identifier, '/ICCIdentifier/ICCId'), 1, Nvl(extractValue(ICC_Identifier, '/ICCIdentifier/ICCId'), -1), 0, -1)
BULK COLLECT INTO l_dup_icc_id_tab
FROM (SELECT VALUE (ICCIdentifier) ICC_Identifier
FROM ego_pub_ws_params i,
TABLE
(XMLSEQUENCE
(EXTRACT
(i.xmlcontent,
'/ICCQueryParam/ICCIdentifiersList/ICCIdentifier'
)
)
) ICCIdentifier
WHERE session_id = p_session_id);
SELECT EXTRACTVALUE (icc_name, '/ICCName')
BULK COLLECT INTO l_dup_icc_name_tab
FROM (SELECT VALUE (iccname) icc_name
FROM ego_pub_ws_params i,
TABLE
(XMLSEQUENCE
(EXTRACT
(i.xmlcontent,
'/ICCQueryParam/ICCIdentifiersList/ICCIdentifier/ICCName'
)
)
) iccname
WHERE session_id = p_session_id);
If the VersionId node does not exist or has a null value, insert -1 as value*/
SELECT Decode(existsNode(ICC_Identifier, '/ICCIdentifier/VersionSequence'), 1, Nvl(extractValue(ICC_Identifier, '/ICCIdentifier/VersionSequence'), -1), 0, -1)
BULK COLLECT INTO l_dup_icc_ver_id_tab
FROM (SELECT VALUE (ICCIdentifier) ICC_Identifier
FROM ego_pub_ws_params i,
TABLE
(XMLSEQUENCE
(EXTRACT
(i.xmlcontent,
'/ICCQueryParam/ICCIdentifiersList/ICCIdentifier'
)
)
) ICCIdentifier
WHERE session_id = p_session_id);
/* If above is valid data, then check for duplicates and insert the data */
IF (l_is_valid) THEN
--debug(L_PROC_NAME||'inside If condition on l_is_valid, l_dup_icc_ver_id_tab (i): '||l_dup_icc_ver_id_tab (i));
/* If Version is given along with the ICC, insert an additional record with version as null.
This entry is to sync up the ICC details.
Though this record is not user entered, we still are inserting user_entered_flag in ego_pub_ws_entities
as Y, so that this record does not get copied back batch fwk tables.
This record should NOT be seen in batch fwk entity objects table
*/
--debug(L_PROC_NAME||' calling check_duplicate_and_insert for an additional ICC record with version as null');
check_duplicate_and_insert( p_session_id => p_session_id,
p_icc_id => To_Number(l_dup_icc_id_tab (i)),
p_icc_ver_id => NULL,
p_icc_name => l_dup_icc_name_tab (i),
p_icc_id_tab => l_icc_id_tab,
p_icc_ver_id_tab => l_icc_ver_id_tab,
p_icc_name_tab => l_icc_name_tab,
p_show_error_flag => G_SHOW_ERROR_FLAG_NO,
p_user_entered_flag => G_USER_ENTERED_FLAG_YES
);
The ICC record was inserted in the above call
(b) Non Versioned ICC - just the ICC record, here l_dup_icc_ver_id_tab(i) will be NULL
These records are always user entered.
*/
--debug(L_PROC_NAME||' calling check_duplicate_and_insert for the user entered record');
check_duplicate_and_insert( p_session_id => p_session_id,
p_icc_id => To_Number(l_dup_icc_id_tab (i)),
p_icc_ver_id => To_Number(l_dup_icc_ver_id_tab(i)),
p_icc_name => l_dup_icc_name_tab (i),
p_icc_id_tab => l_icc_id_tab,
p_icc_ver_id_tab => l_icc_ver_id_tab,
p_icc_name_tab => l_icc_name_tab,
p_show_error_flag => G_SHOW_ERROR_FLAG_YES,
p_user_entered_flag => G_USER_ENTERED_FLAG_YES
);
and insert into ego_pub_ws_entities table */
/* In Create_Params_ICC() procedure, we have dumped config params in both modes (batch,list) into EGO_PUB_WS_CONFIG table.
So, now while fetching the PARENTICCS,CHILDICCS params, we can just get from this table without reference to any mode*/
l_param_parent_hier := EGO_PUB_WS_UTIL.Get_Char_Param_Value(p_session_id, 'PARENTICCS', NULL, NULL);
show_error_flag for every icc record. Pass it to check_duplicate_and_insert procedure and populate when the other
arrays are getting populated. Then p_show_error_flag(counter_icc) can be used to check for dummy icc header records */
IF ( ( counter_icc = l_icc_id_tab.Count )
OR (l_icc_ver_id_tab(counter_icc) IS NOT NULL )
OR ( (l_icc_ver_id_tab(counter_icc) IS NULL
) AND (l_icc_id_tab(counter_icc)<> l_icc_id_tab(counter_icc+1)
)
)
)THEN
--
-- Bug 12377269. Get parent and children ICC of given ICC
-- sreharih. Tue Apr 19 18:26:45 PDT 2011
--
get_hierarchical_iccs(
p_item_catalog_group_id => l_icc_id_tab(counter_icc),
p_version_seq_id => l_icc_ver_id_tab(counter_icc),
p_get_children => l_param_child_hier,
p_get_parents => l_param_parent_hier,
p_only_latest => 'TRUE',
x_hierarchical_iccs => l_heirarchical_iccs);
/* For every versioned ICC , insert an additional record with version as null.
This entry is to sync up the ICC details.
show_error flag will be No for these records.
Though this record is not user entered, we still are inserting user_entered_flag in ego_pub_ws_entities
as Y, so that this record does not get copied back batch fwk tables.
This record should NOT be seen in batch fwk entity objects table
*/
IF ( l_heirarchical_iccs(counter_hier).version_seq_id IS NOT NULL)
THEN
--debug(L_PROC_NAME||' Parent-child ICC Explosion, calling check_duplicate_and_insert for an additional ICC record with version as null');
check_duplicate_and_insert(p_session_id => p_session_id,
p_icc_id => l_heirarchical_iccs(counter_hier).item_catalog_group_id,
p_icc_ver_id => NULL,
p_icc_name => l_heirarchical_iccs(counter_hier).catalog_concat_segs,
p_icc_id_tab => l_icc_id_tab,
p_icc_ver_id_tab => l_icc_ver_id_tab,
p_icc_name_tab => l_icc_name_tab,
p_show_error_flag => G_SHOW_ERROR_FLAG_NO,
p_user_entered_flag =>G_USER_ENTERED_FLAG_YES
);
/* Below call inserts ICC version record.
show_error flag will be Y for these records.
user_entered flag is N, for these records since they are derived ICCs
and have to be copied back to batch fwk entity objects table.
This record should be seen in batch fwk entity objects table
*/
--debug(L_PROC_NAME||' Parent-child ICC Explosion, calling check_duplicate_and_insert for user entered record');
check_duplicate_and_insert(p_session_id => p_session_id,
p_icc_id => l_heirarchical_iccs(counter_hier).item_catalog_group_id,
p_icc_ver_id => l_heirarchical_iccs(counter_hier).version_seq_id,
p_icc_name => l_heirarchical_iccs(counter_hier).catalog_concat_segs,
p_icc_id_tab => l_icc_id_tab,
p_icc_ver_id_tab => l_icc_ver_id_tab,
p_icc_name_tab => l_icc_name_tab,
p_show_error_flag => G_SHOW_ERROR_FLAG_YES,
p_user_entered_flag =>G_USER_ENTERED_FLAG_NO
);
SELECT epwe.pk1_value , epwe.ref1_value ,epwe.sequence_id
,eoaab.attr_group_id, efdfce.descriptive_flex_context_code
FROM ego_obj_ag_assocs_b eoaab,
fnd_objects fo,
ego_fnd_dsc_flx_ctx_ext efdfce,
ego_pub_ws_entities epwe
WHERE eoaab.object_id=fo.object_id
AND fo.obj_name ='EGO_ITEM'
AND eoaab.attr_group_id=efdfce.attr_group_id
AND efdfce.descriptive_flexfield_name = 'EGO_ITEMMGMT_GROUP'
AND efdfce.application_id = G_APPLICATION_ID_EGO
AND eoaab.classification_code = epwe.pk1_value
AND epwe.SESSION_ID = p_session_id
AND epwe.entity_type = G_ENTITY_TYPE_ICC
AND epwe.pk2_value IS NULL /*PK2_VALUE will not be null for icc ver records*/
AND epwe.parent_sequence_id IS NULL
ORDER BY eoaab.attr_group_id, epwe.sequence_id;
SELECT EMCGVB.ITEM_CATALOG_GROUP_ID,EMCGVB.VERSION_SEQ_ID AS icc_ver_seq_id,
EMCGVB.START_ACTIVE_DATE AS icc_strt_ac_date,EMCGVB.END_ACTIVE_DATE AS icc_end_date
,ETAVB.ASSOCIATION_ID,ETAVB.ATTR_ID,ETAVB.ATTR_DISPLAY_NAME,ETAVB.SEQUENCE
, FFVS.FLEX_VALUE_SET_ID, FFVS.FLEX_VALUE_SET_NAME
, EFVVB.VERSION_SEQ_ID AS vs_seq_id, EFVVB.START_ACTIVE_DATE AS vs_start_date, EFVVB.END_ACTIVE_DATE AS vs_end_date
, EPWE.PK1_VALUE, EPWE.PK2_VALUE
FROM EGO_MTL_CATALOG_GRP_VERS_B EMCGVB,
EGO_TRANS_ATTR_VERS_B ETAVB
, FND_FLEX_VALUE_SETS FFVS,
EGO_FLEX_VALUESET_VERSION_B EFVVB
, EGO_PUB_WS_ENTITIES EPWE
WHERE EMCGVB.ITEM_CATALOG_GROUP_ID=ETAVB.ITEM_CATALOG_GROUP_ID
AND EMCGVB.VERSION_SEQ_ID=ETAVB.ICC_VERSION_NUMBER
AND ETAVB.VALUE_SET_ID=FFVS.FLEX_VALUE_SET_ID
AND FFVS.VALIDATION_TYPE IN ('X','I')
AND FFVS.FLEX_VALUE_SET_ID = EFVVB.FLEX_VALUE_SET_ID
AND EFVVB.VERSION_SEQ_ID >= 1
AND EFVVB.START_ACTIVE_DATE <= EMCGVB.START_ACTIVE_DATE
AND Nvl(EFVVB.END_ACTIVE_DATE,Nvl(EMCGVB.START_ACTIVE_DATE,SYSDATE)) >=
Nvl(EMCGVB.START_ACTIVE_DATE,SYSDATE)
AND EMCGVB.ITEM_CATALOG_GROUP_ID = EPWE.PK1_VALUE
AND EMCGVB.VERSION_SEQ_ID = EPWE.PK2_VALUE
AND EPWE.ENTITY_TYPE = 'ICC'--G_ENTITY_TYPE_ICC
AND EPWE.PK2_VALUE IS NOT NULL
AND EPWE.SESSION_ID=p_session_id
ORDER BY FFVS.FLEX_VALUE_SET_ID, EFVVB.VERSION_SEQ_ID;
So, insert a AG record and an AG-ICC association record into ego_pub_ws_entities table.*/
-- get next value for the sequence_id
SELECT EGO_PUB_WS_ENTITIES_S.nextval
INTO l_seq_id
FROM dual;
INSERT INTO EGO_PUB_WS_ENTITIES(SESSION_ID,
ODI_SESSION_ID,
SEQUENCE_ID,
PARENT_SEQUENCE_ID,
ENTITY_TYPE,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE,
REF1_VALUE,
REF2_VALUE,
REF3_VALUE,
REF4_VALUE,
REF5_VALUE,
REF6_VALUE,
REF7_VALUE,
REF8_VALUE,
REF9_VALUE,
REF10_VALUE,
CREATION_DATE,
CREATED_BY,
USER_ENTERED)
VALUES( p_session_id,
p_session_id,
l_seq_id,
NULL,
G_ENTITY_TYPE_AG,
rec_derived_ags.attr_group_id,
NULL,
NULL,
NULL,
NULL,
rec_derived_ags.descriptive_flex_context_code,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
sysdate,
G_CURRENT_USER_ID,
G_USER_ENTERED_FLAG_NO
);
INSERT INTO EGO_PUB_WS_ENTITIES(SESSION_ID,
ODI_SESSION_ID,
SEQUENCE_ID,
PARENT_SEQUENCE_ID,
ENTITY_TYPE,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE,
REF1_VALUE,
REF2_VALUE,
REF3_VALUE,
REF4_VALUE,
REF5_VALUE,
REF6_VALUE,
REF7_VALUE,
REF8_VALUE,
REF9_VALUE,
REF10_VALUE,
CREATION_DATE,
CREATED_BY,
USER_ENTERED)
VALUES( p_session_id,
p_session_id,
l_seq_id,
rec_derived_ags.sequence_id,
G_ENTITY_TYPE_AG,
rec_derived_ags.attr_group_id,
NULL,
NULL,
NULL,
NULL,
rec_derived_ags.descriptive_flex_context_code,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
sysdate,
G_CURRENT_USER_ID,
G_USER_ENTERED_FLAG_NO
);
so insert only the AG-ICC assocation record*/
INSERT INTO EGO_PUB_WS_ENTITIES(SESSION_ID,
ODI_SESSION_ID,
SEQUENCE_ID,
PARENT_SEQUENCE_ID,
ENTITY_TYPE,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE,
REF1_VALUE,
REF2_VALUE,
REF3_VALUE,
REF4_VALUE,
REF5_VALUE,
REF6_VALUE,
REF7_VALUE,
REF8_VALUE,
REF9_VALUE,
REF10_VALUE,
CREATION_DATE,
CREATED_BY,
USER_ENTERED)
VALUES( p_session_id,
p_session_id,
l_prev_ag_seq_id,
rec_derived_ags.sequence_id,
G_ENTITY_TYPE_AG,
rec_derived_ags.attr_group_id,
NULL,
NULL,
NULL,
NULL,
rec_derived_ags.descriptive_flex_context_code,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
sysdate,
G_CURRENT_USER_ID,
G_USER_ENTERED_FLAG_NO
);
/*Technically, this l_prev_ag_seq_id is not needed, we can pass l_seq_id in the else part insert stmt too
since the l_seq_id is not incremented for the else part. Using l_prev_ag_seq_id for better readability only*/
--debug(L_PROC_NAME||' End of an iteration for cur_derived_ags cursor, l_prev_ag_id: '||l_prev_ag_id||' ,l_prev_ag_seq_id: '||l_prev_ag_seq_id);
ego_pub_ws_vs.Insert_VS_Entity ( p_session_id => p_session_id
,p_vs_id => trans_attr_vs.FLEX_VALUE_SET_ID
,p_vs_name => trans_attr_vs.FLEX_VALUE_SET_NAME
,p_vs_version => trans_attr_vs.vs_seq_id
,p_user_entered_flag => G_USER_ENTERED_FLAG_NO
);
The select clause has entity_type column to identify the entity being fetched*/
CURSOR c_derived_entities(l_session_id NUMBER) IS
SELECT PK1_VALUE,
PK2_VALUE,
ENTITY_TYPE
FROM EGO_PUB_WS_ENTITIES
WHERE SESSION_ID = l_session_id
AND NVL(USER_ENTERED, 'N') = 'N'
AND PARENT_SEQUENCE_ID IS NULL;
For now, we are not handling exceptions for this select query, since we assume these records should always be present
*/
SELECT entity_id
BULK COLLECT INTO l_entity_type_id_tbl
FROM EGO_PUB_BAT_ENTS_B
WHERE entity_name IN ('Attribute Group','ICC','Value Set')
ORDER BY entity_name;
/*changing the below select statement to use the base table instead of view.
Using an NVL on pk2_value since it can be null for AG and VS but not ICC
kkq- do we really need below select stmt, since
ego_pub_ws_entities will initially have the same records as ego_pub_bat_ent_objs_b (user_entered=Y)
and as we derive new entity records (user_entered=N), we do not insert them into ego_pub_ws_entities
if they are already present. We can safely assume that all records fetched by cursor c_derived_entities
will not be present in ego_pub_bat_ent_objs_b.*/
SELECT COUNT(*) INTO v_count
FROM EGO_PUB_BAT_ENT_OBJS_B
WHERE BATCH_ID = l_batch_id
AND entity_type_id = l_entity_type_id
AND PK1_VALUE = k.pk1_value
AND NVL(PK2_VALUE,-999) = NVL(k.pk2_value,-999);
SELECT COUNT(1)
INTO l_param_rec_count
FROM EGO_PUB_WS_CONFIG
WHERE SESSION_ID = p_session_id
AND PARAMETER_NAME NOT IN('ODI_SESSION_ID', 'SYSTEM_CODE', 'MODE', 'BATCH_ID');
/* When sync'ing to multiple systems, we have to ensure that params get inserted into ego_pub_ws_config table
only once. Once they are inserted for first system, they can be reused for later systems.
Hence checking for l_param_rec_count = 0*/
IF l_param_rec_count = 0 THEN
--Create Input parameters for ODI
--debug(L_PROC_NAME||'Calling Create_Params_ICC procedure');
SELECT COUNT(1)
INTO l_entity_rec_count
FROM EGO_PUB_WS_ENTITIES
WHERE SESSION_ID = p_session_id
AND ENTITY_TYPE = G_ENTITY_TYPE_ICC;
/* When sync'ing to multiple systems, we have to ensure that entities get inserted into ego_pub_ws_entities table
only once. Once they are inserted for first system, they can be reused for later systems.
Hence checking for l_entity_rec_count = 0*/
IF l_entity_rec_count = 0 THEN
--Create ODI Input table containing entities to process
--debug(L_PROC_NAME||'Calling Create_Entities_ICC procedure');
- handling update of EGO_PUBLICATION_BATCH_GT seperately differently than AG,VS using a IF-ELSE condition.
- in ODI pkg EGO_SYNCICCDETAILS , procedure Write_Errors_ToBatFwk() make a call to the procedure in EGO_PUB_WS_UTIL
*/
/* Algorithm of Write_Errors_ToBatFwk in EGO_PUB_WS_UTIL
1) Select value of trigger_import param
2) loop on systems
2.1 check for invalid sys
2.1.1 if valid sys then
2.1.1.1 if trigger_import = N/False then
2.1.1.1.1 loop on entities
insert all records as INPROCESS
end loop on entities
2.1.1.2 else if trigger_import = Y/True then
2.1.1.2.1 loop on entities
insert all records as SUCCESS
end loop on entities
End if on trigger_import
end if on valid sys
2.1.2 if invalid sys then
2.1.2.1 loop on entities
insert all record with FAILURE
end loop on entities
end if on invalid sys
end loop on systems
3) if trigger_import=Y then
3.1 loop on sys
3.1.1 check for invalid sys
3.1.2 if valid sys then
3.1.2.1 loop on input identifiers then
3.1.2.1.1 select count of errors (E,E1 )
3.1.2.1.2 loop on errors then
3.1.2.1.2.1 if error then
3.1.2.1.2.1.1 if err count > 1 and not E1 then
3.1.2.1.2.1.1.1 if ( entity=icc and pk2_value is null ) then
update GT based on pk1_value alone
3.1.2.1.2.1.1.2 else if ( entity <> icc or pk2_value is not null ) then
update GT based on all pk_values
end if;
update GT based on pk1_value alone
3.1.2.1.2.1.2.2 else if ( entity <> icc or pk2_value is not null ) then
update GT based on all pk_values
end if;
update GT based on pk1_value alone
3.1.2.1.2.1.2.2 else if ( entity <> icc or pk2_value is not null ) then
update GT based on all pk_values
end if;
call EGO_PUB_FWK_PK.Update_Pub_Status with bat_ent_stat_rec array;
SELECT CHAR_VALUE
FROM EGO_PUB_WS_CONFIG
WHERE SESSION_ID = l_session_id
AND PARAMETER_NAME = 'SYSTEM_CODE';
SELECT PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE,
USER_ENTERED
FROM EGO_PUB_WS_ENTITIES
WHERE SESSION_ID = l_session_id
AND ( ( NVL(ENTITY_TYPE,'XYZ') <> G_ENTITY_TYPE_ICC )
OR (ENTITY_TYPE=G_ENTITY_TYPE_ICC AND REF10_VALUE=G_CHAR_YES) );
SELECT SESSION_ID,
INPUT_ID,
SYSTEM_CODE,
PARAM_NAME,
PARAM_VALUE,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE
FROM EGO_PUB_WS_INPUT_IDENTIFIERS
WHERE SESSION_ID = l_session_id
AND SYSTEM_CODE = l_system_code;
SELECT SESSION_ID,
INPUT_ID,
SYSTEM_CODE,
ERR_CODE,
ERR_MESSAGE
FROM EGO_PUB_WS_ERRORS
WHERE SESSION_ID = l_session_id
AND INPUT_ID = l_input_id
AND SYSTEM_CODE = l_system_code;
SELECT BATCH_ID,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE,
SYSTEM_CODE,
STATUS,
MESSAGE
FROM EGO_PUBLICATION_BATCH_GT
WHERE BATCH_ID = l_batch_id;
SELECT CHAR_VALUE
INTO l_trigger_import
FROM EGO_PUB_WS_CONFIG
WHERE SESSION_ID = p_session_id
AND PARAMETER_NAME = 'TRIGGER_IMPORT';
SELECT count(1)
INTO l_invld_sys_cnt
FROM EGO_PUB_WS_ERRORS
WHERE SESSION_ID = p_session_id
AND ERR_CODE = 'D'
AND SYSTEM_CODE = l_system_code;
INSERT INTO EGO_PUBLICATION_BATCH_GT
(BATCH_ID,
BATCH_ENTITY_OBJECT_ID,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE,
USER_ENTERED,
SYSTEM_CODE,
STATUS,
MESSAGE,
PROCESS_FLAG,
RETURN_STATUS,
RETURN_ERROR_MESSAGE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES (l_batch_id,
NULL,
ent.PK1_VALUE,
ent.PK2_VALUE,
ent.PK3_VALUE,
ent.PK4_VALUE,
ent.PK5_VALUE,
ent.USER_ENTERED,
l_system_code,
'I',
l_message,
NULL,
NULL,
NULL,
-1,
SYSDATE,
-1,
SYSDATE
);
INSERT INTO EGO_PUBLICATION_BATCH_GT
(BATCH_ID,
BATCH_ENTITY_OBJECT_ID,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE,
USER_ENTERED,
SYSTEM_CODE,
STATUS,
MESSAGE,
PROCESS_FLAG,
RETURN_STATUS,
RETURN_ERROR_MESSAGE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES (l_batch_id,
NULL,
ent.PK1_VALUE,
ent.PK2_VALUE,
ent.PK3_VALUE,
ent.PK4_VALUE,
ent.PK5_VALUE,
ent.USER_ENTERED,
l_system_code,
'S',
NULL,
NULL,
NULL,
NULL,
-1,
SYSDATE,
-1,
SYSDATE
);
-- insert records for invalid system with error message
IF (l_invld_sys_cnt > 0) THEN
--debug(L_PROC_NAME||' if l_invld_sys_cnt > 0 '||l_invld_sys_cnt);
INSERT INTO EGO_PUBLICATION_BATCH_GT
( BATCH_ID,
BATCH_ENTITY_OBJECT_ID,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE,
USER_ENTERED,
SYSTEM_CODE,
STATUS,
MESSAGE,
PROCESS_FLAG,
RETURN_STATUS,
RETURN_ERROR_MESSAGE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES (l_batch_id,
NULL,
ent.PK1_VALUE,
ent.PK2_VALUE,
ent.PK3_VALUE,
ent.PK4_VALUE,
ent.PK5_VALUE,
ent.USER_ENTERED,
l_system_code,
'F',
l_message,
NULL,
NULL,
NULL,
-1,
SYSDATE,
-1,
SYSDATE
);
-- and update records in global temporary table accordingly using PKs.
IF (l_trigger_import = 'Y' OR l_trigger_import = 'TRUE') THEN
--debug(L_PROC_NAME||' entered second l_trigger_import = Y/TRUE ');
SELECT count(1)
INTO l_invld_sys_cnt
FROM EGO_PUB_WS_ERRORS
WHERE SESSION_ID = p_session_id
AND ERR_CODE = 'D'
AND SYSTEM_CODE = l_system_code;
SELECT count(1)
INTO l_error_rec_count
FROM EGO_PUB_WS_ERRORS
WHERE session_id = p_session_id
AND system_code = l_system_code
AND input_id = l_input_id;
Note that in create_entities_icc procedure, for every ICC Ver record, we inserted an
additional ICC alone record for syncing up the non-versioned info like Pages, AGs etc.
Any error marked for this additional ICC record has to be copied to the actual ICC Ver
record since only the ICC Version record will be present in the batch.
So, in the below update statement, we have the filter only on PK1_VALUE, so that
all ICC Ver records having PK2_VALUE will also get updated with Failure status
if the additional ICC alone record gets failed.
ICC Ver records can only have a Error msg logged, they cannot have a warning message logged.
This is because warning messages are only logged during hierarchy traversal by AG.
AG is always associated to the additional ICC alone record and not any ICC Ver record.*/
/*kkq - below update should also have filter on param_name (eg) ICC.
Otherwise, in rare scenarios, pk1_value may be same for ICC,AG,VS etc*/
--debug(L_PROC_NAME||' ip.PARAM_NAME: '||ip.PARAM_NAME||' ip.PK2_VALUE: '||To_Char(ip.PK2_VALUE));
UPDATE EGO_PUBLICATION_BATCH_GT
SET STATUS = 'F', MESSAGE = l_message
WHERE BATCH_ID = l_batch_id
AND PK1_VALUE = ip.PK1_VALUE
AND SYSTEM_CODE = ip.SYSTEM_CODE;
UPDATE EGO_PUBLICATION_BATCH_GT
SET STATUS = 'F', MESSAGE = l_message
WHERE BATCH_ID = l_batch_id
AND PK1_VALUE = ip.PK1_VALUE
AND NVL(PK2_VALUE,'pk2_value') = NVL(ip.PK2_VALUE,'pk2_value')
AND NVL(PK3_VALUE,'pk3_value') = NVL(ip.PK3_VALUE,'pk3_value')
AND NVL(PK4_VALUE,'pk4_value') = NVL(ip.PK4_VALUE,'pk4_value')
AND NVL(PK5_VALUE,'pk5_value') = NVL(ip.PK5_VALUE,'pk5_value')
AND SYSTEM_CODE = ip.SYSTEM_CODE;
-- Update Err_Message in EGO_PUB_WS_ERROR table
UPDATE EGO_PUB_WS_ERRORS
SET ERR_MESSAGE = l_message
WHERE SESSION_ID = p_session_id
AND INPUT_ID = l_input_id
AND SYSTEM_CODE = l_system_code;
UPDATE EGO_PUBLICATION_BATCH_GT
SET STATUS = 'F', MESSAGE = l_message
WHERE BATCH_ID = l_batch_id
AND PK1_VALUE = ip.PK1_VALUE
AND SYSTEM_CODE = ip.SYSTEM_CODE;
UPDATE EGO_PUBLICATION_BATCH_GT
SET STATUS = 'F', MESSAGE = l_message
WHERE BATCH_ID = l_batch_id
AND PK1_VALUE = ip.PK1_VALUE
AND NVL(PK2_VALUE,'pk2_value') = NVL(ip.PK2_VALUE,'pk2_value')
AND NVL(PK3_VALUE,'pk3_value') = NVL(ip.PK3_VALUE,'pk3_value')
AND NVL(PK4_VALUE,'pk4_value') = NVL(ip.PK4_VALUE,'pk4_value')
AND NVL(PK5_VALUE,'pk5_value') = NVL(ip.PK5_VALUE,'pk5_value')
AND SYSTEM_CODE = ip.SYSTEM_CODE;
-- Update Err_Message in EGO_PUB_WS_ERROR table
UPDATE EGO_PUB_WS_ERRORS
SET ERR_MESSAGE = l_message
WHERE SESSION_ID = p_session_id
AND INPUT_ID = l_input_id
AND SYSTEM_CODE = l_system_code;
UPDATE EGO_PUBLICATION_BATCH_GT
SET STATUS = 'W', MESSAGE = l_message
WHERE BATCH_ID = l_batch_id
AND PK1_VALUE = ip.PK1_VALUE
AND SYSTEM_CODE = ip.SYSTEM_CODE;
UPDATE EGO_PUBLICATION_BATCH_GT
SET STATUS = 'W', MESSAGE = l_message
WHERE BATCH_ID = l_batch_id
AND PK1_VALUE = ip.PK1_VALUE
AND NVL(PK2_VALUE,'pk2_value') = NVL(ip.PK2_VALUE,'pk2_value')
AND NVL(PK3_VALUE,'pk3_value') = NVL(ip.PK3_VALUE,'pk3_value')
AND NVL(PK4_VALUE,'pk4_value') = NVL(ip.PK4_VALUE,'pk4_value')
AND NVL(PK5_VALUE,'pk5_value') = NVL(ip.PK5_VALUE,'pk5_value')
AND SYSTEM_CODE = ip.SYSTEM_CODE;
-- Update Err_Message in EGO_PUB_WS_ERROR table
UPDATE EGO_PUB_WS_ERRORS
SET ERR_MESSAGE = l_message
WHERE SESSION_ID = p_session_id
AND INPUT_ID = l_input_id
AND SYSTEM_CODE = l_system_code;
-- For each record in the temporary global table, invoke Update_Pub_Status API with Batch_id,
-- PKs, and Status, and Message to log the errors per entity per target system to batch
-- publication framework.
v_index := 1;
EGO_PUB_FWK_PK.Update_Pub_Status(l_batch_id, 2, bat_ent_stat_rec,x_bat_status_out,x_return_status,x_msg_count,x_msg_data);