APPS.MTH_UTIL_PKG dependencies on MTH_EQUIP_HIERARCHY

Line 1243: * the mth_equip_hierarchy table. The group id will be used to determine the *

1239:
1240: /* *****************************************************************************
1241: * Procedure :update_equip_hrchy_gid *
1242: * Description :This procedue is used for updating the group_id column in *
1243: * the mth_equip_hierarchy table. The group id will be used to determine the *
1244: * sequence in which a particular record will be processed in the equipment SCD *
1245: * logic. The oldest relationships will have the lowest group id =1 and the new *
1246: * relationships will have higher group id. All the catch all relationships i.e.*
1247: * the relationship with parent = -99999 and effective date = 1-Jan-1900 will *

Line 1283: FROM mth_equip_hierarchy

1279: level_fk_key ,
1280: level_num ,
1281: effective_date effective_date,
1282: group_id
1283: FROM mth_equip_hierarchy
1284: WHERE group_id > 1
1285: /*group_id==1 are catch all rows. */
1286: GROUP BY hierarchy_id,
1287: level_fk_key ,

Line 1297: FROM mth_equip_hierarchy

1293: level_fk_key ,
1294: level_num ,
1295: effective_date ,
1296: parent_fk_key
1297: FROM mth_equip_hierarchy
1298: WHERE group_id IS NULL
1299: ) new_rows
1300: /*new relationships with group id as null */
1301: WHERE old_rows.hierarchy_id = new_rows.hierarchy_id

Line 1327: FROM mth_equip_hierarchy

1323: level_fk_key ,
1324: level_num ,
1325: effective_date effective_date,
1326: group_id
1327: FROM mth_equip_hierarchy
1328: WHERE group_id > 1
1329: /*group_id==1 are catch all rows. */
1330: ) old_rows ,
1331: (SELECT hierarchy_id,

Line 1336: FROM mth_equip_hierarchy

1332: level_fk_key ,
1333: level_num ,
1334: effective_date ,
1335: parent_fk_key
1336: FROM mth_equip_hierarchy
1337: WHERE group_id IS NULL
1338: ) new_rows
1339: /*new relationships with group id as null */
1340: WHERE old_rows.hierarchy_id = new_rows.hierarchy_id

Line 1360: FROM mth_equip_hierarchy

1356: SELECT effective_date,
1357: hierarchy_id ,
1358: level_fk_key ,
1359: level_num
1360: FROM mth_equip_hierarchy
1361: WHERE group_id IS NULL;
1362: BEGIN
1363: FOR l_rows IN cr_conflict_rows
1364: LOOP

Line 1374: UPDATE mth_equip_hierarchy

1370: of the new rows. */
1371: IF(cr_aggr_conflict_rows%FOUND) then
1372: /*set the group id of the new row equal to the group id of the old
1373: row which matches the effective date*/
1374: UPDATE mth_equip_hierarchy
1375: SET group_id = l_rows.group_id
1376: WHERE effective_date = v_new_ed
1377: /*This is the determining condition */
1378: AND hierarchy_id = l_rows.hierarchy_id

Line 1384: UPDATE mth_equip_hierarchy

1380: AND level_num = l_rows.level_num;
1381: END IF;
1382: CLOSE cr_aggr_conflict_rows;
1383: /*Update the odl rows and increment the group id by 1 */
1384: UPDATE mth_equip_hierarchy
1385: SET group_id = l_rows.group_id + 1
1386: WHERE effective_date = l_rows.effective_date
1387: AND hierarchy_id = l_rows.hierarchy_id
1388: AND level_fk_key = l_rows.level_fk_key

Line 1397: FROM mth_equip_hierarchy

1393: conflit rows and non conflict rows*/
1394: /*get the number of new rows remaining to be updated. */
1395: SELECT COUNT(* )
1396: INTO l_new_rows
1397: FROM mth_equip_hierarchy
1398: WHERE group_id IS NULL;
1399: IF l_new_rows > 0 THEN
1400: /*if new rows found */
1401: FOR new_rows IN cr_new_rows

Line 1405: FROM mth_equip_hierarchy

1401: FOR new_rows IN cr_new_rows
1402: LOOP
1403: SELECT MAX(group_id)
1404: INTO l_max_gid
1405: FROM mth_equip_hierarchy
1406: WHERE hierarchy_id = new_rows.hierarchy_id
1407: AND level_fk_key = new_rows.level_fk_key
1408: AND level_num = new_rows.level_num;
1409: /*update the new rows gorup_id column and set it = group_id of old row + 1*/

Line 1410: UPDATE mth_equip_hierarchy

1406: WHERE hierarchy_id = new_rows.hierarchy_id
1407: AND level_fk_key = new_rows.level_fk_key
1408: AND level_num = new_rows.level_num;
1409: /*update the new rows gorup_id column and set it = group_id of old row + 1*/
1410: UPDATE mth_equip_hierarchy
1411: SET group_id = l_max_gid + 1
1412: WHERE hierarchy_id = new_rows.hierarchy_id
1413: AND level_fk_key = new_rows.level_fk_key
1414: AND level_num = new_rows.level_num

Line 1447: FROM mth_equip_hierarchy;

1443: BEGIN
1444: IF minmax = 1 THEN
1445: SELECT MIN(group_id)
1446: INTO v_minmax
1447: FROM mth_equip_hierarchy;
1448: ELSE
1449: SELECT MAX(group_id)
1450: INTO v_minmax
1451: FROM mth_equip_hierarchy;

Line 1451: FROM mth_equip_hierarchy;

1447: FROM mth_equip_hierarchy;
1448: ELSE
1449: SELECT MAX(group_id)
1450: INTO v_minmax
1451: FROM mth_equip_hierarchy;
1452: END IF;
1453:
1454: RETURN v_minmax;
1455: END get_min_max_gid;