DBA Data[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;