[Home] [Help]
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,
5355: wms_dock_appointments_b wdab
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,
7212: wms_dock_appointments_b wdab
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
9246: AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
9247: and mmtt.transfer_to_location = p_consol_locator_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
9291: AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
9292: and mmtt.wms_task_status <> 8
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
9470: WHERE WND.DELIVERY_ID(+) = WDA.DELIVERY_ID
9471: AND wdd.source_line_id = wwtt.transaction_source_line_id
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
9490: WHERE WND.DELIVERY_ID(+) = WDA.DELIVERY_ID
9491: and wts.trip_id = l_trip_id
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
9506: WHERE wts.stop_id = l_stop_id
9507: AND wdd.source_line_id = mmtt.trx_source_line_id
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
9526: WHERE wts.stop_id = l_stop_id
9527: AND wdd.source_line_id = mmt.trx_source_line_id
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
9567: where locator_id = p_consol_locator_id)
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
9580: AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
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
9590: AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
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
9605: (select transaction_temp_id
9606: from wms_wp_tp_mmtt
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
9625: and wwtt.transaction_temp_id in
9626: (select transaction_temp_id
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;
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: (
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
12768: AND wwwl.delivery_Detail_id = WDD.DELIVERY_DETAIL_ID
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, ' ||
13103: ' WMS_DOCK_APPOINTMENTS_B WDO ';
13104:
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 ';
15515: WHERE ((wave_header_id = p_wave) AND
15516: LOWER(wave_status) in ('released', 'completed'))
15517: OR (p_wave IS NULL AND
15518: LOWER(wave_status) in ('released', 'completed'))) wwh,
15519: wsh_delivery_assignments wda,
15520: wsh_delivery_legs wdl,
15521: wsh_new_deliveries wnd,
15522: wsh_trip_stops wts,
15523: wms_dock_appointments_b wdab
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,
15546: wms_dock_appointments_b wdab
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,
15572: wms_dock_appointments_b wdab
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,
15603: wms_dock_appointments_b wdab
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
15620: WHERE wdd.source_header_id = shi.source_header_id
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,
15646: wms_dock_appointments_b wdab
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
15664: WHERE wdd.source_line_id = shi.source_line_id
17074: IF (p_entity = 'Wave') THEN
17075: print_debug('Nothing to Do here' || l_final_query, l_debug);
17076: ELSIF (p_entity = 'Trip') THEN
17077: l_from_query := l_from_query ||
17078: ' ,wsh_delivery_assignments wda, wsh_new_deliveries wnd,
17079: wsh_delivery_legs wdl, wsh_trip_stops wts';
17080: l_where_query := l_where_query ||
17081: ' and wdd.delivery_detail_id = wda.delivery_detail_id
17082: and wda.delivery_id = wnd.delivery_id
17083: and wnd.delivery_id = wdl.delivery_id
17084: and wdl.pick_up_stop_id = wts.stop_id
17085: and wts.trip_id = :entity_value ';
17086: ELSIF (p_entity = 'Delivery') THEN
17087: l_from_query := l_from_query || ' , wsh_delivery_assignments wda';
17088: l_where_query := l_where_query ||
17089: ' and wdd.delivery_detail_id = wda.delivery_detail_id
17090: and wda.delivery_id = :entity_value ';
17091: ELSIF (p_entity = 'Order') THEN
17423: IF (p_entity = 'Wave') THEN
17424: print_debug('No more changes in any string', l_debug);
17425: ELSIF (p_entity = 'Trip') THEN
17426: l_from_query := l_from_query ||
17427: ' , wsh_delivery_assignments wda, wsh_new_deliveries wnd,
17428: wsh_delivery_legs wdl,wsh_trip_stops wts';
17429: l_where_query := l_where_query ||
17430: ' and wda.delivery_detail_id = wdd.delivery_detail_id
17431: and wda.delivery_id = wnd.delivery_id(+)
17432: and wnd.delivery_id = wdl.delivery_id(+)
17433: and wdl.pick_up_stop_id = wts.stop_id(+)
17434: and wts.trip_id = :entity_value';
17435: ELSIF (p_entity = 'Delivery') THEN
17436: l_from_query := l_from_query || ' ,wsh_delivery_assignments wda';
17437: l_where_query := l_where_query ||
17438: ' and wda.delivery_detail_id = wdd.delivery_detail_id
17439: and wda.delivery_id = :entity_value';
17440: ELSIF (p_entity = 'Order') THEN
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
17964: WHERE WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
17965: AND wda.parent_delivery_detail_id = wdd2.delivery_detail_id(+)
18158: MTL_SYSTEM_ITEMS_KFV MSIV,
18159: WSH_CARRIERS WC,
18160: MTL_PARAMETERS MP,
18161: WMS_LICENSE_PLATE_NUMBERS WLPN,
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