1 PACKAGE wms_picking_pkg AS
2 /* $Header: WMSPLPDS.pls 120.7.12010000.2 2008/08/19 09:55:08 anviswan 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 BINARY_INTEGER;
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 );
55
56
57 --
58 -- Name
59 -- PROCEDURE GET_TASKS
60 --
61 -- Purpose
62 -- Gets a list of Tasks given the LPN and Organization.
63 -- Changed as part of Bug#2666620. Refer it for any information.
64 --
65 -- Input Parameters
66 -- p_organization_id => Organization ID
67 -- p_transfer_lpn_id => LPN ID
68 --
69 -- Output Parameters
70 -- x_tasks => Ref Cursor containing the Tasks
71 -- x_drop_type => Either MFG or OTHERS depending on whether LPN has Mfg Picks or not
72 -- x_multiple_drops => Number of drop locations for Mfg Picks
73 -- x_drop_lpn_option => Drop LPN Option
74 -- x_return_status => FND_API.G_RET_STS_SUCESSS or
75 -- FND_API.G_RET_STS_ERROR
76
77 PROCEDURE get_tasks(
78 x_tasks OUT NOCOPY t_genref
79 , x_drop_type OUT NOCOPY VARCHAR2
80 , x_multiple_drops OUT NOCOPY VARCHAR2
81 , x_drop_lpn_option OUT NOCOPY NUMBER
82 , x_return_status OUT NOCOPY VARCHAR2
83 , p_organization_id IN NUMBER
84 , p_transfer_lpn_id IN NUMBER
85 );
86
87
88 --
89 -- Name
90 -- PROCEDURE GET_LOT_NUMBER_INFO
91 --
92 -- Purpose
93 -- Gets the list of all Lots and its Quantity for the passed in list of Transaction Temp IDs.
94 -- Added as part of Bug#2666620. Refer it for any information.
95 --
96 -- Input Parameters
97 -- p_txn_temp_id_list => Comma delimited Transaction Temp ID List
98 --
99 -- Output Parameters
100 -- x_return_status => FND_API.G_RET_STS_SUCESSS or
101 -- FND_API.G_RET_STS_ERROR
102 -- x_lot_num_list => Comma delimited Lot Number List
103 -- x_lot_qty_list => Comma delimited Lot Qty List
104 -- x_display_serials => Whether Serials are associated with the Txn Temp ID list.
105
106 PROCEDURE get_lot_number_info(
107 x_return_status OUT NOCOPY VARCHAR2
108 , x_lot_num_list OUT NOCOPY VARCHAR2
109 , x_lot_qty_list OUT NOCOPY VARCHAR2
110 , x_display_serials OUT NOCOPY VARCHAR2
111 , p_txn_temp_id_list IN VARCHAR2
112 );
113
114 --
115 -- Name
116 -- PROCEDURE GET_SERIAL_NUMBERS
117 --
118 -- Purpose
119 -- Gets the list of all Serials for the passed in list of Transaction Temp IDs. If Lot is given
120 -- the list contains Serials belonging to that Lot alone.
121 -- Added as part of Bug#2666620. Refer it for any information.
122 --
123 -- Input Parameters
124 -- p_txn_temp_id_list => Comma delimited Transaction Temp ID List
125 -- p_lot_number => Lot Number
126 --
127 -- Output Parameters
128 -- x_return_status => FND_API.G_RET_STS_SUCESSS or
129 -- FND_API.G_RET_STS_ERROR
130 -- x_serial_list => Comma Delimited Serial List
131 PROCEDURE get_serial_numbers(
132 x_return_status OUT NOCOPY VARCHAR2
133 , x_serial_list OUT NOCOPY VARCHAR2
134 , p_txn_temp_id_list IN VARCHAR2
135 , p_lot_number IN VARCHAR2
136 );
137
138 PROCEDURE next_task
139 (p_employee_id IN NUMBER,
140 p_effective_start_date IN DATE,
141 p_effective_end_date IN DATE,
142 p_organization_id IN NUMBER,
143 p_subinventory_code IN VARCHAR2,
144 p_equipment_id IN NUMBER,
145 p_equipment_serial IN VARCHAR2,
146 p_number_of_devices IN NUMBER,
147 p_device_id IN NUMBER,
148 p_task_filter IN VARCHAR2,
149 p_task_method IN VARCHAR2,
150 p_prioritize_dispatched_tasks IN VARCHAR2 := 'N', -- BugFix 4560814
151 p_retain_dispatch_task IN VARCHAR2 := 'N', -- BugFix 4560814
152 p_allow_unreleased_task IN VARCHAR2 :='Y', -- for manual picking only bug 4718145
153 p_max_clusters IN NUMBER := null, -- added for cluster picking
154 p_dispatch_needed IN VARCHAR2 := 'Y', -- added for cluster picking
155 x_grouping_document_type IN OUT nocopy VARCHAR2,
156 x_grouping_document_number IN OUT nocopy NUMBER,
157 x_grouping_source_type_id IN OUT nocopy NUMBER,
158 x_is_changed_group IN OUT nocopy VARCHAR2,
159 x_task_info OUT nocopy t_genref,
160 x_task_number OUT nocopy NUMBER,
161 x_num_of_tasks OUT nocopy NUMBER,
162 x_task_type_id OUT nocopy NUMBER,
163 x_avail_device_id OUT nocopy NUMBER,
164 x_device_request_id OUT nocopy NUMBER,
165 x_return_status OUT nocopy VARCHAR2,
166 x_msg_count OUT nocopy NUMBER,
167 x_msg_data OUT nocopy VARCHAR2);
168
169 TYPE task_cursor_type IS REF CURSOR;
170
171
172 TYPE task_record_type IS RECORD
173 (transaction_temp_id NUMBER,
174 subinventory_code VARCHAR2(10),
175 locator_id NUMBER,
176 revision VARCHAR2(3),
177 transaction_uom VARCHAR2(10),
178 transaction_quantity NUMBER,
179 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
180 lot_number VARCHAR2(80),
181 task_type NUMBER,
182 priority NUMBER,
183 operation_plan_id NUMBER,
184 standard_operation_id NUMBER,
185 effective_start_date DATE,
186 effective_end_date DATE,
187 person_resource_id NUMBER,
188 machine_resource_id NUMBER,
189 move_order_line_id NUMBER);
190
191
192 --Start Additions for Bug 6682436
193
194 new_task_table wms_Task_mgmt_pub.new_task_tbl;
195
196 PROCEDURE split_mmtt_lpn(
197 p_transaction_temp_id IN NUMBER
198 , p_line_quantity IN NUMBER
199 , p_transaction_UOM IN VARCHAR2
200 , p_lpn_id IN NUMBER
201 , l_transaction_temp_id OUT NOCOPY NUMBER
202 ,x_return_status OUT NOCOPY VARCHAR2
203 ,x_msg_count OUT NOCOPY NUMBER
204 ,x_msg_data OUT NOCOPY VARCHAR2
205 );
206
207 PROCEDURE split_task( p_source_transaction_number IN NUMBER DEFAULT NULL,
208 p_split_quantities IN wms_Task_mgmt_pub.TASK_QTY_TBL_TYPE ,
209 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
210 x_resultant_tasks OUT NOCOPY WMS_TASK_MGMT_PUB.task_tab_type ,
211 x_resultant_task_details OUT NOCOPY wms_Task_mgmt_pub.TASK_DETAIL_TBL_TYPE ,
212 x_return_status OUT NOCOPY VARCHAR2 ,
213 x_msg_count OUT NOCOPY NUMBER ,
214 x_msg_data OUT NOCOPY VARCHAR2 );
215
216 PROCEDURE validate_quantities( p_transaction_temp_id IN NUMBER ,
217 p_split_quantities IN wms_Task_mgmt_pub.task_qty_tbl_type ,
218 x_lot_control_code OUT NOCOPY NUMBER ,
219 x_serial_control_code OUT NOCOPY NUMBER ,
220 x_split_uom_quantities OUT NOCOPY wms_Task_mgmt_pub.qty_changed_tbl_type ,
221 x_return_status OUT NOCOPY VARCHAR2 ,
222 x_msg_data OUT NOCOPY VARCHAR2 ,
223 x_msg_count OUT NOCOPY VARCHAR2 );
224
225 PROCEDURE split_mmtt( p_orig_transaction_temp_id IN NUMBER ,
226 p_new_transaction_temp_id IN NUMBER ,
227 p_new_transaction_header_id IN NUMBER ,
228 p_new_mol_id IN NUMBER ,
229 p_transaction_qty_to_split IN NUMBER ,
230 p_primary_qty_to_split IN NUMBER ,
231 x_return_status OUT NOCOPY VARCHAR2 ,
232 x_msg_data OUT NOCOPY VARCHAR2 ,
233 x_msg_count OUT NOCOPY VARCHAR2 );
234
235 PROCEDURE split_wdt( p_new_task_id IN NUMBER ,
236 p_new_transaction_temp_id IN NUMBER ,
237 p_new_mol_id IN NUMBER ,
238 p_orig_transaction_temp_id IN NUMBER ,
239 x_return_status OUT NOCOPY VARCHAR2 ,
240 x_msg_data OUT NOCOPY VARCHAR2 ,
241 x_msg_count OUT NOCOPY VARCHAR2 );
242
243 PROCEDURE split_lot_serial( p_orig_transaction_temp_id IN NUMBER ,
244 p_new_transaction_temp_id IN NUMBER ,
245 p_transaction_qty_to_split IN NUMBER ,
246 p_primary_qty_to_split IN NUMBER ,
247 p_inventory_item_id IN NUMBER ,
248 p_organization_id IN NUMBER ,
249 x_return_status OUT NOCOPY VARCHAR2 ,
250 x_msg_data OUT NOCOPY VARCHAR2 ,
251 x_msg_count OUT NOCOPY VARCHAR2 );
252
253 PROCEDURE split_serial( p_orig_transaction_temp_id IN NUMBER ,
254 p_new_transaction_temp_id IN NUMBER ,
255 p_transaction_qty_to_split IN NUMBER ,
256 p_primary_qty_to_split IN NUMBER ,
257 p_inventory_item_id IN NUMBER ,
258 p_organization_id IN NUMBER ,
259 x_return_status OUT NOCOPY VARCHAR2 ,
260 x_msg_data OUT NOCOPY VARCHAR2 ,
261 x_msg_count OUT NOCOPY VARCHAR2 );
262
263 PROCEDURE split_mtlt ( p_new_transaction_temp_id IN NUMBER ,
264 p_transaction_qty_to_split IN NUMBER ,
265 p_primary_qty_to_split IN NUMBER ,
266 p_row_id IN ROWID ,
267 x_return_status OUT NOCOPY VARCHAR2 ,
268 x_msg_data OUT NOCOPY VARCHAR2 ,
269 x_msg_count OUT NOCOPY VARCHAR2 );
270 --End Additions for Bug 6682436
271 --Begin of Bug: 7254397
272 TYPE numset_t IS TABLE OF NUMBER;
273 TYPE numset_tabType IS TABLE OF NUMBER
274 INDEX BY BINARY_INTEGER;
275 PROCEDURE insert_cartonization_id (
276 p_lpn_id IN NUMBER,
277 x_return_status OUT nocopy VARCHAR2,
278 x_msg_count OUT nocopy NUMBER,
279 x_msg_data OUT nocopy VARCHAR2);
280
281 FUNCTION list_cartonization_id RETURN numset_t PIPELINED;
282
283 PROCEDURE clear_cartonization_id(
284 x_return_status OUT nocopy VARCHAR2,
285 x_msg_count OUT nocopy NUMBER,
286 x_msg_data OUT nocopy VARCHAR2);
287 --End of Bug: 7254397
288 FUNCTION get_total_lpns RETURN NUMBER;
289
290 END wms_picking_pkg;
[Home] [Help]