[Home] [Help]
PACKAGE BODY: APPS.CN_IMP_HIERARCHY_PVT
Source
1 PACKAGE BODY CN_IMP_HIERARCHY_PVT AS
2 -- $Header: cnvimhib.pls 120.4 2010/01/27 22:59:01 mguo ship $
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
675 -- Bug fix 6732446
676 ELSE
677 l_parent_value_id := 0;
678 -- End bug fix 6732446
679
680 END IF;
681 cn_message_pkg.debug
682 ('---- Parent node ID = ' || l_parent_value_id ||
683 ' ; Child node = ' || l_hierarchy_node_csr.node_name);
684 -- Get external id of Node, skip default base node .
685 -- create edge if not exist
686 IF l_hierarchy_node_csr.node_name <> p_def_base_name THEN
687 cn_message_pkg.debug
688 ('---- SELECT ' || p_hier_record.primary_key ||
689 ' FROM ' || p_hier_record.base_table_name ||
690 ' WHERE ' || p_hier_record.hierarchy_value ||
691 ' = ''' || l_hierarchy_node_csr.node_name ||'''');
692 OPEN node_csr FOR
693 'SELECT ' || p_hier_record.primary_key ||
694 ' FROM ' || p_hier_record.base_table_name ||
695 ' WHERE ' || p_hier_record.hierarchy_value ||
696 ' = :1' using l_hierarchy_node_csr.node_name;
697 LOOP
698 FETCH node_csr INTO l_external_id;
699 EXIT WHEN node_csr%notfound;
700
701 cn_message_pkg.debug('Node ID = ' || l_external_id);
702
703 END LOOP;
704 IF node_csr%rowcount = 0 THEN
705 l_message :=
706 fnd_message.get_string('CN','CN_HIER_NF_NODE');
707 l_error_code := 'CN_HIER_NF_NODE';
708 RAISE FND_API.g_exc_error;
709 END IF;
710 CLOSE node_csr;
711
712 -- Create hierarchy edge while not exist
713 BEGIN
714 SELECT value_id INTO l_value_id
715 FROM cn_hierarchy_nodes
716 WHERE dim_hierarchy_id = p_dim_hierarchy_id
717 AND org_id = p_org_id
718 AND name = l_hierarchy_node_csr.node_name
719 AND external_id = l_external_id;
720
721 SELECT 1 INTO l_dummy
722 FROM cn_hierarchy_edges
723 WHERE dim_hierarchy_id = p_dim_hierarchy_id
724 AND value_id = l_value_id and org_id=p_org_id
725 AND ((l_parent_value_id IS NULL AND parent_value_id IS NULL) OR
726 (l_parent_value_id IS NOT NULL AND
727 parent_value_id IS NOT NULL AND
728 parent_value_id = l_parent_value_id))
729 ;
730 EXCEPTION
731 WHEN no_data_found THEN
732 cn_dim_hierarchies_pvt.Create_Edge
733 (p_api_version => 1.0,
734 p_init_msg_list => FND_API.G_TRUE,
735 p_dim_hierarchy_id => p_dim_hierarchy_id,
736 p_parent_value_id => l_parent_value_id,
737 p_name => l_hierarchy_node_csr.node_name,
738 p_external_id => l_external_id,
739 p_org_id => p_org_id,
740 x_return_status => x_return_status,
741 x_msg_count => l_msg_count,
742 x_msg_data => l_message,
743 x_value_id => l_value_id);
744
745 IF x_return_status <> FND_API.g_ret_sts_success THEN
746 l_error_code := 'CN_HIER_FAIL_CREATE';
747 IF l_msg_count > 1 THEN
748 l_message :=
749 fnd_msg_pub.get(p_msg_index => fnd_msg_pub.G_FIRST);
750 END IF;
751 RAISE FND_API.g_exc_error;
752 END IF;
753 cn_message_pkg.debug('---- new edge created ');
754 -- Create success
755 COMMIT;
756 END;
757 END IF;
758
759 -- create complete or default node or edge exists
760 CN_IMPORT_PVT.update_imp_lines
761 (p_imp_line_id => l_hierarchy_node_csr.imp_line_id,
762 p_status_code => 'COMPLETE',
763 p_error_code => '');
764
765 x_processed_row := x_processed_row + 1;
766
767 EXCEPTION
768 WHEN FND_API.g_exc_error THEN
769 x_failed_row := x_failed_row + 1;
770 x_processed_row := x_processed_row + 1;
771 x_return_status := FND_API.G_RET_STS_ERROR ;
772 x_error_msg := l_message;
773 CN_IMPORT_PVT.update_imp_lines
774 (p_imp_line_id => l_hierarchy_node_csr.imp_line_id,
775 p_status_code => 'FAIL',
776 p_error_code => l_error_code,
777 p_error_msg => l_message);
778 CN_IMPORT_PVT.update_imp_headers
779 (p_imp_header_id => p_imp_header_id,
780 p_status_code => 'IMPORT_FAIL',
781 p_failed_row => x_failed_row,
782 p_processed_row => x_processed_row);
783 cn_message_pkg.write
784 (p_message_text => 'Record ' ||
785 l_hierarchy_node_csr.record_num || ':' || l_message,
786 p_message_type => 'ERROR');
787 CN_IMPORT_PVT.write_error_rec
788 (p_imp_header_id => p_imp_header_id,
789 p_imp_line_id => l_hierarchy_node_csr.imp_line_id,
790 p_header_list => p_header_list,
791 p_sql_stmt => p_sql_stmt);
792 WHEN OTHERS THEN
793 x_failed_row := x_failed_row + 1;
794 x_processed_row := x_processed_row + 1;
795 x_return_status := FND_API.G_RET_STS_ERROR ;
796 l_error_code := SQLCODE;
797 l_message := SUBSTR (SQLERRM , 1 , 2000);
798 x_error_msg := l_message;
799 CN_IMPORT_PVT.update_imp_lines
800 (p_imp_line_id => l_hierarchy_node_csr.imp_line_id,
801 p_status_code => 'FAIL',
802 p_error_code => l_error_code,
803 p_error_msg => l_message);
804 CN_IMPORT_PVT.update_imp_headers
805 (p_imp_header_id => p_imp_header_id,
806 p_status_code => 'IMPORT_FAIL',
807 p_failed_row => x_failed_row,
808 p_processed_row => x_processed_row);
809 cn_message_pkg.write
810 (p_message_text => 'Record ' ||
811 l_hierarchy_node_csr.record_num || ':' || l_message,
812 p_message_type => 'ERROR');
813 CN_IMPORT_PVT.write_error_rec
814 (p_imp_header_id => p_imp_header_id,
815 p_imp_line_id => l_hierarchy_node_csr.imp_line_id,
816 p_header_list => p_header_list,
817 p_sql_stmt => p_sql_stmt);
818 END;
819
820 END LOOP; -- end c_hierarchy_node_csr loop
821
822 x_error_msg := l_message;
823
824 cn_message_pkg.debug('End Import Hier Node. ');
825
826 END Imp_Hierarchy_Node;
827
828 -- Start of comments
829 -- API name : Hierarchy_Import
830 -- Type : Private.
831 -- Function : programtransfer data from staging table into
832 -- cn_dimension_vl,cn_head_hierarchies,cn_dim_hierarchies
833 -- cn_hierarchy_nodes, cn_hierarchy_edges
834 -- Pre-reqs : None.
835 -- Parameters :
836 -- IN : p_imp_header_id IN NUMBER,
837 -- OUT : errbuf OUT VARCHAR2 Required
838 -- retcode OUTVARCHAR2 Optional
839 -- Version : Current version 1.0
840 --
841 -- Notes : Note text
842 --
843 -- End of comments
844
845 PROCEDURE Hierarchy_Import
846 ( errbuf OUT NOCOPY VARCHAR2,
847 retcode OUT NOCOPY VARCHAR2,
848 p_imp_header_id IN NUMBER ,
849 p_org_id IN NUMBER
850 ) IS
851 l_api_name CONSTANT VARCHAR2(30) := 'Hierarchy_Import';
852
853 l_stage_status cn_imp_lines.status_code%TYPE := 'STAGE';
854 l_imp_header cn_imp_headers_pvt.imp_headers_rec_type
855 := cn_imp_headers_pvt.G_MISS_IMP_HEADERS_REC;
856 l_processed_row NUMBER := 0;
857 l_failed_row NUMBER := 0;
858 l_message VARCHAR2(2000);
859 l_error_code VARCHAR2(30);
860 l_header_list VARCHAR2(2000);
861 l_sql_stmt VARCHAR2(2000);
862 err_num NUMBER;
863 l_return_status VARCHAR2(50);
864 l_msg_count NUMBER := 0;
865 l_process_audit_id cn_process_audits.process_audit_id%TYPE;
866
867 l_dimension_id cn_dimensions_all_b.dimension_id%TYPE;
868 l_head_hierarchy_id cn_head_hierarchies.head_hierarchy_id%TYPE;
869 l_dim_hierarchy_id cn_dim_hierarchies.dim_hierarchy_id%TYPE;
870 l_hier_record imp_hier_rec_type;
871 l_base_tbl_id cn_obj_tables_v.table_id%TYPE;
872 l_primary_key_id cn_obj_columns_v.column_id%TYPE;
873 l_hier_value_id cn_obj_columns_v.column_id%TYPE;
874 l_def_base_name cn_hierarchy_nodes.name%TYPE;
875
876 -- cursor to get all record missed required field
877 CURSOR c_miss_required_csr IS
878 SELECT imp_line_id,record_num FROM cn_hierarchy_imp_v
879 WHERE imp_header_id = p_imp_header_id
880 AND status_code = l_stage_status
881 AND (hierarchy_type IS NULL OR BASE_TABLE_NAME IS NULL OR
882 primary_key IS NULL OR hierarchy_value IS NULL OR
883 hierarchy_name IS NULL OR start_date IS NULL OR
884 default_node_flag IS NULL OR node_name IS NULL)
885 ;
886
887 -- cursor to get all record with wrong default_node_flag
888 CURSOR c_err_dflt_node_csr IS
889 SELECT imp_line_id,record_num FROM cn_hierarchy_imp_v
890 WHERE imp_header_id = p_imp_header_id
891 AND status_code = l_stage_status
892 AND default_node_flag <> 'Y'
893 AND default_node_flag <> 'N'
894 ;
895
896 -- Cursor to get distinct base_table_name from stage table
897 CURSOR c_base_table_csr IS
898 SELECT DISTINCT
899 Upper(base_table_name) BASE_TABLE_NAME
900 FROM CN_HIERARCHY_IMP_V
901 WHERE imp_header_id = p_imp_header_id
902 AND status_code = l_stage_status
903 ;
904
905 -- Cursor to get distinct HIERARCHY_TYPE from stage table
906 CURSOR c_hierarchy_type_csr IS
907 SELECT DISTINCT
908 trim(hierarchy_type) HIERARCHY_TYPE
909 FROM CN_HIERARCHY_IMP_V
910 WHERE imp_header_id = p_imp_header_id
911 AND status_code = l_stage_status
912 ;
913
914 l_hierarchy_type_csr c_hierarchy_type_csr%ROWTYPE;
915
916 -- Cursor to get distinct head_hierarchy for one hierarchy_type
917 CURSOR c_head_hierarchy_csr
918 (l_hier_type cn_hierarchy_imp_v.hierarchy_type%TYPE) IS
919 SELECT DISTINCT
920 trim(hierarchy_name) HIERARCHY_NAME,
921 trim(start_date) START_DATE,
922 trim(end_date) end_date
923 FROM CN_HIERARCHY_IMP_V
924 WHERE imp_header_id = p_imp_header_id
925 AND status_code = l_stage_status
926 AND hierarchy_type = l_hier_type
927 ;
928
929 -- Cursor to get distinct dim_hierarchy for one hierarchy_type,head_hier
930 CURSOR c_dim_hierarchy_csr
931 (l_hier_type cn_hierarchy_imp_v.hierarchy_type%TYPE,
932 l_head_hier cn_hierarchy_imp_v.hierarchy_name%TYPE ) IS
933 SELECT DISTINCT
934 trim(start_date) START_DATE,
935 trim(end_date) end_date
936 FROM CN_HIERARCHY_IMP_V
937 WHERE imp_header_id = p_imp_header_id
938 AND status_code = l_stage_status
939 AND hierarchy_type = l_hier_type
940 AND hierarchy_name = l_head_hier
941 ;
942
943 l_dummy NUMBER;
944 l_tmp VARCHAR2(30);
945 l_parent_node_name cn_hierarchy_imp_v.parent_node_name%TYPE;
946
947 BEGIN
948
949 -- Initialize API return status to success
950 l_return_status := FND_API.G_RET_STS_SUCCESS;
951 retcode := 0 ;
952
953 -- Get imp_header info
954 SELECT name, status_code,server_flag,imp_map_id, source_column_num,
955 import_type_code
956 INTO l_imp_header.name ,l_imp_header.status_code ,
957 l_imp_header.server_flag, l_imp_header.imp_map_id,
958 l_imp_header.source_column_num,l_imp_header.import_type_code
959 FROM cn_imp_headers
960 WHERE imp_header_id = p_imp_header_id;
961
962 l_hier_record.imp_header_id := p_imp_header_id;
963
964 -- open process audit batch
965 cn_message_pkg.begin_batch
966 ( x_process_type => l_imp_header.import_type_code,
967 x_parent_proc_audit_id => p_imp_header_id ,
968 x_process_audit_id => l_process_audit_id,
969 x_request_id => null,
970 p_org_id => p_org_id);
971
972 cn_message_pkg.write
973 (p_message_text => 'HIERARCHY: Start Transfer Data. imp_header_id = '
974 || To_char(p_imp_header_id),
975 p_message_type => 'MILESTONE');
976
977 -- Get source column name list and target column dynamic sql statement
978 CN_IMPORT_PVT.build_error_rec
979 (p_imp_header_id => p_imp_header_id,
980 x_header_list => l_header_list,
981 x_sql_stmt => l_sql_stmt);
982
983 -- -----------------------------------------------------------+
984 -- Check for all required field
985 FOR l_miss_required_csr IN c_miss_required_csr LOOP
986 l_failed_row := l_failed_row + 1;
987 l_processed_row := l_processed_row + 1;
988 l_error_code := 'CN_IMP_MISS_REQUIRED';
989 l_message := fnd_message.get_string('CN','CN_IMP_MISS_REQUIRED');
990 CN_IMPORT_PVT.update_imp_lines
991 (p_imp_line_id => l_miss_required_csr.imp_line_id,
992 p_status_code => 'FAIL',
993 p_error_code => l_error_code,
994 p_error_msg => l_message);
995 CN_IMPORT_PVT.update_imp_headers
996 (p_imp_header_id => p_imp_header_id,
997 p_status_code => 'IMPORT_FAIL',
998 p_failed_row => l_failed_row);
999 cn_message_pkg.write
1000 (p_message_text => 'Record ' ||
1001 To_char(l_miss_required_csr.record_num) || ':' || l_message,
1002 p_message_type => 'ERROR');
1003 CN_IMPORT_PVT.write_error_rec
1004 (p_imp_header_id => p_imp_header_id,
1005 p_imp_line_id => l_miss_required_csr.imp_line_id,
1006 p_header_list => l_header_list,
1007 p_sql_stmt => l_sql_stmt);
1008
1009 retcode := 2;
1010 errbuf := l_message;
1011 END LOOP;
1012
1013 -- -----------------------------------------------------------+
1014 -- Check input of DEFAULT_NODE_FLAG must be 'Y' or 'N',
1015 FOR l_err_dflt_node_csr IN c_err_dflt_node_csr LOOP
1016 l_failed_row := l_failed_row + 1;
1017 l_processed_row := l_processed_row + 1;
1018 l_error_code := 'CN_HIER_ERR_NODEFLAG';
1019 l_message := fnd_message.get_string('CN','CN_HIER_ERR_NODEFLAG');
1020 CN_IMPORT_PVT.update_imp_lines
1021 (p_imp_line_id => l_err_dflt_node_csr.imp_line_id,
1022 p_status_code => 'FAIL',
1023 p_error_code => l_error_code,
1024 p_error_msg => l_message);
1025 CN_IMPORT_PVT.update_imp_headers
1026 (p_imp_header_id => p_imp_header_id,
1027 p_status_code => 'IMPORT_FAIL',
1028 p_failed_row => l_failed_row);
1029 cn_message_pkg.write
1030 (p_message_text => 'Record ' ||
1031 To_char(l_err_dflt_node_csr.record_num) || ':' || l_message,
1032 p_message_type => 'ERROR');
1033 CN_IMPORT_PVT.write_error_rec
1034 (p_imp_header_id => p_imp_header_id,
1035 p_imp_line_id => l_err_dflt_node_csr.imp_line_id,
1036 p_header_list => l_header_list,
1037 p_sql_stmt => l_sql_stmt);
1038
1039 retcode := 2;
1040 errbuf := l_message;
1041 END LOOP;
1042
1043 -- -----------------------------------------------------------+
1044 -- Check if have multiple hierarchy for same base table within this import
1045 FOR l_base_table_csr IN c_base_table_csr LOOP
1046 BEGIN
1047 l_hier_record.base_table_name := l_base_table_csr.base_table_name;
1048 SELECT DISTINCT trim(hierarchy_type) hierarchy_type
1049 INTO l_tmp
1050 FROM CN_HIERARCHY_IMP_V
1051 WHERE imp_header_id = p_imp_header_id
1052 AND status_code = l_stage_status
1053 AND BASE_TABLE_NAME = l_base_table_csr.BASE_TABLE_NAME
1054 ;
1055 EXCEPTION
1056 WHEN TOO_MANY_ROWS THEN
1057 l_message := fnd_message.get_string('CN','CN_HIER_MULTI_BTBL');
1058 seterr_imp_hierarchy
1059 (p_hier_record => l_hier_record,
1060 p_status_code => 'FAIL',
1061 p_error_code => 'CN_HIER_MULTI_BTBL',
1062 p_error_msg => l_message,
1063 x_failed_row => l_failed_row,
1064 x_processed_row => l_processed_row);
1065
1066 cn_message_pkg.write
1067 (p_message_text => l_message ,
1068 p_message_type => 'ERROR');
1069 cn_message_pkg.write
1070 (p_message_text =>
1071 ' All record with base table = ' ||
1072 l_hier_record.base_table_name ||
1073 ' are treated as failed records.' ,
1074 p_message_type => 'ERROR');
1075
1076 retcode := 2;
1077 errbuf := l_message;
1078 END;
1079 END LOOP;
1080
1081 -- -----------------------------------------------------------------+
1082 -- ----------------------- Hierarchy Type --------------------------+
1083 -- -----------------------------------------------------------------+
1084 OPEN c_hierarchy_type_csr;
1085 LOOP
1086 FETCH c_hierarchy_type_csr INTO l_hierarchy_type_csr;
1087 EXIT WHEN c_hierarchy_type_csr%notfound;
1088 l_hier_record := G_MISS_IMP_HIER_REC;
1089 l_hier_record.imp_header_id := p_imp_header_id;
1090 l_hier_record.hierarchy_type := l_hierarchy_type_csr.hierarchy_type;
1091
1092 -- Import hierarchy type
1093 Imp_Hierarchy_Type
1094 (p_imp_header_id => p_imp_header_id,
1095 p_hier_record => l_hier_record,
1096 x_dimension_id => l_dimension_id,
1097 x_base_table_id => l_base_tbl_id,
1098 x_primary_key_id => l_primary_key_id,
1099 x_hier_value_id => l_hier_value_id,
1100 x_error_msg => l_message,
1101 x_return_status => l_return_status,
1102 x_failed_row => l_failed_row,
1103 x_processed_row => l_processed_row,
1104 p_org_id => p_org_id);
1105
1106 IF l_return_status <> FND_API.g_ret_sts_success THEN
1107 retcode := 2;
1108 errbuf := l_message;
1109 GOTO end_hier_type_loop;
1110 END IF;
1111
1112 -- ---------------------------------------------------------------+
1113 -- --------------------- Head Hierarchy --------------------------+
1114 -- ---------------------------------------------------------------+
1115 FOR l_head_hierarchy IN
1116 c_head_hierarchy_csr(l_hier_record.hierarchy_type) LOOP
1117
1118 l_hier_record.hierarchy_name := l_head_hierarchy.hierarchy_name;
1119
1120 -- Import head hierarchy
1121 Imp_Head_Hierarchy
1122 (p_imp_header_id => p_imp_header_id,
1123 p_hier_record => l_hier_record,
1124 p_dimension_id => l_dimension_id,
1125 x_head_hierarchy_id => l_head_hierarchy_id,
1126 x_error_msg => l_message,
1127 x_return_status => l_return_status,
1128 x_failed_row => l_failed_row,
1129 x_processed_row => l_processed_row,
1130 p_org_id => p_org_id);
1131
1132 IF l_return_status <> FND_API.g_ret_sts_success THEN
1133 retcode := 2;
1134 errbuf := l_message;
1135 GOTO end_head_hier_loop;
1136 END IF;
1137
1138 FOR l_dim_hierarchy IN
1139 c_dim_hierarchy_csr
1140 (l_hier_record.hierarchy_type,l_hier_record.hierarchy_name) LOOP
1141 -- ---------------------------------------------------------------+
1142 -- --------------------- Dim Hierarchy ---------------------------+
1143 -- ---------------------------------------------------------------+
1144 l_hier_record.start_date := l_dim_hierarchy.start_date;
1145 l_hier_record.end_date := l_dim_hierarchy.end_date;
1146 -- Import dim hierarchy
1147 Imp_Dim_Hierarchy
1148 (p_imp_header_id => p_imp_header_id,
1149 p_hier_record => l_hier_record,
1150 p_head_hierarchy_id => l_head_hierarchy_id,
1151 x_dim_hierarchy_id => l_dim_hierarchy_id,
1152 x_error_msg => l_message,
1153 x_return_status => l_return_status,
1154 x_failed_row => l_failed_row,
1155 x_processed_row => l_processed_row,
1156 p_org_id => p_org_id);
1157
1158 IF l_return_status <> FND_API.g_ret_sts_success THEN
1159 retcode := 2;
1160 errbuf := l_message;
1161 GOTO end_dim_hier_loop;
1162 END IF;
1163 -- ---------------------------------------------------------------+
1164 -- --------------------- Hierarchy Node---------------------------+
1165 -- ---------------------------------------------------------------+
1166 BEGIN
1167 l_parent_node_name := 'temp';
1168 -- Get default base node name from CSV file
1169 SELECT trim(node_name),trim(parent_node_name)
1170 INTO l_def_base_name,l_parent_node_name
1171 FROM cn_hierarchy_imp_v
1172 WHERE imp_header_id = p_imp_header_id
1173 AND status_code = l_stage_status
1174 AND hierarchy_type = l_hier_record.hierarchy_type
1175 AND hierarchy_name = l_hier_record.hierarchy_name
1176 AND start_date = l_hier_record.start_date
1177 AND Nvl(end_date,FND_API.g_miss_char) =
1178 Nvl(l_hier_record.end_date,FND_API.g_miss_char)
1179 AND default_node_flag = 'Y'
1180 ;
1181 -- parent_node_name must be null if default_node_flag = 'Y'
1182 IF l_parent_node_name IS NOT NULL THEN
1183 l_message :=
1184 fnd_message.get_string('CN','CN_HIER_WRONG_DEFNODE');
1185 l_error_code := 'CN_HIER_WRONG_DEFNODE';
1186 seterr_imp_hierarchy
1187 (p_hier_record => l_hier_record,
1188 p_status_code => 'FAIL',
1189 p_error_code => l_error_code,
1190 p_error_msg => l_message,
1191 x_failed_row => l_failed_row,
1192 x_processed_row => l_processed_row);
1193
1194 cn_message_pkg.write
1195 (p_message_text => l_message ,
1196 p_message_type => 'ERROR');
1197 cn_message_pkg.write
1198 (p_message_text =>
1199 ' All record with hierarchy type = '
1200 ||l_hier_record.hierarchy_type
1201 || ' and Hierarchy name = ' || l_hier_record.hierarchy_name
1202 || ' Start Date = ' || l_hier_record.start_date
1203 || ' End Date = ' || l_hier_record.end_date
1204 || ' are treated as failed records.' ,
1205 p_message_type => 'ERROR');
1206 retcode := 2;
1207 errbuf := l_message;
1208 GOTO end_dim_hier_loop;
1209 END IF;
1210
1211 cn_message_pkg.debug('Def base node = ' || l_def_base_name);
1212
1213 EXCEPTION
1214 WHEN no_data_found THEN
1215 -- get default name from DB
1216 SELECT name INTO l_def_base_name
1217 FROM cn_hierarchy_nodes
1218 WHERE dim_hierarchy_id = l_dim_hierarchy_id and org_id=p_org_id
1219 AND external_id IS NULL ;
1220
1221 WHEN too_many_rows THEN
1222 l_message :=
1223 fnd_message.get_string('CN','CN_HIER_MULTI_DEFNODE');
1224 l_error_code := 'CN_HIER_MULTI_DEFNODE';
1225 seterr_imp_hierarchy
1226 (p_hier_record => l_hier_record,
1227 p_status_code => 'FAIL',
1228 p_error_code => l_error_code,
1229 p_error_msg => l_message,
1230 x_failed_row => l_failed_row,
1231 x_processed_row => l_processed_row);
1232
1233 cn_message_pkg.write
1234 (p_message_text => l_message ,
1235 p_message_type => 'ERROR');
1236 cn_message_pkg.write
1237 (p_message_text =>
1238 ' All record with hierarchy type = '
1239 ||l_hier_record.hierarchy_type
1240 || ' and Hierarchy name = ' || l_hier_record.hierarchy_name
1241 || ' Start Date = ' || l_hier_record.start_date
1242 || ' End Date = ' || l_hier_record.end_date
1243 || ' are treated as failed records.' ,
1244 p_message_type => 'ERROR');
1245 retcode := 2;
1246 errbuf := l_message;
1247 GOTO end_dim_hier_loop;
1248 END;
1249
1250 -- Import hierarchy nodes
1251 Imp_Hierarchy_Node
1252 (p_imp_header_id => p_imp_header_id,
1253 p_hier_record => l_hier_record,
1254 p_dim_hierarchy_id => l_dim_hierarchy_id,
1255 p_def_base_name => l_def_base_name,
1256 p_header_list => l_header_list,
1257 p_sql_stmt => l_sql_stmt,
1258 x_error_msg => l_message,
1259 x_return_status => l_return_status,
1260 x_failed_row => l_failed_row,
1261 x_processed_row => l_processed_row,
1262 p_org_id => p_org_id);
1263
1264 IF l_return_status <> FND_API.g_ret_sts_success THEN
1265 retcode := 2;
1266 errbuf := l_message;
1267 GOTO end_dim_hier_loop;
1268 END IF;
1269
1270 << end_dim_hier_loop >>
1271 NULL ;
1272 END LOOP; -- end Dim Hierarchy Loop
1273
1274 << end_head_hier_loop >>
1275 NULL;
1276 END LOOP; -- end Head Hierarchy Loop
1277
1278 << end_hier_type_loop >>
1279 NULL;
1280 END LOOP; -- end c_hierarchy_type_csr
1281 IF c_hierarchy_type_csr%rowcount = 0 THEN
1282 l_processed_row := 0;
1283 END IF;
1284 CLOSE c_hierarchy_type_csr;
1285 IF l_failed_row = 0 AND retcode = 0 THEN
1286 -- update update_imp_headers
1287 CN_IMPORT_PVT.update_imp_headers
1288 (p_imp_header_id => p_imp_header_id,
1289 p_status_code => 'COMPLETE',
1290 p_processed_row => l_processed_row,
1291 p_failed_row => l_failed_row);
1292 END IF;
1293 cn_message_pkg.write
1294 (p_message_text => 'HIERARCHY: End Transfer Data. imp_header_id = ' ||
1295 To_char(p_imp_header_id),
1296 p_message_type => 'MILESTONE');
1297
1298 -- close process batch
1299 cn_message_pkg.end_batch(l_process_audit_id);
1300
1301 -- Commit all imports
1302 COMMIT;
1303
1304 EXCEPTION
1305 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1306 retcode := 2 ;
1307 cn_message_pkg.end_batch(l_process_audit_id);
1308 FND_MSG_PUB.count_and_get
1309 (p_count => l_msg_count ,
1310 p_data => errbuf ,
1311 p_encoded => FND_API.G_FALSE
1312 );
1313
1314 WHEN OTHERS THEN
1315 err_num := SQLCODE;
1316 IF err_num = -6501 THEN
1317 retcode := 2 ;
1318 errbuf := fnd_program.message;
1319 ELSE
1320 retcode := 2 ;
1321 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1322 THEN
1323 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1324 END IF;
1325 FND_MSG_PUB.count_and_get
1326 (p_count => l_msg_count ,
1327 p_data => errbuf ,
1328 p_encoded => FND_API.G_FALSE
1329 );
1330 END IF;
1331 cn_message_pkg.set_error(l_api_name,errbuf);
1332 cn_message_pkg.end_batch(l_process_audit_id);
1333
1334 END Hierarchy_Import;
1335
1336 END CN_IMP_HIERARCHY_PVT;