[Home] [Help]
2181: Sum(wwl.crossdock_quantity),
2182: -1, wdd.inventory_item_id,
2183: -1 bulk collect into
2184: c_labor_time_tbl
2185: FROM wms_wp_wave_lines wwl, wsh_delivery_details wdd --11775489 get from wdd...FIXED
2186: WHERE wwl.wave_header_id = p_wave_header_id
2187: and wwl.organization_id = wdd.organization_id
2188: and wwl.delivery_detail_id = wdd.delivery_detail_id
2189: GROUP BY wdd.inventory_item_id
2202: Sum(wwl.crossdock_quantity),
2203: -1, wdd.inventory_item_id,
2204: -1 bulk collect into
2205: c_labor_time_tbl
2206: FROM wms_wp_wave_lines wwl, wsh_delivery_details wdd --11775489 get from wdd...FIXED
2207: WHERE wwl.wave_header_id = p_wave_header_id
2208: and wwl.delivery_detail_id = wdd.delivery_detail_id
2209: and wwl.organization_id = wdd.organization_id
2210: GROUP BY wdd.inventory_item_id
2317: inv_salesorder.get_salesorder_for_oeheader(wdd.source_header_id) demand_source_header_id
2318: from wms_wp_wave_headers_vl wwh,
2319: wms_wp_wave_lines wwl, --11775489 get from wdd... FIXED
2320: wms_wp_planning_criteria_vl wwp,
2321: wsh_delivery_details wdd,
2322: wms_wp_rules_simulation wwr
2323: where wwh.wave_header_id = p_wave_header_id
2324: and wwp.planning_criteria_id = p_planning_criteria_id
2325: and wwh.planning_criteria_id = wwp.planning_criteria_id
2401: unit_NUMBER,
2402: preferred_grade,
2403: NULL,
2404: NULL
2405: FROM wsh_delivery_details wdd,
2406: wms_wp_wave_lines wwl, --11775489 added org_id join...FIXED
2407: wms_wp_rules_simulation wwr
2408: WHERE wdd.released_status in ('R', 'B')
2409: AND wwr.delivery_detail_id = wdd.delivery_detail_id
2961: -- FORALL i in l_tbl_first .. l_tbl_last
2962: --dbchange2
2963: FOR i in g_mtrl_tbl.first .. g_mtrl_tbl.last LOOP
2964:
2965: UPDATE wsh_delivery_details
2966: SET move_order_line_id = g_mtrl_tbl(i).line_id
2967: WHERE delivery_detail_id = g_mtrl_tbl(i).TXN_SOURCE_LINE_DETAIL_ID
2968: AND move_order_line_id IS NULL;
2969:
3198: FROM mtl_reservations
3199: WHERE demand_source_line_id IN
3200: (SELECT DISTINCT wdd.source_line_id
3201: FROM WMS_WP_WAVE_LINES wwl,
3202: wsh_delivery_details wdd--11775489 get from wdd... FIXED
3203: WHERE wwl.wave_header_id = p_wave_id
3204: AND wwl.delivery_detail_id = wdd.delivery_detail_id
3205: AND wdd.organization_id = wwl.organization_id
3206: );
3500: FROM mtl_reservations
3501: WHERE demand_source_line_id IN
3502: (SELECT DISTINCT wdd.source_line_id
3503: FROM WMS_WP_WAVE_LINES wwl,
3504: wsh_delivery_details wdd--11775489 get from wdd... FIXED
3505: WHERE wave_header_id = p_wave_id
3506: AND wwl.delivery_detail_id = wdd.delivery_detail_id
3507: AND wdd.organization_id = wwl.organization_id
3508: );
4014: mtrl.pick_methodology_id, mtrl.container_item_id, mtrl.carton_grouping_id, FND_API.G_MISS_CHAR, FND_API.G_MISS_CHAR,
4015: FND_API.G_MISS_CHAR, mtrl.inspection_status, mtrl.wms_process_flag, mtrl.pick_slip_number, mtrl.pick_slip_date,
4016: mtrl.ship_set_id, mtrl.ship_model_id, mtrl.model_quantity, mtrl.required_quantity, mtrl.secondary_quantity,
4017: mtrl.secondary_uom_code, mtrl.secondary_quantity_detailed, mtrl.secondary_quantity_delivered, mtrl.grade_code, mtrl.secondary_required_quantity
4018: FROM mtl_txn_request_lines mtrl, wms_wp_rules_simulation wwr, wsh_delivery_details wdd
4019: WHERE mtrl.TXN_SOURCE_LINE_DETAIL_ID = wwr.delivery_detail_id
4020: AND wwr.delivery_detail_id = wdd.delivery_detail_id
4021: AND p_mode = 'RBP-SS'
4022: and wwr.wave_header_id=p_wave_id
4399:
4400: CURSOR c1 IS
4401: SELECT WDD.ORGANIZATION_ID, WDD.INVENTORY_ITEM_ID, COUNT(wwl.wave_line_id)
4402: FROM wms_wp_wave_lines wwl,
4403: wsh_delivery_details wdd --11775489 get from wdd... FIXED
4404: WHERE wwl.organization_id = p_organization_id
4405: and wwl.wave_header_id = p_wave_id
4406: and wwl.delivery_detail_id = wdd.delivery_detail_id
4407: and wdd.organization_id = wwl.organization_id
4408: GROUP BY WDD.ORGANIZATION_ID, WDD.INVENTORY_ITEM_ID;
4409:
4410: CURSOR c2 IS
4411: SELECT Wdd.ORGANIZATION_ID, DECODE(Wdd.SHIP_SET_ID,NULL,DECODE(Wdd.SHIP_MODEL_COMPLETE_FLAG,'Y',NULL,Wdd.INVENTORY_ITEM_ID),NULL), COUNT(*)
4412: FROM wms_wp_rules_simulation wwl, wsh_delivery_details wdd
4413: WHERE wdd.organization_id = p_organization_id
4414: AND wwl.delivery_detail_id = wdd.delivery_detail_id
4415: and wwl.wave_header_id=p_wave_id
4416: GROUP BY Wdd.ORGANIZATION_ID, DECODE(Wdd.SHIP_SET_ID,NULL,DECODE(Wdd.SHIP_MODEL_COMPLETE_FLAG,'Y',NULL,Wdd.INVENTORY_ITEM_ID),NULL);
4805:
4806:
4807: BEGIN
4808: IF g_update_wdd = 'Y' THEN
4809: UPDATE wsh_delivery_details
4810: SET move_order_line_id=NULL
4811: WHERE delivery_detail_id IN (SELECT delivery_Detail_id
4812: FROM wms_wp_rules_simulation where wave_header_id=p_wave_id);
4813:
5001: END IF;
5002:
5003: BEGIN
5004: print_debug('Update WDD', l_debug);
5005: UPDATE wsh_delivery_details
5006: SET move_order_line_id=NULL
5007: WHERE delivery_detail_id IN (SELECT delivery_Detail_id
5008: FROM wms_wp_rules_simulation where wave_header_id=p_wave_id);
5009:
5059: l_completion_status VARCHAR2(100);
5060:
5061: cursor c_item is
5062: SELECT DISTINCT wdd.inventory_item_id, wwl.organization_id
5063: FROM wms_wp_wave_lines wwl, wsh_delivery_details wdd--11775489 get from wdd...FIXED
5064: WHERE wwl.wave_header_id = p_wave_header_id
5065: and wwl.delivery_detail_id = wdd.delivery_detail_id
5066: and wwl.organization_id = wdd.organization_id
5067: and nvl(remove_from_wave_flag, 'N') <> 'Y';
5088: wwl.wave_line_id
5089: from wms_wp_wave_headers_vl wwh,
5090: wms_wp_wave_lines wwl, --11775489 get from wdd... FIXED...may have performance impact...
5091: wms_wp_planning_criteria_vl wwp,
5092: wsh_delivery_details wdd
5093: where wwh.wave_header_id = p_wave_header_id
5094: and wwl.organization_id = l_org_id
5095: and wdd.inventory_item_id = l_item_id
5096: and wwp.planning_criteria_id = p_planning_criteria_id
5143: l_insufficient_order_number insufficient_order_number;
5144:
5145: cursor c_shipset is
5146: SELECT WDD.DELIVERY_DETAIL_ID
5147: FROM WMS_WP_WAVE_LINES WWL, WSH_DELIVERY_DETAILS WDD --11775489 added org_id join... FIXED
5148: WHERE WWL.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
5149: and WWL.organization_id = wdd.organization_id
5150: AND WWL.WAVE_HEADER_ID = p_wave_header_id
5151: AND WDD.SHIP_SET_ID = v_shipset_no
5153: and v_shipset_no is not null;
5154:
5155: cursor c_model is
5156: SELECT WDD.DELIVERY_DETAIL_ID
5157: FROM WMS_WP_WAVE_LINES WWL, WSH_DELIVERY_DETAILS WDD --11775489 added org_id join... FIXED
5158: WHERE WWL.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
5159: and WWL.organization_id = wdd.organization_id
5160: AND WWL.WAVE_HEADER_ID = p_wave_header_id
5161: AND WDD.top_model_line_id = v_model_no
5164:
5165: cursor c_credit_check is
5166: SELECT wwl.wave_line_id, wdd.source_line_id, wdd.source_header_id
5167: FROM WMS_WP_WAVE_LINES WWL, --11775489 get from wdd... FIXED... check below comment
5168: wsh_delivery_details wdd
5169: WHERE WWL.WAVE_HEADER_ID = p_wave_header_id
5170: and wwl.delivery_detail_id = wdd.delivery_detail_id
5171: and wdd.organization_id = wwl.organization_id
5172: -- and wdd.released_status in ('R','B') --- For Hot Order Changes
5281:
5282: -- For Storing Values in Global Temp Table -- Rules SImulation
5283: cursor c_populate_temp IS
5284: select wwl.delivery_Detail_id
5285: from wms_wp_wave_lines wwl, wsh_delivery_details wdd
5286: where wwl.wave_header_id = p_wave_header_id
5287: and wwl.delivery_detail_id = wdd.delivery_detail_id --11775489 get from wdd... FIXED
5288: and wwl.organization_id = wdd.organization_id;
5289:
5346: (nvl(wts.planned_departure_date,
5347: nvl(wnd.latest_pickup_date, wdd.date_scheduled)))))
5348: into l_target_date
5349: FROM wms_wp_wave_lines wwl, --11775489 get from wdd... FIXED...check performance impact
5350: wsh_delivery_details wdd,
5351: wsh_delivery_assignments wda,
5352: wsh_new_deliveries wnd,
5353: wsh_delivery_legs wdl,
5354: wsh_trip_stops wts,
5381:
5382: insert into wms_wp_rules_simulation
5383: (wave_header_id,delivery_Detail_id, requested_quantity, requested_quantity2)
5384: select wwl.wave_header_id,wwl.delivery_Detail_id, wdd.requested_quantity, wdd.requested_quantity2
5385: from wms_wp_wave_lines wwl, wsh_delivery_details wdd --11775489 get from wdd... FIXED
5386: where wave_header_id = p_wave_header_id
5387: and wdd.released_status in ('R','B') --- For Hot Order Changes;
5388: and wdd.delivery_Detail_id = wwl.delivery_Detail_id
5389: and wdd.organization_id = wwl.organization_id
5587: and (subinventory_code = l_pick_subinventory or
5588: l_pick_subinventory is null);
5589: SELECT Nvl(SUM(wdd.requested_quantity), 0)
5590: INTO l_other_wdd_qty
5591: FROM wsh_delivery_details wdd
5592: WHERE wdd.organization_id = l_org_id
5593: AND wdd.inventory_item_id = l_item_id
5594: and wdd.released_status in ('B', 'E', 'F', 'K', 'R','S') --11822962 only consider wdds where requested material is against a possible reservation
5595: and wdd.delivery_Detail_id not in
5593: AND wdd.inventory_item_id = l_item_id
5594: and wdd.released_status in ('B', 'E', 'F', 'K', 'R','S') --11822962 only consider wdds where requested material is against a possible reservation
5595: and wdd.delivery_Detail_id not in
5596: (select wwl.delivery_detail_id
5597: from wms_wp_wave_lines wwl, wsh_delivery_details wdd2 --11775489 get from wdd... FIXED
5598: where wwl.wave_header_id = p_wave_header_id
5599: and wwl.delivery_detail_id = wdd2.delivery_Detail_id
5600: and wwl.organization_id = wdd2.organization_id
5601: -- and nvl(remove_from_wave_flag, 'N') <> 'Y' --- Removed by Ajith in Phase III as planning it second time is giving an issue
5760:
5761:
5762: print_debug('Checking if Replenishment is needed in Availability check', l_debug);
5763: select sum(wdd.requested_quantity) into l_total_qty
5764: from wms_wp_wave_lines wwl, wsh_delivery_details wdd
5765: where wave_header_id = p_wave_header_id --11775489 get from wdd... FIXED
5766: and wwl.delivery_detail_id = wdd.delivery_detail_id
5767: and wdd.organization_id = wwl.organization_id
5768: and wdd.inventory_item_id = l_item_id and wdd.organization_id=l_org_id;
5822: and (subinventory_code = l_pick_subinventory or
5823: l_pick_subinventory is null);
5824: SELECT Nvl(SUM(wdd.requested_quantity), 0)
5825: INTO l_other_wdd_qty
5826: FROM wsh_delivery_details wdd
5827: WHERE wdd.organization_id = l_org_id
5828: AND wdd.inventory_item_id = l_item_id
5829: and wdd.released_status in ('B', 'E', 'F', 'K', 'R','S') --11822962 only consider wdds where requested material is against a possible reservation
5830: and wdd.delivery_Detail_id not in
5828: AND wdd.inventory_item_id = l_item_id
5829: and wdd.released_status in ('B', 'E', 'F', 'K', 'R','S') --11822962 only consider wdds where requested material is against a possible reservation
5830: and wdd.delivery_Detail_id not in
5831: (select wwl.delivery_detail_id
5832: from wms_wp_wave_lines wwl, wsh_delivery_details wdd2 --11775489 get from wdd... FIXED
5833: where wwl.wave_header_id = p_wave_header_id
5834: and wwl.delivery_detail_id = wdd2.delivery_Detail_id
5835: and wwl.organization_id = wdd2.organization_id
5836: -- and nvl(remove_from_wave_flag, 'N') <> 'Y' --- Removed by Ajith in Phase III as planning it second time is giving an issue
6481: update wms_wp_wave_lines --11775489 get from wdd... FIXED
6482: set remove_from_wave_flag = 'Y',
6483: message = 'Line removed from wave due to insufficient quantity'
6484: where wave_line_id in (select wwl.wave_line_id
6485: from wms_wp_wave_lines wwl, wsh_delivery_details wdd
6486: where wwl.wave_header_id = p_wave_header_id
6487: and nvl(wwl.remove_from_wave_flag, 'N') <> 'Y'
6488: and wwl.delivery_detail_id = wdd.delivery_detail_id
6489: and wwl.organization_id = wdd.organization_id
6518: if v_create_delivery_flag = 'Y' then
6519:
6520: begin
6521:
6522: WSH_DELIVERY_DETAILS_PUB.Autocreate_Deliveries(
6523: -- Standard parameters
6524: p_api_version_number => 1.0,
6525: p_init_msg_list => FND_API.G_FALSE,
6526: p_commit => FND_API.G_FALSE,
7203: (nvl(wts.planned_departure_date,
7204: nvl(wnd.latest_pickup_date, wdd.date_scheduled)))))
7205: into l_target_date
7206: FROM wms_wp_wave_lines wwl, --11775489 get from wdd... FIXED check performance impact
7207: wsh_delivery_details wdd,
7208: wsh_delivery_assignments wda,
7209: wsh_new_deliveries wnd,
7210: wsh_delivery_legs wdl,
7211: wsh_trip_stops wts,
7528: wdd.inventory_item_id,
7529: wwh.pull_replenishment_flag pull_replenishment_flag,
7530: wwl.wave_line_id,
7531: (wdd.requested_quantity - wwl.crossdock_quantity) requested_quantity
7532: from WMS_WP_WAVE_HEADERS_B wwh, wms_wp_wave_lines wwl,wsh_delivery_details wdd --11775489 get from wdd... FIXED check performance impact
7533: where wwh.wave_header_id = p_wave_header_id
7534: and wwh.wave_header_id = wwl.wave_header_id
7535: AND Nvl(wwl.remove_from_Wave_flag, 'N') <> 'Y'
7536: AND wdd.inventory_item_id = l_item_id
7541: ORDER BY inventory_item_id, wave_line_id;
7542:
7543: cursor c_item is
7544: SELECT DISTINCT wdd.inventory_item_id, wwl.organization_id
7545: FROM wms_wp_wave_lines wwl,wsh_delivery_Details wdd --11775489 get from wdd... FIXED
7546: WHERE wave_header_id = p_wave_header_id
7547: and wwl.delivery_detail_id=wdd.delivery_detail_id
7548: and wdd.organization_id = wwl.organization_id
7549: and wdd.released_status in ('R','B') --- For Hot Order Changes
7753: INTO l_reserved_qty
7754: FROM mtl_Reservations
7755: WHERE demand_source_line_id in
7756: (select wdd.source_line_id
7757: from wms_wp_wave_lines wwl, wsh_delivery_details wdd--11775489 get from wdd... FIXED
7758: where wwl.wave_header_id = p_wave_header_id
7759: and wwl.delivery_detail_id = wdd.delivery_detail_id
7760: and wdd.organization_id = wwl.organization_id
7761: and nvl(wwl.remove_from_wave_flag, 'N') <> 'Y')
7765: v_picking_subinventory is null);
7766:
7767: SELECT Nvl(SUM(wdd.requested_quantity), 0)
7768: INTO l_other_wdd_qty
7769: FROM wsh_delivery_details wdd
7770: WHERE wdd.organization_id = l_org_id
7771: AND wdd.inventory_item_id = l_item_id
7772: and wdd.released_status in ('B', 'E', 'F', 'K', 'R','S') --11822962 only consider wdds where requested material is against a possible reservation
7773: and wdd.delivery_Detail_id not in
7802:
7803: print_debug('Bulk Labor Planning Enabled ', l_debug);
7804: select sum(wdd.requested_quantity), sum(wwl.crossdock_quantity)
7805: into l_demand_qty, l_crossdock_qty
7806: from wms_wp_wave_lines wwl,wsh_delivery_details wdd --11775489 get from wdd... FIXED
7807: where wwl.wave_header_id = p_wave_header_id
7808: and nvl(remove_from_wave_flag, 'N') <> 'Y'
7809: and wwl.delivery_detail_id=wdd.delivery_detail_id
7810: and wdd.organization_id = wwl.organization_id
8012: -1,
8013: wdd.inventory_item_id,
8014: -1 bulk collect
8015: into c_labor_time_tbl
8016: FROM wms_wp_wave_lines wwl,wsh_delivery_details wdd --11775489 get from wdd...
8017: WHERE wave_header_id = p_wave_header_id
8018: and wwl.delivery_detail_id=wdd.delivery_detail_id
8019: and wdd.organization_id = wwl.organization_id
8020: and wdd.released_status in ('R','B') --- For Hot Order Changes
8029: -1,
8030: wdd.inventory_item_id,
8031: -1 bulk collect
8032: into c_labor_time_tbl
8033: FROM wms_wp_wave_lines wwl,wsh_delivery_details wdd --11775489 get from wdd...
8034: WHERE wave_header_id = p_wave_header_id
8035: and wwl.delivery_detail_id=wdd.delivery_detail_id
8036: and wdd.organization_id = wwl.organization_id
8037: and wdd.released_status in ('R','B') --- For Hot Order Changes
8906: p_wave_header_id,
8907: remove_from_Wave_flag = 'Y'
8908: WHERE delivery_detail_id IN
8909: (SELECT wwl.delivery_detail_id
8910: FROM wms_wp_wave_lines wwl, wsh_delivery_details wdd
8911: WHERE wwl.wave_header_id = p_wave_header_id
8912: and wwl.delivery_detail_id = wdd.delivery_detail_id --11775489 get from wdd... FIXED
8913: and wwl.organization_id = wdd.organization_id
8914: and nvl(remove_from_wave_flag, 'N') <> 'Y')
9237:
9238: SELECT count(distinct mmtt.transaction_temp_id)
9239: into l_mmtt_count
9240: FROM mtl_material_transactions_temp mmtt,
9241: wsh_Delivery_details wdd,
9242: WSH_NEW_DELIVERIES WND,
9243: WSH_DELIVERY_ASSIGNMENTS WDA
9244: WHERE wdd.source_line_id = mmtt.trx_source_line_id
9245: AND WND.DELIVERY_ID(+) = WDA.DELIVERY_ID
9282:
9283: SELECT count(distinct mmtt.transaction_temp_id)
9284: into l_mmtt_count
9285: FROM mtl_material_transactions_temp mmtt,
9286: wsh_Delivery_details wdd,
9287: WSH_NEW_DELIVERIES WND,
9288: WSH_DELIVERY_ASSIGNMENTS WDA
9289: WHERE wdd.source_line_id = mmtt.trx_source_line_id
9290: AND WND.DELIVERY_ID(+) = WDA.DELIVERY_ID
9376: CURSOR c_carton_grouping_id IS
9377: SELECT DISTINCT carton_grouping_id
9378: FROM wms_waveplan_tasks_temp wwtt,
9379: mtl_txn_request_lines mtrl,
9380: wsh_Delivery_details wdd
9381: WHERE wwtt.move_order_line_id = mtrl.line_id
9382: and wdd.source_line_id = wwtt.transaction_source_line_id
9383: and wwtt.transaction_temp_id in
9384: (select transaction_temp_id
9390: CURSOR c_rc_wdds is
9391: SELECT distinct wdd.delivery_detail_id
9392: FROM mtl_material_transactions_temp mmtt,
9393: mtl_txn_request_lines mtrl,
9394: wsh_Delivery_details wdd
9395: WHERE mmtt.move_order_line_id = mtrl.line_id
9396: AND wdd.source_line_id = mmtt.trx_source_line_id
9397: AND Nvl(wdd.REPLENISHMENT_STATUS, 'C') = 'C'
9398: AND mtrl.carton_grouping_id = p_carton_grouping_id;
9402: CURSOR c_rr_wdds is
9403: select DISTINCT (DELIVERY_DETAIL_ID) from
9404: (
9405: SELECT WDD.DELIVERY_DETAIL_ID DELIVERY_DETAIL_ID
9406: FROM WSH_DELIVERY_DETAILS WDD
9407: WHERE WDD.REPLENISHMENT_STATUS = 'R'
9408: AND (WDD.BATCH_ID IN
9409: ( SELECT WDD.BATCH_ID --bug14534180
9410: FROM WSH_DELIVERY_DETAILS WDD,
9406: FROM WSH_DELIVERY_DETAILS WDD
9407: WHERE WDD.REPLENISHMENT_STATUS = 'R'
9408: AND (WDD.BATCH_ID IN
9409: ( SELECT WDD.BATCH_ID --bug14534180
9410: FROM WSH_DELIVERY_DETAILS WDD,
9411: MTL_TXN_REQUEST_LINES MTRL,
9412: MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
9413: WHERE MMTT.MOVE_ORDER_LINE_ID = MTRL.LINE_ID
9414: AND WDD.SOURCE_LINE_ID = MMTT.TRX_SOURCE_LINE_ID
9414: AND WDD.SOURCE_LINE_ID = MMTT.TRX_SOURCE_LINE_ID
9415: AND MTRL.CARTON_GROUPING_ID = p_carton_grouping_id))
9416: UNION ALL
9417: SELECT WDD.DELIVERY_DETAIL_ID DELIVERY_DETAIL_ID
9418: FROM WSH_DELIVERY_DETAILS WDD
9419: WHERE WDD.REPLENISHMENT_STATUS = 'R'
9420: AND (WDD.BATCH_ID IN
9421: ( SELECT SELECTED_BATCH_ID
9422: FROM WSH_PICKING_BATCHES WPB,
9420: AND (WDD.BATCH_ID IN
9421: ( SELECT SELECTED_BATCH_ID
9422: FROM WSH_PICKING_BATCHES WPB,
9423: MTL_TXN_REQUEST_LINES MTRL,
9424: WSH_DELIVERY_DETAILS WDD2,
9425: MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
9426: WHERE MMTT.MOVE_ORDER_LINE_ID = MTRL.LINE_ID
9427: AND WDD2.SOURCE_LINE_ID = MMTT.TRX_SOURCE_LINE_ID
9428: AND WPB.BATCH_ID = WDD2.BATCH_ID
9461:
9462: CURSOR c_get_trip is
9463: SELECT distinct wts.trip_id trip_id
9464: FROM wms_waveplan_tasks_temp wwtt,
9465: wsh_Delivery_details wdd,
9466: WSH_NEW_DELIVERIES WND,
9467: WSH_DELIVERY_ASSIGNMENTS WDA,
9468: WSH_TRIP_STOPS WTS,
9469: WSH_DELIVERY_LEGS WLG
9481:
9482: CURSOR c_get_trip_Stop is
9483: SELECT distinct wts.stop_id stop_id, wts.STOP_SEQUENCE_NUMBER
9484: FROM mtl_material_transactions_temp mmtt,
9485: wsh_Delivery_details wdd,
9486: WSH_NEW_DELIVERIES WND,
9487: WSH_DELIVERY_ASSIGNMENTS WDA,
9488: WSH_TRIP_STOPS WTS,
9489: WSH_DELIVERY_LEGS WLG
9497:
9498: CURSOR c_get_mmtt is
9499: SELECT distinct mmtt.transaction_temp_id transaction_temp_id
9500: FROM mtl_material_transactions_temp mmtt,
9501: wsh_Delivery_details wdd,
9502: WSH_NEW_DELIVERIES WND,
9503: WSH_DELIVERY_ASSIGNMENTS WDA,
9504: WSH_TRIP_STOPS WTS,
9505: WSH_DELIVERY_LEGS WLG
9517: -- ORDER BY wts.STOP_SEQUENCE_NUMBER desc
9518: union
9519: SELECT distinct mmt.transaction_id transaction_temp_id
9520: FROM mtl_material_transactions mmt,
9521: wsh_Delivery_details wdd,
9522: WSH_NEW_DELIVERIES WND,
9523: WSH_DELIVERY_ASSIGNMENTS WDA,
9524: WSH_TRIP_STOPS WTS,
9525: WSH_DELIVERY_LEGS WLG
9556: p_delivery_id number;
9557:
9558: cursor c_delivery is
9559: select distinct wnd.delivery_id
9560: from wsh_delivery_details wdd,
9561: wsh_new_deliveries wnd,
9562: wsh_delivery_details wdd2,
9563: wsh_delivery_assignments wda
9564: where wdd2.
9558: cursor c_delivery is
9559: select distinct wnd.delivery_id
9560: from wsh_delivery_details wdd,
9561: wsh_new_deliveries wnd,
9562: wsh_delivery_details wdd2,
9563: wsh_delivery_assignments wda
9564: where wdd2.
9565: lpn_id in (select distinct lpn_id
9566: from mtl_onhand_quantities_detail moqd
9571:
9572: -- Get all WDDS that are in Released to Warehouse and Staged status
9573: cursor c_wdd_delivery is
9574: select wdd.delivery_detail_id
9575: from wsh_delivery_details wdd,
9576: wsh_delivery_assignments wda,
9577: wsh_new_deliveries wnd
9578: where wnd.delivery_id = p_delivery_id
9579: and WND.DELIVERY_ID(+) = WDA.DELIVERY_ID
9581: and wdd.released_status in ('Y', 'S');
9582:
9583: cursor c_wdd_Staged is
9584: select wdd.delivery_detail_id
9585: from wsh_delivery_details wdd,
9586: wsh_delivery_assignments wda,
9587: wsh_new_deliveries wnd
9588: where wnd.delivery_id = p_delivery_id
9589: and WND.DELIVERY_ID(+) = WDA.DELIVERY_ID
9596:
9597: CURSOR c_delivery_mmtt IS
9598: SELECT DISTINCT WND.DELIVERY_ID delivery_id
9599: FROM wms_waveplan_tasks_temp wwtt,
9600: wsh_Delivery_details wdd,
9601: WSH_NEW_DELIVERIES WND,
9602: WSH_DELIVERY_ASSIGNMENTS WDA
9603: WHERE wdd.source_line_id = wwtt.transaction_source_line_id
9604: and wwtt.transaction_temp_id in
9616: -- We get all the tasks for the delivery.
9617: CURSOR c_get_mmtt_delivery(p_delivery_mmtt_id in number) is
9618: SELECT distinct wwtt.transaction_temp_id transaction_temp_id
9619: FROM wms_waveplan_tasks_temp wwtt,
9620: wsh_Delivery_details wdd,
9621: WSH_NEW_DELIVERIES WND,
9622: WSH_DELIVERY_ASSIGNMENTS WDA
9623: WHERE WND.DELIVERY_ID = p_delivery_mmtt_id
9624: and wdd.source_line_id = wwtt.transaction_source_line_id
9673:
9674: cursor c_carton_lines is
9675: select wdd.delivery_Detail_id, mmtt.move_order_line_id
9676: -- into p_line_rows(l1), p_move_order_line_tbl(l1)
9677: from wsh_delivery_details wdd, mtl_material_transactions_temp mmtt
9678: where wdd.Source_line_id = mmtt.trx_source_line_id
9679: AND Nvl(wdd.REPLENISHMENT_STATUS, 'C') = 'C'
9680: and mmtt.transaction_temp_id in
9681: (select * from table(wms_wave_planning_pvt.list_mmtt_release)); --11870443
11278: max(ooh.conversion_rate) conversion_rate,
11279: max(ooh.conversion_type_code) conversion_type_code,
11280: max(ooh.conversion_rate_Date) conversion_rate_Date
11281: --max(org_id) org_id 12371804 remove this
11282: FROM wms_wp_Wave_lines wwl, oe_order_headers_all ooh, wsh_delivery_details wdd --11775489 get from wdd... FIXED...check performance problem
11283: WHERE wwl.wave_header_id = p_wave_header_id
11284: and wwl.delivery_detail_id = wdd.delivery_detail_id
11285: and wdd.organization_id = wwl.organization_id
11286: AND wdd.source_header_number = ooh.order_number
12157: p_wave_header_id,
12158: remove_from_Wave_flag = 'Y'
12159: WHERE delivery_detail_id IN
12160: (SELECT wwl.delivery_detail_id
12161: FROM wms_wp_wave_lines wwl, wsh_delivery_details wdd --11775489 get from wdd... FIXED
12162: WHERE wwl.wave_header_id = p_wave_header_id
12163: and wwl.delivery_detail_id = wdd.delivery_detail_id
12164: and wwl.organization_id = wdd.organization_id
12165: and nvl(remove_from_wave_flag, 'N') <> 'Y')
12404:
12405: -- Total Weight
12406: select sum(wdd.net_weight)
12407: into l_count
12408: from wms_wp_wave_lines wwl, wsh_delivery_Details wdd --11775489 get from wdd... FIXED
12409: where wwl.wave_header_id = p_wave_header_id
12410: and wwl.delivery_detail_id = wdd.delivery_detail_id
12411: and wdd.organization_id = wwl.organization_id;
12412:
12458: elsif c_rec.parameter_id in (6003) then
12459: -- Total Volume
12460: select sum(wdd.volume)
12461: into l_count
12462: from wms_wp_wave_lines wwl, wsh_delivery_details wdd--11775489 get from wdd... FIXED
12463: where wwl.wave_header_id = p_wave_header_id
12464: and wwl.delivery_detail_id = wdd.delivery_detail_id
12465: and wdd.organization_id = wwl.organization_id;
12466:
12511: elsif c_rec.parameter_id in (6004) then
12512: -- Total Value
12513: select sum(outstanding_order_value(wdd.source_header_id, wdd.source_line_id))
12514: into l_count
12515: from wms_wp_wave_lines wwl, wsh_delivery_details wdd --11775489 get from wdd... FIXED
12516: where wave_header_id = p_wave_header_id
12517: and wwl.delivery_detail_id = wdd.delivery_detail_id
12518: and wdd.organization_id = wwl.organization_id;
12519:
12564: elsif c_rec.parameter_id in (6005) then
12565: --Delivery Count
12566: select count(distinct wda.delivery_id)
12567: into l_count
12568: from wms_wp_wave_lines wwl, wsh_delivery_assignments wda, wsh_delivery_details wdd --11775489 get from wdd... FIXED
12569: where wwl.wave_header_id = p_wave_header_id
12570: and wwl.delivery_detail_id = wdd.delivery_detail_id
12571: and wwl.organization_id = wdd.organization_id
12572: and wda.delivery_detail_id = wdd.delivery_detail_id;
12617: elsif c_rec.parameter_id in (6006) then
12618: -- Item Count
12619: select count(distinct wdd.inventory_item_id)
12620: into l_count
12621: from wms_wp_wave_lines wwl, wsh_delivery_Details wdd --11775489 get from wdd... FIXED
12622: where wwl.wave_header_id = p_wave_header_id
12623: and wwl.delivery_detail_id = wdd.delivery_detail_id
12624: and wdd.organization_id = wwl.organization_id;
12625:
12694: (select wave_line_id from
12695: (select x.wave_line_id, x.Cum_total_weight
12696: from
12697: (
12698: select wl.wave_line_id , SUM(wdd.net_weight) OVER(ORDER BY wave_line_id) Cum_total_weight from wms_wp_wave_lines wl, wsh_delivery_details wdd
12699: where wl.wave_header_id = :wave_header_id and wdd.delivery_detail_id = wwl.delivery_detail_id and wdd.organization_id = wwl.organization_id
12700: ) x
12701: where Cum_total_weight ' ||
12702: C_REC.OPERATOR_MEANING ||
12715: (select wave_line_id from
12716: (select x.wave_line_id, x.Cum_total_volume
12717: from
12718: (
12719: select wl.wave_line_id , SUM(wdd.volume) OVER(ORDER BY wave_line_id) Cum_total_volume from wms_wp_wave_lines wl, wsh_delivery_details wdd
12720: where wl.wave_header_id = :wave_header_id and wwl.delivery_detail_id = wdd.delivery_detail_id and wdd.organization_id = wwl.organization_id
12721: ) x
12722: where Cum_total_volume ' ||
12723: C_REC.OPERATOR_MEANING ||
12736: (select wave_line_id from
12737: (select x.wave_line_id, x.Cum_value
12738: from
12739: (
12740: select wl.wave_line_id , OUTSTANDING_ORDER_VALUE(wdd.source_header_id, wdd.source_line_id) OVER(ORDER BY wave_line_id) Cum_value from wms_wp_wave_lines wl, wsh_delivery_details
12741: where wave_header_id = :wave_header_id and wwl.delivery_detail_id = wdd.delivery_detail_id and wdd.organization_id = wwl.organization_id
12742: ) x
12743: where Cum_value' ||
12744: C_REC.OPERATOR_MEANING ||
12753:
12754: --Delivery Count
12755: --11775489 get from wdd... check for error?
12756: EXECUTE IMMEDIATE 'delete from wms_wp_wave_lines where wave_line_id in
12757: (select wave_line_id from wms_wp_wave_lines wwl, wsh_delivery_assignments wda, wsh_delivery_details wdd
12758: where wwl.delivery_detail_id = wdd.delivery_detail_id and wwl.organization_id = wdd.organization_id and wdd.delivery_detail_id = wda.delivery_detail_id and wda.delivery_id not in
12759: (
12760: select delivery_id from
12761: (
12759: (
12760: select delivery_id from
12761: (
12762: select min(wwwl.WAVE_LINE_ID) min_line_id, wnd.delivery_id
12763: from WMS_WP_WAVE_LINES wwwl, wsh_delivery_details wdd,
12764: WSH_NEW_DELIVERIES wnd, WSH_DELIVERY_ASSIGNMENTS WDA
12765: where wave_header_id = :p_wave_header_id
12766: and wnd.DELIVERY_ID(+) = WDA.DELIVERY_ID
12767: AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
12783:
12784: --Item Count
12785: --11775489 get from wdd... FIXED
12786: EXECUTE IMMEDIATE 'delete from wms_wp_wave_lines where wave_line_id IN
12787: (SELECT wwl.wave_line_id FROM wms_wp_wave_lines wwl, wsh_delivery_details wdd
12788: WHERE wwl.organization_id = wdd.organization_id AND wwl.delivery_detail_id = wdd.delivery_detail_id AND wdd.inventory_item_id not in
12789: (
12790:
12791: select inventory_item_id from
12790:
12791: select inventory_item_id from
12792: (
12793: select min(wwwl.WAVE_LINE_ID) min_line_id, wdd.inventory_item_id
12794: from WMS_WP_WAVE_LINES wwwl, wsh_delivery_details wdd
12795: where wwwl.wave_header_id = :wave_header_id and wwwl.delivery_detail_id = wdd.delivery_detail_id and wdd.organization_id = wwwl.organization_id
12796: group by wdd.inventory_item_id
12797: order by min_line_id) ' ||
12798: ' where rownum ' || C_REC.OPERATOR_MEANING ||
12891: p_wave_header_id,
12892: remove_from_Wave_flag = 'Y'
12893: WHERE delivery_detail_id IN
12894: (SELECT wwl.delivery_detail_id --11775489 get from wdd... FIXED
12895: FROM wms_wp_wave_lines wwl, wsh_delivery_details wdd
12896: WHERE wave_header_id = p_wave_header_id
12897: and wwl.delivery_detail_id = wdd.delivery_detail_id
12898: and wwl.organization_id = wdd.organization_id
12899: and nvl(remove_from_wave_flag, 'N') <> 'Y')
12929: FOR i IN l_custom_line_action_tbl.first .. l_custom_line_action_tbl.last loop
12930: IF l_custom_line_action_tbl(i) = 'ADD' THEN
12931: SELECT released_status, Nvl(replenishment_status, 'C')
12932: INTO l_released_status, l_replenishment_status
12933: FROM wsh_delivery_details
12934: WHERE delivery_detail_id = l_custom_line_tbl(i)
12935: .delivery_detail_id;
12936:
12937: IF (l_released_status = 'R' OR l_released_status = 'B') and
13094: ' WDD.NET_WEIGHT,' || --????? Is it Net Weight or Gross Weight
13095: ' WDD.VOLUME_UOM_CODE,' ||
13096: ' WDD.VOLUME '; -- Ajith Changed Need to Check
13097: g_Unreleased_SQL := g_Unreleased_SQL ||
13098: ' FROM WSH_DELIVERY_DETAILS WDD,' ||
13099: ' WSH_NEW_DELIVERIES WDE,' ||
13100: ' WSH_DELIVERY_ASSIGNMENTS WDA,' ||
13101: ' WSH_DELIVERY_LEGS WLG,' ||
13102: ' WSH_TRIP_STOPS WTS, ' ||
13106:
13107: IF CUR_REC.PARAMETER_ID = 2002 THEN
13108: if v_quantity_flag = 'N' then
13109: g_Unreleased_SQL := g_Unreleased_SQL ||
13110: ',(SELECT SUM(WDD1.SRC_REQUESTED_QUANTITY) QUANTITY, WDD1.SOURCE_LINE_ID FROM WSH_DELIVERY_DETAILS WDD1
13111: GROUP BY WDD1.SOURCE_LINE_ID) SALES_ORDER_LINE ';
13112:
13113: g_WHERE_SQL := g_WHERE_SQL ||
13114: ' WDD.SOURCE_LINE_ID=SALES_ORDER_LINE.SOURCE_LINE_ID AND ';
13119:
13120: ELSIF CUR_REC.PARAMETER_ID = 3002 THEN
13121: if v_ordercount_flag = 'N' then
13122: g_Unreleased_SQL := g_Unreleased_SQL ||
13123: ',(SELECT COUNT(distinct SOURCE_HEADER_NUMBER) ORDER_COUNT , WDE.DELIVERY_ID DELIVERY_ID FROM WSH_DELIVERY_DETAILS WDD ,WSH_NEW_DELIVERIES WDE, WSH_DELIVERY_ASSIGNMENTS WDA
13124: WHERE WDE.DELIVERY_ID = WDA.DELIVERY_ID AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
13125: GROUP BY WDE.DELIVERY_ID) DELIVERY ';
13126: g_WHERE_SQL := g_WHERE_SQL ||
13127: ' WDE.DELIVERY_ID=DELIVERY.DELIVERY_ID AND ';
13131: /*
13132: ELSIF CUR_REC.PARAMETER_ID = 1005 THEN
13133: --SINGLE LINE ORDER
13134: g_WHERE_SQL := g_WHERE_SQL || g_ADVANCED_SQL ||
13135: ' select 1 FROM wsh_delivery_details wdd1 where wdd1.source_header_number = wdd.source_header_number GROUP BY SOURCE_HEADER_NUMBER HAVING Count(SOURCE_HEADER_NUMBER)=1)';
13136: v_single_line_flag := 'Y';
13137: */
13138: elsif cur_rec.parameter_id IN (5001, 5002, 5003, 5004, 2001, 5005) then
13139: --ITEM
13239: ' AND WTS.STOP_ID = WDO.TRIP_STOP(+) ' || --Added for Dock Assignments
13240: ' AND NVL(WDD.REQUESTED_QUANTITY,0) > 0' ||
13241: ' AND WDD.SOURCE_CODE = ''OE''' ||
13242: ' AND WDD.DELIVERY_DETAIL_ID NOT IN' || --11775489 get from wdd... FIXED
13243: '(SELECT distinct wwl.delivery_detail_id FROM WMS_WP_WAVE_LINES wwl ,wms_wp_wave_headers_vl wwh, wsh_delivery_details wdd
13244: where wwl.wave_header_id=wwh.wave_header_id and wwl.delivery_detail_id = wdd.delivery_detail_id
13245: and wwl.organization_id = wdd.organization_id and (nvl(wwh.wave_firmed_flag,''N'') = ''Y'' and wwh.wave_status <> ''Cancelled'' and nvl(wwl.remove_from_wave_flag,''N'') <>''Y''))';
13246: -- WHERE WAVE_HEADER_ID = :X_WAVE_HEADER_ID)';
13247:
13809: wwl.delivery_detail_id,
13810: wwl.organization_id,
13811: wdd.inventory_item_id,
13812: wdd.requested_quantity
13813: from wms_wp_wave_lines wwl, wsh_delivery_details wdd --11775489 get from wdd... FIXED...check performance
13814: where wwl.wave_header_id = p_wave_header_id
13815: and wdd.inventory_item_id = p_item_id
13816: and wwl.organization_id = p_org_id
13817: and wwl.delivery_detail_id = wdd.delivery_detail_id
13820: ORDER BY wave_header_id, inventory_item_id, wave_line_id;
13821:
13822: cursor c_item is
13823: SELECT DISTINCT wdd.inventory_item_id, wwl.organization_id
13824: FROM wms_wp_wave_lines wwl, wsh_delivery_Details wdd --11775489 get from wdd... FIXED
13825: WHERE wwl.wave_header_id = p_wave_header_id
13826: and wwl.delivery_detail_id = wdd.delivery_detail_id
13827: and wdd.organization_id = wwl.organization_id;
13828:
13960: and (subinventory_code = l_pick_subinventory or
13961: l_pick_subinventory is null);
13962: SELECT Nvl(SUM(wdd.requested_quantity), 0)
13963: INTO l_other_wdd_qty
13964: FROM wsh_delivery_details wdd
13965: WHERE wdd.organization_id = l_org_id
13966: AND wdd.inventory_item_id = l_item_id
13967: and wdd.released_status in ('B', 'E', 'F', 'K', 'R','S') --11822962 only consider wdds where requested material is against a possible reservation
13968: and wdd.delivery_Detail_id not in
13966: AND wdd.inventory_item_id = l_item_id
13967: and wdd.released_status in ('B', 'E', 'F', 'K', 'R','S') --11822962 only consider wdds where requested material is against a possible reservation
13968: and wdd.delivery_Detail_id not in
13969: (select wwl.delivery_detail_id
13970: from wms_wp_wave_lines wwl, wsh_delivery_Details wdd2 --11775489 get from wdd... FIXED
13971: where wwl.wave_header_id = p_wave_header_id
13972: and nvl(wwl.remove_from_wave_flag, 'N') <> 'Y'
13973: and wdd2.source_line_id = wdd.source_line_id
13974: and wwl.delivery_detail_id = wdd2.delivery_detail_id
14086: is
14087:
14088: cursor c_uom is
14089: select nvl(net_weight, 0) net_weight, weight_uom_code
14090: from wsh_delivery_details
14091: where source_header_id = p_source_header_id;
14092:
14093: v_weight_uom varchar2(20);
14094: total_weight number := 0;
14131: is
14132:
14133: cursor c_uom is
14134: select nvl(volume, 0) volume, volume_uom_code
14135: from wsh_delivery_details
14136: where source_header_id = p_source_header_id;
14137:
14138: v_volume_uom varchar2(20);
14139: total_volume number := 0;
14181: select nvl(net_weight, 0) net_weight,
14182: weight_uom_code,
14183: nvl(volume, 0) volume,
14184: volume_uom_code
14185: from wsh_delivery_details
14186: where delivery_detail_id in
14187: (SELECT wwl.delivery_detail_id --11775489 get from wdd... FIXED
14188: FROM wms_wp_wave_lines wwl, wsh_delivery_details wdd
14189: WHERE wwl.wave_header_id = p_wave_header_id
14184: volume_uom_code
14185: from wsh_delivery_details
14186: where delivery_detail_id in
14187: (SELECT wwl.delivery_detail_id --11775489 get from wdd... FIXED
14188: FROM wms_wp_wave_lines wwl, wsh_delivery_details wdd
14189: WHERE wwl.wave_header_id = p_wave_header_id
14190: and wwl.delivery_detail_id = wdd.delivery_detail_id
14191: and wwl.organization_id = wdd.organization_id
14192: and nvl(remove_from_wave_flag, 'N') <> 'Y');
15408: cursor c_lines is
15409: select wwl.wave_line_id
15410: from wms_wp_wave_lines wwl, --11775489 added org_id join...
15411: wms_wp_wave_headers_vl wwh,
15412: wsh_delivery_details wdd
15413: where wwh.wave_header_id = wwl.wave_header_id
15414: and (wwh.wave_status = 'Released')
15415: and nvl(wwl.remove_from_wave_flag, 'N') = 'N'
15416: and wdd.delivery_detail_id = wwl.delivery_detail_id
15442: --also modified to join with wdt
15443: SELECT Sum(Decode(Nvl(wdt.status,0), 4, 1, 0)) loaded,
15444: Count(1) total_tasked
15445: into v_loaded, v_total
15446: FROM wsh_delivery_details wdd, mtl_material_transactions_temp mmtt, wms_dispatched_tasks wdt
15447: WHERE wdd.delivery_detail_id = p_Delivery_Detail_id
15448: AND wdd.move_order_line_id = mmtt.move_order_line_id
15449: AND wdt.transaction_temp_id (+) = mmtt.TRANSACTION_temp_id;
15450:
15508: (nvl(wts.planned_arrival_date,
15509: nvl(wnd.earliest_pickup_date,
15510: wdd.date_scheduled))))) date_scheduled
15511: FROM wms_wp_wave_lines wwl, --11775489 added org_id join...
15512: wsh_delivery_details wdd,
15513: (SELECT wave_header_id wave_id
15514: FROM wms_wp_wave_headers_vl
15515: WHERE ((wave_header_id = p_wave) AND
15516: LOWER(wave_status) in ('released', 'completed'))
15537: CURSOR c_get_trips(p_wave_id NUMBER) IS
15538: SELECT DISTINCT wts.trip_id,
15539: nvl(wdab.end_time, wts.planned_arrival_date) date_scheduled
15540: FROM wms_wp_wave_lines wwl, --11775489 added org_id join...
15541: wsh_delivery_details wdd,
15542: wsh_delivery_assignments wda,
15543: wsh_delivery_legs wdl,
15544: wsh_new_deliveries wnd,
15545: wsh_trip_stops wts,
15563: nvl(wdab.end_time,
15564: (nvl(wts.planned_arrival_date,
15565: wnd.earliest_pickup_date))) date_scheduled
15566: FROM wms_wp_wave_lines wwl, --11775489 added org_id join...
15567: wsh_delivery_details wdd,
15568: wsh_delivery_assignments wda,
15569: wsh_delivery_legs wdl,
15570: wsh_new_deliveries wnd,
15571: wsh_trip_stops wts,
15594: (nvl(wts.planned_arrival_date,
15595: nvl(wnd.earliest_pickup_date,
15596: wdd.date_scheduled))))) date_scheduled
15597: FROM wms_wp_wave_lines wwl, --11775489 added org_id join...
15598: wsh_delivery_details wdd,
15599: wsh_delivery_assignments wda,
15600: wsh_delivery_legs wdl,
15601: wsh_new_deliveries wnd,
15602: wsh_trip_stops wts,
15611: AND wts.stop_id = wdab.trip_stop(+)
15612: AND wdd.organization_id = p_organization_id
15613: and nvl(wwl.remove_from_Wave_flag, 'N') <> 'Y' --11775489 filter out lines no longer in wave
15614: GROUP BY wdd.source_header_id) shi,
15615: wsh_delivery_details wdd,
15616: wsh_delivery_assignments wda,
15617: wsh_delivery_legs wdl,
15618: wsh_new_deliveries wnd,
15619: wsh_trip_stops wts
15637: (nvl(wts.planned_arrival_date,
15638: nvl(wnd.earliest_pickup_date,
15639: wdd.date_scheduled))))) date_scheduled
15640: FROM wms_wp_wave_lines wwl, --11775489 added org_id join...
15641: wsh_delivery_details wdd,
15642: wsh_delivery_assignments wda,
15643: wsh_delivery_legs wdl,
15644: wsh_new_deliveries wnd,
15645: wsh_trip_stops wts,
15655: AND wdd.organization_id = p_organization_id
15656: and nvl(wwl.remove_from_Wave_flag, 'N') <> 'Y' --11775489 filter out lines no longer in wave
15657: GROUP BY wdd.source_line_id) shi,
15658: wms_wp_wave_lines wwl,--11775489 added org_id join...
15659: wsh_delivery_details wdd,
15660: wsh_delivery_assignments wda,
15661: wsh_delivery_legs wdl,
15662: wsh_new_deliveries wnd,
15663: wsh_trip_stops wts
17061:
17062: -------------------------------------------------------------------------------------------------------------
17063: --11775489 get from wdd... FIXED changed to move_order_line_id similar to other bug
17064: l_select_query := 'select mmtt.TRANSACTION_TEMP_ID, mmtt.wms_task_type';
17065: l_from_query := ' from wms_wp_wave_lines wwl,wsh_delivery_details wdd,
17066: mtl_material_transactions_temp mmtt';
17067: l_where_query := ' where wwl.wave_header_id = :wave_id
17068: and wdd.delivery_detail_id = wwl.delivery_detail_id
17069: and wdd.organization_id = wwl.organization_id
17391: if (p_entity = 'Order') then
17392: begin
17393: select distinct (source_header_id)
17394: into l_entity_value
17395: from wsh_delivery_details
17396: where source_header_number = p_entity_value
17397: and organization_id = p_organization_id;
17398:
17399: exception
17410: end if;
17411: --11775489 added org_id join...
17412: IF (p_release_back_ordered_lines = 'Yes') THEN
17413: l_select_query := 'select wwl.delivery_detail_id';
17414: l_from_query := ' from wms_wp_wave_lines wwl,wsh_delivery_details wdd';
17415: l_where_query := ' where wwl.wave_header_id = :wave_id
17416: and wdd.delivery_detail_id = wwl.delivery_detail_id
17417: and wdd.organizatin_id = wwl.organization_id
17418: and wdd.released_status = ''B''
17954: 'K',3,
17955: 'Y',Decode(wlpn.lpn_context,11,Decode(mil.inventory_location_type,2,8,4,9,5,9),9,10)) LINE_PROGRESS_ID,
17956: wwl.wave_header_id
17957: FROM
17958: WSH_DELIVERY_DETAILS WDD,
17959: WMS_WP_WAVE_LINES WWL,
17960: WMS_LICENSE_PLATE_NUMBERS WLPN,
17961: WSH_DELIVERY_ASSIGNMENTS WDA,
17962: mtl_item_locations_kfv mil,
17959: WMS_WP_WAVE_LINES WWL,
17960: WMS_LICENSE_PLATE_NUMBERS WLPN,
17961: WSH_DELIVERY_ASSIGNMENTS WDA,
17962: mtl_item_locations_kfv mil,
17963: wsh_Delivery_details wdd2
17964: WHERE WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
17965: AND wda.parent_delivery_detail_id = wdd2.delivery_detail_id(+)
17966: AND wdd.locator_id = mil.inventory_location_id(+)
17967: and WWL.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
18147: wwl.remove_from_wave_flag,
18148: wwh.wave_firmed_flag,
18149: wwl.message
18150: from
18151: WSH_DELIVERY_DETAILS WDD,
18152: HZ_PARTIES HP,
18153: hz_cust_accounts CUST_ACCT,
18154: OE_ORDER_LINES_ALL OOL,
18155: wms_wp_wave_headers_b WWH,
18162: WSH_DELIVERY_ASSIGNMENTS WDA,
18163: WSH_TRIP_STOPS WTS,
18164: WSH_DELIVERY_LEGS WLG,
18165: mtl_item_locations_kfv mil,
18166: wsh_Delivery_details wdd2
18167: WHERE
18168: HP.party_id = cust_acct.party_id
18169: and cust_acct.status = 'A'
18170: and cust_acct.cust_account_id= wdd.customer_id
18377: WHERE LPN_ID = MMTT.CONTENT_LPN_ID) PICKED_LPN,
18378: mmtt.transaction_uom,
18379: mmtt.transaction_quantity
18380: FROM mtl_material_transactions_temp mmtt,
18381: WSH_DELIVERY_DETAILS WDD,
18382: MTL_ITEM_LOCATIONS_kfv mil, --bug12642410
18383: mtl_system_items_kfv msiv,
18384: wms_op_plans_vl wopv,
18385: per_all_people_f pap,
18509: mmtt.transaction_uom,
18510: mmtt.transaction_quantity
18511: FROM mtl_material_transactions_temp mmtt,
18512: mtl_material_transactions_temp mmtt2, --12736677
18513: WSH_DELIVERY_DETAILS WDD,
18514: MTL_ITEM_LOCATIONS_kfv mil, --bug12642410
18515: mtl_system_items_kfv msiv,
18516: wms_op_plans_vl wopv,
18517: per_all_people_f pap,
18821: WHERE LPN_ID = mmt.CONTENT_LPN_ID) PICKED_LPN,
18822: mmt.transaction_uom,
18823: abs(mmt.transaction_quantity)
18824: FROM mtl_material_transactions mmt,
18825: WSH_DELIVERY_DETAILS WDD,
18826: MTL_ITEM_LOCATIONS_kfv mil, --bug12642410
18827: mtl_system_items_kfv msiv,
18828: wms_op_plans_vl wopv,
18829: per_all_people_f pap,
18995: WHERE LPN_ID = MMTT.CONTENT_LPN_ID) PICKED_LPN,
18996: mmtt.transaction_uom,
18997: mmtt.transaction_quantity
18998: FROM mtl_material_transactions_temp mmtt,
18999: WSH_DELIVERY_DETAILS WDD,
19000: MTL_ITEM_LOCATIONS_kfv mil, --bug12642410
19001: mtl_system_items_kfv msiv,
19002: wms_op_plans_vl wopv,
19003: per_all_people_f pap,
19154: WHERE LPN_ID = wdth.CONTENT_LPN_ID) PICKED_LPN,
19155: wdth.transaction_uom_code,
19156: abs(wdth.transaction_quantity)
19157: FROM
19158: WSH_DELIVERY_DETAILS WDD,
19159: MTL_ITEM_LOCATIONS_kfv mil, --bug12642410
19160: mtl_system_items_kfv msiv,
19161: wms_op_plans_vl wopv,
19162: per_all_people_f pap,
19292: 'E',
19293: 0)
19294: into l_pick_fill_rate
19295: from wms_wp_wave_lines wwl,
19296: wsh_delivery_details wdd,
19297: wms_wp_wwb_tasks_v wwtv,
19298: wms_wp_wave_headers_vl wwh
19299: where wwl.wave_line_id = p_wave_line_id
19300: and wwl.delivery_detail_id = wdd.delivery_detail_id
19326:
19327: --line is in crossdocked status if wdd.released status is 'K' or wdd.move_order_line_id is null
19328: begin
19329: select 'Y' into l_crossdocked from dual where exists
19330: (select 1 from wsh_delivery_details wdd, wms_wp_wave_lines wwl
19331: where wwl.wave_line_id = p_wave_line_id
19332: and wwl.delivery_detail_id = wdd.delivery_detail_id
19333: and wwl.organization_id = wdd.organization_id
19334: and (wdd.released_status = 'K' or (wdd.move_order_line_id is null and wdd.released_status = 'S')));
19349: mmtt.transaction_uom,
19350: wdd.requested_quantity_uom) *
19351: Nvl(MMTT.TRANSACTION_QUANTITY, 0)))
19352: into l_allocated_quantity
19353: from wsh_delivery_details wdd,
19354: mtl_material_transactions_temp mmtt,
19355: wms_wp_wave_lines wwl
19356: where wdd.move_order_line_id = mmtt.move_order_line_id
19357: and wdd.delivery_detail_id = wwl.delivery_detail_id
19359: and wwl.wave_line_id = p_wave_line_id;
19360:
19361: select round(decode(wdd.released_status, 'B', 0, l_allocated_quantity)*100/wdd.requested_quantity)
19362: into l_fill_rate
19363: from wsh_delivery_details wdd,
19364: wms_wp_wave_lines wwl
19365: where wwl.wave_line_id = p_wave_line_id
19366: and wwl.organization_id = wdd.organization_id
19367: and wwl.delivery_detail_id = wdd.delivery_detail_id;
19717:
19718: delete from wms_wp_wave_lines
19719: where wave_line_id in
19720: (select wave_line_id
19721: from wms_wp_wave_lines wwl, wsh_delivery_details wdd
19722: where wwl.wave_header_id = p_wave_header_id
19723: and wdd.delivery_detail_id = wwl.delivery_Detail_id
19724: and wdd.organization_id = wwl.organization_id
19725: and wdd.source_line_number = p_line_number