[Home] [Help]
122: msnt.time_since_repair,
123: msnt.time_since_visit,
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,
217: msnt.time_since_repair,
218: msnt.time_since_visit,
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,
312: msn.time_since_repair,
313: msn.time_since_visit,
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,
407: msn.time_since_repair,
408: msn.time_since_visit,
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,
857:
858:
859: ins_rows := ins_rows + sql%rowcount;
860: -- Bug 6625172: Removing Delete of msnt for each transaction_temp_id from here
861: -- and updating wsh_delivery_details only when p_transfer_param is 'WDD'
862: IF p_transfer_param = 'WDD' THEN
863: /* FORALL i IN 1 .. cur_fetch
864: DELETE FROM mtl_serial_numbers_temp
865: WHERE transaction_temp_id = l_transaction_temp_id(i)
867:
868: del_rows := del_rows + sql%rowcount;
869: ELSE*/
870: FORALL i IN 1 .. cur_fetch
871: UPDATE wsh_delivery_details
872: SET serial_number = NULL,
873: --Added as part of bug 7645262
874: last_update_date = sysdate,
875: request_id = fnd_global.conc_request_id,
891: --{
892: DELETE mtl_serial_numbers_temp
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,
924: --{
925: DELETE mtl_serial_numbers_temp
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,
951:
952: IF l_debug_on THEN
953: WSH_DEBUG_SV.log(l_module_name,'Numbers of rows inserted into wsh_serial_numbers: '|| to_char(ins_rows) ||
954: ', Number of rows deleted from mtl_serial_numbers_temp: '|| to_char(del_rows) ||
955: ', Number of rows updated in wsh_delivery_details: '|| to_char(upd_rows) );
956: WSH_DEBUG_SV.pop(l_module_name);
957: END IF;
958:
959: EXCEPTION
1035: p_batch_id => p_batch_id,
1036: p_interfacing => p_interfacing,
1037: x_return_status => x_return_status );
1038:
1039: -- Case III : 'Serial Number' is present in wsh_delivery_details
1040: transfer_serial_numbers_pvt(
1041: p_transfer_param => 'WDD',
1042: p_batch_id => p_batch_id,
1043: p_interfacing => p_interfacing,
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
1323: WHERE wts.batch_id = p_batch_id
1324: AND wts.stop_id = wdl.pick_up_stop_id
1325: AND wda.delivery_id = wdl.delivery_id
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
1323: WHERE wts.batch_id = p_batch_id
1324: AND wts.stop_id = wdl.pick_up_stop_id
1325: AND wda.delivery_id = wdl.delivery_id
1326: AND wda.delivery_detail_id = wdd.delivery_detail_id
3345: --added for BUG 4538005
3346: dd.ship_from_location_id ship_from_location_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,
3352: wsh_new_deliveries dl,
3353: wsh_trip_stops st,
3354: wsh_trips tr,
3355: wsh_carriers wcv,
3356: wsh_delivery_details dd1,
3357: wsh_delivery_assignments_v da1
3358: WHERE st.stop_id = dg.pick_up_stop_id
3359: AND st.batch_id = p_batch_id
3360: AND st.stop_location_id = dl.initial_pickup_location_id
4796: END IF;
4797:
4798: IF (l_delivery_detail_tbl.count > 0) THEN
4799: FORALL i IN l_delivery_detail_tbl.first..l_delivery_detail_tbl.last
4800: UPDATE wsh_delivery_details
4801: SET inv_interfaced_flag = c_inv_int_full ,
4802: --Added as part of bug 7645262
4803: last_update_date = sysdate,
4804: request_id = fnd_global.conc_request_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
4965: AND wdd.delivery_detail_id = da.delivery_detail_id
4966: AND wdd.source_code <> 'RTV' -- RTV changes
4973:
4974: --RTV changes
4975: CURSOR c_get_rtv_deliveries (p_batch_id IN NUMBER) IS
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
5175: -- Bug#7271241 :removed source_code from where clause as DDs can be
5176: -- associated to OKE system.
5177: IF (nvl(l_detail_ids_count,0) > 0) THEN
5178: FORALL i in l_detail_ids_tbl.FIRST..l_detail_ids_tbl.LAST
5179: update wsh_delivery_details dd
5180: set inv_interfaced_flag = 'N' ,
5181: --Added as part of bug 7645262
5182: last_update_date = sysdate,
5183: request_id = fnd_global.conc_request_id,
5418:
5419: FUNCTION More_Shipment_Exist(p_delivery_id number, p_source_code varchar2, p_source_line_id number) RETURN BOOLEAN is
5420: cursor assigned_line_total is
5421: SELECT count(*) total
5422: from wsh_delivery_details dd,
5423: wsh_delivery_assignments_v da,
5424: wsh_new_deliveries ds
5425: where dd.delivery_detail_id = da.delivery_detail_id
5426: and da.delivery_id = ds.delivery_id
5433: and dd.released_status <> 'D' ; /* H integration: wrudge */
5434: l_assigned_total assigned_line_total%ROWTYPE;
5435: cursor unassigned_line_total is
5436: SELECT count(*) total
5437: from wsh_delivery_details dd,
5438: wsh_delivery_assignments_v da
5439: where dd.delivery_detail_id = da.delivery_detail_id
5440: and da.delivery_id is NULL
5441: and dd.source_line_id = p_source_line_id
5525:
5526: cursor other_batch_delivery_details is
5527: -- muom
5528: select sum(decode(p_fulfill_base,'S',nvl(wdd.picked_quantity2, wdd.requested_quantity2),nvl(wdd.picked_quantity, wdd.requested_quantity)))
5529: from wsh_delivery_details wdd,
5530: wsh_delivery_assignments_v da,
5531: wsh_new_deliveries wnd
5532: where wdd.source_line_id=p_source_line_id and
5533: wdd.source_code = 'OE' and
5797: l_client_id NUMBER;
5798:
5799: CURSOR c_remain_detail_id(c_source_line_id NUMBER) IS
5800: SELECT delivery_detail_id
5801: FROM wsh_delivery_details dd
5802: WHERE source_line_id = c_source_line_id
5803: AND source_code = 'OE'
5804: AND released_status IN ('R', 'B','N','S','X')
5805: AND NVL(container_flag, 'N') = 'N';
5805: AND NVL(container_flag, 'N') = 'N';
5806:
5807: CURSOR c_remain_lines(c_source_line_set_id NUMBER, c_source_header_id NUMBER, p_batch_id NUMBER) IS
5808: SELECT DISTINCT wdd.source_line_id
5809: FROM wsh_delivery_details wdd
5810: WHERE wdd.source_header_id = c_source_header_id
5811: AND wdd.source_code = 'OE'
5812: AND wdd.source_line_set_id = c_source_line_set_id
5813: AND NOT EXISTS
5825: AND wts.batch_id = p_batch_id);
5826:
5827: CURSOR c_picked_dd(c_source_line_id NUMBER, c_source_header_id NUMBER) IS
5828: SELECT 'x'
5829: FROM wsh_delivery_details wdd ,
5830: wsh_delivery_assignments_v wda,
5831: wsh_new_deliveries wnd
5832: WHERE wdd.source_line_id = c_source_line_id
5833: AND wdd.source_code = 'OE'
5903: -- muom
5904: l_tot_dd_req_qty2,
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
5936: --Bug 12823498: If its LSP mode then check whether client_id is populated
5937: ELSIF ( WMS_DEPLOY.WMS_DEPLOYMENT_MODE = 'L' ) THEN
5938: SELECT DISTINCT client_id
5939: INTO l_client_id
5940: FROM wsh_delivery_details
5941: WHERE source_code = 'OE'
5942: AND source_line_id = p_oe_interface_rec.source_line_id;
5943: --}
5944: END IF;
6267: WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_LINE_ID',P_SOURCE_LINE_ID);
6268: END IF;
6269: --
6270: SELECT count(*) into l_count
6271: from wsh_delivery_details
6272: where top_model_line_id is not null
6273: and source_line_id = p_source_line_id
6274: and source_code = p_source_code
6275: and container_flag = 'N'
6319: END IF;
6320: --
6321: if (part_of_pto(p_source_code,p_source_line_id) = TRUE) THEN
6322: SELECT distinct top_model_line_id into l_top_id
6323: from wsh_delivery_details
6324: where source_line_id = p_source_line_id
6325: and source_code = p_source_code
6326: and container_flag = 'N'
6327: and released_status <> 'D' ; /* H integration: wrudge */
6380: --
6381: WSH_DEBUG_SV.log(l_module_name,'p_batch_id',p_batch_id);
6382: END IF;
6383: --
6384: update wsh_delivery_details dd
6385: set inv_interfaced_flag = c_inv_int_full ,
6386: --Added as part of bug 7645262
6387: last_update_date = sysdate,
6388: request_id = fnd_global.conc_request_id,
6554: -- have failed interface to OM.
6555:
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
6968:
6969:
6970: CURSOR top_model_line_csr(p_batch_id NUMBER, p_top_model_line_id NUMBER, p_source_header_id NUMBER) IS
6971: SELECT delivery_detail_id
6972: FROM wsh_delivery_details wdd
6973: WHERE wdd.top_model_line_id = p_top_model_line_id
6974: AND wdd.source_header_id = p_source_header_id -- bug 3642085
6975: AND wdd.source_code = 'OE' -- bug 3642085
6976: MINUS
6974: AND wdd.source_header_id = p_source_header_id -- bug 3642085
6975: AND wdd.source_code = 'OE' -- bug 3642085
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
6990: and wdd.top_model_line_id = p_top_model_line_id;
6991:
6992: CURSOR ship_set_line_csr(p_batch_id NUMBER, p_ship_set_id NUMBER, p_source_header_id NUMBER) IS
6993: Select delivery_detail_id
6994: from wsh_delivery_details wdd
6995: WHERE wdd.ship_set_id = p_ship_set_id
6996: AND wdd.source_header_id = p_source_header_id -- bug 3642085
6997: AND wdd.source_code = 'OE' -- bug 3642085
6998: MINUS
6996: AND wdd.source_header_id = p_source_header_id -- bug 3642085
6997: AND wdd.source_code = 'OE' -- bug 3642085
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
7011: and wdd.ship_set_id = p_ship_set_id;
7012:
7013: CURSOR std_item_line_csr (p_batch_id NUMBER, p_source_line_id NUMBER) IS
7014: SELECT delivery_detail_id
7015: from wsh_delivery_details wdd
7016: WHERE wdd.source_line_id = p_source_line_id
7017: AND wdd.source_code = 'OE' -- bug 3642085
7018: MINUS
7019: SELECT wdd.delivery_detail_id
7016: WHERE wdd.source_line_id = p_source_line_id
7017: AND wdd.source_code = 'OE' -- bug 3642085
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
7494: ORDER BY stop_id ;
7495:
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
7767: l_source_code := 'OE';
7768: l_released_status := 'D';
7769: -- bug 3761090
7770: FORALL i IN l_start_index..l_end_index
7771: UPDATE wsh_delivery_details dd
7772: SET oe_interfaced_flag = l_oe_interfaced_flag ,
7773: --Added as part of bug 7645262
7774: last_update_date = sysdate,
7775: request_id = fnd_global.conc_request_id,
7857: l_container_flag := 'N';
7858: l_source_code := 'OE';
7859: l_released_status := 'D';
7860:
7861: UPDATE wsh_delivery_details dd
7862: set oe_interfaced_flag = l_oe_interfaced_flag ,
7863: --Added as part of bug 7645262
7864: last_update_date = sysdate,
7865: request_id = fnd_global.conc_request_id,
7892: l_source_code := 'OE';
7893: l_released_status := 'D';
7894:
7895: FORALL i IN l_start_index..l_end_index
7896: UPDATE wsh_delivery_details dd
7897: SET oe_interfaced_flag = l_oe_interfaced_flag ,
7898: --Added as part of bug 7645262
7899: last_update_date = sysdate,
7900: request_id = fnd_global.conc_request_id,
7956: l_container_flag := 'N';
7957: l_source_code := 'OE';
7958: l_released_status := 'D';
7959: FORALL i IN l_start_index..l_end_index
7960: UPDATE wsh_delivery_details dd
7961: SET oe_interfaced_flag = Decode(p_bulk_ship_line.error_flag(i),'Y','N','Y') ,
7962: --Added as part of bug 7645262
7963: last_update_date = sysdate,
7964: request_id = fnd_global.conc_request_id,
8345: --Bug 2177678 ,removed the use of oe_order_lines_all
8346: --Now OM locks oe_order_lines_all table for the associated lines
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
8368: CURSOR lock_dds_line_set(c_source_header_id NUMBER,
8369: c_source_line_set_id NUMBER,
8370: c_batch_id NUMBER) IS
8371: SELECT wdd.delivery_detail_id
8372: FROM wsh_delivery_details wdd
8373: WHERE wdd.source_code = 'OE'
8374: AND wdd.source_header_id = c_source_header_id
8375: AND wdd.source_line_set_id = c_source_line_set_id
8376: AND wdd.released_status <> 'D'
8389:
8390: CURSOR lock_dds_line(c_source_header_id NUMBER, c_source_line_id NUMBER,
8391: c_batch_id NUMBER) IS
8392: SELECT wdd.delivery_detail_id
8393: FROM wsh_delivery_details wdd
8394: WHERE wdd.source_code = 'OE'
8395: AND wdd.source_header_id = c_source_header_id
8396: AND wdd.source_line_id = c_source_line_id
8397: AND wdd.released_status <> 'D'
8410:
8411:
8412: CURSOR c_remain_detail_id(c_source_line_id NUMBER) IS
8413: SELECT delivery_detail_id
8414: FROM wsh_delivery_details dd
8415: WHERE source_line_id = c_source_line_id AND
8416: source_code = 'OE' AND
8417: released_status IN ('R', 'B', 'N', 'S', 'X') AND
8418: NVL(container_flag, 'N') = 'N';
8420: CURSOR c_remain_lines(c_source_line_set_id NUMBER,
8421: c_source_header_id NUMBER,
8422: p_batch_id NUMBER) IS
8423: SELECT DISTINCT wdd.source_line_id
8424: from wsh_delivery_details wdd
8425: where wdd.source_header_id = c_source_header_id
8426: and wdd.source_code = 'OE'
8427: and wdd.source_line_set_id = c_source_line_set_id
8428: and not exists (
8441:
8442: CURSOR c_picked_dd(c_source_line_id NUMBER,
8443: c_source_header_id NUMBER) IS
8444: select 'x'
8445: from wsh_delivery_details wdd,
8446: wsh_delivery_assignments_v wda,
8447: wsh_new_deliveries wnd
8448: where wdd.source_line_id = c_source_line_id
8449: and wdd.source_code = 'OE'
8513: --HVOP heali
8514: /*
8515: CURSOR check_bulk_csr (cp_batch_id NUMBER) IS
8516: select 'X'
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,
8515: CURSOR check_bulk_csr (cp_batch_id NUMBER) IS
8516: select 'X'
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
8528: and wdd.source_code = 'OE'
8529: )
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
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
8561: and wda.delivery_id = wdl.delivery_id
8562: and wdd.delivery_detail_id = wda.delivery_detail_id
8562: and wdd.delivery_detail_id = wda.delivery_detail_id
8563: and wdd.container_flag = 'N'
8564: and wdd.source_code = 'OE'
8565: and (EXISTS (select 'any non-cancelled line outside batch'
8566: from wsh_delivery_details wdd2
8567: where wdd2.source_line_id = wdd.source_line_id
8568: and wdd2.source_code = 'OE'
8569: and wdd2.container_flag = 'N'
8570: and wdd2.released_status <> 'D'
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
8579: and wda3.delivery_id = wdl3.delivery_id
8580: and wdd3.delivery_detail_id = wda3.delivery_detail_id
8613: 'S', WSH_WV_UTILS.CONVERT_UOM(from_uom => dd.requested_quantity_uom2,
8614: to_uom => ol.order_quantity_uom,
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,
8694: from_uom => ol.ordered_quantity_uom2,
8695: to_uom => ol.order_quantity_uom,
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,
9752: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
9753: -- mark all 'N' as 'P'
9754:
9755: /* record is already processed by inventory */
9756: update wsh_delivery_details
9757: set inv_interfaced_flag = 'P' ,
9758: --Added as part of bug 7645262
9759: last_update_date = sysdate,
9760: request_id = fnd_global.conc_request_id,
9909: IS
9910:
9911: -- CSUN 01/19/2000 need to modify this cursor once we want to include containers
9912: CURSOR C_Details(p_del_detail_id number) is
9913: SELECT source_line_id, organization_id, org_id from wsh_delivery_details
9914: where delivery_detail_id = p_del_detail_id
9915: and container_flag = 'N'
9916: and released_status <> 'D' ; /* H integration: wrudge */
9917: l_detail_rec c_details%ROWTYPE;
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
10172: AND wts.stop_location_id = wdd.ship_from_location_id
10173: AND wts.stop_id = wdl.pick_up_stop_id
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
10240: AND wts.batch_id = p_batch_id
10241: AND wts.stop_location_id = wdd.ship_from_location_id
10615: SUM( NVL(dd.shipped_quantity, 0 )) total_shipped_quantity,
10616: NULL total_shipped_quantity2,
10617: -- muom
10618: NULL total_om_shipped_quantity
10619: FROM wsh_delivery_Details dd ,
10620: oe_order_lines_all ol
10621: WHERE dd.source_line_id = ol.line_id
10622: AND dd.released_status = 'C'
10623: AND dd.source_code = 'OE'
10623: AND dd.source_code = 'OE'
10624: AND ol.shipped_quantity IS NULL
10625: AND dd.source_line_id IN
10626: ( SELECT DISTINCT dd1.source_line_id
10627: FROM wsh_delivery_details dd1 ,
10628: wsh_delivery_assignments_v da
10629: WHERE da.delivery_id = c_delivery_id
10630: AND da.delivery_detail_id = dd1.delivery_detail_id
10631: AND NVL(dd1.oe_interfaced_flag,'X') = 'X'
10668:
10669: IF l_debug_on THEN
10670: WSH_DEBUG_SV.push(l_module_name);
10671: WSH_DEBUG_SV.log(l_module_name,'delivery_id ',p_delivery_id);
10672: WSH_DEBUG_SV.logmsg(l_module_name, 'Calling program unit WSH_DELIVERY_DETAILS_PKG.Lock_Detail_No_Compare',WSH_DEBUG_SV.C_PROC_LEVEL);
10673: END IF;
10674:
10675: SAVEPOINT l_cancel_wdd;
10676:
10674:
10675: SAVEPOINT l_cancel_wdd;
10676:
10677: --This API will lock the associated delivery details
10678: wsh_delivery_details_pkg.lock_detail_no_compare(p_delivery_id => p_delivery_id);
10679:
10680: /*Get all the details at order line level of the delivery passed which are shipped and not interfaced to OM */
10681: OPEN c_get_oe_interface_line_detail(p_delivery_id);
10682: LOOP
10835: NVL(dd.oe_interfaced_flag, 'X') oe_interfaced_flag ,
10836: ol.flow_status_code ,
10837: SUM( dd.requested_quantity ) total_requested_quantity ,
10838: SUM( NVL(dd.shipped_quantity, 0 )) total_shipped_quantity
10839: FROM wsh_delivery_Details dd ,
10840: oe_order_lines_all ol
10841: WHERE dd.source_line_id = ol.line_id
10842: AND dd.released_status = 'C'
10843: AND dd.source_line_id = c_line_id
10844: AND dd.source_code = 'OE'
10845: and ol.shipped_quantity is NULL
10846: AND NOT EXISTS
10847: ( SELECT 'X'
10848: FROM wsh_delivery_details wdd2
10849: WHERE wdd2.source_line_id = dd.source_line_id
10850: AND wdd2.source_code = 'OE'
10851: AND wdd2.released_status IN ( 'R','B','S','Y','X'))
10852: GROUP BY dd.source_header_id ,
10922: BEGIN
10923: --{
10924: SELECT initial_pickup_date
10925: INTO l_pick_up_date
10926: FROM wsh_delivery_details wdd ,
10927: wsh_delivery_assignments wda,
10928: wsh_new_deliveries wnd
10929: WHERE wdd.source_line_id = line_rec.source_line_id
10930: AND wda.delivery_detail_id = wdd.delivery_detail_id