Freight Carrier Extract query(link between WSH_CARRIERS and WSH_CARRIER_SERVICES)

SELECT  -- WC.CARRIER_ID,
         HP.PARTY_NAME NAME,
         WC.FREIGHT_CODE SHORT_NAME,
         HP.STATUS ACTIVE_FLAG,
         WC.SCAC_CODE,
         WC.CURRENCY_CODE DEFAULT_CURRENCY,
         WC.GENERIC_FLAG,
         FLV.MEANING SERVICE_LEVEL,
         WCS.MODE_OF_TRANSPORT,
         FL.MEANING SHIP_METHOD_MEANING,
         WCS.SL_TIME_UOM,
         WCS.MIN_SL_TIME,
         WCS.MAX_SL_TIME,
         WCS.ENABLED_FLAG,
         WCS.WEB_ENABLED,
         WC.ATTRIBUTE1||WC.ATTRIBUTE2||WC.ATTRIBUTE3||WC.ATTRIBUTE4||WC.ATTRIBUTE5||WC.ATTRIBUTE6||WC.ATTRIBUTE7||WC.ATTRIBUTE8||WC.ATTRIBUTE9||WC.ATTRIBUTE10||WC.ATTRIBUTE11||WC.ATTRIBUTE12||WC.ATTRIBUTE13||WC.ATTRIBUTE14||WC.ATTRIBUTE15 DFF,
         WCSM.SITE_NUMBER,
         WCSM.LOCATION ADDRESS,
         WCSM.ACTIVE SITE_ACTIVE_FLAG,
         WCC.PERSON_LAST_NAME,
         WCC.PERSON_FIRST_NAME,
         WCC.PERSON_PRE_NAME_ADJUNCT TITLE,
         WCC.ACTIVE CONTACT_NAME_ACTIVE_FLAG,
         WCCI.COUNTRY_CODE,
         WCCI.AREA_CODE,
         WCCI.PHONE_NUMBER,
         WCCI.EXTENSION,
         WCCI.CONTACT_TYPE,
         WCCI.PRIMARY PRIMARY_FLAG,
         WCCI.ACTIVE CONTACT_TELE_ACTIVE_FLAG,
         WCCV.CARRIER_CLASS_CATEGORY CATEGORY,
         WCCV.ACTIVE CATEGORY_ENABLED_FLAG,
         WC.MANIFESTING_ENABLED_FLAG,
         WC.WEIGHT_UOM WEIGHT,
         WC.TIME_UOM TIME,
         WC.DIMENSION_UOM DIMENSION,
         WC.VOLUME_UOM VOLUME,
         WC.DISTANCE_UOM DISTANCE,
         PV.VENDOR_NAME SUPPLIER,
         PVS.VENDOR_SITE_CODE DEFAULT_SUPPLIER_SITE,
         WC.FREIGHT_BILL_AUTO_APPROVAL AUTO_APPROVE_BILLS_FLAG,
         WC.FREIGHT_AUDIT_LINE_LEVEL AUDIT_BILL_LINES_FLAG,
         WC.ORIGIN_DSTN_SURCHARGE_LEVEL SURCHARGE_LEVEL,
         WC.CM_FIRST_LOAD_DISCOUNT FIRST_LOAD_DISCOUNT_FLAG,
         WC.DISTANCE_CALCULATION_METHOD DISTANCE_CALCULATION,
         WC.CM_FREE_DH_MILEAGE FREE_DEADHEAD_DISTANCE,
         WC.MAX_OUT_OF_ROUTE "MAX_OUT_OF_ROUTE%",
         WC.MIN_CM_TIME MIN_TIME,
         WC.UNIT_RATE_BASIS,
         WC.MIN_CM_DISTANCE MIN_DISTANCE,
         WC.CM_RATE_VARIANT RATE_VARIANT,
         WC.ALLOW_INTERSPERSE_LOAD ALLOW_INTERSPERSAL_LOAD_FLAG,
         WC.ALLOW_CONTINUOUS_MOVE ALLOW_CONTINUOUS_MOVE_FLAG,
         WC.MAX_NUM_STOPS_PERMITTED MAX_NUMBER_OF_STOPS,
         WC.MAX_CM_TIME MAX_TIME,
         WC.MAX_TOTAL_DISTANCE,
         WC.MAX_CM_DISTANCE MAX_DISTANCE,
         WC.MAX_TOTAL_TIME,
         WC.MAX_CM_DH_DISTANCE MAX_DEADHEAD_DISTANCE,
         WC.MAX_CM_DH_TIME MAX_DEADHEAD_TIME,
         WC.MIN_LAYOVER_TIME,
         WC.MAX_TOTAL_DISTANCE_IN_24HR MAX_DISTANCE_PER_DAY,
         WC.MAX_LAYOVER_TIME,
         WC.MAX_DRIVING_TIME_IN_24HR MAX_DRIVING_TIME_PER_DAY,
         WC.MAX_DUTY_TIME_IN_24HR MAX_ON_DUTY_TIME_PER_DAY,
         WC.MIN_SIZE_LENGTH,
         WC.MAX_SIZE_LENGTH,
         WC.MIN_SIZE_HEIGHT,
         WC.MAX_SIZE_HEIGHT,
         WC.MIN_SIZE_WIDTH,
         WC.MAX_SIZE_WIDTH
  FROM   HZ_PARTIES HP,
         WSH_CARRIERS WC,
         WSH_CARRIER_SERVICES WCS,
         FND_LOOKUP_VALUES_VL FL,
         FND_LOOKUP_VALUES FLV,
         WSH_CARRIER_SITES_MAIN_V WCSM,
         WSH_CARRIER_CONTACTS_V WCC,
         HZ_PARTY_RELATIONSHIPS HPR,
         WSH_CARRIER_CONTACT_INFO_V WCCI,
         WSH_CARRIER_CLASSIFICATIONS_V WCCV,
         PO_VENDORS PV,
         PO_VENDOR_SITES_ALL PVS
 WHERE       WC.CARRIER_ID = HP.PARTY_ID
         AND WC.CARRIER_ID = WCS.CARRIER_ID
         AND FL.LOOKUP_TYPE = 'SHIP_METHOD'
         AND FL.LOOKUP_CODE = WCS.SHIP_METHOD_CODE
         AND FL.VIEW_APPLICATION_ID = 3
         AND FLV.LOOKUP_TYPE = 'WSH_SERVICE_LEVELS'
         AND WCS.SERVICE_LEVEL = FLV.LOOKUP_CODE
         AND WC.CARRIER_ID = WCSM.CARRIER_ID(+)
         AND WC.CARRIER_ID = WCC.CARRIER_ID(+)
         AND WC.CARRIER_ID = HPR.OBJECT_ID(+)
         AND HPR.PARTY_ID = WCCI.RELATION_PARTY_ID(+)
         AND WC.CARRIER_ID = WCCV.PARTY_ID(+)
         AND WC.SUPPLIER_ID = PV.VENDOR_ID(+)
         AND WC.SUPPLIER_SITE_ID = PVS.VENDOR_SITE_ID(+)
         --AND HP.PARTY_NAME = 'PO- MERCER'