[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(
912: SELECT child_id, child_object_type
913: FROM hz_hierarchy_nodes
914: WHERE hierarchy_type='MASTER_REF'
915: AND parent_id=l_geography_id
916: AND child_table_name = 'HZ_GEOGRAPHIES'
917: AND NVL(status,'A') = 'A'
918: AND (effective_end_date IS NULL
919: OR effective_end_date > sysdate)
920: ;
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'
1004: AND relationship_type='MASTER_REF'
1005: AND relationship_code = 'PARENT_OF'
1006: AND status = 'A';
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'
1004: AND relationship_type='MASTER_REF'
1005: AND relationship_code = 'PARENT_OF'
1006: AND status = 'A';
1007:
1057:
1058: IF l_denorm_flag = 'Y' THEN
1059:
1060: -- means there is only one parent left. So set the multiple_parent_flag to 'N' for the geography_id and
1061: -- de-normalize this relation in HZ_GEOGRAPHIES for the geography_id and for all its children.
1062:
1063: UPDATE hz_geographies
1064: SET multiple_parent_flag = 'N'
1065: WHERE geography_id = l_geography_id;
1059:
1060: -- means there is only one parent left. So set the multiple_parent_flag to 'N' for the geography_id and
1061: -- de-normalize this relation in HZ_GEOGRAPHIES for the geography_id and for all its children.
1062:
1063: UPDATE hz_geographies
1064: SET multiple_parent_flag = 'N'
1065: WHERE geography_id = l_geography_id;
1066:
1067: OPEN c_get_all_children;
1084: END IF;
1085:
1086: IF l_remove_denorm_flag = 'Y' THEN
1087: -- set the multiple parent flag for this geography_id to 'Y'
1088: UPDATE hz_geographies
1089: SET multiple_parent_flag = 'Y'
1090: WHERE geography_id = l_geography_id;
1091:
1092: remove_denorm(
1170: END IF;
1171:
1172: -- Bug 4493925: default language_code in case of NULL
1173:
1174: -- construct the statement to de-normalize the identifier wherever it is used in hz_geographies
1175:
1176: l_identifier_subtype := p_geo_identifier_rec.identifier_subtype;
1177: --dbms_output.put_line ('Before validate geo identifier');
1178:
1194: -- get geography_type and geography_use
1195: BEGIN
1196:
1197: SELECT geography_use,geography_type INTO l_geography_use,l_geography_type
1198: FROM HZ_GEOGRAPHIES
1199: WHERE geography_id = p_geo_identifier_rec.geography_id;
1200:
1201: EXCEPTION when no_data_found THEN
1202: fnd_message.set_name('AR', 'HZ_GEO_NO_RECORD');
1364: SELECT distinct geography_element_column,country_code
1365: INTO l_geo_element_col,l_country_code
1366: FROM HZ_GEO_STRUCTURE_LEVELS
1367: WHERE geography_id = (SELECT geography_id FROM
1368: HZ_GEOGRAPHIES WHERE COUNTRY_CODE=(SELECT country_code from hz_geographies
1369: WHERE geography_id = l_geography_id)
1370: AND geography_type='COUNTRY')
1371: AND geography_type = l_geography_type;
1372: l_geo_element_code := l_geo_element_col||'_CODE';
1383: END;
1384: END IF;
1385:
1386:
1387: -- denormalize the primary identifier in HZ_GEOGRAPHIES for identifier_type='NAME' and 'CODE'
1388: -- for this geography_id
1389: IF p_geo_identifier_rec.primary_flag = 'Y' THEN
1390: IF p_geo_identifier_rec.identifier_type='CODE' THEN
1391: -- Bug 4591502 : ISSUE # 17
1390: IF p_geo_identifier_rec.identifier_type='CODE' THEN
1391: -- Bug 4591502 : ISSUE # 17
1392: -- Do not denormalize identfier code in country_code
1393: /* IF l_geography_type = 'COUNTRY' THEN
1394: UPDATE HZ_GEOGRAPHIES
1395: SET geography_code = p_geo_identifier_rec.identifier_value,
1396: country_code = p_geo_identifier_rec.identifier_value
1397: WHERE geography_id = p_geo_identifier_rec.geography_id;
1398:
1397: WHERE geography_id = p_geo_identifier_rec.geography_id;
1398:
1399: ELSE
1400: */
1401: UPDATE HZ_GEOGRAPHIES
1402: -- Bug 4579868 : ISSUE # 11
1403: -- denormalize upper code and not identifier_value directly
1404: -- SET geography_code = p_geo_identifier_rec.identifier_value
1405: SET geography_code = l_identifier_value,
1410:
1411: IF l_geo_element_col IS NOT NULL THEN
1412: ----dbms_output.put_line('after update, before de-normaloizing code');
1413: IF l_geo_element_col IN ('GEOGRAPHY_ELEMENT1','GEOGRAPHY_ELEMENT2','GEOGRAPHY_ELEMENT3','GEOGRAPHY_ELEMENT4','GEOGRAPHY_ELEMENT5') THEN
1414: EXECUTE IMMEDIATE 'UPDATE HZ_GEOGRAPHIES SET '||l_geo_element_code||'= :l_identifier_value '||
1415: ' WHERE country_code= :l_country_code '||
1416: ' AND '||l_geo_element_id||'= :l_geography_id '
1417: USING l_identifier_value, l_country_code, l_geography_id;
1418:
1425: -- END IF;
1426: ----dbms_output.put_line('l_stmnt is '||l_stmnt);
1427: ----dbms_output.put_line('after de-normaloizing code');
1428: IF p_geo_identifier_rec.identifier_type='NAME' THEN
1429: UPDATE HZ_GEOGRAPHIES
1430: SET geography_name = p_geo_identifier_rec.identifier_value,
1431: last_updated_by = hz_utility_pub.LAST_UPDATED_BY,
1432: last_update_date = hz_utility_pub.LAST_UPDATE_DATE
1433: WHERE geography_id = p_geo_identifier_rec.geography_id;
1431: last_updated_by = hz_utility_pub.LAST_UPDATED_BY,
1432: last_update_date = hz_utility_pub.LAST_UPDATE_DATE
1433: WHERE geography_id = p_geo_identifier_rec.geography_id;
1434: IF l_geo_element_col IS NOT NULL THEN
1435: EXECUTE IMMEDIATE 'UPDATE HZ_GEOGRAPHIES SET '||l_geo_element_col||'= :l_identifier_value '||
1436: ' WHERE country_code= :l_country_code '||
1437: ' AND '||l_geo_element_id||'= :l_geography_id '
1438: USING l_identifier_value, l_country_code, l_geography_id;
1439:
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';
1483:
1484: l_get_all_parents c_get_all_parents%ROWTYPE;
1732: x_application_id => NULL,
1733: x_program_login_id => NULL);
1734:
1735: -- Kick off conc prog if primary flag is Y and name or code has been updated
1736: -- It will call procedure HZ_GEOGRAPHIES_PKG.update_geo_element_cp
1737: IF ((l_geography_use = 'MASTER_REF') AND
1738: ((l_old_primary_flag = 'N' AND p_geo_identifier_rec.primary_flag = 'Y') OR
1739: ((l_old_primary_flag = 'Y' AND p_geo_identifier_rec.primary_flag = 'Y') AND
1740: (l_name_updated = 'Y'))
1756: THEN
1757: -- (For TAX, Logic added by Nishant on 27-Oct-2005 for Bug 4578867)
1758: -- FOR geography_use = 'TAX' we dont have any hierarchy (structure),
1759: -- so coulmns geography_element1,geography_element1_name,geography_element1_code...
1760: -- are all null.and the only columns which need to be modified in hz_geographies
1761: -- are geography_name and geography_code
1762:
1763: IF p_geo_identifier_rec.identifier_type = 'CODE' THEN
1764: UPDATE HZ_GEOGRAPHIES
1760: -- are all null.and the only columns which need to be modified in hz_geographies
1761: -- are geography_name and geography_code
1762:
1763: IF p_geo_identifier_rec.identifier_type = 'CODE' THEN
1764: UPDATE HZ_GEOGRAPHIES
1765: SET geography_code = l_new_geo_value,
1766: last_updated_by = hz_utility_pub.LAST_UPDATED_BY,
1767: last_update_date = hz_utility_pub.LAST_UPDATE_DATE
1768: WHERE geography_id = p_geo_identifier_rec.geography_id
1769: AND geography_use = l_geography_use;
1770: END IF;
1771:
1772: IF p_geo_identifier_rec.identifier_type = 'NAME' THEN
1773: UPDATE HZ_GEOGRAPHIES
1774: SET geography_name = l_new_geo_value,
1775: last_updated_by = hz_utility_pub.LAST_UPDATED_BY,
1776: last_update_date = hz_utility_pub.LAST_UPDATE_DATE
1777: WHERE geography_id = p_geo_identifier_rec.geography_id
1906: IF p_master_geography_rec.geography_type='COUNTRY' THEN
1907: l_country_code := p_master_geography_rec.geography_code;
1908: ELSE
1909: SELECT country_code INTO l_country_code
1910: FROM HZ_GEOGRAPHIES
1911: WHERE geography_id= l_parent_geography_tbl(1);
1912: END IF;
1913:
1914: --dbms_output.put_line('before insert_row');
1911: WHERE geography_id= l_parent_geography_tbl(1);
1912: END IF;
1913:
1914: --dbms_output.put_line('before insert_row');
1915: --insert row into HZ_GEOGRAPHIES
1916: HZ_GEOGRAPHIES_PKG.insert_row(
1917: x_rowid => l_rowid,
1918: x_geography_id => x_geography_id,
1919: x_object_version_number => 1,
1912: END IF;
1913:
1914: --dbms_output.put_line('before insert_row');
1915: --insert row into HZ_GEOGRAPHIES
1916: HZ_GEOGRAPHIES_PKG.insert_row(
1917: x_rowid => l_rowid,
1918: x_geography_id => x_geography_id,
1919: x_object_version_number => 1,
1920: x_geography_type => UPPER(p_master_geography_rec.geography_type),
2122:
2123: -- Initialize start_date and end_date
2124: SELECT rowid,start_date,end_date,geography_use,object_version_number INTO l_rowid,l_start_date,l_end_date,
2125: l_geography_use,l_object_version_number
2126: FROM HZ_GEOGRAPHIES
2127: WHERE geography_id=p_geography_id
2128: FOR UPDATE of geography_id NOWAIT;
2129:
2130: --validate object_version_number
2129:
2130: --validate object_version_number
2131: IF l_object_version_number <> p_object_version_number THEN
2132: FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
2133: FND_MESSAGE.SET_TOKEN('TABLE', 'hz_geographies');
2134: FND_MSG_PUB.ADD;
2135: RAISE FND_API.G_EXC_ERROR;
2136: ELSE
2137: p_object_version_number := l_object_version_number + 1;
2188: END IF;
2189: --dbms_output.put_line('After date validation '|| x_return_status);
2190:
2191: --call table handler to update the geography
2192: HZ_GEOGRAPHIES_PKG.update_row(
2193: x_rowid => l_rowid,
2194: x_geography_id => p_geography_id,
2195: x_object_version_number => p_object_version_number,
2196: x_geography_type => NULL,
2359: -- get geography type, country code for parent id
2360: BEGIN
2361: SELECT geography_type, country_code
2362: INTO l_parent_geo_type, l_country_code
2363: FROM hz_geographies
2364: WHERE geography_id = l_geography_range_rec.master_ref_geography_id
2365: AND geography_use = 'MASTER_REF'
2366: AND TRUNC(SYSDATE) BETWEEN START_DATE AND end_date
2367: ;
2836:
2837: -- call relationship API to create a relationship between geography_id and included_geography_id
2838: l_relationship_rec.subject_id := p_geography_id;
2839: l_relationship_rec.subject_type := l_zone_type;
2840: l_relationship_rec.subject_table_name :='HZ_GEOGRAPHIES';
2841: l_relationship_rec.object_id := p_zone_relation_tbl(i).included_geography_id;
2842: l_relationship_rec.object_type :=l_incl_geo_type;
2843: l_relationship_rec.object_table_name := 'HZ_GEOGRAPHIES';
2844: l_relationship_rec.relationship_code := 'PARENT_OF';
2839: l_relationship_rec.subject_type := l_zone_type;
2840: l_relationship_rec.subject_table_name :='HZ_GEOGRAPHIES';
2841: l_relationship_rec.object_id := p_zone_relation_tbl(i).included_geography_id;
2842: l_relationship_rec.object_type :=l_incl_geo_type;
2843: l_relationship_rec.object_table_name := 'HZ_GEOGRAPHIES';
2844: l_relationship_rec.relationship_code := 'PARENT_OF';
2845: l_relationship_rec.relationship_type := l_geography_use;
2846: l_relationship_rec.start_date := p_zone_relation_tbl(i).start_date;
2847: l_relationship_rec.end_date := p_zone_relation_tbl(i).end_date;
2981: END;
2982:
2983: -- zone_name must be unique with in a zone_type
2984: SELECT count(*) INTO l_count
2985: FROM hz_geographies
2986: WHERE geography_name = p_zone_name
2987: AND geography_type = p_zone_type
2988: AND rownum <2;
2989:
2997: -- zone_code must be unique within a zone_type
2998:
2999: IF p_zone_code IS NOT NULL THEN
3000: SELECT count(*) INTO l_count
3001: FROM hz_geographies
3002: WHERE geography_code = upper(p_zone_code)
3003: AND geography_type = p_zone_type
3004: AND rownum <2;
3005:
3051: IF p_zone_relation_tbl(i).included_geography_id IS NOT NULL THEN
3052: BEGIN
3053:
3054: SELECT country_code INTO l_country_code
3055: FROM hz_geographies
3056: WHERE geography_id = p_zone_relation_tbl(i).included_geography_id;
3057: IF l_country_code IS NOT NULL THEN
3058: EXIT;
3059: END IF;
3068: END IF;
3069: END LOOP;
3070:
3071:
3072: -- call table handler to insert a row in hz_geographies
3073:
3074: HZ_GEOGRAPHIES_PKG.insert_row(
3075: x_rowid => l_rowid,
3076: x_geography_id => x_geography_id,
3070:
3071:
3072: -- call table handler to insert a row in hz_geographies
3073:
3074: HZ_GEOGRAPHIES_PKG.insert_row(
3075: x_rowid => l_rowid,
3076: x_geography_id => x_geography_id,
3077: x_object_version_number => 1,
3078: x_geography_type => p_zone_type,