[Home] [Help]
371: IF (p_show_error_flag = G_SHOW_ERROR_FLAG_YES) THEN
372: /* p_show_error_flag will be Y only
373: - for version records for versioned ICCs
374: - for ICC records for non-versioned ICCs */
375: UPDATE ego_pub_ws_entities
376: SET ref10_value = G_SHOW_ERROR_FLAG_YES
377: WHERE (session_id = p_session_id
378: AND pk1_value = p_icc_id
379: AND (p_icc_ver_id IS NULL
386:
387: /*If not duplicate then, insert the record into entities table.
388: Note that parent_sequence_id column is inserted as NULL.
389: This is because we do not maintain info about parent-child ICC hierarchy info
390: in this ego_pub_ws_entities table. Every ICC (though it was derived as a parent/child ICC
391: in create_entities_icc) is treated as an independent ICC.*/
392: IF (NOT l_is_duplicate)
393: THEN
394: p_icc_id_tab (num_of_records + 1) := p_icc_id;
394: p_icc_id_tab (num_of_records + 1) := p_icc_id;
395: p_icc_ver_id_tab (num_of_records + 1) := p_icc_ver_id;
396: p_icc_name_tab (num_of_records + 1) := p_icc_name;
397:
398: /*In ego_pub_ws_entities, we are reusing the ref10_value column for storing show_error_flag*/
399: INSERT INTO EGO_PUB_WS_ENTITIES(SESSION_ID,
400: ODI_SESSION_ID,
401: SEQUENCE_ID,
402: PARENT_SEQUENCE_ID,
395: p_icc_ver_id_tab (num_of_records + 1) := p_icc_ver_id;
396: p_icc_name_tab (num_of_records + 1) := p_icc_name;
397:
398: /*In ego_pub_ws_entities, we are reusing the ref10_value column for storing show_error_flag*/
399: INSERT INTO EGO_PUB_WS_ENTITIES(SESSION_ID,
400: ODI_SESSION_ID,
401: SEQUENCE_ID,
402: PARENT_SEQUENCE_ID,
403: ENTITY_TYPE,
420: CREATED_BY,
421: USER_ENTERED)
422: VALUES( p_session_id,
423: p_session_id,
424: EGO_PUB_WS_ENTITIES_S.nextval,
425: NULL,
426: G_ENTITY_TYPE_ICC,
427: p_icc_id,
428: p_icc_ver_id,
1119:
1120: -- =========================================================================================================
1121: -- Name : Create_Entities_ICC
1122: -- Description : This procedure performs the following actions:
1123: -- 1. Populates ODI input table EGO_PUB_WS_ENTITIES for entity ICC
1124: -- based on the invokation type (e.g. batch, list)
1125: -- 2. Based on the config params PARENTICCS (PublishParent) and CHILDICCS (PublishChild)
1126: -- derive the parent and child hierarchy of the given ICCs and
1127: -- populate them into EGO_PUB_WS_ENTITIES
1123: -- 1. Populates ODI input table EGO_PUB_WS_ENTITIES for entity ICC
1124: -- based on the invokation type (e.g. batch, list)
1125: -- 2. Based on the config params PARENTICCS (PublishParent) and CHILDICCS (PublishChild)
1126: -- derive the parent and child hierarchy of the given ICCs and
1127: -- populate them into EGO_PUB_WS_ENTITIES
1128: -- This procedure is called from Preprocess_Input_ICC
1129: --
1130: -- Scope : Private
1131: --
1169:
1170: l_mode := EGO_PUB_WS_UTIL.Get_Char_Param_Value(p_session_id, 'MODE', NULL, NULL);
1171: --debug(L_PROC_NAME||'MODE: '||l_mode);
1172:
1173: --populate odi input table ego_pub_ws_entities depending on mode from different data sources (i.e.) batch, list
1174: CASE
1175:
1176: --if mode is batch, get information from publication framework using batch_id
1177: WHEN l_mode = 'BATCH' THEN
1178: l_batch_id := EGO_PUB_WS_UTIL.Get_Numeric_Param_Value(p_session_id,'BATCH_ID', NULL, NULL);
1179: --debug(L_PROC_NAME||'Batch_Id: '||l_batch_id);
1180:
1181: --retrieving all ICCs in batch from publication framework entity tables
1182: --and inserting data into ODI metadata input table ego_pub_ws_entities
1183:
1184:
1185: IF l_batch_id IS NOT NULL THEN
1186: /*we are using to_char since l_dup_icc_id_tab, l_dup_icc_ver_id_tab are varchar2 tables
1265: IF (l_dup_icc_ver_id_tab (i) IS NOT NULL)
1266: THEN
1267: /* If Version is given along with the ICC, insert an additional record with version as null.
1268: This entry is to sync up the ICC details.
1269: Though this record is not user entered, we still are inserting user_entered_flag in ego_pub_ws_entities
1270: as Y, so that this record does not get copied back batch fwk tables.
1271: This record should NOT be seen in batch fwk entity objects table
1272: */
1273: --debug(L_PROC_NAME||' calling check_duplicate_and_insert for an additional ICC record with version as null');
1310: END LOOP; /* end of For loop on l_dup_icc_id_tab */
1311: --debug(L_PROC_NAME||' End of For Loop on l_dup_icc_id_tab.count');
1312:
1313: /* If config param ParentICCs or ChildICCs is set to TRUE, then derive the parent or child icc
1314: and insert into ego_pub_ws_entities table */
1315:
1316: /* In Create_Params_ICC() procedure, we have dumped config params in both modes (batch,list) into EGO_PUB_WS_CONFIG table.
1317: So, now while fetching the PARENTICCS,CHILDICCS params, we can just get from this table without reference to any mode*/
1318: l_param_parent_hier := EGO_PUB_WS_UTIL.Get_Char_Param_Value(p_session_id, 'PARENTICCS', NULL, NULL);
1374:
1375: /* For every versioned ICC , insert an additional record with version as null.
1376: This entry is to sync up the ICC details.
1377: show_error flag will be No for these records.
1378: Though this record is not user entered, we still are inserting user_entered_flag in ego_pub_ws_entities
1379: as Y, so that this record does not get copied back batch fwk tables.
1380: This record should NOT be seen in batch fwk entity objects table
1381: */
1382: IF ( l_heirarchical_iccs(counter_hier).version_seq_id IS NOT NULL)
1428: -- =========================================================================================================
1429: -- Name : Explode_ICC
1430: -- Description : This procedure performs the following actions:
1431: -- 1. Explode the given ICCs and fetches the AGs associated to them.
1432: -- Insert these AGs into EGO_PUB_WS_ENTITIES table
1433: -- 2. Based on the config param VALUESETS, finds out whether Valuesets have to be exploded
1434: -- from the derived AGs and calls EGO_PUB_WS_AG.Explode_Attribute_Group procedure.
1435: -- This procedure is called from Preprocess_Input_ICC
1436: --
1459: -- x_group_id NUMBER :=654;
1460: x_return_status VARCHAR2(1);
1461: L_PROC_NAME VARCHAR2(50);
1462:
1463: /* The below cursor fetches the AGs associated to all ICCs in ego_pub_ws_entities table
1464: epwe.pk1_value is icc_id, epwe.ref1_value is icc_name, epwe.sequence_id is the sequence_id of icc record
1465: eoaab.attr_group_id is ag_id, efdfce.descriptive_flex_context_code is ag_name */
1466: CURSOR cur_derived_ags(p_session_id NUMBER) IS
1467: SELECT epwe.pk1_value , epwe.ref1_value ,epwe.sequence_id
1468: ,eoaab.attr_group_id, efdfce.descriptive_flex_context_code
1469: FROM ego_obj_ag_assocs_b eoaab,
1470: fnd_objects fo,
1471: ego_fnd_dsc_flx_ctx_ext efdfce,
1472: ego_pub_ws_entities epwe
1473: WHERE eoaab.object_id=fo.object_id
1474: AND fo.obj_name ='EGO_ITEM'
1475: AND eoaab.attr_group_id=efdfce.attr_group_id
1476: AND efdfce.descriptive_flexfield_name = 'EGO_ITEMMGMT_GROUP'
1498: FROM EGO_MTL_CATALOG_GRP_VERS_B EMCGVB,
1499: EGO_TRANS_ATTR_VERS_B ETAVB
1500: , FND_FLEX_VALUE_SETS FFVS,
1501: EGO_FLEX_VALUESET_VERSION_B EFVVB
1502: , EGO_PUB_WS_ENTITIES EPWE
1503: WHERE EMCGVB.ITEM_CATALOG_GROUP_ID=ETAVB.ITEM_CATALOG_GROUP_ID
1504: AND EMCGVB.VERSION_SEQ_ID=ETAVB.ICC_VERSION_NUMBER
1505: AND ETAVB.VALUE_SET_ID=FFVS.FLEX_VALUE_SET_ID
1506: AND FFVS.VALIDATION_TYPE IN ('X','I')
1529: FOR rec_derived_ags IN cur_derived_ags(p_session_id) LOOP
1530: --debug(L_PROC_NAME||'enter cur_derived_ags, attr_group_id: '||rec_derived_ags.attr_group_id||' ,l_prev_ag_id : '||l_prev_ag_id);
1531:
1532: IF rec_derived_ags.attr_group_id <> l_prev_ag_id THEN
1533: /* Current AG record does not already exist in ego_pub_ws_entities table.
1534: So, insert a AG record and an AG-ICC association record into ego_pub_ws_entities table.*/
1535:
1536: -- get next value for the sequence_id
1537: SELECT EGO_PUB_WS_ENTITIES_S.nextval
1530: --debug(L_PROC_NAME||'enter cur_derived_ags, attr_group_id: '||rec_derived_ags.attr_group_id||' ,l_prev_ag_id : '||l_prev_ag_id);
1531:
1532: IF rec_derived_ags.attr_group_id <> l_prev_ag_id THEN
1533: /* Current AG record does not already exist in ego_pub_ws_entities table.
1534: So, insert a AG record and an AG-ICC association record into ego_pub_ws_entities table.*/
1535:
1536: -- get next value for the sequence_id
1537: SELECT EGO_PUB_WS_ENTITIES_S.nextval
1538: INTO l_seq_id
1533: /* Current AG record does not already exist in ego_pub_ws_entities table.
1534: So, insert a AG record and an AG-ICC association record into ego_pub_ws_entities table.*/
1535:
1536: -- get next value for the sequence_id
1537: SELECT EGO_PUB_WS_ENTITIES_S.nextval
1538: INTO l_seq_id
1539: FROM dual;
1540:
1541: --debug(L_PROC_NAME||'l_seq_id: '||l_seq_id||' ,rec_derived_ags.sequence_id: '||rec_derived_ags.sequence_id);
1539: FROM dual;
1540:
1541: --debug(L_PROC_NAME||'l_seq_id: '||l_seq_id||' ,rec_derived_ags.sequence_id: '||rec_derived_ags.sequence_id);
1542:
1543: INSERT INTO EGO_PUB_WS_ENTITIES(SESSION_ID,
1544: ODI_SESSION_ID,
1545: SEQUENCE_ID,
1546: PARENT_SEQUENCE_ID,
1547: ENTITY_TYPE,
1587: G_CURRENT_USER_ID,
1588: G_USER_ENTERED_FLAG_NO
1589: );
1590:
1591: INSERT INTO EGO_PUB_WS_ENTITIES(SESSION_ID,
1592: ODI_SESSION_ID,
1593: SEQUENCE_ID,
1594: PARENT_SEQUENCE_ID,
1595: ENTITY_TYPE,
1638:
1639: --debug(L_PROC_NAME||'Inserted both AG and ICC-AG association records');
1640:
1641: ELSE /* rec_derived_ags.ag_id l_prev_ag_id */
1642: /* Current AG already exists in ego_pub_ws_entities table,
1643: so insert only the AG-ICC assocation record*/
1644: INSERT INTO EGO_PUB_WS_ENTITIES(SESSION_ID,
1645: ODI_SESSION_ID,
1646: SEQUENCE_ID,
1640:
1641: ELSE /* rec_derived_ags.ag_id l_prev_ag_id */
1642: /* Current AG already exists in ego_pub_ws_entities table,
1643: so insert only the AG-ICC assocation record*/
1644: INSERT INTO EGO_PUB_WS_ENTITIES(SESSION_ID,
1645: ODI_SESSION_ID,
1646: SEQUENCE_ID,
1647: PARENT_SEQUENCE_ID,
1648: ENTITY_TYPE,
1720: */
1721:
1722: FOR trans_attr_vs IN cur_trans_attr_vs(p_session_id) LOOP
1723:
1724: --inserting records in to EGO_PUB_WS_ENTITIES
1725: ego_pub_ws_vs.Insert_VS_Entity ( p_session_id => p_session_id
1726: ,p_vs_id => trans_attr_vs.FLEX_VALUE_SET_ID
1727: ,p_vs_name => trans_attr_vs.FLEX_VALUE_SET_NAME
1728: ,p_vs_version => trans_attr_vs.vs_seq_id
1760: CURSOR c_derived_entities(l_session_id NUMBER) IS
1761: SELECT PK1_VALUE,
1762: PK2_VALUE,
1763: ENTITY_TYPE
1764: FROM EGO_PUB_WS_ENTITIES
1765: WHERE SESSION_ID = l_session_id
1766: AND NVL(USER_ENTERED, 'N') = 'N'
1767: AND PARENT_SEQUENCE_ID IS NULL;
1768:
1806:
1807: /*changing the below select statement to use the base table instead of view.
1808: Using an NVL on pk2_value since it can be null for AG and VS but not ICC
1809: kkq- do we really need below select stmt, since
1810: ego_pub_ws_entities will initially have the same records as ego_pub_bat_ent_objs_b (user_entered=Y)
1811: and as we derive new entity records (user_entered=N), we do not insert them into ego_pub_ws_entities
1812: if they are already present. We can safely assume that all records fetched by cursor c_derived_entities
1813: will not be present in ego_pub_bat_ent_objs_b.*/
1814:
1807: /*changing the below select statement to use the base table instead of view.
1808: Using an NVL on pk2_value since it can be null for AG and VS but not ICC
1809: kkq- do we really need below select stmt, since
1810: ego_pub_ws_entities will initially have the same records as ego_pub_bat_ent_objs_b (user_entered=Y)
1811: and as we derive new entity records (user_entered=N), we do not insert them into ego_pub_ws_entities
1812: if they are already present. We can safely assume that all records fetched by cursor c_derived_entities
1813: will not be present in ego_pub_bat_ent_objs_b.*/
1814:
1815: SELECT COUNT(*) INTO v_count
1861: -- Name : Preprocess_Input_ICC
1862: -- Description : This is the main procedure for pre-processing the ICC entitiy records.
1863: -- This procedure performs the following actions:
1864: -- 1. Calls Create_Params_ICC() to insert config params to EGO_PUB_WS_CONFIG table.
1865: -- 2. Calls Create_Entities_ICC procedure, to populate ODI input table EGO_PUB_WS_ENTITIES
1866: -- for entity ICC, based on the invokation type (e.g. batch, list)
1867: -- 3. Calls Explode_ICC procedure to explode the ICCs and populate AGs into EGO_PUB_WS_ENTITIES
1868: -- 4. In Batch Mode flow, calls Write_Derived_Entites_ToBatFwk procedure to
1869: -- add the derived entities (ICCs,AGs) to Batch Fwk Entities table - EGO_PUB_BAT_ENT_OBJS_B
1863: -- This procedure performs the following actions:
1864: -- 1. Calls Create_Params_ICC() to insert config params to EGO_PUB_WS_CONFIG table.
1865: -- 2. Calls Create_Entities_ICC procedure, to populate ODI input table EGO_PUB_WS_ENTITIES
1866: -- for entity ICC, based on the invokation type (e.g. batch, list)
1867: -- 3. Calls Explode_ICC procedure to explode the ICCs and populate AGs into EGO_PUB_WS_ENTITIES
1868: -- 4. In Batch Mode flow, calls Write_Derived_Entites_ToBatFwk procedure to
1869: -- add the derived entities (ICCs,AGs) to Batch Fwk Entities table - EGO_PUB_BAT_ENT_OBJS_B
1870: -- This procedure gets called from ODI project EGO_SYNCICCDETAILS.
1871: --
1934: --debug(L_PROC_NAME||'before fetching l_entity_rec_count');
1935:
1936: SELECT COUNT(1)
1937: INTO l_entity_rec_count
1938: FROM EGO_PUB_WS_ENTITIES
1939: WHERE SESSION_ID = p_session_id
1940: AND ENTITY_TYPE = G_ENTITY_TYPE_ICC;
1941:
1942: --debug(L_PROC_NAME||'after fetching l_entity_rec_count, count: '|| l_entity_rec_count);
1939: WHERE SESSION_ID = p_session_id
1940: AND ENTITY_TYPE = G_ENTITY_TYPE_ICC;
1941:
1942: --debug(L_PROC_NAME||'after fetching l_entity_rec_count, count: '|| l_entity_rec_count);
1943: /* When sync'ing to multiple systems, we have to ensure that entities get inserted into ego_pub_ws_entities table
1944: only once. Once they are inserted for first system, they can be reused for later systems.
1945: Hence checking for l_entity_rec_count = 0*/
1946: IF l_entity_rec_count = 0 THEN
1947: --Create ODI Input table containing entities to process
2092: FROM EGO_PUB_WS_CONFIG
2093: WHERE SESSION_ID = l_session_id
2094: AND PARAMETER_NAME = 'SYSTEM_CODE';
2095:
2096: /* -- Get input and derived entities from EGO_PUB_WS_ENTITIES
2097: -- consider only the ICC records that user has entered (ref10_value=Y)
2098: -- consider all AG and VS records */
2099: CURSOR c_pub_ws_entities(l_session_id NUMBER)
2100: IS
2103: PK3_VALUE,
2104: PK4_VALUE,
2105: PK5_VALUE,
2106: USER_ENTERED
2107: FROM EGO_PUB_WS_ENTITIES
2108: WHERE SESSION_ID = l_session_id
2109: AND ( ( NVL(ENTITY_TYPE,'XYZ') <> G_ENTITY_TYPE_ICC )
2110: OR (ENTITY_TYPE=G_ENTITY_TYPE_ICC AND REF10_VALUE=G_CHAR_YES) );
2111:
2168:
2169: --debug(L_PROC_NAME||' l_trigger_import: '||l_trigger_import);
2170:
2171: -- use global temporary table (reuse the one currently of batch publication framework)
2172: -- and copy records in EGO_PUB_WS_ENTITIES into it while setting columns status equal to SUCCESS and
2173: -- SYSTEM_CODE equal to current target system for all records.
2174:
2175: FOR sys IN c_system_codes(p_session_id)
2176: LOOP