[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
347: wsh_trips wt
348: WHERE wti.interface_action_code IN (G_TMS_RELEASE_CODE,G_TMS_DELETE_CODE)
349: AND wt.tp_plan_name(+) = wti.tp_plan_name
350: AND wti.group_id in
351: (SELECT wti.group_id FROM wsh_new_del_interface wndi,
352: wsh_del_legs_interface wdli,
353: wsh_trip_stops_interface wtsi,
354: wsh_trips_interface wti,
355: wsh_new_deliveries wnd
611: WHERE wtsi.trip_interface_id = wti.trip_interface_id
612: AND wtsi.interface_action_code = G_TMS_RELEASE_CODE
613: AND wti.interface_action_code = G_TMS_RELEASE_CODE
614: AND wti.group_id in
615: (SELECT wti.group_id FROM wsh_new_del_interface wndi,
616: wsh_del_legs_interface wdli,
617: wsh_trip_stops_interface wtsi,
618: wsh_trips_interface wti,
619: wsh_new_deliveries wnd
714: wnd.ultimate_dropoff_location_id,-- used for internal Locations
715: 'N' processed_flag, -- used to indicate which deliveries have to be assigned
716: wnd.tms_interface_flag -- used to check whether tms version check can be avoided.
717: FROM wsh_del_legs_interface wdli,
718: wsh_new_del_interface wndi,
719: wsh_trip_stops_interface wtsi_pu,
720: wsh_trip_stops_interface wtsi_do,
721: wsh_trips_interface wti,
722: wsh_new_deliveries wnd
761: wnd.ultimate_dropoff_location_id,-- used for internal Locations
762: 'N' processed_flag, -- used to indicate which deliveries have to be assigned
763: wnd.tms_interface_flag -- used to check whether tms version check can be avoided.
764: FROM wsh_del_legs_interface wdli,
765: wsh_new_del_interface wndi,
766: wsh_trip_stops_interface wtsi_pu,
767: wsh_trip_stops_interface wtsi_do,
768: wsh_trips_interface wti,
769: wsh_new_deliveries wnd
809: wnd.ultimate_dropoff_location_id,-- used for internal Locations
810: 'N' processed_flag, -- used to indicate which deliveries have to be assigned
811: wnd.tms_interface_flag -- used to check whether tms version check can be avoided.
812: FROM wsh_del_legs_interface wdli,
813: wsh_new_del_interface wndi,
814: wsh_trip_stops_interface wtsi_pu,
815: wsh_trip_stops_interface wtsi_do,
816: wsh_trips_interface wti,
817: wsh_new_deliveries wnd
826: AND wnd.delivery_id = wdli.delivery_id
827: AND wndi.delivery_id = wdli.delivery_id
828: AND wdli.interface_action_code = G_TMS_RELEASE_CODE
829: AND wti.group_id in
830: (SELECT wti.group_id FROM wsh_new_del_interface wndi,
831: wsh_del_legs_interface wdli,
832: wsh_trip_stops_interface wtsi,
833: wsh_trips_interface wti,
834: wsh_new_deliveries wnd
868: wnd.ultimate_dropoff_location_id,-- used for internal Locations
869: 'N' processed_flag, -- used to indicate which deliveries have to be assigned
870: wnd.tms_interface_flag -- used to check whether tms version check can be avoided.
871: FROM wsh_del_legs_interface wdli,
872: wsh_new_del_interface wndi,
873: wsh_trip_stops_interface wtsi_pu,
874: wsh_trip_stops_interface wtsi_do,
875: wsh_trips_interface wti,
876: wsh_new_deliveries wnd
1009: from wsh_trips_interface wti ,
1010: wsh_trip_stops_interface wtsi ,
1011: wsh_del_legs_interface wdli ,
1012: wsh_freight_costs_interface wfci,
1013: wsh_new_del_interface wndi
1014: where wti.trip_interface_id = c_trip_interface_id --Bug7717569
1015: and wdli.delivery_interface_id = wndi.delivery_interface_id
1016: and wndi.delivery_interface_id = wfci.delivery_interface_id (+)
1017: and wndi.interface_action_code = 'TMS_RELEASE'
1384: -- Errors are logged always for the deliveries
1385: --FORALL l_del_wti_index in INDICES OF l_del_wti_tab
1386: FORALL i in l_del_wti_tab.FIRST..l_del_wti_tab.LAST
1387: DELETE from wsh_interface_errors wie
1388: WHERE wie.interface_table_name = 'WSH_NEW_DEL_INTERFACE'
1389: AND wie.interface_id in (
1390: select wdli.delivery_interface_id
1391: from wsh_trip_stops_interface wtsi,
1392: wsh_del_legs_interface wdli
2579: DECLARE
2580: CURSOR c_dock_appt_rec(c_stop_interface_id IN NUMBER, c_group_id IN NUMBER) IS
2581: SELECT wtsi.dock_name, wnd.organization_id, wtsi.start_time, wtsi.end_time
2582: FROM wsh_del_legs_interface wdli,
2583: wsh_new_del_interface wndi,
2584: wsh_new_deliveries wnd,
2585: wsh_trip_stops_interface wtsi,
2586: wsh_trips_interface wti
2587: WHERE wti.group_id = c_group_id
4182: -- elsif p_exception_action = CLOSE, then close the exception
4183: -- for WSH_OTM_SHIPMENT_ERROR(used for reprocessing from Interface Mesg
4184: -- Corrections Form)
4185: -- Purge any records in wsh_interface_errors
4186: -- Purge records in wsh_new_del_interface
4187: -- Purge records in wsh_del_legs_interface
4188: -- Purge records in wsh_trip_stops_interface
4189: -- Purge records in wsh_trips_interface
4190: -- Index exists on group_id and trip_interface_id of wsh_trips_interface,
4315: END IF;--}
4316:
4317: -- Purge wsh_interface_errors
4318: -- for each of the above entity, there could be errors logged in wsh_interface_errors
4319: -- table_name can be WSH_NEW_DEL_INTERFACE, WSH_TRIP_STOPS_INTERFACE,
4320: -- WSH_TRIPS_INTERFACE
4321:
4322: IF l_debug_on THEN
4323: WSH_DEBUG_SV.logmsg(l_module_name,'Start Purging Interface Errors Data for delivery');
4324: END IF;
4325: -- Delete errors logged for deliveries
4326: FORALL i in l_trip_interface_id_tab.FIRST..l_trip_interface_id_tab.LAST
4327: Delete from wsh_interface_errors wie
4328: where wie.interface_table_name = 'WSH_NEW_DEL_INTERFACE'
4329: and wie.interface_id in (
4330: select wdli.delivery_interface_id
4331: from wsh_trip_stops_interface wtsi,
4332: wsh_del_legs_interface wdli
4359: where wie.interface_table_name = 'WSH_TRIPS_INTERFACE'
4360: and wie.interface_id = l_trip_interface_id_tab(i)
4361: and wie.interface_action_code = G_TMS_RELEASE_CODE;
4362:
4363: -- Purge wsh_new_del_interface
4364: IF l_debug_on THEN
4365: WSH_DEBUG_SV.logmsg(l_module_name,'Start Purging Deliveries Interface Data');
4366: END IF;
4367: FORALL i in l_trip_interface_id_tab.FIRST..l_trip_interface_id_tab.LAST
4364: IF l_debug_on THEN
4365: WSH_DEBUG_SV.logmsg(l_module_name,'Start Purging Deliveries Interface Data');
4366: END IF;
4367: FORALL i in l_trip_interface_id_tab.FIRST..l_trip_interface_id_tab.LAST
4368: Delete from wsh_new_del_interface wndi
4369: where wndi.delivery_interface_id in (
4370: select wdli.delivery_interface_id
4371: from wsh_trip_stops_interface wtsi,
4372: wsh_del_legs_interface wdli
4639: LAST_UPDATED_BY)
4640: VALUES (
4641: WSH_INTERFACE_ERRORS_S.nextval,
4642: p_group_id, --l_groups(i),
4643: 'WSH_NEW_DEL_INTERFACE', --l_table_names(i),
4644: l_dlvy_id_tab(i),--l_interface_ids(i),
4645: G_TMS_RELEASE_CODE,
4646: l_message_name,
4647: l_message,