[Home] [Help]
PACKAGE BODY: APPS.WIP_TIME_ENTRY_PUB
Source
1 PACKAGE BODY WIP_TIME_ENTRY_PUB AS
2 /* $Header: wipwsilb.pls 120.10.12020000.4 2013/03/12 15:29:13 sisankar ship $ */
3
4 procedure process_interface_records(retcode out nocopy number,
5 errbuf out nocopy varchar2,
6 p_organization_id in number)
7 is
8
9 l_shift_enabled boolean := false;
10 l_clock_enabled boolean := False;
11 l_valid_shift_pref boolean := true;
12 l_valid_clock_pref boolean := true;
13 l_conc_status boolean;
14 l_stmt_num number;
15 l_err_msg varchar2(2000);
16 l_return_status number;
17 l_status number :=0 ;
18 l_temp number;
19 l_org_code varchar2(3);
20 l_count number :=0;
21
22 g_user_id number;
23 g_user_login_id number;
24 g_program_appl_id number;
25 g_request_id number;
26 g_program_id number;
27 g_logLevel number;
28
29 v_wip_time_intf t_wip_time_intf;
30
31 cursor running_intf_records(org_id Number) is
32 select *
33 from wip_time_entry_interface
34 where process_status = wip_constants.running
35 and organization_id = org_id;
36
37 e_null_org_id exception;
38
39 begin
40
41 l_stmt_num := 10;
42
43 if p_organization_id is null then
44 raise e_null_org_id;
45 end if;
46
47 select organization_code
48 into l_org_code
49 from mtl_parameters
50 where organization_id = p_organization_id;
51
52 wip_ws_util.trace_log('Launching Import Time Entry Records for Organization: '||l_org_code);
53
54 g_user_id := fnd_global.user_id;
55 g_user_login_id := fnd_global.login_id;
56 g_program_appl_id := fnd_global.prog_appl_id;
57 g_request_id := fnd_global.conc_request_id;
58 g_program_id := fnd_global.conc_program_id;
59 g_logLevel := FND_LOG.g_current_runtime_level;
60
61 l_stmt_num := 20;
62
63 begin
64 select distinct case when (attribute_value_code/10) < 2 then 1 else 0 end
65 into l_temp
66 from wip_preference_values
67 where preference_id=26 and
68 level_id in (select level_id
69 from wip_preference_levels
70 where organization_id = p_organization_id and
71 resp_key is not null and
72 department_id is null);
73 exception
74 when too_many_rows then
75 l_valid_shift_pref := false;
76 fnd_message.set_name('WIP','WIP_INVALID_SHIFT_PREF');
77 fnd_message.set_token('ORG', to_char(l_org_code));
78 l_err_msg := fnd_message.get;
79
80 update wip_time_entry_interface
81 set process_status = wip_constants.error,
82 error = l_err_msg,
83 request_id = g_request_id,
84 program_id = g_program_id,
85 program_application_id = g_program_appl_id,
86 program_update_date = sysdate,
87 last_updated_by = g_user_id,
88 last_update_date = sysdate,
89 last_update_login = g_user_login_id
90 where
91 time_entry_type = 1
92 and process_status = wip_constants.pending
93 and (organization_id = p_organization_id or
94 organization_code = (select mp.organization_code
95 from mtl_parameters mp
96 where mp.organization_id = p_organization_id));
97
98 l_status := sql%rowcount;
99 if l_status>0 then
100 wip_ws_util.trace_log(l_err_msg);
101 end if;
102
103 when no_data_found then
104 l_valid_shift_pref := true;
105 end;
106
107 begin
108 select distinct decode(mod(attribute_value_code,10),1,1,0)
109 into l_temp
110 from wip_preference_values
111 where preference_id=26 and
112 level_id in (select level_id
113 from wip_preference_levels
114 where organization_id = p_organization_id and
115 resp_key is not null and
116 department_id is null);
117 exception
118 when too_many_rows then
119 l_valid_clock_pref := false;
120 fnd_message.set_name('WIP','WIP_INVALID_CLOCK_PREF');
121 fnd_message.set_token('ORG', to_char(l_org_code));
122 l_err_msg := fnd_message.get;
123
124 update wip_time_entry_interface
125 set process_status = wip_constants.error,
126 error = l_err_msg,
127 request_id = g_request_id,
128 program_id = g_program_id,
129 program_application_id = g_program_appl_id,
130 program_update_date = sysdate,
131 last_updated_by = g_user_id,
132 last_update_date = sysdate,
133 last_update_login = g_user_login_id
134 where
135 time_entry_type = 3
136 and process_status = wip_constants.pending
137 and (organization_id = p_organization_id or
138 organization_code = (select mp.organization_code
139 from mtl_parameters mp
140 where mp.organization_id = p_organization_id));
141
142 l_count := sql%rowcount;
143 l_status := l_status+l_count;
144 if l_count >0 then
145 wip_ws_util.trace_log(l_err_msg);
146 end if;
147
148 when no_data_found then
149 l_valid_clock_pref := true;
150 end;
151
152 get_time_preferences(p_organization_id => p_organization_id,
153 x_shift_enabled => l_shift_enabled,
154 x_clock_enabled => l_clock_enabled);
155
156
157 l_stmt_num := 30;
158 if l_shift_enabled and l_valid_shift_pref then
159
160 fnd_message.set_name('WIP','WIP_AAH_IMPORT_NOT_ALLOWED');
161 l_err_msg := fnd_message.get;
162
163 update wip_time_entry_interface
164 set process_status = wip_constants.error,
165 error = l_err_msg,
166 request_id = g_request_id,
167 program_id = g_program_id,
168 program_application_id = g_program_appl_id,
169 program_update_date = sysdate,
170 last_updated_by = g_user_id,
171 last_update_date = sysdate,
172 last_update_login = g_user_login_id
173 where
174 time_entry_type = 1
175 and process_status = wip_constants.pending
176 and (organization_id = p_organization_id or
177 organization_code = (select mp.organization_code
178 from mtl_parameters mp
179 where mp.organization_id = p_organization_id));
180
181 l_count := sql%rowcount;
182 l_status := l_status+l_count;
183 if l_count >0 then
184 wip_ws_util.trace_log(l_err_msg);
185 end if;
186
187 end if;
188 l_stmt_num := 40;
189 if l_clock_enabled and l_valid_clock_pref then
190
191 fnd_message.set_name('WIP','WIP_DLH_IMPORT_NOT_ALLOWED');
192 l_err_msg := fnd_message.get;
193
194 update wip_time_entry_interface
195 set process_status = wip_constants.error,
196 error = l_err_msg,
197 request_id = g_request_id,
198 program_id = g_program_id,
199 program_application_id = g_program_appl_id,
200 program_update_date = sysdate,
201 last_updated_by = g_user_id,
202 last_update_date = sysdate,
203 last_update_login = g_user_login_id
204 where
205 time_entry_type = 3
206 and process_status = wip_constants.pending
207 and (organization_id = p_organization_id or
208 organization_code = (select mp.organization_code
209 from mtl_parameters mp
210 where mp.organization_id = p_organization_id));
211
212 l_count := sql%rowcount;
213 l_status := l_status+l_count;
214 if l_count >0 then
215 wip_ws_util.trace_log(l_err_msg);
216 end if;
217
218 end if;
219 l_stmt_num := 50;
220
221 update wip_time_entry_interface
222 set process_status = wip_constants.running,
223 organization_id = nvl(organization_id,p_organization_id),
224 request_id = fnd_global.conc_request_id,
225 program_id = fnd_global.conc_program_id,
226 program_application_id = fnd_global.prog_appl_id,
227 program_update_date = sysdate,
228 last_updated_by = fnd_global.user_id,
229 last_update_date = sysdate,
230 last_update_login = fnd_global.login_id
231 where
232 process_status = wip_constants.pending
233 and (organization_id = p_organization_id or
234 organization_code = (select mp.organization_code
235 from mtl_parameters mp
236 where mp.organization_id = p_organization_id));
237
238 l_stmt_num := 55;
239 /* Added for Bug 6908314. This SQL will default employee_id from badge_id. */
240 update wip_time_entry_interface wtei
241 set wtei.employee_id = ( select bre.person_id
242 from per_all_people_f papf,
243 bom_resource_employees bre
244 where papf.person_id = bre.person_id
245 and sysdate between papf.effective_start_date and nvl(papf.effective_end_date,sysdate+1)
246 and bre.organization_id = wtei.organization_id
247 and nvl(papf.employee_number,papf.npw_number) = wtei.badge_id
248 and rownum=1
249 ),
250 wtei.last_update_date = sysdate
251 where
252 wtei.organization_id = p_organization_id and
253 wtei.process_status = wip_constants.running and
254 wtei.employee_id is null and
255 wtei.badge_id is not null ;
256
257
258 l_stmt_num := 57;
259 /* Added for to default employee_id for contingent Workers
260 No need as we don't currently support Emp and CW having same badge in same org from MES UI.
261 update wip_time_entry_interface wtei
262 set wtei.employee_id = ( select bre.person_id
263 from per_all_people_f papf,
264 bom_resource_employees bre
265 where papf.person_id = bre.person_id
266 and sysdate between papf.effective_start_date and nvl(papf.effective_end_date,sysdate+1)
267 and bre.organization_id = wtei.organization_id
268 and papf.npw_number = wtei.badge_id
269 and rownum=1
270 ),
271 wtei.last_update_date = sysdate
272 where
273 wtei.organization_id = p_organization_id and
274 wtei.process_status = wip_constants.running and
275 wtei.employee_id is null and
276 wtei.badge_id is not null and
277 upper(wtei.contingent_flag) = 'Y';
278 */
279 l_stmt_num := 60;
280
281 open running_intf_records(p_organization_id);
282 fetch running_intf_records bulk collect into v_wip_time_intf;
283 close running_intf_records;
284
285 l_stmt_num := 70;
286
287 if (g_logLevel <= wip_constants.trace_logging) then
288 wip_ws_util.trace_log('Launching Process to import the records');
289 end if;
290
291 process( p_wip_time_intf_tbl => v_wip_time_intf,
292 x_ret_status => l_return_status);
293
294 l_stmt_num := 80;
295
296 if (g_logLevel <= wip_constants.trace_logging) then
297 wip_ws_util.trace_log('Returned from Process after importing the records');
298 end if;
299
300 commit;
301
302 if l_return_status = 1 and l_status =0 then
303 retcode :=1;
304 errbuf := 'The Import program successfully imported all records';
305 wip_ws_util.trace_log(errbuf);
306 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',errbuf);
307 else
308 retcode := 1;
309 errbuf := 'The Import program marked at least one row as errored';
310 wip_ws_util.trace_log(errbuf);
311 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',errbuf);
312 end if;
313
314 exception
315 when e_null_org_id then
316 retcode := -1;
317 errbuf := 'Organization parameter cannot be null';
318 wip_ws_util.trace_log(errbuf);
319 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',errbuf);
320 when others then
321 retcode := -1;
322 errbuf := 'Errors encountered in interface txn, please check the log file.';
323 wip_ws_util.trace_log(errbuf);
324 wip_ws_util.trace_log(sqlerrm(sqlcode));
325 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',errbuf);
326 end process_interface_records;
327
328 procedure get_time_preferences(p_organization_id IN NUMBER default null,
329 x_shift_enabled OUT NOCOPY boolean,
330 x_clock_enabled OUT NOCOPY boolean)
331 is
332
333 l_level_id Number;
334 l_pref_value Number;
335 l_shift_value Number;
336 l_clock_value Number;
337 begin
338
339 begin
340 -- How to get Resp key here. Without Resp key this will return multiple rows.
341 -- One option is to use resp_id from which the concurrent program is executed.
342 -- another option is to use rownum. Since it doesn't make any sense to have
343 -- different settings w.r.t time entry parameters for different responsibilities,
344 -- we will use rownum.
345
346 select level_id
347 into l_level_id
348 from wip_preference_levels
349 where organization_id = p_organization_id
350 and department_id is null
351 and level_id in (select level_id
352 from wip_preference_values
353 where preference_id = 26)
354 and rownum=1;
355
356 exception
357 when no_data_found then
358 l_level_id :=1; -- set levet to Site level
359 when too_many_rows then -- It will never reach here.
360 l_level_id :=1; -- Currently set to site level.
361 when others then
362 l_level_id :=0;
363 end;
364
365 begin
366 select to_number(attribute_value_code)
367 into l_pref_value
368 from wip_preference_values
369 where level_id = l_level_id
370 and preference_id = 26;
371 exception
372 when no_data_found then
373 -- this might happen if lower level parameters have inherited them from upper levels.
374 -- if records are inherited from upper level, then we will look directly at site level.
375 -- if records are inherited, then we will consider site as the upper level and not responsibility.
376
377 select to_number(attribute_value_code)
378 into l_pref_value
379 from wip_preference_values
380 where level_id = 1
381 and preference_id = 26;
382
383 when others then
384 l_pref_value :=0;
385 end;
386
387 l_shift_value := l_pref_value/10;
388 l_clock_value := mod(l_pref_value,10);
389
390 If l_shift_value < 2 then
391 x_shift_enabled := false;
392 else
393 x_shift_enabled := true;
394 end if;
395
396 If l_clock_value = 1 then
397 x_clock_enabled := false;
398 else
399 x_clock_enabled := true;
400 end if;
401 end get_time_preferences;
402
403 function default_res_id(p_org_id in number,
404 p_wip_id in number,
405 p_op_seq in number,
406 p_res_seq in number)
407
408 return number is
409 l_res_id number;
410 begin
411
412 select resource_id
413 into l_res_id
414 from wip_operation_resources
415 where organization_id = p_org_id
416 and wip_entity_id = p_wip_id
417 and operation_seq_num = p_op_seq
418 and resource_seq_num = p_res_seq;
419
420 return l_res_id;
421 exception
422 when others then
423 return null;
424 end default_res_id;
425
426 function default_res_seq(p_org_id in number,
427 p_wip_id in number,
428 p_op_seq in number,
429 p_res_id in number)
430
431 return number is
432 l_res_seq number;
433 begin
434
435 select resource_seq_num
436 into l_res_seq
437 from wip_operation_resources
438 where organization_id = p_org_id
439 and wip_entity_id = p_wip_id
440 and operation_seq_num = p_op_seq
441 and resource_id = p_res_id
442 and rownum = 1;
443
444 return l_res_seq;
445 exception
446 when others then
447 return null;
448 end default_res_seq;
449
450
451 procedure process(p_wip_time_intf_tbl in t_wip_time_intf,
452 x_ret_status out nocopy number)
453 is
454
455 l_wip_time_intf_tbl t_wip_time_intf;
456 l_counter number;
457 l_error_count number :=0;
458 l_stmt_num number;
459 ins_counter number;
460 type t_wip_res_actual_times is table of wip_resource_actual_times%rowtype index by binary_integer;
461 v_wip_res_actual_times t_wip_res_actual_times;
462
463 type t_interface_id is table of wip_time_entry_interface.interface_id%type index by binary_integer;
464 type t_process_status is table of wip_time_entry_interface.process_status%type index by binary_integer;
465 type t_error is table of wip_time_entry_interface.error%type index by binary_integer;
466
467 v_interface_id t_interface_id;
468 v_process_status t_process_status;
469 v_error t_error;
470 pop_counter number :=1;
471 l_bom_hr_uom varchar2(3);
472 l_duration number := 0;
473 g_logLevel number;
474 l_instance_id number;
475 begin
476
477 g_logLevel := FND_LOG.g_current_runtime_level;
478 l_stmt_num :=10;
479 l_bom_hr_uom := fnd_profile.value('BOM:HOUR_UOM_CODE');
480 l_wip_time_intf_tbl := p_wip_time_intf_tbl;
481 l_counter := l_wip_time_intf_tbl.first;
482 ins_counter := 1;
483 l_stmt_num :=20;
484
485 if (g_logLevel <= wip_constants.trace_logging) then
486 wip_ws_util.trace_log('Beginning to process interface records');
487 end if;
488
489 while l_counter is not null loop
490 l_instance_id := null;
491 if l_wip_time_intf_tbl(l_counter).actual_start_date is null or
492 (l_wip_time_intf_tbl(l_counter).actual_end_date is null and
493 l_wip_time_intf_tbl(l_counter).duration is null) then
494 l_stmt_num :=30;
495 fnd_message.set_name('WIP','WIP_TIME_IMPORT_DATE_NULL');
496 l_wip_time_intf_tbl(l_counter).error := fnd_message.get;
497 l_wip_time_intf_tbl(l_counter).process_status := wip_constants.error;
498 l_error_count := l_error_count +1;
499 wip_ws_util.trace_log('Error Importing record '||'( stmt_num: '||l_stmt_num||') '||
500 l_wip_time_intf_tbl(l_counter).error||' for Intf Id: '||l_wip_time_intf_tbl(l_counter).interface_id);
501 goto skip_validations;
502 end if;
503 l_stmt_num :=35;
504 if l_wip_time_intf_tbl(l_counter).time_entry_type = 1 or
505 l_wip_time_intf_tbl(l_counter).time_entry_type = 2 then /* Actual Attendance Hours,
506 Scheduled Available Hours */
507 if l_wip_time_intf_tbl(l_counter).time_entry_type = 2 and nvl(l_wip_time_intf_tbl(l_counter).action_flag,-1) not in (1,2) then
508 fnd_message.set_name('WIP','WIP_SAH_ONLY_ADJUSTMENT');
509 l_wip_time_intf_tbl(l_counter).error := fnd_message.get;
510 l_wip_time_intf_tbl(l_counter).process_status := wip_constants.error;
511 l_error_count := l_error_count +1;
512 wip_ws_util.trace_log('Error Importing record '||'( stmt_num: '||l_stmt_num||') '||
513 l_wip_time_intf_tbl(l_counter).error||' for Intf Id: '||l_wip_time_intf_tbl(l_counter).interface_id);
514 goto skip_validations;
515 end if;
516
517 l_stmt_num :=40;
518 if l_wip_time_intf_tbl(l_counter).wip_entity_id is not null or
519 l_wip_time_intf_tbl(l_counter).job_name is not null or
520 l_wip_time_intf_tbl(l_counter).operation_seq_num is not null or
521 l_wip_time_intf_tbl(l_counter).resource_seq_num is not null then
522 l_wip_time_intf_tbl(l_counter).wip_entity_id := null;
523 l_wip_time_intf_tbl(l_counter).operation_seq_num := null;
524 l_wip_time_intf_tbl(l_counter).resource_seq_num := null;
525 fnd_message.set_name('WIP','WIP_IMP_JOB_IGNORED');
526 l_wip_time_intf_tbl(l_counter).error := fnd_message.get;
527 l_wip_time_intf_tbl(l_counter).process_status := wip_constants.warning;
528 l_error_count := l_error_count +1;
529 if (g_logLevel <= wip_constants.trace_logging) then
530 wip_ws_util.trace_log('Error Importing record '||'( stmt_num: '||l_stmt_num||') '||
531 l_wip_time_intf_tbl(l_counter).error||' for Intf Id: '||l_wip_time_intf_tbl(l_counter).interface_id);
532 end if;
533 end if;
534 l_stmt_num :=50;
535 if l_wip_time_intf_tbl(l_counter).employee_id is null then
536 fnd_message.set_name('WIP','WIP_NO_EMP_DETAILS');
537 l_wip_time_intf_tbl(l_counter).error := fnd_message.get;
538 l_wip_time_intf_tbl(l_counter).process_status := wip_constants.error;
539 l_error_count := l_error_count +1;
540 wip_ws_util.trace_log('Error Importing record '||'( stmt_num: '||l_stmt_num||') '||
541 l_wip_time_intf_tbl(l_counter).error||' for Intf Id: '||l_wip_time_intf_tbl(l_counter).interface_id);
542 goto skip_validations;
543 end if;
544 l_stmt_num :=60;
545 if is_emp_invalid(p_org_id => l_wip_time_intf_tbl(l_counter).organization_id,
546 p_dep_id => null,
547 p_res_id => null,
548 p_emp_id => l_wip_time_intf_tbl(l_counter).employee_id) then
549 fnd_message.set_name('WIP','WIP_INVALID_EMP_DETAILS');
550 l_wip_time_intf_tbl(l_counter).error := fnd_message.get;
551 l_wip_time_intf_tbl(l_counter).process_status := wip_constants.error;
552 l_error_count := l_error_count +1;
553 wip_ws_util.trace_log('Error Importing record '||'( stmt_num: '||l_stmt_num||') '||
554 l_wip_time_intf_tbl(l_counter).error||' for Intf Id: '||l_wip_time_intf_tbl(l_counter).interface_id);
555 goto skip_validations;
556 end if;
557 l_stmt_num :=70;
558 elsif l_wip_time_intf_tbl(l_counter).time_entry_type = 3 then /* Direct Labor Hours */
559 l_stmt_num :=80;
560 if l_wip_time_intf_tbl(l_counter).wip_entity_id is null and
561 l_wip_time_intf_tbl(l_counter).job_name is null then
562 fnd_message.set_name('WIP','WIP_NO_JOB_DETAILS');
563 l_wip_time_intf_tbl(l_counter).error := fnd_message.get;
564 l_wip_time_intf_tbl(l_counter).process_status := wip_constants.error;
565 l_error_count := l_error_count +1;
566 wip_ws_util.trace_log('Error Importing record '||'( stmt_num: '||l_stmt_num||') '||
567 l_wip_time_intf_tbl(l_counter).error||' for Intf Id: '||l_wip_time_intf_tbl(l_counter).interface_id);
568 goto skip_validations;
569 elsif l_wip_time_intf_tbl(l_counter).wip_entity_id is null and
570 l_wip_time_intf_tbl(l_counter).job_name is not null then
571 l_wip_time_intf_tbl(l_counter).wip_entity_id := default_job_id(p_org_id => l_wip_time_intf_tbl(l_counter).organization_id,
572 p_job_name => l_wip_time_intf_tbl(l_counter).job_name);
573 end if;
574 l_stmt_num :=90;
575 if is_job_invalid(p_org_id => l_wip_time_intf_tbl(l_counter).organization_id,
576 p_we_id => l_wip_time_intf_tbl(l_counter).wip_entity_id) then
577 fnd_message.set_name('WIP','WIP_INVALID_JOB_DETAILS');
578 l_wip_time_intf_tbl(l_counter).error := fnd_message.get;
579 l_wip_time_intf_tbl(l_counter).process_status := wip_constants.error;
580 l_error_count := l_error_count +1;
581 wip_ws_util.trace_log('Error Importing record '||'( stmt_num: '||l_stmt_num||') '||
582 l_wip_time_intf_tbl(l_counter).error||' for Intf Id: '||l_wip_time_intf_tbl(l_counter).interface_id);
583 goto skip_validations;
584 end if;
585 l_stmt_num :=100;
586 if l_wip_time_intf_tbl(l_counter).operation_seq_num is null then
587 fnd_message.set_name('WIP','WIP_JOB_OP_NULL');
588 l_wip_time_intf_tbl(l_counter).error := fnd_message.get;
589 l_wip_time_intf_tbl(l_counter).process_status := wip_constants.error;
590 l_error_count := l_error_count +1;
591 wip_ws_util.trace_log('Error Importing record '||'( stmt_num: '||l_stmt_num||') '||
592 l_wip_time_intf_tbl(l_counter).error||' for Intf Id: '||l_wip_time_intf_tbl(l_counter).interface_id);
593 goto skip_validations;
594 else
595 l_stmt_num :=110;
596 l_wip_time_intf_tbl(l_counter).department_id := get_op_dept_id(p_org_id => l_wip_time_intf_tbl(l_counter).organization_id,
597 p_we_id => l_wip_time_intf_tbl(l_counter).wip_entity_id,
598 p_op_seq => l_wip_time_intf_tbl(l_counter).operation_seq_num);
599 if l_wip_time_intf_tbl(l_counter).department_id < 0 then
600 fnd_message.set_name('WIP','WIP_INVALID_JOB_OP');
601 l_wip_time_intf_tbl(l_counter).error := fnd_message.get;
602 l_wip_time_intf_tbl(l_counter).process_status := wip_constants.error;
603 l_error_count := l_error_count +1;
604 wip_ws_util.trace_log('Error Importing record '||'( stmt_num: '||l_stmt_num||') '||
605 l_wip_time_intf_tbl(l_counter).error||' for Intf Id: '||l_wip_time_intf_tbl(l_counter).interface_id);
606 goto skip_validations;
607 end if;
608 end if;
609 l_stmt_num :=120;
610 if l_wip_time_intf_tbl(l_counter).resource_id is null and
611 l_wip_time_intf_tbl(l_counter).resource_code is null and
612 l_wip_time_intf_tbl(l_counter).resource_seq_num is null then
613 fnd_message.set_name('WIP','WIP_NO_RES_DETAILS');
614 l_wip_time_intf_tbl(l_counter).error := fnd_message.get;
615 l_wip_time_intf_tbl(l_counter).process_status := wip_constants.error;
616 l_error_count := l_error_count +1;
617 wip_ws_util.trace_log('Error Importing record '||'( stmt_num: '||l_stmt_num||') '||
618 l_wip_time_intf_tbl(l_counter).error||' for Intf Id: '||l_wip_time_intf_tbl(l_counter).interface_id);
619 goto skip_validations;
620 elsif l_wip_time_intf_tbl(l_counter).resource_id is null and
621 l_wip_time_intf_tbl(l_counter).resource_code is not null and
622 l_wip_time_intf_tbl(l_counter).resource_seq_num is null then
623 l_wip_time_intf_tbl(l_counter).resource_id := default_res_id(p_org_id => l_wip_time_intf_tbl(l_counter).organization_id,
624 p_res_name => l_wip_time_intf_tbl(l_counter).resource_code);
625 elsif l_wip_time_intf_tbl(l_counter).resource_id is null and
626 l_wip_time_intf_tbl(l_counter).resource_seq_num is not null then
627 l_wip_time_intf_tbl(l_counter).resource_id := default_res_id(p_org_id => l_wip_time_intf_tbl(l_counter).organization_id,
628 p_wip_id => l_wip_time_intf_tbl(l_counter).wip_entity_id,
629 p_op_seq => l_wip_time_intf_tbl(l_counter).operation_seq_num,
630 p_res_seq => l_wip_time_intf_tbl(l_counter).resource_seq_num);
631 elsif l_wip_time_intf_tbl(l_counter).resource_id is not null and
632 l_wip_time_intf_tbl(l_counter).resource_seq_num is null then
633 l_wip_time_intf_tbl(l_counter).resource_seq_num := default_res_seq(p_org_id => l_wip_time_intf_tbl(l_counter).organization_id,
634 p_wip_id => l_wip_time_intf_tbl(l_counter).wip_entity_id,
635 p_op_seq => l_wip_time_intf_tbl(l_counter).operation_seq_num,
636 p_res_id => l_wip_time_intf_tbl(l_counter).resource_id);
637
638 end if;
639 l_stmt_num :=130;
640 if is_res_invalid(p_org_id => l_wip_time_intf_tbl(l_counter).organization_id,
641 p_dep_id => l_wip_time_intf_tbl(l_counter).department_id,
642 p_res_id => l_wip_time_intf_tbl(l_counter).resource_id) then
643 fnd_message.set_name('WIP','WIP_INVALID_RES_DETAILS');
644 l_wip_time_intf_tbl(l_counter).error := fnd_message.get;
645 l_wip_time_intf_tbl(l_counter).process_status := wip_constants.error;
646 l_error_count := l_error_count +1;
647 wip_ws_util.trace_log('Error Importing record '||'( stmt_num: '||l_stmt_num||') '||
648 l_wip_time_intf_tbl(l_counter).error||' for Intf Id: '||l_wip_time_intf_tbl(l_counter).interface_id);
649 goto skip_validations;
650 end if;
651 l_stmt_num :=140;
652 if l_wip_time_intf_tbl(l_counter).employee_id is null then
653 fnd_message.set_name('WIP','WIP_NO_EMP_DETAILS');
654 l_wip_time_intf_tbl(l_counter).error := fnd_message.get;
655 l_wip_time_intf_tbl(l_counter).process_status := wip_constants.error;
656 l_error_count := l_error_count +1;
657 wip_ws_util.trace_log('Error Importing record '||'( stmt_num: '||l_stmt_num||') '||
658 l_wip_time_intf_tbl(l_counter).error||' for Intf Id: '||l_wip_time_intf_tbl(l_counter).interface_id);
659 goto skip_validations;
660 end if;
661 l_stmt_num :=150;
662 if is_emp_invalid(p_org_id => l_wip_time_intf_tbl(l_counter).organization_id,
663 p_dep_id => l_wip_time_intf_tbl(l_counter).department_id,
664 p_res_id => l_wip_time_intf_tbl(l_counter).resource_id,
665 p_emp_id => l_wip_time_intf_tbl(l_counter).employee_id) then
666 fnd_message.set_name('WIP','WIP_INVALID_EMP_DETAILS');
667 l_wip_time_intf_tbl(l_counter).error := fnd_message.get;
668 l_wip_time_intf_tbl(l_counter).process_status := wip_constants.error;
669 l_error_count := l_error_count +1;
670 wip_ws_util.trace_log('Error Importing record '||'( stmt_num: '||l_stmt_num||') '||
671 l_wip_time_intf_tbl(l_counter).error||' for Intf Id: '||l_wip_time_intf_tbl(l_counter).interface_id);
672 goto skip_validations;
673 end if;
674 l_stmt_num :=160;
675 Begin
676 select min(bre.instance_id)
677 into l_instance_id
678 from bom_resource_employees bre
679 where bre.organization_id = l_wip_time_intf_tbl(l_counter).organization_id
680 and bre.resource_id = l_wip_time_intf_tbl(l_counter).resource_id
681 and bre.person_id = l_wip_time_intf_tbl(l_counter).employee_id;
682 Exception
683 when others then
684 l_instance_id := null;
685 End;
686 l_stmt_num :=170;
687 else
688 fnd_message.set_name('WIP','WIP_INVALID_TIME_TYPE');
689 l_wip_time_intf_tbl(l_counter).error := fnd_message.get;
690 l_wip_time_intf_tbl(l_counter).process_status := wip_constants.error;
691 l_error_count := l_error_count +1;
692 wip_ws_util.trace_log('Error Importing record '||'( stmt_num: '||l_stmt_num||') '||
693 l_wip_time_intf_tbl(l_counter).error||' for Intf Id: '||l_wip_time_intf_tbl(l_counter).interface_id);
694 goto skip_validations;
695 end if;
696 l_stmt_num :=180;
697 if ((l_wip_time_intf_tbl(l_counter).actual_end_date is null) or
698 (l_wip_time_intf_tbl(l_counter).actual_end_date is not null and
699 l_wip_time_intf_tbl(l_counter).duration is not null)) then
700
701 if wip_ws_labor_metric_pub.is_time_uom(l_wip_time_intf_tbl(l_counter).uom_code) <> 1 or
702 l_wip_time_intf_tbl(l_counter).uom_code is null then
703 fnd_message.set_name('WIP','WIP_INVALID_TIME_UOM');
704 l_wip_time_intf_tbl(l_counter).error := fnd_message.get;
705 l_wip_time_intf_tbl(l_counter).process_status := wip_constants.error;
706 l_error_count := l_error_count +1;
707 wip_ws_util.trace_log('Error Importing record '||'( stmt_num: '||l_stmt_num||') '||
708 l_wip_time_intf_tbl(l_counter).error||' for Intf Id: '||l_wip_time_intf_tbl(l_counter).interface_id);
709 goto skip_validations;
710 end if;
711
712 if l_wip_time_intf_tbl(l_counter).uom_code <> l_bom_hr_uom then
713 l_duration := inv_convert.inv_um_convert(item_id => -1,
714 precision => 38,
715 from_quantity => l_wip_time_intf_tbl(l_counter).duration,
716 from_unit => l_wip_time_intf_tbl(l_counter).uom_code,
717 to_unit => l_bom_hr_uom,
718 from_name => null,
719 to_name => null);
720 else
721 l_duration := l_wip_time_intf_tbl(l_counter).duration;
722 end if;
723 l_wip_time_intf_tbl(l_counter).actual_end_date := l_wip_time_intf_tbl(l_counter).actual_start_date +
724 (l_duration/24);
725 l_wip_time_intf_tbl(l_counter).duration := l_duration;
726 l_wip_time_intf_tbl(l_counter).uom_code := l_bom_hr_uom;
727 end if;
728 l_stmt_num :=190;
729 if l_wip_time_intf_tbl(l_counter).duration is null then
730 l_wip_time_intf_tbl(l_counter).duration := (l_wip_time_intf_tbl(l_counter).actual_end_date -
731 l_wip_time_intf_tbl(l_counter).actual_start_date)*24;
732 l_wip_time_intf_tbl(l_counter).uom_code := l_bom_hr_uom;
733 end if;
734
735 if (g_logLevel <= wip_constants.trace_logging) then
736 wip_ws_util.trace_log('Completed validations for record in Intf Id: '||l_wip_time_intf_tbl(l_counter).interface_id);
737 end if;
738
739 l_stmt_num :=200;
740 -- Defaulted instance_id for Imported Clock-in records since Labor metrics program relies on instance_id for metrics calculation.
741 -- Bug 12614160.
742 <<skip_validations>>
743 if l_wip_time_intf_tbl(l_counter).process_status = wip_constants.running or
744 l_wip_time_intf_tbl(l_counter).process_status = wip_constants.warning then
745 select wip_resource_actual_times_s.nextval
746 into v_wip_res_actual_times(ins_counter).time_entry_id from dual;
747 v_wip_res_actual_times(ins_counter).organization_id := l_wip_time_intf_tbl(l_counter).organization_id;
748 v_wip_res_actual_times(ins_counter).wip_entity_id := l_wip_time_intf_tbl(l_counter).wip_entity_id;
749 v_wip_res_actual_times(ins_counter).operation_seq_num := l_wip_time_intf_tbl(l_counter).operation_seq_num;
750 v_wip_res_actual_times(ins_counter).resource_id := l_wip_time_intf_tbl(l_counter).resource_id;
751 v_wip_res_actual_times(ins_counter).resource_seq_num := l_wip_time_intf_tbl(l_counter).resource_seq_num;
752 v_wip_res_actual_times(ins_counter).instance_id := l_instance_id;
753 v_wip_res_actual_times(ins_counter).serial_number := null;
754 select decode(l_wip_time_intf_tbl(l_counter).time_entry_type,1,6,2,7,3,5)
755 into v_wip_res_actual_times(ins_counter).time_entry_mode from dual;
756 v_wip_res_actual_times(ins_counter).cost_flag := 'N';
757 v_wip_res_actual_times(ins_counter).add_to_rtg := 'N';
758 v_wip_res_actual_times(ins_counter).status_type := 2;
759 v_wip_res_actual_times(ins_counter).start_date := l_wip_time_intf_tbl(l_counter).actual_start_date;
760 v_wip_res_actual_times(ins_counter).end_date := l_wip_time_intf_tbl(l_counter).actual_end_date;
761 v_wip_res_actual_times(ins_counter).projected_completion_date := null;
762 v_wip_res_actual_times(ins_counter).duration := l_wip_time_intf_tbl(l_counter).duration;
763 v_wip_res_actual_times(ins_counter).uom_code := l_wip_time_intf_tbl(l_counter).uom_code;
764 v_wip_res_actual_times(ins_counter).employee_id := l_wip_time_intf_tbl(l_counter).employee_id;
765 v_wip_res_actual_times(ins_counter).process_status := 1;
766 v_wip_res_actual_times(ins_counter).created_by := fnd_global.user_id;
767 v_wip_res_actual_times(ins_counter).creation_date := sysdate;
768 v_wip_res_actual_times(ins_counter).last_updated_by := fnd_global.user_id;
769 v_wip_res_actual_times(ins_counter).last_update_date := sysdate;
770 v_wip_res_actual_times(ins_counter).last_update_login := fnd_global.login_id;
771 v_wip_res_actual_times(ins_counter).object_version_number := 1;
772 v_wip_res_actual_times(ins_counter).action_flag := nvl(l_wip_time_intf_tbl(l_counter).action_flag,1);
773 v_wip_res_actual_times(ins_counter).request_id := fnd_global.conc_request_id;
774 v_wip_res_actual_times(ins_counter).program_id := fnd_global.conc_program_id;
775 v_wip_res_actual_times(ins_counter).program_application_id := fnd_global.prog_appl_id;
776 v_wip_res_actual_times(ins_counter).program_update_date := sysdate;
777 ins_counter := ins_counter+1;
778 end if;
779 v_process_status(pop_counter) := l_wip_time_intf_tbl(l_counter).process_status;
780 v_error(pop_counter) := l_wip_time_intf_tbl(l_counter).error;
781 v_interface_id(pop_counter) :=l_wip_time_intf_tbl(l_counter).interface_id;
782 pop_counter := pop_counter+1;
783 l_counter := l_wip_time_intf_tbl.next(l_counter);
784 end loop;
785
786 if (g_logLevel <= wip_constants.trace_logging) then
787 wip_ws_util.trace_log('Before Inserting records in Actual Times Table');
788 end if;
789
790 -- insert into wip_resource_actual_times table
791 forall ins_index in v_wip_res_actual_times.first..v_wip_res_actual_times.last
792 INSERT into WIP_RESOURCE_ACTUAL_TIMES values v_wip_res_actual_times(ins_index);
793
794 if (g_logLevel <= wip_constants.trace_logging) then
795 wip_ws_util.trace_log('After Inserting records in Actual Times Table');
796 end if;
797
798 -- update back wip_time_entry_interface table
799 forall upd_index in v_interface_id.first..v_interface_id.last
800 update wip_time_entry_interface set
801 process_status = decode(v_process_status(upd_index),
802 wip_constants.running,wip_constants.completed,v_process_status(upd_index) ),
803 error = v_error(upd_index),
804 last_update_date = sysdate
805 where interface_id = v_interface_id(upd_index);
806
807 if (g_logLevel <= wip_constants.trace_logging) then
808 wip_ws_util.trace_log('After Updating status for records in Interface Table');
809 end if;
810
811 if l_error_count > 0 then
812 x_ret_status := -1;
813 else
814 x_ret_status := 1;
815 end if;
816 end process;
817
818 procedure write_to_log(p_interface_id in number,
819 p_error_msg in varchar2,
820 p_stmt_num in number)
821 is
822 begin
823 fnd_file.put_line(fnd_file.log,'Error while importing time entry record '||'( stmt_num: '||p_stmt_num||') '||p_error_msg||' for interface Id: '||p_interface_id);
824 fnd_file.new_line(fnd_file.log, 2);
825 end write_to_log;
826
827 function is_emp_invalid(p_org_id in number,
828 p_dep_id in number,
829 p_res_id in number,
830 p_emp_id in number)
831 return boolean is
832 l_count number;
833 begin
834 if p_dep_id is not null then
835 select count(1)
836 into l_count
837 from bom_resource_employees bre,
838 bom_department_resources bdr
839 where bdr.department_id = p_dep_id
840 and bdr.resource_id = p_res_id
841 and bdr.resource_id = bre.resource_id
842 and bre.organization_id = p_org_id
843 and bre.person_id = p_emp_id;
844 else
845 select count(1)
846 into l_count
847 from bom_resource_employees bre
848 where bre.organization_id = p_org_id
849 and bre.person_id = p_emp_id
850 and bre.resource_id = nvl(p_res_id,bre.resource_id);
851 end if;
852 if l_count >= 1 then
853 return false;
854 else
855 return true;
856 end if;
857 end is_emp_invalid;
858
859 function default_job_id(p_org_id in number,
860 p_job_name in varchar2)
861 return number is
862 l_we_id number;
863 begin
864 select wip_entity_id
865 into l_we_id
866 from wip_entities
867 where wip_entity_name = p_job_name
868 and organization_id = p_org_id;
869 return l_we_id;
870 exception
871 when others then
872 return null;
873 end default_job_id;
874
875 function is_job_invalid(p_org_id in number,
876 p_we_id in number)
877 return boolean is
878 l_count number;
879 begin
880 select count(1)
881 into l_count
882 from wip_discrete_jobs wdj
883 where wdj.organization_id = p_org_id
884 and wdj.wip_entity_id = p_we_id
885 and wdj.status_type in (wip_constants.released,wip_constants.comp_chrg,wip_constants.hold);
886
887 if l_count = 0 then
888 return true;
889 else
890 return false;
891 end if;
892 end is_job_invalid;
893
894 function get_op_dept_id(p_org_id in number,
895 p_we_id in number,
896 p_op_seq in number)
897 return number is
898 l_dep_id number;
899 begin
900 select department_id
901 into l_dep_id
902 from wip_operations
903 where organization_id=p_org_id
904 and wip_entity_id=p_we_id
905 and operation_seq_num=p_op_seq;
906 return l_dep_id;
907 exception
908 when others then
909 return -1;
910 end get_op_dept_id;
911
912 function default_res_id(p_org_id in number,
913 p_res_name in varchar)
914 return number is
915 l_res_id number;
916 begin
917 select resource_id
918 into l_res_id
919 from bom_resources
920 where organization_id = p_org_id
921 and resource_code = p_res_name;
922 return l_res_id;
923 exception
924 when others then
925 return null;
926 end default_res_id;
927
928 function is_res_invalid(p_org_id in number,
929 p_dep_id in number,
930 p_res_id in number)
931 return boolean is
932 l_count number;
933 begin
934 select count(1)
935 into l_count
936 from bom_department_resources bdr,
937 bom_departments bd
938 where bdr.department_id= bd.department_id
939 and bd.organization_id = p_org_id
940 and bd.department_id = p_dep_id
941 and bdr.resource_id = p_res_id;
942
943 if l_count >= 1 then
944 return false;
945 else
946 return true;
947 end if;
948 end is_res_invalid;
949
950 function is_emp_shift_in(p_wip_entity_id in number,
951 p_employee_id in number)
952 return boolean is
953
954 l_org_id number;
955 l_count number;
956 l_value varchar2(2);
957 l_shift_value number;
958 begin
959
960 select organization_id
961 into l_org_id
962 from wip_entities
963 where wip_entity_id = p_wip_entity_id;
964
965 l_value := wip_ws_util.get_preference_value_code(p_pref_id => 26,
966 p_resp_key => WIP_WS_UTIL.get_current_resp_key,
967 p_org_id => l_org_id,
968 p_dept_id => null);
969 l_shift_value := to_number(l_value)/10;
970 if l_shift_value >= 2 then
971
972 /* If this query returns count as 1 then employee has already shifted in. */
973 select count(1)
974 into l_count
975 from wip_resource_actual_times
976 where organization_id = l_org_id
977 and employee_id = p_employee_id
978 and wip_entity_id is null
979 and time_entry_mode = 8
980 and end_date is null;
981
982 if l_count >= 1 then
983 return true;
984 else
985 return false;
986 end if;
987
988 else
989 return true;
990 end if;
991
992 exception
993 when others then
994 return false;
995
996 end is_emp_shift_in;
997
998 END WIP_TIME_ENTRY_PUB;