[Home] [Help]
PACKAGE: APPS.HXC_ALIAS_UTILITY
Source
1 PACKAGE HXC_ALIAS_UTILITY AS
2 /* $Header: hxcaltutl.pkh 120.1 2005/06/15 17:37:27 jdupont noship $ */
3 -- ----------------------------------------------------------------------------
4 -- |---------------------------< STATIC VARIABLE >--------------------|
5 -- ----------------------------------------------------------------------------
6 ALIAS_SEPARATOR VARCHAR2(80) := 'ALIAS_SEPARATOR';
7
8
9 -- ----------------------------------------------------------------------------
10 -- |---------------------------< TYPE DECLARATION >--------------------|
11 -- ----------------------------------------------------------------------------
12 --
13 TYPE r_alias_def_item IS RECORD
14 (ALIAS_DEFINITION_ID HXC_ALIAS_DEFINITIONS.ALIAS_DEFINITION_ID%TYPE
15 ,ITEM_ATTRIBUTE_CATEGORY VARCHAR2(80)
16 ,RESOURCE_ID NUMBER
17 ,LAYOUT_ID NUMBER
18 ,ALIAS_LABEL VARCHAR2(80)
19 ,PREF_START_DATE DATE
20 ,PREF_END_DATE DATE
21 );
22
23 TYPE t_alias_def_item IS TABLE OF
24 r_alias_def_item
25 INDEX BY BINARY_INTEGER;
26 --
27 --
28 TYPE r_tbb_id_ref is RECORD
29 (ATTRIBUTE_INDEX VARCHAR2(2000),
30 NUMBER_ALIAS NUMBER
31 );
32
33 TYPE t_tbb_id_reference is TABLE OF
34 r_tbb_id_ref
35 INDEX BY BINARY_INTEGER;
36 --
37 --
38
39 TYPE r_tbb_date_ref is RECORD
40 (START_TIME DATE,
41 STOP_TIME DATE
42 );
43
44 TYPE t_tbb_date_reference_table is TABLE OF
45 r_tbb_date_ref
46 INDEX BY BINARY_INTEGER;
47 --
48 --
49 TYPE r_alias_val_att_to_match is RECORD
50 (BLD_BLK_INFO_TYPE hxc_bld_blk_info_types.bld_blk_info_type%TYPE
51 ,ATTRIBUTE_CATEGORY hxc_bld_blk_info_types.bld_blk_info_type%TYPE
52 ,ATTRIBUTE1 hxc_time_attributes.attribute1%TYPE
53 ,ATTRIBUTE2 hxc_time_attributes.attribute2%TYPE
54 ,ATTRIBUTE3 hxc_time_attributes.attribute3%TYPE
55 ,ATTRIBUTE4 hxc_time_attributes.attribute4%TYPE
56 ,ATTRIBUTE5 hxc_time_attributes.attribute5%TYPE
57 ,ATTRIBUTE6 hxc_time_attributes.attribute6%TYPE
58 ,ATTRIBUTE7 hxc_time_attributes.attribute7%TYPE
59 ,ATTRIBUTE8 hxc_time_attributes.attribute8%TYPE
60 ,ATTRIBUTE9 hxc_time_attributes.attribute9%TYPE
61 ,ATTRIBUTE10 hxc_time_attributes.attribute10%TYPE
62 ,ATTRIBUTE11 hxc_time_attributes.attribute11%TYPE
63 ,ATTRIBUTE12 hxc_time_attributes.attribute12%TYPE
64 ,ATTRIBUTE13 hxc_time_attributes.attribute13%TYPE
65 ,ATTRIBUTE14 hxc_time_attributes.attribute14%TYPE
66 ,ATTRIBUTE15 hxc_time_attributes.attribute15%TYPE
67 ,ATTRIBUTE16 hxc_time_attributes.attribute16%TYPE
68 ,ATTRIBUTE17 hxc_time_attributes.attribute17%TYPE
69 ,ATTRIBUTE18 hxc_time_attributes.attribute18%TYPE
70 ,ATTRIBUTE19 hxc_time_attributes.attribute19%TYPE
71 ,ATTRIBUTE20 hxc_time_attributes.attribute20%TYPE
72 ,ATTRIBUTE21 hxc_time_attributes.attribute21%TYPE
73 ,ATTRIBUTE22 hxc_time_attributes.attribute22%TYPE
74 ,ATTRIBUTE23 hxc_time_attributes.attribute23%TYPE
75 ,ATTRIBUTE24 hxc_time_attributes.attribute24%TYPE
76 ,ATTRIBUTE25 hxc_time_attributes.attribute25%TYPE
77 ,ATTRIBUTE26 hxc_time_attributes.attribute26%TYPE
78 ,ATTRIBUTE27 hxc_time_attributes.attribute27%TYPE
79 ,ATTRIBUTE28 hxc_time_attributes.attribute28%TYPE
80 ,ATTRIBUTE29 hxc_time_attributes.attribute29%TYPE
81 ,ATTRIBUTE30 hxc_time_attributes.attribute30%TYPE
82 ,BLD_BLK_INFO_TYPE_ID hxc_time_attributes.bld_blk_info_type_id%TYPE
83 ,COMPONENT_TYPE hxc_alias_type_components.component_type%TYPE
84 ,COMPONENT_NAME hxc_alias_type_components.component_name%TYPE
85 ,REFERENCE_OBJECT hxc_alias_types.reference_object%TYPE
86 ,MAPPING_ATT_CAT hxc_bld_blk_info_type_usages.building_block_category%TYPE
87 ,SEGMENT VARCHAR2(80)
88 );
89
90 TYPE t_alias_val_att_to_match is TABLE OF
91 r_alias_val_att_to_match
92 INDEX BY BINARY_INTEGER;
93
94 --
95 --
96 TYPE r_alias_val_att_rec is RECORD
97 (START_INDEX NUMBER
98 ,END_INDEX NUMBER
99 );
100
101 TYPE t_alias_val_att_rec is TABLE OF
102 r_alias_val_att_rec
103 INDEX BY BINARY_INTEGER;
104
105 --
106 --
107 TYPE r_alias_definition_info IS RECORD
108 (ALIAS_TYPE hxc_alias_types.alias_type%type,
109 REFERENCE_OBJECT hxc_alias_types.reference_object%type,
110 PROMPT hxc_alias_definitions_tl.prompt%type);
111
112 TYPE t_alias_definition_info is TABLE OF
113 r_alias_definition_info
114 INDEX BY BINARY_INTEGER;
115 --
116 --
117 TYPE r_alias_att_info IS RECORD
118 (TIMEKEEPER_ID NUMBER,
119 ALIAS_DEFINITION_ID VARCHAR2(80),
120 ALIAS_TYPE hxc_alias_types.alias_type%TYPE);
121 -- BLD_BLK_INFO_TYPE_ID NUMBER,
122 -- BLD_BLK_INFO_TYPE VARCHAR2(80));
123
124 TYPE t_alias_att_info is TABLE OF
125 r_alias_att_info
126 INDEX BY BINARY_INTEGER;
127
128 --
129 --
130 TYPE r_alias_apps_tab_info IS RECORD
131 (APPS_TAB_NAME VARCHAR2(240));
132
133 TYPE t_alias_apps_tab_info is TABLE OF
134 r_alias_apps_tab_info
135 INDEX BY BINARY_INTEGER;
136 --
137 --
138 TYPE t_alias_att_ref_rec IS RECORD
139 (ATTRIBUTE_INDEX NUMBER,
140 OTL_ALIAS_TYPE VARCHAR2(80),
141 OTL_ALIAS_ATT VARCHAR2(80));
142
143 TYPE t_alias_att_ref_table is TABLE OF
144 t_alias_att_ref_rec
145 INDEX BY BINARY_INTEGER;
146
147
148 -- ----------------------------------------------------------------------------
149 -- |---------------------------< VARIABLE >--------------------|
150 -- ----------------------------------------------------------------------------
151 c_tk_processing VARCHAR2(80) := 'TIMEKEEPER_PROCESSING';
152 c_ss_processing VARCHAR2(80) := 'SS_PROCESSING';
153
154
155 -- ----------------------------------------------------------------------------
156 -- |---------------------------< GLOBAL DECLARATION >--------------------|
157 -- ----------------------------------------------------------------------------
158 g_alias_def_item t_alias_def_item;
159 g_comp_label t_alias_val_att_to_match;
160 g_alias_val_att_to_match t_alias_val_att_to_match;
161 g_alias_def_att_to_match t_alias_val_att_to_match;
162 g_alias_def_att_rec t_alias_val_att_rec;
163 g_alias_def_val_att_rec t_alias_val_att_rec;
164 g_alias_definition_info t_alias_definition_info;
165 g_alias_att_info t_alias_att_info;
166 g_alias_apps_tab_info t_alias_apps_tab_info;
167
168 g_layout_attribute HXC_ATTRIBUTE_TABLE_TYPE;
169
170
171 -- ----------------------------------------------------------------------------
172 -- |--------------------< PROCEDURE/FUNCTION DECLARATION >--------------------|
173 -- ----------------------------------------------------------------------------
174 --
175 -- initialize the global variable
176 --
177 PROCEDURE initialize;
178
179 -- -----------------------------------------------------------------------------|
180 -- |------------------------< process_attribute >---------------------|
181 -- -----------------------------------------------------------------------------|
182
183 FUNCTION process_attribute(p_attribute HXC_ATTRIBUTE_TYPE)
184 RETURN BOOLEAN;
185
186 -- ----------------------------------------------------------------------------
187 -- |------------------------< get_alias_att_info >--------------------|
188 -- ----------------------------------------------------------------------------
189 PROCEDURE get_alias_att_info
190 (p_timekeeper_id IN NUMBER,
191 p_alias_att_info IN OUT NOCOPY t_alias_att_info);
192
193 -- ----------------------------------------------------------------------------
194 -- |----------------------< get_next_negative_attribute_id>--------------------|
195 -- ----------------------------------------------------------------------------
196
197 FUNCTION get_next_negative_attribute_id(
198 p_attributes IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE--hxc_self_service_time_deposit.building_block_attribute_info
199 )
200 RETURN NUMBER;
201
202 -- ----------------------------------------------------------------------------
203 -- |--------------------------< set_attribute_information>--------------------|
204 -- ----------------------------------------------------------------------------
205 PROCEDURE set_attribute_information
206 (p_attributes IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE,--hxc_self_service_time_deposit.building_block_attribute_info,
207 p_index_in_table IN NUMBER,
208 p_attribute_to_set IN VARCHAR2,
209 p_value_to_set IN VARCHAR2);
210
211 -- ----------------------------------------------------------------------------
212 -- |--------------------------< get_attribute_information>--------------------|
213 -- ----------------------------------------------------------------------------
214 PROCEDURE get_attribute_information
215 (p_attributes IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE,--hxc_self_service_time_deposit.building_block_attribute_info,
216 p_index_in_table IN NUMBER,
217 p_attribute_to_get IN VARCHAR2,
218 p_get_value OUT NOCOPY VARCHAR2);
219
220
221
222 -- ----------------------------------------------------------------------------
223 -- |--------------------------< get_attribute_to_match_info --------------------|
224 -- ----------------------------------------------------------------------------
225 PROCEDURE get_attribute_to_match_info
226 (p_attribute_to_match IN OUT NOCOPY t_alias_val_att_to_match,
227 p_index_in_table IN NUMBER,
228 p_attribute_to_get IN VARCHAR2,
229 p_get_value OUT NOCOPY VARCHAR2);
230
231 -- ----------------------------------------------------------------------------
232 -- |--------------------------< set_attribute_to_match_info>--------------------|
233 -- ----------------------------------------------------------------------------
234 PROCEDURE set_attribute_to_match_info
235 (p_attribute_to_match IN OUT NOCOPY t_alias_val_att_to_match,
236 p_index_in_table IN NUMBER,
237 p_attribute_to_set IN VARCHAR2,
238 p_bld_blk_info_type IN VARCHAR2,
239 p_mapping_att_cat IN VARCHAR2,
240 p_value_to_set IN VARCHAR2);
241
242
243 -- ----------------------------------------------------------------------------
244 -- |------------------------< attribute_check> --------------------|
245 -- ----------------------------------------------------------------------------
246 PROCEDURE attribute_check
247 (p_bld_blk_info_type_id IN NUMBER
248 ,p_time_building_block_id IN hxc_time_building_blocks.time_building_block_id%TYPE
249 ,p_attributes IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE--hxc_self_service_time_deposit.building_block_attribute_info
250 ,p_tbb_id_reference_table IN OUT NOCOPY t_tbb_id_reference
251 ,p_attribute_index IN OUT NOCOPY NUMBER
252 ,p_attribute_found IN OUT NOCOPY BOOLEAN);
253
254 -- ----------------------------------------------------------------------------
255 -- |------------------------< get_alias_val_att_to_match>--------------------|
256 -- ----------------------------------------------------------------------------
257 PROCEDURE get_alias_val_att_to_match
258 (p_alias_definition_id IN NUMBER,
259 p_alias_value_id IN NUMBER,
260 p_alias_val_att_to_match IN OUT NOCOPY t_alias_val_att_to_match);
261
262 -- ----------------------------------------------------------------------------
263 -- |------------------------< get_alias_val_att_to_match>--------------------|
264 -- ----------------------------------------------------------------------------
265 PROCEDURE get_alias_val_att_to_match
266 (p_alias_definition_id IN NUMBER,
267 p_alias_val_att_to_match IN OUT NOCOPY t_alias_val_att_to_match);
268
269 -- ----------------------------------------------------------------------------
270 -- |--------------------------< get_tbb_id_reference_table>--------------------|
271 -- ----------------------------------------------------------------------------
272 PROCEDURE get_tbb_id_reference_table
273 (p_attributes IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE,--hxc_self_service_time_deposit.building_block_attribute_info,
274 p_tbb_id_reference_table IN OUT NOCOPY t_tbb_id_reference);
275
276 -- ----------------------------------------------------------------------------
277 -- |--------------------------< get_tbb_date_reference_table>--------------------|
278 -- ----------------------------------------------------------------------------
279 PROCEDURE get_tbb_date_reference_table
280 (p_blocks IN OUT NOCOPY HXC_BLOCK_TABLE_TYPE,--hxc_self_service_time_deposit.timecard_info,
281 p_tbb_date_reference_table IN OUT NOCOPY t_tbb_date_reference_table,
282 p_timecard_start_time OUT NOCOPY DATE,
283 p_timecard_stop_time OUT NOCOPY DATE);
284
285
286 -- ----------------------------------------------------------------------------
287 -- |------------------------< get_alias_def_item for SS >--------------------|
288 -- ----------------------------------------------------------------------------
289 -- | This procedure returns by looking on the preference of the timekeeper |
290 -- | a pl/sql table that contains the alias attribute information |
291 --------------------------------------------------------------------------------
292 PROCEDURE get_alias_def_item
293 (p_resource_id IN NUMBER,
294 p_attributes IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE,--hxc_self_service_time_deposit.building_block_attribute_info,
295 p_alias_def_item IN OUT NOCOPY t_alias_def_item,
296 p_start_time IN DATE,
297 p_stop_time IN DATE,
298 p_cache_label IN BOOLEAN DEFAULT FALSE);
299
300 -- ----------------------------------------------------------------------------
301 -- |---------------------------< get_alias_def_item >--------------------|
302 -- ----------------------------------------------------------------------------
303 --
304 -- This function return the alias defintion item information
305
306 PROCEDURE get_alias_def_item
307 (p_timekeeper_id in NUMBER
308 ,p_alias_def_item IN OUT NOCOPY t_alias_def_item);
309
310
311 -- ----------------------------------------------------------------------------
312 -- |---------------------------< get_alias_definition_info >--------------------|
313 -- ----------------------------------------------------------------------------
314 --
315 -- This function return the alias defintion information
316
317 PROCEDURE get_alias_definition_info
318 (p_alias_definition_id in number,
319 p_alias_type out nocopy varchar2,
320 p_reference_object out nocopy varchar2,
321 p_prompt out nocopy varchar2);
322
323 -- ----------------------------------------------------------------------------
324 -- |---------------------------< get_alias_def_from_value >--------------------|
325 -- ----------------------------------------------------------------------------
326 --
327 -- This function return the alias defintion id from an alias value id
328
329 FUNCTION get_alias_def_from_value
330 (p_alias_value_id in number)
331 RETURN NUMBER;
332
333 -- ----------------------------------------------------------------------------
334 -- |---------------------------< get_vset_table_type_select >--------------------|
335 -- ----------------------------------------------------------------------------
336 --
337 -- This function return the SQL associate to a specifiy alternate name definition
338 -- with VALUE_SET_TABLE Type
339
340 PROCEDURE get_vset_table_type_select
341 (p_alias_definition_id IN NUMBER,
342 x_select OUT NOCOPY VARCHAR2,
343 p_id_type OUT NOCOPY VARCHAR2);
344
345 -- ----------------------------------------------------------------------------
346 -- |---------------------------< get_vset_table_type_select >--------------------|
347 -- ----------------------------------------------------------------------------
348 --
349 -- This function return the SQL associate to a specifiy alternate name definition
350 -- with VALUE_SET_INDEPENDENT Type
351 PROCEDURE get_vset_indep_type_select
352 (p_alias_definition_id IN NUMBER,
353 x_select OUT NOCOPY VARCHAR2);
354
355 -- ----------------------------------------------------------------------------
359 -- This function return the property associate to a specifiy alternate name definition
356 -- |------------------------< get_vset_none_type_property >--------------------|
357 -- ----------------------------------------------------------------------------
358 --
360 -- with VALUE_SET_NONE Type
361 --
362 PROCEDURE get_vset_none_type_property
363 (p_alias_definition_id IN NUMBER,
364 p_format_type OUT NOCOPY VARCHAR2,
365 p_maximum_size OUT NOCOPY NUMBER,
366 p_minimum_value OUT NOCOPY NUMBER,
367 p_maximum_value OUT NOCOPY NUMBER,
368 p_number_precision OUT NOCOPY NUMBER
369 );
370 -- ----------------------------------------------------------------------------
371 -- |--------------------< get_otl_an_context_type_select >--------------------|
372 -- ----------------------------------------------------------------------------
373 PROCEDURE get_otl_an_context_type_select
374 (p_alias_definition_id IN NUMBER,
375 p_timekeeper_person_type IN VARCHAR2 DEFAULT NULL,
376 x_select OUT NOCOPY VARCHAR2);
377
378 -- ----------------------------------------------------------------------------
379 -- |--------------------< get_value_from_index >--------------------|
380 -- ----------------------------------------------------------------------------
381 FUNCTION get_value_from_index
382 (p_str VARCHAR2
383 ,p_index NUMBER
384 )
385 RETURN VARCHAR2;
386
387 -- ----------------------------------------------------------------------------
388 -- |--------------------< query_invoice >--------------------|
389 -- ----------------------------------------------------------------------------
390 FUNCTION query_invoice(p_select IN VARCHAR2)
391 RETURN VARCHAR2;
392
393 -- ----------------------------------------------------------------------------
394 -- |--------------------< get_apps_table_from_type >--------------------|
395 -- ----------------------------------------------------------------------------
396 FUNCTION get_apps_table_from_type(p_alias_definition_id IN VARCHAR2)
397 RETURN VARCHAR2;
398
399 -- ----------------------------------------------------------------------------
400 -- |--------------------< get_alias_att_to_match_to_dep >--------------------|
401 -- ----------------------------------------------------------------------------
402 PROCEDURE get_alias_att_to_match_to_dep(p_alias_definition_id NUMBER
403 ,p_alias_old_value_id NUMBER
404 ,p_alias_type VARCHAR2
405 ,p_original_value VARCHAR2
406 ,p_alias_val_att_to_match OUT NOCOPY t_alias_val_att_to_match
407 ,p_att_to_delete OUT NOCOPY BOOLEAN);
408
409 -- ----------------------------------------------------------------------------
410 -- |---------------------------< remove_empty_attribute >--------------------|
411 -- ----------------------------------------------------------------------------
412 /*
413 PROCEDURE remove_empty_attribute
414 (p_attributes in out NOCOPY hxc_self_service_time_deposit.building_block_attribute_info);
415 */
416 -- ----------------------------------------------------------------------------
417 -- |---------------------------< get_sfl_from_alias_value >--------------------|
418 -- ----------------------------------------------------------------------------
419 FUNCTION get_sfl_from_alias_value(p_alias_value_id IN VARCHAR2)
420 RETURN VARCHAR2;
421
422 -- ----------------------------------------------------------------------------
423 -- |---------------------------< time_entry_rules_segment_trans >--------------------|
424 -- ----------------------------------------------------------------------------
425 PROCEDURE time_entry_rules_segment_trans
426 (p_timecard_id IN NUMBER
427 ,p_timecard_ovn IN NUMBER
428 ,p_start_time IN DATE
429 ,p_stop_time IN DATE
430 ,p_resource_id IN NUMBER
431 ,p_attr_change_table IN OUT NOCOPY hxc_time_entry_rules_utils_pkg.t_change_att_tab);
432
433
434 -- ----------------------------------------------------------------------------
435 -- |---------------------------< debug procedure >--------------------|
436 -- ----------------------------------------------------------------------------
437 PROCEDURE dump_alias_val_att_to_match (p_alias_val_att_to_match IN OUT NOCOPY t_alias_val_att_to_match);
438 PROCEDURE dump_bb_attribute_info
439 (p_attributes in out NOCOPY HXC_ATTRIBUTE_TABLE_TYPE);--hxc_self_service_time_deposit.building_block_attribute_info);
440 PROCEDURE dump_alias_def_item (p_alias_def_item IN OUT NOCOPY t_alias_def_item);
441
442
443 ----------------------------------------------------------------------------------
444 --- TEMPORARY FUNCTION
445 ----------------------------------------------------------------------------------
446
447 -- USE IN SS
448 PROCEDURE alias_def_comma_list(p_alias_type IN VARCHAR2
449 ,p_start_time IN VARCHAR2
450 ,p_stop_time IN VARCHAR2
451 ,p_resource_id IN NUMBER
452 ,p_alias_def_comma OUT NOCOPY VARCHAR2);
453 -- USE IN DEPOSIT WRAPPER UTILITIES
454 FUNCTION get_list_alias_id(p_alias_type IN VARCHAR2
455 ,p_start_time IN VARCHAR2
456 ,p_stop_time IN VARCHAR2
457 ,p_resource_id IN NUMBER) RETURN t_alias_def_item;
458
459 FUNCTION get_bld_blk_type_id(p_type IN varchar2) RETURN NUMBER;
460
461 /*
462 Function convert_attribute_to_type
463 (p_attributes in HXC_SELF_SERVICE_TIME_DEPOSIT.building_block_attribute_info)
467 (p_blocks in HXC_SELF_SERVICE_TIME_DEPOSIT.timecard_info)
464 return HXC_ATTRIBUTE_TABLE_TYPE;
465
466 Function convert_timecard_to_type
468 return HXC_BLOCK_TABLE_TYPE;
469 */
470 -- ----------------------------------------------------------------------------
471 -- |---------------------------< get_translated_detail >--------------------|
472 -- ----------------------------------------------------------------------------
473
474 -- use in the extract
475 PROCEDURE get_translated_detail (p_detail_bb_id in NUMBER,
476 p_detail_bb_ovn in NUMBER,
477 p_resource_id in NUMBER,
478 p_attributes OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE,
479 p_messages IN OUT NOCOPY HXC_MESSAGE_TABLE_TYPE );
480
481
482 END HXC_ALIAS_UTILITY;