[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT wip_eam_work_requests_s.nextval
INTO l_work_request_id FROM DUAL;
select NVL(work_request_auto_approve,'N')
into l_auto_approve_flag
from wip_eam_parameters
where organization_id = p_org_id;
SELECT instance_id
INTO l_maintenance_object_id
FROM csi_item_instances
WHERE
inventory_item_id = p_asset_group_id AND
serial_number = p_asset_number;
-- select owning dept from asset
SELECT eomd.owning_department_id
INTO l_owning_dept_id
FROM eam_org_maint_defaults eomd
WHERE eomd.object_type(+) = 50
AND eomd.object_id = l_maintenance_object_id
and organization_id = p_org_id;
SELECT default_department_id
INTO l_owning_dept_id
FROM wip_eam_parameters
WHERE organization_id = p_org_id;
SELECT NVL(UPPER(wep.work_request_auto_approve),'N'), NVL(UPPER(work_request_asset_num_reqd),'Y')
INTO l_work_request_auto_approve, l_asset_num_reqd
FROM WIP_EAM_PARAMETERS wep
WHERE wep.organization_id = p_org_id;
INSERT INTO wip_eam_work_requests(
work_request_id,
work_request_number,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
asset_number,
asset_group,
organization_id,
work_request_status_id,
work_request_priority_id,
work_request_owning_dept,
wip_entity_id,
eam_linear_location_id,
expected_resolution_date,
description,
work_request_type_id,
work_request_auto_approve,
work_request_created_by,
maintenance_object_type,
maintenance_object_id,
created_for,
phone_number,
e_mail,
contact_preference,
notify_originator,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
)
VALUES(
l_work_request_id,
to_char(l_work_request_id),
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
NVL(p_user_id,FND_GLOBAL.login_id),
p_asset_number,
p_asset_group_id,
p_org_id,
l_status_id,
p_priority_id,
l_owning_dept_id,
null,
p_eam_linear_id,
p_request_by_date,
substrb(l_request_log, 1, 240), --changed for the bug 9088315
p_work_request_type_id,
l_auto_approve_flag,
NVL(p_user_id,FND_GLOBAL.user_id),
l_maintenance_object_type,
l_maintenance_object_id,
nvl(p_created_for,FND_GLOBAL.user_id),
p_phone_number,
p_email,
p_contact_preference,
p_notify_originator,
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
);
SELECT user_name
INTO l_user_name
FROM fnd_user
WHERE user_id = p_user_id;
SELECT '*** '||l_user_name||' ('
||to_char(SYSDATE,'YYYY-MM-DD HH24:MI:SS')||') *** '
INTO l_standard_log
FROM DUAL;
SELECT wip_eam_work_req_notes_s.nextval
INTO l_work_request_note_id
FROM dual;
INSERT INTO wip_eam_work_req_notes(
work_request_note_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
work_request_id,
notes,
work_request_note_type,
notification_id
)
VALUES(
l_work_request_note_id,
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
NVL(p_user_id,FND_GLOBAL.login_id),
l_work_request_id,
l_standard_log,
1, -- 1 for request log, 2 for approver log
null
);
SELECT wip_eam_work_req_notes_s.nextval
INTO l_work_request_note_id FROM DUAL;
INSERT INTO wip_eam_work_req_notes(
work_request_note_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
work_request_id,
notes,
work_request_note_type,
notification_id
)
VALUES(
l_work_request_note_id,
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
NVL(p_user_id,FND_GLOBAL.login_id),
l_work_request_id,
l_request_log,
1, -- 1 for request log, 2 for approver log
null
);
EAM_ASSET_LOG_PVT.INSERT_ROW(
p_event_date => sysdate,
p_event_type => 'EAM_SYSTEM_EVENTS',
p_event_id => 4,
p_organization_id => p_org_id,
p_instance_id => l_maintenance_object_id,
p_comments => l_request_log,
p_reference => l_work_request_id,
p_ref_id => l_work_request_id,
p_instance_number => null,
p_employee_id => nvl(p_created_for,FND_GLOBAL.user_id),
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
PROCEDURE update_work_request (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_org_id IN NUMBER,
p_asset_group_id IN NUMBER,
p_asset_number IN VARCHAR2,
p_request_id IN NUMBER,
p_status_id IN NUMBER,
p_priority_id IN NUMBER,
p_request_by_date IN DATE,
p_request_log IN VARCHAR2,
p_work_request_type_id IN NUMBER,
p_eam_linear_id IN NUMBER DEFAULT NULL,
p_owning_dept_id IN NUMBER,
p_user_id IN NUMBER,
p_created_for IN NUMBER,
p_phone_number IN VARCHAR2,
p_email IN VARCHAR2,
p_contact_preference IN NUMBER,
p_notify_originator IN NUMBER,
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_from_public_api IN VARCHAR2 DEFAULT 'N',
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) is
l_api_name CONSTANT VARCHAR2(30) := 'update_work_request';
SAVEPOINT update_work_request_pvt;
SELECT asset_number,
asset_group,
maintenance_object_id,
work_request_priority_id,
work_request_owning_dept,
expected_resolution_date,
work_request_status_id,
work_request_type_id,
eam_linear_location_id,
created_for,
phone_number,
e_mail,
contact_preference,
notify_originator,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
INTO l_old_asset_number,
l_old_asset_group_id,
l_old_maintenance_object_id,
l_old_priority_id,
l_old_owning_dept_id,
l_old_request_by_date,
l_old_status_id,
l_old_work_request_type_id,
l_old_eam_linear_id,
l_old_created_for,
l_old_phone_number,
l_old_email,
l_old_contact_preference,
l_old_notify_originator,
l_old_attribute_category,
l_old_attribute1,
l_old_attribute2,
l_old_attribute3,
l_old_attribute4,
l_old_attribute5,
l_old_attribute6,
l_old_attribute7,
l_old_attribute8,
l_old_attribute9,
l_old_attribute10,
l_old_attribute11,
l_old_attribute12,
l_old_attribute13,
l_old_attribute14,
l_old_attribute15
FROM wip_eam_work_requests
WHERE work_request_id = p_request_id;
SELECT NVL(UPPER(work_request_asset_num_reqd),'Y')
INTO l_asset_num_reqd
FROM WIP_EAM_PARAMETERS wep
WHERE wep.organization_id = p_org_id;
SELECT user_name
INTO l_user_name
FROM fnd_user
WHERE user_id = p_user_id;
SELECT '*** '||l_user_name||' ('
||to_char(SYSDATE,'YYYY-MM-DD HH24:MI:SS')||') *** '
INTO l_standard_log
FROM DUAL;
SELECT wip_eam_work_req_notes_s.nextval
INTO l_work_request_note_id
FROM dual;
INSERT INTO wip_eam_work_req_notes(
work_request_note_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
work_request_id,
notes,
work_request_note_type,
notification_id
)
values(
l_work_request_note_id,
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
NVL(p_user_id,FND_GLOBAL.login_id),
p_request_id,
l_standard_log,
1, -- 1 for request log, 2 for approver log
null -- Don't know what will be put here for notification_id
);
SELECT instance_id
INTO l_new_maintenance_object_id
FROM csi_item_instances
WHERE
inventory_item_id = l_new_asset_group_id AND
serial_number = l_new_asset_number;
UPDATE wip_eam_work_requests SET
last_update_date = sysdate,
last_updated_by = NVL(p_user_id,FND_GLOBAL.user_id),
last_update_login = NVL(p_user_id,FND_GLOBAL.login_id),
work_request_priority_id = l_new_priority_id,
work_request_owning_dept = l_new_owning_dept_id,
work_request_status_id = l_new_status_id,
expected_resolution_date = l_new_request_by_date,
work_request_type_id = l_new_work_request_type_id,
eam_linear_location_id = l_new_eam_linear_id,
asset_number = l_new_asset_number,
asset_group = l_new_asset_group_id,
maintenance_object_id = l_new_maintenance_object_id,
maintenance_object_type = l_maintenance_object_type,
created_for = l_new_created_for,
phone_number = l_new_phone_number,
e_mail = l_new_email,
contact_preference = l_new_contact_preference,
notify_originator = l_new_notify_originator,
ATTRIBUTE_CATEGORY = l_new_attribute_category,
ATTRIBUTE1 = l_new_attribute1,
ATTRIBUTE2 = l_new_attribute2,
ATTRIBUTE3 = l_new_attribute3,
ATTRIBUTE4 = l_new_attribute4,
ATTRIBUTE5 = l_new_attribute5,
ATTRIBUTE6 = l_new_attribute6,
ATTRIBUTE7 = l_new_attribute7,
ATTRIBUTE8 = l_new_attribute8,
ATTRIBUTE9 = l_new_attribute9,
ATTRIBUTE10 = l_new_attribute10,
ATTRIBUTE11 = l_new_attribute11,
ATTRIBUTE12 = l_new_attribute12,
ATTRIBUTE13 = l_new_attribute13,
ATTRIBUTE14 = l_new_attribute14,
ATTRIBUTE15 = l_new_attribute15
WHERE work_request_id=p_request_id;
SELECT NVL(UPPER(wep.work_req_extended_log_flag), 'N'), NVL(UPPER(wep.work_request_auto_approve),'N')
INTO l_extended_log_flag, l_work_request_auto_approve
FROM WIP_EAM_PARAMETERS wep
WHERE wep.organization_id = p_org_id;
SELECT wip_eam_work_req_notes_s.nextval
INTO l_work_request_note_id
FROM dual;
SELECT concatenated_segments INTO l_old_data
FROM mtl_system_items_b_kfv
WHERE organization_id = p_org_id
AND inventory_item_id = l_old_asset_group_id;
SELECT concatenated_segments INTO l_new_data
FROM mtl_system_items_b_kfv
WHERE organization_id = p_org_id
AND inventory_item_id = l_new_asset_group_id;
INSERT INTO wip_eam_work_req_notes(
work_request_note_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
work_request_id,
notes,
work_request_note_type,
notification_id
)
VALUES(
l_work_request_note_id,
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
NVL(p_user_id,FND_GLOBAL.login_id),
p_request_id,
'@@@ '||FND_MESSAGE.GET||' : '||l_old_data||' -> '||l_new_data,
1, -- 1 for request log, 2 for approver log
null
);
SELECT wip_eam_work_req_notes_s.nextval
INTO l_work_request_note_id
FROM dual;
INSERT INTO wip_eam_work_req_notes(
work_request_note_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
work_request_id,
notes,
work_request_note_type,
notification_id
)
VALUES(
l_work_request_note_id,
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
NVL(p_user_id,FND_GLOBAL.login_id),
p_request_id,
'@@@ '||FND_MESSAGE.GET||' : '||nvl(l_old_asset_number,l_null)||' -> '||nvl(l_new_asset_number,l_null),
1, -- 1 for request log, 2 for approver log
null
);
SELECT wip_eam_work_req_notes_s.nextval
INTO l_work_request_note_id
FROM dual;
SELECT meaning
INTO l_old_data
FROM MFG_LOOKUPS
WHERE lookup_type = 'WIP_EAM_ACTIVITY_PRIORITY'
AND lookup_code = l_old_priority_id;
SELECT meaning
INTO l_new_data
FROM MFG_LOOKUPS
WHERE lookup_type = 'WIP_EAM_ACTIVITY_PRIORITY'
AND lookup_code = l_new_priority_id;
INSERT INTO wip_eam_work_req_notes(
work_request_note_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
work_request_id,
notes,
work_request_note_type,
notification_id
)
VALUES(
l_work_request_note_id,
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
NVL(p_user_id,FND_GLOBAL.login_id),
p_request_id,
'@@@ '||FND_MESSAGE.GET||' : '||l_old_data||' -> '||l_new_data,
1, -- 1 for request log, 2 for approver log
null
);
SELECT wip_eam_work_req_notes_s.nextval
INTO l_work_request_note_id
FROM DUAL;
SELECT meaning
INTO l_old_data
FROM MFG_LOOKUPS
WHERE lookup_type = 'WIP_EAM_WORK_REQ_TYPE'
AND lookup_code = NVL(l_old_work_request_type_id,0);
SELECT meaning
INTO l_new_data
FROM MFG_LOOKUPS
WHERE lookup_type = 'WIP_EAM_WORK_REQ_TYPE'
AND lookup_code = l_new_work_request_type_id;
INSERT INTO wip_eam_work_req_notes(
work_request_note_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
work_request_id,
notes,
work_request_note_type,
notification_id
)
VALUES(
l_work_request_note_id,
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
NVL(p_user_id,FND_GLOBAL.login_id),
p_request_id,
'@@@ '||FND_MESSAGE.GET||' : '||l_old_data||' -> '||l_new_data,
1, -- 1 for request log, 2 for approver log
null
);
SELECT wip_eam_work_req_notes_s.nextval
INTO l_work_request_note_id
FROM DUAL;
SELECT meaning
INTO l_old_data
FROM MFG_LOOKUPS
WHERE lookup_type = 'WIP_EAM_WORK_REQ_STATUS'
AND lookup_code = l_old_status_id;
SELECT meaning
INTO l_new_data
FROM MFG_LOOKUPS
WHERE lookup_type = 'WIP_EAM_WORK_REQ_STATUS'
AND lookup_code = l_new_status_id;
INSERT INTO wip_eam_work_req_notes(
work_request_note_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
work_request_id,
notes,
work_request_note_type,
notification_id
)
VALUES(
l_work_request_note_id,
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
NVL(p_user_id,FND_GLOBAL.login_id),
p_request_id,
'@@@ '||FND_MESSAGE.GET||' : '||l_old_data||' -> '||l_new_data,
1, -- 1 for request log, 2 for approver log
null
);
SELECT wip_eam_work_req_notes_s.nextval
INTO l_work_request_note_id
FROM dual;
SELECT department_code
INTO l_old_data
FROM BOM_DEPARTMENTS
WHERE department_id = l_old_owning_dept_id
AND organization_id = p_org_id;
SELECT department_code
INTO l_new_data
FROM BOM_DEPARTMENTS
WHERE department_id = l_new_owning_dept_id
AND organization_id = p_org_id;
INSERT INTO wip_eam_work_req_notes(
work_request_note_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
work_request_id,
notes,
work_request_note_type,
notification_id
)
VALUES(
l_work_request_note_id,
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
NVL(p_user_id,FND_GLOBAL.login_id),
p_request_id,
'@@@ '||FND_MESSAGE.GET||' : '||l_old_data||' -> '||l_new_data,
1, -- 1 for request log, 2 for approver log
null
);
SELECT wip_eam_work_req_notes_s.nextval
INTO l_work_request_note_id
FROM DUAL;
SELECT fnd_profile.value('SERVER_TIMEZONE_ID') INTO l_timezone_id FROM DUAL;
SELECT timezone_code INTO l_timezone_code FROM fnd_timezones_vl WHERE upgrade_tz_id = l_timezone_id;
INSERT INTO wip_eam_work_req_notes(
work_request_note_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
work_request_id,
notes,
work_request_note_type,
notification_id
)
VALUES(
l_work_request_note_id,
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
NVL(p_user_id,FND_GLOBAL.login_id),
p_request_id,
'@@@ '||FND_MESSAGE.GET||l_timezone_code ||' : '||to_char(l_old_request_by_date, 'dd-MON-yyyy hh24:mi:ss')||' -> '
||to_char(l_new_request_by_date, 'dd-MON-yyyy hh24:mi:ss') ,
1, -- 1 for request log, 2 for approver log
null
);
SELECT wip_eam_work_req_notes_s.nextval
INTO l_work_request_note_id
FROM DUAL;
SELECT user_name
INTO l_old_data
FROM fnd_user
WHERE user_id = l_old_created_for;
SELECT user_name
INTO l_new_data
FROM fnd_user
WHERE user_id = l_new_created_for;
INSERT INTO wip_eam_work_req_notes(
work_request_note_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
work_request_id,
notes,
work_request_note_type,
notification_id
)
VALUES(
l_work_request_note_id,
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
NVL(p_user_id,FND_GLOBAL.login_id),
p_request_id,
'@@@ '||FND_MESSAGE.GET||' : '||l_old_data||' -> '||l_new_data,
1, -- 1 for request log, 2 for approver log
null
);
SELECT wip_eam_work_req_notes_s.nextval
INTO l_work_request_note_id
FROM DUAL;
INSERT INTO wip_eam_work_req_notes(
work_request_note_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
work_request_id,
notes,
work_request_note_type,
notification_id
)
VALUES(
l_work_request_note_id,
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
NVL(p_user_id,FND_GLOBAL.login_id),
p_request_id,
'@@@ '||FND_MESSAGE.GET||' : '||nvl(l_old_email,l_null)||' -> '||nvl(l_new_email,l_null),
1, -- 1 for request log, 2 for approver log
null
);
SELECT wip_eam_work_req_notes_s.nextval
INTO l_work_request_note_id
FROM DUAL;
INSERT INTO wip_eam_work_req_notes(
work_request_note_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
work_request_id,
notes,
work_request_note_type,
notification_id
)
VALUES(
l_work_request_note_id,
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
NVL(p_user_id,FND_GLOBAL.login_id),
p_request_id,
'@@@ '||FND_MESSAGE.GET||' : '||nvl(l_old_phone_number,l_null)||' -> '||nvl(l_new_phone_number,l_null),
1, /* 1 for request log, 2 for approver log*/
null
);
SELECT wip_eam_work_req_notes_s.nextval
INTO l_work_request_note_id
FROM DUAL;
SELECT meaning
INTO l_old_data
FROM MFG_LOOKUPS
WHERE lookup_type = 'SYS_YES_NO'
AND lookup_code = l_old_notify_originator;
SELECT meaning
INTO l_new_data
FROM MFG_LOOKUPS
WHERE lookup_type = 'SYS_YES_NO'
AND lookup_code = l_new_notify_originator;
INSERT INTO wip_eam_work_req_notes(
work_request_note_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
work_request_id,
notes,
work_request_note_type,
notification_id
)
VALUES(
l_work_request_note_id,
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
NVL(p_user_id,FND_GLOBAL.login_id),
p_request_id,
'@@@ '||FND_MESSAGE.GET||' : '||l_old_data||' -> '||l_new_data,
1, -- 1 for request log, 2 for approver log
null
);
SELECT wip_eam_work_req_notes_s.nextval
INTO l_work_request_note_id
FROM DUAL;
SELECT meaning
INTO l_old_data
FROM MFG_LOOKUPS
WHERE lookup_type = 'WIP_EAM_CONTACT_PREFERENCE'
AND lookup_code = l_old_contact_preference;
SELECT meaning
INTO l_new_data
FROM MFG_LOOKUPS
WHERE lookup_type = 'WIP_EAM_CONTACT_PREFERENCE'
AND lookup_code = l_new_contact_preference;
INSERT INTO wip_eam_work_req_notes(
work_request_note_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
work_request_id,
notes,
work_request_note_type,
notification_id
)
VALUES(
l_work_request_note_id,
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
NVL(p_user_id,FND_GLOBAL.login_id),
p_request_id,
'@@@ '||FND_MESSAGE.GET||' : '||l_old_data||' -> '||l_new_data,
1, -- 1 for request log, 2 for approver log
null
);
SELECT wip_eam_work_req_notes_s.nextval
INTO l_work_request_note_id
FROM DUAL;
INSERT INTO wip_eam_work_req_notes(
work_request_note_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
work_request_id,
notes,
work_request_note_type,
notification_id
)
VALUES(
l_work_request_note_id,
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
NVL(p_user_id,FND_GLOBAL.login_id),
p_request_id,
'@@@ '||FND_MESSAGE.GET||' : '||nvl(l_old_attribute_category,l_null)||' -> '||nvl(l_new_attribute_category,l_null),
1, -- 1 for request log, 2 for approver log
null
);
SELECT wip_eam_work_req_notes_s.nextval
INTO l_work_request_note_id
FROM DUAL;
INSERT INTO wip_eam_work_req_notes(
work_request_note_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
work_request_id,
notes,
work_request_note_type,
notification_id
)
VALUES(
l_work_request_note_id,
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
NVL(p_user_id,FND_GLOBAL.login_id),
p_request_id,
l_dff_notes,
1, -- 1 for request log, 2 for approver log
null
);
SELECT wip_eam_work_req_notes_s.nextval
INTO l_work_request_note_id
FROM DUAL;
INSERT INTO wip_eam_work_req_notes(
work_request_note_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
work_request_id,
notes,
work_request_note_type,
notification_id
)
VALUES(
l_work_request_note_id,
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
NVL(p_user_id,FND_GLOBAL.login_id),
p_request_id,
l_dff_notes,
1, -- 1 for request log, 2 for approver log
null
);
SELECT wip_eam_work_req_notes_s.nextval
INTO l_work_request_note_id
FROM DUAL;
INSERT INTO wip_eam_work_req_notes(
work_request_note_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
work_request_id,
notes,
work_request_note_type,
notification_id
)
VALUES(
l_work_request_note_id,
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
NVL(p_user_id,FND_GLOBAL.login_id),
p_request_id,
l_dff_notes,
1, -- 1 for request log, 2 for approver log
null
);
SELECT wip_eam_work_req_notes_s.nextval
INTO l_work_request_note_id
FROM DUAL;
INSERT INTO wip_eam_work_req_notes(
work_request_note_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
work_request_id,
notes,
work_request_note_type,
notification_id
)
VALUES(
l_work_request_note_id,
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
NVL(p_user_id,FND_GLOBAL.login_id),
p_request_id,
l_dff_notes,
1, -- 1 for request log, 2 for approver log
null
);
SELECT wip_eam_work_req_notes_s.nextval
INTO l_work_request_note_id
FROM DUAL;
INSERT INTO wip_eam_work_req_notes(
work_request_note_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
work_request_id,
notes,
work_request_note_type,
notification_id
)
VALUES(
l_work_request_note_id,
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
NVL(p_user_id,FND_GLOBAL.login_id),
p_request_id,
l_dff_notes,
1, -- 1 for request log, 2 for approver log
null
);
SELECT wip_eam_work_req_notes_s.nextval
INTO l_work_request_note_id
FROM DUAL;
INSERT INTO wip_eam_work_req_notes(
work_request_note_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
work_request_id,
notes,
work_request_note_type,
notification_id
)
VALUES(
l_work_request_note_id,
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
NVL(p_user_id,FND_GLOBAL.login_id),
p_request_id,
l_dff_notes,
1, -- 1 for request log, 2 for approver log
null
);
SELECT wip_eam_work_req_notes_s.nextval
INTO l_work_request_note_id
FROM DUAL;
INSERT INTO wip_eam_work_req_notes(
work_request_note_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
work_request_id,
notes,
work_request_note_type,
notification_id
)
VALUES(
l_work_request_note_id,
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
NVL(p_user_id,FND_GLOBAL.login_id),
p_request_id,
l_dff_notes,
1, -- 1 for request log, 2 for approver log
null
);
SELECT wip_eam_work_req_notes_s.nextval
INTO l_work_request_note_id
FROM DUAL;
INSERT INTO wip_eam_work_req_notes(
work_request_note_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
work_request_id,
notes,
work_request_note_type,
notification_id
)
VALUES(
l_work_request_note_id,
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
NVL(p_user_id,FND_GLOBAL.login_id),
p_request_id,
l_dff_notes,
1, -- 1 for request log, 2 for approver log
null
);
SELECT wip_eam_work_req_notes_s.nextval
INTO l_work_request_note_id
FROM DUAL;
INSERT INTO wip_eam_work_req_notes(
work_request_note_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
work_request_id,
notes,
work_request_note_type,
notification_id
)
VALUES(
l_work_request_note_id,
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
NVL(p_user_id,FND_GLOBAL.login_id),
p_request_id,
l_dff_notes,
1, -- 1 for request log, 2 for approver log
null
);
SELECT wip_eam_work_req_notes_s.nextval
INTO l_work_request_note_id
FROM DUAL;
INSERT INTO wip_eam_work_req_notes(
work_request_note_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
work_request_id,
notes,
work_request_note_type,
notification_id
)
VALUES(
l_work_request_note_id,
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
NVL(p_user_id,FND_GLOBAL.login_id),
p_request_id,
l_dff_notes,
1, -- 1 for request log, 2 for approver log
null
);
SELECT wip_eam_work_req_notes_s.nextval
INTO l_work_request_note_id
FROM DUAL;
INSERT INTO wip_eam_work_req_notes(
work_request_note_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
work_request_id,
notes,
work_request_note_type,
notification_id
)
VALUES(
l_work_request_note_id,
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
NVL(p_user_id,FND_GLOBAL.login_id),
p_request_id,
l_dff_notes,
1, -- 1 for request log, 2 for approver log
null
);
SELECT wip_eam_work_req_notes_s.nextval
INTO l_work_request_note_id
FROM DUAL;
INSERT INTO wip_eam_work_req_notes(
work_request_note_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
work_request_id,
notes,
work_request_note_type,
notification_id
)
VALUES(
l_work_request_note_id,
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
NVL(p_user_id,FND_GLOBAL.login_id),
p_request_id,
l_dff_notes,
1, -- 1 for request log, 2 for approver log
null
);
SELECT wip_eam_work_req_notes_s.nextval
INTO l_work_request_note_id
FROM DUAL;
INSERT INTO wip_eam_work_req_notes(
work_request_note_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
work_request_id,
notes,
work_request_note_type,
notification_id
)
VALUES(
l_work_request_note_id,
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
NVL(p_user_id,FND_GLOBAL.login_id),
p_request_id,
l_dff_notes,
1, -- 1 for request log, 2 for approver log
null
);
SELECT wip_eam_work_req_notes_s.nextval
INTO l_work_request_note_id
FROM DUAL;
INSERT INTO wip_eam_work_req_notes(
work_request_note_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
work_request_id,
notes,
work_request_note_type,
notification_id
)
VALUES(
l_work_request_note_id,
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
NVL(p_user_id,FND_GLOBAL.login_id),
p_request_id,
l_dff_notes,
1, -- 1 for request log, 2 for approver log
null
);
SELECT wip_eam_work_req_notes_s.nextval
INTO l_work_request_note_id
FROM DUAL;
INSERT INTO wip_eam_work_req_notes(
work_request_note_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
work_request_id,
notes,
work_request_note_type,
notification_id
)
VALUES(
l_work_request_note_id,
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
NVL(p_user_id,FND_GLOBAL.login_id),
p_request_id,
l_dff_notes,
1, -- 1 for request log, 2 for approver log
null
);
SELECT wip_eam_work_req_notes_s.nextval
INTO l_work_request_note_id
FROM DUAL;
INSERT INTO wip_eam_work_req_notes(
work_request_note_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
work_request_id,
notes,
work_request_note_type,
notification_id
)
VALUES(
l_work_request_note_id,
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
NVL(p_user_id,FND_GLOBAL.login_id),
p_request_id,
l_dff_notes,
1, -- 1 for request log, 2 for approver log
null
);
SELECT count(*)
INTO l_counter
FROM wip_eam_work_requests
WHERE work_request_id = p_request_id And Description Is Null;
UPDATE wip_eam_work_requests
SET description = p_request_log
WHERE work_request_id = p_request_id;
SELECT wip_eam_work_req_notes_s.nextval
INTO l_work_request_note_id
FROM dual;
INSERT INTO wip_eam_work_req_notes(
work_request_note_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
work_request_id,
notes,
work_request_note_type,
notification_id
)
VALUES(
l_work_request_note_id,
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
sysdate,
NVL(p_user_id,FND_GLOBAL.user_id),
NVL(p_user_id,FND_GLOBAL.login_id),
p_request_id,
p_request_log,
1, -- 1 for request log, 2 for approver log
null
);
select decode(wf_item_type,null,'EAMWRAP',wf_item_type),decode(wf_item_key,null,work_request_id,wf_item_key)
into l_old_wf_item_type,l_old_wf_item_key
from wip_eam_work_requests
where work_request_id = p_request_id;
-- since dept has been updated, generate a new notification
-- for the new department
wip_eam_wrapproval_pvt.StartWRAProcess (
p_work_request_id => p_request_id,
p_asset_number => l_new_asset_number,
p_asset_group => l_new_asset_group_id,
p_asset_location => null,
p_organization_id => p_org_id,
p_work_request_status_id => l_new_status_id,
p_work_request_priority_id =>l_new_priority_id,
p_work_request_owning_dept_id => l_new_owning_dept_id,
p_expected_resolution_date => l_new_request_by_date,
p_work_request_type_id => l_new_work_request_type_id,
p_notes => p_request_log,
p_notify_originator => l_new_notify_originator,
p_resultout => l_results_out,
p_error_message => l_error_message
) ;
update wip_eam_work_requests
set work_request_status_id = 1
where work_request_id = p_request_id;
ROLLBACK TO update_work_request_pvt;
ROLLBACK TO update_work_request_pvt;
ROLLBACK TO update_work_request_pvt;
ROLLBACK TO update_work_request_pvt;
END update_work_request;
SELECT department_id INTO x_dept_id
FROM BOM_DEPARTMENTS
WHERE DEPARTMENT_CODE = p_dept_name
And Organization_Id = p_org_id;
SELECT count(*) INTO l_count
FROM BOM_DEPARTMENTS BD
WHERE BD.ORGANIZATION_ID = p_organization_id
AND BD.DEPARTMENT_ID = p_department_id
AND nvl(BD.DISABLE_DATE,sysdate+1) > sysdate;
select count(*) into l_count
from mfg_lookups
where lookup_code = p_lookup_code
and lookup_type = p_lookup_type
and enabled_flag = 'Y'
and sysdate between nvl(start_date_active,sysdate-1)
and nvl(end_date_active,sysdate+1);
select count(*)
into l_count
from fnd_user
where user_id = p_user_id
and sysdate between nvl(start_date,sysdate-1) AND nvl(end_date,sysdate+1);
select nvl(work_request_auto_approve,'N')
into l_auto_approve
from wip_eam_parameters
where organization_id = p_org_id;
SELECT nvl(cii.maintainable_flag, 'Y'), cii.serial_number, msi.eam_item_type
INTO l_dummy_char, l_asset_number,l_dummy_val
FROM csi_item_instances cii, mtl_system_items msi
WHERE cii.instance_id = p_maintenance_object_id AND
cii.last_vld_organization_id = msi.organization_id AND
cii.inventory_item_id = msi.inventory_item_id ;
SELECT nvl(cii.maintainable_flag, 'Y'), eam_item_type
INTO l_dummy_char, l_dummy_val
FROM CSI_ITEM_INSTANCES CII, MTL_SYSTEM_ITEMS MSI
WHERE cii.serial_number = p_asset_number AND
cii.inventory_item_id = p_asset_group_id AND
cii.last_vld_organization_id = msi.organization_id AND
cii.inventory_item_id = msi.inventory_item_id ;
IF p_mode = 'UPDATE' THEN
l_stmt_num := 120;
SELECT organization_id, asset_group, asset_number,work_request_priority_id, work_request_status_id, expected_resolution_date, work_request_owning_dept, work_request_type_id, created_for
INTO l_org_id, l_asset_group_id, l_asset_number, l_priority_id, l_status_id, l_request_by_date, l_owning_dept_id, l_work_request_type_id, l_created_for
FROM wip_eam_work_requests
WHERE work_request_id = p_request_id;
SELECT WORK_REQUEST_AUTO_APPROVE INTO l_auto_approve
FROM WIP_EAM_PARAMETERS
WHERE organization_id = p_org_id;
select eam_item_type
into l_eam_item_type
from mtl_system_items
where inventory_item_id = p_asset_group_id
and organization_id = p_org_id;
SELECT nvl(maintainable_flag, 'Y') into l_dummy_char
FROM CSI_ITEM_INSTANCES cii
WHERE cii.serial_number = p_asset_number AND
cii.inventory_item_id = p_asset_group_id;
validate_for_date_change(l_request_by_date, p_request_by_date, 'EAM_WR_CANNOT_UPDATE', null, x_return_flag);
FND_MESSAGE.SET_NAME('WIP', 'WIP_ML_LAST_UPDATED_BY');
FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Last Updated By: ' || p_user_id);
-- validate_for_num_change(l_status_id, p_status_id,'EAM_WR_CANNOT_UPDATE', null, x_return_flag);
validate_for_num_change(l_priority_id, p_priority_id, 'EAM_WR_CANNOT_UPDATE', null, x_return_flag);
validate_for_num_change(l_owning_dept_id, p_owning_dept_id, 'EAM_WR_CANNOT_UPDATE', null, x_return_flag);
validate_for_num_change(l_work_request_type_id, p_work_request_type_id, 'EAM_WR_CANNOT_UPDATE', null, x_return_flag);
validate_for_date_change(l_request_by_date, p_request_by_date, 'EAM_WR_CANNOT_UPDATE', null, x_return_flag);
select NVL(work_request_auto_approve,'N')
into l_auto_approve_flag
from wip_eam_parameters
where organization_id = p_org_id;
select instance_id into l_instance_id
from csi_item_instances cii
where cii.inventory_item_id = p_asset_group_id
and cii.serial_number = p_asset_number;
SELECT owning_department_id
into l_owning_dept_id
from eam_org_maint_defaults
where object_type = 50
and object_id = p_maintenance_object_id
and organization_id = p_org_id;
-- select owning dept from asset
IF (p_asset_group_id <> 0 AND p_asset_group_id IS NOT NULL) THEN
begin
select instance_id into l_instance_id
from csi_item_instances cii
where cii.inventory_item_id = p_asset_group_id
and cii.serial_number = p_asset_number;
SELECT owning_department_id
INTO l_owning_dept_id
FROM eam_org_maint_defaults eomd
WHERE eomd.organization_id = p_org_id
and eomd.object_type(+) = 50
and eomd.object_id (+) = l_instance_id;
select default_department_id
into l_owning_dept_id
from wip_eam_parameters
where organization_id = p_org_id;
SELECT work_request_auto_approve
INTO l_work_request_auto_approve
FROM wip_eam_parameters
WHERE organization_id = p_org_id;
SELECT area_id
INTO l_asset_location_id
FROM eam_org_maint_defaults eomd
WHERE eomd.organization_id = p_org_id
AND eomd.object_type = 50
AND eomd.object_id = l_instance_id;