146: /* is derived in the function "check_dkdr_trpstp_exists".For every staging lane associated with */
147: /* the dock door, this procedure calls function "check_assigned_staginglanes"( checks for staging */
148: /* lanes which have been already assigned). If an available staging lane is not found, then */
149: /* we check for the earliest available staging lane and return that staging lane to the calling */
150: /* program as well as updated in table "wms_dock_appointments_b" for the trip stop in question. */
151: /****************************************************************************************************/
152:
153: FUNCTION get_available_staginglane(
154: x_return_status OUT NOCOPY VARCHAR2
312: END get_available_staginglane;
313: ----------------------------------------------------------------------------------------------------------------
314: /****************************************************************************************************/
315: /* For every staging lane returned by the cursor "get_stglanes_for_dkdr_cur", we check if */
316: /* it exists in table wms_dock_appointments_b. If it exists then we move on to the next */
317: /* staging lane assigned to the dock door. The staging lane (p_stg_lane_id) is returned if */
318: /* this staging lane has not been assigned to any appointments in wms_dock_appointments_b */
319: /****************************************************************************************************/
320:
314: /****************************************************************************************************/
315: /* For every staging lane returned by the cursor "get_stglanes_for_dkdr_cur", we check if */
316: /* it exists in table wms_dock_appointments_b. If it exists then we move on to the next */
317: /* staging lane assigned to the dock door. The staging lane (p_stg_lane_id) is returned if */
318: /* this staging lane has not been assigned to any appointments in wms_dock_appointments_b */
319: /****************************************************************************************************/
320:
321: FUNCTION check_if_stagelane_assigned(
322: x_return_status OUT NOCOPY VARCHAR2
339: -- to take time of the day into consideration.
340:
341: SELECT 1 into l_dummy
342: FROM DUAL WHERE EXISTS (SELECT staging_lane_id
343: FROM wms_dock_appointments_b
344: WHERE staging_lane_id = p_stg_lane_id
345: and end_time>=sysdate);
346:
347: -- Bug 3122401. SQL was considering old appointments also. This is
406:
407: BEGIN
408: SELECT staging_lane_id, to_char(end_time, 'DD-MON-YY HH:MI:SS')
409: INTO x_stage_id, l_date_end_time
410: FROM wms_dock_appointments_b
411: WHERE end_time = (SELECT min(end_time)
412: FROM wms_dock_appointments_b
413: WHERE dock_id = p_dock_id
414: AND staging_lane_id is not NULL
408: SELECT staging_lane_id, to_char(end_time, 'DD-MON-YY HH:MI:SS')
409: INTO x_stage_id, l_date_end_time
410: FROM wms_dock_appointments_b
411: WHERE end_time = (SELECT min(end_time)
412: FROM wms_dock_appointments_b
413: WHERE dock_id = p_dock_id
414: AND staging_lane_id is not NULL
415: AND end_time>=sysdate)
416: AND staging_lane_id is not NULL
476:
477: -- Bug 4915199, adding filter on end_date to choose earliest future date.
478: SELECT nvl(dock_id,0), nvl(staging_lane_id ,0)
479: INTO x_dock_id, x_staging_lane_id
480: FROM wms_dock_appointments_b
481: WHERE trip_stop = p_trip_stop
482: and end_time = (SELECT min(end_time)
483: FROM wms_dock_appointments_b
484: WHERE trip_stop = p_trip_stop
479: INTO x_dock_id, x_staging_lane_id
480: FROM wms_dock_appointments_b
481: WHERE trip_stop = p_trip_stop
482: and end_time = (SELECT min(end_time)
483: FROM wms_dock_appointments_b
484: WHERE trip_stop = p_trip_stop
485: and end_time >= sysdate)
486: and rownum = 1;
487:
572: trace('Before update PROCEDURE update_staging_lane_id = ' || p_stage_lane_id);
573: trace('Before update PROCEDURE update_staging_lane_id = ' || p_trip_stop);
574: trace('Before update PROCEDURE update_staging_lane_id = ' || p_dock_id);
575: END IF;
576: UPDATE wms_dock_appointments_b
577: SET
578: STAGING_LANE_ID = p_stage_lane_id,
579: LAST_UPDATED_BY = 1,
580: LAST_UPDATE_DATE = sysdate,