Intransit Time Conversion

Using the below query we can find the intransit time and other details.

SELECT mism.ship_method f01
, mism.intransit_time f02
, mism.default_flag f03
, mism.from_organization_id f04
, ood.organization_name f05
, mism.to_organization_id f06
, ood1.organization_name f07
, mism.from_location_id f08
, mism.to_location_id f09
, mism.last_update_date f10
, mism.last_updated_by f11
, mism.creation_date f12
, mism.created_by f13
, mism.last_update_login f14
, mism.attribute_category f15
, mism.to_region_id f16
, mism.destination_type f17
, 'CREATE' f18
FROM mtl_interorg_ship_methods mism
, org_organization_definitions ood
, org_organization_definitions ood1
WHERE mism.from_organization_id = ood.organization_id
AND mism.to_organization_id = ood1.organization_id
AND NOT EXISTS(
SELECT 'X'
FROM xxcofi_time_extract_tbl xxtime
WHERE mism.from_organization_id = xxtime.from_organization_ID
AND mism.to_organization_id = xxtime.to_organization_id
AND mism.intransit_time = xxtime.intransit_time)
-- To exclude the records which alre already processed in staging table
UNION
SELECT DISTINCT NULL
, 1
, NULL
, TO_NUMBER(vendor_site_code) f05
, vendor_site_code f05
, organization_id F06
, substr(dest_org_code,1,4) f07
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, 'CREATE'
FROM xxcofi_sourcing_dc_to_vendor xxsource
WHERE NOT EXISTS(
SELECT 'X'
FROM xxcofi_time_extract_tbl xxtime
WHERE xxsource.vendor_site_code = xxtime.from_organization_name
AND xxsource.organization_id = xxtime.to_organization_id
AND 1 = xxtime.intransit_time);
-- To exclude the records which alre already processed in staging table