[Home] [Help]
PACKAGE BODY: APPS.MSD_CS_DFN_UTL
Source
1 package body msd_cs_dfn_utl as
2 /* $Header: msdcsutb.pls 115.9 2002/12/03 20:28:01 pinamati ship $ */
3
4 Procedure populate_column_defn_array (p_cs_definition_id in number, p_cs_dfn_clmn_map_list out nocopy G_TYP_CS_DEFN_CLMN_MAP_LIST) is
5
6 cursor c1( p_cs_definition_id number) is
7 select * from msd_cs_defn_column_dtls
8 where cs_definition_id = p_cs_definition_id;
9
10 l_placeholder number:=0;
11 l_counter number:=6;
12 l_map_list G_TYP_CS_DEFN_CLMN_MAP_LIST;
13 Begin
14 /*
15 initialize first six elements of array
16 */
17 l_map_list := G_TYP_CS_DEFN_CLMN_MAP_LIST( null);
18
19 For c1_rec in c1(p_cs_definition_id)
20 loop
21
22 /* if c1_rec.column_identifier = 'PRD_LEVEL_ID' then
23 l_placeholder := C_PRD_LEVEL_ID;
24 elsif c1_rec.column_identifier = 'ORG_LEVEL_ID' then
25 l_placeholder := C_ORG_LEVEL_ID;
26 elsif c1_rec.column_identifier = 'GEO_LEVEL_ID' then
27 l_placeholder := C_GEO_LEVEL_ID;
28 elsif c1_rec.column_identifier = 'CHN_LEVEL_ID' then
29 l_placeholder := C_CHN_LEVEL_ID;
30 elsif c1_rec.column_identifier = 'REP_LEVEL_ID' then
31 l_placeholder := C_REP_LEVEL_ID;
32 elsif c1_rec.column_identifier = 'CUS_LEVEL_ID' then
33 l_placeholder := C_CUS_LEVEL_ID;
34 else
35 l_map_list.extend;
36 l_counter := l_counter + 1;
37 l_placeholder := l_counter;
38 end if;
39
40 */
41 l_map_list.extend;
42 l_placeholder := l_placeholder + 1;
43 l_map_list(l_placeholder).table_column := c1_rec.table_column;
44 l_map_list(l_placeholder).source_view_column_name := c1_rec.source_view_column_name;
45 /* l_map_list(l_placeholder).planning_view_column_name := c1_rec.planning_view_column_name; */
46 l_map_list(l_placeholder).column_identifier := c1_rec.column_identifier;
47 /* l_map_list(l_placeholder).alt_key_flag := c1_rec.alt_key_flag; */
48 l_map_list(l_placeholder).index_cntr := substr(c1_rec.table_column, instr(c1_rec.table_column, '_') + 1);
49
50 end loop;
51 p_cs_dfn_clmn_map_list := l_map_list;
52 End;
53
54 Procedure conv_cs_rec_to_array(
55 p_cs_rec in out nocopy G_TYP_ARRAY_VARCHAR,
56 p_attribute1 in varchar2, p_attribute2 in varchar2,
57 p_attribute3 in varchar2, p_attribute4 in varchar2,
58 p_attribute5 in varchar2, p_attribute6 in varchar2,
59 p_attribute7 in varchar2, p_attribute8 in varchar2,
60 p_attribute9 in varchar2, p_attribute10 in varchar2,
61 p_attribute11 in varchar2, p_attribute12 in varchar2,
62 p_attribute13 in varchar2, p_attribute14 in varchar2,
63 p_attribute15 in varchar2, p_attribute16 in varchar2,
64 p_attribute17 in varchar2, p_attribute18 in varchar2,
65 p_attribute19 in varchar2, p_attribute20 in varchar2,
66 p_attribute21 in varchar2, p_attribute22 in varchar2,
67 p_attribute23 in varchar2, p_attribute24 in varchar2,
68 p_attribute25 in varchar2, p_attribute26 in varchar2,
69 p_attribute27 in varchar2, p_attribute28 in varchar2,
70 p_attribute29 in varchar2, p_attribute30 in varchar2,
71 p_attribute31 in varchar2, p_attribute32 in varchar2,
72 p_attribute33 in varchar2, p_attribute34 in varchar2,
73 p_attribute35 in varchar2, p_attribute36 in varchar2,
74 p_attribute37 in varchar2, p_attribute38 in varchar2,
75 p_attribute39 in varchar2, p_attribute40 in varchar2,
76 p_attribute41 in varchar2, p_attribute42 in varchar2,
77 p_attribute43 in varchar2, p_attribute44 in varchar2,
78 p_attribute45 in varchar2, p_attribute46 in varchar2,
79 p_attribute47 in varchar2, p_attribute48 in varchar2,
80 p_attribute49 in varchar2, p_attribute50 in varchar2,
81 p_attribute51 in varchar2, p_attribute52 in varchar2,
82 p_attribute53 in varchar2, p_attribute54 in varchar2,
83 p_attribute55 in varchar2, p_attribute56 in varchar2,
84 p_attribute57 in varchar2, p_attribute58 in varchar2,
85 p_attribute59 in varchar2, p_attribute60 in varchar2) is
86 Begin
87
88 p_cs_rec := G_TYP_ARRAY_VARCHAR(
89 p_attribute1 ,p_attribute2 , p_attribute3 ,p_attribute4 ,
90 p_attribute5 ,p_attribute6 , p_attribute7 ,p_attribute8 ,
91 p_attribute9 ,p_attribute10 , p_attribute11 ,p_attribute12 ,
92 p_attribute13 ,p_attribute14 , p_attribute15 ,p_attribute16 ,
93 p_attribute17 ,p_attribute18 , p_attribute19 ,p_attribute20 ,
94 p_attribute21 ,p_attribute22 , p_attribute23 ,p_attribute24 ,
95 p_attribute25 ,p_attribute26 , p_attribute27 ,p_attribute28 ,
96 p_attribute29 ,p_attribute30 , p_attribute31 ,p_attribute32 ,
97 p_attribute33 ,p_attribute34 , p_attribute35 ,p_attribute36 ,
98 p_attribute37 ,p_attribute38 , p_attribute39 ,p_attribute40 ,
99 p_attribute41 ,p_attribute42 , p_attribute43 ,p_attribute44 ,
100 p_attribute45 ,p_attribute46 , p_attribute47 ,p_attribute48 ,
101 p_attribute49 ,p_attribute50 , p_attribute51 ,p_attribute52 ,
102 p_attribute53 ,p_attribute54 , p_attribute55 ,p_attribute56 ,
103 p_attribute57 ,p_attribute58 , p_attribute59 ,p_attribute60
104 );
105 End;
106
107 Function get_dim_desc ( p_dim_code in varchar2) return varchar2 is
108
109 cursor c1 is
110 select meaning from fnd_lookup_values_vl
111 where lookup_type = 'MSD_DIMENSIONS' and
112 lookup_code = p_dim_code;
113
114 l_ret varchar2(80);
115
116 Begin
117
118 open c1;
119 fetch c1 into l_ret;
120 close c1;
121
122 return nvl(l_ret, p_dim_code);
123
124 END;
125
126 Function get_level_id ( p_dim_code in varchar2, p_level_name in varchar2) return number is
127
128 cursor c_lvl is
129 select level_id
130 from msd_levels
131 where dimension_code = p_dim_code and level_name = p_level_name;
132
133 l_ret number;
134
135 Begin
136
137 if p_dim_code = 'TIM' then
138 /* Get Level name from lookup */
139 l_ret := p_level_name;
140
141 else
142
143 open c_lvl;
144 fetch c_lvl into l_ret;
145 close c_lvl;
146
147 end if;
148 --
149 return l_ret;
150 End;
151
152 Function get_level_name ( p_dim_code in varchar2, p_level_id varchar2) return varchar2 is
153
154 cursor c_lvl is
155 select level_name
156 from msd_levels
157 where dimension_code = p_dim_code and level_id = p_level_id;
158
159 l_ret varchar2(80);
160
161 Begin
162
163 if p_dim_code = 'TIM' then
164 /* Get Level name from lookup */
165 l_ret := p_level_id;
166
167 else
168
169 open c_lvl;
170 fetch c_lvl into l_ret;
171 close c_lvl;
172
173 end if;
174 --
175 return l_ret;
176 End;
177
178 Function get_level_desc ( p_dim_code in varchar2, p_level_id varchar2) return varchar2 is
179
180 cursor c_tim is
181 select meaning
182 from fnd_lookup_values_vl
183 where lookup_type = 'MSD_PERIOD_TYPE' and
184 lookup_code = p_level_id;
185
186 cursor c_lvl is
187 select description
188 from msd_levels_v
189 where owning_dimension_code = p_dim_code and level_id = p_level_id;
190
191 l_ret varchar2(80);
192
193 Begin
194
195 if p_dim_code = 'TIM' then
196 /* Get Level name from lookup */
197 open c_tim;
198 fetch c_tim into l_ret;
199 close c_tim;
200
201 else
202
203 open c_lvl;
204 fetch c_lvl into l_ret;
205 close c_lvl;
206
207 end if;
208 --
209 return l_ret;
210 End;
211
212
213 Function get_planning_server_clmn ( p_cs_definition_id in varchar2, p_column_identifier in varchar2) return varchar2 is
214
215 cursor c is
216 select planning_view_column_name
217 from msd_cs_defn_column_dtls_v
218 where cs_definition_id = p_cs_definition_id and column_identifier = p_column_identifier;
219
220
221 l_ret varchar2(200);
222
223 Begin
224
225 if (p_cs_definition_id is null OR p_column_identifier is null) then
226 l_ret := null;
227 else
228
229 open c;
230 fetch c into l_ret;
231 close c;
232
233 end if;
234
235 return l_ret;
236 End;
237
238
239 End;