[Home] [Help]
PACKAGE BODY: APPS.PAY_RULES_DBI_PKG
Source
1 package body pay_rules_dbi_pkg as
2 /* $Header: pywatdbi.pkb 120.0 2005/05/29 10:16:39 appldev noship $ */
3 --
4 -- Copyright (c) Oracle Corporation 1991, 1992, 1993. All rights reserved.
5 --
6 /*
7 NAME
8 pywatdbi.pkb
9 --
10 DESCRIPTION
11
12 This package is used to create a database item for every column in a
13 table. This is currently to be used by garnishments and the legislative
14 rules which will be held in tables. This package will be generic,
15 however, such that any table can have simple dbi created from its'
16 columns.
17
18 The target audience for using this package would be any table containing
19 data which would be found usefule in a payroll formula or calculation.
20 The data in this table could originate external to Oracle Payroll, yet
21 be immediately usable within a payroll run via this mechanism. The only
22 requirement being that the table is somehow keyed off one of the contexts
23 available to payroll run formulae. As an example, the legislative rules
24 required to process garnishments are "legislation dependent" - ie. are
25 keyed by Jurisdiction.
26
27 Also required is the route text to alias the "source table" as "target".
28 Feel free to extend the parameters in this package if additional context
29 values are required - currently accept 4.
30
31 The create_db_item procedure provides a single function interface for
32 the creation of database items. New routes may be created, or old
33 ones may be re-used (created originally by S Panwar).
34 --
35 MODIFIED (DD-MON-YYYY)
36 H Parichabutr 14-NOV-1995 Created
37 A.Myers 13-FEB-1998 Knock on fix from bug 602851, extra parameter
38 and logic associated with call to procedure
39 hrdyndbi.insert_user_entity
40 S.Doshi 31-MAR-1999 Flexible Dates Conversion
41 rem 110.1 19 jun 99 i harding added ; to exit
42 A.Logue 14-FEB-2000 Utf8 Support.
43
44 */
45 --
46 -- Procedures
47 --
48 PROCEDURE create_db_item(p_name VARCHAR2,
49 p_description VARCHAR2 DEFAULT NULL,
50 p_data_type VARCHAR2,
51 p_null_allowed VARCHAR2,
52 p_definition_text VARCHAR2,
53 p_user_entity_name VARCHAR2,
54 p_user_entity_description VARCHAR2 DEFAULT NULL,
55 p_route_name VARCHAR2,
56 p_param_value1 VARCHAR2 DEFAULT NULL,
57 p_param_value2 VARCHAR2 DEFAULT NULL,
58 p_route_description VARCHAR2 DEFAULT NULL,
59 p_route_text VARCHAR2 DEFAULT NULL,
60 p_context_name1 VARCHAR2 DEFAULT NULL,
61 p_context_name2 VARCHAR2 DEFAULT NULL,
62 p_context_name3 VARCHAR2 DEFAULT NULL,
63 p_context_name4 VARCHAR2 DEFAULT NULL,
64 p_param_name1 VARCHAR2 DEFAULT NULL,
65 p_param_type1 VARCHAR2 DEFAULT NULL,
66 p_param_name2 VARCHAR2 DEFAULT NULL,
67 p_param_type2 VARCHAR2 DEFAULT NULL
68 ) IS
69 --
70 l_route_id NUMBER;
71 l_user_entity_id NUMBER;
72 l_record_inserted BOOLEAN;
73 --
74 BEGIN
75 --
76 -- Get the route id. Create a route if necessary.
77 --
78 BEGIN
79 --
80 SELECT route_id
81 INTO l_route_id
82 FROM ff_routes
83 WHERE route_name = upper(p_route_name);
84 --
85 EXCEPTION WHEN NO_DATA_FOUND THEN
86 --
87 -- Create the route, context usages, and parameters
88 --
89 INSERT INTO ff_routes
90 (route_id,
91 route_name,
92 user_defined_flag,
93 description,
94 text,
95 last_update_date,
96 last_updated_by,
97 last_update_login,
98 created_by,
99 creation_date)
100 VALUES
101 (ff_routes_s.nextval,
102 upper(p_route_name),
103 'N',
104 p_route_description,
105 p_route_text,
106 sysdate,
107 0,
108 0,
109 0,
110 sysdate);
111 --
112 SELECT ff_routes_s.currval
113 INTO l_route_id
114 FROM dual;
115 --
116 -- Insert any context usages
117 --
118 IF p_context_name1 is not null THEN
119 --
120 INSERT INTO ff_route_context_usages
121 (route_id,
122 context_id,
123 sequence_no)
124 select l_route_id,
125 FFC.context_id,
126 1
127 from ff_contexts FFC
128 where context_name = p_context_name1;
129 --
130 END IF;
131 --
132 IF p_context_name2 is not null THEN
133 --
134 INSERT INTO ff_route_context_usages
135 (route_id,
136 context_id,
137 sequence_no)
138 select l_route_id,
139 FFC.context_id,
140 2
141 from ff_contexts FFC
142 where context_name = p_context_name2;
143 --
144 END IF;
145 --
146 IF p_context_name3 is not null THEN
147 --
148 INSERT INTO ff_route_context_usages
149 (route_id,
150 context_id,
151 sequence_no)
152 select l_route_id,
153 FFC.context_id,
154 3
155 from ff_contexts FFC
156 where context_name = p_context_name3;
157 --
158 END IF;
159 --
160 IF p_context_name4 is not null THEN
161 --
162 INSERT INTO ff_route_context_usages
163 (route_id,
164 context_id,
165 sequence_no)
166 select l_route_id,
167 FFC.context_id,
168 4
169 from ff_contexts FFC
170 where context_name = p_context_name4;
171 --
172 END IF;
173 --
174 -- Insert any route parameters
175 --
176 IF p_param_name1 is not null THEN
177 --
178 INSERT INTO ff_route_parameters
179 (route_parameter_id,
180 route_id,
181 sequence_no,
182 parameter_name,
183 data_type)
184 VALUES
185 (ff_route_parameters_s.nextval,
186 l_route_id,
187 1,
188 p_param_name1,
189 p_param_type1);
190 --
191 END IF;
192 --
193 IF p_param_name2 is not null THEN
194 --
195 INSERT INTO ff_route_parameters
196 (route_parameter_id,
197 route_id,
198 sequence_no,
199 parameter_name,
200 data_type)
201 VALUES
202 (ff_route_parameters_s.nextval,
203 l_route_id,
204 1,
205 p_param_name2,
206 p_param_type2);
207 --
208 END IF;
209 --
210 END;
211 --
212 -- Get the user entity id. Create a user entity if necessary.
213 --
214 BEGIN
215 --
216 SELECT user_entity_id
217 INTO l_user_entity_id
218 FROM ff_user_entities
219 WHERE user_entity_name = upper(p_user_entity_name);
220 --
221 EXCEPTION WHEN NO_DATA_FOUND THEN
222 --
223 -- Create the user entity
224 --
225 hrdyndbi.insert_user_entity (
226 p_route_name => p_route_name,
227 p_user_entity_name => p_user_entity_name,
228 p_entity_description => p_user_entity_description,
229 p_not_found_flag => 'Y',
230 p_creator_type => 'SEH',
231 p_creator_id => 0,
232 p_business_group_id => NULL,
233 p_legislation_code => 'US',
234 p_created_by => 0,
235 p_last_login => 0,
236 p_record_inserted => l_record_inserted
237 );
238 --
239 SELECT user_entity_id
240 INTO l_user_entity_id
241 FROM ff_user_entities
242 WHERE user_entity_name = p_user_entity_name;
243 --
244 -- Add any route parameter values
245 --
246 IF p_param_value1 is not null AND l_record_inserted THEN
247 --
248 INSERT into ff_route_parameter_values
249 (route_parameter_id,
250 user_entity_id,
251 value)
252 SELECT route_parameter_id,
253 l_user_entity_id,
254 p_param_value1
255 FROM ff_route_parameters
256 where route_id = l_route_id
257 and sequence_no = 1;
258 --
259 END IF;
260 --
261 IF p_param_value2 is not null AND l_record_inserted THEN
262 --
263 INSERT into ff_route_parameter_values
264 (route_parameter_id,
265 user_entity_id,
266 value)
267 SELECT route_parameter_id,
268 l_user_entity_id,
269 p_param_value2
270 FROM ff_route_parameters
271 where route_id = l_route_id
272 and sequence_no = 2;
273 --
274 END IF;
275 --
276 END;
277 --
278 -- Now build db item
279 --
280 IF l_record_inserted THEN
281 insert into ff_database_items (
282 user_name,
283 user_entity_id,
284 data_type,
285 definition_text,
286 null_allowed_flag,
287 description,
288 last_update_date,
289 last_updated_by,
290 last_update_login,
291 created_by,
292 creation_date)
293 --
294 values (p_name,
295 l_user_entity_id,
296 p_data_type,
297 p_definition_text,
298 p_null_allowed,
299 p_description,
300 sysdate,
301 0,
302 0,
303 0,
304 sysdate);
305 END IF;
306 --
307 END create_db_item;
308 --
309
310 PROCEDURE create_table_column_dbi ( p_table_name VARCHAR2,
311 p_table_short_name VARCHAR2,
312 p_route_sql VARCHAR2,
313 p_key_context1 VARCHAR2,
314 p_key_context2 VARCHAR2,
315 p_key_context3 VARCHAR2,
316 p_key_context4 VARCHAR2) IS
317
318 CURSOR get_column_details (p_tab_name IN VARCHAR2) IS
319 SELECT column_name,
320 decode(data_type, 'CHAR', 'T',
321 'VARCHAR2', 'T',
322 'LONG', 'N',
323 'LONG RAW', 'N',
324 'NUMBER', 'N',
325 'DATE', 'D',
326 'ROWID', 'T', 'T'),
327 nullable
328 FROM user_tab_columns
329 WHERE table_name = p_tab_name;
330
331 l_data_type VARCHAR2(1);
332 l_nullable VARCHAR2(1);
333 l_column_name VARCHAR2(30);
334 l_defn_text VARCHAR2(240);
335 l_dbi_name VARCHAR2(240);
336 l_desc VARCHAR2(240);
337
338 BEGIN
339
340 /*
341 Having the route text and contexts, we only need to set up the following
342 params for each call to create_db_item:
343 * p_name => Table's column name.
344 * p_data_type => Column data type.
345 * p_null_allowed => columns' nullable setting.
346 * p_definition_text => We build as 'SELECT target.'||column_name
347 p_user_entity_name => Use table short name, serves as "root" for dbiname.
348 p_user_entity_desc => Use table name.
349 p_route_name => We have.
350 p_context_name1 => We have, as well as contexts 2,3,4 if needed.
351 p_param_value1 => NULL
352 p_param_name1 => NULL
353 p_param_type1 => NULL
354
355 Database item names should never exceed 80 characters since table and
356 column names are limited to 30 characters each.
357
358 Column information can be found in the table USER_TAB_COLUMNS.
359 */
360
361 OPEN get_column_details (p_table_name);
362
363 LOOP
364
365 FETCH get_column_details
366 INTO l_column_name, l_data_type, l_nullable;
367 EXIT when get_column_details%NOTFOUND;
368
369 l_defn_text := 'SELECT target.'||l_column_name;
370
371 l_dbi_name := UPPER(p_table_short_name||'_'||l_column_name);
372
373 --
374 -- Check that dbi name will not exceed 240 characters.
375 --
376 IF LENGTH(l_dbi_name) > 240 THEN
377
378 -- DBI name too long !?
379
380 EXIT;
381
382 END IF;
383
384 l_desc := 'Generated from '||p_table_name||' table';
385 create_db_item (
386 p_name => l_dbi_name,
387 p_description => l_desc,
388 p_data_type => l_data_type,
389 p_null_allowed => l_nullable,
390 p_definition_text => l_defn_text,
391 p_user_entity_name => p_table_short_name,
392 p_user_entity_description => p_table_name,
393 p_route_name => NULL,
394 p_route_text => p_route_sql,
395 p_context_name1 => p_key_context1,
396 p_context_name2 => p_key_context2,
397 p_context_name3 => p_key_context3,
398 p_context_name4 => p_key_context4);
399 END LOOP;
400
401 CLOSE get_column_details;
402
403 END create_table_column_dbi;
404
405
406 PROCEDURE create_garntab_dbi IS
407
408 TYPE text_table IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
409 TYPE sql_table IS TABLE OF VARCHAR2(20000) INDEX BY BINARY_INTEGER;
410
411 dbi_table_source text_table;
412 dbi_table_shorts text_table;
413 route_sql sql_table;
414
415 l_date_earned_ctx VARCHAR2(30) := 'DATE_EARNED';
416 l_jurisdiction_ctx VARCHAR2(30) := 'JURISDICTION_CODE';
417 l_element_type_ctx VARCHAR2(30) := 'ELEMENT_TYPE_ID';
418
419 l_num_sources number;
420 i number;
421
422 BEGIN
423
424 /* ------------------------------------------------------------------------ */
425 dbi_table_source(1) := 'PAY_GARN_EXEMPTION_RULES';
426 dbi_table_shorts(1) := 'GARN_EXEMPTION';
427 route_sql(1) :=
428 ' PAY_GARN_EXEMPTION_RULES target,
429 PAY_ELEMENT_TYPES_F pet
430 WHERE target.state_code = substr(1,2,&B1)
431 AND target.garn_category = pet.element_information1
432 AND &B3 BETWEEN target.effective_start_date
433 AND target.effective_end_date
434 AND pet.element_type_id = &B2
435 AND &B3 BETWEEN pet.effective_start_date
436 AND pet.effective_end_date';
437 /* ------------------------------------------------------------------------ */
438
439
440 /* ------------------------------------------------------------------------ */
441 dbi_table_source(2) := 'PAY_GARN_ARREARS_RULES';
442 dbi_table_shorts(2) := 'GARN_ARREARS';
443 route_sql(2) :=
444 ' PAY_GARN_EXEMPTION_RULES target,
445 PAY_ELEMENT_TYPES_F pet
446 WHERE target.state_code = substr(1,2,&B1)
447 AND target.garn_category = pet.element_information1
448 AND &B3 BETWEEN target.effective_start_date
449 AND target.effective_end_date
450 AND pet.element_type_id = &B2
451 AND &B3 BETWEEN pet.effective_start_date
452 AND pet.effective_end_date';
453 /* ------------------------------------------------------------------------ */
454
455
456 /* ------------------------------------------------------------------------ */
457 dbi_table_source(3) := 'PAY_GARN_FEE_RULES';
458 dbi_table_shorts(3) := 'GARN_FEE';
459 route_sql(3) :=
460 ' PAY_GARN_EXEMPTION_RULES target,
461 PAY_ELEMENT_TYPES_F pet
462 WHERE target.state_code = substr(1,2,&B1)
463 AND target.garn_category = pet.element_information1
464 AND &B3 BETWEEN target.effective_start_date
465 AND target.effective_end_date
466 AND pet.element_type_id = &B2
467 AND &B3 BETWEEN pet.effective_start_date
468 AND pet.effective_end_date';
469 /* ------------------------------------------------------------------------ */
470
471
472 /* ------------------------------------------------------------------------ */
473 dbi_table_source(4) := 'PAY_GARN_LIMIT_RULES';
474 dbi_table_shorts(4) := 'GARN_LIMIT';
475 route_sql(4) :=
476 ' PAY_GARN_EXEMPTION_RULES target,
477 PAY_ELEMENT_TYPES_F pet
478 WHERE target.state_code = substr(1,2,&B1)
479 AND target.garn_category = pet.element_information1
480 AND &B3 BETWEEN target.effective_start_date
481 AND target.effective_end_date
482 AND pet.element_type_id = &B2
483 AND &B3 BETWEEN pet.effective_start_date
484 AND pet.effective_end_date';
485 /* ------------------------------------------------------------------------ */
486
487
488 for i in 1..l_num_sources LOOP
489
490 pay_rules_dbi_pkg.create_table_column_dbi(
491 p_table_name => dbi_table_source(i),
492 p_table_short_name => dbi_table_shorts(i),
493 p_route_sql => route_sql(i),
494 p_key_context1 => l_jurisdiction_ctx,
495 p_key_context2 => l_element_type_ctx,
496 p_key_context3 => l_date_earned_ctx,
497 p_key_context4 => NULL);
498
499 END LOOP;
500
501 END create_garntab_dbi;
502
503 end pay_rules_dbi_pkg;