APPS.WSH_OTM_RIQ_XML SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 17

	--Selects one option for the Choose Ship Method Action
	G_XPATH_SM_OPTION CONSTANT VARCHAR2(200):='/RemoteQueryReply/RIQQueryReply/RIQResult';

Line: 124

  SELECT NVL(order_date_type_code,'SHIP')
    FROM oe_order_headers_all
   WHERE header_id = p_header_id ;

Line: 136

  SELECT GREATEST(SYSDATE, NVL(schedule_ship_date, request_date)) ship_date,
         DECODE(p_order_date_type,
                'ARRIVAL',GREATEST(SYSDATE, NVL(schedule_arrival_date, request_date)),
                NULL) arrival_date,
         schedule_ship_date,
         schedule_arrival_date
    FROM oe_order_lines_all
   WHERE line_id = p_line_id;

Line: 1013

SELECT carrier_id
FROM WSH_CARRIERS
WHERE carrier_id= c_carrier_id;

Line: 1091

  SELECT lookup_code
    FROM fnd_lookup_values
   WHERE lookup_type = p_lookup_type
     AND UPPER(lookup_code) = UPPER(c_lookup_code)
     AND nvl(start_date_active,SYSDATE) <= SYSDATE
     AND nvl(end_date_active,SYSDATE)   >= SYSDATE
     AND enabled_flag = 'Y'
     AND view_application_id in (660,665);

Line: 1171

	SELECT uom_code
	FROM MTL_UNITS_OF_MEASURE
	WHERE uom_class=c_uom_class
	AND nvl(disable_date, sysdate) >= SYSDATE
	AND attribute15=c_uom;

Line: 1179

	SELECT UOM_CODE
	FROM mtl_units_of_measure
	WHERE uom_code = c_uom
	AND nvl(disable_date, sysdate) >= SYSDATE
	AND uom_class = c_uom_class;

Line: 1276

	SELECT attribute15
	FROM MTL_UNITS_OF_MEASURE
	WHERE uom_code=c_uom
	AND nvl(disable_date, sysdate) >= SYSDATE;

Line: 1365

   SELECT freight_code,generic_flag
   FROM   wsh_carriers
   WHERE carrier_id = c_carrier_id;

Line: 1447

	SELECT position
	FROM TABLE (CAST (c_tab AS WSH_OTM_RIQ_SORT_TAB))
	ORDER BY numberValue;

Line: 1514

		x_source_header_rates_tab.DELETE;

Line: 1519

		l_out_tab.DELETE;

Line: 1520

		l_index_tab.DELETE;

Line: 1521

		l_sort_tab.DELETE;

Line: 1665

   SELECT a.ship_method_code
   FROM wsh_carrier_services a, wsh_org_carrier_services b
   WHERE a.carrier_service_id = b.carrier_service_id
     AND b.organization_id = c_org_id
     AND b.enabled_flag = 'Y'
     AND a.enabled_flag = 'Y'
     AND a.mode_of_transport = c_mode_of_trans
     AND UPPER(a.service_level) = UPPER(c_service_level)
     AND a.carrier_id = c_carrier_id;

Line: 1768

SELECT r.state_code
FROM  WSH_REGION_LOCATIONS rl ,WSH_REGIONS r
WHERE rl.location_id=p_location_id
	AND rl.region_type=g_state_region_type
	AND rl.region_id = r.region_id;

Line: 1776

SELECT wl.postal_code,wl.city,wl.state, ft.iso_territory_code
FROM	WSH_LOCATIONS wl, FND_TERRITORIES ft
WHERE
	wl.wsh_location_id=p_location_id
	AND wl.country=ft.territory_code;

Line: 1784

SELECT hz.postal_code, hz.city,hz.state,ft.iso_territory_code
FROM HZ_LOCATIONS hz,FND_TERRITORIES ft
WHERE
	hz.location_id=p_location_id
	AND hz.country=ft.territory_code;

Line: 1793

SELECT hr.postal_code, hr.town_or_city,hr.region_2,ft.iso_territory_code
FROM HR_LOCATIONS_ALL hr,FND_TERRITORIES ft
WHERE
	hr.location_id=p_location_id
	AND hr.country=ft.territory_code;

Line: 1933

   SELECT unit_length,unit_width,unit_height,dimension_uom_code
   FROM   mtl_system_items
   WHERE  inventory_item_id = c_inv_item_id AND organization_id=c_org_id;

Line: 1971

	x_source_line_tab.DELETE;

Line: 2226

	SELECT carrier_id, mode_of_transport,service_level
	FROM   wsh_carrier_services
	WHERE  ship_method_code = c_shp_mthd_cd;

Line: 2263

	x_source_header_tab.DELETE;

Line: 2622

	SELECT 	XMLELEMENT("Transmission",
	XMLFOREST(
		XMLFOREST(
			c_header_rec.TransmissionType AS "TransmissionType",
			c_header_rec.UserName AS "UserName",
			c_header_rec.Passwd AS "Password"
		) AS "TransmissionHeader",

		XMLELEMENT("GLogXMLElement",
			XMLELEMENT("RemoteQuery",
				XMLELEMENT("RIQQuery",
					XMLConcat(

						XMLELEMENT("RIQRequestType",
							c_header_rec.RIQRequestType
						),
						XMLElement("SourceAddress",
							XMLForest(
								XMLFOREST(
									c_header_rec.SourceCity AS "City",
									c_header_rec.SourceProvinceCode AS "ProvinceCode",
									c_header_rec.SourcePostalCode AS "PostalCode",
									XMLFOREST(
										XMLFOREST(
											c_header_rec.SourceCountryDomain AS "DomainName",
											c_header_rec.SourceCountryCode AS "Xid"
										) AS "Gid"
									) AS "CountryCode3Gid",
									XMLELEMENT("Gid",
										XMLFOREST(
											c_header_rec.SourceLocationDomain AS "DomainName",
											c_header_rec.SourceLocationId AS "Xid"
										)
									) AS "LocationGid"
								) AS "MileageAddress",
								XMLFOREST(
									XMLFOREST(
										c_header_rec.SourceLocationDomain AS "DomainName",
										c_header_rec.SourceCorporationId AS "Xid"
									) AS "Gid"
								) AS "CorporationGid"
							)
						),
						XMLElement("DestAddress",
							XMLForest(

								XMLFOREST(
									c_header_rec.DestCity AS "City",
									c_header_rec.DestProvinceCode AS "ProvinceCode",
									c_header_rec.DestPostalCode AS "PostalCode",
									XMLFOREST(
										XMLFOREST(
											c_header_rec.DestCountryDomain AS "DomainName",
											c_header_rec.DestCountryCode AS "Xid"
										) AS "Gid"
									) AS "CountryCode3Gid",

									XMLELEMENT("Gid",
										XMLFOREST(
											c_header_rec.DestLocationDomain AS "DomainName",
											c_header_rec.DestLocationId AS "Xid"
										)

									) AS "LocationGid"
								) AS "MileageAddress",
								XMLFOREST(
									XMLFOREST(
										c_header_rec.DestLocationDomain AS "DomainName",
										c_header_rec.DestCorporationId AS "Xid"
									) AS "Gid"
								) AS "CorporationGid"
							)
						),
						XMLFOREST(
							XMLFOREST(
								c_header_rec.AvailableByDate AS "AvailableDate",
								XMLFOREST(
									XMLFOREST(
										c_header_rec.AvailableByTZDomain AS "DomainName",
										c_header_rec.AvailableByTimeZoneCode AS "Xid"
									) AS "Gid"
								) AS "TimeZoneGid"
							) AS "AvailableBy"
						),
						XMLFOREST(
							XMLFOREST(
								c_header_rec.DeliveryByDate AS "DeliveryByDate",

								XMLFOREST(
									XMLFOREST(
										c_header_rec.DeliveryByTZDomain AS "DomainName",
										c_header_rec.DeliveryByTimeZoneCode AS "Xid"
									) AS "Gid"
								) AS "TimeZoneGid"
							) AS "DeliveryBy"
						),
						XMLELEMENT("Perspective",
							c_header_rec.Perspective
						),
						XMLELEMENT("UseRIQRoute",
							c_header_rec.UseRIQRoute
						),
						XMLAgg(
							XMLELEMENT("ShipUnit",
								XMLFOREST(

									XMLFOREST(
										XMLFOREST(
											c_header_rec.ShipUnitGid AS "Xid"
										) AS "Gid"

									) AS "ShipUnitGid",

									XMLFOREST(
										XMLFOREST(
											e.weight AS "WeightValue",
											XMLFOREST(
												XMLFOREST(
													e.weightUOMDomain AS "DomainName",
													e.weightUOM AS "Xid"
												) AS "Gid"
											) AS "WeightUOMGid"

										) AS "Weight",
										XMLFOREST(
											e.volume AS "VolumeValue",
											XMLFOREST(
												XMLFOREST(
													e.volumeUOMDomain AS "DomainName",
													e.volumeUOM AS "Xid"
												) AS "Gid"
											) AS "VolumeUOMGid"
										) AS "Volume"
									) AS "WeightVolume",
									XMLFOREST(
										XMLFOREST(
											e.Length AS "LengthValue",
											XMLFOREST(
												XMLFOREST(
													e.lengthUOMDomain AS "DomainName",
													e.lengthUOM AS "Xid"
												) AS "Gid"
											) AS "LengthUOMGid"
										) AS "Length",
										XMLFOREST(
											e.width AS "WidthValue",
											XMLForest(
												XMLFOREST(
													e.widthUOMDomain AS "DomainName",
													e.widthUOM AS "Xid"
												) AS "Gid"

											) AS "WidthUOMGid"
										) AS "Width",
										XMLFOREST(
											e.height AS "HeightValue",
											XMLFOREST(
												XMLFOREST(
													e.heightUOMDomain AS "DomainName",
													e.heightUOM AS "Xid"
												) AS "Gid"
											) AS "HeightUOMGid"
										) AS "Height"
									) AS "LengthWidthHeight",

									XMLFOREST(
										XMLFOREST(
											XMLFOREST(
												XMLFOREST(
													e.ItemDomain AS "DomainName",
													e.ItemId AS "Xid"
												) AS "Gid"
											) AS "PackagedItemGid"
										) AS "PackagedItemRef",
										e.LineNumber AS "LineNumber"
									) AS "ShipUnitContent",
									c_header_rec.ShipUnitCount AS "ShipUnitCount"
								)
							)
						)
					)
				)
			)
		) AS "TransmissionBody"
	)
	)
	FROM TABLE(CAST(c_line_tab AS WSH_OTM_RIQ_LINE_TAB )) e;

Line: 2816

	SELECT
	XMLELEMENT("Transmission",
	XMLFOREST(
		XMLFOREST(
			c_header_rec.TransmissionType AS "TransmissionType",
			c_header_rec.UserName AS "UserName",
			c_header_rec.Passwd AS "Password"
		) AS "TransmissionHeader",

		XMLELEMENT("GLogXMLElement",
			XMLELEMENT("RemoteQuery",
				XMLELEMENT("RIQQuery",
					XMLConcat(

						XMLELEMENT("RIQRequestType",
							c_header_rec.RIQRequestType
						),
						XMLElement("SourceAddress",
							XMLForest(
								XMLFOREST(
									c_header_rec.SourceCity AS "City",
									c_header_rec.SourceProvinceCode AS "ProvinceCode",
									c_header_rec.SourcePostalCode AS "PostalCode",
									XMLFOREST(
										XMLFOREST(
											c_header_rec.SourceCountryDomain AS "DomainName",
											c_header_rec.SourceCountryCode AS "Xid"
										) AS "Gid"
									) AS "CountryCode3Gid",
									XMLELEMENT("Gid",
										XMLFOREST(
											c_header_rec.SourceLocationDomain AS "DomainName",
											c_header_rec.SourceLocationId AS "Xid"
										)
									) AS "LocationGid"
								) AS "MileageAddress",
								XMLFOREST(
									XMLFOREST(
										c_header_rec.SourceLocationDomain AS "DomainName",
										c_header_rec.SourceCorporationId AS "Xid"
									) AS "Gid"
								) AS "CorporationGid"
							)
						),
						XMLElement("DestAddress",
							XMLForest(

								XMLFOREST(
									c_header_rec.DestCity AS "City",
									c_header_rec.DestProvinceCode AS "ProvinceCode",
									c_header_rec.DestPostalCode AS "PostalCode",
									XMLFOREST(
										XMLFOREST(
											c_header_rec.DestCountryDomain AS "DomainName",
											c_header_rec.DestCountryCode AS "Xid"
										) AS "Gid"
									) AS "CountryCode3Gid",
									XMLELEMENT("Gid",
										XMLFOREST(
											c_header_rec.DestLocationDomain AS "DomainName",
											c_header_rec.DestLocationId AS "Xid"
										)

									) AS "LocationGid"
								) AS "MileageAddress",
								XMLFOREST(
									XMLFOREST(
										c_header_rec.DestLocationDomain AS "DomainName",
										c_header_rec.DestCorporationId AS "Xid"
									) AS "Gid"
								) AS "CorporationGid"
							)
						),

						XMLFOREST(
							XMLFOREST(
								XMLFOREST(
									c_header_rec.ModeOfTransportDomain AS "DomainName",
									c_header_rec.ModeOfTransportCode AS "Xid"

								) AS "Gid"
							) AS "TransportModeGid"
						),
						XMLFOREST(
							XMLFOREST(
								XMLFOREST(
									c_header_rec.ServiceProviderDomain AS "DomainName",
									c_header_rec.ServiceProviderId AS "Xid"

								) AS "Gid"
							) AS "ServiceProviderGid"
						),
						XMLFOREST(
							XMLFOREST(
								XMLFOREST(
									c_header_rec.RateServiceDomain AS "DomainName",
									c_header_rec.RateServiceCode AS "Xid"

								) AS "Gid"
							) AS "RateServiceGid"
						),
						XMLFOREST(
							XMLFOREST(
								c_header_rec.AvailableByDate AS "AvailableDate",
								XMLFOREST(
									XMLFOREST(
										c_header_rec.AvailableByTZDomain AS "DomainName",
										c_header_rec.AvailableByTimeZoneCode AS "Xid"
									) AS "Gid"
								) AS "TimeZoneGid"
							) AS "AvailableBy"
						),
						XMLFOREST(
							XMLFOREST(
								c_header_rec.DeliveryByDate AS "DeliveryByDate",

								XMLFOREST(
									XMLFOREST(
										c_header_rec.DeliveryByTZDomain AS "DomainName",
										c_header_rec.DeliveryByTimeZoneCode AS "Xid"
									) AS "Gid"
								) AS "TimeZoneGid"
							) AS "DeliveryBy"
						),
						XMLELEMENT("Perspective",
							c_header_rec.Perspective
						),
						XMLELEMENT("UseRIQRoute",
							c_header_rec.UseRIQRoute
						),

						XMLAgg(
							XMLELEMENT("ShipUnit",
								XMLFOREST(

									XMLFOREST(
										XMLFOREST(
											c_header_rec.ShipUnitGid AS "Xid"
										) AS "Gid"

									) AS "ShipUnitGid",

									XMLFOREST(
										XMLFOREST(
											e.weight AS "WeightValue",
											XMLFOREST(
												XMLFOREST(
													e.weightUOMDomain AS "DomainName",
													e.weightUOM AS "Xid"
												) AS "Gid"
											) AS "WeightUOMGid"

										) AS "Weight",
										XMLFOREST(
											e.volume AS "VolumeValue",
											XMLFOREST(
												XMLFOREST(
													e.volumeUOMDomain AS "DomainName",
													e.volumeUOM AS "Xid"
												) AS "Gid"
											) AS "VolumeUOMGid"
										) AS "Volume"
									) AS "WeightVolume",
									XMLFOREST(
										XMLFOREST(
											e.Length AS "LengthValue",
											XMLFOREST(
												XMLFOREST(
													e.lengthUOMDomain AS "DomainName",
													e.lengthUOM AS "Xid"
												) AS "Gid"
											) AS "LengthUOMGid"
										) AS "Length",
										XMLFOREST(
											e.width AS "WidthValue",
											XMLForest(
												XMLFOREST(
													e.widthUOMDomain AS "DomainName",
													e.widthUOM AS "Xid"
												) AS "Gid"

											) AS "WidthUOMGid"
										) AS "Width",
										XMLFOREST(
											e.height AS "HeightValue",
											XMLFOREST(
												XMLFOREST(
													e.heightUOMDomain AS "DomainName",
													e.heightUOM AS "Xid"
												) AS "Gid"
											) AS "HeightUOMGid"
										) AS "Height"
									) AS "LengthWidthHeight",
									XMLFOREST(
										XMLFOREST(
											XMLFOREST(
												XMLFOREST(
													e.ItemDomain AS "DomainName",
													e.ItemId AS "Xid"
												) AS "Gid"
											) AS "PackagedItemGid"
										) AS "PackagedItemRef",
										e.LineNumber AS "LineNumber"
									) AS "ShipUnitContent",

									c_header_rec.ShipUnitCount AS "ShipUnitCount"
								)
							)
						)
					)
				)
			)
		) AS "TransmissionBody"
	)
	)
	FROM TABLE(CAST(c_line_tab AS WSH_OTM_RIQ_LINE_TAB )) e;

Line: 3037

	SELECT
	XMLELEMENT("Transmission",
	XMLFOREST(
		XMLFOREST(
			c_header_rec.TransmissionType AS "TransmissionType",
			c_header_rec.UserName AS "UserName",
			c_header_rec.Passwd AS "Password"
		) AS "TransmissionHeader",

		XMLELEMENT("GLogXMLElement",
			XMLELEMENT("RemoteQuery",
				XMLELEMENT("OrderRoutingRuleQuery",
					XMLConcat(

						XMLElement("SourceAddress",
							XMLForest(
								XMLFOREST(
									c_header_rec.SourceCity AS "City",
									c_header_rec.SourceProvinceCode AS "ProvinceCode",
									c_header_rec.SourcePostalCode AS "PostalCode",
									XMLFOREST(
										XMLFOREST(
											c_header_rec.SourceCountryDomain AS "DomainName",
											c_header_rec.SourceCountryCode AS "Xid"
										) AS "Gid"
									) AS "CountryCode3Gid",

									XMLELEMENT("Gid",
										XMLFOREST(
											c_header_rec.SourceLocationDomain AS "DomainName",
											c_header_rec.SourceLocationId AS "Xid"
										)
									) AS "LocationGid"
								) AS "MileageAddress",
								XMLFOREST(
									XMLFOREST(
										c_header_rec.SourceLocationDomain AS "DomainName",
										c_header_rec.SourceCorporationId AS "Xid"
									) AS "Gid"
								) AS "CorporationGid"
							)
						),
						XMLElement("DestAddress",
							XMLForest(

								XMLFOREST(
									c_header_rec.DestCity AS "City",
									c_header_rec.DestProvinceCode AS "ProvinceCode",
									c_header_rec.DestPostalCode AS "PostalCode",
									XMLFOREST(
										XMLFOREST(
											c_header_rec.DestCountryDomain AS "DomainName",
											c_header_rec.DestCountryCode AS "Xid"
										) AS "Gid"
									) AS "CountryCode3Gid",
									XMLELEMENT("Gid",
										XMLFOREST(
											c_header_rec.DestLocationDomain AS "DomainName",
											c_header_rec.DestLocationId AS "Xid"
										)

									) AS "LocationGid"
								) AS "MileageAddress",
								XMLFOREST(
									XMLFOREST(
										c_header_rec.DestLocationDomain AS "DomainName",
										c_header_rec.DestCorporationId AS "Xid"
									) AS "Gid"
								) AS "CorporationGid"
							)
						),

						XMLFOREST(
							c_header_rec.AvailableByDate AS "EstDepartureDate"
						),

						XMLFOREST(
							c_header_rec.DeliveryByDate AS "EstArrivalDate"
						),
						XMLFOREST(
							XMLFOREST(
								c_header_rec.TotalWeight AS "WeightValue",
								XMLFOREST(
									XMLFOREST(
										c_header_rec.TotalWeightUOMDomain AS "DomainName",
										c_header_rec.TotalWeightUOM AS "Xid"
									) AS "Gid"
								) AS "WeightUOMGid"

							) AS "Weight"
						),
						XMLFOREST(
							XMLFOREST(
								c_header_rec.TotalVolume AS "VolumeValue",
								XMLFOREST(
									XMLFOREST(
										c_header_rec.TotalVolumeUOMDomain AS "DomainName",
										c_header_rec.TotalVolumeUOM AS "Xid"
									) AS "Gid"
								) AS "VolumeUOMGid"
							) AS "Volume"
						),
						XMLFOREST(
							XMLFOREST(
								XMLFOREST(
									c_header_rec.PaymentMethodDomain AS "DomainName",
									c_header_rec.PaymentMethodCode AS "Xid"
								) AS "Gid"
							) AS "PaymentMethodCodeGid"
						)
					)
				)
			)
		) AS "TransmissionBody"
	)
	)
	FROM TABLE(CAST(c_line_tab AS WSH_OTM_RIQ_LINE_TAB )) e;

Line: 3240

	SELECT XMLSEQUENCE(EXTRACT(C_XML_IN,C_XPATH,g_xml_namespace_map)) XML
	FROM DUAL;

Line: 3485

  SELECT user_conversion_type
    FROM gl_daily_conversion_types
   WHERE conversion_type = p_curr_conv_type;

Line: 4408

				--Insert a charge rec only if charge > 0
				IF ((p_charge IS NOT NULL) AND (p_charge > 0))
				THEN

					IF l_debug_on THEN

						WSH_DEBUG_SV.log(l_module_name,'Creating Charge line');

Line: 5078

        SELECT transactional_curr_code
        FROM oe_order_headers_all
        WHERE header_id = p_source_header_id;

Line: 5763

	SELECT gu_time_class
	FROM WSH_GLOBAL_PARAMETERS;

Line: 5768

	SELECT freight_cost_type_id
	FROM WSH_FREIGHT_COST_TYPES
	WHERE name='PRICE' AND freight_cost_type_code='FTEPRICE';

Line: 5796

	g_carrier_freight_codes.DELETE;

Line: 5797

	g_carrier_generic_flags.DELETE;

Line: 5853

	g_EBS_to_OTM_UOM_map.DELETE;

Line: 5854

	g_OTM_to_EBS_UOM_map.DELETE;

Line: 5912

	g_carrier_freight_codes.DELETE;

Line: 5913

	g_carrier_generic_flags.DELETE;

Line: 5916

	g_EBS_to_OTM_UOM_map.DELETE;

Line: 5917

	g_OTM_to_EBS_UOM_map.DELETE;

Line: 6004

		l_source_line_tab_xml.DELETE;

Line: 6311

        g_source_line_tab_temp.DELETE;