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