[Home] [Help]
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
5356: WHERE wwl.organization_id = l_organization_id
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
7213: WHERE wwl.organization_id = l_org_id
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
9246: AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
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
9291: AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
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
9470: WHERE WND.DELIVERY_ID(+) = WDA.DELIVERY_ID
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
9490: WHERE WND.DELIVERY_ID(+) = WDA.DELIVERY_ID
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
9506: WHERE wts.stop_id = l_stop_id
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
9526: WHERE wts.stop_id = l_stop_id
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.
9565: lpn_id in (select distinct lpn_id
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
9581: and wdd.released_status in ('Y', 'S');
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
9591: and wdd.released_status in ('Y');
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
9605: (select transaction_temp_id
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
9625: and wwtt.transaction_temp_id in
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
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, ' ||
13103: ' WMS_DOCK_APPOINTMENTS_B WDO ';
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 ';
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
15524: WHERE wwl.wave_header_id = wwh.wave_id
15525: AND wwl.delivery_detail_id = wdd.delivery_detail_id
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
15547: WHERE wwl.wave_header_id = p_wave_id
15548: AND wwl.delivery_detail_id = wdd.delivery_detail_id
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
15573: WHERE wwl.wave_header_id = p_wave_id
15574: AND wwl.delivery_detail_id = wdd.delivery_detail_id
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
15604: WHERE wwl.wave_header_id = p_wave_id
15605: AND wwl.delivery_detail_id = wdd.delivery_detail_id
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
15621: AND wdd.delivery_detail_id = wda.delivery_detail_id
15622: AND wda.delivery_id = wnd.delivery_id(+)
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
15647: WHERE wwl.wave_header_id = p_wave_id
15648: AND wwl.delivery_detail_id = wdd.delivery_detail_id
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
15665: AND wdd.delivery_detail_id = wwl.delivery_detail_id
15666: and wwl.organization_id = wdd.organization_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
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(+)
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,
18161: WMS_LICENSE_PLATE_NUMBERS WLPN,