eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.MTH_UDA_PKG dependencies on MTH_EXT_ATTR_T_STG

Line 18: SELECT DISTINCT GROUP_ID FROM MTH_EXT_ATTR_T_STG;

14: e_issue_with_data EXCEPTION;
15: e_no_pk_key EXCEPTION;
16:
17: CURSOR c_null_check IS
18: SELECT DISTINCT GROUP_ID FROM MTH_EXT_ATTR_T_STG;
19: -- main body
20: BEGIN
21: NULL; -- allow compilation
22:

Line 41: SELECT COUNT (1) INTO v_temp FROM MTH_EXT_ATTR_T_STG WHERE GROUP_ID = v_row.GROUP_ID

37: */
38:
39: FOR v_row IN c_null_check
40: LOOP
41: SELECT COUNT (1) INTO v_temp FROM MTH_EXT_ATTR_T_STG WHERE GROUP_ID = v_row.GROUP_ID
42: AND DB_COL IS NULL;
43:
44: IF (v_temp > 1) THEN -- Case 1
45: RAISE e_issue_with_data;

Line 52: in MTH_EXT_ATTR_T_STG, where db_col is null. This is

48: END IF;
49: END LOOP;
50:
51: /* Once we have the column name, update those rows
52: in MTH_EXT_ATTR_T_STG, where db_col is null. This is
53: because, we expect only those rows to have db_col as null
54: which consists ATTR_VALUE as the primary key value. For
55: others since, meta data will be configured, db_col should not be
56: null.

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

55: others since, meta data will be configured, db_col should not be
56: null.
57: */
58:
59: v_stmt := 'UPDATE MTH_EXT_ATTR_T_STG SET ATTR_NAME = '||''''||v_pk_column||''''||' WHERE DB_COL IS NULL';
60: mth_util_pkg.log_msg('v_stmt : '||v_stmt,mth_util_pkg.G_DBG_DYN_SQL);
61: EXECUTE IMMEDIATE v_stmt;
62: COMMIT;
63: mth_util_pkg.log_msg('UPDATE_TO_PRIMARY_KEY end', mth_util_pkg.G_DBG_PROC_FUN_END);

Line 211: SELECT STG.ATTR_GROUP_ID,STG.ATTR_NAME, STG.ATTR_VALUE, STG.DB_COL FROM MTH_EXT_ATTR_T_STG STG

207: be filled in for a row. This helps to insert as well as
208: update a row in the EXT table.
209: */
210: CURSOR c_row_iterator(R_ID NUMBER, ATTR_GRP NUMBER) IS
211: SELECT STG.ATTR_GROUP_ID,STG.ATTR_NAME, STG.ATTR_VALUE, STG.DB_COL FROM MTH_EXT_ATTR_T_STG STG
212: WHERE STG.GROUP_ID = R_ID AND STG.ATTR_GROUP_ID = ATTR_GRP;
213:
214: /*
215: This cursor is used to find all the attribute groups ids having

Line 219: SELECT DISTINCT ATTR_GROUP_ID FROM MTH_EXT_ATTR_T_STG WHERE GROUP_ID= GROUP_ID1;

215: This cursor is used to find all the attribute groups ids having
216: the same row ids and process the same.
217: */
218: CURSOR c_row_iterator1(GROUP_ID1 NUMBER) IS
219: SELECT DISTINCT ATTR_GROUP_ID FROM MTH_EXT_ATTR_T_STG WHERE GROUP_ID= GROUP_ID1;
220:
221: /*
222: This cursor first gets all the rows for which DB_COL is null.
223: Essentially, these are going to be the ones which are the primary

Line 228: 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;

224: keys of the table. This helps to locate a particular row and to decide
225: whether we update or insert a new row.
226: */
227: CURSOR c_row_iterator2(GROUP_ID2 NUMBER, AID NUMBER) IS
228: 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;
229:
230: /*This cursor will get all the NAME VALUE pair for which c_unique_key_flag = 'Y' */
231: CURSOR c_unique_key_flag(GROUP_ID3 NUMBER, AID3 NUMBER) IS
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 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';

228: 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;
229:
230: /*This cursor will get all the NAME VALUE pair for which c_unique_key_flag = 'Y' */
231: CURSOR c_unique_key_flag(GROUP_ID3 NUMBER, AID3 NUMBER) IS
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';
233:
234: -- main body
235: BEGIN
236: NULL; -- allow compilation

Line 258: SELECT COUNT(DISTINCT GROUP_ID) INTO v_cnt_rows FROM MTH_EXT_ATTR_T_STG;

254: */
255: v_stmt_no:= 10;
256:
257: /*Changed the following select statement
258: SELECT COUNT(DISTINCT GROUP_ID) INTO v_cnt_rows FROM MTH_EXT_ATTR_T_STG;
259: due to bug 8349873. Due to the above statement, the logic fails when we have discontinous group ids such as 1,3,5 etc.
260: Changing the statement to select max group_id allows complete iteration through
261: discontinous set.
262: */

Line 263: SELECT NVL(MAX(GROUP_ID),0) INTO v_cnt_rows FROM MTH_EXT_ATTR_T_STG; --Added NVL for bug 14465600

259: due to bug 8349873. Due to the above statement, the logic fails when we have discontinous group ids such as 1,3,5 etc.
260: Changing the statement to select max group_id allows complete iteration through
261: discontinous set.
262: */
263: SELECT NVL(MAX(GROUP_ID),0) INTO v_cnt_rows FROM MTH_EXT_ATTR_T_STG; --Added NVL for bug 14465600
264: mth_util_pkg.log_msg('v_cnt_rows = ' || v_cnt_rows , mth_util_pkg.G_DBG_VAR_VAL);
265: /* Loop through each row of data. A row of data is identified as having the
266: same row id.
267: */

Line 302: SELECT COUNT(1) INTO v_cnt_existing FROM MTH_EXT_ATTR_T_STG

298: by helping to choose proper WHERE CLAUSES. This is achieved by
299: using the v_cnt_existing varaiable
300: */
301: v_stmt_no := 30;
302: SELECT COUNT(1) INTO v_cnt_existing FROM MTH_EXT_ATTR_T_STG
303: WHERE DB_COL IS NULL AND
304: ATTR_GROUP_ID = v_attr_group AND
305: GROUP_ID = VAR;
306: mth_util_pkg.log_msg('No of primary key columns = '||v_cnt_existing, mth_util_pkg.G_DBG_VAR_VAL);

Line 346: SELECT COUNT(1) INTO v_cnt_existing FROM MTH_EXT_ATTR_T_STG

342:
343: IF v_mrc = 'Y' THEN
344: mth_util_pkg.log_msg('Attribute group is MULTI ROW',mth_util_pkg.G_DBG_OTH);
345: /* LOGIC FOR multi row attribute groups */
346: SELECT COUNT(1) INTO v_cnt_existing FROM MTH_EXT_ATTR_T_STG
347: WHERE UNIQUE_KEY_FLAG='Y' AND
348: ATTR_GROUP_ID = v_attr_group AND
349: GROUP_ID = VAR;
350: mth_util_pkg.log_msg('No of unique columns = '||v_cnt_existing, mth_util_pkg.G_DBG_VAR_VAL);

Line 930: SELECT DISTINCT GROUP_ID FROM MTH_EXT_ATTR_T_STG;

926:
927:
928:
929: CURSOR c_null_check IS
930: SELECT DISTINCT GROUP_ID FROM MTH_EXT_ATTR_T_STG;
931: -- main body
932: BEGIN
933: --NULL; -- allow compilation
934:

Line 953: SELECT COUNT (1) INTO v_temp FROM MTH_EXT_ATTR_T_STG WHERE GROUP_ID = v_row.GROUP_ID

949: */
950:
951: FOR v_row IN c_null_check
952: LOOP
953: SELECT COUNT (1) INTO v_temp FROM MTH_EXT_ATTR_T_STG WHERE GROUP_ID = v_row.GROUP_ID
954: AND DB_COL IS NULL;
955:
956: IF (v_temp > v_mst_pk_column.count) THEN -- Case 1
957: RAISE e_issue_with_data;

Line 964: in MTH_EXT_ATTR_T_STG, where db_col is null. This is

960: END IF;
961: END LOOP;
962:
963: /* Once we have the column name, update those rows
964: in MTH_EXT_ATTR_T_STG, where db_col is null. This is
965: because, we expect only those rows to have db_col as null
966: which consists ATTR_VALUE as the primary key value. For
967: others since, meta data will be configured, db_col should not be
968: null.

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

971: --The assumption is the csv columns are in the same order as the primary key columns.
972: v_csv_ctr := v_csv_cols.FIRST;
973: For ctr in v_mst_pk_column.FIRST..v_mst_pk_column.LAST
974: LOOP
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) || '''';
976: --DBMS_OUTPUT.PUT_LINE(v_stmt1);
977: -- v_stmt2 := 'UPDATE MTH_EXT_ATTR_T_STG SET ATTR_NAME = '||''''||v_pk_column(ctr)||''''||' WHERE DB_COL IS NULL ' AND ATTR_NAME = 'ENTITY_TYPE';
978: EXECUTE IMMEDIATE v_stmt1;
979: -- EXECUTE IMMEDIATE v_stmt2;

Line 977: -- v_stmt2 := 'UPDATE MTH_EXT_ATTR_T_STG SET ATTR_NAME = '||''''||v_pk_column(ctr)||''''||' WHERE DB_COL IS NULL ' AND ATTR_NAME = 'ENTITY_TYPE';

973: For ctr in v_mst_pk_column.FIRST..v_mst_pk_column.LAST
974: LOOP
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) || '''';
976: --DBMS_OUTPUT.PUT_LINE(v_stmt1);
977: -- v_stmt2 := 'UPDATE MTH_EXT_ATTR_T_STG SET ATTR_NAME = '||''''||v_pk_column(ctr)||''''||' WHERE DB_COL IS NULL ' AND ATTR_NAME = 'ENTITY_TYPE';
978: EXECUTE IMMEDIATE v_stmt1;
979: -- EXECUTE IMMEDIATE v_stmt2;
980: v_csv_ctr := v_csv_cols.NEXT(v_csv_ctr);
981:

Line 1102: SELECT STG.ATTR_GROUP_ID,STG.ATTR_NAME, STG.ATTR_VALUE, STG.DB_COL FROM MTH_EXT_ATTR_T_STG STG

1098: be filled in for a row. This helps to insert as well as
1099: update a row in the EXT table.
1100: */
1101: CURSOR c_row_iterator(R_ID NUMBER, ATTR_GRP NUMBER) IS
1102: SELECT STG.ATTR_GROUP_ID,STG.ATTR_NAME, STG.ATTR_VALUE, STG.DB_COL FROM MTH_EXT_ATTR_T_STG STG
1103: WHERE STG.GROUP_ID = R_ID AND STG.ATTR_GROUP_ID = ATTR_GRP;
1104:
1105: /*
1106: This cursor is used to find all the attribute groups ids having

Line 1110: SELECT DISTINCT ATTR_GROUP_ID FROM MTH_EXT_ATTR_T_STG WHERE GROUP_ID= GROUP_ID1;

1106: This cursor is used to find all the attribute groups ids having
1107: the same row ids and process the same.
1108: */
1109: CURSOR c_row_iterator1(GROUP_ID1 NUMBER) IS
1110: SELECT DISTINCT ATTR_GROUP_ID FROM MTH_EXT_ATTR_T_STG WHERE GROUP_ID= GROUP_ID1;
1111:
1112: /*
1113: This cursor first gets all the rows for which DB_COL is null.
1114: Essentially, these are going to be the ones which are the primary

Line 1119: 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;

1115: keys of the table. This helps to locate a particular row and to decide
1116: whether we update or insert a new row.
1117: */
1118: CURSOR c_row_iterator2(GROUP_ID2 NUMBER, AID NUMBER) IS
1119: 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;
1120:
1121: /*This cursor will get all the NAME VALUE pair for which c_unique_key_flag = 'Y' */
1122: CURSOR c_unique_key_flag(GROUP_ID3 NUMBER, AID3 NUMBER) IS
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 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';

1119: 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;
1120:
1121: /*This cursor will get all the NAME VALUE pair for which c_unique_key_flag = 'Y' */
1122: CURSOR c_unique_key_flag(GROUP_ID3 NUMBER, AID3 NUMBER) IS
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';
1124:
1125: -- main body
1126: BEGIN
1127: NULL; -- allow compilation

Line 1147: SELECT COUNT(DISTINCT GROUP_ID) INTO v_cnt_rows FROM MTH_EXT_ATTR_T_STG;

1143: */
1144: v_stmt_no:= 10;
1145:
1146: /*Changed the following select statement
1147: SELECT COUNT(DISTINCT GROUP_ID) INTO v_cnt_rows FROM MTH_EXT_ATTR_T_STG;
1148: due to bug 8349873. Due to the above statement, the logic fails when we have discontinous group ids such as 1,3,5 etc.
1149: Changing the statement to select max group_id allows complete iteration through
1150: discontinous set.
1151: */

Line 1152: SELECT MAX(GROUP_ID) INTO v_cnt_rows FROM MTH_EXT_ATTR_T_STG;

1148: due to bug 8349873. Due to the above statement, the logic fails when we have discontinous group ids such as 1,3,5 etc.
1149: Changing the statement to select max group_id allows complete iteration through
1150: discontinous set.
1151: */
1152: SELECT MAX(GROUP_ID) INTO v_cnt_rows FROM MTH_EXT_ATTR_T_STG;
1153:
1154: /* Loop through each row of data. A row of data is identified as having the
1155: same row id.
1156: */

Line 1190: SELECT COUNT(1) INTO v_cnt_existing FROM MTH_EXT_ATTR_T_STG

1186: by helping to choose proper WHERE CLAUSES. This is achieved by
1187: using the v_cnt_existing varaiable
1188: */
1189: v_stmt_no := 30;
1190: SELECT COUNT(1) INTO v_cnt_existing FROM MTH_EXT_ATTR_T_STG
1191: WHERE DB_COL IS NULL AND
1192: ATTR_GROUP_ID = v_attr_group AND
1193: GROUP_ID = VAR;
1194: --DBMS_OUTPUT.PUT_LINE('No of primary key columns = '||v_cnt_existing);

Line 1237: SELECT COUNT(1) INTO v_cnt_existing FROM MTH_EXT_ATTR_T_STG

1233:
1234: IF v_mrc = 'Y' THEN
1235: --DBMS_OUTPUT.PUT_LINE('Attribute group is MULTI ROW');
1236: /* LOGIC FOR multi row attribute groups */
1237: SELECT COUNT(1) INTO v_cnt_existing FROM MTH_EXT_ATTR_T_STG
1238: WHERE UNIQUE_KEY_FLAG='Y' AND
1239: ATTR_GROUP_ID = v_attr_group AND
1240: GROUP_ID = VAR;
1241: -- DBMS_OUTPUT.PUT_LINE('No of unique columns = '||v_cnt_existing);