[Home] [Help]
PACKAGE BODY: APPS.BEN_US_DATABASE_ITEMS_PKG
Source
1 package body ben_us_database_items_pkg as
2 /* $Header: beusdbip.pkb 120.0 2005/05/28 12:01:33 appldev noship $ */
3 --
4 -- Copyright (c) Oracle Corporation 1991, 1992, 1993. All rights reserved.
5 --
6 /*
7 NAME
8 beusdbip.pkb
9 --
10 DESCRIPTION
11 Provides a single function interface for the creation of database
12 items. New routes may be created, or old ones may be re-used.
13 --
14 rem Generated By : pbodla
15 rem
16 rem Version Date Author Reason
17 rem -------------------------------------------------------------------------
18 rem 115.0 28/04/99 pbodla For seeding all Benefits routes and database items
19 rem
20 --
21 */
22 --
23 -- Procedures
24 --
25 PROCEDURE create_db_item(p_name VARCHAR2,
26 p_description VARCHAR2 DEFAULT NULL,
27 p_data_type VARCHAR2,
28 p_null_allowed VARCHAR2,
29 p_definition_text VARCHAR2,
30 p_user_entity_name VARCHAR2,
31 p_user_entity_description VARCHAR2 DEFAULT NULL,
32 p_route_name VARCHAR2,
33 p_param_value1 VARCHAR2 DEFAULT NULL,
34 p_param_value2 VARCHAR2 DEFAULT NULL,
35 p_param_value3 VARCHAR2 DEFAULT NULL,
36 p_route_description VARCHAR2 DEFAULT NULL,
37 p_route_text VARCHAR2 DEFAULT NULL,
38 p_context_name1 VARCHAR2 DEFAULT NULL,
39 p_context_name2 VARCHAR2 DEFAULT NULL,
40 p_context_name3 VARCHAR2 DEFAULT NULL,
41 p_context_name4 VARCHAR2 DEFAULT NULL,
42 p_context_name5 VARCHAR2 DEFAULT NULL,
43 p_context_name6 VARCHAR2 DEFAULT NULL,
44 p_context_name7 VARCHAR2 DEFAULT NULL,
45 p_context_name8 VARCHAR2 DEFAULT NULL,
46 p_context_name9 VARCHAR2 DEFAULT NULL,
47 p_context_name10 VARCHAR2 DEFAULT NULL,
48 p_param_name1 VARCHAR2 DEFAULT NULL,
49 p_param_type1 VARCHAR2 DEFAULT NULL,
50 p_param_name2 VARCHAR2 DEFAULT NULL,
51 p_param_type2 VARCHAR2 DEFAULT NULL,
52 p_param_name3 VARCHAR2 DEFAULT NULL,
53 p_param_type3 VARCHAR2 DEFAULT NULL
54 ) IS
55 --
56 l_route_id NUMBER;
57 l_user_entity_id NUMBER;
58 l_record_inserted BOOLEAN;
59 l_dummy char(1);
60 --
61 BEGIN
62 --
63 -- Get the route id. Create a route if necessary.
64 --
65 hr_utility.set_location ('pay_us_create_col_dbi.create_table_column_dbi', 1);
66 hr_utility.trace ('p_name: ' || p_name);
67 hr_utility.trace ('p_description: ' || p_description);
68 hr_utility.trace ('p_data_type: ' || p_data_type);
69 hr_utility.trace ('p_null_allowed: ' || p_null_allowed);
70 hr_utility.trace ('p_definition_text: ' || p_definition_text);
71 hr_utility.trace ('p_user_entity_name: ' || p_user_entity_name);
72 hr_utility.trace ('p_user_entity_description: ' || p_user_entity_description);
73 hr_utility.trace ('p_route_name: ' || p_route_name);
74 hr_utility.trace ('p_route_description: ' || p_route_description);
75 hr_utility.trace ('p_route_text: ' || p_route_text);
76 hr_utility.trace ('p_context_name1: ' || p_context_name1);
77 hr_utility.trace ('p_context_name2: ' || p_context_name2);
78 hr_utility.trace ('p_context_name3: ' || p_context_name3);
79 --
80 BEGIN
81 --
82 SELECT route_id
83 INTO l_route_id
84 FROM ff_routes
85 WHERE route_name = upper(p_route_name);
86 --
87 EXCEPTION WHEN NO_DATA_FOUND THEN
88 --
89 -- Create the route, context usages, and parameters
90 --
91 INSERT INTO ff_routes
92 (route_id,
93 route_name,
94 user_defined_flag,
95 description,
96 text,
97 last_update_date,
98 last_updated_by,
99 last_update_login,
100 created_by,
101 creation_date)
102 VALUES
103 (ff_routes_s.nextval,
104 upper(p_route_name),
105 'N',
106 p_route_description,
107 p_route_text,
108 sysdate,
109 0,
110 0,
111 0,
112 sysdate);
113 --
114 SELECT ff_routes_s.currval
115 INTO l_route_id
116 FROM dual;
117 --
118 -- Insert any context usages
119 --
120 IF p_context_name1 is not null THEN
121 --
122 INSERT INTO ff_route_context_usages
123 (route_id,
124 context_id,
125 sequence_no)
126 select l_route_id,
127 FFC.context_id,
128 1
129 from ff_contexts FFC
130 where context_name = p_context_name1;
131 --
132 END IF;
133 --
134 IF p_context_name2 is not null THEN
135 --
136 INSERT INTO ff_route_context_usages
137 (route_id,
138 context_id,
139 sequence_no)
140 select l_route_id,
141 FFC.context_id,
142 2
143 from ff_contexts FFC
144 where context_name = p_context_name2;
145 --
146 END IF;
147 --
148 IF p_context_name3 is not null THEN
149 --
150 INSERT INTO ff_route_context_usages
151 (route_id,
152 context_id,
153 sequence_no)
154 select l_route_id,
155 FFC.context_id,
156 3
157 from ff_contexts FFC
158 where context_name = p_context_name3;
159 --
160 END IF;
161 --
162 IF p_context_name4 is not null THEN
163 --
164 INSERT INTO ff_route_context_usages
165 (route_id,
166 context_id,
167 sequence_no)
168 select l_route_id,
169 FFC.context_id,
170 4
171 from ff_contexts FFC
172 where context_name = p_context_name4;
173 --
174 END IF;
175 --
176 IF p_context_name5 is not null THEN
177 --
178 INSERT INTO ff_route_context_usages
179 (route_id,
180 context_id,
181 sequence_no)
182 select l_route_id,
183 FFC.context_id,
184 5
185 from ff_contexts FFC
186 where context_name = p_context_name5;
187 --
188 END IF;
189 --
190 IF p_context_name6 is not null THEN
191 --
192 INSERT INTO ff_route_context_usages
193 (route_id,
194 context_id,
195 sequence_no)
196 select l_route_id,
197 FFC.context_id,
198 6
199 from ff_contexts FFC
200 where context_name = p_context_name6;
201 --
202 END IF;
203 --
204 IF p_context_name7 is not null THEN
205 --
206 INSERT INTO ff_route_context_usages
207 (route_id,
208 context_id,
209 sequence_no)
210 select l_route_id,
211 FFC.context_id,
212 7
213 from ff_contexts FFC
214 where context_name = p_context_name7;
215 --
216 END IF;
217 --
218 IF p_context_name8 is not null THEN
219 --
220 INSERT INTO ff_route_context_usages
221 (route_id,
222 context_id,
223 sequence_no)
224 select l_route_id,
225 FFC.context_id,
226 8
227 from ff_contexts FFC
228 where context_name = p_context_name8;
229 --
230 END IF;
231 --
232 IF p_context_name9 is not null THEN
233 --
234 INSERT INTO ff_route_context_usages
235 (route_id,
236 context_id,
237 sequence_no)
238 select l_route_id,
239 FFC.context_id,
240 9
241 from ff_contexts FFC
242 where context_name = p_context_name9;
243 --
244 END IF;
245 --
246 IF p_context_name10 is not null THEN
247 --
248 INSERT INTO ff_route_context_usages
249 (route_id,
250 context_id,
251 sequence_no)
252 select l_route_id,
253 FFC.context_id,
254 10
255 from ff_contexts FFC
256 where context_name = p_context_name10;
257 --
258 END IF;
259 --
260 -- Insert any route parameters
261 --
262 IF p_param_name1 is not null THEN
263 --
264 INSERT INTO ff_route_parameters
265 (route_parameter_id,
266 route_id,
267 sequence_no,
268 parameter_name,
269 data_type)
270 VALUES
271 (ff_route_parameters_s.nextval,
272 l_route_id,
273 1,
274 p_param_name1,
275 p_param_type1);
276 --
277 END IF;
278 --
279 IF p_param_name2 is not null THEN
280 --
281 INSERT INTO ff_route_parameters
282 (route_parameter_id,
283 route_id,
284 sequence_no,
285 parameter_name,
286 data_type)
287 VALUES
288 (ff_route_parameters_s.nextval,
289 l_route_id,
290 2,
291 p_param_name2,
292 p_param_type2);
293 --
294 END IF;
295 --
296 IF p_param_name3 is not null THEN
297 --
298 INSERT INTO ff_route_parameters
299 (route_parameter_id,
300 route_id,
301 sequence_no,
302 parameter_name,
303 data_type)
304 VALUES
305 (ff_route_parameters_s.nextval,
306 l_route_id,
307 3,
308 p_param_name3,
309 p_param_type3);
310 --
311 END IF;
312 --
313 END;
314 --
315 -- Get the user entity id. Create a user entity if necessary.
316 --
317 BEGIN
318 --
319 SELECT user_entity_id
320 INTO l_user_entity_id
321 FROM ff_user_entities
322 WHERE user_entity_name = upper(p_user_entity_name);
323 --
324 EXCEPTION WHEN NO_DATA_FOUND THEN
325 --
326 -- Create the user entity
327 --
328 hrdyndbi.insert_user_entity (
329 p_route_name => upper(p_route_name),
330 p_user_entity_name => p_user_entity_name,
331 p_entity_description => p_user_entity_description,
332 p_not_found_flag => 'Y',
333 p_creator_type => 'SEH',
334 p_creator_id => 0,
335 p_business_group_id => NULL,
336 p_legislation_code => NULL, -- 'US',
337 p_created_by => 0,
338 p_last_login => 0,
339 p_record_inserted => l_record_inserted);
340 --
341 SELECT user_entity_id
342 INTO l_user_entity_id
343 FROM ff_user_entities
344 WHERE user_entity_name = p_user_entity_name;
345 --
346 -- Add any route parameter values
347 --
348 IF p_param_value1 is not null AND l_record_inserted THEN
349 --
350 INSERT into ff_route_parameter_values
351 (route_parameter_id,
352 user_entity_id,
353 value)
354 SELECT route_parameter_id,
355 l_user_entity_id,
356 p_param_value1
357 FROM ff_route_parameters
358 where route_id = l_route_id
359 and sequence_no = 1;
360 --
361 END IF;
362 --
363 IF p_param_value2 is not null AND l_record_inserted THEN
364 --
365 INSERT into ff_route_parameter_values
366 (route_parameter_id,
367 user_entity_id,
368 value)
369 SELECT route_parameter_id,
370 l_user_entity_id,
371 p_param_value2
372 FROM ff_route_parameters
373 where route_id = l_route_id
374 and sequence_no = 2;
375 --
376 END IF;
377 --
378 --
379 IF p_param_value3 is not null AND l_record_inserted THEN
380 --
381 INSERT into ff_route_parameter_values
382 (route_parameter_id,
383 user_entity_id,
384 value)
385 SELECT route_parameter_id,
386 l_user_entity_id,
387 p_param_value3
388 FROM ff_route_parameters
389 where route_id = l_route_id
390 and sequence_no = 3;
391 --
392 END IF;
393 --
394 END;
395 --
396 -- Now build db item
397 --
398 -- IF l_record_inserted THEN
399 --
400 BEGIN
401 --
402 select null into l_dummy
403 from ff_database_items
404 where user_name = p_name and
405 user_entity_id = l_user_entity_id;
406 --
407 EXCEPTION WHEN NO_DATA_FOUND THEN
408
409 insert into ff_database_items (
410 user_name,
411 user_entity_id,
412 data_type,
413 definition_text,
414 null_allowed_flag,
415 description,
416 last_update_date,
417 last_updated_by,
418 last_update_login,
419 created_by,
420 creation_date)
421 values( p_name,
422 l_user_entity_id,
423 p_data_type,
424 p_definition_text,
425 p_null_allowed,
426 p_description,
427 sysdate,
428 0,
429 0,
430 0,
431 sysdate);
432 end;
433 --END IF;
434 --
435 END create_db_item;
436 --
437 end ben_us_database_items_pkg;