1 PACKAGE inv_replenish_detail_pub AS
2 /* $Header: INVTOTXS.pls 120.2.12000000.1 2007/01/17 16:32:02 appldev 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 );
34
35 -- HW INVCONV - overleoaded procedure
36 PROCEDURE line_details_pub(
37 p_line_id IN NUMBER := fnd_api.g_miss_num
38 , x_number_of_rows OUT NOCOPY NUMBER
39 , x_detailed_qty OUT NOCOPY NUMBER
40 , x_detailed_qty2 OUT NOCOPY NUMBER
41 , x_return_status OUT NOCOPY VARCHAR2
42 , x_msg_count OUT NOCOPY NUMBER
43 , x_msg_data OUT NOCOPY VARCHAR2
44 , x_revision OUT NOCOPY VARCHAR2
45 , x_locator_id OUT NOCOPY NUMBER
46 , x_transfer_to_location OUT NOCOPY NUMBER
47 , x_lot_number OUT NOCOPY VARCHAR2
48 , x_expiration_date OUT NOCOPY DATE
49 , x_transaction_temp_id OUT NOCOPY NUMBER
50 , p_transaction_header_id IN NUMBER
51 , p_transaction_mode IN NUMBER
52 , p_move_order_type IN NUMBER
53 , p_serial_flag IN VARCHAR2
54 , p_plan_tasks IN BOOLEAN DEFAULT FALSE
55 , p_auto_pick_confirm IN BOOLEAN DEFAULT NULL
56 , p_commit IN BOOLEAN DEFAULT FALSE
57 );
58
59
60
61 PROCEDURE assign_expenditure_org(p_transaction_temp_id NUMBER);
62
63 -- HW INVCONV Added Qty2
64 PROCEDURE delete_details(
65 p_transaction_temp_id IN NUMBER
66 , p_move_order_line_id IN NUMBER
67 , p_reservation_id IN NUMBER
68 , p_transaction_quantity IN NUMBER
69 , p_transaction_quantity2 IN NUMBER default FND_API.G_MISS_NUM
70 , p_primary_trx_qty IN NUMBER
71 , x_return_status OUT NOCOPY VARCHAR2
72 , x_msg_count OUT NOCOPY NUMBER
73 , x_msg_data OUT NOCOPY VARCHAR2
74 , p_delete_temp_records IN BOOLEAN default TRUE /*Bug#5505709*/
75 );
76
77 PROCEDURE clear_block_cancel(p_trx_header_id IN NUMBER, p_success IN OUT NOCOPY BOOLEAN);
78
79 PROCEDURE CLEAR_RECORD(p_trx_tmp_id IN NUMBER, p_success IN OUT NOCOPY BOOLEAN);
80
81 PROCEDURE split_line_details(
82 p_transaction_temp_id IN NUMBER
83 , p_missing_quantity IN NUMBER
84 , p_detailed_quantity IN NUMBER
85 , p_transaction_quantity IN NUMBER
86 , x_return_status OUT NOCOPY VARCHAR2
87 , x_msg_count OUT NOCOPY NUMBER
88 , x_msg_data OUT NOCOPY VARCHAR2
89 );
90
91 PROCEDURE auto_pick_confirm(
92 p_line_id IN NUMBER
93 , p_move_order_type IN NUMBER
94 , x_return_status OUT NOCOPY VARCHAR2
95 , x_msg_count OUT NOCOPY NUMBER
96 , x_msg_data OUT NOCOPY VARCHAR2
97 );
98
99 PROCEDURE changed_from_subinventory(
100 p_reservation_id IN NUMBER
101 , p_transaction_temp_id IN NUMBER
102 , p_old_subinventory IN VARCHAR2
103 , p_new_subinventory IN VARCHAR2
104 , p_new_locator_id IN NUMBER
105 , x_to_reservation_id OUT NOCOPY NUMBER
106 , x_return_status OUT NOCOPY VARCHAR2
107 , x_msg_count OUT NOCOPY NUMBER
108 , x_msg_data OUT NOCOPY VARCHAR2
109 );
110
111 PROCEDURE reserve_unconfirm_qty(
112 p_reservation_id IN NUMBER
113 , p_missing_quantity IN NUMBER
114 , x_return_status OUT NOCOPY VARCHAR2
115 , x_msg_count OUT NOCOPY NUMBER
116 , x_msg_data OUT NOCOPY VARCHAR2
117 );
118
119 --Check_Shipping_Tolerances
120 --
121 -- This API checks to make sure that transacting the current allocation
122 -- does not exceed shipping tolerances.
123 -- p_line_id : the move order line id.
124 -- p_quantity: the quantity to be transacted
125 -- x_allowed: 'Y' if txn is allowed, 'N' otherwise
126
127
128 PROCEDURE check_shipping_tolerances(
129 x_return_status OUT NOCOPY VARCHAR2
130 , x_msg_count OUT NOCOPY NUMBER
131 , x_msg_data OUT NOCOPY VARCHAR2
132 , x_allowed OUT NOCOPY VARCHAR2
133 , x_max_quantity OUT NOCOPY NUMBER
134 , p_line_id IN NUMBER
135 , p_quantity IN NUMBER
136 );
137
138 -- OVPK
139 -- Get_Overpick_Qty
140 --
141 -- This API will take 2 input parameters
142 -- 1. p_transaction_temp_id
143 -- 2. p_overpicked_qty
144 -- This API will return
145 -- 1. x_ovpk_allowed
146 -- 2. x_max_qty_allowed
147 -- x_ovpk_allowed will be 0 if overpicking is not allowed
148 -- x_ovpk_allowed will be 1 if overpicking is allowed
149 -- x_max_qty_allowed will return the max qty that can be picked for that task
150
151 -- For Manufacturing Component Pick - Move Order type 5,
152 -- Replenishment - Move Order type 2,
153 -- Requisition - Move Order type 1
154 -- where there is no tolerance set on the quantity that can be picked,
155 -- this procedure will return x_max_qty_allowed as -1
156
157 PROCEDURE get_overpick_qty(
158 p_transaction_temp_id IN NUMBER
159 , p_overpicked_qty IN NUMBER
160 , x_ovpk_allowed OUT NOCOPY NUMBER
161 , x_max_qty_allowed OUT NOCOPY NUMBER
162 , x_return_status OUT NOCOPY VARCHAR2
163 , x_msg_count OUT NOCOPY NUMBER
164 , x_msg_data OUT NOCOPY VARCHAR2
165 );
166
167 -- OVPK
168 -- Check_Overpick
169 --
170 -- This API will take 9 input parameters
171 -- 1. p_transaction_temp_id
172 -- 2. p_overpicked_qty
173 -- 3. p_item_id
174 -- 4. p_rev
175 -- 5. p_lot_num
176 -- 6. p_lot_exp_date
177 -- 7. p_sub
178 -- 8. p_locator_id
179 -- 9. p_lpn_id
180 -- The procedure check_overpick will be called from the client java file
181 -- This API check_overpick will in turn call Get_Overpick_Qty to find
182 -- a) Is overpicking allowed for given Org, MO type and transaction_temp_id ?
183 -- b) What is the max quantity that can be overpicked ?
184 -- It will then log the appropriate error message if the user encounters such a state,
185 -- such as 'Overpicking not allowed' or 'Insufficient stock' or 'Shipping Tolerance exceeded'
186 -- Otherwise it will update QUANTITY_DETAILED in MTRL (if it is not a bulk picked task)
187 -- and return control to the calling routine, with x_check_overpick_passed set to 'Y'
188 -- thereby allowing him to overpick.
189 -- This API will also return x_ovpk_error_code
190 -- This OUT param will return 1 for error INV_OVERPICK_NOT_ALLOWED
191 -- 2 for error INV_LACK_MTRL_TO_OVERPICK
192 -- 3 for error INV_OVERSHIP_TOLERANCE
193
194 PROCEDURE check_overpick(
195 p_transaction_temp_id IN NUMBER
196 , p_overpicked_qty IN NUMBER
197 , p_item_id IN NUMBER
198 , p_rev IN VARCHAR2
199 , p_lot_num IN VARCHAR2
200 , p_lot_exp_date IN DATE
201 , p_sub IN VARCHAR2
202 , p_locator_id IN NUMBER
203 , p_lpn_id IN NUMBER
204 , x_check_overpick_passed OUT NOCOPY VARCHAR
205 , x_ovpk_error_code OUT NOCOPY NUMBER
206 , x_return_status OUT NOCOPY VARCHAR2
207 , x_msg_count OUT NOCOPY NUMBER
208 , x_msg_data OUT NOCOPY VARCHAR2
209 );
210
211
212 -- OVPK
213 -- Check_Overpick(Overloaded procedure)
214 --
215 -- This API will take 10 input parameters
216 -- 1. p_transaction_temp_id
217 -- 2. p_overpicked_qty
218 -- 3. p_item_id
219 -- 4. p_rev
220 -- 5. p_lot_num
221 -- 6. p_lot_exp_date
222 -- 7. p_sub
223 -- 8. p_locator_id
224 -- 9. p_lpn_id
225 -- 10 p_att
226 -- The procedure check_overpick will be called from the client java file
227 -- This API check_overpick will in turn call Get_Overpick_Qty to find
228 -- a) Is overpicking allowed for given Org, MO type and transaction_temp_id ?
229 -- b) What is the max quantity that can be overpicked ?
230 -- It will then log the appropriate error message if the user encounters such a state,
231 -- such as 'Overpicking not allowed' or 'Insufficient stock' or 'Shipping Tolerance exceeded'
232 -- Otherwise it will update QUANTITY_DETAILED in MTRL (if it is not a bulk picked task)
233 -- and return control to the calling routine, with x_check_overpick_passed set to 'Y'
234 -- thereby allowing him to overpick.
235 -- This API will also return x_ovpk_error_code
236 -- This OUT param will return 1 for error INV_OVERPICK_NOT_ALLOWED
237 -- 2 for error INV_LACK_MTRL_TO_OVERPICK
238 -- 3 for error INV_OVERSHIP_TOLERANCE
239
240 PROCEDURE check_overpick(
241 p_transaction_temp_id IN NUMBER
242 , p_overpicked_qty IN NUMBER
243 , p_item_id IN NUMBER
244 , p_rev IN VARCHAR2
245 , p_lot_num IN VARCHAR2
246 , p_lot_exp_date IN DATE
247 , p_sub IN VARCHAR2
248 , p_locator_id IN NUMBER
249 , p_lpn_id IN NUMBER
250 , p_att IN NUMBER
251 , x_check_overpick_passed OUT NOCOPY VARCHAR
252 , x_ovpk_error_code OUT NOCOPY NUMBER
253 , x_return_status OUT NOCOPY VARCHAR2
254 , x_msg_count OUT NOCOPY NUMBER
255 , x_msg_data OUT NOCOPY VARCHAR2
256 );
257
258 -- OVPK - APL
259 -- This API is similar to get_overpick_qty
260 -- But this API will also do the MTLT level check for overpicking
261 -- which is not there in get_overpick_qty
262 -- The additional IN parameter needed here is p_lot_num
263 PROCEDURE get_overpick_qty_lot(
264 p_transaction_temp_id IN NUMBER
265 , p_overpicked_qty IN NUMBER
266 , p_lot_num IN VARCHAR2
267 , x_ovpk_allowed OUT NOCOPY NUMBER
268 , x_max_qty_allowed OUT NOCOPY NUMBER
269 , x_other_mtlt OUT NOCOPY NUMBER
270 , x_return_status OUT NOCOPY VARCHAR2
271 , x_msg_count OUT NOCOPY NUMBER
272 , x_msg_data OUT NOCOPY VARCHAR2
273 );
274
275 -- OVPK - APL
276 -- This API is simliar to check_overpick except that it calls
277 -- get_overpick_qty_lot instead of get_overpick_qty
278 -- This API must be called when doing the MTLT level
279 -- check for overpicking.
280 PROCEDURE check_overpick_lot(
281 p_transaction_temp_id IN NUMBER
282 , p_overpicked_qty IN NUMBER
283 , p_item_id IN NUMBER
284 , p_rev IN VARCHAR2
285 , p_lot_num IN VARCHAR2
286 , p_lot_exp_date IN DATE
287 , p_sub IN VARCHAR2
288 , p_locator_id IN NUMBER
289 , p_lpn_id IN NUMBER
290 , x_check_overpick_passed OUT NOCOPY VARCHAR
291 , x_ovpk_error_code OUT NOCOPY NUMBER
292 , x_return_status OUT NOCOPY VARCHAR2
293 , x_msg_count OUT NOCOPY NUMBER
294 , x_msg_data OUT NOCOPY VARCHAR2
295 );
296
297 END inv_replenish_detail_pub;