1 PACKAGE BODY WMS_LMS_LABOR_PRODUCTIVITY AS
2 /* $Header: WMSLMLPB.pls 120.5 2006/06/06 09:04:56 viberry noship $ */
3
4 g_version_printed BOOLEAN := FALSE;
5 g_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
6
7
8
9 PROCEDURE DEBUG(p_message IN VARCHAR2,
10 p_module IN VARCHAR2 default 'abc',
11 p_level IN VARCHAR2 DEFAULT 9) IS
12 BEGIN
13
14 IF NOT g_version_printed THEN
15 INV_TRX_UTIL_PUB.TRACE('$Header: WMSLMLPB.pls 120.5 2006/06/06 09:04:56 viberry noship $',g_pkg_name, 9);
16 g_version_printed := TRUE;
17 END IF;
18
19 INV_TRX_UTIL_PUB.TRACE( P_MESG =>P_MESSAGE
20 ,P_MOD => p_module
21 ,p_level => p_level
22 );
23 END DEBUG;
24
25
26
27
28 -- This procedure will match all the transaction records in wms_els_trx_src
29 -- table with the setup rows in wms_els_individual_tasks_b and wms_els_grouped_tasks_b
30 --It will DO the following
31
32 -- 1) do the matching of transaction data with els data. Start with the setup row
33 -- with least sequnce.Update the els_data_id column of WMS_ELS_TRX_SRC table
34 -- with the eld_data_id of the setup line with which the matching was found
35 -- update the zone and item category columns with zone_id's and item_category_id of the
36 -- els data row with which the match was found.
37 -- 2). Update the travel and the idle time in the for the transaction row by
38 -- considering the threshold value.
39 -- 3) update the ratings and the score
40 -- 4) Do the matching for groups based on grouped task identifier.
41
42 PROCEDURE MATCH_RATE_TRX_RECORDS(
43 errbuf OUT NOCOPY VARCHAR2
44 , retcode OUT NOCOPY NUMBER
45 , p_org_id IN NUMBER
46 )
47
48 IS
49 CURSOR c_els_data(l_org_id NUMBER) IS
50 SELECT els_data_id,
51 organization_id,
52 activity_id,
53 activity_detail_id,
54 operation_id,
55 equipment_id,
56 source_zone_id,
57 source_subinventory,
58 destination_zone_id,
59 destination_subinventory,
60 labor_txn_source_id,
61 transaction_uom,
62 from_quantity,
63 to_quantity,
64 item_category_id,
65 operation_plan_id,
66 group_id,
67 task_type_id,
68 task_method_id,
69 expected_travel_time,
70 expected_txn_time,
71 expected_idle_time,
72 travel_time_threshold,
73 num_trx_matched
74 FROM wms_els_individual_tasks_b
75 WHERE organization_id = l_org_id
76 AND history_flag IS NULL
77 AND analysis_id IN( 2,3)
78 ORDER BY sequence_number,group_id;
79
80
81 CURSOR c_els_grouped_data(l_org_id NUMBER) IS
82 SELECT els_group_id,
83 organization_id,
84 activity_id,
85 activity_detail_id,
86 operation_id,
87 labor_txn_source_id,
88 source_zone_id,
89 source_subinventory,
90 destination_zone_id,
91 destination_subinventory,
92 task_method_id,
93 task_range_from,
94 task_range_to,
95 group_size,
96 expected_travel_time
97 FROM wms_els_grouped_tasks_b
98 WHERE organization_id = l_org_id
99 ORDER BY sequence_number;
100
101 CURSOR c_els_grouped_data_id(l_org_id NUMBER) IS
102 SELECT els_group_id
103 FROM wms_els_grouped_tasks_b
104 WHERE organization_id = l_org_id;
105
106
107
108 l_els_data c_els_data%ROWTYPE;
109
110 l_group_data c_els_grouped_data%ROWTYPE;
111
112 l_group_data_id c_els_grouped_data_id%ROWTYPE;
113
114 l_update_count NUMBER;
115
116 l_total NUMBER;
117
118 l_sql VARCHAR2(30000);
119
120 l_sql1 VARCHAR2(30000);
121
122 l_where_clause VARCHAR2(20000);
123
124 c NUMBER;
125
126 l_ret BOOLEAN;
127
128 l_message VARCHAR2(250);
129
130 l_num_execution_failed_tasks NUMBER;
131
132 l_num_execution_failed_group NUMBER;
133
134 l_max_id NUMBER;
135
136 l_avg_travel_time NUMBER;
137
138
139 BEGIN
140
141
142 IF g_debug=1 THEN
143 debug('The value of p_org_id '|| p_org_id,'MATCH_RATE_TRX_RECORDS');
144 END IF;
145
146 l_num_execution_failed_tasks := 0;
147 l_num_execution_failed_group := 0;
148 l_update_count := 0;
149 l_total := 0;
150 l_max_id := 0;
151
152 IF g_debug=1 THEN
153 debug('The value of p_org_id '|| p_org_id,'MATCH_RATE_TRX_RECORDS');
154 END IF;
155
156 IF WMS_LMS_UTILS.ORG_LABOR_MGMT_ENABLED(p_org_id) THEN
157
158 IF g_debug=1 THEN
159 debug('Org is Labor Enabled','MATCH_RATE_TRX_RECORDS');
160 END IF;
161
162
163 -- select the maximum els_trx_src_id from wms_els_trx_src_id
164 -- before this processing begins so that we dont update
165 -- the newly added rows which may be added during the
166 -- execution of this program as non-attributed without
167 -- even picking them for processing
168 -- also this will be used so that the newly added rows should not be
169 -- bucketed against a wrong setup row eith higher sequence number
170 -- as the newly added rows may be added after the first few passes
171 -- of the setup rows have already been done
172
173 select max(els_trx_src_id) into l_max_id from wms_els_trx_src
174 where organization_id = p_org_id;
175
176 IF g_debug=1 THEN
177 debug('Value of the l_max_id at time of the beginning of the process'||l_max_id,'MATCH_RATE_TRX_RECORDS');
178 END IF;
179
180 --start doing the matching
181
182 OPEN c_els_data(p_org_id);
183 LOOP
184 FETCH c_els_data INTO l_els_data;
185 EXIT WHEN c_els_data%NOTFOUND;
186
187 -- flush out v_sql and v_where_clause so that it does not hold any old values
188
189 BEGIN
190
191 l_where_clause := NULL;
192
193 l_sql:=NULL;
194
195 -- This fuction will return TRUE if more rows are left non matched after a certain pass of the
196 -- setup data. It will return FALSE when no more rows are left to process. This fucntion will be
197 -- used to exit the processing once all rows in wms_els_trx_src are exhaused even before
198 -- all the rows in setup are exhausted.
199
200 IF g_debug=1 THEN
201 debug('Check if we have some more rows to process if no exit','MATCH_RATE_TRX_RECORDS');
202 END IF;
203
204 IF WMS_LMS_UTILS.UNPROCESSED_ROWS_REMAINING (p_org_id,l_max_id) =2 THEN
205 EXIT;
206 END IF;
207
208 IF g_debug=1 THEN
209 debug('Got some more rows to process so continue with the next setup row','MATCH_RATE_TRX_RECORDS');
210 END IF;
211
212 IF l_els_data.organization_id IS NOT NULL
213 THEN
214 l_where_clause := l_where_clause || ' AND organization_id = :organization_id ';
215 END IF;
216
217 IF l_els_data.activity_id IS NOT NULL
218 THEN
219 l_where_clause := l_where_clause || ' AND activity_id = :activity_id ';
220 END IF;
221
222 IF l_els_data.activity_detail_id IS NOT NULL
223 THEN
224 l_where_clause := l_where_clause || ' AND activity_detail_id = :activity_detail_id ';
225 END IF;
226
227 IF l_els_data.operation_id IS NOT NULL
228 THEN
229 l_where_clause := l_where_clause || ' AND operation_id = :operation_id ';
230 END IF;
231
232
233 IF l_els_data.equipment_id IS NOT NULL
234 THEN
235 l_where_clause := l_where_clause || ' AND equipment_id = :equipment_id ';
236 END IF;
237
238
239 IF l_els_data.source_zone_id IS NOT NULL
240 THEN
241 -- here not only match the zone_id but also if the loactor lies in that zone.
242 l_where_clause := l_where_clause || ' AND ((source_zone_id = :source_zone_id) '
243 || ' OR ( '
244 || 'from_locator_id'
245 || ' IN (select inventory_location_id'
246 || ' from WMS_ZONE_LOCATORS'
247 || ' where zone_id= :source_zone_id AND organization_id = :org_id'
248 || ' AND '
249 || ' WMS_LMS_UTILS. ZONE_LABOR_MGMT_ENABLED(:org_id,:source_zone_id)=''Y'''
250 || ')'
251 || ' )) ';
252 END IF;
253
254
255 IF l_els_data.source_subinventory IS NOT NULL
256 THEN
257 l_where_clause := l_where_clause || ' AND source_subinventory = :source_subinventory ';
258 END IF;
259
260
261 IF l_els_data.destination_zone_id IS NOT NULL
262 THEN
263 -- here not only match the zone_id but also if the loactor lies in that zone.
264 l_where_clause := l_where_clause || ' AND ((destination_zone_id = :destination_zone_id) '
265 || ' OR ( '
266 || ' to_locator_id '
267 || ' IN (select inventory_location_id '
268 || ' from WMS_ZONE_LOCATORS '
269 || ' where zone_id= :destination_zone_id AND organization_id = :org_id'
270 || ' AND '
271 || ' WMS_LMS_UTILS. ZONE_LABOR_MGMT_ENABLED(:org_id,:destination_zone_id)=''Y'''
272 || ')'
273 || ')) ';
274 END IF;
275
276 IF l_els_data.destination_subinventory IS NOT NULL
277 THEN
278 l_where_clause := l_where_clause ||' AND destination_subinventory = :destination_subinventory ';
279 END IF;
280
281 IF l_els_data.labor_txn_source_id IS NOT NULL
282 THEN
283 l_where_clause := l_where_clause || ' AND labor_txn_source_id = :labor_txn_source_id ';
284 END IF;
285
286 IF l_els_data.transaction_uom IS NOT NULL
287 THEN
288 l_where_clause := l_where_clause || ' AND transaction_uom = :transaction_uom ';
289 END IF;
290
291 IF l_els_data.from_quantity IS NOT NULL
292 THEN
293 l_where_clause := l_where_clause || ' AND quantity >= :from_quantity ';
294 END IF;
295
296 IF l_els_data.to_quantity IS NOT NULL
297 THEN
298 l_where_clause := l_where_clause || ' AND quantity <= :to_quantity ';
299 END IF;
300
301 IF l_els_data.item_category_id IS NOT NULL
302 THEN
303 -- here not only match the category_id but also if the item is assigned to that category.
304 l_where_clause :=l_where_clause || ' AND (( item_category_id = :item_category_id)'
305 || 'OR ('
306 || ' inventory_item_id'
307 || ' IN (select inventory_item_id'
308 || ' from MTL_ITEM_CATEGORIES'
309 || ' where category_id= :item_category_id AND organization_id =:org_id'
310 || ')'
311 || ')) ';
312
313 END IF;
314
315 IF l_els_data.operation_plan_id IS NOT NULL
316 THEN
317 l_where_clause := l_where_clause || ' AND operation_plan_id = :operation_plan_id ';
318 END IF;
319
320 IF l_els_data.group_id IS NOT NULL
321 THEN
322 l_where_clause := l_where_clause || ' AND group_id = :group_id ';
323 END IF;
324
325 IF l_els_data.task_type_id IS NOT NULL
326 THEN
327 l_where_clause := l_where_clause || ' AND task_type_id = :task_type_id ' ;
328 END IF;
329
330 IF l_els_data.task_method_id IS NOT NULL
331 THEN
332 l_where_clause := l_where_clause || ' AND task_method_id = :task_method_id ';
333 END IF;
334
335 IF g_debug=1 THEN
336 debug('The value of l_where_clause is '|| l_where_clause,'MATCH_RATE_TRX_RECORDS');
337 END IF;
338
339 l_sql :=' UPDATE wms_els_trx_src '
340 || ' SET '
341 || ' els_data_id = :els_data_id'
342 || ' , source_zone_id = :source_zone'
343 || ' , destination_zone_id = :destination_zone'
344 || ' , item_category_id = :item_category'
345 || ' , match_group = 1'
346 || ' , unattributed_flag = NULL'
347 || ' , travel_time = (CASE when (travel_and_idle_time > NVL(:travel_time_threshold,0))'
348 || ' then NVL(:travel_time_threshold,travel_and_idle_time) '
349 || ' else travel_and_idle_time '
350 || ' end )'
354 || ' end )'
351 || ' , idle_time = (CASE when ( travel_and_idle_time > NVL(:travel_time_threshold,0)) '
352 || 'then (travel_and_idle_time - NVL(:travel_time_threshold,travel_and_idle_time)) '
353 || ' else 0 '
355 || ' , employee_rating_travel = (select labor_rating from WMS_ELS_RATINGS_SETUP WERS, WMS_ELS_PARAMETERS WEP '
356 || ' where WEP.ELS_PARAMETER_ID = WERS.ELS_PARAMETER_ID'
357 || ' AND WEP.ORGANIZATION_ID = :org_id '
358 || ' AND WERS.RATING_TYPE = ''TRA'' '
359 || ' AND ( '
360 || ' (CASE when ( travel_and_idle_time > NVL(:travel_time_threshold,0)) '
361 || ' then NVL(:travel_time_threshold,travel_and_idle_time) '
362 || ' else travel_and_idle_time '
363 || ' end ) '
364 || '/:expected_travel_time '
365 || ' )*100 >= wers.Per_Expected_Time_From '
366 || ' AND ( '
367 || ' (CASE when ( travel_and_idle_time > NVL(:travel_time_threshold,0)) '
368 || ' then NVL(:travel_time_threshold,travel_and_idle_time) '
369 || ' else travel_and_idle_time '
370 || ' end ) '
371 || '/:expected_travel_time '
372 || ' )*100 < NVL(wers.Per_Expected_Time_To,100000) '
373 || ' )'
374 || ' , travel_score = (select per_score from WMS_ELS_RATINGS_SETUP WERS, WMS_ELS_PARAMETERS WEP '
375 || ' where WEP.ELS_PARAMETER_ID = WERS.ELS_PARAMETER_ID'
376 || ' AND WEP.ORGANIZATION_ID = :org_id '
377 || ' AND WERS.RATING_TYPE = ''TRA'' '
378 || ' AND ( '
379 || ' (CASE when ( travel_and_idle_time > NVL(:travel_time_threshold,0)) '
380 || ' then NVL(:travel_time_threshold,travel_and_idle_time) '
381 || ' else travel_and_idle_time '
382 || ' end ) '
383 || '/:expected_travel_time '
384 || ' )*100 >= wers.Per_Expected_Time_From '
385 || ' AND ( '
386 || ' (CASE when ( travel_and_idle_time > NVL(:travel_time_threshold,0)) '
387 || ' then NVL(:travel_time_threshold,travel_and_idle_time) '
388 || ' else travel_and_idle_time '
389 || ' end ) '
390 || '/:expected_travel_time '
391 || ' )*100 < NVL(wers.Per_Expected_Time_To,100000) '
392 || ' )'
393 || ' , employee_rating_txn = ( select labor_rating from WMS_ELS_RATINGS_SETUP WERS, WMS_ELS_PARAMETERS WEP '
394 || ' where WEP.ELS_PARAMETER_ID = WERS.ELS_PARAMETER_ID '
395 || ' AND WEP.ORGANIZATION_ID = :org_id '
396 || ' AND WERS.RATING_TYPE = ''TXN'' '
397 || ' AND (transaction_time/:expected_trx_time)*100 >= wers.Per_Expected_Time_From'
398 || ' AND (transaction_time/:expected_trx_time)*100 < NVL( wers.Per_Expected_Time_To,100000) '
399 || ' ) '
400 || ' , txn_score = ( select per_score from WMS_ELS_RATINGS_SETUP WERS, WMS_ELS_PARAMETERS WEP '
401 || ' where WEP.ELS_PARAMETER_ID = WERS.ELS_PARAMETER_ID '
402 || ' AND WEP.ORGANIZATION_ID = :org_id '
403 || ' AND WERS.RATING_TYPE = ''TXN'' '
404 || ' AND (transaction_time/:expected_trx_time)*100 >= wers.Per_Expected_Time_From'
405 || ' AND (transaction_time/:expected_trx_time)*100 < NVL( wers.Per_Expected_Time_To,100000) '
406 || ' ) '
407 || ' ,employee_rating_Idle = ( select labor_rating from WMS_ELS_RATINGS_SETUP WERS, WMS_ELS_PARAMETERS WEP '
408 || ' where WEP.ELS_PARAMETER_ID = WERS.ELS_PARAMETER_ID '
409 || ' AND WEP.ORGANIZATION_ID = :org_id '
410 || ' AND WERS.RATING_TYPE = ''IDL'' '
411 || ' AND ((CASE when (travel_and_idle_time > NVL(:travel_time_threshold,0) )'
412 || 'then (travel_and_idle_time - NVL(:travel_time_threshold,travel_and_idle_time)) '
413 || 'else 0 '
414 || 'end )'
415 || ' /:expected_idle_time '
416 || ')*100 >= wers.Per_Expected_Time_From '
417 || 'AND ((CASE when (travel_and_idle_time > NVL(:travel_time_threshold,0) )'
418 || 'then (travel_and_idle_time - NVL(:travel_time_threshold,travel_and_idle_time)) '
419 || 'else 0 '
420 || 'end )'
421 || ' /:expected_idle_time '
422 || ')*100 < NVL(wers.Per_Expected_Time_To,10000) '
426 || ' AND WEP.ORGANIZATION_ID = :org_id '
423 || ' )'
424 || ' ,idle_score = ( select per_score from WMS_ELS_RATINGS_SETUP WERS, WMS_ELS_PARAMETERS WEP '
425 || ' where WEP.ELS_PARAMETER_ID = WERS.ELS_PARAMETER_ID '
427 || ' AND WERS.RATING_TYPE = ''IDL'' '
428 || ' AND ((CASE when (travel_and_idle_time > NVL(:travel_time_threshold,0) )'
429 || 'then (travel_and_idle_time - NVL(:travel_time_threshold,travel_and_idle_time)) '
430 || 'else 0 '
431 || 'end )'
432 || ' /:expected_idle_time '
433 || ')*100 >= wers.Per_Expected_Time_From '
434 || 'AND ((CASE when (travel_and_idle_time > NVL(:travel_time_threshold,0) )'
435 || 'then (travel_and_idle_time - NVL(:travel_time_threshold,travel_and_idle_time)) '
436 || 'else 0 '
437 || 'end )'
438 || ' /:expected_idle_time '
439 || ')*100 < NVL(wers.Per_Expected_Time_To,100000) '
440 || ' )' ;
441
442 l_sql := l_sql ||' where els_data_id IS NULL AND transaction_date IS NOT NULL AND els_trx_src_id <= :l_max_id';
443
444
445 IF g_debug=1 THEN
446 debug('The sql clause constructed','MATCH_RATE_TRX_RECORDS');
447 END IF;
448
449 l_sql := l_sql||l_where_clause;
450
451 IF g_debug=1 THEN
452 debug('The l_sql finally constructed ','MATCH_RATE_TRX_RECORDS');
453 END IF;
454
455 c:= dbms_sql.open_cursor;
456
457 IF g_debug=1 THEN
458 debug('Opened the cursor for Binding ','MATCH_RATE_TRX_RECORDS');
459 END IF;
460
461 DBMS_SQL.parse(c, l_sql, DBMS_SQL.native);
462
463
464 IF g_debug=1 THEN
465 debug('Starting Binding the variables ','MATCH_RATE_TRX_RECORDS');
466 END IF;
467
468 DBMS_SQL.bind_variable(c, 'l_max_id', l_max_id);
469
470 DBMS_SQL.bind_variable(c, 'els_data_id', l_els_data.els_data_id);
471
472 DBMS_SQL.bind_variable(c, 'source_zone', l_els_data.source_zone_id);
473
474 DBMS_SQL.bind_variable(c, 'destination_zone', l_els_data.destination_zone_id);
475
476 DBMS_SQL.bind_variable(c, 'item_category', l_els_data.item_category_id);
477
478 DBMS_SQL.bind_variable(c, 'travel_time_threshold', l_els_data.travel_time_threshold);
479
480 DBMS_SQL.bind_variable(c, 'org_id', p_org_id);
481
482 DBMS_SQL.bind_variable(c, 'expected_trx_time', l_els_data.expected_txn_time);
483
484 DBMS_SQL.bind_variable(c, 'expected_travel_time', l_els_data.expected_travel_time);
485
486 DBMS_SQL.bind_variable(c, 'expected_idle_time', l_els_data.expected_idle_time);
487
488 IF l_els_data.organization_id IS NOT NULL
489 THEN
490 DBMS_SQL.bind_variable(c, 'organization_id', l_els_data.organization_id);
491 END IF;
492
493 IF l_els_data.activity_id IS NOT NULL
494 THEN
495 DBMS_SQL.bind_variable(c, 'activity_id', l_els_data.activity_id);
496 END IF;
497
498 IF l_els_data.activity_detail_id IS NOT NULL
499 THEN
500 DBMS_SQL.bind_variable(c, 'activity_detail_id', l_els_data.activity_detail_id);
501 END IF;
502
503 IF l_els_data.operation_id IS NOT NULL
504 THEN
505 DBMS_SQL.bind_variable(c, 'operation_id', l_els_data.operation_id);
506 END IF;
507
508 IF l_els_data.equipment_id IS NOT NULL
509 THEN
510 DBMS_SQL.bind_variable(c, 'equipment_id', l_els_data.equipment_id);
511 END IF;
512
513 IF l_els_data.source_zone_id IS NOT NULL
514 THEN
515 DBMS_SQL.bind_variable(c, 'source_zone_id', l_els_data.source_zone_id);
516 END IF;
517
518 IF l_els_data.source_subinventory IS NOT NULL
519 THEN
520 DBMS_SQL.bind_variable(c, 'source_subinventory', l_els_data.source_subinventory);
521 END IF;
522
523 IF l_els_data.destination_zone_id IS NOT NULL
524 THEN
525 DBMS_SQL.bind_variable(c, 'destination_zone_id', l_els_data.destination_zone_id);
526 END IF;
527
528 IF l_els_data.destination_subinventory IS NOT NULL
529 THEN
530 DBMS_SQL.bind_variable(c, 'destination_subinventory', l_els_data.destination_subinventory);
531 END IF;
532
533 IF l_els_data.labor_txn_source_id IS NOT NULL
534 THEN
535 DBMS_SQL.bind_variable(c, 'labor_txn_source_id', l_els_data.labor_txn_source_id);
536 END IF;
537
538 IF l_els_data.transaction_uom IS NOT NULL
539 THEN
540 DBMS_SQL.bind_variable(c, 'transaction_uom', l_els_data.transaction_uom);
541 END IF;
542
543 IF l_els_data.from_quantity IS NOT NULL
544 THEN
545 DBMS_SQL.bind_variable(c, 'from_quantity', l_els_data.from_quantity);
546 END IF;
547
548 IF l_els_data.to_quantity IS NOT NULL
549 THEN
550 DBMS_SQL.bind_variable(c, 'to_quantity', l_els_data.to_quantity);
551 END IF;
552
553 IF l_els_data.item_category_id IS NOT NULL
554 THEN
555 DBMS_SQL.bind_variable(c, 'item_category_id', l_els_data.item_category_id);
556 END IF;
557
558 IF l_els_data.operation_plan_id IS NOT NULL
559 THEN
560 DBMS_SQL.bind_variable(c, 'operation_plan_id', l_els_data.operation_plan_id);
561 END IF;
562
563 IF l_els_data.group_id IS NOT NULL
564 THEN
565 DBMS_SQL.bind_variable(c, 'group_id', l_els_data.group_id);
566 END IF;
567
568 IF l_els_data.task_type_id IS NOT NULL
569 THEN
570 DBMS_SQL.bind_variable(c, 'task_type_id', l_els_data.task_type_id);
571 END IF;
572
576 END IF;
573 IF l_els_data.task_method_id IS NOT NULL
574 THEN
575 DBMS_SQL.bind_variable(c, 'task_method_id', l_els_data.task_method_id);
577
578 IF g_debug=1 THEN
579 debug('All variables bound '|| l_sql,'MATCH_RATE_TRX_RECORDS');
580 END IF;
581
582 l_update_count := DBMS_SQL.EXECUTE(c);
583
584 IF g_debug=1 THEN
585 debug('SQL executed Number of rows updated '|| l_update_count,'MATCH_RATE_TRX_RECORDS');
586 END IF;
587
588 l_total := l_update_count + NVL(l_els_data.num_trx_matched,0);
589
590 DBMS_SQL.close_cursor(c);
591
592 --update the count with newly matched transactions
593
594 UPDATE wms_els_individual_tasks_b
595 SET
596 num_trx_matched = l_total
597 WHERE els_data_id = l_els_data.els_data_id;
598
599 EXCEPTION
600 WHEN OTHERS THEN
601
602 l_num_execution_failed_tasks := l_num_execution_failed_tasks +1;
603 IF g_debug=1 THEN
604 debug('Execution failed for the els_data_id '|| l_els_data.els_data_id,'MATCH_RATE_EXP_RESOURCE');
605 debug('Exception occured '|| sqlerrm,'MATCH_RATE_TRX_RECORDS');
606 END IF;
607
608 END;
609
610 END LOOP; -- all els_rows exhausted
611
612 CLOSE c_els_data;
613
614 -- Now do the matching for groups
615
616 -- populate a pl/sql table type with the trx table records grouped by
617 -- the grouped_task_identifier.
618
619 --Populate the global temporary table
620
621 IF g_debug=1 THEN
622 debug('Execution finished for Individual tasks','MATCH_RATE_TRX_RECORDS');
623 debug('Starting Execution for matching Grouped tasks','MATCH_RATE_TRX_RECORDS');
624 debug('Populating Global temporary table ','MATCH_RATE_TRX_RECORDS');
625 END IF;
626
627
628
629 INSERT INTO WMS_ELS_GROUPED_TASKS_GTMP
630 (
631 els_grouped_task_id,
632 organization_id,
633 activity_id,
634 activity_detail_id,
635 operation_id,
636 labor_txn_source_id,
637 source_zone_id,
638 source_subinventory,
639 destination_zone_id,
640 destination_subinventory,
641 num_transactions,
642 task_method,
643 group_size,
644 sum_travel_time
645 )
646 SELECT WMS_ELS_GROUPED_TASKS_S.NEXTVAL,
647 organization_id,
648 activity_id,
649 activity_detail_id,
650 operation_id,
651 labor_txn_source_id,
652 source_zone_id,
653 source_subinventory,
654 destination_zone_id,
655 destination_subinventory,
656 num_tasks,
657 task_method_id,
658 group_size,
659 total_travel_time
660 FROM
661 (
662 SELECT
663 organization_id,
664 activity_id,
665 activity_detail_id,
666 operation_id,
667 labor_txn_source_id,
668 source_zone_id,
669 source_subinventory,
670 destination_zone_id,
671 destination_subinventory,
672 count(*) num_tasks,
673 task_method_id,
674 group_size,
675 SUM(travel_time) total_travel_time
676 FROM wms_els_trx_src
677 WHERE organization_id = p_org_id
678 AND match_group = 1
679 AND transaction_date IS NOT NULL
680 AND grouped_task_identifier IS NOT NULL
681 GROUP BY grouped_task_identifier,organization_id,activity_id,
682 activity_detail_id,operation_id,labor_txn_source_id,
683 source_zone_id,source_subinventory,destination_zone_id,destination_subinventory,
684 task_method_id,group_size
685 );
686
687 IF g_debug=1 THEN
688 debug('Finished Populating Global temporary table ','MATCH_RATE_TRX_RECORDS');
689 END IF;
690
691
692 -- open the cursor for the passed org_id
693 IF g_debug=1 THEN
694 debug('Opening cursor for wms_els_grouped_tasks_b ','MATCH_RATE_TRX_RECORDS');
695 END IF;
696
697 OPEN c_els_grouped_data(p_org_id);
698
699 LOOP
700 FETCH c_els_grouped_data INTO l_group_data;
701 EXIT WHEN c_els_grouped_data%NOTFOUND;
702
703 -- open the cursor for the passed org_id
704 BEGIN
705
706 IF g_debug=1 THEN
707 debug('Start building the where clause for wms_els_grouped_tasks_b ','MATCH_RATE_TRX_RECORDS');
708 END IF;
709
710 l_where_clause := NULL;
711 l_update_count := 0;
712
713 IF l_group_data.organization_id IS NOT NULL
714 THEN
715 l_where_clause := l_where_clause || ' AND organization_id = :organization_id ';
716 END IF;
717
718 IF l_group_data.activity_id IS NOT NULL
719 THEN
720 l_where_clause := l_where_clause || ' AND activity_id = :activity_id ';
721 END IF;
722
723 IF l_group_data.activity_detail_id IS NOT NULL
724 THEN
725 l_where_clause := l_where_clause || ' AND activity_detail_id = :activity_detail_id ';
726 END IF;
727
728 IF l_group_data.operation_id IS NOT NULL
729 THEN
730 l_where_clause := l_where_clause || ' AND operation_id = :operation_id ';
731 END IF;
732
733 IF l_group_data.source_zone_id IS NOT NULL
734 THEN
735 l_where_clause := l_where_clause || ' AND source_zone_id = :source_zone_id ';
736 END IF;
737
738 IF l_group_data.source_subinventory IS NOT NULL
739 THEN
740 l_where_clause := l_where_clause ||' AND source_subinventory = :source_subinventory ';
741 END IF;
742
743 IF l_group_data.destination_zone_id IS NOT NULL
744 THEN
745 l_where_clause := l_where_clause || ' AND destination_zone_id = :destination_zone_id ';
746 END IF;
747
748 IF l_group_data.destination_subinventory IS NOT NULL
749 THEN
750 l_where_clause := l_where_clause || ' AND destination_subinventory = :destination_subinventory ';
751 END IF;
755 l_where_clause := l_where_clause || ' AND num_transactions > :task_range_from ' ;
752
753 IF l_group_data.task_range_from IS NOT NULL
754 THEN
756 END IF;
757
758 IF l_group_data.task_range_to IS NOT NULL
759 THEN
760 l_where_clause := l_where_clause || ' AND num_transactions < :task_range_to ' ;
761 END IF;
762
763 IF l_group_data.task_method_id IS NOT NULL
764 THEN
765 l_where_clause := l_where_clause || ' AND task_method =:task_method_id ' ;
766 END IF;
767
768
769 IF g_debug=1 THEN
770 debug('Where clause built successfully '|| l_where_clause ,'MATCH_RATE_TRX_RECORDS');
771 END IF;
772
773 -- flush out l_sql so that it does not hold any old values
774 l_sql:= NULL;
775
776 l_sql:= ' UPDATE WMS_ELS_GROUPED_TASKS_GTMP '
777 || ' SET els_group_id = :els_group_id '
778 || ' where els_group_id IS NULL '
779 || l_where_clause ;
780
781 IF g_debug=1 THEN
782 debug('SQL clause built successfully '|| l_sql ,'MATCH_RATE_TRX_RECORDS');
783 END IF;
784
785 c:= dbms_sql.open_cursor;
786
787 DBMS_SQL.parse(c, l_sql, DBMS_SQL.native);
788
789 IF g_debug=1 THEN
790 debug('Start bining the variables '|| l_where_clause ,'MATCH_RATE_TRX_RECORDS');
791 END IF;
792
793 DBMS_SQL.bind_variable(c, 'els_group_id', l_group_data.els_group_id);
794
795 IF l_group_data.organization_id IS NOT NULL
796 THEN
797 DBMS_SQL.bind_variable(c, 'organization_id', l_group_data.organization_id);
798 END IF;
799
800 IF l_group_data.activity_id IS NOT NULL
801 THEN
802 DBMS_SQL.bind_variable(c, 'activity_id', l_group_data.activity_id);
803 END IF;
804
805 IF l_group_data.activity_detail_id IS NOT NULL
806 THEN
807 DBMS_SQL.bind_variable(c, 'activity_detail_id', l_group_data.activity_detail_id);
808 END IF;
809
810 IF l_group_data.operation_id IS NOT NULL
811 THEN
812 DBMS_SQL.bind_variable(c, 'operation_id', l_group_data.operation_id);
813 END IF;
814
815 IF l_group_data.source_zone_id IS NOT NULL
816 THEN
817 DBMS_SQL.bind_variable(c, 'source_zone_id', l_group_data.source_zone_id);
818 END IF;
819
820 IF l_group_data.source_subinventory IS NOT NULL
821 THEN
822 DBMS_SQL.bind_variable(c, 'source_subinventory', l_group_data.source_subinventory);
823 END IF;
824
825 IF l_group_data.destination_zone_id IS NOT NULL
826 THEN
827 DBMS_SQL.bind_variable(c, 'destination_zone_id', l_group_data.destination_zone_id);
828 END IF;
829
830 IF l_group_data.destination_subinventory IS NOT NULL
831 THEN
832 DBMS_SQL.bind_variable(c, 'destination_subinventory', l_group_data.destination_subinventory);
833 END IF;
834
835 IF l_group_data.task_range_from IS NOT NULL
836 THEN
837 DBMS_SQL.bind_variable(c, 'task_range_from', l_group_data.task_range_from);
838 END IF;
839
840 IF l_group_data.task_range_to IS NOT NULL
841 THEN
842 DBMS_SQL.bind_variable(c, 'task_range_to', l_group_data.task_range_to);
843 END IF;
844
845 IF l_group_data.task_method_id IS NOT NULL
846 THEN
847 DBMS_SQL.bind_variable(c, 'task_method_id', l_group_data.task_method_id);
848 END IF;
849
850 IF g_debug=1 THEN
851 debug('All variables bound successfully','MATCH_RATE_TRX_RECORDS');
852 END IF;
853
854 l_update_count := DBMS_SQL.EXECUTE(c);
855
856 IF g_debug=1 THEN
857 debug('SQL executed. Number of rows updated '|| l_update_count ,'MATCH_RATE_TRX_RECORDS');
858 END IF;
859
860 DBMS_SQL.close_cursor(c);
861
862 EXCEPTION
863 WHEN OTHERS THEN
864
865 l_num_execution_failed_group := l_num_execution_failed_group +1;
866 IF g_debug=1 THEN
867 debug('Execution failed for the els_group_id '|| l_group_data.els_group_id,'MATCH_RATE_TRX_RECORDS');
868 debug('Exception occured '|| sqlerrm,'MATCH_RATE_TRX_RECORDS');
869 END IF;
870
871 END;
872
873 END LOOP; -- matching for groups is done
874
875 CLOSE c_els_grouped_data;
876
877 -- start updating the catual timings in the wms_els_grouped_tasks_b table
878 OPEN c_els_grouped_data_id(p_org_id);
879 LOOP
880 FETCH c_els_grouped_data_id INTO l_group_data_id;
881 EXIT WHEN c_els_grouped_data_id%NOTFOUND;
882
883 SELECT avg(sum_travel_time) into l_avg_travel_time from WMS_ELS_GROUPED_TASKS_GTMP
884 WHERE els_group_id = l_group_data_id.els_group_id;
885
886
887 IF( l_avg_travel_time IS NOT NULL) THEN
888
889 UPDATE WMS_ELS_GROUPED_TASKS_B SET actual_travel_time = (NVL(actual_travel_time,0) + l_avg_travel_time)/2
890 where els_group_id = l_group_data_id.els_group_id;
891
892 END IF;
893
894 END LOOP;
895
896 CLOSE c_els_grouped_data_id;
897
898 l_update_count := NULL;
899
900 -- now update all txns having els_data_id as NULL with processed flag as 1
901 -- also update the match_group flag to 2(done)
902
903 UPDATE wms_els_trx_src SET unattributed_flag = 1 , match_group = 2
904 WHERE els_data_id IS NULL and els_trx_src_id <= l_max_id AND organization_id = p_org_id;
905
906 l_update_count := SQL%ROWCOUNT;
907
908 IF g_debug=1 THEN
909 debug('Number of rows updated as non-standardized '|| l_update_count,'MATCH_RATE_TRX_RECORDS');
910 debug('Value of l_num_execution_failed_tasks '|| l_num_execution_failed_tasks,'MATCH_RATE_TRX_RECORDS');
911 debug('Value of l_num_execution_failed_group'|| l_num_execution_failed_group,'MATCH_RATE_TRX_RECORDS');
912 END IF;
913
914
915 IF ( l_num_execution_failed_tasks = 0 AND l_num_execution_failed_group = 0 )THEN
916 -- every thing is done so the concurrent program has finished normal
917 COMMIT;
918 retcode := 1;
922
919 fnd_message.set_name('WMS', 'WMS_LMS_LP_SUCCESS');
920 l_message := fnd_message.get;
921 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',l_message);
923 ELSE
924 COMMIT;
925 retcode := 3;
926 fnd_message.set_name('WMS', 'WMS_LMS_LP_WARN');
927 l_message := fnd_message.get;
928 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',l_message);
929
930 END IF;
931
932 ELSE -- org is not labor enabled
933
934 IF g_debug=1 THEN
935 debug('Org is not labor enabled '|| sqlerrm,'MATCH_RATE_TRX_RECORDS');
936 END IF;
937
938 retcode := 3;
939 fnd_message.set_name('WMS', 'WMS_ORG_NOT_LMS_ENABLED');
940 l_message := fnd_message.get;
941 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',l_message);
942
943 END IF; -- If org is labor enabled
944
945
946 EXCEPTION
947
948 -- handle exception
949 WHEN OTHERS THEN
950
951 IF g_debug=1 THEN
952 debug('Exception occured . Close all open cursors'|| sqlerrm,'MATCH_RATE_TRX_RECORDS');
953 END IF;
954
955 IF c_els_grouped_data_id%ISOPEN THEN
956 CLOSE c_els_grouped_data_id;
957 END IF;
958
959 IF c_els_grouped_data%ISOPEN THEN
960 CLOSE c_els_grouped_data;
961 END IF;
962
963 IF c_els_data%ISOPEN THEN
964 CLOSE c_els_data;
965 END IF;
966
967 retcode := 2;
968 fnd_message.set_name('WMS', 'WMS_MATCH_LP_ERR');
969 l_message := fnd_message.get;
970 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_message);
971
972
973 END MATCH_RATE_TRX_RECORDS;
974
975 END WMS_LMS_LABOR_PRODUCTIVITY;
976
977
[Home] [Help]