[Home] [Help]
1048: wdd.inventory_item_id,
1049: wdd.requested_quantity,
1050: msik.concatenated_segments,
1051: msik.description
1052: from wsh_delivery_details_ob_grp_v wdd
1053: ,wsh_delivery_assignments_v wda
1054: ,wsh_new_deliveries_ob_grp_v wnd
1055: ,mtl_system_items_kfv msik
1056: where wnd.delivery_id = p_delivery_id
1126:
1127:
1128: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1129:
1130: l_ignore_for_planning wsh_delivery_details.ignore_for_planning%type;
1131: l_tms_interface_flag wsh_new_deliveries.TMS_INTERFACE_FLAG%type;
1132: l_otm_trip_id wsh_trips.trip_id%type;
1133: l_otm_carrier_id wsh_trips.carrier_id%type;
1134: l_otm_ship_method_code wsh_trips.ship_method_code%type;
2911: , wdd.serial_number
2912: , wdd.source_line_id
2913: , wdd.requested_quantity
2914: , wdd.transaction_temp_id
2915: FROM wsh_delivery_details_ob_grp_v wdd
2916: WHERE wdd.organization_id = p_org_id
2917: AND wdd.container_flag <> 'Y'
2918: AND wdd.delivery_detail_id = p_delivery_detail_id
2919: ORDER BY wdd.source_line_id;
2991: BEGIN
2992: SELECT wdd.delivery_detail_id, wfc.freight_cost_id
2993: INTO l_freight_costs.delivery_detail_id,
2994: l_freight_costs.freight_cost_id
2995: FROM wsh_delivery_details_ob_grp_v wdd,
2996: wsh_freight_costs wfc
2997: WHERE wdd.lpn_id = p_outermost_lpn_id
2998: AND wdd.released_status = 'X' -- For LPN reuse ER : 6845650
2999: AND wdd.delivery_detail_id = wfc.delivery_detail_id;
3080:
3081: --Get the delivery detail id corresponding to the outermost lpn_id
3082: SELECT wdd.delivery_detail_id
3083: INTO l_outermost_dd_id
3084: FROM wsh_delivery_details_ob_grp_v wdd
3085: WHERE lpn_id = p_outermost_lpn_id
3086: AND released_status = 'X'; -- For LPN reuse ER : 6845650
3087:
3088: counter := 0;
3609: FOR UPDATE OF freight_cost_id;
3610:
3611: CURSOR delivery_detail IS
3612: SELECT wdd.delivery_detail_id
3613: FROM wsh_delivery_details_ob_grp_v wdd
3614: WHERE lpn_id = p_lpn_id
3615: AND released_status = 'X'; -- For LPN reuse ER : 6845650
3616:
3617: l_freight_cost_rec wsh_freight_costs_pub.pubfreightcostrectype;
3838: , msnt.cycles_since_visit
3839: , msnt.time_since_mark
3840: , msnt.cycles_since_mark
3841: , msnt.number_of_repairs
3842: FROM wsh_delivery_details_ob_grp_v dd
3843: , mtl_serial_numbers_temp msnt
3844: WHERE delivery_detail_id = p_delivery_detail_id
3845: AND msnt.transaction_temp_id = p_transaction_temp_id;
3846:
3956: END IF;
3957:
3958: SELECT SUM(wdd2.requested_quantity)
3959: INTO l_tot_line_qty
3960: FROM wsh_delivery_details_ob_grp_v wdd1
3961: , wsh_delivery_details_ob_grp_v wdd2
3962: WHERE wdd1.delivery_detail_id = p_delivery_detail_id
3963: AND wdd2.source_header_id = wdd1.source_header_id
3964: AND wdd2.source_line_id = wdd1.source_line_id
3957:
3958: SELECT SUM(wdd2.requested_quantity)
3959: INTO l_tot_line_qty
3960: FROM wsh_delivery_details_ob_grp_v wdd1
3961: , wsh_delivery_details_ob_grp_v wdd2
3962: WHERE wdd1.delivery_detail_id = p_delivery_detail_id
3963: AND wdd2.source_header_id = wdd1.source_header_id
3964: AND wdd2.source_line_id = wdd1.source_line_id
3965: AND wdd2.source_code = wdd1.source_code
4379: IF (g_cross_unit_allowed = 'N') THEN
4380: -- get the end item unit number
4381: SELECT oel.end_item_unit_number
4382: INTO l_end_item_unit_number
4383: FROM oe_order_lines_all oel, wsh_delivery_details_ob_grp_v wdd
4384: WHERE wdd.delivery_detail_id = p_delivery_detail_id
4385: AND wdd.source_header_id = oel.header_id
4386: AND wdd.source_line_id = oel.line_id;
4387:
4566: ROLLBACK;
4567: fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
4568: WHEN OTHERS THEN
4569: x_return_status := wsh_util_core.g_ret_sts_unexp_error;
4570: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_ACTIONS.EXPLODE_DELIVERY_DETAILS');
4571: END explode_delivery_details;
4572:
4573:
4574: /* Function overship_staged_lines
4600: l_msg_count NUMBER;
4601: l_error_code VARCHAR2(20000);
4602: l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4603: l_transaction_temp_id NUMBER;
4604: l_shipping_attr WSH_DELIVERY_DETAILS_PUB.ChangedAttributeTabType;
4605: l_shipping_attr_tab wsh_interface.changedattributetabtype;
4606: l_delivery_detail_id NUMBER;
4607: l_picked_qty NUMBER;
4608: l_shipped_qty NUMBER;
4694: , l_primary_uom_code
4695: , l_lot_control_code
4696: , l_serial_number_control_code
4697: FROM wms_direct_ship_temp wds,
4698: wsh_delivery_details wdd,
4699: mtl_system_items msi
4700: WHERE wds.organization_id = p_organization_id
4701: AND wds.lpn_id = p_lpn_id
4702: AND wds.order_header_id = p_source_header_id
4761:
4762: /*At this point we need to split from this delivery detail*/
4763: DEBUG('Before Split Line'||l_delivery_Detail_id,'Overship Staged Lines');
4764:
4765: WSH_DELIVERY_DETAILS_PUB.split_line
4766: (p_api_version => 1.0,
4767: p_init_msg_list => fnd_api.g_false,
4768: p_commit => fnd_api.g_false,
4769: x_return_status => l_return_status,
4791: END IF;
4792:
4793: /*Unassign from old container*/
4794:
4795: WSH_DELIVERY_DETAILS_ACTIONS.Unassign_Detail_from_Cont(
4796: P_DETAIL_ID => l_new_delivery_detail_id,
4797: X_RETURN_STATUS => l_return_status);
4798:
4799: DEBUG('After Unassign'|| l_return_status, 'Overship Staged Lines');
4905: ,l_delivery_detail_tab(i).PRIMARY_UOM_CODE
4906: ,l_delivery_detail_tab(i).LOT_CONTROL_CODE
4907: ,l_delivery_detail_tab(i).SERIAL_NUMBER_CONTROL_CODE
4908: FROM WMS_DIRECT_SHIP_TEMP WDS,
4909: WSH_DELIVERY_DETAILS WDD,
4910: MTL_SYSTEM_ITEMS MSI
4911: WHERE WDS.ORGANIZATION_ID = p_organization_id
4912: AND WDS.lpn_id = p_lpn_id
4913: AND WDS.ORDER_HEADER_ID = p_source_header_id
4982: AND direct_ship_flag = 'Y';
4983:
4984: CURSOR stage_delivery_detail(p_organization_id NUMBER, p_lpn_id NUMBER) IS
4985: SELECT wdd.delivery_detail_id
4986: FROM wsh_delivery_details_ob_grp_v wdd
4987: , wms_direct_ship_temp wds
4988: WHERE wds.organization_id = p_organization_id
4989: AND wds.lpn_id = p_lpn_id
4990: AND wdd.source_header_id = wds.order_header_id
4994:
4995: /* Bug:2463967 */
4996: CURSOR delete_details(p_outermost_lpn_id NUMBER) IS
4997: SELECT delivery_detail_id
4998: FROM wsh_delivery_details_ob_grp_v wdd
4999: , wms_license_plate_numbers lpn
5000: WHERE lpn.outermost_lpn_id = p_outermost_lpn_id
5001: AND lpn.lpn_id = wdd.lpn_id
5002: AND wdd.released_status = 'X'; -- For LPN reuse ER : 6845650
5091: /**********************************
5092: * variable to hold the ignore_for_planning flag of WDD.
5093: * Added for g-log changes
5094: **********************************/
5095: l_ignore_for_planning wsh_delivery_details.ignore_for_planning%type;
5096: l_tms_interface_flag wsh_new_deliveries.TMS_INTERFACE_FLAG%type;
5097:
5098:
5099:
5139: END IF;
5140:
5141: SELECT delivery_detail_id
5142: INTO l_cont_instance_id
5143: FROM wsh_delivery_details_ob_grp_v
5144: WHERE lpn_id = l_outer_lpn.lpn_id
5145: AND released_status = 'X'; -- For LPN reuse ER : 6845650
5146:
5147: SELECT wdd.delivery_detail_id
5145: AND released_status = 'X'; -- For LPN reuse ER : 6845650
5146:
5147: SELECT wdd.delivery_detail_id
5148: INTO l_delivery_detail_id
5149: FROM wsh_delivery_details_ob_grp_v wdd, wms_direct_ship_temp wds
5150: WHERE wds.organization_id = p_organization_id
5151: AND wds.lpn_id = l_outer_lpn.lpn_id
5152: AND wdd.source_header_id = wds.order_header_id
5153: AND wdd.source_line_id = wds.order_line_id
5332: END IF;
5333:
5334: l_sql_query := l_sql_query ||
5335: ' FROM wms_direct_ship_temp wds, ' ||
5336: ' wsh_delivery_details_ob_grp_v wdd, ' ||
5337: ' mtl_system_items msi ' ||
5338: ' WHERE wds.organization_id = :p_organization_id ' ||
5339: ' AND wds.lpn_id = :p_lpn_id ' ||
5340: ' AND wds.order_header_id = :p_order_header_id ' ||
6065:
6066: --patchset J. Shipping API cleanup
6067: l_delivery_detail_tab(i).requested_quantity := l_delivery_detail_tab(i).requested_quantity - l_rsv_qty;
6068: /* 4440809
6069: WSH_DELIVERY_DETAILS_PUB.split_line
6070: (p_api_version => 1.0,
6071: p_init_msg_list => fnd_api.g_false,
6072: p_commit => fnd_api.g_false,
6073: x_return_status => l_return_status,
6893: l_segmentarray fnd_flex_ext.segmentarray;
6894: l_count NUMBER := 0;
6895: l_net_weight NUMBER;
6896: l_deliv_det_id NUMBER;
6897: l_container_name wsh_delivery_details.container_name%TYPE;
6898: l_container_flag wsh_delivery_details.container_flag%TYPE;
6899: l_container_rec wsh_container_grp.changedattributetabtype;
6900: -- added for bug 2529382
6901: l_status_code VARCHAR2(2);
6894: l_count NUMBER := 0;
6895: l_net_weight NUMBER;
6896: l_deliv_det_id NUMBER;
6897: l_container_name wsh_delivery_details.container_name%TYPE;
6898: l_container_flag wsh_delivery_details.container_flag%TYPE;
6899: l_container_rec wsh_container_grp.changedattributetabtype;
6900: -- added for bug 2529382
6901: l_status_code VARCHAR2(2);
6902: l_container_new_name VARCHAR2(30);
6966: SELECT wdd.released_status
6967: , wdd.delivery_detail_id
6968: INTO l_status_code
6969: , l_delivery_detail_id
6970: FROM wsh_delivery_details_ob_grp_v wdd
6971: WHERE wdd.container_name = l_lpn_cur.license_plate_number
6972: AND wdd.released_status = 'X'; --Bug#6878521 Made change bt need to chk this
6973:
6974: IF l_status_code = 'C' THEN
7042: IF (l_debug = 1) THEN
7043: DEBUG('LPN EXISTS: ' || l_count, 'create_update_containers');
7044: END IF;
7045:
7046: --If the LPN does not exist in in wsh_delivery_details, create a new container
7047: IF l_count = 0 THEN
7048:
7049: /* Release 12 (K): LPN Synchronization
7050: Replace the call to wsh_container_grp.create_containers
7151: SELECT wdd.inventory_item_id
7152: , wdd.revision
7153: , wdd.lot_number
7154: , SUM(wdd.picked_quantity)
7155: FROM wms_shipping_transaction_temp wstt, wsh_delivery_details_ob_grp_v wdd
7156: WHERE wstt.delivery_id = p_delivery_id
7157: AND wdd.delivery_detail_id = wstt.delivery_detail_id
7158: AND wdd.released_status = 'Y'
7159: GROUP BY wdd.inventory_item_id, wdd.revision, wdd.lot_number
7177: l_revision VARCHAR2(3);
7178: -- Increased lot size to 80 Char - Mercy Thomas - B4625329
7179: l_lot_number VARCHAR2(80);
7180: l_del_qty NUMBER;
7181: l_shipping_attr wsh_delivery_details_pub.changedattributetabtype;
7182: l_delivery_detail_id NUMBER;
7183: l_picked_qty NUMBER;
7184: l_extra_qty NUMBER;
7185: l_lpn_qty NUMBER;
7209: --Query to get the total no. of distinct items in the delivery
7210:
7211: SELECT COUNT(COUNT(*))
7212: INTO l_delivery_item_count
7213: FROM wms_shipping_transaction_temp wstt, wsh_delivery_details_ob_grp_v wdd
7214: WHERE wstt.delivery_id = p_delivery_id
7215: AND wdd.delivery_detail_id = wstt.delivery_detail_id
7216: AND wdd.released_status = 'Y'
7217: GROUP BY wdd.inventory_item_id, wdd.revision;
7259: SELECT wdd.delivery_detail_id
7260: , wdd.picked_quantity
7261: INTO l_delivery_detail_id
7262: , l_picked_qty
7263: FROM wms_shipping_transaction_temp wstt, wsh_delivery_details_ob_grp_v wdd
7264: WHERE wstt.delivery_id = p_delivery_id
7265: AND wstt.inventory_item_id = l_item_id
7266: AND wdd.delivery_detail_id = wstt.delivery_detail_id
7267: AND wdd.inventory_item_id = wstt.inventory_item_id
7272: WHEN NO_DATA_FOUND THEN
7273: NULL;
7274: END;
7275:
7276: -- Call WSH_DELIVERY_DETAILS to update the shipped_qty
7277: l_shipping_attr(1).shipped_quantity := l_picked_qty + l_extra_qty;
7278: l_shipping_attr(1).delivery_detail_id := l_delivery_detail_id;
7279: wsh_delivery_details_pub.update_shipping_attributes(
7280: p_api_version_number => 1.0
7275:
7276: -- Call WSH_DELIVERY_DETAILS to update the shipped_qty
7277: l_shipping_attr(1).shipped_quantity := l_picked_qty + l_extra_qty;
7278: l_shipping_attr(1).delivery_detail_id := l_delivery_detail_id;
7279: wsh_delivery_details_pub.update_shipping_attributes(
7280: p_api_version_number => 1.0
7281: , p_source_code => 'OE'
7282: , p_init_msg_list => l_init_msg_list
7283: , p_commit => l_commit
7368: AND parent_lpn_id = l_parent_lpn_id;
7369:
7370: CURSOR container_details(p_organization_id NUMBER, l_lpn_id NUMBER) IS
7371: SELECT delivery_detail_id
7372: FROM wsh_delivery_details_ob_grp_v
7373: WHERE organization_id = p_organization_id
7374: AND lpn_id = l_lpn_id
7375: AND released_status = 'X'; -- For LPN reuse ER : 6845650
7376:
7819: EXIT WHEN lpn_cur%NOTFOUND;
7820:
7821: SELECT delivery_detail_id
7822: INTO l_par_del_det_id
7823: FROM wsh_delivery_details_ob_grp_v
7824: WHERE lpn_id = l_lpn_cur.parent_lpn_id
7825: AND released_status = 'X'; -- For LPN reuse ER : 6845650
7826:
7827: SELECT COUNT(*)
7825: AND released_status = 'X'; -- For LPN reuse ER : 6845650
7826:
7827: SELECT COUNT(*)
7828: INTO l_count_del_assign
7829: FROM wsh_delivery_assignments_v wda, wsh_delivery_details_ob_grp_v wdd
7830: WHERE wda.parent_delivery_detail_id = l_par_del_det_id
7831: AND wda.delivery_detail_id = wdd.delivery_detail_id
7832: AND NVL(wdd.container_flag, 'N') = 'N';
7833:
7904: x_return_status := fnd_api.g_ret_sts_success;
7905:
7906: SELECT COUNT(*)
7907: INTO l_count
7908: FROM wsh_delivery_details_ob_grp_v wdd, wsh_delivery_assignments_v wda, mtl_system_items_kfv msik
7909: WHERE wda.delivery_detail_id = wdd.delivery_detail_id
7910: AND NVL(wdd.container_flag, 'N') = 'N'
7911: AND wda.delivery_id = p_delivery_id
7912: AND wdd.inventory_item_id = msik.inventory_item_id
7948: l_parent_del_detail_ids parent_del_detail_ids;
7949:
7950: CURSOR parent_del_detail IS
7951: SELECT delivery_detail_id
7952: FROM wsh_delivery_details_ob_grp_v
7953: WHERE lpn_id IN(SELECT parent_lpn_id
7954: FROM wms_shipping_transaction_temp
7955: WHERE delivery_id = p_delivery_id
7956: AND direct_ship_flag = 'Y');
7959: SELECT parent_delivery_detail_id
7960: FROM wsh_delivery_assignments_v
7961: WHERE delivery_id = p_delivery_id
7962: AND delivery_detail_id NOT IN(SELECT delivery_detail_id
7963: FROM wsh_delivery_details_ob_grp_v
7964: WHERE lpn_id IN(SELECT parent_lpn_id
7965: FROM wms_shipping_transaction_temp
7966: WHERE delivery_id = p_delivery_id
7967: AND direct_ship_flag = 'Y'));
8000: INTO l_num
8001: FROM DUAL
8002: WHERE EXISTS(
8003: SELECT 1
8004: FROM wsh_delivery_assignments_v wda, wsh_delivery_details_ob_grp_v wdd
8005: WHERE wdd.delivery_detail_id = wda.delivery_detail_id
8006: AND wda.delivery_id = p_delivery_id
8007: AND wdd.released_status = 'Y'
8008: AND NVL(container_flag, 'N') = 'N'
8116:
8117: BEGIN
8118:
8119: SELECT nvl(wdd.source_line_set_id,wdd.source_line_id) into l_line_set_id
8120: FROM wsh_delivery_details wdd
8121: WHERE wdd.source_header_id = p_order_header_id
8122: AND wdd.source_line_id= p_order_line_id
8123: AND rownum<2;
8124:
8127: END IF;
8128:
8129: --Calculate the staged qty for the line set which curr line belongs to.
8130: SELECT nvl(sum(nvl(picked_quantity,0)),0) INTO l_staged_qty
8131: FROM wsh_delivery_details wdd
8132: WHERE source_header_id = p_order_header_id
8133: AND released_status='Y'
8134: AND nvl(source_line_set_id,source_line_id) = l_line_set_id;
8135:
8142: FROM mtl_reservations
8143: WHERE organization_id= p_org_id
8144: AND nvl(staged_flag,'N') = 'Y'
8145: AND demand_source_line_id in ( SELECT source_line_id
8146: FROM wsh_delivery_details wdd
8147: WHERE source_header_id = p_order_header_id
8148: AND released_status='Y'
8149: AND nvl(source_line_set_id,source_line_id) = l_line_set_id );
8150:
8789: FND_MESSAGE.SET_NAME('WMS','WMS_LINE_NOT_BOOKED');
8790: FND_MSG_PUB.ADD;
8791: RETURN;
8792: END IF;*/
8793: --Bug 2909327:Changed the call to check wsh_delivery_details instead of oel*/
8794: BEGIN
8795: SELECT 1
8796: INTO l_order_line_status
8797: FROM DUAL
8795: SELECT 1
8796: INTO l_order_line_status
8797: FROM DUAL
8798: WHERE EXISTS(SELECT delivery_detail_id
8799: FROM wsh_delivery_details_ob_grp_v
8800: WHERE released_status IN('R', 'B')
8801: AND source_line_id = l_order_line_rec.line_id
8802: UNION ALL -- bug 4232713 - Need to consider staged WDD lines for overpicking scenarios
8803: SELECT wdd.delivery_detail_id
8800: WHERE released_status IN('R', 'B')
8801: AND source_line_id = l_order_line_rec.line_id
8802: UNION ALL -- bug 4232713 - Need to consider staged WDD lines for overpicking scenarios
8803: SELECT wdd.delivery_detail_id
8804: FROM wsh_delivery_details_ob_grp_v wdd, wms_direct_ship_temp wds
8805: WHERE wdd.released_status = 'Y'
8806: AND wdd.source_line_id = wds.order_line_id);
8807: EXCEPTION
8808: WHEN NO_DATA_FOUND THEN
10265: , intmed_ship_to_location_id
10266: , ship_method_code
10267: , fob_code
10268: , freight_terms_code
10269: FROM wsh_delivery_details_ob_grp_v
10270: WHERE source_header_id = p_header_id
10271: AND source_line_id = p_line_id;
10272:
10273: l_del_detail_rec del_detail%ROWTYPE;
10367: FUNCTION validate_del_for_ds(p_lpn_id IN NUMBER, p_org_id IN NUMBER, p_dock_door_id IN NUMBER, p_header_id IN NUMBER, p_line_id IN NUMBER)
10368: RETURN BOOLEAN IS
10369: CURSOR del_cur IS
10370: SELECT wda.delivery_id
10371: FROM wsh_delivery_assignments_v wda, wsh_delivery_details_ob_grp_v wdd, wms_direct_ship_temp wds
10372: WHERE wds.organization_id = p_org_id
10373: AND wds.dock_door_id = p_dock_door_id
10374: AND wds.lpn_id = p_lpn_id
10375: AND wds.order_header_id = p_header_id
11057: -- updating existing reservation record
11058:
11059: --Bug#5262108.query the staged qty.
11060: SELECT NVL(SUM(picked_quantity),0) INTO l_staged_qty
11061: FROM wsh_delivery_details
11062: WHERE source_header_id = l_order_header_id
11063: AND source_line_id =l_order_line_id
11064: AND released_status='Y';
11065:
11355: END IF;
11356:
11357: --Bug#6071394.query the staged qty.
11358: SELECT NVL(SUM(picked_quantity),0) INTO l_staged_qty
11359: FROM wsh_delivery_details
11360: WHERE source_header_id = l_order_header_id
11361: AND source_line_id =l_order_line_id
11362: AND released_status='Y';
11363:
11594: x_return_status := fnd_api.g_ret_sts_success;
11595:
11596: SELECT delivery_detail_id
11597: INTO l_delivery_detail_id
11598: FROM wsh_delivery_details_ob_grp_v
11599: WHERE source_header_id = p_order_header_id
11600: AND source_line_id = p_order_line_id
11601: AND ROWNUM = 1;
11602: