1 PACKAGE BODY INV_MATERIAL_STATUS_PUB as
2 /* $Header: INVMSPUB.pls 120.3 2008/02/19 19:04:24 musinha ship $ */
3
4 -- Global constant holding package name
5 g_pkg_name constant varchar2(50) := 'INV_MATERIAL_STATUS_PUB';
6
7 PROCEDURE update_status
8 ( p_api_version_number IN NUMBER
9 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
10 , p_commit IN VARCHAR2 DEFAULT fnd_api.g_false
11 , x_return_status OUT NOCOPY VARCHAR2
12 , x_msg_count OUT NOCOPY NUMBER
13 , x_msg_data OUT NOCOPY VARCHAR2
14 , p_object_type IN VARCHAR2
15 , p_status_rec IN INV_MATERIAL_STATUS_PUB.mtl_status_update_rec_type
16 ) IS
17 l_api_version_number CONSTANT NUMBER := 1.0;
18 l_api_name CONSTANT VARCHAR2(30):= 'UPDATE_STATUS';
19 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
20 l_status_rec INV_MATERIAL_STATUS_PUB.mtl_status_update_rec_type;
21 l_msg_count NUMBER;
22 l_msg_data VARCHAR2(240);
23 l_lot_status_enabled VARCHAR2(1);
24 l_default_lot_status_id NUMBER;
25 l_serial_status_enabled VARCHAR2(1);
26 l_default_serial_status_id NUMBER;
27
28 BEGIN
29 -- Standard call to check for call compatibility
30 IF NOT FND_API.Compatible_API_Call
31 ( l_api_version_number
32 , p_api_version_number
33 , l_api_name
34 , G_PKG_NAME
35 )
36 THEN
37 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
38 END IF;
39
40 -- Initialize message list.
41 IF fnd_api.to_boolean(p_init_msg_lst) THEN
42 fnd_msg_pub.initialize;
43 END IF;
44
45 if p_object_type = 'O' or p_object_type = 'S' then
46 -- check if the item is lot_serial status controlled
47 inv_material_status_grp.get_lot_serial_status_control(
48 p_organization_id => p_status_rec.organization_id
49 ,p_inventory_item_id => p_status_rec.inventory_item_id
50 ,x_return_status => l_return_status
51 ,x_msg_count => l_msg_count
52 ,x_msg_data => l_msg_data
53 ,x_lot_status_enabled => l_lot_status_enabled
54 ,x_default_lot_status_id => l_default_lot_status_id
55 ,x_serial_status_enabled => l_serial_status_enabled
56 ,x_default_serial_status_id => l_default_serial_status_id);
57 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
58 RAISE fnd_api.g_exc_unexpected_error;
59 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
60 RAISE fnd_api.g_exc_error;
61 END IF;
62 if (l_lot_status_enabled = 'N' AND p_object_type = 'O') or
63 (l_serial_status_enabled = 'N' AND p_object_type = 'S') then
64 RAISE fnd_api.g_exc_error;
65 end if;
66 end if;
67
68 l_status_rec := p_status_rec;
69 INV_MATERIAL_STATUS_PKG.Initialize_status_rec(l_status_rec);
70
71 if p_object_type = 'Z' then
72 update mtl_secondary_inventories
73 set status_id = l_status_rec.status_id
74 , last_updated_by = l_status_rec.last_updated_by
75 , last_update_date = l_status_rec.last_update_date
76 , last_update_login = l_status_rec.last_update_login
77 where organization_id = l_status_rec.organization_id
78 and secondary_inventory_name = l_status_rec.zone_code;
79 elsif p_object_type = 'L' then
80 update mtl_item_locations
81 set status_id = l_status_rec.status_id
82 , last_updated_by = l_status_rec.last_updated_by
83 , last_update_date = l_status_rec.last_update_date
84 , last_update_login = l_status_rec.last_update_login
85 where organization_id = l_status_rec.organization_id
86 and inventory_location_id = l_status_rec.locator_id;
87 elsif p_object_type = 'O' then
88 update mtl_lot_numbers
89 set status_id = l_status_rec.status_id
90 , last_updated_by = l_status_rec.last_updated_by
91 , last_update_date = l_status_rec.last_update_date
92 , last_update_login = l_status_rec.last_update_login
93 where organization_id = l_status_rec.organization_id
94 and inventory_item_id = l_status_rec.inventory_item_id
95 and lot_number = l_status_rec.lot_number;
96 elsif p_object_type = 'S' then
97 update mtl_serial_numbers
98 set status_id = l_status_rec.status_id
99 , last_updated_by = l_status_rec.last_updated_by
100 , last_update_date = l_status_rec.last_update_date
101 , last_update_login = l_status_rec.last_update_login
102 where current_organization_id = l_status_rec.organization_id
103 and inventory_item_id = l_status_rec.inventory_item_id
104 and serial_number = l_status_rec.serial_number;
105 if l_status_rec.to_serial_number is not null and
106 l_status_rec.serial_number <> l_status_rec.to_serial_number then
107 update mtl_serial_numbers
108 set status_id = l_status_rec.status_id
109 , last_updated_by = l_status_rec.last_updated_by
110 , last_update_date = l_status_rec.last_update_date
111 , last_update_login = l_status_rec.last_update_login
112 where current_organization_id = l_status_rec.organization_id
113 and inventory_item_id = l_status_rec.inventory_item_id
114 and serial_number > l_status_rec.serial_number and
115 serial_number <= l_status_rec.to_serial_number;
116 end if;
117 -- Start of changes for # 6633612---------------
118 elsif p_object_type = 'H' then
119 -- Need to add code to make sure that not serial controlled one.
120 -- I think as this is in else of serial .. no need to check the serial control again
121 if l_status_rec.status_id is not null or l_status_rec.status_id <> 0 or l_status_rec.status_id <> -1 then
122 update mtl_onhand_quantities_detail
123 set status_id = l_status_rec.status_id
124 , last_updated_by = l_status_rec.last_updated_by
125 , last_update_date = l_status_rec.last_update_date
126 , last_update_login = l_status_rec.last_update_login
127 where inventory_item_id = l_status_rec.inventory_item_id
128 and organization_id = l_status_rec.organization_id
129 and subinventory_code = l_status_rec.zone_code
130 and nvl(lot_number, '@@@@') = nvl(l_status_rec.lot_number, '@@@@')
131 and nvl(locator_id, -9999) = nvl(l_status_rec.locator_id, -9999)
132 and nvl(lpn_id, -9999) = nvl(l_status_rec.lpn_id, -9999);
133 end if;
134 -- End of changes for # 6633612---------------
135 else
136 -- Onhand Material Status Support (6633612): Object type is passed as 'Q' from QtyManager.java to
137 -- avoid the update of onhand records. In this case we only want the new record to be inserted into
138 -- the history table.
139 if p_object_type <> 'Q' then
140 l_return_status := fnd_api.g_ret_sts_error;
141 end if;
142 end if;
143
144 -- Bug 6798024: For object_type Q, no update statement is executed, hence
145 -- we should not raise no-data-found error.
146 if ((sql%notfound) AND p_object_type <> 'Q') then
147 raise no_data_found;
148 end if;
149
150 -- Insert the update history to the update status history table
151 INV_MATERIAL_STATUS_PKG.Insert_status_history(p_status_rec);
152
153 if( p_commit = FND_API.G_TRUE ) Then
154 commit;
155 end if;
156
157 x_return_status := l_return_status;
158
159 EXCEPTION
160
161 WHEN FND_API.G_EXC_ERROR THEN
162 x_return_status := FND_API.G_RET_STS_ERROR;
163
164 -- Get message count and data
165 FND_MSG_PUB.Count_And_Get
166 ( p_count => x_msg_count
167 , p_data => x_msg_data
168 );
169
170 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
171 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
172
173 -- Get message count and data
174 FND_MSG_PUB.Count_And_Get
175 ( p_count => x_msg_count
176 , p_data => x_msg_data
177 );
178
179 WHEN OTHERS THEN
180 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
181
182 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
183 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
184 END IF;
185
186 -- Get message count and data
187 FND_MSG_PUB.Count_And_Get
188 ( p_count => x_msg_count
189 , p_data => x_msg_data
190 );
191 END update_status;
192
193 END INV_MATERIAL_STATUS_PUB;
[Home] [Help]