1 PACKAGE inv_replenish_detail_pub AUTHID CURRENT_USER AS
2 /* $Header: INVTOTXS.pls 120.2.12020000.5 2013/04/18 06:41:16 vipratap ship $ */
3
4 -- variables representing missing records and tables
5
6 --G_MISS_DETAIL_REC INV_AUTODETAIL.pp_row;
7 --G_MISS_DETAIL_REC_TBL INV_AUTODETAIL.pp_row_table;
8 --G_MISS_SERIAL_REC INV_AUTODETAIL.serial_row;
9 --G_MISS_SERIAL_REC_TBL INV_AUTODETAIL.serial_row_table;
10
11 -- procedure
12
13 PROCEDURE line_details_pub(
14 p_line_id IN NUMBER := fnd_api.g_miss_num
15 , x_number_of_rows OUT NOCOPY NUMBER
16 , x_detailed_qty OUT NOCOPY NUMBER
17 , x_return_status OUT NOCOPY VARCHAR2
18 , x_msg_count OUT NOCOPY NUMBER
19 , x_msg_data OUT NOCOPY VARCHAR2
20 , x_revision OUT NOCOPY VARCHAR2
21 , x_locator_id OUT NOCOPY NUMBER
22 , x_transfer_to_location OUT NOCOPY NUMBER
23 , x_lot_number OUT NOCOPY VARCHAR2
24 , x_expiration_date OUT NOCOPY DATE
25 , x_transaction_temp_id OUT NOCOPY NUMBER
26 , p_transaction_header_id IN NUMBER
27 , p_transaction_mode IN NUMBER
28 , p_move_order_type IN NUMBER
29 , p_serial_flag IN VARCHAR2
30 , p_plan_tasks IN BOOLEAN DEFAULT FALSE
31 , p_auto_pick_confirm IN BOOLEAN DEFAULT NULL
32 , p_commit IN BOOLEAN DEFAULT FALSE
33 , p_transaction_date IN DATE DEFAULT NULL
34 , p_period_id IN NUMBER DEFAULT NULL
35
36 );
37
38 -- HW INVCONV - overleoaded procedure
39 PROCEDURE line_details_pub(
40 p_line_id IN NUMBER := fnd_api.g_miss_num
41 , x_number_of_rows OUT NOCOPY NUMBER
42 , x_detailed_qty OUT NOCOPY NUMBER
43 , x_detailed_qty2 OUT NOCOPY NUMBER
44 , x_return_status OUT NOCOPY VARCHAR2
45 , x_msg_count OUT NOCOPY NUMBER
46 , x_msg_data OUT NOCOPY VARCHAR2
47 , x_revision OUT NOCOPY VARCHAR2
48 , x_locator_id OUT NOCOPY NUMBER
49 , x_transfer_to_location OUT NOCOPY NUMBER
50 , x_lot_number OUT NOCOPY VARCHAR2
51 , x_expiration_date OUT NOCOPY DATE
52 , x_transaction_temp_id OUT NOCOPY NUMBER
53 , p_transaction_header_id IN NUMBER
54 , p_transaction_mode IN NUMBER
55 , p_move_order_type IN NUMBER
56 , p_serial_flag IN VARCHAR2
57 , p_plan_tasks IN BOOLEAN DEFAULT FALSE
58 , p_auto_pick_confirm IN BOOLEAN DEFAULT NULL
59 , p_commit IN BOOLEAN DEFAULT FALSE
60 , p_transaction_date IN DATE DEFAULT NULL
61 , p_period_id IN NUMBER DEFAULT NULL
62 );
63
64 /* p_transaction_date,p_period_id parameters are added for the Bug#14371307. with out this fix, Transaction Date is showing as sysdate in Material Transaction Form even though Transaction Date in Transact Move Order Form is changed to past date. */
65
66 PROCEDURE assign_expenditure_org(p_transaction_temp_id NUMBER);
67
68 -- HW INVCONV Added Qty2
69 PROCEDURE delete_details(
70 p_transaction_temp_id IN NUMBER
71 , p_move_order_line_id IN NUMBER
72 , p_reservation_id IN NUMBER
73 , p_transaction_quantity IN NUMBER
74 , p_transaction_quantity2 IN NUMBER default FND_API.G_MISS_NUM
75 , p_primary_trx_qty IN NUMBER
76 , x_return_status OUT NOCOPY VARCHAR2
77 , x_msg_count OUT NOCOPY NUMBER
78 , x_msg_data OUT NOCOPY VARCHAR2
79 , p_delete_temp_records IN BOOLEAN default TRUE /*Bug#5505709*/
80 );
81
82 PROCEDURE clear_block_cancel(p_trx_header_id IN NUMBER, p_success IN OUT NOCOPY BOOLEAN);
83
84 PROCEDURE CLEAR_RECORD(p_trx_tmp_id IN NUMBER, p_success IN OUT NOCOPY BOOLEAN);
85
86 PROCEDURE split_line_details(
87 p_transaction_temp_id IN NUMBER
88 , p_missing_quantity IN NUMBER
89 , p_detailed_quantity IN NUMBER
90 , p_transaction_quantity IN NUMBER
91 , x_return_status OUT NOCOPY VARCHAR2
92 , x_msg_count OUT NOCOPY NUMBER
93 , x_msg_data OUT NOCOPY VARCHAR2
94 );
95
96 PROCEDURE auto_pick_confirm(
97 p_line_id IN NUMBER
98 , p_move_order_type IN NUMBER
99 , x_return_status OUT NOCOPY VARCHAR2
100 , x_msg_count OUT NOCOPY NUMBER
101 , x_msg_data OUT NOCOPY VARCHAR2
102 );
103
104 PROCEDURE changed_from_subinventory(
105 p_reservation_id IN NUMBER
106 , p_transaction_temp_id IN NUMBER
107 , p_old_subinventory IN VARCHAR2
108 , p_new_subinventory IN VARCHAR2
109 , p_new_locator_id IN NUMBER
110 , x_to_reservation_id OUT NOCOPY NUMBER
111 , x_return_status OUT NOCOPY VARCHAR2
112 , x_msg_count OUT NOCOPY NUMBER
113 , x_msg_data OUT NOCOPY VARCHAR2
114 );
115
116 PROCEDURE reserve_unconfirm_qty(
117 p_reservation_id IN NUMBER
118 , p_missing_quantity IN NUMBER
119 , x_return_status OUT NOCOPY VARCHAR2
120 , x_msg_count OUT NOCOPY NUMBER
121 , x_msg_data OUT NOCOPY VARCHAR2
122 );
123
124 --Check_Shipping_Tolerances
125 --
126 -- This API checks to make sure that transacting the current allocation
127 -- does not exceed shipping tolerances.
128 -- p_line_id : the move order line id.
129 -- p_quantity: the quantity to be transacted
130 -- x_allowed: 'Y' if txn is allowed, 'N' otherwise
131
132
133 PROCEDURE check_shipping_tolerances(
134 x_return_status OUT NOCOPY VARCHAR2
135 , x_msg_count OUT NOCOPY NUMBER
136 , x_msg_data OUT NOCOPY VARCHAR2
137 , x_allowed OUT NOCOPY VARCHAR2
138 , x_max_quantity OUT NOCOPY NUMBER
139 , x_max_quantity2 OUT NOCOPY NUMBER --MUOM Fulfillment Project
140 , p_line_id IN NUMBER
141 , p_quantity IN NUMBER
142 , p_quantity2 IN NUMBER DEFAULT NULL --MUOM Fulfillment Project
143 );
144
145 -- OVPK
146 -- Get_Overpick_Qty
147 --
148 -- This API will take 2 input parameters
149 -- 1. p_transaction_temp_id
150 -- 2. p_overpicked_qty
151 -- This API will return
152 -- 1. x_ovpk_allowed
153 -- 2. x_max_qty_allowed
154 -- x_ovpk_allowed will be 0 if overpicking is not allowed
155 -- x_ovpk_allowed will be 1 if overpicking is allowed
156 -- x_max_qty_allowed will return the max qty that can be picked for that task
157
158 -- For Manufacturing Component Pick - Move Order type 5,
159 -- Replenishment - Move Order type 2,
160 -- Requisition - Move Order type 1
161 -- where there is no tolerance set on the quantity that can be picked,
162 -- this procedure will return x_max_qty_allowed as -1
163
164 PROCEDURE get_overpick_qty(
165 p_transaction_temp_id IN NUMBER
166 , p_overpicked_qty IN NUMBER
167 , p_overpicked_qty2 IN NUMBER DEFAULT NULL --MUOM Fulfillment Project
168 , x_ovpk_allowed OUT NOCOPY NUMBER
169 , x_max_qty_allowed OUT NOCOPY NUMBER
170 , x_max_qty_allowed2 OUT NOCOPY NUMBER --MUOM Fulfillment Project
171 , x_return_status OUT NOCOPY VARCHAR2
172 , x_msg_count OUT NOCOPY NUMBER
173 , x_msg_data OUT NOCOPY VARCHAR2
174 );
175
176 -- OVPK
177 -- Check_Overpick
178 --
179 -- This API will take 10 input parameters
180 -- 1. p_transaction_temp_id
181 -- 2. p_overpicked_qty
182 -- 3. p_item_id
183 -- 4. p_rev
184 -- 5. p_lot_num
185 -- 6. p_lot_exp_date
186 -- 7. p_sub
187 -- 8. p_locator_id
188 -- 9. p_lpn_id
189 -- 10. p_overpicked_qty2
190 -- The procedure check_overpick will be called from the client java file
191 -- This API check_overpick will in turn call Get_Overpick_Qty to find
192 -- a) Is overpicking allowed for given Org, MO type and transaction_temp_id ?
193 -- b) What is the max quantity that can be overpicked ?
194 -- It will then log the appropriate error message if the user encounters such a state,
195 -- such as 'Overpicking not allowed' or 'Insufficient stock' or 'Shipping Tolerance exceeded'
196 -- Otherwise it will update QUANTITY_DETAILED in MTRL (if it is not a bulk picked task)
197 -- and return control to the calling routine, with x_check_overpick_passed set to 'Y'
198 -- thereby allowing him to overpick.
199 -- This API will also return x_ovpk_error_code
200 -- This OUT param will return 1 for error INV_OVERPICK_NOT_ALLOWED
201 -- 2 for error INV_LACK_MTRL_TO_OVERPICK
202 -- 3 for error INV_OVERSHIP_TOLERANCE
203
204 PROCEDURE check_overpick(
205 p_transaction_temp_id IN NUMBER
206 , p_overpicked_qty IN NUMBER
207 , p_overpicked_qty2 IN NUMBER --MUOM Fulfillment Project
208 , p_item_id IN NUMBER
209 , p_rev IN VARCHAR2
210 , p_lot_num IN VARCHAR2
211 , p_lot_exp_date IN DATE
212 , p_sub IN VARCHAR2
213 , p_locator_id IN NUMBER
214 , p_lpn_id IN NUMBER
215 , x_check_overpick_passed OUT NOCOPY VARCHAR
216 , x_ovpk_error_code OUT NOCOPY NUMBER
217 , x_return_status OUT NOCOPY VARCHAR2
218 , x_msg_count OUT NOCOPY NUMBER
219 , x_msg_data OUT NOCOPY VARCHAR2
220 );
221
222
223 -- OVPK
224 -- Check_Overpick(Overloaded procedure)
225 --
226 -- This API will take 10 input parameters
227 -- 1. p_transaction_temp_id
228 -- 2. p_overpicked_qty
229 -- 3. p_item_id
230 -- 4. p_rev
231 -- 5. p_lot_num
232 -- 6. p_lot_exp_date
233 -- 7. p_sub
234 -- 8. p_locator_id
235 -- 9. p_lpn_id
236 -- 10 p_att
237 -- The procedure check_overpick will be called from the client java file
238 -- This API check_overpick will in turn call Get_Overpick_Qty to find
239 -- a) Is overpicking allowed for given Org, MO type and transaction_temp_id ?
240 -- b) What is the max quantity that can be overpicked ?
241 -- It will then log the appropriate error message if the user encounters such a state,
242 -- such as 'Overpicking not allowed' or 'Insufficient stock' or 'Shipping Tolerance exceeded'
243 -- Otherwise it will update QUANTITY_DETAILED in MTRL (if it is not a bulk picked task)
244 -- and return control to the calling routine, with x_check_overpick_passed set to 'Y'
245 -- thereby allowing him to overpick.
246 -- This API will also return x_ovpk_error_code
247 -- This OUT param will return 1 for error INV_OVERPICK_NOT_ALLOWED
248 -- 2 for error INV_LACK_MTRL_TO_OVERPICK
249 -- 3 for error INV_OVERSHIP_TOLERANCE
250
251 PROCEDURE check_overpick(
252 p_transaction_temp_id IN NUMBER
253 , p_overpicked_qty IN NUMBER
254 , p_item_id IN NUMBER
255 , p_rev IN VARCHAR2
256 , p_lot_num IN VARCHAR2
257 , p_lot_exp_date IN DATE
258 , p_sub IN VARCHAR2
259 , p_locator_id IN NUMBER
260 , p_lpn_id IN NUMBER
261 , p_att IN NUMBER
262 , x_check_overpick_passed OUT NOCOPY VARCHAR
263 , x_ovpk_error_code OUT NOCOPY NUMBER
264 , x_return_status OUT NOCOPY VARCHAR2
265 , x_msg_count OUT NOCOPY NUMBER
266 , x_msg_data OUT NOCOPY VARCHAR2
267 );
268
269 -- OVPK
270 -- Check_Overpick
271 --
272 -- This API will take 9 input parameters
273 -- 1. p_transaction_temp_id
274 -- 2. p_overpicked_qty
275 -- 3. p_item_id
276 -- 4. p_rev
277 -- 5. p_lot_num
278 -- 6. p_lot_exp_date
279 -- 7. p_sub
280 -- 8. p_locator_id
281 -- 9. p_lpn_id
282 -- The procedure check_overpick will be called from the client java file
283 -- This API check_overpick will in turn call Get_Overpick_Qty to find
284 -- a) Is overpicking allowed for given Org, MO type and transaction_temp_id ?
285 -- b) What is the max quantity that can be overpicked ?
286 -- It will then log the appropriate error message if the user encounters such a state,
287 -- such as 'Overpicking not allowed' or 'Insufficient stock' or 'Shipping Tolerance exceeded'
288 -- Otherwise it will update QUANTITY_DETAILED in MTRL (if it is not a bulk picked task)
289 -- and return control to the calling routine, with x_check_overpick_passed set to 'Y'
290 -- thereby allowing him to overpick.
291 -- This API will also return x_ovpk_error_code
292 -- This OUT param will return 1 for error INV_OVERPICK_NOT_ALLOWED
293 -- 2 for error INV_LACK_MTRL_TO_OVERPICK
294 -- 3 for error INV_OVERSHIP_TOLERANCE
295 -- Overloaded for MUOM Fulfillment Project
296
297 PROCEDURE check_overpick(
298 p_transaction_temp_id IN NUMBER
299 , p_overpicked_qty IN NUMBER
300 , p_item_id IN NUMBER
301 , p_rev IN VARCHAR2
302 , p_lot_num IN VARCHAR2
303 , p_lot_exp_date IN DATE
304 , p_sub IN VARCHAR2
305 , p_locator_id IN NUMBER
309 , x_return_status OUT NOCOPY VARCHAR2
306 , p_lpn_id IN NUMBER
307 , x_check_overpick_passed OUT NOCOPY VARCHAR
308 , x_ovpk_error_code OUT NOCOPY NUMBER
310 , x_msg_count OUT NOCOPY NUMBER
311 , x_msg_data OUT NOCOPY VARCHAR2
312 );
313
314
315 -- OVPK - APL
316 -- This API is similar to get_overpick_qty
317 -- But this API will also do the MTLT level check for overpicking
318 -- which is not there in get_overpick_qty
319 -- The additional IN parameter needed here is p_lot_num
320 PROCEDURE get_overpick_qty_lot(
321 p_transaction_temp_id IN NUMBER
322 , p_overpicked_qty IN NUMBER
323 , p_overpicked_qty2 IN NUMBER DEFAULT NULL --MUOM Fulfillment Project
324 , p_lot_num IN VARCHAR2
325 , x_ovpk_allowed OUT NOCOPY NUMBER
326 , x_max_qty_allowed OUT NOCOPY NUMBER
327 , x_max_qty_allowed2 OUT NOCOPY NUMBER --MUOM Fulfillment Project
328 , x_other_mtlt OUT NOCOPY NUMBER
329 , x_other_mtlt2 OUT NOCOPY NUMBER --MUOM Fulfillment Project
330 , x_return_status OUT NOCOPY VARCHAR2
331 , x_msg_count OUT NOCOPY NUMBER
332 , x_msg_data OUT NOCOPY VARCHAR2
333 );
334
335 -- MUOM Fulfillment Project overloaded the procedure
336 PROCEDURE get_overpick_qty_lot(
337 p_transaction_temp_id IN NUMBER
338 , p_overpicked_qty IN NUMBER
339 , p_lot_num IN VARCHAR2
340 , x_ovpk_allowed OUT NOCOPY NUMBER
341 , x_max_qty_allowed OUT NOCOPY NUMBER
342 , x_other_mtlt OUT NOCOPY NUMBER
343 , x_return_status OUT NOCOPY VARCHAR2
344 , x_msg_count OUT NOCOPY NUMBER
345 , x_msg_data OUT NOCOPY VARCHAR2
346 );
347
348
349 -- OVPK - APL
350 -- This API is simliar to check_overpick except that it calls
351 -- get_overpick_qty_lot instead of get_overpick_qty
352 -- This API must be called when doing the MTLT level
353 -- check for overpicking.
354
355 PROCEDURE check_overpick_lot(
356 p_transaction_temp_id IN NUMBER
357 , p_overpicked_qty IN NUMBER
358 , p_overpicked_qty2 IN NUMBER --MUOM Fulfillment Project
359 , p_item_id IN NUMBER
360 , p_rev IN VARCHAR2
361 , p_lot_num IN VARCHAR2
362 , p_lot_exp_date IN DATE
363 , p_sub IN VARCHAR2
364 , p_locator_id IN NUMBER
365 , p_lpn_id IN NUMBER
366 , x_check_overpick_passed OUT NOCOPY VARCHAR
367 , x_ovpk_error_code OUT NOCOPY NUMBER
368 , x_return_status OUT NOCOPY VARCHAR2
369 , x_msg_count OUT NOCOPY NUMBER
370 , x_msg_data OUT NOCOPY VARCHAR2
371 );
372
373 -- OVPK - APL
374 -- This API is simliar to check_overpick except that it calls
375 -- get_overpick_qty_lot instead of get_overpick_qty
376 -- This API must be called when doing the MTLT level
377 -- check for overpicking.
378 -- Overloaded for MUOM Fulfillment Project
379 PROCEDURE check_overpick_lot(
380 p_transaction_temp_id IN NUMBER
381 , p_overpicked_qty IN NUMBER
382 , p_item_id IN NUMBER
383 , p_rev IN VARCHAR2
384 , p_lot_num IN VARCHAR2
385 , p_lot_exp_date IN DATE
386 , p_sub IN VARCHAR2
387 , p_locator_id IN NUMBER
388 , p_lpn_id IN NUMBER
389 , x_check_overpick_passed OUT NOCOPY VARCHAR
390 , x_ovpk_error_code OUT NOCOPY NUMBER
391 , x_return_status OUT NOCOPY VARCHAR2
392 , x_msg_count OUT NOCOPY NUMBER
393 , x_msg_data OUT NOCOPY VARCHAR2
394 );
395
396
397 END inv_replenish_detail_pub;