[Home] [Help]
178: and wts.status_code <> 'CL' /*6634322*/
179: and milk.organization_id = p_organization_id
180: and milk1.organization_id (+) = wda.organization_id
181: and milk1.inventory_location_id (+) = wda.staging_Lane_Id
182: and inv_project.get_locsegs(milk.inventory_location_id, milk.organization_id) like (p_dock_door)
183: and nvl(milk.disable_date,sysdate+1) > sysdate
184: and rownum<2; --We need to just make sure that there exists a row, atleast.
185:
186: --Bug#6018835.Added following cursor.
198: and wts.status_code <> 'CL' /*6634322*/
199: and milk.organization_id = p_organization_id
200: and milk1.organization_id (+) = wda.organization_id
201: and milk1.inventory_location_id (+) = wda.staging_Lane_Id
202: and inv_project.get_locsegs(milk.inventory_location_id, milk.organization_id) like (p_dock_door)
203: AND nvl(milk.disable_date,sysdate+1) > sysdate
204: ANd rownum<2; --Just Checking the existance.
205:
206: l_available_appt NUMBER := 0 ;
222: -- is used to avoid "unambiguous column name" error
223: open x_dock_door_lov for
224: select distinct
225: wda.dock_id
226: , inv_project.get_locsegs(milk.inventory_location_id, milk.organization_id)
227: milk_concatenated_segments
228: , wda.dock_appointment_id
229: , wda.organization_id
230: , wda.trip_stop
249: and wts.status_code <> 'CL' /*6634322*/
250: and milk.organization_id = p_organization_id
251: and milk1.organization_id (+) = wda.organization_id
252: and milk1.inventory_location_id (+) = wda.staging_Lane_Id
253: and inv_project.get_locsegs(milk.inventory_location_id, milk.organization_id) like (p_dock_door)
254: AND nvl(milk.disable_date,sysdate+1) > sysdate
255: order by milk_concatenated_segments;
256:
257: --Bug#6018835.Ends.
266: SELECT dock_id , milk_concatenated_segments, dock_appointment_id,organization_id,
267: trip_stop, trip_id, subinventory_code,concatenated_segments , is_loaded , trip_name
268: FROM (
269: SELECT distinct wda.dock_id,
270: inv_project.get_locsegs(milk.inventory_location_id, milk.organization_id) milk_concatenated_segments,
271: wda.dock_appointment_id, wda.organization_id, wda.trip_stop, wts.trip_id,
272: milk1.subinventory_code, milk1.concatenated_segments ,
273: WMS_SHIPPING_TRANSACTION_PUB.is_loaded(p_organization_id,wda.dock_id,'Y') is_loaded ,
274: wt.name trip_name , ( SYSDATE - wda.end_time ) last_schedule_time_diff
283: and wts.status_code <> 'CL' /*6634322*/
284: and milk.organization_id = p_organization_id
285: and milk1.organization_id (+) = wda.organization_id
286: and milk1.inventory_location_id (+) = wda.staging_Lane_Id
287: and inv_project.get_locsegs(milk.inventory_location_id, milk.organization_id) like (p_dock_door)
288: and nvl(milk.disable_date,sysdate+1) > sysdate
289: ORDER BY 11 ASC
290: )
291: WHERE ROWNUM<2; --We need only the last recent apptmt.
295: SELECT dock_id , milk_concatenated_segments, dock_appointment_id,organization_id,
296: trip_stop, trip_id, subinventory_code,concatenated_segments , is_loaded , trip_name
297: FROM (
298: SELECT distinct wda.dock_id,
299: inv_project.get_locsegs(milk.inventory_location_id, milk.organization_id) milk_concatenated_segments,
300: wda.dock_appointment_id, wda.organization_id, wda.trip_stop, wts.trip_id,
301: milk1.subinventory_code, milk1.concatenated_segments ,
302: WMS_SHIPPING_TRANSACTION_PUB.is_loaded(p_organization_id,wda.dock_id,'Y') is_loaded ,
303: wt.name trip_name , ( wda.start_time - SYSDATE) next_schedule_time_diff
312: and wts.status_code <> 'CL' /*6634322*/
313: and milk.organization_id = p_organization_id
314: and milk1.organization_id (+) = wda.organization_id
315: and milk1.inventory_location_id (+) = wda.staging_Lane_Id
316: and inv_project.get_locsegs(milk.inventory_location_id, milk.organization_id) like (p_dock_door)
317: and nvl(milk.disable_date,sysdate+1) > sysdate
318: ORDER BY 11 ASC
319: )
320: WHERE ROWNUM<2; --We need only one (the immediate next apptmt).
327: -- segments should be suppressed from the KFV in the LOV. Alias "milk_concatenated_segments"
328: -- is used to avoid "unambiguous column name" error
329: open x_dock_door_lov for
330: select milk.inventory_location_id,
331: inv_project.get_locsegs(milk.inventory_location_id, milk.organization_id)
332: milk_concatenated_segments
333: ,0, milk.organization_id,
334: 0,0,'','',is_loaded(p_organization_id,milk.inventory_location_id,'N')
335: ,''
335: ,''
336: from mtl_item_locations_kfv milk
337: where inventory_location_type = 1
338: AND milk.organization_id = p_organization_id
339: AND inv_project.get_locsegs(milk.inventory_location_id, milk.organization_id) like (p_dock_door)
340: --AND segment19 IS NULL -- Bug 5336849, As a release policy
341: -- the code should not depend on FF view during patch installation
342: -- so changing the logic
343: AND nvl(milk.physical_location_id,milk.inventory_location_id) = milk.inventory_location_id
5581: -- segments should be suppressed from the KFV in the LOV. Alias "milk_concatenated_segments"
5582: -- is used to avoid "unambiguous column name" error
5583:
5584: /*
5585: Bug 13104302 -Perf issue- Provided hint 'leading', made the call inv_project.get_locsegs() one per WSTT record.
5586: */
5587: open x_dock_door_lov for
5588: SELECT * FROM (
5589: SELECT /*+LEADING(WSTT)*/ DISTINCT
5587: open x_dock_door_lov for
5588: SELECT * FROM (
5589: SELECT /*+LEADING(WSTT)*/ DISTINCT
5590: milk.inventory_location_id,
5591: inv_project.get_locsegs(wstt.dock_door_id, wstt.organization_id)
5592: milk_concatenated_segments,
5593: 0 dock_appointment_id,
5594: milk.organization_id,
5595: 0 trip_stop,