[Home] [Help]
PACKAGE BODY: APPS.PAY_US_DATABASE_ITEMS_PKG
Source
1 package body pay_us_database_items_pkg as
2 /* $Header: pyusdbip.pkb 115.1 99/07/17 06:42:53 porting ship $ */
3 --
4 -- Copyright (c) Oracle Corporation 1991, 1992, 1993. All rights reserved.
5 --
6 /*
7 NAME
8 pyusdbip.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 S Panwar 18-SEP-1995 Created
16 T Grisco 13-DEC-1995 Added some trace statements and fixed the
17 problem of not converting p_route_name to
18 upper case when passing it to insert_user_entity
19 M Reid 06-JUN-1997 40.3 Removed show errors
20 A Myers 12-FEB-1998 110.1 Added parameter to insert_user_entity and
21 associated logic, as per pydyndbi.pkb 110.10.
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 ) IS
47 --
48 l_route_id NUMBER;
49 l_user_entity_id NUMBER;
50 l_record_inserted BOOLEAN;
51 --
52 BEGIN
53 --
54 -- Get the route id. Create a route if necessary.
55 --
56 hr_utility.set_location ('pay_us_create_col_dbi.create_table_column_dbi', 1);
57 hr_utility.trace ('p_name: ' || p_name);
58 hr_utility.trace ('p_description: ' || p_description);
59 hr_utility.trace ('p_data_type: ' || p_data_type);
60 hr_utility.trace ('p_null_allowed: ' || p_null_allowed);
61 hr_utility.trace ('p_definition_text: ' || p_definition_text);
62 hr_utility.trace ('p_user_entity_name: ' || p_user_entity_name);
63 hr_utility.trace ('p_user_entity_description: ' || p_user_entity_description);
64 hr_utility.trace ('p_route_name: ' || p_route_name);
65 hr_utility.trace ('p_route_description: ' || p_route_description);
66 hr_utility.trace ('p_route_text: ' || p_route_text);
67 hr_utility.trace ('p_context_name1: ' || p_context_name1);
68 hr_utility.trace ('p_context_name2: ' || p_context_name2);
69 --
70 BEGIN
71 --
72 SELECT route_id
73 INTO l_route_id
74 FROM ff_routes
75 WHERE route_name = upper(p_route_name);
76 --
77 EXCEPTION WHEN NO_DATA_FOUND THEN
78 --
79 -- Create the route, context usages, and parameters
80 --
81 INSERT INTO ff_routes
82 (route_id,
83 route_name,
84 user_defined_flag,
85 description,
86 text,
87 last_update_date,
88 last_updated_by,
89 last_update_login,
90 created_by,
91 creation_date)
92 VALUES
93 (ff_routes_s.nextval,
94 upper(p_route_name),
95 'N',
96 p_route_description,
97 p_route_text,
98 sysdate,
99 0,
100 0,
101 0,
102 sysdate);
103 --
104 SELECT ff_routes_s.currval
105 INTO l_route_id
106 FROM dual;
107 --
108 -- Insert any context usages
109 --
110 IF p_context_name1 is not null THEN
111 --
112 INSERT INTO ff_route_context_usages
113 (route_id,
114 context_id,
115 sequence_no)
116 select l_route_id,
117 FFC.context_id,
118 1
119 from ff_contexts FFC
120 where context_name = p_context_name1;
121 --
122 END IF;
123 --
124 IF p_context_name2 is not null THEN
125 --
126 INSERT INTO ff_route_context_usages
127 (route_id,
128 context_id,
129 sequence_no)
130 select l_route_id,
131 FFC.context_id,
132 2
133 from ff_contexts FFC
134 where context_name = p_context_name2;
135 --
136 END IF;
137 --
138 IF p_context_name3 is not null THEN
139 --
140 INSERT INTO ff_route_context_usages
141 (route_id,
142 context_id,
143 sequence_no)
144 select l_route_id,
145 FFC.context_id,
146 3
147 from ff_contexts FFC
148 where context_name = p_context_name3;
149 --
150 END IF;
151 --
152 IF p_context_name4 is not null THEN
153 --
154 INSERT INTO ff_route_context_usages
155 (route_id,
156 context_id,
157 sequence_no)
158 select l_route_id,
159 FFC.context_id,
160 4
161 from ff_contexts FFC
162 where context_name = p_context_name4;
163 --
164 END IF;
165 --
166 -- Insert any route parameters
167 --
168 IF p_param_name1 is not null THEN
169 --
170 INSERT INTO ff_route_parameters
171 (route_parameter_id,
172 route_id,
173 sequence_no,
174 parameter_name,
175 data_type)
176 VALUES
177 (ff_route_parameters_s.nextval,
178 l_route_id,
179 1,
180 p_param_name1,
181 p_param_type1);
182 --
183 END IF;
184 --
185 IF p_param_name2 is not null THEN
186 --
187 INSERT INTO ff_route_parameters
188 (route_parameter_id,
189 route_id,
190 sequence_no,
191 parameter_name,
192 data_type)
193 VALUES
194 (ff_route_parameters_s.nextval,
195 l_route_id,
196 1,
197 p_param_name2,
198 p_param_type2);
199 --
200 END IF;
201 --
202 END;
203 --
204 -- Get the user entity id. Create a user entity if necessary.
205 --
206 BEGIN
207 --
208 SELECT user_entity_id
209 INTO l_user_entity_id
210 FROM ff_user_entities
211 WHERE user_entity_name = upper(p_user_entity_name);
212 --
213 EXCEPTION WHEN NO_DATA_FOUND THEN
214 --
215 -- Create the user entity
216 --
217 hrdyndbi.insert_user_entity (
218 p_route_name => upper(p_route_name),
219 p_user_entity_name => p_user_entity_name,
220 p_entity_description => p_user_entity_description,
221 p_not_found_flag => 'Y',
222 p_creator_type => 'SEH',
223 p_creator_id => 0,
224 p_business_group_id => NULL,
225 p_legislation_code => 'US',
226 p_created_by => 0,
227 p_last_login => 0,
228 p_record_inserted => l_record_inserted);
229 --
230 SELECT user_entity_id
231 INTO l_user_entity_id
232 FROM ff_user_entities
233 WHERE user_entity_name = p_user_entity_name;
234 --
235 -- Add any route parameter values
236 --
237 IF p_param_value1 is not null AND l_record_inserted THEN
238 --
239 INSERT into ff_route_parameter_values
240 (route_parameter_id,
241 user_entity_id,
242 value)
243 SELECT route_parameter_id,
244 l_user_entity_id,
245 p_param_value1
246 FROM ff_route_parameters
247 where route_id = l_route_id
248 and sequence_no = 1;
249 --
250 END IF;
251 --
252 IF p_param_value2 is not null AND l_record_inserted THEN
253 --
254 INSERT into ff_route_parameter_values
255 (route_parameter_id,
256 user_entity_id,
257 value)
258 SELECT route_parameter_id,
259 l_user_entity_id,
260 p_param_value2
261 FROM ff_route_parameters
262 where route_id = l_route_id
263 and sequence_no = 2;
264 --
265 END IF;
266 --
267 END;
268 --
269 -- Now build db item
270 --
271 IF l_record_inserted THEN
272 insert into ff_database_items (
273 user_name,
274 user_entity_id,
275 data_type,
276 definition_text,
277 null_allowed_flag,
278 description,
279 last_update_date,
280 last_updated_by,
281 last_update_login,
282 created_by,
283 creation_date)
284 --
285 values (p_name,
286 l_user_entity_id,
287 p_data_type,
288 p_definition_text,
289 p_null_allowed,
290 p_description,
291 sysdate,
292 0,
293 0,
294 0,
295 sysdate);
296 END IF;
297 --
298 END create_db_item;
299 --
300 end pay_us_database_items_pkg;