1 PACKAGE wms_task_load AS
2 /* $Header: WMSLOADS.pls 120.4.12010000.2 2008/09/10 07:18:14 anviswan ship $ */
3
4 TYPE lpn_lot_qty_rec IS RECORD(
5 lpn_id NUMBER
6 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
7 , lot_number VARCHAR2(80)
8 , pri_qty NUMBER
9 , trx_qty NUMBER
10 , sec_qty NUMBER -- Bug #4141928
11 , non_alloc_qty NUMBER --jxlu 10/22/04 check serial ATT for allocated serial
12 );
13
14
15 TYPE lpn_lot_qty_tb IS TABLE OF lpn_lot_qty_rec
16 INDEX BY BINARY_INTEGER;
17
18 -- PL/SQL TABLE used to store lot_number and qty for passed in lpn_id
19
20 t_lpn_lot_qty_table lpn_lot_qty_tb;
21
22
23 PROCEDURE task_load(
24 p_action IN VARCHAR2
25 -- LOAD_SINGLE/LOAD_MULTIPLE/SPLIT/UPDATE_LOADED
26 , p_organization_id IN NUMBER
27 , p_user_id IN NUMBER
28 , p_person_id IN NUMBER
29 , p_transaction_header_id IN NUMBER
30 , p_temp_id IN NUMBER
31 , p_parent_line_id IN NUMBER -- For bulk parent
32 , p_lpn_id IN NUMBER
33 , p_content_lpn_id IN NUMBER
34 , p_transfer_lpn_id IN NUMBER
35 , p_confirmed_sub IN VARCHAR2
36 , p_confirmed_loc_id IN NUMBER
37 , p_confirmed_uom IN VARCHAR2
38 , p_suggested_uom IN VARCHAR2
39 , p_primary_uom IN VARCHAR2 -- 03/02/04
40 , p_item_id IN NUMBER
41 , p_revision IN VARCHAR2
42 , p_confirmed_qty IN NUMBER
43 , p_confirmed_lots IN VARCHAR2
44 , p_confirmed_lot_trx_qty IN VARCHAR2
45 , p_confirmed_sec_uom IN VARCHAR2
46 , p_confirmed_sec_qty IN VARCHAR2
47 , p_confirmed_serials IN VARCHAR2
48 , p_container_item_id IN NUMBER
49 , p_transaction_type_id IN NUMBER
50 , p_transaction_source_type_id IN NUMBER
51 , p_lpn_match IN NUMBER
52 , p_lpn_match_lpn_id IN NUMBER
53 , p_serial_allocated_flag IN VARCHAR2 -- Y/V or NULL
54 , p_lot_controlled IN VARCHAR2 -- Y/N
55 , p_serial_controlled IN VARCHAR2 -- Y/N
56 , p_effective_start_date IN DATE
57 , p_effective_end_date IN DATE
58 , p_exception IN VARCHAR2 -- SHORT, OVER
59 , p_discrepancies IN VARCHAR2
60 , p_qty_rsn_id IN NUMBER
61 , p_parent_lpn_id IN NUMBER
62 , p_lpnpickedasis IN VARCHAR2 -- Y/N
63 , x_new_transaction_temp_id OUT NOCOPY NUMBER
64 , x_cms_check OUT NOCOPY VARCHAR2
65 , x_return_status OUT NOCOPY VARCHAR2
66 , x_msg_count OUT NOCOPY NUMBER
67 , x_msg_data OUT NOCOPY VARCHAR2);
68
69 PROCEDURE task_merge_split(
70 p_action IN VARCHAR2 -- LOAD_MULTIPLE/LOAD_SINGLE/SPLIT
71 ,p_exception IN VARCHAR2 -- SHORT/OVER
72 ,p_organization_id IN NUMBER
73 ,p_user_id IN NUMBER
74 ,p_transaction_header_id IN NUMBER
75 ,p_transaction_temp_id IN NUMBER
76 ,p_parent_line_id IN NUMBER
77 ,p_remaining_temp_id IN NUMBER
78 ,p_lpn_id IN NUMBER
79 ,p_content_lpn_id IN NUMBER
80 ,p_transfer_lpn_id IN NUMBER
81 ,p_confirmed_sub IN VARCHAR2
82 ,p_confirmed_locator_id IN NUMBER
83 ,p_confirmed_uom IN VARCHAR2
84 ,p_suggested_uom IN VARCHAR2
85 ,p_primary_uom IN VARCHAR2 -- 03/02/04
86 ,p_inventory_item_id IN NUMBER
87 ,p_revision IN VARCHAR2
88 ,p_confirmed_trx_qty IN NUMBER
89 ,p_confirmed_lots IN VARCHAR2
90 ,p_confirmed_lot_trx_qty IN VARCHAR2
91 ,p_confirmed_sec_uom IN VARCHAR2
92 ,p_confirmed_sec_qty IN VARCHAR2
93 ,p_confirmed_serials IN VARCHAR2
94 ,p_container_item_id IN NUMBER
95 ,p_lpn_match IN NUMBER
96 ,p_lpn_match_lpn_id IN NUMBER
97 ,p_serial_allocated_flag IN VARCHAR2
98 ,p_lot_controlled IN VARCHAR2 -- Y/N
99 ,p_serial_controlled IN VARCHAR2 -- Y/N
100 ,p_parent_lpn_id IN NUMBER
101 ,x_new_transaction_temp_id OUT NOCOPY NUMBER
102 ,x_cms_check OUT NOCOPY VARCHAR2
103 ,x_return_status OUT NOCOPY VARCHAR2
104 ,x_msg_count OUT NOCOPY NUMBER
105 ,x_msg_data OUT NOCOPY VARCHAR2);
106
107 PROCEDURE process_F2(
108 p_action IN VARCHAR2 -- NULL/CMS
109 ,p_organization_id IN NUMBER
110 ,p_user_id IN NUMBER
111 ,p_employee_id IN NUMBER
112 ,p_transaction_header_id IN NUMBER
113 ,p_transaction_temp_id IN NUMBER
114 ,p_original_sub IN VARCHAR2
115 ,p_original_locator_id IN NUMBER
116 ,p_lot_controlled IN VARCHAR2 -- Y/N
117 ,p_serial_controlled IN VARCHAR2 -- Y/N
118 ,p_serial_allocated_flag IN VARCHAR2 -- Y/N
119 ,p_suggested_uom IN VARCHAR2 -- original allocation UOM -- 03/02/04
120 ,p_start_over IN VARCHAR2 -- Y/Nstart_over button
121 ,p_retain_task IN VARCHAR2 -- Y/N for bug 4310093
122 ,x_start_over_taskno OUT NOCOPY NUMBER -- start_over
123 ,x_return_status OUT NOCOPY VARCHAR2
124 ,x_msg_count OUT NOCOPY NUMBER
125 ,x_msg_data OUT NOCOPY VARCHAR2) ;
126
127 -- This lpn will be used during the picking process. If the user specifies
128 -- a from lpn, this procedure will figure out if the lpn in question will
129 -- satisfy the pick in question
130 -- It will return 1 if this is the case, 0 if not and 2 if the item does
131 -- not exist in the lpn, 3 if the qty is not adequate and 4 if it already
132 -- has been loaded
133
134 PROCEDURE lpn_match(
135 p_fromlpn_id IN NUMBER
136 , p_org_id IN NUMBER
137 , p_item_id IN NUMBER
138 , p_rev IN VARCHAR2
139 , p_lot IN VARCHAR2
140 , p_trx_qty IN NUMBER
141 , p_trx_uom IN VARCHAR2
142 , p_sec_qty IN NUMBER -- Bug #4141928
143 , p_sec_uom IN VARCHAR2 -- Bug #4141928
144 , x_match OUT NOCOPY NUMBER
145 , x_sub OUT NOCOPY VARCHAR2
146 , x_loc OUT NOCOPY VARCHAR2
147 , x_trx_qty OUT NOCOPY NUMBER
148 , x_trx_sec_qty OUT NOCOPY NUMBER -- Bug #4141928
149 , x_return_status OUT NOCOPY VARCHAR2
150 , x_msg_count OUT NOCOPY NUMBER
151 , x_msg_data OUT NOCOPY VARCHAR2
152 , p_temp_id IN NUMBER
153 , p_parent_line_id IN NUMBER
154 , p_wms_installed IN VARCHAR2
155 , p_transaction_type_id IN NUMBER
156 , p_cost_group_id IN NUMBER
157 , p_is_sn_alloc IN VARCHAR2
158 , p_action IN NUMBER
159 , p_split IN VARCHAR2
160 , p_user_id IN NUMBER
161 , x_temp_id OUT NOCOPY NUMBER
162 , x_loc_id OUT NOCOPY NUMBER
163 , x_lpn_lot_vector OUT NOCOPY VARCHAR2
164 , x_cms_check OUT NOCOPY VARCHAR2
165 , x_parent_lpn_id OUT NOCOPY VARCHAR2
166 , x_trx_qty_alloc OUT NOCOPY NUMBER --jxlu 10/12/04
167 , p_transaction_action_id IN NUMBER --jxlu
168 , p_pickOverNoException IN VARCHAR2
169 , p_toLPN_Default IN VARCHAR2 -- Bug 3855835
170 , p_project_id IN NUMBER
171 , p_task_id IN NUMBER
172 , p_confirmed_sub IN VARCHAR2
173 , p_confirmed_loc_id IN NUMBER
174 , p_from_lpn_id IN NUMBER
175 , x_toLPN_status OUT NOCOPY VARCHAR2 --Bug 3855835
176 , x_lpnpickedasis OUT NOCOPY VARCHAR2
177 , x_lpn_qoh OUT NOCOPY NUMBER
178 );
179
180 -- during the picking process. If the user does not specifies
181 -- a from lpn, this procedure will figure out if the loose quantity will
182 -- satisfy the pick in question, the temp table mtl_allocations_gtmp
183 -- will store the available lot and serial numbers for this pick
184
185 PROCEDURE loose_match(
186 p_org_id IN NUMBER
187 , p_item_id IN NUMBER
188 , p_rev IN VARCHAR2
189 , p_trx_qty IN NUMBER
190 , p_trx_uom IN VARCHAR2
191 , p_pri_uom IN VARCHAR2
192 , p_sec_uom IN VARCHAR2 -- Bug #4141928
193 , p_sec_qty IN NUMBER -- Bug #4141928
194 , p_temp_id IN NUMBER
195 , p_suggested_locator IN NUMBER
196 , p_confirmed_locator IN NUMBER
197 , p_confirmed_sub IN VARCHAR2
198 , p_is_sn_alloc IN VARCHAR2
199 , p_is_revision_control IN VARCHAR2
200 , p_is_lot_control IN VARCHAR2
201 , p_is_serial_control IN VARCHAR2
202 , p_is_negbal_allowed IN VARCHAR2 --vikas 09/07/04 v1
203 , p_toLPN_Default IN VARCHAR2 -- Bug 3855835
204 , p_project_id IN NUMBER
205 , p_task_id IN NUMBER
206 , x_trx_qty OUT NOCOPY NUMBER
207 , x_trx_sec_qty OUT NOCOPY NUMBER -- Bug #4141928
208 , x_return_status OUT NOCOPY VARCHAR2
209 , x_msg_count OUT NOCOPY NUMBER
210 , x_msg_data OUT NOCOPY VARCHAR2
211 , x_toLPN_status OUT NOCOPY VARCHAR2 --Bug 3855835
212 , x_lot_att_vector OUT NOCOPY VARCHAR2
213 , x_trx_qty_alloc OUT NOCOPY NUMBER -- jxlu 10/6/04
214 , p_transaction_type_id IN NUMBER -- Bug 4632519
215 , p_transaction_action_id IN NUMBER -- Bug 4632519
216 );
217
218
219 FUNCTION can_pickdrop(p_transaction_temp_id IN NUMBER)
220 RETURN VARCHAR2;
221
222
223 /* This API will return the number of tasks that still need to be performed
224 for a given carton. If it returns more than 1, the user should not be
225 allowed to drop off the carton*/
226 PROCEDURE check_pack_lpn(
227 p_lpn IN VARCHAR2
228 , p_org_id IN NUMBER
229 , p_container_item_id IN NUMBER
230 , p_temp_id IN NUMBER --Bug7120019
231 , x_lpn_id OUT NOCOPY NUMBER
232 , x_lpn_context OUT NOCOPY NUMBER
233 , x_outermost_lpn_id OUT NOCOPY NUMBER
234 , x_pick_to_lpn_exists OUT NOCOPY BOOLEAN
235 , x_return_status OUT NOCOPY VARCHAR2
236 , x_msg_count OUT NOCOPY NUMBER
237 , x_msg_data OUT NOCOPY VARCHAR2
238 );
239
240 PROCEDURE validate_pick_to_lpn(
241 p_api_version_number IN NUMBER
242 , p_init_msg_lst IN VARCHAR2 := fnd_api.g_false
243 , x_return_status OUT NOCOPY VARCHAR2
244 , x_msg_count OUT NOCOPY NUMBER
245 , x_msg_data OUT NOCOPY VARCHAR2
246 , p_organization_id IN NUMBER
247 , p_pick_to_lpn IN VARCHAR2
248 , p_temp_id IN NUMBER
249 , p_project_id IN NUMBER := NULL
250 , p_task_id IN NUMBER := NULL
251 , p_container_item IN VARCHAR2
252 , p_container_item_id IN NUMBER
253 , p_suggested_container_item IN VARCHAR2
254 , p_suggested_container_item_id IN NUMBER
255 , p_suggested_carton_name IN VARCHAR2
256 , p_suggested_tolpn_id IN NUMBER
257 , x_pick_to_lpn_id OUT NOCOPY NUMBER
258 , p_inventory_item_id IN NUMBER
259 , p_confirmed_sub IN VARCHAR2
260 , p_confirmed_loc_id IN NUMBER
261 , p_revision IN VARCHAR2
262 , p_confirmed_lots IN VARCHAR2
263 , p_from_lpn_id IN NUMBER
264 , p_lot_control IN VARCHAR2
265 , p_revision_control IN VARCHAR2
266 , p_serial_control IN VARCHAR2
267 , p_trx_type_id IN VARCHAR2 -- Bug 4632519
268 , p_trx_action_id IN VARCHAR2 -- Bug 4632519
269 );
270
271 PROCEDURE validate_sub_loc_status(
272 p_wms_installed IN VARCHAR2
273 , p_temp_id IN NUMBER
274 , p_confirmed_sub IN VARCHAR2
275 , p_confirmed_loc_id IN NUMBER
276 , x_return_status OUT NOCOPY VARCHAR2
277 , x_msg_count OUT NOCOPY NUMBER
278 , x_msg_data OUT NOCOPY VARCHAR2
279 , x_result OUT NOCOPY NUMBER
280 );
281
282
283 PROCEDURE insert_serial(
284 p_serial_transaction_temp_id IN OUT NOCOPY NUMBER,
285 p_organization_id IN NUMBER,
286 p_item_id IN NUMBER,
287 p_revision IN VARCHAR2,
288 p_lot IN VARCHAR2,
289 p_transaction_temp_id IN NUMBER,
290 p_created_by IN NUMBER,
291 p_from_serial IN VARCHAR2,
292 p_to_serial IN VARCHAR2,
293 p_status_id IN NUMBER := NULL,
294 x_return_status OUT NOCOPY VARCHAR2,
295 x_msg_data OUT NOCOPY VARCHAR2
296 ) ;
297
298 END wms_task_load;
[Home] [Help]