[Home] [Help]
PACKAGE BODY: APPS.RCV_SHIPMENT_LINES_PKG
Source
1 PACKAGE BODY RCV_SHIPMENT_LINES_PKG as
2 /* $Header: RCVTISLB.pls 120.4.12010000.7 2010/04/12 06:43:12 yjian ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'RCV_SHIPMENT_LINES_PKG';
5 G_FILE_NAME CONSTANT VARCHAR2(30) := 'RCVTISLB.pls';
6 g_module_prefix CONSTANT VARCHAR2(50) := 'pos.plsql.' || g_pkg_name || '.';
7
8 g_asn_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
9 procedure debug_log(p_level in number,
10 p_api_name in varchar2,
11 p_msg in varchar2);
12
13 procedure debug_log(p_level in number,
14 p_api_name in varchar2,
15 p_msg in varchar2)
16 IS
17 l_module varchar2(2000);
18 BEGIN
19 /* Taken from Package FND_LOG
20 LEVEL_UNEXPECTED CONSTANT NUMBER := 6;
21 LEVEL_ERROR CONSTANT NUMBER := 5;
22 LEVEL_EXCEPTION CONSTANT NUMBER := 4;
23 LEVEL_EVENT CONSTANT NUMBER := 3;
24 LEVEL_PROCEDURE CONSTANT NUMBER := 2;
25 LEVEL_STATEMENT CONSTANT NUMBER := 1;
26 */
27
28 l_module := 'pos.plsql.rcv_shipment_lines_pkg.'||p_api_name;
29 IF(g_asn_debug = 'Y')THEN
30 IF ( p_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
31 FND_LOG.string(LOG_LEVEL => p_level,
32 MODULE => l_module,
33 MESSAGE => p_msg);
34 END IF;
35
36 END IF;
37 END debug_log;
38
39
40 PROCEDURE Lock_Line_s( X_Rowid VARCHAR2,
41 X_Shipment_Line_Id NUMBER,
42 X_item_revision VARCHAR2,
43 X_stock_locator_id NUMBER,
44 X_packing_slip VARCHAR2,
45 X_comments VARCHAR2,
46 X_routing_header_id NUMBER,
47 X_Reason_id NUMBER,
48 X_Attribute_Category VARCHAR2,
49 X_Attribute1 VARCHAR2,
50 X_Attribute2 VARCHAR2,
51 X_Attribute3 VARCHAR2,
52 X_Attribute4 VARCHAR2,
53 X_Attribute5 VARCHAR2,
54 X_Attribute6 VARCHAR2,
55 X_Attribute7 VARCHAR2,
56 X_Attribute8 VARCHAR2,
57 X_Attribute9 VARCHAR2,
58 X_Attribute10 VARCHAR2,
59 X_Attribute11 VARCHAR2,
60 X_Attribute12 VARCHAR2,
61 X_Attribute13 VARCHAR2,
62 X_Attribute14 VARCHAR2,
63 X_Attribute15 VARCHAR2,
64 X_Request_Id NUMBER,
65 X_Program_Application_Id NUMBER,
66 X_Program_Id NUMBER,
67 X_Program_Update_Date DATE
68
69 ) IS
70
71 X_progress VARCHAR2(4) := '000';
72
73 CURSOR C IS
74 SELECT *
75 FROM RCV_SHIPMENT_LINES
76 WHERE rowid = X_Rowid
77 FOR UPDATE of Shipment_Line_Id NOWAIT;
78 Recinfo C%ROWTYPE;
79
80 BEGIN
81
82 --insert into foo values (1, 'in lock_lines_s');
83 --commit;
84
85 OPEN C;
86 FETCH C INTO Recinfo;
87 if (C%NOTFOUND) then
88
89 --insert into foo values (2, 'C not found');
90 --commit;
91
92 CLOSE C;
93 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
94 APP_EXCEPTION.Raise_Exception;
95 end if;
96 CLOSE C;
97 if (
98 (Recinfo.shipment_line_id = X_Shipment_Line_Id)
99
100 AND ( (Recinfo.item_revision = X_item_revision)
101 OR ( (Recinfo.item_revision IS NULL)
102 AND (X_item_revision IS NULL)))
103
104 AND ( (Recinfo.locator_id = X_stock_locator_id)
105 OR ( (Recinfo.locator_id IS NULL)
106 AND (X_stock_locator_id IS NULL)))
107
108 AND ( (Recinfo.packing_slip = X_packing_slip)
109 OR ( (Recinfo.packing_slip IS NULL)
110 AND (X_packing_slip IS NULL)))
111
112 AND ( (Recinfo.comments = X_comments)
113 OR ( (Recinfo.comments IS NULL)
114 AND (X_comments IS NULL)))
115
116 AND ( (Recinfo.routing_header_id = X_routing_header_id)
117 OR ( (Recinfo.routing_header_id IS NULL)
118 AND (X_routing_header_id IS NULL)))
119
120 AND ( (Recinfo.Reason_id = X_Reason_id)
121 OR ( (Recinfo.Reason_id IS NULL)
122 AND (X_Reason_id IS NULL)))
123 ) then
124
125 --insert into foo values (3, 'then clause');
126 --commit;
127
128 return;
129 else
130 --insert into foo values (4, 'else clause');
131 --commit;
132 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
133 APP_EXCEPTION.RAISE_EXCEPTION;
134 end if;
135
136 EXCEPTION
137 WHEN OTHERS THEN
138 po_message_s.sql_error('RCV_SHIPMENT_LINES_PKG.Lock_Line_s', x_progress, sqlcode);
139 RAISE;
140
141 END Lock_Line_s;
142
143
144 PROCEDURE Update_Line_s(
145 X_Shipment_Line_Id NUMBER,
146 X_item_revision VARCHAR2,
147 X_stock_locator_id NUMBER,
148 X_packing_slip VARCHAR2,
149 X_comments VARCHAR2,
150 X_routing_header_id NUMBER,
151 X_Reason_id NUMBER
152 ) IS
153 X_progress VARCHAR2(4) := '000';
154 x_temp VARCHAR2(25); --Bug 8899316
155 BEGIN
156
157 /* Bug 8899316 */
158
159 SELECT 'Check for records in RSL'
160 INTO x_temp
161 FROM rcv_shipment_lines
162 where shipment_line_id = x_shipment_line_id;
163
164 /* Bug 8899316 */
165
166 update rcv_shipment_lines
167 set item_revision = x_item_revision,
168 locator_id = x_stock_locator_id,
169 packing_slip = x_packing_slip,
170 comments = x_comments,
171 routing_header_id = x_routing_header_id,
172 reason_id = x_reason_id
173 where shipment_line_id = x_shipment_line_id;
174
175 /* Bug 8899316: Commenting this code as SQL%NOTFOUND is refering to the
176 last sql of trigger code on RSL
177 */
178
179 /* if (SQL%NOTFOUND) then
180 Raise NO_DATA_FOUND;
181 end if; */
182
183
184
185 EXCEPTION
186 WHEN OTHERS THEN
187 po_message_s.sql_error('RCV_SHIPMENT_LINES_PKG.Update_Line_s', x_progress, sqlcode);
188 RAISE;
189
190 END Update_Line_s;
191
192 PROCEDURE update_wc_line(
193 p_shipment_line_id IN NUMBER,
194 p_requested_amount IN NUMBER DEFAULT NULL,
195 p_material_stored_amount IN NUMBER DEFAULT NULL,
196 p_amount_shipped IN NUMBER DEFAULT NULL,
197 p_quantity_shipped IN NUMBER DEFAULT NULL
198 ) IS
199 X_progress VARCHAR2(4) := '000';
200 l_shipment_header_id number;
201 l_api_name varchar2(50);
202 l_itemkey varchar2(60);
203 x_temp VARCHAR2(25); --Bug 8899316
204 BEGIN
205
206 select wf_item_key
207 into l_itemkey
208 from rcv_shipment_headers
209 where shipment_header_id =( select shipment_header_id
210 from rcv_shipment_lines
211 where shipment_line_id= p_shipment_line_id);
212
213 l_api_name := l_itemkey || ' update_wc_line';
214
215
216 IF (g_asn_debug = 'Y') THEN
217 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
218 'Enter update_wc_line');
219 END IF;
220
221 IF (g_asn_debug = 'Y') THEN
222 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
223 'Before call to update_quantity_amount');
224 END IF;
225
226 update_quantity_amount(p_shipment_line_id,p_quantity_shipped,p_amount_shipped);
227 IF (g_asn_debug = 'Y') THEN
228 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
229 'After call to update_quantity_amount');
230 END IF;
231
232 /* Bug 8899316 */
233
234 SELECT 'Check for records in RSL'
235 INTO x_temp
236 FROM rcv_shipment_lines
237 where shipment_line_id = p_shipment_line_id;
238
239 /* Bug 8899316 */
240
241 update rcv_shipment_lines
242 set requested_amount = p_requested_amount,
243 material_stored_amount = p_material_stored_amount,
244 amount_shipped = p_amount_shipped,
245 quantity_shipped = p_quantity_shipped,
246 last_update_date = sysdate,
247 last_updated_by = fnd_global.user_id,
248 last_update_login = fnd_global.login_id
249 where shipment_line_id = p_shipment_line_id;
250
251 /* Bug 8899316: Commenting this code as SQL%NOTFOUND is refering to the
252 last sql of trigger code on RSL
253 */
254
255 /* if (SQL%NOTFOUND) then
256 Raise NO_DATA_FOUND;
257 end if; */
258
259 IF (g_asn_debug = 'Y') THEN
260 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
261 'Leave update_wc_line');
262 END IF;
263
264 return;
265
266 EXCEPTION
267 WHEN OTHERS THEN
268 po_message_s.sql_error('RCV_SHIPMENT_LINES_PKG.Update_wc_line', x_progress, sqlcode);
269 RAISE;
270
271 END Update_wc_line;
272
273 PROCEDURE update_quantity_amount(
274 p_Shipment_Line_Id IN NUMBER,
275 p_quantity_shipped IN NUMBER,
276 p_amount_shipped IN NUMBER
277 ) IS
278 X_progress VARCHAR2(4) := '000';
279 l_orig_qty_shipped number :=0;
280 l_orig_amt_shipped number :=0;
281 l_line_location_id number;
282 l_qty_shipped number :=0;
283 l_amt_shipped number :=0;
284 l_distribution_id number;
285 l_sum_qty NUMBER :=0; --bug 8899316
286 l_remaining_qty NUMBER :=0; --bug 8899316
287 l_wc_qty NUMBER :=0; --bug 8899316
288
289 cursor supply_info is
290 SELECT QUANTITY,
291 UNIT_OF_MEASURE,
292 nvl(ITEM_ID, -1),
293 FROM_ORGANIZATION_ID,
294 TO_ORGANIZATION_ID,
295 TO_CHAR(RECEIPT_DATE,'DDMMYYYY'),
296 ROWID
297 FROM
298 MTL_SUPPLY
299 WHERE CHANGE_FLAG = 'Y';
300
301 /* Bug 8899316 */
302
303 cursor supply_dist_info(p_po_line_location_id NUMBER, p_shipment_line_id NUMBER) IS
304 SELECT quantity,
305 po_distribution_id
306 FROM MTL_SUPPLY
307 WHERE supply_type_code = 'SHIPMENT'
308 AND po_line_location_id = p_po_line_location_id
309 AND shipment_line_id = p_shipment_line_id;
310
311 /* Bug 8899316 */
312
313 l_supply_qty number;
314 l_supply_uom varchar2(26);
315 l_supply_item_id number;
316 l_supply_from_org number;
317 l_supply_to_org number;
318 l_supply_receipt_date varchar2(26);
319 l_supply_rowid varchar2(26);
320 l_primary_uom varchar2(26);
321 l_primary_qty number;
322 l_lead_time number;
323
324 l_shipment_header_id number;
325 l_api_name varchar2(50);
326 l_itemkey varchar2(60);
327
328 BEGIN
329
330 select wf_item_key
331 into l_itemkey
332 from rcv_shipment_headers
333 where shipment_header_id =( select shipment_header_id
334 from rcv_shipment_lines
335 where shipment_line_id= p_shipment_line_id);
336
337 l_api_name := l_itemkey || ' update_quantity_amount';
338
339
340 IF (g_asn_debug = 'Y') THEN
341 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
342 'Enter update_quantity_amount');
343 END IF;
344
345 /* Get the original quantity or amount. We need this
346 * to update po_line_locations.
347 */
348 select nvl(quantity_shipped,0),
349 nvl(amount_shipped,0),
350 po_line_location_id
351 into l_orig_qty_shipped,
352 l_orig_amt_shipped,
353 l_line_location_id
354 from rcv_shipment_lines
355 where shipment_line_id = p_shipment_line_id;
356
357 IF (g_asn_debug = 'Y') THEN
358 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
359 'l_orig_qty_shipped ' ||l_orig_qty_shipped);
363 'l_line_location_id ' ||l_line_location_id);
360 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
361 'l_orig_amt_shipped ' ||l_orig_amt_shipped);
362 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
364 END IF;
365
366
367 /* Update po_line_location.quantity_shipped */
368 l_qty_shipped := p_quantity_shipped - l_orig_qty_shipped;
369 l_amt_shipped := p_amount_shipped - l_orig_amt_shipped;
370
371 IF (g_asn_debug = 'Y') THEN
372 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
373 'l_qty_shipped ' ||l_qty_shipped);
374 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
375 'l_amt_shipped ' ||l_amt_shipped);
376 end if;
377
378 UPDATE po_line_locations_all poll
379 set poll.quantity_shipped = nvl(poll.quantity_shipped,0) +
380 l_qty_shipped,
381 poll.amount_shipped = nvl(poll.amount_shipped,0) +
382 l_amt_shipped,
383 poll.last_update_date = sysdate,
384 poll.last_updated_by = fnd_global.user_id,
385 poll.last_update_login = fnd_global.login_id
386 where poll.line_location_id = l_line_location_id;
387
388
389 IF (g_asn_debug = 'Y') THEN
390 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
391 'After update to poll ' );
392 end if;
393
394 If (p_amount_shipped is not null) then
395 return;
396 end if;
397
398 /* To update PO supply we need to know the distribution id */
399 IF (g_asn_debug = 'Y') THEN
400 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
401 'p_shipment_line_id '||p_shipment_line_id );
402 end if;
403
404 /* Bug 8899316: Commenting this code as mtl_supply logic is incorrect */
405
406 /*
407 select po_distribution_id
408 into l_distribution_id
409 from mtl_supply
410 where supply_type_code = 'SHIPMENT'
411 and po_line_location_id = l_line_location_id
412 and shipment_line_id = p_shipment_line_id;
413
414 IF (g_asn_debug = 'Y') THEN
415 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
416 'l_distribution_id '||l_distribution_id );
417 end if;
418
419 update mtl_supply
420 set quantity = quantity + l_qty_shipped,
421 change_flag = 'Y'
422 where supply_type_code = 'SHIPMENT'
423 and po_line_location_id = l_line_location_id
424 and shipment_line_id = p_shipment_line_id;
425
426 IF (g_asn_debug = 'Y') THEN
427 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
428 'After update to shipment supply' );
429 end if;
430
431 update mtl_supply
432 set quantity = quantity + l_qty_shipped,
433 change_flag = 'Y'
434 where supply_type_code = 'PO'
435 and po_distribution_id = l_distribution_id;
436
437
438 IF (g_asn_debug = 'Y') THEN
439 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
440 'After update to PO supply' );
441 end if;
442
443 */
444
445 /* Bug 8899316: Logic for updating mtl_supply */
446
447 /* Update last distribution in case of over receipt across the pay item */
448
449 select Sum(quantity), Max(po_distribution_id)
450 into l_sum_qty, l_distribution_id
451 from mtl_supply
452 where supply_type_code = 'SHIPMENT'
453 and po_line_location_id = l_line_location_id
454 and shipment_line_id = p_shipment_line_id;
455
456 IF (p_quantity_shipped > l_sum_qty) THEN
457
458 update mtl_supply
459 set quantity = quantity + (p_quantity_shipped - l_sum_qty),
460 change_flag = 'Y'
461 where supply_type_code = 'SHIPMENT'
462 and po_distribution_id = l_distribution_id
463 and shipment_line_id = p_shipment_line_id;
464
465 IF (g_asn_debug = 'Y') THEN
466 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
467 'l_distribution_id '||l_distribution_id );
468 end if;
469
470 l_wc_qty := p_quantity_shipped;
471
472 FOR dist_info IN supply_dist_info(l_line_location_id, p_shipment_line_id)
473 LOOP
474
475 l_remaining_qty := l_wc_qty - dist_info.quantity ;
476
477 /* If quantity on Work Confirmation is greater than the distribution quantity, do not update
478 mtl_supply for distribution */
479
480 IF ( l_remaining_qty > 0) THEN
481 l_wc_qty := l_remaining_qty;
482
483 ELSIF ( l_remaining_qty < 0 ) THEN
484
485 l_wc_qty := 0;
486
487 /* If quantity on Work Confirmation is less than the distribution quantity, delete 'SHIPMENT' supply
488 for the distribution and re-create 'PO' supply. Re-create PO supply for all subsequent distributions */
489
490 UPDATE mtl_supply
491 SET quantity = (quantity + l_remaining_qty),
492 change_flag = 'Y'
493 where supply_type_code = 'SHIPMENT'
494 and po_distribution_id = l_distribution_id;
495
496 insert into mtl_supply
497 (supply_type_code,
498 supply_source_id,
499 last_updated_by,
500 last_update_date,
501 last_update_login,
502 created_by,
503 creation_date,
504 po_header_id,
505 po_line_id,
506 po_line_location_id,
507 po_distribution_id,
508 po_release_id,
509 item_id,
510 item_revision,
511 quantity,
512 unit_of_measure,
513 receipt_date,
514 need_by_date,
515 destination_type_code,
516 location_id,
517 to_organization_id,
518 to_subinventory,
519 change_flag)
520 select 'PO',
521 pd.po_distribution_id,
522 pd.last_updated_by,
523 pd.last_update_date,
524 pd.last_update_login,
525 pd.created_by,
526 pd.creation_date,
527 pd.po_header_id,
528 pd.po_line_id,
529 pd.line_location_id,
530 pd.po_distribution_id,
531 pd.po_release_id,
532 pl.item_id,
533 pl.item_revision,
534 -(l_remaining_qty),
535 pl.unit_meas_lookup_code,
536 nvl(pll.promised_date,pll.need_by_date),
537 nvl(pll.promised_date,pll.need_by_date),
538 pd.destination_type_code,
539 pd.deliver_to_location_id,
540 pd.destination_organization_id,
541 pd.destination_subinventory,
542 'Y'
543 from po_distributions_all pd,
544 po_line_locations_all pll,
545 po_lines_all pl
546 where pd.po_distribution_id = dist_info.po_distribution_id
547 and pll.line_location_id = pd.line_location_id
548 and pl.po_line_id = pd.po_line_id
549 and nvl(pll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
550 and nvl(pll.closed_code, 'OPEN') <> 'CLOSED'
551 and nvl(pll.closed_code, 'OPEN') <> 'CLOSED FOR RECEIVING'
552 and nvl(pll.cancel_flag, 'N') = 'N'
553 and nvl(pll.approved_flag, 'Y') = 'Y'
554 and pll.quantity is not NULL
555 and not exists
556 (select 'Supply Exists'
557 from mtl_supply ms1
558 where ms1.supply_type_code = 'PO'
559 and ms1.supply_source_id = pd.po_distribution_id);
560
561 END IF; --End IF ( l_remaining_qty > 0)
562
563 END LOOP;
564
565 END IF; --End IF (p_quantity_shipped > l_sum_qty)
566
567 /* End Bug 8899316 */
568
569 open supply_info;
570 loop
571 fetch supply_info into l_supply_qty,
572 l_supply_uom,
573 l_supply_item_id,
574 l_supply_from_org,
575 l_supply_to_org,
576 l_supply_receipt_date,
577 l_supply_rowid;
578 exit when supply_info%notfound;
579
580 IF (g_asn_debug = 'Y') THEN
581 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
582 'l_supply_uom '||l_supply_uom );
583 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
584 'l_supply_item_id '||l_supply_item_id );
585 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
586 'l_supply_from_org '||l_supply_from_org );
587 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
588 'l_supply_to_org '||l_supply_to_org );
589 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
590 'l_supply_receipt_date '||l_supply_receipt_date );
591 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
592 'l_supply_rowid '||l_supply_rowid );
593 end if;
594
595 if (l_supply_qty = 0) then --{
596 IF (g_asn_debug = 'Y') THEN
597 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
598 'Before Delete' );
599 end if;
600
601 delete from mtl_supply
602 where rowid = l_supply_rowid;
606 end if;
603 IF (g_asn_debug = 'Y') THEN
604 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
605 'After Delete' );
607 return;
608 else --}{
609 IF (g_asn_debug = 'Y') THEN
610 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
611 'Supply qty not 0' );
612 end if;
613 if l_supply_item_id = -1 then --{
614 SELECT muom.unit_of_measure, NULL
615 INTO l_primary_uom,
616 l_lead_time
617 FROM mtl_units_of_measure muom,
618 mtl_units_of_measure tuom
619 WHERE tuom.unit_of_measure = l_supply_uom
620 AND tuom.uom_class = muom.uom_class
621 AND muom.base_uom_flag = 'Y';
622
623 else --}{
624 SELECT PRIMARY_UNIT_OF_MEASURE,
625 POSTPROCESSING_LEAD_TIME
626 INTO l_primary_uom,
627 l_lead_time
628 FROM MTL_SYSTEM_ITEMS
629 WHERE INVENTORY_ITEM_ID = l_supply_item_id
630 AND ORGANIZATION_ID = l_supply_to_org;
631
632 end if; --}
633
634 end if; --}
635
636 po_uom_s.uom_convert(l_supply_qty,
637 l_supply_uom,
638 l_supply_item_id,
639 l_primary_uom,
640 l_primary_qty
641 );
642
643 IF (g_asn_debug = 'Y') THEN
644 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
645 'Before update to mtl_supply.quantity' );
646 end if;
647 UPDATE MTL_SUPPLY
648 SET TO_ORG_PRIMARY_QUANTITY = l_primary_qty,
649 TO_ORG_PRIMARY_UOM = l_primary_uom,
650 CHANGE_FLAG = NULL,
651 CHANGE_TYPE = null,
652 EXPECTED_DELIVERY_DATE =
653 decode(l_supply_item_id,
654 -1, TO_DATE(NULL),
655 TO_DATE(l_supply_receipt_date,'DDMMYYYY')
656 + nvl(l_lead_time, 0 ))
657 WHERE ROWID = l_supply_rowid;
658
659 IF (g_asn_debug = 'Y') THEN
660 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
661 'After update to mtl_supply.quantity' );
662 end if;
663
664
665 end loop;
666
667 close supply_info;
668 IF (g_asn_debug = 'Y') THEN
669 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
670 'Leave update_quantity_amount' );
671 end if;
672
673 EXCEPTION
674 WHEN OTHERS THEN
675 po_message_s.sql_error('RCV_SHIPMENT_LINES_PKG.Update_quantity_amount', x_progress, sqlcode);
676 RAISE;
677
678 END Update_quantity_amount;
679
680
681 PROCEDURE delete_line_s(
682 p_Shipment_Line_Id IN NUMBER) IS
683 X_progress VARCHAR2(4) := '000';
684 l_shipment_header_id number;
685 l_api_name varchar2(50);
686 l_itemkey varchar2(60);
687
688 BEGIN
689
690 select wf_item_key
691 into l_itemkey
692 from rcv_shipment_headers
693 where shipment_header_id =( select shipment_header_id
694 from rcv_shipment_lines
695 where shipment_line_id= p_shipment_line_id);
696
697 l_api_name := l_itemkey || ' delete_line_s';
698
699
700 IF (g_asn_debug = 'Y') THEN
701 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
702 'Enter delete_line_s');
703 END IF;
704
705 IF (g_asn_debug = 'Y') THEN
706 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
707 'p_shipment_line_id '||p_shipment_line_id);
708 END IF;
709
710 update_quantity_amount(p_shipment_line_id,
711 0,--quantity_shipped
712 0); --amount_shipped
713
714 IF (g_asn_debug = 'Y') THEN
715 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
716 'Before Delete '||p_shipment_line_id);
717 END IF;
718
719 delete from rcv_shipment_lines
720 where shipment_line_id= p_shipment_line_id;
721
722 EXCEPTION
723 WHEN OTHERS THEN
724 po_message_s.sql_error('RCV_SHIPMENT_LINES_PKG.delete_line_s', x_progress, sqlcode);
725 RAISE;
726
727 END delete_line_s;
728
729 PROCEDURE update_approval_status(p_level IN VARCHAR2,
730 p_approval_status IN VARCHAR2,
731 p_comments IN VARCHAR2,
732 p_document_id IN NUMBER) IS
733 x_progress varchar2(4) := '000';
734 l_shipment_header_id number;
735 l_api_name varchar2(50);
736 l_itemkey varchar2(60);
737
738 BEGIN
739
740 if (p_level = 'HEADER') then
741 select wf_item_key
742 into l_itemkey
743 from rcv_shipment_headers
744 where shipment_header_id = p_document_id;
745
746 l_api_name := l_itemkey || ' update_approval_status';
747
748
749 IF (g_asn_debug = 'Y') THEN
750 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
751 'Enter HEADER update_approval_status');
752 END IF;
753
754 update rcv_shipment_headers
755 set comments = p_comments,
756 last_update_date = sysdate,
757 last_updated_by = fnd_global.user_id,
758 last_update_login = fnd_global.login_id
759 where shipment_header_id = p_document_id;
760
761 end if;
762
763 if (p_level = 'LINE') then
764
765 select wf_item_key
766 into l_itemkey
767 from rcv_shipment_headers
768 where shipment_header_id =( select shipment_header_id
769 from rcv_shipment_lines
770 where shipment_line_id= p_document_id);
771
772 l_api_name := l_itemkey || ' update_approval_status';
773
774
775 IF (g_asn_debug = 'Y') THEN
776 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
777 'Enter LINE update_approval_status');
778 END IF;
779
780 update rcv_shipment_lines
781 set approval_status = p_approval_status,
782 comments = p_comments,
783 last_update_date = sysdate,
784 last_updated_by = fnd_global.user_id,
785 last_update_login = fnd_global.login_id
786 where shipment_line_id = p_document_id;
787
788 end if;
789 IF (g_asn_debug = 'Y') THEN
790 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
791 'Leave update_approval_status');
792 END IF;
793
794 EXCEPTION
795 WHEN OTHERS THEN
796 po_message_s.sql_error('RCV_SHIPMENT_LINES_PKG.update_approval_status', x_progress, sqlcode);
797 RAISE;
798
799 END update_approval_status;
800
801 /* Bug 9534775 WC -ve correction */
802 PROCEDURE correct_wc_line(
803 p_shipment_line_id IN NUMBER,
804 p_interface_transaction_id IN NUMBER
805 ) IS
806 X_progress VARCHAR2(4) := '000';
807 l_api_name varchar2(50);
808 l_trx_quantity NUMBER;
809 l_trx_amount NUMBER;
810 l_requested_amount NUMBER;
811 l_material_stored_amount NUMBER;
812
813 BEGIN
814
815 l_api_name := 'rcv_shipment_lines_pkg.correct_wc_line';
816
817 SELECT Nvl(quantity,0),
818 Nvl(amount,0),
819 Nvl(requested_amount,0),
820 Nvl(material_stored_amount,0)
821 INTO l_trx_quantity,
822 l_trx_amount,
823 l_requested_amount,
824 l_material_stored_amount
825 FROM rcv_transactions_interface
826 WHERE interface_transaction_id = p_interface_transaction_id
827 AND shipment_line_id = p_shipment_line_id;
828
829
830 IF (g_asn_debug = 'Y') THEN
831 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
832 'Enter correct_wc_line');
833 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
834 'interface_transaction_id : ' || p_interface_transaction_id);
835 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
836 'shipment_line_id : ' || p_shipment_line_id);
837 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
838 'quantity : ' || l_trx_quantity);
839 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
840 'amount : ' || l_trx_amount);
841 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
842 'requested_amount : ' || l_requested_amount);
843 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
844 'material_stored_amount : ' || l_material_stored_amount);
845 END IF;
846
847 update rcv_shipment_lines
848 set quantity_shipped = quantity_shipped + l_trx_quantity,
849 amount_shipped = amount_shipped + l_trx_amount,
850 requested_amount = requested_amount + l_requested_amount,
851 material_stored_amount = material_stored_amount + l_material_stored_amount
852 WHERE shipment_line_id = p_shipment_line_id;
853
854 IF (g_asn_debug = 'Y') THEN
855 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
856 'Leave correct_wc_line');
857 END IF;
858
859 return;
860
861 EXCEPTION
862 WHEN OTHERS THEN
863 po_message_s.sql_error('RCV_SHIPMENT_LINES_PKG.correct_wc_line', x_progress, sqlcode);
864 RAISE;
865
866 END correct_wc_line;
867 /* Bug 9534775 */
868
869 /* Bug 9534775 WC -ve correction, get available quantity for correction
870 against WC and at Pay Item level receipt */
871
872 PROCEDURE get_wc_correct_quantity(p_correction_type IN VARCHAR2,
873 p_parent_transaction_type IN VARCHAR2,
874 p_receipt_source_code IN VARCHAR2,
875 p_po_line_location_id IN NUMBER,
876 p_shipment_header_id IN NUMBER,
877 p_available_quantity IN OUT NOCOPY NUMBER) IS
878
879 x_progress VARCHAR2(3) := NULL;
880 l_api_name varchar2(50);
881 x_available_quantity NUMBER;
882 x_tolerable_quantity NUMBER;
883 x_primary_uom VARCHAR2(26);
884
885 CURSOR get_dist_line IS
886 SELECT transaction_id,Nvl(quantity,0)
887 FROM rcv_transactions
888 WHERE shipment_header_id = p_shipment_header_id
889 AND po_line_location_id = p_po_line_location_id
890 AND transaction_type = p_parent_transaction_type;
891
892 BEGIN
893 x_progress := 10;
894 l_api_name := 'rcv_shipment_lines_pkg.get_wc_available_quantity';
895 x_progress := 20;
896 IF (g_asn_debug = 'Y') THEN
897 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
898 'Enter get_wc_available_quantity');
899 END IF;
900 x_progress := 30;
901
902 p_available_quantity := 0;
903 FOR c_lines IN get_dist_line LOOP
904 x_available_quantity := 0;
905
906 rcv_quantities_s.get_available_quantity(p_transaction_type => 'CORRECT',
907 p_parent_id => c_lines.transaction_id,
908 p_receipt_source_code => p_receipt_source_code,
909 p_parent_transaction_type => p_parent_transaction_type,
910 p_grand_parent_id => null,
911 p_correction_type => p_correction_type,
912 p_available_quantity => x_available_quantity,
913 p_tolerable_quantity => x_tolerable_quantity,
914 p_unit_of_measure => x_primary_uom
915 );
916
917 IF (g_asn_debug = 'Y') THEN
918 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
919 'transactions_id : ' || c_lines.transaction_id
920 || ' ,available_quantity : ' || x_available_quantity );
921 END IF;
922
923 p_available_quantity := p_available_quantity + x_available_quantity;
924
925 END LOOP;
926
927 IF (g_asn_debug = 'Y') THEN
928 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
929 'p_available_quantity : ' || p_available_quantity);
930 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
931 'Leave get_wc_available_quantity');
932 END IF;
933 EXCEPTION
934 WHEN OTHERS THEN
935 po_message_s.sql_error('RCV_SHIPMENT_LINES_PKG.get_wc_available_quantity', x_progress, sqlcode);
936 RAISE;
937
938 END get_wc_correct_quantity;
939
940
941 /* Bug 9534775 WC -ve correction, get available amount for correction
942 against WC and at Pay Item receipt level */
943
944 PROCEDURE get_wc_correct_amount(p_correction_type IN VARCHAR2,
945 p_parent_transaction_type IN VARCHAR2,
946 p_receipt_source_code IN VARCHAR2,
947 p_po_line_location_id IN NUMBER,
948 p_shipment_header_id IN NUMBER,
949 p_available_amount IN OUT NOCOPY NUMBER) IS
950
951 x_progress VARCHAR2(3) := NULL;
952 l_api_name varchar2(50);
953 x_available_amount NUMBER;
954 x_tolerable_amount NUMBER;
955 x_primary_uom VARCHAR2(26);
956
957
958 CURSOR get_dist_line IS
959 SELECT transaction_id,Nvl(amount,0)
960 FROM rcv_transactions
961 WHERE shipment_header_id = p_shipment_header_id
962 AND po_line_location_id = p_po_line_location_id
963 AND transaction_type = p_parent_transaction_type;
964
965 BEGIN
966 x_progress := 10;
967 l_api_name := 'rcv_shipment_lines_pkg.get_wc_available_amount';
968 x_progress := 20;
969 IF (g_asn_debug = 'Y') THEN
970 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
971 'Enter get_wc_available_amount');
972 END IF;
973 x_progress := 30;
974 p_available_amount := 0;
975 FOR c_lines IN get_dist_line LOOP
976 x_available_amount := 0;
977 rcv_quantities_s.get_available_amount(p_transaction_type => 'CORRECT',
978 p_parent_id => c_lines.transaction_id,
979 p_receipt_source_code => p_receipt_source_code,
980 p_parent_transaction_type => p_parent_transaction_type,
981 p_grand_parent_id => null,
982 p_correction_type => p_correction_type,
983 p_available_amount => x_available_amount,
984 p_tolerable_amount => x_tolerable_amount);
985
986 IF (g_asn_debug = 'Y') THEN
987 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
988 'transactions_id : ' || c_lines.transaction_id
989 || ' ,available_amount : ' || x_available_amount );
990 END IF;
991
992 p_available_amount := p_available_amount + x_available_amount;
993
994
995 END LOOP;
996
997 IF (g_asn_debug = 'Y') THEN
998 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
999 'p_available_amount : ' || p_available_amount);
1000 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
1001 'Leave get_wc_available_amount');
1002 END IF;
1003 EXCEPTION
1004 WHEN OTHERS THEN
1005 po_message_s.sql_error('RCV_SHIPMENT_LINES_PKG.get_wc_available_amount', x_progress, sqlcode);
1006 RAISE;
1007
1008 END get_wc_correct_amount;
1009
1010 END RCV_SHIPMENT_LINES_PKG;