1 PACKAGE BODY WMS_CONTROL_BOARD AS
2 /* $Header: WMSCBCPB.pls 120.2 2005/07/12 02:31:36 varajago noship $ */
3
4 -- Global constant holding package name
5 g_pkg_name CONSTANT VARCHAR2(20) := ' WMS_CONTROL_BOARD' ;
6
7
8 /**************************************
9 *
10 **************************************/
11 -- Bug # 1800521, added new parameter so that where clause of the cursor matches
12 -- exactly same as control board find query condition and chart reflects the
13 -- queried data.
14 -- After making changes in the control board form to split the task and
15 -- exception views into separate ones, it makes sense to have the
16 -- performance chart use the same where clause that is set either through
17 -- the query find window or selecting the nodes in the trees
18 -- The where clause is now set as a form level parameter
19
20 /* --Bug#2483984 Performace Tuning of WMS Control Board
21 -- now there are separate FROM and WHERE clauses for active, pending and completed tasks */
22 PROCEDURE get_status_dist (
23 x_status_chart_data OUT NOCOPY /* file.sql.39 change */ cb_chart_status_tbl_type
24 , x_status_data_count OUT NOCOPY /* file.sql.39 change */ NUMBER
25 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
26 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
27 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
28 , p_cq_type IN NUMBER
29 , p_at_from IN VARCHAR2
30 , p_pt_from IN VARCHAR2
31 , p_ct_from IN VARCHAR2
32 , p_at_where IN VARCHAR2
33 , p_pt_where IN VARCHAR2
34 , p_ct_where IN VARCHAR2
35 , p_acy_from IN VARCHAR2
36 , p_pcy_from IN VARCHAR2
37 , p_ccy_from IN VARCHAR2
38 , p_acy_where IN VARCHAR2
39 , p_pcy_where IN VARCHAR2
40 , p_ccy_where IN VARCHAR2 ) IS
41
42 c_api_name CONSTANT VARCHAR2(20) := 'get_status_dist';
43 l_CursorStmt VARCHAR2(32000);
44 l_CursorID INTEGER;
45 l_Dummy INTEGER;
46 l_temp_status NUMBER;
47 l_temp_count NUMBER;
48 i NUMBER;
49 TYPE status_table IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
50 l_status_code_table status_table;
51 l_temp_status_code VARCHAR2(80);
52 TYPE status_count_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
53 l_status_count_table status_count_table;
54 l_loop_index NUMBER;
55 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
56 BEGIN
57 -- Get all of the lookup values for the status code
58 -- There are 7, 1-Pending, 2-Queued, 3-Dispatched, 4-Loaded , 5 -errored, 6-Completed, 7-Hold
59 i := 1;
60 LOOP
61 SELECT meaning
62 INTO l_temp_status_code
63 FROM mfg_lookups
64 WHERE lookup_type = 'WMS_TASK_STATUS'
65 AND lookup_code = i;
66 l_status_code_table(i) := l_temp_status_code;
67 EXIT WHEN i = 7;
68 i := i + 1;
69 END LOOP;
70
71
72 -- Define the cursor statement
73 -- noraml tasks alone
74 l_CursorStmt := ' SELECT wdt.status, count(*) task_count FROM ' || p_at_from || ' WHERE ' || p_at_where ||
75 ' AND wdt.status IN (2,3,4,5) GROUP BY wdt.status UNION ALL ' ||
76 ' SELECT 1, count(*) task_count FROM ' || p_pt_from || ' WHERE ' || p_pt_where || ' HAVING count(rownum) > 0 ' ||
77 ' UNION ALL SELECT 6, count(*) task_count FROM ' || p_ct_from || ' WHERE ' || p_ct_where || ' HAVING count(rownum) > 0 ';
78
79 IF p_cq_type = 1 THEN
80 -- query noraml + cc tasks
81 l_CursorStmt := l_CursorStmt || ' UNION ALL ' ||
82 ' SELECT wdt.status, count(*) task_count FROM ' || p_acy_from ||
83 ' WHERE ' || p_acy_where || ' AND wdt.status IN (2,3,4,5) GROUP BY wdt.status UNION ALL ' ||
84 ' SELECT 1, count(min(mcce.cycle_count_entry_id)) task_count FROM ' || p_pcy_from ||
85 ' WHERE ' || p_pcy_where || --' and count(rownum) > 0 ' ||
86 ' UNION ALL SELECT 6, count(*) task_count FROM ' || p_ccy_from ||
87 ' WHERE ' || p_ccy_where ||
88 ' HAVING count(rownum) > 0 ';
89 ELSIF p_cq_type = 2 THEN
90 --query cc tasks alone
91 l_CursorStmt := ' SELECT wdt.status, count(*) task_count FROM ' || p_acy_from ||
92 ' WHERE ' || p_acy_where || ' AND wdt.status IN (2,3,4,5) GROUP BY wdt.status UNION ALL ' ||
93 ' SELECT 1, count(min(mcce.cycle_count_entry_id)) task_count FROM ' || p_pcy_from ||
94 ' WHERE ' || p_pcy_where || --' and count(rownum) > 0 ' ||
95 ' UNION ALL SELECT 6, count(*) task_count FROM ' || p_ccy_from ||
96 ' WHERE ' || p_ccy_where ||
97 ' HAVING count(rownum) > 0 ';
98 END IF;
99
100 -- Open a cursor for processing.
101 l_CursorID := DBMS_SQL.OPEN_CURSOR;
102
103 -- Parse the query
104 DBMS_SQL.PARSE(l_CursorID, l_CursorStmt, DBMS_SQL.V7);
105
106 -- Define the output variables
107 DBMS_SQL.DEFINE_COLUMN(l_CursorID, 1, l_temp_status);
108 DBMS_SQL.DEFINE_COLUMN(l_CursorID, 2, l_temp_count);
109
110 -- Execute the statement. We don't care about the return value,
111 -- but we do need to declare a variable for it.
112 l_Dummy := DBMS_SQL.EXECUTE(l_CursorID);
113
114 -- This is the fetch loop
115 x_status_data_count := 0;
116 i := 0;
117 x_return_status := fnd_api.g_ret_sts_success;
118 LOOP
119 -- Fetch the rows into the buffer, and also check for the exit
120 -- condition from the loop.
121 IF DBMS_SQL.FETCH_ROWS(l_CursorID) = 0 THEN
122 EXIT;
123 END IF;
124
125 -- Retrieve the rows from the buffer into temp variables.
126 DBMS_SQL.COLUMN_VALUE(l_CursorID, 1, l_temp_status);
127 DBMS_SQL.COLUMN_VALUE(l_CursorID, 2, l_temp_count);
128
129 --mydebug('CBPT: Storing into temp table for status -->' || l_temp_status || ' = ' || l_temp_count );
130 -- Store these values in the task count table
131 IF (l_status_count_table.EXISTS(l_temp_status)) THEN
132 l_status_count_table(l_temp_status) := l_status_count_table(l_temp_status) + l_temp_count;
133 ELSE
134 l_status_count_table(l_temp_status) := l_temp_count;
135 END IF;
136
137 END LOOP;
138 -- Close the cursor.
139 DBMS_SQL.CLOSE_CURSOR(l_CursorID);
140
141
142 -- Input in the status type and count information into the chart table
143 x_status_data_count := 0;
144 l_loop_index := l_status_count_table.FIRST;
145
146 -- Populate the chart only if there are records returned by the cursor c_type
147 IF (l_loop_index IS NOT NULL) THEN
148 LOOP
149
150 x_status_data_count := x_status_data_count + 1;
151 x_status_chart_data(x_status_data_count).status := l_status_code_table(l_loop_index);
152 x_status_chart_data(x_status_data_count).task_count := l_status_count_table(l_loop_index);
153 EXIT WHEN l_loop_index = l_status_count_table.LAST;
154 l_loop_index := l_status_count_table.NEXT(l_loop_index);
155
156 END LOOP;
157 END IF;
158
159 EXCEPTION
160 WHEN FND_API.G_EXC_ERROR THEN
161 x_return_status := FND_API.G_RET_STS_ERROR;
162 -- Close the cursor.
163 DBMS_SQL.CLOSE_CURSOR(l_CursorID);
164
165 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
166 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
167 -- Close the cursor.
168 DBMS_SQL.CLOSE_CURSOR(l_CursorID);
169
170 WHEN OTHERS THEN
171 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
172 IF (FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
173 FND_MSG_PUB.ADD_EXC_MSG(g_pkg_name, c_api_name);
174 END IF;
175 -- Close the cursor.
176 DBMS_SQL.CLOSE_CURSOR(l_CursorID);
177
178 END get_status_dist;
179
180
181 /**************************************
182 *
183 **************************************/
184
185 /* --Bug#2483984 Performace Tuning of WMS Control Board
186 -- now there are separate FROM and WHERE clauses for active, pending and completed tasks */
187 PROCEDURE get_type_dist(
188 x_type_chart_data OUT NOCOPY /* file.sql.39 change */ cb_chart_type_tbl_type
189 , x_type_data_count OUT NOCOPY /* file.sql.39 change */ NUMBER
190 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
191 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
192 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
193 , p_cq_type IN NUMBER
194 , p_at_from IN VARCHAR2
195 , p_pt_from IN VARCHAR2
196 , p_ct_from IN VARCHAR2
197 , p_at_where IN VARCHAR2
198 , p_pt_where IN VARCHAR2
199 , p_ct_where IN VARCHAR2
200 , p_acy_from IN VARCHAR2
201 , p_pcy_from IN VARCHAR2
202 , p_ccy_from IN VARCHAR2
203 , p_acy_where IN VARCHAR2
204 , p_pcy_where IN VARCHAR2
205 , p_ccy_where IN VARCHAR2 ) IS
206
207 c_api_name CONSTANT VARCHAR2(20) := 'get_type_dist';
208 l_CursorStmt VARCHAR2(32000);
209 l_CursorID INTEGER;
210 l_Dummy INTEGER;
211 l_temp_task NUMBER;
212 l_temp_count NUMBER;
213 l_loop_index NUMBER;
214 TYPE task_type_table IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
215 l_task_type_table task_type_table;
216 l_temp_task_type VARCHAR2(80);
217 TYPE task_count_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
218 l_task_count_table task_count_table;
219
220 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
221 BEGIN
222 -- Get all of the lookup values for the task type description
223 -- There should be only 6, 1-Pick, 2-Putaway, 3-Cycle Count, 4-Replenish 5-MOXfer 6-MOIssue
224 l_loop_index := 1;
225 LOOP
226 SELECT meaning
227 INTO l_temp_task_type
228 FROM mfg_lookups
229 WHERE lookup_type = 'WMS_TASK_TYPES'
230 AND lookup_code = l_loop_index;
231 l_task_type_table(l_loop_index) := l_temp_task_type;
232 EXIT WHEN l_loop_index = 6;
233 l_loop_index := l_loop_index + 1;
234 END LOOP;
235
236 -- Define the cursor statement
237 l_CursorStmt := 'SELECT NVL(wdt.task_type,mmtt.wms_task_type) task_type, count(*) task_count FROM ' ||
238 p_at_from || ' WHERE ' || p_at_where ||
239 ' AND status IN (2,3,4,5) GROUP BY NVL(wdt.task_type,mmtt.wms_task_type) UNION ALL ' ||
240 'SELECT mmtt.wms_task_type task_type, count(*) task_count FROM ' ||
241 p_pt_from || ' WHERE ' || p_pt_where || ' GROUP BY mmtt.wms_task_type UNION ALL ' ||
242 'SELECT wdth.task_type task_type, count(*) task_count FROM ' ||
243 p_ct_From || ' WHERE ' || p_ct_where || ' GROUP BY wdth.task_type ';
244
245 IF p_cq_type = 1 THEN
246 -- query noraml + cc tasks
247 l_CursorStmt := l_CursorStmt || ' UNION ALL ' ||
248 ' SELECT 3 task_type, count(*) task_count FROM ' || p_acy_from ||
249 ' WHERE ' || p_acy_where || ' AND wdt.status IN (2,3,4,5) HAVING count(rownum) > 0 UNION ALL ' ||
250 ' SELECT 3 task_type, count(min(mcce.cycle_count_entry_id)) task_count FROM ' || p_pcy_from ||
251 ' WHERE ' || p_pcy_where || --' and count(rownum) > 0 UNION ALL ' ||
252 ' UNION ALL SELECT 3 task_type, count(*) task_count FROM ' || p_ccy_From ||
253 ' WHERE ' || p_ccy_where || ' HAVING count(rownum) > 0 ';
254 ELSIF p_cq_type = 2 THEN
255 --query cc tasks alone
256 l_CursorStmt := ' SELECT 3 task_type, count(*) task_count FROM ' || p_acy_from ||
257 ' WHERE ' || p_acy_where || ' AND wdt.status IN (2,3,4,5) HAVING count(rownum) > 0 UNION ALL ' ||
258 ' SELECT 3 task_type, count(min(mcce.cycle_count_entry_id)) task_count FROM ' || p_pcy_from ||
259 ' WHERE ' || p_pcy_where || --' and count(rownum) > 0 UNION ALL ' ||
260 ' UNION ALL SELECT 3 task_type, count(*) task_count FROM ' || p_ccy_From ||
261 ' WHERE ' || p_ccy_where || ' HAVING count(rownum) > 0 ';
262 END IF;
263
264 -- Open a cursor for processing.
265 l_CursorID := DBMS_SQL.OPEN_CURSOR;
266
267 -- Parse the query
268 DBMS_SQL.PARSE(l_CursorID, l_CursorStmt, DBMS_SQL.V7);
269
270 -- Define the output variables
271 DBMS_SQL.DEFINE_COLUMN(l_CursorID, 1, l_temp_task);
272 DBMS_SQL.DEFINE_COLUMN(l_CursorID, 2, l_temp_count);
273
274 -- Execute the statement. We don't care about the return value,
275 -- but we do need to declare a variable for it.
276 l_Dummy := DBMS_SQL.EXECUTE(l_CursorID);
277
278 -- The dynamic sql cursor statement will return task types and count but is
279 -- not grouped together based on the task_type. This part will deal with
280 -- doing that while putting the aggregate count information in a table with
281 -- the index being the task_type code. 7 refers to a NULL value for task_type
282 LOOP
283 -- Fetch the rows into the buffer, and also check for the exit
284 -- condition from the loop.
285 IF DBMS_SQL.FETCH_ROWS(l_CursorID) = 0 THEN
286 EXIT;
287 END IF;
288
289 -- Retrieve the rows from the buffer into temp variables.
290 DBMS_SQL.COLUMN_VALUE(l_CursorID, 1, l_temp_task);
291 DBMS_SQL.COLUMN_VALUE(l_CursorID, 2, l_temp_count);
292
293 -- Store these values in the task count table
294 -- Changed for supporting new task types 5, 6
295 IF (l_task_count_table.EXISTS(NVL(l_temp_task, 7))) THEN
296 l_task_count_table(NVL(l_temp_task, 7)) := l_task_count_table(NVL(l_temp_task, 7)) + l_temp_count;
297 ELSE
298 l_task_count_table(NVL(l_temp_task, 7)) := l_temp_count;
299 END IF;
300
301
302 END LOOP;
303
304 -- Close the cursor.
305 DBMS_SQL.CLOSE_CURSOR(l_CursorID);
306
307 -- Input in the task type and count information into the chart table
308 x_type_data_count := 0;
309 l_loop_index := l_task_count_table.FIRST;
310 -- Populate the chart only if there are records returned by the cursor c_type
311 IF (l_loop_index IS NOT NULL) THEN
312 LOOP
313 x_type_data_count := x_type_data_count + 1;
314 -- Changed for supporting new task types
315
316 IF (l_loop_index = 7) THEN
317 x_type_chart_data(x_type_data_count).TYPE := 'Other';
318 ELSE
319 -- Get the lookup value for the task_type from the table
320 x_type_chart_data(x_type_data_count).TYPE := l_task_type_table(l_loop_index);
321 END IF;
322 -- Store the count value associated with the task type
323 x_type_chart_data(x_type_data_count).task_count := l_task_count_table(l_loop_index);
324 EXIT WHEN l_loop_index = l_task_count_table.LAST;
325 l_loop_index := l_task_count_table.NEXT(l_loop_index);
326 END LOOP;
327 END IF;
328
329 x_return_status := fnd_api.g_ret_sts_success;
330
331 EXCEPTION
332 WHEN FND_API.G_EXC_ERROR THEN
333 x_return_status := FND_API.G_RET_STS_ERROR;
334 -- Close the cursor.
335 DBMS_SQL.CLOSE_CURSOR(l_CursorID);
336
337 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
338 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
339 -- Close the cursor.
340 DBMS_SQL.CLOSE_CURSOR(l_CursorID);
341
342 WHEN OTHERS THEN
343 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
344 IF (FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
345 FND_MSG_PUB.ADD_EXC_MSG(g_pkg_name, c_api_name);
346 END IF;
347 -- Close the cursor.
348 DBMS_SQL.CLOSE_CURSOR(l_CursorID);
349
350 END get_type_dist;
351
352
353 ----------------------------------------------------
354 -- Valid status changes are:
355 -- Pending(1) Queued(2) Dispatched(3) Loaded(4)
356 -- Error(5) Completed(6) Hold(7)
357 -- Vertical axis is from_status
358 -- Horizontal axis is to_status
359 -- P(1) Q(2) D(3) L(4) E(5) C(6) H(7)
360 -- P(1) x x x
361 -- Q(2) x x x
362 -- D(3) x x x x
363 -- L(4) x
364 -- E(5) x x x x
365 -- C(6) x
366 -- H(7) x
367 ----------------------------------------------------
368 FUNCTION is_status_valid(
369 p_from_status IN NUMBER
370 , p_to_status IN NUMBER ) RETURN VARCHAR2 IS
371
372 l_is_valid VARCHAR2(2);
373
374 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
375 BEGIN
376 l_is_valid := 'N';
377
378 IF p_from_status = 1 THEN
379 IF p_to_status in (1,2,3) THEN
380 l_is_valid := 'Y';
381 END IF;
382 ELSIF p_from_status = 2 THEN
383 IF p_to_status in (1,2,3) THEN
384 l_is_valid := 'Y';
385 END IF;
386 ELSIF p_from_status = 3 THEN
387 IF p_to_status in (1,2,3,4) THEN
388 l_is_valid := 'Y';
389 END IF;
390 ELSIF p_from_status = 4 THEN
391 IF p_to_status in (4) THEN
392 l_is_valid := 'Y';
393 END IF;
394 ELSIF p_from_status = 5 THEN
395 IF p_to_status in (1,2,3,5) THEN
396 l_is_valid := 'Y';
397 END IF;
398 ELSIF p_from_status = 6 THEN
399 IF p_to_status in (6) THEN
400 l_is_valid := 'Y';
401 END IF;
402 ELSIF p_from_status = 7 THEN
403 IF p_to_status in (7) THEN
404 l_is_valid := 'Y';
405 END IF;
406 END IF;
407
408 return l_is_valid;
409
410 END is_status_valid;
411
412
413
414 /**************************************
415 *
416 **************************************/
417 /*
418 PROCEDURE lock_row(
419 p_rowid IN OUT NOCOPY VARCHAR2
420 , p_transaction_temp_id IN NUMBER
421 , p_task_id IN NUMBER
422 , p_status IN NUMBER
423 , p_priority IN NUMBER
424 , p_person_id IN NUMBER
425 , p_person_resource_id IN NUMBER
426 , p_transaction_source_type_id IN NUMBER NULL --kkoothan
427 ) IS
428 CURSOR C_mcce IS SELECT --kkoothan
429 cycle_count_entry_id
430 , 1 -- status
431 , task_priority
432 FROM MTL_CYCLE_COUNT_ENTRIES
433 WHERE cycle_count_entry_id = p_transaction_temp_id
434 FOR UPDATE OF entry_status_code, task_priority NOWAIT;
435
436 CURSOR C_mmtt IS SELECT
437 transaction_temp_id
438 , wms_task_status
439 , task_priority
440 FROM MTL_MATERIAL_TRANSACTIONS_TEMP
441 WHERE transaction_temp_id = p_transaction_temp_id
442 FOR UPDATE OF wms_task_status, task_priority NOWAIT;
443
444 CURSOR C_wdt IS SELECT
445 transaction_temp_id
446 , task_id
447 , status
448 , priority
449 , person_id
450 , person_resource_id
451 FROM WMS_DISPATCHED_TASKS
452 WHERE transaction_temp_id = p_transaction_temp_id
453 AND task_id = p_task_id
454 FOR UPDATE OF status, priority, person_id, person_resource_id NOWAIT;
455
456 recinfo_mcce C_mcce%ROWTYPE; --kkoothan
457 recinfo_mmtt C_mmtt%ROWTYPE;
458 recinfo_wdt C_wdt%ROWTYPE;
459
460 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
461 BEGIN
462 IF p_transaction_source_type_id = 9 THEN -- kkoothan
463 -- cycle count Task
464
465 BEGIN
466 OPEN C_mcce;
467 FETCH C_mcce INTO recinfo_mcce;
468 IF (c_mcce%notfound) THEN
469 CLOSE C_mcce;
470 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
471 app_exception.raise_exception;
472 END IF;
473 CLOSE C_mcce;
474 EXCEPTION
475 WHEN OTHERS THEN
476 IF SQLCODE = -54 THEN --record locked by other session
477 fnd_message.set_name('WMS', 'WMS_RECORD_BEING_UPDATED');
478 app_exception.raise_exception;
479 END IF;
480 END;
481 ELSE
482 BEGIN
483 OPEN C_mmtt;
484 FETCH C_mmtt INTO recinfo_mmtt;
485 IF (c_mmtt%notfound) THEN
486 CLOSE C_mmtt;
487 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
488 app_exception.raise_exception;
489 END IF;
490 CLOSE C_mmtt;
491 EXCEPTION
492 WHEN OTHERS THEN
493 IF SQLCODE = -54 THEN --record locked by other session
494 fnd_message.set_name('WMS', 'WMS_RECORD_BEING_UPDATED');
495 app_exception.raise_exception;
496 END IF;
497 END;
498 END IF;
499
500 IF (p_transaction_temp_id IN
501 (recinfo_mmtt.transaction_temp_id,recinfo_mcce.cycle_count_entry_id)
502 ) THEN -- kkoothan
503 NULL;
504 ELSE
505 fnd_message.set_name('FND','FORM_RECORD_CHANGED');
506 app_exception.raise_exception;
507 END IF;
508
509 IF p_status =1 THEN
510 -- Pending task, no need to lock wdt
511 RETURN;
512 END IF;
513 BEGIN
514 OPEN C_wdt;
515 FETCH C_wdt INTO recinfo_wdt;
516 IF (c_wdt%notfound) THEN
517 CLOSE C_wdt;
518 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
519 app_exception.raise_exception;
520 END IF;
521 CLOSE C_wdt;
522 EXCEPTION
523 WHEN OTHERS THEN
524 IF SQLCODE = -54 THEN --record locked by other session
525 fnd_message.set_name('WMS', 'WMS_RECORD_BEING_UPDATED');
526 app_exception.raise_exception;
527 END IF;
528 END;
529
530 IF( (recinfo_wdt.transaction_temp_id = p_transaction_temp_id)
531 AND (recinfo_wdt.task_id = p_task_id)
532 ) THEN
533 NULL;
534 ELSE
535 fnd_message.set_name('FND','FORM_RECORD_CHANGED');
536 app_exception.raise_exception;
537 END IF;
538
539 END;
540 */
541
542 --Modified one
543 PROCEDURE lock_row(
544 p_rowid IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
545 , p_transaction_temp_id IN NUMBER
546 , p_task_id IN NUMBER
547 , p_status IN NUMBER
548 , p_priority IN NUMBER
549 , p_person_id IN NUMBER
550 , p_person_resource_id IN NUMBER
551 , p_transaction_source_type_id IN NUMBER --kkoothan
552 ) IS
553
554 MMTT_lock_name VARCHAR2(50):= To_char(p_transaction_temp_id);
555 lock_result NUMBER;
556 l_lock_id VARCHAR2(50);
557 l_return_value NUMBER;
558
559 CURSOR C_mcce IS SELECT --kkoothan
560 cycle_count_entry_id
561 , 1 -- status
562 , task_priority
563 FROM MTL_CYCLE_COUNT_ENTRIES
564 WHERE cycle_count_entry_id = p_transaction_temp_id
565 FOR UPDATE OF entry_status_code, task_priority NOWAIT;
566
567 recinfo_mcce C_mcce%ROWTYPE; --kkoothan
568
569 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
570 BEGIN
571 IF (l_debug = 1) THEN
572 mydebug('Inside lock_row p_transaction_temp_id:'||p_transaction_temp_id);
573 END IF;
574
575 IF p_transaction_source_type_id = 9 THEN -- kkoothan
576 -- cycle count Task
577
578 BEGIN
579 OPEN C_mcce;
580 FETCH C_mcce INTO recinfo_mcce;
581 IF (c_mcce%notfound) THEN
582 CLOSE C_mcce;
583 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
584 app_exception.raise_exception;
585 END IF;
586 CLOSE C_mcce;
587 EXCEPTION
588 WHEN OTHERS THEN
589 IF SQLCODE = -54 THEN --record locked by other session
590 fnd_message.set_name('WMS', 'WMS_RECORD_BEING_UPDATED');
591 app_exception.raise_exception;
592 END IF;
593 END;
594 ELSE
595 IF (l_debug = 1) THEN
596 mydebug('for MMTT');
597 END IF;
598 BEGIN
599 dbms_lock.allocate_unique
600 (lockname => MMTT_lock_name,
601 lockhandle => l_lock_id);
602
603 l_return_value := dbms_lock.request --EXCLUSIVE LOCK
604 (lockhandle => l_lock_id,
605 lockmode => 6,
606 timeout => 1,
607 release_on_commit => TRUE);
608
609 IF (l_debug = 1) THEN
610 mydebug('dbms_lock:l_return_value:'||l_return_value);
611 END IF;
612
613 IF l_return_value IN (1,2) THEN
614 fnd_message.set_name('WMS', 'WMS_RECORD_BEING_UPDATED');
615 app_exception.raise_exception;
616 END IF;
617
618 EXCEPTION
619 WHEN OTHERS THEN
620 IF ((SQLCODE = -54) OR l_return_value IN (1,2)) THEN --record locked by other session
621 IF (l_debug = 1) THEN
622 mydebug('INSIDE EXCEPTION');
623 END IF;
624 fnd_message.set_name('WMS', 'WMS_RECORD_BEING_UPDATED');
625 app_exception.raise_exception;
626
627 END IF;
628 END;
629
630 END IF;
631
632 END;
633
634
635 /***********************************
636 * created by kkoothan to handle cycle count Tasks
637 ***********************************/
638 PROCEDURE update_mcce(
639 p_cycle_count_entry_id IN NUMBER
640 ,p_priority IN NUMBER
641 ,p_updated_by IN NUMBER
642 ,p_user_task_type IN NUMBER
643 ,p_last_update_date IN DATE /* Bug 2372652 */
644 ) IS
645
646 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
647 BEGIN
648
649 UPDATE MTL_CYCLE_COUNT_ENTRIES
650 SET
651 task_priority = p_priority
652 , last_updated_by = p_updated_by
653 , last_update_date= p_last_update_date /* Bug 2372652 */
654 , standard_operation_id = p_user_task_type
655 WHERE cycle_count_entry_id = p_cycle_count_entry_id;
656 commit;
657 EXCEPTION
658 WHEN no_data_found THEN
659 null;
660
661 END update_mcce;
662
663 /***********************************
664 *
665 ***********************************/
666
667 PROCEDURE update_mmtt(
668 p_transaction_temp_id IN NUMBER
669 ,p_priority IN NUMBER
670 ,p_from_status IN NUMBER
671 ,p_to_status IN NUMBER
672 ,p_updated_by IN NUMBER
673 ,p_user_task_type IN NUMBER
674 ,p_task_type IN NUMBER
675 ) IS
676
677 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
678 BEGIN
679 --dbms_output.put_line('in update_mmtt '|| p_transaction_temp_id || ':' || p_priority || ':' ||p_from_status || ':' ||p_to_status);
680
681 UPDATE mtl_material_transactions_temp
682 SET
683 task_priority = p_priority
684 , wms_task_status = decode(p_to_status, NULL, p_from_status, p_to_status)
685 , last_updated_by = p_updated_by
686 , last_update_date= SYSDATE
687 , standard_operation_id = p_user_task_type
688 , wms_task_type = p_task_type
689 WHERE transaction_temp_id = p_transaction_temp_id;
690 commit;
691 --dbms_output.put_line('did update_mmtt ');
692 EXCEPTION
693 WHEN no_data_found THEN
694 null;
695 --dbms_output.put_line('not found in mmtt');
696
697 END update_mmtt;
698
699 /***********************************
700 ***********************************/
701 PROCEDURE update_wdt(
702 p_transaction_temp_id IN NUMBER
703 ,p_task_id IN NUMBER
704 ,p_priority IN NUMBER
705 ,p_from_status IN NUMBER
706 ,p_to_status IN NUMBER
707 ,p_person_id IN NUMBER
708 ,p_person_resource_id IN NUMBER
709 ,p_updated_by IN NUMBER
710 ,p_user_task_type IN NUMBER
711 ,p_task_type IN NUMBER
712 ) IS
713 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
714 BEGIN
715 --dbms_output.put_line('in update wdt');
716 UPDATE wms_dispatched_tasks
717 SET
718 person_resource_id = p_person_resource_id
719 , person_id = p_person_id
720 , priority = p_priority
721 , status = p_to_status
722 , last_updated_by = p_updated_by
723 , last_update_date = SYSDATE
724 , user_task_type = p_user_task_type
725 , task_type = p_task_type
726 WHERE transaction_temp_id = p_transaction_temp_id
727 AND task_id = nvl(p_task_id, task_id);
728
729 END update_wdt;
730
731
732 /**************************************
733 *
734 **************************************/
735 PROCEDURE insert_to_wdt(
736 p_transaction_temp_id IN NUMBER
737 ,p_status IN NUMBER
738 ,p_person_id IN NUMBER
739 ,p_person_resource_id IN NUMBER
740 ,p_updated_by IN NUMBER
741 ,p_transaction_source_type_id IN NUMBER --kkoothan
742 ,x_task_id OUT NOCOPY /* file.sql.39 change */ NUMBER
743 ,x_priority IN NUMBER
744
745 ) IS
746
747 l_org_id NUMBER;
748 l_user_task_type NUMBER;
749 l_wms_task_type NUMBER;
750 l_next_task_id NUMBER;
751 l_operation_plan_id NUMBER;
752 l_move_order_line_id NUMBER;
753
754 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
755 BEGIN
756
757 --Get value from sequence for next task id
758 SELECT wms_dispatched_tasks_s.NEXTVAL
759 INTO l_next_task_id
760 FROM dual ;
761
762 -- Obtain org_id, user_task_type and task_type
763 IF p_transaction_source_type_id =9 THEN --kkoothan
764 -- cycle count
765 SELECT organization_id, standard_operation_id, 3 --task type
766 INTO l_org_id, l_user_task_type, l_wms_task_type
767 FROM mtl_cycle_count_entries
768 WHERE cycle_count_entry_id = p_transaction_temp_id;
769 ELSE
770 SELECT organization_id, standard_operation_id, wms_task_type, operation_plan_id, move_order_line_id
771 INTO l_org_id, l_user_task_type, l_wms_task_type, l_operation_plan_id, l_move_order_line_id
772 FROM mtl_material_transactions_temp
773 WHERE transaction_temp_id = p_transaction_temp_id;
774 END IF;
775
776 --dbms_output.put_line('Before Insert into WMSDT');
777
778
779 INSERT INTO WMS_DISPATCHED_TASKS
780 ( TASK_ID
781 , TRANSACTION_TEMP_ID
782 , ORGANIZATION_ID
783 , USER_TASK_TYPE
784 , PERSON_ID
785 , EFFECTIVE_START_DATE
786 , EFFECTIVE_END_DATE
787 , PERSON_RESOURCE_ID
788 , STATUS
789 , DISPATCHED_TIME
790 , LAST_UPDATE_DATE
791 , LAST_UPDATED_BY
792 , CREATION_DATE
793 , CREATED_BY
794 , task_type
795 , priority
796 , operation_plan_id
797 , move_order_line_id )
798
799 VALUES( l_next_task_id
800 , p_transaction_temp_id
801 , l_org_id
802 , Nvl(l_user_task_type,2)
803 , p_person_id
804 , sysdate
805 , sysdate
806 , p_person_resource_id
807 , p_status
808 , sysdate
809 , sysdate
810 , p_updated_by
811 , sysdate
812 , p_updated_by
813 , l_wms_task_type
814 , x_priority
815 , l_operation_plan_id
816 , l_move_order_line_id);
817 x_task_id := l_next_task_id;
818 END insert_to_wdt;
819
820 /************************************
821 ************************************/
822 PROCEDURE delete_from_wdt(
823 p_transaction_temp_id IN NUMBER , p_task_id IN NUMBER) IS
824 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
825 BEGIN
826 DELETE FROM wms_dispatched_tasks
827 WHERE transaction_temp_id = p_transaction_temp_id
828 AND task_id = nvl(p_task_id, task_id);
829
830 END delete_from_wdt;
831
832
833
834 -- Procedure definition of task manipulation
835 -- Task status lookup
836 -- 1 : Pending
837 -- 2: Queued
838 -- 3: Dispatched
839 -- 4: Loaded
840 -- 5: Error
841 -- 6: Completed
842
843 PROCEDURE task_manipulator(
844 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
845 ,x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
846 ,x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
847 ,x_task_id OUT NOCOPY /* file.sql.39 change */ NUMBER
848 ,p_updated_by IN NUMBER
849 ,p_task_id IN NUMBER
850 ,p_transaction_temp_id IN NUMBER
851 ,p_organization_id IN NUMBER
852 ,p_person_resource_id IN NUMBER
853 ,p_person_id IN NUMBER
854 ,p_priority IN NUMBER
855 ,p_from_status IN NUMBER
856 ,p_to_status IN NUMBER
857 ,p_user_task_type IN NUMBER
858 ,p_task_type IN NUMBER
859 ,p_transaction_source_type_id IN NUMBER -- kkoothan
860 ,p_last_update_date IN DATE /* Bug 2372652 */
861
862 ) IS
863 -- Bug# 1728558, added p_user_task_type parameter in task_manipulator,
864 -- update_mmtt, update_wdt
865
866 c_api_name CONSTANT VARCHAR2(20) := 'task_manipulator';
867
868 from_status NUMBER := p_from_status;
869 to_status NUMBER := p_to_status;
870 from_status_code VARCHAR2(10) ;
871 to_status_code VARCHAR2(10) ;
872
873
874 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
875 BEGIN
876 x_return_status := fnd_api.g_ret_sts_success;
877 x_task_id := p_task_id;
878
879 --dbms_output.put_line('in task_mani');
880
881 /*select mlk1.meaning, mlk2.meaning
882 into from_status_code, to_status_code
883 from mfg_lookups mlk1, mfg_lookups mlk2
884 where mlk1.lookup_type = 'WMS_TASK_TYPES'
885 and mlk2.lookup_type = 'WMS_TASK_TYPES'
886 and mlk1.lookup_code = from_status
887 and mlk2.lookup_code = to_status;*/
888
889 -- Check whether the change of status is valid
890 --dbms_output.put_line('in validate' || from_status || ':' || to_status || ':' || is_status_valid(from_status, to_status));
891
892 IF is_status_valid(from_status, to_status) <> 'Y' THEN
893 fnd_message.set_name('WMS', 'WMS_TASK_STATUS_CHG_NOTALLOWED');
894 fnd_message.set_token('FROM_STATUS', from_status_code);
895 fnd_message.set_token('TO_STATUS', to_status_code);
896 fnd_msg_pub.add;
897 RAISE fnd_api.g_exc_error;
898 END IF;
899
900 --dbms_output.put_line('pass validate');
901
902 IF from_status = to_status THEN
903 -- Status doesn't change
904 IF from_status = 1 THEN
905 --'PENDING'
906 -- Update mmtt/mcce
907 IF p_transaction_source_type_id = 9 THEN -- kkoothan
908 -- cycle count
909 update_mcce(p_transaction_temp_id
910 ,p_priority
911 ,p_updated_by
912 ,p_user_task_type
913 ,p_last_update_date /* Bug 2372652 */
914 );
915 ELSE
916 update_mmtt(p_transaction_temp_id
917 ,p_priority
918 ,from_status
919 ,from_status
920 ,p_updated_by
921 ,p_user_task_type
922 ,p_task_type);
923 END IF;
924
925 ELSIF from_status in (2, 3,4) THEN
926 -- queued, dispatched
927 -- update mmtt/mcce and wdt
928 --dbms_output.put_line('calling update_mmtt ');
929 IF p_transaction_source_type_id = 9 THEN -- kkoothan
930 -- cycle count
931 update_mcce(p_transaction_temp_id
932 ,p_priority
933 ,p_updated_by
934 ,p_user_task_type
935 ,p_last_update_date /* Bug 2372652 */
936 );
937 ELSE
938 update_mmtt(p_transaction_temp_id
939 ,p_priority
940 ,from_status
941 ,from_status
942 ,p_updated_by
943 ,p_user_task_type
944 ,p_task_type);
945 END IF;
946
947 update_wdt(p_transaction_temp_id
948 , p_task_id
949 , p_priority
950 , from_status
951 , to_status
952 , p_person_id
953 , p_person_resource_id
954 , p_updated_by
955 , p_user_task_type
956 , p_task_type);
957 END IF;
958 ELSE
959 -- Status are changed... manipulate more than one tables
960 IF from_status = 1 THEN -- Pending
961 IF to_status in (2,3) THEN --'QUEUED', Dispatched
962 IF p_transaction_source_type_id = 9 THEN -- kkoothan
963 -- cycle count
964 update_mcce(p_transaction_temp_id
965 ,p_priority
966 ,p_updated_by
967 ,p_user_task_type
968 ,p_last_update_date /* Bug 2372652 */
969 );
970 ELSE
971 update_mmtt(p_transaction_temp_id
972 ,p_priority
973 ,from_status
974 ,from_status
975 ,p_updated_by
976 ,p_user_task_type
977 ,p_task_type);
978 END IF;
979 insert_to_wdt(p_transaction_temp_id
980 , to_status
981 , p_person_id
982 , p_person_resource_id
983 , p_updated_by
984 , p_transaction_source_type_id -- kkoothan
985 , x_task_id
986 , p_priority);
987 END IF;
988 ELSIF from_status = 2 THEN --'QUEUED'
989 IF to_status = 1 THEN --'PENDING'
990 IF p_transaction_source_type_id = 9 THEN -- kkoothan
991 -- cycle count
992 update_mcce(p_transaction_temp_id
993 ,p_priority
994 ,p_updated_by
995 ,p_user_task_type
996 ,p_last_update_date /* Bug 2372652 */
997 );
998 ELSE
999 update_mmtt(p_transaction_temp_id
1000 ,p_priority
1001 ,from_status
1002 ,from_status
1003 ,p_updated_by
1004 ,p_user_task_type
1005 ,p_task_type);
1006 END IF;
1007 delete_from_wdt(p_transaction_temp_id
1008 , p_task_id);
1009
1010 x_task_id := null;
1011 ELSIF to_status IN (3) THEN
1012 --'DISPATCHED'
1013 update_wdt(p_transaction_temp_id
1014 , p_task_id
1015 , p_priority
1016 , from_status
1017 , to_status
1018 , p_person_id
1019 , p_person_resource_id
1020 , p_updated_by
1021 , p_user_task_type
1022 , p_task_type);
1023
1024 END IF;
1025 ELSIF from_status = 3 THEN --'DISPATCHED'
1026 IF to_status = 1 THEN --'Pending'
1027 IF p_transaction_source_type_id = 9 THEN -- kkoothan
1028 -- cycle count
1029 update_mcce(p_transaction_temp_id
1030 ,p_priority
1031 ,p_updated_by
1032 ,p_user_task_type
1033 ,p_last_update_date /* Bug 2372652 */
1034 );
1035 ELSE
1036 update_mmtt(p_transaction_temp_id
1037 ,p_priority
1038 ,from_status
1039 ,from_status
1040 ,p_updated_by
1041 ,p_user_task_type
1042 ,p_task_type);
1043 END IF;
1044 delete_from_wdt(p_transaction_temp_id
1045 , p_task_id);
1046
1047 x_task_id := null;
1048
1049 ELSIF to_status in (2,4) THEN --'Queued', 'LOADED'
1050 IF p_transaction_source_type_id = 9 THEN -- kkoothan
1051 -- cycle count
1052 update_mcce(p_transaction_temp_id
1053 ,p_priority
1054 ,p_updated_by
1055 ,p_user_task_type
1056 ,p_last_update_date /* Bug 2372652 */
1057 );
1058 ELSE
1059 update_mmtt(p_transaction_temp_id
1060 ,p_priority
1061 ,from_status
1062 ,from_status
1063 ,p_updated_by
1064 ,p_user_task_type
1065 ,p_task_type);
1066 END IF;
1067 update_wdt(p_transaction_temp_id
1068 , p_task_id
1069 , p_priority
1070 , from_status
1071 , to_status
1072 , p_person_id
1073 , p_person_resource_id
1074 , p_updated_by
1075 , p_user_task_type
1076 , p_task_type);
1077 END IF;
1078 ELSIF from_status = 5 THEN --'ERROR'
1079 IF to_status=1 THEN --'PENDING'
1080 IF p_transaction_source_type_id = 9 THEN -- kkoothan
1081 -- cycle count
1082 update_mcce(p_transaction_temp_id
1083 ,p_priority
1084 ,p_updated_by
1085 ,p_user_task_type
1086 ,p_last_update_date /* Bug 2372652 */
1087 );
1088 ELSE
1089 update_mmtt(p_transaction_temp_id
1090 ,p_priority
1091 ,from_status
1092 ,from_status
1093 ,p_updated_by
1094 ,p_user_task_type
1095 ,p_task_type);
1096 END IF;
1097 delete_from_wdt(p_transaction_temp_id, p_task_id);
1098 x_task_id := null;
1099 ELSIF to_status IN (2, 3) THEN --'QUEUED', 'DISPATCHED'
1100 IF p_transaction_source_type_id = 9 THEN -- kkoothan
1101 -- cycle count
1102 update_mcce(p_transaction_temp_id
1103 ,p_priority
1104 ,p_updated_by
1105 ,p_user_task_type
1106 ,p_last_update_date /* Bug 2372652 */
1107 );
1108 ELSE
1109 update_mmtt(p_transaction_temp_id
1110 ,p_priority
1111 ,from_status
1112 ,from_status
1113 ,p_updated_by
1114 ,p_user_task_type
1115 ,p_task_type);
1116 END IF;
1117 update_wdt(p_transaction_temp_id
1118 , p_task_id
1119 , p_priority
1120 , from_status
1121 , to_status
1122 , p_person_id
1123 , p_person_resource_id
1124 , p_updated_by
1125 , p_user_task_type
1126 , p_task_type);
1127 END IF;
1128 END IF;
1129 END IF;
1130
1131 EXCEPTION
1132 WHEN fnd_api.g_exc_error THEN
1133 x_return_status := fnd_api.g_ret_sts_error ;
1134 inv_rsv_trigger_global.g_from_trigger := FALSE;
1135
1136 WHEN fnd_api.g_exc_unexpected_error THEN
1137 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1138 inv_rsv_trigger_global.g_from_trigger := FALSE;
1139
1140 WHEN OTHERS THEN
1141 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1142 inv_rsv_trigger_global.g_from_trigger := FALSE;
1143
1144 IF (fnd_msg_pub.check_msg_level
1145 (fnd_msg_pub.g_msg_lvl_unexp_error)) THEN
1146 fnd_msg_pub.add_exc_msg(g_pkg_name, c_api_name);
1147 END IF;
1148
1149
1150 END task_manipulator;
1151
1152 PROCEDURE mydebug(msg in varchar2)
1153 IS
1154 l_msg VARCHAR2(5100);
1155 l_ts VARCHAR2(30);
1156 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1157 BEGIN
1158 -- select to_char(sysdate,'MM/DD/YYYY HH:MM:SS') INTO l_ts from dual;
1159 -- l_msg:=l_ts||' '||msg;
1160
1161 l_msg := msg;
1162
1163 inv_mobile_helper_functions.tracelog
1164 (p_err_msg => l_msg,
1165 p_module => 'WMS_CONTROL_BOARD',
1166 p_level => 4);
1167
1168 END;
1169
1170
1171 END WMS_CONTROL_BOARD;
[Home] [Help]