[Home] [Help]
PACKAGE BODY: APPS.HZ_GEOGRAPHY_PUB
Source
1 PACKAGE BODY HZ_GEOGRAPHY_PUB AS
2 /*$Header: ARHGEOSB.pls 120.39 2007/11/15 01:47:12 nsinghai ship $ */
3
4 ------------------------------------
5 -- declaration of private procedures
6 ------------------------------------
7
8 PROCEDURE do_create_master_relation(
9 p_master_relation_rec IN MASTER_RELATION_REC_TYPE,
10 x_relationship_id OUT NOCOPY NUMBER,
11 x_return_status IN OUT NOCOPY VARCHAR2
12 );
13
14 PROCEDURE denormalize_relation(
15 p_geography_id IN NUMBER,
16 p_parent_geography_id IN NUMBER,
17 p_geography_type IN VARCHAR2,
18 x_return_status IN OUT NOCOPY VARCHAR2
19 );
20
21 PROCEDURE remove_denorm(
22 p_geography_id IN NUMBER,
23 p_geography_type IN VARCHAR2
24 );
25 PROCEDURE check_duplicate_name(
26 p_parent_id IN NUMBER,
27 p_child_id IN NUMBER,
28 p_child_name IN VARCHAR2,
29 p_child_type IN VARCHAR2,
30 p_child_identifier_subtype IN VARCHAR2,
31 p_child_language IN VARCHAR2,
32 x_return_status IN OUT NOCOPY VARCHAR2
33 );
34 PROCEDURE check_duplicate_code(
35 p_parent_id IN NUMBER,
36 p_child_id IN NUMBER,
37 p_child_code IN VARCHAR2,
38 p_child_type IN VARCHAR2,
39 p_child_identifier_subtype IN VARCHAR2,
40 p_child_language IN VARCHAR2,
41 x_return_status IN OUT NOCOPY VARCHAR2
42 );
43 -- Added by Nishant on 05-Oct-2005 for bug 3268961
44 FUNCTION multi_parent_update_val(
45 l_geo_id IN NUMBER,
46 l_element_col IN VARCHAR2,
47 l_element_col_type IN VARCHAR2 DEFAULT 'NAME'
48 ) RETURN VARCHAR2;
49
50 ------------------------------
51 --global variables
52 ------------------------------
53
54 g_dup_checked VARCHAR2(1) := 'N';
55
56 -------------------------------
57 -- body of private function
58 -------------------------------
59 -----------------------------------------------------------------------------+
60 -- This function takes in geography_id for and element_column and column_type
61 -- as input, validates if for passed element column there are multiple parents
62 -- or not, it there are, it returns null otherwise it will return element column
63 -- name depending on passed element column type (ID, CODE, NAME)
64 -- Created by Nishant Singhai for Bug 3268961. This function will help in
65 -- indentifying if a particular column has to be updated or not in case of
66 -- multiple parents
67 -----------------------------------------------------------------------------+
68 FUNCTION multi_parent_update_val (l_geo_id NUMBER, l_element_col VARCHAR2,
69 l_element_col_type VARCHAR2 DEFAULT 'NAME')
70 RETURN VARCHAR2 IS
71 l_return_value VARCHAR2(100);
72 l_country_code VARCHAR2(10);
73 BEGIN
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)
83 INTO l_return_value
84 FROM (
85 -- This select gives geography_element column name for those levels which
86 -- are not multiple. For levels at which multiple parents exist, it returns null
87 SELECT decode(no_of_parents, 1, DECODE(geo_temp.parent_object_type,'COUNTRY','GEOGRAPHY_ELEMENT1'
88 ,geo_struct.geography_element_column), NULL) geo_element_col, ROWNUM level_number,
89 geo_temp.parent_object_type geography_type
90 FROM (
91 -- here grouping is based on number of parents at each level
92 SELECT COUNT(parent_object_type) no_of_parents , parent_object_type, level_number
93 FROM (
94 -- This query does the grouping based on parent id and geography type
95 -- Note : sysdate is not truncated in check because we want to not pick
96 -- up those records which are just now end dated (end dated in current flow)
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 )
106 GROUP BY parent_object_type, level_number
107 ORDER BY level_number desc
108 ) geo_temp,
109 hz_geo_structure_levels geo_struct
110 WHERE geo_temp.parent_object_type = DECODE(geo_temp.parent_object_type,
111 'COUNTRY',geo_struct.parent_geography_type,
112 geo_struct.geography_type)
113 AND country_code = l_country_code
114 )
115 WHERE geo_element_col = l_element_col;
116
117 END IF;
118
119 RETURN l_return_value;
120
121 EXCEPTION WHEN OTHERS THEN
122 RETURN NULL;
123 END multi_parent_update_val;
124
125
126 -------------------------------
127 -- body of private procedures
128 -------------------------------
129
130 PROCEDURE denormalize_relation(
131 p_geography_id IN NUMBER,
132 p_parent_geography_id IN NUMBER,
133 p_geography_type IN VARCHAR2,
134 x_return_status IN OUT NOCOPY VARCHAR2) IS
135
136 CURSOR c_get_all_parents IS
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
146 )
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;
156
157 l_get_all_parents c_get_all_parents%ROWTYPE;
158 l_multiple_parent_flag VARCHAR2(1);
159 l_geo_element_col VARCHAR2(30);
160 l_geography_name VARCHAR2(360);
161 l_geography_code VARCHAR2(30);
162 l_country_code VARCHAR2(2);
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
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
181 OPEN c_get_all_parents;
182 LOOP
183 FETCH c_get_all_parents INTO l_get_all_parents;
184 EXIT WHEN c_get_all_parents%NOTFOUND;
185
186 BEGIN
187
188 IF l_get_all_parents.parent_object_type <> 'COUNTRY' THEN
189 -- get geography_element_column for this geography_type
190 SELECT geography_element_column INTO l_geo_element_col
191 FROM HZ_GEO_STRUCTURE_LEVELS
192 WHERE geography_type=l_get_all_parents.parent_object_type
193 AND country_code = l_country_code
194 AND rownum <2 ;
195
196 --dbms_output.put_line('geo_element_col is '||l_geo_element_col);
197 l_geo_element_id_col := l_geo_element_col||'_ID';
198 l_geo_element_code_col := l_geo_element_col||'_CODE';
199
200 ELSE
201 l_geo_element_col := 'GEOGRAPHY_ELEMENT1';
202 l_geo_element_code_col := 'GEOGRAPHY_ELEMENT1_CODE';
203 l_geo_element_id_col := 'GEOGRAPHY_ELEMENT1_ID';
204 END IF;
205
206 -- Bug 6507596 : Added 'T' condition to reinitialize l_element_range for each loop execution.
207 IF l_geo_element_col in ('GEOGRAPHY_ELEMENT1','GEOGRAPHY_ELEMENT2','GEOGRAPHY_ELEMENT3','GEOGRAPHY_ELEMENT4','GEOGRAPHY_ELEMENT5') THEN
208 l_element_range := 'T';
209 ELSE
210 l_element_range := 'F';
211 END IF;
212
213 EXCEPTION WHEN NO_DATA_FOUND THEN
214 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_NO_RECORD');
215 FND_MESSAGE.SET_TOKEN('TOKEN1', 'geography_element_column');
216 FND_MESSAGE.SET_TOKEN('TOKEN2', 'geography_type :'||l_get_all_parents.parent_object_type||', country_code :'||l_country_code);
217 FND_MSG_PUB.ADD;
218 x_return_status := fnd_api.g_ret_sts_error;
219 RAISE FND_API.G_EXC_ERROR;
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.
229 l_geo_element_col_temp := multi_parent_update_val(p_geography_id,l_geo_element_col,'NAME');
230
231 -- do update if it is not a multi parent case. So, do update only if l_geo_element_col_temp is not null
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 '
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);
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
257 END IF; -- end multi parent check
258
259 END LOOP;
260 --dbms_output.put_line('After the loop in denormalize_relation');
261 CLOSE c_get_all_parents;
262
263 END denormalize_relation;
264
265 --removes de-normalization from the geography and its children in case of multiple parents for the geography
266 PROCEDURE remove_denorm(
267 p_geography_id IN NUMBER ,
268 p_geography_type IN VARCHAR2
269 ) IS
270
271 CURSOR c_get_all_children IS
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);
281
282 l_geo_element_col VARCHAR2(30);
283 l_common_parent_flag VARCHAR2(1);
284 l_common_type_flag VARCHAR2(1);
285 l_get_all_children c_get_all_children%ROWTYPE;
286 l_count NUMBER;
287 l_parent_object_type VARCHAR2(30);
288 l_geo_element_col_id VARCHAR2(30);
289 l_geo_element_col_code VARCHAR2(30);
290 --l_stmnt VARCHAR2(1000);
291
292 -- new variables for updating multi parent case
293 l_geography_element2 VARCHAR2(100);
294 l_geography_element3 VARCHAR2(100);
295 l_geography_element4 VARCHAR2(100);
296 l_geography_element5 VARCHAR2(100);
297 l_geography_element6 VARCHAR2(100);
298 l_geography_element7 VARCHAR2(100);
299 l_geography_element8 VARCHAR2(100);
300 l_geography_element9 VARCHAR2(100);
301 l_geography_element10 VARCHAR2(100);
302 l_geography_element2_id VARCHAR2(100);
303 l_geography_element3_id VARCHAR2(100);
304 l_geography_element4_id VARCHAR2(100);
305 l_geography_element5_id VARCHAR2(100);
306 l_geography_element6_id VARCHAR2(100);
307 l_geography_element7_id VARCHAR2(100);
308 l_geography_element8_id VARCHAR2(100);
309 l_geography_element9_id VARCHAR2(100);
310 l_geography_element10_id VARCHAR2(100);
311 l_geography_element2_code VARCHAR2(100);
312 l_geography_element3_code VARCHAR2(100);
313 l_geography_element4_code VARCHAR2(100);
314 l_geography_element5_code VARCHAR2(100);
315
316
317 BEGIN
318
319 --dbms_output.put_line('In remove denorm');
320
321 -- check if all parents of geography_id are of same geography_type
322 BEGIN
323
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)
333 AND level_number = 1;
334
335 l_common_type_flag := 'Y';
336
337 EXCEPTION WHEN too_many_rows THEN
338 l_common_type_flag := 'N';
339 END;
340 --dbms_output.put_line ('common_type_flag = '||l_common_type_flag);
341
342 IF l_common_type_flag= 'Y' THEN
343
344 BEGIN
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';
354 l_geo_element_col_code := l_geo_element_col||'_CODE';
355
356 EXCEPTION WHEN NO_DATA_FOUND THEN
357 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_NO_RECORD');
358 FND_MESSAGE.SET_TOKEN('TOKEN1', 'geography_element_column');
359 FND_MESSAGE.SET_TOKEN('TOKEN2', 'geography_type :'||l_parent_object_type||'and for country_code of geography_id:'||p_geography_id);
360 FND_MSG_PUB.ADD;
361
362 RAISE FND_API.G_EXC_ERROR;
363 END;
364 END IF;
365 --dbms_output.put_line ('geo_element_column is '||l_geo_element_col);
366
367 -- check if all parents of geography_id have same parent
368 SELECT count(distinct parent_id) INTO l_count
369 FROM hz_hierarchy_nodes
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)
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
386 AND level_number = 1;
387
388 --dbms_output.put_line('number of parents :'||to_char(l_count));
389 IF l_count > 1 THEN
390 l_common_parent_flag := 'N';
391 ELSIF l_count = 1 THEN
392 l_common_parent_flag := 'Y';
393 END IF;
394
395 -- remove the de-normalization from the geography_id row and from all its children
396 OPEN c_get_all_children;
397 LOOP
398 FETCH c_get_all_children INTO l_get_all_children;
399 EXIT WHEN c_get_all_children%NOTFOUND;
400 IF (l_common_type_flag ='N' or l_common_parent_flag = 'N') THEN
401 -- nullify all the de-normalized columns
402 -- Removed geography_element1 from update to NULL because any geography can not belong to
403 -- multiple country, so, geography_element1 should not be set to null.
404 -- Done for bug 3268961 on 04-Oct-2005
405 l_geography_element2 := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT2','NAME');
406 l_geography_element3 := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT3','NAME');
407 l_geography_element4 := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT4','NAME');
408 l_geography_element5 := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT5','NAME');
409 l_geography_element6 := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT6','NAME');
410 l_geography_element7 := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT7','NAME');
411 l_geography_element8 := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT8','NAME');
412 l_geography_element9 := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT9','NAME');
413 l_geography_element10 := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT10','NAME');
414 l_geography_element2_id := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT2','ID');
415 l_geography_element3_id := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT3','ID');
416 l_geography_element4_id := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT4','ID');
417 l_geography_element5_id := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT5','ID');
418 l_geography_element6_id := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT6','ID');
419 l_geography_element7_id := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT7','ID');
420 l_geography_element8_id := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT8','ID');
421 l_geography_element9_id := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT9','ID');
422 l_geography_element10_id := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT10','ID');
423 l_geography_element2_code := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT2','CODE');
424 l_geography_element3_code := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT3','CODE');
425 l_geography_element4_code := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT4','CODE');
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),
435 geography_element3= DECODE(l_geography_element3,NULL,NULL,geography_element3),
436 geography_element4= DECODE(l_geography_element4,NULL,NULL,geography_element4),
437 geography_element5= DECODE(l_geography_element5,NULL,NULL,geography_element5),
438 geography_element6= DECODE(l_geography_element6,NULL,NULL,geography_element6),
439 geography_element7= DECODE(l_geography_element7,NULL,NULL,geography_element7),
440 geography_element8= DECODE(l_geography_element8,NULL,NULL,geography_element8),
441 geography_element9= DECODE(l_geography_element9,NULL,NULL,geography_element9),
442 geography_element10= DECODE(l_geography_element10,NULL,NULL,geography_element10),
443 -- geography_element1_id=NULL,
444 geography_element2_id= DECODE(l_geography_element2_id,NULL,NULL,geography_element2_id),
445 geography_element3_id= DECODE(l_geography_element3_id,NULL,NULL,geography_element3_id),
446 geography_element4_id= DECODE(l_geography_element4_id,NULL,NULL,geography_element4_id),
447 geography_element5_id= DECODE(l_geography_element5_id,NULL,NULL,geography_element5_id),
448 geography_element6_id= DECODE(l_geography_element6_id,NULL,NULL,geography_element6_id),
449 geography_element7_id= DECODE(l_geography_element7_id,NULL,NULL,geography_element7_id),
450 geography_element8_id= DECODE(l_geography_element8_id,NULL,NULL,geography_element8_id),
451 geography_element9_id= DECODE(l_geography_element9_id,NULL,NULL,geography_element9_id),
452 geography_element10_id= DECODE(l_geography_element10_id,NULL,NULL,geography_element10_id),
453 -- geography_element1_code = NULL,
454 geography_element2_code = DECODE(l_geography_element2_code,NULL,NULL,geography_element2_code),
455 geography_element3_code = DECODE(l_geography_element3_code,NULL,NULL,geography_element3_code),
456 geography_element4_code = DECODE(l_geography_element4_code,NULL,NULL,geography_element4_code),
457 geography_element5_code = DECODE(l_geography_element5_code,NULL,NULL,geography_element5_code)
458 WHERE geography_id=l_get_all_children.child_id;
459
460 ELSE
461 -- nullify the geography_element_column where the parent info of this geography_id is stored
462 -- Removed geography_element1 from update to NULL because 1 geography can not belong to
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;
472 END IF;
473 END IF;
474 END LOOP;
475 CLOSE c_get_all_children;
476
477 END remove_denorm;
478
479
480 -- This procudure checks for the duplicate names for a child_id with in its parent
481 PROCEDURE check_duplicate_name(
482 p_parent_id IN NUMBER,
483 p_child_id IN NUMBER,
484 p_child_name IN VARCHAR2,
485 p_child_type IN VARCHAR2,
486 p_child_identifier_subtype IN VARCHAR2,
487 p_child_language IN VARCHAR2,
488 x_return_status IN OUT NOCOPY VARCHAR2
489 ) IS
490
491 l_count NUMBER;
492
493 BEGIN
494
495 -- check if the name is duplicated with in the parent of p_child_id
496 -- Added Subtype and language check for bug 4703418 on 28-Nov-2005 (Nishant)
497 SELECT count(*) INTO l_count
498 FROM hz_geography_identifiers
499 WHERE identifier_type='NAME'
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');
509
510 IF l_count > 0 THEN
511
512 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_DUPLICATE_VALUE');
513 FND_MESSAGE.SET_TOKEN('IDENT_TYPE', 'NAME');
514 FND_MESSAGE.SET_TOKEN('VALUE', p_child_name);
515 FND_MESSAGE.SET_TOKEN('GEO_ID', p_child_id);
516 FND_MESSAGE.SET_TOKEN('PARENT_GEO_ID', p_parent_id);
517 FND_MSG_PUB.ADD;
518 x_return_status := fnd_api.g_ret_sts_error;
519
520 END IF;
521
522 END check_duplicate_name;
523
524 -- This procudure checks for the duplicate code for a child_id with in its parent
525 PROCEDURE check_duplicate_code(
526 p_parent_id IN NUMBER,
527 p_child_id IN NUMBER,
528 p_child_code IN VARCHAR2,
529 p_child_type IN VARCHAR2,
530 p_child_identifier_subtype IN VARCHAR2,
531 p_child_language IN VARCHAR2,
532 x_return_status IN OUT NOCOPY VARCHAR2
533 ) IS
534
535 l_count NUMBER;
536
537 BEGIN
538
539 -- check if the name is duplicated with in the parent of p_child_id
540 -- Added Subtype and language check for bug 4703418 on 28-Nov-2005 (Nishant)
541 SELECT count(*) INTO l_count
542 FROM hz_geography_identifiers
543 WHERE identifier_type='CODE'
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');
553
554 IF l_count > 0 THEN
555
556 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_DUPLICATE_VALUE');
557 FND_MESSAGE.SET_TOKEN('IDENT_TYPE', 'CODE');
558 FND_MESSAGE.SET_TOKEN('VALUE', p_child_code);
559 FND_MESSAGE.SET_TOKEN('GEO_ID', p_child_id);
560 FND_MESSAGE.SET_TOKEN('PARENT_GEO_ID', p_parent_id);
561 FND_MSG_PUB.ADD;
562 x_return_status := fnd_api.g_ret_sts_error;
563
564 END IF;
565
566 END check_duplicate_code;
567
568
569
570
571
572
573 /*===========================================================================+
574 | PROCEDURE
575 |
576 |
577 | DESCRIPTION
578 | Creates a relation between master geographies
579 |
580 | SCOPE - PRIVATE
581 |
582 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
583 |
584 | ARGUMENTS : IN:
585 | p_master_relation_rec
586 | OUT:
587 | x_relationship_id
588 | x_return_status
589 |
590 | IN/ OUT:
591 |
592 | RETURNS : NONE
593 |
594 | NOTES
595 |
596 | MODIFICATION HISTORY
597 | 11-22-02 o Rekha Nalluri created.
598 | 07-04-07 o Neeraj Shinde. Bug#5393825
599 |
600 +===========================================================================*/
601
602 PROCEDURE do_create_master_relation(
603 p_master_relation_rec IN MASTER_RELATION_REC_TYPE,
604 x_relationship_id OUT NOCOPY NUMBER,
605 x_return_status IN OUT NOCOPY VARCHAR2
606 ) IS
607
608 l_relationship_rec HZ_RELATIONSHIP_V2PUB.relationship_rec_type;
609 l_status VARCHAR2(1);
610 l_parent_count NUMBER;
611 x_msg_count NUMBER;
612 x_msg_data VARCHAR2(2000);
613 l_geography_name VARCHAR2(360);
614 l_parent_geography_name VARCHAR2(360);
615 l_geography_type VARCHAR2(30);
616 l_geography_code VARCHAR2(30);
617 l_multiple_parent_flag VARCHAR2(1);
618 l_parent_geography_type VARCHAR2(30);
619 l_geo_element_col VARCHAR2(30);
620 x_party_id NUMBER;
621 x_party_number NUMBER;
622 l_country_code VARCHAR2(2);
623 l_count NUMBER;
624
625 --Parameters introduced for Bug#5393825 (Neeraj Shinde)
626 l_identifier_subtype VARCHAR2(30);
627 l_language_code VARCHAR2(4);
628
629
630 BEGIN
631 --dbms_output.put_line('before validate relation');
632 -- validate master relation record for create
633 HZ_GEOGRAPHY_VALIDATE_PVT.validate_master_relation(
634 p_master_relation_rec => p_master_relation_rec,
635 p_create_update_flag => 'C',
636 x_return_status => x_return_status
637 );
638
639 --dbms_output.put_line('After validate relation '||x_return_status);
640 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
641 RAISE FND_API.G_EXC_ERROR;
642 END IF;
643
644
645 -- get geography types for geography_id and parent_geography_id
646
647 /*l_geography_type := HZ_GEOGRAPHY_VALIDATE_PVT.get_geography_type(p_geography_id =>p_master_relation_rec.geography_id,
648 x_return_status => x_return_status);
649
650 --dbms_output.put_line('geography_type in do_create_master_relation:'||l_geography_type);
651 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
652 RAISE FND_API.G_EXC_ERROR;
653 END IF;*/
654
655 l_parent_geography_type := HZ_GEOGRAPHY_VALIDATE_PVT.get_geography_type(p_geography_id => p_master_relation_rec.parent_geography_id,
656 x_return_status => x_return_status);
657
658 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
659 RAISE FND_API.G_EXC_ERROR;
660 END IF;
661
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
671 -- identifier creation.*/
672
673 /* -- Introduced the validations to avoid child geographies with same name/code within
674 -- a parent geography.
675 -- Start of added section (Neeraj Shinde Bug# 5393825) */
676
677 IF l_geography_name IS NOT NULL THEN
678 /*check_duplicate_name(p_parent_id => p_master_relation_rec.parent_geography_id,
679 p_child_id => p_master_relation_rec.geography_id,
680 p_child_name => l_geography_name,
681 p_child_type => l_geography_type,
682 x_return_status => x_return_status
683 ); */
684 BEGIN
685 SELECT identifier_subtype,language_code INTO
686 l_identifier_subtype,l_language_code
687 FROM hz_geography_identifiers
688 WHERE geography_id = p_master_relation_rec.geography_id
689 AND identifier_type = 'NAME'
690 AND primary_flag = 'Y'
691 AND geography_use = 'MASTER_REF';
692
693 check_duplicate_name(p_parent_id => p_master_relation_rec.parent_geography_id,
694 p_child_id => p_master_relation_rec.geography_id,
695 p_child_name=> l_geography_name,
696 p_child_type => l_geography_type,
697 p_child_identifier_subtype => l_identifier_subtype,
698 p_child_language => l_language_code,
699 x_return_status => x_return_status
700 );
701 EXCEPTION
702 WHEN no_data_found THEN
703 -- The geography identifiers are not yet created
704 NULL;
705 END;
706
707 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
708 RAISE FND_API.G_EXC_ERROR;
709 END IF;
710 END IF;
711
712 IF l_geography_code IS NOT NULL THEN
713 /*
714 check_duplicate_code(p_parent_id => p_master_relation_rec.parent_geography_id,
715 p_child_id => p_master_relation_rec.geography_id,
716 p_child_code => l_geography_code,
717 p_child_type => l_geography_type,
718 x_return_status => x_return_status
719 ); */
720 BEGIN
721 SELECT identifier_subtype,language_code INTO
722 l_identifier_subtype,l_language_code
723 FROM hz_geography_identifiers
724 WHERE geography_id = p_master_relation_rec.geography_id
725 AND identifier_type = 'CODE'
726 AND primary_flag = 'Y'
727 AND geography_use = 'MASTER_REF';
728
729 check_duplicate_code(p_parent_id => p_master_relation_rec.parent_geography_id,
730 p_child_id => p_master_relation_rec.geography_id,
731 p_child_code => l_geography_code,
732 p_child_type => l_geography_type,
733 p_child_identifier_subtype => l_identifier_subtype,
734 p_child_language => l_language_code,
735 x_return_status => x_return_status
736 );
737 EXCEPTION
738 WHEN no_data_found THEN
739 -- The geography identifiers are not yet created.
740 NULL;
741 END;
742
743 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
744 RAISE FND_API.G_EXC_ERROR;
745 END IF;
746 END IF;
747
748 -- End of added section (Neeraj Shinde Bug# 5393825)
749
750 EXCEPTION when no_data_found THEN
751 fnd_message.set_name('AR', 'HZ_GEO_NO_RECORD');
752 fnd_message.set_token('TOKEN1','Geography record');
753 fnd_message.set_token('TOKEN2','geography_id '||p_master_relation_rec.geography_id);
754 fnd_msg_pub.add;
755 RAISE FND_API.G_EXC_ERROR;
756 END;
757 -- a geography can not have two countries as its parents
758 IF l_parent_geography_type = 'COUNTRY' THEN
759
760 SELECT count(*) INTO l_count FROM HZ_HIERARCHY_NODES
761 WHERE hierarchy_type='MASTER_REF'
762 AND child_id=p_master_relation_rec.geography_id
763 AND parent_object_type='COUNTRY'
764 AND NVL(status,'A') = 'A'
765 AND level_number = 1;
766
767 IF l_count > 0 THEN
768 fnd_message.set_name('AR', 'HZ_GEO_MULTIPLE_COUNTRIES');
769 fnd_message.set_token('GEO_ID', p_master_relation_rec.geography_id);
770 fnd_msg_pub.add;
771 RAISE FND_API.G_EXC_ERROR;
772 END IF;
773 END IF;
774
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';
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;
787 l_relationship_rec.status := 'A';
788 l_relationship_rec.created_by_module := p_master_relation_rec.created_by_module;
789 l_relationship_rec.application_id := p_master_relation_rec.application_id;
790
791 --dbms_output.put_line('After constructing the master relation record');
792
793 -- call to relationship API to create a relationship
794 HZ_RELATIONSHIP_V2PUB.create_relationship(
795 p_init_msg_list => 'F',
796 p_relationship_rec => l_relationship_rec,
797 x_relationship_id => x_relationship_id,
798 x_party_id => x_party_id,
799 x_party_number => x_party_number,
800 x_return_status => x_return_status,
801 x_msg_count => x_msg_count,
802 x_msg_data => x_msg_data,
803 p_create_org_contact => 'N'
804 );
805 --dbms_output.put_line('x_return_status is '||x_return_status);
806 --dbms_output.put_line('relationship_id is '||x_relationship_id);
807
808 ----dbms_output.put_line('parent_id is '||to_char(l_parent_count));
809
810 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
811 RAISE FND_API.G_EXC_ERROR;
812 END IF;
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'
822 AND relationship_type='MASTER_REF'
823 AND relationship_code = 'PARENT_OF'
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(
833 p_geography_id => p_master_relation_rec.geography_id,
834 p_parent_geography_id => p_master_relation_rec.parent_geography_id,
835 p_geography_type => l_geography_type,
836 x_return_status => x_return_status
837 );
838 --dbms_output.put_line ('after denormalize relation');
839
840
841 ELSIF l_parent_count > 1 THEN
842
843 --dbms_output.put_line ('before call to remove denormalize');
844 -- In case of multiple parents see if the multiple parents have same parent.If yes,remove denormalization
845 -- of the immediate parent information(in that particular geo_element column)for this geography and for all its
846 -- children. If no,then nullify the de-normalization in all the geo_element columns for this geography and for
847 -- all its children.
848
849 remove_denorm(
850 p_geography_id => p_master_relation_rec.geography_id,
851 p_geography_type => l_geography_type
852 );
853
854 END IF;
855
856 END do_create_master_relation;
857
858 /*===========================================================================+
859 | PROCEDURE
860 |
861 |
862 | DESCRIPTION
863 | Updates a relation between master geographies
864 |
865 | SCOPE - PRIVATE
866 |
867 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
868 |
869 | ARGUMENTS : IN:
870 |
871 | p_master_relation_rec
872 | p_object_version_number
873 | OUT:
874 |
875 | x_return_status
876 |
877 | IN/ OUT:
878 |
879 | RETURNS : NONE
880 |
881 | NOTES
882 |
883 | MODIFICATION HISTORY
884 | 11-22-02 o Rekha Nalluri created.
885 |
886 +===========================================================================*/
887
888 PROCEDURE do_update_relationship(
889 p_relationship_id IN NUMBER,
890 p_status IN VARCHAR2,
891 p_object_version_number IN OUT NOCOPY NUMBER,
892 x_return_status IN OUT NOCOPY VARCHAR2
893 ) IS
894
895 l_relationship_rec HZ_RELATIONSHIP_V2PUB.relationship_rec_type;
896 x_msg_count NUMBER;
897 x_msg_data VARCHAR2(2000);
898 l_party_object_version_number NUMBER := NULL;
899 l_parent_count NUMBER;
900 l_geography_type VARCHAR2(30);
901 l_update_flag VARCHAR2(1);
902 l_denorm_flag VARCHAR2(1);
903 l_geography_id NUMBER;
904 l_parent_geography_id NUMBER;
905 l_end_date DATE;
906 l_count NUMBER;
907 l_relationship_type VARCHAR2(30);
908 --p_subject_flag VARCHAR2(1);
909 CURSOR c_get_all_children IS
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
919 OR parent_id = child_id);
920 l_get_all_children c_get_all_children%ROWTYPE;
921 l_remove_denorm_flag VARCHAR2(1);
922 BEGIN
923
924 hz_utility_v2pub.validate_mandatory(
925 p_create_update_flag => 'U',
926 p_column => 'object_version_number',
927 p_column_value => p_object_version_number,
928 x_return_status => x_return_status
929 );
930
931 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
932 RAISE FND_API.G_EXC_ERROR;
933 END IF;
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
943 fnd_message.set_name('AR', 'HZ_GEO_NO_RECORD');
944 fnd_message.set_token('token1', 'relationship');
945 fnd_message.set_token('token1', 'relationship_id '||p_relationship_id);
946 fnd_msg_pub.add;
947 RAISE FND_API.G_EXC_ERROR;
948 END;
949
950 --dbms_output.put_line('after master relation validation');
951
952 IF p_status = 'I' THEN
953 l_end_date := sysdate;
954 ELSIF p_status = 'A' THEN
955 l_end_date := to_date('31-12-4712','DD-MM-YYYY');
956 END IF;
957
958
959 ----dbms_output.put_line('l_end_date is '|| to_char(l_end_date));
960 l_geography_type := HZ_GEOGRAPHY_VALIDATE_PVT.get_geography_type(
961 p_geography_id => l_geography_id,
962 x_return_status => x_return_status);
963
964 ----dbms_output.put_line('after getting geography type '||l_geography_type);
965 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
966 RAISE FND_API.G_EXC_ERROR;
967 END IF;
968
969 -- construct the relationship record for update
970 l_relationship_rec.relationship_id := p_relationship_id;
971 l_relationship_rec.start_date := NULL;
972 l_relationship_rec.end_date := l_end_date;
973 l_relationship_rec.status := p_status;
974 l_relationship_rec.created_by_module := NULL;
975 l_relationship_rec.application_id := NULL;
976
977 IF l_relationship_type <> 'MASTER_REF' THEN
978 HZ_RELATIONSHIP_V2PUB.update_relationship(
979 p_init_msg_list =>'F',
980 p_relationship_rec => l_relationship_rec,
981 p_object_version_number => p_object_version_number,
982 p_party_object_version_number => l_party_object_version_number,
983 x_return_status => x_return_status,
984 x_msg_count => x_msg_count,
985 x_msg_data => x_msg_data
986 );
987
988 ELSE
989
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'
999 AND relationship_type='MASTER_REF'
1000 AND relationship_code = 'PARENT_OF'
1001 AND status = 'A';
1002
1003 --dbms_output.put_line('l_parent_count '|| to_char(l_parent_count));
1004 EXCEPTION WHEN NO_DATA_FOUND THEN
1005 FND_MESSAGE.SET_NAME( 'AR', 'HZ_GEO_NO_RECORD' );
1006 FND_MESSAGE.SET_TOKEN( 'TOKEN1', 'Relationship');
1007 FND_MESSAGE.SET_TOKEN( 'TOKEN2', 'object_id '||l_geography_id||',object_type '||l_geography_type||' and relationship_type MASTER_REF');
1008 FND_MSG_PUB.ADD;
1009 RAISE FND_API.G_EXC_ERROR;
1010 END;
1011
1012
1013 -- if there exists only one parent then do not update
1014 IF (l_parent_count = 1 AND p_status = 'I') THEN
1015 FND_MESSAGE.SET_NAME( 'AR', 'HZ_GEO_SINGLE_PARENT' );
1016 FND_MESSAGE.SET_TOKEN( 'REL_ID', p_relationship_id);
1017 FND_MESSAGE.SET_TOKEN( 'GEO_ID', l_geography_id);
1018 FND_MSG_PUB.ADD;
1019 RAISE FND_API.G_EXC_ERROR;
1020 --l_update_flag := 'N';
1021 END IF;
1022 ----dbms_output.put_line ('parent_count '||to_char(l_parent_count));
1023 IF (l_parent_count = 2 AND p_status= 'I') THEN
1024 l_update_flag := 'Y';
1025 l_denorm_flag := 'Y';
1026 ELSIF (l_parent_count = 1 AND p_status = 'A') THEN
1027 l_update_flag := 'Y';
1028 l_remove_denorm_flag := 'Y';
1029 ELSIF l_parent_count > 2 THEN
1030 l_update_flag := 'Y';
1031 --l_remove_denorm_flag := 'Y';
1032 END IF;
1033 ----dbms_output.put_line('l_update_flag '||l_update_flag);
1034
1035 IF l_update_flag = 'Y' THEN
1036 -- call relationship API for update
1037 HZ_RELATIONSHIP_V2PUB.update_relationship(
1038 p_init_msg_list =>'F',
1039 p_relationship_rec => l_relationship_rec,
1040 p_object_version_number => p_object_version_number,
1041 p_party_object_version_number => l_party_object_version_number,
1042 x_return_status => x_return_status,
1043 x_msg_count => x_msg_count,
1044 x_msg_data => x_msg_data
1045 );
1046
1047 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1048 RAISE FND_API.G_EXC_ERROR;
1049 END IF;
1050 END IF;
1051
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;
1061
1062 OPEN c_get_all_children;
1063 LOOP
1064 FETCH c_get_all_children into l_get_all_children;
1065 EXIT WHEN c_get_all_children%NOTFOUND;
1066
1067 -- call the procedure to denormalize
1068 denormalize_relation(
1069 p_geography_id => l_get_all_children.child_id,
1070 p_parent_geography_id => l_geography_id,
1071 p_geography_type => l_get_all_children.child_object_type,
1072 x_return_status => x_return_status
1073 );
1074
1075 -- de-normalize the relation in the children of geography_id too
1076
1077 END LOOP;
1078 CLOSE 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(
1088 p_geography_id => l_geography_id,
1089 p_geography_type => l_geography_type);
1090 END IF;
1091 END IF;
1092 END do_update_relationship;
1093
1094 /*===========================================================================+
1095 | PROCEDURE
1096 | 3
1097 |
1098 | DESCRIPTION
1099 | Creates a Geography Identifier
1100 |
1101 | SCOPE - PRIVATE
1102 |
1103 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1104 |
1105 | ARGUMENTS : IN:
1106 |
1107 | p_geo_identifier_rec
1108 | OUT:
1109 | x_return_status
1110 |
1111 | IN/ OUT:
1112 |
1113 | RETURNS : NONE
1114 |
1115 | NOTES
1116 |
1117 | MODIFICATION HISTORY
1118 | 12-03-02 o Rekha Nalluri created.
1119 | 07-25-05 o Idris Ali Bug 4493925: language code is
1120 | assinged a default value when it has a null value
1121 +===========================================================================*/
1122
1123 PROCEDURE do_create_geo_identifier(
1124 p_geo_identifier_rec IN GEO_IDENTIFIER_REC_TYPE,
1125 x_return_status IN OUT NOCOPY VARCHAR2
1126 ) IS
1127
1128 l_rowid VARCHAR2(64);
1129 l_geography_use VARCHAR2(30);
1130 l_geography_type VARCHAR2(30);
1131 l_geography_id NUMBER;
1132 l_identifier_value VARCHAR2(360);
1133 l_geo_element_col VARCHAR2(30);
1134 l_country_code VARCHAR2(2);
1135 l_count NUMBER;
1136 --l_stmnt VARCHAR2(1000);
1137 l_geo_element_code VARCHAR2(30);
1138 l_identifier_subtype VARCHAR2(30);
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;
1148
1149 BEGIN
1150
1151 l_geography_id := p_geo_identifier_rec.geography_id;
1152
1153 IF p_geo_identifier_rec.identifier_type = 'CODE' THEN
1154 l_identifier_value := UPPER(p_geo_identifier_rec.identifier_value);
1155 ELSE
1156 l_identifier_value := p_geo_identifier_rec.identifier_value;
1157 END IF;
1158
1159 -- Bug 4493925: default language_code in case of NULL
1160
1161 IF p_geo_identifier_rec.language_code IS NULL THEN
1162 l_language_code := userenv('LANG');
1163 ELSE
1164 l_language_code := p_geo_identifier_rec.language_code;
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
1174
1175 -- validate geography identifier record for create
1176 HZ_GEOGRAPHY_VALIDATE_PVT.validate_geo_identifier(
1177 p_geo_identifier_rec => p_geo_identifier_rec,
1178 p_create_update_flag => 'C',
1179 x_return_status => x_return_status
1180 );
1181
1182 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1183 RAISE FND_API.G_EXC_ERROR;
1184 END IF;
1185
1186 -- check for the duplicate name and code
1187
1188
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');
1198 fnd_message.set_token('TOKEN1','Geography record');
1199 fnd_message.set_token('TOKEN2','geography_id '||p_geo_identifier_rec.geography_id);
1200 fnd_msg_pub.add;
1201 RAISE FND_API.G_EXC_ERROR;
1202 END;
1203
1204 IF l_geography_use = 'MASTER_REF' THEN
1205 IF l_geography_type <> 'COUNTRY' THEN
1206 -- check for the duplicate name/code with in the parents of the geography
1207 OPEN c_get_all_parents;
1208 LOOP
1209 FETCH c_get_all_parents INTO l_get_all_parents;
1210 EXIT WHEN c_get_all_parents%NOTFOUND;
1211 IF p_geo_identifier_rec.identifier_type = 'NAME' THEN
1212 check_duplicate_name(p_parent_id => l_get_all_parents.subject_id,
1213 p_child_id => p_geo_identifier_rec.geography_id,
1214 p_child_name=> p_geo_identifier_rec.identifier_value,
1215 p_child_type => l_geography_type,
1216 p_child_identifier_subtype => l_identifier_subtype,
1217 p_child_language => l_language_code,
1218 x_return_status => x_return_status
1219 );
1220 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1221 RAISE FND_API.G_EXC_ERROR;
1222 EXIT;
1223 END IF;
1224 ELSIF p_geo_identifier_rec.identifier_type = 'CODE' THEN
1225 check_duplicate_code(p_parent_id => l_get_all_parents.subject_id,
1226 p_child_id => p_geo_identifier_rec.geography_id,
1227 p_child_code => p_geo_identifier_rec.identifier_value,
1228 p_child_type => l_geography_type,
1229 p_child_identifier_subtype => l_identifier_subtype,
1230 p_child_language => l_language_code,
1231 x_return_status => x_return_status
1232 );
1233
1234 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1235 RAISE FND_API.G_EXC_ERROR;
1236 EXIT;
1237 END IF;
1238 END IF;
1239 END LOOP;
1240 CLOSE c_get_all_parents;
1241 ELSE
1242 -- Bug 5411429 : check for the duplicate name/code for Country geographies.
1243 SELECT count(*) INTO l_count
1244 FROM hz_geography_identifiers
1245 WHERE identifier_type = p_geo_identifier_rec.identifier_type
1246 AND identifier_subtype = l_identifier_subtype
1247 AND language_code = p_geo_identifier_rec.language_code
1248 AND UPPER(identifier_value) = UPPER(p_geo_identifier_rec.identifier_value)
1249 AND geography_type = 'COUNTRY';
1250
1251 IF l_count > 0 THEN
1252 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_DUP_COUNTRY_IDEN');
1253 FND_MESSAGE.SET_TOKEN('IDEN_VAL', p_geo_identifier_rec.identifier_value);
1254 FND_MSG_PUB.ADD;
1255 x_return_status := fnd_api.g_ret_sts_error;
1256 RAISE FND_API.G_EXC_ERROR;
1257 END IF;
1258 END IF;
1259
1260 END IF;
1261
1262 -- Primary name should be of subtype 'STANDARD_NAME'
1263 IF (p_geo_identifier_rec.identifier_type = 'NAME' AND p_geo_identifier_rec.primary_flag = 'Y') THEN
1264 IF p_geo_identifier_rec.identifier_subtype <> 'STANDARD_NAME' THEN
1265 FND_MESSAGE.SET_NAME( 'AR', 'HZ_GEO_INVALID_SUBTYPE' );
1266 FND_MESSAGE.SET_TOKEN( 'SUBTYPE', p_geo_identifier_rec.identifier_subtype);
1267 FND_MSG_PUB.ADD;
1268 x_return_status := FND_API.G_RET_STS_ERROR;
1269 RAISE FND_API.G_EXC_ERROR;
1270 END IF;
1271 END IF;
1272
1273 -- If this is the first row for the language_code for type NAME then if it is of subtype other than STANDAR_NAME,
1274 -- , make it as STANDARD_NAME
1275
1276 IF (p_geo_identifier_rec.identifier_type = 'NAME' AND p_geo_identifier_rec.identifier_subtype <> 'STANDARD_NAME') THEN
1277
1278 SELECT count(*) INTO l_count from
1279 hz_geography_identifiers
1280 WHERE geography_id = p_geo_identifier_rec.geography_id
1281 AND language_code = l_language_code;
1282
1283 IF l_count = 0 THEN
1284 l_identifier_subtype:= 'STANDARD_NAME';
1285 END IF;
1286 END IF;
1287
1288 IF p_geo_identifier_rec.primary_flag = 'Y' THEN
1289 IF (p_geo_identifier_rec.identifier_type='NAME' AND p_geo_identifier_rec.identifier_subtype = 'STANDARD_NAME') THEN
1290 -- check if there exists a STANDARD_NAME + Primary Flag = Y
1291 SELECT count(*) INTO l_count
1292 FROM hz_geography_identifiers
1293 WHERE geography_id = p_geo_identifier_rec.geography_id
1294 AND identifier_type = 'NAME'
1295 AND identifier_subtype = 'STANDARD_NAME'
1296 AND primary_flag = 'Y'
1297 AND language_code = l_language_code;
1298
1299 IF l_count > 0 THEN
1300 -- update STANDARD_NAME+Y to STANDARD_NAME+N
1301 UPDATE hz_geography_identifiers
1302 SET primary_flag = 'N'
1303 WHERE geography_id = p_geo_identifier_rec.geography_id
1304 AND identifier_type = 'NAME'
1305 AND identifier_subtype = 'STANDARD_NAME'
1306 AND primary_flag = 'Y'
1307 AND language_code = l_language_code;
1308 END IF;
1309
1310 l_identifier_subtype := 'STANDARD_NAME';
1311 END IF;
1312
1313 --check if there exists a primary row already for this geography_id
1314 SELECT count(*) INTO l_count
1315 FROM HZ_GEOGRAPHY_IDENTIFIERS
1316 WHERE geography_id = p_geo_identifier_rec.geography_id
1317 AND identifier_type = p_geo_identifier_rec.identifier_type
1318 AND primary_flag='Y';
1319
1320 IF l_count > 0 THEN
1321 -- set the primary_flag of the existing primary identifier to 'N'
1322 UPDATE hz_geography_identifiers
1323 SET primary_flag = 'N'
1324 WHERE geography_id=p_geo_identifier_rec.geography_id
1325 AND identifier_type = p_geo_identifier_rec.identifier_type
1326 AND primary_flag = 'Y';
1327
1328 END IF;
1329 END IF;
1330
1331 ----dbms_output.put_line('before identifier insert');
1332
1333 -- call table handler to insert the row in hz_geography_identifiers
1334
1335 HZ_GEOGRAPHY_IDENTIFIERS_PKG.insert_row(
1336 x_rowid => l_rowid,
1337 x_geography_id => p_geo_identifier_rec.geography_id,
1338 x_identifier_subtype => l_identifier_subtype,
1339 x_identifier_value => l_identifier_value,
1340 x_geo_data_provider => p_geo_identifier_rec.geo_data_provider,
1341 x_object_version_number => 1,
1342 x_identifier_type => p_geo_identifier_rec.identifier_type,
1343 x_primary_flag => p_geo_identifier_rec.primary_flag,
1344 x_language_code => UPPER(l_language_code),
1345 x_geography_use => l_geography_use,
1346 x_geography_type => UPPER(l_geography_type),
1347 x_created_by_module => p_geo_identifier_rec.created_by_module,
1348 x_application_id => p_geo_identifier_rec.application_id,
1349 x_program_login_id => NULL
1350 );
1351
1352 ----dbms_output.put_line('after identifier insert');
1353
1354 IF (l_geography_type <> 'COUNTRY' AND l_geography_use = 'MASTER_REF') THEN
1355 BEGIN
1356 --get geography_element_column,country_code from hz_geo_structure_levels for this geography_id
1357 --dbms_output.put_line('before getting geo_element_column l_geography_id '||l_geography_id);
1358
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';
1368 l_geo_element_id := l_geo_element_col||'_ID';
1369 --dbms_output.put_line('after getting geo_element_column'||l_geo_element_col);
1370 EXCEPTION
1371 WHEN NO_DATA_FOUND THEN
1372 --dbms_output.put_line('in the error');
1373 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_NO_RECORD');
1374 FND_MESSAGE.SET_TOKEN('TOKEN1', 'geography structure level');
1375 FND_MESSAGE.SET_TOKEN('TOKEN2', 'geography_id: '||to_char(l_count)||',country_code: '||l_country_code||',geography_type: '||l_geography_type);
1376 FND_MSG_PUB.ADD;
1377 x_return_status := FND_API.G_RET_STS_ERROR;
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
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
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
1401 WHERE geography_id = p_geo_identifier_rec.geography_id;
1402 -- END IF;
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
1412 END IF;
1413
1414 --dbms_output.put_line('After first execute');
1415
1416 END IF;
1417 END IF;
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 '||
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
1431 END IF;
1432 --dbms_output.put_line('After second execute');
1433 END IF;
1434 ----dbms_output.put_line('after de-normaloizing name');
1435 END IF;
1436
1437
1438 END do_create_geo_identifier;
1439
1440
1441 -- update geography identifier procedure
1442 PROCEDURE do_update_geo_identifier(
1443 p_geo_identifier_rec IN GEO_IDENTIFIER_REC_TYPE,
1444 p_object_version_number IN OUT NOCOPY NUMBER,
1445 x_cp_request_id OUT NOCOPY NUMBER,
1446 x_return_status IN OUT NOCOPY VARCHAR2
1447 )IS
1448
1449 l_count NUMBER;
1450 l_rowid VARCHAR2(64);
1451 l_geography_use VARCHAR2(30);
1452 l_geography_type VARCHAR2(30);
1453 l_geo_element_col VARCHAR2(30);
1454 l_country_code VARCHAR2(2);
1455 -- l_stmnt VARCHAR2(1000);
1456 l_geo_element_code VARCHAR2(30);
1457 l_geo_element_id VARCHAR2(30);
1458 l_old_primary_flag VARCHAR2(1);
1459 l_geo_identifier_subtype VARCHAR2(30);
1460 l_object_version_number NUMBER;
1461
1462 l_new_geo_subtype VARCHAR2(30);
1463 l_new_geo_value VARCHAR2(360);
1464 l_subtype_updated VARCHAR2(1);
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';
1474
1475 l_get_all_parents c_get_all_parents%ROWTYPE;
1476
1477 BEGIN
1478
1479 l_geo_identifier_subtype := p_geo_identifier_rec.identifier_subtype;
1480
1481 HZ_GEOGRAPHY_VALIDATE_PVT.validate_geo_identifier(
1482 p_geo_identifier_rec => p_geo_identifier_rec,
1483 p_create_update_flag => 'U',
1484 x_return_status => x_return_status
1485 );
1486
1487 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1488 RAISE FND_API.G_EXC_ERROR;
1489 END IF;
1490
1491 hz_utility_v2pub.validate_mandatory(
1492 p_create_update_flag => 'U',
1493 p_column => 'object_version_number',
1494 p_column_value => p_object_version_number,
1495 x_return_status => x_return_status
1496 );
1497 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1498 RAISE FND_API.G_EXC_ERROR;
1499 END IF;
1500
1501 BEGIN
1502
1503 SELECT rowid,geography_type,geography_use,primary_flag,object_version_number
1504 INTO l_rowid,l_geography_type,l_geography_use,l_old_primary_flag,l_object_version_number
1505 FROM hz_geography_identifiers
1506 WHERE geography_id = p_geo_identifier_rec.geography_id
1507 AND identifier_type = p_geo_identifier_rec.identifier_type
1508 AND identifier_subtype = p_geo_identifier_rec.identifier_subtype
1509 AND identifier_value = p_geo_identifier_rec.identifier_value
1510 AND language_code = p_geo_identifier_rec.language_code
1511 FOR UPDATE of geography_id NOWAIT;
1512
1513 --validate object_version_number
1514 IF l_object_version_number <> p_object_version_number THEN
1515 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
1516 FND_MESSAGE.SET_TOKEN('TABLE', 'hz_geography_identifiers');
1517 FND_MSG_PUB.ADD;
1518 RAISE FND_API.G_EXC_ERROR;
1519 ELSE
1520 p_object_version_number := l_object_version_number + 1;
1521 END IF;
1522
1523 EXCEPTION WHEN NO_DATA_FOUND THEN
1524 FND_MESSAGE.SET_NAME( 'AR', 'HZ_GEO_NO_RECORD' );
1525 FND_MESSAGE.SET_TOKEN('TOKEN1', 'geography_identifier');
1526 FND_MESSAGE.SET_TOKEN('TOKEN2', 'geography_id: '||p_geo_identifier_rec.geography_id||', identifier_type: '||
1527 p_geo_identifier_rec.identifier_type||', identifier_subtype: '||p_geo_identifier_rec.identifier_subtype||', identifier_value: '||
1528 p_geo_identifier_rec.identifier_value||', language_code: '||p_geo_identifier_rec.language_code);
1529 FND_MSG_PUB.ADD;
1530 RAISE FND_API.G_EXC_ERROR;
1531 END;
1532
1533 -- Validate new subtype. Update only if valid and geo type is CODE.
1534 IF (p_geo_identifier_rec.identifier_type = 'CODE') THEN
1535 l_new_geo_subtype := p_geo_identifier_rec.new_identifier_subtype;
1536 -- validate new subtype
1537 IF (l_new_geo_subtype IS NOT NULL) THEN
1538 HZ_UTILITY_V2PUB.validate_lookup(
1539 p_column => 'geography_code_type',
1540 p_lookup_type => 'HZ_GEO_IDENTIFIER_SUBTYPE',
1541 p_column_value => l_new_geo_subtype,
1542 x_return_status => x_return_status
1543 );
1544 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1545 RAISE FND_API.G_EXC_ERROR;
1546 END IF;
1547 l_subtype_updated := 'Y';
1548 ELSE -- new geo subtype is null (i.e. no need to update), use the old subtype
1549 l_new_geo_subtype := l_geo_identifier_subtype;
1550 l_subtype_updated := 'N';
1551 END IF;
1552 ELSE -- if idenifier type is NAME (only 1 subtype 'STANDARD_NAME' is allowed)
1553 l_new_geo_subtype := p_geo_identifier_rec.new_identifier_subtype;
1554 -- validate new subtype
1555 IF (l_new_geo_subtype IS NOT NULL) THEN
1556 IF (p_geo_identifier_rec.new_identifier_subtype <> 'STANDARD_NAME') THEN
1557 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_INVALID_LOOKUP' );
1558 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'identifier_subtype' );
1559 FND_MESSAGE.SET_TOKEN( 'LOOKUP_TYPE', 'HZ_GEO_IDENTIFIER_SUBTYPE' );
1560 FND_MSG_PUB.ADD;
1561 x_return_status := FND_API.G_RET_STS_ERROR;
1562 END IF;
1563 l_subtype_updated := 'Y';
1564 ELSE -- new geo subtype is null (i.e. no need to update), use the old subtype
1565 l_new_geo_subtype := l_geo_identifier_subtype;
1566 l_subtype_updated := 'N';
1567 END IF;
1568 END IF;
1569
1570 -- Validate new identifier value (it will be NULL if it is not to be updated)
1571 IF (p_geo_identifier_rec.new_identifier_value IS NOT NULL) THEN
1572 IF p_geo_identifier_rec.identifier_type = 'CODE' THEN
1573 l_new_geo_value := UPPER(p_geo_identifier_rec.new_identifier_value);
1574 ELSE
1575 l_new_geo_value := p_geo_identifier_rec.new_identifier_value;
1576 END IF;
1577 l_name_updated := 'Y';
1578 ELSE -- not to be updated, so retain the old value
1579 l_new_geo_value := p_geo_identifier_rec.identifier_value;
1580 l_name_updated := 'N';
1581 END IF;
1582
1583 -- check if name is duplicate within its parents
1584 IF (l_name_updated = 'Y') THEN
1585 IF l_geography_use = 'MASTER_REF' THEN
1586 IF l_geography_type <> 'COUNTRY' THEN
1587 -- check for the duplicate name/code with in the parents of the geography
1588 OPEN c_get_all_parents;
1589 LOOP
1590 FETCH c_get_all_parents INTO l_get_all_parents;
1591 EXIT WHEN c_get_all_parents%NOTFOUND;
1592 IF p_geo_identifier_rec.identifier_type = 'NAME' THEN
1593 -- check if the name is duplicated with in the parent of p_child_id
1594 -- Added Subtype and language check for bug 4703418 on 28-Nov-2005 (Nishant)
1595 SELECT count(*) INTO l_count
1596 FROM hz_geography_identifiers
1597 WHERE identifier_type='NAME'
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')
1607 AND ROWID <> l_rowid ;
1608
1609 IF l_count > 0 THEN
1610
1611 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_DUPLICATE_VALUE');
1612 FND_MESSAGE.SET_TOKEN('IDENT_TYPE', 'NAME');
1613 FND_MESSAGE.SET_TOKEN('VALUE', l_new_geo_value);
1614 FND_MESSAGE.SET_TOKEN('GEO_ID', p_geo_identifier_rec.geography_id);
1615 FND_MESSAGE.SET_TOKEN('PARENT_GEO_ID', l_get_all_parents.subject_id);
1616 FND_MSG_PUB.ADD;
1617 x_return_status := fnd_api.g_ret_sts_error;
1618 RAISE FND_API.G_EXC_ERROR;
1619 EXIT;
1620 END IF;
1621 ELSIF p_geo_identifier_rec.identifier_type = 'CODE' THEN
1622 -- check if the name is duplicated with in the parent of p_child_id
1623 -- Added Subtype and language check for bug 4703418 on 28-Nov-2005 (Nishant)
1624 SELECT count(*) INTO l_count
1625 FROM hz_geography_identifiers
1626 WHERE identifier_type='CODE'
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')
1636 AND ROWID <> l_rowid ;
1637
1638 IF l_count > 0 THEN
1639
1640 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_DUPLICATE_VALUE');
1641 FND_MESSAGE.SET_TOKEN('IDENT_TYPE', 'CODE');
1642 FND_MESSAGE.SET_TOKEN('VALUE', l_new_geo_value);
1643 FND_MESSAGE.SET_TOKEN('GEO_ID', p_geo_identifier_rec.geography_id);
1644 FND_MESSAGE.SET_TOKEN('PARENT_GEO_ID', l_get_all_parents.subject_id);
1645 FND_MSG_PUB.ADD;
1646 x_return_status := fnd_api.g_ret_sts_error;
1647 RAISE FND_API.G_EXC_ERROR;
1648 EXIT;
1649 END IF;
1650 END IF;
1651 END LOOP;
1652 CLOSE c_get_all_parents;
1653 ELSE
1654 -- Bug 5411429 : check for the duplicate name/code for Country geographies.
1655 SELECT count(*) INTO l_count
1656 FROM hz_geography_identifiers
1657 WHERE identifier_type = p_geo_identifier_rec.identifier_type
1658 AND identifier_subtype = l_new_geo_subtype
1659 AND language_code = p_geo_identifier_rec.language_code
1660 AND UPPER(identifier_value) = UPPER(l_new_geo_value)
1661 AND geography_type = 'COUNTRY'
1662 AND rowid <> l_rowid;
1663
1664 IF l_count > 0 THEN
1665 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_DUP_COUNTRY_IDEN');
1666 FND_MESSAGE.SET_TOKEN('IDEN_VAL', l_new_geo_value);
1667 FND_MSG_PUB.ADD;
1668 x_return_status := fnd_api.g_ret_sts_error;
1669 RAISE FND_API.G_EXC_ERROR;
1670 END IF;
1671
1672 END IF;
1673
1674 END IF;
1675 END IF;
1676
1677 --do not allow the updation of primary_flag from 'Y' to 'N'
1678 IF (l_old_primary_flag = 'Y' AND p_geo_identifier_rec.primary_flag = 'N') THEN
1679 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_COLUMN' );
1680 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'primary_flag from Y to N');
1681 FND_MSG_PUB.ADD;
1682 x_return_status := FND_API.G_RET_STS_ERROR;
1683 RAISE FND_API.G_EXC_ERROR;
1684 END IF;
1685
1686 -- for updating primary_flag from 'N' to 'Y'
1687 IF (l_old_primary_flag = 'N' AND p_geo_identifier_rec.primary_flag = 'Y') THEN
1688
1689 --check if there exists a primary row already for this geography_id
1690 SELECT count(*) INTO l_count
1691 FROM HZ_GEOGRAPHY_IDENTIFIERS
1692 WHERE geography_id = p_geo_identifier_rec.geography_id
1693 AND identifier_type = p_geo_identifier_rec.identifier_type
1694 AND primary_flag='Y'
1695 AND language_code = p_geo_identifier_rec.language_code;
1696
1697 -- --dbms_output.put_line ( 'l_count for primary row '||to_char(l_count));
1698 IF l_count > 0 THEN
1699 -- set the primary_flag of the existing primary identifier to 'N'
1700 UPDATE hz_geography_identifiers
1701 SET primary_flag = 'N'
1702 WHERE geography_id=p_geo_identifier_rec.geography_id
1703 AND identifier_type = p_geo_identifier_rec.identifier_type
1704 AND primary_flag = 'Y'
1705 AND language_code = p_geo_identifier_rec.language_code;
1706 -- --dbms_output.put_line ( 'After update of primary from Y to N');
1707 END IF;
1708 END IF;
1709
1710 hz_geography_identifiers_pkg.update_row(
1711 x_rowid => l_rowid,
1712 x_geography_id => p_geo_identifier_rec.geography_id,
1713 x_identifier_subtype => l_new_geo_subtype,
1714 x_identifier_value => l_new_geo_value,
1715 x_geo_data_provider => p_geo_identifier_rec.geo_data_provider,
1716 x_object_version_number => p_object_version_number,
1717 x_identifier_type => p_geo_identifier_rec.identifier_type,
1718 x_primary_flag => p_geo_identifier_rec.primary_flag,
1719 x_language_code => p_geo_identifier_rec.language_code,
1720 x_geography_use => NULL,
1721 x_geography_type => NULL,
1722 x_created_by_module => NULL,
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'))
1732 )
1733 )
1734 THEN
1735 x_cp_request_id := fnd_request.submit_request(
1736 application => 'AR',
1737 program => 'ARHGEOEU',
1738 argument1 => p_geo_identifier_rec.geography_id,
1739 argument2 => p_geo_identifier_rec.identifier_type,
1740 argument3 => l_new_geo_value);
1741 ELSIF ((l_geography_use = 'TAX') AND
1742 ((l_old_primary_flag = 'N' AND p_geo_identifier_rec.primary_flag = 'Y') OR
1743 ((l_old_primary_flag = 'Y' AND p_geo_identifier_rec.primary_flag = 'Y') AND
1744 (l_name_updated = 'Y'))
1745 )
1746 )
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
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;
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;
1767 END IF;
1768
1769 END do_update_geo_identifier;
1770
1771 -- delete geography identifier
1772 PROCEDURE do_delete_geo_identifier(
1773 p_geography_id IN NUMBER,
1774 p_identifier_type IN VARCHAR2,
1775 p_identifier_subtype IN VARCHAR2,
1776 p_identifier_value IN VARCHAR2,
1777 p_language_code IN VARCHAR2,
1778 x_return_status IN OUT NOCOPY VARCHAR2
1779 ) IS
1780
1781 l_primary_flag VARCHAR2(1);
1782 l_count NUMBER;
1783 l_delete_flag VARCHAR2(1);
1784
1785 BEGIN
1786
1787 l_delete_flag := 'Y';
1788
1789 -- primary identifier can not be deleted
1790 SELECT primary_flag INTO l_primary_flag
1791 FROM hz_geography_identifiers
1792 WHERE geography_id = p_geography_id
1793 AND identifier_type = p_identifier_type
1794 AND identifier_subtype = p_identifier_subtype
1795 AND identifier_value = p_identifier_value
1796 AND language_code = p_language_code;
1797
1798 IF l_primary_flag = 'Y' THEN
1799 l_delete_flag := 'N';
1800 FND_MESSAGE.SET_NAME( 'AR', 'HZ_GEO_NONDELETEABLE' );
1801 FND_MSG_PUB.ADD;
1802 x_return_status := FND_API.G_RET_STS_ERROR;
1803 RAISE FND_API.G_EXC_ERROR;
1804 END IF;
1805
1806 -- If a STANDARD_NAME is being deleted , if there exists another name mark it as STANDARD and delete
1807 -- this row else if another name doesn't exist then delete the row.
1808 IF (p_identifier_type = 'NAME' AND p_identifier_subtype = 'STANDARD_NAME') THEN
1809 select count(*) INTO l_count
1810 from hz_geography_identifiers
1811 where geography_id = p_geography_id
1812 AND language_code = p_language_code
1813 AND identifier_type = 'NAME'
1814 ;
1815 IF l_count > 1 THEN
1816 -- update an identifier to STANDARD
1817 UPDATE hz_geography_identifiers
1818 SET identifier_subtype = 'STANDARD_NAME'
1819 WHERE geography_id = p_geography_id
1820 AND identifier_type= p_identifier_type
1821 AND identifier_subtype <> p_identifier_subtype
1822 AND identifier_value <> p_identifier_value
1823 AND language_code = p_language_code
1824 AND rownum < 2;
1825 l_delete_flag := 'Y';
1826 ELSE
1827 l_delete_flag := 'Y';
1828 END IF;
1829 END IF;
1830
1831 IF l_delete_flag = 'Y' THEN
1832 HZ_GEOGRAPHY_IDENTIFIERS_PKG.delete_row(
1833 x_geography_id => p_geography_id,
1834 x_identifier_subtype => p_identifier_subtype,
1835 x_identifier_value => p_identifier_value,
1836 x_language_code => p_language_code,
1837 x_identifier_type => p_identifier_type
1838 );
1839 END IF;
1840
1841 EXCEPTION WHEN NO_DATA_FOUND THEN
1842 FND_MESSAGE.SET_NAME( 'AR', 'HZ_GEO_NO_RECORD' );
1843 FND_MESSAGE.SET_TOKEN('TOKEN1', 'geography_identifier');
1844 FND_MESSAGE.SET_TOKEN('TOKEN2', 'geography_id: '||p_geography_id||', identifier_type: '||
1845 p_identifier_type||', identifier_subtype: '||p_identifier_subtype||', identifier_value: '||
1846 p_identifier_value||', language_code: '||p_language_code);
1847 FND_MSG_PUB.ADD;
1848 RAISE FND_API.G_EXC_ERROR;
1849
1850 END do_delete_geo_identifier;
1851
1852
1853 -- create Master Geography
1854 PROCEDURE do_create_master_geography(
1855 p_master_geography_rec IN MASTER_GEOGRAPHY_REC_TYPE,
1856 x_geography_id OUT NOCOPY NUMBER,
1857 x_return_status IN OUT NOCOPY VARCHAR2
1858 ) IS
1859
1860 l_count NUMBER;
1861 l_parent_geography_tbl HZ_GEOGRAPHY_PUB.parent_geography_tbl_type;
1862 l_rowid VARCHAR2(64);
1863 l_country_code VARCHAR2(2);
1864 l_master_relation_rec MASTER_RELATION_REC_TYPE;
1865 l_geo_identifier_rec GEO_IDENTIFIER_REC_TYPE;
1866 l_child_geography_id NUMBER;
1867 x_relationship_id NUMBER;
1868 x_msg_count NUMBER;
1869 x_msg_data VARCHAR2(2000);
1870 l_last NUMBER;
1871
1872 BEGIN
1873
1874 --l_country_count := 0;
1875 l_parent_geography_tbl := p_master_geography_rec.parent_geography_id;
1876
1877
1878 -- dbms_output.put_line('In do_create_master_geography, before validate');
1879 -- validate master geography record
1880 HZ_GEOGRAPHY_VALIDATE_PVT.validate_master_geography(
1881 p_master_geography_rec => p_master_geography_rec,
1882 p_create_update_flag => 'C',
1883 x_return_status => x_return_status
1884 );
1885
1886 --dbms_output.put_line('In do_create_master_geography , after validate_master_geography '|| x_return_status);
1887
1888 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1889 RAISE FND_API.G_EXC_ERROR;
1890 END IF;
1891
1892 -- determine the country_code
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');
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),
1908 x_geography_name => p_master_geography_rec.geography_name,
1909 x_geography_use => 'MASTER_REF',
1910 x_geography_code => UPPER(p_master_geography_rec.geography_code),
1911 x_start_date => p_master_geography_rec.start_date,
1912 x_end_date => p_master_geography_rec.end_date,
1913 x_multiple_parent_flag => 'N',
1914 x_created_by_module => p_master_geography_rec.created_by_module,
1915 x_country_code => l_country_code,
1916 x_geography_element1 => NULL,
1917 x_geography_element1_id => NULL,
1918 x_geography_element1_code => NULL,
1919 x_geography_element2 => NULL,
1920 x_geography_element2_id => NULL,
1921 x_geography_element2_code => NULL,
1922 x_geography_element3 => NULL,
1923 x_geography_element3_id => NULL,
1924 x_geography_element3_code => NULL,
1925 x_geography_element4 => NULL,
1926 x_geography_element4_id => NULL,
1927 x_geography_element4_code => NULL,
1928 x_geography_element5 => NULL,
1929 x_geography_element5_id => NULL,
1930 x_geography_element5_code => NULL,
1931 x_geography_element6 => NULL,
1932 x_geography_element6_id => NULL,
1933 x_geography_element7 => NULL,
1934 x_geography_element7_id => NULL,
1935 x_geography_element8 => NULL,
1936 x_geography_element8_id => NULL,
1937 x_geography_element9 => NULL,
1938 x_geography_element9_id => NULL,
1939 x_geography_element10 => NULL,
1940 x_geography_element10_id => NULL,
1941 x_geometry => p_master_geography_rec.geometry,
1942 x_timezone_code => p_master_geography_rec.timezone_code,
1943 x_application_id => p_master_geography_rec.application_id,
1944 x_program_login_id => NULL,
1945 x_attribute_category => NULL,
1946 x_attribute1 => NULL,
1947 x_attribute2 => NULL,
1948 x_attribute3 => NULL,
1949 x_attribute4 => NULL,
1950 x_attribute5 => NULL,
1951 x_attribute6 => NULL,
1952 x_attribute7 => NULL,
1953 x_attribute8 => NULL,
1954 x_attribute9 => NULL,
1955 x_attribute10 => NULL,
1956 x_attribute11 => NULL,
1957 x_attribute12 => NULL,
1958 x_attribute13 => NULL,
1959 x_attribute14 => NULL,
1960 x_attribute15 => NULL,
1961 x_attribute16 => NULL,
1962 x_attribute17 => NULL,
1963 x_attribute18 => NULL,
1964 x_attribute19 => NULL,
1965 x_attribute20 => NULL
1966 );
1967
1968 BEGIN
1969
1970 l_last := l_parent_geography_tbl.last;
1971 IF l_last > 0 THEN
1972 FOR i in 1 .. l_last LOOP
1973
1974 BEGIN
1975
1976 IF l_parent_geography_tbl.exists(i) = TRUE THEN
1977 -- construct master relation record
1978 l_master_relation_rec.geography_id := x_geography_id;
1979 l_master_relation_rec.parent_geography_id := l_parent_geography_tbl(i);
1980 l_master_relation_rec.start_date := p_master_geography_rec.start_date;
1981 l_master_relation_rec.end_date := p_master_geography_rec.end_date;
1982 l_master_relation_rec.created_by_module := p_master_geography_rec.created_by_module;
1983 l_master_relation_rec.application_id := p_master_geography_rec.application_id;
1984
1985 -- call relationship API to create relationship between geography_id and parent_geography_id
1986 create_master_relation(
1987 p_init_msg_list => 'F',
1988 p_master_relation_rec => l_master_relation_rec,
1989 x_relationship_id => x_relationship_id,
1990 x_return_status => x_return_status,
1991 x_msg_count => x_msg_count,
1992 x_msg_data => x_msg_data
1993 );
1994 END IF;
1995 END;
1996 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1997 RAISE FND_API.G_EXC_ERROR;
1998 END IF;
1999 -- --dbms_output.put_line('after create_master_relation id is '|| to_char(x_relationship_id));
2000 END LOOP;
2001 END IF;
2002 END;
2003
2004
2005 -- create an identifier for this geography in HZ_GEOGRAPHY_IDENTIFIERS
2006 -- construct Identifier record for identifier_type 'NAME'/'CODE'
2007 l_geo_identifier_rec.geography_id := x_geography_id;
2008 l_geo_identifier_rec.identifier_subtype := 'STANDARD_NAME';
2009 l_geo_identifier_rec.identifier_value := p_master_geography_rec.geography_name;
2010 l_geo_identifier_rec.identifier_type := 'NAME';
2011 l_geo_identifier_rec.geo_data_provider := p_master_geography_rec.geo_data_provider;
2012 l_geo_identifier_rec.primary_flag := 'Y';
2013 l_geo_identifier_rec.language_code := p_master_geography_rec.language_code;
2014 l_geo_identifier_rec.created_by_module := p_master_geography_rec.created_by_module;
2015 l_geo_identifier_rec.application_id := p_master_geography_rec.application_id;
2016
2017
2018 -- call to create Identifier API
2019 create_geo_identifier(
2020 p_init_msg_list => 'F',
2021 p_geo_identifier_rec => l_geo_identifier_rec,
2022 x_return_status => x_return_status,
2023 x_msg_count => x_msg_count,
2024 x_msg_data => x_msg_data
2025 );
2026
2027 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2028 RAISE FND_API.G_EXC_ERROR;
2029 END IF;
2030 --dbms_output.put_line('after creating name identifier');
2031
2032 IF p_master_geography_rec.geography_code IS NOT NULL
2033 -- Bug 4579847 : do not call for g_miss value for code
2034 and p_master_geography_rec.geography_code <> fnd_api.g_miss_char THEN
2035
2036 -- create an identifier for this geography for identifier_type 'CODE'
2037 l_geo_identifier_rec.identifier_subtype := p_master_geography_rec.geography_code_type;
2038 l_geo_identifier_rec.identifier_value := UPPER(p_master_geography_rec.geography_code);
2039 l_geo_identifier_rec.identifier_type := 'CODE';
2040
2041 --dbms_output.put_line('after constructing the code identifier record');
2042 -- call to create Identifier API
2043 create_geo_identifier(
2044 p_init_msg_list => 'F',
2045 p_geo_identifier_rec => l_geo_identifier_rec,
2046 x_return_status => x_return_status,
2047 x_msg_count => x_msg_count,
2048 x_msg_data => x_msg_data
2049 );
2050
2051 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2052 RAISE FND_API.G_EXC_ERROR;
2053 END IF;
2054 END IF;
2055
2056 END do_create_master_geography;
2057
2058
2059 --Update master geography
2060 PROCEDURE do_update_geography(
2061 p_geography_id IN NUMBER,
2062 p_end_date IN DATE,
2063 p_geometry IN MDSYS.SDO_GEOMETRY,
2064 p_timezone_code IN VARCHAR2,
2065 p_object_version_number IN OUT NOCOPY NUMBER,
2066 x_return_status IN OUT NOCOPY VARCHAR2
2067 ) IS
2068
2069 l_rowid VARCHAR2(64);
2070 x_msg_count NUMBER;
2071 x_msg_data VARCHAR2(2000);
2072 l_status VARCHAR2(1);
2073 l_start_date DATE;
2074 l_end_date DATE;
2075 l_geography_use VARCHAR2(30);
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
2085 AND status = 'A'; --Bug5454824
2086
2087 -- for l_geogrpahy_use = 'TAX' this cursor will read only Active records
2088 -- whose end_date is changed.
2089 -- but for l_geogrpahy_use = 'MASTER_REF' this will read inActive records also
2090
2091 l_get_all_relationships c_get_all_relationships%ROWTYPE;
2092 l_party_object_version_number NUMBER := 1;
2093 l_object_version_number NUMBER;
2094
2095 BEGIN
2096
2097 hz_utility_v2pub.validate_mandatory(
2098 p_create_update_flag => 'U',
2099 p_column => 'object_version_number',
2100 p_column_value => p_object_version_number,
2101 x_return_status => x_return_status
2102 );
2103
2104 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2105 RAISE FND_API.G_EXC_ERROR;
2106 END IF;
2107
2108 BEGIN
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
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;
2125 END IF;
2126
2127 EXCEPTION WHEN NO_DATA_FOUND THEN
2128 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_NO_RECORD');
2129 FND_MESSAGE.SET_TOKEN('TOKEN1','geography');
2130 FND_MESSAGE.SET_TOKEN('TOKEN2', 'geography_id '||p_geography_id);
2131 FND_MSG_PUB.ADD;
2132 RAISE FND_API.G_EXC_ERROR;
2133 END;
2134 --dbms_output.put_line('start date and old end date and end date '||to_char(l_start_date,'dd-mon-yyyy')||'*'||to_char(l_end_date,'dd-mon-yyyy')||'*'||to_char(p_end_date,'dd-mon-yyyy')||'*');
2135 --dbms_output.put_line('After date validation '|| x_return_status);
2136 -- check whether end_date >= start_date
2137 HZ_UTILITY_V2PUB.validate_start_end_date(
2138 p_create_update_flag => 'U',
2139 p_start_date_column_name => 'start_date',
2140 p_start_date => l_start_date,
2141 p_old_start_date => l_start_date,
2142 p_end_date_column_name => 'end_date',
2143 p_end_date => p_end_date,
2144 p_old_end_date => l_end_date,
2145 x_return_status => x_return_status
2146 );
2147
2148 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2149 RAISE FND_API.G_EXC_ERROR;
2150 END IF;
2151 -- in case g_miss_date is passed for end_date, assign the default future date to end_date
2152 l_end_date := p_end_date;
2153
2154 IF l_end_date = fnd_api.g_miss_date THEN
2155 l_end_date := to_date('31-12-4712','DD-MM-YYYY');
2156 END IF;
2157
2158 -- dbms_output.put_line('timezone_code is '||p_timezone_code);
2159 -- validate timezone_code for FK to FND_TIMEZONES
2160 IF p_timezone_code IS NOT NULL THEN
2161
2162 SELECT count(*) INTO l_count
2163 FROM FND_TIMEZONES_B
2164 WHERE timezone_code = p_timezone_code
2165 AND rownum <2;
2166
2167 IF l_count = 0 THEN
2168 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
2169 fnd_message.set_token('FK', 'timezone_code');
2170 fnd_message.set_token('COLUMN','timezone_code');
2171 fnd_message.set_token('TABLE','FND_TIMEZONES_B');
2172 fnd_msg_pub.add;
2173 RAISE FND_API.G_EXC_ERROR;
2174 END IF;
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,
2184 x_geography_name => NULL,
2185 x_geography_use => NULL,
2186 x_geography_code => NULL,
2187 x_start_date => NULL,
2188 x_end_date => l_end_date,
2189 x_multiple_parent_flag => NULL,
2190 x_created_by_module => NULL,
2191 x_country_code => NULL,
2192 x_geography_element1 => NULL,
2193 x_geography_element1_id => NULL,
2194 x_geography_element1_code => NULL,
2195 x_geography_element2 => NULL,
2196 x_geography_element2_id => NULL,
2197 x_geography_element2_code => NULL,
2198 x_geography_element3 => NULL,
2199 x_geography_element3_id => NULL,
2200 x_geography_element3_code => NULL,
2201 x_geography_element4 => NULL,
2202 x_geography_element4_id => NULL,
2203 x_geography_element4_code => NULL,
2204 x_geography_element5 => NULL,
2205 x_geography_element5_id => NULL,
2206 x_geography_element5_code => NULL,
2207 x_geography_element6 => NULL,
2208 x_geography_element6_id => NULL,
2209 x_geography_element7 => NULL,
2210 x_geography_element7_id => NULL,
2211 x_geography_element8 => NULL,
2212 x_geography_element8_id => NULL,
2213 x_geography_element9 => NULL,
2214 x_geography_element9_id => NULL,
2215 x_geography_element10 => NULL,
2216 x_geography_element10_id => NULL,
2217 x_geometry => p_geometry,
2218 x_timezone_code => p_timezone_code,
2219 x_application_id => NULL,
2220 x_program_login_id => NULL,
2221 x_attribute_category => NULL,
2222 x_attribute1 => NULL,
2223 x_attribute2 => NULL,
2224 x_attribute3 => NULL,
2225 x_attribute4 => NULL,
2226 x_attribute5 => NULL,
2227 x_attribute6 => NULL,
2228 x_attribute7 => NULL,
2229 x_attribute8 => NULL,
2230 x_attribute9 => NULL,
2231 x_attribute10 => NULL,
2232 x_attribute11 => NULL,
2233 x_attribute12 => NULL,
2234 x_attribute13 => NULL,
2235 x_attribute14 => NULL,
2236 x_attribute15 => NULL,
2237 x_attribute16 => NULL,
2238 x_attribute17 => NULL,
2239 x_attribute18 => NULL,
2240 x_attribute19 => NULL,
2241 x_attribute20 => NULL
2242 );
2243
2244
2245 IF l_end_date <= sysdate THEN
2246 l_status := 'I';
2247 ELSIF l_end_date > sysdate THEN
2248 l_status := 'A';
2249 ELSIF l_end_date = NULL THEN
2250 l_status :=NULL;
2251 END IF;
2252
2253 -- construct the relationship record for update
2254 --l_relationship_rec.relationship_id := p_relationship_id;
2255 l_relationship_rec.start_date := NULL;
2256 l_relationship_rec.end_date := l_end_date;
2257 l_relationship_rec.status := l_status;
2258 l_relationship_rec.created_by_module := NULL;
2259 l_relationship_rec.application_id := NULL;
2260
2261 -- update relationships with end_date wherever this geography_id is used
2262
2263 OPEN c_get_all_relationships;
2264 LOOP
2265 FETCH c_get_all_relationships INTO l_get_all_relationships;
2266 EXIT WHEN c_get_all_relationships%NOTFOUND;
2267
2268 l_relationship_rec.relationship_id := l_get_all_relationships.relationship_id;
2269
2270 --l_relationship_rec.relationship_code := l_get_all_relationships.relationship_code;
2271 HZ_RELATIONSHIP_V2PUB.update_relationship(
2272 p_init_msg_list => 'F',
2273 p_relationship_rec => l_relationship_rec,
2274 p_object_version_number => l_get_all_relationships.object_version_number,
2275 p_party_object_version_number => l_party_object_version_number,
2276 x_return_status => x_return_status,
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
2286 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2287 RAISE FND_API.G_EXC_ERROR;
2288 EXIT;
2289 END IF;
2290
2291
2292 END LOOP;
2293 CLOSE c_get_all_relationships;
2294
2295 /* update hz_hierarchy_nodes
2296 set effective_end_date = p_end_date
2297 where hierarchy_type=l_geography_use
2298 and parent_id = p_geography_id
2299 or child_id = p_geography_id ; */
2300
2301
2302 END do_update_geography;
2303
2304 /*-----------------------------------------------------------------------------+
2305 Procedure do_create_discrete_geography: Creates discrete geographies for geographies
2306 created through geography range. These discrete geographies are created only
2307 if postal_code_range_flag = Y in HZ_GEOGRAPHY_TYPE_B (for 'MASTER_REF'). This
2308 way postal code range will be converted to discrete geography.
2309
2310 Created By Nishant Singhai 29-Aug-2005
2311 ER# 4539557 : CREATION OF DISCRETE VALUES FROM ZIP CODE RANGES
2312 Modified By Nishant Singhai 22-Sep-2005
2313 Bug# 4591075 : Commented out code as for now we will not be creating
2314 discrete geography for postal code range. Retaining logic
2315 for future use
2316 ------------------------------------------------------------------------------*/
2317
2318 PROCEDURE do_create_discrete_geography (
2319 p_geography_range_rec IN HZ_GEOGRAPHY_PUB.GEOGRAPHY_RANGE_REC_TYPE,
2320 x_return_status IN OUT NOCOPY VARCHAR2
2321 ) IS
2322 /*
2323 l_geography_range_rec HZ_GEOGRAPHY_PUB.GEOGRAPHY_RANGE_REC_TYPE;
2324
2325 l_parent_geo_type VARCHAR2(100);
2326 l_country_code VARCHAR2(100);
2327 l_child_geo_type VARCHAR2(100);
2328 l_geo_range_from NUMBER;
2329 l_geo_range_to NUMBER;
2330 l_geography_id NUMBER;
2331 l_master_geography_rec HZ_GEOGRAPHY_PUB.MASTER_GEOGRAPHY_REC_TYPE;
2332 l_parent_geography_id_tbl HZ_GEOGRAPHY_PUB.PARENT_GEOGRAPHY_TBL_TYPE;
2333 l_msg_data VARCHAR2(10000);
2334 l_msg_count NUMBER;
2335 l_count NUMBER;
2336 */
2337 BEGIN
2338 x_return_status := FND_API.G_RET_STS_SUCCESS;
2339 /*
2340 l_geography_range_rec := p_geography_range_rec;
2341 l_geo_range_from := TO_NUMBER(l_geography_range_rec.geography_from);
2342 l_geo_range_to := TO_NUMBER(l_geography_range_rec.geography_to);
2343
2344 IF ((l_geo_range_from IS NOT NULL) AND (l_geo_range_to IS NOT NULL) AND
2345 (l_geo_range_from <= l_geo_range_to)) THEN
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 ;
2355 EXCEPTION WHEN NO_DATA_FOUND THEN
2356 NULL;
2357 END;
2358
2359 IF (l_parent_geo_type IS NOT NULL) THEN
2360 -- get child geo type for which geo range has to be created
2361 BEGIN
2362 SELECT st.geography_type
2363 INTO l_child_geo_type
2364 FROM hz_geo_structure_levels st
2365 ,hz_geography_types_b tp
2366 WHERE st.country_code = l_country_code
2367 AND st.parent_geography_type = l_parent_geo_type
2368 AND st.geography_type = tp.geography_type
2369 AND tp.geography_use = 'MASTER_REF'
2370 AND tp.postal_code_range_flag = 'Y'
2371 AND tp.geography_use = 'MASTER_REF'
2372 AND ROWNUM < 2
2373 ;
2374 EXCEPTION WHEN OTHERS THEN
2375 NULL;
2376 END;
2377
2378 -- now create discrete geo
2379 IF (l_child_geo_type IS NOT NULL) THEN
2380
2381 l_master_geography_rec.geography_type := l_child_geo_type;
2382 l_master_geography_rec.START_DATE := l_geography_range_rec.start_date;
2383 l_master_geography_rec.END_DATE := l_geography_range_rec.end_date;
2384 l_parent_geography_id_tbl(1) := l_geography_range_rec.master_ref_geography_id;
2385 l_master_geography_rec.parent_geography_id := l_parent_geography_id_tbl;
2386 l_master_geography_rec.created_by_module := l_geography_range_rec.created_by_module;
2387 l_master_geography_rec.application_id := l_geography_range_rec.application_id;
2388
2389 FOR i IN l_geo_range_from..l_geo_range_to LOOP
2390 l_master_geography_rec.geography_name := TO_CHAR(i);
2391
2392 -- check if this geography already exists for given parent
2393 SELECT COUNT(*)
2394 INTO l_count
2395 FROM hz_geography_identifiers id
2396 WHERE UPPER(id.identifier_value) = l_master_geography_rec.geography_name
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'
2406 AND rel.relationship_type = 'MASTER_REF');
2407
2408 IF (l_count = 0) THEN
2409 -- create geography
2410 HZ_GEOGRAPHY_PUB.create_master_geography(
2411 p_init_msg_list => FND_API.G_FALSE,
2412 p_master_geography_rec => l_master_geography_rec,
2413 x_geography_id => l_geography_id,
2414 x_return_status => x_return_status,
2415 x_msg_count => l_msg_count,
2416 x_msg_data => l_msg_data);
2417
2418 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2419 -- In case api throuws any exception, raise execution error
2420 -- which will be cought by calling api.
2421 RAISE FND_API.G_EXC_ERROR;
2422 END IF;
2423 END IF;
2424 --dbms_output.put_line('Create Master Geo For :'||l_master_geography_rec.geography_name||':GeoId:'||l_geography_id);
2425 END LOOP;
2426
2427 ELSE
2428 --dbms_output.put_line('Child Geo type was NULL... ');
2429 NULL;
2430 END IF; -- End of child geo type not null check
2431
2432 ELSE
2433 --dbms_output.put_line('Parent Geo Type was NULL... ');
2434 NULL;
2435 END IF; -- End of parent_geo_type not null check
2436
2437 ELSE
2438 --dbms_output.put_line('From and to ids are wrong... ');
2439 NULL;
2440 END IF;
2441
2442 EXCEPTION
2443 WHEN VALUE_ERROR THEN
2444 -- when alphabet is passed in number field, we will get this error.
2445 -- it would have been taken care of in create range api itself.
2446 -- If not, we do not want to raise error, just don't create discrete
2447 -- geographies.
2448 -- dbms_output.put_line(SUBSTR('Number conversion error...'||SQLERRM,1,255));
2449 NULL;
2450 */
2451 END do_create_discrete_geography;
2452
2453
2454 -- create geography range
2455
2456 PROCEDURE do_create_geography_range(
2457 p_geography_range_rec IN GEOGRAPHY_RANGE_REC_TYPE,
2458 x_return_status IN OUT NOCOPY VARCHAR2
2459 ) IS
2460
2461 l_zone_type VARCHAR2(30);
2462 l_geography_use VARCHAR2(30);
2463 l_rowid ROWID;
2464 l_count NUMBER;
2465
2466
2467 BEGIN
2468
2469
2470
2471 -- check for the uniqueness
2472 /*SELECT count(*) INTO l_count from hz_geography_ranges
2473 WHERE geography_id = p_geography_range_rec.zone_id
2474 AND geography_from = p_geography_range_rec.geography_from
2475 AND to_char(start_date,'DD-MON-YYYY') = to_char(p_geography_range_rec.start_date,'DD_MON-YYYY');
2476
2477 IF l_count > 0 THEN
2478 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
2479 FND_MESSAGE.SET_TOKEN( 'COLUMN','geography_id, geography_from, start_date');
2480 FND_MSG_PUB.ADD;
2481 RAISE FND_API.G_EXC_ERROR;
2482 END IF; */
2483
2484
2485 -- validate geography range
2486 HZ_GEOGRAPHY_VALIDATE_PVT.validate_geography_range(
2487 p_geography_range_rec => p_geography_range_rec,
2488 p_create_update_flag => 'C',
2489 x_return_status => x_return_status
2490 );
2491
2492 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2493 RAISE FND_API.G_EXC_ERROR;
2494 END IF;
2495
2496 -- get the geography_type of the zone_id
2497 l_zone_type := hz_geography_validate_pvt.get_geography_type(p_geography_id => p_geography_range_rec.zone_id,
2498 x_return_status => x_return_status);
2499
2500 -- get geography use
2501 SELECT geography_use INTO l_geography_use
2502 FROM hz_geography_types_b
2503 WHERE geography_type=l_zone_type;
2504
2505 hz_geography_ranges_pkg.insert_row (
2506 x_rowid => l_rowid,
2507 x_geography_id => p_geography_range_rec.zone_id,
2508 x_geography_from => p_geography_range_rec.geography_from,
2509 x_start_date => p_geography_range_rec.start_date,
2510 x_object_version_number => 1,
2511 x_geography_to => p_geography_range_rec.geography_to,
2512 x_identifier_type => p_geography_range_rec.identifier_type,
2513 x_end_date => p_geography_range_rec.end_date,
2514 -- Dhaval : Use queried geography_type
2515 x_geography_type => l_zone_type,
2516 x_geography_use => l_geography_use,
2517 x_master_ref_geography_id => p_geography_range_rec.master_ref_geography_id,
2518 x_created_by_module => p_geography_range_rec.created_by_module,
2519 x_application_id => p_geography_range_rec.application_id,
2520 x_program_login_id => NULL
2521 );
2522
2523 -- ER # 4539557 : Added call to create discrete geo for passed in geography
2524 -- range (Nishant Singhai on 30-Aug-2005)
2525 -- Bug 4591075 : Removing this call for now, as it is decided that we will
2526 -- not create discrete geographies from postal_code range (Nishant 22-Sep-2005)
2527 /*
2528 do_create_discrete_geography (p_geography_range_rec => p_geography_range_rec,
2529 x_return_status => x_return_status);
2530 */
2531
2532 END do_create_geography_range;
2533
2534 -- update geography range
2535 PROCEDURE do_update_geography_range(
2536 p_geography_id IN NUMBER,
2537 p_geography_from IN VARCHAR2,
2538 p_start_date IN DATE,
2539 p_end_date IN DATE,
2540 p_object_version_number IN OUT NOCOPY NUMBER,
2541 x_return_status IN OUT NOCOPY VARCHAR2
2542 ) IS
2543
2544 l_rowid ROWID;
2545 l_start_date DATE;
2546 l_end_date DATE;
2547 l_geography_range_rec GEOGRAPHY_RANGE_REC_TYPE;
2548 l_object_version_number NUMBER;
2549
2550 BEGIN
2551
2552 l_geography_range_rec.zone_id := p_geography_id;
2553 l_geography_range_rec.geography_from := p_geography_from;
2554 l_geography_range_rec.start_date := p_start_date;
2555
2556 -- validate geography range for update
2557 HZ_GEOGRAPHY_VALIDATE_PVT.validate_geography_range(
2558 p_geography_range_rec => l_geography_range_rec,
2559 p_create_update_flag => 'U',
2560 x_return_status => x_return_status
2561 );
2562
2563 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2564 RAISE FND_API.G_EXC_ERROR;
2565 END IF;
2566
2567 hz_utility_v2pub.validate_mandatory(
2568 p_create_update_flag => 'U',
2569 p_column => 'object_version_number',
2570 p_column_value => p_object_version_number,
2571 x_return_status => x_return_status
2572 );
2573
2574 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2575 RAISE FND_API.G_EXC_ERROR;
2576 END IF;
2577
2578 -- check if the row exists
2579 BEGIN
2580
2581 SELECT rowid,start_date,end_date,object_version_number INTO l_rowid,l_start_date,l_end_date,l_object_version_number
2582 FROM hz_geography_ranges
2583 WHERE geography_id = p_geography_id
2584 AND geography_from = p_geography_from
2585 AND start_date = p_start_date
2586 FOR UPDATE OF geography_id,geography_from,start_date NOWAIT;
2587
2588 --validate object_version_number
2589 IF l_object_version_number <> p_object_version_number THEN
2590 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
2591 FND_MESSAGE.SET_TOKEN('TABLE', 'hz_geography_ranges');
2592 FND_MSG_PUB.ADD;
2593 RAISE FND_API.G_EXC_ERROR;
2594 ELSE
2595 p_object_version_number := l_object_version_number + 1;
2596 END IF;
2597
2598 EXCEPTION WHEN NO_DATA_FOUND THEN
2599 FND_MESSAGE.SET_NAME( 'AR', 'HZ_GEO_NO_RECORD' );
2600 FND_MESSAGE.SET_TOKEN( 'TOKEN1', 'Geography Range');
2601 FND_MESSAGE.SET_TOKEN( 'TOKEN2', 'geography_id '||p_geography_id||', geography_from '||p_geography_from||', start_date '||p_start_date);
2602 FND_MSG_PUB.ADD;
2603 RAISE FND_API.G_EXC_ERROR;
2604
2605 END;
2606 -- check if start_date <= end_date
2607
2608 HZ_UTILITY_V2PUB.validate_start_end_date(
2609 p_create_update_flag => 'U',
2610 p_start_date_column_name => 'start_date',
2611 p_start_date => p_start_date,
2612 p_old_start_date => l_start_date,
2613 p_end_date_column_name => 'end_date',
2614 p_end_date => p_end_date,
2615 p_old_end_date => l_end_date,
2616 x_return_status => x_return_status
2617 );
2618
2619 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2620 RAISE FND_API.G_EXC_ERROR;
2621 END IF;
2622
2623 -- call table handler to update the row
2624 HZ_GEOGRAPHY_RANGES_PKG.update_row(
2625 x_rowid => l_rowid,
2626 x_geography_id => p_geography_id,
2627 x_geography_from => p_geography_from,
2628 x_start_date => p_start_date,
2629 x_object_version_number => p_object_version_number,
2630 x_geography_to => NULL,
2631 x_identifier_type => NULL,
2632 x_end_date => p_end_date,
2633 x_geography_type => NULL,
2634 x_geography_use => NULL,
2635 x_master_ref_geography_id => NULL,
2636 x_created_by_module => NULL,
2637 x_application_id => NULL,
2638 x_program_login_id => NULL
2639 );
2640
2641 END do_update_geography_range;
2642
2643 -- create zone relation
2644
2645 PROCEDURE do_create_zone_relation(
2646 p_geography_id IN NUMBER,
2647 p_zone_relation_tbl IN ZONE_RELATION_TBL_TYPE,
2648 p_created_by_module IN VARCHAR2,
2649 p_application_id IN NUMBER,
2650 x_return_status IN OUT NOCOPY VARCHAR2
2651 ) IS
2652
2653 l_count NUMBER;
2654 --l_parent_geography_type VARCHAR2(30);
2655 l_zone_type VARCHAR2(30);
2656 l_geography_use VARCHAR2(30);
2657 l_zone_relation_rec HZ_GEOGRAPHY_VALIDATE_PVT.zone_relation_rec_type;
2658 l_relationship_rec HZ_RELATIONSHIP_V2PUB.relationship_rec_type;
2659 l_geography_range_rec GEOGRAPHY_RANGE_REC_TYPE;
2660 l_incl_geo_type VARCHAR2(30);
2661 x_relationship_id NUMBER;
2662 x_party_id NUMBER;
2663 x_party_number NUMBER;
2664 p_create_org_contact VARCHAR2(1);
2665 x_msg_count NUMBER;
2666 x_msg_data VARCHAR2(2000);
2667 l_limited_by_geography_id NUMBER;
2668
2669 -- Added ro ER 4232852
2670 l_geo_rel_type_rec HZ_GEOGRAPHY_STRUCTURE_PUB.GEO_REL_TYPE_REC_TYPE;
2671 x_relationship_type_id NUMBER;
2672
2673 BEGIN
2674
2675
2676 l_zone_type := HZ_GEOGRAPHY_VALIDATE_PVT.get_geography_type(p_geography_id =>p_geography_id,
2677 x_return_status => x_return_status);
2678
2679
2680 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2681 RAISE FND_API.G_EXC_ERROR;
2682 END IF;
2683
2684 SELECT geography_use,limited_by_geography_id INTO l_geography_use,l_limited_by_geography_id
2685 FROM hz_geography_types_b
2686 WHERE geography_type = l_zone_type;
2687
2688 l_zone_relation_rec.geography_id := p_geography_id;
2689
2690 FOR i in 1 .. p_zone_relation_tbl.count LOOP
2691
2692 -- validate zone relation record
2693 l_zone_relation_rec.included_geography_id := p_zone_relation_tbl(i).included_geography_id;
2694 l_zone_relation_rec.start_date := p_zone_relation_tbl(i).start_date;
2695 l_zone_relation_rec.end_date := p_zone_relation_tbl(i).end_date;
2696
2697 hz_geography_validate_pvt.validate_zone_relation(
2698 p_zone_relation_rec => l_zone_relation_rec,
2699 p_create_update_flag => 'C',
2700 x_return_status => x_return_status
2701 );
2702
2703 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2704 RAISE FND_API.G_EXC_ERROR;
2705 EXIT;
2706 END IF;
2707
2708 --dbms_output.put_line('In loop after validate zone relaion '||x_return_status);
2709
2710 --get geography_type of included_geography_id
2711 l_incl_geo_type := HZ_GEOGRAPHY_VALIDATE_PVT.get_geography_type(p_geography_id =>p_zone_relation_tbl(i).included_geography_id,
2712 x_return_status => x_return_status);
2713
2714 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2715 RAISE FND_API.G_EXC_ERROR;
2716 EXIT;
2717 END IF;
2718
2719 IF l_limited_by_geography_id IS NOT NULL THEN
2720 --included geography_id should have a relationship with limited_by_geography_id either at level >= 0
2721 BEGIN
2722 SELECT 1 INTO l_count
2723 FROM hz_hierarchy_nodes
2724 WHERE parent_id = l_limited_by_geography_id
2725 AND child_id = p_zone_relation_tbl(i).included_geography_id
2726 AND hierarchy_type = 'MASTER_REF'
2727 AND NVL(status,'A') = 'A'
2728 AND (effective_end_date IS NULL
2729 OR effective_end_date > sysdate
2730 )
2731 AND rownum < 2;
2732
2733 EXCEPTION WHEN NO_DATA_FOUND THEN
2734 FND_MESSAGE.SET_NAME( 'AR', 'HZ_GEO_NO_RELATIONSHIP');
2735 FND_MESSAGE.SET_TOKEN( 'INCL_GEO_ID',p_zone_relation_tbl(i).included_geography_id);
2736 FND_MESSAGE.SET_TOKEN( 'LIM_GEO_ID', l_limited_by_geography_id );
2737 FND_MESSAGE.SET_TOKEN( 'ZONE_TYPE', l_zone_type);
2738 FND_MSG_PUB.ADD;
2739 RAISE FND_API.G_EXC_ERROR;
2740 END;
2741 END IF;
2742
2743 -- Fix for ER 4232852
2744 IF l_limited_by_geography_id IS NULL THEN
2745 -- check if there exists a relationship_type for this geography_type and
2746 -- included_geography_type
2747 SELECT count(*) INTO l_COUNT
2748 FROM HZ_RELATIONSHIP_TYPES
2749 WHERE subject_type = l_zone_type
2750 AND object_type= l_incl_geo_type
2751 AND forward_rel_code = 'PARENT_OF'
2752 AND backward_rel_code = 'CHILD_OF'
2753 AND relationship_type = 'TAX';
2754
2755 IF l_count = 0 THEN
2756 -- create a relationship type with this geography type and included geography type
2757 l_geo_rel_type_rec.geography_type := l_incl_geo_type;
2758 l_geo_rel_type_rec.parent_geography_type := l_zone_type;
2759 l_geo_rel_type_rec.status := 'A';
2760 l_geo_rel_type_rec.created_by_module := p_created_by_module;
2761 l_geo_rel_type_rec.application_id := p_application_id;
2762
2763 HZ_GEOGRAPHY_STRUCTURE_PUB.create_geo_rel_type(
2764 p_init_msg_list => 'F',
2765 p_geo_rel_type_rec => l_geo_rel_type_rec,
2766 x_relationship_type_id => x_relationship_type_id,
2767 x_return_status => x_return_status,
2768 x_msg_count => x_msg_count,
2769 x_msg_data => x_msg_data
2770 );
2771
2772 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2773 RAISE FND_API.G_EXC_ERROR;
2774 END IF;
2775 END IF;
2776 END IF;
2777
2778
2779 -- within a zone_type,zones can not have overlapping geographies
2780 -- Changed check from "subject_type = l_zone_type" to "subject_id = p_geography_id"
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
2790 AND sysdate between start_date and nvl(end_date, sysdate + 1)
2791 AND status = 'A'
2792 AND rownum < 2;
2793
2794 IF l_count > 0 THEN
2795 FND_MESSAGE.SET_NAME( 'AR', 'HZ_GEO_OVERLAPPING_GEOGS' );
2796 FND_MESSAGE.SET_TOKEN( 'GEO_ID',p_zone_relation_tbl(i).included_geography_id );
2797 FND_MESSAGE.SET_TOKEN( 'ZONE_TYPE', l_zone_type);
2798 FND_MSG_PUB.ADD;
2799 RAISE FND_API.G_EXC_ERROR;
2800 EXIT;
2801 END IF;
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
2811 AND sysdate between start_date and nvl(end_date, sysdate + 1)
2812 AND status = 'A'
2813 AND rownum <2;
2814
2815 IF l_count > 0 THEN
2816 FND_MESSAGE.SET_NAME( 'AR', 'HZ_GEO_DUPL_INCL_GEO_ID' );
2817 FND_MESSAGE.SET_TOKEN( 'GEO_ID',p_zone_relation_tbl(i).included_geography_id );
2818 FND_MESSAGE.SET_TOKEN( 'ZONE_ID', p_geography_id);
2819 FND_MSG_PUB.ADD;
2820 RAISE FND_API.G_EXC_ERROR;
2821 EXIT;
2822 END IF;
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';
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;
2835 l_relationship_rec.status := 'A';
2836 l_relationship_rec.created_by_module := p_created_by_module;
2837 l_relationship_rec.application_id := p_application_id;
2838
2839 -- call to relationship API to create a relationship
2840 HZ_RELATIONSHIP_V2PUB.create_relationship(
2841 p_init_msg_list => 'F',
2842 p_relationship_rec => l_relationship_rec,
2843 x_relationship_id => x_relationship_id,
2844 x_party_id => x_party_id,
2845 x_party_number => x_party_number,
2846 x_return_status => x_return_status,
2847 x_msg_count => x_msg_count,
2848 x_msg_data => x_msg_data,
2849 p_create_org_contact => 'N'
2850 );
2851
2852 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2853 --dbms_output.put_line('After call to create relationship API '|| x_return_status);
2854 RAISE FND_API.G_EXC_ERROR;
2855 EXIT;
2856 END IF;
2857 IF (p_zone_relation_tbl(i).geography_from IS NOT NULL AND
2858 p_zone_relation_tbl(i).geography_to IS NOT NULL AND
2859 p_zone_relation_tbl(i).geography_from <> fnd_api.g_miss_char AND
2860 p_zone_relation_tbl(i).geography_to <> fnd_api.g_miss_char) THEN
2861 --call create_geography_range API
2862
2863 l_geography_range_rec.zone_id := p_geography_id;
2864 l_geography_range_rec.master_ref_geography_id := p_zone_relation_tbl(i).included_geography_id;
2865 l_geography_range_rec.identifier_type := p_zone_relation_tbl(i).identifier_type;
2866 l_geography_range_rec.geography_from := p_zone_relation_tbl(i).geography_from;
2867 l_geography_range_rec.geography_to := p_zone_relation_tbl(i).geography_to;
2868 l_geography_range_rec.geography_type := p_zone_relation_tbl(i).geography_type;
2869 l_geography_range_rec.start_date := p_zone_relation_tbl(i).start_date;
2870 l_geography_range_rec.end_date := p_zone_relation_tbl(i).end_date;
2871 l_geography_range_rec.created_by_module := p_created_by_module;
2872 l_geography_range_rec.application_id := p_application_id;
2873
2874
2875 --dbms_output.put_line('before call to create geography range');
2876
2877 create_geography_range(
2878 p_init_msg_list => 'F',
2879 p_geography_range_rec => l_geography_range_rec,
2880 x_return_status => x_return_status,
2881 x_msg_count => x_msg_count,
2882 x_msg_data => x_msg_data
2883 );
2884
2885 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2886 RAISE FND_API.G_EXC_ERROR;
2887 EXIT;
2888 END IF;
2889 END IF;
2890 END LOOP;
2891
2892 END do_create_zone_relation;
2893
2894 -- create zone
2895
2896 PROCEDURE do_create_zone(
2897 p_zone_type IN VARCHAR2,
2898 p_zone_name IN VARCHAR2,
2899 p_zone_code IN VARCHAR2,
2900 p_zone_code_type IN VARCHAR2,
2901 p_start_date IN DATE,
2902 p_end_date IN DATE,
2903 p_geo_data_provider IN VARCHAR2,
2904 p_language_code IN VARCHAR2,
2905 p_zone_relation_tbl IN ZONE_RELATION_TBL_TYPE,
2906 p_geometry IN MDSYS.SDO_GEOMETRY,
2907 p_timezone_code IN VARCHAR2,
2908 x_geography_id OUT NOCOPY NUMBER,
2909 p_created_by_module IN VARCHAR2,
2910 p_application_id IN NUMBER,
2911 x_return_status OUT NOCOPY VARCHAR2
2912 ) IS
2913
2914 l_count NUMBER;
2915 l_geo_identifier_rec GEO_IDENTIFIER_REC_TYPE;
2916 l_geography_use VARCHAR2(30);
2917 l_rowid ROWID;
2918 l_country_code VARCHAR2(2);
2919 x_relationship_id NUMBER;
2920 x_msg_count NUMBER;
2921 x_msg_data VARCHAR2(2000);
2922 l_language_code VARCHAR2(4);
2923 l_end_date DATE;
2924 l_limited_by_geography_id NUMBER;
2925
2926 BEGIN
2927
2928 l_end_date := to_date('31-12-4712','DD-MM-YYYY');
2929 -- validate for mandatory columns
2930 HZ_UTILITY_V2PUB.validate_mandatory (
2931 p_create_update_flag =>'C',
2932 p_column => 'zone_type',
2933 p_column_value => p_zone_type,
2934 p_restricted => 'N',
2935 x_return_status => x_return_status
2936 );
2937
2938 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2939 RAISE FND_API.G_EXC_ERROR;
2940 END IF;
2941
2942 HZ_UTILITY_V2PUB.validate_mandatory (
2943 p_create_update_flag =>'C',
2944 p_column => 'zone_name',
2945 p_column_value => p_zone_name,
2946 p_restricted => 'N',
2947 x_return_status => x_return_status
2948 );
2949
2950 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2951 RAISE FND_API.G_EXC_ERROR;
2952 END IF;
2953
2954 -- get geography_use
2955 BEGIN
2956
2957 SELECT geography_use,limited_by_geography_id INTO l_geography_use,l_limited_by_geography_id
2958 FROM hz_geography_types_b
2959 WHERE geography_type = p_zone_type;
2960
2961 EXCEPTION WHEN NO_DATA_FOUND THEN
2962 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
2963 fnd_message.set_token('FK', 'geography_type');
2964 fnd_message.set_token('COLUMN','zone_type');
2965 fnd_message.set_token('TABLE','HZ_GEOGRAPHY_TYPES_B');
2966 fnd_msg_pub.add;
2967 RAISE FND_API.G_EXC_ERROR;
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
2977 IF l_count > 0 THEN
2978 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
2979 FND_MESSAGE.SET_TOKEN( 'COLUMN','zone name');
2980 FND_MSG_PUB.ADD;
2981 RAISE FND_API.G_EXC_ERROR;
2982 END IF;
2983
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
2993 IF l_count > 0 THEN
2994 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
2995 FND_MESSAGE.SET_TOKEN( 'COLUMN','zone code');
2996 FND_MSG_PUB.ADD;
2997 RAISE FND_API.G_EXC_ERROR;
2998 END IF;
2999 END IF;
3000
3001 -- zone_code_type is mandatory if zone_code is NOT NULL
3002 IF (p_zone_code IS NOT NULL AND p_zone_code_type IS NULL) THEN
3003 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
3004 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'zone_code_type' );
3005 FND_MSG_PUB.ADD;
3006 RAISE FND_API.G_EXC_ERROR;
3007 END IF;
3008
3009
3010 -- timezone_code must be FK to fnd_timezones_b
3011 IF p_timezone_code IS NOT NULL THEN
3012 SELECT count(*) INTO l_count
3013 FROM fnd_timezones_b
3014 WHERE timezone_code = p_timezone_code
3015 AND rownum <2;
3016
3017 IF l_count = 0 THEN
3018 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
3019 fnd_message.set_token('FK', 'timezone_code');
3020 fnd_message.set_token('COLUMN','timezone_code');
3021 fnd_message.set_token('TABLE','FND_TIMEZONES_B');
3022 fnd_msg_pub.add;
3023 RAISE FND_API.G_EXC_ERROR;
3024 END IF;
3025 END IF;
3026
3027 -- there must be atleast one included geography for a zone
3028 IF p_zone_relation_tbl.count = 0 THEN
3029 fnd_message.set_name('AR', 'HZ_GEO_NO_INCL_GEOGRAPHIES');
3030 fnd_msg_pub.add;
3031 RAISE FND_API.G_EXC_ERROR;
3032 END IF;
3033
3034 -- get country code of one of the included_geography_id of this zone_type
3035
3036
3037 FOR i in 1 .. p_zone_relation_tbl.count LOOP
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;
3047 EXCEPTION WHEN NO_DATA_FOUND THEN
3048 FND_MESSAGE.SET_NAME( 'AR', 'HZ_GEO_NO_RECORD');
3049 FND_MESSAGE.SET_TOKEN( 'TOKEN1','country code');
3050 FND_MESSAGE.SET_TOKEN( 'TOKEN2', 'included_geography_id '||p_zone_relation_tbl(i).included_geography_id);
3051 FND_MSG_PUB.ADD;
3052 RAISE FND_API.G_EXC_ERROR;
3053 EXIT;
3054 END;
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,
3064 x_object_version_number => 1,
3065 x_geography_type => p_zone_type,
3066 x_geography_name => p_zone_name,
3067 x_geography_use => l_geography_use,
3068 x_geography_code => UPPER(p_zone_code),
3069 x_start_date => NVL(p_start_date,SYSDATE),
3070 x_end_date => NVL(p_end_date,l_end_date),
3071 x_multiple_parent_flag => 'N',
3072 x_created_by_module => p_created_by_module,
3073 x_country_code => l_country_code,
3074 x_geography_element1 => NULL,
3075 x_geography_element1_id => NULL,
3076 x_geography_element1_code => NULL,
3077 x_geography_element2 => NULL,
3078 x_geography_element2_id => NULL,
3079 x_geography_element2_code => NULL,
3080 x_geography_element3 => NULL,
3081 x_geography_element3_id => NULL,
3082 x_geography_element3_code => NULL,
3083 x_geography_element4 => NULL,
3084 x_geography_element4_id => NULL,
3085 x_geography_element4_code => NULL,
3086 x_geography_element5 => NULL,
3087 x_geography_element5_id => NULL,
3088 x_geography_element5_code => NULL,
3089 x_geography_element6 => NULL,
3090 x_geography_element6_id => NULL,
3091 x_geography_element7 => NULL,
3092 x_geography_element7_id => NULL,
3093 x_geography_element8 => NULL,
3094 x_geography_element8_id => NULL,
3095 x_geography_element9 => NULL,
3096 x_geography_element9_id => NULL,
3097 x_geography_element10 => NULL,
3098 x_geography_element10_id => NULL,
3099 x_geometry => p_geometry,
3100 x_timezone_code => p_timezone_code,
3101 x_application_id => p_application_id,
3102 x_program_login_id => NULL,
3103 x_attribute_category => NULL,
3104 x_attribute1 => NULL,
3105 x_attribute2 => NULL,
3106 x_attribute3 => NULL,
3107 x_attribute4 => NULL,
3108 x_attribute5 => NULL,
3109 x_attribute6 => NULL,
3110 x_attribute7 => NULL,
3111 x_attribute8 => NULL,
3112 x_attribute9 => NULL,
3113 x_attribute10 => NULL,
3114 x_attribute11 => NULL,
3115 x_attribute12 => NULL,
3116 x_attribute13 => NULL,
3117 x_attribute14 => NULL,
3118 x_attribute15 => NULL,
3119 x_attribute16 => NULL,
3120 x_attribute17 => NULL,
3121 x_attribute18 => NULL,
3122 x_attribute19 => NULL,
3123 x_attribute20 => NULL
3124 );
3125
3126 -- default language_code in case of NULL
3127 IF p_language_code IS NULL THEN
3128 SELECT userenv('LANG') INTO l_language_code FROM dual;
3129 ELSE
3130 l_language_code := p_language_code;
3131 END IF;
3132
3133
3134 -- construct identifier record
3135 l_geo_identifier_rec.geography_id := x_geography_id;
3136 l_geo_identifier_rec.identifier_subtype := 'STANDARD_NAME';
3137 l_geo_identifier_rec.identifier_value := p_zone_name;
3138 l_geo_identifier_rec.identifier_type := 'NAME';
3139 l_geo_identifier_rec.geo_data_provider := NVL(p_geo_data_provider,'USER_ENTERED');
3140 l_geo_identifier_rec.primary_flag := 'Y';
3141 l_geo_identifier_rec.language_code := l_language_code;
3142 l_geo_identifier_rec.created_by_module := p_created_by_module;
3143 l_geo_identifier_rec.application_id := p_application_id;
3144
3145
3146 -- call create identifier API
3147
3148 create_geo_identifier(
3149 p_init_msg_list => 'F',
3150 p_geo_identifier_rec => l_geo_identifier_rec,
3151 x_return_status => x_return_status,
3152 x_msg_count => x_msg_count,
3153 x_msg_data => x_msg_data
3154 );
3155
3156 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3157 RAISE FND_API.G_EXC_ERROR;
3158 END IF;
3159
3160 --dbms_output.put_line('After first identifier row '||x_return_status);
3161
3162 IF p_zone_code IS NOT NULL
3163 -- Bug 4579847 : do not call for g_miss value for code
3164 and p_zone_code <> fnd_api.g_miss_char THEN
3165 -- create identifier for zone code
3166 l_geo_identifier_rec.identifier_subtype := p_zone_code_type;
3167 l_geo_identifier_rec.identifier_value := p_zone_code;
3168 l_geo_identifier_rec.identifier_type := 'CODE';
3169
3170 create_geo_identifier(
3171 p_init_msg_list => 'F',
3172 p_geo_identifier_rec => l_geo_identifier_rec,
3173 x_return_status => x_return_status,
3174 x_msg_count => x_msg_count,
3175 x_msg_data => x_msg_data
3176 );
3177
3178 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3179 RAISE FND_API.G_EXC_ERROR;
3180 END IF;
3181
3182 --dbms_output.put_line('After second identifier row '||x_return_status);
3183 END IF;
3184
3185 -- call API to create zone relationship
3186 create_zone_relation(
3187 p_init_msg_list => 'F',
3188 p_geography_id => x_geography_id,
3189 p_zone_relation_tbl => p_zone_relation_tbl,
3190 p_created_by_module => p_created_by_module,
3191 p_application_id => p_application_id,
3192 x_return_status => x_return_status,
3193 x_msg_count => x_msg_count,
3194 x_msg_data => x_msg_data
3195 );
3196
3197 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3198 RAISE FND_API.G_EXC_ERROR;
3199 END IF;
3200 --dbms_output.put_line('After insert zone relation '||x_return_status);
3201
3202 END do_create_zone;
3203
3204 ----------------------------
3205 -- body of public procedures
3206 ----------------------------
3207
3208 /**
3209 * PROCEDURE create_master_relation
3210 *
3211 * DESCRIPTION
3212 * Creates Geography Relationships.
3213 *
3214 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3215 *
3216 * ARGUMENTS
3217 * IN:
3218 * p_init_msg_list Initialize message stack if it is set to
3219 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
3220 * p_master_relation_rec Geography type record.
3221 * IN/OUT:
3222 * OUT:
3223 * x_relationship_id Returns relationship_id for the relationship created.
3224 * x_return_status Return status after the call. The status can
3225 * be FND_API.G_RET_STS_SUCCESS (success),
3226 * FND_API.G_RET_STS_ERROR (error),
3227 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
3228 * x_msg_count Number of messages in message stack.
3229 * x_msg_data Message text if x_msg_count is 1.
3230 *
3231 * NOTES
3232 *
3233 * MODIFICATION HISTORY
3234 *
3235 * 11-22-2002 Rekha Nalluri o Created.
3236 *
3237 */
3238
3239 PROCEDURE create_master_relation (
3240 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3241 p_master_relation_rec IN MASTER_RELATION_REC_TYPE,
3242 x_relationship_id OUT NOCOPY NUMBER,
3243 x_return_status OUT NOCOPY VARCHAR2,
3244 x_msg_count OUT NOCOPY NUMBER,
3245 x_msg_data OUT NOCOPY VARCHAR2
3246 ) IS
3247
3248 --l_master_relation_rec MASTER_RELATION_REC_TYPE := p_master_relation_rec;
3249
3250 BEGIN
3251 -- Standard start of API savepoint
3252 SAVEPOINT create_master_relation;
3253
3254 -- Initialize message list if p_init_msg_list is set to TRUE.
3255 IF FND_API.to_Boolean(p_init_msg_list) THEN
3256 FND_MSG_PUB.initialize;
3257 END IF;
3258
3259 -- Initialize API return status to success.
3260 x_return_status := FND_API.G_RET_STS_SUCCESS;
3261
3262 -- Call to business logic.
3263 do_create_master_relation(
3264 p_master_relation_rec => p_master_relation_rec,
3265 x_relationship_id => x_relationship_id,
3266 x_return_status => x_return_status
3267 );
3268
3269 --if validation failed at any point, then raise an exception to stop processing
3270 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3271 RAISE FND_API.G_EXC_ERROR;
3272 END IF;
3273
3274 --g_dup_checked := 'N';
3275 -- Standard call to get message count and if count is 1, get message info.
3276 FND_MSG_PUB.Count_And_Get(
3277 p_encoded => FND_API.G_FALSE,
3278 p_count => x_msg_count,
3279 p_data => x_msg_data);
3280
3281 EXCEPTION
3282 WHEN FND_API.G_EXC_ERROR THEN
3283 g_dup_checked := 'N';
3284 ROLLBACK TO create_master_relation;
3285 x_return_status := FND_API.G_RET_STS_ERROR;
3286 FND_MSG_PUB.Count_And_Get(
3287 p_encoded => FND_API.G_FALSE,
3288 p_count => x_msg_count,
3289 p_data => x_msg_data);
3290 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3291 --g_dup_checked := 'N';
3292 ROLLBACK TO create_master_relation;
3293 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3294 FND_MSG_PUB.Count_And_Get(
3295 p_encoded => FND_API.G_FALSE,
3296 p_count => x_msg_count,
3297 p_data => x_msg_data);
3298
3299 WHEN OTHERS THEN
3300 --g_dup_checked := 'N';
3301 ROLLBACK TO create_master_relation;
3302 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3303 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
3304 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3305 FND_MSG_PUB.ADD;
3306 FND_MSG_PUB.Count_And_Get(
3307 p_encoded => FND_API.G_FALSE,
3308 p_count => x_msg_count,
3309 p_data => x_msg_data);
3310
3311 END create_master_relation;
3312
3313 /**
3314 * PROCEDURE update_relationship
3315 *
3316 * DESCRIPTION
3317 * Updates Geography Relationships.
3318 *
3319 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3320 *
3321 * ARGUMENTS
3322 * IN:
3323 * p_init_msg_list Initialize message stack if it is set to
3324 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
3325 * p_master_relation_rec Geography type record.
3326 * p_object_version_number Object version number of the row
3327 * IN/OUT:
3328 * OUT:
3329 *
3330 * x_return_status Return status after the call. The status can
3331 * be FND_API.G_RET_STS_SUCCESS (success),
3332 * FND_API.G_RET_STS_ERROR (error),
3333 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
3334 * x_msg_count Number of messages in message stack.
3335 * x_msg_data Message text if x_msg_count is 1.
3336 *
3337 * NOTES
3338 *
3339 * MODIFICATION HISTORY
3340 * 11-22-2002 Rekha Nalluri o Created.
3341 *
3342 */
3343
3344 PROCEDURE update_relationship (
3345 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3346 p_relationship_id IN NUMBER,
3347 p_status IN VARCHAR2,
3348 p_object_version_number IN OUT NOCOPY NUMBER,
3349 x_return_status OUT NOCOPY VARCHAR2,
3350 x_msg_count OUT NOCOPY NUMBER,
3351 x_msg_data OUT NOCOPY VARCHAR2
3352 )IS
3353
3354 BEGIN
3355 -- Standard start of API savepoint
3356 SAVEPOINT update_relationship;
3357
3358 -- Initialize message list if p_init_msg_list is set to TRUE.
3359 IF FND_API.to_Boolean(p_init_msg_list) THEN
3360 FND_MSG_PUB.initialize;
3361 END IF;
3362
3363 -- Initialize API return status to success.
3364 x_return_status := FND_API.G_RET_STS_SUCCESS;
3365
3366 -- Call to business logic.
3367 do_update_relationship(
3368 p_relationship_id => p_relationship_id,
3369 p_status => p_status,
3370 p_object_version_number => p_object_version_number,
3371 x_return_status => x_return_status
3372 );
3373
3374 --if validation failed at any point, then raise an exception to stop processing
3375 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3376 RAISE FND_API.G_EXC_ERROR;
3377 END IF;
3378
3379 -- Standard call to get message count and if count is 1, get message info.
3380 FND_MSG_PUB.Count_And_Get(
3381 p_encoded => FND_API.G_FALSE,
3382 p_count => x_msg_count,
3383 p_data => x_msg_data);
3384
3385 EXCEPTION
3386 WHEN FND_API.G_EXC_ERROR THEN
3387 ROLLBACK TO update_relationship;
3388 x_return_status := FND_API.G_RET_STS_ERROR;
3389 FND_MSG_PUB.Count_And_Get(
3390 p_encoded => FND_API.G_FALSE,
3391 p_count => x_msg_count,
3392 p_data => x_msg_data);
3393 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3394 ROLLBACK TO update_relationship;
3395 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3396 FND_MSG_PUB.Count_And_Get(
3397 p_encoded => FND_API.G_FALSE,
3398 p_count => x_msg_count,
3399 p_data => x_msg_data);
3400
3401 WHEN OTHERS THEN
3402 ROLLBACK TO update_relationship;
3403 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3404 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
3405 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3406 FND_MSG_PUB.ADD;
3407 FND_MSG_PUB.Count_And_Get(
3408 p_encoded => FND_API.G_FALSE,
3409 p_count => x_msg_count,
3410 p_data => x_msg_data);
3411
3412 END update_relationship;
3413
3414 /**
3415 * PROCEDURE create_geo_identifier
3416 *
3417 * DESCRIPTION
3418 * Creates Geography Identifiers.
3419 *
3420 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3421 *
3422 * ARGUMENTS
3423 * IN:
3424 * p_init_msg_list Initialize message stack if it is set to
3425 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
3426 * p_geo_identifier_rec Geo_identifier type record.
3427 * IN/OUT:
3428 * OUT:
3429 *
3430 * x_return_status Return status after the call. The status can
3431 * be FND_API.G_RET_STS_SUCCESS (success),
3432 * FND_API.G_RET_STS_ERROR (error),
3433 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
3434 * x_msg_count Number of messages in message stack.
3435 * x_msg_data Message text if x_msg_count is 1.
3436 *
3437 * NOTES
3438 *
3439 * MODIFICATION HISTORY
3440 * 12-03-2002 Rekha Nalluri o Created.
3441 *
3442 */
3443
3444 PROCEDURE create_geo_identifier(
3445 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3446 p_geo_identifier_rec IN GEO_IDENTIFIER_REC_TYPE,
3447 x_return_status OUT NOCOPY VARCHAR2,
3448 x_msg_count OUT NOCOPY NUMBER,
3449 x_msg_data OUT NOCOPY VARCHAR2
3450 ) IS
3451
3452 p_index_name VARCHAR2(30);
3453
3454 BEGIN
3455 -- Standard start of API savepoint
3456 SAVEPOINT create_geo_identifier;
3457
3458 -- Initialize message list if p_init_msg_list is set to TRUE.
3459 IF FND_API.to_Boolean(p_init_msg_list) THEN
3460 FND_MSG_PUB.initialize;
3461 END IF;
3462
3463 -- Initialize API return status to success.
3464 x_return_status := FND_API.G_RET_STS_SUCCESS;
3465
3466 -- Call to business logic.
3467 do_create_geo_identifier(
3468 p_geo_identifier_rec => p_geo_identifier_rec,
3469 x_return_status => x_return_status
3470 );
3471
3472 --if validation failed at any point, then raise an exception to stop processing
3473 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3474 RAISE FND_API.G_EXC_ERROR;
3475 END IF;
3476
3477 -- Standard call to get message count and if count is 1, get message info.
3478 FND_MSG_PUB.Count_And_Get(
3479 p_encoded => FND_API.G_FALSE,
3480 p_count => x_msg_count,
3481 p_data => x_msg_data);
3482
3483 EXCEPTION
3484 WHEN FND_API.G_EXC_ERROR THEN
3485 ROLLBACK TO create_geo_identifier;
3486 x_return_status := FND_API.G_RET_STS_ERROR;
3487 FND_MSG_PUB.Count_And_Get(
3488 p_encoded => FND_API.G_FALSE,
3489 p_count => x_msg_count,
3490 p_data => x_msg_data);
3491 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3492 ROLLBACK TO create_geo_identifier;
3493 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3494 FND_MSG_PUB.Count_And_Get(
3495 p_encoded => FND_API.G_FALSE,
3496 p_count => x_msg_count,
3497 p_data => x_msg_data);
3498
3499 WHEN DUP_VAL_ON_INDEX THEN
3500 ROLLBACK TO create_geo_identifier;
3501 x_return_status := FND_API.G_RET_STS_ERROR;
3502 HZ_UTILITY_V2PUB.find_index_name(p_index_name);
3503 IF p_index_name = 'HZ_GEOGRAPHY_IDENTIFIERS_U1' THEN
3504 FND_MESSAGE.SET_NAME('AR', 'HZ_API_DUPLICATE_COLUMN');
3505 FND_MESSAGE.SET_TOKEN('COLUMN', 'geography_id,identifier_type,identifier_subtype,identifier_value,language_code');
3506 FND_MSG_PUB.ADD;
3507 END IF;
3508 FND_MSG_PUB.Count_And_Get(
3509 p_encoded => FND_API.G_FALSE,
3510 p_count => x_msg_count,
3511 p_data => x_msg_data);
3512
3513 WHEN OTHERS THEN
3514 ROLLBACK TO create_geo_identifier;
3515 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3516 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
3517 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3518 FND_MSG_PUB.ADD;
3519 FND_MSG_PUB.Count_And_Get(
3520 p_encoded => FND_API.G_FALSE,
3521 p_count => x_msg_count,
3522 p_data => x_msg_data);
3523 END create_geo_identifier;
3524
3525 /**
3526 * PROCEDURE update_geo_identifier
3527 *
3528 * DESCRIPTION
3529 * Creates Geography Identifiers.
3530 *
3531 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3532 *
3533 * ARGUMENTS
3534 * IN:
3535 * p_init_msg_list Initialize message stack if it is set to
3536 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
3537 * p_geo_identifier_rec Geo_identifier type record.
3538 *
3539 * IN/OUT:
3540 * p_object_version_number
3541 * OUT:
3542 *
3543 * x_cp_request_id Concurrent Program Request Id, whenever CP
3544 * to update denormalized data gets kicked off.
3545 * x_return_status Return status after the call. The status can
3546 * be FND_API.G_RET_STS_SUCCESS (success),
3547 * FND_API.G_RET_STS_ERROR (error),
3548 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
3549 * x_msg_count Number of messages in message stack.
3550 * x_msg_data Message text if x_msg_count is 1.
3551 *
3552 * NOTES
3553 *
3554 * MODIFICATION HISTORY
3555 * 12-03-2002 Rekha Nalluri o Created.
3556 * 21-Oct-2005 Nishant Added x_cp_request_id OUT parameter
3557 * for Bug 457886
3558 *
3559 */
3560 PROCEDURE update_geo_identifier (
3561 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3562 p_geo_identifier_rec IN GEO_IDENTIFIER_REC_TYPE,
3563 p_object_version_number IN OUT NOCOPY NUMBER,
3564 x_cp_request_id OUT NOCOPY NUMBER,
3565 x_return_status OUT NOCOPY VARCHAR2,
3566 x_msg_count OUT NOCOPY NUMBER,
3567 x_msg_data OUT NOCOPY VARCHAR2
3568 )IS
3569
3570 BEGIN
3571
3572 -- Standard start of API savepoint
3573 SAVEPOINT update_geo_identifier;
3574
3575 -- Initialize message list if p_init_msg_list is set to TRUE.
3576 IF FND_API.to_Boolean(p_init_msg_list) THEN
3577 FND_MSG_PUB.initialize;
3578 END IF;
3579
3580 -- Initialize API return status to success.
3581 x_return_status := FND_API.G_RET_STS_SUCCESS;
3582
3583 -- Call to business logic.
3584 do_update_geo_identifier(
3585 p_geo_identifier_rec => p_geo_identifier_rec,
3586 p_object_version_number => p_object_version_number,
3587 x_cp_request_id => x_cp_request_id,
3588 x_return_status => x_return_status
3589 );
3590
3591 --if validation failed at any point, then raise an exception to stop processing
3592 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3593 RAISE FND_API.G_EXC_ERROR;
3594 END IF;
3595
3596 -- Standard call to get message count and if count is 1, get message info.
3597 FND_MSG_PUB.Count_And_Get(
3598 p_encoded => FND_API.G_FALSE,
3599 p_count => x_msg_count,
3600 p_data => x_msg_data);
3601
3602 EXCEPTION
3603 WHEN FND_API.G_EXC_ERROR THEN
3604 ROLLBACK TO update_geo_identifier;
3605 x_return_status := FND_API.G_RET_STS_ERROR;
3606 FND_MSG_PUB.Count_And_Get(
3607 p_encoded => FND_API.G_FALSE,
3608 p_count => x_msg_count,
3609 p_data => x_msg_data);
3610 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3611 ROLLBACK TO update_geo_identifier;
3612 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3613 FND_MSG_PUB.Count_And_Get(
3614 p_encoded => FND_API.G_FALSE,
3615 p_count => x_msg_count,
3616 p_data => x_msg_data);
3617
3618 WHEN OTHERS THEN
3619 ROLLBACK TO update_geo_identifier;
3620 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3621 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
3622 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3623 FND_MSG_PUB.ADD;
3624 FND_MSG_PUB.Count_And_Get(
3625 p_encoded => FND_API.G_FALSE,
3626 p_count => x_msg_count,
3627 p_data => x_msg_data);
3628
3629 END update_geo_identifier;
3630
3631 /**
3632 * PROCEDURE delete_geo_identifier
3633 *
3634 * DESCRIPTION
3635 * Deletes Geography Identifiers.
3636 *
3637 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3638 *
3639 * ARGUMENTS
3640 * IN:
3641 * p_init_msg_list Initialize message stack if it is set to
3642 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
3643 * p_geography_id geography id
3644 * p_identifier_type
3645 * p_identifier_subtype
3646 * p_identifier_value
3647 *
3648 * OUT:
3649 *
3650 * x_return_status Return status after the call. The status can
3651 * be FND_API.G_RET_STS_SUCCESS (success),
3652 * FND_API.G_RET_STS_ERROR (error),
3653 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
3654 * x_msg_count Number of messages in message stack.
3655 * x_msg_data Message text if x_msg_count is 1.
3656 *
3657 * NOTES
3658 *
3659 * MODIFICATION HISTORY
3660 * 01-02-2003 Rekha Nalluri o Created.
3661 *
3662 */
3663
3664 PROCEDURE delete_geo_identifier(
3665 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3666 p_geography_id IN NUMBER,
3667 p_identifier_type IN VARCHAR2,
3668 p_identifier_subtype IN VARCHAR2,
3669 p_identifier_value IN VARCHAR2,
3670 p_language_code IN VARCHAR2,
3671 x_return_status OUT NOCOPY VARCHAR2,
3672 x_msg_count OUT NOCOPY NUMBER,
3673 x_msg_data OUT NOCOPY VARCHAR2
3674 ) IS
3675 BEGIN
3676
3677 -- Standard start of API savepoint
3678 SAVEPOINT delete_geo_identifier;
3679
3680 -- Initialize message list if p_init_msg_list is set to TRUE.
3681 IF FND_API.to_Boolean(p_init_msg_list) THEN
3682 FND_MSG_PUB.initialize;
3683 END IF;
3684
3685 -- Initialize API return status to success.
3686 x_return_status := FND_API.G_RET_STS_SUCCESS;
3687
3688 -- Call to business logic.
3689 do_delete_geo_identifier(
3690 p_geography_id => p_geography_id,
3691 p_identifier_type => p_identifier_type,
3692 p_identifier_subtype => p_identifier_subtype,
3693 p_identifier_value => p_identifier_value,
3694 p_language_code => p_language_code,
3695 x_return_status => x_return_status
3696 );
3697
3698 --if validation failed at any point, then raise an exception to stop processing
3699 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3700 RAISE FND_API.G_EXC_ERROR;
3701 END IF;
3702
3703 -- Standard call to get message count and if count is 1, get message info.
3704 FND_MSG_PUB.Count_And_Get(
3705 p_encoded => FND_API.G_FALSE,
3706 p_count => x_msg_count,
3707 p_data => x_msg_data);
3708
3709 EXCEPTION
3710 WHEN FND_API.G_EXC_ERROR THEN
3711 ROLLBACK TO delete_geo_identifier;
3712 x_return_status := FND_API.G_RET_STS_ERROR;
3713 FND_MSG_PUB.Count_And_Get(
3714 p_encoded => FND_API.G_FALSE,
3715 p_count => x_msg_count,
3716 p_data => x_msg_data);
3717 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3718 ROLLBACK TO delete_geo_identifier;
3719 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3720 FND_MSG_PUB.Count_And_Get(
3721 p_encoded => FND_API.G_FALSE,
3722 p_count => x_msg_count,
3723 p_data => x_msg_data);
3724
3725 WHEN OTHERS THEN
3726 ROLLBACK TO delete_geo_identifier;
3727 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3728 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
3729 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3730 FND_MSG_PUB.ADD;
3731 FND_MSG_PUB.Count_And_Get(
3732 p_encoded => FND_API.G_FALSE,
3733 p_count => x_msg_count,
3734 p_data => x_msg_data);
3735
3736 END delete_geo_identifier;
3737
3738
3739 /**
3740 * PROCEDURE create_master_geography
3741 *
3742 * DESCRIPTION
3743 * Creates Master Geography.
3744 *
3745 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3746 *
3747 * ARGUMENTS
3748 * IN:
3749 * p_init_msg_list Initialize message stack if it is set to
3750 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
3751 * p_master_geography_rec Master Geography type record.
3752 * IN/OUT:
3753 * OUT:
3754 *
3755 * x_geography_id Return ID of the geography being created.
3756 * x_return_status Return status after the call. The status can
3757 * be FND_API.G_RET_STS_SUCCESS (success),
3758 * FND_API.G_RET_STS_ERROR (error),
3759 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
3760 * x_msg_count Number of messages in message stack.
3761 * x_msg_data Message text if x_msg_count is 1.
3762 *
3763 * NOTES
3764 *
3765 * MODIFICATION HISTORY
3766 * 12-03-2002 Rekha Nalluri o Created.
3767 *
3768 */
3769
3770 PROCEDURE create_master_geography(
3771 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3772 p_master_geography_rec IN MASTER_GEOGRAPHY_REC_TYPE,
3773 x_geography_id OUT NOCOPY NUMBER,
3774 x_return_status OUT NOCOPY VARCHAR2,
3775 x_msg_count OUT NOCOPY NUMBER,
3776 x_msg_data OUT NOCOPY VARCHAR2
3777 )IS
3778
3779 BEGIN
3780 -- Standard start of API savepoint
3781 SAVEPOINT create_master_geography;
3782 --dbms_output.put_line('In the beginning of create_master_geography');
3783
3784 -- Initialize message list if p_init_msg_list is set to TRUE.
3785 IF FND_API.to_Boolean(p_init_msg_list) THEN
3786 FND_MSG_PUB.initialize;
3787 END IF;
3788
3789 -- Initialize API return status to success.
3790 x_return_status := FND_API.G_RET_STS_SUCCESS;
3791
3792 --dbms_output.put_line('before do_create_master_geography');
3793 -- Call to business logic.
3794 do_create_master_geography(
3795 p_master_geography_rec => p_master_geography_rec,
3796 x_geography_id => x_geography_id,
3797 x_return_status => x_return_status
3798 );
3799
3800 --if validation failed at any point, then raise an exception to stop processing
3801 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3802 RAISE FND_API.G_EXC_ERROR;
3803 END IF;
3804
3805 -- Standard call to get message count and if count is 1, get message info.
3806 FND_MSG_PUB.Count_And_Get(
3807 p_encoded => FND_API.G_FALSE,
3808 p_count => x_msg_count,
3809 p_data => x_msg_data);
3810
3811 EXCEPTION
3812 WHEN FND_API.G_EXC_ERROR THEN
3813 ROLLBACK TO create_master_geography;
3814 x_return_status := FND_API.G_RET_STS_ERROR;
3815 FND_MSG_PUB.Count_And_Get(
3816 p_encoded => FND_API.G_FALSE,
3817 p_count => x_msg_count,
3818 p_data => x_msg_data);
3819 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3820 ROLLBACK TO create_master_geography;
3821 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3822 FND_MSG_PUB.Count_And_Get(
3823 p_encoded => FND_API.G_FALSE,
3824 p_count => x_msg_count,
3825 p_data => x_msg_data);
3826
3827 WHEN OTHERS THEN
3828 ROLLBACK TO create_master_geography;
3829 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3830 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
3831 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3832 FND_MSG_PUB.ADD;
3833 FND_MSG_PUB.Count_And_Get(
3834 p_encoded => FND_API.G_FALSE,
3835 p_count => x_msg_count,
3836 p_data => x_msg_data);
3837
3838 END create_master_geography;
3839
3840 /**
3841 * PROCEDURE update_geography
3842 *
3843 * DESCRIPTION
3844 * Updates Geography
3845 *
3846 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3847 *
3848 * ARGUMENTS
3849 * IN:
3850 * p_init_msg_list Initialize message stack if it is set to
3851 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
3852 * p_master_geography_rec Master Geography type record.
3853 *
3854 * IN/OUT:
3855 * p_object_version_number
3856 * OUT:
3857 *
3858 * x_return_status Return status after the call. The status can
3859 * be FND_API.G_RET_STS_SUCCESS (success),
3860 * FND_API.G_RET_STS_ERROR (error),
3861 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
3862 * x_msg_count Number of messages in message stack.
3863 * x_msg_data Message text if x_msg_count is 1.
3864 *
3865 * NOTES
3866 *
3867 * MODIFICATION HISTORY
3868 * 12-12-2002 Rekha Nalluri o Created.
3869 *
3870 */
3871 PROCEDURE update_geography (
3872 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3873 p_geography_id IN NUMBER,
3874 p_end_date IN DATE,
3875 p_geometry IN MDSYS.SDO_GEOMETRY,
3876 p_timezone_code IN VARCHAR2,
3877 p_object_version_number IN OUT NOCOPY NUMBER,
3878 x_return_status OUT NOCOPY VARCHAR2,
3879 x_msg_count OUT NOCOPY NUMBER,
3880 x_msg_data OUT NOCOPY VARCHAR2
3881 ) IS
3882
3883 BEGIN
3884
3885 -- Standard start of API savepoint
3886 SAVEPOINT update_geography;
3887
3888 -- Initialize message list if p_init_msg_list is set to TRUE.
3889 IF FND_API.to_Boolean(p_init_msg_list) THEN
3890 FND_MSG_PUB.initialize;
3891 END IF;
3892
3893 -- Initialize API return status to success.
3894 x_return_status := FND_API.G_RET_STS_SUCCESS;
3895
3896 -- Call to business logic.
3897 do_update_geography(
3898 p_geography_id => p_geography_id,
3899 p_end_date => p_end_date,
3900 p_geometry => p_geometry,
3901 p_timezone_code => p_timezone_code,
3902 p_object_version_number => p_object_version_number,
3903 x_return_status => x_return_status
3904 );
3905
3906 --if validation failed at any point, then raise an exception to stop processing
3907 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3908 RAISE FND_API.G_EXC_ERROR;
3909 END IF;
3910
3911 -- Standard call to get message count and if count is 1, get message info.
3912 FND_MSG_PUB.Count_And_Get(
3913 p_encoded => FND_API.G_FALSE,
3914 p_count => x_msg_count,
3915 p_data => x_msg_data);
3916
3917 EXCEPTION
3918 WHEN FND_API.G_EXC_ERROR THEN
3919 ROLLBACK TO update_geography;
3920 x_return_status := FND_API.G_RET_STS_ERROR;
3921 FND_MSG_PUB.Count_And_Get(
3922 p_encoded => FND_API.G_FALSE,
3923 p_count => x_msg_count,
3924 p_data => x_msg_data);
3925 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3926 ROLLBACK TO update_geography;
3927 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3928 FND_MSG_PUB.Count_And_Get(
3929 p_encoded => FND_API.G_FALSE,
3930 p_count => x_msg_count,
3931 p_data => x_msg_data);
3932
3933 WHEN OTHERS THEN
3934 ROLLBACK TO update_geography;
3935 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3936 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
3937 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3938 FND_MSG_PUB.ADD;
3939 FND_MSG_PUB.Count_And_Get(
3940 p_encoded => FND_API.G_FALSE,
3941 p_count => x_msg_count,
3942 p_data => x_msg_data);
3943
3944 END update_geography;
3945
3946 /**
3947 * PROCEDURE create_geography_range
3948 *
3949 * DESCRIPTION
3950 * Creates Geography Range.
3951 *
3952 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3953 *
3954 * ARGUMENTS
3955 * IN:
3956 * p_init_msg_list Initialize message stack if it is set to
3957 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
3958 * p_geography_range_rec Geography range type record.
3959 * IN/OUT:
3960 * OUT:
3961 *
3962 * x_return_status Return status after the call. The status can
3963 * be FND_API.G_RET_STS_SUCCESS (success),
3964 * FND_API.G_RET_STS_ERROR (error),
3965 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
3966 * x_msg_count Number of messages in message stack.
3967 * x_msg_data Message text if x_msg_count is 1.
3968 *
3969 * NOTES
3970 *
3971 * MODIFICATION HISTORY
3972 * 01-20-2003 Rekha Nalluri o Created.
3973 *
3974 */
3975
3976 PROCEDURE create_geography_range(
3977 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3978 p_geography_range_rec IN GEOGRAPHY_RANGE_REC_TYPE,
3979 x_return_status OUT NOCOPY VARCHAR2,
3980 x_msg_count OUT NOCOPY NUMBER,
3981 x_msg_data OUT NOCOPY VARCHAR2
3982 ) IS
3983
3984 p_index_name VARCHAR2(30);
3985
3986 BEGIN
3987 -- Standard start of API savepoint
3988 SAVEPOINT create_geography_range;
3989 --dbms_output.put_line('In the beginning of create_master_geography');
3990
3991 -- Initialize message list if p_init_msg_list is set to TRUE.
3992 IF FND_API.to_Boolean(p_init_msg_list) THEN
3993 FND_MSG_PUB.initialize;
3994 END IF;
3995
3996 -- Initialize API return status to success.
3997 x_return_status := FND_API.G_RET_STS_SUCCESS;
3998
3999 --dbms_output.put_line('before do_create_geography_range');
4000 -- Call to business logic.
4001 do_create_geography_range(
4002 p_geography_range_rec => p_geography_range_rec,
4003 x_return_status => x_return_status
4004 );
4005
4006 --if validation failed at any point, then raise an exception to stop processing
4007 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4008 RAISE FND_API.G_EXC_ERROR;
4009 END IF;
4010
4011 -- Standard call to get message count and if count is 1, get message info.
4012 FND_MSG_PUB.Count_And_Get(
4013 p_encoded => FND_API.G_FALSE,
4014 p_count => x_msg_count,
4015 p_data => x_msg_data);
4016
4017 EXCEPTION
4018 WHEN FND_API.G_EXC_ERROR THEN
4019 ROLLBACK TO create_geography_range;
4020 x_return_status := FND_API.G_RET_STS_ERROR;
4021 FND_MSG_PUB.Count_And_Get(
4022 p_encoded => FND_API.G_FALSE,
4023 p_count => x_msg_count,
4024 p_data => x_msg_data);
4025 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4026 ROLLBACK TO create_geography_range;
4027 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4028 FND_MSG_PUB.Count_And_Get(
4029 p_encoded => FND_API.G_FALSE,
4030 p_count => x_msg_count,
4031 p_data => x_msg_data);
4032
4033 WHEN DUP_VAL_ON_INDEX THEN
4034 ROLLBACK TO create_geography_range;
4035 x_return_status := FND_API.G_RET_STS_ERROR;
4036 HZ_UTILITY_V2PUB.find_index_name(p_index_name);
4037 IF p_index_name = 'HZ_GEOGRAPHY_RANGES_U1' THEN
4038 FND_MESSAGE.SET_NAME('AR', 'HZ_API_DUPLICATE_COLUMN');
4039 FND_MESSAGE.SET_TOKEN('COLUMN', 'geography_id,geography_from and start_date');
4040 FND_MSG_PUB.ADD;
4041 END IF;
4042 FND_MSG_PUB.Count_And_Get(
4043 p_encoded => FND_API.G_FALSE,
4044 p_count => x_msg_count,
4045 p_data => x_msg_data);
4046
4047 WHEN OTHERS THEN
4048 ROLLBACK TO create_geography_range;
4049 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4050 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
4051 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
4052 FND_MSG_PUB.ADD;
4053 FND_MSG_PUB.Count_And_Get(
4054 p_encoded => FND_API.G_FALSE,
4055 p_count => x_msg_count,
4056 p_data => x_msg_data);
4057
4058 END create_geography_range;
4059
4060 /**
4061 * PROCEDURE update_geography_range
4062 *
4063 * DESCRIPTION
4064 * Updates Geography range
4065 *
4066 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
4067 *
4068 * ARGUMENTS
4069 * IN:
4070 * p_init_msg_list Initialize message stack if it is set to
4071 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
4072 * geography_id
4073 * geography_from
4074 * start_date
4075 * end_date
4076 *
4077 * IN/OUT:
4078 * p_object_version_number
4079 * OUT:
4080 *
4081 * x_return_status Return status after the call. The status can
4082 * be FND_API.G_RET_STS_SUCCESS (success),
4083 * FND_API.G_RET_STS_ERROR (error),
4084 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
4085 * x_msg_count Number of messages in message stack.
4086 * x_msg_data Message text if x_msg_count is 1.
4087 *
4088 * NOTES
4089 *
4090 * MODIFICATION HISTORY
4091 * 01-23-2003 Rekha Nalluri o Created.
4092 *
4093 */
4094 PROCEDURE update_geography_range (
4095 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4096 p_geography_id IN NUMBER,
4097 p_geography_from IN VARCHAR2,
4098 p_start_date IN DATE,
4099 p_end_date IN DATE,
4100 p_object_version_number IN OUT NOCOPY NUMBER,
4101 x_return_status OUT NOCOPY VARCHAR2,
4102 x_msg_count OUT NOCOPY NUMBER,
4103 x_msg_data OUT NOCOPY VARCHAR2
4104 ) IS
4105
4106 BEGIN
4107
4108 -- Standard start of API savepoint
4109 SAVEPOINT update_geography_range;
4110
4111 -- Initialize message list if p_init_msg_list is set to TRUE.
4112 IF FND_API.to_Boolean(p_init_msg_list) THEN
4113 FND_MSG_PUB.initialize;
4114 END IF;
4115
4116 -- Initialize API return status to success.
4117 x_return_status := FND_API.G_RET_STS_SUCCESS;
4118
4119 -- Call to business logic.
4120 do_update_geography_range(
4121 p_geography_id => p_geography_id,
4122 p_geography_from => p_geography_from,
4123 p_start_date => p_start_date,
4124 p_end_date => p_end_date,
4125 p_object_version_number => p_object_version_number,
4126 x_return_status => x_return_status
4127 );
4128
4129 --if validation failed at any point, then raise an exception to stop processing
4130 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4131 RAISE FND_API.G_EXC_ERROR;
4132 END IF;
4133
4134 -- Standard call to get message count and if count is 1, get message info.
4135 FND_MSG_PUB.Count_And_Get(
4136 p_encoded => FND_API.G_FALSE,
4137 p_count => x_msg_count,
4138 p_data => x_msg_data);
4139
4140 EXCEPTION
4141 WHEN FND_API.G_EXC_ERROR THEN
4142 ROLLBACK TO update_geography_range;
4143 x_return_status := FND_API.G_RET_STS_ERROR;
4144 FND_MSG_PUB.Count_And_Get(
4145 p_encoded => FND_API.G_FALSE,
4146 p_count => x_msg_count,
4147 p_data => x_msg_data);
4148 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4149 ROLLBACK TO update_geography_range;
4150 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4151 FND_MSG_PUB.Count_And_Get(
4152 p_encoded => FND_API.G_FALSE,
4153 p_count => x_msg_count,
4154 p_data => x_msg_data);
4155
4156 WHEN OTHERS THEN
4157 ROLLBACK TO update_geography_range;
4158 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4159 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
4160 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
4161 FND_MSG_PUB.ADD;
4162 FND_MSG_PUB.Count_And_Get(
4163 p_encoded => FND_API.G_FALSE,
4164 p_count => x_msg_count,
4165 p_data => x_msg_data);
4166
4167 END update_geography_range;
4168
4169 /**
4170 * PROCEDURE create_zone_relation
4171 *
4172 * DESCRIPTION
4173 * Creates Zone Relation.
4174 *
4175 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
4176 *
4177 * ARGUMENTS
4178 * IN:
4179 * p_init_msg_list Initialize message stack if it is set to
4180 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
4181 * p_geography_id
4182 * p_zone_relation_tbl Zone relation table of records.
4183 * IN/OUT:
4184 * OUT:
4185 *
4186 * x_return_status Return status after the call. The status can
4187 * be FND_API.G_RET_STS_SUCCESS (success),
4188 * FND_API.G_RET_STS_ERROR (error),
4189 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
4190 * x_msg_count Number of messages in message stack.
4191 * x_msg_data Message text if x_msg_count is 1.
4192 *
4193 * NOTES
4194 *
4195 * MODIFICATION HISTORY
4196 * 01-23-2003 Rekha Nalluri o Created.
4197 *
4198 */
4199
4200 PROCEDURE create_zone_relation(
4201 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4202 p_geography_id IN NUMBER,
4203 p_zone_relation_tbl IN ZONE_RELATION_TBL_TYPE,
4204 p_created_by_module IN VARCHAR2,
4205 p_application_id IN NUMBER,
4206 x_return_status OUT NOCOPY VARCHAR2,
4207 x_msg_count OUT NOCOPY NUMBER,
4208 x_msg_data OUT NOCOPY VARCHAR2
4209 ) IS
4210
4211 BEGIN
4212 -- Standard start of API savepoint
4213 SAVEPOINT create_zone_relation;
4214 --dbms_output.put_line('In the beginning of create_zone_relation');
4215
4216 -- Initialize message list if p_init_msg_list is set to TRUE.
4217 IF FND_API.to_Boolean(p_init_msg_list) THEN
4218 FND_MSG_PUB.initialize;
4219 END IF;
4220
4221 -- Initialize API return status to success.
4222 x_return_status := FND_API.G_RET_STS_SUCCESS;
4223
4224 --dbms_output.put_line('before do_create_zone_relation');
4225 -- Call to business logic.
4226 do_create_zone_relation(
4227 p_geography_id => p_geography_id,
4228 p_zone_relation_tbl => p_zone_relation_tbl,
4229 p_created_by_module => p_created_by_module,
4230 p_application_id => p_application_id,
4231 x_return_status => x_return_status
4232 );
4233
4234 --if validation failed at any point, then raise an exception to stop processing
4235 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4236 RAISE FND_API.G_EXC_ERROR;
4237 END IF;
4238
4239 --dbms_output.put_line('after call to do_create '|| x_return_status);
4240
4241 -- Standard call to get message count and if count is 1, get message info.
4242 FND_MSG_PUB.Count_And_Get(
4243 p_encoded => FND_API.G_FALSE,
4244 p_count => x_msg_count,
4245 p_data => x_msg_data);
4246
4247 EXCEPTION
4248 WHEN FND_API.G_EXC_ERROR THEN
4249 ROLLBACK TO create_zone_relation;
4250 x_return_status := FND_API.G_RET_STS_ERROR;
4251 FND_MSG_PUB.Count_And_Get(
4252 p_encoded => FND_API.G_FALSE,
4253 p_count => x_msg_count,
4254 p_data => x_msg_data);
4255 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4256 ROLLBACK TO create_zone_relation;
4257 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4258 FND_MSG_PUB.Count_And_Get(
4259 p_encoded => FND_API.G_FALSE,
4260 p_count => x_msg_count,
4261 p_data => x_msg_data);
4262
4263 WHEN OTHERS THEN
4264 ROLLBACK TO create_zone_relation;
4265 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4266 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
4267 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
4268 FND_MSG_PUB.ADD;
4269 FND_MSG_PUB.Count_And_Get(
4270 p_encoded => FND_API.G_FALSE,
4271 p_count => x_msg_count,
4272 p_data => x_msg_data);
4273
4274 END create_zone_relation;
4275
4276
4277 /**
4278 * PROCEDURE create_zone
4279 *
4280 * DESCRIPTION
4281 * Creates Zone
4282 *
4283 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
4284 *
4285 * ARGUMENTS
4286 * IN:
4287 * p_init_msg_list Initialize message stack if it is set to
4288 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
4289 * p_zone_type
4290 * p_zone_name
4291 * p_zone_code
4292 * p_start_date
4293 * p_end_date
4294 * p_geo_data_provider
4295 * p_zone_relation_tbl table of records to create relationships
4296 * p_geometry
4297 * p_timezone_code
4298 * p_created_by_module
4299 * p_application_id
4300 * p_program_login_id
4301 *
4302 * OUT:
4303 * x_return_status
4304 * Return status after the call. The status can
4305 * be FND_API.G_RET_STS_SUCCESS (success),
4306 * FND_API.G_RET_STS_ERROR (error),
4307 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
4308 * x_msg_count Number of messages in message stack.
4309 * x_msg_data Message text if x_msg_count is 1.
4310 *
4311 * NOTES
4312 *
4313 * MODIFICATION HISTORY
4314 * 01-24-2003 Rekha Nalluri o Created.
4315 *
4316 */
4317
4318 PROCEDURE create_zone(
4319 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4320 p_zone_type IN VARCHAR2,
4321 p_zone_name IN VARCHAR2,
4322 p_zone_code IN VARCHAR2,
4323 p_zone_code_type IN VARCHAR2,
4324 p_start_date IN DATE,
4325 p_end_date IN DATE,
4326 p_geo_data_provider IN VARCHAR2,
4327 p_language_code IN VARCHAR2,
4328 p_zone_relation_tbl IN ZONE_RELATION_TBL_TYPE,
4329 p_geometry IN MDSYS.SDO_GEOMETRY,
4330 p_timezone_code IN VARCHAR2,
4331 x_geography_id OUT NOCOPY NUMBER,
4332 p_created_by_module IN VARCHAR2,
4333 p_application_id IN NUMBER,
4334 x_return_status OUT NOCOPY VARCHAR2,
4335 x_msg_count OUT NOCOPY NUMBER,
4336 x_msg_data OUT NOCOPY VARCHAR2
4337 ) IS
4338
4339 BEGIN
4340
4341
4342 -- Standard start of API savepoint
4343 SAVEPOINT create_zone;
4344 --dbms_output.put_line('In the beginning of create_zone');
4345
4346 -- Initialize message list if p_init_msg_list is set to TRUE.
4347 IF FND_API.to_Boolean(p_init_msg_list) THEN
4348 FND_MSG_PUB.initialize;
4349 END IF;
4350
4351 -- Initialize API return status to success.
4352 x_return_status := FND_API.G_RET_STS_SUCCESS;
4353
4354 --dbms_output.put_line('before do_create_zone');
4355 -- Call to business logic.
4356 do_create_zone (
4357 p_zone_type => p_zone_type,
4358 p_zone_name => p_zone_name,
4359 p_zone_code => p_zone_code,
4360 p_zone_code_type => p_zone_code_type,
4361 p_start_date => p_start_date,
4362 p_end_date => p_end_date,
4363 p_geo_data_provider => p_geo_data_provider,
4364 p_language_code => p_language_code,
4365 p_zone_relation_tbl => p_zone_relation_tbl,
4366 p_geometry => p_geometry,
4367 p_timezone_code => p_timezone_code,
4368 x_geography_id => x_geography_id,
4369 p_created_by_module => p_created_by_module,
4370 p_application_id => p_application_id,
4371 x_return_status => x_return_status
4372 );
4373
4374 --if validation failed at any point, then raise an exception to stop processing
4375 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4376 RAISE FND_API.G_EXC_ERROR;
4377 END IF;
4378
4379 -- Standard call to get message count and if count is 1, get message info.
4380 FND_MSG_PUB.Count_And_Get(
4381 p_encoded => FND_API.G_FALSE,
4382 p_count => x_msg_count,
4383 p_data => x_msg_data);
4384
4385 EXCEPTION
4386 WHEN FND_API.G_EXC_ERROR THEN
4387 ROLLBACK TO create_zone;
4388 x_return_status := FND_API.G_RET_STS_ERROR;
4389 FND_MSG_PUB.Count_And_Get(
4390 p_encoded => FND_API.G_FALSE,
4391 p_count => x_msg_count,
4392 p_data => x_msg_data);
4393 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4394 ROLLBACK TO create_zone;
4395 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4396 FND_MSG_PUB.Count_And_Get(
4397 p_encoded => FND_API.G_FALSE,
4398 p_count => x_msg_count,
4399 p_data => x_msg_data);
4400
4401 WHEN OTHERS THEN
4402 ROLLBACK TO create_zone;
4403 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4404 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
4405 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
4406 FND_MSG_PUB.ADD;
4407 FND_MSG_PUB.Count_And_Get(
4408 p_encoded => FND_API.G_FALSE,
4409 p_count => x_msg_count,
4410 p_data => x_msg_data);
4411 END create_zone;
4412
4413 END HZ_GEOGRAPHY_PUB;