[Home] [Help]
PACKAGE BODY: APPS.WIP_TIME_ENTRY_PUB
Source
1 PACKAGE BODY WIP_TIME_ENTRY_PUB AS
2 /* $Header: wipwsilb.pls 120.10 2008/04/30 12:00:57 sisankar noship $ */
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 papf.employee_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 l_stmt_num := 60;
258
259 open running_intf_records(p_organization_id);
260 fetch running_intf_records bulk collect into v_wip_time_intf;
261 close running_intf_records;
262
263 l_stmt_num := 70;
264
265 if (g_logLevel <= wip_constants.trace_logging) then
266 wip_ws_util.trace_log('Launching Process to import the records');
267 end if;
268
269 process( p_wip_time_intf_tbl => v_wip_time_intf,
270 x_ret_status => l_return_status);
271
272 l_stmt_num := 80;
273
274 if (g_logLevel <= wip_constants.trace_logging) then
275 wip_ws_util.trace_log('Returned from Process after importing the records');
276 end if;
277
278 commit;
279
280 if l_return_status = 1 and l_status =0 then
281 retcode :=1;
282 errbuf := 'The Import program successfully imported all records';
283 wip_ws_util.trace_log(errbuf);
284 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',errbuf);
285 else
286 retcode := 1;
287 errbuf := 'The Import program marked at least one row as errored';
288 wip_ws_util.trace_log(errbuf);
289 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',errbuf);
290 end if;
291
292 exception
293 when e_null_org_id then
294 retcode := -1;
295 errbuf := 'Organization parameter cannot be null';
296 wip_ws_util.trace_log(errbuf);
297 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',errbuf);
298 when others then
299 retcode := -1;
300 errbuf := 'Errors encountered in interface txn, please check the log file.';
301 wip_ws_util.trace_log(errbuf);
302 wip_ws_util.trace_log(sqlerrm(sqlcode));
303 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',errbuf);
304 end process_interface_records;
305
306 procedure get_time_preferences(p_organization_id IN NUMBER default null,
307 x_shift_enabled OUT NOCOPY boolean,
308 x_clock_enabled OUT NOCOPY boolean)
309 is
310
311 l_level_id Number;
312 l_pref_value Number;
313 l_shift_value Number;
314 l_clock_value Number;
315 begin
316
317 begin
318 -- How to get Resp key here. Without Resp key this will return multiple rows.
319 -- One option is to use resp_id from which the concurrent program is executed.
320 -- another option is to use rownum. Since it doesn't make any sense to have
321 -- different settings w.r.t time entry parameters for different responsibilities,
322 -- we will use rownum.
323
324 select level_id
325 into l_level_id
326 from wip_preference_levels
327 where organization_id = p_organization_id
328 and department_id is null
329 and level_id in (select level_id
330 from wip_preference_values
331 where preference_id = 26)
332 and rownum=1;
333
334 exception
335 when no_data_found then
336 l_level_id :=1; -- set levet to Site level
337 when too_many_rows then -- It will never reach here.
338 l_level_id :=1; -- Currently set to site level.
339 when others then
340 l_level_id :=0;
341 end;
342
343 begin
344 select to_number(attribute_value_code)
345 into l_pref_value
346 from wip_preference_values
347 where level_id = l_level_id
348 and preference_id = 26;
349 exception
350 when no_data_found then
351 -- this might happen if lower level parameters have inherited them from upper levels.
352 -- if records are inherited from upper level, then we will look directly at site level.
353 -- if records are inherited, then we will consider site as the upper level and not responsibility.
354
355 select to_number(attribute_value_code)
356 into l_pref_value
357 from wip_preference_values
358 where level_id = 1
359 and preference_id = 26;
360
361 when others then
362 l_pref_value :=0;
363 end;
364
365 l_shift_value := l_pref_value/10;
366 l_clock_value := mod(l_pref_value,10);
367
368 If l_shift_value < 2 then
369 x_shift_enabled := false;
370 else
371 x_shift_enabled := true;
372 end if;
373
374 If l_clock_value = 1 then
375 x_clock_enabled := false;
376 else
377 x_clock_enabled := true;
378 end if;
379 end get_time_preferences;
380
381 function default_res_id(p_org_id in number,
382 p_wip_id in number,
383 p_op_seq in number,
384 p_res_seq in number)
385
386 return number is
387 l_res_id number;
388 begin
389
390 select resource_id
391 into l_res_id
392 from wip_operation_resources
393 where organization_id = p_org_id
394 and wip_entity_id = p_wip_id
395 and operation_seq_num = p_op_seq
396 and resource_seq_num = p_res_seq;
397
398 return l_res_id;
399 exception
400 when others then
401 return null;
402 end default_res_id;
403
404 procedure process(p_wip_time_intf_tbl in t_wip_time_intf,
405 x_ret_status out nocopy number)
406 is
407
408 l_wip_time_intf_tbl t_wip_time_intf;
409 l_counter number;
410 l_error_count number :=0;
411 l_stmt_num number;
412 ins_counter number;
413 type t_wip_res_actual_times is table of wip_resource_actual_times%rowtype index by binary_integer;
414 v_wip_res_actual_times t_wip_res_actual_times;
415
416 type t_interface_id is table of wip_time_entry_interface.interface_id%type index by binary_integer;
417 type t_process_status is table of wip_time_entry_interface.process_status%type index by binary_integer;
418 type t_error is table of wip_time_entry_interface.error%type index by binary_integer;
419
420 v_interface_id t_interface_id;
421 v_process_status t_process_status;
422 v_error t_error;
423 pop_counter number :=1;
424 l_bom_hr_uom varchar2(3);
425 l_duration number := 0;
426 g_logLevel number;
427
428 begin
429
430 g_logLevel := FND_LOG.g_current_runtime_level;
431 l_stmt_num :=10;
432 l_bom_hr_uom := fnd_profile.value('BOM:HOUR_UOM_CODE');
433 l_wip_time_intf_tbl := p_wip_time_intf_tbl;
434 l_counter := l_wip_time_intf_tbl.first;
435 ins_counter := 1;
436 l_stmt_num :=20;
437
438 if (g_logLevel <= wip_constants.trace_logging) then
439 wip_ws_util.trace_log('Beginning to process interface records');
440 end if;
441
442 while l_counter is not null loop
443 if l_wip_time_intf_tbl(l_counter).actual_start_date is null or
444 (l_wip_time_intf_tbl(l_counter).actual_end_date is null and
445 l_wip_time_intf_tbl(l_counter).duration is null) then
446 l_stmt_num :=30;
447 fnd_message.set_name('WIP','WIP_TIME_IMPORT_DATE_NULL');
448 l_wip_time_intf_tbl(l_counter).error := fnd_message.get;
449 l_wip_time_intf_tbl(l_counter).process_status := wip_constants.error;
450 l_error_count := l_error_count +1;
451 wip_ws_util.trace_log('Error Importing record '||'( stmt_num: '||l_stmt_num||') '||
452 l_wip_time_intf_tbl(l_counter).error||' for Intf Id: '||l_wip_time_intf_tbl(l_counter).interface_id);
453 goto skip_validations;
454 end if;
455 l_stmt_num :=35;
456 if l_wip_time_intf_tbl(l_counter).time_entry_type = 1 or
457 l_wip_time_intf_tbl(l_counter).time_entry_type = 2 then /* Actual Attendance Hours,
458 Scheduled Available Hours */
459 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
460 fnd_message.set_name('WIP','WIP_SAH_ONLY_ADJUSTMENT');
461 l_wip_time_intf_tbl(l_counter).error := fnd_message.get;
462 l_wip_time_intf_tbl(l_counter).process_status := wip_constants.error;
463 l_error_count := l_error_count +1;
464 wip_ws_util.trace_log('Error Importing record '||'( stmt_num: '||l_stmt_num||') '||
465 l_wip_time_intf_tbl(l_counter).error||' for Intf Id: '||l_wip_time_intf_tbl(l_counter).interface_id);
466 goto skip_validations;
467 end if;
468
469 l_stmt_num :=40;
470 if l_wip_time_intf_tbl(l_counter).wip_entity_id is not null or
471 l_wip_time_intf_tbl(l_counter).job_name is not null or
472 l_wip_time_intf_tbl(l_counter).operation_seq_num is not null or
473 l_wip_time_intf_tbl(l_counter).resource_seq_num is not null then
474 l_wip_time_intf_tbl(l_counter).wip_entity_id := null;
475 l_wip_time_intf_tbl(l_counter).operation_seq_num := null;
476 l_wip_time_intf_tbl(l_counter).resource_seq_num := null;
477 fnd_message.set_name('WIP','WIP_IMP_JOB_IGNORED');
478 l_wip_time_intf_tbl(l_counter).error := fnd_message.get;
479 l_wip_time_intf_tbl(l_counter).process_status := wip_constants.warning;
480 l_error_count := l_error_count +1;
481 if (g_logLevel <= wip_constants.trace_logging) then
482 wip_ws_util.trace_log('Error Importing record '||'( stmt_num: '||l_stmt_num||') '||
483 l_wip_time_intf_tbl(l_counter).error||' for Intf Id: '||l_wip_time_intf_tbl(l_counter).interface_id);
484 end if;
485 end if;
486 l_stmt_num :=50;
487 if l_wip_time_intf_tbl(l_counter).employee_id is null then
488 fnd_message.set_name('WIP','WIP_NO_EMP_DETAILS');
489 l_wip_time_intf_tbl(l_counter).error := fnd_message.get;
490 l_wip_time_intf_tbl(l_counter).process_status := wip_constants.error;
491 l_error_count := l_error_count +1;
492 wip_ws_util.trace_log('Error Importing record '||'( stmt_num: '||l_stmt_num||') '||
493 l_wip_time_intf_tbl(l_counter).error||' for Intf Id: '||l_wip_time_intf_tbl(l_counter).interface_id);
494 goto skip_validations;
495 end if;
496 l_stmt_num :=60;
497 if is_emp_invalid(p_org_id => l_wip_time_intf_tbl(l_counter).organization_id,
498 p_dep_id => null,
499 p_res_id => null,
500 p_emp_id => l_wip_time_intf_tbl(l_counter).employee_id) then
501 fnd_message.set_name('WIP','WIP_INVALID_EMP_DETAILS');
502 l_wip_time_intf_tbl(l_counter).error := fnd_message.get;
503 l_wip_time_intf_tbl(l_counter).process_status := wip_constants.error;
504 l_error_count := l_error_count +1;
505 wip_ws_util.trace_log('Error Importing record '||'( stmt_num: '||l_stmt_num||') '||
506 l_wip_time_intf_tbl(l_counter).error||' for Intf Id: '||l_wip_time_intf_tbl(l_counter).interface_id);
507 goto skip_validations;
508 end if;
509 l_stmt_num :=70;
510 elsif l_wip_time_intf_tbl(l_counter).time_entry_type = 3 then /* Direct Labor Hours */
511 l_stmt_num :=80;
512 if l_wip_time_intf_tbl(l_counter).wip_entity_id is null and
513 l_wip_time_intf_tbl(l_counter).job_name is null then
514 fnd_message.set_name('WIP','WIP_NO_JOB_DETAILS');
515 l_wip_time_intf_tbl(l_counter).error := fnd_message.get;
516 l_wip_time_intf_tbl(l_counter).process_status := wip_constants.error;
517 l_error_count := l_error_count +1;
518 wip_ws_util.trace_log('Error Importing record '||'( stmt_num: '||l_stmt_num||') '||
519 l_wip_time_intf_tbl(l_counter).error||' for Intf Id: '||l_wip_time_intf_tbl(l_counter).interface_id);
520 goto skip_validations;
521 elsif l_wip_time_intf_tbl(l_counter).wip_entity_id is null and
522 l_wip_time_intf_tbl(l_counter).job_name is not null then
523 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,
524 p_job_name => l_wip_time_intf_tbl(l_counter).job_name);
525 end if;
526 l_stmt_num :=90;
527 if is_job_invalid(p_org_id => l_wip_time_intf_tbl(l_counter).organization_id,
528 p_we_id => l_wip_time_intf_tbl(l_counter).wip_entity_id) then
529 fnd_message.set_name('WIP','WIP_INVALID_JOB_DETAILS');
530 l_wip_time_intf_tbl(l_counter).error := fnd_message.get;
531 l_wip_time_intf_tbl(l_counter).process_status := wip_constants.error;
532 l_error_count := l_error_count +1;
533 wip_ws_util.trace_log('Error Importing record '||'( stmt_num: '||l_stmt_num||') '||
534 l_wip_time_intf_tbl(l_counter).error||' for Intf Id: '||l_wip_time_intf_tbl(l_counter).interface_id);
535 goto skip_validations;
536 end if;
537 l_stmt_num :=100;
538 if l_wip_time_intf_tbl(l_counter).operation_seq_num is null then
539 fnd_message.set_name('WIP','WIP_JOB_OP_NULL');
540 l_wip_time_intf_tbl(l_counter).error := fnd_message.get;
541 l_wip_time_intf_tbl(l_counter).process_status := wip_constants.error;
542 l_error_count := l_error_count +1;
543 wip_ws_util.trace_log('Error Importing record '||'( stmt_num: '||l_stmt_num||') '||
544 l_wip_time_intf_tbl(l_counter).error||' for Intf Id: '||l_wip_time_intf_tbl(l_counter).interface_id);
545 goto skip_validations;
546 else
547 l_stmt_num :=110;
548 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,
549 p_we_id => l_wip_time_intf_tbl(l_counter).wip_entity_id,
550 p_op_seq => l_wip_time_intf_tbl(l_counter).operation_seq_num);
551 if l_wip_time_intf_tbl(l_counter).department_id < 0 then
552 fnd_message.set_name('WIP','WIP_INVALID_JOB_OP');
553 l_wip_time_intf_tbl(l_counter).error := fnd_message.get;
554 l_wip_time_intf_tbl(l_counter).process_status := wip_constants.error;
555 l_error_count := l_error_count +1;
556 wip_ws_util.trace_log('Error Importing record '||'( stmt_num: '||l_stmt_num||') '||
557 l_wip_time_intf_tbl(l_counter).error||' for Intf Id: '||l_wip_time_intf_tbl(l_counter).interface_id);
558 goto skip_validations;
559 end if;
560 end if;
561 l_stmt_num :=120;
562 if l_wip_time_intf_tbl(l_counter).resource_id is null and
563 l_wip_time_intf_tbl(l_counter).resource_code is null and
564 l_wip_time_intf_tbl(l_counter).resource_seq_num is null then
565 fnd_message.set_name('WIP','WIP_NO_RES_DETAILS');
566 l_wip_time_intf_tbl(l_counter).error := fnd_message.get;
567 l_wip_time_intf_tbl(l_counter).process_status := wip_constants.error;
568 l_error_count := l_error_count +1;
569 wip_ws_util.trace_log('Error Importing record '||'( stmt_num: '||l_stmt_num||') '||
570 l_wip_time_intf_tbl(l_counter).error||' for Intf Id: '||l_wip_time_intf_tbl(l_counter).interface_id);
571 goto skip_validations;
572 elsif l_wip_time_intf_tbl(l_counter).resource_id is null and
573 l_wip_time_intf_tbl(l_counter).resource_code is not null and
574 l_wip_time_intf_tbl(l_counter).resource_seq_num is null then
575 l_wip_time_intf_tbl(l_counter).resource_id := default_res_id(p_org_id => l_wip_time_intf_tbl(l_counter).organization_id,
576 p_res_name => l_wip_time_intf_tbl(l_counter).resource_code);
577 elsif l_wip_time_intf_tbl(l_counter).resource_id is null and
578 l_wip_time_intf_tbl(l_counter).resource_seq_num is not null then
579 l_wip_time_intf_tbl(l_counter).resource_id := default_res_id(p_org_id => l_wip_time_intf_tbl(l_counter).organization_id,
580 p_wip_id => l_wip_time_intf_tbl(l_counter).wip_entity_id,
581 p_op_seq => l_wip_time_intf_tbl(l_counter).operation_seq_num,
582 p_res_seq => l_wip_time_intf_tbl(l_counter).resource_seq_num);
583
584 end if;
585 l_stmt_num :=130;
586 if is_res_invalid(p_org_id => l_wip_time_intf_tbl(l_counter).organization_id,
587 p_dep_id => l_wip_time_intf_tbl(l_counter).department_id,
588 p_res_id => l_wip_time_intf_tbl(l_counter).resource_id) then
589 fnd_message.set_name('WIP','WIP_INVALID_RES_DETAILS');
590 l_wip_time_intf_tbl(l_counter).error := fnd_message.get;
591 l_wip_time_intf_tbl(l_counter).process_status := wip_constants.error;
592 l_error_count := l_error_count +1;
593 wip_ws_util.trace_log('Error Importing record '||'( stmt_num: '||l_stmt_num||') '||
594 l_wip_time_intf_tbl(l_counter).error||' for Intf Id: '||l_wip_time_intf_tbl(l_counter).interface_id);
595 goto skip_validations;
596 end if;
597 l_stmt_num :=140;
598 if l_wip_time_intf_tbl(l_counter).employee_id is null then
599 fnd_message.set_name('WIP','WIP_NO_EMP_DETAILS');
600 l_wip_time_intf_tbl(l_counter).error := fnd_message.get;
601 l_wip_time_intf_tbl(l_counter).process_status := wip_constants.error;
602 l_error_count := l_error_count +1;
603 wip_ws_util.trace_log('Error Importing record '||'( stmt_num: '||l_stmt_num||') '||
604 l_wip_time_intf_tbl(l_counter).error||' for Intf Id: '||l_wip_time_intf_tbl(l_counter).interface_id);
605 goto skip_validations;
606 end if;
607 l_stmt_num :=150;
608 if is_emp_invalid(p_org_id => l_wip_time_intf_tbl(l_counter).organization_id,
609 p_dep_id => l_wip_time_intf_tbl(l_counter).department_id,
610 p_res_id => l_wip_time_intf_tbl(l_counter).resource_id,
611 p_emp_id => l_wip_time_intf_tbl(l_counter).employee_id) then
612 fnd_message.set_name('WIP','WIP_INVALID_EMP_DETAILS');
613 l_wip_time_intf_tbl(l_counter).error := fnd_message.get;
614 l_wip_time_intf_tbl(l_counter).process_status := wip_constants.error;
615 l_error_count := l_error_count +1;
616 wip_ws_util.trace_log('Error Importing record '||'( stmt_num: '||l_stmt_num||') '||
617 l_wip_time_intf_tbl(l_counter).error||' for Intf Id: '||l_wip_time_intf_tbl(l_counter).interface_id);
618 goto skip_validations;
619 end if;
620 l_stmt_num :=160;
621 else
622 fnd_message.set_name('WIP','WIP_INVALID_TIME_TYPE');
623 l_wip_time_intf_tbl(l_counter).error := fnd_message.get;
624 l_wip_time_intf_tbl(l_counter).process_status := wip_constants.error;
625 l_error_count := l_error_count +1;
626 wip_ws_util.trace_log('Error Importing record '||'( stmt_num: '||l_stmt_num||') '||
627 l_wip_time_intf_tbl(l_counter).error||' for Intf Id: '||l_wip_time_intf_tbl(l_counter).interface_id);
628 goto skip_validations;
629 end if;
630 l_stmt_num :=180;
631 if ((l_wip_time_intf_tbl(l_counter).actual_end_date is null) or
632 (l_wip_time_intf_tbl(l_counter).actual_end_date is not null and
633 l_wip_time_intf_tbl(l_counter).duration is not null)) then
634
635 if wip_ws_labor_metric_pub.is_time_uom(l_wip_time_intf_tbl(l_counter).uom_code) <> 1 or
636 l_wip_time_intf_tbl(l_counter).uom_code is null then
637 fnd_message.set_name('WIP','WIP_INVALID_TIME_UOM');
638 l_wip_time_intf_tbl(l_counter).error := fnd_message.get;
639 l_wip_time_intf_tbl(l_counter).process_status := wip_constants.error;
640 l_error_count := l_error_count +1;
641 wip_ws_util.trace_log('Error Importing record '||'( stmt_num: '||l_stmt_num||') '||
642 l_wip_time_intf_tbl(l_counter).error||' for Intf Id: '||l_wip_time_intf_tbl(l_counter).interface_id);
643 goto skip_validations;
644 end if;
645
646 if l_wip_time_intf_tbl(l_counter).uom_code <> l_bom_hr_uom then
647 l_duration := inv_convert.inv_um_convert(item_id => -1,
648 precision => 38,
649 from_quantity => l_wip_time_intf_tbl(l_counter).duration,
650 from_unit => l_wip_time_intf_tbl(l_counter).uom_code,
651 to_unit => l_bom_hr_uom,
652 from_name => null,
653 to_name => null);
654 else
655 l_duration := l_wip_time_intf_tbl(l_counter).duration;
656 end if;
657 l_wip_time_intf_tbl(l_counter).actual_end_date := l_wip_time_intf_tbl(l_counter).actual_start_date +
658 (l_duration/24);
659 l_wip_time_intf_tbl(l_counter).duration := l_duration;
660 l_wip_time_intf_tbl(l_counter).uom_code := l_bom_hr_uom;
661 end if;
662 l_stmt_num :=190;
663 if l_wip_time_intf_tbl(l_counter).duration is null then
664 l_wip_time_intf_tbl(l_counter).duration := (l_wip_time_intf_tbl(l_counter).actual_end_date -
665 l_wip_time_intf_tbl(l_counter).actual_start_date)*24;
666 l_wip_time_intf_tbl(l_counter).uom_code := l_bom_hr_uom;
667 end if;
668
669 if (g_logLevel <= wip_constants.trace_logging) then
670 wip_ws_util.trace_log('Completed validations for record in Intf Id: '||l_wip_time_intf_tbl(l_counter).interface_id);
671 end if;
672
673 l_stmt_num :=200;
674 <<skip_validations>>
675 if l_wip_time_intf_tbl(l_counter).process_status = wip_constants.running or
676 l_wip_time_intf_tbl(l_counter).process_status = wip_constants.warning then
677 select wip_resource_actual_times_s.nextval
678 into v_wip_res_actual_times(ins_counter).time_entry_id from dual;
679 v_wip_res_actual_times(ins_counter).organization_id := l_wip_time_intf_tbl(l_counter).organization_id;
680 v_wip_res_actual_times(ins_counter).wip_entity_id := l_wip_time_intf_tbl(l_counter).wip_entity_id;
681 v_wip_res_actual_times(ins_counter).operation_seq_num := l_wip_time_intf_tbl(l_counter).operation_seq_num;
682 v_wip_res_actual_times(ins_counter).resource_id := l_wip_time_intf_tbl(l_counter).resource_id;
683 v_wip_res_actual_times(ins_counter).resource_seq_num := l_wip_time_intf_tbl(l_counter).resource_seq_num;
684 v_wip_res_actual_times(ins_counter).instance_id := null;
685 v_wip_res_actual_times(ins_counter).serial_number := null;
686 select decode(l_wip_time_intf_tbl(l_counter).time_entry_type,1,6,2,7,3,5)
687 into v_wip_res_actual_times(ins_counter).time_entry_mode from dual;
688 v_wip_res_actual_times(ins_counter).cost_flag := 'N';
689 v_wip_res_actual_times(ins_counter).add_to_rtg := 'N';
690 v_wip_res_actual_times(ins_counter).status_type := 2;
691 v_wip_res_actual_times(ins_counter).start_date := l_wip_time_intf_tbl(l_counter).actual_start_date;
692 v_wip_res_actual_times(ins_counter).end_date := l_wip_time_intf_tbl(l_counter).actual_end_date;
693 v_wip_res_actual_times(ins_counter).projected_completion_date := null;
694 v_wip_res_actual_times(ins_counter).duration := l_wip_time_intf_tbl(l_counter).duration;
695 v_wip_res_actual_times(ins_counter).uom_code := l_wip_time_intf_tbl(l_counter).uom_code;
696 v_wip_res_actual_times(ins_counter).employee_id := l_wip_time_intf_tbl(l_counter).employee_id;
697 v_wip_res_actual_times(ins_counter).process_status := 1;
698 v_wip_res_actual_times(ins_counter).created_by := fnd_global.user_id;
699 v_wip_res_actual_times(ins_counter).creation_date := sysdate;
700 v_wip_res_actual_times(ins_counter).last_updated_by := fnd_global.user_id;
701 v_wip_res_actual_times(ins_counter).last_update_date := sysdate;
702 v_wip_res_actual_times(ins_counter).last_update_login := fnd_global.login_id;
703 v_wip_res_actual_times(ins_counter).object_version_number := 1;
704 v_wip_res_actual_times(ins_counter).action_flag := l_wip_time_intf_tbl(l_counter).action_flag;
705 v_wip_res_actual_times(ins_counter).request_id := fnd_global.conc_request_id;
706 v_wip_res_actual_times(ins_counter).program_id := fnd_global.conc_program_id;
707 v_wip_res_actual_times(ins_counter).program_application_id := fnd_global.prog_appl_id;
708 v_wip_res_actual_times(ins_counter).program_update_date := sysdate;
709 ins_counter := ins_counter+1;
710 end if;
711 v_process_status(pop_counter) := l_wip_time_intf_tbl(l_counter).process_status;
712 v_error(pop_counter) := l_wip_time_intf_tbl(l_counter).error;
713 v_interface_id(pop_counter) :=l_wip_time_intf_tbl(l_counter).interface_id;
714 pop_counter := pop_counter+1;
715 l_counter := l_wip_time_intf_tbl.next(l_counter);
716 end loop;
717
718 if (g_logLevel <= wip_constants.trace_logging) then
719 wip_ws_util.trace_log('Before Inserting records in Actual Times Table');
720 end if;
721
722 -- insert into wip_resource_actual_times table
723 forall ins_index in v_wip_res_actual_times.first..v_wip_res_actual_times.last
724 INSERT into WIP_RESOURCE_ACTUAL_TIMES values v_wip_res_actual_times(ins_index);
725
726 if (g_logLevel <= wip_constants.trace_logging) then
727 wip_ws_util.trace_log('After Inserting records in Actual Times Table');
728 end if;
729
730 -- update back wip_time_entry_interface table
731 forall upd_index in v_interface_id.first..v_interface_id.last
732 update wip_time_entry_interface set
733 process_status = decode(v_process_status(upd_index),
734 wip_constants.running,wip_constants.completed,v_process_status(upd_index) ),
735 error = v_error(upd_index),
736 last_update_date = sysdate
737 where interface_id = v_interface_id(upd_index);
738
739 if (g_logLevel <= wip_constants.trace_logging) then
740 wip_ws_util.trace_log('After Updating status for records in Interface Table');
741 end if;
742
743 if l_error_count > 0 then
744 x_ret_status := -1;
745 else
746 x_ret_status := 1;
747 end if;
748 end process;
749
750 procedure write_to_log(p_interface_id in number,
751 p_error_msg in varchar2,
752 p_stmt_num in number)
753 is
754 begin
755 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);
756 fnd_file.new_line(fnd_file.log, 2);
757 end write_to_log;
758
759 function is_emp_invalid(p_org_id in number,
760 p_dep_id in number,
761 p_res_id in number,
762 p_emp_id in number)
763 return boolean is
764 l_count number;
765 begin
766 if p_dep_id is not null then
767 select count(1)
768 into l_count
769 from bom_resource_employees bre,
770 bom_department_resources bdr
771 where bdr.department_id = p_dep_id
772 and bdr.resource_id = p_res_id
773 and bdr.resource_id = bre.resource_id
774 and bre.organization_id = p_org_id
775 and bre.person_id = p_emp_id;
776 else
777 select count(1)
778 into l_count
779 from bom_resource_employees bre
780 where bre.organization_id = p_org_id
781 and bre.person_id = p_emp_id
782 and bre.resource_id = nvl(p_res_id,bre.resource_id);
783 end if;
784 if l_count >= 1 then
785 return false;
786 else
787 return true;
788 end if;
789 end is_emp_invalid;
790
791 function default_job_id(p_org_id in number,
792 p_job_name in varchar2)
793 return number is
794 l_we_id number;
795 begin
796 select wip_entity_id
797 into l_we_id
798 from wip_entities
799 where wip_entity_name = p_job_name
800 and organization_id = p_org_id;
801 return l_we_id;
802 exception
803 when others then
804 return null;
805 end default_job_id;
806
807 function is_job_invalid(p_org_id in number,
808 p_we_id in number)
809 return boolean is
810 l_count number;
811 begin
812 select count(1)
813 into l_count
814 from wip_discrete_jobs wdj
815 where wdj.organization_id = p_org_id
816 and wdj.wip_entity_id = p_we_id
817 and wdj.status_type in (wip_constants.released,wip_constants.comp_chrg,wip_constants.hold);
818
819 if l_count = 0 then
820 return true;
821 else
822 return false;
823 end if;
824 end is_job_invalid;
825
826 function get_op_dept_id(p_org_id in number,
827 p_we_id in number,
828 p_op_seq in number)
829 return number is
830 l_dep_id number;
831 begin
832 select department_id
833 into l_dep_id
834 from wip_operations
835 where organization_id=p_org_id
836 and wip_entity_id=p_we_id
837 and operation_seq_num=p_op_seq;
838 return l_dep_id;
839 exception
840 when others then
841 return -1;
842 end get_op_dept_id;
843
844 function default_res_id(p_org_id in number,
845 p_res_name in varchar)
846 return number is
847 l_res_id number;
848 begin
849 select resource_id
850 into l_res_id
851 from bom_resources
852 where organization_id = p_org_id
853 and resource_code = p_res_name;
854 return l_res_id;
855 exception
856 when others then
857 return null;
858 end default_res_id;
859
860 function is_res_invalid(p_org_id in number,
861 p_dep_id in number,
862 p_res_id in number)
863 return boolean is
864 l_count number;
865 begin
866 select count(1)
867 into l_count
868 from bom_department_resources bdr,
869 bom_departments bd
870 where bdr.department_id= bd.department_id
871 and bd.organization_id = p_org_id
872 and bd.department_id = p_dep_id
873 and bdr.resource_id = p_res_id;
874
875 if l_count >= 1 then
876 return false;
877 else
878 return true;
879 end if;
880 end is_res_invalid;
881
882 function is_emp_shift_in(p_wip_entity_id in number,
883 p_employee_id in number)
884 return boolean is
885
886 l_org_id number;
887 l_count number;
888 l_value varchar2(2);
889 l_shift_value number;
890 begin
891
892 select organization_id
893 into l_org_id
894 from wip_entities
895 where wip_entity_id = p_wip_entity_id;
896
897 l_value := wip_ws_util.get_preference_value_code(p_pref_id => 26,
898 p_resp_key => WIP_WS_UTIL.get_current_resp_key,
899 p_org_id => l_org_id,
900 p_dept_id => null);
901 l_shift_value := to_number(l_value)/10;
902 if l_shift_value >= 2 then
903
904 /* If this query returns count as 1 then employee has already shifted in. */
905 select count(1)
906 into l_count
907 from wip_resource_actual_times
908 where organization_id = l_org_id
909 and employee_id = p_employee_id
910 and wip_entity_id is null
911 and time_entry_mode = 8
912 and end_date is null;
913
914 if l_count >= 1 then
915 return true;
916 else
917 return false;
918 end if;
919
920 else
921 return true;
922 end if;
923
924 exception
925 when others then
926 return false;
927
928 end is_emp_shift_in;
929
930 END WIP_TIME_ENTRY_PUB;