1 PACKAGE wms_picking_pkg AUTHID CURRENT_USER AS
2 /* $Header: WMSPLPDS.pls 120.10.12020000.1 2012/06/26 08:42:09 appldev ship $ */
3
4
5 g_pkg_name CONSTANT VARCHAR2(30) := 'WMS_PICKING_PKG';
6
7 TYPE task_status_table_type IS TABLE OF NUMBER INDEX BY LONG; --For bug 8552027
8 g_previous_task_status task_status_table_type;
9
10 TYPE task_start_over_table_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
11 g_start_over_tempid task_start_over_table_type;
12
13 TYPE t_genref IS REF CURSOR;
14
15 PROCEDURE change_task_to_active(p_transaction_temp_id IN NUMBER, x_return_status OUT NOCOPY VARCHAR2);
16
17 --
18 -- Name
19 -- PROCEDURE GET_NEXT_TASK_INFO
20 --
21 -- Purpose
22 -- Gets the task information.
23 --
24 -- Input Parameters
25 -- p_sign_on_emp_id => Employee ID
26 -- p_sign_on_org_id => Organization ID
27 -- p_transaction_temp_id => Transaction Temp ID (For Manual Pick)
28 -- p_cartonization_id => Cartonization ID (For Label Picking)
29 -- p_device_id => Device ID
30 -- p_is_cluster_pick => Cluster Pick or not
31 -- p_cartons_list => Carton Grouping ID List (For Cluster Picking)
32 --
33 -- Output Parameters
34 -- x_task_info => Ref Cursor containing the Task Information
35 -- x_return_status => FND_API.G_RET_STS_SUCESSS or
36 -- FND_API.G_RET_STS_ERROR
37 -- x_error_code => Code indicating the error message.
38 -- x_error_mesg => Error Messages
39 -- x_mesg_count => Error Messages Count
40
41 PROCEDURE get_next_task_info(
42 p_sign_on_emp_id IN NUMBER
43 , p_sign_on_org_id IN NUMBER
44 , p_transaction_temp_id IN NUMBER := NULL
45 , p_cartonization_id IN NUMBER := NULL
46 , p_device_id IN NUMBER := NULL
47 , x_return_status OUT NOCOPY VARCHAR2
48 , x_error_code OUT NOCOPY NUMBER
49 , x_mesg_count OUT NOCOPY NUMBER
50 , x_error_mesg OUT NOCOPY VARCHAR2
51 , x_task_info OUT NOCOPY t_genref
52 , p_is_cluster_pick IN VARCHAR2 := 'N'
53 , p_cartons_list IN VARCHAR2 := ' (-999) '
54 , p_is_manifest_pick IN VARCHAR2 := 'N' --Added for Case Picking Project
55 );
56
57
58 --
59 -- Name
60 -- PROCEDURE GET_TASKS
61 --
62 -- Purpose
63 -- Gets a list of Tasks given the LPN and Organization.
64 -- Changed as part of Bug#2666620. Refer it for any information.
65 --
66 -- Input Parameters
67 -- p_organization_id => Organization ID
68 -- p_transfer_lpn_id => LPN ID
69 --
70 -- Output Parameters
71 -- x_tasks => Ref Cursor containing the Tasks
72 -- x_drop_type => Either MFG or OTHERS depending on whether LPN has Mfg Picks or not
73 -- x_multiple_drops => Number of drop locations for Mfg Picks
74 -- x_drop_lpn_option => Drop LPN Option
75 -- x_return_status => FND_API.G_RET_STS_SUCESSS or
76 -- FND_API.G_RET_STS_ERROR
77
78 PROCEDURE get_tasks(
79 x_tasks OUT NOCOPY t_genref
80 , x_drop_type OUT NOCOPY VARCHAR2
81 , x_multiple_drops OUT NOCOPY VARCHAR2
82 , x_drop_lpn_option OUT NOCOPY NUMBER
83 , x_return_status OUT NOCOPY VARCHAR2
84 , p_organization_id IN NUMBER
85 , p_transfer_lpn_id IN NUMBER
86 );
87
88
89 --
90 -- Name
91 -- PROCEDURE GET_LOT_NUMBER_INFO
92 --
93 -- Purpose
94 -- Gets the list of all Lots and its Quantity for the passed in list of Transaction Temp IDs.
95 -- Added as part of Bug#2666620. Refer it for any information.
96 --
97 -- Input Parameters
98 -- p_txn_temp_id_list => Comma delimited Transaction Temp ID List
99 --
100 -- Output Parameters
101 -- x_return_status => FND_API.G_RET_STS_SUCESSS or
102 -- FND_API.G_RET_STS_ERROR
103 -- x_lot_num_list => Comma delimited Lot Number List
104 -- x_lot_qty_list => Comma delimited Lot Qty List
105 -- x_display_serials => Whether Serials are associated with the Txn Temp ID list.
106
107 PROCEDURE get_lot_number_info(
108 x_return_status OUT NOCOPY VARCHAR2
109 , x_lot_num_list OUT NOCOPY VARCHAR2
110 , x_lot_qty_list OUT NOCOPY VARCHAR2
111 , x_display_serials OUT NOCOPY VARCHAR2
112 , p_txn_temp_id_list IN VARCHAR2
113 );
114
115 --
116 -- Name
117 -- PROCEDURE GET_SERIAL_NUMBERS
118 --
119 -- Purpose
120 -- Gets the list of all Serials for the passed in list of Transaction Temp IDs. If Lot is given
121 -- the list contains Serials belonging to that Lot alone.
122 -- Added as part of Bug#2666620. Refer it for any information.
123 --
124 -- Input Parameters
125 -- p_txn_temp_id_list => Comma delimited Transaction Temp ID List
126 -- p_lot_number => Lot Number
127 --
128 -- Output Parameters
129 -- x_return_status => FND_API.G_RET_STS_SUCESSS or
130 -- FND_API.G_RET_STS_ERROR
131 -- x_serial_list => Comma Delimited Serial List
132 PROCEDURE get_serial_numbers(
133 x_return_status OUT NOCOPY VARCHAR2
134 , x_serial_list OUT NOCOPY VARCHAR2
135 , p_txn_temp_id_list IN VARCHAR2
136 , p_lot_number IN VARCHAR2
137 );
138
139 PROCEDURE next_task
140 (p_employee_id IN NUMBER,
141 p_effective_start_date IN DATE,
142 p_effective_end_date IN DATE,
143 p_organization_id IN NUMBER,
144 p_subinventory_code IN VARCHAR2,
145 p_equipment_id IN NUMBER,
146 p_equipment_serial IN VARCHAR2,
147 p_number_of_devices IN NUMBER,
148 p_device_id IN NUMBER,
149 p_task_filter IN VARCHAR2,
150 p_task_method IN VARCHAR2,
151 p_prioritize_dispatched_tasks IN VARCHAR2 := 'N', -- BugFix 4560814
152 p_retain_dispatch_task IN VARCHAR2 := 'N', -- BugFix 4560814
153 p_allow_unreleased_task IN VARCHAR2 :='Y', -- for manual picking only bug 4718145
154 p_max_clusters IN NUMBER := null, -- added for cluster picking
155 p_dispatch_needed IN VARCHAR2 := 'Y', -- added for cluster picking
156 x_grouping_document_type IN OUT nocopy VARCHAR2,
157 x_grouping_document_number IN OUT nocopy NUMBER,
158 x_grouping_source_type_id IN OUT nocopy NUMBER,
159 x_is_changed_group IN OUT nocopy VARCHAR2,
160 x_task_info OUT nocopy t_genref,
161 x_task_number OUT nocopy NUMBER,
162 x_num_of_tasks OUT nocopy NUMBER,
163 x_task_type_id OUT nocopy NUMBER,
164 x_avail_device_id OUT nocopy NUMBER,
165 x_device_request_id OUT nocopy NUMBER,
166 x_return_status OUT nocopy VARCHAR2,
167 x_msg_count OUT nocopy NUMBER,
168 x_msg_data OUT nocopy VARCHAR2);
169
170 TYPE task_cursor_type IS REF CURSOR;
171
172
173 TYPE task_record_type IS RECORD
174 (transaction_temp_id NUMBER,
175 subinventory_code VARCHAR2(10),
176 locator_id NUMBER,
177 revision VARCHAR2(3),
178 transaction_uom VARCHAR2(10),
179 transaction_quantity NUMBER,
180 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
181 lot_number VARCHAR2(80),
182 task_type NUMBER,
183 priority NUMBER,
184 operation_plan_id NUMBER,
185 standard_operation_id NUMBER,
186 effective_start_date DATE,
187 effective_end_date DATE,
188 person_resource_id NUMBER,
189 machine_resource_id NUMBER,
190 move_order_line_id NUMBER);
191
192
193 --Start Additions for Bug 6682436
194
195 new_task_table wms_Task_mgmt_pub.new_task_tbl;
196
197 PROCEDURE split_mmtt_lpn(
198 p_transaction_temp_id IN NUMBER
199 , p_line_quantity IN NUMBER
200 , p_transaction_UOM IN VARCHAR2
201 , p_lpn_id IN NUMBER
202 , l_transaction_temp_id OUT NOCOPY NUMBER
203 ,x_return_status OUT NOCOPY VARCHAR2
204 ,x_msg_count OUT NOCOPY NUMBER
205 ,x_msg_data OUT NOCOPY VARCHAR2
206 );
207
208 PROCEDURE split_task( p_source_transaction_number IN NUMBER DEFAULT NULL,
209 p_split_quantities IN wms_Task_mgmt_pub.TASK_QTY_TBL_TYPE ,
210 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
211 x_resultant_tasks OUT NOCOPY WMS_TASK_MGMT_PUB.task_tab_type ,
212 x_resultant_task_details OUT NOCOPY wms_Task_mgmt_pub.TASK_DETAIL_TBL_TYPE ,
213 x_return_status OUT NOCOPY VARCHAR2 ,
214 x_msg_count OUT NOCOPY NUMBER ,
215 x_msg_data OUT NOCOPY VARCHAR2 );
216
217 PROCEDURE validate_quantities( p_transaction_temp_id IN NUMBER ,
218 p_split_quantities IN wms_Task_mgmt_pub.task_qty_tbl_type ,
219 x_lot_control_code OUT NOCOPY NUMBER ,
220 x_serial_control_code OUT NOCOPY NUMBER ,
221 x_split_uom_quantities OUT NOCOPY wms_Task_mgmt_pub.qty_changed_tbl_type ,
222 x_return_status OUT NOCOPY VARCHAR2 ,
223 x_msg_data OUT NOCOPY VARCHAR2 ,
224 x_msg_count OUT NOCOPY VARCHAR2 );
225
226 PROCEDURE split_mmtt( p_orig_transaction_temp_id IN NUMBER ,
227 p_new_transaction_temp_id IN NUMBER ,
228 p_new_transaction_header_id IN NUMBER ,
229 p_new_mol_id IN NUMBER ,
230 p_transaction_qty_to_split IN NUMBER ,
231 p_primary_qty_to_split IN NUMBER ,
232 x_return_status OUT NOCOPY VARCHAR2 ,
233 x_msg_data OUT NOCOPY VARCHAR2 ,
234 x_msg_count OUT NOCOPY VARCHAR2 );
235
236 PROCEDURE split_wdt( p_new_task_id IN NUMBER ,
237 p_new_transaction_temp_id IN NUMBER ,
238 p_new_mol_id IN NUMBER ,
239 p_orig_transaction_temp_id IN NUMBER ,
240 x_return_status OUT NOCOPY VARCHAR2 ,
241 x_msg_data OUT NOCOPY VARCHAR2 ,
242 x_msg_count OUT NOCOPY VARCHAR2 );
243
244 PROCEDURE split_lot_serial( p_orig_transaction_temp_id IN NUMBER ,
245 p_new_transaction_temp_id IN NUMBER ,
246 p_transaction_qty_to_split IN NUMBER ,
247 p_primary_qty_to_split IN NUMBER ,
248 p_inventory_item_id IN NUMBER ,
249 p_organization_id IN NUMBER ,
250 x_return_status OUT NOCOPY VARCHAR2 ,
251 x_msg_data OUT NOCOPY VARCHAR2 ,
252 x_msg_count OUT NOCOPY VARCHAR2 );
253
254 PROCEDURE split_serial( p_orig_transaction_temp_id IN NUMBER ,
255 p_new_transaction_temp_id IN NUMBER ,
256 p_transaction_qty_to_split IN NUMBER ,
257 p_primary_qty_to_split IN NUMBER ,
258 p_inventory_item_id IN NUMBER ,
259 p_organization_id IN NUMBER ,
260 x_return_status OUT NOCOPY VARCHAR2 ,
261 x_msg_data OUT NOCOPY VARCHAR2 ,
262 x_msg_count OUT NOCOPY VARCHAR2 );
263
264 PROCEDURE split_mtlt ( p_new_transaction_temp_id IN NUMBER ,
265 p_transaction_qty_to_split IN NUMBER ,
266 p_primary_qty_to_split IN NUMBER ,
267 p_row_id IN ROWID ,
268 x_return_status OUT NOCOPY VARCHAR2 ,
269 x_msg_data OUT NOCOPY VARCHAR2 ,
270 x_msg_count OUT NOCOPY VARCHAR2 );
271 --End Additions for Bug 6682436
272 --Begin of Bug: 7254397
273 TYPE numset_t IS TABLE OF NUMBER;
274 TYPE numset_tabType IS TABLE OF NUMBER
275 INDEX BY BINARY_INTEGER;
276 PROCEDURE insert_cartonization_id (
277 p_lpn_id IN NUMBER,
281
278 x_return_status OUT nocopy VARCHAR2,
279 x_msg_count OUT nocopy NUMBER,
280 x_msg_data OUT nocopy VARCHAR2);
282 FUNCTION list_cartonization_id RETURN numset_t PIPELINED;
283
284 PROCEDURE clear_cartonization_id(
285 x_return_status OUT nocopy VARCHAR2,
286 x_msg_count OUT nocopy NUMBER,
287 x_msg_data OUT nocopy VARCHAR2);
288 --End of Bug: 7254397
289 FUNCTION get_total_lpns RETURN NUMBER;
290
291
292
293 --Added for Case Picking Project start
294
295 FUNCTION list_order_numbers RETURN numset_t PIPELINED;
296 FUNCTION list_pick_slip_numbers RETURN numset_t PIPELINED;
297
298 PROCEDURE insert_order_numbers (
299 p_order_number IN NUMBER,
300 x_return_status OUT nocopy VARCHAR2,
301 x_msg_count OUT nocopy NUMBER,
302 x_msg_data OUT nocopy VARCHAR2);
303
304 PROCEDURE insert_pick_slip_number (
305 p_pick_slip_number IN NUMBER,
306 x_return_status OUT nocopy VARCHAR2,
307 x_msg_count OUT nocopy NUMBER,
308 x_msg_data OUT nocopy VARCHAR2);
309
310 PROCEDURE clear_order_numbers(
311 x_return_status OUT nocopy VARCHAR2,
312 x_msg_count OUT nocopy NUMBER,
313 x_msg_data OUT nocopy VARCHAR2);
314
315 PROCEDURE clear_pick_slip_number(
316 x_return_status OUT nocopy VARCHAR2,
317 x_msg_count OUT nocopy NUMBER,
318 x_msg_data OUT nocopy VARCHAR2);
319
320 --Added for Case Picking Project end
321
322
323 END wms_picking_pkg;
[Home] [Help]