[Home] [Help]
124: msnt.serial_attribute_category
125: --Bug 8467875
126: from wsh_delivery_details dd,
127: wsh_delivery_assignments_v da,
128: wsh_delivery_legs dg,
129: wsh_new_deliveries dl,
130: wsh_trip_stops st,
131: mtl_serial_numbers_temp msnt
132: where st.batch_id = p_batch_id
219: msnt.serial_attribute_category
220: --Bug 8467875
221: from wsh_delivery_details dd,
222: wsh_delivery_assignments_v da,
223: wsh_delivery_legs dg,
224: wsh_new_deliveries dl,
225: wsh_trip_stops st,
226: mtl_serial_numbers_temp msnt
227: where st.batch_id = p_batch_id
314: msn.serial_attribute_category
315: --Bug 8467875
316: from wsh_delivery_details dd,
317: wsh_delivery_assignments_v da,
318: wsh_delivery_legs dg,
319: wsh_new_deliveries dl,
320: wsh_trip_stops st,
321: mtl_serial_numbers msn
322: where st.batch_id = p_batch_id
409: msn.serial_attribute_category
410: --Bug 8467875
411: from wsh_delivery_details dd,
412: wsh_delivery_assignments_v da,
413: wsh_delivery_legs dg,
414: wsh_new_deliveries dl,
415: wsh_trip_stops st,
416: mtl_serial_numbers msn
417: where st.batch_id = p_batch_id
893: WHERE transaction_temp_id IN
894: ( SELECT DISTINCT dd.transaction_temp_id
895: from wsh_delivery_details dd,
896: wsh_delivery_assignments da,
897: wsh_delivery_legs dg,
898: wsh_new_deliveries dl,
899: wsh_trip_stops st,
900: mtl_serial_numbers_temp msnt
901: where st.batch_id = p_batch_id
926: WHERE transaction_temp_id IN
927: ( SELECT DISTINCT dd.transaction_temp_id
928: from wsh_delivery_details dd,
929: wsh_delivery_assignments da,
930: wsh_delivery_legs dg,
931: wsh_new_deliveries dl,
932: wsh_trip_stops st,
933: mtl_serial_numbers_temp msnt
934: where st.batch_id = p_batch_id
1313: CURSOR get_stops(p_batch_id NUMBER) is
1314: SELECT DISTINCT wts.stop_id
1315: FROM wsh_trip_stops wts,
1316: wsh_trip_stops wts2,
1317: wsh_delivery_legs wdl,
1318: wsh_delivery_legs wdl2,
1319: wsh_delivery_assignments_v wda,
1320: wsh_delivery_assignments_v wda2,
1321: wsh_delivery_details wdd,
1314: SELECT DISTINCT wts.stop_id
1315: FROM wsh_trip_stops wts,
1316: wsh_trip_stops wts2,
1317: wsh_delivery_legs wdl,
1318: wsh_delivery_legs wdl2,
1319: wsh_delivery_assignments_v wda,
1320: wsh_delivery_assignments_v wda2,
1321: wsh_delivery_details wdd,
1322: wsh_delivery_details wdd2
1591: -- Lookup stop for this delivery
1592: CURSOR c_delivery_stop(p_delivery_id NUMBER) IS
1593: SELECT wts.stop_id
1594: FROM wsh_trip_stops wts,
1595: wsh_delivery_legs wdl,
1596: wsh_new_deliveries wnd
1597: WHERE wnd.delivery_id = p_delivery_id
1598: AND wdl.delivery_id = wnd.delivery_id
1599: AND wts.stop_id = wdl.pick_up_stop_id
1622: CURSOR c_stops_org(p_organization_id NUMBER) IS
1623: SELECT DISTINCT wts.stop_id
1624: FROM wsh_trip_stops wts,
1625: wsh_new_deliveries wnd,
1626: wsh_delivery_legs wdl
1627: WHERE wts.pending_interface_flag = 'Y'
1628: AND wdl.pick_up_stop_id = wts.stop_id
1629: AND wnd.initial_pickup_location_id = wts.stop_location_id
1630: AND wdl.delivery_id = wnd.delivery_id
1635:
1636: CURSOR c_batch_stop(p_batch_id NUMBER, p_trip_type VARCHAR2)IS
1637: SELECT wts.stop_id
1638: FROM wsh_trip_stops wts,
1639: wsh_delivery_legs wdl,
1640: wsh_new_deliveries wnd,
1641: wsh_picking_batches wpb
1642: WHERE p_batch_id IS NOT NULL
1643: AND wnd.batch_id = p_batch_id
1653: NULL,2,3);
1654:
1655: CURSOR pickup_oke_headers (p_stop_id in number) IS
1656: SELECT 1
1657: FROM wsh_delivery_legs dg,
1658: wsh_new_deliveries dl,
1659: wsh_trip_stops st
1660: WHERE st.stop_id = dg.pick_up_stop_id AND
1661: nvl(dl.SHIPMENT_DIRECTION , 'O') IN ('O', 'IO') AND -- J Inbound Logistics jckwok
1677: wnd.initial_pickup_location_id,
1678: wnd.delivery_scpod_wf_process,
1679: wnd.del_wf_interface_attr
1680: FROM wsh_new_deliveries wnd,
1681: wsh_delivery_legs wdl,
1682: wsh_trip_stops wts
1683: WHERE wnd.delivery_id = wdl.delivery_id
1684: AND wdl.pick_up_stop_id = p_stop_id
1685: AND wts.stop_id = wdl.pick_up_stop_id
3347: dd.ship_to_site_use_id ship_to_site_use_id
3348: --
3349: FROM wsh_delivery_details dd,
3350: wsh_delivery_assignments_v da,
3351: wsh_delivery_legs dg,
3352: wsh_new_deliveries dl,
3353: wsh_trip_stops st,
3354: wsh_trips tr,
3355: wsh_carriers wcv,
4935: -- bug 5736840
4936:
4937: CURSOR get_details (p_batch_id IN NUMBER) IS
4938: SELECT da.delivery_detail_id
4939: FROM wsh_delivery_assignments da , wsh_delivery_legs dg, wsh_new_deliveries dl, wsh_trip_stops st
4940: where dl.delivery_id = da.delivery_id
4941: AND da.delivery_id IS NOT NULL
4942: AND st.stop_id = dg.pick_up_stop_id
4943: AND st.batch_id = p_batch_id
4956: CURSOR l_get_picking_ln_id_csr (p_batch_id IN NUMBER) IS
4957: SELECT mti.picking_line_id
4958: FROM mtl_transactions_interface mti,
4959: wsh_delivery_assignments_v da ,
4960: wsh_delivery_legs dg,
4961: wsh_new_deliveries dl,
4962: wsh_delivery_details wdd,
4963: wsh_trip_stops st
4964: WHERE mti.picking_line_id = da.delivery_detail_id
4976: SELECT DISTINCT wnd.delivery_id
4977: from wsh_delivery_details wdd,
4978: wsh_delivery_assignments_v wda,
4979: wsh_new_deliveries wnd,
4980: wsh_delivery_legs wdl,
4981: wsh_trip_stops wts
4982: where wdd.source_code = 'RTV'
4983: and nvl(wdd.inv_interfaced_flag, 'N') = 'N'
4984: AND wdd.container_flag = 'N'
5531: wsh_new_deliveries wnd
5532: where wdd.source_line_id=p_source_line_id and
5533: wdd.source_code = 'OE' and
5534: wdd.delivery_detail_id=da.delivery_detail_id and
5535: not exists (select 1 from wsh_delivery_legs lg, wsh_trip_stops st
5536: where st.batch_id= p_batch_id and
5537: st.stop_id = lg.pick_up_stop_id and
5538: nvl(da.delivery_id,0)= lg.delivery_id)
5539: and wnd.delivery_id(+) = da.delivery_id
5813: AND NOT EXISTS
5814: ( SELECT 'x'
5815: FROM wsh_delivery_assignments_v wda,
5816: wsh_new_deliveries wnd ,
5817: wsh_delivery_legs wdl ,
5818: wsh_trip_stops wts
5819: WHERE wdd.delivery_detail_id = wda.delivery_detail_id
5820: AND wda.delivery_id = wnd.delivery_id
5821: AND wda.delivery_id IS NOT NULL
5905: l_tot_dd_shp_qty2,
5906: l_client_id -- LSP PROJECT
5907: FROM wsh_delivery_Details dd ,
5908: wsh_delivery_assignments_v da ,
5909: wsh_delivery_legs dg ,
5910: wsh_new_deliveries dl ,
5911: wsh_trip_stops st
5912: WHERE st.stop_id = dg.pick_up_stop_id
5913: AND st.batch_id = P_batch_id
6401: and nvl(inv_interfaced_flag , 'N') <> 'X'
6402: and dd.delivery_Detail_id in (
6403: SELECT da.delivery_detail_id
6404: FROM wsh_delivery_assignments_v da ,
6405: wsh_delivery_legs dg,
6406: wsh_new_deliveries dl,
6407: wsh_trip_stops st
6408: where dl.delivery_id = da.delivery_id AND
6409: da.delivery_id IS NOT NULL AND
6556: CURSOR c_failed_stops (p_batch_id NUMBER) IS
6557: SELECT DISTINCT stop_id
6558: FROM wsh_delivery_Details dd,
6559: wsh_delivery_assignments_v da ,
6560: wsh_delivery_legs dg,
6561: wsh_new_deliveries dl,
6562: wsh_trip_stops st
6563: WHERE st.stop_id = dg.pick_up_stop_id
6564: AND st.batch_id = p_batch_id
6976: MINUS
6977: SELECT wdd.delivery_detail_id
6978: from wsh_delivery_details wdd,
6979: wsh_delivery_assignments_v wda,
6980: wsh_delivery_legs wdl,
6981: wsh_trip_stops wts
6982: where wts.batch_id = p_batch_id
6983: and wdl.pick_up_stop_id = wts.stop_id
6984: and wda.delivery_id = wdl.delivery_id
6998: MINUS
6999: select wdd.delivery_detail_id
7000: from wsh_delivery_details wdd,
7001: wsh_delivery_assignments_v wda,
7002: wsh_delivery_legs wdl,
7003: wsh_trip_stops wts
7004: where wts.batch_id = p_batch_id
7005: and wdl.pick_up_stop_id = wts.stop_id
7006: and wda.delivery_id = wdl.delivery_id
7018: MINUS
7019: SELECT wdd.delivery_detail_id
7020: from wsh_delivery_details wdd,
7021: wsh_delivery_assignments_v wda,
7022: wsh_delivery_legs wdl,
7023: wsh_trip_stops wts
7024: where wts.batch_id = p_batch_id
7025: and wdl.pick_up_stop_id = wts.stop_id
7026: and wda.delivery_id = wdl.delivery_id
7496: CURSOR c_get_stop_lines (p_stop_id NUMBER) IS
7497: SELECT wdd.source_line_id
7498: FROM wsh_delivery_details wdd,
7499: wsh_trip_stops wts,
7500: wsh_delivery_legs wdl,
7501: wsh_delivery_assignments_v wda
7502: WHERE wts.stop_id = p_stop_id
7503: AND wdl.pick_up_stop_id = wts.stop_id
7504: AND wdl.delivery_id = wda.delivery_id
7780: and released_status <> l_released_status
7781: and dd.delivery_detail_id in (
7782: SELECT /*+ no_unnest */ da.delivery_detail_id
7783: FROM wsh_delivery_assignments_v da ,
7784: wsh_delivery_legs dg,
7785: wsh_new_deliveries dl,
7786: wsh_trip_stops st
7787: where da.delivery_detail_id = dd.delivery_detail_id AND
7788: dl.delivery_id = da.delivery_id AND
7866: last_updated_by = fnd_global.user_id
7867: where delivery_detail_id in (
7868: SELECT da.delivery_detail_id
7869: FROM wsh_delivery_assignments_v da ,
7870: wsh_delivery_legs dg,
7871: wsh_new_deliveries dl,
7872: wsh_trip_stops st,
7873: oe_order_lines_all ol
7874: where da.delivery_detail_id = dd.delivery_detail_id AND
7906: and released_status <> l_released_status
7907: and dd.delivery_detail_id in (
7908: SELECT /*+ no_unnest */ da.delivery_detail_id
7909: FROM wsh_delivery_assignments_v da ,
7910: wsh_delivery_legs dg,
7911: wsh_new_deliveries dl,
7912: wsh_trip_stops st
7913: where da.delivery_detail_id = dd.delivery_detail_id AND
7914: dl.delivery_id = da.delivery_id AND
7969: and released_status <> l_released_status
7970: and dd.delivery_detail_id in (
7971: SELECT /*+ no_unnest */ da.delivery_detail_id
7972: FROM wsh_delivery_assignments_v da ,
7973: wsh_delivery_legs dg,
7974: wsh_new_deliveries dl,
7975: wsh_trip_stops st
7976: where da.delivery_detail_id = dd.delivery_detail_id AND
7977: dl.delivery_id = da.delivery_id AND
8347: CURSOR lock_delivery_line(p_batch_id NUMBER,c_source_header_id NUMBER,c_source_line_id NUMBER) IS
8348: SELECT dd.source_line_id
8349: FROM wsh_delivery_Details dd,
8350: wsh_delivery_assignments_v da ,
8351: wsh_delivery_legs dg,
8352: wsh_new_deliveries dl,
8353: wsh_trip_stops st
8354: WHERE st.stop_id = dg.pick_up_stop_id AND
8355: st.batch_id = p_batch_id AND
8376: AND wdd.released_status <> 'D'
8377: AND EXISTS (
8378: SELECT 'X'
8379: FROM wsh_trip_stops wts,
8380: wsh_delivery_legs wdl,
8381: wsh_delivery_assignments_v wda
8382: WHERE wdd.delivery_detail_id = wda.delivery_detail_id
8383: AND wts.batch_id = c_batch_id
8384: AND wts.stop_id = wdl.pick_up_stop_id
8397: AND wdd.released_status <> 'D'
8398: AND EXISTS (
8399: SELECT 'X'
8400: FROM wsh_trip_stops wts,
8401: wsh_delivery_legs wdl,
8402: wsh_delivery_assignments_v wda
8403: WHERE wdd.delivery_detail_id = wda.delivery_detail_id
8404: AND wts.batch_id = c_batch_id
8405: AND wts.stop_id = wdl.pick_up_stop_id
8428: and not exists (
8429: select 'x'
8430: from wsh_delivery_assignments_v wda,
8431: wsh_new_deliveries wnd,
8432: wsh_delivery_legs wdl,
8433: wsh_trip_stops wts
8434: where wdd.delivery_detail_id = wda.delivery_detail_id
8435: and wda.delivery_id = wnd.delivery_id
8436: and wda.delivery_id is not null
8517: from wsh_delivery_details
8518: where source_line_id in (select source_line_id
8519: from wsh_delivery_details wdd,
8520: wsh_delivery_assignments_v wda,
8521: wsh_delivery_legs wdl,
8522: wsh_trip_stops wts
8523: where wts.batch_id = cp_batch_id
8524: and wdl.pick_up_stop_id = wts.stop_id
8525: and wda.delivery_id = wdl.delivery_id
8530: and (delivery_detail_id not in
8531: (select wdd.delivery_detail_id
8532: from wsh_delivery_details wdd,
8533: wsh_delivery_assignments_v wda,
8534: wsh_delivery_legs wdl,
8535: wsh_trip_stops wts
8536: where wts.batch_id = cp_batch_id
8537: and wdl.pick_up_stop_id = wts.stop_id
8538: and wda.delivery_id = wdl.delivery_id
8552:
8553: CURSOR check_bulk_csr (cp_batch_id NUMBER) IS
8554: select 'X'
8555: from wsh_trip_stops wts,
8556: wsh_delivery_legs wdl,
8557: wsh_delivery_assignments wda ,
8558: wsh_delivery_details wdd
8559: where wts.batch_id = cp_batch_id
8560: and wdl.pick_up_stop_id = wts.stop_id
8570: and wdd2.released_status <> 'D'
8571: and wdd2.delivery_detail_id NOT IN
8572: (select wda3.delivery_detail_id
8573: from wsh_delivery_assignments wda3 ,
8574: wsh_delivery_legs wdl3,
8575: wsh_trip_stops wts3,
8576: wsh_delivery_details wdd3
8577: where wts3.batch_id = cp_batch_id
8578: and wdl3.pick_up_stop_id = wts3.stop_id
8615: quantity => nvl(dd.shipped_quantity2,0),
8616: item_id => dd.inventory_item_id)))) shipped_quantity
8617: FROM wsh_delivery_Details dd,
8618: wsh_delivery_assignments_v da ,
8619: wsh_delivery_legs dg,
8620: wsh_new_deliveries dl,
8621: wsh_trip_stops st,
8622: oe_order_lines_all ol
8623: WHERE st.stop_id = dg.pick_up_stop_id AND
8696: quantity => nvl(dd.shipped_quantity2, 0 ),
8697: item_id => dd.inventory_item_id)))) total_om_shipped_quantity
8698: FROM wsh_delivery_Details dd,
8699: wsh_delivery_assignments_v da ,
8700: wsh_delivery_legs dg,
8701: wsh_new_deliveries dl,
8702: wsh_trip_stops st,
8703: oe_order_lines_all ol
8704: WHERE st.stop_id = dg.pick_up_stop_id AND
9762:
9763: where delivery_detail_id in (
9764: SELECT da.delivery_detail_id
9765: FROM wsh_delivery_assignments_v da ,
9766: wsh_delivery_legs dg,
9767: wsh_new_deliveries dl,
9768: wsh_trip_stops st
9769: where dl.delivery_id = da.delivery_id AND
9770: da.delivery_id IS NOT NULL AND
10163: /* H integration: added 'WSH' to validate for inventory interface wrudge */
10164: CURSOR c_lines_not_interfaced(p_batch_id NUMBER) IS
10165: SELECT wdd.delivery_detail_id
10166: FROM wsh_trip_stops wts,
10167: wsh_delivery_legs wdl,
10168: wsh_delivery_assignments_v wda,
10169: wsh_delivery_details wdd
10170: WHERE wdd.inv_interfaced_flag IN ('N', 'P')
10171: AND wts.batch_id = p_batch_id
10231: FUNCTION OM_INTERFACED ( p_batch_id in number ) RETURN BOOLEAN is
10232: CURSOR c_lines_not_interfaced(p_batch_id NUMBER) IS
10233: SELECT wdd.delivery_detail_id
10234: FROM wsh_trip_stops wts,
10235: wsh_delivery_legs wdl,
10236: wsh_delivery_assignments_v wda,
10237: wsh_delivery_details wdd
10238: WHERE -- wdd.oe_interfaced_flag <> 'Y'
10239: wdd.oe_interfaced_flag NOT IN ( 'Y' ,'X') --Standalone WMS project changes