[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_DATABASE_ITEMS_PKG
Source
1 package body pay_ca_database_items_pkg as
2 /* $Header: pycadbip.pkb 120.0 2005/05/29 03:28:26 appldev noship $ */
3 --
4 -- Copyright (c) Oracle Corporation 1999. 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.
13 --
14 */
15 --
16 -- Procedures
17 --
18 PROCEDURE create_db_item(p_name VARCHAR2,
19 p_description VARCHAR2 DEFAULT NULL,
20 p_data_type VARCHAR2,
21 p_null_allowed VARCHAR2,
22 p_definition_text VARCHAR2,
23 p_user_entity_name VARCHAR2,
24 p_user_entity_description VARCHAR2 DEFAULT NULL,
25 p_route_name VARCHAR2,
26 p_param_value1 VARCHAR2 DEFAULT NULL,
27 p_param_value2 VARCHAR2 DEFAULT NULL,
28 p_route_description VARCHAR2 DEFAULT NULL,
29 p_route_text VARCHAR2 DEFAULT NULL,
30 p_context_name1 VARCHAR2 DEFAULT NULL,
31 p_context_name2 VARCHAR2 DEFAULT NULL,
32 p_context_name3 VARCHAR2 DEFAULT NULL,
33 p_context_name4 VARCHAR2 DEFAULT NULL,
34 p_param_name1 VARCHAR2 DEFAULT NULL,
35 p_param_type1 VARCHAR2 DEFAULT NULL,
36 p_param_name2 VARCHAR2 DEFAULT NULL,
37 p_param_type2 VARCHAR2 DEFAULT NULL
38 ) IS
39
40 BEGIN
41
42 -- Call main package with legislation code defaulted to 'CA'
43 create_db_item( p_name,
44 p_description,
45 p_data_type,
46 p_null_allowed,
47 p_definition_text,
48 p_user_entity_name,
49 p_user_entity_description,
50 p_route_name,
51 p_param_value1,
52 p_param_value2,
53 p_route_description,
54 p_route_text ,
55 p_context_name1,
56 p_context_name2,
57 p_context_name3,
58 p_context_name4,
59 p_param_name1,
60 p_param_type1,
61 p_param_name2,
62 p_param_type2,
63 'CA'
64 );
65
66 END create_db_item;
67
68
69 PROCEDURE create_db_item(p_name VARCHAR2,
70 p_description VARCHAR2 DEFAULT NULL,
71 p_data_type VARCHAR2,
72 p_null_allowed VARCHAR2,
73 p_definition_text VARCHAR2,
74 p_user_entity_name VARCHAR2,
75 p_user_entity_description VARCHAR2 DEFAULT NULL,
76 p_route_name VARCHAR2,
77 p_param_value1 VARCHAR2 DEFAULT NULL,
78 p_param_value2 VARCHAR2 DEFAULT NULL,
79 p_route_description VARCHAR2 DEFAULT NULL,
80 p_route_text VARCHAR2 DEFAULT NULL,
81 p_context_name1 VARCHAR2 DEFAULT NULL,
82 p_context_name2 VARCHAR2 DEFAULT NULL,
83 p_context_name3 VARCHAR2 DEFAULT NULL,
84 p_context_name4 VARCHAR2 DEFAULT NULL,
85 p_param_name1 VARCHAR2 DEFAULT NULL,
86 p_param_type1 VARCHAR2 DEFAULT NULL,
87 p_param_name2 VARCHAR2 DEFAULT NULL,
88 p_param_type2 VARCHAR2 DEFAULT NULL,
89 p_legislation_code VARCHAR2
90 ) IS
91
92 --
93 l_route_id NUMBER;
94 l_user_entity_id NUMBER;
95 --
96 BEGIN
97 --
98 -- Get the route id. Create a route if necessary.
99 --
100 hr_utility.set_location ('pay_us_create_col_dbi.create_table_column_dbi', 1);
101 hr_utility.trace ('p_name: ' || p_name);
102 hr_utility.trace ('Legislation_code: ' || p_legislation_code);
103 hr_utility.trace ('p_description: ' || p_description);
104 hr_utility.trace ('p_data_type: ' || p_data_type);
105 hr_utility.trace ('p_null_allowed: ' || p_null_allowed);
106 hr_utility.trace ('p_definition_text: ' || p_definition_text);
107 hr_utility.trace ('p_user_entity_name: ' || p_user_entity_name);
108 hr_utility.trace ('p_user_entity_description: ' || p_user_entity_description);
109 hr_utility.trace ('p_route_name: ' || p_route_name);
110 hr_utility.trace ('p_route_description: ' || p_route_description);
111 hr_utility.trace ('p_route_text: ' || p_route_text);
112 hr_utility.trace ('p_context_name1: ' || p_context_name1);
113 hr_utility.trace ('p_context_name2: ' || p_context_name2);
114 --
115 BEGIN
116 --
117 SELECT route_id
118 INTO l_route_id
119 FROM ff_routes
120 WHERE route_name = upper(p_route_name);
121 --
122 hr_utility.trace(' selected route..');
123 EXCEPTION WHEN NO_DATA_FOUND THEN
124 --
125 -- Create the route, context usages, and parameters
126 --
127 INSERT INTO ff_routes
128 (route_id,
129 route_name,
130 user_defined_flag,
131 description,
132 text,
133 last_update_date,
134 last_updated_by,
135 last_update_login,
136 created_by,
137 creation_date)
138 VALUES
139 (ff_routes_s.nextval,
140 upper(p_route_name),
141 'N',
142 p_route_description,
143 p_route_text,
144 sysdate,
145 0,
146 0,
147 0,
148 sysdate);
149
150 hr_utility.trace('after insert of route');
151
152 --
153 SELECT ff_routes_s.currval
154 INTO l_route_id
155 FROM dual;
156 --
157 -- Insert any context usages
158 --
159 IF p_context_name1 is not null THEN
160 --
161 INSERT INTO ff_route_context_usages
162 (route_id,
163 context_id,
164 sequence_no)
165 select l_route_id,
166 FFC.context_id,
167 1
168 from ff_contexts FFC
169 where context_name = p_context_name1;
170
171 hr_utility.trace('after insert of context');
172
173 --
174 END IF;
175 --
176 IF p_context_name2 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 2
185 from ff_contexts FFC
186 where context_name = p_context_name2;
187 --
188 END IF;
189 --
190 IF p_context_name3 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 3
199 from ff_contexts FFC
200 where context_name = p_context_name3;
201 --
202 END IF;
203 --
204 IF p_context_name4 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 4
213 from ff_contexts FFC
214 where context_name = p_context_name4;
215 --
216 END IF;
217 --
218 -- Insert any route parameters
219 --
220 IF p_param_name1 is not null THEN
221 --
222 INSERT INTO ff_route_parameters
223 (route_parameter_id,
224 route_id,
225 sequence_no,
226 parameter_name,
227 data_type)
228 VALUES
229 (ff_route_parameters_s.nextval,
230 l_route_id,
231 1,
232 p_param_name1,
233 p_param_type1);
234
235 hr_utility.trace('after insert of param');
236
237 --
238 END IF;
239 --
240 IF p_param_name2 is not null THEN
241 --
242 INSERT INTO ff_route_parameters
243 (route_parameter_id,
244 route_id,
245 sequence_no,
246 parameter_name,
247 data_type)
248 VALUES
249 (ff_route_parameters_s.nextval,
250 l_route_id,
251 1,
252 p_param_name2,
253 p_param_type2);
254 --
255 END IF;
256 --
257 END;
258 --
259 -- Get the user entity id. Create a user entity if necessary.
260 --
261 BEGIN
262 --
263 SELECT user_entity_id
264 INTO l_user_entity_id
265 FROM ff_user_entities
266 WHERE user_entity_name = upper(p_user_entity_name)
267 and legislation_code = p_legislation_code ;
268
269 hr_utility.trace('selected entity id'||to_char(l_user_entity_id));
270
271 --
272 EXCEPTION WHEN NO_DATA_FOUND THEN
273 --
274 -- Create the user entity
275 --
276 hrdyndbi.insert_user_entity (
277 p_route_name => upper(p_route_name),
278 p_user_entity_name => p_user_entity_name,
279 p_entity_description => p_user_entity_description,
280 p_not_found_flag => 'Y',
281 p_creator_type => 'SEH',
282 -- p_creator_type => NULL,
283 -- p_creator_type => 'B',
284 p_creator_id => 0,
285 p_business_group_id => NULL,
286 p_legislation_code => p_legislation_code,
287 p_created_by => 0,
288 p_last_login => 0
289 );
290 hr_utility.trace('p_user_entity_name: ' || p_user_entity_name);
291 hr_utility.trace('Legislation_code: ' || p_legislation_code);
292 END;
293
294 hr_utility.trace('insereted user entity'||p_user_entity_name);
295
296 --
297 BEGIN
298 SELECT user_entity_id
299 INTO l_user_entity_id
300 FROM ff_user_entities
301 WHERE user_entity_name = p_user_entity_name
302 and legislation_code = p_legislation_code;
303 EXCEPTION
304 when no_data_found then
305 hr_utility.trace('user entity is not found');
306 when others then
307 hr_utility.trace('error in others of select of entity');
308 raise;
309 END ;
310 -- Add any route parameter values
311 --
312 IF p_param_value1 is not null THEN
313 --
314 INSERT into ff_route_parameter_values
315 (route_parameter_id,
316 user_entity_id,
317 value)
318 SELECT route_parameter_id,
319 l_user_entity_id,
320 p_param_value1
321 FROM ff_route_parameters
322 where route_id = l_route_id
323 and sequence_no = 1;
324 --
325 END IF;
326 --
327 IF p_param_value2 is not null THEN
328 --
329 INSERT into ff_route_parameter_values
330 (route_parameter_id,
331 user_entity_id,
332 value)
333 SELECT route_parameter_id,
334 l_user_entity_id,
335 p_param_value2
336 FROM ff_route_parameters
337 where route_id = l_route_id
338 and sequence_no = 2;
339 --
340 END IF;
341 --
342 --END;
343 --
344 hr_utility.trace('Alls well am going to create dbi');
345
346 -- Now build db item
347 --
348
349 insert into ff_database_items (
350 user_name,
351 user_entity_id,
352 data_type,
353 definition_text,
354 null_allowed_flag,
355 description,
356 last_update_date,
357 last_updated_by,
358 last_update_login,
359 created_by,
360 creation_date)
361 --
362 values (p_name,
363 l_user_entity_id,
364 p_data_type,
365 p_definition_text,
366 p_null_allowed,
367 p_description,
368 sysdate,
369 0,
370 0,
371 0,
372 sysdate);
373 --
374 END create_db_item;
375 --
376 end pay_ca_database_items_pkg;