[Home] [Help]
PACKAGE BODY: APPS.HXC_SEEDDATA_PKG
Source
1 PACKAGE BODY HXC_SEEDDATA_PKG AS
2 /* $Header: hxcseeddatapkg.pkb 120.2 2006/03/23 02:39:59 gsirigin noship $ */
3
4 FUNCTION get_query(p_object_type in varchar2)
5 RETURN VARCHAR2
6 IS
7
8 l_layouts VARCHAR2(1000)
9 := 'select Layout_Name Value, Layout_ID ID from hxc_layouts ';
10
11 l_alias_definitions VARCHAR2(1000)
12 := 'select alias_definition_name VALUE,alias_definition_id ID from hxc_alias_definitions
13 ';
14
15 l_alias_types VARCHAR2(1000)
16 := 'select ALIAS_TYPE||''-''||REFERENCE_OBJECT Value,ALIAS_TYPE_ID ID from hxc_alias_types
17 ';
18
19
20 l_time_sources VARCHAR2(1000)
21 := 'SELECT NAME VALUE, time_source_id id
22 FROM hxc_time_sources
23 ';
24
25 l_time_recipient VARCHAR2(1000)
26 := 'SELECT NAME VALUE, time_recipient_id id
27 FROM hxc_time_recipients
28 ';
29
30 l_mapping_comps VARCHAR2(1000)
31 := 'SELECT NAME VALUE, mapping_component_id ID
32 FROM hxc_mapping_components
33 ';
34
35 l_mappings VARCHAR2(1000)
36 := 'SELECT NAME VALUE, mapping_id ID
37 FROM hxc_mappings
38 ';
39
40
41 l_retrieval_processes VARCHAR2(1000)
42 := 'SELECT NAME VALUE, retrieval_process_id id
43 FROM hxc_retrieval_processes
44 ';
45
46
47 l_retrieval_rules VARCHAR2(1000)
48 := 'SELECT NAME VALUE, retrieval_rule_id id
49 FROM hxc_retrieval_rules
50 ';
51
52
53 l_deposit_processes VARCHAR2(1000)
54 := 'SELECT NAME VALUE, deposit_process_id id
55 FROM hxc_deposit_processes
56 ';
57
58
59 l_approval_styles VARCHAR2 (1000)
60 := 'SELECT has.NAME VALUE, has.approval_style_id id
61 FROM hxc_approval_styles has
62 ';
63
64 l_data_approval_rule VARCHAR2(1000)
65 := ' SELECT dar.NAME Value, dru.data_app_rule_usage_id id
66 FROM hxc_time_entry_rules dar, hxc_data_app_rule_usages dru
67 WHERE dru.time_entry_rule_id = dar.time_entry_rule_id
68 ';
69
70 l_recurring_periods VARCHAR2(1000)
71 := 'SELECT NAME VALUE, recurring_period_id id
72 FROM hxc_recurring_periods
73 ';
74
75 l_approval_period_set VARCHAR2(1000)
76 := 'SELECT NAME VALUE, approval_period_set_id id
77 FROM hxc_approval_period_sets
78 ';
79
80
81 l_application_set VARCHAR2(1000)
82 := ' SELECT heg.NAME Value, heg.entity_group_id ID
83 FROM hxc_entity_groups heg
84 WHERE heg.entity_type = ''TIME_RECIPIENTS''
85 ';
86
87 l_time_entry_rule_groups VARCHAR2(1000)
88 := ' SELECT heg.NAME Value, heg.entity_group_id ID
89 FROM hxc_entity_groups heg
90 WHERE heg.entity_type = ''TIME_ENTRY_RULES''
91 ';
92
93
94 l_retrieval_rule_groups VARCHAR2(1000)
95 := ' SELECT heg.NAME Value, heg.entity_group_id ID
96 FROM hxc_entity_groups heg
97 WHERE heg.entity_type = ''RETRIEVAL_RULES''
98 ';
99
100
101 l_time_category VARCHAR2(1000)
102 := 'SELECT time_category_name VALUE, time_category_id id
103 FROM hxc_time_categories
104 ';
105
106
107 l_locker_types VARCHAR2(1000)
108 := 'SELECT LOCKER_TYPE||''-''||PROCESS_TYPE VALUE, locker_type_id ID
109 FROM hxc_locker_types
110 ';
111
112
113 l_pref_hierarchies VARCHAR2(1000)
114 := 'SELECT name Value,pref_hierarchy_id ID FROM hxc_pref_hierarchies
115 ';
116
117
118 l_time_entry_rules VARCHAR2(1000)
119 := 'SELECT NAME VALUE, time_entry_rule_id id
120 FROM hxc_time_entry_rules
121 ';
122
123
124 l_query varchar2(1000);
125
126 BEGIN
127
128 IF (p_object_type = 'HXC_LAYOUTS') THEN
129 l_query := l_layouts;
130 ELSIF (p_object_type = 'HXC_ALIAS_DEFINITIONS') THEN
131 l_query := l_alias_definitions;
132 ELSIF (p_object_type = 'HXC_ALIAS_TYPES') THEN
133 l_query := l_alias_types;
134 ELSIF (p_object_type = 'HXC_TIME_SOURCES') THEN
135 l_query := l_time_sources;
136 ELSIF (p_object_type = 'HXC_TIME_RECIPIENTS') THEN
137 l_query := l_time_recipient;
138 ELSIF (p_object_type = 'HXC_MAPPING_COMPONENTS') THEN
139 l_query := l_mapping_comps;
140 ELSIF (p_object_type = 'HXC_MAPPINGS') THEN
141 l_query := l_mappings;
142 ELSIF (p_object_type = 'HXC_RETRIEVAL_PROCESSES') THEN
143 l_query := l_retrieval_processes;
144 ELSIF (p_object_type = 'HXC_RETRIEVAL_RULES') THEN
145 l_query := l_retrieval_rules;
146 ELSIF (p_object_type = 'HXC_DEPOSIT_PROCESSES') THEN
147 l_query := l_deposit_processes;
148 ELSIF (p_object_type = 'HXC_APPROVAL_STYLES') THEN
149 l_query := l_approval_styles;
150 ELSIF (p_object_type = 'HXC_DATA_APPROVAL_RULES') THEN
151 l_query := l_data_approval_rule;
152 ELSIF (p_object_type = 'HXC_RECURRING_PERIODS') THEN
153 l_query := l_recurring_periods;
154 ELSIF (p_object_type = 'HXC_APPROVAL_PERIOD_SETS') THEN
155 l_query := l_approval_period_set;
156 ELSIF (p_object_type = 'HXC_APPLICATION_SETS') THEN
157 l_query := l_application_set;
158 ELSIF (p_object_type = 'HXC_TIME_ENTRY_RULE_GROUPS') THEN
159 l_query := l_time_entry_rule_groups;
160 ELSIF (p_object_type = 'HXC_RETRIEVAL_RULE_GROUPS') THEN
161 l_query := l_retrieval_rule_groups;
162 ELSIF (p_object_type = 'HXC_TIME_CATEGORIES') THEN
163 l_query := l_time_category;
164 ELSIF (p_object_type = 'HXC_LOCKER_TYPES') THEN
165 l_query := l_locker_types;
166 ELSIF (p_object_type = 'HXC_PREF_HIERARCHIES') THEN
167 l_query := l_pref_hierarchies;
168 ELSIF (p_object_type = 'HXC_TIME_ENTRY_RULES') THEN
169 l_query := l_time_entry_rules;
170 END IF;
171
172 return l_query;
173
174 END get_query;
175
176
177 FUNCTION get_value ( p_object_id in number,
178 p_object_type in varchar2 )
179 RETURN VARCHAR2
180 IS
181
182 TYPE get_value IS REF CURSOR; -- define REF CURSOR type
183 c_get_value get_value; -- declare cursor variable
184
185 l_value VARCHAR2(150);
186 l_query varchar2(1000);
187
188 BEGIN
189
190 --we need to get the values
191 --for that we need the query
192 l_query := get_query(p_object_type);
193
194 if l_query is not null then
195 --we need to modify the query a little bit
196 l_query := 'SELECT Value from ('||l_query||') where ID = :p_object_id';
197
198 OPEN c_get_value FOR l_query USING p_object_id;
199 FETCH c_get_value INTO l_value;
200 CLOSE c_get_value;
201
202 RETURN l_value;
203 else
204 RETURN null;
205 end if;
206 END get_value;
207
208 FUNCTION get_legislation_code(p_object_id in number,
209 p_object_type in varchar2 )
210 RETURN varchar2 IS
211
212
213 cursor c_alias_definitions is
214 select legislation_code from hxc_alias_definitions
215 where alias_definition_id = p_object_id;
216
217 cursor c_pref_hierarchies is
218 select legislation_code from hxc_pref_hierarchies
219 where pref_hierarchy_id = p_object_id;
220
221 cursor c_approval_styles is
222 select legislation_code from hxc_approval_styles
223 where approval_style_id = p_object_id;
224
225 cursor c_time_entry_rules is
226 select legislation_code from hxc_time_entry_rules
227 where time_entry_rule_id = p_object_id;
228
229 l_legislation_code varchar2(30);
230
231 BEGIN
232
233 IF (p_object_type = 'HXC_APPROVAL_STYLES') THEN
234 open c_alias_definitions;
235 fetch c_alias_definitions into l_legislation_code;
236 close c_alias_definitions;
237 ELSIF (p_object_type = 'HXC_TIME_ENTRY_RULES') THEN
238 open c_pref_hierarchies;
239 fetch c_pref_hierarchies into l_legislation_code;
240 close c_pref_hierarchies;
241 ELSIF (p_object_type = 'HXC_PREF_HIERARCHIES') THEN
242 open c_approval_styles;
243 fetch c_approval_styles into l_legislation_code;
244 close c_approval_styles;
245 ELSIF (p_object_type = 'HXC_ALIAS_DEFINITIONS') THEN
246 open c_time_entry_rules;
247 fetch c_time_entry_rules into l_legislation_code;
248 close c_time_entry_rules;
249 ELSE
250 l_legislation_code := null;
251 END IF;
252
253 return l_legislation_code;
254
255 END get_legislation_code;
256
257
258
259 PROCEDURE hxc_seeddata_by_level_query
260 (p_seeddata_by_level_data in out NOCOPY t_rec,
261 p_object_type in varchar2,
262 p_value in varchar2,
263 p_application_name in varchar2,
264 p_code_level_required in varchar2,
265 p_count out NOCOPY number
266 )
267 IS
268
269 TYPE ref_cur IS REF CURSOR; -- define REF CURSOR type
270
271 c_get_value ref_cur; -- declare cursor variable
272
273 l_query VARCHAR2 (2000);
274 l_index BINARY_INTEGER;
275
276
277 c_get_seed_data ref_cur;
278
279 l_get_seed_data VARCHAR2(1000) :=
280 'SELECT hsbl.owner_application_id, hsbl.hxc_required, hrl.meaning code_level_required,
281 faptl.application_name, hsbl.created_by, hsbl.creation_date,
282 hsbl.last_updated_by, hsbl.last_update_date, hsbl.last_update_login
283 FROM hxc_seeddata_by_level hsbl,
284 hr_lookups hrl,
285 fnd_application fap,
286 fnd_application_tl faptl
287 WHERE hsbl.object_id = :p_object_id
288 AND hsbl.object_type = :p_object_type
289 AND hrl.lookup_type = ''HXC_REQUIRED''
290 AND hrl.lookup_code = hsbl.hxc_required
291 AND fap.application_id = hsbl.owner_application_id
292 AND faptl.APPLICATION_ID = fap.application_id
293 AND faptl.LANGUAGE = USERENV (''LANG'')';
294
295
296 TYPE r_seed_rec is RECORD (
297 owner_application_id NUMBER (15),
298 HXC_REQUIRED VARCHAR2(30),
299 code_level_required VARCHAR2 (80),
300 application_name VARCHAR2 (240),
301 created_by NUMBER(15),
302 creation_date DATE,
303 last_updated_by number(15),
304 last_update_date date,
305 last_update_login number(15)
306 );
307
308 l_seed_rec r_seed_rec;
309
310
311 BEGIN
312
313 p_count := 0;
314
315 l_query := get_query(p_object_type);
316
317
318 IF l_query is not null then
319
320
321 if p_value is not null then
322 l_query := 'select value,id from ('||l_query||') where value like :p_value';
323 end if;
324
325 if p_application_name is not null then
326 l_get_seed_data := l_get_seed_data || ' AND faptl.application_name like :p_application_name';
327 end if;
328 if p_code_level_required is not null then
329 l_get_seed_data := l_get_seed_data ||' AND faptl.application_name like :p_code_level_required';
330 end if;
331 l_index := 1;
332
333 if p_value is not null then
334 OPEN c_get_value FOR l_query using p_value;
335 else
336 OPEN c_get_value FOR l_query;
337 end if;
338
339 LOOP
340
341 FETCH c_get_value INTO p_seeddata_by_level_data (l_index).VALUE,p_seeddata_by_level_data (l_index).object_id;
342 EXIT WHEN c_get_value%NOTFOUND;
343
344 p_seeddata_by_level_data (l_index).object_type := p_object_type;
345
346 if p_application_name is not null AND p_code_level_required is not null then
347 OPEN c_get_seed_data FOR l_get_seed_data USING p_seeddata_by_level_data (l_index).object_id,
348 p_seeddata_by_level_data (l_index).object_type, p_application_name, p_code_level_required;
349
350 elsif p_application_name is not null AND p_code_level_required is null then
351 OPEN c_get_seed_data FOR l_get_seed_data USING p_seeddata_by_level_data (l_index).object_id,
352 p_seeddata_by_level_data (l_index).object_type, p_application_name;
353
354 elsif p_application_name is null AND p_code_level_required is not null then
355 OPEN c_get_seed_data FOR l_get_seed_data USING p_seeddata_by_level_data (l_index).object_id,
356 p_seeddata_by_level_data (l_index).object_type, p_code_level_required;
357 else
358 OPEN c_get_seed_data FOR l_get_seed_data USING p_seeddata_by_level_data (l_index).object_id,
359 p_seeddata_by_level_data (l_index).object_type;
360 end if;
361
362 FETCH c_get_seed_data INTO l_seed_rec;
363
364 IF c_get_seed_data%FOUND
365 THEN
366
367 p_seeddata_by_level_data (l_index).owner_application_id := l_seed_rec.owner_application_id;
368 p_seeddata_by_level_data (l_index).code_level_required := l_seed_rec.code_level_required;
369 p_seeddata_by_level_data (l_index).application_name := l_seed_rec.application_name;
370 p_seeddata_by_level_data (l_index).created_by := l_seed_rec.created_by;
371 p_seeddata_by_level_data (l_index).creation_date := l_seed_rec.creation_date;
372 p_seeddata_by_level_data (l_index).last_updated_by := l_seed_rec.last_updated_by;
373 p_seeddata_by_level_data (l_index).last_update_date := l_seed_rec.last_update_date;
374 p_seeddata_by_level_data (l_index).last_update_login := l_seed_rec.last_update_login;
375 p_seeddata_by_level_data (l_index).hxc_required := l_seed_rec.hxc_required;
376
377 p_count := p_count + 1;
378
379 ELSE
380
381 -- check if p_application_name or p_code_level_required is not null
382 -- if so we will delete this record
383 if p_application_name is not null or p_code_level_required is not null then
384 p_seeddata_by_level_data.delete(l_index);
385 else
386 p_count := p_count + 1;
387 end if;
388
389 END IF;
390
391 CLOSE c_get_seed_data;
392
393 l_index := l_index + 1;
394 END LOOP;
395 CLOSE c_get_value;
396 END IF;
397
398 END hxc_seeddata_by_level_query;
399
400
401 END HXC_SEEDDATA_PKG;