[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'
934:
935: BEGIN
936: -- validate relationship_id
937: SELECT subject_id,object_id,relationship_type into l_parent_geography_id,l_geography_id,l_relationship_type
938: FROM hz_relationships
939: WHERE relationship_id = p_relationship_id
940: AND relationship_code = 'PARENT_OF';
941:
942: EXCEPTION WHEN NO_DATA_FOUND THEN
990:
991: BEGIN
992: -- check whether there exists atleast one parent for this child before end dating this relation
993: SELECT count(subject_id) INTO l_parent_count
994: FROM HZ_RELATIONSHIPS
995: WHERE object_id = l_geography_id
996: AND object_type=l_geography_type
997: AND object_table_name='HZ_GEOGRAPHIES'
998: AND subject_table_name = 'HZ_GEOGRAPHIES'
1139: l_geo_element_id VARCHAR2(30);
1140: l_language_code VARCHAR2(4);
1141: CURSOR c_get_all_parents IS
1142: SELECT subject_id
1143: FROM hz_relationships
1144: WHERE object_id = p_geo_identifier_rec.geography_id
1145: AND relationship_type = 'MASTER_REF'
1146: AND status = 'A';
1147: l_get_all_parents c_get_all_parents%ROWTYPE;
1465: l_name_updated VARCHAR2(1);
1466:
1467: CURSOR c_get_all_parents IS
1468: SELECT subject_id
1469: FROM hz_relationships
1470: WHERE object_id = p_geo_identifier_rec.geography_id
1471: AND object_table_name = 'HZ_GEOGRAPHIES'
1472: AND relationship_type = 'MASTER_REF'
1473: AND status = 'A';
1598: AND identifier_subtype = l_new_geo_subtype
1599: AND language_code = p_geo_identifier_rec.language_code
1600: AND UPPER(identifier_value) = UPPER(l_new_geo_value)
1601: AND geography_id IN (SELECT object_id
1602: FROM hz_relationships
1603: WHERE subject_id = l_get_all_parents.subject_id
1604: AND object_type = l_geography_type
1605: AND status = 'A'
1606: AND relationship_type = 'MASTER_REF')
1627: AND identifier_subtype = l_new_geo_subtype
1628: AND language_code = p_geo_identifier_rec.language_code
1629: AND identifier_value = UPPER(l_new_geo_value)
1630: AND geography_id IN (SELECT object_id
1631: FROM hz_relationships
1632: WHERE subject_id = l_get_all_parents.subject_id
1633: AND object_type = l_geography_type
1634: AND status = 'A'
1635: AND relationship_type = 'MASTER_REF')
2076: l_count NUMBER;
2077: l_relationship_rec HZ_RELATIONSHIP_V2PUB.RELATIONSHIP_REC_TYPE;
2078: CURSOR c_get_all_relationships IS
2079: SELECT distinct relationship_id,object_version_number
2080: FROM HZ_RELATIONSHIPS
2081: WHERE (subject_id = p_geography_id
2082: OR object_id = p_geography_id)
2083: AND relationship_type= l_geography_use
2084: AND l_geography_use = 'MASTER_REF' --Bug5265511
2277: x_msg_count => x_msg_count,
2278: x_msg_data => x_msg_data
2279: );
2280:
2281: /* update hz_relationships
2282: set status = l_status,
2283: end_date = p_end_date
2284: WHERE relationship_id=l_get_all_relationships.relationship_id;*/
2285:
2397: AND id.geography_use = 'MASTER_REF'
2398: AND id.identifier_type = 'NAME'
2399: AND id.identifier_subtype = 'STANDARD_NAME'
2400: AND EXISTS ( SELECT '1'
2401: FROM hz_relationships rel
2402: WHERE rel.subject_id = l_geography_range_rec.master_ref_geography_id
2403: AND rel.object_id = id.geography_id
2404: AND rel.object_type = l_master_geography_rec.geography_type
2405: AND rel.status = 'A'
2781: -- on 29-Aug-2005 by BAIANAND for Bug 3955631. This will relax the
2782: -- creation of geographies withing zone type. Now it can create multiple geographies
2783: -- within same zone type but not same zone.
2784: SELECT count(*) INTO l_count
2785: FROM hz_relationships
2786: WHERE relationship_type = l_geography_use
2787: -- AND subject_type = l_zone_type
2788: AND subject_id = p_geography_id
2789: AND object_id = p_zone_relation_tbl(i).included_geography_id
2802:
2803: -- included_geography_id must be unique within a geography_id
2804:
2805: SELECT count(*) INTO l_count
2806: FROM hz_relationships
2807: WHERE relationship_type=l_geography_use
2808: AND subject_type=l_zone_type
2809: AND subject_id = p_geography_id
2810: AND object_id = p_zone_relation_tbl(i).included_geography_id