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: 23

    mth_util_pkg.log_msg('UPDATE_TO_PRIMARY_KEY start', mth_util_pkg.G_DBG_PROC_FUN_START);

Line: 41

	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: 51

/* 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: 63

mth_util_pkg.log_msg('UPDATE_TO_PRIMARY_KEY end', mth_util_pkg.G_DBG_PROC_FUN_END);

Line: 67

    mth_util_pkg.log_msg('Exception in UPDATE_TO_PRIMARY_KEY', mth_util_pkg.G_DBG_EXCEPTION);

Line: 70

    mth_util_pkg.log_msg('Exception in UPDATE_TO_PRIMARY_KEY', mth_util_pkg.G_DBG_EXCEPTION);

Line: 73

    mth_util_pkg.log_msg('Exception in UPDATE_TO_PRIMARY_KEY', mth_util_pkg.G_DBG_EXCEPTION);

Line: 77

	mth_util_pkg.log_msg('Exception in UPDATE_TO_PRIMARY_KEY', mth_util_pkg.G_DBG_EXCEPTION);

Line: 85

l_updated_by NUMBER := 15;

Line: 86

l_last_update_login NUMBER := 15;

Line: 92

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: 100

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

Line: 104

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: 117

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

Line: 154

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: 161

 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: 207

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: 219

SELECT DISTINCT ATTR_GROUP_ID FROM MTH_EXT_ATTR_T_STG WHERE GROUP_ID= GROUP_ID1;

Line: 225

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: 232

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: 240

 MTH_UDA_PKG.Update_To_Primary_Key(v_entity);

Line: 252

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: 257

/*Changed the following select statement
SELECT COUNT(DISTINCT GROUP_ID) INTO v_cnt_rows FROM MTH_EXT_ATTR_T_STG;

Line: 260

Changing the statement to select max group_id allows complete iteration through
discontinous set.
*/
SELECT NVL(MAX(GROUP_ID),0) INTO v_cnt_rows FROM MTH_EXT_ATTR_T_STG; --Added NVL for bug 14465600

Line: 288

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

Line: 294

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

Line: 302

		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: 312

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

Line: 340

		SELECT MULTI_ROW_CODE INTO v_mrc FROM EGO_ATTR_GROUPS_V
		WHERE ATTR_GROUP_ID = v_attr_group;

Line: 346

			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: 390

		of pkeys are not present. So proceed with inserting a
		new surrogate key value
		*/
        mth_util_pkg.log_msg(v_stmt,mth_util_pkg.G_DBG_DYN_SQL);

Line: 398

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

Line: 402

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

Line: 405

			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: 415

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

Line: 424

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

Line: 439

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

Line: 450

				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: 454

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

Line: 463

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

Line: 466

		--updated
		COMMIT;

Line: 472

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

Line: 478

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

Line: 657

v_last_update_date DATE; -- Standard WHO column

Line: 658

v_last_update_system_id NUMBER; -- Standard WHO column

Line: 673

    SELECT SYSDATE INTO v_last_update_date FROM	DUAL;

Line: 677

  SELECT MTH_UTIL_PKG.MTH_UA_GET_VAL() INTO v_last_update_system_id FROM DUAL;

Line: 682

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

Line: 692

  SELECT TO_DATE INTO v_to_date
  FROM MTH_RUN_LOG
  WHERE FACT_TABLE = v_fact_table;

Line: 708

    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: 714

    EXECUTE IMMEDIATE v_stmt USING v_last_update_date, v_last_update_system_id, v_fact_table;

Line: 719

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

Line: 747

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: 753

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: 773

  SELECT SYSDATE INTO v_to_date FROM DUAL;

Line: 775

  v_last_update_date := v_to_date;

Line: 779

  SELECT MTH_UTIL_PKG.MTH_UA_GET_VAL() INTO v_last_update_system_id FROM DUAL;

Line: 783

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

Line: 793

    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: 797

    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: 804

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

Line: 808

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

Line: 827

SELECT DISTINCT DB_COL FROM MTH_TAG_READINGS_T_STG;

Line: 839

		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: 846

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

Line: 851

		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, HOUR_FK_KEY, ITEM_FK_KEY, READ_TIME, ATTR_GROUP_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,';

Line: 856

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.HOUR_FK_KEY, TS.ITEM_FK_KEY, TS.READ_TIME,';

Line: 859

		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: 872

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

Line: 908

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

Line: 930

SELECT DISTINCT GROUP_ID FROM MTH_EXT_ATTR_T_STG;

Line: 953

	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: 963

/* 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.
*/

--The assumption is the csv columns are in the same order as the primary key columns.
v_csv_ctr := v_csv_cols.FIRST;

Line: 975

	v_stmt1 := 'UPDATE MTH_EXT_ATTR_T_STG SET ATTR_NAME = '||''''||v_mst_pk_column(ctr)||''''||'  WHERE DB_COL IS NULL AND ATTR_NAME = ' || ''''|| v_csv_cols(v_csv_ctr) || '''';

Line: 995

END;    -- End of UPDATE_TO_COMPOSITE_PRIMARY_KEY;

Line: 1037

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: 1050

  v_stmt := 'INSERT INTO '||v_tname_tl||'(EXTENSION_ID, ATTR_GROUP_ID, ' || v_concat_pk_key || 'SOURCE_LANG, LANGUAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE) SELECT EXTENSION_ID, ATTR_GROUP_ID, ';

Line: 1051

 v_stmt := v_stmt ||v_concat_pk_key || ' ''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: 1098

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: 1110

SELECT DISTINCT ATTR_GROUP_ID FROM MTH_EXT_ATTR_T_STG WHERE GROUP_ID= GROUP_ID1;

Line: 1116

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: 1123

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: 1129

 MTH_UDA_PKG.UPDATE_COMPOSITE_PRIMARY_KEY(v_entity);

Line: 1141

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: 1146

/*Changed the following select statement
SELECT COUNT(DISTINCT GROUP_ID) INTO v_cnt_rows FROM MTH_EXT_ATTR_T_STG;

Line: 1149

Changing the statement to select max group_id allows complete iteration through
discontinous set.
*/
SELECT MAX(GROUP_ID) INTO v_cnt_rows FROM MTH_EXT_ATTR_T_STG;

Line: 1176

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

Line: 1182

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

Line: 1190

		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: 1200

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

Line: 1231

		SELECT MULTI_ROW_CODE INTO v_mrc FROM EGO_ATTR_GROUPS_V
		WHERE ATTR_GROUP_ID = v_attr_group;

Line: 1237

			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: 1273

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

Line: 1281

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

Line: 1288

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

Line: 1292

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

Line: 1295

			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: 1304

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

Line: 1312

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

Line: 1327

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

Line: 1338

				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: 1342

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

Line: 1350

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

Line: 1353

		--updated
		COMMIT;

Line: 1356

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

Line: 1362

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