[Home] [Help]
PACKAGE BODY: APPS.FII_FLEX_MAPPING
Source
1 PACKAGE BODY FII_FLEX_MAPPING as
2 /* $Header: FIICAFXB.pls 120.0 2002/08/24 04:49:52 appldev noship $ */
3
4 /***********************************************************/
5 /* EDW_LOCAL_FLEX_SEG_MAPPINGS cache */
6 /***********************************************************/
7 type map_cache_item is table
8 of varchar2(20)
9 index by binary_integer;
10 g_mc_structure map_cache_item;
11 g_mc_current_structure_id number(15) := -1;
12 g_mc_current_structure varchar2(20) := null;
13
14 /***********************************************************/
15 /* EDW_LOCAL_SET_OF_BOOKS cache */
16 /***********************************************************/
17 type valid_set_of_books_cache is table
18 of varchar2(40)
19 index by binary_integer;
20 g_cache_sets_of_books valid_set_of_books_cache;
21 g_curr_set_of_books_id binary_integer := null;
22 g_curr_concat varchar2(40) := null;
23
24 /***********************************************************/
25 /* GL_CODE_COMBINATIONS cache */
26 /***********************************************************/
27 g_cc_current_ccid number(15) := -1;
28 g_cc_current_segments varchar2(250);
29 g_local_instance varchar2(30);
30 g_mapping_fact varchar2(30) := null;
31
32
33 /***********************************************************/
34 /* function to retrieve foreign key */
35 /***********************************************************/
36 function get_fk (p_ccid in number,
37 p_set_of_books_id in varchar2,
38 p_structure_id in number,
39 p_seg_number in number)
40 return varchar2 is
41 l_tmp VARCHAR2(300);
42 l_tmp2 VARCHAR2(25);
43
44 begin
45 IF (g_cc_current_ccid = p_ccid AND
46 g_curr_set_of_books_id = p_set_of_books_id AND
47 g_mc_current_structure_id = p_structure_id) THEN
48 l_tmp := g_cc_current_segments;
49 ELSE
50 l_tmp := get_value(p_ccid,
51 p_set_of_books_id,
52 p_structure_id);
53 END IF;
54
55 IF (l_tmp = 'NA_EDW') THEN
56 l_tmp2 := 'NA_EDW';
57 ELSE
58 l_tmp2 := rtrim(substrb(l_tmp,(p_seg_number-1)*25+1,25));
59 END IF;
60
61 IF (l_tmp2 <> 'NA_EDW') THEN
62 l_tmp2 := l_tmp2||'-'||g_curr_concat;
63 END IF;
64
65 return(l_tmp2);
66 end get_fk;
67
68
69 /***********************************************************/
70 /* function to retrieve segment value */
71 /***********************************************************/
72 function get_value (p_ccid number,
73 p_set_of_books_id varchar2,
74 p_structure_id number)
75 return varchar2
76 is
77
78 l_sob number;
79 l_instance varchar2(30) := NULL;
80
81 begin
82
83 if (p_ccid is null) or
84 (p_set_of_books_id is null) or
85 (p_structure_id is null) or
86 (not (p_ccid >= -2147483647 and p_ccid <= 2147483647)) or
87 (not (p_structure_id >= -2147483647 and
88 p_structure_id <= 2147483647)) then
89 return 'NA_EDW';
90 end if;
91
92 -- Check if init_cache has been called to initialize
93 -- which facts are being worked on
94
95 if g_mapping_fact is NULL then
96 -- init_cache has not bee called
97 return('CALL INIT_CACHE FIRST');
98 end if;
99
100 -- check whether set of books is valid, if so, retrieve the
101 -- parent_sob_id||parent_instance concatenation for future use
102
103 if g_curr_set_of_books_id = p_set_of_books_id then
104 null; -- this is a valid set of books; continue;
105 else
106 begin
107 g_curr_concat := g_cache_sets_of_books(p_set_of_books_id);
108 if (g_curr_concat = 'N') then
109 g_curr_concat := p_set_of_books_id||'-'||g_local_instance;
110 end if;
111 g_curr_set_of_books_id := p_set_of_books_id;
112 exception
113 when others then
114 return ('NA_EDW'); -- this set of books is invalid;
115 end;
116 end if;
117
118
119 -- check whether the given fact is mapped for this structure
120 -- if so, retrieve the mapping from cache
121
122 if (g_mc_current_structure_id = p_structure_id) then
123 null;
124 else
125 begin
126 g_mc_current_structure := g_mc_structure(p_structure_id);
127 g_mc_current_structure_id := p_structure_id;
128 exception
129 when others then
130 return 'NA_EDW'; -- this structure id is not mapped
131 end;
132 end if;
133
134 /***********************************************************/
135 /* code combinations cache section */
136 /***********************************************************/
137 if (g_cc_current_ccid = p_ccid) then
138
139
140 null;
141 else
142 declare
143 cursor c is
144 SELECT rpad(nvl(segment1,'NA_EDW'), 25, ' ') || rpad(nvl(segment2,'NA_EDW'), 25, ' ') ||
145 rpad(nvl(segment3,'NA_EDW'), 25, ' ') || rpad(nvl(segment4,'NA_EDW'), 25, ' ') ||
146 rpad(nvl(segment5,'NA_EDW'), 25, ' ') || rpad(nvl(segment6,'NA_EDW'), 25, ' ') ||
147 rpad(nvl(segment7,'NA_EDW'), 25, ' ') || rpad(nvl(segment8,'NA_EDW'), 25, ' ') ||
148 rpad(nvl(segment9,'NA_EDW'), 25, ' ') || rpad(nvl(segment10,'NA_EDW'), 25, ' ') ||
149 rpad(nvl(segment11,'NA_EDW'), 25, ' ') || rpad(nvl(segment12,'NA_EDW'), 25, ' ') ||
150 rpad(nvl(segment13,'NA_EDW'), 25, ' ') || rpad(nvl(segment14,'NA_EDW'), 25, ' ') ||
151 rpad(nvl(segment15,'NA_EDW'), 25, ' ') || rpad(nvl(segment16,'NA_EDW'), 25, ' ') ||
152 rpad(nvl(segment17,'NA_EDW'), 25, ' ') || rpad(nvl(segment18,'NA_EDW'), 25, ' ') ||
153 rpad(nvl(segment19,'NA_EDW'), 25, ' ') || rpad(nvl(segment20,'NA_EDW'), 25, ' ') ||
154 rpad(nvl(segment21,'NA_EDW'), 25, ' ') || rpad(nvl(segment22,'NA_EDW'), 25, ' ') ||
155 rpad(nvl(segment23,'NA_EDW'), 25, ' ') || rpad(nvl(segment24,'NA_EDW'), 25, ' ') ||
156 rpad(nvl(segment25,'NA_EDW'), 25, ' ') || rpad(nvl(segment26,'NA_EDW'), 25, ' ') ||
157 rpad(nvl(segment27,'NA_EDW'), 25, ' ') || rpad(nvl(segment28,'NA_EDW'), 25, ' ') ||
158 rpad(nvl(segment29,'NA_EDW'), 25, ' ') || rpad(nvl(segment30,'NA_EDW'), 25, ' ')
159 segments
160 FROM gl_code_combinations
161 WHERE gl_code_combinations.code_combination_id = p_ccid;
162 l_buffer varchar2(250) := NULL;
163 w number(3);
164 x number(3);
165 y number(3);
166 z number(3);
167 begin
168 for combination_record in c loop
169 w := 1;
170 z := 1;
171 l_buffer := 'NA_EDW '||
172 'NA_EDW '||
173 'NA_EDW '||
174 'NA_EDW '||
175 'NA_EDW '||
176 'NA_EDW '||
177 'NA_EDW '||
178 'NA_EDW '||
179 'NA_EDW '||
180 'NA_EDW ';
181 while (w < 21) loop
182 x := to_number(substrb(g_mc_current_structure, w, 2));
183 if (x <> -1) then
184 y := (x - 1) * 25 + 1;
185 l_buffer := substrb(l_buffer, 1, z-1) ||
186 substrb(combination_record.segments, y , 25) ||
187 substrb(l_buffer, z + 25);
188 end if;
189 w := w + 2;
190 z := z + 25;
191 end loop;
192 end loop;
193
194 if (l_buffer is not null) then
195 g_cc_current_ccid := p_ccid;
196 g_cc_current_segments := l_buffer;
197 else
198 return 'NA_EDW'; -- invalid code combination id
199 end if;
200
201 end;
202 end if;
203
204 -- result is: 10 segments||'-'||sob_id||'-'||instance
205 return (g_cc_current_segments||'-'||g_curr_concat);
206 end;
207
208
209 /***********************************************************/
210 /* procedure to initialize memory cache */
211 /***********************************************************/
212 procedure init_cache(p_fact_short_name VARCHAR2) is
213
214 cursor c_valid_sob is
215 select loc.set_of_books_id loc_sob,
216 equi.equi_set_of_books_id equi_sob,
217 parent.set_of_books_id parent_sob,
218 parent.instance parent_instance
219 from edw_local_set_of_books loc,
220 edw_local_equi_set_of_books equi,
221 edw_local_set_of_books parent
222 where loc.instance = g_local_instance
223 and equi.edw_set_of_books_id (+) = loc.edw_set_of_books_id
224 and parent.edw_set_of_books_id (+) = equi.equi_set_of_books_id;
225
226 TYPE StructureRecord IS RECORD (
227 STRUCTURE_ID NUMBER(15),
228 DIMENSION NUMBER,
229 SEGMENT NUMBER);
230
231 TYPE StructureCurType IS REF CURSOR;
232
233 l_buffer varchar2(20) := NULL;
234 l_curr_struct_id number := -1;
235 l_stmt varchar2(1000) := NULL;
236 l_structure_cache StructureCurType;
237 map_record StructureRecord;
238 y number(3);
239 begin
240
241 free_mem_all;
242 g_mapping_fact := p_fact_short_name;
243 g_local_instance := edw_instance.get_code();
244 l_stmt :=
245 'select map.structure_num structure_id, '||
246 ' to_number(rtrim(substrb(map.dimension_short_name, 12, 2), ''_'')) dimension, '||
247 ' to_number(substrb(map.application_column_name, 8, 2)) segment '||
248 'from edw_local_instance inst, '||
249 ' edw_flex_seg_mappings_v@edw_apps_to_wh map, '||
250 ' edw_fact_flex_fk_maps_v@edw_apps_to_wh fact '||
251 'where map.dimension_short_name = fact.dimension_short_name '||
252 'and map.instance_code = inst.instance_code '||
253 'and map.dimension_short_name like ''EDW_GL_ACCT%'' '||
254 'and upper(fact.enabled_flag) = ''Y'' '||
255 'and fact.fact_short_name = '''||p_fact_short_name||''' '||
256 'order by structure_num, dimension' ;
257
258 OPEN l_structure_cache FOR l_stmt;
259
260 LOOP
261 FETCH l_structure_cache INTO map_record;
262 EXIT WHEN l_structure_cache%NOTFOUND;
263
264 if (l_curr_struct_id <> map_record.structure_id) then
265 if (l_curr_struct_id <> -1) then
266 g_mc_structure(l_curr_struct_id) := l_buffer;
267 end if;
268 l_curr_struct_id := map_record.structure_id;
269 l_buffer := '-1-1-1-1-1-1-1-1-1-1';
270 end if;
271 y := (map_record.dimension - 1) * 2 + 1;
272 l_buffer := substrb(l_buffer, 1 , y - 1) ||
273 rpad(to_char(map_record.segment), 2, ' ') ||
274 substrb(l_buffer, y + 2);
275 END LOOP;
276
277 CLOSE l_structure_cache;
278
279 if (l_curr_struct_id <> -1) then
280 -- store the final structure
281 g_mc_structure(l_curr_struct_id) := l_buffer;
282 end if;
283
284
285 for c2 in c_valid_sob loop
286 if (c2.equi_sob is not NULL) then
287 -- The local sob has a parent sob
288 g_cache_sets_of_books(c2.loc_sob) :=
289 c2.parent_sob||'-'||c2.parent_instance;
290 else
291 -- Set cache to 'N' to indicate no parent sob
292 g_cache_sets_of_books(c2.loc_sob) := 'N';
293 end if;
294 end loop;
295
296 end;
297
298 /***********************************************************/
299 /* procedure to release memory cache */
300 /***********************************************************/
301 procedure free_mem_all is
302 begin
303 g_mc_structure.delete;
304 g_cache_sets_of_books.delete;
305 end;
306
307 END FII_FLEX_MAPPING;