[Home] [Help]
PACKAGE BODY: APPS.RCV_SHIPMENT_LINES_PKG
Source
1 PACKAGE BODY RCV_SHIPMENT_LINES_PKG as
2 /* $Header: RCVTISLB.pls 120.4 2005/11/04 15:23:59 pparthas noship $ */
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 BEGIN
155
156 update rcv_shipment_lines
157 set item_revision = x_item_revision,
158 locator_id = x_stock_locator_id,
159 packing_slip = x_packing_slip,
160 comments = x_comments,
161 routing_header_id = x_routing_header_id,
162 reason_id = x_reason_id
163 where shipment_line_id = x_shipment_line_id;
164
165 if (SQL%NOTFOUND) then
166 Raise NO_DATA_FOUND;
167 end if;
168
169 EXCEPTION
170 WHEN OTHERS THEN
171 po_message_s.sql_error('RCV_SHIPMENT_LINES_PKG.Update_Line_s', x_progress, sqlcode);
172 RAISE;
173
174 END Update_Line_s;
175
176 PROCEDURE update_wc_line(
177 p_shipment_line_id IN NUMBER,
178 p_requested_amount IN NUMBER DEFAULT NULL,
179 p_material_stored_amount IN NUMBER DEFAULT NULL,
180 p_amount_shipped IN NUMBER DEFAULT NULL,
181 p_quantity_shipped IN NUMBER DEFAULT NULL
182 ) IS
183 X_progress VARCHAR2(4) := '000';
184 l_shipment_header_id number;
185 l_api_name varchar2(50);
186 l_itemkey varchar2(60);
187
188 BEGIN
189
190 select wf_item_key
191 into l_itemkey
192 from rcv_shipment_headers
193 where shipment_header_id =( select shipment_header_id
194 from rcv_shipment_lines
195 where shipment_line_id= p_shipment_line_id);
196
197 l_api_name := l_itemkey || ' update_wc_line';
198
199
200 IF (g_asn_debug = 'Y') THEN
201 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
202 'Enter update_wc_line');
203 END IF;
204
205 IF (g_asn_debug = 'Y') THEN
206 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
207 'Before call to update_quantity_amount');
208 END IF;
209
210 update_quantity_amount(p_shipment_line_id,p_quantity_shipped,p_amount_shipped);
211 IF (g_asn_debug = 'Y') THEN
212 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
213 'After call to update_quantity_amount');
214 END IF;
215
216 update rcv_shipment_lines
217 set requested_amount = p_requested_amount,
218 material_stored_amount = p_material_stored_amount,
219 amount_shipped = p_amount_shipped,
220 quantity_shipped = p_quantity_shipped,
221 last_update_date = sysdate,
222 last_updated_by = fnd_global.user_id,
223 last_update_login = fnd_global.login_id
224 where shipment_line_id = p_shipment_line_id;
225
226
227 if (SQL%NOTFOUND) then
228 Raise NO_DATA_FOUND;
229 end if;
230
231 IF (g_asn_debug = 'Y') THEN
232 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
233 'Leave update_wc_line');
234 END IF;
235
236 return;
237
238 EXCEPTION
239 WHEN OTHERS THEN
240 po_message_s.sql_error('RCV_SHIPMENT_LINES_PKG.Update_wc_line', x_progress, sqlcode);
241 RAISE;
242
243 END Update_wc_line;
244
245 PROCEDURE update_quantity_amount(
246 p_Shipment_Line_Id IN NUMBER,
247 p_quantity_shipped IN NUMBER,
248 p_amount_shipped IN NUMBER
249 ) IS
250 X_progress VARCHAR2(4) := '000';
251 l_orig_qty_shipped number :=0;
252 l_orig_amt_shipped number :=0;
253 l_line_location_id number;
254 l_qty_shipped number :=0;
255 l_amt_shipped number :=0;
256 l_distribution_id number;
257
258 cursor supply_info is
259 SELECT QUANTITY,
260 UNIT_OF_MEASURE,
261 nvl(ITEM_ID, -1),
262 FROM_ORGANIZATION_ID,
263 TO_ORGANIZATION_ID,
264 TO_CHAR(RECEIPT_DATE,'DDMMYYYY'),
265 ROWID
266 FROM
267 MTL_SUPPLY
268 WHERE CHANGE_FLAG = 'Y';
269
270 l_supply_qty number;
271 l_supply_uom varchar2(26);
272 l_supply_item_id number;
273 l_supply_from_org number;
274 l_supply_to_org number;
275 l_supply_receipt_date varchar2(26);
276 l_supply_rowid varchar2(26);
277 l_primary_uom varchar2(26);
278 l_primary_qty number;
279 l_lead_time number;
280
281 l_shipment_header_id number;
282 l_api_name varchar2(50);
283 l_itemkey varchar2(60);
284
285 BEGIN
286
287 select wf_item_key
288 into l_itemkey
289 from rcv_shipment_headers
290 where shipment_header_id =( select shipment_header_id
291 from rcv_shipment_lines
292 where shipment_line_id= p_shipment_line_id);
293
294 l_api_name := l_itemkey || ' update_quantity_amount';
295
296
297 IF (g_asn_debug = 'Y') THEN
298 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
299 'Enter update_quantity_amount');
300 END IF;
301
302 /* Get the original quantity or amount. We need this
303 * to update po_line_locations.
304 */
305 select nvl(quantity_shipped,0),
306 nvl(amount_shipped,0),
307 po_line_location_id
308 into l_orig_qty_shipped,
309 l_orig_amt_shipped,
310 l_line_location_id
311 from rcv_shipment_lines
312 where shipment_line_id = p_shipment_line_id;
313
314 IF (g_asn_debug = 'Y') THEN
315 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
316 'l_orig_qty_shipped ' ||l_orig_qty_shipped);
317 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
318 'l_orig_amt_shipped ' ||l_orig_amt_shipped);
319 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
320 'l_line_location_id ' ||l_line_location_id);
321 END IF;
322
323
324 /* Update po_line_location.quantity_shipped */
325 l_qty_shipped := p_quantity_shipped - l_orig_qty_shipped;
326 l_amt_shipped := p_amount_shipped - l_orig_amt_shipped;
327
328 IF (g_asn_debug = 'Y') THEN
329 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
330 'l_qty_shipped ' ||l_qty_shipped);
331 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
332 'l_amt_shipped ' ||l_amt_shipped);
333 end if;
334
335 UPDATE po_line_locations_all poll
336 set poll.quantity_shipped = nvl(poll.quantity_shipped,0) +
337 l_qty_shipped,
338 poll.amount_shipped = nvl(poll.amount_shipped,0) +
339 l_amt_shipped,
340 poll.last_update_date = sysdate,
341 poll.last_updated_by = fnd_global.user_id,
342 poll.last_update_login = fnd_global.login_id
343 where poll.line_location_id = l_line_location_id;
344
345
346 IF (g_asn_debug = 'Y') THEN
347 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
348 'After update to poll ' );
349 end if;
350
351 If (p_amount_shipped is not null) then
352 return;
353 end if;
354
355 /* To update PO supply we need to know the distribution id */
356 IF (g_asn_debug = 'Y') THEN
357 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
358 'p_shipment_line_id '||p_shipment_line_id );
359 end if;
360
361 select po_distribution_id
362 into l_distribution_id
363 from mtl_supply
364 where supply_type_code = 'SHIPMENT'
365 and po_line_location_id = l_line_location_id
366 and shipment_line_id = p_shipment_line_id;
367
368 IF (g_asn_debug = 'Y') THEN
369 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
370 'l_distribution_id '||l_distribution_id );
371 end if;
372
373 /* Update the shipment qty with the new quantity. */
374 update mtl_supply
375 set quantity = quantity + l_qty_shipped,
376 change_flag = 'Y'
377 where supply_type_code = 'SHIPMENT'
378 and po_line_location_id = l_line_location_id
379 and shipment_line_id = p_shipment_line_id;
380
381 IF (g_asn_debug = 'Y') THEN
382 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
383 'After update to shipment supply' );
384 end if;
385
386 /* Update PO supply in mtl_supply */
387 update mtl_supply
388 set quantity = quantity + l_qty_shipped,
389 change_flag = 'Y'
390 where supply_type_code = 'PO'
391 and po_distribution_id = l_distribution_id;
392
393
394 IF (g_asn_debug = 'Y') THEN
395 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
396 'After update to PO supply' );
397 end if;
398
399 open supply_info;
400 loop
401 fetch supply_info into l_supply_qty,
402 l_supply_uom,
403 l_supply_item_id,
404 l_supply_from_org,
405 l_supply_to_org,
406 l_supply_receipt_date,
407 l_supply_rowid;
408 exit when supply_info%notfound;
409
410 IF (g_asn_debug = 'Y') THEN
411 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
412 'l_supply_uom '||l_supply_uom );
413 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
414 'l_supply_item_id '||l_supply_item_id );
415 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
416 'l_supply_from_org '||l_supply_from_org );
417 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
418 'l_supply_to_org '||l_supply_to_org );
419 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
420 'l_supply_receipt_date '||l_supply_receipt_date );
421 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
422 'l_supply_rowid '||l_supply_rowid );
423 end if;
424
425 if (l_supply_qty = 0) then --{
426 IF (g_asn_debug = 'Y') THEN
427 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
428 'Before Delete' );
429 end if;
430
431 delete from mtl_supply
432 where rowid = l_supply_rowid;
433 IF (g_asn_debug = 'Y') THEN
434 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
435 'After Delete' );
436 end if;
437 return;
438 else --}{
439 IF (g_asn_debug = 'Y') THEN
440 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
441 'Supply qty not 0' );
442 end if;
443 if l_supply_item_id = -1 then --{
444 SELECT muom.unit_of_measure, NULL
445 INTO l_primary_uom,
446 l_lead_time
447 FROM mtl_units_of_measure muom,
448 mtl_units_of_measure tuom
449 WHERE tuom.unit_of_measure = l_supply_uom
450 AND tuom.uom_class = muom.uom_class
451 AND muom.base_uom_flag = 'Y';
452
453 else --}{
454 SELECT PRIMARY_UNIT_OF_MEASURE,
455 POSTPROCESSING_LEAD_TIME
456 INTO l_primary_uom,
457 l_lead_time
458 FROM MTL_SYSTEM_ITEMS
459 WHERE INVENTORY_ITEM_ID = l_supply_item_id
460 AND ORGANIZATION_ID = l_supply_to_org;
461
462 end if; --}
463
464 end if; --}
465
466 po_uom_s.uom_convert(l_supply_qty,
467 l_supply_uom,
468 l_supply_item_id,
469 l_primary_uom,
470 l_primary_qty
471 );
472
473 IF (g_asn_debug = 'Y') THEN
474 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
475 'Before update to mtl_supply.quantity' );
476 end if;
477 UPDATE MTL_SUPPLY
478 SET TO_ORG_PRIMARY_QUANTITY = l_primary_qty,
479 TO_ORG_PRIMARY_UOM = l_primary_uom,
480 CHANGE_FLAG = NULL,
481 CHANGE_TYPE = null,
482 EXPECTED_DELIVERY_DATE =
483 decode(l_supply_item_id,
484 -1, TO_DATE(NULL),
485 TO_DATE(l_supply_receipt_date,'DDMMYYYY')
486 + nvl(l_lead_time, 0 ))
487 WHERE ROWID = l_supply_rowid;
488
489 IF (g_asn_debug = 'Y') THEN
490 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
491 'After update to mtl_supply.quantity' );
492 end if;
493
494
495 end loop;
496
497 close supply_info;
498 IF (g_asn_debug = 'Y') THEN
499 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
500 'Leave update_quantity_amount' );
501 end if;
502
503 EXCEPTION
504 WHEN OTHERS THEN
505 po_message_s.sql_error('RCV_SHIPMENT_LINES_PKG.Update_quantity_amount', x_progress, sqlcode);
506 RAISE;
507
508 END Update_quantity_amount;
509
510
511 PROCEDURE delete_line_s(
512 p_Shipment_Line_Id IN NUMBER) IS
513 X_progress VARCHAR2(4) := '000';
514 l_shipment_header_id number;
515 l_api_name varchar2(50);
516 l_itemkey varchar2(60);
517
518 BEGIN
519
520 select wf_item_key
521 into l_itemkey
522 from rcv_shipment_headers
523 where shipment_header_id =( select shipment_header_id
524 from rcv_shipment_lines
525 where shipment_line_id= p_shipment_line_id);
526
527 l_api_name := l_itemkey || ' delete_line_s';
528
529
530 IF (g_asn_debug = 'Y') THEN
531 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
532 'Enter delete_line_s');
533 END IF;
534
535 IF (g_asn_debug = 'Y') THEN
536 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
537 'p_shipment_line_id '||p_shipment_line_id);
538 END IF;
539
540 update_quantity_amount(p_shipment_line_id,
541 0,--quantity_shipped
542 0); --amount_shipped
543
544 IF (g_asn_debug = 'Y') THEN
545 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
546 'Before Delete '||p_shipment_line_id);
547 END IF;
548
549 delete from rcv_shipment_lines
550 where shipment_line_id= p_shipment_line_id;
551
552 EXCEPTION
553 WHEN OTHERS THEN
554 po_message_s.sql_error('RCV_SHIPMENT_LINES_PKG.delete_line_s', x_progress, sqlcode);
555 RAISE;
556
557 END delete_line_s;
558
559 PROCEDURE update_approval_status(p_level IN VARCHAR2,
560 p_approval_status IN VARCHAR2,
561 p_comments IN VARCHAR2,
562 p_document_id IN NUMBER) IS
563 x_progress varchar2(4) := '000';
564 l_shipment_header_id number;
565 l_api_name varchar2(50);
566 l_itemkey varchar2(60);
567
568 BEGIN
569
570 if (p_level = 'HEADER') then
571 select wf_item_key
572 into l_itemkey
573 from rcv_shipment_headers
574 where shipment_header_id = p_document_id;
575
576 l_api_name := l_itemkey || ' update_approval_status';
577
578
579 IF (g_asn_debug = 'Y') THEN
580 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
581 'Enter HEADER update_approval_status');
582 END IF;
583
584 update rcv_shipment_headers
585 set comments = p_comments,
586 last_update_date = sysdate,
587 last_updated_by = fnd_global.user_id,
588 last_update_login = fnd_global.login_id
589 where shipment_header_id = p_document_id;
590
591 end if;
592
593 if (p_level = 'LINE') then
594
595 select wf_item_key
596 into l_itemkey
597 from rcv_shipment_headers
598 where shipment_header_id =( select shipment_header_id
599 from rcv_shipment_lines
600 where shipment_line_id= p_document_id);
601
602 l_api_name := l_itemkey || ' update_approval_status';
603
604
605 IF (g_asn_debug = 'Y') THEN
606 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
607 'Enter LINE update_approval_status');
608 END IF;
609
610 update rcv_shipment_lines
611 set approval_status = p_approval_status,
612 comments = p_comments,
613 last_update_date = sysdate,
614 last_updated_by = fnd_global.user_id,
615 last_update_login = fnd_global.login_id
616 where shipment_line_id = p_document_id;
617
618 end if;
619 IF (g_asn_debug = 'Y') THEN
620 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
621 'Leave update_approval_status');
622 END IF;
623
624 EXCEPTION
625 WHEN OTHERS THEN
626 po_message_s.sql_error('RCV_SHIPMENT_LINES_PKG.update_approval_status', x_progress, sqlcode);
627 RAISE;
628
629 END update_approval_status;
630
631
632 END RCV_SHIPMENT_LINES_PKG;