[Home] [Help]
PACKAGE BODY: APPS.HZ_GEOGRAPHY_PUB
Source
1 PACKAGE BODY HZ_GEOGRAPHY_PUB AS
2 /*$Header: ARHGEOSB.pls 120.39.12020000.3 2012/12/15 01:21:37 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 | 12-14-12 o Nishant Singhai Modified cursor c_get_all_children
886 | for bug 14705367
887 |
888 +===========================================================================*/
889
890 PROCEDURE do_update_relationship(
891 p_relationship_id IN NUMBER,
892 p_status IN VARCHAR2,
893 p_object_version_number IN OUT NOCOPY NUMBER,
894 x_return_status IN OUT NOCOPY VARCHAR2
895 ) IS
896
897 l_relationship_rec HZ_RELATIONSHIP_V2PUB.relationship_rec_type;
898 x_msg_count NUMBER;
899 x_msg_data VARCHAR2(2000);
900 l_party_object_version_number NUMBER := NULL;
901 l_parent_count NUMBER;
902 l_geography_type VARCHAR2(30);
903 l_update_flag VARCHAR2(1);
904 l_denorm_flag VARCHAR2(1);
905 l_geography_id NUMBER;
906 l_parent_geography_id NUMBER;
907 l_end_date DATE;
908 l_count NUMBER;
909 l_relationship_type VARCHAR2(30);
910 --p_subject_flag VARCHAR2(1);
911 CURSOR c_get_all_children IS
912 SELECT child_id, child_object_type
913 FROM hz_hierarchy_nodes
914 WHERE hierarchy_type='MASTER_REF'
915 AND parent_id=l_geography_id
916 AND child_table_name = 'HZ_GEOGRAPHIES'
917 AND NVL(status,'A') = 'A'
918 AND (effective_end_date IS NULL
919 OR effective_end_date > sysdate)
920 ;
921 -- removed below condition for bug 14705367 (Nishant 12/14/12)
922 -- we need to pick up all children.. not just self and level 1
923 -- AND (level_number = 1
924 -- OR parent_id = child_id);
925 l_get_all_children c_get_all_children%ROWTYPE;
926 l_remove_denorm_flag VARCHAR2(1);
927 BEGIN
928
929 hz_utility_v2pub.validate_mandatory(
930 p_create_update_flag => 'U',
931 p_column => 'object_version_number',
932 p_column_value => p_object_version_number,
933 x_return_status => x_return_status
934 );
935
936 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
937 RAISE FND_API.G_EXC_ERROR;
938 END IF;
939
940 BEGIN
941 -- validate relationship_id
942 SELECT subject_id,object_id,relationship_type into l_parent_geography_id,l_geography_id,l_relationship_type
943 FROM hz_relationships
944 WHERE relationship_id = p_relationship_id
945 AND relationship_code = 'PARENT_OF';
946
947 EXCEPTION WHEN NO_DATA_FOUND THEN
948 fnd_message.set_name('AR', 'HZ_GEO_NO_RECORD');
949 fnd_message.set_token('token1', 'relationship');
950 fnd_message.set_token('token1', 'relationship_id '||p_relationship_id);
951 fnd_msg_pub.add;
952 RAISE FND_API.G_EXC_ERROR;
953 END;
954
955 --dbms_output.put_line('after master relation validation');
956
957 IF p_status = 'I' THEN
958 l_end_date := sysdate;
959 ELSIF p_status = 'A' THEN
960 l_end_date := to_date('31-12-4712','DD-MM-YYYY');
961 END IF;
962
963
964 ----dbms_output.put_line('l_end_date is '|| to_char(l_end_date));
965 l_geography_type := HZ_GEOGRAPHY_VALIDATE_PVT.get_geography_type(
966 p_geography_id => l_geography_id,
967 x_return_status => x_return_status);
968
969 ----dbms_output.put_line('after getting geography type '||l_geography_type);
970 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
971 RAISE FND_API.G_EXC_ERROR;
972 END IF;
973
974 -- construct the relationship record for update
975 l_relationship_rec.relationship_id := p_relationship_id;
976 l_relationship_rec.start_date := NULL;
977 l_relationship_rec.end_date := l_end_date;
978 l_relationship_rec.status := p_status;
979 l_relationship_rec.created_by_module := NULL;
980 l_relationship_rec.application_id := NULL;
981
982 IF l_relationship_type <> 'MASTER_REF' THEN
983 HZ_RELATIONSHIP_V2PUB.update_relationship(
984 p_init_msg_list =>'F',
985 p_relationship_rec => l_relationship_rec,
986 p_object_version_number => p_object_version_number,
987 p_party_object_version_number => l_party_object_version_number,
988 x_return_status => x_return_status,
989 x_msg_count => x_msg_count,
990 x_msg_data => x_msg_data
991 );
992
993 ELSE
994
995
996 BEGIN
997 -- check whether there exists atleast one parent for this child before end dating this relation
998 SELECT count(subject_id) INTO l_parent_count
999 FROM HZ_RELATIONSHIPS
1000 WHERE object_id = l_geography_id
1001 AND object_type=l_geography_type
1002 AND object_table_name='HZ_GEOGRAPHIES'
1003 AND subject_table_name = 'HZ_GEOGRAPHIES'
1004 AND relationship_type='MASTER_REF'
1005 AND relationship_code = 'PARENT_OF'
1006 AND status = 'A';
1007
1008 --dbms_output.put_line('l_parent_count '|| to_char(l_parent_count));
1009 EXCEPTION WHEN NO_DATA_FOUND THEN
1010 FND_MESSAGE.SET_NAME( 'AR', 'HZ_GEO_NO_RECORD' );
1011 FND_MESSAGE.SET_TOKEN( 'TOKEN1', 'Relationship');
1012 FND_MESSAGE.SET_TOKEN( 'TOKEN2', 'object_id '||l_geography_id||',object_type '||l_geography_type||' and relationship_type MASTER_REF');
1013 FND_MSG_PUB.ADD;
1014 RAISE FND_API.G_EXC_ERROR;
1015 END;
1016
1017
1018 -- if there exists only one parent then do not update
1019 IF (l_parent_count = 1 AND p_status = 'I') THEN
1020 FND_MESSAGE.SET_NAME( 'AR', 'HZ_GEO_SINGLE_PARENT' );
1021 FND_MESSAGE.SET_TOKEN( 'REL_ID', p_relationship_id);
1022 FND_MESSAGE.SET_TOKEN( 'GEO_ID', l_geography_id);
1023 FND_MSG_PUB.ADD;
1024 RAISE FND_API.G_EXC_ERROR;
1025 --l_update_flag := 'N';
1026 END IF;
1027 ----dbms_output.put_line ('parent_count '||to_char(l_parent_count));
1028 IF (l_parent_count = 2 AND p_status= 'I') THEN
1029 l_update_flag := 'Y';
1030 l_denorm_flag := 'Y';
1031 ELSIF (l_parent_count = 1 AND p_status = 'A') THEN
1032 l_update_flag := 'Y';
1033 l_remove_denorm_flag := 'Y';
1034 ELSIF l_parent_count > 2 THEN
1035 l_update_flag := 'Y';
1036 --l_remove_denorm_flag := 'Y';
1037 END IF;
1038 ----dbms_output.put_line('l_update_flag '||l_update_flag);
1039
1040 IF l_update_flag = 'Y' THEN
1041 -- call relationship API for update
1042 HZ_RELATIONSHIP_V2PUB.update_relationship(
1043 p_init_msg_list =>'F',
1044 p_relationship_rec => l_relationship_rec,
1045 p_object_version_number => p_object_version_number,
1046 p_party_object_version_number => l_party_object_version_number,
1047 x_return_status => x_return_status,
1048 x_msg_count => x_msg_count,
1049 x_msg_data => x_msg_data
1050 );
1051
1052 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1053 RAISE FND_API.G_EXC_ERROR;
1054 END IF;
1055 END IF;
1056
1057
1058 IF l_denorm_flag = 'Y' THEN
1059
1060 -- means there is only one parent left. So set the multiple_parent_flag to 'N' for the geography_id and
1061 -- de-normalize this relation in HZ_GEOGRAPHIES for the geography_id and for all its children.
1062
1063 UPDATE hz_geographies
1064 SET multiple_parent_flag = 'N'
1065 WHERE geography_id = l_geography_id;
1066
1067 OPEN c_get_all_children;
1068 LOOP
1069 FETCH c_get_all_children into l_get_all_children;
1070 EXIT WHEN c_get_all_children%NOTFOUND;
1071
1072 -- call the procedure to denormalize
1073 denormalize_relation(
1074 p_geography_id => l_get_all_children.child_id,
1075 p_parent_geography_id => l_geography_id,
1076 p_geography_type => l_get_all_children.child_object_type,
1077 x_return_status => x_return_status
1078 );
1079
1080 -- de-normalize the relation in the children of geography_id too
1081
1082 END LOOP;
1083 CLOSE c_get_all_children;
1084 END IF;
1085
1086 IF l_remove_denorm_flag = 'Y' THEN
1087 -- set the multiple parent flag for this geography_id to 'Y'
1088 UPDATE hz_geographies
1089 SET multiple_parent_flag = 'Y'
1090 WHERE geography_id = l_geography_id;
1091
1092 remove_denorm(
1093 p_geography_id => l_geography_id,
1094 p_geography_type => l_geography_type);
1095 END IF;
1096 END IF;
1097 END do_update_relationship;
1098
1099 /*===========================================================================+
1100 | PROCEDURE
1101 | 3
1102 |
1103 | DESCRIPTION
1104 | Creates a Geography Identifier
1105 |
1106 | SCOPE - PRIVATE
1107 |
1108 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1109 |
1110 | ARGUMENTS : IN:
1111 |
1112 | p_geo_identifier_rec
1113 | OUT:
1114 | x_return_status
1115 |
1116 | IN/ OUT:
1117 |
1118 | RETURNS : NONE
1119 |
1120 | NOTES
1121 |
1122 | MODIFICATION HISTORY
1123 | 12-03-02 o Rekha Nalluri created.
1124 | 07-25-05 o Idris Ali Bug 4493925: language code is
1125 | assinged a default value when it has a null value
1126 +===========================================================================*/
1127
1128 PROCEDURE do_create_geo_identifier(
1129 p_geo_identifier_rec IN GEO_IDENTIFIER_REC_TYPE,
1130 x_return_status IN OUT NOCOPY VARCHAR2
1131 ) IS
1132
1133 l_rowid VARCHAR2(64);
1134 l_geography_use VARCHAR2(30);
1135 l_geography_type VARCHAR2(30);
1136 l_geography_id NUMBER;
1137 l_identifier_value VARCHAR2(360);
1138 l_geo_element_col VARCHAR2(30);
1139 l_country_code VARCHAR2(2);
1140 l_count NUMBER;
1141 --l_stmnt VARCHAR2(1000);
1142 l_geo_element_code VARCHAR2(30);
1143 l_identifier_subtype VARCHAR2(30);
1144 l_geo_element_id VARCHAR2(30);
1145 l_language_code VARCHAR2(4);
1146 CURSOR c_get_all_parents IS
1147 SELECT subject_id
1148 FROM hz_relationships
1149 WHERE object_id = p_geo_identifier_rec.geography_id
1150 AND relationship_type = 'MASTER_REF'
1151 AND status = 'A';
1152 l_get_all_parents c_get_all_parents%ROWTYPE;
1153
1154 BEGIN
1155
1156 l_geography_id := p_geo_identifier_rec.geography_id;
1157
1158 IF p_geo_identifier_rec.identifier_type = 'CODE' THEN
1159 l_identifier_value := UPPER(p_geo_identifier_rec.identifier_value);
1160 ELSE
1161 l_identifier_value := p_geo_identifier_rec.identifier_value;
1162 END IF;
1163
1164 -- Bug 4493925: default language_code in case of NULL
1165
1166 IF p_geo_identifier_rec.language_code IS NULL THEN
1167 l_language_code := userenv('LANG');
1168 ELSE
1169 l_language_code := p_geo_identifier_rec.language_code;
1170 END IF;
1171
1172 -- Bug 4493925: default language_code in case of NULL
1173
1174 -- construct the statement to de-normalize the identifier wherever it is used in hz_geographies
1175
1176 l_identifier_subtype := p_geo_identifier_rec.identifier_subtype;
1177 --dbms_output.put_line ('Before validate geo identifier');
1178
1179
1180 -- validate geography identifier record for create
1181 HZ_GEOGRAPHY_VALIDATE_PVT.validate_geo_identifier(
1182 p_geo_identifier_rec => p_geo_identifier_rec,
1183 p_create_update_flag => 'C',
1184 x_return_status => x_return_status
1185 );
1186
1187 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1188 RAISE FND_API.G_EXC_ERROR;
1189 END IF;
1190
1191 -- check for the duplicate name and code
1192
1193
1194 -- get geography_type and geography_use
1195 BEGIN
1196
1197 SELECT geography_use,geography_type INTO l_geography_use,l_geography_type
1198 FROM HZ_GEOGRAPHIES
1199 WHERE geography_id = p_geo_identifier_rec.geography_id;
1200
1201 EXCEPTION when no_data_found THEN
1202 fnd_message.set_name('AR', 'HZ_GEO_NO_RECORD');
1203 fnd_message.set_token('TOKEN1','Geography record');
1204 fnd_message.set_token('TOKEN2','geography_id '||p_geo_identifier_rec.geography_id);
1205 fnd_msg_pub.add;
1206 RAISE FND_API.G_EXC_ERROR;
1207 END;
1208
1209 IF l_geography_use = 'MASTER_REF' THEN
1210 IF l_geography_type <> 'COUNTRY' THEN
1211 -- check for the duplicate name/code with in the parents of the geography
1212 OPEN c_get_all_parents;
1213 LOOP
1214 FETCH c_get_all_parents INTO l_get_all_parents;
1215 EXIT WHEN c_get_all_parents%NOTFOUND;
1216 IF p_geo_identifier_rec.identifier_type = 'NAME' THEN
1217 check_duplicate_name(p_parent_id => l_get_all_parents.subject_id,
1218 p_child_id => p_geo_identifier_rec.geography_id,
1219 p_child_name=> p_geo_identifier_rec.identifier_value,
1220 p_child_type => l_geography_type,
1221 p_child_identifier_subtype => l_identifier_subtype,
1222 p_child_language => l_language_code,
1223 x_return_status => x_return_status
1224 );
1225 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1226 RAISE FND_API.G_EXC_ERROR;
1227 EXIT;
1228 END IF;
1229 ELSIF p_geo_identifier_rec.identifier_type = 'CODE' THEN
1230 check_duplicate_code(p_parent_id => l_get_all_parents.subject_id,
1231 p_child_id => p_geo_identifier_rec.geography_id,
1232 p_child_code => p_geo_identifier_rec.identifier_value,
1233 p_child_type => l_geography_type,
1234 p_child_identifier_subtype => l_identifier_subtype,
1235 p_child_language => l_language_code,
1236 x_return_status => x_return_status
1237 );
1238
1239 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1240 RAISE FND_API.G_EXC_ERROR;
1241 EXIT;
1242 END IF;
1243 END IF;
1244 END LOOP;
1245 CLOSE c_get_all_parents;
1246 ELSE
1247 -- Bug 5411429 : check for the duplicate name/code for Country geographies.
1248 SELECT count(*) INTO l_count
1249 FROM hz_geography_identifiers
1250 WHERE identifier_type = p_geo_identifier_rec.identifier_type
1251 AND identifier_subtype = l_identifier_subtype
1252 AND language_code = p_geo_identifier_rec.language_code
1253 AND UPPER(identifier_value) = UPPER(p_geo_identifier_rec.identifier_value)
1254 AND geography_type = 'COUNTRY';
1255
1256 IF l_count > 0 THEN
1257 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_DUP_COUNTRY_IDEN');
1258 FND_MESSAGE.SET_TOKEN('IDEN_VAL', p_geo_identifier_rec.identifier_value);
1259 FND_MSG_PUB.ADD;
1260 x_return_status := fnd_api.g_ret_sts_error;
1261 RAISE FND_API.G_EXC_ERROR;
1262 END IF;
1263 END IF;
1264
1265 END IF;
1266
1267 -- Primary name should be of subtype 'STANDARD_NAME'
1268 IF (p_geo_identifier_rec.identifier_type = 'NAME' AND p_geo_identifier_rec.primary_flag = 'Y') THEN
1269 IF p_geo_identifier_rec.identifier_subtype <> 'STANDARD_NAME' THEN
1270 FND_MESSAGE.SET_NAME( 'AR', 'HZ_GEO_INVALID_SUBTYPE' );
1271 FND_MESSAGE.SET_TOKEN( 'SUBTYPE', p_geo_identifier_rec.identifier_subtype);
1272 FND_MSG_PUB.ADD;
1273 x_return_status := FND_API.G_RET_STS_ERROR;
1274 RAISE FND_API.G_EXC_ERROR;
1275 END IF;
1276 END IF;
1277
1278 -- If this is the first row for the language_code for type NAME then if it is of subtype other than STANDAR_NAME,
1279 -- , make it as STANDARD_NAME
1280
1281 IF (p_geo_identifier_rec.identifier_type = 'NAME' AND p_geo_identifier_rec.identifier_subtype <> 'STANDARD_NAME') THEN
1282
1283 SELECT count(*) INTO l_count from
1284 hz_geography_identifiers
1285 WHERE geography_id = p_geo_identifier_rec.geography_id
1286 AND language_code = l_language_code;
1287
1288 IF l_count = 0 THEN
1289 l_identifier_subtype:= 'STANDARD_NAME';
1290 END IF;
1291 END IF;
1292
1293 IF p_geo_identifier_rec.primary_flag = 'Y' THEN
1294 IF (p_geo_identifier_rec.identifier_type='NAME' AND p_geo_identifier_rec.identifier_subtype = 'STANDARD_NAME') THEN
1295 -- check if there exists a STANDARD_NAME + Primary Flag = Y
1296 SELECT count(*) INTO l_count
1297 FROM hz_geography_identifiers
1298 WHERE geography_id = p_geo_identifier_rec.geography_id
1299 AND identifier_type = 'NAME'
1300 AND identifier_subtype = 'STANDARD_NAME'
1301 AND primary_flag = 'Y'
1302 AND language_code = l_language_code;
1303
1304 IF l_count > 0 THEN
1305 -- update STANDARD_NAME+Y to STANDARD_NAME+N
1306 UPDATE hz_geography_identifiers
1307 SET primary_flag = 'N'
1308 WHERE geography_id = p_geo_identifier_rec.geography_id
1309 AND identifier_type = 'NAME'
1310 AND identifier_subtype = 'STANDARD_NAME'
1311 AND primary_flag = 'Y'
1312 AND language_code = l_language_code;
1313 END IF;
1314
1315 l_identifier_subtype := 'STANDARD_NAME';
1316 END IF;
1317
1318 --check if there exists a primary row already for this geography_id
1319 SELECT count(*) INTO l_count
1320 FROM HZ_GEOGRAPHY_IDENTIFIERS
1321 WHERE geography_id = p_geo_identifier_rec.geography_id
1322 AND identifier_type = p_geo_identifier_rec.identifier_type
1323 AND primary_flag='Y';
1324
1325 IF l_count > 0 THEN
1326 -- set the primary_flag of the existing primary identifier to 'N'
1327 UPDATE hz_geography_identifiers
1328 SET primary_flag = 'N'
1329 WHERE geography_id=p_geo_identifier_rec.geography_id
1330 AND identifier_type = p_geo_identifier_rec.identifier_type
1331 AND primary_flag = 'Y';
1332
1333 END IF;
1334 END IF;
1335
1336 ----dbms_output.put_line('before identifier insert');
1337
1338 -- call table handler to insert the row in hz_geography_identifiers
1339
1340 HZ_GEOGRAPHY_IDENTIFIERS_PKG.insert_row(
1341 x_rowid => l_rowid,
1342 x_geography_id => p_geo_identifier_rec.geography_id,
1343 x_identifier_subtype => l_identifier_subtype,
1344 x_identifier_value => l_identifier_value,
1345 x_geo_data_provider => p_geo_identifier_rec.geo_data_provider,
1346 x_object_version_number => 1,
1347 x_identifier_type => p_geo_identifier_rec.identifier_type,
1348 x_primary_flag => p_geo_identifier_rec.primary_flag,
1349 x_language_code => UPPER(l_language_code),
1350 x_geography_use => l_geography_use,
1351 x_geography_type => UPPER(l_geography_type),
1352 x_created_by_module => p_geo_identifier_rec.created_by_module,
1353 x_application_id => p_geo_identifier_rec.application_id,
1354 x_program_login_id => NULL
1355 );
1356
1357 ----dbms_output.put_line('after identifier insert');
1358
1359 IF (l_geography_type <> 'COUNTRY' AND l_geography_use = 'MASTER_REF') THEN
1360 BEGIN
1361 --get geography_element_column,country_code from hz_geo_structure_levels for this geography_id
1362 --dbms_output.put_line('before getting geo_element_column l_geography_id '||l_geography_id);
1363
1364 SELECT distinct geography_element_column,country_code
1365 INTO l_geo_element_col,l_country_code
1366 FROM HZ_GEO_STRUCTURE_LEVELS
1367 WHERE geography_id = (SELECT geography_id FROM
1368 HZ_GEOGRAPHIES WHERE COUNTRY_CODE=(SELECT country_code from hz_geographies
1369 WHERE geography_id = l_geography_id)
1370 AND geography_type='COUNTRY')
1371 AND geography_type = l_geography_type;
1372 l_geo_element_code := l_geo_element_col||'_CODE';
1373 l_geo_element_id := l_geo_element_col||'_ID';
1374 --dbms_output.put_line('after getting geo_element_column'||l_geo_element_col);
1375 EXCEPTION
1376 WHEN NO_DATA_FOUND THEN
1377 --dbms_output.put_line('in the error');
1378 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_NO_RECORD');
1379 FND_MESSAGE.SET_TOKEN('TOKEN1', 'geography structure level');
1380 FND_MESSAGE.SET_TOKEN('TOKEN2', 'geography_id: '||to_char(l_count)||',country_code: '||l_country_code||',geography_type: '||l_geography_type);
1381 FND_MSG_PUB.ADD;
1382 x_return_status := FND_API.G_RET_STS_ERROR;
1383 END;
1384 END IF;
1385
1386
1387 -- denormalize the primary identifier in HZ_GEOGRAPHIES for identifier_type='NAME' and 'CODE'
1388 -- for this geography_id
1389 IF p_geo_identifier_rec.primary_flag = 'Y' THEN
1390 IF p_geo_identifier_rec.identifier_type='CODE' THEN
1391 -- Bug 4591502 : ISSUE # 17
1392 -- Do not denormalize identfier code in country_code
1393 /* IF l_geography_type = 'COUNTRY' THEN
1394 UPDATE HZ_GEOGRAPHIES
1395 SET geography_code = p_geo_identifier_rec.identifier_value,
1396 country_code = p_geo_identifier_rec.identifier_value
1397 WHERE geography_id = p_geo_identifier_rec.geography_id;
1398
1399 ELSE
1400 */
1401 UPDATE HZ_GEOGRAPHIES
1402 -- Bug 4579868 : ISSUE # 11
1403 -- denormalize upper code and not identifier_value directly
1404 -- SET geography_code = p_geo_identifier_rec.identifier_value
1405 SET geography_code = l_identifier_value,
1406 last_updated_by = hz_utility_pub.LAST_UPDATED_BY,
1407 last_update_date = hz_utility_pub.LAST_UPDATE_DATE
1408 WHERE geography_id = p_geo_identifier_rec.geography_id;
1409 -- END IF;
1410
1411 IF l_geo_element_col IS NOT NULL THEN
1412 ----dbms_output.put_line('after update, before de-normaloizing code');
1413 IF l_geo_element_col IN ('GEOGRAPHY_ELEMENT1','GEOGRAPHY_ELEMENT2','GEOGRAPHY_ELEMENT3','GEOGRAPHY_ELEMENT4','GEOGRAPHY_ELEMENT5') THEN
1414 EXECUTE IMMEDIATE 'UPDATE HZ_GEOGRAPHIES SET '||l_geo_element_code||'= :l_identifier_value '||
1415 ' WHERE country_code= :l_country_code '||
1416 ' AND '||l_geo_element_id||'= :l_geography_id '
1417 USING l_identifier_value, l_country_code, l_geography_id;
1418
1419 END IF;
1420
1421 --dbms_output.put_line('After first execute');
1422
1423 END IF;
1424 END IF;
1425 -- END IF;
1426 ----dbms_output.put_line('l_stmnt is '||l_stmnt);
1427 ----dbms_output.put_line('after de-normaloizing code');
1428 IF p_geo_identifier_rec.identifier_type='NAME' THEN
1429 UPDATE HZ_GEOGRAPHIES
1430 SET geography_name = p_geo_identifier_rec.identifier_value,
1431 last_updated_by = hz_utility_pub.LAST_UPDATED_BY,
1432 last_update_date = hz_utility_pub.LAST_UPDATE_DATE
1433 WHERE geography_id = p_geo_identifier_rec.geography_id;
1434 IF l_geo_element_col IS NOT NULL THEN
1435 EXECUTE IMMEDIATE 'UPDATE HZ_GEOGRAPHIES SET '||l_geo_element_col||'= :l_identifier_value '||
1436 ' WHERE country_code= :l_country_code '||
1437 ' AND '||l_geo_element_id||'= :l_geography_id '
1438 USING l_identifier_value, l_country_code, l_geography_id;
1439
1440 END IF;
1441 --dbms_output.put_line('After second execute');
1442 END IF;
1443 ----dbms_output.put_line('after de-normaloizing name');
1444 END IF;
1445
1446
1447 END do_create_geo_identifier;
1448
1449
1450 -- update geography identifier procedure
1451 PROCEDURE do_update_geo_identifier(
1452 p_geo_identifier_rec IN GEO_IDENTIFIER_REC_TYPE,
1453 p_object_version_number IN OUT NOCOPY NUMBER,
1454 x_cp_request_id OUT NOCOPY NUMBER,
1455 x_return_status IN OUT NOCOPY VARCHAR2
1456 )IS
1457
1458 l_count NUMBER;
1459 l_rowid VARCHAR2(64);
1460 l_geography_use VARCHAR2(30);
1461 l_geography_type VARCHAR2(30);
1462 l_geo_element_col VARCHAR2(30);
1463 l_country_code VARCHAR2(2);
1464 -- l_stmnt VARCHAR2(1000);
1465 l_geo_element_code VARCHAR2(30);
1466 l_geo_element_id VARCHAR2(30);
1467 l_old_primary_flag VARCHAR2(1);
1468 l_geo_identifier_subtype VARCHAR2(30);
1469 l_object_version_number NUMBER;
1470
1471 l_new_geo_subtype VARCHAR2(30);
1472 l_new_geo_value VARCHAR2(360);
1473 l_subtype_updated VARCHAR2(1);
1474 l_name_updated VARCHAR2(1);
1475
1476 CURSOR c_get_all_parents IS
1477 SELECT subject_id
1478 FROM hz_relationships
1479 WHERE object_id = p_geo_identifier_rec.geography_id
1480 AND object_table_name = 'HZ_GEOGRAPHIES'
1481 AND relationship_type = 'MASTER_REF'
1482 AND status = 'A';
1483
1484 l_get_all_parents c_get_all_parents%ROWTYPE;
1485
1486 BEGIN
1487
1488 l_geo_identifier_subtype := p_geo_identifier_rec.identifier_subtype;
1489
1490 HZ_GEOGRAPHY_VALIDATE_PVT.validate_geo_identifier(
1491 p_geo_identifier_rec => p_geo_identifier_rec,
1492 p_create_update_flag => 'U',
1493 x_return_status => x_return_status
1494 );
1495
1496 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1497 RAISE FND_API.G_EXC_ERROR;
1498 END IF;
1499
1500 hz_utility_v2pub.validate_mandatory(
1501 p_create_update_flag => 'U',
1502 p_column => 'object_version_number',
1503 p_column_value => p_object_version_number,
1504 x_return_status => x_return_status
1505 );
1506 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1507 RAISE FND_API.G_EXC_ERROR;
1508 END IF;
1509
1510 BEGIN
1511
1512 SELECT rowid,geography_type,geography_use,primary_flag,object_version_number
1513 INTO l_rowid,l_geography_type,l_geography_use,l_old_primary_flag,l_object_version_number
1514 FROM hz_geography_identifiers
1515 WHERE geography_id = p_geo_identifier_rec.geography_id
1516 AND identifier_type = p_geo_identifier_rec.identifier_type
1517 AND identifier_subtype = p_geo_identifier_rec.identifier_subtype
1518 AND identifier_value = p_geo_identifier_rec.identifier_value
1519 AND language_code = p_geo_identifier_rec.language_code
1520 FOR UPDATE of geography_id NOWAIT;
1521
1522 --validate object_version_number
1523 IF l_object_version_number <> p_object_version_number THEN
1524 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
1525 FND_MESSAGE.SET_TOKEN('TABLE', 'hz_geography_identifiers');
1526 FND_MSG_PUB.ADD;
1527 RAISE FND_API.G_EXC_ERROR;
1528 ELSE
1529 p_object_version_number := l_object_version_number + 1;
1530 END IF;
1531
1532 EXCEPTION WHEN NO_DATA_FOUND THEN
1533 FND_MESSAGE.SET_NAME( 'AR', 'HZ_GEO_NO_RECORD' );
1534 FND_MESSAGE.SET_TOKEN('TOKEN1', 'geography_identifier');
1535 FND_MESSAGE.SET_TOKEN('TOKEN2', 'geography_id: '||p_geo_identifier_rec.geography_id||', identifier_type: '||
1536 p_geo_identifier_rec.identifier_type||', identifier_subtype: '||p_geo_identifier_rec.identifier_subtype||', identifier_value: '||
1537 p_geo_identifier_rec.identifier_value||', language_code: '||p_geo_identifier_rec.language_code);
1538 FND_MSG_PUB.ADD;
1539 RAISE FND_API.G_EXC_ERROR;
1540 END;
1541
1542 -- Validate new subtype. Update only if valid and geo type is CODE.
1543 IF (p_geo_identifier_rec.identifier_type = 'CODE') THEN
1544 l_new_geo_subtype := p_geo_identifier_rec.new_identifier_subtype;
1545 -- validate new subtype
1546 IF (l_new_geo_subtype IS NOT NULL) THEN
1547 HZ_UTILITY_V2PUB.validate_lookup(
1548 p_column => 'geography_code_type',
1549 p_lookup_type => 'HZ_GEO_IDENTIFIER_SUBTYPE',
1550 p_column_value => l_new_geo_subtype,
1551 x_return_status => x_return_status
1552 );
1553 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1554 RAISE FND_API.G_EXC_ERROR;
1555 END IF;
1556 l_subtype_updated := 'Y';
1557 ELSE -- new geo subtype is null (i.e. no need to update), use the old subtype
1558 l_new_geo_subtype := l_geo_identifier_subtype;
1559 l_subtype_updated := 'N';
1560 END IF;
1561 ELSE -- if idenifier type is NAME (only 1 subtype 'STANDARD_NAME' is allowed)
1562 l_new_geo_subtype := p_geo_identifier_rec.new_identifier_subtype;
1563 -- validate new subtype
1564 IF (l_new_geo_subtype IS NOT NULL) THEN
1565 IF (p_geo_identifier_rec.new_identifier_subtype <> 'STANDARD_NAME') THEN
1566 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_INVALID_LOOKUP' );
1567 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'identifier_subtype' );
1568 FND_MESSAGE.SET_TOKEN( 'LOOKUP_TYPE', 'HZ_GEO_IDENTIFIER_SUBTYPE' );
1569 FND_MSG_PUB.ADD;
1570 x_return_status := FND_API.G_RET_STS_ERROR;
1571 END IF;
1572 l_subtype_updated := 'Y';
1573 ELSE -- new geo subtype is null (i.e. no need to update), use the old subtype
1574 l_new_geo_subtype := l_geo_identifier_subtype;
1575 l_subtype_updated := 'N';
1576 END IF;
1577 END IF;
1578
1579 -- Validate new identifier value (it will be NULL if it is not to be updated)
1580 IF (p_geo_identifier_rec.new_identifier_value IS NOT NULL) THEN
1581 IF p_geo_identifier_rec.identifier_type = 'CODE' THEN
1582 l_new_geo_value := UPPER(p_geo_identifier_rec.new_identifier_value);
1583 ELSE
1584 l_new_geo_value := p_geo_identifier_rec.new_identifier_value;
1585 END IF;
1586 l_name_updated := 'Y';
1587 ELSE -- not to be updated, so retain the old value
1588 l_new_geo_value := p_geo_identifier_rec.identifier_value;
1589 l_name_updated := 'N';
1590 END IF;
1591
1592 -- check if name is duplicate within its parents
1593 IF (l_name_updated = 'Y') THEN
1594 IF l_geography_use = 'MASTER_REF' THEN
1595 IF l_geography_type <> 'COUNTRY' THEN
1596 -- check for the duplicate name/code with in the parents of the geography
1597 OPEN c_get_all_parents;
1598 LOOP
1599 FETCH c_get_all_parents INTO l_get_all_parents;
1600 EXIT WHEN c_get_all_parents%NOTFOUND;
1601 IF p_geo_identifier_rec.identifier_type = 'NAME' THEN
1602 -- check if the name is duplicated with in the parent of p_child_id
1603 -- Added Subtype and language check for bug 4703418 on 28-Nov-2005 (Nishant)
1604 SELECT count(*) INTO l_count
1605 FROM hz_geography_identifiers
1606 WHERE identifier_type='NAME'
1607 AND identifier_subtype = l_new_geo_subtype
1608 AND language_code = p_geo_identifier_rec.language_code
1609 AND UPPER(identifier_value) = UPPER(l_new_geo_value)
1610 AND geography_id IN (SELECT object_id
1611 FROM hz_relationships
1612 WHERE subject_id = l_get_all_parents.subject_id
1613 AND object_type = l_geography_type
1614 AND status = 'A'
1615 AND relationship_type = 'MASTER_REF')
1616 AND ROWID <> l_rowid ;
1617
1618 IF l_count > 0 THEN
1619
1620 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_DUPLICATE_VALUE');
1621 FND_MESSAGE.SET_TOKEN('IDENT_TYPE', 'NAME');
1622 FND_MESSAGE.SET_TOKEN('VALUE', l_new_geo_value);
1623 FND_MESSAGE.SET_TOKEN('GEO_ID', p_geo_identifier_rec.geography_id);
1624 FND_MESSAGE.SET_TOKEN('PARENT_GEO_ID', l_get_all_parents.subject_id);
1625 FND_MSG_PUB.ADD;
1626 x_return_status := fnd_api.g_ret_sts_error;
1627 RAISE FND_API.G_EXC_ERROR;
1628 EXIT;
1629 END IF;
1630 ELSIF p_geo_identifier_rec.identifier_type = 'CODE' THEN
1631 -- check if the name is duplicated with in the parent of p_child_id
1632 -- Added Subtype and language check for bug 4703418 on 28-Nov-2005 (Nishant)
1633 SELECT count(*) INTO l_count
1634 FROM hz_geography_identifiers
1635 WHERE identifier_type='CODE'
1636 AND identifier_subtype = l_new_geo_subtype
1637 AND language_code = p_geo_identifier_rec.language_code
1638 AND identifier_value = UPPER(l_new_geo_value)
1639 AND geography_id IN (SELECT object_id
1640 FROM hz_relationships
1641 WHERE subject_id = l_get_all_parents.subject_id
1642 AND object_type = l_geography_type
1643 AND status = 'A'
1644 AND relationship_type = 'MASTER_REF')
1645 AND ROWID <> l_rowid ;
1646
1647 IF l_count > 0 THEN
1648
1649 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_DUPLICATE_VALUE');
1650 FND_MESSAGE.SET_TOKEN('IDENT_TYPE', 'CODE');
1651 FND_MESSAGE.SET_TOKEN('VALUE', l_new_geo_value);
1652 FND_MESSAGE.SET_TOKEN('GEO_ID', p_geo_identifier_rec.geography_id);
1653 FND_MESSAGE.SET_TOKEN('PARENT_GEO_ID', l_get_all_parents.subject_id);
1654 FND_MSG_PUB.ADD;
1655 x_return_status := fnd_api.g_ret_sts_error;
1656 RAISE FND_API.G_EXC_ERROR;
1657 EXIT;
1658 END IF;
1659 END IF;
1660 END LOOP;
1661 CLOSE c_get_all_parents;
1662 ELSE
1663 -- Bug 5411429 : check for the duplicate name/code for Country geographies.
1664 SELECT count(*) INTO l_count
1665 FROM hz_geography_identifiers
1666 WHERE identifier_type = p_geo_identifier_rec.identifier_type
1667 AND identifier_subtype = l_new_geo_subtype
1668 AND language_code = p_geo_identifier_rec.language_code
1669 AND UPPER(identifier_value) = UPPER(l_new_geo_value)
1670 AND geography_type = 'COUNTRY'
1671 AND rowid <> l_rowid;
1672
1673 IF l_count > 0 THEN
1674 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_DUP_COUNTRY_IDEN');
1675 FND_MESSAGE.SET_TOKEN('IDEN_VAL', l_new_geo_value);
1676 FND_MSG_PUB.ADD;
1677 x_return_status := fnd_api.g_ret_sts_error;
1678 RAISE FND_API.G_EXC_ERROR;
1679 END IF;
1680
1681 END IF;
1682
1683 END IF;
1684 END IF;
1685
1686 --do not allow the updation of primary_flag from 'Y' to 'N'
1687 IF (l_old_primary_flag = 'Y' AND p_geo_identifier_rec.primary_flag = 'N') THEN
1688 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_COLUMN' );
1689 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'primary_flag from Y to N');
1690 FND_MSG_PUB.ADD;
1691 x_return_status := FND_API.G_RET_STS_ERROR;
1692 RAISE FND_API.G_EXC_ERROR;
1693 END IF;
1694
1695 -- for updating primary_flag from 'N' to 'Y'
1696 IF (l_old_primary_flag = 'N' AND p_geo_identifier_rec.primary_flag = 'Y') THEN
1697
1698 --check if there exists a primary row already for this geography_id
1699 SELECT count(*) INTO l_count
1700 FROM HZ_GEOGRAPHY_IDENTIFIERS
1701 WHERE geography_id = p_geo_identifier_rec.geography_id
1702 AND identifier_type = p_geo_identifier_rec.identifier_type
1703 AND primary_flag='Y'
1704 AND language_code = p_geo_identifier_rec.language_code;
1705
1706 -- --dbms_output.put_line ( 'l_count for primary row '||to_char(l_count));
1707 IF l_count > 0 THEN
1708 -- set the primary_flag of the existing primary identifier to 'N'
1709 UPDATE hz_geography_identifiers
1710 SET primary_flag = 'N'
1711 WHERE geography_id=p_geo_identifier_rec.geography_id
1712 AND identifier_type = p_geo_identifier_rec.identifier_type
1713 AND primary_flag = 'Y'
1714 AND language_code = p_geo_identifier_rec.language_code;
1715 -- --dbms_output.put_line ( 'After update of primary from Y to N');
1716 END IF;
1717 END IF;
1718
1719 hz_geography_identifiers_pkg.update_row(
1720 x_rowid => l_rowid,
1721 x_geography_id => p_geo_identifier_rec.geography_id,
1722 x_identifier_subtype => l_new_geo_subtype,
1723 x_identifier_value => l_new_geo_value,
1724 x_geo_data_provider => p_geo_identifier_rec.geo_data_provider,
1725 x_object_version_number => p_object_version_number,
1726 x_identifier_type => p_geo_identifier_rec.identifier_type,
1727 x_primary_flag => p_geo_identifier_rec.primary_flag,
1728 x_language_code => p_geo_identifier_rec.language_code,
1729 x_geography_use => NULL,
1730 x_geography_type => NULL,
1731 x_created_by_module => NULL,
1732 x_application_id => NULL,
1733 x_program_login_id => NULL);
1734
1735 -- Kick off conc prog if primary flag is Y and name or code has been updated
1736 -- It will call procedure HZ_GEOGRAPHIES_PKG.update_geo_element_cp
1737 IF ((l_geography_use = 'MASTER_REF') AND
1738 ((l_old_primary_flag = 'N' AND p_geo_identifier_rec.primary_flag = 'Y') OR
1739 ((l_old_primary_flag = 'Y' AND p_geo_identifier_rec.primary_flag = 'Y') AND
1740 (l_name_updated = 'Y'))
1741 )
1742 )
1743 THEN
1744 x_cp_request_id := fnd_request.submit_request(
1745 application => 'AR',
1746 program => 'ARHGEOEU',
1747 argument1 => p_geo_identifier_rec.geography_id,
1748 argument2 => p_geo_identifier_rec.identifier_type,
1749 argument3 => l_new_geo_value);
1750 ELSIF ((l_geography_use = 'TAX') AND
1751 ((l_old_primary_flag = 'N' AND p_geo_identifier_rec.primary_flag = 'Y') OR
1752 ((l_old_primary_flag = 'Y' AND p_geo_identifier_rec.primary_flag = 'Y') AND
1753 (l_name_updated = 'Y'))
1754 )
1755 )
1756 THEN
1757 -- (For TAX, Logic added by Nishant on 27-Oct-2005 for Bug 4578867)
1758 -- FOR geography_use = 'TAX' we dont have any hierarchy (structure),
1759 -- so coulmns geography_element1,geography_element1_name,geography_element1_code...
1760 -- are all null.and the only columns which need to be modified in hz_geographies
1761 -- are geography_name and geography_code
1762
1763 IF p_geo_identifier_rec.identifier_type = 'CODE' THEN
1764 UPDATE HZ_GEOGRAPHIES
1765 SET geography_code = l_new_geo_value,
1766 last_updated_by = hz_utility_pub.LAST_UPDATED_BY,
1767 last_update_date = hz_utility_pub.LAST_UPDATE_DATE
1768 WHERE geography_id = p_geo_identifier_rec.geography_id
1769 AND geography_use = l_geography_use;
1770 END IF;
1771
1772 IF p_geo_identifier_rec.identifier_type = 'NAME' THEN
1773 UPDATE HZ_GEOGRAPHIES
1774 SET geography_name = l_new_geo_value,
1775 last_updated_by = hz_utility_pub.LAST_UPDATED_BY,
1776 last_update_date = hz_utility_pub.LAST_UPDATE_DATE
1777 WHERE geography_id = p_geo_identifier_rec.geography_id
1778 AND geography_use = l_geography_use;
1779 END IF;
1780 END IF;
1781
1782 END do_update_geo_identifier;
1783
1784 -- delete geography identifier
1785 PROCEDURE do_delete_geo_identifier(
1786 p_geography_id IN NUMBER,
1787 p_identifier_type IN VARCHAR2,
1788 p_identifier_subtype IN VARCHAR2,
1789 p_identifier_value IN VARCHAR2,
1790 p_language_code IN VARCHAR2,
1791 x_return_status IN OUT NOCOPY VARCHAR2
1792 ) IS
1793
1794 l_primary_flag VARCHAR2(1);
1795 l_count NUMBER;
1796 l_delete_flag VARCHAR2(1);
1797
1798 BEGIN
1799
1800 l_delete_flag := 'Y';
1801
1802 -- primary identifier can not be deleted
1803 SELECT primary_flag INTO l_primary_flag
1804 FROM hz_geography_identifiers
1805 WHERE geography_id = p_geography_id
1806 AND identifier_type = p_identifier_type
1807 AND identifier_subtype = p_identifier_subtype
1808 AND identifier_value = p_identifier_value
1809 AND language_code = p_language_code;
1810
1811 IF l_primary_flag = 'Y' THEN
1812 l_delete_flag := 'N';
1813 FND_MESSAGE.SET_NAME( 'AR', 'HZ_GEO_NONDELETEABLE' );
1814 FND_MSG_PUB.ADD;
1815 x_return_status := FND_API.G_RET_STS_ERROR;
1816 RAISE FND_API.G_EXC_ERROR;
1817 END IF;
1818
1819 -- If a STANDARD_NAME is being deleted , if there exists another name mark it as STANDARD and delete
1820 -- this row else if another name doesn't exist then delete the row.
1821 IF (p_identifier_type = 'NAME' AND p_identifier_subtype = 'STANDARD_NAME') THEN
1822 select count(*) INTO l_count
1823 from hz_geography_identifiers
1824 where geography_id = p_geography_id
1825 AND language_code = p_language_code
1826 AND identifier_type = 'NAME'
1827 ;
1828 IF l_count > 1 THEN
1829 -- update an identifier to STANDARD
1830 UPDATE hz_geography_identifiers
1831 SET identifier_subtype = 'STANDARD_NAME'
1832 WHERE geography_id = p_geography_id
1833 AND identifier_type= p_identifier_type
1834 AND identifier_subtype <> p_identifier_subtype
1835 AND identifier_value <> p_identifier_value
1836 AND language_code = p_language_code
1837 AND rownum < 2;
1838 l_delete_flag := 'Y';
1839 ELSE
1840 l_delete_flag := 'Y';
1841 END IF;
1842 END IF;
1843
1844 IF l_delete_flag = 'Y' THEN
1845 HZ_GEOGRAPHY_IDENTIFIERS_PKG.delete_row(
1846 x_geography_id => p_geography_id,
1847 x_identifier_subtype => p_identifier_subtype,
1848 x_identifier_value => p_identifier_value,
1849 x_language_code => p_language_code,
1850 x_identifier_type => p_identifier_type
1851 );
1852 END IF;
1853
1854 EXCEPTION WHEN NO_DATA_FOUND THEN
1855 FND_MESSAGE.SET_NAME( 'AR', 'HZ_GEO_NO_RECORD' );
1856 FND_MESSAGE.SET_TOKEN('TOKEN1', 'geography_identifier');
1857 FND_MESSAGE.SET_TOKEN('TOKEN2', 'geography_id: '||p_geography_id||', identifier_type: '||
1858 p_identifier_type||', identifier_subtype: '||p_identifier_subtype||', identifier_value: '||
1859 p_identifier_value||', language_code: '||p_language_code);
1860 FND_MSG_PUB.ADD;
1861 RAISE FND_API.G_EXC_ERROR;
1862
1863 END do_delete_geo_identifier;
1864
1865
1866 -- create Master Geography
1867 PROCEDURE do_create_master_geography(
1868 p_master_geography_rec IN MASTER_GEOGRAPHY_REC_TYPE,
1869 x_geography_id OUT NOCOPY NUMBER,
1870 x_return_status IN OUT NOCOPY VARCHAR2
1871 ) IS
1872
1873 l_count NUMBER;
1874 l_parent_geography_tbl HZ_GEOGRAPHY_PUB.parent_geography_tbl_type;
1875 l_rowid VARCHAR2(64);
1876 l_country_code VARCHAR2(2);
1877 l_master_relation_rec MASTER_RELATION_REC_TYPE;
1878 l_geo_identifier_rec GEO_IDENTIFIER_REC_TYPE;
1879 l_child_geography_id NUMBER;
1880 x_relationship_id NUMBER;
1881 x_msg_count NUMBER;
1882 x_msg_data VARCHAR2(2000);
1883 l_last NUMBER;
1884
1885 BEGIN
1886
1887 --l_country_count := 0;
1888 l_parent_geography_tbl := p_master_geography_rec.parent_geography_id;
1889
1890
1891 -- dbms_output.put_line('In do_create_master_geography, before validate');
1892 -- validate master geography record
1893 HZ_GEOGRAPHY_VALIDATE_PVT.validate_master_geography(
1894 p_master_geography_rec => p_master_geography_rec,
1895 p_create_update_flag => 'C',
1896 x_return_status => x_return_status
1897 );
1898
1899 --dbms_output.put_line('In do_create_master_geography , after validate_master_geography '|| x_return_status);
1900
1901 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1902 RAISE FND_API.G_EXC_ERROR;
1903 END IF;
1904
1905 -- determine the country_code
1906 IF p_master_geography_rec.geography_type='COUNTRY' THEN
1907 l_country_code := p_master_geography_rec.geography_code;
1908 ELSE
1909 SELECT country_code INTO l_country_code
1910 FROM HZ_GEOGRAPHIES
1911 WHERE geography_id= l_parent_geography_tbl(1);
1912 END IF;
1913
1914 --dbms_output.put_line('before insert_row');
1915 --insert row into HZ_GEOGRAPHIES
1916 HZ_GEOGRAPHIES_PKG.insert_row(
1917 x_rowid => l_rowid,
1918 x_geography_id => x_geography_id,
1919 x_object_version_number => 1,
1920 x_geography_type => UPPER(p_master_geography_rec.geography_type),
1921 x_geography_name => p_master_geography_rec.geography_name,
1922 x_geography_use => 'MASTER_REF',
1923 x_geography_code => UPPER(p_master_geography_rec.geography_code),
1924 x_start_date => p_master_geography_rec.start_date,
1925 x_end_date => p_master_geography_rec.end_date,
1926 x_multiple_parent_flag => 'N',
1927 x_created_by_module => p_master_geography_rec.created_by_module,
1928 x_country_code => l_country_code,
1929 x_geography_element1 => NULL,
1930 x_geography_element1_id => NULL,
1931 x_geography_element1_code => NULL,
1932 x_geography_element2 => NULL,
1933 x_geography_element2_id => NULL,
1934 x_geography_element2_code => NULL,
1935 x_geography_element3 => NULL,
1936 x_geography_element3_id => NULL,
1937 x_geography_element3_code => NULL,
1938 x_geography_element4 => NULL,
1939 x_geography_element4_id => NULL,
1940 x_geography_element4_code => NULL,
1941 x_geography_element5 => NULL,
1942 x_geography_element5_id => NULL,
1943 x_geography_element5_code => NULL,
1944 x_geography_element6 => NULL,
1945 x_geography_element6_id => NULL,
1946 x_geography_element7 => NULL,
1947 x_geography_element7_id => NULL,
1948 x_geography_element8 => NULL,
1949 x_geography_element8_id => NULL,
1950 x_geography_element9 => NULL,
1951 x_geography_element9_id => NULL,
1952 x_geography_element10 => NULL,
1953 x_geography_element10_id => NULL,
1954 x_geometry => p_master_geography_rec.geometry,
1955 x_timezone_code => p_master_geography_rec.timezone_code,
1956 x_application_id => p_master_geography_rec.application_id,
1957 x_program_login_id => NULL,
1958 x_attribute_category => NULL,
1959 x_attribute1 => NULL,
1960 x_attribute2 => NULL,
1961 x_attribute3 => NULL,
1962 x_attribute4 => NULL,
1963 x_attribute5 => NULL,
1964 x_attribute6 => NULL,
1965 x_attribute7 => NULL,
1966 x_attribute8 => NULL,
1967 x_attribute9 => NULL,
1968 x_attribute10 => NULL,
1969 x_attribute11 => NULL,
1970 x_attribute12 => NULL,
1971 x_attribute13 => NULL,
1972 x_attribute14 => NULL,
1973 x_attribute15 => NULL,
1974 x_attribute16 => NULL,
1975 x_attribute17 => NULL,
1976 x_attribute18 => NULL,
1977 x_attribute19 => NULL,
1978 x_attribute20 => NULL
1979 );
1980
1981 BEGIN
1982
1983 l_last := l_parent_geography_tbl.last;
1984 IF l_last > 0 THEN
1985 FOR i in 1 .. l_last LOOP
1986
1987 BEGIN
1988
1989 IF l_parent_geography_tbl.exists(i) = TRUE THEN
1990 -- construct master relation record
1991 l_master_relation_rec.geography_id := x_geography_id;
1992 l_master_relation_rec.parent_geography_id := l_parent_geography_tbl(i);
1993 l_master_relation_rec.start_date := p_master_geography_rec.start_date;
1994 l_master_relation_rec.end_date := p_master_geography_rec.end_date;
1995 l_master_relation_rec.created_by_module := p_master_geography_rec.created_by_module;
1996 l_master_relation_rec.application_id := p_master_geography_rec.application_id;
1997
1998 -- call relationship API to create relationship between geography_id and parent_geography_id
1999 create_master_relation(
2000 p_init_msg_list => 'F',
2001 p_master_relation_rec => l_master_relation_rec,
2002 x_relationship_id => x_relationship_id,
2003 x_return_status => x_return_status,
2004 x_msg_count => x_msg_count,
2005 x_msg_data => x_msg_data
2006 );
2007 END IF;
2008 END;
2009 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2010 RAISE FND_API.G_EXC_ERROR;
2011 END IF;
2012 -- --dbms_output.put_line('after create_master_relation id is '|| to_char(x_relationship_id));
2013 END LOOP;
2014 END IF;
2015 END;
2016
2017
2018 -- create an identifier for this geography in HZ_GEOGRAPHY_IDENTIFIERS
2019 -- construct Identifier record for identifier_type 'NAME'/'CODE'
2020 l_geo_identifier_rec.geography_id := x_geography_id;
2021 l_geo_identifier_rec.identifier_subtype := 'STANDARD_NAME';
2022 l_geo_identifier_rec.identifier_value := p_master_geography_rec.geography_name;
2023 l_geo_identifier_rec.identifier_type := 'NAME';
2024 l_geo_identifier_rec.geo_data_provider := p_master_geography_rec.geo_data_provider;
2025 l_geo_identifier_rec.primary_flag := 'Y';
2026 l_geo_identifier_rec.language_code := p_master_geography_rec.language_code;
2027 l_geo_identifier_rec.created_by_module := p_master_geography_rec.created_by_module;
2028 l_geo_identifier_rec.application_id := p_master_geography_rec.application_id;
2029
2030
2031 -- call to create Identifier API
2032 create_geo_identifier(
2033 p_init_msg_list => 'F',
2034 p_geo_identifier_rec => l_geo_identifier_rec,
2035 x_return_status => x_return_status,
2036 x_msg_count => x_msg_count,
2037 x_msg_data => x_msg_data
2038 );
2039
2040 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2041 RAISE FND_API.G_EXC_ERROR;
2042 END IF;
2043 --dbms_output.put_line('after creating name identifier');
2044
2045 IF p_master_geography_rec.geography_code IS NOT NULL
2046 -- Bug 4579847 : do not call for g_miss value for code
2047 and p_master_geography_rec.geography_code <> fnd_api.g_miss_char THEN
2048
2049 -- create an identifier for this geography for identifier_type 'CODE'
2050 l_geo_identifier_rec.identifier_subtype := p_master_geography_rec.geography_code_type;
2051 l_geo_identifier_rec.identifier_value := UPPER(p_master_geography_rec.geography_code);
2052 l_geo_identifier_rec.identifier_type := 'CODE';
2053
2054 --dbms_output.put_line('after constructing the code identifier record');
2055 -- call to create Identifier API
2056 create_geo_identifier(
2057 p_init_msg_list => 'F',
2058 p_geo_identifier_rec => l_geo_identifier_rec,
2059 x_return_status => x_return_status,
2060 x_msg_count => x_msg_count,
2061 x_msg_data => x_msg_data
2062 );
2063
2064 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2065 RAISE FND_API.G_EXC_ERROR;
2066 END IF;
2067 END IF;
2068
2069 END do_create_master_geography;
2070
2071
2072 --Update master geography
2073 PROCEDURE do_update_geography(
2074 p_geography_id IN NUMBER,
2075 p_end_date IN DATE,
2076 p_geometry IN MDSYS.SDO_GEOMETRY,
2077 p_timezone_code IN VARCHAR2,
2078 p_object_version_number IN OUT NOCOPY NUMBER,
2079 x_return_status IN OUT NOCOPY VARCHAR2
2080 ) IS
2081
2082 l_rowid VARCHAR2(64);
2083 x_msg_count NUMBER;
2084 x_msg_data VARCHAR2(2000);
2085 l_status VARCHAR2(1);
2086 l_start_date DATE;
2087 l_end_date DATE;
2088 l_geography_use VARCHAR2(30);
2089 l_count NUMBER;
2090 l_relationship_rec HZ_RELATIONSHIP_V2PUB.RELATIONSHIP_REC_TYPE;
2091 CURSOR c_get_all_relationships IS
2092 SELECT distinct relationship_id,object_version_number
2093 FROM HZ_RELATIONSHIPS
2094 WHERE (subject_id = p_geography_id
2095 OR object_id = p_geography_id)
2096 AND relationship_type= l_geography_use
2097 AND l_geography_use = 'MASTER_REF' --Bug5265511
2098 AND status = 'A'; --Bug5454824
2099
2100 -- for l_geogrpahy_use = 'TAX' this cursor will read only Active records
2101 -- whose end_date is changed.
2102 -- but for l_geogrpahy_use = 'MASTER_REF' this will read inActive records also
2103
2104 l_get_all_relationships c_get_all_relationships%ROWTYPE;
2105 l_party_object_version_number NUMBER := 1;
2106 l_object_version_number NUMBER;
2107
2108 BEGIN
2109
2110 hz_utility_v2pub.validate_mandatory(
2111 p_create_update_flag => 'U',
2112 p_column => 'object_version_number',
2113 p_column_value => p_object_version_number,
2114 x_return_status => x_return_status
2115 );
2116
2117 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2118 RAISE FND_API.G_EXC_ERROR;
2119 END IF;
2120
2121 BEGIN
2122
2123 -- Initialize start_date and end_date
2124 SELECT rowid,start_date,end_date,geography_use,object_version_number INTO l_rowid,l_start_date,l_end_date,
2125 l_geography_use,l_object_version_number
2126 FROM HZ_GEOGRAPHIES
2127 WHERE geography_id=p_geography_id
2128 FOR UPDATE of geography_id NOWAIT;
2129
2130 --validate object_version_number
2131 IF l_object_version_number <> p_object_version_number THEN
2132 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
2133 FND_MESSAGE.SET_TOKEN('TABLE', 'hz_geographies');
2134 FND_MSG_PUB.ADD;
2135 RAISE FND_API.G_EXC_ERROR;
2136 ELSE
2137 p_object_version_number := l_object_version_number + 1;
2138 END IF;
2139
2140 EXCEPTION WHEN NO_DATA_FOUND THEN
2141 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_NO_RECORD');
2142 FND_MESSAGE.SET_TOKEN('TOKEN1','geography');
2143 FND_MESSAGE.SET_TOKEN('TOKEN2', 'geography_id '||p_geography_id);
2144 FND_MSG_PUB.ADD;
2145 RAISE FND_API.G_EXC_ERROR;
2146 END;
2147 --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')||'*');
2148 --dbms_output.put_line('After date validation '|| x_return_status);
2149 -- check whether end_date >= start_date
2150 HZ_UTILITY_V2PUB.validate_start_end_date(
2151 p_create_update_flag => 'U',
2152 p_start_date_column_name => 'start_date',
2153 p_start_date => l_start_date,
2154 p_old_start_date => l_start_date,
2155 p_end_date_column_name => 'end_date',
2156 p_end_date => p_end_date,
2157 p_old_end_date => l_end_date,
2158 x_return_status => x_return_status
2159 );
2160
2161 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2162 RAISE FND_API.G_EXC_ERROR;
2163 END IF;
2164 -- in case g_miss_date is passed for end_date, assign the default future date to end_date
2165 l_end_date := p_end_date;
2166
2167 IF l_end_date = fnd_api.g_miss_date THEN
2168 l_end_date := to_date('31-12-4712','DD-MM-YYYY');
2169 END IF;
2170
2171 -- dbms_output.put_line('timezone_code is '||p_timezone_code);
2172 -- validate timezone_code for FK to FND_TIMEZONES
2173 IF p_timezone_code IS NOT NULL THEN
2174
2175 SELECT count(*) INTO l_count
2176 FROM FND_TIMEZONES_B
2177 WHERE timezone_code = p_timezone_code
2178 AND rownum <2;
2179
2180 IF l_count = 0 THEN
2181 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
2182 fnd_message.set_token('FK', 'timezone_code');
2183 fnd_message.set_token('COLUMN','timezone_code');
2184 fnd_message.set_token('TABLE','FND_TIMEZONES_B');
2185 fnd_msg_pub.add;
2186 RAISE FND_API.G_EXC_ERROR;
2187 END IF;
2188 END IF;
2189 --dbms_output.put_line('After date validation '|| x_return_status);
2190
2191 --call table handler to update the geography
2192 HZ_GEOGRAPHIES_PKG.update_row(
2193 x_rowid => l_rowid,
2194 x_geography_id => p_geography_id,
2195 x_object_version_number => p_object_version_number,
2196 x_geography_type => NULL,
2197 x_geography_name => NULL,
2198 x_geography_use => NULL,
2199 x_geography_code => NULL,
2200 x_start_date => NULL,
2201 x_end_date => l_end_date,
2202 x_multiple_parent_flag => NULL,
2203 x_created_by_module => NULL,
2204 x_country_code => NULL,
2205 x_geography_element1 => NULL,
2206 x_geography_element1_id => NULL,
2207 x_geography_element1_code => NULL,
2208 x_geography_element2 => NULL,
2209 x_geography_element2_id => NULL,
2210 x_geography_element2_code => NULL,
2211 x_geography_element3 => NULL,
2212 x_geography_element3_id => NULL,
2213 x_geography_element3_code => NULL,
2214 x_geography_element4 => NULL,
2215 x_geography_element4_id => NULL,
2216 x_geography_element4_code => NULL,
2217 x_geography_element5 => NULL,
2218 x_geography_element5_id => NULL,
2219 x_geography_element5_code => NULL,
2220 x_geography_element6 => NULL,
2221 x_geography_element6_id => NULL,
2222 x_geography_element7 => NULL,
2223 x_geography_element7_id => NULL,
2224 x_geography_element8 => NULL,
2225 x_geography_element8_id => NULL,
2226 x_geography_element9 => NULL,
2227 x_geography_element9_id => NULL,
2228 x_geography_element10 => NULL,
2229 x_geography_element10_id => NULL,
2230 x_geometry => p_geometry,
2231 x_timezone_code => p_timezone_code,
2232 x_application_id => NULL,
2233 x_program_login_id => NULL,
2234 x_attribute_category => NULL,
2235 x_attribute1 => NULL,
2236 x_attribute2 => NULL,
2237 x_attribute3 => NULL,
2238 x_attribute4 => NULL,
2239 x_attribute5 => NULL,
2240 x_attribute6 => NULL,
2241 x_attribute7 => NULL,
2242 x_attribute8 => NULL,
2243 x_attribute9 => NULL,
2244 x_attribute10 => NULL,
2245 x_attribute11 => NULL,
2246 x_attribute12 => NULL,
2247 x_attribute13 => NULL,
2248 x_attribute14 => NULL,
2249 x_attribute15 => NULL,
2250 x_attribute16 => NULL,
2251 x_attribute17 => NULL,
2252 x_attribute18 => NULL,
2253 x_attribute19 => NULL,
2254 x_attribute20 => NULL
2255 );
2256
2257
2258 IF l_end_date <= sysdate THEN
2259 l_status := 'I';
2260 ELSIF l_end_date > sysdate THEN
2261 l_status := 'A';
2262 ELSIF l_end_date = NULL THEN
2263 l_status :=NULL;
2264 END IF;
2265
2266 -- construct the relationship record for update
2267 --l_relationship_rec.relationship_id := p_relationship_id;
2268 l_relationship_rec.start_date := NULL;
2269 l_relationship_rec.end_date := l_end_date;
2270 l_relationship_rec.status := l_status;
2271 l_relationship_rec.created_by_module := NULL;
2272 l_relationship_rec.application_id := NULL;
2273
2274 -- update relationships with end_date wherever this geography_id is used
2275
2276 OPEN c_get_all_relationships;
2277 LOOP
2278 FETCH c_get_all_relationships INTO l_get_all_relationships;
2279 EXIT WHEN c_get_all_relationships%NOTFOUND;
2280
2281 l_relationship_rec.relationship_id := l_get_all_relationships.relationship_id;
2282
2283 --l_relationship_rec.relationship_code := l_get_all_relationships.relationship_code;
2284 HZ_RELATIONSHIP_V2PUB.update_relationship(
2285 p_init_msg_list => 'F',
2286 p_relationship_rec => l_relationship_rec,
2287 p_object_version_number => l_get_all_relationships.object_version_number,
2288 p_party_object_version_number => l_party_object_version_number,
2289 x_return_status => x_return_status,
2290 x_msg_count => x_msg_count,
2291 x_msg_data => x_msg_data
2292 );
2293
2294 /* update hz_relationships
2295 set status = l_status,
2296 end_date = p_end_date
2297 WHERE relationship_id=l_get_all_relationships.relationship_id;*/
2298
2299 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2300 RAISE FND_API.G_EXC_ERROR;
2301 EXIT;
2302 END IF;
2303
2304
2305 END LOOP;
2306 CLOSE c_get_all_relationships;
2307
2308 /* update hz_hierarchy_nodes
2309 set effective_end_date = p_end_date
2310 where hierarchy_type=l_geography_use
2311 and parent_id = p_geography_id
2312 or child_id = p_geography_id ; */
2313
2314
2315 END do_update_geography;
2316
2317 /*-----------------------------------------------------------------------------+
2318 Procedure do_create_discrete_geography: Creates discrete geographies for geographies
2319 created through geography range. These discrete geographies are created only
2320 if postal_code_range_flag = Y in HZ_GEOGRAPHY_TYPE_B (for 'MASTER_REF'). This
2321 way postal code range will be converted to discrete geography.
2322
2323 Created By Nishant Singhai 29-Aug-2005
2324 ER# 4539557 : CREATION OF DISCRETE VALUES FROM ZIP CODE RANGES
2325 Modified By Nishant Singhai 22-Sep-2005
2326 Bug# 4591075 : Commented out code as for now we will not be creating
2327 discrete geography for postal code range. Retaining logic
2328 for future use
2329 ------------------------------------------------------------------------------*/
2330
2331 PROCEDURE do_create_discrete_geography (
2332 p_geography_range_rec IN HZ_GEOGRAPHY_PUB.GEOGRAPHY_RANGE_REC_TYPE,
2333 x_return_status IN OUT NOCOPY VARCHAR2
2334 ) IS
2335 /*
2336 l_geography_range_rec HZ_GEOGRAPHY_PUB.GEOGRAPHY_RANGE_REC_TYPE;
2337
2338 l_parent_geo_type VARCHAR2(100);
2339 l_country_code VARCHAR2(100);
2340 l_child_geo_type VARCHAR2(100);
2341 l_geo_range_from NUMBER;
2342 l_geo_range_to NUMBER;
2343 l_geography_id NUMBER;
2344 l_master_geography_rec HZ_GEOGRAPHY_PUB.MASTER_GEOGRAPHY_REC_TYPE;
2345 l_parent_geography_id_tbl HZ_GEOGRAPHY_PUB.PARENT_GEOGRAPHY_TBL_TYPE;
2346 l_msg_data VARCHAR2(10000);
2347 l_msg_count NUMBER;
2348 l_count NUMBER;
2349 */
2350 BEGIN
2351 x_return_status := FND_API.G_RET_STS_SUCCESS;
2352 /*
2353 l_geography_range_rec := p_geography_range_rec;
2354 l_geo_range_from := TO_NUMBER(l_geography_range_rec.geography_from);
2355 l_geo_range_to := TO_NUMBER(l_geography_range_rec.geography_to);
2356
2357 IF ((l_geo_range_from IS NOT NULL) AND (l_geo_range_to IS NOT NULL) AND
2358 (l_geo_range_from <= l_geo_range_to)) THEN
2359 -- get geography type, country code for parent id
2360 BEGIN
2361 SELECT geography_type, country_code
2362 INTO l_parent_geo_type, l_country_code
2363 FROM hz_geographies
2364 WHERE geography_id = l_geography_range_rec.master_ref_geography_id
2365 AND geography_use = 'MASTER_REF'
2366 AND TRUNC(SYSDATE) BETWEEN START_DATE AND end_date
2367 ;
2368 EXCEPTION WHEN NO_DATA_FOUND THEN
2369 NULL;
2370 END;
2371
2372 IF (l_parent_geo_type IS NOT NULL) THEN
2373 -- get child geo type for which geo range has to be created
2374 BEGIN
2375 SELECT st.geography_type
2376 INTO l_child_geo_type
2377 FROM hz_geo_structure_levels st
2378 ,hz_geography_types_b tp
2379 WHERE st.country_code = l_country_code
2380 AND st.parent_geography_type = l_parent_geo_type
2381 AND st.geography_type = tp.geography_type
2382 AND tp.geography_use = 'MASTER_REF'
2383 AND tp.postal_code_range_flag = 'Y'
2384 AND tp.geography_use = 'MASTER_REF'
2385 AND ROWNUM < 2
2386 ;
2387 EXCEPTION WHEN OTHERS THEN
2388 NULL;
2389 END;
2390
2391 -- now create discrete geo
2392 IF (l_child_geo_type IS NOT NULL) THEN
2393
2394 l_master_geography_rec.geography_type := l_child_geo_type;
2395 l_master_geography_rec.START_DATE := l_geography_range_rec.start_date;
2396 l_master_geography_rec.END_DATE := l_geography_range_rec.end_date;
2397 l_parent_geography_id_tbl(1) := l_geography_range_rec.master_ref_geography_id;
2398 l_master_geography_rec.parent_geography_id := l_parent_geography_id_tbl;
2399 l_master_geography_rec.created_by_module := l_geography_range_rec.created_by_module;
2400 l_master_geography_rec.application_id := l_geography_range_rec.application_id;
2401
2402 FOR i IN l_geo_range_from..l_geo_range_to LOOP
2403 l_master_geography_rec.geography_name := TO_CHAR(i);
2404
2405 -- check if this geography already exists for given parent
2406 SELECT COUNT(*)
2407 INTO l_count
2408 FROM hz_geography_identifiers id
2409 WHERE UPPER(id.identifier_value) = l_master_geography_rec.geography_name
2410 AND id.geography_use = 'MASTER_REF'
2411 AND id.identifier_type = 'NAME'
2412 AND id.identifier_subtype = 'STANDARD_NAME'
2413 AND EXISTS ( SELECT '1'
2414 FROM hz_relationships rel
2415 WHERE rel.subject_id = l_geography_range_rec.master_ref_geography_id
2416 AND rel.object_id = id.geography_id
2417 AND rel.object_type = l_master_geography_rec.geography_type
2418 AND rel.status = 'A'
2419 AND rel.relationship_type = 'MASTER_REF');
2420
2421 IF (l_count = 0) THEN
2422 -- create geography
2423 HZ_GEOGRAPHY_PUB.create_master_geography(
2424 p_init_msg_list => FND_API.G_FALSE,
2425 p_master_geography_rec => l_master_geography_rec,
2426 x_geography_id => l_geography_id,
2427 x_return_status => x_return_status,
2428 x_msg_count => l_msg_count,
2429 x_msg_data => l_msg_data);
2430
2431 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2432 -- In case api throuws any exception, raise execution error
2433 -- which will be cought by calling api.
2434 RAISE FND_API.G_EXC_ERROR;
2435 END IF;
2436 END IF;
2437 --dbms_output.put_line('Create Master Geo For :'||l_master_geography_rec.geography_name||':GeoId:'||l_geography_id);
2438 END LOOP;
2439
2440 ELSE
2441 --dbms_output.put_line('Child Geo type was NULL... ');
2442 NULL;
2443 END IF; -- End of child geo type not null check
2444
2445 ELSE
2446 --dbms_output.put_line('Parent Geo Type was NULL... ');
2447 NULL;
2448 END IF; -- End of parent_geo_type not null check
2449
2450 ELSE
2451 --dbms_output.put_line('From and to ids are wrong... ');
2452 NULL;
2453 END IF;
2454
2455 EXCEPTION
2456 WHEN VALUE_ERROR THEN
2457 -- when alphabet is passed in number field, we will get this error.
2458 -- it would have been taken care of in create range api itself.
2459 -- If not, we do not want to raise error, just don't create discrete
2460 -- geographies.
2461 -- dbms_output.put_line(SUBSTR('Number conversion error...'||SQLERRM,1,255));
2462 NULL;
2463 */
2464 END do_create_discrete_geography;
2465
2466
2467 -- create geography range
2468
2469 PROCEDURE do_create_geography_range(
2470 p_geography_range_rec IN GEOGRAPHY_RANGE_REC_TYPE,
2471 x_return_status IN OUT NOCOPY VARCHAR2
2472 ) IS
2473
2474 l_zone_type VARCHAR2(30);
2475 l_geography_use VARCHAR2(30);
2476 l_rowid ROWID;
2477 l_count NUMBER;
2478
2479
2480 BEGIN
2481
2482
2483
2484 -- check for the uniqueness
2485 /*SELECT count(*) INTO l_count from hz_geography_ranges
2486 WHERE geography_id = p_geography_range_rec.zone_id
2487 AND geography_from = p_geography_range_rec.geography_from
2488 AND to_char(start_date,'DD-MON-YYYY') = to_char(p_geography_range_rec.start_date,'DD_MON-YYYY');
2489
2490 IF l_count > 0 THEN
2491 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
2492 FND_MESSAGE.SET_TOKEN( 'COLUMN','geography_id, geography_from, start_date');
2493 FND_MSG_PUB.ADD;
2494 RAISE FND_API.G_EXC_ERROR;
2495 END IF; */
2496
2497
2498 -- validate geography range
2499 HZ_GEOGRAPHY_VALIDATE_PVT.validate_geography_range(
2500 p_geography_range_rec => p_geography_range_rec,
2501 p_create_update_flag => 'C',
2502 x_return_status => x_return_status
2503 );
2504
2505 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2506 RAISE FND_API.G_EXC_ERROR;
2507 END IF;
2508
2509 -- get the geography_type of the zone_id
2510 l_zone_type := hz_geography_validate_pvt.get_geography_type(p_geography_id => p_geography_range_rec.zone_id,
2511 x_return_status => x_return_status);
2512
2513 -- get geography use
2514 SELECT geography_use INTO l_geography_use
2515 FROM hz_geography_types_b
2516 WHERE geography_type=l_zone_type;
2517
2518 hz_geography_ranges_pkg.insert_row (
2519 x_rowid => l_rowid,
2520 x_geography_id => p_geography_range_rec.zone_id,
2521 x_geography_from => p_geography_range_rec.geography_from,
2522 x_start_date => p_geography_range_rec.start_date,
2523 x_object_version_number => 1,
2524 x_geography_to => p_geography_range_rec.geography_to,
2525 x_identifier_type => p_geography_range_rec.identifier_type,
2526 x_end_date => p_geography_range_rec.end_date,
2527 -- Dhaval : Use queried geography_type
2528 x_geography_type => l_zone_type,
2529 x_geography_use => l_geography_use,
2530 x_master_ref_geography_id => p_geography_range_rec.master_ref_geography_id,
2531 x_created_by_module => p_geography_range_rec.created_by_module,
2532 x_application_id => p_geography_range_rec.application_id,
2533 x_program_login_id => NULL
2534 );
2535
2536 -- ER # 4539557 : Added call to create discrete geo for passed in geography
2537 -- range (Nishant Singhai on 30-Aug-2005)
2538 -- Bug 4591075 : Removing this call for now, as it is decided that we will
2539 -- not create discrete geographies from postal_code range (Nishant 22-Sep-2005)
2540 /*
2541 do_create_discrete_geography (p_geography_range_rec => p_geography_range_rec,
2542 x_return_status => x_return_status);
2543 */
2544
2545 END do_create_geography_range;
2546
2547 -- update geography range
2548 PROCEDURE do_update_geography_range(
2549 p_geography_id IN NUMBER,
2550 p_geography_from IN VARCHAR2,
2551 p_start_date IN DATE,
2552 p_end_date IN DATE,
2553 p_object_version_number IN OUT NOCOPY NUMBER,
2554 x_return_status IN OUT NOCOPY VARCHAR2
2555 ) IS
2556
2557 l_rowid ROWID;
2558 l_start_date DATE;
2559 l_end_date DATE;
2560 l_geography_range_rec GEOGRAPHY_RANGE_REC_TYPE;
2561 l_object_version_number NUMBER;
2562
2563 BEGIN
2564
2565 l_geography_range_rec.zone_id := p_geography_id;
2566 l_geography_range_rec.geography_from := p_geography_from;
2567 l_geography_range_rec.start_date := p_start_date;
2568
2569 -- validate geography range for update
2570 HZ_GEOGRAPHY_VALIDATE_PVT.validate_geography_range(
2571 p_geography_range_rec => l_geography_range_rec,
2572 p_create_update_flag => 'U',
2573 x_return_status => x_return_status
2574 );
2575
2576 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2577 RAISE FND_API.G_EXC_ERROR;
2578 END IF;
2579
2580 hz_utility_v2pub.validate_mandatory(
2581 p_create_update_flag => 'U',
2582 p_column => 'object_version_number',
2583 p_column_value => p_object_version_number,
2584 x_return_status => x_return_status
2585 );
2586
2587 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2588 RAISE FND_API.G_EXC_ERROR;
2589 END IF;
2590
2591 -- check if the row exists
2592 BEGIN
2593
2594 SELECT rowid,start_date,end_date,object_version_number INTO l_rowid,l_start_date,l_end_date,l_object_version_number
2595 FROM hz_geography_ranges
2596 WHERE geography_id = p_geography_id
2597 AND geography_from = p_geography_from
2598 AND start_date = p_start_date
2599 FOR UPDATE OF geography_id,geography_from,start_date NOWAIT;
2600
2601 --validate object_version_number
2602 IF l_object_version_number <> p_object_version_number THEN
2603 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
2604 FND_MESSAGE.SET_TOKEN('TABLE', 'hz_geography_ranges');
2605 FND_MSG_PUB.ADD;
2606 RAISE FND_API.G_EXC_ERROR;
2607 ELSE
2608 p_object_version_number := l_object_version_number + 1;
2609 END IF;
2610
2611 EXCEPTION WHEN NO_DATA_FOUND THEN
2612 FND_MESSAGE.SET_NAME( 'AR', 'HZ_GEO_NO_RECORD' );
2613 FND_MESSAGE.SET_TOKEN( 'TOKEN1', 'Geography Range');
2614 FND_MESSAGE.SET_TOKEN( 'TOKEN2', 'geography_id '||p_geography_id||', geography_from '||p_geography_from||', start_date '||p_start_date);
2615 FND_MSG_PUB.ADD;
2616 RAISE FND_API.G_EXC_ERROR;
2617
2618 END;
2619 -- check if start_date <= end_date
2620
2621 HZ_UTILITY_V2PUB.validate_start_end_date(
2622 p_create_update_flag => 'U',
2623 p_start_date_column_name => 'start_date',
2624 p_start_date => p_start_date,
2625 p_old_start_date => l_start_date,
2626 p_end_date_column_name => 'end_date',
2627 p_end_date => p_end_date,
2628 p_old_end_date => l_end_date,
2629 x_return_status => x_return_status
2630 );
2631
2632 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2633 RAISE FND_API.G_EXC_ERROR;
2634 END IF;
2635
2636 -- call table handler to update the row
2637 HZ_GEOGRAPHY_RANGES_PKG.update_row(
2638 x_rowid => l_rowid,
2639 x_geography_id => p_geography_id,
2640 x_geography_from => p_geography_from,
2641 x_start_date => p_start_date,
2642 x_object_version_number => p_object_version_number,
2643 x_geography_to => NULL,
2644 x_identifier_type => NULL,
2645 x_end_date => p_end_date,
2646 x_geography_type => NULL,
2647 x_geography_use => NULL,
2648 x_master_ref_geography_id => NULL,
2649 x_created_by_module => NULL,
2650 x_application_id => NULL,
2651 x_program_login_id => NULL
2652 );
2653
2654 END do_update_geography_range;
2655
2656 -- create zone relation
2657
2658 PROCEDURE do_create_zone_relation(
2659 p_geography_id IN NUMBER,
2660 p_zone_relation_tbl IN ZONE_RELATION_TBL_TYPE,
2661 p_created_by_module IN VARCHAR2,
2662 p_application_id IN NUMBER,
2663 x_return_status IN OUT NOCOPY VARCHAR2
2664 ) IS
2665
2666 l_count NUMBER;
2667 --l_parent_geography_type VARCHAR2(30);
2668 l_zone_type VARCHAR2(30);
2669 l_geography_use VARCHAR2(30);
2670 l_zone_relation_rec HZ_GEOGRAPHY_VALIDATE_PVT.zone_relation_rec_type;
2671 l_relationship_rec HZ_RELATIONSHIP_V2PUB.relationship_rec_type;
2672 l_geography_range_rec GEOGRAPHY_RANGE_REC_TYPE;
2673 l_incl_geo_type VARCHAR2(30);
2674 x_relationship_id NUMBER;
2675 x_party_id NUMBER;
2676 x_party_number NUMBER;
2677 p_create_org_contact VARCHAR2(1);
2678 x_msg_count NUMBER;
2679 x_msg_data VARCHAR2(2000);
2680 l_limited_by_geography_id NUMBER;
2681
2682 -- Added ro ER 4232852
2683 l_geo_rel_type_rec HZ_GEOGRAPHY_STRUCTURE_PUB.GEO_REL_TYPE_REC_TYPE;
2684 x_relationship_type_id NUMBER;
2685
2686 BEGIN
2687
2688
2689 l_zone_type := HZ_GEOGRAPHY_VALIDATE_PVT.get_geography_type(p_geography_id =>p_geography_id,
2690 x_return_status => x_return_status);
2691
2692
2693 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2694 RAISE FND_API.G_EXC_ERROR;
2695 END IF;
2696
2697 SELECT geography_use,limited_by_geography_id INTO l_geography_use,l_limited_by_geography_id
2698 FROM hz_geography_types_b
2699 WHERE geography_type = l_zone_type;
2700
2701 l_zone_relation_rec.geography_id := p_geography_id;
2702
2703 FOR i in 1 .. p_zone_relation_tbl.count LOOP
2704
2705 -- validate zone relation record
2706 l_zone_relation_rec.included_geography_id := p_zone_relation_tbl(i).included_geography_id;
2707 l_zone_relation_rec.start_date := p_zone_relation_tbl(i).start_date;
2708 l_zone_relation_rec.end_date := p_zone_relation_tbl(i).end_date;
2709
2710 hz_geography_validate_pvt.validate_zone_relation(
2711 p_zone_relation_rec => l_zone_relation_rec,
2712 p_create_update_flag => 'C',
2713 x_return_status => x_return_status
2714 );
2715
2716 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2717 RAISE FND_API.G_EXC_ERROR;
2718 EXIT;
2719 END IF;
2720
2721 --dbms_output.put_line('In loop after validate zone relaion '||x_return_status);
2722
2723 --get geography_type of included_geography_id
2724 l_incl_geo_type := HZ_GEOGRAPHY_VALIDATE_PVT.get_geography_type(p_geography_id =>p_zone_relation_tbl(i).included_geography_id,
2725 x_return_status => x_return_status);
2726
2727 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2728 RAISE FND_API.G_EXC_ERROR;
2729 EXIT;
2730 END IF;
2731
2732 IF l_limited_by_geography_id IS NOT NULL THEN
2733 --included geography_id should have a relationship with limited_by_geography_id either at level >= 0
2734 BEGIN
2735 SELECT 1 INTO l_count
2736 FROM hz_hierarchy_nodes
2737 WHERE parent_id = l_limited_by_geography_id
2738 AND child_id = p_zone_relation_tbl(i).included_geography_id
2739 AND hierarchy_type = 'MASTER_REF'
2740 AND NVL(status,'A') = 'A'
2741 AND (effective_end_date IS NULL
2742 OR effective_end_date > sysdate
2743 )
2744 AND rownum < 2;
2745
2746 EXCEPTION WHEN NO_DATA_FOUND THEN
2747 FND_MESSAGE.SET_NAME( 'AR', 'HZ_GEO_NO_RELATIONSHIP');
2748 FND_MESSAGE.SET_TOKEN( 'INCL_GEO_ID',p_zone_relation_tbl(i).included_geography_id);
2749 FND_MESSAGE.SET_TOKEN( 'LIM_GEO_ID', l_limited_by_geography_id );
2750 FND_MESSAGE.SET_TOKEN( 'ZONE_TYPE', l_zone_type);
2751 FND_MSG_PUB.ADD;
2752 RAISE FND_API.G_EXC_ERROR;
2753 END;
2754 END IF;
2755
2756 -- Fix for ER 4232852
2757 IF l_limited_by_geography_id IS NULL THEN
2758 -- check if there exists a relationship_type for this geography_type and
2759 -- included_geography_type
2760 SELECT count(*) INTO l_COUNT
2761 FROM HZ_RELATIONSHIP_TYPES
2762 WHERE subject_type = l_zone_type
2763 AND object_type= l_incl_geo_type
2764 AND forward_rel_code = 'PARENT_OF'
2765 AND backward_rel_code = 'CHILD_OF'
2766 AND relationship_type = 'TAX';
2767
2768 IF l_count = 0 THEN
2769 -- create a relationship type with this geography type and included geography type
2770 l_geo_rel_type_rec.geography_type := l_incl_geo_type;
2771 l_geo_rel_type_rec.parent_geography_type := l_zone_type;
2772 l_geo_rel_type_rec.status := 'A';
2773 l_geo_rel_type_rec.created_by_module := p_created_by_module;
2774 l_geo_rel_type_rec.application_id := p_application_id;
2775
2776 HZ_GEOGRAPHY_STRUCTURE_PUB.create_geo_rel_type(
2777 p_init_msg_list => 'F',
2778 p_geo_rel_type_rec => l_geo_rel_type_rec,
2779 x_relationship_type_id => x_relationship_type_id,
2780 x_return_status => x_return_status,
2781 x_msg_count => x_msg_count,
2782 x_msg_data => x_msg_data
2783 );
2784
2785 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2786 RAISE FND_API.G_EXC_ERROR;
2787 END IF;
2788 END IF;
2789 END IF;
2790
2791
2792 -- within a zone_type,zones can not have overlapping geographies
2793 -- Changed check from "subject_type = l_zone_type" to "subject_id = p_geography_id"
2794 -- on 29-Aug-2005 by BAIANAND for Bug 3955631. This will relax the
2795 -- creation of geographies withing zone type. Now it can create multiple geographies
2796 -- within same zone type but not same zone.
2797 SELECT count(*) INTO l_count
2798 FROM hz_relationships
2799 WHERE relationship_type = l_geography_use
2800 -- AND subject_type = l_zone_type
2801 AND subject_id = p_geography_id
2802 AND object_id = p_zone_relation_tbl(i).included_geography_id
2803 AND sysdate between start_date and nvl(end_date, sysdate + 1)
2804 AND status = 'A'
2805 AND rownum < 2;
2806
2807 IF l_count > 0 THEN
2808 FND_MESSAGE.SET_NAME( 'AR', 'HZ_GEO_OVERLAPPING_GEOGS' );
2809 FND_MESSAGE.SET_TOKEN( 'GEO_ID',p_zone_relation_tbl(i).included_geography_id );
2810 FND_MESSAGE.SET_TOKEN( 'ZONE_TYPE', l_zone_type);
2811 FND_MSG_PUB.ADD;
2812 RAISE FND_API.G_EXC_ERROR;
2813 EXIT;
2814 END IF;
2815
2816 -- included_geography_id must be unique within a geography_id
2817
2818 SELECT count(*) INTO l_count
2819 FROM hz_relationships
2820 WHERE relationship_type=l_geography_use
2821 AND subject_type=l_zone_type
2822 AND subject_id = p_geography_id
2823 AND object_id = p_zone_relation_tbl(i).included_geography_id
2824 AND sysdate between start_date and nvl(end_date, sysdate + 1)
2825 AND status = 'A'
2826 AND rownum <2;
2827
2828 IF l_count > 0 THEN
2829 FND_MESSAGE.SET_NAME( 'AR', 'HZ_GEO_DUPL_INCL_GEO_ID' );
2830 FND_MESSAGE.SET_TOKEN( 'GEO_ID',p_zone_relation_tbl(i).included_geography_id );
2831 FND_MESSAGE.SET_TOKEN( 'ZONE_ID', p_geography_id);
2832 FND_MSG_PUB.ADD;
2833 RAISE FND_API.G_EXC_ERROR;
2834 EXIT;
2835 END IF;
2836
2837 -- call relationship API to create a relationship between geography_id and included_geography_id
2838 l_relationship_rec.subject_id := p_geography_id;
2839 l_relationship_rec.subject_type := l_zone_type;
2840 l_relationship_rec.subject_table_name :='HZ_GEOGRAPHIES';
2841 l_relationship_rec.object_id := p_zone_relation_tbl(i).included_geography_id;
2842 l_relationship_rec.object_type :=l_incl_geo_type;
2843 l_relationship_rec.object_table_name := 'HZ_GEOGRAPHIES';
2844 l_relationship_rec.relationship_code := 'PARENT_OF';
2845 l_relationship_rec.relationship_type := l_geography_use;
2846 l_relationship_rec.start_date := p_zone_relation_tbl(i).start_date;
2847 l_relationship_rec.end_date := p_zone_relation_tbl(i).end_date;
2848 l_relationship_rec.status := 'A';
2849 l_relationship_rec.created_by_module := p_created_by_module;
2850 l_relationship_rec.application_id := p_application_id;
2851
2852 -- call to relationship API to create a relationship
2853 HZ_RELATIONSHIP_V2PUB.create_relationship(
2854 p_init_msg_list => 'F',
2855 p_relationship_rec => l_relationship_rec,
2856 x_relationship_id => x_relationship_id,
2857 x_party_id => x_party_id,
2858 x_party_number => x_party_number,
2859 x_return_status => x_return_status,
2860 x_msg_count => x_msg_count,
2861 x_msg_data => x_msg_data,
2862 p_create_org_contact => 'N'
2863 );
2864
2865 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2866 --dbms_output.put_line('After call to create relationship API '|| x_return_status);
2867 RAISE FND_API.G_EXC_ERROR;
2868 EXIT;
2869 END IF;
2870 IF (p_zone_relation_tbl(i).geography_from IS NOT NULL AND
2871 p_zone_relation_tbl(i).geography_to IS NOT NULL AND
2872 p_zone_relation_tbl(i).geography_from <> fnd_api.g_miss_char AND
2873 p_zone_relation_tbl(i).geography_to <> fnd_api.g_miss_char) THEN
2874 --call create_geography_range API
2875
2876 l_geography_range_rec.zone_id := p_geography_id;
2877 l_geography_range_rec.master_ref_geography_id := p_zone_relation_tbl(i).included_geography_id;
2878 l_geography_range_rec.identifier_type := p_zone_relation_tbl(i).identifier_type;
2879 l_geography_range_rec.geography_from := p_zone_relation_tbl(i).geography_from;
2880 l_geography_range_rec.geography_to := p_zone_relation_tbl(i).geography_to;
2881 l_geography_range_rec.geography_type := p_zone_relation_tbl(i).geography_type;
2882 l_geography_range_rec.start_date := p_zone_relation_tbl(i).start_date;
2883 l_geography_range_rec.end_date := p_zone_relation_tbl(i).end_date;
2884 l_geography_range_rec.created_by_module := p_created_by_module;
2885 l_geography_range_rec.application_id := p_application_id;
2886
2887
2888 --dbms_output.put_line('before call to create geography range');
2889
2890 create_geography_range(
2891 p_init_msg_list => 'F',
2892 p_geography_range_rec => l_geography_range_rec,
2893 x_return_status => x_return_status,
2894 x_msg_count => x_msg_count,
2895 x_msg_data => x_msg_data
2896 );
2897
2898 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2899 RAISE FND_API.G_EXC_ERROR;
2900 EXIT;
2901 END IF;
2902 END IF;
2903 END LOOP;
2904
2905 END do_create_zone_relation;
2906
2907 -- create zone
2908
2909 PROCEDURE do_create_zone(
2910 p_zone_type IN VARCHAR2,
2911 p_zone_name IN VARCHAR2,
2912 p_zone_code IN VARCHAR2,
2913 p_zone_code_type IN VARCHAR2,
2914 p_start_date IN DATE,
2915 p_end_date IN DATE,
2916 p_geo_data_provider IN VARCHAR2,
2917 p_language_code IN VARCHAR2,
2918 p_zone_relation_tbl IN ZONE_RELATION_TBL_TYPE,
2919 p_geometry IN MDSYS.SDO_GEOMETRY,
2920 p_timezone_code IN VARCHAR2,
2921 x_geography_id OUT NOCOPY NUMBER,
2922 p_created_by_module IN VARCHAR2,
2923 p_application_id IN NUMBER,
2924 x_return_status OUT NOCOPY VARCHAR2
2925 ) IS
2926
2927 l_count NUMBER;
2928 l_geo_identifier_rec GEO_IDENTIFIER_REC_TYPE;
2929 l_geography_use VARCHAR2(30);
2930 l_rowid ROWID;
2931 l_country_code VARCHAR2(2);
2932 x_relationship_id NUMBER;
2933 x_msg_count NUMBER;
2934 x_msg_data VARCHAR2(2000);
2935 l_language_code VARCHAR2(4);
2936 l_end_date DATE;
2937 l_limited_by_geography_id NUMBER;
2938
2939 BEGIN
2940
2941 l_end_date := to_date('31-12-4712','DD-MM-YYYY');
2942 -- validate for mandatory columns
2943 HZ_UTILITY_V2PUB.validate_mandatory (
2944 p_create_update_flag =>'C',
2945 p_column => 'zone_type',
2946 p_column_value => p_zone_type,
2947 p_restricted => 'N',
2948 x_return_status => x_return_status
2949 );
2950
2951 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2952 RAISE FND_API.G_EXC_ERROR;
2953 END IF;
2954
2955 HZ_UTILITY_V2PUB.validate_mandatory (
2956 p_create_update_flag =>'C',
2957 p_column => 'zone_name',
2958 p_column_value => p_zone_name,
2959 p_restricted => 'N',
2960 x_return_status => x_return_status
2961 );
2962
2963 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2964 RAISE FND_API.G_EXC_ERROR;
2965 END IF;
2966
2967 -- get geography_use
2968 BEGIN
2969
2970 SELECT geography_use,limited_by_geography_id INTO l_geography_use,l_limited_by_geography_id
2971 FROM hz_geography_types_b
2972 WHERE geography_type = p_zone_type;
2973
2974 EXCEPTION WHEN NO_DATA_FOUND THEN
2975 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
2976 fnd_message.set_token('FK', 'geography_type');
2977 fnd_message.set_token('COLUMN','zone_type');
2978 fnd_message.set_token('TABLE','HZ_GEOGRAPHY_TYPES_B');
2979 fnd_msg_pub.add;
2980 RAISE FND_API.G_EXC_ERROR;
2981 END;
2982
2983 -- zone_name must be unique with in a zone_type
2984 SELECT count(*) INTO l_count
2985 FROM hz_geographies
2986 WHERE geography_name = p_zone_name
2987 AND geography_type = p_zone_type
2988 AND rownum <2;
2989
2990 IF l_count > 0 THEN
2991 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
2992 FND_MESSAGE.SET_TOKEN( 'COLUMN','zone name');
2993 FND_MSG_PUB.ADD;
2994 RAISE FND_API.G_EXC_ERROR;
2995 END IF;
2996
2997 -- zone_code must be unique within a zone_type
2998
2999 IF p_zone_code IS NOT NULL THEN
3000 SELECT count(*) INTO l_count
3001 FROM hz_geographies
3002 WHERE geography_code = upper(p_zone_code)
3003 AND geography_type = p_zone_type
3004 AND rownum <2;
3005
3006 IF l_count > 0 THEN
3007 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
3008 FND_MESSAGE.SET_TOKEN( 'COLUMN','zone code');
3009 FND_MSG_PUB.ADD;
3010 RAISE FND_API.G_EXC_ERROR;
3011 END IF;
3012 END IF;
3013
3014 -- zone_code_type is mandatory if zone_code is NOT NULL
3015 IF (p_zone_code IS NOT NULL AND p_zone_code_type IS NULL) THEN
3016 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
3017 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'zone_code_type' );
3018 FND_MSG_PUB.ADD;
3019 RAISE FND_API.G_EXC_ERROR;
3020 END IF;
3021
3022
3023 -- timezone_code must be FK to fnd_timezones_b
3024 IF p_timezone_code IS NOT NULL THEN
3025 SELECT count(*) INTO l_count
3026 FROM fnd_timezones_b
3027 WHERE timezone_code = p_timezone_code
3028 AND rownum <2;
3029
3030 IF l_count = 0 THEN
3031 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
3032 fnd_message.set_token('FK', 'timezone_code');
3033 fnd_message.set_token('COLUMN','timezone_code');
3034 fnd_message.set_token('TABLE','FND_TIMEZONES_B');
3035 fnd_msg_pub.add;
3036 RAISE FND_API.G_EXC_ERROR;
3037 END IF;
3038 END IF;
3039
3040 -- there must be atleast one included geography for a zone
3041 IF p_zone_relation_tbl.count = 0 THEN
3042 fnd_message.set_name('AR', 'HZ_GEO_NO_INCL_GEOGRAPHIES');
3043 fnd_msg_pub.add;
3044 RAISE FND_API.G_EXC_ERROR;
3045 END IF;
3046
3047 -- get country code of one of the included_geography_id of this zone_type
3048
3049
3050 FOR i in 1 .. p_zone_relation_tbl.count LOOP
3051 IF p_zone_relation_tbl(i).included_geography_id IS NOT NULL THEN
3052 BEGIN
3053
3054 SELECT country_code INTO l_country_code
3055 FROM hz_geographies
3056 WHERE geography_id = p_zone_relation_tbl(i).included_geography_id;
3057 IF l_country_code IS NOT NULL THEN
3058 EXIT;
3059 END IF;
3060 EXCEPTION WHEN NO_DATA_FOUND THEN
3061 FND_MESSAGE.SET_NAME( 'AR', 'HZ_GEO_NO_RECORD');
3062 FND_MESSAGE.SET_TOKEN( 'TOKEN1','country code');
3063 FND_MESSAGE.SET_TOKEN( 'TOKEN2', 'included_geography_id '||p_zone_relation_tbl(i).included_geography_id);
3064 FND_MSG_PUB.ADD;
3065 RAISE FND_API.G_EXC_ERROR;
3066 EXIT;
3067 END;
3068 END IF;
3069 END LOOP;
3070
3071
3072 -- call table handler to insert a row in hz_geographies
3073
3074 HZ_GEOGRAPHIES_PKG.insert_row(
3075 x_rowid => l_rowid,
3076 x_geography_id => x_geography_id,
3077 x_object_version_number => 1,
3078 x_geography_type => p_zone_type,
3079 x_geography_name => p_zone_name,
3080 x_geography_use => l_geography_use,
3081 x_geography_code => UPPER(p_zone_code),
3082 x_start_date => NVL(p_start_date,SYSDATE),
3083 x_end_date => NVL(p_end_date,l_end_date),
3084 x_multiple_parent_flag => 'N',
3085 x_created_by_module => p_created_by_module,
3086 x_country_code => l_country_code,
3087 x_geography_element1 => NULL,
3088 x_geography_element1_id => NULL,
3089 x_geography_element1_code => NULL,
3090 x_geography_element2 => NULL,
3091 x_geography_element2_id => NULL,
3092 x_geography_element2_code => NULL,
3093 x_geography_element3 => NULL,
3094 x_geography_element3_id => NULL,
3095 x_geography_element3_code => NULL,
3096 x_geography_element4 => NULL,
3097 x_geography_element4_id => NULL,
3098 x_geography_element4_code => NULL,
3099 x_geography_element5 => NULL,
3100 x_geography_element5_id => NULL,
3101 x_geography_element5_code => NULL,
3102 x_geography_element6 => NULL,
3103 x_geography_element6_id => NULL,
3104 x_geography_element7 => NULL,
3105 x_geography_element7_id => NULL,
3106 x_geography_element8 => NULL,
3107 x_geography_element8_id => NULL,
3108 x_geography_element9 => NULL,
3109 x_geography_element9_id => NULL,
3110 x_geography_element10 => NULL,
3111 x_geography_element10_id => NULL,
3112 x_geometry => p_geometry,
3113 x_timezone_code => p_timezone_code,
3114 x_application_id => p_application_id,
3115 x_program_login_id => NULL,
3116 x_attribute_category => NULL,
3117 x_attribute1 => NULL,
3118 x_attribute2 => NULL,
3119 x_attribute3 => NULL,
3120 x_attribute4 => NULL,
3121 x_attribute5 => NULL,
3122 x_attribute6 => NULL,
3123 x_attribute7 => NULL,
3124 x_attribute8 => NULL,
3125 x_attribute9 => NULL,
3126 x_attribute10 => NULL,
3127 x_attribute11 => NULL,
3128 x_attribute12 => NULL,
3129 x_attribute13 => NULL,
3130 x_attribute14 => NULL,
3131 x_attribute15 => NULL,
3132 x_attribute16 => NULL,
3133 x_attribute17 => NULL,
3134 x_attribute18 => NULL,
3135 x_attribute19 => NULL,
3136 x_attribute20 => NULL
3137 );
3138
3139 -- default language_code in case of NULL
3140 IF p_language_code IS NULL THEN
3141 SELECT userenv('LANG') INTO l_language_code FROM dual;
3142 ELSE
3143 l_language_code := p_language_code;
3144 END IF;
3145
3146
3147 -- construct identifier record
3148 l_geo_identifier_rec.geography_id := x_geography_id;
3149 l_geo_identifier_rec.identifier_subtype := 'STANDARD_NAME';
3150 l_geo_identifier_rec.identifier_value := p_zone_name;
3151 l_geo_identifier_rec.identifier_type := 'NAME';
3152 l_geo_identifier_rec.geo_data_provider := NVL(p_geo_data_provider,'USER_ENTERED');
3153 l_geo_identifier_rec.primary_flag := 'Y';
3154 l_geo_identifier_rec.language_code := l_language_code;
3155 l_geo_identifier_rec.created_by_module := p_created_by_module;
3156 l_geo_identifier_rec.application_id := p_application_id;
3157
3158
3159 -- call create identifier API
3160
3161 create_geo_identifier(
3162 p_init_msg_list => 'F',
3163 p_geo_identifier_rec => l_geo_identifier_rec,
3164 x_return_status => x_return_status,
3165 x_msg_count => x_msg_count,
3166 x_msg_data => x_msg_data
3167 );
3168
3169 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3170 RAISE FND_API.G_EXC_ERROR;
3171 END IF;
3172
3173 --dbms_output.put_line('After first identifier row '||x_return_status);
3174
3175 IF p_zone_code IS NOT NULL
3176 -- Bug 4579847 : do not call for g_miss value for code
3177 and p_zone_code <> fnd_api.g_miss_char THEN
3178 -- create identifier for zone code
3179 l_geo_identifier_rec.identifier_subtype := p_zone_code_type;
3180 l_geo_identifier_rec.identifier_value := p_zone_code;
3181 l_geo_identifier_rec.identifier_type := 'CODE';
3182
3183 create_geo_identifier(
3184 p_init_msg_list => 'F',
3185 p_geo_identifier_rec => l_geo_identifier_rec,
3186 x_return_status => x_return_status,
3187 x_msg_count => x_msg_count,
3188 x_msg_data => x_msg_data
3189 );
3190
3191 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3192 RAISE FND_API.G_EXC_ERROR;
3193 END IF;
3194
3195 --dbms_output.put_line('After second identifier row '||x_return_status);
3196 END IF;
3197
3198 -- call API to create zone relationship
3199 create_zone_relation(
3200 p_init_msg_list => 'F',
3201 p_geography_id => x_geography_id,
3202 p_zone_relation_tbl => p_zone_relation_tbl,
3203 p_created_by_module => p_created_by_module,
3204 p_application_id => p_application_id,
3205 x_return_status => x_return_status,
3206 x_msg_count => x_msg_count,
3207 x_msg_data => x_msg_data
3208 );
3209
3210 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3211 RAISE FND_API.G_EXC_ERROR;
3212 END IF;
3213 --dbms_output.put_line('After insert zone relation '||x_return_status);
3214
3215 END do_create_zone;
3216
3217 ----------------------------
3218 -- body of public procedures
3219 ----------------------------
3220
3221 /**
3222 * PROCEDURE create_master_relation
3223 *
3224 * DESCRIPTION
3225 * Creates Geography Relationships.
3226 *
3227 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3228 *
3229 * ARGUMENTS
3230 * IN:
3231 * p_init_msg_list Initialize message stack if it is set to
3232 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
3233 * p_master_relation_rec Geography type record.
3234 * IN/OUT:
3235 * OUT:
3236 * x_relationship_id Returns relationship_id for the relationship created.
3237 * x_return_status Return status after the call. The status can
3238 * be FND_API.G_RET_STS_SUCCESS (success),
3239 * FND_API.G_RET_STS_ERROR (error),
3240 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
3241 * x_msg_count Number of messages in message stack.
3242 * x_msg_data Message text if x_msg_count is 1.
3243 *
3244 * NOTES
3245 *
3246 * MODIFICATION HISTORY
3247 *
3248 * 11-22-2002 Rekha Nalluri o Created.
3249 *
3250 */
3251
3252 PROCEDURE create_master_relation (
3253 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3254 p_master_relation_rec IN MASTER_RELATION_REC_TYPE,
3255 x_relationship_id OUT NOCOPY NUMBER,
3256 x_return_status OUT NOCOPY VARCHAR2,
3257 x_msg_count OUT NOCOPY NUMBER,
3258 x_msg_data OUT NOCOPY VARCHAR2
3259 ) IS
3260
3261 --l_master_relation_rec MASTER_RELATION_REC_TYPE := p_master_relation_rec;
3262
3263 BEGIN
3264 -- Standard start of API savepoint
3265 SAVEPOINT create_master_relation;
3266
3267 -- Initialize message list if p_init_msg_list is set to TRUE.
3268 IF FND_API.to_Boolean(p_init_msg_list) THEN
3269 FND_MSG_PUB.initialize;
3270 END IF;
3271
3272 -- Initialize API return status to success.
3273 x_return_status := FND_API.G_RET_STS_SUCCESS;
3274
3275 -- Call to business logic.
3276 do_create_master_relation(
3277 p_master_relation_rec => p_master_relation_rec,
3278 x_relationship_id => x_relationship_id,
3279 x_return_status => x_return_status
3280 );
3281
3282 --if validation failed at any point, then raise an exception to stop processing
3283 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3284 RAISE FND_API.G_EXC_ERROR;
3285 END IF;
3286
3287 --g_dup_checked := 'N';
3288 -- Standard call to get message count and if count is 1, get message info.
3289 FND_MSG_PUB.Count_And_Get(
3290 p_encoded => FND_API.G_FALSE,
3291 p_count => x_msg_count,
3292 p_data => x_msg_data);
3293
3294 EXCEPTION
3295 WHEN FND_API.G_EXC_ERROR THEN
3296 g_dup_checked := 'N';
3297 ROLLBACK TO create_master_relation;
3298 x_return_status := FND_API.G_RET_STS_ERROR;
3299 FND_MSG_PUB.Count_And_Get(
3300 p_encoded => FND_API.G_FALSE,
3301 p_count => x_msg_count,
3302 p_data => x_msg_data);
3303 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3304 --g_dup_checked := 'N';
3305 ROLLBACK TO create_master_relation;
3306 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3307 FND_MSG_PUB.Count_And_Get(
3308 p_encoded => FND_API.G_FALSE,
3309 p_count => x_msg_count,
3310 p_data => x_msg_data);
3311
3312 WHEN OTHERS THEN
3313 --g_dup_checked := 'N';
3314 ROLLBACK TO create_master_relation;
3315 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3316 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
3317 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3318 FND_MSG_PUB.ADD;
3319 FND_MSG_PUB.Count_And_Get(
3320 p_encoded => FND_API.G_FALSE,
3321 p_count => x_msg_count,
3322 p_data => x_msg_data);
3323
3324 END create_master_relation;
3325
3326 /**
3327 * PROCEDURE update_relationship
3328 *
3329 * DESCRIPTION
3330 * Updates Geography Relationships.
3331 *
3332 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3333 *
3334 * ARGUMENTS
3335 * IN:
3336 * p_init_msg_list Initialize message stack if it is set to
3337 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
3338 * p_master_relation_rec Geography type record.
3339 * p_object_version_number Object version number of the row
3340 * IN/OUT:
3341 * OUT:
3342 *
3343 * x_return_status Return status after the call. The status can
3344 * be FND_API.G_RET_STS_SUCCESS (success),
3345 * FND_API.G_RET_STS_ERROR (error),
3346 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
3347 * x_msg_count Number of messages in message stack.
3348 * x_msg_data Message text if x_msg_count is 1.
3349 *
3350 * NOTES
3351 *
3352 * MODIFICATION HISTORY
3353 * 11-22-2002 Rekha Nalluri o Created.
3354 *
3355 */
3356
3357 PROCEDURE update_relationship (
3358 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3359 p_relationship_id IN NUMBER,
3360 p_status IN VARCHAR2,
3361 p_object_version_number IN OUT NOCOPY NUMBER,
3362 x_return_status OUT NOCOPY VARCHAR2,
3363 x_msg_count OUT NOCOPY NUMBER,
3364 x_msg_data OUT NOCOPY VARCHAR2
3365 )IS
3366
3367 BEGIN
3368 -- Standard start of API savepoint
3369 SAVEPOINT update_relationship;
3370
3371 -- Initialize message list if p_init_msg_list is set to TRUE.
3372 IF FND_API.to_Boolean(p_init_msg_list) THEN
3373 FND_MSG_PUB.initialize;
3374 END IF;
3375
3376 -- Initialize API return status to success.
3377 x_return_status := FND_API.G_RET_STS_SUCCESS;
3378
3379 -- Call to business logic.
3380 do_update_relationship(
3381 p_relationship_id => p_relationship_id,
3382 p_status => p_status,
3383 p_object_version_number => p_object_version_number,
3384 x_return_status => x_return_status
3385 );
3386
3387 --if validation failed at any point, then raise an exception to stop processing
3388 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3389 RAISE FND_API.G_EXC_ERROR;
3390 END IF;
3391
3392 -- Standard call to get message count and if count is 1, get message info.
3393 FND_MSG_PUB.Count_And_Get(
3394 p_encoded => FND_API.G_FALSE,
3395 p_count => x_msg_count,
3396 p_data => x_msg_data);
3397
3398 EXCEPTION
3399 WHEN FND_API.G_EXC_ERROR THEN
3400 ROLLBACK TO update_relationship;
3401 x_return_status := FND_API.G_RET_STS_ERROR;
3402 FND_MSG_PUB.Count_And_Get(
3403 p_encoded => FND_API.G_FALSE,
3404 p_count => x_msg_count,
3405 p_data => x_msg_data);
3406 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3407 ROLLBACK TO update_relationship;
3408 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3409 FND_MSG_PUB.Count_And_Get(
3410 p_encoded => FND_API.G_FALSE,
3411 p_count => x_msg_count,
3412 p_data => x_msg_data);
3413
3414 WHEN OTHERS THEN
3415 ROLLBACK TO update_relationship;
3416 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3417 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
3418 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3419 FND_MSG_PUB.ADD;
3420 FND_MSG_PUB.Count_And_Get(
3421 p_encoded => FND_API.G_FALSE,
3422 p_count => x_msg_count,
3423 p_data => x_msg_data);
3424
3425 END update_relationship;
3426
3427 /**
3428 * PROCEDURE create_geo_identifier
3429 *
3430 * DESCRIPTION
3431 * Creates Geography Identifiers.
3432 *
3433 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3434 *
3435 * ARGUMENTS
3436 * IN:
3437 * p_init_msg_list Initialize message stack if it is set to
3438 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
3439 * p_geo_identifier_rec Geo_identifier type record.
3440 * IN/OUT:
3441 * OUT:
3442 *
3443 * x_return_status Return status after the call. The status can
3444 * be FND_API.G_RET_STS_SUCCESS (success),
3445 * FND_API.G_RET_STS_ERROR (error),
3446 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
3447 * x_msg_count Number of messages in message stack.
3448 * x_msg_data Message text if x_msg_count is 1.
3449 *
3450 * NOTES
3451 *
3452 * MODIFICATION HISTORY
3453 * 12-03-2002 Rekha Nalluri o Created.
3454 *
3455 */
3456
3457 PROCEDURE create_geo_identifier(
3458 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3459 p_geo_identifier_rec IN GEO_IDENTIFIER_REC_TYPE,
3460 x_return_status OUT NOCOPY VARCHAR2,
3461 x_msg_count OUT NOCOPY NUMBER,
3462 x_msg_data OUT NOCOPY VARCHAR2
3463 ) IS
3464
3465 p_index_name VARCHAR2(30);
3466
3467 BEGIN
3468 -- Standard start of API savepoint
3469 SAVEPOINT create_geo_identifier;
3470
3471 -- Initialize message list if p_init_msg_list is set to TRUE.
3472 IF FND_API.to_Boolean(p_init_msg_list) THEN
3473 FND_MSG_PUB.initialize;
3474 END IF;
3475
3476 -- Initialize API return status to success.
3477 x_return_status := FND_API.G_RET_STS_SUCCESS;
3478
3479 -- Call to business logic.
3480 do_create_geo_identifier(
3481 p_geo_identifier_rec => p_geo_identifier_rec,
3482 x_return_status => x_return_status
3483 );
3484
3485 --if validation failed at any point, then raise an exception to stop processing
3486 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3487 RAISE FND_API.G_EXC_ERROR;
3488 END IF;
3489
3490 -- Standard call to get message count and if count is 1, get message info.
3491 FND_MSG_PUB.Count_And_Get(
3492 p_encoded => FND_API.G_FALSE,
3493 p_count => x_msg_count,
3494 p_data => x_msg_data);
3495
3496 EXCEPTION
3497 WHEN FND_API.G_EXC_ERROR THEN
3498 ROLLBACK TO create_geo_identifier;
3499 x_return_status := FND_API.G_RET_STS_ERROR;
3500 FND_MSG_PUB.Count_And_Get(
3501 p_encoded => FND_API.G_FALSE,
3502 p_count => x_msg_count,
3503 p_data => x_msg_data);
3504 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3505 ROLLBACK TO create_geo_identifier;
3506 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3507 FND_MSG_PUB.Count_And_Get(
3508 p_encoded => FND_API.G_FALSE,
3509 p_count => x_msg_count,
3510 p_data => x_msg_data);
3511
3512 WHEN DUP_VAL_ON_INDEX THEN
3513 ROLLBACK TO create_geo_identifier;
3514 x_return_status := FND_API.G_RET_STS_ERROR;
3515 HZ_UTILITY_V2PUB.find_index_name(p_index_name);
3516 IF p_index_name = 'HZ_GEOGRAPHY_IDENTIFIERS_U1' THEN
3517 FND_MESSAGE.SET_NAME('AR', 'HZ_API_DUPLICATE_COLUMN');
3518 FND_MESSAGE.SET_TOKEN('COLUMN', 'geography_id,identifier_type,identifier_subtype,identifier_value,language_code');
3519 FND_MSG_PUB.ADD;
3520 END IF;
3521 FND_MSG_PUB.Count_And_Get(
3522 p_encoded => FND_API.G_FALSE,
3523 p_count => x_msg_count,
3524 p_data => x_msg_data);
3525
3526 WHEN OTHERS THEN
3527 ROLLBACK TO create_geo_identifier;
3528 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3529 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
3530 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3531 FND_MSG_PUB.ADD;
3532 FND_MSG_PUB.Count_And_Get(
3533 p_encoded => FND_API.G_FALSE,
3534 p_count => x_msg_count,
3535 p_data => x_msg_data);
3536 END create_geo_identifier;
3537
3538 /**
3539 * PROCEDURE update_geo_identifier
3540 *
3541 * DESCRIPTION
3542 * Creates Geography Identifiers.
3543 *
3544 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3545 *
3546 * ARGUMENTS
3547 * IN:
3548 * p_init_msg_list Initialize message stack if it is set to
3549 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
3550 * p_geo_identifier_rec Geo_identifier type record.
3551 *
3552 * IN/OUT:
3553 * p_object_version_number
3554 * OUT:
3555 *
3556 * x_cp_request_id Concurrent Program Request Id, whenever CP
3557 * to update denormalized data gets kicked off.
3558 * x_return_status Return status after the call. The status can
3559 * be FND_API.G_RET_STS_SUCCESS (success),
3560 * FND_API.G_RET_STS_ERROR (error),
3561 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
3562 * x_msg_count Number of messages in message stack.
3563 * x_msg_data Message text if x_msg_count is 1.
3564 *
3565 * NOTES
3566 *
3567 * MODIFICATION HISTORY
3568 * 12-03-2002 Rekha Nalluri o Created.
3569 * 21-Oct-2005 Nishant Added x_cp_request_id OUT parameter
3570 * for Bug 457886
3571 *
3572 */
3573 PROCEDURE update_geo_identifier (
3574 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3575 p_geo_identifier_rec IN GEO_IDENTIFIER_REC_TYPE,
3576 p_object_version_number IN OUT NOCOPY NUMBER,
3577 x_cp_request_id OUT NOCOPY NUMBER,
3578 x_return_status OUT NOCOPY VARCHAR2,
3579 x_msg_count OUT NOCOPY NUMBER,
3580 x_msg_data OUT NOCOPY VARCHAR2
3581 )IS
3582
3583 BEGIN
3584
3585 -- Standard start of API savepoint
3586 SAVEPOINT update_geo_identifier;
3587
3588 -- Initialize message list if p_init_msg_list is set to TRUE.
3589 IF FND_API.to_Boolean(p_init_msg_list) THEN
3590 FND_MSG_PUB.initialize;
3591 END IF;
3592
3593 -- Initialize API return status to success.
3594 x_return_status := FND_API.G_RET_STS_SUCCESS;
3595
3596 -- Call to business logic.
3597 do_update_geo_identifier(
3598 p_geo_identifier_rec => p_geo_identifier_rec,
3599 p_object_version_number => p_object_version_number,
3600 x_cp_request_id => x_cp_request_id,
3601 x_return_status => x_return_status
3602 );
3603
3604 --if validation failed at any point, then raise an exception to stop processing
3605 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3606 RAISE FND_API.G_EXC_ERROR;
3607 END IF;
3608
3609 -- Standard call to get message count and if count is 1, get message info.
3610 FND_MSG_PUB.Count_And_Get(
3611 p_encoded => FND_API.G_FALSE,
3612 p_count => x_msg_count,
3613 p_data => x_msg_data);
3614
3615 EXCEPTION
3616 WHEN FND_API.G_EXC_ERROR THEN
3617 ROLLBACK TO update_geo_identifier;
3618 x_return_status := FND_API.G_RET_STS_ERROR;
3619 FND_MSG_PUB.Count_And_Get(
3620 p_encoded => FND_API.G_FALSE,
3621 p_count => x_msg_count,
3622 p_data => x_msg_data);
3623 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3624 ROLLBACK TO update_geo_identifier;
3625 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3626 FND_MSG_PUB.Count_And_Get(
3627 p_encoded => FND_API.G_FALSE,
3628 p_count => x_msg_count,
3629 p_data => x_msg_data);
3630
3631 WHEN OTHERS THEN
3632 ROLLBACK TO update_geo_identifier;
3633 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3634 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
3635 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3636 FND_MSG_PUB.ADD;
3637 FND_MSG_PUB.Count_And_Get(
3638 p_encoded => FND_API.G_FALSE,
3639 p_count => x_msg_count,
3640 p_data => x_msg_data);
3641
3642 END update_geo_identifier;
3643
3644 /**
3645 * PROCEDURE delete_geo_identifier
3646 *
3647 * DESCRIPTION
3648 * Deletes Geography Identifiers.
3649 *
3650 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3651 *
3652 * ARGUMENTS
3653 * IN:
3654 * p_init_msg_list Initialize message stack if it is set to
3655 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
3656 * p_geography_id geography id
3657 * p_identifier_type
3658 * p_identifier_subtype
3659 * p_identifier_value
3660 *
3661 * OUT:
3662 *
3663 * x_return_status Return status after the call. The status can
3664 * be FND_API.G_RET_STS_SUCCESS (success),
3665 * FND_API.G_RET_STS_ERROR (error),
3666 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
3667 * x_msg_count Number of messages in message stack.
3668 * x_msg_data Message text if x_msg_count is 1.
3669 *
3670 * NOTES
3671 *
3672 * MODIFICATION HISTORY
3673 * 01-02-2003 Rekha Nalluri o Created.
3674 *
3675 */
3676
3677 PROCEDURE delete_geo_identifier(
3678 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3679 p_geography_id IN NUMBER,
3680 p_identifier_type IN VARCHAR2,
3681 p_identifier_subtype IN VARCHAR2,
3682 p_identifier_value IN VARCHAR2,
3683 p_language_code IN VARCHAR2,
3684 x_return_status OUT NOCOPY VARCHAR2,
3685 x_msg_count OUT NOCOPY NUMBER,
3686 x_msg_data OUT NOCOPY VARCHAR2
3687 ) IS
3688 BEGIN
3689
3690 -- Standard start of API savepoint
3691 SAVEPOINT delete_geo_identifier;
3692
3693 -- Initialize message list if p_init_msg_list is set to TRUE.
3694 IF FND_API.to_Boolean(p_init_msg_list) THEN
3695 FND_MSG_PUB.initialize;
3696 END IF;
3697
3698 -- Initialize API return status to success.
3699 x_return_status := FND_API.G_RET_STS_SUCCESS;
3700
3701 -- Call to business logic.
3702 do_delete_geo_identifier(
3703 p_geography_id => p_geography_id,
3704 p_identifier_type => p_identifier_type,
3705 p_identifier_subtype => p_identifier_subtype,
3706 p_identifier_value => p_identifier_value,
3707 p_language_code => p_language_code,
3708 x_return_status => x_return_status
3709 );
3710
3711 --if validation failed at any point, then raise an exception to stop processing
3712 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3713 RAISE FND_API.G_EXC_ERROR;
3714 END IF;
3715
3716 -- Standard call to get message count and if count is 1, get message info.
3717 FND_MSG_PUB.Count_And_Get(
3718 p_encoded => FND_API.G_FALSE,
3719 p_count => x_msg_count,
3720 p_data => x_msg_data);
3721
3722 EXCEPTION
3723 WHEN FND_API.G_EXC_ERROR THEN
3724 ROLLBACK TO delete_geo_identifier;
3725 x_return_status := FND_API.G_RET_STS_ERROR;
3726 FND_MSG_PUB.Count_And_Get(
3727 p_encoded => FND_API.G_FALSE,
3728 p_count => x_msg_count,
3729 p_data => x_msg_data);
3730 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3731 ROLLBACK TO delete_geo_identifier;
3732 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3733 FND_MSG_PUB.Count_And_Get(
3734 p_encoded => FND_API.G_FALSE,
3735 p_count => x_msg_count,
3736 p_data => x_msg_data);
3737
3738 WHEN OTHERS THEN
3739 ROLLBACK TO delete_geo_identifier;
3740 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3741 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
3742 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3743 FND_MSG_PUB.ADD;
3744 FND_MSG_PUB.Count_And_Get(
3745 p_encoded => FND_API.G_FALSE,
3746 p_count => x_msg_count,
3747 p_data => x_msg_data);
3748
3749 END delete_geo_identifier;
3750
3751
3752 /**
3753 * PROCEDURE create_master_geography
3754 *
3755 * DESCRIPTION
3756 * Creates Master Geography.
3757 *
3758 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3759 *
3760 * ARGUMENTS
3761 * IN:
3762 * p_init_msg_list Initialize message stack if it is set to
3763 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
3764 * p_master_geography_rec Master Geography type record.
3765 * IN/OUT:
3766 * OUT:
3767 *
3768 * x_geography_id Return ID of the geography being created.
3769 * x_return_status Return status after the call. The status can
3770 * be FND_API.G_RET_STS_SUCCESS (success),
3771 * FND_API.G_RET_STS_ERROR (error),
3772 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
3773 * x_msg_count Number of messages in message stack.
3774 * x_msg_data Message text if x_msg_count is 1.
3775 *
3776 * NOTES
3777 *
3778 * MODIFICATION HISTORY
3779 * 12-03-2002 Rekha Nalluri o Created.
3780 *
3781 */
3782
3783 PROCEDURE create_master_geography(
3784 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3785 p_master_geography_rec IN MASTER_GEOGRAPHY_REC_TYPE,
3786 x_geography_id OUT NOCOPY NUMBER,
3787 x_return_status OUT NOCOPY VARCHAR2,
3788 x_msg_count OUT NOCOPY NUMBER,
3789 x_msg_data OUT NOCOPY VARCHAR2
3790 )IS
3791
3792 BEGIN
3793 -- Standard start of API savepoint
3794 SAVEPOINT create_master_geography;
3795 --dbms_output.put_line('In the beginning of create_master_geography');
3796
3797 -- Initialize message list if p_init_msg_list is set to TRUE.
3798 IF FND_API.to_Boolean(p_init_msg_list) THEN
3799 FND_MSG_PUB.initialize;
3800 END IF;
3801
3802 -- Initialize API return status to success.
3803 x_return_status := FND_API.G_RET_STS_SUCCESS;
3804
3805 --dbms_output.put_line('before do_create_master_geography');
3806 -- Call to business logic.
3807 do_create_master_geography(
3808 p_master_geography_rec => p_master_geography_rec,
3809 x_geography_id => x_geography_id,
3810 x_return_status => x_return_status
3811 );
3812
3813 --if validation failed at any point, then raise an exception to stop processing
3814 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3815 RAISE FND_API.G_EXC_ERROR;
3816 END IF;
3817
3818 -- Standard call to get message count and if count is 1, get message info.
3819 FND_MSG_PUB.Count_And_Get(
3820 p_encoded => FND_API.G_FALSE,
3821 p_count => x_msg_count,
3822 p_data => x_msg_data);
3823
3824 EXCEPTION
3825 WHEN FND_API.G_EXC_ERROR THEN
3826 ROLLBACK TO create_master_geography;
3827 x_return_status := FND_API.G_RET_STS_ERROR;
3828 FND_MSG_PUB.Count_And_Get(
3829 p_encoded => FND_API.G_FALSE,
3830 p_count => x_msg_count,
3831 p_data => x_msg_data);
3832 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3833 ROLLBACK TO create_master_geography;
3834 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3835 FND_MSG_PUB.Count_And_Get(
3836 p_encoded => FND_API.G_FALSE,
3837 p_count => x_msg_count,
3838 p_data => x_msg_data);
3839
3840 WHEN OTHERS THEN
3841 ROLLBACK TO create_master_geography;
3842 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3843 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
3844 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3845 FND_MSG_PUB.ADD;
3846 FND_MSG_PUB.Count_And_Get(
3847 p_encoded => FND_API.G_FALSE,
3848 p_count => x_msg_count,
3849 p_data => x_msg_data);
3850
3851 END create_master_geography;
3852
3853 /**
3854 * PROCEDURE update_geography
3855 *
3856 * DESCRIPTION
3857 * Updates Geography
3858 *
3859 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3860 *
3861 * ARGUMENTS
3862 * IN:
3863 * p_init_msg_list Initialize message stack if it is set to
3864 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
3865 * p_master_geography_rec Master Geography type record.
3866 *
3867 * IN/OUT:
3868 * p_object_version_number
3869 * OUT:
3870 *
3871 * x_return_status Return status after the call. The status can
3872 * be FND_API.G_RET_STS_SUCCESS (success),
3873 * FND_API.G_RET_STS_ERROR (error),
3874 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
3875 * x_msg_count Number of messages in message stack.
3876 * x_msg_data Message text if x_msg_count is 1.
3877 *
3878 * NOTES
3879 *
3880 * MODIFICATION HISTORY
3881 * 12-12-2002 Rekha Nalluri o Created.
3882 *
3883 */
3884 PROCEDURE update_geography (
3885 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3886 p_geography_id IN NUMBER,
3887 p_end_date IN DATE,
3888 p_geometry IN MDSYS.SDO_GEOMETRY,
3889 p_timezone_code IN VARCHAR2,
3890 p_object_version_number IN OUT NOCOPY NUMBER,
3891 x_return_status OUT NOCOPY VARCHAR2,
3892 x_msg_count OUT NOCOPY NUMBER,
3893 x_msg_data OUT NOCOPY VARCHAR2
3894 ) IS
3895
3896 BEGIN
3897
3898 -- Standard start of API savepoint
3899 SAVEPOINT update_geography;
3900
3901 -- Initialize message list if p_init_msg_list is set to TRUE.
3902 IF FND_API.to_Boolean(p_init_msg_list) THEN
3903 FND_MSG_PUB.initialize;
3904 END IF;
3905
3906 -- Initialize API return status to success.
3907 x_return_status := FND_API.G_RET_STS_SUCCESS;
3908
3909 -- Call to business logic.
3910 do_update_geography(
3911 p_geography_id => p_geography_id,
3912 p_end_date => p_end_date,
3913 p_geometry => p_geometry,
3914 p_timezone_code => p_timezone_code,
3915 p_object_version_number => p_object_version_number,
3916 x_return_status => x_return_status
3917 );
3918
3919 --if validation failed at any point, then raise an exception to stop processing
3920 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3921 RAISE FND_API.G_EXC_ERROR;
3922 END IF;
3923
3924 -- Standard call to get message count and if count is 1, get message info.
3925 FND_MSG_PUB.Count_And_Get(
3926 p_encoded => FND_API.G_FALSE,
3927 p_count => x_msg_count,
3928 p_data => x_msg_data);
3929
3930 EXCEPTION
3931 WHEN FND_API.G_EXC_ERROR THEN
3932 ROLLBACK TO update_geography;
3933 x_return_status := FND_API.G_RET_STS_ERROR;
3934 FND_MSG_PUB.Count_And_Get(
3935 p_encoded => FND_API.G_FALSE,
3936 p_count => x_msg_count,
3937 p_data => x_msg_data);
3938 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3939 ROLLBACK TO update_geography;
3940 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3941 FND_MSG_PUB.Count_And_Get(
3942 p_encoded => FND_API.G_FALSE,
3943 p_count => x_msg_count,
3944 p_data => x_msg_data);
3945
3946 WHEN OTHERS THEN
3947 ROLLBACK TO update_geography;
3948 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3949 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
3950 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3951 FND_MSG_PUB.ADD;
3952 FND_MSG_PUB.Count_And_Get(
3953 p_encoded => FND_API.G_FALSE,
3954 p_count => x_msg_count,
3955 p_data => x_msg_data);
3956
3957 END update_geography;
3958
3959 /**
3960 * PROCEDURE create_geography_range
3961 *
3962 * DESCRIPTION
3963 * Creates Geography Range.
3964 *
3965 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3966 *
3967 * ARGUMENTS
3968 * IN:
3969 * p_init_msg_list Initialize message stack if it is set to
3970 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
3971 * p_geography_range_rec Geography range type record.
3972 * IN/OUT:
3973 * OUT:
3974 *
3975 * x_return_status Return status after the call. The status can
3976 * be FND_API.G_RET_STS_SUCCESS (success),
3977 * FND_API.G_RET_STS_ERROR (error),
3978 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
3979 * x_msg_count Number of messages in message stack.
3980 * x_msg_data Message text if x_msg_count is 1.
3981 *
3982 * NOTES
3983 *
3984 * MODIFICATION HISTORY
3985 * 01-20-2003 Rekha Nalluri o Created.
3986 *
3987 */
3988
3989 PROCEDURE create_geography_range(
3990 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3991 p_geography_range_rec IN GEOGRAPHY_RANGE_REC_TYPE,
3992 x_return_status OUT NOCOPY VARCHAR2,
3993 x_msg_count OUT NOCOPY NUMBER,
3994 x_msg_data OUT NOCOPY VARCHAR2
3995 ) IS
3996
3997 p_index_name VARCHAR2(30);
3998
3999 BEGIN
4000 -- Standard start of API savepoint
4001 SAVEPOINT create_geography_range;
4002 --dbms_output.put_line('In the beginning of create_master_geography');
4003
4004 -- Initialize message list if p_init_msg_list is set to TRUE.
4005 IF FND_API.to_Boolean(p_init_msg_list) THEN
4006 FND_MSG_PUB.initialize;
4007 END IF;
4008
4009 -- Initialize API return status to success.
4010 x_return_status := FND_API.G_RET_STS_SUCCESS;
4011
4012 --dbms_output.put_line('before do_create_geography_range');
4013 -- Call to business logic.
4014 do_create_geography_range(
4015 p_geography_range_rec => p_geography_range_rec,
4016 x_return_status => x_return_status
4017 );
4018
4019 --if validation failed at any point, then raise an exception to stop processing
4020 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4021 RAISE FND_API.G_EXC_ERROR;
4022 END IF;
4023
4024 -- Standard call to get message count and if count is 1, get message info.
4025 FND_MSG_PUB.Count_And_Get(
4026 p_encoded => FND_API.G_FALSE,
4027 p_count => x_msg_count,
4028 p_data => x_msg_data);
4029
4030 EXCEPTION
4031 WHEN FND_API.G_EXC_ERROR THEN
4032 ROLLBACK TO create_geography_range;
4033 x_return_status := FND_API.G_RET_STS_ERROR;
4034 FND_MSG_PUB.Count_And_Get(
4035 p_encoded => FND_API.G_FALSE,
4036 p_count => x_msg_count,
4037 p_data => x_msg_data);
4038 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4039 ROLLBACK TO create_geography_range;
4040 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4041 FND_MSG_PUB.Count_And_Get(
4042 p_encoded => FND_API.G_FALSE,
4043 p_count => x_msg_count,
4044 p_data => x_msg_data);
4045
4046 WHEN DUP_VAL_ON_INDEX THEN
4047 ROLLBACK TO create_geography_range;
4048 x_return_status := FND_API.G_RET_STS_ERROR;
4049 HZ_UTILITY_V2PUB.find_index_name(p_index_name);
4050 IF p_index_name = 'HZ_GEOGRAPHY_RANGES_U1' THEN
4051 FND_MESSAGE.SET_NAME('AR', 'HZ_API_DUPLICATE_COLUMN');
4052 FND_MESSAGE.SET_TOKEN('COLUMN', 'geography_id,geography_from and start_date');
4053 FND_MSG_PUB.ADD;
4054 END IF;
4055 FND_MSG_PUB.Count_And_Get(
4056 p_encoded => FND_API.G_FALSE,
4057 p_count => x_msg_count,
4058 p_data => x_msg_data);
4059
4060 WHEN OTHERS THEN
4061 ROLLBACK TO create_geography_range;
4062 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4063 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
4064 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
4065 FND_MSG_PUB.ADD;
4066 FND_MSG_PUB.Count_And_Get(
4067 p_encoded => FND_API.G_FALSE,
4068 p_count => x_msg_count,
4069 p_data => x_msg_data);
4070
4071 END create_geography_range;
4072
4073 /**
4074 * PROCEDURE update_geography_range
4075 *
4076 * DESCRIPTION
4077 * Updates Geography range
4078 *
4079 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
4080 *
4081 * ARGUMENTS
4082 * IN:
4083 * p_init_msg_list Initialize message stack if it is set to
4084 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
4085 * geography_id
4086 * geography_from
4087 * start_date
4088 * end_date
4089 *
4090 * IN/OUT:
4091 * p_object_version_number
4092 * OUT:
4093 *
4094 * x_return_status Return status after the call. The status can
4095 * be FND_API.G_RET_STS_SUCCESS (success),
4096 * FND_API.G_RET_STS_ERROR (error),
4097 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
4098 * x_msg_count Number of messages in message stack.
4099 * x_msg_data Message text if x_msg_count is 1.
4100 *
4101 * NOTES
4102 *
4103 * MODIFICATION HISTORY
4104 * 01-23-2003 Rekha Nalluri o Created.
4105 *
4106 */
4107 PROCEDURE update_geography_range (
4108 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4109 p_geography_id IN NUMBER,
4110 p_geography_from IN VARCHAR2,
4111 p_start_date IN DATE,
4112 p_end_date IN DATE,
4113 p_object_version_number IN OUT NOCOPY NUMBER,
4114 x_return_status OUT NOCOPY VARCHAR2,
4115 x_msg_count OUT NOCOPY NUMBER,
4116 x_msg_data OUT NOCOPY VARCHAR2
4117 ) IS
4118
4119 BEGIN
4120
4121 -- Standard start of API savepoint
4122 SAVEPOINT update_geography_range;
4123
4124 -- Initialize message list if p_init_msg_list is set to TRUE.
4125 IF FND_API.to_Boolean(p_init_msg_list) THEN
4126 FND_MSG_PUB.initialize;
4127 END IF;
4128
4129 -- Initialize API return status to success.
4130 x_return_status := FND_API.G_RET_STS_SUCCESS;
4131
4132 -- Call to business logic.
4133 do_update_geography_range(
4134 p_geography_id => p_geography_id,
4135 p_geography_from => p_geography_from,
4136 p_start_date => p_start_date,
4137 p_end_date => p_end_date,
4138 p_object_version_number => p_object_version_number,
4139 x_return_status => x_return_status
4140 );
4141
4142 --if validation failed at any point, then raise an exception to stop processing
4143 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4144 RAISE FND_API.G_EXC_ERROR;
4145 END IF;
4146
4147 -- Standard call to get message count and if count is 1, get message info.
4148 FND_MSG_PUB.Count_And_Get(
4149 p_encoded => FND_API.G_FALSE,
4150 p_count => x_msg_count,
4151 p_data => x_msg_data);
4152
4153 EXCEPTION
4154 WHEN FND_API.G_EXC_ERROR THEN
4155 ROLLBACK TO update_geography_range;
4156 x_return_status := FND_API.G_RET_STS_ERROR;
4157 FND_MSG_PUB.Count_And_Get(
4158 p_encoded => FND_API.G_FALSE,
4159 p_count => x_msg_count,
4160 p_data => x_msg_data);
4161 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4162 ROLLBACK TO update_geography_range;
4163 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4164 FND_MSG_PUB.Count_And_Get(
4165 p_encoded => FND_API.G_FALSE,
4166 p_count => x_msg_count,
4167 p_data => x_msg_data);
4168
4169 WHEN OTHERS THEN
4170 ROLLBACK TO update_geography_range;
4171 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4172 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
4173 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
4174 FND_MSG_PUB.ADD;
4175 FND_MSG_PUB.Count_And_Get(
4176 p_encoded => FND_API.G_FALSE,
4177 p_count => x_msg_count,
4178 p_data => x_msg_data);
4179
4180 END update_geography_range;
4181
4182 /**
4183 * PROCEDURE create_zone_relation
4184 *
4185 * DESCRIPTION
4186 * Creates Zone Relation.
4187 *
4188 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
4189 *
4190 * ARGUMENTS
4191 * IN:
4192 * p_init_msg_list Initialize message stack if it is set to
4193 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
4194 * p_geography_id
4195 * p_zone_relation_tbl Zone relation table of records.
4196 * IN/OUT:
4197 * OUT:
4198 *
4199 * x_return_status Return status after the call. The status can
4200 * be FND_API.G_RET_STS_SUCCESS (success),
4201 * FND_API.G_RET_STS_ERROR (error),
4202 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
4203 * x_msg_count Number of messages in message stack.
4204 * x_msg_data Message text if x_msg_count is 1.
4205 *
4206 * NOTES
4207 *
4208 * MODIFICATION HISTORY
4209 * 01-23-2003 Rekha Nalluri o Created.
4210 *
4211 */
4212
4213 PROCEDURE create_zone_relation(
4214 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4215 p_geography_id IN NUMBER,
4216 p_zone_relation_tbl IN ZONE_RELATION_TBL_TYPE,
4217 p_created_by_module IN VARCHAR2,
4218 p_application_id IN NUMBER,
4219 x_return_status OUT NOCOPY VARCHAR2,
4220 x_msg_count OUT NOCOPY NUMBER,
4221 x_msg_data OUT NOCOPY VARCHAR2
4222 ) IS
4223
4224 BEGIN
4225 -- Standard start of API savepoint
4226 SAVEPOINT create_zone_relation;
4227 --dbms_output.put_line('In the beginning of create_zone_relation');
4228
4229 -- Initialize message list if p_init_msg_list is set to TRUE.
4230 IF FND_API.to_Boolean(p_init_msg_list) THEN
4231 FND_MSG_PUB.initialize;
4232 END IF;
4233
4234 -- Initialize API return status to success.
4235 x_return_status := FND_API.G_RET_STS_SUCCESS;
4236
4237 --dbms_output.put_line('before do_create_zone_relation');
4238 -- Call to business logic.
4239 do_create_zone_relation(
4240 p_geography_id => p_geography_id,
4241 p_zone_relation_tbl => p_zone_relation_tbl,
4242 p_created_by_module => p_created_by_module,
4243 p_application_id => p_application_id,
4244 x_return_status => x_return_status
4245 );
4246
4247 --if validation failed at any point, then raise an exception to stop processing
4248 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4249 RAISE FND_API.G_EXC_ERROR;
4250 END IF;
4251
4252 --dbms_output.put_line('after call to do_create '|| x_return_status);
4253
4254 -- Standard call to get message count and if count is 1, get message info.
4255 FND_MSG_PUB.Count_And_Get(
4256 p_encoded => FND_API.G_FALSE,
4257 p_count => x_msg_count,
4258 p_data => x_msg_data);
4259
4260 EXCEPTION
4261 WHEN FND_API.G_EXC_ERROR THEN
4262 ROLLBACK TO create_zone_relation;
4263 x_return_status := FND_API.G_RET_STS_ERROR;
4264 FND_MSG_PUB.Count_And_Get(
4265 p_encoded => FND_API.G_FALSE,
4266 p_count => x_msg_count,
4267 p_data => x_msg_data);
4268 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4269 ROLLBACK TO create_zone_relation;
4270 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4271 FND_MSG_PUB.Count_And_Get(
4272 p_encoded => FND_API.G_FALSE,
4273 p_count => x_msg_count,
4274 p_data => x_msg_data);
4275
4276 WHEN OTHERS THEN
4277 ROLLBACK TO create_zone_relation;
4278 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4279 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
4280 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
4281 FND_MSG_PUB.ADD;
4282 FND_MSG_PUB.Count_And_Get(
4283 p_encoded => FND_API.G_FALSE,
4284 p_count => x_msg_count,
4285 p_data => x_msg_data);
4286
4287 END create_zone_relation;
4288
4289
4290 /**
4291 * PROCEDURE create_zone
4292 *
4293 * DESCRIPTION
4294 * Creates Zone
4295 *
4296 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
4297 *
4298 * ARGUMENTS
4299 * IN:
4300 * p_init_msg_list Initialize message stack if it is set to
4301 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
4302 * p_zone_type
4303 * p_zone_name
4304 * p_zone_code
4305 * p_start_date
4306 * p_end_date
4307 * p_geo_data_provider
4308 * p_zone_relation_tbl table of records to create relationships
4309 * p_geometry
4310 * p_timezone_code
4311 * p_created_by_module
4312 * p_application_id
4313 * p_program_login_id
4314 *
4315 * OUT:
4316 * x_return_status
4317 * Return status after the call. The status can
4318 * be FND_API.G_RET_STS_SUCCESS (success),
4319 * FND_API.G_RET_STS_ERROR (error),
4320 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
4321 * x_msg_count Number of messages in message stack.
4322 * x_msg_data Message text if x_msg_count is 1.
4323 *
4324 * NOTES
4325 *
4326 * MODIFICATION HISTORY
4327 * 01-24-2003 Rekha Nalluri o Created.
4328 *
4329 */
4330
4331 PROCEDURE create_zone(
4332 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4333 p_zone_type IN VARCHAR2,
4334 p_zone_name IN VARCHAR2,
4335 p_zone_code IN VARCHAR2,
4336 p_zone_code_type IN VARCHAR2,
4337 p_start_date IN DATE,
4338 p_end_date IN DATE,
4339 p_geo_data_provider IN VARCHAR2,
4340 p_language_code IN VARCHAR2,
4341 p_zone_relation_tbl IN ZONE_RELATION_TBL_TYPE,
4342 p_geometry IN MDSYS.SDO_GEOMETRY,
4343 p_timezone_code IN VARCHAR2,
4344 x_geography_id OUT NOCOPY NUMBER,
4345 p_created_by_module IN VARCHAR2,
4346 p_application_id IN NUMBER,
4347 x_return_status OUT NOCOPY VARCHAR2,
4348 x_msg_count OUT NOCOPY NUMBER,
4349 x_msg_data OUT NOCOPY VARCHAR2
4350 ) IS
4351
4352 BEGIN
4353
4354
4355 -- Standard start of API savepoint
4356 SAVEPOINT create_zone;
4357 --dbms_output.put_line('In the beginning of create_zone');
4358
4359 -- Initialize message list if p_init_msg_list is set to TRUE.
4360 IF FND_API.to_Boolean(p_init_msg_list) THEN
4361 FND_MSG_PUB.initialize;
4362 END IF;
4363
4364 -- Initialize API return status to success.
4365 x_return_status := FND_API.G_RET_STS_SUCCESS;
4366
4367 --dbms_output.put_line('before do_create_zone');
4368 -- Call to business logic.
4369 do_create_zone (
4370 p_zone_type => p_zone_type,
4371 p_zone_name => p_zone_name,
4372 p_zone_code => p_zone_code,
4373 p_zone_code_type => p_zone_code_type,
4374 p_start_date => p_start_date,
4375 p_end_date => p_end_date,
4376 p_geo_data_provider => p_geo_data_provider,
4377 p_language_code => p_language_code,
4378 p_zone_relation_tbl => p_zone_relation_tbl,
4379 p_geometry => p_geometry,
4380 p_timezone_code => p_timezone_code,
4381 x_geography_id => x_geography_id,
4382 p_created_by_module => p_created_by_module,
4383 p_application_id => p_application_id,
4384 x_return_status => x_return_status
4385 );
4386
4387 --if validation failed at any point, then raise an exception to stop processing
4388 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4389 RAISE FND_API.G_EXC_ERROR;
4390 END IF;
4391
4392 -- Standard call to get message count and if count is 1, get message info.
4393 FND_MSG_PUB.Count_And_Get(
4394 p_encoded => FND_API.G_FALSE,
4395 p_count => x_msg_count,
4396 p_data => x_msg_data);
4397
4398 EXCEPTION
4399 WHEN FND_API.G_EXC_ERROR THEN
4400 ROLLBACK TO create_zone;
4401 x_return_status := FND_API.G_RET_STS_ERROR;
4402 FND_MSG_PUB.Count_And_Get(
4403 p_encoded => FND_API.G_FALSE,
4404 p_count => x_msg_count,
4405 p_data => x_msg_data);
4406 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4407 ROLLBACK TO create_zone;
4408 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4409 FND_MSG_PUB.Count_And_Get(
4410 p_encoded => FND_API.G_FALSE,
4411 p_count => x_msg_count,
4412 p_data => x_msg_data);
4413
4414 WHEN OTHERS THEN
4415 ROLLBACK TO create_zone;
4416 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4417 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
4418 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
4419 FND_MSG_PUB.ADD;
4420 FND_MSG_PUB.Count_And_Get(
4421 p_encoded => FND_API.G_FALSE,
4422 p_count => x_msg_count,
4423 p_data => x_msg_data);
4424 END create_zone;
4425
4426 END HZ_GEOGRAPHY_PUB;