[Home] [Help]
PACKAGE BODY: APPS.OE_ORDER_MISC_UTIL
Source
1 PACKAGE BODY OE_Order_Misc_Util AS
2 /* $Header: OEXMISCB.pls 120.2.12020000.3 2012/11/23 08:14:10 spothula ship $ */
3
4 -- Bug 5244726
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_Order_Misc_Util';
6
7 /* Procedure Get_Item_Info
8 -------------------------------------------------------
9 This procedure will return ordered_item, ordered_item_description and
10 inventory_item based on passing in item_identifier_type */
11
12 PROCEDURE GET_ITEM_INFO
13 ( x_return_status OUT NOCOPY VARCHAR2
14 , x_msg_count OUT NOCOPY NUMBER
15 , x_msg_data OUT NOCOPY VARCHAR2
16 , p_item_identifier_type IN VARCHAR2
17 , p_inventory_item_id IN Number
18 , p_ordered_item_id IN Number
19 , p_sold_to_org_id IN Number
20 , p_ordered_item IN VARCHAR2
21 , x_ordered_item OUT NOCOPY VARCHAR2
22 , x_ordered_item_desc OUT NOCOPY VARCHAR2
23 , x_inventory_item OUT NOCOPY VARCHAR2
24 , p_org_id IN Number DEFAULT NULL
25 ) IS
26
27 l_organization_id Number:= OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID',p_org_id);
28
29 --
30 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
31 --
32 BEGIN
33 IF l_debug_level > 0 THEN
34 oe_debug_pub.add( 'ENTER GET_ITEM_INFO PROCEDURE' ) ;
35 oe_debug_pub.add( 'ITEM_IDENTIFIER_TYPE : '||P_ITEM_IDENTIFIER_TYPE ) ;
36 oe_debug_pub.add( 'INVENTORY_ITEM_ID : '||P_INVENTORY_ITEM_ID ) ;
37 oe_debug_pub.add( 'ORDERED_ITEM_ID : '||P_ORDERED_ITEM_ID ) ;
38 oe_debug_pub.add( 'ORDERED_ITEM : '||P_ORDERED_ITEM ) ;
39 oe_debug_pub.add( 'SOLD_TO_ORG_ID : '||P_SOLD_TO_ORG_ID ) ;
40 END IF;
41
42 x_return_status := FND_API.G_RET_STS_SUCCESS; --bug 5064901
43
44 IF NVL(p_item_identifier_type, 'INT') = 'INT' THEN
45
46 SELECT concatenated_segments
47 ,concatenated_segments
48 ,description
49 INTO x_ordered_item
50 ,x_inventory_item
51 ,x_ordered_item_desc
52 FROM mtl_system_items_vl
53 WHERE inventory_item_id = p_inventory_item_id
54 AND organization_id = l_organization_id;
55
56 ELSIF NVL(p_item_identifier_type, 'INT') = 'CUST' AND
57 p_ordered_item_id is not null and p_sold_to_org_id is not null THEN
58
59 SELECT citems.customer_item_number
60 ,sitems.concatenated_segments
61 ,nvl(citems.customer_item_desc, sitems.description)
62 INTO x_ordered_item
63 ,x_inventory_item
64 ,x_ordered_item_desc
65 FROM mtl_customer_items citems
66 ,mtl_customer_item_xrefs cxref
67 ,mtl_system_items_vl sitems
68 WHERE citems.customer_item_id = cxref.customer_item_id
69 AND cxref.inventory_item_id = sitems.inventory_item_id
70 AND sitems.inventory_item_id = p_inventory_item_id
71 AND sitems.organization_id = l_organization_id
72 AND citems.customer_item_id = p_ordered_item_id
73 AND citems.customer_id = p_sold_to_org_id;
74
75 ELSIF NVL(p_item_identifier_type, 'INT') = 'CAT' THEN
76
77 SELECT category_concat_segs
78 ,category_concat_segs
79 ,description
80 INTO x_ordered_item
81 ,x_inventory_item
82 ,x_ordered_item_desc
83 FROM mtl_categories_v
84 WHERE CATEGORY_ID = p_inventory_item_id;
85
86 ELSIF NVL(p_item_identifier_type, 'INT') = 'ALL' THEN
87
88 x_ordered_item := NULL;
89 x_inventory_item := NULL;
90 x_ordered_item_desc := NULL;
91 ELSE
92 IF p_ordered_item_id IS NULL THEN
93 SELECT items.cross_reference
94 ,sitems.concatenated_segments
95 ,nvl(items.description, sitems.description)
96 INTO x_ordered_item
97 ,x_inventory_item
98 ,x_ordered_item_desc
99 FROM mtl_cross_reference_types types
100 , mtl_cross_references items
101 , mtl_system_items_vl sitems
102 WHERE types.cross_reference_type = items.cross_reference_type
103 AND items.inventory_item_id = sitems.inventory_item_id
104 AND sitems.organization_id = l_organization_id
105 AND sitems.inventory_item_id = p_inventory_item_id
106 AND items.cross_reference_type = p_item_identifier_type
107 AND items.cross_reference = p_ordered_item;
108
109 END IF;
110 END IF;
111 IF l_debug_level > 0 THEN
112 oe_debug_pub.add( 'EXIT GET_ITEM_INFO PROCEDURE' ) ;
113 END IF;
114
115 EXCEPTION
116 WHEN NO_DATA_FOUND THEN
117 x_return_status := FND_API.G_RET_STS_ERROR; --bug 5064901
118 IF l_debug_level > 0 THEN
119 oe_debug_pub.add( 'INT type : no_data_found' ) ;
120 END IF;
121 When too_many_rows then
122 x_return_status := FND_API.G_RET_STS_ERROR; --bug 5064901
123 IF l_debug_level > 0 THEN
124 oe_debug_pub.add( 'INT type : too_many_rows' ) ;
125 END IF;
126
127 WHEN OTHERS THEN
128
129 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR; --bug 5064901
130 IF l_debug_level > 0 THEN
131 oe_debug_pub.add( 'INT type : others' ) ;
132 END IF;
133
134 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
135 THEN
136 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME , 'GET_ITEM_INFO');
137 END IF;
138
139 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
140
141 END GET_ITEM_INFO;
142
143 /* Bug 12794393 --made changes in the convert_uom function signature so as to call the overrided version
144 of api inv_convert.inv_um_convert to handle the lot specific conversion*/
145
146 FUNCTION CONVERT_UOM
147 (
148 p_item_id IN NUMBER
149 , p_from_uom_code IN VARCHAR2
150 , p_to_uom_code IN VARCHAR2
151 , p_from_qty IN NUMBER
152 , p_lot_number IN VARCHAR2 DEFAULT NULL -- 12794393
153 , p_organization_id IN NUMBER DEFAULT NULL -- 12794393
154 ) RETURN NUMBER
155 AS
156 l_new_qty NUMBER ;
157 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
158 BEGIN
159 IF p_from_qty IS NOT NULL THEN
160
161 IF p_lot_number IS NOT NULL THEN
162 l_new_qty := INV_CONVERT.INV_UM_CONVERT(item_id => p_item_id
163 ,lot_number => p_lot_number -- 12794393
164 ,organization_id => p_organization_id -- 12794393
165 ,PRECISION =>5 -- 14323382 9 -- Precision (Default precision is 6 decimals)
166 ,from_quantity => p_from_qty
167 ,from_unit => p_from_uom_code
168 ,to_unit => p_to_uom_code
169 ,from_name => NULL -- From uom name
170 ,to_name => NULL -- To uom name
171 );
172 ELSE
173 l_new_qty := INV_CONVERT.INV_UM_CONVERT(item_id => p_item_id
174 ,PRECISION => 5 -- 14323382 9 -- Precision (Default precision is 6 decimals)
175 ,from_quantity => p_from_qty
176 ,from_unit => p_from_uom_code
177 ,to_unit => p_to_uom_code
178 ,from_name => NULL -- From uom name
179 ,to_name => NULL -- To uom name
180 );
181 END IF;
182
183 ELSE
184 IF l_debug_level > 0 THEN
185 oe_debug_pub.add('Convert from quantity is null value, not calling uom conversion api, return null for to_qty');
186 END IF;
187 l_new_qty:=null;
188 END IF;
189
190 RETURN l_new_qty;
191
192 EXCEPTION
193
194 WHEN OTHERS THEN
195 RETURN -99999;
196
197 END CONVERT_UOM;
198
199 END OE_Order_Misc_Util;