[Home] [Help]
PACKAGE BODY: APPS.PAY_DATABASE_ITEMS_PKG
Source
1 package body pay_database_items_pkg as
2 /* $Header: pycadbis.pkb 120.0 2005/05/29 03:28:45 appldev noship $ */
3 --
4 -- Copyright (c) Oracle Corporation 1991, 1992, 1993. All rights reserved.
5 --
6 /*
7 NAME
8 pycadbip.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 MODIFIED (DD-MON-YYYY)
15 RThirlby 20_JUL-1999 Created (copy of pyusdbip.pkb, but with new
16 legislation_code parameter.
17 RThirlby 09-NOV-1999 Commented out if l_record_inserted clause
18 around create_db_item, so that dbi is
19 created even if user entity was previously
20 created.
21 RThirlby 29-FEB-2000 No changes required for 11i upport
22 */
23 --
24 -- Procedures
25 --
26 PROCEDURE create_db_item(p_name VARCHAR2,
27 p_description VARCHAR2 DEFAULT NULL,
28 p_data_type VARCHAR2,
29 p_null_allowed VARCHAR2,
30 p_definition_text VARCHAR2,
31 p_user_entity_name VARCHAR2,
32 p_user_entity_description VARCHAR2 DEFAULT NULL,
33 p_route_name VARCHAR2,
34 p_param_value1 VARCHAR2 DEFAULT NULL,
35 p_param_value2 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_param_name1 VARCHAR2 DEFAULT NULL,
43 p_param_type1 VARCHAR2 DEFAULT NULL,
44 p_param_name2 VARCHAR2 DEFAULT NULL,
45 p_param_type2 VARCHAR2 DEFAULT NULL,
46 p_legislation_code VARCHAR2
47 ) IS
48 --
49 l_route_id NUMBER;
50 l_user_entity_id NUMBER;
51 l_record_inserted BOOLEAN;
52 --
53 BEGIN
54 --
55 -- Get the route id. Create a route if necessary.
56 --
57 hr_utility.set_location ('pay_database_items_pkg.create_db_item', 1);
58 hr_utility.trace ('p_name: ' || p_name);
59 hr_utility.trace ('p_description: ' || p_description);
60 hr_utility.trace ('p_data_type: ' || p_data_type);
61 hr_utility.trace ('p_null_allowed: ' || p_null_allowed);
62 hr_utility.trace ('p_definition_text: ' || p_definition_text);
63 hr_utility.trace ('p_user_entity_name: ' || p_user_entity_name);
64 hr_utility.trace ('p_user_entity_description: ' || p_user_entity_description);
65 hr_utility.trace ('p_route_name: ' || p_route_name);
66 hr_utility.trace ('p_route_description: ' || p_route_description);
67 hr_utility.trace ('p_route_text: ' || p_route_text);
68 hr_utility.trace ('p_context_name1: ' || p_context_name1);
69 hr_utility.trace ('p_context_name2: ' || p_context_name2);
70 hr_utility.trace ('p_legislation_code: ' || p_legislation_code);
71 --
72 BEGIN
73 --
74 SELECT route_id
75 INTO l_route_id
76 FROM ff_routes
77 WHERE route_name = upper(p_route_name);
78 --
79 EXCEPTION WHEN NO_DATA_FOUND THEN
80 --
81 -- Create the route, context usages, and parameters
82 --
83 INSERT INTO ff_routes
84 (route_id,
85 route_name,
86 user_defined_flag,
87 description,
88 text,
89 last_update_date,
90 last_updated_by,
91 last_update_login,
92 created_by,
93 creation_date)
94 VALUES
95 (ff_routes_s.nextval,
96 upper(p_route_name),
97 'N',
98 p_route_description,
99 p_route_text,
100 sysdate,
101 0,
102 0,
103 0,
104 sysdate);
105 --
106 SELECT ff_routes_s.currval
107 INTO l_route_id
108 FROM dual;
109 --
110 -- Insert any context usages
111 --
112 IF p_context_name1 is not null THEN
113 --
114 INSERT INTO ff_route_context_usages
115 (route_id,
116 context_id,
117 sequence_no)
118 select l_route_id,
119 FFC.context_id,
120 1
121 from ff_contexts FFC
122 where context_name = p_context_name1;
123 --
124 END IF;
125 --
126 IF p_context_name2 is not null THEN
127 --
128 INSERT INTO ff_route_context_usages
129 (route_id,
130 context_id,
131 sequence_no)
132 select l_route_id,
133 FFC.context_id,
134 2
135 from ff_contexts FFC
136 where context_name = p_context_name2;
137 --
138 END IF;
139 --
140 IF p_context_name3 is not null THEN
141 --
142 INSERT INTO ff_route_context_usages
143 (route_id,
144 context_id,
145 sequence_no)
146 select l_route_id,
147 FFC.context_id,
148 3
149 from ff_contexts FFC
150 where context_name = p_context_name3;
151 --
152 END IF;
153 --
154 IF p_context_name4 is not null THEN
155 --
156 INSERT INTO ff_route_context_usages
157 (route_id,
158 context_id,
159 sequence_no)
160 select l_route_id,
161 FFC.context_id,
162 4
163 from ff_contexts FFC
164 where context_name = p_context_name4;
165 --
166 END IF;
167 --
168 -- Insert any route parameters
169 --
170 IF p_param_name1 is not null THEN
171 --
172 INSERT INTO ff_route_parameters
173 (route_parameter_id,
174 route_id,
175 sequence_no,
176 parameter_name,
177 data_type)
178 VALUES
179 (ff_route_parameters_s.nextval,
180 l_route_id,
181 1,
182 p_param_name1,
183 p_param_type1);
184 --
185 END IF;
186 --
187 IF p_param_name2 is not null THEN
188 --
189 INSERT INTO ff_route_parameters
190 (route_parameter_id,
191 route_id,
192 sequence_no,
193 parameter_name,
194 data_type)
195 VALUES
196 (ff_route_parameters_s.nextval,
197 l_route_id,
198 1,
199 p_param_name2,
200 p_param_type2);
201 --
202 END IF;
203 --
204 END;
205 --
206 -- Get the user entity id. Create a user entity if necessary.
207 --
208 BEGIN
209 --
210 SELECT user_entity_id
211 INTO l_user_entity_id
212 FROM ff_user_entities
213 WHERE user_entity_name = upper(p_user_entity_name);
214 --
215 EXCEPTION WHEN NO_DATA_FOUND THEN
216 --
217 -- Create the user entity
218 --
219 hrdyndbi.insert_user_entity (
220 p_route_name => upper(p_route_name),
221 p_user_entity_name => p_user_entity_name,
222 p_entity_description => p_user_entity_description,
223 p_not_found_flag => 'Y',
224 p_creator_type => 'SEH',
225 p_creator_id => 0,
226 p_business_group_id => NULL,
227 p_legislation_code => p_legislation_code,
228 p_created_by => 0,
229 p_last_login => 0,
230 p_record_inserted => l_record_inserted);
231 --
232 SELECT user_entity_id
233 INTO l_user_entity_id
234 FROM ff_user_entities
235 WHERE user_entity_name = p_user_entity_name;
236 --
237 -- Add any route parameter values
238 --
239 IF p_param_value1 is not null AND l_record_inserted THEN
240 --
241 INSERT into ff_route_parameter_values
242 (route_parameter_id,
243 user_entity_id,
244 value)
245 SELECT route_parameter_id,
246 l_user_entity_id,
247 p_param_value1
248 FROM ff_route_parameters
249 where route_id = l_route_id
250 and sequence_no = 1;
251 --
252 END IF;
253 --
254 IF p_param_value2 is not null AND l_record_inserted THEN
255 --
256 INSERT into ff_route_parameter_values
257 (route_parameter_id,
258 user_entity_id,
259 value)
260 SELECT route_parameter_id,
261 l_user_entity_id,
262 p_param_value2
263 FROM ff_route_parameters
264 where route_id = l_route_id
265 and sequence_no = 2;
266 --
267 END IF;
268 --
269 END;
270 --
271 -- Now build db item
272 --
273 -- IF l_record_inserted THEN
274 insert into ff_database_items (
275 user_name,
276 user_entity_id,
277 data_type,
278 definition_text,
279 null_allowed_flag,
280 description,
281 last_update_date,
282 last_updated_by,
283 last_update_login,
284 created_by,
285 creation_date)
286 --
287 values (p_name,
288 l_user_entity_id,
289 p_data_type,
290 p_definition_text,
291 p_null_allowed,
292 p_description,
293 sysdate,
294 0,
295 0,
296 0,
297 sysdate);
298 --
299 -- ELSE
300 --
301 -- hr_utility.trace('Database_item already exists, so not reinserted');
302 --
303 -- END IF;
304 --
305 END create_db_item;
306 --
307 end pay_database_items_pkg;