[Home] [Help]
PACKAGE BODY: APPS.HXC_PROJ_PAY_DEP_VALIDATION
Source
1 PACKAGE BODY HXC_PROJ_PAY_DEP_VALIDATION AS
2 /* $Header: hxcprojpaydepval.pkb 120.3 2011/05/06 14:47:10 pnshukla noship $ */
3
4 g_debug boolean := hr_utility.debug_enabled;
5
6 PROCEDURE verify_proj_pay_setup(p_blocks IN HXC_BLOCK_TABLE_TYPE,
7 p_attributes IN HXC_ATTRIBUTE_TABLE_TYPE,
8 p_messages IN OUT NOCOPY HXC_MESSAGE_TABLE_TYPE)
9 IS
10
11 l_resource_id hxc_timecard_summary.resource_id%type;
12 l_start_time hxc_timecard_summary.start_time%type;
13 l_stop_time hxc_timecard_summary.stop_time%type;
14
15 l_proj_pay_integration VARCHAR2(10);
16
17 l_pref_table hxc_preference_evaluation.t_pref_table;
18 l_index NUMBER;
19
20 l_timecard_id NUMBER := 0;
21 l_timecard_ovn NUMBER := 0;
22
23 PROCEDURE verify_atribtues(p_attributes IN HXC_ATTRIBUTE_TABLE_TYPE,
24 p_messages IN OUT NOCOPY HXC_MESSAGE_TABLE_TYPE)
25 IS
26 l_index NUMBER;
27 BEGIN
28
29 IF g_debug THEN
30 hr_utility.trace('PA-PAY> In hxc_proj_pay_dep_validation.verify_atribtues');
31 END IF;
32
33 l_index := p_attributes.first;
34 LOOP
35 EXIT WHEN NOT p_attributes.exists(l_index);
36 IF p_attributes(l_index).ATTRIBUTE_CATEGORY = 'PROJ-PAY-ATTR' THEN
37 IF g_debug THEN
38 hr_utility.trace('PA-PAY> Wrong layout');
39 END IF;
40 hxc_timecard_message_helper.adderrortocollection (
41 p_messages => p_messages
42 , p_message_name => 'HXC_PROJ_PAY_WRONG_LAYOUT'
43 , p_message_level => 'ERROR'
44 , p_message_field => NULL
45 , p_message_tokens => NULL
46 , p_application_short_name => 'HXC'
47 , p_time_building_block_id => NULL
48 , p_time_building_block_ovn => NULL
49 , p_time_attribute_id => NULL
50 , p_time_attribute_ovn => NULL );
51 EXIT;
52 END IF;
53
54 l_index := p_attributes.next(l_index);
55 END LOOP;
56
57 END verify_atribtues;
58
59 PROCEDURE validate_timecard_edit(p_resource_id IN NUMBER,
60 p_start_time IN DATE,
61 p_timecard_id IN NUMBER,
62 p_proj_pay_integration IN VARCHAR2,
63 p_blocks IN HXC_BLOCK_TABLE_TYPE,
64 p_messages IN OUT NOCOPY HXC_MESSAGE_TABLE_TYPE)
65 IS
66
67 l_count NUMBER := 0;
68 l_retrieval_process_id hxc_retrieval_processes.retrieval_process_id%type;
69
70 l_index NUMBER;
71 l_active_building_block_ids VARCHAR2(32767) := null;
72 l_sql_stmt VARCHAR2(32767);
73
74 BEGIN
75
76 IF g_debug THEN
77 hr_utility.trace('PA-PAY> In hxc_proj_pay_dep_validation.validate_timecard_edit');
78 END IF;
79
80
81 IF p_proj_pay_integration = 'Y' THEN
82 -- check if timecard data exists in hxc_pay_pa_link_all table
83 -- if timecard exists return
84 -- if timecard not exists , check whether data has ever retrieved
85 -- if timecard data is transferred to any receipient application raise an error or else return.
86 BEGIN
87
88 SELECT 1
89 INTO l_count
90 FROM hxc_pay_pa_link_all
91 WHERE timecard_id = p_timecard_id
92 AND rownum < 2 ;
93
94 EXCEPTION
95 WHEN NO_DATA_FOUND THEN
96 l_count := 0; -- TIMECARD NOT EXISTS
97 END;
98
99 IF g_debug THEN
100 hr_utility.trace('PA-PAY> Projects Payroll integration is enabled ::'||p_proj_pay_integration);
101 hr_utility.trace('PA-PAY> Number of records exists in hxc_pay_pa_link_all table ::'||l_count);
102 END IF;
103
104 IF l_count > 0 THEN
105 RETURN;
106 END IF;
107
108
109 SELECT retrieval_process_id
110 INTO l_retrieval_process_id
111 FROM hxc_retrieval_processes
112 WHERE name = 'BEE Retrieval Process';
113
114 l_index := p_blocks.first;
115
116 LOOP
117 EXIT WHEN NOT p_blocks.exists(l_index);
118 IF p_blocks(l_index).scope = 'DETAIL'
119 AND FND_DATE.canonical_to_date(p_blocks(l_index).DATE_TO) = hr_general.end_of_time
120 THEN
121 IF l_active_building_block_ids IS NULL THEN
122 l_active_building_block_ids := p_blocks(l_index).time_building_block_id;
123 ELSE
124 l_active_building_block_ids := l_active_building_block_ids || ','||p_blocks(l_index).time_building_block_id;
125 END IF;
126 END IF;
127 l_index := p_blocks.next(l_index);
128 END LOOP;
129
130 IF g_debug THEN
131 hr_utility.trace('PA-PAY> l_active_detail_building_block_ids ::'||l_active_building_block_ids);
132 END IF;
133
134 l_sql_stmt :=
135
136 'SELECT 1
137 FROM hxc_transactions ht,
138 hxc_transaction_details htd
139 WHERE ht.transaction_process_id in ('||l_retrieval_process_id||', -1 )
140 AND ht.transaction_id = htd.transaction_id
141 AND ht.type = ''RETRIEVAL''
142 AND ht.status = ''SUCCESS''
143 AND htd.status = ''SUCCESS''
144 AND htd.time_building_block_id IN ('|| l_active_building_block_ids ||')
145 AND rownum > 2';
146
147 IF g_debug THEN
148 hr_utility.trace('PA-PAY> l_sql_stmt ::'||l_sql_stmt);
149 END IF;
150
151 BEGIN
152 EXECUTE IMMEDIATE l_sql_stmt
153 INTO l_count;
154 EXCEPTION
155 WHEN NO_DATA_FOUND THEN
156 l_count := 0; -- No data ever retrieved to recepient application
157 END;
158
159 IF l_count = 0 THEN
160 IF g_debug THEN
161 hr_utility.trace('PA-PAY> Timecard is never retrieved to payroll recepient application');
162 END IF;
163 END IF;
164
165 IF l_count > 0 THEN
166 IF g_debug THEN
167 hr_utility.trace('PA-PAY> Timecard is retrieved to recepient application.');
168 END IF;
169
170 hxc_timecard_message_helper.adderrortocollection (
171 p_messages => p_messages
172 , p_message_name => 'HXC_PROJ_PAY_INTG_DISABLED'
173 , p_message_level => 'ERROR'
174 , p_message_field => NULL
175 , p_message_tokens => NULL
176 , p_application_short_name => 'HXC'
177 , p_time_building_block_id => NULL
178 , p_time_building_block_ovn => NULL
179 , p_time_attribute_id => NULL
180 , p_time_attribute_ovn => NULL );
181 END IF;
182 ELSE
183 -- need to check whether data exists in hxc_pay_pa_link_all , if exists raise an error
184
185 BEGIN
186
187 SELECT 1
188 INTO l_count
189 FROM hxc_pay_pa_link_all
190 WHERE timecard_id = p_timecard_id
191 AND rownum < 2 ;
192
193 EXCEPTION
194 WHEN NO_DATA_FOUND THEN
195 RETURN;
196 END;
197
198
199 IF g_debug THEN
200 hr_utility.trace('PA-PAY> Projects Payroll integration is enabled ::'||p_proj_pay_integration);
201 hr_utility.trace('PA-PAY> Number of records exists in hxc_pay_pa_link_all table ::'||l_count);
202 END IF;
203
204 IF l_count > 0 THEN
205 hxc_timecard_message_helper.adderrortocollection (
206 p_messages => p_messages
207 , p_message_name => 'HXC_PROJ_PAY_INTG_ENABLED'
208 , p_message_level => 'ERROR'
209 , p_message_field => NULL
210 , p_message_tokens => NULL
211 , p_application_short_name => 'HXC'
212 , p_time_building_block_id => NULL
213 , p_time_building_block_ovn => NULL
214 , p_time_attribute_id => NULL
215 , p_time_attribute_ovn => NULL );
216 RETURN;
217 END IF;
218
219 END IF;
220 END validate_timecard_edit;
221
222
223 BEGIN
224
225 IF g_debug THEN
226 hr_utility.trace('PA-PAY> In hxc_proj_pay_dep_validation.verify_proj_pay_setup');
227 END IF;
228
229 IF g_debug THEN
230 hr_utility.trace('PA-PAY> ATTRIBUTES TABLE START ');
231 hr_utility.trace(' *****************');
232
233 l_index := p_attributes.first;
234 LOOP
235 EXIT WHEN NOT p_attributes.exists(l_index);
236 hr_utility.trace('BB id : '||p_attributes(l_index).BUILDING_BLOCK_ID
237 ||'['||p_attributes(l_index).BUILDING_BLOCK_OVN||']');
238
239 hr_utility.trace(' ATT CAT: '||p_attributes(l_index).ATTRIBUTE_CATEGORY);
240 hr_utility.trace(' ATTRIBUTE1: '||p_attributes(l_index).ATTRIBUTE1);
241 hr_utility.trace(' ATTRIBUTE2: '||p_attributes(l_index).ATTRIBUTE2);
242 hr_utility.trace(' ATTRIBUTE3: '||p_attributes(l_index).ATTRIBUTE3);
243 hr_utility.trace(' ATTRIBUTE4: '||p_attributes(l_index).ATTRIBUTE4);
244 hr_utility.trace(' ATTRIBUTE5: '||p_attributes(l_index).ATTRIBUTE5);
245 hr_utility.trace(' ATTRIBUTE6: '||p_attributes(l_index).ATTRIBUTE6);
246 hr_utility.trace(' ATTRIBUTE7: '||p_attributes(l_index).ATTRIBUTE7);
247 hr_utility.trace(' ATTRIBUTE8: '||p_attributes(l_index).ATTRIBUTE8);
248 hr_utility.trace(' ATTRIBUTE9: '||p_attributes(l_index).ATTRIBUTE9);
249 hr_utility.trace(' ATTRIBUTE10: '||p_attributes(l_index).ATTRIBUTE10);
250 hr_utility.trace(' ATTRIBUTE11: '||p_attributes(l_index).ATTRIBUTE11);
251 hr_utility.trace(' ATTRIBUTE12: '||p_attributes(l_index).ATTRIBUTE12);
252 hr_utility.trace(' ATTRIBUTE13: '||p_attributes(l_index).ATTRIBUTE13);
253 hr_utility.trace(' ATTRIBUTE14: '||p_attributes(l_index).ATTRIBUTE14);
254 hr_utility.trace(' ATTRIBUTE15: '||p_attributes(l_index).ATTRIBUTE15);
255 hr_utility.trace(' ATTRIBUTE16: '||p_attributes(l_index).ATTRIBUTE16);
256 hr_utility.trace(' ATTRIBUTE17: '||p_attributes(l_index).ATTRIBUTE17);
257 hr_utility.trace(' ATTRIBUTE18: '||p_attributes(l_index).ATTRIBUTE18);
258 hr_utility.trace(' ATTRIBUTE19: '||p_attributes(l_index).ATTRIBUTE19);
259 hr_utility.trace(' ATTRIBUTE20: '||p_attributes(l_index).ATTRIBUTE20);
260 hr_utility.trace(' ATTRIBUTE21: '||p_attributes(l_index).ATTRIBUTE21);
261 hr_utility.trace(' ATTRIBUTE22: '||p_attributes(l_index).ATTRIBUTE22);
262 hr_utility.trace(' ATTRIBUTE23: '||p_attributes(l_index).ATTRIBUTE23);
263 hr_utility.trace(' ATTRIBUTE24: '||p_attributes(l_index).ATTRIBUTE24);
264 hr_utility.trace(' ATTRIBUTE25: '||p_attributes(l_index).ATTRIBUTE25);
265 hr_utility.trace(' ATTRIBUTE26: '||p_attributes(l_index).ATTRIBUTE26);
266 hr_utility.trace(' ATTRIBUTE27: '||p_attributes(l_index).ATTRIBUTE27);
267 hr_utility.trace(' ATTRIBUTE28: '||p_attributes(l_index).ATTRIBUTE28);
268 hr_utility.trace(' ATTRIBUTE29: '||p_attributes(l_index).ATTRIBUTE29);
269 hr_utility.trace(' ATTRIBUTE30: '||p_attributes(l_index).ATTRIBUTE30);
270 hr_utility.trace(' ATT ID '||p_attributes(l_index).TIME_ATTRIBUTE_ID);
271 hr_utility.trace(' BLD_BLK_INFO_TYPE_ID : '|| p_attributes(l_index).BLD_BLK_INFO_TYPE_ID );
272 hr_utility.trace(' OBJECT_VERSION_NUMBER : '|| p_attributes(l_index).OBJECT_VERSION_NUMBER );
273 hr_utility.trace(' NEW : '|| p_attributes(l_index).NEW );
274 hr_utility.trace(' CHANGED : '|| p_attributes(l_index).CHANGED );
275 hr_utility.trace(' BLD_BLK_INFO_TYPE : '|| p_attributes(l_index).BLD_BLK_INFO_TYPE );
276 hr_utility.trace(' PROCESS : '|| p_attributes(l_index).PROCESS );
277 hr_utility.trace(' BUILDING_BLOCK_OVN : '|| p_attributes(l_index).BUILDING_BLOCK_OVN );
278 hr_utility.trace('------------------------------------------------------');
279
280 l_index := p_attributes.next(l_index);
281 END LOOP;
282
283 hr_utility.trace('PA-PAY> ATTRIBUTES TABLE END ');
284 hr_utility.trace(' *****************');
285
286 hr_utility.trace('PA-PAY> P_BLOCK TABLE START ');
287 hr_utility.trace(' *****************');
288
289 l_index := p_blocks.first;
290 LOOP
291 EXIT WHEN NOT p_blocks.exists(l_index);
292 hr_utility.trace('RESOURCE_ID :'||p_blocks(l_index).RESOURCE_ID);
293 hr_utility.trace('BB id : '||p_blocks(l_index).TIME_BUILDING_BLOCK_ID
294 ||'['||p_blocks(l_index).OBJECT_VERSION_NUMBER||']');
295
296 hr_utility.trace(' PARENT_BUILDING_BLOCK_ID: '||p_blocks(l_index).PARENT_BUILDING_BLOCK_ID);
297 hr_utility.trace(' PARENT_BUILDING_BLOCK_OVN : '|| p_blocks(l_index).PARENT_BUILDING_BLOCK_OVN ) ;
298 hr_utility.trace(' PARENT_IS_NEW :'|| p_blocks(l_index).PARENT_IS_NEW);
299 hr_utility.trace(' DATE_TO: '||p_blocks(l_index).DATE_TO);
300 hr_utility.trace(' SCOPE: '||p_blocks(l_index).SCOPE);
301 hr_utility.trace(' MEASURE : '||p_blocks(l_index).MEASURE);
302 hr_utility.trace(' START_TIME : '||p_blocks(l_index).START_TIME);
303 hr_utility.trace(' STOP_TIME : '||p_blocks(l_index).STOP_TIME);
304 hr_utility.trace(' TRANSLATION_DISPLAY_KEY : '||p_blocks(l_index).TRANSLATION_DISPLAY_KEY);
305 hr_utility.trace(' APPROVAL_STATUS: '||p_blocks(l_index).APPROVAL_STATUS);
306 hr_utility.trace(' APPROVAL_STYLE_ID: '||p_blocks(l_index).APPROVAL_STYLE_ID);
307 hr_utility.trace(' COMMENT_TEXT : '|| p_blocks(l_index).COMMENT_TEXT);
308 hr_utility.trace(' NEW : '|| p_blocks(l_index).NEW);
309 hr_utility.trace(' CHANGED : '|| p_blocks(l_index).CHANGED);
310 hr_utility.trace(' PROCESS : '|| p_blocks(l_index).PROCESS);
311
312
313 l_index := p_blocks.next(l_index);
314 END LOOP;
315
316 hr_utility.trace(' p_blocks TABLE END ');
317 hr_utility.trace(' *****************');
318
319 END IF;
320
321 l_index := p_blocks.FIRST;
322 LOOP
323 IF (p_blocks(l_index).scope = 'TIMECARD' or p_blocks(l_index).scope = 'TIMECARD_TEMPLATE')
324 AND FND_DATE.canonical_to_date(p_blocks(l_index).DATE_TO) = hr_general.end_of_time
325 THEN
326 hxc_preference_evaluation.resource_preferences(p_blocks(l_index).resource_id,
327 FND_DATE.canonical_to_date(p_blocks(l_index).start_time),
328 FND_DATE.canonical_to_date(p_blocks(l_index).stop_time),
329 l_pref_table);
330 l_resource_id := p_blocks(l_index).resource_id;
331 l_timecard_id := p_blocks(l_index).time_building_block_id;
332 l_timecard_ovn := p_blocks(l_index).object_version_number;
333 l_start_time := FND_DATE.canonical_to_date(p_blocks(l_index).start_time);
334 l_stop_time := FND_DATE.canonical_to_date(p_blocks(l_index).stop_time);
335
336 EXIT;
337 END IF;
338 l_index := p_blocks.NEXT(l_index);
339 EXIT WHEN NOT p_blocks.EXISTS(l_index);
340 END LOOP;
341
342 IF l_pref_table.COUNT = 0 and (p_blocks(l_index).scope = 'TIMECARD' or p_blocks(l_index).scope = 'TIMECARD_TEMPLATE')
343 THEN
344 hr_utility.trace('PA-PAY> Proj-Pay Setup no preferences exists raise an error. Return');
345 hxc_timecard_message_helper.adderrortocollection (
346 p_messages => p_messages
347 , p_message_name => 'HXC_NO_HIER_FOR_DATE'
348 , p_message_level => 'ERROR'
349 , p_message_field => NULL
350 , p_message_tokens => NULL
351 , p_application_short_name => 'HXC'
352 , p_time_building_block_id => NULL
353 , p_time_building_block_ovn => NULL
354 , p_time_attribute_id => NULL
355 , p_time_attribute_ovn => NULL );
356 RETURN;
357 END IF;
358
359 l_index := l_pref_table.FIRST;
360 LOOP
361 IF l_pref_table(l_index).preference_code = 'TS_PA_PAY_INTG'
362 THEN
363 l_proj_pay_integration := l_pref_table(l_index).attribute1;
364 EXIT;
365 END IF;
366 l_index := l_pref_table.NEXT(l_index);
367 EXIT WHEN NOT l_pref_table.EXISTS(l_index);
368 END LOOP;
369
370 IF g_debug THEN
371 hr_utility.trace('PA-PAY> Projects Payroll integration is enabled ::'||l_proj_pay_integration);
372 hr_utility.trace('PA-PAY> l_resource_id ::'||l_resource_id);
373 hr_utility.trace('PA-PAY> l_timecard_id ::'||l_timecard_id);
374 hr_utility.trace('PA-PAY> l_timecard_ovn ::'||l_timecard_ovn);
375 hr_utility.trace('PA-PAY> l_start_time ::'||l_start_time);
376 hr_utility.trace('PA-PAY> l_stop_time ::'||l_stop_time);
377 END IF;
378
379 IF l_proj_pay_integration <> 'Y' THEN
380 verify_atribtues(p_attributes
381 , p_messages);
382 END IF;
383
384 IF p_messages.COUNT > 0 THEN
385 RETURN;
386 END IF;
387
388 IF g_debug THEN
389 hr_utility.trace('PA-PAY> Timecard ID ::'||l_timecard_id);
390 END IF;
391
392 IF l_timecard_id > 0 THEN
393 validate_timecard_edit(l_resource_id
394 , l_start_time
395 , l_timecard_id
396 , l_proj_pay_integration
397 , p_blocks
398 , p_messages);
399 ELSE
400 RETURN;
401 END IF;
402
403 EXCEPTION
404 WHEN OTHERS THEN
405 hr_utility.trace(dbms_utility.format_error_backtrace);
406 END verify_proj_pay_setup;
407
408 END HXC_PROJ_PAY_DEP_VALIDATION;
409