[Home] [Help]
46: CURSOR c_get_deliveries (p_tripid NUMBER) IS
47: SELECT dl.delivery_id, dl.planned_flag, dl.status_code, dl.ignore_for_planning, dl.name delivery_name,
48: dl.organization_id organization_id, -- LPN CONV. rv
49: nvl(dl.shipment_direction,'O') shipment_direction -- LPN CONV. rv
50: FROM wsh_trips t, wsh_trip_stops st, wsh_delivery_legs dg, wsh_new_deliveries dl
51: WHERE t.trip_id = p_tripid AND
52: st.trip_id = t.trip_id AND
53: dg.pick_up_stop_id = st.stop_id AND
54: dl.delivery_id = dg.delivery_id;
54: dl.delivery_id = dg.delivery_id;
55:
56: CURSOR c_get_dels_diff_ignoreflag (p_delid NUMBER, p_tripid NUMBER, p_ignoreplan VARCHAR2) IS
57: SELECT dl.name delivery_name, t.name trip_name
58: FROM wsh_trips t, wsh_trip_stops st, wsh_delivery_legs dg, wsh_new_deliveries dl
59: WHERE t.trip_id <> p_tripid AND
60: st.trip_id = t.trip_id AND
61: dg.pick_up_stop_id = st.stop_id AND
62: dl.delivery_id = dg.delivery_id AND
65: -- OR t.planned_flag='F'); --change in design - firm check not needed
66:
67: CURSOR c_get_del_trips (p_delid NUMBER, p_tripid NUMBER) IS
68: SELECT t.trip_id, t.name trip_name
69: FROM wsh_trips t, wsh_trip_stops st, wsh_delivery_legs dg
70: WHERE t.trip_id <> p_tripid AND
71: st.trip_id = t.trip_id AND
72: dg.pick_up_stop_id = st.stop_id AND
73: dg.delivery_id=p_delid;
74: -- AND t.planned_flag <> 'F'; --change in design - firm check not needed
75:
76: CURSOR c_get_del_ignoreflag_difftrip (p_delid NUMBER, p_ignoreplan VARCHAR2) IS
77: SELECT dl.name delivery_name, t.name trip_name
78: FROM wsh_trips t, wsh_trip_stops st, wsh_delivery_legs dg, wsh_new_deliveries dl
79: WHERE st.trip_id = t.trip_id AND
80: dg.pick_up_stop_id = st.stop_id AND
81: dl.delivery_id = dg.delivery_id AND
82: dl.delivery_id=p_delid AND
1353: CURSOR c_deliveries (c_trip_id NUMBER) IS
1354: SELECT dg.delivery_id
1355: FROM wsh_trips t,
1356: wsh_trip_stops st,
1357: wsh_delivery_legs dg
1358: WHERE t.trip_id = c_trip_id AND
1359: st.trip_id = t.trip_id AND
1360: dg.pick_up_stop_id = st.stop_id;
1361:
1364: CURSOR c_trips (c_trip_id NUMBER, c_delid NUMBER) IS
1365: SELECT t.trip_id
1366: FROM wsh_trips t,
1367: wsh_trip_stops st,
1368: wsh_delivery_legs dg
1369: WHERE t.trip_id <> c_trip_id AND
1370: st.trip_id = t.trip_id AND
1371: dg.pick_up_stop_id = st.stop_id AND
1372: dg.delivery_id=c_delid AND
1377: CURSOR c_find_planunplandeliveries (c_tripid NUMBER, c_delid NUMBER) IS
1378: SELECT dg.delivery_id
1379: FROM wsh_trips t,
1380: wsh_trip_stops st,
1381: wsh_delivery_legs dg,
1382: wsh_new_deliveries dl
1383: WHERE t.trip_id = c_tripid AND
1384: st.trip_id = t.trip_id AND
1385: dg.pick_up_stop_id = st.stop_id AND
1393: -- Bug 3294663, bug 3687559 need to find trips and their firm status
1394: CURSOR c_dlvy_trips (c_delid NUMBER) IS
1395: SELECT distinct st.trip_id, t.planned_flag
1396: FROM wsh_trip_stops st,
1397: wsh_delivery_legs dg,
1398: wsh_trips t
1399: WHERE dg.pick_up_stop_id = st.stop_id AND
1400: dg.delivery_id=c_delid AND
1401: st.trip_id = t.trip_id;
1660:
1661: CURSOR c_deliveries IS
1662: SELECT dg.delivery_id
1663: FROM wsh_trip_stops st,
1664: wsh_delivery_legs dg,
1665: wsh_new_deliveries nd
1666: WHERE st.trip_id = p_entity_id AND
1667: dg.pick_up_stop_id = st.stop_id AND
1668: nd.delivery_id=dg.delivery_id AND
1672: CURSOR c_trips (p_delid NUMBER) IS
1673: SELECT t.trip_id
1674: FROM wsh_trips t,
1675: wsh_trip_stops st,
1676: wsh_delivery_legs dg
1677: WHERE t.trip_id <> p_entity_id AND
1678: st.trip_id = t.trip_id AND
1679: dg.pick_up_stop_id = st.stop_id AND
1680: dg.delivery_id=p_delid
1685: CURSOR c_dlvy_trips IS
1686: SELECT t.trip_id
1687: FROM wsh_trips t,
1688: wsh_trip_stops st,
1689: wsh_delivery_legs dg
1690: WHERE st.trip_id = t.trip_id AND
1691: dg.pick_up_stop_id = st.stop_id AND
1692: dg.delivery_id=p_entity_id
1693: and t.planned_flag='F';