[Home] [Help]
PACKAGE BODY: APPS.CN_DIM_HIERARCHIES_PVT
Source
1 PACKAGE BODY CN_DIM_HIERARCHIES_PVT AS
2 -- $Header: cnvdimhb.pls 120.7 2006/09/18 22:59:36 jxsingh noship $
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CN_DIM_HIERARCHIES_PVT';
5
6 -- Create a new hierarchy type
7 PROCEDURE Create_Hierarchy_Type
8 (p_api_version IN NUMBER, -- required
9 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
10 p_commit IN VARCHAR2 := FND_API.G_FALSE,
11 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
12 p_name IN CN_DIMENSIONS.NAME%TYPE,
13 p_base_table_id IN CN_OBJ_TABLES_V.TABLE_ID%TYPE,
14 p_primary_key_id IN CN_OBJ_COLUMNS_V.COLUMN_ID%TYPE,
15 p_user_column_id IN CN_OBJ_COLUMNS_V.COLUMN_ID%TYPE,
16 --R12 MOAC Changes--Start
17 p_org_id IN CN_DIMENSIONS.ORG_ID%TYPE,
18 --R12 MOAC Changes--End
19 p_description IN CN_DIMENSIONS.DESCRIPTION%TYPE, -- Added for R12
20 x_return_status OUT NOCOPY VARCHAR2,
21 x_msg_count OUT NOCOPY NUMBER,
22 x_msg_data OUT NOCOPY VARCHAR2,
23 x_dimension_id OUT NOCOPY CN_DIMENSIONS.DIMENSION_ID%TYPE) IS
24
25 l_api_name CONSTANT VARCHAR2(30) := 'Create_Hierarchy_Type';
26 l_api_version CONSTANT NUMBER := 1.0;
27 l_rowid ROWID;
28 l_count NUMBER;
29 l_count_tl NUMBER;
30
31 BEGIN
32 -- Standard Start of API savepoint
33 SAVEPOINT Create_Hierarchy_Type;
34 -- Standard call to check for call compatibility.
35 IF NOT FND_API.Compatible_API_Call
36 (l_api_version ,
37 p_api_version ,
38 l_api_name ,
39 G_PKG_NAME )
40 THEN
41 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
42 END IF;
43 -- Initialize message list if p_init_msg_list is set to TRUE.
44 IF FND_API.to_Boolean( p_init_msg_list ) THEN
45 FND_MSG_PUB.initialize;
46 END IF;
47 -- Initialize API return status to success
48 x_return_status := FND_API.G_RET_STS_SUCCESS;
49
50 -- API body
51 -- call the original forms API (CNDIHY.fmb -> CNDIHY2s/b.pls)
52
53 -- validate name is unique
54 select count(1) into l_count from cn_dimensions where name = p_name and org_id = p_org_id; --R12 MOAC change
55 if l_count > 0 then
56 FND_MESSAGE.SET_NAME('CN', 'CN_NAME_NOT_UNIQUE');
57 FND_MSG_PUB.ADD;
58 RAISE FND_API.G_EXC_ERROR;
59 end if;
60
61 select count(1) into l_count_tl
62 from cn_dimensions_all_tl T, fnd_languages L
63 where name = p_name
64 and org_id = p_org_id
65 and T.language = L.language_code
66 and L.INSTALLED_FLAG in ('I', 'B');
67
68 if l_count_tl > 0 then
69 FND_MESSAGE.SET_NAME('CN', 'CN_NAME_NOT_UNIQUE');
70 FND_MSG_PUB.ADD;
71 RAISE FND_API.G_EXC_ERROR;
72 end if;
73
74 -- get dimension ID
75 x_dimension_id := CN_DIMENSIONS_PKG.New_Dimension;
76 cn_dimensions_pkg.insert_row
77 (
78 X_DIMENSION_ID => x_dimension_id,
79 X_DESCRIPTION => p_description,-- Added for R12
80 X_SOURCE_TABLE_ID => p_base_table_id,
81 X_NAME => p_name,
82 X_CREATION_DATE => sysdate,
83 X_CREATED_BY => fnd_global.user_id,
84 X_LAST_UPDATE_DATE => sysdate,
85 X_LAST_UPDATED_BY => fnd_global.user_id,
86 X_LAST_UPDATE_LOGIN => fnd_global.login_id,
87 --R12 MOAC Changes--Start
88 X_ORG_ID => p_org_id);
89 --R12 MOAC Changes--End
90
91 CN_DIHY_TWO_API_PKG.Insert_Dimension
92 (x_dimension_id,
93 p_name,
94 p_base_table_id,
95 p_primary_key_id,
96 p_user_column_id,
97 --R12 MOAC Changes--Start
98 p_org_id);
99 --R12 MOAC Changes--End
100
101 -- End of API body.
102
103 -- Standard check of p_commit.
104 IF FND_API.To_Boolean( p_commit ) THEN
105 COMMIT WORK;
106 END IF;
107 -- Standard call to get message count and if count is 1, get message info.
108 FND_MSG_PUB.Count_And_Get
109 (p_count => x_msg_count ,
110 p_data => x_msg_data ,
111 p_encoded => FND_API.G_FALSE );
112 EXCEPTION
113 WHEN FND_API.G_EXC_ERROR THEN
114 ROLLBACK TO Create_Hierarchy_Type;
115 x_return_status := FND_API.G_RET_STS_ERROR ;
116 FND_MSG_PUB.count_and_get
117 (p_count => x_msg_count ,
118 p_data => x_msg_data ,
119 p_encoded => FND_API.G_FALSE );
120 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
121 ROLLBACK TO Create_Hierarchy_Type;
122 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
123 FND_MSG_PUB.count_and_get
124 (p_count => x_msg_count ,
125 p_data => x_msg_data ,
126 p_encoded => FND_API.G_FALSE );
127 WHEN OTHERS THEN
128 ROLLBACK TO Create_Hierarchy_Type;
129 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
130 IF FND_MSG_PUB.check_msg_level
131 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
132 THEN
133 FND_MSG_PUB.add_exc_msg
134 (G_PKG_NAME ,
135 l_api_name );
136 END IF;
137 FND_MSG_PUB.count_and_get
138 (p_count => x_msg_count ,
139 p_data => x_msg_data ,
140 p_encoded => FND_API.G_FALSE );
141 END Create_Hierarchy_Type;
142
143 -- Update hierarchy type (only name is updateable)
144 PROCEDURE Update_Hierarchy_Type
145 (p_api_version IN NUMBER, -- required
146 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
147 p_commit IN VARCHAR2 := FND_API.G_FALSE,
148 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
149 p_dimension_id IN CN_DIMENSIONS.DIMENSION_ID%TYPE,
150 p_name IN CN_DIMENSIONS.NAME%TYPE,
151 p_object_version_number IN OUT NOCOPY CN_DIMENSIONS.OBJECT_VERSION_NUMBER%TYPE,
152 --R12 MOAC Changes--Start
153 p_org_id IN CN_DIMENSIONS.ORG_ID%TYPE,
154 --R12 MOAC Changes--End
155 p_description IN CN_DIMENSIONS.DESCRIPTION%TYPE, -- Added for R12
156 x_return_status OUT NOCOPY VARCHAR2,
157 x_msg_count OUT NOCOPY NUMBER,
158 x_msg_data OUT NOCOPY VARCHAR2) IS
159
160 l_api_name CONSTANT VARCHAR2(30) := 'Update_Hierarchy_Type';
161 l_api_version CONSTANT NUMBER := 1.0;
162 l_count NUMBER;
163
164 cursor c is
165 select source_table_id, object_version_number from cn_dimensions
166 where dimension_id = p_dimension_id
167 and org_id = p_org_id; --R12 MOAC changes
168
169 tlinfo c%rowtype ;
170 BEGIN
171 -- Standard Start of API savepoint
172 SAVEPOINT Update_Hierarchy_Type;
173 -- Standard call to check for call compatibility.
174 IF NOT FND_API.Compatible_API_Call
175 (l_api_version ,
176 p_api_version ,
177 l_api_name ,
178 G_PKG_NAME )
179 THEN
180 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
181 END IF;
182 -- Initialize message list if p_init_msg_list is set to TRUE.
183 IF FND_API.to_Boolean( p_init_msg_list ) THEN
184 FND_MSG_PUB.initialize;
185 END IF;
186 -- Initialize API return status to success
187 x_return_status := FND_API.G_RET_STS_SUCCESS;
188
189 -- API body
190 -- call the original forms API (CNDIHY.fmb -> CNDIHY2s/b.pls)
191 -- START LOCK ROW --
192 open c;
193 fetch c into tlinfo;
194 if (c%notfound) then
195 close c;
196 fnd_message.set_name('CN', 'CN_RECORD_DELETED');
197 fnd_msg_pub.add;
198 raise fnd_api.g_exc_error;
199 end if;
200 close c;
201
202 -- validate name is unique
203 select count(1) into l_count from cn_dimensions
204 where name = p_name and dimension_id <> p_dimension_id and org_id = p_org_id; --R12 MOAC changes
205 if l_count > 0 then
206 FND_MESSAGE.SET_NAME('CN', 'CN_NAME_NOT_UNIQUE');
207 FND_MSG_PUB.ADD;
208 RAISE FND_API.G_EXC_ERROR;
209 end if;
210
211 -- we cannot update the base table... fetch existing source_table_id
212 -- also do OBJECT_VERSION_NUMBER checking
213
214
215
216 if (tlinfo.object_version_number <> p_object_version_number) then
217 fnd_message.set_name('CN', 'CN_RECORD_CHANGED');
218 fnd_msg_pub.add;
219 raise fnd_api.g_exc_error;
220 end if;
221 -- END LOCK ROW --
222
223 cn_dimensions_pkg.update_row
224 (X_DIMENSION_ID => p_dimension_id,
225 X_DESCRIPTION => p_description, --Added for R12
226 X_SOURCE_TABLE_ID => tlinfo.source_table_id,
227 X_NAME => p_name,
228 X_LAST_UPDATE_DATE => sysdate,
229 X_LAST_UPDATED_BY => fnd_global.user_id,
230 X_LAST_UPDATE_LOGIN => fnd_global.login_id,
231 --R12 MOAC Changes--Star
232 X_ORG_ID => p_org_id,
233 X_OBJECT_VERSION_NUMBER => p_object_version_number);
234 --R12 MOAC Changes--End
235
236
237
238 -- End of API body.
239
240 -- Standard check of p_commit.
241 IF FND_API.To_Boolean( p_commit ) THEN
242 COMMIT WORK;
243 END IF;
244 -- Standard call to get message count and if count is 1, get message info.
245 FND_MSG_PUB.Count_And_Get
246 (p_count => x_msg_count ,
247 p_data => x_msg_data ,
248 p_encoded => FND_API.G_FALSE );
249 EXCEPTION
250 WHEN FND_API.G_EXC_ERROR THEN
251 ROLLBACK TO Update_Hierarchy_Type;
252 x_return_status := FND_API.G_RET_STS_ERROR ;
253 FND_MSG_PUB.count_and_get
254 (p_count => x_msg_count ,
255 p_data => x_msg_data ,
256 p_encoded => FND_API.G_FALSE );
257 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
258 ROLLBACK TO Update_Hierarchy_Type;
259 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
260 FND_MSG_PUB.count_and_get
261 (p_count => x_msg_count ,
262 p_data => x_msg_data ,
263 p_encoded => FND_API.G_FALSE );
264 WHEN OTHERS THEN
265 ROLLBACK TO Update_Hierarchy_Type;
266 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
267 IF FND_MSG_PUB.check_msg_level
268 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
269 THEN
270 FND_MSG_PUB.add_exc_msg
271 (G_PKG_NAME ,
272 l_api_name );
273 END IF;
274 FND_MSG_PUB.count_and_get
275 (p_count => x_msg_count ,
276 p_data => x_msg_data ,
277 p_encoded => FND_API.G_FALSE );
278 END Update_Hierarchy_Type;
279
280 -- Delete hierarchy type
281 PROCEDURE Delete_Hierarchy_Type
282 (p_api_version IN NUMBER, -- required
283 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
284 p_commit IN VARCHAR2 := FND_API.G_FALSE,
285 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
286 p_dimension_id IN CN_DIMENSIONS.DIMENSION_ID%TYPE,
287 --R12 MOAC Changes--Start
288 p_org_id IN CN_DIMENSIONS.ORG_ID%TYPE,
289 --R12 MOAC Changes--End
290 x_return_status OUT NOCOPY VARCHAR2,
291 x_msg_count OUT NOCOPY NUMBER,
292 x_msg_data OUT NOCOPY VARCHAR2) IS
293
294 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Hierarchy_Type';
295 l_api_version CONSTANT NUMBER := 1.0;
296
297 BEGIN
298 -- Standard Start of API savepoint
299 SAVEPOINT Delete_Hierarchy_Type;
300 -- Standard call to check for call compatibility.
301 IF NOT FND_API.Compatible_API_Call
302 (l_api_version ,
303 p_api_version ,
304 l_api_name ,
305 G_PKG_NAME )
306 THEN
307 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
308 END IF;
309 -- Initialize message list if p_init_msg_list is set to TRUE.
310 IF FND_API.to_Boolean( p_init_msg_list ) THEN
311 FND_MSG_PUB.initialize;
312 END IF;
313 -- Initialize API return status to success
314 x_return_status := FND_API.G_RET_STS_SUCCESS;
315
316 -- API body
317 -- call the original forms API (CNDIHY.fmb -> CNDIHY2s/b.pls)
318 cn_dimensions_pkg.delete_row
319 (X_DIMENSION_ID => p_dimension_id,
320 --R12 MOAC Changes--Start
321 X_ORG_ID => p_org_id);
322 --R12 MOAC Changes--End
323
324 -- End of API body.
325
326 -- Standard check of p_commit.
327 IF FND_API.To_Boolean( p_commit ) THEN
328 COMMIT WORK;
329 END IF;
330 -- Standard call to get message count and if count is 1, get message info.
331 FND_MSG_PUB.Count_And_Get
332 (p_count => x_msg_count ,
333 p_data => x_msg_data ,
334 p_encoded => FND_API.G_FALSE );
335 EXCEPTION
336 WHEN FND_API.G_EXC_ERROR THEN
337 ROLLBACK TO Delete_Hierarchy_Type;
338 x_return_status := FND_API.G_RET_STS_ERROR ;
339 FND_MSG_PUB.count_and_get
340 (p_count => x_msg_count ,
341 p_data => x_msg_data ,
342 p_encoded => FND_API.G_FALSE );
343 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
344 ROLLBACK TO Delete_Hierarchy_Type;
345 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
346 FND_MSG_PUB.count_and_get
347 (p_count => x_msg_count ,
348 p_data => x_msg_data ,
349 p_encoded => FND_API.G_FALSE );
350 WHEN OTHERS THEN
351 ROLLBACK TO Delete_Hierarchy_Type;
352 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
353 IF FND_MSG_PUB.check_msg_level
354 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
355 THEN
356 FND_MSG_PUB.add_exc_msg
357 (G_PKG_NAME ,
358 l_api_name );
359 END IF;
360 FND_MSG_PUB.count_and_get
361 (p_count => x_msg_count ,
362 p_data => x_msg_data ,
363 p_encoded => FND_API.G_FALSE );
364 END Delete_Hierarchy_Type;
365
366 -- Create head hierarchy
367 PROCEDURE Create_Head_Hierarchy
368 (p_api_version IN NUMBER, -- required
369 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
370 p_commit IN VARCHAR2 := FND_API.G_FALSE,
371 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
372 p_dimension_id IN CN_HEAD_HIERARCHIES.DIMENSION_ID%TYPE,
373 p_name IN CN_HEAD_HIERARCHIES.NAME%TYPE,
374 --R12 MOAC Changes--Start
375 p_org_id IN CN_HEAD_HIERARCHIES.ORG_ID%TYPE,
376 --R12 MOAC Changes--End
377 x_return_status OUT NOCOPY VARCHAR2,
378 x_msg_count OUT NOCOPY NUMBER,
379 x_msg_data OUT NOCOPY VARCHAR2,
380 x_head_hierarchy_id OUT NOCOPY CN_HEAD_HIERARCHIES.HEAD_HIERARCHY_ID%TYPE) IS
381
382 l_api_name CONSTANT VARCHAR2(30) := 'Create_Head_Hierarchy';
383 l_api_version CONSTANT NUMBER := 1.0;
384 l_rowid ROWID;
385 l_count NUMBER;
386
387 BEGIN
388 -- Standard Start of API savepoint
389 SAVEPOINT Create_Head_Hierarchy;
390 -- Standard call to check for call compatibility.
391 IF NOT FND_API.Compatible_API_Call
392 (l_api_version ,
393 p_api_version ,
394 l_api_name ,
395 G_PKG_NAME )
396 THEN
397 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
398 END IF;
399 -- Initialize message list if p_init_msg_list is set to TRUE.
400 IF FND_API.to_Boolean( p_init_msg_list ) THEN
401 FND_MSG_PUB.initialize;
402 END IF;
403 -- Initialize API return status to success
404 x_return_status := FND_API.G_RET_STS_SUCCESS;
405
406 -- API body
407
408 -- validate name is unique
409 select count(1) into l_count from cn_head_hierarchies
410 where name = p_name and dimension_id = p_dimension_id
411 --R12 MOAC Changes--Start
412 and org_id = p_org_id ;
413 --R12 MOAC Changes--End
414
415 if l_count > 0 then
416 FND_MESSAGE.SET_NAME('CN', 'CN_NAME_NOT_UNIQUE');
417 FND_MSG_PUB.ADD;
418 RAISE FND_API.G_EXC_ERROR;
419 end if;
420
421 -- call the original forms API (CNDIHY.fmb -> CNDIHY2s/b.pls)
422 x_head_hierarchy_id := CN_HEAD_HIERARCHIES_ALL_PKG.Default_Header;
423 cn_head_hierarchies_all_pkg.insert_row
424 (X_ROWID => l_rowid,
425 X_HEAD_HIERARCHY_ID => x_head_hierarchy_id,
426 X_DIMENSION_ID => p_dimension_id,
427 X_DESCRIPTION => NULL, -- description not used
428 X_NAME => p_name,
429 X_CREATION_DATE => sysdate,
430 X_CREATED_BY => fnd_global.user_id,
431 X_LAST_UPDATE_DATE => sysdate,
432 X_LAST_UPDATED_BY => fnd_global.user_id,
433 X_LAST_UPDATE_LOGIN => fnd_global.login_id,
434 --R12 MOAC Changes--Start
435 X_ORG_ID => p_org_id);
436 --R12 MOAC Changes--End
437
438 -- End of API body.
439
440 -- Standard check of p_commit.
441 IF FND_API.To_Boolean( p_commit ) THEN
442 COMMIT WORK;
443 END IF;
444 -- Standard call to get message count and if count is 1, get message info.
445 FND_MSG_PUB.Count_And_Get
446 (p_count => x_msg_count ,
447 p_data => x_msg_data ,
448 p_encoded => FND_API.G_FALSE );
449 EXCEPTION
450 WHEN FND_API.G_EXC_ERROR THEN
451 ROLLBACK TO Create_Head_Hierarchy;
452 x_return_status := FND_API.G_RET_STS_ERROR ;
453 FND_MSG_PUB.count_and_get
454 (p_count => x_msg_count ,
455 p_data => x_msg_data ,
456 p_encoded => FND_API.G_FALSE );
457 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
458 ROLLBACK TO Create_Head_Hierarchy;
459 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
460 FND_MSG_PUB.count_and_get
461 (p_count => x_msg_count ,
462 p_data => x_msg_data ,
463 p_encoded => FND_API.G_FALSE );
464 WHEN OTHERS THEN
465 ROLLBACK TO Create_Head_Hierarchy;
466 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
467 IF FND_MSG_PUB.check_msg_level
468 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
469 THEN
470 FND_MSG_PUB.add_exc_msg
471 (G_PKG_NAME ,
472 l_api_name );
473 END IF;
474 FND_MSG_PUB.count_and_get
475 (p_count => x_msg_count ,
476 p_data => x_msg_data ,
477 p_encoded => FND_API.G_FALSE );
478 END Create_Head_Hierarchy;
479
480 -- Update head hierarchy (only name is updateable)
481 PROCEDURE Update_Head_Hierarchy
482 (p_api_version IN NUMBER, -- required
483 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
484 p_commit IN VARCHAR2 := FND_API.G_FALSE,
485 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
486 p_head_hierarchy_id IN CN_HEAD_HIERARCHIES.HEAD_HIERARCHY_ID%TYPE,
487 p_name IN CN_HEAD_HIERARCHIES.NAME%TYPE,
488 p_object_version_number IN OUT NOCOPY CN_HEAD_HIERARCHIES.OBJECT_VERSION_NUMBER%TYPE,
489 --R12 MOAC Changes--Start
490 p_org_id IN CN_HEAD_HIERARCHIES.ORG_ID%TYPE,
491 --R12 MOAC Changes--End
492 x_return_status OUT NOCOPY VARCHAR2,
493 x_msg_count OUT NOCOPY NUMBER,
494 x_msg_data OUT NOCOPY VARCHAR2) IS
495
496 l_api_name CONSTANT VARCHAR2(30) := 'Update_Head_Hierarchy';
497 l_api_version CONSTANT NUMBER := 1.0;
498 l_count NUMBER;
499
500 cursor c is
501 select object_version_number, description, dimension_id
502 from cn_head_hierarchies
503 where head_hierarchy_id = p_head_hierarchy_id
504 --R12 MOAC Changes--Start
505 and org_id = p_org_id;
506 --R12 MOAC Changes--End
507
508 tlinfo c%rowtype ;
509 BEGIN
510 -- Standard Start of API savepoint
511 SAVEPOINT Update_Head_Hierarchy;
512 -- Standard call to check for call compatibility.
513 IF NOT FND_API.Compatible_API_Call
514 (l_api_version ,
515 p_api_version ,
516 l_api_name ,
517 G_PKG_NAME )
518 THEN
519 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
520 END IF;
521 -- Initialize message list if p_init_msg_list is set to TRUE.
522 IF FND_API.to_Boolean( p_init_msg_list ) THEN
523 FND_MSG_PUB.initialize;
524 END IF;
525 -- Initialize API return status to success
526 x_return_status := FND_API.G_RET_STS_SUCCESS;
527
528 -- API body
529
530 -- call the original forms API (CNDIHY.fmb -> CNDIHY2s/b.pls)
531
532 -- START LOCK ROW --
533 open c;
534 fetch c into tlinfo;
535 if (c%notfound) then
536 close c;
537 fnd_message.set_name('CN', 'CN_RECORD_DELETED');
538 fnd_msg_pub.add;
539 raise fnd_api.g_exc_error;
540 end if;
541 close c;
542
543 if (tlinfo.object_version_number <> p_object_version_number) then
544 fnd_message.set_name('CN', 'CN_RECORD_CHANGED');
545 fnd_msg_pub.add;
546 raise fnd_api.g_exc_error;
547 end if;
548 -- END LOCK ROW --
549
550 -- validate name is unique
551 select count(1) into l_count from cn_head_hierarchies
552 where name = p_name and dimension_id = tlinfo.dimension_id
553 --R12 MOAC Changes--Start
554 and org_id = p_org_id
555 --R12 MOAC Changes--End
556 and head_hierarchy_id <> p_head_hierarchy_id;
557 if l_count > 0 then
558 FND_MESSAGE.SET_NAME('CN', 'CN_NAME_NOT_UNIQUE');
559 FND_MSG_PUB.ADD;
560 RAISE FND_API.G_EXC_ERROR;
561 end if;
562
563 cn_head_hierarchies_all_pkg.update_row
564 (X_HEAD_HIERARCHY_ID => p_head_hierarchy_id,
565 X_DIMENSION_ID => tlinfo.dimension_id, -- leave unchanged
566 X_DESCRIPTION => tlinfo.description, -- leave unchanged
567 X_NAME => p_name,
568 X_LAST_UPDATE_DATE => sysdate,
569 X_LAST_UPDATED_BY => fnd_global.user_id,
570 X_LAST_UPDATE_LOGIN => fnd_global.login_id,
571 --R12 MOAC Changes--Start
572 X_ORG_ID => p_org_id,
573 X_OBJECT_VERSION_NUMBER => p_object_version_number);
574 --R12 MOAC Changes--End
575
576 -- End of API body.
577
578 -- Standard check of p_commit.
579
580 IF FND_API.To_Boolean( p_commit ) THEN
581 COMMIT WORK;
582 END IF;
583 -- Standard call to get message count and if count is 1, get message info.
584 FND_MSG_PUB.Count_And_Get
585 (p_count => x_msg_count ,
586 p_data => x_msg_data ,
587 p_encoded => FND_API.G_FALSE );
588 EXCEPTION
589 WHEN FND_API.G_EXC_ERROR THEN
590 ROLLBACK TO Update_Head_Hierarchy;
591 x_return_status := FND_API.G_RET_STS_ERROR ;
592 FND_MSG_PUB.count_and_get
593 (p_count => x_msg_count ,
594 p_data => x_msg_data ,
595 p_encoded => FND_API.G_FALSE );
596 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
597 ROLLBACK TO Update_Head_Hierarchy;
598 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
599 FND_MSG_PUB.count_and_get
600 (p_count => x_msg_count ,
601 p_data => x_msg_data ,
602 p_encoded => FND_API.G_FALSE );
603 WHEN OTHERS THEN
604 ROLLBACK TO Update_Head_Hierarchy;
605 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
606 IF FND_MSG_PUB.check_msg_level
607 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
608 THEN
609 FND_MSG_PUB.add_exc_msg
610 (G_PKG_NAME ,
611 l_api_name );
612 END IF;
613 FND_MSG_PUB.count_and_get
614 (p_count => x_msg_count ,
615 p_data => x_msg_data ,
616 p_encoded => FND_API.G_FALSE );
617 END Update_Head_Hierarchy;
618
619 -- Delete head hierarchy
620 PROCEDURE Delete_Head_Hierarchy
621 (p_api_version IN NUMBER, -- required
622 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
623 p_commit IN VARCHAR2 := FND_API.G_FALSE,
624 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
625 p_head_hierarchy_id IN CN_HEAD_HIERARCHIES.HEAD_HIERARCHY_ID%TYPE,
626 --R12 MOAC Changes--Start
627 p_org_id IN CN_HEAD_HIERARCHIES.ORG_ID%TYPE,
628 --R12 MOAC Changes--End
629 x_return_status OUT NOCOPY VARCHAR2,
630 x_msg_count OUT NOCOPY NUMBER,
631 x_msg_data OUT NOCOPY VARCHAR2) IS
632
633 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Head_Hierarchy';
634 l_api_version CONSTANT NUMBER := 1.0;
635
636 BEGIN
637 -- Standard Start of API savepoint
638 SAVEPOINT Delete_Head_Hierarchy;
639 -- Standard call to check for call compatibility.
640 IF NOT FND_API.Compatible_API_Call
641 (l_api_version ,
642 p_api_version ,
643 l_api_name ,
644 G_PKG_NAME )
645 THEN
646 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
647 END IF;
648 -- Initialize message list if p_init_msg_list is set to TRUE.
649 IF FND_API.to_Boolean( p_init_msg_list ) THEN
650 FND_MSG_PUB.initialize;
651 END IF;
652 -- Initialize API return status to success
653 x_return_status := FND_API.G_RET_STS_SUCCESS;
654
655 -- API body
656 -- call the original forms API (CNDIHY.fmb -> CNDIHY2s/b.pls)
657 cn_head_hierarchies_all_pkg.delete_row (p_head_hierarchy_id,
658 --R12 MOAC Changes--Start
659 p_org_id);
660 --R12 MOAC Changes--End
661 -- End of API body.
662
663 -- Standard check of p_commit.
664 IF FND_API.To_Boolean( p_commit ) THEN
665 COMMIT WORK;
666 END IF;
667 -- Standard call to get message count and if count is 1, get message info.
668 FND_MSG_PUB.Count_And_Get
669 (p_count => x_msg_count ,
670 p_data => x_msg_data ,
671 p_encoded => FND_API.G_FALSE );
672 EXCEPTION
673 WHEN FND_API.G_EXC_ERROR THEN
674 ROLLBACK TO Delete_Head_Hierarchy;
675 x_return_status := FND_API.G_RET_STS_ERROR ;
676 FND_MSG_PUB.count_and_get
677 (p_count => x_msg_count ,
678 p_data => x_msg_data ,
679 p_encoded => FND_API.G_FALSE );
680 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
681 ROLLBACK TO Delete_Head_Hierarchy;
682 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
683 FND_MSG_PUB.count_and_get
684 (p_count => x_msg_count ,
685 p_data => x_msg_data ,
686 p_encoded => FND_API.G_FALSE );
687 WHEN OTHERS THEN
688 ROLLBACK TO Delete_Head_Hierarchy;
689 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
690 IF FND_MSG_PUB.check_msg_level
691 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
692 THEN
693 FND_MSG_PUB.add_exc_msg
694 (G_PKG_NAME ,
695 l_api_name );
696 END IF;
697 FND_MSG_PUB.count_and_get
698 (p_count => x_msg_count ,
699 p_data => x_msg_data ,
700 p_encoded => FND_API.G_FALSE );
701 END Delete_Head_Hierarchy;
702
703
704 -- Function to identify if the hierarchy is a revenue class hierarchy
705 FUNCTION is_revenue_hier (p_head_hierarchy_id CN_DIM_HIERARCHIES.HEADER_DIM_HIERARCHY_ID%TYPE,
706 p_org_id CN_DIM_HIERARCHIES.ORG_ID%TYPE)
707 RETURN NUMBER IS
708 l_count NUMBER := 0;
709 BEGIN
710 select count(*)
711 into l_count
712 from cn_repositories
713 where rev_class_hierarchy_id = p_head_hierarchy_id
714 --R12 MOAC Changes--Start
715 and org_id = p_org_id;
716 --R12 MOAC Changes--End
717
718 RETURN l_count;
719 END is_revenue_hier;
720
721 -- Create dimension hierarchy
722 PROCEDURE Create_Dim_Hierarchy
723 (p_api_version IN NUMBER, -- required
724 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
725 p_commit IN VARCHAR2 := FND_API.G_FALSE,
726 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
727 p_head_hierarchy_id IN CN_DIM_HIERARCHIES.HEADER_DIM_HIERARCHY_ID%TYPE,
728 p_start_date IN CN_DIM_HIERARCHIES.START_DATE%TYPE,
729 p_end_date IN CN_DIM_HIERARCHIES.END_DATE%TYPE,
730 p_root_node IN CN_DIM_HIERARCHIES.ROOT_NODE%TYPE, -- not used
731 --R12 MOAC Changes--Start
732 p_org_id IN CN_DIM_HIERARCHIES.ORG_ID%TYPE,
733 --R12 MOAC Changes--End
734 x_return_status OUT NOCOPY VARCHAR2,
735 x_msg_count OUT NOCOPY NUMBER,
736 x_msg_data OUT NOCOPY VARCHAR2,
737 x_dim_hierarchy_id OUT NOCOPY CN_DIM_HIERARCHIES.DIM_HIERARCHY_ID%TYPE) IS
738
739 l_api_name CONSTANT VARCHAR2(30) := 'Create_Dim_Hierarchy';
740 l_api_version CONSTANT NUMBER := 1.0;
741 l_count NUMBER;
742 l_name CN_HIERARCHY_NODES.NAME%TYPE;
743 x_root_node CN_DIM_HIERARCHIES.ROOT_NODE%TYPE;
744
745 BEGIN
746 -- Standard Start of API savepoint
747 SAVEPOINT Create_Dim_Hierarchy;
748 -- Standard call to check for call compatibility.
749 IF NOT FND_API.Compatible_API_Call
750 (l_api_version ,
751 p_api_version ,
752 l_api_name ,
753 G_PKG_NAME )
754 THEN
755 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
756 END IF;
757 -- Initialize message list if p_init_msg_list is set to TRUE.
758 IF FND_API.to_Boolean( p_init_msg_list ) THEN
759 FND_MSG_PUB.initialize;
760 END IF;
761 -- Initialize API return status to success
762 x_return_status := FND_API.G_RET_STS_SUCCESS;
763
764 -- API body
765
766 -- validate dates don't overlap
767 select count(1) into l_count
768 from cn_dim_hierarchies
769 where header_dim_hierarchy_id = p_head_hierarchy_id
770 --R12 MOAC Changes--Start
771 and org_id = p_org_id
772 --R12 MOAC Changes--End
773 and ( (start_date <= p_start_date and
774 nvl(end_date,p_start_date) >= p_start_date) OR
775 (start_date >= p_start_date and
776 start_date <= nvl(p_end_date, start_date)));
777 if l_count > 0 then
778 FND_MESSAGE.SET_NAME('CN', 'CN_DATE_OVERLAP');
779 FND_MSG_PUB.ADD;
780 RAISE FND_API.G_EXC_ERROR;
781 end if;
782
783 if p_end_date is not null and
784 p_end_date < p_start_date then
785 FND_MESSAGE.SET_NAME('CN', 'ALL_INVALID_PERIOD_RANGE');
786 FND_MSG_PUB.ADD;
787 RAISE FND_API.G_EXC_ERROR;
788 end if;
789
790
791 -- call new TH (original form used forms built in TH)
792 cn_dim_hierarchies_pkg.insert_row
793 (x_header_dim_hierarchy_id => p_head_hierarchy_id,
794 x_start_date => p_start_date,
795 x_end_date => p_end_date,
796 x_root_node => x_root_node,
797 x_dim_hierarchy_id => x_dim_hierarchy_id,
798 --R12 MOAC Changes--Start
799 x_org_id => p_org_id);
800 --R12 MOAC Changes--End
801
802
803 -- call to insert rows into notify log
804 IF is_revenue_hier (p_head_hierarchy_id,p_org_id) = 1 THEN
805 cn_mark_events_pkg.mark_event_rc_hier
806 (p_event_name => 'CHANGE_RC_HIER',
807 p_object_name => NULL,
808 p_dim_hierarchy_id => x_dim_hierarchy_id,
809 p_head_hierarchy_id => p_head_hierarchy_id,
810 p_start_date => NULL,
811 p_start_date_old => p_start_date,
812 p_end_date => NULL,
813 p_end_date_old => p_end_date,
814 p_org_id => p_org_id);
815 END IF;
816
817 -- End of API body.
818
819 -- Standard check of p_commit.
820 IF FND_API.To_Boolean( p_commit ) THEN
821 COMMIT WORK;
822 END IF;
823 -- Standard call to get message count and if count is 1, get message info.
824 FND_MSG_PUB.Count_And_Get
825 (p_count => x_msg_count ,
826 p_data => x_msg_data ,
827 p_encoded => FND_API.G_FALSE );
828 EXCEPTION
829 WHEN FND_API.G_EXC_ERROR THEN
830 ROLLBACK TO Create_Dim_Hierarchy;
831 x_return_status := FND_API.G_RET_STS_ERROR ;
832 FND_MSG_PUB.count_and_get
833 (p_count => x_msg_count ,
834 p_data => x_msg_data ,
835 p_encoded => FND_API.G_FALSE );
836 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
837 ROLLBACK TO Create_Dim_Hierarchy;
838 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
839 FND_MSG_PUB.count_and_get
840 (p_count => x_msg_count ,
841 p_data => x_msg_data ,
842 p_encoded => FND_API.G_FALSE );
843 WHEN OTHERS THEN
844 ROLLBACK TO Create_Dim_Hierarchy;
845 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
846 IF FND_MSG_PUB.check_msg_level
847 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
848 THEN
849 FND_MSG_PUB.add_exc_msg
850 (G_PKG_NAME ,
851 l_api_name );
852 END IF;
853 FND_MSG_PUB.count_and_get
854 (p_count => x_msg_count ,
855 p_data => x_msg_data ,
856 p_encoded => FND_API.G_FALSE );
857 END Create_Dim_Hierarchy;
858
859 -- Update dimension hierarchy (only dates are updateable)
860 PROCEDURE Update_Dim_Hierarchy
861 (p_api_version IN NUMBER, -- required
862 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
863 p_commit IN VARCHAR2 := FND_API.G_FALSE,
864 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
865 p_dim_hierarchy_id IN CN_DIM_HIERARCHIES.DIM_HIERARCHY_ID%TYPE,
866 p_start_date IN CN_DIM_HIERARCHIES.START_DATE%TYPE,
867 p_end_date IN CN_DIM_HIERARCHIES.END_DATE%TYPE,
868 p_object_version_number IN OUT NOCOPY CN_DIM_HIERARCHIES.OBJECT_VERSION_NUMBER%TYPE,
869 --R12 MOAC Changes--Start
870 p_org_id IN CN_DIM_HIERARCHIES.ORG_ID%TYPE,
871 --R12 MOAC Changes--End
872 x_return_status OUT NOCOPY VARCHAR2,
873 x_msg_count OUT NOCOPY NUMBER,
874 x_msg_data OUT NOCOPY VARCHAR2) IS
875
876 l_api_name CONSTANT VARCHAR2(30) := 'Update_Dim_Hierarchy';
877 l_api_version CONSTANT NUMBER := 1.0;
878 l_count NUMBER;
879 l_head_hierarchy_id CN_DIM_HIERARCHIES.HEADER_DIM_HIERARCHY_ID%TYPE;
880 l_root_node CN_DIM_HIERARCHIES.ROOT_NODE%TYPE;
881 l_old_start_date CN_DIM_HIERARCHIES.START_DATE%TYPE;
882 l_old_end_date CN_DIM_HIERARCHIES.END_DATE%TYPE;
883 BEGIN
884 -- Standard Start of API savepoint
885 SAVEPOINT Update_Dim_Hierarchy;
886 -- Standard call to check for call compatibility.
887 IF NOT FND_API.Compatible_API_Call
888 (l_api_version ,
889 p_api_version ,
890 l_api_name ,
891 G_PKG_NAME )
892 THEN
893 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
894 END IF;
895 -- Initialize message list if p_init_msg_list is set to TRUE.
896 IF FND_API.to_Boolean( p_init_msg_list ) THEN
897 FND_MSG_PUB.initialize;
898 END IF;
899 -- Initialize API return status to success
900 x_return_status := FND_API.G_RET_STS_SUCCESS;
901
902 -- API body
903 -- validate dates don't overlap with another dim hierarchy under the
904 -- same head hierarchy
905
906 -- get head hierarchy ID and root node
907 select header_dim_hierarchy_id, root_node
908 into l_head_hierarchy_id, l_root_node
909 from cn_dim_hierarchies
910 where dim_hierarchy_id = p_dim_hierarchy_id
911 --R12 MOAC Changes--Start
912 and org_id = p_org_id;
913 --R12 MOAC Changes--End
914
915 if p_end_date is not null and
916 p_end_date < p_start_date then
917 FND_MESSAGE.SET_NAME('CN', 'ALL_INVALID_PERIOD_RANGE');
918 FND_MSG_PUB.ADD;
919 RAISE FND_API.G_EXC_ERROR;
920 end if;
921
922 select count(1) into l_count
923 from cn_dim_hierarchies
924 where header_dim_hierarchy_id = l_head_hierarchy_id
925 --R12 MOAC Changes--Start
926 and org_id = p_org_id
927 --R12 MOAC Changes--End
928 and dim_hierarchy_id <> p_dim_hierarchy_id
929 and ( (start_date <= p_start_date and
930 nvl(end_date,p_start_date) >= p_start_date) OR
931 (start_date >= p_start_date and
932 start_date <= nvl(p_end_date, start_date)));
933 if l_count > 0 then
934 FND_MESSAGE.SET_NAME('CN', 'CN_DATE_OVERLAP');
935 FND_MSG_PUB.ADD;
936 RAISE FND_API.G_EXC_ERROR;
937 end if;
938
939
940 -- call to insert rows into notify log
941 IF is_revenue_hier (l_head_hierarchy_id,p_org_id) = 1 THEN -- R12 MOAC changes
942 select start_date, end_date
943 into l_old_start_date, l_old_end_date
944 from cn_dim_hierarchies
945 where dim_hierarchy_id = p_dim_hierarchy_id
946 --R12 MOAC Changes--Start
947 and org_id = p_org_id;
948 --R12 MOAC Changes--End
949
950 cn_mark_events_pkg.mark_event_rc_hier
951 (p_event_name => 'CHANGE_RC_HIER_PERIOD',
952 p_object_name => NULL,
953 p_dim_hierarchy_id => p_dim_hierarchy_id,
954 p_head_hierarchy_id => l_head_hierarchy_id,
955 p_start_date => p_start_date,
956 p_start_date_old => l_old_start_date,
957 p_end_date => p_end_date,
958 p_end_date_old => l_old_end_date,
959 p_org_id => p_org_id);
960 END IF;
961
962
963
964 cn_dim_hierarchies_pkg.update_row
965 (x_dim_hierarchy_id => p_dim_hierarchy_id,
966 x_header_dim_hierarchy_id => l_head_hierarchy_id,
967 x_start_date => p_start_date,
968 x_end_date => p_end_date,
969 x_root_node => l_root_node,
970 x_object_version_number => p_object_version_number,
971 --R12 MOAC Changes--Start
972 x_org_id => p_org_id);
973 --R12 MOAC Changes--End
974
975 -- End of API body.
976
977 --Increase version
978 p_object_version_number := p_object_version_number + 1;
979
980 -- Standard check of p_commit.
981 IF FND_API.To_Boolean( p_commit ) THEN
982 COMMIT WORK;
983 END IF;
984 -- Standard call to get message count and if count is 1, get message info.
985 FND_MSG_PUB.Count_And_Get
986 (p_count => x_msg_count ,
987 p_data => x_msg_data ,
988 p_encoded => FND_API.G_FALSE );
989 EXCEPTION
990 WHEN FND_API.G_EXC_ERROR THEN
991 ROLLBACK TO Update_Dim_Hierarchy;
992 x_return_status := FND_API.G_RET_STS_ERROR ;
993 FND_MSG_PUB.count_and_get
994 (p_count => x_msg_count ,
995 p_data => x_msg_data ,
996 p_encoded => FND_API.G_FALSE );
997 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
998 ROLLBACK TO Update_Dim_Hierarchy;
999 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1000 FND_MSG_PUB.count_and_get
1001 (p_count => x_msg_count ,
1002 p_data => x_msg_data ,
1003 p_encoded => FND_API.G_FALSE );
1004 WHEN OTHERS THEN
1005 ROLLBACK TO Update_Dim_Hierarchy;
1006 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1007 IF FND_MSG_PUB.check_msg_level
1008 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1009 THEN
1010 FND_MSG_PUB.add_exc_msg
1011 (G_PKG_NAME ,
1012 l_api_name );
1013 END IF;
1014 FND_MSG_PUB.count_and_get
1015 (p_count => x_msg_count ,
1016 p_data => x_msg_data ,
1017 p_encoded => FND_API.G_FALSE );
1018 END Update_Dim_Hierarchy;
1019
1020 -- Delete dimension hierarchy
1021 PROCEDURE Delete_Dim_Hierarchy
1022 (p_api_version IN NUMBER, -- required
1023 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1024 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1025 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1026 p_dim_hierarchy_id IN CN_DIM_HIERARCHIES.DIM_HIERARCHY_ID%TYPE,
1027 --R12 MOAC Changes--Start
1028 p_org_id IN CN_DIM_HIERARCHIES.ORG_ID%TYPE,
1029 --R12 MOAC Changes--End
1030 x_return_status OUT NOCOPY VARCHAR2,
1031 x_msg_count OUT NOCOPY NUMBER,
1032 x_msg_data OUT NOCOPY VARCHAR2) IS
1033
1034 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Dim_Hierarchy';
1035 l_api_version CONSTANT NUMBER := 1.0;
1036 l_head_hierarchy_id CN_DIM_HIERARCHIES.HEADER_DIM_HIERARCHY_ID%TYPE;
1037 l_start_date CN_DIM_HIERARCHIES.START_DATE%TYPE;
1038 l_end_date CN_DIM_HIERARCHIES.END_DATE%TYPE;
1039
1040 BEGIN
1041 -- Standard Start of API savepoint
1042 SAVEPOINT Delete_Dim_Hierarchy;
1043 -- Standard call to check for call compatibility.
1044 IF NOT FND_API.Compatible_API_Call
1045 (l_api_version ,
1046 p_api_version ,
1047 l_api_name ,
1048 G_PKG_NAME )
1049 THEN
1050 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1051 END IF;
1052 -- Initialize message list if p_init_msg_list is set to TRUE.
1053 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1054 FND_MSG_PUB.initialize;
1055 END IF;
1056 -- Initialize API return status to success
1057 x_return_status := FND_API.G_RET_STS_SUCCESS;
1058
1059 -- API body
1060
1061 -- get head hierarchy ID, start date and end date
1062 select header_dim_hierarchy_id, start_date, end_date
1063 into l_head_hierarchy_id, l_start_date, l_end_date
1064 from cn_dim_hierarchies
1065 where dim_hierarchy_id = p_dim_hierarchy_id
1066 --R12 MOAC Changes--Start
1067 and org_id = p_org_id;
1068 --R12 MOAC Changes--End
1069
1070 -- call to insert rows into notify log
1071 IF is_revenue_hier (l_head_hierarchy_id,p_org_id) = 1 THEN
1072 cn_mark_events_pkg.mark_event_rc_hier
1073 (p_event_name => 'CHANGE_RC_HIER',
1074 p_object_name => NULL,
1075 p_dim_hierarchy_id => p_dim_hierarchy_id,
1076 p_head_hierarchy_id => l_head_hierarchy_id,
1077 p_start_date => NULL,
1078 p_start_date_old => l_start_date,
1079 p_end_date => NULL,
1080 p_end_date_old => l_end_date,
1081 p_org_id => p_org_id);
1082 END IF;
1083
1084
1085 -- call the original forms API (CNDIHY.fmb -> CNDIHY2s/b.pls)
1086 cn_dim_hierarchies_pkg.delete_row (p_dim_hierarchy_id);--,p_org_id);
1087
1088 -- End of API body.
1089
1090 -- Standard check of p_commit.
1091 IF FND_API.To_Boolean( p_commit ) THEN
1092 COMMIT WORK;
1093 END IF;
1094 -- Standard call to get message count and if count is 1, get message info.
1095 FND_MSG_PUB.Count_And_Get
1096 (p_count => x_msg_count ,
1097 p_data => x_msg_data ,
1098 p_encoded => FND_API.G_FALSE );
1099 EXCEPTION
1100 WHEN FND_API.G_EXC_ERROR THEN
1101 ROLLBACK TO Delete_Dim_Hierarchy;
1102 x_return_status := FND_API.G_RET_STS_ERROR ;
1103 FND_MSG_PUB.count_and_get
1104 (p_count => x_msg_count ,
1105 p_data => x_msg_data ,
1106 p_encoded => FND_API.G_FALSE );
1107 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1108 ROLLBACK TO Delete_Dim_Hierarchy;
1109 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1110 FND_MSG_PUB.count_and_get
1111 (p_count => x_msg_count ,
1112 p_data => x_msg_data ,
1113 p_encoded => FND_API.G_FALSE );
1114 WHEN OTHERS THEN
1115 ROLLBACK TO Delete_Dim_Hierarchy;
1116 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1117 IF FND_MSG_PUB.check_msg_level
1118 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1119 THEN
1120 FND_MSG_PUB.add_exc_msg
1121 (G_PKG_NAME ,
1122 l_api_name );
1123 END IF;
1124 FND_MSG_PUB.count_and_get
1125 (p_count => x_msg_count ,
1126 p_data => x_msg_data ,
1127 p_encoded => FND_API.G_FALSE );
1128 END Delete_Dim_Hierarchy;
1129
1130 -- Create edge
1131 PROCEDURE Create_Edge
1132 (p_api_version IN NUMBER, -- required
1133 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1134 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1135 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1136 p_dim_hierarchy_id IN CN_HIERARCHY_EDGES.DIM_HIERARCHY_ID%TYPE,
1137 p_parent_value_id IN CN_HIERARCHY_EDGES.PARENT_VALUE_ID%TYPE,
1138 p_name IN CN_HIERARCHY_NODES.NAME%TYPE,
1139 p_external_id IN CN_HIERARCHY_NODES.EXTERNAL_ID%TYPE,
1140 --R12 MOAC Changes--Start
1141 p_org_id IN CN_HIERARCHY_EDGES.ORG_ID%TYPE,
1142 --R12 MOAC Changes--End
1143 x_return_status OUT NOCOPY VARCHAR2,
1144 x_msg_count OUT NOCOPY NUMBER,
1145 x_msg_data OUT NOCOPY VARCHAR2,
1146 x_value_id OUT NOCOPY CN_HIERARCHY_EDGES.VALUE_ID%TYPE) IS
1147
1148 l_api_name CONSTANT VARCHAR2(30) := 'Create_Edge';
1149 l_api_version CONSTANT NUMBER := 1.0;
1150 l_max_date CONSTANT DATE := to_date('31-12-9999','DD-MM-YYYY');
1151 l_start_date DATE;
1152 l_end_date DATE;
1153 l_par_name VARCHAR2(30);
1154 l_head_hier_id CN_DIM_HIERARCHIES.HEADER_DIM_HIERARCHY_ID%TYPE;
1155
1156 CURSOR dup_rev_classes is
1157 select h.start_date, h.end_date, r.name
1158 from cn_quota_rules r1, cn_quota_rules r2, cn_dim_explosion d,
1159 cn_dim_hierarchies h, cn_quotas q, cn_revenue_classes r
1160 where r1.revenue_class_id = p_external_id
1161 and r2.revenue_class_id = d.ancestor_external_id
1162 AND r.revenue_class_id = d.ancestor_external_id
1163 and d.value_id = p_parent_value_id
1164 and d.dim_hierarchy_id = h.dim_hierarchy_id
1165 and h.dim_hierarchy_id = p_dim_hierarchy_id
1166 and r1.quota_id = r2.quota_id
1167 and r1.quota_id = q.quota_id
1168 --R12 MOAC Changes--Start
1169 and r1.org_id = r2.org_id
1170 and r2.org_id = d.org_id
1171 and d.org_id = h.org_id
1172 and h.org_id = q.org_id
1173 and q.org_id = r1.org_id
1174 and r1.org_id = p_org_id
1175 and r.org_id = r1.org_id
1176 --R12 MOAC Changes--End
1177 and greatest(q.start_date, h.start_date) <=
1178 least(nvl(q.end_date,l_max_date), nvl(h.end_date,l_max_date));
1179
1180 BEGIN
1181 -- Standard Start of API savepoint
1182 SAVEPOINT Create_Edge;
1183 -- Standard call to check for call compatibility.
1184 IF NOT FND_API.Compatible_API_Call
1185 (l_api_version ,
1186 p_api_version ,
1187 l_api_name ,
1188 G_PKG_NAME )
1189 THEN
1190 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1191 END IF;
1192 -- Initialize message list if p_init_msg_list is set to TRUE.
1193 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1194 FND_MSG_PUB.initialize;
1195 END IF;
1196 -- Initialize API return status to success
1197 x_return_status := FND_API.G_RET_STS_SUCCESS;
1198
1199 -- API body
1200 -- call the original forms API (CNDIHY.fmb -> CNDIHY2s/b.pls)
1201 BEGIN
1202 -- see if we're dealing with the revenue classes
1203 SELECT header_dim_hierarchy_id INTO l_head_hier_id
1204 FROM cn_dim_hierarchies
1205 WHERE dim_hierarchy_id = p_dim_hierarchy_id
1206 --R12 MOAC Changes--Start
1207 and org_id = p_org_id;
1208 --R12 MOAC Changes--End
1209
1210 IF cn_dim_hierarchies_pvt.is_revenue_hier(l_head_hier_id,p_org_id) > 0 THEN
1211 OPEN dup_rev_classes;
1212 FETCH dup_rev_classes INTO l_start_date, l_end_date, l_par_name;
1213
1214 IF dup_rev_classes%found THEN
1215 CLOSE dup_rev_classes;
1216 FND_MESSAGE.SET_NAME('CN', 'REV_CLASS_HIER_CHECK');
1217 fnd_message.set_token('REV_CLASS_NAME_PARENT', l_par_name);
1218 fnd_message.set_token('REV_CLASS_NAME_CHILD', p_name);
1219 fnd_message.set_token('PERIODS', l_start_date || ' - ' || l_end_date);
1220 FND_MSG_PUB.ADD;
1221 RAISE FND_API.G_EXC_ERROR;
1222 END IF;
1223 CLOSE dup_rev_classes;
1224 END IF;
1225
1226 CN_DIHY_TWO_API_PKG.Insert_Edge
1227 (X_name => p_name,
1228 X_dim_hierarchy_id => p_dim_hierarchy_id,
1229 X_value_id => x_value_id,
1230 X_parent_value_id => p_parent_value_id,
1231 X_external_id => p_external_id,
1232 X_hierarchy_api_id => NULL, -- not used
1233 --R12 MOAC Changes--Start
1234 x_org_id => p_org_id);
1235 --R12 MOAC Changes--End
1236 EXCEPTION
1237 WHEN DUP_VAL_ON_INDEX THEN
1238 FND_MESSAGE.SET_NAME('CN', 'HIER_NO_DIAMONDS');
1239 FND_MSG_PUB.ADD;
1240 RAISE FND_API.G_EXC_ERROR;
1241 END;
1242
1243 -- End of API body.
1244
1245 -- Standard check of p_commit.
1246 IF FND_API.To_Boolean( p_commit ) THEN
1247 COMMIT WORK;
1248 END IF;
1249 -- Standard call to get message count and if count is 1, get message info.
1250 FND_MSG_PUB.Count_And_Get
1251 (p_count => x_msg_count ,
1252 p_data => x_msg_data ,
1253 p_encoded => FND_API.G_FALSE );
1254 EXCEPTION
1255 WHEN FND_API.G_EXC_ERROR THEN
1256 ROLLBACK TO Create_Edge;
1257 x_return_status := FND_API.G_RET_STS_ERROR ;
1258 FND_MSG_PUB.count_and_get
1259 (p_count => x_msg_count ,
1260 p_data => x_msg_data ,
1261 p_encoded => FND_API.G_FALSE );
1262 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1263 ROLLBACK TO Create_Edge;
1264 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1265 FND_MSG_PUB.count_and_get
1266 (p_count => x_msg_count ,
1267 p_data => x_msg_data ,
1268 p_encoded => FND_API.G_FALSE );
1269 WHEN OTHERS THEN
1270 ROLLBACK TO Create_Edge;
1271 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1272 IF FND_MSG_PUB.check_msg_level
1273 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1274 THEN
1275 FND_MSG_PUB.add_exc_msg
1276 (G_PKG_NAME ,
1277 l_api_name );
1278 END IF;
1279 FND_MSG_PUB.count_and_get
1280 (p_count => x_msg_count ,
1281 p_data => x_msg_data ,
1282 p_encoded => FND_API.G_FALSE );
1283 END Create_Edge;
1284
1285 -- Delete edge
1286 PROCEDURE Delete_Edge
1287 (p_api_version IN NUMBER, -- required
1288 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1289 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1290 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1291 p_dim_hierarchy_id IN CN_HIERARCHY_EDGES.DIM_HIERARCHY_ID%TYPE,
1292 p_value_id IN CN_HIERARCHY_EDGES.VALUE_ID%TYPE,
1293 p_parent_value_id IN CN_HIERARCHY_EDGES.PARENT_VALUE_ID%TYPE,
1294 --R12 MOAC Changes--Start
1295 p_org_id IN CN_HIERARCHY_EDGES.ORG_ID%TYPE,
1296 --R12 MOAC Changes--End
1297 x_return_status OUT NOCOPY VARCHAR2,
1298 x_msg_count OUT NOCOPY NUMBER,
1299 x_msg_data OUT NOCOPY VARCHAR2) IS
1300
1301 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Edge';
1302 l_api_version CONSTANT NUMBER := 1.0;
1303 l_ext_id CN_HIERARCHY_NODES.EXTERNAL_ID%TYPE;
1304 l_count NUMBER;
1305
1306 BEGIN
1307 -- Standard Start of API savepoint
1308 SAVEPOINT Delete_Edge;
1309 -- Standard call to check for call compatibility.
1310 IF NOT FND_API.Compatible_API_Call
1311 (l_api_version ,
1312 p_api_version ,
1313 l_api_name ,
1314 G_PKG_NAME )
1315 THEN
1316 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1317 END IF;
1318 -- Initialize message list if p_init_msg_list is set to TRUE.
1319 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1320 FND_MSG_PUB.initialize;
1321 END IF;
1322 -- Initialize API return status to success
1323 x_return_status := FND_API.G_RET_STS_SUCCESS;
1324
1325 -- API body
1326
1327 -- make sure the root is not being deleted
1328 if p_parent_value_id is null then
1329 -- see if parent value is a BASE NODE... if so it has no external ID
1330 select external_id into l_ext_id
1331 from cn_hierarchy_nodes
1332 where value_id = p_value_id
1333 --R12 MOAC Changes--Start
1334 and org_id = p_org_id;
1335 --R12 MOAC Changes--End
1336
1337 if l_ext_id is null then
1338 FND_MESSAGE.SET_NAME('CN', 'HIER_NO_DELETE_ROOT');
1339 FND_MSG_PUB.ADD;
1340 RAISE FND_API.G_EXC_ERROR;
1341 end if;
1342 end if;
1343
1344 -- make sure the edge still exists
1345 select count(1) into l_count
1346 from cn_hierarchy_edges
1347 where value_id = p_value_id
1348 and nvl(parent_value_id, -99) = nvl(p_parent_value_id, -99)
1349 and dim_hierarchy_id = p_dim_hierarchy_id
1350 --R12 MOAC Changes--Start
1351 and org_id = p_org_id;
1352 --R12 MOAC Changes--End
1353
1354 if l_count = 0 then
1355 fnd_message.set_name('CN', 'CN_RECORD_DELETED');
1356 fnd_msg_pub.add;
1357 raise fnd_api.g_exc_error;
1358 end if;
1359
1360 CN_DIHY_TWO_API_PKG.Cascade_Delete
1361 (X_value_id => p_value_id,
1362 X_parent_value_id => p_parent_value_id,
1363 X_dim_hierarchy_id => p_dim_hierarchy_id,
1364 X_org_id => p_org_id);
1365
1366 -- End of API body.
1367
1368 -- Standard check of p_commit.
1369 IF FND_API.To_Boolean( p_commit ) THEN
1370 COMMIT WORK;
1371 END IF;
1372 -- Standard call to get message count and if count is 1, get message info.
1373 FND_MSG_PUB.Count_And_Get
1374 (p_count => x_msg_count ,
1375 p_data => x_msg_data ,
1376 p_encoded => FND_API.G_FALSE );
1377 EXCEPTION
1378 WHEN FND_API.G_EXC_ERROR THEN
1379 ROLLBACK TO Delete_Edge;
1380 x_return_status := FND_API.G_RET_STS_ERROR ;
1381 FND_MSG_PUB.count_and_get
1382 (p_count => x_msg_count ,
1383 p_data => x_msg_data ,
1384 p_encoded => FND_API.G_FALSE );
1385 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1386 ROLLBACK TO Delete_Edge;
1387 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1388 FND_MSG_PUB.count_and_get
1389 (p_count => x_msg_count ,
1390 p_data => x_msg_data ,
1391 p_encoded => FND_API.G_FALSE );
1392 WHEN OTHERS THEN
1393 ROLLBACK TO Delete_Edge;
1394 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1395 IF FND_MSG_PUB.check_msg_level
1396 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1397 THEN
1398 FND_MSG_PUB.add_exc_msg
1399 (G_PKG_NAME ,
1400 l_api_name );
1401 END IF;
1402 FND_MSG_PUB.count_and_get
1403 (p_count => x_msg_count ,
1404 p_data => x_msg_data ,
1405 p_encoded => FND_API.G_FALSE );
1406 END Delete_Edge;
1407
1408 -- export
1409 PROCEDURE Export
1410 (errbuf OUT NOCOPY VARCHAR2,
1411 retcode OUT NOCOPY VARCHAR2,
1412 p_imp_header_id IN NUMBER,
1413 --R12 MOAC Changes--Start
1414 p_org_id IN NUMBER) IS
1415 --R12 MOAC Changes--End
1416
1417 l_api_name CONSTANT VARCHAR2(30) := 'Export';
1418 l_process_audit_id cn_process_audits.process_audit_id%TYPE;
1419 l_return_status varchar2(1);
1420 l_msg_data varchar2(2000);
1421 l_msg_count number;
1422 l_col_names CN_IMPORT_PVT.char_data_set_type;
1423 l_data CN_IMPORT_PVT.char_data_set_type;
1424 l_rowcount number := 0;
1425 l_rec_num number := 0;
1426 l_message VARCHAR2(2000);
1427 l_name VARCHAR2(30);
1428 l_type VARCHAR2(30);
1429 l_view_name VARCHAR2(30);
1430 my_message VARCHAR2(2000);
1431 err_num NUMBER;
1432
1433 -- this is a workaround since you cannot declare arrays of a
1434 -- type declared remotely
1435 type vt is table of varchar2(30);
1436 l_col_names_tmp vt := vt('RECORD_NUM', 'HIERARCHY_TYPE', 'BASE_TABLE_NAME',
1437 'PRIMARY_KEY','HIERARCHY_VALUE','HIERARCHY_NAME',
1438 'START_DATE', 'END_DATE', 'DEFAULT_NODE_FLAG',
1439 'PARENT_NODE_NAME','NODE_NAME','LEVEL_NUM');
1440
1441 cursor get_dim_hierarchies is
1442 select D.NAME HIERARCHY_TYPE,
1443 O2.NAME BASE_TABLE_NAME,
1444 O1.NAME PRIMARY_KEY,
1445 O3.NAME HIERARCHY_VALUE,
1446 H.NAME HIERARCHY_NAME,
1447 M.START_DATE START_DATE,
1448 M.END_DATE END_DATE,
1449 m.dim_hierarchy_id
1450 FROM cn_dimensions d, cn_objects o1, cn_objects o2, cn_objects o3,
1451 cn_head_hierarchies h, cn_dim_hierarchies m
1452 WHERE o1.dimension_id = d.dimension_id
1453 AND o1.table_id = d.source_table_id
1454 AND o1.object_type = 'COL'
1455 AND o1.primary_key = 'Y'
1456 AND o2.object_id = o1.table_id
1457 AND o3.table_id = o1.table_id
1458 AND o3.object_type = 'COL'
1459 AND o3.user_column_name = 'Y'
1460 AND d.dimension_id = h.dimension_id
1461 AND h.head_hierarchy_id = m.header_dim_hierarchy_id
1462 AND d.org_id = p_org_id
1463 AND o1.org_id = p_org_id
1464 AND o2.org_id = p_org_id
1465 AND o3.org_id = p_org_id
1466 AND h.org_id = p_org_id
1467 AND m.org_id = p_org_id
1468 ORDER BY 1, 5, 6;
1469
1470 cursor traverse_tree (l_dim_hierarchy_id in number) is
1471 select decode(child.external_id, null, 'Y', 'N') DEFAULT_NODE_FLAG,
1472 parent.name PARENT_NODE_NAME,
1473 child.name NODE_NAME,
1474 e.depth LEVEL_NUM
1475 from cn_hierarchy_nodes child, cn_hierarchy_nodes parent,
1476 (select value_id, parent_value_id, level depth, dim_hierarchy_id
1477 from cn_hierarchy_edges
1478 start with (parent_value_id is null and
1479 dim_hierarchy_id = l_dim_hierarchy_id)
1480 connect by parent_value_id = prior value_id
1481 and dim_hierarchy_id = l_dim_hierarchy_id) e
1482 where child.value_id = e.value_id
1483 and child.dim_hierarchy_id = e.dim_hierarchy_id
1484 and parent.value_id(+) = e.parent_value_id
1485 and parent.dim_hierarchy_id(+) = e.dim_hierarchy_id;
1486
1487 BEGIN
1488 retcode := 0 ;
1489 mo_global.init('CN');
1490 -- Get imp_header info
1491 SELECT h.name, h.import_type_code, t.view_name
1492 INTO l_name, l_type, l_view_name
1493 FROM cn_imp_headers h, cn_import_types t
1494 WHERE h.imp_header_id = p_imp_header_id
1495 AND t.import_type_code = h.import_type_code;
1496
1497 -- open process audit batch
1498 cn_message_pkg.begin_batch
1499 ( x_process_type => l_type,
1500 x_parent_proc_audit_id => p_imp_header_id ,
1501 x_process_audit_id => l_process_audit_id,
1502 x_request_id => null,
1503 --R12 MOAC Changes--Start
1504 p_org_id => p_org_id);
1505 --R12 MOAC Changes--End
1506
1507 cn_message_pkg.write
1508 (p_message_text => 'CN_EXP_HIERARCHY: Start Transfer Data. imp_header_id = ' || To_char(p_imp_header_id),
1509 p_message_type => 'MILESTONE');
1510
1511 -- API call here
1512 -- get column names
1513 for i in 1..l_col_names_tmp.count loop
1514 l_col_names(i) := l_col_names_tmp(i);
1515 end loop;
1516
1517 -- we have to get the rowcount first - since the data must be applied
1518 -- sequentially by column... indexes are like
1519 -- 1 n+1 ... 11n+1 (there are 12 columns)
1520 -- 2 n+2 ... 11n+2
1521 -- n 2n ... 12n
1522 for dim in get_dim_hierarchies loop
1523 for edge in traverse_tree(dim.dim_hierarchy_id) loop
1524 l_rowcount := l_rowcount + 1;
1525 end loop;
1526 end loop;
1527
1528 -- now populate the data
1529 for dim in get_dim_hierarchies loop
1530 cn_message_pkg.write
1531 (p_message_text => 'Downloading dim hierarchy ' ||
1532 dim.dim_hierarchy_id,
1533 p_message_type => 'DEBUG');
1534 for edge in traverse_tree(dim.dim_hierarchy_id) loop
1535 l_rec_num := l_rec_num + 1;
1536 l_data(l_rowcount * 0 + l_rec_num) := l_rec_num;
1537 l_data(l_rowcount * 1 + l_rec_num) := dim.hierarchy_type;
1538 l_data(l_rowcount * 2 + l_rec_num) := dim.base_table_name;
1539 l_data(l_rowcount * 3 + l_rec_num) := dim.primary_key;
1540 l_data(l_rowcount * 4 + l_rec_num) := dim.hierarchy_value;
1541 l_data(l_rowcount * 5 + l_rec_num) := dim.hierarchy_name;
1542 l_data(l_rowcount * 6 + l_rec_num) := dim.start_date;
1543 l_data(l_rowcount * 7 + l_rec_num) := dim.end_date;
1544 l_data(l_rowcount * 8 + l_rec_num) := edge.default_node_flag;
1545 l_data(l_rowcount * 9 + l_rec_num) := edge.parent_node_name;
1546 l_data(l_rowcount * 10 + l_rec_num) := edge.node_name;
1547 l_data(l_rowcount * 11 + l_rec_num) := edge.level_num;
1548 end loop;
1549 end loop;
1550
1551 cn_import_client_pvt.Insert_Data
1552 (p_api_version => 1.0,
1553 p_imp_header_id => p_imp_header_id,
1554 p_import_type_code => l_type,
1555 p_table_name => l_view_name,
1556 p_col_names => l_col_names,
1557 p_data => l_data,
1558 p_row_count => l_rowcount,
1559 x_return_status => l_return_status,
1560 x_msg_count => l_msg_count,
1561 x_msg_data => l_msg_data);
1562
1563 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1564 CN_IMPORT_PVT.update_imp_headers
1565 (p_imp_header_id => p_imp_header_id,
1566 p_status_code => 'FAIL',
1567 p_failed_row => l_rowcount);
1568
1569 cn_message_pkg.write
1570 (p_message_text => 'Export threw exception : rts sts ' ||
1571 l_return_status,
1572 p_message_type => 'ERROR');
1573
1574 my_message := fnd_msg_pub.get(p_encoded => FND_API.g_false);
1575 while (my_message is not null) loop
1576 l_message := l_message || my_message || '; ';
1577 my_message := fnd_msg_pub.get(p_encoded => FND_API.g_false);
1578 end loop;
1579
1580 cn_message_pkg.write
1581 (p_message_text => l_message,
1582 p_message_type => 'ERROR');
1583
1584 retcode := 2;
1585 errbuf := l_message;
1586 ELSE
1587 -- normal completion
1588 CN_IMPORT_PVT.update_imp_headers
1589 (p_imp_header_id => p_imp_header_id,
1590 p_status_code => 'COMPLETE',
1591 p_processed_row => l_rowcount,
1592 p_staged_row => l_rowcount,
1593 p_failed_row => 0);
1594
1595 -- set cn_imp_lines records status = 'COMPLETE'
1596 UPDATE cn_imp_lines
1597 SET status_code = 'COMPLETE'
1598 WHERE imp_header_id = p_imp_header_id
1599 ;
1600
1601 cn_message_pkg.write
1602 (p_message_text => 'CN_EXP_HIERARCHY: End Transfer Data. imp_header_id = ' || To_char(p_imp_header_id),
1603 p_message_type => 'MILESTONE');
1604
1605 END IF;
1606
1607 -- close process batch
1608 cn_message_pkg.end_batch(l_process_audit_id);
1609
1610 -- Commit all imports
1611 COMMIT;
1612
1613 EXCEPTION
1614 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1615 retcode := 2 ;
1616 cn_message_pkg.end_batch(l_process_audit_id);
1617 FND_MSG_PUB.count_and_get
1618 (p_count => l_msg_count ,
1619 p_data => errbuf ,
1620 p_encoded => FND_API.G_FALSE
1621 );
1622
1623 WHEN OTHERS THEN
1624 err_num := SQLCODE;
1625 IF err_num = -6501 THEN
1626 retcode := 2 ;
1627 errbuf := fnd_program.message;
1628 ELSE
1629 retcode := 2 ;
1630 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1631 THEN
1632 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1633 END IF;
1634 FND_MSG_PUB.count_and_get
1635 (p_count => l_msg_count ,
1636 p_data => errbuf ,
1637 p_encoded => FND_API.G_FALSE
1638 );
1639 END IF;
1640
1641 cn_message_pkg.set_error(l_api_name,errbuf);
1642 cn_message_pkg.end_batch(l_process_audit_id);
1643 END Export;
1644
1645
1646 END CN_DIM_HIERARCHIES_PVT;