1 PACKAGE BODY PO_UNITEFF_PKG as
2 /* $Header: POXPMUEB.pls 120.0.12010000.1 2008/09/18 12:21:07 appldev noship $ */
3
4
5 /* GET_UNIT_NUMBER
6 * ---------------
7 * This function is called by PO_SHIP_RCV_SUPPLY_VIEW and
8 * PO_SHIP_SUPPLY_VIEW. Given a shipment line id and an item id,
9 * this function will return the end item unit number that is
10 * stored in MTL_SERIAL_NUMBERS.
11 */
12 FUNCTION GET_UNIT_NUMBER(p_shipment_line_id NUMBER, p_item_id NUMBER)
13 RETURN VARCHAR2 IS
14 v_end_item_unit_number VARCHAR2(30) := NULL;
15 BEGIN
16
17 -- The design requires that an intransit shipment can only have
18 -- a single unit number. Hence, a distinct is used in the select
19 -- clause here. We obtain the serial number by mapping to the
20 -- RCV_SERIALS_SUPPLY table using the shipment_line_id.
21 -- Example: a shipment line contains an item of quantity 9.
22 -- Each of the 9 items have a different serial number.
23 -- However, all of these 9 items should only have a
24 -- single unit number.
25 -- If the distinct fails, we will return a NULL. The intransit
26 -- shipment form should NOT allow this to occur in the first place.
27
28 SELECT DISTINCT msn.end_item_unit_number
29 INTO v_end_item_unit_number
30 FROM mtl_serial_numbers msn,
31 rcv_serials_supply rss
32 WHERE msn.serial_number = rss.serial_num
33 AND rss.shipment_line_id = p_shipment_line_id
34 AND msn.inventory_item_id = p_item_id;
35
36 RETURN(v_end_item_unit_number);
37
38
39 EXCEPTION
40
41 WHEN NO_DATA_FOUND THEN
42 RETURN(to_char(NULL));
43 WHEN OTHERS THEN
44 RETURN(to_char(NULL));
45
46 END GET_UNIT_NUMBER;
47
48
49 END PO_UNITEFF_PKG;
[Home] [Help]