[Home] [Help]
PACKAGE BODY: APPS.HXC_DEPOSIT_WRAPPER_UTILITIES
Source
1 PACKAGE BODY hxc_deposit_wrapper_utilities AS
2 /* $Header: hxcdpwrut.pkb 120.12.12010000.2 2008/10/06 11:54:34 anuthi 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_TRANSACTIONS
2220 --
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 insert into hxc_transactions
2254 (transaction_id
2255 ,transaction_date
2256 ,type
2257 ,transaction_process_id
2258 ,created_by
2259 ,creation_date
2260 ,last_updated_by
2261 ,last_update_date
2262 ,last_update_login
2263 ,status
2264 ) values
2265 (l_transaction_id
2266 ,p_effective_date
2267 ,p_transaction_type
2268 ,p_transaction_process_id
2269 ,null
2270 ,sysdate
2271 ,null
2272 ,sysdate
2273 ,null
2274 ,p_overall_status
2275 );
2276
2277 l_tx_ind := p_transaction_tab.FIRST;
2278
2279 WHILE ( l_tx_ind IS NOT NULL )
2280 LOOP
2281
2282 open c_transaction_detail_sequence;
2283 fetch c_transaction_detail_sequence into l_transaction_detail_id;
2284 close c_transaction_detail_sequence;
2285
2286 insert into hxc_transaction_details
2287 (transaction_detail_id
2288 ,time_building_block_id
2289 ,transaction_id
2290 ,created_by
2291 ,creation_date
2292 ,last_updated_by
2293 ,last_update_date
2294 ,last_update_login
2295 ,time_building_block_ovn
2296 ,status
2297 ,exception_description
2298 ) values
2299 (l_transaction_detail_id
2300 ,p_transaction_tab(l_tx_ind).tbb_id
2301 ,l_transaction_id
2302 ,null
2303 ,sysdate
2304 ,null
2305 ,sysdate
2306 ,null
2307 ,p_transaction_tab(l_tx_ind).tbb_ovn
2308 ,p_transaction_tab(l_tx_ind).status
2309 ,p_transaction_tab(l_tx_ind).exception_desc
2310 );
2311
2312 p_transaction_tab(l_tx_ind).txd_id := l_transaction_detail_id;
2313
2314 l_tx_ind := p_transaction_tab.NEXT(l_tx_ind);
2315
2316 END LOOP;
2317
2318 commit;
2319
2320 end audit_transaction;
2321
2322 ----
2323 -- Function returning a list of hours types and ids for use on the timecard
2324 ----
2325
2326 function timecard_hours_type_list( p_resource_id in varchar2,
2327 p_start_time in varchar2,
2328 p_stop_time in varchar2,
2329 p_alias_or_element_id in varchar2) return varchar2
2330
2331 is
2332
2333 TYPE t_hours_type_list_row is RECORD (
2334 element_id NUMBER,
2335 alias_value_id NUMBER,
2336 display_value hxc_alias_values.alias_value_name%TYPE
2337 );
2338
2339 TYPE t_hours_type_list is table of
2340 t_hours_type_list_row
2341 index by binary_integer;
2342
2343 l_index NUMBER;
2344 l_hrs_typ_index NUMBER;
2345 l_loop_count NUMBER;
2346 l_hours_type_list t_hours_type_list;
2347 l_aliases hxc_alias_utility.t_alias_def_item;
2348 l_id_string VARCHAR2(30); -- this is used to store NUMBER(15)
2349 l_ht_list_string VARCHAR2(32000);
2350 l_resource_id NUMBER;
2351 l_start_time DATE;
2352 l_stop_time DATE;
2353 l_time_diff number := 0;
2354 -- Adding v115.53 Fix for Bug. 3161167
2355 -- Adding new variables used for Insertion Sorting implementation.
2356 i NUMBER;
2357 j NUMBER;
2358 l_hours_type_list_ins_alg t_hours_type_list;
2359 l_display_value_ins_alg hxc_alias_values.alias_value_name%TYPE;
2360
2361
2362 cursor csr_hours_type (p_alias_definition_id number,
2363 p_start_date date,
2364 p_end_date date,
2365 p_person_id number)
2366 is
2367 select havt.alias_value_name Display_Value,
2368 hav.attribute1 element_id,
2369 hav.alias_value_id alias_value_id
2370 from hxc_alias_values hav,
2371 hxc_alias_values_tl havt,
2372 hxc_alias_definitions had
2373 where
2374 --hav.attribute_category='PAYROLL_ELEMENTS'
2375 hav.enabled_flag='Y'
2376 and had.alias_definition_id = hav.alias_definition_id
2377 and had.alias_definition_id = p_alias_definition_id
2378 and havt.language = USERENV('LANG')
2379 and havt.alias_value_id = hav.alias_value_id
2380 and hav.date_from <= p_end_date
2381 and nvl(hav.date_to,hr_general.end_of_time) >=p_start_date
2382 and exists (
2383 select 'x'
2384 from PAY_ELEMENT_TYPES_F ELEMENT,
2385 PAY_ELEMENT_CLASSIFICATIONS CLASSIFICATION,
2386 BEN_BENEFIT_CLASSIFICATIONS BENEFIT,
2387 PAY_ELEMENT_LINKS_F LINK,
2388 PER_ALL_ASSIGNMENTS_F ASGT,
2389 PER_PERIODS_OF_SERVICE SERVICE_PERIOD
2390 WHERE
2391 asgt.person_id = p_person_id and
2392 to_number(hav.attribute1) = ELEMENT.element_type_id
2393 AND ELEMENT.EFFECTIVE_START_DATE <= p_end_date
2394 AND ELEMENT.EFFECTIVE_END_DATE >= p_start_date
2395 AND ASGT.BUSINESS_GROUP_ID = LINK.BUSINESS_GROUP_ID
2396 AND ELEMENT.ELEMENT_TYPE_ID = LINK.ELEMENT_TYPE_ID
2397 AND ELEMENT.BENEFIT_CLASSIFICATION_ID = BENEFIT.BENEFIT_CLASSIFICATION_ID (+)
2398 AND ELEMENT.CLASSIFICATION_ID = CLASSIFICATION.CLASSIFICATION_ID
2399 AND SERVICE_PERIOD.PERIOD_OF_SERVICE_ID = ASGT.PERIOD_OF_SERVICE_ID
2400 AND ASGT.EFFECTIVE_START_DATE <= p_end_date
2401 AND ASGT.EFFECTIVE_END_DATE >= p_start_date
2402 AND LINK.EFFECTIVE_START_DATE <= p_end_date
2403 AND LINK.EFFECTIVE_END_DATE >= p_start_date
2404 AND ELEMENT.INDIRECT_ONLY_FLAG = 'N'
2405 AND UPPER (ELEMENT.ELEMENT_NAME) <> 'VERTEX'
2406 AND not exists
2407 (select 1
2408 from HR_ORGANIZATION_INFORMATION HOI,
2409 PAY_LEGISLATION_RULES PLR
2410 WHERE plr.rule_type in
2411 ('ADVANCE','ADVANCE_INDICATOR','ADV_DEDUCTION',
2412 'PAY_ADVANCE_INDICATOR','ADV_CLEARUP','DEFER_PAY')
2413 AND plr.rule_mode = to_char(element.element_type_id)
2414 AND plr.legislation_code = hoi.org_information9
2415 AND HOI.ORGANIZATION_ID = ASGT.ORGANIZATION_ID
2416 )
2417 AND ELEMENT.CLOSED_FOR_ENTRY_FLAG = 'N'
2418 AND ELEMENT.ADJUSTMENT_ONLY_FLAG = 'N'
2419 AND ((LINK.PAYROLL_ID IS NOT NULL AND LINK.PAYROLL_ID = ASGT.PAYROLL_ID)
2420 OR (LINK.LINK_TO_ALL_PAYROLLS_FLAG = 'Y' AND ASGT.PAYROLL_ID IS NOT NULL)
2421 OR (LINK.PAYROLL_ID IS NULL AND LINK.LINK_TO_ALL_PAYROLLS_FLAG = 'N'))
2422 AND (LINK.ORGANIZATION_ID = ASGT.ORGANIZATION_ID OR LINK.ORGANIZATION_ID IS NULL)
2423 AND (LINK.POSITION_ID = ASGT.POSITION_ID OR LINK.POSITION_ID IS NULL)
2424 AND (LINK.JOB_ID = ASGT.JOB_ID OR LINK.JOB_ID IS NULL)
2425 AND (LINK.GRADE_ID = ASGT.GRADE_ID OR LINK.GRADE_ID IS NULL)
2426 AND (LINK.LOCATION_ID = ASGT.LOCATION_ID OR LINK.LOCATION_ID IS NULL)
2427 AND (LINK.PAY_BASIS_ID = ASGT.PAY_BASIS_ID OR LINK.PAY_BASIS_ID IS NULL)
2428 AND (LINK.EMPLOYMENT_CATEGORY = ASGT.EMPLOYMENT_CATEGORY OR
2429 LINK.EMPLOYMENT_CATEGORY IS NULL)
2430 AND (LINK.PEOPLE_GROUP_ID IS NULL
2431 OR EXISTS (
2432 SELECT 1 FROM PAY_ASSIGNMENT_LINK_USAGES_F USAGE
2433 WHERE USAGE.ASSIGNMENT_ID = ASGT.ASSIGNMENT_ID
2434 AND USAGE.ELEMENT_LINK_ID = LINK.ELEMENT_LINK_ID
2435 AND (USAGE.EFFECTIVE_START_DATE <= p_end_date
2436 AND USAGE.EFFECTIVE_END_DATE >= p_start_date)))
2437 AND (ELEMENT.PROCESSING_TYPE = 'R' OR ASGT.PAYROLL_ID IS NOT NULL)
2438 AND (SERVICE_PERIOD.ACTUAL_TERMINATION_DATE IS NULL
2439 OR (SERVICE_PERIOD.ACTUAL_TERMINATION_DATE IS NOT NULL
2440 AND p_start_date <= DECODE(ELEMENT.POST_TERMINATION_RULE,
2441 'L', NVL(SERVICE_PERIOD.LAST_STANDARD_PROCESS_DATE,hr_general.end_of_time),
2442 'F', NVL(SERVICE_PERIOD.FINAL_PROCESS_DATE,
2443 hr_general.end_of_time),
2444 SERVICE_PERIOD.ACTUAL_TERMINATION_DATE))))
2445 ORDER BY Display_Value;
2446
2447
2448 BEGIN
2449
2450 g_debug:=hr_utility.debug_enabled;
2451 l_time_diff := 24*60*(sysdate - g_ht_time);
2452
2453 -- check to see if we can use a cached result
2454
2455 IF l_time_diff < 5 then
2456
2457 if( g_ht_resource_id = p_resource_id AND
2458 g_ht_start_time = p_start_time AND
2459 g_ht_stop_time = p_stop_time AND
2460 g_ht_alias_or_element_id = p_alias_or_element_id AND
2461 g_ht_resp_id = fnd_global.resp_id )
2462 THEN
2463
2464 return g_hours_type_list;
2465 END IF;
2466
2467 END IF; --- time diff testing
2468
2469
2470 -- if not able to use cached result, need to generate. Store params so that we could cache next time
2471
2472 g_ht_resource_id := p_resource_id;
2473 g_ht_start_time := p_start_time;
2474 g_ht_stop_time := p_stop_time;
2475 g_ht_alias_or_element_id := p_alias_or_element_id;
2476 g_ht_resp_id := fnd_global.resp_id;
2477 g_ht_time := sysdate;
2478
2479
2480
2481 -- convert params
2482 l_resource_id := p_resource_id;
2483 --l_start_time := FND_DATE.CANONICAL_TO_DATE(p_start_time);
2484 --l_stop_time := FND_DATE.CANONICAL_TO_DATE(p_stop_time);
2485
2486 -- get list of valid aliases for this period
2487
2488 IF (p_alias_or_element_id = 'ALIAS'
2489 OR p_alias_or_element_id = 'ELEMENT') THEN
2490
2491 l_aliases := HXC_ALIAS_UTILITY.get_list_alias_id(p_alias_type => 'PAYROLL_ELEMENTS'
2492 ,p_start_time => p_start_time
2493 ,p_stop_time => p_stop_time
2494 ,p_resource_id => l_resource_id );
2495
2496 ELSE
2497
2498
2499 l_aliases := HXC_ALIAS_UTILITY.get_list_alias_id(p_alias_type => p_alias_or_element_id
2500 ,p_start_time => p_start_time
2501 ,p_stop_time => p_stop_time
2502 ,p_resource_id => l_resource_id );
2503
2504 --if g_debug then
2505 --hr_utility.trace('p_alias_or_element_id '||p_alias_or_element_id);
2506 --end if;
2507
2508 END IF;
2509
2510
2511
2512 -- pull out TC_W_TCRD_ALIASES values
2513
2514 l_index:=l_aliases.FIRST;
2515 l_start_time := FND_DATE.CANONICAL_TO_DATE(p_start_time);
2516 l_stop_time := FND_DATE.CANONICAL_TO_DATE(p_stop_time);
2517
2518 -- first check to see that aliases have been setup for this person!!!
2519 -- v115.23
2520
2521 IF ( l_index IS NOT NULL )
2522 THEN
2523
2524 LOOP
2525
2526 -- execute cursor against each of these values. Shouldnt be too slow since
2527 -- SQL cache should save us some time. Also, this will only execute more than
2528 -- once if the alias changes mid period, which will not be the most common case
2529
2530 --dbms_output.put_line('Alais defn id:'||l_pref_table(l_index).attribute1);
2531
2532 FOR l_hours_type in csr_hours_type(p_person_id => l_resource_id,
2533 p_start_date => l_aliases(l_index).pref_start_date,
2534 p_end_date => l_aliases(l_index).pref_end_date,
2535 p_alias_definition_id => l_aliases(l_index).alias_definition_id) LOOP
2536
2537 -- Add values to list . Index list by alias_value_id thus removing duplicates automatically
2538
2539 --dbms_output.put_line('Element ID:'||pot_value.element_id);
2540 --dbms_output.put_line('Alias Value id:'||pot_value.alias_value_id);
2541 --dbms_output.put_line('Display_name:'||pot_value.display_value);
2542
2543 l_hours_type_list(l_hours_type.alias_value_id).element_id := l_hours_type.element_id;
2544 l_hours_type_list(l_hours_type.alias_value_id).alias_value_id := l_hours_type.alias_value_id;
2545 l_hours_type_list(l_hours_type.alias_value_id).display_value := l_hours_type.display_value;
2546
2547 END LOOP;
2548
2549 EXIT WHEN l_loop_count > 5000;
2550
2551 l_loop_count := l_loop_count+1;
2552 l_index := l_aliases.NEXT(l_index);
2553
2554 EXIT WHEN NOT l_aliases.EXISTS(l_index);
2555
2556 END LOOP;
2557
2558 -- Adding v115.56 fix for bug. 3264226
2559 l_hours_type_list_ins_alg := l_hours_type_list;
2560 l_hours_type_list.DELETE;
2561
2562 l_hrs_typ_index := 1;
2563 l_index := null;
2564 l_index := l_hours_type_list_ins_alg.first;
2565 while l_index is not null
2566 loop
2567 l_hours_type_list(l_hrs_typ_index).element_id := l_hours_type_list_ins_alg(l_index).element_id;
2568 l_hours_type_list(l_hrs_typ_index).alias_value_id := l_hours_type_list_ins_alg(l_index).alias_value_id;
2569 l_hours_type_list(l_hrs_typ_index).display_value := l_hours_type_list_ins_alg(l_index).display_value;
2570
2571 l_index := l_hours_type_list_ins_alg.NEXT(l_index);
2572 l_hrs_typ_index := l_hrs_typ_index + 1;
2573 end loop;
2574 l_hours_type_list_ins_alg.DELETE;
2575
2576
2577 -- Adding v115.53 Fix for Bug. 3161167
2578 -- Adding new Insertion sort Algorithm
2579 IF ( l_hours_type_list.COUNT > 0) Then
2580 For i in l_hours_type_list.First+1..l_hours_type_list.LAST
2581 Loop
2582 l_display_value_ins_alg:=l_hours_type_list(i).display_value;
2583 l_hours_type_list_ins_alg(1) := l_hours_type_list(i);
2584 <<inner_loop>>
2585 For j in REVERSE l_hours_type_list.First.. (i-1)
2586 Loop
2587 If l_hours_type_list(j).display_value >= l_display_value_ins_alg then
2588 l_hours_type_list(j+1):=l_hours_type_list(j);
2589 l_hours_type_list(j):=l_hours_type_list_ins_alg(1);
2590 end if;
2591 end loop inner_loop;
2592 end loop;
2593 End If;
2594 -- End sorting logic
2595 -- now compile string according to callers choice
2596
2597 l_index:=l_hours_type_list.FIRST;
2598
2599 if (l_index is not null) then
2600
2601 LOOP
2602
2603 IF (p_alias_or_element_id = 'ALIAS') THEN
2604 l_id_string := l_hours_type_list(l_index).alias_value_id;
2605 ELSIF (p_alias_or_element_id = 'ELEMENT') THEN
2606 l_id_string := l_hours_type_list(l_index).element_id;
2607 ELSE
2608 l_id_string := l_hours_type_list(l_index).alias_value_id;
2609 END IF;
2610
2611 l_ht_list_string := l_ht_list_string
2612 ||l_hours_type_list(l_index).display_value
2613 ||'|'
2614 ||l_id_string
2615 ||'|';
2616
2617 EXIT WHEN l_loop_count > 5000;
2618
2619 l_loop_count := l_loop_count+1;
2620 l_index := l_hours_type_list.NEXT(l_index);
2621
2622 EXIT when not l_hours_type_list.EXISTS(l_index);
2623
2624 END LOOP;
2625
2626
2627 g_hours_type_list := substr(l_ht_list_string,1,length(l_ht_list_string)-1);
2628 RETURN g_hours_type_list;
2629
2630 else
2631
2632 return null;
2633
2634 end if;
2635
2636 ELSE -- l_index for alias list is null v115.23
2637
2638 return null;
2639
2640 END IF;
2641
2642 END timecard_hours_type_list;
2643
2644 --
2645 --
2646 FUNCTION array_to_attributes(
2647 p_attribute_array IN HXC_ATTRIBUTE_TABLE_TYPE
2648 )
2649 RETURN hxc_self_service_time_deposit.building_block_attribute_info
2650 IS
2651 l_array_index NUMBER;
2652 l_attribute_count NUMBER := 1;
2653 l_attributes hxc_self_service_time_deposit.building_block_attribute_info;
2654 BEGIN
2655 l_array_index := p_attribute_array.first;
2656 LOOP
2657 EXIT WHEN NOT p_attribute_array.exists(l_array_index);
2658
2659 l_attributes(l_attribute_count).time_attribute_id := p_attribute_array(l_array_index).time_attribute_id;
2660 l_attributes(l_attribute_count).building_block_id := p_attribute_array(l_array_index).building_block_id;
2661 l_attributes(l_attribute_count).Attribute_Category := p_attribute_array(l_array_index).Attribute_Category;
2662 l_attributes(l_attribute_count).attribute1 := p_attribute_array(l_array_index).attribute1;
2663 l_attributes(l_attribute_count).attribute2 := p_attribute_array(l_array_index).attribute2;
2664 l_attributes(l_attribute_count).attribute3 := p_attribute_array(l_array_index).attribute3;
2665 l_attributes(l_attribute_count).attribute4 := p_attribute_array(l_array_index).attribute4;
2666 l_attributes(l_attribute_count).attribute5 := p_attribute_array(l_array_index).attribute5;
2667 l_attributes(l_attribute_count).attribute6 := p_attribute_array(l_array_index).attribute6;
2668 l_attributes(l_attribute_count).attribute7 := p_attribute_array(l_array_index).attribute7;
2669 l_attributes(l_attribute_count).attribute8 := p_attribute_array(l_array_index).attribute8;
2670 l_attributes(l_attribute_count).attribute9 := p_attribute_array(l_array_index).attribute9;
2671 l_attributes(l_attribute_count).attribute10 := p_attribute_array(l_array_index).attribute10;
2672 l_attributes(l_attribute_count).attribute11 := p_attribute_array(l_array_index).attribute11;
2673 l_attributes(l_attribute_count).attribute12 := p_attribute_array(l_array_index).attribute12;
2674 l_attributes(l_attribute_count).attribute13 := p_attribute_array(l_array_index).attribute13;
2675 l_attributes(l_attribute_count).attribute14 := p_attribute_array(l_array_index).attribute14;
2676 l_attributes(l_attribute_count).attribute15 := p_attribute_array(l_array_index).attribute15;
2677 l_attributes(l_attribute_count).attribute16 := p_attribute_array(l_array_index).attribute16;
2678 l_attributes(l_attribute_count).attribute17 := p_attribute_array(l_array_index).attribute17;
2679 l_attributes(l_attribute_count).attribute18 := p_attribute_array(l_array_index).attribute18;
2680 l_attributes(l_attribute_count).attribute19 := p_attribute_array(l_array_index).attribute19;
2681 l_attributes(l_attribute_count).attribute20 := p_attribute_array(l_array_index).attribute20;
2682 l_attributes(l_attribute_count).attribute21 := p_attribute_array(l_array_index).attribute21;
2683 l_attributes(l_attribute_count).attribute22 := p_attribute_array(l_array_index).attribute22;
2684 l_attributes(l_attribute_count).attribute23 := p_attribute_array(l_array_index).attribute23;
2685 l_attributes(l_attribute_count).attribute24 := p_attribute_array(l_array_index).attribute24;
2686 l_attributes(l_attribute_count).attribute25 := p_attribute_array(l_array_index).attribute25;
2687 l_attributes(l_attribute_count).attribute26 := p_attribute_array(l_array_index).attribute26;
2688 l_attributes(l_attribute_count).attribute27 := p_attribute_array(l_array_index).attribute27;
2689 l_attributes(l_attribute_count).attribute28 := p_attribute_array(l_array_index).attribute28;
2690 l_attributes(l_attribute_count).attribute29 := p_attribute_array(l_array_index).attribute29;
2691 l_attributes(l_attribute_count).attribute30 := p_attribute_array(l_array_index).attribute30;
2692 l_attributes(l_attribute_count).Bld_Blk_Info_Type_Id := p_attribute_array(l_array_index).Bld_Blk_Info_Type_Id;
2693 l_attributes(l_attribute_count).Object_Version_Number := p_attribute_array(l_array_index).Object_Version_Number;
2694 l_attributes(l_attribute_count).new := p_attribute_array(l_array_index).new;
2695 l_attributes(l_attribute_count).changed := p_attribute_array(l_array_index).changed;
2696 l_attributes(l_attribute_count).bld_blk_info_type := p_attribute_array(l_array_index).bld_blk_info_type;
2697
2698 l_attribute_count := l_attribute_count + 1;
2699 l_array_index := p_attribute_array.next(l_array_index);
2700 END LOOP;
2701
2702 RETURN l_attributes;
2703 END array_to_attributes;
2704
2705 FUNCTION attributes_to_array(
2706 p_attributes IN hxc_self_service_time_deposit.building_block_attribute_info
2707 )
2708 RETURN HXC_ATTRIBUTE_TABLE_TYPE
2709 IS
2710 l_attribute_array HXC_ATTRIBUTE_TABLE_TYPE;
2711 l_attribute HXC_ATTRIBUTE_TYPE;
2712 l_array_index NUMBER := 0;
2713 l_attribute_index NUMBER;
2714 l_proc VARCHAR2(50);
2715 BEGIN
2716 g_debug:=hr_utility.debug_enabled;
2717 if g_debug then
2718 l_proc := 'attributes_to_array';
2719 hr_utility.set_location ( g_package||l_proc, 10);
2720 end if;
2721 --initialize attribute array
2722 l_attribute_array := HXC_ATTRIBUTE_TABLE_TYPE();
2723
2724 l_attribute_index := p_attributes.first;
2725 LOOP
2726 EXIT WHEN NOT p_attributes.exists(l_attribute_index);
2727
2728 l_array_index := l_array_index + 1;
2729 l_attribute_array.extend;
2730
2731 l_attribute_array(l_array_index) :=
2732 HXC_ATTRIBUTE_TYPE(
2733 p_attributes(l_attribute_index).TIME_ATTRIBUTE_ID
2734 ,p_attributes(l_attribute_index).BUILDING_BLOCK_ID
2735 ,p_attributes(l_attribute_index).ATTRIBUTE_CATEGORY
2736 ,p_attributes(l_attribute_index).ATTRIBUTE1
2737 ,p_attributes(l_attribute_index).ATTRIBUTE2
2738 ,p_attributes(l_attribute_index).ATTRIBUTE3
2739 ,p_attributes(l_attribute_index).ATTRIBUTE4
2740 ,p_attributes(l_attribute_index).ATTRIBUTE5
2741 ,p_attributes(l_attribute_index).ATTRIBUTE6
2742 ,p_attributes(l_attribute_index).ATTRIBUTE7
2743 ,p_attributes(l_attribute_index).ATTRIBUTE8
2744 ,p_attributes(l_attribute_index).ATTRIBUTE9
2745 ,p_attributes(l_attribute_index).ATTRIBUTE10
2746 ,p_attributes(l_attribute_index).ATTRIBUTE11
2747 ,p_attributes(l_attribute_index).ATTRIBUTE12
2748 ,p_attributes(l_attribute_index).ATTRIBUTE13
2749 ,p_attributes(l_attribute_index).ATTRIBUTE14
2750 ,p_attributes(l_attribute_index).ATTRIBUTE15
2751 ,p_attributes(l_attribute_index).ATTRIBUTE16
2752 ,p_attributes(l_attribute_index).ATTRIBUTE17
2753 ,p_attributes(l_attribute_index).ATTRIBUTE18
2754 ,p_attributes(l_attribute_index).ATTRIBUTE19
2755 ,p_attributes(l_attribute_index).ATTRIBUTE20
2756 ,p_attributes(l_attribute_index).ATTRIBUTE21
2757 ,p_attributes(l_attribute_index).ATTRIBUTE22
2758 ,p_attributes(l_attribute_index).ATTRIBUTE23
2759 ,p_attributes(l_attribute_index).ATTRIBUTE24
2760 ,p_attributes(l_attribute_index).ATTRIBUTE25
2761 ,p_attributes(l_attribute_index).ATTRIBUTE26
2762 ,p_attributes(l_attribute_index).ATTRIBUTE27
2763 ,p_attributes(l_attribute_index).ATTRIBUTE28
2764 ,p_attributes(l_attribute_index).ATTRIBUTE29
2765 ,p_attributes(l_attribute_index).ATTRIBUTE30
2766 ,p_attributes(l_attribute_index).BLD_BLK_INFO_TYPE_ID
2767 ,p_attributes(l_attribute_index).OBJECT_VERSION_NUMBER
2768 ,p_attributes(l_attribute_index).NEW
2769 ,p_attributes(l_attribute_index).CHANGED
2770 ,p_attributes(l_attribute_index).BLD_BLK_INFO_TYPE
2771 ,'N'
2772 ,null
2773 );
2774
2775 l_attribute_index := p_attributes.next(l_attribute_index);
2776 END LOOP;
2777
2778 if g_debug then
2779 hr_utility.set_location ( g_package||l_proc, 20);
2780 end if;
2781 RETURN l_attribute_array;
2782 END attributes_to_array;
2783 --
2784 -- Temporary function
2785 --
2786 FUNCTION array_to_blocks(
2787 p_block_array IN OUT NOCOPY HXC_BLOCK_TABLE_TYPE
2788 )
2789 RETURN hxc_self_service_time_deposit.timecard_info
2790 IS
2791 l_array_index NUMBER;
2792 l_blocks hxc_self_service_time_deposit.timecard_info;
2793 BEGIN
2794 l_array_index := p_block_array.first;
2795 LOOP
2796 EXIT WHEN NOT p_block_array.exists(l_array_index);
2797
2798 l_blocks(l_array_index).TIME_BUILDING_BLOCK_ID :=
2799 p_block_array(l_array_index).TIME_BUILDING_BLOCK_ID;
2800 l_blocks(l_array_index).TYPE :=
2801 p_block_array(l_array_index).TYPE;
2802 l_blocks(l_array_index).MEASURE :=
2803 p_block_array(l_array_index).MEASURE;
2804 l_blocks(l_array_index).UNIT_OF_MEASURE :=
2805 p_block_array(l_array_index).UNIT_OF_MEASURE;
2806 l_blocks(l_array_index).START_TIME :=
2807 fnd_date.canonical_to_date(p_block_array(l_array_index).START_TIME);
2808 l_blocks(l_array_index).STOP_TIME :=
2809 fnd_date.canonical_to_date(p_block_array(l_array_index).STOP_TIME);
2810 l_blocks(l_array_index).PARENT_BUILDING_BLOCK_ID :=
2811 p_block_array(l_array_index).PARENT_BUILDING_BLOCK_ID;
2812 l_blocks(l_array_index).PARENT_IS_NEW :=
2813 p_block_array(l_array_index).PARENT_IS_NEW;
2814 l_blocks(l_array_index).SCOPE :=
2815 p_block_array(l_array_index).SCOPE;
2816 l_blocks(l_array_index).OBJECT_VERSION_NUMBER :=
2817 p_block_array(l_array_index).OBJECT_VERSION_NUMBER;
2818 l_blocks(l_array_index).APPROVAL_STATUS :=
2819 p_block_array(l_array_index).APPROVAL_STATUS;
2820 l_blocks(l_array_index).RESOURCE_ID :=
2821 p_block_array(l_array_index).RESOURCE_ID;
2822 l_blocks(l_array_index).RESOURCE_TYPE :=
2823 p_block_array(l_array_index).RESOURCE_TYPE;
2824 l_blocks(l_array_index).APPROVAL_STYLE_ID :=
2825 p_block_array(l_array_index).APPROVAL_STYLE_ID;
2826 l_blocks(l_array_index).DATE_FROM :=
2827 fnd_date.canonical_to_date(p_block_array(l_array_index).DATE_FROM);
2828 l_blocks(l_array_index).DATE_TO :=
2829 fnd_date.canonical_to_date(p_block_array(l_array_index).DATE_TO);
2830 l_blocks(l_array_index).COMMENT_TEXT :=
2831 p_block_array(l_array_index).COMMENT_TEXT;
2832 l_blocks(l_array_index).PARENT_BUILDING_BLOCK_OVN:=
2833 p_block_array(l_array_index).PARENT_BUILDING_BLOCK_OVN;
2834 l_blocks(l_array_index).NEW :=
2835 p_block_array(l_array_index).NEW;
2836 l_blocks(l_array_index).CHANGED :=
2837 p_block_array(l_array_index).CHANGED;
2838
2839 l_array_index := p_block_array.next(l_array_index);
2840 END LOOP;
2841
2842 RETURN l_blocks;
2843 END array_to_blocks;
2844
2845 --
2846 --
2847 FUNCTION blocks_to_array(
2848 p_blocks IN hxc_self_service_time_deposit.timecard_info
2849 )
2850 RETURN HXC_BLOCK_TABLE_TYPE
2851 IS
2852 l_block_array HXC_BLOCK_TABLE_TYPE;
2853 l_array_index NUMBER := 0;
2854 l_block_index NUMBER;
2855 l_proc VARCHAR2(50);
2856 -- l_block HXC_BLOCK_TYPE;
2857
2858 BEGIN
2859 g_debug:=hr_utility.debug_enabled;
2860 if g_debug then
2861 l_proc := 'blocks_to_array';
2862 hr_utility.set_location ( g_package||l_proc, 10);
2863 end if;
2864 l_block_array := HXC_BLOCK_TABLE_TYPE();
2865
2866 l_block_index := p_blocks.first;
2867 LOOP
2868 EXIT WHEN NOT p_blocks.exists(l_block_index);
2869
2870 l_array_index := l_array_index + 1;
2871 l_block_array.extend;
2872
2873 l_block_array(l_array_index) :=
2874 HXC_BLOCK_TYPE(
2875 p_blocks(l_block_index).TIME_BUILDING_BLOCK_ID
2876 ,p_blocks(l_block_index).TYPE
2877 ,p_blocks(l_block_index).MEASURE
2878 ,p_blocks(l_block_index).UNIT_OF_MEASURE
2879 ,fnd_date.date_to_canonical(p_blocks(l_block_index).START_TIME)
2880 ,fnd_date.date_to_canonical(p_blocks(l_block_index).STOP_TIME)
2881 ,p_blocks(l_block_index).PARENT_BUILDING_BLOCK_ID
2882 ,p_blocks(l_block_index).PARENT_IS_NEW
2883 ,p_blocks(l_block_index).SCOPE
2884 ,p_blocks(l_block_index).OBJECT_VERSION_NUMBER
2885 ,p_blocks(l_block_index).APPROVAL_STATUS
2886 ,p_blocks(l_block_index).RESOURCE_ID
2887 ,p_blocks(l_block_index).RESOURCE_TYPE
2888 ,p_blocks(l_block_index).APPROVAL_STYLE_ID
2889 ,fnd_date.date_to_canonical(p_blocks(l_block_index).DATE_FROM)
2890 ,fnd_date.date_to_canonical(p_blocks(l_block_index).DATE_TO)
2891 ,p_blocks(l_block_index).COMMENT_TEXT
2892 ,p_blocks(l_block_index).PARENT_BUILDING_BLOCK_OVN
2893 ,p_blocks(l_block_index).NEW
2894 ,p_blocks(l_block_index).CHANGED
2895 ,'N'
2896 ,p_blocks(l_block_index).application_set_id
2897 ,p_blocks(l_block_index).TRANSLATION_DISPLAY_KEY --Bug 5565773
2898 );
2899
2900 l_block_index := p_blocks.next(l_block_index);
2901 END LOOP;
2902
2903 if g_debug then
2904 hr_utility.set_location ( g_package||l_proc, 140);
2905 end if;
2906 RETURN l_block_array;
2907 END blocks_to_array;
2908
2909 PROCEDURE maintain_errors (
2910 p_translated_bb_ids_tab hxc_self_service_time_deposit.translate_bb_ids_tab
2911 , p_translated_ta_ids_tab hxc_self_service_time_deposit.translate_ta_ids_tab
2912 , p_messages IN OUT NOCOPY hxc_self_service_time_deposit.message_table
2913 , p_transactions IN OUT NOCOPY hxc_deposit_wrapper_utilities.t_transaction ) IS
2914
2915 l_msg_ind BINARY_INTEGER;
2916 l_tbb_id NUMBER;
2917 l_ta_id NUMBER;
2918
2919 l_proc varchar2(72);
2920
2921 cursor c_max_ovn
2922 (p_tbb_id in number) is
2923 select max(object_version_number)
2924 from hxc_time_building_blocks
2925 where time_building_block_id = p_tbb_id;
2926
2927 cursor c_tx_id
2928 (p_tbb_id in number,
2929 p_tbb_ovn in number) is
2930 select transaction_id
2931 from hxc_transaction_details
2932 where time_building_block_id = p_tbb_id
2933 and object_version_number = p_tbb_ovn;
2934
2935
2936 TYPE r_tbb_vs_txd IS RECORD ( txd_id hxc_transaction_details.transaction_detail_id%TYPE );
2937
2938 TYPE t_tbb_vs_txd IS TABLE OF r_tbb_vs_txd INDEX BY BINARY_INTEGER;
2939
2940 t_tbb_vs_txds t_tbb_vs_txd;
2941
2942 l_tx_ind BINARY_INTEGER;
2943
2944 l_tx_id NUMBER;
2945 l_tbb_ovn NUMBER;
2946
2947 BEGIN
2948 if g_debug then
2949 l_proc := g_package||'.maintain_errors';
2950 hr_utility.trace('transaction details are ');
2951 end if;
2952 /*
2953 FOR x in p_transactions.FIRST .. p_transactions.LAST
2954 LOOP
2955 if g_debug then
2956 hr_utility.trace('txd tbb id is '||to_char(p_transactions(x).tbb_id));
2957 hr_utility.trace('txd txd id is '||to_char(p_transactions(x).txd_id));
2958 end if;
2959 END LOOP;
2960
2961 if g_debug then
2962 hr_utility.trace('');
2963 hr_utility.trace('messages are ');
2964 end if;
2965
2966 FOR x in p_messages.FIRST .. p_messages.LAST
2967 LOOP
2968 if g_debug then
2969 hr_utility.trace('message name is '||p_messages(x).message_name);
2970 hr_utility.trace('tbb id is '||to_char(p_messages(x).time_building_block_id));
2971 hr_utility.trace('tbb ovn is '||to_char(p_messages(x).time_building_block_ovn));
2972 end if;
2973 END LOOP;
2974 */
2975
2976 if g_debug then
2977 hr_utility.set_location('Entering '||l_proc, 10);
2978 end if;
2979
2980 -- parse the transaction table to produce a mapping of time building blocks
2981 -- to transaction details id- this will save traversing the table for every message
2982
2983 -- GPM v115.32
2984
2985 l_tx_ind := p_transactions.FIRST;
2986
2987 WHILE l_tx_ind IS NOT NULL
2988 LOOP
2989
2990 if g_debug then
2991 hr_utility.set_location('Processing '||l_proc, 20);
2992 end if;
2993
2994 t_tbb_vs_txds(p_transactions(l_tx_ind).tbb_id).txd_id := p_transactions(l_tx_ind).txd_id;
2995
2996 l_tx_ind := p_transactions.NEXT(l_tx_ind);
2997
2998 END LOOP;
2999
3000 if g_debug then
3001 hr_utility.set_location('Processing '||l_proc, 30);
3002 end if;
3003
3004 -- if the timecard has just been inserted need to map dummy ids to new ids
3005
3006 l_msg_ind := p_messages.FIRST;
3007
3008 WHILE ( l_msg_ind IS NOT NULL )
3009 LOOP
3010 if g_debug then
3011 hr_utility.set_location('Processing '||l_proc, 40);
3012 end if;
3013 -- assign to variables (makes it easier to read the following code)
3014
3015 l_tbb_id := p_messages(l_msg_ind).time_building_block_id;
3016 l_ta_id := p_messages(l_msg_ind).time_attribute_id;
3017
3018 IF ( ( l_tbb_id IS NOT NULL ) AND ( p_translated_bb_ids_tab.COUNT <> 0 ) )
3019 THEN
3020 if g_debug then
3021 hr_utility.set_location('Processing '||l_proc, 50);
3022 end if;
3023 -- this may not be a new building block
3024
3025 IF ( p_translated_bb_ids_tab.EXISTS(l_tbb_id) )
3026 THEN
3027 if g_debug then
3028 hr_utility.set_location('Processing '||l_proc, 60);
3029 end if;
3030 p_messages(l_msg_ind).time_building_block_id :=
3031 p_translated_bb_ids_tab(l_tbb_id).actual_bb_id;
3032
3033 -- set the object version number back to 1
3034 -- remember it would have been set to ovn+1 in add_error_to_Table
3035
3036 p_messages(l_msg_ind).time_building_block_ovn := 1;
3037
3038 END IF;
3039
3040 END IF;
3041
3042 if g_debug then
3043 hr_utility.set_location('Processing '||l_proc, 70);
3044 end if;
3045 -- now the attribute
3046
3047 IF ( ( l_ta_id IS NOT NULL ) AND ( p_translated_ta_ids_tab.COUNT <> 0 ) )
3048 THEN
3049
3050 IF ( p_translated_ta_ids_tab.EXISTS(l_ta_id) )
3051 THEN
3052
3053 p_messages(l_msg_ind).time_attribute_id :=
3054 p_translated_ta_ids_tab(l_ta_id).actual_ta_id;
3055
3056 END IF;
3057
3058 END IF;
3059
3060 if g_debug then
3061 hr_utility.set_location('Processing '||l_proc, 75);
3062 end if;
3063 -- find the ovn of the tbb
3064 OPEN c_max_ovn(p_messages(l_msg_ind).time_building_block_id);
3065 FETCH c_max_ovn INTO l_tbb_ovn;
3066 CLOSE c_max_ovn;
3067
3068 --find the transaction_id from the tbb
3069 OPEN c_tx_id(p_messages(l_msg_ind).time_building_block_id,l_tbb_ovn);
3070 FETCH c_tx_id INTO l_tx_id;
3071 CLOSE c_tx_id;
3072
3073 IF (l_tx_id is null) THEN
3074 l_tx_id := -1;
3075 END IF;
3076
3077 INSERT INTO hxc_errors (
3078 error_id
3079 , transaction_detail_id
3080 , time_building_block_id
3081 , time_building_block_ovn
3082 , time_attribute_id
3083 , time_attribute_ovn
3084 , message_name
3085 , message_level
3086 , message_field
3087 , message_tokens
3088 , application_short_name
3089 , object_version_number )
3090 VALUES (
3091 hxc_errors_s.nextval
3092 , l_tx_id
3093 , p_messages(l_msg_ind).time_building_block_id
3094 , l_tbb_ovn
3095 , p_messages(l_msg_ind).time_attribute_id
3096 , p_messages(l_msg_ind).time_attribute_ovn
3097 , p_messages(l_msg_ind).message_name
3098 , p_messages(l_msg_ind).message_level
3099 , p_messages(l_msg_ind).message_field
3100 , p_messages(l_msg_ind).message_tokens
3101 , p_messages(l_msg_ind).application_short_name
3102 , 1 );
3103
3104
3105 l_msg_ind := p_messages.NEXT(l_msg_ind);
3106
3107 END LOOP;
3108
3109 if g_debug then
3110 hr_utility.set_location('Leaving '||l_proc, 80);
3111 end if;
3112 END maintain_errors;
3113
3114 /*This function obtains the PAEXPITDFF code from the PAEXPITDFF name.
3115 First if the name is present in the g_code_name_tab cache, then the
3116 corresponding code is fetched, else the corresponding code is fetched
3117 from the database table */
3118 FUNCTION get_dupdff_code(p_dupdff_name IN VARCHAR2) return varchar2
3119
3120 IS
3121 CURSOR get_code(p_name VARCHAR2,P_MESSAGE VARCHAR2)
3122 IS
3123 SELECT descriptive_flex_context_code
3124 FROM fnd_descr_flex_contexts_vl
3125 WHERE descriptive_flex_context_name = p_name
3126 AND descriptive_flexfield_name = 'OTC Information Types'
3127 AND application_id = 809
3128 AND substrB(DESCRIPTIVE_FLEX_CONTEXT_CODE,0,instr(DESCRIPTIVE_FLEX_CONTEXT_CODE,'-')-2)
3129 =substrB(DESCRIPTIVE_FLEX_CONTEXT_name,0,instr(DESCRIPTIVE_FLEX_CONTEXT_name,'-')-2)||'C'
3130 AND SUBSTRB(DESCRIPTION,0, LENGTH(P_MESSAGE))=P_MESSAGE;
3131
3132 l_index NUMBER := 0;
3133 l_code fnd_descr_flex_contexts_vl.descriptive_flex_context_code%TYPE;
3134 l_table_index NUMBER;
3135 l_message varchar2(100);
3136 BEGIN
3137 l_code := NULL;
3138 l_index := g_code_name_tab.FIRST;
3139
3140 LOOP
3141 EXIT WHEN NOT g_code_name_tab.EXISTS(l_index);
3142 -- DBMS_OUTPUT.put_line(g_code_name_tab(l_index).dupdff_name);
3143 -- DBMS_OUTPUT.put_line(g_code_name_tab(l_index).dupdff_code);
3144
3145 IF g_code_name_tab(l_index).dupdff_name = p_dupdff_name
3146 THEN
3147 l_code := g_code_name_tab(l_index).dupdff_code;
3148 EXIT;
3149 END IF;
3150
3151 l_index := g_code_name_tab.NEXT(l_index);
3152 END LOOP;
3153
3154 IF l_code IS NULL
3155 THEN
3156
3157 hr_utility.set_message(809,'HXC_DFF_SYSTEM_CONTEXT');
3158 l_message := hr_utility.get_message;
3159 OPEN get_code(p_dupdff_name,l_message);
3160 FETCH get_code INTO l_code;
3161
3162 if l_code is null then
3163 CLOSE get_code;
3164 return(p_dupdff_name);
3165
3166 else
3167 IF g_code_name_tab.count > 0
3168 THEN
3169 l_table_index := g_code_name_tab.count + 1;
3170 ELSE
3171 l_table_index := 1;
3172 END IF;
3173
3174 g_code_name_tab(l_table_index).dupdff_code := l_code;
3175 g_code_name_tab(l_table_index).dupdff_name := p_dupdff_name;
3176
3177 CLOSE get_code;
3178 end if;
3179 END IF;
3180
3181 RETURN (l_code);
3182 END;
3183
3184 /*This function obtains the PAEXPITDFF Context Name from the PAEXPITDFF Context Code.
3185 First if the Name is present in the g_code_name_tab cache, then the corresponding
3186 code is fetched, else the corresponding code is fetched from the database table */
3187
3188
3189 FUNCTION get_dupdff_name (p_dupdff_code IN VARCHAR2)
3190 RETURN VARCHAR2
3191 IS
3192 CURSOR get_name (p_code VARCHAR2, p_message VARCHAR2)
3193 IS
3194 SELECT descriptive_flex_context_name
3195 FROM fnd_descr_flex_contexts_vl
3196 WHERE descriptive_flex_context_code = p_code
3197 AND descriptive_flexfield_name = 'OTC Information Types'
3198 AND application_id = 809
3199 AND SUBSTRB (
3200 descriptive_flex_context_code,
3201 0,
3202 INSTR (descriptive_flex_context_code, '-') - 2
3203 ) = SUBSTRB (
3204 descriptive_flex_context_name,
3205 0,
3206 INSTR (descriptive_flex_context_name, '-') - 2
3207 )
3208 || 'C'
3209 AND SUBSTRB (description, 0, LENGTH (p_message)) = p_message;
3210
3211 l_index NUMBER := 0;
3212 l_name fnd_descr_flex_contexts_vl.descriptive_flex_context_name%TYPE;
3213 l_message VARCHAR2 (100);
3214 BEGIN
3215 l_name := NULL;
3216
3217 l_index := TO_NUMBER (
3218 SUBSTR (p_dupdff_code, INSTR (p_dupdff_code, '-') + 2)
3219 );
3220 IF g_code_name_tab.EXISTS(L_INDEX) then
3221 l_name := g_code_name_tab (l_index).dupdff_name;
3222 RETURN(l_name);
3223
3224 ELSE
3225
3226 hr_utility.set_message (809, 'HXC_DFF_SYSTEM_CONTEXT');
3227 l_message := hr_utility.GET_MESSAGE;
3228 OPEN get_name (p_dupdff_code, l_message);
3229 FETCH get_name INTO l_name;
3230
3231 IF l_name IS NULL
3232 THEN
3233 CLOSE get_name;
3234 RETURN (p_dupdff_code);
3235 ELSE
3236 g_code_name_tab (l_index).dupdff_name := l_name;
3237 g_code_name_tab (l_index).dupdff_code := p_dupdff_code;
3238 CLOSE get_name;
3239 END IF;
3240 END IF;
3241 RETURN(l_name);
3242 EXCEPTION
3243 WHEN OTHERS THEN
3244 RETURN (p_dupdff_code);
3245 END;
3246
3247 function timecard_hours_type_list( p_resource_id in varchar2,
3248 p_start_time in varchar2,
3249 p_stop_time in varchar2,
3250 p_alias_or_element_id in varchar2,
3251 p_aliases in VARCHAR2,
3252 p_public_template in varchar2) return varchar2
3253
3254 is
3255 cursor cur_hours_type(p_alias_definition_id IN VARCHAR2) is
3256 SELECT
3257 havt.alias_value_name Display_Value,
3258 hav.attribute1 element_id,
3259 hav.alias_value_id alias_value_id
3260 FROM
3261 hxc_alias_values hav,
3262 hxc_alias_values_tl havt,
3263 hxc_alias_definitions had,
3264 PAY_ELEMENT_TYPES_F ELEMENT
3265 WHERE
3266 hav.attribute1 = ELEMENT.element_type_id and
3267 hav.enabled_flag='Y' and
3268 had.alias_definition_id = hav.alias_definition_id and
3269 havt.language = USERENV('LANG') and
3270 havt.alias_value_id =hav.alias_value_id and
3271 had.alias_definition_id = p_alias_definition_id AND
3272 ELEMENT.EFFECTIVE_START_DATE <= sysdate AND
3273 ELEMENT.EFFECTIVE_END_DATE >= sysdate;
3274
3275 l_hours_type_list varchar2(32000) :=null;
3276 l_id_string varchar2(15) :=null;
3277 l_start number;
3278 l_index number;
3279 l_alias varchar2(10);
3280 l_aliases varchar2(1000);
3281 begin
3282
3283
3284 l_aliases :=p_aliases;
3285 if p_public_template = 'Y' then
3286
3287 IF ( p_aliases IS NOT NULL )
3288 THEN
3289 l_start := 1;
3290 l_index :=1;
3291 while(l_index <> 0)
3292 loop
3293 l_index := instr(l_aliases,',',l_start);
3294 l_alias := substr(l_aliases,l_start,(l_index-1));
3295 l_aliases := substr(l_aliases,(l_index+1));
3296 l_start := 1;
3297
3298 if(l_alias is not null) then
3299 FOR l_hours_type in cur_hours_type(l_alias) LOOP
3300
3301 IF (p_alias_or_element_id = 'ALIAS') THEN
3302 l_id_string := l_hours_type.alias_value_id;
3303 ELSIF (p_alias_or_element_id = 'ELEMENT') THEN
3304 l_id_string := l_hours_type.element_id;
3305 END IF;
3306 l_hours_type_list := l_hours_type_list
3307 ||l_hours_type.display_value
3308 ||'|'
3309 ||l_id_string
3310 ||'|';
3311 END LOOP;
3312 end if;
3313 end loop;
3314 END IF;
3315
3316 ELSE
3317
3318 l_hours_type_list := timecard_hours_type_list(p_resource_id,
3319 p_start_time,
3320 p_stop_time,
3321 p_alias_or_element_id);
3322
3323 END IF;
3324 return l_hours_type_list;
3325 END timecard_hours_type_list;
3326
3327 -- Added a new procedure which would replace the resource_id in case of
3328 -- duplication of public templates.
3329
3330 procedure replace_resource_id (p_blocks IN OUT NOCOPY HXC_BLOCK_TABLE_TYPE,
3331 p_resource_id IN hxc_time_building_blocks.resource_id%type) is
3332 l_block_index number;
3333 begin
3334
3335 if (p_blocks.count>0) then
3336 l_block_index := p_blocks.first;
3337 if(p_blocks(l_block_index).resource_id = p_resource_id) then
3338 return; -- If the resourceids are same, then we dont need to change.
3339 end if;
3340 LOOP
3341 EXIT WHEN NOT p_blocks.EXISTS(l_block_index);
3342
3343 p_blocks(l_block_index).resource_id :=p_resource_id;
3344
3345 l_block_index := p_blocks.next(l_block_index);
3346 END LOOP;
3347 end if;
3348
3349 end replace_resource_id;
3350
3351 END hxc_deposit_wrapper_utilities;