[Home] [Help]
376: wt.trip_id TRIP_ID,
377: max(wts.stop_sequence_number) over (partition by wt.trip_id) ULTIMATE_STOP_SEQUENCE_NUMBER
378: FROM (select /*+ PARALLEL(wts_tmp) */ distinct trip_id
379: from wsh_trip_stops wts_tmp where actual_departure_date > g_global_start_date) idl,
380: wsh_trips wt,
381: wsh_trip_stops wts
382: WHERE idl.trip_id = wt.trip_id
383: AND wt.trip_id = wts.trip_id
384: AND wt.status_code IN ('IT', 'CL')
556: gl_sets_of_books gsb
557: WHERE fih.mode_of_transport = 'TL'
558: AND fih.bill_status in ('APPROVED', 'IN_PROGRESS', 'PAID', 'PARTIALLY PAID', 'PARTIAL_PAID')
559: AND fih.bol = wdi.sequence_number
560: AND wdi.entity_name = 'WSH_TRIPS'
561: AND wdi.document_type = 'MBOL'
562: AND wdi.entity_id = itr.trip_id
563: AND itr.stop_rank = 1
564: AND fih.org_id = aspa.org_id
816:
817: DELETE FROM isc_dbi_trip_stops_f
818: WHERE stop_id IN (SELECT DISTINCT log.stop_id
819: FROM isc_dbi_tmp_wts_log log
820: WHERE NOT EXISTS (select '1' from wsh_trip_stops wts, wsh_trips wt
821: where log.stop_id = wts.stop_id
822: and wts.trip_id = wt.trip_id
823: and wt.status_code IN ('IT', 'CL')));
824:
863: max(wts.stop_sequence_number) over (partition by wt.trip_id) ULTIMATE_STOP_SEQUENCE_NUMBER
864: FROM (select /*+ no_merge index(tr) */ distinct tr.trip_id
865: from isc_dbi_tmp_wts_log tmp, wsh_trip_stops tr
866: where tmp.stop_id = tr.stop_id) log,
867: wsh_trips wt,
868: wsh_trip_stops wts
869: WHERE log.trip_id = wt.trip_id
870: AND wt.trip_id = wts.trip_id
871: AND wt.status_code IN ('IT', 'CL')
1152: WHERE log.invoice_header_id = fih.invoice_header_id
1153: AND fih.mode_of_transport = 'TL'
1154: AND fih.bill_status in ('APPROVED', 'IN_PROGRESS', 'PAID', 'PARTIALLY PAID', 'PARTIAL_PAID')
1155: AND fih.bol = wdi.sequence_number
1156: AND wdi.entity_name = 'WSH_TRIPS'
1157: AND wdi.document_type = 'MBOL'
1158: AND wdi.entity_id = itr.trip_id
1159: AND itr.stop_rank = 1
1160: AND fih.org_id = aspa.org_id