[Home] [Help]
PACKAGE BODY: APPS.WSH_TRXLOTS_HANDLER
Source
1 PACKAGE BODY WSH_TRXLOTS_HANDLER AS
2 /* $Header: WSHIIXLB.pls 120.0 2005/05/26 18:00:49 appldev noship $ */
3
4 --
5 -- PACKAGE VARIABLES
6 --
7
8 g_userid NUMBER;
9
10 -- ===========================================================================
11 --
12 -- Name:
13 --
14 -- insert_row
15 --
16 -- Description:
17 --
18 -- Called by the client to insert a row into the
19 -- MTL_TRANSACTION_LOTS_INTERFACE table.
20 --
21 -- ===========================================================================
22
23 --
24 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_TRXLOTS_HANDLER';
25 --
26
27 --HVOP heali
28 PROCEDURE INSERT_ROW_BULK (
29 p_mtl_lot_txn_if_rec IN WSH_SHIP_CONFIRM_ACTIONS.mtl_lot_txn_if_rec_type,
30 x_return_status OUT NOCOPY VARCHAR2) IS
31
32 l_debug_on BOOLEAN;
33 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_ROW_BULK';
34
35 l_start_index NUMBER ;
36 l_end_index NUMBER ;
37
38 BEGIN
39 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
40
41 IF l_debug_on IS NULL THEN
42 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
43 END IF;
44
45 IF l_debug_on THEN
46 WSH_DEBUG_SV.push(l_module_name);
47 WSH_DEBUG_SV.log(l_module_name,'p_mtl_lot_txn_if_rec.count',p_mtl_lot_txn_if_rec.source_line_id.count);
48 END IF;
49
50 x_return_status:=WSH_UTIL_CORE.G_RET_STS_SUCCESS;
51
52 l_start_index := p_mtl_lot_txn_if_rec.source_line_id.first;
53 l_end_index := p_mtl_lot_txn_if_rec.source_line_id.last;
54
55 fnd_profile.get('USER_ID',g_userid);
56
57 IF l_debug_on THEN
58 WSH_DEBUG_SV.log(l_module_name,'g_userid',g_userid);
59 WSH_DEBUG_SV.log(l_module_name,'l_start_index',l_start_index);
60 WSH_DEBUG_SV.log(l_module_name,'l_end_index',l_end_index);
61 END IF;
62
63
64 FORALL i IN l_start_index..l_end_index
65 INSERT INTO mtl_transaction_lots_interface (
66 source_code,
67 source_line_id,
68 transaction_interface_id,
69 lot_number,
70 transaction_quantity,
71 serial_transaction_temp_id,
72 creation_date,
73 created_by,
74 last_update_date,
75 last_updated_by,
76 -- HW OPMCONV. Added secondary_qty and grade
77 SECONDARY_TRANSACTION_QUANTITY,
78 GRADE_CODE
79 ) VALUES (
80 p_mtl_lot_txn_if_rec.source_code(i),
81 p_mtl_lot_txn_if_rec.source_line_id(i),
82 p_mtl_lot_txn_if_rec.transaction_interface_id(i),
83 p_mtl_lot_txn_if_rec.lot_number(i),
84 p_mtl_lot_txn_if_rec.trx_quantity(i),
85 p_mtl_lot_txn_if_rec.serial_transaction_temp_id(i),
86 SYSDATE,
87 g_userid,
88 SYSDATE,
89 g_userid,
90 -- HW OPMCONV. Added secondary_qty and grade
91 p_mtl_lot_txn_if_rec.secondary_trx_quantity(i),
92 p_mtl_lot_txn_if_rec.grade_code(i)
93 );
94
95 IF l_debug_on THEN
96 WSH_DEBUG_SV.log(l_module_name,'Rows inserted in mtl_transaction_lots_interface',SQL%ROWCOUNT);
97 END IF;
98
99 IF l_debug_on THEN
100 WSH_DEBUG_SV.pop(l_module_name);
101 END IF;
102
103 EXCEPTION
104 WHEN OTHERS THEN
105 x_return_status:= WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
106
107 IF l_debug_on THEN
108 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
109 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
110 END IF;
111
112 END insert_row_bulk;
113 --HVOP heali
114
115 PROCEDURE Insert_Row(
116 x_rowid IN OUT NOCOPY VARCHAR2,
117 x_trx_interface_id IN OUT NOCOPY NUMBER,
118 p_source_code IN VARCHAR2,
119 p_source_line_id IN NUMBER,
120 p_lot_number IN VARCHAR2,
121 p_trx_quantity IN NUMBER,
122 p_last_update_date IN DATE,
123 p_last_updated_by IN NUMBER,
124 p_creation_date IN DATE,
125 p_created_by IN NUMBER,
126 p_serial_trx_id IN NUMBER,
127 p_error_code IN VARCHAR2,
128 p_last_update_login IN NUMBER DEFAULT NULL,
129 p_request_id IN NUMBER DEFAULT NULL,
130 p_program_application_id IN NUMBER DEFAULT NULL,
131 p_program_id IN NUMBER DEFAULT NULL,
132 p_program_update_date IN DATE DEFAULT NULL,
133 p_lot_expiration_date IN DATE DEFAULT NULL,
134 p_primary_quantity IN NUMBER DEFAULT NULL,
135 p_process_flag IN VARCHAR2 DEFAULT 'Y',
136 -- HW OPMCONV. Added p_secondary_trx_quantity
137 -- and p_grade_code
138 p_secondary_trx_quantity IN NUMBER DEFAULT NULL,
139 p_grade_code IN VARCHAR2 DEFAULT NULL)
140 IS
141
142 CURSOR row_id IS
143 SELECT rowid FROM mtl_transaction_lots_interface
144 WHERE transaction_interface_id = x_trx_interface_id
145 AND lot_number = p_lot_number;
146
147 CURSOR get_interface_id IS
148 SELECT mtl_material_transactions_s.nextval
149 FROM sys.dual;
150
151 --
152 l_debug_on BOOLEAN;
153 --
154 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_ROW';
155 --
156 BEGIN
157
158 /* wsh_server_debug.log_event('WSH_TRXLOTS_HANDLER.INSERT_ROW',
159 'START',
160 'Start of procedure INSERT_ROW, input parameters:
161 source_code='||p_source_code||
162 ', source_line_id='||p_source_line_id||
163 ', transaction_interface_id='||x_trx_interface_id||
164 ', lot_number='||p_lot_number||
165 ', transaction_quantity='||p_trx_quantity||
166 ', last_update_date='||p_last_update_date||
167 ', last_updated_by='||p_last_updated_by);
168 wsh_server_debug.debug_message(
169 ', creation_date='||p_creation_date||
170 ', created_by='||p_created_by||
171 ', serial_transaction_temp_id='||p_serial_trx_id||
172 ', error_code='||p_error_code||
173 ', last_update_login='||p_last_update_login||
174 ', request_id='||p_request_id||
175 ', program_application_id='||p_program_application_id||
176 ', program_id='||p_program_id||
177 ', program_update_date='||p_program_update_date||
178 ', lot_expiration_date='||p_lot_expiration_date||
179 ', primary_quantity='||p_primary_quantity||
180 ', process_flag='||p_process_flag );
181 */
182 --
183 -- Debug Statements
184 --
185 --
186 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
187 --
188 IF l_debug_on IS NULL
189 THEN
190 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
191 END IF;
192 --
193 IF l_debug_on THEN
194 WSH_DEBUG_SV.push(l_module_name);
195 --
196 WSH_DEBUG_SV.log(l_module_name,'X_ROWID',X_ROWID);
197 WSH_DEBUG_SV.log(l_module_name,'X_TRX_INTERFACE_ID',X_TRX_INTERFACE_ID);
198 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
199 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_LINE_ID',P_SOURCE_LINE_ID);
200 WSH_DEBUG_SV.log(l_module_name,'P_LOT_NUMBER',P_LOT_NUMBER);
201 WSH_DEBUG_SV.log(l_module_name,'P_TRX_QUANTITY',P_TRX_QUANTITY);
202 WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATE_DATE',P_LAST_UPDATE_DATE);
203 WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATED_BY',P_LAST_UPDATED_BY);
204 WSH_DEBUG_SV.log(l_module_name,'P_CREATION_DATE',P_CREATION_DATE);
205 WSH_DEBUG_SV.log(l_module_name,'P_CREATED_BY',P_CREATED_BY);
206 WSH_DEBUG_SV.log(l_module_name,'P_SERIAL_TRX_ID',P_SERIAL_TRX_ID);
207 WSH_DEBUG_SV.log(l_module_name,'P_ERROR_CODE',P_ERROR_CODE);
208 WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATE_LOGIN',P_LAST_UPDATE_LOGIN);
209 WSH_DEBUG_SV.log(l_module_name,'P_REQUEST_ID',P_REQUEST_ID);
210 WSH_DEBUG_SV.log(l_module_name,'P_PROGRAM_APPLICATION_ID',P_PROGRAM_APPLICATION_ID);
211 WSH_DEBUG_SV.log(l_module_name,'P_PROGRAM_ID',P_PROGRAM_ID);
212 WSH_DEBUG_SV.log(l_module_name,'P_PROGRAM_UPDATE_DATE',P_PROGRAM_UPDATE_DATE);
213 WSH_DEBUG_SV.log(l_module_name,'P_LOT_EXPIRATION_DATE',P_LOT_EXPIRATION_DATE);
214 WSH_DEBUG_SV.log(l_module_name,'P_PRIMARY_QUANTITY',P_PRIMARY_QUANTITY);
215 WSH_DEBUG_SV.log(l_module_name,'P_PROCESS_FLAG',P_PROCESS_FLAG);
216 -- HW OPMCONV. Added debugging msgs
217 WSH_DEBUG_SV.log(l_module_name,'P_SECONDARY_TRX_QUANTITY',P_SECONDARY_TRX_QUANTITY);
218 WSH_DEBUG_SV.log(l_module_name,'P_GRADE_CODE',P_GRADE_CODE);
219 END IF;
220 --
221 fnd_profile.get('USER_ID',g_userid);
222
223 -- Set interface id if necessary
224 IF x_trx_interface_id IS NULL THEN
225 OPEN get_interface_id;
226 FETCH get_interface_id INTO x_trx_interface_id;
227 CLOSE get_interface_id;
228 END IF;
229
230 INSERT INTO mtl_transaction_lots_interface(
231 source_code,
232 source_line_id,
233 transaction_interface_id,
234 lot_number,
235 transaction_quantity,
236 last_update_date,
237 last_updated_by,
238 creation_date,
239 created_by,
240 serial_transaction_temp_id,
241 error_code,
242 last_update_login,
243 request_id,
244 program_application_id,
245 program_id,
246 program_update_date,
247 lot_expiration_date,
248 primary_quantity,
249 process_flag,
250 -- HW OPMCONV. Added secondary_qty and grade
251 SECONDARY_TRANSACTION_QUANTITY,
252 GRADE_CODE
253
254 ) VALUES (
255 p_source_code,
256 p_source_line_id,
257 x_trx_interface_id,
258 p_lot_number,
259 p_trx_quantity,
260 NVL(p_last_update_date,SYSDATE),
261 NVL(p_last_updated_by,g_userid),
262 NVL(p_creation_date,SYSDATE),
263 NVL(p_created_by,g_userid),
264 p_serial_trx_id,
265 p_error_code,
266 p_last_update_login,
267 p_request_id,
268 p_program_application_id,
269 p_program_id,
270 p_program_update_date,
271 p_lot_expiration_date,
272 p_primary_quantity,
273 p_process_flag,
274 -- HW OPMCONV. Added secondary_qty and grade
275 p_secondary_trx_quantity,
276 p_grade_code
277 );
278
279 OPEN row_id;
280
281 FETCH row_id INTO x_rowid;
282
283 IF (row_id%NOTFOUND) then
284 /* wsh_server_debug.log_event('WSH_TRXLOTS_HANDLER.INSERT_ROW',
285 'END',
286 'No rowid found. Raising NO_DATA_FOUND.');
287 */
288 CLOSE row_id;
289 RAISE NO_DATA_FOUND;
290 END IF;
291
292 CLOSE row_id;
293
294 /* wsh_server_debug.log_event('WSH_TRXLOTS_HANDLER.INSERT_ROW',
295 'END',
296 'End of procedure INSERT_ROW');
297 */
298 --
299 -- Debug Statements
300 --
301 IF l_debug_on THEN
302 WSH_DEBUG_SV.pop(l_module_name);
303 END IF;
304 --
305 END Insert_Row;
306
307 -- ===========================================================================
308 --
309 -- Name:
310 --
311 -- update_row
312 --
313 -- Description:
314 --
315 -- Called by the client to update a row in the
316 -- MTL_TRANSACTION_LOTS_INTERFACE table.
317 --
318 -- ===========================================================================
319
320 PROCEDURE Update_Row (
321 x_rowid IN OUT NOCOPY VARCHAR2,
322 p_trx_interface_id IN NUMBER,
323 p_source_code IN VARCHAR2,
324 p_source_line_id IN NUMBER,
325 p_lot_number IN VARCHAR2,
326 p_trx_quantity IN NUMBER,
327 p_last_update_date IN DATE,
328 p_last_updated_by IN NUMBER,
329 p_serial_trx_id IN NUMBER,
330 p_error_code IN VARCHAR2,
331 p_last_update_login IN NUMBER DEFAULT NULL,
332 p_request_id IN NUMBER DEFAULT NULL,
333 p_program_application_id IN NUMBER DEFAULT NULL,
334 p_program_id IN NUMBER DEFAULT NULL,
335 p_program_update_date IN DATE DEFAULT NULL,
336 p_lot_expiration_date IN DATE DEFAULT NULL,
337 p_primary_quantity IN NUMBER DEFAULT NULL,
338 p_process_flag IN VARCHAR2 DEFAULT 'Y',
339 -- HW OPMCONV. Added p_secondary_trx_quantity
340 -- and p_grade_code
341 p_secondary_trx_quantity IN NUMBER DEFAULT NULL,
342 p_grade_code IN VARCHAR2 DEFAULT NULL
343 )
344 IS
345 --
346 l_debug_on BOOLEAN;
347 --
348 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_ROW';
349 --
350 BEGIN
351 /* wsh_server_debug.log_event('WSH_TRXLOTS_HANDLER.UPDATE_ROW',
352 'START',
353 'Start of procedure UPDATE_ROW, input parameters:
354 source_code='||p_source_code||
355 ', source_line_id='||p_source_line_id||
356 ', transaction_interface_id='||p_trx_interface_id||
357 ', lot_number='||p_lot_number||
358 ', transaction_quantity='||p_trx_quantity||
359 ', last_update_date='||p_last_update_date||
360 ', last_updated_by='||p_last_updated_by);
361 wsh_server_debug.debug_message(
362 ', serial_transaction_temp_id='||p_serial_trx_id||
363 ', error_code='||p_error_code||
364 ', last_update_login='||p_last_update_login||
365 ', request_id='||p_request_id||
366 ', program_application_id='||p_program_application_id||
367 ', program_id='||p_program_id||
368 ', program_update_date='||p_program_update_date||
369 ', lot_expiration_date='||p_lot_expiration_date||
370 ', primary_quantity='||p_primary_quantity||
371 ', process_flag='||p_process_flag );
372 */
373
374 --
375 -- Debug Statements
376 --
377 --
378 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
379 --
380 IF l_debug_on IS NULL
381 THEN
382 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
383 END IF;
384 --
385 IF l_debug_on THEN
386 WSH_DEBUG_SV.push(l_module_name);
387 --
388 WSH_DEBUG_SV.log(l_module_name,'X_ROWID',X_ROWID);
389 WSH_DEBUG_SV.log(l_module_name,'P_TRX_INTERFACE_ID',P_TRX_INTERFACE_ID);
390 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
391 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_LINE_ID',P_SOURCE_LINE_ID);
392 WSH_DEBUG_SV.log(l_module_name,'P_LOT_NUMBER',P_LOT_NUMBER);
393 WSH_DEBUG_SV.log(l_module_name,'P_TRX_QUANTITY',P_TRX_QUANTITY);
394 WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATE_DATE',P_LAST_UPDATE_DATE);
395 WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATED_BY',P_LAST_UPDATED_BY);
396 WSH_DEBUG_SV.log(l_module_name,'P_SERIAL_TRX_ID',P_SERIAL_TRX_ID);
397 WSH_DEBUG_SV.log(l_module_name,'P_ERROR_CODE',P_ERROR_CODE);
398 WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATE_LOGIN',P_LAST_UPDATE_LOGIN);
399 WSH_DEBUG_SV.log(l_module_name,'P_REQUEST_ID',P_REQUEST_ID);
400 WSH_DEBUG_SV.log(l_module_name,'P_PROGRAM_APPLICATION_ID',P_PROGRAM_APPLICATION_ID);
401 WSH_DEBUG_SV.log(l_module_name,'P_PROGRAM_ID',P_PROGRAM_ID);
402 WSH_DEBUG_SV.log(l_module_name,'P_PROGRAM_UPDATE_DATE',P_PROGRAM_UPDATE_DATE);
403 WSH_DEBUG_SV.log(l_module_name,'P_LOT_EXPIRATION_DATE',P_LOT_EXPIRATION_DATE);
404 WSH_DEBUG_SV.log(l_module_name,'P_PRIMARY_QUANTITY',P_PRIMARY_QUANTITY);
405 WSH_DEBUG_SV.log(l_module_name,'P_PROCESS_FLAG',P_PROCESS_FLAG);
406 -- HW OPMCONV. Added debugging msgs
407 WSH_DEBUG_SV.log(l_module_name,'P_SECONDARY_TRX_QUANTITY',P_SECONDARY_TRX_QUANTITY);
408 WSH_DEBUG_SV.log(l_module_name,'P_GRADE_CODE',P_GRADE_CODE);
409 END IF;
410 --
411 fnd_profile.get('USER_ID',g_userid);
412
413 UPDATE mtl_transaction_lots_interface SET
414 source_code = p_source_code,
415 source_line_id = p_source_line_id,
416 transaction_interface_id = p_trx_interface_id,
417 lot_number = p_lot_number,
418 transaction_quantity = p_trx_quantity,
419 last_updated_by = NVL(p_last_updated_by,g_userid),
420 last_update_date = NVL(p_last_update_date,SYSDATE),
421 serial_transaction_temp_id = p_serial_trx_id,
422 error_code = p_error_code,
423 last_update_login = p_last_update_login,
424 request_id = p_request_id,
425 program_application_id = p_program_application_id,
426 program_id = p_program_id,
427 program_update_date = p_program_update_date,
428 lot_expiration_date = p_lot_expiration_date,
429 primary_quantity = p_primary_quantity,
430 process_flag = p_process_flag,
431 -- HW OPMCONV. Added secondary_qty and grade
432 SECONDARY_TRANSACTION_QUANTITY = p_secondary_trx_quantity,
433 GRADE_CODE = p_grade_code
434 WHERE rowid = x_rowid;
435
436 IF (SQL%NOTFOUND) THEN
437 /* wsh_server_debug.log_event('WSH_TRXLOTS_HANDLER.UPDATE_ROW',
438 'END',
439 'No rows updated. Raising NO_DATA_FOUND.');
440 */
441 RAISE NO_DATA_FOUND;
442 END IF;
443
444 /* wsh_server_debug.log_event('WSH_TRXLOTS_HANDLER.UPDATE_ROW',
445 'END',
446 'End of procedure UPDATE_ROW');
447 */
448 --
449 -- Debug Statements
450 --
451 IF l_debug_on THEN
452 WSH_DEBUG_SV.pop(l_module_name);
453 END IF;
454 --
455 END Update_Row;
456
457 -- ===========================================================================
458 --
459 -- Name:
460 --
461 -- delete_row
462 --
463 -- Description:
464 --
465 -- Called by the client to delete a row in the
466 -- MTL_TRANSACTION_LOTS_INTERFACE table.
467 --
468 -- ===========================================================================
469
470 PROCEDURE Delete_Row (
471 x_rowid IN OUT NOCOPY VARCHAR2 )
472 IS
473 --
474 l_debug_on BOOLEAN;
475 --
476 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_ROW';
477 --
478 BEGIN
479 /* wsh_server_debug.log_event('WSH_TRXLOTS_HANDLER.DELETE_ROW',
480 'START',
481 'Start of procedure DELETE_ROW');
482
483 wsh_server_debug.log_event('WSH_TRXLOTS_HANDLER.DELETE_ROW',
484 'START',
485 'Deleting from mtl_serial_numbers_interface, if any');
486 */
487
488 --
489 -- Debug Statements
490 --
491 --
492 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
493 --
494 IF l_debug_on IS NULL
495 THEN
496 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
497 END IF;
498 --
499 IF l_debug_on THEN
500 WSH_DEBUG_SV.push(l_module_name);
501 --
502 WSH_DEBUG_SV.log(l_module_name,'X_ROWID',X_ROWID);
503 END IF;
504 --
505 DELETE FROM mtl_serial_numbers_interface
506 WHERE transaction_interface_id IN
507 ( SELECT serial_transaction_temp_id
508 FROM mtl_transaction_lots_interface
509 WHERE rowid = x_rowid);
510
511 /* wsh_server_debug.log_event('WSH_TRXLOTS_HANDLER.DELETE_ROW',
512 'END',
513 'Finish with call to DELETE mtl_serial_numbers_interface');
514
515 wsh_server_debug.log_event('WSH_TRXLOTS_HANDLER.DELETE_ROW',
516 'START',
517 'Delete from mtl_transaction_lots_interface ');
518 */
519 DELETE FROM mtl_transaction_lots_interface WHERE rowid = x_rowid;
520
521 IF (SQL%NOTFOUND) THEN
522 /* wsh_server_debug.log_event('WSH_TRXLOTS_HANDLER.DELETE_ROW',
523 'END',
524 'No rows deleted. Raising NO_DATA_FOUND');
525 */
526 RAISE NO_DATA_FOUND;
527 END IF;
528
529 /* wsh_server_debug.log_event('WSH_TRXLOTS_HANDLER.DELETE_ROW',
530 'END',
531 'End of procedure DELETE_ROW');
532 */
533 --
534 -- Debug Statements
535 --
536 IF l_debug_on THEN
537 WSH_DEBUG_SV.pop(l_module_name);
538 END IF;
539 --
540 END Delete_Row;
541
542 -- ===========================================================================
543 --
544 -- Name:
545 --
546 -- lock_row
547 --
548 -- Description:
549 --
550 -- Called by the client to lock a row in the
551 -- MTL_TRANSACTION_LOTS_INTERFACE table.
552 --
553 -- ===========================================================================
554
555 PROCEDURE Lock_Row (
556 x_rowid IN OUT NOCOPY VARCHAR2,
557 p_source_code IN VARCHAR2,
558 p_source_line_id IN NUMBER,
559 p_trx_interface_id IN NUMBER,
560 p_lot_number IN VARCHAR2,
561 p_trx_quantity IN NUMBER,
562 p_lot_expiration_date IN DATE,
563 p_primary_quantity IN NUMBER,
564 p_serial_trx_id IN NUMBER,
565 p_error_code IN VARCHAR2,
566 p_process_flag IN VARCHAR2,
567 -- HW OPMCONV. Added p_secondary_trx_quantity
568 -- and p_grade_code
569 p_secondary_trx_quantity IN NUMBER DEFAULT NULL,
570 p_grade_code IN VARCHAR2 DEFAULT NULL )
571 IS
572 CURSOR lock_record IS
573 SELECT * FROM mtl_transaction_lots_interface
574 WHERE rowid = x_rowid
575 FOR UPDATE NOWAIT;
576
577 rec_info lock_record%ROWTYPE;
578
579 --
580 l_debug_on BOOLEAN;
581 --
582 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_ROW';
583 --
584 BEGIN
585 /* wsh_server_debug.log_event('WSH_TRXLOTS_HANDLER.LOCK_ROW',
586 'START',
587 'Start of procedure LOCK_ROW, input parameters:
588 source_code='||p_source_code||', source_line_id='||p_source_line_id||
589 ', transaction_interface_id='||p_trx_interface_id||
590 ', lot_number='||p_lot_number||
591 ', transaction_quantity='||p_trx_quantity||
592 ', lot_expiration_date='||p_lot_expiration_date||
593 ', primary_quantity='||p_primary_quantity||
594 ', serial_transaction_temp_id='||p_serial_trx_id||
595 ', error_code='||p_error_code||', process_flag='||p_process_flag );
596 */
597
598 --
599 -- Debug Statements
600 --
601 --
602 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
603 --
604 IF l_debug_on IS NULL
605 THEN
606 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
607 END IF;
608 --
609 IF l_debug_on THEN
610 WSH_DEBUG_SV.push(l_module_name);
611 --
612 WSH_DEBUG_SV.log(l_module_name,'X_ROWID',X_ROWID);
613 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
614 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_LINE_ID',P_SOURCE_LINE_ID);
615 WSH_DEBUG_SV.log(l_module_name,'P_TRX_INTERFACE_ID',P_TRX_INTERFACE_ID);
616 WSH_DEBUG_SV.log(l_module_name,'P_LOT_NUMBER',P_LOT_NUMBER);
617 WSH_DEBUG_SV.log(l_module_name,'P_TRX_QUANTITY',P_TRX_QUANTITY);
618 WSH_DEBUG_SV.log(l_module_name,'P_LOT_EXPIRATION_DATE',P_LOT_EXPIRATION_DATE);
619 WSH_DEBUG_SV.log(l_module_name,'P_PRIMARY_QUANTITY',P_PRIMARY_QUANTITY);
620 WSH_DEBUG_SV.log(l_module_name,'P_SERIAL_TRX_ID',P_SERIAL_TRX_ID);
621 WSH_DEBUG_SV.log(l_module_name,'P_ERROR_CODE',P_ERROR_CODE);
622 WSH_DEBUG_SV.log(l_module_name,'P_PROCESS_FLAG',P_PROCESS_FLAG);
623 -- HW OPMCONV. Added debugging msgs
624 WSH_DEBUG_SV.log(l_module_name,'P_SECONDARY_TRX_QUANTITY',P_SECONDARY_TRX_QUANTITY);
625 WSH_DEBUG_SV.log(l_module_name,'P_GRADE_CODE',P_GRADE_CODE);
626 END IF;
627 --
628 OPEN lock_record;
629
630 FETCH lock_record into rec_info;
631
632 IF (lock_record%NOTFOUND) THEN
633 /* wsh_server_debug.log_event('WSH_TRXLOTS_HANDLER.LOCK_ROW',
634 'END',
635 'Lock record failed. Raising exception FORM_RECORD_DELETED');
636 */
637 CLOSE lock_record;
638
639 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
640 app_exception.raise_exception;
641 END IF;
642
643 CLOSE lock_record;
644
645 IF (
646 ( (rec_info.source_code = p_source_code)
647 OR ((rec_info.source_code IS NULL) AND (p_source_code IS NULL)))
648 AND ((rec_info.source_line_id = p_source_line_id)
649 OR ((rec_info.source_line_id IS NULL)
650 AND (p_source_line_id IS NULL)))
651 AND (rec_info.transaction_interface_id = p_trx_interface_id)
652 AND (rec_info.lot_number = p_lot_number)
653 AND (rec_info.transaction_quantity = p_trx_quantity)
654 AND ((rec_info.lot_expiration_date = p_lot_expiration_date)
655 OR ((rec_info.lot_expiration_date IS NULL)
656 AND (p_lot_expiration_date IS NULL)))
657 AND ((rec_info.primary_quantity = p_primary_quantity)
658 OR ((rec_info.primary_quantity IS NULL)
659 AND (p_primary_quantity IS NULL)))
660 AND ((rec_info.serial_transaction_temp_id = p_serial_trx_id)
661 OR ((rec_info.serial_transaction_temp_id IS NULL)
662 AND (p_serial_trx_id IS NULL)))
663 AND ((rec_info.error_code = p_error_code)
664 OR ((rec_info.error_code IS NULL) AND (p_error_code IS NULL)))
665 AND ((rec_info.process_flag = p_process_flag)
666 OR ((rec_info.process_flag IS NULL) AND (p_process_flag IS NULL)))
667 -- HW OPMCONV. Added secondary_qty and grade
668 AND ((rec_info.grade_code = p_grade_code)
669 OR ((rec_info.grade_code IS NULL) AND (p_grade_code IS NULL)))
670 AND ((rec_info.secondary_transaction_quantity = p_secondary_trx_quantity)
671 OR ((rec_info.secondary_transaction_quantity IS NULL) AND (p_secondary_trx_quantity IS NULL)))
672
673 ) THEN
674 /* wsh_server_debug.log_event('WSH_TRXLOTS_HANDLER.LOCK_ROW',
675 'END',
676 'End of procedure LOCK_ROW');
677 */
678 --
679 -- Debug Statements
680 --
681 IF l_debug_on THEN
682 WSH_DEBUG_SV.pop(l_module_name);
683 END IF;
684 --
685 return;
686 ELSE
687 /* wsh_server_debug.log_event('WSH_TRXLOTS_HANDLER.LOCK_ROW',
688 'END',
689 'Lock record failed. Raising exception FORM_RECORD_CHANGED');
690 */
691 fnd_message.set_name('FND','FORM_RECORD_CHANGED');
692 app_exception.raise_exception;
693 END IF;
694
695 --
696 -- Debug Statements
697 --
698 IF l_debug_on THEN
699 WSH_DEBUG_SV.pop(l_module_name);
700 END IF;
701 --
702 END Lock_Row;
703
704 END WSH_TRXLOTS_HANDLER;