[Home] [Help]
74:
75: IF (l_geo_id IS NOT NULL) THEN
76: SELECT country_code
77: INTO l_country_code
78: FROM hz_geographies
79: WHERE geography_id = l_geo_id;
80:
81: SELECT DECODE(l_element_col_type,'ID', geo_element_col||'_ID','CODE',
82: geo_element_col||'_CODE','NAME',geo_element_col,geo_element_col)
97: SELECT parent_id, parent_object_type, level_number
98: FROM hz_hierarchy_nodes
99: WHERE hierarchy_type = 'MASTER_REF'
100: AND SYSDATE+0.0001 BETWEEN effective_start_date AND effective_end_date
101: AND child_table_name = 'HZ_GEOGRAPHIES'
102: AND NVL(status,'A') = 'A'
103: AND child_id = l_geo_id
104: GROUP BY parent_id, parent_object_type, level_number
105: )
137: SELECT parent_id,parent_object_type
138: FROM HZ_HIERARCHY_NODES
139: WHERE child_id = p_geography_id
140: AND child_object_type = p_geography_type
141: AND child_table_name = 'HZ_GEOGRAPHIES'
142: AND hierarchy_type = 'MASTER_REF'
143: AND NVL(status,'A') = 'A'
144: AND (effective_end_date IS NULL
145: OR effective_end_date > sysdate
147: ORDER BY level_number;
148:
149: CURSOR c_get_country_details (l_country_code VARCHAR2) IS
150: SELECT geography_id, geography_name, geography_code
151: FROM hz_geographies
152: WHERE geography_type = 'COUNTRY'
153: AND geography_use = 'MASTER_REF'
154: AND country_code = l_country_code
155: AND SYSDATE BETWEEN START_DATE AND end_date;
163: --l_denorm_stmnt VARCHAR2(2000);
164: l_geo_element_id_col VARCHAR2(30);
165: l_geo_element_code_col VARCHAR2(30);
166: l_element_range VARCHAR2(1);
167: l_country_id hz_geographies.geography_id%TYPE;
168: l_country_name hz_geographies.geography_name%TYPE;
169:
170: l_geo_element_col_temp VARCHAR2(30);
171:
164: l_geo_element_id_col VARCHAR2(30);
165: l_geo_element_code_col VARCHAR2(30);
166: l_element_range VARCHAR2(1);
167: l_country_id hz_geographies.geography_id%TYPE;
168: l_country_name hz_geographies.geography_name%TYPE;
169:
170: l_geo_element_col_temp VARCHAR2(30);
171:
172: BEGIN
172: BEGIN
173:
174: -- get country_code
175: SELECT country_code INTO l_country_code
176: FROM hz_geographies
177: WHERE geography_id= p_parent_geography_id;
178:
179: l_element_range := 'T';
180:
220: END;
221:
222: -- get geography_name
223: SELECT geography_name,geography_code INTO l_geography_name,l_geography_code
224: FROM HZ_GEOGRAPHIES
225: WHERE geography_id=l_get_all_parents.parent_id;
226:
227: -- check if this is a multi parent column. If it is then multi_parent_update_val will return NULL
228: -- otherwise it will return back the column name.
232: IF (l_geo_element_col_temp IS NOT NULL) THEN
233:
234: -- added the if condition to eliminate denormalization of code if the GEOGRAPHY_ELEMENT column is beyond 5 (bug 3111794)
235: IF l_element_range = 'T' THEN
236: EXECUTE IMMEDIATE 'UPDATE HZ_GEOGRAPHIES SET '||l_geo_element_col||'= :l_geography_name '||
237: ','||l_geo_element_id_col||'= :l_parent_id '||
238: ','||l_geo_element_code_col||'= :l_geography_code '||
239: ', multiple_parent_flag = ''N'''||
240: ' where geography_id = :l_geography_id '
240: ' where geography_id = :l_geography_id '
241: USING l_geography_name, l_get_all_parents.parent_id,l_geography_code,p_geography_id;
242:
243: ELSE
244: EXECUTE IMMEDIATE 'UPDATE HZ_GEOGRAPHIES SET '||l_geo_element_col||'= :l_geography_name '||
245: ','||l_geo_element_id_col||'= :l_parent_id '||
246: ', multiple_parent_flag = ''N'''||
247: ' where geography_id = :l_geography_id '
248: USING l_geography_name, to_char(l_get_all_parents.parent_id), to_char(p_geography_id);
248: USING l_geography_name, to_char(l_get_all_parents.parent_id), to_char(p_geography_id);
249:
250: END IF;
251: ELSE -- its a multi parent record (update the flag to 'Y')
252: UPDATE HZ_GEOGRAPHIES
253: SET multiple_parent_flag = 'Y'
254: WHERE geography_id = p_geography_id
255: AND multiple_parent_flag <> 'Y';
256:
272: SELECT child_id,child_object_type
273: FROM HZ_HIERARCHY_NODES
274: WHERE parent_id=p_geography_id
275: AND parent_object_type=p_geography_type
276: AND parent_table_name = 'HZ_GEOGRAPHIES'
277: AND hierarchy_type='MASTER_REF'
278: AND NVL(status,'A') = 'A'
279: AND (effective_end_date IS NULL
280: OR effective_end_date > sysdate);
324: SELECT distinct parent_object_type INTO l_parent_object_type
325: FROM hz_hierarchy_nodes
326: WHERE child_id = p_geography_id
327: AND child_object_type=p_geography_type
328: AND child_table_name = 'HZ_GEOGRAPHIES'
329: AND hierarchy_type='MASTER_REF'
330: AND NVL(status,'A') = 'A'
331: AND (effective_end_date IS NULL
332: OR effective_end_date > sysdate)
345: --get geography_element_column from structures
346: SELECT distinct geography_element_column INTO l_geo_element_col
347: FROM hz_geo_structure_levels
348: WHERE geography_type = l_parent_object_type
349: AND geography_id = (select geography_id from hz_geographies where country_code=
350: (select country_code from hz_geographies where geography_id=p_geography_id)
351: and geography_type = 'COUNTRY'); -- Bug4680789
352:
353: l_geo_element_col_id := l_geo_element_col||'_id';
346: SELECT distinct geography_element_column INTO l_geo_element_col
347: FROM hz_geo_structure_levels
348: WHERE geography_type = l_parent_object_type
349: AND geography_id = (select geography_id from hz_geographies where country_code=
350: (select country_code from hz_geographies where geography_id=p_geography_id)
351: and geography_type = 'COUNTRY'); -- Bug4680789
352:
353: l_geo_element_col_id := l_geo_element_col||'_id';
354: l_geo_element_col_code := l_geo_element_col||'_CODE';
370: WHERE child_id in ( SELECT parent_id
371: FROM hz_hierarchy_nodes
372: WHERE child_id = p_geography_id
373: AND child_object_type = p_geography_type
374: AND child_table_name = 'HZ_GEOGRAPHIES'
375: AND hierarchy_type = 'MASTER_REF'
376: AND NVL(status,'A') = 'A'
377: AND (effective_end_date IS NULL
378: OR effective_end_date > sysdate)
377: AND (effective_end_date IS NULL
378: OR effective_end_date > sysdate)
379: AND parent_id <> child_id
380: AND level_number = 1)
381: AND child_table_name = 'HZ_GEOGRAPHIES'
382: AND hierarchy_type='MASTER_REF'
383: AND (effective_end_date IS NULL
384: OR effective_end_date > sysdate)
385: AND parent_id <> child_id
426: l_geography_element5_code := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT5','CODE');
427:
428: -- here assumption is record was created correctly. And we are only updating those fields which
429: -- have multi parent to NULL.
430: UPDATE HZ_GEOGRAPHIES
431: -- SET multiple_parent_flag = decode(geography_id,p_geography_id,'Y',multiple_parent_flag),
432: SET multiple_parent_flag = 'Y',
433: -- geography_element1=NULL,
434: geography_element2= DECODE(l_geography_element2,NULL,NULL,geography_element2),
463: -- multiple country, so, geography_element1 should not be set to null.
464: -- Done for bug 3268961 on 04-Oct-2005
465: -- IF l_geo_element_col IN ('GEOGRAPHY_ELEMENT1','GEOGRAPHY_ELEMENT2','GEOGRAPHY_ELEMENT3','GEOGRAPHY_ELEMENT4','GEOGRAPHY_ELEMENT5') THEN
466: IF l_geo_element_col IN ('GEOGRAPHY_ELEMENT2','GEOGRAPHY_ELEMENT3','GEOGRAPHY_ELEMENT4','GEOGRAPHY_ELEMENT5') THEN
467: EXECUTE IMMEDIATE 'UPDATE hz_geographies SET multiple_parent_flag='||'''Y'''||','||l_geo_element_col||' = NULL,'||
468: l_geo_element_col_id||' = NULL,'||l_geo_element_col_code||'= NULL where geography_id = '||l_get_all_children.child_id;
469: ELSIF l_geo_element_col IN ('GEOGRAPHY_ELEMENT6','GEOGRAPHY_ELEMENT7','GEOGRAPHY_ELEMENT8','GEOGRAPHY_ELEMENT9','GEOGRAPHY_ELEMENT10') THEN
470: EXECUTE IMMEDIATE 'UPDATE hz_geographies SET multiple_parent_flag='||'''Y'''||','||l_geo_element_col||' = NULL,'||
471: l_geo_element_col_id||' = NULL where geography_id = '||l_get_all_children.child_id;
466: IF l_geo_element_col IN ('GEOGRAPHY_ELEMENT2','GEOGRAPHY_ELEMENT3','GEOGRAPHY_ELEMENT4','GEOGRAPHY_ELEMENT5') THEN
467: EXECUTE IMMEDIATE 'UPDATE hz_geographies SET multiple_parent_flag='||'''Y'''||','||l_geo_element_col||' = NULL,'||
468: l_geo_element_col_id||' = NULL,'||l_geo_element_col_code||'= NULL where geography_id = '||l_get_all_children.child_id;
469: ELSIF l_geo_element_col IN ('GEOGRAPHY_ELEMENT6','GEOGRAPHY_ELEMENT7','GEOGRAPHY_ELEMENT8','GEOGRAPHY_ELEMENT9','GEOGRAPHY_ELEMENT10') THEN
470: EXECUTE IMMEDIATE 'UPDATE hz_geographies SET multiple_parent_flag='||'''Y'''||','||l_geo_element_col||' = NULL,'||
471: l_geo_element_col_id||' = NULL where geography_id = '||l_get_all_children.child_id;
472: END IF;
473: END IF;
474: END LOOP;
662: BEGIN
663: -- check for the duplicate name/code with in the parent geography
664: SELECT geography_name,geography_code,geography_type INTO
665: l_geography_name,l_geography_code,l_geography_type
666: FROM hz_geographies
667: WHERE geography_id = p_master_relation_rec.geography_id;
668:
669: /* -- Removed check for duplicate Name/Code on 05-Dec-2005 (Nishant) for bug 4703418
670: -- This check is already done when identifier is created down the flow during
775:
776: -- construct the relationship_rec
777: l_relationship_rec.subject_id := p_master_relation_rec.parent_geography_id;
778: l_relationship_rec.subject_type := l_parent_geography_type;
779: l_relationship_rec.subject_table_name :='HZ_GEOGRAPHIES';
780: l_relationship_rec.object_id := p_master_relation_rec.geography_id;
781: l_relationship_rec.object_type :=l_geography_type;
782: l_relationship_rec.object_table_name := 'HZ_GEOGRAPHIES';
783: l_relationship_rec.relationship_code := 'PARENT_OF';
778: l_relationship_rec.subject_type := l_parent_geography_type;
779: l_relationship_rec.subject_table_name :='HZ_GEOGRAPHIES';
780: l_relationship_rec.object_id := p_master_relation_rec.geography_id;
781: l_relationship_rec.object_type :=l_geography_type;
782: l_relationship_rec.object_table_name := 'HZ_GEOGRAPHIES';
783: l_relationship_rec.relationship_code := 'PARENT_OF';
784: l_relationship_rec.relationship_type := 'MASTER_REF';
785: l_relationship_rec.start_date := p_master_relation_rec.start_date;
786: l_relationship_rec.end_date := p_master_relation_rec.end_date;
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'
822: AND relationship_type='MASTER_REF'
823: AND relationship_code = 'PARENT_OF'
824: AND status = 'A'
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'
822: AND relationship_type='MASTER_REF'
823: AND relationship_code = 'PARENT_OF'
824: AND status = 'A'
825: AND rownum <3;
824: AND status = 'A'
825: AND rownum <3;
826:
827:
828: -- in case of single parent , denormalize the relationship in HZ_GEOGRAPHIES for this geography_id
829: IF l_parent_count = 1 THEN
830:
831: --dbms_output.put_line ('before denormalize relation');
832: denormalize_relation(
910: SELECT child_id, child_object_type
911: FROM hz_hierarchy_nodes
912: WHERE hierarchy_type='MASTER_REF'
913: AND parent_id=l_geography_id
914: AND child_table_name = 'HZ_GEOGRAPHIES'
915: AND NVL(status,'A') = 'A'
916: AND (effective_end_date IS NULL
917: OR effective_end_date > sysdate)
918: AND (level_number = 1
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'
999: AND relationship_type='MASTER_REF'
1000: AND relationship_code = 'PARENT_OF'
1001: AND status = 'A';
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'
999: AND relationship_type='MASTER_REF'
1000: AND relationship_code = 'PARENT_OF'
1001: AND status = 'A';
1002:
1052:
1053: IF l_denorm_flag = 'Y' THEN
1054:
1055: -- means there is only one parent left. So set the multiple_parent_flag to 'N' for the geography_id and
1056: -- de-normalize this relation in HZ_GEOGRAPHIES for the geography_id and for all its children.
1057:
1058: UPDATE hz_geographies
1059: SET multiple_parent_flag = 'N'
1060: WHERE geography_id = l_geography_id;
1054:
1055: -- means there is only one parent left. So set the multiple_parent_flag to 'N' for the geography_id and
1056: -- de-normalize this relation in HZ_GEOGRAPHIES for the geography_id and for all its children.
1057:
1058: UPDATE hz_geographies
1059: SET multiple_parent_flag = 'N'
1060: WHERE geography_id = l_geography_id;
1061:
1062: OPEN c_get_all_children;
1079: END IF;
1080:
1081: IF l_remove_denorm_flag = 'Y' THEN
1082: -- set the multiple parent flag for this geography_id to 'Y'
1083: UPDATE hz_geographies
1084: SET multiple_parent_flag = 'Y'
1085: WHERE geography_id = l_geography_id;
1086:
1087: remove_denorm(
1165: END IF;
1166:
1167: -- Bug 4493925: default language_code in case of NULL
1168:
1169: -- construct the statement to de-normalize the identifier wherever it is used in hz_geographies
1170:
1171: l_identifier_subtype := p_geo_identifier_rec.identifier_subtype;
1172: --dbms_output.put_line ('Before validate geo identifier');
1173:
1189: -- get geography_type and geography_use
1190: BEGIN
1191:
1192: SELECT geography_use,geography_type INTO l_geography_use,l_geography_type
1193: FROM HZ_GEOGRAPHIES
1194: WHERE geography_id = p_geo_identifier_rec.geography_id;
1195:
1196: EXCEPTION when no_data_found THEN
1197: fnd_message.set_name('AR', 'HZ_GEO_NO_RECORD');
1359: SELECT distinct geography_element_column,country_code
1360: INTO l_geo_element_col,l_country_code
1361: FROM HZ_GEO_STRUCTURE_LEVELS
1362: WHERE geography_id = (SELECT geography_id FROM
1363: HZ_GEOGRAPHIES WHERE COUNTRY_CODE=(SELECT country_code from hz_geographies
1364: WHERE geography_id = l_geography_id)
1365: AND geography_type='COUNTRY')
1366: AND geography_type = l_geography_type;
1367: l_geo_element_code := l_geo_element_col||'_CODE';
1378: END;
1379: END IF;
1380:
1381:
1382: -- denormalize the primary identifier in HZ_GEOGRAPHIES for identifier_type='NAME' and 'CODE'
1383: -- for this geography_id
1384: IF p_geo_identifier_rec.primary_flag = 'Y' THEN
1385: IF p_geo_identifier_rec.identifier_type='CODE' THEN
1386: -- Bug 4591502 : ISSUE # 17
1385: IF p_geo_identifier_rec.identifier_type='CODE' THEN
1386: -- Bug 4591502 : ISSUE # 17
1387: -- Do not denormalize identfier code in country_code
1388: /* IF l_geography_type = 'COUNTRY' THEN
1389: UPDATE HZ_GEOGRAPHIES
1390: SET geography_code = p_geo_identifier_rec.identifier_value,
1391: country_code = p_geo_identifier_rec.identifier_value
1392: WHERE geography_id = p_geo_identifier_rec.geography_id;
1393:
1392: WHERE geography_id = p_geo_identifier_rec.geography_id;
1393:
1394: ELSE
1395: */
1396: UPDATE HZ_GEOGRAPHIES
1397: -- Bug 4579868 : ISSUE # 11
1398: -- denormalize upper code and not identifier_value directly
1399: -- SET geography_code = p_geo_identifier_rec.identifier_value
1400: SET geography_code = l_identifier_value
1403:
1404: IF l_geo_element_col IS NOT NULL THEN
1405: ----dbms_output.put_line('after update, before de-normaloizing code');
1406: IF l_geo_element_col IN ('GEOGRAPHY_ELEMENT1','GEOGRAPHY_ELEMENT2','GEOGRAPHY_ELEMENT3','GEOGRAPHY_ELEMENT4','GEOGRAPHY_ELEMENT5') THEN
1407: EXECUTE IMMEDIATE 'UPDATE HZ_GEOGRAPHIES SET '||l_geo_element_code||'= :l_identifier_value '||
1408: ' WHERE country_code= :l_country_code '||
1409: ' AND '||l_geo_element_id||'= :l_geography_id '
1410: USING l_identifier_value, l_country_code, l_geography_id;
1411:
1418: -- END IF;
1419: ----dbms_output.put_line('l_stmnt is '||l_stmnt);
1420: ----dbms_output.put_line('after de-normaloizing code');
1421: IF p_geo_identifier_rec.identifier_type='NAME' THEN
1422: UPDATE HZ_GEOGRAPHIES
1423: SET geography_name = p_geo_identifier_rec.identifier_value
1424: WHERE geography_id = p_geo_identifier_rec.geography_id;
1425: IF l_geo_element_col IS NOT NULL THEN
1426: EXECUTE IMMEDIATE 'UPDATE HZ_GEOGRAPHIES SET '||l_geo_element_col||'= :l_identifier_value '||
1422: UPDATE HZ_GEOGRAPHIES
1423: SET geography_name = p_geo_identifier_rec.identifier_value
1424: WHERE geography_id = p_geo_identifier_rec.geography_id;
1425: IF l_geo_element_col IS NOT NULL THEN
1426: EXECUTE IMMEDIATE 'UPDATE HZ_GEOGRAPHIES SET '||l_geo_element_col||'= :l_identifier_value '||
1427: ' WHERE country_code= :l_country_code '||
1428: ' AND '||l_geo_element_id||'= :l_geography_id '
1429: USING l_identifier_value, l_country_code, l_geography_id;
1430:
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';
1474:
1475: l_get_all_parents c_get_all_parents%ROWTYPE;
1723: x_application_id => NULL,
1724: x_program_login_id => NULL);
1725:
1726: -- Kick off conc prog if primary flag is Y and name or code has been updated
1727: -- It will call procedure HZ_GEOGRAPHIES_PKG.update_geo_element_cp
1728: IF ((l_geography_use = 'MASTER_REF') AND
1729: ((l_old_primary_flag = 'N' AND p_geo_identifier_rec.primary_flag = 'Y') OR
1730: ((l_old_primary_flag = 'Y' AND p_geo_identifier_rec.primary_flag = 'Y') AND
1731: (l_name_updated = 'Y'))
1747: THEN
1748: -- (For TAX, Logic added by Nishant on 27-Oct-2005 for Bug 4578867)
1749: -- FOR geography_use = 'TAX' we dont have any hierarchy (structure),
1750: -- so coulmns geography_element1,geography_element1_name,geography_element1_code...
1751: -- are all null.and the only columns which need to be modified in hz_geographies
1752: -- are geography_name and geography_code
1753:
1754: IF p_geo_identifier_rec.identifier_type = 'CODE' THEN
1755: UPDATE HZ_GEOGRAPHIES
1751: -- are all null.and the only columns which need to be modified in hz_geographies
1752: -- are geography_name and geography_code
1753:
1754: IF p_geo_identifier_rec.identifier_type = 'CODE' THEN
1755: UPDATE HZ_GEOGRAPHIES
1756: SET geography_code = l_new_geo_value
1757: WHERE geography_id = p_geo_identifier_rec.geography_id
1758: AND geography_use = l_geography_use;
1759: END IF;
1758: AND geography_use = l_geography_use;
1759: END IF;
1760:
1761: IF p_geo_identifier_rec.identifier_type = 'NAME' THEN
1762: UPDATE HZ_GEOGRAPHIES
1763: SET geography_name = l_new_geo_value
1764: WHERE geography_id = p_geo_identifier_rec.geography_id
1765: AND geography_use = l_geography_use;
1766: END IF;
1893: IF p_master_geography_rec.geography_type='COUNTRY' THEN
1894: l_country_code := p_master_geography_rec.geography_code;
1895: ELSE
1896: SELECT country_code INTO l_country_code
1897: FROM HZ_GEOGRAPHIES
1898: WHERE geography_id= l_parent_geography_tbl(1);
1899: END IF;
1900:
1901: --dbms_output.put_line('before insert_row');
1898: WHERE geography_id= l_parent_geography_tbl(1);
1899: END IF;
1900:
1901: --dbms_output.put_line('before insert_row');
1902: --insert row into HZ_GEOGRAPHIES
1903: HZ_GEOGRAPHIES_PKG.insert_row(
1904: x_rowid => l_rowid,
1905: x_geography_id => x_geography_id,
1906: x_object_version_number => 1,
1899: END IF;
1900:
1901: --dbms_output.put_line('before insert_row');
1902: --insert row into HZ_GEOGRAPHIES
1903: HZ_GEOGRAPHIES_PKG.insert_row(
1904: x_rowid => l_rowid,
1905: x_geography_id => x_geography_id,
1906: x_object_version_number => 1,
1907: x_geography_type => UPPER(p_master_geography_rec.geography_type),
2109:
2110: -- Initialize start_date and end_date
2111: SELECT rowid,start_date,end_date,geography_use,object_version_number INTO l_rowid,l_start_date,l_end_date,
2112: l_geography_use,l_object_version_number
2113: FROM HZ_GEOGRAPHIES
2114: WHERE geography_id=p_geography_id
2115: FOR UPDATE of geography_id NOWAIT;
2116:
2117: --validate object_version_number
2116:
2117: --validate object_version_number
2118: IF l_object_version_number <> p_object_version_number THEN
2119: FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
2120: FND_MESSAGE.SET_TOKEN('TABLE', 'hz_geographies');
2121: FND_MSG_PUB.ADD;
2122: RAISE FND_API.G_EXC_ERROR;
2123: ELSE
2124: p_object_version_number := l_object_version_number + 1;
2175: END IF;
2176: --dbms_output.put_line('After date validation '|| x_return_status);
2177:
2178: --call table handler to update the geography
2179: HZ_GEOGRAPHIES_PKG.update_row(
2180: x_rowid => l_rowid,
2181: x_geography_id => p_geography_id,
2182: x_object_version_number => p_object_version_number,
2183: x_geography_type => NULL,
2346: -- get geography type, country code for parent id
2347: BEGIN
2348: SELECT geography_type, country_code
2349: INTO l_parent_geo_type, l_country_code
2350: FROM hz_geographies
2351: WHERE geography_id = l_geography_range_rec.master_ref_geography_id
2352: AND geography_use = 'MASTER_REF'
2353: AND TRUNC(SYSDATE) BETWEEN START_DATE AND end_date
2354: ;
2823:
2824: -- call relationship API to create a relationship between geography_id and included_geography_id
2825: l_relationship_rec.subject_id := p_geography_id;
2826: l_relationship_rec.subject_type := l_zone_type;
2827: l_relationship_rec.subject_table_name :='HZ_GEOGRAPHIES';
2828: l_relationship_rec.object_id := p_zone_relation_tbl(i).included_geography_id;
2829: l_relationship_rec.object_type :=l_incl_geo_type;
2830: l_relationship_rec.object_table_name := 'HZ_GEOGRAPHIES';
2831: l_relationship_rec.relationship_code := 'PARENT_OF';
2826: l_relationship_rec.subject_type := l_zone_type;
2827: l_relationship_rec.subject_table_name :='HZ_GEOGRAPHIES';
2828: l_relationship_rec.object_id := p_zone_relation_tbl(i).included_geography_id;
2829: l_relationship_rec.object_type :=l_incl_geo_type;
2830: l_relationship_rec.object_table_name := 'HZ_GEOGRAPHIES';
2831: l_relationship_rec.relationship_code := 'PARENT_OF';
2832: l_relationship_rec.relationship_type := l_geography_use;
2833: l_relationship_rec.start_date := p_zone_relation_tbl(i).start_date;
2834: l_relationship_rec.end_date := p_zone_relation_tbl(i).end_date;
2968: END;
2969:
2970: -- zone_name must be unique with in a zone_type
2971: SELECT count(*) INTO l_count
2972: FROM hz_geographies
2973: WHERE geography_name = p_zone_name
2974: AND geography_type = p_zone_type
2975: AND rownum <2;
2976:
2984: -- zone_code must be unique within a zone_type
2985:
2986: IF p_zone_code IS NOT NULL THEN
2987: SELECT count(*) INTO l_count
2988: FROM hz_geographies
2989: WHERE geography_code = upper(p_zone_code)
2990: AND geography_type = p_zone_type
2991: AND rownum <2;
2992:
3038: IF p_zone_relation_tbl(i).included_geography_id IS NOT NULL THEN
3039: BEGIN
3040:
3041: SELECT country_code INTO l_country_code
3042: FROM hz_geographies
3043: WHERE geography_id = p_zone_relation_tbl(i).included_geography_id;
3044: IF l_country_code IS NOT NULL THEN
3045: EXIT;
3046: END IF;
3055: END IF;
3056: END LOOP;
3057:
3058:
3059: -- call table handler to insert a row in hz_geographies
3060:
3061: HZ_GEOGRAPHIES_PKG.insert_row(
3062: x_rowid => l_rowid,
3063: x_geography_id => x_geography_id,
3057:
3058:
3059: -- call table handler to insert a row in hz_geographies
3060:
3061: HZ_GEOGRAPHIES_PKG.insert_row(
3062: x_rowid => l_rowid,
3063: x_geography_id => x_geography_id,
3064: x_object_version_number => 1,
3065: x_geography_type => p_zone_type,