[Home] [Help]
87: -- Organization_id Process all shipments which are associated to the
88: -- OTM enabled organization id.
89: --
90: -- p_del_name_low Process all shipments which are associated to the
91: -- deliveries from wsh_new_del_interface which
92: -- having delivery name greater than or equal to
93: -- p_del_name_low
94: --
95: -- p_del_name_high Process all shipments which are associated to the
92: -- having delivery name greater than or equal to
93: -- p_del_name_low
94: --
95: -- p_del_name_high Process all shipments which are associated to the
96: -- deliveries from wsh_new_del_interface which
97: -- having delivery name less than or equal to
98: -- p_del_name_high
99: --
100: -- p_del_pickup_date_low Process all shipments which are associated to the
345: wsh_trips wt
346: WHERE wti.interface_action_code IN (G_TMS_RELEASE_CODE,G_TMS_DELETE_CODE)
347: AND wt.tp_plan_name(+) = wti.tp_plan_name
348: AND wti.group_id in
349: (SELECT wti.group_id FROM wsh_new_del_interface wndi,
350: wsh_del_legs_interface wdli,
351: wsh_trip_stops_interface wtsi,
352: wsh_trips_interface wti,
353: wsh_new_deliveries wnd
607: WHERE wtsi.trip_interface_id = wti.trip_interface_id
608: AND wtsi.interface_action_code = G_TMS_RELEASE_CODE
609: AND wti.interface_action_code = G_TMS_RELEASE_CODE
610: AND wti.group_id in
611: (SELECT wti.group_id FROM wsh_new_del_interface wndi,
612: wsh_del_legs_interface wdli,
613: wsh_trip_stops_interface wtsi,
614: wsh_trips_interface wti,
615: wsh_new_deliveries wnd
709: wnd.ultimate_dropoff_location_id,-- used for internal Locations
710: 'N' processed_flag, -- used to indicate which deliveries have to be assigned
711: wnd.tms_interface_flag -- used to check whether tms version check can be avoided.
712: FROM wsh_del_legs_interface wdli,
713: wsh_new_del_interface wndi,
714: wsh_trip_stops_interface wtsi_pu,
715: wsh_trip_stops_interface wtsi_do,
716: wsh_trips_interface wti,
717: wsh_new_deliveries wnd
756: wnd.ultimate_dropoff_location_id,-- used for internal Locations
757: 'N' processed_flag, -- used to indicate which deliveries have to be assigned
758: wnd.tms_interface_flag -- used to check whether tms version check can be avoided.
759: FROM wsh_del_legs_interface wdli,
760: wsh_new_del_interface wndi,
761: wsh_trip_stops_interface wtsi_pu,
762: wsh_trip_stops_interface wtsi_do,
763: wsh_trips_interface wti,
764: wsh_new_deliveries wnd
804: wnd.ultimate_dropoff_location_id,-- used for internal Locations
805: 'N' processed_flag, -- used to indicate which deliveries have to be assigned
806: wnd.tms_interface_flag -- used to check whether tms version check can be avoided.
807: FROM wsh_del_legs_interface wdli,
808: wsh_new_del_interface wndi,
809: wsh_trip_stops_interface wtsi_pu,
810: wsh_trip_stops_interface wtsi_do,
811: wsh_trips_interface wti,
812: wsh_new_deliveries wnd
821: AND wnd.delivery_id = wdli.delivery_id
822: AND wndi.delivery_id = wdli.delivery_id
823: AND wdli.interface_action_code = G_TMS_RELEASE_CODE
824: AND wti.group_id in
825: (SELECT wti.group_id FROM wsh_new_del_interface wndi,
826: wsh_del_legs_interface wdli,
827: wsh_trip_stops_interface wtsi,
828: wsh_trips_interface wti,
829: wsh_new_deliveries wnd
861: wnd.ultimate_dropoff_location_id,-- used for internal Locations
862: 'N' processed_flag, -- used to indicate which deliveries have to be assigned
863: wnd.tms_interface_flag -- used to check whether tms version check can be avoided.
864: FROM wsh_del_legs_interface wdli,
865: wsh_new_del_interface wndi,
866: wsh_trip_stops_interface wtsi_pu,
867: wsh_trip_stops_interface wtsi_do,
868: wsh_trips_interface wti,
869: wsh_new_deliveries wnd
1002: from wsh_trips_interface wti ,
1003: wsh_trip_stops_interface wtsi ,
1004: wsh_del_legs_interface wdli ,
1005: wsh_freight_costs_interface wfci,
1006: wsh_new_del_interface wndi
1007: where wti.trip_interface_id = c_trip_interface_id --Bug7717569
1008: and wdli.delivery_interface_id = wndi.delivery_interface_id
1009: and wndi.delivery_interface_id = wfci.delivery_interface_id (+)
1010: and wndi.interface_action_code = 'TMS_RELEASE'
1376: -- Errors are logged always for the deliveries
1377: --FORALL l_del_wti_index in INDICES OF l_del_wti_tab
1378: FORALL i in l_del_wti_tab.FIRST..l_del_wti_tab.LAST
1379: DELETE from wsh_interface_errors wie
1380: WHERE wie.interface_table_name = 'WSH_NEW_DEL_INTERFACE'
1381: AND wie.interface_id in (
1382: select wdli.delivery_interface_id
1383: from wsh_trip_stops_interface wtsi,
1384: wsh_del_legs_interface wdli
2571: DECLARE
2572: CURSOR c_dock_appt_rec(c_stop_interface_id IN NUMBER, c_group_id IN NUMBER) IS
2573: SELECT wtsi.dock_name, wnd.organization_id, wtsi.start_time, wtsi.end_time
2574: FROM wsh_del_legs_interface wdli,
2575: wsh_new_del_interface wndi,
2576: wsh_new_deliveries wnd,
2577: wsh_trip_stops_interface wtsi,
2578: wsh_trips_interface wti
2579: WHERE wti.group_id = c_group_id
4174: -- elsif p_exception_action = CLOSE, then close the exception
4175: -- for WSH_OTM_SHIPMENT_ERROR(used for reprocessing from Interface Mesg
4176: -- Corrections Form)
4177: -- Purge any records in wsh_interface_errors
4178: -- Purge records in wsh_new_del_interface
4179: -- Purge records in wsh_del_legs_interface
4180: -- Purge records in wsh_trip_stops_interface
4181: -- Purge records in wsh_trips_interface
4182: -- Index exists on group_id and trip_interface_id of wsh_trips_interface,
4307: END IF;--}
4308:
4309: -- Purge wsh_interface_errors
4310: -- for each of the above entity, there could be errors logged in wsh_interface_errors
4311: -- table_name can be WSH_NEW_DEL_INTERFACE, WSH_TRIP_STOPS_INTERFACE,
4312: -- WSH_TRIPS_INTERFACE
4313:
4314: IF l_debug_on THEN
4315: WSH_DEBUG_SV.logmsg(l_module_name,'Start Purging Interface Errors Data for delivery');
4316: END IF;
4317: -- Delete errors logged for deliveries
4318: FORALL i in l_trip_interface_id_tab.FIRST..l_trip_interface_id_tab.LAST
4319: Delete from wsh_interface_errors wie
4320: where wie.interface_table_name = 'WSH_NEW_DEL_INTERFACE'
4321: and wie.interface_id in (
4322: select wdli.delivery_interface_id
4323: from wsh_trip_stops_interface wtsi,
4324: wsh_del_legs_interface wdli
4351: where wie.interface_table_name = 'WSH_TRIPS_INTERFACE'
4352: and wie.interface_id = l_trip_interface_id_tab(i)
4353: and wie.interface_action_code = G_TMS_RELEASE_CODE;
4354:
4355: -- Purge wsh_new_del_interface
4356: IF l_debug_on THEN
4357: WSH_DEBUG_SV.logmsg(l_module_name,'Start Purging Deliveries Interface Data');
4358: END IF;
4359: FORALL i in l_trip_interface_id_tab.FIRST..l_trip_interface_id_tab.LAST
4356: IF l_debug_on THEN
4357: WSH_DEBUG_SV.logmsg(l_module_name,'Start Purging Deliveries Interface Data');
4358: END IF;
4359: FORALL i in l_trip_interface_id_tab.FIRST..l_trip_interface_id_tab.LAST
4360: Delete from wsh_new_del_interface wndi
4361: where wndi.delivery_interface_id in (
4362: select wdli.delivery_interface_id
4363: from wsh_trip_stops_interface wtsi,
4364: wsh_del_legs_interface wdli
4631: LAST_UPDATED_BY)
4632: VALUES (
4633: WSH_INTERFACE_ERRORS_S.nextval,
4634: p_group_id, --l_groups(i),
4635: 'WSH_NEW_DEL_INTERFACE', --l_table_names(i),
4636: l_dlvy_id_tab(i),--l_interface_ids(i),
4637: G_TMS_RELEASE_CODE,
4638: l_message_name,
4639: l_message,