1 PACKAGE BODY WIP_WS_LABOR_METRIC_PUB AS
2 /* $Header: wipwslmb.pls 120.9.12020000.7 2013/02/12 15:20:19 sisankar ship $ */
3
4 /* To Get Calendar Code for an organization */
5 function get_calendar_code(p_organization_id number)
6 return varchar2 is
7 l_calendar_code varchar2(100) := null;
8 begin
9 select mp.calendar_code
10 into l_calendar_code
11 from mtl_parameters mp
12 where mp.organization_id = p_organization_id;
13 return l_calendar_code;
14 exception
15 when others then return null;
16 end get_calendar_code;
17
18 /* To validate whether the UOM is time based */
19 function is_time_uom(p_uom_code in varchar2)
20 return number is
21 l_uom_class varchar2(10);
22 l_time_based_uom_flag number;
23
24 cursor time_based_uom_cursor is
25 select distinct muc1.uom_class
26 from mtl_uom_conversions muc1,
27 mtl_uom_conversions muc2
28 where (muc1.uom_class = muc2.uom_class
29 and nvl(muc1.disable_date, sysdate + 1) > sysdate)
30 and nvl(muc2.disable_date, sysdate + 1) > sysdate
31 and muc1.uom_code = fnd_profile.value('BOM:HOUR_UOM_CODE')
32 and muc2.uom_code = p_uom_code;
33 begin
34 open time_based_uom_cursor;
35 fetch time_based_uom_cursor into l_uom_class;
36 if time_based_uom_cursor%notfound then
37 l_time_based_uom_flag := 2;
38 else
39 l_time_based_uom_flag := 1;
40 end if;
41 close time_based_uom_cursor;
42 return l_time_based_uom_flag;
43 end is_time_uom;
44
45 /* To obtain end of the time window for metrics calculation */
46 function get_period_end(p_calendar_code in varchar2,
47 p_date in date)
48 return date is
49 l_end_time Date := null;
50 l_temp_time Date;
51 begin
52
53 l_temp_time := p_date;
54 select
55 max(bsd.shift_date + st.to_time/(60*60*24))
56 into l_end_time
57 from
58 bom_shift_dates bsd,
59 (select bst.shift_num,
60 min(bst.from_time) from_time,
61 max(decode(sign(bst.to_time - bst.from_time), -1, 24*60*60, 0) + bst.to_time) to_time
62 from bom_shift_times bst
63 where bst.calendar_code = p_calendar_code
64 group by bst.shift_num) st
65 where bsd.calendar_code = p_calendar_code
66 and bsd.shift_num = st.shift_num
67 and bsd.exception_set_id = -1
68 and bsd.seq_num is not null
69 and bsd.shift_date = trunc(p_date)
70 and bsd.shift_date + st.to_time/(60*60*24) <= l_temp_time
71 and bsd.shift_date + st.from_time/(60*60*24) < l_temp_time;
72
73 while l_end_time is null loop
74
75 l_temp_time := l_temp_time-1;
76
77 select
78 max(bsd.shift_date + st.to_time/(60*60*24))
79 into l_end_time
80 from
81 bom_shift_dates bsd,
82 (select bst.shift_num,
83 max(decode(sign(bst.to_time - bst.from_time), -1, 24*60*60, 0) + bst.to_time) to_time
84 from bom_shift_times bst
85 where bst.calendar_code = p_calendar_code
86 group by bst.shift_num) st
87 where bsd.calendar_code = p_calendar_code
88 and bsd.shift_num = st.shift_num
89 and bsd.exception_set_id = -1
90 and bsd.seq_num is not null
91 and bsd.shift_date = trunc(l_temp_time);
92
93 end loop;
94
95 return l_end_time;
96
97 end get_period_end;
98
99 /* To obtain start of the time window for metrics calculation */
100 function get_period_start(p_org_id in number,
101 p_date in date)
102 return date
103 is
104 l_start_time Date;
105 begin
106 l_start_time := trunc(p_date)-6;
107 /* Commented this code since calculation is for last 7 calendar days and not last 7 working days.
108 for i in 1..6 loop
109 l_start_time := mrp_calendar.PREV_WORK_DAY(p_org_id,1,l_start_time-1);
110 end loop;*/
111 return l_start_time;
112 end get_period_start;
113
114 /* To obtain date for a given seq_num and shift_num (Used in Charts only) */
115 function get_date_for_seq(p_org_id in number,
116 p_seq_num in number,
117 p_shift_num in number)
118 return date
119 is
120 l_eff_date Date;
121 l_converted_date date;
122 l_calendar_code varchar2(50);
123 l_timezone_enabled boolean := ( fnd_profile.value('ENABLE_TIMEZONE_CONVERSIONS') = 'Y' AND
124 fnd_profile.value('CLIENT_TIMEZONE_ID') IS NOT NULL AND
125 fnd_profile.value('SERVER_TIMEZONE_ID') IS NOT NULL AND
126 fnd_profile.value('CLIENT_TIMEZONE_ID') <>
127 fnd_profile.value('SERVER_TIMEZONE_ID'));
128 l_client_id number := fnd_profile.value('CLIENT_TIMEZONE_ID');
129 l_server_id number := fnd_profile.value('SERVER_TIMEZONE_ID');
130 begin
131 l_calendar_code := get_calendar_code(p_org_id);
132 begin
133 --Fix for bug 11847040, must consider the time component too so that the date will be properly converted
134 select shift_date + at.from_time /(24*60*60)
135 into l_eff_date
136 from bom_shift_dates,
137 (select min(bst.from_time) from_time
138 from bom_shift_times bst
139 where bst.calendar_code = l_calendar_code
140 and bst.shift_num = p_shift_num
141 ) at
142 where calendar_code = l_calendar_code
143 and exception_set_id = -1
144 and seq_num = p_seq_num
145 and shift_num =p_shift_num;
146 exception
147 when no_data_found
148 then l_eff_date := null;
149 end;
150
151 if l_timezone_enabled and l_eff_date is not null then
152 l_converted_date := trunc(hz_timezone_pub.convert_datetime(l_server_id,
153 l_client_id,
154 l_eff_date));
155 else
156 l_converted_date := trunc(l_eff_date);
157 end if;
158
159 return l_converted_date;
160 end get_date_for_seq;
161
162 /* To obtain date for a given seq_num and shift_num */
163 function get_date(p_org_id in number,
164 p_seq_num in number,
165 p_shift_num in number)
166 return date
167 is
168 l_eff_date Date;
169 l_calendar_code varchar2(50);
170 begin
171 l_calendar_code := get_calendar_code(p_org_id);
172 begin
173 select shift_date
174 into l_eff_date
175 from bom_shift_dates
176 where calendar_code = l_calendar_code
177 and exception_set_id = -1
178 and seq_num = p_seq_num
179 and shift_num =p_shift_num;
180 return l_eff_date;
181 exception
182 when no_data_found then return null;
183 end;
184 end get_date;
185
186 function get_index_for_date(p_date in date,
187 p_organization_id in number,
188 p_dept_id in number,
189 p_resource_id in number)
190 return varchar2
191 is
192 l_date_seq number;
193 l_shift_num number;
194 l_shift_start_date date;
195 l_shift_end_date date;
196 l_calendar_code varchar2(100);
197 begin
198
199 l_calendar_code := get_calendar_code(p_organization_id);
200 wip_ws_dl_util.get_first_dept_resource_shift
201 (p_cal_code =>l_calendar_code,
202 p_dept_id =>p_dept_id,
203 p_resource_id =>p_resource_id,
204 p_date =>p_date,
205 x_shift_seq =>l_date_seq,
206 x_shift_num =>l_shift_num,
207 x_shift_start_date =>l_shift_start_date,
208 x_shift_end_date =>l_shift_end_date);
209
210 return to_char(l_date_seq||':'||l_shift_num);
211 end get_index_for_date;
212
213 procedure handle_error(p_error_msg in varchar2,
214 p_stmt_num in number,
215 p_proc_name in varchar2)
216 is
217 begin
218 fnd_file.put_line(fnd_file.log,'Error in '||p_proc_name||'( stmt_num: '||p_stmt_num||') '||p_error_msg);
219 fnd_file.new_line(fnd_file.log, 3);
220 end handle_error;
221
222 /* To calculate metrics for the required period */
223 procedure calculate_metrics(retcode out nocopy number,
224 errbuf out nocopy varchar2,
225 p_organization_id in number)
226 is
227
228 /* Record type for storing metrics data for a particular employee */
229 type rec_emp_perf_temp IS record(
230 actual_date_seq number,
231 shift_num number,
232 shift_start date,
233 shift_end date,
234 shift_duration number,
235 act_att_hrs number,
236 dir_lab_hrs number,
237 sch_avl_hrs number,
238 earned_hrs number);
239
240 /* PL/SQL table for storing metrics data for a particular employee */
241 type t_emp_perf_temp is table of rec_emp_perf_temp index by varchar2(25);
242 v_emp_perf_temp t_emp_perf_temp;
243
244 type t_wip_lab_perf_rate is table of wip_labor_performance_rates%rowtype index by binary_integer;
245 v_wip_lab_perf_rate t_wip_lab_perf_rate;
246
247 type t_date_sequence is table of wip_labor_performance_rates.date_sequence%type index by binary_integer;
248 type t_shift_num is table of wip_labor_performance_rates.shift_num%type index by binary_integer;
249 type t_m1_att1 is table of wip_labor_performance_rates.metric1_attribute1%type index by binary_integer;
250 type t_m1_att2 is table of wip_labor_performance_rates.metric1_attribute2%type index by binary_integer;
251 type t_m2_att1 is table of wip_labor_performance_rates.metric2_attribute1%type index by binary_integer;
252 type t_m2_att2 is table of wip_labor_performance_rates.metric2_attribute2%type index by binary_integer;
253 type t_m3_att1 is table of wip_labor_performance_rates.metric3_attribute1%type index by binary_integer;
254 type t_m3_att2 is table of wip_labor_performance_rates.metric3_attribute2%type index by binary_integer;
255
256 v_date_sequence t_date_sequence;
257 v_shift_num t_shift_num;
258 v_m1_att1 t_m1_att1;
259 v_m1_att2 t_m1_att2;
260 v_m2_att1 t_m2_att1;
261 v_m2_att2 t_m2_att2;
262 v_m3_att1 t_m3_att1;
263 v_m3_att2 t_m3_att2;
264
265 /* cursor to get all employees for a particular organization */
266 cursor employees(org_id number) is
267 select bre.organization_id,
268 bdri.department_id,
269 bdri.resource_id,
270 bdri.instance_id,
271 bre.person_id
272 from per_all_people_f papf,
273 bom_resource_employees bre,
274 bom_dept_res_instances bdri,
275 bom_department_resources bdr,
276 bom_resources br
277 where papf.person_id = bre.person_id
278 and bre.instance_id = bdri.instance_id
279 and sysdate between papf.effective_start_date and nvl(papf.effective_end_date,sysdate+1)
280 and bre.resource_id = bdri.resource_id
281 and bdri.department_id = bdr.department_id
282 and bdri.resource_id = bdr.resource_id
283 and bdr.resource_id = br.resource_id
284 and bdr.available_24_hours_flag = 2
285 and wip_ws_labor_metric_pub.is_time_uom(br.unit_of_measure) = 1
286 and bre.organization_id = org_id
287 order by bdri.department_id,
288 bdri.resource_id;
289
290 /* cursor to get all preferences value for a particular organization */
291 cursor get_preferences(org_id number) is
292 select attribute_name,
293 substr(attribute_name,2,1) as identifier,
294 attribute_value_code
295 from wip_preference_values
296 where preference_id = 35
297 and level_id = 1
298 and sequence_number = (select sequence_number
299 from wip_preference_values
300 where preference_id = 35
301 and level_id = 1
302 and attribute_name = 'Org'
303 and attribute_value_code = to_char(org_id))
304 order by 1 desc;
305
306 /* This Cursor will get the information of all shifts for a department resource in the required period. */
307 -- Modified Date conversion logic for Bug 6972082.
308 cursor emp_shifts(p_calendar_code varchar2,
309 start_period date,
310 end_period date,
311 dept_id number,
312 res_id number) is
313 select trunc(bsd.seq_num) shift_date_seq,
314 bsd.shift_num shift_num,
315 bsd.shift_date + st.from_time/(60*60*24) shift_start_time,
316 bsd.shift_date + st.to_time/(60*60*24) shift_end_time,
317 (bsd.shift_date + st.to_time/(60*60))-(bsd.shift_date + st.from_time/(60*60)) shift_duration
318 from bom_shift_dates bsd,
319 (select bst.shift_num,
320 min(bst.from_time) from_time,
321 max(bst.to_time + decode(sign(bst.to_time - bst.from_time), -1, (24*60*60), 0)) to_time
322 from bom_shift_times bst
323 where bst.calendar_code = p_calendar_code
324 group by bst.shift_num) st,
325 bom_calendar_shifts bcs,
326 bom_resource_shifts brs
327 where bsd.calendar_code = p_calendar_code
328 and bsd.exception_set_id = -1
329 and bsd.shift_date between trunc(start_period) and trunc(end_period)
330 and bsd.shift_num = st.shift_num
331 and bsd.seq_num is not null
332 and bsd.calendar_code = bcs.calendar_code
333 and bsd.shift_num = bcs.shift_num
334 /*Fix bug 14674233, shouldn't use the operator < because it should be inclusive of end_period
335 end period is the last shift before sysdate and was retrieved by get_period_end(). So, if we
336 used operator < instead of <=, the last shift will be considered as part of labor metric calculation*/
337 and fnd_date.canonical_to_date(to_char((bsd.shift_date + st.to_time/(60*60*24)),WIP_CONSTANTS.DATETIME_FMT)) <= end_period
338 and brs.shift_num= bcs.shift_num
339 and brs.department_id = dept_id
340 and brs.resource_id = res_id;
341
342 /* cursor to get records from wip_resource_actual_times for calculating AAH */
343 cursor aah_records(org_id number,
344 emp_id number,
345 start_time date,
346 end_time date) is
347 select start_date as actual_date,
348 decode(action_flag,2,-duration,duration) as duration,
349 uom_code
350 from wip_resource_actual_times
351 where organization_id = org_id
352 and employee_id = emp_id
353 and wip_entity_id is null
354 and time_entry_mode in (6,8)
355 and start_date between start_time and end_time
356 and duration is not null;
357
358 /* cursor to get records from wip_resource_actual_times for calculating SAH */
359 cursor sah_records(org_id number,
360 emp_id number,
361 start_time date,
362 end_time date) is
363 select start_date as actual_date,
364 decode(action_flag,2,-duration,duration) as duration,
365 uom_code
366 from wip_resource_actual_times
367 where organization_id = org_id
368 and employee_id = emp_id
369 and wip_entity_id is null
370 and time_entry_mode =7
371 and start_date between start_time and end_time
372 and duration is not null;
373
374 /* cursor to get all records from wip_resource_actual_times for calculating DLH */
375 -- Lot based jobs also uses wip_resource_actual_times. So added condition on entity_type.
376 -- Fix for Bug 12614160. Consider Borrowed Resources. Removed condition on Department and added instance id.
377 cursor dlh_records(org_id number,
378 dept_id number,
379 res_id number,
380 inst_id number,
381 emp_id number,
382 start_time date,
383 end_time date) is
384 select wrat.start_date as actual_date,
385 decode(wrat.action_flag,2,-wrat.duration,wrat.duration) as duration,
386 wrat.uom_code
387 from wip_resource_actual_times wrat,
388 wip_entities we
389 where wrat.organization_id = org_id
390 and we.organization_id = wrat.organization_id
391 and we.wip_entity_id = wrat.wip_entity_id
392 and we.entity_type = 1
393 and wrat.instance_id = inst_id
394 and wrat.resource_id = res_id
395 and wrat.employee_id = emp_id
396 and wrat.wip_entity_id is not null
397 and wrat.duration is not null
398 and wrat.start_date between start_time and end_time;
399
400 /* Bug 7010115: cursor to get all Job-Ops from Clock-In records for calculating earned hours */
401 -- Fix for Bug 12614160. Consider Borrowed Resources. Removed condition on Department and added instance id.
402 cursor clock_in_records(org_id number,
403 dept_id number,
404 res_id number,
405 inst_id number,
406 emp_id number,
407 start_time date,
408 end_time date) is
409 select distinct wrat.wip_entity_id as wip_entity_id,
410 wrat.operation_seq_num as operation_seq_num,
411 get_index_for_date(wrat.start_date,wrat.organization_id,dept_id,res_id) as emp_index
412 from wip_resource_actual_times wrat,
413 wip_entities we
414 where wrat.organization_id = org_id
415 and we.organization_id = wrat.organization_id
416 and we.wip_entity_id = wrat.wip_entity_id
417 and we.entity_type=1
418 and wrat.instance_id = inst_id
419 and wrat.resource_id = res_id
420 and wrat.employee_id = emp_id
421 and wrat.wip_entity_id is not null
422 and wrat.duration is not null
423 and wrat.start_date between start_time and end_time;
424
425 /* cursor to get all records from wip_move_transactions, wip_operations for obtaining completed quantity */
426 /* Modified for Bug 7010115. */
427 -- Lot based jobs should not be considered for earned hours. So added condition on entity_type.
428 cursor eh_records(org_id number,
429 start_time date,
430 end_time date,
431 wip_id number,
432 op_seq number,
433 res_id number,
434 indx varchar2,
435 dep_id number)
436 is
437 select nvl(sum(nvl(t.move_qty,0)),0) as moved_qty
438 from
439 (select
440 wo.wip_entity_id as job,
441 wo.operation_seq_num as op,
442 fm_operation_seq_num as fm_op,
443 fm_intraoperation_step_type as fm_step,
444 to_operation_seq_num as to_op,
445 to_intraoperation_step_type as to_step,
446 decode(wmt.to_intraoperation_step_type,
447 1, (case when ((wmt.to_operation_seq_num > wo.operation_seq_num)) then wmt.primary_quantity
448 when ((wmt.to_operation_seq_num < wo.operation_seq_num)) then -1 * wmt.primary_quantity
449 when (wmt.to_operation_seq_num <= wmt.fm_operation_seq_num
450 and wmt.to_operation_seq_num = wo.operation_seq_num
451 and wmt.fm_intraoperation_step_type not in (4,5)) then -1 * wmt.primary_quantity
452 else 0 end),
453 2, (case when ((wmt.to_operation_seq_num > wo.operation_seq_num)) then wmt.primary_quantity
454 when ((wmt.to_operation_seq_num < wo.operation_seq_num)) then -1 * wmt.primary_quantity
455 when (wmt.to_operation_seq_num <= wmt.fm_operation_seq_num
456 and wmt.to_operation_seq_num = wo.operation_seq_num
457 and wmt.fm_intraoperation_step_type not in (4,5)) then -1 * wmt.primary_quantity
458 else 0 end),
459 3, (case when (wmt.to_operation_seq_num >= wo.operation_seq_num) then wmt.primary_quantity
460 when (wmt.to_operation_seq_num < wo.operation_seq_num
461 and (wmt.fm_intraoperation_step_type not in (4,5)
462 or wmt.fm_operation_seq_num <> wo.operation_seq_num)) then -1 * wmt.primary_quantity
463 else 0 end),
464 5, (case when wmt.to_operation_seq_num > wo.operation_seq_num then wmt.primary_quantity
465 when wmt.to_operation_seq_num < wo.operation_seq_num then -1 * wmt.primary_quantity
466 when wmt.to_operation_seq_num = wo.operation_seq_num then 0
467 else 0 end),
468 4, (case when wmt.to_operation_seq_num > wo.operation_seq_num then wmt.primary_quantity
469 when wmt.to_operation_seq_num < wo.operation_seq_num then -1 * wmt.primary_quantity
470 when wmt.to_operation_seq_num = wo.operation_seq_num then 0
471 else 0 end),
472 0) as move_qty,
473 get_index_for_date(wmt.transaction_date,wmt.organization_id,dep_id,res_id) as emp_index
474 from
475 wip_move_transactions wmt,
476 wip_operations wo,
477 wip_entities we
478 where
479 wo.organization_id = org_id
480 and wo.organization_id = wmt.organization_id
481 and wo.organization_id = we.organization_id
482 and wo.wip_entity_id = we.wip_entity_id
483 and we.entity_type=1
484 and wo.wip_entity_id = wmt.wip_entity_id
485 and wo.operation_seq_num = op_seq
486 and wo.wip_entity_id = wip_id
487 and wo.repetitive_schedule_id is null
488 and wmt.transaction_date between start_time and end_time
489 and ((wo.operation_seq_num >= wmt.fm_operation_seq_num + decode(sign(wmt.fm_intraoperation_step_type - 2),1,1,0)
490 and wo.operation_seq_num < wmt.to_operation_seq_num + decode(sign(wmt.to_intraoperation_step_type - 2),1,1,0)
491 and (wmt.to_operation_seq_num > wmt.fm_operation_seq_num
492 or (wmt.to_operation_seq_num = wmt.fm_operation_seq_num
493 and wmt.fm_intraoperation_step_type <= 2
494 and wmt.to_intraoperation_step_type > 2)))
495 or (wo.operation_seq_num < wmt.fm_operation_seq_num + decode(sign(wmt.fm_intraoperation_step_type-2),1,1,0)
496 and wo.operation_seq_num >= wmt.to_operation_seq_num + decode(sign(wmt.to_intraoperation_step_type-2),1,1,0)
497 and (wmt.fm_operation_seq_num > wmt.to_operation_seq_num
498 or (wmt.fm_operation_seq_num = wmt.to_operation_seq_num
499 and wmt.to_intraoperation_step_type <= 2
500 and wmt.fm_intraoperation_step_type > 2)))
501 or (wmt.fm_intraoperation_step_type in (4,5)
502 and wo.operation_seq_num = wmt.fm_operation_seq_num))) t
503 where
504 t.emp_index = indx;
505
506 -- Fix for Bug 12614160. Consider cases where resource doesn't exist on the job operation.
507 cursor ear_hours_calc(org_id number,
508 we_ent_id number,
509 op_seq_num number,
510 res_id number,
511 move_qty number)
512 is
513 select usage_rate_or_amount *decode(sign(move_qty),-1,0,move_qty) usage_rate,uom_code,basis_type, 1 resource_pick
514 from wip_operation_resources
515 where organization_id= org_id
516 and wip_entity_id= we_ent_id
517 and operation_seq_num= op_seq_num
518 and resource_id = res_id
519 union all
520 select usage_rate_or_amount *decode(sign(move_qty),-1,0,move_qty) usage_rate,uom_code,basis_type, 2 resource_pick
521 from wip_operation_resources wor
522 where organization_id= org_id
523 and wip_entity_id= we_ent_id
524 and operation_seq_num= op_seq_num
525 and resource_seq_num = (select min(wor.resource_seq_num)
526 from wip_operation_resources wor,bom_resources br
527 where wor.organization_id= org_id
528 and wor.wip_entity_id= we_ent_id
529 and wor.operation_seq_num= op_seq_num
530 and WIP_WS_LABOR_METRIC_PUB.is_time_uom(wor.uom_code) = 1
531 and nvl(wor.basis_type,1) <> 2
532 and wor.organization_id = br.organization_id
533 and wor.resource_id = br.resource_id
534 and br.resource_type = 2)
535 order by 4;
536
537 /* local variables */
538 l_prev_department_id number :=null;
539 l_prev_resource_id number :=null;
540 l_calendar_code varchar2(100) := null;
541 l_bom_uom_code varchar2(3) :=null;
542 l_time_uom_flag number :=null;
543 l_uom_code varchar2(3);
544 l_current_date date := null;
545 window_end_time date :=null;
546 window_start_time date :=null;
547
548 l_act_avl_hrs_required boolean := false;
549 l_dir_lab_hrs_required boolean := false;
550 l_sch_avl_hrs_required boolean := false;
551 l_earned_hrs_required boolean := false;
552
553 /* variables for preferences */
554 l_metric1_required boolean := false;
555 l_metric2_required boolean := false;
556 l_metric3_required boolean := false;
557 l_metric1_attribute1 number :=0;
558 l_metric1_attribute2 number :=0;
559 l_metric2_attribute1 number :=0;
560 l_metric2_attribute2 number :=0;
561 l_metric3_attribute1 number :=0;
562 l_metric3_attribute2 number :=0;
563
564 log_metric1 varchar2(30):= 'Disabled';
565 log_metric2 varchar2(30):= 'Disabled';
566 log_metric3 varchar2(30):= 'Disabled';
567 log_act_avl_hrs varchar2(30):= 'Not Required';
568 log_dir_lab_hrs varchar2(30):= 'Not Required';
569 log_sch_avl_hrs varchar2(30):= 'Not Required';
570 log_earned_hrs varchar2(30):= 'Not Required';
571
572 l_act_att_hrs number :=0;
573 l_dir_lab_hrs number :=0;
574 l_sch_avl_hrs number :=0;
575 l_earned_hrs number :=0;
576
577 g_user_id number;
578 g_user_login_id number;
579 g_program_appl_id number;
580 g_request_id number;
581 g_program_id number;
582 g_logLevel number;
583
584 l_date_seq number := null;
585 l_shift_num number := null;
586 l_shift_start_date date := null;
587 l_shift_end_date date := null;
588
589 l_stmt_num number := null;
590 l_proc_name varchar2(50) := 'WIP_WS_LABOR_METRIC_PUB.CALCULATE_METRICS';
591
592 l_org_code varchar2(3);
593 l_pgm_count number :=0;
594
595 emp_index varchar2(25) := null;
596 idx varchar2(25) := null;
597 new_index number;
598
599 x_error_msg varchar2(2000);
600 l_error_count number:=0;
601 l_conc_status boolean;
602
603 e_null_org_id exception;
604
605 is_aah_record_valid boolean;
606 is_sah_record_valid boolean;
607 is_dlh_record_valid boolean;
608 is_eh_record_valid boolean;
609 l_skip_records Number := 0;
610 l_working_emp_count number; --bug 14845207
611
612 begin
613
614 l_stmt_num :=10;
615 g_user_id := fnd_global.user_id;
616 g_user_login_id := fnd_global.login_id;
617 g_program_appl_id := fnd_global.prog_appl_id;
618 g_request_id := fnd_global.conc_request_id;
619 g_program_id := fnd_global.conc_program_id;
620
621 g_logLevel := FND_LOG.g_current_runtime_level;
622
623 if p_organization_id is null then
624 raise e_null_org_id;
625 end if;
626
627 select organization_code
628 into l_org_code
629 from mtl_parameters
630 where organization_id = p_organization_id;
631
632 wip_ws_util.trace_log('Launching Labor Metrics Calculation for Organization: '||l_org_code);
633
634 l_stmt_num :=20;
635
636 /* If the calculation program is running for this org already, then error out.
637 Bug 6891668. Modified Logic for Checking Concurrency */
638
639 select count(1)
640 into l_pgm_count
641 from fnd_concurrent_requests
642 where program_application_id = g_program_appl_id
643 and concurrent_program_id = g_program_id
644 and upper(phase_code) = 'R'
645 and argument1 = to_char(p_organization_id);
646
647 if (l_pgm_count > 1) then
648 fnd_message.set_name('WIP','WIP_RUNNING_LABOR_PRG');
649 fnd_message.set_token('ORG', to_char(l_org_code));
650 x_error_msg := fnd_message.get;
651 wip_ws_util.trace_log('Error in '||l_proc_name||'( stmt_num: '||l_stmt_num||') '||x_error_msg);
652 wip_ws_util.trace_log('Unable to run calculation program for this organization. Please try after some time.');
653 retcode := 2;
654 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',
655 'Errors encountered in calculation program, please check the log file.');
656 return;
657 end if;
658
659 /* Get window_end_time and window_start_time to determine the
660 Time window for metrics calculation. */
661
662 select sysdate
663 into l_current_date
664 from dual;
665
666 l_stmt_num :=30;
667
668 l_bom_uom_code := fnd_profile.value('BOM:HOUR_UOM_CODE');
669 l_calendar_code := get_calendar_code(p_organization_id);
670 window_end_time := get_period_end(l_calendar_code,l_current_date);
671 window_start_time := get_period_start(p_organization_id,window_end_time);
672
673 if (g_logLevel <= wip_constants.trace_logging) then
674 wip_ws_util.trace_log('Calendar: '||l_calendar_code);
675 wip_ws_util.trace_log('Start Time: '||to_char(window_start_time,WIP_CONSTANTS.DATETIME_FMT));
676 wip_ws_util.trace_log('End Time: '||to_char(window_end_time,WIP_CONSTANTS.DATETIME_FMT));
677 wip_ws_util.trace_log('Base Time UOM: '||l_bom_uom_code);
678 end if;
679
680 l_stmt_num :=40;
681 /* Check for the metrics preferences and its requirement: Start */
682
683 for preferences in get_preferences(p_organization_id) loop
684 if preferences.identifier = '1' then
685 if l_metric1_required then
686 if preferences.attribute_value_code ='1' then /* 1-AAH,2-SAH,3-DLH,4-EH */
687 l_act_avl_hrs_required := true;
688 log_act_avl_hrs := 'Required';
689 if preferences.attribute_name = 'M1Attribute1' then
690 l_metric1_attribute1 :=1;
691 else
692 l_metric1_attribute2 :=1;
693 end if;
694 elsif preferences.attribute_value_code ='2' then
695 l_sch_avl_hrs_required := true;
696 log_sch_avl_hrs :='Required';
697 if preferences.attribute_name = 'M1Attribute1' then
698 l_metric1_attribute1 :=2;
699 else
700 l_metric1_attribute2 :=2;
701 end if;
702 elsif preferences.attribute_value_code ='3' then
703 l_dir_lab_hrs_required := true;
704 log_dir_lab_hrs := 'Required';
705 if preferences.attribute_name = 'M1Attribute1' then
706 l_metric1_attribute1 :=3;
707 else
708 l_metric1_attribute2 :=3;
709 end if;
710 else
711 l_earned_hrs_required := true;
712 log_earned_hrs := 'Required';
713 if preferences.attribute_name = 'M1Attribute1' then
714 l_metric1_attribute1 :=4;
715 else
716 l_metric1_attribute2 :=4;
717 end if;
718 end if;
719 end if;
720 if preferences.attribute_name = 'M1Enabled' and preferences.attribute_value_code ='1' then
721 l_metric1_required := true;
722 log_metric1 := 'Enabled';
723 end if;
724 elsif preferences.identifier = '2' then
725 if l_metric2_required then
726 if preferences.attribute_value_code ='1' then /* 1-AAH,2-SAH,3-DLH,4-EH */
727 l_act_avl_hrs_required := true;
728 log_act_avl_hrs := 'Required';
729 if preferences.attribute_name = 'M2Attribute1' then
730 l_metric2_attribute1 :=1;
731 else
732 l_metric2_attribute2 :=1;
733 end if;
734 elsif preferences.attribute_value_code ='2' then
735 l_sch_avl_hrs_required := true;
736 log_sch_avl_hrs :='Required';
737 if preferences.attribute_name = 'M2Attribute1' then
738 l_metric2_attribute1 :=2;
739 else
740 l_metric2_attribute2 :=2;
741 end if;
742 elsif preferences.attribute_value_code ='3' then
743 l_dir_lab_hrs_required := true;
744 log_dir_lab_hrs := 'Required';
745 if preferences.attribute_name = 'M2Attribute1' then
746 l_metric2_attribute1 :=3;
747 else
748 l_metric2_attribute2 :=3;
749 end if;
750 else
751 l_earned_hrs_required := true;
752 log_earned_hrs := 'Required';
753 if preferences.attribute_name = 'M2Attribute1' then
754 l_metric2_attribute1 :=4;
755 else
756 l_metric2_attribute2 :=4;
757 end if;
758 end if;
759 end if;
760 if preferences.attribute_name = 'M2Enabled' and preferences.attribute_value_code ='1' then
761 l_metric2_required := true;
762 log_metric2 := 'Enabled';
763 end if;
764 elsif preferences.identifier = '3' then
765 if l_metric3_required then
766 if preferences.attribute_value_code ='1' then /* 1-AAH,2-SAH,3-DLH,4-EH */
767 l_act_avl_hrs_required := true;
768 log_act_avl_hrs := 'Required';
769 if preferences.attribute_name = 'M3Attribute1' then
770 l_metric3_attribute1 :=1;
771 else
772 l_metric3_attribute2 :=1;
773 end if;
774 elsif preferences.attribute_value_code ='2' then
775 l_sch_avl_hrs_required := true;
776 log_sch_avl_hrs :='Required';
777 if preferences.attribute_name = 'M3Attribute1' then
778 l_metric3_attribute1 :=2;
779 else
780 l_metric3_attribute2 :=2;
781 end if;
782 elsif preferences.attribute_value_code ='3' then
783 l_dir_lab_hrs_required := true;
784 log_dir_lab_hrs := 'Required';
785 if preferences.attribute_name = 'M3Attribute1' then
786 l_metric3_attribute1 :=3;
787 else
788 l_metric3_attribute2 :=3;
789 end if;
790 else
791 l_earned_hrs_required := true;
792 log_earned_hrs := 'Required';
793 if preferences.attribute_name = 'M3Attribute1' then
794 l_metric3_attribute1 :=4;
795 else
796 l_metric3_attribute2 :=4;
797 end if;
798 end if;
799 end if;
800 if preferences.attribute_name = 'M3Enabled' and preferences.attribute_value_code ='1' then
801 l_metric3_required := true;
802 log_metric3 := 'Enabled';
803 end if;
804 end if;
805 end loop;
806
807 l_stmt_num :=50;
808
809 if (g_logLevel <= wip_constants.trace_logging) then
810
811 wip_ws_util.trace_log('Metric 1: '||log_metric1);
812 wip_ws_util.trace_log('Metric 2: '||log_metric2);
813 wip_ws_util.trace_log('Metric 3: '||log_metric3);
814
815 wip_ws_util.trace_log('Actual Attendance Hours: '||log_act_avl_hrs);
816 wip_ws_util.trace_log('Scheduled Available Hours: '||log_sch_avl_hrs);
817 wip_ws_util.trace_log('Direct Labor Hours: '||log_dir_lab_hrs);
818 wip_ws_util.trace_log('Earned Hours: '||log_earned_hrs);
819
820 end if;
821
822 /* Check for the metrics preferences and its requirement: End */
823
824 if l_metric1_required or l_metric2_required or l_metric3_required then
825 /* Delete existing records in wip_labor_performance_rates table for the org.*/
826
827 delete from wip_labor_performance_rates
828 where organization_id= p_organization_id;
829
830 l_stmt_num :=60;
831
832 if (g_logLevel <= wip_constants.trace_logging) then
833 wip_ws_util.trace_log('Deleted Existing records for this Organization.');
834 end if;
835 /* calculate the metrics for every employee associated to any of
836 the department for this organization. */
837 new_index :=0;
838 for employee in employees(p_organization_id) Loop
839
840 if (g_logLevel <= wip_constants.trace_logging) then
841 wip_ws_util.trace_log('Calculation for Emp with Dep_Id: '||to_char(employee.department_id)
842 ||' Res_Id: '||to_char(employee.resource_id)
843 ||' Pers_Id: '||to_char(employee.person_id));
844 end if;
845
846 if ( l_prev_department_id is null or
847 l_prev_resource_id is null or
848 employee.department_id <> l_prev_department_id or
849 employee.resource_id <> l_prev_resource_id)
850 then
851 l_stmt_num :=70;
852 v_emp_perf_temp.delete;
853 for emp_shift in emp_shifts(l_calendar_code,
854 window_start_time,
855 window_end_time,
856 employee.department_id,
857 employee.resource_id)
858 loop
859 l_stmt_num :=80;
860 emp_index := to_char(emp_shift.shift_date_seq||':'||emp_shift.shift_num);
861 v_emp_perf_temp(emp_index).actual_date_seq :=emp_shift.shift_date_seq;
862 v_emp_perf_temp(emp_index).shift_num :=emp_shift.shift_num;
863 v_emp_perf_temp(emp_index).shift_start :=emp_shift.shift_start_time;
864 v_emp_perf_temp(emp_index).shift_end :=emp_shift.shift_end_time;
865 v_emp_perf_temp(emp_index).shift_duration :=emp_shift.shift_duration;
866 v_emp_perf_temp(emp_index).act_att_hrs :=0;
867 v_emp_perf_temp(emp_index).dir_lab_hrs :=0;
868 v_emp_perf_temp(emp_index).sch_avl_hrs :=0;
869 v_emp_perf_temp(emp_index).earned_hrs :=0;
870 if (g_logLevel <= wip_constants.trace_logging) then
871 wip_ws_util.trace_log('Employee Shift Info: '||emp_index);
872 end if;
873 end loop;
874 if (g_logLevel <= wip_constants.trace_logging) then
875 wip_ws_util.trace_log('Re-Creating Shift Information for Employee');
876 end if;
877 else
878 l_stmt_num :=90;
879 emp_index := v_emp_perf_temp.first;
880 while emp_index <= v_emp_perf_temp.last loop
881 v_emp_perf_temp(emp_index).act_att_hrs :=0;
882 v_emp_perf_temp(emp_index).dir_lab_hrs :=0;
883 v_emp_perf_temp(emp_index).sch_avl_hrs :=0;
884 v_emp_perf_temp(emp_index).earned_hrs :=0;
885 emp_index := v_emp_perf_temp.next(emp_index);
886 end loop;
887
888 if (g_logLevel <= wip_constants.trace_logging) then
889 wip_ws_util.trace_log('Re-Using Shift Information for Employee');
890 end if;
891 end if;
892
893 if l_act_avl_hrs_required then /* Calculate actual available hours here. */
894 l_stmt_num :=100;
895 if (g_logLevel <= wip_constants.trace_logging) then
896 wip_ws_util.trace_log('Inside Actual Available Hours Calculation');
897 end if;
898 for emp_act_avl_hrs in aah_records(p_organization_id,
899 employee.person_id,
900 window_start_time,
901 window_end_time)
902 loop
903 l_stmt_num :=110;
904 if (g_logLevel <= wip_constants.trace_logging) then
905 wip_ws_util.trace_log('Record found for Actual Available Hours Calculation');
906 end if;
907 is_aah_record_valid := true;
908 wip_ws_dl_util.get_first_dept_resource_shift
909 (p_cal_code =>l_calendar_code,
910 p_dept_id =>employee.department_id,
911 p_resource_id =>employee.resource_id,
912 p_date =>emp_act_avl_hrs.actual_date,
913 x_shift_seq =>l_date_seq,
914 x_shift_num =>l_shift_num,
915 x_shift_start_date =>l_shift_start_date,
916 x_shift_end_date =>l_shift_end_date);
917
918 /* UOM validations and conversions */
919 l_time_uom_flag := is_time_uom(emp_act_avl_hrs.uom_code);
920 l_stmt_num :=120;
921 -- Modified for bug 6972129.
922 if l_time_uom_flag = 2 then
923 if (g_logLevel <= wip_constants.trace_logging) then
924 fnd_message.set_name('WIP','WIP_LM_TIME_UOM');
925 fnd_message.set_token('EMP', to_char(employee.person_id));
926 x_error_msg := fnd_message.get;
927 wip_ws_util.trace_log(l_proc_name||'( stmt_num: '||l_stmt_num||') '||x_error_msg);
928 --l_error_count := l_error_count + 1;
929 end if;
930 is_aah_record_valid := false;
931 end if;
932 if is_aah_record_valid then
933 if emp_act_avl_hrs.duration is not null and emp_act_avl_hrs.uom_code <> l_bom_uom_code then
934 l_stmt_num :=130;
935 l_act_att_hrs := inv_convert.inv_um_convert(item_id => -1,
936 precision => 38,
937 from_quantity => emp_act_avl_hrs.duration,
938 from_unit => emp_act_avl_hrs.uom_code,
939 to_unit => l_bom_uom_code,
940 from_name => null,
941 to_name => null);
942 else
943 l_act_att_hrs := emp_act_avl_hrs.duration;
944 end if;
945 l_stmt_num :=140;
946 -- Modified for Bug 7150089. Added this condition to avoid calculation if a shift
947 -- exists across multiple days and record is present for first day in that shift.
948 if v_emp_perf_temp.exists(to_char(l_date_seq||':'||l_shift_num)) then
949 v_emp_perf_temp(to_char(l_date_seq||':'||l_shift_num)).act_att_hrs :=
950 v_emp_perf_temp(to_char(l_date_seq||':'||l_shift_num)).act_att_hrs+l_act_att_hrs;
951
952 if (g_logLevel <= wip_constants.trace_logging) then
953 wip_ws_util.trace_log('Dep: '||to_char(employee.department_id)||
954 ' Res: '||to_char(employee.resource_id)||
955 ' Pers: '||to_char(employee.person_id)||
956 ' Date: '||to_char(emp_act_avl_hrs.actual_date,WIP_CONSTANTS.DATETIME_FMT)||
957 ' Shift Seq: '||to_char(l_date_seq)||
958 ' Shift Num: '||to_char(l_shift_num)||
959 ' AAH Value: '||to_char(l_act_att_hrs));
960 end if;
961 end if;
962 end if;
963 end loop;
964 end if; /* if l_act_avl_hrs_required then */
965
966 if l_sch_avl_hrs_required then /* Calculate scheduled available hours here. */
967 l_stmt_num :=150;
968 if (g_logLevel <= wip_constants.trace_logging) then
969 wip_ws_util.trace_log('Inside Scheduled Available Hours Calculation');
970 end if;
971 /* Add Code for capacity changes and exceptions and factor it on the shift duration. */
972 emp_index := v_emp_perf_temp.first;
973 while emp_index <= v_emp_perf_temp.last loop
974 v_emp_perf_temp(emp_index).sch_avl_hrs := v_emp_perf_temp(emp_index).shift_duration;
975 emp_index := v_emp_perf_temp.next(emp_index);
976 end loop;
977 l_stmt_num :=160;
978 for emp_sch_avl_hrs in sah_records(p_organization_id,
979 employee.person_id,
980 window_start_time,
981 window_end_time)
982 loop
983 l_stmt_num :=170;
984 if (g_logLevel <= wip_constants.trace_logging) then
985 wip_ws_util.trace_log('Record found for Scheduled Available Hours Calculation');
986 end if;
987 is_sah_record_valid :=true;
988 wip_ws_dl_util.get_first_dept_resource_shift
989 (p_cal_code =>l_calendar_code,
990 p_dept_id =>employee.department_id,
991 p_resource_id =>employee.resource_id,
992 p_date =>emp_sch_avl_hrs.actual_date,
993 x_shift_seq =>l_date_seq,
994 x_shift_num =>l_shift_num,
995 x_shift_start_date =>l_shift_start_date,
996 x_shift_end_date =>l_shift_end_date);
997
998 /* UOM validations and conversions */
999 l_time_uom_flag := is_time_uom(emp_sch_avl_hrs.uom_code);
1000 l_stmt_num :=180;
1001 -- Modified for bug 6972129.
1002 if l_time_uom_flag = 2 then
1003 if (g_logLevel <= wip_constants.trace_logging) then
1004 fnd_message.set_name('WIP','WIP_LM_TIME_UOM');
1005 fnd_message.set_token('EMP', to_char(employee.person_id));
1006 x_error_msg := fnd_message.get;
1007 wip_ws_util.trace_log(l_proc_name||'( stmt_num: '||l_stmt_num||') '||x_error_msg);
1008 --l_error_count := l_error_count + 1;
1009 end if;
1010 is_sah_record_valid :=false;
1011 end if;
1012 if is_sah_record_valid then
1013 if emp_sch_avl_hrs.duration is not null and emp_sch_avl_hrs.uom_code <> l_bom_uom_code then
1014 l_stmt_num :=190;
1015 l_sch_avl_hrs := inv_convert.inv_um_convert(item_id => -1,
1016 precision => 38,
1017 from_quantity => emp_sch_avl_hrs.duration,
1018 from_unit => emp_sch_avl_hrs.uom_code,
1019 to_unit => l_bom_uom_code,
1020 from_name => null,
1021 to_name => null);
1022 else
1023 l_sch_avl_hrs := emp_sch_avl_hrs.duration;
1024 end if;
1025 l_stmt_num :=200;
1026 -- Modified for Bug 7150089. Added this condition to avoid calculation if a shift
1027 -- exists across multiple days and record is present for first day in that shift.
1028 if v_emp_perf_temp.exists(to_char(l_date_seq||':'||l_shift_num)) then
1029 v_emp_perf_temp(to_char(l_date_seq||':'||l_shift_num)).sch_avl_hrs :=
1030 v_emp_perf_temp(to_char(l_date_seq||':'||l_shift_num)).sch_avl_hrs+l_sch_avl_hrs;
1031
1032 if (g_logLevel <= wip_constants.trace_logging) then
1033 wip_ws_util.trace_log('Dep: '||to_char(employee.department_id)||
1034 ' Res: '||to_char(employee.resource_id)||
1035 ' Pers: '||to_char(employee.person_id)||
1036 ' Date: '||to_char(emp_sch_avl_hrs.actual_date,WIP_CONSTANTS.DATETIME_FMT)||
1037 ' Shift Seq: '||to_char(l_date_seq)||
1038 ' Shift Num: '||to_char(l_shift_num)||
1039 ' SAH Value: '||to_char(l_sch_avl_hrs));
1040 end if;
1041 end if;
1042 end if;
1043 end loop;
1044 end if; /* if l_sch_avl_hrs_required then */
1045
1046 if l_dir_lab_hrs_required then /* Calculate direct labor hours here. */
1047 l_stmt_num :=210;
1048 if (g_logLevel <= wip_constants.trace_logging) then
1049 wip_ws_util.trace_log('Inside Direct Labor Hours Calculation');
1050 end if;
1051 for emp_dir_lab_hrs in dlh_records(p_organization_id,
1052 employee.department_id,
1053 employee.resource_id,
1054 employee.instance_id,
1055 employee.person_id,
1056 window_start_time,
1057 window_end_time)
1058 loop
1059 l_stmt_num :=220;
1060 if (g_logLevel <= wip_constants.trace_logging) then
1061 wip_ws_util.trace_log('Record found for Direct Labor Hours Calculation');
1062 end if;
1063 is_dlh_record_valid :=true;
1064 wip_ws_dl_util.get_first_dept_resource_shift
1065 (p_cal_code =>l_calendar_code,
1066 p_dept_id =>employee.department_id,
1067 p_resource_id =>employee.resource_id,
1068 p_date =>emp_dir_lab_hrs.actual_date,
1069 x_shift_seq =>l_date_seq,
1070 x_shift_num =>l_shift_num,
1071 x_shift_start_date =>l_shift_start_date,
1072 x_shift_end_date =>l_shift_end_date);
1073
1074 /* UOM validations and conversions */
1075 l_time_uom_flag := is_time_uom(emp_dir_lab_hrs.uom_code);
1076 l_stmt_num :=230;
1077 -- Modified for bug 6972129.
1078 if l_time_uom_flag = 2 then
1079 if (g_logLevel <= wip_constants.trace_logging) then
1080 fnd_message.set_name('WIP','WIP_LM_TIME_UOM');
1081 fnd_message.set_token('EMP', to_char(employee.person_id));
1082 x_error_msg := fnd_message.get;
1083 wip_ws_util.trace_log(l_proc_name||'( stmt_num: '||l_stmt_num||') '||x_error_msg);
1084 --l_error_count := l_error_count + 1;
1085 end if;
1086 is_dlh_record_valid :=false;
1087 end if;
1088 if is_dlh_record_valid then
1089 if emp_dir_lab_hrs.duration is not null and emp_dir_lab_hrs.uom_code <> l_bom_uom_code then
1090 l_stmt_num :=240;
1091 l_dir_lab_hrs := inv_convert.inv_um_convert(item_id => -1,
1092 precision => 38,
1093 from_quantity => emp_dir_lab_hrs.duration,
1094 from_unit => emp_dir_lab_hrs.uom_code,
1095 to_unit => l_bom_uom_code,
1096 from_name => null,
1097 to_name => null);
1098 else
1099 l_dir_lab_hrs := emp_dir_lab_hrs.duration;
1100 end if;
1101 l_stmt_num :=250;
1102 idx := to_char(l_date_seq||':'||l_shift_num);
1103 l_stmt_num :=260;
1104 -- Modified for Bug 7150089. Added this condition to avoid calculation if a shift
1105 -- exists across multiple days and record is present for first day in that shift.
1106 if v_emp_perf_temp.exists(to_char(l_date_seq||':'||l_shift_num)) then
1107 v_emp_perf_temp(to_char(l_date_seq||':'||l_shift_num)).dir_lab_hrs :=
1108 v_emp_perf_temp(to_char(l_date_seq||':'||l_shift_num)).dir_lab_hrs+l_dir_lab_hrs;
1109
1110 if (g_logLevel <= wip_constants.trace_logging) then
1111 wip_ws_util.trace_log('Dep: '||to_char(employee.department_id)||
1112 ' Res: '||to_char(employee.resource_id)||
1113 ' Inst : '||to_char(employee.instance_id)||
1114 ' Pers: '||to_char(employee.person_id)||
1115 ' Date: '||to_char(emp_dir_lab_hrs.actual_date,WIP_CONSTANTS.DATETIME_FMT)||
1116 ' Shift Seq: '||to_char(l_date_seq)||
1117 ' Shift Num: '||to_char(l_shift_num)||
1118 ' DLH Value: '||to_char(l_dir_lab_hrs));
1119 end if;
1120 end if;
1121 end if;
1122 l_stmt_num :=270;
1123 end loop;
1124 end if; /* l_dir_lab_hrs_required */
1125 l_stmt_num :=280;
1126 if l_earned_hrs_required then /* Calculate earned hours here. */
1127 l_stmt_num :=290;
1128 /* Modified for Bug 7010115. */
1129 -- Fix for Bug 12614160. Consider cases where resource doesn't exist on the job operation.
1130 l_skip_records := 0;
1131 if (g_logLevel <= wip_constants.trace_logging) then
1132 wip_ws_util.trace_log(' Get Clock-In records in for Earned Hours - Instance Id : '||employee.instance_id || ' Person Id : ' || employee.person_id || ' Resource Id : ' || employee.resource_id);
1133 end if;
1134 for emp_clock_rec in clock_in_records(p_organization_id,
1135 employee.department_id,
1136 employee.resource_id,
1137 employee.instance_id,
1138 employee.person_id,
1139 window_start_time,
1140 window_end_time)
1141 loop
1142 if (g_logLevel <= wip_constants.trace_logging) then
1143 wip_ws_util.trace_log(' Clock-In Details Job : '|| emp_clock_rec.wip_entity_id || ' Op : '||emp_clock_rec.operation_seq_num || ' Index : ' ||emp_clock_rec.emp_index);
1144 end if;
1145 for emp_earned_hrs in eh_records(p_organization_id,
1146 window_start_time,
1147 window_end_time,
1148 emp_clock_rec.wip_entity_id,
1149 emp_clock_rec.operation_seq_num,
1150 employee.resource_id,
1151 emp_clock_rec.emp_index,
1152 employee.department_id)
1153 loop
1154 if (g_logLevel <= wip_constants.trace_logging) then
1155 wip_ws_util.trace_log(' Earned Hours Move Qty : '|| emp_earned_hrs.moved_qty );
1156 end if;
1157 for earned_hrs_rates in ear_hours_calc(p_organization_id,
1158 emp_clock_rec.wip_entity_id,
1159 emp_clock_rec.operation_seq_num,
1160 employee.resource_id,
1161 emp_earned_hrs.moved_qty)
1162 loop
1163 if (g_logLevel <= wip_constants.trace_logging) then
1164 wip_ws_util.trace_log(' inside ear_hours_calc qty : '|| emp_earned_hrs.moved_qty );
1165 end if;
1166 is_eh_record_valid := true;
1167 --Start of bug 14845207 fix
1168 begin
1169 -- if employee didn't clock in/out in the shift then don't consider it when doing earn hour calculation
1170 select count(distinct wrat.employee_id)
1171 into l_working_emp_count
1172 from wip_resource_actual_times wrat
1173 where wrat.wip_entity_id = emp_clock_rec.wip_entity_id
1174 and wrat.operation_seq_num = emp_clock_rec.operation_seq_num
1175 and wrat.organization_id = p_organization_id
1176 and get_index_for_date(wrat.start_date,wrat.organization_id,employee.department_id,wrat.resource_id) = emp_clock_rec.emp_index
1177 and duration is not null;
1178 -- set the l_working_emp_count = 1 if it was 0, because
1179 -- l_working_emp_count will be used for division
1180 if (l_working_emp_count = 0) then
1181 l_working_emp_count := 1;
1182 end if;
1183 exception
1184 when others then
1185 l_working_emp_count := 1;
1186 end;
1187 if (g_logLevel <= wip_constants.trace_logging) then
1188 wip_ws_util.trace_log('Labor metric calculation, l_working_emp_count :'|| l_working_emp_count);
1189 end if;
1190
1191 if earned_hrs_rates.resource_pick = 1 then
1192 l_skip_records := 1;
1193 end if;
1194 if earned_hrs_rates.resource_pick = 2 and l_skip_records = 1 then
1195 is_eh_record_valid := false;
1196 end if;
1197 l_earned_hrs := earned_hrs_rates.usage_rate/l_working_emp_count;
1198 l_uom_code := earned_hrs_rates.uom_code ;
1199 l_time_uom_flag := is_time_uom(l_uom_code);
1200 l_stmt_num :=310;
1201 -- Modified for bug 6972129.
1202 if l_time_uom_flag = 2 then
1203 if (g_logLevel <= wip_constants.trace_logging) then
1204 fnd_message.set_name('WIP','WIP_LM_TIME_UOM');
1205 fnd_message.set_token('EMP', to_char(employee.person_id));
1206 x_error_msg := fnd_message.get;
1207 wip_ws_util.trace_log(l_proc_name||'( stmt_num: '||l_stmt_num||') '||x_error_msg);
1208 --l_error_count := l_error_count + 1;
1209 end if;
1210 is_eh_record_valid := false;
1211 end if;
1212 if earned_hrs_rates.basis_type = 2 then
1213 if (g_logLevel <= wip_constants.trace_logging) then
1214 fnd_message.set_name('WIP','WIP_LM_LOT_BASED_RES');
1215 fnd_message.set_token('EMP', to_char(employee.person_id));
1216 x_error_msg := fnd_message.get;
1217 wip_ws_util.trace_log(l_proc_name||'( stmt_num: '||l_stmt_num||') '||x_error_msg);
1218 --l_error_count := l_error_count + 1;
1219 end if;
1220 is_eh_record_valid := false;
1221 end if;
1222 if is_eh_record_valid then
1223 if l_earned_hrs is not null and l_uom_code <> l_bom_uom_code then
1224 l_stmt_num :=320;
1225 l_earned_hrs := inv_convert.inv_um_convert(item_id => -1,
1226 precision => 38,
1227 from_quantity => l_earned_hrs,
1228 from_unit => l_uom_code,
1229 to_unit => l_bom_uom_code,
1230 from_name => null,
1231 to_name => null);
1232 end if;
1233 l_stmt_num :=330;
1234 -- Modified for Bug 7150089. Added this condition to avoid calculation if a shift
1235 -- exists across multiple days and record is present for first day in that shift.
1236 if v_emp_perf_temp.exists(emp_clock_rec.emp_index) then
1237 v_emp_perf_temp(emp_clock_rec.emp_index).earned_hrs :=
1238 v_emp_perf_temp(emp_clock_rec.emp_index).earned_hrs+l_earned_hrs;
1239
1240 if (g_logLevel <= wip_constants.trace_logging) then
1241 wip_ws_util.trace_log('Dep: '||to_char(employee.department_id)||
1242 ' Res: '||to_char(employee.resource_id)||
1243 ' Inst: '||to_char(employee.instance_id)||
1244 ' Pers: '||to_char(employee.person_id)||
1245 ' Index: '||to_char(emp_clock_rec.emp_index)||
1246 ' Move Qty: '||to_char(emp_earned_hrs.moved_qty)||
1247 ' EH Value: '||to_char(l_earned_hrs));
1248 end if;
1249 end if;
1250 end if;
1251 l_stmt_num :=340;
1252 end loop;
1253 end loop;
1254 l_skip_records := 0;
1255 end loop;
1256 end if; /* if l_earned_hrs_required then */
1257 l_stmt_num :=350;
1258
1259 if (g_logLevel <= wip_constants.trace_logging) then
1260 wip_ws_util.trace_log('Performing data transfer');
1261 end if;
1262
1263 /* transfer data from collection indexed by varchar2 to a collection
1264 indexed by binary_integer so that we can bulk insert in the table.
1265 Also initialize the new collection always. */
1266 emp_index := v_emp_perf_temp.first;
1267 while emp_index <= v_emp_perf_temp.last loop
1268 if emp_index <> ':' or emp_index is not null then
1269 new_index := new_index+1;
1270 v_wip_lab_perf_rate(new_index).Organization_id := p_organization_id;
1271 v_wip_lab_perf_rate(new_index).Department_id := employee.department_id;
1272 v_wip_lab_perf_rate(new_index).Resource_id := employee.resource_id;
1273 v_wip_lab_perf_rate(new_index).Employee_id := employee.person_id;
1274 v_wip_lab_perf_rate(new_index).date_sequence := v_emp_perf_temp(emp_index).actual_date_seq;
1275 v_wip_lab_perf_rate(new_index).Shift_num := v_emp_perf_temp(emp_index).shift_num;
1276 case l_metric1_attribute1 when 1 then v_wip_lab_perf_rate(new_index).Metric1_attribute1 := v_emp_perf_temp(emp_index).act_att_hrs;
1277 when 2 then v_wip_lab_perf_rate(new_index).Metric1_attribute1 := v_emp_perf_temp(emp_index).sch_avl_hrs;
1278 when 3 then v_wip_lab_perf_rate(new_index).Metric1_attribute1 := v_emp_perf_temp(emp_index).dir_lab_hrs;
1279 when 4 then v_wip_lab_perf_rate(new_index).Metric1_attribute1 := v_emp_perf_temp(emp_index).earned_hrs;
1280 else v_wip_lab_perf_rate(new_index).Metric1_attribute1 :=0;
1281 end case;
1282 case l_metric1_attribute2 when 1 then v_wip_lab_perf_rate(new_index).Metric1_attribute2 := v_emp_perf_temp(emp_index).act_att_hrs;
1283 when 2 then v_wip_lab_perf_rate(new_index).Metric1_attribute2 := v_emp_perf_temp(emp_index).sch_avl_hrs;
1284 when 3 then v_wip_lab_perf_rate(new_index).Metric1_attribute2 := v_emp_perf_temp(emp_index).dir_lab_hrs;
1285 when 4 then v_wip_lab_perf_rate(new_index).Metric1_attribute2 := v_emp_perf_temp(emp_index).earned_hrs;
1286 else v_wip_lab_perf_rate(new_index).Metric1_attribute2 :=0;
1287 end case;
1288 case l_metric2_attribute1 when 1 then v_wip_lab_perf_rate(new_index).Metric2_attribute1 := v_emp_perf_temp(emp_index).act_att_hrs;
1289 when 2 then v_wip_lab_perf_rate(new_index).Metric2_attribute1 := v_emp_perf_temp(emp_index).sch_avl_hrs;
1290 when 3 then v_wip_lab_perf_rate(new_index).Metric2_attribute1 := v_emp_perf_temp(emp_index).dir_lab_hrs;
1291 when 4 then v_wip_lab_perf_rate(new_index).Metric2_attribute1 := v_emp_perf_temp(emp_index).earned_hrs;
1292 else v_wip_lab_perf_rate(new_index).Metric2_attribute1 :=0;
1293 end case;
1294 case l_metric2_attribute2 when 1 then v_wip_lab_perf_rate(new_index).Metric2_attribute2 := v_emp_perf_temp(emp_index).act_att_hrs;
1295 when 2 then v_wip_lab_perf_rate(new_index).Metric2_attribute2 := v_emp_perf_temp(emp_index).sch_avl_hrs;
1296 when 3 then v_wip_lab_perf_rate(new_index).Metric2_attribute2 := v_emp_perf_temp(emp_index).dir_lab_hrs;
1297 when 4 then v_wip_lab_perf_rate(new_index).Metric2_attribute2 := v_emp_perf_temp(emp_index).earned_hrs;
1298 else v_wip_lab_perf_rate(new_index).Metric2_attribute2 :=0;
1299 end case;
1300 case l_metric3_attribute1 when 1 then v_wip_lab_perf_rate(new_index).Metric3_attribute1 := v_emp_perf_temp(emp_index).act_att_hrs;
1301 when 2 then v_wip_lab_perf_rate(new_index).Metric3_attribute1 := v_emp_perf_temp(emp_index).sch_avl_hrs;
1302 when 3 then v_wip_lab_perf_rate(new_index).Metric3_attribute1 := v_emp_perf_temp(emp_index).dir_lab_hrs;
1303 when 4 then v_wip_lab_perf_rate(new_index).Metric3_attribute1 := v_emp_perf_temp(emp_index).earned_hrs;
1304 else v_wip_lab_perf_rate(new_index).Metric3_attribute1 :=0;
1305 end case;
1306 case l_metric3_attribute2 when 1 then v_wip_lab_perf_rate(new_index).Metric3_attribute2 := v_emp_perf_temp(emp_index).act_att_hrs;
1307 when 2 then v_wip_lab_perf_rate(new_index).Metric3_attribute2 := v_emp_perf_temp(emp_index).sch_avl_hrs;
1308 when 3 then v_wip_lab_perf_rate(new_index).Metric3_attribute2 := v_emp_perf_temp(emp_index).dir_lab_hrs;
1309 when 4 then v_wip_lab_perf_rate(new_index).Metric3_attribute2 := v_emp_perf_temp(emp_index).earned_hrs;
1310 else v_wip_lab_perf_rate(new_index).Metric3_attribute2 :=0;
1311 end case;
1312 v_wip_lab_perf_rate(new_index).Created_by := g_user_id;
1313 v_wip_lab_perf_rate(new_index).Creation_date := sysdate;
1314 v_wip_lab_perf_rate(new_index).Last_updated_by := g_user_id;
1315 v_wip_lab_perf_rate(new_index).Last_update_date := sysdate;
1316 v_wip_lab_perf_rate(new_index).Last_update_login := g_user_login_id;
1317 v_wip_lab_perf_rate(new_index).Object_version_number := 1;
1318 v_wip_lab_perf_rate(new_index).Request_id := g_request_id;
1319 v_wip_lab_perf_rate(new_index).Program_id := g_program_id;
1320 v_wip_lab_perf_rate(new_index).Program_application_id := g_program_appl_id;
1321 v_wip_lab_perf_rate(new_index).Program_update_date := sysdate;
1322 end if;
1323 emp_index := v_emp_perf_temp.next(emp_index);
1324 end loop;
1325 l_stmt_num :=360;
1326
1327 if (g_logLevel <= wip_constants.trace_logging) then
1328 wip_ws_util.trace_log(' Calculation Completed for employee Dep: '||to_char(employee.department_id)||
1329 ' Res: '||to_char(employee.resource_id)||
1330 ' Pers: '||to_char(employee.person_id));
1331 end if;
1332 l_stmt_num :=370;
1333
1334 l_prev_department_id := employee.department_id ;
1335 l_prev_resource_id := employee.resource_id ;
1336
1337 end loop; /* for employee in employees(p_organization_id) */
1338
1339 l_stmt_num :=380;
1340
1341 /* Bulk Insert Records into Metrics Table */
1342 forall cntr in v_wip_lab_perf_rate.first..v_wip_lab_perf_rate.last
1343 INSERT into WIP_LABOR_PERFORMANCE_RATES values v_wip_lab_perf_rate(cntr);
1344
1345 l_stmt_num :=390;
1346 if (g_logLevel <= wip_constants.trace_logging) then
1347 wip_ws_util.trace_log('Insertion of all calculated records is successful.');
1348 end if;
1349
1350 /* Since attendance hours is entered for an employee at org level and distributed across all departments
1351 if the employee is associated to multiple departments. So org level attendance hours will be over shooted
1352 when summing the value in the dashboard. So we compute the org level summary and insert into metrics table.*/
1353 select t.date_sequence,
1354 t.shift_num,
1355 sum(t.m1_att1),
1356 sum(t.m1_att2),
1357 sum(t.m2_att1),
1358 sum(t.m2_att2),
1359 sum(t.m3_att1),
1360 sum(t.m3_att2)
1361 bulk collect into
1362 v_date_sequence,
1363 v_shift_num,
1364 v_m1_att1,
1365 v_m1_att2,
1366 v_m2_att1,
1367 v_m2_att2,
1368 v_m3_att1,
1369 v_m3_att2
1370 from (
1371 select date_sequence,
1372 shift_num,
1373 decode(l_metric1_attribute1,1,max(metric1_attribute1),sum(metric1_attribute1)) m1_att1,
1374 decode(l_metric1_attribute2,1,max(metric1_attribute2),sum(metric1_attribute2)) m1_att2,
1375 decode(l_metric2_attribute1,1,max(metric2_attribute1),sum(metric2_attribute1)) m2_att1,
1376 decode(l_metric2_attribute2,1,max(metric2_attribute2),sum(metric2_attribute2)) m2_att2,
1377 decode(l_metric3_attribute1,1,max(metric3_attribute1),sum(metric3_attribute1)) m3_att1,
1378 decode(l_metric3_attribute2,1,max(metric3_attribute2),sum(metric3_attribute2)) m3_att2
1379 from wip_labor_performance_rates
1380 where organization_id = p_organization_id
1381 group by
1382 date_sequence,
1383 shift_num,
1384 employee_id
1385 )t
1386 group by
1387 t.date_sequence,
1388 t.shift_num;
1389
1390 l_stmt_num :=400;
1391 if (g_logLevel <= wip_constants.trace_logging) then
1392 wip_ws_util.trace_log('Computed Org Level Summary Data.');
1393 end if;
1394
1395 /* Insert into Metrics Table the org level summary data. Use -1 for dept,res and employee */
1396 forall org_index in v_shift_num.first..v_shift_num.last
1397 insert into wip_labor_performance_rates
1398 (
1399 ORGANIZATION_ID,
1400 DEPARTMENT_ID,
1401 RESOURCE_ID,
1402 EMPLOYEE_ID,
1403 DATE_SEQUENCE,
1404 SHIFT_NUM,
1405 METRIC1_ATTRIBUTE1,
1406 METRIC1_ATTRIBUTE2,
1407 METRIC2_ATTRIBUTE1,
1408 METRIC2_ATTRIBUTE2,
1409 METRIC3_ATTRIBUTE1,
1410 METRIC3_ATTRIBUTE2,
1411 CREATED_BY,
1412 CREATION_DATE,
1413 LAST_UPDATED_BY,
1414 LAST_UPDATE_DATE,
1415 LAST_UPDATE_LOGIN,
1416 PROGRAM_APPLICATION_ID,
1417 PROGRAM_UPDATE_DATE,
1418 PROGRAM_ID,
1419 REQUEST_ID,
1420 OBJECT_VERSION_NUMBER
1421 )
1422 values
1423 (
1424 p_organization_id,
1425 -1,
1426 -1,
1427 -1,
1428 v_date_sequence(org_index),
1429 v_shift_num(org_index),
1430 v_m1_att1(org_index),
1431 v_m1_att2(org_index),
1432 v_m2_att1(org_index),
1433 v_m2_att2(org_index),
1434 v_m3_att1(org_index),
1435 v_m3_att2(org_index),
1436 g_user_id,
1437 sysdate,
1438 g_user_id,
1439 sysdate,
1440 g_user_login_id,
1441 g_program_appl_id,
1442 sysdate,
1443 g_program_id,
1444 g_request_id,
1445 1);
1446
1447 l_stmt_num :=400;
1448 if (g_logLevel <= wip_constants.trace_logging) then
1449 wip_ws_util.trace_log('Insertion of Org Level Summary Data is successful.');
1450 end if;
1451
1452 end if; /* l_metric1_required or l_metric2_required or l_metric3_required */
1453
1454 l_stmt_num :=410;
1455 wip_ws_util.trace_log('Labor Metrics Calculation completed.');
1456
1457 commit;
1458
1459 if l_error_count=0 then
1460 l_conc_status := true;
1461 retcode:=0;
1462 else
1463 retcode := 1;
1464 errbuf := 'Calculation program encountered invalid records. Invalid records will be ignored during calculation.';
1465 fnd_file.put_line(fnd_file.log,errbuf);
1466 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',errbuf);
1467 end if;
1468
1469 exception
1470 when e_null_org_id then
1471 retcode := 2;
1472 errbuf := 'Organization parameter cannot be null';
1473 fnd_file.put_line(fnd_file.log,errbuf);
1474 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',errbuf);
1475 when others then
1476 fnd_message.set_name('WIP','WIP_LAB_UNEXPECTED_ERROR');
1477 x_error_msg := fnd_message.get;
1478 wip_ws_util.trace_log('Error in '||l_proc_name||'( stmt_num: '||l_stmt_num||') '||x_error_msg);
1479 x_error_msg := sqlerrm(sqlcode);
1480 wip_ws_util.trace_log('Error in '||l_proc_name||'( stmt_num: '||l_stmt_num||') '||x_error_msg);
1481 rollback;
1482 retcode := 2;
1483 errbuf := 'Errors encountered in calculation program, please check the log file.';
1484 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',errbuf);
1485
1486 end calculate_metrics;
1487
1488 /* To obtain Client Date for a given Server Date (Used in Charts only) */
1489 function get_client_date(p_date in Date)
1490 return date
1491 is
1492 l_converted_date date;
1493 l_timezone_enabled boolean := ( fnd_profile.value('ENABLE_TIMEZONE_CONVERSIONS') = 'Y' AND
1494 fnd_profile.value('CLIENT_TIMEZONE_ID') IS NOT NULL AND
1495 fnd_profile.value('SERVER_TIMEZONE_ID') IS NOT NULL AND
1496 fnd_profile.value('CLIENT_TIMEZONE_ID') <>
1497 fnd_profile.value('SERVER_TIMEZONE_ID'));
1498 l_client_id number := fnd_profile.value('CLIENT_TIMEZONE_ID');
1499 l_server_id number := fnd_profile.value('SERVER_TIMEZONE_ID');
1500 begin
1501
1502 if l_timezone_enabled and p_date is not null then
1503 l_converted_date := hz_timezone_pub.convert_datetime(l_server_id,
1504 l_client_id,
1505 p_date);
1506 else
1507 l_converted_date := p_date;
1508 end if;
1509
1510 return l_converted_date;
1511 end get_client_date;
1512
1513 END WIP_WS_LABOR_METRIC_PUB;
[Home] [Help]