[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
2315: (wdd.requested_quantity - nvl(wwr.allocated_quantity, 0)) requested_quantity,
2316: wdd.requested_quantity_uom,
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
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
2410: and wwr.wave_header_id=wwl.wave_header_id
3197: task_id
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
3499: DELETE
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
4398: --l_final_query varchar2(1000);
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
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';
5086: -- wdd.source_header_type_id,
5087: decode(wdd.source_document_type_id, 10, 8, 2) demand_source_type_id,
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
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
5163: and v_model_no is not null;
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
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:
5345: SELECT MAX(nvl(wdab.end_time,
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,
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
5388: and wdd.delivery_Detail_id = wwl.delivery_Detail_id
5389: and wdd.organization_id = wwl.organization_id
5390: and nvl(remove_from_wave_flag,'N')<>'Y';
5391:
5392: update wms_wp_wave_lines
5393: set crossdock_quantity = 0
5394: where wave_header_id = p_wave_header_id;
5395: --select
5396: -- Checking if Pick Subinventory is mentioned in Planning Criteria
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;
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
6325: -- l_table_start := l_credit_check_tbl.FIRST;
6326:
6327: forall c1 in l_credit_check_tbl.FIRST ..l_credit_check_tbl.LAST
6328: -- for c1 in l_table_start ..l_tbl_count loop dbchange8
6329: update wms_wp_wave_lines
6330: set remove_from_wave_flag = 'Y',
6331: message = 'Line removed from wave due to credit check hold'
6332: where wave_line_id = l_credit_check_tbl(c1)
6333: and wave_header_id = p_wave_header_id
6431:
6432: if X_DELIVERY_DETAIL_TBL.count > 0 then
6433: --forall l in l_table_start .. l_tbl_count dbchange9
6434: forall l in X_DELIVERY_DETAIL_TBL.FIRST .. X_DELIVERY_DETAIL_TBL.LAST
6435: update wms_wp_wave_lines
6436: set remove_from_wave_flag = 'Y',
6437: message = 'Line removed from wave due to insufficient quantity'
6438: where delivery_Detail_id = X_DELIVERY_DETAIL_TBL(l)
6439: and wave_header_id = p_wave_header_id;
6461: if X_DELIVERY_DETAIL_TBL.count > 0 then
6462: -- Find all the lines that has the same Model No
6463: -- forall l in l_table_start .. l_tbl_count dbchange10
6464: forall l in X_DELIVERY_DETAIL_TBL.FIRST .. X_DELIVERY_DETAIL_TBL.LAST
6465: update wms_wp_wave_lines
6466: set remove_from_wave_flag = 'Y',
6467: message = 'Line removed from wave due to insufficient quantity'
6468: where delivery_Detail_id = X_DELIVERY_DETAIL_TBL(l)
6469: and wave_header_id = p_wave_header_id;
6477: print_debug('In Reject Order', l_debug);
6478: -- Reject Entire Order from Wave
6479: -- forall i3 in l_table_start .. l_tbl_count dbchange11
6480: forall i3 in l_insufficient_order_number.FIRST .. l_insufficient_order_number.LAST
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
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
6498:
6499:
6500: --forall i2 in l_table_start .. l_tbl_count dbchange12
6501: forall i2 in l_insufficient_DD_TBL.FIRST .. l_insufficient_DD_TBL.LAST
6502: update wms_wp_wave_lines
6503: set remove_from_wave_flag = 'Y',
6504: message = 'Line removed from wave due to insufficient quantity'
6505: where delivery_Detail_id = l_insufficient_DD_TBL(i2)
6506: and wave_header_id = p_wave_header_id
6569:
6570: -- forall i in indices of x_wp_crossdock_tbl
6571: if x_wp_crossdock_tbl.count > 0 then
6572: for i in x_wp_crossdock_tbl.FIRST .. x_wp_crossdock_tbl.LAST loop
6573: update wms_wp_wave_lines
6574: set crossdock_quantity = x_wp_crossdock_tbl(i).crossdock_qty
6575: where delivery_detail_id = x_wp_crossdock_tbl(i).delivery_detail_id
6576: and wave_header_id = p_wave_header_id;
6577: end loop;
6583:
6584: if X_CONSOL_LINES_wave_hdr_id.count > 0 then
6585: -- forall m2 in l_table_start .. l_tbl_count dbchange13
6586: forall m2 in X_CONSOL_LINES_wave_hdr_id.FIRST .. X_CONSOL_LINES_wave_hdr_id.LAST
6587: update wms_wp_wave_lines
6588: set planned_fill_rate = X_CONSOL_LINES_fill_rate(m2)
6589: where wave_line_id = X_CONSOL_LINES_wave_line_id(m2);
6590:
6591: end if;
7202: SELECT MAX(nvl(wdab.end_time,
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,
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')
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
7774: (select delivery_detail_id
7775: from wms_wp_wave_lines
7776: where wave_header_id = p_wave_header_id
7777: and nvl(remove_from_wave_flag, 'N') <> 'Y');
7778:
7779: print_debug('Reserved Qty for other dds that do not belong to the wave :' ||
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
8900: if x_return_status = 'S' then
8901:
8902: savepoint release_lines_concurrent_sp;
8903:
8904: UPDATE wms_wp_wave_lines
8905: SET message = 'This line has been Firmed in Wave ' ||
8906: p_wave_header_id,
8907: remove_from_Wave_flag = 'Y'
8908: WHERE delivery_detail_id IN
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')
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
12151:
12152: -- Need to firm the wave as releasing it will firm the wave
12153: savepoint release_wave_lines_sp;
12154:
12155: UPDATE wms_wp_wave_lines
12156: SET message = 'This line has been Firmed in Wave ' ||
12157: p_wave_header_id,
12158: remove_from_Wave_flag = 'Y'
12159: WHERE delivery_detail_id IN
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')
12352: l_done_flag := FND_API.G_TRUE; --12371394
12353: x_return_status := 'E';
12354: else
12355: FORALL i IN 1 .. release_table.Count
12356: insert into wms_wp_wave_lines
12357: (WAVE_HEADER_ID,
12358: WAVE_LINE_ID,
12359: WAVE_LINE_SOURCE,
12360: WAVE_LINE_STATUS,
12368: DEMAND_SOURCE_HEADER_ID,
12369: crossdock_uom)--11775489 this column added
12370: values
12371: (p_wave_header_id,
12372: WMS_WP_WAVE_LINES_S.NEXTVAL,
12373: 'OE',
12374: 'Created',
12375: fnd_global.user_id,
12376: sysdate,
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:
12427:
12428: print_debug('Criteria is satisfied. So not deleting any lines ',
12429: l_debug);
12430: else
12431: delete from wms_wp_wave_lines
12432: where wave_header_id = p_wave_header_id;
12433:
12434: end if;
12435: elsif c_rec.operator_meaning = '>=' then
12437: if l_count >= C_REC.OPERAND_VALUE then
12438: print_debug('Criteria is satisfied. So not deleting any lines ',
12439: l_debug);
12440: else
12441: delete from wms_wp_wave_lines
12442: where wave_header_id = p_wave_header_id;
12443:
12444: end if;
12445: elsif c_rec.operator_meaning = '>' then
12447: if l_count > C_REC.OPERAND_VALUE then
12448: print_debug('Criteria is satisfied. So not deleting any lines ',
12449: l_debug);
12450: else
12451: delete from wms_wp_wave_lines
12452: where wave_header_id = p_wave_header_id;
12453:
12454: end if;
12455:
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:
12480: if l_count = C_REC.OPERAND_VALUE then
12481: print_debug('Criteria is satisfied. So not deleting any lines ',
12482: l_debug);
12483: else
12484: delete from wms_wp_wave_lines
12485: where wave_header_id = p_wave_header_id;
12486:
12487: end if;
12488: elsif c_rec.operator_meaning = '>=' then
12490: if l_count >= C_REC.OPERAND_VALUE then
12491: print_debug('Criteria is satisfied. So not deleting any lines ',
12492: l_debug);
12493: else
12494: delete from wms_wp_wave_lines
12495: where wave_header_id = p_wave_header_id;
12496:
12497: end if;
12498: elsif c_rec.operator_meaning = '>' then
12500: if l_count > C_REC.OPERAND_VALUE then
12501: print_debug('Criteria is satisfied. So not deleting any lines ',
12502: l_debug);
12503: else
12504: delete from wms_wp_wave_lines
12505: where wave_header_id = p_wave_header_id;
12506:
12507: end if;
12508:
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:
12533: if l_count = C_REC.OPERAND_VALUE then
12534: print_debug('Criteria is satisfied. So not deleting any lines ',
12535: l_debug);
12536: else
12537: delete from wms_wp_wave_lines
12538: where wave_header_id = p_wave_header_id;
12539:
12540: end if;
12541: elsif c_rec.operator_meaning = '>=' then
12543: if l_count >= C_REC.OPERAND_VALUE then
12544: print_debug('Criteria is satisfied. So not deleting any lines ',
12545: l_debug);
12546: else
12547: delete from wms_wp_wave_lines
12548: where wave_header_id = p_wave_header_id;
12549:
12550: end if;
12551: elsif c_rec.operator_meaning = '>' then
12553: if l_count > C_REC.OPERAND_VALUE then
12554: print_debug('Criteria is satisfied. So not deleting any lines ',
12555: l_debug);
12556: else
12557: delete from wms_wp_wave_lines
12558: where wave_header_id = p_wave_header_id;
12559:
12560: end if;
12561:
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;
12587: if l_count = C_REC.OPERAND_VALUE then
12588: print_debug('Criteria is satisfied. So not deleting any lines ',
12589: l_debug);
12590: else
12591: delete from wms_wp_wave_lines
12592: where wave_header_id = p_wave_header_id;
12593:
12594: end if;
12595: elsif c_rec.operator_meaning = '>=' then
12597: if l_count >= C_REC.OPERAND_VALUE then
12598: print_debug('Criteria is satisfied. So not deleting any lines ',
12599: l_debug);
12600: else
12601: delete from wms_wp_wave_lines
12602: where wave_header_id = p_wave_header_id;
12603:
12604: end if;
12605: elsif c_rec.operator_meaning = '>' then
12607: if l_count > C_REC.OPERAND_VALUE then
12608: print_debug('Criteria is satisfied. So not deleting any lines ',
12609: l_debug);
12610: else
12611: delete from wms_wp_wave_lines
12612: where wave_header_id = p_wave_header_id;
12613:
12614: end if;
12615:
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:
12639: if l_count = C_REC.OPERAND_VALUE then
12640: print_debug('Criteria is satisfied. So not deleting any lines ',
12641: l_debug);
12642: else
12643: delete from wms_wp_wave_lines
12644: where wave_header_id = p_wave_header_id;
12645:
12646: end if;
12647: elsif c_rec.operator_meaning = '>=' then
12649: if l_count >= C_REC.OPERAND_VALUE then
12650: print_debug('Criteria is satisfied. So not deleting any lines ',
12651: l_debug);
12652: else
12653: delete from wms_wp_wave_lines
12654: where wave_header_id = p_wave_header_id;
12655:
12656: end if;
12657: elsif c_rec.operator_meaning = '>' then
12659: if l_count > C_REC.OPERAND_VALUE then
12660: print_debug('Criteria is satisfied. So not deleting any lines ',
12661: l_debug);
12662: else
12663: delete from wms_wp_wave_lines
12664: where wave_header_id = p_wave_header_id;
12665:
12666: end if;
12667:
12689: if c_rec.parameter_id = 6002 then
12690:
12691: -- Total Weight
12692: --11775489 get from wdd... FIXED
12693: EXECUTE IMMEDIATE 'delete from wms_wp_wave_lines where wave_line_id in
12694: (select wave_line_id from
12695: (select x.wave_line_id, x.Cum_total_weight
12696: from
12697: (
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 ||
12710: elsif c_rec.parameter_id = 6003 then
12711:
12712: -- Total Volume
12713: --11775489 get from wdd... FIXED
12714: EXECUTE IMMEDIATE 'delete from wms_wp_wave_lines where wave_line_id in
12715: (select wave_line_id from
12716: (select x.wave_line_id, x.Cum_total_volume
12717: from
12718: (
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 ||
12731: elsif c_rec.parameter_id = 6004 then
12732:
12733: -- Total Value
12734:
12735: EXECUTE IMMEDIATE 'delete from wms_wp_wave_lines where wave_line_id in
12736: (select wave_line_id from
12737: (select x.wave_line_id, x.Cum_value
12738: from
12739: (
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 ||
12752: elsif c_rec.parameter_id = 6005 then
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
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
12782: elsif c_rec.parameter_id = 6006 then
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:
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 ||
12815:
12816: -- Line Count
12817: select count(1)
12818: into l_count
12819: from wms_wp_wave_lines
12820: where wave_header_id = p_wave_header_id;
12821:
12822: print_debug('Entered Wave Parameter with >, >= ,= operators --- > Line count ',
12823: l_debug);
12834: if l_count = C_REC1.OPERAND_VALUE then
12835: print_debug('Criteria is satisfied. So not deleting any lines ',
12836: l_debug);
12837: else
12838: delete from wms_wp_wave_lines
12839: where wave_header_id = p_wave_header_id;
12840:
12841: end if;
12842: elsif c_rec1.operator_meaning = '>=' then
12844: if l_count >= C_REC1.OPERAND_VALUE then
12845: print_debug('Criteria is satisfied. So not deleting any lines ',
12846: l_debug);
12847: else
12848: delete from wms_wp_wave_lines
12849: where wave_header_id = p_wave_header_id;
12850:
12851: end if;
12852: elsif c_rec1.operator_meaning = '>' then
12854: if l_count > C_REC1.OPERAND_VALUE then
12855: print_debug('Criteria is satisfied. So not deleting any lines ',
12856: l_debug);
12857: else
12858: delete from wms_wp_wave_lines
12859: where wave_header_id = p_wave_header_id;
12860:
12861: end if;
12862:
12864: else
12865:
12866: -- Line Count
12867: /*
12868: v_wave_sql := 'delete from wms_wp_wave_lines where rownum ' ||
12869: C_REC1.OPERATOR_MEANING || C_REC1.OPERAND_VALUE ||
12870: ' and wave_header_id = ' || p_wave_header_id;
12871: */
12872: EXECUTE IMMEDIATE 'delete from wms_wp_wave_lines where wave_line_id not in
12868: v_wave_sql := 'delete from wms_wp_wave_lines where rownum ' ||
12869: C_REC1.OPERATOR_MEANING || C_REC1.OPERAND_VALUE ||
12870: ' and wave_header_id = ' || p_wave_header_id;
12871: */
12872: EXECUTE IMMEDIATE 'delete from wms_wp_wave_lines where wave_line_id not in
12873: (
12874: select wave_line_id from wms_wp_wave_lines where wave_header_id = :wave_header_id and rownum' ||
12875: C_REC1.OPERATOR_MEANING ||
12876: C_REC1.OPERAND_VALUE || ' )' ||
12870: ' and wave_header_id = ' || p_wave_header_id;
12871: */
12872: EXECUTE IMMEDIATE 'delete from wms_wp_wave_lines where wave_line_id not in
12873: (
12874: select wave_line_id from wms_wp_wave_lines where wave_header_id = :wave_header_id and rownum' ||
12875: C_REC1.OPERATOR_MEANING ||
12876: C_REC1.OPERAND_VALUE || ' )' ||
12877: ' and wave_header_id = :wave_header_id'
12878: using p_wave_header_id, p_wave_header_id;
12885:
12886: print_debug('Firmed Flag is ' || l_firmed_flag, l_debug);
12887:
12888: if l_firmed_flag = 'Y' then
12889: UPDATE wms_wp_wave_lines
12890: SET message = 'This line has been Firmed in Wave ' ||
12891: p_wave_header_id,
12892: remove_from_Wave_flag = 'Y'
12893: WHERE delivery_detail_id IN
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')
12915: l_tbl_count := l_custom_line_tbl.count;
12916:
12917: -- FORall i IN 0 .. l_tbl_count dbchange15
12918: FOR i IN l_custom_line_tbl.FIRST .. l_custom_line_tbl.LAST LOOP
12919: DELETE FROM wms_wp_wave_lines
12920: WHERE DELIVERY_DETAIL_ID = l_custom_line_tbl(i).DELIVERY_DETAIL_ID
12921: AND l_custom_line_action_tbl(i) = 'REMOVE'
12922: AND wave_header_id = p_wave_header_id;
12923: end loop;
12922: AND wave_header_id = p_wave_header_id;
12923: end loop;
12924:
12925: print_debug('Deleted ' || SQL%ROWCOUNT ||
12926: ' lines from wms_wp_wave_lines using custom API returned lines',
12927: l_debug);
12928:
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
12944: FROM dual
12945: WHERE l_custom_line_tbl(i)
12946: .delivery_detail_id IN
12947: (SELECT distinct wwl.delivery_detail_id
12948: FROM WMS_WP_WAVE_LINES wwl,
12949: wms_wp_wave_headers_vl wwh
12950: WHERE wwl.wave_header_id = p_wave_header_id -- Bug 13448469 (Replaced 367 with p_wave_header_id)
12951: OR (wwl.wave_header_id = wwh.wave_header_id and
12952: nvl(wwh.wave_firmed_flag, 'N') = 'Y' and
12984: END IF;
12985: END LOOP;
12986:
12987: FORALL i IN l_custom_line_tbl.first .. l_custom_line_tbl.last
12988: INSERT INTO wms_wp_wave_lines VALUES l_custom_line_tbl (i);
12989:
12990: print_debug('Inserted ' || SQL%ROWCOUNT ||
12991: ' detail lines returned by custom API into wms_wp_wave_lines ',
12992: l_debug);
12987: FORALL i IN l_custom_line_tbl.first .. l_custom_line_tbl.last
12988: INSERT INTO wms_wp_wave_lines VALUES l_custom_line_tbl (i);
12989:
12990: print_debug('Inserted ' || SQL%ROWCOUNT ||
12991: ' detail lines returned by custom API into wms_wp_wave_lines ',
12992: l_debug);
12993:
12994: END IF; -- custom API implemented
12995:
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:
13252:
13253: if nvl(g_Add_lines, 'N') = 'Y' then
13254: g_Unreleased_SQL := g_Unreleased_SQL ||
13255: ' AND WDD.DELIVERY_DETAIL_ID NOT IN' ||
13256: '(select distinct delivery_detail_id from wms_wp_Wave_lines where wave_header_id=:X_wave_header_id)';
13257:
13258: end if;
13259:
13260: g_cond_SQL := '';
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:
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
14031: print_debug('Planned Fill Rate:' || X_CONSOL_LINES_TBL(m)
14032: .planned_fill_rate,
14033: l_debug);
14034:
14035: update wms_wp_wave_lines
14036: set planned_fill_rate = X_CONSOL_LINES_TBL(m).planned_fill_rate
14037: where wave_line_id = X_CONSOL_LINES_TBL(m).wave_line_id;
14038:
14039: m := m + 1;
14058:
14059: is
14060: cursor c_lines is
14061: select wwl.wave_line_id
14062: from wms_wp_wave_lines wwl
14063: where wave_header_id = p_wave_header_id
14064: and nvl(remove_from_wave_flag, 'N') <> 'Y'; --11775489 added filter
14065: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
14066:
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');
15285: update wms_wp_wave_headers_vl
15286: set wave_status = 'Completed', wave_Completion_time = sysdate -- should we change the column name to Wave Completion Date
15287: where wave_header_id = l_waves.wave_header_id;
15288:
15289: update wms_wp_wave_lines
15290: set pick_fill_rate = 100
15291: where wave_header_id = l_waves.wave_header_id;
15292:
15293: -- Need to check whether the wave can be closed or not
15406:
15407: -- Transaction Qty should be converted to primary uom
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')
15507: MIN(nvl(wdab.end_time,
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
15536: -- cursor def for getting trips and corresponding date scheduled of a particular wave.
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,
15562: wda.delivery_id,
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,
15593: MIN(nvl(wdab.end_time,
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,
15636: MIN(nvl(wdab.end_time,
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,
15654: AND wts.stop_id = wdab.trip_stop(+)
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,
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
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''
17482: print_debug('Setting the Re Release flag to Y ', l_debug);
17483:
17484: forall i IN l_wdd_table.FIRST .. l_wdd_table.LAST
17485:
17486: UPDATE wms_wp_wave_lines
17487: SET re_release_flag = 'Y'
17488: WHERE delivery_detail_id = l_wdd_table(i);
17489: COMMIT;
17490:
17837: end if; -- 14781148
17838:
17839: IF l_mo_line_table.COUNT > 0 THEN
17840: FORALL i IN l_mo_line_table.FIRST .. l_mo_line_table.LAST
17841: DELETE FROM wms_wp_wave_lines wwwl
17842: WHERE wwwl.delivery_detail_id = l_mo_line_table(i)
17843: AND exists (select 1 from wms_wp_wave_headers_b wwwh where wwwh.wave_firmed_flag = 'N' and wwwl.wave_header_id = wwwh.wave_header_id)
17844: AND nvl(wwwl.remove_from_wave_flag , 'N')='N';
17845:
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,
17963: wsh_Delivery_details wdd2
18152: HZ_PARTIES HP,
18153: hz_cust_accounts CUST_ACCT,
18154: OE_ORDER_LINES_ALL OOL,
18155: wms_wp_wave_headers_b WWH,
18156: WMS_WP_WAVE_LINES WWL,
18157: WSH_NEW_DELIVERIES WND,
18158: MTL_SYSTEM_ITEMS_KFV MSIV,
18159: WSH_CARRIERS WC,
18160: MTL_PARAMETERS MP,
18383: mtl_system_items_kfv msiv,
18384: wms_op_plans_vl wopv,
18385: per_all_people_f pap,
18386: wms_op_operation_instances wooi,
18387: WMS_WP_WAVE_LINES WWL,
18388: wms_dispatched_tasks wdt,
18389: bom_standard_operations bso,
18390: bom_resources br1
18391: where mmtt.transaction_temp_id = wdt.transaction_temp_id(+)
18515: mtl_system_items_kfv msiv,
18516: wms_op_plans_vl wopv,
18517: per_all_people_f pap,
18518: wms_op_operation_instances wooi,
18519: WMS_WP_WAVE_LINES WWL,
18520: wms_dispatched_tasks wdt,
18521: bom_standard_operations bso,
18522: bom_resources br1
18523: where mmtt.transaction_temp_id = wdt.transaction_temp_id(+)
18663: mtl_system_items_kfv msiv,
18664: wms_op_plans_vl wopv,
18665: per_all_people_f pap,
18666: wms_op_operation_instances wooi,
18667: WMS_WP_WAVE_LINES WWL,
18668: wms_dispatched_tasks wdt,
18669: bom_standard_operations bso,
18670: bom_resources br1,
18671: mtl_txn_request_headers mtrh,
18827: mtl_system_items_kfv msiv,
18828: wms_op_plans_vl wopv,
18829: per_all_people_f pap,
18830: wms_op_operation_instances wooi,
18831: WMS_WP_WAVE_LINES WWL,
18832: wms_dispatched_tasks_history wdth,
18833: bom_standard_operations bso,
18834: bom_resources br1
18835: where mmt.transaction_set_id = wdth.transaction_id
19001: mtl_system_items_kfv msiv,
19002: wms_op_plans_vl wopv,
19003: per_all_people_f pap,
19004: wms_op_operation_instances wooi,
19005: WMS_WP_WAVE_LINES WWL,
19006: wms_dispatched_tasks wdt,
19007: bom_standard_operations bso,
19008: bom_resources br1,
19009: mtl_txn_request_lines mtrl
19160: mtl_system_items_kfv msiv,
19161: wms_op_plans_vl wopv,
19162: per_all_people_f pap,
19163: wms_op_operation_instances wooi,
19164: WMS_WP_WAVE_LINES WWL,
19165: wms_dispatched_tasks_history wdth,
19166: bom_standard_operations bso,
19167: bom_resources br1,
19168: mtl_txn_request_lines mtrl,
19291: 0,
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
19305: and nvl(wwl.remove_from_wave_flag, 'N') = 'N'
19306: and wdd.released_status in ('B', 'E', 'Y');
19307:
19308:
19309: update wms_wp_wave_lines
19310: set pick_fill_rate = l_pick_fill_rate
19311: where wave_line_id = p_wave_line_id;
19312:
19313: exception
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')));
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
19358: and wdd.organization_id = wwl.organization_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;
19368: end if;
19366: and wwl.organization_id = wdd.organization_id
19367: and wwl.delivery_detail_id = wdd.delivery_detail_id;
19368: end if;
19369:
19370: update wms_wp_wave_lines
19371: set release_fill_rate = l_fill_rate
19372: where wave_line_id = p_wave_line_id;
19373:
19374:
19664: update wms_wp_wave_headers_vl
19665: set release_immediately = 'Y'
19666: WHERE WAVE_HEADER_ID = P_WAVE_HEADER_ID;
19667:
19668: /*UPDATE wms_wp_wave_lines
19669: SET message = 'This line has been Firmed in Wave '||p_wave_header_id,
19670: remove_from_Wave_flag = 'Y'
19671: WHERE delivery_detail_id IN
19672: (SELECT delivery_detail_id FROM wms_wp_wave_lines WHERE wave_header_id = p_wave_header_id
19668: /*UPDATE wms_wp_wave_lines
19669: SET message = 'This line has been Firmed in Wave '||p_wave_header_id,
19670: remove_from_Wave_flag = 'Y'
19671: WHERE delivery_detail_id IN
19672: (SELECT delivery_detail_id FROM wms_wp_wave_lines WHERE wave_header_id = p_wave_header_id
19673: and nvl(remove_from_wave_flag,'N')<>'Y')
19674: and wave_header_id != p_wave_header_id;
19675:
19676: update wms_wp_wave_headers_vl
19714: x_return_status OUT NOCOPY VARCHAR2) IS
19715:
19716: BEGIN
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
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