[Home] [Help]
PACKAGE BODY: APPS.INV_SELECT_INVENTORY_PKG
Source
1 PACKAGE BODY INV_SELECT_INVENTORY_PKG AS
2 /* $Header: INVPCKLB.pls 120.11.12020000.2 2012/07/09 08:11:41 asugandh ship $ */
3
4 /*##############################################################
5 # NAME
6 # get_source_info
7 # SYNOPSIS
8 # proc get_source_info
9 # DESCRIPTION
10 # This procedure is used to get the source info from diff tables
11 # based on the source type id.For now we added only for
12 # sales orders.
13 ###############################################################*/
14
15 PROCEDURE get_source_info (V_source_type_id IN NUMBER,V_source_line_id IN NUMBER,V_source_id IN NUMBER,
16 X_header_no OUT NOCOPY VARCHAR2, X_line_no OUT NOCOPY NUMBER,
17 X_return_status OUT NOCOPY VARCHAR2) IS
18 CURSOR Cur_get_order IS
19 SELECT a.line_number, b.order_number
20 FROM oe_order_lines_all a, oe_order_headers_all b
21 WHERE a.header_id = b.header_id
22 AND a.line_id = V_source_line_id;
23
24 CURSOR Cur_get_wip_entity IS
25 SELECT entity_type
26 FROM wip_entities
27 WHERE wip_entity_id = V_source_id;
28 X_wip_entity_type NUMBER;
29
30 CURSOR Cur_get_batch IS
31 SELECT a.batch_no, b.line_no
32 FROM gme_batch_header a, gme_material_details b
33 WHERE a.batch_id = b.batch_id
34 AND b.material_detail_id = V_source_line_id;
35 BEGIN
36 X_return_status := FND_API.G_RET_STS_SUCCESS;
37 --Getting the source number from order header Getting the source line number from order lines
38 IF (V_source_type_id = INV_GLOBALS.G_SOURCETYPE_SALESORDER) THEN
39 OPEN Cur_get_order;
40 FETCH Cur_get_order INTO X_line_no, X_header_no;
41 CLOSE Cur_get_order;
42 ELSIF (V_source_type_id = 8) THEN
43 OPEN Cur_get_order;
44 FETCH Cur_get_order INTO X_line_no, X_header_no;
45 CLOSE Cur_get_order;
46 END IF;
47
48 --Getting the source number from batch header Getting the source line number from batch lines
49 IF (V_source_type_id = INV_GLOBALS.G_SOURCETYPE_WIP) THEN
50 OPEN Cur_get_wip_entity;
51 FETCH Cur_get_wip_entity INTO X_wip_entity_type;
52 CLOSE Cur_get_wip_entity;
53 IF (X_wip_entity_type = 10) THEN
54 OPEN Cur_get_batch;
55 FETCH Cur_get_batch INTO X_header_no, X_line_no;
56 CLOSE Cur_get_batch;
57 END IF;
58 END IF;
59
60 EXCEPTION
61 WHEN OTHERS THEN
62 fnd_msg_pub.add_exc_msg ('INV_SELECT_INVENTORY_PKG', 'get_source_info');
63 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
64 END get_source_info;
65
66 /*##############################################################
67 # NAME
68 # get_details
69 # SYNOPSIS
70 # proc get_details
71 # DESCRIPTION
72 # This procedure is used to insert the data from mmtt table
73 # when select available inventory button is pressed.
74 ###############################################################*/
75
76 PROCEDURE get_details (V_move_order_line_id IN NUMBER, X_return_status OUT NOCOPY VARCHAR2) IS
77 BEGIN
78 X_return_status := FND_API.G_RET_STS_SUCCESS;
79 -- Bug#12831673. Added lpn_id
80 INSERT INTO mtl_available_inventory_temp
81 (transaction_temp_id,source_line_id,move_order_line_id,organization_id,inventory_item_id,
82 transaction_source_type_id,transaction_action_id,lot_number,subinventory_code,lot_created,
83 lot_expiration_date,grade_code,locator_id,onhand_qty,secondary_onhand_qty,reason_id,
84 transaction_qty,secondary_transaction_qty,transaction_uom,secondary_uom,revision,order_by,lpn_id)
85
86 SELECT trans.transaction_temp_id,trans.source_line_id,trans.move_order_line_id,
87 trans.organization_id,trans.inventory_item_id,
88 trans.transaction_source_type_id,trans.transaction_action_id,
89 lots.lot_number,trans.subinventory_code,mln.creation_date,
90 mln.expiration_date,lots.grade_code,trans.locator_id,
91 0 onhand_quantity,0 secondary_onhand_qty,lots.reason_id,
92 decode(lots.transaction_quantity,null,trans.transaction_quantity,lots.transaction_quantity),
93 decode(lots.secondary_quantity,null,trans.secondary_transaction_quantity,lots.secondary_quantity),
94 trans.transaction_uom,trans.secondary_uom_code,trans.revision,1,trans.allocated_lpn_id
95 FROM mtl_material_transactions_temp trans, mtl_transaction_lots_temp lots, mtl_lot_numbers mln
96 WHERE trans.move_order_line_id = V_move_order_line_id
97 AND trans.organization_id = mln.organization_id (+)
98 AND trans.inventory_item_id = mln.inventory_item_id (+)
99 AND trans.transaction_temp_id = lots.transaction_temp_id (+)
100 AND decode(lots.lot_number,null,'-99999',lots.lot_number) = decode(mln.lot_number,null,'-99999',mln.lot_number);
101 EXCEPTION
102 WHEN OTHERS THEN
103 fnd_msg_pub.add_exc_msg ('INV_SELECT_INVENTORY_PKG', 'get_details');
104 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
105 END get_details;
106
107 /*##############################################################
108 # NAME
109 # get_available_inventory
110 # SYNOPSIS
111 # proc get_available_inventory
112 # DESCRIPTION
113 # This procedure is used to get the data from wms rules engine
114 # and insert the same into temp table
115 ###############################################################*/
116
117 PROCEDURE get_available_inventory (p_mo_line_id IN NUMBER
118 , x_return_status OUT NOCOPY VARCHAR2
119 , x_msg_count OUT NOCOPY NUMBER
120 , x_msg_data OUT NOCOPY VARCHAR2) IS
121
122 cursor mtl_org_csr (p_mo_line_id number) is
123 select *
124 from mtl_txn_request_lines
125 where line_id = p_mo_line_id ;
126
127 l_status_id NUMBER ;
128 mtl_org_rec mtl_org_csr%ROWTYPE ;
129 l_txn_allowed VARCHAR2(1) ;
130 l_insert_allowed BOOLEAN ;
131 l_wms_installed varchar2(10) ;
132
133 l_reservations inv_reservation_global.mtl_reservation_tbl_type;
134 l_return_status VARCHAR2(50);
135 l_msg_count NUMBER;
136 l_msg_data VARCHAR2(250);
137 l_found NUMBER ; -- Fix for Bu#8910862
138 x_dummy BOOLEAN ; -- Fix for 13920680
139
140 BEGIN
141 gmi_reservation_util.println('PROCEDURE get availabe inventory');
142 wms_engine_pvt.create_suggestions
143 ( p_api_version => 1.0
144 ,x_return_status => l_return_status
145 ,x_msg_count => l_msg_count
146 ,x_msg_data => l_msg_data
147 ,p_transaction_temp_id => p_mo_line_id
148 ,p_reservations => l_reservations
149 ,p_simulation_mode => 10
150 );
151
152 /* Fix for Bug#10170014 Fetch Move Order Details */
153
154 gmi_reservation_util.println('Get Avail: Fetching Move Order Details') ;
155
156 open mtl_org_csr(p_mo_line_id) ;
157 fetch mtl_org_csr into mtl_org_rec ;
158 close mtl_org_csr ;
159
160 gmi_reservation_util.println('Get Avail: rows back '
161 ||WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl.COUNT);
162 gmi_reservation_util.println('Get Avail: inserting the data into the temp');
163 --FOR ALL i IN 1..WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl.COUNT
164
165 FOR i IN 1..WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl.COUNT
166 LOOP
167 -- Fix for Bug#8910862.Check if suggestion already exists
168 BEGIN
169
170 select 1
171 into l_found
172 from mtl_available_inventory_temp
173 where (revision = WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).revision or
174 WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).revision is null )
175 and (lot_number = WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).lot_number or
176 WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).lot_number is null)
177 and (subinventory_code = WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).subinventory_code or
178 WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).subinventory_code is null )
179 and (locator_id = WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).locator_id or
180 WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).locator_id is null )
181 and (lpn_id = WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).lpn_id or
182 WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).lpn_id is null )
183 and (serial_number = WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).serial_number or
184 WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).serial_number is null )
185 and (grade_code = WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).grade_code or
186 WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).grade_code is null) ;
187
188 gmi_reservation_util.println(' Suggestion already exists') ;
189
190 EXCEPTION
191
192 WHEN NO_DATA_FOUND THEN
193 gmi_reservation_util.println(' New Suggestion ') ;
194 gmi_reservation_util.println(' Transaction type id ' || mtl_org_rec.transaction_type_id ) ;
195 gmi_reservation_util.println(' Organization_id ' || mtl_org_rec.organization_id ) ;
196 gmi_reservation_util.println(' Inventory Item Id ' || mtl_org_rec.inventory_item_id ) ;
197 gmi_reservation_util.println(' Subinventory code ' || WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).subinventory_code ) ;
198 gmi_reservation_util.println(' Locator id ' || WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).locator_id ) ;
199 gmi_reservation_util.println(' Lot Number ' || WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).lot_number ) ;
200 gmi_reservation_util.println(' LPN Id ' || WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).lpn_id ) ;
201
202 gmi_reservation_util.println('fetch the rows, Secondary qty '
203 ||WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).secondary_onhand_qty);
204 gmi_reservation_util.println('fetch the rows, grade_code '
205 ||WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).grade_code);
206 /* Fix for Bug#13920680. Validate locator_id for security */
207 x_dummy := TRUE ;
208
209 IF (WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).locator_id IS NOT NULL ) THEN
210 x_dummy := FND_FLEX_KEYVAL.validate_ccid(
211 appl_short_name => 'INV',
212 key_flex_code => 'MTLL',
213 structure_number => 101,
214 combination_id => WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).locator_id,
215 data_set => mtl_org_rec.organization_id,
216 security => 'ENFORCE');
217
218
219 END IF;
220
221
222 IF (x_dummy = TRUE ) THEN
223 gmi_reservation_util.println(' Locator id ' || WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).locator_id ||
224 ' okay for security rules ' ) ;
225 l_txn_allowed := 'Y' ; -- 14168651
226
227 /* start Fix for Bug#10170014. */
228 /* Fix for Bug#14168651. Removed is_status_applicable call as post-query trigger will take care of it */
229 /* Start for #14168651
230 IF (WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).lpn_id is not null ) THEN
231 l_wms_installed := 'TRUE' ;
232 ELSE
233 l_wms_installed := 'FALSE' ;
234 END IF ;
235
236 gmi_reservation_util.println(' Calling INV_MATERIAL_STATUS_GRP.Is_status_applicable ') ;
237
238 l_txn_allowed := INV_MATERIAL_STATUS_GRP.is_status_applicable
239 (p_wms_installed =>l_wms_installed,
240 p_trx_status_enabled =>NULL,
241 p_trx_type_id =>mtl_org_rec.transaction_type_id,
242 p_lot_status_enabled =>NULL ,
243 p_serial_status_enabled =>NULL ,
244 p_organization_id =>mtl_org_rec.organization_id ,
245 p_inventory_item_id =>mtl_org_rec.inventory_item_id,
246 p_sub_code =>WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).subinventory_code,
247 p_locator_id =>WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).locator_id,
248 p_lot_number =>WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).lot_number ,
249 p_serial_number =>NULL ,
250 p_object_type =>'A' ,
251 p_lpn_id =>WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).lpn_id) ;
252
253 gmi_reservation_util.println(' INV_MATERIAL_STATUS_GRP.Is_status_applicable returns ' || l_txn_allowed ) ;
254 End for #14168651
255 */
256
257 IF (l_txn_allowed = 'Y') THEN
258 /* SAI MED */
259 /* Filter available inventory by move order subinventory and locator */
260 l_insert_allowed := TRUE ;
261 IF (mtl_org_rec.from_subinventory_code is not null) THEN
262 IF (WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).subinventory_code = mtl_org_rec.from_subinventory_code ) THEN
263 l_insert_allowed := TRUE ;
264 IF (mtl_org_rec.from_locator_id is not null) THEN
265 IF (WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).locator_id = mtl_org_rec.from_locator_id ) THEN
266 l_insert_allowed := TRUE ;
267 gmi_reservation_util.println(' Subinventory and Locator matches') ;
268 ELSE
269 l_insert_allowed := FALSE;
270 gmi_reservation_util.println(' Subinventory and Locator does not matches') ;
271 END IF ;
272 ELSE
273 l_insert_allowed := TRUE ;
274 gmi_reservation_util.println(' Subinventory matches') ;
275 END IF ;
276 ELSE
277 l_insert_allowed := FALSE ;
278 gmi_reservation_util.println(' Subinventory does not matches') ;
279 END IF ;
280 END IF ;
281
282 /* END Fix for Bug#10170014. */
283
284 IF (l_insert_allowed) THEN
285
286
287 INSERT INTO mtl_available_inventory_temp
288 ( revision
289 , lot_number
290 , lot_expiration_date
291 , subinventory_code
292 , locator_id
293 , cost_group_id
294 , transaction_uom
295 , lpn_id
296 , serial_number
297 , onhand_qty
298 , secondary_onhand_qty
299 , grade_code
300 , consist_string
301 , order_by_string
302 )
303 Values
304 (
305 WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).revision
306 ,WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).lot_number
307 ,WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).lot_expiration_date
308 ,WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).subinventory_code
309 ,WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).locator_id
310 ,WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).cost_group_id
311 ,WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).transaction_uom
312 ,WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).lpn_id
313 ,WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).serial_number
314 ,WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).onhand_qty
315 ,WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).secondary_onhand_qty
316 ,WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).grade_code
317 ,WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).consist_string
318 ,WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).order_by_string
319 );
320 END IF ;
321 END IF ;
322 ELSE -- 13920680
323 gmi_reservation_util.println(' Locator id ' || WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).locator_id ||
324 ' failed for security rules ' ) ;
325 END IF ; -- 13920680
326 WHEN OTHERS THEN NULL ;
327
328 END ;
329 END LOOP;
330 --commit;
331
332 END get_available_inventory;
333
334
335
336 END INV_SELECT_INVENTORY_PKG;