1 package body wms_atf_destination_lpn as
2 /* $Header: WMSADLPB.pls 115.25 2004/03/25 00:44:25 joabraha noship $ */
3 --
4 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
5 --
6 -- ------------------------------------------------------------------------------------
7 -- |---------------------< trace >-----------------------------------------------------|
8 -- ------------------------------------------------------------------------------------
9 -- {Start Of Comments}
10 --
11 -- Description:
12 -- Wrapper around the tracing utility.
13 --
14 -- Prerequisites:
15 -- None
16 --
17 -- In Parameters:
18 -- Name Reqd Type Description
19 -- ---------- ---- -------- ---------------------------------------
20 -- p_message Yes varchar2 Message to be displayed in the log file.
21 -- p_prompt Yes varchar2 Prompt.
22 -- p_level No number Level.
23 --
24 -- Post Success:
25 -- None.
26 --
27 -- Post Failure:
28 -- None
29 --
30 -- Access Status:
31 -- Internal Development Use Only.
32 --
33 -- {End Of Comments}
34 --
35 Procedure trace(
36 p_message in varchar2
37 , p_level in number
38 ) is
39 begin
40 INV_LOG_UTIL.trace(p_message, 'WMS_ATF_DESTINATION_LPN :', p_level);
41 end trace;
42 --
43 -- ---------------------------------------------------------------------------------------
44 -- |---------------------< exit_proc_msg >--------------------------------------------------------|
45 -- ---------------------------------------------------------------------------------------
46 -- {Start Of Comments}
47 --
48 -- Description:
49 -- Wrapper around the tracing utility.
50 --
51 -- Prerequisites:
52 -- None
53 --
54 -- In Parameters:
55 -- Name Reqd Type Description
56 -- p_message Yes varchar2 Message to be displayed in the log file.
57 -- p_prompt Yes varchar2 Prompt.
58 -- p_level No number Level.
59 --
60 -- Post Success:
61 -- None.
62 --
63 -- Post Failure:
64 -- None
65 --
66 -- Access Status:
67 -- Internal Development Use Only.
68 --
69 -- {End Of Comments}
70
71 Procedure exit_proc_msg(
72 x_return_status in varchar2
73 , x_msg_count in number
74 , x_msg_data in varchar2
75 , x_lpn_id in number
76 , x_lpn_valid in varchar2
77 , l_proc in varchar2
78 ) is
79 begin
80 if (l_debug = 1) then
81 trace(' Exiting Procedure '|| l_proc || ':'|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
82 trace(l_proc || ' x_return_status => ' || x_return_status);
83 trace(l_proc || ' x_msg_count => ' || x_msg_count);
84 trace(l_proc || ' x_msg_data => ' || x_msg_data);
85 trace(l_proc || ' x_lpn_id => ' || x_lpn_id);
86 trace(l_proc || ' x_lpn_valid => ' || x_lpn_valid);
87 end if;
88 end exit_proc_msg;
89 --
90 -- ------------------------------------------------------------------------------------
91 -- |----------------------------< get_seed_dest_lpn >----------------------------------|
92 -- ------------------------------------------------------------------------------------
93 -- {Start Of Comments}
94 --
95 -- Description:
96 -- Returns locator based on the specific conditions.
97 --
98 -- Package-Procedure combination
99 --
100 -- Prerequisites:
101 --
102 --
103 --
104 -- In Parameters:
105 -- Name Reqd Type Description
106 --- -------------------------- ---- -------- ----------------------------------
107 -- p_mode Yes varchar2 Valid Modes are Insert and Delete.
108 -- p_task_id Yes varchar2 MMTT.transaction_temp_id
109 -- p_activity_type_id No varchar2 1. Inbound 2. Outbound
110 -- p_lpn_id No LPN passed in for validation purposes.
111 -- p_item_id No Item ID passed in to use as added restriction.
112 --
113 -- Post Success:
114 --
115 --
116 -- Post Failure:
117 -- Details of the error are added to the AOL message stack. When this
118 --
119 -- Access Status:
120 -- Internal Development Use Only.
121 --
122 -- {End Of Comments}
123 --
124 Procedure get_seed_dest_lpn (
125 x_return_status out nocopy varchar2
126 , x_msg_count out nocopy number
127 , x_msg_data out nocopy varchar2
128 , x_lpn_id out nocopy number
129 , x_lpn_valid out nocopy varchar2
130 , p_mode in number
131 , p_task_id in number
135 , p_subinventory_code in varchar2
132 , p_activity_type_id in number
133 , p_lpn_id in number
134 , p_item_id in number
136 , p_locator_id in number
137 , p_api_version in number
138 , p_init_msg_list in varchar2
139 , p_commit in varchar2
140 ) is
141
142 l_proc varchar2(72) := 'GET_SEED_DEST_LPN :';
143 l_prog float;
144 l_loop_num number := 0;
145
146 l_operation_plan_id number;
147 l_operation_plan_dtl_id number;
148 l_plan_type_id number;
149 l_activity_type_id number;
153 l_operation_type number;
150 l_pre_specified_zone_id number;
151 l_pre_specified_sub_code varchar2(100);
152 l_lpn_mtrl_grp_rule_id number;
154
155 l_lpn_id number;
156 l_subinventory_code varchar2(100);
157 l_locator_id number;
158
159 l_is_in_inventory varchar2(1);
160 l_inventory_location_id number;
161 l_subinventory_type varchar2(100);
162
163 l_orig_dest_sub_code varchar2(100);
164 l_orig_dest_loc_id number;
165
166 l_organization_id number;
167 l_zone_id number;
168
169 --p_init_msg_list varchar2(50):= 'TRUE';
170 l_cursor varchar2(50):= null;
171 l_cur_found boolean := false;
172
173 cursor c_oper_plan_details is
174 select mmtt.operation_plan_id,
175 nvl(mmtt.transfer_to_location, mmtt.locator_id),
176 nvl(mmtt.transfer_subinventory, mmtt.subinventory_code),
177 wopd.operation_plan_detail_id,
178 wopi.activity_type_id,
179 wopi.plan_type_id,
180 wopi.orig_dest_sub_code,
181 wopi.orig_dest_loc_id,
182 wopd.pre_specified_zone_id,
183 wopd.pre_specified_sub_code,
184 wopd.lpn_mtrl_grp_rule_id,
185 wopd.operation_type,
186 nvl(wopd.is_in_inventory, 'N'),
187 mmtt.organization_id
188 from wms_op_plan_instances wopi, -- after review on 07/30/03, replaced wms_op_plans_b table with wms_op_plan_instances.
189 wms_op_plan_details wopd,
190 -- wms_zones_b wzb, -- Removed after Code Review on Sept 11th 2003.
191 mtl_material_transactions_temp mmtt,
192 wms_op_operation_instances wooi -- Added after review on 07/30/03
193 where mmtt.organization_id = wopi.organization_id
194 -- @@@ Commented after Code Review on Sept 11th 2003.
195 -- and wzb.zone_id(+) = wopd.pre_specified_zone_id
196 and wopd.operation_plan_detail_id = wooi.operation_plan_detail_id -- Added after review on 07/30/03
197 -- @@@ Commented after Code Review on Sept 16th 2003.
198 -- and wopd.operation_plan_id = wopi.operation_plan_id
199 and wopi.op_plan_instance_id = wooi.op_plan_instance_id
200 and wopi.operation_plan_id = mmtt.operation_plan_id
201 and wooi.source_task_id = mmtt.transaction_temp_id -- Added after review on 07/30/03
202 and mmtt.transaction_temp_id = p_task_id -- 6583491 (dmfdv11i)
203 --
207 -- @@@ athis API will abort if the Material Grouping Rule ID stamped on the detail line is null. There will exist no
204 -- @@@ Commented after for bug fix on Sept 16th 2003. One task is tied to a combination of a Load and a Drop. This means
205 -- @@@ when you query the wooi with the restriction " wooi.source_task_id = mmtt.transaction_temp_id ", it'll bring back
206 -- @@@ 2 records, 1 each for a load and drop. We are only interested in the Drop. This is all the more important becase
208 -- @@@ Material Grouping Rule ID for the Load Operation Plan Detail line. We also know that the operation sequence for the
209 -- @@@ Drop Line is always greater than the Load line and hence the " order by wooi.operation_sequence desc" will bring
210 -- @@@ back the Drop line first and then the Load line. In any case we only consider the first recoerd and in this case now
211 -- @@@ it turns out to be the Drop line.
212 -- order by wopd.operation_type;
213 order by wooi.operation_sequence desc;
214 --
215 --
216 --
217 cursor c_lpn_active_wzone_witem is
218 select wlpn.outermost_lpn_id
219 from wms_license_plate_numbers wlpn,
220 mtl_material_transactions_temp mmtt,
221 wms_zone_locators wzl,
222 wms_op_plan_instances wopi,
223 wms_op_operation_instances wooi,
224 wms_op_operation_instances wooi2
225 where wlpn.lpn_context = decode(l_is_in_inventory, 'Y', 1, 3)
226 and wlpn.organization_id = l_organization_id
227 and wlpn.subinventory_code = p_subinventory_code -- new
228 and wlpn.locator_id = p_locator_id -- new
229 and wlpn.lpn_id = mmtt.lpn_id
230 and wzl.zone_id = l_pre_specified_zone_id
231 and wzl.subinventory_code = wooi.from_subinventory_code
232 and wzl.subinventory_code = p_subinventory_code -- new
233 and wzl.organization_id = l_organization_id -- new
234 and (wzl.entire_sub_flag = 'Y' or wzl.inventory_location_id = wooi.from_locator_id)
235 -- *****from the inner cursor****
236 and wopi.status = 6
237 and wopi.op_plan_instance_id = wooi.op_plan_instance_id
238 and wooi2.operation_status = 3 -- Completed -- Typo corrected, earlier it was wooi.operation_status = 3
239 and wooi2.operation_type_id = 2 -- Drop
240 and wooi2.op_plan_instance_id = wopi.op_plan_instance_id
241 and wooi.from_subinventory_code = p_subinventory_code -- new
242 and wooi.from_locator_id = p_locator_id -- new
243 and wooi.organization_id = l_organization_id -- new
244 and wooi.source_task_id = mmtt.transaction_temp_id -- new
245 and mmtt.inventory_item_id = p_item_id -- new
246 and mmtt.organization_id = l_organization_id -- new
247 and (wooi.operation_status = 1 and wooi.operation_type_id <> 2)
248 and wopi.activity_type_id = l_activity_type_id -- 1 (Inbound)
249 and wopi.organization_id = l_organization_id -- new
250 and wopi.plan_type_id = decode(l_lpn_mtrl_grp_rule_id,1,l_plan_type_id,wopi.plan_type_id)
251 and wopi.orig_dest_sub_code = decode(l_lpn_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopi.orig_dest_sub_code )
252 and wopi.orig_dest_loc_id = decode(l_lpn_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopi.orig_dest_loc_id)
253 order by wooi.last_update_date desc;
254 --
255 --
256 cursor c_lpn_active_wzone_woitem is
257 select wlpn.outermost_lpn_id
258 from mtl_material_transactions_temp mmtt,
259 wms_license_plate_numbers wlpn,
260 wms_zone_locators wzl,
261 wms_op_plan_instances wopi,
262 wms_op_operation_instances wooi,
263 wms_op_operation_instances wooi2
264 where wlpn.lpn_context = decode(l_is_in_inventory, 'Y', 1, 3)
265 and wlpn.organization_id = l_organization_id
266 and wlpn.subinventory_code = p_subinventory_code -- new
267 and wlpn.locator_id = p_locator_id -- new
268 and wlpn.lpn_id = mmtt.lpn_id
269 and wzl.zone_id = l_pre_specified_zone_id
270 and wzl.subinventory_code = wooi.from_subinventory_code
271 and wzl.subinventory_code = p_subinventory_code -- new
272 and wzl.organization_id = l_organization_id -- new
273 and (wzl.entire_sub_flag = 'Y' or wzl.inventory_location_id = wooi.from_locator_id)
274 -- *****from the inner cursor****
275 and wopi.status = 6
276 and wopi.op_plan_instance_id = wooi.op_plan_instance_id
277 and wooi2.operation_status = 3 -- Completed -- Typo corrected, earlier it was wooi.operation_status = 3
278 and wooi2.operation_type_id = 2 -- Drop
279 and wooi2.op_plan_instance_id = wopi.op_plan_instance_id
280 and wooi.from_subinventory_code = p_subinventory_code -- new
284 and mmtt.organization_id = l_organization_id -- new
281 and wooi.from_locator_id = p_locator_id -- new
282 and wooi.organization_id = l_organization_id -- new
283 and wooi.source_task_id = mmtt.transaction_temp_id -- new
285 and (wooi.operation_status = 1 and wooi.operation_type_id <> 2)
286 and wopi.activity_type_id = l_activity_type_id -- 1
287 and wopi.organization_id = l_organization_id -- new
288 and wopi.plan_type_id = decode(l_lpn_mtrl_grp_rule_id,1,l_plan_type_id,wopi.plan_type_id)
289 and wopi.orig_dest_sub_code = decode(l_lpn_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopi.orig_dest_sub_code )
290 and wopi.orig_dest_loc_id = decode(l_lpn_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopi.orig_dest_loc_id)
291 order by mmtt.last_update_date desc;
292 --
293 --
294 cursor c_lpn_active_wozone_witem is
295 select wlpn.outermost_lpn_id
296 from mtl_material_transactions_temp mmtt,
297 wms_license_plate_numbers wlpn,
298 wms_op_plan_instances wopi,
299 wms_op_operation_instances wooi,
300 wms_op_operation_instances wooi2
301 where wlpn.lpn_context = decode(l_is_in_inventory, 'Y', 1, 3)
302 and wlpn.organization_id = l_organization_id
303 and wlpn.subinventory_code = p_subinventory_code -- new
304 and wlpn.locator_id = p_locator_id -- new
305 and wlpn.lpn_id = mmtt.lpn_id
306 and wopi.status = 6
307 and wopi.op_plan_instance_id = wooi.op_plan_instance_id
308 and wooi2.operation_status = 3 -- Completed
309 and wooi2.operation_type_id = 2 -- Drop
310 and wooi2.op_plan_instance_id = wopi.op_plan_instance_id
311 and nvl(wooi.is_in_inventory, 'N') = l_is_in_inventory -- new
312 and wooi.from_subinventory_code = p_subinventory_code -- new
313 and wooi.from_locator_id = p_locator_id -- new
314 and wooi.organization_id = l_organization_id -- new
315 and (wooi.operation_status = 1 and wooi.operation_type_id <> 2)
316 and wooi.source_task_id = mmtt.transaction_temp_id
317 and mmtt.inventory_item_id = p_item_id
318 and mmtt.organization_id = l_organization_id -- new
319 and wopi.activity_type_id = l_activity_type_id
320 and wopi.organization_id = l_organization_id -- new
321 and wopi.plan_type_id = decode(l_lpn_mtrl_grp_rule_id,1,l_plan_type_id,wopi.plan_type_id)
322 and wopi.orig_dest_sub_code = decode(l_lpn_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopi.orig_dest_sub_code )
323 and wopi.orig_dest_loc_id = decode(l_lpn_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopi.orig_dest_loc_id)
324 order by wooi.last_update_date desc;
325 --
326 --
327 --
328 cursor c_lpn_active_wozone_woitem is
329 select wlpn.outermost_lpn_id
330 from mtl_material_transactions_temp mmtt,
331 wms_license_plate_numbers wlpn,
332 wms_op_plan_instances wopi,
333 wms_op_operation_instances wooi,
334 wms_op_operation_instances wooi2
335 where wlpn.lpn_context = decode(l_is_in_inventory, 'Y', 1, 3)
336 and wlpn.organization_id = l_organization_id
337 and wlpn.subinventory_code = p_subinventory_code -- new
338 and wlpn.locator_id = p_locator_id -- new
339 and wlpn.lpn_id = mmtt.lpn_id
340 and wopi.status = 6
341 and wopi.op_plan_instance_id = wooi.op_plan_instance_id
342 and wooi2.operation_status = 3 -- Completed
343 and wooi2.operation_type_id = 2 -- Drop
344 and wooi2.op_plan_instance_id = wopi.op_plan_instance_id
345 and wooi.organization_id = l_organization_id -- new
346 and wooi.source_task_id = mmtt.transaction_temp_id
347 and mmtt.organization_id = l_organization_id -- new
348 and (wooi.operation_status = 1 and wooi.operation_type_id <> 2)
349 and nvl(wooi.is_in_inventory, 'N') = l_is_in_inventory -- new
350 and wooi.from_subinventory_code = p_subinventory_code -- new
351 and wooi.from_locator_id = p_locator_id -- new
352 and wopi.activity_type_id = l_activity_type_id
353 and wopi.organization_id = l_organization_id -- new
354 and wopi.plan_type_id = decode(l_lpn_mtrl_grp_rule_id,1,l_plan_type_id,wopi.plan_type_id)
355 and wopi.orig_dest_sub_code = decode(l_lpn_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopi.orig_dest_sub_code )
356 and wopi.orig_dest_loc_id = decode(l_lpn_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopi.orig_dest_loc_id)
357 order by mmtt.last_update_date desc;
358 --
359 --
360 --
361 cursor c_lpn_comp_wzone_witem is
362 select wlpn.outermost_lpn_id
363 from wms_op_plan_instances_hist wopih,
364 wms_op_opertn_instances_hist wooih,
365 wms_zone_locators wzl,
366 wms_dispatched_tasks_history wdth,
367 wms_license_plate_numbers wlpn
368 where wzl.zone_id = l_pre_specified_zone_id
369 and wzl.subinventory_code = wlpn.subinventory_code
370 and wzl.subinventory_code = p_subinventory_code -- new
371 and wzl.organization_id = l_organization_id -- new
372 and (wzl.entire_sub_flag = 'Y' or wzl.inventory_location_id = wlpn.locator_id)
373 and wlpn.subinventory_code = wdth.dest_subinventory_code
374 and wlpn.locator_id = wdth.dest_locator_id
375 and wlpn.subinventory_code = p_subinventory_code -- new
376 and wlpn.locator_id = p_locator_id -- new
377 and wlpn.organization_id = l_organization_id
378 and wlpn.lpn_id = nvl(wdth.transfer_lpn_id, wdth.content_lpn_id)
379 and wdth.dest_locator_id = p_locator_id
380 and wdth.dest_subinventory_code = p_subinventory_code
381 and wdth.organization_id = l_organization_id -- new
382 and wdth.inventory_item_id = p_item_id
383 and wdth.status = 6 -- Completed task. lookup_type is WMS_TASK_STATUS
384 and wdth.transaction_id = wooih.source_task_id
385 and wooih.operation_sequence in (select max(operation_sequence)
386 from wms_op_opertn_instances_hist wooih2
387 where wooih2.op_plan_instance_id = wopih.op_plan_instance_id
388 and wooih2.operation_type_id in (2,9)
389 and wooih2.operation_status = 3)
390 and wooih.op_plan_instance_id = wopih.op_plan_instance_id
394 and wopih.status = 3 -- Plan Completed
391 and wooih.organization_id = l_organization_id -- new
392 and wooih.to_subinventory_code = p_subinventory_code -- new
393 and wooih.to_locator_id = p_locator_id -- new
395 and wopih.activity_type_id = l_activity_type_id
396 and wopih.organization_id = l_organization_id -- new
397 and wopih.plan_type_id = decode(l_lpn_mtrl_grp_rule_id,1,l_plan_type_id,wopih.plan_type_id)
398 and wopih.orig_dest_sub_code = decode(l_lpn_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopih.orig_dest_sub_code )
399 and wopih.orig_dest_loc_id = decode(l_lpn_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopih.orig_dest_loc_id)
400 order by wlpn.last_update_date desc;
401 --
402 --
403 cursor c_lpn_comp_wzone_woitem is
404 select wlpn.outermost_lpn_id
405 from wms_op_plan_instances_hist wopih,
406 wms_op_opertn_instances_hist wooih,
407 wms_zone_locators wzl,
408 wms_dispatched_tasks_history wdth,
409 wms_license_plate_numbers wlpn
410 where wzl.zone_id = l_pre_specified_zone_id
411 and wzl.subinventory_code = wlpn.subinventory_code
412 and wzl.subinventory_code = p_subinventory_code -- new
413 and wzl.organization_id = l_organization_id -- new
414 and (wzl.entire_sub_flag = 'Y' or wzl.inventory_location_id = wlpn.locator_id)
415 and wlpn.subinventory_code = wdth.dest_subinventory_code
416 and wlpn.locator_id = wdth.dest_locator_id
417 and wlpn.subinventory_code = p_subinventory_code -- new
418 and wlpn.locator_id = p_locator_id -- new
419 and wlpn.organization_id = l_organization_id
420 and wlpn.lpn_id = nvl(wdth.transfer_lpn_id, wdth.content_lpn_id)
421 and wdth.dest_locator_id = p_locator_id
422 and wdth.dest_subinventory_code = p_subinventory_code
423 and wdth.organization_id = l_organization_id -- new
424 and wdth.status = 6 -- Completed task. lookup_type is WMS_TASK_STATUS
425 and wdth.transaction_id = wooih.source_task_id
426 and wooih.operation_sequence in (select max(operation_sequence)
427 from wms_op_opertn_instances_hist wooih2
428 where wooih2.op_plan_instance_id = wopih.op_plan_instance_id
429 and wooih2.operation_type_id in (2,9)
430 and wooih2.operation_status = 3)
431 and wooih.op_plan_instance_id = wopih.op_plan_instance_id
432 and wooih.organization_id = l_organization_id -- new
433 and wooih.to_subinventory_code = p_subinventory_code -- new
434 and wooih.to_locator_id = p_locator_id -- new
435 and wopih.status = 3 -- Plan Completed
436 and wopih.activity_type_id = l_activity_type_id
437 and wopih.organization_id = l_organization_id -- new
438 and wopih.plan_type_id = decode(l_lpn_mtrl_grp_rule_id,1,l_plan_type_id,wopih.plan_type_id)
439 and wopih.orig_dest_sub_code = decode(l_lpn_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopih.orig_dest_sub_code )
440 and wopih.orig_dest_loc_id = decode(l_lpn_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopih.orig_dest_loc_id)
441 order by wlpn.last_update_date desc;
442 --
443 --
444 cursor c_lpn_comp_wozone_witem is
445 select wlpn.outermost_lpn_id
446 from wms_op_plan_instances_hist wopih,
447 wms_op_opertn_instances_hist wooih,
448 wms_dispatched_tasks_history wdth,
449 wms_license_plate_numbers wlpn
450 where wlpn.subinventory_code = wdth.dest_subinventory_code
451 and wlpn.locator_id = wdth.dest_locator_id
452 and wlpn.subinventory_code = p_subinventory_code -- new
453 and wlpn.locator_id = p_locator_id -- new
454 and wlpn.organization_id = l_organization_id
455 and wlpn.lpn_id = nvl(wdth.transfer_lpn_id, wdth.content_lpn_id)
456 and wdth.dest_locator_id = p_locator_id
457 and wdth.dest_subinventory_code = p_subinventory_code
458 and wdth.inventory_item_id = p_item_id -- new
459 and wdth.organization_id = l_organization_id -- new
460 and wdth.status = 6 -- Completed task. lookup_type is WMS_TASK_STATUS
461 and wdth.transaction_id = wooih.source_task_id
462 and wooih.organization_id = l_organization_id -- new
463 and wooih.to_subinventory_code = p_subinventory_code -- new
464 and wooih.to_locator_id = p_locator_id -- new
465 and wooih.operation_sequence in (select max(operation_sequence)
466 from wms_op_opertn_instances_hist wooih2
467 where wooih2.op_plan_instance_id = wopih.op_plan_instance_id
468 and wooih2.operation_type_id in (2,9)
469 and wooih2.operation_status = 3)
470 and wooih.op_plan_instance_id = wopih.op_plan_instance_id
471 and wooih.organization_id = l_organization_id -- new
472 and wooih.to_subinventory_code = p_subinventory_code -- new
473 and wooih.to_locator_id = p_locator_id -- new
474 and wopih.status = 3 -- Plan Completed
475 and wopih.activity_type_id = l_activity_type_id
476 and wopih.organization_id = l_organization_id -- new
477 and wopih.plan_type_id = decode(l_lpn_mtrl_grp_rule_id,1,l_plan_type_id,wopih.plan_type_id)
478 and wopih.orig_dest_sub_code = decode(l_lpn_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopih.orig_dest_sub_code )
479 and wopih.orig_dest_loc_id = decode(l_lpn_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopih.orig_dest_loc_id)
480 order by wlpn.last_update_date desc;
481 --
482 --
483 cursor c_lpn_comp_wozone_woitem is
484 select wlpn.outermost_lpn_id
485 from wms_op_plan_instances_hist wopih,
486 wms_op_opertn_instances_hist wooih,
487 wms_dispatched_tasks_history wdth,
488 wms_license_plate_numbers wlpn
489 where wlpn.subinventory_code = wdth.dest_subinventory_code
490 and wlpn.locator_id = wdth.dest_locator_id
491 and wlpn.subinventory_code = p_subinventory_code -- new
492 and wlpn.locator_id = p_locator_id -- new
493 and wlpn.organization_id = l_organization_id
494 and wlpn.lpn_id = nvl(wdth.transfer_lpn_id, wdth.content_lpn_id)
495 and wdth.dest_locator_id = p_locator_id
496 and wdth.dest_subinventory_code = p_subinventory_code
497 --and wdth.inventory_item_id = p_item_id -- new
501 and wooih.organization_id = l_organization_id -- new
498 and wdth.organization_id = l_organization_id -- new
499 and wdth.status = 6 -- Completed task. lookup_type is WMS_TASK_STATUS
500 and wdth.transaction_id = wooih.source_task_id
502 and wooih.to_subinventory_code = p_subinventory_code -- new
503 and wooih.to_locator_id = p_locator_id -- new
504 and wooih.operation_sequence in (select max(operation_sequence)
505 from wms_op_opertn_instances_hist wooih2
506 where wooih2.op_plan_instance_id = wopih.op_plan_instance_id
507 and wooih2.operation_type_id in (2,9)
508 and wooih2.operation_status = 3)
509 and wooih.op_plan_instance_id = wopih.op_plan_instance_id
510 and wooih.organization_id = l_organization_id -- new
511 and wooih.to_subinventory_code = p_subinventory_code -- new
512 and wooih.to_locator_id = p_locator_id -- new
513 and wopih.status = 3 -- Plan Completed
514 and wopih.activity_type_id = l_activity_type_id
515 and wopih.organization_id = l_organization_id -- new
516 and wopih.plan_type_id = decode(l_lpn_mtrl_grp_rule_id,1,l_plan_type_id,wopih.plan_type_id)
517 and wopih.orig_dest_sub_code = decode(l_lpn_mtrl_grp_rule_id,2,l_orig_dest_sub_code,wopih.orig_dest_sub_code )
518 and wopih.orig_dest_loc_id = decode(l_lpn_mtrl_grp_rule_id,3,l_orig_dest_loc_id,wopih.orig_dest_loc_id)
519 order by wlpn.last_update_date desc;
520 --
521 -- ### End of Cursor and Variable Declaration section
522 --
523 begin
524 -- ### Initialize API return status to success
525 x_return_status := FND_API.G_RET_STS_SUCCESS;
526
527 -- ### Initialize message stack since p_init_msg_list is set to TRUE
528 -- ### The p_init_msg_list is set to 'TRUE' in this code and so the message stack will always be initialised.
529 -- if fnd_api.to_boolean(p_init_msg_list) then
530 -- fnd_msg_pub.initialize;
531 -- end if;
532
533 l_prog := 10;
534 if (l_debug = 1) then
535 trace(' Entering procedure '|| l_proc || ':'|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
536 trace(l_proc || ' p_mode => ' || p_mode);
537 trace(l_proc || ' p_task_id => ' || p_task_id);
538 trace(l_proc || ' p_activity_type_id => ' || p_activity_type_id);
539 trace(l_proc || ' p_lpn_id => ' || p_lpn_id);
540 trace(l_proc || ' p_item_id => ' || p_item_id);
541 end if;
542
543 if (l_debug = 1) then
544 trace(l_proc || ' Opening/Fetching Cursor "c_oper_plan_details"...', 1);
545 end if;
546 -- ### Derive Operation Plan details to start with.
547 open c_oper_plan_details;
548 fetch c_oper_plan_details
549 into l_operation_plan_id, l_locator_id, l_subinventory_code, l_operation_plan_dtl_id,
550 l_activity_type_id, l_plan_type_id, l_orig_dest_sub_code, l_orig_dest_loc_id,
551 l_pre_specified_zone_id, l_pre_specified_sub_code, l_lpn_mtrl_grp_rule_id,
552 l_operation_type, l_is_in_inventory, l_organization_id;
553
554 if c_oper_plan_details%NOTFOUND
555 then
556 fnd_message.set_name('WMS', 'WMS_OPERTN_PLAN_ID_INVALID');
557 fnd_msg_pub.ADD;
558 raise fnd_api.g_exc_error; -- Added after Code Review on Sept 11th 2003.
559 else
560 -- ### Print values detived from teh cursor in nthe log file.
561 if (l_debug = 1) then
562 trace(l_proc || ' Printing Operation Plan Detail Information...');
563 trace(l_proc || ' l_operation_plan_id : '|| l_operation_plan_id);
564 trace(l_proc || ' l_locator_id : '|| l_locator_id);
565 trace(l_proc || ' l_subinventory_code : '|| l_subinventory_code);
566 trace(l_proc || ' l_operation_plan_detail_id : '|| l_operation_plan_dtl_id);
567 trace(l_proc || ' l_activity_type_id : '|| l_activity_type_id);
568 trace(l_proc || ' l_plan_type_id : '|| l_plan_type_id);
569 trace(l_proc || ' l_orig_dest_sub_code : '|| l_orig_dest_sub_code);
570 trace(l_proc || ' l_orig_dest_loc_id : '|| l_orig_dest_loc_id);
571 trace(l_proc || ' l_pre_specified_zone_id : '|| l_pre_specified_zone_id);
572 trace(l_proc || ' l_pre_specified_sub_code : '|| l_pre_specified_sub_code);
573 trace(l_proc || ' l_lpn_mtrl_grp_rule_id : '|| l_lpn_mtrl_grp_rule_id);
574 trace(l_proc || ' l_operation_type : '|| l_operation_type);
575 trace(l_proc || ' l_is_in_inventory : '|| l_is_in_inventory);
576 trace(l_proc || ' l_organization_id : '|| l_organization_id);
577 end if;
578 -- ### Close the above cursor.
579 close c_oper_plan_details;
580 l_prog := 11;
581 -- ### Check to see if a valid Material Grouping Rule is stamped on the oeration plan detail.
582 -- ### The LOV on the Form field allows to select a valid Rule only. Hence the possible cases
583 -- ### are that either there is a value which is valid or a null value. Hence check only for null.
584 if l_lpn_mtrl_grp_rule_id is null then
585 fnd_message.set_name('WMS', 'WMS_MTRL_GRP_RULE_ID_IS_NULL');
586 fnd_msg_pub.ADD;
587 raise fnd_api.g_exc_error; -- Added after Code Review on Sept 11th 2003.
588 end if;
589 end if;
590
591 l_prog := 20;
592 --
593 if (l_debug = 1) then
594 trace(l_proc || ' Now that a Material Grouping Rule "' || l_lpn_mtrl_grp_rule_id || '" is stamped on the Operation Plan Detail Line....', 1);
595 trace(l_proc || ' Proceeding further into the code logic.....');
596 end if;
597 -- @@@ As per the new design as of Sept 16th 2003, the code logic will fork based on if the variable
598 -- @@@ " l_pre_specified_zone_id" is populated from the fetch of the Operation Plan Detail cursor.
599 -- @@@ Now the same cursor is opened irrespective of the Material Grouping Rule stamped on the Operation
600 -- @@@ Plan Detail Line.
601
602 -- ### Prespecified Zone is not null. For th LPN Suggestion logic, the Subinventory and Locator derived by calling
606 then
603 -- ### the Locator Suggection API is passed in irrespective. Hence pre-specified Subinventory and Locator is always
604 -- ### available for the LPN Suggestion Cursors as a restiriction, unlike the Locator Sugegstion API Cusrors.
605 if l_pre_specified_zone_id is not null
607 -- ### Setting Cursor Name.
608 if (l_debug =1 ) then
609 trace(l_proc || ' Within "l_pre_specified_zone_id is not null" segment....', 1);
610 trace(l_proc || ' Opening "active operations" cursor "' ||l_cursor||'"', 1);
611 end if;
612
613 -- @@@ Zone and Item Specified
614 if p_item_id is not null
615 then
616 l_cursor := 'c_lpn_active_wzone_witem';
617 -- ### Open Cursor c_lpn_active_wzone_witem to look within "active operation" plans.
618 open c_lpn_active_wzone_witem;
619 fetch c_lpn_active_wzone_witem
620 into l_lpn_id;
621
622 if c_lpn_active_wzone_witem%NOTFOUND then
623 if (l_debug =1 ) then
624 trace(l_proc || ' "c_lpn_active_wzone_witem" failed with %NOTFOUND...', 1);
625 trace(l_proc || ' Task ID "'|| p_task_id|| '" could not derive records from Active tables...');
626 trace(l_proc || ' Commencing search in History tables with Task ID '|| p_task_id, 1);
627 trace(l_proc || ' Setting OUT variables to null...', 1);
628 trace(l_proc || ' Closing "active operations" cursor "' ||l_cursor||'"', 1);
629 end if;
630 x_lpn_id := null;
631 close c_lpn_active_wzone_witem;
632 l_cursor := null;
633
634 -- ### "active operations" cursor did not return any records, open the "completed operations" cursor
635 -- ### Opening cursor c_lpn_active_without_zone to look for completed operations.
636 -- ### Setting Cursor Name
637 l_cursor := 'c_lpn_comp_wzone_witem';
638 if (l_debug =1 ) then
639 trace(l_proc || ' Opening "completed operations" cursor "' ||l_cursor||'"', 1);
640 end if;
641
642 l_cursor := 'c_lpn_comp_wzone_witem';
643 -- ### Open Cursor c_lpn_comp_wzone_witem to look within "completed operation" plans.
644 open c_lpn_comp_wzone_witem;
645 fetch c_lpn_comp_wzone_witem
646 into l_lpn_id;
647
648 if c_lpn_comp_wzone_witem%NOTFOUND then
649 if (l_debug = 1) then
650 trace(l_proc || ' "c_lpn_comp_wzone_witem" failed with %NOTFOUND...', 1);
651 trace(l_proc || ' Task ID "'|| p_task_id|| '" could not derive records from History tables...');
652 trace(l_proc || ' Technical end of API Execution...');
653 trace(l_proc || ' Setting OUT variables to null...', 1);
654 trace(l_proc || ' Closing "completed operations" cursor "' ||l_cursor||'"', 1);
655 end if;
656 x_lpn_id := null;
657 close c_lpn_comp_wzone_witem;
658 l_cursor := null;
659 elsif c_lpn_comp_wzone_witem%FOUND then
660 -- c_lpn_comp_wzone_witem Cursor found..
661 if (l_debug = 1) then
662 trace(l_proc || ' "c_lpn_comp_wzone_witem" FOUND...', 1);
663 trace(l_proc || ' Closing cursor "' ||l_cursor||'"', 1);
664 end if;
665 close c_lpn_comp_wzone_witem;
666 l_cur_found := true;
667 end if;-- @@@ Marker: c_lpn_comp_wzone_witem FOUND/NOTFOUND
668 elsif c_lpn_active_wzone_witem%FOUND then
669 -- c_lpn_active_wzone_witem Cursor found..
670 if (l_debug = 1) then
671 trace(l_proc || ' "c_lpn_active_wzone_witem" FOUND...', 1);
672 trace(l_proc || ' Closing cursor "' ||l_cursor||'"', 1);
673 end if;
674 close c_lpn_active_wzone_witem;
675 l_cur_found := true;
676 end if;-- @@@ Marker: c_lpn_active_wzone_witem FOUND/NOTFOUND
677 -- @@@ Zone Specified but Item Not Specified
678 elsif p_item_id is null
679 then
680 l_cursor := 'c_lpn_active_wzone_woitem';
681 -- ### Open Cursor c_lpn_active_wzone_woitem to look within "active operation" plans.
682 open c_lpn_active_wzone_woitem;
683 fetch c_lpn_active_wzone_woitem
684 into l_lpn_id;
685
686 if c_lpn_active_wzone_woitem%NOTFOUND then
687 if (l_debug =1 ) then
688 trace(l_proc || ' "c_lpn_active_wzone_woitem" failed with %NOTFOUND...', 1);
689 trace(l_proc || ' Task ID "'|| p_task_id|| '" could not derive records from Active tables...');
690 trace(l_proc || ' Commencing search in History tables with Task ID '|| p_task_id, 1);
691 trace(l_proc || ' Setting OUT variables to null...', 1);
692 trace(l_proc || ' Closing "active operations" cursor "' ||l_cursor||'"', 1);
693 end if;
694 x_lpn_id := null;
695 close c_lpn_active_wzone_woitem;
696 l_cursor := null;
697
698 -- ### "active operations" cursor did not return any records, open the "completed operations" cursor
699 -- ### Opening cursor c_lpn_active_without_zone to look for completed operations.
700 -- ### Setting Cursor Name
701 l_cursor := 'c_lpn_comp_wzone_woitem';
702 if (l_debug =1 ) then
703 trace(l_proc || ' Opening "completed operations" cursor "' ||l_cursor||'"', 1);
704 end if;
705
706 l_cursor := 'c_lpn_comp_wzone_woitem';
707 -- ### Open Cursor c_lpn_comp_wzone_woitem to look within "completed operation" plans.
708 open c_lpn_comp_wzone_woitem;
709 fetch c_lpn_comp_wzone_woitem
710 into l_lpn_id;
711
712 if c_lpn_comp_wzone_woitem%NOTFOUND then
713 if (l_debug = 1) then
717 trace(l_proc || ' Setting OUT variables to null...', 1);
714 trace(l_proc || ' "c_lpn_comp_wzone_woitem" failed with %NOTFOUND...', 1);
715 trace(l_proc || ' Task ID "'|| p_task_id|| '" could not derive records from History tables...');
716 trace(l_proc || ' Technical end of API Execution...');
718 trace(l_proc || ' Closing "completed operations" cursor "' ||l_cursor||'"', 1);
719 end if;
720 x_lpn_id := null;
721 close c_lpn_comp_wzone_woitem;
722 l_cursor := null;
723 elsif c_lpn_comp_wzone_woitem%FOUND then
724 -- c_lpn_comp_wzone_witem Cursor found..
725 if (l_debug = 1) then
726 trace(l_proc || ' "c_lpn_comp_wzone_woitem" FOUND...', 1);
727 trace(l_proc || ' Closing cursor "' ||l_cursor||'"', 1);
728 end if;
729 close c_lpn_comp_wzone_woitem;
730 l_cur_found := true;
731 end if;-- @@@ Marker: c_lpn_comp_wzone_woitem FOUND/NOTFOUND
732 elsif c_lpn_active_wzone_woitem%FOUND then
733 -- c_lpn_active_wzone_woitem Cursor found..
734 if (l_debug = 1) then
735 trace(l_proc || ' "c_lpn_active_wzone_woitem" FOUND...', 1);
736 trace(l_proc || ' Closing cursor "' ||l_cursor||'"', 1);
737 end if;
738 close c_lpn_active_wzone_woitem;
739 l_cur_found := true;
740 end if;-- @@@ Marker: c_lpn_active_wzone_woitem FOUND/NOTFOUND
741 end if;-- @@@ Marker: Check for p_item_id
742 -- ### Prespecified Zone is null..
743 elsif l_pre_specified_zone_id is null
744 then
745 -- ### Setting Cursor Name.
746 if (l_debug =1 ) then
747 trace(l_proc || ' Within "l_pre_specified_zone_id is null" segment....', 1);
748 trace(l_proc || ' Opening "active operations" cursor "' ||l_cursor||'"', 1);
749 end if;
750
751 -- @@@ Zone Not Specified but Item Specified
752 if p_item_id is not null
753 then
754 l_cursor := 'c_lpn_active_wozone_witem';
755 -- ### Open Cursor c_lpn_active_wzone_witem to look within "active operation" plans.
756 open c_lpn_active_wozone_witem;
757 fetch c_lpn_active_wozone_witem
758 into l_lpn_id;
759
760 if c_lpn_active_wozone_witem%NOTFOUND then
761 if (l_debug =1 ) then
762 trace(l_proc || ' "c_lpn_active_wozone_witem" failed with %NOTFOUND...', 1);
763 trace(l_proc || ' Task ID "'|| p_task_id|| '" could not derive records from Active tables...');
764 trace(l_proc || ' Commencing search in History tables with Task ID '|| p_task_id, 1);
765 trace(l_proc || ' Setting OUT variables to null...', 1);
766 trace(l_proc || ' Closing "active operations" cursor "' ||l_cursor||'"', 1);
767 end if;
768 x_lpn_id := null;
769 close c_lpn_active_wozone_witem;
770 l_cursor := null;
771
772 -- ### "active operations" cursor did not return any records, open the "completed operations" cursor
773 -- ### Opening cursor c_lpn_active_without_zone to look for completed operations.
774 -- ### Setting Cursor Name
775 if (l_debug =1 ) then
776 trace(l_proc || ' Opening "completed operations" cursor "' ||l_cursor||'"', 1);
777 end if;
778
779 l_cursor := 'c_lpn_comp_wozone_witem';
780 -- ### Open Cursor c_lpn_comp_wzone_witem to look within "completed operation" plans.
781 open c_lpn_comp_wozone_witem;
782 fetch c_lpn_comp_wozone_witem
783 into l_lpn_id;
787 trace(l_proc || ' "c_lpn_comp_wozone_witem" failed with %NOTFOUND...', 1);
784
785 if c_lpn_comp_wozone_witem%NOTFOUND then
786 if (l_debug = 1) then
788 trace(l_proc || ' Task ID "'|| p_task_id|| '" could not derive records from History tables...');
789 trace(l_proc || ' Technical end of API Execution...');
790 trace(l_proc || ' Setting OUT variables to null...', 1);
791 trace(l_proc || ' Closing "completed operations" cursor "' ||l_cursor||'"', 1);
792 end if;
793 x_lpn_id := null;
794 close c_lpn_comp_wozone_witem;
795 l_cursor := null;
796 elsif c_lpn_comp_wozone_witem%FOUND then
797 -- c_lpn_comp_wzone_witem Cursor found..
798 if (l_debug = 1) then
799 trace(l_proc || ' "c_lpn_comp_wozone_witem" FOUND...', 1);
800 trace(l_proc || ' Closing cursor "' ||l_cursor||'"', 1);
801 end if;
802 close c_lpn_comp_wozone_witem;
803 l_cur_found := true;
804 end if;-- @@@ Marker: c_lpn_comp_wozone_witem FOUND/NOTFOUND
805 elsif c_lpn_active_wozone_witem%FOUND then
806 -- c_lpn_active_wozone_witem Cursor found..
807 if (l_debug = 1) then
808 trace(l_proc || ' "c_lpn_active_wozone_witem" FOUND...', 1);
809 trace(l_proc || ' Closing cursor "' ||l_cursor||'"', 1);
810 end if;
811 close c_lpn_active_wozone_witem;
812 l_cur_found := true;
813 end if;-- @@@ Marker: c_lpn_active_wozone_witem FOUND/NOTFOUND
814 -- @@@ Zone Not Specified and Item Not Specified
815 elsif p_item_id is null
816 then
817 l_cursor := 'c_lpn_active_wozone_woitem';
818 -- ### Open Cursor c_lpn_active_wozone_woitem to look within "active operation" plans.
819 open c_lpn_active_wozone_woitem;
820 fetch c_lpn_active_wozone_woitem
821 into l_lpn_id;
822
823 if c_lpn_active_wozone_woitem%NOTFOUND then
824 if (l_debug =1 ) then
825 trace(l_proc || ' "c_lpn_active_wozone_woitem" failed with %NOTFOUND...', 1);
826 trace(l_proc || ' Task ID "'|| p_task_id|| '" could not derive records from Active tables...');
827 trace(l_proc || ' Commencing search in History tables with Task ID '|| p_task_id, 1);
828 trace(l_proc || ' Setting OUT variables to null...', 1);
829 trace(l_proc || ' Closing "active operations" cursor "' ||l_cursor||'"', 1);
830 end if;
831 x_lpn_id := null;
832 close c_lpn_active_wozone_woitem;
833 l_cursor := null;
834
835 -- ### "active operations" cursor did not return any records, open the "completed operations" cursor
836 -- ### Opening cursor c_lpn_active_without_zone to look for completed operations.
837 -- ### Setting Cursor Name
838 l_cursor := 'c_lpn_comp_wzone_woitem';
839 if (l_debug =1 ) then
840 trace(l_proc || ' Opening "completed operations" cursor "' ||l_cursor||'"', 1);
841 end if;
842
843 l_cursor := 'c_lpn_comp_wozone_woitem';
844 -- ### Open Cursor c_lpn_comp_wozone_woitem to look within "completed operation" plans.
845 open c_lpn_comp_wozone_woitem;
846 fetch c_lpn_comp_wozone_woitem
847 into l_lpn_id;
848
849 if c_lpn_comp_wozone_woitem%NOTFOUND then
850 if (l_debug = 1) then
851 trace(l_proc || ' "c_lpn_comp_wozone_woitem" failed with %NOTFOUND...', 1);
855 trace(l_proc || ' Closing "completed operations" cursor "' ||l_cursor||'"', 1);
852 trace(l_proc || ' Task ID "'|| p_task_id|| '" could not derive records from History tables...');
853 trace(l_proc || ' Technical end of API Execution...');
854 trace(l_proc || ' Setting OUT variables to null...', 1);
856 end if;
857 x_lpn_id := null;
858 close c_lpn_comp_wozone_woitem;
859 l_cursor := null;
860 elsif c_lpn_comp_wozone_woitem%FOUND then
861 -- c_lpn_comp_wozone_woitem Cursor found..
862 if (l_debug = 1) then
863 trace(l_proc || ' "c_lpn_comp_wozone_woitem" FOUND...', 1);
864 trace(l_proc || ' Closing cursor "' ||l_cursor||'"', 1);
865 end if;
866 close c_lpn_comp_wozone_woitem;
867 l_cur_found := true;
868 end if;-- @@@ Marker: c_lpn_comp_wozone_woitem FOUND/NOTFOUND
869 elsif c_lpn_active_wozone_woitem%FOUND then
870 -- c_lpn_active_wozone_woitem Cursor found..
871 if (l_debug = 1) then
872 trace(l_proc || ' "c_lpn_active_wozone_woitem" FOUND...', 1);
873 trace(l_proc || ' Closing cursor "' ||l_cursor||'"', 1);
874 end if;
875 close c_lpn_active_wozone_woitem;
876 l_cur_found := true;
877 end if;-- @@@ Marker: c_lpn_active_wozone_woitem FOUND/NOTFOUND
878 end if;-- @@@ Marker: Check for p_item_id
879 end if;-- @@@ Marker: Check for l_pre_specified_zone_id
880 -- ### Common code to set out values if either the "active" or "complete" "with Zone cursor is FOUND.
881 -- ### This is achieved by setting the value of the boolean "l_cur_found" appropriately.
882 if l_cur_found
883 then
884 if (l_debug =1 ) then
885 trace(l_proc || 'Within "if l_cur_found " is entered...', 1);
886 trace(l_proc || '"' || l_cursor || '" FOUND...', 1);
887 trace(l_proc || ' LPN ID returned by cursor "' || l_cursor || '" => ' || l_lpn_id, 4);
888 end if;
889 x_lpn_id := l_lpn_id;
890 --
891 --### Call trace message before exiting...
892 --
893 exit_proc_msg(
894 x_return_status => x_return_status
895 , x_msg_count => x_msg_count
896 , x_msg_data => x_msg_data
897 , x_lpn_id => x_lpn_id
898 , x_lpn_valid => x_lpn_valid
899 , l_proc => l_proc
900 );
901 l_cursor := null;
902 return;
903 end if;
904
905
906 exception
907 when fnd_api.g_exc_error then
908 x_return_status := fnd_api.g_ret_sts_error;
909
910 if (l_debug = 1) then
911 trace(' Progress at the time of failure is ' || l_prog, 1);
912 trace(' Error Code, Error Message...' || sqlerrm(sqlcode), 1);
913 end if;
914
915 if (l_prog = 10) then
916 if (l_debug = 1) then
917 trace(l_proc || ' "c_oper_plan_details" failed with %NOTFOUND...', 1);
918 trace(l_proc || ' Task ID '|| p_task_id|| ' is invalid. Please pass a Valid Task.');
919 end if;
920 else
921 null;
922 end if;
923
924 if (l_prog = 20) then
925 if (l_debug = 1) then
926 trace(' Material Grouping Rule not stamped on the Operation Plan Detail line. Unable to proceed. Aborting execution...');
927 end if;
928 else
929 null;
930 end if;
931
932 if c_oper_plan_details%ISOPEN then
933 close c_oper_plan_details;
934 end if;
935
936 if c_lpn_active_wzone_witem%ISOPEN then
937 close c_lpn_active_wzone_witem;
938 end if;
939
940 if c_lpn_active_wzone_woitem%ISOPEN then
941 close c_lpn_active_wzone_woitem;
942 end if;
943
944 if c_lpn_comp_wzone_witem%ISOPEN then
945 close c_lpn_comp_wzone_witem;
946 end if;
947
948 if c_lpn_comp_wzone_woitem%ISOPEN then
949 close c_lpn_comp_wzone_woitem;
950 end if;
951
952 if c_lpn_active_wozone_witem%ISOPEN then
953 close c_lpn_active_wozone_witem;
954 end if;
955
956 if c_lpn_active_wozone_woitem%ISOPEN then
957 close c_lpn_active_wozone_woitem;
961 close c_lpn_comp_wozone_witem;
958 end if;
959
960 if c_lpn_comp_wozone_witem%ISOPEN then
962 end if;
963
964 if c_lpn_comp_wozone_woitem%ISOPEN then
965 close c_lpn_comp_wozone_woitem;
966 end if;
967
968 if (l_debug = 1) then
969 trace(l_proc || ' Error Message(Error Code) ' || sqlerrm(sqlcode));
970 end if;
971
972 --
973 --### Call trace message before exiting...
974 --
975 exit_proc_msg(
976 x_return_status => x_return_status
977 , x_msg_count => x_msg_count
978 , x_msg_data => x_msg_data
979 , x_lpn_id => x_lpn_id
980 , x_lpn_valid => x_lpn_valid
981 , l_proc => l_proc
982 );
983
984 when others then
985 if (l_debug = 1) then
986 trace(' Progress at the time of failure is ' || l_prog, 1);
987 trace(' Error Code, Error Message...' || sqlerrm(sqlcode), 1);
988 end if;
989
990 if c_oper_plan_details%ISOPEN then
991 close c_oper_plan_details;
992 end if;
993
994 if c_lpn_active_wzone_witem%ISOPEN then
995 close c_lpn_active_wzone_witem;
996 end if;
997
998 if c_lpn_active_wzone_woitem%ISOPEN then
999 close c_lpn_active_wzone_woitem;
1000 end if;
1001
1002 if c_lpn_comp_wzone_witem%ISOPEN then
1003 close c_lpn_comp_wzone_witem;
1004 end if;
1005
1006 if c_lpn_comp_wzone_woitem%ISOPEN then
1007 close c_lpn_comp_wzone_woitem;
1008 end if;
1009
1010 if c_lpn_active_wozone_witem%ISOPEN then
1011 close c_lpn_active_wozone_witem;
1012 end if;
1013
1014 if c_lpn_active_wozone_woitem%ISOPEN then
1015 close c_lpn_active_wozone_woitem;
1016 end if;
1017
1018 if c_lpn_comp_wozone_witem%ISOPEN then
1019 close c_lpn_comp_wozone_witem;
1020 end if;
1021
1022 if c_lpn_comp_wozone_woitem%ISOPEN then
1023 close c_lpn_comp_wozone_woitem;
1024 end if;
1025
1026 if (l_debug = 1) then
1027 trace(l_proc || ' Error Message(Error Code) ' || sqlerrm(sqlcode));
1028 end if;
1029
1030 --
1031 --### Call trace message before exiting...
1032 --
1033 exit_proc_msg(
1034 x_return_status => x_return_status
1035 , x_msg_count => x_msg_count
1036 , x_msg_data => x_msg_data
1037 , x_lpn_id => x_lpn_id
1038 , x_lpn_valid => x_lpn_valid
1039 , l_proc => l_proc
1040 );
1041 end get_seed_dest_lpn;
1042
1043 end wms_atf_destination_lpn;
[Home] [Help]