[Home] [Help]
PACKAGE BODY: APPS.INV_CR_ASN_DETAILS
Source
1 PACKAGE BODY INV_CR_ASN_DETAILS AS
2 /* $Header: INVCRDIB.pls 120.3 2005/10/10 12:55:32 methomas noship $*/
3
4 -- Global constant holding the package name
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_CR_ASN_DETAILS';
6
7 PROCEDURE print_debug(p_err_msg VARCHAR2,
8 p_level NUMBER)
9 IS
10 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
11 BEGIN
12
13 IF (l_debug = 1) THEN
14 inv_mobile_helper_functions.tracelog
15 (p_err_msg => p_err_msg,
16 p_module => 'INV_CR_ASN_DETAILS',
17 p_level => p_level);
18 END IF;
19
20 -- dbms_output.put_line(p_err_msg);
21 END print_debug;
22
23 PROCEDURE insertrows(p_create_asn_details_rec IN OUT nocopy asn_details_rec_tp )
24 IS
25 l_userid NUMBER;
26 l_loginid NUMBER;
27
28 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
29 BEGIN
30 l_userid := fnd_global.user_id;
31 l_loginid := fnd_global.login_id;
32
33 insert into WMS_ASN_DETAILS (
34 GROUP_ID ,
35 SHIPMENT_NUM ,
36 ORGANIZATION_ID ,
37 DISCREPANCY_REPORTING_CONTEXT ,
38 ITEM_ID ,
39 QUANTITY_EXPECTED ,
40 QUANTITY_ACTUAL ,
41 UNIT_OF_MEASURE_EXPECTED ,
42 UNIT_OF_MEASURE_ACTUAL ,
43 LPN_EXPECTED ,
44 LPN_ACTUAL ,
45 ITEM_REVISION_EXPECTED ,
46 ITEM_REVISION_ACTUAL ,
47 LOT_NUMBER_EXPECTED ,
48 LOT_NUMBER_ACTUAL ,
49 SERIAL_NUMBER_EXPECTED ,
50 SERIAL_NUMBER_ACTUAL ,
51 VENDOR_ID ,
52 VENDOR_SITE_ID ,
53 TRANSACTION_DATE ,
54 LAST_UPDATE_DATE ,
55 LAST_UPDATED_BY ,
56 CREATION_DATE ,
57 CREATED_BY ,
58 LAST_UPDATE_LOGIN
59 ) values
60 (
61 p_create_asn_details_rec.GROUP_ID ,
62 p_create_asn_details_rec.SHIPMENT_NUM ,
63 p_create_asn_details_rec.ORGANIZATION_ID ,
64 p_create_asn_details_rec.DISCREPANCY_REPORTING_CONTEXT ,
65 p_create_asn_details_rec.ITEM_ID ,
66 p_create_asn_details_rec.QUANTITY_EXPECTED ,
67 p_create_asn_details_rec.QUANTITY_ACTUAL ,
68 p_create_asn_details_rec.UNIT_OF_MEASURE_EXPECTED ,
69 p_create_asn_details_rec.UNIT_OF_MEASURE_ACTUAL ,
70 p_create_asn_details_rec.LPN_EXPECTED ,
71 p_create_asn_details_rec.LPN_ACTUAL ,
72 p_create_asn_details_rec.ITEM_REVISION_EXPECTED ,
73 p_create_asn_details_rec.ITEM_REVISION_ACTUAL ,
74 p_create_asn_details_rec.LOT_NUMBER_EXPECTED ,
75 p_create_asn_details_rec.LOT_NUMBER_ACTUAL ,
76 p_create_asn_details_rec.SERIAL_NUMBER_EXPECTED ,
77 p_create_asn_details_rec.SERIAL_NUMBER_ACTUAL ,
78 p_create_asn_details_rec.VENDOR_ID ,
79 p_create_asn_details_rec.VENDOR_SITE_ID ,
80 p_create_asn_details_rec.TRANSACTION_DATE ,
81 sysdate,
82 l_userid,
83 sysdate,
84 l_userid,
85 l_loginid
86 );
87
88 END insertrows;
89
90 PROCEDURE insert_asn_item_details(p_create_asn_details_rec IN OUT nocopy asn_details_rec_tp
91 )
92 IS
93
94 l_exists varchar2(10);
95 l_userid NUMBER;
96 l_loginid NUMBER;
97
98 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
99 BEGIN
100
101 l_userid := fnd_global.user_id;
102 l_loginid := fnd_global.login_id;
103
104 Begin
105 select 1 into l_exists
106 from WMS_ASN_DETAILS
107 where shipment_num = p_create_asn_details_rec.SHIPMENT_NUM
108 and organization_id = p_create_asn_details_rec.ORGANIZATION_ID
109 and discrepancy_reporting_context = p_create_asn_details_rec.DISCREPANCY_REPORTING_CONTEXT
110 and ITEM_ID = p_create_asn_details_rec.ITEM_ID
111 ;
112
113 update WMS_ASN_DETAILS set quantity_actual = (nvl(quantity_actual,0) + p_create_asn_details_rec.QUANTITY_ACTUAL) ,
114 unit_of_measure_actual = p_create_asn_details_rec.unit_of_measure_actual,
115 item_revision_actual = p_create_asn_details_rec.item_revision_actual,
116 transaction_date = sysdate,
117 last_update_date = sysdate,
118 last_updated_by = l_userid,
119 last_update_login = l_loginid
120 where shipment_num = p_create_asn_details_rec.SHIPMENT_NUM
121 and organization_id = p_create_asn_details_rec.ORGANIZATION_ID
122 and discrepancy_reporting_context = p_create_asn_details_rec.DISCREPANCY_REPORTING_CONTEXT
123 and ITEM_ID = p_create_asn_details_rec.ITEM_ID
124 ;
125
126 Exception
127 When No_data_found then
128 insertrows(p_create_asn_details_rec);
129 End;
130 END insert_asn_item_details;
131
132 PROCEDURE insert_asn_item_details_intf(p_create_asn_details_rec IN OUT nocopy asn_details_rec_tp
133 )
134 IS
135
136 l_exists varchar2(10);
137 l_userid NUMBER;
138 l_loginid NUMBER;
139
140 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
141 BEGIN
142
143 l_userid := fnd_global.user_id;
144 l_loginid := fnd_global.login_id;
145
146 Begin
147 select 1 into l_exists
148 from WMS_ASN_DETAILS
149 where shipment_num = p_create_asn_details_rec.SHIPMENT_NUM
150 and organization_id = p_create_asn_details_rec.ORGANIZATION_ID
151 and discrepancy_reporting_context = p_create_asn_details_rec.DISCREPANCY_REPORTING_CONTEXT
152 and ITEM_ID = p_create_asn_details_rec.ITEM_ID
153 ;
154
155 update WMS_ASN_DETAILS set quantity_expected = ( quantity_expected + p_create_asn_details_rec.QUANTITY_EXPECTED) ,
156 transaction_date = sysdate,
157 last_update_date = sysdate,
158 last_updated_by = l_userid,
159 last_update_login = l_loginid
160 where shipment_num = p_create_asn_details_rec.SHIPMENT_NUM
161 and organization_id = p_create_asn_details_rec.ORGANIZATION_ID
162 and discrepancy_reporting_context = p_create_asn_details_rec.DISCREPANCY_REPORTING_CONTEXT
163 and ITEM_ID = p_create_asn_details_rec.ITEM_ID
164 ;
165
166 Exception
167 When No_data_found then
168 insertrows(p_create_asn_details_rec);
169 End;
170 END insert_asn_item_details_intf;
171
172
173 PROCEDURE initialize_details(p_create_asn_details_rec IN OUT nocopy asn_details_rec_tp )
174 IS
175 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
176 Begin
177 -- p_create_asn_details_rec.GROUP_ID := to_number(null);
178 -- p_create_asn_details_rec.SHIPMENT_NUM := null;
179 -- p_create_asn_details_rec.ORGANIZATION_ID := to_number(null);
180 -- p_create_asn_details_rec.ITEM_ID := to_number(null);
181
182 p_create_asn_details_rec.DISCREPANCY_REPORTING_CONTEXT := null;
183 p_create_asn_details_rec.QUANTITY_EXPECTED := to_number(null);
184 p_create_asn_details_rec.QUANTITY_ACTUAL:= to_number(null);
185 p_create_asn_details_rec.UNIT_OF_MEASURE_EXPECTED := null;
186 p_create_asn_details_rec.UNIT_OF_MEASURE_ACTUAL := null;
187 p_create_asn_details_rec.LPN_EXPECTED := null;
188 p_create_asn_details_rec.LPN_ACTUAL := null;
189 p_create_asn_details_rec.ITEM_REVISION_EXPECTED := null;
190 p_create_asn_details_rec.ITEM_REVISION_ACTUAL := null;
191 p_create_asn_details_rec.LOT_NUMBER_EXPECTED := null;
192 p_create_asn_details_rec.LOT_NUMBER_ACTUAL := null;
193 p_create_asn_details_rec.SERIAL_NUMBER_EXPECTED := null;
194 p_create_asn_details_rec.SERIAL_NUMBER_ACTUAL := null;
195 p_create_asn_details_rec.VENDOR_ID := to_number(null);
196 p_create_asn_details_rec.VENDOR_SITE_ID := to_number(null);
197 p_create_asn_details_rec.TRANSACTION_DATE := to_date(null);
198 End;
199
200
201
202 PROCEDURE insert_asn_lot_details(p_create_asn_details_rec IN OUT nocopy asn_details_rec_tp )
203 IS
204
205 l_exists varchar2(10);
206
207 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
208 BEGIN
209
210 Begin
211 select 1 into l_exists
212 from WMS_ASN_DETAILS
213 where shipment_num = p_create_asn_details_rec.SHIPMENT_NUM
214 and organization_id = p_create_asn_details_rec.ORGANIZATION_ID
215 and discrepancy_reporting_context = p_create_asn_details_rec.DISCREPANCY_REPORTING_CONTEXT
216 and ITEM_ID = p_create_asn_details_rec.ITEM_ID
217 and lpn_expected = p_create_asn_details_rec.lpn_expected
218 and lot_number_expected = p_create_asn_details_rec.lot_number_expected
219 and rownum < 2
220 ;
221
222 -- Lot is already there nothing to insert
223
224 Exception
225 When No_data_found then
226 insertrows(p_create_asn_details_rec);
227 End;
228 END insert_asn_lot_details;
229
230
231
232 PROCEDURE insert_asn_lpn_details(p_create_asn_details_rec IN OUT nocopy asn_details_rec_tp )
233
234 IS
235
236 l_exists varchar2(10);
237
238 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
239 BEGIN
240
241 Begin
242 select 1 into l_exists
243 from WMS_ASN_DETAILS
244 where shipment_num = p_create_asn_details_rec.SHIPMENT_NUM
245 and organization_id = p_create_asn_details_rec.ORGANIZATION_ID
246 and discrepancy_reporting_context = p_create_asn_details_rec.DISCREPANCY_REPORTING_CONTEXT
247 and ITEM_ID = p_create_asn_details_rec.ITEM_ID
248 and lpn_expected = p_create_asn_details_rec.lpn_expected
249 and rownum < 2
250 ;
251
252 -- LPN is already there nothing to insert
253
254 Exception
255 When No_data_found then
256 insertrows(p_create_asn_details_rec);
257 End;
258 END insert_asn_lpn_details;
259
260
261 PROCEDURE update_asn_lpn_details(p_create_asn_details_rec IN OUT nocopy asn_details_rec_tp )
262
263 IS
264
265 l_exists varchar2(10);
266 l_userid NUMBER;
267 l_loginid NUMBER;
268
269 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
270 BEGIN
271
272 Begin
273 select 1 into l_exists
274 from WMS_ASN_DETAILS
275 where shipment_num = p_create_asn_details_rec.SHIPMENT_NUM
276 and organization_id = p_create_asn_details_rec.ORGANIZATION_ID
277 and discrepancy_reporting_context = p_create_asn_details_rec.DISCREPANCY_REPORTING_CONTEXT
278 and ITEM_ID = p_create_asn_details_rec.ITEM_ID
279 and ( lpn_expected = p_create_asn_details_rec.lpn_actual
280 or lpn_actual = p_create_asn_details_rec.lpn_actual )
281 and rownum < 2
282 ;
283
284 -- LPN is already there update the quantity
285 update WMS_ASN_DETAILS set
286 quantity_actual = nvl(quantity_actual, 0) + p_create_asn_details_rec.QUANTITY_ACTUAL,
287 lpn_actual = p_create_asn_details_rec.LPN_ACTUAL ,
288 last_update_date = sysdate,
289 last_updated_by = l_userid,
290 last_update_login = l_loginid
291 where shipment_num = p_create_asn_details_rec.SHIPMENT_NUM
292 and organization_id = p_create_asn_details_rec.ORGANIZATION_ID
293 and discrepancy_reporting_context = p_create_asn_details_rec.DISCREPANCY_REPORTING_CONTEXT
294 and ITEM_ID = p_create_asn_details_rec.ITEM_ID
295 and ( lpn_expected = p_create_asn_details_rec.lpn_actual
296 or lpn_actual = p_create_asn_details_rec.lpn_actual )
297 ;
298
299 Exception
300 When No_data_found then
301 insertrows(p_create_asn_details_rec);
302 End;
303 END update_asn_lpn_details;
304
305
306 PROCEDURE update_asn_lot_details(p_create_asn_details_rec IN OUT nocopy asn_details_rec_tp )
307 IS
308
309 l_exists varchar2(10);
310 l_userid NUMBER;
311 l_loginid NUMBER;
312
313 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
314 BEGIN
315
316 -- LOT is already there update the quantity
317 -- Bug 2096130
318 -- If Same Lot is received for same LPN we need to update the
319 -- quantity of Lot added to the prev quantity.
320
321 Begin
322 select 1 into l_exists
323 from WMS_ASN_DETAILS
324 where shipment_num = p_create_asn_details_rec.SHIPMENT_NUM
325 and organization_id = p_create_asn_details_rec.ORGANIZATION_ID
326 and discrepancy_reporting_context = p_create_asn_details_rec.DISCREPANCY_REPORTING_CONTEXT
327 and ITEM_ID = p_create_asn_details_rec.ITEM_ID
328 and ( (lot_number_expected = p_create_asn_details_rec.lot_number_actual and lpn_expected = p_create_asn_details_rec.lpn_actual)
329 or
330 (lot_number_actual = p_create_asn_details_rec.lot_number_actual and lpn_actual = p_create_asn_details_rec.lpn_actual)
331 )
332 and rownum < 2
333 ;
334
335 update WMS_ASN_DETAILS set
336 quantity_actual = nvl(quantity_actual,0) + p_create_asn_details_rec.QUANTITY_ACTUAL ,
337 lot_number_actual = p_create_asn_details_rec.LOT_NUMBER_ACTUAL,
338 lpn_actual = p_create_asn_details_rec.LPN_ACTUAL,
339 last_update_date = sysdate,
340 last_updated_by = l_userid,
341 last_update_login = l_loginid
342 where shipment_num = p_create_asn_details_rec.SHIPMENT_NUM
343 and organization_id = p_create_asn_details_rec.ORGANIZATION_ID
344 and discrepancy_reporting_context = p_create_asn_details_rec.DISCREPANCY_REPORTING_CONTEXT
345 and ITEM_ID = p_create_asn_details_rec.ITEM_ID
346 and ( (lot_number_expected = p_create_asn_details_rec.lot_number_actual and lpn_expected = p_create_asn_details_rec.lpn_actual)
347 or
348 (lot_number_actual = p_create_asn_details_rec.lot_number_actual and lpn_actual = p_create_asn_details_rec.lpn_actual)
349 )
350 ;
351
352 Exception
353 When No_data_found then
354 insertrows(p_create_asn_details_rec);
355 End;
356 END update_asn_lot_details;
357
358
359 PROCEDURE insert_asn_ser_details_exp(p_create_asn_details_rec IN OUT nocopy asn_details_rec_tp )
360 IS
361 l_exists varchar2(10);
362 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
363 BEGIN
364
365 Begin
366 select 1 into l_exists
367 from WMS_ASN_DETAILS
368 where shipment_num = p_create_asn_details_rec.SHIPMENT_NUM
369 and organization_id = p_create_asn_details_rec.ORGANIZATION_ID
370 and discrepancy_reporting_context = p_create_asn_details_rec.DISCREPANCY_REPORTING_CONTEXT
371 and ITEM_ID = p_create_asn_details_rec.ITEM_ID
372 and lpn_expected = p_create_asn_details_rec.lpn_expected
373 and serial_number_expected = p_create_asn_details_rec.serial_number_expected
374 and rownum < 2
375 ;
376
377 -- Serial is already there nothing to insert
378
379 Exception
380 When No_data_found then
381 insertrows(p_create_asn_details_rec);
382 End;
383 END insert_asn_ser_details_exp;
384
385
386
387 PROCEDURE insert_asn_ser_details_act(p_create_asn_details_rec IN OUT nocopy asn_details_rec_tp )
388 IS
389 l_exists varchar2(10);
390 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
391 BEGIN
392
393 Begin
394 select 1 into l_exists
395 from WMS_ASN_DETAILS
396 where shipment_num = p_create_asn_details_rec.SHIPMENT_NUM
397 and organization_id = p_create_asn_details_rec.ORGANIZATION_ID
398 and discrepancy_reporting_context = p_create_asn_details_rec.DISCREPANCY_REPORTING_CONTEXT
399 and ITEM_ID = p_create_asn_details_rec.ITEM_ID
400 and lpn_actual = p_create_asn_details_rec.lpn_actual
401 and serial_number_actual = p_create_asn_details_rec.serial_number_actual
402 and rownum < 2
403 ;
404
405 -- Serial is already there nothing to insert
406
407 Exception
408 When No_data_found then
409 insertrows(p_create_asn_details_rec);
410 End;
411 END insert_asn_ser_details_act;
412
413
414 Function Check_discrepancy
415 ( p_shipment_num IN varchar2,
416 p_organization_id IN number
417 )
418 return boolean
419 IS
420 l_exists number;
421 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
422 Begin
423 For c_item_details in ( select * from WMS_ASN_DETAILS
424 where shipment_num = p_shipment_num
425 and organization_id = p_organization_id
426 order by discrepancy_reporting_context
427 )
428 Loop
429
430 -- Check Item
431 if c_item_details.discrepancy_reporting_context = 'I'
432 then
433 if (
434 (c_item_details.QUANTITY_EXPECTED <> c_item_details.QUANTITY_ACTUAL)
435 or
436 (c_item_details.UNIT_OF_MEASURE_EXPECTED <> c_item_details.UNIT_OF_MEASURE_ACTUAL)
437 or
438 (c_item_details.ITEM_REVISION_EXPECTED <> c_item_details.ITEM_REVISION_ACTUAL)
439 )
440 then
441 return FALSE;
442 end if;
443 end if;
444
445 -- Check LPN
446 if c_item_details.discrepancy_reporting_context = 'L'
447 then
448 if (
449 (c_item_details.LPN_EXPECTED <> c_item_details.LPN_ACTUAL)
450 or
451 (c_item_details.QUANTITY_EXPECTED <> c_item_details.QUANTITY_ACTUAL)
452 )
453 then
454 return FALSE;
455 end if;
456 end if;
457
458 -- Check LOT
459 if c_item_details.discrepancy_reporting_context = 'O'
460 then
461 if (
462 (c_item_details.LPN_EXPECTED <> c_item_details.LPN_ACTUAL)
463 or
464 (c_item_details.LOT_NUMBER_EXPECTED <> c_item_details.LOT_NUMBER_ACTUAL)
465 or
466 (c_item_details.QUANTITY_EXPECTED <> c_item_details.QUANTITY_ACTUAL)
467
468 )
469 then
470 return FALSE;
471 end if;
472 end if;
473
474 -- Check Serial
475 if c_item_details.discrepancy_reporting_context = 'S'
476 then
477 if c_item_details.serial_number_expected is not null
478 then
479 Begin
480 select 1
481 into l_exists
482 from WMS_ASN_DETAILS
483 where shipment_num=p_shipment_num
484 and organization_id = p_organization_id
485 and serial_number_actual = c_item_details.serial_number_expected
486 and lpn_actual = c_item_details.lpn_expected
487 ;
488 Exception
489 when others then return FALSE;
490 End;
491 end if;
492
493 if c_item_details.serial_number_actual is not null
494 then
495 Begin
496 select 1
497 into l_exists
498 from WMS_ASN_DETAILS
499 where shipment_num=p_shipment_num
500 and organization_id = p_organization_id
501 and serial_number_expected = c_item_details.serial_number_actual
502 and lpn_expected = c_item_details.lpn_actual
503 ;
504 Exception
505 when others then return FALSE;
506 End;
507 end if;
508
509
510 end if;
511
512 End Loop;
513
514 return TRUE;
515 End Check_discrepancy;
516
517
518
519 Procedure CREATE_ASN_DETAILS
520 (p_organization_id IN number,
521 p_group_id IN NUMBER,
522 p_rcv_rcpt_rec IN OUT nocopy inv_rcv_std_rcpt_apis.rcv_enter_receipts_rec_tp,
523 p_rcv_transaction_rec IN OUT nocopy inv_rcv_std_rcpt_apis.rcv_transaction_rec_tp,
524 p_rcpt_lot_qty_rec_tb IN OUT nocopy inv_rcv_std_rcpt_apis.rcpt_lot_qty_rec_tb_tp,
525 p_interface_transaction_id IN number,
526 x_status OUT nocopy varchar2,
527 x_message OUT nocopy varchar2)
528 IS
529
530 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
531 l_msg_count NUMBER;
532 l_msg_data VARCHAR2(4000);
533 l_progress VARCHAR2(10);
534 l_create_asn_details_rec asn_details_rec_tp ;
535 l_shipped_quantity number;
536 l_received_quantity number;
537 l_lpn_quantity_expected number;
538 l_license_plate_number varchar2(30);
539 l_lpn_quantity_actual number;
540 l_license_plate_actual varchar2(30);
541 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
542 l_lot_number_expected varchar2(80);
543 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
544 l_lot_number_actual varchar2(80);
545 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
546 p_lot_number varchar2(80);
547 p_num_lots number;
548 p_lot_quantity number;
549
550 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
551 BEGIN
552
553 SAVEPOINT crt_asn_details;
554 x_status := FND_API.G_RET_STS_SUCCESS;
555
556
557 l_progress := '10';
558 IF (l_debug = 1) THEN
559 print_debug('Create ASN Details: '|| l_progress ||' '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
560 END IF;
561
562 IF (l_debug = 1) THEN
563 print_debug('ASN Details ', 1);
564 print_debug('Group Id = '|| p_group_id, 1);
565 print_debug('Organization Id = '|| p_organization_id, 1);
566 print_debug('Shipment Number = '|| p_rcv_rcpt_rec.rcv_shipment_number, 1);
567 print_debug('Item Id = '|| p_rcv_rcpt_rec.item_id, 1);
568 print_debug('UOM = '|| p_rcv_rcpt_rec.primary_uom, 1);
569 print_debug('Revision = '|| p_rcv_rcpt_rec.item_revision, 1);
570 END IF;
571
572 select quantity_received,
573 quantity_shipped
574 into l_received_quantity ,
575 l_shipped_quantity
576 FROM RCV_SHIPMENT_LINES RSL
577 WHERE RSL.SHIPMENT_LINE_ID = p_rcv_rcpt_rec.rcv_shipment_line_id
578 ;
579
580 IF (l_debug = 1) THEN
581 print_debug('Shipped Quantity = '|| l_shipped_quantity, 1);
582 print_debug('Receivd Quantity = '|| l_received_quantity, 1);
583 END IF;
584
585
586 -- Creating Expected ASN Item Details For Reporting
587 -- Should be called for each item
588
589 l_create_asn_details_rec.group_id := p_group_id;
590 l_create_asn_details_rec.organization_id := p_organization_id;
591 l_create_asn_details_rec.shipment_num := p_rcv_rcpt_rec.rcv_shipment_number;
592 l_create_asn_details_rec.ITEM_ID := p_rcv_rcpt_rec.item_id ;
593 l_create_asn_details_rec.DISCREPANCY_REPORTING_CONTEXT := 'I' ;
594 l_create_asn_details_rec.QUANTITY_EXPECTED := l_shipped_quantity ;
595 l_create_asn_details_rec.UNIT_OF_MEASURE_EXPECTED := p_rcv_rcpt_rec.primary_uom;
596 l_create_asn_details_rec.ITEM_REVISION_EXPECTED := p_rcv_rcpt_rec.item_revision ;
597
598 -- Creating Expected ASN Item Details For Reporting
599 --l_create_asn_details_rec.QUANTITY_ACTUAL := l_received_quantity +
600 -- p_rcv_transaction_rec.transaction_qty;
601
602 l_create_asn_details_rec.QUANTITY_ACTUAL := p_rcv_transaction_rec.transaction_qty;
603
604 IF (l_debug = 1) THEN
605 print_debug('Actual Quantity = '|| l_create_asn_details_rec.QUANTITY_ACTUAL, 1);
606 END IF;
607
608 l_create_asn_details_rec.UNIT_OF_MEASURE_ACTUAL := p_rcv_transaction_rec.transaction_uom;
609 l_create_asn_details_rec.ITEM_REVISION_ACTUAL := p_rcv_transaction_rec.item_revision ;
610
611 -- DAte when the ASN is actually Transacted
612 l_create_asn_details_rec.TRANSACTION_DATE := sysdate;
613
614
615 /* Bug 4551595- Commenting the call to this procedure. Calling it from the header level
616 for all the records in rti for a group id. */
617
618 /*-- Call the API for creating rows.
619 insert_asn_item_details(l_create_asn_details_rec); */
620
621 /* End of fix for Bug 4551595 */
622
623 l_progress := '20';
624 IF (l_debug = 1) THEN
625 print_debug('Create ASN Details: '|| l_progress ||' '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
626 END IF;
627
628 -- May be Needed to Call Only Once..
629 -- Creating The Expectd ASN LPN Details For Reporting
630 -- Need to make a cursor for the following for multiple LPNS..
631 -- and multiple lots
632 -- and call the insert in a loop
633
634 Begin
635 --Update The Actual LPN Quantity and populate the rows.
636
637 IF (l_debug = 1) THEN
638 print_debug('LPN ID'||p_rcv_transaction_rec.lpn_id, 1);
639 END IF;
640
641 l_progress := '30';
642 IF (l_debug = 1) THEN
643 print_debug('Create ASN Details: '|| l_progress ||' '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
644 END IF;
645
646 For c_lpn_actual
647 in (
648 select wlpn.license_plate_number,
649 wlpn.lpn_id
650 from wms_license_plate_numbers wlpn
651 where wlpn.lpn_id = p_rcv_transaction_rec.lpn_id
652 )
653 Loop
654
655 initialize_details(l_create_asn_details_rec);
656 l_create_asn_details_rec.DISCREPANCY_REPORTING_CONTEXT := 'L' ;
657 l_create_asn_details_rec.LPN_ACTUAL := c_lpn_actual.license_plate_number;
658
659 -- Should Insert Transaction Quantity Here
660 l_create_asn_details_rec.QUANTITY_ACTUAL := p_rcv_transaction_rec.transaction_qty ;
661
662 -- Need to do only once
663 update_asn_lpn_details(l_create_asn_details_rec);
664
665 -- Insert The Lot Related Rows..
666 -- New code to get the LOT INFO
667 --
668 p_num_lots := p_rcpt_lot_qty_rec_tb.COUNT ;
669
670 -- if p_num_lots is 0 then just process serial
671 -- Otherwise process Lots and then serials
672 --
673
674
675 -- if No Lots so just process serial
676 if p_num_lots > 0 then
677 FOR i IN 1..p_rcpt_lot_qty_rec_tb.COUNT LOOP
678
679 p_lot_quantity := 0;
680 initialize_details(l_create_asn_details_rec);
681 l_create_asn_details_rec.LPN_ACTUAL := c_lpn_actual.license_plate_number;
682 l_create_asn_details_rec.LOT_NUMBER_ACTUAL := p_rcpt_lot_qty_rec_tb(i).lot_number;
683
684 -- Should Insert Transaction Quantity Here
685 IF (l_debug = 1) THEN
686 print_debug('Lot Number = ' || p_rcpt_lot_qty_rec_tb(i).lot_number, 1);
687 print_debug('Lot Quantity = '|| p_rcpt_lot_qty_rec_tb(i).txn_quantity, 1);
688 END IF;
689
690 -- For Standard Receipt take the transaction quantity from MO lines.
691 Begin
692 if nvl(p_rcpt_lot_qty_rec_tb(i).txn_quantity,0) = 0 then
693 -- Case for Receipt Transaction Because Lot_REC has 0 quantity , SO NEED TO GET IT FROM mo ORDER LINES.
694
695 IF (l_debug = 1) THEN
696 print_debug('inv_cr_asn_details: organization_id '||l_create_asn_details_rec.organization_id , 1);
697 print_debug('inv_cr_asn_details: inventory_item_id '|| l_create_asn_details_rec.ITEM_ID, 1);
698 print_debug('inv_cr_asn_details: lot_number '|| l_create_asn_details_rec.LOT_NUMBER_ACTUAL, 1);
699 print_debug('inv_cr_asn_details: reference_type_code '|| '8', 1);
700 print_debug('inv_cr_asn_details: reference_id '|| 'SHIPMENT_LINE_ID' , 1);
701 print_debug('inv_cr_asn_details: interface_transaction_id ' || p_interface_transaction_id, 1);
702 print_debug('inv_cr_asn_details: lpn_id '|| c_lpn_actual.lpn_id , 1);
703 END IF;
704
705 IF (l_debug = 1) THEN
706 print_debug('Inside MO Qty Fetch ', 1);
707 END IF;
708
709 select quantity
710 into p_lot_quantity
711 from mtl_txn_request_lines
712 where organization_id = l_create_asn_details_rec.organization_id
713 and inventory_item_id = l_create_asn_details_rec.ITEM_ID
714 and lot_number = l_create_asn_details_rec.LOT_NUMBER_ACTUAL
715 and reference = 'SHIPMENT_LINE_ID'
716 and reference_type_code = 8
717 and reference_id = p_rcv_rcpt_rec.rcv_shipment_line_id
718 and txn_source_id = p_interface_transaction_id
719 and lpn_id = c_lpn_actual.lpn_id
720 ;
721 End if;
722 Exception when no_data_found then
723 IF (l_debug = 1) THEN
724 print_debug('Lot Number = ' || p_rcpt_lot_qty_rec_tb(i).lot_number || ' for MO order Qty Fetch ', 1);
725 END IF;
726 p_lot_quantity := 0;
727 End;
728
729 if nvl(p_rcpt_lot_qty_rec_tb(i).txn_quantity,0) = 0 then
730 l_create_asn_details_rec.QUANTITY_ACTUAL := p_lot_quantity;
731 else
732 l_create_asn_details_rec.QUANTITY_ACTUAL := p_rcpt_lot_qty_rec_tb(i).txn_quantity ;
733 end if;
734
735 l_create_asn_details_rec.DISCREPANCY_REPORTING_CONTEXT := 'O' ;
736
737 -- If NO LOT SPECIFIED NOTHING TO INSERT/UPDATE
738
739 if l_create_asn_details_rec.LOT_NUMBER_ACTUAL is not null
740 then
741 update_asn_lot_details(l_create_asn_details_rec);
742 end if;
743
744 l_progress := '40';
745 IF (l_debug = 1) THEN
746 print_debug('Create ASN Details: '|| l_progress ||' '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
747 END IF;
748
749 -- Insert The Related Serial Rows
750
751 For c_Serial in
752 (select msn.serial_number serial_number
753 from mtl_serial_numbers msn
754 where msn.inventory_item_id = p_rcv_rcpt_rec.item_id
755 and ( l_create_asn_details_rec.lot_number_actual is not null
756 and msn.lot_number = l_create_asn_details_rec.lot_number_actual)
757 and msn.lpn_id = c_lpn_actual.lpn_id
758 )
759 loop
760
761 initialize_details(l_create_asn_details_rec);
762 l_create_asn_details_rec.LPN_ACTUAL := c_lpn_actual.license_plate_number ;
763 l_create_asn_details_rec.LOT_NUMBER_ACTUAL := p_rcpt_lot_qty_rec_tb(i).lot_number ;
764 l_create_asn_details_rec.serial_number_actual := c_Serial.serial_number;
765 l_create_asn_details_rec.DISCREPANCY_REPORTING_CONTEXT := 'S' ;
766 insert_asn_ser_details_act(l_create_asn_details_rec);
767
768 l_progress := '50';
769 IF (l_debug = 1) THEN
770 print_debug('Create ASN Details: '|| l_progress ||' '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
771 END IF;
772
773 End loop; -- Serial
774 End Loop; -- Lot
775 -- Just Process Serial
776 else
777 -- Insert The Related Serial Rows
778 For c_Serial in
779 (select msn.serial_number serial_number
780 from mtl_serial_numbers msn
781 where msn.inventory_item_id = p_rcv_rcpt_rec.item_id
782 and msn.lpn_id = c_lpn_actual.lpn_id
783 )
784 loop
785 initialize_details(l_create_asn_details_rec);
786 l_create_asn_details_rec.LPN_ACTUAL := c_lpn_actual.license_plate_number ;
787 l_create_asn_details_rec.LOT_NUMBER_ACTUAL := null ;
788 l_create_asn_details_rec.serial_number_actual := c_Serial.serial_number;
789 l_create_asn_details_rec.DISCREPANCY_REPORTING_CONTEXT := 'S' ;
790 insert_asn_ser_details_act(l_create_asn_details_rec);
791
792 l_progress := '50.1';
793 IF (l_debug = 1) THEN
794 print_debug('Create ASN Details: '|| l_progress ||' '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
795 END IF;
796
797 End loop; -- Serial
798 end if;
799 End loop; -- LPN
800
801 l_progress := '60';
802 IF (l_debug = 1) THEN
803 print_debug('Create ASN Details: '|| l_progress ||' '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
804 END IF;
805
806 Exception
807 When No_data_found then
808 -- Case for NO ACTUAL LPN in ASN
809 IF (l_debug = 1) THEN
810 print_debug('Asn has no actual LPN ', 1);
811 END IF;
812 null;
813 End;
814
815
816 /* 4551595-Commenting out the check for discrepancy from here */
817 -- Update the Status
818 /*
819 if (Check_discrepancy(l_create_asn_details_rec.shipment_num,
820 l_create_asn_details_rec.organization_id))
821 then
822 update WMS_ASN_DETAILS set discrepancy_status = 'S'
823 where shipment_num = l_create_asn_details_rec.SHIPMENT_NUM
824 and organization_id = l_create_asn_details_rec.organization_id
825 and discrepancy_reporting_context = 'I'
826 and item_id = l_create_asn_details_rec.item_id
827 ;
828 else
829 update WMS_ASN_DETAILS set discrepancy_status = 'F'
830 where shipment_num = l_create_asn_details_rec.SHIPMENT_NUM
831 and organization_id = l_create_asn_details_rec.organization_id
832 and discrepancy_reporting_context = 'I'
833 and item_id = l_create_asn_details_rec.item_id
834 ;
835 End if;
836 */
837 /* End of fix for Bug 4551595 */
838
839 l_progress := '70';
840 IF (l_debug = 1) THEN
841 print_debug('Create ASN Details: '|| l_progress ||' '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
842 END IF;
843
844
845
846
847 EXCEPTION
848
849 WHEN fnd_api.g_exc_error THEN
850 ROLLBACK TO crt_asn_details;
851 x_status := fnd_api.g_ret_sts_error;
852 fnd_msg_pub.count_and_get
853 (p_encoded => FND_API.g_false,
854 p_count => l_msg_count,
855 p_data => x_message
856 );
857 WHEN fnd_api.g_exc_unexpected_error THEN
858 ROLLBACK TO crt_asn_details;
859 x_status := fnd_api.g_ret_sts_unexp_error ;
860 fnd_msg_pub.count_and_get
861 (p_encoded => FND_API.g_false,
862 p_count => l_msg_count,
863 p_data => x_message
864 );
865 WHEN OTHERS THEN
866 IF (l_debug = 1) THEN
867 print_debug('Other Problem Occured progress = '||l_progress||'sqlcode ='||SQLCODE, 1);
868 END IF;
869 ROLLBACK TO crt_asn_details;
870 x_status := fnd_api.g_ret_sts_unexp_error ;
871 IF SQLCODE IS NOT NULL THEN
872 inv_mobile_helper_functions.sql_error('INV_CR_ASN_DETAILS.create_asn_details', l_progress, SQLCODE);
873 END IF;
874
875 fnd_msg_pub.count_and_get
876 (p_encoded => FND_API.g_false,
877 p_count => l_msg_count,
878 p_data => x_message
879 );
880 END create_asn_details;
881
882
883 Procedure CREATE_ASN_DETAILS_FROM_INTF
884 ( p_interface_transaction_rec IN OUT nocopy rcv_intf_rec_tp ,
885 x_status OUT nocopy varchar2,
886 x_message OUT nocopy varchar2)
887 IS
888
889 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
890 l_msg_count NUMBER;
891 l_msg_data VARCHAR2(4000);
892 l_progress VARCHAR2(10);
893 l_create_asn_details_rec asn_details_rec_tp ;
894 l_shipped_quantity number;
895 l_received_quantity number;
896 l_lpn_quantity_expected number;
897 l_license_plate_number varchar2(30);
898 l_lpn_quantity_actual number;
899 l_license_plate_actual varchar2(30);
900 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
901 l_lot_number_expected varchar2(80);
902 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
903 l_lot_number_actual varchar2(80);
904 l_shipment_num varchar2(30);
905
906 l_vendor_id number;
907 l_vendor_site_id number;
908
909 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
910 BEGIN
911
912 SAVEPOINT crt_asn_details_interface;
913 x_status := FND_API.G_RET_STS_SUCCESS;
914 l_progress := '10';
915
916 IF (l_debug = 1) THEN
917 print_debug('Create ASN Details from interface : 10 '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
918 END IF;
919
920 Begin
921 select shipment_num,
922 vendor_id,
923 vendor_site_id
924 into l_shipment_num,
925 l_vendor_id,
926 l_vendor_site_id
927 FROM RCV_SHIPMENT_HEADERS RSH
928 WHERE RSH.shipment_header_id = p_interface_transaction_rec.shipment_header_id ;
929 Exception
930 when others then raise fnd_api.g_exc_error ;
931 End;
932
933 l_progress := '20';
934 IF (l_debug = 1) THEN
935 print_debug('Create ASN Details from interface : '|| l_progress ||' '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
936 END IF;
937
938 -- Creating Expected ASN Item Details For Reporting
939 -- Should be called for each item
940
941 l_create_asn_details_rec.organization_id := p_interface_transaction_rec.to_organization_id;
942 l_create_asn_details_rec.shipment_num := l_shipment_num;
943 l_create_asn_details_rec.ITEM_ID := p_interface_transaction_rec.item_id ;
944 l_create_asn_details_rec.DISCREPANCY_REPORTING_CONTEXT := 'I' ;
945 l_create_asn_details_rec.QUANTITY_EXPECTED := p_interface_transaction_rec.quantity ;
946 l_create_asn_details_rec.UNIT_OF_MEASURE_EXPECTED := p_interface_transaction_rec.unit_of_measure;
947 l_create_asn_details_rec.ITEM_REVISION_EXPECTED := p_interface_transaction_rec.item_revision ;
948
949 l_create_asn_details_rec.VENDOR_ID := l_vendor_id;
950 l_create_asn_details_rec.VENDOR_SITE_ID := l_vendor_site_id;
951
952 -- Call the API for creating rows.
953 l_progress := '30';
954 IF (l_debug = 1) THEN
955 print_debug('Create ASN Details from interface : '|| l_progress ||' '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
956 END IF;
957
958 insert_asn_item_details_intf(l_create_asn_details_rec);
959
960
961 l_progress := '40';
962 IF (l_debug = 1) THEN
963 print_debug('Create ASN Details from interface : '|| l_progress ||' '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
964 END IF;
965
966 Begin
967
968 l_license_plate_number :='';
969 l_lpn_quantity_expected := 0;
970
971
972 l_progress := '50';
973 IF (l_debug = 1) THEN
974 print_debug('Create ASN Details from interface : '|| l_progress ||' '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
975 END IF;
976
977 For c_lpn in
978 (select wlpn.license_plate_number,
979 wlpc.quantity,
980 wlpc.lot_number,
981 wlpn.lpn_id
982 from wms_lpn_contents wlpc,
983 wms_license_plate_numbers wlpn
984 where wlpc.parent_lpn_id = wlpn.lpn_id
985 and wlpn.source_header_id = p_interface_transaction_rec.shipment_header_id
986 and wlpc.inventory_item_id = p_interface_transaction_rec.item_id
987 order by wlpn.license_plate_number, wlpc.lot_number
988 )
989 Loop
990
991 -- Find The Expected LPN Quantity and populate the rows.
992 initialize_details(l_create_asn_details_rec);
993 l_create_asn_details_rec.DISCREPANCY_REPORTING_CONTEXT := 'L' ;
994 l_create_asn_details_rec.LPN_EXPECTED := c_lpn.license_plate_number ;
995 l_create_asn_details_rec.QUANTITY_EXPECTED := c_lpn.quantity ;
996
997 insert_asn_lpn_details(l_create_asn_details_rec);
998
999 l_progress := '60';
1000 IF (l_debug = 1) THEN
1001 print_debug('Create ASN Details from interface : '|| l_progress ||' '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
1002 END IF;
1003
1004 -- Insert The Lot Related Rows..
1005 initialize_details(l_create_asn_details_rec);
1006 l_create_asn_details_rec.LPN_EXPECTED := c_lpn.license_plate_number ;
1007 l_create_asn_details_rec.LOT_NUMBER_EXPECTED := c_lpn.lot_number ;
1008 l_create_asn_details_rec.QUANTITY_EXPECTED := c_lpn.quantity ;
1009 l_create_asn_details_rec.DISCREPANCY_REPORTING_CONTEXT := 'O' ;
1010
1011 -- If LOT NUMBER IS NULL THEN NO NEED to insert LOT
1012
1013 if l_create_asn_details_rec.LOT_NUMBER_EXPECTED is not null
1014 then
1015 insert_asn_lot_details(l_create_asn_details_rec);
1016 end if;
1017
1018 l_progress := '70';
1019 IF (l_debug = 1) THEN
1020 print_debug('Create ASN Details from interface : '|| l_progress ||' '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
1021 END IF;
1022
1023
1024 -- Insert The Related Serial Rows
1025
1026 For c_Serial in
1027 (select serial_number,
1028 lot_number
1029 from mtl_serial_numbers
1030 where inventory_item_id = p_interface_transaction_rec.item_id
1031 and lpn_id = c_lpn.lpn_id
1032 )
1033 loop
1034
1035 initialize_details(l_create_asn_details_rec);
1036 l_create_asn_details_rec.LPN_EXPECTED := c_lpn.license_plate_number ;
1037 l_create_asn_details_rec.LOT_NUMBER_EXPECTED := c_Serial.lot_number ;
1038 l_create_asn_details_rec.serial_number_expected := c_Serial.serial_number;
1039 l_create_asn_details_rec.DISCREPANCY_REPORTING_CONTEXT := 'S' ;
1040 insert_asn_ser_details_exp(l_create_asn_details_rec);
1041
1042 l_progress := '80';
1043 IF (l_debug = 1) THEN
1044 print_debug('Create ASN Details from interface : '|| l_progress ||' '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
1045 END IF;
1046
1047 End loop;
1048
1049
1050 End Loop;
1051
1052 l_progress := '80';
1053 IF (l_debug = 1) THEN
1054 print_debug('Create ASN Details from interface : '|| l_progress ||' '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
1055 END IF;
1056
1057 Exception
1058 When No_data_found then
1059 -- Case for NO LPN in ASN
1060 IF (l_debug = 1) THEN
1061 print_debug('Asn has no LPN ', 1);
1062 END IF;
1063 null;
1064 End;
1065
1066 l_progress := '90';
1067 IF (l_debug = 1) THEN
1068 print_debug('Create ASN Details from interface : '|| l_progress ||' '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
1069 END IF;
1070
1071 -- Update the Status
1072 update WMS_ASN_DETAILS set discrepancy_status = 'E'
1073 where shipment_num = l_create_asn_details_rec.SHIPMENT_NUM
1074 and organization_id = l_create_asn_details_rec.organization_id
1075 and discrepancy_reporting_context = 'I'
1076 and item_id = l_create_asn_details_rec.item_id
1077 ;
1078
1079
1080
1081 EXCEPTION
1082 WHEN fnd_api.g_exc_error THEN
1083 ROLLBACK TO crt_asn_details_interface;
1084 x_status := fnd_api.g_ret_sts_error;
1085 fnd_msg_pub.count_and_get
1086 (p_encoded => FND_API.g_false,
1087 p_count => l_msg_count,
1088 p_data => x_message
1089 );
1090 WHEN fnd_api.g_exc_unexpected_error THEN
1091 ROLLBACK TO crt_asn_details_interface;
1092 x_status := fnd_api.g_ret_sts_unexp_error ;
1093 fnd_msg_pub.count_and_get
1094 (p_encoded => FND_API.g_false,
1095 p_count => l_msg_count,
1096 p_data => x_message
1097 );
1098 WHEN OTHERS THEN
1099 IF (l_debug = 1) THEN
1100 print_debug('Other Problem Occured progress = '||l_progress||'sqlcode ='||SQLCODE, 1);
1101 END IF;
1102 ROLLBACK TO crt_asn_details_interface;
1103 x_status := fnd_api.g_ret_sts_unexp_error ;
1104 IF SQLCODE IS NOT NULL THEN
1105 inv_mobile_helper_functions.sql_error('INV_CR_ASN_DETAILS.create_asn_details_from_intf', l_progress, SQLCODE);
1106 END IF;
1107
1108 fnd_msg_pub.count_and_get
1109 (p_encoded => FND_API.g_false,
1110 p_count => l_msg_count,
1111 p_data => x_message
1112 );
1113
1114 END create_asn_details_from_intf;
1115
1116 /* Bug 4551595-Added the procedure to be called from INV_STD_RCPT_APIS
1117 at the header level for all rti records with the same
1118 group id */
1119
1120 PROCEDURE update_asn_item_details
1121 ( p_group_id IN NUMBER )
1122 IS
1123
1124 CURSOR rti_record
1125 IS
1126 SELECT * FROM rcv_transactions_interface
1127 WHERE group_id = p_group_id ;
1128
1129 l_rti_record rcv_transactions_interface%ROWTYPE;
1130 l_create_asn_details_rec asn_details_rec_tp ;
1131 l_shipment_num varchar2(30);
1132 l_shipped_quantity number;
1133 l_item_id number;
1134 l_unit_of_measure varchar2(30);
1135 l_item_revision varchar2(3);
1136
1137
1138 BEGIN
1139
1140 print_debug('In the procedure update_asn_item_details', 1);
1141
1142 OPEN rti_record;
1143
1144 LOOP
1145
1146 FETCH rti_record INTO l_rti_record ;
1147
1148 EXIT WHEN rti_record%NOTFOUND ;
1149
1150 initialize_details(l_create_asn_details_rec);
1151
1152 SELECT shipment_num
1153 INTO l_shipment_num
1154 FROM rcv_shipment_headers
1155 WHERE shipment_header_id= l_rti_record.shipment_header_id ;
1156
1157 SELECT quantity_shipped,
1158 item_id,
1159 unit_of_measure,
1160 item_revision
1161 INTO l_shipped_quantity, l_item_id, l_unit_of_measure, l_item_revision
1162 FROM RCV_SHIPMENT_LINES RSL
1163 WHERE RSL.SHIPMENT_LINE_ID = l_rti_record.shipment_line_id ;
1164
1165 print_debug('Values from the rti record :',1);
1166 print_debug('group_id:' || l_rti_record.group_id,1);
1167 print_debug('organization_id:' || l_rti_record.to_organization_id,1);
1168 print_debug('shipment_num:' || l_shipment_num,1);
1169 print_debug('ITEM_ID:' || l_item_id,1);
1170 print_debug('QUANTITY_EXPECTED:' || l_shipped_quantity,1);
1171 print_debug('UNIT_OF_MEASURE_EXPECTED:' || l_unit_of_measure,1);
1172 print_debug('ITEM_REVISION_EXPECTED:' || l_item_revision,1);
1173 print_debug('QUANTITY_ACTUAL:' || l_rti_record.quantity,1);
1174 print_debug('UNIT_OF_MEASURE_ACTUAL:' || l_rti_record.unit_of_measure,1);
1175 print_debug('ITEM_REVISION_ACTUAL:' || l_rti_record.item_revision,1);
1176
1177 l_create_asn_details_rec.group_id := l_rti_record.group_id;
1178 l_create_asn_details_rec.organization_id := l_rti_record.to_organization_id;
1179 l_create_asn_details_rec.shipment_num := l_shipment_num ;
1180 l_create_asn_details_rec.ITEM_ID := l_item_id ;
1181 l_create_asn_details_rec.DISCREPANCY_REPORTING_CONTEXT := 'I' ;
1182 l_create_asn_details_rec.QUANTITY_EXPECTED := l_shipped_quantity;
1183 l_create_asn_details_rec.UNIT_OF_MEASURE_EXPECTED := l_unit_of_measure;
1184 l_create_asn_details_rec.ITEM_REVISION_EXPECTED := l_item_revision ;
1185
1186 l_create_asn_details_rec.QUANTITY_ACTUAL := l_rti_record.quantity;
1187 l_create_asn_details_rec.UNIT_OF_MEASURE_ACTUAL := l_rti_record.unit_of_measure;
1188 l_create_asn_details_rec.ITEM_REVISION_ACTUAL := l_rti_record.item_revision ;
1189 l_create_asn_details_rec.TRANSACTION_DATE := sysdate;
1190
1191 -- Call the API for creating rows.
1192
1193 insert_asn_item_details(l_create_asn_details_rec);
1194
1195 if (Check_discrepancy(l_create_asn_details_rec.shipment_num,
1196 l_create_asn_details_rec.organization_id))
1197 then
1198
1199 update WMS_ASN_DETAILS set discrepancy_status = 'S'
1200 where shipment_num = l_create_asn_details_rec.SHIPMENT_NUM
1201 and organization_id = l_create_asn_details_rec.organization_id
1202 and discrepancy_reporting_context = 'I'
1203 and item_id = l_create_asn_details_rec.item_id
1204 ;
1205 else
1206
1207 update WMS_ASN_DETAILS set discrepancy_status = 'F'
1208 where shipment_num = l_create_asn_details_rec.SHIPMENT_NUM
1209 and organization_id = l_create_asn_details_rec.organization_id
1210 and discrepancy_reporting_context = 'I'
1211 and item_id = l_create_asn_details_rec.item_id
1212 ;
1213
1214 End if;
1215
1216 END LOOP;
1217
1218 END update_asn_item_details;
1219
1220 /* End of fix for Bug 4551595*/
1221
1222 END INV_CR_ASN_DETAILS;
1223