[Home] [Help]
500: AND identifier_subtype = p_child_identifier_subtype
501: AND language_code = p_child_language
502: AND UPPER(identifier_value) = UPPER(p_child_name)
503: AND geography_id IN (SELECT object_id
504: FROM hz_relationships
505: WHERE subject_id = p_parent_id
506: AND object_type = p_child_type
507: AND status = 'A'
508: AND relationship_type = 'MASTER_REF');
544: AND identifier_subtype = p_child_identifier_subtype
545: AND language_code = p_child_language
546: AND identifier_value = UPPER(p_child_code)
547: AND geography_id IN (SELECT object_id
548: FROM hz_relationships
549: WHERE subject_id = p_parent_id
550: AND object_type = p_child_type
551: AND status = 'A'
552: AND relationship_type = 'MASTER_REF');
813: ----dbms_output.put_line('relationship_id is '||to_char(x_relationship_id));
814:
815: -- check whether this geography_id has multiple parents
816: SELECT count(subject_id) INTO l_parent_count
817: FROM HZ_RELATIONSHIPS
818: WHERE object_id = p_master_relation_rec.geography_id
819: AND object_type=l_geography_type
820: AND object_table_name='HZ_GEOGRAPHIES'
821: AND subject_table_name = 'HZ_GEOGRAPHIES'
939:
940: BEGIN
941: -- validate relationship_id
942: SELECT subject_id,object_id,relationship_type into l_parent_geography_id,l_geography_id,l_relationship_type
943: FROM hz_relationships
944: WHERE relationship_id = p_relationship_id
945: AND relationship_code = 'PARENT_OF';
946:
947: EXCEPTION WHEN NO_DATA_FOUND THEN
995:
996: BEGIN
997: -- check whether there exists atleast one parent for this child before end dating this relation
998: SELECT count(subject_id) INTO l_parent_count
999: FROM HZ_RELATIONSHIPS
1000: WHERE object_id = l_geography_id
1001: AND object_type=l_geography_type
1002: AND object_table_name='HZ_GEOGRAPHIES'
1003: AND subject_table_name = 'HZ_GEOGRAPHIES'
1144: l_geo_element_id VARCHAR2(30);
1145: l_language_code VARCHAR2(4);
1146: CURSOR c_get_all_parents IS
1147: SELECT subject_id
1148: FROM hz_relationships
1149: WHERE object_id = p_geo_identifier_rec.geography_id
1150: AND relationship_type = 'MASTER_REF'
1151: AND status = 'A';
1152: l_get_all_parents c_get_all_parents%ROWTYPE;
1474: l_name_updated VARCHAR2(1);
1475:
1476: CURSOR c_get_all_parents IS
1477: SELECT subject_id
1478: FROM hz_relationships
1479: WHERE object_id = p_geo_identifier_rec.geography_id
1480: AND object_table_name = 'HZ_GEOGRAPHIES'
1481: AND relationship_type = 'MASTER_REF'
1482: AND status = 'A';
1607: AND identifier_subtype = l_new_geo_subtype
1608: AND language_code = p_geo_identifier_rec.language_code
1609: AND UPPER(identifier_value) = UPPER(l_new_geo_value)
1610: AND geography_id IN (SELECT object_id
1611: FROM hz_relationships
1612: WHERE subject_id = l_get_all_parents.subject_id
1613: AND object_type = l_geography_type
1614: AND status = 'A'
1615: AND relationship_type = 'MASTER_REF')
1636: AND identifier_subtype = l_new_geo_subtype
1637: AND language_code = p_geo_identifier_rec.language_code
1638: AND identifier_value = UPPER(l_new_geo_value)
1639: AND geography_id IN (SELECT object_id
1640: FROM hz_relationships
1641: WHERE subject_id = l_get_all_parents.subject_id
1642: AND object_type = l_geography_type
1643: AND status = 'A'
1644: AND relationship_type = 'MASTER_REF')
2089: l_count NUMBER;
2090: l_relationship_rec HZ_RELATIONSHIP_V2PUB.RELATIONSHIP_REC_TYPE;
2091: CURSOR c_get_all_relationships IS
2092: SELECT distinct relationship_id,object_version_number
2093: FROM HZ_RELATIONSHIPS
2094: WHERE (subject_id = p_geography_id
2095: OR object_id = p_geography_id)
2096: AND relationship_type= l_geography_use
2097: AND l_geography_use = 'MASTER_REF' --Bug5265511
2290: x_msg_count => x_msg_count,
2291: x_msg_data => x_msg_data
2292: );
2293:
2294: /* update hz_relationships
2295: set status = l_status,
2296: end_date = p_end_date
2297: WHERE relationship_id=l_get_all_relationships.relationship_id;*/
2298:
2410: AND id.geography_use = 'MASTER_REF'
2411: AND id.identifier_type = 'NAME'
2412: AND id.identifier_subtype = 'STANDARD_NAME'
2413: AND EXISTS ( SELECT '1'
2414: FROM hz_relationships rel
2415: WHERE rel.subject_id = l_geography_range_rec.master_ref_geography_id
2416: AND rel.object_id = id.geography_id
2417: AND rel.object_type = l_master_geography_rec.geography_type
2418: AND rel.status = 'A'
2794: -- on 29-Aug-2005 by BAIANAND for Bug 3955631. This will relax the
2795: -- creation of geographies withing zone type. Now it can create multiple geographies
2796: -- within same zone type but not same zone.
2797: SELECT count(*) INTO l_count
2798: FROM hz_relationships
2799: WHERE relationship_type = l_geography_use
2800: -- AND subject_type = l_zone_type
2801: AND subject_id = p_geography_id
2802: AND object_id = p_zone_relation_tbl(i).included_geography_id
2815:
2816: -- included_geography_id must be unique within a geography_id
2817:
2818: SELECT count(*) INTO l_count
2819: FROM hz_relationships
2820: WHERE relationship_type=l_geography_use
2821: AND subject_type=l_zone_type
2822: AND subject_id = p_geography_id
2823: AND object_id = p_zone_relation_tbl(i).included_geography_id