Derive Ship Method Including Carrier, Mode of Transport and Service Level Components.sql

SELECT DISTINCT
	ShipMethod
  , MODE_OF_TRANSPORT
  , SERVICE_LEVEL
  , CARRIER_ID
FROM
	(
		SELECT
			/*+ FIRST_ROWS(11) */
			PartyPEO.PARTY_NAME
		  , WarehouseCarrierServicePEO.MODE_OF_TRANSPORT
		  , WarehouseCarrierServicePEO.SERVICE_LEVEL
		  , WarehouseCarrierServicePEO.ORG_CARRIER_SERVICE_ID
		  , WarehouseCarrierServicePEO.CARRIER_ID
		  , ModeOfTransportPEO.LOOKUP_CODE
		  , ModeOfTransportPEO.MEANING
		  , ServiceLevelPEO.LOOKUP_CODE                                                                  AS LOOKUP_CODE1
		  , ServiceLevelPEO.MEANING                                                                      AS MEANING1
		  , (PartyPEO.PARTY_NAME || ' ' || ModeOfTransportPEO.MEANING || ' ' || ServiceLevelPEO.MEANING) AS ShipMethod
		  , WarehouseCarrierServicePEO.SHIP_METHOD_CODE
		  , WarehouseCarrierServicePEO.ORGANIZATION_ID
		  , ModeOfTransportPEO.LOOKUP_TYPE
		  , ServiceLevelPEO.LOOKUP_TYPE AS LOOKUP_TYPE1
		  , WarehouseCarrierServicePEO.ENABLED_FLAG
		  , PartyPEO.PARTY_ID
		  , CarrierPEO.ENABLED_FLAG AS ENABLED_FLAG1
		  , WarehouseCarrierServicePEO.OBJECT_VERSION_NUMBER
		  , CarrierPEO.CARRIER_ID AS CARRIER_ID1
		FROM
			WSH_ORG_CARRIER_SERVICES WarehouseCarrierServicePEO
		  , RCS_LOOKUPS              ModeOfTransportPEO
		  , RCS_LOOKUPS              ServiceLevelPEO
		  , HZ_PARTIES               PartyPEO
		  , WSH_CARRIERS             CarrierPEO
		WHERE
			(
				WarehouseCarrierServicePEO.CARRIER_ID = PartyPEO.PARTY_ID
			)
			AND
			(
				WarehouseCarrierServicePEO.MODE_OF_TRANSPORT = ModeOfTransportPEO.LOOKUP_CODE
			)
			AND
			(
				ModeOfTransportPEO.LOOKUP_TYPE = 'WSH_MODE_OF_TRANSPORT'
			)
			AND
			(
				WarehouseCarrierServicePEO.SERVICE_LEVEL = ServiceLevelPEO.LOOKUP_CODE
			)
			AND
			(
				ServiceLevelPEO.LOOKUP_TYPE = 'WSH_SERVICE_LEVELS'
			)
			AND
			(
				CarrierPEO.CARRIER_ID = WarehouseCarrierServicePEO.CARRIER_ID
			)
			AND
			(
				CarrierPEO.CARRIER_ID = PartyPEO.PARTY_ID
			)
	)
ORDER BY
	shipmethod
-- Use CARRIER_ID returned from first query to obtain the Cross Reference CARRIER_ID id
SELECT
	target_value
FROM
	MSC_XREF_MAPPING
WHERE
	TO_CHAR(source_value) = TO_CHAR( :CARRIER_ID)-- CARRIER_ID from first query)
	AND entity_name       = 'CARRIERS'
	AND attribute_name    = 'CARRIER_ID'
	AND sr_instance_id IN
	(
		SELECT
			a.instance_id
		FROM
			msc_apps_instances a, hz_orig_systems_vl b
		WHERE
			a.instance_id         = b.orig_system_id
			AND a.order_orch_type = 2
			AND b.status          = 'A'
	)
-- Use CARRIER_ID returned from first query to obtain the Carrier Name
SELECT
	PartyPEO.party_id
  , PartyPEO.party_name
FROM
	HZ_PARTIES PartyPEO
WHERE
	PartyPEO.party_id = TO_CHAR(
	&CARRIER_ID FROM FIRST query)
-- Use MODE_OF_TRANSPORT returned from above to obtain the Cross Reference MODE_OF_TRANSPORT id
SELECT
	target_value
FROM
	MSC_XREF_MAPPING
WHERE
	source_value       = '&MODE_OF_TRANSPORT'-- MODE_OF_TRANSPORT from first query) 
	AND entity_name    = 'WSH_MODE_OF_TRANSPORT'
	AND attribute_name = 'LOOKUP_CODE'
	AND sr_instance_id IN
	(
		SELECT
			a.instance_id
		FROM
			msc_apps_instances a, hz_orig_systems_vl b
		WHERE
			a.instance_id         = b.orig_system_id
			AND a.order_orch_type = 2
			AND b.status          = 'A'
	)
-- Use SERVICE_LEVEL returned from first query to obtain the Cross Reference SERVICE_LEVEL id
SELECT
	target_value
FROM
	MSC_XREF_MAPPING
WHERE
	source_value       = '&SERVICE_LEVEL'-- SERVICE_LEVEL from first query
	AND entity_name    = 'WSH_SERVICE_LEVELS'
	AND attribute_name = 'LOOKUP_CODE'
	AND sr_instance_id IN
	(
		SELECT
			a.instance_id
		FROM
			msc_apps_instances a, hz_orig_systems_vl b
		WHERE
			a.instance_id         = b.orig_system_id
			AND a.order_orch_type = 2
			AND b.status          = 'A'
	)
-- To obtain the SHIP METHOD select for a Fulfillment Line
SELECT
	dfla.source_order_number
  , PartyPEO.party_name || ' ' || ModeOfTransportPEO.MEANING || ' ' || ServiceLevelPEO.MEANING "Ship Method"
  , dfla.CARRIER_ID
  , MXM_CAR.SOURCE_VALUE
  , PartyPEO.party_name
  , dfla.SHIP_MODE_OF_TRANSPORT
  , MXM_MOT.SOURCE_VALUE
  , ModeOfTransportPEO.MEANING "Mode of Transport"
  , dfla.SHIP_CLASS_OF_SERVICE
  , MXM_LOS.SOURCE_VALUE
  , ServiceLevelPEO.MEANING "Service Level"
  , wogs.SHIP_METHOD_CODE
  , wogs.ORG_CARRIER_SERVICE_ID
  , wogs.ENABLED_FLAG
FROM
	doo_fulfill_lines_all    dfla
  , msc_xref_mapping         mxm_LOS
  , msc_xref_mapping         mxm_MOT
  , msc_xref_mapping         mxm_CAR
  , RCS_LOOKUPS              ModeOfTransportPEO
  , RCS_LOOKUPS              ServiceLevelPEO
  , HZ_PARTIES               PartyPEO
  , WSH_ORG_CARRIER_SERVICES wogs
WHERE
	(
		dfla.SHIP_CLASS_OF_SERVICE = mxm_LOS.target_value
		AND mxm_LOS.entity_name    = 'WSH_SERVICE_LEVELS'
	)
	AND
	(
		ServiceLevelPEO.LOOKUP_CODE     = MXM_LOS.SOURCE_VALUE
		AND ServiceLevelPEO.LOOKUP_TYPE = 'WSH_SERVICE_LEVELS'
	)
	AND
	(
		dfla.SHIP_MODE_OF_TRANSPORT = mxm_MOT.target_value
		AND mxm_MOT.entity_name     = 'WSH_MODE_OF_TRANSPORT'
	)
	AND
	(
		ModeOfTransportPEO.LOOKUP_CODE     = MXM_MOT.SOURCE_VALUE
		AND ModeOfTransportPEO.LOOKUP_TYPE = 'WSH_MODE_OF_TRANSPORT'
	)
	AND
	(
		TO_CHAR(dfla.CARRIER_ID) = mxm_CAR.target_value
		AND mxm_CAR.entity_name  = 'CARRIERS'
	)
	AND PartyPEO.party_id = mxm_CAR.source_value
	AND
	(
		TO_CHAR(wogs.CARRIER_ID)   = MXM_CAR.SOURCE_VALUE
		AND wogs.SERVICE_LEVEL     = MXM_LOS.SOURCE_VALUE
		AND wogs.MODE_OF_TRANSPORT = MXM_MOT.SOURCE_VALUE
	)
	AND dfla.source_order_number = '&SOURCE_ORDER_NUMBER'
-- To obtain the Ship Method on a Shipment/Delivery
SELECT
	CARRIER_ID
  , SERVICE_LEVEL
  , MODE_OF_TRANSPORT
  , SHIP_METHOD_CODE
  , ORGANIZATION_ID
FROM
	WSH_ORG_CARRIER_SERVICES
WHERE
	SHIP_METHOD_CODE IN
	(
		SELECT DISTINCT
			(ship_method_code)
		FROM
			wsh_new_deliveries
	)
ORDER BY
	4
  , 5