[Home] [Help]
PACKAGE BODY: APPS.HR_DM_SEED_DP
Source
1 package body hr_dm_seed_dp as
2 /* $Header: perdmsed.pkb 120.0 2005/05/31 17:14:08 appldev noship $ */
3
4 -- ----------------------- seed_api -------------------------------------
5 -- Description:
6 -- Create API module information. This tells the data pump that this is the
7 -- API which will be used for uploading.
8 -- ------------------------------------------------------------------------
9 procedure seed_api
10 (
11 p_module_name in varchar2,
12 p_module_type in varchar2,
13 p_module_package in varchar2,
14 p_data_within_business_group in varchar2 default 'Y',
15 p_legislation_code in varchar2 default null
16 ) is
17 begin
18 hr_dm_utility.message('ROUT','entry:hr_dm_seed_dp.seed_api', 5);
19 hr_dm_utility.message('PARA','(p_module_name - ' || p_module_name ||
20 ')(p_module_type - ' || p_module_type ||
21 ')(p_module_package - ' || p_module_package ||
22 ')(p_data_within_business_group - ' ||
23 p_data_within_business_group ||
24 ')(p_legislation_code - ' || p_legislation_code ||
25 ')', 10);
26
27 -- Insert if the API module does not
28 -- already exist.
29 insert into hr_api_modules (
30 api_module_id,
31 api_module_type,
32 module_name,
33 data_within_business_group,
34 legislation_code,
35 module_package)
36 select hr_api_modules_s.nextval,
37 p_module_type,
38 p_module_name,
39 p_data_within_business_group,
40 p_legislation_code,
41 p_module_package
42 from sys.dual
43 where not exists (
44 select null
45 from hr_api_modules m
46 where m.module_name = p_module_name
47 and m.api_module_type = p_module_type);
48
49 hr_dm_utility.message('ROUT','exit:hr_dm_seed_dp.seed_api', 25);
50 -- error handling
51 exception
52 when others then
53 hr_dm_utility.error(SQLCODE,'hr_dm_seed_dp.seed_api',
54 '(p_module_name - ' || p_module_name ||
55 ')(p_module_type - ' || p_module_type ||
56 ')(p_module_package - ' || p_module_package ||
57 ')(p_data_within_business_group - ' ||
58 p_data_within_business_group ||
59 ')(p_legislation_code - ' || p_legislation_code ||
60 ')','R');
61 raise;
62 end seed_api;
63
64 -- ----------------------- insert_dp_parameters --------------------------------
65 -- Description:
66 -- Create API module parameter information.
67 -- ------------------------------------------------------------------------
68
69 procedure insert_dp_parameters
70 (
71 p_module_name in varchar2,
72 p_module_type in varchar2,
73 p_parameter_name in varchar2,
74 p_mapping_type in varchar2 default 'NORMAL',
75 p_mapping_def in varchar2 default null,
76 p_default_value in varchar2 default null
77 ) is
78 begin
79 hr_dm_utility.message('ROUT','entry:hr_dm_seed_dp.insert_dp_parameters', 5);
80 hr_dm_utility.message('PARA','(p_module_name - ' || p_module_name ||
81 ')(p_module_type - ' || p_module_type ||
82 ')(p_parameter_name - ' || p_parameter_name ||
83 ')(p_mapping_type - ' || p_mapping_type ||
84 ')(p_mapping_def - ' || p_mapping_def ||
85 ')(p_default_value - ' || p_default_value ||
86 ')', 10);
87 -- Insert if the API module does not
88 -- already exist.
89 insert into hr_pump_module_parameters (
90 module_name,
91 api_module_type,
92 api_parameter_name,
93 mapping_type,
94 mapping_definition,
95 default_value)
96 select p_module_name,
97 p_module_type,
98 ltrim(lpad(p_parameter_name,30)),
99 p_mapping_type,
100 p_mapping_def,
101 p_default_value
102 from sys.dual
103 where not exists (
104 select null
105 from hr_pump_module_parameters p
106 where p.module_name = p_module_name
107 and p.api_module_type = p_module_type
108 and p.api_parameter_name = p_parameter_name);
109 hr_dm_utility.message('ROUT','exit:hr_dm_seed_dp.insert_dp_parameters', 25);
110 -- error handling
111 exception
112 when others then
113 hr_dm_utility.error(SQLCODE,'hr_dm_seed_dp.insert_dp_parameters',
114 '(p_module_name - ' || p_module_name ||
115 ')(p_module_type - ' || p_module_type ||
116 ')(p_parameter_name - ' || p_parameter_name ||
117 ')(p_mapping_type - ' || p_mapping_type ||
118 ')(p_mapping_def - ' || p_mapping_def ||
119 ')(p_default_value - ' || p_default_value ||
120 ')','R');
121 raise;
122 end insert_dp_parameters;
123 -- ----------------------- seed_id_parameters --------------------------------
124 -- Description:
125 -- Create API module parameter information. All the parameters of the module
126 -- has to be specifically defined in the data pump so as it should use the
127 -- value of the parameter passed rather than using the lookup keys.
128 --
129 -- ------------------------------------------------------------------------
130
131 procedure seed_id_parameters
132 (
133 p_module_name in varchar2,
134 p_module_type in varchar2,
135 p_columns_tbl in hr_dm_library.t_varchar2_tbl
136 ) is
137 l_list_index number;
138 l_column_name varchar2(30);
139 begin
140
141 hr_dm_utility.message('ROUT','entry:hr_dm_seed_dp.seed_id_parameters', 5);
142 hr_dm_utility.message('PARA','(p_module_name - ' || p_module_name ||
143 ')(p_module_type - ' || p_module_type ||
144 ')', 10);
145
146 -- initialise the variables
147 l_list_index := p_columns_tbl.first;
148
149 --
150 -- read all the elements of pl/sql table i.e columns of the SQL table.
151 -- if the last three characters are '_id', then we have to seed this
152 -- column name into data pump so as it should not use the user key for
153 -- this id column.
154 --
155 while l_list_index is not null loop
156 l_column_name := upper(p_columns_tbl(l_list_index));
157 --
158 -- Do not assign Business_Group_Id column as data pump knows the value of
159 -- business_group_id if the parameter p_omit_business_group_id value is 'Y'
160 --
161 if substr(l_column_name,-3,3) = '_ID' and
162 l_column_name <> 'BUSINESS_GROUP_ID'
163 then
164 -- insert the row in data pump parameters table for this column.
165 insert_dp_parameters ( p_module_name => p_module_name,
166 p_module_type => p_module_type,
167 p_parameter_name => 'P_' || l_column_name);
168
169 end if;
170 l_list_index := p_columns_tbl.next(l_list_index);
171 end loop;
172 hr_dm_utility.message('ROUT','exit:hr_dm_seed_dp.seed_id_parameters', 25);
173 -- error handling
174 exception
175 when others then
176 hr_dm_utility.error(SQLCODE,'hr_dm_seed_dp.seed_id_parameters',
177 '(p_module_name - ' || p_module_name ||
178 ')(p_module_type - ' || p_module_type ||
179 ')','R');
180 raise;
181 end seed_id_parameters;
182 -- ----------------------- seed_data --------------------------------
183 -- Description:
184 -- seed the data pump data for a TUPS.
185 -- Steps required :
186 -- seed the TUPS upload module for the table.
187 -- for each ID column of the table call seed_id_parameter table.
188 -- if table has a column hierarchy then
189 -- seed the TUPS upload hierarchy data module as well
190 --
191 -- Input Parameters :
192 -- p_table_info - Information about table.
193 -- p_columns_tbl - List of all columns of table.
194 -- p_seed_type - Type of TUPS to be seeded.Can have following
195 -- values :
196 -- 'NORMAL' - Normal TUPS procedure to upload data.
197 -- 'HIERARCHY' - TUPS procedure to update the hierarchy
198 --
199 -- --------------------------------------------------------------
200 procedure seed_data
201 (
202 p_table_info in hr_dm_gen_main.t_table_info ,
203 p_columns_tbl in hr_dm_library.t_varchar2_tbl ,
204 p_seed_type in varchar2
205 )
206
207 is
208 l_module_name varchar2(30);
209 l_module_type varchar2(30);
210 l_module_package varchar2(30);
211 begin
212 hr_dm_utility.message('ROUT','entry:hr_dm_seed_dp.seed_data', 5);
213 hr_dm_utility.message('PARA','(p_seed_type - ' || p_seed_type ||
214 ')', 10);
215 --
216 -- initialise the data. set the standatrd TUPS module and
217 -- main upload procedure name
218 --
219 if p_seed_type = 'NORMAL' then
220 l_module_name := upper('u' || p_table_info.short_name);
221 else
222 l_module_name := upper('h' || p_table_info.short_name);
223 end if;
224
225 l_module_type := 'DM';
226 l_module_package := upper('hrdmu_'|| p_table_info.short_name);
227
228 -- seed the TUPS module
229 seed_api ( p_module_name => l_module_name,
230 p_module_type => l_module_type,
231 p_module_package => l_module_package);
232
233 -- seed the parameter id
234 seed_id_parameters( p_module_name => l_module_name,
235 p_module_type => l_module_type,
236 p_columns_tbl => p_columns_tbl);
237 commit;
238
239 -- generate the view and package for the TUPS module seeded
240 -- above.
241
242 hr_dm_utility.message('INFO','Calling datapump meta mapper with ' ||
243 'parameters of (l_module_package - ' ||
244 l_module_package || ')(l_module_name - ' ||
245 l_module_name || ')', 15);
246 begin
247 hr_pump_meta_mapper.generate(l_module_package,
248 l_module_name);
249 exception
250 when others then
251 hr_dm_utility.error(SQLCODE,'hr_dm_seed_dp.seed_data',
252 'Error from hr_pump_meta_mapper.generate'
253 ,'R');
254 raise;
255 end;
256 hr_dm_utility.message('ROUT','exit:hr_dm_seed_dp.seed_data', 25);
257 -- error handling
258 exception
259 when others then
260 hr_dm_utility.error(SQLCODE,'hr_dm_seed_dp.seed_data',
261 '(p_seed_type - ' || p_seed_type ||
262 ')','R');
263 raise;
264 end seed_data;
265 -- ----------------------- main --------------------------------
266 -- Description:
267 -- Main program which will seed the data pump data for a TUPS.
268 -- calls seed_data function to seed the data into data pump.
269 -- if table has a column hierarchy then it seed the column
270 -- hierarchy function as well.
271 -- Input Parameters :
272 -- p_table_info - PL/SQL record containing info about
273 -- current table.
274 -- p_columns_tbl - List of all columns of the table.
275 -- --------------------------------------------------------------
276 procedure main
277 (
278 p_table_info in hr_dm_gen_main.t_table_info ,
279 p_columns_tbl in hr_dm_library.t_varchar2_tbl
280 )
281
282 is
283 begin
284 hr_dm_utility.message('ROUT','entry:hr_dm_seed_dp.main', 5);
285 seed_data(p_table_info => p_table_info,
286 p_columns_tbl => p_columns_tbl,
287 p_seed_type => 'NORMAL');
288
289 -- if table has a column hierarchy then seed update hierarchical cols
290 -- procedure.
291
292 if p_table_info.column_hierarchy = 'Y' then
293 seed_data(p_table_info => p_table_info,
294 p_columns_tbl => p_columns_tbl,
295 p_seed_type => 'HIERARCHY');
296 end if;
297 hr_dm_utility.message('ROUT','exit:hr_dm_seed_dp.main', 25);
298 -- error handling
299 exception
300 when others then
301 hr_dm_utility.error(SQLCODE,'hr_dm_seed_dp.main','(none)','R');
302 raise;
303 end main;
304
305 end hr_dm_seed_dp ;