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