APPS.MTH_UTIL_PKG dependencies on MTH_EQUIP_HIERARCHY

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

1405:
1406: /* *****************************************************************************
1407: * Procedure :update_equip_hrchy_gid *
1408: * Description :This procedue is used for updating the group_id column in *
1409: * the mth_equip_hierarchy table. The group id will be used to determine the *
1410: * sequence in which a particular record will be processed in the equipment SCD *
1411: * logic. The oldest relationships will have the lowest group id =1 and the new *
1412: * relationships will have higher group id. All the catch all relationships i.e.*
1413: * the relationship with parent = -99999 and effective date = 1-Jan-1900 will *

Line 1449: FROM mth_equip_hierarchy

1445: level_fk_key ,
1446: level_num ,
1447: effective_date effective_date,
1448: group_id
1449: FROM mth_equip_hierarchy
1450: WHERE group_id > 1
1451: /*group_id==1 are catch all rows. */
1452: GROUP BY hierarchy_id,
1453: level_fk_key ,

Line 1463: FROM mth_equip_hierarchy

1459: level_fk_key ,
1460: level_num ,
1461: effective_date ,
1462: parent_fk_key
1463: FROM mth_equip_hierarchy
1464: WHERE group_id IS NULL
1465: ) new_rows
1466: /*new relationships with group id as null */
1467: WHERE old_rows.hierarchy_id = new_rows.hierarchy_id

Line 1493: FROM mth_equip_hierarchy

1489: level_fk_key ,
1490: level_num ,
1491: effective_date effective_date,
1492: group_id
1493: FROM mth_equip_hierarchy
1494: WHERE group_id > 1
1495: /*group_id==1 are catch all rows. */
1496: ) old_rows ,
1497: (SELECT hierarchy_id,

Line 1502: FROM mth_equip_hierarchy

1498: level_fk_key ,
1499: level_num ,
1500: effective_date ,
1501: parent_fk_key
1502: FROM mth_equip_hierarchy
1503: WHERE group_id IS NULL
1504: ) new_rows
1505: /*new relationships with group id as null */
1506: WHERE old_rows.hierarchy_id = new_rows.hierarchy_id

Line 1526: FROM mth_equip_hierarchy

1522: SELECT effective_date,
1523: hierarchy_id ,
1524: level_fk_key ,
1525: level_num
1526: FROM mth_equip_hierarchy
1527: WHERE group_id IS NULL;
1528: BEGIN
1529: FOR l_rows IN cr_conflict_rows
1530: LOOP

Line 1540: UPDATE mth_equip_hierarchy

1536: of the new rows. */
1537: IF(cr_aggr_conflict_rows%FOUND) then
1538: /*set the group id of the new row equal to the group id of the old
1539: row which matches the effective date*/
1540: UPDATE mth_equip_hierarchy
1541: SET group_id = l_rows.group_id
1542: WHERE effective_date = v_new_ed
1543: /*This is the determining condition */
1544: AND hierarchy_id = l_rows.hierarchy_id

Line 1550: UPDATE mth_equip_hierarchy

1546: AND level_num = l_rows.level_num;
1547: END IF;
1548: CLOSE cr_aggr_conflict_rows;
1549: /*Update the odl rows and increment the group id by 1 */
1550: UPDATE mth_equip_hierarchy
1551: SET group_id = l_rows.group_id + 1
1552: WHERE effective_date = l_rows.effective_date
1553: AND hierarchy_id = l_rows.hierarchy_id
1554: AND level_fk_key = l_rows.level_fk_key

Line 1563: FROM mth_equip_hierarchy

1559: conflit rows and non conflict rows*/
1560: /*get the number of new rows remaining to be updated. */
1561: SELECT COUNT(* )
1562: INTO l_new_rows
1563: FROM mth_equip_hierarchy
1564: WHERE group_id IS NULL;
1565: IF l_new_rows > 0 THEN
1566: /*if new rows found */
1567: FOR new_rows IN cr_new_rows

Line 1571: FROM mth_equip_hierarchy

1567: FOR new_rows IN cr_new_rows
1568: LOOP
1569: SELECT MAX(group_id)
1570: INTO l_max_gid
1571: FROM mth_equip_hierarchy
1572: WHERE hierarchy_id = new_rows.hierarchy_id
1573: AND level_fk_key = new_rows.level_fk_key
1574: AND level_num = new_rows.level_num;
1575: /*update the new rows gorup_id column and set it = group_id of old row + 1*/

Line 1576: UPDATE mth_equip_hierarchy

1572: WHERE hierarchy_id = new_rows.hierarchy_id
1573: AND level_fk_key = new_rows.level_fk_key
1574: AND level_num = new_rows.level_num;
1575: /*update the new rows gorup_id column and set it = group_id of old row + 1*/
1576: UPDATE mth_equip_hierarchy
1577: SET group_id = l_max_gid + 1
1578: WHERE hierarchy_id = new_rows.hierarchy_id
1579: AND level_fk_key = new_rows.level_fk_key
1580: AND level_num = new_rows.level_num

Line 1613: FROM mth_equip_hierarchy;

1609: BEGIN
1610: IF minmax = 1 THEN
1611: SELECT MIN(group_id)
1612: INTO v_minmax
1613: FROM mth_equip_hierarchy;
1614: ELSE
1615: SELECT MAX(group_id)
1616: INTO v_minmax
1617: FROM mth_equip_hierarchy;

Line 1617: FROM mth_equip_hierarchy;

1613: FROM mth_equip_hierarchy;
1614: ELSE
1615: SELECT MAX(group_id)
1616: INTO v_minmax
1617: FROM mth_equip_hierarchy;
1618: END IF;
1619:
1620: RETURN v_minmax;
1621: END get_min_max_gid;