1 PACKAGE BODY INV_SELECT_INVENTORY_PKG AS
2 /* $Header: INVPCKLB.pls 120.2 2006/08/03 16:52:51 rajreddy noship $ */
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 INSERT INTO mtl_available_inventory_temp
80 (transaction_temp_id,source_line_id,move_order_line_id,organization_id,inventory_item_id,
81 transaction_source_type_id,transaction_action_id,lot_number,subinventory_code,lot_created,
82 lot_expiration_date,grade_code,locator_id,onhand_qty,secondary_onhand_qty,reason_id,
83 transaction_qty,secondary_transaction_qty,transaction_uom,secondary_uom,revision,order_by)
84
85 SELECT trans.transaction_temp_id,trans.source_line_id,trans.move_order_line_id,
86 trans.organization_id,trans.inventory_item_id,
87 trans.transaction_source_type_id,trans.transaction_action_id,
88 lots.lot_number,trans.subinventory_code,mln.creation_date,
89 mln.expiration_date,lots.grade_code,trans.locator_id,
90 0 onhand_quantity,0 secondary_onhand_qty,lots.reason_id,
91 decode(lots.transaction_quantity,null,trans.transaction_quantity,lots.transaction_quantity),
92 decode(lots.secondary_quantity,null,trans.secondary_transaction_quantity,lots.secondary_quantity),
93 trans.transaction_uom,trans.secondary_uom_code,trans.revision,1
94 FROM mtl_material_transactions_temp trans, mtl_transaction_lots_temp lots, mtl_lot_numbers mln
95 WHERE trans.move_order_line_id = V_move_order_line_id
96 AND trans.organization_id = mln.organization_id (+)
97 AND trans.inventory_item_id = mln.inventory_item_id (+)
98 AND trans.transaction_temp_id = lots.transaction_temp_id (+)
99 AND decode(lots.lot_number,null,'-99999',lots.lot_number) = decode(mln.lot_number,null,'-99999',mln.lot_number);
100 EXCEPTION
101 WHEN OTHERS THEN
102 fnd_msg_pub.add_exc_msg ('INV_SELECT_INVENTORY_PKG', 'get_details');
103 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
104 END get_details;
105
106 /*##############################################################
107 # NAME
108 # get_available_inventory
109 # SYNOPSIS
110 # proc get_available_inventory
111 # DESCRIPTION
112 # This procedure is used to get the data from wms rules engine
113 # and insert the same into temp table
114 ###############################################################*/
115
116 PROCEDURE get_available_inventory (p_mo_line_id IN NUMBER
117 , x_return_status OUT NOCOPY VARCHAR2
118 , x_msg_count OUT NOCOPY NUMBER
119 , x_msg_data OUT NOCOPY VARCHAR2) IS
120
121 l_reservations inv_reservation_global.mtl_reservation_tbl_type;
122 l_return_status VARCHAR2(50);
123 l_msg_count NUMBER;
124 l_msg_data VARCHAR2(250);
125
126 BEGIN
127 gmi_reservation_util.println('PROCEDURE get availabe inventory');
128 wms_engine_pvt.create_suggestions
129 ( p_api_version => 1.0
130 ,x_return_status => l_return_status
131 ,x_msg_count => l_msg_count
132 ,x_msg_data => l_msg_data
133 ,p_transaction_temp_id => p_mo_line_id
134 ,p_reservations => l_reservations
135 ,p_simulation_mode => 10
136 );
137
138 gmi_reservation_util.println('Get Avail: rows back '
139 ||WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl.COUNT);
140 gmi_reservation_util.println('Get Avail: inserting the data into the temp');
141 --FOR ALL i IN 1..WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl.COUNT
142 FOR i IN 1..WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl.COUNT
143 LOOP
144 gmi_reservation_util.println('fetch the rows, 2nd qty '
145 ||WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).secondary_onhand_qty);
146 gmi_reservation_util.println('fetch the rows, grade_code '
147 ||WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).grade_code);
148 INSERT INTO mtl_available_inventory_temp
149 ( revision
150 , lot_number
151 , lot_expiration_date
152 , subinventory_code
153 , locator_id
154 , cost_group_id
155 , transaction_uom
156 , lpn_id
157 , serial_number
158 , onhand_qty
159 , secondary_onhand_qty
160 , grade_code
161 , consist_string
162 , order_by_string
163 )
164 Values
165 (
166 WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).revision
167 ,WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).lot_number
168 ,WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).lot_expiration_date
169 ,WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).subinventory_code
170 ,WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).locator_id
171 ,WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).cost_group_id
172 ,WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).transaction_uom
173 ,WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).lpn_id
174 ,WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).serial_number
175 ,WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).onhand_qty
176 ,WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).secondary_onhand_qty
177 ,WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).grade_code
178 ,WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).consist_string
179 ,WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).order_by_string
180 );
181 END LOOP;
182 --commit;
183
184 END get_available_inventory;
185
186
187
188 END INV_SELECT_INVENTORY_PKG;
[Home] [Help]