[Home] [Help]
PACKAGE BODY: APPS.HXC_TIMECARD_PROPERTIES
Source
1 PACKAGE BODY hxc_timecard_properties AS
2 /* $Header: hxctcprops.pkb 120.14 2008/02/25 10:58:32 rchennur noship $ */
3
4 c_alias_name CONSTANT varchar2(27) := 'TcWTcrdAliasesTimecardAlias';
5 c_alias_type CONSTANT varchar2(23) := 'TcWTcrdAliasesAliasType';
6 g_debug boolean := hr_utility.debug_enabled;
7
8 Type property_definition is Record
9 (property_name varchar2(60)
10 ,column_name fnd_descr_flex_column_usages.application_column_name%type
11 );
12
13 Type property_definition_tbl is table of property_definition index by binary_integer;
14
15 g_property_definitions property_definition_tbl;
16
17 Type context_definition is Record
18 (context_code fnd_descr_flex_column_usages.descriptive_flex_context_code%type
19 ,start_index number
20 );
21
22 Type context_definition_tbl is table of context_definition index by binary_integer;
23
24 g_context_definition context_definition_tbl;
25
26 Type period_list_cache is table of number index by binary_integer;
27
28 g_period_list_cache period_list_cache;
29
30 Type asg_id is table of number index by binary_integer;
31 Type asg_date is table of date index by binary_integer;
32 type asg_number is table of per_all_assignments_f.assignment_number%type index by binary_integer;
33 type asg_status is table of per_assignment_status_types.per_system_status%type index by binary_integer;
34
35 function find_segment
36 (p_context_code in fnd_descr_flex_column_usages.descriptive_flex_context_code%type
37 ,p_segment_name in fnd_descr_flex_column_usages.end_user_column_name%type
38 ) return varchar2 is
39
40 cursor c_segment_name
41 (p_cc in fnd_descr_flex_column_usages.descriptive_flex_context_code%type
42 ,p_sn in fnd_descr_flex_column_usages.end_user_column_name%type
43 ) is
44 select application_column_name
45 from fnd_descr_flex_column_usages
46 where descriptive_flexfield_name = 'OTC PREFERENCES'
47 and descriptive_flex_context_code = p_cc
48 and end_user_column_name = p_sn
49 and application_id = 809;
50
51 l_application_column_name fnd_columns.column_name%type;
52
53 Begin
54
55 open c_segment_name(p_context_code,p_segment_name);
56 fetch c_segment_name into l_application_column_name;
57 close c_segment_name;
58
59 return l_application_column_name;
60
61 End find_segment;
62
63 procedure cache_property_definitions
64 (p_for_timecard in boolean) is
65
66 begin
67
68 if(g_property_definitions.count <1) then
69 --
70 -- We need to populate the references for the
71 -- preference contexts
72 --
73
74 if(p_for_timecard) then
75 --
76 -- We need to populate the properties
77 --
78 g_property_definitions(1).column_name := 'ATTRIBUTE1';
79 g_property_definitions(1).property_name := 'TcWAlwNegTimeAllowNegativeEntries';
80 g_property_definitions(2).column_name := 'ATTRIBUTE1';
81 g_property_definitions(2).property_name := 'TcWAprvrDfltOvrdDefaultOverrideApprover';
82 g_property_definitions(3).column_name := 'ATTRIBUTE1';
83 g_property_definitions(3).property_name := 'TcWAprvrEnbleOvrdEnableApproverOverride';
84 g_property_definitions(4).column_name := 'ATTRIBUTE1';
85 g_property_definitions(4).property_name := 'TcWDateFormatsPeriodlist';
86 g_property_definitions(5).column_name := 'ATTRIBUTE2';
87 g_property_definitions(5).property_name := 'TcWDateFormatsTimecardDayHeaderFormat';
88 g_property_definitions(6).column_name := 'ATTRIBUTE3';
89 g_property_definitions(6).property_name := 'TcWDateFormatsTimecardDetailsHeaderFormat';
90 g_property_definitions(7).column_name := 'ATTRIBUTE4';
91 g_property_definitions(7).property_name := 'TcWDateFormatsTemplateTimeperiodPicklist';
92 g_property_definitions(8).column_name := 'ATTRIBUTE1';
93 g_property_definitions(8).property_name := 'TcWDeleteAllowTimecardDeleteAllowed';
94 g_property_definitions(9).column_name := 'ATTRIBUTE1';
95 g_property_definitions(9).property_name := 'TcWDiscnctdEntryDisconnectedEntry';
96 --Property for enabling Defined Project List Export
97 g_property_definitions(10).property_name := 'TcWDiscnctdEntryDefinedProjectListExport';
98 g_property_definitions(10).column_name := 'ATTRIBUTE2';
99
100 g_property_definitions(11).column_name := 'ATTRIBUTE1';
101 g_property_definitions(11).property_name := 'TcWFlowProcessNameSelfServiceFlow';
102 g_property_definitions(12).column_name := 'ATTRIBUTE1';
103 g_property_definitions(12).property_name := 'TcWNumRcntTcrdsDisplayNumberTcards';
104 g_property_definitions(13).column_name := 'ATTRIBUTE1';
105 g_property_definitions(13).property_name := 'TcWRulesEvaluationRulesEvaluation';
106 g_property_definitions(14).column_name := 'ATTRIBUTE1';
107 g_property_definitions(14).property_name := 'TcWTcrdAliasesTimecardAlias1';
108 g_property_definitions(15).column_name := 'ATTRIBUTE10';
109 g_property_definitions(15).property_name := 'TcWTcrdAliasesTimecardAlias10';
110 g_property_definitions(16).column_name := 'ATTRIBUTE2';
111 g_property_definitions(16).property_name := 'TcWTcrdAliasesTimecardAlias2';
112 g_property_definitions(17).column_name := 'ATTRIBUTE3';
113 g_property_definitions(17).property_name := 'TcWTcrdAliasesTimecardAlias3';
114 g_property_definitions(18).column_name := 'ATTRIBUTE4';
115 g_property_definitions(18).property_name := 'TcWTcrdAliasesTimecardAlias4';
116 g_property_definitions(19).column_name := 'ATTRIBUTE5';
117 g_property_definitions(19).property_name := 'TcWTcrdAliasesTimecardAlias5';
118 g_property_definitions(20).column_name := 'ATTRIBUTE6';
119 g_property_definitions(20).property_name := 'TcWTcrdAliasesTimecardAlias6';
120 g_property_definitions(21).column_name := 'ATTRIBUTE7';
121 g_property_definitions(21).property_name := 'TcWTcrdAliasesTimecardAlias7';
122 g_property_definitions(22).column_name := 'ATTRIBUTE8';
123 g_property_definitions(22).property_name := 'TcWTcrdAliasesTimecardAlias8';
124 g_property_definitions(23).column_name := 'ATTRIBUTE9';
125 g_property_definitions(23).property_name := 'TcWTcrdAliasesTimecardAlias9';
126 g_property_definitions(24).column_name := 'ATTRIBUTE1';
127 g_property_definitions(24).property_name := 'TcWTcrdLayoutTimecardLayout';
128 g_property_definitions(25).column_name := 'ATTRIBUTE2';
129 g_property_definitions(25).property_name := 'TcWTcrdLayoutReviewLayout';
130 g_property_definitions(26).column_name := 'ATTRIBUTE3';
131 g_property_definitions(26).property_name := 'TcWTcrdLayoutConfirmationLayout';
132 g_property_definitions(27).column_name := 'ATTRIBUTE4';
133 g_property_definitions(27).property_name := 'TcWTcrdLayoutDetailLayout';
134 g_property_definitions(28).column_name := 'ATTRIBUTE5';
135 g_property_definitions(28).property_name := 'TcWTcrdLayoutExportLayout';
136 g_property_definitions(29).column_name := 'ATTRIBUTE6';
137 g_property_definitions(29).property_name := 'TcWTcrdLayoutAuditLayout';
138 g_property_definitions(30).column_name := 'ATTRIBUTE7';
139 g_property_definitions(30).property_name := 'TcWTcrdLayoutFragmentLayout';
140 g_property_definitions(31).column_name := 'ATTRIBUTE8';
141 g_property_definitions(31).property_name := 'TcWTcrdLayoutNotificationLayout';
142 g_property_definitions(32).column_name := 'ATTRIBUTE1';
143 g_property_definitions(32).property_name := 'TcWTcrdNumEmtyRwsTimecardEmptyRows';
144 g_property_definitions(33).column_name := 'ATTRIBUTE1';
145 g_property_definitions(33).property_name := 'TcWTcrdPeriodTimecardPeriods';
146 g_property_definitions(34).column_name := 'ATTRIBUTE1';
147 g_property_definitions(34).property_name := 'TcWTcrdStAlwEditsTcardStatusAllowEdits';
148 g_property_definitions(35).column_name := 'ATTRIBUTE11';
149 g_property_definitions(35).property_name := 'TcWTcrdStAlwEditsFutureNumber';
150 g_property_definitions(36).column_name := 'ATTRIBUTE6';
151 g_property_definitions(36).property_name := 'TcWTcrdStAlwEditsPastNumber';
152 g_property_definitions(37).column_name := 'ATTRIBUTE1';
153 g_property_definitions(37).property_name := 'TcWTcrdUomTimecardUnitOfMeasure';
154 g_property_definitions(38).column_name := 'ATTRIBUTE2';
155 g_property_definitions(38).property_name := 'TcWTcrdUomUnitOfMeasureFormat';
156
157 g_property_definitions(39).column_name := 'ATTRIBUTE3';
158 g_property_definitions(39).property_name := 'TcWTcrdUomDecimalPrecision';
159 g_property_definitions(40).column_name := 'ATTRIBUTE4';
160 g_property_definitions(40).property_name := 'TcWTcrdUomRoundingRule';
161
162 g_property_definitions(41).column_name := 'ATTRIBUTE1';
163 g_property_definitions(41).property_name := 'TcWTmpltApndOnTcrdAppendOnTcard';
164 g_property_definitions(42).column_name := 'ATTRIBUTE1';
165 g_property_definitions(42).property_name := 'TcWTmpltCreateCreateUserTemplates';
166 g_property_definitions(43).column_name := 'ATTRIBUTE1';
167 g_property_definitions(43).property_name := 'TcWTmpltDfltValAdminAdminDefaultTemplate';
168 g_property_definitions(44).column_name := 'ATTRIBUTE1';
169 g_property_definitions(44).property_name := 'TcWTmpltDfltValUsrUserDefaultTemplate';
170 g_property_definitions(45).column_name := 'ATTRIBUTE1';
171 g_property_definitions(45).property_name := 'TcWTmpltFcnltyTmpltFunctionality';
172 g_property_definitions(46).column_name := 'ATTRIBUTE4';
173 g_property_definitions(46).property_name := 'TcWTmpltFcnltyExcludeHoursCheckBox';
174 g_property_definitions(47).column_name := 'ATTRIBUTE1';
175 g_property_definitions(47).property_name := 'TcWTmpltSvOnTcrdSaveAsTemplate';
176 g_property_definitions(48).column_name := 'ATTRIBUTE1';
177 g_property_definitions(48).property_name := 'TsPerApprovalStyleTsApprovalStyle';
178 g_property_definitions(49).column_name := 'ATTRIBUTE2';
179 g_property_definitions(49).property_name := 'TsPerApprovalStyleTsOverrideApprovalStyle';
180
181
182
183
184 --These properties are newly added for the Display Accrual balances work.
185 g_property_definitions(50).column_name := 'ATTRIBUTE1';
186 g_property_definitions(50).property_name := 'TcWDisplayAccBalDisplayBalances';
187
188 g_property_definitions(51).column_name := 'ATTRIBUTE2';
189 g_property_definitions(51).property_name := 'TcWDisplayAccBalElementSet';
190
191 g_property_definitions(52).column_name := 'ATTRIBUTE3';
192 g_property_definitions(52).property_name := 'TcWDisplayAccBalAccrualEvaluationDate';
193
194 g_property_definitions(53).column_name := 'ATTRIBUTE4';
195 g_property_definitions(53).property_name := 'TcWDisplayAccBalAccrualFunction';
196
197 -- The properties have been newly added for the Public Templates Group.
198 g_property_definitions(54).column_name := 'ATTRIBUTE1';
199 g_property_definitions(54).property_name := 'TcWPublicTemplatePublicTemplateGroup1';
200 g_property_definitions(55).column_name := 'ATTRIBUTE2';
201 g_property_definitions(55).property_name := 'TcWPublicTemplatePublicTemplateGroup2';
202 g_property_definitions(56).column_name := 'ATTRIBUTE3';
203 g_property_definitions(56).property_name := 'TcWPublicTemplatePublicTemplateGroup3';
204 g_property_definitions(57).column_name := 'ATTRIBUTE4';
205 g_property_definitions(57).property_name := 'TcWPublicTemplatePublicTemplateGroup4';
206 g_property_definitions(58).column_name := 'ATTRIBUTE5';
207 g_property_definitions(58).property_name := 'TcWPublicTemplatePublicTemplateGroup5';
208 g_property_definitions(59).column_name := 'ATTRIBUTE6';
209 g_property_definitions(59).property_name := 'TcWPublicTemplatePublicTemplateGroup6';
210 g_property_definitions(60).column_name := 'ATTRIBUTE7';
211 g_property_definitions(60).property_name := 'TcWPublicTemplatePublicTemplateGroup7';
212 g_property_definitions(61).column_name := 'ATTRIBUTE8';
213 g_property_definitions(61).property_name := 'TcWPublicTemplatePublicTemplateGroup8';
214 g_property_definitions(62).column_name := 'ATTRIBUTE9';
215 g_property_definitions(62).property_name := 'TcWPublicTemplatePublicTemplateGroup9';
216 g_property_definitions(63).column_name := 'ATTRIBUTE10';
217 g_property_definitions(63).property_name := 'TcWPublicTemplatePublicTemplateGroup10';
218 -- This property has been add for DA Enhancement
219 g_property_definitions(64).column_name := 'ATTRIBUTE1';
220 g_property_definitions(64).property_name := 'TsPerValidateOnSaveValidateOnSave';
221 g_property_definitions(65).column_name := 'ATTRIBUTE2';--find_segment('TS_PER_VALIDATE_ON_SAVE','DELETE_BLANK_ROWS_ON_SAVE');--
222 g_property_definitions(65).property_name := 'TsPerDeleteBlankRowsOnSave';
223
224 else
225
226 g_property_definitions(1).column_name := 'ATTRIBUTE1';
227 g_property_definitions(1).property_name := 'TsPerApplicationSetTsApplicationSet';
228 g_property_definitions(2).column_name := find_segment('TC_W_TCRD_ST_ALW_EDITS','MODIFY_APPROVED_TC_DETAILS');
229 g_property_definitions(2).property_name := 'TcWTcrdStAlwEditsModifyApprovedTcDetails';
230 g_property_definitions(3).column_name := find_segment('TC_W_TCRD_ST_ALW_EDITS','MODIFY_APPROVED_TC_DAYS');
231 g_property_definitions(3).property_name := 'TcWTcrdStAlwEditsModifyApprovedTcDays';
232 g_property_definitions(4).column_name := 'ATTRIBUTE1';
233 g_property_definitions(4).property_name := 'TsPerMmeTimeEntryRulesPteTimeEntryRule';
234 g_property_definitions(5).column_name := 'ATTRIBUTE1';
235 g_property_definitions(5).property_name := 'TsPerElpRulesElpTimeEntryRuleGroup';
236 g_property_definitions(6).column_name := 'ATTRIBUTE1';
237 g_property_definitions(6).property_name := 'TcWRulesEvaluationRulesEvaluation';
238 g_property_definitions(7).column_name := 'ATTRIBUTE2';
239 g_property_definitions(7).property_name := 'TcWRulesEvaluationAppRulesEvaluation';
240 g_property_definitions(8).column_name := 'ATTRIBUTE1';
241 g_property_definitions(8).property_name := 'TsPerAuditRequirementsAuditRequirements';
242 g_property_definitions(9).column_name := 'ATTRIBUTE1';
243 g_property_definitions(9).property_name := 'TsPerValidateOnSaveValidateOnSave';
244 g_property_definitions(10).column_name := 'ATTRIBUTE1';
245 g_property_definitions(10).property_name := 'TsPerTimeCategoryIdentifyingDayElements';
246 g_property_definitions(11).column_name := 'ATTRIBUTE2';
247 g_property_definitions(11).property_name := 'TsPerNumberofDaysinAssignmentFrequency';
248
249
250 end if; -- if for timecard
251 end if;
252
253 end cache_property_definitions;
254
255 procedure find_name_indices_for_context
256 (p_context_code in fnd_descr_flex_column_usages.descriptive_flex_context_code%type
257 ,p_for_timecard in boolean
258 ,p_start_index out nocopy number
259 ,p_stop_index out nocopy number
260 ) is
261
262 l_index number;
263 l_found_context boolean := false;
264 l_bother_to_look boolean := false;
265
266 Begin
267
268 p_start_index := hr_api.g_number;
269
270 if(g_context_definition.count <1) then
271 cache_property_definitions(p_for_timecard);
272 end if;
273
274 if(p_for_timecard) then
275
276 if(p_context_code = 'TC_W_ALW_NEG_TIME') then
277 p_start_index := 1;
278 p_stop_index := 1;
279 elsif(p_context_code = 'TC_W_APRVR_DFLT_OVRD') then
280 p_start_index := 2;
281 p_stop_index := 2;
282 elsif(p_context_code = 'TC_W_APRVR_ENBLE_OVRD') then
283 p_start_index := 3;
284 p_stop_index := 3;
285 elsif(p_context_code = 'TC_W_DATE_FORMATS') then
286 p_start_index := 4;
287 p_stop_index := 7;
288 elsif(p_context_code = 'TC_W_DELETE_ALLOW') then
289 p_start_index := 8;
290 p_stop_index := 8;
291 elsif(p_context_code = 'TC_W_DISCNCTD_ENTRY') then
292 p_start_index := 9;
293 p_stop_index := 10;
294 elsif(p_context_code = 'TC_W_FLOW_PROCESS_NAME') then
295 p_start_index := 11;
296 p_stop_index := 11;
297 elsif(p_context_code = 'TC_W_NUM_RCNT_TCRDS') then
298 p_start_index := 12;
299 p_stop_index := 12;
300 elsif(p_context_code = 'TC_W_RULES_EVALUATION') then
301 p_start_index := 13;
302 p_stop_index := 13;
303 elsif(p_context_code = 'TC_W_TCRD_ALIASES') then
304 p_start_index := 14;
305 p_stop_index := 23;
306 elsif(p_context_code = 'TC_W_TCRD_LAYOUT') then
307 p_start_index := 24;
308 p_stop_index := 31;
309 elsif(p_context_code = 'TC_W_TCRD_NUM_EMTY_RWS') then
310 p_start_index := 32;
311 p_stop_index := 32;
312 elsif(p_context_code = 'TC_W_TCRD_PERIOD') then
313 p_start_index := 33;
314 p_stop_index := 33;
315 elsif(p_context_code = 'TC_W_TCRD_ST_ALW_EDITS') then
316 p_start_index := 34;
317 p_stop_index := 36;
318 elsif(p_context_code = 'TC_W_TCRD_UOM') then
319 p_start_index := 37;
320 p_stop_index := 40;
321 elsif(p_context_code = 'TC_W_TMPLT_APND_ON_TCRD') then
322 p_start_index := 41;
323 p_stop_index := 41;
324 elsif(p_context_code = 'TC_W_TMPLT_CREATE') then
325 p_start_index := 42;
326 p_stop_index := 42;
327 elsif(p_context_code = 'TC_W_TMPLT_DFLT_VAL_ADMIN') then
328 p_start_index := 43;
329 p_stop_index := 43;
330 elsif(p_context_code = 'TC_W_TMPLT_DFLT_VAL_USR') then
331 p_start_index := 44;
332 p_stop_index := 44;
333 elsif(p_context_code = 'TC_W_TMPLT_FCNLTY') then
334 p_start_index := 45;
335 p_stop_index := 46;
336 elsif(p_context_code = 'TC_W_TMPLT_SV_ON_TCRD') then
337 p_start_index := 47;
338 p_stop_index := 47;
339 elsif(p_context_code = 'TS_PER_APPROVAL_STYLE') then
340 p_start_index := 48;
341 p_stop_index := 49;
342 elsif(p_context_code = 'TC_W_DISPLAY_ACC_BAL') then --This code has been newly added for PTO
343 p_start_index := 50;
344 p_stop_index := 53;
345 elsif(p_context_code = 'TC_W_PUBLIC_TEMPLATE') then --These indices have been newly added for the
346 p_start_index := 54; --Public Templates Enhancement.
347 p_stop_index := 63;
348 elsif(p_context_code = 'TS_PER_VALIDATE_ON_SAVE') then--added for DA Enhancement
349 p_start_index := 64;
350 p_stop_index := 65;
351 end if;
352 else
353 if(p_context_code = 'TS_PER_APPLICATION_SET') then
354 p_start_index := 1;
355 p_stop_index := 1;
356 elsif(p_context_code = 'TC_W_TCRD_ST_ALW_EDITS') then
357 p_start_index := 2;
358 p_stop_index := 3;
359 elsif(p_context_code = 'TS_PER_MME_TIME_ENTRY_RULES') then
360 p_start_index := 4;
361 p_stop_index := 4;
362 elsif(p_context_code = 'TS_PER_ELP_RULES') then
363 p_start_index := 5;
364 p_stop_index := 5;
365 elsif(p_context_code = 'TC_W_RULES_EVALUATION') then
366 p_start_index := 6;
367 p_stop_index := 7;
368 elsif(p_context_code = 'TS_PER_AUDIT_REQUIREMENTS') then
369 p_start_index := 8;
370 p_stop_index := 8;
371 elsif(p_context_code = 'TS_PER_VALIDATE_ON_SAVE') then
372 p_start_index := 9;
373 p_stop_index := 9;
374 elsif(p_context_code = 'TS_PER_DAYS_TO_HOURS') then
375 p_start_index := 10;
376 p_stop_index := 11;
377 end if;
378
379 end if; -- is this for timecard, or deposit wrapper
380
381 End find_name_indices_for_context;
382
383 function set_property_value
384 (p_column in varchar2
385 ,p_preference in HXC_PREFERENCE_EVALUATION.t_pref_table_row
386 ) return varchar2 is
387
388 l_prop_value VARCHAR2(2000) :='';
389
390 begin
391
392 if (p_column = 'ATTRIBUTE1') then
393 l_prop_value := p_preference.attribute1;
394 elsif (p_column = 'ATTRIBUTE2') then
395 l_prop_value := p_preference.attribute2;
396 elsif (p_column = 'ATTRIBUTE3') then
397 l_prop_value := p_preference.attribute3;
398 elsif (p_column = 'ATTRIBUTE4') then
399 l_prop_value := p_preference.attribute4;
400 elsif (p_column = 'ATTRIBUTE5') then
401 l_prop_value := p_preference.attribute5;
402 elsif (p_column = 'ATTRIBUTE6') then
403 l_prop_value := p_preference.attribute6;
404 elsif (p_column = 'ATTRIBUTE7') then
405 l_prop_value := p_preference.attribute7;
406 elsif (p_column = 'ATTRIBUTE8') then
407 l_prop_value := p_preference.attribute8;
408 elsif (p_column = 'ATTRIBUTE9') then
409 l_prop_value := p_preference.attribute9;
410 elsif (p_column = 'ATTRIBUTE10') then
411 l_prop_value := p_preference.attribute10;
412 elsif (p_column = 'ATTRIBUTE11') then
413 l_prop_value := p_preference.attribute11;
414 elsif (p_column = 'ATTRIBUTE12') then
415 l_prop_value := p_preference.attribute12;
416 elsif (p_column = 'ATTRIBUTE13') then
417 l_prop_value := p_preference.attribute13;
418 elsif (p_column = 'ATTRIBUTE14') then
419 l_prop_value := p_preference.attribute14;
420 elsif (p_column = 'ATTRIBUTE15') then
421 l_prop_value := p_preference.attribute15;
422 elsif (p_column = 'ATTRIBUTE16') then
423 l_prop_value := p_preference.attribute16;
424 elsif (p_column = 'ATTRIBUTE17') then
425 l_prop_value := p_preference.attribute17;
426 elsif (p_column = 'ATTRIBUTE18') then
427 l_prop_value := p_preference.attribute18;
428 elsif (p_column = 'ATTRIBUTE19') then
429 l_prop_value := p_preference.attribute19;
430 elsif (p_column = 'ATTRIBUTE20') then
431 l_prop_value := p_preference.attribute20;
432 elsif (p_column = 'ATTRIBUTE21') then
433 l_prop_value := p_preference.attribute21;
434 elsif (p_column = 'ATTRIBUTE22') then
435 l_prop_value := p_preference.attribute22;
436 elsif (p_column = 'ATTRIBUTE23') then
437 l_prop_value := p_preference.attribute23;
438 elsif (p_column = 'ATTRIBUTE24') then
439 l_prop_value := p_preference.attribute24;
440 elsif (p_column = 'ATTRIBUTE25') then
441 l_prop_value := p_preference.attribute25;
442 elsif (p_column = 'ATTRIBUTE26') then
443 l_prop_value := p_preference.attribute26;
444 elsif (p_column = 'ATTRIBUTE27') then
445 l_prop_value := p_preference.attribute27;
446 elsif (p_column = 'ATTRIBUTE28') then
447 l_prop_value := p_preference.attribute28;
448 elsif (p_column = 'ATTRIBUTE29') then
449 l_prop_value := p_preference.attribute29;
450 elsif (p_column = 'ATTRIBUTE30') then
451 l_prop_value := p_preference.attribute30;
452 end if;
453
454 return l_prop_value;
455
456 end set_property_value;
457
458 --
459 -- 115.31
460 -- Modified to include the assignment status type
461 -- Also changed ordering to support fast decision
462 -- in begin_approval to determine whether to 'approve
463 -- on submit'
464 procedure get_assignment_information
465 (p_resource_id in number,
466 p_props in out nocopy hxc_timecard_prop_table_type) is
467 -- fix v115.18 bug no. 3491084
468 -- New boolean
469 l_asg_exists BOOLEAN:= FALSE;
470
471 cursor c_assignment_info(p_pid in number) is
472 select asg.assignment_id,
473 asg.assignment_number,
474 asg.effective_start_date,
475 asg.effective_end_date,
476 nvl(ast.per_system_status,'NoSystemStatus') assignment_status_type
477 from per_all_assignments_f asg,
478 per_assignment_status_types ast
479 where asg.person_id = p_pid
480 and asg.assignment_type in ('E','C')
481 and asg.primary_flag = 'Y'
482 and asg.assignment_status_type_id = ast.assignment_status_type_id;
483
484 l_asg_index binary_integer := 0;
485
486 l_assignment_id asg_id;
487 l_assignment_number asg_number;
488 l_assignment_start asg_date;
489 l_assignment_end asg_date;
490 l_assignment_status asg_status;
491
492 l_user_person_id NUMBER; -- Bug 6350637 --
493 is_self_service BOOLEAN; -- Bug 6350637 --
494
495 begin
496
497 -- Bug 6350637 --
498 -- Select the resource id who is depositing the timecard
499 -- If the user is submitting timecard for self, then set
500 -- flag is_self_service
501
502 SELECT employee_id
503 INTO l_user_person_id
504 FROM fnd_user
505 WHERE user_id = fnd_global.user_id;
506
507 IF(l_user_person_id = p_resource_id)
508 THEN
509 is_self_service := TRUE;
510 END IF;
511 -- Bug 6350637 --
512
513 for asg_rec in c_assignment_info(p_resource_id) loop
514
515 -- Bug 6350637 --
516 -- If user is entering time for self, then make sure
517 -- assignments being populated are all active assignments.
518
519 IF (is_self_service)
520 THEN
521 IF ( asg_rec.assignment_status_type IN ('ACTIVE_ASSIGN','ACTIVE_CWK'))
522 THEN
523 l_asg_index := l_asg_index + 1;
524 l_assignment_id(l_asg_index) := asg_rec.assignment_id;
525 l_assignment_number(l_asg_index) := asg_rec.assignment_number;
526 l_assignment_start(l_asg_index) := asg_rec.effective_start_date;
527 l_assignment_end(l_asg_index) := asg_rec.effective_end_date;
528 l_assignment_status(l_asg_index) := to_char(asg_rec.assignment_status_type);
529 -- Set the Boolean in case assignment information exists.
530 l_asg_exists := TRUE;
531 END IF;
532 ELSE
533 -- If user is timekeeper or linemanager, he might want
534 -- non-active assignments also.
535 l_asg_index := l_asg_index + 1;
536 l_assignment_id(l_asg_index) := asg_rec.assignment_id;
537 l_assignment_number(l_asg_index) := asg_rec.assignment_number;
538 l_assignment_start(l_asg_index) := asg_rec.effective_start_date;
539 l_assignment_end(l_asg_index) := asg_rec.effective_end_date;
540 l_assignment_status(l_asg_index) := to_char(asg_rec.assignment_status_type);
541 -- Set the Boolean in case assignment information exists.
542 l_asg_exists := TRUE;
543 END IF;
544 -- Bug 6350637 --
545 end loop;
546 if not l_asg_exists then
547 -- Add error
548 --
549 -- Initialize the message stack
550 --
551 fnd_msg_pub.initialize;
552 fnd_message.set_name('HXC','HXC_NOT_VALID_ASSIGNMENT');
553 fnd_msg_pub.add;
554 else
555 --
556 -- Populate property structure
557 --
558 for i in 1..l_asg_index loop
559 p_props.extend();
560 p_props(p_props.last) := hxc_timecard_prop_type
561 ('ResourceAssignmentId',
562 null,
563 l_assignment_start(i),
564 l_assignment_end(i),
565 l_assignment_id(i)
566 );
567 end loop;
568 for i in 1..l_asg_index loop
569 p_props.extend();
570 p_props(p_props.last) := hxc_timecard_prop_type
571 ('ResourceAssignmentNumber',
572 null,
573 l_assignment_start(i),
574 l_assignment_end(i),
575 l_assignment_number(i)
576 );
577 end loop;
578 for i in 1..l_asg_index loop
579 p_props.extend();
580 p_props(p_props.last) := hxc_timecard_prop_type
581 ('ResourceAssignmentStartDate',
582 null,
583 l_assignment_start(i),
584 l_assignment_end(i),
585 to_char(l_assignment_start(i),'YYYY/MM/DD')
586 );
587 end loop;
588 for i in 1..l_asg_index loop
589 p_props.extend();
590 p_props(p_props.last) := hxc_timecard_prop_type
591 ('ResourceAssignmentEndDate',
592 null,
593 l_assignment_start(i),
594 l_assignment_end(i),
595 to_char(l_assignment_end(i),'YYYY/MM/DD')
596 );
597 end loop;
598 for i in 1..l_asg_index loop
599 p_props.extend();
600 p_props(p_props.last) := hxc_timecard_prop_type
601 ('ResourceAssignmentStatusType',
602 null,
603 l_assignment_start(i),
604 l_assignment_end(i),
605 l_assignment_status(i)
606 );
607 end loop;
608 end if;
609 END get_assignment_information;
610
611
612 FUNCTION setup_mo_global_params ( p_resource_id in number) return number is
613 l_operating_unit_id NUMBER(15);
614 BEGIN
615
616 -- Derive the operating unit for the resource
617 -- ONLY CALL THIS FOR R12 WHEN API AVAILABLE
618 Begin
619 l_operating_unit_id :=
620 hr_organization_api.get_operating_unit
621 (p_effective_date => sysdate
622 ,p_person_id => p_resource_id);
623
624 exception
625 when others then
626 l_operating_unit_id := fnd_profile.value('ORG_ID');
627 end;
628
629 -- now set the operating unit context
630
631 -- ONLY CALL THIS FOR RELEASE 12
632
633 mo_global.init('HXC');
634
635 mo_global.set_policy_context ( 'S', l_operating_unit_id );
636
637 return l_operating_unit_id;
638
639 END setup_mo_global_params;
640
641
642 PROCEDURE get_org_id ( p_resource_id in number
643 ,p_props in out nocopy hxc_timecard_prop_table_type) is
644
645 l_operating_unit_id NUMBER(15);
646
647 BEGIN
648
649 if g_debug then
650 hr_utility.trace('Entering get org id : resource id is '||to_char(p_resource_id));
651 end if;
652
653 l_operating_unit_id := setup_mo_global_params(p_resource_id);
654
655 -- now add the operating unit to the timecard props record
656
657 p_props.extend();
658
659 p_props(p_props.last) := hxc_timecard_prop_type
660 ('ResourceOrgId'
661 ,null
662 ,hr_general.start_of_time
663 ,hr_general.end_of_time
664 ,l_operating_unit_id
665 );
666 if g_debug then
667 hr_utility.trace('Leaving get_org id : org id is '||to_char(l_operating_unit_id));
668 end if;
669
670 END get_org_id;
671
672
673 procedure get_period_information
674 (p_period_id in NUMBER
675 ,p_start_date in date
676 ,p_end_date in date
677 ,p_props in out nocopy hxc_timecard_prop_table_type) is
678
679 cursor c_period_info
680 (p_recurring_period_id in HXC_RECURRING_PERIODS.RECURRING_PERIOD_ID%TYPE) is
681 select rp.period_type
682 ,rp.duration_in_days
683 ,substr(fnd_date.date_to_canonical(rp.start_date),1,50) start_date
684 from hxc_recurring_periods rp
685 where rp.recurring_period_id = p_recurring_period_id;
686
687 cursor c_number_per_year
688 (p_type in per_time_periods.period_type%type) is
689 select number_per_fiscal_year
690 from per_time_period_types
691 where period_type = p_type;
692
693 l_period_type PER_TIME_PERIOD_TYPES.period_type%TYPE;
694 l_duration_in_days HXC_RECURRING_PERIODS.DURATION_IN_DAYS%TYPE;
695 l_number_per_fiscal_year PER_TIME_PERIOD_TYPES.NUMBER_PER_FISCAL_YEAR%TYPE;
696 l_start_date VARCHAR2(50);
697
698 BEGIN
699
700 if((g_period_list_cache.exists(p_period_id))AND(p_props.exists(g_period_list_cache(p_period_id)))) then
701
702 l_period_type := p_props(g_period_list_cache(p_period_id)).property_value;
703 l_duration_in_days := p_props((g_period_list_cache(p_period_id)+1)).property_value;
704 l_number_per_fiscal_year := p_props((g_period_list_cache(p_period_id)+2)).property_value;
705 l_start_date := p_props((g_period_list_cache(p_period_id)+3)).property_value;
706
707 else
708
709 open c_period_info(p_period_id);
710 fetch c_period_info into l_period_type, l_duration_in_days, l_start_date;
711
712 if(c_period_info%NOTFOUND) then
713 close c_period_info;
714 FND_MESSAGE.SET_NAME('HXC','HXC_NO_PERIOD_FOR_PREF');
715 FND_MESSAGE.RAISE_ERROR;
716 end if;
717 close c_period_info;
718
719 if(l_period_type is not null) then
720 open c_number_per_year(l_period_type);
721 fetch c_number_per_year into l_number_per_fiscal_year;
722 close c_number_per_year;
723 end if;
724
725 end if;
726
727 p_props.extend();
728 if(NOT g_period_list_cache.exists(p_period_id)) then
729 g_period_list_cache(p_period_id) := p_props.last;
730 end if;
731
732 p_props(p_props.last) := hxc_timecard_prop_type
733 ('PeriodType'
734 ,null
735 ,p_start_date
736 ,p_end_date
737 ,l_period_type
738 );
739
740 p_props.extend();
741 p_props(p_props.last) := hxc_timecard_prop_type
742 ('PeriodDurationInDays'
743 ,null
744 ,p_start_date
745 ,p_end_date
746 ,l_duration_in_days
747 );
748
749 p_props.extend();
750 p_props(p_props.last) := hxc_timecard_prop_type
751 ('PeriodNumberPerFiscalYear'
752 ,null
753 ,p_start_date
754 ,p_end_date
755 ,l_number_per_fiscal_year
756 );
757
758 p_props.extend();
759 p_props(p_props.last) := hxc_timecard_prop_type
760 ('PeriodStartDate'
761 ,null
762 ,p_start_date
763 ,p_end_date
764 ,l_start_date
765 );
766
767 end get_period_information;
768
769 procedure get_personal_information
770 (p_resource_id in number
771 ,p_props in out nocopy hxc_timecard_prop_table_type) is
772
773 cursor c_full_name(p_pid in number) is
774 select distinct full_name, effective_start_date, effective_end_date
775 from per_all_people_f
776 where person_id = p_pid;
777
778 l_index number := p_props.last;
779
780 BEGIN
781
782 for name_rec in c_full_name(p_resource_id) loop
783
784 p_props.extend();
785
786 p_props(p_props.last) := hxc_timecard_prop_type
787 ('ResourceIdentifierName'
788 ,null
789 ,name_rec.effective_start_date
790 ,name_rec.effective_end_date
791 ,name_rec.full_name
792 );
793 end loop;
794
795 END get_personal_information;
796
797 function get_alias_reference_object
798 (p_alias_definition_id in hxc_alias_definitions.alias_definition_id%type)
799 RETURN varchar2 is
800
801 cursor c_ref_obj
802 (p_id in hxc_alias_definitions.alias_definition_id%type) is
803 select ty.reference_object
804 from hxc_alias_types ty, hxc_alias_definitions ad
805 where ad.alias_definition_id = p_id
806 and ty.alias_type_id = ad.alias_type_id;
807
808 l_reference_object hxc_alias_types.reference_object%type;
809
810 begin
811
812 open c_ref_obj(p_alias_definition_id);
813 fetch c_ref_obj into l_reference_object;
814 if(c_ref_obj%notfound) then
815 close c_ref_obj;
816 fnd_message.set_name('HXC','HXC_NO_ALIAS_TYPE');
817 fnd_message.set_token('DEF_ID',to_char(p_alias_definition_id));
818 fnd_message.raise_error;
819 else
820 close c_ref_obj;
821 end if;
822
823 return l_reference_object;
824
825 end get_alias_reference_object;
826
827 procedure include_alias_type
828 (p_alias_definition_id in number
829 ,p_alias_property_number in varchar2
830 ,p_date_from in date
831 ,p_date_to in date
832 ,p_props in out nocopy hxc_timecard_prop_table_type
833 ) is
834
835 l_alias_reference hxc_alias_types.reference_object%type;
836 begin
837
838 l_alias_reference := get_alias_reference_object(p_alias_definition_id);
839 if(l_alias_reference is not null) then
840
841 p_props.extend();
842 p_props(p_props.last) := hxc_timecard_prop_type
843 (c_alias_type||p_alias_property_number
844 ,null
845 ,p_date_from
846 ,p_date_to
847 ,l_alias_reference
848 );
849
850 end if;
851
852 end include_alias_type;
853
854 function earliest_date
855 (p_resource_id in NUMBER) return date is
856
857 l_date date;
858
859 begin
860
861 select min(effective_start_date)
862 into l_date
863 from per_all_assignments_f
864 where person_id = p_resource_id;
865
866 return l_date;
867
868 end;
869 --
870 -- Overloaded for the middle tier
871 --
872 procedure get_preference_properties
873 (p_validate in VARCHAR2
874 ,p_resource_id in NUMBER
875 ,p_timecard_start_time in VARCHAR2
876 ,p_timecard_stop_time in VARCHAR2
877 ,p_property_table out nocopy HXC_TIMECARD_PROP_TABLE_TYPE
878 ) is
879
880 l_messages hxc_message_table_type;
881 l_timecard_start_time date := sysdate;
882 l_timecard_stop_time date := sysdate;
883
884 Begin
885
886 l_messages := hxc_message_table_type();
887 if(length(p_timecard_start_time)>5) then
888 l_timecard_start_time := fnd_date.canonical_to_date(p_timecard_start_time);
889 l_timecard_stop_time := fnd_date.canonical_to_date(p_timecard_stop_time);
890 end if;
891
892
893 get_preference_properties
894 (p_validate => p_validate
895 ,p_resource_id => p_resource_id
896 ,p_timecard_start_time => l_timecard_start_time
897 ,p_timecard_stop_time => l_timecard_stop_time
898 ,p_for_timecard => true
899 ,p_messages => l_messages
900 ,p_property_table => p_property_table
901 );
902
903 hxc_timecard_message_helper.processErrors(l_messages);
904
905 End get_preference_properties;
906
907 --
908 -- Overloaded for the middle tier
909 --
910 procedure get_preference_properties
911 (p_validate in VARCHAR2,
912 p_resource_id in NUMBER,
913 p_timecard_start_time in VARCHAR2,
914 p_timecard_stop_time in VARCHAR2,
915 p_property_table out nocopy HXC_TIMECARD_PROP_TABLE_TYPE,
916 p_messages out nocopy HXC_MESSAGE_TABLE_TYPE
917 ) is
918
919 l_timecard_start_time date;
920 l_timecard_stop_time date;
921
922 Begin
923 if(p_messages is null) then
924 p_messages := hxc_message_table_type();
925 end if;
926
927 if(length(p_timecard_start_time)>5) then
928 l_timecard_start_time := fnd_date.canonical_to_date(p_timecard_start_time);
929 l_timecard_stop_time := fnd_date.canonical_to_date(p_timecard_stop_time);
930 else
931 l_timecard_start_time := sysdate;
932 l_timecard_stop_time := sysdate;
933 end if;
934
935 get_preference_properties
936 (p_validate => p_validate,
937 p_resource_id => p_resource_id,
938 p_timecard_start_time => l_timecard_start_time,
939 p_timecard_stop_time => l_timecard_stop_time,
940 p_for_timecard => true,
941 p_messages => p_messages,
942 p_property_table => p_property_table
943 );
944
945 End get_preference_properties;
946
947 procedure get_preference_properties
948 (p_validate in VARCHAR2
949 ,p_resource_id in NUMBER
950 ,p_timecard_start_time in date
951 ,p_timecard_stop_time in date
952 ,p_for_timecard in BOOLEAN
953 ,p_messages in out nocopy hxc_message_table_type
954 ,p_property_table out nocopy HXC_TIMECARD_PROP_TABLE_TYPE
955 ) is
956 Begin
957
958 get_preference_properties
959 (p_validate => p_validate
960 ,p_resource_id => p_resource_id
961 ,p_timecard_start_time => p_timecard_start_time
962 ,p_timecard_stop_time => p_timecard_stop_time
963 ,p_for_timecard => p_for_timecard
964 ,p_timecard_bb_id => null
965 ,p_timecard_bb_ovn => null
966 ,p_messages => p_messages
967 ,p_property_table => p_property_table
968 );
969
970 end get_preference_properties;
971
972 procedure get_preference_properties
973 (p_validate in VARCHAR2
974 ,p_resource_id in NUMBER
975 ,p_timecard_start_time in date
976 ,p_timecard_stop_time in date
977 ,p_for_timecard in BOOLEAN
978 ,p_timecard_bb_id in hxc_time_building_blocks.time_building_block_id%type
979 ,p_timecard_bb_ovn in hxc_time_building_blocks.object_version_number%type
980 ,p_messages in out nocopy hxc_message_table_type
981 ,p_property_table out nocopy HXC_TIMECARD_PROP_TABLE_TYPE
982 ) is
983
984 l_pref_table HXC_PREFERENCE_EVALUATION.T_PREF_TABLE;
985
986 l_prop_table t_prop_table;
987 l_property_value hxc_pref_hierarchies.attribute1%type;
988 l_property_name varchar2(60);
989 l_property_column fnd_descr_flex_column_usages.application_column_name%type;
990 l_index NUMBER;
991 l_prop_index NUMBER :=1;
992 l_date DATE;
993
994 l_name_start_index number;
995 l_name_stop_index number;
996 l_name_index number;
997 l_name varchar2(60);
998
999 l_proc VARCHAR2(30) := 'get_preference_properties';
1000
1001 l_recurring_period_id NUMBER;
1002
1003 begin
1004 g_property_definitions.delete;
1005 cache_property_definitions(p_for_timecard);
1006
1007 g_period_list_cache.delete;
1008
1009 if(p_messages is null) then
1010 p_messages := hxc_message_table_type();
1011 end if;
1012
1013 p_property_table := hxc_timecard_prop_table_type();
1014
1015 -- l_date := earliest_date(p_resource_id);
1016
1017 l_date := to_date('1990/01/01','YYYY/MM/DD');
1018
1019 -- Start by getting all the preferences for the resource
1020 -- over all time!
1021
1022 HXC_PREFERENCE_EVALUATION.RESOURCE_PREFERENCES
1023 (p_resource_id => p_resource_id
1024 ,p_start_evaluation_date => l_date
1025 ,p_end_evaluation_date => to_date('4712/12/31','YYYY/MM/DD')
1026 ,p_pref_table => l_pref_table
1027 );
1028
1029 --
1030 -- Call Set Up validation, to ensure these preferences
1031 -- are valid. We pass null for the timecard id and ovn
1032 -- because at the stage this call is made we don't know
1033 -- these values. Not sure why the validation needs
1034 -- operation, but pass submit in anyway, this value
1035 -- isn't used in that package.
1036 --
1037
1038 if(p_validate = hxc_timecard.c_yes) then
1039 hxc_setup_validation_pkg.execute_otc_validation
1040 (p_operation => hxc_timecard.c_submit
1041 ,p_resource_id => p_resource_id
1042 ,p_timecard_bb_id => p_timecard_bb_id
1043 ,p_timecard_bb_ovn => p_timecard_bb_ovn
1044 ,p_start_date => p_timecard_start_time
1045 ,p_end_date => p_timecard_stop_time
1046 ,p_master_pref_table => l_pref_table
1047 ,p_messages => p_messages
1048 );
1049 end if;
1050
1051 --
1052 -- Record the earliest date we're using to obtain
1053 -- the preferences, this is currently used by
1054 -- the period list generation code.
1055 --
1056 p_property_table.extend();
1057 l_prop_index := p_property_table.last;
1058 p_property_table(l_prop_index) := hxc_timecard_prop_type
1059 ('ResourceEarliestAssignmentDate'
1060 ,null
1061 ,to_date('0001/01/01','YYYY/MM/DD')
1062 ,to_date('4712/12/31','YYYY/MM/DD')
1063 ,to_char(l_date,'YYYY/MM/DD')
1064 );
1065
1066 -- Next for each of the records in the preference table
1067 -- load the properties table
1068
1069 l_index := l_pref_table.first;
1070
1071 LOOP
1072
1073 EXIT WHEN NOT l_pref_table.exists(l_index);
1074
1075 find_name_indices_for_context
1076 (p_context_code => l_pref_table(l_index).preference_code
1077 ,p_for_timecard => p_for_timecard
1078 ,p_start_index => l_name_start_index
1079 ,p_stop_index => l_name_stop_index
1080 );
1081
1082 if(l_name_start_index <> hr_api.g_number) then
1083
1084 For l_name_index in l_name_start_index..l_name_stop_index Loop
1085
1086 l_property_name := g_property_definitions(l_name_index).property_name;
1087 l_property_column := g_property_definitions(l_name_index).column_name;
1088
1089 --
1090 -- We use last here, because inside the loop, we call the
1091 -- period information function, which can add additional
1092 -- records to the prop table, which is safer than just
1093 -- adding 1.
1094 --
1095
1096 l_property_value := set_property_value(l_property_column, l_pref_table(l_index));
1097
1098 if(l_property_value is not null) then
1099
1100 p_property_table.extend();
1101 l_prop_index := p_property_table.last;
1102
1103 if(l_property_name = 'TcWTcrdStAlwEditsPastNumber') then
1104 l_property_name := 'EffectiveTimecardPeriodPastDate';
1105 l_property_value := to_char((sysdate-to_number(l_property_value)),'YYYY/MM/DD');
1106 end if;
1107 if(l_property_name = 'TcWTcrdStAlwEditsFutureNumber') then
1108 l_property_name := 'EffectiveTimecardPeriodFutureDate';
1109 l_property_value := to_char((sysdate+to_number(l_property_value)),'YYYY/MM/DD');
1110 end if;
1111
1112 p_property_table(l_prop_index) := hxc_timecard_prop_type
1113 (l_property_name
1114 ,null
1115 ,l_pref_table(l_index).start_date
1116 ,l_pref_table(l_index).end_date
1117 ,l_property_value
1118 );
1119
1120 if(l_property_name = 'TcWTcrdPeriodTimecardPeriods') then
1121
1122 get_period_information
1123 (p_period_id => l_property_value
1124 ,p_start_date => l_pref_table(l_index).start_date
1125 ,p_end_date => l_pref_table(l_index).end_date
1126 ,p_props=> p_property_table);
1127
1128 end if;
1129
1130 if(substr(l_property_name,1,27) = c_alias_name)then
1131
1132 include_alias_type
1133 (to_number(l_property_value)
1134 ,substr(l_property_name,28)
1135 ,l_pref_table(l_index).start_date
1136 ,l_pref_table(l_index).end_date
1137 ,p_property_table
1138 );
1139 end if;
1140
1141 end if; -- does the property have a value?
1142
1143 END LOOP;
1144
1145 end if; -- did we find the index we wanted.
1146
1147 l_index := l_pref_table.next(l_index);
1148
1149 END LOOP;
1150
1151
1152 get_personal_information
1153 (p_resource_id
1154 ,p_property_table
1155 );
1156
1157 get_org_id ( p_resource_id, p_property_table );
1158 -- 115.31
1159 -- Made this the last call as the order of the
1160 -- properties now matters to hxc_timecard_approval
1161 get_assignment_information
1162 (p_resource_id
1163 ,p_property_table
1164 );
1165
1166 end get_preference_properties;
1167
1168 Function find_property_value
1169 (p_props in HXC_TIMECARD_PROP_TABLE_TYPE
1170 ,p_name in varchar2
1171 ,p_code in varchar2
1172 ,p_segment in number
1173 ,p_start_date in date
1174 ,p_stop_date in date
1175 ) return varchar2 is
1176
1177 l_eval_date date;
1178 l_property_value hxc_pref_hierarchies.attribute1%type := null;
1179
1180 Begin
1181
1182 l_property_value := find_property_value(p_props,p_name,p_code,p_segment,p_start_date);
1183
1184 if(l_property_value is null) then
1185 l_property_value := find_property_value(p_props,p_name,p_code,p_segment,p_stop_date);
1186 if(l_property_value is null) then
1187 --
1188 -- Loop across the days looking for a value
1189 --
1190 l_eval_date := p_start_date + 1;
1191 Loop
1192 Exit when ((l_property_value is not null) OR (trunc(p_stop_date)-trunc(l_eval_date)<1));
1193 l_property_value := find_property_value(p_props,p_name,p_code,p_segment,l_eval_date);
1194 l_eval_date := l_eval_date + 1;
1195 End Loop;
1196 end if;
1197 end if;
1198
1199 return l_property_value;
1200
1201 End find_property_value;
1202
1203 Function find_property_value
1204 (p_props in HXC_TIMECARD_PROP_TABLE_TYPE
1205 ,p_name in varchar2
1206 ,p_code in hxc_pref_hierarchies.code%type
1207 ,p_segment in number
1208 ,p_date in date
1209 ) return varchar2 is
1210
1211 cursor c_prop_name
1212 (p_code in varchar2
1213 ,p_segment in number
1214 ) is
1215 select replace(initcap(replace(descriptive_flex_context_code,'_',' ')),' ')
1216 ||replace(initcap(replace(end_user_column_name,'_',' ')),' ') property_name
1217 from fnd_descr_flex_column_usages
1218 where descriptive_flexfield_name = 'OTC PREFERENCES'
1219 and descriptive_flex_context_code = p_code
1220 and application_column_name = 'ATTRIBUTE'||to_char(p_segment)
1221 and application_id = 809;
1222
1223 l_property_value hxc_pref_hierarchies.attribute1%type;
1224 l_property_name varchar2(240) := null;
1225 l_index number;
1226 l_date_difference number;
1227 l_close_value hxc_pref_hierarchies.attribute1%type;
1228
1229 Begin
1230
1231 if(p_name is null) then
1232 open c_prop_name(p_code,p_segment);
1233 fetch c_prop_name into l_property_name;
1234
1235 if(c_prop_name%notfound) then
1236 close c_prop_name;
1237 fnd_message.set_name('HXC','HXC_UNKNOWN_PROP');
1238 fnd_message.raise_error;
1239 else
1240 close c_prop_name;
1241 end if;
1242 else
1243 l_property_name := p_name;
1244 end if;
1245
1246 l_index := p_props.first;
1247 Loop
1248 Exit when ((not p_props.exists(l_index)) or (l_property_value is not null));
1249
1250 if (p_props(l_index).property_name = l_property_name) then
1251 if(p_date between p_props(l_index).date_from and p_props(l_index).date_to) then
1252 l_property_value := p_props(l_index).property_value;
1253 else
1254 if(l_property_value is null) then
1255 if(
1256 (abs(p_date - p_props(l_index).date_from) < l_date_difference)
1257 OR
1258 (l_date_difference = hr_api.g_number)
1259 ) then
1260 l_close_value := p_props(l_index).property_value;
1261 l_date_difference := abs(p_date - p_props(l_index).date_from);
1262 end if;
1263 end if;
1264 end if;
1265 end if;
1266 l_index := p_props.next(l_index);
1267 End Loop;
1268
1269 if(l_property_value is null) then
1270
1271 l_property_value := l_close_value;
1272
1273 end if;
1274
1275 return l_property_value;
1276
1277 End find_property_value;
1278
1279 END hxc_timecard_properties;