[Home] [Help]
PACKAGE BODY: APPS.BIS_PMF_MIGRATE_DIMENSIONS_PVT
Source
1 PACKAGE BODY BIS_PMF_MIGRATE_DIMENSIONS_PVT AS
2 /* $Header: BISVMDLB.pls 115.11 2002/12/16 10:26:01 rchandra ship $ */
3 /*
4 REM +=======================================================================+
5 REM | Copyright (c) 1998 Oracle Corporation, Redwood Shores, CA, USA |
6 REM | All rights reserved. |
7 REM +=======================================================================+
8 REM | FILENAME |
9 REM | BISVMDLS.pls |
10 REM | |
11 REM | DESCRIPTION |
12 REM | Private API for getting Dimensions from EDW and populating the
13 REM | corresponding BIS Tables .
14 REM | Issues : Error Handling
15 REM | Mismatch in the lengths of name, description for both entities
16 REM | |
17 REM | HISTORY |
18 REM | August-2000 amkulkar Creation
19 REM +=======================================================================+
20 */
21 --
22 G_PKG_NAME CONSTANT VARCHAR2(30):= 'BIS_PMF_MIGRATE_DIMENSIONS_PVT';
23 PROCEDURE MIGRATE_EDW_DIMENSIONS
24 (ERRBUF OUT NOCOPY VARCHAR2
25 ,RETCODE OUT NOCOPY VARCHAR2
26 )
27 IS
28 --This needs to be dynamic SQL as it needs to get installed with BIS
29 /*CURSOR c_dims IS
30 SELECT dim.name dimshortname, dim.longname dimname, dim.description dimdesc
31 FROM cmpwbdimension_v dim;
32
33 CURSOR c_dim_lvls IS
34 SELECT dim.name dimshortname, dim.longname dimname, dim.description dimdesc
35 ,lvl.name lvlshortname, lvl.longname lvlname ,lvl.description lvldesc
36 FROM
37 cmpwbdimension_v dim, cmplevel_v lvl
38 WHERE
39 lvl.dimension = dim.elementid
40 ;
41 */
42 l_dimension_rec BIS_DIMENSION_PUB.Dimension_Rec_Type;
43 l_dimension_level_rec BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
44 l_return_status VARCHAR2(20);
45 l_error_tbl BIS_UTILITIES_PUB.ERROR_TBL_TYPE;
46 l_dim_sql VARCHAR2(32000);
47 l_dimlvl_sql VARCHAR2(32000);
48 l_dimname VARCHAR2(32000);
49 l_dimdesc VARCHAR2(32000);
50 l_dimshortname VARCHAR2(32000);
51 l_lvlname VARCHAR2(32000);
52 l_lvlshortname VARCHAR2(32000);
53 l_lvldesc VARCHAR2(32000);
54 TYPE DIM_CURSOR_TYPE IS REF CURSOR;
55 TYPE DIM_LEVEL_CURSOR_TYPE IS REF CURSOR;
56 l_dim_cursor DIM_CURSOR_TYPE;
57 l_dimlevel_Cursor DIM_LEVEL_CURSOR_TYPE;
58 l_count_dims NUMBER := 0;
59 l_count_levels NUMBER := 0;
60 BEGIN
61 -- Fix for 2214178 starts
62 /*
63 l_dim_sql := ' SELECT dim.name dimshortname, dim.longname dimname, dim.description dimdesc '||
64 ' FROM cmpwbdimension_v dim '||
65 ' WHERE dim.name NOT IN '|| G_LEVEL_EXCLUSION_STRING;
66 l_dimlvl_sql :=' SELECT dim.name dimshortname, dim.longname dimname, dim.description dimdesc '||
67 ' ,lvl.name lvlshortname, lvl.longname lvlname ,lvl.description lvldesc ' ||
68 ' FROM ' ||
69 ' cmpwbdimension_v dim, cmplevel_v lvl ' ||
70 ' WHERE '||
71 ' lvl.dimension = dim.elementid '||
72 ' AND dim.name NOT IN ' || G_LEVEL_EXCLUSION_STRING ;
73 */
74 l_dim_sql := ' SELECT dim.DIM_NAME dimshortname, dim.DIM_LONG_NAME dimname, dim.DIM_DESCRIPTION dimdesc '||
75 ' FROM edw_dimensions_md_v dim '||
76 ' WHERE dim.DIM_NAME NOT IN '|| G_LEVEL_EXCLUSION_STRING;
77
78 l_dimlvl_sql :=' SELECT dim.DIM_NAME dimshortname, dim.DIM_LONG_NAME dimname, dim.DIM_DESCRIPTION dimdesc '||
79 ' ,lvl.LEVEL_NAME lvlshortname, lvl.LEVEL_LONG_NAME lvlname ,lvl.description lvldesc ' ||
80 ' FROM ' ||
81 ' edw_dimensions_md_v dim, edw_levels_md_v lvl ' ||
82 ' WHERE '||
83 ' lvl.DIM_ID = dim.DIM_ID '||
84 ' AND dim.DIM_NAME NOT IN ' || G_LEVEL_EXCLUSION_STRING ;
85
86 -- Fix for 2214178 ends
87
88 --Loop thru the CURSOR get all the records and Load BIS_DIMENSIONS
89 OPEN l_dim_cursor FOR l_dim_sql;
90 LOOP
91 --FOR c_rec IN c_dims LOOP
92 FETCH l_dim_cursor INTO l_dimshortname, l_dimname, l_dimdesc;
93 EXIT WHEN l_dim_cursor%NOTFOUND;
94 l_count_Dims := l_count_Dims+1;
95 l_return_status := NULL;
96 l_dimension_rec.dimension_short_name := l_dimshortname;
97 l_dimension_rec.dimension_name := substr(l_dimname,1,80);
98 l_dimension_rec.description := substr(l_dimdesc,1,240);
99 --Call the API to load the dimension
100 BIS_DIMENSION_PUB.LOAD_DIMENSION(
101 p_api_version => 1.0
102 ,p_Commit => FND_API.G_TRUE
103 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
104 ,p_dimension_rec => l_dimension_rec
105 ,p_owner => BIS_UTILITIES_PUB.G_CUSTOM_OWNER
106 ,x_return_status => l_return_Status
107 ,x_error_tbl => l_error_tbl
108 );
109 END LOOP;
110 OPEN l_dimlevel_cursor FOR l_dimlvl_sql;
111 --FOR c_rec IN c_dim_lvls LOOP
112 LOOP
113 FETCH l_dimlevel_cursor INTO l_Dimshortname, l_dimname, l_dimdesc, l_lvlshortname,
114 l_lvlname, l_lvldesc;
115 EXIT WHEN l_dimlevel_cursor%NOTFOUND;
116 l_count_levels := l_count_levels+1;
117 l_return_status := NULL;
118 l_dimension_level_rec.dimension_short_name := l_dimshortname;
119 l_dimension_level_rec.dimension_name := substr(l_dimname,1,80);
120 l_dimension_level_rec.dimension_level_short_name := l_lvlshortname;
121 l_dimension_level_rec.dimension_level_name := substr(l_lvlname,1,80);
122 l_dimension_level_rec.description := substr(l_lvldesc,1,240);
123 l_dimension_level_rec.level_values_view_name := null;--'BIS_'||c_rec.lvlshortname||'_V';
124 l_Dimension_level_rec.where_clause := null;
125 l_dimension_level_rec.source := G_EDW;
126 BIS_DIMENSION_LEVEL_PUB.LOAD_DIMENSION_LEVEL(
127 p_api_version => 1.0
128 ,p_commit => FND_API.G_TRUE
129 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
130 ,p_dimension_level_rec => l_dimension_level_rec
131 ,p_owner => BIS_UTILITIES_PUB.G_CUSTOM_OWNER
132 ,x_return_status => l_return_status
133 ,x_error_tbl => l_error_tbl
134 );
135
136 END LOOP;
137 FND_MESSAGE.SET_NAME('BIS', 'BIS_DIM_COUNT');
138 FND_MESSAGE.SET_TOKEN('DIMCOUNT', l_count_dims);
139 FND_MESSAGE.SET_TOKEN('DIMLEVELCOUNT', l_count_levels);
140 errbuf := FND_MESSAGE.GET;
141 EXCEPTION
142 WHEN FND_API.G_EXC_ERROR THEN
143 RETCODE := 1;
144 ERRBUF := SQLERRM;
145 RETURN;
146 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
147 RETCODE := 1;
148 ERRBUF := SQLERRM;
149 RETURN;
150 WHEN OTHERS THEN
151 RETCODE := 1;
152 BIS_UTILITIES_PVT.Add_Error_Message
153 ( p_error_msg_id => SQLCODE
154 , p_error_description => SQLERRM
155 , p_error_proc_name => G_PKG_NAME
156 );
157 RETURN;
158
159 END;
160 END BIS_PMF_MIGRATE_DIMENSIONS_PVT;