1 package body ffdbitem as
2 /* $Header: ffdbitem.pkb 115.0 99/07/16 02:02:17 porting ship $ */
3 --
4 --
5 -- Copyright (c) Oracle Corporation (UK) Ltd 1994.
6 -- All Rights Reserved.
7 --
8 -- PRODUCT
9 -- Oracle*FastFormula
10 --
11 -- NAME
12 -- ffdbitem
13 --
14 -- NOTES
15 -- Contains utility functions and procedures for accessing database
16 -- item SQL and values
17 --
18 -- MODIFIED
19 -- pgowers 09-FEB-94 Created
20 -- rneale 19-MAY-94 Added exit(G699)
21 -- jthuring 11-OCT-95 Removed spurious start of comment marker
22 -- mfender 11-JUN-97 Create package statement to standard for
23 -- release 11.
24 --
25 -- PRIVATE CODE
26 --
27 -------------------------------- execute_error --------------------------------
28 --
29 -- NAME
30 -- execute_error
31 --
32 -- DESCRIPTION
33 -- Set error message and raise exception when called with error details
34 --
35 procedure execute_error (p_error_name in varchar2,
36 p_token1 in varchar2,
37 p_token2 in varchar2,
38 p_token3 in varchar2) is
39 begin
40 hr_utility.set_location('ffdbitem.execute_error',1);
41 hr_utility.set_message (802, p_error_name);
42 hr_utility.set_message_token ('1', p_token1);
43 hr_utility.set_message_token ('2', p_token2);
44 hr_utility.set_message_token ('3', p_token3);
45 hr_utility.raise_error;
46 end execute_error;
47
48 -------------------------- process_route_parameters --------------------------
49 --
50 -- NAME
51 -- process_route_parameters
52 --
53 -- DESCRIPTION
54 -- Replace route parameters where their equivalent values for the user
55 -- entity being processed
56 --
57 procedure process_route_parameters (p_item_name in varchar2,
58 p_route_text in out varchar2,
59 p_user_entity_id in number,
60 p_route_id in number) is
61 cursor route_parms is
62 SELECT RP.SEQUENCE_NO,
63 RP.PARAMETER_NAME,
64 UPPER(RP.DATA_TYPE),
65 RPV.VALUE
66 FROM FF_ROUTE_PARAMETERS RP,
67 FF_ROUTE_PARAMETER_VALUES RPV
68 WHERE RP.ROUTE_ID = p_route_id
69 AND RP.ROUTE_PARAMETER_ID = RPV.ROUTE_PARAMETER_ID
70 AND RPV.USER_ENTITY_ID = p_user_entity_id
71 ORDER BY RP.SEQUENCE_NO;
72 --
73 l_sequence_no FF_ROUTE_PARAMETERS.SEQUENCE_NO%TYPE;
74 l_parameter_name FF_ROUTE_PARAMETERS.PARAMETER_NAME%TYPE;
75 l_data_type FF_ROUTE_PARAMETERS.DATA_TYPE%TYPE;
76 l_value FF_ROUTE_PARAMETER_VALUES.VALUE%TYPE;
77 --
78 begin
79 hr_utility.set_location('ffdbitem.process_route_parameters',1);
80 open route_parms;
81 hr_utility.set_location('ffdbitem.process_route_parameters',2);
82 loop
83 fetch route_parms into l_sequence_no, l_parameter_name, l_data_type,
84 l_value;
85 hr_utility.set_location('ffdbitem.process_route_parameters',3);
86 if route_parms%notfound then
87 close route_parms;
88 exit;
89 end if;
90 -- Check that a placeholder exists, error if it doesn't
91 if (instr(p_route_text, '&U'||to_char(l_sequence_no)) = 0) then
92 hr_utility.set_message (802, 'FFT76_NO_MATCHING_U');
93 hr_utility.set_message_token ('1',p_item_name);
94 hr_utility.set_message_token ('2',l_parameter_name);
95 hr_utility.raise_error;
96 end if;
97 hr_utility.set_location('ffdbitem.process_route_parameters',4);
98 -- replace all instances of placeholder with route parameter value
99 p_route_text := replace (p_route_text,
100 '&U'||to_char(l_sequence_no),
101 l_value);
102 hr_utility.set_location('ffdbitem.process_route_parameters',5);
103
104 end loop;
105 hr_utility.set_location('ffdbitem.process_route_parameters',6);
106 -- Check if there are any remaining &U placeholders remaining
107 -- there should be none left
108 if (instr(p_route_text, '&U') > 0) then
109 hr_utility.set_message (802, 'FFT78_PLACEHOLDERS_REMAIN');
110 hr_utility.set_message_token ('1',p_item_name);
111 hr_utility.raise_error;
112 end if;
113 hr_utility.set_location('ffdbitem.process_route_parameters',7);
114 exception
115 when others then
116 if route_parms%isopen then
117 close route_parms;
118 end if;
119 raise;
120 end process_route_parameters;
121 --
122 ------------------------------ process_contexts ------------------------------
123 --
124 -- NAME
125 -- process_contexts
126 --
127 -- DESCRIPTION
128 -- Replace context placeholders in route with bind variable references
129 -- and add context details into output context information structure
130 --
131 procedure process_contexts (p_item_name in varchar2,
132 p_route_text in out varchar2,
133 p_route_id in number,
134 p_contexts out FFCONTEXTS_T) is
135 cursor route_contexts is
136 SELECT RCU.SEQUENCE_NO,
137 FC.CONTEXT_NAME,
138 UPPER(FC.DATA_TYPE)
139 FROM FF_CONTEXTS FC,
140 FF_ROUTE_CONTEXT_USAGES RCU
141 WHERE FC.CONTEXT_ID = RCU.CONTEXT_ID
142 AND RCU.ROUTE_ID = p_route_id
143 ORDER BY RCU.SEQUENCE_NO;
144 --
145 l_sequence_no FF_ROUTE_CONTEXT_USAGES.SEQUENCE_NO%TYPE;
146 l_context_name FF_CONTEXTS.CONTEXT_NAME%TYPE;
147 l_data_type FF_CONTEXTS.DATA_TYPE%TYPE;
148 --
149 begin
150 hr_utility.set_location('ffdbitem.process_contexts',1);
151 open route_contexts;
152 hr_utility.set_location('ffdbitem.process_contexts',2);
153 loop
154 fetch route_contexts into l_sequence_no, l_context_name, l_data_type;
155 hr_utility.set_location('ffdbitem.process_contexts',3);
156 if route_contexts%notfound then
157 close route_contexts;
158 exit;
159 end if;
160 -- Check that a placeholder exists, error if it doesn't
161 if (instr(p_route_text, '&B'||to_char(l_sequence_no)) = 0) then
162 hr_utility.set_message (802, 'FFT77_NO_MATCHING_B');
163 hr_utility.set_message_token ('1',p_item_name);
164 hr_utility.set_message_token ('2','B'||to_char(l_sequence_no));
165 hr_utility.raise_error;
166 end if;
167 hr_utility.set_location('ffdbitem.process_contexts',4);
168 -- replace all instances of placeholder with context bind name
169 p_route_text := replace (p_route_text,
170 '&B'||to_char(l_sequence_no),
171 ':B'||to_char(l_sequence_no));
172 hr_utility.set_location('ffdbitem.process_contexts',5);
173 p_contexts.context_count := l_sequence_no;
174 p_contexts.context_names(l_sequence_no) := l_context_name;
175 p_contexts.bind_names(l_sequence_no) := 'B'||to_char(l_sequence_no);
176 p_contexts.context_types(l_sequence_no) := l_data_type;
177 end loop;
178 -- Check if there are any remaining &B placeholders remaining
179 -- there should be none left
180 hr_utility.set_location('ffdbitem.process_contexts',6);
181 if (instr(p_route_text, '&B') > 0) then
182 hr_utility.set_message (802, 'FFT78_PLACEHOLDERS_REMAIN');
183 hr_utility.set_message_token ('1',p_item_name);
184 hr_utility.raise_error;
185 end if;
186 hr_utility.set_location('ffdbitem.process_contexts',7);
187 exception
188 when others then
189 if route_contexts%isopen then
190 close route_contexts;
191 end if;
192 raise;
193 end process_contexts;
194 --
195 ------------------------------- get_dbitem_info -------------------------------
196 --
197 -- NAME
198 -- get_dbitem_info
199 --
200 -- DESCRIPTION
201 -- Returns all information for a database item required to fetch it's value
202 -- including SQL, context requirements, data type in FFITEM_INFO_T
203 -- given the database item name, formula type id, business group id
204 -- and legislation code
205 --
206 procedure get_dbitem_info (p_item_name in varchar2,
207 p_formula_type_id in number,
208 p_bg_id in number,
209 p_leg_code in varchar2,
210 p_item_info out FFITEM_INFO_T) is
211 --
212 l_data_type FF_DATABASE_ITEMS.DATA_TYPE%type;
213 l_user_entity_id FF_USER_ENTITIES.USER_ENTITY_ID%type;
214 l_null_allowed FF_DATABASE_ITEMS.NULL_ALLOWED_FLAG%type;
215 l_notfound_allowed FF_USER_ENTITIES.NOTFOUND_ALLOWED_FLAG%type;
216 l_route_id FF_ROUTES.ROUTE_ID%type;
217 l_definition_text FF_DATABASE_ITEMS.DEFINITION_TEXT%type;
218 l_route_text varchar2(8000);
219 cursor c1 is
220 SELECT DI.DATA_TYPE,
221 UE.USER_ENTITY_ID,
222 UPPER(DI.NULL_ALLOWED_FLAG),
223 UPPER(UE.NOTFOUND_ALLOWED_FLAG),
224 FR.ROUTE_ID,
225 DI.DEFINITION_TEXT,
226 FR.TEXT
227 FROM FF_DATABASE_ITEMS DI,
228 FF_USER_ENTITIES UE,
229 FF_ROUTES FR
230 WHERE DI.USER_ENTITY_ID = UE.USER_ENTITY_ID
231 AND UE.ROUTE_ID = FR.ROUTE_ID
232 AND DI.USER_NAME = p_item_name
233 AND ( (UE.LEGISLATION_CODE IS NULL AND UE.BUSINESS_GROUP_ID IS NULL)
234 OR (UE.BUSINESS_GROUP_ID IS NULL AND p_leg_code = UE.LEGISLATION_CODE )
235 OR (p_bg_id = UE.BUSINESS_GROUP_ID)
236 )
237 AND NOT EXISTS (
238 SELECT CONTEXT_ID FROM FF_ROUTE_CONTEXT_USAGES IFRCU
239 WHERE IFRCU.ROUTE_ID = FR.ROUTE_ID
240 MINUS
241 SELECT CONTEXT_ID FROM FF_FTYPE_CONTEXT_USAGES
242 WHERE FORMULA_TYPE_ID = p_formula_type_id
243 );
244 begin
245 hr_utility.set_location('ffdbitem.get_dbitem_info',1);
246 open c1;
247 hr_utility.set_location('ffdbitem.get_dbitem_info',2);
248 fetch c1 into l_data_type, l_user_entity_id, l_null_allowed,
249 l_notfound_allowed, l_route_id, l_definition_text,
250 l_route_text;
251 hr_utility.set_location('ffdbitem.get_dbitem_info',3);
252 if c1%notfound then
253 raise_application_error(-20001,
254 'Item '||p_item_name||' could not be found');
255 end if;
256 hr_utility.set_location('ffdbitem.get_dbitem_info',4);
257 -- Check that route text is present
258 if (l_route_text is null) then
259 hr_utility.set_message (802, 'FFTBFC785_BAD_ROUTE_LENGTH');
260 hr_utility.set_message_token ('1',p_item_name);
261 hr_utility.set_message_token ('2','0');
262 hr_utility.raise_error;
263 end if;
264 hr_utility.set_location('ffdbitem.get_dbitem_info',5);
265
266 -- Process route parameters
267 process_route_parameters (p_item_name, l_route_text,
268 l_user_entity_id, l_route_id);
269
270 hr_utility.set_location('ffdbitem.get_dbitem_info',6);
271 -- Process contexts
272 process_contexts (p_item_name, l_route_text, l_route_id,
273 p_item_info.contexts);
274 hr_utility.set_location('ffdbitem.get_dbitem_info',7);
275 --
276 l_route_text := 'select '||l_definition_text||' from '||l_route_text;
277 hr_utility.set_location('ffdbitem.get_dbitem_info',8);
278 --
282 p_item_info.data_type := l_data_type;
279 -- copy the results into the output structure
280 p_item_info.item_name := p_item_name;
281 p_item_info.item_sql := l_route_text;
283 --
284 if l_null_allowed = 'Y' then
285 hr_utility.set_location('ffdbitem.get_dbitem_info',8);
286 p_item_info.null_ok := TRUE;
287 else
288 p_item_info.null_ok := FALSE;
289 hr_utility.set_location('ffdbitem.get_dbitem_info',9);
290 end if;
291 --
292 if l_notfound_allowed = 'Y' then
293 p_item_info.notfound_ok := TRUE;
294 hr_utility.set_location('ffdbitem.get_dbitem_info',10);
295 else
296 p_item_info.notfound_ok := FALSE;
297 hr_utility.set_location('ffdbitem.get_dbitem_info',11);
298 end if;
299 exception
300 when others then
301 if c1%isopen then
302 close c1;
303 end if;
304 -- re raise original exception
305 raise;
306 end get_dbitem_info;
307 --
308 ------------------------------ get_dbitem_value ------------------------------
309 --
310 -- NAME
311 -- get_dbitem_value
312 --
313 -- DESCRIPTION
314 -- Returns the value of a database item given the item details
315 -- currently returns the varchar2 version of the value, so for dates
316 -- this will be in the format DD-MON-YYYY
317 --
318 function get_dbitem_value (p_item_info in FFITEM_INFO_T) return varchar2 is
319 dbitem_cursor integer;
320 dbitem_text_value varchar2(255);
321 dbitem_date_value date;
322 dbitem_number_value number;
323 execute_status integer;
324 rows_fetched integer;
325 begin
326 hr_utility.set_location('ffdbitem.get_dbitem_value',1);
327 -- open a new cursor for the DB item select statement
328 dbitem_cursor := dbms_sql.open_cursor;
329 hr_utility.set_location('ffdbitem.get_dbitem_value',2);
330 hr_utility.trace(p_item_info.item_sql);
331 -- parse the SQL passed in
332 dbms_sql.parse(dbitem_cursor, p_item_info.item_sql, dbms_sql.v7);
333 --
334 hr_utility.set_location('ffdbitem.get_dbitem_value',3);
335 for i in 1..p_item_info.contexts.context_count loop
336 hr_utility.set_location('ffdbitem.get_dbitem_value',4);
337 hr_utility.trace(p_item_info.contexts.bind_names(i));
338 dbms_sql.bind_variable(dbitem_cursor,
339 p_item_info.contexts.bind_names(i),
340 p_item_info.contexts.bind_values(i));
341 end loop;
342 hr_utility.set_location('ffdbitem.get_dbitem_value',5);
343 -- Define an appropriately typed variable
344 if (p_item_info.data_type = 'T') then
345 hr_utility.set_location('ffdbitem.get_dbitem_value',6);
346 -- Define the (single) select list item as a varchar2
347 dbms_sql.define_column(dbitem_cursor, 1, dbitem_text_value, 255);
348 elsif (p_item_info.data_type = 'N') then
349 hr_utility.set_location('ffdbitem.get_dbitem_value',7);
350 -- Define the (single) select list item as a number
351 dbms_sql.define_column(dbitem_cursor, 1, dbitem_number_value);
352 elsif (p_item_info.data_type = 'D') then
353 hr_utility.set_location('ffdbitem.get_dbitem_value',8);
354 -- Define the (single) select list item as a date
355 dbms_sql.define_column(dbitem_cursor, 1, dbitem_date_value);
356 else
357 hr_utility.set_message (802, 'BAD_DATA_TYPE');
358 hr_utility.set_message_token ('1',p_item_info.item_name);
359 hr_utility.raise_error;
360 end if;
361 hr_utility.set_location('ffdbitem.get_dbitem_value',9);
362 --
363 -- Execute the cursor
364 execute_status := dbms_sql.execute(dbitem_cursor);
365 --
366 hr_utility.set_location('ffdbitem.get_dbitem_value',10);
367 -- Fetch the rows (only 1 row should be fetched for database items)
368 rows_fetched := dbms_sql.fetch_rows(dbitem_cursor);
369 hr_utility.set_location('ffdbitem.get_dbitem_value',11);
370 if (rows_fetched = 1) then
371 hr_utility.set_location('ffdbitem.get_dbitem_value',12);
372 -- get column value according to data type of item
373 if (p_item_info.data_type = 'T') then
374 hr_utility.set_location('ffdbitem.get_dbitem_value',13);
375 -- Define the (single) select list item as a varchar2
376 dbms_sql.column_value(dbitem_cursor, 1, dbitem_text_value);
377 hr_utility.set_location('ffdbitem.get_dbitem_value',14);
378 if (dbitem_text_value is null and not p_item_info.null_ok) then
379 execute_error('FFX00_NULL_VALUE', p_item_info.item_name, '1', '');
380 end if;
381 elsif (p_item_info.data_type = 'N') then
382 hr_utility.set_location('ffdbitem.get_dbitem_value',15);
383 -- Define the (single) select list item as a number
384 dbms_sql.column_value(dbitem_cursor, 1, dbitem_number_value);
385 hr_utility.set_location('ffdbitem.get_dbitem_value',16);
386 if (dbitem_number_value is null and not p_item_info.null_ok) then
387 execute_error('FFX00_NULL_VALUE', p_item_info.item_name, '1', '');
388 end if;
389 dbitem_text_value := to_char(dbitem_number_value);
390 else
391 hr_utility.set_location('ffdbitem.get_dbitem_value',17);
392 -- Define the (single) select list item as a date
393 dbms_sql.column_value(dbitem_cursor, 1, dbitem_date_value);
394 hr_utility.set_location('ffdbitem.get_dbitem_value',18);
395 if (dbitem_date_value is null and not p_item_info.null_ok ) then
396 execute_error('FFX00_NULL_VALUE', p_item_info.item_name, '1', '');
397 end if;
398 dbitem_text_value := to_char(dbitem_date_value,'DD-MON-YYYY');
399 end if;
400 elsif (rows_fetched = 0) then
401 hr_utility.set_location('ffdbitem.get_dbitem_value',19);
402 -- No rows found, so if 'notfound' is not OK, then raise no_data_found
403 if not p_item_info.notfound_ok then
404 execute_error('FFX00_DATA_NOT_FOUND', p_item_info.item_name, '1', '');
405 end if;
406 else
410 -- Close cursor
407 -- >1 row, so this is an error
408 execute_error('FFX00_TOO_MANY_ROWS', p_item_info.item_name, '1', '');
409 end if;
411 hr_utility.set_location('ffdbitem.get_dbitem_value',20);
412 dbms_sql.close_cursor(dbitem_cursor);
413 hr_utility.set_location('ffdbitem.get_dbitem_value',21);
414 -- return value
415 return dbitem_text_value;
416 exception
417 when others then
418 -- Close cursor if it is open
419 if dbms_sql.is_open(dbitem_cursor) then
420 dbms_sql.close_cursor(dbitem_cursor);
421 end if;
422 -- re-raise the exception
423 raise;
424 end get_dbitem_value;
425
426 end ffdbitem;
[Home] [Help]