[Home] [Help]
PACKAGE BODY: APPS.HXC_DEPOSIT_WRAPPER_UTILITIES
Source
1 PACKAGE BODY hxc_deposit_wrapper_utilities AS
2 /* $Header: hxcdpwrut.pkb 120.17 2010/08/19 10:50:20 bbayragi ship $ */
3
4
5 g_separator VARCHAR2(2) := '|';
6 g_pref_sep VARCHAR2(2) := '#';
7 g_package VARCHAR2(50) := 'hxc_deposit_wrapper_utilities';
8
9 g_debug boolean :=hr_utility.debug_enabled;
10
11 -- globals for caching the hours type poplist
12 g_ht_resource_id varchar2(20) :=null;
13 g_ht_start_time varchar2(30) :=null;
14 g_ht_stop_time varchar2(30) :=null;
15 g_ht_alias_or_element_id varchar2(30) :=null;
16 g_ht_resp_id number := null;
17 g_hours_type_list varchar2(15000) :=null;
18 g_ht_time date := sysdate;
19 --
20
21
22 G_TS_PER_APPROVAL_STYLE1 hxc_pref_hierarchies.attribute1%type := 'null';
23 G_TS_PER_APPROVAL_STYLE2 hxc_pref_hierarchies.attribute1%type := 'null';
24 G_TC_W_TCRD_ST_ALW_EDITS1 hxc_pref_hierarchies.attribute1%type := 'null';
25 G_TC_W_TCRD_LAYOUT1 hxc_pref_hierarchies.attribute1%type := 'null';
26 G_TC_W_TCRD_LAYOUT2 hxc_pref_hierarchies.attribute1%type := 'null';
27 G_TC_W_TCRD_LAYOUT3 hxc_pref_hierarchies.attribute1%type := 'null';
28 G_TC_W_TCRD_NUM_EMTY_RWS1 hxc_pref_hierarchies.attribute1%type := 'null';
29 G_TC_W_TMPLT_APND_ON_TCRD1 hxc_pref_hierarchies.attribute1%type := 'null';
30 G_TC_W_TMPLT_CREATE1 hxc_pref_hierarchies.attribute1%type := 'null';
31 G_TC_W_TMPLT_DFLT_VAL_ADMIN1 hxc_pref_hierarchies.attribute1%type := 'null';
32 G_TC_W_TMPLT_DFLT_VAL_USR1 hxc_pref_hierarchies.attribute1%type := 'null';
33 G_TC_W_TMPLT_SV_ON_TCRD1 hxc_pref_hierarchies.attribute1%type := 'null';
34 G_TC_W_TMPLT_FCNLTY1 hxc_pref_hierarchies.attribute1%type := 'null';
35 G_TC_W_NUM_RCNT_TCRDS1 hxc_pref_hierarchies.attribute1%type := 'null';
36 G_TC_W_DISCNCTD_ENTRY1 hxc_pref_hierarchies.attribute1%type := 'null';
37 G_TC_W_ALW_NEG_TIME1 hxc_pref_hierarchies.attribute1%type := 'null';
38 G_TC_W_TCRD_UOM1 hxc_pref_hierarchies.attribute1%type := 'null';
39 G_TC_W_TCRD_UOM2 hxc_pref_hierarchies.attribute1%type := 'null';
40 G_TC_W_TCRD_ST_ALW_EDITS6 hxc_pref_hierarchies.attribute1%type := 'null';
41 G_TC_W_TCRD_ST_ALW_EDITS11 hxc_pref_hierarchies.attribute1%type := 'null';
42 G_TC_W_TCRD_LAYOUT4 hxc_pref_hierarchies.attribute1%type := 'null';
43 G_TC_W_APRVR_ENBLE_OVRD1 hxc_pref_hierarchies.attribute1%type := 'null';
44 G_TC_W_APRVR_DFLT_OVRD1 hxc_pref_hierarchies.attribute1%type := 'null';
45 G_TC_W_TCRD_LAYOUT5 hxc_pref_hierarchies.attribute1%type := 'null';
46 G_TC_W_RULES_EVALUATION1 hxc_pref_hierarchies.attribute1%type := 'null';
47
48
49 PROCEDURE initialize_globals is
50
51 BEGIN
52
53 G_TS_PER_APPROVAL_STYLE1 := 'null';
54 G_TS_PER_APPROVAL_STYLE2 := 'null';
55 G_TC_W_TCRD_ST_ALW_EDITS1 := 'null';
56 G_TC_W_TCRD_LAYOUT1 := 'null';
57 G_TC_W_TCRD_LAYOUT2 := 'null';
58 G_TC_W_TCRD_LAYOUT3 := 'null';
59 G_TC_W_TCRD_NUM_EMTY_RWS1 := 'null';
60 G_TC_W_TMPLT_APND_ON_TCRD1 := 'null';
61 G_TC_W_TMPLT_CREATE1 := 'null';
62 G_TC_W_TMPLT_DFLT_VAL_ADMIN1 := 'null';
63 G_TC_W_TMPLT_DFLT_VAL_USR1 := 'null';
64 G_TC_W_TMPLT_SV_ON_TCRD1 := 'null';
65 G_TC_W_TMPLT_FCNLTY1 := 'null';
66 G_TC_W_NUM_RCNT_TCRDS1 := 'null';
67 G_TC_W_DISCNCTD_ENTRY1 := 'null';
68 G_TC_W_ALW_NEG_TIME1 := 'null';
69 G_TC_W_TCRD_UOM1 := 'null';
70 G_TC_W_TCRD_UOM2 := 'null';
71 G_TC_W_TCRD_ST_ALW_EDITS6 := 'null';
72 G_TC_W_TCRD_ST_ALW_EDITS11 := 'null';
73 G_TC_W_TCRD_LAYOUT4 := 'null';
74 G_TC_W_APRVR_ENBLE_OVRD1 := 'null';
75 G_TC_W_APRVR_DFLT_OVRD1 := 'null';
76 G_TC_W_TCRD_LAYOUT5 := 'null';
77 G_TC_W_RULES_EVALUATION1 := 'null';
78
79 END initialize_globals;
80
81 FUNCTION splat_preferences return varchar2 is
82
83 BEGIN
84
85 return
86 G_TS_PER_APPROVAL_STYLE1||g_pref_sep||
87 G_TS_PER_APPROVAL_STYLE2||g_pref_sep||
88 G_TC_W_TCRD_ST_ALW_EDITS1||g_pref_sep||
89 G_TC_W_TCRD_LAYOUT1||g_pref_sep||
90 G_TC_W_TCRD_LAYOUT2||g_pref_sep||
91 G_TC_W_TCRD_LAYOUT3||g_pref_sep||
92 G_TC_W_TCRD_NUM_EMTY_RWS1||g_pref_sep||
93 G_TC_W_TMPLT_APND_ON_TCRD1||g_pref_sep||
94 G_TC_W_TMPLT_CREATE1||g_pref_sep||
95 G_TC_W_TMPLT_DFLT_VAL_ADMIN1||g_pref_sep||
96 G_TC_W_TMPLT_DFLT_VAL_USR1||g_pref_sep||
97 G_TC_W_TMPLT_SV_ON_TCRD1||g_pref_sep||
98 G_TC_W_TMPLT_FCNLTY1||g_pref_sep||
99 G_TC_W_NUM_RCNT_TCRDS1||g_pref_sep||
100 G_TC_W_DISCNCTD_ENTRY1||g_pref_sep||
101 G_TC_W_ALW_NEG_TIME1||g_pref_sep||
102 G_TC_W_TCRD_UOM1||g_pref_sep||
103 G_TC_W_TCRD_UOM2||g_pref_sep||
104 G_TC_W_TCRD_ST_ALW_EDITS6||g_pref_sep||
105 G_TC_W_TCRD_ST_ALW_EDITS11||g_pref_sep||
106 G_TC_W_TCRD_LAYOUT4||g_pref_sep||
107 G_TC_W_APRVR_ENBLE_OVRD1||g_pref_sep||
108 G_TC_W_APRVR_DFLT_OVRD1||g_pref_sep||
109 G_TC_W_TCRD_LAYOUT5||g_pref_sep||
110 G_TC_W_RULES_EVALUATION1;
111
112 END splat_preferences;
113
114 PROCEDURE set_pref_globals
115 (p_prefs in HXC_PREFERENCE_EVALUATION.T_PREF_TABLE
116 ) is
117
118 i NUMBER;
119 l_proc VARCHAR2(30) := 'SET_PREF_GLOBALS';
120
121 BEGIN
122
123 initialize_globals;
124
125 i := p_prefs.first;
126
127 LOOP
128
129 EXIT WHEN NOT p_prefs.exists(i);
130
131 if(p_prefs(i).preference_code='TS_PER_APPROVAL_STYLE') then
132 if(G_TS_PER_APPROVAL_STYLE1 = 'null') then
133 G_TS_PER_APPROVAL_STYLE1 := nvl(p_prefs(i).attribute1,'null');
134 end if;
135 if(G_TS_PER_APPROVAL_STYLE2='null') then
136 G_TS_PER_APPROVAL_STYLE2 := nvl(p_prefs(i).attribute2,'null');
137 end if;
138 end if;
139 if(p_prefs(i).preference_code='TC_W_TCRD_ST_ALW_EDITS') then
140 if(G_TC_W_TCRD_ST_ALW_EDITS1 ='null') then
141 G_TC_W_TCRD_ST_ALW_EDITS1 := nvl(p_prefs(i).attribute1,'null');
142 end if;
143 end if;
144 if(p_prefs(i).preference_code='TC_W_TCRD_LAYOUT') then
145 if(G_TC_W_TCRD_LAYOUT1 ='null') then
146 G_TC_W_TCRD_LAYOUT1 := nvl(p_prefs(i).attribute1,'null');
147 end if;
148 if(G_TC_W_TCRD_LAYOUT2 ='null') then
149 G_TC_W_TCRD_LAYOUT2 := nvl(p_prefs(i).attribute2,'null');
150 end if;
151 if(G_TC_W_TCRD_LAYOUT3 ='null') then
152 G_TC_W_TCRD_LAYOUT3 := nvl(p_prefs(i).attribute3,'null');
153 end if;
154 if(G_TC_W_TCRD_LAYOUT4 ='null') then
155 G_TC_W_TCRD_LAYOUT4 := nvl(p_prefs(i).attribute4,'null');
156 end if;
157 if(G_TC_W_TCRD_LAYOUT5 ='null') then
158 G_TC_W_TCRD_LAYOUT5 := nvl(p_prefs(i).attribute5,'null');
159 end if;
160 end if;
161 if(p_prefs(i).preference_code='TC_W_TCRD_NUM_EMTY_RWS') then
162 if(G_TC_W_TCRD_NUM_EMTY_RWS1 ='null') then
163 G_TC_W_TCRD_NUM_EMTY_RWS1 := nvl(p_prefs(i).attribute1,'null');
164 end if;
165 end if;
166 if(p_prefs(i).preference_code='TC_W_TMPLT_APND_ON_TCRD') then
167 if(G_TC_W_TMPLT_APND_ON_TCRD1 ='null') then
168 G_TC_W_TMPLT_APND_ON_TCRD1 := nvl(p_prefs(i).attribute1,'null');
169 end if;
170 end if;
171 if(p_prefs(i).preference_code='TC_W_TMPLT_CREATE') then
172 if(G_TC_W_TMPLT_CREATE1 ='null') then
173 G_TC_W_TMPLT_CREATE1 := nvl(p_prefs(i).attribute1,'null');
174 end if;
175 end if;
176 if(p_prefs(i).preference_code='TC_W_TMPLT_DFLT_VAL_ADMIN') then
177 if(G_TC_W_TMPLT_DFLT_VAL_ADMIN1 ='null') then
178 G_TC_W_TMPLT_DFLT_VAL_ADMIN1 := nvl(p_prefs(i).attribute1,'null');
179 end if;
180 end if;
181 if(p_prefs(i).preference_code='TC_W_TMPLT_DFLT_VAL_USR') then
182 if(G_TC_W_TMPLT_DFLT_VAL_USR1 ='null') then
183 G_TC_W_TMPLT_DFLT_VAL_USR1 := nvl(p_prefs(i).attribute1,'null');
184 end if;
185 end if;
186 if(p_prefs(i).preference_code='TC_W_TMPLT_SV_ON_TCRD') then
187 if(G_TC_W_TMPLT_SV_ON_TCRD1 ='null') then
188 G_TC_W_TMPLT_SV_ON_TCRD1 := nvl(p_prefs(i).attribute1,'null');
189 end if;
190 end if;
191 if(p_prefs(i).preference_code='TC_W_TMPLT_FCNLTY') then
192 if(G_TC_W_TMPLT_FCNLTY1 ='null') then
193 G_TC_W_TMPLT_FCNLTY1 := nvl(p_prefs(i).attribute1,'null');
194 end if;
195 end if;
196 if(p_prefs(i).preference_code='TC_W_NUM_RCNT_TCRDS') then
197 if(G_TC_W_NUM_RCNT_TCRDS1 ='null') then
198 G_TC_W_NUM_RCNT_TCRDS1 := nvl(p_prefs(i).attribute1,'null');
199 end if;
200 end if;
201 if(p_prefs(i).preference_code='TC_W_DISCNCTD_ENTRY') then
202 if(G_TC_W_DISCNCTD_ENTRY1 ='null') then
203 G_TC_W_DISCNCTD_ENTRY1 := nvl(p_prefs(i).attribute1,'null');
204 end if;
205 end if;
206 if(p_prefs(i).preference_code='TC_W_ALW_NEG_TIME') then
207 if(G_TC_W_ALW_NEG_TIME1 ='null') then
208 G_TC_W_ALW_NEG_TIME1 := nvl(p_prefs(i).attribute1,'null');
209 end if;
210 end if;
211 if(p_prefs(i).preference_code='TC_W_TCRD_UOM') then
212 if(G_TC_W_TCRD_UOM1 ='null') then
213 G_TC_W_TCRD_UOM1 := nvl(p_prefs(i).attribute1,'null');
214 end if;
215 if(G_TC_W_TCRD_UOM2 ='null') then
216 G_TC_W_TCRD_UOM2 := nvl(p_prefs(i).attribute2,'null');
217 end if;
218 end if;
219 if(p_prefs(i).preference_code='TC_W_TCRD_ST_ALW_EDITS') then
220 if(G_TC_W_TCRD_ST_ALW_EDITS6 ='null') then
221 G_TC_W_TCRD_ST_ALW_EDITS6 := nvl(p_prefs(i).attribute6,'null');
222 end if;
223 if(G_TC_W_TCRD_ST_ALW_EDITS11 ='null') then
224 G_TC_W_TCRD_ST_ALW_EDITS11 := nvl(p_prefs(i).attribute11,'null');
225 end if;
226 end if;
227 if(p_prefs(i).preference_code='TC_W_APRVR_ENBLE_OVRD') then
228 if(G_TC_W_APRVR_ENBLE_OVRD1 ='null') then
229 G_TC_W_APRVR_ENBLE_OVRD1 := nvl(p_prefs(i).attribute1,'null');
230 end if;
231 end if;
232 if(p_prefs(i).preference_code='TC_W_APRVR_DFLT_OVRD') then
233 if(G_TC_W_APRVR_DFLT_OVRD1 ='null') then
234 G_TC_W_APRVR_DFLT_OVRD1 := nvl(p_prefs(i).attribute1,'null');
235 end if;
236 end if;
237 if(p_prefs(i).preference_code='TC_W_RULES_EVALUATION') then
238 if(G_TC_W_RULES_EVALUATION1 ='null') then
239 G_TC_W_RULES_EVALUATION1 := nvl(p_prefs(i).attribute1,'null');
240 end if;
241 end if;
242
243 i := p_prefs.next(i);
244
245 END LOOP;
246
247 END set_pref_globals;
248
249 FUNCTION fetch_context_name
250 (p_context_code in FND_DESCR_FLEX_CONTEXTS_VL.DESCRIPTIVE_FLEX_CONTEXT_CODE%TYPE)
251 return varchar2 is
252
253 l_context_name FND_DESCR_FLEX_CONTEXTS_VL.DESCRIPTIVE_FLEX_CONTEXT_NAME%TYPE :='UNSET';
254
255 begin
256
257 -- Perf Rep Fix - SQL ID:3170183
258 -- Added where clause application_id = 809
259
260 select descriptive_flex_context_name into l_context_name
261 from fnd_descr_flex_contexts_vl
262 where descriptive_flexfield_name = 'OTC Information Types'
263 and descriptive_flex_context_code = p_context_code
264 and application_id = 809;
265
266 return l_context_name;
267
268 end fetch_context_name;
269
270 FUNCTION no_values_left
271 (p_string IN VARCHAR2
272 ,p_index IN NUMBER)
273 RETURN BOOLEAN IS
274
275 BEGIN
276
277 IF INSTR(p_string,g_separator,1,(p_index+1)) >0 THEN
278 RETURN FALSE;
279 ELSE
280 RETURN TRUE;
281 END IF;
282
283 END no_values_left;
284
285 FUNCTION add_value_to_string
286 (p_string IN VARCHAR2
287 ,p_value IN varchar2)
288 RETURN VARCHAR2 IS
289 BEGIN
290
291 IF p_value IS NULL OR p_value = ''
292 THEN
293 RETURN p_string||g_separator|| 'null';
294 END IF;
295
296 RETURN p_string||g_separator||p_value;
297
298 END add_value_to_string;
299
300 FUNCTION get_first
301 (p_string IN VARCHAR2
302 ) RETURN VARCHAR2 IS
303
304 BEGIN
305
306 RETURN SUBSTR(p_string,2,(INSTR(p_string,g_separator,1,2)-2));
307
308 END get_first;
309
310 FUNCTION get_value_from_string
311 (p_string IN VARCHAR2
312 ,p_value_index IN NUMBER
313 ) RETURN VARCHAR2 IS
314
315 l_value HXC_TIME_BUILDING_BLOCKS.COMMENT_TEXT%TYPE;
316 l_proc VARCHAR2(30) := 'GET_VALUE_FROM_STRING';
317 BEGIN
318
319 IF (INSTR(p_string,g_separator,1,p_value_index+1) = 0) THEN
320 --
321 -- We need to send back the very last thing in the string, i.e.
322 -- everything from the final g_separator.
323 --
324 l_value := SUBSTR(p_string,(INSTR(p_string,g_separator,1,p_value_index)+1));
325 ELSE
326
327
328 l_value := SUBSTR(p_string
329 ,(INSTR(p_string,g_separator,1,p_value_index)+1)
330 ,((INSTR(p_string,g_separator,1,(p_value_index+1))-1)
331 -INSTR(p_string,g_separator,1,p_value_index))
332 );
333 END IF;
334
335 IF l_value = 'null'
336 THEN
337 l_value := NULL;
338 END IF;
339
340 RETURN l_value;
341
342 END get_value_from_string;
343
344 FUNCTION check_global_context
345 (p_context_prefix in VARCHAR2) return boolean is
346
347 l_dummy VARCHAR2(10);
348
349 BEGIN
350
351 select 'Y' into l_dummy
352 from fnd_descr_flex_contexts
353 where application_id = 809
354 and descriptive_flexfield_name = 'OTC Information Types'
355 and enabled_flag = 'Y'
356 and descriptive_flex_context_code like p_context_prefix||'%GLOBAL%';
357
358 return true;
359
360 EXCEPTION
361 WHEN no_data_found then
362
363 return false;
364
365 END;
366
367 FUNCTION return_projects_context
368 (p_system_linkage in varchar2
369 ,p_expenditure_type in varchar2) return varchar2 is
370
371 cursor c_reference_field is
372 select d.default_context_field_name
373 from fnd_descriptive_flexs d
374 ,fnd_application a
375 ,fnd_product_installations z
376 where d.application_id = a.application_id
377 and z.application_id = a.application_id
378 and a.application_short_name = 'PA'
379 and z.status = 'I'
380 and d.descriptive_flexfield_name = 'PA_EXPENDITURE_ITEMS_DESC_FLEX';
381
382 l_reference_field FND_DESCRIPTIVE_FLEXS.DEFAULT_CONTEXT_FIELD_NAME%TYPE;
383 l_pa_context_code varchar2(100) := '';
384 l_pa_context_name FND_DESCR_FLEX_CONTEXTS_VL.DESCRIPTIVE_FLEX_CONTEXT_NAME%TYPE := '';
385
386 l_pa_desc_flex_info varchar2(240) := '';
387
388 BEGIN
389
390 open c_reference_field;
391 fetch c_reference_field into l_reference_field;
392
393 if c_reference_field%NOTFOUND then
394
395 l_pa_desc_flex_info := '';
396
397 else
398
399 if (l_reference_field = 'SYSTEM_LINKAGE_FUNCTION') then
400
401 if (p_system_linkage <> 'NO_PROJECTS') then
402
403 -- Construct the context in the OTL Information type
404
405 -- l_pa_context_code := 'PAEXPITDFF - '||p_system_linkage;
406 /* If the length of the system linkage is greater than 17, say for Painting-Decorating,
407 then the context name is PAEXPITDFF - Painting-Decorating and corresponding code
408 PAEXPITDFFC - 1221233 is obtained by using the function get_paexpitdff_code
409 Else the usual process is followed */
410
411 l_pa_context_code :=
412 HXC_DEPOSIT_WRAPPER_UTILITIES.get_dupdff_code('PAEXPITDFF - ' || p_system_linkage);
413
414
415 -- Get the corresponding context name
416
417 l_pa_context_name := fetch_context_name(l_pa_context_code);
418
419 end if;
420
421 elsif (l_reference_field = 'EXPENDITURE_TYPE') then
422
423 if (p_expenditure_type <> 'NO_PROJECTS') then
424
425 -- l_pa_context_code := 'PAEXPITDFF - '||p_expenditure_type;
426 l_pa_context_code :=
427 HXC_DEPOSIT_WRAPPER_UTILITIES.get_dupdff_code('PAEXPITDFF - ' || p_expenditure_type);
428
429 l_pa_context_name := fetch_context_name(l_pa_context_code);
430
431 end if;
432
433 elsif ((l_reference_field = '') OR (l_reference_field is NULL)) then
434
435 if (check_global_context('PAEXPITDFF')) then
436
437 l_pa_context_code := 'PAEXPITDFF - GLOBAL';
438 l_pa_context_name := 'PAEXPITDFF - GLOBAL';
439
440 end if;
441
442 end if;
443
444 l_pa_desc_flex_info := l_pa_context_code || g_separator || l_pa_context_name;
445
446 end if;
447
448 close c_reference_field;
449
450 return l_pa_desc_flex_info;
451
452 END return_projects_context;
453
454 Procedure find_pa_information_from_alias
455 (p_alias_value_id in hxc_alias_values.alias_value_id%type,
456 p_expenditure_type out nocopy hxc_time_attributes.attribute3%type,
457 p_system_linkage_function out nocopy hxc_time_attributes.attribute5%type
458 ) is
459
460 cursor c_segments(p_value_id in hxc_alias_values.alias_value_id%type) is
461 select atc.component_name,
462 fdfcu.application_column_name
463 from fnd_descr_flex_column_usages fdfcu,
464 hxc_alias_type_components atc,
465 hxc_alias_types aty,
466 hxc_alias_definitions ad,
467 hxc_alias_values av
468 where av.alias_value_id = p_value_id
469 and av.alias_definition_id = ad.alias_definition_id
470 and ad.alias_type_id = aty.alias_type_id
471 and aty.alias_type_id = atc.alias_type_id
472 and atc.component_name in ('EXPENDITURE_TYPE','SYSTEM_LINKAGE_FUNCTION')
473 and atc.component_name = fdfcu.end_user_column_name
474 and fdfcu.application_id = 809
475 and fdfcu.descriptive_flexfield_name = 'OTC Aliases'
476 and fdfcu.descriptive_flex_context_code = aty.reference_object
477 and aty.alias_type = 'OTL_ALT_DDF';
478
479 cursor c_value_row(p_value_id in hxc_alias_values.alias_value_id%type) is
480 select *
481 from hxc_alias_values av
482 where av.alias_value_id = p_value_id;
483
484 l_alias_value_row c_value_row%rowtype;
485 l_row_value hxc_alias_values.attribute1%type;
486
487 Begin
488 /*
489 We do this in something of strange way to avaid dynamic SQL,
490 and to avaid hard coding the context in the query to look
491 up the values. Perhaps the dynamic SQL would be better?
492 */
493 p_expenditure_type := null;
494 p_system_linkage_function := null;
495
496 open c_value_row(p_alias_value_id);
497 fetch c_value_row into l_alias_value_row;
498 if(c_value_row%found) then
499 close c_value_row;
500 for seg_rec in c_segments(p_alias_value_id) loop
501 l_row_value := '';
502 if(seg_rec.application_column_name='ATTRIBUTE1') then
503 l_row_value := l_alias_value_row.attribute1;
504 elsif(seg_rec.application_column_name='ATTRIBUTE2') then
505 l_row_value := l_alias_value_row.attribute2;
506 elsif(seg_rec.application_column_name='ATTRIBUTE3') then
507 l_row_value := l_alias_value_row.attribute3;
508 elsif(seg_rec.application_column_name='ATTRIBUTE4') then
509 l_row_value := l_alias_value_row.attribute4;
510 elsif(seg_rec.application_column_name='ATTRIBUTE5') then
511 l_row_value := l_alias_value_row.attribute5;
512 elsif(seg_rec.application_column_name='ATTRIBUTE6') then
513 l_row_value := l_alias_value_row.attribute6;
514 elsif(seg_rec.application_column_name='ATTRIBUTE7') then
515 l_row_value := l_alias_value_row.attribute7;
516 elsif(seg_rec.application_column_name='ATTRIBUTE8') then
517 l_row_value := l_alias_value_row.attribute8;
518 elsif(seg_rec.application_column_name='ATTRIBUTE9') then
519 l_row_value := l_alias_value_row.attribute9;
520 elsif(seg_rec.application_column_name='ATTRIBUTE10') then
521 l_row_value := l_alias_value_row.attribute10;
522 elsif(seg_rec.application_column_name='ATTRIBUTE11') then
523 l_row_value := l_alias_value_row.attribute11;
524 elsif(seg_rec.application_column_name='ATTRIBUTE12') then
525 l_row_value := l_alias_value_row.attribute12;
526 elsif(seg_rec.application_column_name='ATTRIBUTE13') then
527 l_row_value := l_alias_value_row.attribute13;
528 elsif(seg_rec.application_column_name='ATTRIBUTE14') then
529 l_row_value := l_alias_value_row.attribute14;
530 elsif(seg_rec.application_column_name='ATTRIBUTE15') then
531 l_row_value := l_alias_value_row.attribute15;
532 elsif(seg_rec.application_column_name='ATTRIBUTE16') then
533 l_row_value := l_alias_value_row.attribute16;
534 elsif(seg_rec.application_column_name='ATTRIBUTE17') then
535 l_row_value := l_alias_value_row.attribute17;
536 elsif(seg_rec.application_column_name='ATTRIBUTE18') then
537 l_row_value := l_alias_value_row.attribute18;
538 elsif(seg_rec.application_column_name='ATTRIBUTE19') then
539 l_row_value := l_alias_value_row.attribute19;
540 elsif(seg_rec.application_column_name='ATTRIBUTE20') then
541 l_row_value := l_alias_value_row.attribute20;
542 elsif(seg_rec.application_column_name='ATTRIBUTE21') then
543 l_row_value := l_alias_value_row.attribute21;
544 elsif(seg_rec.application_column_name='ATTRIBUTE22') then
545 l_row_value := l_alias_value_row.attribute22;
546 elsif(seg_rec.application_column_name='ATTRIBUTE23') then
547 l_row_value := l_alias_value_row.attribute23;
548 elsif(seg_rec.application_column_name='ATTRIBUTE24') then
549 l_row_value := l_alias_value_row.attribute24;
550 elsif(seg_rec.application_column_name='ATTRIBUTE25') then
551 l_row_value := l_alias_value_row.attribute25;
552 elsif(seg_rec.application_column_name='ATTRIBUTE26') then
553 l_row_value := l_alias_value_row.attribute26;
554 elsif(seg_rec.application_column_name='ATTRIBUTE27') then
555 l_row_value := l_alias_value_row.attribute27;
556 elsif(seg_rec.application_column_name='ATTRIBUTE28') then
557 l_row_value := l_alias_value_row.attribute28;
558 elsif(seg_rec.application_column_name='ATTRIBUTE29') then
559 l_row_value := l_alias_value_row.attribute29;
560 elsif(seg_rec.application_column_name='ATTRIBUTE30') then
561 l_row_value := l_alias_value_row.attribute30;
562 end if;
563 if(seg_rec.component_name = 'EXPENDITURE_TYPE') then
564 p_expenditure_type := l_row_value;
565 elsif(seg_rec.component_name = 'SYSTEM_LINKAGE_FUNCTION') then
566 p_system_linkage_function := l_row_value;
567 end if;
568 end loop;
569 else
570 close c_value_row;
571 end if;
572
573 End find_pa_information_from_alias;
574
575 PROCEDURE build_context_string
576 (p_context_codes in varchar2
577 ,p_system_linkage in varchar2
578 ,p_expenditure_type in varchar2
579 ,p_pa_alias_value_id in varchar2
580 ,p_context_string out nocopy varchar2
581 ) is
582
583 l_index number := 1;
584 l_context_code FND_DESCR_FLEX_CONTEXTS_VL.DESCRIPTIVE_FLEX_CONTEXT_CODE%TYPE;
585 l_context_name FND_DESCR_FLEX_CONTEXTS_VL.DESCRIPTIVE_FLEX_CONTEXT_NAME%TYPE;
586 l_pa_info VARCHAR2(240) := '';
587 l_expenditure_type HXC_TIME_ATTRIBUTES.ATTRIBUTE3%TYPE;
588 l_system_linkage_function HXC_TIME_ATTRIBUTES.ATTRIBUTE5%TYPE;
589
590 begin
591
592 p_context_string := '';
593
594 if(length(p_context_codes) > 1) then
595
596 loop
597
598 l_context_code := get_value_from_string(p_context_codes,l_index);
599 l_context_name := fetch_context_name(get_value_from_string(p_context_codes,l_index));
600
601 if (length(p_context_string)>0) then
602
603 p_context_string := p_context_string || g_separator
604 || get_value_from_string(p_context_codes,l_index)
605 || g_separator
606 || fetch_context_name(get_value_from_string(p_context_codes,l_index));
607
608 else
609
610 p_context_string := p_context_string
611 || get_value_from_string(p_context_codes,l_index)
612 || g_separator
613 || fetch_context_name(get_value_from_string(p_context_codes,l_index));
614 end if;
615
616
617 exit when no_values_left(p_context_codes, l_index);
618
619 l_index := l_index + 1;
620
621 end loop;
622 end if;
623
624 if(p_pa_alias_value_id <> 'NO_PROJECTS') then
625 Begin
626 find_pa_information_from_alias
627 (to_number(p_pa_alias_value_id)
628 ,l_expenditure_type
629 ,l_system_linkage_function
630 );
631 -- Will apply for ELP layouts.
632 if((l_expenditure_type is null) and (l_system_linkage_function is null)) then
633 l_system_linkage_function := p_system_linkage;
634 l_expenditure_type := p_expenditure_type;
635 end if;
636 Exception
637 When Others then
638 l_expenditure_type := '';
639 l_system_linkage_function := '';
640 End;
641 else
642 -- Projects Layout.
643 l_system_linkage_function := p_system_linkage;
644 l_expenditure_type := p_expenditure_type;
645 end if;
646
647 l_pa_info := return_projects_context(l_system_linkage_function, l_expenditure_type);
648
649 if (length(l_pa_info) > 0) then
650
651 if (length(p_context_string) > 0) then
652
653 p_context_string := p_context_string || g_separator || l_pa_info;
654
655 else
656
657 p_context_string := l_pa_info;
658
659 end if;
660
661 end if;
662
663 exception
664 when NO_DATA_FOUND then
665
666 FND_MESSAGE.set_name('HXC','HXC_INVALID_INFO_CONTEXT');
667 FND_MESSAGE.set_token('CONTEXT',p_context_codes);
668
669
670 END build_context_string;
671
672
673 PROCEDURE get_person_information
674 (p_resource_id in number
675 ,p_date in date
676 ,p_person_info in out nocopy varchar2) is
677
678 cursor c_person_info
679 (p_person_id in number
680 ,p_d in date) is
681 select papf.full_name
682 ,paa.assignment_number
683 ,to_char(papf.original_date_of_hire,'YYYY/MM/DD')
684 ,paa.assignment_id
685 from per_all_people_f papf
686 ,per_all_assignments_f paa
687 where paa.person_id = papf.person_id
688 and p_d between paa.effective_start_date and paa.effective_end_date
689 and p_d between papf.effective_start_date and papf.effective_end_date
690 and paa.primary_flag = 'Y'
691 and paa.assignment_type = 'E'
692 and papf.person_id = p_person_id;
693
694 cursor closest_asg_record
695 (p_person_id in number
696 ,p_d in date) is
697 select assignment_number
698 ,assignment_id
699 ,effective_start_date
700 ,min(abs(effective_start_date-p_d))
701 from per_all_assignments_f
702 where person_id = p_person_id
703 and primary_flag = 'Y'
704 and assignment_type = 'E'
705 group by assignment_number, assignment_id, effective_start_date;
706
707 cursor closest_per_record
708 (p_person_id in number
709 ,p_d in date) is
710 select full_name,to_char(original_date_of_hire,'YYYY/MM/DD')
711 from per_all_people_f
712 where person_id = p_person_id
713 and effective_start_date <= p_d
714 and effective_end_date >= p_d;
715
716
717 l_full_name PER_ALL_PEOPLE_F.FULL_NAME%TYPE;
718 l_assignment_number PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_NUMBER%TYPE;
719 l_assignment_id PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID%TYPE;
720 l_hire_date VARCHAR2(30);
721 l_asg_start_date DATE;
722 l_min NUMBER;
723
724 BEGIN
725
726 open c_person_info(p_resource_id,p_date);
727 fetch c_person_info into l_full_name, l_assignment_number, l_hire_date,l_assignment_id;
728
729 if (c_person_info%NOTFOUND) then
730
731 -- ok, check for any person records, they might
732 -- begin outside this timecard period start date
733
734 open closest_asg_record(p_resource_id,p_date);
735 fetch closest_asg_record into l_assignment_number, l_assignment_id, l_asg_start_date, l_min;
736
737 if closest_asg_record%NOTFOUND then
738
739 close c_person_info;
740 close closest_asg_record;
741
742 FND_MESSAGE.set_name('HXC','HXC_NO_PERSON_INFO');
743 FND_MESSAGE.raise_error;
744
745 end if;
746
747 close closest_asg_record;
748
749 -- Here we assume that to have an assignment, you MUST
750 -- have a person record!
751
752 open closest_per_record(p_resource_id, l_asg_start_date);
753 fetch closest_per_record into l_full_name, l_hire_date;
754 close closest_per_record;
755
756 end if;
757
758 close c_person_info;
759
760 p_person_info := p_person_info || l_full_name || g_pref_sep || l_assignment_number || g_pref_sep || l_hire_date || g_pref_sep || l_assignment_id;
761
762 end get_person_information;
763
764 PROCEDURE get_period_information
765 (p_resource_id in number
766 ,p_period_returns out nocopy varchar2) is
767
768 cursor c_period_info
769 (p_recurring_period_id in HXC_RECURRING_PERIODS.RECURRING_PERIOD_ID%TYPE) is
770 select rp.period_type
771 ,rp.duration_in_days
772 ,p.number_per_fiscal_year
773 ,substr(fnd_date.date_to_canonical(rp.start_date),1,50) start_date
774 from hxc_recurring_periods rp
775 ,per_time_period_types p
776 where p.period_type (+) = rp.period_type
777 and rp.recurring_period_id = p_recurring_period_id;
778
779 l_period_type PER_TIME_PERIOD_TYPES.period_type%TYPE;
780 l_duration_in_days HXC_RECURRING_PERIODS.DURATION_IN_DAYS%TYPE;
781 l_number_per_fiscal_year PER_TIME_PERIOD_TYPES.NUMBER_PER_FISCAL_YEAR%TYPE;
782 l_start_date VARCHAR2(50);
783 l_recurring_period_id HXC_RECURRING_PERIODS.RECURRING_PERIOD_ID%TYPE;
784
785 l_v_duration_in_days VARCHAR2(2000);
786 l_v_number_per_fiscal_year VARCHAR2(2000);
787 l_v_period_type VARCHAR2(2000);
788
789 BEGIN
790
791 -- First obtain the preference for the recurring period id
792
793 l_recurring_period_id := hxc_preference_evaluation.resource_preferences(p_resource_id,'TC_W_TCRD_PERIOD|1|');
794
795 open c_period_info(l_recurring_period_id);
796 fetch c_period_info into l_period_type, l_duration_in_days, l_number_per_fiscal_year, l_start_date;
797
798 l_v_duration_in_days := NVL(to_char(l_duration_in_days),'null');
799 l_v_number_per_fiscal_year := NVL(to_char(l_number_per_fiscal_year),'null');
800 l_v_period_type := NVL(l_period_type,'null');
801
802
803 if (c_period_info%NOTFOUND) then
804
805 FND_MESSAGE.set_name('HXC','HXC_MISSING_PERIOD_PREF');
806 FND_MESSAGE.raise_error;
807
808 end if;
809
810 close c_period_info;
811
812 p_period_returns := l_v_period_type || g_pref_sep || l_v_duration_in_days || g_pref_sep || l_v_number_per_fiscal_year || g_pref_sep || l_start_date;
813
814 END get_period_information;
815
816 PROCEDURE check_pref_dates_against_asg
817 (p_resource_id in number
818 ,p_start_date in out nocopy date
819 ,p_end_date in out nocopy date
820 ) IS
821
822 cursor c_start_date
823 (p_person_id in NUMBER) is
824 SELECT min(paa.EFFECTIVE_START_DATE)
825 FROM PER_ALL_ASSIGNMENTS_F paa,
826 per_assignment_status_types typ
827 WHERE paa.PERSON_ID = p_person_id
828 AND paa.ASSIGNMENT_TYPE = 'E'
829 AND paa.PRIMARY_FLAG = 'Y'
830 AND paa.ASSIGNMENT_STATUS_TYPE_ID = typ.ASSIGNMENT_STATUS_TYPE_ID
831 AND typ.PER_SYSTEM_STATUS IN ('ACTIVE_ASSIGN','ACTIVE_CWK');
832
833 cursor c_end_date
834 (p_person_id in NUMBER) is
835 SELECT max(paa.EFFECTIVE_END_DATE)
836 FROM PER_ALL_ASSIGNMENTS_F paa,
837 per_assignment_status_types typ
838 WHERE paa.PERSON_ID = p_person_id
839 AND paa.ASSIGNMENT_TYPE = 'E'
840 AND paa.PRIMARY_FLAG = 'Y'
841 AND paa.ASSIGNMENT_STATUS_TYPE_ID = typ.ASSIGNMENT_STATUS_TYPE_ID
842 AND typ.PER_SYSTEM_STATUS IN ('ACTIVE_ASSIGN','ACTIVE_CWK');
843
844
845 l_start_date DATE;
846 l_end_date DATE;
847
848 BEGIN
849
850 open c_start_date(p_resource_id);
851 fetch c_start_date into l_start_date;
852
853 if c_start_date%NOTFOUND then
854 close c_start_date;
855 FND_MESSAGE.set_name('HXC','HXC_NO_ACTIVE_ASG');
856 FND_MESSAGE.raise_error;
857 end if;
858
859 close c_start_date;
860
861 open c_end_date(p_resource_id);
862 fetch c_end_date into l_end_date;
863
864 if c_end_date%NOTFOUND then
865 close c_end_date;
866 FND_MESSAGE.set_name('HXC','HXC_NO_ACTIVE_ASG');
867 FND_MESSAGE.raise_error;
868 end if;
869
870 close c_end_date;
871
872 --added by jxtan
873 IF SYSDATE > l_end_date
874 THEN
875
876 p_start_date := l_start_date;
877 p_end_date := l_end_date;
878
879 RETURN;
880 END IF;
881
882
883 if(p_start_date < l_start_date) then
884 p_start_date := l_start_date;
885 end if;
886
887 if(p_end_date > l_end_date) then
888 p_end_date := l_end_date;
889 end if;
890
891 END check_pref_dates_against_asg;
892
893 PROCEDURE get_preferences
894 (p_resource_id in number
895 ,p_preference_string in varchar2
896 ,p_include_pp in varchar2
897 ,p_preference_date in varchar2
898 ,p_preference_end_date in varchar2
899 ,p_timecard_id in number
900 ,p_preference_returns out nocopy varchar2
901 ) IS
902
903 l_pref_date DATE;
904 l_pref_end_date DATE := null;
905
906 cursor c_start_time(p_id in number) is
907 select start_time,stop_time
908 from hxc_time_building_blocks
909 where time_building_block_id = p_id
910 and date_to = hr_general.end_of_time;
911
912 l_pref_table HXC_PREFERENCE_EVALUATION.T_PREF_TABLE;
913
914 BEGIN
915
916 if (instr(p_preference_date,'/') > 0) then
917 l_pref_date := to_date(p_preference_date,'YYYY/MM/DD');
918 elsif (p_timecard_id > 0) then
919 open c_start_time(p_timecard_id);
920 fetch c_start_time into l_pref_date, l_pref_end_date;
921 close c_start_time;
922 else
923 l_pref_date := sysdate;
924 end if;
925
926 if(l_pref_end_date is null) then
927
928 if (instr(p_preference_end_date,'/') > 0) then
929 l_pref_end_date := to_date(p_preference_end_date,'YYYY/MM/DD');
930 else
931 l_pref_end_date := sysdate;
932 end if;
933
934 end if;
935
936
937 if (p_include_pp = 'Y') then
938
939 get_period_information(p_resource_id, p_preference_returns);
940
941 p_preference_returns := p_preference_returns || g_pref_sep;
942
943 end if;
944
945 -- call the preference package to get all the
946 -- preferences associated with a resource
947
948 -- check the dates, so that we only ask for the
949 -- preference information within the range of
950 -- an active assignment for the resource
951
952 check_pref_dates_against_asg
953 (p_resource_id => p_resource_id
954 ,p_start_date => l_pref_date
955 ,p_end_date => l_pref_end_date
956 );
957
958 hxc_preference_evaluation.resource_preferences
959 (p_resource_id => p_resource_id
960 ,p_start_evaluation_date => l_pref_date
961 ,p_end_evaluation_date => l_pref_end_date
962 ,p_pref_table => l_pref_table
963 );
964
965 -- Ok, next set the global preference variables
966 -- in this package
967
968 set_pref_globals(l_pref_table);
969
970 -- And splat them together!
971
972 p_preference_returns := p_preference_returns || splat_preferences;
973
974 -- Now set the preferences appropriately
975
976 if (p_include_pp = 'Y') then
977
978 p_preference_returns := p_preference_returns || g_pref_sep;
979 get_person_information(p_resource_id, l_pref_date, p_preference_returns);
980
981 end if;
982
983 END get_preferences;
984
985
986 FUNCTION blocks_to_string
987 (p_blocks IN hxc_self_service_time_deposit.timecard_info)
988 RETURN VARCHAR2 IS
989
990 l_block_count NUMBER := 0;
991 l_block_string VARCHAR2(32767) := '';
992 l_proc VARCHAR2(30) := 'BLOCKS_TO_STRING';
993
994 BEGIN
995
996 l_block_count := p_blocks.first;
997
998 LOOP
999
1000 EXIT WHEN NOT p_blocks.exists(l_block_count);
1001
1002 --
1003 -- OK, need to check to see if this is a real
1004 -- building block that we need to send to the recipient
1005 -- application.
1006 --
1007
1008 IF(
1009 (
1010 (p_blocks(l_block_count).type = 'RANGE')
1011 AND
1012 (p_blocks(l_block_count).start_time IS NOT NULL)
1013 )
1014 OR
1015 (
1016 (p_blocks(l_block_count).type = 'MEASURE')
1017 AND
1018 (p_blocks(l_block_count).measure IS NOT null)
1019 )
1020 ) THEN
1021
1022 l_block_string := add_value_to_string
1023 (l_block_string
1024 ,p_blocks(l_block_count).time_building_block_id
1025 );
1026
1027 l_block_string := add_value_to_string
1028 (l_block_string
1029 ,p_blocks(l_block_count).TYPE
1030 );
1031 l_block_string := add_value_to_string
1032 (l_block_string
1033 ,p_blocks(l_block_count).MEASURE
1034 );
1035 l_block_string := add_value_to_string
1036 (l_block_string
1037 ,p_blocks(l_block_count).UNIT_OF_MEASURE
1038 );
1039 l_block_string := add_value_to_string
1040 (l_block_string
1041 ,FND_DATE.DATE_TO_CANONICAL(p_blocks(l_block_count).START_TIME)
1042 );
1043 l_block_string := add_value_to_string
1044 (l_block_string
1045 ,FND_DATE.DATE_TO_CANONICAL(p_blocks(l_block_count).STOP_TIME)
1046 );
1047 l_block_string := add_value_to_string
1048 (l_block_string
1049 ,p_blocks(l_block_count).PARENT_BUILDING_BLOCK_ID
1050 );
1051 l_block_string := add_value_to_string
1052 (l_block_string
1053 ,p_blocks(l_block_count).PARENT_IS_NEW
1054 );
1055 l_block_string := add_value_to_string
1056 (l_block_string
1057 ,p_blocks(l_block_count).SCOPE
1058 );
1059 l_block_string := add_value_to_string
1060 (l_block_string
1061 ,p_blocks(l_block_count).OBJECT_VERSION_NUMBER
1062 );
1063 l_block_string := add_value_to_string
1064 (l_block_string
1065 ,p_blocks(l_block_count).APPROVAL_STATUS
1066 );
1067 l_block_string := add_value_to_string
1068 (l_block_string
1069 ,p_blocks(l_block_count).RESOURCE_ID
1070 );
1071 l_block_string := add_value_to_string
1072 (l_block_string
1073 ,p_blocks(l_block_count).RESOURCE_TYPE
1074 );
1075 l_block_string := add_value_to_string
1076 (l_block_string
1077 ,p_blocks(l_block_count).APPROVAL_STYLE_ID
1078 );
1079 l_block_string := add_value_to_string
1080 (l_block_string
1081 ,FND_DATE.DATE_TO_CANONICAL(p_blocks(l_block_count).DATE_FROM)
1082 );
1083 l_block_string := add_value_to_string
1084 (l_block_string
1085 ,FND_DATE.DATE_TO_CANONICAL(p_blocks(l_block_count).DATE_TO)
1086 );
1087 l_block_string := add_value_to_string
1088 (l_block_string
1089 ,p_blocks(l_block_count).COMMENT_TEXT
1090 );
1091 l_block_string := add_value_to_string
1092 (l_block_string
1093 ,p_blocks(l_block_count).PARENT_BUILDING_BLOCK_OVN
1094 );
1095
1096 l_block_string := add_value_to_string
1097 (l_block_string
1098 ,p_blocks(l_block_count).NEW
1099 );
1100
1101 END IF;
1102
1103 l_block_count := p_blocks.next(l_block_count);
1104
1105 END LOOP;
1106
1107 RETURN l_block_string;
1108
1109 END blocks_to_string;
1110
1111 FUNCTION string_to_blocks
1112 (p_block_string IN varchar2)
1113 RETURN hxc_self_service_time_deposit.timecard_info IS
1114
1115 l_blocks hxc_self_service_time_deposit.timecard_info;
1116 l_blocks2 hxc_self_service_time_deposit.timecard_info;
1117 l_block_count NUMBER :=1;
1118 l_value_index NUMBER :=1;
1119 l_proc VARCHAR2(30) := 'STRING_TO_BLOCKS';
1120
1121 l_bad BOOLEAN :=FALSE;
1122
1123 l_count NUMBER;
1124 l_position NUMBER;
1125 l_result hxc_time_building_blocks.comment_text%TYPE;
1126 l_block_table t_simple_table;
1127
1128 BEGIN
1129
1130 -- new build ...
1131
1132 l_position:=0;
1133 l_block_count:=0;
1134
1135 string_to_table(g_separator,p_block_string,l_block_table);
1136
1137 for l_count in 0..l_block_table.count-1 loop
1138
1139 l_result:=l_block_table(l_count);
1140
1141 if(l_position=0) then
1142 l_blocks2(l_block_count).time_building_block_id := l_result;
1143 elsif(l_position=1) then
1144 l_blocks2(l_block_count).type := l_result;
1145 elsif(l_position=2) then
1146 l_blocks2(l_block_count).measure := l_result;
1147 elsif(l_position=3) then
1148 l_blocks2(l_block_count).unit_of_measure := l_result;
1149 elsif(l_position=4) then
1150 l_blocks2(l_block_count).start_time := FND_DATE.CANONICAL_TO_DATE(l_result);
1151 elsif(l_position=5) then
1152 l_blocks2(l_block_count).stop_time := FND_DATE.CANONICAL_TO_DATE(l_result);
1153 elsif(l_position=6) then
1154 l_blocks2(l_block_count).parent_building_block_id := l_result;
1155 elsif(l_position=7) then
1156 l_blocks2(l_block_count).parent_is_new := l_result;
1157 elsif(l_position=8) then
1158 l_blocks2(l_block_count).scope := l_result;
1159 elsif(l_position=9) then
1160 l_blocks2(l_block_count).object_version_number := l_result;
1161 elsif(l_position=10) then
1162 l_blocks2(l_block_count).approval_status := l_result;
1163 elsif(l_position=11) then
1164 l_blocks2(l_block_count).resource_id := l_result;
1165 elsif(l_position=12) then
1166 l_blocks2(l_block_count).resource_type := l_result;
1167 elsif(l_position=13) then
1168 l_blocks2(l_block_count).approval_style_id := l_result;
1169 elsif(l_position=14) then
1170 l_blocks2(l_block_count).date_from := FND_DATE.CANONICAL_TO_DATE(l_result);
1171 elsif(l_position=15) then
1172 l_blocks2(l_block_count).date_to := FND_DATE.CANONICAL_TO_DATE(l_result);
1173 elsif(l_position=16) then
1174 l_blocks2(l_block_count).comment_text := l_result;
1175 elsif(l_position=17) then
1176 l_blocks2(l_block_count).parent_building_block_ovn := l_result;
1177 elsif(l_position=18) then
1178 l_blocks2(l_block_count).new := l_result;
1179 l_position := -1;
1180 l_block_count := l_block_count+1;
1181 end if;
1182
1183 l_position:=l_position+1;
1184
1185 end loop;
1186
1187 /*
1188 l_block_count:=0;
1189
1190 LOOP
1191
1192 EXIT WHEN no_values_left(p_block_string,l_value_index);
1193
1194 IF l_value_index = 0 THEN
1195
1196 l_blocks(l_block_count).time_building_block_id
1197 := get_first
1198 (p_string => p_block_string);
1199 l_value_index := l_value_index +1;
1200 ELSE
1201 l_blocks(l_block_count).time_building_block_id
1202 := get_value_from_string
1203 (p_string => p_block_string
1204 ,p_value_index => l_value_index);
1205 l_value_index := l_value_index +1;
1206 END IF;
1207
1208 l_blocks(l_block_count).TYPE
1209 := get_value_from_string
1210 (p_string => p_block_string
1211 ,p_value_index => l_value_index);
1212 l_value_index := l_value_index +1;
1213
1214 l_blocks(l_block_count).MEASURE
1215 := get_value_from_string
1216 (p_string => p_block_string
1217 ,p_value_index => l_value_index);
1218 l_value_index := l_value_index +1;
1219
1220 l_blocks(l_block_count).UNIT_OF_MEASURE
1221 := get_value_from_string
1222 (p_string => p_block_string
1223 ,p_value_index => l_value_index);
1224 l_value_index := l_value_index +1;
1225 l_blocks(l_block_count).START_TIME
1226 := FND_DATE.CANONICAL_TO_DATE(
1227 get_value_from_string
1228 (p_string => p_block_string
1229 ,p_value_index => l_value_index));
1230 l_value_index := l_value_index +1;
1231 l_blocks(l_block_count).STOP_TIME
1232 := FND_DATE.CANONICAL_TO_DATE(
1233 get_value_from_string
1234 (p_string => p_block_string
1235 ,p_value_index => l_value_index));
1236 l_value_index := l_value_index +1;
1237 l_blocks(l_block_count).PARENT_BUILDING_BLOCK_ID
1238 := get_value_from_string
1239 (p_string => p_block_string
1240 ,p_value_index => l_value_index);
1241 l_value_index := l_value_index +1;
1242 l_blocks(l_block_count).PARENT_IS_NEW
1243 := get_value_from_string
1244 (p_string => p_block_string
1245 ,p_value_index => l_value_index);
1246 l_value_index := l_value_index +1;
1247 l_blocks(l_block_count).SCOPE
1248 := get_value_from_string
1249 (p_string => p_block_string
1250 ,p_value_index => l_value_index);
1251 l_value_index := l_value_index +1;
1252 l_blocks(l_block_count).OBJECT_VERSION_NUMBER
1253 := get_value_from_string
1254 (p_string => p_block_string
1255 ,p_value_index => l_value_index);
1256 l_value_index := l_value_index +1;
1257 l_blocks(l_block_count).APPROVAL_STATUS
1258 := get_value_from_string
1259 (p_string => p_block_string
1260 ,p_value_index => l_value_index);
1261 l_value_index := l_value_index +1;
1262 l_blocks(l_block_count).RESOURCE_ID
1263 := get_value_from_string
1264 (p_string => p_block_string
1265 ,p_value_index => l_value_index);
1266 l_value_index := l_value_index +1;
1267 l_blocks(l_block_count).RESOURCE_TYPE
1268 := get_value_from_string
1269 (p_string => p_block_string
1270 ,p_value_index => l_value_index);
1271 l_value_index := l_value_index +1;
1272 l_blocks(l_block_count).APPROVAL_STYLE_ID
1273 := get_value_from_string
1274 (p_string => p_block_string
1275 ,p_value_index => l_value_index);
1276 l_value_index := l_value_index +1;
1277 l_blocks(l_block_count).DATE_FROM
1278 := FND_DATE.CANONICAL_TO_DATE(
1279 get_value_from_string
1280 (p_string => p_block_string
1281 ,p_value_index => l_value_index));
1282 l_value_index := l_value_index +1;
1283 l_blocks(l_block_count).DATE_TO
1284 := FND_DATE.CANONICAL_TO_DATE(
1285 get_value_from_string
1286 (p_string => p_block_string
1287 ,p_value_index => l_value_index));
1288 l_value_index := l_value_index +1;
1289 l_blocks(l_block_count).COMMENT_TEXT
1290 := get_value_from_string
1291 (p_string => p_block_string
1292 ,p_value_index => l_value_index);
1293 l_value_index := l_value_index +1;
1294 l_blocks(l_block_count).PARENT_BUILDING_BLOCK_OVN
1295 := get_value_from_string
1296 (p_string => p_block_string
1297 ,p_value_index => l_value_index);
1298 l_value_index := l_value_index +1;
1299
1300 l_blocks(l_block_count).NEW
1301 := get_value_from_string
1302 (p_string => p_block_string
1303 ,p_value_index => l_value_index);
1304 l_value_index := l_value_index +1;
1305
1306 l_block_count := l_block_count+1;
1307
1308 END LOOP;
1309
1310 -- now do comparison
1311
1312 for i in 0..l_blocks2.count-1 LOOP
1313
1314 if(l_blocks(i).time_building_block_id<>l_blocks2(i).time_building_block_id) then l_bad:=TRUE; end if;
1315 if(l_blocks(i).type<>l_blocks2(i).type) then l_bad:=TRUE; end if;
1316
1317 end loop;
1318
1319 if(l_bad=TRUE) then
1320 hr_utility.set_message(809,'HXC_S2B_DO_NOT_MATCH');
1321 hr_utility.raise_error;
1322 end if;
1323
1324 */
1325 RETURN l_blocks2;
1326
1327 END string_to_blocks;
1328
1329 FUNCTION attributes_to_string
1330 (p_attributes IN hxc_self_service_time_deposit.app_attributes_info)
1331 RETURN VARCHAR2 IS
1332
1333 l_attribute_string VARCHAR2(32767);
1334 l_attribute_count NUMBER;
1335
1336 BEGIN
1337
1338 l_attribute_count := p_attributes.first;
1339
1340 LOOP
1341
1342 EXIT WHEN NOT p_attributes.exists(l_attribute_count);
1343
1344 l_attribute_string := add_value_to_string
1345 (l_attribute_string
1346 ,p_attributes(l_attribute_count).time_attribute_id
1347 );
1348 l_attribute_string := add_value_to_string
1349 (l_attribute_string
1350 ,p_attributes(l_attribute_count).Building_Block_Id
1351 );
1352 l_attribute_string := add_value_to_string
1353 (l_attribute_string
1354 ,p_attributes(l_attribute_count).Attribute_Name
1355 );
1356 l_attribute_string := add_value_to_string
1357 (l_attribute_string
1358 ,p_attributes(l_attribute_count).Attribute_Value
1359 );
1360 l_attribute_string := add_value_to_string
1361 (l_attribute_string
1362 ,p_attributes(l_attribute_count).Bld_Blk_Info_Type
1363 );
1364 l_attribute_string := add_value_to_string
1365 (l_attribute_string
1366 ,p_attributes(l_attribute_count).Category
1367 );
1368
1369 l_attribute_count := p_attributes.next(l_attribute_count);
1370
1371 END LOOP;
1372
1373 RETURN l_attribute_string;
1374
1375 END attributes_to_string;
1376
1377 FUNCTION string_to_attributes
1378 (p_attribute_string IN varchar2)
1379 RETURN hxc_self_service_time_deposit.app_attributes_info IS
1380
1381 l_attributes hxc_self_service_time_deposit.app_attributes_info;
1382 l_attributes2 hxc_self_service_time_deposit.app_attributes_info;
1383 l_value_index NUMBER :=1;
1384 l_attribute_count NUMBER := 0;
1385 l_bad BOOLEAN :=FALSE;
1386
1387 l_count NUMBER;
1388 l_position NUMBER;
1389 l_result hxc_time_building_blocks.comment_text%TYPE;
1390 l_attr_table t_simple_table;
1391
1392 BEGIN
1393
1394 -- leave comparision build using get_value_from_string until tested further
1395
1396 l_position := 0;
1397 l_attribute_count := 0;
1398
1399 string_to_table(g_separator,p_attribute_string,l_attr_table);
1400
1401 for l_count in 0..l_attr_table.count-1 loop
1402
1403 l_result := l_attr_table(l_count);
1404
1405 if(l_position=0) then
1406 l_attributes2(l_attribute_count).time_attribute_id := l_result;
1407 elsif(l_position=1) then
1408 l_attributes2(l_attribute_count).building_block_id := l_result;
1409 elsif(l_position=2) then
1410 l_attributes2(l_attribute_count).attribute_name := l_result;
1411 elsif(l_position=3) then
1412 l_attributes2(l_attribute_count).attribute_value := l_result;
1413 elsif(l_position=4) then
1414 l_attributes2(l_attribute_count).bld_blk_info_type := l_result;
1415 elsif(l_position=5) then
1416 l_attributes2(l_attribute_count).category := l_result;
1417 l_position := -1;
1418 l_attribute_count := l_attribute_count+1;
1419 end if;
1420
1421 l_position:=l_position+1;
1422
1423 end loop;
1424
1425 -- now do traditional build ...
1426 /*
1427 l_attribute_count:=0;
1428 LOOP
1429
1430 EXIT WHEN no_values_left(p_attribute_string,l_value_index);
1431
1432 IF l_value_index = 0 THEN
1433 l_attributes(l_attribute_count).time_attribute_id
1434 := get_first
1435 (p_string => p_attribute_string);
1436 l_value_index := l_value_index +1;
1437 ELSE
1438 l_attributes(l_attribute_count).time_attribute_id
1439 := get_value_from_string
1440 (p_string => p_attribute_string
1441 ,p_value_index => l_value_index);
1442 l_value_index := l_value_index +1;
1443 END IF;
1444 l_attributes(l_attribute_count).building_block_id
1445 := get_value_from_string
1446 (p_string => p_attribute_string
1447 ,p_value_index => l_value_index);
1448 l_value_index := l_value_index +1;
1449
1450 l_attributes(l_attribute_count).Attribute_Name
1451 := get_value_from_string
1452 (p_string => p_attribute_string
1453 ,p_value_index => l_value_index);
1454 l_value_index := l_value_index +1;
1455 l_attributes(l_attribute_count).Attribute_Value
1456 := get_value_from_string
1457 (p_string => p_attribute_string
1458 ,p_value_index => l_value_index);
1459 l_value_index := l_value_index +1;
1460 l_attributes(l_attribute_count).Bld_Blk_Info_Type
1461 := get_value_from_string
1462 (p_string => p_attribute_string
1463 ,p_value_index => l_value_index);
1464 l_value_index := l_value_index +1;
1465 l_attributes(l_attribute_count).Category
1466 := get_value_from_string
1467 (p_string => p_attribute_string
1468 ,p_value_index => l_value_index);
1469 l_value_index := l_value_index +1;
1470
1471 l_attribute_count := l_attribute_count +1;
1472
1473 END LOOP;
1474
1475 -- now do comparison
1476
1477 for i in 0..l_attributes2.count-1 LOOP
1478
1479 if(l_attributes(i).time_attribute_id<>l_attributes2(i).time_attribute_id) then l_bad:=TRUE; end if;
1480 if(l_attributes(i).building_block_id<>l_attributes2(i).building_block_id) then l_bad:=TRUE; end if;
1481 if(l_attributes(i).attribute_name<>l_attributes2(i).attribute_name) then l_bad:=TRUE; end if;
1482 if(l_attributes(i).attribute_value<>l_attributes2(i).attribute_value) then l_bad:=TRUE; end if;
1483 if(l_attributes(i).bld_blk_info_type<>l_attributes2(i).bld_blk_info_type) then l_bad:=TRUE; end if;
1484 if(l_attributes(i).category<>l_attributes2(i).category) then l_bad:=TRUE; end if;
1485
1486 end loop;
1487
1488 if(l_bad=TRUE) then
1489 hr_utility.set_message(809,'HXC_S2A_DO_NOT_MATCH');
1490 hr_utility.raise_error;
1491 end if;
1492 */
1493 RETURN l_attributes2;
1494
1495 END string_to_attributes;
1496
1497 FUNCTION string_to_bld_blk_attributes
1498 (p_attribute_string IN varchar2)
1499 RETURN hxc_self_service_time_deposit.building_block_attribute_info IS
1500
1501 l_attributes hxc_self_service_time_deposit.building_block_attribute_info;
1502 l_attributes2 hxc_self_service_time_deposit.building_block_attribute_info;
1503 l_value_index NUMBER :=1;
1504 l_attribute_count NUMBER := 0;
1505
1506
1507
1508
1509 l_bad BOOLEAN :=FALSE;
1510
1511 l_count NUMBER;
1512 l_position NUMBER;
1513 l_result hxc_time_building_blocks.comment_text%TYPE;
1514 l_attr_table t_simple_table;
1515
1516 BEGIN
1517
1518 -- new build ...
1519 -- leave comparision build using get_value_from_string until tested further
1520
1521 l_position:=0;
1522 l_attribute_count:=0;
1523
1524 string_to_table(g_separator,p_attribute_string,l_attr_table);
1525
1526 for l_count in 0..l_attr_table.count-1 loop
1527
1528 l_result:=l_attr_table(l_count);
1529
1530 if(l_position=0) then
1531 l_attributes2(l_attribute_count).time_attribute_id := l_result;
1532 elsif(l_position=1) then
1533 l_attributes2(l_attribute_count).building_block_id := l_result;
1534 elsif(l_position=2) then
1535 l_attributes2(l_attribute_count).Attribute_Category:= l_result;
1536 elsif(l_position=3) then
1537 l_attributes2(l_attribute_count).attribute1 := l_result;
1538 elsif(l_position=4) then
1539 l_attributes2(l_attribute_count).attribute2 := l_result;
1540 elsif(l_position=5) then
1541 l_attributes2(l_attribute_count).attribute3 := l_result;
1542 elsif(l_position=6) then
1543 l_attributes2(l_attribute_count).attribute4 := l_result;
1544 elsif(l_position=7) then
1545 l_attributes2(l_attribute_count).attribute5 := l_result;
1546 elsif(l_position=8) then
1547 l_attributes2(l_attribute_count).attribute6 := l_result;
1548 elsif(l_position=9) then
1549 l_attributes2(l_attribute_count).attribute7 := l_result;
1550 elsif(l_position=10) then
1551 l_attributes2(l_attribute_count).attribute8 := l_result;
1552 elsif(l_position=11) then
1553 l_attributes2(l_attribute_count).attribute9 := l_result;
1554 elsif(l_position=12) then
1555 l_attributes2(l_attribute_count).attribute10 := l_result;
1556 elsif(l_position=13) then
1557 l_attributes2(l_attribute_count).attribute11 := l_result;
1558 elsif(l_position=14) then
1559 l_attributes2(l_attribute_count).attribute12 := l_result;
1560 elsif(l_position=15) then
1561 l_attributes2(l_attribute_count).attribute13 := l_result;
1562 elsif(l_position=16) then
1563 l_attributes2(l_attribute_count).attribute14 := l_result;
1564 elsif(l_position=17) then
1565 l_attributes2(l_attribute_count).attribute15 := l_result;
1566 elsif(l_position=18) then
1567 l_attributes2(l_attribute_count).attribute16 := l_result;
1568 elsif(l_position=19) then
1569 l_attributes2(l_attribute_count).attribute17 := l_result;
1570 elsif(l_position=20) then
1571 l_attributes2(l_attribute_count).attribute18 := l_result;
1572 elsif(l_position=21) then
1573 l_attributes2(l_attribute_count).attribute19 := l_result;
1574 elsif(l_position=22) then
1575 l_attributes2(l_attribute_count).attribute20 := l_result;
1576 elsif(l_position=23) then
1577 l_attributes2(l_attribute_count).attribute21 := l_result;
1578 elsif(l_position=24) then
1579 l_attributes2(l_attribute_count).attribute22 := l_result;
1580 elsif(l_position=25) then
1581 l_attributes2(l_attribute_count).attribute23 := l_result;
1582 elsif(l_position=26) then
1583 l_attributes2(l_attribute_count).attribute24 := l_result;
1584 elsif(l_position=27) then
1585 l_attributes2(l_attribute_count).attribute25 := l_result;
1586 elsif(l_position=28) then
1587 l_attributes2(l_attribute_count).attribute26 := l_result;
1588 elsif(l_position=29) then
1589 l_attributes2(l_attribute_count).attribute27 := l_result;
1590 elsif(l_position=30) then
1591 l_attributes2(l_attribute_count).attribute28 := l_result;
1592 elsif(l_position=31) then
1593 l_attributes2(l_attribute_count).attribute29 := l_result;
1594 elsif(l_position=32) then
1595 l_attributes2(l_attribute_count).attribute30 := l_result;
1596 elsif(l_position=33) then
1597 l_attributes2(l_attribute_count).Bld_Blk_Info_Type_Id := l_result;
1598 elsif(l_position=34) then
1599 l_attributes2(l_attribute_count).Object_Version_Number := l_result;
1600 elsif(l_position=35) then
1601 l_attributes2(l_attribute_count).new := l_result;
1602 elsif(l_position=36) then
1603 l_attributes2(l_attribute_count).changed := l_result;
1604 elsif(l_position=37) then
1605 l_attributes2(l_attribute_count).bld_blk_info_type := l_result;
1606 l_position := -1;
1607 l_attribute_count := l_attribute_count+1;
1608 end if;
1609
1610 l_position:=l_position+1;
1611
1612 end loop;
1613
1614
1615 -- old build
1616 /*
1617
1618 l_attribute_count:=0;
1619
1620 LOOP
1621
1622 EXIT WHEN no_values_left(p_attribute_string,l_value_index);
1623
1624 IF l_value_index = 0 THEN
1625 l_attributes(l_attribute_count).time_attribute_id
1626 := get_first
1627 (p_string => p_attribute_string);
1628 l_value_index := l_value_index +1;
1629 ELSE
1630 l_attributes(l_attribute_count).time_attribute_id
1631 := get_value_from_string
1632 (p_string => p_attribute_string
1633 ,p_value_index => l_value_index);
1634 l_value_index := l_value_index +1;
1635 END IF;
1636 l_attributes(l_attribute_count).building_block_id
1637 := get_value_from_string
1638 (p_string => p_attribute_string
1639 ,p_value_index => l_value_index);
1640 l_value_index := l_value_index +1;
1641 l_attributes(l_attribute_count).Attribute_Category
1642 := get_value_from_string
1643 (p_string => p_attribute_string
1644 ,p_value_index => l_value_index);
1645 l_value_index := l_value_index +1;
1646 l_attributes(l_attribute_count).Attribute1
1647 := get_value_from_string
1648 (p_string => p_attribute_string
1649 ,p_value_index => l_value_index);
1650 l_value_index := l_value_index +1;
1651 l_attributes(l_attribute_count).Attribute2
1652 := get_value_from_string
1653 (p_string => p_attribute_string
1654 ,p_value_index => l_value_index);
1655 l_value_index := l_value_index +1;
1656 l_attributes(l_attribute_count).Attribute3
1657 := get_value_from_string
1658 (p_string => p_attribute_string
1659 ,p_value_index => l_value_index);
1660 l_value_index := l_value_index +1;
1661 l_attributes(l_attribute_count).Attribute4
1662 := get_value_from_string
1663 (p_string => p_attribute_string
1664 ,p_value_index => l_value_index);
1665 l_value_index := l_value_index +1;
1666 l_attributes(l_attribute_count).Attribute5
1667 := get_value_from_string
1668 (p_string => p_attribute_string
1669 ,p_value_index => l_value_index);
1670 l_value_index := l_value_index +1;
1671 l_attributes(l_attribute_count).Attribute6
1672 := get_value_from_string
1673 (p_string => p_attribute_string
1674 ,p_value_index => l_value_index);
1675 l_value_index := l_value_index +1;
1676 l_attributes(l_attribute_count).Attribute7
1677 := get_value_from_string
1678 (p_string => p_attribute_string
1679 ,p_value_index => l_value_index);
1680 l_value_index := l_value_index +1;
1681 l_attributes(l_attribute_count).Attribute8
1682 := get_value_from_string
1683 (p_string => p_attribute_string
1684 ,p_value_index => l_value_index);
1685 l_value_index := l_value_index +1;
1686 l_attributes(l_attribute_count).Attribute9
1687 := get_value_from_string
1688 (p_string => p_attribute_string
1689 ,p_value_index => l_value_index);
1690 l_value_index := l_value_index +1;
1691 l_attributes(l_attribute_count).Attribute10
1692 := get_value_from_string
1693 (p_string => p_attribute_string
1694 ,p_value_index => l_value_index);
1695 l_value_index := l_value_index +1;
1696 l_attributes(l_attribute_count).Attribute11
1697 := get_value_from_string
1698 (p_string => p_attribute_string
1699 ,p_value_index => l_value_index);
1700 l_value_index := l_value_index +1;
1701 l_attributes(l_attribute_count).Attribute12
1702 := get_value_from_string
1703 (p_string => p_attribute_string
1704 ,p_value_index => l_value_index);
1705 l_value_index := l_value_index +1;
1706 l_attributes(l_attribute_count).Attribute13
1707 := get_value_from_string
1708 (p_string => p_attribute_string
1709 ,p_value_index => l_value_index);
1710 l_value_index := l_value_index +1;
1711 l_attributes(l_attribute_count).Attribute14
1712 := get_value_from_string
1713 (p_string => p_attribute_string
1714 ,p_value_index => l_value_index);
1715 l_value_index := l_value_index +1;
1716 l_attributes(l_attribute_count).Attribute15
1717 := get_value_from_string
1718 (p_string => p_attribute_string
1719 ,p_value_index => l_value_index);
1720 l_value_index := l_value_index +1;
1721 l_attributes(l_attribute_count).Attribute16
1722 := get_value_from_string
1723 (p_string => p_attribute_string
1724 ,p_value_index => l_value_index);
1725 l_value_index := l_value_index +1;
1726 l_attributes(l_attribute_count).Attribute17
1727 := get_value_from_string
1728 (p_string => p_attribute_string
1729 ,p_value_index => l_value_index);
1730 l_value_index := l_value_index +1;
1731 l_attributes(l_attribute_count).Attribute18
1732 := get_value_from_string
1733 (p_string => p_attribute_string
1734 ,p_value_index => l_value_index);
1735 l_value_index := l_value_index +1;
1736 l_attributes(l_attribute_count).Attribute19
1737 := get_value_from_string
1738 (p_string => p_attribute_string
1739 ,p_value_index => l_value_index);
1740 l_value_index := l_value_index +1;
1741 l_attributes(l_attribute_count).Attribute20
1742 := get_value_from_string
1743 (p_string => p_attribute_string
1744 ,p_value_index => l_value_index);
1745 l_value_index := l_value_index +1;
1746 l_attributes(l_attribute_count).Attribute21
1747 := get_value_from_string
1748 (p_string => p_attribute_string
1749 ,p_value_index => l_value_index);
1750 l_value_index := l_value_index +1;
1751 l_attributes(l_attribute_count).Attribute22
1752 := get_value_from_string
1753 (p_string => p_attribute_string
1754 ,p_value_index => l_value_index);
1755 l_value_index := l_value_index +1;
1756 l_attributes(l_attribute_count).Attribute23
1757 := get_value_from_string
1758 (p_string => p_attribute_string
1759 ,p_value_index => l_value_index);
1760 l_value_index := l_value_index +1;
1761 l_attributes(l_attribute_count).Attribute24
1762 := get_value_from_string
1763 (p_string => p_attribute_string
1764 ,p_value_index => l_value_index);
1765 l_value_index := l_value_index +1;
1766 l_attributes(l_attribute_count).Attribute25
1767 := get_value_from_string
1768 (p_string => p_attribute_string
1769 ,p_value_index => l_value_index);
1770 l_value_index := l_value_index +1;
1771 l_attributes(l_attribute_count).Attribute26
1772 := get_value_from_string
1773 (p_string => p_attribute_string
1774 ,p_value_index => l_value_index);
1775 l_value_index := l_value_index +1;
1776 l_attributes(l_attribute_count).Attribute27
1777 := get_value_from_string
1778 (p_string => p_attribute_string
1779 ,p_value_index => l_value_index);
1780 l_value_index := l_value_index +1;
1781 l_attributes(l_attribute_count).Attribute28
1782 := get_value_from_string
1783 (p_string => p_attribute_string
1784 ,p_value_index => l_value_index);
1785 l_value_index := l_value_index +1;
1786 l_attributes(l_attribute_count).Attribute29
1787 := get_value_from_string
1788 (p_string => p_attribute_string
1789 ,p_value_index => l_value_index);
1790 l_value_index := l_value_index +1;
1791 l_attributes(l_attribute_count).Attribute30
1792 := get_value_from_string
1793 (p_string => p_attribute_string
1794 ,p_value_index => l_value_index);
1795 l_value_index := l_value_index +1;
1796 l_attributes(l_attribute_count).Bld_Blk_Info_Type_Id
1797 := get_value_from_string
1798 (p_string => p_attribute_string
1799 ,p_value_index => l_value_index);
1800 l_value_index := l_value_index +1;
1801 l_attributes(l_attribute_count).Object_Version_Number
1802 := get_value_from_string
1803 (p_string => p_attribute_string
1804 ,p_value_index => l_value_index);
1805 l_value_index := l_value_index +1;
1806 l_attributes(l_attribute_count).New
1807 := get_value_from_string
1808 (p_string => p_attribute_string
1809 ,p_value_index => l_value_index);
1810 l_value_index := l_value_index +1;
1811 l_attributes(l_attribute_count).Changed
1812 := get_value_from_string
1813 (p_string => p_attribute_string
1814 ,p_value_index => l_value_index);
1815 l_value_index := l_value_index +1;
1816
1817 l_attribute_count := l_attribute_count +1;
1818
1819 END LOOP;
1820
1821 -- now do comparison
1822
1823 for i in 0..l_attributes2.count-1 LOOP
1824
1825 if(l_attributes(i).time_attribute_id<>l_attributes2(i).time_attribute_id) then l_bad:=TRUE; end if;
1826 if(l_attributes(i).building_block_id<>l_attributes2(i).building_block_id) then l_bad:=TRUE; end if;
1827 if(l_attributes(i).attribute_category<>l_attributes2(i).attribute_category) then l_bad:=TRUE; end if;
1828
1829 end loop;
1830
1831 if(l_bad=TRUE) then
1832 hr_utility.set_message(809,'HXC_S2BA_DO_NOT_MATCH');
1833 hr_utility.raise_error;
1834 end if;
1835 */
1836
1837
1838 RETURN l_attributes2;
1839
1840 END string_to_bld_blk_attributes;
1841
1842 FUNCTION messages_to_string
1843 (p_messages IN hxc_self_service_time_deposit.message_table)
1844 RETURN VARCHAR2 IS
1845
1846 l_message_string VARCHAR2(32767);
1847 l_message_count NUMBER;
1848
1849 BEGIN
1850
1851 l_message_count := p_messages.first;
1852
1853 LOOP
1854
1855 EXIT WHEN NOT p_messages.exists(l_message_count);
1856
1857 l_message_string := add_value_to_string
1858 (l_message_string
1859 ,p_messages(l_message_count).message_name
1860 );
1861
1862 l_message_string := add_value_to_string
1863 (l_message_string
1864 ,p_messages(l_message_count).MESSAGE_LEVEL
1865 );
1866 l_message_string := add_value_to_string
1867 (l_message_string
1868 ,p_messages(l_message_count).MESSAGE_FIELD
1869 );
1870 l_message_string := add_value_to_string
1871 (l_message_string
1872 ,p_messages(l_message_count).MESSAGE_TOKENS
1873 );
1874 l_message_string := add_value_to_string
1875 (l_message_string
1876 ,p_messages(l_message_count).APPLICATION_SHORT_NAME
1877 );
1878 l_message_string := add_value_to_string
1879 (l_message_string
1880 ,p_messages(l_message_count).TIME_BUILDING_BLOCK_ID
1881 );
1882 l_message_string := add_value_to_string
1883 (l_message_string
1884 ,p_messages(l_message_count).TIME_ATTRIBUTE_ID
1885 );
1886 l_message_count := p_messages.next(l_message_count);
1887
1888 END LOOP;
1889
1890 RETURN l_message_string;
1891
1892 END messages_to_string;
1893
1894 FUNCTION string_to_messages
1895 (p_message_string IN varchar2)
1896 RETURN hxc_self_service_time_deposit.message_table IS
1897
1898 l_messages hxc_self_service_time_deposit.message_table;
1899 l_value_index NUMBER :=1;
1900 l_message_count NUMBER := 0;
1901
1902 BEGIN
1903
1904 LOOP
1905
1906 EXIT WHEN no_values_left(p_message_string,l_value_index);
1907
1908 IF l_value_index = 0 THEN
1909 l_messages(l_message_count).message_name
1910 := get_first
1911 (p_string => p_message_string);
1912 l_value_index := l_value_index +1;
1913 ELSE
1914 l_messages(l_message_count).message_name
1915 := get_value_from_string
1916 (p_string => p_message_string
1917 ,p_value_index => l_value_index);
1918 l_value_index := l_value_index +1;
1919 END IF;
1920 l_messages(l_message_count).message_level
1921 := get_value_from_string
1922 (p_string => p_message_string
1923 ,p_value_index => l_value_index);
1924 l_value_index := l_value_index +1;
1925 l_messages(l_message_count).message_field
1926 := get_value_from_string
1927 (p_string => p_message_string
1928 ,p_value_index => l_value_index);
1929 l_value_index := l_value_index +1;
1930 l_messages(l_message_count).message_tokens
1931 := get_value_from_string
1932 (p_string => p_message_string
1933 ,p_value_index => l_value_index);
1934 l_value_index := l_value_index +1;
1935 l_messages(l_message_count).application_short_name
1936 := get_value_from_string
1937 (p_string => p_message_string
1938 ,p_value_index => l_value_index);
1939 l_value_index := l_value_index +1;
1940 l_messages(l_message_count).time_building_block_id
1941 := get_value_from_string
1942 (p_string => p_message_string
1943 ,p_value_index => l_value_index);
1944 l_value_index := l_value_index +1;
1945 l_messages(l_message_count).time_attribute_id
1946 := get_value_from_string
1947 (p_string => p_message_string
1948 ,p_value_index => l_value_index);
1949 l_value_index := l_value_index +1;
1950
1951 l_message_count := l_message_count +1;
1952
1953 END LOOP;
1954
1955 RETURN l_messages;
1956
1957 END string_to_messages;
1958
1959 FUNCTION attributes_to_string(
1960 p_attributes IN hxc_self_service_time_deposit.building_block_attribute_info
1961 )
1962 RETURN VARCHAR2
1963 IS
1964 l_attribute_string VARCHAR2(32767) := '';
1965 l_attribute_count NUMBER;
1966 BEGIN
1967
1968 l_attribute_count := p_attributes.first;
1969
1970 LOOP
1971
1972 EXIT WHEN NOT p_attributes.exists(l_attribute_count);
1973
1974
1975 l_attribute_string := add_value_to_string
1976 (l_attribute_string
1977 ,p_attributes(l_attribute_count).time_attribute_id
1978 );
1979
1980 l_attribute_string := add_value_to_string
1981 (l_attribute_string
1982 ,p_attributes(l_attribute_count).building_block_id
1983 );
1984
1985 l_attribute_string := add_value_to_string
1986 (l_attribute_string
1987 ,p_attributes(l_attribute_count).attribute_category
1988 );
1989 l_attribute_string := add_value_to_string
1990 (l_attribute_string
1991 ,p_attributes(l_attribute_count).attribute1
1992 );
1993 l_attribute_string := add_value_to_string
1994 (l_attribute_string
1995 ,p_attributes(l_attribute_count).attribute2
1996 );
1997 l_attribute_string := add_value_to_string
1998 (l_attribute_string
1999 ,p_attributes(l_attribute_count).attribute3
2000 );
2001 l_attribute_string := add_value_to_string
2002 (l_attribute_string
2003 ,p_attributes(l_attribute_count).attribute4
2004 );
2005 l_attribute_string := add_value_to_string
2006 (l_attribute_string
2007 ,p_attributes(l_attribute_count).attribute5
2008 );
2009 l_attribute_string := add_value_to_string
2010 (l_attribute_string
2011 ,p_attributes(l_attribute_count).attribute6
2012 );
2013 l_attribute_string := add_value_to_string
2014 (l_attribute_string
2015 ,p_attributes(l_attribute_count).attribute7
2016 );
2017 l_attribute_string := add_value_to_string
2018 (l_attribute_string
2019 ,p_attributes(l_attribute_count).attribute8
2020 );
2021 l_attribute_string := add_value_to_string
2022 (l_attribute_string
2023 ,p_attributes(l_attribute_count).attribute9
2024 );
2025 l_attribute_string := add_value_to_string
2026 (l_attribute_string
2027 ,p_attributes(l_attribute_count).attribute10
2028 );
2029 l_attribute_string := add_value_to_string
2030 (l_attribute_string
2031 ,p_attributes(l_attribute_count).attribute11
2032 );
2033 l_attribute_string := add_value_to_string
2034 (l_attribute_string
2035 ,p_attributes(l_attribute_count).attribute12
2036 );
2037 l_attribute_string := add_value_to_string
2038 (l_attribute_string
2039 ,p_attributes(l_attribute_count).attribute13
2040 );
2041 l_attribute_string := add_value_to_string
2042 (l_attribute_string
2043 ,p_attributes(l_attribute_count).attribute14
2044 );
2045 l_attribute_string := add_value_to_string
2046 (l_attribute_string
2047 ,p_attributes(l_attribute_count).attribute15
2048 );
2049 l_attribute_string := add_value_to_string
2050 (l_attribute_string
2051 ,p_attributes(l_attribute_count).attribute16
2052 );
2053 l_attribute_string := add_value_to_string
2054 (l_attribute_string
2055 ,p_attributes(l_attribute_count).attribute17
2056 );
2057 l_attribute_string := add_value_to_string
2058 (l_attribute_string
2059 ,p_attributes(l_attribute_count).attribute18
2060 );
2061 l_attribute_string := add_value_to_string
2062 (l_attribute_string
2063 ,p_attributes(l_attribute_count).attribute19
2064 );
2065 l_attribute_string := add_value_to_string
2066 (l_attribute_string
2067 ,p_attributes(l_attribute_count).attribute20
2068 );
2069
2070 l_attribute_string := add_value_to_string
2071 (l_attribute_string
2072 ,p_attributes(l_attribute_count).attribute21
2073 );
2074 l_attribute_string := add_value_to_string
2075 (l_attribute_string
2076 ,p_attributes(l_attribute_count).attribute22
2077 );
2078
2079 l_attribute_string := add_value_to_string
2080 (l_attribute_string
2081 ,p_attributes(l_attribute_count).attribute23
2082 );
2083 l_attribute_string := add_value_to_string
2084 (l_attribute_string
2085 ,p_attributes(l_attribute_count).attribute24
2086 );
2087
2088
2089 l_attribute_string := add_value_to_string
2090 (l_attribute_string
2091 ,p_attributes(l_attribute_count).attribute25
2092 );
2093
2094 l_attribute_string := add_value_to_string
2095 (l_attribute_string
2096 ,p_attributes(l_attribute_count).attribute26
2097 );
2098
2099 l_attribute_string := add_value_to_string
2100 (l_attribute_string
2101 ,p_attributes(l_attribute_count).attribute27
2102 );
2103 l_attribute_string := add_value_to_string
2104 (l_attribute_string
2105 ,p_attributes(l_attribute_count).attribute28
2106 );
2107 l_attribute_string := add_value_to_string
2108 (l_attribute_string
2109 ,p_attributes(l_attribute_count).attribute29
2110 );
2111 l_attribute_string := add_value_to_string
2112 (l_attribute_string
2113 ,p_attributes(l_attribute_count).attribute30
2114 );
2115 l_attribute_string := add_value_to_string
2116 (l_attribute_string
2117 ,p_attributes(l_attribute_count).bld_blk_info_type_id
2118 );
2119 l_attribute_string := add_value_to_string
2120 (l_attribute_string
2121 ,p_attributes(l_attribute_count).object_version_number
2122 );
2123 l_attribute_string := add_value_to_string
2124 (l_attribute_string
2125 ,p_attributes(l_attribute_count).new
2126 );
2127
2128 l_attribute_string := add_value_to_string
2129 (l_attribute_string
2130 ,p_attributes(l_attribute_count).changed
2131 );
2132
2133 l_attribute_string := add_value_to_string
2134 (l_attribute_string
2135 ,p_attributes(l_attribute_count).bld_blk_info_type
2136 );
2137
2138 l_attribute_count := p_attributes.next(l_attribute_count);
2139
2140 END LOOP;
2141
2142 RETURN l_attribute_string;
2143 END attributes_to_string;
2144
2145 -- simple utility to load a pl/sql table from a token separated string of the
2146 -- form '|one|two|three|...'. Table indexing starts at zero
2147 -- Cases:
2148 -- string='|': returns one null table entry
2149 -- string='': returns zero tables entry
2150 -- string='|a|bc' returns two table entries. table(0)='a'. table(1)='bc'
2151
2152 PROCEDURE STRING_TO_TABLE(p_separator IN VARCHAR2,
2153 p_string IN VARCHAR2,
2154 p_table OUT NOCOPY t_simple_table)
2155 is
2156 l_value_index NUMBER :=1;
2157
2158 l_index_start NUMBER;
2159 l_index_next NUMBER;
2160 l_loop_count NUMBER;
2161 l_result hxc_time_building_blocks.comment_text%TYPE;
2162
2163
2164 begin
2165
2166 l_index_start:=INSTR(p_string,p_separator,1,1)+1;
2167
2168 if(l_index_start=1 OR l_index_start is null) then
2169 return;
2170 end if;
2171
2172 l_loop_count:=0;
2173
2174 LOOP
2175
2176 l_index_next := INSTR(p_string,p_separator,l_index_start,1);
2177
2178 if(l_index_next=0) then
2179 if ( length(p_string)+1-l_index_start > 2000 )
2180 then
2181 l_result := SUBSTR(p_string,l_index_start,2000);
2182 else
2183 l_result := SUBSTR(p_string,l_index_start,length(p_string)+1-l_index_start);
2184 end if;
2185 else
2186 if ( l_index_next-l_index_start > 2000 )
2187 then
2188 l_result := SUBSTR(p_string,l_index_start,2000);
2189 else
2190 l_result := SUBSTR(p_string,l_index_start,l_index_next-l_index_start);
2191 end if;
2192 end if;
2193
2194 IF l_result = 'null'
2195 THEN
2196 l_result := NULL;
2197 END IF;
2198
2199 p_table(l_loop_count):=l_result;
2200
2201 l_index_start:=l_index_next+1;
2202 l_loop_count:=l_loop_count+1;
2203 EXIT WHEN l_index_next = 0;
2204
2205 if(l_loop_count>30000) then
2206 hr_utility.set_message(809,'HXC_LPS');
2207 hr_utility.raise_error;
2208 end if;
2209
2210
2211 END LOOP;
2212
2213 end STRING_TO_TABLE;
2214
2215 -- procedure
2216 -- audit_transaction
2217 --
2218 -- description
2219 -- records details of a deposit transaction in HXC_DEP_TRANSACTIONS
2220 -- records details of a retrieval transaction in HXC_TRANSACTIONS
2221 -- parameters
2222 -- p_effective_date - the effective date of the transaction
2223 -- p_transaction_type - deposit type (DEPOSIT/RETRIEVAL)
2224 -- p_transaction_process_id - the id of the deposit process
2225 -- p_overall_status - overall deposit status
2226 -- p_transaction_status - table of transactions
2227
2228 procedure audit_transaction
2229 (p_effective_date in date
2230 ,p_transaction_type in varchar2
2231 ,p_transaction_process_id in number
2232 ,p_overall_status in varchar2
2233 ,p_transaction_tab in out nocopy t_transaction
2234 ) is
2235
2236 PRAGMA AUTONOMOUS_TRANSACTION;
2237
2238 cursor c_transaction_sequence is
2239 select hxc_transactions_s.nextval from dual;
2240
2241 cursor c_transaction_detail_sequence is
2242 select hxc_transaction_details_s.nextval from dual;
2243
2244 l_transaction_id hxc_transactions.transaction_id%TYPE;
2245 l_transaction_detail_id hxc_transaction_details.transaction_detail_id%TYPE;
2246 l_tx_ind BINARY_INTEGER;
2247
2248 begin
2249 open c_transaction_sequence;
2250 fetch c_transaction_sequence into l_transaction_id;
2251 close c_transaction_sequence;
2252
2253
2254 IF p_transaction_type = 'DEPOSIT' THEN
2255 insert into hxc_dep_transactions
2256 (transaction_id
2257 ,transaction_date
2258 ,type
2259 ,transaction_process_id
2260 ,created_by
2261 ,creation_date
2262 ,last_updated_by
2263 ,last_update_date
2264 ,last_update_login
2265 ,status
2266 ) values
2267 (l_transaction_id
2268 ,p_effective_date
2269 ,p_transaction_type
2270 ,p_transaction_process_id
2271 ,null
2272 ,sysdate
2273 ,null
2274 ,sysdate
2275 ,null
2276 ,p_overall_status
2277 );
2278 ELSE
2279 insert into hxc_transactions
2280 (transaction_id
2281 ,transaction_date
2282 ,type
2283 ,transaction_process_id
2284 ,created_by
2285 ,creation_date
2286 ,last_updated_by
2287 ,last_update_date
2288 ,last_update_login
2289 ,status
2290 ) values
2291 (l_transaction_id
2292 ,p_effective_date
2293 ,p_transaction_type
2294 ,p_transaction_process_id
2295 ,null
2296 ,sysdate
2297 ,null
2298 ,sysdate
2299 ,null
2300 ,p_overall_status
2301 );
2302 END IF;
2303
2304
2305
2306
2307 l_tx_ind := p_transaction_tab.FIRST;
2308
2309 WHILE ( l_tx_ind IS NOT NULL )
2310 LOOP
2311
2312 open c_transaction_detail_sequence;
2313 fetch c_transaction_detail_sequence into l_transaction_detail_id;
2314 close c_transaction_detail_sequence;
2315
2316 IF p_transaction_type = 'DEPOSIT' THEN
2317 insert into hxc_dep_transaction_details
2318 (transaction_detail_id
2319 ,time_building_block_id
2320 ,transaction_id
2321 ,created_by
2322 ,creation_date
2323 ,last_updated_by
2324 ,last_update_date
2325 ,last_update_login
2326 ,time_building_block_ovn
2327 ,status
2328 ,exception_description
2329 ) values
2330 (l_transaction_detail_id
2331 ,p_transaction_tab(l_tx_ind).tbb_id
2332 ,l_transaction_id
2333 ,null
2334 ,sysdate
2335 ,null
2336 ,sysdate
2337 ,null
2338 ,p_transaction_tab(l_tx_ind).tbb_ovn
2339 ,p_transaction_tab(l_tx_ind).status
2340 ,p_transaction_tab(l_tx_ind).exception_desc
2341 );
2342 ELSE
2343 insert into hxc_transaction_details
2344 (transaction_detail_id
2345 ,time_building_block_id
2346 ,transaction_id
2347 ,created_by
2348 ,creation_date
2349 ,last_updated_by
2350 ,last_update_date
2351 ,last_update_login
2352 ,time_building_block_ovn
2353 ,status
2354 ,exception_description
2355 ) values
2356 (l_transaction_detail_id
2357 ,p_transaction_tab(l_tx_ind).tbb_id
2358 ,l_transaction_id
2359 ,null
2360 ,sysdate
2361 ,null
2362 ,sysdate
2363 ,null
2364 ,p_transaction_tab(l_tx_ind).tbb_ovn
2365 ,p_transaction_tab(l_tx_ind).status
2366 ,p_transaction_tab(l_tx_ind).exception_desc
2367 );
2368 END IF;
2369
2370 p_transaction_tab(l_tx_ind).txd_id := l_transaction_detail_id;
2371
2372 l_tx_ind := p_transaction_tab.NEXT(l_tx_ind);
2373
2374 END LOOP;
2375
2376 commit;
2377
2378 end audit_transaction;
2379
2380 ----
2381 -- Function returning a list of hours types and ids for use on the timecard
2382 ----
2383
2384 function timecard_hours_type_list( p_resource_id in varchar2,
2385 p_start_time in varchar2,
2386 p_stop_time in varchar2,
2387 p_alias_or_element_id in varchar2) return varchar2
2388
2389 is
2390
2391 TYPE t_hours_type_list_row is RECORD (
2392 element_id NUMBER,
2393 alias_value_id NUMBER,
2394 display_value hxc_alias_values.alias_value_name%TYPE
2395 );
2396
2397 TYPE t_hours_type_list is table of
2398 t_hours_type_list_row
2399 index by binary_integer;
2400
2401 l_index NUMBER;
2402 l_hrs_typ_index NUMBER;
2403 l_loop_count NUMBER;
2404 l_hours_type_list t_hours_type_list;
2405 l_aliases hxc_alias_utility.t_alias_def_item;
2406 l_id_string VARCHAR2(30); -- this is used to store NUMBER(15)
2407 l_ht_list_string VARCHAR2(32000);
2408 l_resource_id NUMBER;
2409 l_start_time DATE;
2410 l_stop_time DATE;
2411 l_time_diff number := 0;
2412 -- Adding v115.53 Fix for Bug. 3161167
2413 -- Adding new variables used for Insertion Sorting implementation.
2414 i NUMBER;
2415 j NUMBER;
2416 l_hours_type_list_ins_alg t_hours_type_list;
2417 l_display_value_ins_alg hxc_alias_values.alias_value_name%TYPE;
2418
2419 --Added for bug 10006152
2420 l_temp1_hours_type_list t_hours_type_list;
2421 l_temp2_hours_type_list t_hours_type_list;
2422 l_element_id NUMBER;
2423 l_display_value hxc_alias_values.alias_value_name%TYPE;
2424 l_dup_alias_value VARCHAR2(1) := 'N';
2425
2426 cursor csr_hours_type (p_alias_definition_id number,
2427 p_start_date date,
2428 p_end_date date,
2429 p_person_id number)
2430 is
2431 select havt.alias_value_name Display_Value,
2432 hav.attribute1 element_id,
2433 hav.alias_value_id alias_value_id
2434 from hxc_alias_values hav,
2435 hxc_alias_values_tl havt,
2436 hxc_alias_definitions had
2437 where
2438 --hav.attribute_category='PAYROLL_ELEMENTS'
2439 hav.enabled_flag='Y'
2440 and had.alias_definition_id = hav.alias_definition_id
2441 and had.alias_definition_id = p_alias_definition_id
2442 and havt.language = USERENV('LANG')
2443 and havt.alias_value_id = hav.alias_value_id
2444 and hav.date_from <= p_end_date
2445 and nvl(hav.date_to,hr_general.end_of_time) >=p_start_date
2446 and exists (
2447 select 'x'
2448 from PAY_ELEMENT_TYPES_F ELEMENT,
2449 PAY_ELEMENT_CLASSIFICATIONS CLASSIFICATION,
2450 BEN_BENEFIT_CLASSIFICATIONS BENEFIT,
2451 PAY_ELEMENT_LINKS_F LINK,
2452 PER_ALL_ASSIGNMENTS_F ASGT,
2453 PER_PERIODS_OF_SERVICE SERVICE_PERIOD
2454 WHERE
2455 asgt.person_id = p_person_id and
2456 to_number(hav.attribute1) = ELEMENT.element_type_id
2457 AND ELEMENT.EFFECTIVE_START_DATE <= p_end_date
2458 AND ELEMENT.EFFECTIVE_END_DATE >= p_start_date
2459 AND ASGT.BUSINESS_GROUP_ID = LINK.BUSINESS_GROUP_ID
2460 AND ELEMENT.ELEMENT_TYPE_ID = LINK.ELEMENT_TYPE_ID
2461 AND ELEMENT.BENEFIT_CLASSIFICATION_ID = BENEFIT.BENEFIT_CLASSIFICATION_ID (+)
2462 AND ELEMENT.CLASSIFICATION_ID = CLASSIFICATION.CLASSIFICATION_ID
2463 AND SERVICE_PERIOD.PERIOD_OF_SERVICE_ID = ASGT.PERIOD_OF_SERVICE_ID
2464 AND ASGT.EFFECTIVE_START_DATE <= p_end_date
2465 AND ASGT.EFFECTIVE_END_DATE >= p_start_date
2466 AND LINK.EFFECTIVE_START_DATE <= p_end_date
2467 AND LINK.EFFECTIVE_END_DATE >= p_start_date
2468 AND ELEMENT.INDIRECT_ONLY_FLAG = 'N'
2469 AND UPPER (ELEMENT.ELEMENT_NAME) <> 'VERTEX'
2470 AND not exists
2471 (select 1
2472 from HR_ORGANIZATION_INFORMATION HOI,
2473 PAY_LEGISLATION_RULES PLR
2474 WHERE plr.rule_type in
2475 ('ADVANCE','ADVANCE_INDICATOR','ADV_DEDUCTION',
2476 'PAY_ADVANCE_INDICATOR','ADV_CLEARUP','DEFER_PAY')
2477 AND plr.rule_mode = to_char(element.element_type_id)
2478 AND plr.legislation_code = hoi.org_information9
2479 AND HOI.ORGANIZATION_ID = ASGT.ORGANIZATION_ID
2480 )
2481 AND ELEMENT.CLOSED_FOR_ENTRY_FLAG = 'N'
2482 AND ELEMENT.ADJUSTMENT_ONLY_FLAG = 'N'
2483 AND ((LINK.PAYROLL_ID IS NOT NULL AND LINK.PAYROLL_ID = ASGT.PAYROLL_ID)
2484 OR (LINK.LINK_TO_ALL_PAYROLLS_FLAG = 'Y' AND ASGT.PAYROLL_ID IS NOT NULL)
2485 OR (LINK.PAYROLL_ID IS NULL AND LINK.LINK_TO_ALL_PAYROLLS_FLAG = 'N'))
2486 AND (LINK.ORGANIZATION_ID = ASGT.ORGANIZATION_ID OR LINK.ORGANIZATION_ID IS NULL)
2487 AND (LINK.POSITION_ID = ASGT.POSITION_ID OR LINK.POSITION_ID IS NULL)
2488 AND (LINK.JOB_ID = ASGT.JOB_ID OR LINK.JOB_ID IS NULL)
2489 AND (LINK.GRADE_ID = ASGT.GRADE_ID OR LINK.GRADE_ID IS NULL)
2490 AND (LINK.LOCATION_ID = ASGT.LOCATION_ID OR LINK.LOCATION_ID IS NULL)
2491 AND (LINK.PAY_BASIS_ID = ASGT.PAY_BASIS_ID OR LINK.PAY_BASIS_ID IS NULL)
2492 AND (LINK.EMPLOYMENT_CATEGORY = ASGT.EMPLOYMENT_CATEGORY OR
2493 LINK.EMPLOYMENT_CATEGORY IS NULL)
2494 AND (LINK.PEOPLE_GROUP_ID IS NULL
2495 OR EXISTS (
2496 SELECT 1 FROM PAY_ASSIGNMENT_LINK_USAGES_F USAGE
2497 WHERE USAGE.ASSIGNMENT_ID = ASGT.ASSIGNMENT_ID
2498 AND USAGE.ELEMENT_LINK_ID = LINK.ELEMENT_LINK_ID
2499 AND (USAGE.EFFECTIVE_START_DATE <= p_end_date
2500 AND USAGE.EFFECTIVE_END_DATE >= p_start_date)))
2501 AND (ELEMENT.PROCESSING_TYPE = 'R' OR ASGT.PAYROLL_ID IS NOT NULL)
2502 AND (SERVICE_PERIOD.ACTUAL_TERMINATION_DATE IS NULL
2503 OR (SERVICE_PERIOD.ACTUAL_TERMINATION_DATE IS NOT NULL
2504 AND p_start_date <= DECODE(ELEMENT.POST_TERMINATION_RULE,
2505 'L', NVL(SERVICE_PERIOD.LAST_STANDARD_PROCESS_DATE,hr_general.end_of_time),
2506 'F', NVL(SERVICE_PERIOD.FINAL_PROCESS_DATE,
2507 hr_general.end_of_time),
2508 SERVICE_PERIOD.ACTUAL_TERMINATION_DATE))))
2509 ORDER BY Display_Value;
2510
2511
2512 BEGIN
2513
2514 g_debug:=hr_utility.debug_enabled;
2515 l_time_diff := 24*60*(sysdate - g_ht_time);
2516
2517 IF g_debug THEN
2518 hr_utility.trace(' In HXC_DEPOSIT_WRAPPER_UTILITIES.TIMECARD_HOURS_TYPE_LIST procedure');
2519
2520 hr_utility.trace(' ****************Initial Information************************ ');
2521
2522 hr_utility.trace(' ****************Start of Local values************************ ');
2523 hr_utility.trace(' p_resource_id ::'||p_resource_id);
2524 hr_utility.trace(' p_start_time ::'||p_start_time);
2525 hr_utility.trace(' p_stop_time ::'||p_stop_time);
2526 hr_utility.trace(' p_alias_or_element_id ::'||p_alias_or_element_id);
2527 hr_utility.trace(' fnd_global.resp_id ::'||fnd_global.resp_id);
2528 hr_utility.trace(' ****************End of Local values************************ ');
2529
2530 hr_utility.trace(' ****************Start of Global values************************ ');
2531 hr_utility.trace(' g_ht_resource_id ::'||g_ht_resource_id);
2532 hr_utility.trace(' g_ht_start_time ::'||g_ht_start_time);
2533 hr_utility.trace(' g_ht_stop_time ::'||g_ht_stop_time);
2534 hr_utility.trace(' g_ht_alias_or_element_id ::'||g_ht_alias_or_element_id);
2535 hr_utility.trace(' g_ht_resp_id ::'||g_ht_resp_id);
2536 hr_utility.trace(' ****************End of Global values************************ ');
2537
2538 hr_utility.trace(' sysdate ::'||sysdate);
2539 hr_utility.trace(' g_ht_time ::'||g_ht_time);
2540 hr_utility.trace(' l_time_diff ::'||l_time_diff);
2541
2542 hr_utility.trace(' ****************End of Initial Information************************ ');
2543 END IF;
2544
2545 -- check to see if we can use a cached result
2546
2547 IF l_time_diff < 5 then
2548
2549 if( g_ht_resource_id = p_resource_id AND
2550 g_ht_start_time = p_start_time AND
2551 g_ht_stop_time = p_stop_time AND
2552 g_ht_alias_or_element_id = p_alias_or_element_id AND
2553 g_ht_resp_id = fnd_global.resp_id )
2554 THEN
2555
2556 IF g_debug THEN
2557 hr_utility.trace(' IF l_time_diff < 5 then RETURN g_hours_type_list ::'||g_hours_type_list);
2558 END IF;
2559
2560 return g_hours_type_list;
2561
2562 END IF;
2563
2564 END IF; --- time diff testing
2565
2566 -- if not able to use cached result, need to generate. Store params so that we could cache next time
2567
2568 g_ht_resource_id := p_resource_id;
2569 g_ht_start_time := p_start_time;
2570 g_ht_stop_time := p_stop_time;
2571 g_ht_alias_or_element_id := p_alias_or_element_id;
2572 g_ht_resp_id := fnd_global.resp_id;
2573 g_ht_time := sysdate;
2574
2575 IF g_debug THEN
2576 hr_utility.trace(' if not able to use cached result, need to generate. Store params so that we could cache next time');
2577 hr_utility.trace(' g_ht_resource_id ::'||g_ht_resource_id);
2578 hr_utility.trace(' g_ht_start_time ::'||g_ht_start_time);
2579 hr_utility.trace(' g_ht_stop_time ::'||g_ht_stop_time);
2580 hr_utility.trace(' g_ht_alias_or_element_id ::'||g_ht_alias_or_element_id);
2581 hr_utility.trace(' g_ht_resp_id ::'||g_ht_resp_id);
2582 hr_utility.trace(' g_ht_time ::'||g_ht_time);
2583 hr_utility.trace(' end of caced items.');
2584 END IF;
2585
2586
2587 -- convert params
2588 l_resource_id := p_resource_id;
2589 --l_start_time := FND_DATE.CANONICAL_TO_DATE(p_start_time);
2590 --l_stop_time := FND_DATE.CANONICAL_TO_DATE(p_stop_time);
2591
2592 IF g_debug THEN
2593 hr_utility.trace(' Get list of valid aliases for this period.');
2594 END IF;
2595
2596 -- get list of valid aliases for this period
2597
2598 IF (p_alias_or_element_id = 'ALIAS'
2599 OR p_alias_or_element_id = 'ELEMENT') THEN
2600
2601 l_aliases := HXC_ALIAS_UTILITY.get_list_alias_id(p_alias_type => 'PAYROLL_ELEMENTS'
2602 ,p_start_time => p_start_time
2603 ,p_stop_time => p_stop_time
2604 ,p_resource_id => l_resource_id );
2605
2606 ELSE
2607
2608
2609 l_aliases := HXC_ALIAS_UTILITY.get_list_alias_id(p_alias_type => p_alias_or_element_id
2610 ,p_start_time => p_start_time
2611 ,p_stop_time => p_stop_time
2612 ,p_resource_id => l_resource_id );
2613
2614 --if g_debug then
2615 --hr_utility.trace('p_alias_or_element_id '||p_alias_or_element_id);
2616 --end if;
2617
2618 END IF;
2619
2620 IF g_debug THEN
2621
2622 l_index := l_aliases.FIRST;
2623 LOOP
2624 EXIT WHEN NOT l_aliases.exists(l_index);
2625
2626 hr_utility.trace(' RESOURCE_ID : '||l_aliases(l_index).RESOURCE_ID);
2627 hr_utility.trace(' PREF_START_DATE : '||l_aliases(l_index).PREF_START_DATE);
2628 hr_utility.trace(' PREF_END_DATE : '||l_aliases(l_index).PREF_END_DATE);
2629 hr_utility.trace(' ALIAS_DEFINITION_ID : '||l_aliases(l_index).ALIAS_DEFINITION_ID);
2630 hr_utility.trace(' ITEM_ATTRIBUTE_CATEGORY : '||l_aliases(l_index).ITEM_ATTRIBUTE_CATEGORY);
2631 hr_utility.trace(' LAYOUT_ID : '||l_aliases(l_index).LAYOUT_ID);
2632 hr_utility.trace(' ALIAS_LABEL : '||l_aliases(l_index).ALIAS_LABEL);
2633
2634 l_index := l_aliases.NEXT(l_index);
2635 END LOOP;
2636 END IF;
2637
2638 -- pull out TC_W_TCRD_ALIASES values
2639
2640 l_index:=l_aliases.FIRST;
2641 l_start_time := FND_DATE.CANONICAL_TO_DATE(p_start_time);
2642 l_stop_time := FND_DATE.CANONICAL_TO_DATE(p_stop_time);
2643
2644 -- first check to see that aliases have been setup for this person!!!
2645 -- v115.23
2646
2647 IF ( l_index IS NOT NULL )
2648 THEN
2649
2650 LOOP
2651
2652 -- execute cursor against each of these values. Shouldnt be too slow since
2653 -- SQL cache should save us some time. Also, this will only execute more than
2654 -- once if the alias changes mid period, which will not be the most common case
2655
2656 --dbms_output.put_line('Alais defn id:'||l_pref_table(l_index).attribute1);
2657
2658 IF g_debug THEN
2659 hr_utility.trace(' l_loop_count ::'||l_loop_count);
2660 END IF;
2661
2662 FOR l_hours_type in csr_hours_type(p_person_id => l_resource_id,
2663 p_start_date => l_aliases(l_index).pref_start_date,
2664 p_end_date => l_aliases(l_index).pref_end_date,
2665 p_alias_definition_id => l_aliases(l_index).alias_definition_id) LOOP
2666
2667 -- Add values to list . Index list by alias_value_id thus removing duplicates automatically
2668
2669 --dbms_output.put_line('Element ID:'||pot_value.element_id);
2670 --dbms_output.put_line('Alias Value id:'||pot_value.alias_value_id);
2671 --dbms_output.put_line('Display_name:'||pot_value.display_value);
2672
2673 l_hours_type_list(l_hours_type.alias_value_id).element_id := l_hours_type.element_id;
2674 l_hours_type_list(l_hours_type.alias_value_id).alias_value_id := l_hours_type.alias_value_id;
2675 l_hours_type_list(l_hours_type.alias_value_id).display_value := l_hours_type.display_value;
2676
2677 IF g_debug THEN
2678 hr_utility.trace(' l_hours_type.alias_value_id ::'||l_hours_type.alias_value_id);
2679 hr_utility.trace(' l_hours_type.element_id ::'||l_hours_type.element_id);
2680 hr_utility.trace(' l_hours_type.alias_value_id ::'||l_hours_type.alias_value_id);
2681 hr_utility.trace(' l_hours_type.display_value ::'||l_hours_type.display_value);
2682 END IF;
2683
2684 END LOOP;
2685
2686 EXIT WHEN l_loop_count > 5000;
2687
2688 l_loop_count := l_loop_count+1;
2689 l_index := l_aliases.NEXT(l_index);
2690
2691 EXIT WHEN NOT l_aliases.EXISTS(l_index);
2692
2693 END LOOP;
2694
2695
2696 -- Adding logic to handle mid-period preference change, fix for bug 10006152
2697
2698 IF g_debug THEN
2699 hr_utility.trace('Printing values in l_hours_type_list before mid-period preference change logic starts..');
2700 l_index := l_hours_type_list.FIRST;
2701 LOOP
2702 EXIT WHEN NOT l_hours_type_list.exists(l_index);
2703
2704 hr_utility.trace(' element_id : '||l_hours_type_list(l_index).element_id);
2705 hr_utility.trace(' alias_value_id : '||l_hours_type_list(l_index).alias_value_id);
2706 hr_utility.trace(' display_value : '||l_hours_type_list(l_index).display_value);
2707
2708 l_index := l_hours_type_list.NEXT(l_index);
2709 END LOOP;
2710 hr_utility.trace('End of values in l_hours_type_list');
2711 END IF;
2712
2713
2714 l_temp1_hours_type_list := l_hours_type_list;
2715 l_hrs_typ_index := null;
2716 l_index := null;
2717
2718 l_index := l_temp1_hours_type_list.first;
2719 while l_index is not null
2720 loop
2721 l_element_id := l_temp1_hours_type_list(l_index).element_id;
2722 l_display_value := l_temp1_hours_type_list(l_index).display_value;
2723
2724 IF l_temp2_hours_type_list.COUNT > 0 THEN
2725 -- Logic to check dup value
2726 l_dup_alias_value := 'N';
2727 l_hrs_typ_index := l_temp2_hours_type_list.first;
2728 while l_hrs_typ_index is not null
2729 loop
2730
2731 IF l_element_id = l_temp2_hours_type_list(l_hrs_typ_index).element_id
2732 AND l_display_value = l_temp2_hours_type_list(l_hrs_typ_index).display_value
2733 THEN
2734 l_dup_alias_value := 'Y';
2735 END IF;
2736
2737 l_hrs_typ_index := l_temp2_hours_type_list.NEXT(l_hrs_typ_index);
2738 end loop;
2739
2740 IF l_dup_alias_value = 'N' THEN
2741 l_temp2_hours_type_list(l_index).element_id := l_temp1_hours_type_list(l_index).element_id;
2742 l_temp2_hours_type_list(l_index).alias_value_id := l_temp1_hours_type_list(l_index).alias_value_id;
2743 l_temp2_hours_type_list(l_index).display_value := l_temp1_hours_type_list(l_index).display_value;
2744 END IF;
2745
2746 ELSE -- IF l_temp2_hours_type_list.COUNT > 0 THEN
2747
2748 l_temp2_hours_type_list(l_index).element_id := l_temp1_hours_type_list(l_index).element_id;
2749 l_temp2_hours_type_list(l_index).alias_value_id := l_temp1_hours_type_list(l_index).alias_value_id;
2750 l_temp2_hours_type_list(l_index).display_value := l_temp1_hours_type_list(l_index).display_value;
2751 END IF;
2752
2753 l_index := l_temp1_hours_type_list.NEXT(l_index);
2754 end loop;
2755
2756 l_hours_type_list := l_temp2_hours_type_list;
2757
2758 IF g_debug THEN
2759 hr_utility.trace('Printing values in l_hours_type_list after mid-period preference logic ...');
2760 l_index := l_hours_type_list.FIRST;
2761 LOOP
2762 EXIT WHEN NOT l_hours_type_list.exists(l_index);
2763
2764 hr_utility.trace(' element_id : '||l_hours_type_list(l_index).element_id);
2765 hr_utility.trace(' alias_value_id : '||l_hours_type_list(l_index).alias_value_id);
2766 hr_utility.trace(' display_value : '||l_hours_type_list(l_index).display_value);
2767
2768 l_index := l_hours_type_list.NEXT(l_index);
2769 END LOOP;
2770 hr_utility.trace('End of values in l_hours_type_list');
2771 END IF;
2772
2773 -- End of fix for bug 10006152
2774
2775 -- Adding v115.56 fix for bug. 3264226
2776 l_hours_type_list_ins_alg := l_hours_type_list;
2777 l_hours_type_list.DELETE;
2778
2779 l_hrs_typ_index := 1;
2780 l_index := null;
2781 l_index := l_hours_type_list_ins_alg.first;
2782 while l_index is not null
2783 loop
2784 l_hours_type_list(l_hrs_typ_index).element_id := l_hours_type_list_ins_alg(l_index).element_id;
2785 l_hours_type_list(l_hrs_typ_index).alias_value_id := l_hours_type_list_ins_alg(l_index).alias_value_id;
2786 l_hours_type_list(l_hrs_typ_index).display_value := l_hours_type_list_ins_alg(l_index).display_value;
2787
2788 l_index := l_hours_type_list_ins_alg.NEXT(l_index);
2789 l_hrs_typ_index := l_hrs_typ_index + 1;
2790 end loop;
2791 l_hours_type_list_ins_alg.DELETE;
2792
2793
2794 -- Adding v115.53 Fix for Bug. 3161167
2795 -- Adding new Insertion sort Algorithm
2796 IF ( l_hours_type_list.COUNT > 0) Then
2797 For i in l_hours_type_list.First+1..l_hours_type_list.LAST
2798 Loop
2799 l_display_value_ins_alg:=l_hours_type_list(i).display_value;
2800 l_hours_type_list_ins_alg(1) := l_hours_type_list(i);
2801 <<inner_loop>>
2802 For j in REVERSE l_hours_type_list.First.. (i-1)
2803 Loop
2804 If l_hours_type_list(j).display_value >= l_display_value_ins_alg then
2805 l_hours_type_list(j+1):=l_hours_type_list(j);
2806 l_hours_type_list(j):=l_hours_type_list_ins_alg(1);
2807 end if;
2808 end loop inner_loop;
2809 end loop;
2810 End If;
2811 -- End sorting logic
2812
2813 IF g_debug THEN
2814
2815 l_index := l_hours_type_list.FIRST;
2816 LOOP
2817 EXIT WHEN NOT l_hours_type_list.exists(l_index);
2818
2819 hr_utility.trace(' element_id : '||l_hours_type_list(l_index).element_id);
2820 hr_utility.trace(' alias_value_id : '||l_hours_type_list(l_index).alias_value_id);
2821 hr_utility.trace(' display_value : '||l_hours_type_list(l_index).display_value);
2822
2823 l_index := l_hours_type_list.NEXT(l_index);
2824 END LOOP;
2825 END IF;
2826
2827 -- now compile string according to callers choice
2828
2829 l_index:=l_hours_type_list.FIRST;
2830
2831 if (l_index is not null) then
2832
2833 LOOP
2834
2835 IF (p_alias_or_element_id = 'ALIAS') THEN
2836 l_id_string := l_hours_type_list(l_index).alias_value_id;
2837 ELSIF (p_alias_or_element_id = 'ELEMENT') THEN
2838 l_id_string := l_hours_type_list(l_index).element_id;
2839 ELSE
2840 l_id_string := l_hours_type_list(l_index).alias_value_id;
2841 END IF;
2842
2843 l_ht_list_string := l_ht_list_string
2844 ||l_hours_type_list(l_index).display_value
2845 ||'|'
2846 ||l_id_string
2847 ||'|';
2848
2849 EXIT WHEN l_loop_count > 5000;
2850
2851 l_loop_count := l_loop_count+1;
2852 l_index := l_hours_type_list.NEXT(l_index);
2853
2854 EXIT when not l_hours_type_list.EXISTS(l_index);
2855
2856 END LOOP;
2857
2858
2859
2860 g_hours_type_list := substr(l_ht_list_string,1,length(l_ht_list_string)-1);
2861
2862 IF g_debug THEN
2863 hr_utility.trace(' l_ht_list_string ::'||l_ht_list_string);
2864 hr_utility.trace(' g_hours_type_list ::'||g_hours_type_list);
2865 hr_utility.trace(' RETURN THE LIST');
2866 hr_utility.trace('10006152> Debug END');
2867 END IF;
2868
2869 RETURN g_hours_type_list;
2870
2871 else
2872
2873 IF g_debug THEN
2874 hr_utility.trace('if (l_index is not null) then');
2875 hr_utility.trace(' RETURN null ');
2876 END IF;
2877
2878 g_hours_type_list := null; -- added for bug 8814955
2879
2880 return g_hours_type_list;
2881
2882 end if;
2883
2884 ELSE -- l_index for alias list is null v115.23
2885
2886 IF g_debug THEN
2887 hr_utility.trace('IF ( l_index IS NOT NULL )');
2888 hr_utility.trace(' RETURN null ');
2889 END IF;
2890
2891 g_hours_type_list := null; -- added for bug 8814955
2892
2893 return g_hours_type_list;
2894
2895 END IF;
2896
2897 END timecard_hours_type_list;
2898
2899
2900 --
2901 --
2902 FUNCTION array_to_attributes(
2903 p_attribute_array IN HXC_ATTRIBUTE_TABLE_TYPE
2904 )
2905 RETURN hxc_self_service_time_deposit.building_block_attribute_info
2906 IS
2907 l_array_index NUMBER;
2908 l_attribute_count NUMBER := 1;
2909 l_attributes hxc_self_service_time_deposit.building_block_attribute_info;
2910 BEGIN
2911 l_array_index := p_attribute_array.first;
2912 LOOP
2913 EXIT WHEN NOT p_attribute_array.exists(l_array_index);
2914
2915 l_attributes(l_attribute_count).time_attribute_id := p_attribute_array(l_array_index).time_attribute_id;
2916 l_attributes(l_attribute_count).building_block_id := p_attribute_array(l_array_index).building_block_id;
2917 l_attributes(l_attribute_count).Attribute_Category := p_attribute_array(l_array_index).Attribute_Category;
2918 l_attributes(l_attribute_count).attribute1 := p_attribute_array(l_array_index).attribute1;
2919 l_attributes(l_attribute_count).attribute2 := p_attribute_array(l_array_index).attribute2;
2920 l_attributes(l_attribute_count).attribute3 := p_attribute_array(l_array_index).attribute3;
2921 l_attributes(l_attribute_count).attribute4 := p_attribute_array(l_array_index).attribute4;
2922 l_attributes(l_attribute_count).attribute5 := p_attribute_array(l_array_index).attribute5;
2923 l_attributes(l_attribute_count).attribute6 := p_attribute_array(l_array_index).attribute6;
2924 l_attributes(l_attribute_count).attribute7 := p_attribute_array(l_array_index).attribute7;
2925 l_attributes(l_attribute_count).attribute8 := p_attribute_array(l_array_index).attribute8;
2926 l_attributes(l_attribute_count).attribute9 := p_attribute_array(l_array_index).attribute9;
2927 l_attributes(l_attribute_count).attribute10 := p_attribute_array(l_array_index).attribute10;
2928 l_attributes(l_attribute_count).attribute11 := p_attribute_array(l_array_index).attribute11;
2929 l_attributes(l_attribute_count).attribute12 := p_attribute_array(l_array_index).attribute12;
2930 l_attributes(l_attribute_count).attribute13 := p_attribute_array(l_array_index).attribute13;
2931 l_attributes(l_attribute_count).attribute14 := p_attribute_array(l_array_index).attribute14;
2932 l_attributes(l_attribute_count).attribute15 := p_attribute_array(l_array_index).attribute15;
2933 l_attributes(l_attribute_count).attribute16 := p_attribute_array(l_array_index).attribute16;
2934 l_attributes(l_attribute_count).attribute17 := p_attribute_array(l_array_index).attribute17;
2935 l_attributes(l_attribute_count).attribute18 := p_attribute_array(l_array_index).attribute18;
2936 l_attributes(l_attribute_count).attribute19 := p_attribute_array(l_array_index).attribute19;
2937 l_attributes(l_attribute_count).attribute20 := p_attribute_array(l_array_index).attribute20;
2938 l_attributes(l_attribute_count).attribute21 := p_attribute_array(l_array_index).attribute21;
2939 l_attributes(l_attribute_count).attribute22 := p_attribute_array(l_array_index).attribute22;
2940 l_attributes(l_attribute_count).attribute23 := p_attribute_array(l_array_index).attribute23;
2941 l_attributes(l_attribute_count).attribute24 := p_attribute_array(l_array_index).attribute24;
2942 l_attributes(l_attribute_count).attribute25 := p_attribute_array(l_array_index).attribute25;
2943 l_attributes(l_attribute_count).attribute26 := p_attribute_array(l_array_index).attribute26;
2944 l_attributes(l_attribute_count).attribute27 := p_attribute_array(l_array_index).attribute27;
2945 l_attributes(l_attribute_count).attribute28 := p_attribute_array(l_array_index).attribute28;
2946 l_attributes(l_attribute_count).attribute29 := p_attribute_array(l_array_index).attribute29;
2947 l_attributes(l_attribute_count).attribute30 := p_attribute_array(l_array_index).attribute30;
2948 l_attributes(l_attribute_count).Bld_Blk_Info_Type_Id := p_attribute_array(l_array_index).Bld_Blk_Info_Type_Id;
2949 l_attributes(l_attribute_count).Object_Version_Number := p_attribute_array(l_array_index).Object_Version_Number;
2950 l_attributes(l_attribute_count).new := p_attribute_array(l_array_index).new;
2951 l_attributes(l_attribute_count).changed := p_attribute_array(l_array_index).changed;
2952 l_attributes(l_attribute_count).bld_blk_info_type := p_attribute_array(l_array_index).bld_blk_info_type;
2953
2954 l_attribute_count := l_attribute_count + 1;
2955 l_array_index := p_attribute_array.next(l_array_index);
2956 END LOOP;
2957
2958 RETURN l_attributes;
2959 END array_to_attributes;
2960
2961 FUNCTION attributes_to_array(
2962 p_attributes IN hxc_self_service_time_deposit.building_block_attribute_info
2963 )
2964 RETURN HXC_ATTRIBUTE_TABLE_TYPE
2965 IS
2966 l_attribute_array HXC_ATTRIBUTE_TABLE_TYPE;
2967 l_attribute HXC_ATTRIBUTE_TYPE;
2968 l_array_index NUMBER := 0;
2969 l_attribute_index NUMBER;
2970 l_proc VARCHAR2(50);
2971 BEGIN
2972 g_debug:=hr_utility.debug_enabled;
2973 if g_debug then
2974 l_proc := 'attributes_to_array';
2975 hr_utility.set_location ( g_package||l_proc, 10);
2976 end if;
2977 --initialize attribute array
2978 l_attribute_array := HXC_ATTRIBUTE_TABLE_TYPE();
2979
2980 l_attribute_index := p_attributes.first;
2981 LOOP
2982 EXIT WHEN NOT p_attributes.exists(l_attribute_index);
2983
2984 l_array_index := l_array_index + 1;
2985 l_attribute_array.extend;
2986
2987 l_attribute_array(l_array_index) :=
2988 HXC_ATTRIBUTE_TYPE(
2989 p_attributes(l_attribute_index).TIME_ATTRIBUTE_ID
2990 ,p_attributes(l_attribute_index).BUILDING_BLOCK_ID
2991 ,p_attributes(l_attribute_index).ATTRIBUTE_CATEGORY
2992 ,p_attributes(l_attribute_index).ATTRIBUTE1
2993 ,p_attributes(l_attribute_index).ATTRIBUTE2
2994 ,p_attributes(l_attribute_index).ATTRIBUTE3
2995 ,p_attributes(l_attribute_index).ATTRIBUTE4
2996 ,p_attributes(l_attribute_index).ATTRIBUTE5
2997 ,p_attributes(l_attribute_index).ATTRIBUTE6
2998 ,p_attributes(l_attribute_index).ATTRIBUTE7
2999 ,p_attributes(l_attribute_index).ATTRIBUTE8
3000 ,p_attributes(l_attribute_index).ATTRIBUTE9
3001 ,p_attributes(l_attribute_index).ATTRIBUTE10
3002 ,p_attributes(l_attribute_index).ATTRIBUTE11
3003 ,p_attributes(l_attribute_index).ATTRIBUTE12
3004 ,p_attributes(l_attribute_index).ATTRIBUTE13
3005 ,p_attributes(l_attribute_index).ATTRIBUTE14
3006 ,p_attributes(l_attribute_index).ATTRIBUTE15
3007 ,p_attributes(l_attribute_index).ATTRIBUTE16
3008 ,p_attributes(l_attribute_index).ATTRIBUTE17
3009 ,p_attributes(l_attribute_index).ATTRIBUTE18
3010 ,p_attributes(l_attribute_index).ATTRIBUTE19
3011 ,p_attributes(l_attribute_index).ATTRIBUTE20
3012 ,p_attributes(l_attribute_index).ATTRIBUTE21
3013 ,p_attributes(l_attribute_index).ATTRIBUTE22
3014 ,p_attributes(l_attribute_index).ATTRIBUTE23
3015 ,p_attributes(l_attribute_index).ATTRIBUTE24
3016 ,p_attributes(l_attribute_index).ATTRIBUTE25
3017 ,p_attributes(l_attribute_index).ATTRIBUTE26
3018 ,p_attributes(l_attribute_index).ATTRIBUTE27
3019 ,p_attributes(l_attribute_index).ATTRIBUTE28
3020 ,p_attributes(l_attribute_index).ATTRIBUTE29
3021 ,p_attributes(l_attribute_index).ATTRIBUTE30
3022 ,p_attributes(l_attribute_index).BLD_BLK_INFO_TYPE_ID
3023 ,p_attributes(l_attribute_index).OBJECT_VERSION_NUMBER
3024 ,p_attributes(l_attribute_index).NEW
3025 ,p_attributes(l_attribute_index).CHANGED
3026 ,p_attributes(l_attribute_index).BLD_BLK_INFO_TYPE
3027 ,'N'
3028 ,null
3029 );
3030
3031 l_attribute_index := p_attributes.next(l_attribute_index);
3032 END LOOP;
3033
3034 if g_debug then
3035 hr_utility.set_location ( g_package||l_proc, 20);
3036 end if;
3037 RETURN l_attribute_array;
3038 END attributes_to_array;
3039 --
3040 -- Temporary function
3041 --
3042 FUNCTION array_to_blocks(
3043 p_block_array IN OUT NOCOPY HXC_BLOCK_TABLE_TYPE
3044 )
3045 RETURN hxc_self_service_time_deposit.timecard_info
3046 IS
3047 l_array_index NUMBER;
3048 l_blocks hxc_self_service_time_deposit.timecard_info;
3049 BEGIN
3050 l_array_index := p_block_array.first;
3051 LOOP
3052 EXIT WHEN NOT p_block_array.exists(l_array_index);
3053
3054 l_blocks(l_array_index).TIME_BUILDING_BLOCK_ID :=
3055 p_block_array(l_array_index).TIME_BUILDING_BLOCK_ID;
3056 l_blocks(l_array_index).TYPE :=
3057 p_block_array(l_array_index).TYPE;
3058 l_blocks(l_array_index).MEASURE :=
3059 p_block_array(l_array_index).MEASURE;
3060 l_blocks(l_array_index).UNIT_OF_MEASURE :=
3061 p_block_array(l_array_index).UNIT_OF_MEASURE;
3062 l_blocks(l_array_index).START_TIME :=
3063 fnd_date.canonical_to_date(p_block_array(l_array_index).START_TIME);
3064 l_blocks(l_array_index).STOP_TIME :=
3065 fnd_date.canonical_to_date(p_block_array(l_array_index).STOP_TIME);
3066 l_blocks(l_array_index).PARENT_BUILDING_BLOCK_ID :=
3067 p_block_array(l_array_index).PARENT_BUILDING_BLOCK_ID;
3068 l_blocks(l_array_index).PARENT_IS_NEW :=
3069 p_block_array(l_array_index).PARENT_IS_NEW;
3070 l_blocks(l_array_index).SCOPE :=
3071 p_block_array(l_array_index).SCOPE;
3072 l_blocks(l_array_index).OBJECT_VERSION_NUMBER :=
3073 p_block_array(l_array_index).OBJECT_VERSION_NUMBER;
3074 l_blocks(l_array_index).APPROVAL_STATUS :=
3075 p_block_array(l_array_index).APPROVAL_STATUS;
3076 l_blocks(l_array_index).RESOURCE_ID :=
3077 p_block_array(l_array_index).RESOURCE_ID;
3078 l_blocks(l_array_index).RESOURCE_TYPE :=
3079 p_block_array(l_array_index).RESOURCE_TYPE;
3080 l_blocks(l_array_index).APPROVAL_STYLE_ID :=
3081 p_block_array(l_array_index).APPROVAL_STYLE_ID;
3082 l_blocks(l_array_index).DATE_FROM :=
3083 fnd_date.canonical_to_date(p_block_array(l_array_index).DATE_FROM);
3084 l_blocks(l_array_index).DATE_TO :=
3085 fnd_date.canonical_to_date(p_block_array(l_array_index).DATE_TO);
3086 l_blocks(l_array_index).COMMENT_TEXT :=
3087 p_block_array(l_array_index).COMMENT_TEXT;
3088 l_blocks(l_array_index).PARENT_BUILDING_BLOCK_OVN:=
3089 p_block_array(l_array_index).PARENT_BUILDING_BLOCK_OVN;
3090 l_blocks(l_array_index).NEW :=
3091 p_block_array(l_array_index).NEW;
3092 l_blocks(l_array_index).CHANGED :=
3093 p_block_array(l_array_index).CHANGED;
3094
3095 l_array_index := p_block_array.next(l_array_index);
3096 END LOOP;
3097
3098 RETURN l_blocks;
3099 END array_to_blocks;
3100
3101 --
3102 --
3103 FUNCTION blocks_to_array(
3104 p_blocks IN hxc_self_service_time_deposit.timecard_info
3105 )
3106 RETURN HXC_BLOCK_TABLE_TYPE
3107 IS
3108 l_block_array HXC_BLOCK_TABLE_TYPE;
3109 l_array_index NUMBER := 0;
3110 l_block_index NUMBER;
3111 l_proc VARCHAR2(50);
3112 -- l_block HXC_BLOCK_TYPE;
3113
3114 BEGIN
3115 g_debug:=hr_utility.debug_enabled;
3116 if g_debug then
3117 l_proc := 'blocks_to_array';
3118 hr_utility.set_location ( g_package||l_proc, 10);
3119 end if;
3120 l_block_array := HXC_BLOCK_TABLE_TYPE();
3121
3122 l_block_index := p_blocks.first;
3123 LOOP
3124 EXIT WHEN NOT p_blocks.exists(l_block_index);
3125
3126 l_array_index := l_array_index + 1;
3127 l_block_array.extend;
3128
3129 l_block_array(l_array_index) :=
3130 HXC_BLOCK_TYPE(
3131 p_blocks(l_block_index).TIME_BUILDING_BLOCK_ID
3132 ,p_blocks(l_block_index).TYPE
3133 ,p_blocks(l_block_index).MEASURE
3134 ,p_blocks(l_block_index).UNIT_OF_MEASURE
3135 ,fnd_date.date_to_canonical(p_blocks(l_block_index).START_TIME)
3136 ,fnd_date.date_to_canonical(p_blocks(l_block_index).STOP_TIME)
3137 ,p_blocks(l_block_index).PARENT_BUILDING_BLOCK_ID
3138 ,p_blocks(l_block_index).PARENT_IS_NEW
3139 ,p_blocks(l_block_index).SCOPE
3140 ,p_blocks(l_block_index).OBJECT_VERSION_NUMBER
3141 ,p_blocks(l_block_index).APPROVAL_STATUS
3142 ,p_blocks(l_block_index).RESOURCE_ID
3143 ,p_blocks(l_block_index).RESOURCE_TYPE
3144 ,p_blocks(l_block_index).APPROVAL_STYLE_ID
3145 ,fnd_date.date_to_canonical(p_blocks(l_block_index).DATE_FROM)
3146 ,fnd_date.date_to_canonical(p_blocks(l_block_index).DATE_TO)
3147 ,p_blocks(l_block_index).COMMENT_TEXT
3148 ,p_blocks(l_block_index).PARENT_BUILDING_BLOCK_OVN
3149 ,p_blocks(l_block_index).NEW
3150 ,p_blocks(l_block_index).CHANGED
3151 ,'N'
3152 ,p_blocks(l_block_index).application_set_id
3153 ,p_blocks(l_block_index).TRANSLATION_DISPLAY_KEY --Bug 5565773
3154 );
3155
3156 l_block_index := p_blocks.next(l_block_index);
3157 END LOOP;
3158
3159 if g_debug then
3160 hr_utility.set_location ( g_package||l_proc, 140);
3161 end if;
3162 RETURN l_block_array;
3163 END blocks_to_array;
3164
3165 PROCEDURE maintain_errors (
3166 p_translated_bb_ids_tab hxc_self_service_time_deposit.translate_bb_ids_tab
3167 , p_translated_ta_ids_tab hxc_self_service_time_deposit.translate_ta_ids_tab
3168 , p_messages IN OUT NOCOPY hxc_self_service_time_deposit.message_table
3169 , p_transactions IN OUT NOCOPY hxc_deposit_wrapper_utilities.t_transaction ) IS
3170
3171 l_msg_ind BINARY_INTEGER;
3172 l_tbb_id NUMBER;
3173 l_ta_id NUMBER;
3174
3175 l_proc varchar2(72);
3176
3177 cursor c_max_ovn
3178 (p_tbb_id in number) is
3179 select max(object_version_number)
3180 from hxc_time_building_blocks
3181 where time_building_block_id = p_tbb_id;
3182
3183 cursor c_tx_id
3184 (p_tbb_id in number,
3185 p_tbb_ovn in number) is
3186 select transaction_id
3187 from hxc_transaction_details
3188 where time_building_block_id = p_tbb_id
3189 and object_version_number = p_tbb_ovn;
3190
3191
3192 TYPE r_tbb_vs_txd IS RECORD ( txd_id hxc_transaction_details.transaction_detail_id%TYPE );
3193
3194 TYPE t_tbb_vs_txd IS TABLE OF r_tbb_vs_txd INDEX BY BINARY_INTEGER;
3195
3196 t_tbb_vs_txds t_tbb_vs_txd;
3197
3198 l_tx_ind BINARY_INTEGER;
3199
3200 l_tx_id NUMBER;
3201 l_tbb_ovn NUMBER;
3202
3203 BEGIN
3204 if g_debug then
3205 l_proc := g_package||'.maintain_errors';
3206 hr_utility.trace('transaction details are ');
3207 end if;
3208 /*
3209 FOR x in p_transactions.FIRST .. p_transactions.LAST
3210 LOOP
3211 if g_debug then
3212 hr_utility.trace('txd tbb id is '||to_char(p_transactions(x).tbb_id));
3213 hr_utility.trace('txd txd id is '||to_char(p_transactions(x).txd_id));
3214 end if;
3215 END LOOP;
3216
3217 if g_debug then
3218 hr_utility.trace('');
3219 hr_utility.trace('messages are ');
3220 end if;
3221
3222 FOR x in p_messages.FIRST .. p_messages.LAST
3223 LOOP
3224 if g_debug then
3225 hr_utility.trace('message name is '||p_messages(x).message_name);
3226 hr_utility.trace('tbb id is '||to_char(p_messages(x).time_building_block_id));
3227 hr_utility.trace('tbb ovn is '||to_char(p_messages(x).time_building_block_ovn));
3228 end if;
3229 END LOOP;
3230 */
3231
3232 if g_debug then
3233 hr_utility.set_location('Entering '||l_proc, 10);
3234 end if;
3235
3236 -- parse the transaction table to produce a mapping of time building blocks
3237 -- to transaction details id- this will save traversing the table for every message
3238
3239 -- GPM v115.32
3240
3241 l_tx_ind := p_transactions.FIRST;
3242
3243 WHILE l_tx_ind IS NOT NULL
3244 LOOP
3245
3246 if g_debug then
3247 hr_utility.set_location('Processing '||l_proc, 20);
3248 end if;
3249
3250 t_tbb_vs_txds(p_transactions(l_tx_ind).tbb_id).txd_id := p_transactions(l_tx_ind).txd_id;
3251
3252 l_tx_ind := p_transactions.NEXT(l_tx_ind);
3253
3254 END LOOP;
3255
3256 if g_debug then
3257 hr_utility.set_location('Processing '||l_proc, 30);
3258 end if;
3259
3260 -- if the timecard has just been inserted need to map dummy ids to new ids
3261
3262 l_msg_ind := p_messages.FIRST;
3263
3264 WHILE ( l_msg_ind IS NOT NULL )
3265 LOOP
3266 if g_debug then
3267 hr_utility.set_location('Processing '||l_proc, 40);
3268 end if;
3269 -- assign to variables (makes it easier to read the following code)
3270
3271 l_tbb_id := p_messages(l_msg_ind).time_building_block_id;
3272 l_ta_id := p_messages(l_msg_ind).time_attribute_id;
3273
3274 IF ( ( l_tbb_id IS NOT NULL ) AND ( p_translated_bb_ids_tab.COUNT <> 0 ) )
3275 THEN
3276 if g_debug then
3277 hr_utility.set_location('Processing '||l_proc, 50);
3278 end if;
3279 -- this may not be a new building block
3280
3281 IF ( p_translated_bb_ids_tab.EXISTS(l_tbb_id) )
3282 THEN
3283 if g_debug then
3284 hr_utility.set_location('Processing '||l_proc, 60);
3285 end if;
3286 p_messages(l_msg_ind).time_building_block_id :=
3287 p_translated_bb_ids_tab(l_tbb_id).actual_bb_id;
3288
3289 -- set the object version number back to 1
3290 -- remember it would have been set to ovn+1 in add_error_to_Table
3291
3292 p_messages(l_msg_ind).time_building_block_ovn := 1;
3293
3294 END IF;
3295
3296 END IF;
3297
3298 if g_debug then
3299 hr_utility.set_location('Processing '||l_proc, 70);
3300 end if;
3301 -- now the attribute
3302
3303 IF ( ( l_ta_id IS NOT NULL ) AND ( p_translated_ta_ids_tab.COUNT <> 0 ) )
3304 THEN
3305
3306 IF ( p_translated_ta_ids_tab.EXISTS(l_ta_id) )
3307 THEN
3308
3309 p_messages(l_msg_ind).time_attribute_id :=
3310 p_translated_ta_ids_tab(l_ta_id).actual_ta_id;
3311
3312 END IF;
3313
3314 END IF;
3315
3316 if g_debug then
3317 hr_utility.set_location('Processing '||l_proc, 75);
3318 end if;
3319 -- find the ovn of the tbb
3320 OPEN c_max_ovn(p_messages(l_msg_ind).time_building_block_id);
3321 FETCH c_max_ovn INTO l_tbb_ovn;
3322 CLOSE c_max_ovn;
3323
3324 --find the transaction_id from the tbb
3325 OPEN c_tx_id(p_messages(l_msg_ind).time_building_block_id,l_tbb_ovn);
3326 FETCH c_tx_id INTO l_tx_id;
3327 CLOSE c_tx_id;
3328
3329 IF (l_tx_id is null) THEN
3330 l_tx_id := -1;
3331 END IF;
3332
3333 INSERT INTO hxc_errors (
3334 error_id
3335 , transaction_detail_id
3336 , time_building_block_id
3337 , time_building_block_ovn
3338 , time_attribute_id
3339 , time_attribute_ovn
3340 , message_name
3341 , message_level
3342 , message_field
3343 , message_tokens
3344 , application_short_name
3345 , object_version_number )
3346 VALUES (
3347 hxc_errors_s.nextval
3348 , l_tx_id
3349 , p_messages(l_msg_ind).time_building_block_id
3350 , l_tbb_ovn
3351 , p_messages(l_msg_ind).time_attribute_id
3352 , p_messages(l_msg_ind).time_attribute_ovn
3353 , p_messages(l_msg_ind).message_name
3354 , p_messages(l_msg_ind).message_level
3355 , p_messages(l_msg_ind).message_field
3356 , p_messages(l_msg_ind).message_tokens
3357 , p_messages(l_msg_ind).application_short_name
3358 , 1 );
3359
3360
3361 l_msg_ind := p_messages.NEXT(l_msg_ind);
3362
3363 END LOOP;
3364
3365 if g_debug then
3366 hr_utility.set_location('Leaving '||l_proc, 80);
3367 end if;
3368 END maintain_errors;
3369
3370 /*This function obtains the PAEXPITDFF code from the PAEXPITDFF name.
3371 First if the name is present in the g_code_name_tab cache, then the
3372 corresponding code is fetched, else the corresponding code is fetched
3373 from the database table */
3374 FUNCTION get_dupdff_code(p_dupdff_name IN VARCHAR2) return varchar2
3375
3376 IS
3377 CURSOR get_code(p_name VARCHAR2,P_MESSAGE VARCHAR2)
3378 IS
3379 SELECT descriptive_flex_context_code
3380 FROM fnd_descr_flex_contexts_vl
3381 WHERE descriptive_flex_context_name = p_name
3382 AND descriptive_flexfield_name = 'OTC Information Types'
3383 AND application_id = 809
3384 AND substrB(DESCRIPTIVE_FLEX_CONTEXT_CODE,0,instr(DESCRIPTIVE_FLEX_CONTEXT_CODE,'-')-2)
3385 =substrB(DESCRIPTIVE_FLEX_CONTEXT_name,0,instr(DESCRIPTIVE_FLEX_CONTEXT_name,'-')-2)||'C'
3386 AND SUBSTRB(DESCRIPTION,0, LENGTH(P_MESSAGE))=P_MESSAGE;
3387
3388 l_index NUMBER := 0;
3389 l_code fnd_descr_flex_contexts_vl.descriptive_flex_context_code%TYPE;
3390 l_table_index NUMBER;
3391 l_message varchar2(100);
3392 BEGIN
3393 l_code := NULL;
3394 l_index := g_code_name_tab.FIRST;
3395
3396 LOOP
3397 EXIT WHEN NOT g_code_name_tab.EXISTS(l_index);
3398 -- DBMS_OUTPUT.put_line(g_code_name_tab(l_index).dupdff_name);
3399 -- DBMS_OUTPUT.put_line(g_code_name_tab(l_index).dupdff_code);
3400
3401 IF g_code_name_tab(l_index).dupdff_name = p_dupdff_name
3402 THEN
3403 l_code := g_code_name_tab(l_index).dupdff_code;
3404 EXIT;
3405 END IF;
3406
3407 l_index := g_code_name_tab.NEXT(l_index);
3408 END LOOP;
3409
3410 IF l_code IS NULL
3411 THEN
3412
3413 hr_utility.set_message(809,'HXC_DFF_SYSTEM_CONTEXT');
3414 l_message := hr_utility.get_message;
3415 OPEN get_code(p_dupdff_name,l_message);
3416 FETCH get_code INTO l_code;
3417
3418 if l_code is null then
3419 CLOSE get_code;
3420 return(p_dupdff_name);
3421
3422 else
3423 IF g_code_name_tab.count > 0
3424 THEN
3425 l_table_index := g_code_name_tab.count + 1;
3426 ELSE
3427 l_table_index := 1;
3428 END IF;
3429
3430 g_code_name_tab(l_table_index).dupdff_code := l_code;
3431 g_code_name_tab(l_table_index).dupdff_name := p_dupdff_name;
3432
3433 CLOSE get_code;
3434 end if;
3435 END IF;
3436
3437 RETURN (l_code);
3438 END;
3439
3440 /*This function obtains the PAEXPITDFF Context Name from the PAEXPITDFF Context Code.
3441 First if the Name is present in the g_code_name_tab cache, then the corresponding
3442 code is fetched, else the corresponding code is fetched from the database table */
3443
3444
3445 FUNCTION get_dupdff_name (p_dupdff_code IN VARCHAR2)
3446 RETURN VARCHAR2
3447 IS
3448 CURSOR get_name (p_code VARCHAR2, p_message VARCHAR2)
3449 IS
3450 SELECT descriptive_flex_context_name
3451 FROM fnd_descr_flex_contexts_vl
3452 WHERE descriptive_flex_context_code = p_code
3453 AND descriptive_flexfield_name = 'OTC Information Types'
3454 AND application_id = 809
3455 AND SUBSTRB (
3456 descriptive_flex_context_code,
3457 0,
3458 INSTR (descriptive_flex_context_code, '-') - 2
3459 ) = SUBSTRB (
3460 descriptive_flex_context_name,
3461 0,
3462 INSTR (descriptive_flex_context_name, '-') - 2
3463 )
3464 || 'C'
3465 AND SUBSTRB (description, 0, LENGTH (p_message)) = p_message;
3466
3467 l_index NUMBER := 0;
3468 l_name fnd_descr_flex_contexts_vl.descriptive_flex_context_name%TYPE;
3469 l_message VARCHAR2 (100);
3470 BEGIN
3471 l_name := NULL;
3472
3473 l_index := TO_NUMBER (
3474 SUBSTR (p_dupdff_code, INSTR (p_dupdff_code, '-') + 2)
3475 );
3476 IF g_code_name_tab.EXISTS(L_INDEX) then
3477 l_name := g_code_name_tab (l_index).dupdff_name;
3478 RETURN(l_name);
3479
3480 ELSE
3481
3482 hr_utility.set_message (809, 'HXC_DFF_SYSTEM_CONTEXT');
3483 l_message := hr_utility.GET_MESSAGE;
3484 OPEN get_name (p_dupdff_code, l_message);
3485 FETCH get_name INTO l_name;
3486
3487 IF l_name IS NULL
3488 THEN
3489 CLOSE get_name;
3490 RETURN (p_dupdff_code);
3491 ELSE
3492 g_code_name_tab (l_index).dupdff_name := l_name;
3493 g_code_name_tab (l_index).dupdff_code := p_dupdff_code;
3494 CLOSE get_name;
3495 END IF;
3496 END IF;
3497 RETURN(l_name);
3498 EXCEPTION
3499 WHEN OTHERS THEN
3500 RETURN (p_dupdff_code);
3501 END;
3502
3503 function timecard_hours_type_list( p_resource_id in varchar2,
3504 p_start_time in varchar2,
3505 p_stop_time in varchar2,
3506 p_alias_or_element_id in varchar2,
3507 p_aliases in VARCHAR2,
3508 p_public_template in varchar2) return varchar2
3509
3510 is
3511 -- Bug 7359347
3512 -- Added the hint to avoid hard parsing
3513 cursor cur_hours_type(p_alias_definition_id IN VARCHAR2) is
3514 SELECT /*+ OPTIMIZER_FEATURES_ENABLE('9.2.0') */
3515 havt.alias_value_name Display_Value,
3516 hav.attribute1 element_id,
3517 hav.alias_value_id alias_value_id
3518 FROM
3519 hxc_alias_values hav,
3520 hxc_alias_values_tl havt,
3521 hxc_alias_definitions had,
3522 PAY_ELEMENT_TYPES_F ELEMENT
3523 WHERE
3524 hav.attribute1 = ELEMENT.element_type_id and
3525 hav.enabled_flag='Y' and
3526 had.alias_definition_id = hav.alias_definition_id and
3527 havt.language = USERENV('LANG') and
3528 havt.alias_value_id =hav.alias_value_id and
3529 had.alias_definition_id = p_alias_definition_id AND
3530 ELEMENT.EFFECTIVE_START_DATE <= sysdate AND
3531 ELEMENT.EFFECTIVE_END_DATE >= sysdate;
3532
3533 l_hours_type_list varchar2(32000) :=null;
3534 l_id_string varchar2(15) :=null;
3535 l_start number;
3536 l_index number;
3537 l_alias varchar2(10);
3538 l_aliases varchar2(1000);
3539 begin
3540
3541
3542 l_aliases :=p_aliases;
3543 if p_public_template = 'Y' then
3544
3545 IF ( p_aliases IS NOT NULL )
3546 THEN
3547 l_start := 1;
3548 l_index :=1;
3549 while(l_index <> 0)
3550 loop
3551 l_index := instr(l_aliases,',',l_start);
3552 l_alias := substr(l_aliases,l_start,(l_index-1));
3553 l_aliases := substr(l_aliases,(l_index+1));
3554 l_start := 1;
3555
3556 if(l_alias is not null) then
3557 FOR l_hours_type in cur_hours_type(l_alias) LOOP
3558
3559 IF (p_alias_or_element_id = 'ALIAS') THEN
3560 l_id_string := l_hours_type.alias_value_id;
3561 ELSIF (p_alias_or_element_id = 'ELEMENT') THEN
3562 l_id_string := l_hours_type.element_id;
3563 END IF;
3564 l_hours_type_list := l_hours_type_list
3565 ||l_hours_type.display_value
3566 ||'|'
3567 ||l_id_string
3568 ||'|';
3569 END LOOP;
3570 end if;
3571 end loop;
3572 END IF;
3573
3574 ELSE
3575
3576 l_hours_type_list := timecard_hours_type_list(p_resource_id,
3577 p_start_time,
3578 p_stop_time,
3579 p_alias_or_element_id);
3580
3581 END IF;
3582 return l_hours_type_list;
3583 END timecard_hours_type_list;
3584
3585 -- Added a new procedure which would replace the resource_id in case of
3586 -- duplication of public templates.
3587
3588 procedure replace_resource_id (p_blocks IN OUT NOCOPY HXC_BLOCK_TABLE_TYPE,
3589 p_resource_id IN hxc_time_building_blocks.resource_id%type) is
3590 l_block_index number;
3591 begin
3592
3593 if (p_blocks.count>0) then
3594 l_block_index := p_blocks.first;
3595 if(p_blocks(l_block_index).resource_id = p_resource_id) then
3596 return; -- If the resourceids are same, then we dont need to change.
3597 end if;
3598 LOOP
3599 EXIT WHEN NOT p_blocks.EXISTS(l_block_index);
3600
3601 p_blocks(l_block_index).resource_id :=p_resource_id;
3602
3603 l_block_index := p_blocks.next(l_block_index);
3604 END LOOP;
3605 end if;
3606
3607 end replace_resource_id;
3608
3609 END hxc_deposit_wrapper_utilities;
3610