[Home] [Help]
127: and wts.status_code = 'OP'
128: and milk.organization_id = p_organization_id
129: and milk1.organization_id (+) = wda.organization_id
130: and milk1.inventory_location_id (+) = wda.staging_Lane_Id
131: and inv_project.get_locsegs(milk.inventory_location_id, milk.organization_id) like (p_dock_door)
132: and nvl(milk.disable_date,sysdate+1) > sysdate
133: and rownum<2; --We need to just make sure that there exists a row, atleast.
134:
135: --Bug#6018835.Added following cursor.
147: and wts.status_code = 'OP'
148: and milk.organization_id = p_organization_id
149: and milk1.organization_id (+) = wda.organization_id
150: and milk1.inventory_location_id (+) = wda.staging_Lane_Id
151: and inv_project.get_locsegs(milk.inventory_location_id, milk.organization_id) like (p_dock_door)
152: AND nvl(milk.disable_date,sysdate+1) > sysdate
153: ANd rownum<2; --Just Checking the existance.
154:
155: l_available_appt NUMBER := 0 ;
171: -- is used to avoid "unambiguous column name" error
172: open x_dock_door_lov for
173: select distinct
174: wda.dock_id
175: , inv_project.get_locsegs(milk.inventory_location_id, milk.organization_id)
176: milk_concatenated_segments
177: , wda.dock_appointment_id
178: , wda.organization_id
179: , wda.trip_stop
198: and wts.status_code = 'OP'
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: order by milk_concatenated_segments;
205:
206: --Bug#6018835.Ends.
215: SELECT dock_id , milk_concatenated_segments, dock_appointment_id,organization_id,
216: trip_stop, trip_id, subinventory_code,concatenated_segments , is_loaded , trip_name
217: FROM (
218: SELECT distinct wda.dock_id,
219: inv_project.get_locsegs(milk.inventory_location_id, milk.organization_id) milk_concatenated_segments,
220: wda.dock_appointment_id, wda.organization_id, wda.trip_stop, wts.trip_id,
221: milk1.subinventory_code, milk1.concatenated_segments ,
222: WMS_SHIPPING_TRANSACTION_PUB.is_loaded(p_organization_id,wda.dock_id,'Y') is_loaded ,
223: wt.name trip_name , ( SYSDATE - wda.end_time ) last_schedule_time_diff
232: and wts.status_code = 'OP'
233: and milk.organization_id = p_organization_id
234: and milk1.organization_id (+) = wda.organization_id
235: and milk1.inventory_location_id (+) = wda.staging_Lane_Id
236: and inv_project.get_locsegs(milk.inventory_location_id, milk.organization_id) like (p_dock_door)
237: and nvl(milk.disable_date,sysdate+1) > sysdate
238: ORDER BY 11 ASC
239: )
240: WHERE ROWNUM<2; --We need only the last recent apptmt.
244: SELECT dock_id , milk_concatenated_segments, dock_appointment_id,organization_id,
245: trip_stop, trip_id, subinventory_code,concatenated_segments , is_loaded , trip_name
246: FROM (
247: SELECT distinct wda.dock_id,
248: inv_project.get_locsegs(milk.inventory_location_id, milk.organization_id) milk_concatenated_segments,
249: wda.dock_appointment_id, wda.organization_id, wda.trip_stop, wts.trip_id,
250: milk1.subinventory_code, milk1.concatenated_segments ,
251: WMS_SHIPPING_TRANSACTION_PUB.is_loaded(p_organization_id,wda.dock_id,'Y') is_loaded ,
252: wt.name trip_name , ( wda.start_time - SYSDATE) next_schedule_time_diff
261: and wts.status_code = 'OP'
262: and milk.organization_id = p_organization_id
263: and milk1.organization_id (+) = wda.organization_id
264: and milk1.inventory_location_id (+) = wda.staging_Lane_Id
265: and inv_project.get_locsegs(milk.inventory_location_id, milk.organization_id) like (p_dock_door)
266: and nvl(milk.disable_date,sysdate+1) > sysdate
267: ORDER BY 11 ASC
268: )
269: WHERE ROWNUM<2; --We need only one (the immediate next apptmt).
276: -- segments should be suppressed from the KFV in the LOV. Alias "milk_concatenated_segments"
277: -- is used to avoid "unambiguous column name" error
278: open x_dock_door_lov for
279: select milk.inventory_location_id,
280: inv_project.get_locsegs(milk.inventory_location_id, milk.organization_id)
281: milk_concatenated_segments
282: ,0, milk.organization_id,
283: 0,0,'','',is_loaded(p_organization_id,milk.inventory_location_id,'N')
284: ,''
284: ,''
285: from mtl_item_locations_kfv milk
286: where inventory_location_type = 1
287: AND milk.organization_id = p_organization_id
288: AND inv_project.get_locsegs(milk.inventory_location_id, milk.organization_id) like (p_dock_door)
289: --AND segment19 IS NULL -- Bug 5336849, As a release policy
290: -- the code should not depend on FF view during patch installation
291: -- so changing the logic
292: AND nvl(milk.physical_location_id,milk.inventory_location_id) = milk.inventory_location_id
5421: -- segments should be suppressed from the KFV in the LOV. Alias "milk_concatenated_segments"
5422: -- is used to avoid "unambiguous column name" error
5423: open x_dock_door_lov for
5424: select DISTINCT milk.inventory_location_id,
5425: inv_project.get_locsegs(milk.inventory_location_id, milk.organization_id)
5426: milk_concatenated_segments,
5427: 0, milk.organization_id,
5428: 0,0,'','',is_loaded(p_organization_id,milk.inventory_location_id,'N')
5429: ,''
5432: where inventory_location_type = 1
5433: and milk.organization_id = p_organization_id
5434: AND milk.organization_id = wstt.organization_id
5435: AND milk.inventory_location_id = wstt.dock_door_id
5436: and inv_project.get_locsegs(milk.inventory_location_id, milk.organization_id) like (p_dock_door)
5437: order by milk_concatenated_segments;
5438:
5439: END GET_LOADED_DOCK_DOORS;
5440: