[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
1498: CURSOR c_deliveries (c_trip_id NUMBER) IS
1499: SELECT dg.delivery_id
1500: FROM wsh_trips t,
1501: wsh_trip_stops st,
1502: wsh_delivery_legs dg
1503: WHERE t.trip_id = c_trip_id AND
1504: st.trip_id = t.trip_id AND
1505: dg.pick_up_stop_id = st.stop_id;
1506:
1509: CURSOR c_trips (c_trip_id NUMBER, c_delid NUMBER) IS
1510: SELECT t.trip_id
1511: FROM wsh_trips t,
1512: wsh_trip_stops st,
1513: wsh_delivery_legs dg
1514: WHERE t.trip_id <> c_trip_id AND
1515: st.trip_id = t.trip_id AND
1516: dg.pick_up_stop_id = st.stop_id AND
1517: dg.delivery_id=c_delid AND
1522: CURSOR c_find_planunplandeliveries (c_tripid NUMBER, c_delid NUMBER) IS
1523: SELECT dg.delivery_id
1524: FROM wsh_trips t,
1525: wsh_trip_stops st,
1526: wsh_delivery_legs dg,
1527: wsh_new_deliveries dl
1528: WHERE t.trip_id = c_tripid AND
1529: st.trip_id = t.trip_id AND
1530: dg.pick_up_stop_id = st.stop_id AND
1538: -- Bug 3294663, bug 3687559 need to find trips and their firm status
1539: CURSOR c_dlvy_trips (c_delid NUMBER) IS
1540: SELECT distinct st.trip_id, t.planned_flag
1541: FROM wsh_trip_stops st,
1542: wsh_delivery_legs dg,
1543: wsh_trips t
1544: WHERE dg.pick_up_stop_id = st.stop_id AND
1545: dg.delivery_id=c_delid AND
1546: st.trip_id = t.trip_id;
1805:
1806: CURSOR c_deliveries IS
1807: SELECT dg.delivery_id
1808: FROM wsh_trip_stops st,
1809: wsh_delivery_legs dg,
1810: wsh_new_deliveries nd
1811: WHERE st.trip_id = p_entity_id AND
1812: dg.pick_up_stop_id = st.stop_id AND
1813: nd.delivery_id=dg.delivery_id AND
1817: CURSOR c_trips (p_delid NUMBER) IS
1818: SELECT t.trip_id
1819: FROM wsh_trips t,
1820: wsh_trip_stops st,
1821: wsh_delivery_legs dg
1822: WHERE t.trip_id <> p_entity_id AND
1823: st.trip_id = t.trip_id AND
1824: dg.pick_up_stop_id = st.stop_id AND
1825: dg.delivery_id=p_delid
1830: CURSOR c_dlvy_trips IS
1831: SELECT t.trip_id
1832: FROM wsh_trips t,
1833: wsh_trip_stops st,
1834: wsh_delivery_legs dg
1835: WHERE st.trip_id = t.trip_id AND
1836: dg.pick_up_stop_id = st.stop_id AND
1837: dg.delivery_id=p_entity_id
1838: and t.planned_flag='F';