1 PACKAGE BODY EDW_FLEX_MAPPING as
2 /* $Header: EDWSRFLB.pls 115.5 2002/12/05 22:19:00 arsantha ship $ */
3
4 /*===========================================================================*/
5
6 FUNCTION GET_VALUE( P_fact_name IN VARCHAR2,
7 P_dim_name IN VARCHAR2,
8 P_ccid IN NUMBER,
9 P_set_of_books_id IN VARCHAR2,
10 P_structure_id IN NUMBER)
11 RETURN VARCHAR2 IS
12
13 l_application_column_name VARCHAR2(30);
14 l_value_set_id NUMBER(10);
15 l_instance_code VARCHAR2(10);
16 l_segment_value VARCHAR2(25);
17 l_sob NUMBER;
18 l_edw_sob NUMBER;
19 l_parent_equi_sob NUMBER;
20 l_parent_sob NUMBER;
21 l_instance VARCHAR2(30);
22 BEGIN
23
24 l_sob := p_set_of_books_id;
25
26 begin
27
28 select loc.edw_set_of_books_id into l_edw_sob
29 from edw_local_set_of_books loc,
30 edw_local_instance inst
31 where P_set_of_books_id = loc.set_of_books_id
32 AND loc.instance = inst.instance_code;
33
34 Exception when no_data_found then
35 return 'NA_EDW';
36 end;
37
38 begin
39
40 select EQUI_SET_OF_BOOKS_ID into l_parent_equi_sob
41 from edw_local_equi_set_of_books
42 where l_edw_sob = EDW_SET_OF_BOOKS_ID;
43
44 select set_of_books_id, instance into l_parent_sob, l_instance
45 from edw_local_set_of_books loc
46 where edw_set_of_books_id = l_parent_equi_sob;
47
48 Exception
49 when no_data_found then
50 null;
51 when others then
52 raise;
53 END;
54 --------------------------
55 IF (P_fact_name IS NULL) OR
56 (P_dim_name IS NULL) OR
57 (P_ccid IS NULL) OR
58 (P_set_of_books_id IS NULL) OR
59 (P_structure_id IS NULL)
60 then
61 RETURN 'NA_EDW';
62 END IF;
63
64
65 SELECT
66 map.application_column_name,
67 map.value_set_id,
68 map.instance_code
69 INTO
70 l_application_column_name,
71 l_value_set_id,
72 l_instance_code
73 FROM
74 edw_local_instance inst,
75 edw_local_flex_seg_mappings_v map,
76 edw_local_fact_flex_fk_maps_v fact
77 WHERE
78 fact.fact_short_name = p_fact_name
79 -- AND fact.fk_physical_name = p_fk_name
80 AND map.dimension_short_name = fact.dimension_short_name
81 AND fact.dimension_short_name = p_dim_name
82 AND map.instance_code = inst.instance_code
83 AND map.structure_num = p_structure_id
84 AND upper(fact.enabled_flag) = 'Y';
85
86 IF l_parent_equi_sob IS NOT NULL THEN
87 l_instance_code := l_instance;
88 l_sob := l_parent_sob;
89 END IF;
90
91 -- We will now put a huge IF statement in order not to use dynamic sql
92
93
94 IF (l_application_column_name = 'SEGMENT1') THEN
95 SELECT segment1
96 INTO l_segment_value
97 FROM gl_code_combinations
98 WHERE code_combination_id = p_ccid
99 AND chart_of_accounts_id = p_structure_id;
100
101 ELSIF (l_application_column_name = 'SEGMENT2') THEN
102 SELECT segment2
103 INTO l_segment_value
104 FROM gl_code_combinations
105 WHERE code_combination_id = p_ccid
106 AND chart_of_accounts_id = p_structure_id;
107
108 ELSIF (l_application_column_name = 'SEGMENT3') THEN
109 SELECT segment3
110 INTO l_segment_value
111 FROM gl_code_combinations
112 WHERE code_combination_id = p_ccid
113 AND chart_of_accounts_id = p_structure_id;
114
115 ELSIF (l_application_column_name = 'SEGMENT4') THEN
116 SELECT segment4
117 INTO l_segment_value
118 FROM gl_code_combinations
119 WHERE code_combination_id = p_ccid
120 AND chart_of_accounts_id = p_structure_id;
121
122 ELSIF (l_application_column_name = 'SEGMENT5') THEN
123 SELECT segment5
124 INTO l_segment_value
125 FROM gl_code_combinations
126 WHERE code_combination_id = p_ccid
127 AND chart_of_accounts_id = p_structure_id;
128
129 ELSIF (l_application_column_name = 'SEGMENT6') THEN
130 SELECT segment6
131 INTO l_segment_value
132 FROM gl_code_combinations
133 WHERE code_combination_id = p_ccid
134 AND chart_of_accounts_id = p_structure_id;
135
136 ELSIF (l_application_column_name = 'SEGMENT7') THEN
137 SELECT segment7
138 INTO l_segment_value
139 FROM gl_code_combinations
140 WHERE code_combination_id = p_ccid
141 AND chart_of_accounts_id = p_structure_id;
142
143 ELSIF (l_application_column_name = 'SEGMENT8') THEN
144 SELECT segment8
145 INTO l_segment_value
146 FROM gl_code_combinations
147 WHERE code_combination_id = p_ccid
148 AND chart_of_accounts_id = p_structure_id;
149
150 ELSIF (l_application_column_name = 'SEGMENT9') THEN
151 SELECT segment9
152 INTO l_segment_value
153 FROM gl_code_combinations
154 WHERE code_combination_id = p_ccid
155 AND chart_of_accounts_id = p_structure_id;
156
157 ELSIF (l_application_column_name = 'SEGMENT10') THEN
158 SELECT segment10
159 INTO l_segment_value
160 FROM gl_code_combinations
161 WHERE code_combination_id = p_ccid
162 AND chart_of_accounts_id = p_structure_id;
163
164 ELSIF (l_application_column_name = 'SEGMENT11') THEN
165 SELECT segment11
166 INTO l_segment_value
167 FROM gl_code_combinations
168 WHERE code_combination_id = p_ccid
169 AND chart_of_accounts_id = p_structure_id;
170
171 ELSIF (l_application_column_name = 'SEGMENT12') THEN
172 SELECT segment12
173 INTO l_segment_value
174 FROM gl_code_combinations
175 WHERE code_combination_id = p_ccid
176 AND chart_of_accounts_id = p_structure_id;
177
178 ELSIF (l_application_column_name = 'SEGMENT13') THEN
179 SELECT segment13
180 INTO l_segment_value
181 FROM gl_code_combinations
182 WHERE code_combination_id = p_ccid
183 AND chart_of_accounts_id = p_structure_id;
184
185 ELSIF (l_application_column_name = 'SEGMENT14') THEN
186 SELECT segment14
187 INTO l_segment_value
188 FROM gl_code_combinations
189 WHERE code_combination_id = p_ccid
190 AND chart_of_accounts_id = p_structure_id;
191
192 ELSIF (l_application_column_name = 'SEGMENT15') THEN
193 SELECT segment15
194 INTO l_segment_value
195 FROM gl_code_combinations
196 WHERE code_combination_id = p_ccid
197 AND chart_of_accounts_id = p_structure_id;
198
199 ELSIF (l_application_column_name = 'SEGMENT16') THEN
200 SELECT segment16
201 INTO l_segment_value
202 FROM gl_code_combinations
203 WHERE code_combination_id = p_ccid
204 AND chart_of_accounts_id = p_structure_id;
205
206 ELSIF (l_application_column_name = 'SEGMENT17') THEN
207 SELECT segment17
208 INTO l_segment_value
209 FROM gl_code_combinations
210 WHERE code_combination_id = p_ccid
211 AND chart_of_accounts_id = p_structure_id;
212
213 ELSIF (l_application_column_name = 'SEGMENT18') THEN
214 SELECT segment18
215 INTO l_segment_value
216 FROM gl_code_combinations
217 WHERE code_combination_id = p_ccid
218 AND chart_of_accounts_id = p_structure_id;
219
220 ELSIF (l_application_column_name = 'SEGMENT19') THEN
221 SELECT segment19
222 INTO l_segment_value
223 FROM gl_code_combinations
224 WHERE code_combination_id = p_ccid
225 AND chart_of_accounts_id = p_structure_id;
226
227 ELSIF (l_application_column_name = 'SEGMENT20') THEN
228 SELECT segment20
229 INTO l_segment_value
230 FROM gl_code_combinations
231 WHERE code_combination_id = p_ccid
232 AND chart_of_accounts_id = p_structure_id;
233 ELSIF (l_application_column_name = 'SEGMENT21') THEN
234 SELECT segment21
235 INTO l_segment_value
236 FROM gl_code_combinations
237 WHERE code_combination_id = p_ccid
238 AND chart_of_accounts_id = p_structure_id;
239 ELSIF (l_application_column_name = 'SEGMENT22') THEN
240 SELECT segment22
241 INTO l_segment_value
242 FROM gl_code_combinations
243 WHERE code_combination_id = p_ccid
244 AND chart_of_accounts_id = p_structure_id;
245 ELSIF (l_application_column_name = 'SEGMENT23') THEN
246 SELECT segment23
247 INTO l_segment_value
248 FROM gl_code_combinations
249 WHERE code_combination_id = p_ccid
250 AND chart_of_accounts_id = p_structure_id;
251 ELSIF (l_application_column_name = 'SEGMENT24') THEN
252 SELECT segment24
253 INTO l_segment_value
254 FROM gl_code_combinations
255 WHERE code_combination_id = p_ccid
256 AND chart_of_accounts_id = p_structure_id;
257 ELSIF (l_application_column_name = 'SEGMENT25') THEN
258 SELECT segment25
259 INTO l_segment_value
260 FROM gl_code_combinations
261 WHERE code_combination_id = p_ccid
262 AND chart_of_accounts_id = p_structure_id;
263 ELSIF (l_application_column_name = 'SEGMENT26') THEN
264 SELECT segment26
265 INTO l_segment_value
266 FROM gl_code_combinations
267 WHERE code_combination_id = p_ccid
268 AND chart_of_accounts_id = p_structure_id;
269 ELSIF (l_application_column_name = 'SEGMENT27') THEN
270 SELECT segment27
271 INTO l_segment_value
272 FROM gl_code_combinations
273 WHERE code_combination_id = p_ccid
274 AND chart_of_accounts_id = p_structure_id;
275 ELSIF (l_application_column_name = 'SEGMENT28') THEN
276 SELECT segment28
277 INTO l_segment_value
278 FROM gl_code_combinations
279 WHERE code_combination_id = p_ccid
280 AND chart_of_accounts_id = p_structure_id;
281 ELSIF (l_application_column_name = 'SEGMENT29') THEN
282 SELECT segment29
283 INTO l_segment_value
284 FROM gl_code_combinations
285 WHERE code_combination_id = p_ccid
286 AND chart_of_accounts_id = p_structure_id;
287 ELSIF (l_application_column_name = 'SEGMENT30') THEN
288 SELECT segment30
289 INTO l_segment_value
290 FROM gl_code_combinations
291 WHERE code_combination_id = p_ccid
292 AND chart_of_accounts_id = p_structure_id;
293
294 END IF;
295
296
297 RETURN l_segment_value||'-'||l_sob||'-'||l_instance_code;
298
299
300 EXCEPTION
301 WHEN NO_DATA_FOUND THEN
302 RETURN 'NA_EDW';
303 WHEN OTHERS THEN
304 IF (SQLCODE <> -20001) THEN
305 return 'Error!';
306 -- Provide debug information
307 -- RETURN "ERROR!! "||debug_info
308 END IF;
309 -- APP_EXCEPTION.RAISE_EXCEPTION;
310
311 END get_value;
312
313
314 END EDW_FLEX_MAPPING;
[Home] [Help]