[Home] [Help]
PACKAGE BODY: APPS.OE_ORDER_MISC_UTIL
Source
1 PACKAGE BODY OE_Order_Misc_Util AS
2 /* $Header: OEXMISCB.pls 120.1 2006/05/25 07:57:27 pkannan noship $ */
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
144 FUNCTION CONVERT_UOM
145 (
146 p_item_id IN NUMBER
147 , p_from_uom_code IN VARCHAR2
148 , p_to_uom_code IN VARCHAR2
149 , p_from_qty IN NUMBER
150 ) RETURN NUMBER
151 AS
152 l_new_qty NUMBER ;
153 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
154 BEGIN
155 IF p_from_qty IS NOT NULL THEN
156 l_new_qty := INV_CONVERT.INV_UM_CONVERT(p_item_id
157 ,9 -- Precision (Default precision is 6 decimals)
158 ,p_from_qty
159 ,p_from_uom_code
160 ,p_to_uom_code
161 ,NULL -- From uom name
162 ,NULL -- To uom name
163 );
164 ELSE
165 IF l_debug_level > 0 THEN
166 oe_debug_pub.add('Convert from quantity is null value, not calling uom conversion api, return null for to_qty');
167 END IF;
168 l_new_qty:=null;
169 END IF;
170
171 RETURN l_new_qty;
172
173 EXCEPTION
174
175 WHEN OTHERS THEN
176 RETURN -99999;
177
178 END CONVERT_UOM;
179
180 END OE_Order_Misc_Util;