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