APPS.EGO_PUB_WS_AG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 38

        /* SOAPUI inserts '?' for null values and in create_entities_attributegroup we read null or non-existant tag as '-1' */
	IF ((p_ag_id = '?') or (p_ag_id = '-1'))  THEN
		l_ag_id := NULL;

Line: 55

		SELECT ATTR_GROUP_NAME
		INTO x_ag_name
		FROM EGO_ATTR_GROUPS_V
		WHERE ATTR_GROUP_ID = to_number(l_ag_id);

Line: 61

		--insert into ego.tr_temp values('x_ag_name', x_ag_name);

Line: 65

		SELECT ATTR_GROUP_ID
		INTO x_ag_id
		FROM EGO_ATTR_GROUPS_V
		WHERE ATTR_GROUP_NAME = l_ag_name;

Line: 80

		SELECT Nvl(Max(INPUT_ID),0) + 1
		INTO l_index
		FROM EGO_PUB_WS_INPUT_IDENTIFIERS
		WHERE session_id =  p_session_id;

Line: 143

		-- replacing following SELECT query with newly added function
		-- in EGO_PUB_WS_UTIL - Get_Numeric_Param_Value, Get_Char_Param_Value,
		-- Get_Date_Param_Value functions

		/*SELECT CHAR_VALUE
		INTO l_mode
		FROM EGO_PUB_WS_CONFIG
		WHERE PARAMETER_NAME = 'MODE'
		AND session_id = p_session_id;

Line: 153

		--insert into EGO.tr_temp VALUES('create param P_session_id :', p_session_id);

Line: 157

		--insert into EGO.tr_temp VALUES('create param P_mode :', l_mode);

Line: 160

		SELECT WEB_SERVICE_NAME
		INTO l_web_service_name
		FROM EGO_PUB_WS_PARAMS
		WHERE SESSION_ID = p_session_id;

Line: 165

		--insert into EGO.tr_temp VALUES('create param l_web_service_name :', l_web_service_name);

Line: 169

			-- replacing following SELECT query with newly added function
			-- in EGO_PUB_WS_UTIL - Get_Numeric_Param_Value, Get_Char_Param_Value,
			-- Get_Date_Param_Value functions

			/*SELECT numeric_value
			INTO l_batch_id
			FROM EGO_PUB_WS_CONFIG
			WHERE PARAMETER_NAME = 'BATCH_ID'
			AND session_id = p_session_id;

Line: 194

				--insert into EGO.tr_temp VALUES('create param l_config_option :', l_config_option);

Line: 198

					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,
									'Y',
									sysdate,
									0,
									l_web_service_name);

Line: 220

			SELECT CHAR_VALUE
			INTO l_param_value
			FROM EGO_PUB_BAT_PARAMS_B
			WHERE PARAMETER_NAME = EGO_PUB_WS_UTIL.G_SYNC_PARAM
                        AND TYPE_ID = l_batch_id;

Line: 232

			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_param_value,
							sysdate,
							0,
							l_web_service_name);

Line: 251

			SELECT CHAR_VALUE
			INTO l_param_value
			FROM EGO_PUB_BAT_PARAMS_B
			WHERE PARAMETER_NAME = EGO_PUB_WS_UTIL.G_TRIGGER_IMPORT_PARAM
                        AND TYPE_ID = l_batch_id;

Line: 257

			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,
							l_param_value,
							sysdate,
							0,
							l_web_service_name);

Line: 293

		--insert into EGO.tr_temp VALUES('create param l_param_names COUNT :', l_param_names_count);

Line: 301

			--insert into EGO.tr_temp VALUES('create param l_config_option :', l_config_option);

Line: 306

				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,
								0,
								l_web_service_name);

Line: 340

		--Inserts language options in Config table
		EGO_PUB_WS_UTIL.Config_Languages(p_session_id,
										l_language_search_str,
										l_web_service_name);

Line: 385

SELECT pk1_value,
       pk2_value,
       pk3_value
FROM EGO_PUB_BAT_ENT_OBJS_V
WHERE batch_id = p_batch_id AND user_entered = 'Y';

Line: 394

		-- replacing following SELECT query with newly added function
		-- in EGO_PUB_WS_UTIL - Get_Numeric_Param_Value, Get_Char_Param_Value,
		-- Get_Date_Param_Value functions

		/*SELECT char_value
		INTO l_mode
		FROM EGO_PUB_WS_CONFIG
		WHERE session_id = p_session_id
		AND parameter_name = 'MODE';

Line: 405

		--insert into EGO.tr_temp values('create input entities p_session_id :', p_session_id);

Line: 409

		--insert into EGO.tr_temp values('create input entities l_mode :', l_mode);

Line: 417

			-- replacing following SELECT query with newly added function
			-- in EGO_PUB_WS_UTIL - Get_Numeric_Param_Value, Get_Char_Param_Value,
			-- Get_Date_Param_Value functions
			/*SELECT NUMERIC_VALUE
			INTO l_batch_id
			FROM EGO_PUB_WS_CONFIG
			WHERE SESSION_ID = p_session_id
			AND PARAMETER_NAME = 'BATCH_ID';

Line: 430

			--and inserting data into ODI metadata input table

			l_ag_index :=0;

Line: 453

						--trudave: EGO_PUB_WS_VALIDATIONS.Validate_Attribute_Group inserts the relevant errors
						--trudave: Do we need to insert the errors again ? I think, no need to have redundant errors.
						--raise_application_error(-20104, 'Invalid AttributeGroup Id');

Line: 458

					--if ag is valid, insert to input table, otherwise generate warning
					IF l_is_valid = TRUE THEN

						-- if ag id exists in input table do not insert ag id
						BEGIN
							SELECT pk1_value
							INTO l_attr_grp_id
							FROM EGO_PUB_WS_ENTITIES
							WHERE pk1_value = l_agrp_id
							AND session_id = p_session_id;

Line: 478

							SELECT EGO_PUB_WS_ENTITIES_S.nextval
							INTO l_seq_id
							FROM dual;

Line: 482

							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,
											CREATION_DATE,
											CREATED_BY,
											USER_ENTERED)
										VALUES( p_session_id,
											p_session_id,
											l_seq_id,
											NULL,
											G_ENTITY_TYPE_AG,
											l_agrp_id,
											NULL,
											NULL,
											NULL,
											NULL,
											l_ag_name,
											NULL,
											NULL,
											NULL,
											NULL,
											sysdate,
											0,
											'Y'
										       );

Line: 530

			--insert into EGO.tr_temp values('create input entities l_mode :', l_mode);

Line: 533

			SELECT   Decode(existsNode(ag_id, '/AGId'), 1, Nvl(extractValue(ag_id, '/AGId'), -1), 0 , -1)
			BULK COLLECT INTO  l_ag_id_tab
			FROM(	SELECT  Value(agid) ag_id
					FROM EGO_PUB_WS_PARAMS i,
					TABLE(XMLSequence(
					extract(i.xmlcontent, 'AGQueryParam/AGIdentifiersList/AGIdentifier/AGId') )) agid
					WHERE session_id=p_session_id
				);

Line: 543

			SELECT   extractValue(ag_name, '/AGName')
			BULK COLLECT INTO  l_ag_name_tab
			FROM(	SELECT  Value(agname) ag_name
					FROM EGO_PUB_WS_PARAMS i,
					TABLE(XMLSequence(
					extract(i.xmlcontent, 'AGQueryParam/AGIdentifiersList/AGIdentifier/AGName') )) agname
					WHERE session_id=p_session_id
				);

Line: 559

			/*insert into tr_temp
			values(l_count, 'l_count = ' || l_count);*/

Line: 561

        	--insert into EGO.tr_temp values('create input entities l_count :', l_count);

Line: 564

			--inserting from XML into data into ODI structure input table
			FOR i IN 1..l_count
			LOOP

				--START VALIDATIONS-----------------------

				--validate ag id and/or name
				IF l_ag_id_tab.Count >= i THEN
					l_temp_varchar1 := l_ag_id_tab(i);

Line: 582

				/*insert into tr_temp
				values(l_ag_name_tab.count, 'l_temp_varchar2 = ' || l_temp_varchar2);*/

Line: 592

					--trudave: EGO_PUB_WS_VALIDATIONS.Validate_Attribute_Group inserts the relevant errors
					--trudave: Do we need to insert the errors again ? I think, no need to have redundant errors.
					--raise_application_error(-20104, 'Invalid ag Id or Name ' || l_temp_varchar2);

Line: 599

				--INSERT INTO EGO.tr_temp values('session_id', p_session_id);

Line: 600

				--INSERT INTO EGO.tr_temp values('l_ag_name', l_ag_name);

Line: 602

				--if ag is valid, insert to input table, otherwise generate warning
				IF l_is_valid = TRUE THEN

					-- if ag id exists in input table do not insert ag id
					BEGIN
						SELECT pk1_value
						INTO l_attr_grp_id
						FROM EGO_PUB_WS_ENTITIES
						WHERE pk1_value = l_ag_id
						AND session_id = p_session_id;

Line: 621

						SELECT EGO_PUB_WS_ENTITIES_S.nextval
						INTO l_seq_id
						FROM dual;

Line: 625

						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,
										l_agrp_id,
										NULL,
										NULL,
										NULL,
									        NULL,
										l_ag_name,
										NULL,
										NULL,
									        NULL,
									        NULL,
										NULL,
										NULL,
										NULL,
										NULL,
										NULL,
										sysdate,
									        0,
										'Y'
									       );

Line: 683

    SELECT 1
    FROM DUAL WHERE EXISTS (
                            SELECT NULL
                            FROM ego_value_set_ext evse
                            WHERE evse.value_set_id = p_valueset_id
                           );

Line: 748

SELECT SESSION_ID,
       SEQUENCE_ID,
       PK1_VALUE,
       REF1_VALUE
FROM EGO_PUB_WS_ENTITIES
WHERE SESSION_ID = p_session_id
AND ENTITY_TYPE = G_ENTITY_TYPE_AG
AND PARENT_SEQUENCE_ID IS NULL;

Line: 760

SELECT  ffvs.flex_value_set_id,
              ffvs.flex_value_set_name,
              fdfcu.application_column_name,
              efdcue.attr_id
FROM FND_DESCR_FLEX_COLUMN_USAGES fdfcu,
          EGO_FND_DF_COL_USGS_EXT efdcue,
          EGO_FND_DSC_FLX_CTX_EXT efdfce,
          FND_FLEX_VALUE_SETS ffvs
WHERE fdfcu.descriptive_flexfield_name = efdcue.descriptive_flexfield_name
AND fdfcu.application_id = efdcue.application_id
AND fdfcu.descriptive_flex_context_code = efdcue.descriptive_flex_context_code
AND fdfcu.application_column_name = efdcue.application_column_name
AND fdfcu.flex_value_set_id = ffvs.flex_value_set_id
AND efdcue.descriptive_flex_context_code = efdfce.descriptive_flex_context_code
AND efdcue.descriptive_flexfield_name = efdfce.descriptive_flexfield_name
AND efdcue.application_id = efdfce.application_id
AND efdfce.application_id = 431
AND efdfce.descriptive_flexfield_name = 'EGO_ITEMMGMT_GROUP'
AND efdfce.attr_group_id = p_ag_id;

Line: 782

	SELECT WEB_SERVICE_NAME
	INTO l_web_service_name
	FROM EGO_PUB_WS_PARAMS
	WHERE SESSION_ID = p_session_id;

Line: 804

	--looping through all user selected end-ags in the batch
    FOR r in c_odi_end_ags(p_session_id)
	LOOP

		--debugging statement remove
		-- get attribute group id (pk1_value), attribute group name (ref1_value), seq_id
		l_ag_id := to_number(r.pk1_value); --a100

Line: 828

				SELECT PK1_VALUE, SEQUENCE_ID, REF1_VALUE
				INTO l_vs_id, l_vs_seq_id, l_vs_name
				FROM EGO_PUB_WS_ENTITIES
				WHERE SESSION_ID = p_session_id
				AND ENTITY_TYPE = G_ENTITY_TYPE_VS
				AND PK1_VALUE = l_valueset_id
				AND PARENT_SEQUENCE_ID IS NULL;

Line: 842

			--insert into EGO.tr_temp VALUES ('l_vs_id', l_vs_id);

Line: 857

				SELECT EGO_PUB_WS_ENTITIES_S.nextval
				INTO l_seq_id
				FROM dual;

Line: 861

				-- as value set does not exist in input table, insert value set record and
				-- insert its association to attribute group

				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_VS,
								l_valueset_id,
							        NULL,
								NULL,
								NULL,
								NULL,
							        l_valueset_name,
								l_attr_id,
								l_application_col_name,
								NULL,
								NULL,
								l_ref6_value,
								NULL,
								NULL,
								NULL,
							        NULL,
								sysdate,
								0,
								'N'
							   );

Line: 912

				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,
								l_parent_seq_id,
								G_ENTITY_TYPE_VS,
								l_valueset_id,
							        NULL,
								NULL,
								NULL,
								NULL,
								l_valueset_name,
								l_attr_id,
								l_application_col_name,
								NULL,
							        NULL,
								l_ref6_value,
								NULL,
								NULL,
								NULL,
								NULL,
								sysdate,
								0,
								'N'
							  );

Line: 964

				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_vs_seq_id,
								l_parent_seq_id,
								G_ENTITY_TYPE_VS,
								l_vs_id,
								NULL,
								NULL,
								NULL,
								NULL,
								l_vs_name,
								l_attr_id,
								l_application_col_name,
								NULL,
								NULL,
								NULL,
								NULL,
								NULL,
								NULL,
								NULL,
								sysdate,
								0,
							        'N'
							  );

Line: 1055

SELECT PK1_VALUE,
       PK2_VALUE
FROM EGO_PUB_WS_ENTITIES
WHERE SESSION_ID = l_session_id
AND ENTITY_TYPE = G_ENTITY_TYPE_VS
AND NVL(USER_ENTERED, 'N') = 'N'
AND PARENT_SEQUENCE_ID IS NULL;

Line: 1068

		SELECT entity_id
		INTO l_entity_id
		FROM EGO_PUB_BAT_ENTS_B
		WHERE ENTITY_NAME='Value Set';

Line: 1075

            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 NVL(PK2_VALUE,'pk2_value') = NVL(k.pk2_value,'pk2_value');

Line: 1125

	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');

Line: 1134

	--INSERT INTO EGO.tr_temp values('p_session_id', p_session_id);

Line: 1143

	/*INSERT INTO tr_temp (Session_id, message)
              values (p_session_id, 'Entering: Create_Entities_Structure' );

Line: 1157

	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_AG;

Line: 1169

		/*INSERT INTO tr_temp (Session_id, message)
              values (p_session_id, 'Entering: Explode_Attribute_Group ' );

Line: 1173

		--Explode Attribute Group for all end-valuesets selected

		l_mode := EGO_PUB_WS_UTIL.Get_Char_Param_Value(p_session_id, 'MODE', NULL, NULL);