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