[Home] [Help]
452:
453: -- ============================================================================
454: -- Name : Create_Params_ICC
455: -- Description : This procedure will determine the mode- BATCH/LIST and insert
456: -- the appropriate config parameters in the ego_pub_ws_config table
457: -- Called from Preprocess_Input_ICC
458: --
459: -- Scope : Private
460: --
501: --debug(L_PROC_NAME||' WEB_SERVICE_NAME: '||l_web_service_name);
502:
503:
504: IF l_mode = 'BATCH' THEN
505: /*BATCH_ID param is available only in ego_pub_ws_config and not in ego_pub_bat_params_b.
506: The Batch UI does not insert this param. The ServiceUtil inserts it into ego_pub_ws_config.
507: Hence for fetching the BATCH_ID, we are passing the mode as NULL.*/
508: l_batch_id := EGO_PUB_WS_UTIL.Get_Numeric_Param_Value(p_session_id, 'BATCH_ID', NULL, NULL);
509: --debug(L_PROC_NAME||'Batch_Id: '||l_batch_id);
502:
503:
504: IF l_mode = 'BATCH' THEN
505: /*BATCH_ID param is available only in ego_pub_ws_config and not in ego_pub_bat_params_b.
506: The Batch UI does not insert this param. The ServiceUtil inserts it into ego_pub_ws_config.
507: Hence for fetching the BATCH_ID, we are passing the mode as NULL.*/
508: l_batch_id := EGO_PUB_WS_UTIL.Get_Numeric_Param_Value(p_session_id, 'BATCH_ID', NULL, NULL);
509: --debug(L_PROC_NAME||'Batch_Id: '||l_batch_id);
510:
521: --debug(L_PROC_NAME||'Batch Mode, Returned from EGO_PUB_WS_UTIL.Get_Parameter_Names procedure, l_param_names.count: '||l_param_names.count);
522:
523:
524: --retrieve all single-value parameters of interest from EGO_PUB_BAT_PARAMS_B table
525: --and store them in table EGO_PUB_WS_CONFIG
526: FOR position IN 1..l_param_names.COUNT
527: LOOP
528: --debug(L_PROC_NAME||'Batch Mode, Inside For Loop on l_param_names.count, position: '||position);
529:
528: --debug(L_PROC_NAME||'Batch Mode, Inside For Loop on l_param_names.count, position: '||position);
529:
530: /* The below insert takes care of all params that have to be defaulted to TRUE */
531: IF l_param_names(position) NOT IN ('PARENTICCS','CHILDICCS','SYNC', 'TRIGGER_IMPORT') THEN
532: INSERT INTO EGO_PUB_WS_CONFIG ( session_id,
533: odi_session_id,
534: Parameter_Name,
535: Data_Type,
536: Char_value,
550: END LOOP;
551:
552: /* The below insert statements take care of the params that have to be taken from EGO_PUB_BAT_PARAMS_B table*/
553:
554: /* Batch Mode param PublishParent will be inserted into ego_pub_ws_config as PARENTICCS */
555: l_param_parent_hier := EGO_PUB_WS_UTIL.Get_Char_Param_Value(p_session_id => p_session_id,
556: p_param_name => 'PublishParent',
557: p_batch_id => l_batch_id,
558: p_mode => 'BATCH'
558: p_mode => 'BATCH'
559: );
560:
561: BEGIN
562: INSERT INTO EGO_PUB_WS_CONFIG ( session_id,
563: odi_session_id,
564: Parameter_Name,
565: Data_Type,
566: Char_value,
581: NULL;
582: END;
583:
584:
585: /* Batch Mode param PublishChild will be inserted into ego_pub_ws_config as CHILDICCS */
586: l_param_child_hier := EGO_PUB_WS_UTIL.Get_Char_Param_Value ( p_session_id => p_session_id,
587: p_param_name => 'PublishChild',
588: p_batch_id => l_batch_id,
589: p_mode => 'BATCH'
588: p_batch_id => l_batch_id,
589: p_mode => 'BATCH'
590: );
591:
592: INSERT INTO EGO_PUB_WS_CONFIG ( session_id,
593: odi_session_id,
594: Parameter_Name,
595: Data_Type,
596: Char_value,
605: sysdate,
606: G_CURRENT_USER_ID,
607: l_web_service_name);
608:
609: /* Batch Mode param Launch Sync Program will be inserted into ego_pub_ws_config as TRIGGER_IMPORT */
610: l_trigger_import := EGO_PUB_WS_UTIL.Get_Char_Param_Value(p_session_id => p_session_id,
611: p_param_name => EGO_PUB_WS_UTIL.G_TRIGGER_IMPORT_PARAM,
612: p_batch_id => l_batch_id,
613: p_mode => 'BATCH'
612: p_batch_id => l_batch_id,
613: p_mode => 'BATCH'
614: );
615:
616: INSERT INTO EGO_PUB_WS_CONFIG ( session_id,
617: odi_session_id,
618: Parameter_Name,
619: Data_Type,
620: Char_value,
630: G_CURRENT_USER_ID,
631: l_web_service_name);
632:
633:
634: /* Batch Mode param BATCH_PROCESS_TYPE=PUBLISH/SYNC will be inserted into ego_pub_ws_config as SYNC=Y/N */
635: l_publish_sync := EGO_PUB_WS_UTIL.Get_Char_Param_Value ( p_session_id => p_session_id,
636: p_param_name => EGO_PUB_WS_UTIL.G_SYNC_PARAM,
637: p_batch_id => l_batch_id,
638: p_mode => 'BATCH'
642: ELSIF l_publish_sync = 'SYNC' THEN
643: l_publish_sync := 'Y';
644: END IF;
645:
646: INSERT INTO EGO_PUB_WS_CONFIG ( session_id,
647: odi_session_id,
648: Parameter_Name,
649: Data_Type,
650: Char_value,
665:
666: l_batch_id := NULL;
667: --
668: --STEP ONE: RETRIEVE ALL SINGLE-VALUE CONFIGURATION PARAMETERS
669: -- AND STORE THEM IN TABLE EGO_PUB_WS_CONFIG
670: --
671:
672: --initialize arrays of parameter names
673: --debug(L_PROC_NAME||'List Mode, Calling EGO_PUB_WS_UTIL.Get_Parameter_Names procedure');
681: EGO_PUB_WS_UTIL.Get_Xpath_Expr(l_web_service_name, l_xpath_expr);
682: --debug(L_PROC_NAME||'List Mode, Returned from EGO_PUB_WS_UTIL.Get_Xpath_Expr procedure, l_xpath_expr.count: '||l_xpath_expr.count);
683:
684: --retrieve all single-value parameters of interest from XML
685: --and store them in table EGO_PUB_WS_CONFIG
686: FOR position IN 1..l_param_names.COUNT
687: LOOP
688: --debug(L_PROC_NAME||'List Mode, Inside For Loop on l_param_names.count, position: '||position||' ,l_xpath_expr: '||l_xpath_expr(position));
689:
692:
693: --if parameter is not provided, assume a default value of 'TRUE'
694:
695: IF l_config_option IS NOT NULL AND l_config_option <> '?' THEN
696: --debug(L_PROC_NAME||'List Mode, Insert into EGO_PUB_WS_CONFIG, Parameter_Name: '||l_param_names(position)||' ,l_config_option: '||l_config_option);
697:
698: /* In the below insert stmt, we are hardcoding data_type as 2 and providing the char value,
699: it implies that we are assuming all config params are of string type.
700: All config params except TRIGGER_IMPORT, SYNC take TRUE/FALSE (as strings not boolean).
698: /* In the below insert stmt, we are hardcoding data_type as 2 and providing the char value,
699: it implies that we are assuming all config params are of string type.
700: All config params except TRIGGER_IMPORT, SYNC take TRUE/FALSE (as strings not boolean).
701: TRIGGER_IMPORT alone takes it as Y/N. SYNC takes SYNC/PUBLISH */
702: INSERT INTO EGO_PUB_WS_CONFIG ( session_id,
703: odi_session_id,
704: Parameter_Name,
705: Data_Type,
706: Char_value,
723: This param is NOT available in ICC xml. So, always set it to Y so that
724: child VS will get synced. Explode_ICC() will call EGO_PUB_WS_AG.Explode_Attribute_Group() procedure.
725: It inturn looks for this param and decides whether to call EGO_PUB_WS_VS.Explode_Value_Set() or not
726: If ICC does not hard-code this param, Explode_Attribute_Group will raise a no-data-found exception.*/
727: --debug(L_PROC_NAME||'Batch,List Mode, Insert into EGO_PUB_WS_CONFIG, Parameter_Name: CHILD_VALUESETS with Value =TRUE');
728: INSERT INTO EGO_PUB_WS_CONFIG ( session_id,
729: odi_session_id,
730: Parameter_Name,
731: Data_Type,
724: child VS will get synced. Explode_ICC() will call EGO_PUB_WS_AG.Explode_Attribute_Group() procedure.
725: It inturn looks for this param and decides whether to call EGO_PUB_WS_VS.Explode_Value_Set() or not
726: If ICC does not hard-code this param, Explode_Attribute_Group will raise a no-data-found exception.*/
727: --debug(L_PROC_NAME||'Batch,List Mode, Insert into EGO_PUB_WS_CONFIG, Parameter_Name: CHILD_VALUESETS with Value =TRUE');
728: INSERT INTO EGO_PUB_WS_CONFIG ( session_id,
729: odi_session_id,
730: Parameter_Name,
731: Data_Type,
732: Char_value,
749: l_web_service_name
750: );
751: --
752: --STEP TWO: RETRIEVE ALL MULTI-VALUE CONFIGURATION PARAMETERS
753: -- AND STORE THEM IN TABLE EGO_PUB_WS_CONFIG
754: --
755:
756: --RETRIEVING LIST OF LANGUAGES
757: l_language_search_str := EGO_PUB_WS_UTIL.Get_Language_Search_Str(l_web_service_name);
766:
767:
768: EXCEPTION
769: --- bug 12755038 , if parallel session is trying to insert the same
770: --- record into EGO_PUB_WS_CONFIG, consume the exception , unique
771: --- index added to prevent duplicates from getting inerted which would subsequently fail
772: --- when the rows were queried later.
773: WHEN DUP_VAL_ON_INDEX then
774: NULL;
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);
1319: l_param_child_hier := EGO_PUB_WS_UTIL.Get_Char_Param_Value(p_session_id, 'CHILDICCS', NULL, NULL);
1320: --debug(L_PROC_NAME||'l_param_parent_hier: '||l_param_parent_hier||' ,l_param_child_hier: '||l_param_child_hier);
1700: --debug(L_PROC_NAME||' End of Loop on cur_derived_ags cursor');
1701:
1702: /*For all the AGs exploded above, VSs have to be derived, so calling EGO_PUB_WS_AG.Explode_Attribute_Group procedure
1703: only when Publish Valuesets config param is set to Yes.
1704: For both modes - batch and list, VALUESETS param has been added to EGO_PUB_WS_CONFIG table in Create_params_ICC procedure.
1705: So, we don't differentiate the mode while fetching this param below*/
1706:
1707: l_param_vs := EGO_PUB_WS_UTIL.Get_Char_Param_Value(p_session_id, 'VALUESETS', NULL, NULL);
1708: --debug(L_PROC_NAME||' l_param_vs: '||l_param_vs);
1860: -- ================================================================================================================
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
1898: l_entity_rec_count :=0;
1899:
1900: /* -- Get the number of records for configuration parameters
1901: -- for the session
1902: -- If no records exist in EGO_PUB_WS_CONFIG for the session then only
1903: -- create configuration parameters for the session.
1904: */
1905:
1906: --debug(L_PROC_NAME||'before fetching l_param_rec_count');
1906: --debug(L_PROC_NAME||'before fetching l_param_rec_count');
1907:
1908: SELECT COUNT(1)
1909: INTO l_param_rec_count
1910: FROM EGO_PUB_WS_CONFIG
1911: WHERE SESSION_ID = p_session_id
1912: AND PARAMETER_NAME NOT IN('ODI_SESSION_ID', 'SYSTEM_CODE', 'MODE', 'BATCH_ID');
1913:
1914: --debug(L_PROC_NAME||'after fetching l_param_rec_count, count: '|| l_param_rec_count);
1913:
1914: --debug(L_PROC_NAME||'after fetching l_param_rec_count, count: '|| l_param_rec_count);
1915:
1916:
1917: /* When sync'ing to multiple systems, we have to ensure that params get inserted into ego_pub_ws_config table
1918: only once. Once they are inserted for first system, they can be reused for later systems.
1919: Hence checking for l_param_rec_count = 0*/
1920: IF l_param_rec_count = 0 THEN
1921: --Create Input parameters for ODI
2083: x_msg_count NUMBER;
2084: x_msg_data VARCHAR2(500);
2085: L_PROC_NAME VARCHAR2(50);
2086:
2087: -- Get System_code from EGO_PUB_WS_CONFIG table
2088:
2089: CURSOR c_system_codes(l_session_id NUMBER)
2090: IS
2091: SELECT CHAR_VALUE
2088:
2089: CURSOR c_system_codes(l_session_id NUMBER)
2090: IS
2091: SELECT CHAR_VALUE
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
2161:
2162:
2163: SELECT CHAR_VALUE
2164: INTO l_trigger_import
2165: FROM EGO_PUB_WS_CONFIG
2166: WHERE SESSION_ID = p_session_id
2167: AND PARAMETER_NAME = 'TRIGGER_IMPORT';
2168:
2169: --debug(L_PROC_NAME||' l_trigger_import: '||l_trigger_import);