[Home] [Help]
PACKAGE BODY: APPS.CN_IMP_HIERARCHY_PVT
Source
1 PACKAGE BODY CN_IMP_HIERARCHY_PVT AS
2 -- $Header: cnvimhib.pls 120.3 2005/09/19 00:58:03 kjayapau noship $
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_IMP_HIERARCHY_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnvimhib.pls';
6
7 -- ========================================================
8 -- Utility Modules
9 -- ========================================================
10 -- --------------------------------------------------------+
11 -- seterr_imp_hierarchy
12 --
13 -- This procedure will set error in cn_imp_lines(cn_hierarchy_imp_v)
14 -- with passed in status and error code
15 -- --------------------------------------------------------+
16 PROCEDURE seterr_imp_hierarchy
17 (p_hier_record IN imp_hier_rec_type,
18 p_status_code IN VARCHAR2,
19 p_error_code IN VARCHAR2,
20 p_error_msg IN VARCHAR2,
21 x_failed_row IN OUT NOCOPY NUMBER,
22 x_processed_row IN OUT NOCOPY NUMBER) IS
23 PRAGMA AUTONOMOUS_TRANSACTION;
24 BEGIN
25
26 UPDATE cn_hierarchy_imp_v
27 SET status_code = p_status_code, error_code = p_error_code,
28 error_msg = p_error_msg
29 WHERE
30 (start_date =
31 Decode(p_hier_record.start_date,FND_API.G_MISS_CHAR,start_date,
32 p_hier_record.start_date)
33 )
34 AND
35 (Nvl(end_date,FND_API.g_miss_char) =
36 Decode(p_hier_record.end_date,
37 FND_API.G_MISS_CHAR,Nvl(end_date,FND_API.g_miss_char) ,
38 NULL,FND_API.g_miss_char,
39 p_hier_record.end_date)
40 )
41 AND
42 (hierarchy_name =
43 Decode(p_hier_record.hierarchy_name,FND_API.G_MISS_CHAR,hierarchy_name,
44 p_hier_record.hierarchy_name)
45 )
46 AND
47 (Upper(hierarchy_value) =
48 Decode(p_hier_record.hierarchy_value,FND_API.G_MISS_CHAR,
49 Upper(hierarchy_value),p_hier_record.hierarchy_value)
50 )
51 AND
52 (Upper(primary_key) =
53 Decode(p_hier_record.primary_key,FND_API.G_MISS_CHAR,Upper(primary_key),
54 p_hier_record.primary_key)
55 )
56 AND
57 (Upper(base_table_name) =
58 Decode(p_hier_record.base_table_name,FND_API.G_MISS_CHAR,
59 Upper(base_table_name),p_hier_record.base_table_name)
60 )
61 AND
62 (hierarchy_type=
63 Decode(p_hier_record.hierarchy_type,FND_API.G_MISS_CHAR,hierarchy_type,
64 p_hier_record.hierarchy_type)
65 )
66 AND
67 (imp_line_id =
68 Decode(p_hier_record.imp_line_id,FND_API.G_MISS_NUM,imp_line_id,
69 p_hier_record.imp_line_id)
70 )
71 AND (imp_header_id = p_hier_record.imp_header_id)
72 AND status_code = 'STAGE'
73 ;
74
75 x_failed_row := x_failed_row + SQL%rowcount;
76 x_processed_row := x_processed_row + SQL%rowcount;
77
78 IF (SQL%ROWCOUNT=0) THEN
79 RAISE NO_DATA_FOUND;
80 END IF;
81 COMMIT;
82
83 CN_IMPORT_PVT.update_imp_headers
84 (p_imp_header_id => p_hier_record.imp_header_id,
85 p_status_code => 'IMPORT_FAIL',
86 p_failed_row => x_failed_row,
87 p_processed_row => x_processed_row);
88
89 END seterr_imp_hierarchy;
90
91 -- --------------------------------------------------------+
92 -- Imp_Hierarchy_Type
93 --
94 -- This procedure will import one hierarchy type
95 -- --------------------------------------------------------+
96 PROCEDURE Imp_Hierarchy_Type
97 (p_imp_header_id IN NUMBER,
98 p_hier_record IN OUT NOCOPY imp_hier_rec_type,
99 x_dimension_id OUT NOCOPY NUMBER,
100 x_base_table_id OUT NOCOPY NUMBER,
101 x_primary_key_id OUT NOCOPY NUMBER,
102 x_hier_value_id OUT NOCOPY NUMBER,
103 x_error_msg OUT NOCOPY VARCHAR2,
104 x_return_status OUT NOCOPY VARCHAR2,
105 x_failed_row IN OUT NOCOPY NUMBER,
106 x_processed_row IN OUT NOCOPY NUMBER,
107 p_org_id IN NUMBER) IS
108
109 l_api_name CONSTANT VARCHAR2(30) := 'Imp_Hierarchy_Type';
110 l_stage_status cn_imp_lines.status_code%TYPE := 'STAGE';
111 l_message VARCHAR2(2000);
112 l_error_code VARCHAR2(30);
113 l_msg_count NUMBER := 0;
114 l_dummy NUMBER;
115 l_description VARCHAR2(2000) := null;
116
117 BEGIN
118 -- Standard Start of API savepoint
119 SAVEPOINT Imp_Hierarchy_Type ;
120 -- Initialize API return status to success
121 x_return_status := FND_API.G_RET_STS_SUCCESS;
122
123 cn_message_pkg.debug
124 ('Start Import Hierarchy Type : ' || p_hier_record.hierarchy_type);
125
126 -- Get BASE_TABLE_NAME,PRIMARY_KEY,HIERARCHY_VALUE
127 BEGIN
128 SELECT DISTINCT Upper(BASE_TABLE_NAME) BASE_TABLE_NAME,
129 Upper(primary_key) PRIMARY_KEY,
130 Upper(hierarchy_value) HIERARCHY_VALUE
131 INTO p_hier_record.base_table_name,p_hier_record.primary_key,
132 p_hier_record.hierarchy_value
133 FROM cn_hierarchy_imp_v
134 WHERE imp_header_id = p_imp_header_id
135 AND status_code = l_stage_status
136 AND hierarchy_type = p_hier_record.hierarchy_type
137 ;
138 EXCEPTION
139 -- Check if has multiple BASE_TABLE_NAME,PRIMARY_KEY,HIERARCHY_VALUE for
140 -- the same hierarchy type within this import
141 WHEN TOO_MANY_ROWS THEN
142 l_message := fnd_message.get_string('CN','CN_HIER_MULTI_BTBL');
143 l_error_code := 'CN_HIER_MULTI_BTBL';
144 p_hier_record.base_table_name := fnd_api.G_MISS_CHAR ;
145 p_hier_record.primary_key := fnd_api.G_MISS_CHAR ;
146 p_hier_record.hierarchy_value := fnd_api.G_MISS_CHAR ;
147 RAISE FND_API.g_exc_error;
148 END;
149 cn_message_pkg.debug('-- Base table = ' || p_hier_record.base_table_name);
150
151 -- Get IDs for BASE_TABLE_NAME
152 BEGIN
153 SELECT table_id INTO x_base_table_id
154 FROM cn_obj_tables_v WHERE name = p_hier_record.base_table_name and org_id=p_org_id
155 ;
156 EXCEPTION
157 -- base table not exist in cn_obj_tables_v
158 WHEN NO_DATA_FOUND THEN
159 l_message := fnd_message.get_string('CN','CN_HIER_NF_BTBL');
160 l_error_code := 'CN_HIER_NF_BTBL';
161 RAISE FND_API.g_exc_error;
162 END;
163 cn_message_pkg.debug('-- Base table ID = ' || x_base_table_id);
164
165 -- Get IDs for PRIMARY_KEY
166 BEGIN
167 SELECT column_id INTO x_primary_key_id
168 FROM cn_obj_columns_v
169 WHERE name = p_hier_record.primary_key AND table_id = x_base_table_id and org_id=p_org_id
170 ;
171 EXCEPTION
172 -- primary_key not exist
173 WHEN NO_DATA_FOUND THEN
174 l_message := fnd_message.get_string('CN','CN_HIER_NF_PKEY');
175 l_error_code := 'CN_HIER_NF_PKEY';
176 RAISE FND_API.g_exc_error;
177 END;
178 cn_message_pkg.debug('-- PK ID = ' || x_primary_key_id);
179
180 -- Get IDs for HIERARCHY_VALUE
181 BEGIN
182 SELECT column_id INTO x_hier_value_id
183 FROM cn_obj_columns_v
184 WHERE name = p_hier_record.hierarchy_value
185 AND table_id = x_base_table_id and org_id=p_org_id
186 ;
187 EXCEPTION
188 -- hierarchy_value not exist
189 WHEN NO_DATA_FOUND THEN
190 l_message := fnd_message.get_string('CN','CN_HIER_NF_HIERVAL');
191 l_error_code := 'CN_HIER_NF_HIERVAL';
192 RAISE FND_API.g_exc_error;
193 END;
194 cn_message_pkg.debug('-- Hier value ID = ' || x_hier_value_id);
195
196 -- Create new hierarchy type if not exist
197 BEGIN
198 SELECT dimension_id INTO x_dimension_id FROM cn_dimensions_vl
199 WHERE name = p_hier_record.hierarchy_type and org_id=p_org_id;
200 EXCEPTION
201 WHEN no_data_found THEN
202 BEGIN
203 -- for new Hierarchy Type, it's base_table should exist in
204 -- cn_base_tables_v, if not, means other Hierarchy Type already use
205 -- this table
206 SELECT 1 INTO l_dummy FROM cn_base_tables_v
207 WHERE table_id = x_base_table_id
208 AND name = p_hier_record.base_table_name and org_id=p_org_id
209 ;
210 EXCEPTION
211 WHEN no_data_found THEN
212 l_message := fnd_message.get_string('CN','CN_HIER_DUP_BTBL');
213 l_error_code := 'CN_HIER_DUP_BTBL';
214 RAISE FND_API.g_exc_error;
215 END;
216
217 cn_dim_hierarchies_pvt.Create_Hierarchy_Type
218 (p_api_version => 1.0,
219 p_init_msg_list => FND_API.G_TRUE,
220 p_name => p_hier_record.hierarchy_type,
221 p_base_table_id => x_base_table_id,
222 p_primary_key_id => x_primary_key_id,
223 p_user_column_id => x_hier_value_id,
224 p_org_id => p_org_id,
225 p_description => l_description,
226 x_return_status => x_return_status,
227 x_msg_count => l_msg_count,
228 x_msg_data => l_message,
229 x_dimension_id => x_dimension_id);
230 IF x_return_status <> FND_API.g_ret_sts_success THEN
231 l_error_code := 'CN_HIER_FAIL_CREATE';
232 IF l_msg_count > 1 THEN
233 l_message :=
234 fnd_msg_pub.get(p_msg_index => fnd_msg_pub.G_FIRST);
235 END IF;
236 RAISE FND_API.g_exc_error;
237 END IF;
238 END;
239 cn_message_pkg.debug('-- dimension ID = ' || x_dimension_id);
240
241 -- Check created/existed hierarchy type has same base table,pk,
242 -- hier_value within the import data
243 BEGIN
244 SELECT 1 INTO l_dummy FROM cn_obj_columns_v
245 WHERE table_id = x_base_table_id
246 AND dimension_id = x_dimension_id
247 AND column_id = x_primary_key_id
248 AND primary_key = 'Y' and org_id=p_org_id
249 ;
250 SELECT 1 INTO l_dummy FROM cn_obj_columns_v
251 WHERE table_id = x_base_table_id
252 AND column_id = x_hier_value_id
253 AND user_column_name = 'Y' and org_id=p_org_id
254 ;
255 EXCEPTION
256 WHEN no_data_found THEN
257 l_message := fnd_message.get_string('CN','CN_HIER_NOTMATCH');
258 l_error_code := 'CN_HIER_NOTMATCH';
259 RAISE FND_API.g_exc_error;
260 END;
261
262 x_error_msg := l_message;
263
264 cn_message_pkg.debug
265 ('End Import Hierarchy Type : ' || p_hier_record.hierarchy_type ||
266 ' dimension_id = ' || x_dimension_id );
267
268 EXCEPTION
269 WHEN FND_API.G_EXC_ERROR THEN
270 ROLLBACK TO Imp_Hierarchy_Type ;
271 x_return_status := FND_API.G_RET_STS_ERROR ;
272 x_error_msg := l_message;
273 seterr_imp_hierarchy
274 (p_hier_record => p_hier_record,
275 p_status_code => 'FAIL',
276 p_error_code => l_error_code,
277 p_error_msg => l_message,
278 x_failed_row => x_failed_row,
279 x_processed_row => x_processed_row);
280 cn_message_pkg.write
281 (p_message_text => l_message ,
282 p_message_type => 'ERROR');
283 cn_message_pkg.write
284 (p_message_text =>
285 ' All record with hierarchy type = ' || p_hier_record.hierarchy_type
286 || ' and base table = ' || p_hier_record.BASE_TABLE_NAME
287 || ' and primary key = ' || p_hier_record.primary_key
288 || ' and hierarchy value = ' || p_hier_record.hierarchy_value
289 || ' are treated as failed records.' ,
290 p_message_type => 'ERROR');
291
292 WHEN OTHERS THEN
293 ROLLBACK TO Imp_Hierarchy_Type ;
294 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
295 cn_message_pkg.set_error(l_api_name,'Unexpected error');
296 l_error_code := SQLCODE;
297 l_message := SUBSTR (SQLERRM , 1 , 2000);
298 x_error_msg := l_message;
299 seterr_imp_hierarchy
300 (p_hier_record => p_hier_record,
301 p_status_code => 'FAIL',
302 p_error_code => l_error_code,
303 p_error_msg => l_message,
304 x_failed_row => x_failed_row,
305 x_processed_row => x_processed_row);
306 cn_message_pkg.write
307 (p_message_text => l_message ,
308 p_message_type => 'ERROR');
309 cn_message_pkg.write
310 (p_message_text =>
311 ' All record with hierarchy type = ' || p_hier_record.hierarchy_type
312 || ' and base table = ' || p_hier_record.BASE_TABLE_NAME
313 || ' and primary key = ' || p_hier_record.primary_key
314 || ' and hierarchy value = ' || p_hier_record.hierarchy_value
315 || ' are treated as failed records.' ,
316 p_message_type => 'ERROR');
317
318 END Imp_Hierarchy_Type;
319
320 -- --------------------------------------------------------+
321 -- Imp_Head_Hierarchy
322 --
323 -- This procedure will import one head hierarchy
324 -- --------------------------------------------------------+
325 PROCEDURE Imp_Head_Hierarchy
326 (p_imp_header_id IN NUMBER,
327 p_hier_record IN imp_hier_rec_type,
328 p_dimension_id IN NUMBER,
329 x_head_hierarchy_id OUT NOCOPY NUMBER,
330 x_error_msg OUT NOCOPY VARCHAR2,
331 x_return_status OUT NOCOPY VARCHAR2,
332 x_failed_row IN OUT NOCOPY NUMBER,
333 x_processed_row IN OUT NOCOPY NUMBER,
334 p_org_id IN NUMBER) IS
335
336 l_api_name CONSTANT VARCHAR2(30) := 'Imp_Head_Hierarchy';
337 l_stage_status cn_imp_lines.status_code%TYPE := 'STAGE';
338 l_message VARCHAR2(2000);
339 l_error_code VARCHAR2(30);
340 l_msg_count NUMBER := 0;
341
342 BEGIN
343 -- Standard Start of API savepoint
344 SAVEPOINT Imp_Head_Hierarchy ;
345 -- Initialize API return status to success
346 x_return_status := FND_API.G_RET_STS_SUCCESS;
347
348 cn_message_pkg.debug
349 ('Start Import Head Hierarchy : ' || p_hier_record.hierarchy_name);
350 -- Create new head hierarchy if not exist
351 BEGIN
352 SELECT head_hierarchy_id INTO x_head_hierarchy_id
353 FROM cn_head_hierarchies
354 WHERE name = p_hier_record.hierarchy_name
355 AND dimension_id = p_dimension_id
356 AND org_id = p_org_id
357 ;
358 EXCEPTION
359 WHEN no_data_found THEN
360 cn_dim_hierarchies_pvt.Create_Head_Hierarchy
361 (p_api_version => 1.0,
362 p_init_msg_list => FND_API.G_TRUE,
363 p_name => p_hier_record.hierarchy_name,
364 p_dimension_id => p_dimension_id,
365 p_org_id => p_org_id,
366 x_return_status => x_return_status,
367 x_msg_count => l_msg_count,
368 x_msg_data => l_message,
369 x_head_hierarchy_id => x_head_hierarchy_id);
370 IF x_return_status <> FND_API.g_ret_sts_success THEN
371 l_error_code := 'CN_HIER_FAIL_CREATE';
372 IF l_msg_count > 1 THEN
373 l_message :=
374 fnd_msg_pub.get(p_msg_index => fnd_msg_pub.G_FIRST);
375 END IF;
376 RAISE FND_API.g_exc_error;
377 END IF;
378 END;
379
380 x_error_msg := l_message;
381
382 cn_message_pkg.debug
383 ('End Import Head Hierarchy : ' || p_hier_record.hierarchy_name ||
384 ' head_hierarchy_id = ' || x_head_hierarchy_id );
385
386 EXCEPTION
387 WHEN FND_API.G_EXC_ERROR THEN
388 ROLLBACK TO Imp_Head_Hierarchy ;
389 x_return_status := FND_API.G_RET_STS_ERROR ;
390 x_error_msg := l_message;
391 seterr_imp_hierarchy
392 (p_hier_record => p_hier_record,
393 p_status_code => 'FAIL',
394 p_error_code => l_error_code,
395 p_error_msg => l_message,
396 x_failed_row => x_failed_row,
397 x_processed_row => x_processed_row);
398 cn_message_pkg.write
399 (p_message_text => l_message ,
400 p_message_type => 'ERROR');
401 cn_message_pkg.write
402 (p_message_text =>
403 ' All record with hierarchy type = ' || p_hier_record.hierarchy_type
404 || ' and Hierarchy name = ' || p_hier_record.hierarchy_name
405 || ' are treated as failed records.' ,
406 p_message_type => 'ERROR');
407
408 WHEN OTHERS THEN
409 ROLLBACK TO Imp_Head_Hierarchy ;
410 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
411 cn_message_pkg.set_error(l_api_name,'Unexpected error');
412 l_error_code := SQLCODE;
413 l_message := SUBSTR (SQLERRM , 1 , 2000);
414 x_error_msg := l_message;
415 seterr_imp_hierarchy
416 (p_hier_record => p_hier_record,
417 p_status_code => 'FAIL',
418 p_error_code => l_error_code,
419 p_error_msg => l_message,
420 x_failed_row => x_failed_row,
421 x_processed_row => x_processed_row);
422 cn_message_pkg.write
423 (p_message_text => l_message ,
424 p_message_type => 'ERROR');
425 cn_message_pkg.write
426 (p_message_text =>
427 ' All record with hierarchy type = ' || p_hier_record.hierarchy_type
428 || ' and Hierarchy name = ' || p_hier_record.hierarchy_name
429 || ' are treated as failed records.' ,
430 p_message_type => 'ERROR');
431
432 END Imp_Head_Hierarchy;
433
434 -- --------------------------------------------------------+
435 -- Imp_Dim_Hierarchy
436 --
437 -- This procedure will import one dim hierarchy
438 -- --------------------------------------------------------+
439 PROCEDURE Imp_Dim_Hierarchy
440 (p_imp_header_id IN NUMBER,
441 p_hier_record IN imp_hier_rec_type,
442 p_head_hierarchy_id IN NUMBER,
443 x_dim_hierarchy_id OUT NOCOPY NUMBER,
444 x_error_msg OUT NOCOPY VARCHAR2,
445 x_return_status OUT NOCOPY VARCHAR2,
446 x_failed_row IN OUT NOCOPY NUMBER,
447 x_processed_row IN OUT NOCOPY NUMBER,
448 p_org_id IN NUMBER) IS
449
450 l_api_name CONSTANT VARCHAR2(30) := 'Imp_Dim_Hierarchy';
451 l_stage_status cn_imp_lines.status_code%TYPE := 'STAGE';
452 l_message VARCHAR2(2000);
453 l_error_code VARCHAR2(30);
454 l_msg_count NUMBER := 0;
455
456 BEGIN
457 -- Standard Start of API savepoint
458 SAVEPOINT Imp_Dim_Hierarchy ;
459 -- Initialize API return status to success
460 x_return_status := FND_API.G_RET_STS_SUCCESS;
461
462 cn_message_pkg.debug
463 ('Start Import Dim Hierarchy : ' || p_hier_record.start_date
464 || p_hier_record.end_date);
465 -- Create new dim hierarchy if not exist
466 BEGIN
467 SELECT dim_hierarchy_id INTO x_dim_hierarchy_id
468 FROM cn_dim_hierarchies
469 WHERE header_dim_hierarchy_id = p_head_hierarchy_id
470 AND org_id = p_org_id
471 AND start_date = To_date(p_hier_record.start_date, 'DD/MM/YYYY')
472 AND Nvl(end_date,FND_API.g_miss_date) =
473 Nvl(To_date(p_hier_record.end_date, 'DD/MM/YYYY'),FND_API.g_miss_date)
474 ;
475
476 EXCEPTION
477 WHEN no_data_found THEN
478 cn_dim_hierarchies_pvt.Create_Dim_Hierarchy
479 (p_api_version => 1.0,
480 p_init_msg_list => FND_API.G_TRUE,
481 p_head_hierarchy_id => p_head_hierarchy_id,
482 p_start_date => To_date(p_hier_record.start_date, 'DD/MM/YYYY'),
483 p_end_date => To_date(p_hier_record.end_date, 'DD/MM/YYYY'),
484 p_root_node => NULL,
485 p_org_id => p_org_id,
486 x_return_status => x_return_status,
487 x_msg_count => l_msg_count,
488 x_msg_data => l_message,
489 x_dim_hierarchy_id => x_dim_hierarchy_id);
490 IF x_return_status <> FND_API.g_ret_sts_success THEN
491 l_error_code := 'CN_HIER_FAIL_CREATE';
492 IF l_msg_count > 1 THEN
493 l_message :=
494 fnd_msg_pub.get(p_msg_index => fnd_msg_pub.G_FIRST);
495 END IF;
496 RAISE FND_API.g_exc_error;
497 END IF;
498 END;
499
500 x_error_msg := l_message;
501
502 cn_message_pkg.debug
503 ('End Import Dim Hierarchy : ' || p_hier_record.start_date ||
504 p_hier_record.end_date || ' dim_hierarchy_id = ' || x_dim_hierarchy_id );
505
506 EXCEPTION
507 WHEN FND_API.G_EXC_ERROR THEN
508 ROLLBACK TO Imp_Dim_Hierarchy ;
509 x_return_status := FND_API.G_RET_STS_ERROR ;
510 x_error_msg := l_message;
511 seterr_imp_hierarchy
512 (p_hier_record => p_hier_record,
513 p_status_code => 'FAIL',
514 p_error_code => l_error_code,
515 p_error_msg => l_message,
516 x_failed_row => x_failed_row,
517 x_processed_row => x_processed_row);
518 cn_message_pkg.write
519 (p_message_text => l_message ,
520 p_message_type => 'ERROR');
521 cn_message_pkg.write
522 (p_message_text =>
523 ' All record with hierarchy type = ' || p_hier_record.hierarchy_type
524 || ' and Hierarchy name = ' || p_hier_record.hierarchy_name
525 || ' Start Date = ' || p_hier_record.start_date
526 || ' End Date = ' || p_hier_record.end_date
527 || ' are treated as failed records.' ,
528 p_message_type => 'ERROR');
529
530 WHEN OTHERS THEN
531 ROLLBACK TO Imp_Dim_Hierarchy ;
532 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
533 cn_message_pkg.set_error(l_api_name,'Unexpected error');
534 l_error_code := SQLCODE;
535 l_message := SUBSTR (SQLERRM , 1 , 2000);
536 x_error_msg := l_message;
537 seterr_imp_hierarchy
538 (p_hier_record => p_hier_record,
539 p_status_code => 'FAIL',
540 p_error_code => l_error_code,
541 p_error_msg => l_message,
542 x_failed_row => x_failed_row,
543 x_processed_row => x_processed_row);
544 cn_message_pkg.write
545 (p_message_text => l_message ,
546 p_message_type => 'ERROR');
547 cn_message_pkg.write
548 (p_message_text =>
549 ' All record with hierarchy type = ' || p_hier_record.hierarchy_type
550 || ' and Hierarchy name = ' || p_hier_record.hierarchy_name
551 || ' Start Date = ' || p_hier_record.start_date
552 || ' End Date = ' || p_hier_record.end_date
553 || ' are treated as failed records.' ,
554 p_message_type => 'ERROR');
555
556 END Imp_Dim_Hierarchy;
557
558 -- --------------------------------------------------------+
559 -- Imp_Hierarchy_Node
560 --
561 -- This procedure will import one dim hierarchy
562 -- --------------------------------------------------------+
563 PROCEDURE Imp_Hierarchy_Node
564 (p_imp_header_id IN NUMBER,
565 p_hier_record IN imp_hier_rec_type,
566 p_dim_hierarchy_id IN NUMBER,
567 p_def_base_name IN VARCHAR2,
568 p_header_list IN VARCHAR2,
569 p_sql_stmt IN VARCHAR2,
570 x_error_msg OUT NOCOPY VARCHAR2,
571 x_return_status OUT NOCOPY VARCHAR2,
572 x_failed_row IN OUT NOCOPY NUMBER,
573 x_processed_row IN OUT NOCOPY NUMBER,
574 p_org_id IN NUMBER) IS
575
576 l_api_name CONSTANT VARCHAR2(30) := 'Imp_Hierarchy_Node';
577 l_stage_status cn_imp_lines.status_code%TYPE := 'STAGE';
578 l_message VARCHAR2(2000);
579 l_error_code VARCHAR2(30);
580 l_msg_count NUMBER := 0;
581 l_dummy NUMBER;
582
583 l_sql_stmt VARCHAR2(8000);
584
585 -- Cursor to get hierarchy nodes
586 CURSOR c_hierarchy_node_csr
587 (l_hier_type cn_hierarchy_imp_v.hierarchy_type%TYPE,
588 l_base_tbl cn_hierarchy_imp_v.base_table_name%TYPE,
589 l_hier_name cn_hierarchy_imp_v.hierarchy_name%TYPE,
590 l_start_date cn_hierarchy_imp_v.start_date%TYPE,
591 l_end_date cn_hierarchy_imp_v.end_date%TYPE)
592 IS
593 SELECT
594 imp_line_id,
595 imp_header_id,
596 status_code,
597 error_code,
598 record_num,
599 trim(parent_node_name) PARENT_NODE_NAME,
600 trim(default_node_flag) DEFAULT_NODE_FLAG,
601 trim(node_name) NODE_NAME
602 FROM CN_HIERARCHY_IMP_V
603 WHERE imp_header_id = p_imp_header_id
604 AND status_code = l_stage_status
605 AND hierarchy_type = l_hier_type
606 AND BASE_TABLE_NAME = l_base_tbl
607 AND hierarchy_name = l_hier_name
608 AND start_date = l_start_date
609 AND Nvl(end_date,FND_API.g_miss_char) =
610 Nvl(l_end_date,FND_API.g_miss_char)
611 ORDER BY level_num
612 ;
613
614 TYPE refcurtype IS ref CURSOR;
615 node_csr refcurtype;
616 l_parent_ext_id cn_hierarchy_nodes.external_id%TYPE ;
617 l_parent_value_id cn_hierarchy_nodes.value_id%TYPE ;
618 l_external_id cn_hierarchy_nodes.external_id%TYPE ;
619 l_value_id cn_hierarchy_nodes.value_id%TYPE ;
620
621 BEGIN
622 -- Initialize API return status to success
623 x_return_status := FND_API.G_RET_STS_SUCCESS;
624
625 cn_message_pkg.debug('Start Import Hier Node.');
626
627 FOR l_hierarchy_node_csr IN
628 c_hierarchy_node_csr
629 (p_hier_record.hierarchy_type,p_hier_record.BASE_TABLE_NAME,
630 p_hier_record.hierarchy_name,p_hier_record.start_date,
631 p_hier_record.end_date)
632 LOOP
633 BEGIN
634 l_parent_value_id := NULL;
635 l_parent_ext_id := NULL;
636 l_external_id := NULL;
637 l_value_id := NULL;
638
639 cn_message_pkg.debug
640 ('-- Importing Parent = ' || l_hierarchy_node_csr.parent_node_name
641 || ' ; Child = ' || l_hierarchy_node_csr.node_name);
642
643 IF l_hierarchy_node_csr.parent_node_name IS NOT NULL THEN
644 -- parent_node_name must be null for default Base Node
645 IF l_hierarchy_node_csr.default_node_flag = 'Y' THEN
646 l_message := fnd_message.get_string('CN','CN_HIER_NN_DFLTNODE');
647 l_error_code := 'CN_HIER_NN_DFLTNODE';
648 RAISE FND_API.g_exc_error;
649 END IF;
650
651 -- Get value id of parent Node
652 BEGIN
653 IF l_hierarchy_node_csr.parent_node_name <> p_def_base_name THEN
654 SELECT value_id INTO l_parent_value_id
655 FROM cn_hierarchy_nodes
656 WHERE dim_hierarchy_id = p_dim_hierarchy_id
657 AND org_id = p_org_id
658 AND name = l_hierarchy_node_csr.parent_node_name;
659 ELSE
660 SELECT value_id INTO l_parent_value_id
661 FROM cn_hierarchy_nodes
662 WHERE dim_hierarchy_id = p_dim_hierarchy_id
663 AND org_id = p_org_id
664 AND external_id IS NULL;
665 END IF;
666 EXCEPTION
667 WHEN no_data_found THEN
668 -- parent_node not exist in cn_hierarchy_nodes
669 l_message :=
670 fnd_message.get_string('CN','CN_HIER_NF_PARENT_NODE');
671 l_error_code := 'CN_HIER_NF_PARENT_NODE';
672 RAISE FND_API.g_exc_error;
673 END;
674 END IF;
675 cn_message_pkg.debug
676 ('---- Parent node ID = ' || l_parent_value_id ||
677 ' ; Child node = ' || l_hierarchy_node_csr.node_name);
678 -- Get external id of Node, skip default base node .
679 -- create edge if not exist
680 IF l_hierarchy_node_csr.node_name <> p_def_base_name THEN
681 cn_message_pkg.debug
682 ('---- SELECT ' || p_hier_record.primary_key ||
683 ' FROM ' || p_hier_record.base_table_name ||
684 ' WHERE ' || p_hier_record.hierarchy_value ||
685 ' = ''' || l_hierarchy_node_csr.node_name ||'''');
686 OPEN node_csr FOR
687 'SELECT ' || p_hier_record.primary_key ||
688 ' FROM ' || p_hier_record.base_table_name ||
689 ' WHERE ' || p_hier_record.hierarchy_value ||
690 ' = :1' using l_hierarchy_node_csr.node_name;
691 LOOP
692 FETCH node_csr INTO l_external_id;
693 EXIT WHEN node_csr%notfound;
694
695 cn_message_pkg.debug('Node ID = ' || l_external_id);
696
697 END LOOP;
698 IF node_csr%rowcount = 0 THEN
699 l_message :=
700 fnd_message.get_string('CN','CN_HIER_NF_NODE');
701 l_error_code := 'CN_HIER_NF_NODE';
702 RAISE FND_API.g_exc_error;
703 END IF;
704 CLOSE node_csr;
705
706 -- Create hierarchy edge while not exist
707 BEGIN
708 SELECT value_id INTO l_value_id
709 FROM cn_hierarchy_nodes
710 WHERE dim_hierarchy_id = p_dim_hierarchy_id
711 AND org_id = p_org_id
712 AND name = l_hierarchy_node_csr.node_name
713 AND external_id = l_external_id;
714
715 SELECT 1 INTO l_dummy
716 FROM cn_hierarchy_edges
717 WHERE dim_hierarchy_id = p_dim_hierarchy_id
718 AND value_id = l_value_id and org_id=p_org_id
719 AND ((l_parent_value_id IS NULL AND parent_value_id IS NULL) OR
720 (l_parent_value_id IS NOT NULL AND
721 parent_value_id IS NOT NULL AND
722 parent_value_id = l_parent_value_id))
723 ;
724 EXCEPTION
725 WHEN no_data_found THEN
726 cn_dim_hierarchies_pvt.Create_Edge
727 (p_api_version => 1.0,
728 p_init_msg_list => FND_API.G_TRUE,
729 p_dim_hierarchy_id => p_dim_hierarchy_id,
730 p_parent_value_id => l_parent_value_id,
731 p_name => l_hierarchy_node_csr.node_name,
732 p_external_id => l_external_id,
733 p_org_id => p_org_id,
734 x_return_status => x_return_status,
735 x_msg_count => l_msg_count,
736 x_msg_data => l_message,
737 x_value_id => l_value_id);
738
739 IF x_return_status <> FND_API.g_ret_sts_success THEN
740 l_error_code := 'CN_HIER_FAIL_CREATE';
741 IF l_msg_count > 1 THEN
742 l_message :=
743 fnd_msg_pub.get(p_msg_index => fnd_msg_pub.G_FIRST);
744 END IF;
745 RAISE FND_API.g_exc_error;
746 END IF;
747 cn_message_pkg.debug('---- new edge created ');
748 -- Create success
749 COMMIT;
750 END;
751 END IF;
752
753 -- create complete or default node or edge exists
754 CN_IMPORT_PVT.update_imp_lines
755 (p_imp_line_id => l_hierarchy_node_csr.imp_line_id,
756 p_status_code => 'COMPLETE',
757 p_error_code => '');
758
759 x_processed_row := x_processed_row + 1;
760
761 EXCEPTION
762 WHEN FND_API.g_exc_error THEN
763 x_failed_row := x_failed_row + 1;
764 x_processed_row := x_processed_row + 1;
765 x_return_status := FND_API.G_RET_STS_ERROR ;
766 x_error_msg := l_message;
767 CN_IMPORT_PVT.update_imp_lines
768 (p_imp_line_id => l_hierarchy_node_csr.imp_line_id,
769 p_status_code => 'FAIL',
770 p_error_code => l_error_code,
771 p_error_msg => l_message);
772 CN_IMPORT_PVT.update_imp_headers
773 (p_imp_header_id => p_imp_header_id,
774 p_status_code => 'IMPORT_FAIL',
775 p_failed_row => x_failed_row,
776 p_processed_row => x_processed_row);
777 cn_message_pkg.write
778 (p_message_text => 'Record ' ||
779 l_hierarchy_node_csr.record_num || ':' || l_message,
780 p_message_type => 'ERROR');
781 CN_IMPORT_PVT.write_error_rec
782 (p_imp_header_id => p_imp_header_id,
783 p_imp_line_id => l_hierarchy_node_csr.imp_line_id,
784 p_header_list => p_header_list,
785 p_sql_stmt => p_sql_stmt);
786 WHEN OTHERS THEN
787 x_failed_row := x_failed_row + 1;
788 x_processed_row := x_processed_row + 1;
789 x_return_status := FND_API.G_RET_STS_ERROR ;
790 l_error_code := SQLCODE;
791 l_message := SUBSTR (SQLERRM , 1 , 2000);
792 x_error_msg := l_message;
793 CN_IMPORT_PVT.update_imp_lines
794 (p_imp_line_id => l_hierarchy_node_csr.imp_line_id,
795 p_status_code => 'FAIL',
796 p_error_code => l_error_code,
797 p_error_msg => l_message);
798 CN_IMPORT_PVT.update_imp_headers
799 (p_imp_header_id => p_imp_header_id,
800 p_status_code => 'IMPORT_FAIL',
801 p_failed_row => x_failed_row,
802 p_processed_row => x_processed_row);
803 cn_message_pkg.write
804 (p_message_text => 'Record ' ||
805 l_hierarchy_node_csr.record_num || ':' || l_message,
806 p_message_type => 'ERROR');
807 CN_IMPORT_PVT.write_error_rec
808 (p_imp_header_id => p_imp_header_id,
809 p_imp_line_id => l_hierarchy_node_csr.imp_line_id,
810 p_header_list => p_header_list,
811 p_sql_stmt => p_sql_stmt);
812 END;
813
814 END LOOP; -- end c_hierarchy_node_csr loop
815
816 x_error_msg := l_message;
817
818 cn_message_pkg.debug('End Import Hier Node. ');
819
820 END Imp_Hierarchy_Node;
821
822 -- Start of comments
823 -- API name : Hierarchy_Import
824 -- Type : Private.
825 -- Function : programtransfer data from staging table into
826 -- cn_dimension_vl,cn_head_hierarchies,cn_dim_hierarchies
827 -- cn_hierarchy_nodes, cn_hierarchy_edges
828 -- Pre-reqs : None.
829 -- Parameters :
830 -- IN : p_imp_header_id IN NUMBER,
831 -- OUT : errbuf OUT VARCHAR2 Required
832 -- retcode OUTVARCHAR2 Optional
833 -- Version : Current version 1.0
834 --
835 -- Notes : Note text
836 --
837 -- End of comments
838
839 PROCEDURE Hierarchy_Import
840 ( errbuf OUT NOCOPY VARCHAR2,
841 retcode OUT NOCOPY VARCHAR2,
842 p_imp_header_id IN NUMBER ,
843 p_org_id IN NUMBER
844 ) IS
845 l_api_name CONSTANT VARCHAR2(30) := 'Hierarchy_Import';
846
847 l_stage_status cn_imp_lines.status_code%TYPE := 'STAGE';
848 l_imp_header cn_imp_headers_pvt.imp_headers_rec_type
849 := cn_imp_headers_pvt.G_MISS_IMP_HEADERS_REC;
850 l_processed_row NUMBER := 0;
851 l_failed_row NUMBER := 0;
852 l_message VARCHAR2(2000);
853 l_error_code VARCHAR2(30);
854 l_header_list VARCHAR2(2000);
855 l_sql_stmt VARCHAR2(2000);
856 err_num NUMBER;
857 l_return_status VARCHAR2(50);
858 l_msg_count NUMBER := 0;
859 l_process_audit_id cn_process_audits.process_audit_id%TYPE;
860
861 l_dimension_id cn_dimensions_all_b.dimension_id%TYPE;
862 l_head_hierarchy_id cn_head_hierarchies.head_hierarchy_id%TYPE;
863 l_dim_hierarchy_id cn_dim_hierarchies.dim_hierarchy_id%TYPE;
864 l_hier_record imp_hier_rec_type;
865 l_base_tbl_id cn_obj_tables_v.table_id%TYPE;
866 l_primary_key_id cn_obj_columns_v.column_id%TYPE;
867 l_hier_value_id cn_obj_columns_v.column_id%TYPE;
868 l_def_base_name cn_hierarchy_nodes.name%TYPE;
869
870 -- cursor to get all record missed required field
871 CURSOR c_miss_required_csr IS
872 SELECT imp_line_id,record_num FROM cn_hierarchy_imp_v
873 WHERE imp_header_id = p_imp_header_id
874 AND status_code = l_stage_status
875 AND (hierarchy_type IS NULL OR BASE_TABLE_NAME IS NULL OR
876 primary_key IS NULL OR hierarchy_value IS NULL OR
877 hierarchy_name IS NULL OR start_date IS NULL OR
878 default_node_flag IS NULL OR node_name IS NULL)
879 ;
880
881 -- cursor to get all record with wrong default_node_flag
882 CURSOR c_err_dflt_node_csr IS
883 SELECT imp_line_id,record_num FROM cn_hierarchy_imp_v
884 WHERE imp_header_id = p_imp_header_id
885 AND status_code = l_stage_status
886 AND default_node_flag <> 'Y'
887 AND default_node_flag <> 'N'
888 ;
889
890 -- Cursor to get distinct base_table_name from stage table
891 CURSOR c_base_table_csr IS
892 SELECT DISTINCT
893 Upper(base_table_name) BASE_TABLE_NAME
894 FROM CN_HIERARCHY_IMP_V
895 WHERE imp_header_id = p_imp_header_id
896 AND status_code = l_stage_status
897 ;
898
899 -- Cursor to get distinct HIERARCHY_TYPE from stage table
900 CURSOR c_hierarchy_type_csr IS
901 SELECT DISTINCT
902 trim(hierarchy_type) HIERARCHY_TYPE
903 FROM CN_HIERARCHY_IMP_V
904 WHERE imp_header_id = p_imp_header_id
905 AND status_code = l_stage_status
906 ;
907
908 l_hierarchy_type_csr c_hierarchy_type_csr%ROWTYPE;
909
910 -- Cursor to get distinct head_hierarchy for one hierarchy_type
911 CURSOR c_head_hierarchy_csr
912 (l_hier_type cn_hierarchy_imp_v.hierarchy_type%TYPE) IS
913 SELECT DISTINCT
914 trim(hierarchy_name) HIERARCHY_NAME,
915 trim(start_date) START_DATE,
916 trim(end_date) end_date
917 FROM CN_HIERARCHY_IMP_V
918 WHERE imp_header_id = p_imp_header_id
919 AND status_code = l_stage_status
920 AND hierarchy_type = l_hier_type
921 ;
922
923 -- Cursor to get distinct dim_hierarchy for one hierarchy_type,head_hier
924 CURSOR c_dim_hierarchy_csr
925 (l_hier_type cn_hierarchy_imp_v.hierarchy_type%TYPE,
926 l_head_hier cn_hierarchy_imp_v.hierarchy_name%TYPE ) IS
927 SELECT DISTINCT
928 trim(start_date) START_DATE,
929 trim(end_date) end_date
930 FROM CN_HIERARCHY_IMP_V
931 WHERE imp_header_id = p_imp_header_id
932 AND status_code = l_stage_status
933 AND hierarchy_type = l_hier_type
934 AND hierarchy_name = l_head_hier
935 ;
936
937 l_dummy NUMBER;
938 l_tmp VARCHAR2(30);
939 l_parent_node_name cn_hierarchy_imp_v.parent_node_name%TYPE;
940
941 BEGIN
942
943 -- Initialize API return status to success
944 l_return_status := FND_API.G_RET_STS_SUCCESS;
945 retcode := 0 ;
946
947 -- Get imp_header info
948 SELECT name, status_code,server_flag,imp_map_id, source_column_num,
949 import_type_code
950 INTO l_imp_header.name ,l_imp_header.status_code ,
951 l_imp_header.server_flag, l_imp_header.imp_map_id,
952 l_imp_header.source_column_num,l_imp_header.import_type_code
953 FROM cn_imp_headers
954 WHERE imp_header_id = p_imp_header_id;
955
956 l_hier_record.imp_header_id := p_imp_header_id;
957
958 -- open process audit batch
959 cn_message_pkg.begin_batch
960 ( x_process_type => l_imp_header.import_type_code,
961 x_parent_proc_audit_id => p_imp_header_id ,
962 x_process_audit_id => l_process_audit_id,
963 x_request_id => null,
964 p_org_id => p_org_id);
965
966 cn_message_pkg.write
967 (p_message_text => 'HIERARCHY: Start Transfer Data. imp_header_id = '
968 || To_char(p_imp_header_id),
969 p_message_type => 'MILESTONE');
970
971 -- Get source column name list and target column dynamic sql statement
972 CN_IMPORT_PVT.build_error_rec
973 (p_imp_header_id => p_imp_header_id,
974 x_header_list => l_header_list,
975 x_sql_stmt => l_sql_stmt);
976
977 -- -----------------------------------------------------------+
978 -- Check for all required field
979 FOR l_miss_required_csr IN c_miss_required_csr LOOP
980 l_failed_row := l_failed_row + 1;
981 l_processed_row := l_processed_row + 1;
982 l_error_code := 'CN_IMP_MISS_REQUIRED';
983 l_message := fnd_message.get_string('CN','CN_IMP_MISS_REQUIRED');
984 CN_IMPORT_PVT.update_imp_lines
985 (p_imp_line_id => l_miss_required_csr.imp_line_id,
986 p_status_code => 'FAIL',
987 p_error_code => l_error_code,
988 p_error_msg => l_message);
989 CN_IMPORT_PVT.update_imp_headers
990 (p_imp_header_id => p_imp_header_id,
991 p_status_code => 'IMPORT_FAIL',
992 p_failed_row => l_failed_row);
993 cn_message_pkg.write
994 (p_message_text => 'Record ' ||
995 To_char(l_miss_required_csr.record_num) || ':' || l_message,
996 p_message_type => 'ERROR');
997 CN_IMPORT_PVT.write_error_rec
998 (p_imp_header_id => p_imp_header_id,
999 p_imp_line_id => l_miss_required_csr.imp_line_id,
1000 p_header_list => l_header_list,
1001 p_sql_stmt => l_sql_stmt);
1002
1003 retcode := 2;
1004 errbuf := l_message;
1005 END LOOP;
1006
1007 -- -----------------------------------------------------------+
1008 -- Check input of DEFAULT_NODE_FLAG must be 'Y' or 'N',
1009 FOR l_err_dflt_node_csr IN c_err_dflt_node_csr LOOP
1010 l_failed_row := l_failed_row + 1;
1011 l_processed_row := l_processed_row + 1;
1012 l_error_code := 'CN_HIER_ERR_NODEFLAG';
1013 l_message := fnd_message.get_string('CN','CN_HIER_ERR_NODEFLAG');
1014 CN_IMPORT_PVT.update_imp_lines
1015 (p_imp_line_id => l_err_dflt_node_csr.imp_line_id,
1016 p_status_code => 'FAIL',
1017 p_error_code => l_error_code,
1018 p_error_msg => l_message);
1019 CN_IMPORT_PVT.update_imp_headers
1020 (p_imp_header_id => p_imp_header_id,
1021 p_status_code => 'IMPORT_FAIL',
1022 p_failed_row => l_failed_row);
1023 cn_message_pkg.write
1024 (p_message_text => 'Record ' ||
1025 To_char(l_err_dflt_node_csr.record_num) || ':' || l_message,
1026 p_message_type => 'ERROR');
1027 CN_IMPORT_PVT.write_error_rec
1028 (p_imp_header_id => p_imp_header_id,
1029 p_imp_line_id => l_err_dflt_node_csr.imp_line_id,
1030 p_header_list => l_header_list,
1031 p_sql_stmt => l_sql_stmt);
1032
1033 retcode := 2;
1034 errbuf := l_message;
1035 END LOOP;
1036
1037 -- -----------------------------------------------------------+
1038 -- Check if have multiple hierarchy for same base table within this import
1039 FOR l_base_table_csr IN c_base_table_csr LOOP
1040 BEGIN
1041 l_hier_record.base_table_name := l_base_table_csr.base_table_name;
1042 SELECT DISTINCT trim(hierarchy_type) hierarchy_type
1043 INTO l_tmp
1044 FROM CN_HIERARCHY_IMP_V
1045 WHERE imp_header_id = p_imp_header_id
1046 AND status_code = l_stage_status
1047 AND BASE_TABLE_NAME = l_base_table_csr.BASE_TABLE_NAME
1048 ;
1049 EXCEPTION
1050 WHEN TOO_MANY_ROWS THEN
1051 l_message := fnd_message.get_string('CN','CN_HIER_MULTI_BTBL');
1052 seterr_imp_hierarchy
1053 (p_hier_record => l_hier_record,
1054 p_status_code => 'FAIL',
1055 p_error_code => 'CN_HIER_MULTI_BTBL',
1056 p_error_msg => l_message,
1057 x_failed_row => l_failed_row,
1058 x_processed_row => l_processed_row);
1059
1060 cn_message_pkg.write
1061 (p_message_text => l_message ,
1062 p_message_type => 'ERROR');
1063 cn_message_pkg.write
1064 (p_message_text =>
1065 ' All record with base table = ' ||
1066 l_hier_record.base_table_name ||
1067 ' are treated as failed records.' ,
1068 p_message_type => 'ERROR');
1069
1070 retcode := 2;
1071 errbuf := l_message;
1072 END;
1073 END LOOP;
1074
1075 -- -----------------------------------------------------------------+
1076 -- ----------------------- Hierarchy Type --------------------------+
1077 -- -----------------------------------------------------------------+
1078 OPEN c_hierarchy_type_csr;
1079 LOOP
1080 FETCH c_hierarchy_type_csr INTO l_hierarchy_type_csr;
1081 EXIT WHEN c_hierarchy_type_csr%notfound;
1082 l_hier_record := G_MISS_IMP_HIER_REC;
1083 l_hier_record.imp_header_id := p_imp_header_id;
1084 l_hier_record.hierarchy_type := l_hierarchy_type_csr.hierarchy_type;
1085
1086 -- Import hierarchy type
1087 Imp_Hierarchy_Type
1088 (p_imp_header_id => p_imp_header_id,
1089 p_hier_record => l_hier_record,
1090 x_dimension_id => l_dimension_id,
1091 x_base_table_id => l_base_tbl_id,
1092 x_primary_key_id => l_primary_key_id,
1093 x_hier_value_id => l_hier_value_id,
1094 x_error_msg => l_message,
1095 x_return_status => l_return_status,
1096 x_failed_row => l_failed_row,
1097 x_processed_row => l_processed_row,
1098 p_org_id => p_org_id);
1099
1100 IF l_return_status <> FND_API.g_ret_sts_success THEN
1101 retcode := 2;
1102 errbuf := l_message;
1103 GOTO end_hier_type_loop;
1104 END IF;
1105
1106 -- ---------------------------------------------------------------+
1107 -- --------------------- Head Hierarchy --------------------------+
1108 -- ---------------------------------------------------------------+
1109 FOR l_head_hierarchy IN
1110 c_head_hierarchy_csr(l_hier_record.hierarchy_type) LOOP
1111
1112 l_hier_record.hierarchy_name := l_head_hierarchy.hierarchy_name;
1113
1114 -- Import head hierarchy
1115 Imp_Head_Hierarchy
1116 (p_imp_header_id => p_imp_header_id,
1117 p_hier_record => l_hier_record,
1118 p_dimension_id => l_dimension_id,
1119 x_head_hierarchy_id => l_head_hierarchy_id,
1120 x_error_msg => l_message,
1121 x_return_status => l_return_status,
1122 x_failed_row => l_failed_row,
1123 x_processed_row => l_processed_row,
1124 p_org_id => p_org_id);
1125
1126 IF l_return_status <> FND_API.g_ret_sts_success THEN
1127 retcode := 2;
1128 errbuf := l_message;
1129 GOTO end_head_hier_loop;
1130 END IF;
1131
1132 FOR l_dim_hierarchy IN
1133 c_dim_hierarchy_csr
1134 (l_hier_record.hierarchy_type,l_hier_record.hierarchy_name) LOOP
1135 -- ---------------------------------------------------------------+
1136 -- --------------------- Dim Hierarchy ---------------------------+
1137 -- ---------------------------------------------------------------+
1138 l_hier_record.start_date := l_dim_hierarchy.start_date;
1139 l_hier_record.end_date := l_dim_hierarchy.end_date;
1140 -- Import dim hierarchy
1141 Imp_Dim_Hierarchy
1142 (p_imp_header_id => p_imp_header_id,
1143 p_hier_record => l_hier_record,
1144 p_head_hierarchy_id => l_head_hierarchy_id,
1145 x_dim_hierarchy_id => l_dim_hierarchy_id,
1146 x_error_msg => l_message,
1147 x_return_status => l_return_status,
1148 x_failed_row => l_failed_row,
1149 x_processed_row => l_processed_row,
1150 p_org_id => p_org_id);
1151
1152 IF l_return_status <> FND_API.g_ret_sts_success THEN
1153 retcode := 2;
1154 errbuf := l_message;
1155 GOTO end_dim_hier_loop;
1156 END IF;
1157 -- ---------------------------------------------------------------+
1158 -- --------------------- Hierarchy Node---------------------------+
1159 -- ---------------------------------------------------------------+
1160 BEGIN
1161 l_parent_node_name := 'temp';
1162 -- Get default base node name from CSV file
1163 SELECT trim(node_name),trim(parent_node_name)
1164 INTO l_def_base_name,l_parent_node_name
1165 FROM cn_hierarchy_imp_v
1166 WHERE imp_header_id = p_imp_header_id
1167 AND status_code = l_stage_status
1168 AND hierarchy_type = l_hier_record.hierarchy_type
1169 AND hierarchy_name = l_hier_record.hierarchy_name
1170 AND start_date = l_hier_record.start_date
1171 AND Nvl(end_date,FND_API.g_miss_char) =
1172 Nvl(l_hier_record.end_date,FND_API.g_miss_char)
1173 AND default_node_flag = 'Y'
1174 ;
1175 -- parent_node_name must be null if default_node_flag = 'Y'
1176 IF l_parent_node_name IS NOT NULL THEN
1177 l_message :=
1178 fnd_message.get_string('CN','CN_HIER_WRONG_DEFNODE');
1179 l_error_code := 'CN_HIER_WRONG_DEFNODE';
1180 seterr_imp_hierarchy
1181 (p_hier_record => l_hier_record,
1182 p_status_code => 'FAIL',
1183 p_error_code => l_error_code,
1184 p_error_msg => l_message,
1185 x_failed_row => l_failed_row,
1186 x_processed_row => l_processed_row);
1187
1188 cn_message_pkg.write
1189 (p_message_text => l_message ,
1190 p_message_type => 'ERROR');
1191 cn_message_pkg.write
1192 (p_message_text =>
1193 ' All record with hierarchy type = '
1194 ||l_hier_record.hierarchy_type
1195 || ' and Hierarchy name = ' || l_hier_record.hierarchy_name
1196 || ' Start Date = ' || l_hier_record.start_date
1197 || ' End Date = ' || l_hier_record.end_date
1198 || ' are treated as failed records.' ,
1199 p_message_type => 'ERROR');
1200 retcode := 2;
1201 errbuf := l_message;
1202 GOTO end_dim_hier_loop;
1203 END IF;
1204
1205 cn_message_pkg.debug('Def base node = ' || l_def_base_name);
1206
1207 EXCEPTION
1208 WHEN no_data_found THEN
1209 -- get default name from DB
1210 SELECT name INTO l_def_base_name
1211 FROM cn_hierarchy_nodes
1212 WHERE dim_hierarchy_id = l_dim_hierarchy_id and org_id=p_org_id
1213 AND external_id IS NULL ;
1214
1215 WHEN too_many_rows THEN
1216 l_message :=
1217 fnd_message.get_string('CN','CN_HIER_MULTI_DEFNODE');
1218 l_error_code := 'CN_HIER_MULTI_DEFNODE';
1219 seterr_imp_hierarchy
1220 (p_hier_record => l_hier_record,
1221 p_status_code => 'FAIL',
1222 p_error_code => l_error_code,
1223 p_error_msg => l_message,
1224 x_failed_row => l_failed_row,
1225 x_processed_row => l_processed_row);
1226
1227 cn_message_pkg.write
1228 (p_message_text => l_message ,
1229 p_message_type => 'ERROR');
1230 cn_message_pkg.write
1231 (p_message_text =>
1232 ' All record with hierarchy type = '
1233 ||l_hier_record.hierarchy_type
1234 || ' and Hierarchy name = ' || l_hier_record.hierarchy_name
1235 || ' Start Date = ' || l_hier_record.start_date
1236 || ' End Date = ' || l_hier_record.end_date
1237 || ' are treated as failed records.' ,
1238 p_message_type => 'ERROR');
1239 retcode := 2;
1240 errbuf := l_message;
1241 GOTO end_dim_hier_loop;
1242 END;
1243
1244 -- Import hierarchy nodes
1245 Imp_Hierarchy_Node
1246 (p_imp_header_id => p_imp_header_id,
1247 p_hier_record => l_hier_record,
1248 p_dim_hierarchy_id => l_dim_hierarchy_id,
1249 p_def_base_name => l_def_base_name,
1250 p_header_list => l_header_list,
1251 p_sql_stmt => l_sql_stmt,
1252 x_error_msg => l_message,
1253 x_return_status => l_return_status,
1254 x_failed_row => l_failed_row,
1255 x_processed_row => l_processed_row,
1256 p_org_id => p_org_id);
1257
1258 IF l_return_status <> FND_API.g_ret_sts_success THEN
1259 retcode := 2;
1260 errbuf := l_message;
1261 GOTO end_dim_hier_loop;
1262 END IF;
1263
1264 << end_dim_hier_loop >>
1265 NULL ;
1266 END LOOP; -- end Dim Hierarchy Loop
1267
1268 << end_head_hier_loop >>
1269 NULL;
1270 END LOOP; -- end Head Hierarchy Loop
1271
1272 << end_hier_type_loop >>
1273 NULL;
1274 END LOOP; -- end c_hierarchy_type_csr
1275 IF c_hierarchy_type_csr%rowcount = 0 THEN
1276 l_processed_row := 0;
1277 END IF;
1278 CLOSE c_hierarchy_type_csr;
1279 IF l_failed_row = 0 AND retcode = 0 THEN
1280 -- update update_imp_headers
1281 CN_IMPORT_PVT.update_imp_headers
1282 (p_imp_header_id => p_imp_header_id,
1283 p_status_code => 'COMPLETE',
1284 p_processed_row => l_processed_row,
1285 p_failed_row => l_failed_row);
1286 END IF;
1287 cn_message_pkg.write
1288 (p_message_text => 'HIERARCHY: End Transfer Data. imp_header_id = ' ||
1289 To_char(p_imp_header_id),
1290 p_message_type => 'MILESTONE');
1291
1292 -- close process batch
1293 cn_message_pkg.end_batch(l_process_audit_id);
1294
1295 -- Commit all imports
1296 COMMIT;
1297
1298 EXCEPTION
1299 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1300 retcode := 2 ;
1301 cn_message_pkg.end_batch(l_process_audit_id);
1302 FND_MSG_PUB.count_and_get
1303 (p_count => l_msg_count ,
1304 p_data => errbuf ,
1305 p_encoded => FND_API.G_FALSE
1306 );
1307
1308 WHEN OTHERS THEN
1309 err_num := SQLCODE;
1310 IF err_num = -6501 THEN
1311 retcode := 2 ;
1312 errbuf := fnd_program.message;
1313 ELSE
1314 retcode := 2 ;
1315 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1316 THEN
1317 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1318 END IF;
1319 FND_MSG_PUB.count_and_get
1320 (p_count => l_msg_count ,
1321 p_data => errbuf ,
1322 p_encoded => FND_API.G_FALSE
1323 );
1324 END IF;
1325 cn_message_pkg.set_error(l_api_name,errbuf);
1326 cn_message_pkg.end_batch(l_process_audit_id);
1327
1328 END Hierarchy_Import;
1329
1330 END CN_IMP_HIERARCHY_PVT;