1 PACKAGE WMS_ITEM_LOAD AS
2 /* $Header: WMSTKILS.pls 120.1 2006/01/31 08:40:08 gayu noship $ */
3
4 TYPE t_genref IS REF CURSOR;
5
6 -- Name: get_available_qty
7 --
8 -- Input parameters:
9 -- p_organization_id Organization ID
10 -- p_lpn_id LPN ID
11 -- p_inventory_item_id Inventory Item ID
12 -- p_revision Revision
13 -- p_prim_uom_code Primary UOM code for the item
14 -- p_uom_code UOM Code to return qty in
15 --
16 -- Output parameters:
17 -- x_return_status
18 -- if the pre_process_load API succeeds, the value is
19 -- fnd_api.g_ret_sts_success;
20 -- if there is an expected error, the value is
21 -- fnd_api.g_ret_sts_error;
22 -- if there is an unexpected error, the value is
23 -- fnd_api.g_ret_sts_unexp_error;
24 -- x_msg_count
25 -- if there are one or more errors, the number of error messages
26 -- in the buffer
27 -- x_msg_data
28 -- if there is one and only one error, the error message
29 -- (See fnd_api package for more details about the above output parameters)
30 -- x_available_qty
31 -- Returns the available quantity for the given item and revision
32 -- combination within the LPN. The quantity returned will be in
33 -- the UOM of the inputted value.
34 --
35 PROCEDURE get_available_qty
36 (p_organization_id IN NUMBER ,
37 p_lpn_id IN NUMBER ,
38 p_inventory_item_id IN NUMBER ,
39 p_revision IN VARCHAR2 := NULL ,
40 p_prim_uom_code IN VARCHAR2 ,
41 p_uom_code IN VARCHAR2 ,
42 x_return_status OUT NOCOPY VARCHAR2 ,
43 x_msg_count OUT NOCOPY NUMBER ,
44 x_msg_data OUT NOCOPY VARCHAR2 ,
45 x_available_qty OUT NOCOPY NUMBER ,
46 x_total_qty OUT NOCOPY NUMBER);--Added for bug 5000292
47
48 -- Name: get_available_lot_qty
49 --
50 -- Input parameters:
51 -- p_organization_id Organization ID
52 -- p_lpn_id LPN ID
53 -- p_inventory_item_id Inventory Item ID
54 -- p_revision Revision
55 -- p_lot_number Lot Number
56 -- p_prim_uom_code Primary UOM code for the item
57 -- p_uom_code UOM Code to return qty in
58 --
59 -- Output parameters:
60 -- x_return_status
61 -- if the pre_process_load API succeeds, the value is
62 -- fnd_api.g_ret_sts_success;
63 -- if there is an expected error, the value is
64 -- fnd_api.g_ret_sts_error;
65 -- if there is an unexpected error, the value is
66 -- fnd_api.g_ret_sts_unexp_error;
67 -- x_msg_count
68 -- if there are one or more errors, the number of error messages
69 -- in the buffer
70 -- x_msg_data
71 -- if there is one and only one error, the error message
72 -- (See fnd_api package for more details about the above output parameters)
73 -- x_available_lot_qty
74 -- Returns the available quantity for the given item, revision
75 -- and lot combination within the LPN. The quantity returned
76 -- will be in the UOM of the inputted value.
77 PROCEDURE get_available_lot_qty
78 (p_organization_id IN NUMBER ,
79 p_lpn_id IN NUMBER ,
80 p_inventory_item_id IN NUMBER ,
81 p_revision IN VARCHAR2 := NULL ,
82 p_lot_number IN VARCHAR2 ,
83 p_prim_uom_code IN VARCHAR2 ,
84 p_uom_code IN VARCHAR2 ,
85 x_return_status OUT NOCOPY VARCHAR2 ,
86 x_msg_count OUT NOCOPY NUMBER ,
87 x_msg_data OUT NOCOPY VARCHAR2 ,
88 x_available_lot_qty OUT NOCOPY NUMBER);
89
90
91 -- Name: pre_process_load
92 --
93 -- Input parameters:
94 -- p_organization_id Organization ID
95 -- p_lpn_id Source LPN ID
96 -- p_inventory_item_id Inventory Item ID
97 -- p_revision Revision (optional)
98 -- p_lot_number Lot Number (optional)
99 -- p_quantity Quantity
100 -- p_uom_code UOM Code
101 -- p_user_id User ID
102 -- p_into_lpn_id Into LPN ID
103 -- p_serial_txn_temp_id Serial Transaction Temp ID to link against
104 -- MSNT records for serials that were loaded.
105 -- The MSN records will have the group mark ID
106 -- set to this value and the MSNT records will
107 -- be inserted with a transaction temp ID
108 -- equal to this value. (optional only for
109 -- serial controlled items)
110 --
111 -- IN OUT parameters:
112 -- p_txn_header_id Transaction header ID to use if a value
113 -- is passed in. -999 will be the default
114 -- value if no header ID exists yet from the
115 -- java mobile UI
116 --
117 -- Output parameters:
118 -- x_return_status
119 -- if the pre_process_load API succeeds, the value is
120 -- fnd_api.g_ret_sts_success;
121 -- if there is an expected error, the value is
122 -- fnd_api.g_ret_sts_error;
123 -- if there is an unexpected error, the value is
124 -- fnd_api.g_ret_sts_unexp_error;
125 -- x_msg_count
126 -- if there are one or more errors, the number of error messages
127 -- in the buffer
128 -- x_msg_data
129 -- if there is one and only one error, the error message
130 -- (See fnd_api package for more details about the above output parameters)
131 --
132 -- Functions: This API will insert appropriate dummy MMTT records for the
133 -- item load details entered. MTLT and MSNT records will
134 -- also be inserted as needed. This will store all of the
135 -- item load details first and use the same transaction
136 -- header ID to tie them all together (in the case of
137 -- multiple lots and serials during one item load cycle).
138 -- This is so we can have all the MMTT records needed and
139 -- just call either the receiving TM or Inventory TM once
140 -- to process them. This procedure will match the item
141 -- load details to the appropriate move order line(s) and
142 -- insert MMTT records which tie back to the matched move
143 -- order line(s).
144 PROCEDURE pre_process_load
145 (p_organization_id IN NUMBER ,
146 p_lpn_id IN NUMBER ,
147 p_inventory_item_id IN NUMBER ,
148 p_revision IN VARCHAR2 := NULL ,
149 p_lot_number IN VARCHAR2 := NULL ,
150 p_quantity IN NUMBER ,
151 p_uom_code IN VARCHAR2 ,
152 --laks
153 p_sec_quantity IN NUMBER ,
154 p_sec_uom_code IN VARCHAR2 ,
155 p_user_id IN NUMBER ,
156 p_into_lpn_id IN NUMBER ,
157 p_serial_txn_temp_id IN NUMBER := NULL ,
158 p_txn_header_id IN OUT NOCOPY NUMBER ,
159 x_return_status OUT NOCOPY VARCHAR2 ,
160 x_msg_count OUT NOCOPY NUMBER ,
161 x_msg_data OUT NOCOPY VARCHAR2);
162
163
164 -- Name: process_load
165 --
166 -- Input parameters:
167 -- p_txn_header_id Transaction header ID
168 -- p_serial_txn_temp_id Serial Transaction Temp ID for the serials
169 -- which were marked during counting. Passing
170 -- this value here so that the temporary MSNT
171 -- records inserted for marked serials can be
172 -- deleted if the item load was for serials.
173 -- p_lpn_context LPN Context so we know if we are working
174 -- with RCV, INV, or WIP LPN's.
175 -- p_lpn_id LPN ID
176 -- p_into_lpn_id Into LPN ID
177 -- p_organization_id Organization ID
178 -- p_user_id User ID
179 -- p_eqp_ins Equipment Instance
180 --
181 -- Output parameters:
182 -- x_return_status
183 -- if the process_load API succeeds, the value is
184 -- fnd_api.g_ret_sts_success;
185 -- if there is an expected error, the value is
186 -- fnd_api.g_ret_sts_error;
187 -- if there is an unexpected error, the value is
188 -- fnd_api.g_ret_sts_unexp_error;
189 -- x_msg_count
190 -- if there are one or more errors, the number of error messages
191 -- in the buffer
192 -- x_msg_data
193 -- if there is one and only one error, the error message
194 -- (See fnd_api package for more details about the above output parameters)
195 --
196 -- Functions: This API will process all of the MMTT records with
197 -- the given transaction header ID inserted by the prior
198 -- call to the pre_process_load procedure. For a receiving
199 -- item load, we will call wms_rcv_pup_pvt.pack_unpack_split
200 -- which will deal with splitting the move order lines,
201 -- inserting the appropriate RTI, MTLI, and MSNI records
202 -- needed to call the receiving manager. This procedure
203 -- will be called in a mode that will not invoke the
204 -- Receiving TM. Suggestions_PUB will be called for each
205 -- move order line created and returned from this
206 -- procedure. Finally, the receiving manager will be
207 -- called which has an implicit COMMIT prior to the call.
208 PROCEDURE process_load
209 (p_txn_header_id IN NUMBER ,
210 p_serial_txn_temp_id IN NUMBER := NULL ,
211 p_lpn_context IN NUMBER ,
212 p_lpn_id IN NUMBER ,
213 p_into_lpn_id IN NUMBER ,
214 p_organization_id IN NUMBER ,
215 p_user_id IN NUMBER ,
216 p_eqp_ins IN VARCHAR2 ,
217 x_return_status OUT NOCOPY VARCHAR2 ,
218 x_msg_count OUT NOCOPY NUMBER ,
219 x_msg_data OUT NOCOPY VARCHAR2);
220
221
222 -- Name: unmark_serials
223 --
224 -- Input parameters:
225 -- p_serial_txn_temp_id Serial Transaction Temp ID for the serials
226 -- which were marked during counting.
227 -- p_organization_id Organization ID
228 -- p_inventory_item_id Inventory Item ID
229 --
230 -- Output parameters:
231 -- x_return_status
232 -- if the process_load API succeeds, the value is
233 -- fnd_api.g_ret_sts_success;
234 -- if there is an expected error, the value is
235 -- fnd_api.g_ret_sts_error;
236 -- if there is an unexpected error, the value is
237 -- fnd_api.g_ret_sts_unexp_error;
238 -- x_msg_count
239 -- if there are one or more errors, the number of error messages
240 -- in the buffer
241 -- x_msg_data
242 -- if there is one and only one error, the error message
243 -- (See fnd_api package for more details about the above output parameters)
244 --
245 -- Functions: Unmark the serials that were marked during item load.
246 -- This is called in case the RCV TM was called and it
247 -- errored out. Since a commit is done, we cannot do a
248 -- rollback. The serials that were marked during loading
249 -- must be unmarked and the temporary MSNT records inserted
250 -- will be deleted.
251 PROCEDURE unmark_serials
252 (p_serial_txn_temp_id IN NUMBER ,
253 p_organization_id IN NUMBER ,
254 p_inventory_item_id IN NUMBER ,
255 x_return_status OUT NOCOPY VARCHAR2 ,
256 x_msg_count OUT NOCOPY NUMBER ,
257 x_msg_data OUT NOCOPY VARCHAR2);
258
259
260 -- Name: cleanup_ATF
261 --
262 -- Input parameters:
263 -- p_txn_header_id Transaction header ID for dummy MMTT's that
264 -- were inserted in the call to pre_process_load
265 -- p_lpn_context LPN Context so we know if we are working
266 -- with RCV, INV, or WIP LPN's.
267 -- p_lpn_id LPN ID from which we performed the item load
268 -- p_organization_id Organization ID
269 -- p_rcv_tm_called Boolean indicating if the RCV TM was called already
270 --
271 -- Output parameters:
272 -- x_return_status
273 -- if the process_load API succeeds, the value is
274 -- fnd_api.g_ret_sts_success;
275 -- if there is an expected error, the value is
276 -- fnd_api.g_ret_sts_error;
277 -- if there is an unexpected error, the value is
278 -- fnd_api.g_ret_sts_unexp_error;
279 -- x_msg_count
280 -- if there are one or more errors, the number of error messages
281 -- in the buffer
282 -- x_msg_data
283 -- if there is one and only one error, the error message
284 -- (See fnd_api package for more details about the above output parameters)
285 --
286 -- Functions: For receiving, this will call the ATF runtime API
292 -- the ATF runtime API delete_dispatched_task which will
287 -- Cleanup_Operation_Instance for each MMTT task record
288 -- that was activated. This is in order to rollback the WDT records
289 -- that were inserted and to revert the ATF operations done
290 -- when the operation was activated previously in the call
291 -- to process_load. For inventory and WIP, this will call
293 -- autonomously delete the WDT record that was inserted.
294 -- The rest of the ATF operation stuff is cleaned up
295 -- through a rollback.
296 PROCEDURE cleanup_ATF
297 (p_txn_header_id IN NUMBER ,
298 p_lpn_context IN NUMBER ,
299 p_lpn_id IN NUMBER ,
300 p_organization_id IN NUMBER ,
301 p_rcv_tm_called IN BOOLEAN ,
302 x_return_status OUT NOCOPY VARCHAR2 ,
303 x_msg_count OUT NOCOPY NUMBER ,
304 x_msg_data OUT NOCOPY VARCHAR2);
305
306
307 END WMS_ITEM_LOAD;