1 package body WMS_DEVHIST_HANDLER_PKG as
2 /* $Header: WMSDVTHB.pls 115.7 2003/02/06 05:04:46 satkumar noship $ */
3 procedure INSERT_ROW (
4 X_ROWID IN OUT NOCOPY VARCHAR2,
5 X_REQUEST_ID IN NUMBER,
6 X_TASK_ID IN NUMBER,
7 X_RELATION_ID IN NUMBER,
8 X_SEQUENCE_ID IN NUMBER,
9 X_TASK_SUMMARY IN VARCHAR2,
10 X_TASK_TYPE_ID IN NUMBER,
11 X_BUSINESS_EVENT_ID IN NUMBER,
12 X_ORGANIZATION_ID IN NUMBER,
13 X_SUBINVENTORY_CODE IN VARCHAR2,
14 X_LOCATOR_ID IN NUMBER,
15 X_TRANSFER_ORG_ID IN NUMBER,
16 X_TRANSFER_SUB_CODE IN VARCHAR2,
17 X_TRANSFER_LOC_ID IN NUMBER,
18 X_INVENTORY_ITEM_ID IN NUMBER,
19 X_REVISION IN VARCHAR2,
20 X_UOM IN VARCHAR2,
21 X_LOT_NUMBER IN VARCHAR2,
22 X_LOT_QTY IN NUMBER,
23 X_SERIAL_NUMBER IN VARCHAR2,
24 X_LPN_ID IN NUMBER,
25 X_TRANSACTION_QUANTITY IN NUMBER,
26 X_DEVICE_ID IN NUMBER,
27 X_STATUS_CODE IN VARCHAR2,
28 X_STATUS_MSG IN VARCHAR2,
29 X_OUTFILE_NAME IN VARCHAR2,
30 X_REQUEST_DATE IN DATE,
31 X_RESUBMIT_DATE IN DATE,
32 X_REQUESTED_BY IN NUMBER,
33 X_RESP_APPLICATION_ID IN NUMBER,
34 X_RESPONSIBILITY_ID IN NUMBER,
35 X_CONCURRENT_REQUEST_ID IN NUMBER,
36 X_PROGRAM_APPLICATION_ID IN NUMBER,
37 X_PROGRAM_ID IN NUMBER,
38 X_PROGRAM_UPDATE_DATE IN NUMBER,
39 X_CREATION_DATE IN DATE,
40 X_CREATED_BY IN NUMBER,
41 X_LAST_UPDATE_DATE IN DATE,
42 X_LAST_UPDATED_BY IN NUMBER,
43 X_LAST_UPDATE_LOGIN IN NUMBER,
44 X_DEVICE_STATUS IN VARCHAR2,
45 X_REASON_ID IN NUMBER,
46 X_XFER_LPN_ID IN NUMBER
47 ) is
48
49 CURSOR C IS SELECT rowid FROM wms_device_requests_hist
50 WHERE request_id = X_REQUEST_ID
51 AND task_id = X_TASK_ID
52 AND business_event_id = x_business_event_id
53 AND task_summary = x_task_summary
54 AND Nvl(sequence_id,-999) = Nvl(x_sequence_id,-999);
55
56 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
57 BEGIN
58 IF (l_debug = 1) THEN
59 inv_log_util.trace('inside inserting for HIST form','WMS_DEVHIST_HANDLER_PKG',9);
60 END IF;
61 insert into WMS_DEVICE_REQUESTS_HIST (
62 REQUEST_ID ,
63 TASK_ID ,
64 RELATION_ID,
65 SEQUENCE_ID,
66 task_summary,
67 task_type_id,
68 business_event_id,
69 organization_id,
70 subinventory_code,
71 locator_id,
72 transfer_org_id,
73 transfer_sub_code,
74 transfer_loc_id,
75 inventory_item_id,
76 revision,
77 uom,
78 lot_number,
79 lot_qty,
80 serial_number,
81 lpn_id,
82 transaction_quantity,
83 device_id,
84 status_code,
85 status_msg,
86 outfile_name,
87 request_date,
88 resubmit_date,
89 requested_by,
90 responsibility_application_id,
91 responsibility_id,
92 concurrent_request_id,
93 program_application_id,
94 program_id,
95 program_update_date,
96 creation_date,
97 created_by,
98 last_update_date,
99 last_updated_by,
100 last_update_login,
101 device_status,
102 reason_id,
103 XFER_LPN_ID
104 ) values (
105 X_REQUEST_ID ,
106 X_TASK_ID ,
107 X_RELATION_ID,
108 X_SEQUENCE_ID,
109 x_task_summary,
110 x_task_type_id,
111 x_business_event_id,
112 x_organization_id,
113 x_subinventory_code,
114 x_locator_id,
115 x_transfer_org_id,
116 x_transfer_sub_code,
117 x_transfer_loc_id,
118 x_inventory_item_id,
119 x_revision,
120 x_uom,
121 x_lot_number,
122 x_lot_qty,
123 x_serial_number,
124 x_lpn_id,
125 x_transaction_quantity,
126 x_device_id,
127 x_status_code,
128 x_status_msg,
129 x_outfile_name,
130 x_request_date,
131 x_resubmit_date,
132 x_requested_by,
133 x_resp_application_id,
134 x_responsibility_id,
135 x_concurrent_request_id,
136 x_program_application_id,
137 x_program_id,
138 x_program_update_date,
139 x_creation_date,
140 x_created_by,
141 x_last_update_date,
142 x_last_updated_by,
143 x_last_update_login,
144 x_device_status,
145 x_reason_id,
146 X_XFER_LPN_ID
147 );
148
149 OPEN C;
150 FETCH C INTO X_Rowid;
151 if (C%NOTFOUND) then
152 CLOSE C;
153 Raise NO_DATA_FOUND;
154 end if;
155 CLOSE C;
156
157 end INSERT_ROW;
158
159 procedure LOCK_ROW (
160 X_ROWID in VARCHAR2,
161 X_REQUEST_ID IN NUMBER,
162 X_TASK_ID IN NUMBER,
163 X_RELATION_ID IN NUMBER,
164 X_SEQUENCE_ID IN NUMBER,
165 X_TASK_SUMMARY IN VARCHAR2,
166 X_TASK_TYPE_ID IN NUMBER,
167 X_BUSINESS_EVENT_ID IN NUMBER,
168 X_ORGANIZATION_ID IN NUMBER,
169 X_SUBINVENTORY_CODE IN VARCHAR2,
170 X_LOCATOR_ID IN NUMBER,
171 X_TRANSFER_ORG_ID IN NUMBER,
172 X_TRANSFER_SUB_CODE IN VARCHAR2,
173 X_TRANSFER_LOC_ID IN NUMBER,
174 X_INVENTORY_ITEM_ID IN NUMBER,
175 X_REVISION IN VARCHAR2,
176 X_UOM IN VARCHAR2,
177 X_LPN_ID IN NUMBER,
178 X_TRANSACTION_QUANTITY IN NUMBER,
179 X_DEVICE_ID IN NUMBER,
180 X_STATUS_CODE IN VARCHAR2,
181 X_STATUS_MSG IN VARCHAR2,
182 X_OUTFILE_NAME IN VARCHAR2,
183 X_REQUEST_DATE IN DATE,
184 X_RESUBMIT_DATE IN DATE,
185 X_REQUESTED_BY IN NUMBER,
186 X_RESP_APPLICATION_ID IN NUMBER,
187 X_RESPONSIBILITY_ID IN NUMBER,
188 X_CONCURRENT_REQUEST_ID IN NUMBER,
189 X_PROGRAM_APPLICATION_ID IN NUMBER,
190 X_PROGRAM_ID IN NUMBER,
191 X_PROGRAM_UPDATE_DATE IN NUMBER,
192 X_DEVICE_STATUS IN VARCHAR2,
193 X_REASON_ID IN NUMBER,
194 X_XFER_LPN_ID IN NUMBER
195 ) is
196 cursor c is SELECT
197 REQUEST_ID ,
198 TASK_ID ,
199 RELATION_ID,
200 SEQUENCE_ID,
201 task_summary,
202 task_type_id,
203 business_event_id,
204 organization_id,
205 subinventory_code,
206 locator_id,
207 transfer_org_id,
208 transfer_sub_code,
209 transfer_loc_id,
210 inventory_item_id,
211 revision,
212 uom,
213 lpn_id,
214 transaction_quantity,
215 device_id,
216 status_code,
217 status_msg,
218 outfile_name,
219 request_date,
220 resubmit_date,
221 requested_by,
222 /*responsibility_application_id,
223 responsibility_id,*/
224 concurrent_request_id,
225 /*program_application_id,
226 program_id,
227 program_update_date,*/
228 device_status,
229 reason_id,
230 XFER_LPN_ID
231 FROM wms_device_requests_hist
232 WHERE ROWID = x_rowid
233 for update OF request_id,task_id,business_event_id,organization_id nowait;
234
235 recinfo c%rowtype;
236
237 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
238 BEGIN
239 open c;
240 fetch c into recinfo;
241 if (c%notfound) then
242 close c;
243 IF (l_debug = 1) THEN
244 inv_log_util.trace('inside LOCK ROW c%notfound','WMS_DEVHIST_HANDLER_PKG',9);
245 END IF;
246 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
247 app_exception.raise_exception;
248 end if;
249 close c;
250 IF (l_debug = 1) THEN
251 inv_log_util.trace('inside LOCK ROW','WMS_DEVHIST_HANDLER_PKG',9);
252 END IF;
253 if ( (recinfo.request_id = x_request_id)
254 AND (recinfo.task_id = X_task_id)
255 AND (recinfo.business_event_id = X_business_event_id)
256 AND (recinfo.ORGANIZATION_ID = X_ORGANIZATION_ID)
257 AND ((recinfo.RELATION_ID = X_RELATION_ID)
258 OR ((recinfo.RELATION_ID is null) AND (X_RELATION_ID is null)))
259 AND ((recinfo.sequence_id = X_sequence_id)
260 OR ((recinfo.sequence_id is null) AND (X_sequence_id is null)))
261 AND ((recinfo.task_summary = X_task_summary)
262 OR ((recinfo.task_summary is null) AND (X_task_summary is null)))
263 AND ((recinfo.task_type_id = X_task_type_id)
264 OR ((recinfo.task_type_id is null) AND (X_task_type_id is null)))
265 AND ((recinfo.subinventory_code = X_subinventory_code)
266 OR ((recinfo.subinventory_code is null) AND (X_subinventory_code is null)))
267 AND ((recinfo.locator_id = X_locator_id)
268 OR ((recinfo.locator_id is null) AND (X_locator_id is null)))
269 AND ((recinfo.transfer_org_id = X_transfer_org_id)
270 OR ((recinfo.transfer_org_id is null) AND (X_transfer_org_id is null)))
271 AND ((recinfo.transfer_sub_code = X_transfer_sub_code)
272 OR ((recinfo.transfer_sub_code is null) AND (X_transfer_sub_code is null)))
273 AND ((recinfo.transfer_loc_id = X_transfer_loc_id)
274 OR ((recinfo.transfer_loc_id is null) AND (X_transfer_loc_id is null)))
275 AND ((recinfo.inventory_item_id = X_inventory_item_id)
276 OR ((recinfo.inventory_item_id is null) AND (X_inventory_item_id is null)))
277 AND ((recinfo.revision = X_revision)
278 OR ((recinfo.revision is null) AND (X_revision is null)))
279 AND ((recinfo.uom = X_uom)
280 OR ((recinfo.uom is null) AND (X_uom is null)))
281 AND ((recinfo.lpn_id = X_lpn_id)
282 OR ((recinfo.lpn_id is null) AND (X_lpn_id is null)))
283 AND ((recinfo.transaction_quantity = X_transaction_quantity)
284 OR ((recinfo.transaction_quantity is null) AND (X_transaction_quantity is null)))
285 AND ((recinfo.device_id = X_device_id)
286 OR ((recinfo.device_id is null) AND (X_device_id is null)))
287 AND ((recinfo.status_code = X_status_code)
288 OR ((recinfo.status_code is null) AND (X_status_code is null)))
289 AND ((recinfo.status_msg = X_status_msg)
290 OR ((recinfo.status_msg is null) AND (X_status_msg is null)))
291 AND ((recinfo.outfile_name = X_outfile_name)
292 OR ((recinfo.outfile_name is null) AND (X_outfile_name is null)))
293 AND ((recinfo.request_date = X_request_date)
294 OR ((recinfo.request_date is null) AND (X_request_date is null)))
295 AND ((recinfo.resubmit_date = X_resubmit_date)
296 OR ((recinfo.resubmit_date is null) AND (X_resubmit_date is null)))
297 AND ((recinfo.requested_by = X_requested_by)
298 OR ((recinfo.requested_by is null) AND (X_requested_by is null)))
299 /*AND ((recinfo.responsibility_application_id = X_resp_application_id)
300 OR ((recinfo.responsibility_application_id is null) AND (X_resp_application_id is null)))
301 AND ((recinfo.responsibility_id = X_responsibility_id)
302 OR ((recinfo.responsibility_id is null) AND (X_responsibility_id is null)))*/
303 AND ((recinfo.concurrent_request_id = X_concurrent_request_id)
304 OR ((recinfo.concurrent_request_id is null) AND (X_concurrent_request_id is null)))
305 /*AND ((recinfo.program_application_id = X_program_application_id)
306 OR ((recinfo.program_application_id is null) AND (X_program_application_id is null)))
307 AND ((recinfo.program_id = X_program_id)
308 OR ((recinfo.program_id is null) AND (X_program_id is null)))
309 AND ((recinfo.program_update_date = X_program_update_date)
310 OR ((recinfo.program_update_date is null) AND (X_program_update_date is null)))*/
311 AND ((recinfo.device_status = X_device_status)
312 OR ((recinfo.device_status is null) AND (X_device_status is null)))
313 AND ((recinfo.reason_id = X_reason_id)--
314 OR ((recinfo.reason_id is null) AND (X_reason_id is null)))
315 AND ((recinfo.XFER_LPN_ID = X_XFER_LPN_ID)
316 OR ((recinfo.XFER_LPN_ID is null) AND (X_XFER_LPN_ID is null)))
317 ) then
318 return;
319 ELSE
320 IF (l_debug = 1) THEN
321 inv_log_util.trace('inside LOCK ROW WILL SHOW CHNAGED MESG','WMS_DEVHIST_HANDLER_PKG',9);
322 END IF;
323 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
324 app_exception.raise_exception;
325 end if;
326 end LOCK_ROW;
327
328 procedure UPDATE_ROW (
329 X_ROWID in VARCHAR2,
333 X_SEQUENCE_ID IN NUMBER,
330 X_REQUEST_ID IN NUMBER,
331 X_TASK_ID IN NUMBER,
332 X_RELATION_ID IN NUMBER,
334 X_TASK_SUMMARY IN VARCHAR2,
335 X_TASK_TYPE_ID IN NUMBER,
336 X_BUSINESS_EVENT_ID IN NUMBER,
337 X_ORGANIZATION_ID IN NUMBER,
338 X_SUBINVENTORY_CODE IN VARCHAR2,
339 X_LOCATOR_ID IN NUMBER,
340 X_TRANSFER_ORG_ID IN NUMBER,
341 X_TRANSFER_SUB_CODE IN VARCHAR2,
342 X_TRANSFER_LOC_ID IN NUMBER,
343 X_INVENTORY_ITEM_ID IN NUMBER,
344 X_REVISION IN VARCHAR2,
345 X_UOM IN VARCHAR2,
346 X_LOT_NUMBER IN VARCHAR2,
347 X_LOT_QTY IN NUMBER,
348 X_SERIAL_NUMBER IN VARCHAR2,
349 X_LPN_ID IN NUMBER,
350 X_TRANSACTION_QUANTITY IN NUMBER,
351 X_DEVICE_ID IN NUMBER,
352 X_STATUS_CODE IN VARCHAR2,
353 X_STATUS_MSG IN VARCHAR2,
354 X_OUTFILE_NAME IN VARCHAR2,
355 X_REQUEST_DATE IN DATE,
356 X_RESUBMIT_DATE IN DATE,
357 X_REQUESTED_BY IN NUMBER,
358 X_RESP_APPLICATION_ID IN NUMBER,
359 X_RESPONSIBILITY_ID IN NUMBER,
360 X_CONCURRENT_REQUEST_ID IN NUMBER,
361 X_PROGRAM_APPLICATION_ID IN NUMBER,
362 X_PROGRAM_ID IN NUMBER,
363 X_PROGRAM_UPDATE_DATE IN NUMBER,
364 X_CREATION_DATE IN DATE,
365 X_CREATED_BY IN NUMBER,
366 X_LAST_UPDATE_DATE IN DATE,
367 X_LAST_UPDATED_BY IN NUMBER,
368 X_LAST_UPDATE_LOGIN IN NUMBER,
369 X_DEVICE_STATUS IN VARCHAR2,
370 X_REASON_ID IN NUMBER,
371 X_XFER_LPN_ID IN NUMBER
372 ) is
373
374 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
375 BEGIN
376
377 /*
378 IF (l_debug = 1) THEN
379 inv_log_util.trace('inside updating for HIST form','WMS_DEVHIST_HANDLER_PKG',9);
380 END IF;
381
382 */
383
384 update wms_device_requests_hist set
385 REQUEST_ID = X_REQUEST_ID,
386 TASK_ID = X_TASK_ID ,
387 relation_id = x_RELATION_ID,
388 sequence_id = X_SEQUENCE_ID,
389 task_summary = X_task_summary,
390 task_type_id = X_task_type_id,
391 business_event_id = X_business_event_id,
392 organization_id = X_organization_id,
393 subinventory_code = X_subinventory_code,
394 locator_id = X_locator_id,
395 transfer_org_id = X_transfer_org_id,
396 transfer_sub_code = X_transfer_sub_code,
397 transfer_loc_id = X_transfer_loc_id,
398 inventory_item_id = X_inventory_item_id,
399 revision = X_revision,
400 uom = X_uom,
401 lot_number = X_lot_number,
402 lot_qty = X_lot_qty,
403 serial_number = X_serial_number,
404 lpn_id = X_lpn_id,
405 transaction_quantity = X_transaction_quantity,
406 device_id = X_device_id,
407 status_code = X_status_code,
408 status_msg = X_status_msg,
409 outfile_name = X_outfile_name,
410 request_date = X_request_date,
411 resubmit_date = X_resubmit_date,
412 requested_by = X_requested_by,
413 responsibility_application_id = X_resp_application_id,
414 responsibility_id = X_responsibility_id,
415 concurrent_request_id = X_concurrent_request_id,
416 program_application_id = X_program_application_id,
417 program_id = X_program_id,
418 program_update_date = X_program_update_date,
419 creation_date = X_creation_date,
420 created_by = X_created_by,
421 last_update_date = X_last_update_date,
422 last_updated_by = X_last_updated_by,
423 last_update_login = X_last_update_login,
424 device_status = X_device_status,
425 reason_id = X_reason_id,
426 xfer_lpn_id = x_xfer_lpn_id
427 WHERE rowid = x_rowid;
428
429
430 if (sql%notfound) then
431 raise no_data_found;
432 end if;
433
434 end UPDATE_ROW;
435
436 procedure DELETE_ROW (
437 X_ROWID in varchar2
438 ) is
439 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
440 begin
441
442 /*
443 IF (l_debug = 1) THEN
444 inv_log_util.trace('inside inserting for HIST form','WMS_DEVHIST_HANDLER_PKG',9);
445 END IF;
446 */
447
448 delete from wms_device_requests_hist
449 WHERE ROWID=X_ROWID;
450
451 if (sql%notfound) then
452 raise no_data_found;
453 end if;
454
455 end DELETE_ROW;
456
457 procedure UPDATE_CHILD_RECORDS
458 (
459 X_REQUEST_ID IN NUMBER,
460 X_TASK_ID IN NUMBER,
461 X_RELATION_ID IN NUMBER,
462 X_SEQUENCE_ID IN NUMBER,
463 X_TASK_TYPE_ID IN NUMBER,
464 X_BUSINESS_EVENT_ID IN NUMBER,
465 X_ORGANIZATION_ID IN NUMBER,
469 X_TRANSFER_SUB_CODE IN VARCHAR2,
466 X_SUBINVENTORY_CODE IN VARCHAR2,
467 X_LOCATOR_ID IN NUMBER,
468 X_TRANSFER_ORG_ID IN NUMBER,
470 X_TRANSFER_LOC_ID IN NUMBER,
471 X_INVENTORY_ITEM_ID IN NUMBER,
472 X_REVISION IN VARCHAR2,
473 X_UOM IN VARCHAR2,
474 X_LPN_ID IN NUMBER,
475 X_TRANSACTION_QUANTITY IN NUMBER,
476 X_DEVICE_ID IN NUMBER,
477 X_STATUS_CODE IN VARCHAR2,
478 X_STATUS_MSG IN VARCHAR2,
479 X_OUTFILE_NAME IN VARCHAR2,
480 X_REQUEST_DATE IN DATE,
481 X_RESUBMIT_DATE IN DATE,
482 X_REQUESTED_BY IN NUMBER,
483 X_RESP_APPLICATION_ID IN NUMBER,
484 X_RESPONSIBILITY_ID IN NUMBER,
485 X_CONCURRENT_REQUEST_ID IN NUMBER,
486 X_CREATION_DATE IN DATE,
487 X_CREATED_BY IN NUMBER,
488 X_LAST_UPDATE_DATE IN DATE,
489 X_LAST_UPDATED_BY IN NUMBER,
490 X_LAST_UPDATE_LOGIN IN NUMBER,
491 X_DEVICE_STATUS IN VARCHAR2,
492 X_REASON_ID IN NUMBER,
493 X_XFER_LPN_ID IN NUMBER
494 ) is
495
496
497 CURSOR C_child_records IS SELECT
498 request_id,
499 task_id,
500 relation_id,
501 sequence_id,
502 business_event_id
503 FROM wms_device_requests_hist
504 WHERE REQUEST_ID = X_REQUEST_ID
505 AND TASK_ID = X_TASK_ID
506 --AND relation_id = x_relation_id
507 AND Nvl(sequence_id,-1) = Nvl(x_sequence_id,-1)
508 AND task_summary = 'N'
509 AND business_event_id = X_business_event_id
510 FOR UPDATE OF
511 transaction_quantity,transfer_loc_id,transfer_sub_code,reason_id,device_status NOWAIT;
512
513 recinfo_child_records C_child_records%ROWTYPE;
514
515 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
516 BEGIN
517 /*
518 IF (l_debug = 1) THEN
519 inv_log_util.trace(' '||X_REQUEST_ID,'WMS_DEVHIST_HANDLER_PKG',9);
520 inv_log_util.trace(' '||X_TASK_ID,'WMS_DEVHIST_HANDLER_PKG',9);
521 inv_log_util.trace(' ' ||x_RELATION_ID,'WMS_DEVHIST_HANDLER_PKG',9);
522 inv_log_util.trace(''||X_SEQUENCE_ID,'WMS_DEVHIST_HANDLER_PKG',9);
523 inv_log_util.trace(' '||X_business_event_id,'WMS_DEVHIST_HANDLER_PKG',9);
524 END IF;
525 */
526 OPEN C_child_records;
527 FETCH C_child_records INTO recinfo_child_records;
528 IF (C_child_records%notfound) THEN
529 CLOSE C_child_records;
530 IF (l_debug = 1) THEN
531 inv_log_util.trace('Return:No chldRec to update','WMS_DEVHIST_HANDLER_PKG',9);
532 END IF;
533 --fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
534 RETURN;
535 ELSE
536 IF (l_debug = 1) THEN
537 inv_log_util.trace('updating the child rec','WMS_DEVHIST_HANDLER_PKG',9);
538 END IF;
539 update wms_device_requests_hist set
540 task_type_id = X_task_type_id,
541 organization_id = X_organization_id,
542 subinventory_code = X_subinventory_code,
543 locator_id = X_locator_id,
544 transfer_org_id = X_transfer_org_id,
545 transfer_sub_code = X_transfer_sub_code,
546 transfer_loc_id = X_transfer_loc_id,
547 inventory_item_id = X_inventory_item_id,
548 revision = X_revision,
549 uom = X_uom,
550 lpn_id = X_lpn_id,
551 transaction_quantity = X_transaction_quantity,
552 device_id = X_device_id,
553 status_code = X_status_code,
554 status_msg = X_status_msg,
555 outfile_name = X_outfile_name,
556 request_date = X_request_date,
557 resubmit_date = X_resubmit_date,
558 requested_by = X_requested_by,
559 responsibility_application_id = X_resp_application_id,
560 responsibility_id = X_responsibility_id,
561 concurrent_request_id = X_concurrent_request_id,
562 creation_date = X_creation_date,
563 created_by = X_created_by,
564 last_update_date = X_last_update_date,
565 last_updated_by = X_last_updated_by,
566 last_update_login = X_last_update_login,
567 device_status = X_device_status,
568 reason_id = X_reason_id,
569 xfer_lpn_id = x_xfer_lpn_id,
570 relation_id = x_relation_id
571 WHERE REQUEST_ID = X_REQUEST_ID
572 AND TASK_ID = X_TASK_ID
573 --AND relation_id = x_relation_id
574 AND Nvl(sequence_id,-1) = Nvl(x_sequence_id,-1)
575 AND task_summary = 'N'
576 AND business_event_id = x_business_event_id;
577
578 END IF;
579 CLOSE C_child_records;
580
581 EXCEPTION
582 WHEN OTHERS THEN
583 IF (l_debug = 1) THEN
584 inv_log_util.trace('inside exception'||SQLCODE,'WMS_DEVHIST_HANDLER_PKG',9);
585 END IF;
586 IF SQLCODE = -54 THEN --record locked by other session
587 fnd_message.set_name('FND','FORM_RECORD_CHANGED');
588 app_exception.raise_exception;
589 END IF;
590
591
592 end UPDATE_CHILD_RECORDS;
593
594 procedure delete_CHILD_RECORDS
595 (X_REQUEST_ID IN NUMBER,
596 X_TASK_ID IN NUMBER,
597 X_RELATION_ID IN NUMBER,
598 X_SEQUENCE_ID IN NUMBER,
599 X_BUSINESS_EVENT_ID IN NUMBER
603 BEGIN
600 ) IS
601
602 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
604 /*
605 IF (l_debug = 1) THEN
606 inv_log_util.trace('Inside delete_CHILD_RECORDS','WMS_DEVHIST_HANDLER_PKG',9);
607 END IF;
608 */
609 delete from wms_device_requests_hist
610 WHERE REQUEST_ID = X_REQUEST_ID
611 AND TASK_ID = X_TASK_ID
612 AND relation_id = x_RELATION_ID
613 AND Nvl(sequence_id,-1) = Nvl(x_sequence_id,-1)
614 AND task_summary = 'N'
615 AND business_event_id = X_business_event_id;
616
617 END delete_child_records;
618
619
620 procedure lock_child_row (
621 X_ROWID in VARCHAR2,
622 X_REQUEST_ID IN NUMBER,
623 X_TASK_ID IN NUMBER,
624 X_RELATION_ID IN NUMBER,
625 X_BUSINESS_EVENT_ID IN NUMBER,
626 X_ORGANIZATION_ID IN NUMBER,
627 X_LOT_NUMBER IN VARCHAR2,
628 X_LOT_QTY IN NUMBER,
629 X_SERIAL_NUMBER IN VARCHAR2,
630 x_is_new_row IN NUMBER --1 =YES, 0=NO
631 ) is
632 cursor c is SELECT
633 REQUEST_ID ,
634 TASK_ID ,
635 RELATION_ID,
636 business_event_id,
637 organization_id,
638 lot_number,
639 lot_qty,
640 serial_number
641 FROM wms_device_requests_hist
642 WHERE ROWID = x_rowid
643 for update OF lot_number,lot_qty,serial_number nowait;
644
645 recinfo c%rowtype;
646
647 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
648 BEGIN
649 /*
650 IF (l_debug = 1) THEN
651 inv_log_util.trace('Inside lock_CHILD_RECORDS:::'||x_is_new_row,'WMS_DEVHIST_HANDLER_PKG',9);
652 END IF;
653 */
654
655 open c;
656 fetch c into recinfo;
657 if (c%notfound) then
658 close c;
659 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
660 app_exception.raise_exception;
661 end if;
662 close c;
663
664 if ( (recinfo.request_id = x_request_id)
665 AND (recinfo.task_id = X_task_id)
666 AND (recinfo.business_event_id = X_business_event_id)
667 AND (recinfo.ORGANIZATION_ID = X_ORGANIZATION_ID)
668 AND ((recinfo.RELATION_ID = X_RELATION_ID)
669 OR ((recinfo.RELATION_ID is null) AND (X_RELATION_ID is null)))
670 AND ((recinfo.lot_number = X_lot_number)
671 OR ((recinfo.lot_number is null) AND (X_lot_number is null)))
672 AND ((recinfo.lot_qty = X_lot_qty)
673 OR ((recinfo.lot_qty is null) AND (X_lot_qty is null)))
674 AND ((recinfo.serial_number = X_serial_number)
675 OR ((recinfo.serial_number is null) AND (X_serial_number is null)))
676
677 ) then
678 return;
679 ELSE
680 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
681 app_exception.raise_exception;
682 end if;
683 --END IF;
684
685 END lock_child_row;
686
687
688 END WMS_DEVHIST_HANDLER_PKG;
[Home] [Help]