[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT UPPER(default_severity),
UPPER(exception_handling),
UPPER(initiate_workflow),
enabled
FROM wsh_exception_definitions_vl
WHERE exception_name = c_exception_name;
p_last_update_date IN DATE DEFAULT NULL,
p_last_updated_by IN NUMBER DEFAULT NULL,
p_last_update_login IN NUMBER DEFAULT NULL,
p_program_application_id IN NUMBER DEFAULT NULL,
p_program_id IN NUMBER DEFAULT NULL,
p_program_update_date IN DATE DEFAULT NULL,
p_status IN VARCHAR2 DEFAULT NULL,
p_action IN VARCHAR2 DEFAULT NULL
) IS
-- standard version infermation
l_api_version CONSTANT NUMBER := 1.0;
SELECT lookup_code
FROM fnd_lookup_values_vl
WHERE lookup_type = 'LOGGING_ENTITY' AND
lookup_code = c_logging_entity AND
enabled_flag = 'Y' AND
(sysdate BETWEEN NVL(start_date_active,sysdate) AND
NVL(end_date_active, sysdate));
-- cursor for update
CURSOR C3 (c_exception_id NUMBER) IS
--Changed for BUG#3330869
-- SELECT *
SELECT
exception_id,
message,
exception_name,
status,
severity,
trip_id,
trip_name,
trip_stop_id,
delivery_id,
delivery_name,
delivery_detail_id,
delivery_assignment_id,
container_name,
inventory_item_id,
lot_number,
-- HW OPMCONV - No need for sublot_number
-- sublot_number,
revision,
serial_number,
unit_of_measure,
quantity,
unit_of_measure2,
quantity2,
subinventory,
locator_id,
arrival_date,
departure_date,
error_message,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
request_id,
logged_at_location_id,
logging_entity,
logging_entity_id,
exception_location_id,
manually_logged
FROM wsh_exceptions
WHERE exception_id = c_exception_id FOR UPDATE;
-- insert record
-- validate required parameters
IF p_logged_at_location_id IS NULL OR
p_logging_entity IS NULL OR
p_logging_entity_id IS NULL OR
p_message IS NULL
THEN
l_msg_name := 'WSH_XC_MIS_REQ_PARAM';
-- Bug 6615016 using the wsh_exceptions_s.nextval directly in the insert statement.
-- SELECT wsh_exceptions_s.nextval INTO l_exception_id FROM SYS.DUAL;
INSERT INTO wsh_exceptions(
exception_id,
exception_location_id,
logged_at_location_id,
logging_entity,
logging_entity_id,
exception_name,
message,
severity,
manually_logged,
status,
trip_id,
trip_name,
trip_stop_id,
delivery_id,
delivery_name,
delivery_detail_id,
delivery_assignment_id,
container_name,
inventory_item_id,
lot_number,
revision,
serial_number,
unit_of_measure,
quantity,
subinventory,
locator_id,
arrival_date,
departure_date,
error_message,
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,
-- BUG#:1549665 hwahdani added request_id
request_id ,
-- HW BUG#:1900149
-- HW OPMCONV - No need for sublot_number
-- sublot_number,
unit_of_measure2,
quantity2,
-- HW end of 1900149
batch_id,
program_application_id,
program_id,
program_update_date
)
VALUES (
-- Bug 6615016 using the wsh_exceptions_s.nextval directly in the insert statement.
--l_exception_id,
wsh_exceptions_s.nextval,
p_exception_location_id,
p_logged_at_location_id,
UPPER(p_logging_entity),
p_logging_entity_id,
DECODE(l_exception_name, NULL, NULL, UPPER(l_exception_name)),
p_message,
-- Shipping Exception enhancement
l_severity,
l_manually_logged,
l_status,
p_trip_id,
p_trip_name,
p_trip_stop_id,
p_delivery_id,
p_delivery_name,
p_delivery_detail_id,
p_delivery_assignment_id,
null, --LPN_conv Synch Up..samanna
p_inventory_item_id,
p_lot_number,
p_revision,
p_serial_number,
p_unit_of_measure,
p_quantity,
p_subinventory,
p_locator_id,
p_arrival_date,
p_departure_date,
p_error_message,
p_attribute_category,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_attribute11,
p_attribute12,
p_attribute13,
p_attribute14,
p_attribute15,
NVL(p_creation_date,SYSDATE),
NVL(p_created_by,FND_GLOBAL.USER_ID),
NVL(p_last_update_date,SYSDATE),
NVL(p_last_updated_by,FND_GLOBAL.USER_ID),
NVL(p_last_update_login,FND_GLOBAL.LOGIN_ID),
-- BUG#:1549665 hwahdani added request_id
l_request_id, -- Bugfix 3711661, previously p_request_id was passed.
-- HW BUG#:1900149 added opm columns
-- HW OPMCONV - No need for sublot_number
-- p_sublot_number,
p_unit_of_measure2,
p_quantity2,
-- HW end of 1900149
l_batch_id, -- Bugfix 3711661, previously p_batch_id was passed.
p_program_application_id,
p_program_id,
p_program_update_date
);
ELSE -- update
-- validate x_exception_id
OPEN C3(x_exception_id);
-- update an existing exception,the required fields can't be changed.
IF ( p_logged_at_location_id IS NOT NULL) OR
(p_exception_location_id IS NOT NULL) OR
(p_logging_entity IS NOT NULL) OR
(p_logging_entity_id IS NOT NULL)
THEN
l_msg_name := 'WSH_XC_CHANGE_REQ_FIELD';
-- Only update on a NULL field is allowed.
-- If the current value is already the same with the parameter,
-- the current value is not changed and the procedure won't fail.
IF p_exception_name IS NOT NULL THEN
IF l_xcp_record.exception_name IS NOT NULL and
l_xcp_record.exception_name <> p_exception_name THEN
l_msg_name := 'WSH_XC_EXP_NAME_EXIST';
-- update p_error_message to the current message text if p_error_message is
-- not NULL
IF p_error_message is NOT NULL then
l_xcp_record.error_message := p_error_message;
/*-- Bug No:2363908 ---> Fix : Update is allowed for all the DFF attributes appearing in SHipping Exception Form
Fix is done by commenting all the checks
IF p_attribute_category IS NOT NULL THEN
IF l_xcp_record.attribute_category IS NULL THEN
l_xcp_record.attribute_category := p_attribute_category ;
UPDATE wsh_exceptions
SET
logged_at_location_id = l_xcp_record.logged_at_location_id,
logging_entity = l_xcp_record.logging_entity,
logging_entity_id = l_xcp_record.logging_entity_id,
exception_name = l_xcp_record.exception_name,
exception_location_id = l_xcp_record.exception_location_id,
message = l_xcp_record.message,
severity = l_xcp_record.severity,
manually_logged = l_xcp_record.manually_logged,
status = l_xcp_record.status,
trip_id = l_xcp_record.trip_id,
trip_name = l_xcp_record.trip_name,
trip_stop_id = l_xcp_record.trip_stop_id,
delivery_id = l_xcp_record.delivery_id,
delivery_name = l_xcp_record.delivery_name,
delivery_detail_id = l_xcp_record.delivery_detail_id,
delivery_assignment_id = l_xcp_record.delivery_assignment_id,
container_name =l_xcp_record.container_name,
inventory_item_id = l_xcp_record.inventory_item_id,
lot_number = l_xcp_record.lot_number,
-- HW BUG#:1900149 added sublot
-- HW OPMCONV - No need for sublot_number
-- sublot_number = l_xcp_record.sublot_number,
revision = l_xcp_record.revision,
serial_number = l_xcp_record.serial_number,
unit_of_measure = l_xcp_record.unit_of_measure,
quantity = l_xcp_record.quantity,
-- HW BUG#:1900149 added uom2 and qty2
unit_of_measure2 = l_xcp_record.unit_of_measure2,
quantity2 = l_xcp_record.quantity2,
-- HW end of 1900149
subinventory = l_xcp_record.subinventory,
locator_id = l_xcp_record.locator_id,
arrival_date = l_xcp_record.arrival_date,
departure_date = l_xcp_record.departure_date,
error_message = l_xcp_record.error_message,
attribute_category = p_attribute_category,
attribute1 = p_attribute1,
attribute2 = p_attribute2,
attribute3 = p_attribute3,
attribute4 = p_attribute4,
attribute5 = p_attribute5,
attribute6 = p_attribute6,
attribute7 = p_attribute7,
attribute8 = p_attribute8,
attribute9 = p_attribute9,
attribute10 = p_attribute10,
attribute11 = p_attribute11,
attribute12 = p_attribute12,
attribute13 = p_attribute13,
attribute14 = p_attribute14,
attribute15 = p_attribute15,
last_update_date = NVL(p_last_update_date,SYSDATE),
last_updated_by = NVL(p_last_updated_by,FND_GLOBAL.USER_ID),
last_update_login = NVL(p_last_update_login,FND_GLOBAL.LOGIN_ID),
--BUG#:1549665 hwahdani added request_id
request_id = l_xcp_record.request_id
WHERE CURRENT OF C3;
SELECT UPPER(exception_name),
DECODE( UPPER(status), 'MANUAL','OPEN' ,
'LOGGED','OPEN' ,
'IN_PROCESS','OPEN' ,
'ERROR','OPEN' ,
'NOT_HANDLED','NO_ACTION_REQUIRED' ,
NULL,'NO_ACTION_REQUIRED' ,
UPPER(status)
)
FROM wsh_exceptions
WHERE exception_id = c_exception_id2;
SELECT UPPER(default_severity), UPPER(exception_handling),
UPPER(initiate_workflow)
FROM wsh_exception_definitions_vl
WHERE exception_name = (
SELECT exception_name
FROM wsh_exceptions
WHERE exception_id = c_exception_id);
UPDATE wsh_exceptions
SET status = l_new_status,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE exception_id = p_exception_id
AND DECODE( UPPER(status),
'MANUAL','OPEN' ,
'LOGGED','OPEN' ,
'IN_PROCESS','OPEN' ,
'ERROR','OPEN' ,
'NOT_HANDLED','NO_ACTION_REQUIRED' ,
NULL,'NO_ACTION_REQUIRED' ,
UPPER(status)
) = l_p_old_status ;
procedure insert_xc_def_form (
x_exception_definition_id in out NOCOPY NUMBER,
p_exception_name in VARCHAR2,
p_description in VARCHAR2,
p_exception_type in VARCHAR2,
p_default_severity in VARCHAR2,
p_exception_handling in VARCHAR2,
p_workflow_item_type in VARCHAR2,
p_workflow_process in VARCHAR2,
p_initiate_workflow in VARCHAR2,
p_update_allowed in VARCHAR2,
p_enabled in VARCHAR2,
p_attribute_category in VARCHAR2,
p_attribute1 in VARCHAR2,
p_attribute2 in VARCHAR2,
p_attribute3 in VARCHAR2,
p_attribute4 in VARCHAR2,
p_attribute5 in VARCHAR2,
p_attribute6 in VARCHAR2,
p_attribute7 in VARCHAR2,
p_attribute8 in VARCHAR2,
p_attribute9 in VARCHAR2,
p_attribute10 in VARCHAR2,
p_attribute11 in VARCHAR2,
p_attribute12 in VARCHAR2,
p_attribute13 in VARCHAR2,
p_attribute14 in VARCHAR2,
p_attribute15 in VARCHAR2,
p_creation_date in DATE,
p_created_by in NUMBER,
p_last_update_date in DATE,
p_last_updated_by in NUMBER,
p_last_update_login in NUMBER
) is
cursor C1 (c_exception_name VARCHAR2) is
select exception_name
from wsh_exception_definitions_vl
where exception_name = c_exception_name;
select ROWID from WSH_EXCEPTION_DEFINITIONS_B
where EXCEPTION_DEFINITION_ID =
c_exception_definition_id;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_XC_DEF_FORM';
WSH_DEBUG_SV.log(l_module_name,'P_UPDATE_ALLOWED',P_UPDATE_ALLOWED);
WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATE_DATE',P_LAST_UPDATE_DATE);
WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATED_BY',P_LAST_UPDATED_BY);
WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATE_LOGIN',P_LAST_UPDATE_LOGIN);
SELECT wsh_exception_definitions_s.nextval INTO l_exception_definition_id FROM SYS.DUAL;
insert into wsh_exception_definitions_b (
exception_definition_id,
exception_type,
default_severity,
exception_handling,
workflow_item_type,
workflow_process,
initiate_workflow,
update_allowed,
enabled,
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
) values (
l_exception_definition_id,
UPPER(p_exception_type),
UPPER(p_default_severity),
UPPER(p_exception_handling),
UPPER(p_workflow_item_type),
UPPER(p_workflow_process),
UPPER(p_initiate_workflow),
UPPER(p_update_allowed),
UPPER(p_enabled),
p_attribute_category,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_attribute11,
p_attribute12,
p_attribute13,
p_attribute14,
p_attribute15,
p_creation_date,
p_created_by,
p_last_update_date,
p_last_updated_by,
p_last_update_login
);
insert into WSH_EXCEPTION_DEFINITIONS_TL (
EXCEPTION_DEFINITION_ID,
EXCEPTION_NAME,
DESCRIPTION,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LANGUAGE,
SOURCE_LANG
) select
l_exception_definition_id,
p_exception_name,
p_description,
p_creation_date,
p_created_by,
p_last_update_date,
p_last_updated_by,
p_last_update_login,
L.LANGUAGE_CODE,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from WSH_EXCEPTION_DEFINITIONS_TL T
where T.EXCEPTION_DEFINITION_ID = l_exception_definition_id
and T.LANGUAGE = L.LANGUAGE_CODE);
end insert_xc_def_form;
procedure update_xc_def_form (
p_exception_definition_id in NUMBER,
p_exception_name in VARCHAR2,
p_description in VARCHAR2,
p_exception_type in VARCHAR2,
p_default_severity in VARCHAR2,
p_exception_handling in VARCHAR2,
p_workflow_item_type in VARCHAR2,
p_workflow_process in VARCHAR2,
p_initiate_workflow in VARCHAR2,
p_update_allowed in VARCHAR2,
p_enabled in VARCHAR2,
p_attribute_category in VARCHAR2,
p_attribute1 in VARCHAR2,
p_attribute2 in VARCHAR2,
p_attribute3 in VARCHAR2,
p_attribute4 in VARCHAR2,
p_attribute5 in VARCHAR2,
p_attribute6 in VARCHAR2,
p_attribute7 in VARCHAR2,
p_attribute8 in VARCHAR2,
p_attribute9 in VARCHAR2,
p_attribute10 in VARCHAR2,
p_attribute11 in VARCHAR2,
p_attribute12 in VARCHAR2,
p_attribute13 in VARCHAR2,
p_attribute14 in VARCHAR2,
p_attribute15 in VARCHAR2,
p_creation_date in DATE,
p_created_by in NUMBER,
p_last_update_date in DATE,
p_last_updated_by in NUMBER,
p_last_update_login in NUMBER,
p_caller in VARCHAR2 -- 5986504
) is
l_exception_name VARCHAR2(30);
l_update_allowed VARCHAR2(1);
select update_allowed from WSH_EXCEPTION_DEFINITIONS_B
where EXCEPTION_DEFINITION_ID =
c_exception_definition_id;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_XC_DEF_FORM';
fetch C2 into l_update_allowed;
WSH_DEBUG_SV.log(l_module_name,'P_UPDATE_ALLOWED',P_UPDATE_ALLOWED);
WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATE_DATE',P_LAST_UPDATE_DATE);
WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATED_BY',P_LAST_UPDATED_BY);
WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATE_LOGIN',P_LAST_UPDATE_LOGIN);
WSH_DEBUG_SV.log(l_module_name,'L_UPDATE_ALLOWED -in Db ', L_UPDATE_ALLOWED); --5986504
-- 5986504{ If Caller is Loader then Either p_update_allowed should be N or a Flip (Y -> N or N-Y)
-- should have taken place to be Elligible for Update.
-- If Caller is Form , then Updates are Elligible
if ( nvl(p_caller, 'FORM') = 'LOADER' and ( upper(p_update_allowed) = 'N' or
(upper(p_update_allowed) = 'Y' and l_update_allowed = 'N')) ) OR
( nvl(p_caller, 'FORM') = 'FORM' ) THEN
update wsh_exception_definitions_b
set
exception_type = p_exception_type,
default_severity = UPPER(p_default_severity),
exception_handling = UPPER(p_exception_handling),
workflow_item_type = UPPER(p_workflow_item_type),
workflow_process = UPPER(p_workflow_process),
initiate_workflow = UPPER(p_initiate_workflow),
update_allowed = UPPER(p_update_allowed),
enabled = UPPER(p_enabled),
attribute_category = p_attribute_category,
attribute1 = p_attribute1,
attribute2 = p_attribute2,
attribute3 = p_attribute3,
attribute4 = p_attribute4,
attribute5 = p_attribute5,
attribute6 = p_attribute6,
attribute7 = p_attribute7,
attribute8 = p_attribute8,
attribute9 = p_attribute9,
attribute10 = p_attribute10,
attribute11 = p_attribute11,
attribute12 = p_attribute12,
attribute13 = p_attribute13,
attribute14 = p_attribute14,
attribute15 = p_attribute15,
creation_date = p_creation_date,
created_by = p_created_by,
last_update_date = p_last_update_date,
last_updated_by = p_last_updated_by,
last_update_login = p_last_update_login
where EXCEPTION_DEFINITION_ID = p_exception_definition_id ;
WSH_DEBUG_SV.log(l_module_name,'After Base Table Update ... ', P_CALLER);
update WSH_EXCEPTION_DEFINITIONS_TL set
EXCEPTION_NAME = p_exception_name,
DESCRIPTION = p_description,
LAST_UPDATE_DATE = p_last_update_date,
LAST_UPDATED_BY = p_last_updated_by,
LAST_UPDATE_LOGIN = p_last_update_login,
SOURCE_LANG = userenv('LANG')
where EXCEPTION_DEFINITION_ID = p_exception_definition_id
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
WSH_DEBUG_SV.log(l_module_name,'After TL Table Update ... ', P_CALLER);
WSH_DEBUG_SV.logmsg(l_module_name,'After Updates '||P_EXCEPTION_DEFINITION_ID,WSH_DEBUG_SV.C_EXCEP_LEVEL);
WSH_DEBUG_SV.logmsg(l_module_name,'After Updates '||P_CALLER,WSH_DEBUG_SV.C_EXCEP_LEVEL);
WSH_DEBUG_SV.log(l_module_name,'- Failed in Update .. ', P_CALLER);
end update_xc_def_form;
p_update_allowed in VARCHAR2,
p_enabled in VARCHAR2,
p_attribute_category in VARCHAR2,
p_attribute1 in VARCHAR2,
p_attribute2 in VARCHAR2,
p_attribute3 in VARCHAR2,
p_attribute4 in VARCHAR2,
p_attribute5 in VARCHAR2,
p_attribute6 in VARCHAR2,
p_attribute7 in VARCHAR2,
p_attribute8 in VARCHAR2,
p_attribute9 in VARCHAR2,
p_attribute10 in VARCHAR2,
p_attribute11 in VARCHAR2,
p_attribute12 in VARCHAR2,
p_attribute13 in VARCHAR2,
p_attribute14 in VARCHAR2,
p_attribute15 in VARCHAR2,
p_creation_date in DATE,
p_created_by in NUMBER,
p_last_update_date in DATE,
p_last_updated_by in NUMBER,
p_last_update_login in NUMBER
) is
cursor C1 (c_exception_name VARCHAR2) is
select exception_definition_id from WSH_EXCEPTION_DEFINITIONS_TL
where EXCEPTION_NAME = c_exception_name
and SOURCE_LANG = userenv('LANG');
WSH_DEBUG_SV.log(l_module_name,'P_UPDATE_ALLOWED',P_UPDATE_ALLOWED);
WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATE_DATE',P_LAST_UPDATE_DATE);
WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATED_BY',P_LAST_UPDATED_BY);
WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATE_LOGIN',P_LAST_UPDATE_LOGIN);
insert_xc_def_form(
x_exception_definition_id=>l_exception_definition_id,
p_exception_name=> p_exception_name,
p_description=> p_description,
p_exception_type=>p_exception_type,
p_default_severity=>p_default_severity,
p_exception_handling=>p_exception_handling,
p_workflow_item_type=>p_workflow_item_type,
p_workflow_process=>p_workflow_process,
p_initiate_workflow=>p_initiate_workflow,
p_update_allowed=>p_update_allowed,
p_enabled=>p_enabled,
p_attribute_category=>p_attribute_category,
p_attribute1=>p_attribute1,
p_attribute2=>p_attribute2,
p_attribute3=>p_attribute3,
p_attribute4=>p_attribute4,
p_attribute5=>p_attribute5,
p_attribute6=>p_attribute6,
p_attribute7=>p_attribute7,
p_attribute8=>p_attribute8,
p_attribute9=>p_attribute9,
p_attribute10=>p_attribute10,
p_attribute11=>p_attribute11,
p_attribute12=>p_attribute12,
p_attribute13=>p_attribute13,
p_attribute14=>p_attribute14,
p_attribute15=>p_attribute15,
p_creation_date=> p_creation_date,
p_created_by=> p_created_by,
p_last_update_date=>p_last_update_date,
p_last_updated_by=>p_last_updated_by,
p_last_update_login=>p_last_update_login
);
/* update all columns except exception_definition_id */
-- raise WSH_XC_DEF_DUP;
update_xc_def_form(
p_exception_definition_id=> l_exception_definition_id,
p_exception_name=> p_exception_name,
p_description=> p_description,
p_exception_type=>p_exception_type,
p_default_severity=>p_default_severity,
p_exception_handling=>p_exception_handling,
p_workflow_item_type=>p_workflow_item_type,
p_workflow_process=>p_workflow_process,
p_initiate_workflow=>p_initiate_workflow,
p_update_allowed=>p_update_allowed,
p_enabled=>p_enabled,
p_attribute_category=>p_attribute_category,
p_attribute1=>p_attribute1,
p_attribute2=>p_attribute2,
p_attribute3=>p_attribute3,
p_attribute4=>p_attribute4,
p_attribute5=>p_attribute5,
p_attribute6=>p_attribute6,
p_attribute7=>p_attribute7,
p_attribute8=>p_attribute8,
p_attribute9=>p_attribute9,
p_attribute10=>p_attribute10,
p_attribute11=>p_attribute11,
p_attribute12=>p_attribute12,
p_attribute13=>p_attribute13,
p_attribute14=>p_attribute14,
p_attribute15=>p_attribute15,
p_creation_date=>p_creation_date,
p_created_by=>p_created_by,
p_last_update_date=>p_last_update_date,
p_last_updated_by=>p_last_updated_by,
p_last_update_login=>p_last_update_login,
p_caller=>l_caller
);
update WSH_EXCEPTION_DEFINITIONS_TL
set
EXCEPTION_NAME = p_exception_name,
DESCRIPTION = p_description,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = decode(p_owner,'SEED',1,0),
LAST_UPDATE_LOGIN = 0,
SOURCE_LANG = userenv('LANG')
where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
and EXCEPTION_DEFINITION_ID = p_exception_definition_id;
procedure delete_xc_def_form (
p_exception_definition_id IN NUMBER)
is
WSH_XC_DEF_NOT_FOUND exception;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_XC_DEF_FORM';
delete from WSH_EXCEPTION_DEFINITIONS_TL
where EXCEPTION_DEFINITION_ID = p_exception_definition_id ;
delete from WSH_EXCEPTION_DEFINITIONS_B
where EXCEPTION_DEFINITION_ID = p_exception_definition_id;
end delete_xc_def_form ;
delete from WSH_EXCEPTION_DEFINITIONS_TL T
where not exists
(select to_char(NULL)
from WSH_EXCEPTION_DEFINITIONS_B B
where B.EXCEPTION_DEFINITION_ID = T.EXCEPTION_DEFINITION_ID
);
update WSH_EXCEPTION_DEFINITIONS_TL T set (
EXCEPTION_NAME,
DESCRIPTION
) = (select
B.EXCEPTION_NAME,
B.DESCRIPTION
from WSH_EXCEPTION_DEFINITIONS_TL B
where B.EXCEPTION_DEFINITION_ID = T.EXCEPTION_DEFINITION_ID
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.EXCEPTION_DEFINITION_ID,
T.LANGUAGE
) in (select
SUBT.EXCEPTION_DEFINITION_ID,
SUBT.LANGUAGE
from WSH_EXCEPTION_DEFINITIONS_TL SUBB, WSH_EXCEPTION_DEFINITIONS_TL SUBT
where SUBB.EXCEPTION_DEFINITION_ID = SUBT.EXCEPTION_DEFINITION_ID
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.EXCEPTION_NAME <> SUBT.EXCEPTION_NAME
or (SUBB.EXCEPTION_NAME is null and SUBT.EXCEPTION_NAME is not null)
or (SUBB.EXCEPTION_NAME is not null and SUBT.EXCEPTION_NAME is null)
or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
));
insert into WSH_EXCEPTION_DEFINITIONS_TL (
EXCEPTION_DEFINITION_ID,
EXCEPTION_NAME,
DESCRIPTION,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LANGUAGE,
SOURCE_LANG
) select
B.EXCEPTION_DEFINITION_ID,
B.EXCEPTION_NAME,
B.DESCRIPTION,
B.CREATION_DATE,
B.CREATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_LOGIN,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from WSH_EXCEPTION_DEFINITIONS_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select to_char(NULL)
from WSH_EXCEPTION_DEFINITIONS_TL T
where T.EXCEPTION_DEFINITION_ID = B.EXCEPTION_DEFINITION_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
select meaning from fnd_lookup_values_vl
where lookup_code = c_lookup_code and
lookup_type = c_lookup_type;
select we.rowid
from wsh_exceptions we
where
nvl(we.request_id,-999) = nvl(p_request_id , nvl(we.request_id,-999))
and nvl(we.exception_name,'XXX') = nvl(p_exception_name, nvl(we.exception_name,'XXX'))
and we.logging_entity = nvl(p_logging_entity, we.logging_entity )
and we.logged_at_location_id = nvl(p_logged_at_location_id , we.logged_at_location_id )
and we.exception_location_id = nvl(p_exception_location_id , we.exception_location_id )
and we.severity = nvl(p_severity, we.severity)
-- If Action=PURGE , Choose Recs. with Status=CLOSED
-- If Action=CLOSED , if p_status is NULL, then Choose Recs. with Status<>CLOSED
-- If Action=CLOSED , if p_status is NOT NULL, then Choose Recs. with p_status<>CLOSED and p_status = Status
and ( ( p_action = 'PURGE' AND we.status = 'CLOSED' ) OR
( p_action = 'CLOSED' AND ( ( p_status IS NULL and we.status <> 'CLOSED' ) OR
( p_status IS NOT NULL and p_status <> 'CLOSED' and we.status = p_status )
)
)
)
and to_char(we.creation_date,'YYYY/MM/DD') BETWEEN
nvl(TO_CHAR(p_creation_date_from,'YYYY/MM/DD') , to_char(we.creation_date,'YYYY/MM/DD') )
and nvl(TO_CHAR(p_creation_date_to,'YYYY/MM/DD') , to_char(we.creation_date,'YYYY/MM/DD') )
and to_char(nvl(we.arrival_date,sysdate),'YYYY/MM/DD') BETWEEN
nvl(TO_CHAR(nvl(p_arrival_date_from,nvl(we.arrival_date,sysdate)),'YYYY/MM/DD') ,
to_char(nvl(we.arrival_date,sysdate),'YYYY/MM/DD') )
and nvl(TO_CHAR(nvl(p_arrival_date_to,nvl(we.arrival_date,sysdate)),'YYYY/MM/DD') ,
to_char(nvl(we.arrival_date,sysdate),'YYYY/MM/DD') )
and to_char(nvl(we.departure_date,sysdate),'YYYY/MM/DD') BETWEEN
nvl(TO_CHAR(nvl(p_departure_date_from,nvl(we.departure_date,sysdate)),'YYYY/MM/DD') ,
to_char(nvl(we.departure_date,sysdate),'YYYY/MM/DD') )
and nvl(TO_CHAR(nvl(p_departure_date_to,nvl(we.departure_date,sysdate)),'YYYY/MM/DD') ,
to_char(nvl(we.departure_date,sysdate),'YYYY/MM/DD') )
and (sysdate - we.creation_date) > nvl(p_data_older_no_of_days,(sysdate - we.creation_date - 1))
and ( (we.delivery_detail_id in (select distinct a.delivery_detail_id
from wsh_delivery_assignments_v a, wsh_exceptions b
where a.delivery_detail_id=b.delivery_detail_id
and a.delivery_id=p_delivery_id)
AND p_delivery_id is not null
AND p_delivery_contents = 'Y')
/*OR (we.container_name in (select distinct a.container_name --LPN Synch Up..samanna
from wsh_delivery_details a, wsh_delivery_assignments_v b
where a.delivery_detail_id = b.delivery_detail_id
and a.container_flag = 'Y'
and b.delivery_id=p_delivery_id)
AND p_delivery_id is not null
AND p_delivery_contents = 'Y')
OR nvl(we.delivery_id,-999) = nvl(p_delivery_id, nvl(we.delivery_id,-999))
)
and nvl(we.trip_id,-999) = nvl(p_trip_id, nvl(we.trip_id,-999))
and nvl(we.trip_stop_id,-999) = nvl(p_trip_stop_id, nvl(we.trip_stop_id,-999))
and nvl(we.delivery_detail_id,-999) = nvl(p_delivery_detail_id, nvl(we.delivery_detail_id,-999))
;*/
UPDATE WSH_EXCEPTIONS
SET status= 'CLOSED',
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.USER_ID
WHERE trip_id = p_trip_id
AND status <> 'CLOSED'
AND severity <> 'INFO';
UPDATE WSH_EXCEPTIONS
SET status= 'CLOSED',
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.USER_ID
WHERE trip_id = p_trip_id
AND status <> 'CLOSED';
UPDATE WSH_EXCEPTIONS
SET status='CLOSED',
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.USER_ID
WHERE trip_stop_id = p_trip_stop_id
AND status <> 'CLOSED'
AND severity <> 'INFO';
UPDATE WSH_EXCEPTIONS
SET status='CLOSED',
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.USER_ID
WHERE trip_stop_id = p_trip_stop_id
AND status <> 'CLOSED';
UPDATE WSH_EXCEPTIONS
SET status = C_STATUS_CLOSED,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.USER_ID
WHERE delivery_detail_id IN
(SELECT wda.delivery_detail_id
FROM wsh_delivery_assignments wda
WHERE wda.delivery_id = p_delivery_id)
AND status <> C_STATUS_CLOSED
AND severity <> C_SEVERITY_INFO;
UPDATE WSH_EXCEPTIONS
SET status = C_STATUS_CLOSED,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.USER_ID
WHERE delivery_id = p_delivery_id
AND status <> C_STATUS_CLOSED
AND severity <> C_SEVERITY_INFO;
UPDATE WSH_EXCEPTIONS
SET status = p_action,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.USER_ID
WHERE delivery_detail_id IN
(SELECT wda.delivery_detail_id
FROM wsh_delivery_assignments wda
WHERE wda.delivery_id = p_delivery_id)
AND status <> C_STATUS_CLOSED;
UPDATE WSH_EXCEPTIONS
SET status = p_action,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.USER_ID
WHERE delivery_id = p_delivery_id
AND status <> C_STATUS_CLOSED;
UPDATE WSH_EXCEPTIONS
SET status='CLOSED',
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.USER_ID
WHERE delivery_id = p_delivery_id
AND status <> 'CLOSED'
AND severity <> 'INFO'
--AND exception_name <> 'WSH_OTM_SHIPMENT_ERROR'; -- OTM R12 glog project;
UPDATE WSH_EXCEPTIONS
SET status='CLOSED',
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.USER_ID
WHERE delivery_id = p_delivery_id
AND status <> 'CLOSED'
--AND exception_name <> 'WSH_OTM_SHIPMENT_ERROR'; -- OTM R12 glog project;
UPDATE WSH_EXCEPTIONS
SET status=p_action ,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.USER_ID
WHERE delivery_detail_id = p_delivery_detail_id
AND status <> 'CLOSED';
Exceptions_SQL := Exceptions_SQL || 'SELECT WE.ROWID';
Temp_SQL := ' SELECT ROWID';
Exceptions_SQL := Exceptions_SQL || ' (SELECT DISTINCT a.delivery_detail_id';
Exceptions_SQL := Exceptions_SQL || ' (SELECT DISTINCT a.container_name';
DELETE from WSH_EXCEPTIONS where rowid = RowIdList(l_counter);
UPDATE WSH_EXCEPTIONS
SET status=p_action ,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.USER_ID
WHERE rowid = RowIdList(l_counter);
SELECT DECODE(severity,'HIGH','ERROR','MEDIUM','WARNING',severity)
FROM wsh_exceptions
WHERE trip_id = v_trip_id
AND status in ('OPEN','ERROR','LOGGED','IN_PROCESS','MANUAL')
AND severity in ('HIGH','MEDIUM','ERROR','WARNING')
ORDER BY decode(severity,'HIGH',1,'ERROR',1,'MEDIUM',2,'WARNING',2,3);
SELECT DECODE(severity,'HIGH','ERROR','MEDIUM','WARNING',severity)
FROM wsh_exceptions
WHERE trip_stop_id = v_stop_id
AND status in ('OPEN','ERROR','LOGGED','IN_PROCESS','MANUAL')
AND severity in ('HIGH','MEDIUM','ERROR','WARNING')
ORDER BY decode(severity,'HIGH',1,'ERROR',1,'MEDIUM',2,'WARNING',2,3);
SELECT DECODE(severity,'HIGH','ERROR','MEDIUM','WARNING',severity)
FROM wsh_exceptions
WHERE delivery_id = v_delivery_id
AND status in ('OPEN','ERROR','LOGGED','IN_PROCESS','MANUAL')
AND severity in ('HIGH','MEDIUM','ERROR','WARNING')
ORDER BY decode(severity,'HIGH',1,'ERROR',1,'MEDIUM',2,'WARNING',2,3);
SELECT DECODE(severity,'HIGH','ERROR','MEDIUM','WARNING',severity)
FROM wsh_exceptions
WHERE delivery_detail_id = v_detail_id
AND status in ('OPEN','ERROR','LOGGED','IN_PROCESS','MANUAL')
AND severity in ('HIGH','MEDIUM','ERROR','WARNING')
ORDER BY decode(severity,'HIGH',1,'ERROR',1,'MEDIUM',2,'WARNING',2,3);
SELECT wdd.delivery_detail_id, wdd.container_flag
FROM wsh_delivery_assignments_v wda, wsh_delivery_details wdd
WHERE wda.delivery_id = v_delivery_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
ORDER BY wdd.container_flag;
SELECT dg.delivery_id
FROM wsh_new_deliveries dl, wsh_delivery_legs dg, wsh_trip_stops st
WHERE dg.delivery_id = dl.delivery_id
AND st.stop_location_id = dl.initial_pickup_location_id
AND st.stop_id = dg.pick_up_stop_id
AND st.stop_id = v_stop_id
UNION ALL
-- drop off deliveries
SELECT dg.delivery_id
FROM wsh_new_deliveries dl, wsh_delivery_legs dg, wsh_trip_stops st
WHERE dg.delivery_id = dl.delivery_id
AND st.stop_location_id = dl.ultimate_dropoff_location_id
AND st.stop_id = dg.drop_off_stop_id
AND st.stop_id = v_stop_id;
SELECT stop_id
FROM wsh_trip_stops
WHERE trip_id = v_trip_id
ORDER BY stop_sequence_number ASC ;
SELECT delivery_detail_id
FROM wsh_delivery_assignments_v
START WITH parent_delivery_detail_id = v_container_id
CONNECT BY PRIOR delivery_detail_id = parent_delivery_detail_id;
SELECT container_flag
FROM wsh_delivery_details
WHERE delivery_detail_id = v_delivery_detail_id;
SELECT stop_id
FROM wsh_trip_stops wts
WHERE wts.physical_stop_id IS NOT NULL
AND wts.physical_location_id IS NOT NULL
AND wts.physical_stop_id=v_stop_id
AND wts.trip_id =(SELECT trip_id
FROM wsh_trip_stops wts1
WHERE wts1.stop_id=v_stop_id);
SELECT wdd.delivery_detail_id, wdd.container_flag
FROM wsh_delivery_assignments_v wda, wsh_delivery_details wdd
WHERE wda.delivery_id = v_delivery_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
ORDER BY wdd.container_flag;
SELECT dg.delivery_id
FROM wsh_new_deliveries dl, wsh_delivery_legs dg, wsh_trip_stops st
WHERE dg.delivery_id = dl.delivery_id
AND st.stop_location_id = dl.initial_pickup_location_id
AND st.stop_id = dg.pick_up_stop_id
AND st.stop_id = v_stop_id
UNION ALL
-- drop off deliveries
SELECT dg.delivery_id
FROM wsh_new_deliveries dl, wsh_delivery_legs dg, wsh_trip_stops st
WHERE dg.delivery_id = dl.delivery_id
AND st.stop_location_id = dl.ultimate_dropoff_location_id
AND st.stop_id = dg.drop_off_stop_id
AND st.stop_id = v_stop_id;
SELECT stop_id
FROM wsh_trip_stops
WHERE trip_id = v_trip_id
ORDER BY stop_sequence_number ASC ;
SELECT stop_id
FROM wsh_trip_stops wts
WHERE wts.physical_stop_id IS NOT NULL
AND wts.physical_location_id IS NOT NULL
AND wts.physical_stop_id=v_stop_id
AND wts.trip_id =(SELECT trip_id
FROM wsh_trip_stops wts1
WHERE wts1.stop_id=v_stop_id);
IF (p_delivery_info_tab(i).tms_interface_flag IN (WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED,
WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED)) THEN
l_new_interface_flag := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;
WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS))) THEN
l_exception_id := NULL;
ELSIF p_delivery_info_tab(i).tms_interface_flag IN (WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED,
WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS) THEN
l_exceptions_to_close_tab(1) := 'WSH_OTM_DEL_UPDATE_REQ';
ELSIF p_delivery_info_tab(i).tms_interface_flag IN (WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS) THEN
l_exceptions_to_close_tab(1) := 'WSH_OTM_DEL_DELETE_REQ';
AND (p_delivery_info_tab(i).tms_interface_flag = WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS)) THEN
--log deleted, only when set from DP to NS or CR or CMP
l_exception_name := 'WSH_OTM_DEL_DELETED';
ELSIF (l_new_interface_flag = WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED) THEN
l_exception_name := 'WSH_OTM_DEL_UPDATE_REQ';
ELSIF (l_new_interface_flag = WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED) THEN
l_exception_name := 'WSH_OTM_DEL_DELETE_REQ';
SELECT severity,
DECODE(severity,'HIGH', 1, 'ERROR', 1, 'MEDIUM', 2, 'WARNING', 2, 3) rank,
exception_name
FROM wsh_exceptions
WHERE delivery_id = p_delivery_id
AND status IN ('OPEN','ERROR','LOGGED','IN_PROCESS','MANUAL')
AND severity IN ('HIGH','MEDIUM','ERROR','WARNING')
AND exception_name IN
('WSH_OTM_DEL_CREATE_REQ','WSH_OTM_DEL_UPDATE_REQ',
'WSH_OTM_DEL_AWAIT_TRIP')
ORDER BY rank ASC;