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