1 package body wms_atf_dest_loc as
2 /* $Header: WMSADLOB.pls 115.47 2004/05/07 21:24:39 joabraha noship $ */
3 --
4 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
5 l_pkg varchar2(72) := 'WMS_ATF_DEST_LOC :';
6
7 --
8 -- ---------------------------------------------------------------------------------------
9 -- |---------------------< trace >--------------------------------------------------------|
10 -- ---------------------------------------------------------------------------------------
11 -- {Start Of Comments}
12 --
13 -- Description:
14 -- Wrapper around the tracing utility.
15 --
16 -- Prerequisites:
17 -- None
18 --
19 -- In Parameters:
20 -- Name Reqd Type Description
21 -- p_message Yes varchar2 Message to be displayed in the log file.
22 -- p_prompt Yes varchar2 Prompt.
23 -- p_level No number Level.
24 --
25 -- Post Success:
26 -- None.
27 --
28 -- Post Failure:
29 -- None
30 --
31 -- Access Status:
32 -- Internal Development Use Only.
33 --
34 -- {End Of Comments}
35 --
36
37 Procedure trace(
38 p_message in varchar2
39 , p_level in number
40 ) is
41 begin
42 INV_LOG_UTIL.trace(p_message, 'WMS_ATF_DEST_LOC', p_level);
43 end trace;
44 --
45 --
46 -- ---------------------------------------------------------------------------------------
47 -- |---------------------< exit_proc_msg >--------------------------------------------------------|
48 -- ---------------------------------------------------------------------------------------
49 -- {Start Of Comments}
50 --
51 -- Description:
52 -- Wrapper around the tracing utility.
53 --
54 -- Prerequisites:
55 -- None
56 --
57 -- In Parameters:
58 -- Name Reqd Type Description
59 -- p_message Yes varchar2 Message to be displayed in the log file.
60 -- p_prompt Yes varchar2 Prompt.
61 -- p_level No number Level.
62 --
63 -- Post Success:
64 -- None.
65 --
66 -- Post Failure:
67 -- None
68 --
69 -- Access Status:
70 -- Internal Development Use Only.
71 --
72 -- {End Of Comments}
73
74 Procedure exit_proc_msg(
75 x_return_status in varchar2
76 , x_msg_count in number
77 , x_msg_data in varchar2
78 , x_locator_id in number
79 , x_zone_id in number
80 , x_subinventory_code in varchar2
81 , x_loc_valid in varchar2
82 , l_proc in varchar2
83 ) is
84 begin
85 if (l_debug = 1) then
86 trace(' Exiting Procedure '|| l_proc || ':'|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
87 trace(l_proc || ' x_return_status => ' || x_return_status);
88 trace(l_proc || ' x_msg_count => ' || x_msg_count);
89 trace(l_proc || ' x_msg_data => ' || x_msg_data);
90 trace(l_proc || ' x_locator_id => ' || x_locator_id);
91 trace(l_proc || ' x_zone_id => ' || x_zone_id);
92 trace(l_proc || ' x_subinventory_code => ' || x_subinventory_code);
93 trace(l_proc || ' x_loc_valid => ' || x_loc_valid);
94 end if;
95 end exit_proc_msg;
96 --
97 -- ---------------------------------------------------------------------------------------
98 -- |----------------------------< Get_Destination_Loc >-----------------------------------|
99 -- ---------------------------------------------------------------------------------------
100 -- {Start Of Comments}
101 --
102 -- Description:
103 -- Returns locator based on the specific conditions.
104 --
105 -- Package-Procedure combination
106 --
107 -- Prerequisites:
108 --
109 --
110 --
111 -- In Parameters:
112 -- Name Reqd Type Description
113 -- x_locator_id Yes varchar2 Short name for parent Module/Business
114 -- x_zone_id Yes varchar2 Call package to be registered -- x_subinventory_code Yes varchar2 Call procedure to be registered
115 -- p_mode Yes varchar2 Effective To Date.
116 -- p_activity_type Yes varchar2 Valid Modes are Insert, Update and
117 -- p_task_id Yes varchar2 Indicates if this is a seeded or
118 -- p_locator_id Yes
119 -- p_item_id Yes
120 --
121 -- Post Success:
122 --
123 --
124 -- Post Failure:
125 -- Details of the error are added to the AOL message stack. When this
126 --
127 -- Access Status:
128 -- Internal Development Use Only.
129 --
130 -- {End Of Comments}
131 --
132 --
133 -- We need 2 wrappers :
134 -- One with Inventory item_id passed in
135 -- One won't pass the p_item_id.
136 Procedure get_seed_dest_loc (
137 x_return_status out nocopy varchar2
138 , x_msg_count out nocopy number
139 , x_msg_data out nocopy varchar2
140 , x_locator_id out nocopy number
141 , x_zone_id out nocopy number
142 , x_subinventory_code out nocopy varchar2
143 , x_loc_valid out nocopy varchar2
144 , p_mode in number
145 , p_task_id in number
146 , p_activity_type_id in number
147 , p_locator_id in number
148 , p_item_id in number
149 , p_api_version in number
150 , p_init_msg_list in varchar2
151 , p_commit in varchar2
152 ) is
153
154 l_proc varchar2(72) := 'GET_SEED_DEST_LOC :';
155 l_prog float := null;
156
157 l_operation_plan_id number := null;
158 l_operation_plan_dtl_id number := null;
159 l_plan_type_id number := null;
160 l_activity_type_id number := null;
161 l_pre_specified_zone_id number := null;
162 l_pre_specified_sub_code varchar2(100) := null;
163 l_loc_mtrl_grp_rule_id number := null;
164 l_operation_type number := null;
165
166 l_lpn_id number := null;
167 l_subinventory_code varchar2(100) := null;
168 l_locator_id number := null;
169
170 l_is_in_inventory varchar2(1) := null;
171 l_inventory_location_id number := null;
172 l_subinventory_type varchar2(100) := null;
173
174 l_orig_dest_sub_code varchar2(100) := null;
175 l_orig_dest_loc_id number := null;
176
177 l_organization_id number := null;
178 l_zone_id number := null;
179
180
181 l_active_cur_found boolean := false;
182 l_cursor varchar2(50):= null;
183 l_cur_found boolean := false;
184
185 l_sysdate varchar2(100);
186 l_sys_date date;
187
188 cursor c_sysdate is
189 select to_char(sysdate, 'RRRR/MM/DD HH24:MI:SS')
190 from dual;
191
192 cursor c_oper_plan_details is
193 select mmtt.operation_plan_id,
194 mmtt.locator_id,
195 mmtt.subinventory_code,
196 wopd.operation_plan_detail_id,
197 wopi.activity_type_id,
198 wopi.plan_type_id,
199 wopi.orig_dest_sub_code,
200 wopi.orig_dest_loc_id,
201 wopd.pre_specified_zone_id,
202 wopd.pre_specified_sub_code,
203 nvl(wopd.loc_mtrl_grp_rule_id, -99),
204 wopd.operation_type,
205 nvl(wopd.is_in_inventory, 'N'),
206 mmtt.organization_id,
207 mmtt.lpn_id
208 from wms_op_plan_instances wopi, -- after review on 07/30/03, replaced wms_op_plans_b table with wms_op_plan_instances.
209 wms_op_plan_details wopd,
210 -- wms_zones_b wzb, -- Removed after Code Review on Sept 11th 2003.
211 mtl_material_transactions_temp mmtt,
212 wms_op_operation_instances wooi -- Added after review on 07/30/03
213 where mmtt.organization_id = nvl(wopi.organization_id, mmtt.organization_id)
214 and wopd.operation_plan_detail_id = wooi.operation_plan_detail_id -- Added after review on 07/30/03
215 and wopi.op_plan_instance_id = wooi.op_plan_instance_id
216 and wopi.operation_plan_id = mmtt.operation_plan_id
217 and wooi.source_task_id = mmtt.transaction_temp_id -- Added after review on 07/30/03
218 and mmtt.transaction_temp_id = p_task_id -- 6583491 (dmfdv11i)
219 --
220 -- @@@ Commented after for bug fix on Sept 16th 2003. One task is tied to a combination of a Load and a Drop. This means
221 -- @@@ when you query the wooi with the restriction " wooi.source_task_id = mmtt.transaction_temp_id ", it'll bring back
222 -- @@@ 2 records, 1 each for a load and drop. We are only interested in the Drop. This is all the more important becase
223 -- @@@ athis API will abort if the Material Grouping Rule ID stamped on the detail line is null. There will exist no
224 -- @@@ Material Grouping Rule ID for the Load Operation Plan Detail line. We also know that the operation sequence for the
225 -- @@@ Drop Line is always greater than the Load line and hence the " order by wooi.operation_sequence desc" will bring
226 -- @@@ back the Drop line first and then the Load line. In any case we only consider the first recoerd and in this case now
227 -- @@@ it turns out to be the Drop line.
228 -- order by wopd.operation_type;
229 order by wooi.operation_sequence desc;
230
231
232 -- Added following two (c_subloc_pend_wo_zone, c_subloc_pend_w_zone) cursors for bug 3393371 - lezhang
233 -- When there are two mmtts in the same LPN, Activate_Operation_Instance for both MMTTs (as well as get_seed_dest_loc)
234 -- will be called before each of them gets completed. The destination suggestion for the second line should consider
235 -- the first line also.
236 -- The first MMTT will have an operation instance WOOI that is drop active.
237
238 cursor c_subloc_pend_wozone_woitem is
239 select milk.subinventory_code, nvl(milk.physical_location_id, milk.inventory_location_id)
240 from mtl_item_locations milk,
241 mtl_material_transactions_temp mmtt,
242 wms_op_operation_instances wooi,
243 wms_op_plan_instances wopi,
244 wms_license_plate_numbers wlpn1,
245 wms_license_plate_numbers wlpn2
246 where wopi.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopi.plan_type_id)
247 and wopi.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopi.orig_dest_sub_code )
248 and wopi.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopi.orig_dest_loc_id)
249 and wooi.op_plan_instance_id = wopi.op_plan_instance_id
250 and wooi.operation_type_id = 2 -- Drop operation
251 and wooi.operation_status = 2 -- Active
252 and wooi.source_task_id = mmtt.transaction_temp_id
253 and mmtt.organization_id = milk.organization_id
254 and mmtt.lpn_id = wlpn1.lpn_id
255 and wlpn2.lpn_id = l_lpn_id
256 and wlpn1.outermost_lpn_id = wlpn2.outermost_lpn_id
257 and milk.subinventory_code = l_pre_specified_sub_code
258 and nvl(mmtt.transfer_to_location, mmtt.locator_id) = milk.inventory_location_id
259 order by wooi.last_update_date desc;
260 --
261 --
262 cursor c_subloc_pend_wzn_wosub_woitem is
263 select milk.subinventory_code, nvl(milk.physical_location_id, milk.inventory_location_id)
264 from mtl_item_locations milk,
265 wms_zone_locators wzl,
266 mtl_material_transactions_temp mmtt,
267 wms_op_operation_instances wooi,
268 wms_op_plan_instances wopi,
269 wms_license_plate_numbers wlpn1,
270 wms_license_plate_numbers wlpn2
271 where wopi.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopi.plan_type_id)
272 and wopi.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopi.orig_dest_sub_code )
273 and wopi.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopi.orig_dest_loc_id)
274 and wooi.op_plan_instance_id = wopi.op_plan_instance_id
275 and wooi.operation_type_id = 2 -- Drop operation
276 and wooi.operation_status = 2 -- Active
277 and wooi.source_task_id = mmtt.transaction_temp_id
278 and mmtt.organization_id = milk.organization_id
279 and mmtt.lpn_id = wlpn1.lpn_id
280 and wlpn2.lpn_id = l_lpn_id
281 and wlpn1.outermost_lpn_id = wlpn2.outermost_lpn_id
282 and nvl(mmtt.transfer_to_location, mmtt.locator_id) = milk.inventory_location_id
283 and wzl.zone_id = l_pre_specified_zone_id
284 and wzl.subinventory_code = milk.subinventory_code
285 and (wzl.entire_sub_flag = 'Y'
286 or wzl.inventory_location_id = nvl(milk.physical_location_id, milk.inventory_location_id))
287 order by wooi.last_update_date desc;
288 --
289 --
290 cursor c_subloc_pend_wzn_wsub_woitem is
291 select milk.subinventory_code, nvl(milk.physical_location_id, milk.inventory_location_id)
292 from mtl_item_locations milk,
293 wms_zone_locators wzl,
294 mtl_material_transactions_temp mmtt,
295 wms_op_operation_instances wooi,
296 wms_op_plan_instances wopi,
297 wms_license_plate_numbers wlpn1,
298 wms_license_plate_numbers wlpn2
299 where wopi.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopi.plan_type_id)
303 and wooi.operation_type_id = 2 -- Drop operation
300 and wopi.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopi.orig_dest_sub_code )
301 and wopi.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopi.orig_dest_loc_id)
302 and wooi.op_plan_instance_id = wopi.op_plan_instance_id
304 and wooi.operation_status = 2 -- Active
305 and wooi.source_task_id = mmtt.transaction_temp_id
306 and mmtt.organization_id = milk.organization_id
307 and mmtt.lpn_id = wlpn1.lpn_id
308 and wlpn2.lpn_id = l_lpn_id
309 and wlpn1.outermost_lpn_id = wlpn2.outermost_lpn_id
310 and nvl(mmtt.transfer_to_location, mmtt.locator_id) = milk.inventory_location_id
311 and wzl.zone_id = l_pre_specified_zone_id
312 and wzl.subinventory_code = milk.subinventory_code
313 and milk.subinventory_code = l_pre_specified_sub_code
314 and (wzl.entire_sub_flag = 'Y'
315 or wzl.inventory_location_id = Nvl(milk.physical_location_id, milk.inventory_location_id))
316 order by wooi.last_update_date desc;
317 --
318 --
319
320 cursor c_subloc_pend_wozone_witem is
321 select milk.subinventory_code, nvl(milk.physical_location_id, milk.inventory_location_id)
322 from mtl_item_locations milk,
323 mtl_material_transactions_temp mmtt,
324 wms_op_operation_instances wooi,
325 wms_op_plan_instances wopi,
326 wms_license_plate_numbers wlpn1,
327 wms_license_plate_numbers wlpn2
328 where wopi.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopi.plan_type_id)
329 and wopi.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopi.orig_dest_sub_code )
330 and wopi.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopi.orig_dest_loc_id)
331 and wooi.op_plan_instance_id = wopi.op_plan_instance_id
332 and wooi.operation_type_id = 2 -- Drop operation
333 and wooi.operation_status = 2 -- Active
334 and wooi.source_task_id = mmtt.transaction_temp_id
335 and mmtt.organization_id = milk.organization_id
336 and mmtt.lpn_id = wlpn1.lpn_id
337 and mmtt.inventory_item_id = p_item_id
338 and wlpn2.lpn_id = l_lpn_id
339 and wlpn1.outermost_lpn_id = wlpn2.outermost_lpn_id
340 and milk.subinventory_code = l_pre_specified_sub_code
341 and nvl(mmtt.transfer_to_location, mmtt.locator_id) = milk.inventory_location_id
342 order by wooi.last_update_date desc;
343 --
344 --
345 cursor c_subloc_pend_wzn_wosub_witem is
346 select milk.subinventory_code, nvl(milk.physical_location_id, milk.inventory_location_id)
347 from mtl_item_locations milk,
348 wms_zone_locators wzl,
349 mtl_material_transactions_temp mmtt,
350 wms_op_operation_instances wooi,
351 wms_op_plan_instances wopi,
352 wms_license_plate_numbers wlpn1,
353 wms_license_plate_numbers wlpn2
354 where wopi.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopi.plan_type_id)
355 and wopi.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopi.orig_dest_sub_code )
356 and wopi.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopi.orig_dest_loc_id)
357 and wooi.op_plan_instance_id = wopi.op_plan_instance_id
358 and wooi.operation_type_id = 2 -- Drop operation
359 and wooi.operation_status = 2 -- Active
360 and wooi.source_task_id = mmtt.transaction_temp_id
361 and mmtt.organization_id = milk.organization_id
362 and mmtt.lpn_id = wlpn1.lpn_id
363 and mmtt.inventory_item_id = p_item_id
364 and wlpn2.lpn_id = l_lpn_id
365 and wlpn1.outermost_lpn_id = wlpn2.outermost_lpn_id
366 and nvl(mmtt.transfer_to_location, mmtt.locator_id) = milk.inventory_location_id
367 and wzl.zone_id = l_pre_specified_zone_id
368 and wzl.subinventory_code = milk.subinventory_code
369 and (wzl.entire_sub_flag = 'Y'
370 or wzl.inventory_location_id = nvl(milk.physical_location_id, milk.inventory_location_id))
371 order by wooi.last_update_date desc;
372 --
373 --
374 cursor c_subloc_pend_wzn_wsub_witem is
375 select milk.subinventory_code, nvl(milk.physical_location_id, milk.inventory_location_id)
376 from mtl_item_locations milk,
377 wms_zone_locators wzl,
378 mtl_material_transactions_temp mmtt,
379 wms_op_operation_instances wooi,
380 wms_op_plan_instances wopi,
381 wms_license_plate_numbers wlpn1,
382 wms_license_plate_numbers wlpn2
383 where wopi.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopi.plan_type_id)
384 and wopi.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopi.orig_dest_sub_code )
385 and wopi.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopi.orig_dest_loc_id)
386 and wooi.op_plan_instance_id = wopi.op_plan_instance_id
387 and wooi.operation_type_id = 2 -- Drop operation
388 and wooi.operation_status = 2 -- Active
389 and wooi.source_task_id = mmtt.transaction_temp_id
390 and mmtt.organization_id = milk.organization_id
391 and mmtt.lpn_id = wlpn1.lpn_id
392 and mmtt.inventory_item_id = p_item_id
393 and wlpn2.lpn_id = l_lpn_id
394 and wlpn1.outermost_lpn_id = wlpn2.outermost_lpn_id
395 and nvl(mmtt.transfer_to_location, mmtt.locator_id) = milk.inventory_location_id
396 and wzl.zone_id = l_pre_specified_zone_id
397 and wzl.subinventory_code = milk.subinventory_code
398 and milk.subinventory_code = l_pre_specified_sub_code
399 and (wzl.entire_sub_flag = 'Y'
400 or wzl.inventory_location_id = Nvl(milk.physical_location_id, milk.inventory_location_id))
401 order by wooi.last_update_date desc;
402
403
404 cursor c_act_wzone_wsub_witem is
405 select wooi.from_subinventory_code, milk2.inventory_location_id, wzl.zone_id
406 from mtl_item_locations milk1,
407 mtl_item_locations milk2,
408 mtl_secondary_inventories msi,
409 wms_zone_locators wzl,
410 -- ***** tables from the inner cursor *****
414 wms_op_plan_instances wopi
411 mtl_material_transactions_temp mmtt,
412 wms_op_operation_instances wooi,
413 wms_op_operation_instances wooi2,
415 where milk2.subinventory_code = msi.secondary_inventory_name
416 and milk2.organization_id = msi.organization_id
417 and ((milk2.disable_date is null and msi.disable_date is null)
418 or (not ( milk2.disable_date < l_sys_date
419 or msi.disable_date < l_sys_date))
420 or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
421 and msi.subinventory_type = 2
422 and (milk2.disable_date is null or milk2.disable_date >= l_sys_date)))
423 and wzl.zone_id = l_pre_specified_zone_id
424 and wzl.subinventory_code = wooi.from_subinventory_code
425 and (wzl.entire_sub_flag = 'Y' or wzl.inventory_location_id = wooi.from_locator_id)
426 and wzl.organization_id = l_organization_id
427 and nvl(msi.subinventory_type, 1) = decode(l_is_in_inventory, 'Y', 1, 2) -- 1:storage, 2:receiving
428 and wooi.from_subinventory_code = msi.secondary_inventory_name
429 and msi.secondary_inventory_name = l_pre_specified_sub_code
430 and msi.organization_id = l_organization_id
431 and milk2.segment19 is null
432 and milk2.segment20 is null
433 and milk2.inventory_location_id = nvl(milk1.physical_location_id, milk1.inventory_location_id)
434 and milk1.organization_id = milk2.organization_id
435 and milk1.inventory_location_id = wooi.from_locator_id
436 and milk1.organization_id = wooi.organization_id
437 and wooi.from_locator_id = decode(p_mode, 1, wooi.from_locator_id, 2, p_locator_id)
438 and wooi.organization_id = mmtt.organization_id
439 -- *****from the inner cursor****
440 and wopi.organization_id = mmtt.organization_id
441 and wopi.status = 6 -- (Operation Plan Status : Active)
442 and wopi.op_plan_instance_id = wooi.op_plan_instance_id
443 and wooi2.operation_status = 3 -- Completed
444 and wooi2.operation_type_id = 2 -- Drop
445 and wooi2.op_plan_instance_id = wopi.op_plan_instance_id
446 and wooi.source_task_id = mmtt.transaction_temp_id
447 and mmtt.organization_id = l_organization_id
448 and (wooi.operation_status = 1 and wooi.operation_type_id <> 2)
449 and mmtt.transaction_temp_id <> p_task_id
450 and mmtt.inventory_item_id = p_item_id
451 and wopi.activity_type_id = l_activity_type_id
452 and wooi.activity_type_id = l_activity_type_id
453 and wopi.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopi.plan_type_id)
454 and wopi.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopi.orig_dest_sub_code )
455 and wopi.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopi.orig_dest_loc_id)
456 order by mmtt.creation_date desc;
457 --
458 --
459 cursor c_comp_wzone_wsub_witem is
460 select wooih.to_subinventory_code, milk2.inventory_location_id, wzl.zone_id
461 from wms_op_plan_instances_hist wopih,
462 wms_op_opertn_instances_hist wooih,
463 mtl_secondary_inventories msi,
464 wms_zone_locators wzl,
465 wms_license_plate_numbers wlpn,
466 wms_dispatched_tasks_history wdth,
467 mtl_item_locations milk1,
468 mtl_item_locations milk2
469 where milk2.subinventory_code = msi.secondary_inventory_name
470 and milk2.organization_id = msi.organization_id
471 and ((milk2.disable_date is null and msi.disable_date is null)
472 or (not ( milk2.disable_date < l_sys_date
473 or msi.disable_date < l_sys_date))
474 or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
475 and msi.subinventory_type = 2
476 and (milk2.disable_date is null or milk2.disable_date >= l_sys_date)))
477 and milk2.segment19 is null
478 and milk2.segment20 is null
479 and milk2.inventory_location_id = nvl(milk1.physical_location_id, milk1.inventory_location_id)
480 and milk2.organization_id = l_organization_id -- newly modified
481 and milk1.organization_id = milk2.organization_id
482 and milk1.inventory_location_id = wooih.to_locator_id -- newly modified
483 and milk1.organization_id = l_organization_id -- newly modified
484 and wzl.zone_id = l_pre_specified_zone_id
485 and wzl.subinventory_code = wooih.to_subinventory_code -- newly modified
486 and wzl.subinventory_code = l_pre_specified_sub_code
487 and wzl.subinventory_code = msi.secondary_inventory_name -- new
488 and (wzl.entire_sub_flag = 'Y' or wzl.inventory_location_id = wooih.to_locator_id)
489 and wzl.organization_id = l_organization_id
490 and wlpn.subinventory_code = msi.secondary_inventory_name
491 and wooih.to_subinventory_code = l_pre_specified_sub_code
492 and wooih.to_subinventory_code = msi.secondary_inventory_name
493 and msi.secondary_inventory_name = l_pre_specified_sub_code -- new
494 and msi.organization_id = l_organization_id --,newly modified
495 and wooih.to_locator_id = decode(p_mode, 1, wooih.to_locator_id, 2, p_locator_id)
496 and wlpn.locator_id = decode(p_mode, 1, wooih.to_locator_id, 2, p_locator_id)
497 and wlpn.subinventory_code = wooih.to_subinventory_code
498 and wlpn.subinventory_code = l_pre_specified_sub_code
499 and wlpn.organization_id = l_organization_id
500 and wlpn.lpn_id = nvl(wdth.transfer_lpn_id, wdth.content_lpn_id)
501 and wdth.status = 6 -- Completed. lookup_type is WMS_TASK_STATUS
502 and wdth.transaction_id = wooih.source_task_id
503 and wdth.inventory_item_id = p_item_id
504 and wdth.organization_id = l_organization_id -- new
505 and wooih.operation_sequence in (select max(operation_sequence)
506 from wms_op_opertn_instances_hist wooih2
507 where wooih2.op_plan_instance_id = wopih.op_plan_instance_id
508 and wooih2.operation_type_id in (2,9)
509 and wooih2.operation_status = 3)
510 and wooih.op_plan_instance_id = wopih.op_plan_instance_id
511 and wopih.status = 3 -- Plan Completed
515 --and wopih.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopih.orig_dest_sub_code )
512 and wopih.activity_type_id = l_activity_type_id
513 and wopih.organization_id = l_organization_id -- new
514 and wopih.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopih.plan_type_id)
516 and wopih.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopih.orig_dest_loc_id)
517 order by wooih.last_update_date desc;
518 --
519 --
520 cursor c_comp_wzonesubitem_destsub is
521 select wooih.to_subinventory_code, milk2.inventory_location_id, wzl.zone_id
522 from wms_op_plan_instances_hist wopih,
523 wms_op_opertn_instances_hist wooih,
524 mtl_secondary_inventories msi,
525 wms_zone_locators wzl,
526 wms_license_plate_numbers wlpn,
527 wms_dispatched_tasks_history wdth,
528 mtl_item_locations milk1,
529 mtl_item_locations milk2
530 where milk2.subinventory_code = msi.secondary_inventory_name
531 and milk2.organization_id = msi.organization_id
532 and ((milk2.disable_date is null and msi.disable_date is null)
533 or (not ( milk2.disable_date < l_sys_date
534 or msi.disable_date < l_sys_date))
535 or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
536 and msi.subinventory_type = 2
537 and (milk2.disable_date is null or milk2.disable_date >= l_sys_date)))
538 and milk2.segment19 is null
539 and milk2.segment20 is null
540 and milk2.inventory_location_id = nvl(milk1.physical_location_id, milk1.inventory_location_id)
541 and milk2.organization_id = l_organization_id -- newly modified
542 and milk1.organization_id = milk2.organization_id
543 and milk1.inventory_location_id = wooih.to_locator_id -- newly modified
544 and milk1.organization_id = l_organization_id -- newly modified
545 and wzl.zone_id = l_pre_specified_zone_id
546 and wzl.subinventory_code = wooih.to_subinventory_code -- newly modified
547 and wzl.subinventory_code = l_pre_specified_sub_code
548 and wzl.subinventory_code = msi.secondary_inventory_name -- new
549 and (wzl.entire_sub_flag = 'Y' or wzl.inventory_location_id = wooih.to_locator_id)
550 and wzl.organization_id = l_organization_id
551 and wlpn.subinventory_code = msi.secondary_inventory_name
552 and wooih.to_subinventory_code = l_pre_specified_sub_code
553 and wooih.to_subinventory_code = msi.secondary_inventory_name
554 and msi.secondary_inventory_name = l_pre_specified_sub_code -- new
555 and msi.organization_id = l_organization_id --,newly modified
556 and wooih.to_locator_id = decode(p_mode, 1, wooih.to_locator_id, 2, p_locator_id)
557 and wlpn.locator_id = decode(p_mode, 1, wooih.to_locator_id, 2, p_locator_id)
558 and wlpn.subinventory_code = wooih.to_subinventory_code
559 and wlpn.subinventory_code = l_pre_specified_sub_code
560 and wlpn.organization_id = l_organization_id
561 and wlpn.lpn_id = nvl(wdth.transfer_lpn_id, wdth.content_lpn_id)
562 and wdth.status = 6 -- Completed. lookup_type is WMS_TASK_STATUS
563 and wdth.transaction_id = wooih.source_task_id
564 and wdth.inventory_item_id = p_item_id
565 and wdth.organization_id = l_organization_id -- new
566 and wooih.operation_sequence in (select max(operation_sequence)
567 from wms_op_opertn_instances_hist wooih2
568 where wooih2.op_plan_instance_id = wopih.op_plan_instance_id
569 and wooih2.operation_type_id in (2,9)
570 and wooih2.operation_status = 3)
571 and wooih.op_plan_instance_id = wopih.op_plan_instance_id
572 and wopih.status = 3 -- Plan Completed
573 and wopih.activity_type_id = l_activity_type_id
574 and wopih.organization_id = l_organization_id -- new
575 --and wopih.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopih.plan_type_id)
576 --and wopih.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopih.orig_dest_sub_code )
577 --and wopih.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopih.orig_dest_loc_id)
578 and wopih.orig_dest_sub_code = l_orig_dest_sub_code
579 order by wooih.last_update_date desc;
580 --
581 --
582 cursor c_act_wzone_wsub_woitem is
583 select wooi.from_subinventory_code, milk2.inventory_location_id, wzl.zone_id
584 from
585 mtl_item_locations milk1,
586 mtl_item_locations milk2,
587 mtl_secondary_inventories msi,
588 wms_zone_locators wzl,
589 -- ***** tables from the inner cursor *****
590 wms_op_operation_instances wooi,
591 wms_op_operation_instances wooi2,
592 wms_op_plan_instances wopi
593 where
594 milk2.subinventory_code = msi.secondary_inventory_name
595 and milk2.organization_id = msi.organization_id
596 and ((milk2.disable_date is null and msi.disable_date is null)
597 or (not ( milk2.disable_date < l_sys_date
598 or msi.disable_date < l_sys_date))
599 or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
600 and msi.subinventory_type = 2
601 and (milk2.disable_date is null or milk2.disable_date >= l_sys_date)))
602 and wzl.zone_id = l_pre_specified_zone_id
603 and wzl.subinventory_code = wooi.from_subinventory_code
604 and (wzl.entire_sub_flag = 'Y' or wzl.inventory_location_id = wooi.from_locator_id)
605 and wzl.organization_id = l_organization_id
606 and nvl(msi.subinventory_type, 1) = decode(l_is_in_inventory, 'Y', 1, 2) -- 1:storage, 2:receiving
607 and wooi.from_subinventory_code = msi.secondary_inventory_name
608 and msi.secondary_inventory_name = l_pre_specified_sub_code
609 and msi.organization_id = l_organization_id
610 and milk2.segment19 is null
611 and milk2.segment20 is null
612 and milk2.inventory_location_id = nvl(milk1.physical_location_id, milk1.inventory_location_id)
613 and milk1.organization_id = milk2.organization_id
614 and milk1.inventory_location_id = wooi.from_locator_id
618 -- *****from the inner cursor****
615 and milk1.organization_id = wooi.organization_id
616 and wooi.from_locator_id = decode(p_mode, 1, wooi.from_locator_id, 2, p_locator_id)
617 and wooi.organization_id = l_organization_id
619 and wopi.status = 6 -- (Operation Plan Status : Active, Completed)
620 and wopi.op_plan_instance_id = wooi.op_plan_instance_id
621 and wooi2.operation_status = 3 -- Completed
622 and wooi2.operation_type_id = 2 -- Drop
623 and wooi2.op_plan_instance_id = wopi.op_plan_instance_id
624 and (wooi.operation_status = 1 and wooi.operation_type_id <> 2)
625 and wopi.activity_type_id = l_activity_type_id
626 and wopi.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopi.plan_type_id)
627 and wopi.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopi.orig_dest_sub_code )
628 and wopi.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopi.orig_dest_loc_id)
629 order by wooi.creation_date desc;
630 --
631 --
632 cursor c_comp_wzone_wsub_woitem is
633 select wooih.to_subinventory_code, milk2.inventory_location_id, wzl.zone_id
634 from wms_op_plan_instances_hist wopih,
635 wms_op_opertn_instances_hist wooih,
636 mtl_secondary_inventories msi,
637 wms_zone_locators wzl,
638 wms_dispatched_tasks_history wdth,
639 wms_license_plate_numbers wlpn,
640 mtl_item_locations milk1,
641 mtl_item_locations milk2
642 where milk2.subinventory_code = msi.secondary_inventory_name
643 and milk2.organization_id = msi.organization_id
644 and ((milk2.disable_date is null and msi.disable_date is null)
645 or (not ( milk2.disable_date < l_sys_date
646 or msi.disable_date < l_sys_date))
647 or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
648 and msi.subinventory_type = 2
649 and (milk2.disable_date is null or milk2.disable_date >= l_sys_date)))
650 and milk2.segment19 is null
651 and milk2.segment20 is null
652 and milk2.inventory_location_id = nvl(milk1.physical_location_id, milk1.inventory_location_id)
653 and milk2.organization_id = l_organization_id
654 and milk1.inventory_location_id = wlpn.locator_id
655 and milk1.inventory_location_id = wooih.to_locator_id -- new
656 and milk1.organization_id = l_organization_id
657 and wzl.zone_id = l_pre_specified_zone_id
658 and wzl.subinventory_code = wlpn.subinventory_code
659 and wzl.subinventory_code = wooih.to_subinventory_code -- new
660 and wzl.subinventory_code = l_pre_specified_sub_code -- new
661 and wzl.subinventory_code = msi.secondary_inventory_name
662 and (wzl.entire_sub_flag = 'Y' or wzl.inventory_location_id = wooih.to_locator_id)
663 and wzl.organization_id = l_organization_id
664 and wlpn.subinventory_code = msi.secondary_inventory_name
665 and msi.secondary_inventory_name = l_pre_specified_sub_code -- new
666 and msi.organization_id = l_organization_id -- newly modified
667 and wooih.to_locator_id = decode(p_mode, 1, wooih.to_locator_id, 2, p_locator_id)
668 and wlpn.subinventory_code = wooih.to_subinventory_code
669 and wlpn.subinventory_code = l_pre_specified_sub_code -- new
670 and wlpn.organization_id = l_organization_id
671 and wlpn.lpn_id = nvl(wdth.transfer_lpn_id, wdth.content_lpn_id)
672 and wdth.status = 6 -- Completed. lookup_type is WMS_TASK_STATUS
673 and wdth.transaction_id = wooih.source_task_id
674 and wooih.operation_sequence in (select max(operation_sequence)
675 from wms_op_opertn_instances_hist wooih2
676 -- where wooih2.op_plan_instance_id = wooih.op_plan_instance_id
677 where wooih2.op_plan_instance_id = wopih.op_plan_instance_id
678 and wooih2.operation_type_id in (2,9)
679 and wooih2.operation_status = 3)
680 and wooih.op_plan_instance_id = wopih.op_plan_instance_id
681 and wopih.status = 3 -- Plan Completed
682 and wopih.activity_type_id = l_activity_type_id
683 and wopih.organization_id = l_organization_id
684 and wopih.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopih.plan_type_id)
685 --and wopih.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopih.orig_dest_sub_code )
686 and wopih.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopih.orig_dest_loc_id)
687 order by wooih.last_update_date desc;
688 --
689 --
690 cursor c_comp_wzonesub_woitem_destsub is
691 select wooih.to_subinventory_code, milk2.inventory_location_id, wzl.zone_id
692 from wms_op_plan_instances_hist wopih,
693 wms_op_opertn_instances_hist wooih,
694 mtl_secondary_inventories msi,
695 wms_zone_locators wzl,
696 wms_dispatched_tasks_history wdth,
697 wms_license_plate_numbers wlpn,
698 mtl_item_locations milk1,
699 mtl_item_locations milk2
700 where milk2.subinventory_code = msi.secondary_inventory_name
701 and milk2.organization_id = msi.organization_id
702 and ((milk2.disable_date is null and msi.disable_date is null)
703 or (not ( milk2.disable_date < l_sys_date
704 or msi.disable_date < l_sys_date))
705 or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
706 and msi.subinventory_type = 2
707 and (milk2.disable_date is null or milk2.disable_date >= l_sys_date)))
708 and milk2.segment19 is null
709 and milk2.segment20 is null
710 and milk2.inventory_location_id = nvl(milk1.physical_location_id, milk1.inventory_location_id)
711 and milk2.organization_id = l_organization_id
712 and milk1.inventory_location_id = wlpn.locator_id
713 and milk1.inventory_location_id = wooih.to_locator_id -- new
714 and milk1.organization_id = l_organization_id
715 and wzl.zone_id = l_pre_specified_zone_id
716 and wzl.subinventory_code = wlpn.subinventory_code
717 and wzl.subinventory_code = wooih.to_subinventory_code -- new
721 and wzl.organization_id = l_organization_id
718 and wzl.subinventory_code = l_pre_specified_sub_code -- new
719 and wzl.subinventory_code = msi.secondary_inventory_name
720 and (wzl.entire_sub_flag = 'Y' or wzl.inventory_location_id = wooih.to_locator_id)
722 and wlpn.subinventory_code = msi.secondary_inventory_name
723 and msi.secondary_inventory_name = l_pre_specified_sub_code -- new
724 and msi.organization_id = l_organization_id -- newly modified
725 and wooih.to_locator_id = decode(p_mode, 1, wooih.to_locator_id, 2, p_locator_id)
726 and wlpn.subinventory_code = wooih.to_subinventory_code
727 and wlpn.subinventory_code = l_pre_specified_sub_code -- new
728 and wlpn.organization_id = l_organization_id
729 and wlpn.lpn_id = nvl(wdth.transfer_lpn_id, wdth.content_lpn_id)
730 and wdth.status = 6 -- Completed. lookup_type is WMS_TASK_STATUS
731 and wdth.transaction_id = wooih.source_task_id
732 and wooih.operation_sequence in (select max(operation_sequence)
733 from wms_op_opertn_instances_hist wooih2
734 -- where wooih2.op_plan_instance_id = wooih.op_plan_instance_id
735 where wooih2.op_plan_instance_id = wopih.op_plan_instance_id
736 and wooih2.operation_type_id in (2,9)
737 and wooih2.operation_status = 3)
738 and wooih.op_plan_instance_id = wopih.op_plan_instance_id
739 and wopih.status = 3 -- Plan Completed
740 and wopih.activity_type_id = l_activity_type_id
741 and wopih.organization_id = l_organization_id
742 --and wopih.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopih.plan_type_id)
743 --and wopih.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopih.orig_dest_sub_code )
744 --and wopih.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopih.orig_dest_loc_id)
745 and wopih.orig_dest_sub_code = l_orig_dest_sub_code
746 order by wooih.last_update_date desc;
747 --
748 --
749 cursor c_act_wzone_only_witem is
750 select wooi.from_subinventory_code, milk2.inventory_location_id, wzl.zone_id
751 from mtl_item_locations milk1,
752 mtl_item_locations milk2,
753 mtl_secondary_inventories msi,
754 wms_zone_locators wzl,
755 -- ***** tables from the inner cursor *****
756 mtl_material_transactions_temp mmtt,
757 wms_op_operation_instances wooi,
758 wms_op_operation_instances wooi2,
759 wms_op_plan_instances wopi
760 where milk2.subinventory_code = msi.secondary_inventory_name
761 and milk2.organization_id = msi.organization_id
762 and ((milk2.disable_date is null and msi.disable_date is null)
763 or (not ( milk2.disable_date < l_sys_date
764 or msi.disable_date < l_sys_date))
765 or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
766 and msi.subinventory_type = 2
767 and (milk2.disable_date is null or milk2.disable_date >= l_sys_date)))
768 and wzl.zone_id = l_pre_specified_zone_id
769 and wzl.subinventory_code = wooi.from_subinventory_code
770 and (wzl.entire_sub_flag = 'Y' or wzl.inventory_location_id = wooi.from_locator_id)
771 and wzl.organization_id = l_organization_id
772 and nvl(msi.subinventory_type, 1) = decode(l_is_in_inventory, 'Y', 1, 2) -- 1:storage, 2:receiving
773 and wooi.from_subinventory_code = msi.secondary_inventory_name
774 and msi.organization_id = l_organization_id
775 and milk2.segment19 is null
776 and milk2.segment20 is null
777 and milk2.inventory_location_id = nvl(milk1.physical_location_id, milk1.inventory_location_id)
778 and milk1.organization_id = milk2.organization_id
779 and milk1.inventory_location_id = wooi.from_locator_id
780 and milk1.organization_id = wooi.organization_id
781 and wooi.from_locator_id = decode(p_mode, 1, wooi.from_locator_id, 2, p_locator_id)
782 and wooi.organization_id = l_organization_id
783 -- *****from the inner cursor****
784 and wopi.organization_id = l_organization_id
785 and wopi.status = 6 -- (Operation Plan Status : Active, Completed)
786 and wopi.op_plan_instance_id = wooi.op_plan_instance_id
787 and wooi2.operation_status = 3 -- Completed
788 and wooi2.operation_type_id = 2 -- Drop
789 and wooi2.op_plan_instance_id = wopi.op_plan_instance_id
790 and wooi.source_task_id = mmtt.transaction_temp_id
791 and mmtt.organization_id = l_organization_id
792 and (wooi.operation_status = 1 and wooi.operation_type_id <> 2)
793 and mmtt.inventory_item_id = p_item_id
794 and wopi.activity_type_id = l_activity_type_id
795 --and wooi.activity_type_id = l_activity_type_id
796 and wopi.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopi.plan_type_id)
797 and wopi.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopi.orig_dest_sub_code )
798 and wopi.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopi.orig_dest_loc_id)
799 order by mmtt.creation_date desc;
800 --
801 --
802 cursor c_comp_wzone_only_witem is
803 select wooih.to_subinventory_code, milk2.inventory_location_id, wzl.zone_id
804 from mtl_item_locations milk1,
805 mtl_item_locations milk2,
806 mtl_secondary_inventories msi,
807 wms_zone_locators wzl,
808 -- ***** tables from the inner cursor *****
809 wms_license_plate_numbers wlpn,
810 wms_dispatched_tasks_history wdth,
811 wms_op_opertn_instances_hist wooih,
812 wms_op_plan_instances_hist wopih
813 where milk2.subinventory_code = msi.secondary_inventory_name
814 and milk2.organization_id = msi.organization_id
815 and ((milk2.disable_date is null and msi.disable_date is null)
816 or (not ( milk2.disable_date < l_sys_date
817 or msi.disable_date < l_sys_date))
818 or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
819 and msi.subinventory_type = 2
820 and (milk2.disable_date is null or milk2.disable_date >= l_sys_date)))
824 and milk2.organization_id = l_organization_id -- newly modified
821 and milk2.segment19 is null
822 and milk2.segment20 is null
823 and milk2.inventory_location_id = nvl(milk1.physical_location_id, milk1.inventory_location_id)
825 and milk1.inventory_location_id = wooih.to_locator_id
826 and milk1.inventory_location_id = decode(p_mode, 1, milk1.inventory_location_id, 2, p_locator_id)
827 and milk1.organization_id = wlpn.organization_id
828 and milk1.organization_id = l_organization_id -- newly modified
829 and wzl.zone_id = l_pre_specified_zone_id
830 and wzl.subinventory_code = wlpn.subinventory_code
831 and wzl.subinventory_code = msi.secondary_inventory_name
832 and (wzl.entire_sub_flag = 'Y' or wzl.inventory_location_id = wooih.to_locator_id)
833 and wzl.organization_id = l_organization_id
834 and nvl(msi.subinventory_type, 1) = decode(l_is_in_inventory, 'Y', 1, 2) -- 1:storage, 2:receiving
835 and msi.organization_id = l_organization_id -- newly modified
836 and wooih.to_locator_id = decode(p_mode, 1, wooih.to_locator_id, 2, p_locator_id)
837 and wlpn.locator_id = wooih.to_locator_id
838 and wlpn.subinventory_code = msi.secondary_inventory_name
839 and wlpn.subinventory_code = wooih.to_subinventory_code
840 and wlpn.locator_id = decode(p_mode, 1, wlpn.locator_id, 2, p_locator_id)
841 and wlpn.organization_id = l_organization_id
842 and wlpn.lpn_id = nvl(wdth.transfer_lpn_id, wdth.content_lpn_id)
843 and wdth.status = 6 -- Completed. lookup_type is WMS_TASK_STATUS
844 and wdth.transaction_id = wooih.source_task_id
845 and wdth.inventory_item_id = p_item_id
846 and wdth.organization_id = l_organization_id -- new
847 and wooih.organization_id = l_organization_id -- new
848 and wooih.to_subinventory_code = msi.secondary_inventory_name -- new
849 and wooih.operation_sequence in (select max(operation_sequence)
850 from wms_op_opertn_instances_hist wooih2
851 where wooih2.op_plan_instance_id = wopih.op_plan_instance_id
852 and wooih2.operation_type_id in (2,9)
853 and wooih2.operation_status = 3)
854 and wopih.op_plan_instance_id = wooih.op_plan_instance_id
855 and wopih.status = 3 -- Plan Completed
856 and wopih.activity_type_id = l_activity_type_id
857 and wopih.organization_id = l_organization_id
858 and wopih.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopih.plan_type_id)
859 --and wopih.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopih.orig_dest_sub_code )
860 and wopih.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopih.orig_dest_loc_id)
861 order by wooih.last_update_date desc;
862 --
863 --
864 cursor c_comp_wzoneonlyitem_destsub is
865 select wooih.to_subinventory_code, milk2.inventory_location_id, wzl.zone_id
866 from mtl_item_locations milk1,
867 mtl_item_locations milk2,
868 mtl_secondary_inventories msi,
869 wms_zone_locators wzl,
870 -- ***** tables from the inner cursor *****
871 wms_license_plate_numbers wlpn,
872 wms_dispatched_tasks_history wdth,
873 wms_op_opertn_instances_hist wooih,
874 wms_op_plan_instances_hist wopih
875 where milk2.subinventory_code = msi.secondary_inventory_name
876 and milk2.organization_id = msi.organization_id
877 and ((milk2.disable_date is null and msi.disable_date is null)
878 or (not ( milk2.disable_date < l_sys_date
879 or msi.disable_date < l_sys_date))
880 or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
881 and msi.subinventory_type = 2
882 and (milk2.disable_date is null or milk2.disable_date >= l_sys_date)))
883 and milk2.segment19 is null
884 and milk2.segment20 is null
885 and milk2.inventory_location_id = nvl(milk1.physical_location_id, milk1.inventory_location_id)
886 and milk2.organization_id = l_organization_id -- newly modified
887 and milk1.inventory_location_id = wooih.to_locator_id
888 and milk1.inventory_location_id = decode(p_mode, 1, milk1.inventory_location_id, 2, p_locator_id)
889 and milk1.organization_id = wlpn.organization_id
890 and milk1.organization_id = l_organization_id -- newly modified
891 and wzl.zone_id = l_pre_specified_zone_id
892 and wzl.subinventory_code = wlpn.subinventory_code
893 and wzl.subinventory_code = msi.secondary_inventory_name
894 and (wzl.entire_sub_flag = 'Y' or wzl.inventory_location_id = wooih.to_locator_id)
895 and wzl.organization_id = l_organization_id
896 and nvl(msi.subinventory_type, 1) = decode(l_is_in_inventory, 'Y', 1, 2) -- 1:storage, 2:receiving
897 and msi.organization_id = l_organization_id -- newly modified
898 and wooih.to_locator_id = decode(p_mode, 1, wooih.to_locator_id, 2, p_locator_id)
899 and wlpn.locator_id = wooih.to_locator_id
900 and wlpn.subinventory_code = msi.secondary_inventory_name
901 and wlpn.subinventory_code = wooih.to_subinventory_code
902 and wlpn.locator_id = decode(p_mode, 1, wlpn.locator_id, 2, p_locator_id)
903 and wlpn.organization_id = l_organization_id
904 and wlpn.lpn_id = nvl(wdth.transfer_lpn_id, wdth.content_lpn_id)
905 and wdth.status = 6 -- Completed. lookup_type is WMS_TASK_STATUS
906 and wdth.transaction_id = wooih.source_task_id
907 and wdth.inventory_item_id = p_item_id
908 and wdth.organization_id = l_organization_id -- new
909 and wooih.organization_id = l_organization_id -- new
910 and wooih.to_subinventory_code = msi.secondary_inventory_name -- new
911 and wooih.operation_sequence in (select max(operation_sequence)
912 from wms_op_opertn_instances_hist wooih2
913 where wooih2.op_plan_instance_id = wopih.op_plan_instance_id
914 and wooih2.operation_type_id in (2,9)
915 and wooih2.operation_status = 3)
916 and wopih.op_plan_instance_id = wooih.op_plan_instance_id
917 and wopih.status = 3 -- Plan Completed
918 and wopih.activity_type_id = l_activity_type_id
922 --and wopih.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopih.orig_dest_loc_id)
919 and wopih.organization_id = l_organization_id
920 --and wopih.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopih.plan_type_id)
921 --and wopih.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopih.orig_dest_sub_code )
923 and wopih.orig_dest_sub_code = l_orig_dest_sub_code
924 order by wooih.last_update_date desc;
925 --
926 --
927 cursor c_act_wzone_only_woitem is
928 select wooi.from_subinventory_code, milk2.inventory_location_id, wzl.zone_id
929 from mtl_item_locations milk1,
930 mtl_item_locations milk2,
931 mtl_secondary_inventories msi,
932 wms_zone_locators wzl,
933 -- ***** tables from the inner cursor *****
934 wms_op_operation_instances wooi,
935 wms_op_operation_instances wooi2,
936 wms_op_plan_instances wopi
937 where milk2.subinventory_code = msi.secondary_inventory_name
938 and milk2.organization_id = msi.organization_id
939 and ((milk2.disable_date is null and msi.disable_date is null)
940 or (not ( milk2.disable_date < l_sys_date
941 or msi.disable_date < l_sys_date))
942 or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
943 and msi.subinventory_type = 2
944 and (milk2.disable_date is null or milk2.disable_date >= l_sys_date)))
945 and wzl.zone_id = l_pre_specified_zone_id
946 and wzl.subinventory_code = wooi.from_subinventory_code
947 and (wzl.entire_sub_flag = 'Y' or wzl.inventory_location_id = wooi.from_locator_id)
948 and wzl.organization_id = l_organization_id
949 and nvl(msi.subinventory_type, 1) = decode(l_is_in_inventory, 'Y', 1, 2) -- 1:storage, 2:receiving
950 and msi.secondary_inventory_name = wooi.from_subinventory_code
951 and msi.organization_id = l_organization_id
952 and milk2.segment19 is null
953 and milk2.segment20 is null
954 and milk2.inventory_location_id = nvl(milk1.physical_location_id, milk1.inventory_location_id)
955 and milk1.organization_id = milk2.organization_id
956 and milk1.inventory_location_id = decode(p_mode, 1, milk1.inventory_location_id, 2, p_locator_id) -- new
957 and milk1.inventory_location_id = wooi.from_locator_id
958 and milk1.organization_id = wooi.organization_id
959 and wooi.from_locator_id = decode(p_mode, 1, wooi.from_locator_id, 2, p_locator_id)
960 and wooi.organization_id = l_organization_id
961 -- *****from the inner cursor****
962 and wopi.organization_id = l_organization_id
963 and wopi.status = 6 -- (Operation Plan Status : Active, Completed)
964 and wopi.op_plan_instance_id = wooi.op_plan_instance_id
965 and wooi2.operation_status = 3 -- Completed
966 and wooi2.operation_type_id = 2 -- Drop
967 and wooi2.op_plan_instance_id = wopi.op_plan_instance_id
968 and (wooi.operation_status = 1 and wooi.operation_type_id <> 2)
969 and wopi.organization_id = l_organization_id
970 and wooi.activity_type_id = l_activity_type_id
971 and wopi.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopi.plan_type_id)
972 and wopi.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopi.orig_dest_sub_code )
973 and wopi.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopi.orig_dest_loc_id)
974 order by wooi.creation_date desc;
975 --
976 --
977 cursor c_comp_wzone_only_woitem is
978 select wooih.to_subinventory_code, milk2.inventory_location_id, wzl.zone_id
979 from wms_op_plan_instances_hist wopih,
980 wms_op_opertn_instances_hist wooih,
981 mtl_secondary_inventories msi,
982 wms_zone_locators wzl,
983 wms_dispatched_tasks_history wdth,
984 wms_license_plate_numbers wlpn,
985 mtl_item_locations milk1,
986 mtl_item_locations milk2
987 where milk2.subinventory_code = msi.secondary_inventory_name
988 and milk2.organization_id = msi.organization_id
989 and ((milk2.disable_date is null and msi.disable_date is null)
990 or (not ( milk2.disable_date < l_sys_date
991 or msi.disable_date < l_sys_date))
992 or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
993 and msi.subinventory_type = 2
994 and (milk2.disable_date is null or milk2.disable_date >= l_sys_date)))
995 and milk2.segment19 is null
996 and milk2.segment20 is null
997 and milk2.inventory_location_id = nvl(milk1.physical_location_id, milk1.inventory_location_id)
998 and milk2.organization_id = l_organization_id -- newly modified
999 and milk1.organization_id = milk2.organization_id
1000 and milk1.inventory_location_id = wlpn.locator_id
1001 and milk1.inventory_location_id = wooih.to_locator_id -- new
1002 and milk1.organization_id = l_organization_id -- newly modified
1003 and wzl.zone_id = l_pre_specified_zone_id
1004 and wzl.subinventory_code = wlpn.subinventory_code
1005 and wzl.subinventory_code = msi.secondary_inventory_name -- new
1006 and (wzl.entire_sub_flag = 'Y' or wzl.inventory_location_id = wooih.to_locator_id)
1007 and wzl.organization_id = l_organization_id
1008 and nvl(msi.subinventory_type, 1) = decode(l_is_in_inventory, 'Y', 1, 2) -- 1:storage, 2:receiving
1009 and msi.secondary_inventory_name = wooih.to_subinventory_code -- new
1010 and msi.organization_id = l_organization_id -- newly modified
1011 and wooih.to_locator_id = decode(p_mode, 1, wooih.to_locator_id, 2, p_locator_id)
1012 and wlpn.subinventory_code = msi.secondary_inventory_name
1013 and wlpn.subinventory_code = wooih.to_subinventory_code
1014 and wlpn.organization_id = l_organization_id
1015 and wlpn.lpn_id = nvl(wdth.transfer_lpn_id, wdth.content_lpn_id)
1016 and wdth.status = 6 -- Completed. lookup_type is WMS_TASK_STATUS
1017 and wdth.transaction_id = wooih.source_task_id
1018 and wooih.operation_sequence in (select max(operation_sequence)
1019 from wms_op_opertn_instances_hist wooih2
1020 where wooih2.op_plan_instance_id = wopih.op_plan_instance_id
1021 and wooih2.operation_type_id in (2,9)
1022 and wooih2.operation_status = 3)
1023 and wooih.op_plan_instance_id = wopih.op_plan_instance_id
1024 and wopih.status = 3 -- Plan Completed
1025 and wopih.activity_type_id = l_activity_type_id
1026 and wopih.organization_id = l_organization_id
1027 and wopih.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopih.plan_type_id)
1028 --and wopih.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopih.orig_dest_sub_code )
1029 and wopih.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopih.orig_dest_loc_id)
1030 order by wooih.last_update_date desc;
1031 --
1032 --
1033 cursor c_comp_wzoneonlywoitem_destsub is
1034 select wooih.to_subinventory_code, milk2.inventory_location_id, wzl.zone_id
1035 from wms_op_plan_instances_hist wopih,
1036 wms_op_opertn_instances_hist wooih,
1037 mtl_secondary_inventories msi,
1038 wms_zone_locators wzl,
1039 wms_dispatched_tasks_history wdth,
1040 wms_license_plate_numbers wlpn,
1041 mtl_item_locations milk1,
1042 mtl_item_locations milk2
1043 where milk2.subinventory_code = msi.secondary_inventory_name
1044 and milk2.organization_id = msi.organization_id
1045 and ((milk2.disable_date is null and msi.disable_date is null)
1046 or (not ( milk2.disable_date < l_sys_date
1047 or msi.disable_date < l_sys_date))
1048 or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
1049 and msi.subinventory_type = 2
1050 and (milk2.disable_date is null or milk2.disable_date >= l_sys_date)))
1051 and milk2.segment19 is null
1052 and milk2.segment20 is null
1053 and milk2.inventory_location_id = nvl(milk1.physical_location_id, milk1.inventory_location_id)
1054 and milk2.organization_id = l_organization_id -- newly modified
1055 and milk1.organization_id = milk2.organization_id
1056 and milk1.inventory_location_id = wlpn.locator_id
1057 and milk1.inventory_location_id = wooih.to_locator_id -- new
1058 and milk1.organization_id = l_organization_id -- newly modified
1059 and wzl.zone_id = l_pre_specified_zone_id
1060 and wzl.subinventory_code = wlpn.subinventory_code
1061 and wzl.subinventory_code = msi.secondary_inventory_name -- new
1062 and (wzl.entire_sub_flag = 'Y' or wzl.inventory_location_id = wooih.to_locator_id)
1063 and wzl.organization_id = l_organization_id
1064 and nvl(msi.subinventory_type, 1) = decode(l_is_in_inventory, 'Y', 1, 2) -- 1:storage, 2:receiving
1065 and msi.secondary_inventory_name = wooih.to_subinventory_code -- new
1066 and msi.organization_id = l_organization_id -- newly modified
1067 and wooih.to_locator_id = decode(p_mode, 1, wooih.to_locator_id, 2, p_locator_id)
1068 and wlpn.subinventory_code = msi.secondary_inventory_name
1069 and wlpn.subinventory_code = wooih.to_subinventory_code
1070 and wlpn.organization_id = l_organization_id
1071 and wlpn.lpn_id = nvl(wdth.transfer_lpn_id, wdth.content_lpn_id)
1072 and wdth.status = 6 -- Completed. lookup_type is WMS_TASK_STATUS
1073 and wdth.transaction_id = wooih.source_task_id
1074 and wooih.operation_sequence in (select max(operation_sequence)
1075 from wms_op_opertn_instances_hist wooih2
1076 where wooih2.op_plan_instance_id = wopih.op_plan_instance_id
1077 and wooih2.operation_type_id in (2,9)
1078 and wooih2.operation_status = 3)
1079 and wooih.op_plan_instance_id = wopih.op_plan_instance_id
1080 and wopih.status = 3 -- Plan Completed
1081 and wopih.activity_type_id = l_activity_type_id
1082 and wopih.organization_id = l_organization_id
1086 and wopih.orig_dest_sub_code = l_orig_dest_sub_code
1083 --and wopih.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopih.plan_type_id)
1084 --and wopih.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopih.orig_dest_sub_code )
1085 --and wopih.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopih.orig_dest_loc_id)
1087 order by wooih.last_update_date desc;
1088 --
1089 --
1090 cursor c_act_wozone_witem is
1091 select wooi.from_subinventory_code, milk2.inventory_location_id
1092 from
1093 mtl_item_locations milk1,
1094 mtl_item_locations milk2,
1095
1096 mtl_secondary_inventories msi,
1097 -- ***** tables from the inner cursor *****
1098 mtl_material_transactions_temp mmtt,
1099 wms_op_operation_instances wooi,
1100 wms_op_operation_instances wooi2,
1101 wms_op_plan_instances wopi
1102 where
1103 milk2.subinventory_code = msi.secondary_inventory_name
1104 and milk2.organization_id = msi.organization_id
1105 and ((milk2.disable_date is null and msi.disable_date is null)
1106 or (not ( milk2.disable_date < l_sys_date
1107 or msi.disable_date < l_sys_date))
1108 or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
1109 and msi.subinventory_type = 2
1110 and (milk2.disable_date is null or milk2.disable_date >= l_sys_date)))
1111 and nvl(msi.subinventory_type, 1) = decode(l_is_in_inventory, 'Y', 1, 2) -- 1:storage, 2:receiving
1112 and wooi.from_subinventory_code = msi.secondary_inventory_name
1113 and wooi.from_subinventory_code = l_pre_specified_sub_code
1114 and msi.secondary_inventory_name = l_pre_specified_sub_code
1115 and msi.organization_id = l_organization_id
1116 and milk2.segment19 is null
1117 and milk2.segment20 is null
1118 and milk2.inventory_location_id = nvl(milk1.physical_location_id, milk1.inventory_location_id)
1119 and milk1.organization_id = milk2.organization_id
1120 and milk1.inventory_location_id = wooi.from_locator_id
1121 and milk1.organization_id = wooi.organization_id
1122 and wooi.from_locator_id = decode(p_mode, 1, wooi.from_locator_id, 2, p_locator_id)
1123 and wooi.organization_id = l_organization_id
1124 -- *****from the inner cursor****
1125 and wopi.status = 6 -- (Operation Plan Status : In progress, Completed)
1126 and wopi.op_plan_instance_id = wooi.op_plan_instance_id
1127 and wooi2.operation_status = 3 -- Completed
1128 and wooi2.operation_type_id = 2 -- Drop
1129 and wooi2.op_plan_instance_id = wopi.op_plan_instance_id
1130 and wooi.source_task_id = mmtt.transaction_temp_id -- Added as per Amin in review.
1131 and mmtt.organization_id = l_organization_id
1132 and (wooi.operation_status = 1 and wooi.operation_type_id <> 2)
1133 and mmtt.transaction_temp_id <> p_task_id
1134 and mmtt.inventory_item_id = p_item_id
1135 and wopi.activity_type_id = l_activity_type_id
1136 and wooi.activity_type_id = l_activity_type_id
1137 and wopi.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopi.plan_type_id)
1138 and wopi.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopi.orig_dest_sub_code )
1139 and wopi.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopi.orig_dest_loc_id)
1140 order by mmtt.creation_date desc;
1141 --
1142 --
1143 cursor c_comp_wozone_witem is
1144 select wlpn.subinventory_code, milk2.inventory_location_id
1145 from wms_op_plan_instances_hist wopih,
1146 wms_op_opertn_instances_hist wooih,
1147 mtl_secondary_inventories msi,
1148 wms_dispatched_tasks_history wdth,
1149 wms_license_plate_numbers wlpn,
1150 mtl_item_locations milk1,
1151 mtl_item_locations milk2
1152 where milk2.subinventory_code = msi.secondary_inventory_name
1153 and milk2.organization_id = msi.organization_id
1154 and ((milk2.disable_date is null and msi.disable_date is null)
1155 or (not ( milk2.disable_date < l_sys_date
1156 or msi.disable_date < l_sys_date))
1157 or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
1158 and msi.subinventory_type = 2
1159 and (milk2.disable_date is null or milk2.disable_date >= l_sys_date)))
1160 and wooih.to_subinventory_code = msi.secondary_inventory_name
1161 and wooih.to_subinventory_code = l_pre_specified_sub_code
1162 and wooih.organization_id = l_organization_id -- new
1163 and wlpn.subinventory_code = msi.secondary_inventory_name
1164 and wlpn.subinventory_code = l_pre_specified_sub_code
1165 and msi.secondary_inventory_name = l_pre_specified_sub_code
1166 and msi.organization_id = l_organization_id -- newly modified
1167 and milk2.segment19 is null
1168 and milk2.segment20 is null
1169 and milk2.inventory_location_id = nvl(milk1.physical_location_id, milk1.inventory_location_id)
1170 and milk2.organization_id = l_organization_id -- new
1171 and milk1.organization_id = l_organization_id -- newly modified
1172 and milk1.inventory_location_id = wooih.to_locator_id -- new
1173 and milk1.inventory_location_id = wlpn.locator_id
1174 and milk1.organization_id = l_organization_id
1175 and wooih.to_locator_id = decode(p_mode, 1, wooih.to_locator_id, 2, p_locator_id)
1176 and wlpn.locator_id = wooih.to_locator_id
1177 and wlpn.subinventory_code = wooih.to_subinventory_code
1178 and wlpn.organization_id = l_organization_id
1179 and wlpn.lpn_id = nvl(wdth.transfer_lpn_id, wdth.content_lpn_id)
1180 and wdth.status = 6 -- Completed. lookup_type is WMS_TASK_STATUS
1181 and wdth.transaction_id = wooih.source_task_id
1182 and wdth.organization_id = l_organization_id
1183 and wdth.inventory_item_id = p_item_id
1184 and wooih.operation_sequence in (select max(operation_sequence)
1185 from wms_op_opertn_instances_hist wooih2
1186 where wooih2.op_plan_instance_id = wopih.op_plan_instance_id
1187 and wooih2.operation_type_id in (2,9)
1191 and wopih.organization_id = l_organization_id -- new
1188 and wooih2.operation_status = 3)
1189 and wooih.op_plan_instance_id = wopih.op_plan_instance_id
1190 and wooih.organization_id = l_organization_id -- new
1192 and wopih.status = 3 -- Plan Completed
1193 and wopih.activity_type_id = l_activity_type_id
1194 and wopih.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopih.plan_type_id)
1195 --and wopih.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopih.orig_dest_sub_code )
1196 and wopih.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopih.orig_dest_loc_id)
1197 order by wooih.last_update_date desc;
1198 --
1199 --
1200 cursor c_comp_wozone_witem_destsub is
1201 select wlpn.subinventory_code, milk2.inventory_location_id
1202 from wms_op_plan_instances_hist wopih,
1203 wms_op_opertn_instances_hist wooih,
1204 mtl_secondary_inventories msi,
1205 wms_dispatched_tasks_history wdth,
1206 wms_license_plate_numbers wlpn,
1207 mtl_item_locations milk1,
1208 mtl_item_locations milk2
1209 where milk2.subinventory_code = msi.secondary_inventory_name
1210 and milk2.organization_id = msi.organization_id
1211 and ((milk2.disable_date is null and msi.disable_date is null)
1212 or (not ( milk2.disable_date < l_sys_date
1213 or msi.disable_date < l_sys_date))
1214 or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
1215 and msi.subinventory_type = 2
1216 and (milk2.disable_date is null or milk2.disable_date >= l_sys_date)))
1217 and wooih.to_subinventory_code = msi.secondary_inventory_name
1218 and wooih.to_subinventory_code = l_pre_specified_sub_code
1219 and wooih.organization_id = l_organization_id -- new
1220 and wlpn.subinventory_code = msi.secondary_inventory_name
1221 and wlpn.subinventory_code = l_pre_specified_sub_code
1222 and msi.secondary_inventory_name = l_pre_specified_sub_code
1223 and msi.organization_id = l_organization_id -- newly modified
1224 and milk2.segment19 is null
1225 and milk2.segment20 is null
1226 and milk2.inventory_location_id = nvl(milk1.physical_location_id, milk1.inventory_location_id)
1227 and milk2.organization_id = l_organization_id -- new
1228 and milk1.organization_id = l_organization_id -- newly modified
1229 and milk1.inventory_location_id = wooih.to_locator_id -- new
1230 and milk1.inventory_location_id = wlpn.locator_id
1231 and milk1.organization_id = l_organization_id
1232 and wooih.to_locator_id = decode(p_mode, 1, wooih.to_locator_id, 2, p_locator_id)
1233 and wlpn.locator_id = wooih.to_locator_id
1234 and wlpn.subinventory_code = wooih.to_subinventory_code
1235 and wlpn.organization_id = l_organization_id
1236 and wlpn.lpn_id = nvl(wdth.transfer_lpn_id, wdth.content_lpn_id)
1237 and wdth.status = 6 -- Completed. lookup_type is WMS_TASK_STATUS
1238 and wdth.transaction_id = wooih.source_task_id
1239 and wdth.organization_id = l_organization_id
1240 and wdth.inventory_item_id = p_item_id
1241 and wooih.operation_sequence in (select max(operation_sequence)
1242 from wms_op_opertn_instances_hist wooih2
1243 where wooih2.op_plan_instance_id = wopih.op_plan_instance_id
1244 and wooih2.operation_type_id in (2,9)
1245 and wooih2.operation_status = 3)
1246 and wooih.op_plan_instance_id = wopih.op_plan_instance_id
1247 and wooih.organization_id = l_organization_id -- new
1248 and wopih.organization_id = l_organization_id -- new
1249 and wopih.status = 3 -- Plan Completed
1250 and wopih.activity_type_id = l_activity_type_id
1251 --and wopih.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopih.plan_type_id)
1252 --and wopih.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopih.orig_dest_sub_code )
1253 --and wopih.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopih.orig_dest_loc_id)
1254 and wopih.orig_dest_sub_code = l_orig_dest_sub_code
1255 order by wooih.last_update_date desc;
1256 --
1257 --
1258 cursor c_act_wozone_woitem is
1259 select wooi.from_subinventory_code, milk2.inventory_location_id
1260 from
1261 mtl_item_locations milk1,
1262 mtl_item_locations milk2,
1263 mtl_secondary_inventories msi,
1264 -- ***** tables from the inner cursor *****
1265 wms_op_operation_instances wooi,
1266 wms_op_operation_instances wooi2,
1267 wms_op_plan_instances wopi
1268 where milk2.subinventory_code = msi.secondary_inventory_name
1269 and milk2.organization_id = msi.organization_id
1270 and ((milk2.disable_date is null and msi.disable_date is null)
1271 or (not ( milk2.disable_date < l_sys_date
1272 or msi.disable_date < l_sys_date))
1273 or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
1274 and msi.subinventory_type = 2
1275 and (milk2.disable_date is null or milk2.disable_date >= l_sys_date)))
1276 and nvl(msi.subinventory_type, 1) = decode(l_is_in_inventory, 'Y', 1, 2) -- 1:storage, 2:receiving
1277 and wooi.from_subinventory_code = msi.secondary_inventory_name
1278 and msi.secondary_inventory_name = l_pre_specified_sub_code
1279 and msi.organization_id = wooi.organization_id
1280 and msi.organization_id = l_organization_id
1281 and milk2.segment19 is null
1282 and milk2.segment20 is null
1283 and milk2.inventory_location_id = nvl(milk1.physical_location_id, milk1.inventory_location_id)
1284 and milk1.organization_id = milk2.organization_id
1285 and milk1.inventory_location_id = wooi.from_locator_id
1286 and milk1.organization_id = wooi.organization_id
1287 and wooi.from_locator_id = decode(p_mode, 1, wooi.from_locator_id, 2, p_locator_id)
1288 -- *****from the inner cursor****
1289 and wopi.status = 6 -- (Operation Plan Status : In progress, Completed)
1290 and wopi.op_plan_instance_id = wooi.op_plan_instance_id
1294 and (wooi.operation_status = 1 and wooi.operation_type_id <> 2)
1291 and wooi2.operation_status = 3 -- Completed
1292 and wooi2.operation_type_id = 2 -- Drop
1293 and wooi2.op_plan_instance_id = wopi.op_plan_instance_id
1295 and wopi.activity_type_id = l_activity_type_id
1296 and wooi.organization_id = l_organization_id
1297 and wopi.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopi.plan_type_id)
1298 and wopi.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopi.orig_dest_sub_code )
1299 and wopi.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopi.orig_dest_loc_id)
1300 order by wooi.creation_date desc;
1301 --
1302 --
1303 cursor c_comp_wozone_woitem is
1304 select wlpn.subinventory_code, milk2.inventory_location_id
1305 from wms_op_plan_instances_hist wopih,
1306 wms_op_opertn_instances_hist wooih,
1307 mtl_secondary_inventories msi,
1308 wms_dispatched_tasks_history wdth,
1309 wms_license_plate_numbers wlpn,
1310 mtl_item_locations milk1,
1311 mtl_item_locations milk2
1312 where milk2.subinventory_code = msi.secondary_inventory_name
1313 and milk2.organization_id = msi.organization_id
1314 and ((milk2.disable_date is null and msi.disable_date is null)
1315 or (not ( milk2.disable_date < l_sys_date
1316 or msi.disable_date < l_sys_date))
1317 or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
1318 and msi.subinventory_type = 2
1319 and (milk2.disable_date is null or milk2.disable_date >= l_sys_date)))
1320 and nvl(msi.subinventory_type, 1) = decode(l_is_in_inventory, 'Y', 1, 2) -- 1:storage, 2:receiving
1321 and wooih.to_subinventory_code = msi.secondary_inventory_name
1322 and wlpn.subinventory_code = msi.secondary_inventory_name
1323 and msi.secondary_inventory_name = l_pre_specified_sub_code
1324 and msi.organization_id = l_organization_id
1325 and milk2.segment19 is null
1326 and milk2.segment20 is null
1327 and milk2.inventory_location_id = nvl(milk1.physical_location_id, milk1.inventory_location_id)
1328 and milk1.organization_id = milk2.organization_id
1329 and milk1.inventory_location_id = wooih.to_locator_id
1330 and milk1.inventory_location_id = wlpn.locator_id
1331 and milk1.organization_id = l_organization_id
1332 and wooih.to_locator_id = decode(p_mode, 1, wooih.to_locator_id, 2, p_locator_id)
1333 and wlpn.locator_id = wooih.to_locator_id
1334 and wlpn.subinventory_code = wooih.to_subinventory_code
1335 and wlpn.organization_id = l_organization_id
1336 and wlpn.lpn_id = nvl(wdth.transfer_lpn_id, wdth.content_lpn_id)
1337 and wdth.status = 6 -- Completed. lookup_type is WMS_TASK_STATUS
1338 and wdth.transaction_id = wooih.source_task_id
1339 and wooih.operation_sequence in (select max(operation_sequence)
1340 from wms_op_opertn_instances_hist wooih2
1341 where wooih2.op_plan_instance_id = wopih.op_plan_instance_id
1342 and wooih2.operation_type_id in (2,9)
1343 and wooih2.operation_status = 3)
1344 and wooih.op_plan_instance_id = wopih.op_plan_instance_id
1345 and wopih.status = 3 -- Plan Completed
1346 and wopih.activity_type_id = l_activity_type_id
1347 and wopih.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopih.plan_type_id)
1348 --and wopih.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopih.orig_dest_sub_code )
1349 and wopih.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopih.orig_dest_loc_id)
1350 order by wooih.last_update_date desc;
1351 --
1352 --
1353 cursor c_comp_wozone_woitem_destsub is
1354 select wlpn.subinventory_code, milk2.inventory_location_id
1355 from wms_op_plan_instances_hist wopih,
1356 wms_op_opertn_instances_hist wooih,
1357 mtl_secondary_inventories msi,
1358 wms_dispatched_tasks_history wdth,
1359 wms_license_plate_numbers wlpn,
1360 mtl_item_locations milk1,
1361 mtl_item_locations milk2
1362 where milk2.subinventory_code = msi.secondary_inventory_name
1363 and milk2.organization_id = msi.organization_id
1364 and ((milk2.disable_date is null and msi.disable_date is null)
1365 or (not ( milk2.disable_date < l_sys_date
1366 or msi.disable_date < l_sys_date))
1367 or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
1368 and msi.subinventory_type = 2
1369 and (milk2.disable_date is null or milk2.disable_date >= l_sys_date)))
1370 and nvl(msi.subinventory_type, 1) = decode(l_is_in_inventory, 'Y', 1, 2) -- 1:storage, 2:receiving
1371 and wooih.to_subinventory_code = msi.secondary_inventory_name
1372 and wlpn.subinventory_code = msi.secondary_inventory_name
1373 and msi.secondary_inventory_name = l_pre_specified_sub_code
1374 and msi.organization_id = l_organization_id
1375 and milk2.segment19 is null
1376 and milk2.segment20 is null
1377 and milk2.inventory_location_id = nvl(milk1.physical_location_id, milk1.inventory_location_id)
1378 and milk1.organization_id = milk2.organization_id
1379 and milk1.inventory_location_id = wooih.to_locator_id
1380 and milk1.inventory_location_id = wlpn.locator_id
1381 and milk1.organization_id = l_organization_id
1382 and wooih.to_locator_id = decode(p_mode, 1, wooih.to_locator_id, 2, p_locator_id)
1383 and wlpn.locator_id = wooih.to_locator_id
1384 and wlpn.subinventory_code = wooih.to_subinventory_code
1385 and wlpn.organization_id = l_organization_id
1386 and wlpn.lpn_id = nvl(wdth.transfer_lpn_id, wdth.content_lpn_id)
1387 and wdth.status = 6 -- Completed. lookup_type is WMS_TASK_STATUS
1388 and wdth.transaction_id = wooih.source_task_id
1389 and wooih.operation_sequence in (select max(operation_sequence)
1390 from wms_op_opertn_instances_hist wooih2
1391 where wooih2.op_plan_instance_id = wopih.op_plan_instance_id
1392 and wooih2.operation_type_id in (2,9)
1393 and wooih2.operation_status = 3)
1397 --and wopih.plan_type_id = decode(l_loc_mtrl_grp_rule_id,1,l_plan_type_id,wopih.plan_type_id)
1394 and wooih.op_plan_instance_id = wopih.op_plan_instance_id
1395 and wopih.status = 3 -- Plan Completed
1396 and wopih.activity_type_id = l_activity_type_id
1398 --and wopih.orig_dest_sub_code = decode(l_loc_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopih.orig_dest_sub_code )
1399 --and wopih.orig_dest_loc_id = decode(l_loc_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopih.orig_dest_loc_id)
1400 and wopih.orig_dest_sub_code = l_orig_dest_sub_code
1401 order by wooih.last_update_date desc;
1402 --
1403 --
1404 cursor c_aux_wzone_wsub is
1405 select milk.inventory_location_id, milk.subinventory_code, wzl.zone_id
1406 from mtl_secondary_inventories msi,
1407 mtl_item_locations milk,
1408 wms_zone_locators wzl
1409 where milk.subinventory_code = msi.secondary_inventory_name
1410 and milk.organization_id = msi.organization_id
1411 and ((milk.disable_date is null and msi.disable_date is null)
1412 or (not ( milk.disable_date < l_sys_date
1413 or msi.disable_date < l_sys_date))
1414 or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
1415 and msi.subinventory_type = 2
1416 and (milk.disable_date is null or milk.disable_date >= l_sys_date)))
1417 and milk.segment20 is null
1418 and milk.segment19 is null
1419 and milk.subinventory_code = msi.secondary_inventory_name
1420 and milk.subinventory_code = l_pre_specified_sub_code
1421 and milk.organization_id = l_organization_id -- new
1422 and msi.secondary_inventory_name = wzl.subinventory_code
1423 and msi.secondary_inventory_name = l_pre_specified_sub_code -- new
1424 and msi.organization_id = l_organization_id -- new
1425 and milk.inventory_location_id = decode(p_mode, 1, milk.inventory_location_id, 2, p_locator_id)
1426 and (wzl.entire_sub_flag = 'Y' or wzl.inventory_location_id = milk.inventory_location_id)
1427 and wzl.organization_id = l_organization_id
1428 and wzl.subinventory_code = l_pre_specified_sub_code
1429 and wzl.zone_id = l_pre_specified_zone_id
1430 order by nvl(milk.empty_flag, 'N') desc, nvl(milk.location_suggested_units, 0), milk.dropping_order asc, milk.picking_order asc;
1431 --
1432 --
1433 cursor c_aux_wzone_only is
1434 select milk.inventory_location_id, milk.subinventory_code, wzl.zone_id
1435 from mtl_secondary_inventories msi,
1436 mtl_item_locations milk,
1437 wms_zone_locators wzl
1438 where milk.subinventory_code = msi.secondary_inventory_name
1439 and milk.organization_id = msi.organization_id
1440 and ((milk.disable_date is null and msi.disable_date is null)
1441 or (not ( milk.disable_date < l_sys_date
1442 or msi.disable_date < l_sys_date))
1443 or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
1444 and msi.subinventory_type = 2
1445 and (milk.disable_date is null or milk.disable_date >= l_sys_date)))
1446 and milk.segment20 is null
1447 and milk.segment19 is null
1448 and milk.subinventory_code = msi.secondary_inventory_name
1449 and milk.organization_id = l_organization_id -- new
1450 and nvl(msi.subinventory_type, 1) = decode(l_is_in_inventory, 'Y', 1, 2) -- 1:storage, 2:receiving
1451 and msi.secondary_inventory_name = wzl.subinventory_code
1452 and msi.organization_id = l_organization_id -- new
1453 and milk.inventory_location_id = decode(p_mode, 1, milk.inventory_location_id, 2, p_locator_id)
1454 and (wzl.entire_sub_flag = 'Y' or wzl.inventory_location_id = milk.inventory_location_id)
1455 and wzl.organization_id = l_organization_id -- newly modified
1456 and wzl.zone_id = l_pre_specified_zone_id
1457 order by nvl(milk.empty_flag, 'N') desc, nvl(milk.location_suggested_units, 0), milk.dropping_order asc, milk.picking_order asc;
1458 --
1459 --
1460 cursor c_aux_wsub_only is
1461 select milk.inventory_location_id, milk.subinventory_code
1462 from mtl_secondary_inventories msi,
1463 mtl_item_locations milk
1464 where ((milk.disable_date is null and msi.disable_date is null)
1465 or (not ( milk.disable_date < l_sys_date
1466 or msi.disable_date < l_sys_date))
1467 or ( msi.disable_date = to_date('01/01/1700', 'DD/MM/RRRR')
1468 and msi.subinventory_type = 2
1469 and (milk.disable_date is null or milk.disable_date >= l_sys_date)))
1470 and milk.segment20 is null
1471 and milk.segment19 is null
1472 and milk.inventory_location_id = decode(p_mode, 1, milk.inventory_location_id, 2, p_locator_id)
1473 and milk.subinventory_code = msi.secondary_inventory_name
1474 and milk.organization_id = l_organization_id -- new
1475 and milk.subinventory_code = l_pre_specified_sub_code -- new
1476 and msi.secondary_inventory_name = l_pre_specified_sub_code
1477 and msi.organization_id = l_organization_id
1478 order by nvl(milk.empty_flag, 'N') desc, nvl(milk.location_suggested_units, 0), milk.dropping_order asc, milk.picking_order asc;
1479 --
1480 --
1481 begin
1482 -- ### Initialize API return status to success
1483 x_return_status := FND_API.G_RET_STS_SUCCESS;
1484
1485 -- Initialize message stack since p_init_msg_list is set to TRUE
1486 -- The p_init_msg_list is set to 'TRUE' in this code and so the message stack will always be initialised.
1487 --if fnd_api.to_boolean(p_init_msg_list) then
1488 -- fnd_msg_pub.initialize;
1489 --end if;
1490
1491 if (l_debug = 1) then
1492 trace(' Entering procedure '|| l_proc || ':'|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
1493 trace(l_proc || ' p_mode => ' || p_mode);
1494 trace(l_proc || ' p_task_id => ' || p_task_id);
1495 trace(l_proc || ' p_activity_type_id => ' || p_activity_type_id);
1496 trace(l_proc || ' p_locator_id => ' || p_locator_id);
1497 trace(l_proc || ' p_item_id => ' || p_item_id);
1498 end if;
1499
1500 open c_sysdate;
1501 fetch c_sysdate into l_sysdate;
1502 close c_sysdate;
1506
1503
1504 dbms_output.put_line(' l_sysdate => ' || l_sysdate);
1505 l_sys_date := to_date(l_sysdate, 'RRRR/MM/DD HH24:MI:SS');
1507 l_prog := 10;
1508 -- ### Setting Cursor Name
1509 l_cursor := 'c_oper_plan_details';
1510 if (l_debug =1 ) then
1511 trace(l_proc || ' Opening "Operation Plan Details" cursor "' ||l_cursor||'"', 1);
1512 end if;
1513
1514 -- ### Derive Operation Plan details to start with.
1515 open c_oper_plan_details;
1516 fetch c_oper_plan_details
1517 into l_operation_plan_id, l_locator_id, l_subinventory_code, l_operation_plan_dtl_id,
1518 l_activity_type_id, l_plan_type_id, l_orig_dest_sub_code, l_orig_dest_loc_id,
1519 l_pre_specified_zone_id, l_pre_specified_sub_code, l_loc_mtrl_grp_rule_id,
1520 l_operation_type, l_is_in_inventory, l_organization_id, l_lpn_id;
1521
1522 if c_oper_plan_details%NOTFOUND then
1523 fnd_message.set_name('WMS', 'WMS_OPERTN_PLAN_ID_INVALID');
1524 fnd_msg_pub.ADD;
1525 raise fnd_api.g_exc_error; -- Added after Code Review on Sept 11th 2003.
1526 else
1527 if (l_debug = 1) then
1528 trace(l_proc || ' Operation Plan ID => ' || nvl(l_operation_plan_id, -99));
1529 trace(l_proc || ' Locator ID => ' || nvl(l_locator_id, -99));
1530 trace(l_proc || ' Subinventory Code => ' || nvl(l_subinventory_code, '@@@'));
1531 trace(l_proc || ' Operation Plan Detail ID => ' || nvl(l_operation_plan_dtl_id, -99));
1532 trace(l_proc || ' Activity Type ID => ' || nvl(l_activity_type_id, -99));
1533 trace(l_proc || ' Plan Type ID => ' || nvl(l_plan_type_id, -99));
1534 trace(l_proc || ' Original Dest Sub Code => ' || nvl(l_orig_dest_sub_code, '@@@'));
1535 trace(l_proc || ' Original Dest Loc ID => ' || nvl(l_orig_dest_loc_id, -99));
1536 trace(l_proc || ' Pre-specified Zone ID => ' || nvl(l_pre_specified_zone_id, -99));
1537 trace(l_proc || ' Pre-specified Sub Code => ' || nvl(l_pre_specified_sub_code, '@@@'));
1538 trace(l_proc || ' Material Grouping Rule ID => ' || nvl(l_loc_mtrl_grp_rule_id, -99));
1539 trace(l_proc || ' Operation Plan Type => ' || nvl(l_operation_type, -99));
1540 trace(l_proc || ' Is In INV Flag => ' || nvl(l_is_in_inventory, 'N'));
1541 trace(l_proc || ' Organization ID => ' || nvl(l_organization_id, -1));
1542 trace(l_proc || ' l_lpn_id => ' || l_lpn_id);
1543 trace(l_proc || ' Closing " Operation Details " cursor "' ||l_cursor||'"', 4);
1544 end if;
1545 -- ### Close the above cursor.
1546 close c_oper_plan_details;
1547 l_prog := 11;
1548 -- ### Check to see if a valid Material Grouping Rule is stamped on the oeration plan detail.
1549 -- ### The LOV on the Form field allows to select a valid Rule only. Hence the possible cases
1550 -- ### are that either there is a valid value or a null value. Hence check only for null.
1551 -- ### Changes effected Oct. 6th 2003.
1552 -- ### Since an nvl has been added to all the derived variables in the cursor the check to see if
1553 -- ### l_loc_mtrl_grp_rule_id is null is being removed.
1554 -- if (l_loc_mtrl_grp_rule_id is null or l_loc_mtrl_grp_rule_id not in (1,2,3)) then
1555 if (l_loc_mtrl_grp_rule_id not in (1,2,3)) then
1556 trace('Incorrect Material Group ID stamped on the operation plan detail line ... Cannot Proceed');
1557 fnd_message.set_name('WMS', 'WMS_MTRL_GRP_RULE_ID_IS_NULL');
1558 fnd_msg_pub.ADD;
1559 raise fnd_api.g_exc_error; -- Added after Code Review on Sept 11th 2003.
1560 end if;
1561 -- ### Added after review with Amin on Oct 3rd 2003.
1562 -- ### Check to make sure for valid data.
1563 -- ### Changes effected Oct. 6th 2003.
1564 -- ### Commented out the following statement. The assumption here is that the is_in_inventory flag on the detail line
1565 -- ### will not be null. It'll be either 'Y' or 'N'. But to accomodate cases where the is_in_inventory is null, the
1566 -- ### SELECT clause of the _oper_plan_details cursor has been modified as follows:
1567 -- ### Before :
1568 -- ### wopd.is_in_inventory,
1569 -- ### After:
1570 -- ### nvl(wopd.is_in_inventory, 'N'),
1571 -- if (l_is_in_inventory is null or l_is_in_inventory not in ('Y','N')) then
1572 if (l_is_in_inventory not in ('Y','N')) then
1573 trace('Incorrect is_in_inventory flag stamped on the operation plan detail line ... Cannot Proceed');
1574 fnd_message.set_name('WMS', 'WMS_INVALID_ISININVFLAG');
1575 fnd_msg_pub.ADD;
1576 raise fnd_api.g_exc_error; -- Added after Code Review on Sept 11th 2003.
1577 end if;
1578 end if;
1579
1580 l_prog := 20;
1581 --
1582 -- @@@ As per the new design as of Sept 16th 2003, the code logic will fork based on if the variable
1583 -- @@@ " l_pre_specified_zone_id" is populated from the fetch of the Operation Plan Detail cursor.
1584 -- @@@ Now the same cursor is opened irrespective of the Material Grouping Rule stamped on the Operation
1585 -- @@@ Plan Detail Line.
1586 -- ### Prespecified Zone is not null..
1587 if l_pre_specified_zone_id is not null then
1588 -- @@@ Pending Cursors are being coded within separate if..then..end if enclosures while checking for
1589 -- @@@ pre-specified sub codes.
1590 if l_pre_specified_sub_code is not null then
1591
1592 -- Added for bug 3393371
1593
1594 IF p_item_id IS NULL THEN
1595 l_cursor := 'c_subloc_pend_wzn_wsub_woitem';
1596 if (l_debug =1 ) then
1597 trace(l_proc || ' Within "l_pre_specified_zone_id is not null" segment....', 1);
1598 trace(l_proc || ' Within "l_pre_specified_sub_code is not null" segment....', 1);
1599 trace(l_proc || ' Within "p_item_id is null" segment....', 1);
1600 trace(l_proc || ' Opening "Pending operations" cursor "' ||l_cursor||'"', 1);
1601 end if;
1602
1603 open c_subloc_pend_wzn_wsub_woitem;
1607 if c_subloc_pend_wzn_wsub_woitem%NOTFOUND then
1604 fetch c_subloc_pend_wzn_wsub_woitem
1605 into x_subinventory_code, x_locator_id;
1606
1608 if (l_debug =1 ) then
1609 trace(l_proc || ' "c_subloc_pend_wzn_wsub_woitem" failed with %NOTFOUND...', 1);
1610 trace(l_proc || ' Setting OUT variables to null...', 1);
1611 trace(l_proc || ' Closing "active operations" cursor "' ||l_cursor||'"', 1);
1612 end if;
1613 x_locator_id := null;
1614 x_zone_id := null;
1615 x_subinventory_code := null;
1616 close c_subloc_pend_wzn_wsub_woitem;
1617 l_cursor := null;
1618 elsif c_subloc_pend_wzn_wsub_woitem%FOUND then
1619 if (l_debug =1 ) then
1620 trace(l_proc || ' Found sub/loc from pending task within the same LPN.', 1);
1621 trace(l_proc || ' x_subinventory_code = '||x_subinventory_code, 1);
1622 trace(l_proc || ' x_locator_id = '||x_locator_id, 1);
1623 end if;
1624 close c_subloc_pend_wzn_wsub_woitem;
1625 --
1626 -- ### Call trace message before exiting...
1627 --
1628 exit_proc_msg( x_return_status => x_return_status
1629 , x_msg_count => x_msg_count
1630 , x_msg_data => x_msg_data
1631 , x_locator_id => x_locator_id
1632 , x_zone_id => x_zone_id
1633 , x_subinventory_code => x_subinventory_code
1634 , x_loc_valid => x_loc_valid
1635 , l_proc => l_proc);
1636 return;
1637 end if;-- Marker: c_subloc_pend_wzn_wsub_woitem FOUND/NOTFOUND
1638
1639 ELSE -- IF p_item_id IS NULL
1640 l_cursor := 'c_subloc_pend_wzn_wsub_witem';
1641 if (l_debug =1 ) then
1642 trace(l_proc || ' Within "l_pre_specified_zone_id is not null" segment....', 1);
1643 trace(l_proc || ' Within "l_pre_specified_sub_code is not null" segment....', 1);
1644 trace(l_proc || ' Within "p_item_id is not null" segment....', 1);
1645 trace(l_proc || ' Opening "Pending operations" cursor "' ||l_cursor||'"', 1);
1646 end if;
1647
1648 open c_subloc_pend_wzn_wsub_witem;
1649 fetch c_subloc_pend_wzn_wsub_witem
1650 into x_subinventory_code, x_locator_id;
1651
1652 if c_subloc_pend_wzn_wsub_witem%NOTFOUND then
1653 if (l_debug =1 ) then
1654 trace(l_proc || ' "c_subloc_pend_wzn_wsub_witem" failed with %NOTFOUND...', 1);
1655 trace(l_proc || ' Setting OUT variables to null...', 1);
1656 trace(l_proc || ' Closing "active operations" cursor "' ||l_cursor||'"', 1);
1657 end if;
1658 x_locator_id := null;
1659 x_zone_id := null;
1660 x_subinventory_code := null;
1661 close c_subloc_pend_wzn_wsub_witem;
1662 l_cursor := null;
1663 elsif c_subloc_pend_wzn_wsub_witem%FOUND then
1664 if (l_debug =1 ) then
1665 trace(l_proc || ' Found sub/loc from pending task within the same LPN.', 1);
1666 trace(l_proc || ' x_subinventory_code = '||x_subinventory_code, 1);
1667 trace(l_proc || ' x_locator_id = '||x_locator_id, 1);
1668 end if;
1669 close c_subloc_pend_wzn_wsub_witem;
1670 --
1671 -- ### Call trace message before exiting...
1672 --
1673 exit_proc_msg( x_return_status => x_return_status
1674 , x_msg_count => x_msg_count
1675 , x_msg_data => x_msg_data
1676 , x_locator_id => x_locator_id
1677 , x_zone_id => x_zone_id
1678 , x_subinventory_code => x_subinventory_code
1679 , x_loc_valid => x_loc_valid
1680 , l_proc => l_proc);
1681 return;
1682 end if;-- Marker: c_subloc_pend_wzn_wsub_witem FOUND/NOTFOUND
1683
1684 END IF; -- IF p_item_id IS NULL
1685
1686 elsif l_pre_specified_sub_code is null then
1687 -- Added for bug 3393371
1688 IF p_item_id IS NULL THEN
1689 l_cursor := 'c_subloc_pend_wzn_wosub_woitem';
1690 if (l_debug =1 ) then
1691 trace(l_proc || ' Within "l_pre_specified_zone_id is not null" segment....', 1);
1692 trace(l_proc || ' Within "l_pre_specified_sub_code is null" segment....', 1);
1693 trace(l_proc || ' Within "p_item_id is null" segment....', 1);
1694 trace(l_proc || ' Opening "Pending operations" cursor "' ||l_cursor||'"', 1);
1695 end if;
1696
1697 open c_subloc_pend_wzn_wosub_woitem;
1698 fetch c_subloc_pend_wzn_wosub_woitem
1699 into x_subinventory_code, x_locator_id;
1700
1701 if c_subloc_pend_wzn_wosub_woitem%NOTFOUND then
1702 if (l_debug =1 ) then
1703 trace(l_proc || ' "c_subloc_pend_wzn_wosub_woitem" failed with %NOTFOUND...', 1);
1704 trace(l_proc || ' Setting OUT variables to null...', 1);
1705 trace(l_proc || ' Closing "active operations" cursor "' ||l_cursor||'"', 1);
1706 end if;
1707 x_locator_id := null;
1708 x_zone_id := null;
1709 x_subinventory_code := null;
1710 close c_subloc_pend_wzn_wosub_woitem;
1711 l_cursor := null;
1712 elsif c_subloc_pend_wzn_wosub_woitem%FOUND then
1713 if (l_debug =1 ) then
1714 trace(l_proc || ' Found sub/loc from pending task within the same LPN.', 1);
1715 trace(l_proc || ' x_subinventory_code = '||x_subinventory_code, 1);
1716 trace(l_proc || ' x_locator_id = '||x_locator_id, 1);
1717 end if;
1718 close c_subloc_pend_wzn_wosub_woitem;
1719 --
1720 -- ### Call trace message before exiting...
1721 --
1722 exit_proc_msg(x_return_status => x_return_status
1723 , x_msg_count => x_msg_count
1724 , x_msg_data => x_msg_data
1725 , x_locator_id => x_locator_id
1726 , x_zone_id => x_zone_id
1727 , x_subinventory_code => x_subinventory_code
1728 , x_loc_valid => x_loc_valid
1729 , l_proc => l_proc);
1730 return;
1734 l_cursor := 'c_subloc_pend_wzn_wosub_witem';
1731 end if;-- Marker: c_subloc_pend_wzn_wosub_woitem FOUND/NOTFOUND
1732
1733 ELSE -- IF p_item_id IS NULL
1735 if (l_debug =1 ) then
1736 trace(l_proc || ' Within "l_pre_specified_zone_id is not null" segment....', 1);
1737 trace(l_proc || ' Within "l_pre_specified_sub_code is null" segment....', 1);
1738 trace(l_proc || ' Within "p_item_id is not null" segment....', 1);
1739 trace(l_proc || ' Opening "Pending operations" cursor "' ||l_cursor||'"', 1);
1740 end if;
1741
1742 open c_subloc_pend_wzn_wosub_witem;
1743 fetch c_subloc_pend_wzn_wosub_witem
1744 into x_subinventory_code, x_locator_id;
1745
1746 if c_subloc_pend_wzn_wosub_witem%NOTFOUND then
1747 if (l_debug =1 ) then
1748 trace(l_proc || ' "c_subloc_pend_wzn_wosub_witem" failed with %NOTFOUND...', 1);
1749 trace(l_proc || ' Setting OUT variables to null...', 1);
1750 trace(l_proc || ' Closing "active operations" cursor "' ||l_cursor||'"', 1);
1751 end if;
1752 x_locator_id := null;
1753 x_zone_id := null;
1754 x_subinventory_code := null;
1755 close c_subloc_pend_wzn_wosub_witem;
1756 l_cursor := null;
1757 elsif c_subloc_pend_wzn_wosub_witem%FOUND then
1758 if (l_debug =1 ) then
1759 trace(l_proc || ' Found sub/loc from pending task within the same LPN.', 1);
1760 trace(l_proc || ' x_subinventory_code = '||x_subinventory_code, 1);
1761 trace(l_proc || ' x_locator_id = '||x_locator_id, 1);
1762 end if;
1763 close c_subloc_pend_wzn_wosub_witem;
1764 --
1765 -- ### Call trace message before exiting...
1766 --
1767 exit_proc_msg(x_return_status => x_return_status
1768 , x_msg_count => x_msg_count
1769 , x_msg_data => x_msg_data
1770 , x_locator_id => x_locator_id
1771 , x_zone_id => x_zone_id
1772 , x_subinventory_code => x_subinventory_code
1773 , x_loc_valid => x_loc_valid
1774 , l_proc => l_proc);
1775 return;
1776 end if;-- Marker: c_subloc_pend_wzn_wosub_witem FOUND/NOTFOUND
1777
1778 END IF; -- IF p_item_id IS NULL
1779
1780 end if;-- Marker: Check l_pre_specified_sub_code
1781
1782
1783 if l_pre_specified_sub_code is not null then
1784 -- @@@ With Zone, With Subinventory, With Item
1785 if p_item_id is not null then
1786 -- ### Setting Cursor Name.
1787 l_cursor := 'c_act_wzone_wsub_witem';
1788 if (l_debug =1 ) then
1789 trace(l_proc || ' Opening "active operations" cursor "' ||l_cursor||'"', 1);
1790 end if;
1791 -- ### Open Cursor c_act_wzone_wsub_witem to look for "active operation" plans.
1792 open c_act_wzone_wsub_witem;
1793 fetch c_act_wzone_wsub_witem
1794 into l_subinventory_code, l_locator_id, l_zone_id;
1795
1796 if c_act_wzone_wsub_witem%NOTFOUND then
1797 if (l_debug =1 ) then
1798 trace(l_proc || ' "c_act_wzone_wsub_witem" failed with %NOTFOUND...', 1);
1799 trace(l_proc || ' Setting OUT variables to null...', 1);
1800 trace(l_proc || ' Closing "active operations" cursor "' ||l_cursor||'"', 1);
1801 end if;
1802 x_locator_id := null;
1803 x_zone_id := null;
1804 x_subinventory_code := null;
1805 close c_act_wzone_wsub_witem;
1806 l_cursor := null;
1807
1808 -- ### "active operations" cursor did not return any records, open the "completed operations" cursor
1809 -- ### Opening cursor c_comp_wzone_wsub_witem to look for completed operations.
1810 -- ### Setting Cursor Name
1811 if (l_debug =1 ) then
1812 trace(l_proc || ' Opening "completed operations" cursor "' ||l_cursor||'"', 1);
1813 end if;
1814
1815 -- ### Check for Material grouping rule when opening the completed operations cursor.
1816 if l_loc_mtrl_grp_rule_id = 2 then
1817 -- ### Open Cursor c_comp_wzonesubitem_destsub to look for "completed operation" plans.
1818 l_cursor := 'c_comp_wzonesubitem_destsub';
1819 open c_comp_wzonesubitem_destsub;
1820 fetch c_comp_wzonesubitem_destsub
1821 into l_subinventory_code, l_locator_id, l_zone_id;
1822
1823 if c_comp_wzonesubitem_destsub%NOTFOUND then
1824 if (l_debug = 1) then
1825 trace(l_proc || ' "c_comp_wzonesubitem_destsub" failed with %NOTFOUND...', 1);
1826 trace(l_proc || ' Setting OUT variables to null...', 1);
1827 trace(l_proc || ' Closing "completed operations" cursor "' ||l_cursor||'"', 1);
1828 end if;
1829 x_locator_id := null;
1830 x_zone_id := null;
1831 x_subinventory_code := null;
1832 close c_comp_wzonesubitem_destsub;
1833 l_cursor := null;
1834 elsif c_comp_wzonesubitem_destsub%FOUND then
1835 -- c_comp_wzonesubitem_destsub cursor found.
1836 if (l_debug = 1) then
1837 trace(l_proc || ' "c_comp_wzonesubitem_destsub" FOUND...', 1);
1838 trace(l_proc || ' Closing cursor "' ||l_cursor||'"', 1);
1839 end if;
1840 close c_comp_wzonesubitem_destsub;
1841 l_cur_found := true;
1842 end if;-- @@@ Marker :c_comp_wzonesubitem_destsub FOUND/NOTFOUND
1843 elsif l_loc_mtrl_grp_rule_id <> 2 then
1844 -- ### Open Cursor c_comp_wzone_wsub_witem to look for "completed operation" plans.
1845 l_cursor := 'c_comp_wzone_wsub_witem';
1846 open c_comp_wzone_wsub_witem;
1847 fetch c_comp_wzone_wsub_witem
1848 into l_subinventory_code, l_locator_id, l_zone_id;
1849
1850 if c_comp_wzone_wsub_witem%NOTFOUND then
1854 trace(l_proc || ' Closing "completed operations" cursor "' ||l_cursor||'"', 1);
1851 if (l_debug = 1) then
1852 trace(l_proc || ' "c_comp_wzone_wsub_witem" failed with %NOTFOUND...', 1);
1853 trace(l_proc || ' Setting OUT variables to null...', 1);
1855 end if;
1856 x_locator_id := null;
1857 x_zone_id := null;
1858 x_subinventory_code := null;
1859 close c_comp_wzone_wsub_witem;
1860 l_cursor := null;
1861 elsif c_comp_wzone_wsub_witem%FOUND then
1862 -- c_comp_wzone_wsub_witem cursor found.
1863 if (l_debug = 1) then
1864 trace(l_proc || ' "c_comp_wzone_wsub_witem" FOUND...', 1);
1865 trace(l_proc || ' Closing cursor "' ||l_cursor||'"', 1);
1866 end if;
1867 close c_comp_wzone_wsub_witem;
1868 l_cur_found := true;
1869 end if; -- @@@ Marker :c_comp_wzone_wsub_witem FOUND/NOTFOUND
1870 end if; -- @@@ Marker: Check for material grouping rule(l_loc_mtrl_grp_rule_id)
1871
1872 if (l_debug =1 ) then
1873 trace(l_proc || ' Both main cursors failed to return any values...', 1);
1874 trace(l_proc || ' Plan "B": Opening Auxillary Cursors to find a location ID ', 4);
1875 end if;
1876 elsif c_act_wzone_wsub_witem%FOUND then
1877 -- c_act_wzone_wsub_witem cursor found.
1878 if (l_debug = 1) then
1879 trace(l_proc || ' "c_act_wzone_wsub_witem" FOUND...', 1);
1880 trace(l_proc || ' Closing cursor "' ||l_cursor||'"', 1);
1881 end if;
1882 close c_act_wzone_wsub_witem;
1883 l_cur_found := true;
1884 end if; -- @@@ Marker: c_act_wzone_wsub_witem FOUND/NOTFOUND
1885 elsif p_item_id is null then
1886 -- @@@ With Zone, With Subinventory, Without Item
1887 -- ### Setting Cursor Name.
1888 l_cursor := 'c_act_wzone_wsub_woitem';
1889 if (l_debug =1 ) then
1890 trace(l_proc || ' Opening "active operations" cursor "' ||l_cursor||'"', 1);
1891 end if;
1892 -- ### Open Cursor c_act_wzone_wsub_woitem to look for "active operation" plans.
1893 open c_act_wzone_wsub_woitem;
1894 fetch c_act_wzone_wsub_woitem
1895 into l_subinventory_code, l_locator_id, l_zone_id;
1896
1897 if c_act_wzone_wsub_woitem%NOTFOUND then
1898 if (l_debug =1 ) then
1899 trace(l_proc || ' "c_act_wzone_wsub_woitem" failed with %NOTFOUND...', 1);
1900 trace(l_proc || ' Setting OUT variables to null...', 1);
1901 trace(l_proc || ' Closing "active operations" cursor "' ||l_cursor||'"', 1);
1902 end if;
1903 x_locator_id := null;
1904 x_zone_id := null;
1905 x_subinventory_code := null;
1906 close c_act_wzone_wsub_woitem;
1907 l_cursor := null;
1908
1909 -- ### "active operations" cursor did not return any records, open the "completed operations" cursor
1910 -- ### Opening cursor c_comp_wzone_wsub_woitem to look for completed operations.
1911 -- ### Setting Cursor Name
1912 if (l_debug =1 ) then
1913 trace(l_proc || ' Opening "completed operations" cursor "' ||l_cursor||'"', 1);
1914 end if;
1915
1916 -- ### Check for Material grouping rule when opening the completed operations cursor.
1917 if l_loc_mtrl_grp_rule_id = 2 then
1918 l_cursor := 'c_comp_wzonesub_woitem_destsub';
1919 -- ### Open Cursor c_comp_wzone_wsub_woitem to look for "completed operation" plans.
1920 open c_comp_wzonesub_woitem_destsub;
1921 fetch c_comp_wzonesub_woitem_destsub
1922 into l_subinventory_code, l_locator_id, l_zone_id;
1923
1924 if c_comp_wzonesub_woitem_destsub%NOTFOUND then
1925 if (l_debug = 1) then
1926 trace(l_proc || ' "c_comp_wzonesub_woitem_destsub" failed with %NOTFOUND...', 1);
1927 trace(l_proc || ' Setting OUT variables to null...', 1);
1928 trace(l_proc || ' Closing "completed operations" cursor "' ||l_cursor||'"', 1);
1929 end if;
1930 x_locator_id := null;
1931 x_zone_id := null;
1932 x_subinventory_code := null;
1933 close c_comp_wzonesub_woitem_destsub;
1934 l_cursor := null;
1935 elsif c_comp_wzonesub_woitem_destsub%FOUND then
1936 -- c_comp_wzonesub_woitem_destsub cursor found.
1937 if (l_debug = 1) then
1938 trace(l_proc || ' "c_comp_wzonesub_woitem_destsub" FOUND...', 1);
1939 trace(l_proc || ' Closing cursor "' ||l_cursor||'"', 1);
1940 end if;
1941 close c_comp_wzonesub_woitem_destsub;
1942 l_cur_found := true;
1943 end if; -- @@@ Marker: c_comp_wzonesub_woitem_destsub FOUND/NOTFOUND
1944 elsif l_loc_mtrl_grp_rule_id <> 2 then -- Check for material grouping rule while opening Completed cursors......
1945 l_cursor := 'c_comp_wzone_wsub_woitem';
1946 -- ### Open Cursor c_comp_wzone_wsub_woitem to look for "completed operation" plans.
1947 open c_comp_wzone_wsub_woitem;
1948 fetch c_comp_wzone_wsub_woitem
1949 into l_subinventory_code, l_locator_id, l_zone_id;
1950
1951 if c_comp_wzone_wsub_woitem%NOTFOUND then
1952 if (l_debug = 1) then
1953 trace(l_proc || ' "c_comp_wzone_wsub_woitem" failed with %NOTFOUND...', 1);
1954 trace(l_proc || ' Setting OUT variables to null...', 1);
1955 trace(l_proc || ' Closing "completed operations" cursor "' ||l_cursor||'"', 1);
1956 end if;
1957 x_locator_id := null;
1958 x_zone_id := null;
1959 x_subinventory_code := null;
1960 close c_comp_wzone_wsub_woitem;
1961 l_cursor := null;
1965 trace(l_proc || ' "c_comp_wzone_wsub_woitem" FOUND...', 1);
1962 elsif c_comp_wzone_wsub_woitem%FOUND then
1963 -- c_comp_wzone_wsub_woitem cursor found.
1964 if (l_debug = 1) then
1966 trace(l_proc || ' Closing cursor "' ||l_cursor||'"', 1);
1967 end if;
1968 close c_comp_wzone_wsub_woitem;
1969 l_cur_found := true;
1970 end if; -- @@@ Marker: c_comp_wzone_wsub_woitem FOUND/NOTFOUND
1971 end if; -- @@@ Check for material grouping rule while opening Completed cursors......
1972
1973 if (l_debug =1 ) then
1974 trace(l_proc || ' Both main cursors failed to return any values...', 1);
1975 trace(l_proc || ' Plan "B": Opening Auxillary Cursors to find a location ID ', 4);
1976 end if;
1977 elsif c_act_wzone_wsub_woitem%FOUND then
1978 -- c_act_wzone_wsub_woitem cursor found.
1979 if (l_debug = 1) then
1980 trace(l_proc || ' "c_act_wzone_wsub_woitem" FOUND...', 1);
1981 trace(l_proc || ' Closing cursor "' ||l_cursor||'"', 1);
1982 end if;
1983 close c_act_wzone_wsub_woitem;
1984 l_cur_found := true;
1985 end if; -- @@@ Marker: c_act_wzone_wsub_woitem FOUND/NOTFOUND
1986 end if; -- @@@ Marker: Check for p_item_id when l_pre_specified_sub_code is not null.
1987 elsif l_pre_specified_sub_code is null then
1988 -- @@@ With Zone, Without Subinventory, With Item
1989 if p_item_id is not null then
1990 -- ### Setting Cursor Name.
1991 l_cursor := 'c_act_wzone_only_witem';
1992 if (l_debug =1 ) then
1993 trace(l_proc || ' Opening "active operations" cursor "' ||l_cursor||'"', 1);
1994 end if;
1995 -- ### Open Cursor c_act_wzone_only_witem to look for "active operation" plans.
1996 open c_act_wzone_only_witem;
1997 fetch c_act_wzone_only_witem
1998 into l_subinventory_code, l_locator_id, l_zone_id;
1999
2000 if c_act_wzone_only_witem%NOTFOUND then
2001 if (l_debug =1 ) then
2002 trace(l_proc || ' "c_act_wzone_only_witem" failed with %NOTFOUND...', 1);
2003 trace(l_proc || ' Setting OUT variables to null...', 1);
2004 trace(l_proc || ' Closing "active operations" cursor "' ||l_cursor||'"', 1);
2005 end if;
2006 x_locator_id := null;
2007 x_zone_id := null;
2008 x_subinventory_code := null;
2009 close c_act_wzone_only_witem;
2010 l_cursor := null;
2011
2012 -- ### "active operations" cursor did not return any records, open the "completed operations" cursor
2013 -- ### Opening cursor c_comp_wzone_only_witem to look for completed operations.
2014 -- ### Setting Cursor Name
2015 if (l_debug =1 ) then
2016 trace(l_proc || ' Opening "completed operations" cursor "' ||l_cursor||'"', 1);
2017 end if;
2018
2019 -- ### Check for Material grouping rule when opening the completed operations cursor.
2020 if l_loc_mtrl_grp_rule_id = 2 then
2021 l_cursor := 'c_comp_wzoneonlyitem_destsub';
2022 -- ### Open Cursor c_comp_wzone_only_witem to look for "completed operation" plans.
2023 open c_comp_wzoneonlyitem_destsub;
2024 fetch c_comp_wzoneonlyitem_destsub
2025 into l_subinventory_code, l_locator_id, l_zone_id;
2026
2027 if c_comp_wzoneonlyitem_destsub%NOTFOUND then
2028 if (l_debug = 1) then
2029 trace(l_proc || ' "c_comp_wzoneonlyitem_destsub" failed with %NOTFOUND...', 1);
2030 trace(l_proc || ' Setting OUT variables to null...', 1);
2031 trace(l_proc || ' Closing "completed operations" cursor "' ||l_cursor||'"', 1);
2032 end if;
2033 x_locator_id := null;
2034 x_zone_id := null;
2035 x_subinventory_code := null;
2036 close c_comp_wzoneonlyitem_destsub;
2037 l_cursor := null;
2038 elsif c_comp_wzoneonlyitem_destsub%FOUND then
2039 -- c_act_wzone_wsub_woitem cursor found.
2040 if (l_debug = 1) then
2041 trace(l_proc || ' "c_act_wzone_wsub_woitem" FOUND...', 1);
2042 trace(l_proc || ' Closing cursor "' ||l_cursor||'"', 1);
2043 end if;
2044 close c_comp_wzoneonlyitem_destsub;
2045 l_cur_found := true;
2046 end if; -- @@@ Marker: c_comp_wzoneonlyitem_destsub FOUND/NOTFOUND
2047 elsif l_loc_mtrl_grp_rule_id <> 2 then -- Check for material grouping rule while opening Completed cursors......
2048 l_cursor := 'c_comp_wzone_only_witem';
2049 -- ### Open Cursor c_comp_wzone_only_witem to look for "completed operation" plans.
2050 open c_comp_wzone_only_witem;
2051 fetch c_comp_wzone_only_witem
2052 into l_subinventory_code, l_locator_id, l_zone_id;
2053
2054 if c_comp_wzone_only_witem%NOTFOUND then
2055 if (l_debug = 1) then
2056 trace(l_proc || ' "c_comp_wzone_only_witem" failed with %NOTFOUND...', 1);
2057 trace(l_proc || ' Setting OUT variables to null...', 1);
2058 trace(l_proc || ' Closing "completed operations" cursor "' ||l_cursor||'"', 1);
2059 end if;
2060 x_locator_id := null;
2061 x_zone_id := null;
2062 x_subinventory_code := null;
2063 close c_comp_wzone_only_witem;
2064 l_cursor := null;
2065 elsif c_comp_wzone_only_witem%FOUND then
2066 -- c_comp_wzone_wsub_woitem cursor found.
2067 if (l_debug = 1) then
2068 trace(l_proc || ' "c_comp_wzone_only_witem" FOUND...', 1);
2069 trace(l_proc || ' Closing cursor "' ||l_cursor||'"', 1);
2070 end if;
2071 close c_comp_wzone_only_witem;
2072 l_cur_found := true;
2076 if (l_debug =1 ) then
2073 end if;-- @@@ Marker: c_comp_wzone_only_witem FOUND/NOTFOUND
2074 end if; -- @@@ Marker: Check for material grouping rule while opening Completed cursors.....
2075
2077 trace(l_proc || ' Both main cursors failed to return any values...', 1);
2078 trace(l_proc || ' Plan "B": Opening Auxillary Cursors to find a location ID ', 4);
2079 end if;
2080 elsif c_act_wzone_only_witem%FOUND then
2081 -- ### c_act_wzone_only_witem cursor found.
2082 if (l_debug = 1) then
2083 trace(l_proc || ' "c_act_wzone_only_witem" FOUND...', 1);
2084 trace(l_proc || ' Closing cursor "' ||l_cursor||'"', 1);
2085 end if;
2086 close c_act_wzone_only_witem;
2087 l_cur_found := true;
2088 end if;-- @@@ Marker: c_act_wzone_only_witem FOUND/NOTFOUND
2089 elsif p_item_id is null then
2090 -- @@@ With Zone, Without Subinventory, Without Item
2091 -- ### Setting Cursor Name.
2092 l_cursor := 'c_act_wzone_only_woitem';
2093 if (l_debug =1 ) then
2094 trace(l_proc || ' Opening "active operations" cursor "' ||l_cursor||'"', 1);
2095 end if;
2096 -- ### Open Cursor c_act_wzone_only_woitem to look for "active operation" plans.
2097 open c_act_wzone_only_woitem;
2098 fetch c_act_wzone_only_woitem
2099 into l_subinventory_code, l_locator_id, l_zone_id;
2100
2101 if c_act_wzone_only_woitem%NOTFOUND then
2102 if (l_debug =1 ) then
2103 trace(l_proc || ' "c_act_wzone_only_woitem" failed with %NOTFOUND...', 1);
2104 trace(l_proc || ' Setting OUT variables to null...', 1);
2105 trace(l_proc || ' Closing "active operations" cursor "' ||l_cursor||'"', 1);
2106 end if;
2107 x_locator_id := null;
2108 x_zone_id := null;
2109 x_subinventory_code := null;
2110 close c_act_wzone_only_woitem;
2111 l_cursor := null;
2112
2113 -- ### "active operations" cursor did not return any records, open the "completed operations" cursor
2114 -- ### Opening cursor c_comp_wzone_only_woitem to look for completed operations.
2115 -- ### Setting Cursor Name
2116 if (l_debug =1 ) then
2117 trace(l_proc || ' Opening "completed operations" cursor "' ||l_cursor||'"', 1);
2118 end if;
2119
2120 -- ### Check for Material grouping rule when opening the completed operations cursor.
2121 if l_loc_mtrl_grp_rule_id = 2 then
2122 l_cursor := 'c_comp_wzoneonlyitem_destsub';
2123 -- ### Open Cursor c_comp_wzoneonlywoitem_destsub to look for "completed operation" plans.
2124 open c_comp_wzoneonlywoitem_destsub;
2125 fetch c_comp_wzoneonlywoitem_destsub
2126 into l_subinventory_code, l_locator_id, l_zone_id;
2127
2128 if c_comp_wzoneonlywoitem_destsub%NOTFOUND then
2129 if (l_debug = 1) then
2130 trace(l_proc || ' "c_comp_wzoneonlywoitem_destsub" failed with %NOTFOUND...', 1);
2131 trace(l_proc || ' Setting OUT variables to null...', 1);
2132 trace(l_proc || ' Closing "completed operations" cursor "' ||l_cursor||'"', 1);
2133 end if;
2134 x_locator_id := null;
2135 x_zone_id := null;
2136 x_subinventory_code := null;
2137 close c_comp_wzoneonlywoitem_destsub;
2138 l_cursor := null;
2139 if (l_debug =1 ) then
2140 trace(l_proc || ' Both main cursors failed to return any values...', 1);
2141 trace(l_proc || ' Plan "B": Opening Auxillary Cursors to find a location ID ', 4);
2142 end if;
2143 elsif c_comp_wzoneonlywoitem_destsub%FOUND then
2144 -- ### c_comp_wzoneonlywoitem_destsub cursor found.
2145 if (l_debug = 1) then
2146 trace(l_proc || ' "c_comp_wzoneonlywoitem_destsub" FOUND...', 1);
2147 trace(l_proc || ' Closing cursor "' ||l_cursor||'"', 1);
2148 end if;
2149 close c_comp_wzoneonlywoitem_destsub;
2150 l_cur_found := true;
2151 end if;-- @@@ Marker: c_comp_wzoneonlywoitem_destsub FOUND/NOTFOUND
2152 elsif l_loc_mtrl_grp_rule_id <> 2 then
2153 l_cursor := 'c_comp_wzone_only_witem';
2154 -- ### Open Cursor c_comp_wzone_only_woitem to look for "completed operation" plans.
2155 open c_comp_wzone_only_woitem;
2156 fetch c_comp_wzone_only_woitem
2157 into l_subinventory_code, l_locator_id, l_zone_id;
2158
2159 if c_comp_wzone_only_woitem%NOTFOUND then
2160 if (l_debug = 1) then
2161 trace(l_proc || ' "c_comp_wzone_only_woitem" failed with %NOTFOUND...', 1);
2162 trace(l_proc || ' Setting OUT variables to null...', 1);
2163 trace(l_proc || ' Closing "completed operations" cursor "' ||l_cursor||'"', 1);
2164 end if;
2165 x_locator_id := null;
2166 x_zone_id := null;
2167 x_subinventory_code := null;
2168 close c_comp_wzone_only_woitem;
2169 l_cursor := null;
2170 if (l_debug =1 ) then
2171 trace(l_proc || ' Both main cursors failed to return any values...', 1);
2172 trace(l_proc || ' Plan "B": Opening Auxillary Cursors to find a location ID ', 4);
2173 end if;
2174 elsif c_comp_wzone_only_woitem%FOUND then
2175 -- ### c_comp_wzone_only_woitem cursor found.
2176 if (l_debug = 1) then
2177 trace(l_proc || ' "c_comp_wzone_only_woitem" FOUND...', 1);
2178 trace(l_proc || ' Closing cursor "' ||l_cursor||'"', 1);
2179 end if;
2180 close c_comp_wzone_only_woitem;
2181 l_cur_found := true;
2182 end if; -- @@@ Marker: c_comp_wzone_only_woitem FOUND/NOTFOUND
2186 if (l_debug = 1) then
2183 end if;-- @@@ Marker: Check l_loc_mtrl_grp_rule_id
2184 elsif c_act_wzone_only_woitem%FOUND then
2185 -- ### c_act_wzone_only_woitem cursor found.
2187 trace(l_proc || ' "c_act_wzone_only_woitem" FOUND...', 1);
2188 trace(l_proc || ' Closing cursor "' ||l_cursor||'"', 1);
2189 end if;
2190 close c_act_wzone_only_woitem;
2191 l_cur_found := true;
2192 end if; -- @@@ Marker: c_act_wzone_only_woitem FOUND/NOTFOUND
2193 end if; -- @@@ Marker: Check for p_item_id when l_pre_specified_sub_code is null.
2194 end if; -- @@@ Marker: Check for l_pre_specified_sub_code
2195 elsif l_pre_specified_zone_id is null then
2196 -- @@@ Zone Not specified, but Subiventory is specified. This is the ATF design wherein either the Zone
2197 -- @@@ or the Subinventory needs to be specified when the Operation Plan details are specified for a
2198 -- @@@ Drop Operation.
2199 -- @@@ Pending Cursors are being coded within separate if..then..end if enclosures while checking for
2200 -- @@@ pre-specified sub codes.
2201 if l_pre_specified_sub_code is not null then
2202
2203 IF p_item_id IS NULL THEN
2204 -- Added for bug 3393371
2205 l_cursor := 'c_subloc_pend_wozone_woitem';
2206 if (l_debug =1 ) then
2207 trace(l_proc || ' Within "l_pre_specified_zone_id is null" segment....', 1);
2208 trace(l_proc || ' Within "l_pre_specified_sub_code is not null" segment....', 1);
2209 trace(l_proc || ' Within "p_item_id is null" segment....', 1);
2210 trace(l_proc || ' Opening "Pending operations" cursor "' ||l_cursor||'"', 1);
2211 end if;
2212
2213 open c_subloc_pend_wozone_woitem;
2214 fetch c_subloc_pend_wozone_woitem
2215 into x_subinventory_code, x_locator_id;
2216
2217 if c_subloc_pend_wozone_woitem%NOTFOUND then
2218 if (l_debug =1 ) then
2219 trace(l_proc || ' "c_subloc_pend_wozone_woitem" failed with %NOTFOUND...', 1);
2220 trace(l_proc || ' Setting OUT variables to null...', 1);
2221 trace(l_proc || ' Closing "active operations" cursor "' ||l_cursor||'"', 1);
2222 end if;
2223 x_locator_id := null;
2224 x_zone_id := null;
2225 x_subinventory_code := null;
2226 close c_subloc_pend_wozone_woitem;
2227 l_cursor := null;
2228 elsif c_subloc_pend_wozone_woitem%FOUND then
2229 if (l_debug =1 ) then
2230 trace(l_proc || ' Found sub/loc from pending task within the same LPN.', 1);
2231 trace(l_proc || ' x_subinventory_code = '||x_subinventory_code, 1);
2232 trace(l_proc || ' x_locator_id = '||x_locator_id, 1);
2233 end if;
2234 close c_subloc_pend_wozone_woitem;
2235 --
2236 -- ### Call trace message before exiting...
2237 --
2238 exit_proc_msg(x_return_status => x_return_status
2239 , x_msg_count => x_msg_count
2240 , x_msg_data => x_msg_data
2241 , x_locator_id => x_locator_id
2242 , x_zone_id => x_zone_id
2243 , x_subinventory_code => x_subinventory_code
2244 , x_loc_valid => x_loc_valid
2245 , l_proc => l_proc);
2246 return;
2247 end if;-- Marker: c_subloc_pend_wozone_woitem FOUND/NOTFOUND
2248
2249 ELSE -- IF p_item_id IS NULL THEN
2250 l_cursor := 'c_subloc_pend_wozone_witem';
2251 if (l_debug =1 ) then
2252 trace(l_proc || ' Within "l_pre_specified_zone_id is null" segment....', 1);
2253 trace(l_proc || ' Within "l_pre_specified_sub_code is not null" segment....', 1);
2254 trace(l_proc || ' Opening "Pending operations" cursor "' ||l_cursor||'"', 1);
2255 end if;
2256
2257 open c_subloc_pend_wozone_witem;
2258 fetch c_subloc_pend_wozone_witem
2259 into x_subinventory_code, x_locator_id;
2260
2261 if c_subloc_pend_wozone_witem%NOTFOUND then
2262 if (l_debug =1 ) then
2263 trace(l_proc || ' "c_subloc_pend_wozone_witem" failed with %NOTFOUND...', 1);
2264 trace(l_proc || ' Setting OUT variables to null...', 1);
2265 trace(l_proc || ' Closing "active operations" cursor "' ||l_cursor||'"', 1);
2266 end if;
2267 x_locator_id := null;
2268 x_zone_id := null;
2269 x_subinventory_code := null;
2270 close c_subloc_pend_wozone_witem;
2271 l_cursor := null;
2272 elsif c_subloc_pend_wozone_witem%FOUND then
2273 if (l_debug =1 ) then
2274 trace(l_proc || ' Found sub/loc from pending task within the same LPN.', 1);
2275 trace(l_proc || ' x_subinventory_code = '||x_subinventory_code, 1);
2276 trace(l_proc || ' x_locator_id = '||x_locator_id, 1);
2277 end if;
2278 close c_subloc_pend_wozone_witem;
2279 --
2280 -- ### Call trace message before exiting...
2281 --
2282 exit_proc_msg(x_return_status => x_return_status
2283 , x_msg_count => x_msg_count
2284 , x_msg_data => x_msg_data
2285 , x_locator_id => x_locator_id
2286 , x_zone_id => x_zone_id
2287 , x_subinventory_code => x_subinventory_code
2288 , x_loc_valid => x_loc_valid
2289 , l_proc => l_proc);
2290 return;
2291 end if;-- Marker: c_subloc_pend_wozone_witem FOUND/NOTFOUND
2292 END IF; -- IF p_item_id IS NULL THEN
2293
2294 end if;-- Marker: Check l_pre_specified_sub_code
2295
2296 -- @@@ Without Zone, With Subinventory, With Item
2297 if p_item_id is not null then
2298 -- ### Setting Cursor Name.
2299 l_cursor := 'c_act_wozone_witem';
2300 if (l_debug =1 ) then
2301 trace(l_proc || ' Opening "active operations" cursor "' ||l_cursor||'"', 1);
2302 end if;
2303 -- ### Open Cursor c_act_wozone_witem to look for "active operation" plans.
2304 open c_act_wozone_witem;
2308 if c_act_wozone_witem%NOTFOUND then
2305 fetch c_act_wozone_witem
2306 into l_subinventory_code, l_locator_id;
2307
2309 if (l_debug =1 ) then
2310 trace(l_proc || ' "c_act_wozone_witem" failed with %NOTFOUND...', 1);
2311 trace(l_proc || ' Setting OUT variables to null...', 1);
2312 trace(l_proc || ' Closing "active operations" cursor "' ||l_cursor||'"', 1);
2313 end if;
2314 x_locator_id := null;
2315 x_zone_id := null;
2316 x_subinventory_code := null;
2317 close c_act_wozone_witem;
2318 l_cursor := null;
2319
2320 -- ### "active operations" cursor did not return any records, open the "completed operations" cursor
2321 -- ### Opening cursor c_comp_wozone_witem to look for completed operations.
2322 -- ### Setting Cursor Name
2323 if (l_debug =1 ) then
2324 trace(l_proc || ' Opening "completed operations" cursor "' ||l_cursor||'"', 1);
2325 end if;
2326
2327 -- ### Check for Material grouping rule when opening the completed operations cursor.
2328 if l_loc_mtrl_grp_rule_id = 2 then
2329 l_cursor := 'c_comp_wozone_witem_destsub';
2330 -- ### Open Cursor c_comp_wozone_witem to look for "completed operation" plans.
2331 open c_comp_wozone_witem_destsub;
2332 fetch c_comp_wozone_witem_destsub
2333 into l_subinventory_code, l_locator_id;
2334
2335 if c_comp_wozone_witem_destsub%NOTFOUND then
2336 if (l_debug = 1) then
2337 trace(l_proc || ' "c_comp_wozone_witem_destsub" failed with %NOTFOUND...', 1);
2338 trace(l_proc || ' Setting OUT variables to null...', 1);
2339 trace(l_proc || ' Closing "completed operations" cursor "' ||l_cursor||'"', 1);
2340 end if;
2341 x_locator_id := null;
2342 x_zone_id := null;
2343 x_subinventory_code := null;
2344 close c_comp_wozone_witem_destsub;
2345 l_cursor := null;
2346 elsif c_comp_wozone_witem_destsub%FOUND then
2347 -- ### c_comp_wozone_witem_destsub cursor found.
2348 if (l_debug = 1) then
2349 trace(l_proc || ' "c_comp_wozone_witem_destsub" FOUND...', 1);
2350 trace(l_proc || ' Closing cursor "' ||l_cursor||'"', 1);
2351 end if;
2352 close c_comp_wozone_witem_destsub;
2353 l_cur_found := true;
2354 end if;-- @@@ Marker: c_comp_wozone_witem_destsub FOUND/NOTFOUND
2355 elsif l_loc_mtrl_grp_rule_id <> 2 then
2356 l_cursor := 'c_comp_wozone_witem';
2357 -- ### Open Cursor c_comp_wozone_witem to look for "completed operation" plans.
2358 open c_comp_wozone_witem;
2359 fetch c_comp_wozone_witem
2360 into l_subinventory_code, l_locator_id;
2361
2362 if c_comp_wozone_witem%NOTFOUND then
2363 if (l_debug = 1) then
2364 trace(l_proc || ' "c_comp_wozone_witem" failed with %NOTFOUND...', 1);
2365 trace(l_proc || ' Setting OUT variables to null...', 1);
2366 trace(l_proc || ' Closing "completed operations" cursor "' ||l_cursor||'"', 1);
2367 end if;
2368 x_locator_id := null;
2369 x_zone_id := null;
2370 x_subinventory_code := null;
2371 close c_comp_wozone_witem;
2372 l_cursor := null;
2373 elsif c_comp_wozone_witem%FOUND then
2374 -- ### c_comp_wozone_witem cursor found.
2375 if (l_debug = 1) then
2376 trace(l_proc || ' "c_comp_wozone_witem" FOUND...', 1);
2377 trace(l_proc || ' Closing cursor "' ||l_cursor||'"', 1);
2378 end if;-- @@@ Marker: c_comp_wozone_witem FOUND/NOTFOUND
2379 close c_comp_wozone_witem;
2380 l_cur_found := true;
2381 end if;-- @@@ Marker: c_comp_wozone_witem FOUND/NOTFOUND
2382 end if;-- @@@ Marker: Check l_loc_mtrl_grp_rule_id
2383
2384 if (l_debug =1 ) then
2385 trace(l_proc || ' Both main cursors failed to return any values...', 1);
2386 trace(l_proc || ' Plan "B": Opening Auxillary Cursors to find a location ID ', 4);
2387 end if;
2388 elsif c_act_wozone_witem%FOUND then
2389 -- c_act_wozone_witem cursor found.
2390 if (l_debug = 1) then
2391 trace(l_proc || ' "c_act_wozone_witem" FOUND...', 1);
2392 trace(l_proc || ' Closing cursor "' ||l_cursor||'"', 1);
2393 end if;
2394 close c_act_wozone_witem;
2395 l_cur_found := true;
2396 end if;-- @@@ Marker: c_act_wozone_witem FOUND/NOTFOUND
2397 elsif p_item_id is null then
2398 -- @@@ Without Zone, With Subinventory, Without Item
2399 -- ### Setting Cursor Name.
2400 l_cursor := 'c_act_wozone_woitem';
2401 if (l_debug =1 ) then
2402 trace(l_proc || ' Opening "active operations" cursor "' ||l_cursor||'"', 1);
2403 end if;
2404 -- ### Open Cursor c_act_wozone_woitem to look for "active operation" plans.
2405 open c_act_wozone_woitem;
2406 fetch c_act_wozone_woitem
2407 into l_subinventory_code, l_locator_id;
2408
2409 if c_act_wozone_woitem%NOTFOUND then
2410 if (l_debug =1 ) then
2411 trace(l_proc || ' "c_act_wozone_woitem" failed with %NOTFOUND...', 1);
2412 trace(l_proc || ' Setting OUT variables to null...', 1);
2413 trace(l_proc || ' Closing "active operations" cursor "' ||l_cursor||'"', 1);
2414 end if;
2415 x_locator_id := null;
2416 x_zone_id := null;
2417 x_subinventory_code := null;
2418 close c_act_wozone_woitem;
2419 l_cursor := null;
2420
2421 -- ### "active operations" cursor did not return any records, open the "completed operations" cursor
2422 -- ### Opening cursor c_comp_wozone_woitem_destsub to look for completed operations.
2423 -- ### Setting Cursor Name
2424 if (l_debug =1 ) then
2425 trace(l_proc || ' Opening "completed operations" cursor "' ||l_cursor||'"', 1);
2426 end if;
2427
2428 -- ### Check for Material grouping rule when opening the completed operations cursor.
2429 if l_loc_mtrl_grp_rule_id = 2 then
2430 l_cursor := 'c_comp_wozone_woitem_destsub';
2431 -- ### Open Cursor c_comp_wozone_witem to look for "completed operation" plans.
2432 open c_comp_wozone_woitem_destsub;
2433 fetch c_comp_wozone_woitem_destsub
2434 into l_subinventory_code, l_locator_id;
2435
2436 if c_comp_wozone_woitem_destsub%NOTFOUND then
2437 if (l_debug = 1) then
2438 trace(l_proc || ' "c_comp_wozone_woitem_destsub" failed with %NOTFOUND...', 1);
2439 trace(l_proc || ' Setting OUT variables to null...', 1);
2440 trace(l_proc || ' Closing "completed operations" cursor "' ||l_cursor||'"', 1);
2441 end if;
2442 x_locator_id := null;
2443 x_zone_id := null;
2444 x_subinventory_code := null;
2445 close c_comp_wozone_woitem_destsub;
2446 l_cursor := null;
2447 elsif c_comp_wozone_woitem_destsub%FOUND then
2448 -- ### c_comp_wozone_woitem_destsub cursor found.
2449 if (l_debug = 1) then
2450 trace(l_proc || ' "c_comp_wozone_woitem_destsub" FOUND...', 1);
2451 trace(l_proc || ' Closing cursor "' ||l_cursor||'"', 1);
2452 end if;
2453 close c_comp_wozone_woitem_destsub;
2454 l_cursor := null;
2455 end if;-- @@@ Marker: c_comp_wozone_woitem_destsub FOUND/NOTFOUND
2456 elsif l_loc_mtrl_grp_rule_id <> 2 then
2457 l_cursor := 'c_comp_wozone_woitem';
2458 -- ### Open Cursor c_comp_wozone_woitem to look for "completed operation" plans.
2459 open c_comp_wozone_woitem;
2460 fetch c_comp_wozone_woitem
2461 into l_subinventory_code, l_locator_id;
2462
2463 if c_comp_wozone_woitem%NOTFOUND then
2464 if (l_debug = 1) then
2465 trace(l_proc || ' "c_comp_wozone_woitem" failed with %NOTFOUND...', 1);
2466 trace(l_proc || ' Setting OUT variables to null...', 1);
2467 trace(l_proc || ' Closing "completed operations" cursor "' ||l_cursor||'"', 1);
2468 end if;
2469 x_locator_id := null;
2470 x_zone_id := null;
2471 x_subinventory_code := null;
2472 close c_comp_wozone_woitem;
2473 l_cursor := null;
2474 elsif c_comp_wozone_woitem%FOUND then
2475 -- ### c_comp_wozone_woitem cursor found.
2476 if (l_debug = 1) then
2477 trace(l_proc || ' "c_comp_wozone_woitem" FOUND...', 1);
2478 trace(l_proc || ' Closing cursor "' ||l_cursor||'"', 1);
2479 end if;-- @@@ Marker:
2480 close c_comp_wozone_woitem;
2481 l_cur_found := true;
2482 end if;-- @@@ Marker: c_comp_wozone_woitem FOUND/NOTFOUND
2483 end if;-- @@@ Marker: Check l_loc_mtrl_grp_rule_id
2484 elsif c_act_wozone_woitem%FOUND then
2485 -- c_act_wozone_woitem cursor found.
2486 if (l_debug = 1) then
2487 trace(l_proc || ' "c_act_wozone_woitem" FOUND...', 1);
2488 trace(l_proc || ' Closing cursor "' ||l_cursor||'"', 1);
2489 end if;
2490 close c_act_wozone_woitem;
2491 l_cur_found := true;
2492 end if; -- @@@ Marker: c_act_wozone_woitem FOUND/NOTFOUND
2493 end if; -- @@@ Marker: Check for p_item_id when l_pre_specified_sub_code is null.
2494 end if; -- @@@ Marker: Check for l_pre_specified_zone_id.
2495
2496
2497 -- ### Now that a valid loc/sub/zone was not found using both the main cursors. Checking out the with the auxillary
2498 -- ### cursors. l_cur_found is initially set to 'false' and set to 'true' when any of the cursors return a location,
2499 -- ### sub and zone. Hence checking the 'not' so that this part of the code will be traversed only if a location was
2500 -- ### not returned by the acive or completed cursors.
2501 if (l_debug =1 ) then
2502 trace(l_proc || ' Commencing Plan "B"', 1);
2503 trace(l_proc || ' Opening Auxillary cursors to derive based on prespecified Zone and/or Sub, if available..', 1);
2504 end if;
2505
2506 if not l_cur_found then
2507 if l_pre_specified_zone_id is not null then
2508 if l_pre_specified_sub_code is not null then
2509 -- @@@ With Zone, With Subinventory
2510 l_cursor := 'c_aux_wzone_wsub';
2511 if (l_debug =1 ) then
2512 trace(l_proc || ' Opening "c_aux_wzone_wsub" cursor "' ||l_cursor||'"', 1);
2513 end if;
2514
2515 -- ### Open Cursor c_aux_wzone_wsub to look for "active operation" plans.
2516 open c_aux_wzone_wsub;
2517 fetch c_aux_wzone_wsub
2518 into l_locator_id, l_subinventory_code, l_zone_id;
2519
2520 if c_aux_wzone_wsub%NOTFOUND then
2521 if (l_debug = 1) then
2522 trace(l_proc || ' "c_aux_wzone_wsub" failed with %NOTFOUND...', 1);
2523 trace(l_proc || ' Setting OUT variables to null...', 1);
2524 trace(l_proc || ' Closing Auxillary cursor "' ||l_cursor||'"', 1);
2525 end if;
2526 x_locator_id := null;
2527 x_zone_id := null;
2528 x_subinventory_code := null;
2529 close c_aux_wzone_wsub;
2530 l_cursor := null;
2531 elsif c_aux_wzone_wsub%FOUND then
2532 -- ### c_aux_wzone_wsub cursor found.
2533 if (l_debug = 1) then
2534 trace(l_proc || ' "c_aux_wzone_wsub" FOUND...', 1);
2535 trace(l_proc || ' Closing Auxillary cursor "' ||l_cursor||'"', 1);
2536 end if;
2537 close c_aux_wzone_wsub;
2538 l_cur_found := true;
2539 end if;-- @@@ Marker: c_aux_wzone_wsub FOUND/NOTFOUND
2540 elsif l_pre_specified_sub_code is null then
2541 -- @@@ With Zone Only
2542 l_cursor := 'c_aux_wzone_only';
2543 if (l_debug =1 ) then
2544 trace(l_proc || ' Opening "c_aux_wzone_only" cursor "' ||l_cursor||'"', 1);
2545 end if;
2546
2547 -- ### Open Cursor c_aux_wzone_only to look for "active operation" plans.
2548 open c_aux_wzone_only;
2549 fetch c_aux_wzone_only
2550 into l_locator_id, l_subinventory_code, l_zone_id;
2551
2552 if c_aux_wzone_only%NOTFOUND then
2553 if (l_debug = 1) then
2554 trace(l_proc || ' "c_aux_wzone_only" failed with %NOTFOUND...', 1);
2555 trace(l_proc || ' Setting OUT variables to null...', 1);
2556 trace(l_proc || ' Closing Auxillary cursor "' ||l_cursor||'"', 1);
2557 end if;
2558 x_locator_id := null;
2559 x_zone_id := null;
2560 x_subinventory_code := null;
2561 close c_aux_wzone_only;
2562 l_cursor := null;
2563 elsif c_aux_wzone_only%FOUND then
2564 -- ### c_aux_wzone_only cursor found.
2565 if (l_debug = 1) then
2566 trace(l_proc || ' "c_aux_wzone_only" FOUND...', 1);
2567 trace(l_proc || ' Closing Auxillary cursor "' ||l_cursor||'"', 1);
2568 end if;
2569 close c_aux_wzone_only;
2570 l_cur_found := true;
2571 end if;-- @@@ Marker: c_aux_wzone_only FOUND/NOTFOUND
2572 end if; -- @@@ Marker: Check l_pre_specified_sub_code
2573 elsif l_pre_specified_zone_id is null then
2574 if l_pre_specified_sub_code is not null then
2575 -- @@@ With Subinventory Only
2576 l_cursor := 'c_aux_wsub_only';
2577 if (l_debug =1 ) then
2578 trace(l_proc || ' Opening "c_aux_wsub_only" cursor "' ||l_cursor||'"', 1);
2579 end if;
2580
2581 -- ### Open Cursor c_aux_wsub_only to look for "active operation" plans.
2582 open c_aux_wsub_only;
2583 fetch c_aux_wsub_only
2584 into l_locator_id, l_subinventory_code;
2585
2586 if c_aux_wsub_only%NOTFOUND then
2587 if (l_debug = 1) then
2588 trace(l_proc || ' "c_aux_wsub_only" failed with %NOTFOUND...', 1);
2589 trace(l_proc || ' Setting OUT variables to null...', 1);
2590 trace(l_proc || ' Closing Auxillary cursor "' ||l_cursor||'"', 1);
2591 end if;
2592 x_locator_id := null;
2593 x_zone_id := null;
2594 x_subinventory_code := null;
2595 close c_aux_wsub_only;
2596 l_cursor := null;
2597 elsif c_aux_wsub_only%FOUND then
2598 -- ### c_aux_wsub_only cursor found.
2599 if (l_debug = 1) then
2600 trace(l_proc || ' "c_aux_wsub_only" FOUND...', 1);
2601 trace(l_proc || ' Closing Auxillary cursor "' ||l_cursor||'"', 1);
2602 end if;
2603 close c_aux_wsub_only;
2604 l_cur_found := true;
2605 end if;-- @@@ Marker: c_aux_wsub_only FOUND/NOTFOUND
2606 end if;-- @@@ Marker: Check l_pre_specified_zone_id
2607 end if;-- @@@ Marker: Check l_pre_specified_zone_id
2608 end if;-- @@@ Marker: not l_cur_found
2609
2610
2611 -- ### Common code to set out values if either the "active" or "complete" "with Zone cursor is FOUND.
2612 -- ### This is achieved by setting the value of the boolean "l_cur_found_wzone" appropriately.
2613 -- ### "l_cur_found_wzone" is set to true if either the "c_subloczone_act_wzone" or "c_subloczone_comp_wzone"
2614 -- ### cursors are found.
2615 if l_cur_found then
2616 if (l_debug =1 ) then
2617 trace(l_proc || 'Within "if l_cur_found_wzone " is entered...', 1);
2618 trace(l_proc || l_cursor || ' FOUND...', 1);
2619 trace(l_proc || ' Subinventory Code returned by cursor ' || l_cursor || ' => ' || l_subinventory_code, 4);
2620 trace(l_proc || ' Location ID returned by cursor ' || l_cursor || ' => ' || l_locator_id, 4);
2621 trace(l_proc || ' Zone ID returned by cursor ' || l_cursor || ' => ' || nvl(l_zone_id, -99), 4);
2622 end if;
2623
2624 -- ### 'W' means Warning. Though the locator may belong to the prespecified sub and zone, it may not be the
2625 -- ### optimal location since it was not returned by neither the active nor the completed plan cursors.
2626 -- ### DLD says 'Locator could not be validated, but it is in the same zone/sub'.
2627 -- ### 'E' means Error. Locator does not belong to the prespecified sub and zone and hence return a status
2628 -- ### of 'Error'.
2629 if (p_mode = 2 and l_cursor not in ('c_aux_wsub_only', 'c_aux_wzone_only', 'c_aux_wzone_wsub')) then
2630 x_loc_valid := 'Y';
2631 elsif l_cursor in ('c_aux_wsub_only', 'c_aux_wzone_only', 'c_aux_wzone_wsub') then
2632 x_loc_valid := 'W';
2633 end if;
2634
2635 x_locator_id := l_locator_id;
2636 x_zone_id := l_zone_id;
2637 x_subinventory_code := l_subinventory_code;
2638 --
2639 -- ### Call trace message before exiting...
2640 --
2641 exit_proc_msg(
2642 x_return_status => x_return_status
2643 , x_msg_count => x_msg_count
2644 , x_msg_data => x_msg_data
2645 , x_locator_id => x_locator_id
2646 , x_zone_id => x_zone_id
2647 , x_subinventory_code => x_subinventory_code
2648 , x_loc_valid => x_loc_valid
2649 , l_proc => l_proc);
2650 l_cursor := null;
2651 return;
2652 else
2653 -- @@@ Fix for bug 3583898. At this point, none of the cursors are found and hence the l_cur_found is not true.
2654 -- @@@
2655 if (l_debug =1 ) then
2656 trace(l_proc || 'l_cursor is => '|| nvl(l_cursor, '@@@@'), 1);
2657 end if;
2658
2659 if (p_mode = 2 and l_cursor is null) then
2660 x_loc_valid := 'E';
2661 end if;
2662 end if;
2663
2664 exception
2665 when fnd_api.g_exc_error then
2666 x_return_status := fnd_api.g_ret_sts_error;
2667
2668 if (l_debug = 1) then
2669 trace(' Progress at the time of failure is ' || l_prog, 1);
2670 trace(' Error Code, Error Message...' || sqlerrm(sqlcode), 1);
2671 end if;
2672
2673 if (l_prog = 10) then
2674 if (l_debug = 1) then
2675 trace(l_proc || ' "c_oper_plan_details" failed with %NOTFOUND...', 1);
2676 trace(l_proc || ' Task ID '|| p_task_id|| ' is invalid. Please pass a Valid Task.');
2677 end if;
2678 end if;
2679
2680 if (l_prog = 11) then
2681 if (l_debug = 1) then
2682 trace(' Material Grouping Rule not stamped on the Operation Plan Detail line.');
2683 trace(' Unable to proceed. Aborting execution...');
2684 end if;
2685 end if;
2686
2687 if c_oper_plan_details%isopen then close c_oper_plan_details; end if;
2688 if c_subloc_pend_wozone_woitem%isopen then close c_subloc_pend_wozone_woitem; end if;
2689 if c_subloc_pend_wzn_wosub_woitem%isopen then close c_subloc_pend_wzn_wosub_woitem; end if;
2690 if c_subloc_pend_wzn_wsub_woitem%isopen then close c_subloc_pend_wzn_wsub_woitem; end if;
2691 if c_subloc_pend_wozone_witem%isopen then close c_subloc_pend_wozone_witem; end if;
2692 if c_subloc_pend_wzn_wosub_witem%isopen then close c_subloc_pend_wzn_wosub_witem; end if;
2693 if c_subloc_pend_wzn_wsub_witem%isopen then close c_subloc_pend_wzn_wsub_witem; end if;
2694
2695 if c_act_wzone_wsub_witem%ISOPEN then close c_act_wzone_wsub_witem; end if;
2696 if c_act_wzone_wsub_woitem%isopen then close c_act_wzone_wsub_woitem; end if;
2697 if c_act_wzone_only_witem%isopen then close c_act_wzone_only_witem; end if;
2698 if c_act_wzone_only_woitem%isopen then close c_act_wzone_only_woitem; end if;
2699 if c_act_wozone_witem%isopen then close c_act_wozone_witem; end if;
2700 if c_act_wozone_woitem%isopen then close c_act_wozone_woitem; end if;
2701
2702 if c_comp_wzone_wsub_witem%ISOPEN then close c_comp_wzone_wsub_witem; end if;
2703 if c_comp_wzone_wsub_woitem%isopen then close c_comp_wzone_wsub_woitem; end if;
2704 if c_comp_wzone_only_witem%isopen then close c_comp_wzone_only_witem; end if;
2705 if c_comp_wzone_only_woitem%isopen then close c_comp_wzone_only_woitem; end if;
2706 if c_comp_wozone_witem%isopen then close c_comp_wozone_witem; end if;
2707 if c_comp_wozone_woitem%isopen then close c_comp_wozone_woitem; end if;
2708
2709 if c_comp_wzonesubitem_destsub%ISOPEN then close c_comp_wzonesubitem_destsub; end if;
2710 if c_comp_wzonesub_woitem_destsub%isopen then close c_comp_wzonesub_woitem_destsub; end if;
2711 if c_comp_wzoneonlyitem_destsub%isopen then close c_comp_wzoneonlyitem_destsub; end if;
2712 if c_comp_wzoneonlywoitem_destsub%isopen then close c_comp_wzoneonlywoitem_destsub; end if;
2713 if c_comp_wozone_witem_destsub%isopen then close c_comp_wozone_witem_destsub; end if;
2714 if c_comp_wozone_woitem_destsub%isopen then close c_comp_wozone_woitem_destsub; end if;
2715
2716 --
2717 --### Call trace message before exiting...
2718 --
2719 exit_proc_msg(
2720 x_return_status => x_return_status
2721 , x_msg_count => x_msg_count
2722 , x_msg_data => x_msg_data
2723 , x_locator_id => x_locator_id
2724 , x_zone_id => x_zone_id
2725 , x_subinventory_code => x_subinventory_code
2726 , x_loc_valid => x_loc_valid
2727 , l_proc => l_proc);
2728
2729 when others then
2730 x_return_status := fnd_api.g_ret_sts_error;
2731 if (l_debug = 1) then
2732 trace(' Progress at the time of failure is ' || l_prog, 1);
2733 trace(' Error Code, Error Message...' || sqlerrm(sqlcode), 1);
2734 end if;
2735
2736 if c_oper_plan_details%isopen then close c_oper_plan_details; end if;
2737 if c_subloc_pend_wozone_woitem%isopen then close c_subloc_pend_wozone_woitem; end if;
2738 if c_subloc_pend_wzn_wosub_woitem%isopen then close c_subloc_pend_wzn_wosub_woitem; end if;
2739 if c_subloc_pend_wzn_wsub_woitem%isopen then close c_subloc_pend_wzn_wsub_woitem; end if;
2740 if c_subloc_pend_wozone_witem%isopen then close c_subloc_pend_wozone_witem; end if;
2741 if c_subloc_pend_wzn_wosub_witem%isopen then close c_subloc_pend_wzn_wosub_witem; end if;
2742 if c_subloc_pend_wzn_wsub_witem%isopen then close c_subloc_pend_wzn_wsub_witem; end if;
2743
2744 if c_act_wzone_wsub_witem%isopen then close c_act_wzone_wsub_witem; end if;
2745 if c_act_wzone_wsub_woitem%isopen then close c_act_wzone_wsub_woitem; end if;
2746 if c_act_wzone_only_witem%isopen then close c_act_wzone_only_witem; end if;
2747 if c_act_wzone_only_woitem%isopen then close c_act_wzone_only_woitem; end if;
2748 if c_act_wozone_witem%isopen then close c_act_wozone_witem; end if;
2749 if c_act_wozone_woitem%isopen then close c_act_wozone_woitem; end if;
2750
2751 if c_comp_wzone_wsub_witem%isopen then close c_comp_wzone_wsub_witem; end if;
2752 if c_comp_wzone_wsub_woitem%isopen then close c_comp_wzone_wsub_woitem; end if;
2753 if c_comp_wzone_only_witem%isopen then close c_comp_wzone_only_witem; end if;
2754 if c_comp_wzone_only_woitem%isopen then close c_comp_wzone_only_woitem; end if;
2755 if c_comp_wozone_witem%isopen then close c_comp_wozone_witem; end if;
2756 if c_comp_wozone_woitem%isopen then close c_comp_wozone_woitem; end if;
2757
2758 if c_comp_wzonesubitem_destsub%isopen then close c_comp_wzonesubitem_destsub; end if;
2759 if c_comp_wzonesub_woitem_destsub%isopen then close c_comp_wzonesub_woitem_destsub; end if;
2760 if c_comp_wzoneonlyitem_destsub%isopen then close c_comp_wzoneonlyitem_destsub; end if;
2761 if c_comp_wzoneonlywoitem_destsub%isopen then close c_comp_wzoneonlywoitem_destsub; end if;
2762 if c_comp_wozone_witem_destsub%isopen then close c_comp_wozone_witem_destsub; end if;
2763 if c_comp_wozone_woitem_destsub%isopen then close c_comp_wozone_woitem_destsub; end if;
2764
2765 --
2766 --### Call trace message before exiting...
2767 --
2768 exit_proc_msg(
2769 x_return_status => x_return_status
2770 , x_msg_count => x_msg_count
2771 , x_msg_data => x_msg_data
2772 , x_locator_id => x_locator_id
2773 , x_zone_id => x_zone_id
2774 , x_subinventory_code => x_subinventory_code
2775 , x_loc_valid => x_loc_valid
2776 , l_proc => l_proc);
2777
2778 end get_seed_dest_loc;
2779
2780 end wms_atf_dest_loc;
[Home] [Help]