[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT wsh_carrier_services_s.nextval
FROM sys.dual;
SELECT rowid
FROM WSH_CARRIER_SERVICES
WHERE carrier_service_id = p_carrier_service_id;
WSH_DEBUG_SV.log(l_module_name, 'UPDATE_MOT_SL',
p_Carrier_Service_Info.UPDATE_MOT_SL);
Select count(*)
into l_smc
From fnd_lookup_values
Where lookup_type like 'SHIP_METHOD' and
lookup_code like p_Carrier_Service_Info.ship_method_code;
l_procedure := 'Calling FND_LOOKUP_VALUE_PKG.INSERT_ROW';
FND_LOOKUP_VALUES_PKG.INSERT_ROW(
X_ROWID => l_rowid,
X_LOOKUP_TYPE => 'SHIP_METHOD',
X_SECURITY_GROUP_ID => 0,
X_VIEW_APPLICATION_ID =>3,
X_LOOKUP_CODE => p_Carrier_Service_Info.ship_method_code,
X_TAG => NULL,
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ENABLED_FLAG => 'Y',
X_START_DATE_ACTIVE => SYSDATE,
X_END_DATE_ACTIVE => NULL,
X_TERRITORY_CODE => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL,
X_MEANING => p_Carrier_Service_Info.ship_method_meaning,
X_DESCRIPTION => p_Carrier_Service_Info.ship_method_meaning,
X_CREATION_DATE => sysdate,
X_CREATED_BY => FND_GLOBAL.USER_ID,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
X_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID);
l_procedure := 'Inserting Into Wsh_Carrier_Services';
WSH_DEBUG_SV.log(l_module_name,'Inserting into wsh_carrier_services');
INSERT INTO wsh_carrier_services(
carrier_service_id,
carrier_id,
mode_of_transport,
enabled_flag,
web_enabled,
service_level,
min_sl_time,
max_sl_time,
sl_time_uom,
ship_method_code,
ship_method_meaning,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
-- Pack J Enhancement
MAX_NUM_STOPS_PERMITTED,
MAX_TOTAL_DISTANCE,
MAX_TOTAL_TIME,
ALLOW_INTERSPERSE_LOAD,
MAX_LAYOVER_TIME,
MIN_LAYOVER_TIME,
MAX_TOTAL_DISTANCE_IN_24HR,
MAX_DRIVING_TIME_IN_24HR,
MAX_DUTY_TIME_IN_24HR,
MAX_CM_DISTANCE,
MAX_CM_TIME,
MAX_CM_DH_DISTANCE,
MAX_SIZE_WIDTH,
MAX_SIZE_HEIGHT,
MAX_SIZE_LENGTH,
MIN_SIZE_WIDTH,
MIN_SIZE_HEIGHT,
MIN_SIZE_LENGTH,
MAX_OUT_OF_ROUTE,
CM_FREE_DH_MILEAGE,
MIN_CM_DISTANCE,
CM_FIRST_LOAD_DISCOUNT,
MIN_CM_TIME,
UNIT_RATE_BASIS,
CM_RATE_VARIANT,
DISTANCE_CALCULATION_METHOD,
ALLOW_CONTINUOUS_MOVE,
MAX_CM_DH_TIME,
ORIGIN_DSTN_SURCHARGE_LEVEL,
UPDATE_MOT_SL
)
VALUES (
l_carrier_service_id,
p_Carrier_Service_Info.Carrier_Id,
p_Carrier_Service_Info.mode_of_transport,
p_Carrier_Service_Info.Enabled_Flag,
p_Carrier_Service_Info.web_enabled,
p_Carrier_Service_Info.service_level,
p_Carrier_Service_Info.min_sl_time,
p_Carrier_Service_Info.max_sl_time,
p_Carrier_Service_Info.sl_time_uom,
p_Carrier_Service_Info.Ship_Method_Code,
p_Carrier_Service_Info.Ship_Method_Meaning,
p_Carrier_Service_Info.Attribute_Category,
p_Carrier_Service_Info.Attribute1,
p_Carrier_Service_Info.Attribute2,
p_Carrier_Service_Info.Attribute3,
p_Carrier_Service_Info.Attribute4,
p_Carrier_Service_Info.Attribute5,
p_Carrier_Service_Info.Attribute6,
p_Carrier_Service_Info.Attribute7,
p_Carrier_Service_Info.Attribute8,
p_Carrier_Service_Info.Attribute9,
p_Carrier_Service_Info.Attribute10,
p_Carrier_Service_Info.Attribute11,
p_Carrier_Service_Info.Attribute12,
p_Carrier_Service_Info.Attribute13,
p_Carrier_Service_Info.Attribute14,
p_Carrier_Service_Info.Attribute15,
p_Carrier_Service_Info.Creation_date,
p_Carrier_Service_Info.Created_By,
p_Carrier_Service_Info.Last_Update_Date,
p_Carrier_Service_Info.Last_Updated_By,
p_Carrier_Service_Info.Last_Update_Login,
-- Pack J Enhancement
p_Carrier_Service_Info.MAX_NUM_STOPS_PERMITTED,
p_Carrier_Service_Info.MAX_TOTAL_DISTANCE,
p_Carrier_Service_Info.MAX_TOTAL_TIME,
p_Carrier_Service_Info.ALLOW_INTERSPERSE_LOAD,
p_Carrier_Service_Info.MAX_LAYOVER_TIME,
p_Carrier_Service_Info.MIN_LAYOVER_TIME,
p_Carrier_Service_Info.MAX_TOTAL_DISTANCE_IN_24HR,
p_Carrier_Service_Info.MAX_DRIVING_TIME_IN_24HR,
p_Carrier_Service_Info.MAX_DUTY_TIME_IN_24HR,
p_Carrier_Service_Info.MAX_CM_DISTANCE,
p_Carrier_Service_Info.MAX_CM_TIME,
p_Carrier_Service_Info.MAX_CM_DH_DISTANCE,
p_Carrier_Service_Info.MAX_SIZE_WIDTH,
p_Carrier_Service_Info.MAX_SIZE_HEIGHT,
p_Carrier_Service_Info.MAX_SIZE_LENGTH,
p_Carrier_Service_Info.MIN_SIZE_WIDTH,
p_Carrier_Service_Info.MIN_SIZE_HEIGHT,
p_Carrier_Service_Info.MIN_SIZE_LENGTH,
p_Carrier_Service_Info.MAX_OUT_OF_ROUTE,
p_Carrier_Service_Info.CM_FREE_DH_MILEAGE,
p_Carrier_Service_Info.MIN_CM_DISTANCE,
p_Carrier_Service_Info.CM_FIRST_LOAD_DISCOUNT,
p_Carrier_Service_Info.MIN_CM_TIME,
p_Carrier_Service_Info.UNIT_RATE_BASIS,
p_Carrier_Service_Info.CM_RATE_VARIANT,
p_Carrier_Service_Info.DISTANCE_CALCULATION_METHOD,
p_Carrier_Service_Info.ALLOW_CONTINUOUS_MOVE,
p_Carrier_Service_Info.MAX_CM_DH_TIME,
p_Carrier_Service_Info.ORIGIN_DSTN_SURCHARGE_LEVEL,
p_Carrier_Service_Info.UPDATE_MOT_SL
);
SELECT
carrier_service_id,
carrier_id,
mode_of_transport,
enabled_flag,
web_enabled,
service_level,
min_sl_time,
max_sl_time,
sl_time_uom,
ship_method_code,
ship_method_meaning,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
-- Pack J Enhancement
MAX_NUM_STOPS_PERMITTED,
MAX_TOTAL_DISTANCE,
MAX_TOTAL_TIME,
ALLOW_INTERSPERSE_LOAD,
MAX_LAYOVER_TIME,
MIN_LAYOVER_TIME,
MAX_TOTAL_DISTANCE_IN_24HR,
MAX_DRIVING_TIME_IN_24HR,
MAX_DUTY_TIME_IN_24HR,
MAX_CM_DISTANCE,
MAX_CM_TIME,
MAX_CM_DH_DISTANCE,
MAX_SIZE_WIDTH,
MAX_SIZE_HEIGHT,
MAX_SIZE_LENGTH,
MIN_SIZE_WIDTH,
MIN_SIZE_HEIGHT,
MIN_SIZE_LENGTH,
MAX_OUT_OF_ROUTE,
CM_FREE_DH_MILEAGE,
MIN_CM_DISTANCE,
CM_FIRST_LOAD_DISCOUNT,
MIN_CM_TIME,
UNIT_RATE_BASIS,
CM_RATE_VARIANT,
DISTANCE_CALCULATION_METHOD,
ALLOW_CONTINUOUS_MOVE,
MAX_CM_DH_TIME,
ORIGIN_DSTN_SURCHARGE_LEVEL,
UPDATE_MOT_SL
FROM wsh_carrier_services
WHERE rowid = p_rowid
FOR UPDATE of Carrier_Service_id NOWAIT;
SELECT LOOKUP_CODE,
TAG,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
TERRITORY_CODE,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
MEANING,
DESCRIPTION
FROM FND_LOOKUP_VALUES
where LOOKUP_TYPE = 'SHIP_METHOD'
and SECURITY_GROUP_ID = 0
and VIEW_APPLICATION_ID = 3
and LOOKUP_CODE = p_Carrier_Service_Info.SHIP_METHOD_CODE;
WSH_DEBUG_SV.log(l_module_name, 'UPDATE_MOT_SL',
p_Carrier_Service_Info.UPDATE_MOT_SL);
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
WSH_DEBUG_SV.log(l_module_name,'FORM_RECORD_DELETED');
AND ((Recinfo.UPDATE_MOT_SL = p_Carrier_Service_Info.UPDATE_MOT_SL)
OR ((Recinfo.UPDATE_MOT_SL IS NULL)
AND (p_Carrier_Service_Info.UPDATE_MOT_SL is NULL)))
THEN
--
IF l_debug_on THEN
WSH_DEBUG_SV.log(l_module_name,'All matched');
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
WSH_DEBUG_SV.log(l_module_name,'FORM_RECORD_DELETED');
PROCEDURE Update_Carrier_Service
(
p_rowid IN VARCHAR2
, p_Carrier_Service_Info IN CSRecType DEFAULT NULL
, p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
, x_Return_Status OUT NOCOPY VARCHAR2
, x_position OUT NOCOPY NUMBER
, x_procedure OUT NOCOPY VARCHAR2
, x_sqlerr OUT NOCOPY VARCHAR2
, x_sql_code OUT NOCOPY VARCHAR2
, x_exception_msg OUT NOCOPY VARCHAR2
)
IS
CURSOR C_lookup_row(p_ship_method_code VARCHAR2) IS
--Bug3330869 SELECT *
SELECT LOOKUP_CODE,
TAG,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
TERRITORY_CODE,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
MEANING,
DESCRIPTION
FROM FND_LOOKUP_VALUES
where LOOKUP_TYPE = 'SHIP_METHOD'
and SECURITY_GROUP_ID = 0
and VIEW_APPLICATION_ID = 3
and LOOKUP_CODE = p_ship_method_code;
SELECT freight_code
FROM WSH_CARRIERS car,WSH_CARRIER_SERVICES carser
WHERE carser.carrier_id = car.carrier_id
and carser.rowid=x_rowid;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_CARRIER_SERVICE';
UPDATE wsh_carrier_services
SET
service_level = p_Carrier_Service_Info.Service_level,
mode_of_transport = p_Carrier_Service_Info.mode_of_transport,
min_sl_time = p_Carrier_Service_Info.min_sl_time,
max_sl_time = p_Carrier_Service_Info.max_sl_time,
sl_time_uom = p_Carrier_Service_Info.sl_time_uom,
enabled_flag = p_Carrier_Service_Info.Enabled_Flag,
web_enabled = p_Carrier_Service_Info.Web_Enabled,
ship_method_code = p_Carrier_Service_Info.Ship_Method_Code,
ship_method_meaning = p_Carrier_Service_Info.Ship_Method_Meaning,
attribute_category = p_Carrier_Service_Info.Attribute_Category,
attribute1 = p_Carrier_Service_Info.Attribute1,
attribute2 = p_Carrier_Service_Info.Attribute2,
attribute3 = p_Carrier_Service_Info.Attribute3,
attribute4 = p_Carrier_Service_Info.Attribute4,
attribute5 = p_Carrier_Service_Info.Attribute5,
attribute6 = p_Carrier_Service_Info.Attribute6,
attribute7 = p_Carrier_Service_Info.Attribute7,
attribute8 = p_Carrier_Service_Info.Attribute8,
attribute9 = p_Carrier_Service_Info.Attribute9,
attribute10 = p_Carrier_Service_Info.Attribute10,
attribute11 = p_Carrier_Service_Info.Attribute11,
attribute12 = p_Carrier_Service_Info.Attribute12,
attribute13 = p_Carrier_Service_Info.Attribute13,
attribute14 = p_Carrier_Service_Info.Attribute14,
attribute15 = p_Carrier_Service_Info.Attribute15,
last_update_date = p_Carrier_Service_Info.Last_Update_Date,
last_updated_by = p_Carrier_Service_Info.Last_Updated_By,
last_update_login = p_Carrier_Service_Info.Last_Update_Login,
-- Pack J Enhancement
MAX_NUM_STOPS_PERMITTED = p_Carrier_Service_Info.MAX_NUM_STOPS_PERMITTED,
MAX_TOTAL_DISTANCE = p_Carrier_Service_Info.MAX_TOTAL_DISTANCE,
MAX_TOTAL_TIME = p_Carrier_Service_Info.MAX_TOTAL_TIME,
ALLOW_INTERSPERSE_LOAD = p_Carrier_Service_Info.ALLOW_INTERSPERSE_LOAD,
MAX_LAYOVER_TIME = p_Carrier_Service_Info.MAX_LAYOVER_TIME,
MIN_LAYOVER_TIME = p_Carrier_Service_Info.MIN_LAYOVER_TIME,
MAX_TOTAL_DISTANCE_IN_24HR = p_Carrier_Service_Info.MAX_TOTAL_DISTANCE_IN_24HR,
MAX_DRIVING_TIME_IN_24HR = p_Carrier_Service_Info.MAX_DRIVING_TIME_IN_24HR,
MAX_DUTY_TIME_IN_24HR = p_Carrier_Service_Info.MAX_DUTY_TIME_IN_24HR,
MAX_CM_DISTANCE = p_Carrier_Service_Info.MAX_CM_DISTANCE,
MAX_CM_TIME = p_Carrier_Service_Info.MAX_CM_TIME,
MAX_CM_DH_DISTANCE = p_Carrier_Service_Info.MAX_CM_DH_DISTANCE,
MAX_SIZE_WIDTH = p_Carrier_Service_Info.MAX_SIZE_WIDTH,
MAX_SIZE_HEIGHT = p_Carrier_Service_Info.MAX_SIZE_HEIGHT,
MAX_SIZE_LENGTH = p_Carrier_Service_Info.MAX_SIZE_LENGTH,
MIN_SIZE_WIDTH = p_Carrier_Service_Info.MIN_SIZE_WIDTH,
MIN_SIZE_HEIGHT = p_Carrier_Service_Info.MIN_SIZE_HEIGHT,
MIN_SIZE_LENGTH = p_Carrier_Service_Info.MIN_SIZE_LENGTH,
MAX_OUT_OF_ROUTE = p_Carrier_Service_Info.MAX_OUT_OF_ROUTE,
CM_FREE_DH_MILEAGE = p_Carrier_Service_Info.CM_FREE_DH_MILEAGE,
MIN_CM_DISTANCE = p_Carrier_Service_Info.MIN_CM_DISTANCE,
CM_FIRST_LOAD_DISCOUNT = p_Carrier_Service_Info.CM_FIRST_LOAD_DISCOUNT,
MIN_CM_TIME = p_Carrier_Service_Info.MIN_CM_TIME,
UNIT_RATE_BASIS = p_Carrier_Service_Info.UNIT_RATE_BASIS,
CM_RATE_VARIANT = p_Carrier_Service_Info.CM_RATE_VARIANT,
DISTANCE_CALCULATION_METHOD = p_Carrier_Service_Info.DISTANCE_CALCULATION_METHOD,
ALLOW_CONTINUOUS_MOVE = p_Carrier_Service_Info.ALLOW_CONTINUOUS_MOVE,
MAX_CM_DH_TIME = p_Carrier_Service_Info.MAX_CM_DH_TIME,
ORIGIN_DSTN_SURCHARGE_LEVEL = p_Carrier_Service_Info.ORIGIN_DSTN_SURCHARGE_LEVEL,
UPDATE_MOT_SL = p_Carrier_Service_Info.UPDATE_MOT_SL
WHERE rowid = p_rowid;
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
WSH_DEBUG_SV.log(l_module_name, 'Cursor opened successfully called from update_carrier_service procedure'); --Bug3330869
FND_LOOKUP_VALUES_PKG.UPDATE_ROW
(
X_LOOKUP_TYPE => 'SHIP_METHOD',
X_SECURITY_GROUP_ID => 0,
X_VIEW_APPLICATION_ID => 3,
X_LOOKUP_CODE => lookupinfo.LOOKUP_CODE,
X_TAG => lookupinfo.TAG,
X_ATTRIBUTE_CATEGORY => lookupinfo.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => lookupinfo.ATTRIBUTE1,
X_ATTRIBUTE2 => lookupinfo.ATTRIBUTE2,
X_ATTRIBUTE3 => lookupinfo.ATTRIBUTE3,
X_ATTRIBUTE4 => lookupinfo.ATTRIBUTE4,
X_ENABLED_FLAG => p_Carrier_Service_Info.ENABLED_FLAG,
X_START_DATE_ACTIVE => lookupinfo.START_DATE_ACTIVE,
X_END_DATE_ACTIVE => lookupinfo.END_DATE_ACTIVE,
X_TERRITORY_CODE => lookupinfo.TERRITORY_CODE,
X_ATTRIBUTE5 => lookupinfo.ATTRIBUTE5,
X_ATTRIBUTE6 => lookupinfo.ATTRIBUTE6,
X_ATTRIBUTE7 => lookupinfo.ATTRIBUTE7,
X_ATTRIBUTE8 => lookupinfo.ATTRIBUTE8,
X_ATTRIBUTE9 => lookupinfo.ATTRIBUTE9,
X_ATTRIBUTE10 => lookupinfo.ATTRIBUTE10,
X_ATTRIBUTE11 => lookupinfo.ATTRIBUTE11,
X_ATTRIBUTE12 => lookupinfo.ATTRIBUTE12,
X_ATTRIBUTE13 => lookupinfo.ATTRIBUTE13,
X_ATTRIBUTE14 => lookupinfo.ATTRIBUTE14,
X_ATTRIBUTE15 => lookupinfo.ATTRIBUTE15,
X_MEANING => p_Carrier_Service_Info.SHIP_METHOD_MEANING,
X_DESCRIPTION => p_Carrier_Service_Info.SHIP_METHOD_MEANING,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
X_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID
);
UPDATE WSH_CARRIER_SHIP_METHODS
SET WEB_ENABLED = p_Carrier_Service_Info.Web_Enabled,
LAST_UPDATE_DATE =sysdate,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
ATTRIBUTE1 = p_Carrier_Service_Info.Attribute1,
ATTRIBUTE2 = p_Carrier_Service_Info.Attribute2,
ATTRIBUTE3 = p_Carrier_Service_Info.Attribute3,
ATTRIBUTE4 = p_Carrier_Service_Info.Attribute4,
ATTRIBUTE5 = p_Carrier_Service_Info.Attribute5,
ATTRIBUTE6 = p_Carrier_Service_Info.Attribute6,
ATTRIBUTE7 = p_Carrier_Service_Info.Attribute7,
ATTRIBUTE8 = p_Carrier_Service_Info.Attribute8,
ATTRIBUTE9 = p_Carrier_Service_Info.Attribute9,
ATTRIBUTE10 = p_Carrier_Service_Info.Attribute10,
ATTRIBUTE11 = p_Carrier_Service_Info.Attribute11,
ATTRIBUTE12 = p_Carrier_Service_Info.Attribute12,
ATTRIBUTE13 = p_Carrier_Service_Info.Attribute13,
ATTRIBUTE14 = p_Carrier_Service_Info.Attribute14,
ATTRIBUTE15 = p_Carrier_Service_Info.Attribute15
WHERE FREIGHT_CODE = l_freight_code
AND SHIP_METHOD_CODE = p_Carrier_Service_Info.Ship_Method_Code;
END Update_Carrier_Service;
UPDATE wsh_carrier_ship_methods
SET enabled_flag = 'N'
WHERE ship_method_code = p_ship_method_code
AND freight_code = p_freight_code;
UPDATE wsh_org_carrier_services
SET enabled_flag = 'N'
WHERE carrier_service_id= p_carrier_service_id;