[Home] [Help]
PACKAGE BODY: APPS.WSH_TRXSN_HANDLER
Source
1 PACKAGE BODY WSH_TRXSN_HANDLER AS
2 /* $Header: WSHIISNB.pls 120.1 2010/07/05 13:16:20 sunilku 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 --Bugfix 8467875 Start
79 cycles_since_mark,
80 cycles_since_new,
81 cycles_since_overhaul,
82 cycles_since_repair,
83 cycles_since_visit,
84 c_attribute1,
85 c_attribute10,
86 c_attribute11,
87 c_attribute12,
88 c_attribute13,
89 c_attribute14,
90 c_attribute15,
91 c_attribute16,
92 c_attribute17,
93 c_attribute18,
94 c_attribute19,
95 c_attribute2,
96 c_attribute20,
97 c_attribute3,
98 c_attribute4,
99 c_attribute5,
100 c_attribute6,
101 c_attribute7,
102 c_attribute8,
103 c_attribute9,
104 d_attribute1,
105 d_attribute10,
106 d_attribute2,
107 d_attribute3,
108 d_attribute4,
109 d_attribute5,
110 d_attribute6,
111 d_attribute7,
112 d_attribute8,
113 d_attribute9,
114 number_of_repairs,
115 n_attribute1,
116 n_attribute10,
117 n_attribute2,
118 n_attribute3,
119 n_attribute4,
120 n_attribute5,
121 n_attribute6,
122 n_attribute7,
123 n_attribute8,
124 n_attribute9,
125 territory_code,
126 time_since_mark,
127 time_since_new,
128 time_since_overhaul,
129 time_since_repair,
130 time_since_visit,
131 serial_attribute_category
132 --Bugfix 8467875 End
133 ) VALUES (
134 p_mtl_ser_txn_if_rec.source_code(i),
135 p_mtl_ser_txn_if_rec.source_line_id(i),
136 p_mtl_ser_txn_if_rec.transaction_interface_id(i),
137 p_mtl_ser_txn_if_rec.fm_serial_number(i),
138 p_mtl_ser_txn_if_rec.to_serial_number(i),
139 1,
140 SYSDATE,
141 g_userid,
142 SYSDATE,
143 g_userid,
144 p_mtl_ser_txn_if_rec.attribute_category(i), -- Bug 3628620
145 p_mtl_ser_txn_if_rec.attribute1(i),
146 p_mtl_ser_txn_if_rec.attribute2(i),
147 p_mtl_ser_txn_if_rec.attribute3(i),
148 p_mtl_ser_txn_if_rec.attribute4(i),
149 p_mtl_ser_txn_if_rec.attribute5(i),
150 p_mtl_ser_txn_if_rec.attribute6(i),
151 p_mtl_ser_txn_if_rec.attribute7(i),
152 p_mtl_ser_txn_if_rec.attribute8(i),
153 p_mtl_ser_txn_if_rec.attribute9(i),
154 p_mtl_ser_txn_if_rec.attribute10(i),
155 p_mtl_ser_txn_if_rec.attribute11(i),
156 p_mtl_ser_txn_if_rec.attribute12(i),
157 p_mtl_ser_txn_if_rec.attribute13(i),
158 p_mtl_ser_txn_if_rec.attribute14(i),
159 p_mtl_ser_txn_if_rec.attribute15(i),
160 --Bugfix 8467875 Start
161 p_mtl_ser_txn_if_rec.cycles_since_mark(i),
162 p_mtl_ser_txn_if_rec.cycles_since_new(i),
163 p_mtl_ser_txn_if_rec.cycles_since_overhaul(i),
164 p_mtl_ser_txn_if_rec.cycles_since_repair(i),
165 p_mtl_ser_txn_if_rec.cycles_since_visit(i),
166 p_mtl_ser_txn_if_rec.c_attribute1(i),
167 p_mtl_ser_txn_if_rec.c_attribute10(i),
168 p_mtl_ser_txn_if_rec.c_attribute11(i),
169 p_mtl_ser_txn_if_rec.c_attribute12(i),
170 p_mtl_ser_txn_if_rec.c_attribute13(i),
171 p_mtl_ser_txn_if_rec.c_attribute14(i),
172 p_mtl_ser_txn_if_rec.c_attribute15(i),
173 p_mtl_ser_txn_if_rec.c_attribute16(i),
174 p_mtl_ser_txn_if_rec.c_attribute17(i),
175 p_mtl_ser_txn_if_rec.c_attribute18(i),
176 p_mtl_ser_txn_if_rec.c_attribute19(i),
177 p_mtl_ser_txn_if_rec.c_attribute2(i),
178 p_mtl_ser_txn_if_rec.c_attribute20(i),
179 p_mtl_ser_txn_if_rec.c_attribute3(i),
180 p_mtl_ser_txn_if_rec.c_attribute4(i),
181 p_mtl_ser_txn_if_rec.c_attribute5(i),
182 p_mtl_ser_txn_if_rec.c_attribute6(i),
183 p_mtl_ser_txn_if_rec.c_attribute7(i),
184 p_mtl_ser_txn_if_rec.c_attribute8(i),
185 p_mtl_ser_txn_if_rec.c_attribute9(i),
186 p_mtl_ser_txn_if_rec.d_attribute1(i),
187 p_mtl_ser_txn_if_rec.d_attribute10(i),
188 p_mtl_ser_txn_if_rec.d_attribute2(i),
189 p_mtl_ser_txn_if_rec.d_attribute3(i),
190 p_mtl_ser_txn_if_rec.d_attribute4(i),
191 p_mtl_ser_txn_if_rec.d_attribute5(i),
192 p_mtl_ser_txn_if_rec.d_attribute6(i),
193 p_mtl_ser_txn_if_rec.d_attribute7(i),
194 p_mtl_ser_txn_if_rec.d_attribute8(i),
195 p_mtl_ser_txn_if_rec.d_attribute9(i),
196 p_mtl_ser_txn_if_rec.number_of_repairs(i),
197 p_mtl_ser_txn_if_rec.n_attribute1(i),
198 p_mtl_ser_txn_if_rec.n_attribute10(i),
199 p_mtl_ser_txn_if_rec.n_attribute2(i),
200 p_mtl_ser_txn_if_rec.n_attribute3(i),
201 p_mtl_ser_txn_if_rec.n_attribute4(i),
202 p_mtl_ser_txn_if_rec.n_attribute5(i),
203 p_mtl_ser_txn_if_rec.n_attribute6(i),
204 p_mtl_ser_txn_if_rec.n_attribute7(i),
205 p_mtl_ser_txn_if_rec.n_attribute8(i),
206 p_mtl_ser_txn_if_rec.n_attribute9(i),
207 p_mtl_ser_txn_if_rec.territory_code(i),
208 p_mtl_ser_txn_if_rec.time_since_mark(i),
209 p_mtl_ser_txn_if_rec.time_since_new(i),
210 p_mtl_ser_txn_if_rec.time_since_overhaul(i),
211 p_mtl_ser_txn_if_rec.time_since_repair(i),
212 p_mtl_ser_txn_if_rec.time_since_visit(i),
213 p_mtl_ser_txn_if_rec.serial_attribute_category(i)
214 --Bugfix 8467875 End
215 );
216
217 IF l_debug_on THEN
218 WSH_DEBUG_SV.log(l_module_name,'Rows inserted in mtl_serial_numbers_interface',SQL%ROWCOUNT);
219 END IF;
220
221
222 IF l_debug_on THEN
223 WSH_DEBUG_SV.pop(l_module_name);
224 END IF;
225
226 EXCEPTION
227 WHEN OTHERS THEN
228 x_return_status:= WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
229
230 IF l_debug_on THEN
231 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
232 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
233 END IF;
234
235 END insert_row_bulk;
236 --HVOP heali
237
238 -- ===========================================================================
239 --
240 -- Name:
241 --
242 -- insert_row
243 --
244 -- Description:
245 --
246 -- Called by the client to insert a row into the
247 -- MTL_SERIAL_NUMBERS_INTERFACE table.
248 --
249 -- ===========================================================================
250 PROCEDURE Insert_Row (
251 x_rowid IN OUT NOCOPY VARCHAR2,
252 x_trx_interface_id IN OUT NOCOPY NUMBER,
253 p_source_code IN VARCHAR2,
254 p_source_line_id IN NUMBER,
255 p_fm_serial_number IN VARCHAR2,
256 p_to_serial_number IN VARCHAR2,
257 p_creation_date IN DATE,
258 p_created_by IN NUMBER,
259 p_last_updated_by IN NUMBER,
260 p_last_update_date IN DATE,
261 p_last_update_login IN NUMBER DEFAULT NULL,
262 p_request_id IN NUMBER DEFAULT NULL,
263 p_program_application_id IN NUMBER DEFAULT NULL,
264 p_program_id IN NUMBER DEFAULT NULL,
265 p_program_update_date IN DATE DEFAULT NULL,
266 p_parent_serial_number IN VARCHAR2 DEFAULT NULL,
267 p_vendor_serial_number IN VARCHAR2 DEFAULT NULL,
268 p_vendor_lot_number IN VARCHAR2 DEFAULT NULL,
269 p_error_code IN VARCHAR2 DEFAULT NULL,
270 p_process_flag IN NUMBER DEFAULT 1)
271 IS
272
273 CURSOR row_id IS
274 SELECT rowid FROM mtl_serial_numbers_interface
275 WHERE transaction_interface_id = x_trx_interface_id
276 AND NVL(fm_serial_number,'-1') =
277 NVL(p_fm_serial_number, NVL(fm_serial_number,'-1'))
278 AND NVL(to_serial_number,'-1') =
279 NVL(p_to_serial_number, NVL(to_serial_number,'-1'));
280
281 CURSOR get_interface_id IS
282 SELECT mtl_material_transactions_s.nextval
283 FROM sys.dual;
284
285 --
286 l_debug_on BOOLEAN;
287 --
288 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_ROW';
289 --
290 BEGIN
291
292 /* wsh_server_debug.log_event('WSH_TRXSN_HANDLER.INSERT_ROW',
293 'START',
294 'Start of procedure INSERT_ROW, input parameters:
295 source_code='||p_source_code||
296 ',source_line_id='||to_char(p_source_line_id)||
297 ', transaction_interface_id='||to_char(x_trx_interface_id)||
298 ', fm_serial_number='||p_fm_serial_number||
299 ', to_serial_number='||p_to_serial_number||
300 ', creation_date='||p_creation_date||
301 ', created_by='||to_char(p_created_by));
302 wsh_server_debug.debug_message(
303 ', last_updated_by='||to_char(p_last_updated_by)||
304 ', last_update_date='||p_last_update_date||
305 ', last_update_login='||p_last_update_login||
306 ', request_id='||p_request_id||
307 ', program_application_id='||p_program_application_id||
308 ', program_id='||p_program_id||
309 ', program_update_date='||p_program_update_date||
310 ', parent_serial_number='||p_parent_serial_number||
311 ', vendor_serial_number='||p_vendor_serial_number||
312 ', vendor_lot_number='||p_vendor_lot_number||
313 ', error_code='||p_error_code||
314 ', process_flag='||to_char(p_process_flag) );
315 */
316
317 -- if from serial number is NULL, raise exception
318 --
319 --
320 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
321 --
322 IF l_debug_on IS NULL
323 THEN
324 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
325 END IF;
326 --
327 IF l_debug_on THEN
328 WSH_DEBUG_SV.push(l_module_name);
329 --
330 WSH_DEBUG_SV.log(l_module_name,'X_ROWID',X_ROWID);
331 WSH_DEBUG_SV.log(l_module_name,'X_TRX_INTERFACE_ID',X_TRX_INTERFACE_ID);
332 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
333 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_LINE_ID',P_SOURCE_LINE_ID);
334 WSH_DEBUG_SV.log(l_module_name,'P_FM_SERIAL_NUMBER',P_FM_SERIAL_NUMBER);
335 WSH_DEBUG_SV.log(l_module_name,'P_TO_SERIAL_NUMBER',P_TO_SERIAL_NUMBER);
336 WSH_DEBUG_SV.log(l_module_name,'P_CREATION_DATE',P_CREATION_DATE);
337 WSH_DEBUG_SV.log(l_module_name,'P_CREATED_BY',P_CREATED_BY);
338 WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATED_BY',P_LAST_UPDATED_BY);
339 WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATE_DATE',P_LAST_UPDATE_DATE);
340 WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATE_LOGIN',P_LAST_UPDATE_LOGIN);
341 WSH_DEBUG_SV.log(l_module_name,'P_REQUEST_ID',P_REQUEST_ID);
342 WSH_DEBUG_SV.log(l_module_name,'P_PROGRAM_APPLICATION_ID',P_PROGRAM_APPLICATION_ID);
343 WSH_DEBUG_SV.log(l_module_name,'P_PROGRAM_ID',P_PROGRAM_ID);
344 WSH_DEBUG_SV.log(l_module_name,'P_PROGRAM_UPDATE_DATE',P_PROGRAM_UPDATE_DATE);
345 WSH_DEBUG_SV.log(l_module_name,'P_PARENT_SERIAL_NUMBER',P_PARENT_SERIAL_NUMBER);
346 WSH_DEBUG_SV.log(l_module_name,'P_VENDOR_SERIAL_NUMBER',P_VENDOR_SERIAL_NUMBER);
347 WSH_DEBUG_SV.log(l_module_name,'P_VENDOR_LOT_NUMBER',P_VENDOR_LOT_NUMBER);
348 WSH_DEBUG_SV.log(l_module_name,'P_ERROR_CODE',P_ERROR_CODE);
349 WSH_DEBUG_SV.log(l_module_name,'P_PROCESS_FLAG',P_PROCESS_FLAG);
350 END IF;
351 --
352 IF (p_fm_serial_number IS NULL) THEN
353 /* wsh_server_debug.log_event('WSH_TRXSN_HANDLER.INSERT_ROW',
354 'END',
355 'Insert failed. From serial number is NULL.
356 Raising WSH_FM_SERIALNO_NULL');
357 */
358 IF l_debug_on THEN
359 WSH_DEBUG_SV.pop(l_module_name,'WSH_FM_SERIALNO_NULL');
360 END IF;
361 RAISE WSH_FM_SERIALNO_NULL;
362 END IF;
363
364 fnd_profile.get('USER_ID',g_userid);
365
366 -- Set interface id if necessary
367 IF l_debug_on THEN
368 WSH_DEBUG_SV.log(l_module_name,'USER_ID',g_userid);
369 END IF;
370 IF x_trx_interface_id IS NULL THEN
371 OPEN get_interface_id;
372 FETCH get_interface_id INTO x_trx_interface_id;
373 CLOSE get_interface_id;
374 END IF;
375
376 INSERT INTO mtl_serial_numbers_interface (
377 source_code,
378 source_line_id,
379 transaction_interface_id,
380 fm_serial_number,
381 to_serial_number,
382 creation_date,
383 created_by,
384 last_updated_by,
385 last_update_date,
386 last_update_login,
387 request_id,
388 program_application_id,
389 program_id,
390 program_update_date,
391 parent_serial_number,
392 vendor_serial_number,
393 vendor_lot_number,
394 error_code,
395 process_flag
396 ) VALUES (
397 p_source_code,
398 p_source_line_id,
399 x_trx_interface_id,
400 p_fm_serial_number,
401 p_to_serial_number,
402 NVL(p_creation_date,SYSDATE),
403 NVL(p_created_by,g_userid),
404 NVL(p_last_updated_by,g_userid),
405 NVL(p_last_update_date,SYSDATE),
406 p_last_update_login,
407 p_request_id,
408 p_program_application_id,
409 p_program_id,
410 p_program_update_date,
411 p_parent_serial_number,
412 p_vendor_serial_number,
413 p_vendor_lot_number,
414 p_error_code,
415 p_process_flag
416 );
417 IF l_debug_on THEN
418 WSH_DEBUG_SV.log(l_module_name,'Rows inserted',SQL%ROWCOUNT);
419 END IF;
420 OPEN row_id;
421
422 FETCH row_id INTO x_rowid;
423
424 IF (row_id%NOTFOUND) then
425 /* wsh_server_debug.log_event('WSH_TRXSN_HANDLER.INSERT_ROW',
426 'END',
427 'No rowid found. Raising NO_DATA_FOUND.');
428 */
429 CLOSE row_id;
430 IF l_debug_on THEN
431 WSH_DEBUG_SV.pop(l_module_name,'NO_DATA_FOUND');
432 END IF;
433 RAISE NO_DATA_FOUND;
434 END IF;
435
436 CLOSE row_id;
437
438 /* wsh_server_debug.log_event('WSH_TRXSN_HANDLER.INSERT_ROW',
439 'END',
440 'End of procedure INSERT_ROW');
441 */
442
443 --
444 IF l_debug_on THEN
445 WSH_DEBUG_SV.pop(l_module_name);
446 END IF;
447 --
448 END Insert_Row;
449
450 -- ===========================================================================
451 --
452 -- Name:
453 --
454 -- update_row
455 --
456 -- Description:
457 --
458 -- Called by the client to update a row in the
459 -- MTL_SERIAL_NUMBERS_INTERFACE table.
460 --
461 -- ===========================================================================
462
463 PROCEDURE Update_Row (
464 x_rowid IN OUT NOCOPY VARCHAR2,
465 p_trx_interface_id IN NUMBER,
466 p_source_code IN VARCHAR2,
467 p_source_line_id IN NUMBER,
468 p_fm_serial_number IN VARCHAR2,
469 p_to_serial_number IN VARCHAR2,
470 p_last_updated_by IN NUMBER,
471 p_last_update_date IN DATE,
472 p_last_update_login IN NUMBER DEFAULT NULL,
473 p_request_id IN NUMBER DEFAULT NULL,
474 p_program_application_id IN NUMBER DEFAULT NULL,
475 p_program_id IN NUMBER DEFAULT NULL,
476 p_program_update_date IN DATE DEFAULT NULL,
477 p_parent_serial_number IN VARCHAR2 DEFAULT NULL,
478 p_vendor_serial_number IN VARCHAR2 DEFAULT NULL,
479 p_vendor_lot_number IN VARCHAR2 DEFAULT NULL,
480 p_error_code IN VARCHAR2 DEFAULT NULL,
481 p_process_flag IN NUMBER DEFAULT 1)
482 IS
483 --
484 l_debug_on BOOLEAN;
485 --
486 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_ROW';
487 --
488 BEGIN
489 /* wsh_server_debug.log_event('WSH_TRXSN_HANDLER.UPDATE_ROW',
490 'START',
491 'Start of procedure UPDATE_ROW, input parameters:
492 source_code='||p_source_code||
493 ', source_line_id='||p_source_line_id||
494 ', transaction_interface_id='||p_trx_interface_id||
495 ', fm_serial_number='||p_fm_serial_number||
496 ', to_serial_number='||p_to_serial_number);
497 wsh_server_debug.debug_message(
498 ', last_updated_by='||p_last_updated_by||
499 ', last_update_date='||p_last_update_date||
500 ', last_update_login='||p_last_update_login||
501 ', request_id='||p_request_id||
502 ', program_application_id='||p_program_application_id||
503 ', program_id='||p_program_id||
504 ', program_update_date='||p_program_update_date||
505 ', parent_serial_number='||p_parent_serial_number||
506 ', vendor_serial_number='||p_vendor_serial_number||
507 ', vendor_lot_number='||p_vendor_lot_number||
508 ', error_code='||p_error_code||', process_flag='||p_process_flag );
509 */
510
511 --
512 --
513 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
514 --
515 IF l_debug_on IS NULL
516 THEN
517 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
518 END IF;
519 --
520 IF l_debug_on THEN
521 WSH_DEBUG_SV.push(l_module_name);
522 --
523 WSH_DEBUG_SV.log(l_module_name,'X_ROWID',X_ROWID);
524 WSH_DEBUG_SV.log(l_module_name,'P_TRX_INTERFACE_ID',P_TRX_INTERFACE_ID);
525 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
526 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_LINE_ID',P_SOURCE_LINE_ID);
527 WSH_DEBUG_SV.log(l_module_name,'P_FM_SERIAL_NUMBER',P_FM_SERIAL_NUMBER);
528 WSH_DEBUG_SV.log(l_module_name,'P_TO_SERIAL_NUMBER',P_TO_SERIAL_NUMBER);
529 WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATED_BY',P_LAST_UPDATED_BY);
530 WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATE_DATE',P_LAST_UPDATE_DATE);
531 WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATE_LOGIN',P_LAST_UPDATE_LOGIN);
532 WSH_DEBUG_SV.log(l_module_name,'P_REQUEST_ID',P_REQUEST_ID);
533 WSH_DEBUG_SV.log(l_module_name,'P_PROGRAM_APPLICATION_ID',P_PROGRAM_APPLICATION_ID);
534 WSH_DEBUG_SV.log(l_module_name,'P_PROGRAM_ID',P_PROGRAM_ID);
535 WSH_DEBUG_SV.log(l_module_name,'P_PROGRAM_UPDATE_DATE',P_PROGRAM_UPDATE_DATE);
536 WSH_DEBUG_SV.log(l_module_name,'P_PARENT_SERIAL_NUMBER',P_PARENT_SERIAL_NUMBER);
537 WSH_DEBUG_SV.log(l_module_name,'P_VENDOR_SERIAL_NUMBER',P_VENDOR_SERIAL_NUMBER);
538 WSH_DEBUG_SV.log(l_module_name,'P_VENDOR_LOT_NUMBER',P_VENDOR_LOT_NUMBER);
539 WSH_DEBUG_SV.log(l_module_name,'P_ERROR_CODE',P_ERROR_CODE);
540 WSH_DEBUG_SV.log(l_module_name,'P_PROCESS_FLAG',P_PROCESS_FLAG);
541 END IF;
542 --
543 IF (p_fm_serial_number IS NULL) THEN
544 /* wsh_server_debug.log_event('WSH_TRXSN_HANDLER.UPDATE_ROW',
545 'END',
546 'UPDATE failed. From serial number is NULL.
547 Raising WSH_FM_SERIALNO_NULL');
548 */
549 RAISE WSH_FM_SERIALNO_NULL;
550 IF l_debug_on THEN
551 WSH_DEBUG_SV.pop(l_module_name,'WSH_FM_SERIALNO_NULL');
552 END IF;
553
554 END IF;
555
556 fnd_profile.get('USER_ID',g_userid);
557
558 UPDATE mtl_serial_numbers_interface SET
559 source_code = p_source_code,
560 source_line_id = p_source_line_id,
561 transaction_interface_id = p_trx_interface_id,
562 fm_serial_number = p_fm_serial_number,
563 to_serial_number = p_to_serial_number,
564 last_updated_by = NVL(p_last_updated_by,g_userid),
565 last_update_date = NVL(p_last_update_date,SYSDATE),
566 last_update_login = p_last_update_login,
567 request_id = p_request_id,
568 program_application_id = p_program_application_id,
569 program_id = p_program_id,
570 program_update_date = p_program_update_date,
571 parent_serial_number = p_parent_serial_number,
572 vendor_serial_number = p_vendor_serial_number,
573 vendor_lot_number = p_vendor_lot_number,
574 error_code = p_error_code,
575 process_flag = p_process_flag
576 WHERE rowid = x_rowid;
577 IF l_debug_on THEN
578 WSH_DEBUG_SV.log(l_module_name,'Rows Updated',SQL%ROWCOUNT);
579 END IF;
580
581 IF (SQL%NOTFOUND) THEN
582 /* wsh_server_debug.log_event('WSH_TRXSN_HANDLER.UPDATE_ROW',
583 'END',
584 'No rows updated. Raising NO_DATA_FOUND.');
585 */
586 IF l_debug_on THEN
587 WSH_DEBUG_SV.pop(l_module_name,'NO_DATA_FOUND');
588 END IF;
589 RAISE NO_DATA_FOUND;
590 END IF;
591
592 /* wsh_server_debug.log_event('WSH_TRXSN_HANDLER.UPDATE_ROW',
593 'END',
594 'End of procedure UPDATE_ROW');
595 */
596 --
597 IF l_debug_on THEN
598 WSH_DEBUG_SV.pop(l_module_name);
599 END IF;
600 --
601 END Update_Row;
602
603 -- ===========================================================================
604 --
605 -- Name:
606 --
607 -- delete_row
608 --
609 -- Description:
610 --
611 -- Called by the client to delete a row in the
612 -- MTL_SERIAL_NUMBERS_INTERFACE table.
613 --
614 -- ===========================================================================
615
616 PROCEDURE Delete_Row (
617 x_rowid IN OUT NOCOPY VARCHAR2 )
618 IS
619 --
620 l_debug_on BOOLEAN;
621 --
622 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_ROW';
623 --
624 BEGIN
625 /* wsh_server_debug.log_event('WSH_TRXSN_HANDLER.DELETE_ROW',
626 'START',
627 'Start of procedure DELETE_ROW');
628 */
629 --
630 --
631 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
632 --
633 IF l_debug_on IS NULL
634 THEN
635 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
636 END IF;
637 --
638 IF l_debug_on THEN
639 WSH_DEBUG_SV.push(l_module_name);
640 --
641 WSH_DEBUG_SV.log(l_module_name,'X_ROWID',X_ROWID);
642 END IF;
643 --
644 DELETE FROM mtl_serial_numbers_interface WHERE rowid = x_rowid;
645
646 IF l_debug_on THEN
647 WSH_DEBUG_SV.log(l_module_name,'Rows deleted',SQL%ROWCOUNT);
648 END IF;
649 IF (SQL%NOTFOUND) THEN
650 /* wsh_server_debug.log_event('WSH_TRXSN_HANDLER.DELETE_ROW',
651 'END',
652 'No rows deleted. Raising NO_DATA_FOUND');
653 */
654 IF l_debug_on THEN
655 WSH_DEBUG_SV.pop(l_module_name,'NO_DATA_FOUND');
656 END IF;
657 RAISE NO_DATA_FOUND;
658 END IF;
659
660 /* wsh_server_debug.log_event('WSH_TRXSN_HANDLER.DELETE_ROW',
661 'END',
662 'End of procedure DELETE_ROW');
663 */
664 --
665 IF l_debug_on THEN
666 WSH_DEBUG_SV.pop(l_module_name);
667 END IF;
668 --
669 END Delete_Row;
670
671 -- ===========================================================================
672 --
673 -- Name:
674 --
675 -- lock_row
676 --
677 -- Description:
678 --
679 -- Called by the client to lock a row in the
680 -- MTL_SERIAL_NUMBERS_INTERFACE table.
681 --
682 -- ===========================================================================
683
684 PROCEDURE Lock_Row (
685 x_rowid IN OUT NOCOPY VARCHAR2,
686 p_source_code IN VARCHAR2,
687 p_source_line_id IN NUMBER,
688 p_trx_interface_id IN NUMBER,
689 p_vendor_serial_number IN VARCHAR2,
690 p_vendor_lot_number IN VARCHAR2,
691 p_fm_serial_number IN VARCHAR2,
692 p_to_serial_number IN VARCHAR2,
693 p_error_code IN VARCHAR2,
694 p_process_flag IN NUMBER,
695 p_parent_serial_number IN VARCHAR2 )
696 IS
697 CURSOR lock_record IS
698 SELECT * FROM mtl_serial_numbers_interface
699 WHERE rowid = x_rowid
700 FOR UPDATE NOWAIT;
701
702 rec_info lock_record%ROWTYPE;
703
704 --
705 l_debug_on BOOLEAN;
706 --
707 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_ROW';
708 --
709 BEGIN
710 /* wsh_server_debug.log_event('WSH_TRXSN_HANDLER.LOCK_ROW',
711 'START',
712 'Start of procedure LOCK_ROW, input parameters:
713 source_code='||p_source_code||
714 ', source_line_id='||p_source_line_id||
715 ', transaction_interface_id='||p_trx_interface_id||
716 ', vendor_serial_number='||p_vendor_serial_number||
717 ', vendor_lot_number='||p_vendor_lot_number||
718 ', fm_serial_number='||p_fm_serial_number||
719 ', to_serial_number='||p_to_serial_number||
720 ', error_code='||p_error_code||', process_flag='||p_process_flag||
721 ', parent_serial_number='||p_parent_serial_number );
722 */
723 --
724 --
725 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
726 --
727 IF l_debug_on IS NULL
728 THEN
729 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
730 END IF;
731 --
732 IF l_debug_on THEN
733 WSH_DEBUG_SV.push(l_module_name);
734 --
735 WSH_DEBUG_SV.log(l_module_name,'X_ROWID',X_ROWID);
736 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
737 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_LINE_ID',P_SOURCE_LINE_ID);
738 WSH_DEBUG_SV.log(l_module_name,'P_TRX_INTERFACE_ID',P_TRX_INTERFACE_ID);
739 WSH_DEBUG_SV.log(l_module_name,'P_VENDOR_SERIAL_NUMBER',P_VENDOR_SERIAL_NUMBER);
740 WSH_DEBUG_SV.log(l_module_name,'P_VENDOR_LOT_NUMBER',P_VENDOR_LOT_NUMBER);
741 WSH_DEBUG_SV.log(l_module_name,'P_FM_SERIAL_NUMBER',P_FM_SERIAL_NUMBER);
742 WSH_DEBUG_SV.log(l_module_name,'P_TO_SERIAL_NUMBER',P_TO_SERIAL_NUMBER);
743 WSH_DEBUG_SV.log(l_module_name,'P_ERROR_CODE',P_ERROR_CODE);
744 WSH_DEBUG_SV.log(l_module_name,'P_PROCESS_FLAG',P_PROCESS_FLAG);
745 WSH_DEBUG_SV.log(l_module_name,'P_PARENT_SERIAL_NUMBER',P_PARENT_SERIAL_NUMBER);
746 END IF;
747 --
748 OPEN lock_record;
749
750 FETCH lock_record into rec_info;
751
752 IF (lock_record%NOTFOUND) THEN
753 /* wsh_server_debug.log_event('WSH_TRXSN_HANDLER.LOCK_ROW',
754 'END',
755 'Lock record failed. Raising exception FORM_RECORD_DELETED');
756 */
757 CLOSE lock_record;
758
759 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
760 IF l_debug_on THEN
761 WSH_DEBUG_SV.pop(l_module_name,'FORM_RECORD_DELETED');
762 END IF;
763 app_exception.raise_exception;
764 END IF;
765
766 CLOSE lock_record;
767
768 IF (
769 ( (rec_info.source_code = p_source_code)
770 OR ((rec_info.source_code IS NULL) AND (p_source_code IS NULL)))
771 AND ((rec_info.source_line_id = p_source_line_id)
772 OR ((rec_info.source_line_id IS NULL)
773 AND (p_source_line_id IS NULL)))
774 AND (rec_info.transaction_interface_id = p_trx_interface_id)
775 AND ((rec_info.vendor_serial_number = p_vendor_serial_number)
776 OR ((rec_info.vendor_serial_number IS NULL)
777 AND (p_vendor_serial_number IS NULL)))
778 AND ((rec_info.vendor_lot_number = p_vendor_lot_number)
779 OR ((rec_info.vendor_lot_number IS NULL)
780 AND (p_vendor_lot_number IS NULL)))
781 AND ((rec_info.fm_serial_number = p_fm_serial_number)
782 OR ((rec_info.fm_serial_number IS NULL)
783 AND (p_fm_serial_number IS NULL)))
784 AND ((rec_info.to_serial_number = p_to_serial_number)
785 OR ((rec_info.to_serial_number IS NULL)
786 AND (p_to_serial_number IS NULL)))
787 AND ((rec_info.error_code = p_error_code)
788 OR ((rec_info.error_code IS NULL) AND (p_error_code IS NULL)))
789 AND ((rec_info.process_flag = p_process_flag)
790 OR ((rec_info.process_flag IS NULL) AND (p_process_flag IS NULL)))
791 AND ((rec_info.parent_serial_number = p_parent_serial_number)
792 OR ((rec_info.parent_serial_number IS NULL)
793 AND (p_parent_serial_number IS NULL)))
794 ) THEN
795 /* wsh_server_debug.log_event('WSH_TRXSN_HANDLER.LOCK_ROW',
796 'END',
797 'End of procedure LOCK_ROW');
798 */
799 --
800 IF l_debug_on THEN
801 WSH_DEBUG_SV.pop(l_module_name);
802 END IF;
803 --
804 return;
805 ELSE
806 /* wsh_server_debug.log_event('WSH_TRXSN_HANDLER.LOCK_ROW',
807 'END',
808 'Lock record failed. Raising exception FORM_RECORD_CHANGED');
809 */
810 fnd_message.set_name('FND','FORM_RECORD_CHANGED');
811 IF l_debug_on THEN
812 WSH_DEBUG_SV.pop(l_module_name,'FORM_RECORD_CHANGED');
813 END IF;
814 app_exception.raise_exception;
815 END IF;
816
817 IF l_debug_on THEN
818 WSH_DEBUG_SV.pop(l_module_name);
819 END IF;
820 --
821 END Lock_Row;
822
823 END WSH_TRXSN_HANDLER;