APPS.MTH_UDA_PKG SQL Statements

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

Line: 4

PROCEDURE UPDATE_TO_PRIMARY_KEY(P_ENTITY IN VARCHAR2) IS
--initialize variables here
v_entity_code NUMBER;

Line: 18

SELECT DISTINCT GROUP_ID FROM MTH_EXT_ATTR_T_STG;

Line: 39

	SELECT COUNT (1) INTO v_temp FROM MTH_EXT_ATTR_T_STG WHERE GROUP_ID = v_row.GROUP_ID
	AND DB_COL IS NULL;

Line: 49

/* Once we have the column name, update those rows
in MTH_EXT_ATTR_T_STG, where db_col is null. This is
because, we expect only those rows to have db_col as null
which consists ATTR_VALUE as the primary key value. For
others since, meta data will be configured, db_col should not be
null.
*/

v_stmt := 'UPDATE MTH_EXT_ATTR_T_STG SET ATTR_NAME = '||''''||v_pk_column||''''||' WHERE DB_COL IS NULL';

Line: 78

l_updated_by NUMBER := 15;

Line: 79

l_last_update_login NUMBER := 15;

Line: 85

Check whether we need to insert or update these values
by checking p_if_row_exists. if this is 0, we are inserting.
*/

IF (p_if_row_exists = 0) THEN
/* Row does not exists, assign values to creation_date
and created_by*/
v_stmt := 'UPDATE '||p_ext_tbl_name||' SET LAST_UPDATE_DATE = '||''''||SYSDATE||''''||', LAST_UPDATED_BY = '||l_updated_by||', LAST_UPDATE_LOGIN = ';

Line: 93

v_stmt := v_stmt||l_last_update_login||', CREATED_BY = '||l_updated_by||', CREATION_DATE = '||''''||SYSDATE||''''||' WHERE EXTENSION_ID = '||p_extension_id;

Line: 97

v_stmt := 'UPDATE '||p_ext_tbl_name||' SET LAST_UPDATE_DATE = '||''''||SYSDATE||''''||', LAST_UPDATED_BY = '||l_updated_by||', LAST_UPDATE_LOGIN = '||l_last_update_login||' WHERE EXTENSION_ID = '||p_extension_id;

Line: 110

            RAISE_APPLICATION_ERROR(-20001,' in the procedure to update who columns');

Line: 147

Now, check if it was already existing, if not, we will insert a new row,
else call the upload who procedure to update who columns
*/

IF (p_if_row_exists = 0) THEN
 -- INSERT A NEW ROW
 --DBMS_OUTPUT.PUT_LINE('inserting the rows');

Line: 154

 v_stmt := 'INSERT INTO '||v_tname_tl||'(EXTENSION_ID, ATTR_GROUP_ID, '||v_pk_column||',
 SOURCE_LANG, LANGUAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE)
 SELECT EXTENSION_ID, ATTR_GROUP_ID, '||v_pk_column||', ''US''SOURCE_LANG, ''US'' LANGUAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY,LAST_UPDATE_LOGIN, CREATED_BY,CREATION_DATE FROM '||v_tname_b||' WHERE EXTENSION_ID = '||p_extId;

Line: 200

be filled in for a row. This helps to insert as well as
update a row in the EXT table.
*/
CURSOR c_row_iterator(R_ID NUMBER, ATTR_GRP NUMBER) IS
SELECT STG.ATTR_GROUP_ID,STG.ATTR_NAME, STG.ATTR_VALUE, STG.DB_COL FROM MTH_EXT_ATTR_T_STG STG
WHERE STG.GROUP_ID = R_ID AND STG.ATTR_GROUP_ID = ATTR_GRP;

Line: 212

SELECT DISTINCT ATTR_GROUP_ID FROM MTH_EXT_ATTR_T_STG WHERE GROUP_ID= GROUP_ID1;

Line: 218

whether we update or insert a new row.
*/
CURSOR c_row_iterator2(GROUP_ID2 NUMBER, AID NUMBER) IS
SELECT ATTR_NAME, ATTR_VALUE FROM MTH_EXT_ATTR_T_STG WHERE GROUP_ID = GROUP_ID2 AND DB_COL IS NULL AND ATTR_GROUP_ID = AID;

Line: 225

SELECT DB_COL, ATTR_VALUE FROM MTH_EXT_ATTR_T_STG WHERE GROUP_ID = GROUP_ID3 AND ATTR_GROUP_ID = AID3 AND UNIQUE_KEY_FLAG='Y';

Line: 231

 MTH_UDA_PKG.Update_To_Primary_Key(v_entity);

Line: 243

Select the different row ids present, each different row id refers to the data for a single
row. It is possible to have one to many relationship between row id and attribute group.
*/
v_stmt_no:= 10;

Line: 247

SELECT COUNT(DISTINCT GROUP_ID) INTO v_cnt_rows FROM MTH_EXT_ATTR_T_STG;

Line: 271

		v_stmt_var := 'SELECT EXTENSION_ID FROM '||v_tname||' WHERE ATTR_GROUP_ID ='||v_attr_group;

Line: 277

		v_stmt := 'SELECT COUNT(1) FROM '||v_tname||' WHERE ATTR_GROUP_ID ='||v_attr_group;

Line: 285

		SELECT COUNT(1) INTO v_cnt_existing FROM MTH_EXT_ATTR_T_STG
		WHERE DB_COL IS NULL AND
		ATTR_GROUP_ID = v_attr_group AND
		GROUP_ID = VAR;

Line: 295

		whether we update or insert a new row.
		*/
		--DBMS_OUTPUT.PUT_LINE('Preparing query using pkey columns');

Line: 326

		SELECT MULTI_ROW_CODE INTO v_mrc FROM EGO_ATTR_GROUPS_V
		WHERE ATTR_GROUP_ID = v_attr_group;

Line: 332

			SELECT COUNT(1) INTO v_cnt_existing FROM MTH_EXT_ATTR_T_STG
			WHERE UNIQUE_KEY_FLAG='Y' AND
			ATTR_GROUP_ID = v_attr_group AND
			GROUP_ID = VAR;

Line: 368

	--	DBMS_OUTPUT.PUT_LINE('The updated statements after unique key check are ');

Line: 376

		of pkeys are not present. So proceed with inserting a
		new surrogate key value
		*/
		EXECUTE IMMEDIATE v_stmt INTO v_if_row_exists ;

Line: 383

			--DBMS_OUTPUT.PUT_LINE('INSERT THE NEW ROW');

Line: 387

			v_stmt := 'SELECT EGO_EXTFWK_S.NEXTVAL FROM DUAL';

Line: 390

			v_stmt := 'INSERT INTO '||v_tname||' (EXTENSION_ID, ATTR_GROUP_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE) VALUES (:1, :2, '||''''||SYSDATE||''''||', -1, -1,'||''''||SYSDATE||''''||'  )';

Line: 399

		--	DBMS_OUTPUT.PUT_LINE('UPDATE THE DATA');

Line: 407

		to insert/update in the EXT Table
		*/
		--DBMS_OUTPUT.PUT_LINE('Iterate over all the columns to insert/update');

Line: 422

			--DBMS_OUTPUT.PUT_LINE('The column name to be updated/inserted '||v_col_name);

Line: 433

				v_stmt := 'UPDATE '||v_tname||' SET '||v_col_name||' = '||'TO_DATE('||''''||v_date_val||''''||',''MM/DD/YYYY HH24:MI:SS'')'||' WHERE EXTENSION_ID = '||v_extId;

Line: 437

				v_stmt := 'UPDATE '||v_tname||' SET '||v_col_name||' = '||''''||v_col_val||''''||' WHERE EXTENSION_ID = '||v_extId;

Line: 446

		END LOOP; -- Completing insertion or updating a single row

Line: 448

	call procedure to update standard who columns
	*/
	--DBMS_OUTPUT.PUT_LINE('calling who procedure');

Line: 454

	Call the procedure to update TL Table
	*/
	MTH_UDA_PKG.NTB_UploadTL(v_entity,v_extId,v_if_row_exists);

Line: 616

v_last_update_date DATE; -- Standard WHO column

Line: 617

v_last_update_system_id NUMBER; -- Standard WHO column

Line: 632

    SELECT SYSDATE INTO v_last_update_date FROM	DUAL;

Line: 636

  SELECT MTH_UTIL_PKG.MTH_UA_GET_VAL() INTO v_last_update_system_id FROM DUAL;

Line: 641

  SELECT COUNT(FACT_TABLE) INTO v_count
  FROM MTH_RUN_LOG
  WHERE FACT_TABLE = v_fact_table;

Line: 651

  SELECT TO_DATE INTO v_to_date
  FROM MTH_RUN_LOG
  WHERE FACT_TABLE = v_fact_table;

Line: 667

    v_stmt := 'UPDATE MTH_RUN_LOG SET FROM_DATE = TO_DATE, LAST_UPDATE_DATE = :1, LAST_UPDATE_SYSTEM_ID =:2 WHERE
            FACT_TABLE =:3';

Line: 673

    EXECUTE IMMEDIATE v_stmt USING v_last_update_date, v_last_update_system_id, v_fact_table;

Line: 678

    v_stmt := 'UPDATE MTH_RUN_LOG SET TO_DATE = NULL WHERE
            FACT_TABLE =:1';

Line: 706

CREATION_DATE will be same as v_last_update_date if the load is run for the first time,
else if the load is being run again, CREATION_DATE would already be populated, so there is no
need for this variable
*/
v_last_update_date DATE; -- Standard WHO column

Line: 712

CREATION_SYSTEM_ID will be same as LAST_UPDATE_SYSTEM_ID if the load is run for the first time,
else if the load is being run again, CREATION_SYSTEM_ID would already be populated, so there is no
need for this variable
*/
v_last_update_system_id NUMBER; -- Standard WHO column

Line: 732

  SELECT SYSDATE INTO v_to_date FROM DUAL;

Line: 734

  v_last_update_date := v_to_date;

Line: 738

  SELECT MTH_UTIL_PKG.MTH_UA_GET_VAL() INTO v_last_update_system_id FROM DUAL;

Line: 742

  SELECT COUNT(FACT_TABLE) INTO v_cnt
  FROM MTH_RUN_LOG
  WHERE FACT_TABLE = v_fact_table;

Line: 752

    v_stmt := 'INSERT INTO MTH_RUN_LOG (FACT_TABLE, FROM_DATE, TO_DATE, CREATION_DATE,
    LAST_UPDATE_DATE, CREATION_SYSTEM_ID, LAST_UPDATE_SYSTEM_ID) VALUES (:1, :2, :3, :4, :5, :6, :7)';

Line: 756

    EXECUTE IMMEDIATE v_stmt USING v_fact_table, v_from_date, v_to_date, v_last_update_date,
    v_last_update_date, v_last_update_system_id, v_last_update_system_id;

Line: 763

    v_stmt := 'UPDATE MTH_RUN_LOG SET TO_DATE = :1, LAST_UPDATE_DATE = :2, LAST_UPDATE_SYSTEM_ID =:3 WHERE
            FACT_TABLE =:4';

Line: 767

    EXECUTE IMMEDIATE v_stmt USING v_to_date, v_last_update_date, v_last_update_system_id, v_fact_table;

Line: 786

SELECT DISTINCT DB_COL FROM MTH_TAG_READINGS_T_STG;

Line: 798

		SELECT * FROM MTH_TAG_READINGS_T_STG,(SELECT NVL(FND_GLOBAL.User_Id,-1)l_updated_by,NVL(FND_GLOBAL.Login_Id,-1)l_last_update_login FROM DUAL )D
		WHERE DB_COL = '||''''||v_colname||''''||') TS
		ON (';

Line: 805

		UPDATE
		SET ED.'||v_colname||' = TS.TAG_DATA,
		ED.LAST_UPDATE_DATE = ''''||SYSDATE||'''',
		ED.LAST_UPDATED_BY = TS.l_updated_by,';

Line: 810

		v_stmt := v_stmt||'ED.LAST_UPDATE_LOGIN = TS.l_last_update_login
		WHEN NOT MATCHED THEN
		INSERT ('||v_colname||',EXTENSION_ID, EQUIPMENT_PK_KEY,WORKORDER_FK_KEY,SEGMENT_FK_KEY,SHIFT_WORKDAY_FK_KEY, ITEM_FK_KEY, READ_TIME, ATTR_GROUP_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,';

Line: 815

v_stmt||'LAST_UPDATE_LOGIN,CREATED_BY,CREATION_DATE,RECIPE_NUM,RECIPE_VERSION)
		VALUES (TS.TAG_DATA,EGO_EXTFWK_S.NEXTVAL, TS.EQUIPMENT_FK_KEY, TS.WORKORDER_FK_KEY, TS.SEGMENT_FK_KEY,TS.SHIFT_WORKDAY_FK_KEY,TS.ITEM_FK_KEY, TS.READ_TIME,';

Line: 818

		v_stmt := v_stmt||'TS.ATTR_GROUP_ID,'||''''||SYSDATE||''''||',TS.l_updated_by,TS.l_last_update_login,TS.l_updated_by,'||''''||SYSDATE||''''||',TS.RECIPE_NUM, TS.RECIPE_VERSION)';

Line: 831

		RAISE_APPLICATION_ERROR(-20008,'The Tag Data you are tyring to insert is of Character Data Type. A number is expected instead.');